[Home] [Help]
1: PACKAGE BODY WSH_PICK_LIST AS
2: /* $Header: WSHPGSLB.pls 120.44.12010000.4 2008/09/15 13:02:51 gbhargav ship $ */
3:
4:
5: g_hash_base NUMBER := 1;
7:
8:
9: --
10: -- Package
11: -- WSH_PICK_LIST
12: --
13: -- Purpose
14: -- This package does the following:
15: -- - Generate selection list
47:
48: G_ONLINE_PICK_RELEASE_SKIP VARCHAR2(1) := 'N';
49: --
50:
51: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_PICK_LIST';
52:
53: g_demand_table demRecTabTyp;
54:
55: -- Global Variables
55: -- Global Variables
56: g_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
57: g_trolin_delivery_ids WSH_UTIL_CORE.Id_Tab_Type;
58: g_order_number NUMBER;
59: g_del_detail_ids WSH_PICK_LIST.DelDetTabTyp;
60: g_exp_pick_release_stat Inv_Express_Pick_Pub.P_Pick_Release_Status_Tbl;
61:
62: -- X-dock, declare package level global variables
63: g_allocation_method WSH_PICKING_BATCHES.ALLOCATION_METHOD%TYPE;
61:
62: -- X-dock, declare package level global variables
63: g_allocation_method WSH_PICKING_BATCHES.ALLOCATION_METHOD%TYPE;
64: g_xdock_delivery_ids WSH_UTIL_CORE.Id_Tab_Type; -- used for X-dock only
65: g_xdock_detail_ids WSH_PICK_LIST.DelDetTabTyp; -- used for X-dock only
66:
67: /**************************
68: -- X-dock
69: -- Description: This function is called from create_move_order_lines and
81: FUNCTION check_allocation_method RETURN VARCHAR2 IS
82:
83: BEGIN
84: IF g_valid_allocation IS NULL THEN
85: IF g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
86: WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
87: WSH_PICK_LIST.C_PRIORITIZE_INVENTORY) THEN
88: g_valid_allocation := 'Y';
89: ELSE
82:
83: BEGIN
84: IF g_valid_allocation IS NULL THEN
85: IF g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
86: WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
87: WSH_PICK_LIST.C_PRIORITIZE_INVENTORY) THEN
88: g_valid_allocation := 'Y';
89: ELSE
90: g_valid_allocation := 'N';
83: BEGIN
84: IF g_valid_allocation IS NULL THEN
85: IF g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
86: WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
87: WSH_PICK_LIST.C_PRIORITIZE_INVENTORY) THEN
88: g_valid_allocation := 'Y';
89: ELSE
90: g_valid_allocation := 'N';
91: END IF;
151: --
152: RETURN; -- back to calling API
153: END IF;
154:
155: WSH_PICK_LIST.G_BATCH_ID := p_batch_id;
156:
157: BEGIN --{
158: --Get the seeded document set id
159: SELECT WRS.REPORT_SET_ID
156:
157: BEGIN --{
158: --Get the seeded document set id
159: SELECT WRS.REPORT_SET_ID
160: INTO WSH_PICK_LIST.G_SEED_DOC_SET
161: FROM WSH_REPORT_SET_LINES WRSL,
162: WSH_REPORT_SETS WRS
163: WHERE WRS.NAME = 'Pick Slip Report'
164: AND WRS.REPORT_SET_ID = WRSL.REPORT_SET_ID ;
163: WHERE WRS.NAME = 'Pick Slip Report'
164: AND WRS.REPORT_SET_ID = WRSL.REPORT_SET_ID ;
165: --
166: IF l_debug_on THEN
167: WSH_DEBUG_SV.logmsg(l_module_name, 'SEEDED PICK RELEASE DOCUMENT SET IS '||WSH_PICK_LIST.G_SEED_DOC_SET );
168: END IF;
169: --
170: EXCEPTION
171: --
170: EXCEPTION
171: --
172: WHEN NO_DATA_FOUND THEN
173: --
174: WSH_PICK_LIST.G_SEED_DOC_SET := NULL;
175: --
176: IF l_debug_on THEN
177: WSH_DEBUG_SV.logmsg(l_module_name, 'SEEDED PICK RELEASE DOCUMENT SET NOT FOUND');
178: END IF;
178: END IF;
179: --
180: WHEN TOO_MANY_ROWS THEN
181: --
182: WSH_PICK_LIST.G_SEED_DOC_SET := NULL;
183: --
184: IF l_debug_on THEN
185: WSH_DEBUG_SV.logmsg(l_module_name, 'SEEDED PICK RELEASE DOCUMENT SET HAS > 1 REPORT. IGNORING...');
186: END IF;
219: -- x_return_status OUT NOCOPY Return Status.
220: -- End of comments
221: PROCEDURE Get_Org_Params(
222: p_organization_id IN NUMBER,
223: x_org_info OUT NOCOPY WSH_PICK_LIST.Org_Params_Rec,
224: x_return_status OUT NOCOPY VARCHAR2)
225: IS
226:
227: --Cursor to get inventory pick confirmed required flag.
655: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PR_PICK_SLIP_NUMBER.PRINT_PICK_SLIP'
656: ,WSH_DEBUG_SV.C_PROC_LEVEL);
657: END IF;
658: WSH_PR_PICK_SLIP_NUMBER.Print_Pick_Slip (
659: p_report_set_id => WSH_PICK_LIST.G_SEED_DOC_SET,
660: p_organization_id => p_organization_id,
661: p_order_header_id => WSH_PR_CRITERIA.g_order_header_id,
662: p_ps_mode => p_ps_mode,
663: p_batch_id => p_batch_id,
896: l_count_picked_lines NUMBER;
897: l_delv_id NUMBER;
898: l_wf_rs VARCHAR2(1); --Pick to POD WF Project
899:
900: l_del_detail_id WSH_PICK_LIST.DelDetTabTyp;
901: j NUMBER;
902:
903: --bug 7171766
904: l_match_found BOOLEAN;
901: j NUMBER;
902:
903: --bug 7171766
904: l_match_found BOOLEAN;
905: l_group_match_seq_tbl WSH_PICK_LIST.group_match_seq_tab_type;
906: K NUMBER ;
907:
908: BEGIN
909:
945: l_date := SYSDATE;
946:
947: -- X-dock
948: -- Call to WMS API to process lines for X-dock
949: IF g_allocation_method IN (WSH_PICK_LIST.C_CROSSDOCK_ONLY, WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK) THEN --{
950: IF l_debug_on THEN
951: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
952: WSH_DEBUG_SV.log(l_module_name,'Release Table Count',wsh_pr_criteria.release_table.count);
953: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit '||'WMS_XDOCK_PEGGING_PUB.PLANNED_CROSS_DOCK',WSH_DEBUG_SV.C_PROC_LEVEL);
1030: p_inventory_item_id => WSH_PR_CRITERIA.release_table(i).inventory_item_id,
1031: p_logged_at_location_id => WSH_PR_CRITERIA.release_table(i).ship_from_location_id,
1032: p_exception_location_id => WSH_PR_CRITERIA.release_table(i).ship_from_location_id,
1033: p_request_id => l_request_id,
1034: p_batch_id => WSH_PICK_LIST.g_batch_id,
1035: p_delivery_detail_id => WSH_PR_CRITERIA.release_table(i).delivery_detail_id,
1036: x_return_status => l_exception_return_status,
1037: x_msg_count => l_exception_msg_count,
1038: x_msg_data => l_exception_msg_data,
1050: -- end of X-dock related call
1051:
1052: -- If p_num_workers in Release_Batch api is 1 and WMS Org, then Generate Grouping Ids for the lines
1053: -- X-dock,cartonization is not applicable for X-dock only mode
1054: IF p_worker_id IS NULL AND WSH_PICK_LIST.G_NUM_WORKERS = 1 AND p_wms_flag = 'Y'
1055: AND WSH_PR_CRITERIA.release_table.count > 0
1056: AND g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
1057: WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
1058: WSH_PICK_LIST.C_PRIORITIZE_INVENTORY)
1052: -- If p_num_workers in Release_Batch api is 1 and WMS Org, then Generate Grouping Ids for the lines
1053: -- X-dock,cartonization is not applicable for X-dock only mode
1054: IF p_worker_id IS NULL AND WSH_PICK_LIST.G_NUM_WORKERS = 1 AND p_wms_flag = 'Y'
1055: AND WSH_PR_CRITERIA.release_table.count > 0
1056: AND g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
1057: WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
1058: WSH_PICK_LIST.C_PRIORITIZE_INVENTORY)
1059: THEN --{
1060: IF l_debug_on THEN
1053: -- X-dock,cartonization is not applicable for X-dock only mode
1054: IF p_worker_id IS NULL AND WSH_PICK_LIST.G_NUM_WORKERS = 1 AND p_wms_flag = 'Y'
1055: AND WSH_PR_CRITERIA.release_table.count > 0
1056: AND g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
1057: WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
1058: WSH_PICK_LIST.C_PRIORITIZE_INVENTORY)
1059: THEN --{
1060: IF l_debug_on THEN
1061: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1054: IF p_worker_id IS NULL AND WSH_PICK_LIST.G_NUM_WORKERS = 1 AND p_wms_flag = 'Y'
1055: AND WSH_PR_CRITERIA.release_table.count > 0
1056: AND g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
1057: WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
1058: WSH_PICK_LIST.C_PRIORITIZE_INVENTORY)
1059: THEN --{
1060: IF l_debug_on THEN
1061: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1062: END IF;
1099:
1100: -- X-dock
1101: -- Populate trolin_tbl only for these allocation methods, this call is not
1102: -- required for X-dock only
1103: IF g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
1104: WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
1105: WSH_PICK_LIST.C_PRIORITIZE_INVENTORY) THEN
1106: --{
1107:
1100: -- X-dock
1101: -- Populate trolin_tbl only for these allocation methods, this call is not
1102: -- required for X-dock only
1103: IF g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
1104: WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
1105: WSH_PICK_LIST.C_PRIORITIZE_INVENTORY) THEN
1106: --{
1107:
1108: -- Print before the loop
1101: -- Populate trolin_tbl only for these allocation methods, this call is not
1102: -- required for X-dock only
1103: IF g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
1104: WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
1105: WSH_PICK_LIST.C_PRIORITIZE_INVENTORY) THEN
1106: --{
1107:
1108: -- Print before the loop
1109: IF l_debug_on THEN
1167: g_trolin_tbl(l_count).to_subinventory_code := p_to_subinventory;
1168: g_trolin_tbl(l_count).to_locator_id := p_to_locator;
1169:
1170: -- If p_num_workers in Release_Batch api is 1 and WMS Org, then populate Carton Grouping Ids for the lines
1171: IF p_worker_id IS NULL AND WSH_PICK_LIST.G_NUM_WORKERS = 1 AND p_wms_flag = 'Y' THEN
1172: --{
1173: --bug 7171766
1174: l_match_found :=FALSE;
1175:
1455: WSH_DEBUG_SV.logmsg(l_module_name,'============');
1456: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Inv_Express_Pick_Pub.Pick_Release with COUNT:'||G_TROLIN_TBL.COUNT );
1457: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Inv_Express_Pick_Pub.Pick_Release',WSH_DEBUG_SV.C_PROC_LEVEL);
1458: END IF;
1459: WSH_PICK_LIST.G_AUTO_PICK_CONFIRM := 'Y';
1460: Inv_Express_Pick_Pub.Pick_Release (
1461: p_api_version => l_api_version_number,
1462: p_init_msg_list => FND_API.G_TRUE,
1463: p_commit => l_commit,
1529: p_inventory_item_id => WSH_PR_CRITERIA.release_table(i).inventory_item_id,
1530: p_logged_at_location_id => WSH_PR_CRITERIA.release_table(i).ship_from_location_id,
1531: p_exception_location_id => WSH_PR_CRITERIA.release_table(i).ship_from_location_id,
1532: p_request_id => l_request_id,
1533: p_batch_id => WSH_PICK_LIST.g_batch_id,
1534: x_return_status => l_exception_return_status,
1535: x_msg_count => l_exception_msg_count,
1536: x_msg_data => l_exception_msg_data,
1537: x_exception_id => l_dummy_exception_id);
2390: --
2391: l_done_flag VARCHAR2(1);
2392: l_pick_release_stat INV_PICK_RELEASE_PUB.INV_Release_Status_Tbl_Type;
2393: l_mmtt_tbl INV_MO_LINE_DETAIL_UTIL.G_MMTT_Tbl_Type;
2394: l_org_info WSH_PICK_LIST.Org_Params_Rec;
2395: l_tmp_rel_status VARCHAR2(1);
2396: --
2397: l_auto_pick_confirm_num NUMBER;
2398: l_plan_tasks BOOLEAN;
2437: END IF;
2438:
2439: -- If worker_id is present, set Global variable as Parallel Pick Release and Parallel Pick Slips
2440: IF p_worker_id IS NOT NULL THEN
2441: WSH_PICK_LIST.G_PICK_REL_PARALLEL := TRUE;
2442: IF l_debug_on THEN
2443: WSH_DEBUG_SV.logmsg(l_module_name,'Pick Release Mode is Parallel ');
2444: END IF;
2445: END IF;
2605:
2606: -- 10.20.40.20 Calling Move Order Line API
2607: --{
2608: IF l_debug_on THEN
2609: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICK_LIST.Create_Move_Order_Lines'
2610: ,WSH_DEBUG_SV.C_PROC_LEVEL);
2611: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
2612: END IF;
2613: Create_Move_Order_Lines (
2625: x_return_status => l_return_status
2626: );
2627: IF l_debug_on THEN
2628: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
2629: WSH_DEBUG_SV.log(l_module_name,'WSH_PICK_LIST.Create_Move_Order_Lines l_return_status' ,l_return_status);
2630: END IF;
2631: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2632: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
2633: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_PICK_LIST.Create_Move_Order_Lines');
2629: WSH_DEBUG_SV.log(l_module_name,'WSH_PICK_LIST.Create_Move_Order_Lines l_return_status' ,l_return_status);
2630: END IF;
2631: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2632: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
2633: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_PICK_LIST.Create_Move_Order_Lines');
2634: RAISE e_return;
2635: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2636: IF l_completion_status = 'NORMAL' THEN
2637: l_completion_status := 'WARNING';
2649: -- and if Pick Slip Printing Mode is Immediate or Delivery is part of Pick Slip Grouping Rule
2650: -- 3) If it is not worker and parameter p_num_workers in Release_Batch API = 1
2651: --{
2652: IF (p_worker_id IS NOT NULL AND (WSH_PR_CRITERIA.g_use_delivery_ps = 'Y' OR l_org_info.print_pick_slip_mode = 'I')) OR
2653: (p_worker_id IS NULL AND WSH_PICK_LIST.G_NUM_WORKERS > 1 AND (WSH_PR_CRITERIA.g_use_delivery_ps = 'Y'
2654: OR l_org_info.print_pick_slip_mode = 'I')) OR
2655: (p_worker_id IS NULL AND WSH_PICK_LIST.G_NUM_WORKERS = 1) THEN
2656: IF ((l_org_info.autocreate_deliveries = 'Y') AND (WSH_PR_CRITERIA.g_trip_id = 0)
2657: AND (WSH_PR_CRITERIA.g_delivery_id = 0) AND (WSH_PR_CRITERIA.release_table.count > 0))THEN
2651: --{
2652: IF (p_worker_id IS NOT NULL AND (WSH_PR_CRITERIA.g_use_delivery_ps = 'Y' OR l_org_info.print_pick_slip_mode = 'I')) OR
2653: (p_worker_id IS NULL AND WSH_PICK_LIST.G_NUM_WORKERS > 1 AND (WSH_PR_CRITERIA.g_use_delivery_ps = 'Y'
2654: OR l_org_info.print_pick_slip_mode = 'I')) OR
2655: (p_worker_id IS NULL AND WSH_PICK_LIST.G_NUM_WORKERS = 1) THEN
2656: IF ((l_org_info.autocreate_deliveries = 'Y') AND (WSH_PR_CRITERIA.g_trip_id = 0)
2657: AND (WSH_PR_CRITERIA.g_delivery_id = 0) AND (WSH_PR_CRITERIA.release_table.count > 0))THEN
2658: --{
2659: IF l_debug_on THEN
2729: END LOOP;
2730: --}
2731: END IF;
2732: IF l_debug_on THEN
2733: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICK_LIST.Autocreate_Deliveries'
2734: ,WSH_DEBUG_SV.C_PROC_LEVEL);
2735: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
2736: END IF;
2737: Autocreate_Deliveries (
2741: x_return_status => l_return_status
2742: );
2743: IF l_debug_on THEN
2744: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
2745: WSH_DEBUG_SV.log(l_module_name,'WSH_PICK_LIST.Autocreate_Deliveries l_return_status'
2746: ,l_return_status);
2747: END IF;
2748: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2749: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
2746: ,l_return_status);
2747: END IF;
2748: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2749: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
2750: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_PICK_LIST.Autocreate_Deliveries');
2751: RAISE e_return;
2752: END IF;
2753: END IF;
2754: --}
2803: END IF;
2804:
2805: -- If Parallel process, then Cartonization is done in Parent Process
2806: -- Else done here
2807: IF WSH_PICK_LIST.G_PICK_REL_PARALLEL THEN
2808: l_skip_cartonization := TRUE;
2809: l_plan_tasks := TRUE;
2810: ELSE
2811: l_skip_cartonization := FALSE;
2920: p_inventory_item_id => WSH_PR_CRITERIA.release_table(i).inventory_item_id,
2921: p_logged_at_location_id => WSH_PR_CRITERIA.release_table(i).ship_from_location_id,
2922: p_exception_location_id => WSH_PR_CRITERIA.release_table(i).ship_from_location_id,
2923: p_request_id => l_request_id,
2924: p_batch_id => WSH_PICK_LIST.g_batch_id,
2925: x_return_status => l_exception_return_status,
2926: x_msg_count => l_exception_msg_count,
2927: x_msg_data => l_exception_msg_data,
2928: x_exception_id => l_dummy_exception_id);
3014:
3015: -- If Pick Slip Print Mode is Immediate for Non-WMS Orgs
3016: -- OR Pick Slip Print Mode is Immediate and Cartonization is done before Allocation for WMS Orgs
3017: IF (l_org_info.print_pick_slip_mode = 'I') AND (l_org_info.wms_org = 'N' OR
3018: (l_org_info.wms_org = 'Y' AND p_worker_id IS NULL AND WSH_PICK_LIST.G_NUM_WORKERS = 1)) THEN
3019: --{
3020: IF (WSH_PR_PICK_SLIP_NUMBER.g_print_ps_table.COUNT > 0) THEN
3021: --{
3022: IF l_debug_on THEN
3031: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PR_PICK_SLIP_NUMBER.PRINT_PICK_SLIP',WSH_DEBUG_SV.C_PROC_LEVEL);
3032: END IF;
3033: WSH_PR_PICK_SLIP_NUMBER.Print_Pick_Slip(
3034: p_pick_slip_number => WSH_PR_PICK_SLIP_NUMBER.g_print_ps_table(i),
3035: p_report_set_id => WSH_PICK_LIST.G_SEED_DOC_SET,
3036: p_organization_id => batch_rec.organization_id,
3037: p_ps_mode => 'I',
3038: x_api_status => l_return_status,
3039: x_error_message => l_message );
3078: -- Auto Pick Confirm
3079: IF l_org_info.express_pick_flag = 'N' AND l_org_info.auto_pick_confirm = 'Y'
3080: AND l_pick_release_stat.count > 0 THEN
3081: --{
3082: WSH_PICK_LIST.G_AUTO_PICK_CONFIRM := 'Y';
3083: --{
3084: IF l_debug_on THEN
3085: WSH_DEBUG_SV.logmsg(l_module_name, '=================' );
3086: WSH_DEBUG_SV.logmsg(l_module_name, 'AUTO PICK CONFIRM' );
3187: IF l_total_detailed_count > 0 THEN
3188:
3189: UPDATE WSH_PR_WORKERS
3190: SET detailed_count = detailed_count + l_total_detailed_count
3191: WHERE batch_id = WSH_PICK_LIST.G_BATCH_ID
3192: AND organization_id = batch_rec.organization_id
3193: AND type = 'DOC';
3194:
3195: END IF;
3206: organization_id,
3207: printer_name
3208: )
3209: SELECT
3210: WSH_PICK_LIST.G_BATCH_ID,
3211: 'PRINTER',
3212: batch_rec.organization_id,
3213: WSH_INV_INTEGRATION_GRP.G_PRINTERTAB(i)
3214: FROM dual
3214: FROM dual
3215: WHERE NOT EXISTS (
3216: SELECT 'x'
3217: FROM wsh_pr_workers
3218: WHERE batch_id = WSH_PICK_LIST.G_BATCH_ID
3219: AND type = 'PRINTER'
3220: AND organization_id = batch_rec.organization_id
3221: AND printer_name = WSH_INV_INTEGRATION_GRP.G_PRINTERTAB(i));
3222: END IF;
3453: ROLLBACK TO s_Release_Batch_Sub_sf;
3454: END IF;
3455:
3456: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
3457: WSH_UTIL_CORE.PrintMsg('Exception occurred in WSH_PICK_LIST.Release_Batch_Sub');
3458: IF G_CONC_REQ = FND_API.G_TRUE THEN
3459: -- Set the completion status only in the case of Child Request
3460: -- If this is done as part of Parent Process, this setting will cause program to stop without doing
3461: -- any further processing
3461: -- any further processing
3462: IF p_worker_id is not null then
3463: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
3464: END IF;
3465: errbuf := 'Exception occurred in WSH_PICK_LIST';
3466: retcode := '2';
3467: END IF;
3468: G_ONLINE_PICK_RELEASE_RESULT := 'F';
3469: IF l_debug_on THEN
3477: ROLLBACK TO s_Release_Batch_Sub_sf;
3478: END IF;
3479:
3480: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
3481: WSH_UTIL_CORE.PrintMsg('Exception occurred in WSH_PICK_LIST.Release_Batch_Sub');
3482: IF G_CONC_REQ = FND_API.G_TRUE THEN
3483: -- Set the completion status only in the case of Child Request
3484: -- If this is done as part of Parent Process, this setting will cause program to stop without doing
3485: -- any further processing
3485: -- any further processing
3486: IF p_worker_id is not null then
3487: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
3488: END IF;
3489: errbuf := 'Exception occurred in WSH_PICK_LIST';
3490: retcode := '2';
3491: END IF;
3492: G_ONLINE_PICK_RELEASE_RESULT := 'F';
3493: IF l_debug_on THEN
4012: l_tot_dd_records NUMBER;
4013: l_worker_records NUMBER;
4014: l_smc_records NUMBER;
4015: l_dd_records NUMBER;
4016: l_org_info WSH_PICK_LIST.Org_Params_Rec;
4017: l_org_info_tbl WSH_PICK_LIST.Org_Params_Rec_Tbl;
4018: l_detail_cfetch NUMBER;
4019: l_detail_pfetch NUMBER;
4020: l_del_details_tbl WSH_UTIL_CORE.Id_Tab_Type;
4013: l_worker_records NUMBER;
4014: l_smc_records NUMBER;
4015: l_dd_records NUMBER;
4016: l_org_info WSH_PICK_LIST.Org_Params_Rec;
4017: l_org_info_tbl WSH_PICK_LIST.Org_Params_Rec_Tbl;
4018: l_detail_cfetch NUMBER;
4019: l_detail_pfetch NUMBER;
4020: l_del_details_tbl WSH_UTIL_CORE.Id_Tab_Type;
4021: l_mo_lines_tbl INV_MOVE_ORDER_PUB.Num_Tbl_Type;
4056: l_errbuf VARCHAR2(2000);
4057:
4058: --bug 7171766
4059: l_match_found BOOLEAN;
4060: l_group_match_seq_tbl WSH_PICK_LIST.group_match_seq_tab_type;
4061: K NUMBER ;
4062:
4063:
4064: BEGIN
4939: END IF;
4940:
4941: -- Autocreate Deliveries only if G_NUM_WORKERS > 1
4942: -- Otherwise it would have been done before Allocation in Release_Batch_Sub
4943: IF WSH_PICK_LIST.G_NUM_WORKERS > 1 THEN --{
4944:
4945: -- Call Autocreate Deliveries for the Batch
4946: -- Loop thru all Organization - Details that are not assigned to any deliveries
4947: IF l_debug_on THEN
4974: FETCH c_get_unassigned_details BULK COLLECT INTO l_del_details_tbl LIMIT l_pr_batch_size;
4975: l_detail_cfetch := c_get_unassigned_details%ROWCOUNT - l_detail_pfetch;
4976: EXIT WHEN (l_detail_cfetch = 0);
4977: IF l_debug_on THEN
4978: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICK_LIST.Autocreate_Deliveries'
4979: ,WSH_DEBUG_SV.C_PROC_LEVEL);
4980: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
4981: END IF;
4982: Autocreate_Deliveries (
4986: x_return_status => l_return_status
4987: );
4988: IF l_debug_on THEN
4989: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
4990: WSH_DEBUG_SV.log(l_module_name,'WSH_PICK_LIST.Autocreate_Deliveries l_return_status'
4991: ,l_return_status);
4992: END IF;
4993: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
4994: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
4991: ,l_return_status);
4992: END IF;
4993: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
4994: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
4995: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_PICK_LIST.Autocreate_Deliveries');
4996: RAISE e_return;
4997: END IF;
4998:
4999: l_detail_pfetch := c_get_unassigned_details%ROWCOUNT;
5163: COMMIT;
5164: END IF;
5165:
5166: -- WMS Cartonization is done only if p_num_workers in Release_Batch is > 1
5167: IF ( WSH_PICK_LIST.G_NUM_WORKERS > 1 )
5168: -- X-dock
5169: -- Need to add check to ensure delivery detail is not X-docked
5170: -- either MOL is null or MOL is not null and move order type = PUTAWAY
5171: AND g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
5167: IF ( WSH_PICK_LIST.G_NUM_WORKERS > 1 )
5168: -- X-dock
5169: -- Need to add check to ensure delivery detail is not X-docked
5170: -- either MOL is null or MOL is not null and move order type = PUTAWAY
5171: AND g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
5172: WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
5173: WSH_PICK_LIST.C_PRIORITIZE_INVENTORY) THEN
5174: --{
5175: FOR crec IN c_wms_orgs(p_batch_id) LOOP --{
5168: -- X-dock
5169: -- Need to add check to ensure delivery detail is not X-docked
5170: -- either MOL is null or MOL is not null and move order type = PUTAWAY
5171: AND g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
5172: WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
5173: WSH_PICK_LIST.C_PRIORITIZE_INVENTORY) THEN
5174: --{
5175: FOR crec IN c_wms_orgs(p_batch_id) LOOP --{
5176: IF l_org_info_tbl(crec.organization_id).auto_pick_confirm = 'Y' THEN
5169: -- Need to add check to ensure delivery detail is not X-docked
5170: -- either MOL is null or MOL is not null and move order type = PUTAWAY
5171: AND g_allocation_method IN (WSH_PICK_LIST.C_INVENTORY_ONLY,
5172: WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
5173: WSH_PICK_LIST.C_PRIORITIZE_INVENTORY) THEN
5174: --{
5175: FOR crec IN c_wms_orgs(p_batch_id) LOOP --{
5176: IF l_org_info_tbl(crec.organization_id).auto_pick_confirm = 'Y' THEN
5177: IF l_debug_on THEN
5325: FOR crec in c_sum_worker(p_batch_id) LOOP --{
5326: IF l_debug_on THEN
5327: WSH_DEBUG_SV.logmsg(l_module_name,'Printing Documents for Organization '||crec.organization_id);
5328: WSH_DEBUG_SV.logmsg(l_module_name, 'TOTAL DETAILED COUNT IS '||crec.tot_detailed);
5329: WSH_DEBUG_SV.logmsg(l_module_name, 'Seeded Pick Slip Document Set '||WSH_PICK_LIST.G_SEED_DOC_SET);
5330: WSH_DEBUG_SV.logmsg(l_module_name, 'Pick Release Document Set '||l_org_info_tbl(crec.organization_id).doc_set_id);
5331: END IF;
5332: l_print_ps := 'N';
5333: IF crec.tot_detailed > 0 AND WSH_PICK_LIST.G_SEED_DOC_SET IS NOT NULL AND
5329: WSH_DEBUG_SV.logmsg(l_module_name, 'Seeded Pick Slip Document Set '||WSH_PICK_LIST.G_SEED_DOC_SET);
5330: WSH_DEBUG_SV.logmsg(l_module_name, 'Pick Release Document Set '||l_org_info_tbl(crec.organization_id).doc_set_id);
5331: END IF;
5332: l_print_ps := 'N';
5333: IF crec.tot_detailed > 0 AND WSH_PICK_LIST.G_SEED_DOC_SET IS NOT NULL AND
5334: l_org_info_tbl(crec.organization_id).express_pick_flag = 'N' AND
5335: l_org_info_tbl(crec.organization_id).autodetail_flag = 'Y' THEN
5336: l_print_ps := 'Y';
5337: END IF;
5336: l_print_ps := 'Y';
5337: END IF;
5338: IF (l_print_ps = 'Y') OR (l_org_info_tbl(crec.organization_id).doc_set_id <> -1) THEN
5339: IF l_debug_on THEN
5340: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICK_LIST.Print_Docs'
5341: ,WSH_DEBUG_SV.C_PROC_LEVEL);
5342: END IF;
5343: Print_docs (
5344: p_batch_id => p_batch_id,
6294: ROLLBACK TO s_Release_Batch_sp;
6295: END IF;
6296:
6297: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
6298: WSH_UTIL_CORE.PrintMsg('Exception occurred in WSH_PICK_LIST');
6299: IF get_orgs%ISOPEN THEN
6300: CLOSE get_orgs;
6301: END IF;
6302: IF get_pack_ship_groups%ISOPEN THEN
6363: CLOSE c_requests;
6364: END IF;
6365: IF G_CONC_REQ = FND_API.G_TRUE THEN
6366: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
6367: errbuf := 'Exception occurred in WSH_PICK_LIST';
6368: retcode := '2';
6369: END IF;
6370: G_ONLINE_PICK_RELEASE_RESULT := 'F';
6371: IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
6388: WHEN OTHERS THEN
6389: --
6390: ROLLBACK;
6391: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
6392: WSH_UTIL_CORE.PrintMsg('Exception occurred in WSH_PICK_LIST');
6393: IF get_orgs%ISOPEN THEN
6394: CLOSE get_orgs;
6395: END IF;
6396: IF get_pack_ship_groups%ISOPEN THEN
6457: CLOSE c_requests;
6458: END IF;
6459: IF G_CONC_REQ = FND_API.G_TRUE THEN
6460: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
6461: errbuf := 'Exception occurred in WSH_PICK_LIST';
6462: retcode := '2';
6463: END IF;
6464: G_ONLINE_PICK_RELEASE_RESULT := 'F';
6465: IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
6860:
6861: WHEN OTHERS THEN
6862: --
6863: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
6864: FND_MSG_PUB.Add_Exc_Msg( 'WSH_PICK_LIST', 'Release_Batch_SRS' );
6865: END IF;
6866: --
6867: IF G_CONC_REQ = FND_API.G_TRUE THEN
6868: l_ret_code := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
7845: CLOSE c_is_detail_assigned;
7846: END IF;
7847: --
7848:
7849: WSH_UTIL_CORE.Default_Handler('WSH_PICK_LIST.Launch_Pick_Release');
7850: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7851: IF check_detail_for_SC%ISOPEN THEN
7852: CLOSE check_detail_for_SC;
7853: END IF;
8305: wsh_debug_sv.pop(l_module_name);
8306: END IF;
8307: --
8308: END CalcWorkingDay;
8309: END WSH_PICK_LIST;