 /*order 502392*/
      /*
      CHANGE REPORT for Table CUSTOMERCLUSTER
          - Adding column FINANCIALCLUSTER
          - Adding column FINANCIALYEARSTART
      ACTION is ALTER or modify Table CUSTOMERCLUSTER
      */

ALTER TABLE CUSTOMERCLUSTER ADD (FINANCIALCLUSTER     VARCHAR2(1) DEFAULT 'N' 
    NOT NULL
                                   CONSTRAINT YN_ValidationNew153
                                          CHECK (FINANCIALCLUSTER IN ('Y', 'N'))
    );


ALTER TABLE CUSTOMERCLUSTER ADD (FINANCIALYEARSTART   NUMBER DEFAULT 1 NOT NULL
                                   CONSTRAINT FinancialYearStart_valid
                                          CHECK (FINANCIALYEARSTART BETWEEN 1 
    AND 52));


      /*
      ACTION is CREATE Table INVOICECORRECTIONTYPE
      */

CREATE SEQUENCE SEQ_INVOICECORRECTIONTYPE
START WITH 1
INCREMENT BY 1;

CREATE TABLE INVOICECORRECTIONTYPE (
       INVOICECORRECTIONTYPE_ID NUMBER NOT NULL,
       CODE                 VARCHAR2(12) NULL,
       FACTORYDESCRIPTION   VARCHAR2(40) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       DEBITCREDIT          VARCHAR2(1) DEFAULT 'D' NOT NULL
                                   CONSTRAINT DC_Validation1
                                          CHECK (DEBITCREDIT IN ('D', 'C')),
       SEQUENCENUMBER       NUMBER DEFAULT 0 NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew154
                                          CHECK (ACTIVE IN ('Y', 'N'))
);

CREATE UNIQUE INDEX XAK1INVOICECORRECTIONTYPE ON INVOICECORRECTIONTYPE
(
       CODE                           
);


ALTER TABLE INVOICECORRECTIONTYPE
       ADD  ( CONSTRAINT XPKINVOICECORRECTIONTYPE PRIMARY KEY (
              INVOICECORRECTIONTYPE_ID) ) ;

      
      /*
      ACTION is CREATE Table INVOICECORRECTIONTYPE_DESC
      */

CREATE TABLE INVOICECORRECTIONTYPE_DESC (
       INVOICECORRECTIONTYPE_ID NUMBER NOT NULL,
       LANGUAGE_ID          NUMBER NOT NULL,
       DESCRIPTION          VARCHAR2(40) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1INVOICECORRECTIONTYPE_DESC ON INVOICECORRECTIONTYPE_DESC
(
       INVOICECORRECTIONTYPE_ID       ASC
);

CREATE INDEX XIF2INVOICECORRECTIONTYPE_DESC ON INVOICECORRECTIONTYPE_DESC
(
       LANGUAGE_ID                    ASC
);


ALTER TABLE INVOICECORRECTIONTYPE_DESC
       ADD  ( CONSTRAINT XPKINVOICECORRECTIONTYPE_DESC PRIMARY KEY (
              INVOICECORRECTIONTYPE_ID, LANGUAGE_ID) ) ;


ALTER TABLE INVOICECORRECTIONTYPE_DESC
       ADD  ( CONSTRAINT INVOICECORRECTIONTYPE_D_LANGUA
              FOREIGN KEY (LANGUAGE_ID)
                             REFERENCES LANGUAGES
                             ON DELETE CASCADE ) ;


ALTER TABLE INVOICECORRECTIONTYPE_DESC
       ADD  ( CONSTRAINT INVOICECORRECTIONTYPE_D_INVOIC
              FOREIGN KEY (INVOICECORRECTIONTYPE_ID)
                             REFERENCES INVOICECORRECTIONTYPE
                             ON DELETE CASCADE ) ;

--------FILL INVOICECORRECTIONTYPE TABLE
insert into invoicecorrectiontype i
  (i.invoicecorrectiontype_id,
   code,
   factorydescription,
   systemuser_id,
   timestamp,
   debitcredit,
   sequencenumber,
   active)
  select seq_invoicelinesubtype.nextval,
         101,
         'Credit due to changes in quantity',
         getminsystemuser(),
         sysdate,
         'C',
         1,
         'Y'
    from dual;

insert into invoicecorrectiontype i
  (i.invoicecorrectiontype_id,
   code,
   factorydescription,
   systemuser_id,
   timestamp,
   debitcredit,
   sequencenumber,
   active)
  select seq_invoicelinesubtype.nextval,
         102,
         'Correction due to changes in quantity',
         getminsystemuser(),
         sysdate,
         'D',
         2,
         'Y'
    from dual;

insert into invoicecorrectiontype i
  (i.invoicecorrectiontype_id,
   code,
   factorydescription,
   systemuser_id,
   timestamp,
   debitcredit,
   sequencenumber,
   active)
  select seq_invoicelinesubtype.nextval,
         103,
         'Credit due to price changes',
         getminsystemuser(),
         sysdate,
         'C',
         3,
         'Y'
    from dual;

insert into invoicecorrectiontype i
  (i.invoicecorrectiontype_id,
   code,
   factorydescription,
   systemuser_id,
   timestamp,
   debitcredit,
   sequencenumber,
   active)
  select seq_invoicelinesubtype.nextval,
         104,
         'Correction due to price changes',
         getminsystemuser(),
         sysdate,
         'D',
         4,
         'Y'
    from dual;

insert into invoicecorrectiontype i
  (i.invoicecorrectiontype_id,
   code,
   factorydescription,
   systemuser_id,
   timestamp,
   debitcredit,
   sequencenumber,
   active)
  select seq_invoicelinesubtype.nextval,
         105,
         'Credit due to discount changes',
         getminsystemuser(),
         sysdate,
         'C',
         5,
         'Y'
    from dual;

insert into invoicecorrectiontype i
  (i.invoicecorrectiontype_id,
   code,
   factorydescription,
   systemuser_id,
   timestamp,
   debitcredit,
   sequencenumber,
   active)
  select seq_invoicelinesubtype.nextval,
         106,
         'Correction due to discount changes',
         getminsystemuser(),
         sysdate,
         'D',
         6,
         'Y'
    from dual;

insert into invoicecorrectiontype i
  (i.invoicecorrectiontype_id,
   code,
   factorydescription,
   systemuser_id,
   timestamp,
   debitcredit,
   sequencenumber,
   active)
  select seq_invoicelinesubtype.nextval,
         107,
         'Credit due to multiple changes',
         getminsystemuser(),
         sysdate,
         'C',
         7,
         'Y'
    from dual;

insert into invoicecorrectiontype i
  (i.invoicecorrectiontype_id,
   code,
   factorydescription,
   systemuser_id,
   timestamp,
   debitcredit,
   sequencenumber,
   active)
  select seq_invoicelinesubtype.nextval,
         108,
         'Correction due to multiple changes',
         getminsystemuser(),
         sysdate,
         'D',
         8,
         'Y'
    from dual;

insert into invoicecorrectiontype i
  (i.invoicecorrectiontype_id,
   code,
   factorydescription,
   systemuser_id,
   timestamp,
   debitcredit,
   sequencenumber,
   active)
  select seq_invoicelinesubtype.nextval,
         109,
         'Credit due to changes in rent stops',
         getminsystemuser(),
         sysdate,
         'C',
         9,
         'Y'
    from dual;

insert into invoicecorrectiontype i
  (i.invoicecorrectiontype_id,
   code,
   factorydescription,
   systemuser_id,
   timestamp,
   debitcredit,
   sequencenumber,
   active)
  select seq_invoicelinesubtype.nextval,
         110,
         'Correction due to changes in rent stops',
         getminsystemuser(),
         sysdate,
         'D',
         10,
         'Y'
    from dual;

insert into invoicecorrectiontype_desc
  (invoicecorrectiontype_id,
   language_id,
   description,
   systemuser_id,
   timestamp)
  select i.invoicecorrectiontype_id,
         l.language_id,
         i.factorydescription,
         getminsystemuser(),
         sysdate
    from invoicecorrectiontype i, languages l
   where i.code in (101,102,103,104,105,106,107,108,109,110) and l.translatedescription = 'Y';
--end fill invoicecorrection type

      /*
      ACTION is CREATE Table INVOICELINEDETAIL
      */
CREATE SEQUENCE SEQ_INVOICELINEDETAIL
START WITH 1
INCREMENT BY 1;

CREATE TABLE INVOICELINEDETAIL (
       INVOICELINEDETAIL_ID NUMBER NOT NULL,
       INVOICEHEADER_ID     NUMBER NOT NULL,
       INVOICELINE_ID       NUMBER NULL,
       DISTRIBUTIONPOINT_LINK_ID NUMBER NOT NULL,
       PRODUCT_LINK_ID      NUMBER NOT NULL,
       SIZEDEFINITION_LINK_ID NUMBER NOT NULL,
       FINISHINGMETHOD_ID   NUMBER NOT NULL,
       DELIVERYSCHEME_ID    NUMBER NULL,
       COSTCENTER_LINK_ID   NUMBER NOT NULL,
       INVPERIODPRICE       NUMBER NOT NULL,
       INVPERIODDISCOUNT    NUMBER NOT NULL,
       INVQTY               NUMBER NOT NULL,
       INVCALCPRICE         NUMBER NOT NULL,
       INVCALCAMOUNT        NUMBER NOT NULL,
       STARTDATE            DATE NULL,
       ENDDATE              DATE NULL,
       DETAILLINETYPE       NUMBER DEFAULT 0 NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL
);

COMMENT ON COLUMN INVOICELINEDETAIL.DETAILLINETYPE IS '0 = Standard (Rent)
1 = Rentstop
2 = Sample';
CREATE INDEX XIF1INVOICELINEDETAIL ON INVOICELINEDETAIL
(
       INVOICEHEADER_ID               ASC
);

CREATE INDEX XIF2INVOICELINEDETAIL ON INVOICELINEDETAIL
(
       INVOICELINE_ID                 ASC
);

CREATE INDEX XIF4INVOICELINEDETAIL ON INVOICELINEDETAIL
(
       DISTRIBUTIONPOINT_LINK_ID      ASC
);

CREATE INDEX XIF5INVOICELINEDETAIL ON INVOICELINEDETAIL
(
       PRODUCT_LINK_ID                ASC
);

CREATE INDEX XIF6INVOICELINEDETAIL ON INVOICELINEDETAIL
(
       SIZEDEFINITION_LINK_ID         ASC
);

CREATE INDEX XIF7INVOICELINEDETAIL ON INVOICELINEDETAIL
(
       FINISHINGMETHOD_ID             ASC
);

CREATE INDEX XIF8INVOICELINEDETAIL ON INVOICELINEDETAIL
(
       DELIVERYSCHEME_ID              ASC
);

CREATE INDEX XIF9INVOICELINEDETAIL ON INVOICELINEDETAIL
(
       COSTCENTER_LINK_ID             ASC
);


ALTER TABLE INVOICELINEDETAIL
       ADD  ( CONSTRAINT XPKINVOICELINEDETAIL PRIMARY KEY (
              INVOICELINEDETAIL_ID) ) ;

      
      /*
      CHANGE REPORT for Table INVOICELINEPRODUCT
          - Adding column STARTDATE
          - Adding column ENDDATE
          - Adding column BASEPRICE
          - Adding column CORRECTIONTYPE
          - Adding index XIF7INVOICELINEPRODUCT
      ACTION is ALTER or modify Table INVOICELINEPRODUCT
      */

ALTER TABLE INVOICELINEPRODUCT ADD (STARTDATE            DATE NULL);


ALTER TABLE INVOICELINEPRODUCT ADD (ENDDATE              DATE NULL);


ALTER TABLE INVOICELINEPRODUCT ADD (BASEPRICE            NUMBER NULL);


ALTER TABLE INVOICELINEPRODUCT ADD (CORRECTIONTYPE_ID       NUMBER NULL);

CREATE INDEX XIF7INVOICELINEPRODUCT ON INVOICELINEPRODUCT
(
       CORRECTIONTYPE_ID                 ASC
);

ALTER TABLE INVOICELINEPRODUCT
       ADD  ( CONSTRAINT INVOICELINEPRODUCT_CORRECTIONT
              FOREIGN KEY (CORRECTIONTYPE_ID)
                             REFERENCES INVOICECORRECTIONTYPE ) ;

ALTER TABLE INVOICELINEDETAIL
       ADD  ( CONSTRAINT INVOICELINEDETAIL_COSTCENTER_L
              FOREIGN KEY (COSTCENTER_LINK_ID)
                             REFERENCES COSTCENTER_LINK ) ;


ALTER TABLE INVOICELINEDETAIL
       ADD  ( CONSTRAINT INVOICELINEDETAIL_DELIVERYSCHE
              FOREIGN KEY (DELIVERYSCHEME_ID)
                             REFERENCES DELIVERYSCHEME ) ;


ALTER TABLE INVOICELINEDETAIL
       ADD  ( CONSTRAINT INVOICELINEDETAIL_FINISHINGMET
              FOREIGN KEY (FINISHINGMETHOD_ID)
                             REFERENCES FINISHINGMETHOD ) ;


ALTER TABLE INVOICELINEDETAIL
       ADD  ( CONSTRAINT INVOICELINEDETAIL_SIZEDEFINITI
              FOREIGN KEY (SIZEDEFINITION_LINK_ID)
                             REFERENCES SIZEDEFINITION_LINK ) ;


ALTER TABLE INVOICELINEDETAIL
       ADD  ( CONSTRAINT INVOICELINEDETAIL_PRODUCT_LINK
              FOREIGN KEY (PRODUCT_LINK_ID)
                             REFERENCES PRODUCT_LINK ) ;


ALTER TABLE INVOICELINEDETAIL
       ADD  ( CONSTRAINT INVOICELINEDETAIL_DISTRIBUTION
              FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID)
                             REFERENCES DISTRIBUTIONPOINT_LINK ) ;


ALTER TABLE INVOICELINEDETAIL
       ADD  ( CONSTRAINT INVOICELINEDETAIL_INVOICELINE_
              FOREIGN KEY (INVOICELINE_ID)
                             REFERENCES INVOICELINE
                             ON DELETE CASCADE ) ;


ALTER TABLE INVOICELINEDETAIL
       ADD  ( CONSTRAINT INVOICELINEDETAIL_INVOICEHEADE
              FOREIGN KEY (INVOICEHEADER_ID)
                             REFERENCES INVOICEHEADER
                             ON DELETE CASCADE ) ;


--Nested table CostCenterHistInvData

CREATE TYPE ACTUALRENTSTOP_TAB  IS TABLE OF  VARCHAR2(1);
/
CREATE TYPE ACTUALPRICE_TAB     IS TABLE OF  NUMBER;
/
CREATE TYPE ACTUALDISCOUNT_TAB  IS TABLE OF  NUMBER;
/
CREATE TYPE ACTUALQUANTITY_TAB  IS TABLE OF  NUMBER;
/
CREATE TYPE ACTUALSAMPLEQTY_TAB IS TABLE OF  NUMBER;
/
CREATE TYPE ACTUALCALCPRICE_TAB IS TABLE OF  NUMBER;
/

CREATE SEQUENCE SEQ_COSTCENTERHISTINVDATA
START WITH 1
INCREMENT BY 1;

CREATE TABLE COSTCENTERHISTINVDATA(
	COSTCENTERHISTINVDATA_ID  NUMBER NOT NULL,
	INVOICEHEADER_ID 	  NUMBER NOT NULL,
	DISTRIBUTIONPOINT_LINK_ID NUMBER NOT NULL,
	PRODUCT_LINK_ID 	  NUMBER NOT NULL,
	SIZEDEFINITION_LINK_ID    NUMBER NOT NULL,
	FINISHINGMETHOD_ID 	  NUMBER NOT NULL,
	DELIVERYSCHEME_ID 	  NUMBER NULL,
	COSTCENTER_LINK_ID 	  NUMBER NOT NULL,
	STARTDATE 		  DATE NOT NULL,
	DAYSINPERIOD 		  INTEGER NOT NULL,
	SYSTEMUSER_ID 		  NUMBER NOT NULL,
	TIMESTAMP 		  DATE NOT NULL,
	CC_RENTSTOP 		ACTUALRENTSTOP_TAB,
	CC_ACTUALPRICE 		ACTUALPRICE_TAB,
	CC_ACTUALDISCOUNT 	ACTUALDISCOUNT_TAB,
	CC_ACTUALQUANTITY 	ACTUALQUANTITY_TAB,
	CC_ACTUALSAMPLEQTY 	ACTUALSAMPLEQTY_TAB,
	CC_CALCPRICE 		ACTUALCALCPRICE_TAB
)
	NESTED TABLE CC_RENTSTOP STORE AS AR_HISTINVDATA_TABLE,
	NESTED TABLE CC_ACTUALPRICE STORE AS AP_HISTINVDATA_TABLE,
	NESTED TABLE CC_ACTUALDISCOUNT STORE AS AD_HISTINVDATA_TABLE,
	NESTED TABLE CC_ACTUALQUANTITY STORE AS AQ_HISTINVDATA_TABLE,
	NESTED TABLE CC_ACTUALSAMPLEQTY STORE AS AS_HISTINVDATA_TABLE,
	NESTED TABLE CC_CALCPRICE STORE AS AC_HISTINVDATA_TABLE;


ALTER TABLE COSTCENTERHISTINVDATA
       ADD  ( CONSTRAINT XPKCOSTCENTERHISTINVDATA PRIMARY KEY (
              COSTCENTERHISTINVDATA_ID) ) ;

CREATE UNIQUE INDEX XAK1COSTCENTERHISTINVDATA ON COSTCENTERHISTINVDATA
(
  INVOICEHEADER_ID		ASC,
  DISTRIBUTIONPOINT_LINK_ID	ASC,
  PRODUCT_LINK_ID		ASC,
  SIZEDEFINITION_LINK_ID	ASC,
  FINISHINGMETHOD_ID		ASC,
  DELIVERYSCHEME_ID		ASC
);

ALTER TABLE COSTCENTERHISTINVDATA
       ADD  ( CONSTRAINT COSTCENTERHISTINVDATA_COSTCENT
              FOREIGN KEY (COSTCENTER_LINK_ID)
                             REFERENCES COSTCENTER_LINK ) ;


ALTER TABLE COSTCENTERHISTINVDATA
       ADD  ( CONSTRAINT COSTCENTERHISTINVDATA_DELIVERY
              FOREIGN KEY (DELIVERYSCHEME_ID)
                             REFERENCES DELIVERYSCHEME ) ;


ALTER TABLE COSTCENTERHISTINVDATA
       ADD  ( CONSTRAINT COSTCENTERHISTINVDATA_FINISHIN
              FOREIGN KEY (FINISHINGMETHOD_ID)
                             REFERENCES FINISHINGMETHOD ) ;


ALTER TABLE COSTCENTERHISTINVDATA
       ADD  ( CONSTRAINT COSTCENTERHISTINVDATA_SIZEDEFI
              FOREIGN KEY (SIZEDEFINITION_LINK_ID)
                             REFERENCES SIZEDEFINITION_LINK ) ;


ALTER TABLE COSTCENTERHISTINVDATA
       ADD  ( CONSTRAINT COSTCENTERHISTINVDATA_PRODUCT_
              FOREIGN KEY (PRODUCT_LINK_ID)
                             REFERENCES PRODUCT_LINK ) ;


ALTER TABLE COSTCENTERHISTINVDATA
       ADD  ( CONSTRAINT COSTCENTERHISTINVDATA_DISTRIBU
              FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID)
                             REFERENCES DISTRIBUTIONPOINT_LINK ) ;

ALTER TABLE COSTCENTERHISTINVDATA
       ADD  ( CONSTRAINT COSTCENTERHISTINVDATA_INVOICEH
              FOREIGN KEY (INVOICEHEADER_ID)
                             REFERENCES INVOICEHEADER
                             ON DELETE CASCADE ) ;
