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;

4 comments:

12345 said...

Good Blog , Thanks for sharing this informative article.
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

12345 said...

Good Blog , Thanks for sharing this informative article.
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

Anonymous said...

Thanks for sharing This informative article. It would be helpful to all ,Keep sharing.
Oracle Fusion HCM Online Training

shaik shah said...

Goodblog, thanks for sharing this informative article. It would be helpful to all .your way of explaining in this good in this article.
Oracle Fusion HCM Online Training

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *