1 PACKAGE BODY WMS_CostGroupEngine_PVT AS
2 /* $Header: WMSVPPGB.pls 120.3 2005/06/20 21:33:47 appldev 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 CURSOR c_input_line IS
169 SELECT organization_id
170 ,to_subinventory_code
171 ,transaction_action_id
172 ,to_organization_id
173 FROM wms_cost_groups_input_v
174 WHERE line_id = p_line_id;
175
176 CURSOR c_fob_point IS
177 SELECT fob_point
178 FROM MTL_INTERORG_PARAMETERS
179 WHERE from_organization_id = l_organization_id/*changed from l_source_organization_id for 3224420*/
180 AND to_organization_id = l_transfer_organization_id;
181
182 --cursor used to determine if the rule package exists
183 CURSOR l_pack_gen IS
184 SELECT count(object_name)
185 FROM user_objects
186 WHERE object_name = l_package_name;
187
188 --cursor to get the cost group from the rule
189 CURSOR l_rule_cg IS
190 SELECT type_hdr_id
191 FROM wms_rules_b
192 WHERE rule_id = l_rule_id;
193
194 --cursor used to get default cost group from sub
195 CURSOR l_default_cg_sub IS
196 SELECT default_cost_group_id
197 FROM mtl_secondary_inventories
198 WHERE secondary_inventory_name = l_to_subinventory_code
199 AND organization_id = l_organization_id;
200
201
202 --cursor used to get default cost group from org
203 CURSOR l_default_cg_org IS
204 SELECT default_cost_group_id
205 FROM mtl_parameters
206 WHERE organization_id = l_organization_id;
207
208 BEGIN
209
210 SAVEPOINT assignCGSP;
211
212 -- l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
213 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
214 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
215 END IF;
216 l_debug := g_debug;
217
218 -- Standard call to check for call compatibility
219 IF NOT fnd_api.compatible_api_call( l_api_version
220 ,p_api_version
221 ,l_api_name
222 ,g_pkg_name ) THEN
223 RAISE fnd_api.g_exc_unexpected_error;
224 END IF;
225
226
227 -- Initialize message list if p_init_msg_list is set to TRUE
228 IF fnd_api.to_boolean( p_init_msg_list ) THEN
229 fnd_msg_pub.initialize;
230 END IF;
231 --
232 -- Initialize API return status to success
233 x_return_status := fnd_api.g_ret_sts_success;
234
235 -- debugging portion
236 -- can be commented out for final code
237
238 IF l_debug = 1 THEN
239 log_procedure(l_api_name, '', 'Start Assign_Cost_Group');
240 log_procedure (l_api_name, 'Line_id', p_line_id);
241 log_procedure (l_api_name, 'Input type', p_input_type);
242 END IF;
243
244 -- Validate input parameters and pre-requisites, if validation level
245 -- requires this
246 IF p_validation_level <> fnd_api.g_valid_level_none THEN
247
248 --check for null line_id
249 if (p_line_id IS NULL) then
250 fnd_message.set_name('WMS','WMS_CG_MISSING_LINE_ID');
251 fnd_msg_pub.add;
252 log_error_msg(l_api_name, 'missing_line_id');
253 raise fnd_api.g_exc_error;
254 end if;
255
256 --check for null type code
257 if (p_input_type IS NULL) then
258 fnd_message.set_name('WMS','WMS_CG_MISSING_INPUT_TYPE');
259 fnd_msg_pub.add;
260 log_error_msg(l_api_name, 'missing_input_type');
261 raise fnd_api.g_exc_error;
262 end if;
263
264 END IF;
265
266 IF p_input_type = g_input_mmtt THEN
267 If l_debug = 1 then
268 log_event(l_api_name, 'mmtt_line', 'Update a transaction with ' ||
269 'transaction temp id: ' || p_line_id);
270 end if;
271 ELSIF p_input_type = g_input_mtrl THEN
272 If l_debug = 1 then
273 log_event(l_api_name, 'mmtt_line', 'Update a move order with ' ||
274 'move order line id: ' || p_line_id);
275 end if;
276 ELSE
277 fnd_message.set_name('WMS','WMS_CG_MISSING_INPUT_TYPE');
278 fnd_msg_pub.add;
279 log_error_msg(l_api_name, 'invalid_input_type');
280 raise fnd_api.g_exc_error;
281 END IF;
282
283 --set record type in global variable
284 g_current_input_type := p_input_type;
285
286 --validate simulation mode parameters
287 IF p_simulation_mode < g_no_simulation OR
288 p_simulation_mode > g_rule_mode THEN
289 l_simulation_mode := g_no_simulation;
290 ELSIF p_simulation_mode IN (g_rule_mode, g_strategy_mode) AND
291 p_simulation_id IS NULL THEN
292 l_simulation_mode := g_no_simulation;
293 ELSE
294 l_simulation_mode := p_simulation_mode;
295 END IF;
296
297 --get input line from view; store in record
298 OPEN c_input_line;
299 FETCH c_input_line INTO l_organization_id
300 ,l_to_subinventory_code
301 ,l_transaction_action_id
302 ,l_transfer_organization_id;
303 IF (c_input_line%NOTFOUND) THEN
304 CLOSE c_input_line;
305 fnd_message.set_name('WMS','WMS_CG_LINE_NOT_FOUND');
306 fnd_msg_pub.add;
307 log_error_msg(l_api_name, 'line_not_found');
308 raise fnd_api.g_exc_error;
309 END IF;
310 CLOSE c_input_line;
311
312 -- Determine which organization to use.
313 -- If direct org transfer, use the transfer_org, since that is the
314 -- receiving org. If intransit receipt, we store the organization_id
315 -- in the to_organization_id in the view.
316
317 IF l_transaction_action_id IN (3,12) THEN
318 l_organization_id := l_transfer_organization_id;
319 -- For intransit shipment, the org to run rules on depends on the
320 -- fob point flag
321 ELSIF l_transaction_action_id = 21 THEN
322 OPEN c_fob_point;
323 FETCH c_fob_point INTO l_fob_point;
324 If c_fob_point%NOTFOUND Then
325 l_fob_point:= NULL;
326 End If;
327 CLOSE c_fob_point;
328
329 --if fob point = 1, then the ownership change occurs at issue.
330 -- If this is true, then we need to run rules on destination org
331 If l_fob_point IS NOT NULL and l_fob_point = 1 Then
332 l_organization_id := l_transfer_organization_id;
333 /*3224420Else
334 l_organization_id := l_source_organization_id;*/
335 End If;
336 /*3224220ELSE
337 l_organization_id := l_source_organization_id;*/
338 END IF;
339
340 if l_debug = 1 then
341 log_statement(l_api_name, 'org_id', 'Running the engine on org ' ||
342 l_organization_id);
343 end if;
344
345 --get Strategy
346 IF l_simulation_mode = g_no_simulation THEN
347 if l_debug = 1 then
348 log_event(l_api_name, '', 'Calling the Strategy Search function');
349 end if;
350
351 If (g_current_input_type = g_input_mmtt) then
352 if l_debug = 1 then
353 log_event(l_api_name, '', 'wms_rules_workbench_pvt.cg_mmtt_search');
354 log_event(l_api_name, 'p_transaction_temp_id',p_line_id);
355 log_event(l_api_name, 'p_organization_id', l_organization_id);
356 end if;
357 wms_rules_workbench_pvt.cg_mmtt_search
358 ( p_api_version => 1.0
359 ,p_init_msg_list => fnd_api.g_false
360 ,p_validation_level => fnd_api.g_valid_level_none
361 ,x_return_status => l_return_status
362 ,x_msg_count => x_msg_count
363 ,x_msg_data => x_msg_data
364 ,p_transaction_temp_id => p_line_id
365 ,p_type_code => 5
366 ,x_return_type => l_return_type
367 ,x_return_type_id => l_return_type_id
368 ,p_organization_id => l_organization_id
369 ,x_sequence_number => l_sequence_number );
370 if l_debug = 1 then
371 log_statement(l_api_name, 'l_return_type', l_return_type);
372 log_statement(l_api_name, 'l_return_type_id', l_return_type_id);
373 log_statement(l_api_name, 'l_organization_id', l_organization_id);
374 log_statement(l_api_name, 'l_sequence_number ', l_sequence_number );
375 log_event(l_api_name, 'cg_mmtt_search', 'End');
376 end if;
377 elsif (g_current_input_type = g_input_mtrl) then
378 if l_debug = 1 then
379 log_event(l_api_name, 'Search', 'wms_rules_workbench_pvt.search');
380 end if;
381 wms_rules_workbench_pvt.Search
382 ( p_api_version => 1.0
383 ,p_init_msg_list => fnd_api.g_false
384 ,p_validation_level => fnd_api.g_valid_level_none
385 ,x_return_status => l_return_status
386 ,x_msg_count => x_msg_count
387 ,x_msg_data => x_msg_data
388 ,p_transaction_temp_id => p_line_id
389 ,p_type_code => 5
390 ,x_return_type => l_return_type
391 ,x_return_type_id => l_return_type_id
392 ,p_organization_id => l_organization_id
393 ,x_sequence_number => l_sequence_number);
394
395 if l_debug = 1 then
396 log_statement(l_api_name, ': l_return_type', l_return_type);
397 log_statement(l_api_name, ': l_return_type_id', l_return_type_id);
398 log_statement(l_api_name, ': l_organization_id', l_organization_id);
399 log_statement(l_api_name, ': l_sequence_number ', l_sequence_number );
400 log_event(l_api_name, 'search', 'End');
401 end if;
402 End if;
403 if l_return_status = fnd_api.g_ret_sts_unexp_error then
404 if l_debug = 1 THEN
405 log_error(l_api_name, 'strat_search_unexp_err',
406 'Unexpected error in wms_strategy_pvt search procedure');
407 end if;
408 raise fnd_api.g_exc_unexpected_error;
409
410 elsif l_return_status = fnd_api.g_ret_sts_error then
411 if l_debug = 1 then
412 log_error(l_api_name, 'strat_search_err',
416 elsif l_return_status = fnd_api.g_ret_sts_success then
413 'Error in wms_strategy_pvt search procedure');
414 end if;
415 raise fnd_api.g_exc_error;
417 --If l_rules_engine_mode = 1 then
418 -- {{[ Test Case # UTK-REALLOC-3.1.3:3c
419 -- Description: Strategy search based on rule and strategy assignments
420 -- Searching Cost Group rule assignments Misc Receipt ] }}
421
422 -- {{[ Test Case # UTK-REALLOC-3.1.3:3m
423 -- Description: Strategy search based on rule and strategy assignments
424 -- Searching Cost Group rule assignments PO Receipt ] }}
425
426 if l_return_type = 'S' then
427 l_strategy_id :=l_return_type_id;
428 l_cg_rule_id := NULL;
429 elsif l_return_type = 'R' then
430 l_cg_rule_id :=l_return_type_id;
431 l_strategy_id := NULL;
432 elsif l_return_type = 'V' then
433 l_cost_group_id:=l_return_type_id;
434 l_strategy_id := NULL;
435 l_cg_rule_id := NULL;
436 else
437 l_strategy_id := NULL;
438 l_cg_rule_id := NULL;
439 l_cost_group_id:=NULL;
440 end if;
441 End If;
442 --end if;
443
444 if l_debug = 1 then
445 if l_strategy_id is not null then
446 log_event(l_api_name, 'strategy_found','Strategy id:'|| l_strategy_id);
447 elsif l_cg_rule_id is not null then
448 log_event(l_api_name, 'Rule_found','Rule id:'|| l_cg_rule_id);
449 elsif l_cost_group_id is not null then
450 log_event(l_api_name, 'Direct Value Found ','Rule id:'|| l_cost_group_id);
451 end if;
452 end if;
453 ELSIF l_simulation_mode = g_strategy_mode THEN
454 l_strategy_id := p_simulation_id;
455 -- May have to add code if simulation is done for rule
456 ELSE
457 l_strategy_id := NULL;
458 l_cg_rule_id := NULL;
459 END IF;
460
461 if l_debug = 1 then
462 if l_strategy_id is not NULL then
463 log_statement(l_api_name, '', 'Using strategy:' || l_strategy_id);
464 elsif l_cg_rule_id is not NULL then
465 log_statement(l_api_name, '', 'Using rule:' || l_cg_rule_id);
466 elsif l_cost_group_id is not null then
467 log_statement(l_api_name, '', 'Using Value:' || l_cost_group_id);
468 end if;
469 end if;
470 -- Get rules within that strategy
471 -- Initialize the internal rules table
472 IF l_strategy_id IS NOT NULL THEN
473 wms_strategy_pvt.InitStrategyRules ( l_return_status
474 ,x_msg_count
475 ,x_msg_data
476 ,l_strategy_id );
477
478 if l_return_status = fnd_api.g_ret_sts_unexp_error then
479 if l_debug = 1 then
480 log_error(l_api_name, 'init_rules_unexp_err',
481 'Unexpected error in wms_strategy_pvt InitStrategyRules');
482 end if;
483 raise fnd_api.g_exc_unexpected_error;
484 elsif l_return_status = fnd_api.g_ret_sts_error then
485 if l_debug = 1 then
486 log_error(l_api_name, 'init_rules_err',
487 'Error in wms_strategy_pvt InitStrategyRules');
488 end if;
489 raise fnd_api.g_exc_error;
490 end if;
491 END IF;
492 -- Loop through all the rules, until all input lines are satisfied
493 -- [ call the rule and exit from the loop ]
494 --
495 -- {{[ Test Case # UTK-REALLOC-3.1.3:3d
496 -- Description: Strategy search based on rule and strategy assignments
497 -- Make sure searching all the rules in the strategy , if stg_id returned ] }}
498
499 -- {{[ Test Case # UTK-REALLOC-3.1.3:3e
500 -- Description: Strategy search based on rule and strategy assignments
501 -- Calling single rule , if rule_id returned ]}}
502
503
504 IF(l_cost_group_id is NULL) THEN
505 if l_debug = 1 then
506 log_statement(l_api_name, 'l_cost_group_id is NULL', 'Needs to Derive');
507 end if;
508 WHILE l_strategy_id IS NOT NULL OR
509 p_simulation_mode = g_rule_mode OR
510 l_cg_rule_id is NOT NULL LOOP
511 --- added for Patchset 'K'
512
513 if l_debug = 1 then
514 log_event(l_api_name, 'Inside While ', 'p_simulation_mode== ' ||p_simulation_mode || 'g_rule_mode== ' ||g_rule_mode);
515
516 end if;
517
518 --get id of next rule in strategy , if workbench returns the strategy_id
519 --for cost group rules, we don't use partial success flag
520
521 If p_simulation_mode = g_rule_mode Then
522 l_rule_id := p_simulation_id;
523 ELSIF l_cg_rule_id is not NULL THEN -- added this code for patchset 'K'
524 l_rule_id := l_cg_rule_id;
525 Else
526
527 wms_re_common_pvt.GetNextRule(
528 x_rule_id => l_rule_id
529 ,x_partial_success_allowed_flag => l_partial_flag);
530 End If;
531 EXIT WHEN l_rule_id IS NULL;
532 if l_debug = 1 then
533 log_event(l_api_name, '', 'Current rule ' ||l_rule_id);
534 log_statement(l_api_name, '',
538 wms_rule_pvt.GetPackageName(l_rule_id, l_package_name);
535 'calling GetPackageName( '||l_rule_id ||',' ||l_package_name ||')');
536 end if;
537 -- get the pre-generated package name for this rule
539 if l_debug = 1 then
540 log_statement(l_api_name, '',
541 'Calling open_curs -' || l_package_name);
542 log_statement(l_api_name, 'l_rule_id', l_rule_id);
543 log_statement(l_api_name, 'l_sql_return ', l_sql_return );
544 log_statement(l_api_name, 'Before Entering Loop ', '');
545 END IF;
546
547 For l_rule_counter IN 1..2 LOOP
548 execute_CG_rule(l_rule_id, p_line_id,l_sql_return);
549
550 IF l_debug = 1 THEN
551 log_statement(l_api_name, 'execute_CG_rule Loop (l_rule_counter ) ', l_rule_counter);
552 log_statement(l_api_name, 'l_sql_return ', l_sql_return );
553 END IF;
554
555 IF (l_sql_return = -1 ) and l_rule_counter = 2 THEN --error
556 fnd_message.set_name('WMS', 'WMS_PACKAGE_MISSING');
557 fnd_message.set_token('RULEID', l_rule_id);
558 fnd_msg_pub.ADD;
559 if l_debug = 1 then
560 log_statement(l_api_name, 'l_sql_return ', l_sql_return );
561 log_error_msg(l_api_name, 'rule_package_missing');
562 log_statement(l_api_name,'', 'Package name: ' || l_package_name);
563 end if;
564 RAISE fnd_api.g_exc_unexpected_error;
565 ELSIF l_sql_return <> -1 THEN
566 EXIT;
567 END IF;
568 END LOOP;
569
570 if l_debug = 1 then
571 log_statement(l_api_name, 'l_sql_return ', l_sql_return );
572 log_statement(l_api_name, '','Finished Call to execute_CG_rule');
573 end if;
574
575 IF l_sql_return > 0 THEN -- the rule matches the task
576 if l_debug = 1 THEN
577 log_event(l_api_name, '', 'Rule succeeded');
578 end if;
579 OPEN l_rule_cg;
580 FETCH l_rule_cg INTO l_cost_group_id;
581 IF l_rule_cg%NOTFOUND OR l_cost_group_id IS NULL THEN
582 --if rule does not have cost group id, don't raise error;
583 --just get cost group id from sub/org
584 if l_debug = 1 then
585 log_event(l_api_name, '',
586 'CostGroup id not found');
587 end if;
588 l_cost_group_id := NULL;
589 END IF;
590 CLOSE l_rule_cg;
591
592 IF l_debug = 1 THEN
593 log_event(l_api_name, '',
594 'found Cost group id: ' ||
595 l_cost_group_id);
596 end if;
597 -- set global value for simulation form
598 wms_search_order_globals_pvt.g_costgroup_rule_id := l_rule_id;
599 ELSE
600 if l_debug = 1 then
601 log_event(l_api_name, '',
602 'No cost group found');
603 end if;
604 END IF;
605 -- close the rule package cursor
606 -- execute_close_rule(l_rule_id);
607
608 -- cost group assigned, jump out of the rule loop
609 EXIT when l_cost_group_id IS NOT NULL OR
610 l_cg_rule_id is NOT NULL OR
611 l_simulation_mode = g_rule_mode;
612
613 END LOOP;
614
615 END IF;
616 if l_debug = 1 then
617 log_statement(l_api_name, '',
618 'Finished checking rules for cg');
619 end if;
620 --if no cost group found, then get cost group from sub or org
621 IF l_cost_group_id IS NULL THEN
622 if l_debug = 1 then
623 log_event(l_api_name, '',
624 'cost group not found using rules engine.');
625 end if;
626
627
628 --set global for no cg found from rule; used in sim form
629 wms_search_order_globals_pvt.g_costgroup_rule_id := -999;
630
631 --if to_sub is defined, get default cost group from there
632 IF (l_to_subinventory_code IS NOT NULL) THEN
633 if l_debug = 1 then
634 log_event(l_api_name, '',
635 'Getting default cost group fo sub' ||
636 l_to_subinventory_code);
637 end if;
638
639 OPEN l_default_cg_sub;
640 FETCH l_default_cg_sub into l_cost_group_id;
641 IF l_default_cg_sub%NOTFOUND OR l_cost_group_id IS NULL THEN
642 --don't raise error, just try to get cg from org
643 l_cost_group_id := NULL;
644 if l_debug = 1 then
645 log_event(l_api_name, '',
646 'No default cost group for dest sub');
647 end if;
648 ELSE
649 if l_debug = 1 then
650 log_event(l_api_name, '',
651 'Cost group id: ' || l_cost_group_id);
652 end if;
653 END IF;
654 CLOSE l_default_cg_sub;
655
656 END IF;
657
658 --if no to_sub, or default cost group not defined at to_sub,
659 -- get default cost group from org
660 -- 10.30.00 - now, we only get the cost group at org level for
661 -- transaction records. For move order lines, it's better
662 -- to let the put away engine find a to_sub, and then use that
663 -- sub's cost group
667 IF (l_cost_group_id IS NULL and p_input_type = g_input_mmtt) THEN
664 -- {{[ Test Case # UTK-REALLOC-3.1.3:3f
665 -- Description: Strategy search based on rule and strategy assignments
666 -- Uses default rule_id , if stg_id / rule_id is not returned ]}}
668 if l_debug = 1 then
669 log_event(l_api_name, '',
670 'Getting the default cost group orga ' ||
671 l_organization_id);
672 end if;
673 OPEN l_default_cg_org;
674 FETCH l_default_cg_org into l_cost_group_id;
675 IF (l_default_cg_org%NOTFOUND OR l_cost_group_id IS NULL) THEN
676 --raise error here
677 CLOSE l_default_cg_org;
678 fnd_message.set_name('INV','INV_NO_DEFAULT_COST_GROUP');
679 fnd_msg_pub.add;
680 if l_debug = 1 then
681 log_error_msg(l_api_name, 'no_default_org_cg');
682 end if;
683 raise fnd_api.g_exc_error;
684 END IF;
685 CLOSE l_default_cg_org;
686 if l_debug = 1 then
687 log_event(l_api_name, 'default_cg_for_org',
688 'Cost group id: ' || l_cost_group_id);
689 end if;
690 --This should only happen for mtrl records
691 ELSIF l_cost_group_id IS NULL THEN
692 if l_debug = 1 then
693 log_event(l_api_name, '',
694 'Found no costgroup for this move order line.');
695 end if;
696 END IF;
697
698
699 END IF;
700
701 -- update mmtt or mtrl with cost group id
702 IF l_cost_group_id IS NOT NULL THEN
703 --if input_line_type = MMTT
704 if (p_input_type = g_input_mmtt) then
705 --IF Transfer or intransit, update transfer_cost_group_id
706 IF (l_transaction_action_id IN (2,3,28,21)) THEN
707 UPDATE mtl_material_transactions_temp mmtt
708 SET mmtt.transfer_cost_group_id = l_cost_group_id
709 WHERE mmtt.transaction_temp_id = p_line_id;
710
711 --else, update cost_group_id
712 ELSE
713 UPDATE mtl_material_transactions_temp mmtt
714 SET mmtt.cost_group_id = l_cost_group_id
715 WHERE mmtt.transaction_temp_id = p_line_id;
716 END IF;
717
718 --else if input_line_type Move Order
719 else
720 UPDATE mtl_txn_request_lines mtrl
721 SET mtrl.to_cost_group_id = l_cost_group_id
722 WHERE mtrl.line_id = p_line_id;
723 end if;
724
725 --update global value for simulation form
726 wms_search_order_globals_pvt.g_costgroup_id := l_cost_group_id;
727 END IF;
728
729 -- Standard check of p_commit
730 IF fnd_api.to_boolean(p_commit) THEN
731 COMMIT WORK;
732 END IF;
733
734 -- debugging portion
735 -- can be commented out for final code
736
737 if l_debug = 1 then
738 log_procedure(l_api_name, '', 'End Assign_Cost_Group');
739 end if;
740 EXCEPTION
741
742 WHEN fnd_api.g_exc_error THEN
743
744 ROLLBACK TO assignCGSP;
745 x_return_status := fnd_api.g_ret_sts_error;
746 fnd_msg_pub.count_and_get( p_count => x_msg_count
747 ,p_data => x_msg_data );
748 if l_debug = 1 then
749 log_error(l_api_name, 'error', 'Error - ' || x_msg_data);
750 end if;
751
752 WHEN OTHERS THEN
753
754 ROLLBACK TO assignCGSP;
755 x_return_status := fnd_api.g_ret_sts_unexp_error;
756 fnd_msg_pub.count_and_get( p_count => x_msg_count
757 ,p_data => x_msg_data );
758 if l_debug = 1 then
759 log_error(l_api_name,'unexp_error','Unexpected error - ' || x_msg_data);
760 end if;
761
762 END assign_cost_group;
763
764
765 FUNCTION GetCurrentInputType RETURN NUMBER IS
766
767 BEGIN
768
769 Return g_current_input_type;
770
771 END GetCurrentInputType;
772
773
774
775 END WMS_CostGroupEngine_PVT;
[Home] [Help]