PO Receiving Transactions Pipeline

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