select /*+ push_pred(wfc) push_pred(shipped_lots) push_pred(picked_lots) */
haou2.name selling_operating_unit,
haou1.name shipping_operating_unit,
ood.organization_code,
-- delivery level info
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
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,
hl.postal_code,
hl.city,
hl.country,
wdd.cust_po_number customer_po_number,
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,
&category_columns
oola.ordered_item,
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,
-- hold status (aggregated once upstream, not per-row scalar subqueries)
xxen_util.yes(nvl2(line_holds.line_id,'Y',null)) line_on_hold,
xxen_util.yes(nvl2(header_holds.header_id,'Y',null)) header_on_hold,
-- salesperson
rs_order.name salesperson_order,
rs_cust.name salesperson_customer,
-- 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,
(select listagg(x.name,', ') within group (order by x.name) from (select distinct wt.name from wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_trips wt where wdl.delivery_id=wnd.delivery_id and wdl.pick_up_stop_id=wts.stop_id and wts.trip_id=wt.trip_id) x) trip_number,
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(wdd.lot_number,shipped_lots.lot_numbers,picked_lots.lot_numbers) 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
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 and --> to prevent duplicates, just take the -qty trx leg sub to staging.
mmt.pick_slip_number is not null and
wdd.source_code='OE' and
wdd.released_status<>'S' and -- not yet released to warehouse
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' and -- not yet released to warehouse
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' and -- released to warehouse
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' and -- released to warehouse
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' and -- released to warehouse
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(oola.promise_date) promise_date,
trunc(mmt.transaction_date)-trunc(oola.promise_date) shipment_delay,
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 :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 :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 :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 :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 :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,
-- ids (non user-facing, kept at the end)
wnd.delivery_id delivery_note_id,
wdd.delivery_detail_id
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
x.delivery_detail_id,
x.freight_cost_currency,
x.freight_cost,
listagg(x.freight_cost_name,',') within group (order by x.freight_cost_type_code,x.freight_cost_name) over (partition by x.delivery_detail_id,x.freight_cost_currency) freight_cost_name,
listagg(x.freight_cost_type,',') within group (order by x.freight_cost_type_code,x.freight_cost_name) over (partition by x.delivery_detail_id,x.freight_cost_currency) freight_cost_type
from
(
select
wfc.delivery_detail_id,
wfc.currency_code freight_cost_currency,
wfct.name freight_cost_name,
wfct.freight_cost_type_code,
xxen_util.meaning(wfct.freight_cost_type_code,'FREIGHT_COST_TYPE',665) 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,
sum(lengthb(wfct.name)+1) over (partition by wfc.delivery_detail_id,wfc.currency_code order by wfct.freight_cost_type_code,wfct.name rows between unbounded preceding and current row) lengthb1,
sum(lengthb(xxen_util.meaning(wfct.freight_cost_type_code,'FREIGHT_COST_TYPE',665))+1) over (partition by wfc.delivery_detail_id,wfc.currency_code order by wfct.freight_cost_type_code,wfct.name rows between unbounded preceding and current row) lengthb2
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')
) x
where
x.lengthb1<=4000 and x.lengthb2<=4000
) wfc,
mtl_system_items_vl msiv,
mtl_material_transactions mmt,
(
select distinct
x.picking_line_id,
listagg(x.lot_number,',') within group (order by x.lot_number) over (partition by x.picking_line_id) lot_numbers
from
(
select
mmt.picking_line_id,
mtln.lot_number,
sum(lengthb(mtln.lot_number)+1) over (partition by mmt.picking_line_id order by mtln.lot_number rows between unbounded preceding and current row) lengthb
from
mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln
where
2=2 and
mtln.transaction_id=mmt.transaction_id and
mmt.transaction_source_type_id in (2,8) and
mmt.transaction_quantity<0
) x
where
x.lengthb<=4000
) shipped_lots,
(
select distinct
x.move_order_line_id,
listagg(x.lot_number,',') within group (order by x.lot_number) over (partition by x.move_order_line_id) lot_numbers
from
(
select
mmt.move_order_line_id,
mtln.lot_number,
sum(lengthb(mtln.lot_number)+1) over (partition by mmt.move_order_line_id order by mtln.lot_number rows between unbounded preceding and current row) lengthb
from
mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln
where
2=2 and
mtln.transaction_id=mmt.transaction_id and
mmt.transaction_source_type_id in (2,8) and
mmt.transaction_quantity<0
) x
where
x.lengthb<=4000
) picked_lots,
oe_order_headers_all ooha,
oe_order_lines_all oola,
(select oohoa.line_id from oe_order_holds_all oohoa where nvl(oohoa.released_flag,'N')='N' and oohoa.line_id is not null group by oohoa.line_id) line_holds,
(select oohoa.header_id from oe_order_holds_all oohoa where nvl(oohoa.released_flag,'N')='N' group by oohoa.header_id) header_holds,
ra_salesreps_all rs_order,
ra_salesreps_all rs_cust,
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
nvl2(wdd.lot_number,-1,wdd.delivery_detail_id)=shipped_lots.picking_line_id(+) and
nvl2(wdd.lot_number,-1,wdd.move_order_line_id)=picked_lots.move_order_line_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
oola.line_id=line_holds.line_id(+) and
ooha.header_id=header_holds.header_id(+) and
ooha.org_id=rs_order.org_id(+) and
nvl(ooha.salesrep_id,-3)=rs_order.salesrep_id(+) and
hcasa.org_id=rs_cust.org_id(+) and
nvl(hcsua.primary_salesrep_id,-3)=rs_cust.salesrep_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 |
