Inventory Items Update API/Interface Script

/**************************API******************************/
DECLARE
   l_item_tbl_typ                               ego_item_pub.item_tbl_type;
   x_item_table                                 ego_item_pub.item_tbl_type;
   x_inventory_item_id                          mtl_system_items_b.inventory_item_id%TYPE;
   x_organization_id                            mtl_system_items_b.organization_id%TYPE;
   x_return_status                              VARCHAR2 (1);
   x_msg_count                                  NUMBER (10);
   x_msg_data                                   VARCHAR2 (1000);
   x_message_list                               error_handler.error_tbl_type;
 
   CURSOR lcu_validated_data(cp_status_flag VARCHAR2) IS
      SELECT ORG_ID,
             inventory_item_id
        FROM INV_ITEM_CONV PIR
       WHERE PIR.status_flag = cp_status_flag
       AND EXISTS (select 1 from mtl_system_items_b where price_tolerance_percent = 0 AND segment1 =
       PIR.ITEM_CODE AND ORGANIZATION_ID = PIR.ORG_ID);
 
BEGIN


   FOR i IN lcu_validated_data('SS') LOOP
           --Setting FND global variables.
           --Replace MFG user name with appropriate user name.
        /*   fnd_global.apps_initialize (11224
                                     , 20634
                                     , 401
                                      );*/
         --FIRST Item definition
         l_item_tbl_typ (1).transaction_type := 'UPDATE';   -- Replace this with 'UPDATE' for update transaction.
         l_item_tbl_typ (1).inventory_item_id := i.inventory_item_id;
         l_item_tbl_typ (1).organization_id := i.org_id;
         l_item_tbl_typ (1).price_tolerance_percent := '';
         --DBMS_OUTPUT.put_line ('=====================================');
         --DBMS_OUTPUT.put_line ('Calling EGO_ITEM_PUB.Process_Items API');
         ego_item_pub.process_items (p_api_version        => 1.0
                                   , p_init_msg_list      => fnd_api.g_true
                                   , p_commit             => fnd_api.g_true
                                   , p_item_tbl           => l_item_tbl_typ
                                   , x_item_tbl           => x_item_table
                                   , x_return_status      => x_return_status
                                   , x_msg_count          => x_msg_count
                                    );
         --DBMS_OUTPUT.put_line ('==================================');
         --DBMS_OUTPUT.put_line ('Return Status ==>' || x_return_status);
   
         IF (x_return_status = fnd_api.g_ret_sts_success)
         THEN
            FOR i IN 1 .. x_item_table.COUNT
            LOOP
               DBMS_OUTPUT.put_line ('Inventory Item Id :' || TO_CHAR (x_item_table (i).inventory_item_id));
               --DBMS_OUTPUT.put_line ('Organization Id   :' || TO_CHAR (x_item_table (i).organization_id));
            END LOOP;
         ELSE
            DBMS_OUTPUT.put_line ('Error Messages :');
            error_handler.get_message_list (x_message_list      => x_message_list);
   
            FOR i IN 1 .. x_message_list.COUNT
            LOOP
               DBMS_OUTPUT.put_line (x_message_list (i).MESSAGE_TEXT);
            END LOOP;
         END IF;
   
         --DBMS_OUTPUT.put_line ('==================================');
       
  END LOOP;
 
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception Occured :');
      DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
      DBMS_OUTPUT.put_line ('=====================================');
END;

/**************************Interface******************************/

DECLARE

LN_BULK_ERROR_CNT NUMBER;

GN_BULK_LIMIT NUMBER := 500;

CURSOR lcu_validated_data(cp_status_flag VARCHAR2) IS

      SELECT ORG_ID,

             last_update_date,

             last_updated_by,

             creation_date,

             created_by,

             last_update_login,

             program_update_date,

             ITEM_CODE,

             DESCRIPTION,

             PRIMARY_UOM,

             expense_account_id,

             asset_category_id,

             buyer_id,

             LIST_PRICE,

             PRIMARY_UNIT_MEASURE

        FROM INV_ITEM_CONV PIR

       WHERE PIR.status_flag = cp_status_flag;

      

       TYPE validated_data_tbl_typ IS TABLE OF lcu_validated_data%ROWTYPE INDEX BY BINARY_INTEGER;

    lt_validated_data_tbl_typ validated_data_tbl_typ;

BEGIN

  OPEN lcu_validated_data('SS');

    LOOP

   

      lt_validated_data_tbl_typ.DELETE;

   

      EXIT WHEN lcu_validated_data%NOTFOUND;

      FETCH lcu_validated_data BULK COLLECT

        INTO lt_validated_data_tbl_typ LIMIT gn_bulk_limit;

   

      ln_bulk_error_cnt := 0;

   

      IF lt_validated_data_tbl_typ.COUNT > 0 THEN

     

        BEGIN

       

          --===========

          --Bulk Insert

          --===========

          FORALL i IN 1 .. lt_validated_data_tbl_typ.COUNT SAVE EXCEPTIONS

            INSERT INTO apps.mtl_system_items_interface

              (organization_id,

               last_update_date,

               last_updated_by,

               creation_date,

               created_by,

               last_update_login,

               summary_flag,

               enabled_flag,

               description,

               segment1,

               segment2,

               purchasing_item_flag,

               shippable_item_flag,

               customer_order_flag,

               internal_order_flag,

               service_item_flag,

               inventory_item_flag,

               eng_item_flag,

               inventory_asset_flag,

               purchasing_enabled_flag,

               customer_order_enabled_flag,

               internal_order_enabled_flag,

               so_transactions_flag,

               mtl_transactions_enabled_flag,

               stock_enabled_flag,

               bom_enabled_flag,

               build_in_wip_flag,

               inspection_required_flag

               -- ,bom_item_type                                   

              ,

               pick_components_flag,

               replenish_to_order_flag,

               atp_components_flag,

               atp_flag,

               inventory_item_status_code,

               min_minmax_quantity,

               max_minmax_quantity,

               vendor_warranty_flag,

               serviceable_product_flag,

               invoiceable_item_flag,

               invoice_enabled_flag,

               must_use_approved_vendor_flag,

               request_id,

               program_application_id,

               program_id,

               program_update_date,

               outside_operation_flag,

               costing_enabled_flag,

               cycle_count_enabled_flag,

               auto_created_config_flag,

               process_flag,

               item_number,

               template_id,

               transaction_type,

               transaction_id,

               set_process_id,

               lot_control_code,

               primary_unit_of_measure,

               primary_uom_code,

               attribute_category,

               attribute11,

               attribute9,

               attribute18,

               hazardous_material_flag,

               EXPENSE_ACCOUNT,

               ASSET_CATEGORY_ID,

               BUYER_ID,

               QTY_RCV_EXCEPTION_CODE,

               LIST_PRICE_PER_UNIT,

               QTY_RCV_TOLERANCE,

               PRICE_TOLERANCE_PERCENT,

               ENFORCE_SHIP_TO_LOCATION_CODE,

               ALLOW_SUBSTITUTE_RECEIPTS_FLAG,

                ALLOW_UNORDERED_RECEIPTS_FLAG,

                ALLOW_EXPRESS_DELIVERY_FLAG,

                DAYS_EARLY_RECEIPT_ALLOWED,

                DAYS_LATE_RECEIPT_ALLOWED,

                RECEIPT_DAYS_EXCEPTION_CODE,

                RECEIVING_ROUTING_ID,

                ITEM_TYPE,

                SO_AUTHORIZATION_FLAG

                )

            VALUES

              (lt_validated_data_tbl_typ(i).org_id,

               lt_validated_data_tbl_typ(i).last_update_date,

               lt_validated_data_tbl_typ(i).last_updated_by,

               lt_validated_data_tbl_typ(i).creation_date,

               lt_validated_data_tbl_typ(i).created_by,

               lt_validated_data_tbl_typ(i).last_update_login,

               NULL, --lt_validated_data_tbl_typ(i).summary_flag 

               NULL, --lt_validated_data_tbl_typ(i).enabled_flag

               lt_validated_data_tbl_typ(i).description,

               lt_validated_data_tbl_typ(i).ITEM_CODE,

               NULL,

               'Y', --lt_validated_data_tbl_typ(i).purchasing_item_flag

               NULL, --lt_validated_data_tbl_typ(i).shippable_item_flag

               NULL, --lt_validated_data_tbl_typ(i).customer_order_flag

               NULL, --lt_validated_data_tbl_typ(i).internal_order_flag

               NULL, --lt_validated_data_tbl_typ(i).service_item_flag

               NULL, --lt_validated_data_tbl_typ(i).inventory_item_flag

               NULL ,--lt_validated_data_tbl_typ(i).eng_item_flag

               NULL ,--lt_validated_data_tbl_typ(i).inventory_asset_flag

               'Y', --lt_validated_data_tbl_typ(i).purchasing_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).customer_order_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).internal_order_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).so_transactions_flag

               NULL ,--lt_validated_data_tbl_typ(i).mtl_transactions_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).stock_enabled_flag 

               NULL, --lt_validated_data_tbl_typ(i).bom_enabled_flag 

               NULL ,--lt_validated_data_tbl_typ(i).build_in_wip_flag 

               NULL,

               --,'Y'    --lt_validated_data_tbl_typ(i).bom_item_type   

               NULL, --lt_validated_data_tbl_typ(i).pick_components_flag 

               NULL, --lt_validated_data_tbl_typ(i).replenish_to_order_flag                      

               --,null    --lt_validated_data_tbl_typ(i).base_item_id 

               NULL, --lt_validated_data_tbl_typ(i).atp_components_flag 

               NULL, --lt_validated_data_tbl_typ(i).atp_flag      

               'Active', --lt_validated_data_tbl_typ(i).inventory_item_status_code

               NULL,

               NULL,

               NULL, --lt_validated_data_tbl_typ(i).vendor_warranty_flag                            

               --,null    --lt_validated_data_tbl_typ(i).serviceable_component_flag

               NULL, --lt_validated_data_tbl_typ(i).serviceable_product_flag

               NULL ,--lt_validated_data_tbl_typ(i).invoiceable_item_flag                            

               --,null    --lt_validated_data_tbl_typ(i).tax_code     

               NULL, --lt_validated_data_tbl_typ(i).invoice_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).must_use_approved_vendor_flag 

               123456, --lt_validated_data_tbl_typ(i).request_id

               NULL,

               NULL,

               lt_validated_data_tbl_typ(i).program_update_date,

               NULL, --lt_validated_data_tbl_typ(i).outside_operation_flag

               NULL, --lt_validated_data_tbl_typ(i).costing_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).cycle_count_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).auto_created_config_flag           

               --      ,pfcinv_items_recordid_s.NEXTVAL    --lt_validated_data_tbl_typ(i).transaction_id

               1, --lt_validated_data_tbl_typ(i).process_flag     

               --      ,null    --lt_validated_data_tbl_typ(i).organization_code

               NULL, --lt_validated_data_tbl_typ(i).item_number

               --      ,null    --lt_validated_data_tbl_typ(i).copy_item_number

               --      ,lt_validated_data_tbl_typ(i).template_id

               NULL, --lt_validated_data_tbl_typ(i).template_id 

               'UPDATE', --lt_validated_data_tbl_typ(i).transaction_type

               NULL, --lt_validated_data_tbl_typ(i).transaction_id

               0, --lt_validated_data_tbl_typ(i).set_process_id                                  

               -- ,lt_validated_data_tbl_typ(i).long_description

               NULL ,--lt_validated_data_tbl_typ(i).lot_control_code

               lt_validated_data_tbl_typ(i).PRIMARY_UNIT_MEASURE, --lt_validated_data_tbl_typ(i).primary_unit_of_measure

               lt_validated_data_tbl_typ(i).PRIMARY_UOM,

               NULL, --lt_validated_data_tbl_typ(i).attribute_category

               NULL, --lt_validated_data_tbl_typ(i).attribute11

               NULL, --lt_validated_data_tbl_typ(i).attribute9

               NULL, --lt_validated_data_tbl_typ(i).attribute18

               NULL, --lt_validated_data_tbl_typ(i).hazardous_material_flag

               lt_validated_data_tbl_typ(i).expense_account_id,

               lt_validated_data_tbl_typ(i).asset_category_id,

               lt_validated_data_tbl_typ(i).BUYER_ID,

               'REJECT',

               lt_validated_data_tbl_typ(i).LIST_PRICE,

               0,

                NULL,

               'WARNING', --ENFORCE_SHIP_TO_LOCATION_CODE

               'N', --ALLOW_SUBSTITUTE_RECEIPTS_FLAG

               'N', --ALLOW_UNORDERED_RECEIPTS_FLAG

               'Y', --ALLOW_EXPRESS_DELIVERY_FLAG

               5, --DAYS_EARLY_RECEIPT_ALLOWED

               5, --DAYS_LATE_RECEIPT_ALLOWED

               'WARNING', --RECEIPT_DAYS_EXCEPTION_CODE

               3, --RECEIVING_ROUTING_ID

               'P', --ITEM_TYPE

               1 --SO_AUTHORIZATION_FLAG

               );

       

        EXCEPTION

          WHEN OTHERS THEN

         

            dbms_output.put_line('Bulk Error Count :-> ' ||

                                   ln_bulk_error_cnt);

            dbms_output.put_line('Bulk Insert Error Message :-> ' ||

                                   SQLERRM);

                     

            ln_bulk_error_cnt := SQL%BULK_EXCEPTIONS.COUNT;

            FOR i IN 1 .. ln_bulk_error_cnt LOOP

              dbms_output.put_line('Error# ' || i || ' at iteration# ' || SQL%BULK_EXCEPTIONS(i)

                                     .ERROR_INDEX);

              dbms_output.put_line('Error Message is ' ||

                                     SQLERRM(-SQL%BULK_EXCEPTIONS(i)

                                             .ERROR_CODE));

            END LOOP;

            --ROLLBACK;

          --RAISE;

       

        END;

     

      END IF;

   

      COMMIT;

   

    END LOOP;

    CLOSE lcu_validated_data;

END;