[Home] [Help]
PACKAGE BODY: APPS.FEM_ALLOC_BR_UTIL_PKG
Source
1 PACKAGE BODY FEM_ALLOC_BR_UTIL_PKG AS
2 --$Header: fem_alloc_br_utl.plb 120.23.12000000.3 2007/08/21 10:51:51 asadadek noship $
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation, Redwood Shores, CA, USA |
5 | All rights reserved. |
6 +==========================================================================+
7 | FILENAME
8 |
9 | fem_alloc_br_utl.plb
10 |
11 | NAME FEM_ALLOC_BR_UTIL_PKG
12 |
13 | DESCRIPTION
14 |
15 | Package Body for the FEM Mapping Utility Package
16 |
17 | HISTORY
18 |
19 | 19-JAN-07 RFlippo initial creation
20 | 22-FEB-07 RFlippo added generate_condition_summary
21 | 16-MAR-07 RFlippo modified generate_condition_summary to store
22 | attr data type in the gt table
23 | 20-MAR-07 RFlippo modfied refresh_maprule_from_snapshot to handle
24 | data in fem_object_dependencies
25 | 27-MAR-07 RFlippo fixed issues with cr_new_ver_from_defaults; added
26 | get_default_definition
27 | 29-MAR-07 RFlippo added insert into fem_alloc_br_objects when create
28 | new default rule
29 | 12-APR-07 RFlippo fixed issues with Table data components in
30 | generate_condition_summary
31 | 26-APR-07 RFlippo added generate_fctr_summary API for creating the
32 | Factor table summary data for the Factor table Details
33 | pluggable region
34 | 10-MAY-07 RFlippo added commit for when p_commit is TRUE on all procedures
35 | except for gen_cond_summary and gen_fctr_summary;
36 | also changed start/end date default logic to use
37 | profile options first
38 | 24-MAY-07 RFlippo modified get_default_definition so that
39 | it does not try to insert into fem_alloc_br_objects
40 | when a default definition already exists
41 | 29-JUN-2007 asadadek bug#6158146. Call API delete_map_rule_content to handle
42 | deletion of map rule contents minus the helper records
43 | instead of DeleteObjectDefinition.
44 | 2-JUL-07 RFlippo Bug#6146396 Set any dimension cols in the Mapping Output list
45 | = "SAME_AS_SOURCE" if they don't have a default
46 | assigned in fem_alloc_br_dimensions for the
47 | default rule
48 | 7-JUL-07 RFlippo Bug#6179151 Modify so that for Adj rules, the default
49 | is VALUE
50 | 6-JUL-07 RFlippo Need to get a unique object name for the snapshot objects.
51 | To do this, will concatenate the sysdate, include MI:SS
52 | so that we can have multiple snapshots (Preview, Edit, etc)
53 | for a given mapping rule. Can even support multiple snapshots
54 | of the same object type (i.e, Preview) without any conflict;
55 | Also change logic for creating a new default rule,
56 | so that the default rule obj def name and description
57 | come from the seeded default rule (gvsc=null)
58 | 10-JUL-07 Rflippo bug#6196776 Modify set_dim_usage_dflt so that it only does
59 | defaults for enabled_flg='Y'
60 |
61 | 21-AUG-07 asadadek Bug 6348530. Added private API set_VT_attributes to set the visual
62 | trace attributes upon save.
63 |
64 +=========================================================================*/
65
66 -----------------------
67 -- Package Constants --
68 -----------------------
69 c_resp_app_id CONSTANT NUMBER := FND_GLOBAL.RESP_APPL_ID;
70
71 c_user_id CONSTANT NUMBER := FND_GLOBAL.USER_ID;
72 c_login_id NUMBER := FND_GLOBAL.Login_Id;
73
74 c_module_pkg CONSTANT VARCHAR2(80) := 'fem.plsql.fem_alloc_br_util_pkg';
75 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FEM_ALLOC_BR_UTIL_PKG';
76
77 f_set_status BOOLEAN;
78
79 c_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
80 c_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
81 c_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
82 c_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
83 c_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
84 c_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
85
86 c_object_version CONSTANT NUMBER := 1;
87
88 C_SNAP_OBJTYPE CONSTANT VARCHAR2(30) := 'MAPPING_EDIT_SNAPSHOT';
89 C_DFLT_OBJTYPE CONSTANT VARCHAR2(30) := 'MAPPING_RULE_DEFAULTS';
90 C_RULE_OBJTYPE CONSTANT VARCHAR2(30) := 'MAPPING_RULE';
91
92 C_MAX_END_DATE CONSTANT DATE := to_date('12/31/9999','MM/DD/YYYY');
93
94
95 -----------------------
96 -- Package Variables --
97 -----------------------
98 v_module_log VARCHAR2(255);
99
100
101 v_token_value VARCHAR2(150);
102 v_token_trans VARCHAR2(1);
103
104 v_msg_text VARCHAR2(4000);
105
106 gv_prg_msg VARCHAR2(2000);
107 gv_callstack VARCHAR2(2000);
108
109
110 -----------------------
111 -- Private Procedures --
112 -----------------------
113 PROCEDURE Validate_OA_Params (
114 p_api_version IN NUMBER,
115 p_init_msg_list IN VARCHAR2,
116 p_commit IN VARCHAR2,
117 p_encoded IN VARCHAR2,
118 x_return_status OUT NOCOPY VARCHAR2
119 );
120
121
122 PROCEDURE set_dim_usage_dflt (p_object_definition_id IN NUMBER);
123 procedure set_VT_attributes(p_object_definition_id NUMBER);
124
125 /*************************************************************************
126
127 set_dim_usage_dflt
128
129 PURPOSE: Set any dimension cols in the Mapping Output list
130 that do not have a default assigned to "SAME_AS_SOURCE" (bug#6146396)
131
132 Here's the logic:
133 We always insert a set of rows for the target table
134 However - if post_to_ledger_flg = 'Y' on fem_alloc_br_formula,
135 then we insert another set of records for FEM_BALANCES
136
137 7/5/2007 Rflippo Bug#6179151 Modify so that for Adj rules, the default
138 is VALUE
139 7/10/2007 Rflippo bug#6196776 Only do defaults for enabled_flg='Y'
140 *************************************************************************/
141
142 PROCEDURE set_dim_usage_dflt (p_object_definition_id IN NUMBER) IS
143
144 cursor c_tgttab (p_obj_def_id IN VARCHAR2) is
145 SELECT function_seq, table_name, post_to_ledger_flg, function_cd
146 FROM fem_alloc_br_formula
147 WHERE object_definition_id = p_obj_def_id
148 AND function_cd IN ('CREDIT','DEBIT')
149 AND enable_flg <> 'N';
150
151 cursor c_dimcol (p_obj_def_id IN VARCHAR2, p_func_seq IN NUMBER, p_tgt_table IN VARCHAR2) is
152 SELECT column_name
153 FROM fem_tab_column_prop
154 WHERE table_name = p_tgt_table
155 AND column_property_code = 'MAPPING_UI_OUTPUT'
156 AND column_name NOT IN (
157 SELECT alloc_dim_col_name
158 FROM fem_alloc_br_dimensions
159 WHERE object_definition_id = p_obj_def_id
160 AND function_seq = p_func_seq);
161
162 cursor c_bal_dimcol (p_obj_def_id IN VARCHAR2, p_func_seq IN NUMBER) is
163 SELECT column_name
164 FROM fem_tab_column_prop
165 WHERE table_name = 'FEM_BALANCES'
166 AND column_property_code = 'MAPPING_UI_OUTPUT'
167 AND column_name NOT IN (
168 SELECT alloc_dim_col_name
169 FROM fem_alloc_br_dimensions
170 WHERE object_definition_id = p_obj_def_id
171 AND function_seq = p_func_seq);
172
173 v_rule_type_code VARCHAR2(30);
174 v_object_id NUMBER;
175 v_dflt_usage VARCHAR2(30);
176
177 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
178 'fem.plsql.fem_alloc_br_util_pkg.set_dim_usage_dflt';
179
180
181 BEGIN
182
183 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
184 FEM_ENGINES_PKG.TECH_MESSAGE(
185 p_severity => FND_LOG.level_procedure,
186 p_module => C_MODULE,
187 p_msg_text => 'Begin Procedure');
188 END IF;
189
190
191 -- Identify the rule type
192 SELECT object_id
193 INTO v_object_id
194 FROM fem_objdef_helper_rules
195 WHERE helper_obj_def_id = p_object_definition_id
196 AND helper_object_type_code = 'MAPPING_EDIT_SNAPSHOT';
197
198 SELECT map_rule_type_code
199 INTO v_rule_type_code
200 FROM fem_alloc_br_objects
201 WHERE map_rule_object_id = v_object_id;
202
203 IF v_rule_type_code = 'ADJUSTMENT' THEN
204 v_dflt_usage := 'VALUE';
205 ELSE v_dflt_usage := 'SAME_AS_SOURCE';
206
207 END IF;
208
209
210 FOR func_seq IN c_tgttab (p_object_definition_id) LOOP
211 -- do the insert for the "non post to balances" records
212 FOR dimcol IN c_dimcol (p_object_definition_id, func_seq.function_seq, func_seq.table_name) LOOP
213 insert into fem_alloc_br_dimensions (
214 OBJECT_DEFINITION_ID
215 ,FUNCTION_SEQ
216 ,ALLOC_DIM_COL_NAME
217 ,POST_TO_BALANCES_FLAG
218 ,FUNCTION_CD
219 ,ALLOC_DIM_USAGE_CODE
220 ,DIMENSION_VALUE
221 ,DIMENSION_VALUE_CHAR
222 ,PERCENT_DISTRIBUTION_CODE
223 ,CREATED_BY
224 ,CREATION_DATE
225 ,LAST_UPDATED_BY
226 ,LAST_UPDATE_DATE
227 ,LAST_UPDATE_LOGIN
228 ,OBJECT_VERSION_NUMBER )
229 values (p_object_definition_id, func_seq.function_seq, dimcol.column_name,
230 'N', func_seq.function_cd,
231 v_dflt_usage,null,null,null,c_user_id,sysdate,
232 c_user_id,sysdate,null,1);
233 END LOOP;
234
235 -- do the insert for the post_to_balances record if 'Y' for post_to_ledger_flg
236 IF func_seq.post_to_ledger_flg = 'Y' THEN
237 FOR baldimcol IN c_bal_dimcol (p_object_definition_id, func_seq.function_seq) LOOP
238 insert into fem_alloc_br_dimensions (
239 OBJECT_DEFINITION_ID
240 ,FUNCTION_SEQ
241 ,ALLOC_DIM_COL_NAME
242 ,POST_TO_BALANCES_FLAG
243 ,FUNCTION_CD
244 ,ALLOC_DIM_USAGE_CODE
245 ,DIMENSION_VALUE
246 ,DIMENSION_VALUE_CHAR
247 ,PERCENT_DISTRIBUTION_CODE
248 ,CREATED_BY
249 ,CREATION_DATE
250 ,LAST_UPDATED_BY
251 ,LAST_UPDATE_DATE
252 ,LAST_UPDATE_LOGIN
253 ,OBJECT_VERSION_NUMBER )
254 values (p_object_definition_id, func_seq.function_seq, baldimcol.column_name,
255 'Y', func_seq.function_cd,
256 v_dflt_usage,null,null,null,c_user_id,sysdate,
257 c_user_id,sysdate,null,1);
258 END LOOP;
259 END IF;
260
261 END LOOP;
262
263 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
264 FEM_ENGINES_PKG.TECH_MESSAGE(
265 p_severity => FND_LOG.level_procedure,
266 p_module => C_MODULE,
267 p_msg_text => 'End Procedure');
268 END IF;
269
270 END set_dim_usage_dflt;
271
272 /*************************************************************************
273
274 Create_snapshot
275
279 06-JUL-07 RFlippo Need to get a unique object name for the snapshot objects.
276 PURPOSE: Creates a new empty snapshot object (Mapping helper rule) and object definition,
277 and registers the association to the true mapping rule in FEM_OBJDEF_HELPER_RULES.
278
280 To do this, will concatenate the sysdate, include MI:SS
281 so that we can have multiple snapshots (Preview, Edit, etc)
282 for a given mapping rule. Can even support multiple snapshots
283 of the same object type (i.e, Preview) without any conflict.
284
285 *************************************************************************/
286
287 PROCEDURE create_snapshot (
288 p_map_rule_obj_def_id IN NUMBER,
289 p_snapshot_obj_type_code IN VARCHAR2 DEFAULT 'MAPPING_EDIT_SNAPSHOT',
290 p_api_version IN NUMBER DEFAULT c_api_version,
291 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
292 p_commit IN VARCHAR2 DEFAULT c_false,
293 p_encoded IN VARCHAR2 DEFAULT c_true,
294 x_snapshot_object_id OUT NOCOPY NUMBER,
295 x_snapshot_objdef_id OUT NOCOPY NUMBER,
296 x_return_status OUT NOCOPY VARCHAR2,
297 x_msg_count OUT NOCOPY NUMBER,
298 x_msg_data OUT NOCOPY VARCHAR2) IS
299
300 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
301 'fem.plsql.fem_alloc_br_util_pkg.create_snapshot';
302 C_API_NAME CONSTANT VARCHAR2(30) := 'Create_Snapshot';
303
304 e_unexp EXCEPTION;
305 e_error EXCEPTION;
306 e_invalid_obj EXCEPTION;
307 e_noobj EXCEPTION;
308 e_helper_reg EXCEPTION;
309 e_objtype EXCEPTION;
310
311 v_folder_id FEM_FOLDERS_B.folder_id%TYPE;
312 v_local_vs_combo_id FEM_OBJECT_CATALOG_B.local_vs_combo_id%TYPE;
313 v_object_access_code FEM_OBJECT_CATALOG_B.object_access_code%TYPE;
317 v_display_name FEM_OBJECT_DEFINITION_TL.display_name%TYPE;
314 v_object_origin_code FEM_OBJECT_CATALOG_B.object_origin_code%TYPE;
315 v_object_name FEM_OBJECT_CATALOG_TL.object_name%TYPE;
316 v_description FEM_OBJECT_CATALOG_TL.description%TYPE;
318 v_object_type_code FEM_OBJECT_CATALOG_B.object_type_code%TYPE;
319
320 v_profile_start_value VARCHAR2(1000);
321 v_profile_end_value VARCHAR2(1000);
322 v_effective_start_date DATE;
323 v_effective_end_date DATE;
324
325 v_sysdate VARCHAR2(50);
326
327 v_count number;
328
329 v_objdef_desc varchar2(255);
330
331 BEGIN
332
333 -- Standard Start of API savepoint
334 SAVEPOINT create_snapshot_pub;
335
336
337 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
338 FEM_ENGINES_PKG.TECH_MESSAGE(
339 p_severity => FND_LOG.level_procedure,
340 p_module => C_MODULE,
341 p_msg_text => 'Begin Procedure');
342 END IF;
343
344
345 -- Initialize return status to unexpected error
346 x_return_status := c_unexp;
347
348 -- Check for call compatibility.
349 IF NOT FND_API.Compatible_API_Call (c_api_version,
350 p_api_version,
351 C_API_NAME,
352 G_PKG_NAME)
353 THEN
354 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
355 FEM_ENGINES_PKG.TECH_MESSAGE(
356 p_severity => FND_LOG.level_statement,
357 p_module => C_MODULE,
358 p_msg_text => 'API Version ('||C_API_VERSION||') not compatible with '
359 ||'passed in version ('||p_api_version||')');
360 END IF;
361 RAISE e_unexp;
362 END IF;
363
364
365 Validate_OA_Params (
366 p_api_version => p_api_version,
367 p_init_msg_list => p_init_msg_list,
368 p_commit => p_commit,
369 p_encoded => p_encoded,
370 x_return_status => x_return_status);
371
372 IF (x_return_status <> c_success) THEN
373 RAISE e_error;
374 END IF;
375
376 /* Convert the sysdate to a string so we can concatenate it into the
377 snapshot object name */
378 SELECT to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
379 INTO v_sysdate
380 FROM dual;
381
382
383 /* Verify the mapping rule is valid and get mapping rule object info
384 NOTE: object_name for the snapshot will be = true rule object_id, because
385 not possible for the snapshot object and the true rule object to
386 share the same object_name*/
387 BEGIN
388
389 SELECT C.folder_id, C.local_vs_combo_id, C.object_access_code,
390 C.object_origin_code, C.object_id, C.description, D.display_name,
391 D.description
392 INTO v_folder_id, v_local_vs_combo_id, v_object_access_code,
393 v_object_origin_code, v_object_name, v_description, v_display_name,
397 AND D.object_definition_id = p_map_rule_obj_def_id
394 v_objdef_desc
395 FROM fem_object_catalog_vl C, fem_object_definition_vl D
396 WHERE D.object_id = C.object_id
398 AND C.object_type_code IN (C_RULE_OBJTYPE, C_DFLT_OBJTYPE);
399
400 EXCEPTION
401 WHEN no_data_found THEN
402 RAISE e_invalid_obj;
403
404 END;
405
406 /* Verify the object type code for the snapshot rule */
407 BEGIN
408 SELECT object_type_code
409 INTO v_object_type_code
410 FROM fem_object_types_vl
411 WHERE object_type_code IN ('MAPPING_EDIT_SNAPSHOT','MAPPING_PREVIEW')
412 AND object_type_code = p_snapshot_obj_type_code;
413
414 EXCEPTION
415 WHEN no_data_found THEN
416 RAISE e_objtype;
417 END;
418
419 /* Get the profile option start/end dates */
420 v_profile_start_value := fnd_profile.value_specific (
421 'FEM_EFFECTIVE_START_DATE'
422 ,fnd_global.user_id
423 ,fnd_global.resp_id
424 ,fnd_global.prog_appl_id);
425
426
427 v_profile_end_value := fnd_profile.value_specific (
428 'FEM_EFFECTIVE_END_DATE'
429 ,fnd_global.user_id
430 ,fnd_global.resp_id
431 ,fnd_global.prog_appl_id);
432
433 /* Try to get the date value from the profile option
434 if date value no good, just use the sysdate for start
435 and maxdate for end*/
436 BEGIN
437
438 IF v_profile_start_value IS NOT NULL THEN
439 v_effective_start_date := to_date(v_profile_start_value,'YYYY-MM-DD');
440 ELSE
441 v_effective_start_date := sysdate;
442 END IF;
443
444 EXCEPTION WHEN OTHERS THEN v_effective_start_date := null;
445 END;
446
447 BEGIN
448
449 IF v_profile_end_value IS NOT NULL THEN
450 v_effective_end_date := to_date(v_profile_end_value,'YYYY-MM-DD');
451 ELSE
452 v_effective_end_date := C_MAX_END_DATE;
453 END IF;
454
455 EXCEPTION WHEN OTHERS THEN v_effective_end_date := null;
456 END;
457
458
459
460
461 /* create the snapshot */
462 fem_object_catalog_util_pkg.create_object (x_object_id => x_snapshot_object_id
463 ,x_object_definition_id => x_snapshot_objdef_id
464 ,X_MSG_COUNT => x_msg_count
465 ,X_MSG_DATA => x_msg_data
466 ,X_RETURN_STATUS => x_return_status
467 ,P_API_VERSION => C_API_VERSION
468 ,P_COMMIT => C_FALSE
469 ,P_OBJECT_TYPE_CODE => p_snapshot_obj_type_code
470 ,P_FOLDER_ID => v_folder_id
471 ,P_LOCAL_VS_COMBO_ID => v_local_vs_combo_id
472 ,P_OBJECT_ACCESS_CODE => v_object_access_code
473 ,P_OBJECT_ORIGIN_CODE => v_object_origin_code
474 ,P_OBJECT_NAME => v_object_name||v_sysdate
475 ,P_DESCRIPTION => v_description
476 ,P_EFFECTIVE_START_DATE => v_effective_start_date
477 ,P_EFFECTIVE_END_DATE => v_effective_end_date
478 ,P_OBJ_DEF_NAME => v_display_name);
479
480
481 IF x_return_status NOT IN (c_success) THEN
482 RAISE e_noobj;
483 END IF;
484
485 /* Need to update the description of the snapshot
486 to be the same as the source default rule (since
487 the create_object api doesn't allow us to specify
488 description */
489 update fem_object_definition_tl
490 set description = v_objdef_desc
491 where object_definition_id = x_snapshot_objdef_id;
492
493
494 /* Register the association between the snapshot and the true rule */
495 fem_helper_rule_util_pkg.register_helper_rule (
496 p_rule_obj_def_id => p_map_rule_obj_def_id,
497 p_helper_obj_def_id => x_snapshot_objdef_id,
498 p_helper_object_type_code => p_snapshot_obj_type_code,
499 p_api_version => c_api_version,
500 p_init_msg_list => c_false,
501 p_commit => c_false,
502 p_encoded => c_true,
503 x_return_status => x_return_status,
504 x_msg_count => x_msg_count,
505 x_msg_data => x_msg_data);
506
507 IF x_return_status NOT IN (c_success) THEN
508 RAISE e_helper_reg;
509 END IF;
510
511 x_return_status := c_success;
512
513 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
514 FEM_ENGINES_PKG.TECH_MESSAGE(
515 p_severity => FND_LOG.level_procedure,
516 p_module => C_MODULE,
517 p_msg_text => 'End Procedure');
518 END IF;
519
520 IF FND_API.To_Boolean( p_commit ) THEN
521 COMMIT WORK;
522 END IF;
523
524
525 EXCEPTION
526 WHEN e_invalid_obj THEN
527 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
528 FEM_ENGINES_PKG.TECH_MESSAGE(
529 p_severity => FND_LOG.level_statement,
530 p_module => C_MODULE,
531 p_msg_text => 'Mapping Rule Object Definition does not exist'||p_map_rule_obj_def_id);
532 END IF;
533 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
534 p_count => x_msg_count,
535 p_data => x_msg_data);
536 ROLLBACK TO create_snapshot_pub;
537 x_return_status := c_error;
538
539 WHEN e_objtype THEN
540 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
541 FEM_ENGINES_PKG.TECH_MESSAGE(
542 p_severity => FND_LOG.level_statement,
543 p_module => C_MODULE,
544 p_msg_text => 'Snapshot object type must be either MAPPING_EDIT_SNAPSHOT or MAPPING_PREVIEW');
545 END IF;
546 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
547 p_count => x_msg_count,
551
548 p_data => x_msg_data);
549 ROLLBACK TO create_snapshot_pub;
550 x_return_status := c_error;
552 WHEN e_helper_reg THEN
553 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
554 FEM_ENGINES_PKG.TECH_MESSAGE(
555 p_severity => FND_LOG.level_statement,
556 p_module => C_MODULE,
557 p_msg_text => 'Unable to register Helper Rule metadata');
558 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
559 p_count => x_msg_count,
560 p_data => x_msg_data);
561 END IF;
562 ROLLBACK TO create_snapshot_pub;
563 x_return_status := c_error;
564
565 WHEN e_noobj THEN
566 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
567 FEM_ENGINES_PKG.TECH_MESSAGE(
568 p_severity => FND_LOG.level_statement,
569 p_module => C_MODULE,
570 p_msg_text => 'Unable to create Snapshot Object and Object Definition ID');
571 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
572 p_count => x_msg_count,
573 p_data => x_msg_data);
574 END IF;
575 ROLLBACK TO create_snapshot_pub;
576 x_return_status := c_error;
577
578
579 WHEN others THEN
580 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
581 FEM_ENGINES_PKG.TECH_MESSAGE(
582 p_severity => FND_LOG.level_statement,
583 p_module => C_MODULE,
584 p_msg_text => 'Unexpected error.');
585 FEM_ENGINES_PKG.TECH_MESSAGE(
586 p_severity => FND_LOG.level_statement,
587 p_module => C_MODULE,
588 p_msg_text => SQLERRM);
589 END IF;
590 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
591 p_count => x_msg_count,
592 p_data => x_msg_data);
593 ROLLBACK TO create_snapshot_pub;
594 x_return_status := c_unexp;
595
596
597 END create_snapshot;
598
599
600
601
602 /*************************************************************************
603
604 Refresh_maprule_from_snapshot
605
606 PURPOSE: updates the true mapping rule definition with content from the
607 Edit snapshot
608
609 *************************************************************************/
610
611 PROCEDURE refresh_maprule_from_snapshot (
612 p_map_rule_obj_def_id IN NUMBER,
613 p_api_version IN NUMBER DEFAULT c_api_version,
614 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
615 p_commit IN VARCHAR2 DEFAULT c_false,
616 p_encoded IN VARCHAR2 DEFAULT c_true,
617 x_snapshot_objdef_id OUT NOCOPY NUMBER,
618 x_return_status OUT NOCOPY VARCHAR2,
619 x_msg_count OUT NOCOPY NUMBER,
620 x_msg_data OUT NOCOPY VARCHAR2) IS
621
622 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
623 'fem.plsql.fem_alloc_br_util_pkg.refresh_maprule_from_snapshot';
624 C_API_NAME CONSTANT VARCHAR2(30) := 'Refresh_maprule_from_Snapshot';
625 C_SNAP_OBJTYPE CONSTANT VARCHAR2(30) := 'MAPPING_EDIT_SNAPSHOT';
626
627 v_return_status VARCHAR2(30);
628 v_msg_count NUMBER;
629 v_msg_data VARCHAR2(4000);
630
631 v_helper_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
632 v_snap_objdef_name FEM_OBJECT_DEFINITION_TL.display_name%TYPE;
633 v_snap_objdef_desc FEM_OBJECT_DEFINITION_TL.description%TYPE;
634 v_snap_start_date FEM_OBJECT_DEFINITION_B.effective_start_date%TYPE;
635 v_snap_end_date FEM_OBJECT_DEFINITION_B.effective_start_date%TYPE;
636
637
638 e_unexp EXCEPTION;
639 e_error EXCEPTION;
640 e_invalid_obj EXCEPTION;
641
642
643 v_count number;
644
645 cursor c1 (p_object_definition_id IN NUMBER) IS
646 SELECT distinct sub_object_id, creation_date, last_update_date
647 FROM fem_alloc_br_formula
648 WHERE object_definition_id = p_object_definition_id
649 AND sub_object_id IS NOT NULL;
650
651 BEGIN
652
653 -- Standard Start of API savepoint
654 SAVEPOINT refresh_map_from_snapshot_pub;
655
656
657 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
658 FEM_ENGINES_PKG.TECH_MESSAGE(
659 p_severity => FND_LOG.level_procedure,
660 p_module => C_MODULE,
661 p_msg_text => 'Begin Procedure');
662 END IF;
663
664
665 -- Initialize return status to unexpected error
666 x_return_status := c_unexp;
667
668 -- Check for call compatibility.
669 IF NOT FND_API.Compatible_API_Call (c_api_version,
670 p_api_version,
671 C_API_NAME,
672 G_PKG_NAME)
673 THEN
674 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
675 FEM_ENGINES_PKG.TECH_MESSAGE(
676 p_severity => FND_LOG.level_statement,
677 p_module => C_MODULE,
678 p_msg_text => 'API Version ('||C_API_VERSION||') not compatible with '
679 ||'passed in version ('||p_api_version||')');
680 END IF;
681 RAISE e_unexp;
682 END IF;
683
684
685 Validate_OA_Params (
686 p_api_version => p_api_version,
687 p_init_msg_list => p_init_msg_list,
688 p_commit => p_commit,
689 p_encoded => p_encoded,
690 x_return_status => x_return_status);
691
692 IF (x_return_status <> c_success) THEN
693 RAISE e_error;
694 END IF;
695
696
697
698 /* Verify the mapping rule is valid*/
699 BEGIN
703 FROM fem_object_catalog_vl C, fem_object_definition_vl D
700
701 SELECT 1
702 INTO v_count
704 WHERE D.object_id = C.object_id
705 AND D.object_definition_id = p_map_rule_obj_def_id
706 AND C.object_type_code IN (C_RULE_OBJTYPE, C_DFLT_OBJTYPE);
707
708 EXCEPTION
709 WHEN no_data_found THEN
710 RAISE e_invalid_obj;
711
712 END;
713
714 /* identify the edit snapshot for the mapping rule */
715 fem_helper_rule_util_pkg.get_helper_rule (
716 p_rule_obj_def_id => p_map_rule_obj_def_id,
717 p_helper_object_type_code => C_SNAP_OBJTYPE,
718 p_api_version => C_API_VERSION,
719 p_init_msg_list => C_FALSE,
720 p_commit => C_FALSE,
721 p_encoded => C_TRUE,
722 x_return_status => v_return_status,
723 x_msg_count => v_msg_count,
724 x_msg_data => v_msg_data,
725 x_helper_obj_def_id => v_helper_obj_def_id );
726
727 IF v_return_status NOT IN (c_success) THEN
728 RAISE e_unexp;
729 END IF;
730
731
732 /* delete the content for the true rule */
733 FEM_BR_MAPPING_RULE_PVT.delete_map_rule_content(p_map_rule_obj_def_id);
734
735
736
737 IF x_return_status NOT IN (c_success) THEN
738 RAISE e_unexp;
739 END IF;
740
741
742 /* copy the content from the snapshot to the true rule */
743 fem_br_mapping_rule_pvt.CopyObjectDefinition (
744 p_copy_type_code => fem_business_rule_pvt.g_duplicate
745 ,p_source_obj_def_id => v_helper_obj_def_id
746 ,p_target_obj_def_id => p_map_rule_obj_def_id
747 ,p_created_by => c_user_id
748 ,p_creation_date => sysdate);
749
750 /* update the objdef name/description */
751 SELECT display_name, description, effective_start_date, effective_end_date
752 INTO v_snap_objdef_name, v_snap_objdef_desc, v_snap_start_date, v_snap_end_date
753 FROM fem_object_definition_vl
754 WHERE object_definition_id = v_helper_obj_def_id;
755
756 UPDATE fem_object_definition_vl
757 SET display_name = v_snap_objdef_name, description = v_snap_objdef_desc,
758 effective_start_date = v_snap_start_date, effective_end_date = v_snap_end_date
759 WHERE object_definition_id = p_map_rule_obj_def_id;
760
761 /* Refresh the data in fem_object_dependencies with the new dependency data
762 for the rule. */
763
764 DELETE FROM fem_object_dependencies
765 WHERE object_definition_id = p_map_rule_obj_def_id;
766
767 FOR subobj IN c1 (p_map_rule_obj_def_id) LOOP
768 INSERT INTO fem_object_dependencies
769 (OBJECT_DEFINITION_ID
770 ,REQUIRED_OBJECT_ID
771 ,CREATED_BY
772 ,CREATION_DATE
773 ,LAST_UPDATED_BY
774 ,LAST_UPDATE_DATE
775 ,LAST_UPDATE_LOGIN
776 ,OBJECT_VERSION_NUMBER )
777 VALUES (p_map_rule_obj_def_id
778 ,subobj.sub_object_id
779 ,c_user_id
780 ,subobj.creation_date
781 ,c_user_id
782 ,subobj.last_update_date
783 ,c_login_id
784 ,c_object_version);
785
786 END LOOP;
787
788 --Bug 6348530. Set the visual trace attributes.
789 set_VT_attributes(p_map_rule_obj_def_id);
790
791 x_snapshot_objdef_id := v_helper_obj_def_id;
792 x_return_status := c_success;
793
794
795
796 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
797 FEM_ENGINES_PKG.TECH_MESSAGE(
798 p_severity => FND_LOG.level_procedure,
799 p_module => C_MODULE,
800 p_msg_text => 'End Procedure');
801 END IF;
802
803 IF FND_API.To_Boolean( p_commit ) THEN
804 COMMIT WORK;
805 END IF;
806
807
808 EXCEPTION
809 WHEN e_invalid_obj THEN
810 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
811 FEM_ENGINES_PKG.TECH_MESSAGE(
812 p_severity => FND_LOG.level_statement,
813 p_module => C_MODULE,
814 p_msg_text => 'Mapping Rule Object Definition does not exist'||p_map_rule_obj_def_id);
815 END IF;
816 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
817 p_count => x_msg_count,
818 p_data => x_msg_data);
819 ROLLBACK TO refresh_map_from_snapshot_pub;
820 x_return_status := c_error;
821
822
823
824 WHEN OTHERS THEN
825 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
826 FEM_ENGINES_PKG.TECH_MESSAGE(
827 p_severity => FND_LOG.level_statement,
828 p_module => C_MODULE,
829 p_msg_text => 'Unexpected error.');
830 FEM_ENGINES_PKG.TECH_MESSAGE(
831 p_severity => FND_LOG.level_statement,
832 p_module => C_MODULE,
833 p_msg_text => SQLERRM);
834 END IF;
835 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
836 p_count => x_msg_count,
837 p_data => x_msg_data);
838 ROLLBACK TO refresh_map_from_snapshot_pub;
839 x_return_status := c_unexp;
840
841
842 END refresh_maprule_from_snapshot;
843
844
845
846
847 /*************************************************************************
848
849 Refresh_snapshot_from_maprule
850
851 PURPOSE: updates the snapshot definition with content from the
852 true mapping rule
853
854 *************************************************************************/
855
856 PROCEDURE refresh_snapshot_from_maprule (
857 p_map_rule_obj_def_id IN NUMBER,
858 p_api_version IN NUMBER DEFAULT c_api_version,
859 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
865 x_msg_data OUT NOCOPY VARCHAR2) IS
860 p_commit IN VARCHAR2 DEFAULT c_false,
861 p_encoded IN VARCHAR2 DEFAULT c_true,
862 x_snapshot_objdef_id OUT NOCOPY NUMBER,
863 x_return_status OUT NOCOPY VARCHAR2,
864 x_msg_count OUT NOCOPY NUMBER,
866
867 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
868 'fem.plsql.fem_alloc_br_util_pkg.refresh_snapshot_from_maprule';
869 C_API_NAME CONSTANT VARCHAR2(30) := 'Refresh_Snapshot_from_snapshot';
870
871 v_return_status VARCHAR2(30);
872 v_msg_count NUMBER;
873 v_msg_data VARCHAR2(4000);
874
875 v_helper_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
876
877 v_maprule_objdef_name FEM_OBJECT_DEFINITION_TL.display_name%TYPE;
878 v_maprule_objdef_desc FEM_OBJECT_DEFINITION_TL.description%TYPE;
879 v_maprule_start_date FEM_OBJECT_DEFINITION_B.effective_start_date%TYPE;
880 v_maprule_end_date FEM_OBJECT_DEFINITION_B.effective_start_date%TYPE;
881
882 v_snapshot_object_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
883
884
885 e_unexp EXCEPTION;
886 e_no_helper EXCEPTION;
887 e_error EXCEPTION;
888 e_invalid_obj EXCEPTION;
889 e_objtype EXCEPTION;
890
891
892 v_count number;
893
894 BEGIN
895
896 -- Standard Start of API savepoint
897 SAVEPOINT refresh_snapshot_from_map_pub;
898
899
900 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
901 FEM_ENGINES_PKG.TECH_MESSAGE(
902 p_severity => FND_LOG.level_procedure,
903 p_module => C_MODULE,
904 p_msg_text => 'Begin Procedure');
905 END IF;
906
907
908 -- Initialize return status to unexpected error
909 x_return_status := c_unexp;
910
911 -- Check for call compatibility.
912 IF NOT FND_API.Compatible_API_Call (c_api_version,
913 p_api_version,
914 C_API_NAME,
915 G_PKG_NAME)
916 THEN
917 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
918 FEM_ENGINES_PKG.TECH_MESSAGE(
919 p_severity => FND_LOG.level_statement,
920 p_module => C_MODULE,
921 p_msg_text => 'API Version ('||C_API_VERSION||') not compatible with '
922 ||'passed in version ('||p_api_version||')');
923 END IF;
924 RAISE e_unexp;
925 END IF;
926
927
928 Validate_OA_Params (
929 p_api_version => p_api_version,
930 p_init_msg_list => p_init_msg_list,
931 p_commit => p_commit,
932 p_encoded => p_encoded,
933 x_return_status => x_return_status);
934
935 IF (x_return_status <> c_success) THEN
936 RAISE e_error;
937 END IF;
938
939
940 /* Verify the mapping rule is valid*/
941 BEGIN
942
943 SELECT 1
944 INTO v_count
945 FROM fem_object_catalog_vl C, fem_object_definition_vl D
946 WHERE D.object_id = C.object_id
947 AND D.object_definition_id = p_map_rule_obj_def_id
948 AND C.object_type_code IN (C_RULE_OBJTYPE, C_DFLT_OBJTYPE);
949
950 EXCEPTION
951 WHEN no_data_found THEN
952 RAISE e_invalid_obj;
953
954 END;
955
956 /* identify the edit snapshot for the mapping rule */
957 fem_helper_rule_util_pkg.get_helper_rule (
958 p_rule_obj_def_id => p_map_rule_obj_def_id,
959 p_helper_object_type_code => C_SNAP_OBJTYPE,
960 p_api_version => C_API_VERSION,
961 p_init_msg_list => C_FALSE,
962 p_commit => C_FALSE,
963 p_encoded => C_TRUE,
964 x_return_status => v_return_status,
965 x_msg_count => v_msg_count,
966 x_msg_data => v_msg_data,
967 x_helper_obj_def_id => v_helper_obj_def_id );
968
969 IF v_return_status NOT IN (c_success) THEN
970
971 create_snapshot (
972 p_map_rule_obj_def_id => p_map_rule_obj_def_id,
973 x_snapshot_object_id => v_snapshot_object_id,
974 x_snapshot_objdef_id => v_helper_obj_def_id,
975 x_return_status => x_return_status,
976 x_msg_count => v_msg_count,
977 x_msg_data => v_msg_data);
978
979 END IF;
980
981 IF x_return_status NOT IN (c_success) THEN
982 RAISE e_unexp;
983 END IF;
984
985
986 /* delete the content for the snapshot */
987 FEM_BR_MAPPING_RULE_PVT.delete_map_rule_content(v_helper_obj_def_id);
988
989
990 IF x_return_status NOT IN (c_success) THEN
991 RAISE e_unexp;
992 END IF;
993
994
995 /* copy the content from the true rule to the snapshot */
996 fem_br_mapping_rule_pvt.CopyObjectDefinition (
997 p_copy_type_code => fem_business_rule_pvt.g_duplicate
1001 ,p_creation_date => sysdate);
998 ,p_source_obj_def_id => p_map_rule_obj_def_id
999 ,p_target_obj_def_id => v_helper_obj_def_id
1000 ,p_created_by => c_user_id
1002
1003 /* update the objdef name/description */
1004 SELECT display_name, description, effective_start_date, effective_end_date
1005 INTO v_maprule_objdef_name, v_maprule_objdef_desc,v_maprule_start_date, v_maprule_end_date
1006 FROM fem_object_definition_vl
1007 WHERE object_definition_id = p_map_rule_obj_def_id;
1008
1009 UPDATE fem_object_definition_vl
1010 SET display_name = v_maprule_objdef_name, description = v_maprule_objdef_desc,
1011 effective_start_date = v_maprule_start_date, effective_end_date = v_maprule_end_date
1012 WHERE object_definition_id = v_helper_obj_def_id;
1013
1014
1015 x_snapshot_objdef_id := v_helper_obj_def_id;
1016 x_return_status := c_success;
1017
1018 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1019 FEM_ENGINES_PKG.TECH_MESSAGE(
1020 p_severity => FND_LOG.level_procedure,
1021 p_module => C_MODULE,
1022 p_msg_text => 'End Procedure');
1023 END IF;
1024
1025 IF FND_API.To_Boolean( p_commit ) THEN
1026 COMMIT WORK;
1027 END IF;
1028
1029
1030 EXCEPTION
1031 WHEN e_invalid_obj THEN
1032 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1033 FEM_ENGINES_PKG.TECH_MESSAGE(
1034 p_severity => FND_LOG.level_statement,
1035 p_module => C_MODULE,
1036 p_msg_text => 'Mapping Rule Object Definition does not exist '||p_map_rule_obj_def_id);
1037 END IF;
1038 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
1039 p_count => x_msg_count,
1040 p_data => x_msg_data);
1041 ROLLBACK TO refresh_snapshot_from_map_pub;
1042 x_return_status := c_error;
1043
1044 WHEN e_no_helper THEN
1045 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1046 FEM_ENGINES_PKG.TECH_MESSAGE(
1047 p_severity => FND_LOG.level_statement,
1048 p_module => C_MODULE,
1049 p_msg_text => 'Unable to identify snapshot for map_rule_obj_def_id = '||p_map_rule_obj_def_id);
1050 END IF;
1051 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
1052 p_count => x_msg_count,
1053 p_data => x_msg_data);
1054 ROLLBACK TO refresh_snapshot_from_map_pub;
1055 x_return_status := c_error;
1056
1057
1058
1059 WHEN OTHERS THEN
1060 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1061 FEM_ENGINES_PKG.TECH_MESSAGE(
1062 p_severity => FND_LOG.level_statement,
1063 p_module => C_MODULE,
1064 p_msg_text => 'Unexpected error.');
1065 FEM_ENGINES_PKG.TECH_MESSAGE(
1066 p_severity => FND_LOG.level_statement,
1067 p_module => C_MODULE,
1068 p_msg_text => SQLERRM);
1069 END IF;
1070 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
1071 p_count => x_msg_count,
1072 p_data => x_msg_data);
1073 ROLLBACK TO refresh_snapshot_from_map_pub;
1074 x_return_status := c_unexp;
1075
1076
1077 END refresh_snapshot_from_maprule;
1078
1079
1080
1081 /*************************************************************************
1082
1083 Refresh_snapshot_from_defaults
1084
1085 PURPOSE: updates the snapshot definition with content from the
1086 seeded default
1087
1088 HISTORY:
1089 6/26/2007 Rflippo Bug#6146396 Set any dimension cols in the Mapping Output list
1090 = "SAME_AS_SOURCE" if they don't have a default
1091 assigned in fem_alloc_br_dimensions for the
1092 default rule
1093
1094 *************************************************************************/
1095
1096 PROCEDURE refresh_snapshot_from_defaults (
1097 p_map_rule_obj_def_id IN NUMBER,
1098 p_api_version IN NUMBER DEFAULT c_api_version,
1099 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
1100 p_commit IN VARCHAR2 DEFAULT c_false,
1101 p_encoded IN VARCHAR2 DEFAULT c_true,
1102 x_snapshot_objdef_id OUT NOCOPY NUMBER,
1103 x_return_status OUT NOCOPY VARCHAR2,
1104 x_msg_count OUT NOCOPY NUMBER,
1105 x_msg_data OUT NOCOPY VARCHAR2) IS
1106
1107 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1108 'fem.plsql.fem_alloc_br_util_pkg.refresh_snapshot_from_defaults';
1109 C_API_NAME CONSTANT VARCHAR2(30) := 'Refresh_Snapshot_from_defaults';
1110
1111 v_return_status VARCHAR2(30);
1112 v_msg_count NUMBER;
1113 v_msg_data VARCHAR2(4000);
1114
1115 v_dflt_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
1116 v_map_rule_type_code FEM_ALLOC_BR_OBJECTS.map_rule_type_code%TYPE;
1117 v_helper_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
1118 v_global_vs_combo_id fem_global_vs_combos_b.global_vs_combo_id%TYPE;
1119
1120 e_unexp EXCEPTION;
1121 e_no_default EXCEPTION;
1122 e_error EXCEPTION;
1123 e_invalid_obj EXCEPTION;
1124 e_no_helper EXCEPTION;
1125
1126
1127
1128 v_count number;
1129
1130
1131
1132 BEGIN
1133 -- Standard Start of API savepoint
1134 SAVEPOINT refresh_snapshot_from_dflt_pub;
1135
1136
1137 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1138 FEM_ENGINES_PKG.TECH_MESSAGE(
1139 p_severity => FND_LOG.level_procedure,
1140 p_module => C_MODULE,
1141 p_msg_text => 'Begin Procedure');
1142 END IF;
1143
1144
1148 -- Check for call compatibility.
1145 -- Initialize return status to unexpected error
1146 x_return_status := c_unexp;
1147
1149 IF NOT FND_API.Compatible_API_Call (c_api_version,
1150 p_api_version,
1151 C_API_NAME,
1152 G_PKG_NAME)
1153 THEN
1154 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1155 FEM_ENGINES_PKG.TECH_MESSAGE(
1156 p_severity => FND_LOG.level_statement,
1157 p_module => C_MODULE,
1158 p_msg_text => 'API Version ('||C_API_VERSION||') not compatible with '
1159 ||'passed in version ('||p_api_version||')');
1160 END IF;
1161 RAISE e_unexp;
1162 END IF;
1163
1164
1165 Validate_OA_Params (
1166 p_api_version => p_api_version,
1167 p_init_msg_list => p_init_msg_list,
1168 p_commit => p_commit,
1169 p_encoded => p_encoded,
1170 x_return_status => x_return_status);
1171
1172 IF (x_return_status <> c_success) THEN
1173 RAISE e_error;
1174 END IF;
1175
1176
1177 /* Verify the mapping rule is valid*/
1178 BEGIN
1179
1180 SELECT O.map_rule_type_code
1181 INTO v_map_rule_type_code
1182 FROM fem_object_catalog_vl C, fem_object_definition_vl D,
1183 fem_alloc_br_objects O
1184 WHERE D.object_id = C.object_id
1185 AND D.object_definition_id = p_map_rule_obj_def_id
1186 AND C.object_type_code = 'MAPPING_RULE'
1187 AND C.object_id = O.map_rule_object_id;
1188
1189 EXCEPTION
1190 WHEN no_data_found THEN
1191 RAISE e_invalid_obj;
1192
1193 END;
1194
1195 /* identify the default definition for the mapping rule */
1196 v_global_vs_combo_id := fem_dimension_util_pkg.global_vs_combo_id(
1197 p_ledger_id => null
1198 ,x_return_status => v_return_status
1199 ,x_msg_count => v_msg_count
1200 ,x_msg_data => v_msg_data);
1201
1202 BEGIN
1203 SELECT min (D.object_definition_id)
1204 INTO v_dflt_obj_def_id
1205 FROM fem_object_definition_b D, fem_alloc_br_objects O,
1206 fem_object_catalog_b C
1207 WHERE C.object_id = D.object_id
1208 AND C.object_type_code = C_DFLT_OBJTYPE
1209 AND C.object_id = O.map_rule_object_id
1210 AND O.map_rule_type_code = v_map_rule_type_code
1211 AND C.local_vs_combo_id = v_global_vs_combo_id;
1212
1213 EXCEPTION
1214
1215 WHEN no_data_found THEN v_dflt_obj_def_id := null;
1216
1217 END;
1218
1219 IF v_dflt_obj_def_id IS NULL THEN
1220 RAISE e_no_default;
1221 END IF;
1222 /* identify the edit snapshot for the mapping rule */
1223 fem_helper_rule_util_pkg.get_helper_rule (
1224 p_rule_obj_def_id => p_map_rule_obj_def_id,
1225 p_helper_object_type_code => C_SNAP_OBJTYPE,
1226 p_api_version => C_API_VERSION,
1227 p_init_msg_list => C_FALSE,
1228 p_commit => C_FALSE,
1229 p_encoded => C_TRUE,
1230 x_return_status => v_return_status,
1231 x_msg_count => v_msg_count,
1232 x_msg_data => v_msg_data,
1236 RAISE e_no_helper;
1233 x_helper_obj_def_id => v_helper_obj_def_id );
1234
1235 IF v_return_status NOT IN (c_success) THEN
1237 END IF;
1238
1239
1240 /* delete the content for the snapshot */
1241 FEM_BR_MAPPING_RULE_PVT.delete_map_rule_content(v_helper_obj_def_id);
1242
1243
1244 IF x_return_status NOT IN (c_success) THEN
1245 RAISE e_unexp;
1246 END IF;
1247
1248
1249 /* copy the content from the default rule to the snapshot */
1250 fem_br_mapping_rule_pvt.CopyObjectDefinition (
1251 p_copy_type_code => fem_business_rule_pvt.g_duplicate
1252 ,p_source_obj_def_id => v_dflt_obj_def_id
1253 ,p_target_obj_def_id => v_helper_obj_def_id
1254 ,p_created_by => c_user_id
1255 ,p_creation_date => sysdate);
1256
1257 /*Bug#6146396 Set any dimension cols in the Mapping Output list
1258 that do not have a default assigned to "SAME_AS_SOURCE"
1259 */
1260 set_dim_usage_dflt (v_helper_obj_def_id);
1261
1262 x_snapshot_objdef_id := v_helper_obj_def_id;
1263 x_return_status := c_success;
1264
1265 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1266 FEM_ENGINES_PKG.TECH_MESSAGE(
1267 p_severity => FND_LOG.level_procedure,
1268 p_module => C_MODULE,
1269 p_msg_text => 'End Procedure');
1270 END IF;
1271
1272 IF FND_API.To_Boolean( p_commit ) THEN
1273 COMMIT WORK;
1274 END IF;
1275
1276
1277 EXCEPTION
1278 WHEN e_invalid_obj THEN
1279 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1280 FEM_ENGINES_PKG.TECH_MESSAGE(
1284 END IF;
1281 p_severity => FND_LOG.level_statement,
1282 p_module => C_MODULE,
1283 p_msg_text => 'Mapping Rule Object Definition does not exist '||p_map_rule_obj_def_id);
1285 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
1286 p_count => x_msg_count,
1287 p_data => x_msg_data);
1288 ROLLBACK TO refresh_snapshot_from_dflt_pub;
1289 x_return_status := c_error;
1290
1291 WHEN e_no_default THEN
1292 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1293 FEM_ENGINES_PKG.TECH_MESSAGE(
1294 p_severity => FND_LOG.level_statement,
1295 p_module => C_MODULE,
1296 p_msg_text => 'Mapping Rule defaults seed data is missing');
1297 END IF;
1298 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
1299 p_count => x_msg_count,
1300 p_data => x_msg_data);
1301 ROLLBACK TO refresh_snapshot_from_dflt_pub;
1302 x_return_status := c_error;
1303
1304 WHEN e_no_helper THEN
1305 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1306 FEM_ENGINES_PKG.TECH_MESSAGE(
1307 p_severity => FND_LOG.level_statement,
1308 p_module => C_MODULE,
1309 p_msg_text => 'Unable to identify snapshot for map_rule_obj_def_id = '||p_map_rule_obj_def_id);
1310 END IF;
1311 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
1312 p_count => x_msg_count,
1313 p_data => x_msg_data);
1314 ROLLBACK TO refresh_snapshot_from_dflt_pub;
1315 x_return_status := c_error;
1316
1317
1318
1319 WHEN OTHERS THEN
1320 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1321 FEM_ENGINES_PKG.TECH_MESSAGE(
1322 p_severity => FND_LOG.level_statement,
1323 p_module => C_MODULE,
1324 p_msg_text => 'Unexpected error.');
1325 FEM_ENGINES_PKG.TECH_MESSAGE(
1326 p_severity => FND_LOG.level_statement,
1327 p_module => C_MODULE,
1328 p_msg_text => SQLERRM);
1329 END IF;
1330 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
1331 p_count => x_msg_count,
1332 p_data => x_msg_data);
1333 ROLLBACK TO refresh_snapshot_from_dflt_pub;
1334 x_return_status := c_unexp;
1335
1336
1337 END refresh_snapshot_from_defaults;
1338
1339
1340
1341 /*************************************************************************
1342
1343 get_default_definition
1344
1345 PURPOSE: This procedure queries the db for a default rule definition
1346 for the given rule type. If one exists, it returns it. If
1347 one does not exist, it creates and copies the content from
1348 the seeded default rule definition of that type.
1349
1350 06-JUL-07 RFlippo When creating a default rule, get the obj def name
1351 and description from the seeded default rule
1352 *************************************************************************/
1353
1354 PROCEDURE get_default_definition (
1355 p_map_rule_type_code IN VARCHAR2,
1356 p_target_folder_id IN VARCHAR2 DEFAULT NULL,
1357 p_api_version IN NUMBER DEFAULT c_api_version,
1358 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
1359 p_commit IN VARCHAR2 DEFAULT c_false,
1360 p_encoded IN VARCHAR2 DEFAULT c_true,
1361 x_dflt_objdef_id OUT NOCOPY NUMBER,
1362 x_return_status OUT NOCOPY VARCHAR2,
1363 x_msg_count OUT NOCOPY NUMBER,
1364 x_msg_data OUT NOCOPY VARCHAR2) IS
1365
1366 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1367 'fem.plsql.fem_alloc_br_util_pkg.get_default_definition';
1368 C_API_NAME CONSTANT VARCHAR2(30) := 'Get_default_definition';
1369
1370 v_return_status VARCHAR2(30);
1371 v_msg_count NUMBER;
1372 v_msg_data VARCHAR2(4000);
1373
1374 v_count NUMBER;
1375
1376 v_dflt_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
1377 v_map_rule_type_code FEM_ALLOC_BR_OBJECTS.map_rule_type_code%TYPE;
1378 v_helper_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
1379
1380 v_global_vs_combo_id fem_global_vs_combos_b.global_vs_combo_id%TYPE;
1381 v_object_name fem_object_catalog_tl.object_name%TYPE;
1382 v_description fem_object_catalog_tl.description%TYPE;
1383 v_dflt_object_id fem_object_catalog_b.object_id%TYPE;
1384 v_seeded_dflt_obj_def_id fem_object_definition_b.object_definition_id%TYPE;
1385 v_seeded_dflt_object_id fem_object_catalog_b.object_id%TYPE;
1386 v_new_dflt_object_id fem_object_catalog_b.object_id%TYPE;
1387 v_folder_id fem_object_catalog_b.folder_id%TYPE;
1388 v_dflt_folder_id fem_object_catalog_b.folder_id%TYPE;
1389
1390 v_profile_start_value VARCHAR2(1000);
1391 v_profile_end_value VARCHAR2(1000);
1392 v_effective_start_date DATE;
1393 v_effective_end_date DATE;
1394 v_objdef_name VARCHAR2(150);
1395 v_objdef_desc VARCHAR2(255);
1396
1397
1398 e_unexp EXCEPTION;
1399 e_no_default EXCEPTION;
1400 e_error EXCEPTION;
1401 e_noobj EXCEPTION;
1402 e_no_helper EXCEPTION;
1403 e_no_seeded_dflt EXCEPTION;
1404 e_invalid_rule_type EXCEPTION;
1405
1406
1407
1408 BEGIN
1409 -- Standard Start of API savepoint
1410 SAVEPOINT get_dflt_def_pub;
1411
1412
1413 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1414 FEM_ENGINES_PKG.TECH_MESSAGE(
1415 p_severity => FND_LOG.level_procedure,
1416 p_module => C_MODULE,
1417 p_msg_text => 'Begin Procedure');
1418 END IF;
1419
1420
1424 -- Check for call compatibility.
1421 -- Initialize return status to unexpected error
1422 x_return_status := c_unexp;
1423
1425 IF NOT FND_API.Compatible_API_Call (c_api_version,
1426 p_api_version,
1427 C_API_NAME,
1428 G_PKG_NAME)
1429 THEN
1430 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1431 FEM_ENGINES_PKG.TECH_MESSAGE(
1432 p_severity => FND_LOG.level_statement,
1433 p_module => C_MODULE,
1434 p_msg_text => 'API Version ('||C_API_VERSION||') not compatible with '
1435 ||'passed in version ('||p_api_version||')');
1436 END IF;
1437 RAISE e_unexp;
1438 END IF;
1439
1440
1441 Validate_OA_Params (
1442 p_api_version => p_api_version,
1443 p_init_msg_list => p_init_msg_list,
1444 p_commit => p_commit,
1445 p_encoded => p_encoded,
1446 x_return_status => x_return_status);
1447
1448 IF (x_return_status <> c_success) THEN
1449 RAISE e_error;
1450 END IF;
1451
1452 /* Verify the rule type is valid*/
1453
1454 SELECT count(*)
1455 INTO v_count
1456 FROM fem_map_rule_types_b
1457 WHERE map_rule_type_code = p_map_rule_type_code;
1458
1459 IF v_count = 0 THEN
1460 RAISE e_invalid_rule_type;
1461 END IF;
1462
1463
1464 /* identify the default definition for the rule type and gvsc*/
1465 BEGIN
1466
1467 v_global_vs_combo_id := fem_dimension_util_pkg.global_vs_combo_id(
1468 p_ledger_id => null
1469 ,x_return_status => v_return_status
1470 ,x_msg_count => v_msg_count
1471 ,x_msg_data => v_msg_data);
1472
1473 SELECT min (D.object_definition_id)
1474 INTO x_dflt_objdef_id
1475 FROM fem_object_definition_b D, fem_alloc_br_objects O,
1476 fem_object_catalog_b C
1477 WHERE C.object_id = D.object_id
1478 AND C.object_type_code = C_DFLT_OBJTYPE
1479 AND C.object_id = O.map_rule_object_id
1480 AND O.map_rule_type_code = p_map_rule_type_code
1481 AND C.local_vs_combo_id = v_global_vs_combo_id;
1482
1483
1484
1485
1486 EXCEPTION
1487 WHEN no_data_found THEN x_dflt_objdef_id := null;
1488 END;
1489
1490 IF x_dflt_objdef_id IS NULL THEN
1491 /* No default definition for this gvsc, so we're going to create one */
1492 BEGIN
1493 /* Identify the seeded default rule where gvsc = null for this rule type */
1494 SELECT D1.objdef, C1.object_id
1495 INTO v_seeded_dflt_obj_def_id,v_seeded_dflt_object_id
1496 FROM
1497 (SELECT min (D.object_definition_id) objdef
1498 FROM fem_object_definition_b D, fem_alloc_br_objects O,
1499 fem_object_catalog_b C
1500 WHERE C.object_id = D.object_id
1501 AND C.object_type_code = C_DFLT_OBJTYPE
1502 AND C.object_id = O.map_rule_object_id
1503 AND O.map_rule_type_code = p_map_rule_type_code
1504 AND C.local_vs_combo_id IS NULL) D1,
1505 fem_object_definition_b C1
1506 WHERE D1.objdef = C1.object_definition_id;
1507
1508
1509
1510 EXCEPTION
1511 WHEN no_data_found THEN
1512 raise e_no_seeded_dflt;
1513 END;
1514
1515
1516 SELECT object_name, description, folder_id
1517 INTO v_object_name, v_description, v_dflt_folder_id
1518 FROM fem_object_catalog_vl C
1519 WHERE object_id = v_seeded_dflt_object_id;
1520
1521 IF p_target_folder_id IS NULL THEN
1522 v_folder_id := v_dflt_folder_id;
1523 ELSE v_folder_id := p_target_folder_id;
1524 END IF;
1525
1526 SELECT display_name, description
1527 INTO v_objdef_name, v_objdef_desc
1528 FROM fem_object_definition_vl
1529 WHERE object_definition_id = v_seeded_dflt_obj_def_id;
1530
1531 /* Get the profile option start/end dates */
1532 v_profile_start_value := fnd_profile.value_specific (
1533 'FEM_EFFECTIVE_START_DATE'
1534 ,fnd_global.user_id
1535 ,fnd_global.resp_id
1536 ,fnd_global.prog_appl_id);
1537
1538
1539 v_profile_end_value := fnd_profile.value_specific (
1540 'FEM_EFFECTIVE_END_DATE'
1541 ,fnd_global.user_id
1542 ,fnd_global.resp_id
1543 ,fnd_global.prog_appl_id);
1544
1545 /* Try to get the date value from the profile option
1546 if date value no good, just use the sysdate for start
1547 and maxdate for end*/
1548 BEGIN
1549
1550 IF v_profile_start_value IS NOT NULL THEN
1551 v_effective_start_date := to_date(v_profile_start_value,'YYYY-MM-DD');
1552 ELSE
1553 v_effective_start_date := sysdate;
1554 END IF;
1555
1556 EXCEPTION WHEN OTHERS THEN v_effective_start_date := null;
1557 END;
1558
1559 BEGIN
1560
1561 IF v_profile_end_value IS NOT NULL THEN
1562 v_effective_end_date := to_date(v_profile_end_value,'YYYY-MM-DD');
1563 ELSE
1564 v_effective_end_date := C_MAX_END_DATE;
1565 END IF;
1566
1567 EXCEPTION WHEN OTHERS THEN v_effective_end_date := null;
1568 END;
1569
1570
1571 /* create the new default definition
1572 Note that to ensure uniqueness, the object_name is
1576 ,X_MSG_COUNT => x_msg_count
1573 concatenated with the global combo*/
1574 fem_object_catalog_util_pkg.create_object (x_object_id => v_new_dflt_object_id
1575 ,x_object_definition_id => x_dflt_objdef_id
1577 ,X_MSG_DATA => x_msg_data
1578 ,X_RETURN_STATUS => x_return_status
1579 ,P_API_VERSION => C_API_VERSION
1580 ,P_COMMIT => C_FALSE
1581 ,P_OBJECT_TYPE_CODE => C_DFLT_OBJTYPE
1582 ,P_FOLDER_ID => v_folder_id
1583 ,P_LOCAL_VS_COMBO_ID => v_global_vs_combo_id
1584 ,P_OBJECT_ACCESS_CODE => 'W'
1585 ,P_OBJECT_ORIGIN_CODE => 'USER'
1586 ,P_OBJECT_NAME => v_object_name||':'||v_global_vs_combo_id
1587 ,P_DESCRIPTION => v_description
1588 ,P_EFFECTIVE_START_DATE => v_effective_start_date
1589 ,P_EFFECTIVE_END_DATE => v_effective_end_date
1590 ,P_OBJ_DEF_NAME => v_objdef_name);
1591
1592 IF x_return_status NOT IN (c_success) THEN
1593 RAISE e_noobj;
1594 END IF;
1595
1596 /* Update the default rule description (for all languages),
1597 since we couldn't provide it when we created the default rule object */
1598 UPDATE fem_object_definition_tl
1599 SET description = v_objdef_desc
1600 WHERE object_definition_id = x_dflt_objdef_id;
1601
1602 /* copy the content from the seeded gvsc = null default rule to the new default rule */
1603 fem_br_mapping_rule_pvt.CopyObjectDefinition (
1604 p_copy_type_code => fem_business_rule_pvt.g_duplicate
1605 ,p_source_obj_def_id => v_seeded_dflt_obj_def_id
1606 ,p_target_obj_def_id => x_dflt_objdef_id
1607 ,p_created_by => c_user_id
1608 ,p_creation_date => sysdate);
1609
1610 /* Insert the rule type information into fem_alloc_br_objects */
1611 INSERT INTO fem_alloc_br_objects (
1612 MAP_RULE_OBJECT_ID
1613 ,MAP_RULE_TYPE_CODE
1614 ,OBJECT_VERSION_NUMBER
1615 ,CREATION_DATE
1616 ,CREATED_BY
1617 ,LAST_UPDATED_BY
1618 ,LAST_UPDATE_DATE
1619 ,LAST_UPDATE_LOGIN )
1620 VALUES (v_new_dflt_object_id
1621 , p_map_rule_type_code
1622 ,1
1623 ,sysdate
1624 ,C_USER_ID
1625 ,C_USER_ID
1626 ,sysdate
1627 ,C_LOGIN_ID);
1628
1629
1630 END IF;
1631 x_return_status := c_success;
1632
1633 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1634 FEM_ENGINES_PKG.TECH_MESSAGE(
1635 p_severity => FND_LOG.level_procedure,
1636 p_module => C_MODULE,
1637 p_msg_text => 'End Procedure');
1638 END IF;
1639
1640 IF FND_API.To_Boolean( p_commit ) THEN
1641 COMMIT WORK;
1642 END IF;
1643
1644
1645 EXCEPTION
1646 WHEN e_invalid_rule_type THEN
1647 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1648 FEM_ENGINES_PKG.TECH_MESSAGE(
1649 p_severity => FND_LOG.level_statement,
1650 p_module => C_MODULE,
1651 p_msg_text => 'Mapping Rule Type does not exist '||p_map_rule_type_code);
1652 END IF;
1653 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
1654 p_count => x_msg_count,
1655 p_data => x_msg_data);
1656 ROLLBACK TO get_dflt_def_pub;
1657 x_return_status := c_error;
1658
1659 WHEN e_no_seeded_dflt THEN
1660 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1661 FEM_ENGINES_PKG.TECH_MESSAGE(
1662 p_severity => FND_LOG.level_statement,
1663 p_module => C_MODULE,
1664 p_msg_text => 'Mapping Rule defaults seed data is missing');
1665 END IF;
1666 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
1667 p_count => x_msg_count,
1668 p_data => x_msg_data);
1669 ROLLBACK TO get_dflt_def_pub;
1670 x_return_status := c_error;
1671
1672 WHEN e_noobj THEN
1673 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1674 FEM_ENGINES_PKG.TECH_MESSAGE(
1675 p_severity => FND_LOG.level_statement,
1676 p_module => C_MODULE,
1677 p_msg_text => 'Unable to create object for default rule type = '||p_map_rule_type_code||
1678 ' and global_vs_combo_id ='||v_global_vs_combo_id);
1679 END IF;
1680 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
1681 p_count => x_msg_count,
1682 p_data => x_msg_data);
1683 ROLLBACK TO get_dflt_def_pub;
1684 x_return_status := c_error;
1685
1686
1687
1688 WHEN OTHERS THEN
1689 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1690 FEM_ENGINES_PKG.TECH_MESSAGE(
1691 p_severity => FND_LOG.level_statement,
1692 p_module => C_MODULE,
1693 p_msg_text => 'Unexpected error.');
1694 FEM_ENGINES_PKG.TECH_MESSAGE(
1695 p_severity => FND_LOG.level_statement,
1696 p_module => C_MODULE,
1697 p_msg_text => SQLERRM);
1698 END IF;
1699 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
1700 p_count => x_msg_count,
1701 p_data => x_msg_data);
1702 ROLLBACK TO get_dflt_def_pub;
1703 x_return_status := c_unexp;
1704
1705
1706 END get_default_definition;
1707
1708
1709
1710
1711 /*************************************************************************
1712
1713 OA Exception Handler
1714
1715 *************************************************************************/
1716
1717 PROCEDURE Validate_OA_Params (
1718 p_api_version IN NUMBER,
1719 p_init_msg_list IN VARCHAR2,
1720 p_commit IN VARCHAR2,
1721 p_encoded IN VARCHAR2,
1722 x_return_status OUT NOCOPY VARCHAR2
1726 e_bad_p_init_msg_list EXCEPTION;
1723 )
1724 IS
1725 e_bad_p_api_ver EXCEPTION;
1727 e_bad_p_commit EXCEPTION;
1728 e_bad_p_encoded EXCEPTION;
1729 BEGIN
1730
1731 x_return_status := c_success;
1732
1733 CASE p_api_version
1734 WHEN c_api_version THEN NULL;
1735 ELSE RAISE e_bad_p_api_ver;
1736 END CASE;
1737
1738 CASE p_init_msg_list
1739 WHEN c_false THEN NULL;
1740 WHEN c_true THEN
1741 FND_MSG_PUB.Initialize;
1742 ELSE RAISE e_bad_p_init_msg_list;
1743 END CASE;
1744
1745 CASE p_encoded
1746 WHEN c_false THEN NULL;
1747 WHEN c_true THEN NULL;
1748 ELSE RAISE e_bad_p_encoded;
1749 END CASE;
1750
1751 CASE p_commit
1752 WHEN c_false THEN NULL;
1753 WHEN c_true THEN NULL;
1754 ELSE RAISE e_bad_p_commit;
1755 END CASE;
1756
1757 EXCEPTION
1758 WHEN e_bad_p_api_ver THEN
1759 FEM_ENGINES_PKG.Put_Message(
1760 p_app_name => 'FEM',
1761 p_msg_name => 'FEM_BAD_P_API_VER_ERR',
1762 p_token1 => 'VALUE',
1763 p_value1 => p_api_version);
1764 x_return_status := c_error;
1765
1766 WHEN e_bad_p_init_msg_list THEN
1767 FEM_ENGINES_PKG.Put_Message(
1768 p_app_name => 'FEM',
1769 p_msg_name => 'FEM_BAD_P_INIT_MSG_LIST_ERR');
1770 x_return_status := c_error;
1771
1772 WHEN e_bad_p_encoded THEN
1773 FEM_ENGINES_PKG.Put_Message(
1774 p_app_name => 'FEM',
1775 p_msg_name => 'FEM_BAD_P_ENCODED_ERR');
1776 x_return_status := c_error;
1777
1778 WHEN e_bad_p_commit THEN
1779 FEM_ENGINES_PKG.Put_Message(
1780 p_app_name => 'FEM',
1781 p_msg_name => 'FEM_BAD_P_COMMIT_ERR');
1782 x_return_status := c_error;
1783
1784 END Validate_OA_Params;
1785
1786 /*************************************************************************
1787
1788 get_rule_dirty_flag
1789 This function identifies if the content for a true mapping rule matches
1790 the content stored in the edit snapshot for that rule
1791
1792 *************************************************************************/
1793 FUNCTION get_rule_dirty_flag (p_map_rule_obj_def_id IN NUMBER) RETURN VARCHAR2 IS
1794
1795 v_helper_obj_def_id NUMBER;
1796
1797 v_rule_date DATE;
1798 v_snaprule_date DATE;
1799
1800 C_SNAP_OBJTYPE CONSTANT VARCHAR2(30) := 'MAPPING_EDIT_SNAPSHOT';
1801
1802 v_return_status VARCHAR2(30);
1803 v_msg_count NUMBER;
1804 v_msg_data VARCHAR2(4000);
1805
1806 e_dirty_rule EXCEPTION;
1807
1808 TYPE cv_curs IS REF CURSOR;
1809 TYPE date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1810 TYPE nbr_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1811 TYPE char_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1812
1813 cv_get_ruleformuladata cv_curs;
1814 cv_get_snapformuladata cv_curs;
1815 cv_get_ruledimdata cv_curs;
1816 cv_get_snapdimdata cv_curs;
1817
1818 v_br_cost_flag VARCHAR2(1);
1819 v_br_acc_flag VARCHAR2(1);
1820 v_sbr_cost_flag VARCHAR2(1);
1821 v_sbr_acc_flag VARCHAR2(1);
1822
1823 t_br_function_seq nbr_type;
1824 t_br_function_cd char_type;
1825 t_br_sub_object_id nbr_type;
1826 t_br_value nbr_type;
1827 t_br_table_name char_type;
1828 t_br_column_name char_type;
1829 t_br_math char_type;
1830 t_br_form_macro_cd char_type;
1831 t_br_force char_type;
1832 t_br_enable char_type;
1833 t_br_post_to_ledger char_type;
1834 t_br_open char_type;
1835 t_br_close char_type;
1836 t_br_apply char_type;
1837
1838 t_br_dim_col char_type;
1839 t_br_post_to_balances char_type;
1840 t_br_alloc_dim_usage char_type;
1841 t_br_dim_value nbr_type;
1842 t_br_dim_value_char char_type;
1843 t_br_percent char_type;
1844
1845 t_sbr_function_seq nbr_type;
1846 t_sbr_function_cd char_type;
1847 t_sbr_sub_object_id nbr_type;
1848 t_sbr_value nbr_type;
1849 t_sbr_table_name char_type;
1850 t_sbr_column_name char_type;
1851 t_sbr_math char_type;
1852 t_sbr_form_macro_cd char_type;
1853 t_sbr_force char_type;
1854 t_sbr_enable char_type;
1855 t_sbr_post_to_ledger char_type;
1856 t_sbr_open char_type;
1857 t_sbr_close char_type;
1858 t_sbr_apply char_type;
1859
1860 t_sbr_dim_col char_type;
1861 t_sbr_post_to_balances char_type;
1862 t_sbr_alloc_dim_usage char_type;
1863 t_sbr_dim_value nbr_type;
1864 t_sbr_dim_value_char char_type;
1865 t_sbr_percent char_type;
1866
1867
1868
1869 v_sql VARCHAR2(4000);
1870
1871 BEGIN
1872
1873
1874 /* identify the edit snapshot for the mapping rule */
1875 fem_helper_rule_util_pkg.get_helper_rule (
1876 p_rule_obj_def_id => p_map_rule_obj_def_id,
1877 p_helper_object_type_code => C_SNAP_OBJTYPE,
1878 p_api_version => C_API_VERSION,
1879 p_init_msg_list => C_FALSE,
1880 p_commit => C_FALSE,
1881 p_encoded => C_TRUE,
1882 x_return_status => v_return_status,
1883 x_msg_count => v_msg_count,
1884 x_msg_data => v_msg_data,
1885 x_helper_obj_def_id => v_helper_obj_def_id );
1886
1887 IF v_return_status NOT IN (c_success) THEN
1888 RAISE e_dirty_rule;
1889 END IF;
1890
1891
1892 /* get the business rule data */
1893 SELECT cost_contribution_flag, accumulate_flag
1894 INTO v_br_cost_flag,v_br_acc_flag
1895 FROM fem_alloc_business_rule
1896 WHERE object_definition_id = p_map_rule_obj_def_id;
1897
1898 /* get the snapshot business rule data */
1899 SELECT cost_contribution_flag, accumulate_flag
1903
1900 INTO v_sbr_cost_flag,v_sbr_acc_flag
1901 FROM fem_alloc_business_rule
1902 WHERE object_definition_id = v_helper_obj_def_id;
1904
1905 IF v_br_cost_flag <> v_sbr_cost_flag OR v_br_acc_flag <> v_sbr_acc_flag THEN
1906 RAISE e_dirty_rule;
1907 END IF;
1908
1909 /* get br_formula data */
1910 v_sql := 'SELECT function_seq, function_cd, sub_object_id, value, table_name, '||
1911 'column_name, math_operator_cd,formula_macro_cd,force_to_100_flg,'||
1912 'enable_flg, post_to_ledger_flg, open_paren, close_paren,'||
1913 'apply_to_debit_code'||
1914 ' FROM fem_alloc_br_formula'||
1915 ' WHERE object_definition_id = :1'||
1916 ' ORDER BY function_seq';
1917
1918 OPEN cv_get_ruleformuladata FOR v_sql USING p_map_rule_obj_def_id;
1919
1920 FETCH cv_get_ruleformuladata BULK COLLECT
1921 INTO t_br_function_seq
1922 ,t_br_function_cd
1923 ,t_br_sub_object_id
1924 ,t_br_value
1925 ,t_br_table_name
1926 ,t_br_column_name
1927 ,t_br_math
1928 ,t_br_form_macro_cd
1929 ,t_br_force
1930 ,t_br_enable
1931 ,t_br_post_to_ledger
1932 ,t_br_open
1933 ,t_br_close
1934 ,t_br_apply;
1935
1936
1937 OPEN cv_get_snapformuladata FOR v_sql USING v_helper_obj_def_id;
1938 FETCH cv_get_snapformuladata BULK COLLECT
1939 INTO t_sbr_function_seq
1940 ,t_sbr_function_cd
1941 ,t_sbr_sub_object_id
1942 ,t_sbr_value
1943 ,t_sbr_table_name
1944 ,t_sbr_column_name
1945 ,t_sbr_math
1946 ,t_sbr_form_macro_cd
1947 ,t_sbr_force
1948 ,t_sbr_enable
1949 ,t_sbr_post_to_ledger
1950 ,t_sbr_open
1951 ,t_sbr_close
1952 ,t_sbr_apply;
1953
1954
1955 IF t_br_function_seq.LAST = t_sbr_function_seq.LAST THEN
1956 FOR i IN 1..t_br_function_seq.LAST LOOP
1957 IF t_br_function_seq(i) <> t_sbr_function_seq(i) OR
1958 t_br_function_cd(i) <> t_sbr_function_cd(i) OR
1959 t_br_sub_object_id(i) <> t_sbr_sub_object_id(i) OR
1960 t_br_value(i) <> t_sbr_value(i) OR
1961 t_br_table_name(i) <> t_sbr_table_name(i) OR
1962 t_br_column_name(i) <> t_sbr_column_name(i) OR
1963 t_br_math(i) <> t_sbr_math(i) OR
1964 t_br_form_macro_cd(i) <> t_sbr_form_macro_cd(i) OR
1965 t_br_force(i) <> t_sbr_force(i) OR
1966 t_br_enable(i) <> t_sbr_enable(i) OR
1967 t_br_post_to_ledger(i) <> t_sbr_post_to_ledger(i) OR
1968 t_br_open(i) <> t_sbr_open(i) OR
1969 t_br_close(i) <> t_sbr_close(i) OR
1970 t_br_apply(i) <> t_sbr_apply(i) THEN
1971 RAISE e_dirty_rule;
1972 END IF;
1973 END LOOP;
1974 ELSE RAISE e_dirty_rule;
1975 END IF;
1976
1977 t_br_function_seq.DELETE;
1978 t_br_function_cd.DELETE;
1979 t_sbr_function_seq.DELETE;
1980 t_sbr_function_cd.DELETE;
1981
1982
1983 /* get br_dimensions data */
1984 v_sql := 'SELECT function_seq, alloc_dim_col_name, post_to_balances_flag, '||
1985 'function_cd, alloc_dim_usage_code, dimension_value, dimension_value_char,'||
1986 'percent_distribution_code'||
1987 ' FROM fem_alloc_br_dimensions'||
1988 ' WHERE object_definition_id = :1'||
1989 ' ORDER BY function_seq, alloc_dim_col_name, post_to_balances_flag';
1990
1991 OPEN cv_get_ruledimdata FOR v_sql USING p_map_rule_obj_def_id;
1992 FETCH cv_get_ruledimdata BULK COLLECT
1993 INTO t_br_function_seq
1994 ,t_br_dim_col
1995 ,t_br_post_to_balances
1996 ,t_br_function_cd
1997 ,t_br_alloc_dim_usage
1998 ,t_br_dim_value
1999 ,t_br_dim_value_char
2000 ,t_br_percent;
2001
2002
2003 OPEN cv_get_snapdimdata FOR v_sql USING v_helper_obj_def_id;
2004 FETCH cv_get_snapdimdata BULK COLLECT
2005 INTO t_sbr_function_seq
2006 ,t_sbr_dim_col
2007 ,t_sbr_post_to_balances
2008 ,t_sbr_function_cd
2009 ,t_sbr_alloc_dim_usage
2010 ,t_sbr_dim_value
2011 ,t_sbr_dim_value_char
2012 ,t_sbr_percent;
2013
2014 IF t_br_function_seq.LAST = t_sbr_function_seq.LAST THEN
2015 FOR i IN 1..t_br_function_seq.LAST LOOP
2016 IF t_br_function_seq(i) <> t_sbr_function_seq(i) OR
2017 t_br_dim_col(i) <> t_sbr_dim_col(i) OR
2018 t_br_post_to_balances(i) <> t_sbr_post_to_balances(i) OR
2019 t_br_function_cd(i) <> t_sbr_function_cd(i) OR
2020 t_br_alloc_dim_usage(i) <> t_sbr_alloc_dim_usage(i) OR
2021 t_br_dim_value(i) <> t_sbr_dim_value(i) OR
2022 t_br_dim_value_char(i) <> t_sbr_dim_value_char(i) OR
2023 t_br_percent(i) <> t_sbr_percent(i) THEN
2024 RAISE e_dirty_rule;
2025 END IF;
2026 END LOOP;
2027 ELSE RAISE e_dirty_rule;
2028 END IF;
2029
2030 CLOSE cv_get_ruleformuladata;
2031 CLOSE cv_get_snapformuladata;
2032 CLOSE cv_get_ruledimdata;
2033 CLOSE cv_get_snapdimdata;
2034
2035 RETURN 'N';
2036
2037 EXCEPTION
2038 WHEN e_dirty_rule THEN return 'Y';
2039
2040 WHEN OTHERS THEN return 'Y';
2041
2042 END get_rule_dirty_flag;
2043
2044
2045 /*************************************************************************
2046
2047 generate_condition_summary
2048 This procedure generates the condition summary info and populates the
2049 FEM_ALLOC_BR_COND_SUM_GT table with it
2050
2051 *************************************************************************/
2052 PROCEDURE generate_condition_summary (
2053 p_condition_object_id IN NUMBER,
2054 p_api_version IN NUMBER DEFAULT c_api_version,
2055 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
2059 x_msg_count OUT NOCOPY NUMBER,
2056 p_commit IN VARCHAR2 DEFAULT c_false,
2057 p_encoded IN VARCHAR2 DEFAULT c_true,
2058 x_return_status OUT NOCOPY VARCHAR2,
2060 x_msg_data OUT NOCOPY VARCHAR2) IS
2061
2062 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2063 'fem.plsql.fem_alloc_br_util_pkg.generate_condition_summary';
2064 C_API_NAME CONSTANT VARCHAR2(30) := 'Generate_condition_summary';
2065
2066 v_return_status VARCHAR2(30);
2067 v_msg_count NUMBER;
2068 v_msg_data VARCHAR2(4000);
2069
2070 e_unexp EXCEPTION;
2071 e_invalid_obj EXCEPTION;
2072
2073 v_cond_obj_def_id NUMBER;
2074
2075 v_sql varchar2(4000);
2076
2077 -- Dimension Component Properties
2078 v_table_name FEM_ALLOC_BR_COND_SUM_GT.table_display_name%TYPE;
2079 v_cond_type_code FEM_LOOKUPS.lookup_code%TYPE;
2080 v_cond_type FEM_ALLOC_BR_COND_SUM_GT.condition_type%TYPE;
2081 v_cond_column FEM_ALLOC_BR_COND_SUM_GT.column_display_name%TYPE;
2082 v_cond_value FEM_ALLOC_BR_COND_SUM_GT.condition_value%TYPE;
2083 v_temp_cond_value FEM_ALLOC_BR_COND_SUM_GT.condition_value%TYPE;
2084 v_temp_min_cond_value FEM_ALLOC_BR_COND_SUM_GT.condition_value%TYPE;
2085 v_temp_max_cond_value FEM_ALLOC_BR_COND_SUM_GT.condition_value%TYPE;
2086 v_hier_attr FEM_ALLOC_BR_COND_SUM_GT.hier_attr_display_name%TYPE;
2087 v_dim_comp_type VARCHAR2(1); -- Not shown on the screen - differentiates
2088 -- hierarchy components from Attribute components
2089 v_value_set_id NUMBER; -- Not shown on the screen - identifies the value_set
2090 -- if the member comes from a value set dimension
2091
2092
2093 v_any_table FEM_LOOKUPS.meaning%TYPE;
2094 v_operator_name FEM_LOOKUPS.meaning%TYPE;
2095 v_and_name FEM_LOOKUPS.meaning%TYPE;
2096 -- Dimension Metadata
2097 v_member_table VARCHAR2(30);
2098 v_member_col VARCHAR2(30);
2099 v_member_name_col VARCHAR2(30);
2100 v_vsr_flag VARCHAR2(1); -- value_set_required_flag for the dimension
2101
2102
2103 cursor c_objdef (p_cond_object_id IN NUMBER) IS
2104 SELECT object_definition_id
2105 FROM fem_object_definition_b
2106 WHERE object_id = p_cond_object_id
2107 ORDER BY effective_end_date;
2108
2109 -- Identify the condition components for that Condition Object:
2110 cursor c_comp (p_object_definition_id IN NUMBER) IS
2111 SELECT C.cond_component_obj_id, O.object_definition_id cond_comp_obj_def_id,
2112 C.data_dim_flag,
2113 OB.local_vs_combo_id global_vs_combo_id
2114 FROM fem_cond_components C,
2115 fem_object_definition_b O,
2116 fem_object_catalog_b OB
2117 WHERE C.cond_component_obj_id = O.object_id
2118 AND C.condition_obj_def_id = p_object_definition_id
2119 AND O.object_id = OB.object_id;
2120
2121
2122 cursor c_dimcomp (p_cond_comp_obj_def_id IN NUMBER) IS
2123 SELECT D.dim_comp_type, D.dim_id, D.dim_column, D.value,
2124 T.dimension_name
2125 FROM fem_cond_dim_components D,
2126 fem_dimensions_vl T
2127 WHERE D.cond_dim_cmp_obj_def_id = p_cond_comp_obj_def_id
2128 AND D.dim_id = T.dimension_id;
2129
2130
2131 cursor c_hier (p_cond_obj_def_id IN NUMBER) IS
2132 SELECT D.hierarchy_obj_id, O.object_name
2133 FROM (SELECT DISTINCT hierarchy_obj_id
2134 FROM fem_cond_dim_cmp_dtl D
2135 WHERE cond_dim_cmp_obj_def_id = p_cond_obj_def_id) D,
2136 fem_object_catalog_vl O
2137 WHERE D.hierarchy_obj_id = O.object_id;
2138
2139
2140 cursor c_attr (p_cond_obj_def_id IN NUMBER, p_dimension_id IN NUMBER) IS
2141 SELECT A.attribute_id, A.attribute_name, A.attribute_dimension_id,
2142 A.attribute_data_type_code, D.dim_attr_value
2143 FROM fem_dim_attributes_vl A, fem_cond_dim_cmp_dtl D, fem_cond_dim_components C
2144 WHERE A.dimension_id = p_dimension_id
2145 AND A.dimension_id = C.dim_id
2146 AND D.cond_dim_cmp_obj_def_id = p_cond_obj_def_id
2147 AND D.cond_dim_cmp_obj_def_id = C.cond_dim_cmp_obj_def_id
2148 AND A.attribute_varchar_label = D.dim_attr_varchar_label;
2149
2150 cursor c_steps (p_cond_comp_obj_def_id IN NUMBER) IS
2151 SELECT S.step_sequence, S.table_name, S.step_type, S.column_name, S.operator,
2152 D.value, D.max_range_value,
2153 T.fem_data_type_code, T.dimension_id, T.display_name col_display_name, TA.display_name table_display_name
2154 FROM fem_cond_data_cmp_steps S, fem_cond_data_cmp_st_dtl D,
2155 fem_tab_columns_vl T, fem_tables_vl TA
2156 WHERE S.step_sequence = D.step_sequence
2157 AND S.cond_data_cmp_obj_def_id = D.cond_data_cmp_obj_def_id
2158 AND S.table_name= D.table_name
2159 AND S.cond_data_cmp_obj_def_id = p_cond_comp_obj_def_id
2160 AND S.table_name = T.table_name
2161 AND S.column_name = T.column_name
2162 AND T.table_name = TA.table_name;
2163
2164
2165 BEGIN
2166
2167 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2168 FEM_ENGINES_PKG.TECH_MESSAGE(
2169 p_severity => FND_LOG.level_procedure,
2170 p_module => C_MODULE,
2171 p_msg_text => 'Begin Procedure');
2172 END IF;
2173
2174
2175 -- Initialize return status to unexpected error
2176 x_return_status := c_unexp;
2177
2181 C_API_NAME,
2178 -- Check for call compatibility.
2179 IF NOT FND_API.Compatible_API_Call (c_api_version,
2180 p_api_version,
2182 G_PKG_NAME)
2183 THEN
2184 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2185 FEM_ENGINES_PKG.TECH_MESSAGE(
2186 p_severity => FND_LOG.level_statement,
2187 p_module => C_MODULE,
2188 p_msg_text => 'API Version ('||C_API_VERSION||') not compatible with '
2189 ||'passed in version ('||p_api_version||')');
2190 END IF;
2191 RAISE e_unexp;
2192 END IF;
2193
2194
2195 Validate_OA_Params (
2196 p_api_version => p_api_version,
2197 p_init_msg_list => p_init_msg_list,
2198 p_commit => p_commit,
2199 p_encoded => p_encoded,
2200 x_return_status => x_return_status);
2201
2202 IF (x_return_status <> c_success) THEN
2203 RAISE e_unexp;
2204 END IF;
2205
2206
2207 -- initialize
2208 DELETE FROM fem_alloc_br_cond_sum_gt
2209 WHERE condition_obj_def_id IN (SELECT object_definition_id
2210 FROM fem_object_definition_b
2211 WHERE object_id = p_condition_object_id);
2212
2213 SELECT meaning
2214 INTO v_any_table
2215 FROM fem_lookups
2216 WHERE lookup_type = 'FEM_CONDITION_TABLE_MACROS'
2217 AND lookup_code = 'ANY';
2218
2219
2220 -- get the list of Condition object definitions
2221 FOR objdef IN c_objdef (p_condition_object_id) LOOP
2222
2223 -- Get the list of Condition Component Object Definitions for that Condition
2224 FOR comp in c_comp (objdef.object_definition_id) LOOP
2225
2226
2227 /*************************************************************************
2228 Dimension Components
2229 ***************************************************************************/
2230 IF comp.data_dim_flag IN ('D','V') THEN
2231 v_table_name := v_any_table;
2232
2233 FOR dimcomp IN c_dimcomp (comp.cond_comp_obj_def_id) LOOP
2234 v_cond_column := dimcomp.dimension_name;
2235
2236 IF comp.data_dim_flag = 'D' AND NVL(dimcomp.dim_comp_type,'X') = 'H' THEN
2237 -------------------------------------------------------------------
2238 -- Hierarchy component
2239 SELECT meaning
2240 INTO v_cond_type
2241 FROM fem_lookups
2242 WHERE lookup_type = 'FEM_CONDITION_DIM_COMP_TYPES'
2243 AND lookup_code = 'HIERARCHY';
2244
2245 FOR hier IN c_hier (comp.cond_comp_obj_def_id) LOOP
2246 v_hier_attr := hier.object_name;
2247 v_cond_value := null;
2248
2249 INSERT INTO FEM_ALLOC_BR_COND_SUM_GT (
2250 CONDITION_OBJ_DEF_ID,
2251 TABLE_DISPLAY_NAME,
2252 COLUMN_DISPLAY_NAME,
2253 CONDITION_TYPE,
2254 HIER_ATTR_DISPLAY_NAME,
2255 CONDITION_VALUE )
2256 VALUES (objdef.object_definition_id,
2257 v_table_name,
2258 v_cond_column,
2259 v_cond_type,
2260 v_hier_attr,
2261 v_cond_value);
2262 END LOOP;
2263 -------------------------------------------------------------------
2264 ELSIF comp.data_dim_flag = 'D' AND NVL(dimcomp.dim_comp_type,'X') = 'A' THEN
2265 -------------------------------------------------------------------
2266 -- Attribute component
2267 SELECT meaning
2268 INTO v_cond_type
2269 FROM fem_lookups
2270 WHERE lookup_type = 'FEM_CONDITION_DIM_COMP_TYPES'
2271 AND lookup_code = 'ATTRIBUTE';
2272
2273 FOR attr IN c_attr (comp.cond_comp_obj_def_id,dimcomp.dim_id) LOOP
2274 v_hier_attr := attr.attribute_name;
2275
2276 IF attr.attribute_data_type_code = 'DIMENSION' THEN
2277
2278 SELECT value_set_required_flag
2279 INTO v_vsr_flag
2280 FROM fem_xdim_dimensions
2281 WHERE dimension_id = attr.attribute_dimension_id;
2282
2283 IF v_vsr_flag = 'Y' THEN
2284 v_value_set_id := fem_dimension_util_pkg.dimension_value_set_id (attr.attribute_dimension_id);
2285 ELSE v_value_set_id := null;
2286 END IF;
2287
2288 v_cond_value :=
2289 fem_dimension_util_pkg.get_dim_member_name
2290 (attr.attribute_dimension_id,
2291 attr.dim_attr_value,
2292 v_value_set_id);
2293
2294 ELSE -- NUMBER, VARCHAR or DATE attribute
2295 v_cond_value := attr.dim_attr_value;
2296 END IF;
2297
2298 INSERT INTO FEM_ALLOC_BR_COND_SUM_GT (
2299 CONDITION_OBJ_DEF_ID,
2300 TABLE_DISPLAY_NAME,
2301 COLUMN_DISPLAY_NAME,
2302 CONDITION_TYPE,
2303 HIER_ATTR_DISPLAY_NAME,
2304 CONDITION_VALUE,
2305 COND_VALUE_ATTR_DATA_TYPE )
2306 VALUES (objdef.object_definition_id,
2307 v_table_name,
2308 v_cond_column,
2312 attr.attribute_data_type_code);
2309 v_cond_type,
2310 v_hier_attr,
2311 v_cond_value,
2313
2314 END LOOP; -- c_attr
2315
2316 ELSIF comp.data_dim_flag = 'V' THEN
2317 -------------------------------------------------------------------
2318
2319 -- Dimension value component
2320 v_hier_attr := null;
2321
2322 SELECT meaning
2323 INTO v_cond_type
2324 FROM fem_lookups
2325 WHERE lookup_type = 'FEM_CONDITION_DIM_COMP_TYPES'
2326 AND lookup_code = 'VALUE';
2327
2328 SELECT value_set_required_flag
2329 INTO v_vsr_flag
2330 FROM fem_xdim_dimensions
2331 WHERE dimension_id = dimcomp.dim_id;
2332
2333 IF v_vsr_flag = 'Y' THEN
2334 v_value_set_id := fem_dimension_util_pkg.dimension_value_set_id (dimcomp.dim_id);
2335 ELSE v_value_set_id := null;
2336 END IF;
2337
2338 v_cond_value :=
2339 fem_dimension_util_pkg.get_dim_member_name
2340 (dimcomp.dim_id,
2341 dimcomp.value,
2342 v_value_set_id);
2343
2344
2345 INSERT INTO FEM_ALLOC_BR_COND_SUM_GT (
2346 CONDITION_OBJ_DEF_ID,
2347 TABLE_DISPLAY_NAME,
2348 COLUMN_DISPLAY_NAME,
2349 CONDITION_TYPE,
2350 HIER_ATTR_DISPLAY_NAME,
2351 CONDITION_VALUE )
2352 VALUES (objdef.object_definition_id,
2353 v_table_name,
2354 v_cond_column,
2355 v_cond_type,
2356 v_hier_attr,
2357 v_cond_value);
2358
2359 END IF; -- data_dim_flag
2360 END LOOP; -- c_dimcomp
2361 ELSIF comp.data_dim_flag IN ('T') THEN
2362 -------------------------------------------------------------------
2363 -- Table component
2364 v_hier_attr := null;
2365
2366 FOR step IN c_steps (comp.cond_comp_obj_def_id) LOOP
2367
2368
2369 SELECT meaning
2370 INTO v_operator_name
2371 FROM fem_lookups
2372 WHERE lookup_type = 'FEM_CONDITION_OPERATOR'
2373 AND lookup_code = step.operator;
2374
2375 IF step.step_type ='DATA_SPECIFIC' THEN
2376 SELECT meaning
2377 INTO v_cond_type
2378 FROM fem_lookups
2379 WHERE lookup_type = 'FEM_CONDITION_DATA_STEP_TYPE'
2380 AND lookup_code = 'DATA_SPECIFIC';
2381
2382
2383 IF step.fem_data_type_code = 'DIMENSION' THEN
2384 SELECT value_set_required_flag
2385 INTO v_vsr_flag
2386 FROM fem_xdim_dimensions
2387 WHERE dimension_id = step.dimension_id;
2388
2389 IF v_vsr_flag = 'Y' THEN
2390 v_value_set_id := fem_dimension_util_pkg.dimension_value_set_id (step.dimension_id);
2391 ELSE v_value_set_id := null;
2392 END IF;
2393
2394 v_temp_cond_value :=
2395 fem_dimension_util_pkg.get_dim_member_name
2396 (step.dimension_id,
2397 step.value,
2398 v_value_set_id);
2399
2400 ELSE v_temp_cond_value := step.value;
2401
2402 END IF;
2403
2404 v_cond_value := v_operator_name||' '||v_temp_cond_value;
2405
2406 ELSIF step.step_type = 'DATA_RANGE' THEN
2407
2408 SELECT meaning
2409 INTO v_cond_type
2410 FROM fem_lookups
2411 WHERE lookup_type = 'FEM_CONDITION_DATA_STEP_TYPE'
2412 AND lookup_code = 'DATA_RANGE';
2413
2414 SELECT meaning
2415 INTO v_and_name
2416 FROM fem_lookups
2417 WHERE lookup_type = 'FEM_CONDITION_OPERATOR_AND'
2418 AND lookup_code = 'AND';
2419
2420 IF step.fem_data_type_code = 'DIMENSION' THEN
2421 SELECT value_set_required_flag
2422 INTO v_vsr_flag
2423 FROM fem_xdim_dimensions
2424 WHERE dimension_id = step.dimension_id;
2425
2426 IF v_vsr_flag = 'Y' THEN
2427 v_value_set_id := fem_dimension_util_pkg.dimension_value_set_id (step.dimension_id);
2428 ELSE v_value_set_id := null;
2429 END IF;
2430
2431 v_temp_min_cond_value :=
2432 fem_dimension_util_pkg.get_dim_member_name
2433 (step.dimension_id,
2434 step.value,
2435 v_value_set_id);
2436
2437 v_temp_min_cond_value :=
2438 fem_dimension_util_pkg.get_dim_member_name
2439 (step.dimension_id,
2440 step.max_range_value,
2441 v_value_set_id);
2442 ELSE v_temp_min_cond_value := step.value;
2446 v_cond_value := v_operator_name||' '||
2443 v_temp_max_cond_value := step.max_range_value;
2444
2445 END IF;
2447 v_temp_min_cond_value||' '||
2448 v_and_name||v_temp_max_cond_value;
2449
2450
2451 ELSIF step.step_type = 'DATA_ANOTHER_COL' THEN
2452 SELECT meaning
2453 INTO v_cond_type
2454 FROM fem_lookups
2455 WHERE lookup_type = 'FEM_CONDITION_DATA_STEP_TYPE'
2456 AND lookup_code = 'ANOTHER_COL';
2457
2458 v_cond_value := step.value;
2459
2460 END IF; -- step_type
2461
2462 SELECT display_name
2463 INTO v_table_name
2464 FROM fem_tables_vl
2465 WHERE table_name = step.table_name;
2466
2467 SELECT display_name
2468 INTO v_cond_column
2469 FROM fem_tab_columns_vl
2470 WHERE table_name = step.table_name
2471 AND column_name = step.column_name;
2472
2473
2474 INSERT INTO FEM_ALLOC_BR_COND_SUM_GT (
2475 CONDITION_OBJ_DEF_ID,
2476 TABLE_DISPLAY_NAME,
2477 COLUMN_DISPLAY_NAME,
2478 CONDITION_TYPE,
2479 HIER_ATTR_DISPLAY_NAME,
2480 CONDITION_VALUE )
2481 VALUES (objdef.object_definition_id,
2482 v_table_name,
2483 v_cond_column,
2484 v_cond_type,
2485 v_hier_attr,
2486 v_cond_value);
2487
2488 END LOOP; -- c_steps
2489 END IF; -- data_dim_flag
2490 END LOOP; -- c_comp
2491 END LOOP; -- c_objdef
2492
2493 x_return_status := c_success;
2494
2495 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2496 FEM_ENGINES_PKG.TECH_MESSAGE(
2497 p_severity => FND_LOG.level_procedure,
2498 p_module => C_MODULE,
2499 p_msg_text => 'End Procedure');
2500 END IF;
2501
2502 EXCEPTION
2503 WHEN OTHERS THEN
2504 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2505 FEM_ENGINES_PKG.TECH_MESSAGE(
2506 p_severity => FND_LOG.level_statement,
2507 p_module => C_MODULE,
2508 p_msg_text => 'Unexpected error.');
2509 FEM_ENGINES_PKG.TECH_MESSAGE(
2510 p_severity => FND_LOG.level_statement,
2511 p_module => C_MODULE,
2512 p_msg_text => SQLERRM);
2513 END IF;
2514 x_return_status := c_unexp;
2515
2516
2517 END generate_condition_summary;
2518
2519 /*************************************************************************
2520
2521 create_new_ver_from_defaults
2522
2523 PURPOSE: To create a new mapping rule object definition, and copy
2524 the content from the default mapping rule associated with
2525 that Rule Type
2526
2527 NOTES: Given a Mapping Rule Object ID, this API does the following:
2528 1) Creates an object definition for that mapping rule
2529 2) Creates an edit snapshot for that Mapping Rule Object if
2530 one doesn't already exist (uses the existing snapshot if
2531 it already exists)
2532 3) Populates the snapshot content with the content from the
2533 default for that rule type
2534 4) Populates the mapping rule content with the content from
2535 the snapshot
2536
2537 *************************************************************************/
2538
2539
2540 PROCEDURE create_new_ver_from_defaults (
2541 p_map_rule_obj_id IN NUMBER,
2542 p_api_version IN NUMBER DEFAULT c_api_version,
2543 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
2544 p_commit IN VARCHAR2 DEFAULT c_false,
2545 p_encoded IN VARCHAR2 DEFAULT c_true,
2546 x_map_rule_objdef_id OUT NOCOPY NUMBER,
2547 x_snapshot_objdef_id OUT NOCOPY NUMBER,
2548 x_return_status OUT NOCOPY VARCHAR2,
2549 x_msg_count OUT NOCOPY NUMBER,
2550 x_msg_data OUT NOCOPY VARCHAR2) IS
2551
2552 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2553 'fem.plsql.fem_alloc_br_util_pkg.create_new_ver_from_defaults';
2554 C_API_NAME CONSTANT VARCHAR2(30) := 'create_new_ver_from_defaults';
2555
2556 v_return_status VARCHAR2(30);
2557 v_msg_count NUMBER;
2558 v_msg_data VARCHAR2(4000);
2559
2560 v_fem_obj_def_row FEM_OBJECT_DEFINITION_VL%ROWTYPE;
2561
2562
2563 v_dflt_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
2564 v_snap_obj_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
2565 v_snap_objdef_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
2566 v_map_rule_type_code FEM_ALLOC_BR_OBJECTS.map_rule_type_code%TYPE;
2570 v_object_name fem_object_catalog_tl.object_name%TYPE;
2567
2568
2569 v_folder_id fem_folders_b.folder_id%TYPE;
2571 v_description fem_object_catalog_tl.description%TYPE;
2572 v_dflt_object_id fem_object_catalog_b.object_id%TYPE;
2573
2574
2575 e_unexp EXCEPTION;
2576 e_no_default EXCEPTION;
2577 e_no_objdef EXCEPTION;
2578 e_invalid_obj EXCEPTION;
2579 e_no_seeded_dflt EXCEPTION;
2580 e_noobj EXCEPTION;
2581
2582
2583
2584
2585 v_count number;
2586
2587 BEGIN
2588 -- Standard Start of API savepoint
2589 SAVEPOINT create_new_ver_from_dflt_pub;
2590
2591
2592 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2593 FEM_ENGINES_PKG.TECH_MESSAGE(
2594 p_severity => FND_LOG.level_procedure,
2595 p_module => C_MODULE,
2596 p_msg_text => 'Begin Procedure');
2597 END IF;
2598
2599
2600 -- Initialize return status to unexpected error
2601 x_return_status := c_unexp;
2602
2603 -- Check for call compatibility.
2604 IF NOT FND_API.Compatible_API_Call (c_api_version,
2605 p_api_version,
2606 C_API_NAME,
2607 G_PKG_NAME)
2608 THEN
2609 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2610 FEM_ENGINES_PKG.TECH_MESSAGE(
2611 p_severity => FND_LOG.level_statement,
2612 p_module => C_MODULE,
2613 p_msg_text => 'API Version ('||C_API_VERSION||') not compatible with '
2614 ||'passed in version ('||p_api_version||')');
2615 END IF;
2616 RAISE e_unexp;
2617 END IF;
2618
2619
2620 Validate_OA_Params (
2621 p_api_version => p_api_version,
2622 p_init_msg_list => p_init_msg_list,
2623 p_commit => p_commit,
2624 p_encoded => p_encoded,
2625 x_return_status => x_return_status);
2626
2627 IF (x_return_status <> c_success) THEN
2628 RAISE e_unexp;
2629 END IF;
2630
2631
2632 /* Verify the mapping rule is valid*/
2633 BEGIN
2634
2635 SELECT O.map_rule_type_code, C.folder_id
2636 INTO v_map_rule_type_code, v_folder_id
2637 FROM fem_object_catalog_b C, fem_alloc_br_objects O
2638 WHERE C.object_id = p_map_rule_obj_id
2639 AND C.object_type_code = 'MAPPING_RULE'
2640 AND C.object_id = O.map_rule_object_id;
2641
2642 EXCEPTION
2643 WHEN no_data_found THEN
2644 RAISE e_invalid_obj;
2645
2646 END;
2647
2648 /* identify the default definition for the rule type and gvsc*/
2649 get_default_definition (
2650 p_map_rule_type_code => v_map_rule_type_code,
2651 p_target_folder_id => v_folder_id,
2652 x_dflt_objdef_id => v_dflt_obj_def_id,
2653 x_return_status => v_return_status,
2654 x_msg_count => v_msg_count,
2655 x_msg_data => v_msg_data);
2656
2657
2658
2659 /* Create the mapping rule object definition using default obj def as a template */
2660 SELECT *
2661 INTO v_fem_obj_def_row
2662 FROM fem_object_definition_vl
2663 WHERE object_definition_id = v_dflt_obj_def_id;
2664
2665 FEM_OBJECT_CATALOG_UTIL_PKG.create_object_definition(x_object_definition_id => x_map_rule_objdef_id,
2666 x_msg_count => x_msg_count,
2667 x_msg_data => x_msg_data,
2668 x_return_status => x_return_status,
2669 p_api_version => 1.0,
2670 p_commit => FND_API.G_FALSE,
2671 p_object_id => p_map_rule_obj_id,
2672 p_effective_start_date => v_fem_obj_def_row.effective_start_date,
2673 p_effective_end_date => v_fem_obj_def_row.effective_end_date,
2674 p_obj_def_name => v_fem_obj_def_row.display_name,
2675 p_object_origin_code => 'USER'
2676 );
2677
2678 if(x_return_status <> c_success) THEN
2679 RAISE e_no_objdef;
2680 END IF;
2681
2682 UPDATE fem_object_definition_vl
2683 SET description = v_fem_obj_def_row.description
2684 WHERE object_definition_id = x_map_rule_objdef_id;
2685
2686 /* identify the edit snapshot for the mapping rule */
2687 fem_helper_rule_util_pkg.get_helper_rule (
2688 p_rule_obj_def_id => x_map_rule_objdef_id,
2689 p_helper_object_type_code => C_SNAP_OBJTYPE,
2690 p_api_version => C_API_VERSION,
2691 p_init_msg_list => C_FALSE,
2692 p_commit => C_FALSE,
2693 p_encoded => C_TRUE,
2694 x_return_status => v_return_status,
2695 x_msg_count => v_msg_count,
2696 x_msg_data => v_msg_data,
2697 x_helper_obj_def_id => v_snap_objdef_id );
2698
2699 IF v_return_status NOT IN (c_success) THEN
2700
2701 /* Since there is no existing Edit Snapshot, create one for the Mapping Rule */
2702 create_snapshot(
2703 p_map_rule_obj_def_id => x_map_rule_objdef_id,
2704 x_snapshot_object_id => v_snap_obj_id,
2705 x_snapshot_objdef_id => v_snap_objdef_id,
2706 x_return_status => v_return_status,
2707 x_msg_count => v_msg_count,
2708 x_msg_data => v_msg_data);
2709
2710 END IF;
2711
2712 /* copy the content from the default rule to the snapshot */
2713 fem_br_mapping_rule_pvt.CopyObjectDefinition (
2714 p_copy_type_code => fem_business_rule_pvt.g_duplicate
2715 ,p_source_obj_def_id => v_dflt_obj_def_id
2716 ,p_target_obj_def_id => v_snap_objdef_id
2717 ,p_created_by => c_user_id
2718 ,p_creation_date => sysdate);
2719
2720 /*Bug#6146396 Set any dimension cols in the Mapping Output list
2721 that do not have a default assigned to "SAME_AS_SOURCE"
2722 */
2723 set_dim_usage_dflt (v_snap_objdef_id);
2724
2725
2726 /* copy the content from the snapshot to the rule*/
2727 fem_br_mapping_rule_pvt.CopyObjectDefinition (
2728 p_copy_type_code => fem_business_rule_pvt.g_duplicate
2732 ,p_creation_date => sysdate);
2729 ,p_source_obj_def_id => v_snap_objdef_id
2730 ,p_target_obj_def_id => x_map_rule_objdef_id
2731 ,p_created_by => c_user_id
2733
2734 x_snapshot_objdef_id := v_snap_objdef_id;
2735 x_return_status := c_success;
2736
2737 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2738 FEM_ENGINES_PKG.TECH_MESSAGE(
2739 p_severity => FND_LOG.level_procedure,
2740 p_module => C_MODULE,
2741 p_msg_text => 'End Procedure');
2742 END IF;
2743
2744 IF FND_API.To_Boolean( p_commit ) THEN
2745 COMMIT WORK;
2746 END IF;
2747
2748
2749 EXCEPTION
2750 WHEN e_invalid_obj THEN
2751 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2752 FEM_ENGINES_PKG.TECH_MESSAGE(
2753 p_severity => FND_LOG.level_statement,
2754 p_module => C_MODULE,
2755 p_msg_text => 'Mapping Rule Object does not exist '||p_map_rule_obj_id);
2756 END IF;
2757 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
2758 p_count => x_msg_count,
2759 p_data => x_msg_data);
2760 ROLLBACK TO create_new_ver_from_dflt_pub;
2761 x_return_status := c_error;
2762
2763 WHEN e_no_default THEN
2764 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2765 FEM_ENGINES_PKG.TECH_MESSAGE(
2766 p_severity => FND_LOG.level_statement,
2767 p_module => C_MODULE,
2768 p_msg_text => 'Mapping Rule defaults seed data is missing');
2769 END IF;
2770 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
2771 p_count => x_msg_count,
2772 p_data => x_msg_data);
2773 ROLLBACK TO create_new_ver_from_dflt_pub;
2774 x_return_status := c_error;
2775
2776 WHEN e_no_objdef THEN
2777 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2778 FEM_ENGINES_PKG.TECH_MESSAGE(
2779 p_severity => FND_LOG.level_statement,
2780 p_module => C_MODULE,
2781 p_msg_text => 'Unable to create new object definition');
2782 END IF;
2783 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
2784 p_count => x_msg_count,
2785 p_data => x_msg_data);
2786 ROLLBACK TO create_new_ver_from_dflt_pub;
2787 x_return_status := c_error;
2788
2789
2790 WHEN OTHERS THEN
2791 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2792 FEM_ENGINES_PKG.TECH_MESSAGE(
2793 p_severity => FND_LOG.level_statement,
2794 p_module => C_MODULE,
2795 p_msg_text => 'Unexpected error.');
2796 FEM_ENGINES_PKG.TECH_MESSAGE(
2797 p_severity => FND_LOG.level_statement,
2798 p_module => C_MODULE,
2799 p_msg_text => SQLERRM);
2800 END IF;
2801 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
2802 p_count => x_msg_count,
2803 p_data => x_msg_data);
2804 ROLLBACK TO create_new_ver_from_dflt_pub;
2805 x_return_status := c_unexp;
2806
2807
2808 END create_new_ver_from_defaults;
2809
2810 /*************************************************************************
2811
2812 defaults_exist
2813
2814 PURPOSE: To identify whether or not a default rule exists for a given rule type
2815
2816 **************************************************************************/
2817
2818 FUNCTION defaults_exist(p_map_rule_type_code IN VARCHAR2) RETURN VARCHAR2 IS
2819
2820 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2821 'fem.plsql.fem_alloc_br_util_pkg.defaults_exist';
2822 C_API_NAME CONSTANT VARCHAR2(30) := 'Defaults_exist';
2823
2824
2825 v_defaults_exist_flag VARCHAR2(1) := 'N';
2826
2827 v_return_status VARCHAR2(30);
2828 v_msg_count NUMBER;
2829 v_msg_data VARCHAR2(4000);
2830
2831 v_count NUMBER;
2832
2833 v_global_vs_combo_id fem_global_vs_combos_b.global_vs_combo_id%TYPE;
2834 v_dflt_objdef_id fem_object_definition_b.object_definition_id%TYPE;
2835
2836
2837 BEGIN
2838
2839
2840 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2841 FEM_ENGINES_PKG.TECH_MESSAGE(
2842 p_severity => FND_LOG.level_procedure,
2843 p_module => C_MODULE,
2844 p_msg_text => 'Begin Procedure');
2845 END IF;
2846
2847
2848 /* identify the default definition for the rule type and gvsc*/
2849
2850
2851 v_global_vs_combo_id := fem_dimension_util_pkg.global_vs_combo_id(
2852 p_ledger_id => null
2853 ,x_return_status => v_return_status
2854 ,x_msg_count => v_msg_count
2855 ,x_msg_data => v_msg_data);
2856
2857 BEGIN
2858 SELECT min (D.object_definition_id)
2859 INTO v_dflt_objdef_id
2860 FROM fem_object_definition_b D, fem_alloc_br_objects O,
2861 fem_object_catalog_b C
2862 WHERE C.object_id = D.object_id
2863 AND C.object_type_code = C_DFLT_OBJTYPE
2864 AND C.object_id = O.map_rule_object_id
2865 AND O.map_rule_type_code = p_map_rule_type_code
2866 AND C.local_vs_combo_id = v_global_vs_combo_id;
2867
2868 EXCEPTION
2869 WHEN no_data_found THEN v_dflt_objdef_id := null;
2870 END;
2871
2872 IF v_dflt_objdef_id IS NOT NULL THEN
2873 v_defaults_exist_flag := 'Y';
2874 END IF;
2875
2876 RETURN v_defaults_exist_flag;
2877
2878 EXCEPTION
2879 WHEN OTHERS THEN RETURN 'N';
2880
2881 END defaults_exist;
2882
2883
2887 This procedure generates the Factor table summary info and populates the
2884 /*************************************************************************
2885
2886 generate_fctr_summary
2888 FEM_ALLOC_BR_FCTR_SUM_GT table with it
2889
2890 *************************************************************************/
2891 PROCEDURE generate_fctr_summary (
2892 p_fctr_object_id IN NUMBER,
2893 p_api_version IN NUMBER DEFAULT c_api_version,
2894 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
2895 p_commit IN VARCHAR2 DEFAULT c_false,
2896 p_encoded IN VARCHAR2 DEFAULT c_true,
2897 x_return_status OUT NOCOPY VARCHAR2,
2898 x_msg_count OUT NOCOPY NUMBER,
2899 x_msg_data OUT NOCOPY VARCHAR2) IS
2900
2901 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2902 'fem.plsql.fem_alloc_br_util_pkg.generate_fctr_summary';
2903 C_API_NAME CONSTANT VARCHAR2(30) := 'Generate_fctr_summary';
2904
2905 v_return_status VARCHAR2(30);
2906 v_msg_count NUMBER;
2907 v_msg_data VARCHAR2(4000);
2908
2909 e_unexp EXCEPTION;
2910 e_invalid_obj EXCEPTION;
2911
2912 v_fctr_obj_def_id NUMBER;
2913
2914 v_sql varchar2(4000);
2915
2916 -- Dimension Component Properties
2917 v_matching_dimension_name FEM_ALLOC_BR_FCTR_SUM_GT.matching_dimension_name%TYPE;
2918 v_hier_relation_code FEM_LOOKUPS.lookup_code%TYPE;
2919 v_hier_name FEM_ALLOC_BR_FCTR_SUM_GT.hierarchy_name%TYPE;
2920 v_group_name FEM_ALLOC_BR_FCTR_SUM_GT.dimension_group_name%TYPE;
2921 v_hier_relation_desc FEM_ALLOC_BR_FCTR_SUM_GT.hier_relation_desc%TYPE;
2922
2923
2924
2925 cursor c_objdef (p_cond_object_id IN NUMBER) IS
2926 SELECT object_definition_id
2927 FROM fem_object_definition_b
2928 WHERE object_id = p_fctr_object_id
2929 ORDER BY effective_end_date;
2930
2931 -- Identify the factor table dimensions for that Factor Table Object:
2932 cursor c_dim (p_object_definition_id IN NUMBER) IS
2933 SELECT D.dimension_name, F.hier_object_id, F.hier_group_id, F.hier_relation_code
2934 FROM fem_dimensions_vl D, fem_factor_table_dims F
2935 WHERE F.dimension_id = D.dimension_id
2936 AND F.object_definition_id = p_object_definition_id
2937 AND F.dim_usage_code = 'MATCH';
2938
2939
2940 BEGIN
2941
2942 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2943 FEM_ENGINES_PKG.TECH_MESSAGE(
2944 p_severity => FND_LOG.level_procedure,
2945 p_module => C_MODULE,
2946 p_msg_text => 'Begin Procedure');
2947 END IF;
2948
2949
2950 -- Initialize return status to unexpected error
2951 x_return_status := c_unexp;
2952
2953 -- Check for call compatibility.
2954 IF NOT FND_API.Compatible_API_Call (c_api_version,
2955 p_api_version,
2956 C_API_NAME,
2957 G_PKG_NAME)
2958 THEN
2959 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2960 FEM_ENGINES_PKG.TECH_MESSAGE(
2961 p_severity => FND_LOG.level_statement,
2962 p_module => C_MODULE,
2963 p_msg_text => 'API Version ('||C_API_VERSION||') not compatible with '
2964 ||'passed in version ('||p_api_version||')');
2965 END IF;
2966 RAISE e_unexp;
2967 END IF;
2968
2969
2970 Validate_OA_Params (
2971 p_api_version => p_api_version,
2972 p_init_msg_list => p_init_msg_list,
2973 p_commit => p_commit,
2974 p_encoded => p_encoded,
2975 x_return_status => x_return_status);
2976
2977 IF (x_return_status <> c_success) THEN
2978 RAISE e_unexp;
2979 END IF;
2980
2981
2982 -- initialize
2983 DELETE FROM fem_alloc_br_fctr_sum_gt
2984 WHERE factor_obj_def_id IN (SELECT object_definition_id
2985 FROM fem_object_definition_b
2986 WHERE object_id = p_fctr_object_id);
2987
2988
2989
2990 -- get the list of Factor Table object definitions
2991 FOR objdef IN c_objdef (p_fctr_object_id) LOOP
2992
2993 -- Get the list of Factor Table Dimensions
2994 FOR dim in c_dim (objdef.object_definition_id) LOOP
2995
2996 /*************************************************************************
2997 Factor Table Dimensions
2998 ***************************************************************************/
2999 IF dim.hier_object_id IS NOT NULL THEN
3000
3001 SELECT object_name
3002 INTO v_hier_name
3003 FROM fem_object_catalog_vl
3004 WHERE object_id = dim.hier_object_id;
3005 END IF;
3006
3007 IF dim.hier_group_id IS NOT NULL THEN
3008
3009 SELECT dimension_group_name
3010 INTO v_group_name
3011 FROM fem_dimension_grps_vl
3012 WHERE dimension_group_id = dim.hier_group_id;
3013 END IF;
3014
3015 IF dim.hier_relation_code IS NOT NULL THEN
3016 SELECT meaning
3017 INTO v_hier_relation_desc
3018 FROM fem_lookups
3019 WHERE lookup_type = 'FEM_COND_HIER_RELATIONS'
3020 AND lookup_code = dim.hier_relation_code;
3021
3022 END IF;
3023
3024 INSERT INTO FEM_ALLOC_BR_FCTR_SUM_GT (
3025 FACTOR_OBJ_DEF_ID,
3026 MATCHING_DIMENSION_NAME,
3027 HIERARCHY_NAME,
3028 DIMENSION_GROUP_NAME,
3029 HIER_RELATION_DESC )
3030 VALUES (objdef.object_definition_id,
3034 v_hier_relation_desc );
3031 dim.dimension_name,
3032 v_hier_name,
3033 v_group_name,
3035 END LOOP; -- c_dim
3036 END LOOP; -- c_objdef
3037
3038 x_return_status := c_success;
3039
3040 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3041 FEM_ENGINES_PKG.TECH_MESSAGE(
3042 p_severity => FND_LOG.level_procedure,
3043 p_module => C_MODULE,
3044 p_msg_text => 'End Procedure');
3045 END IF;
3046
3047 EXCEPTION
3048 WHEN OTHERS THEN
3049 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3050 FEM_ENGINES_PKG.TECH_MESSAGE(
3051 p_severity => FND_LOG.level_statement,
3052 p_module => C_MODULE,
3053 p_msg_text => 'Unexpected error.');
3054 FEM_ENGINES_PKG.TECH_MESSAGE(
3055 p_severity => FND_LOG.level_statement,
3056 p_module => C_MODULE,
3057 p_msg_text => SQLERRM);
3058 END IF;
3059 x_return_status := c_unexp;
3060
3061
3062 END generate_fctr_summary;
3063
3064 procedure set_VT_attributes(p_object_definition_id NUMBER)
3065 IS
3066 v_map_rule_type_code VARCHAR2(30) := NULL;
3067 v_src_flag NUMBER := 0;
3068 v_drv_flag NUMBER := 0;
3069 v_trace_contrib_flag NUMBER := 0;
3070 v_stat_count NUMBER := 0;
3071 v_row_count NUMBER := 0;
3072 e_unexp EXCEPTION;
3073 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3074 'fem.plsql.fem_alloc_br_util_pkg.set_VT_attributes';
3075
3076 BEGIN
3077
3078 --Get the mapping Rule Type Code.
3079 BEGIN
3080
3081 select map_rule_type_code into v_map_rule_type_code
3082 from fem_alloc_br_objects abo,fem_object_definition_b defs
3083 where defs.object_definition_id = p_object_definition_id
3084 and abo.map_rule_object_id = defs.object_id ;
3085
3086 EXCEPTION
3087 WHEN no_data_found THEN
3088 raise e_unexp;
3089 END;
3090
3091 IF v_map_rule_type_code <> 'ADJUSTMENT' THEN
3092 v_src_flag := 1;
3093 END IF;
3094
3095 IF v_map_rule_type_code = 'PERCENT_DISTRIBUTION' THEN
3096 v_drv_flag := 1;
3097 ELSIF v_map_rule_type_code = 'DIMENSION' THEN
3098 v_drv_flag := 1;
3099 ELSIF v_map_rule_type_code = 'RETRIEVE_STATISTICS' THEN
3100 --Get the no. of statistics within the rule.
3101 select count(*) into v_stat_count
3102 from fem_alloc_br_formula
3103 where object_definition_id = p_object_definition_id
3104 and function_cd = 'TABLE_ACCESS';
3105
3106 IF v_stat_count = 1 THEN
3107 v_drv_flag := 1;
3108 END IF;
3109
3110 END IF;
3111
3112 --Get the Track Contributions flag.
3113 BEGIN
3114 select decode(NVL(cost_contribution_flag,'N'),'Y',1,0)
3115 into v_trace_contrib_flag
3116 from fem_alloc_business_rule
3117 where object_definition_id = p_object_definition_id;
3118
3119 EXCEPTION
3120 WHEN no_data_found THEN
3121 raise e_unexp;
3122 END;
3123
3124
3125 SELECT count(*) INTO v_row_count
3126 FROM fem_vt_obj_def_attribs
3127 WHERE object_definition_id = p_object_definition_id;
3128
3129 IF v_row_count = 0 THEN
3130 INSERT INTO fem_vt_obj_def_attribs(object_definition_id,source_enabled_flg,
3131 driver_enabled_flg,trace_contribution_enabled_flg,
3132 created_by,creation_date,last_updated_by,
3133 last_update_date,last_update_login,object_version_number)
3134 VALUES (p_object_definition_id,v_src_flag,
3135 v_drv_flag,v_trace_contrib_flag,
3136 c_user_id,sysdate,c_user_id,sysdate,c_login_id,0);
3137 ELSE
3138
3139 UPDATE fem_vt_obj_def_attribs
3140 SET source_enabled_flg = v_src_flag,driver_enabled_flg = v_drv_flag,
3141 trace_contribution_enabled_flg = v_trace_contrib_flag ,
3142 Last_update_date = sysdate,last_update_login =c_login_id,
3143 last_updated_by=c_user_id,object_version_number = (object_version_number+1)
3144 WHERE object_definition_id = p_object_definition_id;
3145 END IF;
3146
3147
3148 EXCEPTION
3149 WHEN others THEN
3150 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3151 FEM_ENGINES_PKG.TECH_MESSAGE(
3152 p_severity => FND_LOG.level_statement,
3153 p_module => C_MODULE,
3154 p_msg_text => 'Unexpected error.');
3155 FEM_ENGINES_PKG.TECH_MESSAGE(
3156 p_severity => FND_LOG.level_statement,
3157 p_module => C_MODULE,
3158 p_msg_text => SQLERRM);
3159 raise e_unexp;
3160 END IF;
3161
3162
3163 END set_VT_attributes;
3164
3165 END FEM_alloc_br_Util_Pkg;