Query to Get PO Approval Document History & Latest Approver

Hello,

if you are looking for a query to fetch the PO Document History, you can use the below query to get the details and query will get the data from the below-attached screen 

**************************************************************

The query for the Approval History:

SELECT DISTINCT 
/* poa.ACTION_CODE,
poa.ACTION_DATE,
poa.IDENTIFICATION_KEY,
poa.OBJECT_ID,
poa.OBJECT_TYPE_CODE,
NVL(poa.PERFORMER_ID, - 1) PERFORMER_ID,
poa.SEQUENCE_NUM,
panf.LIST_NAME */
poa.SEQUENCE_NUM,
pha.segment1 po_number,
pha.ATTRIBUTE1 PN,
(
select description
from pjf_projects_all_vl
where 
segment1=pha.ATTRIBUTE1
and rownum=1
) PDESC,
pha.TYPE_LOOKUP_CODE,
fbu.bu_name procurement_bu,
TO_CHAR(pha.creation_date, 'DD-MM-YYYY') po_cdate,
TO_CHAR(pha.creation_date, 'DD-MM-YYYY') po_sdate,
pha.comments po_desc,
pha.currency_code,
pha.document_status po_status,
pha.revision_num,
rbu.bu_name req_bu,
psv.vendor_name supplier,
poa.ACTION_CODE,
poa.ACTION_DATE,
poa.ACTION_DATE ASSIGNMENT_DATE,
-- PU.USERNAME,
pha.po_header_id,
PSS.vendor_site_code,
'https:' || (
SELECT external_virtual_host
FROM fusion.ask_deployed_domains ad
WHERE ad.deployed_domain_name = 'FADomain'
) || '/xmlpserver/Custom/Procurement/Purchasing/PO Author Detail Report.xdo?&_paramsp_header_id=' || pha.po_header_id || '&_xt=Output&_xf=pdf&_xautorun=true&_linkToReport=true' url,
(
SELECT DISPLAY_NAME
FROM PER_PERSON_NAMES_F
WHERE PERSON_ID = PHA.AGENT_ID
AND NAME_TYPE = 'GLOBAL'
AND ROWNUM = 1
) BUYER,
poa.ROLE_CODE ROLE,
panf.DISPLAY_NAME USERNAME,
poa.PERFORMER_ID,
poa.IDENTIFICATION_KEY,
nvl((select to_char(USER_COMMENT)
from POR_APPROVAL_TASK_COMMENTS patc
where 1=1
and patc.OBJECT_ID=poa.OBJECT_ID
and patc.IDENTIFICATION_KEY=poa.IDENTIFICATION_KEY
and patc.PERFORMER_ID=poa.PERFORMER_ID
and rownum=1 ),POA.NOTE) AI
FROM PO_ACTION_HISTORY poa,
PO_HEADERS_ALL pha,
PER_PERSON_NAMES_F panf,
PO_VERSIONS pv,
fun_names_business_units_v fbu,
fun_names_business_units_v rbu,
poz_suppliers_v psv,
poz_supplier_sites_v PSS
WHERE 1 = 1
AND pha.PO_HEADER_ID = poa.OBJECT_ID
AND poa.OBJECT_TYPE_CODE = 'PO'
AND poa.ACTION_CODE IN (
'SUBMIT',
'SUBMIT CHANGE',
'APPROVE',
'ACCEPT',
'REJECT',
'WITHDRAW',
'CANCEL',
'CANCEL CHANGE ORDER',
'APPROVAL_ERROR',
'APPROVAL_EXPIRATION',
'REQUEST ACKNOWLEDGEMENT',
'IMPLEMENT EXTERNAL CO'
)
AND poa.PERFORMER_ID = panf.PERSON_ID(+)
AND panf.NAME_TYPE = 'GLOBAL'
AND poa.PO_VERSION_ID = pv.VERSION_ID(+)
AND pha.prc_bu_id = fbu.bu_id
AND pha.REQ_BU_ID = rbu.bu_id
AND pha.vendor_id = psv.vendor_id
AND PSS.vendor_id = PSV.vendor_id
AND PSS.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
-- AND pha.segment1 = 'PO-1097'
AND pha.po_header_id = :p_header_id
ORDER BY poa.SEQUENCE_NUM

**************************************************************

The Query to get the Latest Approver:

WITH po_amt

AS (

SELECT (pla.quantity * pla.list_price) po_amount,

pla.po_header_id

FROM po_lines_all pla

)

SELECT DISTINCT pha.po_header_id,

pha.segment1 po_num,

pha.ATTRIBUTE1 project_num,

(

SELECT description

FROM pjf_projects_all_vl

WHERE segment1 = pha.ATTRIBUTE1

AND rownum = 1

) project_name,

(

SELECT sum(po_amount)

FROM po_amt

WHERE po_amt.po_header_id = pha.po_header_id

) po_amount,

pha.type_lookup_code po_type,

TO_CHAR(pha.creation_date, 'DD-MM-YYYY') po_cdate,

TO_CHAR(pha.creation_date, 'DD-MM-YYYY') po_sdate,

nvl((

SELECT to_char(ASSIGNEDDATE, 'DD-MM-YYYY')

FROM fa_fusion_soainfra.wftask

WHERE compositeinstanceid = pov.APPROVAL_INSTANCE_ID

AND ASSIGNEESDISPLAYNAME IS NOT NULL

), TO_CHAR(pah.ACTION_DATE, 'DD-MM-YYYY')) app_Date,

pha.comments po_desc,

pha.currency_code,

pha.document_status po_status,

fbu.bu_name procurement_bu,

Rbu.bu_name REQ_bu,

pha.revision_num,

psv.vendor_name supplier,

ppn.DISPLAY_NAME buyer_name,

pah.po_Version_id,

pha.agent_id,

pov.approval_instance_id,

pov.revision_num POV_revision_num,

(

SELECT max(pah1.ACTION_DATE)

FROM po_versions pov1,

po_action_history pah1

WHERE pov1.Version_id = pah1.po_Version_id

AND NVL(pov1.revision_num, '99999') = NVL(pov.revision_num, '99999')

AND pah1.ACTION_CODE = 'APPROVE'

AND pah1.object_id = pha.po_header_id

AND PAH1.PERFORMER_ID = pha.AGENT_ID

) latest_Approve_Date,

(

SELECT ASSIGNEESDISPLAYNAME

FROM fa_fusion_soainfra.wftask

WHERE compositeinstanceid = pov.APPROVAL_INSTANCE_ID

AND ASSIGNEESDISPLAYNAME IS NOT NULL

AND ROWNUM = 1

) APPROVER,

abs(trunc(nvl((

SELECT ASSIGNEDDATE

FROM fa_fusion_soainfra.wftask

WHERE compositeinstanceid = pov.APPROVAL_INSTANCE_ID

AND ASSIGNEESDISPLAYNAME IS NOT NULL

), pah.ACTION_DATE) - sysdate)) + 1 DAYS,

pha.prc_bu_id,

ppn.person_id,

(

'https://' || (

SELECT external_virtual_host

FROM fusion.ask_deployed_domains ad

WHERE ad.deployed_domain_name = 'FADomain'

) || ':443/xmlpserver/Custom/Procurement/PO Author Approval Report.xdo?_xiasynch=&_xpf=&_xpt=0&_dFlag=false&_edIndex=0&_dIndex=0&_rToken=&_ranDiag=false&_xmode=3&_paramsp_header_id=' || pha.po_header_id || '&_xt=Output&_xf=pdf&_xautorun=true&_linkToReport=true'

) DETAIL_URL

FROM po_headers_all pha,

-- po_lines_all pla,

fun_names_business_units_v fbu,

fun_names_business_units_v rbu,

poz_suppliers_v psv,

per_person_names_f ppn,

po_versions pov,

po_action_history pah

WHERE 1 = 1

--  AND PHA.SEGMENT1 = 'PO-1102'

AND pha.prc_bu_id = fbu.bu_id

AND pha.REQ_BU_ID = rbu.bu_id

AND pha.vendor_id = psv.vendor_id

AND pha.agent_id = ppn.person_id

AND ppn.name_type = 'GLOBAL'

AND pov.Version_id = pah.po_Version_id

AND pha.po_header_id = pah.object_id

AND pha.document_status IN ('PENDING APPROVAL')

AND pha.prc_bu_id IN (:P_BU_ID)

AND (

pha.REQ_BU_ID IN (:P_REQ_BU_ID)

OR LEAST(:P_REQ_BU_ID) IS NULL

)

**************************************************************

                                    Thank You !!