PO Cancelled Purchase Orders

select
x.operating_unit,
x.po_number,
x.release_number,
x.order_type,
x.vendor,
x.vendor_site,
x.order_date,
x.created_by,
x.order_amount,
x.currency,
x.buyer,
x.cancelled_date,
x.cancelled_by,
x.reason
 -- line
&lp_line_cols
from
(
select
 mgoat.organization_name operating_unit,
 poh.segment1 po_number,
 null release_number,
 flvv.description order_type,
 pov.vendor_name vendor,
 pvs.vendor_site_code vendor_site,
 trunc(poh.creation_date) order_date,
 nvl(papf3.full_name,fu.user_name || ' (' || fu.description || ')') created_by,
 poh.currency_code currency,
 sum(nvl(pll.quantity,0) * nvl(pll.price_override,0)) over (partition by poh.po_header_id) order_amount,
 papf2.full_name buyer,
 trunc(pah.action_date) cancelled_date,
 papf.full_name cancelled_by,
 pah.note reason,
 --
 &lp_line_po_cols
 --
 decode(psp.manual_po_num_type,'NUMERIC', null,poh.segment1) ord_sort_1,
 decode(psp.manual_po_num_type,'NUMERIC', to_number(poh.segment1), null) ord_sort_2
from
 po_headers_all poh,
 po_action_history pah,
 po_vendors pov,
 ap_supplier_sites_all pvs,
 fnd_user fu,
 per_all_people_f papf,
 per_all_people_f papf2,
 per_all_people_f papf3,
 po_system_parameters_all psp,
 mo_glob_org_access_tmp mgoat,
 fnd_lookup_values_vl flvv,
 --
 financials_system_params_all fspa,
 po_lines_all pol,
 po_line_locations_all pll,
 mtl_categories_kfv mc,
 mtl_system_items_vl msi
where
     1=1
 and 2=2
 and poh.cancel_flag = 'Y'
 and poh.org_id = psp.org_id
 and poh.org_id = fspa.org_id
 and poh.org_id = mgoat.organization_id
 and pov.vendor_id = poh.vendor_id
 and pvs.vendor_site_id = poh.vendor_site_id
 and (pah.object_revision_num,pah.sequence_num) =
  (select
    max(pah2.object_revision_num),
    max(pah2.sequence_num)
   from
    po_action_history pah2
   where
        4=4
    and pah2.object_type_code in ('PO','PA')
    and pah2.action_code in ('CANCEL','MASSCANCEL','RELEASE_MASSCANCEL')
    and poh.po_header_id = pah2.object_id
  )
 and pah.object_id = poh.po_header_id
 and pah.object_type_code in ('PO','PA')
 and pah.action_code in ( 'CANCEL','MASSCANCEL','RELEASE_MASSCANCEL')
 and papf.person_id(+) = pah.employee_id
 and papf2.person_id(+) = poh.agent_id
 and fu.user_id = poh.created_by
 and papf3.person_id(+) = fu.employee_id
 and flvv.lookup_type = 'PO TYPE'
 and flvv.view_application_id = 201
 and flvv.lookup_code = poh.type_lookup_code
 --
 and pol.po_header_id(+) = poh.po_header_id
 and pll.po_line_id(+) = pol.po_line_id
 and pll.po_release_id(+) is null
 and ( pll.shipment_type in ('STANDARD','PLANNED') or poh.type_lookup_code='BLANKET')
 and mc.category_id(+) = pol.category_id
 and msi.inventory_item_id(+) = pol.item_id
 and nvl(msi.organization_id,fspa.inventory_organization_id) = fspa.inventory_organization_id
 --
 and papf.employee_number (+) is not null
 and trunc(sysdate) between papf.effective_start_date (+)and papf.effective_end_date(+)
 and decode(hr_general.get_xbg_profile,'Y', papf.business_group_id(+) , hr_general.get_business_group_id) = papf.business_group_id(+)
 and papf2.employee_number(+) is not null
 and trunc(sysdate) between papf2.effective_start_date (+) and papf2.effective_end_date (+)
 and decode(hr_general.get_xbg_profile,'Y', papf2.business_group_id(+) , hr_general.get_business_group_id) = papf2.business_group_id(+)
 and papf3.employee_number(+) is not null
 and trunc(sysdate) between papf3.effective_start_date (+) and papf3.effective_end_date (+)
 and decode(hr_general.get_xbg_profile,'Y', papf3.business_group_id(+) , hr_general.get_business_group_id) = papf3.business_group_id(+)
union
select
 mgoat.organization_name operating_unit,
 poh.segment1 po_number,
 por.release_num release_number,
 flvv.description order_type,
 pov.vendor_name vendor,
 pvs.vendor_site_code vendor_site,
 trunc(por.creation_date) order_date,
 nvl(papf3.full_name,fu.user_name || ' (' || fu.description || ')') created_by,
 poh.currency_code currency,
 sum(decode (pol.order_type_lookup_code, 'RATE', pll.amount, 'FIXED PRICE', pll.amount, nvl(pll.quantity, 0) * nvl(pll.price_override, 0))) over (partition by por.po_release_id) order_amount,
 papf2.full_name buyer,
 trunc(pah.action_date) cancelled_date,
 papf.full_name cancelled_by,
 pah.note reason,
 --
 &lp_line_rel_cols
 --
 decode(psp.manual_po_num_type,'NUMERIC', null,poh.segment1) ord_sort_1,
 decode(psp.manual_po_num_type,'NUMERIC', to_number(poh.segment1), null) ord_sort_2
from
 po_releases_all por,
 po_headers_all poh,
 ap_supplier_sites_all pvs,
 po_vendors pov,
 po_action_history pah,
 fnd_user fu,
 per_all_people_f papf,
 per_all_people_f papf2,
 per_all_people_f papf3,
 po_system_parameters_all psp,
 mo_glob_org_access_tmp mgoat,
 fnd_lookup_values_vl flvv,
 --
 financials_system_params_all fspa,
 po_line_locations_all pll,
 po_lines_all pol,
 mtl_categories_kfv mc,
 mtl_system_items_vl msi
where
     1=1
 and 3=3
 and por.cancel_flag = 'Y'
 and por.org_id = psp.org_id
 and poh.org_id = fspa.org_id
 and por.org_id = mgoat.organization_id
 and poh.po_header_id = por.po_header_id
 and pov.vendor_id = poh.vendor_id
 and pvs.vendor_site_id = poh.vendor_site_id
 and (pah.object_revision_num,pah.sequence_num) =
  (select
    max(pah2.object_revision_num),
    max(pah2.sequence_num)
   from
    po_action_history pah2
   where
        4=4
    and pah2.object_type_code in ('RELEASE')
    and pah2.action_code in ('CANCEL','MASSCANCEL','RELEASE_MASSCANCEL')
    and por.po_release_id = pah2.object_id
  )
 and pah.object_id = por.po_release_id
 and pah.object_type_code in ('RELEASE')
 and pah.action_code in ( 'CANCEL','MASSCANCEL','RELEASE_MASSCANCEL')
 and papf.person_id(+) = pah.employee_id
 and papf2.person_id(+) = por.agent_id
 and fu.user_id = por.created_by
 and papf3.person_id(+) = fu.employee_id
 and pll.shipment_type in ('BLANKET','SCHEDULED')
 and flvv.lookup_type = 'DOCUMENT'
 and flvv.view_application_id = 201
 and flvv.lookup_code = 'RELEASE'
 --
 and pll.po_release_id = por.po_release_id
 and pol.po_line_id = pll.po_line_id
 and mc.category_id(+) = pol.category_id
 and msi.inventory_item_id(+) = pol.item_id
 and nvl(msi.organization_id,fspa.inventory_organization_id) = fspa.inventory_organization_id
 --
 and trunc(sysdate) between papf.effective_start_date (+)and papf.effective_end_date(+)
 and decode(hr_general.get_xbg_profile,'Y', papf.business_group_id(+) , hr_general.get_business_group_id) = papf.business_group_id(+)
 and trunc(sysdate) between papf2.effective_start_date (+) and papf2.effective_end_date (+)
 and decode(hr_general.get_xbg_profile,'Y', papf2.business_group_id(+) , hr_general.get_business_group_id) = papf2.business_group_id(+)
 and papf3.employee_number(+) is not null
 and trunc(sysdate) between papf3.effective_start_date (+) and papf3.effective_end_date (+)
 and decode(hr_general.get_xbg_profile,'Y', papf3.business_group_id(+) , hr_general.get_business_group_id) = papf3.business_group_id(+)
) x
order by
x.operating_unit,
x.ord_sort_1,
x.ord_sort_2,
x.release_number
&lp_line_sort