This content has been marked as final. Show 13 replies
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
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.
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
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
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?
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!
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.
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 !
what is issue with using archive tables ?
You can capture all information that you need..
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.
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.
Thank you for sharing the information. I will let you know if that works.
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 !
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.