5 Replies Latest reply: May 8, 2012 9:46 AM by Sandeep Gandhi, Consultant RSS

    Po not created on the basis of Req Number

    846717
      Hello guys,

      I need ur help, I want to find all those requisition numbers which are not used for creation of PO.
      plz guide me & give the script & link of tables.

      Thanks
      ab...
        • 1. Re: Po not created on the basis of Req Number
          846717
          Plz reply asap.....it's urgent
          • 2. Re: Po not created on the basis of Req Number
            Mahendra
            843714 wrote:
            Plz reply asap.....it's urgent
            Check this script..I think it will return all the associated requistions on PO..so change the query accordingly
            Note i have taken ex of standard PO only here..and the agent_id is the particular buyer associated with it..

            select pha.PO_HEADER_ID, pha.SEGMENT1 ,pha.CREATION_DATE,pha.CREATED_BY,pha.AGENT_ID,prha.SEGMENT1 as req
            from
            PO_HEADERS_ALL pha,
            PO_DISTRIBUTIONS_ALL pda,
            PO_REQ_DISTRIBUTIONS_ALL prda,
            PO_REQUISITION_HEADERS_ALL prha,
            PO_REQUISITION_LINES_ALL prla
            where pha.PO_HEADER_ID=pda.PO_HEADER_ID
            and prla.REQUISITION_LINE_ID=prda.REQUISITION_LINE_ID
            and prha.REQUISITION_HEADER_ID=prla.REQUISITION_HEADER_ID
            and pda.REQ_DISTRIBUTION_ID=prda.DISTRIBUTION_ID
            and pha.TYPE_LOOKUP_CODE='STANDARD'
            and pha.AGENT_ID=31698
            and pha.SEGMENT1=:PO_NUM
            order by pha.SEGMENT1

            Let me know if it has helped you in anyway

            HTH
            Mahendra
            • 3. Re: Po not created on the basis of Req Number
              Sandeep Gandhi, Consultant
              Try
              SELECT prha.segment1 req#, prla.line_num, prda.requisition_line_id
                FROM po_req_distributions_all prda,
                     po_requisition_lines_all prla,
                     po_requisition_headers_all prha
               WHERE prda.requisition_line_id = prla.requisition_line_id
                 AND prla.requisition_header_id = prha.requisition_header_id
                 AND NOT EXISTS (SELECT 1
                                   FROM po_distributions_all pda
                                  WHERE pda.req_distribution_id = prda.distribution_id)
              Sandeep Gandhi
              • 4. Re: Po not created on the basis of Req Number
                846717
                Thanks sandeep for reply.....

                run the mentioned query and verify all those req_num but some of the requisitions number are used for create PO.
                I think need to put some more condition in query.

                Thanks
                Ab..
                • 5. Re: Po not created on the basis of Req Number
                  Sandeep Gandhi, Consultant
                  If the PO was created from a req., then the req gets linked to the PO with the req_distribution_id.
                  Such requisitions will not show up on the query.

                  If the user simply typed in the requisition number in the distribution area when creating a PO, then there is no link. And therefore the req. will show up.
                  You should stop users from doing that.

                  Sandeep Gandhi