/* Formatted on 2010/03/02 18:12 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR c1
   IS
      SELECT DISTINCT inventory_item_id
                 FROM rule_table;

   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 50000;

      FOR indx1 IN 1 .. z1.COUNT
      LOOP
         flag := flag + 1;

         -- 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_table temp, strategy_rule 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_table temp, strategy_rule 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_table temp, strategy_rule 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_table temp, strategy_rule 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_attributes empa
            SET strategy_source = l_strategy_source,
                psl_strategy = l_psl_strategy
          WHERE inventory_item_id = z1 (indx1).inventory_item_id;

         IF (flag > 5000)
         THEN
            INSERT INTO TEST
                 VALUES (SYSDATE, flag);

            COMMIT;
            flag := 0;
         END IF;
      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;
/