This query will extract the PO amendment details for the revised PO’s
SELECT po_number, po_date, approved_date, buyer_name, revision_number,
po_header_description, amendment_by, amendment_date,
CASE
when (((NVL(old_price,-1)NVL(new_price,-1)) and old_price is not null) and
((NVL(old_quantity,-1)NVL(current_qty,-1)) and old_quantity is not null)) THEN ‘Price & Quantity’
when ((NVL(old_price,-1)NVL(new_price,-1)) and old_price is not null) THEN ‘Price’
when ((NVL(old_quantity,-1)NVL(current_qty,-1)) and old_quantity is not null) THEN ‘Quantity’
END amendment_type,
old_value actual_po_value,
amendmeted_po_value, project_number,
project_name, task_number, task_name
FROM (SELECT poh.segment1 po_number, poh.creation_date po_date,
poh.approved_date approved_date, papf.full_name buyer_name,
poh.revision_num revision_number,
poh.comments po_header_description,
(SELECT user_name
FROM fnd_user
WHERE user_id = poh.last_updated_by) amendment_by,
poh.last_update_date amendment_date,
SUM (pol.quantity) current_qty,
old_po.old_quantity,
old_po.old_value,
SUM ((pol.quantity * pol.unit_price)) amendmeted_po_value,
old_po.old_price old_price,
SUM(pol.unit_price) new_price,
CASE
WHEN ((SELECT COUNT (project_id)
FROM pa_projects_all
WHERE project_id IN (
SELECT project_id
FROM po_distributions_all
WHERE po_header_id = poh.po_header_id))) =
1
THEN (SELECT segment1
FROM pa_projects_all
WHERE project_id =
(SELECT project_id
FROM po_distributions_all
WHERE po_header_id =
poh.po_header_id
AND ROWNUM = 1))
END project_number,
CASE
WHEN ((SELECT COUNT (project_id)
FROM pa_projects_all
WHERE project_id IN (
SELECT project_id
FROM po_distributions_all
WHERE po_header_id = poh.po_header_id))) =
1
THEN (SELECT NAME
FROM pa_projects_all
WHERE project_id =
(SELECT project_id
FROM po_distributions_all
WHERE po_header_id =
poh.po_header_id
AND ROWNUM = 1))
END project_name,
CASE
WHEN ((SELECT COUNT (task_id)
FROM pa_tasks
WHERE task_id IN (
SELECT task_id
FROM po_distributions_all
WHERE po_header_id = poh.po_header_id))) =
1
THEN (SELECT task_number
FROM pa_tasks
WHERE task_id =
(SELECT DISTINCT task_id
FROM po_distributions_all
WHERE po_header_id =
poh.po_header_id
AND ROWNUM = 1))
END task_number,
CASE
WHEN ((SELECT COUNT (task_id)
FROM pa_tasks
WHERE task_id IN (
SELECT task_id
FROM po_distributions_all
WHERE po_header_id =
poh.po_header_id))) =
1
THEN (SELECT task_number
FROM pa_tasks
WHERE task_id =
(SELECT DISTINCT task_id
FROM po_distributions_all
WHERE po_header_id =
poh.po_header_id
AND ROWNUM = 1))
END task_name
FROM po_headers_all poh, po_lines_all pol,
per_all_people_f papf,
(select sum(unit_price) old_price,sum(quantity) old_quantity,sum(unit_price*quantity) old_value,a1.po_header_id
from po_lines_archive_all a1,
(SELECT
p2.po_line_id, p2.revision_num,
CASE WHEN EXISTS (SELECT p1.po_line_id FROM po_lines_archive_all p1 WHERE
p1.po_line_id=p2.po_line_id
and p1.po_header_id=p2.po_header_id
and p1.latest_external_flag=’N’
and p1.po_header_id=p2.po_header_id)
THEN (select max(p3.revision_num)
from po_lines_archive_all p3 where p3.po_line_id=p2.po_line_id and p3.latest_external_flag=’N’)
ELSE p2.revision_num
END AS rev_num
FROM po_lines_archive_all p2
where p2.latest_external_flag=’Y’
–and p2.po_header_id=320787
) a2
where a1.po_line_id=a2.po_line_id
and a1.REVISION_NUM=a2.rev_num
group by a1.po_header_id) old_po
–,pa_projects_all pa,po_distributions_all pod
WHERE poh.po_header_id = pol.po_header_id
and old_po.po_header_id=poh.po_header_id
AND poh.authorization_status=’APPROVED’
AND poh.agent_id = papf.person_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.effective_start_date)
AND TRUNC (papf.effective_end_date)
AND poh.revision_num > 0
GROUP BY poh.segment1,
poh.creation_date,
poh.approved_date,
papf.full_name,
poh.revision_num,
poh.comments,
poh.last_update_date,
–pol.quantity,
–pol.unit_price,
poh.last_updated_by,
old_po.old_quantity,
old_po.old_value,
old_po.old_price,
poh.po_header_id)
ORDER BY po_number
Recent Posts
