How to create a procurement Agent & Test PO PDF Output in Oracle Fuion SCM

If are trying to open any PO from the system you should have the below access then only you are able to view or you can make any changes to the existing PO's

1.Your user account should linked to employee Record

Navigation: Clink on the navigator and select User & Roles under "My Team" Task 

Click on the "+" icon to create an employee for the user account

Enter all the mandatory data and then select "Link user account" & Search for your user account to be linked.

and then click on save & close. By completing this step, your user account is associated with an employee record.

Now we will give data set access to the needed user account

To do this below we need to go to setup & maintenance and search for "Manage Data Access for Users" Task and give access

And then save and close, after this step data set access will be added

Then create a procurement agent for your user account

To do this we need to go to Setup & Maintenance and search for "Manage Procurement Agents" Task

Once the Task Opens click on "+" to create a procurement agent for your user account

Give the details of the Business Unit, Agent(Your user account if your account is not appearing, it means your user is not linked to an Employee for this follow the above step)

and you can control the different actions by setting the access priority and then save and close

Now we set all of them, you can test it now you will be able to search a PO and you can view and edit based on the action you needed

Search for a PO

If you want to test any PO PDF output, click the VIEW PDF button.

                                                *************Thank You*************

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 !!