select wnd.name delivery,
wnd.delivery_id delivery_id,
substrb(cust.party_name,1,50) customer,
wpb.name name,
wsh_util_core.get_location_description(wnd.INITIAL_PICKUP_LOCATION_ID, 'NEW UI CODE') ship_from,
wsh_util_core.get_location_description(wnd.ULTIMATE_DROPOFF_LOCATION_ID, 'NEW UI CODE') ship_to,
wnd.INITIAL_PICKUP_DATE pickup_date1,
wnd.ULTIMATE_DROPOFF_DATE dropoff_date1,
lv.meaning ship_method,
wdi.sequence_number bol,
wnd.WAYBILL waybill,
wnd.GROSS_WEIGHT gross_weight,
wnd.WEIGHT_UOM_CODE uom,
we.exception_id,
decode(:p_batch_type, 'SC', decode(wnd.status_code, 'CO', 'Y',
'IT','Y',
'CL','Y',
'N'), NULL) shipped,
decode(we.exception_id, NULL, 'Y'
,decode(we.error_message,'E', 'N', 'Y')) packed,
WSH_WSHRDASP_XMLP_PKG.cf_1formula(wnd.delivery_id) CF_trip_name,
WSH_WSHRDASP_XMLP_PKG.cf_message(we.exception_id) CF_message,
wsh_util_core.get_location_description(wnd.ULTIMATE_DROPOFF_LOCATION_ID, 'NEW UI CODE') ship_to,
to_char(wnd.INITIAL_PICKUP_DATE,'DD-MON-YY') pickup_date,
to_char(wnd.ULTIMATE_DROPOFF_DATE ,'DD-MON-YY') dropoff_date,
lv.meaning ship_method,
wdi.sequence_number bol,
wnd.WAYBILL waybill,
-- wnd.GROSS_WEIGHT gross_weight,
wnd.WEIGHT_UOM_CODE uom,
we.exception_id,
decode(:p_batch_type, 'SC', decode(wnd.status_code, 'CO', 'Y',
'IT','Y',
'CL','Y',
'N'), NULL) shipped,
decode(we.exception_id, NULL, 'Y'
,decode(we.error_message,'E', 'N', 'Y')) packed
from wsh_new_deliveries wnd, wsh_picking_batches wpb, wsh_exceptions we,
fnd_lookup_values_vl lv, wsh_delivery_legs wdl,wsh_document_instances wdi,
(SELECT party.party_name,
cust_acct.cust_account_id,
'C' consignee_flag
FROM hz_parties party,
hz_cust_accounts cust_acct
WHERE cust_acct.party_id = party.party_id
UNION
SELECT party.party_name,
pv.vendor_id cust_account_id,
'V' consignee_flag
FROM hz_parties party,
po_vendors pv
WHERE pv.party_id = party.party_id) cust
where
nvl(wnd.shipment_direction, 'O') IN ('O', 'IO') and
wnd.delivery_type = 'STANDARD' and
wpb.batch_id = decode(:p_batch_type, 'SC', wnd.batch_id, 'AP', wnd.ap_batch_id, wpb.batch_id) and
&p_pr_batch
&lp_bol_number
&p_exceptions
we.delivery_id(+) = wnd.delivery_id and
we.exception_name(+) = 'WSH_BATCH_MESSAGE' and
lv.lookup_code(+) = wpb.ship_method_code and
lv.lookup_type(+) = 'SHIP_METHOD' and
lv.view_application_id(+) = 3 and
cust.cust_account_id (+)= wnd.customer_id
and cust.consignee_flag (+)= NVL(wnd.consignee_flag,'C')
and wdi.entity_id (+) = wdl.delivery_leg_id
AND wdi.entity_name (+) = 'WSH_DELIVERY_LEGS' AND wdi.document_type (+) = 'BOL'
AND wdi.status (+) <> 'CANCELLED'
AND wnd.delivery_id = wdl.delivery_id (+)
ORDER BY 16 DESC,15 DESC,3 ASC,5 ASC,4 ASC,13 ASC,12 ASC,7 ASC,11 ASC,10 ASC,9 ASC,8 ASC,6 ASC,1 ASC,2 ASC |
