[Home] [Help]
336: , wdd.lot_number
337: , wdd.serial_number
338: , wdd.transaction_temp_id
339: from wms_license_plate_numbers wlpn
340: , wsh_delivery_details_ob_grp_v wdd
341: , wsh_delivery_assignments_v wda
342: , wsh_delivery_details_ob_grp_v wdd2
343: where wlpn.lpn_id = wdd2.lpn_id
344: and wlpn.outermost_lpn_id = p_lpn_id
338: , wdd.transaction_temp_id
339: from wms_license_plate_numbers wlpn
340: , wsh_delivery_details_ob_grp_v wdd
341: , wsh_delivery_assignments_v wda
342: , wsh_delivery_details_ob_grp_v wdd2
343: where wlpn.lpn_id = wdd2.lpn_id
344: and wlpn.outermost_lpn_id = p_lpn_id
345: and wdd2.lpn_id is not null -- for performance, bug 2418639
346: and wda.parent_delivery_detail_id = wdd2.delivery_detail_id
560: p_trip_id IN NUMBER) IS
561: BEGIN
562: open x_delivery_detail_id for
563: select wdd.delivery_detail_id, wdd.lpn_id
564: from wsh_delivery_details_ob_grp_v wdd
565: , wsh_delivery_assignments_v wda
566: , wsh_delivery_legs_ob_grp_v wdl
567: , wsh_trip_stops_ob_grp_v pickup_stop
568: , wsh_trip_stops_ob_grp_v dropoff_stop
592: , wda.delivery_id
593: , lpn.license_plate_number
594: , wdd.locator_id
595: , wdd.released_status
596: from wsh_delivery_details_ob_grp_v wdd
597: , wsh_delivery_assignments_v wda
598: , wms_license_plate_numbers lpn
599: , wsh_delivery_details_ob_grp_v wdd2
600: where wdd2.lpn_id = lpn.lpn_id
595: , wdd.released_status
596: from wsh_delivery_details_ob_grp_v wdd
597: , wsh_delivery_assignments_v wda
598: , wms_license_plate_numbers lpn
599: , wsh_delivery_details_ob_grp_v wdd2
600: where wdd2.lpn_id = lpn.lpn_id
601: and wdd2.lpn_id is not null
602: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
603: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
918: from wms_license_plate_numbers lpn
919: , wms_license_plate_numbers wlpn
920: , wsh_new_deliveries_ob_grp_v wnd
921: , wsh_delivery_legs_ob_grp_v wdl
922: , wsh_delivery_details_ob_grp_v wdd
923: , wsh_delivery_assignments_v wda
924: , wsh_delivery_details_ob_grp_v wdd2
925: where wdl.pick_up_stop_id = p_trip_stop_id
926: and wdl.delivery_id = wnd.delivery_id
920: , wsh_new_deliveries_ob_grp_v wnd
921: , wsh_delivery_legs_ob_grp_v wdl
922: , wsh_delivery_details_ob_grp_v wdd
923: , wsh_delivery_assignments_v wda
924: , wsh_delivery_details_ob_grp_v wdd2
925: where wdl.pick_up_stop_id = p_trip_stop_id
926: and wdl.delivery_id = wnd.delivery_id
927: and wnd.status_code in ('OP', 'PA')
928: and wnd.delivery_id = wda.delivery_id
945: , wlpn.license_plate_number as license_plate_number
946: , nvl(wda.delivery_id,0)
947: , get_delivery_name(wda.delivery_id)
948: , nvl(wdd.load_seq_number,0) as load_seq_num
949: from wsh_delivery_details_ob_grp_v wdd
950: , wsh_delivery_assignments_v wda
951: , wms_license_plate_numbers lpn
952: ,wms_license_plate_numbers wlpn
953: ,wsh_new_deliveries_ob_grp_v wndv
970: , wlpn.license_plate_number as license_plate_number
971: , nvl(wda.delivery_id,0)
972: , get_delivery_name(wda.delivery_id)
973: , nvl(wdd.load_seq_number,0) as load_seq_num
974: from wsh_delivery_details_ob_grp_v wdd
975: , wsh_delivery_assignments_v wda
976: , wms_license_plate_numbers lpn
977: , wms_license_plate_numbers wlpn
978: where wdd.delivery_detail_id = wda.delivery_detail_id
992: , wlpn.license_plate_number as license_plate_number
993: , nvl(wda.delivery_id,0)
994: , get_delivery_name(wda.delivery_id)
995: , nvl(wdd.load_seq_number,0) as load_seq_num
996: from wsh_delivery_details_ob_grp_v wdd
997: , wsh_delivery_assignments_v wda
998: , wms_license_plate_numbers lpn
999: ,wms_license_plate_numbers wlpn
1000: ,wsh_new_deliveries_ob_grp_v wndv
1017: , wlpn.license_plate_number as license_plate_number
1018: , nvl(wda.delivery_id,0)
1019: , get_delivery_name(wda.delivery_id)
1020: , nvl(wdd.load_seq_number,0) as load_seq_num
1021: from wsh_delivery_details_ob_grp_v wdd
1022: , wsh_delivery_assignments_v wda
1023: , wms_license_plate_numbers lpn
1024: ,wms_license_plate_numbers wlpn
1025: where wdd.delivery_detail_id = wda.delivery_detail_id
1046: from wms_license_plate_numbers lpn
1047: , wms_license_plate_numbers wlpn
1048: , wsh_new_deliveries_ob_grp_v wnd
1049: , wsh_delivery_legs_ob_grp_v wdl
1050: , wsh_delivery_details_ob_grp_v wdd
1051: , wsh_delivery_assignments_v wda
1052: , wsh_delivery_details_ob_grp_v wdd2
1053: where wdl.pick_up_stop_id = p_trip_stop_id
1054: and wdl.delivery_id = wnd.delivery_id
1048: , wsh_new_deliveries_ob_grp_v wnd
1049: , wsh_delivery_legs_ob_grp_v wdl
1050: , wsh_delivery_details_ob_grp_v wdd
1051: , wsh_delivery_assignments_v wda
1052: , wsh_delivery_details_ob_grp_v wdd2
1053: where wdl.pick_up_stop_id = p_trip_stop_id
1054: and wdl.delivery_id = wnd.delivery_id
1055: --and wdl.PARENT_DELIVERY_LEG_ID IS NULL -- Added for MDC : if delivery
1056: --is associated to a consol delivery, do not allow to select here
1077: select distinct lpn.outermost_lpn_id
1078: , wlpn.license_plate_number as license_plate_number
1079: , nvl(wda.delivery_id,0)
1080: , get_delivery_name(wda.delivery_id)
1081: from wsh_delivery_details_ob_grp_v wdd
1082: , wsh_delivery_assignments_v wda
1083: , wms_license_plate_numbers lpn
1084: ,wms_license_plate_numbers wlpn
1085: ,wsh_new_deliveries_ob_grp_v wndv
1102: select distinct lpn.outermost_lpn_id
1103: , wlpn.license_plate_number as license_plate_number
1104: , nvl(wda.delivery_id,0)
1105: , get_delivery_name(wda.delivery_id)
1106: from wsh_delivery_details_ob_grp_v wdd
1107: , wsh_delivery_assignments_v wda
1108: , wms_license_plate_numbers lpn
1109: ,wms_license_plate_numbers wlpn
1110: where wdd.delivery_detail_id = wda.delivery_detail_id
1148: /* select wstt.trip_id, wstt.delivery_id, wstt.delivery_detail_id, wstt.inventory_item_id, wstt.quantity, wstt.outermost_lpn,
1149: wstt.parent_lpn_id, wstt.parent_lpn, wdd.serial_number,
1150: msik.concatenated_segments, msik.serial_number_control_code, wdd.requested_quantity,
1151: wdd.subinventory, wdd.revision, wdd.locator_id, wdd.lot_number
1152: from wms_shipping_transaction_temp wstt, wsh_delivery_details_ob_grp_v wdd, mtl_system_items_kfv msik
1153: where wstt.delivery_detail_id = wdd.delivery_detail_id
1154: and wstt.outermost_lpn_id = p_outermost_lpn_id
1155: and msik.inventory_item_id = wdd.inventory_item_id
1156: and msik.organization_id = wdd.organization_id
1160: wlpn2.license_plate_number,wlpn.lpn_id, wlpn.license_plate_number,wdd.serial_number,
1161: msik.concatenated_segments,
1162: msik.serial_number_control_code, wdd.requested_quantity,wdd.subinventory, wdd.revision,
1163: wdd.locator_id, wdd.lot_number,wdd.picked_quantity
1164: from wms_license_plate_numbers wlpn, wms_license_plate_numbers wlpn2, wsh_delivery_details_ob_grp_v wdd,
1165: mtl_system_items_kfv msik,
1166: wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2
1167: where wlpn.outermost_lpn_id = p_outermost_lpn_id
1168: and wlpn.lpn_id = wdd2.lpn_id
1162: msik.serial_number_control_code, wdd.requested_quantity,wdd.subinventory, wdd.revision,
1163: wdd.locator_id, wdd.lot_number,wdd.picked_quantity
1164: from wms_license_plate_numbers wlpn, wms_license_plate_numbers wlpn2, wsh_delivery_details_ob_grp_v wdd,
1165: mtl_system_items_kfv msik,
1166: wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2
1167: where wlpn.outermost_lpn_id = p_outermost_lpn_id
1168: and wlpn.lpn_id = wdd2.lpn_id
1169: and wdd2.lpn_id is not null -- for performance, bug 2418639
1170: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1237: l_result := 0;
1238:
1239: select transaction_temp_id
1240: into l_transaction_temp_id
1241: from wsh_delivery_details_ob_grp_v
1242: where delivery_detail_id = l_delivery_detail_id;
1243:
1244: if( l_transaction_temp_id is null ) then
1245: select mtl_material_Transactions_s.nextval
1337: p_outermost_lpn_id IN NUMBER) IS
1338:
1339: cursor sum_delivery_details(p_outermost_lpn_id IN NUMBER, p_delivery_id IN NUMBER) is
1340: select sum(wdd.requested_quantity), sum(wlc.quantity), wda.delivery_id, wlc.inventory_item_id, wlc.parent_lpn_id
1341: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2,
1342: wms_license_plate_numbers lpn, wms_lpn_contents wlc
1343: where wdd.delivery_detail_id = wda.delivery_detail_id
1344: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
1345: and wdd2.lpn_id = lpn.lpn_id
1417: l_delivery_id NUMBER;
1418: l_trip_id NUMBER;
1419: cursor deliveries_in_lpn is
1420: select distinct wda.delivery_id
1421: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
1422: wsh_delivery_details_ob_grp_v wdd2
1423: where lpn.outermost_lpn_id = p_outermost_lpn_id
1424: and wdd2.lpn_id = lpn.lpn_id
1425: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1418: l_trip_id NUMBER;
1419: cursor deliveries_in_lpn is
1420: select distinct wda.delivery_id
1421: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
1422: wsh_delivery_details_ob_grp_v wdd2
1423: where lpn.outermost_lpn_id = p_outermost_lpn_id
1424: and wdd2.lpn_id = lpn.lpn_id
1425: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1426: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
1487: l_delivery_id NUMBER;
1488: l_trip_id NUMBER;
1489: cursor deliveries_in_lpn is
1490: select distinct wda.delivery_id
1491: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
1492: wsh_delivery_details_ob_grp_v wdd2
1493: where lpn.outermost_lpn_id = p_outermost_lpn_id
1494: and wdd2.lpn_id = lpn.lpn_id
1495: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1488: l_trip_id NUMBER;
1489: cursor deliveries_in_lpn is
1490: select distinct wda.delivery_id
1491: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
1492: wsh_delivery_details_ob_grp_v wdd2
1493: where lpn.outermost_lpn_id = p_outermost_lpn_id
1494: and wdd2.lpn_id = lpn.lpn_id
1495: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1496: and wdd2.lpn_id is not null -- for performance, bug 2418639
1610: l_prev_hdr_id NUMBER;
1611: l_prev_line_id NUMBER;
1612: cursor delivery_details( p_outermost_lpn_id NUMBER) is
1613: select wdd.delivery_detail_id, wdd.source_header_id, wdd.source_line_id
1614: from wms_license_plate_numbers wlpn, wsh_delivery_details_ob_grp_v wdd0, wsh_delivery_assignments_v wda,
1615: wsh_delivery_details_ob_grp_v wdd
1616: where
1617: wlpn.outermost_lpn_id = p_outermost_lpn_id
1618: and wlpn.lpn_id = wdd0.lpn_id
1611: l_prev_line_id NUMBER;
1612: cursor delivery_details( p_outermost_lpn_id NUMBER) is
1613: select wdd.delivery_detail_id, wdd.source_header_id, wdd.source_line_id
1614: from wms_license_plate_numbers wlpn, wsh_delivery_details_ob_grp_v wdd0, wsh_delivery_assignments_v wda,
1615: wsh_delivery_details_ob_grp_v wdd
1616: where
1617: wlpn.outermost_lpn_id = p_outermost_lpn_id
1618: and wlpn.lpn_id = wdd0.lpn_id
1619: and wdd0.released_status = 'X' -- For LPN reuse ER : 6845650
1848: -- check if the outermost LPN contains multiple delivery
1849: if p_trip_id = 0 then
1850: select count(distinct wda.delivery_id)
1851: into l_delivery_count
1852: from wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd
1853: , wms_license_plate_numbers wlpn
1854: where wlpn.outermost_lpn_id = p_outermost_lpn_id
1855: and wlpn.lpn_id = wdd.lpn_id
1856: and wdd.lpn_id is not null -- for performance, bug 2418639
2962: lpn.subinventory_code,
2963: milk.concatenated_segments,
2964: wnd.name
2965: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,wsh_trip_stops_ob_grp_v pickup_stop,
2966: wsh_delivery_legs_ob_grp_v wdl,wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
2967: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
2968: where wdd.delivery_detail_id = wda.delivery_detail_id
2969: and wdd.released_status = 'Y'
2970: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
2963: milk.concatenated_segments,
2964: wnd.name
2965: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,wsh_trip_stops_ob_grp_v pickup_stop,
2966: wsh_delivery_legs_ob_grp_v wdl,wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
2967: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
2968: where wdd.delivery_detail_id = wda.delivery_detail_id
2969: and wdd.released_status = 'Y'
2970: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
2971: and wda.delivery_id = wdl.delivery_id
2985: lpn.subinventory_code,
2986: milk.concatenated_segments,
2987: wnd.name
2988: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,
2989: wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
2990: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
2991: where wdd.delivery_detail_id = wda.delivery_detail_id
2992: and wdd.released_status = 'Y'
2993: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
2986: milk.concatenated_segments,
2987: wnd.name
2988: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,
2989: wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
2990: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
2991: where wdd.delivery_detail_id = wda.delivery_detail_id
2992: and wdd.released_status = 'Y'
2993: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
2994: and wda.delivery_id in ( select distinct delivery_id
3045: into l_count
3046: from wsh_trip_stops_ob_grp_v pickup_stop
3047: ,wsh_delivery_legs_ob_grp_v wdl
3048: ,wsh_delivery_assignments_v wda
3049: ,wsh_delivery_details_ob_grp_v wdd
3050: ,wsh_delivery_details_ob_grp_v wdd2
3051: ,wms_license_plate_numbers lpn
3052: ,wms_license_plate_numbers wlpn
3053: -- 5582189 dherring added table in from clause so that
3046: from wsh_trip_stops_ob_grp_v pickup_stop
3047: ,wsh_delivery_legs_ob_grp_v wdl
3048: ,wsh_delivery_assignments_v wda
3049: ,wsh_delivery_details_ob_grp_v wdd
3050: ,wsh_delivery_details_ob_grp_v wdd2
3051: ,wms_license_plate_numbers lpn
3052: ,wms_license_plate_numbers wlpn
3053: -- 5582189 dherring added table in from clause so that
3054: -- the status code can be checked
3087: , mtl_item_locations_kfv milk
3088: , wsh_trip_stops_ob_grp_v pickup_stop
3089: , wsh_delivery_legs_ob_grp_v wdl
3090: , wsh_delivery_assignments_v wda
3091: , wsh_delivery_details_ob_grp_v wdd
3092: , wsh_delivery_details_ob_grp_v wdd2
3093: , wsh_new_deliveries_ob_grp_v wnd
3094: , wms_license_plate_numbers wlpn
3095: where wdd.delivery_detail_id = wda.delivery_detail_id
3088: , wsh_trip_stops_ob_grp_v pickup_stop
3089: , wsh_delivery_legs_ob_grp_v wdl
3090: , wsh_delivery_assignments_v wda
3091: , wsh_delivery_details_ob_grp_v wdd
3092: , wsh_delivery_details_ob_grp_v wdd2
3093: , wsh_new_deliveries_ob_grp_v wnd
3094: , wms_license_plate_numbers wlpn
3095: where wdd.delivery_detail_id = wda.delivery_detail_id
3096: and wdd.released_status = 'Y'
3114: select count(distinct lpn.outermost_lpn_id)
3115: into l_count
3116: from wsh_delivery_assignments_v wda
3117: ,wsh_new_deliveries_ob_grp_v wnd
3118: ,wsh_delivery_details_ob_grp_v wdd
3119: ,wsh_delivery_details_ob_grp_v wdd2
3120: ,wms_license_plate_numbers lpn
3121: ,wms_license_plate_numbers wlpn
3122: where
3115: into l_count
3116: from wsh_delivery_assignments_v wda
3117: ,wsh_new_deliveries_ob_grp_v wnd
3118: ,wsh_delivery_details_ob_grp_v wdd
3119: ,wsh_delivery_details_ob_grp_v wdd2
3120: ,wms_license_plate_numbers lpn
3121: ,wms_license_plate_numbers wlpn
3122: where
3123: wdd.delivery_detail_id = wda.delivery_detail_id
3169: , wnd.name
3170: from wms_license_plate_numbers lpn
3171: , mtl_item_locations_kfv milk
3172: , wsh_delivery_assignments_v wda
3173: , wsh_delivery_details_ob_grp_v wdd
3174: , wsh_delivery_details_ob_grp_v wdd2
3175: , wsh_new_deliveries_ob_grp_v wnd
3176: , wms_license_plate_numbers wlpn
3177: where wdd.delivery_detail_id = wda.delivery_detail_id
3170: from wms_license_plate_numbers lpn
3171: , mtl_item_locations_kfv milk
3172: , wsh_delivery_assignments_v wda
3173: , wsh_delivery_details_ob_grp_v wdd
3174: , wsh_delivery_details_ob_grp_v wdd2
3175: , wsh_new_deliveries_ob_grp_v wnd
3176: , wms_license_plate_numbers wlpn
3177: where wdd.delivery_detail_id = wda.delivery_detail_id
3178: and wdd.released_status = 'Y'
3236: if( p_trip_id <> 0 ) then
3237: BEGIN
3238: select count(*)
3239: into l_count
3240: from wsh_delivery_details_ob_grp_v wdd
3241: , wsh_delivery_assignments_v wda
3242: , wsh_delivery_legs_ob_grp_v wdl
3243: , wsh_Trip_stops_ob_grp_v pickup_stop
3244: , mtl_system_items_kfv msik
3269: , wdd.inventory_item_id
3270: , wdd.requested_quantity
3271: , msik.concatenated_segments
3272: , msik.description
3273: from wsh_delivery_details_ob_grp_v wdd
3274: , wsh_delivery_assignments_v wda
3275: , wsh_new_deliveries_ob_grp_v wnd
3276: , wsh_delivery_legs_ob_grp_v wdl
3277: , wsh_trip_Stops_ob_grp_v pickup_stop
3292: else
3293: BEGIN
3294: select count(*)
3295: into l_count
3296: from wsh_delivery_details_ob_grp_v wdd
3297: , wsh_delivery_assignments_v wda
3298: , mtl_system_items_kfv msik
3299: where
3300: wda.delivery_detail_id = wdd.delivery_detail_id
3341: , wdd.inventory_item_id
3342: , wdd.requested_quantity
3343: , msiv.concatenated_segments
3344: , msiv.description
3345: from wsh_delivery_details_ob_grp_v wdd
3346: , wsh_delivery_assignments_v wda
3347: , mtl_system_items_vl msiv
3348: , wsh_new_deliveries_ob_grp_v wnd
3349: where wda.delivery_detail_id = wdd.delivery_detail_id
3391: unshipped_count NUMBER := 0;
3392: l_source_header_id NUMBER;
3393: CURSOR ship_set_in_trip(p_trip_id NUMBER) IS
3394: SELECT distinct wdd.ship_set_id
3395: FROM wsh_delivery_details_ob_grp_v wdd,wms_shipping_transaction_temp wstt
3396: WHERE wdd.delivery_detail_id = wstt.delivery_detail_id
3397: AND wdd.ship_set_id is not null
3398: AND wstt.organization_id = p_organization_id
3399: AND wstt.trip_id = p_trip_id
3401: AND nvl(wstt.direct_ship_flag,'N') = 'N';
3402:
3403: CURSOR delivered_ship_set IS
3404: SELECT distinct wdd.ship_set_id, wdd.source_header_id
3405: FROM wsh_delivery_details_ob_grp_v wdd,wms_shipping_transaction_temp wstt
3406: WHERE wdd.delivery_detail_id = wstt.delivery_detail_id
3407: AND wdd.ship_set_id is not null
3408: AND wstt.organization_id = p_organization_id
3409: AND wstt.dock_appoint_flag = 'N'
3412:
3413: CURSOR c_unshipped_count (p_ship_set_id NUMBER)
3414: IS
3415: SELECT 1
3416: FROM wsh_delivery_details_ob_grp_v wdd,
3417: oe_order_lines_all oel
3418: WHERE oel.ship_set_id = p_ship_set_id
3419: AND wdd.source_line_id = oel.line_id
3420: AND wdd.source_header_id = oel.header_id
3476: INTO unshipped_count
3477: FROM DUAL
3478: WHERE exists (
3479: SELECT 1
3480: FROM wsh_delivery_details_ob_grp_v wdd
3481: WHERE
3482: wdd.source_header_id = l_source_header_id
3483: AND wdd.ship_set_id = l_ship_set_id
3484: AND nvl(wdd.shipped_quantity,wdd.picked_quantity) is null
3543: x_return_status OUT NOCOPY VARCHAR2) IS
3544: /*
3545: cursor Delivery_Details(p_delivery_id NUMBER) is
3546: select wdd.delivery_detail_id
3547: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda
3548: where wda.delivery_id = p_delivery_id
3549: and wda.delivery_detail_id = wdd.delivery_detail_id
3550: and wdd.lpn_id is NULL;
3551: */
3621:
3622: CURSOR outer_lpn(p_delivery_id number) IS
3623: SELECT DISTINCT wda.parent_delivery_detail_id inner_lpn_wdd,
3624: wlpn.outermost_lpn_id outermost_lpn_id
3625: FROM wsh_delivery_details_ob_grp_v wdd,
3626: wsh_delivery_assignments_v wda,
3627: wms_license_plate_numbers wlpn
3628: WHERE wda.delivery_id = p_delivery_id
3629: AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
5193: ol.header_id,
5194: ol.line_id,
5195: wdd.released_status,
5196: mol.organization_id
5197: FROM wsh_delivery_details_ob_grp_v wdd,
5198: wsh_delivery_assignments_v wda,
5199: wsh_delivery_details_ob_grp_v wdd2,
5200: wms_license_plate_numbers lpn,
5201: mtl_txn_request_lines mol,
5195: wdd.released_status,
5196: mol.organization_id
5197: FROM wsh_delivery_details_ob_grp_v wdd,
5198: wsh_delivery_assignments_v wda,
5199: wsh_delivery_details_ob_grp_v wdd2,
5200: wms_license_plate_numbers lpn,
5201: mtl_txn_request_lines mol,
5202: oe_order_lines_all ol
5203: WHERE lpn.outermost_lpn_id = p_lpn_id
5582: END IF;
5583: BEGIN
5584: SELECT COUNT(*)
5585: INTO l_count
5586: FROM wsh_delivery_details_ob_grp_v wdd,
5587: wsh_delivery_assignments_v wda,
5588: wsh_new_deliveries_ob_grp_v wnd,
5589: wsh_delivery_legs_ob_grp_v wdl,
5590: wsh_trip_Stops_ob_grp_v pickup_stop,
5615: wdd.requested_quantity,
5616: wdd.requested_quantity_uom,
5617: msik.concatenated_segments,
5618: msik.description
5619: FROM wsh_delivery_details_ob_grp_v wdd,
5620: wsh_delivery_assignments_v wda,
5621: wsh_new_deliveries_ob_grp_v wnd,
5622: wsh_delivery_legs_ob_grp_v wdl,
5623: wsh_trip_stops_ob_grp_v pickup_stop,
5635: ELSE
5636: BEGIN
5637: SELECT COUNT(*)
5638: INTO l_count
5639: FROM wsh_delivery_details_ob_grp_v wdd,
5640: wsh_delivery_assignments_v wda,
5641: mtl_system_items_kfv
5642: msik, wsh_new_deliveries_ob_grp_v wnd
5643: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
5680: wdd.requested_quantity,
5681: wdd.requested_quantity_uom,
5682: msik.concatenated_segments,
5683: msik.description
5684: FROM wsh_delivery_details_ob_grp_v wdd,
5685: wsh_delivery_assignments_v wda,
5686: mtl_system_items_kfv
5687: msik, wsh_new_deliveries_ob_grp_v wnd
5688: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
5765: , wdd.source_header_type_id
5766: , c.party_name
5767: , hca.cust_account_id
5768: , c.party_number
5769: from wsh_delivery_details_ob_grp_v wdd
5770: --, R12 TCA changes ra_customers c -- added the following tables instead
5771: , hz_parties c , hz_cust_accounts hca
5772: , oe_transaction_types_tl otl
5773: , wms_direct_ship_temp wdst
5854: , NVL(wds.processed_flag,'N') processed_flag
5855: , NVL(wds.processed_quantity,0) processed_quantity
5856: , wdd.src_requested_quantity_uom
5857: FROM oe_order_lines_all oel
5858: , wsh_delivery_details_ob_grp_v wdd
5859: , mtl_system_items_kfv msik
5860: , wms_direct_ship_temp wds
5861: WHERE oel.header_id =p_header_id
5862: and oel.ship_from_org_id = p_org_id
5951: , 'N' processed_flag
5952: , 0 processed_quantity
5953: , wdd.src_requested_quantity_uom
5954: FROM oe_order_lines_all oel
5955: , wsh_delivery_details_ob_grp_v wdd
5956: , mtl_system_items_kfv msik
5957: , wms_direct_ship_temp wds
5958: WHERE oel.header_id =p_header_id
5959: and oel.ship_from_org_id = p_org_id
6056: , NVL(wds.processed_flag,'N') processed_flag
6057: , NVL(wds.processed_quantity,0) processed_quantity
6058: , wdd.src_requested_quantity_uom
6059: FROM oe_order_lines_all oel
6060: , wsh_delivery_details_ob_grp_v wdd
6061: , mtl_system_items_kfv msik
6062: , wms_direct_ship_temp wds
6063: WHERE oel.header_id =p_header_id
6064: and oel.ship_from_org_id = p_org_id
6154: , 'N' processed_flag
6155: , 0 processed_quantity
6156: , wdd.src_requested_quantity_uom
6157: FROM oe_order_lines_all oel
6158: , wsh_delivery_details_ob_grp_v wdd
6159: , mtl_system_items_kfv msik
6160: , wms_direct_ship_temp wds
6161: WHERE oel.header_id =p_header_id
6162: and oel.ship_from_org_id = p_org_id
6409: */
6410: FUNCTION get_container_name(p_container_name IN VARCHAR2) RETURN VARCHAR2 IS
6411:
6412: CURSOR l_container IS
6413: select container_name from wsh_delivery_details_ob_grp_v
6414: where container_name like p_container_name||'-@-%';
6415:
6416: l_num_part_str VARCHAR2(30);
6417: