1 PACKAGE BODY WMS_CostGroupEngine_PVT AS
2 /* $Header: WMSVPPGB.pls 120.3.12020000.3 2013/01/30 05:30:18 rdudani ship $*/
3 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_CostGroupEngine_PVT';
4 --
5 l_debug NUMBER;
6 --Procedures for logging messages
7 PROCEDURE log_event(
8 p_api_name VARCHAR2,
9 p_label VARCHAR2,
10 p_message VARCHAR2) IS
11 l_module VARCHAR2(255);
12 BEGIN
13 l_module := g_pkg_name || p_label;
14 inv_log_util.trace(p_message, l_module, 9);
15
16 END log_event;
17
18 PROCEDURE log_error(
19 p_api_name VARCHAR2,
20 p_label VARCHAR2,
21 p_message VARCHAR2) IS
22 l_module VARCHAR2(255);
23 BEGIN
24 l_module := g_pkg_name || p_label;
25 inv_log_util.trace(p_message, l_module, 9);
26
27 END log_error;
28
29 PROCEDURE log_error_msg(
30 p_api_name VARCHAR2,
31 p_label VARCHAR2) IS
32 l_module VARCHAR2(255);
33 BEGIN
34 l_module := g_pkg_name || p_label;
35
36 inv_log_util.trace('', l_module, 9);
37 END log_error_msg;
38
39 PROCEDURE log_procedure(
40 p_api_name VARCHAR2,
41 p_label VARCHAR2,
42 p_message VARCHAR2) IS
43 l_module VARCHAR2(255);
44 BEGIN
45 l_module := g_pkg_name || p_label;
46
47 inv_log_util.trace(p_message, l_module, 9);
48 END log_procedure;
49
50 PROCEDURE log_statement(
51 p_api_name VARCHAR2,
52 p_label VARCHAR2,
53 p_message VARCHAR2) IS
54 l_module VARCHAR2(255);
55 BEGIN
56 l_module := g_pkg_name || p_label;
57 inv_log_util.trace(p_message, l_module, 9);
58 END log_statement;
59 ---------------------------------------------------------------------------------
60 --- Procedures to handle the static calls to open, fetch and close cursor
61 --- based on the Rule_id. The name of the API_call is decided based on the the
62 --- flag retrived from the table.
63
64
65 ----------------
66 PROCEDURE execute_CG_rule( p_rule_id IN NUMBER,
67 p_line_id IN NUMBER,
68 x_sql_return OUT NOCOPY NUMBER) IS
69
70 invalid_pkg_state EXCEPTION;
71 Pragma Exception_Init(invalid_pkg_state, -6508);
72
73 l_api_name VARCHAR2(30);
74 l_list_pkg VARCHAR2(30);
75 l_package_name VARCHAR2(128);
76
77 l_ctr number := 0;
78
79 BEGIN
80
81 -- Switching logic to avoid potential contentation issues
82 IF wms_costgroupengine_pvt.g_rule_list_cg_ctr IS NULL THEN
83 wms_costgroupengine_pvt.g_rule_list_cg_ctr := wms_rule_gen_pkgs.get_count_no_lock('COST_GROUP' );
84 END IF;
85 l_ctr := wms_costgroupengine_pvt.g_rule_list_cg_ctr;
86
87
88 IF (l_ctr = 1) then
89 wms_rule_cg_pkg1.EXECUTE_CG_RULE( p_rule_id,
90 p_line_id,
91 x_sql_return);
92 ELSIF (l_ctr = 2) then
93 wms_rule_cg_pkg2.EXECUTE_CG_RULE( p_rule_id,
94 p_line_id,
95 x_sql_return);
96 ELSIF (l_ctr = 3) then
97 wms_rule_cg_pkg3.EXECUTE_CG_RULE( p_rule_id,
98 p_line_id,
99 x_sql_return);
100
101 END IF;
102 If x_sql_return is NUll Then
103 x_sql_return := 1;
104 End if;
105
106 EXCEPTION
107 WHEN INVALID_PKG_STATE THEN
108 x_sql_return := -1;
109 wms_costgroupengine_pvt.g_rule_list_cg_ctr := wms_rule_gen_pkgs.get_count_no_lock('COST_GROUP' );
110 WMS_ENGINE_PVT.G_SUGG_FAILURE_MESSAGE := 'Invalid Package, Contact your DBA - '|| l_list_pkg || ' / ' || l_package_name;
111 fnd_message.set_name('WMS', 'WMS_INVALID_PKG');
112 fnd_message.set_token('LIST_PKG', l_list_pkg);
113 fnd_message.set_token('RULE_NAME', l_package_name);
114 fnd_msg_pub.ADD;
115 log_error(l_api_name, 'execute_open_rule', 'Invalid Package, Contact your DBA - '
116 || l_list_pkg || ' / ' || l_package_name);
117
118 END execute_CG_rule;
119 ----------------
120
121 PROCEDURE assign_cost_group(
122 p_api_version IN NUMBER
123 ,p_init_msg_list IN VARCHAR2
124 ,p_commit IN VARCHAR2
125 ,p_validation_level IN NUMBER
126 ,x_return_status OUT NOCOPY VARCHAR2
127 ,x_msg_count OUT NOCOPY NUMBER
128 ,x_msg_data OUT NOCOPY VARCHAR2
129 ,p_line_id IN NUMBER
130 ,p_input_type IN NUMBER
131 ,p_simulation_mode IN NUMBER
132 ,p_simulation_id IN NUMBER
133 ) IS
134
135 l_rule_id NUMBER;
136 l_pack_exists NUMBER;
137 l_package_name VARCHAR2(30);
138 l_rule_func_sql long;
139 l_rule_result NUMBER;
140 l_cursor INTEGER;
141 l_dummy NUMBER;
142 l_count NUMBER;
143 l_return_status VARCHAR(1);
144 l_sql_return NUMBER;
145 l_strategy_id NUMBER;
146 l_cg_rule_id NUMBER; --- Added new column
147 l_partial_flag WMS_STRATEGY_MEMBERS.partial_success_allowed_flag%TYPE;
148 l_to_subinventory_code VARCHAR2(10);
149 l_organization_id NUMBER;
150 l_source_organization_id NUMBER;
151 l_transfer_organization_id NUMBER;
152 l_cost_group_id NUMBER := NULL;
153 l_transaction_action_id NUMBER;
154 l_input_line WMS_COST_GROUPS_INPUT_V%ROWTYPE;
155 l_fob_point NUMBER := NULL;
156 l_simulation_mode NUMBER;
157 l_api_version constant number := 1.0;
158 l_api_name constant varchar2(30) := 'Assign_Cost_Group';
159
160 l_return_type VARCHAR2(1);
161 l_return_type_id NUMBER;
162 l_sequence_number NUMBER;
163 l_rules_engine_mode NUMBER := 1; -- nvl(FND_PROFILE.VALUE('WMS_RULES_ENGINE_MODE'), 0);
164
165 l_rule_counter INTEGER;
166 g_debug NUMBER;
167 --cursor to get input lines
168
169 /*commented for bug 10023490. Replaced with following cursors
170 CURSOR c_input_line IS
171 SELECT organization_id
172 ,to_subinventory_code
173 ,transaction_action_id
174 ,to_organization_id
175 FROM wms_cost_groups_input_v
176 WHERE line_id = p_line_id; */
177
178 CURSOR c_input_line_mtrl IS --Bug#10023490
179 SELECT MTRL.ORGANIZATION_ID ORGANIZATION_ID,
180 MTRL.TO_SUBINVENTORY_CODE TO_SUBINVENTORY_CODE,
181 to_number(NULL) transaction_action_id, -- swapped the third and fourth parameter for bug 6761233
182 MTRL.ORGANIZATION_ID TO_ORGANIZATION_ID
183 FROM MTL_TXN_REQUEST_LINES MTRL
184 WHERE mtrl.line_id=p_line_id;
185
186 CURSOR c_input_line_mmtt IS --Bug#10023490
187 SELECT
188 DECODE(mmtt.transaction_action_id, 3, mmtt.organization_id,
189 21, DECODE(mip.fob_point, 1, transfer_organization, organization_id)
190 , organization_id) ORGANIZATION_ID,
191 DECODE(transaction_action_id, 1, NULL,
192 2, transfer_subinventory,
193 3, transfer_subinventory,
194 21, NULL,
195 28, transfer_subinventory,
196 29, subinventory_code,
197 32, NULL,
198 34, NULL
199 , subinventory_code) to_subinventory_code,
200 mmtt.transaction_action_id transaction_action_id, -- swapped the third and fourth parameter for bug 6761233
201 DECODE(transaction_action_id, 3, transfer_organization,
202 21, transfer_organization,
203 organization_id) TO_ORGANIZATION_ID
204 FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
205 MTL_INTERORG_PARAMETERS mip
206 WHERE mip.from_organization_id (+) = mmtt.organization_id
207 AND mip.to_organization_id (+) = mmtt.transfer_organization
208 AND mmtt.transaction_temp_id = p_line_id;
209
210 CURSOR c_fob_point IS
211 SELECT fob_point
212 FROM MTL_INTERORG_PARAMETERS
213 WHERE from_organization_id = l_organization_id/*changed from l_source_organization_id for 3224420*/
214 AND to_organization_id = l_transfer_organization_id;
215
216 --cursor used to determine if the rule package exists
217 CURSOR l_pack_gen IS
218 SELECT count(object_name)
219 FROM user_objects
220 WHERE object_name = l_package_name;
221
222 --cursor to get the cost group from the rule
223 CURSOR l_rule_cg IS
224 SELECT type_hdr_id
225 FROM wms_rules_b
226 WHERE rule_id = l_rule_id;
227
228 --cursor used to get default cost group from sub
229 CURSOR l_default_cg_sub IS
230 SELECT default_cost_group_id
231 FROM mtl_secondary_inventories
232 WHERE secondary_inventory_name = l_to_subinventory_code
233 AND organization_id = l_organization_id;
234
235
236 --cursor used to get default cost group from org
237 CURSOR l_default_cg_org IS
238 SELECT default_cost_group_id
239 FROM mtl_parameters
240 WHERE organization_id = l_organization_id;
241
242 BEGIN
243
244 SAVEPOINT assignCGSP;
245
246 -- l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
247 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
248 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
249 END IF;
250 l_debug := g_debug;
251
252 -- Standard call to check for call compatibility
253 IF NOT fnd_api.compatible_api_call( l_api_version
254 ,p_api_version
255 ,l_api_name
256 ,g_pkg_name ) THEN
257 RAISE fnd_api.g_exc_unexpected_error;
258 END IF;
259
260
261 -- Initialize message list if p_init_msg_list is set to TRUE
262 IF fnd_api.to_boolean( p_init_msg_list ) THEN
263 fnd_msg_pub.initialize;
264 END IF;
265 --
266 -- Initialize API return status to success
267 x_return_status := fnd_api.g_ret_sts_success;
268
269 -- debugging portion
270 -- can be commented out for final code
271
272 IF l_debug = 1 THEN
273 log_procedure(l_api_name, '', 'Start Assign_Cost_Group');
274 log_procedure (l_api_name, 'Line_id', p_line_id);
275 log_procedure (l_api_name, 'Input type', p_input_type);
276 END IF;
277
278 -- Validate input parameters and pre-requisites, if validation level
279 -- requires this
280 IF p_validation_level <> fnd_api.g_valid_level_none THEN
281
282 --check for null line_id
283 if (p_line_id IS NULL) then
284 fnd_message.set_name('WMS','WMS_CG_MISSING_LINE_ID');
285 fnd_msg_pub.add;
286 log_error_msg(l_api_name, 'missing_line_id');
287 raise fnd_api.g_exc_error;
288 end if;
289
290 --check for null type code
291 if (p_input_type IS NULL) then
292 fnd_message.set_name('WMS','WMS_CG_MISSING_INPUT_TYPE');
293 fnd_msg_pub.add;
294 log_error_msg(l_api_name, 'missing_input_type');
295 raise fnd_api.g_exc_error;
296 end if;
297
298 END IF;
299
300 IF p_input_type = g_input_mmtt THEN
301 If l_debug = 1 then
302 log_event(l_api_name, 'mmtt_line', 'Update a transaction with ' ||
303 'transaction temp id: ' || p_line_id);
304 end if;
305 ELSIF p_input_type = g_input_mtrl THEN
306 If l_debug = 1 then
307 log_event(l_api_name, 'mmtt_line', 'Update a move order with ' ||
308 'move order line id: ' || p_line_id);
309 end if;
310 ELSE
311 fnd_message.set_name('WMS','WMS_CG_MISSING_INPUT_TYPE');
312 fnd_msg_pub.add;
313 log_error_msg(l_api_name, 'invalid_input_type');
314 raise fnd_api.g_exc_error;
315 END IF;
316
317 --set record type in global variable
318 g_current_input_type := p_input_type;
319
320 --validate simulation mode parameters
321 IF p_simulation_mode < g_no_simulation OR
322 p_simulation_mode > g_rule_mode THEN
323 l_simulation_mode := g_no_simulation;
324 ELSIF p_simulation_mode IN (g_rule_mode, g_strategy_mode) AND
325 p_simulation_id IS NULL THEN
326 l_simulation_mode := g_no_simulation;
327 ELSE
328 l_simulation_mode := p_simulation_mode;
329 END IF;
330
331 --get input line from view; store in record
332 --Bug#10023490-Added cursor based on input type.
333 IF p_input_type = g_input_mmtt THEN
334 OPEN c_input_line_mmtt;
335 FETCH c_input_line_mmtt INTO l_organization_id
336 ,l_to_subinventory_code
337 ,l_transaction_action_id
338 ,l_transfer_organization_id;
339 IF (c_input_line_mmtt%NOTFOUND) THEN
340 CLOSE c_input_line_mmtt;
341 fnd_message.set_name('WMS','WMS_CG_LINE_NOT_FOUND');
342 fnd_msg_pub.add;
343 log_error_msg(l_api_name, 'line_not_found');
344 raise fnd_api.g_exc_error;
345 END IF;
346 CLOSE c_input_line_mmtt;
347 ELSIF p_input_type = g_input_mtrl THEN
348 OPEN c_input_line_mtrl;
349 FETCH c_input_line_mtrl INTO l_organization_id
350 ,l_to_subinventory_code
351 ,l_transaction_action_id
352 ,l_transfer_organization_id;
353 IF (c_input_line_mtrl%NOTFOUND) THEN
354 CLOSE c_input_line_mtrl;
355 fnd_message.set_name('WMS','WMS_CG_LINE_NOT_FOUND');
356 fnd_msg_pub.add;
357 log_error_msg(l_api_name, 'line_not_found');
358 raise fnd_api.g_exc_error;
359 END IF;
360 CLOSE c_input_line_mtrl;
361 END IF;
362
363 -- Determine which organization to use.
364 -- If direct org transfer, use the transfer_org, since that is the
365 -- receiving org. If intransit receipt, we store the organization_id
366 -- in the to_organization_id in the view.
367
368 IF l_transaction_action_id IN (3,12) THEN
369 l_organization_id := l_transfer_organization_id;
370 -- For intransit shipment, the org to run rules on depends on the
371 -- fob point flag
372 ELSIF l_transaction_action_id = 21 THEN
373 OPEN c_fob_point;
374 FETCH c_fob_point INTO l_fob_point;
375 If c_fob_point%NOTFOUND Then
376 l_fob_point:= NULL;
377 End If;
378 CLOSE c_fob_point;
379
380 --if fob point = 1, then the ownership change occurs at issue.
381 -- If this is true, then we need to run rules on destination org
382 If l_fob_point IS NOT NULL and l_fob_point = 1 Then
383 l_organization_id := l_transfer_organization_id;
384 /*3224420Else
385 l_organization_id := l_source_organization_id;*/
386 End If;
387 /*3224220ELSE
388 l_organization_id := l_source_organization_id;*/
389 END IF;
390
391 if l_debug = 1 then
392 log_statement(l_api_name, 'org_id', 'Running the engine on org ' ||
393 l_organization_id);
394 end if;
395
396 --get Strategy
397 IF l_simulation_mode = g_no_simulation THEN
398 if l_debug = 1 then
399 log_event(l_api_name, '', 'Calling the Strategy Search function');
400 end if;
401
402 If (g_current_input_type = g_input_mmtt) then
403 if l_debug = 1 then
404 log_event(l_api_name, '', 'wms_rules_workbench_pvt.cg_mmtt_search');
405 log_event(l_api_name, 'p_transaction_temp_id',p_line_id);
406 log_event(l_api_name, 'p_organization_id', l_organization_id);
407 end if;
408 wms_rules_workbench_pvt.cg_mmtt_search
409 ( p_api_version => 1.0
410 ,p_init_msg_list => fnd_api.g_false
411 ,p_validation_level => fnd_api.g_valid_level_none
412 ,x_return_status => l_return_status
413 ,x_msg_count => x_msg_count
414 ,x_msg_data => x_msg_data
415 ,p_transaction_temp_id => p_line_id
416 ,p_type_code => 5
417 ,x_return_type => l_return_type
418 ,x_return_type_id => l_return_type_id
419 ,p_organization_id => l_organization_id
420 ,x_sequence_number => l_sequence_number );
421 if l_debug = 1 then
422 log_statement(l_api_name, 'l_return_type', l_return_type);
423 log_statement(l_api_name, 'l_return_type_id', l_return_type_id);
424 log_statement(l_api_name, 'l_organization_id', l_organization_id);
425 log_statement(l_api_name, 'l_sequence_number ', l_sequence_number );
426 log_event(l_api_name, 'cg_mmtt_search', 'End');
427 end if;
428 elsif (g_current_input_type = g_input_mtrl) then
429 if l_debug = 1 then
430 log_event(l_api_name, 'Search', 'wms_rules_workbench_pvt.search');
431 end if;
432 wms_rules_workbench_pvt.Search
433 ( p_api_version => 1.0
434 ,p_init_msg_list => fnd_api.g_false
435 ,p_validation_level => fnd_api.g_valid_level_none
436 ,x_return_status => l_return_status
437 ,x_msg_count => x_msg_count
438 ,x_msg_data => x_msg_data
439 ,p_transaction_temp_id => p_line_id
440 ,p_type_code => 5
441 ,x_return_type => l_return_type
442 ,x_return_type_id => l_return_type_id
443 ,p_organization_id => l_organization_id
444 ,x_sequence_number => l_sequence_number);
445
446 if l_debug = 1 then
447 log_statement(l_api_name, ': l_return_type', l_return_type);
448 log_statement(l_api_name, ': l_return_type_id', l_return_type_id);
449 log_statement(l_api_name, ': l_organization_id', l_organization_id);
450 log_statement(l_api_name, ': l_sequence_number ', l_sequence_number );
451 log_event(l_api_name, 'search', 'End');
452 end if;
453 End if;
454 if l_return_status = fnd_api.g_ret_sts_unexp_error then
455 if l_debug = 1 THEN
456 log_error(l_api_name, 'strat_search_unexp_err',
457 'Unexpected error in wms_strategy_pvt search procedure');
458 end if;
459 raise fnd_api.g_exc_unexpected_error;
460
461 elsif l_return_status = fnd_api.g_ret_sts_error then
462 if l_debug = 1 then
463 log_error(l_api_name, 'strat_search_err',
464 'Error in wms_strategy_pvt search procedure');
465 end if;
466 raise fnd_api.g_exc_error;
467 elsif l_return_status = fnd_api.g_ret_sts_success then
468 --If l_rules_engine_mode = 1 then
469 -- {{[ Test Case # UTK-REALLOC-3.1.3:3c
470 -- Description: Strategy search based on rule and strategy assignments
471 -- Searching Cost Group rule assignments Misc Receipt ] }}
472
473 -- {{[ Test Case # UTK-REALLOC-3.1.3:3m
474 -- Description: Strategy search based on rule and strategy assignments
475 -- Searching Cost Group rule assignments PO Receipt ] }}
476
477 if l_return_type = 'S' then
478 l_strategy_id :=l_return_type_id;
479 l_cg_rule_id := NULL;
480 elsif l_return_type = 'R' then
481 l_cg_rule_id :=l_return_type_id;
482 l_strategy_id := NULL;
483 elsif l_return_type = 'V' then
484 l_cost_group_id:=l_return_type_id;
485 l_strategy_id := NULL;
486 l_cg_rule_id := NULL;
487 else
488 l_strategy_id := NULL;
489 l_cg_rule_id := NULL;
490 l_cost_group_id:=NULL;
491 end if;
492 End If;
493 --end if;
494
495 if l_debug = 1 then
496 if l_strategy_id is not null then
497 log_event(l_api_name, 'strategy_found','Strategy id:'|| l_strategy_id);
498 elsif l_cg_rule_id is not null then
499 log_event(l_api_name, 'Rule_found','Rule id:'|| l_cg_rule_id);
500 elsif l_cost_group_id is not null then
501 log_event(l_api_name, 'Direct Value Found ','Rule id:'|| l_cost_group_id);
502 end if;
503 end if;
504 ELSIF l_simulation_mode = g_strategy_mode THEN
505 l_strategy_id := p_simulation_id;
506 -- May have to add code if simulation is done for rule
507 ELSE
508 l_strategy_id := NULL;
509 l_cg_rule_id := NULL;
510 END IF;
511
512 if l_debug = 1 then
513 if l_strategy_id is not NULL then
514 log_statement(l_api_name, '', 'Using strategy:' || l_strategy_id);
515 elsif l_cg_rule_id is not NULL then
516 log_statement(l_api_name, '', 'Using rule:' || l_cg_rule_id);
517 elsif l_cost_group_id is not null then
518 log_statement(l_api_name, '', 'Using Value:' || l_cost_group_id);
519 end if;
520 end if;
521 -- Get rules within that strategy
522 -- Initialize the internal rules table
523 IF l_strategy_id IS NOT NULL THEN
524 wms_strategy_pvt.InitStrategyRules ( l_return_status
525 ,x_msg_count
526 ,x_msg_data
527 ,l_strategy_id );
528
529 if l_return_status = fnd_api.g_ret_sts_unexp_error then
530 if l_debug = 1 then
531 log_error(l_api_name, 'init_rules_unexp_err',
532 'Unexpected error in wms_strategy_pvt InitStrategyRules');
533 end if;
534 raise fnd_api.g_exc_unexpected_error;
535 elsif l_return_status = fnd_api.g_ret_sts_error then
536 if l_debug = 1 then
537 log_error(l_api_name, 'init_rules_err',
538 'Error in wms_strategy_pvt InitStrategyRules');
539 end if;
540 raise fnd_api.g_exc_error;
541 end if;
542 END IF;
543 -- Loop through all the rules, until all input lines are satisfied
544 -- [ call the rule and exit from the loop ]
545 --
546 -- {{[ Test Case # UTK-REALLOC-3.1.3:3d
547 -- Description: Strategy search based on rule and strategy assignments
548 -- Make sure searching all the rules in the strategy , if stg_id returned ] }}
549
550 -- {{[ Test Case # UTK-REALLOC-3.1.3:3e
551 -- Description: Strategy search based on rule and strategy assignments
552 -- Calling single rule , if rule_id returned ]}}
553
554
555 IF(l_cost_group_id is NULL) THEN
556 if l_debug = 1 then
557 log_statement(l_api_name, 'l_cost_group_id is NULL', 'Needs to Derive');
558 end if;
559 WHILE l_strategy_id IS NOT NULL OR
560 p_simulation_mode = g_rule_mode OR
561 l_cg_rule_id is NOT NULL LOOP
562 --- added for Patchset 'K'
563
564 if l_debug = 1 then
565 log_event(l_api_name, 'Inside While ', 'p_simulation_mode== ' ||p_simulation_mode || 'g_rule_mode== ' ||g_rule_mode);
566
567 end if;
568
569 --get id of next rule in strategy , if workbench returns the strategy_id
570 --for cost group rules, we don't use partial success flag
571
572 If p_simulation_mode = g_rule_mode Then
573 l_rule_id := p_simulation_id;
574 ELSIF l_cg_rule_id is not NULL THEN -- added this code for patchset 'K'
575 l_rule_id := l_cg_rule_id;
576 Else
577
578 wms_re_common_pvt.GetNextRule(
579 x_rule_id => l_rule_id
580 ,x_partial_success_allowed_flag => l_partial_flag);
581 End If;
582 EXIT WHEN l_rule_id IS NULL;
583 if l_debug = 1 then
584 log_event(l_api_name, '', 'Current rule ' ||l_rule_id);
585 log_statement(l_api_name, '',
586 'calling GetPackageName( '||l_rule_id ||',' ||l_package_name ||')');
587 end if;
588 -- get the pre-generated package name for this rule
589 wms_rule_pvt.GetPackageName(l_rule_id, l_package_name);
590 if l_debug = 1 then
591 log_statement(l_api_name, '',
592 'Calling open_curs -' || l_package_name);
593 log_statement(l_api_name, 'l_rule_id', l_rule_id);
594 log_statement(l_api_name, 'l_sql_return ', l_sql_return );
595 log_statement(l_api_name, 'Before Entering Loop ', '');
596 END IF;
597
598 For l_rule_counter IN 1..2 LOOP
599 execute_CG_rule(l_rule_id, p_line_id,l_sql_return);
600
601 IF l_debug = 1 THEN
602 log_statement(l_api_name, 'execute_CG_rule Loop (l_rule_counter ) ', l_rule_counter);
603 log_statement(l_api_name, 'l_sql_return ', l_sql_return );
604 END IF;
605
606 IF (l_sql_return = -1 ) and l_rule_counter = 2 THEN --error
607 fnd_message.set_name('WMS', 'WMS_PACKAGE_MISSING');
608 fnd_message.set_token('RULEID', l_rule_id);
609 fnd_msg_pub.ADD;
610 if l_debug = 1 then
611 log_statement(l_api_name, 'l_sql_return ', l_sql_return );
612 log_error_msg(l_api_name, 'rule_package_missing');
613 log_statement(l_api_name,'', 'Package name: ' || l_package_name);
614 end if;
615 RAISE fnd_api.g_exc_unexpected_error;
616 ELSIF l_sql_return <> -1 THEN
617 EXIT;
618 END IF;
619 END LOOP;
620
621 if l_debug = 1 then
622 log_statement(l_api_name, 'l_sql_return ', l_sql_return );
623 log_statement(l_api_name, '','Finished Call to execute_CG_rule');
624 end if;
625
626 IF l_sql_return > 0 THEN -- the rule matches the task
627 if l_debug = 1 THEN
628 log_event(l_api_name, '', 'Rule succeeded');
629 end if;
630 OPEN l_rule_cg;
631 FETCH l_rule_cg INTO l_cost_group_id;
632 IF l_rule_cg%NOTFOUND OR l_cost_group_id IS NULL THEN
633 --if rule does not have cost group id, don't raise error;
634 --just get cost group id from sub/org
635 if l_debug = 1 then
636 log_event(l_api_name, '',
637 'CostGroup id not found');
638 end if;
639 l_cost_group_id := NULL;
640 END IF;
641 CLOSE l_rule_cg;
642
643 IF l_debug = 1 THEN
644 log_event(l_api_name, '',
645 'found Cost group id: ' ||
646 l_cost_group_id);
647 end if;
648 -- set global value for simulation form
649 wms_search_order_globals_pvt.g_costgroup_rule_id := l_rule_id;
650 ELSE
651 if l_debug = 1 then
652 log_event(l_api_name, '',
653 'No cost group found');
654 end if;
655 END IF;
656 -- close the rule package cursor
657 -- execute_close_rule(l_rule_id);
658
659 -- cost group assigned, jump out of the rule loop
660 EXIT when l_cost_group_id IS NOT NULL OR
661 l_cg_rule_id is NOT NULL OR
662 l_simulation_mode = g_rule_mode;
663
664 END LOOP;
665
666 END IF;
667 if l_debug = 1 then
668 log_statement(l_api_name, '',
669 'Finished checking rules for cg');
670 end if;
671 --if no cost group found, then get cost group from sub or org
672 IF l_cost_group_id IS NULL THEN
673 if l_debug = 1 then
674 log_event(l_api_name, '',
675 'cost group not found using rules engine.');
676 end if;
677
678
679 --set global for no cg found from rule; used in sim form
680 wms_search_order_globals_pvt.g_costgroup_rule_id := -999;
681
682 --if to_sub is defined, get default cost group from there
683 IF (l_to_subinventory_code IS NOT NULL) THEN
684 if l_debug = 1 then
685 log_event(l_api_name, '',
686 'Getting default cost group fo sub' ||
687 l_to_subinventory_code);
688 end if;
689
690 OPEN l_default_cg_sub;
691 FETCH l_default_cg_sub into l_cost_group_id;
692 IF l_default_cg_sub%NOTFOUND OR l_cost_group_id IS NULL THEN
693 --don't raise error, just try to get cg from org
694 l_cost_group_id := NULL;
695 if l_debug = 1 then
696 log_event(l_api_name, '',
697 'No default cost group for dest sub');
698 end if;
699 ELSE
700 if l_debug = 1 then
701 log_event(l_api_name, '',
702 'Cost group id: ' || l_cost_group_id);
703 end if;
704 END IF;
705 CLOSE l_default_cg_sub;
706
707 END IF;
708
709 --if no to_sub, or default cost group not defined at to_sub,
710 -- get default cost group from org
711 -- 10.30.00 - now, we only get the cost group at org level for
712 -- transaction records. For move order lines, it's better
713 -- to let the put away engine find a to_sub, and then use that
714 -- sub's cost group
715 -- {{[ Test Case # UTK-REALLOC-3.1.3:3f
716 -- Description: Strategy search based on rule and strategy assignments
717 -- Uses default rule_id , if stg_id / rule_id is not returned ]}}
718 IF (l_cost_group_id IS NULL and p_input_type = g_input_mmtt) THEN
719 if l_debug = 1 then
720 log_event(l_api_name, '',
721 'Getting the default cost group orga ' ||
722 l_organization_id);
723 end if;
724 OPEN l_default_cg_org;
725 FETCH l_default_cg_org into l_cost_group_id;
726 IF (l_default_cg_org%NOTFOUND OR l_cost_group_id IS NULL) THEN
727 --raise error here
728 CLOSE l_default_cg_org;
729 fnd_message.set_name('INV','INV_NO_DEFAULT_COST_GROUP');
730 fnd_msg_pub.add;
731 if l_debug = 1 then
732 log_error_msg(l_api_name, 'no_default_org_cg');
733 end if;
734 raise fnd_api.g_exc_error;
735 END IF;
736 CLOSE l_default_cg_org;
737 if l_debug = 1 then
738 log_event(l_api_name, 'default_cg_for_org',
739 'Cost group id: ' || l_cost_group_id);
740 end if;
741 --This should only happen for mtrl records
742 ELSIF l_cost_group_id IS NULL THEN
743 if l_debug = 1 then
744 log_event(l_api_name, '',
745 'Found no costgroup for this move order line.');
746 end if;
747 END IF;
748
749
750 END IF;
751
752 -- update mmtt or mtrl with cost group id
753 IF l_cost_group_id IS NOT NULL THEN
754 --if input_line_type = MMTT
755 if (p_input_type = g_input_mmtt) then
756 --IF Transfer or intransit, update transfer_cost_group_id
757 IF (l_transaction_action_id IN (2,3,28,21)) THEN
758 UPDATE mtl_material_transactions_temp mmtt
759 SET mmtt.transfer_cost_group_id = l_cost_group_id
760 WHERE mmtt.transaction_temp_id = p_line_id;
761
762 --else, update cost_group_id
763 ELSE
764 UPDATE mtl_material_transactions_temp mmtt
765 SET mmtt.cost_group_id = l_cost_group_id
766 WHERE mmtt.transaction_temp_id = p_line_id;
767 END IF;
768
769 --else if input_line_type Move Order
770 else
771 UPDATE mtl_txn_request_lines mtrl
772 SET mtrl.to_cost_group_id = l_cost_group_id
773 WHERE mtrl.line_id = p_line_id;
774 end if;
775
776 --update global value for simulation form
777 wms_search_order_globals_pvt.g_costgroup_id := l_cost_group_id;
778 END IF;
779
780 -- Standard check of p_commit
781 IF fnd_api.to_boolean(p_commit) THEN
782 COMMIT WORK;
783 END IF;
784
785 -- debugging portion
786 -- can be commented out for final code
787
788 if l_debug = 1 then
789 log_procedure(l_api_name, '', 'End Assign_Cost_Group');
790 end if;
791 EXCEPTION
792
793 WHEN fnd_api.g_exc_error THEN
794
795 ROLLBACK TO assignCGSP;
796 x_return_status := fnd_api.g_ret_sts_error;
797 fnd_msg_pub.count_and_get( p_count => x_msg_count
798 ,p_data => x_msg_data );
799 if l_debug = 1 then
800 log_error(l_api_name, 'error', 'Error - ' || x_msg_data);
801 end if;
802
803 WHEN OTHERS THEN
804
805 ROLLBACK TO assignCGSP;
806 x_return_status := fnd_api.g_ret_sts_unexp_error;
807 fnd_msg_pub.count_and_get( p_count => x_msg_count
808 ,p_data => x_msg_data );
809 if l_debug = 1 then
810 log_error(l_api_name,'unexp_error','Unexpected error - ' || x_msg_data);
811 end if;
812
813 END assign_cost_group;
814
815
816 FUNCTION GetCurrentInputType RETURN NUMBER IS
817
818 BEGIN
819
820 Return g_current_input_type;
821
822 END GetCurrentInputType;
823
824
825
826 END WMS_CostGroupEngine_PVT;
[Home] [Help]