[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
1768:
1769: CURSOR delivery_details( p_outermost_lpn_id NUMBER) is
1770: SELECT wstt.delivery_detail_id, wstt.requested_quantity
1771: FROM wms_shipping_transaction_temp wstt
1772: , wsh_delivery_details wdd
1773: WHERE wstt.outermost_lpn_id = p_outermost_lpn_id
1774: AND wstt.delivery_detail_id = wdd.delivery_detail_id
1775: AND NVL(oe_dual_uom_util.get_fulfillment_base(wdd.source_line_id), 'P') = 'P';
1776:
1789: l_loaded_dock_door VARCHAR2(2000);
1790: l_delivery_count NUMBER;
1791: l_delivery_detail_ids VARCHAR2(1024);
1792:
1793: l_detail_attributes wsh_delivery_details_pub.ChangedAttributeTabType;
1794:
1795: l_index NUMBER := 1;
1796: l_return_status VARCHAR2(1);
1797: l_msg_count NUMBER;
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
1996: Debug('About to call Shipping API to update the shipped quantity','LPN_SUBMIT');
1997: END IF;
1998:
1999: IF l_detail_attributes.COUNT > 0 THEN
2000: wsh_delivery_details_pub.update_shipping_attributes
2001: (p_api_version_number => 1.0,
2002: p_init_msg_list => G_TRUE,
2003: p_commit => G_FALSE,
2004: x_return_status => l_return_status,
2008: p_source_code => 'OE');
2009:
2010: IF l_return_status <> G_RET_STS_SUCCESS THEN
2011: IF l_debug = 1 THEN
2012: debug('wsh_delivery_details_pub.update_shipping_attributes failed'
2013: || ' with status: ' || l_return_status, 'LPN_SUBMIT');
2014: debug('l_msg_count: ' || l_msg_count,'LPN_SUBMIT');
2015: debug('l_msg_data: ' || l_msg_data,'LPN_SUBMIT');
2016: END IF;
2117: * Added for g-log changes
2118: **********************************/
2119:
2120:
2121: l_ignore_for_planning wsh_delivery_details.ignore_for_planning%type;
2122: l_tms_interface_flag wsh_new_deliveries.TMS_INTERFACE_FLAG%TYPE;
2123:
2124:
2125: cursor delivery_for_trip is
2451: l_del_rows WSH_UTIL_CORE.ID_TAB_TYPE;
2452: l_delivery_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2453: l_open_del_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2454: l_new_del_id WSH_UTIL_CORE.ID_TAB_TYPE;
2455: l_del_detail_id WSH_DELIVERY_DETAILS_PUB.ID_TAB_TYPE;
2456: l_return_Status VARCHAR2(1);
2457: l_grouping_rows WSH_UTIL_CORE.ID_TAB_TYPE;
2458: l_del_index NUMBER;
2459: l_del_count NUMBER;
2551: IF (l_debug = 1) THEN
2552: DEBUG('The value of del_row is '||l_del_detail_id(1),'mob_ship');
2553: DEBUG('The value of del_id is '||l_open_del_ids(j),'mob_ship');
2554: END IF;
2555: WSH_DELIVERY_DETAILS_PUB.detail_to_delivery
2556: (p_api_version =>1.0
2557: ,x_return_status =>l_return_status
2558: ,x_msg_count =>l_msg_count
2559: ,x_msg_data =>l_msg_data
2596: IF (l_debug = 1) THEN
2597: DEBUG('Calling Auto Create Deliveries - ASSIGNMENT UNSUCCESSFUL','mob_ship');
2598: END IF;
2599:
2600: WSH_DELIVERY_DETAILS_PUB.autocreate_deliveries
2601: (p_api_version_number =>1.0
2602: , p_init_msg_list =>FND_API.G_FALSE
2603: , p_commit =>FND_API.G_FALSE
2604: , x_return_status =>l_return_status
2609: ELSIF l_open_del_ids.count =0 THEN
2610: IF (l_debug = 1) THEN
2611: DEBUG('cALLING aUTO CREATE DELIVIERIES -No open deliveries','mob_ship');
2612: END IF;
2613: WSH_DELIVERY_DETAILS_PUB.autocreate_deliveries
2614: (p_api_version_number =>1.0
2615: , p_init_msg_list =>FND_API.G_FALSE
2616: , p_commit =>FND_API.G_FALSE
2617: , x_return_status =>l_return_status
2835: l_msg_count NUMBER;
2836: l_msg_data VARCHAR2(2000);
2837: l_summary_buffer VARCHAR2(2000);
2838:
2839: l_ignore_for_planning wsh_delivery_details.ignore_for_planning%TYPE;
2840: l_tms_interface_flag wsh_new_deliveries.TMS_INTERFACE_FLAG%TYPE;
2841:
2842: CURSOR c_get_otm_flags is
2843: SELECT ignore_for_planning, tms_interface_flag
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
3557: -- Cases where the ship set is split within the same
3558: -- delivery will be caught by the missing lpn procedure.
3559: SELECT count(delivery_detail_id)
3560: INTO unshipped_count
3561: FROM wsh_delivery_details wdd
3562: WHERE wdd.released_status NOT IN ('C','X','D')
3563: AND NOT EXISTS (select 1
3564: FROM wms_shipping_transaction_temp wstt
3565: WHERE wstt.delivery_detail_id = wdd.delivery_detail_id)
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
5171: l_delivery_out_rec wsh_interface_ext_grp.del_action_out_rec_type;
5172: l_planned_flag VARCHAR2(1);
5173: /* Bug: 5585359: 10/09/06 - End*/
5174:
5175: l_delivery_details wsh_delivery_details_pub.id_tab_type;
5176: l_msg_count NUMBER;
5177: l_msg_data VARCHAR2(2000);
5178: BEGIN
5179: IF g_debug IS NULL THEN
5186: /* Bug: 5585359: 10/09/06 */
5187: l_delivery_id_tab(1) := p_delivery_id;
5188:
5189: IF l_debug = 1 THEN
5190: debug('Calling WSH_DELIVERY_DETAILS_PUB.detail_to_delivery ', 'UNASSIGN_DELIVERY_LINE');
5191: debug('delivery_detail_id : ' || p_delivery_detail_id, 'UNASSIGN_DELIVERY_LINE');
5192: debug('delivery_id : ' || p_delivery_id, 'UNASSIGN_DELIVERY_LINE');
5193: END IF;
5194: -- {{ Test a case where delivery exist before ship confirm }}
5219: BEGIN
5220: SELECT wnd.planned_flag
5221: INTO l_planned_flag
5222: FROM wsh_new_deliveries wnd,
5223: wsh_delivery_details wdd,
5224: wsh_delivery_assignments wda
5225: WHERE wdd.delivery_detail_id = l_delivery_details(1)
5226: AND wda.delivery_detail_id = wdd.delivery_detail_id
5227: AND wda.delivery_id = wnd.delivery_id;
5227: AND wda.delivery_id = wnd.delivery_id;
5228: EXCEPTION
5229: WHEN NO_DATA_FOUND THEN
5230: IF l_debug = 1 THEN
5231: debug('Calling WSH_DELIVERY_DETAILS_PUB.detail_to_delivery ', 'UNASSIGN_DELIVERY_LINE');
5232: END IF;
5233: null; -- It is OK
5234: END;
5235: END IF;
5267: debug('Unfirmed the Delivery !','UNASSIGN_DELIVERY_LINE');
5268: END IF;
5269: /* Bug: 5585359: 10/09/06 - End*/
5270:
5271: WSH_DELIVERY_DETAILS_PUB.detail_to_delivery
5272: (p_api_version => 1.0,
5273: p_init_msg_list => G_FALSE,
5274: p_commit => p_commit_flag,
5275: x_return_status => x_return_status,
5278: p_tabofdeldets => l_delivery_details,
5279: p_action => 'UNASSIGN');
5280:
5281: IF l_debug = 1 THEN
5282: debug('WSH_DELIVERY_DETAILS_PUB.detail_to_delivery '
5283: || 'return status: ' || x_return_status
5284: , 'UNASSIGN_DELIVERY_LINE');
5285:
5286: IF x_return_status <> 'S' THEN
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
5426: debug('Disassociate delivery detail with the old lpn, delivery detail id = '
5427: ||l_shipping_attr_tab(l_wdd_counter).delivery_detail_id, 'WMS_SHIPPING_TRANSACTION_PUB');
5428: END IF;
5429:
5430: WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_from_Cont
5431: ( P_DETAIL_ID => l_shipping_attr_tab(l_wdd_counter).delivery_detail_id,
5432: X_RETURN_STATUS => l_return_status );
5433: if (l_return_status <> fnd_api.g_ret_sts_success) then
5434: IF (l_debug = 1) THEN
5617: l_delivery_detail_id NUMBER;
5618: CURSOR delivery_details IS
5619: SELECT wstt.delivery_detail_id
5620: FROM wms_shipping_transaction_temp wstt
5621: , wsh_delivery_details wdd
5622: WHERE wstt.organization_id = p_organization_id
5623: AND nvl(wstt.direct_ship_flag,'N') = 'N'
5624: AND wstt.outermost_lpn_id = p_outermost_lpn_id
5625: AND wdd.delivery_detail_id = wstt.delivery_detail_id
5625: AND wdd.delivery_detail_id = wstt.delivery_detail_id
5626: AND NVL(oe_dual_uom_util.get_fulfillment_base(wdd.source_line_id), 'P') = 'P';
5627:
5628: l_index NUMBER;
5629: l_detail_attributes WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
5630: l_return_status VARCHAR2(1);
5631: l_msg_data VARCHAR2(2000);
5632: l_msg_count NUMBER;
5633:
5666: l_index := l_index + 1;
5667: END LOOP;
5668:
5669: IF l_debug = 1 THEN
5670: debug('About to call wsh_delivery_details_pub.update_shipping_attributes','LPN_UNLOAD');
5671: debug('l_detail_attributes count: ' || l_detail_attributes.COUNT,'LPN_UNLOAD');
5672: END IF;
5673:
5674: IF l_detail_attributes.COUNT > 0 THEN
5671: debug('l_detail_attributes count: ' || l_detail_attributes.COUNT,'LPN_UNLOAD');
5672: END IF;
5673:
5674: IF l_detail_attributes.COUNT > 0 THEN
5675: wsh_delivery_details_pub.update_shipping_attributes
5676: (p_api_version_number => 1.0,
5677: p_init_msg_list => G_TRUE,
5678: p_commit => G_FALSE,
5679: x_return_status => l_return_status,
5683: p_source_code => 'OE');
5684:
5685: IF l_return_status <> G_RET_STS_SUCCESS THEN
5686: IF l_debug = 1 THEN
5687: debug('wsh_delivery_details_pub.update_shipping_attributes failed'
5688: || ' with status: ' || l_return_status, 'LPN_UNLOAD');
5689: END IF;
5690: RAISE fnd_api.g_exc_unexpected_error;
5691: END IF;
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
6108: and lpc.inventory_item_id = oel.inventory_item_id
6109: )
6110: -- Bug# 4258360: Do not include order lines with crossdocked WDD records
6111: AND NOT EXISTS (SELECT 'xdock'
6112: FROM wsh_delivery_details wdd_xdock
6113: WHERE wdd_xdock.source_header_id = oel.header_id
6114: AND wdd_xdock.source_line_id = oel.line_id
6115: AND wdd_xdock.released_status = 'S'
6116: AND wdd_xdock.move_order_line_id IS NULL)
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
6195: and wdd.source_header_id = oel.header_id
6196: and wdd.source_line_id = oel.line_id
6197: and wdd.released_status in ('Y')
6198: and not exists (select 1
6199: from wsh_delivery_details wdd2
6200: where wdd.source_header_id =wdd2.source_header_id
6201: and wdd.source_line_id = wdd2.source_line_id
6202: and wdd2.released_status in ('B','X','R')
6203: )
6218: and lpc.inventory_item_id = oel.inventory_item_id
6219: )
6220: -- Bug# 4258360: Do not include order lines with crossdocked WDD records
6221: AND NOT EXISTS (SELECT 'xdock'
6222: FROM wsh_delivery_details wdd_xdock
6223: WHERE wdd_xdock.source_header_id = oel.header_id
6224: AND wdd_xdock.source_line_id = oel.line_id
6225: AND wdd_xdock.released_status = 'S'
6226: AND wdd_xdock.move_order_line_id IS NULL)
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
6324: )
6325: and NVL(oel.project_id,-1)=NVL(p_project_id,-1) and NVL(oel.task_id,-1)=NVL(p_task_id,-1)
6326: -- Bug# 4258360: Do not include order lines with crossdocked WDD records
6327: AND NOT EXISTS (SELECT 'xdock'
6328: FROM wsh_delivery_details wdd_xdock
6329: WHERE wdd_xdock.source_header_id = oel.header_id
6330: AND wdd_xdock.source_line_id = oel.line_id
6331: AND wdd_xdock.released_status = 'S'
6332: AND wdd_xdock.move_order_line_id IS NULL)
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
6411: and wdd.source_header_id = oel.header_id
6412: and wdd.source_line_id = oel.line_id
6413: and wdd.released_status in ('Y')
6414: and not exists (select 1
6415: from wsh_delivery_details wdd2
6416: where wdd.source_header_id =wdd2.source_header_id
6417: and wdd.source_line_id = wdd2.source_line_id
6418: and wdd2.released_status in ('B','X','R')
6419: )
6435: )
6436: and NVL(oel.project_id,-1)=NVL(p_project_id,-1) and NVL(oel.task_id,-1)=NVL(p_task_id,-1)
6437: -- Bug# 4258360: Do not include order lines with crossdocked WDD records
6438: AND NOT EXISTS (SELECT 'xdock'
6439: FROM wsh_delivery_details wdd_xdock
6440: WHERE wdd_xdock.source_header_id = oel.header_id
6441: AND wdd_xdock.source_line_id = oel.line_id
6442: AND wdd_xdock.released_status = 'S'
6443: AND wdd_xdock.move_order_line_id IS NULL)
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: