Oracle R12 PO Approval Pending Query
SELECT fu.user_name po_created_by,pha.org_id ,
(SELECT NAME || ' ( ' || short_code || ' )'
FROM hr_operating_units
WHERE organization_id = pha.org_id) ou_name,
pha.segment1 po_num, pha.revision_num, SUM(pla.quantity) po_qty, SUM(pla.unit_price) po_price,
pha.authorization_status, pha.creation_date po_creation_date,
wn.to_user, wn.subject, wn.original_recipient, wn.begin_date,
TRUNC (SYSDATE - wn.begin_date) no_days_pending,d.name Approval_path
,wn.from_role forrwad_by,wn.notification_id,pha.po_header_id
FROM wf_notifications wn,
po_headers_all pha,
fnd_user fu,
po_lines_all pla,
po_action_history a,per_position_structures_v d
WHERE 1=1 --status = 'OPEN'
AND pha.org_id = pla.org_id
AND pha.authorization_status = 'IN PROCESS'
-- AND MESSAGE_TYPE = 'POAPPRV'
-- AND message_name = 'PO_PO_APPROVE_PDF'
AND a.object_id = pha.po_header_id
AND a.approval_path_id = d.position_structure_id
--AND wn.end_date IS NULL
AND NOTIFICATION_ID =(Select max(NOTIFICATION_ID) FROM wf_notifications wn Where pha.segment1 = user_key)
AND a.SEQUENCE_NUM = (Select max(aa.sequence_num) FROM po_action_history aa where aa.object_id = a.object_id and ACTION_CODE='SUBMIT' )
AND pha.segment1 = user_key
AND pha.created_by = fu.user_id
AND pha.po_header_id = pla.po_header_id
AND NVL (pha.cancel_flag, 'N') <> 'Y'
AND pha.org_id = NVL(:P_ORG_ID,pha.org_id)
---- AND pha.creation_date >= '08-JAN-2019'
----AND trunc(wn.begin_date) between trunc(:P_from_date) and trunc(:P_to_date)
AND pha.agent_id = NVL((select employee_id from fnd_user where user_id=:p_buyer_id),pha.agent_id)
--AND NOT EXISTS (SELECT 1 FROM po_action_history pah, per_position_structures_v pps WHERE pps.name LIKE '%Emergency%'
AND pah.SEQUENCE_NUM=(Select max(aa.sequence_num) FROM po_action_history aa where aa.object_id = pah.object_id and ACTION_CODE='SUBMIT')
AND pah.approval_path_id = pps.position_structure_id AND pha.po_header_id = pah.object_id)
GROUP BY pha.authorization_status,
pha.creation_date,
pha.org_id,
wn.to_user,
wn.subject,
wn.original_recipient,
fu.user_name,
wn.begin_date,d.name,
wn.from_role,wn.notification_id,pha.revision_num,
pha.po_header_id,pha.segment1
ORDER BY 1, 2
SELECT fu.user_name po_created_by,pha.org_id ,
(SELECT NAME || ' ( ' || short_code || ' )'
FROM hr_operating_units
WHERE organization_id = pha.org_id) ou_name,
pha.segment1 po_num, pha.revision_num, SUM(pla.quantity) po_qty, SUM(pla.unit_price) po_price,
pha.authorization_status, pha.creation_date po_creation_date,
wn.to_user, wn.subject, wn.original_recipient, wn.begin_date,
TRUNC (SYSDATE - wn.begin_date) no_days_pending,d.name Approval_path
,wn.from_role forrwad_by,wn.notification_id,pha.po_header_id
FROM wf_notifications wn,
po_headers_all pha,
fnd_user fu,
po_lines_all pla,
po_action_history a,per_position_structures_v d
WHERE 1=1 --status = 'OPEN'
AND pha.org_id = pla.org_id
AND pha.authorization_status = 'IN PROCESS'
-- AND MESSAGE_TYPE = 'POAPPRV'
-- AND message_name = 'PO_PO_APPROVE_PDF'
AND a.object_id = pha.po_header_id
AND a.approval_path_id = d.position_structure_id
--AND wn.end_date IS NULL
AND NOTIFICATION_ID =(Select max(NOTIFICATION_ID) FROM wf_notifications wn Where pha.segment1 = user_key)
AND a.SEQUENCE_NUM = (Select max(aa.sequence_num) FROM po_action_history aa where aa.object_id = a.object_id and ACTION_CODE='SUBMIT' )
AND pha.segment1 = user_key
AND pha.created_by = fu.user_id
AND pha.po_header_id = pla.po_header_id
AND NVL (pha.cancel_flag, 'N') <> 'Y'
AND pha.org_id = NVL(:P_ORG_ID,pha.org_id)
---- AND pha.creation_date >= '08-JAN-2019'
----AND trunc(wn.begin_date) between trunc(:P_from_date) and trunc(:P_to_date)
AND pha.agent_id = NVL((select employee_id from fnd_user where user_id=:p_buyer_id),pha.agent_id)
--AND NOT EXISTS (SELECT 1 FROM po_action_history pah, per_position_structures_v pps WHERE pps.name LIKE '%Emergency%'
AND pah.SEQUENCE_NUM=(Select max(aa.sequence_num) FROM po_action_history aa where aa.object_id = pah.object_id and ACTION_CODE='SUBMIT')
AND pah.approval_path_id = pps.position_structure_id AND pha.po_header_id = pah.object_id)
GROUP BY pha.authorization_status,
pha.creation_date,
pha.org_id,
wn.to_user,
wn.subject,
wn.original_recipient,
fu.user_name,
wn.begin_date,d.name,
wn.from_role,wn.notification_id,pha.revision_num,
pha.po_header_id,pha.segment1
ORDER BY 1, 2