-- ABS-26012
-------   CUSTOMERBGPROTECTIONPRICE   -----------
CREATE SEQUENCE SEQ_CUSTOMERBGPROTECTIONPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERBGPROTECTIONPRICE
(
CUSTOMERBGPROTECTIONPRICE_ID NUMBER NOT NULL,
CUSTOMER_ID NUMBER NOT NULL,
PRODUCT_ID NUMBER NOT NULL,
SIZEDEFINITION_ID NUMBER NOT NULL,
PRICECOMPONENT NUMBER NOT NULL CONSTRAINT PRICECOMPONENT_VALID1_1 CHECK (PRICECOMPONENT IN (1,2,3,4)),
STARTDATE DATE NOT NULL,
PRICE NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN CUSTOMERBGPROTECTIONPRICE.PRICECOMPONENT IS '1 = Garment Maintenance Protection (GMP)
2 = Garment Setup Protection (GSP)
3 = Garment Loss Protection (GLP)
4 = Garment Repair Protection (GRP)';

ALTER TABLE CUSTOMERBGPROTECTIONPRICE ADD CONSTRAINT XPKCUSTOMERBGPROTECTIONPRICE PRIMARY KEY (CUSTOMERBGPROTECTIONPRICE_ID);

CREATE UNIQUE INDEX XAK1CUSTOMERBGPROTECTIONPRICE ON CUSTOMERBGPROTECTIONPRICE (CUSTOMER_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, PRICECOMPONENT ASC, STARTDATE ASC);

CREATE INDEX XIF1CUSTOMERBGPROTECTIONPRICE ON CUSTOMERBGPROTECTIONPRICE (CUSTOMER_ID ASC);
CREATE INDEX XIF2CUSTOMERBGPROTECTIONPRICE ON CUSTOMERBGPROTECTIONPRICE (PRODUCT_ID ASC);
CREATE INDEX XIF3CUSTOMERBGPROTECTIONPRICE ON CUSTOMERBGPROTECTIONPRICE (SIZEDEFINITION_ID ASC);

ALTER TABLE CUSTOMERBGPROTECTIONPRICE ADD CONSTRAINT CUSTOMERBGPROTECTIONPRICE_CID FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE CUSTOMERBGPROTECTIONPRICE ADD CONSTRAINT CUSTOMERBGPROTECTIONPRICE_PID FOREIGN KEY (PRODUCT_ID)
REFERENCES PRODUCT (PRODUCT_ID);

ALTER TABLE CUSTOMERBGPROTECTIONPRICE ADD CONSTRAINT CUSTOMERBGPROTECTIONPRICE_SDID FOREIGN KEY (SIZEDEFINITION_ID)
REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);

-------   GENERALBGPROTECTIONPRICE   -----------
CREATE SEQUENCE SEQ_GENERALBGPROTECTIONPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE GENERALBGPROTECTIONPRICE
(
GENERALBGPROTECTIONPRICE_ID NUMBER NOT NULL,
GENERALPRICELIST_ID NUMBER NOT NULL,
PRODUCT_ID NUMBER NOT NULL,
SIZEDEFINITION_ID NUMBER NOT NULL,
PRICECOMPONENT NUMBER NOT NULL CONSTRAINT PRICECOMPONENT_VALID1_2 CHECK (PRICECOMPONENT IN (1,2,3,4)),
STARTDATE DATE NOT NULL,
PRICE NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);


COMMENT ON COLUMN GENERALBGPROTECTIONPRICE.GENERALPRICELIST_ID IS 'PriceType = 9 – Insurance';

COMMENT ON COLUMN GENERALBGPROTECTIONPRICE.PRICECOMPONENT IS '1 = Garment Maintenance Protection (GMP)
2 = Garment Setup Protection (GSP)
3 = Garment Loss Protection (GLP)
4 = Garment Repair Protection (GRP)';

ALTER TABLE GENERALBGPROTECTIONPRICE ADD CONSTRAINT XPKGENERALBGPROTECTIONPRICE PRIMARY KEY (GENERALBGPROTECTIONPRICE_ID);

CREATE UNIQUE INDEX XAK1GENERALBGPROTECTIONPRICE ON GENERALBGPROTECTIONPRICE (GENERALPRICELIST_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, PRICECOMPONENT ASC, STARTDATE ASC);

CREATE INDEX XIF1GENERALBGPROTECTIONPRICE ON GENERALBGPROTECTIONPRICE (GENERALPRICELIST_ID ASC);
CREATE INDEX XIF2GENERALBGPROTECTIONPRICE ON GENERALBGPROTECTIONPRICE (PRODUCT_ID ASC);
CREATE INDEX XIF3GENERALBGPROTECTIONPRICE ON GENERALBGPROTECTIONPRICE (SIZEDEFINITION_ID ASC);

ALTER TABLE GENERALBGPROTECTIONPRICE ADD CONSTRAINT GENERALBGPROTECTIONPRICE_GPID FOREIGN KEY (GENERALPRICELIST_ID)
REFERENCES GENERALPRICELIST (GENERALPRICELIST_ID) ON DELETE CASCADE;

ALTER TABLE GENERALBGPROTECTIONPRICE ADD CONSTRAINT GENERALBGPROTECTIONPRICE_PID FOREIGN KEY (PRODUCT_ID)
REFERENCES PRODUCT (PRODUCT_ID);

ALTER TABLE GENERALBGPROTECTIONPRICE ADD CONSTRAINT GENERALBGPROTECTIONPRICE_SDID FOREIGN KEY (SIZEDEFINITION_ID)
REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);

-------   CUSTOMERBUDGETPROTECTION   -----------
CREATE TABLE CUSTOMERBUDGETPROTECTION
(
CUSTOMER_ID NUMBER NOT NULL,
MATERIALMAINTENANCE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD364 CHECK (MATERIALMAINTENANCE IN ('Y','N')),
MATERIALMAINTENANCEPERCENTAGE NUMBER DEFAULT 0 NULL,
GARMENTSETUP VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD365 CHECK (GARMENTSETUP IN ('Y','N')),
GARMENTMAINTENANCE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD366 CHECK (GARMENTMAINTENANCE IN ('Y','N')),
GARMENTLOSS VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD367 CHECK (GARMENTLOSS IN ('Y','N')),
GARMENTREPAIR VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD368 CHECK (GARMENTREPAIR IN ('Y','N')),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN CUSTOMERBUDGETPROTECTION.MATERIALMAINTENANCEPERCENTAGE IS 'Between 0.0 and 100.0';

ALTER TABLE CUSTOMERBUDGETPROTECTION ADD CONSTRAINT XPKCUSTOMERBUDGETPROTECTION PRIMARY KEY (CUSTOMER_ID);

ALTER TABLE CUSTOMERBUDGETPROTECTION ADD CONSTRAINT CUSTOMERBUDGETPROTECTION_CID FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE CUSTOMERBUDGETPROTECTION ADD CONSTRAINT MATERIALMAINTENANCEPERC_CHECK CHECK (((MATERIALMAINTENANCEPERCENTAGE BETWEEN 0 AND 100) AND MATERIALMAINTENANCEPERCENTAGE IS NOT NULL) OR (MATERIALMAINTENANCEPERCENTAGE IS NULL));

-------   DISTRIBPOINTBGPROTECTION   -----------
CREATE TABLE DISTRIBPOINTBGPROTECTION
(
DISTRIBUTIONPOINT_ID NUMBER NOT NULL,
CUSTOMER_ID NUMBER NOT NULL,
MATERIALMAINTENANCE VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD369 CHECK (MATERIALMAINTENANCE IN ('Y','N')),
GARMENTSETUP VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD370 CHECK (GARMENTSETUP IN ('Y','N')),
GARMENTMAINTENANCE VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD371 CHECK (GARMENTMAINTENANCE IN ('Y','N')),
GARMENTLOSS VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD372 CHECK (GARMENTLOSS IN ('Y','N')),
GARMENTREPAIR VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD373 CHECK (GARMENTREPAIR IN ('Y','N')),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE DISTRIBPOINTBGPROTECTION ADD CONSTRAINT XPKDISTRIBPOINTBGPROTECTION PRIMARY KEY (DISTRIBUTIONPOINT_ID);

CREATE INDEX XIF1DISTRIBPOINTBGPROTECTION ON DISTRIBPOINTBGPROTECTION (CUSTOMER_ID ASC);

ALTER TABLE DISTRIBPOINTBGPROTECTION ADD CONSTRAINT DISTRIBPOINTBGPROTECTION_DPID FOREIGN KEY (DISTRIBUTIONPOINT_ID)
REFERENCES DISTRIBUTIONPOINT (DISTRIBUTIONPOINT_ID) ON DELETE CASCADE;

ALTER TABLE DISTRIBPOINTBGPROTECTION ADD CONSTRAINT DISTRIBPOINTBGPROTECTION_CID FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

-------   PLPCBUDGETPROTECTION   -----------
CREATE TABLE PLPCBUDGETPROTECTION
(
PRODUCTLISTPERCUSTOMER_ID NUMBER NOT NULL,
CUSTOMER_ID NUMBER NOT NULL,
MATERIALMAINTENANCE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD374 CHECK (MATERIALMAINTENANCE IN ('Y','N')),
MMPSPECIFICPERCENTAGE NUMBER NULL,
GARMENTSETUP VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD375 CHECK (GARMENTSETUP IN ('Y','N')),
GARMENTMAINTENANCE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD376 CHECK (GARMENTMAINTENANCE IN ('Y','N')),
GARMENTLOSS VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD377 CHECK (GARMENTLOSS IN ('Y','N')),
GARMENTREPAIR VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD378 CHECK (GARMENTREPAIR IN ('Y','N')),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE PLPCBUDGETPROTECTION ADD CONSTRAINT XPKPLPCBUDGETPROTECTION PRIMARY KEY (PRODUCTLISTPERCUSTOMER_ID);

CREATE INDEX XIF1PLPCBUDGETPROTECTION ON PLPCBUDGETPROTECTION (CUSTOMER_ID ASC);

ALTER TABLE PLPCBUDGETPROTECTION ADD CONSTRAINT PLPCBUDGETPROTECTION_PLPC FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID)
REFERENCES PRODUCTLISTPERCUSTOMER (PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE PLPCBUDGETPROTECTION ADD CONSTRAINT PLPCBUDGETPROTECTION_CID FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

-------   INVOICELINESUBTYPE   -----------
INSERT INTO INVOICELINESUBTYPE
  (INVOICELINESUBTYPE_ID,
   SUBTYPE,
   INVOICELINETYPE_ID,
   FACTORYDESCRIPTION,
   SYSTEMUSER_ID,
   TIMESTAMP)
  SELECT SEQ_INVOICELINESUBTYPE.NEXTVAL,
         26,
         11,
         'Material Maintenance Protection (MMP)',
         GETMINSYSTEMUSER(),
         SYSDATE
    FROM DUAL;
COMMIT;

INSERT INTO INVOICELINESUBTYPE
  (INVOICELINESUBTYPE_ID,
   SUBTYPE,
   INVOICELINETYPE_ID,
   FACTORYDESCRIPTION,
   SYSTEMUSER_ID,
   TIMESTAMP)
  SELECT SEQ_INVOICELINESUBTYPE.NEXTVAL,
         27,
         11,
         'Garment Maintenance Protection (GMP)',
         GETMINSYSTEMUSER(),
         SYSDATE
    FROM DUAL;
COMMIT;

INSERT INTO INVOICELINESUBTYPE
  (INVOICELINESUBTYPE_ID,
   SUBTYPE,
   INVOICELINETYPE_ID,
   FACTORYDESCRIPTION,
   SYSTEMUSER_ID,
   TIMESTAMP)
  SELECT SEQ_INVOICELINESUBTYPE.NEXTVAL,
         28,
         11,
         'Garment Setup Protection (GSP)',
         GETMINSYSTEMUSER(),
         SYSDATE
    FROM DUAL;
COMMIT;

INSERT INTO INVOICELINESUBTYPE
  (INVOICELINESUBTYPE_ID,
   SUBTYPE,
   INVOICELINETYPE_ID,
   FACTORYDESCRIPTION,
   SYSTEMUSER_ID,
   TIMESTAMP)
  SELECT SEQ_INVOICELINESUBTYPE.NEXTVAL,
         29,
         11,
         'Garment Loss Protection (GLP)',
         GETMINSYSTEMUSER(),
         SYSDATE
    FROM DUAL;
COMMIT;

INSERT INTO INVOICELINESUBTYPE
  (INVOICELINESUBTYPE_ID,
   SUBTYPE,
   INVOICELINETYPE_ID,
   FACTORYDESCRIPTION,
   SYSTEMUSER_ID,
   TIMESTAMP)
  SELECT SEQ_INVOICELINESUBTYPE.NEXTVAL,
         30,
         11,
         'Garment Repair Protection (GRP)',
         GETMINSYSTEMUSER(),
         SYSDATE
    FROM DUAL;
COMMIT;

INSERT INTO INVOICELINESUBTYPE_DESC
      (INVOICELINESUBTYPE_ID,
       LANGUAGE_ID,
       DESCRIPTION,
       SYSTEMUSER_ID,
       TIMESTAMP)
      SELECT I.INVOICELINESUBTYPE_ID,
             L.LANGUAGE_ID,
             I.FACTORYDESCRIPTION,
             GETMINSYSTEMUSER(),
             SYSDATE
        FROM INVOICELINESUBTYPE I, LANGUAGES L
       WHERE L.TRANSLATEDESCRIPTION = 'Y' AND I.SUBTYPE = 26 AND I.INVOICELINETYPE_ID = 11;
COMMIT;

INSERT INTO INVOICELINESUBTYPE_DESC
      (INVOICELINESUBTYPE_ID,
       LANGUAGE_ID,
       DESCRIPTION,
       SYSTEMUSER_ID,
       TIMESTAMP)
      SELECT I.INVOICELINESUBTYPE_ID,
             L.LANGUAGE_ID,
             I.FACTORYDESCRIPTION,
             GETMINSYSTEMUSER(),
             SYSDATE
        FROM INVOICELINESUBTYPE I, LANGUAGES L
       WHERE L.TRANSLATEDESCRIPTION = 'Y' AND I.SUBTYPE = 27 AND I.INVOICELINETYPE_ID = 11;
COMMIT;

INSERT INTO INVOICELINESUBTYPE_DESC
      (INVOICELINESUBTYPE_ID,
       LANGUAGE_ID,
       DESCRIPTION,
       SYSTEMUSER_ID,
       TIMESTAMP)
      SELECT I.INVOICELINESUBTYPE_ID,
             L.LANGUAGE_ID,
             I.FACTORYDESCRIPTION,
             GETMINSYSTEMUSER(),
             SYSDATE
        FROM INVOICELINESUBTYPE I, LANGUAGES L
       WHERE L.TRANSLATEDESCRIPTION = 'Y' AND I.SUBTYPE = 28 AND I.INVOICELINETYPE_ID = 11;
COMMIT;

INSERT INTO INVOICELINESUBTYPE_DESC
      (INVOICELINESUBTYPE_ID,
       LANGUAGE_ID,
       DESCRIPTION,
       SYSTEMUSER_ID,
       TIMESTAMP)
      SELECT I.INVOICELINESUBTYPE_ID,
             L.LANGUAGE_ID,
             I.FACTORYDESCRIPTION,
             GETMINSYSTEMUSER(),
             SYSDATE
        FROM INVOICELINESUBTYPE I, LANGUAGES L
       WHERE L.TRANSLATEDESCRIPTION = 'Y' AND I.SUBTYPE = 29 AND I.INVOICELINETYPE_ID = 11;
COMMIT;

INSERT INTO INVOICELINESUBTYPE_DESC
      (INVOICELINESUBTYPE_ID,
       LANGUAGE_ID,
       DESCRIPTION,
       SYSTEMUSER_ID,
       TIMESTAMP)
      SELECT I.INVOICELINESUBTYPE_ID,
             L.LANGUAGE_ID,
             I.FACTORYDESCRIPTION,
             GETMINSYSTEMUSER(),
             SYSDATE
        FROM INVOICELINESUBTYPE I, LANGUAGES L
       WHERE L.TRANSLATEDESCRIPTION = 'Y' AND I.SUBTYPE = 30 AND I.INVOICELINETYPE_ID = 11;
COMMIT;
