Open Requisition of a User
alter session set current_schema=apps;
SELECT DISTINCT pha1.segment1 PO_NUMBER
,prha1.segment1 REQUISITION_NUMBER
,aps.vendor_name SUPPLIER_NAME
, pha1.currency_code AMOUNT_CURRENCY
, to_char(trunc(pha1.CREATION_DATE),'DD-MON-YYYY') CREATION_DATE
FROM apps.po_headers_all PHA1
, apps.po_distributions_all PDA1
, apps.po_req_distributions_all PRDA1
, apps.po_requisition_lines_all PRLA1
, apps.po_requisition_headers_all PRHA1
,ap_suppliers aps
WHERE pha1.po_header_id = pda1.po_header_id
AND ( ( pda1.req_header_reference_num = prha1.segment1
AND pda1.req_line_reference_num = prla1.line_num
)
OR(pda1.req_distribution_id=prda1.distribution_id)
)
AND prda1.requisition_line_id = prla1.requisition_line_id
AND prla1.requisition_header_id = prha1.requisition_header_id
AND pha1.org_id=prha1.org_id
and aps.vendor_id=pha1.vendor_id
and prha1.wf_item_key in
(
select item_key
from wf_notifications
where upper(trim(recipient_role)) like upper('%ashleigh%')
and trunc(sysdate) - trunc(begin_date) < 30
/*and status <> 'OPEN'*/
and message_type = 'REQAPPRV')
and prha1.created_by = 4852
