DECLARE

    CURSOR C1 IS 
    SELECT DISTINCT INVENTORY_ITEM_ID 
    FROM RULE_INTERIM_1;
    
    TYPE X1 IS TABLE OF C1%ROWTYPE INDEX BY PLS_INTEGER;
    Z1 X1;

    l_strategy_source NUMBER;
    l_psl_strategy VARCHAR2(20);
BEGIN


    OPEN C1;
    LOOP
       FETCH C1 BULK COLLECT INTO Z1 LIMIT 50001;
       FOR INDX1 IN 1 .. Z1.COUNT LOOP
       
       -- adding the Priority into logic for calculating the strategy source and Psl Strategy flag
       
       
       SELECT max(to_number(temp.STRATEGY_SOURCE)) into l_strategy_source
                      FROM RULE_INTERIM_1 TEMP,STRATEGY_RULES CS 
                      WHERE temp.strategy_rule_id=cs.strategy_rule_id 
                      AND INVENTORY_ITEM_ID = Z1(INDX1).inventory_item_id
                      AND NVL(priority,-1) in (SELECT NVL(min(cs.priority),-1)
                      FROM RULE_INTERIM_1 TEMP,STRATEGY_RULES CS 
                      WHERE temp.strategy_rule_id=cs.strategy_rule_id AND 
               INVENTORY_ITEM_ID = Z1(INDX1).inventory_item_id);
       
       SELECT DECODE(psl_strategy,
       5, 'Non-PSL',
       4, 'Mixed (P, LD, L)', 
       3, 'Mixed (P Only)',
       2, 'PSL (P, LD, L)',
       1, 'PSL (P Only)', '') into l_psl_strategy
       FROM (SELECT temp.psl_strategy
       FROM RULE_INTERIM_1 TEMP,STRATEGY_RULES CS 
       WHERE temp.strategy_rule_id=cs.strategy_rule_id 
       AND INVENTORY_ITEM_ID = Z1(INDX1).inventory_item_id
       AND NVL(priority,-1) in (SELECT NVL(min(cs.priority),-1)
       FROM RULE_INTERIM_1 TEMP,STRATEGY_RULES CS 
       WHERE temp.strategy_rule_id=cs.strategy_rule_id AND 
INVENTORY_ITEM_ID = Z1(INDX1).inventory_item_id ) GROUP BY temp.psl_strategy ) WHERE ROWNUM < 2;
       
       
          UPDATE PART EMPA
          SET strategy_source = l_strategy_source,
          psl_strategy =  l_psl_strategy
          WHERE INVENTORY_ITEM_ID = Z1(INDX1).inventory_item_id;

       END LOOP;
    EXIT WHEN C1%NOTFOUND;
    END LOOP;
    CLOSE C1;

       
/*-----------------------------
|                             |
|       FINAL COMMIT          |
|                             |
+-----------------------------*/
       COMMIT;


EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_stack());
       ROLLBACK;
END;
/