[Home] [Help]
PACKAGE BODY: APPS.WSH_PR_CRITERIA
Source
1 PACKAGE BODY WSH_PR_CRITERIA AS
2 /* $Header: WSHPRCRB.pls 120.17 2008/01/08 01:33:53 mvudugul ship $ */
3 --
4 -- PACKAGE TYPES
5 --
6 --Record type to store attributes of order.
7 TYPE psrTyp IS RECORD (
8 attribute NUMBER,
9 attribute_name VARCHAR2(30),
10 priority NUMBER,
11 sort_order VARCHAR2(4));
12
13 TYPE psrTabTyp IS TABLE OF psrTyp INDEX BY BINARY_INTEGER;
14
15 -- dramamoo 09-Mar-01 Start of Table to store Detailed List IDs instead of using Concatenated String
16 TYPE psrDetLst IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
17
18
19 CURSOR c_work_cursorID(l_batch_id NUMBER, l_organization_id NUMBER, l_mode VARCHAR2) IS
20 SELECT organization_id, inventory_item_id, mo_start_line_number, mo_line_count
21 FROM wsh_pr_workers
22 WHERE batch_id = l_batch_id
23 AND organization_id = l_organization_id
24 AND type = 'PICK'
25 AND processed = 'N'
26 AND l_mode = 'PICK'
27 UNION
28 SELECT organization_id, inventory_item_id, mo_start_line_number, mo_line_count
29 FROM wsh_pr_workers
30 WHERE batch_id = l_batch_id
31 AND organization_id = l_organization_id
32 AND type = 'PICK'
33 AND processed = 'N'
34 AND inventory_item_id IS NULL
35 AND l_mode = 'PICK-SS'
36 ORDER BY 1, 2 DESC;
37
38 --
39 -- PACKAGE CONSTANTS
40
41 -- Indicate what attributes are used in Pick Sequence Rules
42 C_INVOICE_VALUE CONSTANT BINARY_INTEGER := 1;
43 C_ORDER_NUMBER CONSTANT BINARY_INTEGER := 2;
44 C_SCHEDULE_DATE CONSTANT BINARY_INTEGER := 3;
45 C_TRIP_STOP_DATE CONSTANT BINARY_INTEGER := 4;
46 C_SHIPMENT_PRIORITY CONSTANT BINARY_INTEGER := 5;
47
48 --
49 -- PACKAGE VARIABLES
50 --
51 g_use_trip_stop_date BOOLEAN := FALSE;
52 g_initialized BOOLEAN := FALSE;
53 g_ordered_psr psrTabTyp;
54 g_use_order_ps VARCHAR2(1) := 'Y';
55 g_invoice_value_flag VARCHAR2(1) := 'N';
56 g_total_pick_criteria NUMBER;
57 -- g_order_type_id NUMBER; -- Bugfix 3604021
58 g_order_line_id NUMBER;
59 g_backorders_flag VARCHAR2(1);
60 g_include_planned_lines VARCHAR2(1);
61 g_details_list VARCHAR2(100);
62 g_customer_id NUMBER;
63 g_inventory_item_id NUMBER;
64 g_shipment_priority VARCHAR2(30);
65 g_ship_method_code VARCHAR2(30);
66 g_ship_set_number NUMBER;
67 g_ship_to_loc_id NUMBER;
68 g_project_id NUMBER;
69 g_task_id NUMBER;
70 g_Unreleased_SQL VARCHAR(3000) := NULL;
71 g_Backordered_SQL VARCHAR(3000) := NULL;
72 g_Cond_SQL VARCHAR(2000) := NULL;
73 g_orderby_SQL VARCHAR2(500) := NULL;
74
75 g_lock_or_hold_failed BOOLEAN := FALSE;
76 g_failed_ship_set_id NUMBER := NULL;
77 g_failed_top_model_line_id NUMBER := NULL;
78 g_last_ship_set_id NUMBER := NULL;
79 g_last_top_model_line_id NUMBER := NULL;
80 g_last_model_quantity NUMBER := NULL;
81 g_last_header_id NUMBER := NULL;
82 g_last_source_code VARCHAR2(30) := NULL;
83
84 -- dramamoo 09-Mar-01 Start of Table to store Detailed List IDs instead of using Concatenated String
85 g_det_lst psrDetLst;
86
87 --Bug 4775539
88 g_cache_header_id NUMBER;
89 g_cache_demand_header_id NUMBER;
90
91 -- selected from cursors
92 v_count NUMBER;
93 v_line_id NUMBER;
94 v_header_id NUMBER;
95 v_org_id NUMBER;
96 v_inventory_item_id NUMBER;
97 v_move_order_line_id NUMBER;
98 v_delivery_detail_id NUMBER;
99 v_ship_model_complete_flag VARCHAR2(1);
100 v_top_model_line_id NUMBER;
101 v_ship_from_location_id NUMBER;
102 v_ship_method_code VARCHAR2(30);
103 v_shipment_priority VARCHAR2(30);
104 v_date_scheduled DATE;
105 v_requested_quantity NUMBER;
106 v_requested_quantity_uom VARCHAR2(3);
107 v_project_id NUMBER;
108 v_task_id NUMBER;
109 v_from_sub VARCHAR2(10);
110 v_to_sub VARCHAR2(10);
111 v_released_status VARCHAR2(1);
112 v_ship_set_id NUMBER;
113 v_model_quantity NUMBER;
114 v_source_code VARCHAR2(30);
115 v_source_header_number VARCHAR2(150);
116 v_planned_departure_date DATE;
117 v_delivery_id NUMBER;
118 v_unit_number VARCHAR2(30);
119 v_source_doc_type NUMBER;
120 v_demand_source_header_id NUMBER;
121 v_invoice_value INTEGER;
122 v_cursorID INTEGER;
123 v_pr_org_id NUMBER;
124 v_pr_inv_item_id NUMBER;
125 v_pr_mo_header_id NUMBER;
126 v_pr_mo_line_number NUMBER;
127 v_pr_mo_line_count NUMBER;
128 v_total_rec_fetched NUMBER := 0;
129 v_prev_item_id NUMBER;
130 v_ignore INTEGER;
131 v_reservable_type VARCHAR2(1);
132 v_last_update_date DATE; --Bug# 3248578
133
134 v_customer_id NUMBER;-- anxsharm, X-dock
135
136 -- hverddin 27-JUN-00 Start Of OPM Changes
137 -- HW OPMCONV. Need to expand length of grade to 150
138 v_preferred_grade VARCHAR2(150);
139 v_requested_quantity2 NUMBER;
140 v_requested_quantity_uom2 VARCHAR2(3);
141 -- hverddin 27-JUN-00 End Of OPM Changes
142
143 -- Track local PL/SQL table information
144 g_del_current_line NUMBER := 1;
145 g_rel_current_line NUMBER := 1;
146 first_line relRecTyp;
147
148 -- Return status of procedures
149 g_return_status VARCHAR2(1);
150
151 -- FORWARD DECLARATIONS
152 --
153
154 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_PR_CRITERIA';
155
156 PROCEDURE Insert_RL_Row(
157 p_enforce_ship_set_and_smc IN VARCHAR2,
158 x_skip_detail OUT NOCOPY VARCHAR2, --Bug# 3248578
159 x_api_status OUT NOCOPY VARCHAR2
160 );
161
162 PROCEDURE Process_Buffer(
163 p_print_flag IN VARCHAR2,
164 p_buffer_name IN VARCHAR2,
165 p_buffer_text IN VARCHAR2,
166 p_bind_value IN VARCHAR2 default NULL
167 );
168
169
170 -- Start of comments
171 -- API name : Validate_SS_SMC
172 -- Type : Private
173 -- Pre-reqs : None.
174 -- Procedure: API to validate Ship Set Competitions. Api does
175 -- 1. Checks if any lines in the ship set are not yet imported.
176 -- 2. If number of Ship Set lines in Server Side is not equal to Release table lines, then reset the release table.
177 -- Parameters :
178 -- IN:
179 -- p_ship_set_id IN Ship set id.
180 -- p_top_model_line_id IN Top model line id, used when ship_set_id is null.
181 -- p_order_header_id IN Order header id.
182 -- OUT:
183 -- x_api_status OUT NOCOPY Standard to output api status.
184 -- End of comments
185 PROCEDURE validate_ss_smc(
186 p_ship_set_id IN NUMBER,
187 p_top_model_line_id IN NUMBER,
188 p_order_header_id IN NUMBER,
189 p_source_code IN VARCHAR2,
190 x_api_status OUT NOCOPY VARCHAR2)
191 IS
192 --
193 l_start_index NUMBER := 0;
194 l_end_index NUMBER := 0;
195 l_ss_count NUMBER := 0;
196 l_smc_count NUMBER := 0;
197 l_db_count NUMBER := 0;
198 l_status BOOLEAN;
199 --
200 l_debug_on BOOLEAN;
201 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_SS_SMC';
202 --
203 BEGIN
204 --
205 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
206 --
207 IF l_debug_on IS NULL
208 THEN
209 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
210 END IF;
211 --
212 IF l_debug_on THEN
213 WSH_DEBUG_SV.push(l_module_name);
214 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_SET_ID',P_SHIP_SET_ID);
215 WSH_DEBUG_SV.log(l_module_name,'P_TOP_MODEL_LINE_ID',P_TOP_MODEL_LINE_ID);
216 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_HEADER_ID',P_ORDER_HEADER_ID);
217 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
218 END IF;
219 --
220 x_api_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
221 --
222
223 IF l_debug_on THEN
224 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
225 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit OE_Shipping_Integration_PUB.Check_Import_Pending_Lines',WSH_DEBUG_SV.C_PROC_LEVEL);
226 END IF;
227
228 -- Checks if any lines in the ship set are not yet imported
229 -- Bug :3832310. The call to "WSH_DELIVERY_VALIDATIONS.Check_SS_Imp_Pending"
230 -- is changed to "OE_Shipping_Integration_PUB.Check_Import_Pending_Lines".
231 -- Since this call is applicable to both ship sets and SMC's make this
232 -- call before the IF condition branching the logic for these two kinds of
233 -- groupings.
234
235 l_status := OE_Shipping_Integration_PUB.Check_Import_Pending_Lines(
236 p_header_id => P_ORDER_HEADER_ID,
237 p_ship_set_id => p_ship_set_id,
238 p_top_model_line_id => P_TOP_MODEL_LINE_ID,
239 p_transactable_flag => 'Y',
240 x_return_status => x_api_status );
241
242 IF (x_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
243 IF l_debug_on THEN
244 WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WSH_DELIVERY_VALIDATIONS.CHECK_SS_IMP_PENDING');
245 WSH_DEBUG_SV.pop(l_module_name);
246 END IF;
247 RETURN;
248 END IF;
249
250 IF (p_ship_set_id IS NOT NULL) THEN
251 --{
252
253 IF (l_status = FALSE) THEN
254 --All lines in SS are imported
255
256 SELECT count(*)
257 INTO l_ss_count
258 FROM wsh_delivery_details
259 WHERE ship_set_id = p_ship_set_id
260 AND source_header_id = p_order_header_id
261 AND source_code = p_source_code
262 AND released_status IN ('R','N','B')
263 AND pickable_flag = 'Y';
264 --
265 IF l_debug_on THEN
266 WSH_DEBUG_SV.logmsg(l_module_name, 'FOUND '||L_SS_COUNT||' RECORDS IN DB' );
267 END IF;
268 --
269 ELSE
270
271 -- Some of the lines in SS are not imported
272 IF l_debug_on THEN
273 WSH_DEBUG_SV.logmsg(l_module_name, 'THERE EXISTS A LINE IN THIS SHIP SET WHICH IS NOT YET IMPORTED' );
274 END IF;
275 --
276 l_ss_count := -1;
277 --
278 END IF;
279
280
281 l_start_index := g_rel_current_line - 1;
282 WHILE ((l_start_index > 0) AND
283 (release_table(l_start_index).ship_set_id = p_ship_set_id)) LOOP
284
285 l_start_index := l_start_index -1;
286 END LOOP;
287
288 l_end_index := l_start_index;
289 l_start_index := g_rel_current_line - 1;
290 l_db_count := l_start_index - l_end_index;
291 --
292 IF l_debug_on THEN
293 WSH_DEBUG_SV.logmsg(l_module_name, 'FOUND '||L_DB_COUNT||' RECORDS IN RELEASE_TABLE');
294 END IF;
295 --
296
297
298 IF (l_db_count <> l_ss_count) THEN
299 -- release_table SS doesn't match with Database SS
300 IF (l_ss_count <> -1) THEN
301 IF l_debug_on THEN
302 WSH_DEBUG_SV.logmsg(l_module_name, 'THE DB COUNT FOR SS '||P_SHIP_SET_ID || ' DOES NOT MATCH WITH THE SELECTED RECORD COUNT');
303 END IF;
304 END IF;
305
306
307 IF l_debug_on THEN
308 WSH_DEBUG_SV.logmsg(l_module_name,'REMOVING THE SHIP SET FROM RELEASE TABLE');
309 END IF;
310
311 --Removing Ship Set lines from release table as SS doesn't match with Database SS
312 FOR i in l_end_index+1..l_start_index LOOP
313 release_table.delete(i);
314 END LOOP;
315
316 -- reset the g_rel_current_line after deletion
317 g_rel_current_line := l_end_index + 1;
318 --
319 IF l_debug_on THEN
320 WSH_DEBUG_SV.log(l_module_name,'G_REL_CURRENT_LINE',G_REL_CURRENT_LINE);
321 END IF;
322 --
323 END IF;
324 --}
325 ELSE --p_ship_set_id is NULL
326 --{
327
328 IF l_debug_on THEN
329 WSH_DEBUG_SV.logmsg(l_module_name, 'CHECKING FOR SMC WITH TOP_MODEL_LINE_ID '||P_TOP_MODEL_LINE_ID ||' FOR ORDER HEADER '||P_ORDER_HEADER_ID||' SOURCE CODE '||P_SOURCE_CODE );
330 END IF;
331 --
332
333 IF (l_status = FALSE) THEN
334 --Check for SMC with top_model_line_id
335 SELECT count(*)
336 INTO l_smc_count
337 FROM wsh_delivery_details
338 WHERE top_model_line_id = p_top_model_line_id
339 AND source_header_id = p_order_header_id
340 AND ship_model_complete_flag = 'Y'
341 AND source_code = p_source_code
342 AND released_status IN ('R','N','B')
343 AND pickable_flag = 'Y';
344
345 IF l_debug_on THEN
346 WSH_DEBUG_SV.logmsg(l_module_name, 'FOUND '||L_SMC_COUNT||' RECORDS IN DB' );
347 END IF;
348
349 ELSE
350 -- Some of the lines in SMC are not imported
351 IF l_debug_on THEN
352 WSH_DEBUG_SV.logmsg(l_module_name, 'THERE EXISTS A LINE IN THIS SMC SET WHICH IS NOT YET IMPORTED' );
353 END IF;
354 --
355 l_smc_count := -1;
356 --
357 END IF;
358
359
360 l_start_index := g_rel_current_line - 1;
361 WHILE ((l_start_index > 0) AND
362 (release_table(l_start_index).top_model_line_id = p_top_model_line_id))
363 LOOP
364 l_start_index := l_start_index -1;
365 END LOOP;
366
367 l_end_index := l_start_index;
368 l_start_index := g_rel_current_line - 1;
369 l_db_count := l_start_index - l_end_index;
370
371
372 IF l_debug_on THEN
373 WSH_DEBUG_SV.logmsg(l_module_name,'FOUND '||L_DB_COUNT||' RECORDS IN RELEASE_TABLE');
374 END IF;
375 --
376 IF (l_db_count <> l_smc_count) THEN
377 -- release_table SMC count doesn't match with Database SMC count
378 --
379 IF l_debug_on THEN
380 WSH_DEBUG_SV.logmsg(l_module_name, 'THE DB COUNT FOR SMC '||P_TOP_MODEL_LINE_ID || ' DOES NOT MATCH WITH THE SELECTED RECORD COUNT');
381 WSH_DEBUG_SV.logmsg(l_module_name, 'REMOVING THE SMC FROM RELEASE TABLE' );
382 END IF;
383
384
385 --Removing the SMC lines from Release Table.
386 FOR i in l_end_index+1..l_start_index LOOP
387 release_table.delete(i);
388 END LOOP;
389
390
391 -- reset the g_rel_current_line after deletion
392 g_rel_current_line := l_end_index + 1;
393 --
394 IF l_debug_on THEN
395 WSH_DEBUG_SV.logmsg(l_module_name, 'G_REL_CURRENT_LINE SET BACK TO '||G_REL_CURRENT_LINE );
396 END IF;
397 --
398 END IF;
399 --}
400 END IF;
401
402
403 --
404 IF l_debug_on THEN
405 WSH_DEBUG_SV.pop(l_module_name);
406 END IF;
407 --
408 EXCEPTION
409 --
410 WHEN OTHERS THEN
411 wsh_util_core.default_handler('WSH_PR_CRITERIA.validate_ss_smc');
412 x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
413 --
414 IF l_debug_on THEN
415 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
416 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
417 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
418 END IF;
419 --
420 END validate_ss_smc;
421
422
423 -- Start of comments
424 -- API name : Set_Globals
425 -- Type : Private
426 -- Pre-reqs : None.
427 -- Procedure: API to set the global variables related to Ship Sets and models.
428 -- Parameters :
429 -- IN:
430 -- p_enforce_ship_set_and_smc IN Whether to enforce Ship Set and SMC validate value Y/N.
431 -- p_ship_set_id IN Ship set id.
432 -- p_top_model_line_id IN Top model line id.
433 -- OUT:
434 -- None
435 -- End of comments
436 PROCEDURE Set_Globals(
437 p_enforce_ship_set_and_smc IN VARCHAR2,
438 p_ship_set_id IN NUMBER,
439 p_top_model_line_id IN NUMBER)
440 IS
441 --
442 l_debug_on BOOLEAN;
443 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SET_GLOBALS';
444 --
445 BEGIN
446 --
447 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
448 --
449 IF l_debug_on IS NULL
450 THEN
451 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
452 END IF;
453 --
454 IF l_debug_on THEN
455 WSH_DEBUG_SV.push(l_module_name);
456 WSH_DEBUG_SV.log(l_module_name,'P_ENFORCE_SHIP_SET_AND_SMC',P_ENFORCE_SHIP_SET_AND_SMC);
457 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_SET_ID',P_SHIP_SET_ID);
458 WSH_DEBUG_SV.log(l_module_name,'P_TOP_MODEL_LINE_ID',P_TOP_MODEL_LINE_ID);
459 END IF;
460 --
461
462 --IF only enforce ship set and SMC
463 IF (p_enforce_ship_set_and_smc = 'Y') THEN
464 g_lock_or_hold_failed := TRUE;
465 g_failed_ship_set_id := p_ship_set_id;
466 g_failed_top_model_line_id := p_top_model_line_id;
467 g_last_ship_set_id := p_ship_set_id;
468 g_last_top_model_line_id := p_top_model_line_id;
469 END IF;
470 --
471 IF l_debug_on THEN
472 WSH_DEBUG_SV.pop(l_module_name);
473 END IF;
474 --
475 END Set_Globals;
476
477
478 -- Start of comments
479 -- API name : Get_Detail_Lock
480 -- Type : Private
481 -- Pre-reqs : None.
482 -- Procedure: API to lock the delivery detail line for pick release. If locking is successful,then it check
483 -- for current date with previous retrive last update date, if not same then it re-query the detail lines.
484 -- Parameters :
485 -- IN:
486 -- p_delivery_detail_id IN Delivery detail id.
487 -- p_ship_set_id IN Ship set id.
488 -- p_top_model_line_id IN Top model line id.
489 -- p_enforce_ship_set_and_smc IN Whether to enforce Ship Set and SMC validate value Y/N.
490 -- OUT:
491 -- x_skip_detail OUT NOCOPY Ignoring delivery detail since no longer satisfies the pick release criteria,validate value Y/N.
492 -- x_return_status OUT NOCOPY Standard to output api status.
493 -- Bug 4775539 added 4 new out variables
494 -- End of comments
495
496 PROCEDURE Get_Detail_Lock(
497 p_delivery_detail_id IN NUMBER,
498 p_ship_set_id IN NUMBER,
499 p_top_model_line_id IN NUMBER,
500 p_enforce_ship_set_and_smc IN VARCHAR2,
501 -- Bug 4775539 added 4 new out variables
502 x_requested_qty_uom OUT NOCOPY VARCHAR2,
503 x_src_requested_qty_uom OUT NOCOPY VARCHAR2,
504 x_src_requested_qty OUT NOCOPY NUMBER,
505 x_inv_item_id OUT NOCOPY NUMBER,
506 x_skip_detail OUT NOCOPY VARCHAR2, -- Bug# 3248578
507 x_return_status OUT NOCOPY VARCHAR2)
508 IS
509 --
510 record_locked EXCEPTION;
511 PRAGMA EXCEPTION_INIT(record_locked, -54);
512 --
513
514 --Cursor to lock the delivery detail line.
515 CURSOR lock_for_update(v_del_detail_id IN NUMBER) IS
516 SELECT ROWID, LAST_UPDATE_DATE, --Bug# 3248578
517 -- Bug 4775539
518 REQUESTED_QUANTITY_UOM,
519 SRC_REQUESTED_QUANTITY_UOM,
520 SRC_REQUESTED_QUANTITY,
521 INVENTORY_ITEM_ID
522 FROM WSH_DELIVERY_DETAILS
523 WHERE DELIVERY_DETAIL_ID = v_del_detail_id
524 AND RELEASED_STATUS IN ('R','B','X')
525 AND MOVE_ORDER_LINE_ID IS NULL
526 FOR UPDATE NOWAIT;
527 --
528 -- Start Bug# 3248578
529
530 --Cursor to get the delivery detail info.
531 CURSOR new_detail_info(v_del_detail_id IN NUMBER) IS
532 SELECT wdd.SOURCE_LINE_ID, -- Start New Col Addition
533 wdd.SOURCE_HEADER_ID,
534 wdd.INVENTORY_ITEM_ID,
535 WDD.SHIPMENT_PRIORITY_CODE,
536 WDD.SOURCE_CODE SOURCE_CODE,
537 WDD.SOURCE_HEADER_NUMBER SOURCE_HEADER_NUMBER, -- End New Col Addition
538 wdd.organization_id,
539 wdd.move_order_line_id,
540 wdd.ship_from_location_id,
541 wdd.ship_method_code,
542 wdd.shipment_priority_code,
543 wdd.date_scheduled,
544 wdd.requested_quantity,
545 wdd.requested_quantity_uom,
546 wdd.preferred_grade,
547 wdd.requested_quantity2,
548 wdd.requested_quantity_uom2,
549 wdd.project_id,
550 wdd.task_id,
551 wdd.subinventory,
552 wdd.subinventory,
553 wdd.released_status,
554 wdd.ship_model_complete_flag,
555 wdd.top_model_line_id,
556 wdd.ship_set_id,
557 wda.delivery_id,
558 wdd.last_update_date
559 FROM wsh_delivery_details wdd,
560 wsh_delivery_assignments_v wda
561 WHERE wdd.delivery_detail_id = v_del_detail_id
562 AND wda.delivery_detail_id = wdd.delivery_detail_id
563 AND wdd.date_scheduled IS NOT NULL
564 AND wdd.date_requested >= nvl(g_from_request_date, wdd.date_requested)
565 AND wdd.date_requested <= nvl(g_to_request_date, wdd.date_requested)
566 AND wdd.date_scheduled >= nvl(g_from_sched_ship_date, wdd.date_scheduled)
567 AND wdd.date_scheduled <= nvl(g_to_sched_ship_date, wdd.date_scheduled)
568 AND nvl(wdd.requested_quantity,0) > 0
569 AND wdd.released_status IN ('R','B','X')
570 -- bug 5166340: wdd subinventory needs to be compared
571 -- with g_RelSubInventory(subinventory field value)
572 -- not with g_from_subinventory(pick from subinventory field value)
573 -- AND nvl(wdd.subinventory, -99) = decode(g_from_subinventory, NULL,
574 -- nvl(wdd.subinventory, -99), g_from_subinventory)
575 AND nvl(wdd.subinventory, -99) = decode(g_RelSubInventory, NULL,
576 nvl(wdd.subinventory, -99), g_RelSubInventory)
577 AND nvl(wdd.project_id,0) = decode(g_project_id, 0, nvl(wdd.project_id,0), g_project_id)
578 AND nvl(wdd.task_id,0) = decode(g_task_id, 0, nvl(wdd.task_id,0), g_task_id)
579 AND nvl(wdd.ship_set_id,0) = decode(g_ship_set_number, 0, nvl(wdd.ship_set_id,0), g_ship_set_number)
580 AND nvl(wdd.shipment_priority_code,-99) = decode(g_shipment_priority, NULL,
581 nvl(wdd.shipment_priority_code,-99), g_shipment_priority)
582 AND wdd.organization_id = nvl(g_organization_id, wdd.organization_id)
583 AND wdd.ship_from_location_id = decode(g_ship_from_loc_id, -1, wdd.ship_from_location_id,
584 g_ship_from_loc_id)
585 AND (( wda.delivery_id IS NOT NULL AND ( g_include_planned_lines <> 'N' OR
586 wda.delivery_id = g_delivery_id OR
587 g_trip_id <> 0
588 )
589 ) OR
590 ( wda.delivery_id IS NULL AND g_delivery_id = 0 AND g_trip_id = 0 )
591 );
592
593 -- End Bug# 3248578
594
595 l_rowid VARCHAR2(30);
596
597 l_last_update_date DATE; --Bug# 3248578
598
599 l_debug_on BOOLEAN;
600 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DETAIL_LOCK';
601 --
602 BEGIN
603 --
604 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
605 --
606 IF l_debug_on IS NULL THEN
607 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
608 END IF;
609 --
610 IF l_debug_on THEN
611 WSH_DEBUG_SV.push(l_module_name);
612 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
613 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_SET_ID',P_SHIP_SET_ID);
614 WSH_DEBUG_SV.log(l_module_name,'P_TOP_MODEL_LINE_ID',P_TOP_MODEL_LINE_ID);
615 WSH_DEBUG_SV.log(l_module_name,'P_ENFORCE_SHIP_SET_AND_SMC',P_ENFORCE_SHIP_SET_AND_SMC);
616 END IF;
617 --
618 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
619 x_skip_detail := 'N'; --Bug# 3248578
620 --
621
622 --Cursor to lock the delivery detail line.
623 OPEN lock_for_update(p_delivery_detail_id);
624 FETCH lock_for_update INTO l_rowid, l_last_update_date,
625 -- Bug 4775539
626 x_requested_qty_uom,x_src_requested_qty_uom,x_src_requested_qty,x_inv_item_id;
627 --
628 IF lock_for_update%NOTFOUND THEN
629 CLOSE lock_for_update;
630 RAISE no_data_found;
631 END IF;
632
633 -- Start Bug# 3248578
634 IF l_debug_on THEN
635 WSH_DEBUG_SV.log(l_module_name,'l_last_update_date',l_last_update_date);
636 WSH_DEBUG_SV.log(l_module_name,'v_last_update_date',v_last_update_date);
637 END IF;
638
639
640 IF l_last_update_date <> v_last_update_date THEN
641 -- Record has been changed during the Pick Release process
642 IF l_debug_on THEN
643 WSH_DEBUG_SV.log(l_module_name,'Record has been changed during the Pick Release process');
644 END IF;
645
646 --Cursor to get the detail info.
647 OPEN new_detail_info(p_delivery_detail_id);
648 FETCH new_detail_info INTO
649 v_line_id, -- Start New Col Addition
650 v_header_id,
651 v_inventory_item_id,
652 v_shipment_priority,
653 v_source_code,
654 v_source_header_number, -- End New Col Addition
655 v_org_id,
656 v_move_order_line_id,
657 v_ship_from_location_id,
658 v_ship_method_code,
659 v_shipment_priority,
660 v_date_scheduled,
661 v_requested_quantity,
662 v_requested_quantity_uom,
663 v_preferred_grade,
664 v_requested_quantity2,
665 v_requested_quantity_uom2,
666 v_project_id,
667 v_task_id,
668 v_from_sub,
669 v_to_sub,
670 v_released_status,
671 v_ship_model_complete_flag,
672 v_top_model_line_id,
673 v_ship_set_id,
674 v_delivery_id,
675 v_last_update_date;
676 IF new_detail_info%NOTFOUND THEN
677 --jckwok wrap debug stmts around if.. end if
678 IF l_debug_on THEN
679 WSH_DEBUG_SV.logmsg(l_module_name,'Ignoring delivery detail : '||p_delivery_detail_id ||' since no longer satisfies the pick release criteria' );
680 END IF;
681 x_skip_detail := 'Y';
682 IF lock_for_update%ISOPEN THEN
683 CLOSE lock_for_update;
684 END IF;
685 END IF;
686 CLOSE new_detail_info;
687
688 END IF;
689 -- End Bug# 3248578
690 --
691
692 --Successfully lock the detail lines.
693 IF l_debug_on THEN
694 WSH_DEBUG_SV.logmsg(l_module_name, 'SUCCESSFULLY LOCKED DELIVERY DETAIL '||P_DELIVERY_DETAIL_ID);
695 WSH_DEBUG_SV.pop(l_module_name);
696 END IF;
697 --
698 EXCEPTION
699 --
700 WHEN record_locked THEN
701 --
702 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
703 -- Bug Bug 4775539
704 x_requested_qty_uom := null;
705 x_src_requested_qty_uom := null;
706 x_src_requested_qty := null;
707 x_inv_item_id := null;
708
709 Set_Globals(p_enforce_ship_set_and_smc, p_ship_set_id, p_top_model_line_id);
710 --
711 IF l_debug_on THEN
712 WSH_DEBUG_SV.logmsg(l_module_name, 'CANNOT LOCK DELIVERY DETAIL FOR UPDATE: '|| P_DELIVERY_DETAIL_ID);
713 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
714 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
715 END IF;
716 --
717 WHEN no_data_found THEN
718 --
719 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
720 Set_Globals(p_enforce_ship_set_and_smc, p_ship_set_id, p_top_model_line_id);
721 --
722 IF l_debug_on THEN
723 WSH_DEBUG_SV.logmsg(l_module_name, 'DELIVERY DETAIL LINE NOT FOUND: '|| P_DELIVERY_DETAIL_ID);
724 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
725 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
726 END IF;
727 --
728 END Get_Detail_Lock;
729
730
731
732 -- Start of comments
733 -- API name : Init
734 -- Type : Public
735 -- Pre-reqs : None.
736 -- Procedure: API to initializes session and criteria for pick release. Api does
737 -- 1. Initializes variables for the session
738 -- 2. Retrieves criteria for the batch and sets up session variables.
739 -- 3. Locks row for the batch
740 -- 4. Update who columns for the batch
741 -- Parameters :
742 -- IN:
743 -- p_batch_id IN batch to be processed
744 -- p_worker_id IN worker id
745 -- OUT:
746 -- x_api_status OUT NOCOPY Standard to output api status.
747 -- End of comments
748
749 PROCEDURE Init(
750 p_batch_id IN NUMBER,
751 p_worker_id IN NUMBER,
752 x_api_status OUT NOCOPY VARCHAR2
753 ) IS
754 -- cursor to get batch parameter information
755 CURSOR get_batch(v_batch_id IN NUMBER) IS
756 SELECT NAME,
757 BACKORDERS_ONLY_FLAG,
758 NVL(AUTODETAIL_PR_FLAG, NULL),
759 NVL(AUTO_PICK_CONFIRM_FLAG, NULL),
760 NVL(PICK_SEQUENCE_RULE_ID, ''),
761 NVL(PICK_GROUPING_RULE_ID, ''),
762 NVL(INCLUDE_PLANNED_LINES, 'N'),
763 NVL(ORGANIZATION_ID, ''),
764 NVL(CUSTOMER_ID, 0),
765 NVL(FROM_REQUESTED_DATE, NULL),
766 NVL(TO_REQUESTED_DATE, NULL),
767 NVL(EXISTING_RSVS_ONLY_FLAG, 'N'),
768 NVL(ORDER_HEADER_ID, 0),
769 NVL(INVENTORY_ITEM_ID, 0),
770 NVL(TRIP_ID, 0),
771 NVL(TRIP_STOP_ID, 0),
772 NVL(DELIVERY_ID, 0),
773 NVL(ORDER_TYPE_ID, 0),
774 NVL(FROM_SCHEDULED_SHIP_DATE, NULL),
775 NVL(TO_SCHEDULED_SHIP_DATE, NULL),
776 NVL(SHIPMENT_PRIORITY_CODE, ''),
777 NVL(SHIP_METHOD_CODE, ''),
778 NVL(SHIP_SET_NUMBER, 0),
779 NVL(DELIVERY_DETAIL_ID, 0),
780 NVL(SHIP_TO_LOCATION_ID, 0),
781 NVL(DEFAULT_STAGE_SUBINVENTORY, ''),
782 NVL(DEFAULT_STAGE_LOCATOR_ID,''),
783 NVL(PICK_FROM_SUBINVENTORY,''),
784 NVL(PICK_FROM_LOCATOR_ID,''),
785 NVL(TASK_ID,0),
786 NVL(PROJECT_ID,0),
787 NVL(SHIP_FROM_LOCATION_ID, -1),
788 NVL(AUTOCREATE_DELIVERY_FLAG, NULL),
789 SHIP_CONFIRM_RULE_ID,
790 NVL(AUTOPACK_FLAG, 'N'),
791 AUTOPACK_LEVEL,
792 NON_PICKING_FLAG,
793 NVL(ORDER_LINE_ID, 0),
794 NVL(DOCUMENT_SET_ID, '-1'),
795 TASK_PLANNING_FLAG,
796 --
797 -- rlanka : Pack J Enhancement
798 NVL(category_set_id, 0),
799 NVL(category_id, 0),
800 NVL(region_id, 0),
801 NVL(zone_id, 0),
802 NVL(ac_delivery_criteria, NULL),
803 NVL(rel_subinventory, NULL),
804 --
805 -- deliveryMerge
806 NVL(append_flag, NULL),
807 -- Bug #3266659 : Shipset/SMC Criteria
808 NVL(SHIP_SET_SMC_FLAG,'A'),
809 ACTUAL_DEPARTURE_DATE,
810 NVL(ALLOCATION_METHOD,'I'), --anxsharm, X-dock
811 CROSSDOCK_CRITERIA_ID , --anxsharm, X-dock
812 DYNAMIC_REPLENISHMENT_FLAG --bug# 6689448 (replenishment project)
813 FROM WSH_PICKING_BATCHES
814 WHERE BATCH_ID = v_batch_id;
815
816 -- cursor to get batch parameter information
817 CURSOR get_lock_batch(v_batch_id IN NUMBER) IS
818 SELECT NAME,
819 BACKORDERS_ONLY_FLAG,
820 NVL(AUTODETAIL_PR_FLAG, NULL),
821 NVL(AUTO_PICK_CONFIRM_FLAG, NULL),
822 NVL(PICK_SEQUENCE_RULE_ID, ''),
823 NVL(PICK_GROUPING_RULE_ID, ''),
824 NVL(INCLUDE_PLANNED_LINES, 'N'),
825 NVL(ORGANIZATION_ID, ''),
826 NVL(CUSTOMER_ID, 0),
827 NVL(FROM_REQUESTED_DATE, NULL),
828 NVL(TO_REQUESTED_DATE, NULL),
829 NVL(EXISTING_RSVS_ONLY_FLAG, 'N'),
830 NVL(ORDER_HEADER_ID, 0),
831 NVL(INVENTORY_ITEM_ID, 0),
832 NVL(TRIP_ID, 0),
833 NVL(TRIP_STOP_ID, 0),
834 NVL(DELIVERY_ID, 0),
835 NVL(ORDER_TYPE_ID, 0),
836 NVL(FROM_SCHEDULED_SHIP_DATE, NULL),
837 NVL(TO_SCHEDULED_SHIP_DATE, NULL),
838 NVL(SHIPMENT_PRIORITY_CODE, ''),
839 NVL(SHIP_METHOD_CODE, ''),
840 NVL(SHIP_SET_NUMBER, 0),
841 NVL(DELIVERY_DETAIL_ID, 0),
842 NVL(SHIP_TO_LOCATION_ID, 0),
843 NVL(DEFAULT_STAGE_SUBINVENTORY, ''),
844 NVL(DEFAULT_STAGE_LOCATOR_ID,''),
845 NVL(PICK_FROM_SUBINVENTORY,''),
846 NVL(PICK_FROM_LOCATOR_ID,''),
847 NVL(TASK_ID,0),
848 NVL(PROJECT_ID,0),
849 NVL(SHIP_FROM_LOCATION_ID, -1),
850 NVL(AUTOCREATE_DELIVERY_FLAG, NULL),
851 SHIP_CONFIRM_RULE_ID,
852 NVL(AUTOPACK_FLAG, 'N'),
853 AUTOPACK_LEVEL,
854 NON_PICKING_FLAG,
855 NVL(ORDER_LINE_ID, 0),
856 NVL(DOCUMENT_SET_ID, '-1'),
857 TASK_PLANNING_FLAG,
858 --
859 -- rlanka : Pack J Enhancement
860 NVL(category_set_id, 0),
861 NVL(category_id, 0),
862 NVL(region_id, 0),
863 NVL(zone_id, 0),
864 NVL(ac_delivery_criteria, NULL),
865 NVL(rel_subinventory, NULL),
866 --
867 -- deliveryMerge
868 NVL(append_flag, NULL),
869 -- Bug #3266659 : Shipset/SMC Criteria
870 NVL(SHIP_SET_SMC_FLAG,'A'),
871 ACTUAL_DEPARTURE_DATE,
872 NVL(ALLOCATION_METHOD,'I'), --anxsharm, X-dock
873 CROSSDOCK_CRITERIA_ID, --anxsharm, X-dock
874 DYNAMIC_REPLENISHMENT_FLAG --bug# 6689448 (replenishment project)
875 FROM WSH_PICKING_BATCHES
876 WHERE BATCH_ID = v_batch_id
877 FOR UPDATE OF BATCH_ID NOWAIT;
878
879 -- cursor to get line information
880 CURSOR get_line_info(v_line_id In NUMBER) IS
881 SELECT HEADER_ID
882 FROM OE_ORDER_LINES_ALL
883 WHERE LINE_ID = v_line_id;
884
885 -- cursor to get lines within a container
886 CURSOR get_inner_items (v_del_detail_id NUMBER) IS
887 SELECT WDD.DELIVERY_DETAIL_ID
888 FROM wsh_delivery_assignments_v WDA,
889 WSH_DELIVERY_DETAILS WDD
890 WHERE WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
891 AND WDD.CONTAINER_FLAG = 'N'
892 AND WDA.DELIVERY_ASSIGNMENT_ID IN
893 (SELECT WDA1.DELIVERY_ASSIGNMENT_ID
894 FROM wsh_delivery_assignments_v WDA1
895 START WITH WDA1.PARENT_DELIVERY_DETAIL_ID = v_del_detail_id
896 CONNECT BY PRIOR WDA1.DELIVERY_DETAIL_ID = WDA1.PARENT_DELIVERY_DETAIL_ID);
897
898 -- Cursor to get lines within a batch.
899 -- Changes: added checks on LINE_DIRECTION for J Inbound Logistics. jckwok
900
901 -- Bug 3433645 :
902 -- Added condition AND CONTAINER_FLAG = 'Y' to fetch containers only.
903
904 CURSOR get_batch_details (v_batch_id NUMBER, v_del_det NUMBER) IS
905 SELECT DELIVERY_DETAIL_ID -- If Launch PR from STF for del details, we stamp batch id
906 FROM WSH_DELIVERY_DETAILS -- on details. wsh_picking_batches has the detail as -1,
907 WHERE BATCH_ID = v_batch_id -- batch can have several delivery details
908 AND nvl(LINE_DIRECTION , 'O') IN ('O', 'IO')
909 AND CONTAINER_FLAG='Y'
910 UNION
911 SELECT DELIVERY_DETAIL_ID -- If use PR form then we do not stamp batch id in wdd,
912 FROM WSH_DELIVERY_DETAILS -- wsh_picking_batches has the delivery detail stamped
913 WHERE DELIVERY_DETAIL_ID = v_del_det -- we can specify only one detail per batch
914 AND nvl(LINE_DIRECTION , 'O') IN ('O', 'IO')
915 AND BATCH_ID IS NULL
916 AND CONTAINER_FLAG='Y';
917
918 record_locked EXCEPTION;
919 PRAGMA EXCEPTION_INIT(record_locked, -54);
920 l_order_header_id NUMBER;
921 l_temp_line NUMBER;
922
923 --
924 l_debug_on BOOLEAN;
925 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INIT';
926 --
927
928 -- Variables for INV_Validate APIs
929 l_validate_org INV_Validate.Org;
930 l_validate_sub INV_Validate.Sub;
931 l_validate_from_sub INV_Validate.Sub;
932 l_validate_locator INV_Validate.Locator;
933 l_validate_item INV_Validate.Item;
934
935
936 BEGIN
937 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
938 IF l_debug_on IS NULL
939 THEN
940 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
941 END IF;
942
943 IF l_debug_on THEN
944 WSH_DEBUG_SV.push(l_module_name);
945 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
946 WSH_DEBUG_SV.log(l_module_name,'P_WORKER_ID',P_WORKER_ID);
947 END IF;
948 --
949 -- initialize the WHO session variables
950 g_request_id := FND_GLOBAL.CONC_REQUEST_ID;
951 g_application_id := FND_GLOBAL.PROG_APPL_ID;
952 g_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
953 g_user_id := FND_GLOBAL.USER_ID;
954 g_login_id := FND_GLOBAL.CONC_LOGIN_ID;
955
956 IF l_debug_on THEN
957 WSH_DEBUG_SV.log(l_module_name, 'REQUEST_ID', TO_CHAR(G_REQUEST_ID));
958 WSH_DEBUG_SV.log(l_module_name, 'APPLICATION_ID',TO_CHAR(G_APPLICATION_ID));
959 WSH_DEBUG_SV.log(l_module_name, 'PROGRAM_ID', TO_CHAR(G_PROGRAM_ID));
960 WSH_DEBUG_SV.log(l_module_name, 'USER_ID', TO_CHAR(G_USER_ID));
961 WSH_DEBUG_SV.log(l_module_name, 'LOGIN_ID', TO_CHAR(G_LOGIN_ID));
962 END IF;
963
964 IF p_worker_id IS NULL THEN --{
965 -- fetch release criteria for the batch and lock row
966 OPEN get_lock_batch(p_batch_id);
967 FETCH get_lock_batch
968 INTO g_batch_name,
969 g_backorders_flag,
970 g_autodetail_flag,
971 g_auto_pick_confirm_flag,
972 g_pick_seq_rule_id,
973 g_pick_grouping_rule_id,
974 g_include_planned_lines,
975 g_organization_id,
976 g_customer_id,
977 g_from_request_date,
978 g_to_request_date,
979 g_existing_rsvs_only_flag,
980 g_order_header_id,
981 g_inventory_item_id,
982 g_trip_id,
983 g_trip_stop_id,
984 g_delivery_id,
985 g_order_type_id,
986 g_from_sched_ship_date,
987 g_to_sched_ship_date,
988 g_shipment_priority,
989 g_ship_method_code,
990 g_ship_set_number,
991 g_del_detail_id,
992 g_ship_to_loc_id,
993 g_to_subinventory,
994 g_to_locator,
995 g_from_subinventory,
996 g_from_locator,
997 g_task_id,
998 g_project_id,
999 g_ship_from_loc_id,
1000 g_autocreate_deliveries,
1001 g_auto_ship_confirm_rule_id,
1002 g_autopack_flag,
1003 g_autopack_level,
1004 g_non_picking_flag,
1005 g_order_line_id,
1006 g_doc_set_id,
1007 g_task_planning_flag,
1008 g_CategorySetID,
1009 g_CategoryID,
1010 g_RegionID,
1011 g_ZoneID,
1012 g_acDelivCriteria,
1013 g_RelSubInventory,
1014 g_append_flag,
1015 g_ship_set_smc_flag,
1016 g_actual_departure_date,
1017 g_allocation_method, -- anxsharm, X-dock
1018 g_crossdock_criteria_id, -- anxsharm, X-dock
1019 g_dynamic_replenishment_flag; --bug# 6689448 (replenishment project)
1020
1021 -- Handle batch does not exist condition
1022 IF get_lock_batch%NOTFOUND THEN
1023 x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1024 IF l_debug_on THEN
1025 WSH_DEBUG_SV.logmsg(l_module_name, 'BATCH ID ' || TO_CHAR(P_BATCH_ID) || ' DOES NOT EXIST.');
1026 WSH_DEBUG_SV.pop(l_module_name);
1027 END IF;
1028 IF get_lock_batch%ISOPEN THEN
1029 CLOSE get_lock_batch;
1030 END IF;
1031 RETURN;
1032 END IF;
1033 IF get_lock_batch%ISOPEN THEN
1034 CLOSE get_lock_batch;
1035 END IF;
1036 --}
1037 ELSE --{
1038 -- fetch release criteria for the batch
1039 OPEN get_batch(p_batch_id);
1040 FETCH get_batch
1041 INTO g_batch_name,
1042 g_backorders_flag,
1043 g_autodetail_flag,
1044 g_auto_pick_confirm_flag,
1045 g_pick_seq_rule_id,
1046 g_pick_grouping_rule_id,
1047 g_include_planned_lines,
1048 g_organization_id,
1049 g_customer_id,
1050 g_from_request_date,
1051 g_to_request_date,
1052 g_existing_rsvs_only_flag,
1053 g_order_header_id,
1054 g_inventory_item_id,
1055 g_trip_id,
1056 g_trip_stop_id,
1057 g_delivery_id,
1058 g_order_type_id,
1059 g_from_sched_ship_date,
1060 g_to_sched_ship_date,
1061 g_shipment_priority,
1062 g_ship_method_code,
1063 g_ship_set_number,
1064 g_del_detail_id,
1065 g_ship_to_loc_id,
1066 g_to_subinventory,
1067 g_to_locator,
1068 g_from_subinventory,
1069 g_from_locator,
1070 g_task_id,
1071 g_project_id,
1072 g_ship_from_loc_id,
1073 g_autocreate_deliveries,
1074 g_auto_ship_confirm_rule_id,
1075 g_autopack_flag,
1076 g_autopack_level,
1077 g_non_picking_flag,
1078 g_order_line_id,
1079 g_doc_set_id,
1080 g_task_planning_flag,
1081 g_CategorySetID,
1082 g_CategoryID,
1083 g_RegionID,
1084 g_ZoneID,
1085 g_acDelivCriteria,
1086 g_RelSubInventory,
1087 g_append_flag,
1088 g_ship_set_smc_flag,
1089 g_actual_departure_date,
1090 g_allocation_method, -- anxsharm, X-dock
1091 g_crossdock_criteria_id, -- anxsharm, X-dock
1092 g_dynamic_replenishment_flag; --bug# 6689448 (replenishment project)
1093
1094 -- Handle batch does not exist condition
1095 IF get_batch%NOTFOUND THEN
1096 x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1097 IF l_debug_on THEN
1098 WSH_DEBUG_SV.logmsg(l_module_name, 'BATCH ID ' || TO_CHAR(P_BATCH_ID) || ' DOES NOT EXIST.');
1099 WSH_DEBUG_SV.pop(l_module_name);
1100 END IF;
1101 IF get_batch%ISOPEN THEN
1102 CLOSE get_batch;
1103 END IF;
1104 RETURN;
1105 END IF;
1106 IF get_batch%ISOPEN THEN
1107 CLOSE get_batch;
1108 END IF;
1109
1110 END IF; --}
1111
1112 -- Write to log the variables that have been initialized
1113 IF l_debug_on THEN
1114 WSH_DEBUG_SV.logmsg(l_module_name,'PICK RELEASE PARAMETERS ARE...' );
1115 WSH_DEBUG_SV.log(l_module_name, 'BATCH_NAME', G_BATCH_NAME);
1116 WSH_DEBUG_SV.log(l_module_name, 'BACKORDERS_FLAG', G_BACKORDERS_FLAG);
1117 WSH_DEBUG_SV.log(l_module_name, 'AUTODETAIL_PR_FLAG', G_AUTODETAIL_FLAG);
1118 WSH_DEBUG_SV.log(l_module_name, 'AUTO_PICK_CONFIRM_FLAG', G_AUTO_PICK_CONFIRM_FLAG);
1119 WSH_DEBUG_SV.log(l_module_name, 'ORDER_HEADER_ID', G_ORDER_HEADER_ID);
1120 WSH_DEBUG_SV.log(l_module_name, 'ORDER_TYPE_ID', G_ORDER_TYPE_ID);
1121 WSH_DEBUG_SV.log(l_module_name, 'SHIP_FROM_LOC_ID', G_SHIP_FROM_LOC_ID);
1122 WSH_DEBUG_SV.log(l_module_name, 'ORGANIZATION_ID', G_ORGANIZATION_ID);
1123 WSH_DEBUG_SV.log(l_module_name, 'CUSTOMER_ID', G_CUSTOMER_ID);
1124 WSH_DEBUG_SV.log(l_module_name, 'SHIP_TO_LOC_ID', G_SHIP_TO_LOC_ID);
1125 WSH_DEBUG_SV.log(l_module_name, 'SHIPMENT_PRIORITY', G_SHIPMENT_PRIORITY);
1126 WSH_DEBUG_SV.log(l_module_name, 'SHIP_METHOD_CODE', G_SHIP_METHOD_CODE);
1127 WSH_DEBUG_SV.log(l_module_name, 'SHIP_SET_NUMBER', G_SHIP_SET_NUMBER);
1128 WSH_DEBUG_SV.log(l_module_name, 'LINE/CONTAINER ID', G_DEL_DETAIL_ID);
1129 WSH_DEBUG_SV.log(l_module_name, 'FROM_REQUEST_DATE',
1130 TO_CHAR(G_FROM_REQUEST_DATE , 'DD-MON-YYYY HH24:MI:SS'));
1131 WSH_DEBUG_SV.log(l_module_name, 'TO_REQUEST_DATE',
1132 TO_CHAR(G_TO_REQUEST_DATE , 'DD-MON-YYYY HH24:MI:SS'));
1133 WSH_DEBUG_SV.log(l_module_name, 'FROM_SCHED_SHIP_DATE',
1134 TO_CHAR(G_FROM_SCHED_SHIP_DATE , 'DD-MON-YYYY HH24:MI:SS'));
1135 WSH_DEBUG_SV.log(l_module_name, 'TO_SCHED_SHIP_DATE',
1136 TO_CHAR(G_TO_SCHED_SHIP_DATE , 'DD-MON-YYYY HH24:MI:SS'));
1137 WSH_DEBUG_SV.log(l_module_name, 'EXISTING_RSVS_ONLY_FLAG', G_EXISTING_RSVS_ONLY_FLAG);
1138 WSH_DEBUG_SV.log(l_module_name, 'TO_SUBINVENTORY', G_TO_SUBINVENTORY);
1139 WSH_DEBUG_SV.log(l_module_name, 'TO_LOCATOR', G_TO_LOCATOR);
1140 WSH_DEBUG_SV.log(l_module_name, 'FROM_SUBINVENTORY', G_FROM_SUBINVENTORY);
1141 WSH_DEBUG_SV.log(l_module_name, 'FROM_LOCATOR', G_FROM_LOCATOR);
1142 WSH_DEBUG_SV.log(l_module_name, 'INVENTORY_ITEM_ID', G_INVENTORY_ITEM_ID);
1143 WSH_DEBUG_SV.log(l_module_name, 'TRIP_ID', G_TRIP_ID);
1144 WSH_DEBUG_SV.log(l_module_name, 'TRIP_STOP_ID', G_TRIP_STOP_ID);
1145 WSH_DEBUG_SV.log(l_module_name, 'DELIVERY_ID', G_DELIVERY_ID);
1146 WSH_DEBUG_SV.log(l_module_name, 'PICK_GROUPING_RULE_ID', G_PICK_GROUPING_RULE_ID);
1147 WSH_DEBUG_SV.log(l_module_name, 'PICK_SEQ_RULE_ID', G_PICK_SEQ_RULE_ID);
1148 WSH_DEBUG_SV.log(l_module_name, 'DOC_SET_ID', G_DOC_SET_ID);
1149 WSH_DEBUG_SV.log(l_module_name, 'INCLUDE_PLANNED_LINES', G_INCLUDE_PLANNED_LINES);
1150 WSH_DEBUG_SV.log(l_module_name, 'AUTOCREATE_DELIVERY_FLAG', G_AUTOCREATE_DELIVERIES);
1151 WSH_DEBUG_SV.log(l_module_name, 'ORDER_LINE_ID', G_ORDER_LINE_ID);
1152 WSH_DEBUG_SV.log(l_module_name, 'PROJECT_ID', G_PROJECT_ID);
1153 WSH_DEBUG_SV.log(l_module_name, 'TASK_ID', G_TASK_ID);
1154 WSH_DEBUG_SV.log(l_module_name, 'TASK_PLANNING_FLAG', G_TASK_PLANNING_FLAG);
1155 -- rlanka : Pack J Enhancement
1156 WSH_DEBUG_SV.log(l_module_name, 'Region ID', g_RegionID);
1157 WSH_DEBUG_SV.log(l_module_name, 'Zone ID', g_ZoneID);
1158 WSH_DEBUG_SV.log(l_module_name, 'Category Set ID',g_CategorySetID);
1159 WSH_DEBUG_SV.log(l_module_name, 'Category ID', g_CategoryID);
1160 WSH_DEBUG_SV.log(l_module_name, 'AC Deliv Criteria',g_acDelivCriteria);
1161 WSH_DEBUG_SV.log(l_module_name, 'Rel Subinventory', g_RelSubInventory);
1162 -- deliveryMerge
1163 WSH_DEBUG_SV.log(l_module_name, 'Append Flag', g_append_flag);
1164 WSH_DEBUG_SV.log(l_module_name, 'SHIP_SET_SMC_FLAG', G_SHIP_SET_SMC_FLAG );
1165 -- anxsharm, X-dock
1166 WSH_DEBUG_SV.log(l_module_name, 'ALLOCATION METHOD', G_ALLOCATION_METHOD );
1167 WSH_DEBUG_SV.log(l_module_name, 'CROSSDOCK CRITERIA ID', G_CROSSDOCK_CRITERIA_ID );
1168 --bug# 6689448 (replenishment project)
1169 WSH_DEBUG_SV.log(l_module_name, 'DYNAMIC REPLENISHMENT FLAG', G_DYNAMIC_REPLENISHMENT_FLAG);
1170 END IF;
1171
1172 -- Validate only if Parent Worker Process
1173 IF p_worker_id IS NULL THEN --{
1174
1175 -- Validating order_line_id
1176 IF g_order_line_id <> 0 THEN
1177
1178 OPEN get_line_info(g_order_line_id);
1179 FETCH get_line_info
1180 INTO l_order_header_id;
1181
1182 IF get_line_info%NOTFOUND THEN
1183
1184 x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1185 --
1186 IF l_debug_on THEN
1187 WSH_DEBUG_SV.logmsg(l_module_name, 'ORDER LINE ID ' || TO_CHAR (G_ORDER_LINE_ID)
1188 || 'DOES NOT EXIST' );
1189 WSH_DEBUG_SV.pop(l_module_name);
1190 END IF;
1191 --
1192 RETURN;
1193
1194 END IF;
1195
1196 IF l_order_header_id <> g_order_header_id THEN
1197
1198 x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1199 --
1200 IF l_debug_on THEN
1201 WSH_DEBUG_SV.logmsg(l_module_name, 'ORDER LINE ID ' || TO_CHAR (G_ORDER_LINE_ID)
1202 || 'DOES NOT BELONG TO');
1203 WSH_DEBUG_SV.logmsg(l_module_name, 'ORDER HEADER ID ' || TO_CHAR ( G_ORDER_HEADER_ID ));
1204 WSH_DEBUG_SV.pop(l_module_name);
1205 END IF;
1206 --
1207 RETURN;
1208 END IF;
1209
1210 IF get_line_info%ISOPEN THEN
1211 CLOSE get_line_info;
1212 END IF;
1213 END IF;
1214
1215 --
1216 --Bug 3433645 : OMFST:J: AUTOMATED SHIPPING FAILING FOR ORDER WITH HUGE DELIVERY LINES
1217 -- BULK UPDATE ALL THE LINES WITH THE SAME BATCH ID.
1218
1219 --1) Get all the container item in the batch.
1220 --2) For every container get the inner non container items
1221 -- Stamp the non container items with same batch id
1222 --3) If atleast one update then set g_del_detail_id = -1
1223 -- g_del_detail_id used for building the dynamic SQL
1224
1225 IF g_del_detail_id <> 0 THEN
1226
1227 --Cursor to get all the containers within the batch
1228 OPEN get_batch_details(p_batch_id, g_del_detail_id);
1229 LOOP
1230 FETCH get_batch_details into l_temp_line;
1231 EXIT WHEN get_batch_details%NOTFOUND;
1232
1233 -- Get lines within a container
1234 OPEN get_inner_items(l_temp_line);
1235 FETCH get_inner_items BULK COLLECT INTO g_det_lst;
1236 CLOSE get_inner_items;
1237
1238 IF (g_det_lst.COUNT > 0) THEN
1239 FORALL i IN g_det_lst.FIRST..g_det_lst.LAST
1240 UPDATE WSH_DELIVERY_DETAILS
1241 SET BATCH_ID = p_batch_id
1242 WHERE DELIVERY_DETAIL_ID = g_det_lst(i);
1243 IF (SQL%ROWCOUNT > 0) THEN
1244 g_del_detail_id := -1;
1245 END IF;
1246 END IF;
1247 END LOOP;
1248
1249 IF get_batch_details%ISOPEN THEN
1250 CLOSE get_batch_details;
1251 END IF;
1252 END IF;
1253
1254 -- Validating From_Subinventory and From_Locator
1255 IF (g_from_subinventory IS NOT NULL) THEN --{
1256
1257 IF l_debug_on THEN
1258 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_Validate.From_Subinventory',WSH_DEBUG_SV.C_PROC_LEVEL);
1259 END IF;
1260 l_validate_org := NULL;
1261 l_validate_sub := NULL;
1262 l_validate_locator := NULL;
1263 l_validate_item := NULL;
1264 l_validate_sub.secondary_inventory_name := g_from_subinventory;
1265 l_validate_org.organization_id := g_organization_id;
1266
1267 IF (INV_Validate.From_Subinventory( p_sub => l_validate_sub,
1268 p_org => l_validate_org,
1269 p_item => l_validate_item,
1270 p_acct_txn => 1
1271 ) = 0 ) THEN
1272
1273 x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1274 --
1275 IF l_debug_on THEN
1276 WSH_DEBUG_SV.logmsg(l_module_name, g_from_subinventory||' is not a Valid Subinventory for Org ID :'|| g_organization_id);
1277 WSH_DEBUG_SV.pop(l_module_name);
1278 END IF;
1279 --
1280 RETURN;
1281 --
1282 END IF;
1283
1284 IF (g_from_locator IS NOT NULL) THEN
1285 IF l_debug_on THEN
1286 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_Validate.From_Locator',WSH_DEBUG_SV.C_PROC_LEVEL);
1287 END IF;
1288 l_validate_locator.inventory_location_id := g_from_locator;
1289 IF (INV_Validate.From_Locator( p_locator => l_validate_locator,
1290 p_org => l_validate_org,
1291 p_item => l_validate_item,
1292 p_from_sub => l_validate_sub,
1293 p_project_id => g_project_id,
1294 p_task_id => g_task_id,
1295 p_txn_action_id => 0) = 0 ) THEN
1296 --
1297 x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1298 --
1299 IF l_debug_on THEN
1300 WSH_DEBUG_SV.logmsg(l_module_name, g_from_locator||' is not a Valid Locator for Subinventory :'|| g_from_subinventory);
1301 WSH_DEBUG_SV.pop(l_module_name);
1302 END IF;
1303 --
1304 RETURN;
1305 --
1306 END IF;
1307 END IF;
1308 END IF;
1309 --}
1310
1311 -- Validating To_Subinventory and To_Locator
1312 IF (g_to_subinventory IS NOT NULL) THEN --{
1313
1314 IF l_debug_on THEN
1315 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_Validate.To_Subinventory',WSH_DEBUG_SV.C_PROC_LEVEL);
1316 END IF;
1317 l_validate_org := NULL;
1318 l_validate_sub := NULL;
1319 l_validate_from_sub := NULL;
1320 l_validate_locator := NULL;
1321 l_validate_item := NULL;
1322 l_validate_sub.secondary_inventory_name := g_to_subinventory;
1323 l_validate_org.organization_id := g_organization_id;
1324 l_validate_from_sub.secondary_inventory_name := g_from_subinventory;
1325
1326 IF (INV_Validate.To_Subinventory( p_sub => l_validate_sub,
1327 p_org => l_validate_org,
1328 p_item => l_validate_item,
1329 p_from_sub => l_validate_from_sub,
1330 p_acct_txn => 1
1331 ) = 0 ) THEN
1332
1333 x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1334 --
1335 IF l_debug_on THEN
1336 WSH_DEBUG_SV.logmsg(l_module_name, g_to_subinventory||' is not a Valid Subinventory for Org ID :'|| g_organization_id);
1337 WSH_DEBUG_SV.pop(l_module_name);
1338 END IF;
1339 --
1340 RETURN;
1341 --
1342 END IF;
1343
1344 IF (g_to_locator IS NOT NULL) THEN
1345 IF l_debug_on THEN
1346 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_Validate.To_Locator',WSH_DEBUG_SV.C_PROC_LEVEL);
1347 END IF;
1348 l_validate_locator.inventory_location_id := g_to_locator;
1349 IF (INV_Validate.To_Locator( p_locator => l_validate_locator,
1350 p_org => l_validate_org,
1351 p_item => l_validate_item,
1352 p_to_sub => l_validate_sub,
1353 p_project_id => g_project_id,
1354 p_task_id => g_task_id,
1355 p_txn_action_id => 0) = 0 ) THEN
1356
1357 x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1358 --
1359 IF l_debug_on THEN
1360 WSH_DEBUG_SV.logmsg(l_module_name, g_to_locator||' is not a Valid Locator for Subinventory :'|| g_to_subinventory);
1361 WSH_DEBUG_SV.pop(l_module_name);
1362 END IF;
1363 --
1364 RETURN;
1365
1366 END IF;
1367 END IF;
1368 END IF; --}
1369
1370 -- Validating RelSubinventory
1371 IF (g_relsubinventory IS NOT NULL) THEN --{
1372 IF l_debug_on THEN
1373 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_Validate.From_Subinventory',WSH_DEBUG_SV.C_PROC_LEVEL);
1374 END IF;
1375 l_validate_org := NULL;
1376 l_validate_sub := NULL;
1377 l_validate_locator := NULL;
1378 l_validate_item := NULL;
1379 l_validate_sub.secondary_inventory_name := g_relsubinventory;
1380 l_validate_org.organization_id := g_organization_id;
1381
1382 IF (INV_Validate.From_Subinventory( p_sub => l_validate_sub,
1383 p_org => l_validate_org,
1384 p_item => l_validate_item,
1385 p_acct_txn => 1
1386 ) = 0 ) THEN
1387
1388 x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1389 --
1390 IF l_debug_on THEN
1391 WSH_DEBUG_SV.logmsg(l_module_name, g_relsubinventory||' is not a Valid Subinventory for Org ID :'|| g_organization_id);
1392 WSH_DEBUG_SV.pop(l_module_name);
1393 END IF;
1394 --
1395 RETURN;
1396
1397 END IF;
1398 END IF; --}
1399
1400 IF l_debug_on THEN
1401 WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATING REQUEST ID FOR BATCH' );
1402 END IF;
1403
1404 -- Update picking batch setting request id and other who parameters
1405 UPDATE WSH_PICKING_BATCHES
1406 SET REQUEST_ID = g_request_id,
1407 PROGRAM_APPLICATION_ID = g_application_id,
1408 PROGRAM_ID = g_program_id,
1409 PROGRAM_UPDATE_DATE = SYSDATE,
1410 LAST_UPDATED_BY = g_user_id,
1411 LAST_UPDATE_DATE = SYSDATE,
1412 LAST_UPDATE_LOGIN = g_login_id
1413 WHERE BATCH_ID = p_batch_id
1414 AND (REQUEST_ID IS NULL OR REQUEST_ID = g_request_id);
1415
1416 IF SQL%NOTFOUND THEN
1417 IF l_debug_on THEN
1418 WSH_DEBUG_SV.logmsg(l_module_name, 'PICKING BATCH ' || TO_CHAR(P_BATCH_ID) || ' NOT EXIST');
1419 WSH_DEBUG_SV.logmsg(l_module_name, 'OR ANOTHER REQUEST HAS ALREADY RELEASED THIS BATCH');
1420 END IF;
1421 END IF;
1422
1423 END IF; --}
1424
1425 g_initialized := TRUE;
1426 x_api_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1427
1428 IF l_debug_on THEN
1429 WSH_DEBUG_SV.pop(l_module_name);
1430 END IF;
1431
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434
1435 x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1436
1437 IF get_lock_batch%ISOPEN THEN
1438 CLOSE get_lock_batch;
1439 END IF;
1440 IF get_batch%ISOPEN THEN
1441 CLOSE get_batch;
1442 END IF;
1443 IF get_line_info%ISOPEN THEN
1444 CLOSE get_line_info;
1445 END IF;
1446 IF get_batch_details%ISOPEN THEN
1447 CLOSE get_batch_details;
1448 END IF;
1449 IF get_inner_items%ISOPEN THEN
1450 CLOSE get_inner_items;
1451 END IF;
1452
1453 IF l_debug_on THEN
1454 WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN WSH_PR_CRITERIA.INIT');
1455 WSH_DEBUG_SV.logmsg(l_module_name,'Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1456 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1457 END IF;
1458
1459 END Init;
1460
1461
1462 -- Start of comments
1463 -- API name : Init_Rules
1464 -- Type : Public
1465 -- Pre-reqs : None.
1466 -- Procedure: API to retrieves sequencing information based on sequence rule and
1467 -- group information based on grouping rule.
1468 -- Parameters :
1469 -- IN:
1470 -- p_pick_seq_rule_id IN pick sequence rule id.
1471 -- p_pick_grouping_rule_id IN pick grouping rule id.
1472 -- OUT:
1473 -- x_api_status OUT NOCOPY Standard to output api status.
1474 -- End of comments
1475 PROCEDURE Init_Rules (
1476 p_pick_seq_rule_id IN NUMBER,
1477 p_pick_grouping_rule_id IN NUMBER,
1478 x_api_status OUT NOCOPY VARCHAR2
1479 ) IS
1480 -- cursor to fetch pick sequence rule info
1481 CURSOR pick_seq_rule(v_psr_id IN NUMBER) IS
1482 SELECT NAME,
1483 NVL(ORDER_ID_PRIORITY, -1),
1484 DECODE(ORDER_ID_SORT, 'A', 'ASC', 'D', 'DESC', ''),
1485 NVL(INVOICE_VALUE_PRIORITY, -1),
1486 DECODE(INVOICE_VALUE_SORT, 'A', 'ASC', 'D', 'DESC', ''),
1487 NVL(SCHEDULE_DATE_PRIORITY, -1),
1488 DECODE(SCHEDULE_DATE_SORT, 'A', 'ASC', 'D', 'DESC', ''),
1489 NVL(SHIPMENT_PRI_PRIORITY, -1),
1490 DECODE(SHIPMENT_PRI_SORT, 'A', 'ASC', 'D', 'DESC', ''),
1491 NVL(TRIP_STOP_DATE_PRIORITY, -1),
1492 DECODE(TRIP_STOP_DATE_SORT, 'A', 'ASC', 'D', 'DESC', '')
1493 FROM WSH_PICK_SEQUENCE_RULES
1494 WHERE PICK_SEQUENCE_RULE_ID = v_psr_id
1495 AND SYSDATE BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE)) AND
1496 NVL(END_DATE_ACTIVE, TRUNC(SYSDATE)+1);
1497
1498 -- cursor to determine if pick slip rule contains order number and delivery
1499 CURSOR order_ps_group(v_pgr_id IN NUMBER) IS
1500 SELECT NVL(ORDER_NUMBER_FLAG,'N'), NVL(DELIVERY_FLAG,'N')
1501 FROM WSH_PICK_GROUPING_RULES
1502 WHERE PICK_GROUPING_RULE_ID = v_pgr_id
1503 AND SYSDATE BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE)) AND
1504 NVL(END_DATE_ACTIVE, TRUNC(SYSDATE)+1);
1505
1506 l_pick_seq_rule_name VARCHAR2(30);
1507 l_invoice_value_priority NUMBER;
1508 l_order_number_priority NUMBER;
1509 l_schedule_date_priority NUMBER;
1510 l_trip_stop_date_priority NUMBER;
1511 l_shipment_pri_priority NUMBER;
1512 l_invoice_value_sort VARCHAR2(4);
1513 l_order_number_sort VARCHAR2(4);
1514 l_schedule_date_sort VARCHAR2(4);
1515 l_trip_stop_date_sort VARCHAR2(4);
1516 l_shipment_pri_sort VARCHAR2(4);
1517 i NUMBER;
1518 j NUMBER;
1519 l_temp_psr psrTyp;
1520
1521 --
1522 l_debug_on BOOLEAN;
1523 --
1524 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INIT_RULES';
1525 --
1526 BEGIN
1527 --
1528 -- Debug Statements
1529 --
1530 --
1531 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1532 --
1533 IF l_debug_on IS NULL
1534 THEN
1535 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1536 END IF;
1537 --
1538 IF l_debug_on THEN
1539 WSH_DEBUG_SV.push(l_module_name);
1540 WSH_DEBUG_SV.log(l_module_name,'P_PICK_SEQ_RULE_ID',P_PICK_SEQ_RULE_ID);
1541 WSH_DEBUG_SV.log(l_module_name,'P_PICK_GROUPING_RULE_ID',P_PICK_GROUPING_RULE_ID);
1542 WSH_DEBUG_SV.logmsg(l_module_name, 'FETCHING PICK SEQUENCE RULE INFORMATION FOR THE BATCH');
1543 END IF;
1544 --
1545 -- fetch pick sequence rule parameters
1546 OPEN pick_seq_rule(p_pick_seq_rule_id);
1547 FETCH pick_seq_rule
1548 INTO l_pick_seq_rule_name,
1549 l_order_number_priority,
1550 l_order_number_sort,
1551 l_invoice_value_priority,
1552 l_invoice_value_sort,
1553 l_schedule_date_priority,
1554 l_schedule_date_sort,
1555 l_shipment_pri_priority,
1556 l_shipment_pri_sort,
1557 l_trip_stop_date_priority,
1558 l_trip_stop_date_sort;
1559
1560 -- handle pick sequence rule does not exist
1561 IF pick_seq_rule%NOTFOUND THEN
1562 --
1563 x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1564 --
1565 IF l_debug_on THEN
1566 WSH_DEBUG_SV.logmsg(l_module_name, 'PICK SEQUENCE RULE ID ' || TO_CHAR ( G_PICK_SEQ_RULE_ID ) || ' DOES NOT EXIST.');
1567 WSH_DEBUG_SV.pop(l_module_name);
1568 END IF;
1569 --
1570 RETURN;
1571 END IF;
1572 IF pick_seq_rule%ISOPEN THEN
1573 CLOSE pick_seq_rule;
1574 END IF;
1575
1576 -- initialize the pick sequence rule parameters
1577 i := 1;
1578 g_use_trip_stop_date := FALSE;
1579 IF (l_invoice_value_priority <> -1) THEN
1580 g_ordered_psr(i).attribute := C_INVOICE_VALUE;
1581 g_ordered_psr(i).attribute_name := 'INVOICE_VALUE';
1582
1583 -- initialize the invoice_value_flag to be used as part
1584 -- of building the select statement
1585 g_invoice_value_flag := 'Y';
1586 g_ordered_psr(i).priority := l_invoice_value_priority;
1587 g_ordered_psr(i).sort_order := l_invoice_value_sort;
1588 i := i + 1;
1589 END IF;
1590
1591 IF (l_order_number_priority <> -1) THEN
1592 g_ordered_psr(i).attribute := C_ORDER_NUMBER;
1593 g_ordered_psr(i).attribute_name := 'ORDER_NUMBER';
1594 g_ordered_psr(i).priority := l_order_number_priority;
1595 g_ordered_psr(i).sort_order := l_order_number_sort;
1596 i := i + 1;
1597 END IF;
1598 IF (l_schedule_date_priority <> -1) THEN
1599 g_ordered_psr(i).attribute := C_SCHEDULE_DATE;
1600 g_ordered_psr(i).attribute_name := 'SCHEDULE_DATE';
1601 g_ordered_psr(i).priority := l_schedule_date_priority;
1602 g_ordered_psr(i).sort_order := l_schedule_date_sort;
1603 i := i + 1;
1604 END IF;
1605
1606 IF (l_trip_stop_date_priority <> -1) THEN
1607 g_use_trip_stop_date := TRUE;
1608 g_ordered_psr(i).attribute := C_TRIP_STOP_DATE;
1609 g_ordered_psr(i).attribute_name := 'TRIP_STOP_DATE';
1610 g_ordered_psr(i).priority := l_trip_stop_date_priority;
1611 g_ordered_psr(i).sort_order := l_trip_stop_date_sort;
1612 i := i + 1;
1613 END IF;
1614
1615 IF (l_shipment_pri_priority <> -1) THEN
1616 g_ordered_psr(i).attribute := C_SHIPMENT_PRIORITY;
1617 g_ordered_psr(i).attribute_name := 'SHIPMENT_PRIORITY';
1618 g_ordered_psr(i).priority := l_shipment_pri_priority;
1619 g_ordered_psr(i).sort_order := l_shipment_pri_sort;
1620 i := i + 1;
1621 END IF;
1622 g_total_pick_criteria := i - 1;
1623
1624 -- sort the table for pick sequence rule according to priority
1625 FOR i IN 1..g_total_pick_criteria LOOP
1626 FOR j IN i+1..g_total_pick_criteria LOOP
1627 IF (g_ordered_psr(j).priority < g_ordered_psr(i).priority) THEN
1628 l_temp_psr := g_ordered_psr(j);
1629 g_ordered_psr(j) := g_ordered_psr(i);
1630 g_ordered_psr(i) := l_temp_psr;
1631 END IF;
1632 END LOOP;
1633 END LOOP;
1634
1635 -- determine the most significant pick sequence rule attribute
1636 g_primary_psr := g_ordered_psr(1).attribute_name;
1637
1638 IF l_debug_on THEN
1639 WSH_DEBUG_SV.logmsg(l_module_name, 'PRIMARY PICK RULE IS ' || G_PRIMARY_PSR );
1640 END IF;
1641 --
1642
1643 -- print pick sequence rule information for debugging purposes
1644 FOR i IN 1..g_total_pick_criteria LOOP
1645 --
1646 IF l_debug_on THEN
1647 WSH_DEBUG_SV.logmsg(l_module_name, 'ATTRIBUTE = ' || G_ORDERED_PSR ( I ) .ATTRIBUTE_NAME || ' ' || 'PRIORITY = ' || TO_CHAR ( G_ORDERED_PSR ( I ) .PRIORITY ) || ' ' || 'SORT = ' || G_ORDERED_PSR ( I ) .SORT_ORDER );
1648 END IF;
1649 --
1650 END LOOP;
1651 --
1652 IF l_debug_on THEN
1653 WSH_DEBUG_SV.logmsg(l_module_name, 'DETERMINING IF ORDER NUMBER IS IN GROUPING RULE...');
1654 END IF;
1655
1656 --Determine if order number is in grouping rule.
1657 OPEN order_ps_group(p_pick_grouping_rule_id);
1658 FETCH order_ps_group INTO g_use_order_ps, g_use_delivery_ps;
1659 IF order_ps_group%NOTFOUND THEN
1660 g_use_order_ps := 'N';
1661 g_use_delivery_ps := 'N';
1662 END IF;
1663 IF order_ps_group%ISOPEN THEN
1664 CLOSE order_ps_group;
1665 END IF;
1666
1667 x_api_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1668 --
1669 IF l_debug_on THEN
1670 WSH_DEBUG_SV.log(l_module_name, 'g_use_order_ps ' , g_use_order_ps );
1671 WSH_DEBUG_SV.log(l_module_name, 'g_use_delivery_ps ' , g_use_delivery_ps );
1672 WSH_DEBUG_SV.pop(l_module_name);
1673 END IF;
1674 --
1675 EXCEPTION
1676 --
1677 WHEN OTHERS THEN
1678 --
1679 x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1680 --
1681 IF pick_seq_rule%ISOPEN THEN
1682 CLOSE pick_seq_rule;
1683 END IF;
1684 IF order_ps_group%ISOPEN THEN
1685 CLOSE order_ps_group;
1686 END IF;
1687 --
1688 IF l_debug_on THEN
1689 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1690 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1691 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1692 END IF;
1693 --
1694 END Init_Rules;
1695
1696
1697 -- Start of comments
1698 -- API name : Get_Worker_Records
1699 -- Type : Public
1700 -- Pre-reqs : None.
1701 -- Procedure: API to get worker records for a specific batch_id and organization_id combination
1702 -- based on the mode (PICK-SS / PICK)
1703 -- Parameters :
1704 -- IN:
1705 -- p_mode IN Mode (Valid Values : PICK-SS and PICK)
1706 -- p_batch_id IN batch to be processed
1707 -- p_organization_id IN Organization to be processed
1708 -- OUT:
1709 -- x_api_status OUT NOCOPY Standard to output api status.
1710 -- End of comments
1711 PROCEDURE Get_Worker_Records (
1712 p_mode IN VARCHAR2,
1713 p_batch_id IN NUMBER,
1714 p_organization_id IN NUMBER,
1715 x_api_status OUT NOCOPY VARCHAR2
1716 ) IS
1717
1718 l_debug_on BOOLEAN;
1719 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_WORKER_RECORDS';
1720 --
1721 l_query VARCHAR2(4000);
1722
1723 BEGIN
1724 --
1725 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1726 --
1727 IF l_debug_on IS NULL THEN
1728 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1729 END IF;
1730 --
1731 IF l_debug_on THEN
1732 WSH_DEBUG_SV.push(l_module_name);
1733 WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
1734 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
1735 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
1736 END IF;
1737
1738 x_api_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1739
1740 IF c_work_cursorID%ISOPEN THEN
1741 CLOSE c_work_cursorID;
1742 END IF;
1743
1744 OPEN c_work_cursorID(p_batch_id, p_organization_id, p_mode);
1745
1746 IF l_debug_on THEN
1747 WSH_DEBUG_SV.logmsg(l_module_name, 'Status '||x_api_status);
1748 WSH_DEBUG_SV.pop(l_module_name);
1749 END IF;
1750
1751 EXCEPTION
1752 --
1753 WHEN OTHERS THEN
1754 --
1755 x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1756 --
1757 IF c_work_cursorID%ISOPEN THEN
1758 CLOSE c_work_cursorID;
1759 END IF;
1760 --
1761 IF l_debug_on THEN
1762 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1763 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1764 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1765 END IF;
1766 --
1767 END Get_Worker_Records;
1768
1769
1770 -- Start of comments
1771 -- API name : Init_Cursor
1772 -- Type : Public
1773 -- Pre-reqs : None.
1774 -- Procedure: API to creates a dynamic SQL statement for delivery lines based on release criteria .
1775 --
1776 -- Parameters :
1777 -- IN:
1778 -- p_organization_id IN Organization id.
1779 -- p_mode IN Mode, valid value SUMMARY/WORKER.
1780 -- p_wms_org IN Is Organization WMS enabled, valid value Y/N.
1781 -- p_mo_header_id IN Move Order Header id.
1782 -- p_inv_item_id IN Inventory Item id.
1783 -- p_enforce_ship_set_and_smc IN Whether to enforce Ship Set and SMC validate value Y/N.
1784 -- p_print_flag IN If need to print the value in log file.
1785 -- p_express_pick IN If express pick, valid value Y/N.
1786 -- p_batch_id IN Batch to be processed.
1787 -- OUT:
1788 -- x_worker_count OUT NOCOPY Worker Records Count.
1789 -- x_smc_worker_count OUT NOCOPY SMC Worker Records Count.
1790 -- x_dd_count OUT NOCOPY Delivery Details Records Count.
1791 -- x_api_status OUT NOCOPY Standard to output api status.
1792 -- End of comments
1793
1794 Procedure Init_Cursor (
1795 p_organization_id IN NUMBER,
1796 p_mode IN VARCHAR2,
1797 p_wms_org IN VARCHAR2,
1798 p_mo_header_id IN NUMBER,
1799 p_inv_item_id IN NUMBER,
1800 p_enforce_ship_set_and_smc IN VARCHAR2,
1801 p_print_flag IN VARCHAR2,
1802 p_express_pick IN VARCHAR2,
1803 p_batch_id IN NUMBER,
1804 x_worker_count OUT NOCOPY NUMBER,
1805 x_smc_worker_count OUT NOCOPY NUMBER,
1806 x_dd_count OUT NOCOPY NUMBER,
1807 x_api_status OUT NOCOPY VARCHAR2
1808 ) IS
1809 --
1810 i NUMBER;
1811 l_ont_source_code VARCHAR2(240);
1812
1813 l_count NUMBER;
1814 --
1815 l_debug_on BOOLEAN;
1816 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INIT_CURSOR';
1817 --
1818 BEGIN
1819 --
1820 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1821 --
1822 IF l_debug_on IS NULL THEN
1823 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1824 END IF;
1825 --
1826 IF l_debug_on THEN
1827 --
1828 WSH_DEBUG_SV.push(l_module_name);
1829 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
1830 WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
1831 WSH_DEBUG_SV.log(l_module_name,'P_WMS_ORG',P_WMS_ORG);
1832 WSH_DEBUG_SV.log(l_module_name,'P_MO_HEADER_ID',P_MO_HEADER_ID);
1833 WSH_DEBUG_SV.log(l_module_name,'P_INV_ITEM_ID',P_INV_ITEM_ID);
1834 WSH_DEBUG_SV.log(l_module_name,'P_ENFORCE_SHIP_SET_AND_SMC',P_ENFORCE_SHIP_SET_AND_SMC);
1835 WSH_DEBUG_SV.log(l_module_name,'P_PRINT_FLAG',P_PRINT_FLAG);
1836 WSH_DEBUG_SV.log(l_module_name,'P_EXPRESS_PICK',P_EXPRESS_PICK);
1837 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
1838 --
1839 END IF;
1840 --
1841 --bug# 6719369(replenishment project) : need to consider the replenishment status value.
1842 IF g_backorders_flag NOT IN ('E','I','O','M') THEN
1843 --
1844 x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1845 --
1846 IF l_debug_on THEN
1847 WSH_DEBUG_SV.logmsg(l_module_name, 'INVALID BACKORDER MODE.');
1848 WSH_DEBUG_SV.pop(l_module_name);
1849 END IF;
1850 --
1851 RETURN;
1852 --
1853 END IF;
1854
1855 --
1856 -- Make sure the first_line record has header_id set to -1
1857 -- This is used to determine whether the Get_Lines
1858 -- is called for the first time or not. The first_line is set
1859 -- as a dummy line
1860 first_line.source_header_id := -1;
1861 g_del_current_line := 1;
1862
1863 -- Selection for unreleased lines, build the dynamic sql based on release batch parameters.
1864 g_Unreleased_SQL := '';
1865
1866 -- 10. Based on P_MODE parameter, the query columns are selected.
1867 --{
1868 IF p_mode = 'SUMMARY' THEN
1869 IF p_enforce_ship_set_and_smc = 'N' THEN
1870 Process_Buffer(p_print_flag, 'u', 'SELECT WDD.ORGANIZATION_ID, WDD.INVENTORY_ITEM_ID, COUNT(*) ');
1871 ELSE
1872 Process_Buffer(p_print_flag, 'u', 'SELECT WDD.ORGANIZATION_ID,
1873 DECODE(WDD.SHIP_SET_ID,NULL,DECODE(WDD.SHIP_MODEL_COMPLETE_FLAG,''Y'',NULL,WDD.INVENTORY_ITEM_ID),NULL)
1874 ITEM1, COUNT(*) ');
1875 END IF;
1876
1877 ELSE --{
1878
1879 Process_Buffer(p_print_flag,
1880 'u', 'SELECT DISTINCT WDD.SOURCE_LINE_ID,');
1881 Process_Buffer(p_print_flag,
1882 'u', ' WDD.SOURCE_HEADER_ID,');
1883 Process_Buffer(p_print_flag,
1884 'u', ' WDD.ORGANIZATION_ID,');
1885 Process_Buffer(p_print_flag,
1886 'u', ' WDD.INVENTORY_ITEM_ID,');
1887 Process_Buffer(p_print_flag,
1888 'u', ' WDD.MOVE_ORDER_LINE_ID,');
1889 Process_Buffer(p_print_flag,
1890 'u', ' WDD.DELIVERY_DETAIL_ID,' );
1891 Process_Buffer(p_print_flag,
1892 'u', ' WDD.SHIP_MODEL_COMPLETE_FLAG,');
1893 Process_Buffer(p_print_flag,
1894 'u', ' WDD.TOP_MODEL_LINE_ID,');
1895 Process_Buffer(p_print_flag,
1896 'u', ' WDD.SHIP_FROM_LOCATION_ID,');
1897 Process_Buffer(p_print_flag,
1898 'u', ' NULL SHIP_METHOD_CODE,');
1899 Process_Buffer(p_print_flag,
1900 'u', ' WDD.SHIPMENT_PRIORITY_CODE,');
1901 Process_Buffer(p_print_flag,
1902 'u', ' WDD.DATE_SCHEDULED DATE_SCHEDULED,');
1903 Process_Buffer(p_print_flag,
1904 'u', ' WDD.REQUESTED_QUANTITY,');
1905 Process_Buffer(p_print_flag,
1906 'u', ' WDD.REQUESTED_QUANTITY_UOM,');
1907 Process_Buffer(p_print_flag,
1908 'u', ' WDD.PREFERRED_GRADE,' );
1909 Process_Buffer(p_print_flag,
1910 'u', ' WDD.REQUESTED_QUANTITY2,' );
1911 Process_Buffer(p_print_flag,
1912 'u', ' WDD.REQUESTED_QUANTITY_UOM2,' );
1913 Process_Buffer(p_print_flag,
1914 'u', ' WDD.PROJECT_ID,' );
1915 Process_Buffer(p_print_flag,
1916 'u', ' WDD.TASK_ID,' );
1917 Process_Buffer(p_print_flag,
1918 'u', ' WDD.SUBINVENTORY FROM_SUBINVENTORY_CODE,' );
1919 Process_Buffer(p_print_flag,
1920 'u', ' WDD.SUBINVENTORY TO_SUBINVENTORY_CODE,' );
1921 Process_Buffer(p_print_flag,
1922 'u', ' WDD.RELEASED_STATUS RELEASED_STATUS,' );
1923 Process_Buffer(p_print_flag,
1924 'u', ' WDD.SHIP_SET_ID SHIP_SET_ID,' );
1925 Process_Buffer(p_print_flag,
1926 'u', ' WDD.SOURCE_CODE SOURCE_CODE,' );
1927 Process_Buffer(p_print_flag,
1928 'u', ' WDD.SOURCE_HEADER_NUMBER SOURCE_HEADER_NUMBER,' );
1929
1930 -- Bug 2040002: Need PLANNED_DEPARTURE_DATE only if released by trip or
1931 -- is part of release sequence rule
1932 IF ((g_use_trip_stop_date) OR (WSH_PR_CRITERIA.g_trip_id > 0)) THEN
1933 Process_Buffer(p_print_flag,
1934 'u', ' WTS.PLANNED_DEPARTURE_DATE,');
1935 ELSE
1936 Process_Buffer(p_print_flag,
1937 'u', ' NULL PLANNED_DEPARTURE_DATE,');
1938 END IF;
1939
1940 Process_Buffer(p_print_flag,
1941 'u', ' WDA.DELIVERY_ID,');
1942 Process_Buffer(p_print_flag,
1943 'u', ' OL.END_ITEM_UNIT_NUMBER,');
1944 Process_Buffer(p_print_flag,
1945 'u', ' OL.SOURCE_DOCUMENT_TYPE_ID,');
1946 Process_Buffer(p_print_flag,
1947 'u', ' MSI.RESERVABLE_TYPE,');
1948
1949 Process_Buffer(p_print_flag, 'u', ' WDD.LAST_UPDATE_DATE,'); --Bug# 3248578
1950
1951 --HVOP
1952 IF (g_existing_rsvs_only_flag = 'Y') THEN
1953 Process_Buffer(p_print_flag,
1954 'u', ' MR.DEMAND_SOURCE_HEADER_ID,');
1955 ELSE
1956 Process_Buffer(p_print_flag,
1957 'u', ' -1 DEMAND_SOURCE_HEADER_ID,');
1958 END IF;
1959
1960 IF g_invoice_value_flag = 'Y' THEN
1961 Process_Buffer(p_print_flag,
1962 'u', ' WSH_PICK_CUSTOM.OUTSTANDING_ORDER_VALUE(WDD.SOURCE_HEADER_ID) OUTSTANDING_ORDER_VALUE,');
1963 ELSE
1964 Process_Buffer(p_print_flag,
1965 'u', ' -1 OUTSTANDING_ORDER_VALUE,');
1966 END IF;
1967
1968 -- anxsharm, X-dock, add customer_id
1969 Process_Buffer(p_print_flag, 'u', ' WDD.CUSTOMER_ID');
1970 -- anxsharm, end of code for X-dock
1971
1972 --}
1973 END IF;
1974 --}
1975
1976 -- 20. Common Tables to be selected irrespective of P_MODE parameter
1977 --{
1978 Process_Buffer(p_print_flag,
1979 'u', ' FROM WSH_DELIVERY_DETAILS WDD,');
1980 Process_Buffer(p_print_flag,
1981 'u', ' wsh_delivery_assignments_v WDA,');
1982
1983 -- Bug 2040002: Need WSH_NEW_DELIVERIES under following conditions only
1984 IF ((g_ship_method_code IS NOT NULL) OR
1985 (g_trip_id > 0) OR
1986 (g_use_trip_stop_date) OR
1987 (g_delivery_id > 0)) THEN
1988 Process_Buffer(p_print_flag,
1989 'u', ' WSH_NEW_DELIVERIES WDE,');
1990 END IF;
1991
1992 -- Bug 2040002: Need Trip/Stop info only if released by trip/stop or if
1993 -- Departure Date is part of release sequence rule
1994 IF ((g_use_trip_stop_date) OR (WSH_PR_CRITERIA.g_trip_id > 0)) THEN
1995 Process_Buffer(p_print_flag,
1996 'u', ' WSH_DELIVERY_LEGS WLG,');
1997 Process_Buffer(p_print_flag,
1998 'u', ' WSH_TRIP_STOPS WTS,');
1999 END IF;
2000
2001 IF (g_existing_rsvs_only_flag = 'Y') THEN
2002 /* NC Prior Reservations for OPM Bug#2768102 */
2003 /* Added if condition */
2004 -- HW OPMCONV - Removed checking for process flag
2005 Process_Buffer(p_print_flag,
2006 'u', ' MTL_SALES_ORDERS MSORD1, ');
2007 Process_Buffer(p_print_flag,
2008 'u', ' MTL_RESERVATIONS MR, ');
2009 Process_Buffer(p_print_flag,
2010 'u', ' OE_TRANSACTION_TYPES_TL OTTT, '); --bug 6082122: Added table OE_TRANSACTION_TYPES_TL
2011 END IF;
2012
2013 Process_Buffer(p_print_flag,
2014 'u', ' OE_ORDER_LINES_ALL OL, ');
2015 Process_Buffer(p_print_flag,
2016 'u', ' MTL_SYSTEM_ITEMS MSI ');
2017 /* rlanka : Pack J Enhancement
2018 * Need mtl_category_sets and mtl_category_set_valid_cats only *
2019 * if pick releasing by category *
2020 */
2021 IF g_CategorySetID <> 0 AND g_CategoryID <> 0 THEN
2022 Process_Buffer(p_print_flag, 'u', ' , MTL_CATEGORY_SETS MCS, MTL_ITEM_CATEGORIES MIC ');
2023 END IF;
2024 --
2025 Process_Buffer(p_print_flag,
2026 'u', ' WHERE WDD.DATE_SCHEDULED IS NOT NULL');
2027
2028 -- Bug 2040002: Can do hard join only when released by trip
2029 -- Do outer join when Departure Date is part of release sequence rule
2030 -- Otherwise we don't need to access these tables
2031 IF (WSH_PR_CRITERIA.g_trip_id > 0) THEN
2032 Process_Buffer(p_print_flag,
2033 'u', ' AND WTS.STOP_ID = WLG.PICK_UP_STOP_ID ');
2034 Process_Buffer(p_print_flag,
2035 'u', ' AND WLG.DELIVERY_ID = WDE.DELIVERY_ID ');
2036 ELSIF (g_use_trip_stop_date) THEN
2037 Process_Buffer(p_print_flag,
2038 'u', ' AND WTS.STOP_ID(+) = WLG.PICK_UP_STOP_ID ');
2039 Process_Buffer(p_print_flag,
2040 'u', ' AND WLG.DELIVERY_ID(+) = WDE.DELIVERY_ID ');
2041 END IF;
2042
2043 -- Bug 2040002:
2044 IF ((g_delivery_id > 0) OR (g_trip_id > 0)) THEN
2045 Process_Buffer(p_print_flag,
2046 'u', ' AND WDE.DELIVERY_ID = WDA.DELIVERY_ID ');
2047 ELSIF ((g_ship_method_code IS NOT NULL) OR (g_use_trip_stop_date)) THEN
2048 Process_Buffer(p_print_flag,
2049 'u', ' AND WDE.DELIVERY_ID(+) = WDA.DELIVERY_ID ');
2050 END IF;
2051 Process_Buffer(p_print_flag,
2052 'u', ' AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID');
2053 Process_Buffer(p_print_flag,
2054 'u', ' AND NVL(WDD.REQUESTED_QUANTITY,0) > 0');
2055 Process_Buffer(p_print_flag,
2056 'u', ' AND WDD.SOURCE_LINE_ID = OL.LINE_ID');
2057 Process_Buffer(p_print_flag,
2058 'u', ' AND WDD.SOURCE_CODE = ''OE'' ');
2059 Process_Buffer(p_print_flag,
2060 'u', ' AND WDD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID ');
2061 Process_Buffer(p_print_flag,
2062 'u', ' AND WDD.ORGANIZATION_ID = MSI.ORGANIZATION_ID ');
2063
2064 -- Both unreleased and backordered SQL share common conditions
2065 IF p_print_flag = 'Y' THEN
2066 --
2067 IF l_debug_on THEN
2068 WSH_DEBUG_SV.logmsg(l_module_name, ' <COMMON CONDITIONS>');
2069 END IF;
2070 --
2071 END IF;
2072
2073 --Construct the where clause
2074 g_cond_SQL := '';
2075
2076 --bug# 6689448 (replenishment project) :(begin) need to consider the replenishment status value.
2077 IF ( g_backorders_flag = 'I' ) THEN
2078 Process_Buffer(p_print_flag,'c', ' AND WDD.RELEASED_STATUS IN ( ''R'',''B'',''X'') AND NVL(WDD.REPLENISHMENT_STATUS,''C'') = ''C'' ');
2079 ELSIF ( g_backorders_flag = 'E' ) THEN
2080 Process_Buffer(p_print_flag,'c', ' AND WDD.RELEASED_STATUS IN ( ''R'',''X'') AND WDD.REPLENISHMENT_STATUS IS NULL');
2081 ELSIF ( g_backorders_flag = 'O' ) THEN
2082 Process_Buffer(p_print_flag,'c', ' AND WDD.RELEASED_STATUS IN ( ''B'',''X'') AND WDD.REPLENISHMENT_STATUS IS NULL');
2083 ELSIF ( g_backorders_flag = 'M' ) THEN
2084 Process_Buffer(p_print_flag,'c', ' AND WDD.RELEASED_STATUS IN ( ''R'',''B'') AND WDD.REPLENISHMENT_STATUS = ''C'' ');
2085 END IF;
2086 --bug# 6689448 (replenishment project) : (end)
2087
2088 --Bug 3433645 : OMFST:J: AUTOMATED SHIPPING FAILING FOR ORDER WITH HUGE DELIVERY LINES
2089 --g_del_detail_id is set to -1 if batch id for an item in a container is chagned.
2090
2091 IF (g_del_detail_id = -1 ) THEN
2092
2093 Process_Buffer(p_print_flag,'c',' AND WDA.DELIVERY_DETAIL_ID IN '
2094 ||'( SELECT DELIVERY_DETAIL_ID FROM '
2095 ||' WSH_DELIVERY_DETAILS WHERE BATCH_ID = :X_batch_id '
2096 ||' AND CONTAINER_FLAG = ''N'')'); -- ,p_batch_id);
2097
2098 ELSIF (g_del_detail_id <> 0) THEN
2099 Process_Buffer(p_print_flag,'c', ' AND WDA.DELIVERY_DETAIL_ID = :X_del_detail_id'); -- ,g_del_detail_id);
2100 END IF;
2101 --End of Fix for Bug 3433645
2102
2103 IF (g_order_header_id <> 0 AND g_order_line_id = 0) THEN
2104 Process_Buffer(p_print_flag,'c', ' AND WDD.SOURCE_HEADER_ID = :X_header_id '); -- ,g_order_header_id);
2105 ELSIF (g_order_header_id <> 0 AND g_order_line_id <> 0) THEN
2106 Process_Buffer(p_print_flag,'c',' AND WDD.SOURCE_HEADER_ID + 0 = :X_header_id'); -- ,g_order_header_id);
2107 END IF;
2108
2109 IF (g_order_line_id <> 0) THEN
2110 Process_Buffer(p_print_flag,'c', ' AND WDD.SOURCE_LINE_ID = :X_order_line_id'); -- ,g_order_line_id);
2111 END IF;
2112
2113 IF (g_customer_id <> 0) THEN
2114 Process_Buffer(p_print_flag,'c', ' AND WDD.CUSTOMER_ID = :X_customer_id'); -- ,g_customer_id);
2115 END IF;
2116
2117 IF (g_ship_from_loc_id <> -1) THEN
2118 Process_Buffer(p_print_flag,'c', ' AND WDD.SHIP_FROM_LOCATION_ID = :X_ship_from_loc_id'); -- ,g_ship_from_loc_id);
2119 END IF;
2120
2121 IF (g_ship_to_loc_id <> 0) THEN
2122 Process_Buffer(p_print_flag,'c', ' AND WDD.SHIP_TO_LOCATION_ID = :X_ship_to_loc_id'); -- ,g_ship_to_loc_id);
2123 END IF;
2124
2125 IF (g_order_type_id <> 0) THEN
2126 Process_Buffer(p_print_flag,'c', ' AND WDD.SOURCE_HEADER_TYPE_ID = :X_order_type_id'); -- ,g_order_type_id);
2127 END IF;
2128
2129 IF (g_ship_set_number <> 0) THEN
2130 Process_Buffer(p_print_flag,'c', ' AND WDD.SHIP_SET_ID = :X_ship_set_id'); -- ,g_ship_set_number);
2131 END IF;
2132
2133 IF (g_task_id <> 0) THEN
2134 Process_Buffer(p_print_flag,'c', ' AND WDD.TASK_ID = :X_task_id'); -- ,g_task_id);
2135 END IF;
2136
2137 IF (g_project_id <> 0) THEN
2138 Process_Buffer(p_print_flag,'c', ' AND WDD.PROJECT_ID = :X_project_id'); -- ,g_project_id);
2139 END IF;
2140
2141 IF (p_organization_id IS NOT NULL) THEN
2142 Process_Buffer(p_print_flag,'c', ' AND WDD.ORGANIZATION_ID = :X_org_id',p_organization_id);
2143 END IF;
2144
2145 -- Bug 2040002: Flipped WDE and WDD. Not related to this bug
2146 IF (g_ship_method_code IS NOT NULL) THEN
2147 Process_Buffer(p_print_flag,'c', ' AND NVL(WDE.SHIP_METHOD_CODE,WDD.SHIP_METHOD_CODE) = :X_ship_method_code '); -- ,g_ship_method_code);
2148 END IF;
2149
2150 IF (g_shipment_priority IS NOT NULL) THEN
2151 Process_Buffer(p_print_flag,'c', ' AND WDD.SHIPMENT_PRIORITY_CODE = :X_shipment_priority '); -- ,g_shipment_priority);
2152 END IF;
2153
2154 IF (g_from_request_date IS NOT NULL) THEN
2155 Process_Buffer(p_print_flag,'c',
2156 ' AND TO_CHAR(WDD.DATE_REQUESTED, ''RRRR/MM/DD HH24:MI:SS'') >= :X_from_request_date');
2157 END IF;
2158
2159 IF (g_to_request_date IS NOT NULL) THEN
2160 Process_Buffer(p_print_flag,'c',
2161 ' AND TO_CHAR(WDD.DATE_REQUESTED, ''RRRR/MM/DD HH24:MI:SS'') <= :X_to_request_date');
2162 END IF;
2163
2164 IF (g_from_sched_ship_date IS NOT NULL) THEN
2165 Process_Buffer(p_print_flag,'c',
2166 ' AND TO_CHAR(WDD.DATE_SCHEDULED, ''RRRR/MM/DD HH24:MI:SS'') >= :X_from_sched_ship_date');
2167 END IF;
2168
2169 IF (g_to_sched_ship_date IS NOT NULL) THEN
2170 Process_Buffer(p_print_flag,'c',
2171 ' AND TO_CHAR(WDD.DATE_SCHEDULED, ''RRRR/MM/DD HH24:MI:SS'') <= :X_to_sched_ship_date');
2172 END IF;
2173 --}
2174
2175 -- 30. For P_MODE as WORKER, the input parameter p_inv_item_id is considered as a query criteria
2176 --{
2177 IF p_mode = 'WORKER' THEN
2178 IF p_inv_item_id IS NOT NULL THEN
2179 Process_Buffer(p_print_flag,'c', ' AND WDD.INVENTORY_ITEM_ID = :X_inventory_item_id '); -- ,g_inventory_item_id);
2180 END IF;
2181 ELSE
2182 IF (g_inventory_item_id <> 0) THEN
2183 Process_Buffer(p_print_flag,'c', ' AND WDD.INVENTORY_ITEM_ID = :X_inventory_item_id '); -- ,g_inventory_item_id);
2184 END IF;
2185 END IF;
2186 --}
2187
2188 -- 40. Common Query Criteria
2189 --{
2190 Process_Buffer(p_print_flag,'c', ' AND (WDA.DELIVERY_ID IS NULL');
2191
2192 IF (g_include_planned_lines <> 'N') THEN
2193 Process_Buffer(p_print_flag,'c', ' OR :X_include_planned_lines <> ''N'' '); -- ,g_include_planned_lines);
2194 END IF;
2195
2196 IF (g_trip_id <> 0) THEN
2197 Process_Buffer(p_print_flag,'c', ' OR :X_trip_id <> 0'); -- ,g_trip_id );
2198 END IF;
2199
2200 IF (g_delivery_id <> 0) THEN
2201 Process_Buffer(p_print_flag,'c', ' OR :X_delivery_id <> 0'); -- ,g_delivery_id );
2202 END IF;
2203
2204 Process_Buffer(p_print_flag,'c', ' )');
2205
2206 Process_Buffer(p_print_flag,
2207 'c', ' AND (NVL(WDD.DEP_PLAN_REQUIRED_FLAG,''N'') = ''N''');
2208 Process_Buffer(p_print_flag,
2209 'c', ' OR (NVL(WDD.DEP_PLAN_REQUIRED_FLAG,''N'') = ''Y''');
2210 IF ((g_ship_method_code IS NOT NULL) OR
2211 (g_trip_id > 0) OR
2212 (g_use_trip_stop_date) OR
2213 (g_delivery_id > 0)) THEN
2214 Process_Buffer(p_print_flag,
2215 'c', ' AND NVL(WDE.PLANNED_FLAG,''N'') IN (''Y'',''F'')))');
2216 ELSE
2217 Process_Buffer(p_print_flag,
2218 'c', ' AND EXISTS ( SELECT 1 FROM WSH_NEW_DELIVERIES WDE1
2219 WHERE WDE1.DELIVERY_ID = WDA.DELIVERY_ID
2220 AND ( NVL(WDE1.PLANNED_FLAG,''N'') IN (''Y'',''F'' ) )) ))');
2221 END IF;
2222
2223 -- Handling trips and deliveries
2224 IF ( g_delivery_id <> 0) THEN
2225 Process_Buffer(p_print_flag,'c', ' AND WDE.DELIVERY_ID = :X_delivery_id'); -- ,g_delivery_id);
2226 END IF;
2227
2228 IF (g_trip_id <> 0) THEN
2229 Process_Buffer(p_print_flag,'c', ' AND WTS.TRIP_ID = :X_trip_id '); -- ,g_trip_id);
2230 IF (g_trip_stop_id <> 0) THEN
2231 Process_Buffer(p_print_flag,'c', ' AND WTS.STOP_ID = :X_trip_stop_id'); -- ,g_trip_stop_id);
2232 END IF;
2233 END IF;
2234
2235 IF (p_express_pick = 'Y' AND p_enforce_ship_set_and_smc = 'Y') THEN
2236 Process_Buffer(p_print_flag,'c', ' AND NOT(WDD.SHIP_SET_ID is null AND WDD.SHIP_MODEL_COMPLETE_FLAG = ''Y'' AND WDD.TOP_MODEL_LINE_ID is not null)');
2237
2238 END IF;
2239
2240 --If only prior reservation exists
2241 IF (g_existing_rsvs_only_flag = 'Y') THEN
2242
2243 l_ont_source_code := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
2244 --
2245 -- Debug Statements
2246 --
2247 IF l_debug_on THEN
2248 WSH_DEBUG_SV.logmsg(l_module_name, 'OM: SOURCE CODE IS '|| L_ONT_SOURCE_CODE );
2249 END IF;
2250 --
2251 Process_Buffer(p_print_flag,'c', ' AND MSORD1.SEGMENT1 = WDD.SOURCE_HEADER_NUMBER');
2252 --bug 6082122: Comparing MSORD1.SEGMENT2 with the order type name for the base language
2253 Process_Buffer(p_print_flag,'c', ' AND MSORD1.SEGMENT2 = OTTT.NAME');
2254 Process_Buffer(p_print_flag,'c', ' AND OTTT.TRANSACTION_TYPE_ID = WDD.SOURCE_HEADER_TYPE_ID');
2255 Process_Buffer(p_print_flag,'c', ' AND OTTT.LANGUAGE = (SELECT language_code FROM fnd_languages WHERE installed_flag = ''B'') ');
2256 IF (l_ont_source_code is NOT NULL) THEN
2257 Process_Buffer(p_print_flag,'c', ' AND MSORD1.SEGMENT3 = '''|| l_ont_source_code ||'''');
2258 END IF;
2259 Process_Buffer(p_print_flag,'c', ' AND MR.DEMAND_SOURCE_HEADER_ID = MSORD1.SALES_ORDER_ID');
2260 Process_Buffer(p_print_flag,'c', ' AND MR.DEMAND_SOURCE_LINE_ID = WDD.SOURCE_LINE_ID');
2261 Process_Buffer(p_print_flag,'c', ' AND NVL(MR.SHIP_READY_FLAG, 2) = 2');
2262 Process_Buffer(p_print_flag,'c', ' AND MR.PRIMARY_RESERVATION_QUANTITY - NVL(MR.DETAILED_QUANTITY,0) > 0');
2263
2264 -- Bug 3157902
2265 -- rlanka : Process_Buffer accepts only 1 bind variable
2266 --
2267 IF (g_from_subinventory IS NOT NULL) THEN
2268 Process_Buffer(p_print_flag,'c',
2269 ' AND ((MR.SUBINVENTORY_CODE IS NOT NULL ' ||
2270 ' AND MR.SUBINVENTORY_CODE = :X_subinventory_code)' ||
2271 ' OR MR.SUBINVENTORY_CODE IS NULL)'); -- ,
2272 -- g_from_subinventory);
2273 END IF;
2274 --
2275 IF (g_from_locator IS NOT NULL) THEN
2276 Process_Buffer(p_print_flag,'c',
2277 ' AND ((MR.LOCATOR_ID IS NOT NULL ' ||
2278 ' AND MR.LOCATOR_ID = :X_locator_id)' ||
2279 ' OR MR.LOCATOR_ID IS NULL)'); -- ,
2280 -- g_from_locator);
2281 END IF;
2282 --
2283 END IF;
2284
2285 /* rlanka : Pack J Enhancement
2286 * Need mtl_category_sets and mtl_category_set_valid_cats only *
2287 * if pick releasing by category *
2288 * Need wsh_region_locations only if releasing by region/zone *
2289 */
2290 IF (g_CategorySetID <> 0) THEN
2291 Process_Buffer(p_print_flag, 'c', ' AND MCS.category_set_id = MIC.category_set_id '
2292 || ' AND MCS.category_set_id = :x_CategorySetID '); -- ,g_CategorySetID);
2293
2294 IF (g_CategoryID <> 0) THEN
2295 Process_Buffer(p_print_flag, 'c', ' AND MIC.category_id = :x_CategoryID '
2296 || ' AND MIC.organization_id = WDD.organization_id '
2297 || ' AND MIC.inventory_item_id = WDD.inventory_item_id '); -- ,g_CategoryID);
2298 END IF;
2299 END IF;
2300
2301 --
2302 IF g_RegionID <> 0 THEN
2303 Process_Buffer(p_print_flag, 'c',
2304 ' AND WDD.ship_to_location_id IN (select location_id '
2305 || ' FROM wsh_region_locations WHERE region_id = :x_RegionID)'); -- ,g_RegionID);
2306 END IF;
2307 --
2308 IF g_ZoneID <> 0 THEN
2309 Process_Buffer(p_print_flag, 'c',
2310 ' AND WDD.ship_to_location_id IN (select location_id '
2311 || ' FROM wsh_region_locations WHERE region_id IN '
2312 || ' (SELECT region_id FROM wsh_zone_regions WHERE '
2313 || ' parent_region_id = :x_ZoneID))'); -- ,g_ZoneID);
2314 END IF;
2315 --
2316 IF (g_RelSubInventory IS NOT NULL) THEN
2317 Process_Buffer(p_print_flag, 'c',
2318 ' AND NVL(WDD.subinventory, ''-99'') = NVL(:x_RelSubInv, ''-99'')'); -- ,g_RelSubInventory);
2319 END IF;
2320 --}
2321
2322 -- 50. For Ship Sets/SMCs, P_MODE and P_INV_ITEM_ID is also considered.
2323 --{
2324 /* Bug#: 3266659 : Ignore the line when pick release criteria SHIP_SET_SMC_FLAG is E (Exclude)
2325 and line is part of the SHIP SET or line is part of the Model with SHIP MODEL COMPLETE is set YES.
2326 Ignore the line when pick release criteria SHIP_SET_SMC_FLAG is I (Include)
2327 and line is not part of the SHIP SET AND line is part of the Model with SHIP MODEL COMPLETE is set NO.
2328 This action is applicable only when the shipping parameter "Enforce Ship Set and SMC" is set 'YES' */
2329 IF ( p_enforce_ship_set_and_smc = 'Y' ) THEN
2330 IF ( g_ship_set_smc_flag = 'E') OR ( p_mode = 'WORKER' AND p_inv_item_id IS NOT NULL )THEN
2331 Process_Buffer(p_print_flag, 'c', ' AND WDD.SHIP_SET_ID IS NULL '
2332 || ' AND ( NVL(WDD.SHIP_MODEL_COMPLETE_FLAG,''N'') = ''N'')');
2333 ELSIF( g_ship_set_smc_flag = 'I') OR ( p_mode = 'WORKER' AND p_inv_item_id IS NULL ) THEN
2334 Process_Buffer(p_print_flag, 'c', ' AND (WDD.SHIP_SET_ID IS NOT NULL '
2335 || ' OR ( NVL(WDD.SHIP_MODEL_COMPLETE_FLAG,''N'') = ''Y'' '
2336 || ' AND WDD.TOP_MODEL_LINE_ID IS NOT NULL ))');
2337 END IF;
2338 END IF;
2339 --}
2340
2341 -- 60. Build Order By Clause and Group By Clause (for P_MODE = SUMMARY)
2342 --
2343 -- Determine the order by clause
2344 --
2345 IF l_debug_on THEN
2346 WSH_DEBUG_SV.logmsg(l_module_name, 'ORDER BY CLAUSE FOR ORGANIZATION ' || TO_CHAR(P_ORGANIZATION_ID));
2347 END IF;
2348 --
2349 g_orderby_SQL := '';
2350
2351 IF p_mode = 'SUMMARY' THEN
2352 --{
2353 IF p_enforce_ship_set_and_smc = 'N' THEN
2354 Process_Buffer('Y','o', ' GROUP BY WDD.ORGANIZATION_ID, WDD.INVENTORY_ITEM_ID ');
2355 ELSE
2356 Process_Buffer('Y','o', ' GROUP BY WDD.ORGANIZATION_ID,
2357 DECODE(WDD.SHIP_SET_ID,NULL,DECODE(WDD.SHIP_MODEL_COMPLETE_FLAG,''Y'',NULL,WDD.INVENTORY_ITEM_ID),NULL) ');
2358 END IF;
2359 --}
2360
2361 ELSE
2362 --{
2363 Process_Buffer('Y','o', ' ORDER BY ');
2364
2365 --Bug #2898157 : Replaced SOURCE_HEADER_ID with SOURCE_HEADER_NUMBER
2366 --Bug #3266659 : do not consider the shipsets/SMC when g_ship_set_smc_flag is E (Exclude shipsets/SMCs)
2367 -- and p_enforce_ship_set_and_smc = 'Y'
2368 IF (p_enforce_ship_set_and_smc = 'Y' AND g_ship_set_smc_flag <> 'E') THEN
2369 --{
2370 FOR i IN 1..g_total_pick_criteria LOOP
2371 IF (g_ordered_psr(i).attribute = C_INVOICE_VALUE) THEN
2372 Process_Buffer('Y','o', ' OUTSTANDING_ORDER_VALUE ' || g_ordered_psr(i).sort_order || ', ');
2373 Process_Buffer('Y','o', ' SOURCE_HEADER_ID ASC ,');
2374 ELSIF (g_ordered_psr(i).attribute = C_ORDER_NUMBER) THEN
2375 Process_Buffer('Y','o', ' to_number(SOURCE_HEADER_NUMBER) ' || g_ordered_psr(i).sort_order || ', ');
2376 ELSIF (g_ordered_psr(i).attribute = C_SCHEDULE_DATE) THEN
2377 Process_Buffer('Y','o', ' DATE_SCHEDULED ' || g_ordered_psr(i).sort_order || ', ');
2378 ELSIF (g_ordered_psr(i).attribute = C_SHIPMENT_PRIORITY) THEN
2379 Process_Buffer('Y','o', ' SHIPMENT_PRIORITY_CODE ' || g_ordered_psr(i).sort_order || ', ');
2380 END IF;
2381 END LOOP;
2382
2383 Process_Buffer('Y','o', ' NVL(WDD.SHIP_SET_ID,999999999), ');
2384
2385 -- Consider SMC only if SS is not specified
2386 Process_Buffer('Y','o', ' DECODE(NVL(WDD.SHIP_SET_ID,-999999999), -999999999, WDD.SHIP_MODEL_COMPLETE_FLAG,NULL) DESC, ');
2387
2388 -- This is necessary to push the non-transactable lines ahead in SS/SMC
2389 Process_Buffer('Y','o', ' RELEASED_STATUS DESC, ');
2390
2391 -- Consider SMC only if SS is not specified
2392 Process_Buffer('Y','o', ' DECODE(NVL(WDD.SHIP_SET_ID,-999999999), -999999999,WDD.TOP_MODEL_LINE_ID,NULL), ');
2393 Process_Buffer('Y','o', ' WDD.INVENTORY_ITEM_ID, ');
2394
2395 -- Inventory needs this for grouping all lines by line_id for SMCs
2396 Process_Buffer('Y','o', ' WDD.SOURCE_LINE_ID, ');
2397
2398 FOR i IN 1..g_total_pick_criteria LOOP
2399 IF (g_ordered_psr(i).attribute = C_TRIP_STOP_DATE) THEN
2400 Process_Buffer('Y','o', ' PLANNED_DEPARTURE_DATE ' || g_ordered_psr(i).sort_order || ', ');
2401 END IF;
2402 END LOOP;
2403 --}
2404
2405 ELSE
2406 --{
2407 -- Not SS/SMC enforced
2408
2409 Process_Buffer('Y','o', ' WDD.INVENTORY_ITEM_ID, ');
2410 --Bug #2898157 : Replaced SOURCE_HEADER_ID with SOURCE_HEADER_NUMBER
2411
2412 FOR i IN 1..g_total_pick_criteria LOOP
2413 IF (g_ordered_psr(i).attribute = C_INVOICE_VALUE) THEN
2414 Process_Buffer('Y','o', ' OUTSTANDING_ORDER_VALUE ' || g_ordered_psr(i).sort_order || ', ');
2415 Process_Buffer('Y','o', ' SOURCE_HEADER_ID ASC ,');
2416 ELSIF (g_ordered_psr(i).attribute = C_ORDER_NUMBER) THEN
2417 Process_Buffer('Y','o', ' to_number(SOURCE_HEADER_NUMBER) ' || g_ordered_psr(i).sort_order || ', ');
2418 ELSIF (g_ordered_psr(i).attribute = C_SCHEDULE_DATE) THEN
2419 Process_Buffer('Y','o', ' DATE_SCHEDULED ' || g_ordered_psr(i).sort_order || ', ');
2420 ELSIF (g_ordered_psr(i).attribute = C_TRIP_STOP_DATE) THEN
2421 Process_Buffer('Y','o', ' PLANNED_DEPARTURE_DATE ' || g_ordered_psr(i).sort_order || ', ');
2422 ELSIF (g_ordered_psr(i).attribute = C_SHIPMENT_PRIORITY) THEN
2423 Process_Buffer('Y','o', ' SHIPMENT_PRIORITY_CODE ' || g_ordered_psr(i).sort_order || ', ');
2424 END IF;
2425 END LOOP;
2426 --}
2427 END IF;
2428
2429 Process_Buffer('Y','o', ' SHIP_FROM_LOCATION_ID');
2430
2431 --}
2432 END IF;
2433
2434 --HVOP
2435 g_sql_stmt := '( ' || g_Unreleased_SQL || g_Cond_SQL||' ) '|| g_orderby_SQL;
2436
2437 --HVOP
2438
2439 IF l_debug_on THEN
2440 WSH_DEBUG_SV.logmsg(l_module_name,'Dynamic SQL-'||g_Unreleased_SQL||g_cond_SQL||g_orderby_SQL);
2441 END IF;
2442
2443 -- 70. Parse Cursor
2444 v_CursorID := DBMS_SQL.Open_Cursor;
2445 --
2446 IF l_debug_on THEN
2447 WSH_DEBUG_SV.logmsg(l_module_name, 'PARSE CURSOR' );
2448 END IF;
2449 --
2450 DBMS_SQL.Parse(v_CursorID, g_Unreleased_SQL || g_Cond_SQL|| g_orderby_SQL, DBMS_SQL.v7 );
2451 --
2452 IF l_debug_on THEN
2453 WSH_DEBUG_SV.logmsg(l_module_name, 'COLUMN DEFINITION FOR CURSOR' );
2454 END IF;
2455 --
2456 --{
2457 IF p_mode = 'SUMMARY' THEN
2458 DBMS_SQL.Define_Column(v_CursorID, 1, v_org_id);
2459 DBMS_SQL.Define_Column(v_CursorID, 2, v_inventory_item_id);
2460 DBMS_SQL.Define_Column(v_CursorID, 3, v_count);
2461 ELSE --{
2462 DBMS_SQL.Define_Column(v_CursorID, 1, v_line_id);
2463 DBMS_SQL.Define_Column(v_CursorID, 2, v_header_id);
2464 DBMS_SQL.Define_Column(v_CursorID, 3, v_org_id);
2465 DBMS_SQL.Define_Column(v_CursorID, 4, v_inventory_item_id);
2466 DBMS_SQL.Define_Column(v_CursorID, 5, v_move_order_line_id);
2467 DBMS_SQL.Define_Column(v_CursorID, 6, v_delivery_detail_id);
2468 DBMS_SQL.Define_Column(v_CursorID, 7, v_ship_model_complete_flag,1);
2469 DBMS_SQL.Define_Column(v_CursorID, 8, v_top_model_line_id);
2470 DBMS_SQL.Define_Column(v_CursorID, 9, v_ship_from_location_id);
2471 DBMS_SQL.Define_Column(v_CursorID, 10, v_ship_method_code,30);
2472 DBMS_SQL.Define_Column(v_CursorID, 11, v_shipment_priority,30);
2473 DBMS_SQL.Define_Column(v_CursorID, 12, v_date_scheduled);
2474 DBMS_SQL.Define_Column(v_CursorID, 13, v_requested_quantity);
2475 DBMS_SQL.Define_Column(v_CursorID, 14, v_requested_quantity_uom,3);
2476 DBMS_SQL.Define_Column(v_CursorID, 15, v_preferred_grade, 150); -- HW OPMCONV.
2477 DBMS_SQL.Define_Column(v_CursorID, 16, v_requested_quantity2);
2478 DBMS_SQL.Define_Column(v_CursorID, 17, v_requested_quantity_uom2, 3);
2479 DBMS_SQL.Define_Column(v_CursorID, 18, v_project_id);
2480 DBMS_SQL.Define_Column(v_CursorID, 19, v_task_id);
2481 DBMS_SQL.Define_Column(v_CursorID, 20, v_from_sub,10);
2482 DBMS_SQL.Define_Column(v_CursorID, 21, v_to_sub,10);
2483 DBMS_SQL.Define_Column(v_CursorID, 22, v_released_status,1);
2484 DBMS_SQL.Define_Column(v_CursorID, 23, v_ship_set_id);
2485 DBMS_SQL.Define_Column(v_CursorID, 24, v_source_code,30);
2486 DBMS_SQL.Define_Column(v_CursorID, 25, v_source_header_number,150);
2487 DBMS_SQL.Define_Column(v_CursorID, 26, v_planned_departure_date);
2488 DBMS_SQL.Define_Column(v_CursorID, 27, v_delivery_id);
2489 DBMS_SQL.Define_Column(v_CursorID, 28, v_unit_number, 30);
2490 DBMS_SQL.Define_Column(v_CursorID, 29, v_source_doc_type);
2491 DBMS_SQL.Define_Column(v_CursorID, 30, v_reservable_type,1);
2492 DBMS_SQL.Define_Column(v_CursorID, 31, v_last_update_date);
2493 DBMS_SQL.Define_Column(v_CursorID, 32, v_demand_source_header_id);
2494 DBMS_SQL.Define_Column(v_CursorID, 33, v_invoice_value);
2495 -- anxsharm, X-dock, customer_id
2496 DBMS_SQL.Define_Column(v_CursorID, 34, v_customer_id);
2497 --}
2498 END IF;
2499 --}
2500
2501 -- 80. Bind release criteria values
2502 --
2503 IF l_debug_on THEN
2504 WSH_DEBUG_SV.logmsg(l_module_name, 'BIND CURSOR' );
2505 END IF;
2506 --
2507 --{
2508 IF (g_del_detail_id = -1) THEN
2509 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_batch_id',p_batch_id);
2510 ELSIF (g_del_detail_id <> 0) THEN
2511 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_del_detail_id',g_del_detail_id);
2512 END IF;
2513 IF (g_order_header_id <> 0) THEN
2514 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_header_id',g_order_header_id);
2515 END IF;
2516 IF (g_order_line_id <> 0) THEN
2517 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_order_line_id',g_order_line_id);
2518 END IF;
2519 IF (g_customer_id <> 0) THEN
2520 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_customer_id',g_customer_id);
2521 END IF;
2522 IF (g_ship_from_loc_id <> -1) THEN
2523 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_ship_from_loc_id',g_ship_from_loc_id);
2524 END IF;
2525 IF (g_ship_to_loc_id <> 0) THEN
2526 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_ship_to_loc_id',g_ship_to_loc_id);
2527 END IF;
2528 IF (g_order_type_id <> 0) THEN
2529 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_order_type_id',g_order_type_id);
2530 END IF;
2531 IF (g_ship_set_number <> 0) THEN
2532 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_ship_set_id',g_ship_set_number);
2533 END IF;
2534 IF (g_task_id <> 0) THEN
2535 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_task_id',g_task_id);
2536 END IF;
2537 IF (g_project_id <> 0) THEN
2538 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_project_id',g_project_id);
2539 END IF;
2540 IF (p_organization_id IS NOT NULL) THEN
2541 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_org_id',p_organization_id);
2542 END IF;
2543 IF (g_ship_method_code IS NOT NULL) THEN
2544 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_ship_method_code',g_ship_method_code);
2545 END IF;
2546 IF (g_shipment_priority IS NOT NULL) THEN
2547 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_shipment_priority',g_shipment_priority);
2548 END IF;
2549 IF (g_from_request_date IS NOT NULL) THEN
2550 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_from_request_date',TO_CHAR(g_from_request_date,'RRRR/MM/DD HH24:MI:SS'));
2551 END IF;
2552 IF (g_to_request_date IS NOT NULL) THEN
2553 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_to_request_date',TO_CHAR(g_to_request_date,'RRRR/MM/DD HH24:MI:SS'));
2554 END IF;
2555 IF (g_from_sched_ship_date IS NOT NULL) THEN
2556 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_from_sched_ship_date',TO_CHAR(g_from_sched_ship_date,'RRRR/MM/DD HH24:MI:SS'));
2557 END IF;
2558 IF (g_to_sched_ship_date IS NOT NULL) THEN
2559 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_to_sched_ship_date',TO_CHAR(g_to_sched_ship_date,'RRRR/MM/DD HH24:MI:SS'));
2560 END IF;
2561 --}
2562
2563 --{
2564 IF p_mode = 'WORKER' THEN
2565 IF (p_inv_item_id IS NOT NULL) THEN
2566 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_inventory_item_id',p_inv_item_id);
2567 END IF;
2568 ELSE
2569 IF (g_inventory_item_id <> 0) THEN
2570 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_inventory_item_id',g_inventory_item_id);
2571 END IF;
2572 END IF;
2573 --}
2574
2575 --{
2576 IF (g_include_planned_lines <> 'N') THEN
2577 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_include_planned_lines',g_include_planned_lines);
2578 END IF;
2579 IF (g_trip_id <> 0) THEN
2580 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_trip_id',g_trip_id);
2581 IF (g_trip_stop_id <> 0) THEN
2582 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_trip_stop_id', g_trip_stop_id);
2583 END IF;
2584 END IF;
2585 IF (g_delivery_id <> 0) THEN
2586 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_delivery_id',g_delivery_id);
2587 END IF;
2588 IF (g_existing_rsvs_only_flag = 'Y') THEN
2589 IF (g_from_subinventory IS NOT NULL) THEN
2590 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_subinventory_code',g_from_subinventory);
2591 END IF;
2592 IF (g_from_locator IS NOT NULL) THEN
2593 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_locator_id',g_from_locator);
2594 END IF;
2595 END IF;
2596 IF (g_categorysetid <> 0) THEN
2597 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_categorysetid',g_categorysetid);
2598 IF (g_categoryid <> 0) THEN
2599 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_categoryid',g_categoryid);
2600 END IF;
2601 END IF;
2602 IF (g_regionid <> 0) THEN
2603 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_regionid',g_regionid);
2604 END IF;
2605 IF (g_zoneid <> 0) THEN
2606 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_zoneid',g_zoneid);
2607 END IF;
2608 IF (g_RelSubInventory IS NOT NULL) THEN
2609 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_relsubinv',g_relsubinventory);
2610 END IF;
2611 --}
2612
2613 -- 90. Execute the cursor
2614 --
2615 IF l_debug_on THEN
2616 WSH_DEBUG_SV.logmsg(l_module_name, 'EXECUTING CURSOR' );
2617 END IF;
2618 --
2619 v_ignore := DBMS_SQL.Execute(v_CursorID);
2620
2621
2622 -- 100. Fetching records for SUMMARY mode and inserting worker records
2623 --{
2624 IF p_mode = 'SUMMARY' THEN
2625 l_count := 0;
2626 LOOP
2627 IF DBMS_SQL.Fetch_Rows(v_cursorID) = 0 THEN
2628 DBMS_SQL.Close_Cursor(v_cursorID);
2629 v_cursorID := NULL;
2630 EXIT;
2631 ELSE
2632 DBMS_SQL.Column_Value(v_CursorID, 1, v_org_id);
2633 DBMS_SQL.Column_Value(v_CursorID, 2, v_inventory_item_id);
2634 DBMS_SQL.Column_Value(v_CursorID, 3, v_count);
2635
2636 INSERT INTO WSH_PR_WORKERS (
2637 batch_id,
2638 type,
2639 mo_header_id,
2640 organization_id,
2641 inventory_item_id,
2642 mo_start_line_number,
2643 mo_line_count,
2644 processed
2645 )
2646 VALUES (
2647 WSH_PICK_LIST.G_BATCH_ID,
2648 'PICK',
2649 p_mo_header_id,
2650 v_org_id,
2651 v_inventory_item_id,
2652 l_count + 1,
2653 v_count,
2654 'N'
2655 );
2656
2657 x_worker_count := nvl(x_worker_count,0) + 1;
2658 IF v_inventory_item_id IS NULL THEN
2659 x_smc_worker_count := nvl(x_smc_worker_count,0) + 1;
2660 END IF;
2661 l_count := l_count + v_count;
2662 END IF;
2663 END LOOP;
2664
2665 x_dd_count := nvl(x_dd_count,0) + l_count;
2666
2667 IF l_debug_on THEN
2668 WSH_DEBUG_SV.log(l_module_name,'x_dd_count ',x_dd_count);
2669 WSH_DEBUG_SV.log(l_module_name,'x_worker_count ',x_worker_count);
2670 WSH_DEBUG_SV.log(l_module_name,'x_smc_worker_count ',x_smc_worker_count);
2671 END IF;
2672 END IF;
2673 --}
2674
2675 x_api_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2676
2677 IF l_debug_on THEN
2678 WSH_DEBUG_SV.pop(l_module_name);
2679 END IF;
2680
2681 EXCEPTION
2682 --
2683 WHEN OTHERS THEN
2684 IF DBMS_SQL.IS_Open(v_cursorID) THEN
2685 DBMS_SQL.Close_Cursor(v_cursorID);
2686 END IF;
2687 --
2688 x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2689 --
2690 IF l_debug_on THEN
2691 WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN CREATING SELECT STATEMENT');
2692 WSH_DEBUG_SV.logmsg(l_module_name, 'SQL ERROR: ' || SQLERRM ( SQLCODE ));
2693 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2694 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2695 END IF;
2696 --
2697 END Init_Cursor;
2698
2699
2700 -- Start of comments
2701 -- API name : Get_Lines
2702 -- Type : Public
2703 -- Pre-reqs : None.
2704 -- Procedure: API This routine returns information about the lines that
2705 -- are eligible for release.
2706 -- 1.Open the dynamic cursor for sql generated in api Init_Cursor for fetching.
2707 -- 2. It fetches rows from the cursor for unreleased and backordered lines, and inserts the each
2708 -- row in the release_table based on the release sequence rule.
2709 -- Parameters :
2710 -- IN:
2711 -- p_enforce_ship_set_and_smc IN Whether to enforce Ship Set and SMC validate value Y/N.
2712 -- p_wms_flag IN Org is WMS enabled or not. Valid values Y/N.
2713 -- p_express_pick_flag IN Express Pick is enabled or not , Valid Values Y/N.
2714 -- p_batch_id IN Pick Release Batch ID.
2715 -- OUT:
2716 -- x_done_flag OUT NOCOPY whether all lines have been fetched
2717 -- x_api_status OUT NOCOPY Standard to output api status.
2718 -- End of comments
2719 Procedure Get_Lines (
2720 p_enforce_ship_set_and_smc IN VARCHAR2,
2721 p_wms_flag IN VARCHAR2,
2722 p_express_pick_flag IN VARCHAR2,
2723 p_batch_id IN NUMBER,
2724 x_done_flag OUT NOCOPY VARCHAR2,
2725 x_api_status OUT NOCOPY VARCHAR2
2726 ) IS
2727 --
2728 l_get_lock_status VARCHAR2(1);
2729 --
2730 l_debug_on BOOLEAN;
2731 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_LINES';
2732 l_ship_set_id NUMBER;
2733 l_model_line_id NUMBER;
2734 --
2735 l_skip_detail VARCHAR2(1); -- Bug# 3248578
2736
2737 -- Bug 4775539
2738 l_requested_qty_uom VARCHAR2(3);
2739 l_src_requested_qty_uom VARCHAR2(3);
2740 l_src_requested_qty NUMBER;
2741 l_inv_item_id NUMBER;
2742
2743 -- LPN CONV. rvishnuv
2744 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
2745 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
2746 l_msg_count NUMBER;
2747 l_msg_data VARCHAR2(32767);
2748 e_return_excp EXCEPTION;
2749 -- LPN CONV. rvishnuv
2750
2751 l_mode VARCHAR2(10); -- for setting mode value
2752 l_temp VARCHAR2(1);
2753 l_dummy NUMBER;
2754 l_dummy1 NUMBER;
2755 l_dummy2 NUMBER;
2756
2757 BEGIN
2758 --
2759 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2760 IF l_debug_on IS NULL THEN
2761 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2762 END IF;
2763 --
2764 IF l_debug_on THEN
2765 WSH_DEBUG_SV.push(l_module_name);
2766 WSH_DEBUG_SV.log(l_module_name,'P_ENFORCE_SHIP_SET_AND_SMC',P_ENFORCE_SHIP_SET_AND_SMC);
2767 WSH_DEBUG_SV.log(l_module_name,'P_WMS_FLAG',P_WMS_FLAG);
2768 WSH_DEBUG_SV.log(l_module_name,'P_EXPRESS_PICK_FLAG',P_EXPRESS_PICK_FLAG);
2769 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
2770 END IF;
2771 --
2772 x_api_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2773 -- handle uninitialized package errors here
2774 IF g_initialized = FALSE THEN
2775 --{
2776 x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2777 --
2778 IF l_debug_on THEN
2779 WSH_DEBUG_SV.logmsg(l_module_name, 'THE PACKAGE MUST BE INITIALIZED BEFORE USE' );
2780 WSH_DEBUG_SV.pop(l_module_name);
2781 END IF;
2782 --
2783 RETURN;
2784 --}
2785 END IF;
2786 --
2787 g_rel_current_line := 1;
2788 -- Clear the table
2789 release_table.delete;
2790
2791 -- Set flag to indicate that fetching is not completed for an organization
2792 x_done_flag := FND_API.G_FALSE;
2793
2794 -- Set the mode for Init_Cursor
2795 -- If Single process, mode is Null, otherwise Worker
2796 IF WSH_PICK_LIST.G_PICK_REL_PARALLEL THEN
2797 l_mode := 'WORKER';
2798 ELSE
2799 l_mode := NULL;
2800 END IF;
2801
2802 -- If called after the first time, place the last row fetched in previous
2803 -- call as the first row, since it was not returned in the previous call
2804 IF first_line.source_header_id <> -1 THEN
2805 --{
2806 -- Bug 1878992: Need to lock this again because the last lock would have got released
2807 -- by commit before detailing for the previous batch
2808 --
2809 Get_Detail_Lock(
2810 p_delivery_detail_id => first_line.delivery_detail_id,
2811 p_ship_set_id => first_line.ship_set_id,
2812 p_top_model_line_id => first_line.top_model_line_id,
2813 p_enforce_ship_set_and_smc => p_enforce_ship_set_and_smc,
2814 -- Bug 4775539
2815 x_requested_qty_uom => l_requested_qty_uom,
2816 x_src_requested_qty_uom => l_src_requested_qty_uom,
2817 x_src_requested_qty => l_src_requested_qty,
2818 x_inv_item_id => l_inv_item_id,
2819 x_skip_detail => l_skip_detail, -- Bug# 3248578
2820 x_return_status => l_get_lock_status);
2821 IF (l_get_lock_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2822 --{
2823 IF l_skip_detail = 'N' THEN
2824 IF l_debug_on THEN
2825 WSH_DEBUG_SV.logmsg(l_module_name, 'GOT LOCK ON DELIVERY DETAIL '||
2826 FIRST_LINE.DELIVERY_DETAIL_ID||' AGAIN');
2827 END IF;
2828 --
2829 release_table(g_rel_current_line) := first_line;
2830 g_last_ship_set_id := release_table(g_rel_current_line).ship_set_id;
2831 g_last_top_model_line_id := release_table(g_rel_current_line).top_model_line_id;
2832 g_last_model_quantity := release_table(g_rel_current_line).top_model_quantity;
2833 g_last_header_id := release_table(g_rel_current_line).source_header_id;
2834 g_last_source_code := release_table(g_rel_current_line).source_code;
2835 g_rel_current_line := g_rel_current_line + 1;
2836 --}
2837 ELSE
2838 IF l_debug_on THEN
2839 WSH_DEBUG_SV.logmsg(l_module_name,'No longer eligible to pick release. Ignoring Delivery Detail '||first_line.delivery_detail_id);
2840 END IF;
2841 END IF;
2842
2843 ELSE
2844 --{
2845 IF l_debug_on THEN
2846 WSH_DEBUG_SV.logmsg(l_module_name, 'UNABLE TO LOCK. IGNORING DELIVERY DETAIL '
2847 ||FIRST_LINE.DELIVERY_DETAIL_ID );
2848 END IF;
2849 --}
2850 END IF;
2851 --}
2852 ELSE
2853 --{
2854 g_lock_or_hold_failed := FALSE;
2855 g_failed_ship_set_id := NULL;
2856 g_failed_top_model_line_id := NULL;
2857 g_last_ship_set_id := NULL;
2858 g_last_top_model_line_id := NULL;
2859 g_last_model_quantity := NULL;
2860 g_last_header_id := NULL;
2861 g_last_source_code := NULL;
2862 --}
2863 END IF; /* if first_line */
2864
2865 IF l_debug_on THEN
2866 wsh_debug_sv.log(l_module_name, 'g_rel_current_line', g_rel_current_line);
2867 END IF;
2868
2869 LOOP
2870 --{
2871 IF v_cursorID IS NOT NULL THEN
2872 l_dummy := DBMS_SQL.Fetch_Rows(v_cursorID);
2873 END IF;
2874 IF ( v_cursorID IS NULL ) OR ( l_dummy = 0 ) THEN
2875 -- Either all lines are fetched for a worker record or a New Organization
2876 --{
2877 IF l_debug_on THEN
2878 WSH_DEBUG_SV.log(l_module_name, 'v_cursorID',v_cursorID);
2879 END IF;
2880
2881 --{
2882 -- Fetch and lock worker record to get Organization - Item combination
2883 v_prev_item_id := v_pr_inv_item_id;
2884 LOOP
2885 FETCH c_work_cursorID INTO v_pr_org_id, v_pr_inv_item_id, v_pr_mo_line_number, v_pr_mo_line_count;
2886 IF c_work_cursorID%NOTFOUND THEN --{
2887 IF l_debug_on THEN
2888 WSH_DEBUG_SV.logmsg(l_module_name, 'FETCHED ALL LINES FOR ORGANIZATION');
2889 END IF;
2890 IF c_work_cursorID%ISOPEN THEN
2891 CLOSE c_work_cursorID;
2892 END IF;
2893 IF v_CursorID IS NOT NULL THEN
2894 DBMS_SQL.Close_Cursor(v_CursorID);
2895 v_CursorID := NULL;
2896 END IF;
2897 x_done_flag := FND_API.G_TRUE;
2898 EXIT;
2899 ELSE
2900 --{
2901 IF l_debug_on THEN
2902 WSH_DEBUG_SV.logmsg(l_module_name,'Getting lock for worker record Batch '||p_batch_id
2903 ||' Org '||v_pr_org_id|| ' Item '||v_pr_inv_item_id);
2904 END IF;
2905 DECLARE
2906 worker_row_locked exception;
2907 PRAGMA EXCEPTION_INIT(worker_row_locked, -54);
2908 BEGIN
2909 SELECT 'Y'
2910 INTO l_temp
2911 FROM wsh_pr_workers
2912 WHERE batch_id = p_batch_id
2913 AND organization_id = v_pr_org_id
2914 AND NVL(inventory_item_id,-99) = NVL(v_pr_inv_item_id,-99)
2915 AND type = 'PICK'
2916 AND processed = 'N'
2917 FOR UPDATE NOWAIT;
2918 EXIT; -- successfully locked
2919 EXCEPTION
2920 WHEN worker_row_locked THEN
2921 IF l_debug_on THEN
2922 WSH_DEBUG_SV.logmsg(l_module_name,'Unable to lock as worker record as it is already locked');
2923 END IF;
2924 l_temp := 'N';
2925 WHEN no_data_found THEN
2926 IF l_debug_on THEN
2927 WSH_DEBUG_SV.logmsg(l_module_name,'Record already processed by one of the other workers');
2928 END IF;
2929 l_temp := 'N';
2930 WHEN OTHERS THEN
2931 IF l_debug_on THEN
2932 WSH_DEBUG_SV.logmsg(l_module_name,'Unable to lock as worker record due to error');
2933 WSH_DEBUG_SV.logmsg(l_module_name,SQLERRM);
2934 END IF;
2935 l_temp := 'N';
2936 END;
2937 --}
2938 END IF;
2939 END LOOP;
2940 --}
2941
2942 -- No more lines to process, then Exit ; otherwise check if worker record
2943 -- was locked and update worker record
2944 IF x_done_flag = FND_API.G_TRUE THEN
2945 -- Reinitialize the first line marker since we have
2946 -- fetched all rows
2947 first_line.source_header_id := -1;
2948
2949 -- If the last line is in SS/SMC we need to validate it
2950 IF ((g_rel_current_line > 1) AND
2951 ((release_table(g_rel_current_line - 1).ship_set_id > 0) OR
2952 (release_table(g_rel_current_line - 1).top_model_line_id > 0))) THEN
2953 --{
2954 validate_ss_smc(
2955 release_table(g_rel_current_line - 1).ship_set_id,
2956 release_table(g_rel_current_line - 1).top_model_line_id,
2957 release_table(g_rel_current_line - 1).source_header_id,
2958 release_table(g_rel_current_line - 1).source_code,
2959 g_return_status);
2960 IF (g_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2961 x_api_status := g_return_status;
2962 IF l_debug_on THEN
2963 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR OCCURRED IN VALIDATE_SS_SMC/0' );
2964 END IF;
2965 raise e_return_excp; -- LPN CONV. rv
2966 END IF;
2967 --}
2968 END IF;
2969 EXIT;
2970 ELSIF l_temp = 'Y' THEN --{
2971 -- Update Worker Record as processed
2972 BEGIN
2973 UPDATE wsh_pr_workers
2974 SET processed = 'Y'
2975 WHERE batch_id = p_batch_id
2976 AND organization_id = v_pr_org_id
2977 AND NVL(inventory_item_id,-99) = NVL(v_pr_inv_item_id,-99)
2978 AND type = 'PICK';
2979 EXCEPTION WHEN OTHERS THEN
2980 x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2981 IF l_debug_on THEN
2982 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred in updating worker records as processed');
2983 WSH_DEBUG_SV.pop(l_module_name);
2984 END IF;
2985 IF DBMS_SQL.Is_Open(v_CursorID) THEN
2986 DBMS_SQL.Close_Cursor(v_CursorID);
2987 END IF;
2988 IF c_work_cursorID%ISOPEN THEN
2989 CLOSE c_work_cursorID;
2990 END IF;
2991 ROLLBACK;
2992 RETURN;
2993 END;
2994 END IF; --}
2995
2996 --{
2997 -- Open Cursor for Organization - Item combination, only if called for new Organization OR
2998 -- if the previous call was for SMC and now for regular item as Item Bind Variable changes
2999 IF ( v_cursorID IS NULL ) OR ( v_prev_item_id IS NULL ) THEN
3000 IF l_debug_on THEN
3001 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PR_CRITERIA.Init_Cursor', WSH_DEBUG_SV.C_PROC_LEVEL);
3002 END IF;
3003 Init_Cursor (
3004 p_organization_id => v_pr_org_id,
3005 p_mode => l_mode,
3006 p_wms_org => p_wms_flag,
3007 p_mo_header_id => NULL, -- this is not required for WORKER mode
3008 p_inv_item_id => v_pr_inv_item_id,
3009 p_enforce_ship_set_and_smc => p_enforce_ship_set_and_smc,
3010 p_print_flag => 'Y', -- this should be printed only once for each Organization
3011 -- use global variable to print only once for all Orgs
3012 p_express_pick => p_express_pick_flag,
3013 p_batch_id => p_batch_id,
3014 x_worker_count => l_dummy,
3015 x_smc_worker_count => l_dummy2,
3016 x_dd_count => l_dummy1,
3017 x_api_status => x_api_status);
3018 IF (x_api_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (x_api_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3019 IF l_debug_on THEN
3020 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred in Init_Cursor');
3021 WSH_DEBUG_SV.pop(l_module_name);
3022 END IF;
3023 IF DBMS_SQL.Is_Open(v_CursorID) THEN
3024 DBMS_SQL.Close_Cursor(v_CursorID);
3025 END IF;
3026 IF c_work_cursorID%ISOPEN THEN
3027 CLOSE c_work_cursorID;
3028 END IF;
3029 ROLLBACK;
3030 RETURN;
3031 END IF;
3032 v_total_rec_fetched := 0;
3033 ELSE
3034 -- Bind the new Item and execute the cursor again
3035 IF l_debug_on THEN
3036 WSH_DEBUG_SV.logmsg(l_module_name, 'Binding new Item '||v_pr_inv_item_id||' and Executing Cursor' );
3037 END IF;
3038 DBMS_SQL.BIND_VARIABLE(v_CursorID,':X_inventory_item_id',v_pr_inv_item_id);
3039 l_dummy := DBMS_SQL.Execute(v_CursorID);
3040 v_total_rec_fetched := 0;
3041 END IF;
3042 --}
3043
3044 --}
3045 ELSE
3046 --{
3047 IF l_debug_on THEN
3048 WSH_DEBUG_SV.logmsg(l_module_name, 'MAP OUTPUT COLUMNS' );
3049 END IF;
3050
3051 DBMS_SQL.Column_Value(v_CursorID, 1, v_line_id);
3052 DBMS_SQL.Column_Value(v_CursorID, 2, v_header_id);
3053 DBMS_SQL.Column_Value(v_CursorID, 3, v_org_id);
3054 DBMS_SQL.Column_Value(v_CursorID, 4, v_inventory_item_id);
3055 DBMS_SQL.Column_Value(v_CursorID, 5, v_move_order_line_id);
3056 DBMS_SQL.Column_Value(v_CursorID, 6, v_delivery_detail_id);
3057 DBMS_SQL.Column_Value(v_CursorID, 7, v_ship_model_complete_flag);
3058 DBMS_SQL.Column_Value(v_CursorID, 8, v_top_model_line_id);
3059 DBMS_SQL.Column_Value(v_CursorID, 9, v_ship_from_location_id);
3060 DBMS_SQL.Column_Value(v_CursorID, 10, v_ship_method_code);
3061 DBMS_SQL.Column_Value(v_CursorID, 11, v_shipment_priority);
3062 DBMS_SQL.Column_Value(v_CursorID, 12, v_date_scheduled);
3063 DBMS_SQL.Column_Value(v_CursorID, 13, v_requested_quantity);
3064 DBMS_SQL.Column_Value(v_CursorID, 14, v_requested_quantity_uom);
3065 DBMS_SQL.Column_Value(v_CursorID, 15, v_preferred_grade);
3066 DBMS_SQL.Column_Value(v_CursorID, 16, v_requested_quantity2);
3067 DBMS_SQL.Column_Value(v_CursorID, 17, v_requested_quantity_uom2);
3068 DBMS_SQL.Column_Value(v_CursorID, 18, v_project_id);
3069 DBMS_SQL.Column_Value(v_CursorID, 19, v_task_id);
3070 DBMS_SQL.Column_Value(v_CursorID, 20, v_from_sub);
3071 DBMS_SQL.Column_Value(v_CursorID, 21, v_to_sub);
3072 DBMS_SQL.Column_Value(v_CursorID, 22, v_released_status);
3073 DBMS_SQL.Column_Value(v_CursorID, 23, v_ship_set_id);
3074 DBMS_SQL.Column_Value(v_CursorID, 24, v_source_code);
3075 DBMS_SQL.Column_Value(v_CursorID, 25, v_source_header_number);
3076 DBMS_SQL.Column_Value(v_CursorID, 26, v_planned_departure_date);
3077 DBMS_SQL.Column_Value(v_CursorID, 27, v_delivery_id);
3078 DBMS_SQL.Column_Value(v_CursorID, 28, v_unit_number);
3079 DBMS_SQL.Column_Value(v_CursorID, 29, v_source_doc_type);
3080 DBMS_SQL.Column_Value(v_CursorID, 30, v_reservable_type);
3081 DBMS_SQL.Column_Value(v_CursorID, 31, v_last_update_date);
3082 DBMS_SQL.Column_Value(v_CursorID, 32, v_demand_source_header_id);
3083 DBMS_SQL.Column_Value(v_CursorID, 33, v_invoice_value);
3084 -- anxsharm, X-dock, customer_id
3085 DBMS_SQL.Column_Value(v_CursorID, 34, v_customer_id);
3086
3087 IF l_debug_on THEN
3088 WSH_DEBUG_SV.log(l_module_name, 'CURRENT LINE IS', TO_CHAR(G_REL_CURRENT_LINE));
3089 WSH_DEBUG_SV.log(l_module_name, 'Delivery detail ID', v_delivery_detail_id);
3090 END IF;
3091
3092 -- Save fetched record into release table
3093 Insert_RL_Row(p_enforce_ship_set_and_smc, l_skip_detail, g_return_status);
3094
3095 IF (g_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) OR (g_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3096 x_api_status := g_return_status;
3097 IF l_debug_on THEN
3098 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR OCCURRED IN INSERT_RL_ROW');
3099 --WSH_DEBUG_SV.pop(l_module_name);
3100 END IF;
3101 IF DBMS_SQL.Is_Open(v_CursorID) THEN
3102 DBMS_SQL.Close_Cursor(v_CursorID);
3103 END IF;
3104 IF c_work_cursorID%ISOPEN THEN
3105 CLOSE c_work_cursorID;
3106 END IF;
3107 --RETURN;
3108 raise e_return_excp; -- LPN CONV. rv
3109 ELSIF (g_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
3110 IF l_debug_on THEN
3111 WSH_DEBUG_SV.logmsg(l_module_name, 'WARNING: SKIPPING THE ORDER LINE');
3112 END IF;
3113 x_api_status := g_return_status;
3114 ELSE
3115 IF l_skip_detail = 'N' THEN
3116 g_rel_current_line := g_rel_current_line + 1;
3117 v_total_rec_fetched := v_total_rec_fetched + 1;
3118 ELSE
3119 IF l_debug_on THEN
3120 WSH_DEBUG_SV.logmsg(l_module_name,'Skipping the Order Line');
3121 END IF;
3122 x_api_status := g_return_status;
3123 END IF;
3124 END IF;
3125 --}
3126 END IF;
3127
3128 IF l_debug_on THEN
3129 WSH_DEBUG_SV.logmsg(l_module_name,'v_pr_mo_line_count : '||v_pr_mo_line_count||
3130 ' , v_total_rec_fetched : '||v_total_rec_fetched);
3131 END IF;
3132
3133 IF (v_pr_mo_line_count is not null AND v_total_rec_fetched > v_pr_mo_line_count) THEN
3134 --{
3135 IF (release_table(g_rel_current_line - 1).ship_set_id > 0 OR
3136 release_table(g_rel_current_line - 1).top_model_line_id > 0) THEN
3137 l_ship_set_id := release_table(g_rel_current_line - 1).ship_set_id;
3138 l_model_line_id := release_table(g_rel_current_line - 1).top_model_line_id;
3139 LOOP
3140 IF (release_table(g_rel_current_line - 1).ship_set_id = l_ship_set_id OR
3141 release_table(g_rel_current_line - 1).top_model_line_id = l_model_line_id) THEN
3142 release_table.delete(g_rel_current_line - 1);
3143 g_rel_current_line := g_rel_current_line -1;
3144 END IF;
3145 END LOOP;
3146 END IF;
3147 --}
3148 END IF;
3149
3150 IF (g_rel_current_line > MAX_LINES) THEN
3151 --{
3152 IF (((release_table(g_rel_current_line - 1).ship_set_id > 0) AND
3153 (release_table(g_rel_current_line - 1).ship_set_id =
3154 release_table(g_rel_current_line - 2).ship_set_id)) OR
3155 ((release_table(g_rel_current_line - 1).top_model_line_id > 0) AND
3156 (release_table(g_rel_current_line - 1).top_model_line_id =
3157 release_table(g_rel_current_line - 2).top_model_line_id))) THEN
3158 IF l_debug_on THEN
3159 WSH_DEBUG_SV.logmsg(l_module_name,'LARGE MODEL OR SS, IN MODEL FETCH MODE');
3160 END IF;
3161 ELSE
3162 first_line := release_table(g_rel_current_line - 1);
3163 release_table.delete(g_rel_current_line - 1);
3164 g_rel_current_line := g_rel_current_line - 1;
3165 x_done_flag := FND_API.G_FALSE;
3166 --If exceed MAX_LINES and not broken ship set or model
3167 EXIT;
3168 END IF;
3169 --}
3170 END IF;
3171 --}
3172 END LOOP;
3173
3174 -- LPN CONV. rvishnuv
3175 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3176 THEN
3177 --{
3178
3179 IF l_debug_on THEN
3180 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3181 END IF;
3182
3183
3184 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3185 (
3186 p_in_rec => l_lpn_in_sync_comm_rec,
3187 x_return_status => g_return_status,
3188 x_out_rec => l_lpn_out_sync_comm_rec
3189 );
3190 --
3191 IF l_debug_on THEN
3192 WSH_DEBUG_SV.log(l_module_name, 'Return Status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS API is ', g_return_status);
3193 WSH_DEBUG_SV.log(l_module_name, 'current value of x_api_status is ', x_api_status);
3194 END IF;
3195 --
3196 IF g_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
3197 THEN
3198 --{
3199 x_api_status := g_return_status;
3200 IF l_debug_on THEN
3201 WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR OCCURRED IN WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS');
3202 END IF;
3203 --}
3204 ELSIF (g_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
3205 AND nvl(x_api_status, WSH_UTIL_CORE.G_RET_STS_SUCCESS) NOT IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR))
3206 THEN
3207 --{
3208 x_api_status := g_return_status;
3209 IF l_debug_on THEN
3210 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR OCCURRED IN WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS');
3211 END IF;
3212 --}
3213 ELSIF (g_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
3214 AND nvl(x_api_status, WSH_UTIL_CORE.G_RET_STS_SUCCESS) NOT IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR))
3215 THEN
3216 --{
3217 x_api_status := g_return_status;
3218 IF l_debug_on THEN
3219 WSH_DEBUG_SV.logmsg(l_module_name, 'WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS API returned warning');
3220 END IF;
3221 --}
3222 END IF;
3223 --}
3224 END IF;
3225 -- LPN CONV. rvishnuv
3226
3227 --
3228 IF l_debug_on THEN
3229 WSH_DEBUG_SV.log(l_module_name, 'release_table.COUNT', release_table.COUNT);
3230 WSH_DEBUG_SV.pop(l_module_name);
3231 END IF;
3232 --
3233 EXCEPTION
3234 --
3235 -- LPN CONV. rv
3236 WHEN e_return_excp THEN
3237 --
3238 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3239 THEN
3240 --{
3241 --
3242 IF l_debug_on THEN
3243 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3244 END IF;
3245 --
3246 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3247 (
3248 p_in_rec => l_lpn_in_sync_comm_rec,
3249 x_return_status => g_return_status,
3250 x_out_rec => l_lpn_out_sync_comm_rec
3251 );
3252 --
3253 IF l_debug_on THEN
3254 WSH_DEBUG_SV.log(l_module_name, 'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS', g_return_status);
3255 END IF;
3256 IF (g_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) AND x_api_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3257 x_api_status := g_return_status;
3258 END IF;
3259 --}
3260 END IF;
3261 --
3262 IF l_debug_on THEN
3263 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_RETURN_EXCP');
3264 END IF;
3265 -- LPN CONV. rv
3266 --
3267
3268
3269 WHEN OTHERS THEN
3270 --
3271 IF l_debug_on THEN
3272 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3273 END IF;
3274 --
3275 IF DBMS_SQL.Is_Open(v_CursorID) THEN
3276 DBMS_SQL.Close_Cursor(v_CursorID);
3277 END IF;
3278 IF c_work_cursorID%ISOPEN THEN
3279 CLOSE c_work_cursorID;
3280 END IF;
3281 --
3282 x_done_flag := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3283 x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3284 --
3285 -- K LPN CONV. rv
3286 --
3287 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3288 THEN
3289 --{
3290 IF l_debug_on THEN
3291 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3292 END IF;
3293
3294 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3295 (
3296 p_in_rec => l_lpn_in_sync_comm_rec,
3297 x_return_status => g_return_status,
3298 x_out_rec => l_lpn_out_sync_comm_rec
3299 );
3300 --
3301 --
3302 IF l_debug_on THEN
3303 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',g_return_status);
3304 END IF;
3305 --
3306 --
3307 --}
3308 END IF;
3309 --
3310 -- K LPN CONV. rv
3311 --
3312 --
3313 IF l_debug_on THEN
3314 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3315 END IF;
3316 --
3317 END Get_Lines;
3318
3319
3320 -- Start of comments
3321 -- API name : Insert_RL_Row
3322 -- Type : Private
3323 -- Pre-reqs : None.
3324 -- Procedure: API to store the pick release eligible line to pick release pl/sql table. Api does
3325 -- 1. Determine eligibility for ship set and models.
3326 -- 2. For order management source line check for Credit hold.
3327 -- 3. Calculate reservation and backordered the difference, if requested quantity grater than the reserved quantity.
3328 -- 4. Store the line information in pl/sql release table.
3329 -- Parameters :
3330 -- IN:
3331 -- p_enforce_ship_set_and_smc IN Whether to enforce Ship Set and SMC validate value Y/N.
3332 -- OUT:
3333 -- x_done_flag OUT NOCOPY whether all lines have been fetched
3334 -- x_skip_detail OUT NOCOPY Ignoring delivery detail since no longer satisfies the pick release criteria,
3335 -- validate value Y/N.
3336 -- x_api_status OUT NOCOPY Standard to output api status.
3337 -- End of comments
3338 PROCEDURE Insert_RL_Row(
3339 p_enforce_ship_set_and_smc IN VARCHAR2,
3340 x_skip_detail OUT NOCOPY VARCHAR2, --Bug# 3248578
3341 x_api_status OUT NOCOPY VARCHAR2
3342 ) IS
3343
3344 CURSOR c_order_line(v_line_id IN NUMBER) IS
3345 SELECT ORDERED_QUANTITY,
3346 ORDER_QUANTITY_UOM
3347 FROM OE_ORDER_LINES_ALL
3348 WHERE LINE_ID = v_line_id;
3349
3350 l_return_status VARCHAR2(100);
3351 l_ordered_quantity NUMBER;
3352 l_order_quantity_uom VARCHAR2(3);
3353 l_requested_quantity NUMBER;
3354 l_new_delivery_detail_id NUMBER;
3355 l_split_status VARCHAR2(30);
3356 l_result NUMBER;
3357
3358 -- HW BUG#:1941429 cross_docking for OPM
3359 l_result2 NUMBER;
3360 l_requested_quantity2 NUMBER;
3361 l_msg_count NUMBER;
3362 l_msg_data VARCHAR2(3000);
3363
3364 l_exception_return_status VARCHAR2(30);
3365 l_exception_msg_count NUMBER;
3366 l_exception_msg_data VARCHAR2(4000) := NULL;
3367 l_dummy_exception_id NUMBER;
3368 l_message VARCHAR2(2000);
3369 l_request_id NUMBER;
3370
3371 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
3372 l_dbi_rs VARCHAR2(1); -- DBI Project
3373
3374 l_debug_on BOOLEAN;
3375 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_RL_ROW';
3376
3377 -- Bug 4775539
3378 l_requested_qty_uom varchar2(3);
3379 l_src_requested_qty_uom varchar2(3);
3380 l_src_requested_qty number;
3381 l_inv_item_id number;
3382 l_exception_name varchar2(2000);
3383
3384 BEGIN
3385 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3386 --
3387 IF l_debug_on IS NULL
3388 THEN
3389 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3390 END IF;
3391 --
3392 IF l_debug_on THEN
3393 WSH_DEBUG_SV.push(l_module_name);
3394 WSH_DEBUG_SV.log(l_module_name,'P_ENFORCE_SHIP_SET_AND_SMC',P_ENFORCE_SHIP_SET_AND_SMC);
3395 WSH_DEBUG_SV.log(l_module_name, 'Delivery ID', v_delivery_id);
3396 WSH_DEBUG_SV.log(l_module_name, 'Released Status', v_released_status);
3397 WSH_DEBUG_SV.log(l_module_name, 'Quantity', v_requested_quantity);
3398 WSH_DEBUG_SV.log(l_module_name, 'Quantity UOM', v_requested_quantity_uom);
3399 WSH_DEBUG_SV.log(l_module_name, 'v_ship_model_complete_flag',v_ship_model_complete_flag);
3400 END IF;
3401
3402 x_skip_detail := 'N'; --Bug# 3248578
3403
3404 IF (p_enforce_ship_set_and_smc = 'Y') THEN
3405 --{
3406 IF (v_released_status IN ('X')) THEN
3407 --None pick release eligible line, ignore
3408 IF l_debug_on THEN
3409 WSH_DEBUG_SV.logmsg(l_module_name, 'THIS IS NON-TRANSACTABLE LINE. IGNORING SS/SMC' );
3410 END IF;
3411 v_ship_set_id := NULL;
3412 v_top_model_line_id := NULL;
3413 v_ship_model_complete_flag := NULL;
3414 END IF;
3415
3416 IF (v_ship_set_id IS NOT NULL) THEN
3417 -- Ignore SMC if SS is Specified
3418 IF l_debug_on THEN
3419 WSH_DEBUG_SV.logmsg(l_module_name, 'IGNORING SMC AS SHIP SET IS SPECIFIED' );
3420 END IF;
3421 v_ship_model_complete_flag := NULL;
3422 v_top_model_line_id := NULL;
3423 ELSE
3424 IF (NVL(v_ship_model_complete_flag,'N') = 'N') THEN
3425 -- Ignore top_model_line_id if SMC is not set to Y
3426 v_top_model_line_id := NULL;
3427 END IF;
3428 END IF;
3429
3430 IF ((g_last_ship_set_id IS NULL) AND (g_last_top_model_line_id IS NULL)) THEN
3431 g_lock_or_hold_failed := FALSE;
3432 g_failed_ship_set_id := NULL;
3433 g_failed_top_model_line_id := NULL;
3434 g_last_model_quantity := NULL;
3435 g_last_header_id := NULL;
3436 g_last_source_code := NULL;
3437 END IF;
3438
3439 IF l_debug_on THEN
3440 WSH_DEBUG_SV.log(l_module_name,'g_last_ship_set_id',g_last_ship_set_id);
3441 WSH_DEBUG_SV.log(l_module_name,'v_ship_set_id',v_ship_set_id);
3442 WSH_DEBUG_SV.log(l_module_name,'g_last_top_model_line_id',g_last_top_model_line_id);
3443 WSH_DEBUG_SV.log(l_module_name,'v_top_model_line_id',v_top_model_line_id);
3444 END IF;
3445
3446 IF (((g_last_ship_set_id IS NOT NULL) AND (g_last_ship_set_id <> NVL(v_ship_set_id,-99))) OR
3447 ((g_last_top_model_line_id IS NOT NULL) AND (g_last_top_model_line_id <> NVL(v_top_model_line_id,-99)))) THEN
3448 -- SHIP SET OR SMC IS CHANGED. WE NEED TO VALIDATE SS/SMC
3449 validate_ss_smc(
3450 g_last_ship_set_id,
3451 g_last_top_model_line_id,
3452 g_last_header_id,
3453 g_last_source_code,
3454 l_return_status);
3455 IF (g_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3456 IF l_debug_on THEN
3457 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR OCCURRED IN VALIDATE_SS_SMC/5' );
3458 END IF;
3459 x_api_status := l_return_status;
3460 IF l_debug_on THEN
3461 WSH_DEBUG_SV.pop(l_module_name);
3462 END IF;
3463 RETURN;
3464 END IF;
3465
3466 g_lock_or_hold_failed := FALSE;
3467 g_failed_ship_set_id := NULL;
3468 g_failed_top_model_line_id := NULL;
3469 g_last_ship_set_id := NULL;
3470 g_last_top_model_line_id := NULL;
3471 g_last_model_quantity := NULL;
3472 g_last_header_id := NULL;
3473 g_last_source_code := NULL;
3474 END IF;
3475 --}
3476 ELSE
3477 --{
3478 v_ship_set_id := NULL;
3479 v_top_model_line_id := NULL;
3480 v_ship_model_complete_flag := 'N';
3481 --}
3482 END IF; -- p_enforce_ship_set_and_smc
3483
3484
3485 IF (v_source_code = 'OE') THEN --{
3486 -- Bug 1287776: Check for Credit Check and Holds
3487 IF l_debug_on THEN
3488 WSH_DEBUG_SV.logmsg(l_module_name,
3489 'PROCESSING HEADER '
3490 ||TO_CHAR ( V_HEADER_ID )
3491 ||' LINE '
3492 ||TO_CHAR ( V_LINE_ID )
3493 || ' DETAIL '
3494 ||TO_CHAR ( V_DELIVERY_DETAIL_ID )
3495 ||' SS '
3496 ||TO_CHAR ( V_SHIP_SET_ID )
3497 || ' MODEL '
3498 ||TO_CHAR ( V_TOP_MODEL_LINE_ID )
3499 || ' ITEM '
3500 ||TO_CHAR ( V_INVENTORY_ITEM_ID ) );
3501 END IF;
3502
3503 IF ((p_enforce_ship_set_and_smc = 'Y') AND
3504 (g_lock_or_hold_failed) AND
3505 ((g_failed_ship_set_id IS NOT NULL) OR (g_failed_top_model_line_id IS NOT NULL))) THEN
3506 --{
3507 IF l_debug_on THEN
3508 WSH_DEBUG_SV.logmsg(l_module_name, 'IGNORING THIS LINE SINCE ONE OF THE PREVIOUS LINES IN SHIP SET/SMC FAILED' );
3509 END IF;
3510 -- Ignore this line as previous line in ship set or model is faild.
3511 x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3512 IF l_debug_on THEN
3513 WSH_DEBUG_SV.pop(l_module_name);
3514 END IF;
3515 RETURN;
3516 --}
3517 END IF;
3518
3519 -- Check if Credit Check needs to be called
3520 IF (WSH_PR_CRITERIA.g_credit_check_option = 'A') OR
3521 (WSH_PR_CRITERIA.g_credit_check_option = 'R' AND v_released_status = 'R') OR
3522 (WSH_PR_CRITERIA.g_credit_check_option = 'B' AND v_released_status = 'B') THEN --{
3523 IF l_debug_on THEN
3524 WSH_DEBUG_SV.logmsg(l_module_name, 'CHECKING FOR CREDIT CHECK/HOLDS' );
3525 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DETAILS_VALIDATIONS.CHECK_CREDIT_HOLDS',WSH_DEBUG_SV.C_PROC_LEVEL);
3526 END IF;
3527 WSH_DETAILS_VALIDATIONS.check_credit_holds(
3528 p_detail_id => v_delivery_detail_id,
3529 p_activity_type => 'PICK',
3530 p_source_line_id => v_line_id,
3531 p_source_header_id => v_header_id,
3532 p_source_code => v_source_code,
3533 p_init_flag => 'N',
3534 x_return_status => l_return_status);
3535
3536 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3537 /* Don't error out. Raise Warning only */
3538 x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3539 Set_Globals(p_enforce_ship_set_and_smc, v_ship_set_id, v_top_model_line_id);
3540 IF l_debug_on THEN
3541 WSH_DEBUG_SV.pop(l_module_name);
3542 END IF;
3543 RETURN;
3544 END IF;
3545 END IF; --}
3546 END IF; --}
3547
3548
3549 Get_Detail_Lock(
3550 p_delivery_detail_id => v_delivery_detail_id,
3551 p_ship_set_id => v_ship_set_id,
3552 p_top_model_line_id => v_top_model_line_id,
3553 p_enforce_ship_set_and_smc => p_enforce_ship_set_and_smc,
3554 -- Bug 4775539
3555 x_requested_qty_uom => l_requested_qty_uom,
3556 x_src_requested_qty_uom => l_src_requested_qty_uom,
3557 x_src_requested_qty => l_src_requested_qty,
3558 x_inv_item_id => l_inv_item_id,
3559 x_skip_detail => x_skip_detail, -- Bug# 3248578
3560 x_return_status => x_api_status);
3561
3562 IF (x_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3563 IF l_debug_on THEN
3564 WSH_DEBUG_SV.logmsg(l_module_name, 'GET_DETAIL_LOCK RETURNED WITH WARNING' );
3565 WSH_DEBUG_SV.pop(l_module_name);
3566 END IF;
3567 --
3568 RETURN;
3569 -- Bug# 3248578 start of change
3570 ELSIF (x_api_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND (x_skip_detail = 'Y') THEN
3571 IF l_debug_on THEN
3572 WSH_DEBUG_SV.logmsg(l_module_name,'No longer eligible to Pick Release, ignoring the line');
3573 END IF;
3574 RETURN;
3575 END IF;
3576
3577 -- Bug# 3248578 end of change
3578
3579 -- HW BUG#:1941429 for cross_docking. We need to branch */
3580 /* HW OPMCONV - Removed code forking
3581
3582 /* Moved Prior Reservation validation from WSHPGSLB.pls to here for SS/SMC */
3583 l_result := v_requested_quantity;
3584 l_requested_quantity := v_requested_quantity;
3585 -- HW BUG#:1941429 cross_docking OPM
3586 l_result2 := v_requested_quantity2;
3587 l_requested_quantity2 := nvl(v_requested_quantity2,0);
3588
3589 --Check for reservation status for prior reservation line.
3590 -- Bug 4775539 Added g_honor_pick_from default is 'Y'
3591 IF ((g_existing_rsvs_only_flag = 'Y' or (g_honor_pick_from = 'Y' and g_from_subinventory is not null)) and (v_released_status <> 'X')) THEN
3592 --{
3593 -- Bug 4775539
3594 IF (g_existing_rsvs_only_flag <> 'Y') THEN
3595 IF v_header_id <> NVL(g_cache_header_id, -99) THEN
3596 IF l_debug_on THEN
3597 WSH_DEBUG_SV.logmsg(l_module_name, 'Call API to get demand sales order number for '||v_header_id);
3598 END IF;
3599 v_demand_source_header_id := INV_SALESORDER.GET_SALESORDER_FOR_OEHEADER(v_header_id);
3600 g_cache_header_id := v_header_id;
3601 g_cache_demand_header_id := v_demand_source_header_id;
3602 ELSE
3603 IF l_debug_on THEN
3604 WSH_DEBUG_SV.logmsg(l_module_name, 'use cached value '||g_cache_demand_header_id);
3605 END IF;
3606 v_demand_source_header_id := g_cache_demand_header_id;
3607 END IF;
3608 END IF;
3609
3610 IF l_debug_on THEN
3611 WSH_DEBUG_SV.logmsg(l_module_name, 'CHECKING FOR RESERVATIONS' );
3612 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICK_LIST.CALCULATE_RESERVATIONS',WSH_DEBUG_SV.C_PROC_LEVEL);
3613 END IF;
3614 --Calculate Reservations
3615 -- HW OPMCONV - Pass and return qty2
3616 WSH_PICK_LIST.Calculate_Reservations(
3617 p_demand_source_header_id => v_demand_source_header_id,
3618 p_demand_source_line_id => v_line_id,
3619 p_requested_quantity => v_requested_quantity,
3620 -- Bug 4775539
3621 p_requested_quantity_uom => l_requested_qty_uom,
3622 p_src_requested_quantity_uom => l_src_requested_qty_uom,
3623 p_src_requested_quantity => l_src_requested_qty,
3624 p_inv_item_id => l_inv_item_id,
3625 -- p_requested_quantity2 => v_requested_quantity2,
3626 x_result => l_result,
3627 x_result2 => l_result2);
3628
3629 IF (l_result = 0) THEN -- No Fulfillment
3630 IF l_debug_on THEN
3631 -- Bug 4775539
3632 IF g_existing_rsvs_only_flag = 'Y' THEN
3633 WSH_DEBUG_SV.logmsg(l_module_name, 'PRIOR RESERVATIONS IS SPECIFIED AND RESERVED QUANTITY IS 0' );
3634 ELSE
3635 WSH_DEBUG_SV.logmsg(l_module_name, 'PICK FROM SUB IS HONORED AND AVAILABLE QUANTITY IS 0' );
3636 END IF;
3637 WSH_DEBUG_SV.logmsg(l_module_name, 'IGNORING THE LINE' );
3638 END IF;
3639 --
3640 Set_Globals(p_enforce_ship_set_and_smc, v_ship_set_id, v_top_model_line_id);
3641
3642 x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3643 -- bug 2164110 - log exception
3644 l_message := null;
3645 -- Bug 4775539
3646 IF g_existing_rsvs_only_flag = 'Y' THEN
3647 l_exception_name := 'WSH_PICK_PRIOR_RSV';
3648 ELSE
3649 l_exception_name := 'WSH_HONOR_PICK_FROM';
3650 END IF;
3651 FND_MESSAGE.SET_NAME('WSH',l_exception_name);
3652 -- end Bug 4775539
3653 FND_MESSAGE.SET_NAME('WSH','WSH_PICK_PRIOR_RSV');
3654 l_message := FND_MESSAGE.GET;
3655 l_request_id := fnd_global.conc_request_id;
3656 IF ( l_request_id <> -1 OR WSH_PICK_LIST.G_BATCH_ID IS NOT NULL ) THEN
3657 wsh_xc_util.log_exception(
3658 p_api_version => 1.0,
3659 p_logging_entity => 'SHIPPER',
3660 p_logging_entity_id => FND_GLOBAL.USER_ID,
3661 -- Bug 4775539
3662 p_exception_name => l_exception_name,
3663 p_message => l_message,
3664 p_logged_at_location_id => v_ship_from_location_id,
3665 p_exception_location_id => v_ship_from_location_id,
3666 p_delivery_detail_id => v_delivery_detail_id,
3667 p_request_id => l_request_id,
3668 p_batch_id => WSH_PICK_LIST.G_BATCH_ID,
3669 x_return_status => l_exception_return_status,
3670 x_msg_count => l_exception_msg_count,
3671 x_msg_data => l_exception_msg_data,
3672 x_exception_id => l_dummy_exception_id);
3673 wsh_util_core.PrintMsg('Please view Shipping Exception Report for detail of the logged exception');
3674 END IF;
3675 -- end bug 2164110
3676
3677 IF l_debug_on THEN
3678 WSH_DEBUG_SV.pop(l_module_name);
3679 END IF;
3680 RETURN;
3681 ELSIF (l_result = l_requested_quantity) THEN -- Complete Fulfillment
3682 NULL;
3683 ELSE -- (l_result < l_requested_quantity) Partial Fulfillment
3684 IF ((v_ship_set_id > 0) OR (v_top_model_line_id > 0)) THEN
3685 IF l_debug_on THEN
3686 IF l_debug_on THEN
3687 IF g_existing_rsvs_only_flag = 'Y' THEN
3688 WSH_DEBUG_SV.logmsg(l_module_name, 'PRIOR RESERVATIONS IS SPECIFIED AND SS/SMC LINE IS PARTIALLY RESERVED' );
3689 ELSE
3690 WSH_DEBUG_SV.logmsg(l_module_name, 'PICK FROM HONOR IS SPECIFIED AND SS/SMC LINE IS PARTIALLY RESERVED' );
3691 END IF;
3692 END IF;
3693 WSH_DEBUG_SV.logmsg(l_module_name, 'IGNORING THE LINE' );
3694 END IF;
3695
3696 Set_Globals(p_enforce_ship_set_and_smc, v_ship_set_id, v_top_model_line_id);
3697 x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3698 -- bug 2164110 - log exception
3699 l_message := null;
3700 -- Bug 4775539
3701 IF g_existing_rsvs_only_flag = 'Y' THEN
3702 l_exception_name := 'WSH_PICK_PRIOR_RSV';
3703 ELSE
3704 l_exception_name := 'WSH_HONOR_PICK_FROM';
3705 END IF;
3706 FND_MESSAGE.SET_NAME('WSH',l_exception_name);
3707 -- end Bug 4775539
3708 FND_MESSAGE.SET_NAME('WSH','WSH_PICK_PRIOR_RSV');
3709 l_message := FND_MESSAGE.GET;
3710 l_request_id := fnd_global.conc_request_id;
3711 IF ( l_request_id <> -1 OR WSH_PICK_LIST.G_BATCH_ID IS NOT NULL ) THEN
3712 wsh_xc_util.log_exception(
3713 p_api_version => 1.0,
3714 p_logging_entity => 'SHIPPER',
3715 p_logging_entity_id => FND_GLOBAL.USER_ID,
3716 -- Bug 4775539
3717 p_exception_name => l_exception_name,
3718 p_message => l_message,
3719 p_logged_at_location_id => v_ship_from_location_id,
3720 p_exception_location_id => v_ship_from_location_id,
3721 p_delivery_detail_id => v_delivery_detail_id,
3722 p_request_id => l_request_id,
3723 p_batch_id => WSH_PICK_LIST.G_BATCH_ID,
3724 x_return_status => l_exception_return_status,
3725 x_msg_count => l_exception_msg_count,
3726 x_msg_data => l_exception_msg_data,
3727 x_exception_id => l_dummy_exception_id);
3728 wsh_util_core.PrintMsg('Please view Shipping Exception Report for detail of the logged exception');
3729 END IF;
3730 -- end bug 2164110
3731
3732 IF l_debug_on THEN
3733 WSH_DEBUG_SV.pop(l_module_name);
3734 END IF;
3735 RETURN;
3736 ELSE
3737 l_requested_quantity := l_requested_quantity - l_result;
3738 -- HW OPMCONV - Added qty2
3739 l_requested_quantity2 := l_requested_quantity2 - l_result2;
3740
3741 IF l_debug_on THEN
3742 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
3743 END IF;
3744
3745 -- HW OPMCONV - Added l_requested_quantity2 and re-arranged parameters
3746
3747 WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
3748 p_from_detail_id => v_delivery_detail_id,
3749 p_req_quantity => l_requested_quantity,
3750 x_new_detail_id => l_new_delivery_detail_id,
3751 p_req_quantity2 => l_requested_quantity2,
3752 x_return_status => l_split_status,
3753 p_unassign_flag => 'N'
3754 );
3755
3756 IF l_debug_on THEN
3757 WSH_DEBUG_SV.logmsg(l_module_name, 'CREATED NEW DELIVERY DETAIL '||L_NEW_DELIVERY_DETAIL_ID );
3758 END IF;
3759 END IF;
3760 END IF;
3761 --}
3762 END IF;
3763
3764 --}
3765 -- To check if there is a Non-Reservable Item present and set the
3766 -- global flag to 'Y'. If the flag is already set, no need to reset
3767 -- it again.
3768 IF G_NONRESERVABLE_ITEM = 'N' AND v_reservable_type = 2 THEN
3769 G_NONRESERVABLE_ITEM := 'Y';
3770 END IF;
3771
3772 IF l_debug_on THEN
3773 WSH_DEBUG_SV.logmsg(l_module_name, 'INSERT INTO TABLE' );
3774 END IF;
3775
3776 --Store the detail line values to release table
3777 release_table(g_rel_current_line).source_line_id := v_line_id;
3778 release_table(g_rel_current_line).source_header_id := v_header_id;
3779 release_table(g_rel_current_line).organization_id := v_org_id;
3780 release_table(g_rel_current_line).inventory_item_id := v_inventory_item_id;
3781 release_table(g_rel_current_line).move_order_line_id := v_move_order_line_id;
3782 release_table(g_rel_current_line).delivery_detail_id := v_delivery_detail_id;
3783
3784 release_table(g_rel_current_line).line_number := v_pr_mo_line_number;
3785 v_pr_mo_line_number := v_pr_mo_line_number + 1;
3786
3787 release_table(g_rel_current_line).ship_from_location_id := v_ship_from_location_id;
3788 release_table(g_rel_current_line).ship_method_code := v_ship_method_code;
3789 release_table(g_rel_current_line).shipment_priority := v_shipment_priority;
3790 release_table(g_rel_current_line).date_scheduled := v_date_scheduled;
3791 release_table(g_rel_current_line).requested_quantity := l_result;
3792 release_table(g_rel_current_line).requested_quantity_uom := v_requested_quantity_uom;
3793 release_table(g_rel_current_line).preferred_grade := v_preferred_grade;
3794
3795 -- HW BUG#:1941429 OPM cross_docking
3796 -- Changed v_requested_quantiy2 to be l_result2
3797 release_table(g_rel_current_line).requested_quantity2 := nvl(l_result2,0);
3798 release_table(g_rel_current_line).requested_quantity_uom2 := v_requested_quantity_uom2;
3799 release_table(g_rel_current_line).project_id := v_project_id;
3800 release_table(g_rel_current_line).task_id := v_task_id;
3801 release_table(g_rel_current_line).from_sub := v_from_sub;
3802 release_table(g_rel_current_line).to_sub := v_to_sub;
3803 release_table(g_rel_current_line).released_status := v_released_status;
3804 release_table(g_rel_current_line).ship_set_id := v_ship_set_id;
3805 release_table(g_rel_current_line).source_header_number := v_source_header_number;
3806 -- Inventory wants only ship_set_id if SMC and SHIP SET are specified for a MODEL
3807 IF ((release_table(g_rel_current_line).ship_set_id is NULL) AND
3808 (v_top_model_line_id is NOT NULL)) THEN
3809 release_table(g_rel_current_line).top_model_line_id := v_top_model_line_id;
3810
3811 IF (v_top_model_line_id <> NVL(g_last_top_model_line_id,-99)) THEN
3812 OPEN c_order_line(v_top_model_line_id);
3813 FETCH c_order_line INTO l_ordered_quantity, l_order_quantity_uom;
3814 CLOSE c_order_line;
3815
3816 IF l_debug_on THEN
3817 WSH_DEBUG_SV.log(l_module_name, 'l_ordered_quantity',l_ordered_quantity);
3818 WSH_DEBUG_SV.log(l_module_name, 'l_order_quantity_uom',l_order_quantity_uom);
3819 END IF;
3820
3821 release_table(g_rel_current_line).top_model_quantity := l_ordered_quantity;
3822 g_last_model_quantity := l_ordered_quantity;
3823 ELSE
3824 release_table(g_rel_current_line).top_model_quantity := g_last_model_quantity;
3825 END IF;
3826 ELSE
3827 release_table(g_rel_current_line).top_model_line_id := NULL;
3828 release_table(g_rel_current_line).top_model_quantity := NULL;
3829 END IF;
3830 release_table(g_rel_current_line).source_code := v_source_code;
3831 release_table(g_rel_current_line).planned_departure_date := v_planned_departure_date;
3832 release_table(g_rel_current_line).delivery_id := v_delivery_id;
3833 release_table(g_rel_current_line).unit_number := v_unit_number;
3834 release_table(g_rel_current_line).source_doc_type := v_source_doc_type;
3835 IF (g_existing_rsvs_only_flag = 'Y') THEN
3836 release_table(g_rel_current_line).demand_source_header_id := v_demand_source_header_id;
3837 END IF;
3838
3839 -- anxsharm, X-dock
3840 release_table(g_rel_current_line).customer_id := v_customer_id;
3841 -- anxsharm, end of X-dock
3842
3843 IF v_reservable_type = 2 THEN -- ECO 5220234
3844 release_table(g_rel_current_line).non_reservable_flag := 'Y';
3845 END IF;
3846
3847 IF (p_enforce_ship_set_and_smc = 'Y') THEN
3848 g_last_ship_set_id := v_ship_set_id;
3849 g_last_top_model_line_id := v_top_model_line_id;
3850 g_last_header_id := v_header_id;
3851 g_last_source_code := v_source_code;
3852 END IF;
3853
3854 x_api_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3855
3856 --
3857 IF l_debug_on THEN
3858 WSH_DEBUG_SV.pop(l_module_name);
3859 END IF;
3860 --
3861 EXCEPTION
3862 WHEN OTHERS THEN
3863 IF l_debug_on THEN
3864 WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN INSERT_RL_ROW' );
3865 END IF;
3866 x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3867 IF l_debug_on THEN
3868 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3869 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3870 END IF;
3871
3872 END Insert_RL_Row;
3873
3874
3875 -- Start of comments
3876 -- API name : Process_Buffer
3877 -- Type : Private
3878 -- Pre-reqs : None.
3879 -- Procedure: API to processes a line of text, by first writing to the
3880 -- log file and then concatenating it to the required SQL buffer.
3881 -- Parameters :
3882 -- IN:
3883 -- p_print_flag IN Indicates whether to print to log.
3884 -- p_buffer_name IN Identifies which buffer to append to.
3885 -- 'u' -> Unreleased_SQL
3886 -- 'b' -> Backordered_SQL
3887 -- 'c' -> cond_SQL
3888 -- p_buffer_text IN Identifies the text to process.
3889 -- p_bind_value IN Identifies the value for the bind variable refere in p_buffer_text.
3890 -- OUT:
3891 -- None
3892 -- End of comments
3893 PROCEDURE Process_Buffer(
3894 p_print_flag IN VARCHAR2,
3895 p_buffer_name IN VARCHAR2,
3896 p_buffer_text IN VARCHAR2,
3897 p_bind_value IN VARCHAR2 default NULL
3898 ) IS
3899 --
3900 l_debug_on BOOLEAN;
3901 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_BUFFER';
3902 --
3903 BEGIN
3904 --
3905 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3906 --
3907 IF l_debug_on IS NULL THEN
3908 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3909 END IF;
3910 --
3911 IF p_buffer_name = 'u' THEN
3912 g_Unreleased_SQL := g_Unreleased_SQL || p_buffer_text;
3913 ELSIF p_buffer_name = 'b' THEN
3914 g_Backordered_SQL := g_Backordered_SQL || p_buffer_text;
3915 ELSIF p_buffer_name = 'c' THEN
3916 g_cond_SQL := g_cond_SQL || p_buffer_text;
3917 ELSIF p_buffer_name = 'o' THEN
3918 g_orderby_SQL := g_orderby_SQL || p_buffer_text;
3919 ELSE
3920 RETURN;
3921 END IF;
3922
3923 END Process_Buffer;
3924
3925 END WSH_PR_CRITERIA;