Post date: Jul 05, 2011 10:4:14 AM
CREATE OR REPLACE PROCEDURE APPS.CLB_INV_PrcsItmOrgAsg (
p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER,
p_invent_item_id IN NUMBER,
p_user_id IN NUMBER,
p_resp_appl_id IN NUMBER,
p_resp_id IN NUMBER)
AS
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2 (2) := FND_API.G_TRUE;
l_commit VARCHAR2 (2) := FND_API.G_TRUE;
l_item_org_assignment_tbl EGO_ITEM_PUB.ITEM_ORG_ASSIGNMENT_TBL_TYPE;
x_message_list Error_Handler.Error_Tbl_Type;
x_return_status VARCHAR2 (2);
x_msg_count NUMBER := 0;
--l_user_id NUMBER := -1;
--l_resp_id NUMBER := -1;
--l_application_id NUMBER := -1;
l_rowcnt NUMBER := 1;
--l_user_name VARCHAR2 (30);-- := 'MII';
--l_resp_name VARCHAR2 (30);--:= 'CLB_INVENTORY_SUPER_USER';
--v_inv_item_id NUMBER;
v_org_code VARCHAR2 (3);
CURSOR csr_org_items
IS
SELECT inventory_item_id,
segment1,
primary_uom_code,
COUNT (inventory_item_id)
FROM mtl_system_items_b msib, mtl_parameters mpr
WHERE MSIB.ORGANIZATION_ID = MPR.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_STATUS_CODE = 'Active'
AND inventory_item_id = p_invent_item_id
GROUP BY inventory_item_id, segment1, primary_uom_code;
CURSOR c_org_id (v_inv_Item_id NUMBER)
IS
SELECT mp.organization_id
FROM mtl_parameters mp
MINUS
SELECT msi.organization_id
FROM mtl_system_items msi
WHERE inventory_item_id = v_inv_item_id;
BEGIN
DBMS_OUTPUT.ENABLE (1000000);
FND_GLOBAL.APPS_INITIALIZE (p_user_id, p_resp_appl_id, p_resp_id); -- MGRPLM / Development Manager / EGO
DBMS_OUTPUT.put_line( 'Initialized applications context: '
|| p_user_id
|| ' '
|| p_resp_appl_id
|| ' '
|| p_resp_id);
-- call API to assign Items
DBMS_OUTPUT.PUT_LINE ('===========================================');
DBMS_OUTPUT.PUT_LINE (
'Calling EGO_ITEM_PUB.Process_Item_Org_Assignment API');
FOR itm IN csr_org_items
LOOP
FOR c2 IN c_org_id (itm.inventory_Item_id)
LOOP
l_item_org_assignment_tbl (l_rowcnt).INVENTORY_ITEM_ID :=
itm.inventory_item_id;
l_item_org_assignment_tbl (l_rowcnt).ITEM_NUMBER := itm.segment1;
SELECT organization_code
INTO v_org_code
FROM mtl_parameters
WHERE organization_id = c2.organization_id;
l_item_org_assignment_tbl (l_rowcnt).ORGANIZATION_CODE := v_org_code;
l_item_org_assignment_tbl (l_rowcnt).PRIMARY_UOM_CODE :=
itm.primary_uom_code;
EGO_ITEM_PUB.PROCESS_ITEM_ORG_ASSIGNMENTS (
P_API_VERSION => l_api_version,
P_INIT_MSG_LIST => l_init_msg_list,
P_COMMIT => l_commit,
P_ITEM_ORG_ASSIGNMENT_TBL => l_item_org_assignment_tbl,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count);
DBMS_OUTPUT.PUT_LINE ('=========================================');
DBMS_OUTPUT.PUT_LINE ('Return Status: ' || x_return_status);
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
THEN
DBMS_OUTPUT.PUT_LINE ('Error Messages :');
Error_Handler.GET_MESSAGE_LIST (x_message_list => x_message_list);
FOR i IN 1 .. x_message_list.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE (x_message_list (i).MESSAGE_TEXT);
END LOOP;
END IF;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('=========================================');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Exception Occured :');
DBMS_OUTPUT.PUT_LINE (SQLCODE || ':' || SQLERRM);
DBMS_OUTPUT.PUT_LINE ('========================================');
END;
/