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

    Approved reqs not on PO.

    589474
      Helo there,
      Is there a way to search all the requisitions in Ipro which are approved but not on a PO?? Or is there a standard report which i can run to see all the approved reqs which are not on a PO??


      Thanks!!
        • 1. Re: Approved reqs not on PO.
          527590
          Dear user586471,

          You can get the list of approved requisitions using the Autocreate form. There you can give the status as approved and get the list of requisitions which are approved as well as PO creation not done...

          Regards
          Merlin Rajesh
          • 2. Re: Approved reqs not on PO.
            602093
            Run this query, this will give you all the Requisitions not placed on a PO: (I myself experimented this solution)

            SELECT prh.segment1 "List of Requisitions"
            FROM po_requisition_headers_all prh.
            WHERE
            prh.requisition_header_id IN
            (
            SELECT requisition_header_id
            FROM po_requisition_lines_all
            WHERE
            requisition_line_id IN
            (
            SELECT requisition_line_id
            FROM po_req_distributions_all
            WHERE
            distribution_id NOT IN (SELECT po_req_distribution_id FROM po_distribution_id)
            )
            • 3. Re: Approved reqs not on PO.
              583637
              You can find your Approved Req in Req.Summary Form.
              If you know the Item Number .Query with Item Number Otherewise you can search with Satus also.
              • 4. Re: Approved reqs not on PO.
                608922
                Adding one more condition to get approved req's to Jith's solution:
                SELECT prh.segment1 "List of Requisitions" 
                FROM po_requisition_headers_all prh.
                WHERE
                prh.requisition_header_id IN
                (
                SELECT requisition_header_id 
                FROM po_requisition_lines_all 
                WHERE
                requisition_line_id IN
                (
                SELECT requisition_line_id 
                FROM po_req_distributions_all
                WHERE
                distribution_id NOT IN (SELECT po_req_distribution_id FROM po_distribution_id)
                ) 
                AND prh.approval_status='APPROVED'
                Message was edited by:
                user605919
                • 5. Re: Approved reqs not on PO.
                  628722
                  In R12, I think you can directly check the reqs_in_pool_flag column in PO_REQ_LINES_ALL. This column if set to Y, denotes the requistion can be used in Autocreate which means it has not been placed on a PO. And to be on the safer side we can also join with PO_REQ_HEDAERS_ALL to get the approval status of Req .
                  • 6. Re: Approved reqs not on PO.
                    629504
                    Hi ,

                    Use the advanced search functionality under Requisitions in iProc to search for all approved reqs .

                    Regards,

                    Sanjam
                    • 7. Re: Approved reqs not on PO.
                      560477
                      I cant get this piece of code to work :

                      SELECT prh.segment1 "List of Requisitions"
                      FROM po_requisition_headers_all prh.
                      WHERE
                      prh.requisition_header_id IN
                      (
                      SELECT requisition_header_id
                      FROM po_requisition_lines_all
                      WHERE
                      requisition_line_id IN
                      (
                      SELECT requisition_line_id
                      FROM po_req_distributions_all
                      WHERE
                      distribution_id NOT IN (SELECT po_req_distribution_id FROM po_distribution_id)
                      ) )

                      It just returns no rows, when i know it should display 700+ req lines with no po number.

                      Any ideas?
                      • 8. Re: Approved reqs not on PO.
                        623098
                        how about using "Purchase Requisition Status Report"?