-- ABS-131431
ALTER TABLE ERP_U_PLPC ADD
( SIZECODE              VARCHAR2(12) DEFAULT '-' NULL,
  FINISHINGMETHODCODE   VARCHAR2(6) DEFAULT '-' NULL,
  SRCHARGEPERCENTAGE    NUMBER DEFAULT 100 NULL,
  MMPSPECIFICPERCENTAGE NUMBER NULL,
  GARMENTSETUP          VARCHAR2(1) DEFAULT 'N' NULL,
  GARMENTMAINTENANCE    VARCHAR2(1) DEFAULT 'N' NULL,
  GARMENTLOSS           VARCHAR2(1) DEFAULT 'N' NULL,
  GARMENTLOSSPERCENTAGE NUMBER DEFAULT 100 NULL,
  GARMENTEMBLEM         VARCHAR2(1) DEFAULT 'N' NULL,
  MBYOPPERCENTAGE       NUMBER DEFAULT 100 NULL,
  MLPSPECIFICPERCENTAGE NUMBER DEFAULT 100 NULL,
  GARMENTREPAIR         VARCHAR2(1) DEFAULT 'N' NULL );
CREATE INDEX XIE2_ERP_U_PLPC ON ERP_U_PLPC(CUSTOMERNUMBER, PRODUCTCODE, SIZECODE, FINISHINGMETHODCODE);
ALTER TABLE ERP_U_PLPC
  ADD CONSTRAINT CHK_ERP_U_PLPC_GARMENTEMBLEM
  CHECK (GARMENTEMBLEM IN ('Y','N'));
ALTER TABLE ERP_U_PLPC
  ADD CONSTRAINT CHK_ERP_U_PLPC_GARMENTLOSS
  CHECK (GARMENTLOSS IN ('Y','N'));
ALTER TABLE ERP_U_PLPC
  ADD CONSTRAINT CHK_ERP_U_PLPC_GARMENTMAINT
  CHECK (GARMENTMAINTENANCE IN ('Y','N'));
ALTER TABLE ERP_U_PLPC
  ADD CONSTRAINT CHK_ERP_U_PLPC_GARMENTREPAIR
  CHECK (GARMENTREPAIR IN ('Y','N'));
ALTER TABLE ERP_U_PLPC
  ADD CONSTRAINT CHK_ERP_U_PLPC_GARMENTSETUP
  CHECK (GARMENTSETUP IN ('Y','N'));
ALTER TABLE ERP_U_PLPC
  ADD CONSTRAINT CHK_ERP_U_PLPC_GRMLOSSPERC
  CHECK (GARMENTLOSSPERCENTAGE BETWEEN 0 AND 100);
ALTER TABLE ERP_U_PLPC
  ADD CONSTRAINT CHK_ERP_U_PLPC_MBYOPPERC
  CHECK ((MBYOPPERCENTAGE BETWEEN 0.0 AND 100.0) OR MBYOPPERCENTAGE IS NULL);
ALTER TABLE ERP_U_PLPC
  ADD CONSTRAINT CHK_ERP_U_PLPC_MLPSPECIFICPERC
  CHECK (MLPSPECIFICPERCENTAGE IS NULL OR MLPSPECIFICPERCENTAGE BETWEEN 0.0 AND 100.0);
ALTER TABLE ERP_U_PLPC
  ADD CONSTRAINT CHK_ERP_U_PLPC_MMPSPECIFICPERC
  CHECK ((MMPSPECIFICPERCENTAGE BETWEEN 0.0 AND 100.0) OR (MMPSPECIFICPERCENTAGE IS NULL));
COMMENT ON COLUMN ERP_U_PLPC.SIZECODE
  IS 'If left blank we will use size code - ( dash), used to find PLPC';
COMMENT ON COLUMN ERP_U_PLPC.FINISHINGMETHODCODE
  IS 'If left blank we will use finishing method - (dash) used to find PLPC';
COMMENT ON COLUMN ERP_U_PLPC.MMPSPECIFICPERCENTAGE
  IS 'Material Maintenance Protection (MMP) specific percentage. Between 0.0 and 100.0, if empty (Null) then take the percentage defined on Customer or Product level if BPMMPENHANCED module. If on customer level is defined 0.0 (no MMP) then must be set to Null';
COMMENT ON COLUMN ERP_U_PLPC.GARMENTSETUP
  IS 'Default ''N''';
COMMENT ON COLUMN ERP_U_PLPC.GARMENTMAINTENANCE
  IS 'Default ''N''';
COMMENT ON COLUMN ERP_U_PLPC.GARMENTLOSS
  IS 'Default ''N''';
COMMENT ON COLUMN ERP_U_PLPC.GARMENTLOSSPERCENTAGE
  IS 'Default 100';
COMMENT ON COLUMN ERP_U_PLPC.GARMENTEMBLEM
  IS 'Default ''N''';
COMMENT ON COLUMN ERP_U_PLPC.MLPSPECIFICPERCENTAGE
  IS 'Material Loss Protection (MLP) specific percentage. If empty (Null) then take the percentage defined on Customer or Product level. If on customer level is defined 0.0 (no MLP) then must be set to Null.';
COMMENT ON COLUMN ERP_U_PLPC.GARMENTREPAIR
  IS 'Default ''N''';

CREATE TABLE ERP_U_CUSTOMER
( TRANSFER_ID                   NUMBER NOT NULL,
  TRANSFER_STATUS               NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_U_CUSTOMER_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  CUSTOMERNUMBER                NUMBER NOT NULL,
  MINIMUMINVOICEMOUNT           NUMBER(15,6) DEFAULT 0 NOT NULL,
  MATERIALMAINTENANCEPERCENTAGE NUMBER DEFAULT 0 NULL,
  GARMENTSETUP                  VARCHAR2(1) DEFAULT 'N' NULL,
  GARMENTMAINTENANCE            VARCHAR2(1) DEFAULT 'N' NULL,
  GARMENTLOSS                   VARCHAR2(1) DEFAULT 'N' NULL,
  GARMENTREPAIR                 VARCHAR2(1) DEFAULT 'N' NULL, 
  MBYOPPERCENTAGE               NUMBER DEFAULT 0 NULL,
  GARMENTLOSSPERCENTAGE         NUMBER DEFAULT 100 NULL,
  GARMENTEMBLEM                 VARCHAR2(1) DEFAULT 'N' NULL, 
  MATERIALLOSSPERCENTAGE        NUMBER DEFAULT 0 NULL,
  PREPCHARGE                    NUMBER(15,6) NULL,
  PREPCHARGE_STARTDATE          DATE DEFAULT TRUNC(SYSDATE-1) NULL,
  RESTOCKINGPRICE               NUMBER(15,6) NULL,
  RESTOCKINGPRICE_STARTDATE     DATE DEFAULT TRUNC(SYSDATE-1) NULL,
  EXCHANGECHARGE                NUMBER(15,6) NULL,
  EXCHANGECHARGE_STARTDATE      DATE DEFAULT TRUNC(SYSDATE-1) NULL);
ALTER TABLE ERP_U_CUSTOMER
 ADD CONSTRAINT XPKERP_U_CUSTOMER PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIE1_ERP_U_CUSTOMER ON ERP_U_CUSTOMER(TRANSFER_STATUS);
CREATE INDEX XIE2_ERP_U_CUSTOMER ON ERP_U_CUSTOMER(CUSTOMERNUMBER);
COMMENT ON COLUMN ERP_U_CUSTOMER.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_U_CUSTOMER.MINIMUMINVOICEMOUNT
  IS   'Customers minimum invoice amount';
COMMENT ON COLUMN ERP_U_CUSTOMER.MATERIALMAINTENANCEPERCENTAGE
  IS   'Between 0.0 and 100.0';

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