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;
No comments:
Post a Comment