1 Reply Latest reply on Jul 21, 2010 3:45 PM by Sandeep Gandhi, Consultant

    Reg: PO and Requisition

      Hi Consultants,

      We have a requirement to report the all NOn sale Purchase order and Requision those were approved by a same person(user).

      Anyone please advice me or send me any sample query.

      i developed below query for this but need to include approval condition.

      SELECT pv.vendor_name "SUPPLIER_NAME",
      pv.segment1 "SUPPLIER_NUM",
      prh.segment1 " REQUISITION_NUM" ,
      poh.segment1 "PO_NUM",
      nvl(por.release_num,0) "REL_NUM"
      ( select sum(unit_price * quantity)
      from po_lines_all
      where po_header_Id = poh.po_header_id) " PO_AMOUNT",
      per.full_name "BUYER_NAME",
      peb.full_name "REQUESTER_NAME"
      FROM po_headers_all poh,
      po_lines_all pol,
      po_line_locations_all pll,
      po_releases_all por,
      po_distributions_all pld,
      po_req_distributions_all prd,
      po_requisition_liness_all prl
      po_requisition_headers_all prh,
      po_vendors pv,
      per_all_people_f per,
      per_all_people_f peb ,
      WHERE poh.po_header_id = pol.po_header_id
      AND poh.approved_flag = 'Y'
      AND pol.po_line_id = pll.po_line_id
      AND pll.line_location_id = pld.line_location_id
      AND pll.po_release_id = por.po_release_id (+)
      AND pld.distribution_id = prd.req_distribution_id
      AND prd.requisition_line_id = prl.requisition_line_id
      AND prl.requisition_header_id = prh.requisition_header_id
      AND poh.vendor_id = pv.vendor_id
      AND poh.agent_id = per.person_id (+)
      AND pld.deliver_to_person_id = peb.person_id