ITEM ORGANIZATION ASSIGNMENT

========================= ITEM ORGANIZATION ASSIGNMENT =================
Pre requisites
---------------
1) Item Exists for Master Org.

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE ProcessItmOrgAsg(p_organization_code IN VARCHAR2)
AS
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
l_commit VARCHAR2(2) := FND_API.G_FALSE;
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) := 'MGRPLM';
l_resp_name VARCHAR2(30) := 'EGO_DEVELOPMENT_MANAGER';

CURSOR csr_org_items IS
SELECT inventory_item_id, segment1, primary_uom_code
FROM mtl_system_items_b
WHERE segment1 = 'D10001';

BEGIN

-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;

-- Get the application_id and responsibility_id
SELECT application_id, responsibility_id
INTO l_application_id, l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;

FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id); -- MGRPLM / Development Manager / EGO
dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_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
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_id
INTO l_item_org_assignment_tbl(l_rowcnt).ORGANIZATION_ID
FROM mtl_parameters
WHERE organization_code = p_organization_code;

l_item_org_assignment_tbl(l_rowcnt).ORGANIZATION_CODE := p_organization_code;
l_item_org_assignment_tbl(l_rowcnt).PRIMARY_UOM_CODE := itm.primary_uom_code;
END LOOP;

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;
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;
/