13 Replies Latest reply on Jan 19, 2010 7:11 AM by 687471

    Help Me - Requisition

    629779
      I have a requirement like this.
      I want to create a Pending Requisitions Report.
      Pending Requisitions in the sense - If a PO is not created for an approved requisition
      then that Requisition is considered to be Pending.

      How to accomplish this.Which table maps between PO and Requisition

      Thanks in Advance.
        • 1. Re: Help Me - Requisition
          578444
          Hi Binod,

          When ever a requisition get converted to PO, then the line_location_id in the po_requisition_lines_all table contain the po_line_locations_all.line_location_id value. Else this column contains NULL. Similarly the po_req_distributions_all.distribution_id is being stored in the req_distribution_id column of po_distributions_all table.
          Please try to join the requisition & PO tables with the above 2 condtions to get the pending requisiont report. Let me know if you are still facing any issue.

          Regards,
          Jyoti
          • 2. Re: Help Me - Requisition
            629779
            Thanks for the reply.

            What if the case PO Created Manually.
            • 3. Re: Help Me - Requisition
              588543
              The above mentioned link will exist only in the case of Autocreated POs. For manually created POs, there is no link. Depending on your business requirement, you need to scan through the Purchase Orders and find out if the requisition is already satisfied or not.

              Cheers,
              Ganesh
              • 4. Re: Help Me - Requisition
                SachinAhuja
                Whichever way you create Requisition, if it converted into the PO then line_location_id will get populated.
                • 5. Re: Help Me - Requisition
                  588543
                  By manually created PO, I think he talks about a PO created from scratch without any reference to the requisition.

                  Cheers,
                  Ganesh
                  • 6. Re: Help Me - Requisition
                    686383
                    Yes Ganesh

                    For Example
                    If i am raising two requisition

                    Req no 34 "Two IBM Labtops"
                    once again another requisition
                    Req No 35 "Two IBM Laptops"

                    Now I am creating a purchase order manually (not using Auto create) for the Req no : 34
                    PO number : 121
                    "Two IBM Laptops"


                    In my report i should show the pending Requisitions like

                    PO is not created for Req no 35.

                    Is there any way i could link Requisiition and PO when PO created manually.Can i add Descriptive flexfiel in PO Form.

                    Thanks

                    Vinod

                    Edited by: vinothkumar.d@interlaceindia.com on Apr 5, 2009 7:11 PM
                    • 7. Re: Help Me - Requisition
                      578444
                      Hi Vinod,

                      As there is no such direct link between requisition & PO if the PO is created manually. You can use the DFF available in the PO headers to store the requisition number or any information related to requisition as per your requirement.

                      Regards,
                      Jyoti
                      • 8. Re: Help Me - Requisition
                        588543
                        Could you please explain your business case here. If a requisition is already there, why you have to create a PO manually instead of using Autocreate?

                        Just curious :)

                        Cheers
                        Ganesh
                        • 9. Re: Help Me - Requisition
                          686383
                          See this is the Business Case

                          I have a person called ENGINEER who creates Requision
                          HOD is the Person who will create the PO if requisition is approved

                          Now I want to generate a report(This shows the Requisitions for which
                          PO is not created) which will be used for HOD who can create the PO according to the Report

                          HOD can group the requisition and can create as a single PO
                          for example
                          Req No: 34 "Two IBM Laptops"
                          Req No: 35 "Two IBM Laptops"
                          Req No: 36 "Two IBM Laptops"

                          If HOD has created a PO Manually combining 34 & 36
                          Po number : 121
                          "Four IBM Laptops"

                          I have to show a report like
                          PO is not created for Req No 35 .

                          Edited by: vinothkumar.d@interlaceindia.com on Apr 6, 2009 3:00 AM
                          • 10. Re: Help Me - Requisition
                            588543
                            The HOD can still use Autocreate. Autocreate provides lots of flexibility for the requisition grouping.
                            Cheers
                            Ganesh
                            • 11. Re: Help Me - Requisition
                              686383
                              HOD can even add additional Items and also can additional quantity if he want.
                              • 12. Re: Help Me - Requisition
                                sanni
                                REQUISITIONS WHICH ARE APPROVED BUT PO IS NOT CREATED....IF IT IS MANUALLY CREATED THEN U SHOULD CHANGE THE QUERY ACCORDING TO UR REFERENCE REQUISTION NO

                                SELECT PRD.DISTRIBUTION_ID FROM
                                PO_REQUISITION_HEADERS_ALL PRH,PO_REQUISITION_LINES_ALL PRL,PO_REQ_DISTRIBUTIONS_ALL PRD
                                WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
                                AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
                                AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
                                AND PRD.DISTRIBUTION_ID NOT IN (SELECT PRD.DISTRIBUTION_ID FROM
                                PO_REQUISITION_HEADERS_ALL PRH,PO_REQUISITION_LINES_ALL PRL,PO_REQ_DISTRIBUTIONS_ALL PRD,PO_DISTRIBUTIONS_ALL PDA
                                WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
                                AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
                                AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
                                AND PRD.DISTRIBUTION_ID = PDA.REQ_DISTRIBUTION_ID)
                                • 13. Re: Help Me - Requisition
                                  687471
                                  Hi,


                                  There are two tables named PO_REQ_DISTRIBUTIONS_ALL and PO_DISTRIBUTIONS_ALL, These tables contains the reference between requisition and purchase orders.

                                  The distribution_id column in PO_REQ_DISTRIBUTIONS_ALL is mapped with req_distribution_id column in PO_DISTRIBUTIONS_ALL table.

                                  user9007858 gave the correct query:

                                  The query below is just a tuned version.

                                  SELECT PRD.DISTRIBUTION_ID
                                  FROM PO_REQUISITION_HEADERS_ALL PRH,
                                  PO_REQUISITION_LINES_ALL   PRL,
                                  PO_REQ_DISTRIBUTIONS_ALL   PRD
                                  WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
                                  AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
                                  AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
                                  AND PRD.DISTRIBUTION_ID NOT IN
                                  *(SELECT pda.req_distribution_id*
                                  FROM PO_DISTRIBUTIONS_ALL       PDA
                                  where pda.req_distribution_id is not null
                                  *)*