-- Add column to ERP_O_INVOICELINE
alter table ERP_O_INVOICELINE add BILLINGMETHODCODE varchar2(12);
-- Add comments to the columns 
comment on column ERP_O_INVOICELINE.BILLINGMETHODCODE
  is 'Optional code based on the invoicelinetype, invoicelinesubtype and priceusage ( frequency )';

-- Create new table ERP_O_COSTREPORTINFO
create table ERP_O_COSTREPORTINFO
(
  TRANSFER_ID           NUMBER not null,
  TRANSFER_STATUS       NUMBER not null,
  INVOICELINE_ID        NUMBER not null,
  COSTCENTERCODE        VARCHAR2(12) not null,
  COSTCENTERDESCRIPTION VARCHAR2(30) not null,
  YEAR                  NUMBER not null,
  WEEK                  NUMBER not null,
  QUANTITY              NUMBER not null,
  AMOUNT                NUMBER not null,
  MINIMUMQTY            NUMBER not null,
  DELIVEREDQTY          NUMBER not null
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_COSTREPORTINFO
  add constraint FK_ERP_O_COSTREPORTINFO foreign key (TRANSFER_ID, INVOICELINE_ID)
  references ERP_O_INVOICELINE (TRANSFER_ID, INVOICELINE_ID) on delete cascade;
-- Create/Recreate indexes 
create index INDX_ERP_O_COSTREPORTINFO on ERP_O_COSTREPORTINFO (TRANSFER_ID, INVOICELINE_ID, COSTCENTERCODE);


-- Create table ERP_C_BILLINGMETHODCODE
create table ERP_C_BILLINGMETHODCODE
(
  INVOICELINETYPE_ID number not null,
  INVOICELINESUBTYPE number not null,
  BILLINGMETHOD      number not null,
  PRICEUSAGE         number default 0 not null,
  FREQUENCY          number,
  BILLINGMETHODCODE  VARCHAR2(12) not null
);
-- Create unique key and FK constraint 
alter table ERP_C_BILLINGMETHODCODE
  add constraint PK_ERP_C_BILLINGMETHODCODE unique (INVOICELINETYPE_ID, INVOICELINESUBTYPE, BILLINGMETHOD, PRICEUSAGE, FREQUENCY);
alter table ERP_C_BILLINGMETHODCODE
  add constraint FK_ERP_C_BMC_INVOICELINETYPE foreign key (INVOICELINETYPE_ID)
  references INVOICELINETYPE (INVOICELINETYPE_ID) on delete cascade;
-- Add comments to the table 
comment on table ERP_C_BILLINGMETHODCODE
  is 'ERP CONVERSION TABLE for BILLINGMETHODCODE in ERP_O_INVOICELINE';
-- Add comments to the columns 
comment on column ERP_C_BILLINGMETHODCODE.INVOICELINETYPE_ID
  is 'From invoiceline';
comment on column ERP_C_BILLINGMETHODCODE.INVOICELINESUBTYPE
  is 'From invoiceline, for values check invoicelinesubtype.subtype';
comment on column ERP_C_BILLINGMETHODCODE.BILLINGMETHOD
  is 'From PLPC, possible values
1 = Flat rated rent
2 = Rent based on minimum
3 = Circulating inventory
4 = Piece rate only
5 = Lease';
comment on column ERP_C_BILLINGMETHODCODE.PRICEUSAGE
  is 'From PLPC, possible values
0 = Not applicable
1 = Split price
2 = Rent price only
3 = Wash price only';
comment on column ERP_C_BILLINGMETHODCODE.FREQUENCY
  is 'From invoicelineproduct.DELIVERYSCHEMECODE.FREQUENCY Only filled for linetype 1, sublinetype 0.';
comment on column ERP_C_BILLINGMETHODCODE.BILLINGMETHODCODE
  is 'Export value needed to fill ERP_O_INVOICELINE.BILLINGMETHODCODE';
