10 Replies Latest reply on Jun 6, 2012 5:22 AM by User385867

    PO- Recommunicated - Need to find who has done that

    User385867
      Hi Team,

      I am having one interesting question on PO email communication to supplier.

      I have a PO which was created , shipped, invoiced and closed in March-2012, but unfortunately some body from business users re-communicated the PO to supplier again on 2nd-May-2012 , and the supplier sent the goods again. So now we are trying to find which business user has re-communicated the PO?

      Verified the data in below wf tables but no clue.
      select * from wf_notifications where notification_id = 79252394;
      select * from wf_item_activity_statuses where item_key like '1029422%';
      select * from wf_items where item_key like '1029422%';

      PO Re-communication steps.

      PurchaseOrder Summary > Query with PO Number > Tools > Communicate > Select Emaila check box and enter Email and Press ok.

      Thanks,
      Suresh
        • 1. Re: PO- Recommunicated - Need to find who has done that
          Mahendra
          Suresh,

          whenever PO is sent for approval or recommunicated...
          It triggers PO Output for Communication conncurrent program..correct..

          So get the po_header_id for that specific PO from Po_headers_all
          Po output for communication takes number of parameters...in that it takes po_header_id too..

          run the below query with po_header_id

          select * from FND_CONCURRENT_REQUESTS where argument21='114486' order by request_date desc

          this should list out the latest records for that PO

          get the requested_by from the above query..

          check with fnd_user table to get the username...

          Let me know if that helped you

          HTH
          Mahendra
          • 2. Re: PO- Recommunicated - Need to find who has done that
            User385867
            Hi Mahendra,

            First of all thanks for your reply.

            I have verified the query you given, but it din't returned any data. Also I don't see any concurrent program getting triggered after re-communicating the PO.

            Just fyi..

            I have done some further investigation below but ended up with no solution.

            Step 1: Reproduced the issue in dev environment by enabling the SQL Trace, so that all sql statements (insert and update) will be captured.
            Step 2: What is happening in the flow is, the resubmitting process will just make an entry in wf_notifications table and leaves it (request will be in pending status).
            Step 3: We have every 5 min scheduled Work flow Background Process is place, which will process all pending notifications.
            Step 4: So waited till next workflow background process completes to process my notification.
            Step 5: So now I have the sql trace of both My action Plan + Workflow Background Process.
            Step 6: From the SQL trace, the entire process touching below tables..
            select * from wf_notifications where notification_id = 79252394;
            select * from wf_item_activity_statuses where item_key = '1106292-18189';
            select * from wf_items where item_key = '1106292-18189'
            select * from WF_NOTIFICATION_ATTRIBUTES where notification_id=79252394
            select * From WF_COMMENTS where notification_id=79252394
            select * from FND_SEARCHABLE_CHANGE_LOG where pk_value_1=79252394
            select * from WF_COMMENTS where notification_id=79252394;
            select * from WF_ITEM_ACTIVITY_STATUSES where notification_id=79252394;

            select printed_date,print_count, p.*
            from po_headers_all p where po_header_id=1106292 (Here these 2 columns printed_date,print_count, will get updated)

            But none of the table capturing the user information.

            Regards,
            suresh
            • 3. Re: PO- Recommunicated - Need to find who has done that
              Mahendra
              we have that setup in our instance...we are on R12..
              i will try to check if we can find any other way...

              can you check this thread...as how can we enable audit tracking..

              How to get PO CHANGE HISTORY from back end tables?

              Mahendra
              • 4. Re: PO- Recommunicated - Need to find who has done that
                PranitSaha
                Hi,

                You may use the below SQL.

                SELECT *
                FROM
                fnd_concurrent_requests
                WHERE concurrent_program_id IN
                (
                SELECT concurrent_program_id
                FROM
                fnd_concurrent_programs_tl
                WHERE user_concurrent_program_name = 'PO Output for Communication"
                ) AND argument3 = <PO Number>
                ORDER BY last_update_date DESC

                See whether it returns any rows.

                Thanks,

                PS.
                • 5. Re: PO- Recommunicated - Need to find who has done that
                  User385867
                  Hi PS,

                  Below query not returning any data . I also I don't see this report 'PO Output for Communication" triggering after recommunication. But as per the trace files po_headers table us updating with columns printed_date and print_count values when recommunication is done.


                  SELECT *
                  FROM
                  fnd_concurrent_requests
                  WHERE concurrent_program_id IN
                  (
                  SELECT concurrent_program_id
                  FROM
                  fnd_concurrent_programs_tl
                  WHERE user_concurrent_program_name = 'PO Output for Communication"
                  ) AND argument3 = <PO Number>
                  ORDER BY last_update_date DESC
                  • 6. Re: PO- Recommunicated - Need to find who has done that
                    PranitSaha
                    Hi Suresh,

                    Can you check the following setup and see the value.

                    Responsibility: Purchasing Super User
                    Navigation: Setup -> Organization -> Purchasing Options
                    Go to "Control" Tab
                    Check the value in the "PO Output Format" field.

                    If the value is "PDF", the query mentioned earlier should return data.

                    If the value is "Text", the query below should help you to identify the request.

                    SELECT *
                    FROM
                    fnd_concurrent_requests
                    WHERE concurrent_program_id IN
                    (
                    SELECT concurrent_program_id
                    FROM
                    fnd_concurrent_programs_tl
                    WHERE user_concurrent_program_name = 'Print Purchase Orders'
                    ) AND argument3 LIKE '%<PO_NUMBER>'
                    ORDER BY last_update_date DESC

                    Let us know your observations.

                    Thanks,

                    PS.
                    • 7. Re: PO- Recommunicated - Need to find who has done that
                      User385867
                      Hi PS

                      The system is on R12, so I don't see a control tab, morover the purchase options page is OA page in R12.
                      I found the similar information in the OA page.

                      In the Purchase Options page, under Document control section I can see the following field..

                      Output Format: PDF

                      But still the first query not returned any data.

                      Regards,
                      Suresh
                      • 8. Re: PO- Recommunicated - Need to find who has done that
                        Mahendra
                        935244 wrote:
                        Hi PS

                        The system is on R12, so I don't see a control tab, morover the purchase options page is OA page in R12.
                        I found the similar information in the OA page.

                        In the Purchase Options page, under Document control section I can see the following field..

                        Output Format: PDF

                        But still the first query not returned any data.

                        Regards,
                        Suresh
                        Suresh,

                        How does the communicate gennerally go to supplier when the PO was approved??
                        email..fax..xml or edi??

                        Generally in our client we have this as email with pdf style..so po out for communication kick off automatically

                        Thanks
                        Mahendra
                        • 9. Re: PO- Recommunicated - Need to find who has done that
                          PranitSaha
                          Hi Suresh,

                          The automatic printing gets initiated only on the approval and revisions of PO.

                          Therefore, as a workaround, you may refer the PO action history to see the latest approval and revisions to determine who has printed the PO.

                          To be more confirmed, compare the revision/approval time with the PRINTED_DATE column value in the PO_HEADERS_ALL table.

                          Thanks,

                          PS.
                          • 10. Re: PO- Recommunicated - Need to find who has done that
                            User385867
                            Hi PS,

                            Verified the PO Action history table but no luck.

                            Here is my print date from po_header..

                            select printed_date,print_count, p.*
                            from po_headers_all p where po_header_id=1106292
                            Output
                            ---------
                            2/05/2012 2:05:47 PM 2

                            and I don't see any relevant entry in the po action history

                            select * from po_headers_all where po_header_id=1106292;
                            There is no entry found with date 2/05/2012 2:05:47 PM

                            Thanks
                            Suresh