with
po as
(
select
hou.name operating_unit
, 'PO' document_source
, (select pdtav.type_name
from po_document_types_all_vl pdtav
where pdtav.document_type_code in ('PO','PA')
and pdtav.document_subtype = pha.type_lookup_code
and pdtav.org_id = pha.org_id
) document_type
, pha.segment1 document_number
, pha.creation_date document_date
, pha.revision_num document_revision
, po_headers_sv3.get_po_status(pha.po_header_id) document_status
, xxen_util.user_name(pha.created_by) document_created_by
, pha.comments document_description
, pra.release_num release_number
, pra.release_date release_date
, xxen_util.user_name(pra.created_by) release_created_by
, decode(nvl(pha.cancel_flag,'N') || nvl(pla.cancel_flag,'N') || nvl(plla.cancel_flag,'N') || nvl(pra.cancel_flag,'N')
,'NNNN','No','Yes') document_cancelled
, nvl(pra.approved_date,pha.approved_date) approved_date
, asu.vendor_name supplier_name
, asu.segment1 supplier_number
, assa.vendor_site_code supplier_site
, null source_organization
, null sales_order_number
, null req_preparer
, ppx_b.full_name po_buyer
, (select pltv.line_type
from po_line_types_v pltv
where pltv.line_type_id = pla.line_type_id
) line_type
, pla.line_num line_num
, plla.shipment_num ship_line_num
, mck.concatenated_segments item_category
, msiv.item item
, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type
, coalesce(pla.item_description,msiv.item_desc) item_description
, ( select cic.item_cost
from cst_item_costs cic
where cic.organization_id = msiv.organization_id
and cic.inventory_item_id = msiv.item_id
and cic.cost_type_id = 1
) item_standard_cost
, pla.vendor_product_num supplier_item
, nvl(plla.price_override,pla.unit_price) unit_price
, pha.currency_code currency
, nvl(muomt.unit_of_measure_tl
,pla.unit_meas_lookup_code) uom
, plla.quantity quantity_ordered
, plla.quantity
-nvl(plla.quantity_cancelled,0)
-nvl(plla.quantity_received,0) quantity_due
, plla.quantity_cancelled
, plla.quantity_received
, plla.quantity_billed
, plla.quantity_accepted
, plla.quantity_rejected
, (plla.quantity - nvl(plla.quantity_cancelled,0))
* nvl(plla.price_override,pla.unit_price) line_amount
, coalesce(plla.promised_date,plla.need_by_date,plla.last_accept_date)
request_date
, plla.need_by_date need_by_date
, plla.promised_date promised_date
, plla.last_accept_date last_acceptable_date
, (select distinct min(pllaa.promised_date) keep (dense_rank first order by pllaa.revision_num)
from po_line_locations_archive_all pllaa
where pllaa.line_location_id = plla.line_location_id
and pllaa.promised_date is not null
) original_promised_date
, case pha.type_lookup_code
when 'STANDARD'
then case
when plla.inspection_required_flag='N' and plla.receipt_required_flag='N'
then '2-Way'
when plla.inspection_required_flag='N' and plla.receipt_required_flag='Y'
then '3-Way'
when plla.inspection_required_flag='Y' and plla.receipt_required_flag='Y'
then '4-Way'
end
end match_approval_level
, xxen_util.meaning(plla.match_option,'POS_INVOICE_MATCH_OPTION',0) match_option
, rt.transaction_date receipt_date
, trunc(rt.transaction_date-coalesce(plla.promised_date,plla.need_by_date,plla.last_accept_date))
delivery_delay
, rsl.quantity_shipped shipped_quantity
, rt.quantity receipt_quantity
, rt.primary_quantity receipt_primary_quantity
, rt.primary_unit_of_measure receipt_primary_uom
, rsh.receipt_num receipt_num
, rsl.line_num receipt_line_num
, xxen_util.meaning(rsl.shipment_line_status_code,'SHIPMENT LINE STATUS',201)
shipment_line_status
, rsl.vendor_item_num shipment_supplier_item
, rsl.item_description shipment_item_description
, rsh.shipped_date
, rsh.shipment_num
, rsh.packing_slip
, rsh.waybill_airbill_num
, mp_sto.organization_code ship_to_organization
, hlat_sto.location_code ship_to_location
, mp_dto.organization_code delivered_to_organization
, hlat_dto.location_code delivered_to_location
, ppx_dto.full_name receiver
, ( select distinct listagg(pda.segment1,', ') within group (order by pda.segment1) over (partition by pda.line_location_id)
from
( select distinct
pda.line_location_id
, ppa.segment1
from
po_distributions_all pda
, pa_projects_all ppa
where
pda.project_id = ppa.project_id
) pda
where pda.line_location_id = plla.line_location_id
) project
, ( select distinct listagg(pda.task_number,', ') within group (order by pda.task_number) over (partition by pda.line_location_id)
from
( select distinct
pda.line_location_id
, pt.task_number
from
po_distributions_all pda
, pa_tasks pt
where
pda.task_id = pt.task_id
) pda
where pda.line_location_id = plla.line_location_id
) task
, assa.address_line1 supplier_address1
, assa.address_line2 supplier_address2
, assa.address_line3 supplier_address3
, assa.zip supplier_zip
, assa.city supplier_city
, nvl(ftv.territory_short_name,assa.country) supplier_country
, nvl2(asco.prefix,asco.prefix || ' ',null)
|| nvl2(asco.first_name,asco.first_name || ' ',null)
|| nvl2(asco.middle_name,asco.middle_name || ' ',null)
|| asco.last_name supplier_contact
, asco.area_code || asco.phone supplier_phone
, asco.email_address supplier_email
from
po_headers_all pha
, po_lines_all pla
, po_line_locations_all plla
, po_releases_all pra
, hr_operating_units hou
, ap_suppliers asu
, ap_supplier_sites_all assa
, ap_supplier_contacts asco
, fnd_territories_vl ftv
, per_people_x ppx_b
, mtl_categories_kfv mck
, ( select
fspa.org_id org_id
, msiv.organization_id organization_id
, msiv.inventory_item_id item_id
, msiv.concatenated_segments item
, msiv.item_type item_type
, msiv.description item_desc
from
mtl_system_items_vl msiv
, financials_system_params_all fspa
where
fspa.inventory_organization_id = msiv.organization_id
) msiv
, mtl_units_of_measure_tl muomt
, hr_locations_all_tl hlat_sto
, mtl_parameters mp_sto
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, rcv_transactions rt
, mtl_parameters mp_dto
, hr_locations_all_tl hlat_dto
, per_people_x ppx_dto
where
pha.po_header_id = pla.po_header_id
and pla.po_header_id = plla.po_header_id
and pla.po_line_id = plla.po_line_id
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 = asu.vendor_id
and pha.vendor_site_id = assa.vendor_site_id
and assa.country = ftv.territory_code (+)
and pha.vendor_contact_id = asco.vendor_contact_id (+)
and pha.agent_id = ppx_b.person_id (+)
and pla.category_id = mck.category_id (+)
and pla.org_id = msiv.org_id (+)
and pla.item_id = msiv.item_id(+)
and pla.unit_meas_lookup_code = muomt.unit_of_measure (+)
and userenv('lang') = muomt.language (+)
and plla.ship_to_organization_id = mp_sto.organization_id
and plla.ship_to_location_id = hlat_sto.location_id (+)
and userenv('lang') = hlat_sto.language (+)
and plla.line_location_id = rt.po_line_location_id (+)
and rt.transaction_type (+) = 'RECEIVE'
and rt.shipment_line_id = rsl.shipment_line_id (+)
and rt.shipment_header_id = rsh.shipment_header_id (+)
and rt.organization_id = mp_dto.organization_id (+)
and rt.location_id = hlat_dto.location_id (+)
and userenv('lang') = hlat_dto.language (+)
and rt.employee_id = ppx_dto.person_id(+)
&xrrpv_clause
)
,ir as
(
select
hou.name operating_unit
, 'INT REQ' document_source
, (select pdtav.type_name
from po_document_types_all_vl pdtav
where pdtav.document_type_code in ('REQUISITION')
and pdtav.document_subtype = prha.type_lookup_code
and pdtav.org_id = prha.org_id
) document_type
, prha.segment1 document_number
, prha.creation_date document_date
, null document_revision
, ( select distinct plc.displayed_field
from po_lookup_codes plc
where plc.lookup_code = prha.authorization_status
and plc.lookup_type = 'AUTHORIZATION STATUS'
) document_status
, xxen_util.user_name(prha.created_by) document_created_by
, prha.description document_description
, null release_number
, null release_date
, null release_created_by
, decode(nvl(prha.cancel_flag,'N') || nvl(prla.cancel_flag,'N')
,'NN','No','Yes') document_cancelled
, prha.approved_date approved_date
, null supplier_name
, null supplier_number
, null supplier_site
, mp_src.organization_code source_organization
, ooha.order_number sales_order_number
, ppx_p.full_name req_preparer
, null po_buyer
, (select pltv.line_type
from po_line_types_v pltv
where pltv.line_type_id = prla.line_type_id
) line_type
, prla.line_num line_num
, null ship_line_num
, mck.concatenated_segments item_category
, msiv.item item
, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type
, coalesce(prla.item_description,msiv.item_desc) item_description
, ( select cic.item_cost
from cst_item_costs cic
where cic.organization_id = msiv.organization_id
and cic.inventory_item_id = msiv.item_id
and cic.cost_type_id = 1
) item_standard_cost
, null supplier_item
, prla.unit_price unit_price
, coalesce(prla.currency_code,rt.currency_code,ooha.transactional_curr_code)
currency
, nvl(muomt.unit_of_measure_tl
,prla.unit_meas_lookup_code) uom
, prla.quantity quantity_ordered
, prla.quantity
-nvl(prla.quantity_cancelled,0)
-nvl(prla.quantity_delivered,0) quantity_due
, prla.quantity_cancelled
, prla.quantity_delivered quantity_received
, to_number(null) quantity_billed
, to_number(null) quantity_accepted
, to_number(null) quantity_rejected
, (prla.quantity - nvl(prla.quantity_cancelled,0))
* prla.unit_price line_amount
, prla.need_by_date request_date
, prla.need_by_date need_by_date
, to_date(null) promised_date
, to_date(null) last_acceptable_date
, to_date(null) original_promised_date
, null match_approval_level
, null match_option
, rt.transaction_date receipt_date
, trunc(rt.transaction_date-prla.need_by_date) delivery_delay
, rsl.quantity_shipped shipped_quantity
, rt.quantity receipt_quantity
, rt.primary_quantity receipt_primary_quantity
, rt.primary_unit_of_measure receipt_primary_uom
, rsh.receipt_num receipt_num
, rsl.line_num receipt_line_num
, xxen_util.meaning(rsl.shipment_line_status_code,'SHIPMENT LINE STATUS',201)
shipment_line_status
, rsl.vendor_item_num shipment_supplier_item
, rsl.item_description shipment_item_description
, rsh.shipped_date
, rsh.shipment_num
, rsh.packing_slip
, rsh.waybill_airbill_num
, mp_sto.organization_code ship_to_organization
, hlat_sto.location_code ship_to_location
, mp_dto.organization_code delivered_to_organization
, hlat_dto.location_code delivered_to_location
, ppx_dto.full_name receiver
, null project
, null task
, null supplier_address1
, null supplier_address2
, null supplier_address3
, null supplier_zip
, null supplier_city
, null supplier_country
, null supplier_contact
, null supplier_phone
, null supplier_email
from
po_requisition_headers_all prha
, po_requisition_lines_all prla
, hr_operating_units hou
, oe_order_headers_all ooha
, per_people_x ppx_p
, mtl_categories_kfv mck
, ( select
fspa.org_id org_id
, msiv.organization_id organization_id
, msiv.inventory_item_id item_id
, msiv.concatenated_segments item
, msiv.item_type item_type
, msiv.description item_desc
from
mtl_system_items_vl msiv
, financials_system_params_all fspa
where
fspa.inventory_organization_id = msiv.organization_id
) msiv
, mtl_units_of_measure_tl muomt
, hr_locations_all_tl hlat_sto
, mtl_parameters mp_src
, mtl_parameters mp_sto
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, rcv_transactions rt
, mtl_parameters mp_dto
, hr_locations_all_tl hlat_dto
, per_people_x ppx_dto
where
prha.requisition_header_id = prla.requisition_header_id
and prha.type_lookup_code = 'INTERNAL'
and prha.org_id = hou.organization_id
and prha.preparer_id = ppx_p.person_id (+)
and prla.category_id = mck.category_id (+)
and prla.org_id = msiv.org_id (+)
and prla.item_id = msiv.item_id(+)
and prla.unit_meas_lookup_code = muomt.unit_of_measure (+)
and userenv('lang') = muomt.language (+)
and prla.source_organization_id = mp_src.organization_id (+)
and prla.destination_organization_id = mp_sto.organization_id
and prla.deliver_to_location_id = hlat_sto.location_id (+)
and userenv('lang') = hlat_sto.language (+)
and prla.requisition_header_id = ooha.source_document_id (+)
and ooha.source_document_type_id (+) = 10
and prla.requisition_line_id = rt.requisition_line_id (+)
and rt.transaction_type (+) = 'RECEIVE'
and rt.shipment_line_id = rsl.shipment_line_id (+)
and rt.shipment_header_id = rsh.shipment_header_id (+)
and rt.organization_id = mp_dto.organization_id (+)
and rt.location_id = hlat_dto.location_id (+)
and userenv('lang') = hlat_dto.language (+)
and rt.employee_id = ppx_dto.person_id(+)
&xrrpv_clause
)
select x.*
from
(
select y.*
from po y
where 1=1
and 2=2
union all
select y.*
from ir y
where 1=1
and 2=2
) x
order by
x.operating_unit
,x.document_type
,x.document_number
,x.line_num
,x.ship_line_num |