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

Query to fetch Workflow Vocation Rules in Oracle Apps R12












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

How to find the Database version (DBMS_DB_VERSION)? Example.













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:

image

DBMS_DB_VERSION:

image
I hope it helps!!

Sunday, January 1, 2017 by Team search · 2

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.