--ABS-55250/ABS-61564
------------- INTERNETORDERLINE -------------
ALTER TABLE INTERNETORDERLINE ADD (MAXORDERQTY NUMBER NULL);

COMMENT ON COLUMN INTERNETORDERLINE.MAXORDERQTY
  IS 'Maximum order quantity when ordering via web or PDA';

------------- WEBORDER -------------
ALTER TABLE WEBORDER ADD (MAXORDERQTY NUMBER NULL);

COMMENT ON COLUMN WEBORDER.MAXORDERQTY
  IS 'Maximum order quantity when ordering via web';
   
------------- MAXORDERQUANTITY -------------
CREATE TABLE MAXORDERQUANTITY
( PRODUCTLISTPERDISTRIBPOINT_ID NUMBER NOT NULL,
  DAY_ID                        NUMBER NOT NULL,
  QUANTITY                      NUMBER DEFAULT 0 NOT NULL,
  PREVIOUSQUANTITY              NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID                 NUMBER NOT NULL,
  TIMESTAMP                     DATE NOT NULL);
  
ALTER TABLE MAXORDERQUANTITY
  ADD CONSTRAINT XPKMAXORDERQUANTITY PRIMARY KEY (PRODUCTLISTPERDISTRIBPOINT_ID, DAY_ID);
  
CREATE INDEX XIF1MAXORDERQUANTITY ON MAXORDERQUANTITY (PRODUCTLISTPERDISTRIBPOINT_ID ASC);
CREATE INDEX XIF2MAXORDERQUANTITY ON MAXORDERQUANTITY (DAY_ID ASC);

ALTER TABLE MAXORDERQUANTITY
  ADD CONSTRAINT MAXORDERQUANTITY_PLPD FOREIGN KEY (PRODUCTLISTPERDISTRIBPOINT_ID)
  REFERENCES PRODUCTLISTPERDISTRIBPOINT (PRODUCTLISTPERDISTRIBPOINT_ID) ON DELETE CASCADE;
ALTER TABLE MAXORDERQUANTITY
  ADD CONSTRAINT MAXORDERQUANTITY_DAY FOREIGN KEY (DAY_ID)
  REFERENCES DAY (DAY_ID);

------------- CUSTOMERINTERNETOPTIONS -------------
ALTER TABLE CUSTOMERINTERNETOPTIONS ADD (MAXORDERQTYWEB NUMBER DEFAULT 0 NOT NULL 
                                        CONSTRAINT MAXORDERQTYWEB_VALID1 CHECK (MAXORDERQTYWEB IN (0, 1)));
 
COMMENT ON COLUMN CUSTOMERINTERNETOPTIONS.MAXORDERQTYWEB
  IS 'This attribute indicates if it is allowed to define a maximum number of items to order via web or PDA at PLPC or CPL level.
Possible values:
0 = No maximum may be defined,
1 = Maximum may be defined on product, PLPC or CPL level';

-- create the system setting when it does not exist yet for all plants, setting its initial value to -1 (instead of the regular default 0)

create or replace procedure tempCreateSS as
begin
CreateSystemSetting('Linen Recording', 'MAXORDERQTYWEBPDA', '-1', 'I', '0', 'N', 'N', 0, 2, '', 'This setting controls if a maximum number of items to be ordered can be defined at CPL level. This maximum (if any) will be taken into account when ordering items via a PDA or via the Web Portal.'|| chr(10) ||'Possible values'|| chr(10) ||'0 = No maximum. application behaves like before'|| chr(10) ||'1 = Maximum can be defined on product level, PLPC level or CPL level'|| chr(10) ||'2 = Defined on customer level if any maximum can be defined', 'N');
end tempCreateSS;
/
execute tempCreateSS;

drop procedure tempCreateSS;

update systemsetting sn
   set sn.value =
       nvl((select decode(ov.value, '2', '1', '0')
          from systemsetting ov
         where ov.plant_id = sn.plant_id
           and ov.name = 'MINMAXQTYTODELIVER'), '0')
 where sn.name = 'MAXORDERQTYWEBPDA'
   and sn.value = '-1';
commit;

-- Option 2 will no longer be used as of release 9.02.01, from that moment on it will be replaced by option 1 of system setting MAXORDERQTYWEBPDA
update systemsetting sn
   set sn.value = '0'
 where sn.name = 'MINMAXQTYTODELIVER'
   and sn.value = '2';
commit;

-- update all customers that belong to plant that has new setting '1' and set the customer value accordingly
update customerinternetoptions cio
   set cio.maxorderqtyweb = 1
 where cio.customer_id in
       (select cu.customer_id
          from plant pl, businessunit bu, customer cu
         where bu.plant_id = pl.plant_id
           and cu.defaultbusinessunit_id = bu.businessunit_id
           and exists (select 1
                  from systemsetting s
                 where s.plant_id = pl.plant_id
                   and s.name = 'MAXORDERQTYWEBPDA'
                   and s.value = 1))
   and cio.maxorderqtyweb = 0;
commit;
   

