-- 20011850.90 (#7 Loss Charge & Credits during grace period - Deadline must be live on Mon 17th Oct 2011)

-----------------------LOSSPERIODS TABLE--------------------------------

CREATE SEQUENCE SEQ_LOSSPERIODS
START WITH 1
INCREMENT BY 1;

CREATE TABLE LOSSPERIODS
(
  LOSSPERIODS_ID       NUMBER NOT NULL ,
  YEAR                 NUMBER(4) NOT NULL ,
  PERIOD               NUMBER(2) NOT NULL  CONSTRAINT  PERIOD_valid CHECK (PERIOD BETWEEN 1 AND 99),
  PERIODENDDATE        DATE NOT NULL ,
  DAYSINPERIOD         NUMBER(2) DEFAULT  0  NOT NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
);


ALTER TABLE LOSSPERIODS
ADD CONSTRAINT  XPKLOSSPERIODS PRIMARY KEY (LOSSPERIODS_ID);

CREATE UNIQUE INDEX XAK1LOSSPERIODS ON LOSSPERIODS
(PERIODENDDATE   ASC);

CREATE UNIQUE INDEX XAK2LOSSPERIODS ON LOSSPERIODS
(YEAR   ASC,PERIOD   ASC);


-----------------------LOSSPERIODSPROCESSED TABLE--------------------------------


CREATE TABLE LOSSPERIODSPROCESSED
(
  LOSSPERIODS_ID       NUMBER NOT NULL ,
  PLANT_ID             NUMBER NOT NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
);


ALTER TABLE LOSSPERIODSPROCESSED
ADD CONSTRAINT  XPKLOSSPERIODSPROCESSED PRIMARY KEY (LOSSPERIODS_ID,PLANT_ID);

CREATE  INDEX XIF1LOSSPERIODSPROCESSED ON LOSSPERIODSPROCESSED
(LOSSPERIODS_ID   ASC);

ALTER TABLE LOSSPERIODSPROCESSED
ADD CONSTRAINT LOSSPERIODSPROCESSED_LOSSPERIO FOREIGN KEY (LOSSPERIODS_ID) REFERENCES LOSSPERIODS (LOSSPERIODS_ID) ON DELETE CASCADE;

CREATE  INDEX XIF2LOSSPERIODSPROCESSED ON LOSSPERIODSPROCESSED
(PLANT_ID   ASC);

ALTER TABLE LOSSPERIODSPROCESSED
ADD CONSTRAINT LOSSPERIODSPROCESSED_PLANT FOREIGN KEY (PLANT_ID) REFERENCES PLANT (PLANT_ID);


----------------------PROCESS TABLE------------------------

INSERT INTO  PROCESS 
      (PROCESS_ID,
       DESCRIPTION,
       ACTIVE)
    VALUES
      (104,
       'Loss for pool',
       'Y');



---------------------CUSTOMER TABLE------------------------

ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE DISABLE;

ALTER TABLE CUSTOMER
  ADD (CHARGELOSSFORPOOLITEMS NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT CHARGELOSSFORPOOL_va CHECK (CHARGELOSSFORPOOLITEMS IN (0, 1)));

ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE ENABLE;    
  
COMMENT ON COLUMN CUSTOMER.CHARGELOSSFORPOOLITEMS IS '0 = Not applicable / do not charge loss for pool
1 = Charge loss for pool items
';



---------------------------INVOICEAPPENDIXDEFINITION TABLE--------------------------


INSERT INTO  INVOICEAPPENDIXDEFINITION 
      (INVOICEAPPENDIXDEFINITION_ID,
       INVOICEAPPENDIXTYPE,
       DESCRIPTION,
       REPORTNAME,
       CUSTOMERCODE,
       MODULE,
       MANUAL,
       DIRECTINVOICE,
       OTHERSERVICES,
       RESIDENTIALSERVICES,
       SYSTEMUSER_ID,
       TIMESTAMP)
    VALUES
      (34,
       34,
       'Condensed Loss charges/credit pool items',
       'CondensedInvoiceAppendixLossForPool.rpt',     
       '',
       'LOSSFORPOOL',
       'N',
       'N',
       'Y',
       'N',
       GETMINSYSTEMUSER(),
       SYSDATE);


INSERT INTO  INVOICEAPPENDIXDEFINITION 
      (INVOICEAPPENDIXDEFINITION_ID,
       INVOICEAPPENDIXTYPE,
       DESCRIPTION,
       REPORTNAME,
       CUSTOMERCODE,
       MODULE,
       MANUAL,
       DIRECTINVOICE,
       OTHERSERVICES,
       RESIDENTIALSERVICES,
       SYSTEMUSER_ID,
       TIMESTAMP)
    VALUES
      (35,
       35,
       'Loss charges / credits for pool items',
       'InvoiceAppendixLossForPool.rpt',     
       '',
       'LOSSFORPOOL',
       'N',
       'N',
       'Y',
       'N',
       GETMINSYSTEMUSER(),
       SYSDATE);




---------------------------UNIQUEITEMLOSS TABLE--------------------------

CREATE SEQUENCE SEQ_UNIQUEITEMLOSS
START WITH 1
INCREMENT BY 1;

CREATE TABLE UNIQUEITEMLOSS
(
  UNIQUEITEMLOSS_ID    NUMBER NOT NULL ,
  UNIQUEITEM_LINK_ID   NUMBER NOT NULL ,
  CUSTOMER_LINK_ID     NUMBER NOT NULL ,
  DISTRIBUTIONPOINT_LINK_ID NUMBER NOT NULL ,
  LOSSSTATUS           NUMBER(1) NOT NULL  CONSTRAINT  LOSSSTATUS_valid CHECK (LOSSSTATUS IN (0, 1, 2, 3,4)),
  LOSSPERIODS_ID       NUMBER NOT NULL ,
  OUTSCANDATE          DATE NOT NULL ,
  RETURNEDDATE         DATE DEFAULT  to_date('31/12/2099','dd/mm/yyyy')  NOT NULL ,
  LOSSINVOICEHEADER_ID NUMBER NULL ,
  LOSSAMOUNT           NUMBER(15,2) NOT NULL ,
  CREDITINVOICEHEADER_ID NUMBER NULL ,
  CREDITAMOUNT         NUMBER(15,2) NOT NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN UNIQUEITEMLOSS.LOSSSTATUS IS '0 = Loss, (to be) charged
1 = Loss, charged
2 = Returned, (to be) credited 100 %
3 = Returned, (to be) credited 50 %
4 = Returned, not credited

';

ALTER TABLE UNIQUEITEMLOSS
ADD CONSTRAINT  XPKUNIQUEITEMLOSS PRIMARY KEY (UNIQUEITEMLOSS_ID);

CREATE  INDEX XIF1UNIQUEITEMLOSS ON UNIQUEITEMLOSS
(UNIQUEITEM_LINK_ID   ASC);

ALTER TABLE UNIQUEITEMLOSS
ADD CONSTRAINT UNIQUEITEMLOSS_UNIQUEITEM_LINK FOREIGN KEY (UNIQUEITEM_LINK_ID) REFERENCES UNIQUEITEM_LINK (UNIQUEITEM_LINK_ID);

CREATE  INDEX XIF2UNIQUEITEMLOSS ON UNIQUEITEMLOSS
(CUSTOMER_LINK_ID   ASC);

ALTER TABLE UNIQUEITEMLOSS
ADD CONSTRAINT UNIQUEITEMLOSS_CUSTOMER_LINK FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);

CREATE  INDEX XIF3UNIQUEITEMLOSS ON UNIQUEITEMLOSS
(DISTRIBUTIONPOINT_LINK_ID   ASC);

ALTER TABLE UNIQUEITEMLOSS
ADD CONSTRAINT UNIQUEITEMLOSS_DISTRIBPOINT_LK FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID) REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);

CREATE  INDEX XIF4UNIQUEITEMLOSS ON UNIQUEITEMLOSS
(LOSSPERIODS_ID   ASC);

ALTER TABLE UNIQUEITEMLOSS
ADD CONSTRAINT UNIQUEITEMLOSS_LOSSPERIODS FOREIGN KEY (LOSSPERIODS_ID) REFERENCES LOSSPERIODS (LOSSPERIODS_ID);

CREATE  INDEX XIF5UNIQUEITEMLOSS ON UNIQUEITEMLOSS
(LOSSINVOICEHEADER_ID   ASC);

ALTER TABLE UNIQUEITEMLOSS
ADD CONSTRAINT UNIQUEITEMLOSS_INVOICEHEADER1 FOREIGN KEY (LOSSINVOICEHEADER_ID) REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE SET NULL;

CREATE  INDEX XIF6UNIQUEITEMLOSS ON UNIQUEITEMLOSS
(CREDITINVOICEHEADER_ID   ASC);


ALTER TABLE UNIQUEITEMLOSS
ADD CONSTRAINT UNIQUEITEMLOSS_INVOICEHEADER2 FOREIGN KEY (CREDITINVOICEHEADER_ID) REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE SET NULL;

CREATE  INDEX XIE1UNIQUEITEMLOSS ON UNIQUEITEMLOSS
(UNIQUEITEM_LINK_ID   ASC,LOSSSTATUS   ASC);

CREATE  INDEX XIE2UNIQUEITEMLOSS ON UNIQUEITEMLOSS
(UNIQUEITEM_LINK_ID   ASC,OUTSCANDATE   ASC);

CREATE  INDEX XIE3UNIQUEITEMLOSS ON UNIQUEITEMLOSS
(LOSSSTATUS   ASC,RETURNEDDATE   ASC);

CREATE  INDEX XIE4UNIQUEITEMLOSS ON UNIQUEITEMLOSS
(CUSTOMER_LINK_ID   ASC,LOSSSTATUS   ASC);



-----------------------------------------INVOICELINESUBTYPE TABLE------------------------------------


INSERT INTO INVOICELINESUBTYPE I
  (I.INVOICELINESUBTYPE_ID,
   SUBTYPE,
   INVOICELINETYPE_ID,
   FACTORYDESCRIPTION,
   SYSTEMUSER_ID,
   TIMESTAMP)
  SELECT SEQ_INVOICELINESUBTYPE.NEXTVAL,
         37,
         9,
         'Loss for pool',
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM   DUAL; 


INSERT INTO INVOICELINESUBTYPE I
  (I.INVOICELINESUBTYPE_ID,
   SUBTYPE,
   INVOICELINETYPE_ID,
   FACTORYDESCRIPTION,
   SYSTEMUSER_ID,
   TIMESTAMP)
  SELECT SEQ_INVOICELINESUBTYPE.NEXTVAL,
         38,
         9,
         'Credit for pool',
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM   DUAL; 











