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