Monday, 8 January 2018

Inventory Item Interface to Upload Item in Oracle


In this post I will share you complete script to upload Inventory Items in Oracle through Item Interface in Oracle apps.


DECLARE
      CURSOR cursor_item_stg
      IS
        SELECT   * FROM ITEM_MASTER_STAG
                         
      v_child_org_id      VARCHAR2 (10);
      v_child_org_code    VARCHAR2 (10);
      v_master_org_id     VARCHAR2 (10);
      v_master_org_code   VARCHAR2 (10);
      l_template_id       VARCHAR2 (20);
      l_inv_id            VARCHAR2 (20);
      l_error             VARCHAR2 (1000);
      l_struct            VARCHAR2 (20);
      l_cat               VARCHAR2 (20);
      l_category          NUMBER;
      exist_num11         NUMBER;
      master_exist_num    NUMBER;
      alert_number        NUMBER;
      v_template_name     VARCHAR2 (20000);
      item_code           VARCHAR2 (2000);
   BEGIN
      FOR i IN cursor_item_stg
      LOOP
         item_code := (i.new_item_code);
         BEGIN
            ---------------------Fetech master and Child Oragnisation Code------------------------
            BEGIN
               SELECT a.organization_id, a.organization_code,
                      a.master_organization_id, b.organization_code
                 INTO v_child_org_id, v_child_org_code,
                      v_master_org_id, v_master_org_code
                 FROM mtl_parameters a, org_organization_definitions b
                WHERE a.master_organization_id = b.organization_id
                  AND a.organization_code = i.org_code;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  v_child_org_id := NULL;
                  v_child_org_code := NULL;
                  v_master_org_id := NULL;
                  v_master_org_code := NULL;
               WHEN OTHERS
               THEN
                  v_child_org_id := NULL;
                  v_child_org_code := NULL;
                  v_master_org_id := NULL;
                  v_master_org_code := NULL;
            END;
            ----------------------------Fetch Template Code -----------------------
            BEGIN
               SELECT template_id, template_name
                 INTO l_template_id, v_template_name
                 FROM apps.mtl_item_templates
                WHERE UPPER (template_name) = UPPER (i.template_name);
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  l_template_id := NULL;
               WHEN OTHERS
               THEN
                  l_template_id := NULL;
            END;
            /* Insert Record Into Interface data */
            BEGIN
               --------FOR MASTER organisation Item insertion ----------------------
               IF v_child_org_id = v_master_org_id
               THEN
                  INSERT INTO mtl_system_items_interface
                              (organization_id, description,
                               long_description, segment1,
                               segment2, segment3, segment4,
                               segment5, segment6, segment7,
                               segment8, segment9, segment10,
                               segment11, segment12, segment13,
                               segment14, segment15, segment16,
                               segment17, segment18, segment19,
                               segment20, primary_uom_code,
                               secondary_uom_code, process_flag,
                               template_id, transaction_type,
                               set_process_id, shelf_life_days,
                               location_control_code,
                               ----INVENTORY_ITEM_FLAG,
                               min_minmax_quantity, max_minmax_quantity,
                               expense_account,
                               must_use_approved_vendor_flag,
                               days_late_receipt_allowed,
                               list_price_per_unit, attribute1,
                               attribute2, attribute3,attribute4,attribute5,
                               attribute6,attribute7,attribute8,attribute9,
                               attribute10,attribute11,attribute12,attribute13,
                               attribute14,attribute15
                              )
                       VALUES (v_child_org_id, i.item_description,
                               i.item_long_desc, i.new_item_code,
                               i.segment2, i.segment3, i.segment4,
                               i.segment5, i.segment6, i.segment7,
                               i.segment8, i.segment9, i.segment10,
                               i.segment11, i.segment12, i.segment13,
                               i.segment14, i.segment15, i.segment16,
                               i.segment17, i.segment18, i.segment19,
                               i.segment20, i.stocking_uom,
                               i.procurement_uom, 1,
                               l_template_id, 'CREATE',
                               1, i.shelf_period,
                               DECODE (i.stock_locator_flag, 'N', 1, 1),
                               -- NVL(R (L (I.Inventory_Planning_Method)),'N'),
                               i.inv_min, i.inv_max,
                               DECODE (NVL (i.expense_item_control, 'N'),
                                       'N', NULL,
                                       i.expense_account
                                      ),
                               i.approved_supplier_flag,
                               i.purchasing_lead_days,
                               i.purchased_list_price, i.attribute1,
                               i.attribute2, i.attribute3,i.attribute4,i.attribute5,
                               i.attribute6,i.attribute7,i.attribute8,i.attribute9,
                               i.attribute10,i.attribute11,i.attribute12,i.attribute13,
                               i.attribute14,i.attribute15
                              );
                  COMMIT;
               ELSIF v_child_org_id <> v_master_org_id
               THEN
                  /*If Item Code New In Child Organization
                  but  Exist in Master Organizatio Then Create only One Record For
                  Child organization  */
                  BEGIN
                     SELECT COUNT (*)
                       INTO master_exist_num
                       FROM mtl_system_items_b
                      WHERE (segment1) = ((i.new_item_code))
                        AND organization_id = v_master_org_id;
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        master_exist_num := 0;
                     WHEN OTHERS
                     THEN
                        master_exist_num := 0;
                  END;
                  IF master_exist_num <> 0
                  THEN
                     INSERT INTO mtl_system_items_interface
                                 (organization_id, description,
                                  long_description, segment1,
                                  segment2, segment3,
                                  segment4, segment5,
                                  segment6, segment7,
                                  segment8, segment9,
                                  segment10, segment11,
                                  segment12, segment13,
                                  segment14, segment15,
                                  segment16, segment17,
                                  segment18, segment19,
                                  segment20, primary_uom_code,
                                  secondary_uom_code, process_flag,
                                  template_id, transaction_type,
                                  set_process_id, shelf_life_days,
                                  location_control_code,
                                  ----INVENTORY_ITEM_FLAG,
                                  min_minmax_quantity, max_minmax_quantity,
                                  expense_account,
                                  must_use_approved_vendor_flag,
                                  days_late_receipt_allowed,
                                  list_price_per_unit, attribute1,
                                  attribute2, attribute3,attribute4,attribute5,
                                  attribute6,attribute7,attribute8,attribute9,
                                  attribute10,attribute11,attribute12,attribute13,
                                  attribute14,attribute15
                                 )
                          VALUES (v_child_org_id, i.item_description,
                                  i.item_long_desc, (i.new_item_code),
                                  (i.segment2), (i.segment3),
                                  (i.segment4), (i.segment5),
                                  (i.segment6), (i.segment7),
                                  (i.segment8), (i.segment9),
                                  (i.segment10), (i.segment11),
                                  (i.segment12), (i.segment13),
                                  (i.segment14), (i.segment15),
                                  (i.segment16), (i.segment17),
                                  (i.segment18), (i.segment19),
                                  (i.segment20), i.stocking_uom,
                                  i.procurement_uom, 1,
                                  l_template_id, 'CREATE',
                                  1, i.shelf_period,
                                  DECODE (i.stock_locator_flag, 'N', 1, 1),
                                  -------------NVL(R (L (I.Inventory_Planning_Method)),'N'),
                                  i.inv_min, i.inv_max,
                                  DECODE (NVL (i.expense_item_control, 'N'),
                                          'N', NULL,
                                          i.expense_account
                                         ),
                                  i.approved_supplier_flag,
                                  i.purchasing_lead_days,
                                  i.purchased_list_price, i.attribute1,
                                  i.attribute2, i.attribute3,i.attribute4,i.attribute5,
                               i.attribute6,i.attribute7,i.attribute8,i.attribute9,
                               i.attribute10,i.attribute11,i.attribute12,i.attribute13,
                               i.attribute14,i.attribute15
                                 );
                  /*If Item Code New In Child Organization
                  but Not Exist in Master Organizatio Then Create One Record For
                  Master Organization and careate reocrd for Child organization  */
                  ELSE
                     ---------Master Organization -----------
                     /*  Now Check Same New  Item Code is existed with master organization            */
                     BEGIN
                        SELECT COUNT (*)
                          INTO exist_num11
                          FROM mtl_system_items_interface
                         WHERE inventory_item_id IS NULL
                           AND organization_id = v_master_org_id
                           AND (segment1) = ((i.new_item_code));
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           exist_num11 := 0;
                        WHEN OTHERS
                        THEN
                           exist_num11 := 0;
                     END;
                     IF exist_num11 = 0
                     THEN
                        INSERT INTO mtl_system_items_interface
                                    (organization_id, description,
                                     long_description, segment1,
                                     segment2, segment3,
                                     segment4, segment5,
                                     segment6, segment7,
                                     segment8, segment9,
                                     segment10, segment11,
                                     segment12, segment13,
                                     segment14, segment15,
                                     segment16, segment17,
                                     segment18, segment19,
                                     segment20, primary_uom_code,
                                     secondary_uom_code, process_flag,
                                     template_id, transaction_type,
                                     set_process_id, shelf_life_days,
                                     location_control_code,
                                     ----INVENTORY_ITEM_FLAG,
                                     min_minmax_quantity,
                                     max_minmax_quantity,
                                     expense_account,
                                     must_use_approved_vendor_flag,
                                     days_late_receipt_allowed,
                                     list_price_per_unit, attribute1,
                                     attribute2, attribute3,attribute4,attribute5,
                                     attribute6, attribute7, attribute8, attribute9,
                                     attribute10, attribute11, attribute12,attribute13,
                                     attribute14,attribute15
                                    )
                             VALUES (v_master_org_id, i.item_description,
                                     i.item_long_desc, (i.new_item_code),
                                     (i.segment2
                                     ), (i.segment3),
                                     (i.segment4), (i.segment5),
                                     (i.segment6), (i.segment7),
                                     (i.segment8), (i.segment9),
                                     (i.segment10
                                     ), (i.segment11),
                                     (i.segment12), (i.segment13),
                                     (i.segment14
                                     ), (i.segment15),
                                     (i.segment16), (i.segment17),
                                     (i.segment18
                                     ), (i.segment19),
                                     (i.segment20), i.stocking_uom,
                                     i.procurement_uom, 1,
                                     l_template_id, 'CREATE',
                                     1, i.shelf_period,
                                     DECODE (i.stock_locator_flag, 'N', 1, 1),
                                     -------------NVL(R (L (I.Inventory_Planning_Method)),'N'),
                                     i.inv_min,
                                     i.inv_max,
                                     DECODE (NVL (i.expense_item_control, 'N'),
                                             'N', NULL,
                                             i.expense_account
                                            ),
                                     i.approved_supplier_flag,
                                     i.purchasing_lead_days,
                                     i.purchased_list_price, i.attribute1,
                                     i.attribute2, i.attribute3,i.attribute4,i.attribute5,
                               i.attribute6,i.attribute7,i.attribute8,i.attribute9,
                               i.attribute10,i.attribute11,i.attribute12,i.attribute13,
                               i.attribute14,i.attribute15
                                    );
                        --------------------------Child Organization ------------------
                        INSERT INTO mtl_system_items_interface
                                    (organization_id, description,
                                     long_description, segment1,
                                     segment2, segment3,
                                     segment4, segment5,
                                     segment6, segment7,
                                     segment8, segment9,
                                     segment10, segment11,
                                     segment12, segment13,
                                     segment14, segment15,
                                     segment16, segment17,
                                     segment18, segment19,
                                     segment20, primary_uom_code,
                                     secondary_uom_code, process_flag,
                                     template_id, transaction_type,
                                     set_process_id, shelf_life_days,
                                     location_control_code,
                                     ----INVENTORY_ITEM_FLAG,
                                     min_minmax_quantity,
                                     max_minmax_quantity,
                                     expense_account,
                                     must_use_approved_vendor_flag,
                                     days_late_receipt_allowed,
                                     list_price_per_unit, attribute1,
                                     attribute2, attribute3,attribute4,attribute5,
                                     attribute6, attribute7, attribute8, attribute9,
                                     attribute10, attribute11, attribute12,attribute13,
                                     attribute14,attribute15
                                    )
                             VALUES (v_child_org_id, i.item_description,
                                     i.item_long_desc, (i.new_item_code),
                                     (i.segment2
                                     ), (i.segment3),
                                     (i.segment4), (i.segment5),
                                     (i.segment6), (i.segment7),
                                     (i.segment8), (i.segment9),
                                     (i.segment10
                                     ), (i.segment11),
                                     (i.segment12), (i.segment13),
                                     (i.segment14
                                     ), (i.segment15),
                                     (i.segment16), (i.segment17),
                                     (i.segment18
                                     ), (i.segment19),
                                     (i.segment20), i.stocking_uom,
                                     i.procurement_uom, 1,
                                     l_template_id, 'CREATE',
                                     1, i.shelf_period,
                                     DECODE (i.stock_locator_flag, 'N', 1, 1),
                                     -------------NVL(R (L (I.Inventory_Planning_Method)),'N'),
                                     i.inv_min,
                                     i.inv_max,
                                     DECODE (NVL (i.expense_item_control, 'N'),
                                             'N', NULL,
                                             i.expense_account
                                            ),
                                     i.approved_supplier_flag,
                                     i.purchasing_lead_days,
                                     i.purchased_list_price, i.attribute1,
                                     i.attribute2, i.attribute3,i.attribute4,i.attribute5,
                               i.attribute6,i.attribute7,i.attribute8,i.attribute9,
                               i.attribute10,i.attribute11,i.attribute12,i.attribute13,
                               i.attribute14,i.attribute15
                                    );
                     ELSE
                        --------------------------Child Organization ------------------
                        INSERT INTO mtl_system_items_interface
                                    (organization_id, description,
                                     long_description, segment1,
                                     segment2, segment3,
                                     segment4, segment5,
                                     segment6, segment7,
                                     segment8, segment9,
                                     segment10, segment11,
                                     segment12, segment13,
                                     segment14, segment15,
                                     segment16, segment17,
                                     segment18, segment19,
                                     segment20, primary_uom_code,
                                     secondary_uom_code, process_flag,
                                     template_id, transaction_type,
                                     set_process_id, shelf_life_days,
                                     location_control_code,
                                     min_minmax_quantity,
                                     max_minmax_quantity,
                                     expense_account,
                                     must_use_approved_vendor_flag,
                                     days_late_receipt_allowed,
                                     list_price_per_unit, attribute1,
                                     attribute2, attribute3,attribute4,attribute5,
                                     attribute6, attribute7, attribute8, attribute9,
                                     attribute10, attribute11, attribute12,attribute13,
                                     attribute14,attribute15
                                    )
                             VALUES (v_child_org_id, i.item_description,
                                     i.item_long_desc, (i.new_item_code),
                                     (i.segment2
                                     ), (i.segment3),
                                     (i.segment4), (i.segment5),
                                     (i.segment6), (i.segment7),
                                     (i.segment8), (i.segment9),
                                     (i.segment10
                                     ), (i.segment11),
                                     (i.segment12), (i.segment13),
                                     (i.segment14
                                     ), (i.segment15),
                                     (i.segment16), (i.segment17),
                                     (i.segment18
                                     ), (i.segment19),
                                     (i.segment20), i.stocking_uom,
                                     i.procurement_uom, 1,
                                     l_template_id, 'CREATE',
                                     1, i.shelf_period,
                                     DECODE (i.stock_locator_flag, 'N', 1, 1),
                                     i.inv_min,
                                     i.inv_max,
                                     DECODE (NVL (i.expense_item_control, 'N'),
                                             'N', NULL,
                                             i.expense_account
                                            ),
                                     i.approved_supplier_flag,
                                     i.purchasing_lead_days,
                                     i.purchased_list_price, i.attribute1,
                                     i.attribute2, i.attribute3,i.attribute4,i.attribute5,
                               i.attribute6,i.attribute7,i.attribute8,i.attribute9,
                               i.attribute10,i.attribute11,i.attribute12,i.attribute13,
                               i.attribute14,i.attribute15
                                    );
                     END IF;
                  END IF;
               END IF;
            END;
            UPDATE item_master_stag
               SET flag_process = 'P'
             WHERE entry_id = i.entry_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error := SQLERRM ();
               UPDATE item_master_stag
                  SET flag_process = 'E',
                      error_message = l_error
                WHERE entry_id = i.entry_id;
         END;
      END LOOP;
      COMMIT;
   END;

0 comments:

Post a Comment