Loading

Saturday, June 24, 2017

Query to fetch PATCH action details














This is known query. But, still i would like to share the one which we use regularly for doing the impact analysis of Oracle Patch. 


Below query helps you to get the actions performed by a Patch application. 

Select J.PATCH_NAME,
       H.APPLICATIoNS_SYSTEM_NAME,
       H.NAME,
       I.DRIVER_FILE_NAME,
       D.APP_SHORT_NAME,
       D.SUBDIR,
       D.FILENAME,
       E.ACTION_CODE,
       max(F.VERSION) latest
  from AD_BUGS                  A,
       AD_PATCH_RUN_BUGS        B,
       AD_PATCH_RUN_BUG_ACTIONS C,
       AD_FILES                 D,
       AD_PATCH_COMMON_ACTIONS  E,
       AD_FILE_VERSIONS         F,
       AD_PATCH_RUNS            G,
       AD_APPL_TOPS             H,
       AD_PATCH_DRIVERS         I,
       AD_APPLIED_PATCHES       J
 where A.BUG_ID = B.BUG_ID
   and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID
   and C.FILE_ID = D.FILE_ID
   and E.COMMON_ACtION_ID = C.COMMON_ACTION_ID
   and D.FILE_ID = F.FILE_ID
   and G.APPL_TOP_ID = H.APPL_TOP_ID
   and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID
   and I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID
   and B.PATCH_RUN_ID = G.PATCH_RUN_ID
   and C.EXECUTED_FLAG = 'Y'
   and G.PATCH_DRIVER_ID in
       (select PATCH_DRIVER_ID
          from AD_PATCH_DRIVERS
         where APPLIED_PATCH_ID in
               (select APPLIED_PATCH_ID
                  from AD_APPLIED_PATCHES
                 where PATCH_NAME = '22136978'))
 GROUP BY J.PATCH_NAME,
          H.APPLICATIoNS_SYSTEM_NAME,
          H.NAME,
          I.DRIVER_FILE_NAME,
          D.APP_SHORT_NAME,
          D.SUBDIR,
          D.FILENAME,
          E.ACTION_CODE;

Hope it helps!!


Saturday, June 24, 2017 by Team search · 0

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

Monday, November 28, 2016

Tuning PLSQL Oracle 10g – Using the PLS_INTEGER data type for arithmetic operations














  • PLS_INTEGER a data type introduced in Oracle 7 especially to speed up the intensive arithmetic operations.
  • It’s part of NUMBER family
  • It’s an only data type in oracle which uses native machine arithmetic instead of “C” arithmetic library. This in turn makes its faster than “NUMBER” data type
  • This 32 bit data type can store values in the range of -2147483648 to 2147483648

Let’s undertake a case study where we will observe the difference in performance of arithmetic operations using NUMBER and PLS_INTEGER data type.


Script:


SET SERVEROUTPUT ON;
DECLARE
  ln_number       NUMBER      DEFAULT 0;
  ln_pls_integer  PLS_INTEGER DEFAULT 0;
  ln_start_time   NUMBER;
  ln_end_time     NUMBER;
BEGIN
  ln_start_time := DBMS_UTILITY.get_cpu_time;
  FOR i in 1 .. 100000000
  LOOP
    ln_pls_integer := ln_pls_integer+1;
  END LOOP;
  ln_end_time := DBMS_UTILITY.get_cpu_time;
  DBMS_OUTPUT.put_line('Time Taken (PLS_IMTEGER): ' 
                       ||ROUND(ln_end_time - ln_start_time,2)
                      );
 
  ln_start_time := DBMS_UTILITY.get_cpu_time;
  FOR i in 1 .. 100000000
  LOOP
    ln_number := ln_number+1;
  END LOOP;
  ln_end_time := DBMS_UTILITY.get_cpu_time;
  DBMS_OUTPUT.put_line('Time Taken  (NUMBER)     : '
                       ||ROUND(ln_end_time - ln_start_time,2)
                      );
END;


Test Results





 The test results are very much evident that I got around 300% improvement in performance.

 

Monday, November 28, 2016 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.