-- ABS-35341
--------------- SQSTOCKCHARGE --------------- 
ALTER TABLE SQSTOCKCHARGE ADD (QTYGENERALSTOCKNEW NUMBER NULL,
                               QTYGENERALSTOCKUSED NUMBER NULL,
                               QTYCUSTOMERRELATEDNEW NUMBER NULL,
                               QTYCUSTOMERRELATEDUSED NUMBER NULL,
                               QTYCUSTOMEROWNEDNEW NUMBER NULL,
                               QTYCUSTOMEROWNEDUSED NUMBER NULL,
                               QTYLEASEDSTOCKNEW NUMBER NULL,
                               QTYLEASEDSTOCKUSED NUMBER NULL);
                               

--------------- PRODUCTLISTPERCUSTOMER ---------------   
ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (CHARGERENTONSTOCK VARCHAR2(1) DEFAULT 'N' NOT NULL 
                                        CONSTRAINT YN_VALIDATIONFIELD416 CHECK (CHARGERENTONSTOCK IN ('Y', 'N')));

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.CHARGERENTONSTOCK
  IS 'Can only be set to "Y" when module RENTONSTOCK is in the license';

--------------- CUSTOMERSTOCKCHARGEINFO ---------------    
CREATE TABLE CUSTOMERSTOCKCHARGEINFO
( CUSTOMER_ID            NUMBER NOT NULL,
  CHARGEGENERALSTOCKNEW  VARCHAR2(1) NOT NULL 
                         CONSTRAINT YN_VALIDATIONFIELD417 CHECK (CHARGEGENERALSTOCKNEW IN ('Y', 'N')),
  CHARGEGENERALSTOCKUSED VARCHAR2(1) NOT NULL 
                         CONSTRAINT YN_VALIDATIONFIELD418 CHECK (CHARGEGENERALSTOCKUSED IN ('Y', 'N')),
  CHARGECRSTOCKNEW       VARCHAR2(1) NOT NULL 
                         CONSTRAINT YN_VALIDATIONFIELD419 CHECK (CHARGECRSTOCKNEW IN ('Y', 'N')),
  CHARGECRSTOCKUSED      VARCHAR2(1) NOT NULL
                         CONSTRAINT YN_VALIDATIONFIELD420 CHECK (CHARGECRSTOCKUSED IN ('Y', 'N')),
  CHARGECOGSTOCKNEW      VARCHAR2(1) NOT NULL
                         CONSTRAINT YN_VALIDATIONFIELD421 CHECK (CHARGECOGSTOCKNEW IN ('Y', 'N')),
  CHARGECOGSTOCKUSED     VARCHAR2(1) NOT NULL 
                         CONSTRAINT YN_VALIDATIONFIELD422 CHECK (CHARGECOGSTOCKUSED IN ('Y', 'N')),
  CHARGELEASEDSTOCKNEW   VARCHAR2(1) NOT NULL
                         CONSTRAINT YN_VALIDATIONFIELD423 CHECK (CHARGELEASEDSTOCKNEW IN ('Y', 'N')),
  CHARGELEASEDSTOCKUSED  VARCHAR2(1) NOT NULL 
                         CONSTRAINT YN_VALIDATIONFIELD424 CHECK (CHARGELEASEDSTOCKUSED IN ('Y', 'N')),
  STOCKCHARGETYPE        NUMBER DEFAULT 0 NOT NULL
                         CONSTRAINT STOCKCHARGETYPE_VALID1_1 CHECK (STOCKCHARGETYPE IN (0,1)),
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL);

COMMENT ON COLUMN CUSTOMERSTOCKCHARGEINFO.STOCKCHARGETYPE
  IS '0 = Charge for all products
1 = Charge when defined in PLPC';

ALTER TABLE CUSTOMERSTOCKCHARGEINFO
  ADD CONSTRAINT XPKCUSTOMERSTOCKCHARGEINFO PRIMARY KEY (CUSTOMER_ID);

ALTER TABLE CUSTOMERSTOCKCHARGEINFO
  ADD CONSTRAINT CUSTOMERSTOCKCHARGEINFO_CUST FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
                            
  
create or replace procedure createCustomerStockChargeInfo is

cursor customerswithoutcsci is 
  select c.customer_id
    from customer c
   where c.chargestockcharge = 'Y'
     and c.customer_id not in
         (select csci.customer_id from CustomerStockChargeInfo csci);
         
begin 
  for customerID in customersWithoutCSCI loop
    begin 
      insert into CustomerStockChargeInfo csci
        (csci.customer_id,
         csci.chargegeneralstocknew,
         csci.chargegeneralstockused,
         csci.chargecrstocknew,
         csci.chargecrstockused,
         csci.chargecogstocknew,
         csci.chargecogstockused,
         csci.chargeleasedstocknew,
         csci.chargeleasedstockused,
         csci.stockchargetype,
         csci.systemuser_id,
         csci.timestamp)
      values
        (customerID.customer_id,
         'N',
         'N',
         'N',
         'Y',
         'N',
         'Y',
         'N',
         'Y',
         0,
         getminsystemuser(),
         sysdate);
         
       commit;
    end;
   end loop;
end createCustomerStockChargeInfo;
/
execute createCustomerStockChargeInfo;

drop procedure createCustomerStockChargeInfo;
