APPS.FEM_WEBADI_MEMBER_UTILS_PVT SQL Statements

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

Line: 436

    INSERT INTO BNE_INTERFACE_COLS_B (
      INTERFACE_COL_TYPE,
      INTERFACE_COL_NAME,
      ENABLED_FLAG,
      REQUIRED_FLAG,
      DISPLAY_FLAG,
      READ_ONLY_FLAG,
      NOT_NULL_FLAG,
      SUMMARY_FLAG,
      MAPPING_ENABLED_FLAG,
      DATA_TYPE,
      FIELD_SIZE,
      DEFAULT_TYPE,
      DEFAULT_VALUE,
      SEGMENT_NUMBER,
      GROUP_NAME,
      OA_FLEX_CODE,
      OA_CONCAT_FLEX,
      VAL_TYPE,
      VAL_ID_COL,
      VAL_MEAN_COL,
      VAL_DESC_COL,
      VAL_OBJ_NAME,
      VAL_ADDL_W_C,
      VAL_COMPONENT_APP_ID,
      VAL_COMPONENT_CODE,
      OA_FLEX_NUM,
      OA_FLEX_APPLICATION_ID,
      DISPLAY_ORDER,
      UPLOAD_PARAM_LIST_ITEM_NUM,
      EXPANDED_SQL_QUERY,
      APPLICATION_ID,
      INTERFACE_CODE,
      OBJECT_VERSION_NUMBER,
      SEQUENCE_NUM,
      LOV_TYPE,
      OFFLINE_LOV_ENABLED_FLAG,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN,
      VARIABLE_DATA_TYPE_CLASS
    )
    VALUES
    ( l_interface_cols_tbl(i).INTERFACE_COL_TYPE,
      l_interface_cols_tbl(i).INTERFACE_COL_NAME,
      'Y',
      'N',
      l_interface_cols_tbl(i).DISPLAY_FLAG,
      l_interface_cols_tbl(i).READ_ONLY_FLAG,
      'Y',
      'N',
      'N',
      l_interface_cols_tbl(i).DATA_TYPE,
      l_interface_cols_tbl(i).FIELD_SIZE,
      NULL,
      NULL,
      l_interface_cols_tbl(i).SEGMENT_VALUE,
      l_interface_cols_tbl(i).GROUP_NAME,
      NULL,
      NULL,
      l_interface_cols_tbl(i).VAL_TYPE,
      l_interface_cols_tbl(i).VAL_ID_COL,
      l_interface_cols_tbl(i).VAL_MEAN_COL,
      l_interface_cols_tbl(i).VAL_DESC_COL,
      l_interface_cols_tbl(i).VAL_OBJ_NAME,
      l_interface_cols_tbl(i).VAL_ADDL_W_C,
      l_interface_cols_tbl(i).VAL_COMPONENT_APP_ID,
      l_interface_cols_tbl(i).VAL_COMPONENT_CODE,
      NULL,
      NULL,
      l_interface_cols_tbl(i).DISPLAY_ORDER,
      l_interface_cols_tbl(i).UPLOAD_PARAM_LIST_ITEM_NUM,
      NULL,
      274,
      p_interface_code,
      1,
      l_interface_cols_tbl(i).SEQUENCE_NUM,
      l_interface_cols_tbl(i).LOV_TYPE,
      l_interface_cols_tbl(i).OFFLINE_LOV_ENABLED_FLAG,
      SYSDATE,
      l_user_id,
      SYSDATE,
      l_user_id,
      l_login_id,
      NULL
    );

Line: 525

    INSERT INTO BNE_INTERFACE_COLS_TL (
      CREATED_BY,
      CREATION_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN,
      LAST_UPDATE_DATE,
      USER_HINT,
      PROMPT_LEFT,
      USER_HELP_TEXT,
      PROMPT_ABOVE,
      INTERFACE_CODE,
      SEQUENCE_NUM,
      APPLICATION_ID,
      LANGUAGE,
      SOURCE_LANG
    )
    SELECT l_user_id
    ,      SYSDATE
    ,      l_user_id
    ,      l_login_id
    ,      SYSDATE
    ,      M2.MESSAGE_TEXT
    ,      M1.MESSAGE_TEXT
    ,      NULL
    ,      M1.MESSAGE_TEXT
    ,      p_interface_code
    ,      l_interface_cols_tbl(i).SEQUENCE_NUM
    ,      274
    ,      L.LANGUAGE_CODE
    ,      USERENV('LANG')
    FROM   FND_NEW_MESSAGES M1,
           FND_NEW_MESSAGES M2,
           FND_LANGUAGES L
    WHERE  L.INSTALLED_FLAG IN ('I', 'B')
    AND    M1.MESSAGE_NAME (+)= l_interface_cols_tbl(i).FND_MESSAGE_NAME
    AND    M1.LANGUAGE_CODE (+)= L.LANGUAGE_CODE
    AND    M2.MESSAGE_NAME (+)= l_interface_cols_tbl(i).USER_HINT_FND_MESSAGE_NAME
    AND    M2.LANGUAGE_CODE (+)= L.LANGUAGE_CODE;

Line: 613

  x_updated_flag                 OUT NOCOPY   VARCHAR2
)
IS
  --
  l_api_name    CONSTANT         VARCHAR2(30) := 'Populate_Dim_Intf_Attr_Cols';

Line: 625

  l_updated_flag                 VARCHAR2(1)   := 'N';

Line: 717

  x_updated_flag := 'N';

Line: 727

    IF (x_updated_flag = 'N') THEN
      x_updated_flag := 'Y';

Line: 735

    SELECT I.SEQUENCE_NUM
    ,      I.INTERFACE_COL_NAME
    FROM   BNE_LAYOUT_COLS L
    ,      BNE_INTERFACE_COLS_B I
    WHERE  L.APPLICATION_ID = 274
    AND    L.INTERFACE_APP_ID = 274
    AND    L.INTERFACE_CODE = p_interface_code
    AND    I.APPLICATION_ID = L.INTERFACE_APP_ID
    AND    I.INTERFACE_CODE = L.INTERFACE_CODE
    AND    I.SEQUENCE_NUM = L.INTERFACE_SEQ_NUM
    AND    I.INTERFACE_COL_NAME LIKE 'P_ATTRIBUTE%'
    AND    NOT EXISTS
    (
      SELECT 1
      FROM   FEM_WEBADI_DIM_ATTR_MAPS M
      WHERE  M.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
      AND    M.INTERFACE_COL = I.INTERFACE_COL_NAME
    )
  )
  LOOP

    IF (x_updated_flag = 'N') THEN
      x_updated_flag := 'Y';

Line: 760

    UPDATE BNE_INTERFACE_COLS_B
    SET    VAL_TYPE = NULL
    ,      VAL_OBJ_NAME = NULL
    ,      VAL_COMPONENT_APP_ID = NULL
    ,      VAL_COMPONENT_CODE = NULL
    ,      LOV_TYPE = NULL
    ,      OFFLINE_LOV_ENABLED_FLAG = NULL
    ,      SEGMENT_NUMBER = NULL
    ,      GROUP_NAME = NULL
    ,      VARIABLE_DATA_TYPE_CLASS = NULL
    WHERE  APPLICATION_ID = 274
    AND    INTERFACE_CODE = p_interface_code
    AND    SEQUENCE_NUM = del_rec.SEQUENCE_NUM
    AND    INTERFACE_COL_NAME IS NOT NULL;

Line: 779

    UPDATE BNE_INTERFACE_COLS_TL
    SET    USER_HINT = NULL
    ,      PROMPT_LEFT = del_rec.INTERFACE_COL_NAME
    ,      USER_HELP_TEXT = NULL
    ,      PROMPT_ABOVE = del_rec.INTERFACE_COL_NAME
    WHERE  APPLICATION_ID = 274
    AND    INTERFACE_CODE = p_interface_code
    AND    SEQUENCE_NUM = del_rec.SEQUENCE_NUM;

Line: 798

    IF (x_updated_flag = 'N') THEN
      x_updated_flag := 'Y';

Line: 805

      SELECT REF.column_value AS INTERFACE_COL
      ,      DATA_TYPE
      ,      MAP.ATTRIBUTE_VARCHAR_LABEL
      ,      ATTRIBUTE_REQUIRED_FLAG
      FROM  TABLE(CAST(g_changed_dt_intf_col_tbl AS FND_TABLE_OF_VARCHAR2_30)) REF
      ,     FEM_WEBADI_DIM_ATTR_MAPS MAP
      ,     FEM_DIM_ATTRIBUTES_B A
      WHERE MAP.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
      AND   MAP.INTERFACE_COL = REF.column_value
      AND   A.DIMENSION_ID = p_dimension_id
      AND   A.ATTRIBUTE_VARCHAR_LABEL = MAP.ATTRIBUTE_VARCHAR_LABEL
    )
    LOOP

      l_sequence_num := NULL;

Line: 823

        SELECT SEQUENCE_NUM
        ,      VAL_COMPONENT_CODE
        FROM   BNE_INTERFACE_COLS_B
        WHERE  APPLICATION_ID = 274
        AND    INTERFACE_CODE = p_interface_code
        AND    INTERFACE_COL_NAME = dt_changed_rec.INTERFACE_COL
      )
      LOOP
        l_sequence_num := intf_rec.SEQUENCE_NUM;

Line: 838

      DELETE BNE_INTERFACE_COLS_TL
      WHERE  APPLICATION_ID = 274
      AND    INTERFACE_CODE = p_interface_code
      AND    SEQUENCE_NUM = l_sequence_num;

Line: 843

      INSERT INTO BNE_INTERFACE_COLS_TL
      (
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_LOGIN,
        LAST_UPDATE_DATE,
        USER_HINT,
        PROMPT_LEFT,
        USER_HELP_TEXT,
        PROMPT_ABOVE,
        INTERFACE_CODE,
        SEQUENCE_NUM,
        APPLICATION_ID,
        LANGUAGE,
        SOURCE_LANG
      )
      SELECT l_user_id
      ,      SYSDATE
      ,      l_user_id
      ,      l_login_id
      ,      SYSDATE
      ,      M.MESSAGE_TEXT
      ,      B.ATTRIBUTE_NAME
      ,      NULL
      ,      B.ATTRIBUTE_NAME
      ,      p_interface_code
      ,      l_sequence_num
      ,      274
      ,      L.LANGUAGE_CODE
      ,      B.SOURCE_LANG
      FROM   FEM_XDIM_DIMENSIONS_VL D
      ,      FEM_DIM_ATTRIBUTES_B A
      ,      FEM_DIM_ATTRIBUTES_TL B
      ,      FND_NEW_MESSAGES M
      ,      FND_LANGUAGES L
      WHERE  L.INSTALLED_FLAG IN ('I', 'B')
      AND    D.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
      AND    D.DIMENSION_ID = A.DIMENSION_ID
      AND    A.ATTRIBUTE_VARCHAR_LABEL = dt_changed_rec.ATTRIBUTE_VARCHAR_LABEL
      AND    A.ATTRIBUTE_ID = B.ATTRIBUTE_ID
      AND    B.LANGUAGE (+) = L.LANGUAGE_CODE
      AND    M.MESSAGE_NAME (+)=
             DECODE(dt_changed_rec.DATA_TYPE, 'DIMENSION',
               DECODE(dt_changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_LOV_REQ', 'FEM_ADI_USER_HINT_LOV'),
             DECODE(dt_changed_rec.DATA_TYPE, 'VARCHAR2',
               DECODE(dt_changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_TEXT_REQ', 'FEM_ADI_USER_HINT_TEXT'),
             DECODE(dt_changed_rec.DATA_TYPE, 'NUMBER',
               DECODE(dt_changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_NUMBER_REQ', 'FEM_ADI_USER_HINT_NUMBER'),
             DECODE(dt_changed_rec.DATA_TYPE, 'DATE',
               DECODE(dt_changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_DATE_REQ', 'FEM_ADI_USER_HINT_DATE'), NULL))))
      AND    M.LANGUAGE_CODE (+)= L.LANGUAGE_CODE;

Line: 898

        UPDATE BNE_INTERFACE_COLS_B
        SET    VAL_TYPE = 'JAVA'
        ,      VAL_OBJ_NAME = 'oracle.apps.fem.integrator.dimension.validators.FemAttributeValidator'
        ,      VAL_COMPONENT_APP_ID = 274
        ,      VAL_COMPONENT_CODE = 'FEM_ATTRIBUTE'
        ,      LOV_TYPE = 'STANDARD'
        ,      OFFLINE_LOV_ENABLED_FLAG = 'N'
        ,      VARIABLE_DATA_TYPE_CLASS = NULL
        WHERE  APPLICATION_ID = 274
        AND    INTERFACE_CODE = p_interface_code
        AND    INTERFACE_COL_NAME = dt_changed_rec.INTERFACE_COL;

Line: 915

        UPDATE BNE_INTERFACE_COLS_B
        SET    VAL_TYPE = NULL
        ,      VAL_OBJ_NAME = NULL
        ,      VAL_COMPONENT_APP_ID = NULL
        ,      VAL_COMPONENT_CODE = NULL
        ,      LOV_TYPE = NULL
        ,      OFFLINE_LOV_ENABLED_FLAG = NULL
        ,      VARIABLE_DATA_TYPE_CLASS =
               DECODE(dt_changed_rec.DATA_TYPE, 'DATE',
               'oracle.apps.fem.integrator.dimension.validators.FemAttributeDateTypeValidator',
               DECODE(dt_changed_rec.DATA_TYPE, 'NUMBER',
               'oracle.apps.fem.integrator.dimension.validators.FemAttributeNumericTypeValidator', NULL))
        WHERE  APPLICATION_ID = 274
        AND    INTERFACE_CODE = p_interface_code
        AND    INTERFACE_COL_NAME = dt_changed_rec.INTERFACE_COL;

Line: 951

  SELECT MAX(SEQUENCE_NUM)
  INTO   l_max_sequence_num
  FROM   BNE_INTERFACE_COLS_B
  WHERE  APPLICATION_ID = 274
  AND    INTERFACE_CODE = p_interface_code;

Line: 959

    SELECT MAX(SEQUENCE_NUM)
    INTO   l_max_sequence_num
    FROM   BNE_INTERFACE_COLS_B
    WHERE  APPLICATION_ID = 274
    AND    INTERFACE_CODE = p_interface_code;

Line: 965

    IF (x_updated_flag = 'N') THEN
      x_updated_flag := 'Y';

Line: 971

      SELECT REF.column_value AS INTERFACE_COL
      ,      DATA_TYPE
      ,      MAP.ATTRIBUTE_VARCHAR_LABEL
      ,      ATTRIBUTE_REQUIRED_FLAG
      FROM  TABLE(CAST(g_changed_intf_col_tbl AS FND_TABLE_OF_VARCHAR2_30)) REF
      ,     FEM_WEBADI_DIM_ATTR_MAPS MAP
      ,     FEM_DIM_ATTRIBUTES_B A
      WHERE MAP.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
      AND   MAP.INTERFACE_COL = REF.column_value
      AND   A.DIMENSION_ID = p_dimension_id
      AND   A.ATTRIBUTE_VARCHAR_LABEL = MAP.ATTRIBUTE_VARCHAR_LABEL
    )
    LOOP
      l_sequence_num := NULL;

Line: 988

        SELECT SEQUENCE_NUM
        ,      VAL_COMPONENT_CODE
        FROM   BNE_INTERFACE_COLS_B
        WHERE  APPLICATION_ID = 274
        AND    INTERFACE_CODE = p_interface_code
        AND    INTERFACE_COL_NAME = changed_rec.INTERFACE_COL
      )
      LOOP
        l_sequence_num := intf_rec.SEQUENCE_NUM;

Line: 1005

        DELETE BNE_INTERFACE_COLS_TL
        WHERE  APPLICATION_ID = 274
        AND    INTERFACE_CODE = p_interface_code
        AND    SEQUENCE_NUM = l_sequence_num;

Line: 1010

        INSERT INTO BNE_INTERFACE_COLS_TL
        (
          CREATED_BY,
          CREATION_DATE,
          LAST_UPDATED_BY,
          LAST_UPDATE_LOGIN,
          LAST_UPDATE_DATE,
          USER_HINT,
          PROMPT_LEFT,
          USER_HELP_TEXT,
          PROMPT_ABOVE,
          INTERFACE_CODE,
          SEQUENCE_NUM,
          APPLICATION_ID,
          LANGUAGE,
          SOURCE_LANG
        )
        SELECT l_user_id
        ,      SYSDATE
        ,      l_user_id
        ,      l_login_id
        ,      SYSDATE
        ,      M.MESSAGE_TEXT
        ,      B.ATTRIBUTE_NAME
        ,      NULL
        ,      B.ATTRIBUTE_NAME
        ,      p_interface_code
        ,      l_sequence_num
        ,      274
        ,      L.LANGUAGE_CODE
        ,      B.SOURCE_LANG
        FROM   FEM_XDIM_DIMENSIONS_VL D
        ,      FEM_DIM_ATTRIBUTES_B A
        ,      FEM_DIM_ATTRIBUTES_TL B
        ,      FND_NEW_MESSAGES M
        ,      FND_LANGUAGES L
        WHERE  L.INSTALLED_FLAG IN ('I', 'B')
        AND    D.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
        AND    D.DIMENSION_ID = A.DIMENSION_ID
        AND    A.ATTRIBUTE_VARCHAR_LABEL = changed_rec.ATTRIBUTE_VARCHAR_LABEL
        AND    A.ATTRIBUTE_ID = B.ATTRIBUTE_ID
        AND    B.LANGUAGE (+) = L.LANGUAGE_CODE
        AND    M.MESSAGE_NAME (+)=
               DECODE(changed_rec.DATA_TYPE, 'DIMENSION',
                 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_LOV_REQ', 'FEM_ADI_USER_HINT_LOV'),
               DECODE(changed_rec.DATA_TYPE, 'VARCHAR2',
                 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_TEXT_REQ', 'FEM_ADI_USER_HINT_TEXT'),
               DECODE(changed_rec.DATA_TYPE, 'NUMBER',
                 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_NUMBER_REQ', 'FEM_ADI_USER_HINT_NUMBER'),
               DECODE(changed_rec.DATA_TYPE, 'DATE',
                 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_DATE_REQ', 'FEM_ADI_USER_HINT_DATE'), NULL))))
        AND    M.LANGUAGE_CODE (+)= L.LANGUAGE_CODE;

Line: 1071

          UPDATE BNE_INTERFACE_COLS_B
          SET    VAL_TYPE = 'JAVA'
          ,      VAL_OBJ_NAME = 'oracle.apps.fem.integrator.dimension.validators.FemAttributeValidator'
          ,      VAL_COMPONENT_APP_ID = 274
          ,      VAL_COMPONENT_CODE = 'FEM_ATTRIBUTE'
          ,      LOV_TYPE = 'STANDARD'
          ,      OFFLINE_LOV_ENABLED_FLAG = 'N'
          ,      SEGMENT_NUMBER = 100 + ((UPLOAD_PARAM_LIST_ITEM_NUM -8) * 10)
          ,      GROUP_NAME = 'MEMBER_GROUP_VALIDATOR'
          ,      VARIABLE_DATA_TYPE_CLASS = NULL
          WHERE  APPLICATION_ID = 274
          AND    INTERFACE_CODE = p_interface_code
          AND    SEQUENCE_NUM = l_sequence_num;

Line: 1087

          UPDATE BNE_INTERFACE_COLS_B
          SET    VAL_TYPE = NULL
          ,      VAL_OBJ_NAME = NULL
          ,      VAL_COMPONENT_APP_ID = NULL
          ,      VAL_COMPONENT_CODE = NULL
          ,      LOV_TYPE = NULL
          ,      OFFLINE_LOV_ENABLED_FLAG = NULL
          ,      SEGMENT_NUMBER = 100 + ((UPLOAD_PARAM_LIST_ITEM_NUM -8) * 10)
          ,      GROUP_NAME = 'MEMBER_GROUP_VALIDATOR'
          ,      VARIABLE_DATA_TYPE_CLASS =
                 DECODE(changed_rec.DATA_TYPE, 'DATE',
                 'oracle.apps.fem.integrator.dimension.validators.FemAttributeDateTypeValidator',
                 DECODE(changed_rec.DATA_TYPE, 'NUMBER',
                 'oracle.apps.fem.integrator.dimension.validators.FemAttributeNumericTypeValidator',
                 NULL))
          WHERE  APPLICATION_ID = 274
          AND    INTERFACE_CODE = p_interface_code
          AND    SEQUENCE_NUM = l_sequence_num;

Line: 1109

          UPDATE BNE_INTERFACE_COLS_B
          SET    SEGMENT_NUMBER = 100 + ((UPLOAD_PARAM_LIST_ITEM_NUM -8) * 10)
          ,      GROUP_NAME = 'MEMBER_GROUP_VALIDATOR'
          ,      VARIABLE_DATA_TYPE_CLASS =
                 DECODE(changed_rec.DATA_TYPE, 'DATE',
                 'oracle.apps.fem.integrator.dimension.validators.FemAttributeDateTypeValidator',
                 DECODE(changed_rec.DATA_TYPE, 'NUMBER',
                 'oracle.apps.fem.integrator.dimension.validators.FemAttributeNumericTypeValidator',
                 NULL))
          WHERE  APPLICATION_ID = 274
          AND    INTERFACE_CODE = p_interface_code
          AND    SEQUENCE_NUM = l_sequence_num;

Line: 1131

        INSERT INTO BNE_INTERFACE_COLS_B (
          INTERFACE_COL_TYPE,
          INTERFACE_COL_NAME,
          ENABLED_FLAG,
          REQUIRED_FLAG,
          DISPLAY_FLAG,
          READ_ONLY_FLAG,
          NOT_NULL_FLAG,
          SUMMARY_FLAG,
          MAPPING_ENABLED_FLAG,
          DATA_TYPE,
          FIELD_SIZE,
          DEFAULT_TYPE,
          DEFAULT_VALUE,
          SEGMENT_NUMBER,
          GROUP_NAME,
          OA_FLEX_CODE,
          OA_CONCAT_FLEX,
          VAL_TYPE,
          VAL_ID_COL,
          VAL_MEAN_COL,
          VAL_DESC_COL,
          VAL_OBJ_NAME,
          VAL_ADDL_W_C,
          VAL_COMPONENT_APP_ID,
          VAL_COMPONENT_CODE,
          OA_FLEX_NUM,
          OA_FLEX_APPLICATION_ID,
          DISPLAY_ORDER,
          UPLOAD_PARAM_LIST_ITEM_NUM,
          EXPANDED_SQL_QUERY,
          APPLICATION_ID,
          INTERFACE_CODE,
          OBJECT_VERSION_NUMBER,
          SEQUENCE_NUM,
          LOV_TYPE,
          OFFLINE_LOV_ENABLED_FLAG,
          CREATION_DATE,
          CREATED_BY,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          LAST_UPDATE_LOGIN,
          VARIABLE_DATA_TYPE_CLASS
        )
        VALUES
        ( 1,
          changed_rec.INTERFACE_COL,
          'Y',
          'N',
          'Y',
          'N',
          'Y',
          'N',
          'N',
          2,
          2000,
          NULL,
          NULL,
          100+(TO_NUMBER(SUBSTR(changed_rec.INTERFACE_COL, 12))*10),
          'MEMBER_GROUP_VALIDATOR',
          NULL,
          NULL,
          DECODE(changed_rec.DATA_TYPE, 'DIMENSION', 'JAVA', NULL),
          NULL,
          NULL ,
          NULL,
          DECODE(changed_rec.DATA_TYPE, 'DIMENSION', 'oracle.apps.fem.integrator.dimension.validators.FemAttributeValidator', NULL),
          NULL,
          DECODE(changed_rec.DATA_TYPE, 'DIMENSION', 274, NULL),
          DECODE(changed_rec.DATA_TYPE, 'DIMENSION', 'FEM_ATTRIBUTE', NULL),
          NULL,
          NULL,
          l_max_sequence_num*10,
          TO_NUMBER(SUBSTR(changed_rec.INTERFACE_COL, 12)) + 8,
          NULL,
          274,
          p_interface_code,
          1,
          l_max_sequence_num,
          DECODE(changed_rec.DATA_TYPE, 'DIMENSION', 'STANDARD', NULL),
          DECODE(changed_rec.DATA_TYPE, 'DIMENSION', 'N', NULL),
          SYSDATE,
          l_user_id,
          SYSDATE,
          l_user_id,
          l_login_id,
          DECODE(changed_rec.DATA_TYPE, 'DATE',
          'oracle.apps.fem.integrator.dimension.validators.FemAttributeDateTypeValidator',
          DECODE(changed_rec.DATA_TYPE, 'NUMBER',
          'oracle.apps.fem.integrator.dimension.validators.FemAttributeNumericTypeValidator',
          NULL))
        );

Line: 1224

        INSERT INTO BNE_INTERFACE_COLS_TL
        (
          CREATED_BY,
          CREATION_DATE,
          LAST_UPDATED_BY,
          LAST_UPDATE_LOGIN,
          LAST_UPDATE_DATE,
          USER_HINT,
          PROMPT_LEFT,
          USER_HELP_TEXT,
          PROMPT_ABOVE,
          INTERFACE_CODE,
          SEQUENCE_NUM,
          APPLICATION_ID,
          LANGUAGE,
          SOURCE_LANG
        )
        SELECT l_user_id
        ,      SYSDATE
        ,      l_user_id
        ,      l_login_id
        ,      SYSDATE
        ,      M.MESSAGE_TEXT
        ,      B.ATTRIBUTE_NAME
        ,      NULL
        ,      B.ATTRIBUTE_NAME
        ,      p_interface_code
        ,      l_max_sequence_num
        ,      274
        ,      L.LANGUAGE_CODE
        ,      B.SOURCE_LANG
        FROM   FEM_XDIM_DIMENSIONS_VL D
        ,      FEM_DIM_ATTRIBUTES_B A
        ,      FEM_DIM_ATTRIBUTES_TL B
        ,      FND_NEW_MESSAGES M
        ,      FND_LANGUAGES L
        WHERE  L.INSTALLED_FLAG IN ('I', 'B')
        AND    D.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
        AND    D.DIMENSION_ID = A.DIMENSION_ID
        AND    A.ATTRIBUTE_VARCHAR_LABEL = changed_rec.ATTRIBUTE_VARCHAR_LABEL
        AND    A.ATTRIBUTE_ID = B.ATTRIBUTE_ID
        AND    B.LANGUAGE (+) = L.LANGUAGE_CODE
        AND    M.MESSAGE_NAME (+)=
               DECODE(changed_rec.DATA_TYPE, 'DIMENSION',
                 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_LOV_REQ', 'FEM_ADI_USER_HINT_LOV'),
               DECODE(changed_rec.DATA_TYPE, 'VARCHAR2',
                 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_TEXT_REQ', 'FEM_ADI_USER_HINT_TEXT'),
               DECODE(changed_rec.DATA_TYPE, 'NUMBER',
                 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_NUMBER_REQ',  'FEM_ADI_USER_HINT_NUMBER'),
               DECODE(changed_rec.DATA_TYPE, 'DATE',
                 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_DATE_REQ', 'FEM_ADI_USER_HINT_DATE'), NULL))))
        AND    M.LANGUAGE_CODE (+)= L.LANGUAGE_CODE;

Line: 1289

    SELECT REF.column_value AS INTERFACE_COL_NAME
    FROM   TABLE(CAST(l_attr_intf_col_name_tbl AS FND_TABLE_OF_VARCHAR2_30)) REF
    WHERE  NOT EXISTS
    (
      SELECT INTERFACE_COL_NAME
      FROM   BNE_INTERFACE_COLS_B
      WHERE  APPLICATION_ID = 274
      AND    INTERFACE_CODE = p_interface_code
      AND    INTERFACE_COL_NAME = REF.column_value
    ))
  LOOP

    l_max_sequence_num := l_max_sequence_num + 1;

Line: 1303

    INSERT INTO BNE_INTERFACE_COLS_B (
      INTERFACE_COL_TYPE,
      INTERFACE_COL_NAME,
      ENABLED_FLAG,
      REQUIRED_FLAG,
      DISPLAY_FLAG,
      READ_ONLY_FLAG,
      NOT_NULL_FLAG,
      SUMMARY_FLAG,
      MAPPING_ENABLED_FLAG,
      DATA_TYPE,
      FIELD_SIZE,
      DISPLAY_ORDER,
      UPLOAD_PARAM_LIST_ITEM_NUM,
      APPLICATION_ID,
      INTERFACE_CODE,
      OBJECT_VERSION_NUMBER,
      SEQUENCE_NUM,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN
    )
    VALUES
    ( 1,
      intf_rec.INTERFACE_COL_NAME,
      'Y',
      'N',
      'Y',
      'N',
      'Y',
      'N',
      'N',
      2,
      2000,
      l_max_sequence_num*10,
      TO_NUMBER(SUBSTR(intf_rec.INTERFACE_COL_NAME, 12)) + 8,
      274,
      p_interface_code,
      1,
      l_max_sequence_num,
      SYSDATE,
      l_user_id,
      SYSDATE,
      l_user_id,
      l_login_id
    );

Line: 1352

    INSERT INTO BNE_INTERFACE_COLS_TL
    (
      CREATED_BY,
      CREATION_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN,
      LAST_UPDATE_DATE,
      USER_HINT,
      PROMPT_LEFT,
      USER_HELP_TEXT,
      PROMPT_ABOVE,
      INTERFACE_CODE,
      SEQUENCE_NUM,
      APPLICATION_ID,
      LANGUAGE,
      SOURCE_LANG
    )
    SELECT l_user_id
    ,      SYSDATE
    ,      l_user_id
    ,      l_login_id
    ,      SYSDATE
    ,      NULL
    ,      intf_rec.INTERFACE_COL_NAME
    ,      NULL
    ,      intf_rec.INTERFACE_COL_NAME
    ,      p_interface_code
    ,      l_max_sequence_num
    ,      274
    ,      L.LANGUAGE_CODE
    ,      USERENV('LANG')
    FROM   FND_LANGUAGES L
    WHERE  L.INSTALLED_FLAG IN ('I', 'B');

Line: 1466

  l_default_ledger_query VARCHAR2(150) := 'select ledger_name from fem_ledgers_vl where ledger_id =
  (select FND_PROFILE.VALUE_SPECIFIC(''FEM_LEDGER'') from dual)'; -- Bug#5533480

Line: 1492

    SELECT 'Y'
    INTO   l_existed_flag
    FROM   BNE_LAYOUTS_B
    WHERE  APPLICATION_ID = 274
    AND    LAYOUT_CODE = l_layout_code;

Line: 1505

    INSERT INTO BNE_LAYOUTS_B
    ( APPLICATION_ID
    , LAYOUT_CODE
    , OBJECT_VERSION_NUMBER
    , STYLESHEET_APP_ID
    , STYLESHEET_CODE
    , INTEGRATOR_APP_ID
    , INTEGRATOR_CODE
    , STYLE
    , STYLE_CLASS
    , REPORTING_FLAG
    , REPORTING_INTERFACE_APP_ID
    , REPORTING_INTERFACE_CODE
    , CREATION_DATE
    , CREATED_BY
    , LAST_UPDATE_DATE
    , LAST_UPDATED_BY
    , LAST_UPDATE_LOGIN
    , CREATE_DOC_LIST_APP_ID
    , CREATE_DOC_LIST_CODE
    )
    VALUES
    ( 274
    , l_layout_code
    , 1
    , 231
    , 'DEFAULT'
    , 274
    , p_integrator_code
    , NULL
    , 'BNE_PAGE'
    , 'N'
    , NULL
    , NULL
    , SYSDATE
    , l_user_id
    , SYSDATE
    , l_user_id
    , l_login_id
    , NULL
    , NULL
    );

Line: 1548

    INSERT INTO BNE_LAYOUTS_TL
    ( APPLICATION_ID
    , LAYOUT_CODE
    , USER_NAME
    , CREATED_BY
    , CREATION_DATE
    , LAST_UPDATED_BY
    , LAST_UPDATE_LOGIN
    , LAST_UPDATE_DATE
    , LANGUAGE
    , SOURCE_LANG
    )
    SELECT 274
    ,      l_layout_code
    ,      SUBSTR(M.MESSAGE_TEXT,0, INSTR(M.MESSAGE_TEXT, 'DIM_NAME')-2) ||
           DT.DIMENSION_NAME ||
           SUBSTR(M.MESSAGE_TEXT,INSTR(M.MESSAGE_TEXT, 'DIM_NAME')+8)
    ,      l_user_id
    ,      SYSDATE
    ,      l_user_id
    ,      l_login_id
    ,      SYSDATE
    ,      DT.LANGUAGE
    ,      DT.SOURCE_LANG
    FROM   FEM_DIMENSIONS_TL DT
    ,      FEM_DIMENSIONS_B DB
    ,      FND_NEW_MESSAGES M
    ,      FND_LANGUAGES L
    WHERE  DB.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
    AND    DT.DIMENSION_ID = DB.DIMENSION_ID
    AND    M.APPLICATION_ID= 274
    AND    M.MESSAGE_NAME = 'FEM_ADI_MEMBER_LAYOUT'
    AND    M.LANGUAGE_CODE = DT.LANGUAGE
    AND    M.LANGUAGE_CODE = L.LANGUAGE_CODE
    AND    L.INSTALLED_FLAG IN ('I', 'B');

Line: 1590

    SELECT B.BLOCK_ID
    INTO   l_header_block_id
    FROM   BNE_LAYOUT_BLOCKS_B B
    WHERE  B.APPLICATION_ID = 274
    AND    B.LAYOUT_CODE = l_layout_code
    AND    B.LAYOUT_ELEMENT = 'HEADER';

Line: 1606

    INSERT INTO BNE_LAYOUT_BLOCKS_B
    ( APPLICATION_ID
    , LAYOUT_CODE
    , BLOCK_ID
    , OBJECT_VERSION_NUMBER
    , PARENT_ID
    , LAYOUT_ELEMENT
    , STYLE_CLASS
    , STYLE
    , ROW_STYLE_CLASS
    , ROW_STYLE
    , COL_STYLE_CLASS
    , COL_STYLE
    , PROMPT_DISPLAYED_FLAG
    , PROMPT_STYLE_CLASS
    , PROMPT_STYLE
    , HINT_DISPLAYED_FLAG
    , HINT_STYLE_CLASS
    , HINT_STYLE
    , ORIENTATION
    , LAYOUT_CONTROL
    , DISPLAY_FLAG
    , BLOCKSIZE
    , MINSIZE
    , MAXSIZE
    , SEQUENCE_NUM
    , PROMPT_COLSPAN
    , HINT_COLSPAN
    , ROW_COLSPAN
    , SUMMARY_STYLE_CLASS
    , SUMMARY_STYLE
    , CREATION_DATE
    , CREATED_BY
    , LAST_UPDATE_DATE
    , LAST_UPDATED_BY
    , LAST_UPDATE_LOGIN
    ) VALUES
    ( 274
    , l_layout_code
    , l_header_block_id
    , 1
    , NULL
    , 'HEADER'
    , 'BNE_HEADER'
    , NULL
    , 'BNE_HEADER_ROW'
    , NULL
    , NULL
    , NULL
    , 'Y'
    , 'BNE_HEADER_HEADER'
    , NULL
    , 'Y'
    , 'BNE_HEADER_HINT'
    , NULL
    , 'HORIZONTAL'
    , 'COLUMN_FLOW'
    , 'Y'
    , 1
    , 1
    , 1
    , 10
    , 3
    , 1
    , 2
    , 'BNE_LINES_TOTAL'
    , NULL
    , SYSDATE
    , l_user_id
    , SYSDATE
    , l_user_id
    , l_login_id
    );

Line: 1680

    INSERT INTO BNE_LAYOUT_BLOCKS_TL
    ( APPLICATION_ID
    , LAYOUT_CODE
    , BLOCK_ID
    , USER_NAME
    , CREATED_BY
    , CREATION_DATE
    , LAST_UPDATED_BY
    , LAST_UPDATE_LOGIN
    , LAST_UPDATE_DATE
    , LANGUAGE
    , SOURCE_LANG
    )
    SELECT 274
    ,      l_layout_code
    ,      l_header_block_id
    ,      M.MESSAGE_TEXT
    ,      l_user_id
    ,      SYSDATE
    ,      l_user_id
    ,      l_login_id
    ,      SYSDATE
    ,      L.LANGUAGE_CODE
    ,      USERENV('LANG')
    FROM   FND_NEW_MESSAGES M,
           FND_LANGUAGES L
    WHERE  M.MESSAGE_NAME = 'LAY_LB_HEADER'
    AND    M.LANGUAGE_CODE = L.LANGUAGE_CODE
    AND    L.INSTALLED_FLAG IN ('I', 'B');

Line: 1717

    SELECT B.BLOCK_ID
    INTO   l_line_block_id
    FROM   BNE_LAYOUT_BLOCKS_B B
    WHERE  B.APPLICATION_ID = 274
    AND    B.LAYOUT_CODE = l_layout_code
    AND    B.LAYOUT_ELEMENT = 'LINE'
    AND    B.PARENT_ID =
    (
      SELECT BLOCK_ID
      FROM   BNE_LAYOUT_BLOCKS_B
      WHERE  APPLICATION_ID = B.APPLICATION_ID
      AND    LAYOUT_CODE = B.LAYOUT_CODE
      AND    LAYOUT_ELEMENT = 'HEADER'
    );

Line: 1740

    INSERT INTO BNE_LAYOUT_BLOCKS_B
    ( APPLICATION_ID
    , LAYOUT_CODE
    , BLOCK_ID
    , OBJECT_VERSION_NUMBER
    , PARENT_ID
    , LAYOUT_ELEMENT
    , STYLE_CLASS
    , STYLE
    , ROW_STYLE_CLASS
    , ROW_STYLE
    , COL_STYLE_CLASS
    , COL_STYLE
    , PROMPT_DISPLAYED_FLAG
    , PROMPT_STYLE_CLASS
    , PROMPT_STYLE
    , HINT_DISPLAYED_FLAG
    , HINT_STYLE_CLASS
    , HINT_STYLE
    , ORIENTATION
    , LAYOUT_CONTROL
    , DISPLAY_FLAG
    , BLOCKSIZE
    , MINSIZE
    , MAXSIZE
    , SEQUENCE_NUM
    , PROMPT_COLSPAN
    , HINT_COLSPAN
    , ROW_COLSPAN
    , SUMMARY_STYLE_CLASS
    , SUMMARY_STYLE
    , CREATION_DATE
    , CREATED_BY
    , LAST_UPDATE_DATE
    , LAST_UPDATED_BY
    , LAST_UPDATE_LOGIN
    ) VALUES
    ( 274
    , l_layout_code
    , l_line_block_id
    , 1
    , l_header_block_id
    , 'LINE'
    , 'BNE_LINES'
    , NULL
    , 'BNE_LINES_ROW'
    , NULL
    , NULL
    , NULL
    , 'Y'
    , 'BNE_LINES_HEADER'
    , NULL
    , 'Y'
    , 'BNE_LINES_HINT'
    , NULL
    , 'VERTICAL'
    , 'TABLE_FLOW'
    , 'Y'
    , 10
    , 1
    , 1
    , 20
    , NULL
    , NULL
    , NULL
    , 'BNE_LINES_TOTAL'
    , NULL
    , SYSDATE
    , l_user_id
    , SYSDATE
    , l_user_id
    , l_login_id
    );

Line: 1814

    INSERT INTO BNE_LAYOUT_BLOCKS_TL
    ( APPLICATION_ID
    , LAYOUT_CODE
    , BLOCK_ID
    , USER_NAME
    , CREATED_BY
    , CREATION_DATE
    , LAST_UPDATED_BY
    , LAST_UPDATE_LOGIN
    , LAST_UPDATE_DATE
    , LANGUAGE
    , SOURCE_LANG
    )
    SELECT 274
    ,      l_layout_code
    ,      l_line_block_id
    ,      M.MESSAGE_TEXT
    ,      l_user_id
    ,      SYSDATE
    ,      l_user_id
    ,      l_login_id
    ,      SYSDATE
    ,      L.LANGUAGE_CODE
    ,      USERENV('LANG')
    FROM   FND_NEW_MESSAGES M,
           FND_LANGUAGES L
    WHERE  M.MESSAGE_NAME = 'LAY_LB_LINE'
    AND    M.LANGUAGE_CODE = L.LANGUAGE_CODE
    AND    L.INSTALLED_FLAG IN ('I', 'B');

Line: 1850

    SELECT NVL(MAX(A.INTERFACE_SEQ_NUM), 0)
    INTO   l_interface_seq_num
    FROM   BNE_LAYOUT_COLS A
    WHERE  A.APPLICATION_ID = 274
    AND    A.BLOCK_ID = l_header_block_id
    AND    A.LAYOUT_CODE = l_layout_code;

Line: 1864

    INSERT INTO BNE_LAYOUT_COLS
    ( APPLICATION_ID
    , LAYOUT_CODE
    , BLOCK_ID
    , OBJECT_VERSION_NUMBER
    , INTERFACE_APP_ID
    , INTERFACE_CODE
    , INTERFACE_SEQ_NUM
    , SEQUENCE_NUM
    , STYLE
    , STYLE_CLASS
    , HINT_STYLE
    , HINT_STYLE_CLASS
    , PROMPT_STYLE
    , PROMPT_STYLE_CLASS
    , DEFAULT_TYPE
    , DEFAULT_VALUE
    , CREATED_BY
    , CREATION_DATE
    , LAST_UPDATED_BY
    , LAST_UPDATE_LOGIN
    , LAST_UPDATE_DATE
    )
    VALUES
    ( 274
    , l_layout_code
    , l_header_block_id
    , 1
    , 274
    , 'FEM_DIM_MEMBER_HEADER_INTF'
    , 1
    , 1
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , l_user_id
    , SYSDATE
    , l_user_id
    , l_login_id
    , SYSDATE
    );

Line: 1916

  UPDATE BNE_LAYOUT_COLS
  SET    BLOCK_ID = l_header_block_id
  WHERE  APPLICATION_ID = 274
  AND    LAYOUT_CODE = l_layout_code
  AND    INTERFACE_APP_ID = 274
  AND    INTERFACE_CODE = p_interface_code
  AND    INTERFACE_SEQ_NUM IN
  (
      SELECT SEQUENCE_NUM
      FROM BNE_INTERFACE_COLS_B I
      WHERE I.APPLICATION_ID = 274
      AND   I.INTERFACE_CODE = p_interface_code
      AND   I.INTERFACE_COL_NAME IN (
            SELECT column_value
            FROM TABLE(CAST(l_header_block_cols AS FND_TABLE_OF_VARCHAR2_30))
            )
  )
  AND   BLOCK_ID <> l_header_block_id;

Line: 1941

    SELECT INTERFACE_COL_NAME
    ,      SEQUENCE_NUM
    ,      DECODE (INTERFACE_COL_NAME, 'P_DIMENSION_VARCHAR_LABEL', 'CONSTANT','P_LEDGER_ID','SQL',
           NULL) AS DEFAULT_TYPE
    ,      DECODE (INTERFACE_COL_NAME, 'P_DIMENSION_VARCHAR_LABEL', p_dimension_varchar_label,'P_LEDGER_ID',l_default_ledger_query,
           NULL) AS DEFAULT_VALUE  /* Bug#5533480 */
    FROM BNE_INTERFACE_COLS_B IC
    WHERE APPLICATION_ID = 274
    AND   INTERFACE_CODE = p_interface_code
    AND   INTERFACE_COL_NAME IN (
          SELECT column_value
          FROM TABLE(CAST(l_header_block_cols AS FND_TABLE_OF_VARCHAR2_30))
          )
    AND   NOT EXISTS
          (
            SELECT 1
                FROM   BNE_LAYOUT_COLS
                WHERE  APPLICATION_ID = 274
                AND    LAYOUT_CODE = l_layout_code
                AND    INTERFACE_APP_ID = 274
              AND    INTERFACE_CODE = p_interface_code
                AND    INTERFACE_SEQ_NUM = IC.SEQUENCE_NUM
                AND    BLOCK_ID = l_header_block_id
          )
  )
  LOOP
    IF NOT((p_dimension_type_code <> 'TIME' AND intf_rec.INTERFACE_COL_NAME = 'P_CALENDAR_DISPLAY_CODE') OR
      (p_value_set_required_flag = 'N' AND intf_rec.INTERFACE_COL_NAME = 'P_LEDGER_ID')) THEN
      INSERT INTO BNE_LAYOUT_COLS
      ( APPLICATION_ID
      , LAYOUT_CODE
      , BLOCK_ID
      , OBJECT_VERSION_NUMBER
      , INTERFACE_APP_ID
      , INTERFACE_CODE
      , INTERFACE_SEQ_NUM
      , SEQUENCE_NUM
      , STYLE
      , STYLE_CLASS
      , HINT_STYLE
      , HINT_STYLE_CLASS
      , PROMPT_STYLE
      , PROMPT_STYLE_CLASS
      , DEFAULT_TYPE
      , DEFAULT_VALUE
      , CREATED_BY
      , CREATION_DATE
      , LAST_UPDATED_BY
      , LAST_UPDATE_LOGIN
      , LAST_UPDATE_DATE
      )
      VALUES
      ( 274
      , l_layout_code
      , l_header_block_id
      , 1
      , 274
      , p_interface_code
      , intf_rec.SEQUENCE_NUM
      , intf_rec.SEQUENCE_NUM * 10
      , NULL
      , NULL
      , NULL
      , NULL
      , NULL
      , NULL
      , intf_rec.DEFAULT_TYPE
      , intf_rec.DEFAULT_VALUE
      , l_user_id
      , SYSDATE
      , l_user_id
      , l_login_id
      , SYSDATE
      );

Line: 2022

  UPDATE BNE_LAYOUT_COLS
  SET    BLOCK_ID = l_line_block_id
  WHERE  APPLICATION_ID = 274
  AND    LAYOUT_CODE = l_layout_code
  AND    INTERFACE_APP_ID = 274
  AND    INTERFACE_CODE = p_interface_code
  AND    INTERFACE_SEQ_NUM IN
  (
      SELECT SEQUENCE_NUM
      FROM BNE_INTERFACE_COLS_B I
      WHERE I.APPLICATION_ID = 274
      AND   I.INTERFACE_CODE = p_interface_code
      AND   I.INTERFACE_COL_NAME IN (
             SELECT column_value
             FROM TABLE(CAST(l_line_block_cols AS FND_TABLE_OF_VARCHAR2_30))
            )
  )
  AND   BLOCK_ID <> l_line_block_id;

Line: 2045

  DELETE BNE_LAYOUT_COLS
  WHERE  APPLICATION_ID = 274
  AND    INTERFACE_CODE = p_interface_code
  AND    INTERFACE_SEQ_NUM IN
  ( SELECT I.SEQUENCE_NUM
    FROM   BNE_INTERFACE_COLS_B I
    WHERE  I.APPLICATION_ID = INTERFACE_APP_ID
    AND    I.INTERFACE_CODE = INTERFACE_CODE
    AND    I.INTERFACE_COL_NAME LIKE 'P_ATTRIBUTE%'
    AND   NOT EXISTS
    (
      SELECT 1
      FROM   FEM_WEBADI_DIM_ATTR_MAPS M
      WHERE  M.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
      AND    M.INTERFACE_COL = I.INTERFACE_COL_NAME
    )
  );

Line: 2067

  DELETE BNE_LAYOUT_COLS
  WHERE  APPLICATION_ID = 274
  AND    LAYOUT_CODE = l_layout_code
  AND    INTERFACE_APP_ID = 274
  AND    INTERFACE_CODE = p_interface_code
  AND    INTERFACE_SEQ_NUM NOT IN
  (
      SELECT SEQUENCE_NUM
      FROM BNE_INTERFACE_COLS_B I
      WHERE I.APPLICATION_ID = 274
      AND   I.INTERFACE_CODE = p_interface_code
      AND   (I.INTERFACE_COL_NAME IN (
             SELECT column_value
             FROM TABLE(CAST(l_header_block_cols AS FND_TABLE_OF_VARCHAR2_30))
            )
            OR
            I.INTERFACE_COL_NAME IN (
             SELECT column_value
             FROM TABLE(CAST(l_line_block_cols AS FND_TABLE_OF_VARCHAR2_30))
            )
            OR I.INTERFACE_COL_NAME LIKE 'P_ATTRIBUTE%'
            )
  );

Line: 2095

  INSERT INTO BNE_LAYOUT_COLS
  ( APPLICATION_ID
  , LAYOUT_CODE
  , BLOCK_ID
  , OBJECT_VERSION_NUMBER
  , INTERFACE_APP_ID
  , INTERFACE_CODE
  , INTERFACE_SEQ_NUM
  , SEQUENCE_NUM
  , STYLE
  , STYLE_CLASS
  , HINT_STYLE
  , HINT_STYLE_CLASS
  , PROMPT_STYLE
  , PROMPT_STYLE_CLASS
  , DEFAULT_TYPE
  , DEFAULT_VALUE
  , CREATED_BY
  , CREATION_DATE
  , LAST_UPDATED_BY
  , LAST_UPDATE_LOGIN
  , LAST_UPDATE_DATE
  )
  SELECT 274
  ,      l_layout_code
  ,      l_line_block_id
  ,      1
  ,      274
  ,      p_interface_code
  ,      IC.SEQUENCE_NUM
  ,      IC.SEQUENCE_NUM * 10
  ,      NULL
  ,      NULL
  ,      NULL
  ,      NULL
  ,      NULL
  ,      NULL
  ,      NULL
  ,      NULL
  ,      l_user_id
  ,      SYSDATE
  ,      l_user_id
  ,      l_login_id
  ,      SYSDATE
  FROM   BNE_INTERFACE_COLS_B IC
  WHERE  APPLICATION_ID = 274
  AND    IC.INTERFACE_CODE = p_interface_code
  AND (  ( p_group_use_code = 'NOT_SUPPORTED'
           AND NOT IC.INTERFACE_COL_NAME =
                  DECODE(p_group_use_code, 'NOT_SUPPORTED', 'P_DIMENSION_GROUP_DISPLAY_CODE', IC.INTERFACE_COL_NAME)
         )
         OR
         ( p_group_use_code <> 'NOT_SUPPORTED'
           AND IC.INTERFACE_COL_NAME =
               DECODE(p_group_use_code, 'NOT_SUPPORTED', 'P_DIMENSION_GROUP_DISPLAY_CODE', IC.INTERFACE_COL_NAME)
        )
      )
  AND    INTERFACE_COL_NAME IN (
           SELECT column_value
           FROM TABLE(CAST(l_line_block_cols AS FND_TABLE_OF_VARCHAR2_30))
         )
  AND    NOT EXISTS
  ( SELECT 1
    FROM   BNE_LAYOUT_COLS LC
    WHERE  LC.APPLICATION_ID = IC.APPLICATION_ID
    AND    LC.LAYOUT_CODE = l_layout_code
    AND    LC.BLOCK_ID = l_line_block_id
    AND    LC.INTERFACE_APP_ID = IC.APPLICATION_ID
    AND    LC.INTERFACE_CODE = IC.INTERFACE_CODE
    AND    LC.INTERFACE_SEQ_NUM = IC. SEQUENCE_NUM
  );

Line: 2169

     DELETE FROM BNE_LAYOUT_COLS WHERE LAYOUT_CODE = l_layout_code AND INTERFACE_CODE = p_interface_code
     AND INTERFACE_SEQ_NUM = (SELECT SEQUENCE_NUM FROM BNE_INTERFACE_COLS_B WHERE INTERFACE_CODE = p_interface_code
     AND INTERFACE_COL_NAME = 'P_MEMBER_DISPLAY_CODE');

Line: 2175

  INSERT INTO BNE_LAYOUT_COLS
  ( APPLICATION_ID
  , LAYOUT_CODE
  , BLOCK_ID
  , OBJECT_VERSION_NUMBER
  , INTERFACE_APP_ID
  , INTERFACE_CODE
  , INTERFACE_SEQ_NUM
  , SEQUENCE_NUM
  , STYLE
  , STYLE_CLASS
  , HINT_STYLE
  , HINT_STYLE_CLASS
  , PROMPT_STYLE
  , PROMPT_STYLE_CLASS
  , DEFAULT_TYPE
  , DEFAULT_VALUE
  , CREATED_BY
  , CREATION_DATE
  , LAST_UPDATED_BY
  , LAST_UPDATE_LOGIN
  , LAST_UPDATE_DATE
  )
  SELECT 274
  ,      l_layout_code
  ,      l_line_block_id
  ,      1
  ,      274
  ,      p_interface_code
  ,      IC.SEQUENCE_NUM
  ,      IC.SEQUENCE_NUM * 10
  ,      NULL
  ,      NULL
  ,      NULL
  ,      NULL
  ,      NULL
  ,      NULL
  ,      NULL
  ,      NULL
  ,      l_user_id
  ,      SYSDATE
  ,      l_user_id
  ,      l_login_id
  ,      SYSDATE
  FROM  BNE_INTERFACE_COLS_B IC
  WHERE APPLICATION_ID = 274
  AND   IC.INTERFACE_CODE = p_interface_code
  AND   EXISTS
  ( SELECT 1
    FROM   FEM_WEBADI_DIM_ATTR_MAPS M
    WHERE  M.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
    AND    M.INTERFACE_COL = INTERFACE_COL_NAME
  )
  AND    NOT EXISTS
  ( SELECT 1
    FROM   BNE_LAYOUT_COLS LC
    WHERE  LC.APPLICATION_ID = IC.APPLICATION_ID
    AND    LC.LAYOUT_CODE = l_layout_code
    AND    LC.BLOCK_ID = l_line_block_id
    AND    LC.INTERFACE_APP_ID = IC.APPLICATION_ID
    AND    LC.INTERFACE_CODE = IC.INTERFACE_CODE
    AND    LC.INTERFACE_SEQ_NUM = IC. SEQUENCE_NUM
  );

Line: 2305

  l_updated_flag              VARCHAR2(10)  ;

Line: 2342

  SELECT DIMENSION_ID
  ,      DIMENSION_NAME
  ,      'FEM_DIM_' || DIMENSION_ID
  ,      'UPLOAD_MEMBER_INTERFACE' || DIMENSION_ID
  ,      DIMENSION_TYPE_CODE
  ,      VALUE_SET_REQUIRED_FLAG
  ,      GROUP_USE_CODE
  INTO   l_dimension_id
  ,      l_dimension_name
  ,      l_object_code
  ,      l_interface_name
  ,      l_dimension_type_code
  ,      l_value_set_required_flag
  ,      l_group_use_code
  FROM FEM_XDIM_DIMENSIONS_VL
  WHERE DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label;

Line: 2378

    SELECT 'Y'
    INTO   l_existed_flag
    FROM   BNE_INTEGRATORS_B
    WHERE  APPLICATION_ID = 274
    AND    INTEGRATOR_CODE = l_integrator_code;

Line: 2391

    INSERT INTO BNE_INTEGRATORS_B
      ( APPLICATION_ID
      , INTEGRATOR_CODE
      , OBJECT_VERSION_NUMBER
      , UPLOAD_PARAM_LIST_APP_ID
      , UPLOAD_PARAM_LIST_CODE
      , UPLOAD_SERV_PARAM_LIST_APP_ID
      , UPLOAD_SERV_PARAM_LIST_CODE
      , IMPORT_PARAM_LIST_APP_ID
      , IMPORT_PARAM_LIST_CODE
      , IMPORT_TYPE
      , UPLOADER_CLASS
      , DATE_FORMAT
      , CREATED_BY
      , CREATION_DATE
      , LAST_UPDATED_BY
      , LAST_UPDATE_DATE
      , ENABLED_FLAG)
    VALUES
      ( 274
      , l_integrator_code
      , 1
      , 274
      , l_intg_upl_param_list_code
      , 231
      , 'UPL_SERV_INTERF_COLS'
      , 274
      , l_intg_imp_param_list_code
      , 1
      , 'oracle.apps.bne.integrator.upload.BneUploader'
      , 'yyyy-MM-dd'
      , l_user_id
      , SYSDATE
      , l_user_id
      , SYSDATE
      , 'Y');

Line: 2428

    INSERT INTO BNE_INTEGRATORS_TL
      ( APPLICATION_ID
      , INTEGRATOR_CODE
      , LANGUAGE
      , SOURCE_LANG
      , USER_NAME
      , UPLOAD_HEADER
      , UPLOAD_TITLE_BAR
      , CREATED_BY
      , CREATION_DATE
      , LAST_UPDATED_BY
      , LAST_UPDATE_DATE)
    SELECT 274
    ,      l_integrator_code
    ,      M.LANGUAGE_CODE
    ,      userenv('LANG')
    ,      M.MESSAGE_TEXT
    ,      'Upload Parameters'
    ,      'Upload Parameters'
    ,      l_user_id
    ,      SYSDATE
    ,      l_user_id
    ,      SYSDATE
    FROM   FND_NEW_MESSAGES M,
           FND_LANGUAGES L
    WHERE  M.MESSAGE_NAME = 'FEM_ADI_MEMBER_INTEGRATOR'
    AND    M.LANGUAGE_CODE = L.LANGUAGE_CODE
    AND    L.INSTALLED_FLAG IN ('I', 'B');

Line: 2481

    SELECT 'Y'
    INTO   l_existed_flag
    FROM   BNE_INTERFACES_B
    WHERE  APPLICATION_ID = 274
    AND    INTERFACE_CODE = l_interface_code;

Line: 2494

    SELECT NVL(MAX(UPLOAD_ORDER), 0) + 1
    INTO   l_order_seq
    FROM   BNE_INTERFACES_B
    WHERE  APPLICATION_ID = 274
    AND    INTEGRATOR_APP_ID = 274
    AND    INTEGRATOR_CODE = l_integrator_code;

Line: 2505

    INSERT INTO BNE_INTERFACES_B
      (APPLICATION_ID,
       INTERFACE_CODE,
       OBJECT_VERSION_NUMBER,
       INTEGRATOR_APP_ID,
       INTEGRATOR_CODE,
       INTERFACE_NAME,
       UPLOAD_TYPE,
       UPLOAD_PARAM_LIST_APP_ID,
       UPLOAD_PARAM_LIST_CODE,
       UPLOAD_ORDER,
       CREATED_BY,
       CREATION_DATE,
       LAST_UPDATED_BY,
       LAST_UPDATE_DATE)
    VALUES
      (274,
       l_interface_code,
       1,
       274,
       l_integrator_code,
       l_interface_name,
       2,
       274,
       l_intf_upl_param_list_code,
       l_order_seq,
       l_user_id,
       SYSDATE,
       l_user_id,
       SYSDATE);

Line: 2538

    INSERT INTO BNE_INTERFACES_TL
      ( APPLICATION_ID,
        INTERFACE_CODE,
        LANGUAGE,
        SOURCE_LANG,
        USER_NAME,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_DATE)
    SELECT 274
    ,      l_interface_code
    ,      D.LANGUAGE
    ,      D.SOURCE_LANG
    ,      SUBSTR(M.MESSAGE_TEXT,0, INSTR(M.MESSAGE_TEXT, 'DIM_NAME')-2) ||
           D.DIMENSION_NAME ||
           SUBSTR(M.MESSAGE_TEXT,INSTR(M.MESSAGE_TEXT, 'DIM_NAME')+8)
    ,      l_user_id
    ,      SYSDATE
    ,      l_user_id
    ,      SYSDATE
    FROM   FEM_DIMENSIONS_TL D, FND_NEW_MESSAGES M, FND_LANGUAGES L
    WHERE  D.DIMENSION_ID = l_dimension_id
    AND    M.APPLICATION_ID= 274
    AND    M.MESSAGE_NAME = 'FEM_ADI_MEMBER_INTERFACE'
    AND    M.LANGUAGE_CODE = D.LANGUAGE
    AND    M.LANGUAGE_CODE = L.LANGUAGE_CODE
    AND    L.INSTALLED_FLAG IN ('I', 'B');

Line: 2605

      x_updated_flag                 => l_updated_flag
    );

Line: 2612

    l_updated_flag := 'Y';

Line: 2630

      x_updated_flag                 => l_updated_flag
    );

Line: 2642

  IF (l_updated_flag = 'Y') THEN

    Populate_Dim_Layout (
      p_api_version                  => 1.0,
      p_init_msg_list                => FND_API.G_FALSE,
      p_commit                       => FND_API.G_FALSE,
      x_return_status                => l_return_status,
      x_msg_count                    => l_msg_count,
      x_msg_data                     => l_msg_data,
      p_integrator_code              => l_integrator_code,
      p_interface_code               => l_interface_code,
      p_dimension_varchar_label      => p_dimension_varchar_label,
      p_dimension_name               => l_dimension_name,
      p_object_code                  => l_object_code,
      p_dimension_type_code          => l_dimension_type_code,
      p_value_set_required_flag      => l_value_set_required_flag,
      p_group_use_code               => l_group_use_code
    );

Line: 2669

    UPDATE BNE_INTEGRATORS_B
    SET    OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
    --,      LAST_UPDATE_DATE = last_update_date + 1
    WHERE  APPLICATION_ID = 274
    AND    INTEGRATOR_CODE = l_integrator_code;

Line: 2743

  SELECT
    dimension_id
  , intf_member_b_table_name
  , intf_member_tl_table_name
  , intf_attribute_table_name
  , member_b_table_name
  , member_display_code_col
  , member_name_col
  , hierarchy_table_name
  , dimension_type_code
  , group_use_code
  , value_set_required_flag
  FROM
    fem_xdim_dimensions_vl xDimVL
  WHERE
    xDimVL.dimension_varchar_label = p_dimension_varchar_label ;

Line: 2765

  SELECT
    AttrMaps.interface_col
  , AttrMaps.attribute_varchar_label
  , AttrMaps.data_type
  FROM
    fem_webadi_dim_attr_maps AttrMaps
  WHERE
    AttrMaps.dimension_varchar_label = p_dimension_varchar_label ;

Line: 2803

  g_global_val_tbl.DELETE ;

Line: 2985

  SELECT
    attrmap.interface_col
  , dimattr.attribute_data_type_code
  FROM
    fem_dim_attributes_b     dimattr
  , fem_xdim_dimensions_vl   xdim
  , fem_webadi_dim_attr_maps attrmap
  WHERE
    attrmap.dimension_varchar_label      =  p_dimension_varchar_label
    AND xdim.dimension_varchar_label     =  attrmap.dimension_varchar_label
    AND dimattr.dimension_id             =  xdim.dimension_id
    AND dimattr.attribute_varchar_label  =  attrmap.attribute_varchar_label
    AND dimattr.attribute_data_type_code <> attrmap.data_type ;

Line: 3004

  SELECT
    dimattr.attribute_varchar_label
  , dimattr.attribute_data_type_code
  FROM
    fem_dim_attributes_b   dimattr
  , fem_xdim_dimensions_vl xdim
  WHERE
    xdim.dimension_varchar_label = p_dimension_varchar_label
  AND xdim.dimension_id          = dimattr.dimension_id
  AND NOT EXISTS
  ( SELECT
      attrmap.attribute_varchar_label
    FROM
      fem_webadi_dim_attr_maps attrmap
    WHERE
      attrmap.dimension_varchar_label      = xdim.dimension_varchar_label
      AND attrmap.attribute_varchar_label  = dimattr.attribute_varchar_label
  )
  ORDER BY dimattr.attribute_required_flag DESC;

Line: 3027

    SELECT
      VALUE(avlble_seq) col_map_seq
    FROM
      TABLE( CAST( g_sequences_tbl AS FND_TABLE_OF_NUMBER ) ) avlble_seq
    MINUS
    SELECT
      TO_NUMBER( SUBSTR( attrmap.interface_col, 12 ) ) col_map_seq
    FROM
      fem_webadi_dim_attr_maps attrmap
    WHERE
      attrmap.dimension_varchar_label = p_dimension_varchar_label ;

Line: 3071

  DELETE
  FROM
    fem_webadi_dim_attr_maps attrmap
  WHERE
    attrmap.dimension_varchar_label = p_dimension_varchar_label
    AND attrmap.attribute_varchar_label NOT IN
    ( SELECT
        attr.attribute_varchar_label
      FROM
        fem_dim_attributes_b   attr
      , fem_xdim_dimensions_vl xdim
      WHERE
        xdim.dimension_varchar_label        = attrmap.dimension_varchar_label
          AND attr.dimension_id             = xdim.dimension_id
    ) ;

Line: 3091

    l_curr_activity := 'Stale records deleted. Now Updating records with ' ||
                       'changed datatype.' ;

Line: 3116

        UPDATE
          fem_webadi_dim_attr_maps attrmap
        SET
          attrmap.data_type = l_data_type_tbl(l_indx)
        WHERE
          attrmap.dimension_varchar_label = p_dimension_varchar_label
          AND attrmap.interface_col       = g_changed_dt_intf_col_tbl(l_indx) ;

Line: 3132

    l_curr_activity := 'Update of records with changed data type done.' ;

Line: 3146

  SELECT
    NVL( MAX ( TO_NUMBER( SUBSTR( attrmap.interface_col, 12 ) ) ), 0 )
  , COUNT(1)
  INTO
    l_max_attr_seq
  , l_cnt_rec
  FROM
    fem_webadi_dim_attr_maps attrmap
  WHERE
    attrmap.dimension_varchar_label = p_dimension_varchar_label ;

Line: 3172

  l_data_type_tbl.DELETE ;

Line: 3237

      INSERT INTO
        fem_webadi_dim_attr_maps
        ( interface_col
        , dimension_varchar_label
        , attribute_varchar_label
        , data_type
        )
      VALUES
      ( g_changed_intf_col_tbl( l_indx )
      , p_dimension_varchar_label
      , l_attr_label_tbl(l_indx)
      , l_data_type_tbl(l_indx)
      ) ;

Line: 3254

        l_curr_activity := 'Bulk inserting with no gap logic done.' ;

Line: 3288

        INSERT INTO
          fem_webadi_dim_attr_maps
          ( interface_col
          , dimension_varchar_label
          , attribute_varchar_label
          , data_type
          )
        VALUES
        ( g_changed_intf_col_tbl(l_indx)
        , p_dimension_varchar_label
        , l_attr_label_tbl(l_indx)
        , l_data_type_tbl(l_indx)
        ) ;

Line: 3305

        l_curr_activity := 'Bulk insertion with gap logic done.' ;

Line: 3425

  l_update_str           VARCHAR2(4000) := NULL ;

Line: 3435

  l_b_table_ins_clause   := 'INSERT INTO ' ||
                             g_global_val_tbl(1).intf_member_b_table_name ||
                             '( ' ||
                             g_global_val_tbl(1).member_display_code_col ||
                             ', status' ;

Line: 3445

  l_tl_table_ins_clause  := 'INSERT INTO ' ||
                            g_global_val_tbl(1).intf_member_tl_table_name ||
                            '( ' ||
                            g_global_val_tbl(1).member_display_code_col ||
                            ', language' ||
                            ',' ||g_global_val_tbl(1).member_name_col ||
                            ', description' ||
                            ', status' ;

Line: 3495

          l_update_str := 'UPDATE ' ||
                             g_global_val_tbl(1).intf_member_b_table_name ||
                          ' SET ' ||
                            'status = :b_status' ||
                            ', dimension_group_display_code = ' ||
                            ':b_dimension_group_display_code' ||
                          ' WHERE ' ||
                            g_global_val_tbl(1).member_display_code_col ||
                            ' = :b_member_display_code' ||
                            ' AND value_set_display_code = ' ||
                            ':b_value_set_display_code' ;

Line: 3508

            l_update_str
          USING
            'LOAD'
          , g_global_val_tbl(1).dim_grp_disp_code
          , g_global_val_tbl(1).member_display_code
          , g_global_val_tbl(1).value_set_display_code ;

Line: 3544

          l_update_str := 'UPDATE ' ||
                             g_global_val_tbl(1).intf_member_b_table_name ||
                          ' SET ' ||
                            'status = :b_status' ||
                          ' WHERE ' ||
                            g_global_val_tbl(1).member_display_code_col ||
                            ' = :b_member_display_code' ||
                            ' AND value_set_display_code = ' ||
                            ':b_value_set_display_code' ;

Line: 3555

            l_update_str
          USING
            'LOAD'
          , g_global_val_tbl(1).member_display_code
          , g_global_val_tbl(1).value_set_display_code ;

Line: 3593

        l_update_str := 'UPDATE ' ||
                           g_global_val_tbl(1).intf_member_tl_table_name ||
                        ' SET ' ||
                        g_global_val_tbl(1).member_name_col ||
                        ' = :b_member_name ' ||
                        ', description   = :b_description ' ||
                        ', status        = :b_status ' ||
                        ' WHERE ' ||
                          g_global_val_tbl(1).member_display_code_col ||
                          ' = :b_member_display_code' ||
                          ' AND value_set_display_code = ' ||
                          ':b_value_set_display_code' ||
                          ' AND language = :b_language ' ;

Line: 3608

          l_update_str
        USING
          g_global_val_tbl(1).member_name
        , g_global_val_tbl(1).member_description
        , 'LOAD'
        , g_global_val_tbl(1).member_display_code
        , g_global_val_tbl(1).value_set_display_code
        , g_session_language ;

Line: 3656

          l_update_str := 'UPDATE ' ||
                             g_global_val_tbl(1).intf_member_b_table_name ||
                          ' SET ' ||
                            'status = :b_status' ||
                            ', dimension_group_display_code = ' ||
                            ':b_dimension_group_display_code' ||
                          ' WHERE ' ||
                            g_global_val_tbl(1).member_display_code_col ||
                            ' = :b_member_display_code' ;

Line: 3667

            l_update_str
          USING
            'LOAD'
          , g_global_val_tbl(1).dim_grp_disp_code
          , g_global_val_tbl(1).member_display_code ;

Line: 3699

          l_update_str := 'UPDATE ' ||
                             g_global_val_tbl(1).intf_member_b_table_name ||
                          ' SET ' ||
                            'status = :b_status' ||
                          ' WHERE ' ||
                            g_global_val_tbl(1).member_display_code_col ||
                            ' = :b_member_display_code' ;

Line: 3708

            l_update_str
          USING
            'LOAD'
          , g_global_val_tbl(1).member_display_code ;

Line: 3741

        l_update_str := 'UPDATE ' ||
                           g_global_val_tbl(1).intf_member_tl_table_name ||
                        ' SET ' ||
                        g_global_val_tbl(1).member_name_col ||
                        ' = :b_member_name ' ||
                        ', description   = :b_description ' ||
                        ', status        = :b_status ' ||
                        ' WHERE ' ||
                          g_global_val_tbl(1).member_display_code_col ||
                          ' = :b_member_display_code' ||
                          ' AND language = :b_language ' ;

Line: 3754

          l_update_str
        USING
          g_global_val_tbl(1).member_name
        , g_global_val_tbl(1).member_description
        , 'LOAD'
        , g_global_val_tbl(1).member_display_code
        , g_session_language ;

Line: 3802

  l_update_str         VARCHAR2(4000) := NULL ;

Line: 3834

    l_curr_activity := 'Not a time dimension. Insert in member b table' ;

Line: 3857

    l_b_table_str := 'INSERT INTO ' ||
                        g_global_val_tbl(1).intf_member_b_table_name ||
                        '( dimension_varchar_label' ||
                        ', member_code' ||
                        ', status' ||
                        ')' ||
                     'VALUES' ||
                     '( :b_dim_varchar_label' ||
                     ', :b_member_code' ||
                     ', :b_status' ||
                     ')' ;

Line: 3882

        l_update_str := 'UPDATE ' ||
                           g_global_val_tbl(1).intf_member_b_table_name ||
                        ' SET ' ||
                          'status = :b_status' ||
                        ' WHERE ' ||
                        '   dimension_varchar_label' ||
                          ' = :b_dimension_varchar_label' ||
                          ' AND member_code = ' ||
                          ':b_member_code' ;

Line: 3893

          l_update_str
        USING
          'LOAD'
        , g_global_val_tbl(1).dimension_varchar_label
        , g_global_val_tbl(1).member_display_code ;

Line: 3904

      l_curr_activity := 'Insert in member b table done, do it for TL table' ;

Line: 3917

    l_tl_table_str := 'INSERT INTO ' ||
                         g_global_val_tbl(1).intf_member_tl_table_name ||
                         '( dimension_varchar_label' ||
                         ', member_code' ||
                         ', language' ||
                         ', member_name' ||
                         ', description' ||
                         ', status' ||
                         ')' ||
                      'VALUES' ||
                      '( :b_dimension_varchar_label' ||
                      ', :b_member_code' ||
                      ', :b_language' ||
                      ', :b_member_name' ||
                      ', :b_description' ||
                      ', :b_status' ||
                      ')' ;

Line: 3951

        l_update_str := 'UPDATE ' ||
                           g_global_val_tbl(1).intf_member_tl_table_name ||
                        ' SET ' ||
                          'status = :b_status' ||
                          ', description = :b_description' ||
                          ', member_name' ||
                          ' = :b_member_name ' ||
                        ' WHERE ' ||
                          '  dimension_varchar_label' ||
                          '    = :b_dimension_varchar_label' ||
                          ' AND member_code = :b_member_code ' ||
                          ' AND language = :b_language';

Line: 3966

          EXECUTE IMMEDIATE --Update#1
            l_update_str
          USING
            'LOAD'
          , g_global_val_tbl(1).member_description
          , g_global_val_tbl(1).member_name
          , g_global_val_tbl(1).dimension_varchar_label
          , g_global_val_tbl(1).member_display_code
          , g_session_language ;

Line: 4058

  l_update_str                VARCHAR2(4000) := NULL ;

Line: 4087

  SELECT
    Attr.attribute_dimension_id
  , AttrVer.version_display_code
  , xDim.value_set_required_flag
  FROM
    fem_dim_attributes_b Attr
  , fem_dim_attr_versions_b AttrVer
  , fem_xdim_dimensions xDim
  WHERE
    Attr.dimension_id                = dim_id
    AND Attr.attribute_varchar_label = attr_label
    AND Attr.attribute_id            = AttrVer.attribute_id
    AND AttrVer.default_version_flag = requied_flag
    AND Attr.attribute_dimension_id  = xDim.dimension_id(+) ; -- ** --

Line: 4108

  SELECT
    dimattr.attribute_id
  , dimattr.attribute_required_flag
  FROM
    fem_dim_attributes_b dimattr
  WHERE
    dimattr.attribute_varchar_label = attr_label
    AND dimattr.dimension_id        = dim_id ;

Line: 4124

  SELECT
    1
  FROM
    fem_dim_attr_grps    attrgrp
  , fem_dimension_grps_b dimgrp
  WHERE
    dimgrp.dimension_group_display_code = dim_grp_disp_code
    AND attrgrp.dimension_group_id      = dimgrp.dimension_group_id
    AND attrgrp.attribute_id            = attr_id ;

Line: 4144

  SELECT
    1
  FROM
    fem_dim_attr_grps attrgrp
  WHERE
    attrgrp.attribute_id = attr_id ;

Line: 4296

    SELECT
      ValSet.value_set_display_code
    INTO
      l_attr_asgn_vs_disp_code
    FROM
      fem_value_sets_b ValSet
    WHERE
      ValSet.value_set_id = l_attr_value_set_id ;

Line: 4339

    l_b_table_str := 'INSERT INTO ' ||
                        g_global_val_tbl(1).intf_member_b_table_name ||
                        '( cal_period_end_date' ||
                        ', cal_period_number' ||
                        ', status' ||
                        ', dimension_group_display_code' ||
                        ', calendar_display_code' ||
                        ')' ||
                     'VALUES' ||
                     '( :b_cal_period_end_date' ||
                     ', :b_cal_period_number' ||
                     ', :b_status' ||
                     ', :b_dimension_group_display_code' ||
                     ', :b_calendar_display_code' ||
                     ')' ;

Line: 4371

        l_update_str := 'UPDATE ' ||
                          g_global_val_tbl(1).intf_member_b_table_name ||
                        ' SET ' ||
                        'status = :b_status ' ||
                        'WHERE ' ||
                        'calendar_display_code = :b_calendar_display_code ' ||
                        ' AND dimension_group_display_code = ' ||
                        ' :b_dimension_group_display_code AND ' ||
                        'cal_period_end_date = :b_cal_period_end_date AND ' ||
                        'cal_period_number = :b_cal_period_number' ;

Line: 4383

          l_update_str
        USING
          'LOAD'
        , g_global_val_tbl(1).calendar_display_code
        , g_global_val_tbl(1).dim_grp_disp_code
        , g_date_end_date_value
        , g_cal_pr_num_col_name_value ;

Line: 4396

      l_curr_activity := 'Insert in member b table done. Do it for TL table' ;

Line: 4407

    l_tl_table_str := 'INSERT INTO ' ||
                         g_global_val_tbl(1).intf_member_tl_table_name ||
                         '( cal_period_end_date' ||
                         ', cal_period_number' ||
                         ', language'||
                         ', cal_period_name' ||
                         ', description' ||
                         ', status' ||
                         ', calendar_display_code' ||
                         ', dimension_group_display_code' ||
                         ')' ||
                      'VALUES' ||
                      '( :b_cal_period_end_date' ||
                      ', :b_cal_period_number' ||
                      ', :b_language' ||
                      ', :b_cal_period_name' ||
                      ', :b_description'||
                      ', :b_status' ||
                      ', :b_calendar_display_code' ||
                      ', :b_dimension_group_display_code' ||
                      ')' ;

Line: 4448

        l_update_str := 'UPDATE ' ||
                          g_global_val_tbl(1).intf_member_tl_table_name ||
                        ' SET ' ||
                        'cal_period_name = :b_cal_period_name ' ||
                        ', description   = :b_description ' ||
                        ', status        = :b_status ' ||
                        'WHERE ' ||
                        'calendar_display_code = :b_calendar_display_code ' ||
                        ' AND dimension_group_display_code = ' ||
                        ' :b_dimension_group_display_code AND ' ||
                        'cal_period_end_date = :b_cal_period_end_date AND ' ||
                        'cal_period_number   = :b_cal_period_number AND ' ||
                        'language            = :b_language' ;

Line: 4465

            l_update_str
          USING
            g_global_val_tbl(1).member_name
          , g_global_val_tbl(1).member_description
          , 'LOAD'
          , g_global_val_tbl(1).calendar_display_code
          , g_global_val_tbl(1).dim_grp_disp_code
          , g_date_end_date_value
          , g_cal_pr_num_col_name_value
          , g_session_language ;

Line: 4497

      l_curr_activity := 'Insert in TL done. For time attrib intf table' ;

Line: 4514

      l_curr_activity := 'Insert in TL done. For non-time attrib intf table' ;

Line: 4608

            l_update_str := 'UPDATE ' ||
                            g_global_val_tbl(1).intf_attribute_table_name ||
                            ' SET ' ||
                            '   status = :b_status ' ||
                            ' , attribute_assign_value ' ||
                            '     = :b_attrib_asgn_value ' ||
                            'WHERE ' ||
                              g_global_val_tbl(1).member_display_code_col ||
                            ' = :b_member_disp_code ' ||
                            'AND value_set_display_code ' ||
                            ' = :b_vs_disp_code ' ||
                            'AND attribute_varchar_label ' ||
                            ' = :b_attrib_varchar_label '||
                            'AND version_display_code ' ||
                            ' = :b_ver_disp_code ' ||
                            'AND NVL(attr_assign_vs_display_code, ''XYZ'') ' ||
                            ' = NVL(:b_asgn_vs_disp_code, ''XYZ'')' ;

Line: 4630

              l_update_str
            USING
              'LOAD'
            , p_attribute_value
            , g_global_val_tbl(1).member_display_code
            , g_global_val_tbl(1).value_set_display_code
            , l_attribute_varchar_label
            , g_version_display_code(g_version_display_code.COUNT)
            , l_attr_asgn_vs_disp_code ;

Line: 4644

              l_attr_t_str := 'INSERT INTO ' ||
                               g_global_val_tbl(1).intf_attribute_table_name ||
                               '( ' ||
                               g_global_val_tbl(1).member_display_code_col ||
                               ', attribute_varchar_label' ||
                               ', value_set_display_code' ||
                               ', attribute_assign_value' ||
                               ', attr_assign_vs_display_code' ||
                               ', status' ||
                               ', version_display_code' ||
                              ')' ||
                              'VALUES' ||
                              '( :b_member_display_code' ||
                              ', :b_attribute_varchar_label' ||
                              ', :b_value_set_display_code' ||
                              ', :b_attribute_assign_value' ||
                              ', :b_attr_assign_vs_display_code' ||
                              ', :b_status' ||
                              ', :b_version_display_code' ||
                              ')' ;

Line: 4683

            l_update_str := 'UPDATE ' ||
                             g_global_val_tbl(1).intf_attribute_table_name ||
                            ' SET ' ||
                            '   status = :b_status ' ||
                            ' , attribute_assign_value ' ||
                            '    = :b_attrib_asgn_value ' ||
                            'WHERE ' ||
                              g_global_val_tbl(1).member_display_code_col ||
                            ' = :b_member_disp_code AND ' ||
                            'attribute_varchar_label ' ||
                            ' = :b_attrib_varchar_label '||
                            'AND version_display_code ' ||
                            ' = :b_ver_disp_code ' ||
                            'AND NVL(attr_assign_vs_display_code, ''XYZ'') ' ||
                            ' = NVL(:b_asgn_vs_disp_code, ''XYZ'') ' ;

Line: 4700

              l_update_str
            USING
              'LOAD'
            , p_attribute_value
            , g_global_val_tbl(1).member_display_code
            , l_attribute_varchar_label
            , g_version_display_code(g_version_display_code.COUNT)
            , l_attr_asgn_vs_disp_code ;

Line: 4713

              l_attr_t_str := 'INSERT INTO ' ||
                                 g_global_val_tbl(1).intf_attribute_table_name ||
                              '( ' ||
                               g_global_val_tbl(1).member_display_code_col ||
                               ', attribute_varchar_label' ||
                               ', attribute_assign_value' ||
                               ', attr_assign_vs_display_code' ||
                               ', status' ||
                               ', version_display_code' ||
                               ')' ||
                               'VALUES' ||
                               '( :b_member_display_code' ||
                               ', :b_attribute_varchar_label' ||
                               ', :b_attribute_assign_value' ||
                               ', :b_attr_assign_vs_display_code' ||
                               ', :b_status' ||
                               ', :b_version_display_code' ||
                               ')' ;

Line: 4748

          l_update_str := 'UPDATE ' ||
                          'FEM_SHARED_ATTR_T' ||
                          ' SET ' ||
                          '   status = :b_status ' ||
                          ' , attribute_assign_value ' ||
                          '     = :b_attribute_assign_value ' ||
                          'WHERE ' ||
                          '  dimension_varchar_label         = ' ||
                          ':b_dimension_varchar_label' ||
                          '  AND member_code                 = ' ||
                          ':b_member_code' ||
                          '  AND attribute_varchar_label     = ' ||
                          ':b_attribute_varchar_label '||
                          '  AND version_display_code        = ' ||
                          ':b_version_display_code' ||
                          '  AND NVL(attr_assign_vs_display_code, ''XYZ'')' ||
                          '  = NVL(:b_attr_assign_vs_display_code, ''XYZ'') ' ;

Line: 4768

            l_update_str
          USING
            'LOAD'
          , p_attribute_value
          , g_global_val_tbl(1).dimension_varchar_label
          , g_global_val_tbl(1).member_display_code
          , l_attribute_varchar_label
          , g_version_display_code(g_version_display_code.COUNT)
          , l_attr_asgn_vs_disp_code ;

Line: 4782

            l_attr_t_str := 'INSERT INTO ' ||
                            'FEM_SHARED_ATTR_T' ||
                            '( dimension_varchar_label' ||
                            ', member_code' ||
                            ', attribute_varchar_label' ||
                            ', version_display_code' ||
                            ', attribute_assign_value' ||
                            ', attr_assign_vs_display_code' ||
                            ', status' ||
                            ')' ||
                            'VALUES' ||
                            '( :dimension_varchar_label' ||
                            ', :b_member_code' ||
                            ', :b_attribute_varchar_label' ||
                            ', :b_version_display_code' ||
                            ', :b_attribute_assign_value' ||
                            ', :b_attr_assign_vs_display_code' ||
                            ', :b_status' ||
                            ')' ;

Line: 4935

  l_update_str               VARCHAR2(4000) := NULL ;

Line: 4960

  SELECT
    VS.value_set_display_code
  FROM
    fem_Value_Sets_vl VS
  WHERE
    VS.value_set_id = ( FEM_DIMENSION_UTIL_PKG.Dimension_Value_Set_Id
                        ( dim_id -- p_dimension_id
                        , ledger -- p_ledger_id
                        )
                      ) ;

Line: 4977

  SELECT
    dimattr.attribute_id
    ,dimattr.attribute_required_flag, dimattr.attribute_data_type_code
  FROM
    fem_dim_attributes_b dimattr
  WHERE
    dimattr.attribute_varchar_label = attr_label
    AND dimattr.dimension_id        = dim_id ;

Line: 4993

  SELECT
    1
  FROM
    fem_dim_attr_grps    attrgrp
  , fem_dimension_grps_b dimgrp
  WHERE
    dimgrp.dimension_group_display_code = dim_grp_disp_code
    AND attrgrp.dimension_group_id      = dimgrp.dimension_group_id
    AND attrgrp.attribute_id            = attr_id ;

Line: 5059

  g_not_null_attr_val_tbl.DELETE ;

Line: 5904

        l_update_str := 'UPDATE ' ||
                           g_global_val_tbl(1).intf_attribute_table_name ||
                        ' SET ' ||
                        '   status = :b_status ' ||
                        ' , attribute_assign_value ' ||
                        '     = :b_attrib_asgn_value ' ||
                        'WHERE ' ||
                        'cal_period_end_date ' ||
                        ' = :b_cal_period_end_date AND ' ||
                        'cal_period_number  = :b_cal_period_number AND ' ||
                        'calendar_display_code = ' ||
                        ' :b_calendar_display_code '||
                        'AND dimension_group_display_code = ' ||
                        ' :b_dimension_group_display_code AND ' ||
                        'attribute_varchar_label  = ' ||
                        ' :b_attribute_varchar_label ' ||
                        'AND version_display_code = ' ||
                        ' :b_version_display_code AND ' ||
                        'NVL(attr_assign_vs_display_code, ''XYZ'') = ' ||
                        ' NVL(:b_attr_assign_vs_display_code, ''XYZ'') ' ;

Line: 5926

          l_update_str
        USING
          'LOAD'
        , l_curr_attr_value
        , l_cal_period_end_date
        , g_cal_pr_num_col_name_value
        , g_global_val_tbl(1).calendar_display_code
        , g_global_val_tbl(1).dim_grp_disp_code
        , l_curr_attr_label
        , g_version_display_code(l_att_indx)
        , g_attribute_vs_display_code(l_att_indx) ;

Line: 5942

          l_attr_t_str := 'INSERT INTO ' ||
                           g_global_val_tbl(1).intf_attribute_table_name ||
                           '( cal_period_end_date ' ||
                           ', cal_period_number' ||
                           ', attribute_varchar_label' ||
                           ', attribute_assign_value' ||
                           ', attr_assign_vs_display_code' ||
                           ', status' ||
                           ', calendar_display_code' ||
                           ', dimension_group_display_code' ||
                           ', version_display_code' ||
                           ')' ||
                          'VALUES' ||
                          '( :b_cal_period_end_date' ||
                          ', :b_cal_period_number' ||
                          ', :b_attribute_varchar_label' ||
                          ', :b_attribute_assign_value' ||
                          ', :b_attr_assign_vs_display_code' ||
                          ', :b_status' ||
                          ', :b_calendar_display_code' ||
                          ', :b_dimension_group_display_code' ||
                          ', :b_version_display_code' ||
                          ')' ;

Line: 6113

PROCEDURE Delete_Fem_Webadi_Seed (
  p_api_version                  IN           NUMBER  ,
  p_init_msg_list                IN           VARCHAR2,
  p_commit                       IN           VARCHAR2,
  x_return_status                OUT NOCOPY   VARCHAR2,
  x_msg_count                    OUT NOCOPY   NUMBER  ,
  x_msg_data                     OUT NOCOPY   VARCHAR2
) IS

l_api_name    CONSTANT         VARCHAR2(30) := 'Delete_Fem_Webadi_Seed';

Line: 6126

SELECT layout_code FROM bne_layouts_b WHERE integrator_code = 'FEM_DIM_MEMBER_INTG';

Line: 6129

SELECT interface_code FROM bne_interfaces_b WHERE integrator_code = 'FEM_DIM_MEMBER_INTG'
 AND interface_code <> 'FEM_DIM_MEMBER_HEADER_INTF';

Line: 6134

SAVEPOINT Delete_Fem_Webadi_Seed_Pvt;

Line: 6158

 DELETE FROM bne_layout_cols WHERE layout_code = dimintg_lyts_csr_rec.layout_code
  AND interface_code <> 'FEM_DIM_MEMBER_HEADER_INTF';

Line: 6160

 DELETE FROM bne_layout_blocks_b WHERE layout_code = dimintg_lyts_csr_rec.layout_code;

Line: 6161

 DELETE FROM bne_layout_blocks_tl WHERE layout_code = dimintg_lyts_csr_rec.layout_code;

Line: 6166

          ,p_msg_text => 'Deleted...');

Line: 6175

 DELETE FROM  bne_layouts_tl WHERE layout_code = dimintg_lyts_csr_rec.layout_code;

Line: 6178

DELETE FROM bne_layouts_b WHERE integrator_code = 'FEM_DIM_MEMBER_INTG';

Line: 6182

          ,p_msg_text => 'Deleted...');

Line: 6190

DELETE FROM bne_interface_cols_b WHERE interface_code = dimintg_intfs_csr_rec.interface_code;

Line: 6191

DELETE FROM bne_interface_cols_tl WHERE interface_code = dimintg_intfs_csr_rec.interface_code;

Line: 6196

          ,p_msg_text => 'Deleted...');

Line: 6204

DELETE FROM bne_interfaces_tl WHERE interface_code = dimintg_intfs_csr_rec.interface_code;

Line: 6207

DELETE FROM bne_interfaces_b WHERE integrator_code = 'FEM_DIM_MEMBER_INTG'
 AND interface_code <> 'FEM_DIM_MEMBER_HEADER_INTF';

Line: 6212

          ,p_msg_text => 'Deleted...');

Line: 6218

DELETE FROM fem_webadi_dim_attr_maps;

Line: 6222

          ,p_msg_text => 'Deleted...');

Line: 6233

    ROLLBACK TO Delete_Fem_Webadi_Seed_pvt ;

Line: 6240

    ROLLBACK TO Delete_Fem_Webadi_Seed_Pvt ;

Line: 6247

    ROLLBACK TO Delete_Fem_Webadi_Seed_Pvt;

Line: 6258

END Delete_Fem_Webadi_Seed;