Showing posts with label Purchasing. Show all posts
Showing posts with label Purchasing. Show all posts

Saturday, May 17, 2014

Open Requisition of a User

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