[Home] [Help]
115: G_RET_STS_ERROR VARCHAR2(1) := FND_API.g_ret_sts_error;
116: G_RET_STS_UNEXP_ERROR VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
117: G_FALSE VARCHAR2(1) := FND_API.G_FALSE;
118: G_TRUE VARCHAR2(1) := FND_API.G_TRUE;
119: G_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
120:
121: PROCEDURE DEBUG(p_message IN VARCHAR2,
122: p_module IN VARCHAR2 ,
123: p_level IN VARCHAR2 DEFAULT 9) IS
120:
121: PROCEDURE DEBUG(p_message IN VARCHAR2,
122: p_module IN VARCHAR2 ,
123: p_level IN VARCHAR2 DEFAULT 9) IS
124: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
125: BEGIN
126:
127: IF (l_debug = 1) THEN
128: INV_TRX_UTIL_PUB.TRACE( P_MESG =>P_MESSAGE
176: END get_fulfillment_base;
177:
178: FUNCTION get_demand_src_type(p_order_header_id NUMBER)
179: RETURN NUMBER IS
180: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
181: l_order_source_id NUMBER;
182: BEGIN
183: IF NOT g_oe_header_type.EXISTS(p_order_header_id) THEN
184: SELECT order_source_id
204:
205: PROCEDURE GET_TRIPSTOP_INFO(x_tripstop_info OUT NOCOPY t_genref
206: ,p_trip_id IN NUMBER
207: ,p_org_id IN NUMBER) IS
208: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
209: BEGIN
210: /*Bug 2900813: Added code to fetch even Ship Method code and Enforce Ship Method Flag*/
211: OPEN x_tripstop_info FOR
212: SELECT wt.trip_id
233: PROCEDURE GET_DELIVERY_INFO(x_delivery_info OUT NOCOPY t_genref,
234: p_delivery_id IN NUMBER,
235: p_org_id IN NUMBER) IS
236:
237: l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
238: l_trip_id NUMBER;
239: l_trip_name wsh_trips.name%TYPE;
240:
241: BEGIN
298: END GET_DELIVERY_INFO;
299:
300: /* bug 11828449 */
301: FUNCTION check_direct_ship_for_lpn (p_lpn_id IN NUMBER) RETURN BOOLEAN IS
302: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
303: l_direct_ship VARCHAR(1) := 'N';
304:
305: BEGIN
306: IF (l_debug = 1) THEN
348: AND wstt.outermost_lpn_id = wlpn.lpn_id
349: AND wstt.direct_ship_flag = 'Y';
350: l_lpn_string VARCHAR2(30);
351: l_lpn_concat VARCHAR2(2000);
352: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
353: BEGIN
354: OPEN delivery_lpn;
355: LOOP
356: FETCH delivery_lpn into l_lpn_string;
367:
368: FUNCTION GET_SHIPMETHOD_MEANING(p_ship_method_code IN VARCHAR2)
369: RETURN VARCHAR2 IS
370: l_ship_method_meaning VARCHAR2(80);
371: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
372: BEGIN
373: IF p_ship_method_code IS NULL THEN
374: RETURN '';
375: ELSE
388:
389: FUNCTION GET_FOBLOC_CODE_MEANING(p_fob_code IN VARCHAR2)
390: RETURN VARCHAR2 IS
391: l_fob_loc_meaning VARCHAR2(80);
392: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
393: BEGIN
394: IF p_fob_code IS NULL THEN
395: RETURN '';
396: ELSE
410:
411: FUNCTION GET_FOB_LOCATION(p_fob_location_id IN NUMBER)
412: RETURN VARCHAR2 IS
413: l_fob_location VARCHAR2(80);
414: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
415: BEGIN
416: IF p_fob_location_id IS NULL THEN
417: RETURN '';
418: ELSE
429:
430: FUNCTION GET_FREIGHT_TERM(p_freight_term_code VARCHAR2)
431: RETURN VARCHAR2 IS
432: l_freight_term VARCHAR2(80);
433: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
434: BEGIN
435: IF p_freight_term_code IS NULL THEN
436: RETURN '';
437: ELSE
448:
449: FUNCTION GET_BOL(p_delivery_id NUMBER)
450: RETURN NUMBER IS
451: l_BOL NUMBER;
452: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
453: BEGIN
454: IF p_delivery_id IS NULL THEN
455: RETURN '';
456: ELSE
471: RETURN '';
472: END GET_BOL;
473:
474: FUNCTION get_enforce_ship RETURN VARCHAR2 IS
475: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
476: x_enforce_ship VARCHAR2(1);
477: x_return_status VARCHAR2(1);
478: x_param_info WSH_SHIPPING_PARAMS_GRP.Global_Params_Rec;
479: BEGIN
545: AND wstt.dock_door_id <> p_dock_door_id
546: AND milk.organization_id = p_org_id
547: AND milk.inventory_location_id =wstt.dock_door_id;
548:
549: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
550: BEGIN
551: IF (l_debug = 1) THEN
552: debug('In check deliveries','Check_Delivery');
553: debug('p_delivery_id : ' || p_delivery_id ,'Check_Delivery');
875: FROM WMS_FREIGHT_COST_TEMP
876: WHERE delivery_id = p_delivery_id
877: AND FREIGHT_COST_ID IS NULL
878: FOR UPDATE OF FREIGHT_COST_ID;
879: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
880: BEGIN
881: x_return_status := fnd_api.g_ret_sts_success;
882:
883: IF p_delivery_id IS NOT NULL THEN
1111: ,p_dock_door_id IN NUMBER
1112: ,p_organization_id IN NUMBER
1113: ) IS
1114: l_count NUMBER;
1115: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1116: BEGIN
1117: IF (l_debug = 1) THEN
1118: Debug( ' in missing_item_check', 'MISSING_ITEM_CUR');
1119: END IF;
1207: l_trip_shipmethod_code VARCHAR2(30);
1208: l_trip_shipmethod_meaning VARCHAR2(80);
1209:
1210:
1211: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1212:
1213: l_ignore_for_planning wsh_delivery_details.ignore_for_planning%type;
1214: l_tms_interface_flag wsh_new_deliveries.TMS_INTERFACE_FLAG%type;
1215: l_otm_trip_id wsh_trips.trip_id%type;
1647: AND ORGANIZATION_ID=P_ORG_ID
1648: AND DOCK_DOOR_ID=P_DOCK_DOOR_ID
1649: AND NVL(DIRECT_SHIP_FLAG,'N')='Y';
1650:
1651: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1652: BEGIN
1653:
1654: -- Call this API from Trip Info page of the Delivery Info. If thats the case
1655: -- then Delivery might have been changed. First update it then Shipconfirm all
1884: l_msg_data VARCHAR2(20000);
1885: l_out_trip_id VARCHAR2(30);
1886: l_out_trip_name VARCHAR2(30);
1887:
1888: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1889: BEGIN
1890: x_return_status:= fnd_api.g_ret_sts_success;
1891: wms_globals.g_ship_confirm_method := 'DIRECT';
1892: IF (l_debug = 1) THEN
2127: FROM wsh_trips_ob_grp_v
2128: WHERE trip_id = p_trip_id
2129: FOR UPDATE NOWAIT;
2130:
2131: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2132: BEGIN
2133: IF (l_debug = 1) THEN
2134: debug('Begin UPDATE_TRIPSTOP ','UPDATE_TRIPSTOP');
2135: debug('p_trip_id : ' || p_trip_id , 'UPDATE_TRIPSTOP');
2429: l_count1 NUMBER;
2430: l_count2 NUMBER;
2431: l_dummy VARCHAR2(20000);
2432: l_num NUMBER;
2433: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2434:
2435: BEGIN
2436: x_return_status := fnd_api.g_ret_sts_success;
2437:
2675: WHERE wstt.organization_id = p_org_id
2676: AND wstt.dock_door_id = p_dock_door_id
2677: AND wstt.trip_id = p_trip_id;
2678:
2679: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2680: BEGIN
2681: IF (l_debug = 1) THEN
2682: DEBUG('p_trip_id : ' || p_trip_id, 'close_trip');
2683: DEBUG('p_vehicle_item_id : ' || p_vehicle_item_id, 'close_trip');
3122: l_lpn_del_det_tab wsh_util_core.id_tab_type;
3123:
3124: l_freight_costs WSH_FREIGHT_COSTS_PUB.PubFreightCostRecType;
3125:
3126: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3127: l_lpn_tbl WMS_Data_Type_Definitions_PUB.LPNTableType;
3128: l_lpn_rec WMS_Data_Type_Definitions_PUB.LPNRecordType;
3129:
3130: cursor lpn_cur is
3567: WHERE organization_id = p_org_id
3568: AND trip_id = p_trip_id;
3569:
3570: l_outermost_lpn_id NUMBER;
3571: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3572: BEGIN
3573: x_return_status := fnd_api.g_ret_sts_success;
3574:
3575: IF p_trip_id IS NOT NULL THEN
3668: , x_last_upd_date OUT NOCOPY DATE
3669: , x_current_date OUT NOCOPY DATE
3670: ) IS
3671: x_progress VARCHAR2(3) := NULL;
3672: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3673: BEGIN
3674: x_progress := 10;
3675: x_userid := fnd_global.user_id;
3676: x_progress := 20;
3735: l_msg_count NUMBER;
3736: l_msg_data VARCHAR2(20000);
3737: l_trx_status_enabled NUMBER;
3738: l_lpn_status VARCHAR2(1) := 'Y';
3739: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3740: BEGIN
3741: x_return_status := fnd_api.g_ret_sts_success;
3742:
3743: SELECT NVL(status_control_flag,2)
3823: l_return_status VARCHAR2(1);
3824: l_msg_count NUMBER;
3825: l_msg_data VARCHAR2(20000);
3826: l_freight_cost_id NUMBER;
3827: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3828: BEGIN
3829: x_return_status := fnd_api.g_ret_sts_success;
3830: SAVEPOINT freight_cost;
3831: OPEN delivery_detail;
4136: l_succes NUMBER;
4137: l_tot_line_qty NUMBER;
4138: l_max_limit BOOLEAN;
4139: l_end_item_unit_number VARCHAR2(30);
4140: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
4141: l_proc_msg VARCHAR2(300);
4142: -- Bug No 3390432
4143: l_serial_number_str VARCHAR2(3000);
4144: l_cur_group_mark_id NUMBER;
4801: l_return_status VARCHAR2(1);
4802: l_msg_data VARCHAR2(3000);
4803: l_msg_count NUMBER;
4804: l_error_code VARCHAR2(20000);
4805: l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4806: l_shipping_attr WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
4807: l_shipping_attr_tab wsh_interface.changedattributetabtype;
4808: l_delivery_detail_id NUMBER;
4809: l_picked_qty NUMBER;
5374: l_action_prms wsh_interface_ext_grp.del_action_parameters_rectype;
5375: l_delivery_id_tab wsh_util_core.id_tab_type;
5376: l_delivery_out_rec wsh_interface_ext_grp.del_action_out_rec_type;
5377:
5378: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5379: -- Bug# 3464013: Replaced the static cursor with dynamic ref cursor
5380: l_sql_query VARCHAR2(10000);
5381: TYPE delivery_details_type IS REF CURSOR;
5382: delivery_details delivery_details_type;
7612: l_msg_count NUMBER;
7613: l_msg_data VARCHAR2(20000);
7614: l_trip_cursor trip_cursor%ROWTYPE;
7615: l_vehicle_item_id NUMBER; --Bug#6878013
7616: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
7617: l_ship_method_code wsh_trips.ship_method_code%TYPE;
7618: l_enforce_ship_method VARCHAR2(1);
7619: l_trip_name wsh_trips.NAME%TYPE;
7620: no_ship_method_code EXCEPTION;
7840: l_is_serial_control BOOLEAN := FALSE;
7841: l_inner_lpn inner_lpn%ROWTYPE;
7842: l_msg_data VARCHAR2(20000);
7843: l_demand_source_type_id NUMBER;
7844: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
7845: BEGIN
7846: x_return_status := fnd_api.g_ret_sts_success;
7847:
7848: IF (l_debug = 1) THEN
7999: l_container_new_name VARCHAR2(30);
8000: --Bug number:2701925:
8001: l_delivery_detail_id NUMBER;
8002: l_delivery_name VARCHAR2(30) := NULL;
8003: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
8004:
8005: -- Release 12 (K): LPN Synchronization/Convergence
8006: -- Types needed for WSH_WMS_LPN_GRP.Create_Update_Containers
8007: l_lpn_cur WMS_Data_Type_Definitions_PUB.LPNRecordType;
8287: l_return_status VARCHAR2(1);
8288: l_msg_data VARCHAR2(20000);
8289: l_msg_count NUMBER;
8290: x_error_code NUMBER;
8291: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
8292: BEGIN
8293: x_return_status := fnd_api.g_ret_sts_success;
8294:
8295: BEGIN
8479: l_par_del_det_tab wsh_util_core.id_tab_type;
8480:
8481: l_count NUMBER := 0;
8482: l_parent_counter NUMBER := 0;
8483: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
8484:
8485: l_dummy_number NUMBER;
8486: l_container_flag VARCHAR2(1);
8487:
8904: l_lpn_cur lpn_cur%ROWTYPE;
8905: l_par_del_det_id NUMBER;
8906: l_count_del_assign NUMBER;
8907: lpn_name VARCHAR2(30);
8908: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
8909: BEGIN
8910: x_return_status := fnd_api.g_ret_sts_success;
8911: x_error_code := NULL;
8912: OPEN lpn_cur;
8994: , x_return_status OUT NOCOPY VARCHAR2
8995: , x_missing_count OUT NOCOPY NUMBER
8996: ) IS
8997: l_count NUMBER;
8998: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
8999: BEGIN
9000: x_missing_count := 0;
9001: x_return_status := fnd_api.g_ret_sts_success;
9002:
9061: FROM wms_shipping_transaction_temp
9062: WHERE delivery_id = p_delivery_id
9063: AND direct_ship_flag = 'Y'));
9064:
9065: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
9066: BEGIN
9067: x_return_status := fnd_api.g_ret_sts_success;
9068:
9069: BEGIN
9179: l_total_resvd_qty NUMBER := 0 ;
9180: l_total_resvd_sec_qty NUMBER := 0 ;
9181: l_return_status VARCHAR2(30);
9182: l_line_set_id NUMBER ;
9183: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
9184: BEGIN
9185: x_return_status := fnd_api.g_ret_sts_success;
9186: x_prim_qty := 0;
9187: x_sec_qty := 0;
9402: TYPE skipped_line_tab IS TABLE OF NUMBER
9403: INDEX BY BINARY_INTEGER;
9404:
9405: l_skipped_line_tab skipped_line_tab;
9406: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
9407:
9408: -- Bug# 3464013: Replaced the static cursor with dynamic ref cursor
9409: l_sql_query VARCHAR2(10000);
9410: TYPE l_lpn_contents_cur_type IS REF CURSOR;
10665: l_upd_dummy_sn inv_reservation_global.serial_number_tbl_type;
10666: l_resv_id NUMBER;
10667: l_other_resv_qty NUMBER;
10668: l_other_resv_sec_qty NUMBER;
10669: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
10670: l_count_wdst NUMBER; --Bug#4546137.
10671: l_overship_case VARCHAR2(1) := 'N' ; --Bug#5262108
10672: l_qty_to_pick NUMBER; --Bug#5262108.
10673: l_sec_qty_to_pick NUMBER;
12018: AND source_line_id = p_line_id;
12019:
12020: l_del_detail_rec del_detail%ROWTYPE;
12021: l_count NUMBER := 0;
12022: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
12023: BEGIN
12024: IF (l_debug = 1) THEN
12025: DEBUG('p_line_processed= ' || p_line_processed, 'Validate_Del_Grp_Rules');
12026: DEBUG('p_header_id= ' || p_header_id, 'Validate_Del_Grp_Rules');
12135: l_return_status VARCHAR2(3);
12136: l_msg_count NUMBER;
12137: l_msg_data VARCHAR(20000);
12138: l_result BOOLEAN := TRUE;
12139: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
12140:
12141: l_chk_del_exc EXCEPTION;
12142:
12143: BEGIN
12275: l_return_status VARCHAR2(3);
12276: l_msg_count NUMBER;
12277: l_msg_data VARCHAR2(20000);
12278: l_error_code NUMBER;
12279: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
12280: BEGIN
12281: IF (l_debug = 1) THEN
12282: DEBUG('Procedure Load_LPN called with parameters', 'Load_LPN');
12283: DEBUG('p_org_id=' || p_org_id, 'Load_LPN');
12558: l_msg_count NUMBER;
12559: l_msg_data VARCHAR2(20000);
12560: l_error_code NUMBER;
12561: l_temp_count NUMBER;
12562: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
12563: --Added for bug 5262108
12564: l_allowed_flag VARCHAR2(1);
12565: l_max_quantity_allowed NUMBER;
12566: l_max_sec_qty_allowed NUMBER;
13571: l_delivery_detail_id NUMBER;
13572: l_return_status VARCHAR2(10);
13573: l_msg_count NUMBER;
13574: l_msg_data VARCHAR2(20000);
13575: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
13576: BEGIN
13577: IF (l_debug = 1) THEN
13578: DEBUG('In the check holds procedure', 'Check_holds');
13579: DEBUG('header id:' || p_order_header_id, 'Check_holds');
13653: l_lpn_tab t_num_tab;
13654: l_del_tab t_num_tab;
13655: l_trip_tab t_num_tab;
13656:
13657: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
13658: BEGIN
13659: OPEN c_wstt_closed_del;
13660: FETCH c_wstt_closed_del BULK COLLECT
13661: INTO l_lpn_tab, l_del_tab, l_trip_tab;
13716: RETURN BOOLEAN IS
13717: l_start_index NUMBER;
13718: l_end_index NUMBER;
13719: l_result BOOLEAN := FALSE;
13720: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
13721: BEGIN
13722: IF (l_debug = 1) THEN
13723: DEBUG('p_item_id=' || p_item_id, 'Validate_End_Unit_Num');
13724: DEBUG('p_end_unit_number=' || p_end_unit_number, 'Validate_End_Unit_Num');
13768:
13769: FUNCTION validate_end_unit_num_at(p_index IN NUMBER, p_end_unit_number IN VARCHAR2)
13770: RETURN BOOLEAN IS
13771: l_result BOOLEAN := FALSE;
13772: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
13773: BEGIN
13774: IF (l_debug = 1) THEN
13775: DEBUG('p_index=' || p_index, 'Validate_End_Unit_Num_At');
13776: DEBUG('p_end_unit_number=' || p_end_unit_number, 'Validate_End_Unit_Num_At');
13813: x_return_status OUT NOCOPY VARCHAR2,
13814: x_msg_count OUT NOCOPY NUMBER,
13815: x_msg_data OUT NOCOPY VARCHAR2) IS
13816:
13817: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
13818: l_ct_wt_enabled NUMBER := 0;
13819: BEGIN
13820: IF (l_debug = 1) THEN
13821: DEBUG('Value of lpn_id is = ' || p_lpn_id, 'is_ct_wt_enabled');