CREATE OR REPLACE PROCEDURE RESET_CHANGE_PARAM(
   process_date   IN   DATE DEFAULT TRUNC (SYSDATE)
)
IS
CURSOR  products_data is
   SELECT vi.item_id, vl.location_id, mt.store_min, mt.store_max, mt.facings, mt.fixture_fill, mt.action_code,
          mt.transit_time, mt.effective_date
 	 FROM dp.t_src_matrix mt,
       					items vi,
       					t_ep_Catal tc,
       					location vl, 
	   					t_ep_shinr ts
				 WHERE  (mt.action_code = 'CHG-LIVE'   OR
                       mt.action_code = 'CHANGEPLUS'   OR
                       mt.action_code = 'CHANGE_MINUS' OR
                       mt.action_code = 'CHG-RST'      OR
                       mt.action_code = 'CHANGE')
  				  AND  mt.catalog = tc.catal
  				  AND  tc.t_ep_catal_ep_id = vi.t_ep_catal_ep_id
  				  AND  mt.ship_to_number = ts.shinr
  				  AND  vl.t_ep_shinr_ep_id = ts.t_ep_shinr_ep_id;
				  
TYPE prod_tab IS TABLE OF products_data%ROWTYPE;
products_tab   prod_tab := prod_tab();


TYPE itemloc_typ IS TABLE OF sales_data.item_id%TYPE INDEX BY PLS_INTEGER;
TYPE stf_typ IS TABLE OF sales_data.store_max%TYPE INDEX BY PLS_INTEGER;
TYPE fixfill_typ IS TABLE OF sales_data.fixture_fill%TYPE INDEX BY PLS_INTEGER;
TYPE action_typ IS TABLE OF dp.t_src_matrix.action_code%TYPE INDEX BY PLS_INTEGER;
TYPE date_typ IS TABLE OF dp.t_src_matrix.effective_date%TYPE INDEX BY PLS_INTEGER;


action action_typ;
item itemloc_typ;
loc  itemloc_typ;


stmin stf_typ;
stmax stf_typ;
facing stf_typ;
fixfill fixfill_typ;

itemN itemloc_typ;
locN itemloc_typ;
stminN stf_typ;
stmaxN stf_typ;
facingN stf_typ;
fixfillN fixfill_typ;


--Date and time variables
trTime stf_typ;
efDate date_typ;
nb_err  Pls_integer ; 
total  Pls_integer := 0 ;  



v_start_date  DATE := SYSDATE;
v_end_date   DATE ;
cnt_rec    NUMBER := 0;
v_time  NUMBER := 0;
v_seq   NUMBER := 0;

BEGIN
    --Check the content
	BEGIN
		SELECT COUNT(1) INTO cnt_rec FROM dp.t_src_matrix
		WHERE ROWNUM < 2;
		EXCEPTION
  	WHEN NO_DATA_FOUND THEN NULL;
	END;

	BEGIN -- create table that will collect the statistics
		check_and_drop('alice_reset_param');

   		dynamic_ddl('CREATE TABLE alice_reset_param
            		(run_id number null,
             		categ_desc varchar2(100) NULL,
             		no_rec number null,
             		start_date date NULL,
             		end_date date NULL,
			 		time_elapsed number)');
	END;


IF cnt_rec > 0 THEN 
		OPEN products_data; 
LOOP
 FETCH products_data BULK COLLECT INTO products_tab LIMIT 999;

   FOR j IN products_tab.FIRST .. products_tab.LAST
   LOOP
      item (j) :=  products_tab (j).item_id;
       loc (j) :=  products_tab (j).location_id;
    action (j) :=  products_tab (j).action_code;
     stmin (j) :=  products_tab (j).store_min;
     stmax (j) :=  products_tab (j).store_max;
   fixfill (j) :=  products_tab (j).fixture_fill;
    facing (j) :=  products_tab (j).facings;
    trTime (j) :=  products_tab (j).transit_time;
    efDate (j) :=  products_tab (j).effective_date;
    
  IF action (j) = 'CHG-LIVE' THEN 
      FORALL i IN item.FIRST .. item.LAST
            UPDATE sales_data
               SET store_max = stmax(i),
                   store_min = stmin(i),
                     facings = facing(i),
                fixture_fill = fixfill(i)
             WHERE item_id = item(i)
               AND location_id = loc(i)
               AND store_max IS NOT NULL
               AND store_min IS NOT NULL
               AND facings IS NOT NULL;
      ELSE
   --This part will be for the CHG-RST
   -------------------------------------------------- 
   SELECT          m.item_id,
   				   m.location_id,
   				   NVL (s.store_max, m.store_max),
                   NVL (s.store_min, m.store_min),
                   NVL (s.facings, m.facings),
                   NVL (s.fixture_fill, m.fixture_fill)
 BULK COLLECT INTO itemN,
                   locN,
 				   stmaxN,
                   stminN,
                   facingN,
                   fixfillN
              FROM mdp_matrix m, sales_data s
             WHERE s.sales_date = trunc(process_date)
			   AND m.item_id = products_tab (j).item_id
               AND m.location_id = products_tab (j).location_id
               AND m.item_id = s.item_id
               AND m.location_id = s.location_id;
       
        FOR x IN itemN.FIRST .. itemN.LAST
          LOOP 
           IF     products_tab (j).store_max <> stmaxN (x)
               OR products_tab (j).store_Min <> stminN (x)
               OR products_tab (j).facings <> facingN (x)
               OR products_tab (j).fixture_fill <> fixfillN (x)
           THEN 
            FORALL x IN itemN.FIRST .. itemN.LAST
             UPDATE dr.sales_data
                  SET store_max = NVL (store_max, stmaxN (x)),
                      store_min = NVL (store_min, stminN (x)),
                      facings = NVL (facings, facingN (x)),
                      fixture_fill = NVL (fixture_fill, fixfillN (x))
                WHERE item_id = itemN (x)
                  AND location_id = locN (x)
                  AND sales_date <
                           TO_DATE (efDate (j), 'DD-MON-YYYY')
                         - (trTime (j));

               UPDATE dr.sales_data
                  SET store_max = NULL,
                      store_min = NULL,
                      facings = NULL,
                      fixture_fill = NULL
                WHERE item_id = itemN (x)
                  AND location_id = locN (x)
                  AND sales_date >=
                           TO_DATE (efDate (j), 'DD-MON-YYYY')
                         - (trTime (j));
      END IF;
        END LOOP; 
   END IF;
   END LOOP;
 
    v_seq := v_seq + 1;
    cnt_rec := products_tab.COUNT;
    v_end_date := SYSDATE;
    
    v_time := ROUND((v_end_date-v_start_date)*1440,5);
	INSERT INTO alice_reset_param
    VALUES(v_seq,'Bulk partial',cnt_rec, v_START_DATE,
	                                     v_end_date,
										 v_time);
	v_start_date := v_end_date;
	v_end_date := SYSDATE;
	COMMIT;
EXIT WHEN products_data%NOTFOUND;
END LOOP;
CLOSE products_data;
INSERT INTO alice_reset_param
SELECT max(run_id)+1, 'Bulk Total', SUM(no_rec), MIN(START_DATE), MAX(end_date), 
ROUND((MAX(end_date) - MIN(START_DATE))* 1440,5) FROM alice_reset_param;

COMMIT;
END IF;
END;

[ALIGN=left]
DECLARE

    CURSOR  chg_live_cur is
   SELECT vi.item_id, vl.location_id, mt.store_min, mt.store_max, mt.facings, mt.fixture_fill,
          mt.transit_time, mt.effective_date
 	 FROM dp.t_src_matrix mt,
       					items vi,
       					t_ep_Catal tc,
       					location vl, 
	   					t_ep_shinr ts
				 WHERE  mt.action_code = 'CHG-LIVE'
  				  AND  mt.catalog = tc.catal
  				  AND  tc.t_ep_catal_ep_id = vi.t_ep_catal_ep_id
  				  AND  mt.ship_to_number = ts.shinr
  				  AND  vl.t_ep_shinr_ep_id = ts.t_ep_shinr_ep_id
			      AND  ROWNUM < 50; 
			      
	CURSOR chg_rst_cur IS 
				SELECT vi.item_id, vl.location_id, mt.store_min, mt.store_max, mt.facings, mt.fixture_fill,
          			   mt.transit_time, mt.effective_date
 	 			  FROM dp.t_src_matrix mt,
       					items vi,
       					t_ep_Catal tc,
       					location vl, 
	   					t_ep_shinr ts
				 WHERE (mt.action_code = 'CHANGEPLUS'   OR
                       mt.action_code = 'CHANGE_MINUS' OR
                       mt.action_code = 'CHG-RST'      OR
                       mt.action_code = 'CHANGE')
				   AND mt.catalog = tc.catal
  				   AND tc.t_ep_catal_ep_id = vi.t_ep_catal_ep_id
  				   AND mt.ship_to_number = ts.shinr
  				   AND vl.t_ep_shinr_ep_id = ts.t_ep_shinr_ep_id
				   AND ROWNUM < 2;

	-- collection of scalars
	TYPE itemloc_typ IS TABLE OF sales_data.item_id%TYPE INDEX BY PLS_INTEGER;
    TYPE stf_typ IS TABLE OF sales_data.store_max%TYPE INDEX BY PLS_INTEGER;
    TYPE fixfill_typ IS TABLE OF sales_data.fixture_fill%TYPE INDEX BY PLS_INTEGER;
    TYPE action_typ IS TABLE OF dp.t_src_matrix.action_code%TYPE INDEX BY PLS_INTEGER;
    TYPE date_typ IS TABLE OF dp.t_src_matrix.effective_date%TYPE INDEX BY PLS_INTEGER;

action action_typ;
item itemloc_typ;
loc  itemloc_typ;


stmin stf_typ;
stmax stf_typ;
facing stf_typ;
fixfill fixfill_typ;

itemN itemloc_typ;
locN itemloc_typ;
stminN stf_typ;
stmaxN stf_typ;
facingN stf_typ;
fixfillN fixfill_typ;


--Date and time variables
trTime stf_typ;
efDate date_typ;
nb_err  Pls_integer ; 
total  Pls_integer := 0 ;  



v_start_date  DATE := SYSDATE;
v_end_date   DATE ;
cnt_rec    NUMBER := 0;
cnt_rec_ch NUMBER := 0;
v_time  NUMBER := 0;
v_seq   NUMBER := 0;


    -- create an exception handler for ORA-24381
    dml_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN 
 --Check the content of products_data
 
	BEGIN
		SELECT COUNT(1) INTO cnt_rec FROM dp.t_src_matrix
		WHERE action_code = 'CHG-LIVE';
		EXCEPTION
  	WHEN NO_DATA_FOUND THEN NULL;
	END;

 --Check the content of sales_cur
	BEGIN
		SELECT COUNT(1) INTO cnt_rec_ch FROM dp.t_src_matrix
		WHERE action_code = 'CHANGEPLUS'   OR
              action_code = 'CHANGE_MINUS' OR
              action_code = 'CHG-RST'      OR
              action_code = 'CHANGE';
		EXCEPTION
  	WHEN NO_DATA_FOUND THEN NULL;
	END;

 IF cnt_rec > 0 THEN 
	OPEN chg_live_cur; 
 LOOP
     FETCH chg_live_cur BULK COLLECT INTO 
	 item, loc, stmin, stmax, facing, fixfill, trTime,
	 efDate LIMIT 20;
	 
	 BEGIN 
	 FORALL i IN item.FIRST .. item.LAST SAVE EXCEPTIONS
            UPDATE sales_data
               SET store_max = stmax(i),
                   store_min = stmin(i),
                     facings = facing(i),
                fixture_fill = fixfill(i)
             WHERE item_id = item(i)
               AND location_id = loc(i)
               AND store_max IS NOT NULL
               AND store_min IS NOT NULL
               AND facings IS NOT NULL; 
     EXCEPTION
     WHEN dml_errors THEN NULL; 
        --FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
         --   DBMS_OUTPUT.put_line('Error in CHG-LIVE');
        --END LOOP;
    END;
EXIT WHEN chg_live_cur%NOTFOUND;
END LOOP;
CLOSE chg_live_cur;
ELSE NULL;
END IF;


--For Chg-Rst
 IF cnt_rec_ch > 0 THEN 
	OPEN chg_rst_cur; 
 LOOP
     FETCH chg_rst_cur BULK COLLECT INTO 
	 item, loc, stmin, stmax, facing, fixfill, trTime, efDate LIMIT 20;
     --Update with values from the cursor if sales_date < ef_date - trtime
	 BEGIN
	 FORALL i IN item.FIRST .. item.LAST save EXCEPTIONS 
	
	  UPDATE dr.sales_data
       SET store_max = NVL(store_max, stmax(i)),
           store_min = NVL(store_min, stmin(i)),
             facings = NVL (facings, facing(i)),
        fixture_fill = NVL (fixture_fill, fixfill(i))
     WHERE sales_date < TO_DATE (efDate(i), 'DD-MON-YYYY') - trTime(i) AND 
          (item_id, location_id) IN 
          (SELECT s.item_id, s.location_id
             FROM mdp_matrix m, sales_data s
            WHERE s.item_id = item(i)
              AND s.location_id = loc(i)
              AND s.sales_date = TRUNC(sysdate)
              AND s.item_id = m.item_id
              AND s.location_id = m.location_id
              AND (NVL (s.store_max, m.store_max) <> stmax(i)
               OR  NVL (s.store_min, m.store_min) <> stmin(i)
               OR  NVL (s.facings, m.facings) <> facing(i)
               OR  NVL (s.fixture_fill, m.fixture_fill) <> fixfill(i))
			);
    EXCEPTION
         WHEN dml_errors THEN NULL; 
        --FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
         --   DBMS_OUTPUT.put_line('Error in CHG-LIVE');
        --END LOOP;
    END;
    
	BEGIN
	 FORALL i IN item.FIRST .. item.LAST save EXCEPTIONS     
       --Set to NULL if sales_date > ef_date - transit time

               UPDATE dr.sales_data
                  SET store_max = NULL,
                      store_min = NULL,
                      facings = NULL,
                      fixture_fill = NULL
                WHERE item_id = item(i)
                  AND location_id = loc(i)
                  AND sales_date >=
                           TO_DATE (efDate(i), 'DD-MON-YYYY')
                         - (trTime(i));
     EXCEPTION
     WHEN dml_errors THEN NULL; 
        --FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
         --   DBMS_OUTPUT.put_line('Error in CHG-LIVE');
        --END LOOP;
     END;
  EXIT WHEN chg_rst_cur%NOTFOUND;
END LOOP;
CLOSE chg_rst_cur;
ELSE NULL;
END IF;


EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;[/ALIGN]