WSH Shipping/Delivery Transactions

select /*+ push_pred(wfc) */
 haou2.name selling_operating_unit,
 haou1.name shipping_operating_unit,
 ood.organization_code,
 -- delivery level info
 wnd.delivery_id delivery_note_id,
 wnd.name delivery_note_number,
 trunc(nvl(mmt.transaction_date,wnd.initial_pickup_date)) delivery_note_date,
 xxen_util.meaning(wnd.status_code,'DELIVERY_STATUS',665) delivery_status,
 trunc(mmt.transaction_date) actual_shipment_date,
 trunc(wnd.confirm_date) confirmed_date,
 wnd.confirmed_by,
 -- delivery detail info
 wdd.delivery_detail_id,
 xxen_util.meaning(wdd.released_status,'PICK_STATUS',665) release_status,
 trunc(wpb.creation_date) released_date,
 -- freight cost details
 wfc.freight_cost_currency,
 wfc.freight_cost_name,
 wfc.freight_cost_type,
 wfc.freight_cost,
 -- customer details
 hca.account_number customer_number,
 nvl(hca.account_name,hp.party_name) customer_name,
 hcsua.location customer_ship_to_site,
 hz_format_pub.format_address(hl.location_id,null,null,',') customer_ship_to_address,
 wdd.cust_po_number customer_po_number,
 wnd.delivery_id customer_delivery_code,
 wnd.name customer_delivery_name,
 -- delivery item details
 msiv.concatenated_segments item,
 wdd.item_description item_description,
 xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
 wdd.requested_quantity,
 decode(wdd.released_status,'Y',nvl(wdd.picked_quantity,wdd.requested_quantity),NULL) picked_qty,
 wdd.shipped_quantity,
 wdd.delivered_quantity,
 wdd.cancelled_quantity,
 wdd.requested_quantity_uom uom,
 -- packing metrics
 wdd.net_weight,
 wdd.gross_weight,
 wdd.weight_uom_code weight_uom,
 wdd.volume,
 wdd.volume_uom_code volume_uom,
 wdd.unit_price,
 wdd.currency_code,
 -- shipment details
 wnd.waybill,
 wsh_util_core.derive_shipment_priority(wnd.delivery_id) shipment_priority,
 wsh_util_core.ship_method_to_freight(wnd.ship_method_code,wdd.organization_id) delivery_freight_code,
 xxen_util.meaning(wnd.ship_method_code,'SHIP_METHOD',3) delivery_shipping_method,
 xxen_util.meaning(wnd.freight_terms_code,'FREIGHT_TERMS',660) delivery_freight_terms,
 nvl(xxen_util.meaning(wnd.fob_code,'FOB',222),wnd.fob_code) delivery_fob,
 wsh_util_core.ship_method_to_freight(nvl(wdd.ship_method_code,wnd.ship_method_code),wdd.organization_id) delivery_line_freight_code,
 xxen_util.meaning(nvl(wdd.ship_method_code,wnd.ship_method_code),'SHIP_METHOD',3) delivery_line_shipping_method,
 xxen_util.meaning(nvl(wdd.freight_terms_code,wnd.freight_terms_code),'FREIGHT_TERMS',660) delivery_line_freight_terms,
 nvl(xxen_util.meaning(nvl(wdd.fob_code,wnd.fob_code),'FOB',222),wnd.fob_code) delivery_line_fob,
 wdd.tracking_number,
 wdd.customer_dock_code,
 -- container/lot details
 wdd.container_flag,
 xxen_util.meaning(wdd.container_type_code,'CONTAINER_TYPE',401) container_type,
 wdd.container_name,
 coalesce((select listagg(mtln.lot_number,',') within group (order by mtln.lot_number) -- shipped
           from   mtl_material_transactions mmt,
                  mtl_transaction_lot_numbers mtln
           where  mmt.picking_line_id = wdd.delivery_detail_id
           and    mmt.transaction_source_type_id in (2,8)
           and    mmt.transaction_quantity  < 0
           and    mtln.transaction_id = mmt.transaction_id
          ),
          (select listagg(mtln.lot_number,',') within group (order by mtln.lot_number) -- picked
           from   mtl_material_transactions mmt,
                  mtl_transaction_lot_numbers mtln
           where  mmt.move_order_line_id = wdd.move_order_line_id
           and    mmt.transaction_source_type_id in (2,8)
           and    mmt.transaction_quantity < 0 --> to prevent duplicates, just take the -qty trx leg sub to staging.
           and    mtln.transaction_id = mmt.transaction_id
          ),
          wdd.lot_number
         ) lot_numbers,
 -- exceptions
 (select dbms_lob.substr(rtrim(xmlagg(xmlelement(name excptn,wev.description,',').extract('//text()') order by wev.description).GetClobVal(),','),4000,1)
  from   (select distinct
                 wev.delivery_detail_id,
                 wev.description
          from   wsh_exceptions_v wev
          where  wev.status = 'OPEN'
          and    wev.severity = 'ERROR'
         ) wev
  where  wev.delivery_detail_id = wdd.delivery_detail_id
 ) error_exceptions,
 -- source references/invoice details
 wpb.name release_batch,
 coalesce((select mmt.pick_slip_number -- released to warehouse
           from   mtl_material_transactions mmt
           where  mmt.move_order_line_id = wdd.move_order_line_id
           and    mmt.transaction_id = decode(nvl(wdd.transaction_id ,-99),-99,mmt.transaction_id,wdd.transaction_id)
           and    mmt.transaction_source_type_id in (2,8)
           and    nvl(mmt.transaction_quantity,0) < 0 --> to prevent duplicates, just take the -qty trx leg sub to staging.
           and    mmt.pick_slip_number is not null
           and    wdd.source_code = 'OE'
           and    wdd.released_status != 'S' -- released to warehouse
           and    rownum=1
          ),
          (select mtrl.pick_slip_number
           from   mtl_txn_request_lines mtrl
           where  mtrl.line_id = wdd.move_order_line_id
           and    mtrl.pick_slip_number is not null
           and    wdd.source_code = 'OE'
           and    wdd.released_status != 'S' -- released to warehouse
           and    wdd.transaction_id is null
           and    rownum=1
          ),
          (select mmtt.pick_slip_number
           from   mtl_material_transactions_temp mmtt
           where  mmtt.move_order_line_id = wdd.move_order_line_id
           and    nvl(mmtt.parent_line_id,0) = 0
           and    mmtt.pick_slip_number is not null
           and    wdd.source_code = 'OE'
           and    wdd.released_status = 'S' -- released to warehouse
           and    rownum=1
          ),
          (select mmttp.pick_slip_number
           from   mtl_material_transactions_temp mmtt,
                  mtl_material_transactions_temp mmttp
           where
                  mmttp.transaction_temp_id = mmtt.parent_line_id
           and    mmtt.parent_line_id != mmtt.transaction_temp_id
           and    mmtt.move_order_line_id = wdd.move_order_line_id
           and    nvl(mmtt.parent_line_id,0) = 0
           and    mmttp.pick_slip_number is not null
           and    wdd.source_code = 'OE'
           and    wdd.released_status = 'S' -- released to warehouse
           and    rownum=1
          ),
          (select mmtt.pick_slip_number
           from   mtl_material_transactions_temp mmtt
           where
                  mmtt.move_order_line_id = wdd.move_order_line_id
           and    mmtt.parent_line_id = mmtt.transaction_temp_id
           and    mmtt.pick_slip_number is not null
           and    wdd.source_code = 'OE'
           and    wdd.released_status = 'S' -- released to warehouse
           and    rownum=1
          )
         ) pick_slip_number,
 (select mtrh.request_number
  from   mtl_txn_request_lines mtrl,
         mtl_txn_request_headers mtrh
  where  mtrl.header_id = mtrh.header_id
  and    mtrl.line_id = wdd.move_order_line_id
 ) move_order_number,
 wdi.sequence_number pack_slip_number,
 xxen_util.meaning(wdd.source_code,'SOURCE_SYSTEM',665) source,
 wdd.source_header_type_name source_document_type,
 wdd.source_header_number source_document_number,
 wdd.source_line_number source_document_line,
 ooha.ordered_date source_document_date,
 rcta.trx_number invoice_number,
 rctla.line_number invoice_line_number,
 rcta.trx_date invoice_date,
 -- date info
 trunc(wdd.date_requested) requested_date,
 trunc(wdd.date_scheduled) scheduled_date,
 trunc(wdd.earliest_pickup_date) earliest_pickup_date,
 trunc(wdd.latest_pickup_date) latest_pickup_date,
 trunc(wdd.earliest_dropoff_date) earliest_dropoff_date,
 trunc(wdd.latest_dropoff_date) latest_dropoff_date,
 trunc(wnd.initial_pickup_date) delivery_initial_pickup_date,
 trunc(wnd.ultimate_dropoff_date) delivery_ultimate_dropoff_date,
 -- ship from/to locations
 wsh_util_core.get_location_description(nvl(wnd.initial_pickup_location_id,wdd.ship_from_location_id), case :p_loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) ship_from_location,
 wsh_util_core.get_location_description(nvl(wnd.ultimate_dropoff_location_id,wdd.ship_to_location_id), case :p_loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) ship_to_location,
 wsh_util_core.get_location_description(wdd.deliver_to_location_id, case :p_loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) deliver_to_location,
 wsh_util_core.get_location_description(nvl(wnd.intmed_ship_to_location_id,wdd.intmed_ship_to_location_id), case :p_loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) int_med_ship_to_location,
 wsh_util_core.get_location_description(wnd.fob_location_id, case :p_loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) fob_location,
 -- packing/shipping instructions
 wdd.packing_instructions,
 wdd.shipping_instructions
from
 wsh_delivery_details wdd,
 wsh_delivery_assignments wda,
 wsh_new_deliveries wnd,
 wsh_picking_batches wpb,
 wsh_document_instances wdi,
 wsh_carriers wc,
 (select distinct
   wfc.delivery_detail_id,
   wfc.currency_code freight_cost_currency,
   listagg(wfct.name,',') within group (order by wfct.freight_cost_type_code,wfct.name) over (partition by wfc.delivery_detail_id,wfc.currency_code) freight_cost_name,
   listagg(xxen_util.meaning(wfct.freight_cost_type_code,'FREIGHT_COST_TYPE',665),',') within group (order by wfct.freight_cost_type_code) over (partition by wfc.delivery_detail_id,wfc.currency_code) freight_cost_type,
   sum(nvl(wfc.total_amount,wfc.unit_amount * nvl(wfc.quantity,1))) over (partition by wfc.delivery_detail_id,wfc.currency_code) freight_cost
  from
   wsh_freight_costs wfc,
   wsh_freight_cost_types wfct
  where
   wfc.freight_cost_type_id = wfct.freight_cost_type_id and
   nvl(wfc.charge_source_code, 'MANUAL') in ('PRICING_ENGINE' ,'MANUAL')
 ) wfc,
 mtl_system_items_vl msiv,
 mtl_material_transactions mmt,
 oe_order_headers_all ooha,
 oe_order_lines_all oola,
 ra_customer_trx_lines_all rctla,
 ra_customer_trx_all rcta,
 org_organization_definitions ood,
 hr_all_organization_units haou1,
 hz_cust_accounts hca,
 hz_parties hp,
 hz_cust_site_uses_all hcsua,
 hz_cust_acct_sites_all hcasa,
 hz_party_sites hps,
 hz_locations hl,
 hr_all_organization_units haou2
where
    1=1
and nvl(wdd.line_direction,'O') in ('O','IO')
and wdd.delivery_detail_id = wda.delivery_detail_id (+)
and wda.delivery_id = wnd.delivery_id (+)
and nvl(wnd.shipment_direction, 'O') in ('O','IO')
and wnd.delivery_type (+) = 'STANDARD'
and wdd.batch_id = wpb.batch_id(+)
and wnd.delivery_id = wdi.entity_id(+)
and wdi.entity_name(+) = 'WSH_NEW_DELIVERIES'
and wdi.document_type(+) = 'PACK_TYPE'
and wdd.carrier_id = wc.carrier_id(+)
and wdd.organization_id = msiv.organization_id(+)
and wdd.inventory_item_id = msiv.inventory_item_id(+)
and wdd.delivery_detail_id = mmt.picking_line_id(+)
and mmt.transaction_source_type_id (+) in (2,8)
and mmt.transaction_quantity (+) < 0
and wdd.delivery_detail_id = wfc.delivery_detail_id (+)
--
and decode(wdd.source_code,'OE',wdd.source_header_id) = ooha.header_id(+)
and decode(wdd.source_code,'OE',wdd.source_line_id)  = oola.line_id(+)
and rctla.interface_line_context(+) = 'ORDER ENTRY'
and decode(wdd.source_code,'OE',wdd.source_header_number) = rctla.interface_line_attribute1(+)
and decode(wdd.source_code,'OE',wdd.source_header_type_name) = rctla.interface_line_attribute2(+)
and decode(wdd.source_code,'OE',to_char(wdd.source_line_id)) = rctla.interface_line_attribute6(+)
and nvl(rctla.interface_line_attribute3,nvl(wnd.name,'??')) = nvl(wnd.name,'??')
and nvl(rctla.interface_line_attribute11,'0') = '0'
and rctla.customer_trx_id = rcta.customer_trx_id(+)
--
and wdd.organization_id = ood.organization_id(+)
and ood.operating_unit = haou1.organization_id(+)
and wdd.customer_id = hca.cust_account_id(+)
and hca.party_id = hp.party_id(+)
and wdd.ship_to_site_use_id = hcsua.site_use_id(+)
and hcsua.cust_acct_site_id = hcasa.cust_acct_site_id(+)
and hcasa.party_site_id = hps.party_site_id(+)
and hps.location_id = hl.location_id(+)
and hcasa.org_id = haou2.organization_id(+)
--
and (wdd.organization_id is null or exists (select null from org_access_view oav where oav.organization_id = wdd.organization_id and oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id))
and (   haou1.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
     or haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
     or (haou1.organization_id is null and haou2.organization_id is null)
    )
order by
 selling_operating_unit,
 shipping_operating_unit,
 organization_code,
 delivery_note_date,
 delivery_note_id,
 delivery_detail_id