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