--Order 20011284.100

------------------CLIENTBILLINGINFO----------------------

ALTER TABLE CLIENTBILLINGINFO
	ADD (INVOICECAPPEDEXCESSAMOUNTS NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT INVCAPPEDEXCESSAMOUNTS_valid1 CHECK (INVOICECAPPEDEXCESSAMOUNTS IN (0, 1)));

ALTER TABLE CLIENTBILLINGINFO
  ADD (INSURANCECOMPANY_ID NUMBER NULL);
  
ALTER TABLE CLIENTBILLINGINFO 
  ADD CONSTRAINT InsuranceCompany_valid1 CHECK (((INVOICECAPPEDEXCESSAMOUNTS = 1) and (INSURANCECOMPANY_ID is not null)) or ((INVOICECAPPEDEXCESSAMOUNTS = 0) and (INSURANCECOMPANY_ID is null)));

ALTER TABLE CLIENTBILLINGINFO
	ADD (INSURANCEPOLICYNUMBER VARCHAR2(25) NULL);

ALTER TABLE CLIENTBILLINGINFO
  ADD CONSTRAINT INSURANCEPOLICYNUMBER_valid1 CHECK (((INVOICECAPPEDEXCESSAMOUNTS = 1) and (INSURANCEPOLICYNUMBER is not null)) or ((INVOICECAPPEDEXCESSAMOUNTS = 0) and (INSURANCEPOLICYNUMBER is null)));

COMMENT ON COLUMN CLIENTBILLINGINFO.INVOICECAPPEDEXCESSAMOUNTS IS '0 = Not applicable / To customer
1 = To insurance company';

COMMENT ON COLUMN CLIENTBILLINGINFO.INSURANCECOMPANY_ID IS 'If InvoiceCappedExcessAmounts = 1 then this must be filled.';

CREATE  INDEX XIF6CLIENTBILLINGINFO ON CLIENTBILLINGINFO
(INSURANCECOMPANY_ID   ASC);

ALTER TABLE CLIENTBILLINGINFO
ADD CONSTRAINT CLIENTBILLINGINFO_CUSTOMER FOREIGN KEY (INSURANCECOMPANY_ID) REFERENCES CUSTOMER (CUSTOMER_ID);


------------------CUSTOMERBILLINGINFO----------------------

ALTER TABLE CUSTOMERBILLINGINFO
	ADD (CUSTOMERROLE NUMBER  DEFAULT 0 NOT NULL CONSTRAINT CustomerRole_valid1 CHECK (CUSTOMERROLE IN (0, 1)));

COMMENT ON COLUMN CUSTOMERBILLINGINFO.CUSTOMERROLE IS '0 = Debtor
1 = Insurance company';

------------------CUSTOMERRESIDENTIALSETTINGS----------------------

ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
	ADD (CAPPEDCLIENTINVOICE NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT CappedClientInvoice_valid1 CHECK (CAPPEDCLIENTINVOICE IN (0, 1)));

ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
	ADD (EXCESSAMOUNTINVOICELAYOUT_ID NUMBER NULL);	

COMMENT ON COLUMN CUSTOMERRESIDENTIALSETTINGS.CAPPEDCLIENTINVOICE IS '0 = Not applicable / customer does not have capped client invoices
1 = Customer has capped client invoices';

CREATE  INDEX XIF7CUSTOMERRESIDENTIALSETTING ON CUSTOMERRESIDENTIALSETTINGS
(EXCESSAMOUNTINVOICELAYOUT_ID   ASC);

ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
ADD CONSTRAINT CUSTRESSETTINGS_FORMLAYOUT FOREIGN KEY (EXCESSAMOUNTINVOICELAYOUT_ID) REFERENCES FORMLAYOUT (FORMLAYOUT_ID);
	
------------------INVENTORYCATEGORY----------------------

ALTER TABLE INVENTORYCATEGORY
	ADD (USEFORINVOICING NUMBER  DEFAULT 0 NOT NULL CONSTRAINT UseForInvoicing_valid1 CHECK (USEFORINVOICING IN (0, 1, 2, 3, 4)));

COMMENT ON COLUMN INVENTORYCATEGORY.USEFORINVOICING IS '0 = Not applicable / Wash of products
1 = Rent
2 = Labels
3 = Sales
4 = Weights';

------------------CLIENTCATEGORYINVENTORY----------------------

ALTER TABLE CLIENTCATEGORYINVENTORY
	ADD (INVOICETO NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT InvoiceTo_valid1 CHECK (INVOICETO IN (0, 1, 2)));

ALTER TABLE CLIENTCATEGORYINVENTORY
	ADD (CAPPEDAMOUNT NUMBER(15,6) DEFAULT 0 NOT NULL);
	
COMMENT ON COLUMN CLIENTCATEGORYINVENTORY.INVOICETO IS '0 = Entirely to client
1 = Entirely to customer
2 = To client with capped amount, rest is invoiced to customer or insurance company';

------------------INVOICELINETYPE----------------------

COMMENT ON COLUMN INVOICELINETYPE.TYPE IS '01= Rent
02= Delivery (wash)
03= Soil Weight
04= Clean Weight
05= Sales
06= Repair
07= Replacement charge
08= Automatic Replacement (non-garm)
09= Loss charge
10= Remaining value charge
11= Overuse charge
12= Prep / Emblem charge
13= Finance charge
14= Cash Delay line
15= Miscellaneous Invoice line
16= Special Charge
17= Minimum invoice amount
18= Tax
19=Emblem Rent
20=Locker Rent
21=Locker service
22=Lease
23=Stock Charge
24=Lost Customer Residual Value Charge      25=Lost Revenue Charge
26 = Residential wash
27 = Residential labels
28 = Service costs
29 = Capped Excess Amounts';

------------------INVOICEHEADER----------------------

COMMENT ON COLUMN INVOICEHEADER.INVOICETYPE IS '1=Generated
2=Manual
4=Additional Generated
5=Lost Customer
6=Corrections Invoice
7 = Excess Amounts / insurance company invoice';

------------------INVOICELINE----------------------
ALTER TABLE INVOICELINE
	ADD (INVENTORYCATEGORY_ID NUMBER  DEFAULT 0 NOT NULL);

ALTER TABLE INVOICELINE
	ADD (INVENTORYCATEGORYCODE VARCHAR2(6) NULL);

CREATE  INDEX XIE1INVOICELINE ON INVOICELINE
(INVOICEHEADER_ID   ASC,INVENTORYCATEGORY_ID   ASC);
	
COMMENT ON COLUMN INVOICELINE.INVENTORYCATEGORY_ID IS 'ID from the InventoryCategory. Only used for client invoices. Note that this is not a foreign key.';


------------------INVOICELINEEXCESSAMOUNT----------------------
CREATE TABLE INVOICELINEEXCESSAMOUNT
(
	INVOICELINE_ID       NUMBER NOT NULL ,
	INVOICETO            NUMBER(1) DEFAULT  0  NOT NULL  CONSTRAINT  InvoiceTo_valid1_1 CHECK (INVOICETO IN (0, 1, 2)),
	INVOICECUSTOMER_LINK_ID NUMBER NOT NULL ,
	INSURANCEPOLICYNUMBER VARCHAR2(25) NULL ,
	CUSTOMERINVOICELINE_ID NUMBER NULL ,
	INVOICEDTOCUSTOMER_YN VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField68 CHECK (INVOICEDTOCUSTOMER_YN IN ('Y', 'N')),
	CAPPEDAMOUNT         NUMBER(15,6) DEFAULT  0  NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN INVOICELINEEXCESSAMOUNT.INVOICETO IS '0 = Invoice to customer
1 = invoice to insurance company';

COMMENT ON COLUMN INVOICELINEEXCESSAMOUNT.INVOICEDTOCUSTOMER_YN IS 'This attribute is used in combination with customerinvoiceline_ID. When customerInvoiceLine_ID is filled it must be set to ‘Y’. This attribute is needed for cleanup of invoices, when customer invoice is deleted the invoiceline must still be marked as invoiced.';

CREATE  INDEX XIF2INVOICEEXCESSAMOUNT ON INVOICELINEEXCESSAMOUNT
(INVOICECUSTOMER_LINK_ID   ASC);

CREATE  INDEX XIF3INVOICEEXCESSAMOUNT ON INVOICELINEEXCESSAMOUNT
(CUSTOMERINVOICELINE_ID   ASC);

CREATE  INDEX XIE1INVOICEEXCESSAMOUNT ON INVOICELINEEXCESSAMOUNT
(INVOICEDTOCUSTOMER_YN   ASC,INVOICECUSTOMER_LINK_ID   ASC);

ALTER TABLE INVOICELINEEXCESSAMOUNT
ADD CONSTRAINT  XPKINVOICEEXCESSAMOUNT PRIMARY KEY (INVOICELINE_ID);

ALTER TABLE INVOICELINEEXCESSAMOUNT
ADD CONSTRAINT INVLINEEXCESSAMOUNT_INVLINE FOREIGN KEY (INVOICELINE_ID) REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE CASCADE;

ALTER TABLE INVOICELINEEXCESSAMOUNT
ADD CONSTRAINT INVLINEEXCESSAMOUNT_CUSTOMER_L FOREIGN KEY (INVOICECUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);

ALTER TABLE INVOICELINEEXCESSAMOUNT
ADD CONSTRAINT INVLINEEXCESSAMOUNT_CUSTINV FOREIGN KEY (CUSTOMERINVOICELINE_ID) REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE SET NULL;

--insert into invoicelinetype table

insert into invoicelinetype
  select 29,
         sysdate,
         getminsystemuser(),
         'N',
         'Capped Excess Amounts',
         29,
         null,
         'Y',
         'N',
         3,
         null,
         0
    from dual;

commit;

insert into invoicelinetype_desc
  (invoicelinetype_id, language_id, description, systemuser_id, timestamp)
  select x.invoicelinetype_id,
         l.language_id,
         x.factorydescription,
         x.systemuser_id,
         x.timestamp
    from invoicelinetype x, languages l
   where x.invoicelinetype_id = 29;

commit;
   
--insert into invoicelinesubtype table

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             0,
             29,
             'Full credit on client invoice',
             1,
             sysdate
        from dual;
commit;

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             1,
             29,
             'Credit Excess Amount on client invoice',
             1,
             sysdate
        from dual;
commit;

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             2,
             29,
             'Full debit to customer',
             1,
             sysdate
        from dual;
commit;

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             3,
             29,
             'Debit Excess Amount to customer',
             1,
             sysdate
        from dual;
commit;

