[Home] [Help]
387: , wdd.lot_number
388: , wdd.serial_number
389: , wdd.transaction_temp_id
390: from wms_license_plate_numbers wlpn
391: , wsh_delivery_details_ob_grp_v wdd
392: , wsh_delivery_assignments_v wda
393: , wsh_delivery_details_ob_grp_v wdd2
394: where wlpn.lpn_id = wdd2.lpn_id
395: and wlpn.outermost_lpn_id = p_lpn_id
389: , wdd.transaction_temp_id
390: from wms_license_plate_numbers wlpn
391: , wsh_delivery_details_ob_grp_v wdd
392: , wsh_delivery_assignments_v wda
393: , wsh_delivery_details_ob_grp_v wdd2
394: where wlpn.lpn_id = wdd2.lpn_id
395: and wlpn.outermost_lpn_id = p_lpn_id
396: and wdd2.lpn_id is not null -- for performance, bug 2418639
397: and wda.parent_delivery_detail_id = wdd2.delivery_detail_id
613: p_trip_id IN NUMBER) IS
614: BEGIN
615: open x_delivery_detail_id for
616: select wdd.delivery_detail_id, wdd.lpn_id
617: from wsh_delivery_details_ob_grp_v wdd
618: , wsh_delivery_assignments_v wda
619: , wsh_delivery_legs_ob_grp_v wdl
620: , wsh_trip_stops_ob_grp_v pickup_stop
621: , wsh_trip_stops_ob_grp_v dropoff_stop
645: , wda.delivery_id
646: , lpn.license_plate_number
647: , wdd.locator_id
648: , wdd.released_status
649: from wsh_delivery_details_ob_grp_v wdd
650: , wsh_delivery_assignments_v wda
651: , wms_license_plate_numbers lpn
652: , wsh_delivery_details_ob_grp_v wdd2
653: where wdd2.lpn_id = lpn.lpn_id
648: , wdd.released_status
649: from wsh_delivery_details_ob_grp_v wdd
650: , wsh_delivery_assignments_v wda
651: , wms_license_plate_numbers lpn
652: , wsh_delivery_details_ob_grp_v wdd2
653: where wdd2.lpn_id = lpn.lpn_id
654: and wdd2.lpn_id is not null
655: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
656: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
971: from wms_license_plate_numbers lpn
972: , wms_license_plate_numbers wlpn
973: , wsh_new_deliveries_ob_grp_v wnd
974: , wsh_delivery_legs_ob_grp_v wdl
975: , wsh_delivery_details_ob_grp_v wdd
976: , wsh_delivery_assignments_v wda
977: , wsh_delivery_details_ob_grp_v wdd2
978: where wdl.pick_up_stop_id = p_trip_stop_id
979: and wdl.delivery_id = wnd.delivery_id
973: , wsh_new_deliveries_ob_grp_v wnd
974: , wsh_delivery_legs_ob_grp_v wdl
975: , wsh_delivery_details_ob_grp_v wdd
976: , wsh_delivery_assignments_v wda
977: , wsh_delivery_details_ob_grp_v wdd2
978: where wdl.pick_up_stop_id = p_trip_stop_id
979: and wdl.delivery_id = wnd.delivery_id
980: and wnd.status_code in ('OP', 'PA')
981: and wnd.delivery_id = wda.delivery_id
998: , wlpn.license_plate_number as license_plate_number
999: , nvl(wda.delivery_id,0)
1000: , get_delivery_name(wda.delivery_id)
1001: , nvl(wdd.load_seq_number,0) as load_seq_num
1002: from wsh_delivery_details_ob_grp_v wdd
1003: , wsh_delivery_assignments_v wda
1004: , wms_license_plate_numbers lpn
1005: ,wms_license_plate_numbers wlpn
1006: ,wsh_new_deliveries_ob_grp_v wndv
1023: , wlpn.license_plate_number as license_plate_number
1024: , nvl(wda.delivery_id,0)
1025: , get_delivery_name(wda.delivery_id)
1026: , nvl(wdd.load_seq_number,0) as load_seq_num
1027: from wsh_delivery_details_ob_grp_v wdd
1028: , wsh_delivery_assignments_v wda
1029: , wms_license_plate_numbers lpn
1030: , wms_license_plate_numbers wlpn
1031: where wdd.delivery_detail_id = wda.delivery_detail_id
1045: , wlpn.license_plate_number as license_plate_number
1046: , nvl(wda.delivery_id,0)
1047: , get_delivery_name(wda.delivery_id)
1048: , nvl(wdd.load_seq_number,0) as load_seq_num
1049: from wsh_delivery_details_ob_grp_v wdd
1050: , wsh_delivery_assignments_v wda
1051: , wms_license_plate_numbers lpn
1052: ,wms_license_plate_numbers wlpn
1053: ,wsh_new_deliveries_ob_grp_v wndv
1070: , wlpn.license_plate_number as license_plate_number
1071: , nvl(wda.delivery_id,0)
1072: , get_delivery_name(wda.delivery_id)
1073: , nvl(wdd.load_seq_number,0) as load_seq_num
1074: from wsh_delivery_details_ob_grp_v wdd
1075: , wsh_delivery_assignments_v wda
1076: , wms_license_plate_numbers lpn
1077: ,wms_license_plate_numbers wlpn
1078: where wdd.delivery_detail_id = wda.delivery_detail_id
1099: from wms_license_plate_numbers lpn
1100: , wms_license_plate_numbers wlpn
1101: , wsh_new_deliveries_ob_grp_v wnd
1102: , wsh_delivery_legs_ob_grp_v wdl
1103: , wsh_delivery_details_ob_grp_v wdd
1104: , wsh_delivery_assignments_v wda
1105: , wsh_delivery_details_ob_grp_v wdd2
1106: where wdl.pick_up_stop_id = p_trip_stop_id
1107: and wdl.delivery_id = wnd.delivery_id
1101: , wsh_new_deliveries_ob_grp_v wnd
1102: , wsh_delivery_legs_ob_grp_v wdl
1103: , wsh_delivery_details_ob_grp_v wdd
1104: , wsh_delivery_assignments_v wda
1105: , wsh_delivery_details_ob_grp_v wdd2
1106: where wdl.pick_up_stop_id = p_trip_stop_id
1107: and wdl.delivery_id = wnd.delivery_id
1108: --and wdl.PARENT_DELIVERY_LEG_ID IS NULL -- Added for MDC : if delivery
1109: --is associated to a consol delivery, do not allow to select here
1130: select distinct lpn.outermost_lpn_id
1131: , wlpn.license_plate_number as license_plate_number
1132: , nvl(wda.delivery_id,0)
1133: , get_delivery_name(wda.delivery_id)
1134: from wsh_delivery_details_ob_grp_v wdd
1135: , wsh_delivery_assignments_v wda
1136: , wms_license_plate_numbers lpn
1137: ,wms_license_plate_numbers wlpn
1138: ,wsh_new_deliveries_ob_grp_v wndv
1155: select distinct lpn.outermost_lpn_id
1156: , wlpn.license_plate_number as license_plate_number
1157: , nvl(wda.delivery_id,0)
1158: , get_delivery_name(wda.delivery_id)
1159: from wsh_delivery_details_ob_grp_v wdd
1160: , wsh_delivery_assignments_v wda
1161: , wms_license_plate_numbers lpn
1162: ,wms_license_plate_numbers wlpn
1163: where wdd.delivery_detail_id = wda.delivery_detail_id
1201: /* select wstt.trip_id, wstt.delivery_id, wstt.delivery_detail_id, wstt.inventory_item_id, wstt.quantity, wstt.outermost_lpn,
1202: wstt.parent_lpn_id, wstt.parent_lpn, wdd.serial_number,
1203: msik.concatenated_segments, msik.serial_number_control_code, wdd.requested_quantity,
1204: wdd.subinventory, wdd.revision, wdd.locator_id, wdd.lot_number
1205: from wms_shipping_transaction_temp wstt, wsh_delivery_details_ob_grp_v wdd, mtl_system_items_kfv msik
1206: where wstt.delivery_detail_id = wdd.delivery_detail_id
1207: and wstt.outermost_lpn_id = p_outermost_lpn_id
1208: and msik.inventory_item_id = wdd.inventory_item_id
1209: and msik.organization_id = wdd.organization_id
1213: wlpn2.license_plate_number,wlpn.lpn_id, wlpn.license_plate_number,wdd.serial_number,
1214: msik.concatenated_segments,
1215: msik.serial_number_control_code, wdd.requested_quantity,wdd.subinventory, wdd.revision,
1216: wdd.locator_id, wdd.lot_number,wdd.picked_quantity
1217: from wms_license_plate_numbers wlpn, wms_license_plate_numbers wlpn2, wsh_delivery_details_ob_grp_v wdd,
1218: mtl_system_items_kfv msik,
1219: wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2
1220: where wlpn.outermost_lpn_id = p_outermost_lpn_id
1221: and wlpn.lpn_id = wdd2.lpn_id
1215: msik.serial_number_control_code, wdd.requested_quantity,wdd.subinventory, wdd.revision,
1216: wdd.locator_id, wdd.lot_number,wdd.picked_quantity
1217: from wms_license_plate_numbers wlpn, wms_license_plate_numbers wlpn2, wsh_delivery_details_ob_grp_v wdd,
1218: mtl_system_items_kfv msik,
1219: wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2
1220: where wlpn.outermost_lpn_id = p_outermost_lpn_id
1221: and wlpn.lpn_id = wdd2.lpn_id
1222: and wdd2.lpn_id is not null -- for performance, bug 2418639
1223: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1291: l_result := 0;
1292:
1293: select transaction_temp_id , transaction_id --BUG# 10264172
1294: into l_transaction_temp_id , l_transaction_id --BUG# 10264172
1295: from wsh_delivery_details_ob_grp_v
1296: where delivery_detail_id = l_delivery_detail_id;
1297:
1298: if( l_transaction_temp_id is null ) then
1299: select mtl_material_Transactions_s.nextval
1395: p_outermost_lpn_id IN NUMBER) IS
1396:
1397: cursor sum_delivery_details(p_outermost_lpn_id IN NUMBER, p_delivery_id IN NUMBER) is
1398: select sum(wdd.requested_quantity), sum(wlc.quantity), wda.delivery_id, wlc.inventory_item_id, wlc.parent_lpn_id
1399: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2,
1400: wms_license_plate_numbers lpn, wms_lpn_contents wlc
1401: where wdd.delivery_detail_id = wda.delivery_detail_id
1402: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
1403: and wdd2.lpn_id = lpn.lpn_id
1475: l_delivery_id NUMBER;
1476: l_trip_id NUMBER;
1477: cursor deliveries_in_lpn is
1478: select distinct wda.delivery_id
1479: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
1480: wsh_delivery_details_ob_grp_v wdd2
1481: where lpn.outermost_lpn_id = p_outermost_lpn_id
1482: and wdd2.lpn_id = lpn.lpn_id
1483: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1476: l_trip_id NUMBER;
1477: cursor deliveries_in_lpn is
1478: select distinct wda.delivery_id
1479: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
1480: wsh_delivery_details_ob_grp_v wdd2
1481: where lpn.outermost_lpn_id = p_outermost_lpn_id
1482: and wdd2.lpn_id = lpn.lpn_id
1483: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1484: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
1545: l_delivery_id NUMBER;
1546: l_trip_id NUMBER;
1547: cursor deliveries_in_lpn is
1548: select distinct wda.delivery_id
1549: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
1550: wsh_delivery_details_ob_grp_v wdd2
1551: where lpn.outermost_lpn_id = p_outermost_lpn_id
1552: and wdd2.lpn_id = lpn.lpn_id
1553: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1546: l_trip_id NUMBER;
1547: cursor deliveries_in_lpn is
1548: select distinct wda.delivery_id
1549: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
1550: wsh_delivery_details_ob_grp_v wdd2
1551: where lpn.outermost_lpn_id = p_outermost_lpn_id
1552: and wdd2.lpn_id = lpn.lpn_id
1553: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1554: and wdd2.lpn_id is not null -- for performance, bug 2418639
1668: l_prev_hdr_id NUMBER;
1669: l_prev_line_id NUMBER;
1670: cursor delivery_details( p_outermost_lpn_id NUMBER) is
1671: select wdd.delivery_detail_id, wdd.source_header_id, wdd.source_line_id
1672: from wms_license_plate_numbers wlpn, wsh_delivery_details_ob_grp_v wdd0, wsh_delivery_assignments_v wda,
1673: wsh_delivery_details_ob_grp_v wdd
1674: where
1675: wlpn.outermost_lpn_id = p_outermost_lpn_id
1676: and wlpn.lpn_id = wdd0.lpn_id
1669: l_prev_line_id NUMBER;
1670: cursor delivery_details( p_outermost_lpn_id NUMBER) is
1671: select wdd.delivery_detail_id, wdd.source_header_id, wdd.source_line_id
1672: from wms_license_plate_numbers wlpn, wsh_delivery_details_ob_grp_v wdd0, wsh_delivery_assignments_v wda,
1673: wsh_delivery_details_ob_grp_v wdd
1674: where
1675: wlpn.outermost_lpn_id = p_outermost_lpn_id
1676: and wlpn.lpn_id = wdd0.lpn_id
1677: and wdd0.released_status = 'X' -- For LPN reuse ER : 6845650
1908: -- check if the outermost LPN contains multiple delivery
1909: if p_trip_id = 0 then
1910: select count(distinct wda.delivery_id)
1911: into l_delivery_count
1912: from wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd
1913: , wms_license_plate_numbers wlpn
1914: where wlpn.outermost_lpn_id = p_outermost_lpn_id
1915: and wlpn.lpn_id = wdd.lpn_id
1916: and wdd.lpn_id is not null -- for performance, bug 2418639
3023: lpn.subinventory_code,
3024: milk.concatenated_segments,
3025: wnd.name
3026: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,wsh_trip_stops_ob_grp_v pickup_stop,
3027: wsh_delivery_legs_ob_grp_v wdl,wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
3028: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
3029: where wdd.delivery_detail_id = wda.delivery_detail_id
3030: and wdd.released_status = 'Y'
3031: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
3024: milk.concatenated_segments,
3025: wnd.name
3026: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,wsh_trip_stops_ob_grp_v pickup_stop,
3027: wsh_delivery_legs_ob_grp_v wdl,wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
3028: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
3029: where wdd.delivery_detail_id = wda.delivery_detail_id
3030: and wdd.released_status = 'Y'
3031: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
3032: and wda.delivery_id = wdl.delivery_id
3046: lpn.subinventory_code,
3047: milk.concatenated_segments,
3048: wnd.name
3049: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,
3050: wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
3051: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
3052: where wdd.delivery_detail_id = wda.delivery_detail_id
3053: and wdd.released_status = 'Y'
3054: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
3047: milk.concatenated_segments,
3048: wnd.name
3049: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,
3050: wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
3051: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
3052: where wdd.delivery_detail_id = wda.delivery_detail_id
3053: and wdd.released_status = 'Y'
3054: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
3055: and wda.delivery_id in ( select distinct delivery_id
3106: into l_count
3107: from wsh_trip_stops_ob_grp_v pickup_stop
3108: ,wsh_delivery_legs_ob_grp_v wdl
3109: ,wsh_delivery_assignments_v wda
3110: ,wsh_delivery_details_ob_grp_v wdd
3111: ,wsh_delivery_details_ob_grp_v wdd2
3112: ,wms_license_plate_numbers lpn
3113: ,wms_license_plate_numbers wlpn
3114: -- 5582189 dherring added table in from clause so that
3107: from wsh_trip_stops_ob_grp_v pickup_stop
3108: ,wsh_delivery_legs_ob_grp_v wdl
3109: ,wsh_delivery_assignments_v wda
3110: ,wsh_delivery_details_ob_grp_v wdd
3111: ,wsh_delivery_details_ob_grp_v wdd2
3112: ,wms_license_plate_numbers lpn
3113: ,wms_license_plate_numbers wlpn
3114: -- 5582189 dherring added table in from clause so that
3115: -- the status code can be checked
3148: , mtl_item_locations_kfv milk
3149: , wsh_trip_stops_ob_grp_v pickup_stop
3150: , wsh_delivery_legs_ob_grp_v wdl
3151: , wsh_delivery_assignments_v wda
3152: , wsh_delivery_details_ob_grp_v wdd
3153: , wsh_delivery_details_ob_grp_v wdd2
3154: , wsh_new_deliveries_ob_grp_v wnd
3155: , wms_license_plate_numbers wlpn
3156: where wdd.delivery_detail_id = wda.delivery_detail_id
3149: , wsh_trip_stops_ob_grp_v pickup_stop
3150: , wsh_delivery_legs_ob_grp_v wdl
3151: , wsh_delivery_assignments_v wda
3152: , wsh_delivery_details_ob_grp_v wdd
3153: , wsh_delivery_details_ob_grp_v wdd2
3154: , wsh_new_deliveries_ob_grp_v wnd
3155: , wms_license_plate_numbers wlpn
3156: where wdd.delivery_detail_id = wda.delivery_detail_id
3157: and wdd.released_status = 'Y'
3175: select count(distinct lpn.outermost_lpn_id)
3176: into l_count
3177: from wsh_delivery_assignments_v wda
3178: ,wsh_new_deliveries_ob_grp_v wnd
3179: ,wsh_delivery_details_ob_grp_v wdd
3180: ,wsh_delivery_details_ob_grp_v wdd2
3181: ,wms_license_plate_numbers lpn
3182: ,wms_license_plate_numbers wlpn
3183: where
3176: into l_count
3177: from wsh_delivery_assignments_v wda
3178: ,wsh_new_deliveries_ob_grp_v wnd
3179: ,wsh_delivery_details_ob_grp_v wdd
3180: ,wsh_delivery_details_ob_grp_v wdd2
3181: ,wms_license_plate_numbers lpn
3182: ,wms_license_plate_numbers wlpn
3183: where
3184: wdd.delivery_detail_id = wda.delivery_detail_id
3230: , wnd.name
3231: from wms_license_plate_numbers lpn
3232: , mtl_item_locations_kfv milk
3233: , wsh_delivery_assignments_v wda
3234: , wsh_delivery_details_ob_grp_v wdd
3235: , wsh_delivery_details_ob_grp_v wdd2
3236: , wsh_new_deliveries_ob_grp_v wnd
3237: , wms_license_plate_numbers wlpn
3238: where wdd.delivery_detail_id = wda.delivery_detail_id
3231: from wms_license_plate_numbers lpn
3232: , mtl_item_locations_kfv milk
3233: , wsh_delivery_assignments_v wda
3234: , wsh_delivery_details_ob_grp_v wdd
3235: , wsh_delivery_details_ob_grp_v wdd2
3236: , wsh_new_deliveries_ob_grp_v wnd
3237: , wms_license_plate_numbers wlpn
3238: where wdd.delivery_detail_id = wda.delivery_detail_id
3239: and wdd.released_status = 'Y'
3297: if( p_trip_id <> 0 ) then
3298: BEGIN
3299: select count(*)
3300: into l_count
3301: from wsh_delivery_details_ob_grp_v wdd
3302: , wsh_delivery_assignments_v wda
3303: , wsh_delivery_legs_ob_grp_v wdl
3304: , wsh_Trip_stops_ob_grp_v pickup_stop
3305: , mtl_system_items_kfv msik
3330: , wdd.inventory_item_id
3331: , wdd.requested_quantity
3332: , msik.concatenated_segments
3333: , msik.description
3334: from wsh_delivery_details_ob_grp_v wdd
3335: , wsh_delivery_assignments_v wda
3336: , wsh_new_deliveries_ob_grp_v wnd
3337: , wsh_delivery_legs_ob_grp_v wdl
3338: , wsh_trip_Stops_ob_grp_v pickup_stop
3353: else
3354: BEGIN
3355: select count(*)
3356: into l_count
3357: from wsh_delivery_details_ob_grp_v wdd
3358: , wsh_delivery_assignments_v wda
3359: , mtl_system_items_kfv msik
3360: where
3361: wda.delivery_detail_id = wdd.delivery_detail_id
3402: , wdd.inventory_item_id
3403: , wdd.requested_quantity
3404: , msiv.concatenated_segments
3405: , msiv.description
3406: from wsh_delivery_details_ob_grp_v wdd
3407: , wsh_delivery_assignments_v wda
3408: , mtl_system_items_vl msiv
3409: , wsh_new_deliveries_ob_grp_v wnd
3410: where wda.delivery_detail_id = wdd.delivery_detail_id
3452: unshipped_count NUMBER := 0;
3453: l_source_header_id NUMBER;
3454: CURSOR ship_set_in_trip(p_trip_id NUMBER) IS
3455: SELECT distinct wdd.ship_set_id
3456: FROM wsh_delivery_details_ob_grp_v wdd,wms_shipping_transaction_temp wstt
3457: WHERE wdd.delivery_detail_id = wstt.delivery_detail_id
3458: AND wdd.ship_set_id is not null
3459: AND wstt.organization_id = p_organization_id
3460: AND wstt.trip_id = p_trip_id
3462: AND nvl(wstt.direct_ship_flag,'N') = 'N';
3463:
3464: CURSOR delivered_ship_set IS
3465: SELECT distinct wdd.ship_set_id, wdd.source_header_id
3466: FROM wsh_delivery_details_ob_grp_v wdd,wms_shipping_transaction_temp wstt
3467: WHERE wdd.delivery_detail_id = wstt.delivery_detail_id
3468: AND wdd.ship_set_id is not null
3469: AND wstt.organization_id = p_organization_id
3470: AND wstt.dock_appoint_flag = 'N'
3473:
3474: CURSOR c_unshipped_count (p_ship_set_id NUMBER)
3475: IS
3476: SELECT 1
3477: FROM wsh_delivery_details_ob_grp_v wdd,
3478: oe_order_lines_all oel
3479: WHERE oel.ship_set_id = p_ship_set_id
3480: AND wdd.source_line_id = oel.line_id
3481: AND wdd.source_header_id = oel.header_id
3537: INTO unshipped_count
3538: FROM DUAL
3539: WHERE exists (
3540: SELECT 1
3541: FROM wsh_delivery_details_ob_grp_v wdd
3542: WHERE
3543: wdd.source_header_id = l_source_header_id
3544: AND wdd.ship_set_id = l_ship_set_id
3545: AND nvl(wdd.shipped_quantity,wdd.picked_quantity) is null
3604: x_return_status OUT NOCOPY VARCHAR2) IS
3605: /*
3606: cursor Delivery_Details(p_delivery_id NUMBER) is
3607: select wdd.delivery_detail_id
3608: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda
3609: where wda.delivery_id = p_delivery_id
3610: and wda.delivery_detail_id = wdd.delivery_detail_id
3611: and wdd.lpn_id is NULL;
3612: */
3693:
3694: CURSOR outer_lpn(p_delivery_id number) IS
3695: SELECT DISTINCT wda.parent_delivery_detail_id inner_lpn_wdd,
3696: wlpn.outermost_lpn_id outermost_lpn_id
3697: FROM wsh_delivery_details_ob_grp_v wdd,
3698: wsh_delivery_assignments_v wda,
3699: wms_license_plate_numbers wlpn
3700: WHERE wda.delivery_id = p_delivery_id
3701: AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
5353: ol.header_id,
5354: ol.line_id,
5355: wdd.released_status,
5356: mol.organization_id
5357: FROM wsh_delivery_details_ob_grp_v wdd,
5358: wsh_delivery_assignments_v wda,
5359: wsh_delivery_details_ob_grp_v wdd2,
5360: wms_license_plate_numbers lpn,
5361: mtl_txn_request_lines mol,
5355: wdd.released_status,
5356: mol.organization_id
5357: FROM wsh_delivery_details_ob_grp_v wdd,
5358: wsh_delivery_assignments_v wda,
5359: wsh_delivery_details_ob_grp_v wdd2,
5360: wms_license_plate_numbers lpn,
5361: mtl_txn_request_lines mol,
5362: oe_order_lines_all ol
5363: WHERE lpn.outermost_lpn_id = p_lpn_id
5759: END IF;
5760: BEGIN
5761: SELECT COUNT(*)
5762: INTO l_count
5763: FROM wsh_delivery_details_ob_grp_v wdd,
5764: wsh_delivery_assignments_v wda,
5765: wsh_new_deliveries_ob_grp_v wnd,
5766: wsh_delivery_legs_ob_grp_v wdl,
5767: wsh_trip_Stops_ob_grp_v pickup_stop,
5792: wdd.requested_quantity,
5793: wdd.requested_quantity_uom,
5794: msik.concatenated_segments,
5795: msik.description
5796: FROM wsh_delivery_details_ob_grp_v wdd,
5797: wsh_delivery_assignments_v wda,
5798: wsh_new_deliveries_ob_grp_v wnd,
5799: wsh_delivery_legs_ob_grp_v wdl,
5800: wsh_trip_stops_ob_grp_v pickup_stop,
5812: ELSE
5813: BEGIN
5814: SELECT COUNT(*)
5815: INTO l_count
5816: FROM wsh_delivery_details_ob_grp_v wdd,
5817: wsh_delivery_assignments_v wda,
5818: mtl_system_items_kfv
5819: msik, wsh_new_deliveries_ob_grp_v wnd
5820: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
5857: wdd.requested_quantity,
5858: wdd.requested_quantity_uom,
5859: msik.concatenated_segments,
5860: msik.description
5861: FROM wsh_delivery_details_ob_grp_v wdd,
5862: wsh_delivery_assignments_v wda,
5863: mtl_system_items_kfv
5864: msik, wsh_new_deliveries_ob_grp_v wnd
5865: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
5977: , wdd.source_header_type_id
5978: , c.party_name
5979: , hca.cust_account_id
5980: , c.party_number
5981: from wsh_delivery_details_ob_grp_v wdd
5982: --, R12 TCA changes ra_customers c -- added the following tables instead
5983: , hz_parties c , hz_cust_accounts hca
5984: , oe_transaction_types_tl otl
5985: , wms_direct_ship_temp wdst
6078: , wdd.src_requested_quantity2
6079: , wdd.REQUESTED_QUANTITY_UOM2
6080: , NVL(wds.secondary_processed_qty,0) secondary_processed_qty
6081: FROM oe_order_lines_all oel
6082: , wsh_delivery_details_ob_grp_v wdd
6083: , mtl_system_items_kfv msik
6084: , wms_direct_ship_temp wds
6085: WHERE oel.header_id =p_header_id
6086: and oel.ship_from_org_id = p_org_id
6182: , 0
6183: , wdd.REQUESTED_QUANTITY_UOM2
6184: , 0 secondary_processed_qty
6185: FROM oe_order_lines_all oel
6186: , wsh_delivery_details_ob_grp_v wdd
6187: , mtl_system_items_kfv msik
6188: , wms_direct_ship_temp wds
6189: WHERE oel.header_id =p_header_id
6190: and oel.ship_from_org_id = p_org_id
6293: , wdd.src_requested_quantity2
6294: , wdd.REQUESTED_QUANTITY_UOM2
6295: , NVL(wds.secondary_processed_qty,0) secondary_processed_qty
6296: FROM oe_order_lines_all oel
6297: , wsh_delivery_details_ob_grp_v wdd
6298: , mtl_system_items_kfv msik
6299: , wms_direct_ship_temp wds
6300: WHERE oel.header_id =p_header_id
6301: and oel.ship_from_org_id = p_org_id
6398: , 0
6399: , wdd.REQUESTED_QUANTITY_UOM2
6400: , 0 secondary_processed_qty
6401: FROM oe_order_lines_all oel
6402: , wsh_delivery_details_ob_grp_v wdd
6403: , mtl_system_items_kfv msik
6404: , wms_direct_ship_temp wds
6405: WHERE oel.header_id =p_header_id
6406: and oel.ship_from_org_id = p_org_id
6656: */
6657: FUNCTION get_container_name(p_container_name IN VARCHAR2) RETURN VARCHAR2 IS
6658:
6659: CURSOR l_container IS
6660: select container_name from wsh_delivery_details_ob_grp_v
6661: where container_name like p_container_name||'-@-%';
6662:
6663: l_num_part_str VARCHAR2(30);
6664: