select distinct
wnd.name delivery,
ooha.order_number,
mp.organization_code||'-'||xpb.box_id box_number,
regexp_replace(xxen_util.meaning(xpb.box_type_code,'XXWSH_PACKING_BOX_TYPES',665),'\s\d+x\d+x\d+') box_type,
xpb.gross_weight,
sum(xpba.weight) over (partition by xpb.box_id) net_weight,
xpb.length,
xpb.width,
xpb.height,
ottt.name order_type,
ottt2.name line_type,
xpba.shipping_comment,
xpb.shipping_box_type,
xxen_util.meaning(wnd.ship_method_code,'SHIP_METHOD',3) ship_method,
hca.account_name customer_name,
nvl(ftv.territory_short_name,hl.country) ship_to_country
from
mtl_parameters mp,
wsh_new_deliveries wnd,
hz_cust_accounts hca,
hz_locations hl,
fnd_territories_vl ftv,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
xxwsh_packing_box_assignments xpba,
xxwsh_packing_boxes xpb,
mtl_system_items_b msib,
oe_order_headers_all ooha,
oe_order_lines_all oola,
oe_transaction_types_tl ottt,
oe_transaction_types_tl ottt2
where
1=1 and
mp.organization_id=xpba.organization_id and
wnd.customer_id=hca.cust_account_id(+) and
wnd.ultimate_dropoff_location_id=hl.location_id and
hl.country=ftv.territory_code(+) and
wnd.delivery_id=wda.delivery_id and
wda.delivery_detail_id=xpba.delivery_detail_id and
xpba.box_id=xpb.box_id and
xpba.organization_id=msib.organization_id(+) and
xpba.component_item_id=msib.inventory_item_id(+) and
wda.delivery_detail_id=wdd.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
ooha.order_type_id=ottt.transaction_type_id(+) and
ottt.language(+)=userenv('lang') and
oola.line_type_id=ottt2.transaction_type_id(+) and
ottt2.language(+)=userenv('lang') |
