Oracle R12 Inventory Item Update through API - Sample for SHIPPABLE_ITEM_FLAG update
--CREATE OR REPLACE PROCEDURE JG_ITEMS_UPDATE_API
--AS
DECLARE
x_item_tbl EGO_ITEM_PUB.ITEM_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_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_tbl EGO_ITEM_PUB.ITEM_TBL_TYPE;
l_role_grant_tbl EGO_ITEM_PUB.ROLE_GRANT_TBL_TYPE;
l_user_name VARCHAR2 (30) := 'JGTECH';
l_resp_name VARCHAR2 (30) := 'India Local Inventory';
l_item_catalog_group_id NUMBER := 0;
CURSOR C1
IS
SELECT 271 ORGANIZATION_ID,
13018 INVENTORY_ITEM_ID,
-- '4946992' ITEM,
'Y' SHIPPABLE_ITEM_FLAG
/*PREPROCESSING_LEAD_TIME,
FULL_LEAD_TIME,
POSTPROCESSING_LEAD_TIME,
FIXED_LEAD_TIME,
VARIABLE_LEAD_TIME,
CUM_MANUFACTURING_LEAD_TIME,
CUMULATIVE_TOTAL_LEAD_TIME,
BUYER,
MRP_PLANNING_CODE,
ATO_FORECAST_CONTROL,
NVL(UPPER(END_ASSEMBLY_PEGGING_FLAG),'B')END_ASSEMBLY_PEGGING_FLAG,
DECODE(UPPER(CREATE_SUPPLY_FLAG),'YES','Y','N')CREATE_SUPPLY_FLAG,
PLANNER_CODE,
PLANNING_MAKE_BUY_CODE,
INVENTORY_PLANNING_CODE,
MIN_MINMAX_QUANTITY,
MAX_MINMAX_QUANTITY,
DECODE(UPPER(BOM_ENABLED_FLAG),'YES','Y','N')BOM_ENABLED_FLAG,
DECODE(UPPER(BUILD_IN_WIP_FLAG),'YES','Y','N')BUILD_IN_WIP_FLAG,
DECODE(UPPER(PURCHASING_ENABLED_FLAG),'YES','Y','N')PURCHASING_ENABLED_FLAG,
TRANSACTION_TYPE*/
FROM DUAL; --XX_ITEM_UPDATE_STG;
BEGIN
-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;
FOR i IN C1 LOOP
-- Get the application_id and responsibility_id
SELECT application_id, responsibility_id
INTO l_application_id, l_resp_id
FROM fnd_responsibility_vl
WHERE responsibility_name = l_resp_name
--FND_GLOBAL.APPS_INITIALIZE (1380, 51037, 7000);
FND_GLOBAL.APPS_INITIALIZE (l_user_id, l_resp_id, l_application_id);
DBMS_OUTPUT.put_line (
'Initialized applications context: '
|| l_user_id
|| ' '
|| l_resp_id
|| ' '
|| l_application_id);
-- Load l_item_tbl with the data
l_item_tbl (l_rowcnt).Organization_id := i.Organization_id; -- Organization Id
l_item_tbl (l_rowcnt).inventory_item_id := i.inventory_item_id;
l_item_tbl (l_rowcnt).transaction_type := 'UPDATE';
-- l_item_tbl (l_rowcnt).Segment1 := i.item; -- Item Number
l_item_tbl (l_rowcnt).SHIPPABLE_ITEM_FLAG := i.SHIPPABLE_ITEM_FLAG; -- Item Description
-- l_item_tbl(l_rowcnt).Template_Name := 'Finished Good'; -- Item template (** should be associated to ICC, Not mandatory)
-- call API to load Items
DBMS_OUTPUT.PUT_LINE ('=====================================');
DBMS_OUTPUT.PUT_LINE ('Calling EGO_ITEM_PUB.Process_Items API');
EGO_ITEM_PUB.PROCESS_ITEMS (p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_item_tbl => l_item_tbl,
p_role_grant_tbl => l_role_grant_tbl,
x_item_tbl => x_item_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
FOR i IN 1 .. x_item_tbl.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE (
'Inventory Item Id :'
|| TO_CHAR (x_item_tbl (i).inventory_item_id));
DBMS_OUTPUT.PUT_LINE (
'Organization Id :' || TO_CHAR (x_item_tbl (i).organization_id));
END LOOP;
ELSE
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;
COMMIT;
DBMS_OUTPUT.PUT_LINE ('=====================================');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Exception Occured :');
DBMS_OUTPUT.PUT_LINE (SQLERRM || ':' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
DBMS_OUTPUT.PUT_LINE ('=====================================');
RAISE;
END JG_ITEMS_UPDATE_API;