--ABS-73805

-------------- ERP_I_CONTRACTPRODUCT --------------
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT CHK_ERP_I_CONTRACTPRODUCT_PROD;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTPROD_SIZECODE_CHK;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTPROD_FINISHINGMT_CHK;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_BILLINGMETHOD_VALID;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD324;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD325;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD326;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CHRWASHSONLYFORCOG_VALID;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_INVENTORYBILLINGTYPE;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_MINIMUMFREQUENCY_VALID;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD327;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CHRLOSSUNIQUEPOOLDAYS;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CHARGERENTNOTRETUR_VALID;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD328;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD329;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CHARGEWASHESPACK_VALID;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD330;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT SEASONALRENT_VALID1;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT SRCMETHOD_VALID1;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT SRCFREQUENCY_VALID1;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT SHOWFOCRENTONINVOICE_VALID1;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT RESETRVUPONREISSUE_VALID1;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_DELAYEDEVENEXCHANGE;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_DEEDDELIVERYSCHEME_VALID;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD334;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_ORDERVIAWEB_VALID;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_SOILCOUNTMETHOD_VALID;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD335;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD336;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD337;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD338;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD339;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD340;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_ALLOWRETURNTOSTOCK_VALID;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_RAGGEDITEMACTION_VALID;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_YN_VALIDATIONFIELD341;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_PRODCONT_COMPLIANCYRULE;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_PRODCONT_COMPLIANCYPRICG;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTPROD_GARMENTSETUP;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTPROD_GARMMAINTENANCE;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTPROD_GARMENTLOSS;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTPROD_GARMENTREPAIR;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTRACTPRODUCT_MMPS;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTRACTPRODUCT_TOM_CHK;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTPROD_SIZE_BILLING;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTRACTPRODUCT_SIZE;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTPROD_CHSPWEEK_CHK;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTPROD_DEEDELIVERYSCHM;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTPROD_EXCHANGE_CHK;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTPROD_DATES_CHK;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTPROD_INVBILLING_CHK;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_CONTPROD_MINIMFREQ_CHECK;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_PRODCONT_MINIMUM_CHECK;
ALTER TABLE ERP_I_CONTRACTPRODUCT DROP CONSTRAINT ERP_I_PRODCONT_BILLMETHOD2;

CREATE TABLE ERP_I_CONTRACTPRODUCT
( TRANSFER_ID                    NUMBER NOT NULL,
  TRANSFER_STATUS                NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID16 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  CONTRACTCODE                   VARCHAR2(15) NOT NULL,
  PRODUCTCODE                    VARCHAR2(12) NOT NULL CONSTRAINT CHK_ERP_I_CONTRACTPRODUCT_PROD CHECK (PRODUCTCODE = UPPER(TRIM(PRODUCTCODE))),
  SIZECODE                       VARCHAR2(12) DEFAULT '-' NOT NULL CONSTRAINT ERP_I_CONTPROD_SIZECODE_CHK CHECK (SIZECODE = UPPER(TRIM(SIZECODE))),
  FINISHINGMETHOD                VARCHAR2(12) DEFAULT '-' NOT NULL CONSTRAINT ERP_I_CONTPROD_FINISHINGMT_CHK CHECK (FINISHINGMETHOD = UPPER(TRIM(FINISHINGMETHOD))),
  STARTDATE                      DATE DEFAULT TRUNC(SYSDATE),
  ENDDATE                        DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY'),
  BILLINGMETHOD                  NUMBER NULL CONSTRAINT ERP_I_BILLINGMETHOD_VALID CHECK (BILLINGMETHOD BETWEEN 1 AND 5),
  CHANGESPERWEEK                 VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD324 CHECK (CHANGESPERWEEK IN ('Y','N')),
  CHARGEOVERUSE                  VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD325 CHECK (CHARGEOVERUSE IN ('Y','N')),
  RENTPRICEPERFREQUENCY          VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD326 CHECK (RENTPRICEPERFREQUENCY IN ('Y','N')),
  CHARGEWASHESONLYFORCOG         NUMBER(1) NULL CONSTRAINT ERP_I_CHRWASHSONLYFORCOG_VALID CHECK (CHARGEWASHESONLYFORCOG BETWEEN 0 AND 4),
  INVENTORYBILLINGTYPE           NUMBER(1) NULL CONSTRAINT ERP_I_INVENTORYBILLINGTYPE CHECK (INVENTORYBILLINGTYPE BETWEEN 0 AND 4),
  MINIMUMTURNAROUND              NUMBER NULL,
  MINIMUMQUANTITY                NUMBER NULL,
  MINIMUMFREQUENCY               NUMBER(1) NULL CONSTRAINT ERP_I_MINIMUMFREQUENCY_VALID CHECK (MINIMUMFREQUENCY BETWEEN 1 AND 5),
  CHARGELOSSFORPOOL_YN           VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD327 CHECK (CHARGELOSSFORPOOL_YN IN ('Y','N')),
  CHARGELOSSUNIQUEPOOLDAYS       NUMBER(3) NULL CONSTRAINT ERP_I_CHRLOSSUNIQUEPOOLDAYS CHECK (CHARGELOSSUNIQUEPOOLDAYS BETWEEN 0 AND 999),
  PRICEUSAGE                     NUMBER(1) NULL CONSTRAINT ERP_I_PRICEUSAGE_VALID CHECK (PRICEUSAGE BETWEEN 0 AND 3),
  CHARGERENTNORETURNPOOLAFTERWKS NUMBER NULL,
  CHARGERENTNOTRETURNED          NUMBER(1) NULL CONSTRAINT ERP_I_CHARGERENTNOTRETUR_VALID CHECK (CHARGERENTNOTRETURNED BETWEEN 0 AND 2),
  PERCENTAGEAFTERDEPRECIATION    NUMBER NULL,
  INVOICEFREQRELATEDRENTPRICE    VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD328 CHECK (INVOICEFREQRELATEDRENTPRICE IN ('Y','N')),
  INVWASHBASEDONSOILCOUNT        VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD329 CHECK (INVWASHBASEDONSOILCOUNT IN ('Y','N')),
  CHARGEWASHESPACK               NUMBER(1) NULL CONSTRAINT ERP_I_CHARGEWASHESPACK_VALID CHECK (CHARGEWASHESPACK BETWEEN 1 AND 3),
  RETURNSTOCKALLOWED             VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD330 CHECK (RETURNSTOCKALLOWED IN ('Y','N')),
  SEASONTYPECODE                 VARCHAR2(6) NULL,
  SEASONALRENT                   NUMBER(1) NULL CONSTRAINT SEASONALRENT_VALID1 CHECK (SEASONALRENT BETWEEN 0 AND 2),
  CHARGEWASHINACTIVESEASON       VARCHAR2(1) NULL,
  RESIDUALVALUESCHEMECODE        VARCHAR2(6) NULL,
  SRCMETHOD                      NUMBER(1) NULL CONSTRAINT SRCMETHOD_VALID1 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,
  SRCFREQUENCY                   NUMBER(1) NULL CONSTRAINT SRCFREQUENCY_VALID1 CHECK (SRCFREQUENCY IN (0,1,2,3)),
  SRCCHARGERENT                  NUMBER(1) NULL,
  SHOWFOCRENTONINVOICE           VARCHAR2(1) NULL CONSTRAINT SHOWFOCRENTONINVOICE_VALID1 CHECK (SHOWFOCRENTONINVOICE IN ('Y','N')), 
  RESETRVUPONREISSUE             NUMBER(1) NULL CONSTRAINT RESETRVUPONREISSUE_VALID1 CHECK (RESETRVUPONREISSUE IN (0, 1)),
  DELAYEDEVENEXCHANGE            NUMBER(1) NULL CONSTRAINT ERP_I_DELAYEDEVENEXCHANGE CHECK (DELAYEDEVENEXCHANGE BETWEEN 0 AND 8),
  DEEDELIVERYSCHEME              NUMBER(1) NULL CONSTRAINT ERP_I_DEEDDELIVERYSCHEME_VALID CHECK (DEEDELIVERYSCHEME IN (0, 1)),
  STRAIGHTEVENEXCHANGE           VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD334 CHECK (STRAIGHTEVENEXCHANGE IN ('Y','N')),
  ORDERVIAWEB                    NUMBER(1) NULL CONSTRAINT ERP_I_ORDERVIAWEB_VALID CHECK (ORDERVIAWEB IN (0, 1)),
  SOILCOUNTMETHOD                NUMBER(1) NULL CONSTRAINT ERP_I_SOILCOUNTMETHOD_VALID CHECK (SOILCOUNTMETHOD BETWEEN 0 AND 3),
  CLEANCOUNT                     VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD335 CHECK (CLEANCOUNT IN ('Y','N')),
  CODEUNIQUELY                   VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD336 CHECK (CODEUNIQUELY IN ('Y','N')),
  ONSITEINVENTORYCOUNT           VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD337 CHECK (ONSITEINVENTORYCOUNT IN ('Y','N')),
  AUTOMATICSALES                 VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD338 CHECK (AUTOMATICSALES IN ('Y','N')),
  CUSTOMERRELATED                VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD339 CHECK (CUSTOMERRELATED IN ('Y','N')),
  AUTOREPLAFTERLIFETIME          VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD340 CHECK (AUTOREPLAFTERLIFETIME IN ('Y','N')),
  ALLOWRETURNTOSTOCK             NUMBER(1) NULL CONSTRAINT ERP_I_ALLOWRETURNTOSTOCK_VALID CHECK (ALLOWRETURNTOSTOCK BETWEEN 0 AND 2),
  RAGGEDITEMACTION               NUMBER(1) NULL CONSTRAINT ERP_I_RAGGEDITEMACTION_VALID CHECK (RAGGEDITEMACTION BETWEEN 0 AND 6),
  CUSTOMCODE                     VARCHAR2(16) NULL,
  CUSTOMDESCRIPTION              VARCHAR2(30) NULL,
  EANNUMBER                      NUMBER NULL,
  NANNUMBER                      NUMBER NULL,
  SALESEMPLOYEENUMBER            NUMBER NULL,
  SALESEMPLOYEENAME              VARCHAR2(40) NULL,
  SALESEMPLOYEEPLANTCODE         VARCHAR2(6) NULL,
  SALESEMPLOYEE2NUMBER           NUMBER NULL,
  SALESEMPLOYEE2NAME             VARCHAR2(40) NULL,
  SALESEMPLOYEE2PLANTCODE        VARCHAR2(6) NULL,
  COMMISSIONPAYABLE              VARCHAR2(1) NULL CONSTRAINT ERP_I_YN_VALIDATIONFIELD341 CHECK (COMMISSIONPAYABLE IN ('Y','N')),
  GAINREASONCODE                 VARCHAR2(6) NULL,
  GAINREASONDESCRIPTION          VARCHAR2(30) NULL,
  GAINCOMPETITORCODE             VARCHAR2(6) NULL,
  GAINCOMPETITORNAME             VARCHAR2(30) NULL,
  LOSTREASONCODE                 VARCHAR2(6) NULL,
  LOSTREASONDESCRIPTION          VARCHAR2(30) NULL,
  LOSTCOMPETITORCODE             VARCHAR2(6) NULL,
  LOSTCOMPETITORNAME             VARCHAR2(30) NULL,
  COMPLIANCYRULEMISC             NUMBER NULL CONSTRAINT ERP_I_PRODCONT_COMPLIANCYRULE CHECK (COMPLIANCYRULEMISC IN (NULL, 0, 1, 2)),
  REMARK                         VARCHAR2(80) NULL,
  COMPLIANCYRULEPRICING          NUMBER NULL CONSTRAINT ERP_I_PRODCONT_COMPLIANCYPRICG CHECK (COMPLIANCYRULEPRICING IN (NULL, 0, 1, 2)),
  GARMENTSETUP                   VARCHAR2(1) NULL CONSTRAINT ERP_I_CONTPROD_GARMENTSETUP CHECK (GARMENTSETUP IN ('Y','N')),
  GARMENTMAINTENANCE             VARCHAR2(1) NULL CONSTRAINT ERP_I_CONTPROD_GARMMAINTENANCE CHECK (GARMENTMAINTENANCE IN ('Y','N')), 
  GARMENTLOSS                    VARCHAR2(1) NULL CONSTRAINT ERP_I_CONTPROD_GARMENTLOSS CHECK (GARMENTLOSS IN ('Y','N')), 
  GARMENTREPAIR                  VARCHAR2(1) NULL CONSTRAINT ERP_I_CONTPROD_GARMENTREPAIR CHECK (GARMENTREPAIR IN ('Y','N')), 
  MMPSPECIFICPERCENTAGE          NUMBER NULL CONSTRAINT ERP_I_CONTRACTPRODUCT_MMPS CHECK ((MMPSPECIFICPERCENTAGE BETWEEN 0 AND 100) OR (MMPSPECIFICPERCENTAGE IS NULL)),     
  TYPEOFMERCHANDISE              NUMBER DEFAULT 2 NOT NULL CONSTRAINT ERP_I_CONTRACTPRODUCT_TOM_CHK CHECK (TYPEOFMERCHANDISE NOT IN (4)));

COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';  
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.CONTRACTCODE
  IS 'Must exist in ERP_I_CONTRACT';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.PRODUCTCODE
  IS 'Must exist in ERP_I_PRODUCT';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.BILLINGMETHOD
  IS '1 = Flat rated rent 
2 = Rent based on minimum 
3 = Circulating Inventory 
4 = Piece rate only 
5 = Lease';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.CHANGESPERWEEK
  IS 'BM1, BM5';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.CHARGEOVERUSE
  IS 'BM1, BM3, BM5';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.RENTPRICEPERFREQUENCY
  IS 'BM1';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.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 ERP_I_CONTRACTPRODUCT.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 ERP_I_CONTRACTPRODUCT.MINIMUMTURNAROUND
  IS 'Percentage (BM2)';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.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 ERP_I_CONTRACTPRODUCT.MINIMUMFREQUENCY
  IS '1 = Every week 
2 = Every delivery 
3 = Every month (BM2) 
4 = Every delivery Qty > 0 
5 = Delivery Scheme based';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.CHARGELOSSFORPOOL_YN
  IS 'BM2, BM3, BM4';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.CHARGELOSSUNIQUEPOOLDAYS
  IS '0 – 999 
(BM2, BM3, BM4)';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.PRICEUSAGE
  IS '0 = Not applicable 
1 = Split price 
2 = Rent price only 
3 = Wash price only (BM3)';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.CHARGERENTNORETURNPOOLAFTERWKS
  IS 'BM3';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.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 ERP_I_CONTRACTPRODUCT.PERCENTAGEAFTERDEPRECIATION
  IS 'BM3';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.INVOICEFREQRELATEDRENTPRICE
  IS 'BM3';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.INVWASHBASEDONSOILCOUNT
  IS 'BM3';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.CHARGEWASHESPACK
  IS '1 = Pack 
2 = Content 
3 = Both (BM4)';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.RETURNSTOCKALLOWED
  IS 'BM5';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.SEASONALRENT
  IS '0 = Not applicable 
1 = Only charge rent during active season 
2 = Charge rent for both active and inactive season)';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.CHARGEWASHINACTIVESEASON
  IS 'Note: in PLPC this attribute is called ChargeRentInactiveSeason but used for charging wash) .';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.RESIDUALVALUESCHEMECODE
  IS 'Best would be if it already exists in the database, we do not know how to set it up correctly if it does not exist it will be created and it will get the default values';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.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 ERP_I_CONTRACTPRODUCT.SRCPERCENTAGESOILCOUNT
  IS 'Used for SRCMethod = 1';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.SRCPERCENTAGEINVENTORY
  IS 'Used for SRCMethod = 2';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.SRCPERCENTAGEBILLEDQTY
  IS 'Used for SRCMethod = 4';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.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 ERP_I_CONTRACTPRODUCT.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 ERP_I_CONTRACTPRODUCT.SHOWFOCRENTONINVOICE
  IS 'Y / N (FOC = Free Of Charge)';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.RESETRVUPONREISSUE
  IS 'Indicates whether commercial Residual Value should be reset upon Reissue of a garment Possible values: 
0 = No 
1 = Yes';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.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 ERP_I_CONTRACTPRODUCT.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 ERP_I_CONTRACTPRODUCT.ORDERVIAWEB
  IS '0 = No
1 = Yes';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.SOILCOUNTMETHOD
  IS '0 = no soil count
1 = manual
2 = auto return assumed
3 = weight based';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.ALLOWRETURNTOSTOCK
  IS '0 = Yes (default)
1 = Yes, excluding special sizes
2 = No';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.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';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.COMPLIANCYRULEMISC
  IS '0 = Contract suggested, can be overruled 
1 = Contract enforced, can only be overruled by maintenance user
2 = Contract enforced
No default (Null)';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT.COMPLIANCYRULEPRICING  
  IS '0 = Contract suggested, can be overruled 
1 = Contract enforced, can only be overruled by maintenance user 
2 = Contract enforced No default 
(Null = no compliancy rule)'; 

--section break for pack_Script

ALTER TABLE ERP_I_CONTRACTPRODUCT
  ADD CONSTRAINT PK_ERP_I_CONTRACTPRODUCT PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_I_CONTRACTPRODUCT ON ERP_I_CONTRACTPRODUCT (CONTRACTCODE ASC,PRODUCTCODE ASC,SIZECODE ASC,FINISHINGMETHOD ASC,STARTDATE ASC);   

CREATE INDEX XIF1ERP_I_CONTRACTPRODUCT ON ERP_I_CONTRACTPRODUCT (SALESEMPLOYEEPLANTCODE ASC);
CREATE INDEX XIF2ERP_I_CONTRACTPRODUCT ON ERP_I_CONTRACTPRODUCT (SALESEMPLOYEE2PLANTCODE ASC);
             
ALTER TABLE ERP_I_CONTRACTPRODUCT
  ADD CONSTRAINT ERP_I_FK_CONTRCTPROD_PLANTCODE FOREIGN KEY (SALESEMPLOYEEPLANTCODE)
  REFERENCES PLANT (CODE) ON DELETE CASCADE;
ALTER TABLE ERP_I_CONTRACTPRODUCT
  ADD CONSTRAINT ERP_I_FK_CONTRTPROD_PLANTCODE2 FOREIGN KEY (SALESEMPLOYEE2PLANTCODE)
  REFERENCES PLANT (CODE) ON DELETE CASCADE;
ALTER TABLE ERP_I_CONTRACTPRODUCT
  ADD CONSTRAINT ERP_I_CONTPROD_SIZE_BILLING
  CHECK ((SIZECODE <> '*') OR (SIZECODE='*' AND BILLINGMETHOD IN (1,3,4,5)));  
ALTER TABLE ERP_I_CONTRACTPRODUCT
  ADD CONSTRAINT ERP_I_CONTRACTPRODUCT_SIZE
  CHECK ((SIZECODE = '*' AND TYPEOFMERCHANDISE = 2) OR (SIZECODE <> '*'));
ALTER TABLE ERP_I_CONTRACTPRODUCT
  ADD CONSTRAINT ERP_I_CONTPROD_CHSPWEEK_CHK
  CHECK ((TYPEOFMERCHANDISE = 2 AND CHANGESPERWEEK = 'Y' AND BILLINGMETHOD IN (1,5)) OR CHANGESPERWEEK = 'N');
ALTER TABLE ERP_I_CONTRACTPRODUCT
  ADD CONSTRAINT ERP_I_CONTPROD_DEEDELIVERYSCHM
  CHECK ((DEEDELIVERYSCHEME IS NULL) OR (DEEDELIVERYSCHEME IS NOT NULL AND DELAYEDEVENEXCHANGE = 7));
ALTER TABLE ERP_I_CONTRACTPRODUCT
  ADD CONSTRAINT ERP_I_CONTPROD_EXCHANGE_CHK
  CHECK ((DELAYEDEVENEXCHANGE > 0 AND STRAIGHTEVENEXCHANGE = 'N') OR (DELAYEDEVENEXCHANGE = 0));
ALTER TABLE ERP_I_CONTRACTPRODUCT
  ADD CONSTRAINT ERP_I_CONTPROD_DATES_CHK
  CHECK (STARTDATE <= ENDDATE);
ALTER TABLE ERP_I_CONTRACTPRODUCT
  ADD CONSTRAINT ERP_I_CONTPROD_INVBILLING_CHK
  CHECK ((INVENTORYBILLINGTYPE = 0 AND BILLINGMETHOD <> 3) OR (BILLINGMETHOD=3 AND INVENTORYBILLINGTYPE IN (0, 1, 2, 3, 4)));
ALTER TABLE ERP_I_CONTRACTPRODUCT
  ADD CONSTRAINT ERP_I_CONTPROD_MINIMFREQ_CHECK
  CHECK ((MINIMUMFREQUENCY <> 5) OR (MINIMUMFREQUENCY=5 AND DELAYEDEVENEXCHANGE = 7));
ALTER TABLE ERP_I_CONTRACTPRODUCT
  ADD CONSTRAINT ERP_I_PRODCONT_MINIMUM_CHECK
  CHECK ((((MINIMUMQUANTITY = 0 AND MINIMUMTURNAROUND >= 0) OR
             (MINIMUMQUANTITY >= 0 AND MINIMUMTURNAROUND = 0)) AND BILLINGMETHOD = 2) OR
             BILLINGMETHOD <> 2);
ALTER TABLE ERP_I_CONTRACTPRODUCT
  ADD CONSTRAINT ERP_I_PRODCONT_BILLMETHOD2
  CHECK (SIZECODE <> '*' OR BILLINGMETHOD <>2);
  
-------------- ERP_I_CONTRACTPRODUCT_LOG -------------- 
CREATE TABLE ERP_I_CONTRACTPRODUCT_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID17 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL, 
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);
  
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTPRODUCT_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
  
ALTER TABLE ERP_I_CONTRACTPRODUCT_LOG
  ADD CONSTRAINT PK_ERP_I_CONTRACTPRODUCT_LOG PRIMARY KEY (TRANSFER_ID);    

CREATE INDEX XIE1ERP_I_CONTRACTPRODUCT_LOG ON ERP_I_CONTRACTPRODUCT_LOG (TIMESTAMP ASC);

ALTER TABLE ERP_I_CONTRACTPRODUCT_LOG
  ADD CONSTRAINT FK_ERP_I_CONTRACTPRODUCT_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CONTRACTPRODUCT (TRANSFER_ID) ON DELETE CASCADE;

-------------- ERP_I_PRICELIST -------------- 
ALTER TABLE ERP_I_PRICELIST DROP CONSTRAINT CHK_PRICETYPE_VALIDATION1;
ALTER TABLE ERP_I_PRICELIST DROP CONSTRAINT CHK_ERP_I_PRICELIST_PROD;
ALTER TABLE ERP_I_PRICELIST DROP CONSTRAINT CHK_ERP_I_PRICELISTT_SIZE;
ALTER TABLE ERP_I_PRICELIST DROP CONSTRAINT CHK_ERP_I_PRICELIST_FINISHNGMT;
ALTER TABLE ERP_I_PRICELIST DROP CONSTRAINT CHK_ERP_I_PRICELIST_RENTPRICTP;
ALTER TABLE ERP_I_PRICELIST DROP CONSTRAINT WASHPRICETYPE_VALID1;
ALTER TABLE ERP_I_PRICELIST DROP CONSTRAINT CHK_ERP_I_PRICELIST_VOLUMEPRC;
ALTER TABLE ERP_I_PRICELIST DROP CONSTRAINT CHK_ERP_I_PRICELIST_PC;
ALTER TABLE ERP_I_PRICELIST DROP CONSTRAINT CHK_PRICETYPE_VAL_SOIL;
ALTER TABLE ERP_I_PRICELIST DROP CONSTRAINT CHK_PRICETYPE_VAL_RENTPRICETP;
ALTER TABLE ERP_I_PRICELIST DROP CONSTRAINT CHK_PRICETYPE_VAL_WASHPRICETP;

CREATE TABLE ERP_I_PRICELIST
( TRANSFER_ID          NUMBER NOT NULL,
  TRANSFER_STATUS      NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID18 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  PRICELISTCODE        VARCHAR2(15) NOT NULL,
  PRICELISTTYPE        NUMBER NOT NULL CONSTRAINT CHK_PRICETYPE_VALIDATION1 CHECK (PRICELISTTYPE BETWEEN 1 AND 12),
  PRICELISTDESCRIPTION VARCHAR2(40) NOT NULL,
  CURRENCYCODE         VARCHAR2(3) NOT NULL,
  PRODUCTCODE          VARCHAR2(12) NULL CONSTRAINT CHK_ERP_I_PRICELIST_PROD CHECK (PRODUCTCODE=UPPER(TRIM(PRODUCTCODE))),
  SIZECODE             VARCHAR2(12) NULL CONSTRAINT CHK_ERP_I_PRICELISTT_SIZE CHECK (SIZECODE IS NULL OR SIZECODE = UPPER(TRIM(SIZECODE))),
  FINISHINGMETHODCODE  VARCHAR2(6) NULL CONSTRAINT CHK_ERP_I_PRICELIST_FINISHNGMT CHECK (FINISHINGMETHODCODE IS NULL OR FINISHINGMETHODCODE = UPPER(RTRIM(LTRIM(FINISHINGMETHODCODE)))),
  WEIGHINGCATEGORYCODE VARCHAR2(6) NULL,
  REPAIRTYPECODE       VARCHAR2(6) NULL,
  RENTPRICETYPE        NUMBER DEFAULT 1 NULL CONSTRAINT CHK_ERP_I_PRICELIST_RENTPRICTP CHECK (RENTPRICETYPE BETWEEN 1 AND 10),
  RENTCHANGESPERWEEK   NUMBER NULL,
  PRICE                NUMBER(15,6) NOT NULL,
  PRICEPERMINUTE       NUMBER(15,6) NULL,
  STARTDATEPRICE       DATE DEFAULT TRUNC(SYSDATE)-1 NOT NULL,
  WASHPRICETYPE        NUMBER DEFAULT 1 NULL CONSTRAINT WASHPRICETYPE_VALID1 CHECK (WASHPRICETYPE IN (1,2,4,5)),
  CHAINCODE            VARCHAR2(6) NULL,
  PRICEINDEXCODE       VARCHAR2(6) NULL,
  PRICEUPDATELOCKDATE  DATE NULL,
  VOLUMEPRICE          NUMBER(6) DEFAULT 1 NOT NULL CONSTRAINT CHK_ERP_I_PRICELIST_VOLUMEPRC CHECK (VOLUMEPRICE BETWEEN 1 AND 999999),
  SPECIALCHARGECODE    VARCHAR2(6) NULL,
  PERCENTAGE           NUMBER NULL,
  PRICECOMPONENT       NUMBER NULL CONSTRAINT CHK_ERP_I_PRICELIST_PC CHECK (PRICECOMPONENT IN (1,2,3,4)),
  SOIL 				   VARCHAR2(1) DEFAULT 'Y' NULL CONSTRAINT CHK_PRICETYPE_VAL_SOIL CHECK (SOIL IN ('Y', 'N')));

COMMENT ON COLUMN ERP_I_CONTRACT.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly'; 
COMMENT ON COLUMN ERP_I_PRICELIST.PRICELISTTYPE
  IS '1 = Wash 2 = Rent 3 = Replacement 4 = Sales 5 = Weight 6 = Repair 7 = Emblem 8 = Emblem Template 9 = Garment Insurance 10 = Special Charge';
COMMENT ON COLUMN ERP_I_PRICELIST.FINISHINGMETHODCODE
  IS 'For 1,2 Obligated.';
COMMENT ON COLUMN ERP_I_PRICELIST.WEIGHINGCATEGORYCODE
  IS 'For 5=Weight obligated';
COMMENT ON COLUMN ERP_I_PRICELIST.REPAIRTYPECODE
  IS 'For 6=Repair obligated';
COMMENT ON COLUMN ERP_I_PRICELIST.RENTPRICETYPE
  IS 'For 2=Rent obligated.
Possible values 1=Piece price
2=Changes/Wk
3=Overuse price
4=Per Frequency
5=Under use Price
6=Lease
7=Split price
8=Per Invoice Frequency
10=Loan';
COMMENT ON COLUMN ERP_I_PRICELIST.RENTCHANGESPERWEEK
  IS 'Obligated when J=2 or 4
If 2 then number of changes
If 4 then frequency in weeks';
COMMENT ON COLUMN ERP_I_PRICELIST.PRICEPERMINUTE
  IS 'Can only be used for 6.';
COMMENT ON COLUMN ERP_I_PRICELIST.WASHPRICETYPE
  IS 'For 1=Wash obligated. Possible values 1 = All items
2 = COG items only
4 = Split price
5 = Heavy Soil';
COMMENT ON COLUMN ERP_I_PRICELIST.CHAINCODE
  IS 'Optional chain code that is linked to this pricelist';
COMMENT ON COLUMN ERP_I_PRICELIST.PRICEINDEXCODE
  IS 'Optional price index code that is linked to this pricelist';
COMMENT ON COLUMN ERP_I_PRICELIST.PRICEUPDATELOCKDATE
  IS 'Optional price lock date code that is linked to this pricelist';
COMMENT ON COLUMN ERP_I_PRICELIST.VOLUMEPRICE
  IS 'Related to the Salesprice, and only used when
there is a Salesprice, then the default value is 1';
COMMENT ON COLUMN ERP_I_PRICELIST.SPECIALCHARGECODE
  IS 'Must be filled when PRICELISTTYPE = 10';
COMMENT ON COLUMN ERP_I_PRICELIST.PERCENTAGE
  IS 'Will only be used when PRICELISTTYPE = 10 and when the special charge is of the type Percentage';
COMMENT ON COLUMN ERP_I_PRICELIST.PRICECOMPONENT
  IS '1 = Garment Maintenance Protection (GMP)
2 = Garment Setup Protection (GSP)
3 = Garment Loss Protection (GLP)
4 = Garment Repair Protection (GRP)';
COMMENT ON COLUMN ERP_I_PRICELIST.SOIL
  IS 'Y=Soil N=Clean';
  
ALTER TABLE ERP_I_PRICELIST
  ADD CONSTRAINT PK_ERP_I_PRICELIST PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_I_PRICELIST ON ERP_I_PRICELIST (PRICELISTCODE ASC, PRICELISTTYPE ASC, PRODUCTCODE ASC, SIZECODE ASC, FINISHINGMETHODCODE ASC, WEIGHINGCATEGORYCODE ASC, REPAIRTYPECODE ASC, RENTPRICETYPE ASC, RENTCHANGESPERWEEK ASC, STARTDATEPRICE ASC, WASHPRICETYPE ASC, SPECIALCHARGECODE ASC, PRICECOMPONENT ASC);

CREATE INDEX XIE21ERP_I_PRICELIST ON ERP_I_PRICELIST (PRODUCTCODE ASC);
CREATE INDEX XIF1ERP_I_PRICELIST ON ERP_I_PRICELIST (CURRENCYCODE ASC);

ALTER TABLE ERP_I_PRICELIST
  ADD CONSTRAINT ERP_I_PRICELIST_CURRENCY FOREIGN KEY (CURRENCYCODE)
  REFERENCES CURRENCY (CODE);

ALTER TABLE ERP_I_PRICELIST
  ADD CONSTRAINT CHK_PRICETYPE_VAL_RENTPRICETP
  CHECK (PRICELISTTYPE <> 2 OR (RENTPRICETYPE BETWEEN 1 AND 10 AND PRICELISTTYPE = 2));
ALTER TABLE ERP_I_PRICELIST
  ADD CONSTRAINT CHK_PRICETYPE_VAL_WASHPRICETP
  CHECK (PRICELISTTYPE <> 1 OR (WASHPRICETYPE BETWEEN 1 AND 5 AND PRICELISTTYPE = 1));  
  
ALTER TABLE ERP_I_PRICELIST DROP CONSTRAINT ERP_I_PRICELIST_PRODUCTCODE;
  
-------------- ERP_I_PRICELIST_LOG --------------
CREATE TABLE ERP_I_PRICELIST_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT TRANSFER_STATUS_VALID19 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);
  
COMMENT ON COLUMN ERP_I_PRICELIST_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_PRICELIST_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_I_PRICELIST_LOG
  ADD CONSTRAINT PK_ERP_I_PRICELIST_LOG PRIMARY KEY (TRANSFER_ID);
 
CREATE INDEX XIE1ERP_I_PRICELIST_LOG ON ERP_I_PRICELIST_LOG (TIMESTAMP ASC);

ALTER TABLE ERP_I_PRICELIST_LOG
  ADD CONSTRAINT FK_ERP_I_PRICELIST_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_PRICELIST (TRANSFER_ID) ON DELETE CASCADE; 

-------------- ERP_I_CONTRACTPURCHASEORDER --------------
ALTER TABLE ERP_I_CONTRACTPURCHASEORDER DROP CONSTRAINT CHK_ERP_I_CONTPO_WARNPERCENT;
ALTER TABLE ERP_I_CONTRACTPURCHASEORDER DROP CONSTRAINT CHK_ERP_I_CONTPO_WARNDAYS;
ALTER TABLE ERP_I_CONTRACTPURCHASEORDER DROP CONSTRAINT CHK_ERP_I_CONTPO_CLOSEACTION;
ALTER TABLE ERP_I_CONTRACTPURCHASEORDER DROP CONSTRAINT CHK_ERP_I_CONTPO_MAXPERCENTAGE;
ALTER TABLE ERP_I_CONTRACTPURCHASEORDER DROP CONSTRAINT CHK_ERP_I_CONTPO_INVLINETYPEPO;
ALTER TABLE ERP_I_CONTRACTPURCHASEORDER DROP CONSTRAINT CHK_ERP_I_CONTPO_MANUALPO;
ALTER TABLE ERP_I_CONTRACTPURCHASEORDER DROP CONSTRAINT CHK_ERP_I_CONTPO_SINGLECUSTPO;

CREATE TABLE ERP_I_CONTRACTPURCHASEORDER
( TRANSFER_ID           NUMBER NOT NULL,
  TRANSFER_STATUS       NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID20 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  CONTRACTCODE          VARCHAR2(15) NOT NULL,
  PURCHASEORDERNUMBER   VARCHAR2(40) NOT NULL,
  STARTDATE             DATE NOT NULL,
  EXPIRATIONDATE        DATE NOT NULL,
  VALUE                 NUMBER DEFAULT 0 NOT NULL,
  INVOICEDAMOUNT        NUMBER DEFAULT 0 NOT NULL,
  WARNPERCENTAGE        NUMBER(5,2) DEFAULT 0.00 NOT NULL CONSTRAINT CHK_ERP_I_CONTPO_WARNPERCENT CHECK (WARNPERCENTAGE BETWEEN 0.00 AND 100.00),
  WARNDAYS              NUMBER(4) DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_CONTPO_WARNDAYS CHECK (WARNDAYS BETWEEN 0 AND 9999),
  CLOSEACTION           NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_ERP_I_CONTPO_CLOSEACTION CHECK (CLOSEACTION IN (0, 1, 2)),
  MAXPERCENTAGE         NUMBER(5,2) DEFAULT 100.00 NOT NULL CONSTRAINT CHK_ERP_I_CONTPO_MAXPERCENTAGE CHECK (MAXPERCENTAGE BETWEEN 100.00 AND 999.00),
  INVOICELINETYPESPERPO VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_ERP_I_CONTPO_INVLINETYPEPO CHECK (INVOICELINETYPESPERPO IN ('Y', 'N')),
  BUSINESSUNIT          VARCHAR2(6) NULL,
  MANUALPO              VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_ERP_I_CONTPO_MANUALPO CHECK (MANUALPO IN ('Y', 'N')),
  SINGLECUSTOMERPO      VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_ERP_I_CONTPO_SINGLECUSTPO CHECK (SINGLECUSTOMERPO IN ('Y','N')));

COMMENT ON COLUMN ERP_I_CONTRACTPURCHASEORDER.CLOSEACTION
  IS '0 = PO is closed when value is reached 1 = Continue until expired (exceed PO value) 2 = Continue until expired or maximum percentage';
COMMENT ON COLUMN ERP_I_CONTRACTPURCHASEORDER.SINGLECUSTOMERPO
  IS 'WHEN SET TO Y THERE CAN ONLY BE 1 ROW AT MOST IN CONTRACTPOCUSTOMER TABLE LINKED TO THIS CONTRACTPURCHASEORDER';

ALTER TABLE ERP_I_CONTRACTPURCHASEORDER
  ADD CONSTRAINT PK_ERP_I_CONTRACTPO PRIMARY KEY (TRANSFER_ID);

-------------- ERP_I_CONTRPURCHASEORDLINE --------------
CREATE TABLE ERP_I_CONTRPURCHASEORDLINE
( TRANSFER_ID            NUMBER NOT NULL,
  TRANSFER_STATUS        NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID21 CHECK (TRANSFER_STATUS IN (0,1)),
  CONTRACTCODE           VARCHAR2(15) NOT NULL,
  PURCHASEORDERNUMBER    VARCHAR2(40) NOT NULL,
  LINENUMBER             NUMBER NOT NULL,
  CUSTOMERNUMBER         NUMBER NOT NULL,
  PRODUCTCODE            VARCHAR2(6) NOT NULL,
  SIZECODE               VARCHAR2(6) NOT NULL,
  FINISHINGMETHOD        VARCHAR2(6) NULL,
  WEARERNUMBER           NUMBER NULL,
  WEAREREMPLOYMENTNUMBER NUMBER DEFAULT 1 NULL,
  QUANTITY               NUMBER NULL,
  PRICE                  NUMBER NULL);

CREATE UNIQUE INDEX XAK1ERP_I_CONTRPURCHASEORDLINE ON ERP_I_CONTRPURCHASEORDLINE (TRANSFER_ID ASC, CONTRACTCODE ASC, PURCHASEORDERNUMBER ASC, CUSTOMERNUMBER ASC, PRODUCTCODE ASC, SIZECODE ASC, FINISHINGMETHOD ASC, WEARERNUMBER ASC, WEAREREMPLOYMENTNUMBER ASC);

CREATE INDEX XIF1ERP_I_CONTRPURCHASEORDLINE ON ERP_I_CONTRPURCHASEORDLINE (TRANSFER_ID ASC);
ALTER TABLE ERP_I_CONTRPURCHASEORDLINE
  ADD CONSTRAINT FK_ERP_I_CONTRPURCHASEORDLINE FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CONTRACTPURCHASEORDER (TRANSFER_ID) ON DELETE CASCADE;
 
-------------- ERP_I_CONTRPURCHASEORDER_LOG --------------
CREATE TABLE ERP_I_CONTRPURCHASEORDER_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID22 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);

COMMENT ON COLUMN ERP_I_CONTRPURCHASEORDER_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRPURCHASEORDER_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_I_CONTRPURCHASEORDER_LOG
  ADD CONSTRAINT PK_ERP_I_CONTRPURCHASEO_LOG PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_I_CONTRPURCHASEO_LOGG ON ERP_I_CONTRPURCHASEORDER_LOG (TIMESTAMP ASC);

ALTER TABLE ERP_I_CONTRPURCHASEORDER_LOG
  ADD CONSTRAINT FK_ERP_I_CONTRPURCHASEO_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CONTRACTPURCHASEORDER (TRANSFER_ID) ON DELETE CASCADE; 
  
-------------- ERP_I_CONTRACTPOCUSTOMER --------------
ALTER TABLE ERP_I_CONTRACTPOCUSTOMER DROP CONSTRAINT CHK_ERP_I_CONTRACTPOCUST_DATES;

CREATE TABLE ERP_I_CONTRACTPOCUSTOMER
( TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID23 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  CONTRACTCODE        VARCHAR2(15) NOT NULL,
  PURCHASEORDERNUMBER VARCHAR2(40) NOT NULL,
  CUSTOMERNUMBER      NUMBER NOT NULL,
  CUS_STARTDATE       DATE DEFAULT TRUNC(SYSDATE)-1 NOT NULL,
  STARTDATE           DATE NOT NULL,
  ENDDATE             DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL);
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN ERP_I_CONTRACTPOCUSTOMER.CONTRACTCODE
  IS 'CONTRACT CODE FOR CUSTOMER AND PO';
COMMENT ON COLUMN ERP_I_CONTRACTPOCUSTOMER.CUS_STARTDATE
  IS 'CUSTOMER CONTRACT START DATE, TO FIND THE RIGHT CUSTOMER CONTRACT';
COMMENT ON COLUMN ERP_I_CONTRACTPOCUSTOMER.STARTDATE
  IS 'STARTDATE CONTRACTPOCUSTOMER';
COMMENT ON COLUMN ERP_I_CONTRACTPOCUSTOMER.ENDDATE
  IS 'ENDDATE CONTRACTPOCUSTOMER';
  
ALTER TABLE ERP_I_CONTRACTPOCUSTOMER
  ADD CONSTRAINT PK_ERP_I_CONTRACTPOCUSTOMER PRIMARY KEY (TRANSFER_ID);

CREATE INDEX XIE1ERP_I_CONTRACTPOCUSTOMER ON ERP_I_CONTRACTPOCUSTOMER (CONTRACTCODE ASC, PURCHASEORDERNUMBER ASC, CUSTOMERNUMBER ASC, CUS_STARTDATE ASC);

ALTER TABLE ERP_I_CONTRACTPOCUSTOMER
  ADD CONSTRAINT CHK_ERP_I_CONTRACTPOCUST_DATES
  CHECK (STARTDATE <= ENDDATE);  

--section break for pack_Script

-------------- ERP_I_CONTRACTPOCUSTOMER_LOG --------------
CREATE TABLE ERP_I_CONTRACTPOCUSTOMER_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID24 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL );

COMMENT ON COLUMN ERP_I_CONTRACTPOCUSTOMER_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTPOCUSTOMER_LOG.PROCESS_STATUS
  IS '0 = PROCESSED CORRECTLY, 1 = PROCESS STOPPED WITH ERRORS';
  
ALTER TABLE ERP_I_CONTRACTPOCUSTOMER_LOG
  ADD CONSTRAINT PK_ERP_I_CONTRACTPOCUST_LOG PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_I_CONTRACTPOCUST_LOG ON ERP_I_CONTRACTPOCUSTOMER_LOG (TIMESTAMP ASC);

ALTER TABLE ERP_I_CONTRACTPOCUSTOMER_LOG
  ADD CONSTRAINT FK_ERP_I_CONTRACTPOCUST_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CONTRACTPOCUSTOMER (TRANSFER_ID) ON DELETE CASCADE;   
  
-------------- ERP_I_RECEIVE_POL --------------
ALTER TABLE ERP_I_RECEIVE_POL ADD (MODIFICATIONCODE1 VARCHAR2(10) NULL,
								   MODIFICATIONCODE2 VARCHAR2(10) NULL,
								   MODIFICATIONCODE3 VARCHAR2(10) NULL);

-------------- ERP_I_STOCKDEFINITIONPERITEM --------------
ALTER TABLE ERP_I_STOCKDEFINITIONPERITEM DROP CONSTRAINT CHK_ERP_I_STCKDEFPERITM_CUSTWN;

CREATE TABLE ERP_I_STOCKDEFINITIONPERITEM
( TRANSFER_ID           NUMBER NOT NULL,
  TRANSFER_STATUS       NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID25 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  STOCKROOMCODE         VARCHAR2(12) NOT NULL,
  CUSTOMERNUMBER        NUMBER NULL,
  CUSTOMEROWNED         VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_ERP_I_STCKDEFPERITM_CUSTWN CHECK (CUSTOMEROWNED IN ('Y','N')),
  PRODUCTCODE           VARCHAR2(12) NOT NULL,
  SIZECODE              VARCHAR2(12) DEFAULT '-' NOT NULL,
  MINQTYNEW             NUMBER DEFAULT 0 NOT NULL,
  MINQTYUSED            NUMBER DEFAULT 0 NOT NULL,
  MAXQTYNEW             NUMBER DEFAULT 999 NOT NULL,
  MAXQTYUSED            NUMBER DEFAULT 999 NOT NULL,
  DEFAULTSUPPLIERNUMBER NUMBER NOT NULL);

COMMENT ON COLUMN ERP_I_STOCKDEFINITIONPERITEM.CUSTOMEROWNED
  IS 'POSSIBLE VALUES ''N'',''Y''';

ALTER TABLE ERP_I_STOCKDEFINITIONPERITEM
  ADD CONSTRAINT PK_ERP_I_STOCKDEFPERITEM PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_I_STOCKDEFPERITEM ON ERP_I_STOCKDEFINITIONPERITEM (TRANSFER_STATUS ASC);
  
-------------- ERP_I_STOCKDEFPERITEM_LOG --------------
CREATE TABLE ERP_I_STOCKDEFPERITEM_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID26 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);

ALTER TABLE ERP_I_STOCKDEFPERITEM_LOG
  ADD CONSTRAINT PK_ERP_I_STOCKDEFPERITEM_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_STOCKDEFPERITEM_LOG
  ADD CONSTRAINT FK_ERP_I_STOCKDEFPERITEM_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_STOCKDEFINITIONPERITEM (TRANSFER_ID) ON DELETE CASCADE;

-------------- ERP_I_DC_ISSUE --------------
CREATE TABLE ERP_I_DC_ISSUE
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID27 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  STOCKROOMCODE   VARCHAR2(12) NOT NULL,
  WORKORDERNUMBER NUMBER NOT NULL,
  CUSTOMERNUMBER  NUMBER NULL,
  PRODUCTCODE     VARCHAR2(12) NOT NULL,
  SIZECODE        VARCHAR2(12) NOT NULL,
  QUANTITY        NUMBER NOT NULL,
  DC_VALUE        NUMBER NOT NULL,
  PRIMARYID       VARCHAR2(24) NULL,
  ASSIGNDATE      DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
  REASONCODE      VARCHAR2(6) NOT NULL,
  QUALITYGRADE    VARCHAR2(1) NOT NULL);

COMMENT ON COLUMN ERP_I_DC_ISSUE.STOCKROOMCODE
  IS 'ITS MANAGEDBYBU_ID IS THE WORKORDER BUSINESSUNIT_ID';
COMMENT ON COLUMN ERP_I_DC_ISSUE.QUALITYGRADE
  IS 'REQUESTEDGRADE';

ALTER TABLE ERP_I_DC_ISSUE
  ADD CONSTRAINT PK_ERP_I_DC_ISSUE PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_I_DC_ISSUE ON ERP_I_DC_ISSUE (TRANSFER_STATUS ASC);

CREATE INDEX XIF1ERP_I_DC_ISSUE ON ERP_I_DC_ISSUE (PRODUCTCODE ASC);
CREATE INDEX XIF2ERP_I_DC_ISSUE ON ERP_I_DC_ISSUE (SIZECODE ASC);
CREATE INDEX XIF3ERP_I_DC_ISSUE ON ERP_I_DC_ISSUE (STOCKROOMCODE ASC);

ALTER TABLE ERP_I_DC_ISSUE
  ADD CONSTRAINT FK_ERP_DC_ISSUE_PRODUCT FOREIGN KEY (PRODUCTCODE)
  REFERENCES PRODUCT (CODE);
ALTER TABLE ERP_I_DC_ISSUE
  ADD CONSTRAINT FK_ERP_DC_ISSUE_SIZE FOREIGN KEY (SIZECODE)
  REFERENCES SIZEDEFINITION (CODE);
ALTER TABLE ERP_I_DC_ISSUE
  ADD CONSTRAINT FK_ERP_DC_ISSUE_STOCKROOM FOREIGN KEY (STOCKROOMCODE)
  REFERENCES STOCKROOM (CODE);

-------------- ERP_I_DC_ISSUE_LOG --------------
CREATE TABLE ERP_I_DC_ISSUE_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID28 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL,
  REASONCODE      VARCHAR2(6) NOT NULL,
  QUALITYGRADE    VARCHAR2(1) NOT NULL);

COMMENT ON COLUMN ERP_I_DC_ISSUE.QUALITYGRADE IS 'REQUESTEDGRADE';
  
ALTER TABLE ERP_I_DC_ISSUE_LOG
  ADD CONSTRAINT PK_ERP_I_DC_ISSUE_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_DC_ISSUE_LOG
  ADD CONSTRAINT FK_ERP_I_DC_ISSUE_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_DC_ISSUE (TRANSFER_ID) ON DELETE CASCADE;
  
-------------- ERP_I_LEDGERPRODUCT --------------
CREATE TABLE ERP_I_LEDGERPRODUCT
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID29 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  PRODUCTCODE     VARCHAR2(12) NOT NULL,
  INSERVACCOUNT   VARCHAR2(12) NOT NULL,
  SHELFSTACCOUNT  VARCHAR2(12) NOT NULL,
  COSTFGACCOUNT   VARCHAR2(12) NOT NULL,
  OBSACCOUNT      VARCHAR2(12) NOT NULL);

COMMENT ON COLUMN ERP_I_LEDGERPRODUCT.PRODUCTCODE
  IS 'MUST BE AN EXISTING PRODUCT.CODE';
COMMENT ON COLUMN ERP_I_LEDGERPRODUCT.INSERVACCOUNT
  IS 'FOR LEDGERACCNTTYPE INSERV';
COMMENT ON COLUMN ERP_I_LEDGERPRODUCT.SHELFSTACCOUNT
  IS 'FOR LEDGERACCNTTYPE SHLFST';
COMMENT ON COLUMN ERP_I_LEDGERPRODUCT.COSTFGACCOUNT
  IS 'FOR LEDGERACCNTTYPE COSTFG';
COMMENT ON COLUMN ERP_I_LEDGERPRODUCT.OBSACCOUNT
  IS 'FOR LEDGERACCNTTYPE OBS';

ALTER TABLE ERP_I_LEDGERPRODUCT
  ADD CONSTRAINT PK_ERP_I_LEDGERPRODUCT PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIF1ERP_I_LEDGERPRODUCT ON ERP_I_LEDGERPRODUCT (PRODUCTCODE ASC);

ALTER TABLE ERP_I_LEDGERPRODUCT
  ADD CONSTRAINT FK_ERP_I_LEDGERPRODUCT_PROD FOREIGN KEY (PRODUCTCODE)
  REFERENCES PRODUCT (CODE); 

-------------- ERP_I_LEDGERPRODUCT_LOG --------------
CREATE TABLE ERP_I_LEDGERPRODUCT_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID30 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);

ALTER TABLE ERP_I_LEDGERPRODUCT_LOG
  ADD CONSTRAINT PK_ERP_I_LEDGERPRODUCT_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_LEDGERPRODUCT_LOG
  ADD CONSTRAINT FK_ERP_I_LEDGERPRODUCT_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_LEDGERPRODUCT (TRANSFER_ID) ON DELETE CASCADE;

-------------- ERP_I_CONTACTPERSON --------------
ALTER TABLE ERP_I_CONTACTPERSON DROP CONSTRAINT CHK_ERP_I_CONTACTPERS_INVCNTP;
ALTER TABLE ERP_I_CONTACTPERSON DROP CONSTRAINT CHK_ERP_I_CONTACTPERS_ACTIVE;

CREATE TABLE ERP_I_CONTACTPERSON
( TRANSFER_ID               NUMBER NOT NULL,
  TRANSFER_STATUS           NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID31 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  CUSTOMERNUMBER            NUMBER NOT NULL,
  CONTACTPERSONNUMBER       NUMBER NOT NULL,
  CONTACTPERSONFUNCTIONCODE VARCHAR2(6) NOT NULL,
  NAME                      VARCHAR2(40) NOT NULL,
  PHONE                     VARCHAR2(15) NULL,
  EMAIL                     VARCHAR2(40) NULL,
  MOBILEPHONE               VARCHAR2(15) NULL,
  INVOICECONTACTPERSON      VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_ERP_I_CONTACTPERS_INVCNTP CHECK (INVOICECONTACTPERSON IN ('Y','N')),
  FAX                       VARCHAR2(15) NULL,
  ACTIVE                    VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_ERP_I_CONTACTPERS_ACTIVE CHECK (ACTIVE IN ('Y','N')));

COMMENT ON COLUMN ERP_I_CONTACTPERSON.INVOICECONTACTPERSON
  IS 'THIS CONTACT PERSON INFO WILL BE PRINTED ON INVOICE';

ALTER TABLE ERP_I_CONTACTPERSON
  ADD CONSTRAINT PK_ERP_I_CONTACTPERSON PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_I_CONTACTPERSON ON ERP_I_CONTACTPERSON (CUSTOMERNUMBER ASC, CONTACTPERSONNUMBER ASC);

-------------- ERP_I_CONTACTPERSON_LOG --------------
CREATE TABLE ERP_I_CONTACTPERSON_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID32 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);

ALTER TABLE ERP_I_CONTACTPERSON_LOG
  ADD CONSTRAINT PK_ERP_I_CONTACTPERSON_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_CONTACTPERSON_LOG
  ADD CONSTRAINT FK_ERP_I_CONTACTPERSON_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CONTACTPERSON (TRANSFER_ID) ON DELETE CASCADE;

-------------- ERP_I_AR_BALANCES_LOG --------------
CREATE TABLE ERP_I_AR_BALANCES_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID34 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);

ALTER TABLE ERP_I_AR_BALANCES_LOG
  ADD CONSTRAINT PK_ERP_I_AR_BALANCES_LOG PRIMARY KEY (TRANSFER_ID);