My only requirement is to assign column name and value dynamically
||' my_pkg.l_item_tbl_typ2(1).'||:P_COLUMN_NAME ||' := '||:P_COLUMN_VALUE ||'; '
because I don't know P_COLUMN_NAME and Value in advance. But when I execute the above statement dynamically , it returns error PLS-00302: component 'L_ITEM_TBL_TYP' must be declared. Please help me if you can..
DECLARE
------
cursor APPS_INITIALIZE (P_User_Name varchar, p_resp varchar2) is
SELECT urg.USER_ID,
urg.RESPONSIBILITY_ID,
urg.RESPONSIBILITY_APPLICATION_ID
--into p_user_id
-- ,l_responsibility_id
-- ,l_application_id
FROM FND_USER_RESP_GROUPS urg
,FND_USER fu
,FND_RESPONSIBILITY_VL fr
where FR.RESPONSIBILITY_ID = URG.RESPONSIBILITY_ID
and upper(FR.RESPONSIBILITY_NAME) LIKE upper(p_resp)
and FU.USER_ID = URG.USER_ID
and upper(FU.USER_NAME) like upper(P_User_Name)--nvl(FND_PROFILE.VALUE('USERNAME'),P_User_Name)
--and fu.user_id = p_user_id
and rownum = 1;
------
--master_item_rec Master_Item_c%rowtype;
l_item_tbl_typ ego_item_pub.item_tbl_type;
x_item_table ego_item_pub.item_tbl_type;
x_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
x_organization_id mtl_system_items_b.organization_id%TYPE;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER (10);
x_msg_data VARCHAR2 (1000);
x_message_list error_handler.error_tbl_type;
---
v_buyer_id number(15);
x number(15);
sql_stmt varchar2(4000);
APPS_INITIALIZE_REC APPS_INITIALIZE%ROWTYPE;
BEGIN
open APPS_INITIALIZE (:P_User_Name,:p_resp_like);
fetch APPS_INITIALIZE into APPS_INITIALIZE_rec;
close APPS_INITIALIZE;
--Setting FND global variables.
fnd_global.apps_initialize (APPS_INITIALIZE_REC.USER_ID
,APPS_INITIALIZE_REC.RESPONSIBILITY_ID
,APPS_INITIALIZE_REC.RESPONSIBILITY_APPLICATION_ID
);
--DBMS_OUTPUT.put_line ('Initialized');
--for Master_Item_rec in Master_Item_c loop
DBMS_OUTPUT.put_line ('start Program');
l_item_tbl_typ (1).transaction_type := 'UPDATE'; -- Replace this with 'UPDATE' for update transaction.
l_item_tbl_typ (1).inventory_item_id := :P_ITEM_ID;--7239;
l_item_tbl_typ (1).organization_id := :P_ORGANIZATION_ID;
---------
sql_stmt :=
' BEGIN '
||' l_item_tbl_typ(1).'||:P_COLUMN_NAME ||' := '||:P_COLUMN_VALUE ||'; '
||' END;';
DBMS_OUTPUT.put_line ('SQL_STMT: '||sql_stmt);
EXECUTE IMMEDIATE sql_stmt;
DBMS_OUTPUT.put_line ('=====================================');
DBMS_OUTPUT.put_line ('Calling EGO_ITEM_PUB.Process_Items API');
ego_item_pub.process_items (p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_false
, p_item_tbl => l_item_tbl_typ
, x_item_tbl => x_item_table
, 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 <> 'S' then
error_handler.get_message_list (x_message_list => x_message_list);
DBMS_OUTPUT.put_line (x_message_list (1).MESSAGE_TEXT); -- we only need first message
----------
DBMS_OUTPUT.put_line ('Before Update <> S');
else
DBMS_OUTPUT.put_line ('Before Update = S');
end if;
IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
FOR i IN 1 .. x_item_table.COUNT
LOOP
DBMS_OUTPUT.put_line ('Inventory Item Id :' || TO_CHAR (x_item_table (i).inventory_item_id));
DBMS_OUTPUT.put_line ('Organization Id :' || TO_CHAR (x_item_table (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;
--commit;
rollback;
--
DBMS_OUTPUT.put_line ('==================================');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Exception Occured :');
DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
DBMS_OUTPUT.put_line ('=====================================');
--commit;
rollback;
END;