Wednesday, January 25, 2017
API to Update Purchase Requisition in Oracle Apps R12 (PO_REQUISITION_UPDATE_PUB.update_requisition)
In this post, i have shared a sample script for updating the purchase requisition.
As per Oracle Documentation, this API can be used to update PR with status INCOMPLETE or APPROVED only. But, i tried to update a INPROCESS PR and it worked :) .
API: PO_REQUISITION_UPDATE_PUB.update_requisition
Tested Instance: R12
Script:
DECLARE
lrec_req_hdr PO_REQUISITION_UPDATE_PUB.req_hdr;ltab_req_line_tbl PO_REQUISITION_UPDATE_PUB.req_line_tbl;ltab_req_dist_dtl PO_REQUISITION_UPDATE_PUB.req_dist_tbl;lv_return_status VARCHAR2(1);ln_msg_count NUMBER;lv_msg_data VARCHAR2(2000);
BEGIN
lrec_req_hdr.requisition_header_id := 115001;lrec_req_hdr.org_id := 85;ltab_req_line_tbl(1).requisition_header_id := 115001;ltab_req_line_tbl(1).requisition_line_id := 126332;ltab_req_line_tbl(1).suggested_vendor_name := 'SHARE_ORACLE';ltab_req_line_tbl(1).suggested_vendor_location := 'SHARE_ORACLE_LOC';
PO_REQUISITION_UPDATE_PUB.update_requisition(p_init_msg_list => 'T',p_commit => 'F',x_return_status => lv_return_status,x_msg_count => ln_msg_count,x_msg_data => lv_msg_data,p_submit_approval => 'N',p_req_hdr => lrec_req_hdr,p_req_line_tbl => ltab_req_line_tbl,p_req_dist_tbl => ltab_req_dist_dtl);
DBMS_OUTPUT.PUT_LINE('Return Status :' || lv_return_status);DBMS_OUTPUT.PUT_LINE('Msg Count :' || ln_msg_count);DBMS_OUTPUT.PUT_LINE('Msg Data :' || lv_msg_data);
END;
Hope this helps!!
Wednesday, January 25, 2017 by Team search · 3
Below query will help you to get the vocation rules setup in the system.
SELECT wfr.rule_id, wfr.message_type, wfr.message_name, wfr.begin_date, wfr.end_date, wfr.action, wfr.role, wfr.action_argument, wfiy.display_name as type_display, wm.display_name as msg_display, wm.subject, wl.meaning as action_display, wfiy.name, wm.type, wm.name as name1, wl.lookup_type, wl.lookup_code FROM wf_routing_rules wfr, wf_item_types_vl wfiy, wf_messages_vl wm, wf_lookups wl WHERE wfr.role = '&USERNAME' AND wfr.message_type = wfiy.name(+) AND wfr.message_type = wm.type(+) AND wfr.message_name = wm.name(+) AND wfr.action = wl.lookup_code AND wl.lookup_type = 'WFSTD_ROUTING_ACTIONS' ORDER BY type_display, msg_display, begin_date;
by Team search · 0
Sunday, January 1, 2017
I use to work in multiple database versions. Further, i use to have my own utility packages which i use for collecting various stats, doing code review etc. But, the major problem i face is building a common utility which can be compiled in different oracle database versions. Especially, in a database like Oracle, where every version adds more suprises and features.
So, the anonymous block which i build for preparing the utility uses a standard oracle utility named “DBMS_DB_VERSION”. Its a very useful one to check the version of the database and proceed. Below is the sample example.
V$VERSION:
DBMS_DB_VERSION:
Sunday, January 1, 2017 by Team search · 2
Subscribe to:
Posts (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.