1 PACKAGE BODY WSH_PICKING_BATCHES_GRP AS
2 /* $Header: WSHPRGPB.pls 120.12 2011/01/20 12:37:10 sunilku ship $ */
3
4 -- Package Variables
5 --
6 --===================
7 -- CONSTANTS
8 --===================
9 --
10 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_PICKING_BATCHES_GRP';
11 -- add your constants here if any
12
13 --===================
14 -- PROCEDURES
15 --===================
16 --
17 --=============================================================================================
18 -- Start of comments
19 --
20 -- API Name : Create_Batch
21 -- Type : Group
22 -- Pre-reqs : None.
23 -- Function : This API is called from WSH_PICKING_BATCHES_PUB
24 -- to validate all the parameters and create the Picking Batch
25 --
26 -- PARAMETERS : p_api_version_number known api versionerror buffer
27 -- p_init_msg_list FND_API.G_TRUE to reset list
28 -- x_return_status return status
29 -- x_msg_count number of messages in the list
30 -- x_msg_data text of messages
31 -- p_commit FND_API.G_TRUE to perform a commit
32 -- p_rule_id Picking Rule Id used for defaulting purpose
33 -- p_rule_name Picking Rule name used for defaulting purpose, Ignore it when p_rule_id
34 -- contains value
35 -- p_batch_rec Contains the all the parameters for the batch
36 -- p_batch_prefix Batch prefix to be prefixed to the batch name
37 -- x_batch_id Out parameter contains the batch id created
38 -- VERSION : current version 1.0
39 -- initial version 1.0
40 --============================================================================================
41
42 PROCEDURE Create_Batch(
43 p_api_version_number IN NUMBER,
44 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
45 p_commit IN VARCHAR2 DEFAULT NULL,
46 x_return_status OUT NOCOPY VARCHAR2,
47 x_msg_count OUT NOCOPY NUMBER,
48 x_msg_data OUT NOCOPY VARCHAR2,
49 p_rule_id IN NUMBER DEFAULT NULL,
50 p_rule_name IN VARCHAR2 DEFAULT NULL,
51 p_batch_rec IN WSH_PICKING_BATCHES_PUB.Batch_Info_Rec,
52 p_batch_prefix IN VARCHAR2 DEFAULT NULL,
53 x_batch_id OUT NOCOPY NUMBER) IS
54
55
56
57 ---- cursor to get Picking Rule information for the Given Picking Rule Id
58 CURSOR Get_Picking_Rule_Info(v_rule_id IN NUMBER,v_rule_name IN VARCHAR2) IS
59 SELECT -- name,
60 backorders_only_flag,
61 autodetail_pr_flag,
62 auto_pick_confirm_flag,
63 pick_sequence_rule_id,
64 pick_grouping_rule_id,
65 include_planned_lines,
66 organization_id,
67 customer_id,
68 from_requested_date,
69 to_requested_date,
70 existing_rsvs_only_flag,
71 order_header_id,
72 inventory_item_id,
73 order_type_id,
74 from_scheduled_ship_date,
75 to_scheduled_ship_date,
76 shipment_priority_code,
77 ship_method_code,
78 ship_set_number,
79 ship_to_location_id,
80 default_stage_subinventory,
81 default_stage_locator_id,
82 pick_from_subinventory,
83 pick_from_locator_id,
84 task_id,
85 project_id,
86 ship_from_location_id,
87 autocreate_delivery_flag,
88 ship_confirm_rule_id,
89 autopack_flag,
90 autopack_level,
91 document_set_id,
92 task_planning_flag,
93 ac_delivery_criteria,
94 append_flag,
95 task_priority,
96 nvl(allocation_method,'I'), -- X-dock
97 crossdock_criteria_id, -- X-dock
98 dynamic_replenishment_flag, --bug# 6689448 (replenishment project)
99 rel_subinventory, --Bug8221008
100 client_id -- LSP PROJECT
101 FROM wsh_picking_rules
102 WHERE picking_rule_id = nvl(v_rule_id,picking_rule_id)
103 AND name = nvl(v_rule_name,name)
104 AND trunc(sysdate) BETWEEN nvl(start_date_active,trunc(sysdate)) AND nvl(end_date_active,trunc(sysdate) + 1);
105
106
107 CURSOR Get_Order_Type_id(p_type_id IN NUMBER ,p_type_name IN VARCHAR2) IS
108 SELECT transaction_type_id
109 FROM oe_transaction_types_tl
110 WHERE p_type_id IS NOT NULL
111 AND transaction_type_id = p_type_id
112 UNION All
113 SELECT transaction_type_id
114 FROM oe_transaction_types_tl
115 WHERE p_type_id IS NULL
116 AND name = p_type_name;
117
118
119 CURSOR Get_order_header_id(p_order_number IN NUMBER ,p_order_type_id IN NUMBER,p_header_id IN NUMBER) IS
120 SELECT header_id
121 FROM oe_order_headers_all
122 WHERE p_header_id IS NOT NULL
123 AND header_id = p_header_id
124 UNION ALL
125 SELECT header_id
126 FROM oe_order_headers_all
127 WHERE p_header_id IS NULL
128 AND order_number = p_order_number
129 AND order_type_id = p_order_type_id;
130
131 CURSOR Get_Ship_Set_Id(p_ship_set_id IN NUMBER, p_ship_set_number IN VARCHAR2, p_header_id IN NUMBER) IS
132 SELECT set_id
133 FROM OE_SETS
134 WHERE p_ship_set_id IS NOT NULL
135 AND set_id = p_ship_set_id
136 UNION ALL
137 SELECT set_id
138 FROM OE_SETS
139 WHERE p_ship_set_id IS NULL
140 AND header_id = p_header_id
141 AND set_name = p_ship_set_number
142 AND set_type = 'SHIP_SET';
143
144 CURSOR Check_Delivery_Detail_Id(p_delivery_detail_id IN NUMBER) IS
145 SELECT delivery_detail_id
146 FROM wsh_delivery_details
147 WHERE delivery_detail_id = p_delivery_detail_id
148 AND container_flag IN ('N','Y'); -- R12 MDC
149
150 CURSOR Check_Item_Id( p_item_id IN NUMBER,p_organization_id IN NUMBER) IS
151 SELECT inventory_item_id
152 FROM mtl_system_items
153 WHERE inventory_item_id = p_item_id
154 AND organization_id = nvl(p_organization_id,organization_id)
155 AND rownum = 1;
156
157 CURSOR Check_Subinventory( p_subinventory_name IN VARCHAR2,p_organization_id IN NUMBER) IS
158 SELECT secondary_inventory_name
159 FROM mtl_subinventories_trk_val_v
160 WHERE secondary_inventory_name = p_subinventory_name
161 AND organization_id = p_organization_id;
162
163 CURSOR Check_Locator_Id ( p_Locator_Id IN VARCHAR2, p_subinventory IN VARCHAR2, p_organization_id IN NUMBER) IS
164 SELECT Inventory_location_id
165 FROM mtl_item_locations
166 WHERE Inventory_location_id = p_Locator_Id
167 AND subinventory_code = p_subinventory
168 AND organization_id = p_organization_id;
169
170 CURSOR Check_Category_Set_Id( p_category_set_id IN NUMBER) IS
171 SELECT category_set_id
172 FROM mtl_category_sets
173 WHERE category_set_id = p_category_set_id
174 AND rownum = 1;
175
176 CURSOR Check_Category_Id( p_category_id IN NUMBER ) IS
177 SELECT category_id
178 FROM mtl_item_categories
179 WHERE category_id = p_category_id
180 AND rownum = 1;
181
182 -- Bug 3438300 - Start
183 cursor c_location_id (p_org_id VARCHAR2) IS
184 select location_id from wsh_ship_from_orgs_v
185 where organization_id = p_org_id;
186
187 cursor c_org_id (p_location_id NUMBER) IS
188 select organization_id from wsh_ship_from_orgs_v
189 where location_id = p_location_id;
190
191 -- Bug# 3438300 - End
192
193 --R12 MDC
194 CURSOR c_check_consol_dlvy(p_delivery_id IN NUMBER) IS
195 SELECT delivery_id
196 FROM wsh_new_deliveries
197 WHERE delivery_id = p_delivery_id
198 AND delivery_type = 'STANDARD';
199
200
201 -- Standard call to check for call compatibility
202 l_api_version CONSTANT NUMBER := 1.0;
203 l_api_name CONSTANT VARCHAR2(30) := 'WSH_PICKING_BATCHES_GRP';
204 l_return_status VARCHAR2(30) := NULL;
205 l_msg_count NUMBER;
206 l_msg_data VARCHAR2(32767);
207 l_number_of_errors NUMBER := 0;
208 l_number_of_warnings NUMBER := 0;
209 l_debug_on BOOLEAN;
210
211 -- Record Variable which holds the input Batch Record
212 l_batch_in_rec WSH_PICKING_BATCHES_PUB.Batch_Info_rec;
213 -- Record Variable which holds final data to be sent to the private API
214 l_batch_grp_rec WSH_PICKING_BATCHES_GRP.Batch_Info_rec;
215
216 l_rule_id NUMBER;
217 l_rule_name VARCHAR2(60);
218 l_batch_name_prefix VARCHAR2(60);
219 -- bug 3463315 add validation when append_flag = 'Y'
220 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
221
222 -- Bug 3438300 - Start
223 l_ship_from_loc_id NUMBER;
224 l_org_id NUMBER;
225 -- Bug# 3438300 - End
226
227 l_is_WMS_org VARCHAR2(1); -- Bug# 3480908
228 --
229 l_client_name VARCHAR2(200); -- LSP PROJECT
230 --
231 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_BATCH';
232 --
233
234 WSH_INVALID_CONSOL_DEL EXCEPTION;
235
236 BEGIN
237
238 --
239 -- Debug Statements
240 --
241 --
242 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
243 --
244 IF l_debug_on IS NULL THEN
245 --{
246 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
247 --}
248 END IF;
249 --
250 SAVEPOINT PICKING_BATCH_GRP;
251
252 -- Standard begin of API savepoint
253 --
254 -- Debug Statements
255 --
256 IF l_debug_on THEN
257 --{
258 wsh_debug_sv.push(l_module_name);
259 --
260 wsh_debug_sv.LOG(l_module_name, 'P_API_VERSION_NUMBER', p_api_version_number);
261 wsh_debug_sv.LOG(l_module_name, 'P_INIT_MSG_LIST', p_init_msg_list);
262 wsh_debug_sv.LOG(l_module_name, 'P_COMMIT', p_commit);
263 --}
264 END IF;
265
266 IF NOT fnd_api.compatible_api_call(
267 l_api_version,
268 p_api_version_number,
269 l_api_name,
270 g_pkg_name) THEN
271 RAISE fnd_api.g_exc_unexpected_error;
272 END IF;
273 -- Check p_init_msg_list
274 IF fnd_api.to_boolean(p_init_msg_list) THEN
275 --{
276 fnd_msg_pub.initialize;
277 --}
278 END IF;
279 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
280 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
281
282 l_batch_in_rec := p_batch_rec;
283 l_rule_id := p_rule_id;
284 l_rule_name := p_rule_name;
285 l_batch_name_prefix := p_batch_prefix;
286
287
288 --
289 -- Debug Statements
290 --
291 IF l_debug_on THEN
292 --{
293 wsh_debug_sv.log(l_module_name,'PROCESSING THE RULE INFORMATION'|| ' P_RULE_ID : ' || TO_CHAR( l_rule_id ) || ' P_RULE_NAME: '|| l_rule_name);
294 --}
295 END IF;
296 --
297 IF ( l_rule_id is NOT NULL AND l_rule_id <> FND_API.G_MISS_NUM ) OR ( l_rule_name is NOT NULL AND l_rule_name <> FND_API.G_MISS_CHAR ) THEN
298 --{ Passed the Values or Not
299 IF ( l_rule_id = FND_API.G_MISS_NUM ) THEN
300 --{
301 l_rule_id := NULL;
302 --}
303 END IF;
304 IF ( l_rule_name = FND_API.G_MISS_CHAR ) OR ( l_rule_id IS NOT NULL) THEN
305 --{
306 l_rule_name := NULL;
307 --}
308 END IF;
309 --
310 --
311 -- Debug Statements
312 --
313 IF l_debug_on THEN
314 --{
315 WSH_DEBUG_SV.logmsg(l_module_name, 'FETCHING PICKING RULE INFO FOR THE GIVEN RULE ID AND RULE NAME' );
316 --}
317 END IF;
318 --
319 -- fetch Picking Rule Information for the given picking Rule Id
320 OPEN Get_Picking_Rule_Info(l_rule_id,l_rule_name);
321 FETCH Get_Picking_Rule_Info
322 INTO l_batch_grp_rec.backorders_only_flag,
323 l_batch_grp_rec.autodetail_pr_flag,
324 l_batch_grp_rec.auto_pick_confirm_flag,
325 l_batch_grp_rec.pick_sequence_rule_id,
326 l_batch_grp_rec.pick_grouping_rule_id,
327 l_batch_grp_rec.include_planned_lines,
328 l_batch_grp_rec.organization_id,
329 l_batch_grp_rec.customer_id,
330 l_batch_grp_rec.from_requested_date,
331 l_batch_grp_rec.to_requested_date,
332 l_batch_grp_rec.existing_rsvs_only_flag,
333 l_batch_grp_rec.order_header_id,
334 l_batch_grp_rec.inventory_item_id,
335 l_batch_grp_rec.order_type_id,
336 l_batch_grp_rec.from_scheduled_ship_date,
337 l_batch_grp_rec.to_scheduled_ship_date,
338 l_batch_grp_rec.shipment_priority_code,
339 l_batch_grp_rec.ship_method_code,
340 l_batch_grp_rec.ship_set_id,
341 l_batch_grp_rec.ship_to_location_id,
342 l_batch_grp_rec.Default_Stage_subinventory,
343 l_batch_grp_rec.Default_Stage_locator_id,
344 l_batch_grp_rec.pick_from_subinventory,
345 l_batch_grp_rec.pick_from_locator_id,
346 l_batch_grp_rec.task_id,
347 l_batch_grp_rec.project_id,
348 l_batch_grp_rec.ship_from_location_id,
349 l_batch_grp_rec.autocreate_delivery_flag,
350 l_batch_grp_rec.ship_confirm_rule_id,
351 l_batch_grp_rec.autopack_flag,
352 l_batch_grp_rec.autopack_level,
353 l_batch_grp_rec.document_set_id,
354 l_batch_grp_rec.task_planning_flag,
355 l_batch_grp_rec.ac_delivery_criteria,
356 l_batch_grp_rec.append_flag,
357 l_batch_grp_rec.task_priority,
358 l_batch_grp_rec.allocation_method, --X-dock
359 l_batch_grp_rec.crossdock_criteria_id, --X-dock
360 l_batch_grp_rec.dynamic_replenishment_flag, --bug# 6689448 (replenishment project)
361 l_batch_grp_rec.rel_subinventory, --Bug8221008
362 l_batch_grp_rec.client_id; -- LSP PROJECT
363 -- If rule info provided is not exist raise error
364 IF Get_Picking_Rule_Info%NOTFOUND THEN
365 --{
366 --
367 -- Debug Statements
368 --
369 IF l_debug_on THEN
370 --{
371 WSH_DEBUG_SV.logmsg(l_module_name, 'P_RULE_ID : ' || TO_CHAR( p_rule_id ) || 'P_RULE_NAME: '|| p_rule_name || ' DOES NOT EXIST.');
372 --}
373 END IF;
374 --
375 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
376 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','PICKING_RULE');
377 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
378 wsh_util_core.add_message(x_return_status);
379 CLOSE Get_Picking_Rule_Info;
380 IF l_debug_on THEN
381 --{
382 WSH_DEBUG_SV.pop(l_module_name);
383 --}
384 END IF;
385 --
386 return;
387 --}
388 END IF;
389 CLOSE Get_Picking_Rule_Info;
390 --} End of Pick Rule Checking
391 END IF;
392
393
394 ------------------------------------------------------------------------------------------------------------
395 -- VALIDATE ALL THE INPUT PARAMETER VALUES
396 -- LOGIC followed
397 -- If Parameter value is NULL then treat it as NOT PASSED to the API
398 -- (*** In this case consider the value from the picking rule info if picking rule info provided)
399 -- If the parameter value is FND_API.G_MISS.xxx then treat it as NULL
400 -- Otherwise Validate the parameter value.
401 ------------------------------------------------------------------------------------------------------------
402
403 -- Validating the Back Order Only Flag
404 IF ( l_batch_in_rec.Backorders_Only_Flag IS NOT NULL ) THEN
405 --{ Passed the Values or Not
406 IF (l_batch_in_rec.Backorders_Only_Flag <> FND_API.G_MISS_CHAR ) THEN
407 --{ Checking value is null or not
408 -- BUG # 6719369 (Rplenishment project); Need to consider the
409 -- 'replenishment completed' status.
410 IF ( l_batch_in_rec.Backorders_Only_Flag NOT IN ('I','E','O','M') ) THEN
411 --{
412 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
413 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','BACKORDERS_ONLY_FLAG ');
414 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
415 wsh_util_core.add_message(x_return_status);
416 return;
417 --}
418 END IF;
419 l_batch_grp_rec.Backorders_Only_Flag := l_batch_in_rec.Backorders_Only_Flag ;
420 ELSE
421 l_batch_grp_rec.Backorders_Only_Flag := NULL;
422 --}
423 END IF;
424 --}
425 END IF;
426
427 -- End of Validating the Back Order Only Flag
428 --
429 -- LSP PROJECT : begin
430 --- Cleint Validations
431
432 IF (WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'L') THEN
433 --{
434 IF ( ( l_batch_in_rec.client_Id IS NOT NULL ) OR ( l_batch_in_rec.client_code IS NOT NULL )) THEN
435 --{ Passed the Values or Not
436 IF l_batch_in_rec.client_Id = FND_API.G_MISS_NUM THEN
437 --{
438 l_batch_in_rec.client_Id := NULL;
439 --}
440 END IF;
441 IF l_batch_in_rec.client_code = FND_API.G_MISS_CHAR THEN
442 --{
443 l_batch_in_rec.client_code := NULL;
444 --}
445 END IF;
446 IF ( ( l_batch_in_rec.client_Id IS NOT NULL) OR ( l_batch_in_rec.client_code IS NOT NULL) ) THEN
447 --{
448 IF l_debug_on THEN
449 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_DEPLOY.GET_CLIENT_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
450 END IF;
451 wms_deploy.get_client_details(
452 x_client_id => l_batch_in_rec.client_Id,
453 x_client_code => l_batch_in_rec.client_code,
454 x_client_name => l_client_name,
455 x_return_status => l_return_status);
456 IF l_debug_on THEN
457 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
458 END IF;
459 --
460 wsh_util_core.api_post_call(
461 p_return_status => l_return_status,
462 x_num_warnings => l_number_of_warnings,
463 x_num_errors => l_number_of_errors,
464 p_msg_data => l_msg_data);
465 l_batch_grp_rec.client_Id := l_batch_in_rec.client_Id;
466 ELSE
467 l_batch_grp_rec.client_Id := NULL;
468 --}
469 END IF;
470 --} Passed the Values or Not
471 END IF;
472 ELSE
473 l_batch_grp_rec.client_Id := NULL;
474 --}
475 END IF;
476 -- End of Client Validations
477 -- LSP PROJECT : end
478 --
479 --- Organization Validations
480 IF ( ( l_batch_in_rec.Organization_Id IS NOT NULL ) OR ( l_batch_in_rec.Organization_Code IS NOT NULL ) ) THEN
481 --{ Passed the Values or Not
482 IF ( l_batch_in_rec.Organization_Id = FND_API.G_MISS_NUM ) THEN
483 --{
484 l_batch_in_rec.Organization_Id := NULL;
485 --}
486 END IF;
487
488 IF ( l_batch_in_rec.Organization_Code = FND_API.G_MISS_CHAR ) THEN
489 --{
490 l_batch_in_rec.Organization_Code := NULL;
491 --}
492 END IF;
493
494 IF ( ( l_batch_in_rec.Organization_Id IS NOT NULL ) OR ( l_batch_in_rec.Organization_Code IS NOT NULL ) ) THEN
495 --{
496
497 IF l_debug_on THEN
498 --{
499 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_ORG',WSH_DEBUG_SV.C_PROC_LEVEL);
500 --}
501 END IF;
502 WSH_UTIL_VALIDATE.Validate_Org(
503 p_Org_id => l_batch_in_rec.Organization_Id,
504 p_Org_Code => l_batch_in_rec.Organization_Code,
505 x_return_status => l_return_status);
506
507 IF l_debug_on THEN
508 --{
509 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
510 --}
511 END IF;
512 --
513 wsh_util_core.api_post_call(
514 p_return_status => l_return_status,
515 x_num_warnings => l_number_of_warnings,
516 x_num_errors => l_number_of_errors,
517 p_msg_data => l_msg_data
518 );
519
520 l_batch_grp_rec.Organization_Id :=l_batch_in_rec.Organization_Id;
521
522 -- Bug# 3480908 - start - Checking whether the org is a WMS org
523 IF l_debug_on THEN
524 --{
525 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.Check_Wms_Org',WSH_DEBUG_SV.C_PROC_LEVEL);
526 --}
527 END IF;
528 l_is_WMS_org := WSH_UTIL_VALIDATE.Check_Wms_Org(
529 p_organization_id => l_batch_grp_rec.Organization_Id);
530
531 IF l_debug_on THEN
532 --{
533 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
534 --}
535 END IF;
536 --
537 wsh_util_core.api_post_call(
538 p_return_status => l_return_status,
539 x_num_warnings => l_number_of_warnings,
540 x_num_errors => l_number_of_errors,
541 p_msg_data => l_msg_data
542 );
543
544 -- Bug# 3480908 - End
545
546 ELSE
547 l_batch_grp_rec.Organization_Id := NULL;
548 --}
549 END IF;
550 --} Passed the Values or Not
551 END IF;
552
553 -- End of Organization Validations
554
555 --- Document Set Validations
556
557 IF ( ( l_batch_in_rec.Document_Set_Id IS NOT NULL ) OR ( l_batch_in_rec.Document_Set_Name IS NOT NULL )) THEN
558 --{ Passed the Values or Not
559 IF ( l_batch_in_rec.Document_Set_Id = FND_API.G_MISS_NUM ) THEN
560 --{
561 l_batch_in_rec.Document_Set_id := NULL;
562 --}
563 END IF;
564 IF ( l_batch_in_rec.Document_Set_Name = FND_API.G_MISS_CHAR) THEN
565 --{
566 l_batch_in_rec.Document_Set_name := NULL;
567 --}
568 END IF;
569 IF ( l_batch_in_rec.Document_Set_Id IS NOT NULL) OR ( l_batch_in_rec.Document_Set_name IS NOT NULL) THEN
570 --{
571 IF l_debug_on THEN
572 --{
573 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_REPORT_SET',WSH_DEBUG_SV.C_PROC_LEVEL);
574 --}
575 END IF;
576 WSH_UTIL_VALIDATE.Validate_Report_Set(
577 p_report_set_id => l_batch_in_rec.Document_Set_Id,
578 p_report_set_name => l_batch_in_rec.Document_Set_name,
579 x_return_status => l_return_status);
580
581 IF l_debug_on THEN
582 --{
583 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
584 --}
585 END IF;
586 --
587 wsh_util_core.api_post_call(
588 p_return_status => l_return_status,
589 x_num_warnings => l_number_of_warnings,
590 x_num_errors => l_number_of_errors,
591 p_msg_data => l_msg_data
592 );
593 l_batch_grp_rec.Document_Set_Id := l_batch_in_rec.Document_Set_Id;
594 ELSE
595 l_batch_grp_rec.Document_Set_Id := NULL;
596 --}
597 END IF;
598 --} Passed the Values or Not
599 END IF;
600
601 -- End of Document Set Validations
602
603 -- Ship Method Validations
604
605 IF ( ( l_batch_in_rec.Ship_Method_Code IS NOT NULL ) OR ( l_batch_in_rec.Ship_Method_Name IS NOT NULL )) THEN
606 --{ Passed the Values or Not
607 IF ( l_batch_in_rec.Ship_Method_Code = FND_API.G_MISS_CHAR ) THEN
608 --{
609 l_batch_in_rec.Ship_Method_Code := NULL;
610 --}
611 END IF;
612 IF ( l_batch_in_rec.Ship_Method_Name = FND_API.G_MISS_CHAR ) THEN
613 --{
614 l_batch_in_rec.Ship_Method_Name := NULL;
615 --}
616 END IF;
617 IF ( l_batch_in_rec.Ship_Method_Code IS NOT NULL) OR ( l_batch_in_rec.Ship_Method_Name IS NOT NULL) THEN
618 --{
619 IF l_debug_on THEN
620 --{
621 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_SHIP_METHOD',WSH_DEBUG_SV.C_PROC_LEVEL);
622 --}
623 END IF;
624 WSH_UTIL_VALIDATE.Validate_Ship_Method(
625 p_ship_method_code => l_batch_in_rec.Ship_Method_Code,
626 p_ship_method_name => l_batch_in_rec.Ship_Method_Name,
627 x_return_status => l_return_status);
628
629 IF l_debug_on THEN
630 --{
631 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
632 --}
633 END IF;
634 --
635 wsh_util_core.api_post_call(
636 p_return_status => l_return_status,
637 x_num_warnings => l_number_of_warnings,
638 x_num_errors => l_number_of_errors,
639 p_msg_data => l_msg_data
640 );
641 l_batch_grp_rec.ship_Method_Code := l_batch_in_rec.Ship_Method_Code;
642 ELSE
643 l_batch_grp_rec.ship_Method_Code := NULL;
644 --}
645 END IF;
646 --} Passed the Values or Not
647 END IF;
648
649 --- End of Ship Method Validations
650
651 --- Customer Validations
652
653 IF ( ( l_batch_in_rec.Customer_Id IS NOT NULL ) OR ( l_batch_in_rec.Customer_Number IS NOT NULL )) THEN
654 --{ Passed the Values or Not
655 IF l_batch_in_rec.Customer_Id = FND_API.G_MISS_NUM THEN
656 --{
657 l_batch_in_rec.Customer_Id := NULL;
658 --}
659 END IF;
660 IF l_batch_in_rec.Customer_Number = FND_API.G_MISS_CHAR THEN
661 --{
662 l_batch_in_rec.Customer_Number := NULL;
663 --}
664 END IF;
665 IF ( ( l_batch_in_rec.Customer_Id IS NOT NULL) OR ( l_batch_in_rec.Customer_Number IS NOT NULL) ) THEN
666 --{
667 IF l_debug_on THEN
668 --{
669 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_CUSTOMER',WSH_DEBUG_SV.C_PROC_LEVEL);
670 --}
671 END IF;
672 WSH_UTIL_VALIDATE.Validate_Customer(
673 p_Customer_id => l_batch_in_rec.Customer_Id,
674 p_Customer_Number => l_batch_in_rec.Customer_Number,
675 x_return_status => l_return_status);
676
677 IF l_debug_on THEN
678 --{
679 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
680 --}
681 END IF;
682 --
683 wsh_util_core.api_post_call(
684 p_return_status => l_return_status,
685 x_num_warnings => l_number_of_warnings,
686 x_num_errors => l_number_of_errors,
687 p_msg_data => l_msg_data
688 );
689 l_batch_grp_rec.Customer_Id := l_batch_in_rec.Customer_Id;
690 ELSE
691 l_batch_grp_rec.Customer_Id := NULL;
692 --}
693 END IF;
694 --} Passed the Values or Not
695 END IF;
696
697 -- End of Customer Validations
698
699 -- Order Type Validations
700
701 IF ( ( l_batch_in_rec.Order_Type_Id IS NOT NULL ) OR ( l_batch_in_rec.Order_Type_Name IS NOT NULL )) THEN
702 --{ Passed the Values or Not
703 IF ( l_batch_in_rec.Order_Type_Id = FND_API.G_MISS_NUM ) THEN
704 --{
705 l_batch_in_rec.Order_Type_Id := NULL;
706 --}
707 END IF;
708 IF ( l_batch_in_rec.Order_Type_Name = FND_API.G_MISS_CHAR ) THEN
709 --{
710 l_batch_in_rec.Order_Type_Name := NULL;
711 --}
712 END IF;
713 IF ( ( l_batch_in_rec.Order_Type_Id IS NOT NULL ) OR ( l_batch_in_rec.Order_Type_Name IS NOT NULL) )THEN
714 --{
715 OPEN Get_Order_Type_id(l_batch_in_rec.Order_Type_Id,l_batch_in_rec.Order_Type_Name);
716 FETCH Get_Order_Type_id INTO l_batch_in_rec.Order_Type_Id;
717 IF ( Get_Order_Type_id%NOTFOUND ) THEN
718 --{
719 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
720 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ORDER_TYPE');
721 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
722 wsh_util_core.add_message(x_return_status);
723 CLOSE Get_Order_Type_id;
724 return;
725 --}
726 END IF;
727 CLOSE Get_Order_Type_id;
728 l_batch_grp_rec.Order_Type_Id := l_batch_in_rec.Order_Type_Id;
729 ELSE
730 l_batch_grp_rec.Order_Type_Id := NULL;
731 --}
732 END IF;
733 --} Passed the Values or Not
734 END IF;
735
736 -- End of Order Type Validations
737
738
739 -- Order Number Validations
740
741 IF ( ( l_batch_in_rec.order_header_id IS NOT NULL ) OR ( l_batch_in_rec.order_number IS NOT NULL )) THEN
742 --{ Passed the Values or Not
743 IF ( l_batch_in_rec.order_header_id = FND_API.G_MISS_NUM ) THEN
744 --{
745 l_batch_in_rec.order_header_id := NULL;
746 --}
747 END IF;
748 IF ( l_batch_in_rec.order_number = FND_API.G_MISS_NUM ) OR ( l_batch_in_rec.order_header_id IS NOT NULL ) THEN
749 --{
750 l_batch_in_rec.order_number := NULL;
751 --}
752 END IF;
753 IF ( l_batch_in_rec.order_number IS NOT NULL AND l_batch_grp_rec.order_type_id IS NULL ) THEN
754 --{
755 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
756 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ORDER_NUMBER AND ORDER_TYPE_ID combination');
757 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
758 wsh_util_core.add_message(x_return_status);
759 return;
760 --}
761 END IF;
762 IF ( ( l_batch_in_rec.order_header_id IS NOT NULL ) OR ( l_batch_in_rec.order_number IS NOT NULL AND l_batch_grp_rec.order_type_id IS NOT NULL ) ) THEN
763 --{
764 OPEN Get_Order_Header_id(l_batch_in_rec.Order_Number,l_batch_grp_rec.Order_Type_id,l_batch_in_rec.order_header_id);
765 FETCH Get_Order_Header_id INTO l_batch_in_rec.order_header_id;
766 IF ( Get_Order_Header_id%NOTFOUND ) THEN
767 --{
768 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
769 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ORDER_NUMBER OR ORDER_HEADER_ID');
770 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
771 wsh_util_core.add_message(x_return_status);
772 CLOSE Get_Order_Header_id;
773 return;
774 --}
775 END IF;
776 CLOSE Get_Order_Header_id;
777 l_batch_grp_rec.order_header_id := l_batch_in_rec.order_header_id;
778 ELSE
779 l_batch_grp_rec.order_header_id := NULL;
780 --}
781 END IF;
782 --}Passed the Values or Not
783 END IF;
784
785 -- End of Order Number Validations
786
787 -- Ship Set Number Validations
788
789 IF ( ( l_batch_in_rec.ship_set_id IS NOT NULL ) OR ( l_batch_in_rec.ship_set_number IS NOT NULL )) THEN
790 --{ Passed the Values or Not
791 IF ( l_batch_in_rec.ship_set_id = FND_API.G_MISS_NUM ) THEN
792 --{
793 l_batch_in_rec.ship_set_id := NULL;
794 --}
795 END IF;
796 IF ( ( l_batch_in_rec.ship_set_number = FND_API.G_MISS_CHAR ) OR ( l_batch_in_rec.ship_set_id IS NOT NULL ) ) THEN
797 --{
798 l_batch_in_rec.ship_set_number := NULL;
799 --}
800 END IF;
801 IF ( l_batch_in_rec.ship_set_number IS NOT NULL AND l_batch_grp_rec.order_header_id IS NULL ) THEN
802 --{
803 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
804 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','SHIP_SET_NUMBER AND ORDER_HEADER_ID combination');
805 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
806 wsh_util_core.add_message(x_return_status);
807 return;
808 --}
809 END IF;
810 IF ( ( l_batch_in_rec.ship_set_id IS NOT NULL ) OR ( l_batch_in_rec.ship_set_number IS NOT NULL AND l_batch_grp_rec.order_header_id IS NOT NULL ) ) THEN
811 --{
812 OPEN Get_Ship_Set_Id(l_batch_in_rec.ship_set_id,l_batch_in_rec.ship_set_number,l_batch_grp_rec.order_header_id);
813 FETCH Get_Ship_Set_Id INTO l_batch_in_rec.ship_set_id;
814 IF ( Get_Ship_Set_Id%NOTFOUND ) THEN
815 --{
816 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
817 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','SHIP_SET_NUMBER OR SHIP_SET_ID');
818 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
819 wsh_util_core.add_message(x_return_status);
820 CLOSE Get_Ship_Set_Id;
821 return;
822 --}
823 END IF;
824 CLOSE Get_Ship_Set_Id;
825 l_batch_grp_rec.ship_set_id := l_batch_in_rec.ship_set_id;
826 ELSE
827 l_batch_grp_rec.ship_set_id := NULL;
828 --}
829 END IF;
830 --} Passed the Values or Not
831 END IF;
832
833 -- End of Ship Set Number Validations
834
835 --- Ship To Location Validations
836 IF l_batch_grp_rec.Customer_Id IS NOT NULL THEN --Bug# 3480908 - Ignore Ship_To if customer not provided
837 IF ( ( l_batch_in_rec.Ship_To_Location_Id IS NOT NULL ) OR ( l_batch_in_rec.Ship_To_Location_code IS NOT NULL ) ) THEN
838 --{ Passed the Values or Not
839 IF ( l_batch_in_rec.Ship_To_Location_Id = FND_API.G_MISS_NUM ) THEN
840 --{
841 l_batch_in_rec.Ship_To_Location_Id := NULL;
842 --}
843 END IF;
844 IF ( l_batch_in_rec.Ship_To_Location_code = FND_API.G_MISS_CHAR) THEN
845 --{
846 l_batch_in_rec.Ship_To_Location_code := NULL;
847 --}
848 END IF;
849 IF ( ( l_batch_in_rec.Ship_To_Location_Id IS NOT NULL) OR ( l_batch_in_rec.Ship_To_Location_code IS NOT NULL ) ) THEN
850 --{
851 IF l_debug_on THEN
852 --{
853 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_LOCATION',WSH_DEBUG_SV.C_PROC_LEVEL);
854 --}
855 END IF;
856 WSH_UTIL_VALIDATE.Validate_Location(
857 p_location_id => l_batch_in_rec.Ship_To_Location_Id,
858 p_location_code => l_batch_in_rec.Ship_To_Location_Code,
859 x_return_status => l_return_status,
860 p_isWshLocation => TRUE);
861
862 IF l_debug_on THEN
863 --{
864 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
865 --}
866 END IF;
867 --
868 wsh_util_core.api_post_call(
869 p_return_status => l_return_status,
870 x_num_warnings => l_number_of_warnings,
871 x_num_errors => l_number_of_errors,
872 p_msg_data => l_msg_data
873 );
874
875 l_batch_grp_rec.Ship_To_Location_Id := l_batch_in_rec.Ship_To_Location_Id;
876 ELSE
877 l_batch_grp_rec.Ship_To_Location_Id:= NULL;
878 --}
879 END IF;
880 --} Passed the Values or Not
881 END IF;
882 ELSE -- Bug# 3480908
883 l_batch_grp_rec.Ship_To_Location_Id:= NULL;
884 END IF;
885 -- End of Ship to Location Validations
886
887 --- Ship From Location Validations
888
889 IF ( ( l_batch_in_rec.Ship_From_Location_Id IS NOT NULL ) OR ( l_batch_in_rec.Ship_From_Location_code IS NOT NULL ) ) THEN
890 --{ Passed the Values or Not
891 IF ( l_batch_in_rec.Ship_From_Location_Id = FND_API.G_MISS_NUM ) THEN
892 --{
893 l_batch_in_rec.Ship_From_Location_Id := NULL;
894 --}
895 END IF;
896 IF ( l_batch_in_rec.Ship_From_Location_code = FND_API.G_MISS_CHAR) THEN
897 --{
898 l_batch_in_rec.Ship_From_Location_code := NULL;
899 --}
900 END IF;
901 IF ( ( l_batch_in_rec.Ship_From_Location_Id IS NOT NULL) OR ( l_batch_in_rec.Ship_From_Location_code IS NOT NULL ) ) THEN
902 --{
903 IF l_debug_on THEN
904 --{
905 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_LOCATION',WSH_DEBUG_SV.C_PROC_LEVEL);
906 --}
907 END IF;
908 WSH_UTIL_VALIDATE.Validate_Location(
909 p_location_id => l_batch_in_rec.Ship_From_Location_Id,
910 p_location_code => l_batch_in_rec.Ship_From_Location_Code,
911 x_return_status => l_return_status,
912 p_isWshLocation => TRUE);
913
914 IF l_debug_on THEN
915 --{
916 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
917 --}
918 END IF;
919 --
920 wsh_util_core.api_post_call(
921 p_return_status => l_return_status,
922 x_num_warnings => l_number_of_warnings,
923 x_num_errors => l_number_of_errors,
924 p_msg_data => l_msg_data
925 );
926 l_batch_grp_rec.Ship_From_Location_Id := l_batch_in_rec.Ship_From_Location_Id;
927 ELSE
928 l_batch_grp_rec.Ship_From_Location_Id := NULL;
929 --}
930 END IF;
931 --} Passed the Values or Not
932 -- Bug 5373798
933 ELSE -- (l_batch_in_rec.Ship_From_Location_Id IS NULL) AND ( l_batch_in_rec.Ship_From_Location_code IS NULL)
934 l_batch_grp_rec.Ship_From_Location_Id := NULL;
935 END IF;
936
937
938 -- End of Ship From Location Validations
939
940
941 --- Trip Validations
942
943 IF ( ( l_batch_in_rec.Trip_Id IS NOT NULL ) OR ( l_batch_in_rec.Trip_Name IS NOT NULL ) ) THEN
944 --{ Passed the Values or Not
945 IF ( l_batch_in_rec.Trip_Id = FND_API.G_MISS_NUM ) THEN
946 --{
947 l_batch_in_rec.Trip_Id := NULL;
948 --}
949 END IF;
950 IF ( l_batch_in_rec.Trip_name = FND_API.G_MISS_CHAR) THEN
951 --{
952 l_batch_in_rec.Trip_name := NULL;
953 --}
954 END IF;
955 IF ( ( l_batch_in_rec.Trip_Id IS NOT NULL ) OR ( l_batch_in_rec.Trip_Name IS NOT NULL ) ) THEN
956 --{
957 IF l_debug_on THEN
958 --{
959 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_TRIP_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
960 --}
961 END IF;
962 WSH_UTIL_VALIDATE.Validate_Trip_Name(
963 p_trip_id => l_batch_in_rec.Trip_Id,
964 p_trip_name => l_batch_in_rec.Trip_Name,
965 x_return_status => l_return_status);
966
967 IF l_debug_on THEN
968 --{
969 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
970 --}
971 END IF;
972 --
973 wsh_util_core.api_post_call(
974 p_return_status => l_return_status,
975 x_num_warnings => l_number_of_warnings,
976 x_num_errors => l_number_of_errors,
977 p_msg_data => l_msg_data
978 );
979
980 l_batch_grp_rec.Trip_Id :=l_batch_in_rec.Trip_Id;
981 ELSE
982 l_batch_grp_rec.Trip_Id := NULL;
983 --}
984 END IF;
985 --} Passed the Values or Not
986 END IF;
987
988 -- End of Trip Validations
989
990 -- Delivery Validations
991
992 IF ( ( l_batch_in_rec.Delivery_Id IS NOT NULL ) OR ( l_batch_in_rec.Delivery_Name IS NOT NULL ) ) THEN
993 --{ Passed the Values or Not
994 IF ( l_batch_in_rec.Delivery_Id = FND_API.G_MISS_NUM ) THEN
995 --{
996 l_batch_in_rec.Delivery_Id := NULL;
997 --}
998 END IF;
999 IF ( l_batch_in_rec.Delivery_Name = FND_API.G_MISS_CHAR) THEN
1000 --{
1001 l_batch_in_rec.Delivery_name := NULL;
1002 --}
1003 END IF;
1004 IF ( ( l_batch_in_rec.Delivery_Id IS NOT NULL) OR ( l_batch_in_rec.Delivery_Name IS NOT NULL ) ) THEN
1005 --{
1006 IF l_debug_on THEN
1007 --{
1008 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_DELIVERY_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1009 --}
1010 END IF;
1011 WSH_UTIL_VALIDATE.Validate_Delivery_Name(
1012 p_delivery_id => l_batch_in_rec.Delivery_Id,
1013 p_delivery_name => l_batch_in_rec.Delivery_Name,
1014 x_return_status => l_return_status);
1015
1016 IF l_debug_on THEN
1017 --{
1018 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1019 --}
1020 END IF;
1021 --
1022 wsh_util_core.api_post_call(
1023 p_return_status => l_return_status,
1024 x_num_warnings => l_number_of_warnings,
1025 x_num_errors => l_number_of_errors,
1026 p_msg_data => l_msg_data
1027 );
1028
1029 --R12 MDC
1030 --Delivery must be a standard delivery
1031 OPEN c_check_consol_dlvy(l_batch_in_rec.Delivery_Id);
1032 FETCH c_check_consol_dlvy INTO l_batch_in_rec.Delivery_Id;
1033 IF c_check_consol_dlvy%NOTFOUND THEN
1034 CLOSE c_check_consol_dlvy;
1035 RAISE WSH_INVALID_CONSOL_DEL;
1036 END IF;
1037 CLOSE c_check_consol_dlvy;
1038
1039 l_batch_grp_rec.Delivery_Id := l_batch_in_rec.Delivery_Id;
1040 ELSE
1041 l_batch_grp_rec.Delivery_Id := NULL;
1042 --}
1043 END IF;
1044 --} Passed the Values or Not
1045 END IF;
1046
1047 -- End of Delivery Validations
1048
1049 --- Trip Stop Validations
1050 --- Consider the trip stops only when the trip information is provided
1051 --- same thing is being followed in pick release form also
1052 IF ( l_batch_grp_rec.trip_id IS NOT NULL ) THEN
1053 --{
1054 IF ( ( l_batch_in_rec.Trip_Stop_Id IS NOT NULL ) OR ( l_batch_in_rec.trip_stop_location_id IS NOT NULL ) ) THEN
1055 --{ Passed the Values or Not
1056 IF ( l_batch_in_rec.Trip_Stop_Id = FND_API.G_MISS_NUM ) THEN
1057 --{
1058 l_batch_in_rec.Trip_Stop_Id := NULL;
1059 --}
1060 END IF;
1061 IF ( l_batch_in_rec.trip_stop_location_id = FND_API.G_MISS_NUM ) THEN
1062 --{
1063 l_batch_in_rec.trip_stop_location_id := NULL;
1064 --}
1065 END IF;
1066 IF ( ( l_batch_in_rec.Trip_Stop_Id IS NOT NULL ) OR ( l_batch_in_rec.trip_stop_location_id IS NOT NULL ) ) THEN
1067 --{
1068 IF l_debug_on THEN
1069 --{
1070 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_STOP_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1071 --}
1072 END IF;
1073 WSH_UTIL_VALIDATE.Validate_Stop_Name(
1074 p_stop_id => l_batch_in_rec.Trip_Stop_Id,
1075 p_trip_id => l_batch_grp_rec.Trip_id,
1076 p_stop_location_id => l_batch_in_rec.trip_stop_location_id,
1077 p_planned_dep_date => NULL,
1078 x_return_status => l_return_status);
1079
1080 IF l_debug_on THEN
1081 --{
1082 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1083 --}
1084 END IF;
1085 --
1086 wsh_util_core.api_post_call(
1087 p_return_status => l_return_status,
1088 x_num_warnings => l_number_of_warnings,
1089 x_num_errors => l_number_of_errors,
1090 p_msg_data => l_msg_data
1091 );
1092
1093 l_batch_grp_rec.Trip_Stop_Id := l_batch_in_rec.Trip_Stop_Id ;
1094 ELSE
1095 l_batch_grp_rec.Trip_Stop_Id := NULL;
1096 --}
1097 END IF;
1098 --} Passed the Values or Not
1099 END IF;
1100 --}
1101 END IF;
1102
1103 -- End of Trip Stop Validations
1104
1105
1106 -- Pick_Grouping_Rule_Id Validations
1107
1108 IF ( ( l_batch_in_rec.pick_grouping_rule_Id IS NOT NULL ) OR ( l_batch_in_rec.pick_grouping_rule_Name IS NOT NULL ) ) THEN
1109 --{ Passed the Values or Not
1110 IF ( l_batch_in_rec.pick_grouping_rule_Id = FND_API.G_MISS_NUM ) THEN
1111 --{
1112 l_batch_in_rec.pick_grouping_rule_Id := NULL;
1113 --}
1114 END IF;
1115 IF ( l_batch_in_rec.pick_grouping_rule_Name = FND_API.G_MISS_CHAR) THEN
1116 --{
1117 l_batch_in_rec.pick_grouping_rule_name := NULL;
1118 --}
1119 END IF;
1120 IF ( ( l_batch_in_rec.pick_grouping_rule_Id IS NOT NULL) OR ( l_batch_in_rec.pick_grouping_rule_Name IS NOT NULL ) ) THEN
1121 --{
1122 IF l_debug_on THEN
1123 --{
1124 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_PICK_GROUP_RULE_NAMe',WSH_DEBUG_SV.C_PROC_LEVEL);
1125 --}
1126 END IF;
1127 WSH_UTIL_VALIDATE.Validate_Pick_Group_Rule_Name(
1128 p_pick_grouping_rule_id => l_batch_in_rec.pick_grouping_rule_Id,
1129 p_pick_grouping_rule_name => l_batch_in_rec.pick_grouping_rule_Name,
1130 x_return_status => l_return_status);
1131
1132 IF l_debug_on THEN
1133 --{
1134 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1135 --}
1136 END IF;
1137 --
1138 wsh_util_core.api_post_call(
1139 p_return_status => l_return_status,
1140 x_num_warnings => l_number_of_warnings,
1141 x_num_errors => l_number_of_errors,
1142 p_msg_data => l_msg_data
1143 );
1144
1145 l_batch_grp_rec.pick_grouping_rule_Id :=l_batch_in_rec.pick_grouping_rule_Id;
1146 ELSE
1147 l_batch_grp_rec.pick_grouping_rule_Id := NULL;
1148 --}
1149 END IF;
1150 --} Passed the Values or Not
1151 END IF;
1152
1153 -- End of Pick_Grouping_Rule_Id Validations
1154
1155 -- Pick_Sequence_Rule_Id Validations
1156
1157 IF ( ( l_batch_in_rec.pick_sequence_rule_Id IS NOT NULL ) OR ( l_batch_in_rec.pick_sequence_rule_Name IS NOT NULL ) ) THEN
1158 --{ Passed the Values or Not
1159 IF ( l_batch_in_rec.pick_sequence_rule_Id = FND_API.G_MISS_NUM ) THEN
1160 --{
1161 l_batch_in_rec.pick_sequence_rule_Id := NULL;
1162 --}
1163 END IF;
1164 IF ( l_batch_in_rec.pick_sequence_rule_name = FND_API.G_MISS_CHAR) THEN
1165 --{
1166 l_batch_in_rec.pick_sequence_rule_name := NULL;
1167 --}
1168 END IF;
1169 IF ( ( l_batch_in_rec.pick_sequence_rule_Id IS NOT NULL) OR ( l_batch_in_rec.pick_sequence_rule_Name IS NOT NULL ) ) THEN
1170 --{
1171 IF l_debug_on THEN
1172 --{
1173 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_PICK_SEQ_RULE_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1174 --}
1175 END IF;
1176 WSH_UTIL_VALIDATE.Validate_Pick_Seq_Rule_Name(
1177 p_pick_sequence_rule_id => l_batch_in_rec.pick_sequence_rule_Id,
1178 p_pick_sequence_rule_name => l_batch_in_rec.pick_sequence_rule_Name,
1179 x_return_status => l_return_status);
1180
1181 IF l_debug_on THEN
1182 --{
1183 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1184 --}
1185 END IF;
1186 --
1187 wsh_util_core.api_post_call(
1188 p_return_status => l_return_status,
1189 x_num_warnings => l_number_of_warnings,
1190 x_num_errors => l_number_of_errors,
1191 p_msg_data => l_msg_data
1192 );
1193
1194 l_batch_grp_rec.pick_sequence_rule_Id :=l_batch_in_rec.pick_sequence_rule_Id;
1195 ELSE
1196 l_batch_grp_rec.pick_sequence_rule_Id := NULL;
1197 --}
1198 END IF;
1199 --} Passed the Values or Not
1200 END IF;
1201
1202 -- End of Pick_Sequence_Rule_Id Validations
1203
1204 -- Bug 3463315 Append_Flag validation
1205
1206 IF ( l_batch_in_rec.autocreate_delivery_flag IS NOT NULL ) THEN
1207 IF ( l_batch_in_rec.autocreate_delivery_flag = FND_API.G_MISS_CHAR ) THEN
1208 l_batch_in_rec.autocreate_delivery_flag := NULL;
1209 END IF;
1210
1211 IF (l_batch_in_rec.autocreate_delivery_flag IS NOT NULL) THEN
1212 l_batch_grp_rec.autocreate_delivery_flag := l_batch_in_rec.autocreate_delivery_flag;
1213 ELSE
1214 l_batch_grp_rec.autocreate_delivery_flag := NULL;
1215 END IF;
1216
1217 END IF;
1218
1219 -- Bug# 3480908 - Start
1220 IF l_batch_grp_rec.autocreate_delivery_flag <> 'Y' THEN
1221 l_batch_grp_rec.Autopack_Flag := 'N';
1222 l_batch_grp_rec.Ship_Confirm_Rule_Id := NULL;
1223 l_batch_grp_rec.ac_Delivery_Criteria := NULL;
1224 END IF;
1225 -- Bug# 3480908 - End
1226
1227 IF ( l_batch_in_rec.ac_delivery_criteria IS NOT NULL ) THEN
1228 IF ( l_batch_in_rec.ac_delivery_criteria = FND_API.G_MISS_CHAR ) THEN
1229 l_batch_in_rec.ac_delivery_criteria := NULL;
1230 END IF;
1231
1232 IF (l_batch_in_rec.ac_delivery_criteria IS NOT NULL) THEN
1233 l_batch_grp_rec.ac_delivery_criteria := l_batch_in_rec.ac_delivery_criteria;
1234 ELSE
1235 l_batch_grp_rec.ac_delivery_criteria := NULL;
1236 END IF;
1237 END IF;
1238
1239 -- End of Bug 3463315 Append_Flag validation
1240
1241 -- Ship_Confirm_Rule_Id Validations
1242
1243 IF ( ( l_batch_in_rec.ship_confirm_rule_Id IS NOT NULL ) OR ( l_batch_in_rec.ship_confirm_rule_Name IS NOT NULL ) ) THEN
1244 --{ Passed the Values or Not
1245 IF ( l_batch_in_rec.ship_confirm_rule_Id = FND_API.G_MISS_NUM ) THEN
1246 --{
1247 l_batch_in_rec.ship_confirm_rule_Id := NULL;
1248 --}
1249 END IF;
1250 IF ( l_batch_in_rec.ship_confirm_rule_name = FND_API.G_MISS_CHAR) THEN
1251 --{
1252 l_batch_in_rec.ship_confirm_rule_name := NULL;
1253 --}
1254 END IF;
1255 IF ( ( l_batch_in_rec.ship_confirm_rule_Id IS NOT NULL) OR ( l_batch_in_rec.ship_confirm_rule_Name IS NOT NULL ) ) THEN
1256 --{
1257 IF l_debug_on THEN
1258 --{
1259 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_SHIP_CON_RULE_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1260 --}
1261 END IF;
1262 WSH_UTIL_VALIDATE.Validate_Ship_Con_Rule_Name(
1263 p_ship_confirm_rule_id => l_batch_in_rec.ship_confirm_rule_Id,
1264 p_ship_confirm_rule_name => l_batch_in_rec.ship_confirm_rule_Name,
1265 x_return_status => l_return_status);
1266
1267 IF l_debug_on THEN
1268 --{
1269 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1270 --}
1271 END IF;
1272 --
1273 wsh_util_core.api_post_call(
1274 p_return_status => l_return_status,
1275 x_num_warnings => l_number_of_warnings,
1276 x_num_errors => l_number_of_errors,
1277 p_msg_data => l_msg_data
1278 );
1279
1280 l_batch_grp_rec.ship_confirm_rule_Id :=l_batch_in_rec.ship_confirm_rule_Id;
1281 ELSE
1282 l_batch_grp_rec.ship_confirm_rule_Id := NULL;
1283 --}
1284 END IF;
1285 --} Passed the Values or Not
1286 END IF;
1287
1288 -- End of ship_confirm_Rule_Id Validations
1289 --{
1290 --
1291 -- Posco ER : By now, we know for sure whether a Ship Confirm Rule
1292 -- has to be used with this batch or NOT
1293 --
1294 -- So, proceed to validate the Actual Departure date
1295 --
1296 IF l_batch_grp_rec.ship_confirm_rule_id IS NULL THEN
1297 --{
1298 IF l_batch_in_rec.actual_departure_date IS NOT NULL THEN
1299 --
1300 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_ACTUAL_DEP_DATE');
1301 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1302 wsh_util_core.add_message(x_return_status);
1303 --
1304 IF l_debug_on THEN
1305 wsh_debug_sv.logmsg(l_module_name, 'Error - WSH_NO_ACTUAL_DEP_DATE');
1306 wsh_debug_sv.pop(l_module_name);
1307 END IF;
1308 --
1309 RETURN;
1310 --
1311 END IF;
1312 --}
1313 ELSE
1314 --{
1315 IF l_batch_in_rec.actual_departure_date IS NOT NULL THEN
1316 --{
1317 WSH_UTIL_VALIDATE.ValidateActualDepartureDate(
1318 p_ship_confirm_rule_id => l_batch_grp_rec.ship_confirm_rule_id,
1319 p_actual_departure_date => l_batch_in_rec.actual_departure_date,
1320 x_return_status => l_return_status);
1321 --
1322 wsh_util_core.api_post_call(
1323 p_return_status => l_return_status,
1324 x_num_warnings => l_number_of_warnings,
1325 x_num_errors => l_number_of_errors,
1326 p_msg_data => l_msg_data
1327 );
1328 --
1329 l_batch_grp_rec.actual_departure_date := l_batch_in_rec.actual_departure_date;
1330 --}
1331 ELSE
1332 l_batch_grp_rec.actual_departure_date := NULL;
1333 END IF;
1334 --}
1335 END IF;
1336 --
1337 --}
1338 --
1339 --- Carrier Validations
1340 -- Bug#: 3266659 - Removing carrier validations
1341 /*
1342 IF ( ( l_batch_in_rec.Carrier_Id IS NOT NULL ) OR ( l_batch_in_rec.Carrier_Name IS NOT NULL ) ) THEN
1343 --{ Passed the Values or Not
1344 IF ( l_batch_in_rec.Carrier_Id = FND_API.G_MISS_NUM ) THEN
1345 --{
1346 l_batch_in_rec.Carrier_Id := NULL;
1347 --}
1348 END IF;
1349 IF ( l_batch_in_rec.Carrier_name = FND_API.G_MISS_CHAR ) THEN
1350 --{
1351 l_batch_in_rec.Carrier_Name := NULL;
1352 --}
1353 END IF;
1354 IF ( ( l_batch_in_rec.Carrier_Id IS NOT NULL ) OR ( l_batch_in_rec.Carrier_Name IS NOT NULL ) ) THEN
1355 --{
1356 IF l_debug_on THEN
1357 --{
1358 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_CARRIER',WSH_DEBUG_SV.C_PROC_LEVEL);
1359 --}
1360 END IF;
1361 WSH_UTIL_VALIDATE.Validate_Carrier(
1362 x_Carrier_id => l_batch_in_rec.Carrier_Id,
1363 p_Carrier_name => l_batch_in_rec.Carrier_Name,
1364 x_return_status => l_return_status);
1365
1366 IF l_debug_on THEN
1367 --{
1368 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1369 --}
1370 END IF;
1371 --
1372 wsh_util_core.api_post_call(
1373 p_return_status => l_return_status,
1374 x_num_warnings => l_number_of_warnings,
1375 x_num_errors => l_number_of_errors,
1376 p_msg_data => l_msg_data
1377 );
1378
1379 l_batch_grp_rec.Carrier_Id := l_batch_in_rec.Carrier_Id;
1380 ELSE
1381 l_batch_grp_rec.Carrier_Id := NULL;
1382 --}
1383 END IF;
1384 --} Passed the Values or Not
1385 END IF;
1386 */
1387 -- End of Carrier Validations
1388
1389 -- Inventory Item Id Validations
1390
1391 IF ( l_batch_in_rec.Inventory_Item_Id IS NOT NULL ) THEN
1392 --{ Passed the Values or Not
1393 IF ( l_batch_in_rec.Inventory_Item_Id <> FND_API.G_MISS_NUM ) THEN
1394 --{
1395 OPEN Check_Item_id(l_batch_in_rec.Inventory_Item_Id,l_batch_grp_rec.organization_id);
1396 FETCH Check_Item_id INTO l_batch_in_rec.Inventory_Item_Id;
1397 IF ( Check_Item_id%NOTFOUND ) THEN
1398 --{
1399 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1400 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','INVENTORY_ITEM_ID');
1401 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1402 wsh_util_core.add_message(x_return_status);
1403 CLOSE Check_Item_id;
1404 return;
1405 --}
1406 END IF;
1407 CLOSE Check_Item_id;
1408 l_batch_grp_rec.Inventory_Item_Id := l_batch_in_rec.Inventory_Item_Id;
1409 ELSE
1410 l_batch_grp_rec.Inventory_Item_Id := NULL;
1411 --}
1412 END IF;
1413 --} Passed the Values or Not
1414 END IF;
1415
1416 -- End of Inventory Item Id Validations
1417
1418
1419 -- Subinventory and locators are considered only when the organization is mentioned
1420
1421 IF ( l_batch_grp_rec.organization_id IS NOT NULL ) THEN
1422 --{ Check subinventories and locators only when Org is specified
1423 -- Default_Stage_Subinventory Validations
1424 IF ( l_batch_in_rec.Default_Stage_Subinventory IS NOT NULL ) THEN
1425 --{ Passed the Values or Not
1426 IF ( l_batch_in_rec.Default_Stage_Subinventory <> FND_API.G_MISS_CHAR) THEN
1427 --{
1428 OPEN Check_Subinventory( l_batch_in_rec.Default_Stage_Subinventory,l_batch_grp_rec.organization_id);
1429 FETCH Check_Subinventory INTO l_batch_in_rec.Default_Stage_Subinventory;
1430 IF ( Check_Subinventory%NOTFOUND ) THEN
1431 --{
1432 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1433 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','DEFAULT_STAGE_SUBINVENTORY');
1434 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1435 wsh_util_core.add_message(x_return_status);
1436 CLOSE Check_Subinventory;
1437 return;
1438 --}
1439 END IF;
1440 CLOSE Check_Subinventory;
1441 l_batch_grp_rec.Default_Stage_Subinventory := l_batch_in_rec.Default_Stage_Subinventory;
1442 ELSE
1443 l_batch_grp_rec.Default_Stage_Subinventory := NULL;
1444 --}
1445 END IF;
1446 --} Passed the Values or Not
1447 END IF;
1448 -- End of Default_Stage_Subinventory Validations
1449 --Bug8221008 Validations for rel_subinventory
1450 IF ( l_batch_in_rec.rel_subinventory IS NOT NULL ) THEN
1451 --{ Passed the Values or Not
1452 IF ( l_batch_in_rec.rel_subinventory <> FND_API.G_MISS_CHAR) THEN
1453 --{
1454 OPEN Check_Subinventory( l_batch_in_rec.rel_subinventory,l_batch_grp_rec.organization_id);
1455 FETCH Check_Subinventory INTO l_batch_in_rec.rel_subinventory;
1456 IF ( Check_Subinventory%NOTFOUND ) THEN
1457 --{
1458 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1459 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','REL_SUBINVENTORY');
1460 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1461 wsh_util_core.add_message(x_return_status);
1462 CLOSE Check_Subinventory;
1463 return;
1464 --}
1465 END IF;
1466 CLOSE Check_Subinventory;
1467 l_batch_grp_rec.rel_subinventory := l_batch_in_rec.rel_subinventory;
1468 ELSE
1469 l_batch_grp_rec.rel_subinventory := NULL;
1470 --}
1471 END IF;
1472 --} Passed the Values or Not
1473 END IF;
1474 --Bug8221008 End of validations for rel_subinventory
1475
1476 -- Default_Stage_Locator_Id Validations
1477 IF ( l_batch_in_rec.Default_Stage_Locator_Id IS NOT NULL ) THEN
1478 --{ Passed the Values or Not
1479 IF (l_batch_in_rec.Default_Stage_Locator_Id <> FND_API.G_MISS_NUM ) THEN
1480 --{
1481 OPEN Check_Locator_Id( l_batch_in_rec.Default_Stage_Locator_Id,l_batch_grp_rec.Default_Stage_Subinventory,l_batch_grp_rec.organization_id);
1482 FETCH Check_Locator_Id INTO l_batch_in_rec.Default_Stage_Locator_Id;
1483 IF ( Check_Locator_Id%NOTFOUND ) THEN
1484 --{
1485 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1486 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','DEFAULT_STAGE_LOCATOR_ID');
1487 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1488 wsh_util_core.add_message(x_return_status);
1489 CLOSE Check_Locator_Id;
1490 return;
1491 --}
1492 END IF;
1493 CLOSE Check_Locator_Id;
1494 l_batch_grp_rec.Default_Stage_Locator_Id := l_batch_in_rec.Default_Stage_Locator_Id;
1495 ELSE
1496 l_batch_grp_rec.Default_Stage_Locator_Id := NULL;
1497 --}
1498 END IF;
1499 --} Passed the Values or Not
1500 END IF;
1501 -- End of Default_Stage_Locator_Id Validations
1502
1503
1504 -- Pick_From_Subinventory Validations
1505 IF ( l_batch_in_rec.Pick_From_Subinventory IS NOT NULL ) THEN
1506 --{ Passed the Values or Not
1507 IF ( l_batch_in_rec.Pick_From_Subinventory <> FND_API.G_MISS_CHAR) THEN
1508 --{
1509 OPEN Check_Subinventory( l_batch_in_rec.Pick_From_Subinventory,l_batch_grp_rec.organization_id);
1510 FETCH Check_Subinventory INTO l_batch_in_rec.Pick_From_Subinventory;
1511 IF ( Check_Subinventory%NOTFOUND ) THEN
1512 --{
1513 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1514 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','PICK_FROM_SUBINVENTORY');
1515 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1516 wsh_util_core.add_message(x_return_status);
1517 CLOSE Check_Subinventory;
1518 return;
1519 --}
1520 END IF;
1521 CLOSE Check_Subinventory;
1522 l_batch_grp_rec.Pick_From_Subinventory := l_batch_in_rec.Pick_From_Subinventory;
1523 ELSE
1524 l_batch_grp_rec.Pick_From_Subinventory := NULL;
1525 --}
1526 END IF;
1527 --} Passed the Values or Not
1528 END IF;
1529 -- End of Pick_From_Subinventory Validations
1530
1531 -- Pick_From_locator_Id Validations
1532 IF ( l_batch_in_rec.Pick_From_locator_Id IS NOT NULL ) THEN
1533 --{ Passed the Values or Not
1534 IF (l_batch_in_rec.Pick_From_locator_Id <> FND_API.G_MISS_NUM ) THEN
1535 --{
1536 OPEN Check_Locator_Id( l_batch_in_rec.Pick_From_locator_Id,l_batch_grp_rec.Pick_From_Subinventory,l_batch_grp_rec.organization_id );
1537 FETCH Check_Locator_Id INTO l_batch_in_rec.Pick_From_locator_Id;
1538 IF ( Check_Locator_Id%NOTFOUND ) THEN
1539 --{
1540 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1541 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','PICK_FROM_LOCATOR_ID');
1542 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1543 wsh_util_core.add_message(x_return_status);
1544 CLOSE Check_Locator_Id;
1545 return;
1546 --}
1547 END IF;
1548 CLOSE Check_Locator_Id;
1549 l_batch_grp_rec.Pick_From_locator_Id := l_batch_in_rec.Pick_From_locator_Id;
1550 ELSE
1551 l_batch_grp_rec.Pick_From_locator_Id := NULL;
1552 --}
1553 END IF;
1554 --} Passed the Values or Not
1555 END IF;
1556 -- End of Pick_From_locator_Id Validations
1557 -- } Check subinventories and locators only when Org is specified
1558 END IF;
1559
1560 -- End of subinventory and locator validations
1561
1562 -- Delivery_Detail_Id Validations
1563
1564 IF ( l_batch_in_rec.Delivery_Detail_Id IS NOT NULL ) THEN
1565 --{ Passed the Values or Not
1566 IF ( l_batch_in_rec.Delivery_Detail_Id <> FND_API.G_MISS_NUM ) THEN
1567 --{
1568 IF (l_batch_in_rec.Delivery_Detail_Id = -1 ) THEN
1569 --{ Bug# 7505524, in wave pick release case, wms is passing dd as -1.
1570 l_batch_grp_rec.Delivery_Detail_Id := l_batch_in_rec.Delivery_Detail_Id;
1571 ELSE
1572 l_batch_grp_rec.Delivery_Detail_Id := l_batch_in_rec.Delivery_Detail_Id;
1573 OPEN Check_Delivery_Detail_Id(l_batch_in_rec.Delivery_Detail_Id);
1574 FETCH Check_Delivery_Detail_Id INTO l_batch_in_rec.Delivery_Detail_Id;
1575 IF ( Check_Delivery_Detail_Id%NOTFOUND ) THEN
1576 --{
1577 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1578 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','DELIVERY_DETAIL_ID');
1579 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1580 wsh_util_core.add_message(x_return_status);
1581 CLOSE Check_Delivery_Detail_Id;
1582 return;
1583 --}
1584 END IF;
1585 CLOSE Check_Delivery_Detail_Id;
1586 l_batch_grp_rec.Delivery_Detail_Id := l_batch_in_rec.Delivery_Detail_Id;
1587 --}
1588 END IF;
1589 ELSE
1590 l_batch_grp_rec.Delivery_Detail_Id := NULL;
1591 --}
1592 END IF;
1593 --} Passed the Values or Not
1594 END IF;
1595
1596 -- End of Delivery_Detail_Id Validations
1597
1598 -- Category_Set_ID Validations
1599 IF ( l_batch_in_rec.Category_Set_Id IS NOT NULL ) THEN
1600 --{ Passed the Values or Not
1601 IF ( l_batch_in_rec.Category_Set_Id <> FND_API.G_MISS_NUM ) THEN
1602 --{
1603 OPEN Check_Category_Set_Id(l_batch_in_rec.Category_Set_Id);
1604 FETCH Check_Category_Set_Id INTO l_batch_in_rec.Category_Set_Id;
1605 IF ( Check_Category_Set_Id%NOTFOUND ) THEN
1606 --{
1607 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1608 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CATEGORY_SET_ID');
1609 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1610 wsh_util_core.add_message(x_return_status);
1611 CLOSE Check_Category_Set_Id;
1612 return;
1613 --}
1614 END IF;
1615 CLOSE Check_Category_Set_Id;
1616 l_batch_grp_rec.Category_Set_Id := l_batch_in_rec.Category_Set_Id;
1617 ELSE
1618 l_batch_grp_rec.Category_Set_Id := NULL;
1619 --}
1620 END IF;
1621 --} Passed the Values or Not
1622 END IF;
1623 -- End of Category_Set_ID Validations
1624
1625
1626 -- Category_ID Validations
1627 IF ( l_batch_grp_rec.Category_Set_Id IS NOT NULL ) THEN
1628 --{ Set_Id
1629 IF ( l_batch_in_rec.Category_Id IS NOT NULL ) THEN
1630 --{ Passed the Values or Not
1631 IF ( l_batch_in_rec.Category_Id <> FND_API.G_MISS_NUM ) THEN
1632 --{
1633 OPEN Check_Category_Id(l_batch_in_rec.Category_Id);
1634 FETCH Check_Category_Id INTO l_batch_in_rec.Category_Id;
1635 IF ( Check_Category_Id%NOTFOUND ) THEN
1636 --{
1637 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1638 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CATEGORY_ID');
1639 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1640 wsh_util_core.add_message(x_return_status);
1641 CLOSE Check_Category_Id;
1642 return;
1643 --}
1644 END IF;
1645 CLOSE Check_Category_Id;
1646 l_batch_grp_rec.Category_Id := l_batch_in_rec.Category_Id;
1647 ELSE
1648 l_batch_grp_rec.Category_Id := NULL;
1649 --}
1650 END IF;
1651 --} Passed the Values or Not
1652 END IF;
1653 --} Set_Id
1654 END IF;
1655
1656 -- End of Category_ID Validations
1657
1658 -- Ignore_Shipset_Smc Validations
1659
1660 IF ( l_batch_in_rec.Ship_Set_Smc_Flag IS NOT NULL ) THEN
1661 --{ Passed the Values or Not
1662 IF ( l_batch_in_rec.Ship_Set_Smc_Flag <> FND_API.G_MISS_CHAR ) THEN
1663 --{
1664 IF ( l_batch_in_rec.Ship_Set_Smc_Flag NOT IN ('I','E','A') ) THEN
1665 --{
1666 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1667 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','SHIP_SET_SMC_FLAG');
1668 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1669 wsh_util_core.add_message(x_return_status);
1670 return;
1671 --}
1672 END IF;
1673 l_batch_grp_rec.Ship_Set_Smc_Flag :=l_batch_in_rec.Ship_Set_Smc_Flag;
1674 ELSE
1675 l_batch_grp_rec.Ship_Set_Smc_Flag := NULL;
1676 --}
1677 END IF;
1678 --} Passed the Values or Not
1679 END IF;
1680
1681 -- End of Ignore_Shipset_Smc Validations
1682
1683
1684 -- Auto_Pick_Confirm_Flag Validations
1685 IF ( l_batch_in_rec.Auto_Pick_Confirm_Flag IS NOT NULL ) THEN
1686 --{ Passed the Values or Not
1687 IF (l_batch_in_rec.Auto_Pick_Confirm_Flag <> FND_API.G_MISS_CHAR) THEN
1688 --{
1689 IF ( l_batch_in_rec.Auto_Pick_Confirm_Flag NOT IN ('Y','N') ) THEN
1690 --{
1691 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1692 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','AUTO_PICK_CONFIRM_FLAG');
1693 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1694 wsh_util_core.add_message(x_return_status);
1695 return;
1696 --}
1697 END IF;
1698 l_batch_grp_rec.Auto_Pick_Confirm_Flag := l_batch_in_rec.Auto_Pick_Confirm_Flag;
1699 ELSE
1700 l_batch_grp_rec.Auto_Pick_Confirm_Flag := NULL;
1701 --}
1702 END IF;
1703 --} Passed the Values or Not
1704 END IF;
1705 -- End of Auto_Pick_Confirm_Flag Validations
1706
1707 -- Validating the Existing Reservations Only Flag
1708
1709 IF ( l_batch_in_rec.Existing_Rsvs_Only_Flag IS NOT NULL ) THEN
1710 --{ Passed the Values or Not
1711 IF (l_batch_in_rec.Existing_Rsvs_Only_Flag <> FND_API.G_MISS_CHAR) THEN
1712 --{
1713 IF ( l_batch_in_rec.Existing_Rsvs_Only_Flag NOT IN ('N','Y')) THEN
1714 --{
1715 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1716 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','EXISTING_RSVS_ONLY_FLAG');
1717 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1718 wsh_util_core.add_message(x_return_status);
1719 return;
1720 --}
1721 END IF;
1722 l_batch_grp_rec.Existing_Rsvs_Only_Flag := l_batch_in_rec.Existing_Rsvs_Only_Flag;
1723 ELSE
1724 l_batch_grp_rec.Existing_Rsvs_Only_Flag := NULL;
1725 --}
1726 END IF;
1727 --} Passed the Values or Not
1728 END IF;
1729
1730 -- End of Validating the Existing Reservations Only Flag
1731
1732 -- Include Planned Lines Validations
1733
1734 IF ( l_batch_in_rec.Include_Planned_Lines IS NOT NULL ) THEN
1735 --{ Passed the Values or Not
1736 IF (l_batch_in_rec.Include_Planned_Lines <> FND_API.G_MISS_CHAR) THEN
1737 --{
1738 IF ( l_batch_in_rec.Include_Planned_Lines NOT IN ('Y','N') ) THEN
1739 --{
1740 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1741 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','INCLUDE_PLANNED_LINES');
1742 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1743 wsh_util_core.add_message(x_return_status);
1744 return;
1745 --}
1746 END IF;
1747 l_batch_grp_rec.Include_Planned_Lines := l_batch_in_rec.Include_Planned_Lines;
1748 ELSE
1749 l_batch_grp_rec.Include_Planned_Lines := NULL;
1750 --}
1751 END IF;
1752 --} Passed the Values or Not
1753 END IF;
1754
1755 -- End of Include Planned Lines Validations
1756
1757 -- Autodetail_Pr_Flag Validations
1758
1759 IF ( l_batch_in_rec.Autodetail_Pr_Flag IS NOT NULL ) THEN
1760 --{ Passed the Values or Not
1761 IF (l_batch_in_rec.Autodetail_Pr_Flag <> FND_API.G_MISS_CHAR) THEN
1762 --{
1763 IF ( l_batch_in_rec.Autodetail_Pr_Flag NOT IN ('Y','N') ) THEN
1764 --{
1765 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1766 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','AUTODETAIL_PR_FLAG');
1767 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1768 wsh_util_core.add_message(x_return_status);
1769 return;
1770 --}
1771 END IF;
1772 l_batch_grp_rec.Autodetail_Pr_Flag :=l_batch_in_rec.Autodetail_Pr_Flag;
1773 ELSE
1774 l_batch_grp_rec.Autodetail_Pr_Flag := NULL;
1775 --}
1776 END IF;
1777 --} Passed the Values or Not
1778 END IF;
1779
1780 -- End of Autodetail_Pr_Flag Validations
1781
1782
1783 -- Autopack_Flag Validations
1784
1785 IF ( l_batch_in_rec.Autopack_Flag IS NOT NULL ) THEN
1786 --{ Passed the Values or Not
1787 IF (l_batch_in_rec.Autopack_Flag <> FND_API.G_MISS_CHAR) THEN
1788 --{
1789 IF ( l_batch_in_rec.Autopack_Flag NOT IN ('Y','N') ) THEN
1790 --{
1791 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1792 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','AUTOPACK_FLAG');
1793 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1794 wsh_util_core.add_message(x_return_status);
1795 return;
1796 --}
1797 END IF;
1798 l_batch_grp_rec.Autopack_Flag :=l_batch_in_rec.Autopack_Flag;
1799 ELSE
1800 l_batch_grp_rec.Autopack_Flag := NULL;
1801 --}
1802 END IF;
1803 --} Passed the Values or Not
1804 END IF;
1805
1806 -- End of Autopack_Flag Validations
1807
1808 -- Autopack_Level Validations
1809 -- Consider the Autopack_level only when the Autopack_Flag set to "Y"
1810 IF ( l_batch_grp_rec.Autopack_Flag = 'Y' ) THEN
1811 --{
1812 IF ( l_batch_in_rec.Autopack_Level IS NOT NULL ) THEN
1813 --{ Passed the Values or Not
1814 IF (l_batch_in_rec.Autopack_Level <> FND_API.G_MISS_NUM) THEN
1815 --{
1816 IF ( l_batch_in_rec.Autopack_Level NOT IN (0,1,2) ) THEN
1817 --{
1818 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1819 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','AUTOPACK_LEVEL');
1820 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1821 wsh_util_core.add_message(x_return_status);
1822 return;
1823 --}
1824 END IF;
1825 l_batch_grp_rec.Autopack_Level := l_batch_in_rec.Autopack_Level;
1826 ELSE
1827 l_batch_grp_rec.Autopack_Level := NULL;
1828 --}
1829 END IF;
1830 --} Passed the Values or Not
1831 END IF;
1832 --}
1833 END IF;
1834
1835 -- End of Autopack_Level Validations
1836
1837 -- Task_Planning_Flag Validations
1838
1839 IF ( l_batch_in_rec.Task_Planning_Flag IS NOT NULL ) THEN
1840 --{ Passed the Values or Not
1841 IF (l_batch_in_rec.Task_Planning_Flag <> FND_API.G_MISS_CHAR) THEN
1842 --{
1843 IF ( l_batch_in_rec.Task_Planning_Flag NOT IN ('Y','N') ) THEN
1844 --{
1845 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1846 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','TASK_PLANNING_FLAG');
1847 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1848 wsh_util_core.add_message(x_return_status);
1849 return;
1850 --}
1851 END IF;
1852 l_batch_grp_rec.Task_Planning_Flag :=l_batch_in_rec.Task_Planning_Flag;
1853 ELSE
1854 l_batch_grp_rec.Task_Planning_Flag := NULL;
1855 --}
1856 END IF;
1857 --} Passed the Values or Not
1858 END IF;
1859 -- End of Task_Planning_Flag Validations
1860
1861 -- Bug# 3480908 - Start - If the org is WMS enabled, and Plan task is yes then auto alocate is yes.
1862 IF l_batch_grp_rec.Task_Planning_Flag = 'Y' AND l_is_WMS_org = 'Y' THEN
1863 l_batch_grp_rec.Autodetail_Pr_Flag := 'Y';
1864 END IF;
1865 -- Bug# 3480908 - End
1866
1867
1868 --bug# 6689448 (replenishment project): begin
1869 -- dynamic_replenishment_flag Validations
1870
1871 IF ( l_batch_in_rec.dynamic_replenishment_flag IS NOT NULL ) THEN
1872 --{ Passed the Values or Not
1873 IF (l_batch_in_rec.dynamic_replenishment_flag <> FND_API.G_MISS_CHAR) THEN
1874 --{
1875 IF ( l_batch_in_rec.dynamic_replenishment_flag NOT IN ('Y','N') ) THEN
1876 --{
1877 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1878 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','dynamic_replenishment_flag');
1879 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1880 wsh_util_core.add_message(x_return_status);
1881 return;
1882 --}
1883 END IF;
1884 l_batch_grp_rec.dynamic_replenishment_flag :=l_batch_in_rec.dynamic_replenishment_flag;
1885 ELSE
1886 l_batch_grp_rec.dynamic_replenishment_flag := NULL;
1887 --}
1888 END IF;
1889 --} Passed the Values or Not
1890 END IF;
1891 -- End of dynamic_replenishment_flag Validations
1892
1893 -- If the org is WMS enabled, and Plan task is yes then auto alocate is yes.
1894 IF l_batch_grp_rec.dynamic_replenishment_flag = 'Y' AND l_is_WMS_org = 'Y' THEN
1895 l_batch_grp_rec.Autodetail_Pr_Flag := 'Y';
1896 END IF;
1897
1898 --bug# 6689448 (replenishment project): end
1899
1900 -- Non_Picking_flag Validations
1901 -- Bug#: 3266659 - Removing Non_Picking_flag validations
1902 /*
1903 IF ( l_batch_in_rec.Non_Picking_flag IS NOT NULL ) THEN
1904 --{ Passed the Values or Not
1905 IF (l_batch_in_rec.Non_Picking_flag <> FND_API.G_MISS_CHAR) THEN
1906 --{
1907 IF ( l_batch_in_rec.Non_Picking_flag NOT IN ('Y','N') ) THEN
1908 --{
1909 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
1910 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','NON_PICKING_FLAG');
1911 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1912 wsh_util_core.add_message(x_return_status);
1913 return;
1914 --}
1915 END IF;
1916 l_batch_grp_rec.Non_Picking_flag :=l_batch_in_rec.Non_Picking_flag;
1917 ELSE
1918 l_batch_grp_rec.Non_Picking_flag := NULL;
1919 --}
1920 END IF;
1921 --} Passed the Values or Not
1922 END IF;
1923 */
1924 -- End of Non_Picking_flag Validations
1925
1926 -- Shipment_Priority_Code Validations
1927
1928 IF ( l_batch_in_rec.Shipment_Priority_Code IS NOT NULL ) THEN
1929 --{
1930 IF (l_batch_in_rec.Shipment_Priority_Code <> FND_API.G_MISS_CHAR) THEN
1931 --{
1932 l_batch_grp_rec.Shipment_Priority_Code := l_batch_in_rec.Shipment_Priority_Code; --Bugfix 10121045
1933 ELSE
1934 l_batch_grp_rec.Shipment_Priority_Code := NULL; --Bugfix 10121045
1935 --}
1936 END IF;
1937 --}
1938 END IF;
1939
1940 -- End of Validating the Shipment_Priority_Code
1941
1942
1943 -- Project_Id Validations
1944 IF ( l_batch_in_rec.Project_Id IS NOT NULL ) THEN
1945 --{
1946 IF ( l_batch_in_rec.Project_Id <> FND_API.G_MISS_NUM ) THEN
1947 --{
1948 l_batch_grp_rec.Project_Id := l_batch_in_rec.Project_Id;
1949 ELSE
1950 l_batch_grp_rec.Project_Id := NULL;
1951 --}
1952 END IF;
1953 --}
1954 END IF;
1955 -- End of Project_Id Validations
1956
1957
1958 -- Task_Id Validations
1959 IF ( l_batch_in_rec.Task_Id IS NOT NULL ) THEN
1960 --{
1961 IF ( l_batch_in_rec.Task_Id <> FND_API.G_MISS_NUM ) THEN
1962 --{
1963 l_batch_grp_rec.Task_Id := l_batch_in_rec.Task_Id;
1964 ELSE
1965 l_batch_grp_rec.Task_Id := NULL;
1966 --}
1967 END IF;
1968 --}
1969 END IF;
1970 -- End of Task_Id Validations
1971
1972 -- From Requested Date Validations
1973
1974 IF ( l_batch_in_rec.From_Requested_Date IS NOT NULL ) THEN
1975 --{
1976 IF ( l_batch_in_rec.From_Requested_Date <> FND_API.G_MISS_DATE ) THEN
1977 --{
1978 l_batch_grp_rec.From_Requested_Date := l_batch_in_rec.From_Requested_Date;
1979 ELSE
1980 l_batch_grp_rec.From_Requested_Date := NULL;
1981 --}
1982 END IF;
1983 --}
1984 END IF;
1985
1986 -- End of From Requested Date Validations
1987
1988 -- To Requested Date Validations
1989
1990 IF ( l_batch_in_rec.To_Requested_Date IS NOT NULL ) THEN
1991 --{
1992 IF ( l_batch_in_rec.To_Requested_Date <> FND_API.G_MISS_DATE ) THEN
1993 --{
1994 l_batch_grp_rec.To_Requested_Date := l_batch_in_rec.To_Requested_Date;
1995 ELSE
1996 l_batch_grp_rec.To_Requested_Date := NULL;
1997 --}
1998 END IF;
1999 --}
2000 END IF;
2001
2002 -- End of To_Requested_Date Validations
2003
2004 -- From Scheduled Ship Date Validations
2005
2006 IF ( l_batch_in_rec.From_Scheduled_Ship_Date IS NOT NULL ) THEN
2007 --{
2008 IF ( l_batch_in_rec.From_Scheduled_Ship_Date <> FND_API.G_MISS_DATE ) THEN
2009 --{
2010 l_batch_grp_rec.From_Scheduled_Ship_Date := l_batch_in_rec.From_Scheduled_Ship_Date;
2011 ELSE
2012 l_batch_grp_rec.From_Scheduled_Ship_Date := NULL;
2013 --}
2014 END IF;
2015 --}
2016 END IF;
2017
2018 -- End of From_Scheduled_Ship_Date Validations
2019
2020 -- To Scheduled Ship Date Validations
2021
2022 IF ( l_batch_in_rec.To_Scheduled_Ship_Date IS NOT NULL ) THEN
2023 --{
2024 IF ( l_batch_in_rec.To_Scheduled_Ship_Date <> FND_API.G_MISS_DATE ) THEN
2025 --{
2026 l_batch_grp_rec.To_Scheduled_Ship_Date := l_batch_in_rec.To_Scheduled_Ship_Date;
2027 ELSE
2028 l_batch_grp_rec.To_Scheduled_Ship_Date := NULL;
2029 --}
2030 END IF;
2031 --}
2032 END IF;
2033
2034 -- End of To Scheduled Ship Date Validations
2035
2036 -- Validate all Attribute Fields
2037 IF ( l_batch_in_rec.Attribute_Category IS NOT NULL ) THEN
2038 --{
2039 IF ( l_batch_in_rec.Attribute_Category <> FND_API.G_MISS_CHAR ) THEN
2040 --{
2041 l_batch_grp_rec.Attribute_Category := l_batch_in_rec.Attribute_Category;
2042 ELSE
2043 l_batch_grp_rec.Attribute_Category := NULL;
2044 --}
2045 END IF;
2046 --}
2047 END IF;
2048
2049 IF ( l_batch_in_rec.Attribute1 IS NOT NULL ) THEN
2050 --{
2051 IF ( l_batch_in_rec.Attribute1 <> FND_API.G_MISS_CHAR ) THEN
2052 --{
2053 l_batch_grp_rec.Attribute1 := l_batch_in_rec.Attribute1;
2054 ELSE
2055 l_batch_grp_rec.Attribute1 := NULL;
2056 --}
2057 END IF;
2058 --}
2059 END IF;
2060
2061 IF ( l_batch_in_rec.Attribute2 IS NOT NULL ) THEN
2062 --{
2063 IF ( l_batch_in_rec.Attribute2 <> FND_API.G_MISS_CHAR ) THEN
2064 --{
2065 l_batch_grp_rec.Attribute2 := l_batch_in_rec.Attribute2;
2066 ELSE
2067 l_batch_grp_rec.Attribute2 := NULL;
2068 --}
2069 END IF;
2070 --}
2071 END IF;
2072
2073 IF ( l_batch_in_rec.Attribute3 IS NOT NULL ) THEN
2074 --{
2075 IF ( l_batch_in_rec.Attribute3 <> FND_API.G_MISS_CHAR ) THEN
2076 --{
2077 l_batch_grp_rec.Attribute3 := l_batch_in_rec.Attribute3;
2078 ELSE
2079 l_batch_grp_rec.Attribute3 := NULL;
2080 --}
2081 END IF;
2082 --}
2083 END IF;
2084
2085 IF ( l_batch_in_rec.Attribute4 IS NOT NULL ) THEN
2086 --{
2087 IF ( l_batch_in_rec.Attribute4 <> FND_API.G_MISS_CHAR ) THEN
2088 --{
2089 l_batch_grp_rec.Attribute4 := l_batch_in_rec.Attribute4;
2090 ELSE
2091 l_batch_grp_rec.Attribute4 := NULL;
2092 --}
2093 END IF;
2094 --}
2095 END IF;
2096
2097 IF ( l_batch_in_rec.Attribute5 IS NOT NULL ) THEN
2098 --{
2099 IF ( l_batch_in_rec.Attribute5 <> FND_API.G_MISS_CHAR ) THEN
2100 --{
2101 l_batch_grp_rec.Attribute5 := l_batch_in_rec.Attribute5;
2102 ELSE
2103 l_batch_grp_rec.Attribute5 := NULL;
2104 --}
2105 END IF;
2106 --}
2107 END IF;
2108
2109 IF ( l_batch_in_rec.Attribute6 IS NOT NULL ) THEN
2110 --{
2111 IF ( l_batch_in_rec.Attribute6 <> FND_API.G_MISS_CHAR ) THEN
2112 --{
2113 l_batch_grp_rec.Attribute6 := l_batch_in_rec.Attribute6;
2114 ELSE
2115 l_batch_grp_rec.Attribute6 := NULL;
2116 --}
2117 END IF;
2118 --}
2119 END IF;
2120
2121 IF ( l_batch_in_rec.Attribute7 IS NOT NULL ) THEN
2122 --{
2123 IF ( l_batch_in_rec.Attribute7 <> FND_API.G_MISS_CHAR ) THEN
2124 --{
2125 l_batch_grp_rec.Attribute7 := l_batch_in_rec.Attribute7;
2126 ELSE
2127 l_batch_grp_rec.Attribute7 := NULL;
2128 --}
2129 END IF;
2130 --}
2131 END IF;
2132
2133 IF ( l_batch_in_rec.Attribute8 IS NOT NULL ) THEN
2134 --{
2135 IF ( l_batch_in_rec.Attribute8 <> FND_API.G_MISS_CHAR ) THEN
2136 --{
2137 l_batch_grp_rec.Attribute8 := l_batch_in_rec.Attribute8;
2138 ELSE
2139 l_batch_grp_rec.Attribute8 := NULL;
2140 --}
2141 END IF;
2142 --}
2143 END IF;
2144
2145 IF ( l_batch_in_rec.Attribute9 IS NOT NULL ) THEN
2146 --{
2147 IF ( l_batch_in_rec.Attribute9 <> FND_API.G_MISS_CHAR ) THEN
2148 --{
2149 l_batch_grp_rec.Attribute9 := l_batch_in_rec.Attribute9;
2150 ELSE
2151 l_batch_grp_rec.Attribute9 := NULL;
2152 --}
2153 END IF;
2154 --}
2155 END IF;
2156
2157 IF ( l_batch_in_rec.Attribute10 IS NOT NULL ) THEN
2158 --{
2159 IF ( l_batch_in_rec.Attribute10 <> FND_API.G_MISS_CHAR ) THEN
2160 --{
2161 l_batch_grp_rec.Attribute10 := l_batch_in_rec.Attribute10;
2162 ELSE
2163 l_batch_grp_rec.Attribute10 := NULL;
2164 --}
2165 END IF;
2166 --}
2167 END IF;
2168
2169 IF ( l_batch_in_rec.Attribute11 IS NOT NULL ) THEN
2170 --{
2171 IF ( l_batch_in_rec.Attribute11 <> FND_API.G_MISS_CHAR ) THEN
2172 --{
2173 l_batch_grp_rec.Attribute11 := l_batch_in_rec.Attribute11;
2174 ELSE
2175 l_batch_grp_rec.Attribute11 := NULL;
2176 --}
2177 END IF;
2178 --}
2179 END IF;
2180
2181 IF ( l_batch_in_rec.Attribute12 IS NOT NULL ) THEN
2182 --{
2183 IF ( l_batch_in_rec.Attribute12 <> FND_API.G_MISS_CHAR ) THEN
2184 --{
2185 l_batch_grp_rec.Attribute12 := l_batch_in_rec.Attribute12;
2186 ELSE
2187 l_batch_grp_rec.Attribute12 := NULL;
2188 --}
2189 END IF;
2190 --}
2191 END IF;
2192
2193 IF ( l_batch_in_rec.Attribute13 IS NOT NULL ) THEN
2194 --{
2195 IF ( l_batch_in_rec.Attribute13 <> FND_API.G_MISS_CHAR ) THEN
2196 --{
2197 l_batch_grp_rec.Attribute13 := l_batch_in_rec.Attribute13;
2198 ELSE
2199 l_batch_grp_rec.Attribute13 := NULL;
2200 --}
2201 END IF;
2202 --}
2203 END IF;
2204
2205 IF ( l_batch_in_rec.Attribute14 IS NOT NULL ) THEN
2206 --{
2207 IF ( l_batch_in_rec.Attribute14 <> FND_API.G_MISS_CHAR ) THEN
2208 --{
2209 l_batch_grp_rec.Attribute14 := l_batch_in_rec.Attribute14;
2210 ELSE
2211 l_batch_grp_rec.Attribute14 := NULL;
2212 --}
2213 END IF;
2214 --}
2215 END IF;
2216
2217 IF ( l_batch_in_rec.Attribute15 IS NOT NULL ) THEN
2218 --{
2219 IF ( l_batch_in_rec.Attribute15 <> FND_API.G_MISS_CHAR ) THEN
2220 --{
2221 l_batch_grp_rec.Attribute15 := l_batch_in_rec.Attribute15;
2222 ELSE
2223 l_batch_grp_rec.Attribute15 := NULL;
2224 --}
2225 END IF;
2226 --}
2227 END IF;
2228
2229 -- End of Validating all Attribute Fields
2230
2231 -- Validatiing the Batch Name Prefix
2232 IF ( l_batch_name_prefix = FND_API.G_MISS_CHAR ) THEN
2233 --{
2234 l_batch_name_prefix := NULL;
2235 --}
2236 END IF;
2237 -- End of Validating the Batch Name Prefix
2238
2239 -- Bug 3463315 Append_Flag validation
2240 IF l_batch_in_rec.Append_Flag is NOT NULL THEN
2241 IF l_batch_in_rec.Append_Flag <> FND_API.G_MISS_CHAR THEN
2242 l_batch_grp_rec.Append_Flag := l_batch_in_rec.Append_Flag;
2243 ELSE
2244 l_batch_grp_rec.Append_Flag := NULL;
2245 END IF;
2246 END IF;
2247
2248 -- Validating the Task Priority
2249 IF l_batch_in_rec.task_priority is NOT NULL THEN
2250 IF l_batch_in_rec.task_priority <> FND_API.G_MISS_NUM THEN
2251 l_batch_grp_rec.task_priority := l_batch_in_rec.task_priority;
2252 ELSE
2253 l_batch_grp_rec.task_priority := NULL;
2254 END IF;
2255 END IF;
2256 -- End of Validating the Task Priority
2257
2258 --
2259 -- Autocreate Delivery Flag can only be Y or N
2260 --
2261 IF (l_batch_grp_rec.autocreate_delivery_flag IS NOT NULL AND
2262 l_batch_grp_rec.autocreate_Delivery_flag NOT IN ('Y', 'N')) THEN
2263 --{
2264 FND_MESSAGE.SET_NAME('WSH', 'WSH_OI_INVALID_ATTRIBUTE');
2265 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'AUTOCREATE_DELIVERY_FLAG');
2266 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2267 wsh_util_core.add_message(x_return_status);
2268 --
2269 IF l_debug_on THEN
2270 WSH_DEBUG_SV.pop(l_module_name);
2271 END IF;
2272 --
2273 return;
2274 --}
2275 END IF;
2276 --
2277 --
2278 -- ac_delivery_criteria can be used
2279 -- only if autocreate_delivery_flag is Y
2280 --
2281 IF (l_batch_grp_rec.ac_delivery_criteria IS NOT NULL AND
2282 l_batch_grp_rec.autocreate_delivery_flag <> 'Y') THEN
2283 --{
2284 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_ACDEL_CRITERIA');
2285 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2286 wsh_util_core.add_message(x_return_status, l_module_name);
2287 --
2288 IF l_debug_on THEN
2289 wsh_debug_sv.pop(l_module_name);
2290 END IF;
2291 --
2292 RETURN;
2293 --}
2294 END IF;
2295 --
2296 IF l_batch_grp_rec.Append_Flag = 'Y' or l_batch_grp_rec.Append_Flag is NULL THEN
2297
2298 IF l_batch_grp_rec.organization_id is NULL THEN
2299 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_APPEND_ORGANIZATION');
2300 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2301 wsh_util_core.add_message(x_return_status);
2302 IF l_debug_on THEN
2303 WSH_DEBUG_SV.pop(l_module_name);
2304 END IF;
2305 return;
2306 END IF;
2307
2308 WSH_SHIPPING_PARAMS_PVT.Get(
2309 p_organization_id => l_batch_grp_rec.organization_id,
2310 x_param_info => l_param_info,
2311 x_return_status => l_return_status);
2312
2313 IF l_debug_on THEN
2314 wsh_debug_sv.logmsg(l_module_name, 'Return status from WSH_SHIPPING_PARAMS_PVT.Get is :'||l_return_status );
2315 END IF;
2316
2317 wsh_util_core.api_post_call(
2318 p_return_status => l_return_status,
2319 x_num_warnings => l_number_of_warnings,
2320 x_num_errors => l_number_of_errors,
2321 p_msg_data => l_msg_data
2322 );
2323
2324 IF NVL(l_param_info.appending_limit, 'N') = 'N' THEN
2325 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_APPEND_APPENDING_LIMIT');
2326 FND_MESSAGE.SET_NAME('ORGANIZATION_NAME', WSH_UTIL_CORE.Get_Org_Name(l_batch_grp_rec.organization_id));
2327 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2328 wsh_util_core.add_message(x_return_status);
2329 IF l_debug_on THEN
2330 WSH_DEBUG_SV.pop(l_module_name);
2331 END IF;
2332 return;
2333 END IF;
2334
2335 IF NVL(l_batch_grp_rec.autocreate_delivery_flag, 'N') <> 'Y' THEN
2336 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_APPEND_AUTOCREATE_DEL');
2337 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2338 wsh_util_core.add_message(x_return_status);
2339 IF l_debug_on THEN
2340 WSH_DEBUG_SV.pop(l_module_name);
2341 END IF;
2342 return;
2343 END IF;
2344
2345 IF NVL(l_batch_grp_rec.ac_delivery_criteria, 'Y') <> 'N' THEN
2346 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_APPEND_AC_DEL_CRITERIA');
2347 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2348 wsh_util_core.add_message(x_return_status);
2349 IF l_debug_on THEN
2350 WSH_DEBUG_SV.pop(l_module_name);
2351 END IF;
2352 return;
2353 END IF;
2354
2355 IF NVL(l_batch_grp_rec.auto_pick_confirm_flag, 'Y') <> 'N' THEN
2356 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_APPEND_AUTO_PICK');
2357 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2358 wsh_util_core.add_message(x_return_status);
2359 IF l_debug_on THEN
2360 WSH_DEBUG_SV.pop(l_module_name);
2361 END IF;
2362 return;
2363 END IF;
2364
2365 IF l_batch_grp_rec.ship_confirm_rule_id is not NULL THEN
2366 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_APPEND_AUTO_SHIP');
2367 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2368 wsh_util_core.add_message(x_return_status);
2369 IF l_debug_on THEN
2370 WSH_DEBUG_SV.pop(l_module_name);
2371 END IF;
2372 return;
2373 END IF;
2374
2375 IF NVL(l_batch_grp_rec.autopack_flag, 'Y') <> 'N' THEN
2376 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_APPEND_AUTO_PACK');
2377 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2378 wsh_util_core.add_message(x_return_status);
2379 IF l_debug_on THEN
2380 WSH_DEBUG_SV.pop(l_module_name);
2381 END IF;
2382 return;
2383 END IF;
2384
2385 ELSIF l_batch_grp_rec.Append_Flag <> 'N' THEN
2386 FND_MESSAGE.SET_NAME('WSH', 'WSH_OI_INVALID_ATTRIBUTE');
2387 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'APPEND_FLAG');
2388 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2389 wsh_util_core.add_message(x_return_status);
2390 IF l_debug_on THEN
2391 WSH_DEBUG_SV.pop(l_module_name);
2392 END IF;
2393 return;
2394 END IF;
2395 -- Bug 3463315 End of Append_Flag validation
2396
2397 -- Bug# 3438300 - Start - Validating mutually exclusive Ship from and Organization
2398 IF l_batch_grp_rec.Organization_Id IS NOT NULL AND l_batch_grp_rec.Ship_From_Location_Id IS NOT NULL THEN
2399 --check whether both are valid combination
2400 open c_location_id(l_batch_grp_rec.Organization_Id);
2401 FETCH c_location_id into l_ship_from_loc_id;
2402 close c_location_id;
2403
2404 IF l_ship_from_loc_id <> l_batch_grp_rec.Ship_From_Location_Id THEN
2405 FND_MESSAGE.SET_NAME('WSH','WSH_ORG_SHIPFROM_NOT_COMP');
2406 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2407 wsh_util_core.add_message(x_return_status);
2408 IF l_debug_on THEN
2409 WSH_DEBUG_SV.pop(l_module_name);
2410 END IF;
2411 return;
2412 END IF;
2413 ELSIF l_batch_grp_rec.Organization_Id IS NOT NULL THEN
2414 -- Org is provided, populate the Ship from
2415 open c_location_id(l_batch_grp_rec.Organization_Id);
2416 FETCH c_location_id into l_ship_from_loc_id;
2417 close c_location_id;
2418 l_batch_grp_rec.Ship_From_Location_Id := l_ship_from_loc_id;
2419 ELSIF l_batch_grp_rec.Ship_From_Location_Id IS NOT NULL THEN
2420 -- Ship from is provided, populate the Org
2421 open c_org_id(l_batch_grp_rec.Ship_From_Location_Id);
2422 FETCH c_org_id into l_org_id;
2423 close c_org_id;
2424 l_batch_grp_rec.Organization_Id := l_org_id;
2425 END IF;
2426 -- Bug# 3438300 - End
2427
2428 IF (l_batch_grp_rec.order_header_id IS NULL AND l_batch_grp_rec.ship_set_id IS NULL AND
2429 l_batch_grp_rec.Trip_id IS NULL AND l_batch_grp_rec.Delivery_Id IS NULL AND
2430 l_batch_grp_rec.Delivery_Detail_Id IS NULL AND l_batch_grp_rec.Trip_Stop_Id IS NULL AND
2431 l_batch_grp_rec.Project_Id IS NULL AND l_batch_grp_rec.Task_Id IS NULL ) THEN --{
2432 --
2433 IF l_batch_grp_rec.Organization_Id IS NULL THEN --{
2434 -- Fatal error if no warehouse specified
2435 FND_MESSAGE.SET_NAME('WSH','WSH_PR_NO_WAREHOUSE');
2436 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2437 wsh_util_core.add_message(x_return_status);
2438 IF l_debug_on THEN
2439 WSH_DEBUG_SV.pop(l_module_name);
2440 END IF;
2441 RETURN;
2442 ELSE --}{
2443 -- Warning if warehouse specified and others null
2444 FND_MESSAGE.SET_NAME('WSH', 'WSH_PR_CRITICAL_NULL');
2445 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2446 wsh_util_core.add_message(x_return_status);
2447 END IF; --}
2448 --
2449 END IF; --}
2450
2451 -- X-dock
2452 -- Validating the Allocation method
2453 IF l_batch_in_rec.allocation_method is NOT NULL THEN
2454 IF l_batch_in_rec.allocation_method <> FND_API.G_MISS_CHAR THEN
2455 l_batch_grp_rec.allocation_method := l_batch_in_rec.allocation_method;
2456 ELSE
2457 l_batch_grp_rec.allocation_method := 'I';
2458 END IF;
2459 END IF;
2460 -- End of Validating the Allocation Method
2461
2462 -- Validating the Crossdock Criteria id
2463 --
2464 -- Bug #7505524 : Copied the following validation code from the
2465 -- public API WSH_PICKING_BATCHES_PUB.create_batch.
2466 --
2467 -- X-dock
2468 -- 1)Validate CrossDock Criteria Name for WMS org.
2469 -- Always use crossdock_criteria_id except when crossdock_criteria_id is null
2470 -- and crossdock_criteria_name has been specified
2471 -- 2)The values of crossdock criteria will be ignored for non-WMS org
2472 --
2473 IF WSH_UTIL_VALIDATE.check_wms_org(l_batch_in_rec.organization_id) = 'Y' THEN
2474 IF (l_batch_in_rec.crossdock_criteria_id IS NULL AND
2475 l_batch_in_rec.crossdock_criteria_name IS NOT NULL) THEN
2476 -- derive crossdock criteria id
2477 WMS_CROSSDOCK_GRP.chk_planxd_crt_id_name
2478 (p_criterion_id => l_batch_in_rec.crossdock_criteria_id,
2479 p_criterion_name => l_batch_in_rec.crossdock_criteria_name,
2480 x_return_status => l_return_status);
2481
2482 wsh_util_core.api_post_call(
2483 p_return_status => l_return_status,
2484 x_num_warnings => l_number_of_warnings,
2485 x_num_errors => l_number_of_errors,
2486 p_msg_data => l_msg_data
2487 );
2488 END IF;
2489 END IF;
2490 -- end of X-dock
2491 -- Bug #7505524: end of bug.
2492 --
2493 -- Public API would have already converted crossdock criteria name to id,
2494 -- so validation is only done for id.
2495 IF l_batch_in_rec.crossdock_criteria_id is NOT NULL THEN
2496 IF l_batch_in_rec.crossdock_criteria_id <> FND_API.G_MISS_NUM THEN
2497 l_batch_grp_rec.crossdock_criteria_id := l_batch_in_rec.crossdock_criteria_id;
2498 ELSE
2499 l_batch_grp_rec.crossdock_criteria_id := NULL;
2500 END IF;
2501 END IF;
2502 -- End of Validating the Crossdock Criteria id
2503
2504 --
2505 -- Validate Values using l_batch_grp_rec as the values from l_batch_in_rec
2506 -- have been validated and transferred to l_batch_grp_rec
2507 IF WSH_UTIL_VALIDATE.check_wms_org(l_batch_grp_rec.organization_id) = 'Y' THEN
2508 --{
2509 IF (l_batch_grp_rec.allocation_method NOT IN
2510 (WSH_PICK_LIST.C_INVENTORY_ONLY,WSH_PICK_LIST.C_CROSSDOCK_ONLY,
2511 WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,WSH_PICK_LIST.C_PRIORITIZE_INVENTORY
2512 )
2513 ) THEN
2514 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_ALLOCATION_METHOD');
2515 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2516 wsh_util_core.add_message(x_return_status);
2517 IF l_debug_on THEN
2518 WSH_DEBUG_SV.pop(l_module_name);
2519 END IF;
2520 RETURN;
2521 ELSIF l_batch_grp_rec.crossdock_criteria_id IS NOT NULL AND
2522 l_batch_grp_rec.allocation_method IN (WSH_PICK_LIST.C_CROSSDOCK_ONLY,
2523 WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,WSH_PICK_LIST.C_PRIORITIZE_INVENTORY) THEN
2524 -- call WMS API to validate cross dock criteria
2525 WMS_CROSSDOCK_GRP.validate_planxdock_crt_id
2526 (p_criterion_id => l_batch_grp_rec.crossdock_criteria_id,
2527 x_return_status => l_return_status);
2528 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2529 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_XDOCK_CRITERION');
2530 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2531 wsh_util_core.add_message(x_return_status);
2532 IF l_debug_on THEN
2533 WSH_DEBUG_SV.pop(l_module_name);
2534 END IF;
2535 RETURN;
2536 END IF;
2537 ELSIF (l_batch_grp_rec.crossdock_criteria_id IS NOT NULL AND
2538 l_batch_grp_rec.allocation_method = (WSH_PICK_LIST.C_INVENTORY_ONLY)) THEN
2539 FND_MESSAGE.SET_NAME('WSH','WSH_XDOCK_INVALID_ALLOCMETHOD');
2540 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2541 wsh_util_core.add_message(x_return_status);
2542 IF l_debug_on THEN
2543 WSH_DEBUG_SV.pop(l_module_name);
2544 END IF;
2545 RETURN;
2546 -- Bug#7168917: Dynamic_replenish_flag value should not be 'Yes' when
2547 -- allocation method is 'Cross Dock Only' OR 'Prioritize Inventory'.
2548 ELSIF (l_batch_grp_rec.dynamic_replenishment_flag = 'Y' AND
2549 l_batch_grp_rec.allocation_method IN (WSH_PICK_LIST.C_CROSSDOCK_ONLY,WSH_PICK_LIST.C_PRIORITIZE_INVENTORY)) THEN
2550 FND_MESSAGE.SET_NAME('WSH', 'WSH_OI_INVALID_ATTRIBUTE');
2551 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'DYNAMIC_REPLENISHMENT_FLAG');
2552 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2553 wsh_util_core.add_message(x_return_status);
2554 IF l_debug_on THEN
2555 WSH_DEBUG_SV.pop(l_module_name);
2556 END IF;
2557 RETURN;
2558 END IF;
2559 -- Bug #7505524:begin
2560 -- ECO 4634966
2561 IF l_batch_grp_rec.existing_rsvs_only_flag = 'Y' AND
2562 l_batch_grp_rec.allocation_method IN (WSH_PICK_LIST.C_CROSSDOCK_ONLY,
2563 WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,
2564 WSH_PICK_LIST.C_PRIORITIZE_INVENTORY) THEN
2565 FND_MESSAGE.SET_NAME('WSH','WSH_XDOCK_INVALID_ALLOCMETHOD');
2566 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2567 wsh_util_core.add_message(x_return_status);
2568 IF l_debug_on THEN
2569 WSH_DEBUG_SV.pop(l_module_name);
2570 END IF;
2571 RETURN;
2572 END IF;
2573 -- End of ECO 4634966
2574 -- Bug #7505524: end of bug.
2575 ELSE -- non WMS org
2576 IF l_batch_grp_rec.allocation_method IN (WSH_PICK_LIST.C_CROSSDOCK_ONLY,
2577 WSH_PICK_LIST.C_PRIORITIZE_CROSSDOCK,WSH_PICK_LIST.C_PRIORITIZE_INVENTORY) THEN
2578 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_ALLOCATION_METHOD');
2579 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2580 wsh_util_core.add_message(x_return_status);
2581 IF l_debug_on THEN
2582 WSH_DEBUG_SV.pop(l_module_name);
2583 END IF;
2584 RETURN;
2585 END IF;
2586 --}
2587 END IF;
2588 -- end of X-dock
2589 --
2590 --=======================================================================================================
2591 -- End Of the VALIDATION
2592 --=======================================================================================================
2593
2594 -- Calling the Private API to Create the Batch
2595
2596 IF l_debug_on THEN
2597 --{
2598 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICKING_BATCHES_PKG.INSERT_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
2599 --}
2600 END IF;
2601 --
2602 WSH_PICKING_BATCHES_PKG.Insert_Row(
2603 X_Rowid => l_batch_grp_rec.Row_Id,
2604 X_Batch_Id => l_batch_grp_rec.Batch_Id,
2605 P_Creation_Date => l_batch_grp_rec.Creation_Date,
2606 P_Created_By => l_batch_grp_rec.Created_By,
2607 P_Last_Update_Date => l_batch_grp_rec.Last_Update_Date,
2608 P_Last_Updated_By => l_batch_grp_rec.Last_Updated_By,
2609 P_Last_Update_Login => l_batch_grp_rec.Last_Update_Login,
2610 p_batch_name_prefix => l_batch_name_prefix,
2611 X_Name => l_batch_grp_rec.Name,
2612 P_Backorders_Only_Flag => l_batch_grp_rec.Backorders_Only_Flag,
2613 P_Document_Set_Id => l_batch_grp_rec.Document_Set_Id,
2614 P_Existing_Rsvs_Only_Flag => l_batch_grp_rec.Existing_Rsvs_Only_Flag,
2615 P_Shipment_Priority_Code => l_batch_grp_rec.Shipment_Priority_Code,
2616 P_Ship_Method_Code => l_batch_grp_rec.Ship_Method_Code,
2617 P_Customer_Id => l_batch_grp_rec.Customer_Id,
2618 P_Order_Header_Id => l_batch_grp_rec.Order_Header_Id,
2619 P_Ship_Set_Number => l_batch_grp_rec.Ship_Set_Id,
2620 P_Inventory_Item_Id => l_batch_grp_rec.Inventory_Item_Id,
2621 P_Order_Type_Id => l_batch_grp_rec.Order_Type_Id,
2622 P_From_Requested_Date => l_batch_grp_rec.From_Requested_Date,
2623 P_To_Requested_Date => l_batch_grp_rec.To_Requested_Date,
2624 P_From_Scheduled_Ship_Date => l_batch_grp_rec.From_Scheduled_Ship_Date,
2625 P_To_Scheduled_Ship_Date => l_batch_grp_rec.To_Scheduled_Ship_Date,
2626 P_Ship_To_Location_Id => l_batch_grp_rec.Ship_To_Location_Id,
2627 P_Ship_From_Location_Id => l_batch_grp_rec.Ship_From_Location_Id,
2628 P_Trip_Id => l_batch_grp_rec.Trip_Id,
2629 p_Delivery_Id => l_batch_grp_rec.Delivery_Id,
2630 P_Include_Planned_Lines => l_batch_grp_rec.Include_Planned_Lines,
2631 P_Pick_Grouping_Rule_Id => l_batch_grp_rec.Pick_Grouping_Rule_Id,
2632 P_Pick_Sequence_Rule_Id => l_batch_grp_rec.Pick_Sequence_Rule_Id,
2633 P_Autocreate_Delivery_Flag => l_batch_grp_rec.Autocreate_Delivery_Flag,
2634 P_Attribute_Category => l_batch_grp_rec.Attribute_Category,
2635 P_Attribute1 => l_batch_grp_rec.Attribute1,
2636 P_Attribute2 => l_batch_grp_rec.Attribute2,
2637 P_Attribute3 => l_batch_grp_rec.Attribute3,
2638 P_Attribute4 => l_batch_grp_rec.Attribute4,
2639 P_Attribute5 => l_batch_grp_rec.Attribute5,
2640 P_Attribute6 => l_batch_grp_rec.Attribute6,
2641 P_Attribute7 => l_batch_grp_rec.Attribute7,
2642 P_Attribute8 => l_batch_grp_rec.Attribute8,
2643 P_Attribute9 => l_batch_grp_rec.Attribute9,
2644 P_Attribute10 => l_batch_grp_rec.Attribute10,
2645 P_Attribute11 => l_batch_grp_rec.Attribute11,
2646 P_Attribute12 => l_batch_grp_rec.Attribute12,
2647 P_Attribute13 => l_batch_grp_rec.Attribute13,
2648 P_Attribute14 => l_batch_grp_rec.Attribute14,
2649 P_Attribute15 => l_batch_grp_rec.Attribute15,
2650 P_Autodetail_Pr_Flag => l_batch_grp_rec.Autodetail_Pr_Flag,
2651 -- Bug#: 3266659 - Setting carrier id to NULL
2652 P_Carrier_Id => NULL,
2653 P_Trip_Stop_Id => l_batch_grp_rec.Trip_Stop_Id,
2654 P_Default_Stage_Subinventory => l_batch_grp_rec.Default_Stage_Subinventory,
2655 P_Default_Stage_Locator_Id => l_batch_grp_rec.Default_Stage_Locator_Id,
2656 P_Pick_From_Subinventory => l_batch_grp_rec.Pick_From_Subinventory,
2657 P_Pick_From_locator_Id => l_batch_grp_rec.Pick_From_locator_Id,
2658 P_Auto_Pick_Confirm_Flag => l_batch_grp_rec.Auto_Pick_Confirm_Flag,
2659 P_Delivery_Detail_Id => l_batch_grp_rec.Delivery_Detail_Id,
2660 P_Project_Id => l_batch_grp_rec.Project_Id,
2661 P_Task_Id => l_batch_grp_rec.Task_Id,
2662 P_Organization_Id => l_batch_grp_rec.Organization_Id,
2663 P_Ship_Confirm_Rule_Id => l_batch_grp_rec.Ship_Confirm_Rule_Id,
2664 P_Autopack_Flag => l_batch_grp_rec.Autopack_Flag,
2665 P_Autopack_Level => l_batch_grp_rec.Autopack_Level,
2666 P_Task_Planning_Flag => l_batch_grp_rec.Task_Planning_Flag,
2667 -- Bug#: 3266659 - Removing non_picking_flag
2668 -- P_Non_Picking_Flag => l_batch_grp_rec.Non_Picking_Flag,
2669 p_categorySetID => l_batch_grp_rec.category_set_id,
2670 p_categoryID => l_batch_grp_rec.category_id,
2671 p_ship_set_smc_flag => l_batch_grp_rec.ship_set_smc_flag,
2672 -- Bug#: 3266659 - Adding the columns like zone, region, delivery criteria, release
2673 -- subinventory and append flag
2674 p_regionID => l_batch_grp_rec.region_id,
2675 p_zoneId => l_batch_grp_rec.zone_id,
2676 p_acDelivCriteria => l_batch_grp_rec.ac_delivery_criteria,
2677 p_RelSubinventory => l_batch_grp_rec.rel_subinventory,
2678 p_append_flag => l_batch_grp_rec.append_flag,
2679 p_task_priority => l_batch_grp_rec.task_priority,
2680 p_actual_departure_date => l_batch_grp_rec.actual_departure_date,
2681 p_allocation_method => l_batch_grp_rec.allocation_method, -- X-dock
2682 p_crossdock_criteria_id => l_batch_grp_rec.crossdock_criteria_id, -- X-dock
2683 p_dynamic_replenishment_flag => l_batch_grp_rec.dynamic_replenishment_flag, --bug# 6689448 (replenishment project)
2684 p_client_id => l_batch_grp_rec.client_id -- LSP PROJECT :
2685 );
2686
2687 IF l_debug_on THEN
2688 --{
2689 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2690 --}
2691 END IF;
2692 --
2693 wsh_util_core.api_post_call(
2694 p_return_status => l_return_status,
2695 x_num_warnings => l_number_of_warnings,
2696 x_num_errors => l_number_of_errors,
2697 p_msg_data => l_msg_data
2698 );
2699
2700 x_batch_id := l_batch_grp_rec.batch_id ;
2701
2702 IF FND_API.TO_BOOLEAN(p_commit) THEN
2703 --{
2704 COMMIT WORK;
2705 --}
2706 END IF;
2707
2708 FND_MSG_PUB.Count_And_Get
2709 (
2710 p_count => x_msg_count,
2711 p_data => x_msg_data,
2712 p_encoded => FND_API.G_FALSE
2713 );
2714
2715 IF l_debug_on THEN
2716 --{
2717 WSH_DEBUG_SV.pop(l_module_name);
2718 --}
2719 END IF;
2720 --
2721 EXCEPTION
2722 WHEN FND_API.G_EXC_ERROR THEN
2723 ROLLBACK TO PICKING_BATCH_GRP;
2724 x_return_status := FND_API.G_RET_STS_ERROR ;
2725 wsh_util_core.add_message(x_return_status);
2726 FND_MSG_PUB.Count_And_Get
2727 (
2728 p_count => x_msg_count,
2729 p_data => x_msg_data,
2730 p_encoded => FND_API.G_FALSE
2731 );
2732
2733 IF l_debug_on THEN
2734 --{
2735 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2736 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2737 --}
2738 END IF;
2739 --
2740
2741 WHEN WSH_INVALID_CONSOL_DEL THEN
2742 ROLLBACK TO PICKING_BATCH_GRP;
2743 FND_MESSAGE.SET_NAME('WSH', 'WSH_PUB_CONSOL_DEL_PR');
2744 FND_MESSAGE.SET_TOKEN('PARAMETER',l_batch_in_rec.Delivery_id);
2745 x_return_status := FND_API.G_RET_STS_ERROR ;
2746 wsh_util_core.add_message(x_return_status);
2747 FND_MSG_PUB.Count_And_Get
2748 (
2749 p_count => x_msg_count,
2750 p_data => x_msg_data,
2751 p_encoded => FND_API.G_FALSE
2752 );
2753
2754 IF l_debug_on THEN
2755 --{
2756 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_PUB_CONSOL_DEL_PR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2757 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_PUB_CONSOL_DEL_PR');
2758 --}
2759 END IF;
2760 --
2761
2762 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2763 ROLLBACK TO PICKING_BATCH_GRP;
2764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2765 wsh_util_core.add_message(x_return_status, l_module_name);
2766 FND_MSG_PUB.Count_And_Get
2767 (
2768 p_count => x_msg_count,
2769 p_data => x_msg_data,
2770 p_encoded => FND_API.G_FALSE
2771 );
2772 --
2773
2774 IF l_debug_on THEN
2775 --{
2776 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2777 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2778 --}
2779 END IF;
2780 WHEN OTHERS THEN
2781 ROLLBACK TO PICKING_BATCH_GRP;
2782 wsh_util_core.default_handler('WSH_PICKING_BATCHES_PUB.CREATE_BATCH');
2783 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2784 FND_MSG_PUB.Count_And_Get
2785 (
2786 p_count => x_msg_count,
2787 p_data => x_msg_data,
2788 p_encoded => FND_API.G_FALSE
2789 );
2790
2791 IF l_debug_on THEN
2792 --{
2793 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2794 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2795 --}
2796 END IF;
2797 --
2798 END Create_Batch;
2799
2800
2801 --============================================================================================
2802 -- Start of comments
2803 --
2804 -- API Name : Release_Batch
2805 -- Type : Group
2806 -- Pre-reqs : None.
2807 -- Function : The procedure takes in a Batch_id/ Batch_name and depending on the p_release_mode
2808 -- value it process the batch. p_log_level value should be greator than 0 when
2809 -- customer want to get the pick release log fine incace of concurrent pick release
2810 -- release.
2811 -- It will do some basic validations on the the input parameters
2812 -- like log_level should be positive , and on the not null values of p_batch_rec.
2813 --
2814 -- PARAMETERS : p_api_version_number known api version number
2815 -- p_init_msg_list FND_API.G_TRUE to reset list
2816 -- p_commit FND_API.G_TRUE to perform a commit
2817 -- x_return_status return status
2818 -- x_msg_count number of messages in the list
2819 -- x_msg_data text of messages
2820 -- p_batch_id Picking Batch Id which is used to get Batch
2821 -- information from the wsh_picking_batches table.
2822 -- p_batch_name Picking Batch Name which is used to get Batch
2823 -- information from the wsh_picking_batches table.
2824 -- p_log_level Controlls the log message generated by cuncurrent
2825 -- pick release process.
2826 -- p_release_mode Used to do ONLINE or CONCURRENT pick release,
2827 -- Default is "CONCURREN"
2828 -- p_num_workers Number of Workers for Parallel Pick Release
2829 -- Default is 1 -- Bug # 7505524.
2830 -- For Online Mode it is always 1
2831 -- For Concurrent Mode the value is determined by profile
2832 -- x_request_id Out parameter contains the request Id for the concurrent request submitted
2833 -- VERSION : current version 1.0
2834 -- initial version 1.0
2835 -- End of comments
2836 --============================================================================================
2837
2838 PROCEDURE Release_Batch (
2839 -- Standard parameters
2840 p_api_version_number IN NUMBER,
2841 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
2842 p_commit IN VARCHAR2 DEFAULT NULL,
2843 x_return_status OUT NOCOPY VARCHAR2,
2844 x_msg_count OUT NOCOPY NUMBER,
2845 x_msg_data OUT NOCOPY VARCHAR2,
2846
2847 -- program specific paramters.
2848 p_batch_id IN NUMBER DEFAULT NULL,
2849 p_batch_name IN VARCHAR2 DEFAULT NULL,
2850 p_log_level IN NUMBER DEFAULT NULL,
2851 p_release_mode IN VARCHAR2 DEFAULT 'CONCURRENT',
2852 -- p_num_workers IN NUMBER , -- Bug # 7505524
2853 x_request_id OUT NOCOPY NUMBER
2854 ) IS
2855
2856 -- Standard call to check for call compatibility
2857 l_api_version CONSTANT NUMBER := 1.0;
2858 l_api_name CONSTANT VARCHAR2(30) := 'WSH_PICKING_BATCHES_GRP';
2859 l_return_status VARCHAR2(30);
2860 l_msg_count NUMBER;
2861 l_msg_data VARCHAR2(32767);
2862 l_number_of_errors NUMBER := 0;
2863 l_number_of_warnings NUMBER := 0;
2864 l_debug_on BOOLEAN;
2865 --
2866 l_batch_id NUMBER;
2867 l_batch_name VARCHAR2(30);
2868 l_pick_result VARCHAR2(30);
2869 l_pick_phase VARCHAR2(30);
2870 l_pick_skip VARCHAR2(30);
2871 l_log_level NUMBER;
2872 l_num_workers NUMBER; -- bug # 7505524
2873
2874 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RELEASE_BATCH';
2875 --
2876
2877 BEGIN
2878
2879 --
2880 -- Debug Statements
2881 --
2882 --
2883 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2884 --
2885 IF l_debug_on IS NULL THEN
2886 --{
2887 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2888 --}
2889 END IF;
2890 --
2891 SAVEPOINT PICKING_BATCH_GRP;
2892
2893 -- Standard begin of API savepoint
2894 --
2895 -- Debug Statements
2896 --
2897 IF l_debug_on THEN
2898 --{
2899 wsh_debug_sv.push(l_module_name);
2900 --
2901 wsh_debug_sv.LOG(l_module_name, 'P_API_VERSION_NUMER', p_api_version_number);
2902 wsh_debug_sv.LOG(l_module_name, 'P_INIT_MSG_LIST', p_init_msg_list);
2903 wsh_debug_sv.LOG(l_module_name, 'P_COMMIT', p_commit);
2904 --wsh_debug_sv.LOG(l_module_name, 'P_NUM_WORKERS', p_num_workers); -- Bug # 7505524.
2905 --}
2906 END IF;
2907
2908 IF NOT fnd_api.compatible_api_call(
2909 l_api_version,
2910 p_api_version_number,
2911 l_api_name,
2912 g_pkg_name) THEN
2913 RAISE fnd_api.g_exc_unexpected_error;
2914 END IF;
2915 -- Check p_init_msg_list
2916 IF fnd_api.to_boolean(p_init_msg_list) THEN
2917 --{
2918 fnd_msg_pub.initialize;
2919 --}
2920 END IF;
2921 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2922 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2923 l_batch_id := p_batch_id;
2924 l_batch_name := p_batch_name;
2925 l_log_level := p_log_level;
2926
2927
2928 ------------------------------------------------------------------------------------------------------------
2929 -- VALIDATE ALL THE INPUT VALUES
2930 ------------------------------------------------------------------------------------------------------------
2931
2932 -- Validating Batch Name and Batch Id
2933 IF ( ( l_batch_Id IS NOT NULL AND l_batch_Id <> FND_API.G_MISS_NUM ) OR ( l_batch_Name IS NOT NULL AND l_batch_name <> FND_API.G_MISS_CHAR ) ) THEN
2934 --{
2935 IF ( l_batch_Id = FND_API.G_MISS_NUM ) THEN
2936 --{
2937 l_batch_Id := NULL;
2938 --}
2939 END IF;
2940 IF ( l_batch_name = FND_API.G_MISS_CHAR ) THEN
2941 --{
2942 l_batch_name := NULL;
2943 --}
2944 END IF;
2945 IF ( ( l_batch_Id IS NOT NULL) OR ( l_batch_name IS NOT NULL ) ) THEN
2946 --{
2947 WSH_UTIL_VALIDATE.Validate_Picking_Batch_Name(
2948 p_picking_batch_Id => l_batch_Id,
2949 p_picking_batch_name => l_batch_Name,
2950 x_return_status => l_return_status);
2951
2952 IF l_debug_on THEN
2953 --{
2954 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2955 --}
2956 END IF;
2957 --
2958 wsh_util_core.api_post_call(
2959 p_return_status => l_return_status,
2960 x_num_warnings => l_number_of_warnings,
2961 x_num_errors => l_number_of_errors,
2962 p_msg_data => l_msg_data
2963 );
2964 --}
2965 END IF;
2966 ELSE
2967 --
2968 --
2969 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
2970 FND_MESSAGE.SET_TOKEN('FIELD_NAME ',' BATCH_ID OR BATCH_NAME ');
2971 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2972 wsh_util_core.add_message(x_return_status);
2973 return;
2974 --}
2975 END IF;
2976
2977 -- End of Batch Name Validations
2978
2979 -- Validating the Release Mode
2980 IF ( ( p_release_mode IS NULL ) OR ( p_release_mode NOT IN ('CONCURRENT','ONLINE'))) THEN
2981 --{
2982 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
2983 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','P_RELEASE_MODE');
2984 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2985 wsh_util_core.add_message(x_return_status);
2986 return;
2987 --}
2988 END IF;
2989 -- End of Validating the Release Mode
2990
2991 -- Validating the Log Level
2992 IF ( l_log_level <> 0 ) THEN
2993 --{
2994 WSH_UTIL_VALIDATE.Validate_Negative(
2995 p_value => l_log_level,
2996 x_return_status => l_return_status);
2997
2998 IF l_debug_on THEN
2999 --{
3000 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3001 --}
3002 END IF;
3003 --
3004 wsh_util_core.api_post_call(
3005 p_return_status => l_return_status,
3006 x_num_warnings => l_number_of_warnings,
3007 x_num_errors => l_number_of_errors,
3008 p_msg_data => l_msg_data
3009 );
3010 --}
3011 END IF;
3012 -- End of Log Level Validation
3013
3014
3015 IF ( p_release_mode = 'CONCURRENT' ) THEN
3016 --{
3017 -- Bug # 7505524: Copied the following validation code from the
3018 -- public API WSH_PICKING_BATCHES_PUB.release_batch.
3019 l_num_workers := NVL(FND_PROFILE.Value('WSH_PR_NUM_WORKERS'), 1);
3020
3021 IF l_debug_on THEN
3022 --{
3023 WSH_DEBUG_SV.log(l_module_name,'l_num_workers',l_num_workers);
3024 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICKING_BATCHES_PKG.SUBMIT_RELEASE_REQUEST',WSH_DEBUG_SV.C_PROC_LEVEL);
3025 --}
3026 END IF;
3027 --
3028 x_request_id := WSH_PICKING_BATCHES_PKG.Submit_Release_Request(
3029 p_batch_id => l_batch_id,
3030 p_log_level => l_log_level,
3031 p_num_workers => l_num_workers); -- bug # 7505524
3032 IF l_debug_on THEN
3033 --{
3034 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3035 --}
3036 END IF;
3037 --
3038 wsh_util_core.api_post_call(
3039 p_return_status => l_return_status,
3040 x_num_warnings => l_number_of_warnings,
3041 x_num_errors => l_number_of_errors,
3042 p_msg_data => l_msg_data
3043 );
3044
3045 IF ( x_request_id > 0) THEN
3046 --{
3047 FND_MESSAGE.SET_NAME('ONT','SO_OTHER_CONCURRENT_REQUEST_ID');
3048 FND_MESSAGE.SET_TOKEN('REQUESTID',TO_CHAR(x_request_id));
3049 ELSE
3050 FND_MESSAGE.SET_NAME('WSH','WSH_PR_REVOKED');
3051 FND_MESSAGE.SET_NAME('ONT','SO_SH_NO_CREATE_PICK_SLIPS');
3052 --}
3053 END IF;
3054
3055 ELSE
3056 IF l_debug_on THEN
3057 --{
3058 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICK_LIST.ONLINE_RELEASE',WSH_DEBUG_SV.C_PROC_LEVEL);
3059 --}
3060 END IF;
3061 --
3062 WSH_PICK_LIST.Online_Release(
3063 p_batch_id => l_batch_id,
3064 p_pick_result => l_pick_result,
3065 p_pick_phase => l_pick_phase,
3066 p_pick_skip => l_pick_skip);
3067
3068 IF l_debug_on THEN
3069 --{
3070 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3071 --}
3072 END IF;
3073 --
3074 wsh_util_core.api_post_call(
3075 p_return_status => l_return_status,
3076 x_num_warnings => l_number_of_warnings,
3077 x_num_errors => l_number_of_errors,
3078 p_msg_data => l_msg_data
3079 );
3080
3081
3082 IF (nvl(l_pick_result,'F') = 'F' ) then
3083 --{
3084 IF (nvl(l_pick_phase,'START') ='MOVE_ORDER_LINES') then
3085 --{
3086 FND_MESSAGE.SET_NAME('WSH','WSH_PR_ONLINE_PARTIAL');
3087 FND_MESSAGE.SET_TOKEN('MOVE_ORDER', l_batch_name);
3088 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3089 wsh_util_core.add_message(x_return_status,l_module_name);
3090 ELSE
3091 FND_MESSAGE.SET_NAME('WSH','WSH_PR_ONLINE_FAILED');
3092 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3093 wsh_util_core.add_message(x_return_status,l_module_name);
3094 --}
3095 END IF;
3096 ELSIF (nvl(l_pick_result, 'F') = 'W') then
3097 IF (nvl(l_pick_phase,'START') ='MOVE_ORDER_LINES') then
3098 --{
3099 FND_MESSAGE.SET_NAME('WSH','WSH_PR_ONLINE_PART_WARN');
3100 -- bug# 8340984 : Changed error to warning here (same behavior when performed
3101 -- pick release through release sales orders form)
3102 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3103 wsh_util_core.add_message(x_return_status,l_module_name);
3104 ELSE
3105 FND_MESSAGE.SET_NAME('WSH','WSH_PR_ONLINE_WARN');
3106 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3107 wsh_util_core.add_message(x_return_status,l_module_name);
3108 --}
3109 END IF;
3110 ELSE
3111 FND_MESSAGE.SET_NAME('WSH','WSH_PR_ONLINE_SUCCESS');
3112 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3113 wsh_util_core.add_message(x_return_status,l_module_name);
3114 --}
3115 END IF;
3116 --}
3117 END IF;
3118
3119
3120 IF l_debug_on THEN
3121 --{
3122 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3123 --}
3124 END IF;
3125 --
3126 wsh_util_core.api_post_call(
3127 p_return_status => l_return_status,
3128 x_num_warnings => l_number_of_warnings,
3129 x_num_errors => l_number_of_errors,
3130 p_msg_data => l_msg_data
3131 );
3132
3133 IF FND_API.TO_BOOLEAN(p_commit) THEN
3134 --{
3135 COMMIT WORK;
3136 --}
3137 END IF;
3138
3139 FND_MSG_PUB.Count_And_Get
3140 (
3141 p_count => x_msg_count,
3142 p_data => x_msg_data,
3143 p_encoded => FND_API.G_FALSE
3144 );
3145
3146 IF l_debug_on THEN
3147 --{
3148 WSH_DEBUG_SV.pop(l_module_name);
3149 --}
3150 END IF;
3151 --
3152 EXCEPTION
3153 WHEN FND_API.G_EXC_ERROR THEN
3154 ROLLBACK TO PICKING_BATCH_GRP;
3155 x_return_status := FND_API.G_RET_STS_ERROR ;
3156 wsh_util_core.add_message(x_return_status);
3157 FND_MSG_PUB.Count_And_Get
3158 (
3159 p_count => x_msg_count,
3160 p_data => x_msg_data,
3161 p_encoded => FND_API.G_FALSE
3162 );
3163
3164 IF l_debug_on THEN
3165 --{
3166 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3167 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3168 --}
3169 END IF;
3170 --
3171
3172 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3173 ROLLBACK TO PICKING_BATCH_GRP;
3174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3175 wsh_util_core.add_message(x_return_status, l_module_name);
3176 FND_MSG_PUB.Count_And_Get
3177 (
3178 p_count => x_msg_count,
3179 p_data => x_msg_data,
3180 p_encoded => FND_API.G_FALSE
3181 );
3182 --
3183
3184 IF l_debug_on THEN
3185 --{
3186 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3187 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3188 --}
3189 END IF;
3190 WHEN OTHERS THEN
3191 ROLLBACK TO PICKING_BATCH_GRP;
3192 wsh_util_core.default_handler('WSH_PICKING_BATCHES_GRP.RELEASE_BATCH');
3193 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3194 FND_MSG_PUB.Count_And_Get
3195 (
3196 p_count => x_msg_count,
3197 p_data => x_msg_data,
3198 p_encoded => FND_API.G_FALSE
3199 );
3200
3201 IF l_debug_on THEN
3202 --{
3203 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3204 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3205 --}
3206 END IF;
3207 --
3208 END Release_Batch;
3209
3210 -- Bug 7505524 : Added a new api release_wms_wave
3211 --============================================================================================
3212 -- Start of comments
3213 --
3214 -- API Name : release_wms_wave
3215 -- Type : Group
3216 -- Pre-reqs : None.
3217 -- Function : The procedure takes in a pick wave header id, pick release batch information and
3218 -- pick release mode. It creates new picking batch with the information provided in picking batch
3219 -- record. It creates the picking batch with delivery_detail_id of picking batch value as -1 so
3220 -- that while pick releasing it considers all delivery detail lines with the batch id stamped.
3221 -- Updates new batch id value on all delivery detail lines which are eligible for pick release
3222 -- and associated to the pick wave header id passed. Pick releases the batch created in the
3223 -- pick release mode(ONLINE/CONCURRENT) passed via p_release_mode.
3224 -- Sends the batch id and concurrent request number of pick release request to the caller.
3225 --
3226 -- PARAMETERS : p_batch_rec Contains the all the parameters for the batch
3227 -- p_release_mode Used to do ONLINE or CONCURRENT pick release,
3228 -- Default is "CONCURREN"
3229 -- p_pick_wave_header_id pick wave header id value.
3230 -- x_return_status return status
3231 -- x_msg_count number of messages in the list
3232 -- x_msg_data text of messages
3233 -- x_batch_id Out parameter contains the batch id created
3234 -- x_request_id Out parameter contains the request Id for the pick release
3235 -- concurrent request submitted
3236
3237 -- End of comments
3238 --============================================================================================
3239
3240 PROCEDURE release_wms_wave(
3241 p_batch_rec IN WSH_PICKING_BATCHES_PUB.Batch_Info_Rec,
3242 p_release_mode IN VARCHAR2 DEFAULT 'CONCURRENT',
3243 p_pick_wave_header_id IN NUMBER,
3244 x_batch_id OUT NOCOPY NUMBER,
3245 x_request_id OUT NOCOPY NUMBER,
3246 x_return_status OUT NOCOPY VARCHAR2,
3247 x_msg_count OUT NOCOPY NUMBER,
3248 x_msg_data OUT NOCOPY VARCHAR2
3249 ) IS
3250
3251 CURSOR c_get_dds_for_wave (c_pick_wave_header_id NUMBER) IS
3252 SELECT wdd.delivery_detail_id
3253 FROM wsh_delivery_details wdd,
3254 wms_wp_wave_lines wpl,
3255 wms_wp_wave_headers_vl wph
3256 WHERE wdd.delivery_detail_id = wpl.delivery_detail_id
3257 AND wpl.wave_header_id = c_pick_wave_header_id
3258 AND wpl.wave_header_id=wph.wave_header_id
3259 AND nvl(wpl.remove_from_wave_flag,'N') <> 'Y'
3260 AND (wph.wave_Status in ('Planned','Created') or (wph.wave_status = 'Released' and Re_release_flag = 'Y'))
3261 AND wdd.released_status in ('R', 'B', 'X')
3262 AND NVL(wdd.replenishment_status,'C') = 'C';
3263
3264 record_locked EXCEPTION;
3265 PRAGMA EXCEPTION_INIT(record_locked, -54);
3266
3267 l_batch_rec WSH_PICKING_BATCHES_PUB.Batch_Info_Rec;
3268 l_dd_tbl WSH_UTIL_CORE.id_tab_type;
3269 l_batch_id NUMBER;
3270 l_request_id NUMBER;
3271 l_log_level NUMBER := 0;
3272 l_tot_fetch NUMBER;
3273 l_pre_fetch NUMBER;
3274 l_cur_fetch NUMBER;
3275 l_batch_size NUMBER :=5;
3276 l_tot_upd_dds NUMBER := 0;
3277 l_dd_upd_tbl WSH_UTIL_CORE.id_tab_type;
3278 l_return_status VARCHAR2(1);
3279 l_temp_rec WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
3280 l_num_errors NUMBER;
3281 --
3282 l_debug_on BOOLEAN;
3283 --
3284 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'release_wms_wave';
3285 --
3286 BEGIN
3287 --{
3288 --
3289 -- Debug Statements
3290 --
3291 --
3292 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3293 --
3294 IF l_debug_on IS NULL THEN
3295 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3296 END IF;
3297 --
3298 savepoint create_batch;
3299 IF l_debug_on THEN
3300 WSH_DEBUG_SV.push(l_module_name);
3301 l_log_level := 1;
3302 wsh_debug_sv.LOG(l_module_name, 'p_release_mode', p_release_mode);
3303 wsh_debug_sv.LOG(l_module_name, 'p_pick_wave_header_id', p_pick_wave_header_id);
3304 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICKING_BATCHES_GRP.Create_Batch',WSH_DEBUG_SV.C_PROC_LEVEL);
3305 END IF;
3306 --pass delivery detail id as -1 as dd's are getting updated with batch id.
3307 l_batch_rec := p_batch_rec;
3308 l_batch_rec.Delivery_Detail_Id := -1;
3309 WSH_PICKING_BATCHES_GRP.Create_Batch(
3310 p_api_version_number => 1.1,
3311 p_batch_rec => l_batch_rec,
3312 x_batch_id => l_batch_id,
3313 x_return_status => l_return_status,
3314 x_msg_count => x_msg_count,
3315 x_msg_data => x_msg_data
3316 );
3317 IF l_debug_on THEN
3318 wsh_debug_sv.LOG(l_module_name, 'WSH_PICKING_BATCHES_GRP.Create_Batch return status', l_return_status);
3319 wsh_debug_sv.LOG(l_module_name, 'l_batch_id', l_batch_id);
3320 END IF;
3321 --
3322 IF NVL(l_batch_id,0) = 0 OR l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
3323 --{
3324 IF NVL(l_batch_id,0) = 0 AND l_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
3325 --{
3326 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3327 ELSE
3328 x_return_status := l_return_status;
3329 --}
3330 END IF;
3331 x_batch_id := NULL;
3332 IF l_debug_on THEN
3333 WSH_DEBUG_SV.pop(l_module_name);
3334 END IF;
3335 RETURN;
3336 --}
3337 END IF;
3338 --
3339 -- Updating batch Id value on WDD records associated to the pick wave header.
3340 savepoint update_batch_id_on_dd;
3341 --
3342 l_tot_fetch := 0;
3343 OPEN c_get_dds_for_wave (p_pick_wave_header_id);
3344 LOOP
3345 --{
3346 l_pre_fetch := l_tot_fetch;
3347 FETCH c_get_dds_for_wave BULK COLLECT INTO l_dd_tbl LIMIT l_batch_size;
3348 l_tot_fetch := c_get_dds_for_wave%ROWCOUNT;
3349 l_cur_fetch := l_tot_fetch - l_pre_fetch;
3350 EXIT WHEN ( l_cur_fetch <= 0);
3351 FOR i in 1..l_dd_tbl.COUNT LOOP
3352 BEGIN
3353 savepoint lock_delivery_detail_loop;
3354 WSH_DELIVERY_DETAILS_PKG.lock_detail_no_compare(p_delivery_detail_id => l_dd_tbl(i));
3355 l_dd_upd_tbl(l_dd_upd_tbl.COUNT + 1) := l_dd_tbl(i);
3356 EXCEPTION
3357 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
3358 rollback to lock_delivery_detail_loop;
3359 FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVB_LOCK_FAILED');
3360 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_dd_tbl(i));
3361 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
3362 l_num_errors := l_num_errors + 1;
3363 --
3364 IF l_debug_on THEN
3365 WSH_DEBUG_SV.log(l_module_name,'Unable to obtain lock on the Delivery Detail Id',l_dd_tbl(i));
3366 END IF;
3367 --
3368 WHEN others THEN
3369 rollback to lock_delivery_detail_loop;
3370 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3371 END;
3372 --
3373 END LOOP;
3374 --Update the detail lines with batch id.
3375 FORALL i in 1..l_dd_upd_tbl.count
3376 UPDATE wsh_delivery_details
3377 SET batch_id = l_batch_id,
3378 last_updated_by = fnd_global.user_id,
3379 last_update_date = SYSDATE,
3380 last_update_login = fnd_global.login_id
3381 WHERE delivery_detail_id = l_dd_upd_tbl(i);
3382 l_tot_upd_dds := l_tot_upd_dds + sql%rowcount;
3383 IF l_debug_on THEN
3384 wsh_debug_sv.LOG(l_module_name, 'SQl updated',sql%rowcount);
3385 END IF;
3386 --}
3387 END LOOP;
3388 --
3389 CLOSE c_get_dds_for_wave;
3390 --
3391 IF l_debug_on THEN
3392 wsh_debug_sv.LOG(l_module_name, 'Total number of delivery details selected',l_tot_fetch);
3393 wsh_debug_sv.LOG(l_module_name, 'Total number of delivery details selected after locking',l_tot_upd_dds);
3394 END IF;
3395 IF l_tot_fetch = 0 THEN
3396 --{
3397 IF l_debug_on THEN
3398 wsh_debug_sv.LOG(l_module_name, 'There are no eligible delivery lines for the pick release',l_tot_fetch);
3399 END IF;
3400 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3401 RETURN;
3402 --}
3403 END IF;
3404 IF l_tot_upd_dds < l_tot_fetch THEN
3405 --{
3406 IF l_debug_on THEN
3407 wsh_debug_sv.LOG(l_module_name, 'Number of delivery detail lines failed while locking',l_tot_fetch-l_tot_upd_dds);
3408 END IF;
3409 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3410 --}
3411 END IF;
3412
3413
3414 -- Call the pick release code only if there are one or more pick release eligible delivery lines.
3415 IF (l_tot_upd_dds > 0) THEN
3416 --{
3417 IF l_debug_on THEN
3418 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICKING_BATCHES_PUB.RELEASE_BATCH',WSH_DEBUG_SV.C_PROC_LEVEL);
3419 END IF;
3420 --
3421 WSH_PICKING_BATCHES_GRP.release_batch(
3422 p_api_version_number => 1.1,
3423 p_batch_id => l_batch_id,
3424 p_log_level => l_log_level,
3425 p_release_mode => p_release_mode,
3426 x_return_status => l_return_status,
3427 x_msg_count => x_msg_count,
3428 x_msg_data => x_msg_data,
3429 x_request_id => l_request_id);
3430 IF l_debug_on THEN
3431 wsh_debug_sv.LOG(l_module_name, 'WSH_PICKING_BATCHES_GRP.release_batch return status:', l_return_status);
3432 wsh_debug_sv.LOG(l_module_name, 'pick release concurrent req.id:',l_request_id);
3433 END IF;
3434 IF ((l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING))
3435 AND ( p_release_mode='ONLINE' OR nvl(l_request_id,0) = 0 )) THEN
3436 --{
3437 ROLLBACK TO update_batch_id_on_dd;
3438 --}
3439 END IF;
3440 --
3441 x_batch_id := l_batch_id;
3442 x_request_id := l_request_id;
3443 x_return_status := l_return_status;
3444 ELSE -- Get debug msgs when all dds failed while locking
3445 FND_MSG_PUB.Count_And_Get(
3446 p_count => x_msg_count,
3447 p_data => x_msg_data,
3448 p_encoded => FND_API.G_FALSE
3449 );
3450 --}
3451 END IF;
3452 --
3453 IF l_debug_on THEN
3454 WSH_DEBUG_SV.pop(l_module_name);
3455 END IF;
3456
3457 EXCEPTION
3458 WHEN Others THEN
3459 ROLLBACK TO create_batch;
3460 WSH_UTIL_CORE.Default_Handler ('WSH_PICKING_BATCHES_GRP.release_wms_wave');
3461 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3462 IF c_get_dds_for_wave%ISOPEN THEN
3463 CLOSE c_get_dds_for_wave;
3464 END IF;
3465 --
3466 -- Debug Statements
3467 --
3468 IF l_debug_on THEN
3469 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3470 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3471 END IF;
3472 --
3473 END release_wms_wave;
3474
3475 END WSH_PICKING_BATCHES_GRP;