Script for item conversion in oracle apps
In this post I will share you complete script for item conversion in oracle apps. This script uses oracle standard inventory item interface to insert or create new inventory items in the oracle system. We are using the Item interface tables to insert the new inventory items in oracle apps. You can refer this Script for item conversion in oracle apps. Here below you will get the complete Script for item conversion in oracle apps.
Important Interface Tables for Item conversion in oracle apps
1.mtl_system_items_interface
Example of Script for item conversion 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