1 Ответить Последний ответ: 16.09.2019 17:10, автор: Stick

    Purchase Requistions that are not converted as PO yet

    user12192160

      Hi Team,

       

      Is there any back query to find out requisitions which are not converted as PO?

       

      Thanks

      Nagaraju

        • 1. Re: Purchase Requistions that are not converted as PO yet
          Stick

          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