-- ABS-36168/ ABS-40447

--- CONTRACTPRODUCT table ---
CREATE SEQUENCE SEQ_CONTRACTPRODUCT
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTPRODUCT
(
  CONTRACTPRODUCT_ID     NUMBER NOT NULL,
  CONTRACT_ID            NUMBER NOT NULL,
  PRODUCT_LINK_ID        NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID NUMBER NOT NULL,
  FINISHINGMETHOD_ID     NUMBER NOT NULL,
  STARTDATE              DATE NOT NULL,
  ENDDATE                DATE NOT NULL,
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL
)
;

ALTER TABLE CONTRACTPRODUCT
  ADD CONSTRAINT XPKCONTRACTPRODUCT PRIMARY KEY (CONTRACTPRODUCT_ID);

CREATE INDEX XAK1CONTRACTPRODUCT ON CONTRACTPRODUCT (CONTRACT_ID ASC, PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC, FINISHINGMETHOD_ID ASC, STARTDATE ASC);
  
CREATE INDEX XIF1CONTRACTPRODUCT ON CONTRACTPRODUCT (CONTRACT_ID ASC);
CREATE INDEX XIF2CONTRACTPRODUCT ON CONTRACTPRODUCT (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF3CONTRACTPRODUCT ON CONTRACTPRODUCT (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF4CONTRACTPRODUCT ON CONTRACTPRODUCT (FINISHINGMETHOD_ID ASC);

ALTER TABLE CONTRACTPRODUCT
  ADD CONSTRAINT CONTRACTPRODUCT_CONTR FOREIGN KEY (CONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;
ALTER TABLE CONTRACTPRODUCT
  ADD CONSTRAINT CONTRACTPRODUCT_PRODLINK FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
ALTER TABLE CONTRACTPRODUCT
  ADD CONSTRAINT CONTRACTPRODUCT_SIZEDEF FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
ALTER TABLE CONTRACTPRODUCT
  ADD CONSTRAINT CONTRACTPRODUCT_FM FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);

--- CONTRACTPRODUCTBILLING table ---
CREATE TABLE CONTRACTPRODUCTBILLING
(
  CONTRACTPRODUCT_ID                  NUMBER NOT NULL,
  BILLINGMETHOD                       NUMBER NULL CONSTRAINT BILLINGMETHOD_VALID1_1
                                      CHECK (BILLINGMETHOD BETWEEN 1 AND 5),
  CHANGESPERWEEK                      VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD324
                                      CHECK (CHANGESPERWEEK IN ('Y','N')),
  CHARGEOVERUSE                       VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD325
                                      CHECK (CHARGEOVERUSE IN ('Y','N')),
  RENTPRICEPERFREQUENCY               VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD326
                                      CHECK (RENTPRICEPERFREQUENCY IN ('Y','N')),
  CHARGEWASHESONLYFORCOG              NUMBER NULL CONSTRAINT CHARGEWASHSONLYFORCOG_VALID1_1
                                      CHECK (CHARGEWASHESONLYFORCOG BETWEEN 0 AND 4),
  INVENTORYBILLINGTYPE                NUMBER NULL CONSTRAINT INVENTORYBILLINGTYPE_VALID1_1
                                      CHECK (INVENTORYBILLINGTYPE BETWEEN 0 AND 4),
  MINIMUMTURNAROUND                   NUMBER NULL,
  MINIMUMQUANTITY                     NUMBER NULL,
  MINIMUMFREQUENCY                    NUMBER NULL CONSTRAINT MINIMUMFREQUENCY_VALID1_1
                                      CHECK (MINIMUMFREQUENCY BETWEEN 1 AND 3),
  CHARGELOSSFORPOOL_YN                VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD327
                                      CHECK (CHARGELOSSFORPOOL_YN IN ('Y','N')),
  CHARGELOSSUNIQUEPOOLDAYS            NUMBER(3) NULL CONSTRAINT CHRLOSSUNIQUEPOOLDAYS_VALID1_1
                           	      CHECK (CHARGELOSSUNIQUEPOOLDAYS BETWEEN 0 AND 999),
  PRICEUSAGE                          NUMBER NULL CONSTRAINT PRICEUSAGE_VALID1_1
                                      CHECK (PRICEUSAGE BETWEEN 0 AND 3),
  CHARGERENTNORETURNPOOLAFTERWKS      NUMBER NULL,
  CHARGERENTNOTRETURNED               NUMBER NULL CONSTRAINT CHARGERENTNOTRETURNED_VALID1_2
                                      CHECK (CHARGERENTNOTRETURNED BETWEEN 0 AND 2),
  PERCENTAGEAFTERDEPRECIATION         NUMBER NULL,
  INVOICEFREQRELATEDRENTPRICE         VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD328
                                      CHECK (INVOICEFREQRELATEDRENTPRICE IN ('Y','N')),
  INVWASHBASEDONSOILCOUNT             VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD329
                                      CHECK (INVWASHBASEDONSOILCOUNT IN ('Y','N')),
  CHARGEWASHESPACK                    NUMBER NULL CONSTRAINT CHARGEWASHESPACK_VALID1_1
                                      CHECK (CHARGEWASHESPACK BETWEEN 1 AND 3),
  RETURNSTOCKALLOWED                  VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD330
                                      CHECK (RETURNSTOCKALLOWED IN ('Y','N')),
  SYSTEMUSER_ID                       NUMBER NOT NULL,
  TIMESTAMP                           DATE NOT NULL
)
;

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.BILLINGMETHOD
  IS '1 = Flat rated rent
2 = Rent based on minimum
3 = Circulating Inventory
4 = Piece rate only
5 = Lease
';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.CHANGESPERWEEK
  IS 'BM1, BM5';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.CHARGEOVERUSE
  IS 'BM1, BM3, BM5';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.RENTPRICEPERFREQUENCY
  IS 'BM1';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.CHARGEWASHESONLYFORCOG
  IS '0 = Both LOG and COG same wash price
1 = Both LOG and COG distinct wash price
2 = LOG only
3 = COG only
4 = Sales only
(BM1, BM3, BM4, BM5)
';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.INVENTORYBILLINGTYPE
  IS '0 - circulating inventory
1 - real quantity
2 - max inventory qty
3 - on-site inventory qty
4 – Rent for Non-returned items after n weeks
(BM2, BM3, BM5)
';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.MINIMUMTURNAROUND
  IS 'Percentage (BM2)';
COMMENT ON COLUMN CONTRACTPRODUCTBILLING.MINIMUMQUANTITY
  IS 'The Quantity itself is not defined in the contract, this is only used as a flag to indicate whether the percentage (MinimumTurnAround) is used or a fixed quantity.
(BM2)
';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.MINIMUMFREQUENCY
  IS '1 = Every week
2 = Every delivery
3 = Every month
(BM2)
';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.CHARGELOSSFORPOOL_YN
  IS 'BM2, BM3, BM4';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.CHARGELOSSUNIQUEPOOLDAYS
  IS '0 – 999
(BM2, BM3, BM4)
';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.PRICEUSAGE
  IS '0 = Not applicable
1 = Split price
2 = Rent price only
3 = Wash price only
(BM3)
';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.CHARGERENTNORETURNPOOLAFTERWKS
  IS 'BM3';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.CHARGERENTNOTRETURNED
  IS 'Used in combination with BM3 and InventoryBillingType = 4
0 = Charge Rent for not returned Pool
1 = Charge Rent for not returned Garments (non-pool)
2 = Charge Rent for not returned Pool and Garments
';
COMMENT ON COLUMN CONTRACTPRODUCTBILLING.PERCENTAGEAFTERDEPRECIATION
  IS 'BM3';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.INVOICEFREQRELATEDRENTPRICE
  IS 'BM3';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.INVWASHBASEDONSOILCOUNT
  IS 'BM3';

COMMENT ON COLUMN CONTRACTPRODUCTBILLING.CHARGEWASHESPACK
  IS '1 = Pack
2 = Content
3 = Both
(BM4)
';
COMMENT ON COLUMN CONTRACTPRODUCTBILLING.RETURNSTOCKALLOWED
  IS 'BM5';

ALTER TABLE CONTRACTPRODUCTBILLING
  ADD CONSTRAINT XPKCONTRACTPRODUCTBILLING PRIMARY KEY (CONTRACTPRODUCT_ID);
  
ALTER TABLE CONTRACTPRODUCTBILLING
  ADD CONSTRAINT CONTRACTPRODUCTBILLING_CONTR FOREIGN KEY (CONTRACTPRODUCT_ID)
  REFERENCES CONTRACTPRODUCT (CONTRACTPRODUCT_ID) ON DELETE CASCADE;

--- CONTRACTPRODUCTADDBILLING table ---
CREATE TABLE CONTRACTPRODUCTADDBILLING
(
  CONTRACTPRODUCT_ID       NUMBER NOT NULL,
  SEASONTYPE_ID            NUMBER NULL,
  SEASONALRENT             NUMBER NULL CONSTRAINT SEASONALRENT_VALID1_2
                           CHECK (SEASONALRENT BETWEEN 0 AND 2),
  CHARGEWASHINACTIVESEASON VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD331
                           CHECK (CHARGEWASHINACTIVESEASON IN ('Y','N')),
  RESIDUALVALUESCHEME_ID   NUMBER NULL,
  SRCMETHOD                NUMBER NULL CONSTRAINT SRCMETHOD_VALID1_1
                           CHECK (SRCMETHOD BETWEEN 0 AND 4),
  SRCPERCENTAGESOILCOUNT   NUMBER(15,2) NULL,
  SRCPERCENTAGEINVENTORY   NUMBER(15,2) NULL,
  SRCPERCENTAGEBILLEDQTY   NUMBER(15,2) NULL,
  DELIVERREPLACEMENTQTY    VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD332
                           CHECK (DELIVERREPLACEMENTQTY IN ('Y','N')),
  SRCFREQUENCY             NUMBER NULL CONSTRAINT SRCFREQUENCY_VALID1_1
                           CHECK (SRCFREQUENCY BETWEEN 1 AND 3),
  SRCCHARGERENT            NUMBER NULL CONSTRAINT SRCCHARGERENT_VALID1_1
                           CHECK (SRCCHARGERENT IN (0, 1)),
  SHOWFOCRENTONINVOICE     VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD333
                           CHECK (SHOWFOCRENTONINVOICE IN ('Y','N')),
  RESETRVUPONREISSUE       NUMBER NULL CONSTRAINT RESETRVUPONREISSUE_VALID1_2
                           CHECK (RESETRVUPONREISSUE IN (0, 1)),
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL
)
;

COMMENT ON COLUMN CONTRACTPRODUCTADDBILLING.SEASONALRENT IS '0 = Not applicable
1 = Only charge rent during active season
2 =  Charge rent for both active and inactive season)';

COMMENT ON COLUMN CONTRACTPRODUCTADDBILLING.CHARGEWASHINACTIVESEASON
  IS 'Note: in PLPC this attribute is called ChargeRentInactiveSeason but used for charging wash).
';

COMMENT ON COLUMN CONTRACTPRODUCTADDBILLING.SRCMETHOD
  IS '0 = No Systematic Replacement,
1 = Percentage of Soil Count,
2 = Percentage of Circulating Inventory,
3 = Fixed Quantity
4 = Percentage of Billed Quantity
';
COMMENT ON COLUMN CONTRACTPRODUCTADDBILLING.SRCPERCENTAGESOILCOUNT
  IS 'Used for SRCMethod = 1';
COMMENT ON COLUMN CONTRACTPRODUCTADDBILLING.SRCPERCENTAGEINVENTORY
  IS 'Used for SRCMethod = 2';
COMMENT ON COLUMN CONTRACTPRODUCTADDBILLING.SRCPERCENTAGEBILLEDQTY
  IS 'Used for SRCMethod = 4';

COMMENT ON COLUMN CONTRACTPRODUCTADDBILLING.SRCFREQUENCY
  IS '1 = Every Delivery,
2 = Every Week First Delivery,
3 = Every Week Last Delivery.
Only applicable if SRCMethod = 2 or 3.
';

COMMENT ON COLUMN CONTRACTPRODUCTADDBILLING.SRCCHARGERENT
  IS '0 = No Rent is charged for Replacements
1 = Additional Rent is charged for Replacements in addition to SRC. Additional Rent is charged only once.
';

COMMENT ON COLUMN CONTRACTPRODUCTADDBILLING.SHOWFOCRENTONINVOICE
  IS 'Y / N (FOC = Free Of Charge)';

COMMENT ON COLUMN CONTRACTPRODUCTADDBILLING.RESETRVUPONREISSUE
  IS 'Indicates whether commercial Residual Value should be reset upon Reissue of a garment
Possible values:
0 = No
1 = Yes
';

ALTER TABLE CONTRACTPRODUCTADDBILLING
  ADD CONSTRAINT XPKCONTRACTPRODUCTADDBILLING PRIMARY KEY (CONTRACTPRODUCT_ID);
  
CREATE INDEX XIF1CONTRACTPRODUCTADDBILLING ON CONTRACTPRODUCTADDBILLING (SEASONTYPE_ID ASC);

ALTER TABLE CONTRACTPRODUCTADDBILLING
  ADD CONSTRAINT CONTRACTPRODUCTADDBILLING_CP FOREIGN KEY (CONTRACTPRODUCT_ID)
  REFERENCES  CONTRACTPRODUCT(CONTRACTPRODUCT_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTPRODUCTADDBILLING
  ADD CONSTRAINT CONTRACTPRODUCTADDBILLING_ST FOREIGN KEY (SEASONTYPE_ID)
  REFERENCES  SEASONTYPE(SEASONTYPE_ID);

--- CONTRACTPRODUCTORDERDELIVERY table ---
CREATE TABLE CONTRACTPRODUCTORDERDELIVERY
(
  CONTRACTPRODUCT_ID    NUMBER NOT NULL,
  DELAYEDEVENEXCHANGE   NUMBER NULL CONSTRAINT DELAYEDEVENEXCHANGE_VALID1_1
                        CHECK (DELAYEDEVENEXCHANGE BETWEEN 0 AND 8),
  DEEDELIVERYSCHEME    NUMBER NULL CONSTRAINT DEEDDELIVERYSCHEME_VALID1_1
                        CHECK (DEEDELIVERYSCHEME IN (0, 1)),
  STRAIGHTEVENEXCHANGE  VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD334
                        CHECK (STRAIGHTEVENEXCHANGE IN ('Y','N')),
  ORDERVIAWEB           NUMBER NULL CONSTRAINT ORDERVIAWEB_VALID1_1
                        CHECK (ORDERVIAWEB IN (0, 1)),
  SOILCOUNTMETHOD       NUMBER NULL CONSTRAINT SOILCOUNTMETHOD_VALID1_1
                        CHECK (SOILCOUNTMETHOD BETWEEN 0 AND 3),
  CLEANCOUNT            VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD335
                        CHECK (CLEANCOUNT IN ('Y','N')),
  CODEUNIQUELY          VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD336
                        CHECK (CODEUNIQUELY IN ('Y','N')),
  ONSITEINVENTORYCOUNT  VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD337
                        CHECK (ONSITEINVENTORYCOUNT IN ('Y','N')),
  AUTOMATICSALES        VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD338
                        CHECK (AUTOMATICSALES IN ('Y','N')),
  CUSTOMERRELATED       VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD339
                        CHECK (CUSTOMERRELATED IN ('Y','N')),
  AUTOREPLAFTERLIFETIME VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD340
                        CHECK (AUTOREPLAFTERLIFETIME IN ('Y','N')),
  ALLOWRETURNTOSTOCK    NUMBER(1) NULL CONSTRAINT ALLOWRETURNTOSTOCK_VALID1_1
                        CHECK (ALLOWRETURNTOSTOCK BETWEEN 0 AND 2),
  RAGGEDITEMACTION      NUMBER(1) NULL CONSTRAINT RAGGEDITEMACTION_VALID1_1
                        CHECK (RAGGEDITEMACTION BETWEEN 0 AND 6),
  SYSTEMUSER_ID         NUMBER NOT NULL,
  TIMESTAMP             DATE NOT NULL
)
;

COMMENT ON COLUMN CONTRACTPRODUCTORDERDELIVERY.DELAYEDEVENEXCHANGE
  IS '0 = No delay
1 = Next visit
2 = Next week same day
3 = 10 day delay
4 = Return route in route schedule
5 = 14 delay basis
6 = 28 delay basis
7 = According to delivery scheme
8 = Next visit including same day
';
COMMENT ON COLUMN CONTRACTPRODUCTORDERDELIVERY.DEEDELIVERYSCHEME
  IS '0 = Delivery frequency is not considered in calculation for next delivery date (unchecked, default)
1 = Delivery Frequency is used in calculation for next delivery date (checked).
';

COMMENT ON COLUMN CONTRACTPRODUCTORDERDELIVERY.ORDERVIAWEB
  IS '0 = No
1 = Yes
';
COMMENT ON COLUMN CONTRACTPRODUCTORDERDELIVERY.SOILCOUNTMETHOD
  IS '0 = no soil count
1 = manual
2 = auto return assumed
3 = weight based
';

COMMENT ON COLUMN CONTRACTPRODUCTORDERDELIVERY.ALLOWRETURNTOSTOCK
  IS '0 = Yes (default)
1 = Yes, excluding special sizes
2 = No
';

COMMENT ON COLUMN CONTRACTPRODUCTORDERDELIVERY.RAGGEDITEMACTION
  IS '0 = Not defined (follow system setting)
1 = No Action - Default
2 = Decrease Max Inventory - Default
3 = Create Work Order for Replacement - Default
4 = No Action - Fixed
5 = Decrease Max Inventory - Fixed
6 = Create Work Order for Replacement - Fixed
';

ALTER TABLE CONTRACTPRODUCTORDERDELIVERY
  ADD CONSTRAINT XPKCONTRACTPRODUCTORDERDELIV PRIMARY KEY (CONTRACTPRODUCT_ID);
  
ALTER TABLE CONTRACTPRODUCTORDERDELIVERY
  ADD CONSTRAINT CONTRACTPRODUCTORDERDELIV_CP FOREIGN KEY (CONTRACTPRODUCT_ID)
  REFERENCES CONTRACTPRODUCT (CONTRACTPRODUCT_ID) ON DELETE CASCADE;

--- CONTRACTPRODUCTMISCELLANEOUS table ---
CREATE TABLE CONTRACTPRODUCTMISCELLANEOUS
(
  CONTRACTPRODUCT_ID           NUMBER NOT NULL,
  CUSTOMCODE                   VARCHAR2(16) NULL,
  CUSTOMDESCRIPTION            VARCHAR2(30) NULL,
  EANNUMBER                    NUMBER NULL,
  NANNUMBER                    NUMBER NULL,
  SALESEMPLOYEE_ID             NUMBER NULL,
  COMMISSIONPAYABLE            VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD341
                               CHECK (COMMISSIONPAYABLE IN ('Y','N')),
  SALESEMPLOYEE2_ID            NUMBER NULL,
  CONTRACTPURCHASEORDER_ID     NUMBER NULL,
  CONTRACTPURCHASEORDERLINE_ID NUMBER NULL,
  GAINREASON_ID                NUMBER NULL,
  GAINCOMPETITOR_ID            NUMBER NULL,
  LOSTREASON_ID                NUMBER NULL,
  LOSTCOMPETITOR_ID            NUMBER NULL,
  REMARK                       VARCHAR2(80) NULL,
  SYSTEMUSER_ID                NUMBER NOT NULL,
  TIMESTAMP                    DATE NOT NULL
)
;

ALTER TABLE CONTRACTPRODUCTMISCELLANEOUS
  ADD CONSTRAINT XPKCONTRACTPRODUCTMISC PRIMARY KEY (CONTRACTPRODUCT_ID);
  
CREATE INDEX XIF1CONTRACTPRODUCTMISC ON CONTRACTPRODUCTMISCELLANEOUS (SALESEMPLOYEE_ID ASC);
CREATE INDEX XIF2CONTRACTPRODUCTMISC ON CONTRACTPRODUCTMISCELLANEOUS (SALESEMPLOYEE2_ID ASC);
CREATE INDEX XIF3CONTRACTPRODUCTMISC ON CONTRACTPRODUCTMISCELLANEOUS (CONTRACTPURCHASEORDER_ID ASC);
CREATE INDEX XIF4CONTRACTPRODUCTMISC ON CONTRACTPRODUCTMISCELLANEOUS (CONTRACTPURCHASEORDERLINE_ID ASC);
CREATE INDEX XIF5CONTRACTPRODUCTMISC ON CONTRACTPRODUCTMISCELLANEOUS (GAINREASON_ID ASC);
CREATE INDEX XIF6CONTRACTPRODUCTMISC ON CONTRACTPRODUCTMISCELLANEOUS (GAINCOMPETITOR_ID ASC);
CREATE INDEX XIF7CONTRACTPRODUCTMISC ON CONTRACTPRODUCTMISCELLANEOUS (LOSTREASON_ID ASC);
CREATE INDEX XIF8CONTRACTPRODUCTMISC ON CONTRACTPRODUCTMISCELLANEOUS (LOSTCOMPETITOR_ID ASC);

ALTER TABLE CONTRACTPRODUCTMISCELLANEOUS
  ADD CONSTRAINT CONTRACTPRODUCTMISC_CP FOREIGN KEY (CONTRACTPRODUCT_ID)
  REFERENCES CONTRACTPRODUCT (CONTRACTPRODUCT_ID) ON DELETE CASCADE;
ALTER TABLE CONTRACTPRODUCTMISCELLANEOUS
  ADD CONSTRAINT CONTRACTPRODUCTMISC_SE FOREIGN KEY (SALESEMPLOYEE_ID)
  REFERENCES EMPLOYEE (EMPLOYEE_ID);
ALTER TABLE CONTRACTPRODUCTMISCELLANEOUS
  ADD CONSTRAINT CONTRACTPRODUCTMISC_SE2 FOREIGN KEY (SALESEMPLOYEE2_ID)
  REFERENCES EMPLOYEE (EMPLOYEE_ID);
ALTER TABLE CONTRACTPRODUCTMISCELLANEOUS
  ADD CONSTRAINT CONTRACTPRODUCTMISC_CPO FOREIGN KEY (CONTRACTPURCHASEORDER_ID)
  REFERENCES CONTRACTPURCHASEORDER (CONTRACTPURCHASEORDER_ID);
ALTER TABLE CONTRACTPRODUCTMISCELLANEOUS
  ADD CONSTRAINT CONTRACTPRODUCTMISC_CPOL FOREIGN KEY (CONTRACTPURCHASEORDERLINE_ID)
  REFERENCES CONTRACTPURCHASEORDERLINE (CONTRACTPURCHASEORDERLINE_ID);
ALTER TABLE CONTRACTPRODUCTMISCELLANEOUS
  ADD CONSTRAINT CONTRACTPRODUCTMISC_GLR FOREIGN KEY (GAINREASON_ID)
  REFERENCES GAINLOSTREASON (GAINLOSTREASON_ID);
ALTER TABLE CONTRACTPRODUCTMISCELLANEOUS
  ADD CONSTRAINT CONTRACTPRODUCTMISC_GCOMP FOREIGN KEY (GAINCOMPETITOR_ID)
  REFERENCES COMPETITOR (COMPETITOR_ID);
ALTER TABLE CONTRACTPRODUCTMISCELLANEOUS
  ADD CONSTRAINT CONTRACTPRODUCTMISC_LR FOREIGN KEY (LOSTREASON_ID)
  REFERENCES GAINLOSTREASON (GAINLOSTREASON_ID);
ALTER TABLE CONTRACTPRODUCTMISCELLANEOUS
  ADD CONSTRAINT CONTRACTPRODUCTMISC_LCOMP FOREIGN KEY (LOSTCOMPETITOR_ID)
  REFERENCES COMPETITOR (COMPETITOR_ID);

--- CONTRACTCOMPONENTGRMSETTINGS table ---
CREATE TABLE CONTRACTCOMPONENTGRMSETTINGS
(
  CONTRACT_ID              NUMBER NOT NULL,
  ALLOWAUTOMATICSALES      VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD342
                           CHECK (ALLOWAUTOMATICSALES IN ('Y','N')),
  DEPOSITSTOCKALLOWED      VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD343
                           CHECK (DEPOSITSTOCKALLOWED IN ('Y','N')),
  CHARGEREPAIRS            NUMBER NULL CONSTRAINT CHARGEREPAIRS_VALID1_1
                           CHECK (CHARGEREPAIRS BETWEEN 0 AND 2),
  CHARGEREPAIRCOG          NUMBER NULL,
  VARIABLESERVICES         NUMBER NULL,
  CHARGERESIDUALVALUE      VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD344
                           CHECK (CHARGERESIDUALVALUE IN ('Y','N')),
  CHARGEWASHFIRSTISSUE     VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD345
                           CHECK (CHARGEWASHFIRSTISSUE IN ('Y','N')),
  CHARGELOSSCHARGE         VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD346
                           CHECK (CHARGELOSSCHARGE IN ('Y','N')),
  ALLOWCHARGERETURNRENT    NUMBER NULL CONSTRAINT ALLOWCHARGERETURNRENT_VALID1_2
                           CHECK (ALLOWCHARGERETURNRENT IN (0, 1)),
  RETURNRENTWEEKS          NUMBER NULL CONSTRAINT RETURNRENTWEEKS_VALID1_1
                           CHECK (RETURNRENTWEEKS BETWEEN 0 AND 999),
  ALLOWCHARGEMINRENT       NUMBER NULL CONSTRAINT ALLOWCHARGEMINRENT_VALID1_2
                           CHECK (ALLOWCHARGEMINRENT IN (0, 1)),
  MINRENTWEEKS             NUMBER NULL CONSTRAINT MINRENTWEEKS_VALID1_1
                           CHECK (MINRENTWEEKS BETWEEN 0 AND 999),
  CHARGESERVICECOSTS       VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD347
                           CHECK (CHARGESERVICECOSTS IN ('Y','N')),
  DAYSSPECIALGRADE         NUMBER NULL CONSTRAINT DAYSSPECIALGRADE_VALID1_1
                           CHECK (DAYSSPECIALGRADE BETWEEN 0 AND 999),
  FIRSTDAYSGRADE_ID        NUMBER NULL,
  FIRSTISSUEGRADE_ID       NUMBER NULL,
  QUALITYGRADE_ID          NUMBER NULL,
  GARMENTRENTSPECIFICATION NUMBER NULL CONSTRAINT GARMENTRENTSPECIFIC_VALID1_1
                           CHECK (GARMENTRENTSPECIFICATION BETWEEN 0 AND 7),
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL
)
;

COMMENT ON COLUMN CONTRACTCOMPONENTGRMSETTINGS.CHARGEREPAIRS
  IS '0 = No Repair allowed and No charge repair
1 = Repair allowed and No charge repair
2 = Repair allowed and Charge repair
';

COMMENT ON COLUMN CONTRACTCOMPONENTGRMSETTINGS.CHARGEREPAIRCOG
  IS '0 = No Repair allowed and No charge repair
1 = Repair allowed and No charge repair
2 = Repair allowed and Charge repair
';

COMMENT ON COLUMN CONTRACTCOMPONENTGRMSETTINGS.VARIABLESERVICES
  IS '0 = No Repair allowed and No charge repair
1 = Repair allowed and No charge repair
2 = Repair allowed and Charge repair
';

COMMENT ON COLUMN CONTRACTCOMPONENTGRMSETTINGS.ALLOWCHARGERETURNRENT
  IS 'Flag that indicates whether return rent should be charged for this customer. Possible values:
0 = Do not charge return rent
1 = Do charge return rent
';

COMMENT ON COLUMN CONTRACTCOMPONENTGRMSETTINGS.RETURNRENTWEEKS
  IS '0 – 999. This attributes indicates the number of weeks that still rent may be charged after the item has been taken out of circulation. ';

COMMENT ON COLUMN CONTRACTCOMPONENTGRMSETTINGS.ALLOWCHARGEMINRENT
  IS 'Flag that indicates whether min rent should be charged for this customer. Possible values:
0 = Do not charge return rent
1 = Do charge return rent
';

COMMENT ON COLUMN CONTRACTCOMPONENTGRMSETTINGS.MINRENTWEEKS
  IS '0 – 999. This attribute indicates the minimum number of weeks that rent will be charged for this customer. When an item has been taken out of circulation prior to reaching this number of weeks then still rent will be charged (as compensation) during this period.';

COMMENT ON COLUMN CONTRACTCOMPONENTGRMSETTINGS.DAYSSPECIALGRADE
  IS '0 – 999';

COMMENT ON COLUMN CONTRACTCOMPONENTGRMSETTINGS.GARMENTRENTSPECIFICATION
  IS '0 = Not applicable
1 = Products per Wearer
2 = Products per Wearer Condensed
3 = Wearer totals only
4 = Quantities per Product
5 = Number of men per total price
6 = Wearer function
7 = Wearer Function Fixed Total Price
';

ALTER TABLE CONTRACTCOMPONENTGRMSETTINGS
  ADD CONSTRAINT XPKCONTRACTCOMPONENTGRMSETT PRIMARY KEY (CONTRACT_ID);
  
CREATE INDEX XIF1CONTRACTCOMPONENTGRMSETT ON CONTRACTCOMPONENTGRMSETTINGS (FIRSTDAYSGRADE_ID ASC);
CREATE INDEX XIF2CONTRACTCOMPONENTGRMSETT ON CONTRACTCOMPONENTGRMSETTINGS (FIRSTISSUEGRADE_ID ASC);
CREATE INDEX XIF3CONTRACTCOMPONENTGRMSETT ON CONTRACTCOMPONENTGRMSETTINGS (QUALITYGRADE_ID ASC);
  
ALTER TABLE CONTRACTCOMPONENTGRMSETTINGS
  ADD CONSTRAINT CONTRACTCOMPONENTGRMSETT_CONTR FOREIGN KEY (CONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;
ALTER TABLE CONTRACTCOMPONENTGRMSETTINGS
  ADD CONSTRAINT CONTRACTCOMPONENTGRMSETT_FDG FOREIGN KEY (FIRSTDAYSGRADE_ID)
  REFERENCES QUALITYGRADE (QUALITYGRADE_ID);
ALTER TABLE CONTRACTCOMPONENTGRMSETTINGS
  ADD CONSTRAINT CONTRACTCOMPONENTGRMSETT_FIG FOREIGN KEY (FIRSTISSUEGRADE_ID)
  REFERENCES QUALITYGRADE (QUALITYGRADE_ID);
ALTER TABLE CONTRACTCOMPONENTGRMSETTINGS
  ADD CONSTRAINT CONTRACTCOMPONENTGRMSETT_QG FOREIGN KEY (QUALITYGRADE_ID)
  REFERENCES QUALITYGRADE (QUALITYGRADE_ID);

--- CONTRACTCOMPONENTGRMCHANGES table --- 
ALTER TABLE CONTRACTCOMPONENTGRMCHANGES
  ADD CONSTRAINT XPKCONTRACTCOMPONENTGRMCHANGES PRIMARY KEY (CONTRACT_ID);

--- CONTRACTCOMPONENTBILLING table --- 
ALTER TABLE CONTRACTCOMPONENTBILLING ADD (SPLITINVOICESPERACTIVITY VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD348
                                          CHECK (SPLITINVOICESPERACTIVITY IN ('Y','N')),
                                          CHARGELOSSFORPOOLITEMS NUMBER NULL CONSTRAINT CHRLOSSFORPOOLITEMS_VALID1_1
                                          CHECK (CHARGELOSSFORPOOLITEMS IN (0, 1)),
                                          CHARGELOSSFORUNIQUEPOOLDAYS NUMBER NULL CONSTRAINT CHRLOSSFORUNIQUEPOOLD_VALID1_1
                                          CHECK (CHARGELOSSFORUNIQUEPOOLDAYS BETWEEN 1 AND 999));

COMMENT ON COLUMN CONTRACTCOMPONENTBILLING.CHARGELOSSFORPOOLITEMS
  IS '0 = Not applicable / do not charge loss for pool
1 = Charge loss for pool items
';
COMMENT ON COLUMN CONTRACTCOMPONENTBILLING.CHARGELOSSFORUNIQUEPOOLDAYS
  IS '1 – 999';

ALTER TABLE CONTRACTCOMPONENTBILLING
  ADD CONSTRAINT XPKCONTRACTCOMPONENTBILLING PRIMARY KEY (CONTRACT_ID);


-- CONTRACTCOMPONENTPAYMENT table ---
ALTER TABLE CONTRACTCOMPONENTPAYMENT
  ADD CONSTRAINT XPKCONTRACTCOMPONENTPAYMENT PRIMARY KEY (CONTRACT_ID);


-- CONTRACTCOMPONENTMISC table ---
CREATE TABLE CONTRACTCOMPONENTMISC
(
  CONTRACT_ID             NUMBER NOT NULL,
  CUSTOMERRELATED         NUMBER NULL CONSTRAINT CUSTOMERRELATED_VALID1_1
                          CHECK (CUSTOMERRELATED BETWEEN 0 AND 5),
  RELATEDCUSTOMER_LINK_ID NUMBER NULL,
  POREQUIRED              NUMBER NULL CONSTRAINT POREQUIRED_VALID1_1
                          CHECK (POREQUIRED IN (0, 1)),
  SOILCOUNT               VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD349
                          CHECK (SOILCOUNT IN ('Y','N')),
  INTERNETORDERING        VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD350
                          CHECK (INTERNETORDERING IN ('Y','N')),
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
)
;

COMMENT ON COLUMN CONTRACTCOMPONENTMISC.CUSTOMERRELATED
  IS 'Possible values:
0=Not applicable
1=Related to this customer
2=Related to bill to customer (only valid when bill to customer <> customer)
3=Related to master account (only valid if customer is connected to master account)
4=Related to common customer (only valid if customer is connected to common customer)
5=Related to other customer (only valid if customer is connected to other customer)

';
COMMENT ON COLUMN CONTRACTCOMPONENTMISC.RELATEDCUSTOMER_LINK_ID
  IS 'Only filled when CustomerRelated > 1.';
  
COMMENT ON COLUMN CONTRACTCOMPONENTMISC.POREQUIRED
  IS '0 = PO is not required
1 = PO is required
';

ALTER TABLE CONTRACTCOMPONENTMISC
  ADD CONSTRAINT XPKCONTRACTCOMPONENTMISC PRIMARY KEY (CONTRACT_ID);
  
CREATE INDEX XIF1CONTRACTCOMPONENTMISC ON CONTRACTCOMPONENTMISC (RELATEDCUSTOMER_LINK_ID ASC);

ALTER TABLE CONTRACTCOMPONENTMISC
  ADD CONSTRAINT CONTRACTCOMPONENTMISC_CONTR FOREIGN KEY (CONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTCOMPONENTMISC
  ADD CONSTRAINT CONTRACTCOMPONENTMISC_RC FOREIGN KEY (RELATEDCUSTOMER_LINK_ID)
  REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID) ON DELETE SET NULL;

--- CONTRACTPRICELIST table ---
ALTER TABLE CONTRACTPRICELIST MODIFY (GENERALPRICELIST_ID NULL);

COMMENT ON COLUMN CONTRACTPRICELIST.GENERALPRICELIST_ID
  IS 'Null means customer specific price.';
  
COMMENT ON COLUMN CONTRACTPRICELIST.PRICETYPE
  IS '0 = Undefined / inherited
1 = Wash
2 = Rent
3 = Replacement
4 = Sales
5 = Weight
6 = Repair
7 = Emblem
8 = Emblem Template
9 = Garment Insurance
10 = Special Charge
11 = Residential rent
';

CREATE INDEX XIE2CONTRACTPRICELIST ON CONTRACTPRICELIST (CONTRACT_ID ASC, PRICETYPE ASC, PRIORITY ASC);

UPDATE CONTRACTPRICELIST CPL
SET PRICETYPE = (SELECT PRICETYPE FROM GENERALPRICELIST GPL WHERE GPL.GENERALPRICELIST_ID = CPL.GENERALPRICELIST_ID)
WHERE CPL.GENERALPRICELIST_ID IS NOT NULL;

COMMIT;

--- CONTRACTDISCOUNTLIST table ---
CREATE SEQUENCE SEQ_CONTRACTDISCOUNTLIST
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTDISCOUNTLIST
(
  CONTRACTDISCOUNTLIST_ID NUMBER NOT NULL,
  CONTRACT_ID             NUMBER NOT NULL,
  GENERALDISCOUNTLIST_ID  NUMBER NOT NULL,
  STARTDATE               DATE NOT NULL,
  ENDDATE                 DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL,
  PRIORITY                NUMBER NOT NULL CONSTRAINT PRIORITY_VALID1_3
                          CHECK (PRIORITY BETWEEN 0 AND 999),
  PRICETYPE               NUMBER NOT NULL CONSTRAINT PRICETYPE_VALID1_2
                          CHECK (PRICETYPE BETWEEN 0 AND 2)
)
;

COMMENT ON COLUMN CONTRACTDISCOUNTLIST.PRIORITY
  IS '0 – 9999';
COMMENT ON COLUMN CONTRACTDISCOUNTLIST.PRICETYPE
  IS '0 = Undefined / inherited
1 = Wash
2 = Rent
';

ALTER TABLE CONTRACTDISCOUNTLIST
  ADD CONSTRAINT XPKCONTRACTDISCOUNTLIST PRIMARY KEY (CONTRACTDISCOUNTLIST_ID);
  
CREATE UNIQUE INDEX XAK1CONTRACTDISCOUNTLIST ON CONTRACTDISCOUNTLIST (CONTRACT_ID ASC, GENERALDISCOUNTLIST_ID ASC, STARTDATE ASC);

CREATE INDEX XIE1CONTRACTDISCOUNTLIST ON CONTRACTDISCOUNTLIST (CONTRACT_ID ASC, PRICETYPE ASC, PRIORITY ASC);
CREATE INDEX XIE2CONTRACTDISCOUNTLIST ON CONTRACTDISCOUNTLIST (CONTRACT_ID ASC, STARTDATE ASC, ENDDATE ASC);

CREATE INDEX XIF1CONTRACTDISCOUNTLIST ON CONTRACTDISCOUNTLIST (CONTRACT_ID ASC);
CREATE INDEX XIF2CONTRACTDISCOUNTLIST ON CONTRACTDISCOUNTLIST (GENERALDISCOUNTLIST_ID ASC);

ALTER TABLE CONTRACTDISCOUNTLIST
  ADD CONSTRAINT CONTRACTDISCOUNTLIST_CONTRACT FOREIGN KEY (CONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID);
  
ALTER TABLE CONTRACTDISCOUNTLIST
  ADD CONSTRAINT CONTRACTDISCOUNTLIST_GDL FOREIGN KEY (GENERALDISCOUNTLIST_ID)
  REFERENCES GENERALDISCOUNTLIST (GENERALDISCOUNTLIST_ID);