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.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(shipped_lots.lot_numbers,
picked_lots.lot_numbers,
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
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
mmt.transaction_source_type_id in (2,8)
and mmt.transaction_quantity < 0
and mtln.transaction_id = mmt.transaction_id
) 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
mmt.transaction_source_type_id in (2,8)
and mmt.transaction_quantity < 0
and mtln.transaction_id = mmt.transaction_id
) x
where
x.lengthb <= 4000
) picked_lots,
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 wdd.delivery_detail_id = shipped_lots.picking_line_id (+)
and 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 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 |
