PO Receiving Transactions Pipeline
Description
Run
PO Receiving Transactions Pipeline and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Operating Unit |
|
LOV | |
| Document Source |
|
LOV | |
| Document Number |
|
LOV | |
| PO Buyer |
|
LOV | |
| Req Preparer |
|
LOV | |
| Receipt Date From |
|
Date | |
| Receipt Date To |
|
Date | |
| Document Date From |
|
Date | |
| Document Date To |
|
Date | |
| Supplier |
|
LOV | |
| Supplier Site |
|
LOV | |
| Item |
|
LOV | |
| Item Type |
|
LOV | |
| Has Open Quantity |
|
LOV Oracle | |
| Overdue |
|
LOV Oracle | |
| Exclude Cancelled |
|
LOV Oracle |
