4 Replies Latest reply on Oct 4, 2008 11:42 AM by 623098

    Approved Requisitions with no PO Number

    560477
      I need to write a report which shows all Requisitions which have been approved but where no Purchase Order number has been created.

      We have got an issue where Req's are being approved but not turned into PO's...

      They would like a SQL report writing to list all these Approved Requisitions which have no PO created (i.e no PO Number)

      Im struggling to write the SQL for this, i cant seem to work out what im doing wrong. Can anyone explain the functional relationships, and even better the technical relationships?

      Many thanks!
      Gary
        • 1. Re: Approved Requisitions with no PO Number
          618177
          Hi,

          You can work on below statement.

          SELECT *
          FROM po_requisition_headers_all pp,
          po_req_distributions_all prd,
          po_requisition_lines_all prl
          WHERE pp.authorization_status = 'APPROVED'
          AND pp.requisition_header_id = prl.requisition_header_id
          AND prd.requisition_line_id = prl.requisition_line_id
          AND prd.distribution_id NOT IN
          (SELECT ppd.req_distribution_id FROM po_distributions_all ppd
          WHERE ppd.org_id = prd.org_id
          AND ppd.req_distribution_id = prd.distribution_id)

          Bilal SARIOZ
          • 2. Re: Approved Requisitions with no PO Number
            628722
            There is a column line_location_id in the PO_REQUISITION_LINES_ALL which will give you the connection between Req and PO.

            The value in the above column would map to PO_LINE_LOCATIONS_ALL.line_location_id column.

            You can run a sql to verify the reqs for which the PO_REQUISITION_LINES_ALL.line_location_id is null and are approved.

            You can pretty much reuse the sql below.

            -Arun
            • 3. Re: Approved Requisitions with no PO Number
              Subramanyam Jayram
              Hi,
              This query will help to develop the report..
              -----list all Purchase Requisition without a Purchase Order that means a PR has not been autocreated to PO.
              select
              prh.segment1 "PR NUM",
              trunc(prh.creation_date) "CREATED ON",
              trunc(prl.creation_date) "Line Creation Date" ,
              prl.line_num "Seq #",
              msi.segment1 "Item Num",
              prl.item_description "Description",
              prl.quantity "Qty",
              trunc(prl.need_by_date) "Required By",
              ppf1.full_name "REQUESTOR",
              ppf2.agent_name "BUYER"
              from
              po.po_requisition_headers_all prh,
              po.po_requisition_lines_all prl,
              apps.per_people_f ppf1,
              (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,
              po.po_req_distributions_all prd,
              inv.mtl_system_items_b msi,
              po.po_line_locations_all pll,
              po.po_lines_all pl,
              po.po_headers_all ph
              WHERE
              prh.requisition_header_id = prl.requisition_header_id
              and prl.requisition_line_id = prd.requisition_line_id
              and ppf1.person_id = prh.preparer_id
              and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date
              and ppf2.agent_id(+) = msi.buyer_id
              and msi.inventory_item_id = prl.item_id
              and msi.organization_id = prl.destination_organization_id
              and pll.line_location_id(+) = prl.line_location_id
              and pll.po_header_id = ph.po_header_id(+)
              AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)
              AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
              AND PLL.LINE_LOCATION_ID IS NULL
              AND PRL.CLOSED_CODE IS NULL
              AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
              ORDER BY 1,2

              Thanks
              Subramanyam Jayaram
              • 4. Re: Approved Requisitions with no PO Number
                623098
                Actually, you can use standard report 'Purchase Requisition Status Report' from Purchasing Responsibility user,
                you can see any Requisition with PO number and PO Status.
                i assume, you can ignore all requisition with PO number and PO status, while you can progress Requisition without any PO Number and status.