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