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
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
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
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
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.*
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
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
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(*)
WHERE PRH.CREATION_DATE > sysdate-30;
PO Purchase Requisition Lines: Created during past 30 days
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
FROM apps_fnd.PO_HEADERS_ALL POH
WHERE POH.CREATION_DATE > sysdate-30;
PO Purchase Order Lines: Created during past 30 days
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:
