1 package body WMS_Strategy_PVT as
2 /* $Header: WMSVPPSB.pls 120.10.12010000.5 2008/11/14 08:54:44 mitgupta ship $ */
3
4 -- File : WMSVPPSB.pls
5 -- Content : WMS_Strategy_PVT package body
6 -- Description : W<S strategy private API's
7 -- Notes :
8 -- Modified : 02/08/99 mzeckzer created
9 -- : 04/20/99 bitang modified
10 -- Modified : 05/17/02 Grao
11 -- Modified : 05/12/05 Grao - [Added code to handle rule_id instead of strategy_id from
12 -- rules workbench ]
13 -- Modified : 09/06/2008 Kbanddyo - [Added call to procedure INV_Quantity_Tree_PVT. release_lock
14 -- as part of bug fix 6867434]
15
16 g_pkg_name constant varchar2(30) := 'WMS_Strategy_PVT';
17 -- API versions called within WMS_Strategy_PVT.Apply API
18 g_pp_rule_api_version constant number := 1.0; -- WMS_Rule_PVT
19 g_qty_tree_api_version constant number := 1.0; -- INV_Quantity_Tree_PVT
20
21 --Procedures for logging messages
22 PROCEDURE log_event(
23 p_api_name VARCHAR2,
24 p_label VARCHAR2,
25 p_message VARCHAR2) IS
26
27 l_module VARCHAR2(255);
28
29 BEGIN
30
31 l_module := 'wms.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
32 inv_log_util.trace(p_message, l_module, 9);
33 /*fnd_log.string(
34 log_level => FND_LOG.LEVEL_EVENT
35 ,module => l_module
36 ,message => p_message);
37 inv_log_util.trace(p_message, l_module, 9);
38 gmi_reservation_util.println(p_message); */
39 END log_event;
40
41 PROCEDURE log_error(
42 p_api_name VARCHAR2,
43 p_label VARCHAR2,
44 p_message VARCHAR2) IS
45
46 l_module VARCHAR2(255);
47
48 BEGIN
49
50 l_module := 'wms.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
51 inv_log_util.trace(p_message, l_module, 9);
52 /*
53 fnd_log.string(
54 log_level => FND_LOG.LEVEL_ERROR
55 ,module => l_module
56 ,message => p_message);
57 inv_log_util.trace(p_message, l_module, 9);
58 gmi_reservation_util.println(p_label||' '||p_message); */
59 END log_error;
60
61 PROCEDURE log_error_msg(
62 p_api_name VARCHAR2,
63 p_label VARCHAR2) IS
64
65 l_module VARCHAR2(255);
66
67 BEGIN
68
69 l_module := 'wms.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
70 inv_log_util.trace(p_message => 'Error in '||p_api_name,
71 p_module => l_module,
72 p_level => 9);
73 /*
74 fnd_log.message(
75 log_level => FND_LOG.LEVEL_ERROR
76 ,module => l_module
77 ,pop_message => FALSE);
78
79 inv_log_util.trace(p_message => 'Error in '||p_api_name,
80 p_module => l_module,
81 p_level => 9);
82
83 gmi_reservation_util.println(p_label); */
84 END log_error_msg;
85
86 PROCEDURE log_procedure(
87 p_api_name VARCHAR2,
88 p_label VARCHAR2,
89 p_message VARCHAR2) IS
90
91 l_module VARCHAR2(255);
92
93 BEGIN
94
95 l_module := 'wms.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
96 inv_log_util.trace(p_message, l_module, 9);
97 /*
98 fnd_log.string(
99 log_level => FND_LOG.LEVEL_PROCEDURE
100 ,module => l_module
101 ,message => p_message);
102 inv_log_util.trace(p_message, l_module, 9);
103 gmi_reservation_util.println(p_message);
104 */
105 END log_procedure;
106
107 PROCEDURE log_statement(p_api_name VARCHAR2, p_label VARCHAR2, p_message VARCHAR2) IS
108 l_module VARCHAR2(255);
109 BEGIN
110 l_module := 'wms.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
111 inv_log_util.trace(p_message, l_module, 9);
112 END log_statement;
113
114 -- Start of comments
115 -- Name : InitInput
116 -- Function : Initializes internal table of detail input records.
117 -- Returns input header information.
118 -- Pre-reqs : none
119 -- Parameters :
120 -- x_return_status out varchar2(1)
121 -- x_msg_count out number
122 -- x_msg_data out varchar2(2000)
123 -- p_transaction_temp_id in number required
124 -- p_type_code in number required
125 -- x_organization_id out number
126 -- x_inventory_item_id out number
127 -- x_transaction_source_type_id out number
128 -- x_transaction_source_id out number
129 -- x_trx_source_line_id out number
130 -- x_trx_source_delivery_id out number
131 -- x_transaction_source_name out varchar2(30)
132 -- x_tree_mode out number
133 -- Notes : privat procedure for internal use only
134 -- End of comments
135
136 procedure InitInput (
137 x_return_status out nocopy varchar2
138 ,x_msg_count out nocopy number
139 ,x_msg_data out nocopy varchar2
140 ,p_transaction_temp_id in number
141 ,p_type_code in number
142 ,x_organization_id out nocopy number
143 ,x_inventory_item_id out nocopy number
144 ,x_transaction_uom out nocopy varchar2
145 ,x_primary_uom out nocopy varchar2
146 ,x_secondary_uom out nocopy varchar2
147 ,x_transaction_source_type_id out nocopy number
148 ,x_transaction_source_id out nocopy number
149 ,x_trx_source_line_id out nocopy number
150 ,x_trx_source_delivery_id out nocopy number
151 ,x_transaction_source_name out nocopy varchar2
152 ,x_transaction_type_id out nocopy number
153 ,x_tree_mode out nocopy number
154 ) is
155
156 l_api_name VARCHAR2(30) := 'InitInput';
157 l_pp_transaction_temp_id
158 WMS_TRANSACTIONS_TEMP.PP_TRANSACTION_TEMP_ID%type;
159 l_revision WMS_TRANSACTIONS_TEMP.REVISION%type;
160 l_lot_number WMS_TRANSACTIONS_TEMP.LOT_NUMBER%type;
161 l_lot_expiration_date WMS_TRANSACTIONS_TEMP.LOT_EXPIRATION_DATE%type;
162 l_from_subinventory_code WMS_TRANSACTIONS_TEMP.FROM_SUBINVENTORY_CODE%type;
163 l_from_locator_id WMS_TRANSACTIONS_TEMP.FROM_LOCATOR_ID%type;
164 l_from_cost_group_id WMS_TRANSACTIONS_TEMP.FROM_COST_GROUP_ID%type;
165 l_to_subinventory_code WMS_TRANSACTIONS_TEMP.TO_SUBINVENTORY_CODE%type;
166 l_to_locator_id WMS_TRANSACTIONS_TEMP.TO_LOCATOR_ID%type;
167 l_to_cost_group_id WMS_TRANSACTIONS_TEMP.TO_COST_GROUP_ID%type;
168 l_primary_quantity WMS_TRANSACTIONS_TEMP.PRIMARY_QUANTITY%type;
169 l_secondary_quantity WMS_TRANSACTIONS_TEMP.SECONDARY_QUANTITY%type;
170 l_grade_code WMS_TRANSACTIONS_TEMP.GRADE_CODE%type;
171 l_line_type_code WMS_TRANSACTIONS_TEMP.LINE_TYPE_CODE%type;
172 l_reservation_id NUMBER;
173 l_serial_number WMS_TRANSACTIONS_TEMP.SERIAL_NUMBER%type;
174 l_transaction_action_id Number;
175 l_from_organization_id NUMBER;
176 l_to_organization_id NUMBER;
177
178 --- [ Added code - l_serial_number WMS_TRANSACTIONS_TEMP.SERIAL_NUMBER; ]
179 l_lpn_id NUMBER;
180
181 l_debug NUMBER;
182 --use to_organization if put away, from_organization if pick
183 -- 3/7/01 - changed query to get txn_source_id and txn_source_line_id
184 -- instead of header_id and line_id
185 /* Bug #5265024
186 CURSOR inphead IS
187 SELECT decode(p_type_code, 1, mpsmttv.to_organization_id,
188 mpsmttv.from_organization_id) organization_id
189 ,mpsmttv.inventory_item_id
190 ,mpsmttv.transaction_source_type_id
191 ,mpsmttv.txn_source_id
192 ,mpsmttv.txn_source_line_id
193 ,mpsmttv.txn_source_line_detail
194 ,mpsmttv.txn_source_name
195 ,mpsmttv.transaction_type_id
196 ,mpsmttv.transaction_uom
197 ,msi.primary_uom_code
198 ,msi.secondary_uom_code
199 from mtl_system_items msi
200 ,wms_strategy_mat_txn_tmp_v mpsmttv
201 where msi.organization_id =
202 decode(p_type_code, 1, mpsmttv.to_organization_id,
203 mpsmttv.from_organization_id)
204 and msi.inventory_item_id = mpsmttv.inventory_item_id
205 and mpsmttv.line_id = p_transaction_temp_id
206 and mpsmttv.type_code = p_type_code; */
207
208 CURSOR inphead1 IS
209 select txn_source_id ,
210 txn_source_line_id,
211 txn_source_name,
212 txn_source_line_detail
213 from wms_txn_context_temp wtct
214 where line_id = p_transaction_temp_id;
215
216
217 --changed by jcearley on 12/8/99 to order transfers in order of
218 --pick suggestions and put away suggestions
219 -- [ Added the following code in the cursor inpline / serial allocation proj ]
220 cursor inpline is
221 select mptt.PP_TRANSACTION_TEMP_ID
222 ,mptt.REVISION
223 ,mptt.LOT_NUMBER
224 ,mptt.LOT_EXPIRATION_DATE
225 ,mptt.FROM_SUBINVENTORY_CODE
226 ,mptt.FROM_LOCATOR_ID
227 ,mptt.FROM_COST_GROUP_ID
228 ,mptt.TO_SUBINVENTORY_CODE
229 ,mptt.TO_LOCATOR_ID
230 ,mptt.TO_COST_GROUP_ID
231 ,mptt.primary_quantity
232 ,mptt.secondary_quantity
233 ,mptt.grade_code
234 ,mptt.reservation_id
235 ,mptt.serial_number ---- [ Added code - ,mptt.serial_number ]
236 ,mptt.lpn_id
237 from WMS_TRANSACTIONS_TEMP mptt
238 where mptt.TRANSACTION_TEMP_ID = p_transaction_temp_id
239 and mptt.TYPE_CODE = p_type_code
240 and mptt.LINE_TYPE_CODE = 1
241 order by mptt.pp_transaction_temp_id
242 ;
243
244
245
246 begin
247 IF (g_debug IS NULL) THEN
248 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
249 END IF;
250 l_debug := g_debug;
251 If (l_debug = 1) then
252 log_procedure(l_api_name,'start', 'start InitInput');
253 End if;
254 -- Initialize API return status to success
255 x_return_status := fnd_api.g_ret_sts_success;
256 If l_debug = 1 THEN
257 log_procedure(l_api_name,'start', 'p_type_code '||p_type_code);
258 log_procedure(l_api_name,'start', 'p_transaction_temp_id '||p_transaction_temp_id);
259 END IF;
260
261
262 --- Added the following code as part bug fix 5265024 --
263 -- Set and get the MO line values
264 IF inv_cache.set_mol_rec(p_transaction_temp_id) THEN
265 x_transaction_type_id := inv_cache.mol_rec.transaction_type_id;
266 x_inventory_item_id := inv_cache.mol_rec.inventory_item_id;
267 x_transaction_source_type_id := inv_cache.mol_rec.transaction_source_type_id;
268
269 l_transaction_action_id := inv_cache.mtt_rec.transaction_action_id;
270
271 --- This code is added to handle direct-org xfers
272 IF l_transaction_action_id = 3 THEN
273 l_to_organization_id := inv_cache.mol_rec.to_organization_id;
274 ELSE
275 l_from_organization_id := inv_cache.mol_rec.organization_id;
276 END IF;
277
278 IF p_type_code = 1 AND l_transaction_action_id = 3 THEN
279 x_organization_id := l_to_organization_id;
280 ELSE
281 x_organization_id := l_from_organization_id;
282 END IF;
283
284 x_transaction_uom := inv_cache.mol_rec.uom_code;
285 x_primary_uom := inv_cache.item_rec.primary_uom_code;
286 x_secondary_uom := inv_cache.item_rec.secondary_uom_code ;
287
288 open inphead1;
289 fetch inphead1 into
290 x_transaction_source_id,
291 x_trx_source_line_id,
292 x_transaction_source_name,
293 x_trx_source_delivery_id;
294 if inphead1%notfound then
295 If (l_debug = 1) then
296 log_event(l_api_name, 'no_input_head',
297 'The general input information stored in ' ||
298 'WMS_TXN_CONTEXT_TEMP could not be found. ' ||
299 'Detailing will fail.');
300 End if;
301 end if;
302 close inphead1;
303
304
305 END IF;
306 If (l_debug = 1) then
307 log_statement(l_api_name, 'Detailing Header Values ', '-------------');
308 log_statement(l_api_name, 'l_transaction_action_id ', l_transaction_action_id);
309 log_statement(l_api_name, 'x_organization_id ', x_organization_id);
310 log_statement(l_api_name, 'x_transaction_type_id ', x_transaction_type_id);
311 log_statement(l_api_name, 'x_inventory_item_id ', x_inventory_item_id);
312 log_statement(l_api_name, 'x_transaction_source_type_id', x_transaction_source_type_id);
313 log_statement(l_api_name, 'x_transaction_source__id', x_transaction_source_id);
314 log_statement(l_api_name, 'x_trx_source_line_id', x_trx_source_line_id);
315 log_statement(l_api_name, 'x_transaction_source_name', x_transaction_source_name);
316 log_statement(l_api_name, 'x_trx_source_delivery_id' , x_trx_source_delivery_id );
317 log_statement(l_api_name, 'x_transaction_uom ', x_transaction_uom);
318 log_statement(l_api_name, 'x_primary_uom ' , x_primary_uom);
319 log_statement(l_api_name, 'x_transaction_source_name ', x_transaction_source_name );
320 END IF;
321
322 /* --
323 -- debugging portion
324 -- can be commented ut for final code
325 IF inv_pp_debug.is_debug_mode THEN
326 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
327 END IF;
328 -- end of debugging section
329 --
330 -- Get txn detail line input parameters
331
332 -- Commented as a part of performance bug fix 5265024
333 open inphead;
334 fetch inphead into x_organization_id
335 ,x_inventory_item_id
336 ,x_transaction_source_type_id
337 ,x_transaction_source_id
338 ,x_trx_source_line_id
339 ,x_trx_source_delivery_id
340 ,x_transaction_source_name
341 ,x_transaction_type_id
342 ,x_transaction_uom
343 ,x_primary_uom
344 ,x_secondary_uom
345 ;
346 if inphead%notfound then
347 --close inphead;
348 -- no need to raise error - instead, no lines will be detailed
349 -- raise no_data_found;
350 If (l_debug = 1) then
351 log_event(l_api_name, 'no_input_head',
352 'The general input information stored in ' ||
353 'WMS_STRATEGY_MAT_TXN_TMP_V could not be found. ' ||
354 'Detailing will fail.');
355 End if;
356 end if;
357 close inphead;
358 */
359 -- End of bug fix 5265024
360 log_procedure(l_api_name,'start', 'got head ');
361 -- Tree mode should be parameter for pp engine call ??!!
362 -- ER 7307189 changes start
363 /*
364 if p_type_code = 2 then
365 x_tree_mode := INV_Quantity_Tree_PVT.g_transaction_mode;
366 else
367 x_tree_mode := null;
368 end if;
369 */
370
371 log_statement(l_api_name, 'x_transaction_source_type_id:-', x_transaction_source_type_id);
372 log_statement(l_api_name, 'l_transaction_action_id:-', l_transaction_action_id);
373 log_statement(l_api_name, 'p_type_code:-', p_type_code);
374
375 if p_type_code= 2 and x_transaction_source_type_id=4 and l_transaction_action_id=2 then
376 x_tree_mode := INV_Quantity_Tree_PUB.g_no_lpn_rsvs_mode ;
377 elsif p_type_code = 2 then
378 x_tree_mode := INV_Quantity_Tree_PVT.g_transaction_mode;
379 else
380 x_tree_mode := null;
381 end if;
382
383 -- ER 7307189 changes end
384
385 log_procedure(l_api_name,'start', 'tree mode '||x_tree_mode);
386 -- Initialize input line PL/SQL table
387 Wms_re_common_pvt.InitInputTable;
388
389 -- Loop through txn detail line input parameters
390 open inpline;
391 while true loop
392 fetch inpline into l_pp_transaction_temp_id
393 ,l_revision
394 ,l_lot_number
395 ,l_lot_expiration_date
396 ,l_from_subinventory_code
397 ,l_from_locator_id
398 ,l_from_cost_group_id
399 ,l_to_subinventory_code
400 ,l_to_locator_id
401 ,l_to_cost_group_id
402 ,l_primary_quantity
403 ,l_secondary_quantity
404 ,l_grade_code
405 ,l_reservation_id
406 ,l_serial_number -- [ new code -- l_serial_number]
407 ,l_lpn_id;
408
409 log_procedure(l_api_name,'start', 'inpline '||l_lot_number);
410
411 exit when inpline%notfound;
412
413 -- create a new input line record in the input line table
414 Wms_re_common_pvt.InitInputLine ( l_pp_transaction_temp_id
415 ,l_revision
416 ,l_lot_number
417 ,l_lot_expiration_date
418 ,l_from_subinventory_code
419 ,l_from_locator_id
420 ,l_from_cost_group_id
421 ,l_to_subinventory_code
422 ,l_to_locator_id
423 ,l_to_cost_group_id
424 ,l_primary_quantity
425 ,l_secondary_quantity
426 ,l_grade_code
427 ,l_reservation_id
428 ,l_serial_number -- [ new code - serial_number]
429 ,l_lpn_id
430 );
431 end loop;
432 close inpline;
433
434 /* --no need to raise error - no lines will be detailed
435 if Wms_re_common_pvt.GetCountInputLines = 0 then
436 raise no_data_found;
437 end if;
438
439 --
440 -- debugging portion
441 -- can be commented ut for final code
442 IF inv_pp_debug.is_debug_mode THEN
443 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
444 END IF;
445 -- end of debugging section
446 -- */
447 If (l_debug = 1) then
448 log_procedure(l_api_name, 'end', 'End InitInput');
449 End if;
450 exception
451 when others then
452 /* --
453 -- debugging portion
454 -- can be commented ut for final code
455 IF inv_pp_debug.is_debug_mode THEN
456 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
457 -- the message retrieved here since it is no longer on the stack
458 inv_pp_debug.set_last_error_message(Sqlerrm);
459 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
460 inv_pp_debug.send_last_error_message;
461 END IF;
462 -- end of debugging section
463 -- */
464 /* if inphead%isopen then
465 close inphead;
466 end if; */
467 if inpline%isopen then
468 close inpline;
469 end if;
470 x_return_status := fnd_api.g_ret_sts_unexp_error;
471 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
472 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
473 end if;
474 fnd_msg_pub.count_and_get( p_count => x_msg_count
475 ,p_data => x_msg_data );
476 If (l_debug = 1) then
477 log_error(l_api_name, 'error', 'Error in InitInput - ' ||x_msg_data);
478 End if;
479
480 end InitInput;
481
482 -- Start of comments
483 -- Name : InitStrategyRules
484 -- Function : Initializes internal table of strategy members ( = rules ).
485 -- Pre-reqs : none
486 -- Parameters :
487 -- x_return_status out varchar2(1)
488 -- x_msg_count out number
489 -- x_msg_data out varchar2(2000)
490 -- p_strategy_id in number required
491 -- Notes : privat procedure for internal use only
492 -- End of comments
493
494 procedure InitStrategyRules (
495 x_return_status out NOCOPY varchar2
496 ,x_msg_count out NOCOPY number
497 ,x_msg_data out NOCOPY varchar2
498 ,p_strategy_id in number
499 ) is
500
501 l_api_name VARCHAR2(30) := 'InitStrategyRules';
502 l_rule_id WMS_STRATEGY_MEMBERS.RULE_ID%type;
503 l_partial_success_allowed_flag
504 WMS_STRATEGY_MEMBERS.PARTIAL_SUCCESS_ALLOWED_FLAG%type;
505 l_rule_counter integer;
506 l_debug NUMBER;
507 --changed by jcearley on 12/8/99
508 --rules assigned to strategies can now be disabled, so we
509 --now have to check to make sure that all the rules are enabled
510 --before we use them in the engine
511 CURSOR rules IS
512 SELECT wsm.rule_id
513 ,wsm.partial_success_allowed_flag
514 ,NVL(wsb.over_allocation_mode, 1) ,wsb.tolerance_value
515 FROM wms_strategy_members wsm
516 ,wms_strategies_b wsb
517 ,wms_rules_b wrb
518 WHERE wsm.strategy_id = p_strategy_id
519 AND wsb.strategy_id = p_strategy_id
520 AND wrb.rule_id = wsm.rule_id
521 AND wrb.enabled_flag = 'Y'
522 AND wms_datecheck_pvt.date_valid (wsb.organization_id,
523 wsm.date_type_code,
524 wsm.date_type_from,
525 wsm.date_type_to,
526 wsm.effective_from,
527 wsm.effective_to) = 'Y'
528 ORDER BY wsm.sequence_number;
529
530 begin
531 IF (g_debug IS NULL) THEN
532 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
533 END IF;
534 l_debug := g_debug;
535 If (l_debug = 1) then
536 log_procedure(l_api_name, 'start', 'Start InitStrategyRules');
537 End if;
538 -- nothing to init if p_strategy_id is null (no rule detailing)
539 IF p_strategy_id IS NULL THEN
540 x_return_status := fnd_api.g_ret_sts_success;
541 RETURN;
542 END IF;
543 /*--
544 --
545 -- debugging portion
546 -- can be commented ut for final code
547 IF inv_pp_debug.is_debug_mode THEN
548 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
549 END IF;
550 -- end of debugging section
551 -- */
552 -- Initialize API return status to success
553 x_return_status := fnd_api.g_ret_sts_success;
554
555 -- Initialize local input line counter
556 l_rule_counter := 0;
557
558 -- Initialize strategy members PL/SQL table
559 Wms_re_common_pvt.InitRulesTable;
560
561 -- Loop through strategy members
562 open rules;
563 while true loop
564 fetch rules into l_rule_id
565 ,l_partial_success_allowed_flag
566 ,g_over_allocation_mode
567 ,g_tolerance_value;
568 exit when rules%notfound;
569
570 -- create a new record in the rule table
571 Wms_re_common_pvt.InitRule (
572 l_rule_id
573 ,l_partial_success_allowed_flag
574 ,l_rule_counter
575 );
576 end loop;
577 close rules;
578
579 if l_rule_counter = 0 then
580 If (l_debug = 1) then
581 log_event(l_api_name, 'no_rules', 'No rules enabled for ' ||
582 'strategy ' || p_strategy_id);
583 End if;
584 raise no_data_found;
585 end if;
586 /*
587 --
588 -- debugging portion
589 -- can be commented ut for final code
590 IF inv_pp_debug.is_debug_mode THEN
591 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
592 END IF;
593 -- end of debugging section
594 -- */
595 If (l_debug = 1) then
596 log_procedure(l_api_name, 'end', 'End InitStrategyRules');
597 End if;
598 --g_debug := NULL;
599 exception
600 when others then
601 /* --
602 -- debugging portion
603 -- can be commented ut for final code
604 IF inv_pp_debug.is_debug_mode THEN
605 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
606 -- the message retrieved here since it is no longer on the stack
607 inv_pp_debug.set_last_error_message(Sqlerrm);
608 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
609 inv_pp_debug.send_last_error_message;
610 END IF;
611 -- end of debugging section
612 -- */
613 if rules%isopen then
614 close rules;
615 end if;
616 x_return_status := fnd_api.g_ret_sts_unexp_error;
617 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
618 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
619 end if;
620 fnd_msg_pub.count_and_get( p_count => x_msg_count
621 ,p_data => x_msg_data );
622
623 If (l_debug = 1) then
624 log_error(l_api_name, 'error', 'Error in InitStrategyRules - ' ||
625 x_msg_data);
626 End if;
627 --g_debug := NULL;
628
629 end InitStrategyRules;
630
631 -- Start of comments
632 -- Name : InitQtyTree
633 -- Function : Initializes quantity tree for picking and returns tree id.
634 -- Pre-reqs : none
635 -- Parameters :
636 -- x_return_status out varchar2(1)
637 -- x_msg_count out number
638 -- x_msg_data out varchar2(2000)
639 -- p_organization_id in number required
640 -- p_inventory_item_id in number required
641 -- p_transaction_source_type_id in number required
642 -- p_transaction_source_id in number required
643 -- p_trx_source_line_id in number required
644 -- p_trx_source_delivery_id in number required
645 -- p_transaction_source_name in varchar2 required
646 -- p_tree_mode in number required
647 -- x_tree_id out number
648 -- Notes : privat procedure for internal use only
649 -- End of comments
650
651 procedure InitQtyTree (
652 x_return_status out nocopy varchar2
653 ,x_msg_count out nocopy number
654 ,x_msg_data out nocopy varchar2
655 ,p_organization_id in number
656 ,p_inventory_item_id in number
657 ,p_transaction_source_type_id in number
658 ,p_transaction_source_id in number
659 ,p_trx_source_line_id in number
660 ,p_trx_source_delivery_id in number
661 ,p_transaction_source_name in varchar2
662 ,p_tree_mode in number
663 ,x_tree_id out nocopy number
664 ) is
665
666 l_api_name VARCHAR2(30) := 'InitQtyTree';
667 l_rev_control_code MTL_SYSTEM_ITEMS.REVISION_QTY_CONTROL_CODE%type;
668 l_lot_control_code MTL_SYSTEM_ITEMS.LOT_CONTROL_CODE%type;
669 l_ser_control_code MTL_SYSTEM_ITEMS.SERIAL_NUMBER_CONTROL_CODE%type;
670 l_is_revision_control boolean;
671 l_is_lot_control boolean;
672 l_is_serial_control boolean;
673 l_msg_data VARCHAR2(240);
674 l_transaction_source_id NUMBER;
675 l_trx_source_line_id NUMBER;
676 l_debug NUMBER;
677 cursor iteminfo is
678 select nvl(msi.REVISION_QTY_CONTROL_CODE,1)
679 ,nvl(msi.LOT_CONTROL_CODE,1)
680 ,nvl(msi.SERIAL_NUMBER_CONTROL_CODE,1)
681 from MTL_SYSTEM_ITEMS msi
682 where ORGANIZATION_ID = p_organization_id
683 and INVENTORY_ITEM_ID = p_inventory_item_id
684 ;
685 begin
686
687 IF (g_debug IS NULL) THEN
688 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
689 END IF;
690 l_debug := g_debug;
691 If (l_debug = 1) then
692 log_procedure(l_api_name, 'start', 'Start InitQtyTree');
693 End if;
694 /*--
695 -- debugging portion
696 -- can be commented ut for final code
697 IF inv_pp_debug.is_debug_mode THEN
698 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
699 END IF;
700 -- end of debugging section
701 -- */
702 open iteminfo;
703 fetch iteminfo into l_rev_control_code
704 ,l_lot_control_code
705 ,l_ser_control_code;
706 if iteminfo%notfound then
707 close iteminfo;
708 raise no_data_found;
709 end if;
710 close iteminfo;
711
712 if l_rev_control_code = 1 then
713 l_is_revision_control := false;
714 else
715 l_is_revision_control := true;
716 end if;
717 if l_lot_control_code = 1 then
718 l_is_lot_control := false;
719 else
720 l_is_lot_control := true;
721 end if;
722 if l_ser_control_code = 1 then
723 l_is_serial_control := false;
724 else
725 l_is_serial_control := true;
726 end if;
727
728 -- bug 2398927
729 --if source type id is 13 (inventory), don't pass in the demand
730 --source line and header info. This info was causing LPN putaway
731 -- to fall for unit effective items.
732 IF p_transaction_source_type_id IN (4,13) THEN
733 l_transaction_source_id := -9999;
734 l_trx_source_line_id := -9999;
735 ELSE
736 l_transaction_source_id := p_transaction_source_id;
737 l_trx_source_line_id := p_trx_source_line_id;
738 END IF;
739
740 If (l_debug = 1) then
741 log_event(l_api_name, 'create_tree',
742 'Trying to create quantity tree in exclusive mode');
743 End if;
744
745 INV_Quantity_Tree_PVT.Create_Tree
746 (
747 p_api_version_number => g_qty_tree_api_version
748 --,p_init_msg_list => fnd_api.g_false
749 ,x_return_status => x_return_status
750 ,x_msg_count => x_msg_count
751 ,x_msg_data => x_msg_data
752 ,p_organization_id => p_organization_id
753 ,p_inventory_item_id => p_inventory_item_id
754 ,p_tree_mode => p_tree_mode
755 ,p_is_revision_control => l_is_revision_control
756 ,p_is_lot_control => l_is_lot_control
757 ,p_is_serial_control => l_is_serial_control
758 ,p_asset_sub_only => FALSE
759 ,p_include_suggestion => TRUE
760 ,p_demand_source_type_id => p_transaction_source_type_id
761 ,p_demand_source_header_id => l_transaction_source_id
762 ,p_demand_source_line_id => l_trx_source_line_id
763 ,p_demand_source_name => p_transaction_source_name
764 ,p_demand_source_delivery => p_trx_source_delivery_id
765 ,p_lot_expiration_date => sysdate
766 ,p_onhand_source => inv_quantity_tree_pvt.g_all_subs
767 ,p_exclusive => inv_quantity_tree_pvt.g_exclusive
768 ,p_pick_release => inv_quantity_tree_pvt.g_pick_release_yes
769 ,x_tree_id => x_tree_id
770 );
771 --
772 If (l_debug = 1) then
773 log_event(l_api_name, 'create_tree_finished',
774 'Created quantity tree in exclusive mode');
775 End if;
776 /* -- debugging portion
777 -- can be commented ut for final code
778 IF inv_pp_debug.is_debug_mode THEN
779 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
780 END IF;
781 -- end of debugging section */
782 If (l_debug = 1) then
783 log_procedure(l_api_name, 'end', 'End InitQtyTree');
784 End if;
785 --
786 exception
787 when others then
788 /* --
789 -- debugging portion
790 -- can be commented ut for final code
791 IF inv_pp_debug.is_debug_mode THEN
792 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
793 -- the message retrieved here since it is no longer on the stack
794 inv_pp_debug.set_last_error_message(Sqlerrm);
795 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
796 inv_pp_debug.send_last_error_message;
797 END IF;
798 -- end of debugging section
799 -- */
800 if iteminfo%isopen then
801 close iteminfo;
802 end if;
803 x_return_status := fnd_api.g_ret_sts_unexp_error;
804 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
805 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
806 end if;
807 fnd_msg_pub.count_and_get( p_count => x_msg_count
808 ,p_data => x_msg_data );
809 If (l_debug = 1) then
810 log_error(l_api_name, 'error', 'Error in InitQtyTree - ' || x_msg_data);
811 End if;
812 end InitQtyTree;
813
814 -- Start of comments
815 -- Name : FreeGlobals
816 -- Function : Frees internal tables of strategy members and detailed input
817 -- records.
818 -- Pre-reqs : none
819 -- Parameters : none
820 -- Notes : privat procedure for internal use only
821 -- End of comments
822
823 procedure FreeGlobals is
824 l_api_name VARCHAR2(30) := 'FreeGlobals';
825 begin
826 /* --
827 -- debugging portion
828 -- can be commented ut for final code
829 IF inv_pp_debug.is_debug_mode THEN
830 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
831 END IF;
832 -- end of debugging section
833 -- */
834 Wms_re_common_pvt.InitInputTable;
835 Wms_re_common_pvt.InitRulesTable;
836 /* --
837 -- debugging portion
838 -- can be commented ut for final code
839 IF inv_pp_debug.is_debug_mode THEN
840 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
841 END IF;
842 -- end of debugging section
843 -- */
844 end FreeGlobals;
845
846 -- Start of comments
847 -- API name : Search
848 -- Type : Private
849 -- Function : Searches for a pick or put away strategy according to
850 -- provided transaction/reservation input and set up strategy
851 -- assignments to business objects.
852 -- Calls stub procedure to search for strategy assignments in a
853 -- customer-defined manner before actually following his own
854 -- algorithm to determine the valid strategy.
855 -- Pre-reqs : transaction record in WMS_STRATEGY_MAT_TXN_TMP_V uniquely
856 -- identified by parameters p_transaction_temp_id and
857 -- p_type_code ( base table MTL_MATERIAL_TRANSACTIONS_TEMP )
858 -- Parameters :
859 -- p_api_version in number required
860 -- p_init_msg_list in varchar2 optional default = fnd_api.g_false
861 -- p_validation_level in number optional default =
862 -- fnd_api.g_valid_level_full
863 -- x_return_status out varchar2(1)
864 -- x_msg_count out number
865 -- x_msg_data out varchar2(2000)
866 -- p_transaction_temp_id in number required default = NULL
867 -- p_type_code in number required default = NULL
868 -- x_strategy_id out number
869 -- Version : Current version 1.0
870 --
871 -- Changed ...
872 -- Previous version
873 --
874 -- Initial version 1.0
875 -- Notes : calls stub procedure WMS_re_Custom_PUB.SearchForStrategy
876 -- and API's of Wms_re_common_pvt
877 -- End of comments
878
879 procedure Search (
880 p_api_version in number
881 ,p_init_msg_list in varchar2 := fnd_api.g_false
882 ,p_validation_level in number := fnd_api.g_valid_level_full
883 ,x_return_status out NOCOPY varchar2
884 ,x_msg_count out NOCOPY number
885 ,x_msg_data out NOCOPY varchar2
886 ,p_transaction_temp_id in number := NULL
887 ,p_type_code in number := NULL
888 ,x_strategy_id out NOCOPY number
889 ,p_organization_id IN NUMBER DEFAULT NULL
890 ) is
891
892 -- API standard variables
893 l_api_version constant number := 1.0;
894 l_api_name constant varchar2(30) := 'Search';
895
896 -- variables needed for validation
897 l_dummy number;
898 l_hierarchy number;
899
900 -- variables needed for dynamic SQL
901 l_select long := null;
902 l_from long := null;
903 l_where long := null;
904 l_order_by long := null;
905 l_stmt long := null;
906 l_identifier varchar2(10);
907 l_cursor integer;
908 l_rows integer;
909
910 -- other variables
911 l_organization_id MTL_PARAMETERS.ORGANIZATION_ID%type;
912 l_object_id WMS_OBJECTS_B.OBJECT_ID%type;
913 l_strat_asgmt_db_object_id WMS_OBJECTS_B.STRAT_ASGMT_DB_OBJECT_ID%type;
914 l_db_object_id WMS_DB_OBJECTS.DB_OBJECT_ID%type;
915 l_table_name WMS_DB_OBJECTS.TABLE_NAME%type;
916 l_table_alias WMS_DB_OBJECTS.TABLE_ALIAS%type;
917 l_parent_table_alias WMS_DB_OBJECTS.TABLE_ALIAS%type;
918 l_parameter_type_code WMS_PARAMETERS_B.PARAMETER_TYPE_CODE%type;
919 l_column_name WMS_PARAMETERS_B.COLUMN_NAME%type;
920 l_expression WMS_PARAMETERS_B.EXPRESSION%type;
921 l_data_type_code WMS_PARAMETERS_B.DATA_TYPE_CODE%type;
922 l_parent_parameter_type_code WMS_PARAMETERS_B.PARAMETER_TYPE_CODE%type;
923 l_parent_column_name WMS_PARAMETERS_B.COLUMN_NAME%type;
924 l_parent_expression WMS_PARAMETERS_B.EXPRESSION%type;
925 l_parent_data_type_code WMS_PARAMETERS_B.DATA_TYPE_CODE%type;
926 l_left_part_conv_fct varchar2(100);
927 l_right_part_conv_fct varchar2(100);
928 l_search_type_code NUMBER;
929 l_join varchar2(400);
930 l_last_object_found BOOLEAN;
931 l_pk1_value VARCHAR2(150);
932 l_pk2_value VARCHAR2(150);
933 l_pk3_value VARCHAR2(150);
934 l_pk4_value VARCHAR2(150);
935 l_pk5_value VARCHAR2(150);
936 ---
937 --
938 --Bug # 2465807 / Grao - To handle SO for Cost Group Search Order (Item Type)
939
940 l_table_alias_left WMS_DB_OBJECTS.TABLE_ALIAS%type;
941
942 -- cursor for getting actual inventory org to search for strategy
943 cursor input is
944 select decode(p_type_code, 1, mpsmttv.TO_ORGANIZATION_ID,
945 mpsmttv.FROM_ORGANIZATION_ID) organization_id
946 from WMS_STRATEGY_MAT_TXN_TMP_V mpsmttv
947 where mpsmttv.LINE_ID = p_transaction_temp_id
948 and mpsmttv.TYPE_CODE = p_type_code;
949
950 --cursor for getting org for cost group search
951 cursor cg_org is
952 select organization_id
953 from wms_cost_groups_input_v wcgiv
954 where wcgiv.line_id = p_transaction_temp_id;
955
956
957 -- cursor for hierarchy of possible strategy assignments
958 -- use p_type_code, not l_search_type_code, since hierarchy is
959 -- defined by users using form, which doesn't show strat search types
960 cursor hierarchy is
961 select mpo.OBJECT_ID
962 ,mpo.STRAT_ASGMT_DB_OBJECT_ID
963 from WMS_OBJECTS_B mpo
964 ,WMS_ORG_HIERARCHY_OBJS mpoho
965 where mpoho.ORGANIZATION_ID = l_organization_id
966 and mpoho.TYPE_CODE = p_type_code
967 and mpo.OBJECT_ID = mpoho.OBJECT_ID
968 and mpo.STRAT_ASGMT_DB_OBJECT_ID is not null
969 and mpo.STRAT_ASGMT_LOV_SQL is not null
970 order by mpoho.SEARCH_ORDER;
971
972 -- cursor for all DB objects needed to build strategy searching dynamic SQL
973 cursor objects is
974 select mpdo.DB_OBJECT_ID
975 ,mpdo.TABLE_NAME
976 ,mpdo.TABLE_ALIAS
977 from WMS_DB_OBJECTS mpdo
978 where mpdo.db_object_id IN
979 (SELECT mpdo.db_object_id
980 FROM wms_db_objects mpdo
981 WHERE mpdo.db_object_id = l_strat_asgmt_db_object_id
982 UNION
983 SELECT mpdop.parent_db_object_id
984 FROM wms_db_objects_parents mpdop
985 WHERE mpdop.type_code = l_search_type_code
986 Connect by mpdop.DB_OBJECT_ID = prior mpdop.PARENT_DB_OBJECT_ID
987 Start with mpdop.DB_OBJECT_ID = l_strat_asgmt_db_object_id AND
988 mpdop.type_code = l_search_type_code );
989
990 -- cursor for join information regarding the actual and parent DB object
991 --Bug # 2465807 / Grao - To handle SO for Cost Group Search Order (Item Type)
992 -- Modified the cursor to get the parent table alias
993
994 cursor conditions is
995 select mpp.PARAMETER_TYPE_CODE
996 ,mpp.COLUMN_NAME
997 ,mpdop1.TABLE_ALIAS --- added for CG
998 ,mpp.EXPRESSION
999 ,mpp.DATA_TYPE_CODE
1000 ,mppp.PARAMETER_TYPE_CODE
1001 ,mppp.COLUMN_NAME
1002 ,mppp.EXPRESSION
1003 ,mppp.DATA_TYPE_CODE
1004 ,mpdop.TABLE_ALIAS -- alias n.a. for multi object based parameters
1005 from WMS_DB_OBJECTS mpdop
1006 ,WMS_DB_OBJECTS mpdop1 -- added for CG
1007 ,WMS_PARAMETERS_B mppp
1008 ,WMS_PARAMETERS_B mpp
1009 ,WMS_DB_OBJECT_JOINS mpdoj
1010 where mpdoj.DB_OBJECT_ID = l_db_object_id
1011 and mpdoj.type_code = l_search_type_code
1012 and mpp.PARAMETER_ID = mpdoj.PARAMETER_ID
1013 and mppp.PARAMETER_ID = mpdoj.PARENT_PARAMETER_ID
1014 and mpdop1.DB_OBJECT_ID = mpp.DB_OBJECT_ID --- Added for CG
1015 and mpdop.DB_OBJECT_ID (+) = mppp.DB_OBJECT_ID;
1016 --
1017 l_err VARCHAR2(240);
1018 l_pos NUMBER;
1019 l_strategy_id NUMBER;
1020 l_debug NUMBER;
1021
1022 BEGIN
1023 IF (g_debug IS NULL) THEN
1024 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1025 END IF;
1026 l_debug := g_debug;
1027 --
1028 If (l_debug = 1) then
1029 log_procedure(l_api_name, 'start', 'Start Search');
1030 End if;
1031 /*-- debugging portion
1032 -- can be commented ut for final code
1033 IF inv_pp_debug.is_debug_mode THEN
1034 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
1035 END IF;
1036 -- end of debugging section
1037 -- */
1038 -- Standard call to check for call compatibility
1039 if not fnd_api.compatible_api_call( l_api_version
1040 ,p_api_version
1041 ,l_api_name
1042 ,g_pkg_name ) then
1043 raise fnd_api.g_exc_unexpected_error;
1044 end if;
1045
1046 -- Initialize message list if p_init_msg_list is set to TRUE
1047 if fnd_api.to_boolean( p_init_msg_list ) then
1048 fnd_msg_pub.initialize;
1049 end if;
1050
1051 -- Initialize API return status to success
1052 x_return_status := fnd_api.g_ret_sts_success;
1053
1054 -- Validate input parameters and pre-requisites, if validation level
1055 -- requires this
1056 if p_validation_level <> fnd_api.g_valid_level_none then
1057 if p_transaction_temp_id is null or
1058 p_transaction_temp_id = fnd_api.g_miss_num then
1059 fnd_message.set_name('WMS','WMS_TRX_REQ_LINE_ID_MISS');
1060 fnd_msg_pub.add;
1061 If (l_debug = 1) then
1062 log_error_msg(l_api_name, 'missing_txn_temp_id');
1063 End if;
1064 raise fnd_api.g_exc_unexpected_error;
1065 end if;
1066 if p_type_code is null or
1067 p_type_code = fnd_api.g_miss_num then
1068 fnd_message.set_name('WMS','WMS_STRA_TYPE_CODE_MISS');
1069 fnd_msg_pub.add;
1070 If (l_debug = 1) then
1071 log_error_msg(l_api_name, 'missing_type_code');
1072 End if;
1073 raise fnd_api.g_exc_unexpected_error;
1074 end if;
1075 end if;
1076
1077 -- get actual inventory org to search for strategy
1078 -- ( and by the way, validate pre-requisites )
1079 IF p_organization_id IS NULL THEN
1080 If p_type_code = 5 Then --cost group engine
1081 open cg_org;
1082 fetch cg_org into l_organization_id;
1083 if cg_org%notfound then
1084 close cg_org;
1085 fnd_message.set_name('WMS','WMS_TRX_REQ_REC_NOTFOUND');
1086 fnd_msg_pub.add;
1087 If (l_debug = 1) then
1088 log_error_msg(l_api_name, 'missing_org_id_cg');
1089 End if;
1090 raise fnd_api.g_exc_unexpected_error;
1091 end if;
1092 close cg_org;
1093 Else -- pick/put strategy
1094 open input;
1095 fetch input into l_organization_id;
1096 if input%notfound then
1097 close input;
1098 fnd_message.set_name('WMS','WMS_TRX_REQ_REC_NOTFOUND');
1099 fnd_msg_pub.add;
1100 If (l_debug = 1) then
1101 log_error_msg(l_api_name, 'missing_org_id_pp');
1102 End if;
1103 raise fnd_api.g_exc_unexpected_error;
1104 end if;
1105 close input;
1106 End If;
1107 ELSE
1108 l_organization_id := p_organization_id;
1109 END IF;
1110
1111 -- Call custom-specific strategy search stub procedure
1112 wms_re_Custom_PUB.SearchForStrategy (
1113 p_init_msg_list
1114 ,x_return_status
1115 ,x_msg_count
1116 ,x_msg_data
1117 ,p_transaction_temp_id
1118 ,p_type_code
1119 ,l_strategy_id
1120 );
1121 -- leave the actual procedure, if stub procedure already found a strategy
1122 if x_return_status = fnd_api.g_ret_sts_success then
1123 If (l_debug = 1) then
1124 log_event(l_api_name, 'custom_search',
1125 'Strategy found using custom strategy search function. ' ||
1126 'Strategy: ' || l_strategy_id);
1127 End if;
1128 x_strategy_id := l_strategy_id;
1129 return;
1130 -- leave the actual procedure, if stub procedure got an unexpected error
1131 elsif x_return_status = fnd_api.g_ret_sts_unexp_error then
1132 raise fnd_api.g_exc_unexpected_error;
1133 -- continue strategy search, if stub procedure didn't find strategy already
1134 elsif x_return_status = fnd_api.g_ret_sts_error then
1135 -- Re-Initialize API return status to success
1136 x_return_status := fnd_api.g_ret_sts_success;
1137 -- every other return status seems to be unexpected: leave
1138 else
1139 fnd_message.set_name('WMS','WMS_INVALID_RETURN_STATUS');
1140 -- WMS_re_Custom_PUB.SearchForStrategy returned wrong status
1141 fnd_msg_pub.add;
1142 If (l_debug = 1) then
1143 log_error_msg(l_api_name, 'bad_return_status');
1144 End if;
1145 raise fnd_api.g_exc_unexpected_error;
1146 end if;
1147
1148 --two different type codes at work here
1149 --p_type_code tells us which engine is calling this procedure
1150 -- 1 or 2 means pick/put engine
1151 -- 5 means cost group engine
1152 --l_search_type_code is the type_code used in the rules engine
1153 -- data repository to indicate which objects should be used for
1154 -- building sql statements.
1155 -- 99 means pick/put strategy search
1156 -- 98 means pick/put cost group search
1157 --Here, we need to set the l_search_type_code based on the p_type_code
1158 IF p_type_code = 5 THEN --cost group
1159 l_search_type_code := 98;
1160 ELSE --p_type_code = 1 or 2 (pick/put)
1161 l_search_type_code := 99;
1162 END IF;
1163
1164 -- Loop through the hierarchy of possible strategy assignments
1165 l_hierarchy := 0;
1166 open hierarchy;
1167 while true loop
1168 fetch hierarchy into l_object_id
1169 ,l_strat_asgmt_db_object_id;
1170 exit when hierarchy%notfound;
1171 l_hierarchy := l_hierarchy + 1;
1172 If (g_debug = 1) then
1173 log_event(l_api_name, 'current_object',
1174 'Looking for strategy assigned to object_id ' || l_object_id);
1175 End if;
1176
1177 -- -------------------------------------------------------------------- --
1178 -- BUILD DYNAMIC SQL TO FIND STRATEGY --
1179 -- -------------------------------------------------------------------- --
1180
1181 -- Initialize variables for dynamically bound input parameters
1182 inv_sql_binding_pvt.InitBindTables;
1183
1184 -- Initialize 'where' and 'from' clause
1185 l_where := null;
1186 l_from := null;
1187 --indicates whether root of object tree was found
1188 l_last_object_found := FALSE;
1189
1190 -- loop through all the DB objects necessary to build the dynamic SQL
1191 open objects;
1192 while true loop
1193 fetch objects into l_db_object_id
1194 ,l_table_name
1195 ,l_table_alias;
1196 exit when objects%notfound;
1197
1198 -- Add DB object to 'from' clause
1199 if l_db_object_id = l_strat_asgmt_db_object_id then -- 1st record
1200 l_from := 'from '||l_table_name||' '||l_table_alias|| '
1201 '|| l_from;
1202 else
1203 l_from := l_from||','||l_table_name||' '||l_table_alias|| '
1204 ';
1205 end if;
1206
1207 -- Add static parts, when strategy assignment table arises
1208 if l_table_name = 'WMS_STRATEGY_ASSIGNMENTS' then
1209
1210 -- Initialize 'select' clause
1211 l_select := 'select '||l_table_alias||'.STRATEGY_ID'|| '
1212 ,' || l_table_alias || '.PK1_VALUE ' || '
1213 ,' || l_table_alias || '.PK2_VALUE ' || '
1214 ,' || l_table_alias || '.PK3_VALUE ' || '
1215 ,' || l_table_alias || '.PK4_VALUE ' || '
1216 ,' || l_table_alias || '.PK5_VALUE ' || '
1217 ';
1218 -- add organization id, to search for assignments set up within the
1219 -- actual organization only
1220 -- Bug 1736590 - Need to look for strategies that are common to
1221 -- all orgs; added -1
1222 l_identifier := inv_sql_binding_pvt.InitBindVar(l_organization_id);
1223 l_where := l_where||'and '||l_table_alias||
1224 '.ORGANIZATION_ID IN (' ||l_identifier|| ', -1)
1225 ';
1226
1227 -- add object id restriction
1228 l_identifier := inv_sql_binding_pvt.InitBindVar(l_object_id);
1229 l_where := l_where||'and '||l_table_alias||'.OBJECT_ID = '||
1230 l_identifier|| '
1231 ';
1232
1233 -- add type code restriction
1234 l_identifier := inv_sql_binding_pvt.InitBindVar(p_type_code);
1235 l_where := l_where||'and '||l_table_alias||
1236 '.STRATEGY_TYPE_CODE = '||l_identifier|| '
1237 ';
1238
1239 -- add effective date restrictions
1240 l_where := l_where
1241 ||'and wms_datecheck_pvt.date_valid( '
1242 ||l_table_alias||'.organization_id, '
1243 ||l_table_alias||'.date_type_code, '
1244 ||l_table_alias||'.date_type_from, '
1245 ||l_table_alias||'.date_type_to, '
1246 ||l_table_alias||'.effective_from, '
1247 ||l_table_alias||'.effective_to) = ''Y'' '
1248 ||' and (select wsbxyz.enabled_flag from wms_strategies_b wsbxyz where '
1249 ||l_table_alias||'.strategy_id = wsbxyz.strategy_id) = ''Y'' ';
1250
1251 --add order by for sequence number
1252 l_order_by := 'order by '
1253 || l_table_alias || '.SEQUENCE_NUMBER';
1254
1255 end if;
1256
1257 -- join last DB object with Key Identifiers
1258 if l_db_object_id = 1000 then
1259 l_identifier := inv_sql_binding_pvt.InitBindVar(p_type_code);
1260 l_where :='and '||l_table_alias||'.TYPE_CODE = '||l_identifier||'
1261 '||l_where;
1262 l_identifier:=inv_sql_binding_pvt.InitBindVar(p_transaction_temp_id);
1263 l_where := 'where '||l_table_alias||'.LINE_ID = '||
1264 l_identifier||'
1265 '||l_where;
1266 l_last_object_found := TRUE;
1267 -- for cost groups
1268 elsif l_db_object_id = 40 then -- wms_cost_group_input_v
1269 l_identifier := inv_sql_binding_pvt.InitBindVar(p_transaction_temp_id);
1270 l_where := 'where '||l_table_alias||'.LINE_ID = '||
1271 l_identifier||'
1272 '||l_where;
1273 l_last_object_found := TRUE;
1274 end if;
1275 -- loop through all the join conditions
1276 open conditions;
1277 while true loop
1278 fetch conditions into l_parameter_type_code
1279 ,l_column_name
1280 ,l_table_alias_left --- Added for CG
1281 ,l_expression
1282 ,l_data_type_code
1283 ,l_parent_parameter_type_code
1284 ,l_parent_column_name
1285 ,l_parent_expression
1286 ,l_parent_data_type_code
1287 ,l_parent_table_alias;
1288 exit when conditions%notfound;
1289
1290 -- find out, if data type conversion is needed
1291 inv_sql_binding_pvt.GetConversionString ( l_data_type_code
1292 ,l_parent_data_type_code
1293 ,l_left_part_conv_fct
1294 ,l_right_part_conv_fct );
1295
1296 -- add join conditions to 'where' clause ( in backward order )
1297 l_join := l_right_part_conv_fct||'
1298 ';
1299 if l_parent_parameter_type_code = 1 then
1300 l_join := l_parent_table_alias||'.'||l_parent_column_name
1301 ||l_join;
1302 else
1303 l_join := l_parent_expression||l_join;
1304 end if;
1305 l_join := ' = '||l_left_part_conv_fct||l_join;
1306 if l_parameter_type_code = 1 then
1307 if (l_search_type_code = 98) then --- Added for CG
1308 l_join := l_table_alias_left||'.'||l_column_name||l_join;
1309 else
1310 l_join := l_table_alias||'.'||l_column_name||l_join;
1311 end if;
1312 elsif l_parameter_type_code = 2 then
1313 l_join := l_expression||l_join;
1314 end if;
1315 l_where := l_where || 'and '|| l_join;
1316 end loop;
1317 close conditions;
1318 end loop;
1319 close objects;
1320 if l_last_object_found = FALSE then
1321 close hierarchy;
1322 fnd_message.set_name('WMS','WMS_DB_OBJECT_CHAIN');
1323 -- Seed data corrupted: DB object chain
1324 fnd_msg_pub.add;
1325 If (g_debug = 1) then
1326 log_error_msg(l_api_name, 'bad_db_object_chain');
1327 End if;
1328 raise fnd_api.g_exc_unexpected_error;
1329 end if;
1330
1331 -- ----------------------------------------------------------------
1332 -- EXECUTE DYNAMIC SQL TO FIND STRATEGY
1333 -- ----------------------------------------------------------------
1334 -- 1st step: assemble the SQL statement
1335 -- remark: ordering of records is not necessary, because one effective
1336 -- strategy of one type ( pick OR put away )is allowed per object
1337 -- only !
1338 l_stmt := l_select || l_from || l_where || l_order_by;
1339 --inv_pp_debug.send_long_to_pipe(l_stmt);
1340 If (g_debug = 1) then
1341 log_event(l_api_name, 'Dynamic SQL STMT for Stg Search Order', l_stmt);
1342 End if;
1343
1344 --Wms_re_common_pvt.ShowSQL(l_stmt);
1345 inv_sql_binding_pvt.ShowBindVars;
1346
1347 -- 2nd step: get a cursor and parse the SQL statement
1348 l_cursor := dbms_sql.open_cursor;
1349 dbms_sql.parse( l_cursor, l_stmt, dbms_sql.native );
1350
1351 -- 3rd step: bind input variables
1352 inv_sql_binding_pvt.BindVars(l_cursor);
1353
1354 -- 4th step: define output column
1355 dbms_sql.define_column(l_cursor, 1, l_strategy_id);
1356 dbms_sql.define_column(l_cursor, 2, l_pk1_value,150);
1357 dbms_sql.define_column(l_cursor, 3, l_pk2_value,150);
1358 dbms_sql.define_column(l_cursor, 4, l_pk3_value,150);
1359 dbms_sql.define_column(l_cursor, 5, l_pk4_value,150);
1360 dbms_sql.define_column(l_cursor, 6, l_pk5_value,150);
1361
1362 -- 5th step: execute the SQL statement and fetch one record
1363 l_rows := dbms_sql.execute_and_fetch(l_cursor, false);
1364 if l_rows = 0 then
1365 l_strategy_id := null;
1366 else
1367 dbms_sql.column_value(l_cursor, 1, l_strategy_id);
1368 dbms_sql.column_value(l_cursor, 2, l_pk1_value);
1369 dbms_sql.column_value(l_cursor, 3, l_pk2_value);
1370 dbms_sql.column_value(l_cursor, 4, l_pk3_value);
1371 dbms_sql.column_value(l_cursor, 5, l_pk4_value);
1372 dbms_sql.column_value(l_cursor, 6, l_pk5_value);
1373 end if;
1374
1375 -- 6th step: finally, close dynamic cursor
1376 dbms_sql.close_cursor(l_cursor);
1377
1378 -- interrupt the search, if a strategy was found
1379 exit when l_strategy_id is not null;
1380 If (g_debug = 1) then
1381 log_event(l_api_name, 'no_strat_found',
1382 'No strategy found for this object');
1383 End if;
1384 end loop;
1385 close hierarchy;
1386
1387 --commenting out exception calls; if strategy can't be found,
1388 -- we don't want to raise error. calling function will have to
1389 -- use some sort of default. We should create a log message, though.
1390 if l_hierarchy = 0 then
1391 fnd_message.set_name('WMS','WMS_SEARCH_ORDER_EMPTY');
1392 --inv_pp_debug.send_message_to_pipe('no search order defined for this org');
1393 -- Strategy search object hierarchy contains no entry
1394 --fnd_msg_pub.add;
1395 If (g_debug = 1) then
1396 log_event(l_api_name, 'no_hierarchy',
1397 'No strategy search order defined for this organization');
1398 End if;
1399 --raise fnd_api.g_exc_error;
1400 end if;
1401 if l_strategy_id is null then
1402 fnd_message.set_name('WMS','WMS_NO_STRATEGY_ASSIGN');
1403 -- inv_pp_debug.send_message_to_pipe('no strategy assigned');
1404 --No active strategy assignment detected according to provided input
1405 --fnd_msg_pub.add;
1406 If (g_debug = 1) then
1407 log_event(l_api_name, 'no_strategy',
1408 'The strategy search function failed to find a valid ' ||
1409 'strategy.');
1410 End if;
1411 --raise fnd_api.g_exc_error;
1412 IF p_type_code = 1 THEN
1413 wms_search_order_globals_pvt.g_putaway_strategy_id := -999;
1414 ELSIF p_type_code = 2 THEN
1415 wms_search_order_globals_pvt.g_pick_strategy_id := -999;
1416 ELSIF p_type_code = 5 THEN
1417 wms_search_order_globals_pvt.g_costgroup_strategy_id := -999;
1418 END IF;
1419 else
1420 If (g_debug = 1) then
1421 log_event(l_api_name, 'strategy_found',
1422 'The strategy search function found a valid strategy. ' ||
1423 'Strategy : '|| l_strategy_id);
1424 End if;
1425 -- Calls to populate globals used by Simulation form
1426 wms_engine_pvt.g_business_object_id := l_object_id;
1427 IF p_type_code = 1 THEN
1428 wms_search_order_globals_pvt.g_putaway_business_object_id := l_object_id;
1429 wms_search_order_globals_pvt.g_putaway_strategy_id := l_strategy_id;
1430 wms_search_order_globals_pvt.g_putaway_pk1_value:= l_pk1_value;
1431 wms_search_order_globals_pvt.g_putaway_pk2_value:= l_pk2_value;
1432 wms_search_order_globals_pvt.g_putaway_pk3_value:= l_pk3_value;
1433 wms_search_order_globals_pvt.g_putaway_pk4_value:= l_pk4_value;
1434 wms_search_order_globals_pvt.g_putaway_pk5_value:= l_pk5_value;
1435 ELSIF p_type_code = 2 THEN
1436 wms_search_order_globals_pvt.g_pick_business_object_id := l_object_id;
1437 wms_search_order_globals_pvt.g_pick_strategy_id := l_strategy_id;
1438 wms_search_order_globals_pvt.g_pick_pk1_value:= l_pk1_value;
1439 wms_search_order_globals_pvt.g_pick_pk2_value:= l_pk2_value;
1440 wms_search_order_globals_pvt.g_pick_pk3_value:= l_pk3_value;
1441 wms_search_order_globals_pvt.g_pick_pk4_value:= l_pk4_value;
1442 wms_search_order_globals_pvt.g_pick_pk5_value:= l_pk5_value;
1443 ELSIF p_type_code = 5 THEN
1444 wms_search_order_globals_pvt.g_costgroup_business_object_id := l_object_id;
1445 wms_search_order_globals_pvt.g_costgroup_strategy_id := l_strategy_id;
1446 wms_search_order_globals_pvt.g_costgroup_pk1_value:= l_pk1_value;
1447 wms_search_order_globals_pvt.g_costgroup_pk2_value:= l_pk2_value;
1448 wms_search_order_globals_pvt.g_costgroup_pk3_value:= l_pk3_value;
1449 wms_search_order_globals_pvt.g_costgroup_pk4_value:= l_pk4_value;
1450 wms_search_order_globals_pvt.g_costgroup_pk5_value:= l_pk5_value;
1451 END IF;
1452 end if;
1453 x_strategy_id := l_strategy_id;
1454 -- Clean up variables for dynamically bound input parameters
1455 inv_sql_binding_pvt.InitBindTables;
1456
1457 -- Standard call to get message count and if count is 1, get message info
1458 fnd_msg_pub.count_and_get( p_count => x_msg_count
1459 ,p_data => x_msg_data );
1460 IF inv_pp_debug.is_debug_mode THEN
1461 inv_pp_debug.send_message_to_pipe('strategy id found '||l_strategy_id);
1462 END IF;
1463 --
1464 -- debugging portion
1465 -- can be commented ut for final code
1466 IF inv_pp_debug.is_debug_mode THEN
1467 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
1468 END IF;
1469 -- end of debugging section
1470 If (g_debug = 1) then
1471 log_procedure(l_api_name, 'end', 'End Search');
1472 End if;
1473
1474 g_debug := NULL;
1475 --
1476 exception
1477 when fnd_api.g_exc_error then
1478 --
1479 -- debugging portion
1480 -- can be commented ut for final code
1481 IF inv_pp_debug.is_debug_mode THEN
1482 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
1483 -- the message retrieved here since it is no longer on the stack
1484 inv_pp_debug.set_last_error_message(Sqlerrm);
1485 inv_pp_debug.set_last_error_position(dbms_sql.last_error_position);
1486 --changed by jcearley on 11/22/99 because call was causing error
1487 -- inv_pp_debug.set_last_dynamic_sql(l_stmt);
1488 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
1489 inv_pp_debug.send_last_error_message;
1490 -- inv_pp_debug.send_last_dynamic_sql;
1491 inv_pp_debug.send_last_error_position;
1492 END IF;
1493 -- end of debugging section
1494 --
1495 inv_sql_binding_pvt.InitBindTables;
1496 x_return_status := fnd_api.g_ret_sts_error;
1497 fnd_msg_pub.count_and_get( p_count => x_msg_count
1498 ,p_data => x_msg_data );
1499 If (g_debug = 1) then
1500 log_error(l_api_name, 'error', 'Error in Search - ' || x_msg_data);
1501 End if;
1502
1503 g_debug := NULL;
1504
1505 when fnd_api.g_exc_unexpected_error then
1506 --
1507 -- debugging portion
1508 -- can be commented ut for final code
1509 IF inv_pp_debug.is_debug_mode THEN
1510 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
1511 -- the message retrieved here since it is no longer on the stack
1512 inv_pp_debug.set_last_error_message(Sqlerrm);
1513 inv_pp_debug.set_last_error_position(dbms_sql.last_error_position);
1514 -- inv_pp_debug.set_last_dynamic_sql(l_stmt);
1515 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
1516 inv_pp_debug.send_last_error_message;
1517 -- inv_pp_debug.send_last_dynamic_sql;
1518 inv_pp_debug.send_last_error_position;
1519 END IF;
1520 -- end of debugging section
1521 --
1522 inv_sql_binding_pvt.InitBindTables;
1523 x_return_status := fnd_api.g_ret_sts_unexp_error;
1524 fnd_msg_pub.count_and_get( p_count => x_msg_count
1525 ,p_data => x_msg_data );
1526 If (g_debug = 1) then
1527 log_error(l_api_name, 'unexp_error',
1528 'Unexpected error in Search - ' || x_msg_data);
1529 End if;
1530 g_debug := NULL;
1531
1532 when others then
1533 --
1534 -- debugging portion
1535 -- can be commented ut for final code
1536 IF inv_pp_debug.is_debug_mode THEN
1537 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
1538 -- the message retrieved here since it is no longer on the stack
1539 inv_pp_debug.set_last_error_message(Sqlerrm);
1540 inv_pp_debug.set_last_error_position(dbms_sql.last_error_position);
1541 -- inv_pp_debug.set_last_dynamic_sql(l_stmt);
1542 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
1543 inv_pp_debug.send_last_error_message;
1544 -- inv_pp_debug.send_last_dynamic_sql;
1545 inv_pp_debug.send_last_error_position;
1546 END IF;
1547 -- end of debugging section
1548 --
1549 inv_sql_binding_pvt.InitBindTables;
1550 if input%isopen then
1551 close input;
1552 end if;
1553 if hierarchy%isopen then
1554 close hierarchy;
1555 end if;
1556 if objects%isopen then
1557 close objects;
1558 end if;
1559 if conditions%isopen then
1560 close conditions;
1561 end if;
1562 x_return_status := fnd_api.g_ret_sts_unexp_error;
1563 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
1564 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1565 end if;
1566 fnd_msg_pub.count_and_get( p_count => x_msg_count
1567 ,p_data => x_msg_data );
1568 If (g_debug = 1) then
1569 log_error(l_api_name, 'other_error',
1570 'Other error in Search - ' || x_msg_data);
1571 End if;
1572 g_debug := NULL;
1573 end Search;
1574
1575 -- Start of comments
1576 -- Name : Get_Max_Tolerance
1577 -- Function : Gets the Max Tolerance value as well as sets the Min Qty that can be allocated against MO Line
1578 -- Pre-reqs : none
1579 -- Parameters :
1580 -- p_transaction_temp_id in number
1581 -- p_organization_id in number
1582 -- p_inventory_item_id in number
1583 -- p_trx_source_line_id in number
1584 -- x_return_status out varchar2(1)
1585 -- x_msg_count out number
1586 -- x_msg_data out varchar2(2000)
1587 -- Notes : private procedure for internal use only
1588 -- End of comments
1589 PROCEDURE get_max_tolerance(
1590 p_transaction_temp_id IN NUMBER
1591 , p_organization_id IN NUMBER
1592 , p_inventory_item_id IN NUMBER
1593 , p_trx_source_line_id IN NUMBER
1594 , x_max_tolerance OUT NOCOPY NUMBER
1595 , x_return_status OUT NOCOPY VARCHAR2
1596 , x_msg_count OUT NOCOPY NUMBER
1597 , x_msg_data OUT NOCOPY NUMBER
1598 ) IS
1599
1600 l_return_status VARCHAR2(1);
1601 l_api_name CONSTANT VARCHAR2(30) := 'Get_Max_Tolerance';
1602 l_debug NUMBER := g_debug;
1603 l_allowed_flag VARCHAR2(1);
1604 l_mo_quantity NUMBER;
1605 l_quantity_to_pick NUMBER;
1606 l_max_quantity NUMBER;
1607 l_other_alloc NUMBER;
1608 l_cur_mo_alloc NUMBER;
1609 l_max_possible_qty NUMBER;
1610 l_max_tolerance NUMBER;
1611 l_avail_req_qty NUMBER;
1612
1613 BEGIN
1614 IF (l_debug = 1) THEN
1615 log_statement(l_api_name, 'Entering get_max_tolerance', '-------------------');
1616 END IF;
1617 l_return_status := FND_API.G_RET_STS_SUCCESS;
1618 l_mo_quantity := WMS_Engine_PVT.g_mo_quantity;
1619
1620 -- l_other_alloc is sum of allocation for all MO except current MO, with same p_trx_source_line_id
1621 SELECT NVL(SUM(transaction_quantity), 0)
1622 INTO l_other_alloc
1623 FROM mtl_material_transactions_temp
1624 WHERE move_order_line_id <> p_transaction_temp_id
1625 AND organization_id = p_organization_id
1626 AND inventory_item_id = p_inventory_item_id
1627 AND transaction_action_id = 28
1628 AND trx_source_line_id = p_trx_source_line_id;
1629
1630 -- l_quantity_to_pick is required by 'wsh_details_validations.check_quantity_to_pick' to decide if any more
1631 -- allocation is allowed or not and will return value of l_allowed_flag. This is not being used currently here.
1632 l_quantity_to_pick := l_other_alloc + l_mo_quantity;
1633
1634 /* Shipping API returns the following:
1635 l_max_quantity: Maximum quantity(including order line tolerance) that can be staged (e.g. 110 - already staged)
1636 l_avail_req_qty: Minimum quantity remaining to be staged to satify the actual sales order line quantity
1637 (e.g. 100 - already staged) */
1638 wsh_details_validations.check_quantity_to_pick
1639 ( p_order_line_id => p_trx_source_line_id
1640 , p_quantity_to_pick => l_quantity_to_pick
1641 , x_allowed_flag => l_allowed_flag
1642 , x_max_quantity_allowed => l_max_quantity
1643 , x_return_status => l_return_status
1644 , x_avail_req_quantity => l_avail_req_qty );
1645 IF (l_debug = 1) THEN
1646 log_statement(l_api_name, 'Return status from check_quantity_to_pick = ', l_return_status);
1647 log_statement(l_api_name, 'l_max_quantity ' , l_max_quantity);
1648 log_statement(l_api_name, 'l_avail_req_qty ', l_avail_req_qty);
1649 END IF;
1650 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1651 RAISE FND_API.G_EXC_ERROR;
1652 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1653 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1654 END IF;
1655
1656 -- l_cur_mo_alloc is any existing allocation for the current MO
1657 SELECT NVL(SUM(transaction_quantity), 0)
1658 INTO l_cur_mo_alloc
1659 FROM mtl_material_transactions_temp
1660 WHERE move_order_line_id = p_transaction_temp_id;
1661
1662 -- l_max_possible_qty is maximum quantity(including tolerance) that can be allocated
1663 -- l_other_alloc should never exceed l_max_quantity, however to be safe putting GREATEST
1664 l_max_possible_qty := GREATEST(0, l_max_quantity - NVL(l_other_alloc,0));
1665
1666 -- WMS_RULE_PVT.g_min_qty_to_allocate is minimum quantity remaining to be allocated to satisfy sales order line
1667 -- quantity. Loop in Rules will try to allocate till this or current MO quantity whichever is less and will
1668 -- exit if this quantity is allocated
1669 WMS_RULE_PVT.g_min_qty_to_allocate := GREATEST(0,l_avail_req_qty - NVL(l_other_alloc,0) - NVL(l_cur_mo_alloc, 0));
1670
1671 -- l_max_tolerance is tolerance value for the current MO, however it will be set to zero if it is non-negative
1672 -- and Rule allocation mode does not allows it
1673 l_max_tolerance := l_max_possible_qty - l_mo_quantity;
1674
1675 IF (l_debug = 1) THEN
1676 log_statement(l_api_name, 'l_mo_quantity ', l_mo_quantity);
1677 log_statement(l_api_name, 'l_other_alloc ', l_other_alloc);
1678 log_statement(l_api_name, 'l_cur_mo_alloc ', l_cur_mo_alloc);
1679 log_statement(l_api_name, 'l_max_possible_qty ', l_max_possible_qty);
1680 log_statement(l_api_name, 'g_min_qty_to_allocate ', WMS_RULE_PVT.g_min_qty_to_allocate);
1681 log_statement(l_api_name, 'l_max_tolerance ', l_max_tolerance);
1682 END IF;
1683
1684 x_return_status := l_return_status;
1685 x_max_tolerance := l_max_tolerance;
1686
1687 IF (l_debug = 1) THEN
1688 log_statement(l_api_name, 'Exiting get_max_tolerance', '--------------------');
1689 END IF;
1690 EXCEPTION
1691 WHEN FND_API.G_EXC_ERROR THEN
1692 x_return_status := FND_API.G_RET_STS_ERROR;
1693 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1694 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1695 x_return_status := FND_API.G_RET_STS_ERROR;
1696 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1697 WHEN OTHERS THEN
1698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1699 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1700 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1701 END IF;
1702 END get_max_tolerance;
1703
1704 -- Start of comments
1705 -- API name : Apply
1706 -- Type : Private
1707 -- Function : Applies a pick or put away strategy to the given transaction
1708 -- or reservation input parameters and creates recommendations
1709 -- Pre-reqs : transaction record in WMS_STRATEGY_MAT_TXN_TMP_V uniquely
1710 -- identified by parameters p_transaction_temp_id and
1711 -- p_type_code ( base table MTL_MATERIAL_TRANSACTIONS_TEMP )
1712 -- at least one transaction detail record in
1713 -- WMS_TRX_DETAILS_TMP_V identified by line type code = 1
1714 -- and parameters p_transaction_temp_id and p_type_code
1715 -- ( base tables MTL_MATERIAL_TRANSACTIONS_TEMP and
1716 -- WMS_TRANSACTIONS_TEMP )
1717 -- strategy record in WMS_STRATEGIES_B uniquely identified by
1718 -- parameter p_strategy_id
1719 -- at least one strategy member record in
1720 -- WMS_STRATEGY_MEMBERS identified by parameter
1721 -- p_strategy_id
1722 -- Parameters :
1723 -- p_api_version in number required
1724 -- p_init_msg_list in varchar2 optional default = fnd_api.g_false
1725 -- p_commit in varchar2 optional default = fnd_api.g_false
1726 -- p_validation_level in number optional default =
1727 -- fnd_api.g_valid_level_full
1728 -- x_return_status out varchar2(1)
1729 -- x_msg_count out number
1730 -- x_msg_data out varchar2(2000)
1731 -- p_transaction_temp_id in number required default = NULL
1732 -- p_type_code in number required default = NULL
1733 -- p_strategy_id in number required default = NULL
1734 -- ,p_quick_pick_flag in varchar2 default 'N' The other value are 'Y' and 'Q'
1735 -- 'Y' is passed in patchset 'J' onwards for Inventory Moves
1736 -- when the lpn_request_context is 1 and
1737 -- 'Q' is added to enable the functionality in 11.5.9 /'I'
1738 -- Version : Current version 1.0
1739 --
1740 -- Changed ...
1741 -- Previous version
1742 --
1743 -- Initial version 1.0
1744 -- Notes : calls API's of Wms_re_common_pvt, WMS_Rule_PVT
1745 -- and INV_Quantity_Tree_PVT
1746 -- This API must be called internally by
1747 -- WMS_Engine_PVT.Create_Suggestions only !
1748 -- End of comments
1749
1750 procedure Apply (
1751 p_api_version in number
1752 ,p_init_msg_list in varchar2 := fnd_api.g_false
1753 ,p_commit in varchar2 := fnd_api.g_false
1754 ,p_validation_level in number := fnd_api.g_valid_level_full
1755 ,x_return_status out NOCOPY varchar2
1756 ,x_msg_count out NOCOPY number
1757 ,x_msg_data out NOCOPY varchar2
1758 ,p_transaction_temp_id in number := NULL
1759 ,p_type_code in number := NULL
1760 ,p_strategy_id in number := NULL
1761 ,p_rule_id in number := NULL -- [ Added new column p_rule_id ]
1762 ,p_detail_serial in BOOLEAN DEFAULT FALSE
1763 ,p_from_serial IN VARCHAR2 DEFAULT NULL
1764 ,p_to_serial IN VARCHAR2 DEFAULT NULL
1765 ,p_detail_any_serial IN NUMBER DEFAULT NULL
1766 ,p_unit_volume IN NUMBER DEFAULT NULL
1767 ,p_volume_uom_code IN VARCHAR2 DEFAULT NULL
1768 ,p_unit_weight IN NUMBER DEFAULT NULL
1769 ,p_weight_uom_code IN VARCHAR2 DEFAULT NULL
1770 ,p_base_uom_code IN VARCHAR2 DEFAULT NULL
1771 ,p_lpn_id IN NUMBER DEFAULT NULL
1772 ,p_unit_number IN VARCHAR2 DEFAULT NULL
1773 ,p_allow_non_partial_rules IN BOOLEAN DEFAULT TRUE
1774 ,p_simulation_mode IN NUMBER DEFAULT 0
1775 ,p_simulation_id IN NUMBER DEFAULT NULL
1776 ,p_project_id IN NUMBER DEFAULT NULL
1777 ,p_task_id IN NUMBER DEFAULT NULL
1778 ,p_quick_pick_flag IN VARCHAR2 DEFAULT 'N'
1779 ) is
1780
1781 -- API standard variables
1782 l_api_version constant number := 1.0;
1783 l_api_name constant varchar2(30) := 'Apply';
1784
1785 l_organization_id MTL_MATERIAL_TRANSACTIONS_TEMP.ORGANIZATION_ID%type;
1786 l_inventory_item_id MTL_MATERIAL_TRANSACTIONS_TEMP.INVENTORY_ITEM_ID%type;
1787 l_line_type_code WMS_TRANSACTIONS_TEMP.LINE_TYPE_CODE%type;
1788 l_transaction_uom MTL_MATERIAL_TRANSACTIONS_TEMP.TRANSACTION_UOM%type;
1789 l_primary_uom MTL_SYSTEM_ITEMS.PRIMARY_UOM_CODE%type;
1790 l_secondary_uom VARCHAR2(3) ; -- MTL_SYSTEM_ITEMS.SECONDARY_UOM_CODE%type;
1791 l_grade_code VARCHAR2(150);
1792 l_transaction_source_type_id
1793 MTL_MATERIAL_TRANSACTIONS_TEMP.TRANSACTION_SOURCE_TYPE_ID%type;
1794 l_transaction_source_id
1795 MTL_MATERIAL_TRANSACTIONS_TEMP.TRANSACTION_SOURCE_ID%type;
1796 l_trx_source_line_id
1797 MTL_MATERIAL_TRANSACTIONS_TEMP.TRX_SOURCE_LINE_ID%type;
1798 l_trx_source_delivery_id
1799 MTL_MATERIAL_TRANSACTIONS_TEMP.TRX_SOURCE_DELIVERY_ID%type;
1800 l_transaction_source_name
1801 MTL_MATERIAL_TRANSACTIONS_TEMP.TRANSACTION_SOURCE_NAME%type;
1802 l_transaction_type_id
1803 MTL_MATERIAL_TRANSACTIONS_TEMP.TRANSACTION_TYPE_ID%type;
1804
1805 l_rule_id WMS_STRATEGY_MEMBERS.RULE_ID%type;
1806 l_partial_success_allowed_flag
1807 WMS_STRATEGY_MEMBERS.PARTIAL_SUCCESS_ALLOWED_FLAG%type;
1808 l_finished VARCHAR2(1);
1809 l_tree_mode NUMBER;
1810 l_tree_id NUMBER;
1811 l_msg_data VARCHAR2(240);
1812 l_msg_count NUMBER;
1813 l_skip_rule VARCHAR2(1);
1814 l_simulation_mode NUMBER;
1815 l_allow_non_partial_rules BOOLEAN;
1816
1817 --Aded bug3237702 caching
1818 l_req_locator_id NUMBER;
1819 l_req_subinventory_code MTL_MATERIAL_TRANSACTIONS_TEMP.SUBINVENTORY_CODE%type;
1820 is_pickrelease BOOLEAN;
1821 --Bug 3237702 ends
1822
1823 -- Rules J Project Variables
1824 --
1825
1826 l_current_release_level NUMBER := WMS_UI_TASKS_APIS.G_WMS_PATCH_LEVEL;
1827 l_j_release_level NUMBER := WMS_UI_TASKS_APIS.G_PATCHSET_J;
1828 l_quick_pick_flag VARCHAR2(1); -- 'J Project:This variable is used for QuickPick during Inventory Move
1829 -- Values 'Y' - Perform Quick Pick ,
1830 -- 'N' - Do not call quick Pick
1831 -- 'Q' - Perform Quick Pick for patset 'I' / Without Qty_tree validation
1832 -- for performance reasons
1833 l_qty_tree_allowed VARCHAR2(1) := 'Y' ;
1834 l_debug NUMBER ;
1835
1836 -- [ Lot Indivisable Var
1837 l_lot_divisible_flag VARCHAR2(1);
1838 l_lot_control_code NUMBER;
1839 l_indiv_lot_allowed VARCHAR2(1); -- [ Added ]
1840 l_max_tolerance NUMBER;
1841 l_min_tolerance NUMBER;
1842
1843 --]
1844
1845 -- [ 3.1.4 Inventory Allocation - Skipping Put away rules ]
1846 l_wms_installed BOOLEAN;
1847 l_wms_enabled_flag VARCHAR2(1);
1848 l_over_allocation_mode NUMBER;
1849 l_tolerance_value NUMBER;
1850
1851 CURSOR c_output_exists IS
1852 SELECT 'Y'
1853 FROM DUAL
1854 WHERE EXISTS(
1855 SELECT PP_TRANSACTION_TEMP_ID
1856 FROM WMS_TRANSACTIONS_TEMP
1857 WHERE TYPE_CODE = p_type_code
1858 AND LINE_TYPE_CODE = 2);
1859
1860 begin
1861 IF (g_debug IS NULL) THEN
1862 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1863 END IF;
1864 l_debug := g_debug;
1865 l_wms_installed := INV_CACHE.wms_installed;
1866
1867
1868 IF p_quick_pick_flag = 'Q' THEN
1869 l_qty_tree_allowed := 'N' ;
1870 ELSE
1871 l_qty_tree_allowed := 'Y' ;
1872 END IF;
1873 --
1874 If (l_debug = 1) then
1875 log_procedure(l_api_name, 'start', 'Start wms_strategy_pvt.Apply');
1876 End if;
1877 -- debugging portion
1878 -- can be commented ut for final code
1879
1880 -- Bug 2286454
1881 l_allow_non_partial_rules := p_allow_non_partial_rules;
1882
1883 IF inv_pp_debug.is_debug_mode THEN
1884 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
1885 END IF;
1886 -- end of debugging section
1887 --
1888 -- Standard start of API savepoint
1889 savepoint ApplyStrategySP;
1890
1891 -- Standard call to check for call compatibility
1892 if not fnd_api.compatible_api_call( l_api_version
1893 ,p_api_version
1894 ,l_api_name
1895 ,g_pkg_name ) then
1896 raise fnd_api.g_exc_unexpected_error;
1897 end if;
1898
1899 -- Initialize message list if p_init_msg_list is set to TRUE
1900 if fnd_api.to_boolean( p_init_msg_list ) then
1901 fnd_msg_pub.initialize;
1902 end if;
1903
1904 -- Initialize API return status to success
1905 x_return_status := fnd_api.g_ret_sts_success;
1906
1907 -- Initialize functional return status to 'missing'
1908 --l_finished := fnd_api.g_miss_char; --bug 3673962
1909 l_finished := fnd_api.g_false; --bug3673962
1910
1911 -- Validate input parameters and pre-requisites, if validation level
1912 -- requires this
1913 if p_validation_level <> fnd_api.g_valid_level_none then
1914 if p_transaction_temp_id is null or
1915 p_transaction_temp_id = fnd_api.g_miss_num
1916 then
1917 fnd_message.set_name('WMS','WMS_TRX_REQ_LINE_ID_MISS');
1918 -- Transaction input identifier required but not provided
1919 fnd_msg_pub.add;
1920 If (l_debug = 1) then
1921 log_error_msg(l_api_name,'missing_txn_temp_id');
1922 End if;
1923 raise fnd_api.g_exc_unexpected_error;
1924 end if;
1925 if p_type_code is null or
1926 p_type_code = fnd_api.g_miss_num
1927 then
1928 fnd_message.set_name('WMS','WMS_STRA_TYPE_CODE_MISS');
1929 -- Strategy type code required but not provided
1930 fnd_msg_pub.add;
1931 If (l_debug = 1) then
1932 log_error_msg(l_api_name,'missing_type_code');
1933 End if;
1934 raise fnd_api.g_exc_unexpected_error;
1935 end if;
1936 /* Strategy Id is no longer required
1937 if p_strategy_id is null or
1938 p_strategy_id = fnd_api.g_miss_num then
1939 fnd_message.set_name('WMS','WMS_STRATEGY_ID_MISSING');
1940 -- Strategy identifier required but not provided
1941 fnd_msg_pub.add;
1942 raise fnd_api.g_exc_unexpected_error;
1943 end if;
1944 */
1945 end if;
1946
1947 --Added for bug3237702
1948 -- Check whether this is a pick release process and if locator is specified
1949 If inv_cache.is_pickrelease then
1950 is_pickrelease := true;
1951 l_req_locator_id := inv_cache.tolocator_id;
1952 l_req_subinventory_code := inv_cache.tosubinventory_code;
1953 End if;
1954 --bug3237702 ends
1955
1956 -- make sure, everything is clean
1957 FreeGlobals;
1958
1959 -- [ Initilizing the lot control valuse from cache
1960 l_lot_divisible_flag := inv_cache.item_rec.lot_divisible_flag;
1961 l_lot_control_code := inv_cache.item_rec.lot_control_code;
1962 -- ]
1963
1964 --if simulation mode is put away rule, but we are doing picking,
1965 -- ignore simulation modea
1966 IF p_simulation_mode = 4 AND
1967 p_type_code = 2 THEN
1968 l_simulation_mode := 0;
1969 ELSE
1970 l_simulation_mode := p_simulation_mode;
1971 END IF;
1972
1973 -- Initialize the internal input table
1974 InitInput ( x_return_status
1975 ,l_msg_count
1976 ,l_msg_data
1977 ,p_transaction_temp_id
1978 ,p_type_code
1979 ,l_organization_id
1980 ,l_inventory_item_id
1981 ,l_transaction_uom
1982 ,l_primary_uom
1983 ,l_secondary_uom
1984 ,l_transaction_source_type_id
1985 ,l_transaction_source_id
1986 ,l_trx_source_line_id
1987 ,l_trx_source_delivery_id
1988 ,l_transaction_source_name
1989 ,l_transaction_type_id
1990 ,l_tree_mode
1991 );
1992 if x_return_status = fnd_api.g_ret_sts_unexp_error then
1993 raise fnd_api.g_exc_unexpected_error;
1994 elsif x_return_status = fnd_api.g_ret_sts_error then
1995 raise fnd_api.g_exc_error;
1996 end if;
1997
1998 select NVL(WMS_ENABLED_FLAG, 'N') INTO l_wms_enabled_flag
1999 from mtl_parameters
2000 where ORGANIZATION_ID = l_organization_id;
2001
2002 -- Build Qty Tree, if type_code = 2
2003 l_tree_id := null;
2004 if p_type_code = 2 and l_qty_tree_allowed = 'Y' THEN -- Added for bug #4006426
2005 InitQtyTree ( x_return_status
2006 ,l_msg_count
2007 ,l_msg_data
2008 ,l_organization_id
2009 ,l_inventory_item_id
2010 ,l_transaction_source_type_id
2011 ,l_transaction_source_id
2012 ,l_trx_source_line_id
2013 ,l_trx_source_delivery_id
2014 ,l_transaction_source_name
2015 ,l_tree_mode
2016 ,l_tree_id
2017 );
2018 if x_return_status = fnd_api.g_ret_sts_unexp_error then
2019 raise fnd_api.g_exc_unexpected_error;
2020 elsif x_return_status = fnd_api.g_ret_sts_error then
2021 raise fnd_api.g_exc_error;
2022 end if;
2023 end if;
2024 ---
2025
2026 /** J Project:If l_quick_pick_flag equals to 'Y' and the type code is 'Pick',
2027 Call Quickpick() during the user initiated Moves moves for
2028 validations without actually calling the Picking rules engine **/
2029
2030 --trace(' ================== Before entering p_quick_Pick_flag = Y and p_type_code = 2 ') ;
2031 --trace ('p_quick_Pick_flag ' || p_quick_Pick_flag );
2032 --trace('p_type_code '|| p_type_code);
2033
2034 log_event(l_api_name,'', 'before quick pick');
2035 IF p_quick_pick_flag in ( 'Y', 'Q') and p_type_code = 2 then -- modified Bug #4006426
2036
2037 If (l_debug = 1) then
2038 log_event(l_api_name, 'APPLY', 'WMS_RULES_PVT.Calling QuickPick() ');
2039 End if;
2040 WMS_Rule_PVT.QuickPick (
2041 p_api_version => g_pp_rule_api_version
2042 , p_init_msg_list => fnd_api.g_false
2043 , p_commit => fnd_api.g_false
2044 , p_validation_level => fnd_api.g_valid_level_full
2045 , x_return_status => x_return_status
2046 , x_msg_count => l_msg_count
2047 , x_msg_data => l_msg_data
2048 , p_type_code => p_type_code
2049 , p_transaction_temp_id => p_transaction_temp_id
2050 , p_organization_id => l_organization_id
2051 , p_inventory_item_id => l_inventory_item_id
2052 , p_transaction_uom => l_transaction_uom
2053 , p_primary_uom => l_primary_uom
2054 , p_secondary_uom => l_secondary_uom
2055 , p_grade_code => l_grade_code
2056 , p_transaction_type_id => l_transaction_type_id
2057 , p_tree_id => l_tree_id
2058 , x_finished => l_finished
2059 , p_detail_serial => p_detail_serial
2060 , p_from_serial => p_from_serial
2061 , p_to_serial => p_to_serial
2062 , p_detail_any_serial => p_detail_any_serial
2063 , p_unit_volume => p_unit_volume
2064 , p_volume_uom_code => p_volume_uom_code
2065 , p_unit_weight => p_unit_weight
2066 , p_weight_uom_code => p_weight_uom_code
2067 , p_base_uom_code => p_base_uom_code
2068 , p_lpn_id => p_lpn_id
2069 , p_unit_number => p_unit_number
2070 , p_simulation_mode => p_simulation_mode
2071 , p_project_id => p_project_id
2072 , p_task_id => p_task_id
2073 );
2074
2075 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2076 raise fnd_api.g_exc_error;
2077 END IF;
2078 Else ---- Patchset H , I and non Invt. Moves
2079 log_event(l_api_name,'', 'no quick pick');
2080 -- Initialize the internal rules table
2081 InitStrategyRules ( x_return_status
2082 ,l_msg_count
2083 ,l_msg_data
2084 ,p_strategy_id );
2085
2086 if x_return_status = fnd_api.g_ret_sts_unexp_error then
2087 raise fnd_api.g_exc_unexpected_error;
2088 elsif x_return_status = fnd_api.g_ret_sts_error then
2089 raise fnd_api.g_exc_error;
2090 end if;
2091
2092 log_event(l_api_name,'', 'after init strategyrules ');
2093
2094 -- Loop through all the rules, until all input lines are satisfied
2095 if l_debug = 1 THEN
2096 log_event(l_api_name,'', 'getcountinputlines '||wms_re_common_pvt.getcountinputlines);
2097 END IF;
2098
2099 --- [ Lot Indiv 3.1.6 Indivisible Lot support
2100 --- Getting the max and min tolerance for lot_indivisable items and storing in global vars
2101 --- to be used in wms_rules_pvt.Apply()
2102 IF l_debug = 1 THEN
2103 log_statement(l_api_name, 'Calling get_tolerance()', '');
2104 log_statement(l_api_name, 'lot_divisible_flag', l_lot_divisible_flag);
2105 log_statement(l_api_name, 'l_lot_control_code ', l_lot_control_code );
2106 END IF;
2107
2108 g_allocated_quantity := 0;
2109 l_over_allocation_mode := g_over_allocation_mode;
2110 l_tolerance_value := g_tolerance_value;
2111
2112 WMS_RULE_PVT.g_max_tolerance := 0;
2113 WMS_RULE_PVT.g_over_allocation := 'N';
2114
2115 IF l_lot_divisible_flag = 'N' and l_lot_control_code <> 1 and p_type_code = 2 THEN -- lot ctl and indivisible
2116 WMS_RULE_PVT.g_min_tolerance := 0;
2117
2118 INV_Pick_Release_PVT.get_tolerance(p_mo_line_id => p_transaction_temp_id,
2119 x_return_status => x_return_status,
2120 x_msg_count => x_msg_count,
2121 x_msg_data => x_msg_data,
2122 x_max_tolerance => l_max_tolerance,
2123 x_min_tolerance => l_min_tolerance );
2124 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2125 IF l_debug = 1 THEN
2126 log_statement(l_api_name, 'INV_Pick_Release_PVT.get_tolerance', 'Unexpected error in get_tolerance Call');
2127 END IF;
2128 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2129 IF l_debug = 1 THEN
2130 log_statement(l_api_name, 'INV_Pick_Release_PVT.get_tolerance', 'Error in get_tolerance Call');
2131 END IF;
2132 END IF;
2133 WMS_RULE_PVT.g_max_tolerance := l_max_tolerance;
2134 WMS_RULE_PVT.g_min_tolerance := l_min_tolerance;
2135 ELSIF p_type_code = 2 AND WMS_Engine_PVT.g_move_order_type = 3 and l_wms_enabled_flag = 'Y' THEN
2136 -- Though with constant tolerance over allocation will occur, but WMS_RULE_PVT.g_over_allocation = 'N'
2137 -- will show that get_max-tolerance was not called,which will decide EXIT condition in the loop in Rules API
2138 IF l_over_allocation_mode = 3 THEN
2139 WMS_RULE_PVT.g_max_tolerance := (l_tolerance_value * WMS_Engine_PVT.g_mo_quantity)/100;
2140 WMS_RULE_PVT.g_over_allocation := 'N';
2141
2142 ELSIF l_over_allocation_mode = 2 THEN
2143 get_max_tolerance(
2144 p_transaction_temp_id => p_transaction_temp_id,
2145 p_organization_id => l_organization_id,
2146 p_inventory_item_id => l_inventory_item_id,
2147 p_trx_source_line_id => l_trx_source_line_id,
2148 x_max_tolerance => l_max_tolerance,
2149 x_return_status => x_return_status,
2150 x_msg_count => x_msg_count,
2151 x_msg_data => x_msg_data
2152 );
2153 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2154 IF l_debug = 1 THEN
2155 log_statement(l_api_name, 'get_max_tolerance', 'Unexpected error in get_max_tolerance Call');
2156 END IF;
2157 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2158 IF l_debug = 1 THEN
2159 log_statement(l_api_name, 'get_max_tolerance', 'Error in get_max_tolerance Call');
2160 END IF;
2161 END IF;
2162 WMS_RULE_PVT.g_max_tolerance := l_max_tolerance;
2163 WMS_RULE_PVT.g_over_allocation := 'Y';
2164
2165 ELSIF p_strategy_id IS NULL or l_over_allocation_mode = 1 THEN
2166 -- Querying all MO other than current MO line that have the same l_trx_source_line_id to see if any one
2167 -- then have been Over Allocated earlier. If so, then even though the current Strategy does not allow
2168 -- for Over Allocation, we need to consider the previous over allocated quantity and make sure we honor
2169 -- the tolerance limits
2170 BEGIN
2171 SELECT 'Y'
2172 INTO WMS_RULE_PVT.g_over_allocation
2173 FROM mtl_txn_request_lines mtrl
2174 WHERE mtrl.txn_source_line_id = l_trx_source_line_id
2175 AND mtrl.LINE_ID <> p_transaction_temp_id
2176 AND mtrl.quantity_detailed IS NOT NULL
2177 AND mtrl.inventory_item_id = l_inventory_item_id
2178 AND mtrl.organization_id = l_organization_id
2179 HAVING sum(mtrl.quantity - Nvl(mtrl.quantity_delivered, 0)) < (SELECT sum(Nvl(mmtt.transaction_quantity, 0))
2180 FROM mtl_material_transactions_temp mmtt
2181 WHERE mmtt.trx_source_line_id = l_trx_source_line_id
2182 AND mmtt.move_order_line_id <> p_transaction_temp_id
2183 AND mmtt.inventory_item_id = l_inventory_item_id
2184 AND mmtt.organization_id = l_organization_id
2185 AND mmtt.transaction_action_id = 28);
2186 EXCEPTION
2187 WHEN NO_DATA_FOUND THEN
2188 WMS_RULE_PVT.g_over_allocation := 'N';
2189 END;
2190
2191 IF WMS_RULE_PVT.g_over_allocation = 'Y' THEN
2192 get_max_tolerance(
2193 p_transaction_temp_id => p_transaction_temp_id,
2194 p_organization_id => l_organization_id,
2195 p_inventory_item_id => l_inventory_item_id,
2196 p_trx_source_line_id => l_trx_source_line_id,
2197 x_max_tolerance => l_max_tolerance,
2198 x_return_status => x_return_status,
2199 x_msg_count => x_msg_count,
2200 x_msg_data => x_msg_data
2201 );
2202 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2203 IF l_debug = 1 THEN
2204 log_statement(l_api_name, 'get_max_tolerance', 'Unexpected error in get_max_tolerance Call');
2205 END IF;
2206 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2207 IF l_debug = 1 THEN
2208 log_statement(l_api_name, 'get_max_tolerance', 'Error in get_max_tolerance Call');
2209 END IF;
2210 END IF;
2211 IF l_max_tolerance < 0 THEN
2212 WMS_RULE_PVT.g_max_tolerance := l_max_tolerance;
2213 END IF;
2214 END IF;
2215 END IF;
2216 ELSE
2217 WMS_RULE_PVT.g_over_allocation := 'N';
2218 END IF;
2219
2220 IF l_debug = 1 THEN
2221 log_statement(l_api_name, 'g_max_tolerance ', WMS_RULE_PVT.g_max_tolerance);
2222 log_statement(l_api_name, 'WMS_RULE_PVT.g_over_allocation ', WMS_RULE_PVT.g_over_allocation);
2223 END IF;
2224 --- [ End get_tolerance() ]
2225 ---
2226 while Wms_re_common_pvt.GetCountInputLines > 0 OR p_strategy_id IS NULL LOOP
2227 If l_debug = 1 THEN
2228 log_event(l_api_name,'', 'looping rules ');
2229 END IF;
2230
2231 -- only fetch rule if p_strategy_id is not null
2232 -- which is no rule detailing
2233 IF p_strategy_id IS NOT null AND
2234 l_simulation_mode NOT IN (2,4) THEN
2235
2236 -- Get the next rule
2237 wms_re_common_pvt.GetNextRule ( l_rule_id
2238 ,l_partial_success_allowed_flag );
2239
2240 If l_debug = 1 THEN
2241 log_event(l_api_name,'', 'get rule '||l_rule_id);
2242 END IF;
2243 exit when l_rule_id is null;
2244
2245 If (l_debug = 1) then
2246 log_event(l_api_name, 'rule_num',
2247 'Calling Apply procedure for rule ' || l_rule_id);
2248 End if;
2249 ELSIF l_simulation_mode IN (2,4) THEN
2250 l_rule_id := p_simulation_id;
2251 l_partial_success_allowed_flag := 'Y';
2252
2253 -- [ New Code : to call rule assignments ]
2254 ELSIF p_rule_id IS NOT null THEN
2255 l_rule_id := p_rule_id;
2256 ELSE
2257 l_rule_id := NULL;
2258 l_partial_success_allowed_flag := 'Y';
2259 If (l_debug = 1) then
2260 log_event(l_api_name, 'null_rule',
2261 'Calling Apply procedure for null rule');
2262 End if;
2263 END IF;
2264
2265 -- Bug 1734809 - Rules whose partial success allowed flag = N
2266 -- should not be executed if previous rules in the strategy
2267 -- have allocated some of the material
2268 l_skip_rule := 'N';
2269 IF l_partial_success_allowed_flag = 'N' THEN
2270 OPEN c_output_exists;
2271 FETCH c_output_exists INTO l_skip_rule;
2272
2273 IF (l_partial_success_allowed_flag = 'N' AND l_allow_non_partial_rules = FALSE) THEN
2274 l_skip_rule := 'Y';
2275 ELSIF c_output_exists%NOTFOUND Then
2276 l_skip_rule := 'N';
2277 Else
2278 If (l_debug = 1) then
2279 log_event(l_api_name, 'skip_rule',
2280 'Skipping rule with partial success allowed flag = N,' ||
2281 'since previous rules partially allocated.');
2282 End if;
2283 End If;
2284 CLOSE c_output_exists;
2285 ELSE
2286 l_skip_rule := 'N';
2287 END IF;
2288
2289 IF l_skip_rule = 'N' THEN
2290
2291
2292 If (l_debug = 1) then
2293 log_event(l_api_name, 'APPLY()', 'Calling ApplyRule() with rule id : ' || l_rule_id);
2294 log_event(l_api_name, 'APPLY()', 'Calling ApplyRule() with p_detail_any_serial : ' || p_detail_any_serial);
2295 log_event(l_api_name, 'APPLY()', 'Calling ApplyRule() p_from_serial : ' || p_from_serial);
2296 log_event(l_api_name, 'APPLY()', ' p_type_code : ' || p_type_code);
2297 log_event(l_api_name, 'APPLY()', ' l_wms_enabled_flag : ' || l_wms_enabled_flag);
2298
2299 End if;
2300
2301 --Added for bug3237702
2302 IF (is_pickrelease AND p_type_code = 1 AND l_req_locator_id IS NOT NULL) OR
2303 ( p_type_code = 1 AND l_wms_enabled_flag = 'N' ) OR -- [ Skiping putaway rules for INV Org ]
2304 (WMS_ENGINE_PVT.g_Is_xdock AND p_type_code = 1 ) -- [ Skip Rules for Xdocking
2305 THEN
2306 IF l_debug = 1 THEN
2307 log_event(l_api_name, 'APPLY()', 'applydefloc ');
2308 If l_wms_installed THEN
2309 log_event(l_api_name, 'APPLY()', 'Calling applydefloc for WMS flow');
2310 ELSE
2311 log_event(l_api_name, 'APPLY()', 'applydefloc for non WMS flow ');
2312 END IF;
2313
2314 END IF;
2315 WMS_rule_PVT.applydefloc
2316 (
2317 p_api_version => g_pp_rule_api_version,
2318 p_init_msg_list => fnd_api.g_false,
2319 p_commit => p_commit,
2320 p_validation_level => fnd_api.g_valid_level_none,
2321 x_return_status => x_return_status,
2322 x_msg_count => l_msg_count,
2323 x_msg_data => l_msg_data,
2324 p_transaction_temp_id => p_transaction_temp_id,
2325 p_organization_id => l_organization_id,
2326 p_inventory_item_id => l_inventory_item_id,
2327 p_subinventory_code => l_req_subinventory_code,
2328 p_locator_id => l_req_locator_id,
2329 p_transaction_uom => l_transaction_uom,
2330 p_primary_uom => l_primary_uom,
2331 p_transaction_type_id => l_transaction_type_id,
2332 x_finished => l_finished,
2333 p_lpn_id => p_lpn_id,
2334 p_simulation_mode => l_simulation_mode,
2335 p_project_id => p_project_id,
2336 p_task_id => p_task_id
2337 );
2338
2339 -- LG convergence add
2340 ElsIF p_simulation_mode = 10 THEN --mode is manual alloc
2341 log_event(l_api_name, 'APPLY()', 'get_availabe_inv ');
2342 WMS_Rule_PVT.get_available_inventory(
2343 p_api_version => g_pp_rule_api_version
2344 , p_init_msg_list => fnd_api.g_false
2345 , p_commit => fnd_api.g_false
2346 , p_validation_level => fnd_api.g_valid_level_full
2347 , x_return_status => x_return_status
2348 , x_msg_count => l_msg_count
2349 , x_msg_data => l_msg_data
2350 , p_rule_id => l_rule_id
2351 , p_type_code => p_type_code
2352 , p_partial_success_allowed_flag => l_partial_success_allowed_flag
2353 , p_transaction_temp_id => p_transaction_temp_id
2354 , p_organization_id => l_organization_id
2355 , p_inventory_item_id => l_inventory_item_id
2356 , p_transaction_uom => l_transaction_uom
2357 , p_primary_uom => l_primary_uom
2358 , p_transaction_type_id => l_transaction_type_id
2359 , p_tree_id => l_tree_id
2360 , x_finished => l_finished
2361 , p_detail_serial => p_detail_serial
2362 , p_from_serial => p_from_serial
2363 , p_to_serial => p_to_serial
2364 , p_detail_any_serial => p_detail_any_serial
2365 , p_unit_volume => p_unit_volume
2366 , p_volume_uom_code => p_volume_uom_code
2367 , p_unit_weight => p_unit_weight
2368 , p_weight_uom_code => p_weight_uom_code
2369 , p_base_uom_code => p_base_uom_code
2370 , p_lpn_id => p_lpn_id
2371 , p_unit_number => p_unit_number
2372 , p_simulation_mode => p_simulation_mode
2373 , p_project_id => p_project_id
2374 , p_task_id => p_task_id
2375 ) ;
2376
2377 --bug#6867434 start
2378 INV_Quantity_Tree_PVT. release_lock(
2379 p_api_version_number => g_qty_tree_api_version,
2380 p_init_msg_lst => fnd_api.g_false,
2381 x_return_status => x_return_status,
2382 x_msg_count => l_msg_count,
2383 x_msg_data => l_msg_data,
2384 p_organization_id => l_organization_id,
2385 p_inventory_item_id => l_inventory_item_id
2386 ) ;
2387 --bug#6867434 end
2388 log_event(l_api_name, 'APPLY()', 'end of get_availabe_inv ');
2389
2390 -- End of LG convergence
2391 Elsif p_rule_id is not null Then
2392
2393 -- {{ Test Case # UTK-REALLOC-3.1.3:3a
2394 --and Test Case # UTK-REALLOC-3.1.3:3b
2395 -- Description: Strategy search based on rule and strategy assignments
2396 -- Searching pick rule/ Putaway rules assignments
2397 -- Defaulting the l_partial_success_allowed_flag to 'Y' }}
2398 -- {{[ Test Case # UTK-REALLOC-3.1.3:3g
2399 -- Make sure searching all the picking rules in the strategy , if stg_id returned }}
2400 -- {{[ Test Case # UTK-REALLOC-3.1.3:3h
2401 -- Calling pick single rule , if rule_id returned}}
2402 -- {{[ Test Case # UTK-REALLOC-3.1.3:3i
2403 -- Uses default pick rule_id , if stg_id / rule_id is not returned}}
2404
2405 -- {{[ Test Case # UTK-REALLOC-3.1.3:3j
2406 -- Make sure searching all the put away in the strategy , if stg_id returned}}
2407 -- {{[ Test Case # UTK-REALLOC-3.1.3:3k
2408 -- Calling put away single rule , if rule_id returned}}
2409 -- {{[ Test Case # UTK-REALLOC-3.1.3:3l
2410 -- Uses default put away rule_id , if stg_id / rule_id is not returned}}
2411
2412 If (l_debug = 1) then
2413 log_event(l_api_name, 'APPLY()', 'rule apply for pick or putaway rule');
2414 End If;
2415
2416 WMS_Rule_PVT.Apply (
2417 p_api_version => g_pp_rule_api_version
2418 , p_init_msg_list => fnd_api.g_false
2419 , p_commit => fnd_api.g_false
2420 , p_validation_level => fnd_api.g_valid_level_full
2421 , x_return_status => x_return_status
2422 , x_msg_count => l_msg_count
2423 , x_msg_data => l_msg_data
2424 , p_rule_id => l_rule_id
2425 , p_type_code => p_type_code
2426 , p_partial_success_allowed_flag => 'Y'
2427 , p_transaction_temp_id => p_transaction_temp_id
2428 , p_organization_id => l_organization_id
2429 , p_inventory_item_id => l_inventory_item_id
2430 , p_transaction_uom => l_transaction_uom
2431 , p_primary_uom => l_primary_uom
2432 , p_secondary_uom => l_secondary_uom
2433 , p_grade_code => l_grade_code
2434 , p_transaction_type_id => l_transaction_type_id
2435 , p_tree_id => l_tree_id
2436 , x_finished => l_finished
2437 , p_detail_serial => p_detail_serial
2438 , p_from_serial => p_from_serial
2439 , p_to_serial => p_to_serial
2440 , p_detail_any_serial => p_detail_any_serial
2441 , p_unit_volume => p_unit_volume
2442 , p_volume_uom_code => p_volume_uom_code
2443 , p_unit_weight => p_unit_weight
2444 , p_weight_uom_code => p_weight_uom_code
2445 , p_base_uom_code => p_base_uom_code
2446 , p_lpn_id => p_lpn_id
2447 , p_unit_number => p_unit_number
2448 , p_simulation_mode => p_simulation_mode
2449 , p_project_id => p_project_id
2450 , p_task_id => p_task_id
2451 );
2452 Else
2453 --Bug3237702 ends
2454
2455 log_event(l_api_name, 'APPLY()', 'rule apply ');
2456 WMS_Rule_PVT.Apply (
2457 p_api_version => g_pp_rule_api_version
2458 , p_init_msg_list => fnd_api.g_false
2459 , p_commit => fnd_api.g_false
2460 , p_validation_level => fnd_api.g_valid_level_full
2461 , x_return_status => x_return_status
2462 , x_msg_count => l_msg_count
2463 , x_msg_data => l_msg_data
2464 , p_rule_id => l_rule_id
2465 , p_type_code => p_type_code
2466 , p_partial_success_allowed_flag => l_partial_success_allowed_flag
2467 , p_transaction_temp_id => p_transaction_temp_id
2468 , p_organization_id => l_organization_id
2469 , p_inventory_item_id => l_inventory_item_id
2470 , p_transaction_uom => l_transaction_uom
2471 , p_primary_uom => l_primary_uom
2472 , p_secondary_uom => l_secondary_uom
2473 , p_grade_code => l_grade_code
2474 , p_transaction_type_id => l_transaction_type_id
2475 , p_tree_id => l_tree_id
2476 , x_finished => l_finished
2477 , p_detail_serial => p_detail_serial
2478 , p_from_serial => p_from_serial
2479 , p_to_serial => p_to_serial
2480 , p_detail_any_serial => p_detail_any_serial
2481 , p_unit_volume => p_unit_volume
2482 , p_volume_uom_code => p_volume_uom_code
2483 , p_unit_weight => p_unit_weight
2484 , p_weight_uom_code => p_weight_uom_code
2485 , p_base_uom_code => p_base_uom_code
2486 , p_lpn_id => p_lpn_id
2487 , p_unit_number => p_unit_number
2488 , p_simulation_mode => p_simulation_mode
2489 , p_project_id => p_project_id
2490 , p_task_id => p_task_id
2491 );
2492
2493 -- Bug # 3413372
2494 -- Initilize global variable /Tables used by get_itemobhand(),
2495 -- get_project_attribute() in WMS_PARAMETERS_PVT
2496 wms_parameter_pvt.g_GetItemOnhq_IsRuleCached := 'N';
2497 wms_parameter_pvt.g_GetProjAttr_IsRuleCached := 'N';
2498
2499 wms_parameter_pvt.g_locator_item_quantity.DELETE;
2500 wms_parameter_pvt.g_bulkCollect_Locator.DELETE;
2501 wms_parameter_pvt.g_bulkCollect_quantity.DELETE;
2502 -- end of Bug# 3413372
2503 END IF;
2504 if x_return_status = fnd_api.g_ret_sts_unexp_error then
2505 raise fnd_api.g_exc_unexpected_error;
2506 elsif x_return_status = fnd_api.g_ret_sts_error then
2507 raise fnd_api.g_exc_error;
2508 end if;
2509 END IF;
2510 if (l_debug = 1) then
2511 log_statement(l_api_name, 'l_finished ', l_finished);
2512 end if;
2513 --gmi_reservation_util.println('l_finished '||l_finished);
2514 exit when fnd_api.to_boolean(l_finished) OR p_strategy_id IS NULL
2515 OR l_simulation_mode IN (2,4,10);
2516 end loop;
2517
2518
2519 END IF; -- end Patchset J changes
2520
2521 -- Standard check of p_commit
2522 if fnd_api.to_boolean(p_commit) then
2523 commit work;
2524 end if;
2525
2526 --
2527 -- Standard call to get message count and if count is 1, get message info
2528 fnd_msg_pub.count_and_get( p_count => x_msg_count
2529 ,p_data => x_msg_data );
2530 /* --
2531 -- debugging portion
2532 -- can be commented ut for final code
2533 IF inv_pp_debug.is_debug_mode THEN
2534 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
2535 END IF;
2536 -- end of debugging section */
2537 If (l_debug = 1) then
2538 log_procedure(l_api_name, 'end', 'End Apply');
2539 End if;
2540 -- g_debug := NULL;
2541 --
2542 exception
2543 when fnd_api.g_exc_error then
2544 /* --
2545 -- debugging portion
2546 -- can be commented ut for final code
2547 IF inv_pp_debug.is_debug_mode THEN
2548 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2549 -- the message retrieved here since it is no longer on the stack
2550 inv_pp_debug.set_last_error_message(Sqlerrm);
2551 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2552 inv_pp_debug.send_last_error_message;
2553 END IF;
2554 -- end of debugging section
2555 -- */
2556 rollback to ApplyStrategySP;
2557 FreeGlobals;
2558 fnd_msg_pub.count_and_get( p_count => x_msg_count
2559 ,p_data => x_msg_data );
2560 If (l_debug = 1) then
2561 log_error(l_api_name, 'error', 'Error in Apply - ' || x_msg_data);
2562 End if;
2563 g_debug := NULL;
2564
2565 when fnd_api.g_exc_unexpected_error then
2566 /*--
2567 -- debugging portion
2568 -- can be commented ut for final code
2569 IF inv_pp_debug.is_debug_mode THEN
2570 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2571 -- the message retrieved here since it is no longer on the stack
2572 inv_pp_debug.set_last_error_message(Sqlerrm);
2573 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2574 inv_pp_debug.send_last_error_message;
2575 END IF;
2576 -- end of debugging section
2577 -- */
2578 rollback to ApplyStrategySP;
2579 FreeGlobals;
2580 x_return_status := fnd_api.g_ret_sts_unexp_error;
2581 fnd_msg_pub.count_and_get( p_count => x_msg_count
2582 ,p_data => x_msg_data );
2583 If (l_debug = 1) then
2584 log_error(l_api_name, 'unexp_error',
2585 'Unexpected error in Apply - ' || x_msg_data);
2586 End if;
2587 -- g_debug := NULL;
2588
2589 when others then
2590 /*--
2591 -- debugging portion
2592 -- can be commented ut for final code
2593 IF inv_pp_debug.is_debug_mode THEN
2594 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2595 -- the message retrieved here since it is no longer on the stack
2596 inv_pp_debug.set_last_error_message(Sqlerrm);
2597 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2598 inv_pp_debug.send_last_error_message;
2599 END IF;
2600 -- end of debugging section
2601 -- */
2602 rollback to ApplyStrategySP;
2603 FreeGlobals;
2604 x_return_status := fnd_api.g_ret_sts_unexp_error;
2605 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
2606 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2607 end if;
2608 fnd_msg_pub.count_and_get( p_count => x_msg_count
2609 ,p_data => x_msg_data );
2610 If (l_debug = 1) then
2611 log_error(l_api_name, 'other_error',
2612 'Other error in Apply - ' || x_msg_data);
2613 End if;
2614 --g_debug := NULL;
2615 end Apply;
2616 end WMS_Strategy_PVT;
[Home] [Help]