Random SQLs – AP (Account Payables), PO (Purchasing)

 Introduction:

Some example SQLs (queries) related to EBS Modules PO (Purchasing) AP (Account Payables functionality within the Oracle Applications.

I have put the SQLs that I used for various support and troubleshooting tasks without much editing or beautification. You might need to change many tables and WHERE clauses to make this useful for your application.

 

SQLs:

Approval History Details for a single Purchase Order: Substitute PO Header ID in the below SQL

-- PO Approval History Details
SELECT PAH.SEQUENCE_NUM AS SEQUENCE_NUM
    , FU.USER_NAME AS APPROVER
    , PAH.ACTION_CODE AS ACTION
    , PAH.ACTION_DATE
    , PAH.CREATION_DATE AS RECD_DATE
    , PHA.SEGMENT1 PO_NUM
    , FU.DESCRIPTION AS APPROVER_NAME
    , FU.EMPLOYEE_ID AS APPROVER_ID
    , PAH.NOTE AS APPROVAL_NOTE
FROM APPS_FND.PO_ACTION_HISTORY PAH, APPS_FND.PO_HEADERS_ALL PHA, APPS_FND.FND_USER FU
WHERE PAH.OBJECT_ID = PHA.PO_HEADER_ID
AND PHA.PO_HEADER_ID = 1503035 – change PO Header ID
AND PAH.EMPLOYEE_ID = FU.EMPLOYEE_ID
AND PAH.OBJECT_TYPE_CODE = 'PO'
ORDER BY PAH.SEQUENCE_NUM DESC;

 

PO Line Level Details for a single Purchase Order: Substitute PO Header ID in the below SQL

-- PO Line level details
SELECT
    PO_LINE_ID AS LINE_ID,
    LINE_NUM AS LINE_NUM,
    ITEM_ID AS ITEM_ID,
    '7112530141' AS ITEM_NUM,          -- TODO
    ITEM_DESCRIPTION AS ITEM_DESC,
    UNIT_MEAS_LOOKUP_CODE AS UOM,
    QUANTITY AS QUANTITY,
    UNIT_PRICE AS UNIT_PRICE,
    QUANTITY*UNIT_PRICE AS LINE_AMOUNT,
    'KIM.622001.000.01.OPURCH DFLT.0.0.0.000000' AS CHARGE_ACCT        -- TODO
FROM PO_LINES_ALL POL
WHERE PO_HEADER_ID = 1504025;  -- change PO header ID

 

Purchase Order Notification Details: Substitute Notification ID in the below SQL

 

-- PO, Notification - Header level details
SELECT
    WNT.NOTIFICATION_ID AS NOTIFICATION_ID,
    WNT.FROM_USER AS FROM_USER,
    WNT.TO_USER AS TO_USER,
    WNT.BEGIN_DATE AS SENT_DATE,
    WNT.DUE_DATE AS DUE_DATE,
    POH.PO_HEADER_ID AS PO_HEADER_ID,
    POH.SEGMENT1 AS PO_NUMBER,
    POH.VENDOR_ID AS SUPPLIER_ID,
    (SELECT VENDOR_NAME FROM PO_VENDORS VND WHERE VND.VENDOR_ID = POH.VENDOR_ID) AS SUPPLIER_NAME,
    (SELECT VENDOR_SITE_CODE FROM PO_VENDOR_SITES_ALL VDS WHERE VDS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID) AS SUPPLIER_SITE,
    POH.COMMENTS AS PO_DESC,
    WNT.FROM_ROLE AS FWD_FROM,
    WNT.FROM_ROLE AS PREPARER,
    (SELECT NAME FROM HR_OPERATING_UNITS WHERE ORGANIZATION_ID = POH.ORG_ID) AS OPER_UNIT,
    POH.NOTE_TO_AUTHORIZER AS PO_NOTE,
    WNT.SUBJECT AS PO_SUBJECT,
    WNT.RECIPIENT_ROLE AS RESPONDER,
FROM WF_NOTIFICATIONS WNT, PO_HEADERS_ALL POH
WHERE WNT.NOTIFICATION_ID = 4772925
AND POH.PO_HEADER_ID(+) = NVL(SUBSTR(WNT.ITEM_KEY, 0, INSTR(WNT.ITEM_KEY, '-')-1), WNT.ITEM_KEY)
AND WNT.MESSAGE_TYPE = 'POAPPRV';
 

 

Notification History Details for a single Purchase Order: Substitute PO Notification ID in the below SQL

SELECT
    WNT.NOTIFICATION_ID AS NOTIFICATION_ID,
    WNT.FROM_USER AS FROM_USER,
    WNT.TO_USER AS TO_USER,
    WNT.BEGIN_DATE AS SENT_DATE,
    WNT.DUE_DATE AS DUE_DATE,
    POH.PO_HEADER_ID AS PO_HEADER_ID,
    POH.SEGMENT1 AS PO_NUMBER,
    POH.VENDOR_ID AS SUPPLIER_ID,
    (SELECT VENDOR_NAME FROM PO_VENDORS VND WHERE VND.VENDOR_ID = POH.VENDOR_ID) AS SUPPLIER_NAME,
    (SELECT VENDOR_SITE_CODE FROM PO_VENDOR_SITES_ALL VDS WHERE VDS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID) AS SUPPLIER_SITE,
    POH.COMMENTS AS PO_DESC,
    WNT.FROM_ROLE AS FWD_FROM,
    WNT.FROM_ROLE AS PREPARER,
    (SELECT NAME FROM HR_OPERATING_UNITS WHERE ORGANIZATION_ID = POH.ORG_ID) AS OPER_UNIT,
    POH.NOTE_TO_AUTHORIZER AS PO_NOTE,
    WNT.SUBJECT AS PO_SUBJECT,
    WNT.RECIPIENT_ROLE AS RESPONDER,
FROM WF_NOTIFICATIONS WNT, PO_HEADERS_ALL POH
WHERE WNT.NOTIFICATION_ID = 4772925
AND POH.PO_HEADER_ID(+) = NVL(SUBSTR(WNT.ITEM_KEY, 0, INSTR(WNT.ITEM_KEY, '-')-1), WNT.ITEM_KEY)
AND WNT.MESSAGE_TYPE = 'POAPPRV';
 

Attachment Details of a single Purchase Order: Substitute PO Header ID in the below SQL

 
    select fad.entity_name   
          ,fad.document_id  
          ,fad.pk1_value  
          ,fad.pk2_value  
          ,fd.datatype_id  , fd.media_id
          ,(fad.entity_name || '_' || fad.document_id ||'_' || fl.file_name) file_name  
          ,fl.file_data   --, fad.*, fd.*, fl.*, fdd.*
    from fnd_attached_documents fad  
        ,fnd_documents fd  
        ,fnd_lobs fl 
        ,fnd_document_datatypes fdd  
    where fad.document_id = fd.document_id  
    and   fd.media_id     = fl.file_id  
    and   fd.datatype_id  = fdd.datatype_id  
    and   fdd.user_name   = 'File'  
    and   fad.entity_name = 'PO_HEADERS' -- replace with the entity_name you want to extract. 
    and fad.document_id =  1500025
    order by fad.pk1_value, fad.pk2_value;

 

Tables Involved in PO Attachment Functionality:

 
For Importing Attachments in oracle application one has to populate following tables.
1. FND_DOCUMENTS
2. FND_ATTACHED_DOCUMENTS
3. FND_DOCUMENTS_TL
4. FND_DOCUMENT_DATATYPES.
5. FND_DOCUMENT_CATEGORIES
6. FND_DOCUMENTS_LONG_TEXT (Long text type attachment).
7. FND_DOCUMENTS_SHORT_TEXT (Short text type attachment).
8. FND_DOCUMENTS_LONG_RAW
9. FND_LOBS (File type attachments).
 

PO Notification Counts – Opened during past 24 hours: With Status Open at present

SELECT wn.*, wa.*

FROM apps_fnd.wf_notifications wn, apps_fnd.wf_activities wa
where wn.message_name = wa.message
and wn.sent_date > sysdate-1
and wn.status not in ('CANCELED', 'CLOSED')
and length(wa.result_type) > 1
and wn.message_type = 'POAPPRV';
 

 

PO Notification Counts – Opened EVER: With Status Open at present

SELECT wn.*, wa.*
FROM apps_fnd.wf_notifications wn, apps_fnd.wf_activities wa
where wn.message_name = wa.message
--and wn.sent_date > sysdate-1
and wn.status not in ('CANCELED', 'CLOSED')
and length(wa.result_type) > 1
and wn.message_type = 'POAPPRV';

 

PO Notification Counts – Opened during past 24 hours: With ANY Status at present

SELECT wn.*, wa.*
FROM apps_fnd.wf_notifications wn, apps_fnd.wf_activities wa
where wn.message_name = wa.message
and wn.sent_date > sysdate-1  -- substitute 7 for week, 30 for month
--and wn.status not in ('CANCELED', 'CLOSED')
and length(wa.result_type) > 1
and wn.message_type = 'POAPPRV';

 

PO Purchase Requisitions: Created during past 30 days

SELECT count(*)

FROM apps_fnd.PO_REQUISITION_HEADERS_ALL PRH
WHERE PRH.CREATION_DATE > sysdate-30;
 

PO Purchase Requisition Lines: Created during past 30 days

SELECT count(*)
FROM apps_fnd.PO_REQUISITION_HEADERS_ALL PRH, apps_fnd.PO_REQUISITION_LINES_ALL PRL
WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRH.CREATION_DATE > sysdate-30;
 
 

PO Purchase Orders: Created during past 30 days

SELECT count(*)
FROM apps_fnd.PO_HEADERS_ALL POH
WHERE POH.CREATION_DATE > sysdate-30;
 

PO Purchase Order Lines: Created during past 30 days

SELECT count(*)
FROM apps_fnd.PO_HEADERS_ALL POH, apps_fnd.PO_LINES_ALL POL
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POH.CREATION_DATE > sysdate-30;
  

Keywords:

PO_REQUISITION_HEADERS_ALL, PO_REQUISITION_LINES_ALL, PO_HEADERS_ALL, PO, Purchasing, AP, Account Payables, EBS, R12, Query, SQL, SQLPLUS

Oracle DB SQLs – DBA Queries – Performance, Top 10, Locks, Long Pending Jobs, etc.

 

Usage:

A few Miscellaneous SQLs for multiple DB Health Check Tasks:

1.    SQLs taking maximum resources

2.    Identify long running programs

3.    Identify database locks that is waiting for resolution

4.    Query executed from a single schema/session

5.    Track a single user or a session

 

SQLs:

Top 20 SQLs in Buffer:

 

-- Top Disk Reads, Reads per Execution

 

SELECT * FROM

    (SELECT SQA.SQL_TEXT SQL_TEXT

        ,TRUNC (SQA.DISK_READS / DECODE (SQA.EXECUTIONS, 0, 1, SQA.EXECUTIONS)

    ) READS_PER_EXECUTION

,SQA.BUFFER_GETS, SQA.DISK_READS, SQA.EXECUTIONS, SQA.SORTS, SQA.ADDRESS

FROM V$SQLAREA SQA

ORDER BY 2 DESC)

WHERE ROWNUM <= 21;

 

Top SQLs by Disk Reads (Secondary Memory Usage):

 

-- Top Disk Reads per Execution

 

SELECT SUBSTR (SQA.SQL_TEXT, 1, 200) SQL_TEXT

        ,TRUNC (SQA.DISK_READS / DECODE (SQA.EXECUTIONS, 0, 1, SQA.EXECUTIONS)

    ) READS_PER_EXECUTION

    ,SQA.BUFFER_GETS, SQA.DISK_READS, SQA.EXECUTIONS, SQA.SORTS, SQA.ADDRESS

FROM V$SQLAREA SQA

WHERE ROWNUM <= 21

ORDER BY 2 DESC;

 

 

Currently Active Sessions, SQLs:

-- Currently Active/Running Sessions, SQLs

 

SELECT SS.USERNAME, SS.SID, SS.OSUSER, SQT.SQL_ID, SQL_TEXT

FROM V$SQLTEXT_WITH_NEWLINES SQT,V$SESSION SS

WHERE SQT.ADDRESS = SS.SQL_ADDRESS

AND SQT.HASH_VALUE = SS.SQL_HASH_VALUE

AND SS.STATUS = 'ACTIVE'

AND SS.USERNAME <> 'SYSTEM'

ORDER BY SS.SID, SQT.PIECE;

 

Currently Active Database Locks:

 

-- Currently Active Database Locks - Waiting for at least 7777 seconds

 

SELECT OBJ.OBJECT_NAME, OBJ.OBJECT_TYPE, LKO.SESSION_ID,

  LK.TYPE,         -- TYPE OR SYSTEM/USER LOCK

  LK.LMODE,        -- LOCK MODE IN WHICH SESSION HOLDS LOCK

  LK.REQUEST, LK.BLOCK, LK.CTIME WAIT_DURATION         -- TIME SINCE CURRENT MODE WAS GRANTED

FROM V$LOCKED_OBJECT LKO, ALL_OBJECTS OBJ, V$LOCK LK

WHERE LKO.OBJECT_ID = OBJ.OBJECT_ID

  AND LK.ID1 = OBJ.OBJECT_ID

  AND LK.SID = LKO.SESSION_ID

  AND LK.CTIME > 7777

ORDER BY LK.CTIME DESC, LKO.SESSION_ID, OBJ.OBJECT_NAME;

 

 

Long Running DB Operations, Costly SQLs

--  Long Running DB Operations, Costly SQLs

 

SELECT SID, TO_CHAR(START_TIME,'HH24:MI:SS') STIME,

    MESSAGE,( SOFAR/TOTALWORK)* 100 PERCENT

FROM V$SESSION_LONGOPS

WHERE SOFAR/TOTALWORK < 1;

 

 

Long Running SQLs, currently in progress:

-- Queries that are Currently Running for more than 60 Seconds

 

SELECT SS.USERNAME, SS.SID, SS.SERIAL#, SS.LAST_CALL_ET/60 MINS_RUNNING, SQT.SQL_TEXT

FROM V$SESSION SS, V$SQLTEXT_WITH_NEWLINES SQT

WHERE  SS.SQL_ADDRESS = SQT.ADDRESS

AND SS.STATUS = 'ACTIVE'

AND SS.TYPE <> 'BACKGROUND'

AND SS.LAST_CALL_ET > 60

ORDER BY SID, SERIAL#, SQT.PIECE;

 

 

Identify a Single Problem SQL, Troublshoot:

Step 1:

-- Steps - Identify a slow SQL, get details for troubleshooting

-- 1. GET SQLS

 

SELECT

    SS.SID, SS.SERIAL#, SS.USERNAME,

    SS.OSUSER, TRX.START_TIME,

    PRC.SPID, SS.STATUS, SS.MACHINE,

    SQA.ACTION, SQA.MODULE, SS.PROGRAM

FROM

    V$SESSION SS, V$PROCESS PRC, V$TRANSACTION TRX,

    V$SQLAREA SQA

WHERE SS.PADDR = PRC.ADDR

AND SS.SADDR = TRX.SES_ADDR

AND SS.SQL_ADDRESS = SQA.ADDRESS (+)

AND TO_DATE(TRX.START_TIME,'MM/DD/YY HH24:MI:SS') <= SYSDATE - (15/1440) -- RUNNING FOR 15 MINUTES

ORDER BY TRX.START_TIME;

 

Step 2:

-- 2. GET SQL ADDRESS - ENTER THE SID VALUE

-- 3429    5977    00    INACTIVE    0000001B08BA4A78

 

SELECT SID, SERIAL#,SQL_ADDRESS, STATUS,PREV_SQL_ADDR FROM V$SESSION WHERE SID='3065';

 

 

Step 3:

-- 3. GET Full SQL

 

SELECT PIECE, SQL_TEXT FROM V$SQLTEXT X WHERE ADDRESS='10000009AB4EF340'

ORDER BY PIECE ASC;

 

 

 

 

Keywords:

Oracle DB, EBS, DBA, Performance Tuning, Optimization, PL/SQL, Top 10 SQLs, Query, SQL, SQLPLUS, Memory, Cards, Cost, V$SESSION, V$SQLAREA, V$SQLTEXT