--Order 20014641.70


---INVOICEFREQUENCYLOSSPERIOD table---

CREATE SEQUENCE SEQ_INVOICEFREQUENCYLOSSPERIOD
START WITH 1
INCREMENT BY 1;

CREATE TABLE INVOICEFREQUENCYLOSSPERIOD
(
	INVOICEFREQUENCYLOSSPERIOD_ID NUMBER NOT NULL ,
	INVOICEFREQUENCY_ID  NUMBER NOT NULL ,
	ENDDATE              DATE NOT NULL ,
	CUTOFFDATE           DATE NOT NULL ,
	YEAR                 NUMBER(4) DEFAULT  0  NOT NULL ,
	PERIOD               NUMBER(2) DEFAULT  0  NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN INVOICEFREQUENCYLOSSPERIOD.ENDDATE IS 'EndDate is included in the period.';

COMMENT ON COLUMN INVOICEFREQUENCYLOSSPERIOD.CUTOFFDATE IS 'Not returned items that were delivered before (and including) the cutoffdate are charged for loss.';

ALTER TABLE INVOICEFREQUENCYLOSSPERIOD
ADD CONSTRAINT  XPKINVOICEFREQUENCYLOSSPERIOD PRIMARY KEY (INVOICEFREQUENCYLOSSPERIOD_ID);

CREATE UNIQUE INDEX XAK1INVOICEFREQUENCYLOSSPERIOD ON INVOICEFREQUENCYLOSSPERIOD
(INVOICEFREQUENCY_ID   ASC,ENDDATE   ASC);

CREATE INDEX XIF1INVOICEFREQUENCYLOSSPERIOD ON INVOICEFREQUENCYLOSSPERIOD
(INVOICEFREQUENCY_ID   ASC);

CREATE INDEX XIE1INVOICEFREQUENCYLOSSPERIOD ON INVOICEFREQUENCYLOSSPERIOD
(INVOICEFREQUENCY_ID   ASC,CUTOFFDATE   ASC);

ALTER TABLE INVOICEFREQUENCYLOSSPERIOD
ADD CONSTRAINT INVOICEFREQUENCYLOSSPERIOD_INV FOREIGN KEY (INVOICEFREQUENCY_ID) REFERENCES INVOICEFREQUENCY (INVOICEFREQUENCY_ID) ON DELETE CASCADE;

---LOSSGRACESCHEME table---

CREATE SEQUENCE SEQ_LOSSGRACESCHEME
START WITH 1
INCREMENT BY 1;

CREATE TABLE LOSSGRACESCHEME
(
	LOSSGRACESCHEME_ID   NUMBER NOT NULL ,
	CODE                 VARCHAR2(6) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE LOSSGRACESCHEME
ADD CONSTRAINT  XPKLOSSGRACESCHEME PRIMARY KEY (LOSSGRACESCHEME_ID);

CREATE UNIQUE INDEX XAK1LOSSGRACESCHEME ON LOSSGRACESCHEME
(CODE   ASC);


---LOSSGRACESCHEME_DESC table---

CREATE TABLE LOSSGRACESCHEME_DESC
(
	LOSSGRACESCHEME_ID   NUMBER NOT NULL ,
	LANGUAGE_ID          NUMBER NOT NULL ,
	DESCRIPTION          VARCHAR2(30) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE LOSSGRACESCHEME_DESC
ADD CONSTRAINT  XPKLOSSGRACESCHEME_DESC PRIMARY KEY (LOSSGRACESCHEME_ID,LANGUAGE_ID);

CREATE INDEX XIF1LOSSGRACESCHEME_DESC ON LOSSGRACESCHEME_DESC
(LOSSGRACESCHEME_ID   ASC);

CREATE INDEX XIF2LOSSGRACESCHEME_DESC ON LOSSGRACESCHEME_DESC
(LANGUAGE_ID   ASC);

ALTER TABLE LOSSGRACESCHEME_DESC
ADD CONSTRAINT LOSSGRACESCHEME_DESC_LOSSGRACE FOREIGN KEY (LOSSGRACESCHEME_ID) REFERENCES LOSSGRACESCHEME (LOSSGRACESCHEME_ID) ON DELETE CASCADE;

ALTER TABLE LOSSGRACESCHEME_DESC
ADD CONSTRAINT LOSSGRACESCHEME_DESC_LANGUAGES FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;

---LOSSGRACESCHEMEDETAIL table---

CREATE TABLE LOSSGRACESCHEMEDETAIL
(
	LOSSGRACESCHEME_ID   NUMBER NOT NULL ,
	NUMBEROFDAYS         NUMBER DEFAULT  0  NOT NULL  CONSTRAINT  NUMBEROFDAYS_valid1_1 CHECK (NUMBEROFDAYS BETWEEN 0 AND 9999),
	PERCENTAGE           NUMBER DEFAULT  0  NOT NULL  CONSTRAINT  PERCENTAGE_valid1_1 CHECK (PERCENTAGE BETWEEN 0 AND 100),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE LOSSGRACESCHEMEDETAIL
ADD CONSTRAINT  XPKLOSSGRACESCHEMEDETAIL PRIMARY KEY (LOSSGRACESCHEME_ID,NUMBEROFDAYS);

CREATE INDEX XIF1LOSSGRACESCHEMEDETAIL ON LOSSGRACESCHEMEDETAIL
(LOSSGRACESCHEME_ID   ASC);

ALTER TABLE LOSSGRACESCHEMEDETAIL
ADD CONSTRAINT LOSSGRACESCHEMEDETAIL_LOSSGRAC FOREIGN KEY (LOSSGRACESCHEME_ID) REFERENCES LOSSGRACESCHEME (LOSSGRACESCHEME_ID) ON DELETE CASCADE;

---CUSTOMER table---

ALTER TABLE CUSTOMER
	ADD (CHARGELOSSUNIQUEPOOLDAYS NUMBER  DEFAULT 0 NOT NULL CONSTRAINT CHARGELOSSUNIQ_valid1_1 CHECK (CHARGELOSSUNIQUEPOOLDAYS BETWEEN 0 AND 999));

ALTER TABLE CUSTOMER
	ADD (LOSSGRACESCHEME_ID NUMBER NULL);

COMMENT ON COLUMN CUSTOMER.CHARGELOSSUNIQUEPOOLDAYS IS 'Number of days after which loss is charged for unique pool items.';

CREATE INDEX XIF45CUSTOMER ON CUSTOMER
(LOSSGRACESCHEME_ID   ASC);

ALTER TABLE CUSTOMER
ADD CONSTRAINT LOSSGRACESCHEME_CUSTOMER FOREIGN KEY (LOSSGRACESCHEME_ID) REFERENCES LOSSGRACESCHEME (LOSSGRACESCHEME_ID);

---PRODUCTLISTPERCUSTOMER table---

ALTER TABLE PRODUCTLISTPERCUSTOMER
	DROP CONSTRAINT INVENTORYBILLINGTYP;


ALTER TABLE PRODUCTLISTPERCUSTOMER
	ADD  CONSTRAINT INVENTORYBILLINGTYP  CHECK (INVENTORYBILLINGTYPE IN (0, 1, 2, 3, 4, 5));

ALTER TABLE PRODUCTLISTPERCUSTOMER
	ADD (CHARGELOSSUNIQUEPOOLDAYS NUMBER(3)  DEFAULT 0 NOT NULL CONSTRAINT CHARGELOSSUNIQ_valid1_2 CHECK (CHARGELOSSUNIQUEPOOLDAYS BETWEEN 0 AND 999));

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.INVENTORYBILLINGTYPE IS 'For billingmethod 3:
0 - circulating inventory
1 - real quantity
2 - max inventory qty
3 - on-site inventory qty
4 - Non-returned pool after n weeks
5 – Loss for non-returned pool after n days';

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.CHARGELOSSUNIQUEPOOLDAYS IS '0 = No loss is charged for unique pool
1 – 999 Number of days after which loss is charged for unique pool.';


---UNIQUEITEMLOSS table---

ALTER TABLE UNIQUEITEMLOSS
	ADD (INVOICEFREQUENCYLOSSPERIOD_ID NUMBER 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 < 100 %
4 = Returned, not credited
5 = Returned before loss is invoiced';

COMMENT ON COLUMN UNIQUEITEMLOSS.LOSSPERIODS_ID IS 'Replaced with Invoice-FrequencyLossPeriod_ID, for historic reasons it is not yet deleted or cleared.';

CREATE INDEX XIF7UNIQUEITEMLOSS ON UNIQUEITEMLOSS
(INVOICEFREQUENCYLOSSPERIOD_ID   ASC);

ALTER TABLE UNIQUEITEMLOSS
ADD CONSTRAINT UNIQUEITEMLOSS_INVOICEFREQLOSS FOREIGN KEY (INVOICEFREQUENCYLOSSPERIOD_ID) REFERENCES INVOICEFREQUENCYLOSSPERIOD (INVOICEFREQUENCYLOSSPERIOD_ID);


---PRODUCT table---

ALTER TABLE PRODUCT
	ADD (CHARGELOSSFORPOOL VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField117 CHECK (CHARGELOSSFORPOOL IN ('Y', 'N')));


--insert into invoicefrequencylossperiod

insert into invoicefrequencylossperiod
select seq_invoicefrequencylossperiod.nextval,
  pif.invoicefrequency_id,
  lp.periodenddate,
  (select nvl(max(lp2.periodenddate), lp.periodenddate - 28)
    from lossperiods lp2
    where lp2.periodenddate < lp.periodenddate),
  lp.year,
  lp.period,
  1,
  sysdate
from lossperiods lp, plantinvoicefrequency pif
where pif.documenttype = 1;

commit;

--insert into lossgracescheme

insert into lossgracescheme
select seq_lossgracescheme.nextval,
'-',
1,
sysdate
from dual;

commit;

--insert into lossgraceschemedetail

insert into lossgraceschemedetail
select
lgs.lossgracescheme_id,
56,
100,
1,
sysdate
from lossgracescheme lgs;

commit;

insert into lossgraceschemedetail
select
lgs.lossgracescheme_id,
337,
50,
1,
sysdate
from lossgracescheme lgs;

commit;

--insert into lossgracescheme_desc

insert into lossgracescheme_desc
select
lgs.lossgracescheme_id,
l.language_id,
'-',
1,
sysdate
from lossgracescheme lgs, languages l
where l.translatedescription = 'Y';

commit;

--update customer

update customer c
   set c.lossgracescheme_id =
       (select lgs.lossgracescheme_id
          from lossgracescheme lgs
         where lgs.code = '-')
 where c.chargelossforpoolitems = 1;

commit;

--update product

update product p
set p.chargelossforpool = 'Y'
where p.uniquepoolitem = 'Y';

commit;

--update plpc

update productlistpercustomer plpc
  set inventorybillingtype = 5
  where (select c.chargelossforpoolitems from customer c where c.customer_id = plpc.customer_id) = 1
  and (select p.uniquepoolitem from product p where p.product_id = plpc.product_id) = 'Y'
  and plpc.billingmethod in (3, 4)
  and plpc.inventorybillingtype = 0
  and (select dbs.code from databasesettings dbs) = 'BCH';

commit;

--update uniqueitemloss table

create or replace procedure update_uniqueitemloss is  
begin
  for c_lp in (select * from lossperiods lp) loop
    update uniqueitemloss uil
     set uil.invoicefrequencylossperiod_id = (select iflp.invoicefrequencylossperiod_id
                                              from invoicefrequencylossperiod iflp
                                              where iflp.enddate = c_lp.periodenddate
                                              and iflp.invoicefrequency_id =
                                                  (select c.invoicefrequency_id
                                                   from customer c, customer_link cl
                                                   where c.customer_id = cl.customer_id
                                                   and cl.customer_link_id = uil.customer_link_id))
     where uil.lossperiods_id = c_lp.lossperiods_id;
     commit;
  end loop;
end update_uniqueitemloss;
/

execute update_uniqueitemloss;

drop procedure update_uniqueitemloss;