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, pha.approved_date,SUM (pla.quantity) po_qty, SUM (pla.unit_price) po_price,
--SUM(pla.quantity*pla.unit_price) po_value,
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 approve_by,pha.po_header_id,wn.notification_id,pha.agent_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
AND pha.org_id = pla.org_id
AND pha.authorization_status = 'APPROVED'
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 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 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.agent_id = NVL((select employee_id from fnd_user where user_id=:p_buyer_id),pha.agent_id)
--AND trunc(a.action_date) >= '08-JAN-2019'
---AND trunc(pha.approved_date) between trunc(sysdate-1) and trunc(sysdate)
----AND trunc(wn.begin_date) between trunc(:P_from_date) and trunc(:P_to_date)
--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.approved_date,
pha.org_id,
wn.to_user,
wn.subject,
wn.original_recipient,
fu.user_name,
wn.begin_date,d.name,pha.agent_id,
pha.segment1,pha.revision_num,wn.from_role, pha.po_header_id, wn.notification_id
ORDER BY 1, 2
(SELECT NAME || ' ( ' || short_code || ' )'
FROM hr_operating_units
WHERE organization_id = pha.org_id) ou_name,
pha.segment1 po_num,pha.revision_num, pha.approved_date,SUM (pla.quantity) po_qty, SUM (pla.unit_price) po_price,
--SUM(pla.quantity*pla.unit_price) po_value,
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 approve_by,pha.po_header_id,wn.notification_id,pha.agent_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
AND pha.org_id = pla.org_id
AND pha.authorization_status = 'APPROVED'
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 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 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.agent_id = NVL((select employee_id from fnd_user where user_id=:p_buyer_id),pha.agent_id)
--AND trunc(a.action_date) >= '08-JAN-2019'
---AND trunc(pha.approved_date) between trunc(sysdate-1) and trunc(sysdate)
----AND trunc(wn.begin_date) between trunc(:P_from_date) and trunc(:P_to_date)
--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.approved_date,
pha.org_id,
wn.to_user,
wn.subject,
wn.original_recipient,
fu.user_name,
wn.begin_date,d.name,pha.agent_id,
pha.segment1,pha.revision_num,wn.from_role, pha.po_header_id, wn.notification_id
ORDER BY 1, 2