[Home] [Help]
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;
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
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;
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:
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,
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
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'
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;
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;
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'
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;
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: */
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:
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:
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: */
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: /*
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
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 := '
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 := '
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 := '
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
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:
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.
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:
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:
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
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' || '
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 || '
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,';
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:
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
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;
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 || '
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:
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');
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
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:
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
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: */
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 := '
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 := '
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: */
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 := '
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 := '
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 := '
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
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:
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
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: */
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 := '
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 ' || '
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 || '
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,';
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;
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
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
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;
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 := '
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:
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:
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:
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(
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(
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,
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,
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,
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,
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
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' || ',
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(
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(
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(
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(
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(
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(
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) || ', ' ||
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 || ', ' ||
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') || ', ' ||
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
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
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;
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;
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:
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
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;
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'
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
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
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) || ')';
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);
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;
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:
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
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:
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:
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
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:
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 := '';
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';
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 := '
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 (
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 ||
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 || '
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,
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
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
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
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:
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(
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:
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
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(
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,
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',
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',
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',
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',
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;
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:
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
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,
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,
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,
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
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;
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:
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:
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:
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: /*
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
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
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
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',
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: );
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:
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
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:
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,
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:
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);
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:
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;
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:
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
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 || ','||
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;
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
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:
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
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:
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
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:
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
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 || '||''-''||'||
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;
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
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;
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
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;
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
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:
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
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:
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');
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:
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||'
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
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
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);
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;
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
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';
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
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;
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;
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'
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;
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
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;
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
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;
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
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;
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
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;
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
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
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
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)';
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)';
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||
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||
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||
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
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:
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 ,'
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 || ' '
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
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
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';
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 ,'
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 || ' '
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
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
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';
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 '
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 ,'
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 || ' '
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
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
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';
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 '
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 ,'
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 || ' '
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
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
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';
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 '
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 ,'
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 || ' '
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
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
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';
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'
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';
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';
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;
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
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 ||'
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';
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;
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:
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 ||'
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';
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;
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:
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 ||'
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';
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;
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:
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
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
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
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
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
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;
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
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
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;
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;
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
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
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;
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
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
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
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:
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';
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
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
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
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
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
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
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
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:
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
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 || ' '
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:
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
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
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
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,
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,
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,
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:
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
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:
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
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'
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,
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
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'
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
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
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
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
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
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;
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
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 || ' '
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:
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
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
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
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,
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,
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,
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:
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
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: (
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
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)
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';
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'
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
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) || ')';
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);
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 || ' = (
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
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;
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
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.';
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;
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
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;
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
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;
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'
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
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.';
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 ||
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;
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.'||
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;
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
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;
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)';
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
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
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
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
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;
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
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
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
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
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;
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:
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;
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;
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:
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
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
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',
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: );
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:
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
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:
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,
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:
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:
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;
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;
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(
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');
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
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