Purchase Requistions that are not converted as PO yet

Here is an alert that we run:

SELECT distinct

a.segment1

,b.line_num

,b.need_by_date

,to_CHAR(b.creation_date, 'dd-mon-rr')

,d.email_address

,max(f.action_date)

,(trunc(sysdate) - trunc(f.action_date))

,d.Full_name

INTO

&reqnum

,&linenum

,&needdate

,&creatdate

,&email

,&appdate

,&dayswait

,&name

FROM

PO_REQUISITION_HEADERs_all a

,PO_REQUISITION_LINES_all b

,PO_REQ_DISTRIBUTIONS_all c

,hr_employees d

,po_action_history f

where

       suggested_buyer_id is not null

and b.requisition_line_id = c.requisition_line_id

and a.authorization_status = 'APPROVED'

and a.requisition_header_id = b.requisition_header_id

and suggested_buyer_id = d.employee_id

and b.source_type_code = 'VENDOR'

and a.org_id =405

and NVL(b.modified_by_agent_flag,'N') = 'N'

and b.line_location_id is null

and f.object_id = a.requisition_header_id

and f.object_type_code = 'REQUISITION'

and f.action_code = 'APPROVE'

and nvl(b.cancel_flag, 'N') = 'N' -----is null

and nvl(b.closed_code, 'N') = 'N' -----is null

and f.sequence_num = (select max(e.sequence_num)

     from po_action_history e

     where e.object_id = a.requisition_header_id

       and e.object_type_code = 'REQUISITION')    

and nvl(a.TRANSFERRED_TO_OE_FLAG, 'N') = 'N'

and (trunc(sysdate) - trunc(f.action_date)) >= 6

group by a.segment1 ,b.line_num ,b.need_by_date ,b.creation_date

,d.email_address ,f.action_date, d.full_name

order by 8,3

Hope this helps