Bulk Collect

 Bulk Collect

CREATE OR REPLACE PROCEDURE po_uom_conversion

                      ( pr_act_shipment_stdate  IN DATE,

                        pr_act_shipment_eddate  IN DATE

                        ) AS

  CURSOR lcu_initialextract_data

   IS

  SELECT ooh.order_number

        ,ooh.ordered_date

        ,ooh.header_id

        ,ool.line_number||'.'||ool.shipment_number line_number

        ,ool.ordered_quantity

        ,ool.order_quantity_uom

        ,item.description ordered_item_desc

        ,ool.ship_from_org_id organization_id

        ,ool.line_id

        ,ool.top_model_line_id

        ,ool.org_id

    FROM  oe_order_headers_all ooh

         ,oe_order_lines_all ool

     ,mtl_system_items_b item

  WHERE 1=1

    AND ool.actual_shipment_date >= pr_act_shipment_stdate

    AND ool.actual_shipment_date <= pr_act_shipment_eddate

    --AND ooh.header_id = p_so_order_no

    AND item.inventory_item_id = ool.inventory_item_id

    AND item.organization_id = ool.ship_from_org_id

    AND ool.header_id = ooh.header_id

    AND ooh.cancelled_flag = 'N'

    AND ooh.booked_flag = 'Y'

    AND ool.cancelled_flag = 'N'

    AND ool.booked_flag = 'Y' ; 

 

    gn_bulk_limit                 NUMBER       :=10000;

 

    --std_rec standard_conversions%ROWTYPE;

    --invalid_conversion      EXCEPTION;

  

   TYPE data_tbl_typ IS TABLE OF lcu_initialextract_data%ROWTYPE INDEX BY BINARY_INTEGER;

   lt_data_tbl_typ   data_tbl_typ;

   std_index               NUMBER;

 

BEGIN

 

      execute IMMEDIATE 'TRUNCATE TABLE abc';

 

      OPEN lcu_initialextract_data;

      LOOP

     

      lt_data_tbl_typ.DELETE;

      FETCH lcu_initialextract_data BULK COLLECT INTO lt_data_tbl_typ LIMIT gn_bulk_limit;

         IF lt_data_tbl_typ.COUNT > 0 THEN

            FOR i IN 1..lt_data_tbl_typ.COUNT

            LOOP

           

               BEGIN

            

                 INSERT INTO abc (A,B,C,D) VALUES(  lt_data_tbl_typ(i).order_number       

                                                   ,lt_data_tbl_typ(i).ordered_date               

                                                   ,lt_data_tbl_typ(i).line_number

                           ,lt_data_tbl_typ(i).ordered_item_desc

                                                  ) ;

                         COMMIT;

               EXCEPTION

                WHEN OTHERS THEN

              

                   DBMS_OUTPUT.PUT_LINE (' Error Message :-> '||SQLERRM);

                                                     

               END;                                 

                                        

            END LOOP;                                     

               

         END IF;--IF lt_data_tbl_typ.COUNT > 0 THEN     

     

      EXIT WHEN lcu_initialextract_data%NOTFOUND;

      END LOOP;

      CLOSE lcu_initialextract_data;

     NULL;

 EXCEPTION

  WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE (' Error Message :-> '||SQLERRM);

   RAISE;

 END po_uom_conversion;

/

Show errors;

/

 ----------------------------------

 CREATE INDEX XX_OE_ORDER_LINES_N10 ON OE_ORDER_LINES_ALL

(actual_shipment_date)

 BEGIN

po_uom_conversion ('01-FEB-2003','05-FEB-2003');

END;

/

BEGIN

EXECUTE po_uom_conversion (90017);

END;

/