-- ABS-119474/ ABS-121151

--------CUSTOMERGARMENTCHARGES TABLE-------------
ALTER TABLE CUSTOMERGARMENTCHARGES ADD (RENTCALCMETHODPOSITIVEMOVE NUMBER DEFAULT 0 NOT NULL
                                                                   CONSTRAINT CHK_CUSTGRMCH_RENTCALCPOS CHECK (RENTCALCMETHODPOSITIVEMOVE IN (0, 1, 2, 3)),
                                        RENTCALCMETHODNEGATIVEMOVE NUMBER DEFAULT 0 NOT NULL
                                                                   CONSTRAINT CHK_CUSTGRMCH_RENTCALCNEG CHECK (RENTCALCMETHODNEGATIVEMOVE IN (0, 1, 2, 3)));

COMMENT ON COLUMN CUSTOMERGARMENTCHARGES.RENTCALCMETHODPOSITIVEMOVE
  IS 'If a wearer inventory starts or increased in the month, this is how it this billed

0 - Default, no calculation
1 - Active part of month billing (Pro rata)
2 - Whole of month bill
3 - No bill

Only used for module RENTBM1CPWMAX and coefficient billing
';
COMMENT ON COLUMN CUSTOMERGARMENTCHARGES.RENTCALCMETHODNEGATIVEMOVE
  IS 'If a wearer inventory ends or decreased in the month, this is how it this billed

0 - Default, no calculation
1 - Active part of month billing (Pro rata)
2 - Whole of month bill
3 - No bill

Only used for module RENTBM1CPWMAX and coefficient billing
';

----------- WEARERINVENTORYMOVEMENT TABLE -----------
CREATE SEQUENCE SEQ_WEARERINVENTORYMOVEMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE WEARERINVENTORYMOVEMENT
(
  WEARERINVENTORYMOVEMENT_ID NUMBER NOT NULL,
  WEARERINVENTORY_ID         NUMBER NOT NULL,
  MOVEMENTDATE               DATE NOT NULL,
  MAXINVENTORY               NUMBER NOT NULL,
  CIRCINVENTORY              NUMBER NOT NULL,
  CHANGESPERWEEK             NUMBER NOT NULL,
  PREVMAXINVENTORY           NUMBER NOT NULL,
  PREVCIRCINVENTORY          NUMBER NOT NULL,
  PREVCHANGESPERWEEK         NUMBER NOT NULL,
  CREATIONTIMESTAMP          DATE NOT NULL,
  CREATIONSYSTEMUSER_ID      NUMBER NOT NULL,
  BILLINGTYPE                NUMBER NOT NULL
                             CONSTRAINT CHK_WIMOVEMENT_BILLINGTYPE CHECK (BILLINGTYPE IN (0, 1, 2, 3)),
  TEMPORARYRENTSTOP          NUMBER DEFAULT 0 NOT NULL 
                             CONSTRAINT CHK_WIMOVEMENT_TEMPRENTSTOP CHECK (TEMPORARYRENTSTOP IN (0, 1, 2)),         
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
)
;

COMMENT ON COLUMN WEARERINVENTORYMOVEMENT.BILLINGTYPE
  IS '0 - Default, no bill / not billed yet
1 - Active part of month billing (Pro rata)
2 - Whole of month bill
3 - No bill

NOTE: this is set during invoicing not creation
Only used for module RENTBM1CPWMAX
';

COMMENT ON COLUMN WEARERINVENTORYMOVEMENT.TEMPORARYRENTSTOP
  IS '1 = Start of temp rent stop
2 = End of temp rent stop
';

ALTER TABLE WEARERINVENTORYMOVEMENT
  ADD CONSTRAINT XPKWEARERINVENTORYMOVEMENT PRIMARY KEY (WEARERINVENTORYMOVEMENT_ID);

CREATE INDEX XIE1WEARERINVENTORYMOVEMENT ON WEARERINVENTORYMOVEMENT (WEARERINVENTORY_ID, MOVEMENTDATE);
CREATE INDEX XIF1WEARERINVENTORYMOVEMENT ON WEARERINVENTORYMOVEMENT (WEARERINVENTORY_ID);

ALTER TABLE WEARERINVENTORYMOVEMENT
  ADD CONSTRAINT FK_WIMOVEMENT_WI FOREIGN KEY (WEARERINVENTORY_ID)
  REFERENCES WEARERINVENTORY (WEARERINVENTORY_ID) ON DELETE CASCADE;

create or replace procedure populate_wearerinventorymovement is
lv_count number;
begin
  select count(*) into lv_count from wearerinventorymovement;
  if lv_count = 0
    then
      INSERT INTO WEARERINVENTORYMOVEMENT
                  (WEARERINVENTORYMOVEMENT_ID,
                   WEARERINVENTORY_ID,
                   MOVEMENTDATE,
                   MAXINVENTORY,
                   CIRCINVENTORY,
                   CHANGESPERWEEK,
                   PREVMAXINVENTORY,
                   PREVCIRCINVENTORY,
                   PREVCHANGESPERWEEK,
                   CREATIONTIMESTAMP,
                   CREATIONSYSTEMUSER_ID,
                   BILLINGTYPE,
                   SYSTEMUSER_ID,
                   TIMESTAMP)
        SELECT
        SEQ_WEARERINVENTORYMOVEMENT.NEXTVAL,
        WI.WEARERINVENTORY_ID,
        WI.DATEACTIVE,
        WI.MAXINVENTORY,
        WI.CIRCINVENTORY,
        WI.CHANGESPERWEEK,
        WI.MAXINVENTORY,
        WI.CIRCINVENTORY,
        WI.CHANGESPERWEEK,
        SYSDATE,
        GETMINSYSTEMUSER(),
        0,
        GETMINSYSTEMUSER(),
        SYSDATE
        FROM
        WEARERINVENTORY WI;
      COMMIT;
 end if;
end populate_wearerinventorymovement;
/
execute populate_wearerinventorymovement; 
drop procedure populate_wearerinventorymovement;
---------- PLPCTEMPRENTSTOP TABLE -------------

CREATE TABLE PLPCTEMPRENTSTOP 
(
PRODUCTLISTPERCUSTOMER_ID   NUMBER NOT NULL,
TEMPORARYRENTSTOPSTARTDATE  DATE NOT NULL,
TEMPORARYRENTSTOPENDDATE    DATE NOT NULL,
SYSTEMUSER_ID               NUMBER NOT NULL,
TIMESTAMP                   DATE NOT NULL                      
);

ALTER TABLE PLPCTEMPRENTSTOP
  ADD CONSTRAINT XPKPLPCTEMPRENTSTOP PRIMARY KEY (PRODUCTLISTPERCUSTOMER_ID);

ALTER TABLE PLPCTEMPRENTSTOP
  ADD CONSTRAINT FK_PLPCTEMPRENTSTOP_PLPC FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID)
  REFERENCES PRODUCTLISTPERCUSTOMER (PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;
