[Home] [Help]
PACKAGE BODY: APPS.FEM_BR_MAPPING_RULE_PVT
Source
1 PACKAGE BODY FEM_BR_MAPPING_RULE_PVT AS
2 /* $Header: FEMVMAPB.pls 120.6.12010000.2 2008/10/06 17:51:21 huli ship $ */
3
4 --------------------------------------------------------------------------------
5 -- PRIVATE CONSTANTS
6 --------------------------------------------------------------------------------
7
8 G_PKG_NAME constant varchar2(30) := 'FEM_BR_MAPPING_RULE_PVT';
9 G_APPS_SHORT_NAME CONSTANT VARCHAR2 (30) := 'FEM';
10 G_LOCAL_COND_NAME CONSTANT VARCHAR2 (30) := 'LOCAL MAPPING ';
11
12 G_BLOCK constant varchar2(80) := G_APPS_SHORT_NAME ||'.PLSQL.'||G_PKG_NAME;
13
14 -- Log Level Constants
15 G_LOG_LEVEL_1 constant number := FND_LOG.Level_Statement;
16 G_LOG_LEVEL_2 constant number := FND_LOG.Level_Procedure;
17 G_LOG_LEVEL_3 constant number := FND_LOG.Level_Event;
18 G_LOG_LEVEL_4 constant number := FND_LOG.Level_Exception;
19 G_LOG_LEVEL_5 constant number := FND_LOG.Level_Error;
20 G_LOG_LEVEL_6 constant number := FND_LOG.Level_Unexpected;
21
22
23 --------------------------------------------------------------------------------
24 -- PRIVATE SPECIFICATIONS
25 --------------------------------------------------------------------------------
26 PROCEDURE DeleteBrObjectRec(
27 p_obj_id in number
28 );
29
30 PROCEDURE DeleteHelperRecs(
31 p_obj_def_id in number
32 );
33
34 PROCEDURE DeleteMappingRuleRec(
35 p_obj_def_id in number
36 );
37
38 PROCEDURE DeleteFormulaRecs(
39 p_obj_def_id in number
40 );
41
42 PROCEDURE DeleteDimensionRecs(
43 p_obj_def_id in number
44 );
45
46 PROCEDURE CopyMappingRuleRec(
47 p_source_obj_def_id in number
48 ,p_target_obj_def_id in number
49 ,p_created_by in number
50 ,p_creation_date in date
51 );
52
53 PROCEDURE CopyBrObjectRec(
54 p_source_obj_id in number
55 ,p_target_obj_id in number
56 ,p_created_by in number
57 ,p_creation_date in date
58 );
59
60
61 PROCEDURE CopyFormulaRecs(
62 p_copy_type_code in varchar2
63 ,p_source_obj_def_id in number
64 ,p_target_obj_def_id in number
65 ,p_created_by in number
66 ,p_creation_date in date
67 );
68
69 PROCEDURE CopyDimensionRecs(
70 p_source_obj_def_id in number
71 ,p_target_obj_def_id in number
72 ,p_created_by in number
73 ,p_creation_date in date
74 );
75
76 FUNCTION Get_Cond_Obj_Def_Id (p_obj_id NUMBER )
77 RETURN NUMBER;
78
79 FUNCTION Get_Mapping_Rule_Condition
80 (p_obj_def_id NUMBER,
81 p_func_seq NUMBER
82 ) RETURN NUMBER;
83
84 FUNCTION IS_TABLE_ENABLED (p_table_name varchar2)
85 RETURN BOOLEAN;
86
87
88 PROCEDURE delete_dimension_rec (
89 p_obj_def_id in number
90 ,p_func_seq in number);
91
92
93 PROCEDURE populate_dimension_recs (
94 p_table_name in varchar2
95 ,p_obj_def_id in number
96 ,p_func_seq in number
97 ,p_function_cd in varchar2
98 ,p_column_property_code in varchar2
99 ,p_alloc_dim_usage_code in varchar2
100 ,p_post_to_balance_flag in varchar2
101 ,p_percent_distribution_code in varchar2);
102
103
104 PROCEDURE synchronize_dimension_recs (
105 p_table_name in varchar2
106 ,p_obj_def_id in number
107 ,p_func_seq in number
108 ,p_function_cd in varchar2
109 ,p_column_property_code in varchar2
110 ,p_alloc_dim_usage_code in varchar2
111 ,p_post_to_balance_flag in varchar2
112 ,p_percent_distribution_code in varchar2);
113
114
115
116 --------------------------------------------------------------------------------
117 -- PUBLIC BODIES
118 --------------------------------------------------------------------------------
119 --
120 -- PROCEDURE
121 -- DeleteObjectDetails
122 --
123 -- DESCRIPTION
124 -- Deletes the object extension data from fem_alloc_br_objects
125 -- for mapping rules.
126 --
127 -- IN
128 -- p_obj_id - Object ID.
129 --
130 PROCEDURE DeleteObjectDetails (
131 p_obj_id in number
132 )
133
134 IS
135 g_api_name constant varchar2(30) := 'DeleteObjectDetails';
136
137 BEGIN
138
139 DeleteBrObjectRec (
140 p_obj_id => p_obj_id
141 );
142
143 EXCEPTION
144
145 when others then
146 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, g_api_name);
147 raise FND_API.G_EXC_UNEXPECTED_ERROR;
148
149 END DeleteObjectDetails;
150
151 --
152 -- PROCEDURE
153 -- DeleteObjectDefinition
154 --
155 -- DESCRIPTION
156 -- Deletes all the details records of a Mapping Rule Definition.
157 --
158 -- IN
159 -- p_obj_def_id - Object Definition ID.
160 --
161 --------------------------------------------------------------------------------
162 PROCEDURE DeleteObjectDefinition(
163 p_obj_def_id in number
164 )
165 --------------------------------------------------------------------------------
166 IS
167
168 l_api_name constant varchar2(30) := 'DeleteObjectDefinition';
169 l_prg_msg VARCHAR2(2000);
170 l_callstack VARCHAR2(2000);
171 BEGIN
172
173 FEM_ENGINES_PKG.Tech_Message (
174 p_severity => G_LOG_LEVEL_3
175 ,p_module => G_BLOCK||'.'||l_api_name
176 ,p_msg_text => 'BEGIN, p_obj_def_id:' || p_obj_def_id
177 );
178
179
180 DeleteHelperRecs(
181 p_obj_def_id => p_obj_def_id
182 );
183
184 FEM_ENGINES_PKG.Tech_Message (
185 p_severity => G_LOG_LEVEL_3
186 ,p_module => G_BLOCK||'.'||l_api_name
187 ,p_msg_text => 'After DeleteHelperRecs'
188 );
189
190 DeleteDimensionRecs(
191 p_obj_def_id => p_obj_def_id
192 );
193
194 FEM_ENGINES_PKG.Tech_Message (
195 p_severity => G_LOG_LEVEL_3
196 ,p_module => G_BLOCK||'.'||l_api_name
197 ,p_msg_text => 'After DeleteDimensionRecs' );
198
199 DeleteFormulaRecs(
200 p_obj_def_id => p_obj_def_id
201 );
202 FEM_ENGINES_PKG.Tech_Message (
203 p_severity => G_LOG_LEVEL_3
204 ,p_module => G_BLOCK||'.'||l_api_name
205 ,p_msg_text => 'After DeleteFormulaRecs' );
206
207 DeleteMappingRuleRec(
208 p_obj_def_id => p_obj_def_id
209 );
210
211 FEM_ENGINES_PKG.Tech_Message (
212 p_severity => G_LOG_LEVEL_3
213 ,p_module => G_BLOCK||'.'||l_api_name
214 ,p_msg_text => 'After DeleteMappingRuleRec' );
215
216 EXCEPTION
217
218 when others then
219 l_callstack := DBMS_UTILITY.Format_Call_Stack;
220 l_prg_msg := SQLERRM;
221 FEM_ENGINES_PKG.Tech_Message (
222 p_severity => G_LOG_LEVEL_6
223 ,p_module => G_BLOCK||'.'||l_api_name
224 ,p_msg_text => 'others mapping, l_callstack:' || l_callstack
225 );
226 FEM_ENGINES_PKG.Tech_Message (
227 p_severity => G_LOG_LEVEL_6
228 ,p_module => G_BLOCK||'.'||l_api_name
229 ,p_msg_text => 'others mapping, l_prg_msg:' || l_prg_msg
230 );
231
232 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
233 raise FND_API.G_EXC_UNEXPECTED_ERROR;
234
235 END DeleteObjectDefinition;
236
237
238 --
239 -- PROCEDURE
240 -- CopyObjectDetails
241 --
242 -- DESCRIPTION
243 -- Creates the object detail record of a new Mapping Rule (target)
244 -- by copying the object detail record of another Mapping Rule (source).
245 --
246 -- IN
247 -- p_source_obj_id - Source Object ID.
248 -- p_target_obj_id - Target Object ID.
249 -- p_created_by - FND User ID (optional).
250 -- p_creation_date - System Date (optional).
251 --
252 --------------------------------------------------------------------------------
253 PROCEDURE CopyObjectDetails(
254 p_copy_type_code in varchar2
255 ,p_source_obj_id in number
256 ,p_target_obj_id in number
257 ,p_created_by in number
258 ,p_creation_date in date
259 )
260 --------------------------------------------------------------------------------
261 IS
262
263 g_api_name constant varchar2(30) := 'CopyObjectDetails';
264
265 BEGIN
266
267 CopyBrObjectRec(
268 p_source_obj_id => p_source_obj_id
269 ,p_target_obj_id => p_target_obj_id
270 ,p_created_by => p_created_by
271 ,p_creation_date => p_creation_date
272 );
273
274
275 EXCEPTION
276
277 when others then
278 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, g_api_name);
279 raise FND_API.G_EXC_UNEXPECTED_ERROR;
280
281 END CopyObjectDetails;
282
283
284
285
286 --
287 -- PROCEDURE
288 -- CopyObjectDefinition
289 --
290 -- DESCRIPTION
291 -- Creates all the detail records of a new Mapping Rule Definition (target)
292 -- by copying the detail records of another Mapping Rule Definition (source).
293 --
294 -- IN
295 -- p_source_obj_def_id - Source Object Definition ID.
296 -- p_target_obj_def_id - Target Object Definition ID.
297 -- p_created_by - FND User ID (optional).
298 -- p_creation_date - System Date (optional).
299 --
300 --------------------------------------------------------------------------------
301 PROCEDURE CopyObjectDefinition(
302 p_copy_type_code in varchar2
303 ,p_source_obj_def_id in number
304 ,p_target_obj_def_id in number
305 ,p_created_by in number
306 ,p_creation_date in date
307 )
308 --------------------------------------------------------------------------------
309 IS
310
311 g_api_name constant varchar2(30) := 'CopyObjectDefinition';
312
313 BEGIN
314
315 CopyMappingRuleRec(
316 p_source_obj_def_id => p_source_obj_def_id
317 ,p_target_obj_def_id => p_target_obj_def_id
318 ,p_created_by => p_created_by
319 ,p_creation_date => p_creation_date
320 );
321
322 CopyFormulaRecs(
323 p_copy_type_code => p_copy_type_code
324 ,p_source_obj_def_id => p_source_obj_def_id
325 ,p_target_obj_def_id => p_target_obj_def_id
326 ,p_created_by => p_created_by
327 ,p_creation_date => p_creation_date
328 );
329
330 CopyDimensionRecs(
331 p_source_obj_def_id => p_source_obj_def_id
332 ,p_target_obj_def_id => p_target_obj_def_id
333 ,p_created_by => p_created_by
334 ,p_creation_date => p_creation_date
335 );
336
337 EXCEPTION
338
339 when others then
340 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, g_api_name);
341 raise FND_API.G_EXC_UNEXPECTED_ERROR;
342
343 END CopyObjectDefinition;
344
345
346
347 --------------------------------------------------------------------------------
348 -- PRIVATE BODIES
349 --------------------------------------------------------------------------------
350
351 --
352 -- PROCEDURE
353 -- Get_Cond_Obj_Def_Id
354 --
355 -- DESCRIPTION
356 -- Retrieve the condition object definition id based on condition id
357 --
358 -- IN
359 -- p_obj_id - Condition Object ID.
360 --
361 --------------------------------------------------------------------------------
362 FUNCTION Get_Cond_Obj_Def_Id (p_obj_id NUMBER)
363 RETURN NUMBER
364 IS
365 CURSOR c_cond_def_id (p_obj_id NUMBER)
366 IS
367 select object_definition_id
368 from fem_object_definition_b
369 where object_id = p_obj_id
370 and old_approved_copy_flag = 'N';
371 l_cond_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
372 BEGIN
373 OPEN c_cond_def_id (p_obj_id);
374 FETCH c_cond_def_id INTO l_cond_def_id;
375 IF c_cond_def_id%NOTFOUND THEN
376 FEM_ENGINES_PKG.User_Message (
377 p_app_name => G_APPS_SHORT_NAME
378 ,p_msg_name => 'FEM_OBJDEFNOTFOUND_ERR'
379 );
380 CLOSE c_cond_def_id;
381 RAISE FND_API.G_EXC_ERROR;
382 ELSE
383 CLOSE c_cond_def_id;
384 END IF;
385 RETURN l_cond_def_id;
386 END;
387
388 --
389 -- PROCEDURE
390 -- Get_Mapping_Rule_Condition
391 --
392 -- DESCRIPTION
393 -- Retrieve the condition object id based on mapping rule definition id
394 -- and function sequence
395 --
396 -- IN
397 -- p_obj_def_id - Mapping rule definition ID.
398 -- p_func_seq - Corresponding function sequence
399 --
400 --------------------------------------------------------------------------------
401 FUNCTION Get_Mapping_Rule_Condition
402 (p_obj_def_id NUMBER,
403 p_func_seq NUMBER
404 ) RETURN NUMBER
405 IS
406 l_cond_id FEM_OBJECT_CATALOG_B.object_id%TYPE;
407 BEGIN
408 select sub_object_id
409 into l_cond_id
410 from fem_alloc_br_formula
411 where object_definition_id = p_obj_def_id
412 and function_seq = p_func_seq;
413 RETURN l_cond_id;
414 EXCEPTION
415 WHEN NO_DATA_FOUND THEN
416 RETURN NULL;
417 END;
418
419 --
420 -- PROCEDURE
421 -- DeleteHelperRecs
422 --
423 -- DESCRIPTION
424 -- Deletes a Mapping Rule Object data performing deletes on records
425 -- in the FEM_ALLOC_BR_OBJECTS table. Also cleans up the associated
426 -- helper rules.
427 -- Logic is as follows:
428 -- 1) For the map rule - get all helper obj defs that are in use for the
429 -- map rule obj def, but not in use for a diff map rule obj def
430 -- 2) delete each helper obj def and unregister from fem_objdef_helper_rules
431 -- table for that map rule obj def+ helper obj def combo
432 -- 3) check if the helper obj def being deleted has any other obj defs for
433 -- the helper object + helper obj type combo. If not, then delete the
434 -- helper object also
435 --
436 -- IN
437 -- p_obj_def_id - Object Definition ID.
438 --
439 --------------------------------------------------------------------------------
440 PROCEDURE DeleteHelperRecs(
441 p_obj_def_id in number
442 )
443 --------------------------------------------------------------------------------
444 IS
445 v_return_status VARCHAR2(1);
446 v_msg_count NUMBER;
447 v_msg_data VARCHAR2 (2000);
448 v_count NUMBER;
449
450
451 cursor c_helper_rules (p_rule_def_id IN NUMBER) IS
452 SELECT helper_obj_def_id, helper_object_id, helper_object_type_code
453 FROM fem_objdef_helper_rules
454 WHERE object_definition_id = p_rule_def_id
455 AND helper_obj_def_id NOT IN (select helper_obj_def_id
456 FROM fem_objdef_helper_rules
457 WHERE object_definition_id <> p_rule_def_id);
458
459
460
461 BEGIN
462
463
464 FOR helper_rule IN c_helper_rules (p_obj_def_id) LOOP
465
466 FEM_BUSINESS_RULE_PVT.DeleteObjectDefinition (
467 p_api_version => 1.0,
468 p_init_msg_list => FND_API.G_FALSE,
469 p_commit => FND_API.G_FALSE,
470 x_return_status => v_return_status,
471 x_msg_count => v_msg_count,
472 x_msg_data => v_msg_data,
473 p_object_type_code => helper_rule.helper_object_type_code,
474 p_obj_def_id => helper_rule.helper_obj_def_id);
475
476
477 IF (v_return_status = FND_API.G_RET_STS_ERROR) then
478 raise FND_API.G_EXC_ERROR;
479 ELSIF (v_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
480 raise FND_API.G_EXC_UNEXPECTED_ERROR;
481 END IF;
482
483
484 /* Have we deleted the last object definition for this helper object?
485 If so - delete the helper object also*/
486 SELECT count(*)
487 INTO v_count
488 FROM fem_object_definition_b
489 WHERE object_id = helper_rule.helper_object_id;
490
491 IF v_count = 0 THEN
492
493 FEM_BUSINESS_RULE_PVT.DeleteObject (
494 p_api_version => 1.0,
495 p_init_msg_list => FND_API.G_FALSE,
496 p_commit => FND_API.G_FALSE,
497 x_return_status => v_return_status,
498 x_msg_count => v_msg_count,
499 x_msg_data => v_msg_data,
500 p_object_type_code => helper_rule.helper_object_type_code,
501 p_obj_id => helper_rule.helper_object_id);
502
503 IF (v_return_status = FND_API.G_RET_STS_ERROR) then
504 raise FND_API.G_EXC_ERROR;
505 ELSIF (v_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
506 raise FND_API.G_EXC_UNEXPECTED_ERROR;
507 END IF;
508
509
510 END IF;
511
512 END LOOP;
513
514
515 /* Last step - delete all of the helper rule registrations for the object def
516 being deleted*/
517 DELETE FROM fem_objdef_helper_rules
518 WHERE object_definition_id = p_obj_def_id;
519
520
521 END DeleteHelperRecs;
522
523 --
524 -- PROCEDURE
525 -- DeleteBrObjectRec
526 --
527 -- DESCRIPTION
528 -- Deletes the record in FEM_ALLOC_BR_OBJECTS for the map rule object
529 --
530 -- IN
531 -- p_obj_id - Object ID.
532 --
533 --------------------------------------------------------------------------------
534 PROCEDURE DeleteBrObjectRec(
535 p_obj_id in number
536 )
537 --------------------------------------------------------------------------------
538 IS
539 BEGIN
540
541 delete from fem_alloc_br_objects
542 where map_rule_object_id = p_obj_id;
543
544 END DeleteBrObjectRec;
545
546
547 --
548 -- PROCEDURE
549 -- DeletMappingRuleRec
550 --
551 -- DESCRIPTION
552 -- Deletes a Mapping Rule Definition by performing deletes on records
553 -- in the FEM_ALLOC_BUSINESS_RULE table.
554 --
555 -- IN
556 -- p_obj_def_id - Object Definition ID.
557 --
558 --------------------------------------------------------------------------------
559 PROCEDURE DeleteMappingRuleRec(
560 p_obj_def_id in number
561 )
562 --------------------------------------------------------------------------------
563 IS
564 BEGIN
565
566 delete from fem_alloc_business_rule
567 where object_definition_id = p_obj_def_id;
568
569 END DeleteMappingRuleRec;
570
571
572 --
573 -- PROCEDURE
574 -- DeleteFormulaRecs
575 --
576 -- DESCRIPTION
577 -- Deletes Mapping Rule Definition Formulas by performing deletes on records
578 -- in the FEM_ALLOC_BR_FORMULA table.
579 --
580 -- IN
581 -- p_obj_def_id - Object Definition ID.
582 --
583 --------------------------------------------------------------------------------
584 PROCEDURE DeleteFormulaRecs(
585 p_obj_def_id in number
586 )
587 --------------------------------------------------------------------------------
588 IS
589 CURSOR c_local_cond_id (p_rule_def_id NUMBER)
590 IS
591 select sub_object_id
592 from fem_alloc_br_formula formula
593 where formula.object_definition_id = p_rule_def_id
594 and exists (select 1
595 from fem_object_catalog_b obj
596 where obj.object_id = formula.sub_object_id
597 and obj.object_type_code = 'CONDITION_MAPPING');
598
599 l_local_cond_id NUMBER;
600 l_return_status varchar2(1);
601 l_msg_count number;
602 l_msg_data varchar2 (2000);
603
604 l_api_name constant varchar2(30) := 'DeleteFormulaRecs';
605
606 BEGIN
607
608 FEM_ENGINES_PKG.Tech_Message (
609 p_severity => G_LOG_LEVEL_3
610 ,p_module => G_BLOCK||'.'||l_api_name
611 ,p_msg_text => 'BEGIN, p_obj_def_id: ' || p_obj_def_id
612 );
613
614 open c_local_cond_id (p_obj_def_id);
615 loop
616 fetch c_local_cond_id into l_local_cond_id;
617 exit when c_local_cond_id%notfound;
618 FEM_ENGINES_PKG.Tech_Message (
619 p_severity => G_LOG_LEVEL_3
620 ,p_module => G_BLOCK||'.'||l_api_name
621 ,p_msg_text => 'Before calling FEM_BUSINESS_RULE_PVT.DeleteObject, l_local_cond_id: '
622 || l_local_cond_id
623 );
624 FEM_BUSINESS_RULE_PVT.DeleteObject (
625 p_api_version => 1.0
626 ,p_init_msg_list => FND_API.G_FALSE
627 ,p_commit => FND_API.G_FALSE
628 ,x_return_status => l_return_status
629 ,x_msg_count => l_msg_count
630 ,x_msg_data => l_msg_data
631 ,p_object_type_code => 'CONDITION_MAPPING'
632 ,p_obj_id => l_local_cond_id
633 );
634
635 FEM_ENGINES_PKG.Tech_Message (
636 p_severity => G_LOG_LEVEL_3
637 ,p_module => G_BLOCK||'.'||l_api_name
638 ,p_msg_text => 'After calling FEM_BUSINESS_RULE_PVT.DeleteObject, l_local_cond_id: '
639 || l_local_cond_id
640 );
641
642 if (l_return_status = FND_API.G_RET_STS_ERROR) then
643 FEM_ENGINES_PKG.Tech_Message (
644 p_severity => G_LOG_LEVEL_3
645 ,p_module => G_BLOCK||'.'||l_api_name
646 ,p_msg_text => 'After calling FEM_BUSINESS_RULE_PVT.DeleteObject, l_return_status = FND_API.G_RET_STS_ERROR '
647 );
648
649 close c_local_cond_id;
650 raise FND_API.G_EXC_ERROR;
651 elsif (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
652 FEM_ENGINES_PKG.Tech_Message (
653 p_severity => G_LOG_LEVEL_3
654 ,p_module => G_BLOCK||'.'||l_api_name
655 ,p_msg_text => 'After calling FEM_BUSINESS_RULE_PVT.DeleteObject, l_return_status = FND_API.G_RET_STS_UNEXP_ERROR '
656 );
657 close c_local_cond_id;
658 raise FND_API.G_EXC_UNEXPECTED_ERROR;
659 end if;
660 end loop;
661
662 close c_local_cond_id;
663
664 FEM_ENGINES_PKG.Tech_Message (
665 p_severity => G_LOG_LEVEL_3
666 ,p_module => G_BLOCK||'.'||l_api_name
667 ,p_msg_text => 'END1 '
668 );
669
670 delete from fem_alloc_br_formula
671 where object_definition_id = p_obj_def_id;
672
673 FEM_ENGINES_PKG.Tech_Message (
674 p_severity => G_LOG_LEVEL_3
675 ,p_module => G_BLOCK||'.'||l_api_name
676 ,p_msg_text => 'END '
677 );
678
679 END DeleteFormulaRecs;
680
681
682 --
683 -- PROCEDURE
684 -- DeleteDimensionRecs
685 --
686 -- DESCRIPTION
687 -- Delete Mapping Rule Definition Dimensions by performing deletes on records
688 -- in the FEM_ALLOC_BR_DIMENSIONS table.
689 --
690 -- IN
691 -- p_obj_def_id - Object Definition ID.
692 --
693 --------------------------------------------------------------------------------
694 PROCEDURE DeleteDimensionRecs(
695 p_obj_def_id in number
696 )
697 --------------------------------------------------------------------------------
698 IS
699 BEGIN
700
701 delete from fem_alloc_br_dimensions
702 where object_definition_id = p_obj_def_id;
703
704 END DeleteDimensionRecs;
705
706 --
707 -- PROCEDURE
708 -- CopyBrObjectRec
709 --
710 -- DESCRIPTION
711 -- Creates a new Mapping Rule object by copying the record in the
712 -- FEM_ALLOC_BR_OBJECTS table.
713 --
714 -- IN
715 -- p_source_obj_id - Source Object ID.
716 -- p_target_obj_id - Target Object ID.
717 -- p_created_by - FND User ID (optional).
718 -- p_creation_date - System Date (optional).
719 --
720 --------------------------------------------------------------------------------
721 PROCEDURE CopyBrObjectRec(
722 p_source_obj_id in number
723 ,p_target_obj_id in number
724 ,p_created_by in number
725 ,p_creation_date in date
726 )
727 --------------------------------------------------------------------------------
728 IS
729 BEGIN
730
731 insert into fem_alloc_br_objects (
732 MAP_RULE_OBJECT_ID
733 ,MAP_RULE_TYPE_CODE
734 ,OBJECT_VERSION_NUMBER
735 ,CREATION_DATE
736 ,CREATED_BY
737 ,LAST_UPDATED_BY
738 ,LAST_UPDATE_DATE
739 ,LAST_UPDATE_LOGIN
740 ) select
741 p_target_obj_id
742 ,MAP_RULE_TYPE_CODE
743 ,OBJECT_VERSION_NUMBER
744 ,nvl(p_creation_date,creation_date)
745 ,nvl(p_created_by,created_by)
746 ,FND_GLOBAL.user_id
747 ,sysdate
748 ,FND_GLOBAL.login_id
749 from fem_alloc_br_objects
750 where map_rule_object_id = p_source_obj_id;
751
752 END CopyBrObjectRec;
753
754
755
756 --
757 -- PROCEDURE
758 -- CopyMappingRuleRec
759 --
760 -- DESCRIPTION
761 -- Creates a new Mapping Rule Definition by copying records in the
762 -- PFT_ALLOC_BUSINESS_RULE table.
763 --
764 -- IN
765 -- p_source_obj_def_id - Source Object Definition ID.
766 -- p_target_obj_def_id - Target Object Definition ID.
767 -- p_created_by - FND User ID (optional).
768 -- p_creation_date - System Date (optional).
769 --
770 --------------------------------------------------------------------------------
771 PROCEDURE CopyMappingRuleRec(
772 p_source_obj_def_id in number
773 ,p_target_obj_def_id in number
774 ,p_created_by in number
775 ,p_creation_date in date
776 )
777 --------------------------------------------------------------------------------
778 IS
779 BEGIN
780
781 insert into fem_alloc_business_rule (
782 object_definition_id
783 ,cost_contribution_flag
784 ,accumulate_flag
785 /*,source_where_clause
786 ,driver_where_clause*/
787 ,formula
788 ,created_by
789 ,creation_date
790 ,last_updated_by
791 ,last_update_date
792 ,last_update_login
793 ,object_version_number
794 ) select
795 p_target_obj_def_id
796 ,cost_contribution_flag
797 ,accumulate_flag
798 /*,source_where_clause
799 ,driver_where_clause*/
800 ,formula
801 ,nvl(p_created_by,created_by)
802 ,nvl(p_creation_date,creation_date)
803 ,FND_GLOBAL.user_id
804 ,sysdate
805 ,FND_GLOBAL.login_id
806 ,object_version_number
807 from fem_alloc_business_rule
808 where object_definition_id = p_source_obj_def_id;
809
810 END CopyMappingRuleRec;
811
812
813 --
814 -- PROCEDURE
815 -- CopyFormulaRecs
816 --
817 -- DESCRIPTION
818 -- Creates a new Mapping Rule Definition Formula by copying records in the
819 -- FEM_ALLOC_BR_FORMULA table.
820 --
821 -- IN
822 -- p_source_obj_def_id - Source Object Definition ID.
823 -- p_target_obj_def_id - Target Object Definition ID.
824 -- p_created_by - FND User ID (optional).
825 -- p_creation_date - System Date (optional).
826 --
827 --------------------------------------------------------------------------------
828 PROCEDURE CopyFormulaRecs(
829 p_copy_type_code in varchar2
830 ,p_source_obj_def_id in number
831 ,p_target_obj_def_id in number
832 ,p_created_by in number
833 ,p_creation_date in date
834 )
835 --------------------------------------------------------------------------------
836 IS
837 CURSOR c_allc_br_formula (p_obj_def_id NUMBER)
838 IS
839 select
840 p_target_obj_def_id object_definition_id
841 ,function_seq
842 ,function_cd
843 ,sub_object_id
844 ,value
845 ,table_name
846 ,column_name
847 ,math_operator_cd
848 ,formula_macro_cd
849 ,force_to_100_flg
850 ,enable_flg
851 ,post_to_ledger_flg
852 ,open_paren
853 ,close_paren
854 ,apply_to_debit_code
855 ,nvl(p_created_by,created_by) created_by
856 ,nvl(p_creation_date,creation_date) creation_date
857 ,FND_GLOBAL.user_id last_updated_by
858 ,sysdate last_update_date
859 ,FND_GLOBAL.login_id LAST_UPDATE_LOGIN
860 ,object_version_number
861 from fem_alloc_br_formula formula
862 where formula.object_definition_id = p_obj_def_id
863 and exists (select 1
864 from fem_object_catalog_b obj
865 where obj.object_id = formula.sub_object_id
866 and obj.object_type_code = 'CONDITION_MAPPING');
867
868 l_formula_rec c_allc_br_formula%ROWTYPE;
869
870 l_source_cond_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
871 l_source_cond_obj_id FEM_OBJECT_CATALOG_B.object_id%TYPE;
872
873 l_target_cond_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
874 l_target_cond_obj_id FEM_OBJECT_CATALOG_B.object_id%TYPE;
875 l_target_cond_obj_name FEM_OBJECT_CATALOG_TL.object_name%TYPE;
876 l_target_cond_obj_def_name FEM_OBJECT_DEFINITION_TL.display_name%TYPE;
877
878 l_backup_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
879 l_current_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
880
881 l_return_status varchar2(1);
882 l_msg_count number;
883 l_msg_data varchar2(2000);
884
885 BEGIN
886
887
888 insert into fem_alloc_br_formula (
889 object_definition_id
890 ,function_seq
891 ,function_cd
892 ,sub_object_id
893 ,value
894 ,table_name
895 ,column_name
896 ,math_operator_cd
897 ,formula_macro_cd
898 ,force_to_100_flg
899 ,enable_flg
900 ,post_to_ledger_flg
901 ,open_paren
902 ,close_paren
903 ,apply_to_debit_code
904 ,created_by
905 ,creation_date
906 ,last_updated_by
907 ,last_update_date
908 ,last_update_login
909 ,object_version_number
910 ) select
911 p_target_obj_def_id
912 ,function_seq
913 ,function_cd
914 ,sub_object_id
915 ,value
916 ,table_name
917 ,column_name
918 ,math_operator_cd
919 ,formula_macro_cd
920 ,force_to_100_flg
921 ,enable_flg
922 ,post_to_ledger_flg
923 ,open_paren
924 ,close_paren
925 ,apply_to_debit_code
926 ,nvl(p_created_by,f.created_by)
927 ,nvl(p_creation_date,f.creation_date)
928 ,FND_GLOBAL.user_id
929 ,sysdate
930 ,FND_GLOBAL.login_id
931 ,f.object_version_number
932 from fem_alloc_br_formula f
933 where f.object_definition_id = p_source_obj_def_id
934 and (f.sub_object_id is NULL
935 or NOT EXISTS (select 1
936 from fem_object_catalog_b o
937 where o.object_id = f.sub_object_id
938 and o.object_type_code = 'CONDITION_MAPPING'));
939
940 -- cursor to select all records from fem_alloc_br_formula and join
941 -- with fem_object_catalog_b to only return records that have
942 -- object_type_code = 'CONDITION_MAPPING'.
943 FOR l_formula_rec IN c_allc_br_formula (p_source_obj_def_id)
944 LOOP
945
946 -- Duplicate Local Condition
947 case p_copy_type_code
948
949 when FEM_BUSINESS_RULE_PVT.G_DUPLICATE then
950
951 l_source_cond_obj_id := l_formula_rec.sub_object_id;
952 l_source_cond_obj_def_id := Get_Cond_Obj_Def_Id (l_source_cond_obj_id);
953
954 l_target_cond_obj_def_id := FEM_BUSINESS_RULE_PVT.GetNewObjDefId();
955 l_target_cond_obj_id := FEM_BUSINESS_RULE_PVT.GetNewObjId();
956
957 when FEM_BUSINESS_RULE_PVT.G_BACKUP then
958
959 l_source_cond_obj_id := l_formula_rec.sub_object_id;
960
961 l_source_cond_obj_def_id := Get_Cond_Obj_Def_Id (l_source_cond_obj_id);
962
963 l_backup_obj_def_id := p_target_obj_def_id;
964
965 l_target_cond_obj_id :=
966 Get_Mapping_Rule_Condition
967 (p_obj_def_id => l_backup_obj_def_id,
968 p_func_seq => l_formula_rec.function_seq
969 );
970 if (l_target_cond_obj_id is null) then
971 l_target_cond_obj_def_id := FEM_BUSINESS_RULE_PVT.GetNewObjDefId();
972 l_target_cond_obj_id := FEM_BUSINESS_RULE_PVT.GetNewObjId();
973 else
974 l_target_cond_obj_def_id :=
975 Get_Cond_Obj_Def_Id (l_target_cond_obj_id);
976 end if;
977
978 when FEM_BUSINESS_RULE_PVT.G_REVERT then
979
980 l_source_cond_obj_id := l_formula_rec.sub_object_id;
981 l_source_cond_obj_def_id := Get_Cond_Obj_Def_Id (l_source_cond_obj_id);
982
983 l_current_obj_def_id := p_target_obj_def_id;
984
985 if (l_current_obj_def_id is null) then
986 FEM_ENGINES_PKG.User_Message (
987 p_app_name => G_APPS_SHORT_NAME
988 ,p_msg_name => 'FEM_BR_RVRT_OLD_APPR_CPY_ERR'
989 );
990 raise FND_API.G_EXC_ERROR;
991 end if;
992
993 l_target_cond_obj_id :=
994 Get_Mapping_Rule_Condition
995 (p_obj_def_id => l_current_obj_def_id,
996 p_func_seq => l_formula_rec.function_seq
997 );
998
999 if (l_target_cond_obj_id is null) then
1000 l_target_cond_obj_def_id := FEM_BUSINESS_RULE_PVT.GetNewObjDefId();
1001 l_target_cond_obj_id := FEM_BUSINESS_RULE_PVT.GetNewObjId();
1002 else
1003 l_target_cond_obj_def_id :=
1004 Get_Cond_Obj_Def_Id (l_target_cond_obj_id);
1005 end if;
1006
1007 end case;
1008
1009 l_target_cond_obj_name := G_LOCAL_COND_NAME || l_target_cond_obj_id;
1010 l_target_cond_obj_def_name := G_LOCAL_COND_NAME || l_target_cond_obj_def_id;
1011
1012 FEM_BUSINESS_RULE_PVT.DuplicateObject (
1013 p_api_version => 1.0
1014 ,p_init_msg_list => FND_API.G_FALSE
1015 ,p_commit => FND_API.G_FALSE
1016 ,x_return_status => l_return_status
1017 ,x_msg_count => l_msg_count
1018 ,x_msg_data => l_msg_data
1019 ,p_object_type_code => 'CONDITION'
1020 ,p_source_obj_id => l_source_cond_obj_id
1021 ,p_source_obj_def_id => l_source_cond_obj_def_id
1022 ,x_target_obj_id => l_target_cond_obj_id
1023 ,p_target_obj_name => l_target_cond_obj_name
1024 ,x_target_obj_def_id => l_target_cond_obj_def_id
1025 ,p_target_obj_def_name => l_target_cond_obj_def_name
1026 ,p_created_by => p_created_by
1027 ,p_creation_date => p_creation_date
1028 );
1029
1030 if (l_return_status = FND_API.G_RET_STS_ERROR) then
1031 raise FND_API.G_EXC_ERROR;
1032 elsif (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1033 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1034 end if;
1035 insert into fem_alloc_br_formula (
1036 object_definition_id
1037 ,function_seq
1038 ,function_cd
1039 ,sub_object_id
1040 ,value
1041 ,table_name
1042 ,column_name
1043 ,math_operator_cd
1044 ,formula_macro_cd
1045 ,force_to_100_flg
1046 ,enable_flg
1047 ,post_to_ledger_flg
1048 ,open_paren
1049 ,close_paren
1050 ,apply_to_debit_code
1051 ,created_by
1052 ,creation_date
1053 ,last_updated_by
1054 ,last_update_date
1055 ,last_update_login
1056 ,object_version_number
1057 ) values (
1058 l_formula_rec.object_definition_id
1059 ,l_formula_rec.function_seq
1060 ,l_formula_rec.function_cd
1061 ,l_target_cond_obj_id
1062 ,l_formula_rec.value
1063 ,l_formula_rec.table_name
1064 ,l_formula_rec.column_name
1065 ,l_formula_rec.math_operator_cd
1066 ,l_formula_rec.formula_macro_cd
1067 ,l_formula_rec.force_to_100_flg
1068 ,l_formula_rec.enable_flg
1069 ,l_formula_rec.post_to_ledger_flg
1070 ,l_formula_rec.open_paren
1071 ,l_formula_rec.close_paren
1072 ,l_formula_rec.apply_to_debit_code
1073 ,l_formula_rec.created_by
1074 ,l_formula_rec.creation_date
1075 ,l_formula_rec.last_updated_by
1076 ,l_formula_rec.last_update_date
1077 ,l_formula_rec.last_update_login
1078 ,l_formula_rec.object_version_number
1079 );
1080
1081 -- Update the object dependency record to now point to the new Local
1082 -- Condition object.
1083 update fem_object_dependencies
1084 set required_object_id = l_target_cond_obj_id
1085 where object_definition_id = l_formula_rec.object_definition_id
1086 and required_object_id = l_source_cond_obj_id;
1087
1088 END LOOP;
1089 EXCEPTION
1090 WHEN FND_API.G_EXC_ERROR THEN
1091 IF c_allc_br_formula%ISOPEN THEN
1092 CLOSE c_allc_br_formula;
1093 END IF;
1094 RAISE FND_API.G_EXC_ERROR;
1095 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1096 IF c_allc_br_formula%ISOPEN THEN
1097 CLOSE c_allc_br_formula;
1098 END IF;
1099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1100
1101 END CopyFormulaRecs;
1102
1103
1104 --
1105 -- PROCEDURE
1106 -- CopyDimensionRecs
1107 --
1108 -- DESCRIPTION
1109 -- Creates new Mapping Rule Definition Dimensions by copying records in the
1110 -- FEM_ALLOC_BR_DIMENSIONS table.
1111 --
1112 -- IN
1113 -- p_source_obj_def_id - Source Object Definition ID.
1114 -- p_target_obj_def_id - Target Object Definition ID.
1115 -- p_created_by - FND User ID (optional).
1116 -- p_creation_date - System Date (optional).
1117 --
1118 --------------------------------------------------------------------------------
1119 PROCEDURE CopyDimensionRecs(
1120 p_source_obj_def_id in number
1121 ,p_target_obj_def_id in number
1122 ,p_created_by in number
1123 ,p_creation_date in date
1124 )
1125 --------------------------------------------------------------------------------
1126 IS
1127 BEGIN
1128
1129 insert into fem_alloc_br_dimensions (
1130 object_definition_id
1131 ,function_seq
1132 ,alloc_dim_col_name
1133 ,post_to_balances_flag
1134 ,function_cd
1135 ,alloc_dim_usage_code
1136 /* ,hierarchy_obj_def_id
1137 ,alloc_dim_track_flag*/
1138 ,dimension_value
1139 ,dimension_value_char
1140 ,percent_distribution_code
1141 ,created_by
1142 ,creation_date
1143 ,last_updated_by
1144 ,last_update_date
1145 ,last_update_login
1146 ,object_version_number
1147 ) select
1148 p_target_obj_def_id
1149 ,function_seq
1150 ,alloc_dim_col_name
1151 ,post_to_balances_flag
1152 ,function_cd
1153 ,alloc_dim_usage_code
1154 /* ,hierarchy_obj_def_id
1155 ,alloc_dim_track_flag*/
1156 ,dimension_value
1157 ,dimension_value_char
1158 ,percent_distribution_code
1159 ,nvl(p_created_by,created_by)
1160 ,nvl(p_creation_date,creation_date)
1161 ,FND_GLOBAL.user_id
1162 ,sysdate
1163 ,FND_GLOBAL.login_id
1164 ,object_version_number
1165 from fem_alloc_br_dimensions
1166 where object_definition_id = p_source_obj_def_id;
1167
1168 END CopyDimensionRecs;
1169
1170 --
1171 -- PROCEDURE
1172 -- synchronize_mapping_definition
1173 --
1174 -- DESCRIPTION
1175 -- Synchronize the mappping definition with meta data.
1176 -- Psudo code
1177 -- Loop through all corresponding formula rows
1178 -- for every formula row that includes a table
1179 -- if the table is enabled
1180 -- call synchronize_dim_rows
1181 -- else if FEM_BALANCES is enabled
1182 -- update the formula row with FEM_BALANCES
1183 -- delete all corresponding rows in the FEM_ALLOC_BR_DIMENSIONS
1184 -- populate default rows in the FEM_ALLOC_BR_DIMENSIONS for FEM_BALANCES
1185 -- else if FEM_BALANCES is disabled
1186 -- error out
1187 --
1188 -- IN
1189 -- p_api_version - API Version
1190 -- p_init_msg_list - Initialize Message List Flag (Boolean)
1191 -- p_commit - Commit Work Flag (Boolean)
1192 -- p_obj_def_id - Object Definition ID
1193 --
1194 -- OUT
1195 -- x_return_status - Return Status of API Call
1196 -- x_msg_count - Total Count of Error Messages in API Call
1197 -- x_msg_data - Error Message in API Call
1198 --
1199 PROCEDURE synchronize_mapping_definition(
1200 p_api_version in number
1201 ,p_init_msg_list in varchar2 := FND_API.G_FALSE
1202 ,p_commit in varchar2 := FND_API.G_FALSE
1203 ,p_obj_def_id in number
1204 ,x_return_status out nocopy varchar2
1205 ,x_msg_count out nocopy number
1206 ,x_msg_data out nocopy varchar2
1207 )
1208 IS
1209
1210 --
1211 -- Standard API information constants.
1212 --
1213 L_API_VERSION CONSTANT NUMBER := 1.0;
1214 L_API_NAME CONSTANT VARCHAR2(30) := 'SYNCHRONIZE_MAPPING_DEFINITION';
1215
1216 CURSOR c_alloc_br_formula
1217 IS
1218 select function_seq, function_cd, table_name, nvl (post_to_ledger_flg, 'N') as post_to_ledger_flg
1219 from FEM_ALLOC_BR_FORMULA
1220 where object_definition_id = p_obj_def_id
1221 and function_cd in ('FILTER', 'DEBIT', 'CREDIT',
1222 'TABLE_ACCESS', 'PCT_DISTRB', 'LEAFFUNC')
1223 order by function_seq asc;
1224 l_formula_rec c_alloc_br_formula%ROWTYPE;
1225
1226
1227 L_DEFAULT_TABLE constant varchar2(30) := 'FEM_BALANCES';
1228 L_DEFAULT_COL_NAME constant varchar2(30) := 'FEM_CURR_PERIOD_AMT';
1229 l_balanced_enabled_flg boolean := false;
1230
1231 L_MAPPING_UI_INPUT constant varchar2(30) := 'MAPPING_UI_INPUT';
1232 L_MAPPING_UI_OUTPUT constant varchar2(30) := 'MAPPING_UI_OUTPUT';
1233 l_column_property_code varchar2(30) := L_MAPPING_UI_INPUT;
1234 L_POST_TO_BALANCES_FLAG_NO varchar2(1) := 'N';
1235
1236 L_NOT_APPLICABLE varchar2(30) := 'NOT_APPLICABLE';
1237 L_VALUE varchar2(30) := 'VALUE';
1238 L_SAME_AS_SOURCE varchar2(30) := 'SAME_AS_SOURCE';
1239 L_ALL varchar2(30) := 'ALL';
1240
1241 l_percent_distribution_code varchar2(30) := NULL;
1242
1243 l_alloc_dim_usage_code varchar2(30) := L_ALL;
1244
1245 l_adjustment_flag boolean := true;
1246
1247 l_col_name varchar2 (30) := null;
1248
1249 BEGIN
1250 --
1251 -- Initialize savepoint.
1252 --
1253 SAVEPOINT synchronize_mapping_definition;
1254
1255 --
1256 -- Standard check for API version compatibility.
1257 --
1258 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1259 p_api_version,
1260 L_API_NAME,
1261 G_PKG_NAME)
1262 THEN
1263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1264 END IF;
1265
1266 --
1267 -- Initialize message list if p_init_msg_list is set to TRUE.
1268 --
1269 IF FND_API.To_Boolean (p_init_msg_list) THEN
1270 FND_MSG_PUB.Initialize;
1271 END IF;
1272
1273 --
1274 -- Initialize API return status to success.
1275 --
1276 x_return_status := FND_API.G_RET_STS_SUCCESS;
1277
1278
1279
1280 l_balanced_enabled_flg := IS_TABLE_ENABLED (L_DEFAULT_TABLE);
1281
1282 for l_formula_rec in c_alloc_br_formula
1283 loop
1284
1285 if l_formula_rec.function_cd = 'FILTER' then
1286 l_adjustment_flag := false;
1287 elsif l_formula_rec.function_cd in ('DEBIT', 'CREDIT') then
1288
1289 l_column_property_code := L_MAPPING_UI_OUTPUT;
1290
1291 if l_adjustment_flag then
1292 l_alloc_dim_usage_code := L_VALUE;
1293 else
1294 l_alloc_dim_usage_code := L_SAME_AS_SOURCE;
1295 end if;
1296
1297 elsif l_formula_rec.function_cd = 'PCT_DISTRB' then
1298 l_percent_distribution_code := L_NOT_APPLICABLE;
1299 end if;
1300
1301
1302 if IS_TABLE_ENABLED (l_formula_rec.table_name) then -- table enabled
1303 if upper(l_formula_rec.post_to_ledger_flg) <> 'Y' then
1304 synchronize_dimension_recs (
1305 p_table_name => l_formula_rec.table_name
1306 ,p_obj_def_id => p_obj_def_id
1307 ,p_func_seq => l_formula_rec.function_seq
1308 ,p_function_cd => l_formula_rec.function_cd
1309 ,p_column_property_code => l_column_property_code
1310 ,p_alloc_dim_usage_code => l_alloc_dim_usage_code
1311 ,p_post_to_balance_flag => L_POST_TO_BALANCES_FLAG_NO
1312 ,p_percent_distribution_code => l_percent_distribution_code);
1313 end if;
1314 else --table disabled
1315 if not l_balanced_enabled_flg then --FEM_BALANCES is disabled
1316 RAISE FND_API.G_EXC_ERROR;
1317 else --default to FEM_BALANCES
1318 if l_formula_rec.function_cd = 'TABLE_ACCESS' then
1319 l_col_name := NULL;
1320 else
1321 l_col_name := L_DEFAULT_COL_NAME;
1322 end if;
1323
1324 update FEM_ALLOC_BR_FORMULA set table_name = L_DEFAULT_TABLE,
1325 column_name = l_col_name, sub_object_id = null
1326 where object_definition_id = p_obj_def_id
1327 and function_seq = l_formula_rec.function_seq;
1328
1329 delete_dimension_rec (
1330 p_obj_def_id => p_obj_def_id
1331 ,p_func_seq => l_formula_rec.function_seq);
1332
1333 --can't be adjustment type
1334 if l_formula_rec.function_cd not in ('TABLE_ACCESS',
1335 'LEAFFUNC') then
1336 populate_dimension_recs (
1337 p_table_name => L_DEFAULT_TABLE
1338 ,p_obj_def_id => p_obj_def_id
1339 ,p_func_seq => l_formula_rec.function_seq
1340 ,p_function_cd => l_formula_rec.function_cd
1341 ,p_column_property_code => l_column_property_code
1342 ,p_alloc_dim_usage_code => l_alloc_dim_usage_code
1343 ,p_post_to_balance_flag => L_POST_TO_BALANCES_FLAG_NO
1344 ,p_percent_distribution_code => l_percent_distribution_code);
1345 end if;
1346 end if;
1347
1348 end if;
1349
1350 end loop;
1351
1352 --
1353 -- Standard check for commit request.
1354 --
1355 IF FND_API.To_Boolean (p_commit) THEN
1356 COMMIT WORK;
1357 END IF;
1358
1359 --
1360 -- Standard API to get message count, and if 1,
1361 -- set the message data OUT variable.
1362 --
1363 FND_MSG_PUB.Count_And_Get (
1364 p_count => x_msg_count,
1365 p_data => x_msg_data
1366 );
1367
1368
1369 EXCEPTION
1370 WHEN FND_API.G_EXC_ERROR THEN
1371 ROLLBACK TO synchronize_mapping_definition;
1372 if c_alloc_br_formula%ISOPEN then
1373 close c_alloc_br_formula;
1374 end if;
1375 x_return_status := FND_API.G_RET_STS_ERROR;
1376 FND_MSG_PUB.Count_And_Get (
1377 p_encoded => FND_API.g_false,
1378 p_count => x_msg_count,
1379 p_data => x_msg_data
1380 );
1381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1382 ROLLBACK TO synchronize_mapping_definition;
1383 if c_alloc_br_formula%ISOPEN then
1384 close c_alloc_br_formula;
1385 end if;
1386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1387 FND_MSG_PUB.Count_And_Get (
1388 p_encoded => FND_API.g_false,
1389 p_count => x_msg_count,
1390 p_data => x_msg_data
1391 );
1392 WHEN OTHERS THEN
1393 ROLLBACK TO synchronize_mapping_definition;
1394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1395 if c_alloc_br_formula%ISOPEN then
1396 close c_alloc_br_formula;
1397 end if;
1398 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1399 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1400 END IF;
1401 FND_MSG_PUB.Count_And_Get (
1402 p_count => x_msg_count,
1403 p_data => x_msg_data
1404 );
1405 END synchronize_mapping_definition;
1406
1407
1408 FUNCTION IS_TABLE_ENABLED (p_table_name varchar2)
1409 RETURN BOOLEAN
1410 IS
1411 CURSOR c_table_check
1412 IS
1413 select 1
1414 FROM fem_table_class_assignmt tc, fem_table_class_usages tu
1415 where tu.table_classification_code = tc.table_classification_code
1416 and tc.enabled_flag = 'Y'
1417 and tc.table_name = p_table_name;
1418 l_table_check number := NULL;
1419
1420 BEGIN
1421 open c_table_check ;
1422 fetch c_table_check into l_table_check;
1423 close c_table_check;
1424 if l_table_check is null then -- table disabled
1425 return FALSE;
1426 else
1427 return TRUE;
1428 end if;
1429 END;
1430
1431 PROCEDURE delete_dimension_rec (
1432 p_obj_def_id in number
1433 ,p_func_seq in number)
1434 IS
1435 BEGIN
1436 delete from fem_alloc_br_dimensions
1437 where object_definition_id = p_obj_def_id
1438 and function_seq = p_func_seq;
1439
1440 END;
1441
1442 PROCEDURE populate_dimension_recs (
1443 p_table_name in varchar2
1444 ,p_obj_def_id in number
1445 ,p_func_seq in number
1446 ,p_function_cd in varchar2
1447 ,p_column_property_code in varchar2
1448 ,p_alloc_dim_usage_code in varchar2
1449 ,p_post_to_balance_flag in varchar2
1450 ,p_percent_distribution_code in varchar2)
1451 IS
1452 L_MAPPING_UI_INPUT constant varchar2(30) := 'MAPPING_UI_INPUT';
1453 L_MAPPING_UI_OUTPUT constant varchar2(30) := 'MAPPING_UI_OUTPUT';
1454 l_column_property_code varchar2(30) := L_MAPPING_UI_INPUT;
1455
1456 CURSOR c_dimension_candidates
1457 IS
1458 select column_name
1459 from fem_tab_column_prop
1460 where column_property_code = p_column_property_code
1461 and table_name = p_table_name;
1462
1463 l_dimension_candidate c_dimension_candidates%ROWTYPE;
1464
1465 l_date date := sysdate;
1466
1467 BEGIN
1468
1469 -- Note that p_function_cd can't be LEAFFUNC
1470 for l_dimension_candidate in c_dimension_candidates
1471 loop
1472 insert into fem_alloc_br_dimensions (
1473 object_definition_id
1474 ,function_seq
1475 ,alloc_dim_col_name
1476 ,post_to_balances_flag
1477 ,function_cd
1478 ,alloc_dim_usage_code
1479 ,dimension_value
1480 ,dimension_value_char
1481 ,percent_distribution_code
1482 ,created_by
1483 ,creation_date
1484 ,last_updated_by
1485 ,last_update_date
1486 ,last_update_login
1487 ,object_version_number)
1488 values
1489 (p_obj_def_id
1490 ,p_func_seq
1491 ,l_dimension_candidate.column_name
1492 ,p_post_to_balance_flag
1493 ,p_function_cd
1494 ,p_alloc_dim_usage_code
1495 ,null
1496 ,null
1497 ,p_percent_distribution_code
1498 ,FND_GLOBAL.User_ID
1499 ,l_date
1500 ,FND_GLOBAL.User_ID
1501 ,l_date
1502 ,FND_GLOBAL.Conc_Login_ID
1503 ,1.0);
1504 end loop;
1505
1506 END populate_dimension_recs;
1507
1508
1509 PROCEDURE synchronize_dimension_recs (
1510 p_table_name in varchar2
1511 ,p_obj_def_id in number
1512 ,p_func_seq in number
1513 ,p_function_cd in varchar2
1514 ,p_column_property_code in varchar2
1515 ,p_alloc_dim_usage_code in varchar2
1516 ,p_post_to_balance_flag in varchar2
1517 ,p_percent_distribution_code in varchar2)
1518 IS
1519 CURSOR c_orphan_dimensions
1520 IS
1521 select alloc_dim_col_name, alloc_dim_usage_code
1522 from fem_alloc_br_dimensions
1523 where object_definition_id = p_obj_def_id
1524 and function_seq = p_func_seq
1525 and alloc_dim_col_name not in
1526 (select column_name
1527 from fem_tab_column_prop
1528 where column_property_code = p_column_property_code
1529 and table_name = p_table_name);
1530
1531 l_orphan_dimensions_rec c_orphan_dimensions%ROWTYPE;
1532
1533 CURSOR c_missing_dimensions
1534 IS
1535 select column_name
1536 from fem_tab_column_prop
1537 where column_property_code = p_column_property_code
1538 and table_name = p_table_name
1539 and column_name not in (select alloc_dim_col_name
1540 from fem_alloc_br_dimensions
1541 where object_definition_id = p_obj_def_id
1542 and function_seq = p_func_seq);
1543
1544 l_missing_dimensions_rec c_missing_dimensions%ROWTYPE;
1545
1546 l_date date := sysdate;
1547 BEGIN
1548 if p_function_cd not in ('LEAFFUNC', 'TABLE_ACCESS') then
1549 for l_orphan_dimensions_rec in c_orphan_dimensions
1550 loop
1551 delete from fem_alloc_br_dimensions
1552 where object_definition_id = p_obj_def_id
1553 and function_seq = p_func_seq
1554 and alloc_dim_col_name = l_orphan_dimensions_rec.alloc_dim_col_name;
1555
1556 if l_orphan_dimensions_rec.alloc_dim_usage_code = 'SAME_AS_PCT'
1557 and p_function_cd in ('CREDIT', 'DEBIT') then
1558 begin
1559 update fem_alloc_br_dimensions
1560 set percent_distribution_code = 'NOT_APPLICABLE'
1561 where object_definition_id = p_obj_def_id
1562 and alloc_dim_col_name = l_orphan_dimensions_rec.alloc_dim_col_name
1563 and function_cd = 'PCT_DISTRB'
1564 and percent_distribution_code in ('PERCENT_DISTRIBUTION', 'MATCHING_DIMENSION');
1565 exception
1566 when NO_DATA_FOUND then
1567 l_orphan_dimensions_rec.alloc_dim_col_name := l_orphan_dimensions_rec.alloc_dim_col_name;
1568 end;
1569 end if;
1570 end loop;
1571
1572 for l_missing_dimensions_rec in c_missing_dimensions
1573 loop
1574 insert into fem_alloc_br_dimensions (
1575 object_definition_id
1576 ,function_seq
1577 ,alloc_dim_col_name
1578 ,post_to_balances_flag
1579 ,function_cd
1580 ,alloc_dim_usage_code
1581 ,dimension_value
1582 ,dimension_value_char
1583 ,percent_distribution_code
1584 ,created_by
1585 ,creation_date
1586 ,last_updated_by
1587 ,last_update_date
1588 ,last_update_login
1589 ,object_version_number)
1590 values
1591 (p_obj_def_id
1592 ,p_func_seq
1593 ,l_missing_dimensions_rec.column_name
1594 ,p_post_to_balance_flag
1595 ,p_function_cd
1596 ,p_alloc_dim_usage_code
1597 ,null
1598 ,null
1599 ,p_percent_distribution_code
1600 ,FND_GLOBAL.User_ID
1601 ,l_date
1602 ,FND_GLOBAL.User_ID
1603 ,l_date
1604 ,FND_GLOBAL.Conc_Login_ID
1605 ,1.0);
1606 end loop;
1607 end if;
1608
1609 END synchronize_dimension_recs;
1610
1611 /*************************************************************************
1612
1613 delete_map_rule_content
1614 This procedure deletes the data from the 3 tables that store
1615 the mapping rule content viz. FEM_ALLOC_BUSINESS_RULE,
1616 FEM_ALLOC_BR_FORMULA and FEM_ALLOC_BR_DIMENSIONS.
1617
1618 *************************************************************************/
1619
1620 PROCEDURE delete_map_rule_content(p_object_definition_id IN NUMBER)
1621 IS
1622 c_api_name constant varchar2(30) := ' delete_map_rule_content';
1623
1624 BEGIN
1625
1626 DeleteDimensionRecs(
1627 p_obj_def_id => p_object_definition_id
1628 );
1629
1630 DeleteFormulaRecs(
1631 p_obj_def_id => p_object_definition_id
1632 );
1633
1634 DeleteMappingRuleRec(
1635 p_obj_def_id => p_object_definition_id
1636 );
1637
1638 EXCEPTION
1639
1640 when others then
1641 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, c_api_name);
1642 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1643
1644 END delete_map_rule_content;
1645
1646 -- Bug#6496686 -- Begin
1647 --
1648 -- PROCEDURE
1649 -- DeleteTuningOptionDetails
1650 --
1651 -- DESCRIPTION
1652 -- Deletes any other information related to Mapping Rule
1653 -- like Tuning Options
1654 --
1655 -- IN
1656 -- p_obj_id - Object ID.
1657 --
1658 --------------------------------------------------------------------------------
1659 PROCEDURE DeleteTuningOptionDetails(
1660 p_obj_id in number
1661 )
1662 --------------------------------------------------------------------------------
1663 IS
1664
1665 l_return_status varchar2(1);
1666 l_msg_count number;
1667 l_msg_data varchar2 (2000);
1668 BEGIN
1669
1670 FEM_ADMIN_UTIL_PKG.Delete_Obj_Tuning_Options (
1671 p_api_version => 1.0
1672 ,p_init_msg_list => FND_API.G_FALSE
1673 ,p_commit => FND_API.G_FALSE
1674 ,p_encoded => FND_API.G_FALSE
1675 ,x_return_status => l_return_status
1676 ,x_msg_count => l_msg_count
1677 ,x_msg_data => l_msg_data
1678 ,p_object_id => p_obj_id
1679 );
1680
1681 END DeleteTuningOptionDetails;
1682 -- Bug#6496686 -- End
1683
1684 END FEM_BR_MAPPING_RULE_PVT;