13 Replies Latest reply on Jul 30, 2008 4:27 PM by 514497

    How to get PO CHANGE HISTORY from back end tables?

    514497
      Hi All,

      I need to get the PO Change history from the back end reason being users want to see not only the change made but who made the changes on those PO's. The PO change history.html doesnot give the information of the user who made changes to the PO. Can any one provide me the query involved in this html page. I can add the updated_by column to that. I built a query already but it is not giving me the field altered on the PO Line.

      I learnt that we can get the information from the archive tables but my question is how to know which field has been changed from the original PO to the revised PO. I am unable to figure out if that revision is for Unit Price change or amount change or Bill to change etc. i.e., as it shows in PO change History html page I need to see which field is altered on a particular PO. Is that possible?

      Thanks for your help!
      Prathima

      Message was edited by:
      Prathima
        • 1. Re: How to get PO CHANGE HISTORY from back end tables?
          550926
          How can you guarantee that last_updated_by etc columns are accurate?
          What if updates happen as a result of Workflow background process?

          My advice to you is to enable audit on the table.

          To implement auditing, follow the steps in below link
          http://oracle.anilpassi.com/audit-trail-in-oracle-apps-ebs.html

          Thanks
          Anil Passi
          • 2. Re: How to get PO CHANGE HISTORY from back end tables?
            514497
            Hi Anil,

            Turning Audit on PO_LINES_ALL helped to track the changes. I have a problem though.
            In the sense - Below is the query I created to track changes on Unit Price of a Po Line.

            SELECT
            poh.segment1 po_num
            ,pol.line_num po_line_num
            ,pola.unit_price price_changed_from
            ,pol.unit_price price_changed_to
            ,fnd1.description created_by
            ,fnd.description last_updated_by
            ,fnd2.description previously_updated_by
            --,decode(pola.audit_transaction_type,'U','UPDATE','I','INSERT') audit_transaction_type
            ,pol.last_update_date
            FROM po.po_lines_all_a pola,po.po_lines_all pol,apps.fnd_user fnd,apps.fnd_user fnd1,po.po_headers_all poh,apps.fnd_user fnd2
            WHERE pola.po_line_id = pol.po_line_id
            AND pola.audit_transaction_type = 'U'
            and poh.po_header_id = pol.po_header_id
            AND fnd.user_name = pola.audit_user_name
            and pola.unit_price is not null
            and fnd1.user_id = pol.created_by
            and fnd2.user_id(+) = pola.LAST_UPDATED_BY

            Now the problem is
            When I update a line twice. intially I changed the unitprice value of line 4 from .72 to 0.8 and later I changed it back to 0.8 to 0.72. Intial Changed to 0.72 shuld be 0.8 but it changed to 0.72 as it is pulling from po_lines_all table. How can you capture that changed to???

            po_num Line Changed from Changed to
            26603     4     0.72000      0.72000
            26603     4     0.80000     0.72000

            Please help.
            Thanks,
            Prathima
            • 3. Re: How to get PO CHANGE HISTORY from back end tables?
              602093
              Use as many columns in audit tables as many changes you are trying to track.

              Eg: You want to track the changes made from 1 to 2, then 2 to 3 and then 3 to 1.
              Have 3 old and new columns like old_val1,old_val2,old_val3 and new_val1,new_val2,new_val3.

              Otherwise in general you can track only the latest changed and changing values

              Rgds,
              Jithendra
              • 4. Re: How to get PO CHANGE HISTORY from back end tables?
                514497
                Can we add the old_val1, old_val2 and new_val1,new_val2 etc to audit table? How will that work? The requirement has been modified now to track specific columns on a PO.

                User requested to track changes for Unit Price, Quantity, Ship to, Promise date and Account code on a PO.

                But now the problem is when I turn audit on PO_HEADERS_ALL, PO_LINES_ALL , PO_LINE_LOCATIONS_ALL and PO_DISTRIBUTIONS_ALL any change made on po_header table will have po_header_id but not in po_lines_all audit table.

                So I am stuck as in how I can put all these changes together on one report, I am unable to join the audit tables to get track exact changes.

                Any ideas please?

                Thanks,
                Prathima
                • 5. Re: How to get PO CHANGE HISTORY from back end tables?
                  514497
                  When audit is turned on the PO_LINE_ALL table why PO_HEADER_ID is not populated in the po_lines_all_a (audit table) if any change is made on a po line ?

                  Please help with your ideas on this!

                  Thanks,
                  Prathima
                  • 6. Re: How to get PO CHANGE HISTORY from back end tables?
                    628428
                    Prathima,
                    I think you should use archiving table for this. Not sure what is your archiving setting (when you archive), but that table will give you everything. For every change you make, you should find a new row in that table for a given header or line.

                    You can compare the control tables to the max row in the archive table for a given column or columns.

                    Thanks
                    Nagamohan
                    • 7. Re: How to get PO CHANGE HISTORY from back end tables?
                      514497
                      But again even if we can get the information from the archive tables my question is how to know which field has been changed from the original PO to the revised PO. I am unable to figure out if that revision is for Unit Price change or amount change or Bill to change etc.

                      I figured out that the audit tables creates corresponding views also which stores some of the information.

                      I have written a PL/SQL code to capture the changes and it works good for PO_HEADERS_ALL changes (like ship to location etc) and created a temp table to dump all these values into it so that my report will be easier.

                      Now I am in process to code for PO_LINES_ALL changes based on a view which captures po_header_id for all the changed lines.

                      Already code is growing bigger and bigger and I am loosing track of it.

                      Are there any easy methods to capture this information? I am not sure if that will be easier also :-( !!!!!

                      Any ideas are appreciated !

                      Thank you,
                      Prathima
                      • 8. Re: How to get PO CHANGE HISTORY from back end tables?
                        611584
                        Hi Prathima,
                        what is issue with using archive tables ?

                        You can capture all information that you need..

                        Thanks
                        • 9. Re: How to get PO CHANGE HISTORY from back end tables?
                          514497
                          From archive tables I cannt tell what exactly has changed I mean to say how to know if that revision is for Unit Price or Quantity change etc. How to know that from Archive tables? I need to display changed from value and changed to value of any column and also who changed it in the report.

                          That is the issue with the archive tables. Correct me if I am wrong.

                          Thanks,
                          Prathima
                          • 10. Re: How to get PO CHANGE HISTORY from back end tables?
                            611584
                            I believe whenever there is a change which causes a new revision gets logged in archive table, if your Archive on field is set to 'Communicate' or set to 'Approve'.
                            Refer to Note:315607.1

                            I know its not exactly same problem you have, but there a zip file attached (sql to compare po_header_all and archive) with this doc, you can have a similar query to compare headers and archive OR two rows of archive.

                            Let me know if it helps.

                            regards,
                            Saurabh
                            • 11. Re: How to get PO CHANGE HISTORY from back end tables?
                              514497
                              Saurabh,

                              Thank you for sharing the information. I will let you know if that works.

                              Thank You,
                              Prathima
                              • 12. Re: How to get PO CHANGE HISTORY from back end tables?
                                514497
                                The queries which we have in the Zip file (Note # 315607.1) is giving me the data which is not syncronized between the PO_HEADERS_ALL and PO_HEADERS_ARCHIVE_ALL tables which doesnt seem to help for the requirement I have.

                                Looks like I have to go with the PL/SQL code with Audit tables.

                                Thank you for all your help !
                                Prathima
                                • 13. Re: How to get PO CHANGE HISTORY from back end tables?
                                  514497
                                  Hi All,

                                  I think I figured out that the Audit tables only create few views which has all the data we need to track. This helped me develop a simple query without much PL/SQL coding.

                                  Thanks to Anil for the wonderful idea of turning Audit on tables. It helped !

                                  Thank you to one and all who gave thier valuble ideas on this.
                                  Prathima