Thanks a lot. I explored TRM already.
What I found is that whenever there is changes in any level either header or line PO revision got changed.
But when there is change in lines only then for that particular line revision got changed.
So for the 8th revision suppose there were 6 lines. And for 2 lines there were price changes. So those two lines have revision number 8. But for the rest 4 lines revision 8 was not there.
I want to get all the six lines when I choose revision 8 in my report parameter.
How can I achieve this..
My Query is as follow:
Select pha.segment1, pla.item_description, pla.quantity, pla.unit_price
from po_headers_all pha, po_lines_all pla
where pha.po_header_id =pla.po_header_id
and pha.po_header_id = :header_id
and pha.revision_num =nvl(:PO_revision_num,pha.revision_num)
Select phar.segment1, plar.item_description, plar.quantity, plar.unit_price
from po_headers_archive_all phar, po_lines_archive_all plar
where phar.po_header_id =plar.po_header_id
and phar.po_header_id = :header_id
and phar.revision_num =:PO_revision_num
and plar.revision_num =:PO_revision_num
and phar.revision_num = plar.revision_num
The issue is when I am selecting the current revision of PO , I am getting correct data as query is fetching data from po_headers_all and po_lines_all tables.
But when I am selecting previous versions of PO, I am getting correct data for header level. But for line level, I am getting only those lines which are amended for that PO.
Client requirement is to get complete line details for that PO whether lines are amended or not. Means if for PO revision 2 , three lines amended and there are total 5 lines---- so all 5 lines need to be displayed.
Thanks for your valuable thoughts.
Message was edited by: user11655073