[Home] [Help]
PACKAGE BODY: APPS.FEM_INTG_DIM_RULE_ENG_PKG
Source
1 PACKAGE BODY fem_intg_dim_rule_eng_pkg AS
2 /* $Header: fem_intg_dim_eng.plb 120.10 2008/04/01 07:01:24 rguerrer ship $ */
3
4 pc_log_level_statement CONSTANT NUMBER := FND_LOG.level_statement;
5 pc_log_level_procedure CONSTANT NUMBER := FND_LOG.level_procedure;
6 pc_log_level_event CONSTANT NUMBER := FND_LOG.level_event;
7 pc_log_level_exception CONSTANT NUMBER := FND_LOG.level_exception;
8 pc_log_level_error CONSTANT NUMBER := FND_LOG.level_error;
9 pc_log_level_unexpected CONSTANT NUMBER := FND_LOG.level_unexpected;
10 pc_module_name CONSTANT VARCHAR2(100) := 'fem.plsql.fem_intg_dim_rule_eng_pkg';
11 pv_crlf CONSTANT VARCHAR2(1) := '
12 ';
13
14 pv_progress varchar2(100) ;
15
16 PROCEDURE create_map_placeholder_records(
17 p_coa_id IN NUMBER,
18 p_gvsc_id IN NUMBER,
19 p_max_ccid_in_map_table IN NUMBER,
20 p_max_ccid_in_glccid_table IN NUMBER,
21 x_rows_processed OUT NOCOPY NUMBER
22 );
23
24 PROCEDURE print_pkg_variable_values;
25 PROCEDURE register_fem_value_set (p_fem_value_set_id IN NUMBER
26 ,p_regiser_type IN VARCHAR2
27 ,p_fnd_vs_id IN NUMBER
28 ,p_dim_id IN NUMBER
29 ,x_status IN OUT NOCOPY NUMBER);
30
31
32 PROCEDURE Init IS
33 v_object_id NUMBER;
34 c_func_name CONSTANT VARCHAR2(30) := '.init';
35 FEMOGL_com_dim_missing EXCEPTION;
36 FEMOGL_cc_dim_missing EXCEPTION;
37 FEMOGL_cctr_org_dim_missing EXCEPTION;
38
39 --
40 -- Find FlexField Qualified Segment number
41 --
42 -- Note that a matched segment column name is compared with
43 -- a list of mapping column names in FEM_INTG_DIM_RULE_DEFS
44 -- and a matched index number of the list will be returned.
45 --
46 CURSOR FlexQualifiedSegmentNum(
47 c_qualifier VARCHAR2,
48 c_dimension_id NUMBER
49 ) IS
50 SELECT
51 CASE
52 WHEN D.APPLICATION_COLUMN_NAME1 =
53 A.APPLICATION_COLUMN_NAME THEN 1
54 WHEN D.APPLICATION_COLUMN_NAME2 =
55 A.APPLICATION_COLUMN_NAME THEN 2
56 WHEN D.APPLICATION_COLUMN_NAME3 =
57 A.APPLICATION_COLUMN_NAME THEN 3
58 WHEN D.APPLICATION_COLUMN_NAME4 =
59 A.APPLICATION_COLUMN_NAME THEN 4
60 WHEN D.APPLICATION_COLUMN_NAME5 =
61 A.APPLICATION_COLUMN_NAME THEN 5
62 ELSE NULL
63 END SEGMENT_NUMBER
64 FROM
65 FEM_INTG_DIM_RULES R,
66 FEM_OBJECT_DEFINITION_B OD,
67 FEM_INTG_DIM_RULE_DEFS D,
68 FND_SEGMENT_ATTRIBUTE_VALUES A
69 WHERE
70 R.CHART_OF_ACCOUNTS_ID = pv_coa_id AND
71 R.DIMENSION_ID = c_dimension_id AND
72 OD.OBJECT_ID = R.DIM_RULE_OBJ_ID AND
73 D.DIM_RULE_OBJ_DEF_ID = OD.OBJECT_DEFINITION_ID AND
74 A.APPLICATION_ID = 101 AND
75 A.ID_FLEX_CODE = 'GL#' AND
76 A.ID_FLEX_NUM = pv_coa_id AND
77 A.SEGMENT_ATTRIBUTE_TYPE = c_qualifier AND
78 A.ATTRIBUTE_VALUE = 'Y';
79
80 BEGIN
81 pv_progress := 'Start';
82 FEM_ENGINES_PKG.Tech_Message
83 ( p_severity => pc_log_level_procedure
84 ,p_module => pc_module_name||c_func_name
85 ,p_app_name => 'FEM'
86 ,p_msg_name => 'FEM_GL_POST_201'
87 ,p_token1 => 'FUNC_NAME'
88 ,p_value1 => pc_module_name||c_func_name
89 ,p_token2 => 'TIME'
90 ,p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
91
92 pv_progress := 'Fetch dim_id, varchar lable, coa_id , coa name';
93
94 SELECT R.DIMENSION_ID
95 ,DECODE(R.DIMENSION_ID,
96 0, 'INTERCOMPANY',
97 B.DIMENSION_VARCHAR_LABEL)
98 ,R.CHART_OF_ACCOUNTS_ID
99 ,F.ID_FLEX_STRUCTURE_CODE
100 INTO pv_dim_id
101 ,pv_dim_varchar_label
102 ,pv_coa_id
103 ,pv_coa_name
104 FROM FEM_INTG_DIM_RULES R
105 ,FND_ID_FLEX_STRUCTURES F
106 ,FEM_DIMENSIONS_B B
107 WHERE R.DIM_RULE_OBJ_ID = pv_dim_rule_obj_id
108 AND F.APPLICATION_ID = 101
109 AND F.ID_FLEX_CODE = 'GL#'
110 AND F.ID_FLEX_NUM = R.CHART_OF_ACCOUNTS_ID
111 AND DECODE(R.DIMENSION_ID,
112 0, B.DIMENSION_VARCHAR_LABEL,
113 B.DIMENSION_ID) =
114 DECODE(R.DIMENSION_ID,
115 0, 'COMPANY_COST_CENTER_ORG',
116 R.DIMENSION_ID);
117
118 FEM_ENGINES_PKG.Tech_Message
119 ( p_severity => pc_log_level_procedure
120 ,p_module => pc_module_name||c_func_name
121 ,p_msg_text => 'Initialize pv_mapped_segs array');
122
123 pv_progress := 'Extend mapped segs';
124 FOR i IN pv_mapped_segs.count()..4
125 LOOP
126 pv_mapped_segs.extend();
127 END LOOP;
128
129 pv_progress := 'Initialize major package variables 1 of 3';
130
131 FEM_ENGINES_PKG.Tech_Message
132 ( p_severity => pc_log_level_procedure
133 ,p_module => pc_module_name||c_func_name
134 ,p_msg_text => 'Initialize major package variables 1 of 3 ');
135
136 SELECT SEGMENT_COUNT
137 , DIM_MAPPING_OPTION_CODE
138 , DEFAULT_MEMBER_ID
139 , DEFAULT_MEMBER_VALUE_SET_ID
140 , NVL(MAX_CCID_PROCESSED,-1)
141 , NVL(MAX_FLEX_VALUE_ID_PROCESSED,-1)
142 , DECODE(DIM_MAPPING_OPTION_CODE, 'SINGLEVAL'
143 ,DEFAULT_MEMBER_VALUE_SET_ID,
144 NVL(FEM_VALUE_SET_ID,-1))
145 , APPLICATION_COLUMN_NAME1
146 , NVL(APPLICATION_COLUMN_NAME2,'-99')
147 , NVL(APPLICATION_COLUMN_NAME3,'-99')
148 , NVL(APPLICATION_COLUMN_NAME4,'-99')
149 , NVL(APPLICATION_COLUMN_NAME5,'-99')
150 INTO pv_segment_count
151 , pv_dim_mapping_option_code
152 , pv_default_member_id
153 , pv_default_member_vs_id
154 , pv_max_ccid_processed
155 , pv_max_flex_value_id_processed
156 , pv_fem_vs_id
157 , pv_mapped_segs(1).application_column_name
158 , pv_mapped_segs(2).application_column_name
159 , pv_mapped_segs(3).application_column_name
160 , pv_mapped_segs(4).application_column_name
161 , pv_mapped_segs(5).application_column_name
162 FROM FEM_INTG_DIM_RULE_DEFS
163 WHERE DIM_RULE_OBJ_DEF_ID = pv_dim_rule_obj_def_id;
164
165 FOR i in 1..5
166 LOOP
167 pv_mapped_segs(i).vs_id := -99;
168 END LOOP;
169
170 FEM_ENGINES_PKG.Tech_Message
171 ( p_severity => pc_log_level_procedure
172 ,p_module => pc_module_name||c_func_name
173 ,p_msg_text => 'Initialize major package variables 2 of 3 ');
174
175 FOR i IN 1..pv_segment_count LOOP
176 BEGIN
177 SELECT NVL(S.FLEX_VALUE_SET_ID,-99),
178 DECODE(V.VALIDATION_TYPE, 'F', 'Y', 'N'),
179 T.APPLICATION_TABLE_NAME,
180 T.ID_COLUMN_NAME,
181 T.VALUE_COLUMN_NAME,
182 T.COMPILED_ATTRIBUTE_COLUMN_NAME,
183 T.MEANING_COLUMN_NAME,
184 T.ADDITIONAL_WHERE_CLAUSE,
185 DECODE(V.VALIDATION_TYPE,'D','Y','N'),
186 DECODE(V.VALIDATION_TYPE,'D',V.PARENT_FLEX_VALUE_SET_ID,NULL)
187 INTO pv_mapped_segs(i).vs_id,
188 pv_mapped_segs(i).table_validated_flag,
189 pv_mapped_segs(i).table_name,
190 pv_mapped_segs(i).id_col_name,
191 pv_mapped_segs(i).val_col_name,
192 pv_mapped_segs(i).compiled_attr_col_name,
193 pv_mapped_segs(i).meaning_col_name,
194 pv_mapped_segs(i).where_clause,
195 pv_mapped_segs(i).dependent_value_set_flag,
196 pv_mapped_segs(i).dependent_vs_id
197 FROM FND_ID_FLEX_SEGMENTS S,
198 FND_FLEX_VALUE_SETS V,
199 FND_FLEX_VALIDATION_TABLES T
200 WHERE S.APPLICATION_ID = 101
201 AND S.ID_FLEX_CODE = 'GL#'
202 AND S.ID_FLEX_NUM = pv_coa_id
203 AND S.APPLICATION_COLUMN_NAME =
204 pv_mapped_segs(i).application_column_name
205 AND V.FLEX_VALUE_SET_ID = NVL(S.FLEX_VALUE_SET_ID, -99)
206 AND T.FLEX_VALUE_SET_ID (+) = V.FLEX_VALUE_SET_ID;
207 EXCEPTION
208 WHEN OTHERS THEN
209 FEM_ENGINES_PKG.Tech_Message
210 ( p_severity => pc_log_level_procedure
211 ,p_module => pc_module_name||c_func_name
212 ,p_msg_text => 'Cannot find value set id in a given colum name');
213
214 pv_mapped_segs(i).vs_id := -99;
215 END;
216 END LOOP;
217
218 FOR i in 1..pv_segment_count
219 LOOP
220 IF pv_mapped_segs(i).dependent_value_set_flag = 'Y'
221 THEN
222 IF i = 2
223 THEN
224 pv_mapped_segs(i).dependent_segment_column := 'segment1_value';
225 ELSE
226 FOR k in 1..i
227 LOOP
228 IF pv_mapped_segs(k).vs_id = pv_mapped_segs(i).dependent_vs_id
229 THEN
230 pv_mapped_segs(i).dependent_segment_column := 'segment'||k||'_value';
231 END IF;
232 END LOOP;
233 END IF;
234 END IF;
235 END LOOP;
236
237 -- ----------------------------------------------------------
238 -- Get Org dimension ID
239 -- ----------------------------------------------------------
240 BEGIN
241 SELECT DIMENSION_ID
242 INTO pv_cctr_org_dim_id
243 FROM FEM_DIMENSIONS_B
244 WHERE DIMENSION_VARCHAR_LABEL = 'COMPANY_COST_CENTER_ORG';
245 EXCEPTION
246 WHEN NO_DATA_FOUND THEN
247 pv_cctr_org_dim_id := -1;
248 WHEN OTHERS THEN
249 RAISE;
250 END;
251
252 -- ----------------------------------------------------------
253 -- Get financial element dimension ID
254 -- ----------------------------------------------------------
255
256 BEGIN
257 SELECT DIMENSION_ID
258 INTO pv_fin_element_dim_id
259 FROM FEM_DIMENSIONS_B
260 WHERE DIMENSION_VARCHAR_LABEL = 'FINANCIAL_ELEMENT';
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 pv_fin_element_dim_id := -1;
264 WHEN OTHERS THEN
265 RAISE;
266 END;
267
268
269 -- ----------------------------------------------------------
270 -- Get financial element value set ID
271 -- ----------------------------------------------------------
272
273 BEGIN
274 SELECT value_set_id
275 INTO pv_fin_element_vs_id
276 FROM FEM_FIN_ELEMS_B
277 WHERE ROWNUM = 1;
278 EXCEPTION
279 WHEN NO_DATA_FOUND THEN
280 pv_fin_element_vs_id := -1;
281 WHEN OTHERS THEN
282 RAISE;
283 END;
284
285 pv_progress := 'Get extended acct type attribute id';
286 SELECT a.attribute_id
287 ,v.version_id
288 INTO pv_ext_acct_type_attr_id
289 ,pv_ext_acct_attr_version_id
290 FROM fem_dim_attributes_b a,
291 fem_dim_attr_versions_b v
292 WHERE a.dimension_id = 2
293 AND a.attribute_varchar_label='EXTENDED_ACCOUNT_TYPE'
294 AND v.attribute_id = a.attribute_id
295 AND v.default_version_flag = 'Y';
296
297 pv_progress := 'Get source system code';
298
299 SELECT SOURCE_SYSTEM_CODE
300 INTO pv_source_system_code_id
301 FROM FEM_SOURCE_SYSTEMS_B
302 WHERE SOURCE_SYSTEM_DISPLAY_CODE = 'OGL';
303
304 IF pv_dim_varchar_label IN ('COMPANY_COST_CENTER_ORG', 'INTERCOMPANY')
305 THEN
306 BEGIN
307 pv_progress := 'Get pv_com_dim_id';
308 SELECT DIMENSION_ID
309 INTO pv_com_dim_id
310 FROM FEM_DIMENSIONS_B
311 WHERE DIMENSION_VARCHAR_LABEL = 'COMPANY';
312
313 pv_progress := 'Get pv_cc_dim_id';
314 SELECT DIMENSION_ID
315 INTO pv_cc_dim_id
316 FROM FEM_DIMENSIONS_B
317 WHERE DIMENSION_VARCHAR_LABEL = 'COST_CENTER';
318 EXCEPTION
319 WHEN NO_DATA_FOUND THEN
320 IF pv_dim_mapping_option_code = 'MULTISEG'
321 THEN
322 IF pv_progress = 'Get pv_com_dim_id'
323 THEN
324 RAISE FEMOGL_com_dim_missing;
325 ELSE
326 RAISE FEMOGL_cc_dim_missing;
327 END IF;
328 ELSE
329 NULL; -- raise no error
330 END IF;
331 END;
332
333 OPEN FlexQualifiedSegmentNUM('GL_BALANCING', pv_cctr_org_dim_id);
334 FETCH FlexQualifiedSegmentNUM INTO pv_balancing_segment_num;
335 CLOSE FlexQualifiedSegmentNUM;
336
337 OPEN FlexQualifiedSegmentNUM('FA_COST_CTR', pv_cctr_org_dim_id);
338 FETCH FlexQualifiedSegmentNUM INTO pv_cost_center_segment_num;
339 CLOSE FlexQualifiedSegmentNUM;
340
341 ELSIF pv_dim_varchar_label = 'NATURAL_ACCOUNT' THEN
342
343 pv_natural_account_segment_num := 1;
344
345 ELSIF pv_dim_varchar_label = 'LINE_ITEM' THEN
346
347 IF pv_dim_mapping_option_code = 'SINGLESEG' THEN
348 pv_natural_account_segment_num := 1;
349 ELSE
350 OPEN FlexQualifiedSegmentNUM('GL_ACCOUNT', pv_dim_id);
351 FETCH FlexQualifiedSegmentNUM INTO pv_natural_account_segment_num;
352 CLOSE FlexQualifiedSegmentNUM;
353 END IF;
354
355 END IF;
356
357 FEM_ENGINES_PKG.Tech_Message
358 ( p_severity => pc_log_level_procedure
359 ,p_module => pc_module_name||c_func_name
360 ,p_msg_text => 'Initialize major package variables 3 of 3 ');
361
362 pv_progress := 'Get table info for dimension';
363 IF pv_dim_id <> 0 THEN
364
365 SELECT MEMBER_B_TABLE_NAME
366 ,MEMBER_TL_TABLE_NAME
367 ,MEMBER_COL
368 ,MEMBER_NAME_COL
369 ,MEMBER_VL_OBJECT_NAME
370 ,MEMBER_DESCRIPTION_COL
371 ,MEMBER_DISPLAY_CODE_COL
372 ,ATTRIBUTE_TABLE_NAME
373 INTO pv_member_b_table_name
374 ,pv_member_tl_table_name
375 ,pv_member_col
376 ,pv_member_name_col
377 ,pv_member_vl_object_name
378 ,pv_member_desc_col
379 ,pv_member_display_code_col
380 ,pv_attr_table_name
381 FROM FEM_XDIM_DIMENSIONS
382 WHERE DIMENSION_ID = pv_dim_id;
383
384 ELSE
385
386 FEM_ENGINES_PKG.Tech_Message
387 ( p_severity => pc_log_level_procedure
388 ,p_module => pc_module_name||c_func_name
389 ,p_msg_text => '- Intercompany case - ');
390
391
392 IF pv_cctr_org_dim_id = -1
393 THEN
394 RAISE FEMOGL_cctr_org_dim_missing;
395 END IF;
396
397 SELECT MEMBER_B_TABLE_NAME,
398 MEMBER_TL_TABLE_NAME,
399 MEMBER_VL_OBJECT_NAME,
400 MEMBER_COL,
401 MEMBER_DISPLAY_CODE_COL,
402 MEMBER_NAME_COL,
403 ATTRIBUTE_TABLE_NAME
404 INTO pv_member_b_table_name,
405 pv_member_tl_table_name,
406 pv_member_vl_object_name,
407 pv_cctr_org_member_col,
408 pv_member_display_code_col,
409 pv_member_name_col,
410 pv_attr_table_name
411 FROM FEM_XDIM_DIMENSIONS
412 WHERE DIMENSION_ID = pv_cctr_org_dim_id;
413
414 pv_member_col := 'INTERCOMPANY_ID';
415
416 END IF;
417
418 FEM_ENGINES_PKG.Tech_Message
419 (p_severity => pc_log_level_procedure,
420 p_module => pc_module_name || c_func_name,
421 p_app_name => 'FEM',
422 p_msg_name => 'FEM_GL_POST_202',
423 p_token1 => 'FUNC_NAME',
424 p_value1 => pc_module_name||c_func_name,
425 p_token2 => 'TIME',
426 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
427 EXCEPTION
428 WHEN FEMOGL_cctr_org_dim_missing THEN
429 FEM_ENGINES_PKG.Tech_Message
430 (p_severity => pc_log_level_error
431 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
432 ,p_msg_text => 'Cannot find CCTR-ORG dimension ID');
433
434 FEM_ENGINES_PKG.Tech_Message
435 (p_severity => pc_log_level_error
436 ,p_module => pc_module_name||c_func_name
437 ,p_app_name => 'FEM'
438 ,p_msg_name => 'FEM_INTG_DIM_ENG_101'
439 ,p_token1 => 'DIM_ID'
440 ,p_value1 => pv_dim_id);
441
442 FEM_ENGINES_PKG.Tech_Message
443 (p_severity => pc_log_level_error
444 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
445 ,p_app_name => 'FEM'
446 ,p_msg_name => 'FEM_GL_POST_203'
447 ,p_token1 => 'FUNC_NAME'
448 ,p_value1 => pc_module_name||c_func_name
449 ,p_token2 => 'TIME'
450 ,p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
451
452 fnd_message.set_name('FEM','FEM_INTG_DIM_ENG_101');
453 fnd_message.set_token('DIM_ID',pv_dim_id);
454
455 app_exception.raise_exception;
456
457 WHEN FEMOGL_com_dim_missing THEN
458 FEM_ENGINES_PKG.Tech_Message
459 (p_severity => pc_log_level_error
460 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
461 ,p_msg_text => 'Cannot find company dimension ID');
462
463 FEM_ENGINES_PKG.Tech_Message
464 (p_severity => pc_log_level_error
465 ,p_module => pc_module_name||c_func_name
466 ,p_app_name => 'FEM'
467 ,p_msg_name => 'FEM_INTG_DIM_ENG_102'
468 ,p_token1 => 'DIM_ID'
469 ,p_value1 => pv_dim_id);
470
471 FEM_ENGINES_PKG.Tech_Message
472 (p_severity => pc_log_level_error
473 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
474 ,p_app_name => 'FEM'
475 ,p_msg_name => 'FEM_GL_POST_203'
476 ,p_token1 => 'FUNC_NAME'
477 ,p_value1 => c_func_name
478 ,p_token2 => 'TIME'
479 ,p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
480
481 fnd_message.set_name('FEM','FEM_INTG_DIM_ENG_102');
482 fnd_message.set_token('DIM_ID',pv_dim_id);
483
487 FEM_ENGINES_PKG.Tech_Message
484 app_exception.raise_exception;
485
486 WHEN FEMOGL_cc_dim_missing THEN
488 (p_severity => pc_log_level_error
489 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
490 ,p_msg_text => 'Cannot find cost center dimension ID');
491
492 FEM_ENGINES_PKG.Tech_Message
493 (p_severity => pc_log_level_error
494 ,p_module => pc_module_name||c_func_name
495 ,p_app_name => 'FEM'
496 ,p_msg_name => 'FEM_INTG_DIM_ENG_103'
497 ,p_token1 => 'DIM_ID'
498 ,p_value1 => pv_dim_id);
499
500 FEM_ENGINES_PKG.Tech_Message
501 (p_severity => pc_log_level_error
502 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
503 ,p_app_name => 'FEM'
504 ,p_msg_name => 'FEM_GL_POST_203'
505 ,p_token1 => 'FUNC_NAME'
506 ,p_value1 => c_func_name
507 ,p_token2 => 'TIME'
508 ,p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
509
510 fnd_message.set_name('FEM','FEM_INTG_DIM_ENG_103');
511 fnd_message.set_token('DIM_ID',pv_dim_id);
512
513 app_exception.raise_exception;
514
515 WHEN OTHERS THEN
516 FEM_ENGINES_PKG.Tech_Message
517 (p_severity => pc_log_level_unexpected
518 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
519 ,p_msg_text => sqlerrm);
520
521 FEM_ENGINES_PKG.Tech_Message
522 (p_severity => pc_log_level_unexpected
523 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
524 ,p_msg_text => 'Location before failure: '||pv_progress);
525
526 FEM_ENGINES_PKG.User_Message
527 (p_msg_text => sqlerrm);
528
529 FEM_ENGINES_PKG.Tech_Message
530 (p_severity => pc_log_level_procedure
531 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
532 ,p_app_name => 'FEM'
533 ,p_msg_name => 'FEM_GL_POST_203'
534 ,p_token1 => 'FUNC_NAME'
535 ,p_value1 => pc_module_name||c_func_name
536 ,p_token2 => 'TIME'
537 ,p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
538
539 raise_application_error(-20001, fnd_message.get);
540 END;
541
542
543 PROCEDURE Main (x_errbuf OUT NOCOPY VARCHAR2,
544 x_retcode OUT NOCOPY VARCHAR2,
545 p_dim_rule_obj_def_id IN NUMBER,
546 p_execution_mode IN VARCHAR2)
547 IS
548 v_max_ccid_in_map_table NUMBER;
549 v_max_ccid_in_glccid_table NUMBER;
550 v_completion_code NUMBER;
551 v_dim_process_row_cnt NUMBER;
552 v_ccc_org_dim_id NUMBER;
553 FEMOGL_fatal_err EXCEPTION;
554 FEMOGL_gvsc_not_set EXCEPTION;
555 FEMOGL_warn EXCEPTION;
556 FEMOGL_no_data_to_load EXCEPTION;
557 FEMOGL_all_data_invalid EXCEPTION;
558 v_compl_code NUMBER;
559 v_cp_status VARCHAR2(30);
560 c_func_name CONSTANT VARCHAR2(30) := '.Main';
561 v_global_defs_vs_id NUMBER;
562 v_completion_code_register NUMBER;
563 v_completion_code_final NUMBER;
564 v_map_records_inserted_count NUMBER;
565 v_status NUMBER;
566 v_rowcount NUMBER;
567 v_temp_vs_id NUMBER;
568 v_cctr_map_option_code VARCHAR2(30);
569 v_cctr_seg_count NUMBER;
570 v_com_fnd_vs_id NUMBER;
571 v_cc_fnd_vs_id NUMBER;
572
573 v_interco_req_id NUMBER;
574 v_interco_rule_def_id NUMBER;
575
576 -- Bug#6238739: Added as output parameters for the call.
577 l_return_status VARCHAR2(1);
578 l_msg_count NUMBER;
579 l_msg_data VARCHAR2(2000);
580
581 TYPE INDEX_NAME is TABLE OF VARCHAR2(30);
582 pv_index_name INDEX_NAME;
583 BEGIN
584 pv_progress := 'Main Start';
585 FEM_ENGINES_PKG.Tech_Message
586 (p_severity => pc_log_level_procedure,
587 p_module => pc_module_name||c_func_name,
588 p_app_name => 'FEM',
589 p_msg_name => 'FEM_GL_POST_201',
590 p_token1 => 'FUNC_NAME',
591 p_value1 => pc_module_name||c_func_name,
592 p_token2 => 'TIME',
593 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
594
595 pv_dim_rule_obj_def_id := p_dim_rule_obj_def_id;
596 pv_user_id := NVL(FND_GLOBAL.USER_ID,'-1');
597 pv_login_id := NVL(FND_GLOBAL.CONC_LOGIN_ID,
598 FND_GLOBAL.LOGIN_ID);
599 pv_req_id := NVL(FND_GLOBAL.CONC_REQUEST_ID,1);
600 pv_pgm_id := NVL(FND_GLOBAL.CONC_PROGRAM_ID,1);
601 pv_pgm_app_id := NVL(FND_GLOBAL.PROG_APPL_ID,274);
602
603 pv_progress := 'Before select of object id and folder id';
604
605 SELECT O.OBJECT_ID, O.FOLDER_ID
606 INTO pv_dim_rule_obj_id, pv_folder_id
607 FROM FEM_OBJECT_DEFINITION_B B,
608 FEM_OBJECT_CATALOG_B O
609 WHERE B.OBJECT_DEFINITION_ID = pv_dim_rule_obj_def_id
613
610 AND O.OBJECT_ID = B.OBJECT_ID
611 AND O.OBJECT_TYPE_CODE = 'OGL_INTG_DIM_RULE';
612
614 -- ----------------------------------------------------------
615 -- Check if User has authorization to run dimension rules
616 -- ----------------------------------------------------------
617 -- REMOVED CODE
618
619 -- -----------------------------------
620 -- *** Register Process Execution ***
621 -- -----------------------------------
622
623 pv_progress := 'Calling FEM_GL_POST_PROCESS_PKG.Register_Process_Execution';
624
625 FEM_ENGINES_PKG.Tech_Message
626 ( p_severity => pc_log_level_event
627 ,p_module => pc_module_name||c_func_name
628 ,p_msg_text => 'Before calling Process lock');
629
630
631 FEM_INTG_PL_PKG.Register_Process_Execution(
632 p_obj_id => pv_dim_rule_obj_id,
633 p_obj_def_id => pv_dim_rule_obj_def_id,
634 p_req_id => pv_req_id,
635 p_user_id => pv_user_id,
636 p_login_id => pv_login_id,
637 p_pgm_id => pv_pgm_id,
638 p_pgm_app_id => pv_pgm_app_id,
639 p_module_name => pc_module_name,
640 x_completion_code => v_completion_code_register
641 );
642
643 IF v_completion_code_register = 2 THEN
644 FEM_ENGINES_PKG.Tech_Message
645 ( p_severity => pc_log_level_error
646 ,p_module => pc_module_name||c_func_name
647 ,p_msg_text => 'Before calling Process lock');
648 RAISE FEMOGL_fatal_err;
649 END IF;
650
651 -- ------------------------------------
652 -- *** Initialize Package Variables ***
653 -- ------------------------------------
654
655 pv_progress := 'Calling to initialize';
656
657 FEM_ENGINES_PKG.Tech_Message
658 ( p_severity => pc_log_level_error
659 ,p_module => pc_module_name||c_func_name
660 ,p_msg_text => 'Before calling Init');
661
662 FEM_ENGINES_PKG.User_Message(
663 p_app_name => 'FEM',
664 p_msg_name => 'FEM_INTG_DIM_ENG_501'
665 );
666
667 Init;
668
669 pv_progress := 'Before select of GVSC ID';
670 BEGIN
671 SELECT COA.GLOBAL_VS_COMBO_ID
672 INTO pv_gvsc_id
673 FROM FEM_OBJECT_DEFINITION_B DEF,
674 FEM_INTG_COA_GVSC_MAP COA
675 WHERE DEF.OBJECT_DEFINITION_ID = p_dim_rule_obj_def_id
676 AND COA.CHART_OF_ACCOUNTS_ID = pv_coa_id;
677 EXCEPTION
678 WHEN NO_DATA_FOUND THEN
679 RAISE FEMOGL_gvsc_not_set;
680 END;
681
682 IF pv_fem_vs_id = -1
683 AND pv_dim_mapping_option_code <> 'SINGLEVAL'
684 THEN
685 pv_progress := 'FEM_ID is -1, entered IF';
686 IF NVL(pv_dim_varchar_label,'X') <> 'INTERCOMPANY'
687 THEN
688 BEGIN
689 pv_progress := 'select fem_vs_id from AOL valuse set map';
690 SELECT fem_value_set_id
691 INTO pv_fem_vs_id
692 FROM FEM_INTG_AOL_VALSET_MAP
693 WHERE DIMENSION_ID = pv_dim_id
694 AND NVL(SEGMENT1_VALUE_SET_ID,-99) = pv_mapped_segs(1).vs_id
695 AND NVL(SEGMENT2_VALUE_SET_ID,-99) = pv_mapped_segs(2).vs_id
696 AND NVL(SEGMENT3_VALUE_SET_ID,-99) = pv_mapped_segs(3).vs_id
697 AND NVL(SEGMENT4_VALUE_SET_ID,-99) = pv_mapped_segs(4).vs_id
698 AND NVL(SEGMENT5_VALUE_SET_ID,-99) = pv_mapped_segs(5).vs_id;
699 EXCEPTION
700 WHEN NO_DATA_FOUND THEN
701 --
702 -- Create FEM Value Set
703 --
704
705 SELECT FEM_VALUE_SETS_B_S.nextval
706 INTO pv_fem_vs_id
707 FROM DUAL;
708
709 pv_progress := 'insert fem vs into aol mapping table';
710
711 INSERT INTO FEM_INTG_AOL_VALSET_MAP(
712 FEM_VALUE_SET_ID,
713 DIMENSION_ID,
714 SEGMENT1_VALUE_SET_ID,
715 SEGMENT2_VALUE_SET_ID,
716 SEGMENT3_VALUE_SET_ID,
717 SEGMENT4_VALUE_SET_ID,
718 SEGMENT5_VALUE_SET_ID,
719 CREATION_DATE,
720 CREATED_BY,
721 LAST_UPDATE_DATE,
722 LAST_UPDATED_BY,
723 LAST_UPDATE_LOGIN
724 )
725 VALUES
726 (
727 pv_fem_vs_id,
728 pv_dim_id,
729 pv_mapped_segs(1).vs_id,
730 pv_mapped_segs(2).vs_id,
731 pv_mapped_segs(3).vs_id,
732 pv_mapped_segs(4).vs_id,
733 pv_mapped_segs(5).vs_id,
734 SYSDATE,
735 pv_user_id,
736 SYSDATE,
737 pv_user_id,
738 pv_login_id
739 );
740
741 pv_progress := 'register fem value set';
742 --------------------------------------------------
743 -- Register FEM value set
744 ---------------------------------------------------
745 register_fem_value_set (p_fem_value_set_id => pv_fem_vs_id
746 ,p_regiser_type => 'STANDARD'
747 ,p_fnd_vs_id => NULL
748 ,p_dim_id => pv_dim_id
749 ,x_status => v_status);
750 IF v_status = 0
754 ,p_module => pc_module_name||c_func_name
751 THEN
752 FEM_ENGINES_PKG.Tech_Message
753 ( p_severity => pc_log_level_error
755 ,p_msg_text => 'Unexpected error while registering FEM value set');
756 raise_application_error(-20001, fnd_message.get);
757 END IF;
758
759 WHEN OTHERS THEN
760 FEM_ENGINES_PKG.Tech_Message
761 ( p_severity => pc_log_level_error
762 ,p_module => pc_module_name||c_func_name
763 ,p_msg_text => 'Following error during selecting fem VSID
764 from FEM_INTG_AOL_VALSET_MAP: '||sqlerrm);
765 raise_application_error(-20001, fnd_message.get);
766 END;
767
768 IF(pv_dim_id = 8) THEN
769 UPDATE fem_intg_dim_rule_defs def
770 SET default_member_value_set_id = pv_fem_vs_id,
771 default_member_id =
772 (SELECT org.company_cost_center_org_id
773 FROM fem_cctr_orgs_b org
774 WHERE org.cctr_org_display_code = 'Default'
775 AND org.value_set_id = pv_fem_vs_id)
776 WHERE def.dim_rule_obj_def_id =
777 (SELECT odb.object_definition_id
778 FROM fem_object_definition_b odb,
779 fem_intg_dim_rules rule
780 WHERE odb.object_id = rule.dim_rule_obj_id
781 AND rule.chart_of_accounts_id = pv_coa_id
782 AND rule.dimension_id = 0)
783 AND def.dim_mapping_option_code = 'SINGLEVAL';
784 END IF;
785
786 ELSE /* Intercompany case */
787
788 pv_progress := 'Check if Org defintion and Intercompany definition are of same type';
789
790 SELECT ruledef.DIM_MAPPING_OPTION_CODE
791 , ruledef.SEGMENT_COUNT
792 INTO v_cctr_map_option_code
793 , v_cctr_seg_count
794 from fem_intg_dim_rules rules
795 , fem_intg_dim_rule_defs ruledef
796 , fem_object_definition_b objdef
797 where rules.chart_of_accounts_id = pv_coa_id
798 and rules.dim_rule_obj_id = objdef.object_id
799 and objdef.object_definition_id = ruledef.dim_rule_obj_def_id
800 and rules.dimension_id = pv_cctr_org_dim_id;
801
802 IF (pv_segment_count <> v_cctr_seg_count)
803 OR (pv_dim_mapping_option_code <> v_cctr_map_option_code)
804 THEN
805 FEM_ENGINES_PKG.User_Message(
806 p_app_name => 'FEM',
807 p_msg_name => 'FEM_INTG_DIM_ENG_508'
808 );
809 fnd_message.set_name('FEM','FEM_INTG_DIM_ENG_508');
810 app_exception.raise_exception;
811 END IF;
812
813
814 pv_progress := 'select fem_valueset_id for intercompany based on CCTR org dimension';
815 BEGIN
816 SELECT value_set_id
817 INTO pv_fem_vs_id
818 FROM fem_global_vs_combo_defs
819 WHERE global_vs_combo_id = pv_gvsc_id
820 AND dimension_id = pv_cctr_org_dim_id;
821
822 SELECT value_set_id
823 INTO pv_com_vs_id
824 FROM fem_global_vs_combo_defs
825 WHERE global_vs_combo_id = pv_gvsc_id
826 AND dimension_id = pv_com_dim_id;
827
828 SELECT value_set_id
829 INTO pv_cc_vs_id
830 FROM fem_global_vs_combo_defs
831 WHERE global_vs_combo_id = pv_gvsc_id
832 AND dimension_id = pv_cc_dim_id;
833
834 EXCEPTION
835 WHEN OTHERS THEN
836 FEM_ENGINES_PKG.Tech_Message
837 ( p_severity => pc_log_level_procedure
838 ,p_module => pc_module_name||c_func_name
839 ,p_msg_text => 'varlable'||pv_dim_varchar_label);
840 raise_application_error(-20001, fnd_message.get);
841 END;
842
843 IF pv_fem_vs_id = -1
844 THEN
845 FEM_ENGINES_PKG.User_Message(
846 p_app_name => 'FEM',
847 p_msg_name => 'FEM_INTG_DIM_ENG_507'
848 );
849 fnd_message.set_name('FEM','FEM_INTG_DIM_ENG_507');
850 app_exception.raise_exception;
851
852 ELSE
853 --
854 -- Register value set id in FEM_GLOBAL_VS_COMBO_DEFS for
855 -- intercompany dimension
856 --
857 pv_progress := 'Update FEM_GLOBAL_VS_COMBO_DEFS for intercompany dimension';
858
859 UPDATE FEM_GLOBAL_VS_COMBO_DEFS
860 SET VALUE_SET_ID = pv_fem_vs_id
861 ,LAST_UPDATED_BY = pv_user_id
862 ,LAST_UPDATE_DATE = SYSDATE
863 ,LAST_UPDATE_LOGIN = pv_login_id
864 WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
865 AND DIMENSION_ID = pv_dim_id;
866 END IF;
867
868 END IF; /* INTERCOMPANY */
869
870 END IF; /* pv_fem_vs_id IS NULL */
871
872 IF NVL(pv_dim_varchar_label,'X') <> 'INTERCOMPANY'
873 THEN
874 BEGIN
875 SELECT VALUE_SET_ID
876 INTO v_global_defs_vs_id
877 FROM FEM_GLOBAL_VS_COMBO_DEFS
881 WHEN NO_DATA_FOUND THEN
878 WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
879 AND DIMENSION_ID = pv_dim_id;
880 EXCEPTION
882 FEM_ENGINES_PKG.Tech_Message
883 ( p_severity => pc_log_level_error
884 ,p_module => pc_module_name||c_func_name
885 ,p_msg_text => 'Unexpected error while getting value set from FEM_GLOBAL_VS_COMBO_DEFS');
886 raise_application_error(-20001, fnd_message.get);
887 END;
888 IF v_global_defs_vs_id = -1
889 THEN
890 pv_progress := 'Update FEM_GLOBAL_VS_COMBO_DEFS';
891 UPDATE FEM_GLOBAL_VS_COMBO_DEFS
892 SET VALUE_SET_ID = pv_fem_vs_id
893 ,LAST_UPDATED_BY = pv_user_id
894 ,LAST_UPDATE_DATE = SYSDATE
895 ,LAST_UPDATE_LOGIN = pv_login_id
896 WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
897 AND DIMENSION_ID = pv_dim_id;
898
899 IF pv_dim_varchar_label = 'NATURAL_ACCOUNT'
900 THEN
901 UPDATE FEM_GLOBAL_VS_COMBO_DEFS
902 SET VALUE_SET_ID = pv_fin_element_vs_id
903 ,LAST_UPDATED_BY = pv_user_id
904 ,LAST_UPDATE_DATE = SYSDATE
905 ,LAST_UPDATE_LOGIN = pv_login_id
906 WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
907 AND DIMENSION_ID = pv_fin_element_dim_id;
908 END IF;
909 END IF;
910 END IF;
911
912
913 IF pv_dim_varchar_label = 'INTERCOMPANY'
914 THEN
915 SELECT value_set_id
916 INTO pv_com_vs_id
917 FROM fem_global_vs_combo_defs
918 WHERE global_vs_combo_id = pv_gvsc_id
919 AND dimension_id = pv_com_dim_id;
920
921 SELECT value_set_id
922 INTO pv_cc_vs_id
923 FROM fem_global_vs_combo_defs
924 WHERE global_vs_combo_id = pv_gvsc_id
925 AND dimension_id = pv_cc_dim_id;
926 END IF;
927
928
929 IF pv_dim_varchar_label = 'COMPANY_COST_CENTER_ORG'
930 THEN
931
932 --
933 -- Splitting the logic for Single segment and multi segment case for stability
934 -- because code changes were done at last minute. Need to revisit
935 -- this section and combine the logic together for next release.
936 --
937 --
938 IF pv_dim_mapping_option_code = 'MULTISEG'
939 THEN
940 pv_progress := 'Handling special ORG processing logic for CO/CC for Multi Segment';
941 -- Create Company and Cost Center FEM Value Sets if not exists
942 --
943 pv_progress := 'select fem_vs_id from AOL valuse set map for com vsid';
944 BEGIN
945 SELECT fem_value_set_id
946 INTO pv_com_vs_id
947 FROM FEM_INTG_AOL_VALSET_MAP
948 WHERE DIMENSION_ID = pv_com_dim_id
949 AND NVL(SEGMENT1_VALUE_SET_ID,-99) = pv_mapped_segs(1).vs_id
950 AND NVL(SEGMENT2_VALUE_SET_ID,-99) = -99
951 AND NVL(SEGMENT3_VALUE_SET_ID,-99) = -99
952 AND NVL(SEGMENT4_VALUE_SET_ID,-99) = -99
953 AND NVL(SEGMENT5_VALUE_SET_ID,-99) = -99;
954 EXCEPTION
955 WHEN NO_DATA_FOUND THEN
956 pv_com_vs_id := -1;
957 WHEN OTHERS THEN
958 pv_progress := 'unexpected error whiel selecting pv_com_vs_id from FEM_INTG_AOL_VALSET_MAP: ' ||sqlerrm;
959 raise_application_error(-20001, fnd_message.get);
960 END;
961
962 IF pv_com_vs_id = -1
963 THEN
964 --
965 -- Create FEM Value Set
966 --
967 pv_progress := 'insert newly created company vset into aol mapping table';
968
969 SELECT FEM_VALUE_SETS_B_S.nextval
970 INTO pv_com_vs_id
971 FROM DUAL;
972
973 INSERT INTO FEM_INTG_AOL_VALSET_MAP
974 (
975 FEM_VALUE_SET_ID,
976 DIMENSION_ID,
977 SEGMENT1_VALUE_SET_ID,
978 SEGMENT2_VALUE_SET_ID,
979 SEGMENT3_VALUE_SET_ID,
980 SEGMENT4_VALUE_SET_ID,
981 SEGMENT5_VALUE_SET_ID,
982 CREATION_DATE,
983 CREATED_BY,
984 LAST_UPDATE_DATE,
985 LAST_UPDATED_BY,
986 LAST_UPDATE_LOGIN
987 )
988 VALUES
989 (
990 pv_com_vs_id,
991 pv_com_dim_id,
992 pv_mapped_segs(1).vs_id,
993 -99,
994 -99,
995 -99,
996 -99,
997 SYSDATE,
998 pv_user_id,
999 SYSDATE,
1000 pv_user_id,
1001 pv_login_id
1002 );
1003
1004 --------------------------------------------------
1005 -- Register FEM value set
1006 ---------------------------------------------------
1007 register_fem_value_set (p_fem_value_set_id => pv_com_vs_id
1008 ,p_regiser_type => 'COMPANY'
1009 ,p_fnd_vs_id => pv_mapped_segs(1).vs_id
1010 ,p_dim_id => pv_com_dim_id
1014 FEM_ENGINES_PKG.Tech_Message
1011 ,x_status => v_status);
1012 IF v_status = 0
1013 THEN
1015 ( p_severity => pc_log_level_error
1016 ,p_module => pc_module_name||c_func_name
1017 ,p_msg_text => 'Unexpected error while registering company value set');
1018 raise_application_error(-20001, fnd_message.get);
1019 END IF;
1020
1021 END IF;
1022 --
1023 -- Bug fix 4190298
1024 -- Placed the update out of If statement as this was never done for SINGLESEG case
1025 --
1026 pv_progress := 'Update FEM_GLOBAL_VS_COMBO_DEFS for company dim';
1027
1028 UPDATE FEM_GLOBAL_VS_COMBO_DEFS
1029 SET VALUE_SET_ID = pv_com_vs_id
1030 ,LAST_UPDATED_BY = pv_user_id
1031 ,LAST_UPDATE_DATE = SYSDATE
1032 ,LAST_UPDATE_LOGIN = pv_login_id
1033 WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
1034 AND DIMENSION_ID = pv_com_dim_id;
1035
1036 --
1037 -- Create Company Cost Center FEM Value Sets if not exists
1038 --
1039 pv_progress := 'select fem_vs_id from AOL valuse set map for cc vsid';
1040
1041 IF pv_dim_mapping_option_code = 'SINGLESEG'
1042 THEN
1043 v_temp_vs_id := pv_mapped_segs(1).vs_id;
1044 ELSE
1045 v_temp_vs_id := pv_mapped_segs(2).vs_id;
1046 END IF;
1047 BEGIN
1048 SELECT fem_value_set_id
1049 INTO pv_cc_vs_id
1050 FROM FEM_INTG_AOL_VALSET_MAP
1051 WHERE DIMENSION_ID = pv_cc_dim_id
1052 AND NVL(SEGMENT1_VALUE_SET_ID,-99) = v_temp_vs_id
1053 AND NVL(SEGMENT2_VALUE_SET_ID,-99) = -99
1054 AND NVL(SEGMENT3_VALUE_SET_ID,-99) = -99
1055 AND NVL(SEGMENT4_VALUE_SET_ID,-99) = -99
1056 AND NVL(SEGMENT5_VALUE_SET_ID,-99) = -99;
1057 EXCEPTION
1058 WHEN NO_DATA_FOUND THEN
1059 pv_cc_vs_id := -1;
1060 WHEN OTHERS THEN
1061 pv_progress := 'unexpected error while selecting pv_cc_vs_id
1062 from FEM_INTG_AOL_VALSET_MAP: ' ||sqlerrm;
1063 raise_application_error(-20001, fnd_message.get);
1064 END;
1065
1066 IF pv_cc_vs_id = -1
1067 THEN
1068
1069
1070 SELECT FEM_VALUE_SETS_B_S.nextval
1071 INTO pv_cc_vs_id
1072 FROM DUAL;
1073
1074 pv_progress := 'insert newly created cc vset into aol mapping table';
1075
1076 INSERT INTO FEM_INTG_AOL_VALSET_MAP
1077 (
1078 FEM_VALUE_SET_ID,
1079 DIMENSION_ID,
1080 SEGMENT1_VALUE_SET_ID,
1081 SEGMENT2_VALUE_SET_ID,
1082 SEGMENT3_VALUE_SET_ID,
1083 SEGMENT4_VALUE_SET_ID,
1084 SEGMENT5_VALUE_SET_ID,
1085 CREATION_DATE,
1086 CREATED_BY,
1087 LAST_UPDATE_DATE,
1088 LAST_UPDATED_BY,
1089 LAST_UPDATE_LOGIN
1090 )
1091 VALUES
1092 (
1093 pv_cc_vs_id,
1094 pv_cc_dim_id,
1095 v_temp_vs_id,
1096 -99,
1097 -99,
1098 -99,
1099 -99,
1100 SYSDATE,
1101 pv_user_id,
1102 SYSDATE,
1103 pv_user_id,
1104 pv_login_id
1105 );
1106
1107 --------------------------------------------------
1108 -- Register FEM value set
1109 ---------------------------------------------------
1110 register_fem_value_set (p_fem_value_set_id => pv_com_vs_id
1111 ,p_regiser_type => 'COST_CENTER'
1112 ,p_fnd_vs_id => v_temp_vs_id
1113 ,p_dim_id => pv_cc_dim_id
1114 ,x_status => v_status);
1115
1116 IF v_status = 0
1117 THEN
1118 FEM_ENGINES_PKG.Tech_Message
1119 ( p_severity => pc_log_level_error
1120 ,p_module => pc_module_name||c_func_name
1121 ,p_msg_text => 'Unexpected error while registering Cost-center value set');
1122 raise_application_error(-20001, fnd_message.get);
1123 END IF;
1124
1125 END IF;
1126
1127 pv_progress := 'Insert into FEM_GLOBAL_VS_COMBO_DEFS for cc dim';
1128 UPDATE FEM_GLOBAL_VS_COMBO_DEFS
1129 SET VALUE_SET_ID = pv_cc_vs_id
1130 ,LAST_UPDATED_BY = pv_user_id
1131 ,LAST_UPDATE_DATE = SYSDATE
1132 ,LAST_UPDATE_LOGIN = pv_login_id
1133 WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
1134 AND DIMENSION_ID = pv_cc_dim_id;
1135 ELSE
1136 pv_progress := 'Handling special ORG processing logic for CO/CC for Single Segment';
1137
1138 IF pv_balancing_segment_num = 1
1139 THEN
1140 v_com_fnd_vs_id := pv_mapped_segs(1).vs_id;
1141 v_cc_fnd_vs_id := -1;
1142 ELSE
1143 v_com_fnd_vs_id := -1;
1147 BEGIN
1144 v_cc_fnd_vs_id := pv_mapped_segs(1).vs_id;
1145 END IF;
1146
1148 SELECT fem_value_set_id
1149 INTO pv_com_vs_id
1150 FROM FEM_INTG_AOL_VALSET_MAP
1151 WHERE DIMENSION_ID = pv_com_dim_id
1152 AND NVL(SEGMENT1_VALUE_SET_ID,-99) = v_com_fnd_vs_id
1153 AND NVL(SEGMENT2_VALUE_SET_ID,-99) = -99
1154 AND NVL(SEGMENT3_VALUE_SET_ID,-99) = -99
1155 AND NVL(SEGMENT4_VALUE_SET_ID,-99) = -99
1156 AND NVL(SEGMENT5_VALUE_SET_ID,-99) = -99;
1157 EXCEPTION
1158 WHEN NO_DATA_FOUND THEN
1159 pv_com_vs_id := -1;
1160 END;
1161
1162 BEGIN
1163 SELECT fem_value_set_id
1164 INTO pv_cc_vs_id
1165 FROM FEM_INTG_AOL_VALSET_MAP
1166 WHERE DIMENSION_ID = pv_cc_dim_id
1167 AND NVL(SEGMENT1_VALUE_SET_ID,-99) = v_cc_fnd_vs_id
1168 AND NVL(SEGMENT2_VALUE_SET_ID,-99) = -99
1169 AND NVL(SEGMENT3_VALUE_SET_ID,-99) = -99
1170 AND NVL(SEGMENT4_VALUE_SET_ID,-99) = -99
1171 AND NVL(SEGMENT5_VALUE_SET_ID,-99) = -99;
1172 EXCEPTION
1173 WHEN NO_DATA_FOUND THEN
1174 pv_cc_vs_id := -1;
1175 END;
1176
1177 IF pv_com_vs_id = -1
1178 THEN
1179 --
1180 -- Create FEM Value Set
1181 --
1182 pv_progress := 'insert newly created company vset into aol mapping table';
1183
1184 IF v_com_fnd_vs_id = -1
1185 THEN
1186 SELECT default_value_set_id
1187 INTO pv_com_vs_id
1188 FROM fem_xdim_dimensions
1189 WHERE dimension_id = pv_com_dim_id;
1190 ELSE
1191 SELECT FEM_VALUE_SETS_B_S.nextval
1192 INTO pv_com_vs_id
1193 FROM DUAL;
1194 END IF;
1195
1196 INSERT INTO FEM_INTG_AOL_VALSET_MAP
1197 (
1198 FEM_VALUE_SET_ID,
1199 DIMENSION_ID,
1200 SEGMENT1_VALUE_SET_ID,
1201 SEGMENT2_VALUE_SET_ID,
1202 SEGMENT3_VALUE_SET_ID,
1203 SEGMENT4_VALUE_SET_ID,
1204 SEGMENT5_VALUE_SET_ID,
1205 CREATION_DATE,
1206 CREATED_BY,
1207 LAST_UPDATE_DATE,
1208 LAST_UPDATED_BY,
1209 LAST_UPDATE_LOGIN
1210 )
1211 VALUES
1212 (
1213 pv_com_vs_id,
1214 pv_com_dim_id,
1215 v_com_fnd_vs_id,
1216 -99,
1217 -99,
1218 -99,
1219 -99,
1220 SYSDATE,
1221 pv_user_id,
1222 SYSDATE,
1223 pv_user_id,
1224 pv_login_id
1225 );
1226
1227 IF v_com_fnd_vs_id <> -1
1228 THEN
1229 --------------------------------------------------
1230 -- Register FEM value set
1231 ---------------------------------------------------
1232 register_fem_value_set (p_fem_value_set_id => pv_com_vs_id
1233 ,p_regiser_type => 'COMPANY'
1234 ,p_fnd_vs_id => v_com_fnd_vs_id
1235 ,p_dim_id => pv_com_dim_id
1236 ,x_status => v_status);
1237 IF v_status = 0
1238 THEN
1239 FEM_ENGINES_PKG.Tech_Message
1240 ( p_severity => pc_log_level_error
1241 ,p_module => pc_module_name||c_func_name
1242 ,p_msg_text => 'Unexpected error while registering company value set');
1243 raise_application_error(-20001, fnd_message.get);
1244 END IF; /* IF v_status = 0 */
1245 END IF; /* IF v_com_fnd_vs_id <> -1 */
1246 END IF; /* IF pv_com_vs_id = -1 */
1247
1248 pv_progress := 'Update FEM_GLOBAL_VS_COMBO_DEFS for company dim';
1249
1250 UPDATE FEM_GLOBAL_VS_COMBO_DEFS
1251 SET VALUE_SET_ID = pv_com_vs_id
1252 ,LAST_UPDATED_BY = pv_user_id
1253 ,LAST_UPDATE_DATE = SYSDATE
1254 ,LAST_UPDATE_LOGIN = pv_login_id
1255 WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
1256 AND DIMENSION_ID = pv_com_dim_id;
1257
1258 IF pv_cc_vs_id = -1
1259 THEN
1260 --
1261 -- Create FEM Value Set
1262 --
1263 pv_progress := 'insert newly created cost center vset into aol mapping table';
1264
1265 IF v_cc_fnd_vs_id = -1
1266 THEN
1267 SELECT default_value_set_id
1268 INTO pv_cc_vs_id
1269 FROM fem_xdim_dimensions
1270 WHERE dimension_id = pv_cc_dim_id;
1271 ELSE
1272 SELECT FEM_VALUE_SETS_B_S.nextval
1273 INTO pv_cc_vs_id
1274 FROM DUAL;
1275 END IF;
1276
1277 INSERT INTO FEM_INTG_AOL_VALSET_MAP
1278 (
1279 FEM_VALUE_SET_ID,
1280 DIMENSION_ID,
1281 SEGMENT1_VALUE_SET_ID,
1282 SEGMENT2_VALUE_SET_ID,
1283 SEGMENT3_VALUE_SET_ID,
1287 CREATED_BY,
1284 SEGMENT4_VALUE_SET_ID,
1285 SEGMENT5_VALUE_SET_ID,
1286 CREATION_DATE,
1288 LAST_UPDATE_DATE,
1289 LAST_UPDATED_BY,
1290 LAST_UPDATE_LOGIN
1291 )
1292 VALUES
1293 (
1294 pv_cc_vs_id,
1295 pv_cc_dim_id,
1296 v_cc_fnd_vs_id,
1297 -99,
1298 -99,
1299 -99,
1300 -99,
1301 SYSDATE,
1302 pv_user_id,
1303 SYSDATE,
1304 pv_user_id,
1305 pv_login_id
1306 );
1307
1308 IF v_cc_fnd_vs_id <> -1
1309 THEN
1310 --------------------------------------------------
1311 -- Register FEM value set
1312 ---------------------------------------------------
1313 register_fem_value_set (p_fem_value_set_id => pv_cc_vs_id
1314 ,p_regiser_type => 'COMPANY'
1315 ,p_fnd_vs_id => v_cc_fnd_vs_id
1316 ,p_dim_id => pv_cc_dim_id
1317 ,x_status => v_status);
1318 IF v_status = 0
1319 THEN
1320 FEM_ENGINES_PKG.Tech_Message
1321 ( p_severity => pc_log_level_error
1322 ,p_module => pc_module_name||c_func_name
1323 ,p_msg_text => 'Unexpected error while registering cost center value set');
1324 raise_application_error(-20001, fnd_message.get);
1325 END IF; /* IF v_status = 0 */
1326 END IF; /* IF v_cc_fnd_vs_id <> -1 */
1327 END IF; /* IF pv_cc_vs_id = -1 */
1328
1329 pv_progress := 'Insert into FEM_GLOBAL_VS_COMBO_DEFS for cc dim';
1330 UPDATE FEM_GLOBAL_VS_COMBO_DEFS
1331 SET VALUE_SET_ID = pv_cc_vs_id
1332 ,LAST_UPDATED_BY = pv_user_id
1333 ,LAST_UPDATE_DATE = SYSDATE
1334 ,LAST_UPDATE_LOGIN = pv_login_id
1335 WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
1336 AND DIMENSION_ID = pv_cc_dim_id;
1337
1338 END IF; /* IF pv_dim_mapping_option_code = 'MULTISEG' */
1339 END IF; /* IF pv_dim_varchar_label = 'COMPANY_COST_CENTER_ORG' */
1340
1341 pv_progress := 'Before update fem_intg_dim_rule_defs';
1342
1343 FEM_ENGINES_PKG.Tech_Message
1344 ( p_severity => pc_log_level_event
1345 ,p_module => pc_module_name||c_func_name
1346 ,p_msg_text => 'Before updating fem_global_vs_combo_defs with'
1347 ||pv_fem_vs_id||' for dimension '||pv_dim_id);
1348
1349 FEM_ENGINES_PKG.User_Message(
1350 p_app_name => 'FEM',
1351 p_msg_name => 'FEM_INTG_DIM_ENG_502',
1352 p_token1 => 'VERSION_ID',
1353 p_value1 => p_dim_rule_obj_def_id,
1354 p_token2 => 'VALUE_SET_ID',
1355 p_value2 => pv_fem_vs_id
1356 );
1357
1358 UPDATE fem_intg_dim_rule_defs
1359 SET fem_value_set_id = pv_fem_vs_id
1360 WHERE dim_rule_obj_def_id = p_dim_rule_obj_def_id;
1361
1362 /*
1363 * create place holder records in mapping table
1364 * =============================================
1365 */
1366
1367 FEM_ENGINES_PKG.Tech_Message
1368 ( p_severity => pc_log_level_event
1369 ,p_module => pc_module_name||c_func_name
1370 ,p_msg_text => 'Before locking fem_intg_ogl_ccid_map');
1371
1372 -- Bug fix 4301926
1373 LOCK TABLE FEM_INTG_OGL_CCID_MAP IN EXCLUSIVE MODE;
1374
1375 -- Get max_id of place holder records
1376
1377 SELECT NVL(MAX(CODE_COMBINATION_ID),-1)
1378 INTO v_max_ccid_in_map_table
1379 FROM FEM_INTG_OGL_CCID_MAP
1380 WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id;
1381
1382
1383 --Get max_id of records in CCID table
1384
1385 SELECT NVL(MAX(CODE_COMBINATION_ID),-1)
1386 INTO v_max_ccid_in_glccid_table
1387 FROM GL_CODE_COMBINATIONS
1388 WHERE CHART_OF_ACCOUNTS_ID = pv_coa_id;
1389
1390 IF v_max_ccid_in_map_table < v_max_ccid_in_glccid_table
1391 THEN
1392 FEM_ENGINES_PKG.Tech_Message
1393 ( p_severity => pc_log_level_event
1394 ,p_module => pc_module_name||c_func_name
1395 ,p_msg_text => 'Before calling create_map_placeholder_records');
1396
1397 FEM_ENGINES_PKG.User_Message(
1398 p_app_name => 'FEM',
1399 p_msg_name => 'FEM_INTG_DIM_ENG_503');
1400
1401 create_map_placeholder_records(
1402 pv_coa_id,
1403 pv_gvsc_id,
1404 v_max_ccid_in_map_table,
1405 v_max_ccid_in_glccid_table,
1406 v_map_records_inserted_count
1407 );
1408 ELSE
1409 FEM_ENGINES_PKG.Tech_Message
1410 ( p_severity => pc_log_level_event
1411 ,p_module => pc_module_name||c_func_name
1412 ,p_msg_text => 'Not creating mapping records ');
1413 END IF;
1414
1415 COMMIT;
1416
1417 pv_max_ccid_to_be_mapped := v_max_ccid_in_glccid_table;
1418
1419
1420 ---------------------------------------------------------------------------
1421 -- *** Print all package level variable values to debug log
1425 pv_dim_id := pv_cctr_org_dim_id;
1422 ---------------------------------------------------------------------------
1423
1424 IF NVL(pv_dim_varchar_label,'X') = 'INTERCOMPANY' THEN
1426 END IF;
1427
1428 print_pkg_variable_values;
1429
1430 /*
1431 * Call sub-modules Single value/Single Segment/Multiple Segment
1432 * based on type
1433 */
1434 pv_progress := 'Before Case';
1435 CASE pv_dim_mapping_option_code
1436 WHEN 'SINGLESEG'
1437 THEN
1438 FEM_ENGINES_PKG.Tech_Message
1439 ( p_severity => pc_log_level_event
1440 ,p_module => pc_module_name||c_func_name
1441 ,p_msg_text => 'Before calling Detail_Single_Segment');
1442
1443 FEM_ENGINES_PKG.User_Message(
1444 p_app_name => 'FEM',
1445 p_msg_name => 'FEM_INTG_DIM_ENG_504');
1446
1447 FEM_INTG_NEW_DIM_MEMBER_PKG.Detail_Single_Segment (v_completion_code
1448 ,v_dim_process_row_cnt);
1449 -- start bug fix 5377544
1450 -- Start bug fix 5560443 - Since intercomapny rule is going to launch the worker requests
1451 -- we need to have entry point for launch workers incase intercompany is singleval segment
1452 WHEN 'SINGLEVAL'
1453 THEN
1454 IF pv_dim_varchar_label = 'INTERCOMPANY' THEN
1455 FEM_ENGINES_PKG.Tech_Message
1456 ( p_severity => pc_log_level_event
1457 ,p_module => pc_module_name||c_func_name
1458 ,p_msg_text => 'Before calling Detail_Single_Value');
1459
1460 FEM_ENGINES_PKG.User_Message(
1461 p_app_name => 'FEM',
1462 p_msg_name => 'FEM_INTG_DIM_ENG_505');
1463
1464 FEM_INTG_NEW_DIM_MEMBER_PKG.Detail_Single_Value (v_completion_code
1465 ,v_dim_process_row_cnt);
1466 END IF;
1467 -- End bug fix 5560443
1468 -- end bug fix 5377544
1469
1470 WHEN 'MULTISEG'
1471 THEN
1472 FEM_ENGINES_PKG.Tech_Message
1473 ( p_severity => pc_log_level_event
1474 ,p_module => pc_module_name||c_func_name
1475 ,p_msg_text => 'Before calling Detail_Multi_Segment');
1476
1477 FEM_ENGINES_PKG.User_Message(
1478 p_app_name => 'FEM',
1479 p_msg_name => 'FEM_INTG_DIM_ENG_506');
1480
1481 FEM_INTG_NEW_DIM_MEMBER_PKG.Detail_Multi_Segment (v_completion_code
1482 ,v_dim_process_row_cnt);
1483 ELSE
1484 FEM_ENGINES_PKG.Tech_Message
1485 ( p_severity => pc_log_level_event
1486 ,p_module => pc_module_name||c_func_name
1487 ,p_msg_text => 'Case statement for calling segment population not
1488 satisfied: '||pv_dim_mapping_option_code);
1489 END CASE;
1490 IF v_completion_code <> 0
1491 THEN
1492 raise_application_error(-20001, fnd_message.get);
1493 END IF;
1494
1495 FEM_ENGINES_PKG.Tech_Message
1496 (p_severity => pc_log_level_procedure,
1497 p_module => pc_module_name || c_func_name,
1498 p_app_name => 'FEM',
1499 p_msg_name => 'FEM_GL_POST_202',
1500 p_token1 => 'FUNC_NAME',
1501 p_value1 => pc_module_name||c_func_name,
1502 p_token2 => 'TIME',
1503 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1504
1505
1506 FEM_INTG_PL_PKG.Final_Process_Logging(
1507 p_obj_id => pv_dim_rule_obj_id,
1508 p_obj_def_id => pv_dim_rule_obj_def_id,
1509 p_req_id => pv_req_id,
1510 p_user_id => pv_user_id,
1511 p_login_id => pv_login_id,
1512 p_exec_status => 'SUCCESS',
1513 p_row_num_loaded => v_dim_process_row_cnt,
1514 p_err_num_count => 0,
1515 p_final_msg_name => 'FEM_INTG_PROC_SUCCESS',
1516 p_module_name => pc_module_name,
1517 x_completion_code => v_completion_code_final
1518 );
1519
1520
1521 -- Run Intercompany if applicable
1522 IF pv_dim_varchar_label = 'COMPANY_COST_CENTER_ORG' THEN
1523 SELECT odb.object_definition_id
1524 INTO v_interco_rule_def_id
1525 FROM fem_object_definition_b odb,
1526 fem_intg_dim_rules rule
1527 WHERE odb.object_id = rule.dim_rule_obj_id
1528 AND rule.chart_of_accounts_id = pv_coa_id
1529 AND rule.dimension_id = 0;
1530
1531 v_interco_req_id := FND_REQUEST.submit_request(
1532 'FEM', 'FEM_INTG_DIM_RULE_ENGINE', null, null, FALSE,
1533 to_char(v_interco_rule_def_id), 'MEMBER');
1534 END IF;
1535
1536 commit;
1537
1538
1539 FEM_ENGINES_PKG.Tech_Message
1540 ( p_severity => pc_log_level_event
1541 ,p_module => pc_module_name||c_func_name
1542 ,p_msg_text => 'Gathering statistics for FEM');
1543
1544 FEM_ENGINES_PKG.User_Message
1545 ( p_msg_text => 'Before Gathering statistics for FEM');
1546
1547 -- fnd_stats.gather_schema_statistics('FEM', null, null, null, null);
1548 -- Bug 5084804
1549 fnd_stats.GATHER_TABLE_STATS('FEM','FEM_INTG_AOL_VALSET_MAP');
1550 fnd_stats.GATHER_TABLE_STATS('FEM','FEM_INTG_OGL_CCID_MAP');
1551 fnd_stats.GATHER_TABLE_STATS('FEM','FEM_COMPANIES_B');
1552 fnd_stats.GATHER_TABLE_STATS('FEM','FEM_COMPANIES_TL');
1556 fnd_stats.GATHER_TABLE_STATS('FEM', pv_member_b_table_name);
1553 fnd_stats.GATHER_TABLE_STATS('FEM','FEM_COST_CENTERS_B');
1554 fnd_stats.GATHER_TABLE_STATS('FEM','FEM_COST_CENTERS_TL');
1555
1557 fnd_stats.GATHER_TABLE_STATS('FEM', pv_member_tl_table_name);
1558 fnd_stats.GATHER_TABLE_STATS('FEM', pv_attr_table_name);
1559
1560 select INDEX_NAME BULK COLLECT
1561 INTO pv_index_name
1562 from all_indexes where table_name in('FEM_INTG_AOL_VALSET_MAP',
1563 'FEM_INTG_OGL_CCID_MAP',
1564 'FEM_COMPANIES_B',
1565 'FEM_COMPANIES_TL',
1566 'FEM_COST_CENTERS_B',
1567 'FEM_COST_CENTERS_TL',
1568 pv_member_b_table_name,
1569 pv_member_tl_table_name,
1570 pv_attr_table_name)
1571 AND table_owner = 'FEM' and owner = 'FEM';
1572
1573 IF (pv_index_name.FIRST IS NOT NULL AND pv_index_name.LAST IS NOT NULL) THEN
1574 FOR i in pv_index_name.FIRST.. pv_index_name.LAST
1575 LOOP
1576 --bug fix 5489150
1577 --fnd_stats.GATHER_INDEX_STATS('FEM',pv_index_name(i),null,null,null);
1578 fnd_stats.GATHER_INDEX_STATS('FEM',pv_index_name(i));
1579 END LOOP;
1580 END IF;
1581
1582 FEM_ENGINES_PKG.User_Message
1583 ( p_msg_text => 'After Gathering statistics for FEM');
1584
1585 --
1586 -- Bug#6238739: Added to maintain FEM_LEDGER_DIM_VS_MAPS mapping table.
1587 --
1588 FEM_ENGINES_PKG.Tech_Message
1589 ( p_severity => pc_log_level_event
1590 ,p_module => pc_module_name||c_func_name
1591 ,p_msg_text => 'Calling Refresh_ledger_vs_maps API'
1592 ) ;
1593
1594 FEM_GLOBAL_VS_COMBO_UTIL_PKG.Refresh_ledger_vs_maps
1595 (
1596 x_return_status => l_return_status,
1597 x_msg_count => l_msg_count,
1598 x_msg_data => l_msg_data,
1599 p_global_vs_combo_id => pv_gvsc_id
1600 ) ;
1601 --
1602 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1603 RAISE FND_API.G_EXC_ERROR;
1604 END IF ;
1605 -- Bug#6238739: End
1606
1607 FEM_ENGINES_PKG.Tech_Message(
1608 p_severity => pc_log_level_statement,
1609 p_module => pc_module_name || '.return_values',
1610 p_msg_text => 'v_completion_code=' || v_completion_code_final ||
1611 ', v_dim_process_row_cnt=' || v_dim_process_row_cnt ||
1612 ', v_completion_code_final=' || v_completion_code_final
1613 );
1614
1615 EXCEPTION
1616 WHEN app_exceptions.application_exception THEN
1617 ROLLBACK;
1618 print_pkg_variable_values;
1619
1620 FEM_ENGINES_PKG.User_Message
1621 (p_msg_text => fnd_message.get);
1622
1623 FEM_ENGINES_PKG.Tech_Message
1624 (p_severity => pc_log_level_unexpected
1625 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
1626 ,p_msg_text => sqlerrm);
1627
1628 FEM_ENGINES_PKG.Tech_Message
1629 (p_severity => pc_log_level_unexpected
1630 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
1631 ,p_msg_text => 'Location before failure: '||pv_progress);
1632
1633 FEM_ENGINES_PKG.Tech_Message
1634 (p_severity => pc_log_level_procedure
1635 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
1636 ,p_app_name => 'FEM'
1637 ,p_msg_name => 'FEM_GL_POST_203'
1638 ,p_token1 => 'FUNC_NAME'
1639 ,p_value1 => pc_module_name||c_func_name
1640 ,p_token2 => 'TIME'
1641 ,p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1642
1643 FEM_INTG_PL_PKG.Final_Process_Logging(
1644 p_obj_id => pv_dim_rule_obj_id,
1645 p_obj_def_id => pv_dim_rule_obj_def_id,
1646 p_req_id => pv_req_id,
1647 p_user_id => pv_user_id,
1648 p_login_id => pv_login_id,
1649 p_exec_status => 'ERROR_RERUN',
1650 p_row_num_loaded => 0,
1651 p_err_num_count => v_dim_process_row_cnt,
1652 p_final_msg_name => 'FEM_INTG_PROC_FAILURE',
1653 p_module_name => pc_module_name,
1654 x_completion_code => v_completion_code_final
1655 );
1656 x_retcode := 2;
1657 x_errbuf := fnd_message.get;
1658
1659 WHEN OTHERS THEN
1660 ROLLBACK;
1661 print_pkg_variable_values;
1662
1663 FEM_ENGINES_PKG.Tech_Message
1664 (p_severity => pc_log_level_unexpected
1665 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
1666 ,p_msg_text => sqlerrm);
1667
1668 FEM_ENGINES_PKG.Tech_Message
1669 (p_severity => pc_log_level_unexpected
1670 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
1671 ,p_msg_text => 'Location before failure: '||pv_progress);
1672
1673 FEM_ENGINES_PKG.User_Message
1674 (p_msg_text => sqlerrm);
1675
1676 FEM_ENGINES_PKG.Tech_Message
1677 (p_severity => pc_log_level_procedure
1678 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
1682 ,p_value1 => pc_module_name||c_func_name
1679 ,p_app_name => 'FEM'
1680 ,p_msg_name => 'FEM_GL_POST_203'
1681 ,p_token1 => 'FUNC_NAME'
1683 ,p_token2 => 'TIME'
1684 ,p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1685
1686 FEM_INTG_PL_PKG.Final_Process_Logging(
1687 p_obj_id => pv_dim_rule_obj_id,
1688 p_obj_def_id => pv_dim_rule_obj_def_id,
1689 p_req_id => pv_req_id,
1690 p_user_id => pv_user_id,
1691 p_login_id => pv_login_id,
1692 p_exec_status => 'ERROR_RERUN',
1693 p_row_num_loaded => 0,
1694 p_err_num_count => v_dim_process_row_cnt,
1695 p_final_msg_name => 'FEM_INTG_PROC_FAILURE',
1696 p_module_name => pc_module_name,
1697 x_completion_code => v_completion_code_final
1698 );
1699
1700 x_retcode := 2;
1701 x_errbuf := fnd_message.get;
1702 END;
1703
1704
1705 PROCEDURE create_map_placeholder_records
1706 (p_coa_id IN NUMBER
1707 ,p_gvsc_id IN NUMBER
1708 ,p_max_ccid_in_map_table IN NUMBER
1709 ,p_max_ccid_in_glccid_table IN NUMBER
1710 ,x_rows_processed OUT NOCOPY NUMBER)
1711 IS
1712 v_user_id number;
1713 v_login_id number;
1714 c_func_name constant varchar2(30) := 'create_map_placeholder_records';
1715 BEGIN
1716 FEM_ENGINES_PKG.Tech_Message
1717 (p_severity => pc_log_level_procedure,
1718 p_module => pc_module_name||c_func_name,
1719 p_app_name => 'FEM',
1720 p_msg_name => 'FEM_GL_POST_201',
1721 p_token1 => 'FUNC_NAME',
1722 p_value1 => pc_module_name||c_func_name,
1723 p_token2 => 'TIME',
1724 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1725
1726 INSERT INTO FEM_INTG_OGL_CCID_MAP(
1727 CODE_COMBINATION_ID,
1728 GLOBAL_VS_COMBO_ID,
1729 COMPANY_COST_CENTER_ORG_ID,
1730 NATURAL_ACCOUNT_ID,
1731 LINE_ITEM_ID,
1732 CREATION_DATE,
1733 CREATED_BY,
1734 LAST_UPDATE_DATE,
1735 LAST_UPDATED_BY,
1736 LAST_UPDATE_LOGIN,
1737 PRODUCT_ID,
1738 CHANNEL_ID,
1739 PROJECT_ID,
1740 CUSTOMER_ID,
1741 ENTITY_ID,
1742 INTERCOMPANY_ID,
1743 USER_DIM1_ID,
1744 USER_DIM2_ID,
1745 USER_DIM3_ID,
1746 USER_DIM4_ID,
1747 USER_DIM5_ID,
1748 USER_DIM6_ID,
1749 USER_DIM7_ID,
1750 USER_DIM8_ID,
1751 USER_DIM9_ID,
1752 USER_DIM10_ID,
1753 TASK_ID,
1754 EXTENDED_ACCOUNT_TYPE)
1755 SELECT
1756 GLCC.CODE_COMBINATION_ID,
1757 p_gvsc_id,
1758 -1,
1759 -1,
1760 -1,
1761 SYSDATE,
1762 pv_user_id,
1763 SYSDATE,
1764 pv_user_id,
1765 pv_login_id,
1766 -1,
1767 -1,
1768 -1,
1769 -1,
1770 -1,
1771 -1,
1772 -1,
1773 -1,
1774 -1,
1775 -1,
1776 -1,
1777 -1,
1778 -1,
1779 -1,
1780 -1,
1781 -1,
1782 -1,
1783 -1
1784 FROM GL_CODE_COMBINATIONS GLCC
1785 WHERE GLCC.CODE_COMBINATION_ID BETWEEN
1786 p_max_ccid_in_map_table+1 AND p_max_ccid_in_glccid_table
1787 AND CHART_OF_ACCOUNTS_ID = p_coa_id
1788 AND GLCC.SUMMARY_FLAG = 'N';
1789
1790 x_rows_processed := SQL%ROWCOUNT;
1791 FEM_ENGINES_PKG.Tech_Message
1792 (p_severity => pc_log_level_statement
1793 ,p_module => pc_module_name||c_func_name
1794 ,p_app_name => 'FEM'
1795 ,p_msg_name => 'FEM_GL_POST_216'
1796 ,p_token1 => 'TABLE'
1797 ,p_value1 => 'FEM_INTG_OGL_CCID_MAP'
1798 ,p_token2 => 'NUM'
1799 ,p_value2 => x_rows_processed);
1800
1801 FEM_ENGINES_PKG.Tech_Message
1802 (p_severity => pc_log_level_procedure,
1803 p_module => pc_module_name || c_func_name,
1804 p_app_name => 'FEM',
1805 p_msg_name => 'FEM_GL_POST_202',
1806 p_token1 => 'FUNC_NAME',
1807 p_value1 => pc_module_name||c_func_name,
1808 p_token2 => 'TIME',
1809 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1810 EXCEPTION
1811 WHEN OTHERS THEN
1812 FEM_ENGINES_PKG.Tech_Message
1813 (p_severity => pc_log_level_unexpected
1814 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
1815 ,p_msg_text => sqlerrm);
1816
1817
1818 FEM_ENGINES_PKG.User_Message
1819 (p_msg_text => sqlerrm);
1820
1821 FEM_ENGINES_PKG.Tech_Message
1822 (p_severity => pc_log_level_procedure
1823 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
1824 ,p_app_name => 'FEM'
1825 ,p_msg_name => 'FEM_GL_POST_203'
1826 ,p_token1 => 'FUNC_NAME'
1827 ,p_value1 => pc_module_name||c_func_name
1831
1828 ,p_token2 => 'TIME'
1829 ,p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1830 END;
1832 PROCEDURE register_fem_value_set (p_fem_value_set_id IN NUMBER
1833 ,p_regiser_type IN VARCHAR2
1834 ,p_fnd_vs_id IN NUMBER
1835 ,p_dim_id IN NUMBER
1836 ,x_status IN OUT NOCOPY NUMBER) IS
1837 v_vs_name VARCHAR2(755) := null;
1838 v_vs_desc VARCHAR2(1500) := null;
1839 v_seg_name VARCHAR2(60) := null;
1840 v_seg_desc VARCHAR2(240) := null;
1841 v_rowid ROWID;
1842 c_func_name CONSTANT VARCHAR2(30) := '.register_fem_value_set';
1843 v_vs_count NUMBER;
1844
1845 v_return_status VARCHAR2(100);
1846 v_msg_count NUMBER;
1847 v_msg_data VARCHAR2(2000);
1848 BEGIN
1849 FEM_ENGINES_PKG.Tech_Message
1850 (p_severity => pc_log_level_procedure,
1851 p_module => pc_module_name||c_func_name,
1852 p_app_name => 'FEM',
1853 p_msg_name => 'FEM_GL_POST_201',
1854 p_token1 => 'FUNC_NAME',
1855 p_value1 => pc_module_name||c_func_name,
1856 p_token2 => 'TIME',
1857 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1858 x_status := 1;
1859
1860 IF p_regiser_type = 'STANDARD'
1861 THEN
1862 SELECT flex_value_set_name
1863 ,description
1864 INTO v_vs_name
1865 ,v_vs_desc
1866 FROM FND_FLEX_VALUE_SETS
1867 WHERE flex_value_set_id = pv_mapped_segs(1).vs_id;
1868
1869 IF pv_segment_count > 1
1870 THEN
1871 FOR i in 2..pv_segment_count
1872 LOOP
1873 SELECT flex_value_set_name
1874 ,description
1875 INTO v_seg_name
1876 ,v_seg_desc
1877 FROM FND_FLEX_VALUE_SETS
1878 WHERE flex_value_set_id = pv_mapped_segs(i).vs_id;
1879
1880 v_vs_name := v_vs_name || '-'||v_seg_name;
1881 v_vs_desc := v_vs_desc || '-'||v_seg_desc;
1882
1883 END LOOP;
1884 END IF;
1885 ELSE
1886 SELECT flex_value_set_name
1887 ,description
1888 INTO v_vs_name
1889 ,v_vs_desc
1890 FROM FND_FLEX_VALUE_SETS
1891 WHERE flex_value_set_id = p_fnd_vs_id;
1892 END IF;
1893
1894 --
1895 -- Check to see if the valueset already exists
1896 --
1897 SELECT count(*)
1898 INTO v_vs_count
1899 FROM FEM_VALUE_SETS_B
1900 WHERE value_set_id = p_fem_value_set_id;
1901
1902 IF length(v_vs_name) > 140
1903 THEN
1904 v_vs_name := substr(v_vs_name, 1,140)||':DIM:'||p_dim_id;
1905 ELSE
1906 v_vs_name := v_vs_name||':DIM:'||p_dim_id;
1907 END IF;
1908
1909 IF length(v_vs_desc) > 245
1910 THEN
1911 v_vs_desc := substr(v_vs_desc, 1,245)||':DIM:'||p_dim_id;
1912 ELSE
1913 v_vs_desc := v_vs_desc||':DIM:'||p_dim_id;
1914 END IF;
1915
1916 IF v_vs_count = 0
1917 THEN
1918 BEGIN
1919 FEM_ENGINES_PKG.Tech_Message
1920 ( p_severity => pc_log_level_statement
1921 ,p_module => pc_module_name||c_func_name
1922 ,p_msg_text => 'Calling FEM_VALUE_SETS_PKG.insert_row with' ||pv_crlf||
1923 'X_VALUE_SET_ID => '||p_fem_value_set_id||pv_crlf||
1924 'X_DEFAULT_LOAD_MEMBER_ID => NULL'||pv_crlf||
1925 'X_DEFAULT_MEMBER_ID => NULL'||pv_crlf||
1926 'X_OBJECT_VERSION_NUMBER => 1'||pv_crlf||
1927 'X_DEFAULT_HIERARCHY_OBJ_ID => NULL'||pv_crlf||
1928 'X_READ_ONLY_FLAG => N'||pv_crlf||
1929 'X_VALUE_SET_DISPLAY_CODE => '||v_vs_name||pv_crlf||
1930 'X_DIMENSION_ID => '||p_dim_id||pv_crlf||
1931 'X_VALUE_SET_NAME => '||v_vs_name||pv_crlf||
1932 'X_DESCRIPTION => '||v_vs_desc);
1933
1934 FEM_VALUE_SETS_PKG.insert_row
1935 ( X_ROWID => v_rowid
1936 , X_VALUE_SET_ID => p_fem_value_set_id
1937 , X_DEFAULT_LOAD_MEMBER_ID => NULL
1938 , X_DEFAULT_MEMBER_ID => NULL
1939 , X_OBJECT_VERSION_NUMBER => 1
1940 , X_DEFAULT_HIERARCHY_OBJ_ID => NULL
1941 , X_READ_ONLY_FLAG => 'N'
1942 , X_VALUE_SET_DISPLAY_CODE => v_vs_name
1943 , X_DIMENSION_ID => p_dim_id
1944 , X_VALUE_SET_NAME => v_vs_name
1945 , X_DESCRIPTION => v_vs_desc
1946 , X_CREATION_DATE => SYSDATE
1947 , X_CREATED_BY => pv_user_id
1948 , X_LAST_UPDATE_DATE => SYSDATE
1949 , X_LAST_UPDATED_BY => pv_user_id
1950 , X_LAST_UPDATE_LOGIN => pv_login_id);
1951
1952 FEM_ENGINES_PKG.Tech_Message
1953 ( p_severity => pc_log_level_statement
1954 ,p_module => pc_module_name||c_func_name
1955 ,p_msg_text => 'Row ID returned'||v_rowid);
1956
1957 IF v_rowid IS NULL
1958 THEN
1962 -- Now create the default value and update the dimension rule
1959 x_status := 0;
1960 END IF;
1961
1963 IF pv_dim_varchar_label = 'COMPANY_COST_CENTER_ORG' AND
1964 p_regiser_type = 'STANDARD' THEN
1965 fem_dimension_util_pkg.generate_default_load_member
1966 (x_return_status => v_return_status,
1967 x_msg_count => v_msg_count,
1968 x_msg_data => v_msg_data,
1969 p_vs_id => p_fem_value_set_id);
1970
1971 UPDATE fem_cctr_orgs_b
1972 SET read_only_flag = 'Y'
1973 WHERE cctr_org_display_code = 'Default'
1974 AND value_set_id = p_fem_value_set_id;
1975
1976 END IF;
1977
1978 EXCEPTION
1979 WHEN OTHERS THEN
1980 x_status := 0;
1981 FEM_ENGINES_PKG.Tech_Message
1982 (p_severity => pc_log_level_unexpected
1983 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
1984 ,p_msg_text => sqlerrm);
1985 raise_application_error(-20001, fnd_message.get);
1986 END;
1987 ELSE
1988 FEM_ENGINES_PKG.Tech_Message
1989 ( p_severity => pc_log_level_statement
1990 ,p_module => pc_module_name||c_func_name
1991 ,p_msg_text => 'FEM valueset already exists for '||p_fem_value_set_id);
1992
1993 END IF;
1994 FEM_ENGINES_PKG.Tech_Message
1995 (p_severity => pc_log_level_procedure,
1996 p_module => pc_module_name || c_func_name,
1997 p_app_name => 'FEM',
1998 p_msg_name => 'FEM_GL_POST_202',
1999 p_token1 => 'FUNC_NAME',
2000 p_value1 => pc_module_name||c_func_name,
2001 p_token2 => 'TIME',
2002 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2003
2004 EXCEPTION
2005 WHEN OTHERS THEN
2006 x_status := 0;
2007 FEM_ENGINES_PKG.Tech_Message
2008 (p_severity => pc_log_level_unexpected
2009 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
2010 ,p_msg_text => sqlerrm);
2011
2012 FEM_ENGINES_PKG.User_Message
2013 (p_msg_text => sqlerrm);
2014
2015 FEM_ENGINES_PKG.Tech_Message
2016 (p_severity => pc_log_level_procedure
2017 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
2018 ,p_app_name => 'FEM'
2019 ,p_msg_name => 'FEM_GL_POST_203'
2020 ,p_token1 => 'FUNC_NAME'
2021 ,p_value1 => pc_module_name||c_func_name
2022 ,p_token2 => 'TIME'
2023 ,p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2024
2025 raise_application_error(-20001, fnd_message.get);
2026
2027 END;
2028
2029 PROCEDURE print_pkg_variable_values IS
2030 c_func_name CONSTANT VARCHAR2(30) := '.print_pkg_variable_values';
2031 BEGIN
2032 FEM_ENGINES_PKG.Tech_Message
2033 ( p_severity => pc_log_level_procedure
2034 ,p_module => pc_module_name||c_func_name
2035 ,p_msg_text => 'Values of package variables'||pv_crlf||
2036 'pv_dim_rule_obj_id:'||pv_dim_rule_obj_id||pv_crlf||
2037 'pv_dim_rule_obj_def_id:'||pv_dim_rule_obj_def_id||pv_crlf||
2038 'pv_dim_id:'||pv_dim_id||pv_crlf||
2039 'pv_user_id:'||pv_user_id||pv_crlf||
2040 'pv_dim_varchar_label:'||pv_dim_varchar_label||pv_crlf||
2041 'pv_member_b_table_name:'||pv_member_b_table_name||pv_crlf||
2042 'pv_member_tl_table_name:'||pv_member_tl_table_name||pv_crlf||
2043 'pv_member_vl_object_name:'||pv_member_vl_object_name||pv_crlf||
2044 'pv_member_col:'||pv_member_col||pv_crlf||
2045 'pv_member_display_code_col:'||pv_member_display_code_col||pv_crlf||
2046 'pv_member_name_col:'||pv_member_name_col||pv_crlf||
2047 'pv_member_desc_col:'||pv_member_desc_col||pv_crlf||
2048 'pv_attr_table_name:'||pv_attr_table_name||pv_crlf||
2049 'pv_coa_id:'||pv_coa_id||pv_crlf||
2050 'pv_gvsc_id:'||pv_gvsc_id||pv_crlf||
2051 'pv_fem_vs_id:'||pv_fem_vs_id||pv_crlf||
2052 'pv_ledger_attr_varchar_label:'||pv_ledger_attr_varchar_label||pv_crlf||
2053 'pv_com_dim_id:'||pv_com_dim_id||pv_crlf||
2054 'pv_cc_dim_id:'||pv_cc_dim_id||pv_crlf||
2055 'pv_cctr_org_dim_id:'||pv_cctr_org_dim_id||pv_crlf||
2056 'pv_dim_mapping_option_code:'||pv_dim_mapping_option_code||pv_crlf||
2057 'pv_default_member_id:'||pv_default_member_id||pv_crlf||
2058 'pv_default_member_vsid:'||pv_default_member_vs_id||pv_crlf||
2059 'pv_segment_count:'||pv_segment_count||pv_crlf||
2060 'pv_balancing_segment_num:'||pv_balancing_segment_num||pv_crlf||
2061 'pv_cost_center_segment_num:'||pv_cost_center_segment_num||pv_crlf||
2062 'pv_natural_account_segment_num:'||pv_natural_account_segment_num||pv_crlf||
2063 'pv_source_system_code_id:'||pv_source_system_code_id||pv_crlf||
2064 'pv_max_ccid_processed:'||pv_max_ccid_processed||pv_crlf||
2065 'pv_max_ccid_to_be_mapped:'||pv_max_ccid_to_be_mapped||pv_crlf||
2066 'pv_max_ccid_in_map_table:'||pv_max_ccid_in_map_table||pv_crlf||
2067 'pv_max_flex_value_id_processed:'||pv_max_flex_value_id_processed||pv_crlf||
2068 'pv_ext_acct_type_attr_id:'||pv_ext_acct_type_attr_id||pv_crlf||
2069 'pv_ext_acct_attr_version_id:'||pv_ext_acct_attr_version_id||pv_crlf||
2070 'pv_req_id:'||pv_req_id||pv_crlf||
2071 'pv_com_vs_id:'||pv_com_vs_id||pv_crlf||
2072 'pv_cc_vs_id:'||pv_cc_vs_id||pv_crlf||
2076 'pv_login_id:'||pv_login_id);
2073 'pv_req_id:'||pv_req_id||pv_crlf||
2074 'pv_pgm_id:'||pv_pgm_id||pv_crlf||
2075 'pv_pgm_app_id:'||pv_pgm_app_id||pv_crlf||
2077 IF pv_mapped_segs.count() = 5
2078 THEN
2079 FEM_ENGINES_PKG.Tech_Message
2080 ( p_severity => pc_log_level_procedure
2081 ,p_module => pc_module_name||c_func_name
2082 ,p_msg_text => 'Map Seg Info Contents'||pv_crlf||
2083 '-->pv_mapped_segs(1).application_column_name:'||pv_mapped_segs(1).application_column_name||pv_crlf||
2084 '-->pv_mapped_segs(1).vs_id:'||pv_mapped_segs(1).vs_id||pv_crlf||
2085 '-->pv_mapped_segs(1).table_validated_flag:'||pv_mapped_segs(1).table_validated_flag||pv_crlf||
2086 '-->pv_mapped_segs(1).table_name:'||pv_mapped_segs(1).table_name||pv_crlf||
2087 '-->pv_mapped_segs(1).id_col_name:'||pv_mapped_segs(1).id_col_name||pv_crlf||
2088 '-->pv_mapped_segs(1).val_col_name:'||pv_mapped_segs(1).val_col_name||pv_crlf||
2089 '-->pv_mapped_segs(1).compiled_attr_col_name:'||pv_mapped_segs(1).compiled_attr_col_name||pv_crlf||
2090 '-->pv_mapped_segs(1).meaning_col_name:'||pv_mapped_segs(1).meaning_col_name||pv_crlf||
2091 '-->pv_mapped_segs(1).where_clause:'||pv_mapped_segs(1).where_clause||pv_crlf||
2092 '-->pv_mapped_segs(1).dependent_value_set_flag:'||pv_mapped_segs(1).dependent_value_set_flag||pv_crlf||
2093 '-->pv_mapped_segs(1).dependent_segment_column:'||pv_mapped_segs(1).dependent_segment_column||pv_crlf||
2094 '-->pv_mapped_segs(1).dependent_vs_id:'||pv_mapped_segs(1).dependent_vs_id||pv_crlf||pv_crlf||
2095
2096 '-->pv_mapped_segs(2).application_column_name:'||pv_mapped_segs(2).application_column_name||pv_crlf||
2097 '-->pv_mapped_segs(2).vs_id:'||pv_mapped_segs(2).vs_id||pv_crlf||
2098 '-->pv_mapped_segs(2).table_validated_flag:'||pv_mapped_segs(2).table_validated_flag||pv_crlf||
2099 '-->pv_mapped_segs(2).table_name:'||pv_mapped_segs(2).table_name||pv_crlf||
2100 '-->pv_mapped_segs(2).id_col_name:'||pv_mapped_segs(2).id_col_name||pv_crlf||
2101 '-->pv_mapped_segs(2).val_col_name:'||pv_mapped_segs(2).val_col_name||pv_crlf||
2102 '-->pv_mapped_segs(2).compiled_attr_col_name:'||pv_mapped_segs(2).compiled_attr_col_name||pv_crlf||
2103 '-->pv_mapped_segs(2).meaning_col_name:'||pv_mapped_segs(2).meaning_col_name||pv_crlf||
2104 '-->pv_mapped_segs(2).where_clause:'||pv_mapped_segs(2).where_clause||pv_crlf||
2105 '-->pv_mapped_segs(2).dependent_value_set_flag:'||pv_mapped_segs(2).dependent_value_set_flag||pv_crlf||
2106 '-->pv_mapped_segs(2).dependent_segment_column:'||pv_mapped_segs(2).dependent_segment_column||pv_crlf||
2107 '-->pv_mapped_segs(2).dependent_vs_id:'||pv_mapped_segs(2).dependent_vs_id||pv_crlf||pv_crlf||
2108
2109 '-->pv_mapped_segs(3).application_column_name:'||pv_mapped_segs(3).application_column_name||pv_crlf||
2110 '-->pv_mapped_segs(3).vs_id:'||pv_mapped_segs(3).vs_id||pv_crlf||
2111 '-->pv_mapped_segs(3).table_validated_flag:'||pv_mapped_segs(3).table_validated_flag||pv_crlf||
2112 '-->pv_mapped_segs(3).table_name:'||pv_mapped_segs(3).table_name||pv_crlf||
2113 '-->pv_mapped_segs(3).id_col_name:'||pv_mapped_segs(3).id_col_name||pv_crlf||
2114 '-->pv_mapped_segs(3).val_col_name:'||pv_mapped_segs(3).val_col_name||pv_crlf||
2115 '-->pv_mapped_segs(3).compiled_attr_col_name:'||pv_mapped_segs(3).compiled_attr_col_name||pv_crlf||
2116 '-->pv_mapped_segs(3).meaning_col_name:'||pv_mapped_segs(3).meaning_col_name||pv_crlf||
2117 '-->pv_mapped_segs(3).where_clause:'||pv_mapped_segs(3).where_clause||pv_crlf||
2118 '-->pv_mapped_segs(3).dependent_value_set_flag:'||pv_mapped_segs(3).dependent_value_set_flag||pv_crlf||
2119 '-->pv_mapped_segs(3).dependent_segment_column:'||pv_mapped_segs(3).dependent_segment_column||pv_crlf||
2120 '-->pv_mapped_segs(3).dependent_vs_id:'||pv_mapped_segs(3).dependent_vs_id||pv_crlf||pv_crlf||
2121
2122 '-->pv_mapped_segs(4).application_column_name:'||pv_mapped_segs(4).application_column_name||pv_crlf||
2123 '-->pv_mapped_segs(4).vs_id:'||pv_mapped_segs(4).vs_id||pv_crlf||
2124 '-->pv_mapped_segs(4).table_validated_flag:'||pv_mapped_segs(4).table_validated_flag||pv_crlf||
2125 '-->pv_mapped_segs(4).table_name:'||pv_mapped_segs(4).table_name||pv_crlf||
2126 '-->pv_mapped_segs(4).id_col_name:'||pv_mapped_segs(4).id_col_name||pv_crlf||
2127 '-->pv_mapped_segs(4).val_col_name:'||pv_mapped_segs(4).val_col_name||pv_crlf||
2128 '-->pv_mapped_segs(4).compiled_attr_col_name:'||pv_mapped_segs(4).compiled_attr_col_name||pv_crlf||
2129 '-->pv_mapped_segs(4).meaning_col_name:'||pv_mapped_segs(4).meaning_col_name||pv_crlf||
2130 '-->pv_mapped_segs(4).where_clause:'||pv_mapped_segs(4).where_clause||pv_crlf||
2131 '-->pv_mapped_segs(4).dependent_value_set_flag:'||pv_mapped_segs(4).dependent_value_set_flag||pv_crlf||
2132 '-->pv_mapped_segs(4).dependent_segment_column:'||pv_mapped_segs(4).dependent_segment_column||pv_crlf||
2133 '-->pv_mapped_segs(4).dependent_vs_id:'||pv_mapped_segs(4).dependent_vs_id||pv_crlf||pv_crlf||
2134
2135 '-->pv_mapped_segs(5).application_column_name:'||pv_mapped_segs(5).application_column_name||pv_crlf||
2136 '-->pv_mapped_segs(5).vs_id:'||pv_mapped_segs(5).vs_id||pv_crlf||
2137 '-->pv_mapped_segs(5).table_validated_flag:'||pv_mapped_segs(5).table_validated_flag||pv_crlf||
2138 '-->pv_mapped_segs(5).table_name:'||pv_mapped_segs(5).table_name||pv_crlf||
2139 '-->pv_mapped_segs(5).id_col_name:'||pv_mapped_segs(5).id_col_name||pv_crlf||
2143 '-->pv_mapped_segs(5).where_clause:'||pv_mapped_segs(5).where_clause||pv_crlf||
2140 '-->pv_mapped_segs(5).val_col_name:'||pv_mapped_segs(5).val_col_name||pv_crlf||
2141 '-->pv_mapped_segs(5).compiled_attr_col_name:'||pv_mapped_segs(5).compiled_attr_col_name||pv_crlf||
2142 '-->pv_mapped_segs(5).meaning_col_name:'||pv_mapped_segs(5).meaning_col_name||pv_crlf||
2144 '-->pv_mapped_segs(5).dependent_value_set_flag:'||pv_mapped_segs(5).dependent_value_set_flag||pv_crlf||
2145 '-->pv_mapped_segs(5).dependent_segment_column:'||pv_mapped_segs(5).dependent_segment_column||pv_crlf||
2146 '-->pv_mapped_segs(5).dependent_vs_id:'||pv_mapped_segs(5).dependent_vs_id||pv_crlf
2147 );
2148 END IF;
2149
2150 END;
2151
2152 -- ======================================================================
2153 -- Procedure
2154 -- UNDO_DIM_RULE
2155 -- Purpose
2156 -- This procedure in package FEM_INTG_DIM_RULE_ENG_PKG
2157 -- History
2158 -- 12-22-05 Gaurav Nayyar Created
2159 -- Arguments
2160 -- x_errbuf Standard Concurrent Program parameter
2161 -- x_retcode Standard Concurrent Program parameter
2162 -- p_dim_rule_obj_id Dimension rule object ID
2163 -- ======================================================================
2164
2165 PROCEDURE UNDO_DIM_RULE (x_errbuf OUT NOCOPY VARCHAR2,
2166 x_retcode OUT NOCOPY VARCHAR2,
2167 p_dim_rule_obj_id IN NUMBER)
2168 IS
2169
2170 l_func_name CONSTANT VARCHAR2(30) := '.UNDO_DIM_RULE';
2171 l_chart_of_accounts_id NUMBER;
2172 l_global_vs_combo_id NUMBER;
2173 l_obj_id NUMBER;
2174 l_obj_def_id NUMBER;
2175 l_intercomp_obj_id NUMBER;
2176 l_intercomp_obj_def_id NUMBER;
2177 l_dim_id Number;
2178 l_dim_mapping_Option_code VARCHAR2(32);
2179 l_request_id Number;
2180 l_memb_b_tab_name VARCHAR2(64);
2181 l_memb_tl_tab_name VARCHAR2(64);
2182 l_hier_tab_name VARCHAR2(64);
2183 l_attr_tab_name VARCHAR2(64);
2184 l_memb_col_name VARCHAR2(64);
2185 l_value_set_id NUMBER;
2186 comp_val_set_id NUMBER;
2187 cctr_val_set_id NUMBER;
2188 l_found_hier_flag BOOLEAN :=true;
2189 temp_query VARCHAR2(256);
2190 l_gvsc_exist_flag BOOLEAN :=true;
2191 temp_stmt VARCHAR2(256);
2192 dummy_val NUMBER;
2193 count1 NUMBER;
2194
2195 TYPE refcursor IS REF CURSOR;
2196 exec_cur refcursor;
2197
2198 Cursor BalRuleExec(c_charts_of_account_id NUMBER) IS
2199 SELECT 1 FROM fem_pl_object_executions
2200 WHERE object_id
2201 IN (SELECT BAL_RULE_OBJ_ID
2202 FROM fem_intg_bal_rules
2203 WHERE chart_of_accounts_id =c_charts_of_account_id );
2204
2205 Cursor FemDataExists(c_gvsc_id NUMBER) IS
2206 SELECT 1
2207 FROM fem_data_locations fdl,
2208 fem_ledgers_attr fla,
2209 fem_dim_attributes_b fdab,
2210 fem_dim_attr_versions_b fdavb
2211 WHERE fdab.attribute_varchar_label = 'GLOBAL_VS_COMBO'
2212 AND fdavb.attribute_id = fdab.attribute_id
2213 AND fdavb.default_version_flag = 'Y'
2214 AND fla.attribute_id = fdab.attribute_id
2215 AND fla.version_id = fdavb.version_id
2216 AND fla.dim_attribute_numeric_member = c_gvsc_id
2217 AND fdl.ledger_id = fla.ledger_id
2218 AND fdl.table_name <> 'FEM_BALANCES';
2219
2220 Cursor GVSCExistsForFemValueSet(c_gvsc_id Number,
2221 c_dim_id Number,
2222 c_val_set_id Number)
2223 IS
2224 select 1 from fem_global_vs_combo_defs
2225 where global_vs_combo_id <> c_gvsc_id
2226 and dimension_id = c_dim_id
2227 and value_set_id = c_val_set_id ;
2228
2229 BEGIN
2230
2231 FEM_ENGINES_PKG.Tech_Message
2232 (p_severity => pc_log_level_procedure,
2233 p_module => pc_module_name || l_func_name,
2234 p_app_name => 'FEM',
2235 p_msg_name => 'FEM_GL_POST_201',
2236 p_token1 => 'FUNC_NAME',
2237 p_value1 => 'FEM_INTG_DIM_RULE_ENG_PKG.UNDO_DIM_RULE',
2238 p_token2 => 'TIME',
2239 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2240
2241 -- retrieving the chart of accounts,dimension id for dimension rule
2242 SELECT chart_of_accounts_id,
2243 dimension_id
2244 INTO l_chart_of_accounts_id,
2245 l_dim_id
2246 FROM fem_intg_dim_rules
2247 WHERE dim_rule_obj_id = p_dim_rule_obj_id;
2248
2249 -- retrieving the global value set combination id for chart of accounts
2250
2251 SELECT GLOBAL_VS_COMBO_ID
2252 INTO l_global_vs_combo_id
2253 FROM fem_intg_coa_gvsc_map
2254 WHERE chart_of_accounts_id = l_chart_of_accounts_id;
2255
2256 -- get the object definition id for the dimension rule object id passed in
2257
2258 SELECT object_definition_id
2259 INTO l_obj_def_id
2260 FROM fem_object_definition_b
2261 WHERE object_id = p_dim_rule_obj_id ;
2262
2263 FEM_ENGINES_PKG.Tech_Message
2264 (p_severity => pc_log_level_statement,
2265 p_module => pc_module_name || l_func_name,
2266 p_msg_text => 'l_chart_of_accounts_id:= ' || l_chart_of_accounts_id
2270
2267 ||' l_global_vs_combo_id := ' || l_global_vs_combo_id
2268 ||' object_id:= '|| p_dim_rule_obj_id
2269 ||' object_definition_id:= ' || l_obj_def_id);
2271
2272 -- getting the dimension mapping option
2273
2274 SELECT dim_mapping_Option_code
2275 INTO l_dim_mapping_Option_code
2276 FROM fem_intg_dim_rule_defs
2277 WHERE dim_rule_obj_def_id = l_obj_def_id;
2278
2279 FEM_ENGINES_PKG.Tech_Message
2280 (p_severity => pc_log_level_statement,
2281 p_module => pc_module_name || l_func_name,
2282 p_msg_text => 'l_dim_mapping_Option_code:'|| l_dim_mapping_Option_code);
2283
2284 -- getting the table and column name for the dimension
2285
2286 SELECT MEMBER_B_TABLE_NAME,
2287 MEMBER_TL_TABLE_NAME,
2288 HIERARCHY_TABLE_NAME,
2289 ATTRIBUTE_TABLE_NAME,
2290 MEMBER_COL
2291 INTO l_memb_b_tab_name,
2292 l_memb_tl_tab_name,
2293 l_hier_tab_name,
2294 l_attr_tab_name,
2295 l_memb_col_name
2296 FROM fem_xdim_dimensions
2297 WHERE dimension_id = l_dim_id;
2298
2299 FEM_ENGINES_PKG.Tech_Message
2300 (p_severity => pc_log_level_statement,
2301 p_module => pc_module_name || l_func_name,
2302 p_msg_text => 'dimension_id:' || l_dim_id ||
2303 'MEMBER_B_TABLE_NAME:=' || l_memb_b_tab_name ||
2304 'MEMBER_TL_TABLE_NAME:=' || l_memb_tl_tab_name ||
2305 'HIERARCHY_TABLE_NAME:=' || l_hier_tab_name ||
2306 'ATTRIBUTE_TABLE_NAME:=' || l_attr_tab_name ||
2307 'MEMBER_COL:=' || l_memb_col_name);
2308
2309 IF (l_dim_mapping_Option_code = 'SINGLESEG' OR l_dim_mapping_Option_code = 'MULTISEG') THEN
2310
2311 SELECT value_set_id
2312 INTO l_value_set_id
2313 FROM fem_global_vs_combo_defs
2314 WHERE global_vs_combo_Id = l_global_vs_combo_id
2315 and dimension_id = l_dim_id;
2316
2317 FEM_ENGINES_PKG.Tech_Message
2318 (p_severity => pc_log_level_statement,
2319 p_module => pc_module_name || l_func_name,
2320 p_msg_text => 'l_value_set_id:'|| l_value_set_id);
2321
2322 IF(l_dim_id = 8) THEN
2323
2324 SELECT value_set_id
2325 INTO comp_val_set_id
2326 FROM fem_global_vs_combo_defs
2327 WHERE dimension_id =112
2328 AND global_vs_combo_Id = l_global_vs_combo_id;
2329
2330 SELECT value_set_id
2331 INTO cctr_val_set_id
2332 FROM fem_global_vs_combo_defs
2333 WHERE dimension_id =113
2334 AND global_vs_combo_Id = l_global_vs_combo_id;
2335 END IF;
2336
2337 END IF;
2338
2339 -- For organization dimension get object_id and object_definition_id for intercompany dimension rule
2340 IF(l_dim_id = 8) THEN
2341
2342 SELECT odb.object_id,odb.object_definition_id
2343 INTO l_intercomp_obj_id,l_intercomp_obj_def_id
2344 FROM fem_object_definition_b odb,
2345 fem_intg_dim_rules idr
2346 WHERE odb.object_id = idr.dim_rule_obj_id
2347 And idr.chart_of_accounts_id = l_chart_of_accounts_id
2348 AND idr.dimension_id = 0;
2349
2350 FEM_ENGINES_PKG.Tech_Message
2351 (p_severity => pc_log_level_statement,
2352 p_module => pc_module_name || l_func_name,
2353 p_msg_text => 'l_intercomp_obj_id:'|| l_intercomp_obj_id ||
2354 'l_intercomp_obj_def_id:'|| l_intercomp_obj_def_id);
2355
2356 END IF;
2357
2358 -- if Balance rule executed for ledger then error
2359
2360 OPEN BalRuleExec(l_chart_of_accounts_id);
2361 FETCH BalRuleExec INTO count1;
2362 IF BalRuleExec%FOUND THEN
2363 close BalRuleExec;
2364 -- throw error out
2365 FEM_ENGINES_PKG.Tech_Message
2366 (p_severity => pc_log_level_unexpected,
2367 p_module => pc_module_name || l_func_name ,
2368 p_msg_name => 'FEM_INTG_DIM_RULE_POST_05');
2369
2370 x_retcode:=2;
2371 x_errbuf:=FND_MESSAGE.Get_String('FEM', 'FEM_INTG_DIM_RULE_POST_05');
2372
2373 FEM_ENGINES_PKG.User_Message
2374 (p_app_name => 'FEM',
2375 p_msg_text => x_errbuf);
2376
2377 FEM_ENGINES_PKG.Tech_Message
2378 (p_severity => pc_log_level_procedure,
2379 p_module => pc_module_name || l_func_name,
2380 p_app_name => 'FEM',
2381 p_msg_name => 'FEM_GL_POST_203',
2382 p_token1 => 'FUNC_NAME',
2383 p_value1 => 'FEM_INTG_DIM_RULE_ENG_PKG.UNDO_DIM_RULE',
2384 p_token2 => 'TIME',
2385 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2386
2387 return;
2388 ELSE
2389 close BalRuleExec;
2390 END IF;
2391
2392
2393 OPEN FemDataExists(l_global_vs_combo_id);
2394 FETCH FemDataExists INTO count1;
2395 IF FemDataExists%FOUND THEN
2396 close FemDataExists;
2397 -- throw error out
2398 FEM_ENGINES_PKG.Tech_Message
2399 (p_severity => pc_log_level_unexpected,
2400 p_module => pc_module_name || l_func_name ,
2401 p_msg_name => 'FEM_INTG_DIM_RULE_POST_03');
2402
2403 x_retcode:=2;
2404 x_errbuf:=FND_MESSAGE.Get_String('FEM', 'FEM_INTG_DIM_RULE_POST_03');
2405
2406 FEM_ENGINES_PKG.User_Message
2410 FEM_ENGINES_PKG.Tech_Message
2407 (p_app_name => 'FEM',
2408 p_msg_text => x_errbuf);
2409
2411 (p_severity => pc_log_level_procedure,
2412 p_module => pc_module_name || l_func_name,
2413 p_app_name => 'FEM',
2414 p_msg_name => 'FEM_GL_POST_203',
2415 p_token1 => 'FUNC_NAME',
2416 p_value1 => 'FEM_INTG_DIM_RULE_ENG_PKG.UNDO_DIM_RULE',
2417 p_token2 => 'TIME',
2418 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2419
2420 return;
2421 ELSE
2422 close FemDataExists;
2423 END IF;
2424
2425
2426
2427
2428 FEM_ENGINES_PKG.User_Message
2429 (p_app_name => 'FEM',
2430 p_msg_name => 'FEM_INTG_DIM_RULE_POST_01');
2431
2432 -- getting the max reuest ID
2433
2434 SELECT max(o.request_id)
2435 INTO l_request_id
2436 FROM fem_pl_object_executions o
2437 WHERE o.display_flag = 'Y'
2438 AND o.object_id = p_dim_rule_obj_id;
2439
2440 FEM_ENGINES_PKG.Tech_Message
2441 (p_severity => pc_log_level_statement,
2442 p_module => pc_module_name || l_func_name,
2443 p_msg_text => 'Most recent request Id ' || l_request_id || ' for object id ' || p_dim_rule_obj_id);
2444
2445
2446 -- Starting the concurrent program FEM_UNDO_OBJ_EXEC
2447
2448 l_request_id := fnd_request.submit_request
2449 (application => 'FEM',
2450 program => 'FEM_UNDO_OBJ_EXEC',
2451 sub_request => FALSE,
2452 argument1 => to_char(p_dim_rule_obj_id),
2453 argument2 => to_char(l_request_id),
2454 argument3 => 1100,
2455 argument4 => 'Y',
2456 argument5 => 'N');
2457
2458 FEM_ENGINES_PKG.User_Message
2459 (p_app_name => 'FEM',
2460 p_msg_name => 'FEM_INTG_DIM_RULE_POST_02',
2461 p_token1 => 'DIM_RULE_OBJ_ID',
2462 p_value1 => p_dim_rule_obj_id);
2463
2464 FEM_ENGINES_PKG.Tech_Message
2465 (p_severity => pc_log_level_statement,
2466 p_module => pc_module_name || l_func_name,
2467 p_msg_name => 'FEM_INTG_DIM_RULE_POST_02',
2468 p_token1 => 'DIM_RULE_OBJ_ID',
2469 p_value1 => p_dim_rule_obj_id);
2470
2471 -- Updating the FEM_INTG_OGL_CCID_MAP
2472
2473 IF (l_dim_id <> 29) THEN
2474
2475 FEM_ENGINES_PKG.Tech_Message
2476 (p_severity => pc_log_level_statement,
2477 p_module => pc_module_name || l_func_name,
2478 p_msg_text => 'Updating FEM_INTG_OGL_CCID_MAP setting ' || l_memb_col_name || ' to -1');
2479
2480 temp_stmt:= 'UPDATE fem_intg_ogl_ccid_map
2481 SET ' || l_memb_col_name ||' =-1 ' ;
2482
2483 IF (l_dim_id = 8) THEN
2484 temp_stmt := temp_stmt || ' ,INTERCOMPANY_ID=-1 ';
2485 END IF;
2486
2487 IF (l_dim_id = 2) THEN
2488 temp_stmt := temp_stmt || ' ,EXTENDED_ACCOUNT_TYPE=-1 ';
2489 END IF;
2490
2491 temp_stmt := temp_stmt || ' WHERE GLOBAL_VS_COMBO_ID =:1 ' ;
2492
2493 FEM_ENGINES_PKG.Tech_Message
2494 (p_severity => pc_log_level_statement,
2495 p_module => pc_module_name || l_func_name,
2496 p_app_name => 'FEM',
2497 p_msg_name => 'FEM_GL_POST_204',
2498 p_token1 => 'VAR_NAME',
2499 p_value1 => 'temp_stmt',
2500 p_token2 => 'VAR_VAL',
2501 p_value2 => temp_stmt);
2502
2503 EXECUTE IMMEDIATE temp_stmt
2504 USING l_global_vs_combo_id;
2505
2506 END IF;
2507
2508 -- Updating the fem_global_vs_combo_defs
2509
2510 FEM_ENGINES_PKG.Tech_Message
2511 (p_severity => pc_log_level_statement,
2515 IF (l_dim_id = 8) THEN
2512 p_module => pc_module_name || l_func_name,
2513 p_msg_text => 'Updating fem_global_vs_combo_defs setting value_set_id to -1 ');
2514
2516 UPDATE fem_global_vs_combo_defs
2517 SET value_set_id = -1
2518 WHERE global_vs_combo_id = l_global_vs_combo_id
2519 AND dimension_id in (8,112,113);
2520 ELSE
2521 UPDATE fem_global_vs_combo_defs
2522 SET value_set_id = -1
2523 WHERE global_vs_combo_id = l_global_vs_combo_id
2524 AND dimension_id = l_dim_id;
2525 END IF;
2526
2527 -- updating the dimension rule defs.
2528
2529 FEM_ENGINES_PKG.Tech_Message
2530 (p_severity => pc_log_level_statement,
2531 p_module => pc_module_name || l_func_name,
2532 p_msg_text => 'Updating fem_intg_dim_rule_defs setting MAX_CCID_PROCESSED,MAX_FLEX_VALUE_ID_PROCESSED,FEM_VALUE_SET_ID to null ');
2533
2534 UPDATE fem_intg_dim_rule_defs
2535 SET MAX_CCID_PROCESSED = null,
2536 MAX_FLEX_VALUE_ID_PROCESSED = null,
2537 FEM_VALUE_SET_ID = null
2538 WHERE DIM_RULE_OBJ_DEF_ID = l_obj_def_id;
2539
2540 -- set intercompany dimension rule definition values if dimension is organization
2541
2542 IF(l_dim_id = 8) THEN
2543 FEM_ENGINES_PKG.Tech_Message
2544 (p_severity => pc_log_level_statement,
2545 p_module => pc_module_name || l_func_name,
2546 p_msg_text => 'Updating fem_intg_dim_rule_defs setting DEFAULT_MEMBER_ID,DEFAULT_MEMBER_VALUE_SET_ID to null for intercompany dimension');
2547
2548 UPDATE fem_intg_dim_rule_defs
2549 SET MAX_CCID_PROCESSED = null,
2550 MAX_FLEX_VALUE_ID_PROCESSED = null,
2551 FEM_VALUE_SET_ID = null,
2552 DEFAULT_MEMBER_ID = null,
2553 DEFAULT_MEMBER_VALUE_SET_ID = null
2554 WHERE DIM_RULE_OBJ_DEF_ID = l_intercomp_obj_def_id;
2555
2556 END IF;
2557
2558 x_retcode:=0;
2559
2560 IF (l_dim_mapping_Option_code = 'SINGLESEG' OR l_dim_mapping_Option_code = 'MULTISEG') THEN
2561
2562 -- no other global value set combination exists
2563 -- that uses this fem value set
2564
2565
2566 OPEN GVSCExistsForFemValueSet(l_global_vs_combo_id,l_dim_id,l_value_set_id);
2567 FETCH GVSCExistsForFemValueSet INTO dummy_val;
2568 IF GVSCExistsForFemValueSet%NOTFOUND THEN
2569 close GVSCExistsForFemValueSet;
2570 l_gvsc_exist_flag := false;
2571 FEM_ENGINES_PKG.Tech_Message
2572 (p_severity => pc_log_level_statement,
2573 p_module => pc_module_name || l_func_name,
2574 p_msg_text => 'NO global value set combination exists for value set:= ' || l_value_set_id);
2575 ELSE
2576 close GVSCExistsForFemValueSet;
2577 END IF;
2578
2579
2580 -- no hierarchy exists that uses this fem value set
2581
2582 temp_query := 'SELECT 1 from ' || l_hier_tab_name ||
2583 ' WHERE parent_value_set_id =:1 OR child_value_set_id =:2 ';
2584
2585 OPEN exec_cur FOR temp_query using l_value_set_id,l_value_set_id;
2586
2587
2588 FETCH exec_cur INTO dummy_val;
2589 IF exec_cur%NOTFOUND THEN
2590 close exec_cur;
2591 l_found_hier_flag := false;
2592
2593 FEM_ENGINES_PKG.Tech_Message
2594 (p_severity => pc_log_level_statement,
2595 p_module => pc_module_name || l_func_name,
2596 p_msg_text => 'no hierarchy exists that uses this fem value set');
2597 ELSE
2598 close exec_cur;
2599 END IF;
2600
2601 IF(NOT l_gvsc_exist_flag AND NOT l_found_hier_flag) THEN
2602
2603 temp_query := 'Delete from ' || l_memb_b_tab_name ||
2604 ' where value_set_id =:1 ';
2605
2606 FEM_ENGINES_PKG.Tech_Message
2607 (p_severity => pc_log_level_statement,
2608 p_module => pc_module_name || l_func_name,
2609 p_app_name => 'FEM',
2610 p_msg_name => 'FEM_GL_POST_204',
2611 p_token1 => 'VAR_NAME',
2612 p_value1 => 'temp_query',
2613 p_token2 => 'VAR_VAL',
2614 p_value2 => temp_query);
2615
2616 EXECUTE IMMEDIATE temp_query using l_value_set_id;
2617
2618 temp_query := 'Delete from ' || l_memb_tl_tab_name ||
2619 ' where value_set_id =:1 ';
2620
2621 FEM_ENGINES_PKG.Tech_Message
2622 (p_severity => pc_log_level_statement,
2623 p_module => pc_module_name || l_func_name,
2624 p_app_name => 'FEM',
2625 p_msg_name => 'FEM_GL_POST_204',
2626 p_token1 => 'VAR_NAME',
2627 p_value1 => 'temp_query',
2628 p_token2 => 'VAR_VAL',
2629 p_value2 => temp_query);
2630
2631 EXECUTE IMMEDIATE temp_query using l_value_set_id;
2632
2633 temp_query := 'Delete from ' || l_attr_tab_name ||
2634 ' where value_set_id =:1 ';
2635
2636 FEM_ENGINES_PKG.Tech_Message
2637 (p_severity => pc_log_level_statement,
2638 p_module => pc_module_name || l_func_name,
2639 p_app_name => 'FEM',
2640 p_msg_name => 'FEM_GL_POST_204',
2641 p_token1 => 'VAR_NAME',
2642 p_value1 => 'temp_query',
2643 p_token2 => 'VAR_VAL',
2644 p_value2 => temp_query);
2645
2646 EXECUTE IMMEDIATE temp_query using l_value_set_id;
2647
2648 -- removing the rows for value sets
2649 FEM_ENGINES_PKG.Tech_Message
2650 (p_severity => pc_log_level_statement,
2651 p_module => pc_module_name || l_func_name ,
2652 p_msg_text => 'removing the rows for value sets');
2653
2654 delete from fem_value_sets_b where value_set_id = l_value_set_id;
2655 delete from fem_value_sets_tl where value_set_id = l_value_set_id;
2656
2657 IF (l_dim_id = 8) THEN
2658
2659 -- retriveing company value set_id for the retrieved global_value_set_id
2660
2661 IF (comp_val_set_id<>-1) THEN
2662 FEM_ENGINES_PKG.Tech_Message
2663 (p_severity => pc_log_level_statement,
2664 p_module => pc_module_name || l_func_name ,
2665 p_msg_text => 'deleting the company value sets');
2666
2667 DELETE FROM FEM_COMPANIES_B WHERE value_set_id = comp_val_set_id;
2668 DELETE FROM FEM_COMPANIES_TL WHERE value_set_id = comp_val_set_id;
2669 DELETE FROM FEM_COMPANIES_ATTR WHERE value_set_id = comp_val_set_id;
2670
2671 -- deleting the company value sets
2672
2673 delete from fem_value_sets_b where value_set_id = comp_val_set_id;
2674
2675 END IF;
2676
2677 -- retriveing cost center value set_id for the retrieved global_value_set_id
2678
2679 IF (cctr_val_set_id<>-1) THEN
2680 FEM_ENGINES_PKG.Tech_Message
2681 (p_severity => pc_log_level_statement,
2682 p_module => pc_module_name || l_func_name ,
2683 p_msg_text => 'deleting the cost center value sets');
2684
2685 DELETE FROM FEM_COST_CENTERS_B WHERE value_set_id = cctr_val_set_id;
2686 DELETE FROM FEM_COST_CENTERS_TL WHERE value_set_id = cctr_val_set_id;
2687 DELETE FROM FEM_COST_CENTERS_ATTR WHERE value_set_id = cctr_val_set_id;
2688
2689 -- deleting the cost center value sets
2690 delete from fem_value_sets_b where value_set_id = cctr_val_set_id;
2691
2692 END IF;
2693
2694 END IF;
2695
2696 -- remove row from fem_intg_aol_valset_map
2697 FEM_ENGINES_PKG.Tech_Message
2698 (p_severity => pc_log_level_statement,
2699 p_module => pc_module_name || l_func_name ,
2700 p_msg_text => 'removing row from fem_intg_aol_valset_map');
2701
2702 delete from fem_intg_aol_valset_map where fem_value_set_id = l_value_set_id;
2703
2704 ELSE
2705 IF(l_gvsc_exist_flag) THEN
2706 x_errbuf:= FND_MESSAGE.Get_String('FEM', 'FEM_INTG_DIM_RULE_POST_03');
2707 ELSIF (l_found_hier_flag) THEN
2708 x_errbuf:= FND_MESSAGE.Get_String('FEM', 'FEM_INTG_DIM_RULE_POST_04');
2709 END IF;
2710
2711 FEM_ENGINES_PKG.Tech_Message
2712 (p_severity => pc_log_level_exception,
2713 p_module => pc_module_name || l_func_name ,
2714 p_msg_text => x_errbuf);
2715
2716 FEM_ENGINES_PKG.User_Message
2717 (p_app_name => 'FEM',
2718 p_msg_text => x_errbuf);
2719
2720 x_retcode:=1;
2721
2722 END IF;
2723
2724 END IF;
2725
2726 FEM_ENGINES_PKG.Tech_Message
2727 (p_severity => pc_log_level_procedure,
2728 p_module => pc_module_name || l_func_name ,
2729 p_msg_name => 'FEM_GL_POST_202',
2730 p_token1 => 'FUNC_NAME',
2731 p_value1 => 'FEM_INTG_DIM_RULE_ENG_PKG.UNDO_DIM_RULE',
2732 p_token2 => 'TIME',
2733 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2734
2735 EXCEPTION
2736
2737 WHEN OTHERS THEN
2738 ROLLBACK;
2739
2740 x_retcode:=2;
2741 x_errbuf:=SQLERRM;
2742
2743 FEM_ENGINES_PKG.Tech_Message
2744 (p_severity => pc_log_level_exception,
2745 p_module => pc_module_name || l_func_name,
2746 p_app_name => 'FEM',
2747 p_msg_name => 'FEM_GL_POST_215',
2748 p_token1 => 'ERR_MSG',
2749 p_value1 => SQLERRM);
2750
2751 FEM_ENGINES_PKG.User_Message
2752 (p_app_name => 'FEM',
2753 p_msg_name => 'FEM_GL_POST_215',
2754 p_token1 => 'ERR_MSG',
2755 p_value1 => SQLERRM);
2756
2757 FEM_ENGINES_PKG.Tech_Message
2758 (p_severity => pc_log_level_procedure,
2759 p_module => pc_module_name || l_func_name,
2760 p_app_name => 'FEM',
2761 p_msg_name => 'FEM_GL_POST_203',
2762 p_token1 => 'FUNC_NAME',
2763 p_value1 => 'FEM_INTG_DIM_RULE_ENG_PKG.UNDO_DIM_RULE',
2764 p_token2 => 'TIME',
2765 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2766
2767
2768 END UNDO_DIM_RULE;
2769
2770 END;