[Home] [Help]
PACKAGE BODY: APPS.FEM_INTG_NEW_DIM_MEMBER_PKG
Source
1 PACKAGE BODY fem_intg_new_dim_member_pkg AS
2 /* $Header: fem_intg_dimmemb.plb 120.15 2007/01/27 01:48:20 mikeward noship $ */
3 pc_log_level_statement CONSTANT NUMBER := FND_LOG.level_statement;
4 pc_log_level_procedure CONSTANT NUMBER := FND_LOG.level_procedure;
5 pc_log_level_event CONSTANT NUMBER := FND_LOG.level_event;
6 pc_log_level_exception CONSTANT NUMBER := FND_LOG.level_exception;
7 pc_log_level_error CONSTANT NUMBER := FND_LOG.level_error;
8 pc_log_level_unexpected CONSTANT NUMBER := FND_LOG.level_unexpected;
9 pc_module_name CONSTANT VARCHAR2(100)
10 := 'fem.plsql.fem_intg_new_dim_member_pkg';
11 pc_loop_counter_max CONSTANT NUMBER := 50;
12 pc_sleep_second CONSTANT NUMBER := 10;
13 pc_lock_timeout CONSTANT INTEGER := 1;
14 pc_lockmode CONSTANT INTEGER := 6;
15 pc_expiration_secs CONSTANT INTEGER := 6;
16 pc_release_on_commit CONSTANT BOOLEAN := TRUE;
17
18 pv_progress VARCHAR2(100);
19 pv_crlf CONSTANT VARCHAR2(1) := '
20 ';
21
22 pv_user_id CONSTANT NUMBER := FND_GLOBAL.User_Id;
23 pv_login_id CONSTANT NUMBER := FND_GLOBAL.Login_Id;
24
25 pv_dim_id NUMBER;
26 pv_dim_vs_id NUMBER;
27
28
29 -- start bug fix 5377544
30 pv_batch_size CONSTANT NUMBER := 10000;
31 --PROCEDURE Check_All_CCIDS_Mapped(x_result OUT NOCOPY VARCHAR2);
32 -- end bug fix 5377544
33
34
35 /* ======================================================================
36 Procedure
37 Populate_Dimension_Attribute
38 Purpose
39 This routine populates dimension attributes. It constructs MERGE
40 statements dynamically based on various factors, e.g. mapping method,
41 a type associated value set, dimension, and etc.
42
43 The following is a sample dynamic MERGE statement for Natural Account
44 dimension, Single Detail level segment, Independent value set:
45
46 MERGE INTO FEM_NAT_ACCTS_ATTR ATTR
47 USING (
48 SELECT
49 A.ATTRIBUTE_ID,
50 AV.VERSION_ID,
51 M.NATURAL_ACCOUNT_ID,
52 :pv_fem_vs_id VALUE_SET_ID,
53 NULL DIM_ATTRIBUTE_VALUE_SET_ID,
54 DECODE(
55 A.ATTRIBUTE_VARCHAR_LABEL,
56 'SOURCE_SYSTEM_CODE', :pv_source_system_code_id,
57 NULL
58 ) DIM_ATTRIBUTE_NUMERIC_MEMBER,
59 DECODE(
60 A.ATTRIBUTE_VARCHAR_LABEL,
61 'EXTENDED_ACCOUNT_TYPE',
62 DECODE(
63 SUBSTR(
64 FND_GLOBAL.NEWLINE ||
65 V.COMPILED_VALUE_ATTRIBUTES ||
66 FND_GLOBAL.NEWLINE,
67 INSTR(
68 FND_GLOBAL.NEWLINE ||
69 V.COMPILED_VALUE_ATTRIBUTES ||
70 FND_GLOBAL.NEWLINE,
71 FND_GLOBAL.NEWLINE,
72 1, :v_account_type_pos
73 )+1,
74 1
75 ),
76 'A', 'ASSET',
77 'E', 'EXPENSE',
78 'R', 'REVENUE',
79 'L', 'LIABILITY',
80 'O', 'EQUITY'
81 ),
82 'BUDGET_ALLOWED_FLAG',
83 SUBSTR(
84 FND_GLOBAL.NEWLINE ||
85 V.COMPILED_VALUE_ATTRIBUTES ||
86 FND_GLOBAL.NEWLINE,
87 INSTR(
88 FND_GLOBAL.NEWLINE ||
89 V.COMPILED_VALUE_ATTRIBUTES ||
90 FND_GLOBAL.NEWLINE,
91 FND_GLOBAL.NEWLINE,
92 1, :v_budget_pos
93 )+1,
94 1
95 ),
96 'NAT_ACCT_EXPENSE_TYPE_CODE', 'FIXED',
97 'INVENTORIABLE_FLAG', 'N',
98 'RECON_LEAF_NODE_FLAG', :v_leaf_flag,
99 NULL
100 ) DIM_ATTRIBUTE_VARCHAR_MEMBER,
101
102 1 OBJECT_VERSION_NUMBER,
103 'N' AW_SNAPSHOT_FLAG,
104 'Y' READ_ONLY_FLAG,
105 :b_sysdate CREATION_DATE,
106 :pv_user_id CREATED_BY,
107 :b_sysdate LAST_UPDATE_DATE,
108 :pv_user_id LAST_UPDATED_BY,
109 :pv_login_id LAST_UPDATE_LOGIN
110 FROM
111 FEM_NAT_ACCTS_B M,
112 FND_FLEX_VALUES V,
113 FEM_DIM_ATTRIBUTES_B A,
114 FEM_DIM_ATTR_VERSIONS_B AV
115 WHERE
116 M.VALUE_SET_ID = :b_driving_where_vs_id ||
117 :b_m_vs_id || :b_gt_dim_id AND
118 V.FLEX_VALUE_SET_ID = :b_flex_value_where_vs_id1 AND
119 V.FLEX_VALUE = M.NATURAL_ACCOUNT_DISPLAY_CODE ||
120 :b_flex_value_where_vs_id2 AND
121 A.DIMENSION_ID = :b_a_dim_id AND
122 AV.ATTRIBUTE_ID = A.ATTRIBUTE_ID AND
123 AV.DEFAULT_VERSION_FLAG = 'Y' || :b_pv_gvsc_id || :b_pv_dim_id AND
124 A.ATTRIBUTE_VARCHAR_LABEL IN (
125 'SOURCE_SYSTEM_CODE',
126 'EXTENDED_ACCOUNT_TYPE',
127 'BUDGET_ALLOWED_FLAG',
128 'NAT_ACCT_EXPENSE_TYPE_CODE',
129 'INVENTORIABLE_FLAG',
130 'RECON_LEAF_NODE_FLAG'
131 )
132 ) S
133 ON (
134 ATTR.ATTRIBUTE_ID = S.ATTRIBUTE_ID AND
135 ATTR.VERSION_ID = S.VERSION_ID AND
136 ATTR.NATURAL_ACCOUNT_ID = S.NATURAL_ACCOUNT_ID AND
137 ATTR.VALUE_SET_ID = S.VALUE_SET_ID
138 )
139 WHEN MATCHED THEN UPDATE
140 SET ATTR.LAST_UPDATE_DATE = SYSDATE
141 WHEN NOT MATCHED THEN INSERT (
142 ATTR.ATTRIBUTE_ID,
143 ATTR.VERSION_ID,
144 ATTR.NATURAL_ACCOUNT_ID,
145 ATTR.VALUE_SET_ID,
146 ATTR.DIM_ATTRIBUTE_VALUE_SET_ID,
147 ATTR.DIM_ATTRIBUTE_NUMERIC_MEMBER,
148 ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER,
149 ATTR.OBJECT_VERSION_NUMBER,
150 ATTR.AW_SNAPSHOT_FLAG,
151 ATTR.READ_ONLY_FLAG,
152 ATTR.CREATION_DATE,
153 ATTR.CREATED_BY,
154 ATTR.LAST_UPDATE_DATE,
155 ATTR.LAST_UPDATED_BY,
156 ATTR.LAST_UPDATE_LOGIN
157 ) VALUES (
158 S.ATTRIBUTE_ID,
159 S.VERSION_ID,
160 S.NATURAL_ACCOUNT_ID,
161 S.VALUE_SET_ID,
162 S.DIM_ATTRIBUTE_VALUE_SET_ID,
163 S.DIM_ATTRIBUTE_NUMERIC_MEMBER,
164 S.DIM_ATTRIBUTE_VARCHAR_MEMBER,
165 S.OBJECT_VERSION_NUMBER,
166 S.AW_SNAPSHOT_FLAG,
167 S.READ_ONLY_FLAG,
168 S.CREATION_DATE,
169 S.CREATED_BY,
170 S.LAST_UPDATE_DATE,
171 S.LAST_UPDATED_BY,
172 S.LAST_UPDATE_LOGIN
173 )
174 USING pv_fem_vs_id, 10, v_account_type_pos, v_budget_pos, 'Y',
175 SYSDATE, pv_user_id, SYSDATE, pv_user_id, pv_login_id,
176 pv_fem_vs_id, NULL, NULL, pv_mapped_segs(1).vs_id, NULL,
177 pv_dim_id, NULL, NULL
178 ====================================================================== */
179 PROCEDURE Populate_Dimension_Attribute(
180 p_summary_flag IN VARCHAR,
181 x_completion_code OUT NOCOPY NUMBER,
182 x_row_count_tot OUT NOCOPY NUMBER
183 ) IS
184 v_module_name VARCHAR2(100);
185 v_func_name VARCHAR2(100);
186 v_attribute_num NUMBER;
187 v_leaf_flag VARCHAR2(1);
188
189 v_member_col VARCHAR2(30);
190
191 v_member_id_col_name VARCHAR2(200);
192 v_member_dc_col_name VARCHAR2(200);
193
194 v_decode_company VARCHAR2(200) := NULL;
195 v_decode_cost_center VARCHAR2(200) := NULL;
196 v_limit_attribute_company VARCHAR2(200) := NULL;
197 v_limit_attribute_cost_center VARCHAR2(200) := NULL;
198
199 v_attributes_where VARCHAR2(1000);
200 v_driving_from VARCHAR2(1000) := NULL;
201 v_driving_where VARCHAR2(1000) := NULL;
202 v_flex_value_from1 VARCHAR2(1000) := NULL;
203 v_flex_value_from2 VARCHAR2(1000) := NULL;
204 v_flex_value_where1 VARCHAR2(1000);
205 v_flex_value_where2 VARCHAR2(1000);
206 v_gvsc_from VARCHAR2(1000) := NULL;
207 v_gvsc_where VARCHAR2(1000);
208 v_from VARCHAR2(4000);
209 v_where VARCHAR2(4000);
210
211 v_account_type_pos NUMBER := NULL;
212 v_budget_pos NUMBER := NULL;
213
214 v_dim_attr_value_set_id VARCHAR2(1000);
215 v_dim_attr_numeric_member VARCHAR2(4000);
216 v_dim_attr_varchar_member1 VARCHAR2(4000);
217 v_dim_attr_varchar_member2 VARCHAR2(4000) := NULL;
218
219 v_stmt1 VARCHAR2(4000);
220 v_stmt2 VARCHAR2(4000);
221 v_stmt3 VARCHAR2(4000);
222 v_stmt4 VARCHAR2(4000);
223 v_stmt5 VARCHAR2(4000);
224 v_stmt6 VARCHAR2(4000);
225
226 b_driving_where_vs_id NUMBER := NULL;
227 b_m_vs_id NUMBER := NULL;
228 b_gt_dim_id NUMBER := NULL;
229 b_flex_value_where_vs_id1 NUMBER := NULL;
230 b_flex_value_where_vs_id2 NUMBER := NULL;
231 b_a_dim_id NUMBER;
232 b_gv_gvsc_id NUMBER := NULL;
233 b_gv_dim_id NUMBER := NULL;
234
235 FEM_INTG_DIM_RULE_fatal_err EXCEPTION;
236
237 --
238 -- Find a Natural Account Segment Qualifier position
239 --
240 CURSOR SegmentQualifierPosition(seg_num NUMBER, qualifier VARCHAR2) IS
241 SELECT POSITION
242 FROM (
243 SELECT ROWNUM POSITION, VALUE_ATTRIBUTE_TYPE
244 FROM (
245 SELECT VALUE_ATTRIBUTE_TYPE
246 FROM FND_FLEX_VALIDATION_QUALIFIERS
247 WHERE ID_FLEX_APPLICATION_ID = 101
248 AND ID_FLEX_CODE = 'GL#'
249 AND SEGMENT_ATTRIBUTE_TYPE IN ('GL_GLOBAL', 'GL_ACCOUNT')
250 AND FLEX_VALUE_SET_ID =
251 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(seg_num).vs_id
252 ORDER BY ASSIGNMENT_DATE, VALUE_ATTRIBUTE_TYPE
253 )
254 )
255 WHERE VALUE_ATTRIBUTE_TYPE = qualifier;
256
257 BEGIN
258
259 --piush_util.put_line('Entering fem_intg_dim.populate_dimension_attribute');
260
261 v_module_name := 'fem.plsql.fem_intg_dim.populate_dimension_attribute';
262 v_func_name := 'FEM_INTG_NEW_DIM_MEMBER_PKG.Populate_Dimension_Attribute';
263 v_flex_value_where1 := ' || :b_flex_value_where_vs_id1';
264 v_flex_value_where2 := ' || :b_flex_value_where_vs_id2';
265 v_gvsc_where := ' || :b_pv_gvsc_id || :b_pv_dim_id';
266 b_a_dim_id := FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
267
268 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'INTERCOMPANY' THEN
269 v_member_col := FEM_INTG_DIM_RULE_ENG_PKG.pv_cctr_org_member_col;
270 ELSE
271 v_member_col := FEM_INTG_DIM_RULE_ENG_PKG.pv_member_col;
272 END IF;
273
274 FEM_ENGINES_PKG.Tech_Message(
275 p_severity => pc_log_level_procedure,
276 p_module => v_module_name || '.begin',
277 p_app_name => 'FEM',
278 p_msg_name => 'FEM_GL_POST_201',
279 p_token1 => 'FUNC_NAME',
280 p_value1 => v_func_name,
281 p_token2 => 'TIME',
282 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
283 );
284
285 x_completion_code := 0;
286 x_row_count_tot := 0;
287
288 --
289 -- Find the number of dimension attributes
290 --
291 SELECT COUNT(ATTRIBUTE_ID)
292 INTO v_attribute_num
293 FROM FEM_DIM_ATTRIBUTES_B
294 WHERE DIMENSION_ID = FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
295
296 --piush_util.put_line('v_attribute_num = ' || v_attribute_num);
297
298 IF v_attribute_num <> 0 THEN
299
300 --piush_util.put_line('begin preparation 1 for attribute population');
301
302 FEM_ENGINES_PKG.Tech_Message(
303 p_severity => pc_log_level_statement,
304 p_module => v_module_name || '.begin_prep1_populate_attribute',
305 p_msg_text => 'begin preparation 1 for attribute population'
306 );
307
308 --
309 -- Find Segment Qualifier Positions
310 --
311 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'NATURAL_ACCOUNT' THEN
312
313 OPEN SegmentQualifierPosition(
314 FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num,
315 'GL_ACCOUNT_TYPE'
316 );
317 FETCH SegmentQualifierPosition INTO v_account_type_pos;
318 CLOSE SegmentQualifierPosition;
319
320 OPEN SegmentQualifierPosition(
321 FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num,
322 'DETAIL_BUDGETING_ALLOWED'
323 );
324 FETCH SegmentQualifierPosition INTO v_budget_pos;
325 CLOSE SegmentQualifierPosition;
326
327 FEM_ENGINES_PKG.Tech_Message(
328 p_severity => pc_log_level_statement,
329 p_module => v_module_name || '.segment_qualifier_positions',
330 p_msg_text => 'v_account_type_pos=' || v_account_type_pos || ', ' ||
331 'v_budget_pos=' || v_budget_pos
332 );
333
334 --piush_util.put_line('segment_qualifier_positions. v_account_type_pos=' || v_account_type_pos || ', ' || 'v_budget_pos=' || v_budget_pos);
335
336 ELSIF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'LINE_ITEM' THEN
337
338 OPEN SegmentQualifierPosition(
339 FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num,
340 'GL_ACCOUNT_TYPE'
341 );
342 FETCH SegmentQualifierPosition INTO v_account_type_pos;
343 CLOSE SegmentQualifierPosition;
344
345 FEM_ENGINES_PKG.Tech_Message(
346 p_severity => pc_log_level_statement,
347 p_module => v_module_name || '.segment_qualifier_positions',
348 p_msg_text => 'v_account_type_pos=' || v_account_type_pos
349 );
350
351 --piush_util.put_line('v_account_type_pos=' || v_account_type_pos);
352
353 END IF;
354
355 --
356 -- Set Dynamic SQL elements based on the mapping option and the value set type
357 --
358 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_mapping_option_code = 'SINGLESEG' AND
359 p_summary_flag = 'N' THEN
360 /*
361 Single Segment, Detail Level
362 */
363 v_leaf_flag := 'Y';
364
365 v_member_id_col_name := 'M.' || v_member_col;
366 v_member_dc_col_name := 'M.' || FEM_INTG_DIM_RULE_ENG_PKG.pv_member_display_code_col;
367
368 v_driving_from := '
369 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name || ' M,';
370
371 v_driving_where := '
372 M.VALUE_SET_ID = :b_driving_where_vs_id || :b_m_vs_id || :b_gt_dim_id';
373
374 b_driving_where_vs_id := FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id;
375
376 /*
377 Member id is used for COMPANY_COST_CENTER_ORG's attributes.
378 */
379 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'COMPANY_COST_CENTER_ORG' OR
380 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'INTERCOMPANY' THEN
381
382 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_balancing_segment_num = 1 THEN
383 /*
384 Join to Company member table to access member id
385 */
386 v_flex_value_from1 := '
387 FEM_COMPANIES_B COMPANY,';
388
389 v_flex_value_where1 := ' AND
390 COMPANY.VALUE_SET_ID = :b_flex_value_where_vs_id1 AND
391 COMPANY.COMPANY_DISPLAY_CODE = ' || v_member_dc_col_name;
392
393 b_flex_value_where_vs_id1 := FEM_INTG_DIM_RULE_ENG_PKG.pv_com_vs_id;
394
395 v_decode_company := '
396 ''COMPANY'', COMPANY.COMPANY_ID,';
397 v_limit_attribute_company := '
398 ''COMPANY'',';
399 ELSE
400 /*
401 Join to Cost Center member table to access member id
402 */
403 v_flex_value_from1 := '
404 FEM_COST_CENTERS_B COST_CENTER,';
405
406 v_flex_value_where1 := ' AND
407 COST_CENTER.VALUE_SET_ID = :b_flex_value_where_vs_id1 AND
408 COST_CENTER.COST_CENTER_DISPLAY_CODE = ' || v_member_dc_col_name;
409
410 b_flex_value_where_vs_id1 := FEM_INTG_DIM_RULE_ENG_PKG.pv_cc_vs_id;
411
412 v_decode_cost_center := '
413 ''COST_CENTER'', COST_CENTER.COST_CENTER_ID,';
414 v_limit_attribute_cost_center := '
415 ''COST_CENTER'',';
416
417 END IF;
418
419 /*
420 Join to FEM_GLOBAL_VS_COMBO_DEFS to get
421 DIM_ATTRIBUTE_VALUE_SET_ID.
422 */
423 v_gvsc_from := ',
424 FEM_GLOBAL_VS_COMBO_DEFS GV';
425
426 v_gvsc_where := ' AND
427 GV.GLOBAL_VS_COMBO_ID = :b_gv_gvsc_id AND
428 GV.DIMENSION_ID = DECODE(
429 A.ATTRIBUTE_VARCHAR_LABEL,
430 ''COMPANY'', A.ATTRIBUTE_DIMENSION_ID,
431 ''COST_CENTER'', A.ATTRIBUTE_DIMENSION_ID,
432 :b_gv_dim_id
433 )';
434
435 b_gv_gvsc_id := FEM_INTG_DIM_RULE_ENG_PKG.pv_gvsc_id;
436 b_gv_dim_id := FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
437
438 ELSIF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'NATURAL_ACCOUNT' OR
439 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'LINE_ITEM' THEN
440
441 /*
442 Join to Value Set table to get Segment Qualifiers.
443
444 */
445 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).table_validated_flag = 'N' THEN
446
447 v_flex_value_from1 := '
448 FND_FLEX_VALUES V,';
449
450 ELSE
451 v_flex_value_from1 := '
452 (SELECT' || '
453 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).vs_id || ' FLEX_VALUE_SET_ID,' || '
454 ' || NVL(FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).id_col_name, 'NULL') || ' FLEX_VALUE_ID,' || '
455 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).val_col_name || ' FLEX_VALUE,' || '
456 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).compiled_attr_col_name ||
457 ' COMPILED_VALUE_ATTRIBUTES
458 FROM' || '
459 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).table_name || '
460 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).where_clause || ') V,';
461
462 END IF;
463
464 v_flex_value_where1 := ' AND
465 V.FLEX_VALUE_SET_ID = :b_flex_value_where_vs_id1 AND
466 V.FLEX_VALUE = M.' || FEM_INTG_DIM_RULE_ENG_PKG.pv_member_display_code_col;
467
468 b_flex_value_where_vs_id1 := FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).vs_id;
469
470 END IF;
471
472 v_from := v_driving_from || v_flex_value_from1 || v_flex_value_from2 || '
473 FEM_DIM_ATTRIBUTES_B A,
474 FEM_DIM_ATTR_VERSIONS_B AV' || v_gvsc_from;
475
476 v_where := v_driving_where || v_flex_value_where1 || v_flex_value_where2 || ' AND
477 A.DIMENSION_ID = :b_a_dim_id AND
478 AV.ATTRIBUTE_ID = A.ATTRIBUTE_ID AND
479 AV.DEFAULT_VERSION_FLAG = ''Y''' || v_gvsc_where;
480
481 ELSIF (FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_mapping_option_code = 'SINGLESEG' AND
482 p_summary_flag = 'Y') OR
483 (FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_mapping_option_code = 'MULTISEG' AND
484 p_summary_flag = 'N') THEN
485
486
487 --piush_util.put_line('Inside the ElseIf FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_mapping_option_code = MULTISEG');
488 /*
489 Notes
490 -----
491 - For Single Segment Summary level, there should not be no
492 Table Validated value set.
493
494 - For Multiple Segments and Single Segment Summary level,
495 GT table is a driving table.
496
497 - A support for Multiple Segments hierarchy (summary level) is to
498 be provided in a later release.
499 */
500
501 IF p_summary_flag = 'Y' THEN
502 v_leaf_flag := 'N';
503 ELSE
504 v_leaf_flag := 'Y';
505 END IF;
506
507 v_member_id_col_name := 'M.' || v_member_col;
508
509 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'COMPANY_COST_CENTER_ORG' OR
510 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'INTERCOMPANY' THEN
511
512 --piush_util.put_line('COMPANY_COST_CENTER_ORG or INTERCOMPANY');
513
514 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_mapping_option_code = 'MULTISEG' OR
515 FEM_INTG_DIM_RULE_ENG_PKG.pv_balancing_segment_num = 1 THEN
516 /*
517 Join to Company member table to access member id
518 */
519 v_flex_value_from1 := '
520 FEM_COMPANIES_B COMPANY,';
521
522 v_flex_value_where1 := ' AND
523 COMPANY.VALUE_SET_ID = :b_flex_value_where_vs_id1 AND
524 COMPANY.COMPANY_DISPLAY_CODE = GT.SEGMENT' ||
525 FEM_INTG_DIM_RULE_ENG_PKG.pv_balancing_segment_num || '_VALUE';
526
527 b_flex_value_where_vs_id1 := FEM_INTG_DIM_RULE_ENG_PKG.pv_com_vs_id;
528
529 v_decode_company := '
530 ''COMPANY'', COMPANY.COMPANY_ID,';
531 v_limit_attribute_company := '
532 ''COMPANY'',';
533
534 END IF;
535
536 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_mapping_option_code = 'MULTISEG' OR
537 FEM_INTG_DIM_RULE_ENG_PKG.pv_balancing_segment_num <> 1 THEN
538 /*
539 Join to Cost Center member table to access member id
540 */
541 v_flex_value_from2 := '
542 FEM_COST_CENTERS_B COST_CENTER,';
543
544 v_flex_value_where2 := ' AND
545 COST_CENTER.VALUE_SET_ID = :b_flex_value_where_vs_id2 AND
546 COST_CENTER.COST_CENTER_DISPLAY_CODE = GT.SEGMENT' ||
547 FEM_INTG_DIM_RULE_ENG_PKG.pv_cost_center_segment_num || '_VALUE';
548
549 b_flex_value_where_vs_id2 := FEM_INTG_DIM_RULE_ENG_PKG.pv_cc_vs_id;
550
551 v_decode_cost_center := '
552 ''COST_CENTER'', COST_CENTER.COST_CENTER_ID,';
553 v_limit_attribute_cost_center := '
554 ''COST_CENTER'',';
555
556 END IF;
557
558 v_gvsc_from := ',
559 FEM_GLOBAL_VS_COMBO_DEFS GV';
560 v_gvsc_where := ' AND
561 GV.GLOBAL_VS_COMBO_ID = :b_gv_gvsc_id AND
562 GV.DIMENSION_ID = DECODE(
563 A.ATTRIBUTE_VARCHAR_LABEL,
564 ''COMPANY'', A.ATTRIBUTE_DIMENSION_ID,
565 ''COST_CENTER'', A.ATTRIBUTE_DIMENSION_ID,
566 :b_gv_dim_id
567 )';
568
569 b_gv_gvsc_id := FEM_INTG_DIM_RULE_ENG_PKG.pv_gvsc_id;
570 b_gv_dim_id := FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
571
572 ELSIF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'NATURAL_ACCOUNT' OR
573 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'LINE_ITEM' THEN
574
575 /*
576 Join to Value Set table to access Segment Qualifiers
577 */
578 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).table_validated_flag = 'N' THEN
579 v_flex_value_from1 := '
580 FND_FLEX_VALUES V,';
581 ELSE
582 v_flex_value_from1 := '
583 (SELECT ' || '
584 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).vs_id || ' FLEX_VALUE_SET_ID,' || '
585 ' || NVL(FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).id_col_name, 'NULL') || ' FLEX_VALUE_ID,' || '
586 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).val_col_name || ' FLEX_VALUE,' || '
587 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).compiled_attr_col_name || ' COMPILED_VALUE_ATTRIBUTES
588 FROM ' || '
589 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).table_name || '
590 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).where_clause || ') V,';
591 END IF;
592
593 v_flex_value_where1 := ' AND
594 V.FLEX_VALUE_SET_ID = :b_flex_value_where_vs_id1 AND
595 V.FLEX_VALUE = GT.SEGMENT' || FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num || '_VALUE';
596
597 b_flex_value_where_vs_id1 := FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).vs_id;
598
599 END IF;
600
601 v_from := '
602 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name || ' M,
603 FEM_INTG_DIM_MEMBERS_GT GT,' || v_flex_value_from1 || v_flex_value_from2 || '
604 FEM_DIM_ATTRIBUTES_B A,
605 FEM_DIM_ATTR_VERSIONS_B AV' || v_gvsc_from;
606
607 v_where := '
608 M.VALUE_SET_ID = :b_driving_where_vs_id || :b_m_vs_id AND
609 GT.DIMENSION_ID = :b_gt_dim_id AND
610 GT.CONCAT_SEGMENT_VALUE = M.' || FEM_INTG_DIM_RULE_ENG_PKG.pv_member_display_code_col || v_flex_value_where1 || v_flex_value_where2 || ' AND
611 A.DIMENSION_ID = :b_a_dim_id AND
612 AV.ATTRIBUTE_ID = A.ATTRIBUTE_ID AND
613 AV.DEFAULT_VERSION_FLAG = ''Y''' || v_gvsc_where;
614
615 b_m_vs_id := FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id;
616 b_gt_dim_id := FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
617
618 END IF;
619
620 FEM_ENGINES_PKG.Tech_Message(
621 p_severity => pc_log_level_statement,
622 p_module => v_module_name || '.end_prep1_populate_attribute',
623 p_msg_text => 'end preparation 1 for attribute population'
624 );
625
626 --piush_util.put_line('end preparation 1 for attribute population');
627
628 v_dim_attr_value_set_id := 'NULL';
629 v_dim_attr_varchar_member2 := ' '; -- using a single space as NULL
630 -- will be replaced with a word NULL
631 -- by FND logging
632 --
633 -- Set Attributes
634 --
635 FEM_ENGINES_PKG.Tech_Message(
636 p_severity => pc_log_level_statement,
637 p_module => v_module_name || '.begin_prep2_populate_attribute',
638 p_msg_text => 'begin preparation 2 for attribute population'
639 );
640
641 --piush_util.put_line('begin preparation 2 for attribute population');
642
643 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'COMPANY_COST_CENTER_ORG' OR
644 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'INTERCOMPANY' THEN
645
646 v_dim_attr_value_set_id :=
647 'DECODE(
648 A.ATTRIBUTE_VARCHAR_LABEL,
649 ''COMPANY'', GV.VALUE_SET_ID,
650 ''COST_CENTER'', GV.VALUE_SET_ID,
651 NULL
652 )';
653
654 v_dim_attr_numeric_member :=
655 ' DECODE(
656 A.ATTRIBUTE_VARCHAR_LABEL,
657 ''SOURCE_SYSTEM_CODE'', :pv_source_system_code_id,' || v_decode_company || v_decode_cost_center || '
658 NULL
659 )';
660
661 v_dim_attr_varchar_member1 :=
662 ' DECODE(
663 A.ATTRIBUTE_VARCHAR_LABEL,
664 ''HIDDEN_FLAG'', ''N'',
665 ''RECON_LEAF_NODE_FLAG'', :v_account_type_pos || :v_budget_pos || :v_leaf_flag,
666 ''CCTR_ORG_TYPE'', ''OTHER'',
667 NULL
668 )';
669
670 /*
671 Limit attributes
672 */
673 v_attributes_where := ' AND
674 A.ATTRIBUTE_VARCHAR_LABEL IN (
675 ''SOURCE_SYSTEM_CODE'',' || v_limit_attribute_company || v_limit_attribute_cost_center || '
676 ''HIDDEN_FLAG'',
677 ''RECON_LEAF_NODE_FLAG'',
678 ''CCTR_ORG_TYPE''
679 )';
680
681 ELSIF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'NATURAL_ACCOUNT' THEN
682
683 v_dim_attr_numeric_member :=
684 ' DECODE(
685 A.ATTRIBUTE_VARCHAR_LABEL,
686 ''SOURCE_SYSTEM_CODE'', :pv_source_system_code_id,
687 NULL
688 )';
689
690 v_dim_attr_varchar_member1 :=
691 ' DECODE(
692 A.ATTRIBUTE_VARCHAR_LABEL,
693 ''EXTENDED_ACCOUNT_TYPE'',
694 DECODE(
695 SUBSTR(
696 FND_GLOBAL.NEWLINE ||
697 V.COMPILED_VALUE_ATTRIBUTES ||
698 FND_GLOBAL.NEWLINE,
699 INSTR(
700 FND_GLOBAL.NEWLINE ||
701 V.COMPILED_VALUE_ATTRIBUTES ||
702 FND_GLOBAL.NEWLINE,
703 FND_GLOBAL.NEWLINE,
704 1, :v_account_type_pos
705 )+1,
706 1
707 ),
708 ''A'', ''ASSET'',
709 ''E'', ''EXPENSE'',
710 ''R'', ''REVENUE'',
711 ''L'', ''LIABILITY'',
712 ''O'', ''EQUITY''
713 )';
714
715 v_dim_attr_varchar_member2 :=
716 ' ''BUDGET_ALLOWED_FLAG'',
717 SUBSTR(
718 FND_GLOBAL.NEWLINE ||
719 V.COMPILED_VALUE_ATTRIBUTES ||
720 FND_GLOBAL.NEWLINE,
721 INSTR(
722 FND_GLOBAL.NEWLINE ||
723 V.COMPILED_VALUE_ATTRIBUTES ||
724 FND_GLOBAL.NEWLINE,
725 FND_GLOBAL.NEWLINE,
726 1, :v_budget_pos
727 )+1,
728 1
729 ),
730 ''NAT_ACCT_EXPENSE_TYPE_CODE'', ''FIXED'',
731 ''INVENTORIABLE_FLAG'', ''N'',
732 ''RECON_LEAF_NODE_FLAG'', :v_leaf_flag,
733 NULL
734 )';
735
736 /*
737 Limit attributes
738 */
739 v_attributes_where := ' AND
740 A.ATTRIBUTE_VARCHAR_LABEL IN (
741 ''SOURCE_SYSTEM_CODE'',
742 ''EXTENDED_ACCOUNT_TYPE'',
743 ''BUDGET_ALLOWED_FLAG'',
744 ''NAT_ACCT_EXPENSE_TYPE_CODE'',
745 ''INVENTORIABLE_FLAG'',
746 ''RECON_LEAF_NODE_FLAG''
747 )';
748
749 ELSIF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'LINE_ITEM' THEN
750
751 v_dim_attr_numeric_member :=
752 ' DECODE(
753 A.ATTRIBUTE_VARCHAR_LABEL,
754 ''SOURCE_SYSTEM_CODE'', :pv_source_system_code_id,
755 NULL
756 )';
757
758 v_dim_attr_varchar_member1 :=
759 ' DECODE(
760 A.ATTRIBUTE_VARCHAR_LABEL,
761 ''EXTENDED_ACCOUNT_TYPE'',
762 DECODE(
763 SUBSTR(
764 FND_GLOBAL.NEWLINE ||
765 V.COMPILED_VALUE_ATTRIBUTES ||
766 FND_GLOBAL.NEWLINE,
767 INSTR(
768 FND_GLOBAL.NEWLINE ||
769 V.COMPILED_VALUE_ATTRIBUTES ||
770 FND_GLOBAL.NEWLINE,
771 FND_GLOBAL.NEWLINE,
772 1, :v_account_type_pos
773 )+1,
774 1
775 ),
776 ''A'', ''ASSET'',
777 ''E'', ''EXPENSE'',
778 ''R'', ''REVENUE'',
779 ''L'', ''LIABILITY'',
780 ''O'', ''EQUITY''
781 ),
782 ''BETTER_FLAG'', ''N'',
783 ''RECON_LEAF_NODE_FLAG'', :v_budget_pos || :v_leaf_flag,
784 NULL
785 )';
786
787 /*
788 Limit attributes
789 */
790 v_attributes_where := ' AND
791 A.ATTRIBUTE_VARCHAR_LABEL IN (
792 ''SOURCE_SYSTEM_CODE'',
793 ''EXTENDED_ACCOUNT_TYPE'',
794 ''BETTER_FLAG'',
795 ''RECON_LEAF_NODE_FLAG''
796 )';
797
798 ELSIF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'PRODUCT' THEN
799
800 v_dim_attr_numeric_member :=
801 ' DECODE(
802 A.ATTRIBUTE_VARCHAR_LABEL,
803 ''SOURCE_SYSTEM_CODE'', :pv_source_system_code_id,
804 NULL
805 )';
806
807 v_dim_attr_varchar_member1 :=
808 ' DECODE(
809 A.ATTRIBUTE_VARCHAR_LABEL,
810 ''PRODUCT_UOM'', ''EACH'',
811 ''PRODUCT_MATERIAL_FLAG'', ''N'',
812 ''RECON_LEAF_NODE_FLAG'', :v_account_type_pos || :v_budget_pos || :v_leaf_flag,
813 NULL
814 )';
815
816 /*
817 Limit attributes
818 */
819 v_attributes_where := ' AND
820 A.ATTRIBUTE_VARCHAR_LABEL IN (
821 ''SOURCE_SYSTEM_CODE'',
822 ''PRODUCT_UOM'',
823 ''PRODUCT_MATERIAL_FLAG'',
824 ''RECON_LEAF_NODE_FLAG''
825 )';
826
827 ELSIF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label IN (
828 'CHANNEL', 'CUSTOMER', 'ENTITY', 'PROJECT', 'TASK', 'GEOGRAPHY',
829 'USER_DIM1', 'USER_DIM2', 'USER_DIM3', 'USER_DIM4', 'USER_DIM5',
830 'USER_DIM6', 'USER_DIM7', 'USER_DIM8', 'USER_DIM9', 'USER_DIM10'
831 ) THEN
832
833 v_dim_attr_numeric_member :=
834 ' DECODE(
835 A.ATTRIBUTE_VARCHAR_LABEL,
836 ''SOURCE_SYSTEM_CODE'', :pv_source_system_code_id,
837 NULL
838 )';
839
840 v_dim_attr_varchar_member1 :=
841 ' DECODE(
842 A.ATTRIBUTE_VARCHAR_LABEL,
843 ''RECON_LEAF_NODE_FLAG'', :v_account_type_pos || :v_budget_pos || :v_leaf_flag,
844 NULL
845 )';
846
847 /*
848 Limit attributes
849 */
850 v_attributes_where := ' AND
851 A.ATTRIBUTE_VARCHAR_LABEL IN (
852 ''SOURCE_SYSTEM_CODE'',
853 ''RECON_LEAF_NODE_FLAG''
854 )';
855
856 END IF;
857
858 FEM_ENGINES_PKG.Tech_Message(
859 p_severity => pc_log_level_statement,
860 p_module => v_module_name || '.end_prep2_populate_attribute',
861 p_msg_text => 'end preparation 2 for attribute population'
862 );
863
864 --piush_util.put_line('end preparation 2 for attribute population');
865
866 --
867 -- Construct Dynamic SQL
868 --
869 v_stmt1 := '
870 MERGE INTO ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name || ' ATTR
871 USING (
872 SELECT
873 A.ATTRIBUTE_ID,
874 AV.VERSION_ID' || ',
875 ' || v_member_id_col_name || ',
876 :pv_fem_vs_id VALUE_SET_ID,
877 ' || v_dim_attr_value_set_id || ' DIM_ATTRIBUTE_VALUE_SET_ID,';
878
879 v_stmt2 := v_dim_attr_numeric_member || ' DIM_ATTRIBUTE_NUMERIC_MEMBER,';
880
881 IF v_dim_attr_varchar_member2 = ' ' THEN
882 v_stmt3 := v_dim_attr_varchar_member1 || ' DIM_ATTRIBUTE_VARCHAR_MEMBER,';
883 v_stmt4 := v_dim_attr_varchar_member2;
884 ELSE
885 v_stmt3 := v_dim_attr_varchar_member1 || ',';
886 v_stmt4 := v_dim_attr_varchar_member2 || ' DIM_ATTRIBUTE_VARCHAR_MEMBER,';
887 END IF;
888
889 v_stmt5 := '
890 1 OBJECT_VERSION_NUMBER,
891 ''N'' AW_SNAPSHOT_FLAG,
892 ''Y'' READ_ONLY_FLAG,
893 :b_sysdate CREATION_DATE,
894 :pv_user_id CREATED_BY,
895 :b_sysdate LAST_UPDATE_DATE,
896 :pv_user_id LAST_UPDATED_BY,
897 :pv_login_id LAST_UPDATE_LOGIN
898 FROM ' || v_from || '
899 WHERE ' || v_where || v_attributes_where;
900
901 v_stmt6 :=
902 ' ) S
903 ON (
904 ATTR.ATTRIBUTE_ID = S.ATTRIBUTE_ID AND
905 ATTR.VERSION_ID = S.VERSION_ID AND
906 ATTR.' || v_member_col || ' = S.' || v_member_col || ' AND
907 ATTR.VALUE_SET_ID = S.VALUE_SET_ID
908 )
909 WHEN MATCHED THEN UPDATE
910 SET ATTR.LAST_UPDATE_DATE = SYSDATE
911 WHEN NOT MATCHED THEN INSERT (
912 ATTR.ATTRIBUTE_ID,
913 ATTR.VERSION_ID,
914 ATTR.' || v_member_col || ',
915 ATTR.VALUE_SET_ID,
916 ATTR.DIM_ATTRIBUTE_VALUE_SET_ID,
917 ATTR.DIM_ATTRIBUTE_NUMERIC_MEMBER,
918 ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER,
919 ATTR.OBJECT_VERSION_NUMBER,
920 ATTR.AW_SNAPSHOT_FLAG,
921 ATTR.READ_ONLY_FLAG,
922 ATTR.CREATION_DATE,
923 ATTR.CREATED_BY,
924 ATTR.LAST_UPDATE_DATE,
925 ATTR.LAST_UPDATED_BY,
926 ATTR.LAST_UPDATE_LOGIN
927 ) VALUES (
928 S.ATTRIBUTE_ID,
929 S.VERSION_ID,
930 S.' || v_member_col || ',
931 S.VALUE_SET_ID,
932 S.DIM_ATTRIBUTE_VALUE_SET_ID,
933 S.DIM_ATTRIBUTE_NUMERIC_MEMBER,
934 S.DIM_ATTRIBUTE_VARCHAR_MEMBER,
935 S.OBJECT_VERSION_NUMBER,
936 S.AW_SNAPSHOT_FLAG,
937 S.READ_ONLY_FLAG,
938 S.CREATION_DATE,
939 S.CREATED_BY,
940 S.LAST_UPDATE_DATE,
941 S.LAST_UPDATED_BY,
942 S.LAST_UPDATE_LOGIN
943 )';
944
945 FEM_ENGINES_PKG.Tech_Message(
946 p_severity => pc_log_level_statement,
947 p_module => v_module_name || '.dsql_insert_merge_into_' ||
948 FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
949 p_msg_text => v_stmt1
950 );
951
952 FEM_ENGINES_PKG.Tech_Message(
953 p_severity => pc_log_level_statement,
954 p_module => v_module_name || '.dsql_insert_merge_into_' ||
955 FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
956 p_msg_text => v_stmt2
957 );
958
959 FEM_ENGINES_PKG.Tech_Message(
960 p_severity => pc_log_level_statement,
961 p_module => v_module_name || '.dsql_insert_merge_into_' ||
962 FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
963 p_msg_text => v_stmt3
964 );
965
966 FEM_ENGINES_PKG.Tech_Message(
967 p_severity => pc_log_level_statement,
968 p_module => v_module_name || '.dsql_insert_merge_into_' ||
969 FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
970 p_msg_text => v_stmt4
971 );
972
973 FEM_ENGINES_PKG.Tech_Message(
974 p_severity => pc_log_level_statement,
975 p_module => v_module_name || '.dsql_insert_merge_into_' ||
976 FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
977 p_msg_text => v_stmt5
978 );
979
980 FEM_ENGINES_PKG.Tech_Message(
981 p_severity => pc_log_level_statement,
982 p_module => v_module_name || '.dsql_insert_merge_into_' ||
983 FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
984 p_msg_text => v_stmt6
985 );
986
987 FEM_ENGINES_PKG.Tech_Message(
988 p_severity => pc_log_level_statement,
989 p_module => v_module_name || '.dsql_insert_merge_into_' ||
990 FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
991 p_msg_text => 'USING ' ||
992 TO_CHAR(FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id) || ', ' ||
993 TO_CHAR(FEM_INTG_DIM_RULE_ENG_PKG.pv_source_system_code_id) || ', ' ||
994 TO_CHAR(v_account_type_pos) || ', ' ||
995 TO_CHAR(v_budget_pos) || ', ' ||
996 v_leaf_flag || ', ' ||
997 TO_CHAR(SYSDATE, 'YYYY/MM/DD') || ', ' ||
998 TO_CHAR(pv_user_id) || ', ' ||
999 TO_CHAR(SYSDATE, 'YYYY/MM/DD') || ', ' ||
1000 TO_CHAR(pv_user_id) || ', ' ||
1001 TO_CHAR(pv_login_id) || ', ' ||
1002 TO_CHAR(b_driving_where_vs_id) || ', ' ||
1003 TO_CHAR(b_m_vs_id) || ', ' ||
1004 TO_CHAR(b_gt_dim_id) || ', ' ||
1005 TO_CHAR(b_flex_value_where_vs_id1) || ', ' ||
1006 TO_CHAR(b_flex_value_where_vs_id2) || ', ' ||
1007 TO_CHAR(b_a_dim_id) || ', ' ||
1008 TO_CHAR(b_gv_gvsc_id) || ', ' ||
1009 TO_CHAR(b_gv_dim_id)
1010 );
1011
1012 --piush_util.put_line('Stmt = ' || v_stmt1 || v_stmt2 || v_stmt3 || v_stmt4 || v_stmt5 || v_stmt6);
1013
1014 EXECUTE IMMEDIATE v_stmt1 || v_stmt2 || v_stmt3 || v_stmt4 ||
1015 v_stmt5 || v_stmt6
1016 USING
1017 FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id, -- Always
1018 FEM_INTG_DIM_RULE_ENG_PKG.pv_source_system_code_id, -- Always
1019 v_account_type_pos, -- NATURAL_ACCOUNT/LINE_ITEM
1020 v_budget_pos, -- NATURAL_ACCOUNT
1021 v_leaf_flag, -- Always
1022 SYSDATE, -- Always
1023 pv_user_id, -- Always
1024 SYSDATE, -- Always
1025 pv_user_id, -- Always
1026 pv_login_id, -- Always
1027 b_driving_where_vs_id, -- Single Seg Detail
1028 b_m_vs_id, -- Single Seg Summary/Multi Seg Detail
1029 b_gt_dim_id, -- Single Seg Summary/Multi Seg Detail
1030 b_flex_value_where_vs_id1, -- 1. Single Seg Detail, Table Validated
1031 -- COMPARNY_COST_CENTER_ORG/
1032 -- NATURAL_ACCOUNT/LINE_ITEM
1033 -- 2. Single Seg Summary/Multi Seg Detail
1034 -- NATURAL_ACCOUNT/LINE_ITEM
1035 b_flex_value_where_vs_id2, -- 1. Multi Seg Detail
1036 -- COMPARNY_COST_CENTER_ORG
1037 b_a_dim_id, -- Always
1038 b_gv_gvsc_id, -- COMPANY_COST_CENTER_ORG/INTERCOMPANY
1039 b_gv_dim_id; -- COMPANY_COST_CENTER_ORG/INTERCOMPANY
1040
1041 x_row_count_tot := SQL%ROWCOUNT;
1042
1043 --piush_util.put_line('SQL%ROWCOUNT = ' || SQL%ROWCOUNT);
1044
1045 FEM_ENGINES_PKG.Tech_Message(
1046 p_severity => pc_log_level_statement,
1047 p_module => v_module_name || '.cnt_insert_merge_into_' ||
1048 FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
1049 p_msg_text => x_row_count_tot
1050 );
1051
1052 END IF;
1053
1054 FEM_ENGINES_PKG.Tech_Message(
1055 p_severity => pc_log_level_procedure,
1056 p_module => v_module_name || '.end',
1057 p_app_name => 'FEM',
1058 p_msg_name => 'FEM_GL_POST_202',
1059 p_token1 => 'FUNC_NAME',
1060 p_value1 => v_func_name,
1061 p_token2 => 'TIME',
1062 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1063 );
1064
1065 EXCEPTION
1066
1067 WHEN FEM_INTG_DIM_RULE_fatal_err THEN
1068
1069 ROLLBACK;
1070
1071 FEM_ENGINES_PKG.Tech_Message(
1072 p_severity => pc_log_level_procedure,
1073 p_module => v_module_name || 'unexpected_exception',
1074 p_app_name => 'FEM',
1075 p_msg_name => 'FEM_GL_POST_203',
1076 p_token1 => 'FUNC_NAME',
1077 p_value1 => v_func_name,
1078 p_token2 => 'TIME',
1079 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1080 );
1081
1082 x_completion_code := 2;
1083
1084 WHEN OTHERS THEN
1085
1086 ROLLBACK;
1087
1088 FEM_ENGINES_PKG.Tech_Message(
1089 p_severity => pc_log_level_unexpected,
1090 p_module => v_module_name || '.unexpected_exception',
1091 p_app_name => 'FEM',
1092 p_msg_name => 'FEM_GL_POST_215',
1093 p_token1 => 'ERR_MSG',
1094 p_value1 => SQLERRM
1095 );
1096
1097 FEM_ENGINES_PKG.User_Message(
1098 p_app_name => 'FEM',
1099 p_msg_name => 'FEM_GL_POST_215',
1100 p_token1 => 'ERR_MSG',
1101 p_value1 => SQLERRM
1102 );
1103
1104 FEM_ENGINES_PKG.Tech_Message(
1105 p_severity => pc_log_level_procedure,
1106 p_module => v_module_name || 'unexpected_exception',
1107 p_app_name => 'FEM',
1108 p_msg_name => 'FEM_GL_POST_203',
1109 p_token1 => 'FUNC_NAME',
1110 p_value1 => v_func_name,
1111 p_token2 => 'TIME',
1112 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1113 );
1114
1115 x_completion_code := 2;
1116
1117 --raise;
1118
1119 END Populate_Dimension_Attribute;
1120
1121
1122 PROCEDURE Detail_Single_Value(
1123 x_completion_code OUT NOCOPY NUMBER,
1124 x_row_count_tot OUT NOCOPY NUMBER
1125 ) IS
1126 v_rows_processed NUMBER;
1127 c_func_name CONSTANT VARCHAR2(30)
1128 := '.Detail_Single_Value';
1129 v_upd_map_table_stmt VARCHAR2(4000);
1130 v_column_list VARCHAR2(1000);
1131 v_value_list VARCHAR2(1000);
1132
1133 v_lockhandle VARCHAR2(100);
1134 v_lock_result NUMBER;
1135 v_loop_counter NUMBER;
1136
1137 FEM_INTG_DIM_RULE_ulock_err EXCEPTION;
1138
1139
1140 CURSOR ColumnList IS
1141 SELECT COLUMN_NAME
1142 FROM FEM_TAB_COLUMNS_B
1143 WHERE TABLE_NAME = 'FEM_BALANCES'
1144 AND FEM_DATA_TYPE_CODE = 'DIMENSION'
1145 AND DIMENSION_ID = FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
1146
1147 -- Start bug Fix 5579716
1148 v_request_id NUMBER;
1149 v_gcs_vs_id NUMBER;
1150 v_fch_vs_select_stmt VARCHAR2(1000):=
1151 'SELECT 1
1152 FROM fem_global_vs_combo_defs fch_vs_combo
1153 WHERE fch_vs_combo.global_vs_combo_id = ( SELECT fch_global_vs_combo_id
1154 FROM gcs_system_options )
1155 AND fch_vs_combo.dimension_id = 8
1156 AND fch_vs_combo.value_set_id = :fem_value_set_id';
1157
1158 TYPE vs_cursor IS REF CURSOR;
1159 fch_vs_cursor vs_cursor;
1160
1161 -- End bug Fix 5579716
1162
1163 BEGIN
1164 FEM_ENGINES_PKG.Tech_Message
1165 ( p_severity => pc_log_level_procedure
1166 ,p_module => pc_module_name||c_func_name
1167 ,p_app_name => 'FEM'
1168 ,p_msg_name => 'FEM_GL_POST_201'
1169 ,p_token1 => 'FUNC_NAME'
1170 ,p_value1 => pc_module_name||c_func_name
1171 ,p_token2 => 'TIME'
1172 ,p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1173
1174 x_completion_code := 0;
1175 v_rows_processed := 0;
1176
1177 DBMS_LOCK.ALLOCATE_UNIQUE(
1178 'FEM_INTG_DIM_RULE' || FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id,
1179 v_lockhandle,
1180 pc_expiration_secs
1181 );
1182
1183 v_loop_counter := 1;
1184
1185 LOOP
1186 IF v_loop_counter > pc_loop_counter_max
1187 THEN
1188
1189 FEM_ENGINES_PKG.Tech_Message(
1190 p_severity => pc_log_level_statement
1191 ,p_module => pc_module_name||c_func_name
1192 ,p_msg_text => 'raising FEM_INTG_DIM_RULE_ulock_err'
1193 );
1194 RAISE FEM_INTG_DIM_RULE_ulock_err;
1195 END IF;
1196
1197 v_lock_result := DBMS_LOCK.REQUEST(
1198 v_lockhandle,
1199 pc_lockmode,
1200 pc_lock_timeout,
1201 pc_release_on_commit
1202 );
1203
1204 IF v_lock_result = 0 OR v_lock_result = 4
1205 THEN
1206 EXIT;
1207 ELSE
1208 v_loop_counter := v_loop_counter + 1;
1209
1210 FEM_ENGINES_PKG.Tech_Message(
1211 p_severity => pc_log_level_statement
1212 ,p_module => pc_module_name||c_func_name
1213 ,p_msg_text => 'sleeping ' || pc_sleep_second || ' second'
1214 );
1215 DBMS_LOCK.SLEEP(pc_sleep_second);
1216 END IF;
1217 END LOOP;
1218
1219 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label <> 'GEOGRAPHY'
1220 THEN
1221
1222 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'INTERCOMPANY'
1223 THEN
1224
1225 v_column_list := FEM_INTG_DIM_RULE_ENG_PKG.pv_member_col;
1226 v_value_list := FEM_INTG_DIM_RULE_ENG_PKG.pv_default_member_id;
1227
1228 ELSIF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label
1229 = 'COMPANY_COST_CENTER_ORG'
1230 THEN
1231
1232 FOR rec IN ColumnList LOOP
1233 IF rec.column_name <> 'INTERCOMPANY_ID' THEN
1234 v_column_list := v_column_list || rec.column_name || ',';
1235 v_value_list := v_value_list || FEM_INTG_DIM_RULE_ENG_PKG.pv_default_member_id || ',';
1236 END IF;
1237 END LOOP;
1238
1239 v_column_list := '(' || TRIM(TRAILING ',' FROM v_column_list) || ')';
1240 v_value_list := TRIM(TRAILING ',' FROM v_value_list);
1241
1242 ELSE
1243
1244 FOR rec IN ColumnList LOOP
1245 v_column_list := v_column_list || rec.column_name || ',';
1246 v_value_list := v_value_list || FEM_INTG_DIM_RULE_ENG_PKG.pv_default_member_id || ',';
1247 END LOOP;
1248
1249 v_column_list := '(' || TRIM(TRAILING ',' FROM v_column_list) || ')';
1250 v_value_list := TRIM(TRAILING ',' FROM v_value_list);
1251 END IF;
1252
1253 v_upd_map_table_stmt :=
1254 'UPDATE fem_intg_ogl_ccid_map fiocm
1255 SET '||v_column_list||' = '||v_value_list||'
1256 WHERE fiocm.code_combination_id between
1257 :v_low and :v_high
1258 AND fiocm.global_vs_combo_id = :v_gvsc_id';
1259
1260 FEM_ENGINES_PKG.Tech_Message
1261 (p_severity => pc_log_level_statement
1262 ,p_module => pc_module_name||c_func_name
1263 ,p_app_name => 'FEM'
1264 ,p_msg_name => 'FEM_GL_POST_204'
1265 ,p_token1 => 'VAR_NAME'
1266 ,p_value1 => 'SQL Statement'
1267 ,p_token2 => 'VAR_VAL'
1268 ,p_value2 => v_upd_map_table_stmt);
1269
1270 EXECUTE IMMEDIATE v_upd_map_table_stmt
1271 USING FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_processed+1
1272 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped
1273 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_gvsc_id;
1274
1275 v_rows_processed := v_rows_processed + SQL%ROWCOUNT;
1276
1277 FEM_ENGINES_PKG.Tech_Message
1278 (p_severity => pc_log_level_statement
1279 ,p_module => pc_module_name||c_func_name
1280 ,p_app_name => 'FEM'
1281 ,p_msg_name => 'FEM_GL_POST_216'
1282 ,p_token1 => 'TABLE'
1283 ,p_value1 => 'fem_intg_ogl_ccid_map'
1284 ,p_token2 => 'NUM'
1285 ,p_value2 => SQL%ROWCOUNT);
1286
1287 v_rows_processed := v_rows_processed + SQL%ROWCOUNT;
1288
1289 pv_progress := 'after executing update map';
1290 --piush_util.put_line(pv_progress);
1291
1292 END IF;
1293
1294 --------------------------------------------------------------
1295 -- Update Dimension definition table with max_ccid_processed
1296 --------------------------------------------------------------
1297
1298 UPDATE fem_intg_dim_rule_defs
1299 SET max_ccid_processed = fem_intg_dim_rule_eng_pkg.pv_max_ccid_to_be_mapped
1300 WHERE dim_rule_obj_def_id = fem_intg_dim_rule_eng_pkg.pv_dim_rule_obj_def_id;
1301 v_rows_processed := v_rows_processed + SQL%ROWCOUNT;
1302
1303
1304 x_row_count_tot := v_rows_processed;
1305 COMMIT;
1306
1307 -- Start bug Fix 5579716
1308 BEGIN
1309 OPEN fch_vs_cursor FOR v_fch_vs_select_stmt USING FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id;
1310 FETCH fch_vs_cursor INTO v_gcs_vs_id;
1311
1312 IF (v_gcs_vs_id IS NOT NULL) THEN
1313
1314 -- submit entity orgs synch program
1315 v_request_id := FND_REQUEST.submit_request( application => 'GCS',
1316 program => 'FCH_UPDATE_ENTITY_ORGS',
1317 sub_request => FALSE);
1318
1319 FEM_ENGINES_PKG.User_Message(
1320 p_app_name => 'FEM',
1321 p_msg_text => 'Submitted Update Entity Organizations Request ' || v_request_id
1322 );
1323
1324 END IF;
1325
1326 CLOSE fch_vs_cursor;
1327
1328 EXCEPTION WHEN OTHERS THEN NULL;
1329 END;
1330 -- End bug Fix 5579716
1331
1332 FEM_ENGINES_PKG.Tech_Message
1333 (p_severity => pc_log_level_procedure,
1334 p_module => pc_module_name || c_func_name,
1335 p_app_name => 'FEM',
1336 p_msg_name => 'FEM_GL_POST_202',
1337 p_token1 => 'FUNC_NAME',
1338 p_value1 => c_func_name,
1339 p_token2 => 'TIME',
1340 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1341 EXCEPTION
1342 WHEN FEM_INTG_DIM_RULE_ulock_err THEN
1343
1344 ROLLBACK;
1345
1346 FEM_ENGINES_PKG.Tech_Message(
1347 p_severity => pc_log_level_exception,
1348 p_module => pc_module_name||c_func_name || '.ulock_err_exception',
1349 p_app_name => 'FEM',
1350 p_msg_name => 'FEM_INTG_DIM_RULE_ULOCK_EXISTS'
1351 );
1352
1353 FEM_ENGINES_PKG.User_Message(
1354 p_app_name => 'FEM',
1355 p_msg_name => 'FEM_INTG_DIM_RULE_ULOCK_EXISTS'
1356 );
1357
1358 FEM_ENGINES_PKG.Tech_Message(
1359 p_severity => pc_log_level_procedure,
1360 p_module => pc_module_name||c_func_name||'.ulock_err_exception',
1361 p_app_name => 'FEM',
1362 p_msg_name => 'FEM_GL_POST_203',
1363 p_token1 => 'FUNC_NAME',
1364 p_value1 => c_func_name,
1365 p_token2 => 'TIME',
1366 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1367 );
1368
1369 x_completion_code := 2;
1370
1371 WHEN OTHERS THEN
1372 FEM_ENGINES_PKG.Tech_Message
1373 (p_severity => pc_log_level_statement
1374 ,p_module => pc_module_name||c_func_name
1375 ,p_msg_text => 'Error: ' || pv_progress);
1376
1377 FEM_ENGINES_PKG.Tech_Message
1378 (p_severity => pc_log_level_statement
1379 ,p_module => pc_module_name||c_func_name
1380 ,p_msg_text => 'Error: ' || sqlerrm);
1381
1382 FEM_ENGINES_PKG.User_Message
1383 (p_msg_text => sqlerrm);
1384
1385 FEM_ENGINES_PKG.Tech_Message
1386 (p_severity => pc_log_level_procedure
1387 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
1388 ,p_app_name => 'FEM'
1389 ,p_msg_name => 'FEM_GL_POST_203'
1390 ,p_token1 => 'FUNC_NAME'
1391 ,p_value1 => c_func_name
1392 ,p_token2 => 'TIME'
1393 ,p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1394
1395
1396 x_completion_code := 2;
1397 END;
1398
1399
1400 /* ======================================================================
1401 Procedure
1402 Populate_Single_Segment
1403 Purpose
1404 This routine is a private routine called by the Detail_Single_Segment
1405 routine. It populates dimension member tables for a single segment
1406 mapping case. The routine constructs MERGE statements dynamically based
1407 a type associated value set.
1408
1409 The following is a sample dynamic MERGE statement for _B table
1410 in a case of Independent value set:
1411
1412 MERGE INTO p_member_b_table_name B
1413 USING (
1414 SELECT
1415 :p_vs_id VALUE_SET_ID,
1416 FLEX_VALUE_ID MEMBER_ID,
1417 FLEX_VALUE MEMBER_DISPLAY_CODE
1418 FROM
1419 FND_FLEX_VALUES
1420 WHERE
1421 FLEX_VALUE_SET_ID = :v_vs_id_b AND
1422 SUMMARY_FLAG = 'N'
1423 ) S
1424 ON (
1425 B.VALUE_SET_ID = S.VALUE_SET_ID AND
1426 B.<p_member_display_code_col> = S.MEMBER_DISPLAY_CODE
1427 )
1428 WHEN MATCHED THEN UPDATE
1429 SET B.LAST_UPDATE_DATE = SYSDATE
1430 WHEN NOT MATCHED THEN INSERT (
1431 VALUE_SET_ID,
1432 <p_member_col>,
1433 <p_member_display_code_col>,
1434 ENABLED_FLAG,
1435 PERSONAL_FLAG,
1436 READ_ONLY_FLAG,
1437 OBJECT_VERSION_NUMBER,
1438 CREATION_DATE,
1439 CREATED_BY,
1440 LAST_UPDATE_DATE,
1441 LAST_UPDATED_BY,
1442 LAST_UPDATE_LOGIN
1443 ) VALUES (
1444 S.VALUE_SET_ID,
1445 S.MEMBER_ID,
1446 S.MEMBER_DISPLAY_CODE,
1447 'Y',
1448 'N',
1449 'Y',
1450 1,
1451 :b_sysdate,
1452 :pv_user_id,
1453 :b_sysdate,
1454 :pv_user_id,
1455 :pv_login_id
1456 )
1457 USING p_vs_id, pv_mapped_segs(1).vs_id,
1458 SYSDATE, pv_user_id, SYSDATE, pv_user_id, pv_login_id
1459 ====================================================================== */
1460 PROCEDURE Populate_Single_Segment(
1461 p_dim_id IN NUMBER,
1462 p_vs_id IN NUMBER,
1463 p_member_b_table_name IN VARCHAR2,
1464 p_member_tl_table_name IN VARCHAR2,
1465 p_member_col IN VARCHAR2,
1466 p_member_display_code_col IN VARCHAR2,
1467 p_member_name_col IN VARCHAR2,
1468 x_row_count_tot OUT NOCOPY NUMBER
1469 ) IS
1470 v_module_name VARCHAR2(100);
1471 v_func_name VARCHAR2(100);
1472 v_row_count_tot1 NUMBER;
1473 v_row_count_tot2 NUMBER;
1474 v_stmt1 VARCHAR2(4000);
1475 v_stmt2 VARCHAR2(4000);
1476 v_stmt3 VARCHAR2(4000);
1477 v_stmt4 VARCHAR2(4000);
1478 v_using_b VARCHAR2(4000);
1479 v_using_tl VARCHAR2(4000);
1480 v_vs_id_b NUMBER;
1481 v_member_id_val VARCHAR2(50);
1482 v_source_lang VARCHAR2(50);
1483 v_cr VARCHAR2(100);
1484 BEGIN
1485 v_module_name := 'fem.plsql.fem_intg_dim.populate_single_segment';
1486 v_func_name := 'FEM_INTG_NEW_DIM_MEMBER_PKG.Populate_Single_Segment';
1487
1488 FEM_ENGINES_PKG.Tech_Message(
1489 p_severity => pc_log_level_procedure,
1490 p_module => v_module_name || '.begin',
1491 p_app_name => 'FEM',
1492 p_msg_name => 'FEM_GL_POST_201',
1493 p_token1 => 'FUNC_NAME',
1494 p_value1 => v_func_name,
1495 p_token2 => 'TIME',
1496 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1497 );
1498
1499 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).table_validated_flag = 'N' THEN
1500
1501 v_using_b := '
1502 USING (
1503 SELECT
1504 :p_vs_id VALUE_SET_ID,
1505 FLEX_VALUE_ID MEMBER_ID,
1506 FLEX_VALUE MEMBER_DISPLAY_CODE
1507 FROM
1508 FND_FLEX_VALUES
1509 WHERE
1510 FLEX_VALUE_SET_ID = :v_vs_id_b AND
1511 SUMMARY_FLAG = ''N''
1512 ) S';
1513
1514 v_using_tl := '
1515 USING (
1516 SELECT
1517 M.VALUE_SET_ID,
1518 T.FLEX_VALUE_ID MEMBER_COL,
1519 T.FLEX_VALUE_MEANING MEMBER_NAME,
1520 T.DESCRIPTION MEMBER_DESC,
1521 T.LANGUAGE LANGUAGE_CODE,
1522 T.SOURCE_LANG
1523 FROM
1524 ' || p_member_b_table_name || ' M,
1525 FND_FLEX_VALUES B,
1526 FND_FLEX_VALUES_TL T
1527 WHERE
1528 M.VALUE_SET_ID = :p_vs_id AND
1529 T.FLEX_VALUE_ID = M.' || p_member_col || ' AND
1530 B.FLEX_VALUE_ID = T.FLEX_VALUE_ID AND
1531 B.FLEX_VALUE_SET_ID = :v_vs_id_b AND
1532 B.SUMMARY_FLAG = ''N''
1533 ) S';
1534
1535 v_vs_id_b := FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).vs_id;
1536
1537 v_member_id_val := 'S.MEMBER_ID';
1538 v_source_lang := 'S.SOURCE_LANG';
1539
1540 ELSE
1541
1542 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).where_clause IS NOT NULL THEN
1543 v_cr := '
1544 ';
1545 ELSE
1546 v_cr := '';
1547 END IF;
1548
1549 v_using_b := '
1550 USING (
1551 SELECT
1552 :p_vs_id || :v_vs_id_b VALUE_SET_ID,
1553 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).val_col_name || ' MEMBER_DISPLAY_CODE
1554 FROM
1555 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).table_name ||
1556 v_cr || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).where_clause || '
1557 ) S';
1558
1559 v_using_tl := '
1560 USING (
1561 SELECT
1562 B.VALUE_SET_ID,
1563 B.' || p_member_col || ' MEMBER_COL,
1564 V.MEMBER_NAME,
1565 V.MEMBER_DESC,
1566 L.LANGUAGE_CODE
1567 FROM (
1568 SELECT
1569 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).val_col_name || ' MEMBER_DISPLAY_CODE,
1570 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).val_col_name || ' MEMBER_NAME,
1571 ' || NVL(FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).meaning_col_name, 'NULL') || ' MEMBER_DESC
1572 FROM
1573 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).table_name ||
1574 v_cr || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).where_clause || '
1575 ) V,
1576 ' || p_member_b_table_name || ' B,
1577 FND_LANGUAGES L
1578 WHERE
1579 B.VALUE_SET_ID = :p_vs_id || :v_vs_id_b AND
1580 B.' || p_member_display_code_col || ' = V.MEMBER_DISPLAY_CODE AND
1581 L.INSTALLED_FLAG = ''B''
1582 ) S';
1583
1584 v_vs_id_b := '';
1585
1586 v_member_id_val := 'FND_FLEX_VALUES_S.NEXTVAL';
1587 v_source_lang := 'S.LANGUAGE_CODE';
1588
1589 END IF;
1590
1591 v_stmt1 := '
1592 MERGE INTO ' || p_member_b_table_name || ' B' || v_using_b || '
1593 ON (
1594 B.VALUE_SET_ID = S.VALUE_SET_ID AND
1595 B.' || p_member_display_code_col || ' = S.MEMBER_DISPLAY_CODE
1596 )';
1597 -- Bug 4393061 - changed read_only_flag to 'N'
1598 v_stmt2 := '
1599 WHEN MATCHED THEN UPDATE
1600 SET B.LAST_UPDATE_DATE = SYSDATE
1601 WHEN NOT MATCHED THEN INSERT (
1602 VALUE_SET_ID,
1603 ' || p_member_col || ',
1604 ' || p_member_display_code_col || ',
1605 ENABLED_FLAG,
1606 PERSONAL_FLAG,
1607 READ_ONLY_FLAG,
1608 OBJECT_VERSION_NUMBER,
1609 CREATION_DATE,
1610 CREATED_BY,
1611 LAST_UPDATE_DATE,
1612 LAST_UPDATED_BY,
1613 LAST_UPDATE_LOGIN
1614 ) VALUES (
1615 S.VALUE_SET_ID,
1616 ' || v_member_id_val || ',
1617 S.MEMBER_DISPLAY_CODE,
1618 ''Y'',
1619 ''N'',
1620 ''N'',
1621 1,
1622 :b_sysdate,
1623 :pv_user_id,
1624 :b_sysdate,
1625 :pv_user_id,
1626 :pv_login_id
1627 )';
1628
1629 v_stmt3 := '
1630 MERGE INTO ' || p_member_tl_table_name || ' TL' || v_using_tl || '
1631 ON (
1632 TL.VALUE_SET_ID = S.VALUE_SET_ID AND
1633 TL.' || p_member_col || ' = S.MEMBER_COL AND
1634 TL.LANGUAGE = S.LANGUAGE_CODE
1635 )';
1636
1637 v_stmt4 := '
1638 WHEN MATCHED THEN UPDATE
1639 SET TL.LAST_UPDATE_DATE = SYSDATE,
1640 TL.DESCRIPTION = S.MEMBER_DESC
1641 WHEN NOT MATCHED THEN INSERT (
1642 VALUE_SET_ID,
1643 ' || p_member_col || ',
1644 ' || p_member_name_col || ',
1645 DESCRIPTION,
1646 LANGUAGE,
1647 SOURCE_LANG,
1648 CREATION_DATE,
1649 CREATED_BY,
1650 LAST_UPDATE_DATE,
1651 LAST_UPDATED_BY,
1652 LAST_UPDATE_LOGIN
1653 ) VALUES (
1654 S.VALUE_SET_ID,
1655 S.MEMBER_COL,
1656 S.MEMBER_NAME,
1657 S.MEMBER_DESC,
1658 S.LANGUAGE_CODE,
1659 ' || v_source_lang || ',
1660 :b_sysdate,
1661 :pv_user_id,
1662 :b_sysdate,
1663 :pv_user_id,
1664 :pv_login_id
1665 )';
1666
1667 FEM_ENGINES_PKG.Tech_Message(
1668 p_severity => pc_log_level_statement,
1669 p_module => v_module_name || '.dsql_merge_into_' ||
1670 p_member_b_table_name,
1671 p_msg_text => v_stmt1
1672 );
1673
1674 FEM_ENGINES_PKG.Tech_Message(
1675 p_severity => pc_log_level_statement,
1676 p_module => v_module_name || '.dsql_merge_into_' ||
1677 p_member_b_table_name,
1678 p_msg_text => v_stmt2
1679 );
1680
1681 FEM_ENGINES_PKG.Tech_Message(
1682 p_severity => pc_log_level_statement,
1683 p_module => v_module_name || '.dsql_merge_into_' ||
1684 p_member_b_table_name,
1685 p_msg_text => 'USING ' ||
1686 TO_CHAR(p_vs_id) || ', ' ||
1687 TO_CHAR(v_vs_id_b) || ', ' ||
1688 TO_CHAR(SYSDATE, 'YYYY/MM/DD') || ', ' ||
1689 TO_CHAR(pv_user_id) || ', ' ||
1690 TO_CHAR(SYSDATE, 'YYYY/MM/DD') || ', ' ||
1691 TO_CHAR(pv_user_id) || ', ' ||
1692 TO_CHAR(pv_login_id)
1693 );
1694
1695 EXECUTE IMMEDIATE v_stmt1 || v_stmt2
1696 USING
1697 p_vs_id,
1698 v_vs_id_b,
1699 SYSDATE,
1700 pv_user_id,
1701 SYSDATE,
1702 pv_user_id,
1703 pv_login_id;
1704
1705 v_row_count_tot1 := SQL%ROWCOUNT;
1706
1707 FEM_ENGINES_PKG.Tech_Message(
1708 p_severity => pc_log_level_statement,
1709 p_module => v_module_name || '.cnt_merge_into_' ||
1710 p_member_tl_table_name,
1711 p_msg_text => v_row_count_tot1
1712 );
1713
1714 FEM_ENGINES_PKG.Tech_Message(
1715 p_severity => pc_log_level_statement,
1716 p_module => v_module_name || '.dsql_merge_into_' ||
1717 p_member_tl_table_name,
1718 p_msg_text => v_stmt3
1719 );
1720
1721 FEM_ENGINES_PKG.Tech_Message(
1722 p_severity => pc_log_level_statement,
1723 p_module => v_module_name || '.dsql_merge_into_' ||
1724 p_member_tl_table_name,
1725 p_msg_text => v_stmt4
1726 );
1727
1728 FEM_ENGINES_PKG.Tech_Message(
1729 p_severity => pc_log_level_statement,
1730 p_module => v_module_name || '.dsql_merge_into_' ||
1731 p_member_tl_table_name,
1732 p_msg_text => 'USING ' ||
1733 TO_CHAR(p_vs_id) || ', ' ||
1734 TO_CHAR(v_vs_id_b) || ', ' ||
1735 TO_CHAR(SYSDATE, 'YYYY/MM/DD') || ', ' ||
1736 TO_CHAR(pv_user_id) || ', ' ||
1737 TO_CHAR(SYSDATE, 'YYYY/MM/DD') || ', ' ||
1738 TO_CHAR(pv_user_id) || ', ' ||
1739 TO_CHAR(pv_login_id)
1740 );
1741
1742 EXECUTE IMMEDIATE v_stmt3 || v_stmt4
1743 USING
1744 p_vs_id,
1745 v_vs_id_b,
1746 SYSDATE,
1747 pv_user_id,
1748 SYSDATE,
1749 pv_user_id,
1750 pv_login_id;
1751
1752 v_row_count_tot2 := SQL%ROWCOUNT;
1753
1754 FEM_ENGINES_PKG.Tech_Message(
1755 p_severity => pc_log_level_statement,
1756 p_module => v_module_name || '.cnt_merge_into_' ||
1757 p_member_tl_table_name,
1758 p_msg_text => v_row_count_tot2
1759 );
1760
1761 x_row_count_tot := v_row_count_tot1 + v_row_count_tot2;
1762
1763 FEM_ENGINES_PKG.Tech_Message(
1764 p_severity => pc_log_level_procedure,
1765 p_module => v_module_name || '.end',
1766 p_app_name => 'FEM',
1767 p_msg_name => 'FEM_GL_POST_202',
1768 p_token1 => 'FUNC_NAME',
1769 p_value1 => v_func_name,
1770 p_token2 => 'TIME',
1771 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1772 );
1773
1774 END Populate_Single_Segment;
1775
1776
1777 /* ======================================================================
1778 Procedure
1779 Detail_Single_Segment
1780 Purpose
1781 This routine populates dimension member tables as well as dimension
1782 member attribute tables by calling the Populate_Single_Segment and
1783 the Populate_Dimension_Attribute routines, respectively.
1784
1785 The routine also updates FEM_INTG_OGL_CCID_MAP table through
1786 a dynamically constructed UPDATE statement based on dimension.
1787
1788 The following is a sample dynamic UPDATE statement for Company Cost
1789 Center Organization:
1790
1791 UPDATE FEM_INTG_OGL_CCID_MAP M
1792 SET (COMPANY_COST_CENTER_ORG_ID) = (
1793 SELECT
1794 B.COMPANY_COST_CENTER_ORG_ID
1795 FROM
1796 FEM_CCTR_ORGS_B B,
1797 GL_CODE_COMBINATIONS G
1798 WHERE
1799 B.VALUE_SET_ID = :pv_fem_vs_id AND
1800 B.CCTR_ORG_DISPLAY_CODE =
1801 G.<pv_mapped_segs(1).application_column_name> AND
1802 G.CHART_OF_ACCOUNTS_ID = :pv_coa_id AND
1803 G.SUMMARY_FLAG = 'N' AND
1804 M.CODE_COMBINATION_ID = G.CODE_COMBINATION_ID
1805 )
1806 WHERE M.GLOBAL_VS_COMBO_ID = :pv_gvsc_id
1807 AND M.CODE_COMBINATION_ID IN (
1808 SELECT
1809 M2.CODE_COMBINATION_ID
1810 FROM
1811 FEM_CCTR_ORGS_B B2,
1812 FEM_INTG_OGL_CCID_MAP M2,
1813 GL_CODE_COMBINATIONS G2
1814 WHERE
1815 B2.VALUE_SET_ID = :pv_fem_vs_id AND
1816 B2.CCTR_ORG_DISPLAY_CODE =
1817 G2.<pv_mapped_segs(1).application_column_name> AND
1818 G2.CHART_OF_ACCOUNTS_ID = :pv_coa_id AND
1819 G2.SUMMARY_FLAG = 'N' AND
1820 M2.CODE_COMBINATION_ID = G2.CODE_COMBINATION_ID AND
1821 M2.GLOBAL_VS_COMBO_ID = :pv_gvsc_id AND
1822 M2.CODE_COMBINATION_ID BETWEEN :pv_max_ccid_processed+1 AND
1823 :pv_max_ccid_to_be_mapped
1824 )
1825 USING pv_fem_vs_id, pv_coa_id, pv_gvsc_id, pv_fem_vs_id, pv_coa_id,
1826 pv_gvsc_id, pv_max_ccid_processed+1, pv_max_ccid_to_be_mapped
1827
1828 Note that there is a possible redundant where clause when updating the
1829 FEM_INTG_OGL_CCID_MAP table. For details, see bug4350641.
1830
1831 ====================================================================== */
1832 PROCEDURE Detail_Single_Segment(
1833 x_completion_code OUT NOCOPY NUMBER,
1834 x_row_count_tot OUT NOCOPY NUMBER
1835 ) IS
1836 v_module_name VARCHAR2(100);
1837 v_func_name VARCHAR2(100);
1838
1839 v_lockhandle VARCHAR2(100);
1840 v_lock_result NUMBER;
1841 v_loop_counter NUMBER;
1842
1843 v_member_col VARCHAR2(30);
1844
1845 v_stmt1 VARCHAR2(4000);
1846 v_stmt2 VARCHAR2(4000);
1847 v_stmt3 VARCHAR2(4000);
1848
1849 v_completion_code NUMBER;
1850 v_row_count_tot NUMBER;
1851
1852 v_column_list VARCHAR2(1000);
1853 v_value_list VARCHAR2(1000);
1854 v_result VARCHAR2(20);
1855
1856 FEM_INTG_DIM_RULE_fatal_err EXCEPTION;
1857 FEM_INTG_DIM_RULE_ulock_err EXCEPTION;
1858 FEM_INTG_DIM_RULE_attr_err EXCEPTION;
1859
1860 --start bug fix 5560443
1861 /*
1862 CURSOR ColumnList IS
1863 SELECT COLUMN_NAME
1864 FROM FEM_TAB_COLUMNS_B
1865 WHERE TABLE_NAME = 'FEM_BALANCES'
1866 AND FEM_DATA_TYPE_CODE = 'DIMENSION'
1867 AND DIMENSION_ID = FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
1868 */
1869 --End bug fix 5560443
1870
1871 -- start bug fix 5377544
1872 v_Num_Workers NUMBER;
1873 X_errbuf VARCHAR2(2000);
1874 v_dim_rule_req_count NUMBER;
1875 FEM_INTG_DIM_RULE_worker_err EXCEPTION;
1876 -- end bug fix 5377544
1877
1878 BEGIN
1879
1880 v_module_name := 'fem.plsql.fem_intg_dim.detail_single_segment';
1881 v_func_name := 'FEM_INTG_NEW_DIM_MEMBER_PKG.Detail_Single_Segment';
1882
1883 FEM_ENGINES_PKG.Tech_Message(
1884 p_severity => pc_log_level_procedure,
1885 p_module => v_module_name || '.begin',
1886 p_app_name => 'FEM',
1887 p_msg_name => 'FEM_GL_POST_201',
1888 p_token1 => 'FUNC_NAME',
1889 p_value1 => v_func_name,
1890 p_token2 => 'TIME',
1891 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1892 );
1893
1894 x_completion_code := 0;
1895 x_row_count_tot := 0;
1896
1897 FEM_ENGINES_PKG.Tech_Message(
1898 p_severity => pc_log_level_statement,
1899 p_module => v_module_name || '.begin_dim_member_populate_' ||
1900 LOWER(FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label),
1901 p_msg_text => 'begin '||FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label||
1902 ' dimension member population'
1903 );
1904
1905 DBMS_LOCK.ALLOCATE_UNIQUE(
1906 'FEM_INTG_DIM_RULE' || FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id,
1907 v_lockhandle,
1908 pc_expiration_secs
1909 );
1910
1911 v_loop_counter := 0;
1912
1913 LOOP
1914 IF v_loop_counter > pc_loop_counter_max THEN
1915
1916 FEM_ENGINES_PKG.Tech_Message(
1917 p_severity => pc_log_level_statement,
1918 p_module => v_module_name || '.ulock_err',
1919 p_msg_text => 'raising FEM_INTG_DIM_RULE_ulock_err'
1920 );
1921
1922 RAISE FEM_INTG_DIM_RULE_ulock_err;
1923 END IF;
1924
1925 v_lock_result := DBMS_LOCK.REQUEST(
1926 v_lockhandle,
1927 pc_lockmode,
1928 pc_lock_timeout,
1929 pc_release_on_commit
1930 );
1931
1932 IF v_lock_result = 0 OR v_lock_result = 4 THEN
1933 EXIT;
1934 ELSE
1935 v_loop_counter := v_loop_counter + 1;
1936
1937 FEM_ENGINES_PKG.Tech_Message(
1938 p_severity => pc_log_level_statement,
1939 p_module => v_module_name || '.ulock_sleep',
1940 p_msg_text => 'sleeping ' || pc_sleep_second || ' second'
1941 );
1942
1943 DBMS_LOCK.SLEEP(pc_sleep_second);
1944 END IF;
1945
1946 END LOOP;
1947
1948 --
1949 -- Populate Single Segment member tables for
1950 -- COMPANY_COST_CENTER_ORG/INTERCOMPANY
1951 --
1952 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'COMPANY_COST_CENTER_ORG' OR
1953 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'INTERCOMPANY' THEN
1954
1955 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_balancing_segment_num = 1 THEN
1956 -- When a Single Segment is a Balancing Segment
1957 Populate_Single_Segment(
1958 p_dim_id => FEM_INTG_DIM_RULE_ENG_PKG.pv_com_dim_id,
1959 p_vs_id => FEM_INTG_DIM_RULE_ENG_PKG.pv_com_vs_id,
1960 p_member_b_table_name => 'FEM_COMPANIES_B',
1961 p_member_tl_table_name => 'FEM_COMPANIES_TL',
1962 p_member_col => 'COMPANY_ID',
1963 p_member_display_code_col => 'COMPANY_DISPLAY_CODE',
1964 p_member_name_col => 'COMPANY_NAME',
1965 x_row_count_tot => v_row_count_tot
1966 );
1967 x_row_count_tot := x_row_count_tot + v_row_count_tot;
1968
1969 ELSE
1970 -- When a Single Segment is a Cost Center Segment
1971 Populate_Single_Segment(
1972 p_dim_id => FEM_INTG_DIM_RULE_ENG_PKG.pv_cc_dim_id,
1973 p_vs_id => FEM_INTG_DIM_RULE_ENG_PKG.pv_cc_vs_id,
1974 p_member_b_table_name => 'FEM_COST_CENTERS_B',
1975 p_member_tl_table_name => 'FEM_COST_CENTERS_TL',
1976 p_member_col => 'COST_CENTER_ID',
1977 p_member_display_code_col => 'COST_CENTER_DISPLAY_CODE',
1978 p_member_name_col => 'COST_CENTER_NAME',
1979 x_row_count_tot => v_row_count_tot
1980 );
1981 x_row_count_tot := x_row_count_tot + v_row_count_tot;
1982
1983 END IF;
1984
1985 END IF;
1986
1987 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'INTERCOMPANY' THEN
1988 v_member_col := FEM_INTG_DIM_RULE_ENG_PKG.pv_cctr_org_member_col;
1989 ELSE
1990 v_member_col := FEM_INTG_DIM_RULE_ENG_PKG.pv_member_col;
1991 END IF;
1992
1993 --
1994 -- Populate Single Segment member tables
1995 --
1996 Populate_Single_Segment(
1997 p_dim_id => FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id,
1998 p_vs_id => FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id,
1999 p_member_b_table_name => FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name,
2000 p_member_tl_table_name => FEM_INTG_DIM_RULE_ENG_PKG.pv_member_tl_table_name,
2001 p_member_col => v_member_col,
2002 p_member_display_code_col => FEM_INTG_DIM_RULE_ENG_PKG.pv_member_display_code_col,
2003 p_member_name_col => FEM_INTG_DIM_RULE_ENG_PKG.pv_member_name_col,
2004 x_row_count_tot => v_row_count_tot
2005 );
2006 x_row_count_tot := x_row_count_tot + v_row_count_tot;
2007
2008 --
2009 -- Populate dimension attributes
2010 -- (COMPANY and CCTR do not have attributes to populate)
2011 --
2012 FEM_ENGINES_PKG.User_Message(
2013 p_app_name => 'FEM',
2014 p_msg_name => 'FEM_INTG_DIM_MEMB_501'
2015 );
2016
2017 Populate_Dimension_Attribute(
2018 p_summary_flag => NVL(FEM_INTG_DIM_RULE_ENG_PKG.pv_summary_flag, 'N'),
2019 x_completion_code => v_completion_code,
2020 x_row_count_tot => v_row_count_tot
2021 );
2022
2023 IF v_completion_code = 2 THEN
2024
2025 FEM_ENGINES_PKG.Tech_Message(
2026 p_severity => pc_log_level_statement,
2027 p_module => v_module_name || '.populate_attribute_err',
2028 p_msg_text => 'raising FEM_INTG_DIM_RULE_attr_err'
2029 );
2030
2031 RAISE FEM_INTG_DIM_RULE_attr_err;
2032 END IF;
2033
2034 FEM_ENGINES_PKG.Tech_Message(
2035 p_severity => pc_log_level_statement,
2036 p_module => v_module_name || '.populate_attribute_err_return',
2037 p_msg_text => 'v_completion_code=' || v_completion_code ||
2038 ', v_row_count_tot=' || v_row_count_tot
2039 );
2040
2041 x_row_count_tot := x_row_count_tot + v_row_count_tot;
2042
2043 COMMIT;
2044
2045 FEM_ENGINES_PKG.Tech_Message(
2046 p_severity => pc_log_level_statement,
2047 p_module => v_module_name || '.end_dim_member_populate_' ||
2048 lower(FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label),
2049 p_msg_text => 'end ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label
2050 || ' dimension member population'
2051 );
2052
2053 /*
2054 Although the Dimension Rule Engine should create dimension values for
2055 the Geography dimension if there is a dimension rule defined for it,
2056 it should not attempt to update FEM_INTG_OGL_CCID_MAP with its members.
2057 This is because the GEOGRAPHY_ID column does not exist in both
2058 FEM_BALANCES and FEM_INTG_OGL_CCID_MAP tables. For details,
2059 see bug4093543.
2060 */
2061 --Start bug fix 5560443
2062 /*
2063 --dedutta: removed the Geography check here
2064 NonNullFlag := false;
2065 open ColumnList;
2066 fetch ColumnList into ColumnList_rec;
2067 if ColumnList%found then
2068 NonNullFlag := true;
2069 end if;
2070 close ColumnList;
2071
2072 IF NonNullFlag THEN
2073 */
2074 -- start bug fix 5377544
2075 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'INTERCOMPANY' THEN
2076
2077 --End bug fix 5560443
2078
2079 -- Since requests will reach completed phase irrespective of status
2080 -- Check if any dimension rule requests which are not having completed phase
2081 -- for any dimension other than org dimension for the same chart of account
2082 -- If any request found then issue sleep timer
2083 LOOP
2084 BEGIN
2085 SELECT 1
2086 INTO v_dim_rule_req_count
2087 FROM dual
2088 WHERE EXISTS ( SELECT 1
2089 FROM fnd_concurrent_programs fcp,
2090 fnd_concurrent_requests fcr,
2091 fem_intg_dim_rules idr,
2092 fem_object_definition_b fodb
2093 WHERE fcp.concurrent_program_id = fcr.concurrent_program_id
2094 AND fcp.application_id = fcr.program_application_id
2095 AND fcp.application_id = 274
2096 AND fcp.concurrent_program_name = 'FEM_INTG_DIM_RULE_ENGINE'
2097 AND fcr.phase_code <> 'C'
2098 AND idr.dim_rule_obj_id = fodb.object_id
2099 AND idr.chart_of_accounts_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id
2100 --Start bug fix 5560443
2101 AND idr.dimension_id <> 0
2102 --End bug fix 5560443
2103 AND fcr.argument1 = fodb.object_definition_id
2104 AND fcr.argument2 = 'MEMBER');
2105 DBMS_LOCK.SLEEP(pc_sleep_second);
2106 EXCEPTION WHEN NO_DATA_FOUND THEN EXIT;
2107 END;
2108 END LOOP;
2109
2110 select nvl(value,1)*2 no_of_workers
2111 into v_Num_Workers
2112 from v$parameter
2113 where name = 'cpu_count';
2114
2115 FEM_ENGINES_PKG.User_Message(
2116 p_app_name => 'FEM',
2117 p_msg_text => 'Kicking off '||v_Num_Workers||' workers requests at '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')
2118 );
2119
2120 FEM_ENGINES_PKG.Tech_Message(
2121 p_severity => pc_log_level_statement,
2122 p_module => v_module_name || '.dsql_update_fem_intg_ogl_ccid_map',
2123 p_msg_text => 'USING ' ||
2124 TO_CHAR(FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id) || ', ' ||
2125 TO_CHAR(FEM_INTG_DIM_RULE_ENG_PKG.pv_gvsc_id)
2126 );
2127
2128 -- AD Parallel framework Manager processing
2129
2130 --Purge all the info from ad processing tables
2131 ad_parallel_updates_pkg.purge_processed_units
2132 (X_owner => 'FEM',
2133 X_table => 'FEM_INTG_OGL_CCID_MAP',
2134 X_script => FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id);
2135
2136 ad_parallel_updates_pkg.delete_update_information
2137 (X_update_type => ad_parallel_updates_pkg.ROWID_RANGE,
2138 X_owner => 'FEM',
2139 X_table => 'FEM_INTG_OGL_CCID_MAP',
2140 X_script => FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id);
2141
2142 -- submit update CCID worker
2143 AD_CONC_UTILS_PKG.submit_subrequests( X_errbuf => X_errbuf,
2144 X_retcode => v_completion_code,
2145 X_WorkerConc_app_shortname => 'FEM',
2146 X_WorkerConc_progname => 'FEM_INTG_DIM_RULE_WORKER',
2147 X_batch_size => pv_batch_size,
2148 X_Num_Workers => v_Num_Workers,
2149 X_Argument4 => FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id,
2150 X_Argument5 => FEM_INTG_DIM_RULE_ENG_PKG.pv_gvsc_id,
2151 X_Argument6 => FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_processed,
2152 X_Argument7 => FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped
2153 );
2154
2155 IF v_completion_code = 2 THEN
2156
2157 RAISE FEM_INTG_DIM_RULE_worker_err;
2158
2159 END IF;
2160
2161 --
2162 -- Update dimension rule definitions for single segment/value rules
2163 --
2164
2165 UPDATE FEM_INTG_DIM_RULE_DEFS
2166 SET MAX_CCID_PROCESSED = FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped
2167 WHERE DIM_RULE_OBJ_DEF_ID IN ( SELECT defs.dim_rule_obj_def_id
2168 FROM fem_intg_dim_rules idr,
2169 fem_object_definition_b fodb,
2170 fem_xdim_dimensions fxd,
2171 fem_intg_dim_rule_defs defs,
2172 fem_tab_columns_b ftcb
2173 WHERE ftcb.table_name = 'FEM_BALANCES'
2174 AND ftcb.fem_data_type_code = 'DIMENSION'
2175 AND ftcb.dimension_id = fxd.dimension_id
2176 AND DECODE(ftcb.column_name,'INTERCOMPANY_ID', 0, fxd.dimension_id) = idr.dimension_id
2177 AND idr.chart_of_accounts_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id
2178 AND idr.dim_rule_obj_id = fodb.object_id
2179 AND defs.dim_rule_obj_def_id = fodb.object_definition_id
2180 AND defs.dim_mapping_option_code IN ('SINGLESEG','SINGLEVAL') );
2181
2182
2183 v_row_count_tot := SQL%ROWCOUNT;
2184
2185 FEM_ENGINES_PKG.Tech_Message(
2186 p_severity => pc_log_level_statement,
2187 p_module => v_module_name || '.cnt_update_FEM_INTG_DIM_RULE_DEFS',
2188 p_msg_text => v_row_count_tot
2189 );
2190
2191 x_row_count_tot := x_row_count_tot + v_row_count_tot;
2192
2193 END IF;
2194
2195 x_completion_code := v_completion_code;
2196
2197 --Start bug fix 5560443
2198 --END IF;
2199 --End bug fix 5560443
2200
2201 -- end bug fix 5377544
2202
2203 COMMIT;
2204
2205 FEM_ENGINES_PKG.Tech_Message(
2206 p_severity => pc_log_level_statement,
2207 p_module => v_module_name || '.end_mapping_table',
2208 p_msg_text => 'end update mapping table'
2209 );
2210
2211 FEM_ENGINES_PKG.Tech_Message(
2212 p_severity => pc_log_level_procedure,
2213 p_module => v_module_name || '.end',
2214 p_app_name => 'FEM',
2215 p_msg_name => 'FEM_GL_POST_202',
2216 p_token1 => 'FUNC_NAME',
2217 p_value1 => v_func_name,
2218 p_token2 => 'TIME',
2219 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2220 );
2221
2222 EXCEPTION
2223
2224 WHEN FEM_INTG_DIM_RULE_fatal_err THEN
2225
2226 ROLLBACK;
2227
2228 FEM_ENGINES_PKG.Tech_Message(
2229 p_severity => pc_log_level_procedure,
2230 p_module => v_module_name || 'unexpected_exception',
2231 p_app_name => 'FEM',
2232 p_msg_name => 'FEM_GL_POST_203',
2233 p_token1 => 'FUNC_NAME',
2234 p_value1 => v_func_name,
2235 p_token2 => 'TIME',
2236 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2237 );
2238
2239 x_completion_code := 2;
2240
2241 WHEN FEM_INTG_DIM_RULE_ulock_err THEN
2242
2243 ROLLBACK;
2244
2245 FEM_ENGINES_PKG.Tech_Message(
2246 p_severity => pc_log_level_exception,
2247 p_module => v_module_name || '.ulock_err_exception',
2248 p_app_name => 'FEM',
2249 p_msg_name => 'FEM_INTG_DIM_RULE_ULOCK_EXISTS'
2250 );
2251
2252 FEM_ENGINES_PKG.User_Message(
2253 p_app_name => 'FEM',
2254 p_msg_name => 'FEM_INTG_DIM_RULE_ULOCK_EXISTS'
2255 );
2256
2257 FEM_ENGINES_PKG.Tech_Message(
2258 p_severity => pc_log_level_procedure,
2259 p_module => v_module_name || '.ulock_err_exception',
2260 p_app_name => 'FEM',
2261 p_msg_name => 'FEM_GL_POST_203',
2262 p_token1 => 'FUNC_NAME',
2263 p_value1 => v_func_name,
2264 p_token2 => 'TIME',
2265 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2266 );
2267
2268 x_completion_code := 2;
2269
2270 WHEN FEM_INTG_DIM_RULE_attr_err THEN
2271
2272 ROLLBACK;
2273
2274 FEM_ENGINES_PKG.Tech_Message(
2275 p_severity => pc_log_level_exception,
2276 p_module => v_module_name || '.attr_err_exception',
2277 p_app_name => 'FEM',
2278 p_msg_name => 'FEM_INTG_DIM_RULE_ATTR_FAILURE'
2279 );
2280
2281 FEM_ENGINES_PKG.User_Message(
2282 p_app_name => 'FEM',
2283 p_msg_name => 'FEM_INTG_DIM_RULE_ATTR_FAILURE'
2284 );
2285
2286 FEM_ENGINES_PKG.Tech_Message(
2287 p_severity => pc_log_level_procedure,
2288 p_module => v_module_name || '.attr_err_exception',
2289 p_app_name => 'FEM',
2290 p_msg_name => 'FEM_GL_POST_203',
2291 p_token1 => 'FUNC_NAME',
2292 p_value1 => v_func_name,
2293 p_token2 => 'TIME',
2294 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2295 );
2296
2297 x_completion_code := 2;
2298
2299 WHEN FEM_INTG_DIM_RULE_worker_err THEN
2300
2301 ROLLBACK;
2302
2303 FEM_ENGINES_PKG.Tech_Message(
2304 p_severity => pc_log_level_statement,
2305 p_module => v_module_name || '.worker_err',
2306 p_msg_text => 'Dimension Rule Worker Error: ' || X_errbuf
2307 );
2308
2309 FEM_ENGINES_PKG.User_Message(
2310 p_app_name => 'FEM',
2311 p_msg_text => 'Dimension Rule Worker Error: ' || X_errbuf
2312 );
2313
2314 x_completion_code := 2;
2315
2316 WHEN OTHERS THEN
2317
2318 ROLLBACK;
2319
2320 FEM_ENGINES_PKG.Tech_Message(
2321 p_severity => pc_log_level_unexpected,
2322 p_module => v_module_name || '.unexpected_exception',
2323 p_app_name => 'FEM',
2324 p_msg_name => 'FEM_GL_POST_215',
2325 p_token1 => 'ERR_MSG',
2326 p_value1 => SQLERRM
2327 );
2328
2329 FEM_ENGINES_PKG.User_Message(
2330 p_app_name => 'FEM',
2331 p_msg_name => 'FEM_GL_POST_215',
2332 p_token1 => 'ERR_MSG',
2333 p_value1 => SQLERRM
2334 );
2335
2336 FEM_ENGINES_PKG.Tech_Message(
2337 p_severity => pc_log_level_procedure,
2338 p_module => v_module_name || '.unexpected_exception',
2339 p_app_name => 'FEM',
2340 p_msg_name => 'FEM_GL_POST_203',
2341 p_token1 => 'FUNC_NAME',
2342 p_value1 => v_func_name,
2343 p_token2 => 'TIME',
2344 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2345 );
2346
2347 x_completion_code := 2;
2348
2349 END Detail_Single_Segment;
2350
2351
2352 PROCEDURE Detail_Multi_Segment(
2353 x_completion_code OUT NOCOPY NUMBER,
2354 x_row_count_tot OUT NOCOPY NUMBER,
2355 p_calling_module IN varchar default null
2356 ) is
2357 TYPE r_id_array is TABLE OF VARCHAR2(30);
2358 c_func_name CONSTANT VARCHAR2(30) := '.Detail_Multi_Segment';
2359 v_rows_processed NUMBER;
2360 v_attr_completion_code VARCHAR2(30);
2361 v_attr_row_count NUMBER;
2362 v_sql_stmt VARCHAR2(4000);
2363 v_main_gt_insert_stmt VARCHAR2(4000);
2364 v_main_insert_gt_count NUMBER;
2365 v_comp_gt_insert_stmt VARCHAR2(4000);
2366 v_cc_gt_insert_stmt VARCHAR2(4000);
2367 v_comp_insert_gt_count NUMBER;
2368 v_cc_insert_gt_count NUMBER;
2369 v_comp_member_b_count NUMBER;
2370 v_cc_member_b_count NUMBER;
2371 v_comp_member_tl_count NUMBER;
2372 v_comp_member_vl_count NUMBER;
2373 v_cc_member_tl_count NUMBER;
2374 v_cc_member_vl_count NUMBER;
2375 v_insert_member_b_stmt VARCHAR2(4000);
2376 v_insert_member_b_count NUMBER;
2377 v_insert_member_vl_stmt VARCHAR2(4000);
2378 v_insert_member_vl_count NUMBER;
2379 v_merge_stmt VARCHAR2(4000);
2380 v_merge_count NUMBER;
2381 v_insert_cc_vl_stmt VARCHAR2(4000);
2382 v_insert_comp_vl_stmt VARCHAR2(4000);
2383 v_upd_map_table_stmt VARCHAR2(4000);
2384 v_upd_map_table_count NUMBER;
2385 v_lockhandle VARCHAR2(100);
2386 v_lock_result NUMBER;
2387 v_loop_counter NUMBER;
2388 v_cols VARCHAR2(100);
2389 v_column_list VARCHAR2(1000);
2390 v_value_list VARCHAR2(1000);
2391 v_result VARCHAR2(20);
2392 v_seg1_vs_id NUMBER;
2393 v_seg2_vs_id NUMBER;
2394 v_seg3_vs_id NUMBER;
2395 v_seg4_vs_id NUMBER;
2396 v_seg5_vs_id NUMBER;
2397
2398 FEM_INTG_DIM_RULE_ulock_err EXCEPTION;
2399 FEM_INTG_DIM_RULE_attr_err EXCEPTION;
2400
2401 CURSOR ColumnList IS
2402 SELECT COLUMN_NAME
2403 FROM FEM_TAB_COLUMNS_B
2404 WHERE TABLE_NAME = 'FEM_BALANCES'
2405 AND FEM_DATA_TYPE_CODE = 'DIMENSION'
2406 AND DIMENSION_ID = FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
2407
2408 -- start bug fix 5377544
2409 v_Num_Workers NUMBER;
2410 X_errbuf VARCHAR2(2000);
2411 v_completion_code NUMBER;
2412 v_dim_rule_req_count NUMBER;
2413 FEM_INTG_DIM_RULE_worker_err EXCEPTION;
2414 -- end bug fix 5377544
2415
2416 -- Start bug Fix 5447696
2417 v_request_id NUMBER;
2418 v_gcs_vs_id NUMBER;
2419 v_fch_vs_select_stmt VARCHAR2(1000):=
2420 'SELECT 1
2421 FROM fem_global_vs_combo_defs fch_vs_combo
2422 WHERE fch_vs_combo.global_vs_combo_id = ( SELECT fch_global_vs_combo_id
2423 FROM gcs_system_options )
2424 AND fch_vs_combo.dimension_id = 8
2425 AND fch_vs_combo.value_set_id = :fem_value_set_id';
2426
2427 TYPE vs_cursor IS REF CURSOR;
2428 fch_vs_cursor vs_cursor;
2429
2430 -- End bug Fix 5447696
2431 BEGIN
2432
2433 --piush_util.put_line('Now entering FEM_INTG_NEW_DIM_MEMBER_PKG.Detail_Multi_Segment ********************');
2434
2435 x_completion_code := 0;
2436
2437 FEM_ENGINES_PKG.Tech_Message
2438 ( p_severity => pc_log_level_procedure
2439 ,p_module => pc_module_name||c_func_name
2440 ,p_app_name => 'FEM'
2441 ,p_msg_name => 'FEM_GL_POST_201'
2442 ,p_token1 => 'FUNC_NAME'
2443 ,p_value1 => pc_module_name||c_func_name
2444 ,p_token2 => 'TIME'
2445 ,p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2446
2447
2448
2449 DBMS_LOCK.ALLOCATE_UNIQUE(
2450 'FEM_INTG_DIM_RULE' || FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id,
2451 v_lockhandle,
2452 pc_expiration_secs
2453 );
2454
2455 v_loop_counter := 0;
2456
2457 LOOP
2458 IF v_loop_counter > pc_loop_counter_max
2459 THEN
2460
2461 FEM_ENGINES_PKG.Tech_Message(
2462 p_severity => pc_log_level_statement
2463 ,p_module => pc_module_name||c_func_name
2464 ,p_msg_text => 'raising FEM_INTG_DIM_RULE_ulock_err'
2465 );
2466 --piush_util.put_line('Raising exception FEM_INTG_DIM_RULE_ulock_err');
2467 RAISE FEM_INTG_DIM_RULE_ulock_err;
2468 END IF;
2469
2470 v_lock_result := DBMS_LOCK.REQUEST(
2471 v_lockhandle,
2472 pc_lockmode,
2473 pc_lock_timeout,
2474 pc_release_on_commit
2475 );
2476
2477 IF v_lock_result = 0 OR v_lock_result = 4
2478 THEN
2479 EXIT;
2480 ELSE
2481 v_loop_counter := v_loop_counter + 1;
2482
2483 FEM_ENGINES_PKG.Tech_Message(
2484 p_severity => pc_log_level_statement
2485 ,p_module => pc_module_name||c_func_name
2486 ,p_msg_text => 'sleeping ' || pc_sleep_second || ' second'
2487 );
2488
2489 DBMS_LOCK.SLEEP(pc_sleep_second);
2490 END IF;
2491 END LOOP;
2492 pv_progress := 'Start dynamic building of GT insert';
2493 --piush_util.put_line(pv_progress);
2494 x_row_count_tot := 0;
2495
2496 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'INTERCOMPANY'
2497 THEN
2498 pv_local_member_col := FEM_INTG_DIM_RULE_ENG_PKG.pv_cctr_org_member_col;
2499 ELSE
2500 pv_local_member_col := FEM_INTG_DIM_RULE_ENG_PKG.pv_member_col;
2501 END IF;
2502
2503 ------------------------------------------------------------------------------
2504 -- Build dyanmic SQL to insert all the unique combination of concatenaned
2505 -- members into GT table FEM_INTG_DIM_MEMBERS_GT
2506 ------------------------------------------------------------------------------
2507
2508 --piush_util.put_line('p_calling_module = ' || p_calling_module);
2509
2510 if p_calling_module is null then
2511
2512 --piush_util.put_line('if p_calling_module is null');
2513
2514 v_main_gt_insert_stmt
2515 := ' INSERT INTO FEM_INTG_DIM_MEMBERS_GT
2516 ( DIMENSION_ID
2517 , SEGMENT1_VALUE
2518 , SEGMENT2_VALUE
2519 , SEGMENT3_VALUE
2520 , SEGMENT4_VALUE
2521 , SEGMENT5_VALUE
2522 , CONCAT_SEGMENT_VALUE)
2523 SELECT DISTINCT
2524 :v_dim_id, '||
2525 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).application_column_name||'
2526 ,'||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).application_column_name;
2527 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 2
2528 THEN
2529 v_main_gt_insert_stmt := v_main_gt_insert_stmt || ','||
2530 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).application_column_name;
2531 ELSE
2532 v_main_gt_insert_stmt := v_main_gt_insert_stmt || ',-1';
2533 END IF;
2534
2535 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 3
2536 THEN
2537 v_main_gt_insert_stmt := v_main_gt_insert_stmt || ','||
2538 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).application_column_name;
2539 ELSE
2540 v_main_gt_insert_stmt := v_main_gt_insert_stmt || ',-1';
2541 END IF;
2542
2543 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 4
2544 THEN
2545 v_main_gt_insert_stmt := v_main_gt_insert_stmt || ','||
2546 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).application_column_name;
2547 ELSE
2548 v_main_gt_insert_stmt := v_main_gt_insert_stmt || ',-1';
2549 END IF;
2550
2551 v_main_gt_insert_stmt := v_main_gt_insert_stmt ||','
2552 ||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).application_column_name
2553 || '||''-''||'
2554 ||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).application_column_name;
2555
2556 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 2
2557 THEN
2558 v_main_gt_insert_stmt := v_main_gt_insert_stmt || '||''-''||'||
2559 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).application_column_name;
2560 END IF;
2561
2562 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 3
2563 THEN
2564 v_main_gt_insert_stmt := v_main_gt_insert_stmt || '||''-''||'||
2565 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).application_column_name;
2566 END IF;
2567
2568 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 4
2569 THEN
2570 v_main_gt_insert_stmt := v_main_gt_insert_stmt ||'||''-''||'||
2571 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).application_column_name;
2572 END IF;
2573 v_main_gt_insert_stmt := v_main_gt_insert_stmt ||
2574 ' FROM GL_CODE_COMBINATIONS GCC
2575 WHERE code_combination_id <= :v_high
2576 AND summary_flag = ''N''
2577 AND chart_of_accounts_id = :v_coa_id';
2578
2579 FEM_ENGINES_PKG.Tech_Message
2580 (p_severity => pc_log_level_statement
2581 ,p_module => pc_module_name||c_func_name
2582 ,p_app_name => 'FEM'
2583 ,p_msg_name => 'FEM_GL_POST_204'
2584 ,p_token1 => 'VAR_NAME'
2585 ,p_value1 => 'SQL Statement'
2586 ,p_token2 => 'VAR_VAL'
2587 ,p_value2 => v_main_gt_insert_stmt);
2588
2589 pv_progress := 'Before executing GT population for dimension';
2590 --piush_util.put_line(pv_progress);
2591 EXECUTE IMMEDIATE v_main_gt_insert_stmt
2592 USING
2593 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id
2594 -- ,FEM_INTG_DIM_RULE_ENG_PKG.pv_max_flex_value_id_processed + 1
2595 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped
2596 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id;
2597
2598 else
2599
2600 --piush_util.put_line('else block');
2601
2602 --piush_util.put_line('FEM_INTG_HIER_RULE_ENG_PKG.pv_hier_obj_def_id = ' || FEM_INTG_HIER_RULE_ENG_PKG.pv_hier_obj_def_id);
2603
2604 v_main_gt_insert_stmt
2605 := ' INSERT INTO FEM_INTG_DIM_MEMBERS_GT
2606 ( DIMENSION_ID
2607 , SEGMENT1_VALUE
2608 , SEGMENT2_VALUE
2609 , SEGMENT3_VALUE
2610 , SEGMENT4_VALUE
2611 , SEGMENT5_VALUE
2612 , CONCAT_SEGMENT_VALUE)
2613 SELECT DISTINCT :v_dim_id
2614 , substr(hgt.child_display_code, 1
2615 , decode(instr(hgt.child_display_code, ''-'', 1, 1), 0
2616 , length(hgt.child_display_code)
2617 , instr(hgt.child_display_code, ''-'', 1, 1)-1))
2618 , decode(instr(hgt.child_display_code, ''-'', 1, 1), 0, ''-1''
2619 , substr(hgt.child_display_code, instr(hgt.child_display_code, ''-'', 1, 1)+1
2620 , decode(instr(hgt.child_display_code, ''-'', 1, 2), 0
2621 , length(hgt.child_display_code) - instr(hgt.child_display_code, ''-'', 1, 1)
2622 , instr(hgt.child_display_code, ''-'', 1, 2)-instr(hgt.child_display_code, ''-'', 1, 1)-1)))
2623 , decode(instr(hgt.child_display_code, ''-'', 1, 2), 0, ''-1''
2624 , substr(hgt.child_display_code, instr(hgt.child_display_code, ''-'', 1, 2)+1
2625 , decode(instr(hgt.child_display_code, ''-'', 1, 3), 0
2626 , length(hgt.child_display_code) - instr(hgt.child_display_code, ''-'', 1, 2)
2627 , instr(hgt.child_display_code, ''-'', 1, 3)-instr(hgt.child_display_code, ''-'', 1, 2)-1)))
2628 , decode(instr(hgt.child_display_code, ''-'', 1, 3), 0, ''-1''
2629 , substr(hgt.child_display_code, instr(hgt.child_display_code, ''-'', 1, 3)+1
2630 , decode(instr(hgt.child_display_code, ''-'', 1, 4), 0
2631 , length(hgt.child_display_code) - instr(hgt.child_display_code, ''-'', 1, 3)
2632 , instr(hgt.child_display_code, ''-'', 1, 4)-instr(hgt.child_display_code, ''-'', 1, 3)-1)))
2633 , decode(instr(hgt.child_display_code, ''-'', 1, 4), 0, ''-1''
2634 , substr(hgt.child_display_code, instr(hgt.child_display_code, ''-'', 1, 4)+1, length(hgt.child_display_code)-instr(hgt.child_display_code, ''-'', 1, 3)))
2635 , hgt.child_display_code
2636 from FEM_INTG_DIM_HIER_GT hgt
2637 where hgt.HIERARCHY_OBJ_DEF_ID = :v_hier_obj_def_id';
2638
2639 --piush_util.put_line('v_main_gt_insert_stmt = ' || v_main_gt_insert_stmt);
2640
2641
2642 FEM_ENGINES_PKG.Tech_Message
2643 (p_severity => pc_log_level_statement
2644 ,p_module => pc_module_name||c_func_name
2645 ,p_app_name => 'FEM'
2646 ,p_msg_name => 'FEM_GL_POST_204'
2647 ,p_token1 => 'VAR_NAME'
2648 ,p_value1 => 'SQL Statement'
2649 ,p_token2 => 'VAR_VAL'
2650 ,p_value2 => v_main_gt_insert_stmt);
2651
2652 EXECUTE IMMEDIATE v_main_gt_insert_stmt
2653 USING FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id
2654 , FEM_INTG_HIER_RULE_ENG_PKG.pv_hier_obj_def_id;
2655
2656 end if;
2657
2658 --piush_util.put_line('Execute v_main_gt_insert_stmt');
2659
2660 v_main_insert_gt_count := SQL%ROWCOUNT;
2661
2662 --piush_util.put_line('Number of rows inserted = ' || SQL%ROWCOUNT);
2663
2664 FEM_ENGINES_PKG.Tech_Message
2665 (p_severity => pc_log_level_statement
2666 ,p_module => pc_module_name||c_func_name
2667 ,p_app_name => 'FEM'
2668 ,p_msg_name => 'FEM_GL_POST_216'
2669 ,p_token1 => 'TABLE'
2670 ,p_value1 => 'FEM_INTG_DIM_MEMBERS_GT'
2671 ,p_token2 => 'NUM'
2672 ,p_value2 => v_main_insert_gt_count);
2673
2674
2675 pv_progress := 'After executing GT population for dimension';
2676 --piush_util.put_line(pv_progress);
2677
2678 -------------------------------------------------------------------
2679 -- ***** MEMBER TABLE POPULATION ******
2680 --
2681 -- Build dyanmic SQL to insert new members into FEM mebers table
2682 -- only new members will be inserted into the table
2683 -------------------------------------------------------------------
2684 v_insert_member_b_stmt :=
2685 'INSERT INTO '||
2686 FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name||' ( '||
2687 pv_local_member_col||'
2688 , value_set_id
2689 , dimension_group_id
2690 , '||FEM_INTG_DIM_RULE_ENG_PKG.pv_member_display_code_col||'
2691 , enabled_flag
2692 , personal_flag
2693 , creation_date
2694 , created_by
2695 , last_updated_by
2696 , last_update_date
2697 , last_update_login
2698 , object_version_number
2699 , read_only_flag)
2700 SELECT
2701 fnd_flex_values_s.nextval
2702 , :v_fem_vs_id
2703 , null
2704 , concat_segment_value
2705 , ''Y''
2706 , ''N''
2707 , sysdate
2708 , :v_userid
2709 , :v_userid
2710 , sysdate
2711 , :v_login_id
2712 , 1
2713 , ''N''
2714 FROM fem_intg_dim_members_gt tab1
2715 WHERE NOT EXISTS (SELECT ''x''
2716 FROM ' || -- Bug 4393061 - changed read_only_flag to 'N'
2717 FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name ||' tab2
2718 WHERE tab2.value_set_id = :v_fem_vs_id
2719 AND tab1.concat_segment_value
2720 = tab2.'||
2721 FEM_INTG_DIM_RULE_ENG_PKG.pv_member_display_code_col
2722 ||')';
2723
2724 pv_progress := 'Before executing member_b population';
2725 --piush_util.put_line(pv_progress);
2726 --piush_util.put_line('v_insert_member_b_stmt = ' || v_insert_member_b_stmt);
2727
2728 FEM_ENGINES_PKG.Tech_Message
2729 (p_severity => pc_log_level_statement
2730 ,p_module => pc_module_name||c_func_name
2731 ,p_app_name => 'FEM'
2732 ,p_msg_name => 'FEM_GL_POST_204'
2733 ,p_token1 => 'VAR_NAME'
2734 ,p_value1 => 'SQL Statement'
2735 ,p_token2 => 'VAR_VAL'
2736 ,p_value2 => v_insert_member_b_stmt);
2737
2738 EXECUTE IMMEDIATE v_insert_member_b_stmt
2739 USING
2740 FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id
2741 , pv_user_id
2742 , pv_user_id
2743 , pv_login_id
2744 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id;
2745
2746 v_insert_member_b_count := SQL%ROWCOUNT;
2747
2748 FEM_ENGINES_PKG.Tech_Message
2749 (p_severity => pc_log_level_statement
2750 ,p_module => pc_module_name||c_func_name
2751 ,p_app_name => 'FEM'
2752 ,p_msg_name => 'FEM_GL_POST_216'
2753 ,p_token1 => 'TABLE'
2754 ,p_value1 => FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name
2755 ,p_token2 => 'NUM'
2756 ,p_value2 => v_insert_member_b_count);
2757
2758 pv_progress := 'after executing member_b population';
2759 --piush_util.put_line(pv_progress);
2760
2761 --------------------------------------------------------
2762 ------------Insert into Member_TL table ----------------
2763 --------------------------------------------------------
2764
2765 FOR i in 1..fem_intg_dim_rule_eng_pkg.pv_segment_count
2766 LOOP
2767 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(i).table_validated_flag,'N') = 'Y'
2768 AND fem_intg_dim_rule_eng_pkg.pv_mapped_segs(i).meaning_col_name is NULL
2769 THEN
2770 fem_intg_dim_rule_eng_pkg.pv_mapped_segs(i).meaning_col_name := ''' ''';
2771 END IF;
2772 END LOOP;
2773
2774 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(1).table_validated_flag, 'N') = 'Y'
2775 THEN
2776 v_seg1_vs_id := -99;
2777 ELSE
2778 v_seg1_vs_id := fem_intg_dim_rule_eng_pkg.pv_mapped_segs(1).vs_id;
2779 END IF;
2780
2781 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).table_validated_flag, 'N') = 'Y'
2782 THEN
2783 v_seg2_vs_id := -99;
2784 ELSE
2785 v_seg2_vs_id := fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).vs_id;
2786 END IF;
2787
2788 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(3).table_validated_flag, 'N') = 'Y'
2789 THEN
2790 v_seg3_vs_id := -99;
2791 ELSE
2792 v_seg3_vs_id := fem_intg_dim_rule_eng_pkg.pv_mapped_segs(3).vs_id;
2793 END IF;
2794
2795 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(4).table_validated_flag, 'N') = 'Y'
2796 THEN
2797 v_seg4_vs_id := -99;
2798 ELSE
2799 v_seg4_vs_id := fem_intg_dim_rule_eng_pkg.pv_mapped_segs(4).vs_id;
2800 END IF;
2801
2802 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(5).table_validated_flag, 'N') = 'Y'
2803 THEN
2804 v_seg5_vs_id := -99;
2805 ELSE
2806 v_seg5_vs_id := fem_intg_dim_rule_eng_pkg.pv_mapped_segs(5).vs_id;
2807 END IF;
2808
2809
2810 v_merge_stmt:= 'Merge into ' || fem_intg_dim_rule_eng_pkg.pv_member_tl_table_name || ' TL
2811 USING(';
2812
2813 v_merge_stmt := v_merge_stmt || 'SELECT tab1.'||pv_local_member_col||' MEM_COL
2814 , tab1.value_set_id VAL_SET_ID
2815 , fil.language_code MEM_LANG
2816 , fil_source.language_code LANG_CODE
2817 , '||fem_intg_dim_rule_eng_pkg.pv_member_display_code_col || ' DISP_CODE_COL';
2818 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(1).table_validated_flag,'N') = 'Y'
2819 THEN
2820 v_merge_stmt := v_merge_stmt||',SUBSTR(TL1.DESCR,1,50)';
2821 ELSE
2822 v_merge_stmt := v_merge_stmt||',SUBSTR(TL1.description,1,50)';
2823 END IF;
2824
2825 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).table_validated_flag,'N') = 'Y'
2826 THEN
2827 v_merge_stmt := v_merge_stmt||'||''-''||SUBSTR(TL2.DESCR,1,50)';
2828 ELSE
2829 v_merge_stmt := v_merge_stmt||'||''-''||SUBSTR(TL2.description,1,50)';
2830 END IF;
2831
2832
2833 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 2
2834 THEN
2835 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(3).table_validated_flag,'N') = 'Y'
2836 THEN
2837 v_merge_stmt := v_merge_stmt||
2838 '||''-''||SUBSTR(TL3.DESCR,1,50)';
2839 ELSE
2840 v_merge_stmt := v_merge_stmt||'||''-''||SUBSTR(TL3.description,1,50)';
2841 END IF;
2842 END IF;
2843
2844 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 3
2845 THEN
2846 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(4).table_validated_flag,'N') = 'Y'
2847 THEN
2848 v_merge_stmt := v_merge_stmt||
2849 '||''-''||SUBSTR(TL4.DESCR,1,50)';
2850 ELSE
2851 v_merge_stmt := v_merge_stmt || '||''-''||SUBSTR(TL4.description,1,50)';
2852 END IF;
2853 END IF;
2854
2855 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 4
2856 THEN
2857 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(5).table_validated_flag,'N') = 'Y'
2858 THEN
2859 v_merge_stmt := v_merge_stmt||
2860 '||''-''||SUBSTR(TL5.DESCR,1,50)';
2861 ELSE
2862 v_merge_stmt := v_merge_stmt || '||''-''||SUBSTR(TL5.description,1,50)';
2863 END IF;
2864 END IF;
2865
2866 v_merge_stmt := v_merge_stmt || ' MEMB_DESC';
2867 v_merge_stmt := v_merge_stmt ||
2868 ',sysdate CREATED_DATE
2869 ,:v_userid CREATED_BY
2870 ,:v_userid UPDATED_BY
2871 ,sysdate UPDATED_DATE
2872 ,:v_login_id UPDATE_LOGIN
2873 FROM '||fem_intg_dim_rule_eng_pkg.pv_member_b_table_name ||' tab1,
2874 fem_intg_dim_members_gt GT
2875 ,fnd_languages fil
2876 ,fnd_languages fil_source';
2877
2878 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(1).table_validated_flag,'N') = 'Y'
2879 THEN
2880 v_merge_stmt := v_merge_stmt|| ',( SELECT '
2881 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).meaning_col_name
2882 ||' DESCR ,'
2883 ||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).val_col_name
2884 || ' flex_value FROM '
2885 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).table_name || ' '
2886 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).where_clause || ') TL1';
2887 ELSE
2888 v_merge_stmt := v_merge_stmt
2889 ||' ,fnd_flex_values flex1
2890 ,fnd_flex_values_tl TL1';
2891 END IF;
2892
2893 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).table_validated_flag,'N') = 'Y'
2894 THEN
2895 v_merge_stmt := v_merge_stmt|| ',( SELECT '
2896 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).meaning_col_name
2897 ||' DESCR ,'
2898 ||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).val_col_name
2899 || ' flex_value FROM '
2900 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).table_name || ' '
2901 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).where_clause || ') TL2';
2902 ELSE
2903 v_merge_stmt := v_merge_stmt
2904 ||' ,fnd_flex_values flex2
2905 ,fnd_flex_values_tl TL2';
2906 END IF;
2907
2908 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 2
2909 THEN
2910 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(3).table_validated_flag,'N') = 'Y'
2911 THEN
2912 v_merge_stmt := v_merge_stmt|| ',( SELECT '
2913 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).meaning_col_name
2914 ||' DESCR ,'
2915 ||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).val_col_name
2916 || ' flex_value FROM '
2917 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).table_name || ' '
2918 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).where_clause || ') TL3';
2919 ELSE
2920 v_merge_stmt := v_merge_stmt
2921 ||',fnd_flex_values flex3
2922 , fnd_flex_values_tl TL3';
2923 END IF;
2924 END IF;
2925
2926 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 3
2927 THEN
2928 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(4).table_validated_flag,'N') = 'Y'
2929 THEN
2930 v_merge_stmt := v_merge_stmt|| ',( SELECT '
2931 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).meaning_col_name
2932 ||' DESCR ,'
2933 ||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).val_col_name
2934 || ' flex_value FROM '
2935 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).table_name || ' '
2936 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).where_clause || ') TL4';
2937 ELSE
2938 v_merge_stmt := v_merge_stmt
2939 ||',fnd_flex_values flex4
2940 , fnd_flex_values_tl TL4';
2941 END IF;
2942 END IF;
2943
2944 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 4
2945 THEN
2946 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(5).table_validated_flag,'N') = 'Y'
2947 THEN
2948 v_merge_stmt := v_merge_stmt|| ',( SELECT '
2949 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).meaning_col_name
2950 ||' DESCR ,'
2951 ||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).val_col_name
2952 || ' flex_value FROM '
2953 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).table_name || ' '
2954 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).where_clause || ') TL5';
2955 ELSE
2956 v_merge_stmt := v_merge_stmt
2957 ||',fnd_flex_values flex5
2958 ,fnd_flex_values_tl TL5';
2959 END IF;
2960 END IF;
2961
2962 v_merge_stmt := v_merge_stmt
2963 || ' WHERE fil.installed_flag in (''B'', ''I'')
2964 AND fil_source.installed_flag = ''B''
2965 AND GT.concat_segment_value = tab1.'||
2966 fem_intg_dim_rule_eng_pkg.pv_member_display_code_col||'
2967 AND GT.dimension_id = :v_dim_id
2968 AND tab1.value_set_id = :v_fem_vs_id';
2969
2970 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(1).table_validated_flag,'N') = 'Y'
2971 THEN
2972 v_merge_stmt := v_merge_stmt ||'
2973 AND -99 = :map_seg1_vs_id
2974 AND TL1.flex_value = gt.segment1_value';
2975 ELSE
2976 v_merge_stmt := v_merge_stmt ||'
2977 AND TL1.language = fil.language_code
2978 AND flex1.flex_value_set_id = :map_seg1_vs_id
2979 AND flex1.flex_value_id = TL1.flex_value_id
2980 AND flex1.flex_value = gt.segment1_value';
2981 END IF;
2982
2983
2984 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).table_validated_flag,'N') = 'Y'
2985 THEN
2986 v_merge_stmt := v_merge_stmt ||'
2987 AND -99 = :map_seg2_vs_id
2988 AND TL2.flex_value = gt.segment2_value';
2989 ELSE
2990 v_merge_stmt := v_merge_stmt ||'
2991 AND TL2.language = fil.language_code
2992 AND flex2.flex_value_set_id = :map_seg2_vs_id
2993 AND flex2.flex_value_id = TL2.flex_value_id
2994 AND flex2.flex_value = gt.segment2_value';
2995 IF fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).dependent_value_set_flag = 'Y'
2996 THEN
2997 v_merge_stmt := v_merge_stmt || '
2998 AND flex2.parent_flex_value_low = '
2999 ||fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).dependent_segment_column;
3000 END IF;
3001 END IF;
3002
3003 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 2
3004 THEN
3005 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(3).table_validated_flag,'N') = 'Y'
3006 THEN
3007 v_merge_stmt := v_merge_stmt ||'
3008 AND -99 = :map_seg3_vs_id
3009 AND TL3.flex_value = gt.segment3_value';
3010 ELSE
3011 v_merge_stmt := v_merge_stmt || '
3012 AND TL3.language = fil.language_code
3013 AND flex3.flex_value_set_id = :map_seg3_vs_id
3014 AND flex3.flex_value_id = TL3.flex_value_id
3015 AND flex3.flex_value = gt.segment3_value';
3016 IF fem_intg_dim_rule_eng_pkg.pv_mapped_segs(3).dependent_value_set_flag = 'Y'
3017 THEN
3018 v_merge_stmt := v_merge_stmt || '
3019 AND flex3.parent_flex_value_low = '
3020 ||fem_intg_dim_rule_eng_pkg.pv_mapped_segs(3).dependent_segment_column;
3021 END IF;
3022 END IF;
3023 END IF;
3024
3025 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 3
3026 THEN
3027 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(4).table_validated_flag,'N') = 'Y'
3028 THEN
3029 v_merge_stmt := v_merge_stmt ||'
3030 AND -99 = :map_seg4_vs_id
3031 AND TL4.flex_value = gt.segment4_value';
3032 ELSE
3033 v_merge_stmt := v_merge_stmt || '
3034 AND TL4.language = fil.language_code
3035 AND flex4.flex_value_set_id = :map_seg4_vs_id
3036 AND flex4.flex_value_id = TL4.flex_value_id
3037 AND flex4.flex_value = gt.segment4_value';
3038 IF fem_intg_dim_rule_eng_pkg.pv_mapped_segs(4).dependent_value_set_flag = 'Y'
3039 THEN
3040 v_merge_stmt := v_merge_stmt || '
3041 AND flex4.parent_flex_value_low = '||fem_intg_dim_rule_eng_pkg.pv_mapped_segs(4).dependent_segment_column;
3042 END IF;
3043 END IF;
3044 END IF;
3045
3046 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 4
3047 THEN
3048 IF NVL(fem_intg_dim_rule_eng_pkg.pv_mapped_segs(5).table_validated_flag,'N') = 'Y'
3049 THEN
3050 v_merge_stmt := v_merge_stmt ||'
3051 AND -99 = :map_seg5_vs_id
3052 AND TL5.flex_value = gt.segment5_value';
3053 ELSE
3054 v_merge_stmt := v_merge_stmt || '
3055 AND TL5.language = fil.language_code
3056 AND flex5.flex_value_set_id = :map_seg5_vs_id
3057 AND flex5.flex_value_id = TL5.flex_value_id
3058 AND flex5.flex_value = gt.segment5_value';
3059 IF fem_intg_dim_rule_eng_pkg.pv_mapped_segs(5).dependent_value_set_flag = 'Y'
3060 THEN
3061 v_merge_stmt := v_merge_stmt || '
3062 AND flex5.parent_flex_value_low = '
3063 ||fem_intg_dim_rule_eng_pkg.pv_mapped_segs(5).dependent_segment_column;
3064 END IF;
3065 END IF;
3066 END IF;
3067
3068 v_merge_stmt := v_merge_stmt || ') D
3069 ON(
3070 TL.VALUE_SET_ID = D.VAL_SET_ID
3071 AND TL.LANGUAGE = D.MEM_LANG
3072 AND TL.'||pv_local_member_col||' = D.MEM_COL';
3073
3074
3075 v_merge_stmt := v_merge_stmt|| ')
3076 WHEN MATCHED THEN UPDATE
3077 SET TL.DESCRIPTION = D.MEMB_DESC
3078 WHEN NOT MATCHED THEN Insert ('||
3079 pv_local_member_col||',
3080 VALUE_SET_ID
3081 , LANGUAGE
3082 , SOURCE_LANG
3083 , ' ||fem_intg_dim_rule_eng_pkg.pv_member_name_col||'
3084 , DESCRIPTION
3085 , CREATION_DATE
3086 , CREATED_BY
3087 , LAST_UPDATED_BY
3088 , LAST_UPDATE_DATE
3089 , LAST_UPDATE_LOGIN )
3090 VALUES(
3091 D.MEM_COL,
3092 D.VAL_SET_ID,
3093 D.MEM_LANG,
3094 D.LANG_CODE,
3095 D.DISP_CODE_COL,
3096 D.MEMB_DESC,
3097 D.CREATED_DATE,
3098 D.CREATED_BY,
3099 D.UPDATED_BY,
3100 D.UPDATED_DATE,
3101 D.UPDATE_LOGIN)';
3102
3103
3104 FEM_ENGINES_PKG.Tech_Message
3105 (
3106 p_severity => pc_log_level_statement
3107 ,p_module => pc_module_name||c_func_name
3108 ,p_msg_text => v_merge_stmt);
3109
3110 --
3111 -- Execute built statement for inserting dimension members
3112 --
3113 CASE fem_intg_dim_rule_eng_pkg.pv_segment_count
3114 WHEN 1
3115 THEN
3116 EXECUTE IMMEDIATE v_merge_stmt
3117 USING
3118 pv_user_id
3119 ,pv_user_id
3120 ,pv_login_id
3121 ,fem_intg_dim_rule_eng_pkg.pv_dim_id
3122 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id
3123 ,v_seg1_vs_id;
3124 -- ,FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id;
3125 WHEN 2
3126 THEN
3127 EXECUTE IMMEDIATE v_merge_stmt
3128 USING
3129 pv_user_id
3130 ,pv_user_id
3131 ,pv_login_id
3132 ,fem_intg_dim_rule_eng_pkg.pv_dim_id
3133 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id
3134 ,v_seg1_vs_id
3135 ,v_seg2_vs_id;
3136 -- ,FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id;
3137 WHEN 3
3138 THEN
3139 EXECUTE IMMEDIATE v_merge_stmt
3140 USING
3141 pv_user_id
3142 ,pv_user_id
3143 ,pv_login_id
3144 ,fem_intg_dim_rule_eng_pkg.pv_dim_id
3145 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id
3146 ,v_seg1_vs_id
3147 ,v_seg2_vs_id
3148 ,v_seg3_vs_id;
3149 -- ,FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id;
3150 WHEN 4
3151 THEN
3152 EXECUTE IMMEDIATE v_merge_stmt
3153 USING
3154 pv_user_id
3155 ,pv_user_id
3156 ,pv_login_id
3157 ,fem_intg_dim_rule_eng_pkg.pv_dim_id
3158 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id
3159 ,v_seg1_vs_id
3160 ,v_seg2_vs_id
3161 ,v_seg3_vs_id
3162 ,v_seg4_vs_id;
3163 -- ,FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id;
3164 WHEN 5
3165 THEN
3166 EXECUTE IMMEDIATE v_merge_stmt
3167 USING
3168 pv_user_id
3169 ,pv_user_id
3170 ,pv_login_id
3171 ,fem_intg_dim_rule_eng_pkg.pv_dim_id
3172 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id
3173 ,v_seg1_vs_id
3174 ,v_seg2_vs_id
3175 ,v_seg3_vs_id
3176 ,v_seg4_vs_id
3177 ,v_seg5_vs_id;
3178 -- ,FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id;
3179 END CASE;
3180
3181 v_merge_count := SQL%ROWCOUNT;
3182
3183
3184 FEM_ENGINES_PKG.Tech_Message
3185 (p_severity => pc_log_level_statement
3186 ,p_module => pc_module_name||c_func_name
3187 ,p_app_name => 'FEM'
3188 ,p_msg_name => 'FEM_GL_POST_216'
3189 ,p_token1 => 'TABLE'
3190 ,p_value1 => FEM_INTG_DIM_RULE_ENG_PKG.pv_member_tl_table_name
3191 ,p_token2 => 'NUM'
3192 ,p_value2 => v_merge_count);
3193
3194 pv_progress := 'after executing member_tl population';
3195
3196
3197 /* MEMBER TABLE POPULATION
3198 * =======================
3199 *
3200 * Build dyanmic SQL to insert new members into FEM mebers table
3201 * Only new members will be inserted into the table
3202 */
3203
3204 IF upper( FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name) = 'FEM_CCTR_ORGS_B'
3205 THEN
3206 FEM_ENGINES_PKG.Tech_Message
3207 (
3208 p_severity => pc_log_level_event
3209 ,p_module => pc_module_name||c_func_name
3210 ,p_msg_text => 'Processing dimension is of type CCTR-ORG');
3211
3212 pv_progress := 'Before creating dynamic GT insert for Company';
3213 --piush_util.put_line(pv_progress);
3214 v_comp_gt_insert_stmt :=
3215 'INSERT INTO FEM_INTG_DIM_MEMBERS_GT GT
3216 ( DIMENSION_ID
3217 , SEGMENT1_VALUE
3218 , SEGMENT2_VALUE
3219 , SEGMENT3_VALUE
3220 , SEGMENT4_VALUE
3221 , SEGMENT5_VALUE
3222 , CONCAT_SEGMENT_VALUE)
3223 SELECT DISTINCT
3224 :v_dest_dim_id
3225 , -1
3226 , -1
3227 , -1
3228 , -1
3229 , -1
3230 , segment1_value
3231 FROM FEM_INTG_DIM_MEMBERS_GT GT2
3232 WHERE GT2.dimension_id = :v_dim_id';
3233
3234
3235 FEM_ENGINES_PKG.Tech_Message
3236 (p_severity => pc_log_level_statement
3237 ,p_module => pc_module_name||c_func_name
3238 ,p_app_name => 'FEM'
3239 ,p_msg_name => 'FEM_GL_POST_204'
3240 ,p_token1 => 'VAR_NAME'
3241 ,p_value1 => 'SQL Statement'
3242 ,p_token2 => 'VAR_VAL'
3243 ,p_value2 => v_comp_gt_insert_stmt);
3244
3245 -- Execute population of GT table for company dimension members
3246 EXECUTE IMMEDIATE v_comp_gt_insert_stmt
3247 USING FEM_INTG_DIM_RULE_ENG_PKG.pv_com_dim_id
3248 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
3249
3250 v_comp_insert_gt_count := SQL%ROWCOUNT;
3251 FEM_ENGINES_PKG.Tech_Message
3252 (p_severity => pc_log_level_statement
3253 ,p_module => pc_module_name||c_func_name
3254 ,p_app_name => 'FEM'
3255 ,p_msg_name => 'FEM_GL_POST_216'
3256 ,p_token1 => 'TABLE'
3257 ,p_value1 => 'FEM_INTG_DIM_MEMBERS_GT (for Comp dim)'
3258 ,p_token2 => 'NUM'
3259 ,p_value2 => v_comp_insert_gt_count);
3260
3261
3262 -- Insert individual dimension members for company
3263
3264 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).table_validated_flag = 'N'
3265 THEN
3266 pv_progress := 'Before insert into fem_companies_b';
3267 --piush_util.put_line(pv_progress);
3268 INSERT INTO fem_companies_b
3269 (
3270 company_id,
3271 value_set_id,
3272 company_display_code,
3273 enabled_flag,
3274 personal_flag,
3275 creation_date,
3276 created_by,
3277 last_updated_by,
3278 last_update_date,
3279 last_update_login,
3280 read_only_flag,
3281 object_version_number
3282 )
3283 SELECT flex.FLEX_VALUE_ID
3284 ,fem_intg_dim_rule_eng_pkg.pv_com_vs_id
3285 ,tab1.concat_segment_value
3286 ,'Y'
3287 ,'N'
3288 ,SYSDATE
3289 ,pv_user_id
3290 ,pv_user_id
3291 ,SYSDATE
3292 ,pv_login_id
3293 ,'N' -- Bug 4393061 - changed read_only_flag to 'N'
3294 ,1
3295 FROM fem_intg_dim_members_gt tab1
3296 ,fnd_flex_values flex
3297 WHERE dimension_id = fem_intg_dim_rule_eng_pkg.pv_com_dim_id
3298 AND flex.FLEX_VALUE_SET_ID = fem_intg_dim_rule_eng_pkg.pv_mapped_segs(1).vs_id
3299 AND flex.flex_value = tab1.concat_segment_value
3300 AND not exists ( SELECT 'x'
3301 FROM fem_companies_b tab2
3302 WHERE tab2.value_set_id = fem_intg_dim_rule_eng_pkg.pv_com_vs_id
3303 AND tab1.concat_segment_value = tab2.company_display_code);
3304
3305 v_comp_member_b_count := SQL%ROWCOUNT;
3306
3307 FEM_ENGINES_PKG.Tech_Message
3308 (p_severity => pc_log_level_statement
3309 ,p_module => pc_module_name||c_func_name
3310 ,p_app_name => 'FEM'
3311 ,p_msg_name => 'FEM_GL_POST_216'
3312 ,p_token1 => 'TABLE'
3313 ,p_value1 => 'FEM_COMPANIES_B'
3314 ,p_token2 => 'NUM'
3315 ,p_value2 => v_comp_member_b_count);
3316
3317 pv_progress := 'Before insert into fem_companies_tl';
3318 --piush_util.put_line(pv_progress);
3319
3320 INSERT INTO fem_companies_tl
3321 (
3322 company_id,
3323 value_set_id,
3324 language,
3325 source_lang,
3326 company_name,
3327 description,
3328 creation_date,
3329 created_by,
3330 last_updated_by,
3331 last_update_date,
3332 last_update_login
3333 )
3334 SELECT TL.FLEX_VALUE_ID
3335 ,tab1.value_set_id
3336 ,TL.language
3337 ,TL.source_lang
3338 ,tab1.company_display_code
3339 ,TL.description
3340 ,SYSDATE
3341 ,pv_user_id
3342 ,pv_user_id
3343 ,SYSDATE
3344 ,pv_login_id
3345 FROM fem_companies_b tab1
3346 ,fnd_flex_values_tl TL
3347 WHERE tab1.value_set_id = fem_intg_dim_rule_eng_pkg.pv_com_vs_id
3348 AND tab1.company_id = TL.flex_value_id
3349 AND not exists ( SELECT 'x'
3350 FROM fem_companies_tl tab2
3351 WHERE tab1.value_set_id = tab2.value_set_id
3352 AND tab1.company_id = tab2.company_id
3353 AND TL.language = tab2.language );
3354
3355 v_comp_member_tl_count := SQL%ROWCOUNT;
3356
3357 FEM_ENGINES_PKG.Tech_Message
3358 (p_severity => pc_log_level_statement
3359 ,p_module => pc_module_name||c_func_name
3360 ,p_app_name => 'FEM'
3361 ,p_msg_name => 'FEM_GL_POST_216'
3362 ,p_token1 => 'TABLE'
3363 ,p_value1 => 'FEM_COMPANIES_TL'
3364 ,p_token2 => 'NUM'
3365 ,p_value2 => v_comp_member_tl_count);
3366
3367 ELSE /* table validated value set */
3368
3369 pv_progress := 'Before insert into fem_companies_vl';
3370 --piush_util.put_line(pv_progress);
3371
3372 v_insert_comp_vl_stmt := 'INSERT INTO fem_companies_vl
3373 (
3374 company_id,
3375 value_set_id,
3376 company_display_code,
3377 enabled_flag,
3378 personal_flag,
3379 creation_date,
3380 created_by,
3381 last_updated_by,
3382 last_update_date,
3383 last_update_login,
3384 read_only_flag,
3385 object_version_number,
3386 company_name,
3387 description
3388 )
3389 SELECT FND_FLEX_VALUES_S.nextval
3390 ,:v_seg1_vs_id
3391 ,concat_segment_value
3392 ,''Y''
3393 ,''N''
3394 ,SYSDATE
3395 ,:v_user_id
3396 ,:v_user_id
3397 ,SYSDATE
3398 ,:v_login_id
3399 ,''N''
3400 ,1
3401 ,concat_segment_value
3402 ,flex.descr
3403 FROM fem_intg_dim_members_gt tab1';
3404
3405 v_insert_comp_vl_stmt := v_insert_comp_vl_stmt|| ',( SELECT '
3406 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).meaning_col_name
3407 ||' DESCR ,'
3408 ||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).val_col_name
3409 || ' flex_value FROM '
3410 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).table_name || ' '
3411 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).where_clause || ') FLEX';
3412
3413 v_insert_comp_vl_stmt := v_insert_comp_vl_stmt|| '
3414 WHERE dimension_id = :v_com_dim_id
3415 AND flex.flex_value = tab1.concat_segment_value
3416 AND not exists ( SELECT ''x''
3417 FROM fem_companies_vl tab2
3418 WHERE :v_seg1_vs_id = tab2.value_set_id
3419 AND tab1.concat_segment_value = tab2.company_display_code)';
3420
3421 FEM_ENGINES_PKG.Tech_Message
3422 (p_severity => pc_log_level_statement
3423 ,p_module => pc_module_name||c_func_name
3424 ,p_msg_text => 'Executing SQL Statement: '||v_insert_comp_vl_stmt||
3425 'Using: '||fem_intg_dim_rule_eng_pkg.pv_mapped_segs(1).vs_id
3426 ||','||pv_user_id
3427 ||','||pv_user_id
3428 ||','||pv_login_id
3429 ||','||fem_intg_dim_rule_eng_pkg.pv_com_dim_id
3430 ||','||fem_intg_dim_rule_eng_pkg.pv_mapped_segs(1).vs_id);
3431
3432 EXECUTE IMMEDIATE v_insert_comp_vl_stmt
3433 USING fem_intg_dim_rule_eng_pkg.pv_com_vs_id,
3434 pv_user_id,
3435 pv_user_id,
3436 pv_login_id,
3437 fem_intg_dim_rule_eng_pkg.pv_com_dim_id,
3438 fem_intg_dim_rule_eng_pkg.pv_com_vs_id;
3439
3440 v_comp_member_vl_count := SQL%ROWCOUNT;
3441
3442 FEM_ENGINES_PKG.Tech_Message
3443 (p_severity => pc_log_level_statement
3444 ,p_module => pc_module_name||c_func_name
3445 ,p_app_name => 'FEM'
3446 ,p_msg_name => 'FEM_GL_POST_216'
3447 ,p_token1 => 'TABLE'
3448 ,p_value1 => 'FEM_COMPANIES_VL'
3449 ,p_token2 => 'NUM'
3450 ,p_value2 => v_comp_member_vl_count);
3451
3452 END IF;
3453
3454
3455 -- Execute population of GT table for cost center dimension members
3456
3457 pv_progress := 'Before building dynamic stmt for CostCenter GT INSERT';
3458 --piush_util.put_line(pv_progress);
3459 v_cc_gt_insert_stmt :=
3460 'INSERT INTO FEM_INTG_DIM_MEMBERS_GT GT
3461 ( DIMENSION_ID
3462 , SEGMENT1_VALUE
3463 , SEGMENT2_VALUE
3464 , SEGMENT3_VALUE
3465 , SEGMENT4_VALUE
3466 , SEGMENT5_VALUE
3467 , CONCAT_SEGMENT_VALUE)
3468 SELECT DISTINCT
3469 :v_dest_dim_id
3470 , -1
3471 , -1
3472 , -1
3473 , -1
3474 , -1
3475 , segment2_value
3476 FROM FEM_INTG_DIM_MEMBERS_GT GT2
3477 WHERE GT2.dimension_id = :v_dim_id';
3478
3479 pv_progress := 'Before EXECUTION of CostCenter GT INSERT';
3480 --piush_util.put_line(pv_progress);
3481
3482 EXECUTE IMMEDIATE v_cc_gt_insert_stmt
3483 USING FEM_INTG_DIM_RULE_ENG_PKG.pv_cc_dim_id
3484 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
3485
3486 v_cc_insert_gt_count := SQL%ROWCOUNT;
3487
3488 FEM_ENGINES_PKG.Tech_Message
3489 (p_severity => pc_log_level_statement
3490 ,p_module => pc_module_name||c_func_name
3491 ,p_app_name => 'FEM'
3492 ,p_msg_name => 'FEM_GL_POST_216'
3493 ,p_token1 => 'TABLE'
3494 ,p_value1 => 'FEM_INTG_DIM_MEMBERS_GT (FOR CC DIM)'
3495 ,p_token2 => 'NUM'
3496 ,p_value2 => v_cc_insert_gt_count);
3497
3498
3499 -- Insert individual dimension members for cost center
3500
3501
3502 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).table_validated_flag = 'N'
3503 THEN
3504 pv_progress := 'Before insert into fem_cost_centers_b';
3505 --piush_util.put_line(pv_progress);
3506 IF fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).dependent_value_set_flag = 'Y'
3507 THEN
3508 INSERT INTO fem_cost_centers_b
3509 (
3510 cost_center_id,
3511 value_set_id,
3512 cost_center_display_code,
3513 enabled_flag,
3514 personal_flag,
3515 creation_date,
3516 created_by,
3517 last_updated_by,
3518 last_update_date,
3519 last_update_login,
3520 read_only_flag,
3521 object_version_number
3522 )
3523 SELECT flex.FLEX_VALUE_ID
3524 ,fem_intg_dim_rule_eng_pkg.pv_cc_vs_id
3525 ,segment2_value
3526 ,'Y'
3527 ,'N'
3528 ,SYSDATE
3529 ,pv_user_id
3530 ,pv_user_id
3531 ,SYSDATE
3532 ,pv_login_id
3533 ,'N'
3534 ,1
3535 FROM fem_intg_dim_members_gt tab1
3536 ,fnd_flex_values flex
3537 WHERE dimension_id = fem_intg_dim_rule_eng_pkg.pv_dim_id /* Because dependent VS*/
3538 AND flex.FLEX_VALUE_SET_ID = fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).vs_id
3539 AND flex.parent_flex_value_low = tab1.segment1_value
3540 AND flex.flex_value = tab1.segment2_value
3541 AND not exists ( SELECT 'x'
3542 FROM fem_cost_centers_b tab2
3543 WHERE tab2.value_set_id = fem_intg_dim_rule_eng_pkg.pv_cc_vs_id
3544 AND tab1.segment2_value = tab2.cost_center_display_code); v_cc_member_b_count := SQL%ROWCOUNT;
3545
3546 ELSE /* Independent value set */
3547 INSERT INTO fem_cost_centers_b
3548 (
3549 cost_center_id,
3550 value_set_id,
3551 cost_center_display_code,
3552 enabled_flag,
3553 personal_flag,
3554 creation_date,
3555 created_by,
3556 last_updated_by,
3557 last_update_date,
3558 last_update_login,
3559 read_only_flag,
3560 object_version_number
3561 )
3562 SELECT flex.FLEX_VALUE_ID
3563 ,fem_intg_dim_rule_eng_pkg.pv_cc_vs_id
3564 ,concat_segment_value
3565 ,'Y'
3566 ,'N'
3567 ,SYSDATE
3568 ,pv_user_id
3569 ,pv_user_id
3570 ,SYSDATE
3571 ,pv_login_id
3572 ,'N' -- Bug 4393061 - changed read_only_flag to 'N'
3573 ,1
3574 FROM fem_intg_dim_members_gt tab1
3575 ,fnd_flex_values flex
3576 WHERE dimension_id = fem_intg_dim_rule_eng_pkg.pv_cc_dim_id
3577 AND flex.FLEX_VALUE_SET_ID = fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).vs_id
3578 AND flex.flex_value = tab1.concat_segment_value
3579 AND not exists ( SELECT 'x'
3580 FROM fem_cost_centers_b tab2
3581 WHERE tab2.value_set_id = fem_intg_dim_rule_eng_pkg.pv_cc_vs_id
3582 AND tab1.concat_segment_value = tab2.cost_center_display_code);
3583
3584 v_cc_member_b_count := SQL%ROWCOUNT;
3585 END IF;
3586
3587 FEM_ENGINES_PKG.Tech_Message
3588 (p_severity => pc_log_level_statement
3589 ,p_module => pc_module_name||c_func_name
3590 ,p_app_name => 'FEM'
3591 ,p_msg_name => 'FEM_GL_POST_216'
3592 ,p_token1 => 'TABLE'
3593 ,p_value1 => 'FEM_COST_CENTERS_B'
3594 ,p_token2 => 'NUM'
3595 ,p_value2 => v_cc_member_b_count);
3596
3597 pv_progress := 'Before insert into fem_cost_centers_tl';
3598 --piush_util.put_line(pv_progress);
3599 INSERT INTO fem_cost_centers_tl
3600 (
3601 cost_center_id,
3602 value_set_id,
3603 language,
3604 source_lang,
3605 cost_center_name,
3606 description,
3607 creation_date,
3608 created_by,
3609 last_updated_by,
3610 last_update_date,
3611 last_update_login
3612 )
3613 SELECT TL.FLEX_VALUE_ID
3614 ,tab1.value_set_id
3615 ,TL.language
3616 ,TL.source_lang
3617 ,tab1.cost_center_display_code
3618 ,TL.description
3619 ,SYSDATE
3620 ,pv_user_id
3621 ,pv_user_id
3622 ,SYSDATE
3623 ,pv_login_id
3624 FROM fem_cost_centers_b tab1
3625 ,fnd_flex_values_tl TL
3626 WHERE tab1.value_set_id = fem_intg_dim_rule_eng_pkg.pv_cc_vs_id
3627 AND tab1.cost_center_id = TL.flex_value_id
3628 AND not exists ( SELECT 'x'
3629 FROM fem_cost_centers_tl tab2
3630 WHERE tab1.value_set_id = tab2.value_set_id
3631 AND tab1.cost_center_id = tab2.cost_center_id
3632 AND TL.language = tab2.language );
3633
3634 v_cc_member_tl_count := SQL%ROWCOUNT;
3635 FEM_ENGINES_PKG.Tech_Message
3636 (p_severity => pc_log_level_statement
3637 ,p_module => pc_module_name||c_func_name
3638 ,p_app_name => 'FEM'
3639 ,p_msg_name => 'FEM_GL_POST_216'
3640 ,p_token1 => 'TABLE'
3641 ,p_value1 => 'FEM_COST_CENTERS_TL'
3642 ,p_token2 => 'NUM'
3643 ,p_value2 => v_cc_member_tl_count);
3644
3645 ELSE /* CC is of table validated case */
3646
3647 pv_progress := 'Before insert into fem_cost_centers_vl';
3648 --piush_util.put_line(pv_progress);
3649
3650 v_insert_cc_vl_stmt := 'INSERT INTO fem_cost_centers_vl
3651 (
3652 cost_center_id,
3653 value_set_id,
3654 cost_center_display_code,
3655 enabled_flag,
3656 personal_flag,
3657 creation_date,
3658 created_by,
3659 last_updated_by,
3660 last_update_date,
3661 last_update_login,
3662 read_only_flag,
3663 object_version_number,
3664 cost_center_name,
3665 description
3666 )
3667 SELECT FND_FLEX_VALUES_S.nextval
3668 ,:v_seg2_vs_id
3669 ,concat_segment_value
3670 ,''Y''
3671 ,''N''
3672 ,SYSDATE
3673 ,:v_user_id
3674 ,:v_user_id
3675 ,SYSDATE
3676 ,:v_login_id
3677 ,''N''
3678 ,1
3679 ,concat_segment_value
3680 ,flex.descr
3681 FROM fem_intg_dim_members_gt tab1';
3682 -- Bug 4393061 - changed read_only_flag to 'N'
3683 v_insert_cc_vl_stmt := v_insert_cc_vl_stmt|| ',( SELECT '
3684 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).meaning_col_name
3685 ||' DESCR ,'
3686 ||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).val_col_name
3687 || ' flex_value FROM '
3688 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).table_name || ' '
3689 || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).where_clause || ') FLEX';
3690
3691 v_insert_cc_vl_stmt := v_insert_cc_vl_stmt|| '
3692 WHERE dimension_id = :v_cc_dim_id
3693 AND flex.flex_value = tab1.concat_segment_value
3694 AND not exists ( SELECT ''x''
3695 FROM fem_cost_centers_vl tab2
3696 WHERE :v_seg2_vs_id = tab2.value_set_id
3697 AND tab1.concat_segment_value = tab2.cost_center_display_code)';
3698
3699 FEM_ENGINES_PKG.Tech_Message
3700 (p_severity => pc_log_level_statement
3701 ,p_module => pc_module_name||c_func_name
3702 ,p_msg_text => 'Executing SQL Statement: '||v_insert_cc_vl_stmt||
3703 'Using: '||fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).vs_id
3704 ||','||pv_user_id
3705 ||','||pv_user_id
3706 ||','||pv_login_id
3707 ||','||fem_intg_dim_rule_eng_pkg.pv_cc_dim_id
3708 ||','||fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).vs_id);
3709
3710 EXECUTE IMMEDIATE v_insert_cc_vl_stmt
3711 USING fem_intg_dim_rule_eng_pkg.pv_cc_vs_id,
3712 pv_user_id,
3713 pv_user_id,
3714 pv_login_id,
3715 fem_intg_dim_rule_eng_pkg.pv_cc_dim_id,
3716 fem_intg_dim_rule_eng_pkg.pv_cc_vs_id;
3717
3718 v_cc_member_vl_count := SQL%ROWCOUNT;
3719
3720 FEM_ENGINES_PKG.Tech_Message
3721 (p_severity => pc_log_level_statement
3722 ,p_module => pc_module_name||c_func_name
3723 ,p_app_name => 'FEM'
3724 ,p_msg_name => 'FEM_GL_POST_216'
3725 ,p_token1 => 'TABLE'
3726 ,p_value1 => 'FEM_COST_CENTERS_VL'
3727 ,p_token2 => 'NUM'
3728 ,p_value2 => v_cc_member_vl_count);
3729
3730 END IF;
3731 ELSE /* Not of CCTR type */
3732 FEM_ENGINES_PKG.Tech_Message
3733 (
3734 p_severity => pc_log_level_event
3735 ,p_module => pc_module_name||c_func_name
3736 ,p_msg_text => 'Processing dimension is not of type CCTR-ORG');
3737
3738 END IF;
3739
3740 v_attr_completion_code := 0;
3741 FEM_ENGINES_PKG.User_Message(
3742 p_app_name => 'FEM',
3743 p_msg_name => 'FEM_INTG_DIM_MEMB_501'
3744 );
3745
3746 pv_progress := 'Before calling Populate_Dimension_Attribute';
3747 --piush_util.put_line(pv_progress);
3748 Populate_Dimension_Attribute(
3749 p_summary_flag => 'N'
3750 ,x_completion_code => v_attr_completion_code
3751 ,x_row_count_tot => v_attr_row_count
3752 );
3753 IF v_attr_completion_code <> 0
3754 THEN
3755 FEM_ENGINES_PKG.Tech_Message
3756 (
3757 p_severity => pc_log_level_event
3758 ,p_module => pc_module_name||c_func_name
3759 ,p_msg_text => 'Unexpected error from Populate_Dimension_Attribute');
3760 --piush_util.put_line('Raising exception FEM_INTG_DIM_RULE_attr_err');
3761 RAISE FEM_INTG_DIM_RULE_attr_err;
3762 END IF;
3763
3764
3765 -------------------------------------------------------------------------
3766 -- Store MAX flex value ID from member table into
3767 -- fem_intg_dim_rule_defs.max_flex_value_id_Processed
3768 -------------------------------------------------------------------------
3769 pv_progress := 'Before update of fem_intg_dim_rule_defs.max_flex_value_id_processed';
3770 --piush_util.put_line(pv_progress);
3771
3772 UPDATE fem_intg_dim_rule_defs
3773 SET max_flex_value_id_processed
3774 = FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped
3775 WHERE dim_rule_obj_def_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_rule_obj_def_id;
3776
3777 FEM_ENGINES_PKG.Tech_Message
3778 (
3779 p_severity => pc_log_level_procedure
3780 ,p_module => pc_module_name||c_func_name
3781 ,p_msg_text => 'Update fem_intg_dim_rule_defs.max_flex_value_id_processed'||
3782 'with '||FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped);
3783
3784 v_rows_processed :=
3785 NVL(v_main_insert_gt_count,0)
3786 + NVL(v_comp_insert_gt_count,0)
3787 + NVL(v_cc_insert_gt_count,0)
3788 + NVL(v_comp_member_b_count,0)
3789 + NVL(v_comp_member_vl_count,0)
3790 + NVL(v_cc_member_b_count,0)
3791 + NVL(v_comp_member_tl_count,0)
3792 + NVL(v_cc_member_tl_count,0)
3793 + NVL(v_cc_member_vl_count,0)
3794 + NVL(v_insert_member_b_count,0)
3795 + NVL(v_merge_count,0)
3796 + NVL(v_insert_member_vl_count,0)
3797 + NVL(v_comp_member_vl_count,0)
3798 + NVL(v_attr_row_count, 0);
3799 COMMIT;
3800
3801 pv_progress := 'Before getting list of columns to be mapped';
3802 --piush_util.put_line(pv_progress);
3803
3804 /*
3805 * Get the columns to be updated
3806 */
3807
3808 pv_progress := 'Before building map table dynamic update stmt';
3809 --piush_util.put_line(pv_progress);
3810
3811 /* UPDATE MAPPING TABLE
3812 * =======================
3813 *
3814 * Build dyanmic SQL to insert new members into FEM mebers table
3815 * Only new members will be inserted into the table
3816 */
3817
3818 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'INTERCOMPANY'
3819 THEN
3820
3821 v_column_list := FEM_INTG_DIM_RULE_ENG_PKG.pv_member_col;
3822 v_value_list := 'member_table.COMPANY_COST_CENTER_ORG_ID';
3823
3824 ELSIF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label
3825 = 'COMPANY_COST_CENTER_ORG'
3826 THEN
3827
3828 FOR rec IN ColumnList LOOP
3829 IF rec.column_name <> 'INTERCOMPANY_ID' THEN
3830 v_column_list := v_column_list || rec.column_name || ',';
3831 v_value_list := v_value_list || 'member_table.'|| FEM_INTG_DIM_RULE_ENG_PKG.pv_member_col || ',';
3832 END IF;
3833 END LOOP;
3834
3835 v_column_list := '(' || TRIM(TRAILING ',' FROM v_column_list) || ')';
3836 v_value_list := TRIM(TRAILING ',' FROM v_value_list);
3837
3838 ELSE
3839
3840 FOR rec IN ColumnList LOOP
3841 v_column_list := v_column_list || rec.column_name || ',';
3842 v_value_list := v_value_list || 'member_table.'|| FEM_INTG_DIM_RULE_ENG_PKG.pv_member_col || ',';
3843 END LOOP;
3844
3845 v_column_list := '(' || TRIM(TRAILING ',' FROM v_column_list) || ')';
3846 v_value_list := TRIM(TRAILING ',' FROM v_value_list);
3847 END IF;
3848
3849 FEM_ENGINES_PKG.Tech_Message
3850 (
3851 p_severity => pc_log_level_procedure
3852 ,p_module => pc_module_name||c_func_name
3853 ,p_msg_text => 'Columns '||v_column_list||' will be updated in mapping table');
3854
3855
3856 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label <> 'GEOGRAPHY'
3857 THEN
3858
3859 v_upd_map_table_stmt := 'UPDATE fem_intg_ogl_ccid_map fiocm
3860 SET ' || v_column_list || ' = (
3861 SELECT ' || v_value_list || '
3862 FROM '|| FEM_INTG_DIM_RULE_ENG_PKG.pv_member_vl_object_name||' member_table
3863 , gl_code_combinations GCC
3864 WHERE GCC.code_combination_id = fiocm.code_combination_id
3865 AND member_table.value_set_id = :v_fem_vs_id
3866 AND member_table.'||FEM_INTG_DIM_RULE_ENG_PKG.pv_member_display_code_col
3867 ||' = ';
3868 v_upd_map_table_stmt := v_upd_map_table_stmt || 'GCC.'||
3869 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).application_column_name||'||''-''
3870 ||GCC.'||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).application_column_name;
3871
3872 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 2
3873 THEN
3874 v_upd_map_table_stmt := v_upd_map_table_stmt || '||''-''||GCC.';
3875 v_upd_map_table_stmt := v_upd_map_table_stmt ||
3876 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).application_column_name;
3877 END IF;
3878
3879 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_segment_count > 3
3880 THEN
3881 v_upd_map_table_stmt := v_upd_map_table_stmt ||'||''-''||GCC.'||
3882 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).application_column_name;
3883 END IF;
3884
3885 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_segment_count > 4
3886 THEN
3887 v_upd_map_table_stmt := v_upd_map_table_stmt ||'||''-''||GCC.'||
3888 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).application_column_name;
3889 END IF;
3890 v_upd_map_table_stmt := v_upd_map_table_stmt ||')';
3891
3892 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'NATURAL_ACCOUNT'
3893 THEN
3894 v_upd_map_table_stmt := v_upd_map_table_stmt || ', extended_account_type =
3895 (select attr.dim_attribute_varchar_member
3896 from fem_nat_accts_attr attr
3897 ,fem_nat_accts_b b
3898 ,gl_code_combinations g
3899 where attr.value_set_id = b.value_set_id
3900 and attr.natural_account_id = b.natural_account_id
3901 and attr.value_set_id = :v_fem_vs_id
3902 and g.chart_of_accounts_id = :pv_coa_id
3903 and attr.attribute_id = :v_ext_acct_type_attr_id
3904 and attr.version_id = :v_ext_acct_type_ver_id
3905 and b.natural_account_display_code = g.'
3906 ||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).application_column_name ||'||''-''
3907 ||g.'||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).application_column_name;
3908 IF fem_intg_dim_rule_eng_pkg.pv_segment_count > 2
3909 THEN
3910 v_upd_map_table_stmt := v_upd_map_table_stmt || '||''-''||g.';
3911 v_upd_map_table_stmt := v_upd_map_table_stmt ||
3912 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).application_column_name; END IF;
3913
3914 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_segment_count > 3
3915 THEN
3916 v_upd_map_table_stmt := v_upd_map_table_stmt ||'||''-''||g.'||
3917 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).application_column_name;
3918 END IF;
3919
3920 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_segment_count > 4
3921 THEN
3922 v_upd_map_table_stmt := v_upd_map_table_stmt ||'||''-''||g.'||
3923 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).application_column_name;
3924 END IF;
3925
3926 v_upd_map_table_stmt := v_upd_map_table_stmt || ' and g.summary_flag = ''N''
3927 and fiocm.code_combination_id = g.code_combination_id)';
3928
3929 END IF;
3930
3931 v_upd_map_table_stmt := v_upd_map_table_stmt ||' WHERE fiocm.code_combination_id between :v_ccid_low AND :v_ccidhigh
3932 AND fiocm.global_vs_combo_id = :v_gvsc_id';
3933
3934
3935 --
3936 -- Execute built statement for updating mapping table
3937 -- with correct dimension member ID values
3938 --
3939 pv_progress := 'Before executing update map';
3940 --piush_util.put_line(pv_progress);
3941
3942 FEM_ENGINES_PKG.Tech_Message
3943 (p_severity => pc_log_level_statement
3944 ,p_module => pc_module_name||c_func_name
3945 ,p_app_name => 'FEM'
3946 ,p_msg_name => 'FEM_GL_POST_204'
3947 ,p_token1 => 'VAR_NAME'
3948 ,p_value1 => 'SQL Statement'
3949 ,p_token2 => 'VAR_VAL'
3950 ,p_value2 => v_upd_map_table_stmt);
3951
3952 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'NATURAL_ACCOUNT'
3953 THEN
3954 EXECUTE IMMEDIATE v_upd_map_table_stmt
3955 USING FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id
3956 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id
3957 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id
3958 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_ext_acct_type_attr_id
3959 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_ext_acct_attr_version_id
3960 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_processed+1
3961 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped
3962 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_gvsc_id;
3963 ELSE
3964 EXECUTE IMMEDIATE v_upd_map_table_stmt
3965 USING FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id
3966 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_processed+1
3967 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped
3968 ,FEM_INTG_DIM_RULE_ENG_PKG.pv_gvsc_id;
3969 END IF;
3970
3971 v_upd_map_table_count := SQL%ROWCOUNT;
3972 v_rows_processed := v_rows_processed + v_upd_map_table_count;
3973 FEM_ENGINES_PKG.Tech_Message
3974 (p_severity => pc_log_level_statement
3975 ,p_module => pc_module_name||c_func_name
3976 ,p_app_name => 'FEM'
3977 ,p_msg_name => 'FEM_GL_POST_217'
3978 ,p_token1 => 'TABLE'
3979 ,p_value1 => 'fem_intg_ogl_ccid_map'
3980 ,p_token2 => 'NUM'
3981 ,p_value2 => v_upd_map_table_count);
3982
3983 pv_progress := 'after executing update map';
3984 --piush_util.put_line(pv_progress);
3985
3986 x_row_count_tot := v_rows_processed;
3987
3988 END IF;
3989
3990 COMMIT;
3991
3992 -- start bug fix 5377544
3993
3994 --Start bug fix 5560443
3995 IF ( FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'INTERCOMPANY' AND
3996 --Start bug fix 5578766
3997 (p_calling_module IS NULL OR p_calling_module <> 'HIER_MULTI_SEG')) THEN
3998 --End bug fix 5578766
3999
4000 --End bug fix 5560443
4001
4002 -- Since requests will reach completed phase irrespective of status
4003 -- Check if any dimension rule requests which are not having completed phase
4004 -- for any dimension other than org dimension for the same chart of account
4005 -- If any request found then issue sleep timer
4006 LOOP
4007 BEGIN
4008 SELECT 1
4009 INTO v_dim_rule_req_count
4010 FROM dual
4011 WHERE EXISTS ( SELECT 1
4012 FROM fnd_concurrent_programs fcp,
4013 fnd_concurrent_requests fcr,
4014 fem_intg_dim_rules idr,
4015 fem_object_definition_b fodb
4016 WHERE fcp.concurrent_program_id = fcr.concurrent_program_id
4017 AND fcp.application_id = fcr.program_application_id
4018 AND fcp.application_id = 274
4019 AND fcp.concurrent_program_name = 'FEM_INTG_DIM_RULE_ENGINE'
4020 AND fcr.phase_code <> 'C'
4021 AND idr.dim_rule_obj_id = fodb.object_id
4022 AND idr.chart_of_accounts_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id
4023 --Start bug fix 5560443
4024 AND idr.dimension_id <> 0
4025 --End bug fix 5560443
4026 AND fcr.argument1 = fodb.object_definition_id
4027 AND fcr.argument2 = 'MEMBER');
4028 DBMS_LOCK.SLEEP(pc_sleep_second);
4029 EXCEPTION WHEN NO_DATA_FOUND THEN EXIT;
4030 END;
4031 END LOOP;
4032
4033 select nvl(value,1)*2 no_of_workers
4034 into v_Num_Workers
4035 from v$parameter
4036 where name = 'cpu_count';
4037
4038 FEM_ENGINES_PKG.User_Message(
4039 p_app_name => 'FEM',
4040 p_msg_text => 'Kicking off '||v_Num_Workers||' workers requests at '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')
4041 );
4042
4043 FEM_ENGINES_PKG.Tech_Message(
4044 p_severity => pc_log_level_statement,
4045 p_module => pc_module_name||c_func_name,
4046 p_msg_text => 'USING ' ||
4047 TO_CHAR(FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id) || ', ' ||
4048 TO_CHAR(FEM_INTG_DIM_RULE_ENG_PKG.pv_gvsc_id)
4049 );
4050
4051 -- AD Parallel framework Manager processing
4052
4053 --Purge all the info from ad processing tables
4054 ad_parallel_updates_pkg.purge_processed_units
4055 (X_owner => 'FEM',
4056 X_table => 'FEM_INTG_OGL_CCID_MAP',
4057 X_script => FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id);
4058
4059 ad_parallel_updates_pkg.delete_update_information
4060 (X_update_type => ad_parallel_updates_pkg.ROWID_RANGE,
4061 X_owner => 'FEM',
4062 X_table => 'FEM_INTG_OGL_CCID_MAP',
4063 X_script => FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id);
4064
4065 -- submit update CCID worker
4066 AD_CONC_UTILS_PKG.submit_subrequests( X_errbuf => X_errbuf,
4067 X_retcode => v_completion_code,
4068 X_WorkerConc_app_shortname => 'FEM',
4069 X_WorkerConc_progname => 'FEM_INTG_DIM_RULE_WORKER',
4070 X_batch_size => pv_batch_size,
4071 X_Num_Workers => v_Num_Workers,
4072 X_Argument4 => FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id,
4073 X_Argument5 => FEM_INTG_DIM_RULE_ENG_PKG.pv_gvsc_id,
4074 X_Argument6 => FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_processed,
4075 X_Argument7 => FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped
4076 );
4077
4078 IF v_completion_code = 2 THEN
4079
4080 RAISE FEM_INTG_DIM_RULE_worker_err;
4081
4082 END IF;
4083
4084 --
4085 -- Update dimension rule definitions for single segment/value rules
4086 --
4087
4088 UPDATE FEM_INTG_DIM_RULE_DEFS
4089 SET MAX_CCID_PROCESSED = FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped
4090 WHERE DIM_RULE_OBJ_DEF_ID IN ( SELECT defs.dim_rule_obj_def_id
4091 FROM fem_intg_dim_rules idr,
4092 fem_object_definition_b fodb,
4093 fem_xdim_dimensions fxd,
4094 fem_intg_dim_rule_defs defs,
4095 fem_tab_columns_b ftcb
4096 WHERE ftcb.table_name = 'FEM_BALANCES'
4097 AND ftcb.fem_data_type_code = 'DIMENSION'
4098 AND ftcb.dimension_id = fxd.dimension_id
4099 AND DECODE(ftcb.column_name,'INTERCOMPANY_ID', 0, fxd.dimension_id) = idr.dimension_id
4100 AND idr.chart_of_accounts_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id
4101 AND idr.dim_rule_obj_id = fodb.object_id
4102 AND defs.dim_rule_obj_def_id = fodb.object_definition_id
4103 AND defs.dim_mapping_option_code IN ('SINGLESEG','SINGLEVAL') );
4104
4105
4106 v_rows_processed := SQL%ROWCOUNT;
4107
4108 COMMIT;
4109
4110 FEM_ENGINES_PKG.Tech_Message(
4111 p_severity => pc_log_level_statement,
4112 p_module => pc_module_name || '.cnt_update_FEM_INTG_DIM_RULE_DEFS',
4113 p_msg_text => v_rows_processed
4114 );
4115
4116 x_row_count_tot := x_row_count_tot + v_rows_processed;
4117
4118 -- Start bug Fix 5447696
4119 BEGIN
4120 OPEN fch_vs_cursor FOR v_fch_vs_select_stmt USING FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id;
4121 FETCH fch_vs_cursor INTO v_gcs_vs_id;
4122
4123 IF (v_gcs_vs_id IS NOT NULL) THEN
4124
4125 -- submit entity orgs synch program
4126 v_request_id := FND_REQUEST.submit_request( application => 'GCS',
4127 program => 'FCH_UPDATE_ENTITY_ORGS',
4128 sub_request => FALSE);
4129
4130 FEM_ENGINES_PKG.User_Message(
4131 p_app_name => 'FEM',
4132 p_msg_text => 'Submitted Update Entity Organizations Request ' || v_request_id
4133 );
4134
4135 END IF;
4136
4137 CLOSE fch_vs_cursor;
4138
4139 EXCEPTION WHEN OTHERS THEN NULL;
4140 END;
4141 -- End bug Fix 5447696
4142
4143 END IF;
4144
4145 x_completion_code := v_completion_code;
4146 -- end bug fix 5377544
4147
4148 FEM_ENGINES_PKG.Tech_Message
4149 (p_severity => pc_log_level_procedure,
4150 p_module => pc_module_name || c_func_name,
4151 p_app_name => 'FEM',
4152 p_msg_name => 'FEM_GL_POST_202',
4153 p_token1 => 'FUNC_NAME',
4154 p_value1 => c_func_name,
4155 p_token2 => 'TIME',
4156 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
4157
4158 EXCEPTION
4159 WHEN FEM_INTG_DIM_RULE_ulock_err THEN
4160
4161 ROLLBACK;
4162
4163 FEM_ENGINES_PKG.Tech_Message(
4164 p_severity => pc_log_level_exception,
4165 p_module => PC_module_name || c_func_name|| '.ulock_err_exception',
4166 p_app_name => 'FEM',
4167 p_msg_name => 'FEM_INTG_DIM_RULE_ULOCK_EXISTS'
4168 );
4169
4170 FEM_ENGINES_PKG.User_Message(
4171 p_app_name => 'FEM',
4172 p_msg_name => 'FEM_INTG_DIM_RULE_ULOCK_EXISTS'
4173 );
4174
4175 FEM_ENGINES_PKG.Tech_Message(
4176 p_severity => pc_log_level_procedure,
4177 p_module => pc_module_name || c_func_name|| '.ulock_err_exception',
4178 p_app_name => 'FEM',
4179 p_msg_name => 'FEM_GL_POST_203',
4180 p_token1 => 'FUNC_NAME',
4181 p_value1 => c_func_name,
4182 p_token2 => 'TIME',
4183 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
4184 );
4185
4186 x_completion_code := 2;
4187
4188 WHEN FEM_INTG_DIM_RULE_worker_err THEN
4189
4190 ROLLBACK;
4191
4192 FEM_ENGINES_PKG.Tech_Message(
4193 p_severity => pc_log_level_statement,
4194 p_module => pc_module_name || '.worker_err',
4195 p_msg_text => 'Dimension Rule Worker Error: ' || X_errbuf
4196 );
4197
4198 FEM_ENGINES_PKG.User_Message(
4199 p_app_name => 'FEM',
4200 p_msg_text => 'Dimension Rule Worker Error: ' || X_errbuf
4201 );
4202
4203 x_completion_code := 2;
4204
4205 WHEN OTHERS THEN
4206 --piush_util.put_line('Exception Block');
4207 --piush_util.put_line('SQLCODE = ' || SQLCODE);
4208 --piush_util.put_line('SQLERRM = ' || SQLERRM);
4209 --raise;
4210 ROLLBACK;
4211 FEM_ENGINES_PKG.Tech_Message
4212 (p_severity => pc_log_level_statement
4213 ,p_module => pc_module_name||c_func_name
4214 ,p_msg_text => 'Error: ' || pv_progress);
4215
4216 FEM_ENGINES_PKG.Tech_Message
4217 (p_severity => pc_log_level_statement
4218 ,p_module => pc_module_name||c_func_name
4219 ,p_msg_text => 'Error: ' || sqlerrm);
4220 FEM_ENGINES_PKG.Tech_Message
4221 (p_severity => pc_log_level_unexpected
4222 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
4223 ,p_msg_text => sqlerrm);
4224
4225
4226 FEM_ENGINES_PKG.User_Message
4227 (p_msg_text => sqlerrm);
4228
4229 FEM_ENGINES_PKG.Tech_Message
4230 (p_severity => pc_log_level_procedure
4231 ,p_module => pc_module_name||c_func_name||'.unexpected_exception'
4232 ,p_app_name => 'FEM'
4233 ,p_msg_name => 'FEM_GL_POST_203'
4234 ,p_token1 => 'FUNC_NAME'
4235 ,p_value1 => c_func_name
4236 ,p_token2 => 'TIME'
4237 ,p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
4238
4239
4240 x_completion_code := 2;
4241 END;
4242
4243
4244 -- ======================================================================
4245 -- Procedure
4246 -- Create_Parent_Members
4247 -- Purpose
4248 -- This routine
4249 -- History
4250 -- 11-05-04 Jee Kim Created
4251 -- Arguments
4252 -- x_completion_code Completion status of the routine
4253 -- ======================================================================
4254
4255 PROCEDURE Create_Parent_Members(
4256 x_completion_code OUT NOCOPY NUMBER) IS
4257
4258 FEM_INTG_fatal_err EXCEPTION;
4259
4260 v_sql_stmt VARCHAR2(2000);
4261 v_compl_code NUMBER := 0;
4262 v_row_count_tot NUMBER := 0;
4263
4264 BEGIN
4265
4266 FEM_ENGINES_PKG.Tech_Message
4267 (p_severity => pc_log_level_procedure,
4268 p_module => 'fem.plsql.fem_intg_new_dim_member.Create_Parent_Members',
4269 p_app_name => 'FEM',
4270 p_msg_name => 'FEM_GL_POST_201',
4271 p_token1 => 'FUNC_NAME',
4272 p_value1 => 'FEM_INTG_NEW_DIM_MEMBER_PKG.Create_Parent_Members',
4273 p_token2 => 'TIME',
4274 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
4275
4276 x_completion_code := 0;
4277
4278 -- Insert all distinct parent members from the hierarchy into
4279 -- the member GT table FEM_INTG_DIM_MEMBERS_GT
4280 v_sql_stmt :=
4281 'INSERT INTO fem_intg_dim_members_gt
4282 (dimension_id,
4283 segment1_value,
4284 segment2_value,
4285 segment3_value,
4286 segment4_value,
4287 segment5_value,
4288 concat_segment_value)
4289 SELECT DISTINCT
4290 '||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_id||',
4291 hgt.child_display_code,
4292 ''-1'',
4293 ''-1'',
4294 ''-1'',
4295 ''-1'',
4296 hgt.child_display_code
4297 FROM FEM_INTG_DIM_HIER_GT hgt,
4298 FND_FLEX_VALUES ff
4299 WHERE ff.flex_value_set_id = '||FEM_INTG_HIER_RULE_ENG_PKG.pv_aol_vs_id||'
4300 AND ff.flex_value = hgt.child_display_code';
4301
4302
4303
4304 FEM_ENGINES_PKG.Tech_Message
4305 (p_severity => pc_log_level_procedure,
4306 p_module => 'fem.plsql.fem_intg_new_dim_member.Create_Parent_Members',
4307 p_app_name => 'FEM',
4308 p_msg_name => 'FEM_GL_POST_204',
4309 p_token1 => 'VAR_NAME',
4310 p_value1 => 'v_sql_stmt',
4311 p_token2 => 'VAR_VAL',
4312 p_value2 => v_sql_stmt);
4313
4314 EXECUTE IMMEDIATE v_sql_stmt;
4315 COMMIT;
4316
4317 -- Merge all parent members into the FEM member _B and _TL tables
4318 v_sql_stmt :=
4319 'MERGE INTO '||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_b_tab||' b
4320 USING (SELECT gt.concat_segment_value,
4321 ffv.flex_value_id
4322 FROM fem_intg_dim_members_gt gt,
4323 fnd_flex_values ffv
4324 WHERE ffv.flex_value_set_id = :pv_aol_vs_id
4325 AND ffv.flex_value = gt.concat_segment_value) s
4326 ON (b.'||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_col||' =s.flex_value_id
4327 AND b.value_set_id = :pv_dim_vs_id)
4328 WHEN MATCHED THEN UPDATE
4329 SET b.last_update_date = SYSDATE
4330 WHEN NOT MATCHED THEN
4331 INSERT
4332 (b.'||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_col||',
4333 b.value_set_id,
4334 b.dimension_group_id,
4335 b.'||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_disp_col||',
4336 b.enabled_flag,
4337 b.personal_flag,
4338 b.creation_date,
4339 b.created_by,
4340 b.last_updated_by,
4341 b.last_update_login,
4342 b.last_update_date,
4343 b.read_only_flag,
4344 b.object_version_number)
4345 VALUES
4346 (s.flex_value_id,
4347 :pv_dim_vs_id,
4348 NULL,
4349 s.concat_segment_value,
4350 ''Y'',
4351 ''N'',
4352 SYSDATE,
4353 :pv_user_id,
4354 :pv_user_id,
4355 :pv_login_id,
4356 SYSDATE,
4357 ''N'',
4358 1)';
4359
4360 FEM_ENGINES_PKG.Tech_Message
4361 (p_severity => pc_log_level_procedure,
4362 p_module => 'fem.plsql.fem_intg_new_dim_member.Create_Parent_Members.',
4363 p_app_name => 'FEM',
4364 p_msg_name => 'FEM_GL_POST_204',
4365 p_token1 => 'VAR_NAME',
4366 p_value1 => 'v_sql_stmt',
4367 p_token2 => 'VAR_VAL',
4368 p_value2 => v_sql_stmt);
4369
4370 EXECUTE IMMEDIATE v_sql_stmt
4371 USING FEM_INTG_HIER_RULE_ENG_PKG.pv_aol_vs_id,
4372 FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_vs_id,
4373 FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_vs_id,
4374 pv_user_id,
4375 pv_user_id,
4376 pv_login_id;
4377
4378 v_sql_stmt :=
4379 'MERGE INTO '||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_tl_tab||' b
4380 USING (SELECT tl.flex_value_id, ffv.flex_value,
4381 tl.description,
4382 tl.language, tl.source_lang
4383 FROM fem_intg_dim_members_gt gt,
4384 fnd_flex_values_tl tl,
4385 fnd_flex_values ffv
4386 WHERE tl.flex_value_id = ffv.flex_value_id
4387 AND ffv.flex_value_set_id = :pv_aol_vs_id
4388 AND ffv.flex_value = gt.concat_segment_value) s
4389 ON (b.'||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_col||' = s.flex_value_id
4390 AND b.language = s.language
4391 AND b.value_set_id = :pv_dim_vs_id)
4392 WHEN MATCHED THEN UPDATE
4393 SET b.last_update_date = SYSDATE
4394 WHEN NOT MATCHED THEN
4395 INSERT
4396 (b.'||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_col||',
4397 b.value_set_id,
4398 b.'||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_name_col||',
4399 b.language,
4400 b.source_lang,
4401 b.creation_date,
4402 b.created_by,
4403 b.last_updated_by,
4404 b.last_update_login,
4405 b.last_update_date,
4406 b.description)
4407 VALUES
4408 (s.flex_value_id,
4409 :pv_dim_vs_id,
4410 s.flex_value,
4411 s.language,
4412 s.source_lang,
4413 SYSDATE,
4414 :pv_user_id,
4415 :pv_user_id,
4416 :pv_login_id,
4417 SYSDATE,
4418 s.description)';
4419
4420 FEM_ENGINES_PKG.Tech_Message
4421 (p_severity => pc_log_level_procedure,
4422 p_module => 'fem.plsql.fem_intg_new_dim_member.Create_Parent_Members.',
4423 p_app_name => 'FEM',
4424 p_msg_name => 'FEM_GL_POST_204',
4425 p_token1 => 'VAR_NAME',
4426 p_value1 => 'v_sql_stmt',
4427 p_token2 => 'VAR_VAL',
4428 p_value2 => v_sql_stmt);
4429
4430 EXECUTE IMMEDIATE v_sql_stmt
4431 USING FEM_INTG_HIER_RULE_ENG_PKG.pv_aol_vs_id,
4432 FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_vs_id,
4433 FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_vs_id,
4434 pv_user_id,
4435 pv_user_id,
4436 pv_login_id;
4437
4438
4439 -- Initialize the variables requred for Populate_Attr( )
4440 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_rule_obj_id
4441 := FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_rule_obj_id;
4442 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_rule_obj_def_id
4443 := FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_rule_obj_def_id;
4444 FEM_INTG_DIM_RULE_ENG_PKG.pv_user_id := pv_user_id;
4445
4446 FEM_INTG_DIM_RULE_ENG_PKG.Init;
4447
4448 FEM_ENGINES_PKG.User_Message(
4449 p_app_name => 'FEM',
4450 p_msg_name => 'FEM_INTG_DIM_MEMB_501');
4451
4452 -- Call FEM_INTG_NEW_MEMBER_PKG.Populate_Attr( ).
4453 fem_intg_new_dim_member_pkg.Populate_Dimension_Attribute(
4454 p_summary_flag => 'Y',
4455 x_completion_code => v_compl_code,
4456 x_row_count_tot => v_row_count_tot);
4457
4458 IF v_compl_code = 2 THEN
4459 RAISE FEM_INTG_fatal_err;
4460 END IF;
4461
4462 COMMIT;
4463
4464 x_completion_code := 0;
4465
4466 FEM_ENGINES_PKG.Tech_Message
4467 (p_severity => pc_log_level_procedure,
4468 p_module => 'fem.plsql.fem_intg_new_dim_member.Create_Parent_Members',
4469 p_app_name => 'FEM',
4470 p_msg_name => 'FEM_GL_POST_202',
4471 p_token1 => 'FUNC_NAME',
4472 p_value1 => 'FEM_INTG_NEW_DIM_MEMBER_PKG.Create_Parent_Members',
4473 p_token2 => 'TIME',
4474 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
4475
4476 return;
4477
4478 EXCEPTION
4479
4480 WHEN FEM_INTG_fatal_err THEN
4481 ROLLBACk;
4482
4483 FEM_ENGINES_PKG.Tech_Message
4484 (p_severity => pc_log_level_unexpected,
4485 p_module => 'fem.plsql.fem_intg_new_dim_member.Create_Parent_Members',
4486 p_app_name => 'FEM',
4487 p_msg_name => 'FEM_GL_POST_215',
4488 p_token1 => 'ERR_MSG',
4489 p_value1 => SQLERRM);
4490
4491 FEM_ENGINES_PKG.User_Message
4492 (p_app_name => 'FEM',
4493 p_msg_name => 'FEM_GL_POST_215',
4494 p_token1 => 'ERR_MSG',
4495 p_value1 => SQLERRM);
4496
4497 FEM_ENGINES_PKG.Tech_Message
4498 (p_severity => pc_log_level_procedure,
4499 p_module => 'fem.plsql.fem_intg_new_dim_member.Create_Parent_Members',
4500 p_app_name => 'FEM',
4501 p_msg_name => 'FEM_GL_POST_203',
4502 p_token1 => 'FUNC_NAME',
4503 p_value1 => 'FEM_INTG_NEW_DIM_MEMBER_PKG.Create_Parent_Members',
4504 p_token2 => 'TIME',
4505 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
4506
4507 x_completion_code := 2;
4508 return;
4509
4510 WHEN OTHERS THEN
4511
4512 ROLLBACK;
4513
4514 FEM_ENGINES_PKG.Tech_Message
4515 (p_severity => pc_log_level_unexpected,
4516 p_module => 'fem.plsql.fem_intg_new_dim_member.Create_Parent_Members',
4517 p_app_name => 'FEM',
4518 p_msg_name => 'FEM_GL_POST_215',
4519 p_token1 => 'ERR_MSG',
4520 p_value1 => SQLERRM);
4521
4522 FEM_ENGINES_PKG.User_Message
4523 (p_app_name => 'FEM',
4524 p_msg_name => 'FEM_GL_POST_215',
4525 p_token1 => 'ERR_MSG',
4526 p_value1 => SQLERRM);
4527
4528 FEM_ENGINES_PKG.Tech_Message
4529 (p_severity => pc_log_level_procedure,
4530 p_module => 'fem.plsql.fem_intg_new_dim_member.Create_Parent_Members',
4531 p_app_name => 'FEM',
4532 p_msg_name => 'FEM_GL_POST_203',
4533 p_token1 => 'FUNC_NAME',
4534 p_value1 => 'FEM_INTG_NEW_DIM_MEMBER_PKG.Create_Parent_Members',
4535 p_token2 => 'TIME',
4536 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
4537
4538 x_completion_code := 2;
4539 return;
4540
4541 END Create_Parent_Members;
4542
4543 -- start Bug fix 5377544
4544 /*
4545 PROCEDURE Check_All_CCIDS_Mapped(x_result OUT NOCOPY VARCHAR2) IS
4546 v_unmapped_count NUMBER;
4547 v_mapped_count NUMBER;
4548 BEGIN
4549 x_result := 'ALL_MAPPED';
4550 IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'INTERCOMPANY'
4551 THEN
4552 SELECT nvl(sum(decode(intercompany_id,-1,1,0)),0)
4553 ,nvl(sum(decode(intercompany_id,-1,0,1)),0)
4554 INTO v_unmapped_count
4555 ,v_mapped_count
4556 FROM fem_intg_ogl_ccid_map
4557 WHERE global_vs_combo_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_gvsc_id;
4558 IF v_unmapped_count > 0
4559 THEN
4560 IF v_mapped_count = 0
4561 THEN
4562 x_result := 'NOTHING_MAPPED';
4563 ELSE
4564 x_result := 'SOME_UNMAPPED';
4565 END IF;
4566 ELSE
4567 x_result := 'ALL_MAPPED';
4568 END IF;
4569 END IF;
4570 END;
4571 */
4572 -- start Bug fix 5377544
4573
4574 --
4575 -- Worker program API for FEM_INTG_OGL_CCID_MAP table update
4576 -- for single_segment dimension rules
4577 -- leveraging AD Parallel framework for Bug fix 5377544
4578 --
4579
4580 PROCEDURE fem_intg_dim_rule_worker( X_errbuf OUT NOCOPY VARCHAR2,
4581 X_retcode OUT NOCOPY VARCHAR2,
4582 p_batch_size IN NUMBER,
4583 p_Worker_Id IN NUMBER,
4584 p_Num_Workers IN NUMBER,
4585 p_coa_id IN VARCHAR2,
4586 p_gvsc_id IN VARCHAR2,
4587 p_max_ccid_processed IN VARCHAR2,
4588 p_max_ccid_to_be_mapped IN VARCHAR2
4589 )
4590 IS
4591 v_product VARCHAR2(30) := 'FEM';
4592 v_table_name VARCHAR2(30) := 'FEM_INTG_OGL_CCID_MAP';
4593 v_update_name VARCHAR2(30);
4594 v_status VARCHAR2(30);
4595 v_industry VARCHAR2(30);
4596 v_retstatus BOOLEAN;
4597 v_table_owner VARCHAR2(30);
4598 v_any_rows_to_process BOOLEAN;
4599 v_start_rowid ROWID;
4600 v_end_rowid ROWID;
4601 v_rows_processed NUMBER;
4602 v_module_name VARCHAR2(100);
4603 v_upd_stmt1 VARCHAR2(20000);
4604 v_upd_stmt3 VARCHAR2(200);
4605 v_ccid_cur_stmt VARCHAR2(500);
4606 v_ccid_update_stmt VARCHAR2(20000);
4607 v_ext_acct_type_attr_id NUMBER;
4608 v_ext_acct_type_ver_id NUMBER;
4609 v_start_pos NUMBER;
4610
4611 TYPE ccid_cur_type IS REF CURSOR;
4612 v_ccid_cur ccid_cur_type;
4613 TYPE ccid_list_type IS TABLE OF NUMBER;
4614 v_ccid_list ccid_list_type;
4615
4616 CURSOR c_upd_dim_list_cur (p_coa_id NUMBER)
4617 IS
4618 SELECT ftcb.column_name target_col,
4619 fxd.member_col source_col,
4620 fxd.member_b_table_name source_b_table_name,
4621 fxd.member_display_code_col source_display_code_col,
4622 NVL(defs.fem_value_set_id,-1) fem_value_set_id,
4623 defs.application_column_name1,
4624 defs.default_member_id,
4625 defs.dim_mapping_option_code
4626 FROM fem_intg_dim_rules idr,
4627 fem_object_definition_b fodb,
4628 fem_xdim_dimensions fxd,
4629 fem_intg_dim_rule_defs defs,
4630 fem_tab_columns_b ftcb
4631 WHERE ftcb.table_name = 'FEM_BALANCES'
4632 AND ftcb.fem_data_type_code = 'DIMENSION'
4633 AND ftcb.dimension_id = fxd.dimension_id
4634 AND DECODE(ftcb.column_name,'INTERCOMPANY_ID', 0, fxd.dimension_id) = idr.dimension_id
4635 AND idr.dim_rule_obj_id = fodb.object_id
4636 AND idr.chart_of_accounts_id = p_coa_id
4637 AND defs.dim_rule_obj_def_id = fodb.object_definition_id
4638 AND defs.dim_mapping_option_code IN ('SINGLESEG','SINGLEVAL');
4639
4640 BEGIN
4641
4642 v_module_name := 'fem.plsql.fem_intg_dim.FEM_INTG_DIM_RULE_WORKER';
4643
4644 FEM_ENGINES_PKG.Tech_Message(
4645 p_severity => pc_log_level_procedure,
4646 p_module => v_module_name || '.start_worker',
4647 p_msg_text => 'Start of mapping table update worker id : '||p_Worker_Id
4648 );
4649
4650 FEM_ENGINES_PKG.User_Message(
4651 p_app_name => 'FEM',
4652 p_msg_text => '<< Start of mapping table update worker >>'
4653 );
4654 FEM_ENGINES_PKG.User_Message(
4655 p_app_name => 'FEM',
4656 p_msg_text => 'p_Worker_Id : '||p_Worker_Id
4657 );
4658 FEM_ENGINES_PKG.User_Message(
4659 p_app_name => 'FEM',
4660 p_msg_text => 'p_coa_id : '||p_coa_id
4661 );
4662 FEM_ENGINES_PKG.User_Message(
4663 p_app_name => 'FEM',
4664 p_msg_text => 'p_gvsc_id : '||p_gvsc_id
4665 );
4666 FEM_ENGINES_PKG.User_Message(
4667 p_app_name => 'FEM',
4668 p_msg_text => 'p_Num_Workers : '||p_Num_Workers
4669 );
4670 FEM_ENGINES_PKG.User_Message(
4671 p_app_name => 'FEM',
4672 p_msg_text => 'p_Worker_Id : '||p_Worker_Id
4673 );
4674 FEM_ENGINES_PKG.User_Message(
4675 p_app_name => 'FEM',
4676 p_msg_text => 'p_max_ccid_processed : '||p_max_ccid_processed
4677 );
4678 FEM_ENGINES_PKG.User_Message(
4679 p_app_name => 'FEM',
4680 p_msg_text => 'p_max_ccid_to_be_mapped : '||p_max_ccid_to_be_mapped
4681 );
4682
4683 --
4684 -- get schema name of the table for ROWID range processing
4685 --
4686 v_retstatus := fnd_installation.get_app_info( v_product,
4687 v_status,
4688 v_industry,
4689 v_table_owner);
4690
4691 if ((v_retstatus = FALSE) OR (v_table_owner is null)) then
4692 raise_application_error(-20001, 'Cannot get schema name for product : '||v_product);
4693 end if;
4694
4695
4696 --
4697 -- Worker processing
4698 --
4699
4700 v_update_name := p_coa_id;
4701
4702 ad_parallel_updates_pkg.initialize_rowid_range( ad_parallel_updates_pkg.ROWID_RANGE,
4703 v_table_owner,
4704 v_table_name,
4705 v_update_name,
4706 p_Worker_Id,
4707 p_Num_Workers,
4708 p_batch_size,
4709 0);
4710
4711 ad_parallel_updates_pkg.get_rowid_range( v_start_rowid,
4712 v_end_rowid,
4713 v_any_rows_to_process,
4714 p_batch_size,
4715 TRUE);
4716
4717 WHILE (v_any_rows_to_process = TRUE) LOOP
4718
4719 FEM_ENGINES_PKG.User_Message(
4720 p_app_name => 'FEM',
4721 p_msg_text => 'Processing rowid from ' || v_start_rowid || ' to '|| v_end_rowid || pv_crlf
4722 );
4723
4724 v_upd_stmt1 := '
4725 UPDATE FEM_INTG_OGL_CCID_MAP M SET ';
4726
4727 FOR v_upd_dim_list IN c_upd_dim_list_cur (p_coa_id) LOOP
4728
4729 IF v_upd_dim_list.TARGET_COL = 'NATURAL_ACCOUNT_ID' THEN
4730
4731 -- Natural Account will always be SINGLESEG
4732 -- so explicit dim_mapping_option_code check not required
4733
4734 SELECT a.attribute_id
4735 ,v.version_id
4736 INTO v_ext_acct_type_attr_id
4737 ,v_ext_acct_type_ver_id
4738 FROM fem_dim_attributes_b a,
4739 fem_dim_attr_versions_b v
4740 WHERE a.dimension_id = 2
4741 AND a.attribute_varchar_label='EXTENDED_ACCOUNT_TYPE'
4742 AND v.attribute_id = a.attribute_id
4743 AND v.default_version_flag = 'Y';
4744
4745 --Start Bugfix 5653284
4746 v_upd_stmt1 := v_upd_stmt1 ||'
4747 EXTENDED_ACCOUNT_TYPE = NVL( (
4748 SELECT
4749 attr.dim_attribute_varchar_member
4750 FROM
4751 fem_nat_accts_attr attr,
4752 fem_nat_accts_b b,
4753 gl_code_combinations g
4754 WHERE
4755 attr.value_set_id = b.value_set_id AND
4756 attr.natural_account_id = b.natural_account_id AND
4757 attr.value_set_id = '||v_upd_dim_list.FEM_VALUE_SET_ID||' AND
4758 b.natural_account_display_code = g.' ||
4759 v_upd_dim_list.APPLICATION_COLUMN_NAME1 || ' AND
4760 g.chart_of_accounts_id = '||p_coa_id||' AND
4761 attr.attribute_id = '||v_ext_acct_type_attr_id||' AND
4762 attr.version_id = '||v_ext_acct_type_ver_id||' AND
4763 g.summary_flag = ''N'' AND
4764 m.code_combination_id = g.code_combination_id
4765 ), -1), ';
4766
4767 END IF;
4768
4769 IF v_upd_dim_list.dim_mapping_option_code = 'SINGLESEG' THEN
4770 v_upd_stmt1 := v_upd_stmt1 ||'
4771 '|| v_upd_dim_list.TARGET_COL || ' = NVL( (
4772 SELECT
4773 b.' || v_upd_dim_list.SOURCE_COL || '
4774 FROM
4775 ' || v_upd_dim_list.SOURCE_B_TABLE_NAME || ' B,
4776 gl_code_combinations g
4777 WHERE
4778 b.value_set_id = '||v_upd_dim_list.FEM_VALUE_SET_ID||' AND
4779 b.' || v_upd_dim_list.SOURCE_DISPLAY_CODE_COL ||
4780 ' = g.' || v_upd_dim_list.APPLICATION_COLUMN_NAME1 ||' AND
4781 g.chart_of_accounts_id = '||p_coa_id||' AND
4782 g.summary_flag = ''N'' AND
4783 m.code_combination_id = g.code_combination_id
4784 ), -1), ';
4785 --End Bugfix 5653284
4786 ELSE
4787 v_upd_stmt1 := v_upd_stmt1 ||'
4788 '|| v_upd_dim_list.TARGET_COL || ' = '|| v_upd_dim_list.DEFAULT_MEMBER_ID || ', ';
4789 END IF;
4790
4791 END LOOP;
4792
4793 v_upd_stmt1 := substr(v_upd_stmt1, 1, length(v_upd_stmt1)-2);
4794
4795 v_upd_stmt3 := '
4796 WHERE m.global_vs_combo_id = :pv_gvsc_id AND
4797 m.code_combination_id = :pv_ccid_val ';
4798
4799 v_ccid_update_stmt := v_upd_stmt1 || v_upd_stmt3;
4800
4801 v_ccid_cur_stmt := 'SELECT code_combination_id
4802 FROM fem_intg_ogl_ccid_map
4803 WHERE global_vs_combo_id = :pv_gvsc_id
4804 AND code_combination_id BETWEEN :max_ccid_processed AND :max_ccid_to_be_mapped
4805 AND rowid BETWEEN :rowid_low and :rowid_high';
4806
4807 IF (v_ccid_cur_stmt IS NOT NULL AND v_ccid_update_stmt IS NOT NULL) THEN
4808
4809 FEM_ENGINES_PKG.User_Message(
4810 p_app_name => 'FEM',
4811 p_msg_text => 'v_ccid_cur_stmt : '||v_ccid_cur_stmt
4812 );
4813
4814 FEM_ENGINES_PKG.User_Message(
4815 p_app_name => 'FEM',
4816 p_msg_text => 'v_ccid_update_stmt : '
4817 );
4818
4819 v_start_pos := 1;
4820
4821 LOOP
4822
4823 FEM_ENGINES_PKG.User_Message(
4824 p_app_name => 'FEM',
4825 p_msg_text => substr(v_ccid_update_stmt, v_start_pos, 4000)
4826 );
4827
4828 v_start_pos := v_start_pos + 4000;
4829 EXIT WHEN v_start_pos > length(v_ccid_update_stmt);
4830
4831 END LOOP;
4832 END IF;
4833
4834 -- start table update logic
4835 OPEN v_ccid_cur FOR v_ccid_cur_stmt
4836 USING p_gvsc_id, p_max_ccid_processed + 1, p_max_ccid_to_be_mapped, v_start_rowid, v_end_rowid;
4837
4838 LOOP
4839
4840 FETCH v_ccid_cur BULK COLLECT INTO v_ccid_list LIMIT pv_batch_size;
4841
4842 IF (v_ccid_list.FIRST IS NOT NULL AND v_ccid_list.LAST IS NOT NULL) THEN
4843
4844 FORALL i IN v_ccid_list.FIRST..v_ccid_list.LAST
4845 EXECUTE IMMEDIATE v_ccid_update_stmt
4846 USING p_gvsc_id, v_ccid_list(i);
4847
4848 END IF;
4849 EXIT WHEN v_ccid_cur%NOTFOUND;
4850 END LOOP;
4851
4852 CLOSE v_ccid_cur;
4853 -- end FEM update logic
4854 v_rows_processed := SQL%ROWCOUNT;
4855 ad_parallel_updates_pkg.processed_rowid_range( v_rows_processed,
4856 v_end_rowid);
4857 COMMIT;
4858
4859 ad_parallel_updates_pkg.get_rowid_range( v_start_rowid,
4860 v_end_rowid,
4861 v_any_rows_to_process,
4862 p_batch_size,
4863 FALSE);
4864
4865 END LOOP;
4866
4867 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
4868
4869 FEM_ENGINES_PKG.Tech_Message(
4870 p_severity => pc_log_level_procedure,
4871 p_module => v_module_name || '.end_worker',
4872 p_msg_text => '<< end of mapping table update worker >>'
4873 );
4874
4875 FEM_ENGINES_PKG.User_Message(
4876 p_app_name => 'FEM',
4877 p_msg_text => '<< end of mapping table update worker >>'
4878 );
4879
4880 EXCEPTION
4881 WHEN OTHERS THEN
4882 FEM_ENGINES_PKG.Tech_Message(
4883 p_severity => pc_log_level_exception,
4884 p_module => v_module_name || '.err_worker',
4885 p_msg_text => 'Worker Error '||SQLERRM
4886 );
4887
4888 FEM_ENGINES_PKG.User_Message(
4889 p_app_name => 'FEM',
4890 p_msg_text => 'Worker Error '||SQLERRM
4891 );
4892
4893 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
4894 raise;
4895
4896 END; -- end worker API
4897
4898 END fem_intg_new_dim_member_pkg;