Item import interface in oracle apps r12
Oracle apps has provided the Item Interface to create the Inventory Items form the External system to the Oracle application. This Item interface helps to create the Inventory Items directly from the Interface without creating manually one by one from the Front end Application. In this Post , I will show the Item Import Interface in oracle apps r12 and the Complete procedure which helps to import item from Item interface to the Oracle Inventory base tables.
Steps for Item Import in Oracle apps r12
1.Prepare the Items data in the Form of Item Import Interface tables.
2.Insert Item Information in the Item Interface Tables.
3.Run the 'Item Import' program to import items from Item Interface tables to the Oracle item Base tables.
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;
Import Items:-
This Program helps to Create the Inventory Items from External Application to the Oracle application. This Item Import programs helps to move the records from the Item interface table to the Oracle Base Tables.
This Program helps to Create the Inventory Items from External Application to the Oracle application. This Item Import programs helps to move the records from the Item interface table to the Oracle Base Tables.
7 comments:
Nice blog, I really like what you write in this blog, I also have some relevant Information about Best HR Training In Hyderabad | Hr training institute in Hyderabad! if you want more information.
Oracle Fusion HCM Online Training
Splendid blog I visit this blog it's unimaginably great. Inquisitively, in this blog substance shaped without a doubt and reasonable. The substance of data is useful.
Oracle Fusion HCM Online Training
Oracle Fusion Financials Online Training
Good Blog Thanks For Sharing this informative airtrl/
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
Brilliant blog I visit this blog it's incredibly awesome. Curiously, in this blog content formed doubtlessly and sensible. The substance of information is helpful.
Oracle Fusion HCM Online Training
Good blog thanks for sharing this informative article. It would be helpful full to all.
Oracle Fusion HCM Online Training
I like this article very much, if Anyone Reads Your Blog, Each And Every one will appreciate you.
Oracle Fusion Financials Online Training
Thank you for sharing such a nice and interesting blog.
Spark and Scala Online Training
Spark Scala Training
Hyderabad
Post a Comment