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