eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FEM_DIMENSION_MIGRATION_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 17


   Rob Flippo     25-JUL-06  Bug#5331497 insert_value_sets procedure
                             default_member_id, default_load_member_id and
                             default_hierarchy_obj_id should be null when
                             value_set is created, since these values all come
                             from sequence that is not identical between source
                             and target db
   mwickram       08-AUG-06  Bug 5287339 - DIMENSION HIERARCHY MIGRATION FAILS

 **************************************************************************/

-------------------------------
-- Declare package variables --
-------------------------------
   f_set_status  BOOLEAN;

Line: 99


FUNCTION GET_INSERT_B_SQL(p_source_db_link  IN VARCHAR2,
                          p_dim_varchar_lbl IN VARCHAR2) RETURN VARCHAR2;

Line: 102


FUNCTION GET_INSERT_DIM_GRP_B_SQL(p_source_db_link IN VARCHAR2) RETURN VARCHAR2;

Line: 104


FUNCTION GET_INSERT_DIM_GRP_TL_SQL(p_source_db_link IN VARCHAR2) RETURN VARCHAR2;

Line: 106


FUNCTION GET_INSERT_TL_SQL(p_source_db_link  IN VARCHAR2,
                           p_dim_varchar_lbl IN VARCHAR2) RETURN VARCHAR2;

Line: 120


PROCEDURE INSERT_CALENDARS(p_source_db_link IN VARCHAR2);

Line: 122


PROCEDURE INSERT_VALUE_SETS(p_source_db_link IN VARCHAR2);

Line: 163


PROCEDURE UPDATE_CALP_ATTRIBUTES(p_source_db_link IN VARCHAR2);

Line: 191


SELECT DB_LINK_NAME
INTO gv_db_link_name
FROM FEM_DB_LINKS_VL
WHERE UPPER(DATABASE_LINK) = UPPER(p_db_link);

Line: 197


l_db_link_sql   := 'SELECT SYSDATE FROM DUAL@'||p_db_link;

Line: 227


l_chk_dim_name_sql  VARCHAR2(1000) := 'SELECT DIMENSION_NAME ' ||
                                      'FROM FEM_DIMENSIONS_TL '||'@'||p_source_db_link||' '||
                                      'WHERE UPPER(DIMENSION_NAME) = UPPER(:src_dim_name)';

Line: 230


l_dim_name_sql   VARCHAR2(2000) := 'SELECT DIMENSION_ID, '||
       'USER_DEFINED_FLAG, '||
       'GROUP_USE_CODE, '||
       'VALUE_SET_REQUIRED_FLAG, '||
       'MEMBER_B_TABLE_NAME, '||
       'ATTRIBUTE_TABLE_NAME, '||
       'MEMBER_DISPLAY_CODE_COL, '||
       'MEMBER_NAME_COL, '||
       'MEMBER_DESCRIPTION_COL, '||
       'MEMBER_TL_TABLE_NAME, '||
       'MEMBER_COL, '||
       'DECODE(LOADER_OBJECT_DEF_ID, NULL, NULL, LOADER_OBJECT_DEF_ID+ 700), '||
       'HIERARCHY_TABLE_NAME, '||
       'DECODE(HIERARCHY_TABLE_NAME, NULL, NULL, HIERARCHY_TABLE_NAME||''_T'')'||
'FROM   FEM_XDIM_DIMENSIONS_VL '||'@'||p_source_db_link||' '||
'WHERE  DIMENSION_NAME = :p_source_user_dim_name';

Line: 249


SELECT DIMENSION_ID,
       DECODE(LOADER_OBJECT_DEF_ID, NULL, NULL, LOADER_OBJECT_DEF_ID+ 700),
       USER_DEFINED_FLAG,
       GROUP_USE_CODE,
       SIMPLE_DIMENSION_FLAG,
       VALUE_SET_REQUIRED_FLAG,
       INTF_MEMBER_B_TABLE_NAME,
       INTF_MEMBER_TL_TABLE_NAME,
       INTF_ATTRIBUTE_TABLE_NAME,
       MEMBER_B_TABLE_NAME,
       ATTRIBUTE_TABLE_NAME,
       MEMBER_DISPLAY_CODE_COL,
       MEMBER_NAME_COL,
       MEMBER_DESCRIPTION_COL,
       MEMBER_TL_TABLE_NAME,
       MEMBER_COL,
       HIERARCHY_TABLE_NAME,
       DECODE(HIERARCHY_TABLE_NAME, NULL, NULL, HIERARCHY_TABLE_NAME||'_T')
INTO   gv_dim_props_rec.DIMENSION_ID,
       gv_dim_props_rec.MIGRATION_OBJ_DEF_ID,
       gv_dim_props_rec.USER_DEFINED_FLAG,
       gv_dim_props_rec.GROUP_USE_CODE,
       gv_dim_props_rec.SIMPLE_DIMENSION_FLAG,
       gv_dim_props_rec.VALUE_SET_REQUIRED_FLAG,
       gv_dim_props_rec.INTF_MEMBER_B_TABLE_NAME,
       gv_dim_props_rec.INTF_MEMBER_TL_TABLE_NAME,
       gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME,
       gv_dim_props_rec.MEMBER_B_TABLE_NAME,
       gv_dim_props_rec.ATTRIBUTE_TABLE_NAME,
       gv_dim_props_rec.MEMBER_DISPLAY_CODE_COL,
       gv_dim_props_rec.MEMBER_NAME_COL,
       gv_dim_props_rec.MEMBER_DESCRIPTION_COL,
       gv_dim_props_rec.MEMBER_TL_TABLE_NAME,
       gv_dim_props_rec.MEMBER_COL,
       gv_dim_props_rec.HIERARCHY_TABLE_NAME,
       gv_dim_props_rec.HIERARCHY_INTF_TABLE_NAME
FROM   FEM_XDIM_DIMENSIONS_VL
WHERE  DIMENSION_VARCHAR_LABEL = UPPER(p_dim_name);

Line: 352


SELECT object_id
INTO gv_dim_props_rec.MIGRATION_OBJ_ID
FROM fem_object_definition_b
WHERE object_definition_id = gv_dim_props_rec.MIGRATION_OBJ_DEF_ID
AND object_id IN (SELECT object_id FROM fem_object_catalog_b
WHERE object_type_code = 'DIM_MEMBER_MIGRATION');

Line: 359


SELECT object_id
INTO gv_src_dim_props_rec.MIGRATION_OBJ_ID
FROM fem_object_definition_b
WHERE object_definition_id = gv_src_dim_props_rec.MIGRATION_OBJ_DEF_ID
AND object_id IN (SELECT object_id FROM fem_object_catalog_b
WHERE object_type_code = 'DIM_MEMBER_MIGRATION');

Line: 394


l_chk_version_name_sql     VARCHAR2(1000) := 'SELECT VERSION_NAME ' ||
                                             'FROM FEM_DIM_ATTR_VERSIONS_TL '||'@'||p_source_db_link||' '||
                                             'WHERE UPPER(VERSION_NAME) = UPPER(:p_version_name)';

Line: 398


l_chk_version_disp_cd_sql  VARCHAR2(1000) := 'SELECT VERSION_DISPLAY_CODE' ||
                                             'FROM FEM_DIM_ATTR_VERSIONS_B '||'@'||p_source_db_link||' '||
                                             'WHERE UPPER(VERSION_DISPLAY_CODE) = UPPER(:p_version_disp_cd)';

Line: 505


FUNCTION get_insert_b_sql(p_source_db_link IN VARCHAR2, p_dim_varchar_lbl IN VARCHAR2) RETURN VARCHAR2

IS

l_dim_vc_select VARCHAR2(35) := ''''||p_dim_varchar_lbl||'''';

Line: 510


l_base_select VARCHAR2(35) := 'B.'||gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL;

Line: 511


l_vs_select VARCHAR2(35) := 'VS.VALUE_SET_DISPLAY_CODE';

Line: 512


l_dg_select VARCHAR2(35) := 'DG.DIMENSION_GROUP_DISPLAY_CODE';

Line: 513


l_status_select VARCHAR2(10) := '''LOAD''';

Line: 514


l_batch_name_select VARCHAR2(30) := '''Y''';

Line: 523


l_insert_table  VARCHAR2(100) := gv_dim_props_rec.INTF_MEMBER_B_TABLE_NAME;

Line: 526


l_where_clause VARCHAR2(1000) := --' WHERE NOT EXISTS (SELECT 1 FROM '||l_insert_table||' INTF WHERE INTF.'||
                                -- gv_dim_props_rec.MEMBER_DISPLAY_CODE_COL||' = '||l_base_select||')'||
                                ' WHERE {{data_slice}}';

Line: 531


l_cal_period_select VARCHAR2(1000) := 'CA2.DATE_ASSIGN_VALUE, CA1.NUMBER_ASSIGN_VALUE, ''LOAD'', CAL.CALENDAR_DISPLAY_CODE, '||
                                      'DG.DIMENSION_GROUP_DISPLAY_CODE, ''Y''';

Line: 548


l_insert_sql VARCHAR2(32767);

Line: 555


  l_insert_sql := 'INSERT INTO '||l_insert_table||
'(SELECT '|| l_cal_period_select||
' FROM '|| l_cal_period_from||l_cal_period_where||')';

Line: 562


l_base_select := l_base_select||l_comma1;

Line: 563


l_status_select := l_status_select||l_comma1;

Line: 567


  l_vs_select := l_vs_select ||l_comma1;

Line: 570


  l_vs_select := NULL;

Line: 576


  l_dg_select := NULL;

Line: 580


  l_dg_select := l_dg_select||l_comma1;

Line: 584


  l_dim_vc_select := l_dim_vc_select||l_comma1;

Line: 586


  l_dim_vc_select := NULL;

Line: 603


l_insert_sql := 'INSERT INTO '||l_insert_table||
'(SELECT '|| l_dim_vc_select||l_base_select||
             l_vs_select||
             l_status_select||
             l_dg_select||
             l_batch_name_select||
' FROM '|| l_base_from||
           l_vs_from||
           l_dim_grp_from||l_where_clause||')';

Line: 616


RETURN l_insert_sql;

Line: 635


FUNCTION get_insert_tl_sql(p_source_db_link IN VARCHAR2, p_dim_varchar_lbl IN VARCHAR2) RETURN VARCHAR2

IS

l_dim_vc_select VARCHAR2(35) := ''''||p_dim_varchar_lbl||'''';

Line: 640


l_tl_dc_select VARCHAR2(35) := 'B.'||gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL;

Line: 641


l_vs_select VARCHAR2(35) := 'VS.VALUE_SET_DISPLAY_CODE';

Line: 642


l_status_select VARCHAR2(10) := '''LOAD''';

Line: 643


l_tl_lang_select VARCHAR2(35) := 'TL.LANGUAGE';

Line: 644


l_tl_name_select VARCHAR2(35) := 'TL.'||gv_src_dim_props_rec.MEMBER_NAME_COL;

Line: 645


l_tl_desc_select VARCHAR2(35) := 'TL.'||gv_src_dim_props_rec.MEMBER_DESCRIPTION_COL;

Line: 646


l_batch_name_select VARCHAR2(30) := '''Y''';

Line: 654


l_insert_table  VARCHAR2(100) := gv_dim_props_rec.INTF_MEMBER_TL_TABLE_NAME;

Line: 660


                                ' AND {{data_slice}} AND EXISTS (SELECT 1 FROM FND_LANGUAGES L'||
                                ' WHERE TL.LANGUAGE = L.LANGUAGE_CODE AND L.INSTALLED_FLAG IN (''I'',''B''))';

Line: 665


l_cal_period_select VARCHAR2(1000) := 'CA2.DATE_ASSIGN_VALUE, CA1.NUMBER_ASSIGN_VALUE, TL.LANGUAGE, '||
                                     'TL.CAL_PERIOD_NAME, TL.DESCRIPTION, ''LOAD'', CAL.CALENDAR_DISPLAY_CODE, '||
                                      'DG.DIMENSION_GROUP_DISPLAY_CODE, ''Y''';

Line: 682


                      ' AND EXISTS (SELECT 1 FROM FND_LANGUAGES L'||
                                ' WHERE TL.LANGUAGE = L.LANGUAGE_CODE AND L.INSTALLED_FLAG IN (''I'',''B''))';

Line: 685


l_insert_sql VARCHAR2(32767);

Line: 692


  l_insert_sql := 'INSERT INTO '||l_insert_table||
'(SELECT '|| l_cal_period_select||
' FROM '|| l_cal_period_from||l_cal_period_where||')';

Line: 698


l_tl_dc_select  := l_tl_dc_select ||l_comma1;

Line: 699


l_tl_lang_select := l_tl_lang_select||l_comma1;

Line: 700


l_tl_name_select  := l_tl_name_select ||l_comma1;

Line: 701


l_tl_desc_select  := l_tl_desc_select ||l_comma1;

Line: 702


l_status_select := l_status_select||l_comma1;

Line: 705


  l_dim_vc_select := l_dim_vc_select||l_comma1;

Line: 707


  l_dim_vc_select := NULL;

Line: 711


  l_vs_select := l_vs_select ||l_comma1;

Line: 714


  l_vs_select := NULL;

Line: 720


  l_dg_select := NULL;

Line: 724


  l_status_select := l_status_select||l_comma1;

Line: 734


l_insert_sql := 'INSERT INTO '||l_insert_table||
'(SELECT '|| l_dim_vc_select||l_tl_dc_select||
             l_vs_select||
             l_tl_lang_select||
             l_tl_name_select||
             l_tl_desc_select||
             l_status_select||
             l_batch_name_select||
' FROM '|| l_tl_from||
           l_vs_from||l_where_clause||')';

Line: 748


RETURN l_insert_sql;

Line: 763


l_insert_clause VARCHAR2(100) :=
  ' INSERT INTO '|| gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME;

Line: 766


l_insert_cols  VARCHAR2(2000) :=
  ' ( '||gv_dim_props_rec.MEMBER_DISPLAY_CODE_COL||', ATTRIBUTE_VARCHAR_LABEL,'||
  ' ATTRIBUTE_ASSIGN_VALUE, ATTR_ASSIGN_VS_DISPLAY_CODE, STATUS, CREATED_BY_DIM_MIGRATION_FLAG,'||
  ' CALPATTR_CAL_DISPLAY_CODE, CALPATTR_DIMGRP_DISPLAY_CODE, CALPATTR_END_DATE, CALPATTR_PERIOD_NUM,'||
  ' VERSION_DISPLAY_CODE'||
  ' {{vs_insert_col}} {{calp_insert_col}} )';

Line: 773


l_calp_insert_cols VARCHAR2(500) :=
  ' ,CAL_PERIOD_END_DATE, CAL_PERIOD_NUMBER, CALENDAR_DISPLAY_CODE, DIMENSION_GROUP_DISPLAY_CODE';

Line: 776


l_shared_vc_insert VARCHAR2(50) := 'DIMENSION_VARCHAR_LABEL';

Line: 780


l_vs_insert_cols  VARCHAR2(50) :=
  ' ,VALUE_SET_DISPLAY_CODE';

Line: 783


l_select_clause  VARCHAR2(2000) :=
  ' ( SELECT {{vc_select_clause}}'||
  ' B.'|| gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL||
  ' ,DA.ATTRIBUTE_VARCHAR_LABEL'||
  ' ,DECODE(DA.ATTRIBUTE_VALUE_COLUMN_NAME'||
  '   ,''DATE_ASSIGN_VALUE'', TO_CHAR(ATTR.DATE_ASSIGN_VALUE, ''{{icx_date_format}}'')'||
  '   ,''NUMBER_ASSIGN_VALUE'', TO_CHAR(ATTR.NUMBER_ASSIGN_VALUE)'||
  '   ,''VARCHAR_ASSIGN_VALUE'', ATTR.VARCHAR_ASSIGN_VALUE'||
  '   ,''DIM_ATTRIBUTE_NUMERIC_MEMBER'', FEM_DIMENSION_UTIL_PKG.Get_Dim_Member_Display_Code@'||p_source_db_link||'('||
  '     DA.ATTRIBUTE_DIMENSION_ID'||
  '     ,TO_CHAR(ATTR.DIM_ATTRIBUTE_NUMERIC_MEMBER)'||
  '     ,ATTR.DIM_ATTRIBUTE_VALUE_SET_ID)'||
  '   ,''DIM_ATTRIBUTE_VARCHAR_MEMBER'', FEM_DIMENSION_UTIL_PKG.Get_Dim_Member_Display_Code@'||p_source_db_link||'('||
  '     DA.ATTRIBUTE_DIMENSION_ID'||
  '     ,ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER'||
  '     ,ATTR.DIM_ATTRIBUTE_VALUE_SET_ID)'||
  ' ) AS ATTR_ASSIGN_VALUE'||
  ' ,DECODE(DA.ATTRIBUTE_DATA_TYPE_CODE'||
  '   ,''DIMENSION'', DECODE(ATTR.DIM_ATTRIBUTE_VALUE_SET_ID'||
  '     ,NULL,NULL'||
  '     ,FEM_MIR_PKG.Get_Dim_Member_Display_Code@'||p_source_db_link||'('||
  '       ''VALUE_SET'',ATTR.DIM_ATTRIBUTE_VALUE_SET_ID)'||
  '   )'||
  '   ,NULL'||
  ' ) AS ATTR_ASSIGN_VS_DISPLAY_CODE'||
  ' ,DECODE(DA.ATTRIBUTE_DIMENSION_ID, 1, ''UPDATE'', ''LOAD'')'||
  ' ,''Y'''||
  ' ,NULL AS CALPATTR_CAL_DISPLAY_CODE'||
  ' ,NULL AS CALPATTR_DIMGRP_DISPLAY_CODE'||
  ' ,NULL AS CALPATTR_END_DATE'||
  ' ,NULL AS CALP_ATTR_PERIOD_NUM'||
  ' ,AV.VERSION_DISPLAY_CODE'||
  ' {{vs_select_clause}} {{calp_select_clause}}';

Line: 817


l_dim_vc_select VARCHAR2(50) := ''''||p_dim_varchar_lbl||''''||',';

Line: 819


l_calp_select VARCHAR2(500) :=
  ' ,CA2.DATE_ASSIGN_VALUE, CA1.NUMBER_ASSIGN_VALUE, CAL.CALENDAR_DISPLAY_CODE'||
  ' ,DG.DIMENSION_GROUP_DISPLAY_CODE';

Line: 823


l_vs_select_clause VARCHAR2(50) :=
  ' ,VS.VALUE_SET_DISPLAY_CODE';

Line: 870


l_insert_sql  VARCHAR2(32767);

Line: 878


      l_select_clause := REPLACE(l_select_clause,'{{icx_date_format}}',l_icx_date_format);

Line: 882


        l_insert_cols := REPLACE(l_insert_cols, gv_dim_props_rec.MEMBER_DISPLAY_CODE_COL||', ', '');

Line: 883


        l_insert_cols := REPLACE(l_insert_cols, '{{calp_insert_col}}', l_calp_insert_cols);

Line: 884


        l_select_clause := REPLACE(l_select_clause,'{{calp_select_clause}}',l_calp_select);

Line: 885


        l_select_clause := REPLACE(l_select_clause,'B.'|| gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL ||',' , '');

Line: 889


        l_insert_cols := REPLACE(l_insert_cols, '{{calp_insert_col}}','');

Line: 890


        l_select_clause := REPLACE(l_select_clause,'{{calp_select_clause}}','');

Line: 897


        l_insert_cols := REPLACE(l_insert_cols, gv_dim_props_rec.MEMBER_DISPLAY_CODE_COL,
                                 l_shared_vc_insert||l_shared_member_col);

Line: 899


        l_select_clause := REPLACE(l_select_clause,'{{vc_select_clause}}',l_dim_vc_select);

Line: 901


        l_select_clause := REPLACE(l_select_clause,'{{vc_select_clause}}','');

Line: 906


         l_insert_cols := REPLACE(l_insert_cols,'{{vs_insert_col}}',l_vs_insert_cols);

Line: 907


         l_select_clause := REPLACE(l_select_clause,'{{vs_select_clause}}',l_vs_select_clause);

Line: 911


         l_insert_cols := REPLACE(l_insert_cols,'{{vs_insert_col}}','');

Line: 912


         l_select_clause := REPLACE(l_select_clause,'{{vs_select_clause}}','');

Line: 923


l_insert_sql := l_insert_clause||l_insert_cols||l_select_clause||l_from_clause||l_where_clause;

Line: 926


RETURN l_insert_sql;

Line: 944


l_dim_sql VARCHAR2(500) := 'SELECT MEMBER_B_TABLE_NAME,'||
                       ' MEMBER_DISPLAY_CODE_COL,'||
                       ' MEMBER_COL '||
                       ' FROM '||l_xdim_table||
                       ' WHERE DIMENSION_ID = :attr_dimension_id';

Line: 959


l_attr_dc_sql := 'SELECT '||l_display_code_col ||
              ' FROM '||l_table_name ||
              ' WHERE '||l_member_col||' = :value';

Line: 978


PROCEDURE update_calp_attributes(p_source_db_link IN VARCHAR2)

IS

l_attr_assign_value  VARCHAR2(1000);

Line: 989


'SELECT ATTRIBUTE_ASSIGN_VALUE '||
' FROM '||gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME||
' WHERE STATUS = ''UPDATE''';

Line: 993


l_select_sql VARCHAR2(2000) :=
'SELECT CAL.CALENDAR_DISPLAY_CODE,'||
' DG.DIMENSION_GROUP_DISPLAY_CODE,'||
' ATTR.NUMBER_ASSIGN_VALUE AS GL_PERIOD_NUM,'||
' ATTR1.DATE_ASSIGN_VALUE AS CAL_PERIOD_END_DATE'||
' FROM   FEM_CALENDARS_B@'||p_source_db_link||' CAL,'||
' FEM_DIMENSION_GRPS_B@'||p_source_db_link||' DG,'||
' FEM_CAL_PERIODS_B@'||p_source_db_link||' CP,'||
' FEM_CAL_PERIODS_ATTR@'||p_source_db_link||' ATTR,'||
' FEM_DIM_ATTRIBUTES_B@'||p_source_db_link||' DA,'||
' FEM_DIM_ATTR_VERSIONS_B@'||p_source_db_link||' AV,'||
' FEM_CAL_PERIODS_ATTR@'||p_source_db_link||' ATTR1,'||
' FEM_DIM_ATTRIBUTES_B@'||p_source_db_link||' DA1,'||
' FEM_DIM_ATTR_VERSIONS_B@'||p_source_db_link||' AV1'||
' WHERE  CP.CAL_PERIOD_ID = :cal_period_id'||
' AND    CP.CALENDAR_ID = CAL.CALENDAR_ID'||
' AND    DG.DIMENSION_GROUP_ID = CP.DIMENSION_GROUP_ID'||
' AND    ATTR.ATTRIBUTE_ID = DA.ATTRIBUTE_ID'||
' AND    ATTR.CAL_PERIOD_ID = CP.CAL_PERIOD_ID'||
' AND    DA.ATTRIBUTE_VARCHAR_LABEL = ''GL_PERIOD_NUM'''||
' AND    ATTR.VERSION_ID = AV.VERSION_ID'||
' AND    AV.DEFAULT_VERSION_FLAG = ''Y'''||
' AND    ATTR1.ATTRIBUTE_ID = DA1.ATTRIBUTE_ID'||
' AND    ATTR1.CAL_PERIOD_ID = CP.CAL_PERIOD_ID'||
' AND    DA1.ATTRIBUTE_VARCHAR_LABEL = ''CAL_PERIOD_END_DATE'''||
' AND    ATTR1.VERSION_ID = AV1.VERSION_ID'||
' AND    AV1.DEFAULT_VERSION_FLAG = ''Y''';

Line: 1021


l_update_sql VARCHAR2(1000):= 'UPDATE '||gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME||
                ' SET ATTRIBUTE_ASSIGN_VALUE = NULL,'||
                ' CALPATTR_CAL_DISPLAY_CODE = :cal_display_code,'||
                ' CALPATTR_DIMGRP_DISPLAY_CODE = :dim_grp_display_code,'||
                ' CALPATTR_END_DATE = :calp_end_date,'||
                ' CALPATTR_PERIOD_NUM = :calp_period_num,'||
                ' STATUS = ''LOAD'''||
                ' WHERE ATTRIBUTE_ASSIGN_VALUE = :cal_period_id'||
                ' AND STATUS = ''UPDATE'' AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';

Line: 1031


TYPE UPDATE_CALP_CURSOR_TYPE IS REF CURSOR;

Line: 1033


update_calp_cur UPDATE_CALP_CURSOR_TYPE;

Line: 1037


OPEN update_calp_cur FOR l_calp_intf_attr_sql;

Line: 1041


FETCH update_calp_cur INTO l_attr_assign_value;

Line: 1043


EXIT WHEN update_calp_cur%NOTFOUND;

Line: 1045


EXECUTE IMMEDIATE l_select_sql
INTO    l_cal_display_code,
        l_dim_grp_display_code,
        l_calp_period_num,
        l_calp_end_date
USING   l_attr_assign_value;

Line: 1053


EXECUTE IMMEDIATE l_update_sql
USING   l_cal_display_code,
        l_dim_grp_display_code,
        l_calp_end_date,
        l_calp_period_num,
        l_attr_assign_value;

Line: 1079


l_insert_clause VARCHAR2(100) := 'INSERT INTO '|| gv_dim_props_rec.HIERARCHY_INTF_TABLE_NAME;

Line: 1081


l_insert_cols  VARCHAR2(500) := '(HIERARCHY_OBJECT_NAME, HIERARCHY_OBJ_DEF_DISPLAY_NAME, DISPLAY_ORDER_NUM, WEIGHTING_PCT, STATUS, LANGUAGE, CREATED_BY_DIM_MIGRATION_FLAG {{dc_insert}} {{vs_insert}} {{calp_insert}})';

Line: 1083


l_dc_insert VARCHAR2(100) := ', PARENT_DISPLAY_CODE, CHILD_DISPLAY_CODE';

Line: 1085


l_calp_insert VARCHAR2(500) := ', CALENDAR_DISPLAY_CODE, PARENT_DIM_GRP_DISPLAY_CODE, CHILD_DIM_GRP_DISPLAY_CODE, PARENT_CAL_PERIOD_NUMBER, PARENT_CAL_PERIOD_END_DATE, CHILD_CAL_PERIOD_NUMBER, CHILD_CAL_PERIOD_END_DATE';

Line: 1087


l_vs_insert VARCHAR2(100) := ', PARENT_VALUE_SET_DISPLAY_CODE, CHILD_VALUE_SET_DISPLAY_CODE';

Line: 1090


l_select_clause  VARCHAR2(1500) := '(SELECT '''||p_hier_obj_name||''', '''||p_hier_obj_def_name||''','||
                                   ' HIER.DISPLAY_ORDER_NUM, HIER.WEIGHTING_PCT, ''LOAD'', '''||USERENV('LANG')||''', ''Y'' {{dc_select}} {{vs_select}} {{calp_select}}';

Line: 1094


l_dc_select VARCHAR2(100) := ', B1.'||gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL||', B2.'||gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL;

Line: 1096


l_vs_select VARCHAR2(100) := ', VS1.VALUE_SET_DISPLAY_CODE, VS2.VALUE_SET_DISPLAY_CODE';

Line: 1099


l_calp_select VARCHAR2(500) := ', CAL.CALENDAR_DISPLAY_CODE, DG1.DIMENSION_GROUP_DISPLAY_CODE, DG2.DIMENSION_GROUP_DISPLAY_CODE, CA1.NUMBER_ASSIGN_VALUE, CA2.DATE_ASSIGN_VALUE, CA3.NUMBER_ASSIGN_VALUE, CA4.DATE_ASSIGN_VALUE';

Line: 1146


l_insert_sql  VARCHAR2(32767);

Line: 1151


l_insert_cols := REPLACE(l_insert_cols, '{{dc_insert}}', l_dc_insert);

Line: 1152


l_insert_cols := REPLACE(l_insert_cols, '{{calp_insert}}', '');

Line: 1154


l_select_clause:= REPLACE(l_select_clause, '{{dc_select}}', l_dc_select);

Line: 1155


l_select_clause:= REPLACE(l_select_clause, '{{calp_select}}', '');

Line: 1164


l_insert_cols := REPLACE(l_insert_cols, '{{calp_insert}}', l_calp_insert);

Line: 1165


l_insert_cols := REPLACE(l_insert_cols, '{{dc_insert}}', '');

Line: 1166


l_select_clause:= REPLACE(l_select_clause, '{{calp_select}}', l_calp_select);

Line: 1167


l_select_clause:= REPLACE(l_select_clause, '{{dc_select}}', '');

Line: 1180


l_insert_cols := REPLACE(l_insert_cols, '{{vs_insert}}', l_vs_insert);

Line: 1181


l_select_clause:= REPLACE(l_select_clause, '{{vs_select}}', l_vs_select);

Line: 1186


l_insert_cols := REPLACE(l_insert_cols, '{{vs_insert}}', '');

Line: 1187


l_select_clause:= REPLACE(l_select_clause, '{{vs_select}}', '');

Line: 1194


l_insert_sql := l_insert_clause||l_insert_cols||l_select_clause||l_from_clause||l_where_clause;

Line: 1197


RETURN l_insert_sql;

Line: 1221


l_insert_clause VARCHAR2(100) := 'INSERT INTO FEM_HIERARCHIES_T';

Line: 1224


l_select_clause  VARCHAR2(1500);

Line: 1230


l_insert_sql  VARCHAR2(32767);

Line: 1256


l_select_clause := '(SELECT '''||p_hier_obj_name||''', '''||l_folder_name||''','||
                                   ''''||USERENV('LANG')||''', '''||p_dim_varchar_lbl||''','||
                                   ' HIERARCHY_TYPE_CODE, GROUP_SEQUENCE_ENFORCED_CODE, MULTI_TOP_FLAG, MULTI_VALUE_SET_FLAG,'||
                                   ' HIERARCHY_USAGE_CODE, FLATTENED_ROWS_FLAG, ''LOAD'', '''||p_hier_obj_def_name||''','||
                                   l_eff_start_date_str||', '||l_eff_end_date_str||', NULL, ''Y''';

Line: 1262


l_insert_sql := l_insert_clause||l_select_clause||l_from_clause||l_where_clause;

Line: 1264


RETURN l_insert_sql;

Line: 1276


l_insert_clause VARCHAR2(100) := 'INSERT INTO FEM_HIER_VALUE_SETS_T';

Line: 1278


l_insert_select VARCHAR2(1500) := '(SELECT '''||p_hier_obj_name||''', VS.VALUE_SET_DISPLAY_CODE, '''||USERENV('LANG')||''', ''LOAD'', ''Y''';

Line: 1281


l_insert_from   VARCHAR2(200) := ' FROM FEM_HIER_VALUE_SETS@'||p_source_db_link||
                                 ' HVS, FEM_VALUE_SETS_B@'||p_source_db_link||' VS';

Line: 1283


l_insert_where  VARCHAR2(500) := ' WHERE HVS.HIERARCHY_OBJ_ID = :hier_obj_id'||
                                 ' AND HVS.VALUE_SET_ID = VS.VALUE_SET_ID)';

Line: 1286


l_insert_sql VARCHAR2(32767) := l_insert_clause || l_insert_select || l_insert_from || l_insert_where;

Line: 1289


RETURN l_insert_sql;

Line: 1300


l_insert_clause VARCHAR2(100) := 'INSERT INTO FEM_HIER_DIM_GRPS_T';

Line: 1302


l_insert_select VARCHAR2(1500) := '(SELECT '''||p_hier_obj_name||''', '''||USERENV('LANG')||''', ''LOAD'', DG.DIMENSION_GROUP_DISPLAY_CODE, ''Y''';

Line: 1305


l_insert_from   VARCHAR2(200) := ' FROM FEM_HIER_DIMENSION_GRPS@'||p_source_db_link||
                                 ' HDG, FEM_DIMENSION_GRPS_B@'||p_source_db_link||' DG';

Line: 1307


l_insert_where  VARCHAR2(500) := ' WHERE HDG.HIERARCHY_OBJ_ID = :hier_obj_id'||
                                 ' AND HDG.DIMENSION_GROUP_ID = DG.DIMENSION_GROUP_ID)';

Line: 1310


l_insert_sql VARCHAR2(32767) := l_insert_clause || l_insert_select || l_insert_from || l_insert_where;

Line: 1313


RETURN l_insert_sql;

Line: 1319


FUNCTION get_insert_dim_grp_b_sql(p_source_db_link IN VARCHAR2) RETURN VARCHAR2

IS

l_insert_clause VARCHAR2(100) := 'INSERT INTO FEM_DIMENSION_GRPS_B_T';

Line: 1324


l_insert_select VARCHAR2(200) := ' (SELECT B.DIMENSION_GROUP_DISPLAY_CODE, D.DIMENSION_VARCHAR_LABEL,'||
                                 'B.DIMENSION_GROUP_SEQ, ''LOAD'', B.TIME_GROUP_TYPE_CODE, ''Y''';

Line: 1327


l_insert_from   VARCHAR2(200) := ' FROM FEM_DIMENSION_GRPS_B@'||p_source_db_link||
                                 ' B, FEM_DIMENSIONS_B@'||p_source_db_link||' D';

Line: 1329


l_insert_where  VARCHAR2(500) := ' WHERE B.DIMENSION_ID = D.DIMENSION_ID'||
                                 ' AND D.DIMENSION_ID = :dimension_id'||
                                 ' AND NOT EXISTS (SELECT 1 FROM FEM_DIMENSION_GRPS_B_T'||
                                 ' WHERE DIMENSION_GROUP_DISPLAY_CODE = B.DIMENSION_GROUP_DISPLAY_CODE'||
                                 ' AND DIMENSION_VARCHAR_LABEL = D.DIMENSION_VARCHAR_LABEL'||
                                 ' AND DIMENSION_GROUP_SEQ = B.DIMENSION_GROUP_SEQ))';

Line: 1336


l_insert_sql VARCHAR2(32767) := l_insert_clause || l_insert_select || l_insert_from || l_insert_where;

Line: 1339


RETURN l_insert_sql;

Line: 1348


FUNCTION get_insert_dim_grp_tl_sql(p_source_db_link IN VARCHAR2) RETURN VARCHAR2

IS

l_insert_clause VARCHAR2(100) := 'INSERT INTO FEM_DIMENSION_GRPS_TL_T';

Line: 1353


l_insert_select VARCHAR2(200) := ' (SELECT B.DIMENSION_GROUP_DISPLAY_CODE, TL.LANGUAGE,'||
                                 ' TL.DIMENSION_GROUP_NAME, TL.DESCRIPTION, ''LOAD'','||
                                 ' D.DIMENSION_VARCHAR_LABEL, ''Y''';

Line: 1357


l_insert_from   VARCHAR2(200) := ' FROM FEM_DIMENSION_GRPS_B@'||p_source_db_link||
                                 ' B, FEM_DIMENSION_GRPS_TL@'||p_source_db_link||
                                 ' TL, FEM_DIMENSIONS_B@'||p_source_db_link||' D';

Line: 1360


l_insert_where  VARCHAR2(1000) := ' WHERE B.DIMENSION_GROUP_ID = TL.DIMENSION_GROUP_ID'||
                                 ' AND B.DIMENSION_ID = D.DIMENSION_ID'||
                                -- ' AND B.DIMENSION_ID = TL.DIMENSION_ID'||
                                 ' AND TL.DIMENSION_ID = :dimension_id'||
                                 ' AND EXISTS (SELECT 1 FROM FND_LANGUAGES L'||
                                 ' WHERE TL.LANGUAGE = L.LANGUAGE_CODE AND L.INSTALLED_FLAG IN (''I'',''B''))'||
                                 ' AND NOT EXISTS (SELECT 1 FROM FEM_DIMENSION_GRPS_TL_T'||
                                 ' WHERE DIMENSION_GROUP_DISPLAY_CODE = B.DIMENSION_GROUP_DISPLAY_CODE'||
                                 ' AND DIMENSION_VARCHAR_LABEL = D.DIMENSION_VARCHAR_LABEL'||
                                 ' AND LANGUAGE = TL.LANGUAGE'||
                                 ' AND DIMENSION_GROUP_NAME = TL.DIMENSION_GROUP_NAME))';

Line: 1372


l_insert_sql VARCHAR2(32767) := l_insert_clause || l_insert_select || l_insert_from || l_insert_where;

Line: 1376


RETURN l_insert_sql;

Line: 1391


PROCEDURE insert_value_sets(p_source_db_link IN VARCHAR2)

IS

l_row_id  rowid_type; --ROWID;

Line: 1407


'SELECT ROW_ID, FEM_VALUE_SETS_B_S.NEXTVAL,'||
'       VALUE_SET_NAME, DESCRIPTION, null,'||
'       VALUE_SET_DISPLAY_CODE,'||
'       null,'||
'       READ_ONLY_FLAG'||
' FROM   FEM_VALUE_SETS_VL@'||p_source_db_link||' A'||
' WHERE  A.DIMENSION_ID = '||gv_src_dim_props_rec.DIMENSION_ID||
' AND    NOT EXISTS (SELECT 1'||
'                  FROM FEM_VALUE_SETS_VL'||
'                  WHERE VALUE_SET_DISPLAY_CODE = A.VALUE_SET_DISPLAY_CODE)';

Line: 1418


TYPE INSERT_VS_CURSOR_TYPE IS REF CURSOR;

Line: 1420


insert_vs_cur INSERT_VS_CURSOR_TYPE;

Line: 1429


OPEN insert_vs_cur FOR l_vs_sql;

Line: 1433


FETCH insert_vs_cur BULK COLLECT INTO
l_row_id,
l_vs_id,
l_vs_name,
l_vs_desc,
l_default_member_id,
l_vs_dc,
l_default_hier_obj_id,
l_read_only_flag
LIMIT 10000;

Line: 1457


SELECT VALUE_SET_NAME
INTO l_dup_vs_name
FROM FEM_VALUE_SETS_VL
WHERE VALUE_SET_NAME = l_vs_name(i);

Line: 1465


       p_module => c_block||'.insert_value_sets',
       p_msg_text => 'Duplicate vs name found in db '|| l_dup_vs_name);

Line: 1475


FEM_VALUE_SETS_PKG.INSERT_ROW(X_ROWID => l_row_id(i),
                              X_VALUE_SET_ID => l_vs_id(i),
                              X_DEFAULT_LOAD_MEMBER_ID => null,
                              X_DEFAULT_MEMBER_ID => null,
                              X_OBJECT_VERSION_NUMBER => 1,
                              X_DEFAULT_HIERARCHY_OBJ_ID => null,
                              X_READ_ONLY_FLAG => l_read_only_flag(i),
                              X_VALUE_SET_DISPLAY_CODE => l_vs_dc(i),
                              X_DIMENSION_ID => gv_src_dim_props_rec.DIMENSION_ID,
                              X_VALUE_SET_NAME => l_vs_name(i),
                              X_DESCRIPTION => l_vs_desc(i),
                              X_CREATION_DATE => sysdate,
                              X_CREATED_BY => gv_apps_user_id,
                              X_LAST_UPDATE_DATE => sysdate,
                              X_LAST_UPDATED_BY => gv_apps_user_id,
                              X_LAST_UPDATE_LOGIN => gv_login_id);

Line: 1505


PROCEDURE insert_calendars(p_source_db_link IN VARCHAR2)

IS

l_row_id  rowid_type;

Line: 1521


'SELECT ROW_ID, FEM_CALENDARS_B_S.NEXTVAL,'||
'       CALENDAR_NAME, DESCRIPTION, ENABLED_FLAG,'||
'       CALENDAR_DISPLAY_CODE,'||
'       PERSONAL_FLAG,'||
'       READ_ONLY_FLAG'||
' FROM   FEM_CALENDARS_VL@'||p_source_db_link||' A'||
' WHERE  NOT EXISTS (SELECT 1'||
'                  FROM FEM_CALENDARS_VL'||
'                  WHERE CALENDAR_DISPLAY_CODE = A.CALENDAR_DISPLAY_CODE)';

Line: 1531


TYPE INSERT_CAL_CURSOR_TYPE IS REF CURSOR;

Line: 1533


insert_cal_cur INSERT_CAL_CURSOR_TYPE;

Line: 1542


OPEN insert_cal_cur FOR l_cal_sql;

Line: 1546


FETCH insert_cal_cur BULK COLLECT INTO
l_row_id,
l_cal_id,
l_cal_name,
l_cal_desc,
l_enabled_flag,
l_cal_dc,
l_personal_flag,
l_read_only_flag
LIMIT 10000;

Line: 1569


SELECT CALENDAR_NAME
INTO l_dup_cal_name
FROM FEM_CALENDARS_VL
WHERE CALENDAR_NAME = l_cal_name(i);

Line: 1579


FEM_CALENDARS_PKG.INSERT_ROW (
  X_ROWID  => l_row_id(i),
  X_CALENDAR_ID => l_cal_id(i),
  X_READ_ONLY_FLAG => l_read_only_flag(i),
  X_PERSONAL_FLAG => l_personal_flag(i),
  X_ENABLED_FLAG => l_enabled_flag(i),
  X_CALENDAR_DISPLAY_CODE => l_cal_dc(i),
  X_OBJECT_VERSION_NUMBER => 1,
  X_CALENDAR_NAME => l_cal_name(i),
  X_DESCRIPTION => l_cal_desc(i),
  X_CREATION_DATE => sysdate,
  X_CREATED_BY => gv_apps_user_id,
  X_LAST_UPDATE_DATE => sysdate,
  X_LAST_UPDATED_BY => gv_apps_user_id,
  X_LAST_UPDATE_LOGIN => gv_login_id);

Line: 1650


         P_LAST_UPDATE_LOGIN      => gv_login_id,
         P_PROGRAM_ID             => gv_pgm_id,
         P_PROGRAM_LOGIN_ID       => gv_login_id,
         P_PROGRAM_APPLICATION_ID => gv_pgm_app_id,
         P_EXEC_MODE_CODE         => p_execution_mode,
         P_DIMENSION_ID           => null,
         P_TABLE_NAME             => null,
         P_HIERARCHY_NAME         => null,
         X_MSG_COUNT              => v_msg_count,
         X_MSG_DATA               => v_msg_data,
         X_RETURN_STATUS          => v_API_return_status);

Line: 1692


         P_LAST_UPDATE_LOGIN         => gv_login_id,
         P_EXEC_MODE_CODE            => p_execution_mode,
         X_EXEC_STATE                => v_exec_state,
         X_PREV_REQUEST_ID           => v_previous_request_id,
         X_MSG_COUNT                 => v_msg_count,
         X_MSG_DATA                  => v_msg_data,
         X_RETURN_STATUS             => v_API_return_status);

Line: 1712


         P_LAST_UPDATE_LOGIN         => gv_login_id,
         X_MSG_COUNT                 => v_msg_count,
         X_MSG_DATA                  => v_msg_data,
         X_RETURN_STATUS             => v_API_return_status);

Line: 1805


l_delete_b_sql        VARCHAR2(200);

Line: 1806


l_delete_tl_sql       VARCHAR2(200);

Line: 1807


l_delete_attr_sql     VARCHAR2(200);

Line: 1808


l_delete_dg_b_sql     VARCHAR2(200);

Line: 1809


l_delete_dg_tl_sql    VARCHAR2(200);

Line: 1840


l_delete_b_sql := 'DELETE FROM '||gv_dim_props_rec.INTF_MEMBER_B_TABLE_NAME||
                  ' WHERE CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';

Line: 1843


l_delete_tl_sql := 'DELETE FROM '||gv_dim_props_rec.INTF_MEMBER_TL_TABLE_NAME||
                   ' WHERE CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';

Line: 1846


l_delete_attr_sql := 'DELETE FROM '||gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME||
                     ' WHERE CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';

Line: 1849


l_delete_dg_b_sql := 'DELETE FROM FEM_DIMENSION_GRPS_B_T'||
                     ' WHERE DIMENSION_VARCHAR_LABEL = :dim_varchar_lbl AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';

Line: 1852


l_delete_dg_tl_sql := 'DELETE FROM FEM_DIMENSION_GRPS_TL_T'||
                     ' WHERE DIMENSION_VARCHAR_LABEL = :dim_varchar_lbl AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';

Line: 1859


    p_module => c_block||'.l_delete_b_sql',
    p_msg_text => l_delete_b_sql);

Line: 1862


  EXECUTE IMMEDIATE l_delete_b_sql;

Line: 1869


    p_module => c_block||'.l_delete_tl_sql',
    p_msg_text => l_delete_tl_sql);

Line: 1872


  EXECUTE IMMEDIATE l_delete_tl_sql;

Line: 1879


    p_module => c_block||'.l_delete_attr_sql',
    p_msg_text => l_delete_attr_sql);

Line: 1882


  EXECUTE IMMEDIATE l_delete_attr_sql;

Line: 1888


    p_module => c_block||'.l_delete_dg_b_sql',
    p_msg_text => l_delete_dg_b_sql);

Line: 1891


EXECUTE IMMEDIATE l_delete_dg_b_sql USING p_dim_varchar_lbl;

Line: 1896


    p_module => c_block||'.l_delete_dg_tl_sql',
    p_msg_text => l_delete_dg_tl_sql);

Line: 1899


EXECUTE IMMEDIATE l_delete_dg_tl_sql USING p_dim_varchar_lbl;

Line: 2142


l_insert_b_sql VARCHAR2(32767);

Line: 2143


l_insert_tl_sql VARCHAR2(32767);

Line: 2144


l_insert_attr_sql  VARCHAR2(32767);

Line: 2146


l_insert_dim_grp_b_sql VARCHAR2(32767);

Line: 2147


l_insert_dim_grp_tl_sql VARCHAR2(32767);

Line: 2163


l_insert_b_sql := get_insert_b_sql(p_source_db_link, p_dim_varchar_lbl);

Line: 2167


  p_module => c_block||'.l_insert_b_sql',
  p_msg_text => l_insert_b_sql);

Line: 2171


l_insert_tl_sql := get_insert_tl_sql(p_source_db_link, p_dim_varchar_lbl);

Line: 2175


  p_module => c_block||'.l_insert_tl_sql',
  p_msg_text => l_insert_tl_sql);

Line: 2178


l_insert_attr_sql := get_dim_attr_sql(p_version_mode, p_source_db_link, p_dim_varchar_lbl);

Line: 2182


  p_module => c_block||'.l_insert_attr_sql',
  p_msg_text => l_insert_attr_sql);

Line: 2185


l_insert_dim_grp_b_sql := get_insert_dim_grp_b_sql(p_source_db_link);

Line: 2189


  p_module => c_block||'.l_insert_dim_grp_b_sql',
  p_msg_text => l_insert_dim_grp_b_sql);

Line: 2192


l_insert_dim_grp_tl_sql := get_insert_dim_grp_tl_sql(p_source_db_link);

Line: 2196


  p_module => c_block||'.l_insert_dim_grp_tl_sql',
  p_msg_text => l_insert_dim_grp_tl_sql);

Line: 2200


insert_value_sets(p_source_db_link);

Line: 2204


  insert_calendars(p_source_db_link);

Line: 2211


EXECUTE IMMEDIATE l_insert_dim_grp_b_sql USING gv_src_dim_props_rec.DIMENSION_ID;

Line: 2214


EXECUTE IMMEDIATE l_insert_dim_grp_tl_sql USING gv_src_dim_props_rec.DIMENSION_ID;

Line: 2230


          P_ENG_SQL => l_insert_b_sql,
          P_TABLE_ALIAS => 'B',
          P_RUN_NAME => 'PROCESS MEMBERS B',
          P_ENG_PRG => 'FEM_DIM_MEMBER_MIGRATION_PKG.PROCESS_MEMBERS',
          P_CONDITION => NULL,
          P_FAILED_REQ_ID => NULL
          ,P_SOURCE_DB_LINK => p_source_db_link
         );

Line: 2244


              RAISE e_insert_b_exception;

Line: 2258


          P_ENG_SQL => l_insert_tl_sql,
          P_TABLE_ALIAS => 'TL',
          P_RUN_NAME => 'PROCESS MEMBERS TL',
          P_ENG_PRG => 'FEM_DIM_MEMBER_MIGRATION_PKG.PROCESS_MEMBERS',
          P_CONDITION => NULL,
          P_FAILED_REQ_ID => NULL,
          P_SOURCE_DB_LINK => p_source_db_link
         );

Line: 2271


              RAISE e_insert_tl_exception;

Line: 2285


          P_ENG_SQL => l_insert_attr_sql,
          P_TABLE_ALIAS => 'ATTR',
          P_RUN_NAME => 'PROCESS MEMBERS ATTR',
          P_ENG_PRG => 'FEM_DIM_MEMBER_MIGRATION_PKG.PROCESS_MEMBERS',
          P_CONDITION => NULL,
          P_FAILED_REQ_ID => NULL,
          P_SOURCE_DB_LINK => p_source_db_link
         );

Line: 2298


              RAISE e_insert_attr_exception;

Line: 2302


    update_calp_attributes(p_source_db_link);

Line: 2311


  WHEN e_insert_b_exception THEN
        FEM_ENGINES_PKG.TECH_MESSAGE
          (p_severity => c_log_level_5
          ,p_module => c_block||'.'||c_proc_name||'.Exception'
          ,p_app_name => c_fem
          ,p_msg_name => G_INSERT_ERROR
          ,P_TOKEN1 => 'TABLE_NAME'
          ,P_VALUE1 => gv_dim_props_rec.INTF_MEMBER_B_TABLE_NAME);

Line: 2322


          ,p_msg_name => G_INSERT_ERROR
          ,P_TOKEN1 => 'TABLE_NAME'
          ,P_VALUE1 => gv_dim_props_rec.INTF_MEMBER_B_TABLE_NAME);

Line: 2328


  WHEN e_insert_tl_exception THEN
        FEM_ENGINES_PKG.TECH_MESSAGE
          (p_severity => c_log_level_5
          ,p_module => c_block||'.'||c_proc_name||'.Exception'
          ,p_app_name => c_fem
          ,p_msg_name => G_INSERT_ERROR
          ,P_TOKEN1 => 'TABLE_NAME'
          ,P_VALUE1 => gv_dim_props_rec.INTF_MEMBER_TL_TABLE_NAME);

Line: 2339


          ,p_msg_name => G_INSERT_ERROR
          ,P_TOKEN1 => 'TABLE_NAME'
          ,P_VALUE1 => gv_dim_props_rec.INTF_MEMBER_TL_TABLE_NAME);

Line: 2344


  WHEN e_insert_attr_exception THEN
        FEM_ENGINES_PKG.TECH_MESSAGE
          (p_severity => c_log_level_5
          ,p_module => c_block||'.'||c_proc_name||'.Exception'
          ,p_app_name => c_fem
          ,p_msg_name => G_INSERT_ERROR
          ,P_TOKEN1 => 'TABLE_NAME'
          ,P_VALUE1 => gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME);

Line: 2355


          ,p_msg_name => G_INSERT_ERROR
          ,P_TOKEN1 => 'TABLE_NAME'
          ,P_VALUE1 => gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME);

Line: 2390


l_delete_b_sql     VARCHAR2(200);

Line: 2391


l_delete_tl_sql    VARCHAR2(200);

Line: 2392


l_delete_attr_sql  VARCHAR2(200);

Line: 2393


l_delete_dg_b_sql     VARCHAR2(200);

Line: 2394


l_delete_dg_tl_sql    VARCHAR2(200);

Line: 2396


l_delete_hier_rule_sql VARCHAR2(200);

Line: 2397


l_delete_hier_sql VARCHAR2(200);

Line: 2398


l_delete_hier_vs_sql VARCHAR2(200);

Line: 2399


l_delete_hier_dg_sql VARCHAR2(200);

Line: 2403


l_hier_rule_sql VARCHAR2(1000):= 'SELECT A.OBJECT_ID, B.FOLDER_NAME'||
' FROM FEM_OBJECT_CATALOG_VL@'||p_source_db_link||' A,'||
' FEM_FOLDERS_VL@'||p_source_db_link||' B'||
' WHERE A.OBJECT_TYPE_CODE = ''HIERARCHY'''||
' AND A.FOLDER_ID = B.FOLDER_ID'||
' AND A.OBJECT_NAME = :hier_obj_name';

Line: 2453


l_delete_hier_rule_sql := 'DELETE FROM FEM_HIERARCHIES_T'||
                  ' WHERE HIERARCHY_OBJECT_NAME = :hier_obj_name AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';

Line: 2456


l_delete_hier_sql := 'DELETE FROM '||gv_dim_props_rec.HIERARCHY_INTF_TABLE_NAME||
                   ' WHERE HIERARCHY_OBJECT_NAME = :hier_obj_name AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';

Line: 2459


l_delete_hier_vs_sql := 'DELETE FROM FEM_HIER_VALUE_SETS_T'||
                     ' WHERE HIERARCHY_OBJECT_NAME = :hier_obj_name AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';

Line: 2462


l_delete_hier_dg_sql := 'DELETE FROM FEM_HIER_DIM_GRPS_T'||
                     ' WHERE HIERARCHY_OBJECT_NAME = :hier_obj_name AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';

Line: 2465


EXECUTE IMMEDIATE l_delete_hier_rule_sql USING p_hier_obj_name;

Line: 2466


EXECUTE IMMEDIATE l_delete_hier_sql USING p_hier_obj_name;

Line: 2467


EXECUTE IMMEDIATE l_delete_hier_vs_sql USING p_hier_obj_name;

Line: 2468


EXECUTE IMMEDIATE l_delete_hier_dg_sql USING p_hier_obj_name;

Line: 2661


l_insert_hier_sql  VARCHAR2(32767);

Line: 2662


l_insert_hier_rule_sql  VARCHAR2(32767);

Line: 2663


l_insert_hier_vs_sql VARCHAR2(32767);

Line: 2664


l_insert_hier_dg_sql VARCHAR2(32767);

Line: 2675


l_hier_rule_sql VARCHAR2(1000):= 'SELECT A.OBJECT_ID'||
' FROM FEM_OBJECT_CATALOG_VL@'||p_source_db_link||' A'||
' WHERE A.OBJECT_TYPE_CODE = ''HIERARCHY'''||
' AND A.OBJECT_NAME = :hier_obj_name';

Line: 2680


l_hier_version_sql VARCHAR2(1000):= 'SELECT B.OBJECT_DEFINITION_ID,'||
' B.DISPLAY_NAME,'||
' B.EFFECTIVE_START_DATE,'||
' B.EFFECTIVE_END_DATE'||
' FROM FEM_OBJECT_DEFINITION_VL@'||p_source_db_link||' B'||
' WHERE B.OBJECT_ID = :hier_obj_id'||'{{version_where}}';

Line: 2695


SELECT OBJECT_ID
INTO l_target_obj_id
FROM FEM_OBJECT_CATALOG_VL
WHERE OBJECT_NAME = p_hier_obj_name;

Line: 2760


l_insert_hier_rule_sql := get_dim_hier_rule_sql(p_dim_varchar_lbl,
                                                p_folder_name,
                                                p_hier_obj_name,
                                                p_hier_obj_def_name,
                                                p_source_db_link,
                                                l_eff_start_date,
                                                l_eff_end_date);

Line: 2769


l_insert_hier_sql := get_dim_hier_sql(p_hier_obj_name,
                                      p_hier_obj_def_name,
                                      l_hier_obj_def_id,
                                      p_dim_varchar_lbl,
                                      p_source_db_link);

Line: 2780


    p_module => c_block||'.l_insert_hier_rule_sql',
    p_msg_text => l_insert_hier_rule_sql);

Line: 2783


EXECUTE IMMEDIATE l_insert_hier_rule_sql USING p_hier_obj_id;

Line: 2794


          P_ENG_SQL => l_insert_hier_sql,
          P_TABLE_ALIAS => 'HIER',
          P_RUN_NAME => 'PROCESS MEMBERS HIER',
          P_ENG_PRG => 'FEM_DIM_MEMBER_MIGRATION_PKG.PROCESS_MEMBERS',
          P_CONDITION => NULL,
          P_FAILED_REQ_ID => NULL,
          P_SOURCE_DB_LINK => p_source_db_link);

Line: 2839


l_insert_hier_rule_sql := get_dim_hier_rule_sql(p_dim_varchar_lbl,
                                                p_folder_name,
                                                p_hier_obj_name,
                                                l_hier_obj_def_name,
                                                p_source_db_link,
                                                l_eff_start_date,
                                                l_eff_end_date);

Line: 2848


l_insert_hier_sql := get_dim_hier_sql(p_hier_obj_name,
                                      l_hier_obj_def_name,
                                      l_hier_obj_def_id,
                                      p_dim_varchar_lbl,
                                      p_source_db_link);

Line: 2858


    p_module => c_block||'.l_insert_hier_rule_sql',
    p_msg_text => l_insert_hier_rule_sql);

Line: 2861


EXECUTE IMMEDIATE l_insert_hier_rule_sql USING p_hier_obj_id;

Line: 2868


    p_module => c_block||'.l_insert_hier_sql',
    p_msg_text => l_insert_hier_sql);

Line: 2878


          P_ENG_SQL => l_insert_hier_sql,
          P_TABLE_ALIAS => 'HIER',
          P_RUN_NAME => 'PROCESS MEMBERS HIER',
          P_ENG_PRG => 'FEM_DIM_MEMBER_MIGRATION_PKG.PROCESS_MEMBERS',
          P_CONDITION => NULL,
          P_FAILED_REQ_ID => NULL
          ,P_SOURCE_DB_LINK => p_source_db_link
         );

Line: 2891


              RAISE e_insert_hier_exception;

Line: 2900


l_insert_hier_vs_sql := get_hier_vs_sql(p_hier_obj_name,
                                        p_source_db_link);

Line: 2904


    p_module => c_block||'.l_insert_hier_vs_sql',
    p_msg_text => l_insert_hier_vs_sql);

Line: 2907


l_insert_hier_dg_sql := get_hier_dg_sql(p_hier_obj_name, p_source_db_link);

Line: 2912


    p_module => c_block||'.l_insert_hier_dg_sql',
    p_msg_text => l_insert_hier_dg_sql);

Line: 2915


EXECUTE IMMEDIATE l_insert_hier_vs_sql USING p_hier_obj_id;

Line: 2918


EXECUTE IMMEDIATE l_insert_hier_dg_sql USING p_hier_obj_id;

Line: 2943


WHEN e_insert_hier_exception THEN

        FEM_ENGINES_PKG.TECH_MESSAGE
          (p_severity => c_log_level_5
          ,p_module => c_block||'.'||c_proc_name||'.Exception'
          ,p_app_name => c_fem
          ,p_msg_name => G_INSERT_ERROR
          ,P_TOKEN1 => 'TABLE_NAME'
          ,P_VALUE1 => gv_dim_props_rec.HIERARCHY_INTF_TABLE_NAME);

Line: 2955


          ,p_msg_name => G_INSERT_ERROR
          ,P_TOKEN1 => 'TABLE_NAME'
          ,P_VALUE1 => gv_dim_props_rec.HIERARCHY_INTF_TABLE_NAME);

Line: 3037


   /*FEM_PL_PKG.Update_Obj_Exec_Errors(
     P_API_VERSION               => c_api_version,
     P_COMMIT                    => c_true,
     P_REQUEST_ID                => gv_request_id,
     P_OBJECT_ID                 => gv_dim_props_rec.MIGRATION_OBJ_ID,
     P_USER_ID                   => gv_apps_user_id,
     P_LAST_UPDATE_LOGIN         => null,
     X_MSG_COUNT                 => v_msg_count,
     X_MSG_DATA                  => v_msg_data,
     X_RETURN_STATUS             => v_API_return_status);

Line: 3055


   FEM_PL_PKG.Update_Obj_Exec_Status(
     P_API_VERSION               => c_api_version,
     P_COMMIT                    => c_true,
     P_REQUEST_ID                => gv_request_id,
     P_OBJECT_ID                 => gv_dim_props_rec.MIGRATION_OBJ_ID,
     P_EXEC_STATUS_CODE          => p_execution_status,
     P_USER_ID                   => gv_apps_user_id,
     P_LAST_UPDATE_LOGIN         => null,
     X_MSG_COUNT                 => v_msg_count,
     X_MSG_DATA                  => v_msg_data,
     X_RETURN_STATUS             => v_API_return_status);

Line: 3074


   FEM_PL_PKG.Update_Request_Status(
     P_API_VERSION               => c_api_version,
     P_COMMIT                    => c_true,
     P_REQUEST_ID                => gv_request_id,
     P_EXEC_STATUS_CODE          => p_execution_status,
     P_USER_ID                   => gv_apps_user_id,
     P_LAST_UPDATE_LOGIN         => null,
     X_MSG_COUNT                 => v_msg_count,
     X_MSG_DATA                  => v_msg_data,
     X_RETURN_STATUS             => v_API_return_status);

Line: 3270


   /*FEM_PL_PKG.Update_Obj_Exec_Errors(
     P_API_VERSION               => c_api_version,
     P_COMMIT                    => c_true,
     P_REQUEST_ID                => gv_request_id,
     P_OBJECT_ID                 => gv_dim_props_rec.MIGRATION_OBJ_ID,
     P_USER_ID                   => gv_apps_user_id,
     P_LAST_UPDATE_LOGIN         => null,
     X_MSG_COUNT                 => v_msg_count,
     X_MSG_DATA                  => v_msg_data,
     X_RETURN_STATUS             => v_API_return_status);

Line: 3288


   FEM_PL_PKG.Update_Obj_Exec_Status(
     P_API_VERSION               => c_api_version,
     P_COMMIT                    => c_true,
     P_REQUEST_ID                => gv_request_id,
     P_OBJECT_ID                 => gv_dim_props_rec.MIGRATION_OBJ_ID,
     P_EXEC_STATUS_CODE          => p_execution_status,
     P_USER_ID                   => gv_apps_user_id,
     P_LAST_UPDATE_LOGIN         => null,
     X_MSG_COUNT                 => v_msg_count,
     X_MSG_DATA                  => v_msg_data,
     X_RETURN_STATUS             => v_API_return_status);

Line: 3307


   FEM_PL_PKG.Update_Request_Status(
     P_API_VERSION               => c_api_version,
     P_COMMIT                    => c_true,
     P_REQUEST_ID                => gv_request_id,
     P_EXEC_STATUS_CODE          => p_execution_status,
     P_USER_ID                   => gv_apps_user_id,
     P_LAST_UPDATE_LOGIN         => null,
     X_MSG_COUNT                 => v_msg_count,
     X_MSG_DATA                  => v_msg_data,
     X_RETURN_STATUS             => v_API_return_status);