SELECT DISTINCT
pha.vendor_id,
hou.name “Organization”,
pha.segment1 “po number”,
pla.line_num “po line num”,
(TRUNC (pha.creation_date – TO_DATE (’01-01-1900′, ‘DD/MM/YYYY’)) + 2) as “PO creation Date”,
aps.vendor_name “Supplier Name”,
apss.vendor_site_code “Supplier site”,
fu.user_name “Change request created by”,
(TRUNC (pcr.creation_date – TO_DATE (’01-01-1900′, ‘DD/MM/YYYY’)) + 2) as “change request submission date”,
(TRUNC (pcr.response_date – TO_DATE (’01-01-1900′, ‘DD/MM/YYYY’)) + 2) as “change response date”,
fu1.user_name “change responded by”,
DECODE (pcr.new_promised_date, NULL, ‘NO’, ‘YES’) AS “promised date change”,
DECODE (pcr.new_quantity, NULL, ‘NO’, ‘YES’) AS “quantity change”,
DECODE (pcr.new_price, NULL, ‘NO’, ‘YES’) AS “price change”,
DECODE (pcr.new_supplier_part_number, NULL, ‘NO’, ‘YES’) AS “supplier part number change”,
DECODE(SUBSTR((SELECT LISTAGG (ACCEPTED_FLAG, ‘,’)
WITHIN GROUP (ORDER BY pav.PO_RELEASE_ID)
FROM PO_ACCEPTANCES pav
WHERE 1 = 1 AND pav.po_header_id = pha.po_header_id
OR (pav.PO_RELEASE_ID = PRA.PO_RELEASE_ID
AND pav.REVISION_NUM = PRA.REVISION_NUM)),1,1),’Y’,’YES’,’N’,’NO’,”) ACCEPTED,
TO_DATE (SUBSTR ((SELECT LISTAGG (ACTION_DATE, ‘,’)
WITHIN GROUP (ORDER BY pav.PO_RELEASE_ID)
FROM PO_ACCEPTANCES pav
WHERE 1 = 1 AND pav.po_header_id = pha.po_header_id
OR (pav.PO_RELEASE_ID = PRA.PO_RELEASE_ID
AND pav.REVISION_NUM = PRA.REVISION_NUM)),1,9),’DD-MON-RRRR’) ACCEPTED_DATE
FROM po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_releases_all pra,
apps.hr_operating_units hou,
apps.ap_suppliers aps,
apps.ap_supplier_sites_all apss,
apps.fnd_user fu,
apps.po_change_requests pcr,
apps.fnd_user fu1,
po_supplier_users_v psuv
WHERE 1 = 1
AND pha.po_header_id = pla.po_header_id
AND pla.po_header_id = plla.po_header_id
AND ( (pla.line_num = pcr.document_line_number)
OR pcr.document_line_number IS NULL)
AND pla.po_line_id = plla.po_line_id
AND pha.org_id IN (445, 927)
AND plla.po_header_id = pra.po_header_id(+)
AND plla.po_release_id = pra.po_release_id(+)
AND pha.org_id = hou.organization_id
AND pha.vendor_id = aps.vendor_id
AND pha.vendor_site_id = apss.vendor_site_id
AND pcr.document_header_id(+) = pha.po_header_id
AND pcr.created_by = fu.user_id(+)
AND pcr.responded_by = fu1.user_id(+)
AND psuv.po_vendor_id = pha.vendor_id
AND pha.creation_date >
(select creation_date
from fnd_user fu2
where 1=1
and fu2.user_id=psuv.fnd_user_id
and fu2.end_date is null)
ORDER BY pha.segment1, pla.line_num
Recent Posts