[Home] [Help]
106: G_RET_STS_ERROR VARCHAR2(1) := FND_API.g_ret_sts_error;
107: G_RET_STS_UNEXP_ERROR VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
108: G_FALSE VARCHAR2(1) := FND_API.G_FALSE;
109: G_TRUE VARCHAR2(1) := FND_API.G_TRUE;
110: G_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
111:
112: PROCEDURE DEBUG(p_message IN VARCHAR2,
113: p_module IN VARCHAR2 ,
114: p_level IN VARCHAR2 DEFAULT 9) IS
111:
112: PROCEDURE DEBUG(p_message IN VARCHAR2,
113: p_module IN VARCHAR2 ,
114: p_level IN VARCHAR2 DEFAULT 9) IS
115: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
116: BEGIN
117:
118: IF (l_debug = 1) THEN
119: INV_TRX_UTIL_PUB.TRACE( P_MESG =>P_MESSAGE
163:
164: PROCEDURE GET_TRIPSTOP_INFO(x_tripstop_info OUT NOCOPY t_genref
165: ,p_trip_id IN NUMBER
166: ,p_org_id IN NUMBER) IS
167: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
168: BEGIN
169: /*Bug 2900813: Added code to fetch even Ship Method code and Enforce Ship Method Flag*/
170: OPEN x_tripstop_info FOR
171: SELECT wt.trip_id
192: PROCEDURE GET_DELIVERY_INFO(x_delivery_info OUT NOCOPY t_genref,
193: p_delivery_id IN NUMBER,
194: p_org_id IN NUMBER) IS
195:
196: l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
197: l_trip_id NUMBER;
198: l_trip_name wsh_trips.name%TYPE;
199:
200: BEGIN
265: AND wstt.outermost_lpn_id = wlpn.lpn_id
266: AND wstt.direct_ship_flag = 'Y';
267: l_lpn_string VARCHAR2(30);
268: l_lpn_concat VARCHAR2(2000);
269: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
270: BEGIN
271: OPEN delivery_lpn;
272: LOOP
273: FETCH delivery_lpn into l_lpn_string;
284:
285: FUNCTION GET_SHIPMETHOD_MEANING(p_ship_method_code IN VARCHAR2)
286: RETURN VARCHAR2 IS
287: l_ship_method_meaning VARCHAR2(80);
288: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
289: BEGIN
290: IF p_ship_method_code IS NULL THEN
291: RETURN '';
292: ELSE
305:
306: FUNCTION GET_FOBLOC_CODE_MEANING(p_fob_code IN VARCHAR2)
307: RETURN VARCHAR2 IS
308: l_fob_loc_meaning VARCHAR2(80);
309: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
310: BEGIN
311: IF p_fob_code IS NULL THEN
312: RETURN '';
313: ELSE
327:
328: FUNCTION GET_FOB_LOCATION(p_fob_location_id IN NUMBER)
329: RETURN VARCHAR2 IS
330: l_fob_location VARCHAR2(80);
331: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
332: BEGIN
333: IF p_fob_location_id IS NULL THEN
334: RETURN '';
335: ELSE
346:
347: FUNCTION GET_FREIGHT_TERM(p_freight_term_code VARCHAR2)
348: RETURN VARCHAR2 IS
349: l_freight_term VARCHAR2(80);
350: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
351: BEGIN
352: IF p_freight_term_code IS NULL THEN
353: RETURN '';
354: ELSE
365:
366: FUNCTION GET_BOL(p_delivery_id NUMBER)
367: RETURN NUMBER IS
368: l_BOL NUMBER;
369: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
370: BEGIN
371: IF p_delivery_id IS NULL THEN
372: RETURN '';
373: ELSE
388: RETURN '';
389: END GET_BOL;
390:
391: FUNCTION get_enforce_ship RETURN VARCHAR2 IS
392: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
393: x_enforce_ship VARCHAR2(1);
394: x_return_status VARCHAR2(1);
395: x_param_info WSH_SHIPPING_PARAMS_GRP.Global_Params_Rec;
396: BEGIN
462: AND wstt.dock_door_id <> p_dock_door_id
463: AND milk.organization_id = p_org_id
464: AND milk.inventory_location_id =wstt.dock_door_id;
465:
466: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
467: BEGIN
468: IF (l_debug = 1) THEN
469: debug('In check deliveries','Check_Delivery');
470: debug('p_delivery_id : ' || p_delivery_id ,'Check_Delivery');
792: FROM WMS_FREIGHT_COST_TEMP
793: WHERE delivery_id = p_delivery_id
794: AND FREIGHT_COST_ID IS NULL
795: FOR UPDATE OF FREIGHT_COST_ID;
796: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
797: BEGIN
798: x_return_status := fnd_api.g_ret_sts_success;
799:
800: IF p_delivery_id IS NOT NULL THEN
1028: ,p_dock_door_id IN NUMBER
1029: ,p_organization_id IN NUMBER
1030: ) IS
1031: l_count NUMBER;
1032: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1033: BEGIN
1034: IF (l_debug = 1) THEN
1035: Debug( ' in missing_item_check', 'MISSING_ITEM_CUR');
1036: END IF;
1124: l_trip_shipmethod_code VARCHAR2(30);
1125: l_trip_shipmethod_meaning VARCHAR2(80);
1126:
1127:
1128: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1129:
1130: l_ignore_for_planning wsh_delivery_details.ignore_for_planning%type;
1131: l_tms_interface_flag wsh_new_deliveries.TMS_INTERFACE_FLAG%type;
1132: l_otm_trip_id wsh_trips.trip_id%type;
1552: AND ORGANIZATION_ID=P_ORG_ID
1553: AND DOCK_DOOR_ID=P_DOCK_DOOR_ID
1554: AND NVL(DIRECT_SHIP_FLAG,'N')='Y';
1555:
1556: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1557: BEGIN
1558:
1559: -- Call this API from Trip Info page of the Delivery Info. If thats the case
1560: -- then Delivery might have been changed. First update it then Shipconfirm all
1789: l_msg_data VARCHAR2(20000);
1790: l_out_trip_id VARCHAR2(30);
1791: l_out_trip_name VARCHAR2(30);
1792:
1793: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1794: BEGIN
1795: x_return_status:= fnd_api.g_ret_sts_success;
1796: wms_globals.g_ship_confirm_method := 'DIRECT';
1797: IF (l_debug = 1) THEN
2031: FROM wsh_trips_ob_grp_v
2032: WHERE trip_id = p_trip_id
2033: FOR UPDATE NOWAIT;
2034:
2035: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2036: BEGIN
2037: IF (l_debug = 1) THEN
2038: debug('Begin UPDATE_TRIPSTOP ','UPDATE_TRIPSTOP');
2039: debug('p_trip_id : ' || p_trip_id , 'UPDATE_TRIPSTOP');
2333: l_count1 NUMBER;
2334: l_count2 NUMBER;
2335: l_dummy VARCHAR2(20000);
2336: l_num NUMBER;
2337: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2338:
2339: BEGIN
2340: x_return_status := fnd_api.g_ret_sts_success;
2341:
2573: WHERE wstt.organization_id = p_org_id
2574: AND wstt.dock_door_id = p_dock_door_id
2575: AND wstt.trip_id = p_trip_id;
2576:
2577: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2578: BEGIN
2579: IF (l_debug = 1) THEN
2580: DEBUG('p_trip_id : ' || p_trip_id, 'close_trip');
2581: DEBUG('p_vehicle_item_id : ' || p_vehicle_item_id, 'close_trip');
2965: l_lpn_del_det_tab wsh_util_core.id_tab_type;
2966:
2967: l_freight_costs WSH_FREIGHT_COSTS_PUB.PubFreightCostRecType;
2968:
2969: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2970: l_lpn_tbl WMS_Data_Type_Definitions_PUB.LPNTableType;
2971: l_lpn_rec WMS_Data_Type_Definitions_PUB.LPNRecordType;
2972:
2973: cursor lpn_cur is
3377: WHERE organization_id = p_org_id
3378: AND trip_id = p_trip_id;
3379:
3380: l_outermost_lpn_id NUMBER;
3381: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3382: BEGIN
3383: x_return_status := fnd_api.g_ret_sts_success;
3384:
3385: IF p_trip_id IS NOT NULL THEN
3478: , x_last_upd_date OUT NOCOPY DATE
3479: , x_current_date OUT NOCOPY DATE
3480: ) IS
3481: x_progress VARCHAR2(3) := NULL;
3482: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3483: BEGIN
3484: x_progress := 10;
3485: x_userid := fnd_global.user_id;
3486: x_progress := 20;
3544: l_msg_count NUMBER;
3545: l_msg_data VARCHAR2(20000);
3546: l_trx_status_enabled NUMBER;
3547: l_lpn_status VARCHAR2(1) := 'Y';
3548: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3549: BEGIN
3550: x_return_status := fnd_api.g_ret_sts_success;
3551:
3552: SELECT status_control_flag
3621: l_return_status VARCHAR2(1);
3622: l_msg_count NUMBER;
3623: l_msg_data VARCHAR2(20000);
3624: l_freight_cost_id NUMBER;
3625: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3626: BEGIN
3627: x_return_status := fnd_api.g_ret_sts_success;
3628: SAVEPOINT freight_cost;
3629: OPEN delivery_detail;
3934: l_succes NUMBER;
3935: l_tot_line_qty NUMBER;
3936: l_max_limit BOOLEAN;
3937: l_end_item_unit_number VARCHAR2(30);
3938: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3939: l_proc_msg VARCHAR2(300);
3940: -- Bug No 3390432
3941: l_serial_number_str VARCHAR2(3000);
3942: l_cur_group_mark_id NUMBER;
4598: l_return_status VARCHAR2(1);
4599: l_msg_data VARCHAR2(3000);
4600: l_msg_count NUMBER;
4601: l_error_code VARCHAR2(20000);
4602: l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4603: l_transaction_temp_id NUMBER;
4604: l_shipping_attr WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
4605: l_shipping_attr_tab wsh_interface.changedattributetabtype;
4606: l_delivery_detail_id NUMBER;
5074: l_action_prms wsh_interface_ext_grp.del_action_parameters_rectype;
5075: l_delivery_id_tab wsh_util_core.id_tab_type;
5076: l_delivery_out_rec wsh_interface_ext_grp.del_action_out_rec_type;
5077:
5078: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5079: -- Bug# 3464013: Replaced the static cursor with dynamic ref cursor
5080: l_sql_query VARCHAR2(10000);
5081: TYPE delivery_details_type IS REF CURSOR;
5082: delivery_details delivery_details_type;
6505: l_msg_count NUMBER;
6506: l_msg_data VARCHAR2(20000);
6507: l_trip_cursor trip_cursor%ROWTYPE;
6508: l_vehicle_item_id NUMBER; --Bug#6878013
6509: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
6510: l_ship_method_code wsh_trips.ship_method_code%TYPE;
6511: l_enforce_ship_method VARCHAR2(1);
6512: l_trip_name wsh_trips.NAME%TYPE;
6513: no_ship_method_code EXCEPTION;
6734: l_inner_lpn inner_lpn%ROWTYPE;
6735: l_msg_data VARCHAR2(20000);
6736: l_order_source_id NUMBER;
6737: l_demand_source_type_id NUMBER;
6738: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
6739: BEGIN
6740: x_return_status := fnd_api.g_ret_sts_success;
6741:
6742: IF (l_debug = 1) THEN
6902: l_container_new_name VARCHAR2(30);
6903: --Bug number:2701925:
6904: l_delivery_detail_id NUMBER;
6905: l_delivery_name VARCHAR2(30) := NULL;
6906: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
6907:
6908: -- Release 12 (K): LPN Synchronization/Convergence
6909: -- Types needed for WSH_WMS_LPN_GRP.Create_Update_Containers
6910: l_lpn_cur WMS_Data_Type_Definitions_PUB.LPNRecordType;
7190: l_return_status VARCHAR2(1);
7191: l_msg_data VARCHAR2(20000);
7192: l_msg_count NUMBER;
7193: x_error_code NUMBER;
7194: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
7195: BEGIN
7196: x_return_status := fnd_api.g_ret_sts_success;
7197:
7198: BEGIN
7382: l_par_del_det_tab wsh_util_core.id_tab_type;
7383:
7384: l_count NUMBER := 0;
7385: l_parent_counter NUMBER := 0;
7386: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
7387:
7388: l_dummy_number NUMBER;
7389: l_container_flag VARCHAR2(1);
7390:
7807: l_lpn_cur lpn_cur%ROWTYPE;
7808: l_par_del_det_id NUMBER;
7809: l_count_del_assign NUMBER;
7810: lpn_name VARCHAR2(30);
7811: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
7812: BEGIN
7813: x_return_status := fnd_api.g_ret_sts_success;
7814: x_error_code := NULL;
7815: OPEN lpn_cur;
7897: , x_return_status OUT NOCOPY VARCHAR2
7898: , x_missing_count OUT NOCOPY NUMBER
7899: ) IS
7900: l_count NUMBER;
7901: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
7902: BEGIN
7903: x_missing_count := 0;
7904: x_return_status := fnd_api.g_ret_sts_success;
7905:
7965: FROM wms_shipping_transaction_temp
7966: WHERE delivery_id = p_delivery_id
7967: AND direct_ship_flag = 'Y'));
7968:
7969: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
7970: BEGIN
7971: x_return_status := fnd_api.g_ret_sts_success;
7972:
7973: BEGIN
8078: l_staged_qty NUMBER := 0 ;
8079: l_total_resvd_qty NUMBER := 0 ;
8080: l_return_status VARCHAR2(30);
8081: l_line_set_id NUMBER ;
8082: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
8083: BEGIN
8084:
8085: IF (l_debug = 1) THEN
8086: DEBUG('In Get_qty_to_pick p_order_header_id '|| p_order_header_id , 'Get_qty_to_pick');
8283: TYPE skipped_line_tab IS TABLE OF NUMBER
8284: INDEX BY BINARY_INTEGER;
8285:
8286: l_skipped_line_tab skipped_line_tab;
8287: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
8288:
8289: -- Bug# 3464013: Replaced the static cursor with dynamic ref cursor
8290: l_sql_query VARCHAR2(10000);
8291: TYPE l_lpn_contents_cur_type IS REF CURSOR;
9283: l_upd_dummy_sn inv_reservation_global.serial_number_tbl_type;
9284: l_chk_resv_qty NUMBER;
9285: l_resv_id NUMBER;
9286: l_other_resv_qty NUMBER;
9287: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
9288: l_count_wdst NUMBER; --Bug#4546137.
9289: l_overship_case VARCHAR2(1) := 'N' ; --Bug#5262108
9290: l_qty_to_pick NUMBER; --Bug#5262108.
9291: BEGIN
10271: AND source_line_id = p_line_id;
10272:
10273: l_del_detail_rec del_detail%ROWTYPE;
10274: l_count NUMBER := 0;
10275: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
10276: BEGIN
10277: IF (l_debug = 1) THEN
10278: DEBUG('p_line_processed= ' || p_line_processed, 'Validate_Del_Grp_Rules');
10279: DEBUG('p_header_id= ' || p_header_id, 'Validate_Del_Grp_Rules');
10382: l_return_status VARCHAR2(3);
10383: l_msg_count NUMBER;
10384: l_msg_data VARCHAR(20000);
10385: l_result BOOLEAN := TRUE;
10386: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
10387: BEGIN
10388: IF (l_debug = 1) THEN
10389: DEBUG('Checking delivery for direct ship', 'Validate_Del_For_DS');
10390: DEBUG('delivery id:'||l_del_cur_rec.delivery_id, 'Validate_Del_For_DS');
10494: l_return_status VARCHAR2(3);
10495: l_msg_count NUMBER;
10496: l_msg_data VARCHAR2(20000);
10497: l_error_code NUMBER;
10498: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
10499: BEGIN
10500: IF (l_debug = 1) THEN
10501: DEBUG('Procedure Load_LPN called with parameters', 'Load_LPN');
10502: DEBUG('p_org_id=' || p_org_id, 'Load_LPN');
10761: l_msg_count NUMBER;
10762: l_msg_data VARCHAR2(20000);
10763: l_error_code NUMBER;
10764: l_temp_count NUMBER;
10765: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
10766: --Added for bug 5262108
10767: l_allowed_flag VARCHAR2(1);
10768: l_max_quantity_allowed NUMBER;
10769: l_avail_req_quantity NUMBER;
11582: l_delivery_detail_id NUMBER;
11583: l_return_status VARCHAR2(10);
11584: l_msg_count NUMBER;
11585: l_msg_data VARCHAR2(20000);
11586: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
11587: BEGIN
11588: IF (l_debug = 1) THEN
11589: DEBUG('In the check holds procedure', 'Check_holds');
11590: DEBUG('header id:' || p_order_header_id, 'Check_holds');
11674: WHERE delivery_id = p_del_id);
11675:
11676: -- AND LPN_CONTEXT=1; As even during Ship Confirm the records werent gettng deleted
11677: --
11678: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
11679: BEGIN
11680: FOR l_wstt_del IN wstt_del LOOP
11681: FOR l_closed_del IN closed_del(l_wstt_del.delivery_id) LOOP
11682: FOR l_lpn_cur IN lpn_cur(l_closed_del.delivery_id) LOOP
11723: RETURN BOOLEAN IS
11724: l_start_index NUMBER;
11725: l_end_index NUMBER;
11726: l_result BOOLEAN := FALSE;
11727: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
11728: BEGIN
11729: IF (l_debug = 1) THEN
11730: DEBUG('p_item_id=' || p_item_id, 'Validate_End_Unit_Num');
11731: DEBUG('p_end_unit_number=' || p_end_unit_number, 'Validate_End_Unit_Num');
11775:
11776: FUNCTION validate_end_unit_num_at(p_index IN NUMBER, p_end_unit_number IN VARCHAR2)
11777: RETURN BOOLEAN IS
11778: l_result BOOLEAN := FALSE;
11779: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
11780: BEGIN
11781: IF (l_debug = 1) THEN
11782: DEBUG('p_index=' || p_index, 'Validate_End_Unit_Num_At');
11783: DEBUG('p_end_unit_number=' || p_end_unit_number, 'Validate_End_Unit_Num_At');