10 Replies Latest reply on Sep 15, 2016 7:25 AM by Bommi

    Need help on open POs

    Bommi

      Hi Experts,

           I need a query on open POs with below details.

                PO#

                Deliver to Person ID

                Requestor

                Vendor Number+Site

                Payment Terms

       

           So, I prepared as below

       

      SELECT  poh.segment1   po_number

              ,pod.deliver_to_person_id

              ,ppf.full_name requestor_fullname

              ,aps.segment1||'-'||poh.vendor_site_id   vendornum_siteid

              ,pt.name terms

      FROM    PO_HEADERS_ALL poh

              ,PO_LINES_ALL pol

              ,PO_DISTRIBUTIONS_ALL pod

              ,PER_ALL_PEOPLE_F ppf

              ,AP_SUPPLIERS aps

              ,AP_SUPPLIER_SITES_ALL apss

              ,AP_TERMS_tl pt

      WHERE   1=1

      AND     (poh.closed_code NOT IN ('CLOSED','FINALLY CLOSED')

               OR poh.closed_code IS NULL)

      AND     pol.po_header_id(+)=poh.po_header_id

      AND     pod.po_header_id(+)=poh.po_header_id

      AND     pod.po_line_id=pol.po_line_id

      AND     ppf.person_id=pod.deliver_to_person_id

      AND     aps.vendor_id=poh.vendor_id

      AND        aps.vendor_id=apss.vendor_id

      AND        poh.vendor_site_id=apss.vendor_site_id

      AND     pt.term_id=poh.terms_id

      AND     (poh.authorization_status='APPROVED'

              OR poh.authorization_status IS NULL

              )

      ORDER BY poh.segment1 DESC;

       

       

           But, I have duplication in this.

           Say I have a PO 33774. It has 2 distributions. Where for one distribution I have a requestor name and for other there is no Requestor Name.

           I have one more PO,33772 where it has only one distribution and it has no Requestor Name for that.

           My client requirement is even though, the PO has 2 lines with one having requestor and other with no requestor, he needs only one row. And also has to display the POs having no requestor at all

           So, in our case, I need only one record for PO 33774 and one record for 33772.

           But, by using above query, i got only 1 line 33774 which is fine, but not fetched 33772.

           So, can you please help me on this.

       

       

      Thanks in Advance,

      Bommi

        • 1. Re: Need help on open POs
          John_K

          Your code is wrong anyway because you're not date-tracking per_all_people_f.

          • 2. Re: Need help on open POs
            Bommi

            forget about that... they need only open POs whether user is active or inactive doesn't matter.

            • 3. Re: Need help on open POs
              John_K

              That's not what date-tracking is for. You will end up with duplicate rows with how it's currently written, regardless of the user in/active status.

              • 4. Re: Need help on open POs
                J Reinhart

                You can get the second PO by making the join to the person an outer (optional) join :  Put an open parentheses (  then a plus sign + then a close parentheses )     In here, the screen changed that to the plus sign in the green circle.

                 

                AND     ppf.person_id =pod.deliver_to_person_id

                 

                It will also, however, bring both lines of the first PO, the one without the requestor as well as the one with.  You should get two lines for each PO.

                 

                When you have multiple lines and potentially multiple requestors, your query will return a line for each requestor.  If you really want one line per PO, then take all the references to po_lines, po_distributions, and per_people_f out of the query.   Once that's done, add this as if it is a column being selected:

                 

                 

                ( select decode( count(distinct(pod.deliver_to_person_id)), 1 , min(ppf.full_name), 0, 'No Requestor Identified', 'Multiple Requestors')

                 

                   po_distributions_all pod, apps.per_all_people_f ppf


                  pod.delivery_to_person_id = ppf.person_id ( + )

                 

                    pod.po_header_id = poh.po_header_id) Requestor_Info

                 

                 

                This mini-query will act like a column being selected.  If it finds that all lines have the same requestor, then it will return that person's name.  If none of the lines have a requestor, then it will return the text 'No Requestor Identified'.  If there are lines requested by several people, then it will return the text "Multiple Requestors"

                • 5. Re: Need help on open POs
                  Bommi

                  Hi Reinhart,

                       Very thanks for your inputs

                       But, now client came with one more point as.... If a PO has multiple requestors, then fetch the first Requestor

                       For example, I have a PO, 329764 it has totally 44 lines. And with 4 different requestors added to them and also have NULL (totally 5)

                       So, he wants to fetch the first Requestor if PO has multiple Requestors.

                       Also, I have a question. Does really this case valid? I mean, can a PO has multiple Requestors?

                   

                   

                  Thanks in Advance,

                  Bommi

                  • 6. Re: Need help on open POs
                    Doifode

                    hi,

                     

                    YES, PO can have multiple requester.

                    you can create a sub query in select where you can pass po_header_id to distribution table and get only top most requester.

                     

                    and in your query you are using

                    AND     ppf.person_id=pod.deliver_to_person_id

                    this is wrong in condition when there is no requester ,so it wount have deliver_to_person_id.

                    so as @J Reinhart has said,you need to use outer joint for it.

                     

                    As per your requirement I have tried to modified your query,

                    see if it works for you.

                     

                    SELECT distinct poh.segment1   po_number

                    ,(select pod1.deliver_to_person_id from PO_DISTRIBUTIONS_ALL pod1 where pod1.po_distribution_id =

                    (select min(pod2.po_distribution_id) from PO_DISTRIBUTIONS_ALL pod2 where pod2.po_header_id = poh.po_header_id))deliver_to_person_id

                    ,(select ppf.full_name from PER_ALL_PEOPLE_F ppf where ppf.person_id =(select pod1.deliver_to_person_id from PO_DISTRIBUTIONS_ALL pod1 where pod1.po_distribution_id =

                    (select min(pod2.po_distribution_id) from PO_DISTRIBUTIONS_ALL pod2 where pod2.po_header_id = poh.po_header_id)))requestor_fullname

                            --,ppf.full_name requestor_fullname

                            ,aps.segment1||'-'||poh.vendor_site_id   vendornum_siteid

                            ,pt.name terms

                    FROM    PO_HEADERS_ALL poh

                            ,PO_LINES_ALL pol

                            ,PO_DISTRIBUTIONS_ALL pod

                            ,PER_ALL_PEOPLE_F ppf

                            ,AP_SUPPLIERS aps

                            ,AP_SUPPLIER_SITES_ALL apss

                            ,AP_TERMS_tl pt

                    WHERE   1=1

                    AND     (poh.closed_code NOT IN ('CLOSED','FINALLY CLOSED')

                             OR poh.closed_code IS NULL)

                    AND     pol.po_header_id(+)=poh.po_header_id

                    AND     pod.po_header_id(+)=poh.po_header_id

                    AND     pod.po_line_id=pol.po_line_id

                    AND     ppf.person_id(+)=pod.deliver_to_person_id

                    AND     aps.vendor_id=poh.vendor_id

                    AND        aps.vendor_id=apss.vendor_id

                    AND        poh.vendor_site_id=apss.vendor_site_id

                    AND     pt.term_id=poh.terms_id

                    AND     (poh.authorization_status='APPROVED'

                           OR poh.authorization_status IS NULL

                            )

                    and poh.segment1 = '33772'

                    ORDER BY poh.segment1 DESC;

                     

                    Kindlu check and reply.

                    • 7. Re: Need help on open POs
                      Bommi

                      Hi Doifode,

                           Thanks for your reply.

                           I tried. But when I comment WHERE clause where you passing PO# (and poh.segment1 = '33772') and run, then it is returning error as shown in below screenshot.

                           Moreover, when I tried with PO# 39764 where it has 5 requestors, then it is printing NULL for 'requestor_fullname'.

                       

                      PO Error Screen.png

                       

                           If possible, can you share your mail ID, so that I will send list of POs (PO#, Requestors-all the requestors for each PO, Vendor_num-site_id,terms) in Excel. It may help you in preparing the query.

                       

                       

                      Thanks and Regards,

                      Bommi

                      • 8. Re: Need help on open POs
                        J Reinhart

                        Hi Bommi,

                         

                        This will return the first requestor:

                         

                         

                        (select min(ppf.full_name) from apps.per_all_people_f ppf where ppf.person_id =

                         

                        (select pod.deliver_to_person_id from apps.po_distributions_all pod where pod.po_distribution_id =

                         

                        (select min(po_distribution_id) from apps.po_distributions_all pod2 where pod2.po_header_id = poh.po_header_id and pod.deliver_to_person_id is not null) ) )


                        It takes the requestor id from the first po distribution that has a requestor specified, and returns his/her name.   PO distribution id's are sequential, so the lowest number is the one created first. 

                         

                        As to whether there can be multiple requestors, it depends on your business.  If you have people each enter their own POs directly, then it might not happen.   If you gather up similar requirements from many people to create the PO, then it will happen.  If you are re-doing the office, for example, and ask each manager how many chairs he needs for his team, then there could be one distribution for each manager, with their name and department on it.  Companies also group POs to get volume discounts -- if you buy 500 chairs on one PO for the whole office building, you will probably get a lower price per chair than if each manager ordered a dozen separately.

                         

                        Regards,

                         

                        J

                        • 9. Re: Need help on open POs
                          Doifode

                          hi,

                          modified query,

                           

                          SELECT distinct poh.segment1   po_number

                          ,(select pod1.deliver_to_person_id from PO_DISTRIBUTIONS_ALL pod1 where pod1.po_distribution_id =

                          (select min(pod2.po_distribution_id) from PO_DISTRIBUTIONS_ALL pod2 where pod2.po_header_id = poh.po_header_id))deliver_to_person_id

                          ,(select min(ppf.full_name) from PER_ALL_PEOPLE_F ppf where ppf.person_id =(select pod1.deliver_to_person_id from PO_DISTRIBUTIONS_ALL pod1 where pod1.po_distribution_id =

                          (select min(pod2.po_distribution_id) from PO_DISTRIBUTIONS_ALL pod2 where pod2.po_header_id = poh.po_header_id)))requestor_fullname

                                  --,ppf.full_name requestor_fullname

                                  ,aps.segment1||'-'||poh.vendor_site_id   vendornum_siteid

                                  ,pt.name terms

                          FROM    PO_HEADERS_ALL poh

                                  ,PO_LINES_ALL pol

                                  ,PO_DISTRIBUTIONS_ALL pod

                                  ,PER_ALL_PEOPLE_F ppf

                                  ,AP_SUPPLIERS aps

                                  ,AP_SUPPLIER_SITES_ALL apss

                                  ,AP_TERMS_tl pt

                          WHERE   1=1

                          AND     (poh.closed_code NOT IN ('CLOSED','FINALLY CLOSED')

                                   OR poh.closed_code IS NULL)

                          AND     pol.po_header_id(+)=poh.po_header_id

                          AND     pod.po_header_id(+)=poh.po_header_id

                          AND     pod.po_line_id=pol.po_line_id

                          AND     ppf.person_id(+)=pod.deliver_to_person_id

                          AND     aps.vendor_id=poh.vendor_id

                          AND        aps.vendor_id=apss.vendor_id

                          AND        poh.vendor_site_id=apss.vendor_site_id

                          AND     pt.term_id=poh.terms_id

                          AND     (poh.authorization_status='APPROVED'

                                 OR poh.authorization_status IS NULL

                                  )

                          --and poh.segment1 = '33772'

                          ORDER BY poh.segment1 DESC;

                           

                          Kindlu check and reply.

                          • 10. Re: Need help on open POs
                            Bommi

                            Hi Doifode,

                                 Great....

                                 Your query is working fine and returned expected data.

                                 Thank you so much

                             

                            SELECT  sub1.po_number  po_number

                                    ,sub1.requestor_num

                                    ,sub1.requestor_name

                                    ,sub1.vendornum_siteid

                                    ,sub1.terms

                            FROM    (SELECT

                                            sub2_sql.po_number

                                            ,MIN(sub2_sql.min_line) line_num

                                            ,MIN(sub2_sql.min_dist_num) dist_num

                                            ,MIN(sub2_sql.deliver_to_person_id) person_id

                                            ,ppf1.employee_number   requestor_num

                                            ,ppf1.full_name requestor_name

                                            ,sub2_sql.vendor_site_code

                                            ,sub2_sql.vendornum_siteid

                                            ,sub2_sql.terms

                                    FROM    (SELECT  DISTINCT poh.segment1   po_number

                                                    ,MIN(pol.line_num) min_line

                                                    ,MIN(pod.distribution_num) min_dist_num

                                                    ,MIN(pod.po_distribution_id)

                                                    ,apss.vendor_site_code

                                                    ,MIN(pod.deliver_to_person_id)  deliver_to_person_id

                                                    ,aps.segment1||'-'||poh.vendor_site_id   vendornum_siteid

                                                    ,pt.name terms

                                            FROM    PO_HEADERS_ALL poh

                                                    ,PO_LINES_ALL pol

                                                    ,PO_DISTRIBUTIONS_ALL pod

                                                    ,PER_ALL_PEOPLE_F ppf

                                                    ,AP_SUPPLIERS aps

                                                    ,AP_SUPPLIER_SITES_ALL apss

                                                    ,AP_TERMS_tl pt

                                            WHERE   1=1

                                            --AND     poh.segment1=33731

                                            AND     (poh.closed_code NOT IN ('CLOSED','FINALLY CLOSED')

                                                     OR poh.closed_code IS NULL)

                                            AND     pol.po_header_id(+)=poh.po_header_id

                                            AND     pod.po_header_id=poh.po_header_id

                                            AND     pod.po_line_id(+)=pol.po_line_id

                                            AND     ppf.person_id(+)=pod.deliver_to_person_id

                                            AND     aps.vendor_id=poh.vendor_id

                                            AND     aps.vendor_id=apss.vendor_id

                                            AND     poh.vendor_site_id=apss.vendor_site_id

                                            AND     pt.term_id=poh.terms_id

                                            AND     (poh.authorization_status NOT IN ('REJECTED')

                                                     OR poh.authorization_status IS NULL

                                                     )

                                            --AND     ppf.full_name IS NOT NULL

                                            GROUP BY    poh.segment1

                                                        ,aps.segment1||'-'||poh.vendor_site_id

                                                        ,apss.vendor_site_code

                                                        ,pt.name

                                            ) sub2_sql

                                            ,PER_ALL_PEOPLE_F ppf1

                                    WHERE   1=1

                                    AND     ppf1.person_id(+)=sub2_sql.deliver_to_person_id

                                    GROUP BY sub2_sql.po_number

                                            ,ppf1.employee_number

                                            ,ppf1.full_name

                                            ,sub2_sql.vendor_site_code

                                            ,sub2_sql.vendornum_siteid

                                            ,sub2_sql.terms

                                    ) sub1

                            ORDER BY sub1.po_number DESC;

                             

                                 I actually used subqueries as below. And I observed that the cost of query is almost similar, but, your query cost is 1089 and mine is 1093.

                             

                            Thank you,

                            Bommi