--Order 20010986.50

CREATE SEQUENCE SEQ_CUSTOMERSPECIALCHARGEPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERSPECIALCHARGEPRICE
(
	CUSTOMERSPECIALCHARGEPRICE_ID NUMBER NOT NULL ,
	CUSTOMER_ID          NUMBER NOT NULL ,
	SPECIALCHARGE_ID     NUMBER NOT NULL ,
	STARTDATE            DATE NOT NULL ,
	PRICE                NUMBER(11,6) NULL ,
	PERCENTAGE           NUMBER NULL ,
	MINIMUMAMOUNT        NUMBER(28,6) NULL ,
	MAXIMUMAMOUNT        NUMBER(28,6) NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;

ALTER TABLE CUSTOMERSPECIALCHARGEPRICE
ADD CONSTRAINT  XPKCUSTOMERSPECIALCHARGEPRICE PRIMARY KEY (CUSTOMERSPECIALCHARGEPRICE_ID) 
USING INDEX TABLESPACE INDX;

CREATE  INDEX XIF1CUSTOMERSPECIALCHARGEPRICE ON CUSTOMERSPECIALCHARGEPRICE
(CUSTOMER_ID   ASC) TABLESPACE INDX;

CREATE  INDEX XIF2CUSTOMERSPECIALCHARGEPRICE ON CUSTOMERSPECIALCHARGEPRICE
(SPECIALCHARGE_ID   ASC) TABLESPACE INDX;

CREATE UNIQUE INDEX XAK1CUSTOMERSPECIALCHARGEPRICE ON CUSTOMERSPECIALCHARGEPRICE
(CUSTOMER_ID   ASC,SPECIALCHARGE_ID   ASC,STARTDATE   ASC) TABLESPACE INDX;


CREATE SEQUENCE SEQ_GENERALSPECIALCHARGEPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE GENERALSPECIALCHARGEPRICE
(
	GENERALSPECIALCHARGEPRICE_ID NUMBER NOT NULL ,
	GENERALPRICELIST_ID  NUMBER NOT NULL ,
	SPECIALCHARGE_ID     NUMBER NOT NULL ,
	STARTDATE            DATE NOT NULL ,
	PRICE                NUMBER(11,6) NOT NULL ,
	PERCENTAGE           NUMBER NOT NULL ,
	MINIMUMAMOUNT        NUMBER(28,6) NOT NULL ,
	MAXIMUMAMOUNT        NUMBER(28,6) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;



COMMENT ON COLUMN GENERALSPECIALCHARGEPRICE.PRICE IS 'When  the special charge is of the type “Fixed amount”';




COMMENT ON COLUMN GENERALSPECIALCHARGEPRICE.PERCENTAGE IS 'When the special charge is of the type “Percentage”';




COMMENT ON COLUMN GENERALSPECIALCHARGEPRICE.MINIMUMAMOUNT IS 'When the special charge is of the type “Percentage”';




COMMENT ON COLUMN GENERALSPECIALCHARGEPRICE.MAXIMUMAMOUNT IS 'When the special charge is of the type “Percentage”';

ALTER TABLE GENERALSPECIALCHARGEPRICE
ADD CONSTRAINT  XPKGENERALSPECIALCHARGE PRIMARY KEY (GENERALSPECIALCHARGEPRICE_ID)
USING INDEX TABLESPACE INDX;

CREATE  INDEX XIF1GENERALSPECIALCHARGEPRICE ON GENERALSPECIALCHARGEPRICE
(GENERALPRICELIST_ID   ASC) TABLESPACE INDX;


CREATE UNIQUE INDEX XAK1GENERALSPECIALCHARGE ON GENERALSPECIALCHARGEPRICE
(GENERALPRICELIST_ID   ASC,SPECIALCHARGE_ID   ASC,STARTDATE   ASC) TABLESPACE INDX;



CREATE  INDEX XIF2GENERALSPECIALCHARGE ON GENERALSPECIALCHARGEPRICE
(SPECIALCHARGE_ID   ASC) TABLESPACE INDX;



COMMENT ON COLUMN GENERALPRICELIST.PRICETYPE IS '1 = Wash
2 = Rent
3 = Replacement
4 = Sales
5 = Weight
6 = Repair
7 = Emblem
8 = Emblem Template
9 = Garment Insurance
10 = Special Charge';



COMMENT ON COLUMN PRICELISTPERCUSTOMER.PRICETYPE IS '1 = Wash
2 = Rent
3 = Replacement
4 = Sales
5 = Weight
6 = Repair
7 = Emblem
8 = Emblem Template
9 = Garment Insurance
10 = Special Charge';



ALTER TABLE CUSTOMERSPECIALCHARGEPRICE
ADD CONSTRAINT CUSTOMERSPECCHARGEPRICE_CUSTOM FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;



ALTER TABLE CUSTOMERSPECIALCHARGEPRICE
ADD CONSTRAINT CUSTOMERSPECCHARGEPRICE_SPECIA FOREIGN KEY (SPECIALCHARGE_ID) REFERENCES SPECIALCHARGE (SPECIALCHARGE_ID) ON DELETE CASCADE;



ALTER TABLE GENERALSPECIALCHARGEPRICE
ADD CONSTRAINT GENERALSPECCHARGEPRICE_GENERAL FOREIGN KEY (GENERALPRICELIST_ID) REFERENCES GENERALPRICELIST (GENERALPRICELIST_ID) ON DELETE CASCADE;



ALTER TABLE GENERALSPECIALCHARGEPRICE
ADD CONSTRAINT GENERALSPECCHARGEPRICE_SPECIAL FOREIGN KEY (SPECIALCHARGE_ID) REFERENCES SPECIALCHARGE (SPECIALCHARGE_ID);

--insert into PriceListPerCustomer

insert into pricelistpercustomer
  (pricelistpercustomer_id,
   systemuser_id,
   timestamp,
   dateinactive,
   customer_id,
   dateactive,
   pricetype,
   priority)
  select seq_pricelistpercustomer.nextval,
         getminsystemuser(),
         sysdate,
         to_date('31-12-2099', 'dd-mm-yyyy'),
         t.*
    from (select distinct csc.customer_id,
                          to_date('01-01-2000', 'dd-mm-yyyy') dateactive,
                          10 pricetype,
                          1 priority
            from customerspecialcharge csc) t;


--insert into CustomerSpecialChargePrice

insert into customerspecialchargeprice cscp
  select seq_customerspecialchargeprice.nextval,
         csc.customer_id,
         csc.specialcharge_id,
         (case csc.overruled
           when 'Y' then
            coalesce(csc.startdate, sysdate - 364)
           when 'N' then
            sp.startdate
         END) startdate,
         (case csc.overruled
           when 'Y' then
            csc.newspecificamount
           when 'N' then
            sp.newamount
         end) price,
         (case csc.overruled
           when 'Y' then
            csc.newspecificpercentage
           when 'N' then
            sp.newpercentage
         end) percentage,
         (case csc.overruled
           when 'Y' then
            csc.minimumamount
           when 'N' then
            sp.minimumamount
         end) minimumamount,
         (case csc.overruled
           when 'Y' then
             case when csc.minimumamount = 0 and csc.maximumamount = 0 then null
                  else csc.maximumamount
             end   
           when 'N' then
            sp.maximumamount
         end) maximumamount,
         getminsystemuser(),
         sysdate
    from customerspecialcharge csc, specialcharge sp
   WHERE CSC.SPECIALCHARGE_ID = SP.SPECIALCHARGE_ID;

insert into customerspecialchargeprice cscp
  select seq_customerspecialchargeprice.nextval,
         csc.customer_id,
         csc.specialcharge_id,
         (case csc.overruled
           when 'Y' then
            csc.startdate - 365
           when 'N' then
            sp.startdate - 365
         END) startdate,
         (case csc.overruled
           when 'Y' then
            csc.oldspecificamount
           when 'N' then
            sp.oldamount
         end) price,
         (case csc.overruled
           when 'Y' then
            csc.oldspecificpercentage
           when 'N' then
            sp.oldpercentage
         end) percentage,
         (case csc.overruled
           when 'Y' then
            csc.minimumamount
           when 'N' then
            sp.minimumamount
         end) minimumamount,
         (case csc.overruled
           when 'Y' then
             case when csc.minimumamount = 0 and csc.maximumamount = 0 then null
                  else csc.maximumamount
             end
           when 'N' then
            sp.maximumamount
         end) maximumamount,
         getminsystemuser(),
         sysdate
    from customerspecialcharge csc, specialcharge sp
   WHERE CSC.SPECIALCHARGE_ID = SP.SPECIALCHARGE_ID
     and (csc.oldspecificamount != 0 or csc.oldspecificpercentage != 0
     or sp.oldpercentage != 0 or sp.oldamount != 0);