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

Common Queries: AP, PO, Requisitions, Purchasing

 

Introduction:

Some common SQLs for Oracle Applications EBS Purchasing Module.

I am using Release 12.2.4. But these SQLs should be applicable for most of the versions.

 

SQLs:

Get Notifications associated with a PO Number:

SELECT NOTIFICATION_ID, RECIPIENT_ROLE, FROM_ROLE, USER_KEY, ITEM_KEY, MESSAGE_TYPE, MESSAGE_NAME, STATUS, BEGIN_DATE, RESPONDER, FROM_USER, TO_USER, SUBJECT
FROM WF_NOTIFICATIONS WN
WHERE MESSAGE_TYPE = 'POAPPRV'
AND USER_KEY = '1047424';
 

Notification Items associated with a Single User:

SELECT WN.*, WA.*
FROM WF_NOTIFICATIONS WN, WF_ACTIVITIES WA
WHERE WN.MESSAGE_NAME = WA.MESSAGE
--AND WN.SENT_DATE > SYSDATE-99
AND WN.STATUS NOT IN ('CANCELED', 'CLOSED')
AND LENGTH(WA.RESULT_TYPE) > 1
AND WA.END_DATE IS NULL
AND WN.RECIPIENT_ROLE = '<USERNAME>';

 

Add dates, if the requirement is to get only specific duration notifications. Also add these two statements, if required:

LENGTH(WA.RESULT_TYPE) > 1     ==>> OPEN, TO DO (NEEDS ACTION)

WA.RESULT_TYPE = '*'        ==>> OPEN, FYI

 

Notification Status à WN.STATUS

OPEN - NOT VIEWED EVEN ONCE - CAN BE FYI OR RESPONSED

CLOSED - VIEWED AT LEAST ONCE

CANCELLED - NO MORE ACTIVE (MOST LIKELY TIMED OUT/ EXPIRED)

 

Some common Work Flow Item Types:

PORCPT    PO_RCV_BUYER_NON_RECEIPT_JRAD

PORPOCHA    NEW_PO_CHANGE_JRAD

PORCPT        PO CONFIRM RECEIPT

PORPOCHA        PO CHANGE APPROVAL FOR REQUESTER

APCCARD    OIE_DUNNING_NOTIFICATIONS_1

POAPPRV    PO_PO_HAS_BEEN_APPROVE

APCCARD        CREDIT CARDS

POAPPRV        PO APPROVAL

 

All Notifications to All Users:

SELECT WN.RECIPIENT_ROLE, COUNT(*)
FROM WF_NOTIFICATIONS WN, WF_ACTIVITIES WA
WHERE WN.MESSAGE_NAME = WA.MESSAGE
AND WN.SENT_DATE > SYSDATE-30
AND WN.STATUS NOT IN ('CANCELED', 'CLOSED')
AND WN.RECIPIENT_ROLE NOT IN ('SYSADMIN')
AND WN.RECIPIENT_ROLE NOT LIKE '%|%'
AND WN.RECIPIENT_ROLE NOT LIKE '%:%'
AND LENGTH(WA.RESULT_TYPE) > 1
--AND ROWNUM < 100
AND WA.END_DATE IS NULL
GROUP BY WN.RECIPIENT_ROLE
HAVING COUNT(*) >= 2
ORDER BY 2 DESC;
 

All Requisition Header and Line Details:

SELECT *
FROM PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRL
WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRH.CREATION_DATE BETWEEN SYSDATE-11 AND SYSDATE
AND ROWNUM < 21;

 

All Purchase Order Header and Line Details:

SELECT POH.*, POL.*
FROM PO_HEADERS_ALL POH, PO_LINES_ALL POL
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POH.CREATION_DATE BETWEEN SYSDATE-11 AND SYSDATE-4
AND ROWNUM < 21;

 

Keywords:

Oracle EBS, PO, AP, Purchasing, Account Payables, SQL, Query, sqlplus