--ABS-98598
CREATE TABLE ERP_I_PRODUCTLISTPERCUSTOMER
(
TRANSFER_ID           NUMBER NOT NULL,
TRANSFER_STATUS       NUMBER DEFAULT 0 NOT NULL 
 CONSTRAINT CHK_ERP_PRODLISTPCUST_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
CUSTOMERNUMBER        NUMBER NOT NULL,
PRODUCTCODE           VARCHAR2(12) NOT NULL,
SIZECODE              VARCHAR2(12) NOT NULL,
FINISHINGMETHODCODE   VARCHAR2(6) NULL,
DATEACTIVE            DATE NULL,
DATEINACTIVE          DATE NULL,
CODEUNIQUELY          VARCHAR2(1) NULL,
SOILWEIGHT            NUMBER(6,2) NULL,
ONSITEINVENTORYCOUNT  VARCHAR2(1) NULL,
BILLINGMETHOD         NUMBER NOT NULL,
CIRCULATINGINVENTORY  NUMBER NULL,
CHANGESPERWEEK        VARCHAR2(1) NOT NULL,
FIXEDQUANTITY         NUMBER NOT NULL,
CHARGEOVERUSE         VARCHAR2(1) NOT NULL,
MINIMUMFREQUENCY      NUMBER NOT NULL,
MINIMUMTURNAROUND     NUMBER NULL,
MINIMUMQUANTITY       NUMBER NULL,
INVENTORYBILLINGTYPE  NUMBER NULL,
PERCENTAFTERDEPRECIATION NUMBER NULL,
SOILCOUNTMETHOD       NUMBER NULL,
CLEANCOUNT            VARCHAR2(1) NULL,
SRCMETHOD             NUMBER NULL,
SRCFREQUENCY          NUMBER NULL,
SRCPERCENTAGESOILCOUNT NUMBER NULL,
SRCPERCENTAGEINVENTORY NUMBER NULL,
SRCQUANTITY           NUMBER NULL,
DELIVERREPLACEMENTQTY VARCHAR2(1) NULL,
CHARGESTOCKROOMINVENTORY VARCHAR2(1) NULL,
QTYFREEOFCHARGE       NUMBER NULL,
CUSTOMCODE            VARCHAR2(16) NULL,
CUSTOMDESCRIPTION     VARCHAR2(30) NULL,
CUSTOMBUNDLESIZE      NUMBER NULL,
GAINCOMPETITORCODE    VARCHAR2(6) NULL,
GAINCOMPETITORNAME    VARCHAR2(30) NULL,
LOSTCOMPETITORCODE    VARCHAR2(6) NULL,
LOSTCOMPETITORNAME    VARCHAR2(30) NULL,
GAINREASONCODE        VARCHAR2(6) NULL,
GAINREASONDESCRIPTION VARCHAR2(30) NULL,
LOSTREASONCODE        VARCHAR2(6) NULL,
LOSTREASONDESCRIPTION VARCHAR2(30) NULL,
RESIDUALVALUESCHEMECODE    VARCHAR2(6) NULL,
DELAYEDEVENEXCHANGE   NUMBER NULL,
DELIVERYDAYMO         VARCHAR2(1) NULL,
DELIVERYDAYTU         VARCHAR2(1) NULL,
DELIVERYDAYWE         VARCHAR2(1) NULL,
DELIVERYDAYTH         VARCHAR2(1) NULL,
DELIVERYDAYFR         VARCHAR2(1) NULL,
DELIVERYDAYSA         VARCHAR2(1) NULL,
DELIVERYDAYSU         VARCHAR2(1) NULL,
QUANTITYRETURNED      NUMBER NULL,
PLPCEANNUMBER         NUMBER(4) NULL,
SPECIFICPUTUPTYPE     NUMBER NULL,
SALESEMPLOYEENUMBER   NUMBER NULL,
SALESEMPLOYEENAME     VARCHAR2(40) NULL,
RETURNSTOCKALLOWED    VARCHAR2(1) NULL,
STRAIGHTEVENEXCHANGE  VARCHAR2(1) NULL,
ISSUECUSTOMERRELATED  VARCHAR2(1) NULL,
DDEDELIVERYSCHEMECODE VARCHAR2(6) NULL,
REMARK                VARCHAR2(80) NULL,
LOCATION              VARCHAR2(6) NULL,
CHARGEWASHESONLYFORCOG NUMBER NULL,
RENTPRICEPERFREQUENCY VARCHAR2(1) NOT NULL,
PRICEUSAGE            NUMBER(1) NOT NULL,
EMBLEMTEMPLATE        VARCHAR2(6) NULL,
SRCCHARGERENT         NUMBER NULL,
TREATMENTCHECKSCHEMECODE VARCHAR2(6) NULL,
DELIVERYLABELLAYOUT   VARCHAR2(6) NULL,
SALESEMPLOYEENUMBER2  NUMBER NULL,
SALESEMPLOYEENAME2    VARCHAR2(40) NULL,
DEFAULTSTOCKROOM      VARCHAR2(12) NULL,
FIXEDSTOCKROOM        VARCHAR2(1) NULL,
ORDERVIAWEB           NUMBER(1) NULL,
FREEQTYFREQUENCY      NUMBER(1) NULL,
INTERNALCALCPERCENTAGELOG NUMBER(4,1) NULL,
INTERNALCALCPERCENTAGECOG NUMBER(4,1) NULL,
CHARGEEXCESS          NUMBER NULL,
BUDGETUSAGE           NUMBER NULL,
BUDGETCODE            VARCHAR2(6) NULL,
DEPOSITSTOCKALLOWED   VARCHAR2(1) NULL,
DEPOSITSTOCKCHARGEPERCENTAGE NUMBER NULL,
BM1FIXEDWEARERS       VARCHAR2(1) NULL,
STERILISATIONREQUIRED VARCHAR2(1) NULL,
MMPSPECIFICPERCENTAGE NUMBER NULL,
GARMENTSETUP          VARCHAR2(1) NULL,
GARMENTMAINTENANCE    VARCHAR2(1) NULL,
GARMENTLOSS           VARCHAR2(1) NULL,
GARMENTREPAIR         VARCHAR2(1) NULL,
CONTRACTCODE          VARCHAR2(15) NULL,
IMAGECARE             NUMBER NULL,
ALLOWRETURNTOSTOCK    NUMBER(1) NULL,
MBYOPPERCENTAGE       NUMBER NULL,
SEASONTYPECODE        VARCHAR2(6) NULL,
SEASONALRENT          NUMBER NULL,
CHARGEWASHINACTIVESEASON VARCHAR2(1) NULL,
AUTOSOILPERCENTAGE    NUMBER NULL,
LOSSCHARGEPERCENTAGE  NUMBER(5,2) NULL,
SRCHARGEPERCENTAGE    NUMBER(5,2) NULL,
DEFAULT_SUPPLIERNUMBER NUMBER NULL,
GARMENTLOSSPERCENTAGE NUMBER NULL,
GARMENTEMBLEM         VARCHAR2(1) NULL,
SPECIFICBULKPACKINGTYPE NUMBER NULL,
TIMESTAMP             DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.TRANSFER_STATUS
  IS  '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_I_PRODUCTLISTPERCUSTOMER
 ADD CONSTRAINT PK_ERP_I_PRODLISTPERCUST PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1ERP_I_PRODLISTPERCUST ON ERP_I_PRODUCTLISTPERCUSTOMER(TRANSFER_STATUS);
ALTER TABLE ERP_I_PRODUCTLISTPERCUSTOMER
  ADD CONSTRAINT UK_ERP_I_PRODLISTPERCUST UNIQUE (TRANSFER_ID, CUSTOMERNUMBER, PRODUCTCODE, SIZECODE,FINISHINGMETHODCODE);
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.sizecode
  IS  'Code of the size. If unknown use minus ‘-’.
Size star ‘*’ is only allowed for products with type of merchandise 2. 
Sizecode star ‘*’ is not allowed for billingmethod 2';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.dateinactive
  IS  '31/12/2099 if product is still serviced for this customer';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.onsiteinventorycount
  IS  'Y if on site inventory checks must be done for this item at this customer.';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.billingmethod
  IS  '1-Flat rated rent
2-Rent Based on Minimum
3-Circulating Inventory
4-Piece Rate Only
Billingmethod 1, 2 and 3 is NOT allowed for type of merchandize 3 (sales only items).
5-Lease';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.circulatinginventory
  IS  'Defines the currently active circulating inventory.
Only filled in case of :
Billingcode=1 and no changes/week or
Billingcode=2 or
Billingcode=3 and realquantity=N
Can be filled (not obligated) for Billingcode=4.
Note: Only fill it when customer does not have departments. Else export at lower level in delivery standards';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.changesperweek
  IS  'For billing method 1 Flat rated rent
Y if flat rated rent based on changes per week. 
For other billing methods put N
If type of merchandize<>2 (garments) then Y is NOT allowed.';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.fixedquantity
  IS  'For billing method 1 Flat rated rent.
Note: Only fill it when customer does not have departments. Else export at lower level in delivery standards';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.chargeoveruse
  IS  'For billing method 1 and changes per week=Y or for billing method 3 & real quantity=y';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.minimumfrequency
  IS  'For billing method 2 rent based on minimum.
1-Every Week
2-Every Visit
4-Every Delivery
5-Delivery scheme based ONLY possible when delayed even exchange = 7 ';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.minimumturnaround
  IS  'For billing method 2 rent based on minimum.
Percentage of Inventory defined as minimum. 0 if not applicable';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.minimumquantity
  IS  'For billing method 2 rent based on minimum.
Number of pieces defined as minimum. 0 if not applicable';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.inventorybillingtype
  IS  'Possible values are:
0 = Circulating inventory (default)
1 = Real quantity 
2 = Max inventory quantity
3 = Onsite inventory quantity
4 = Non-returned pool after n weeks
For Billing Method 2:
0 – Based on Circulating Inventory
1 – Based on Real Quantity
2 – Based on Max Inventory
(3, 4 not used)';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.percentafterdepreciation
  IS  'For billing method 3 and realquantity=Y this percentage is calculated after depreciation. 0..100.
0 for all other situations/methods.';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.soilcountmethod
  IS  '0 if not soil counted, else:
1-Manual
2-Auto Return assumed (for i.e. mats)
3-Weight Based';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.cleancount
  IS  'Y if the packing employee should count the items when packing. (i.e. for Customer owned goods) ';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.srcmethod
  IS  '0-No Systematic Replacement Charge.
1-Percentage of Soil Count,
2-Percentage of Circulating inventory.
3-Fixed Quantity';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.srcfrequency
  IS  'For SRCMETHOD 0 or 1:
0-Not applicable
For SRCMETHOD 2 or 3:
1-Every Delivery
2-Every Week First Delivery
3-Every Week Last Delivery';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.delayedevenexchange
  IS  '0=No delayed even exchange, but fixed
 delivery scheme.
1=Next Visit
2=Next week same day 
3=10 Day delay
4=Return route of route,
5 = 14 day delay basis
2	= 28 day delay basis
7 = According to delivery -> When used also fill in the new field DEEDeliveryschemecode (field CC)';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.specificputuptype
  IS  '0 = No Specific, inherit from Product
1 = Put up per customer (Packingnote)
2 = Bulk put up
3 = Bundle tag
4 = Bulk putup scan per route';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.rentpriceperfrequency
  IS  '0 = Both LOG and COG, same washprice (default)
1 = Both LOG and COG, distinct washprice
2 = LOG only
3 = COG only
4 = Sales only (BM1, BM3, BM4, BM5)';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUSTOMER.priceusage
  IS  '0 = Not applicable
1 = Split price
2 = Rent price only
3 = Wash price only';

CREATE TABLE ERP_I_PRODUCTLISTPERCUST_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_I_PRODLISTPCUST_LOG_V1 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_PRODUCTLISTPERCUST_LOG.TRANSFER_STATUS
  IS  '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_PRODUCTLISTPERCUST_LOG.PROCESS_STATUS
  IS  '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_PRODUCTLISTPERCUST_LOG
  ADD CONSTRAINT PK_ERP_I_PRODLISTPCUST_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_PRODUCTLISTPERCUST_LOG
  ADD CONSTRAINT FK_ERP_I_PRODLISTPCUST_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_PRODUCTLISTPERCUSTOMER(TRANSFER_ID) ON DELETE CASCADE;