--order 502392

rename SEQ_COSTCENTERHISTINVDATA to SEQ_COSTCENTERHISTINVDATA_OLD;

rename COSTCENTERHISTINVDATA to COSTCENTERHISTINVDATA_OLD;

alter table COSTCENTERHISTINVDATA_OLD
rename constraint XPKCOSTCENTERHISTINVDATA to XPKCOSTCENTERHISTINVDATA_OLD;

alter table COSTCENTERHISTINVDATA_OLD
rename constraint COSTCENTERHISTINVDATA_COSTCENT to COSTCENTERHISTINVDATA_CC_OLD;

alter table COSTCENTERHISTINVDATA_OLD
rename constraint COSTCENTERHISTINVDATA_DELIVERY to COSTCENTERHISTINVDATA_DEL_OLD;

alter table COSTCENTERHISTINVDATA_OLD
rename constraint COSTCENTERHISTINVDATA_DISTRIBU to COSTCENTERHISTINVDATA_DIST_OLD;

alter table COSTCENTERHISTINVDATA_OLD
rename constraint COSTCENTERHISTINVDATA_FINISHIN to COSTCENTERHISTINVDATA_FIN_OLD;

alter table COSTCENTERHISTINVDATA_OLD
rename constraint COSTCENTERHISTINVDATA_INVOICEH to COSTCENTERHISTINVDATA_INV_OLD;

alter table COSTCENTERHISTINVDATA_OLD
rename constraint COSTCENTERHISTINVDATA_PRODUCT_ to COSTCENTERHISTINVDATA_PROD_OLD;

alter table COSTCENTERHISTINVDATA_OLD
rename constraint COSTCENTERHISTINVDATA_SIZEDEFI to COSTCENTERHISTINVDATA_SIZE_OLD;

alter index XAK1COSTCENTERHISTINVDATA rename to XAK1COSTCENTERHISTINVDATA_OLD;

alter index XPKCOSTCENTERHISTINVDATA rename to XPKCOSTCENTERHISTINVDATA_OLD;

-----------------------------------------------------------
CREATE SEQUENCE SEQ_COSTCENTERHISTINVDATA
START WITH 1
INCREMENT BY 1;


CREATE OR REPLACE TYPE ACTUALRENTSTOP_TABEL AS OBJECT (
SEQV NUMBER,
RENTSTOP VARCHAR2(1)
); 
/
CREATE OR REPLACE TYPE ACTUALRENTSTOP_TAB1  AS TABLE OF  ACTUALRENTSTOP_TABEL;
/

CREATE OR REPLACE TYPE ACTUALPRICE_TABEL AS OBJECT (
SEQV NUMBER,
PRICE NUMBER
) ;
/
CREATE OR REPLACE TYPE ACTUALPRICE_TAB1  AS TABLE OF  ACTUALPRICE_TABEL;
/

CREATE OR REPLACE TYPE ACTUALQUANTITY_TABEL AS OBJECT (
SEQV NUMBER,
QUANTITY NUMBER
) ;
/
CREATE OR REPLACE TYPE ACTUALQUANTITY_TAB1 AS TABLE OF  ACTUALQUANTITY_TABEL;
/

CREATE OR REPLACE TYPE ACTUALSAMPLEQTY_TABEL AS OBJECT (
SEQV NUMBER,
SAMPLEQTY NUMBER
) ;
/
CREATE OR REPLACE TYPE ACTUALSAMPLEQTY_TAB1 AS TABLE OF ACTUALSAMPLEQTY_TABEL;
/

CREATE OR REPLACE TYPE ACTUALCALCPRICE_TABEL AS OBJECT (
SEQV NUMBER,
CALCPRICE NUMBER
) ;
/
CREATE OR REPLACE TYPE ACTUALCALCPRICE_TAB1 AS TABLE OF  ACTUALCALCPRICE_TABEL;
/

CREATE OR REPLACE TYPE ACTUALDISCOUNT_TABEL AS OBJECT (
SEQV NUMBER,
DISCOUNT NUMBER
) ;
/
CREATE OR REPLACE TYPE ACTUALDISCOUNT_TAB1 AS TABLE OF  ACTUALDISCOUNT_TABEL;
/


-- Create table
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,
  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_TAB1,
  CC_ACTUALPRICE            ACTUALPRICE_TAB1,
  CC_ACTUALDISCOUNT         ACTUALDISCOUNT_TAB1,
  CC_ACTUALQUANTITY         ACTUALQUANTITY_TAB1,
  CC_ACTUALSAMPLEQTY        ACTUALSAMPLEQTY_TAB1,
  CC_CALCPRICE              ACTUALCALCPRICE_TAB1,
  ORIGIN                    NUMBER default 0 not null
)
nested table CC_RENTSTOP store as AR_CCHISTINVDATA_TABLE
nested table CC_ACTUALPRICE store as AP_CCHISTINVDATA_TABLE
nested table CC_ACTUALDISCOUNT store as AD_CCHISTINVDATA_TABLE
nested table CC_ACTUALQUANTITY store as AQ_CCHISTINVDATA_TABLE
nested table CC_ACTUALSAMPLEQTY store as AS_CCHISTINVDATA_TABLE
nested table CC_CALCPRICE store as AC_CCHISTINVDATA_TABLE;

comment on column COSTCENTERHISTINVDATA.ORIGIN
  is '0 = Initial invoice
1 = Correction invoice
2 = Correction gap first invoice
3 = Correction gap previous invoice
9 = In-arrear invoice';

alter table COSTCENTERHISTINVDATA
  add constraint XPKCOSTCENTERHISTINVDATA primary key (COSTCENTERHISTINVDATA_ID) ;

alter table COSTCENTERHISTINVDATA
  add constraint COSTCENTERHISTINVDATA_COSTCEN foreign key (COSTCENTER_LINK_ID)
  references COSTCENTER_LINK (COSTCENTER_LINK_ID);

alter table COSTCENTERHISTINVDATA
  add constraint COSTCENTERHISTINVDATA_DELIVER foreign key (DELIVERYSCHEME_ID)
  references DELIVERYSCHEME (DELIVERYSCHEME_ID);

alter table COSTCENTERHISTINVDATA
  add constraint COSTCENTERHISTINVDATA_DISTRIB foreign key (DISTRIBUTIONPOINT_LINK_ID)
  references DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);

alter table COSTCENTERHISTINVDATA
  add constraint COSTCENTERHISTINVDATA_FINISHI foreign key (FINISHINGMETHOD_ID)
  references FINISHINGMETHOD (FINISHINGMETHOD_ID);

alter table COSTCENTERHISTINVDATA
  add constraint COSTCENTERHISTINVDATA_INVOICE foreign key (INVOICEHEADER_ID)
  references INVOICEHEADER (INVOICEHEADER_ID) on delete cascade;

alter table COSTCENTERHISTINVDATA
  add constraint COSTCENTERHISTINVDATA_PRODUCT foreign key (PRODUCT_LINK_ID)
  references PRODUCT_LINK (PRODUCT_LINK_ID);

alter table COSTCENTERHISTINVDATA
  add constraint COSTCENTERHISTINVDATA_SIZEDEF foreign key (SIZEDEFINITION_LINK_ID)
  references SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);

create unique index XAK1COSTCENTERHISTINVDATA on COSTCENTERHISTINVDATA (INVOICEHEADER_ID, DISTRIBUTIONPOINT_LINK_ID, PRODUCT_LINK_ID, SIZEDEFINITION_LINK_ID, FINISHINGMETHOD_ID, DELIVERYSCHEME_ID, ORIGIN);