APPS.FEM_MAPPING_PREVIEW_UTIL_PKG SQL Statements

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

Line: 40

PROCEDURE GetSelectClause(
  p_preview_obj_def_id     IN NUMBER,
  p_preview_obj_id         IN NUMBER,
  p_request_id             IN NUMBER,
  p_preview_row_group      IN VARCHAR2,
  p_fact_table_name        IN VARCHAR2,
  x_select_clause          OUT NOCOPY VARCHAR2);

Line: 126

PROCEDURE UpdatePreviewStats(
  p_preview_obj_def_id      IN NUMBER,
  p_preview_row_group       IN VARCHAR2,
  p_temp_table_name         IN VARCHAR2,
  p_map_table_type          IN VARCHAR2,
  p_map_obj_def_id          IN NUMBER,
  p_ledger_id               IN NUMBER,
  p_cal_period_id           IN NUMBER);

Line: 223

    SELECT request_id
    FROM fem_pl_object_executions
    WHERE exec_object_definition_id = cv_obj_def_id
    UNION
    SELECT cv_request_id
    FROM dual
    ORDER BY request_id;

Line: 278

    SELECT request_id
    INTO v_request_id
    FROM fem_alloc_previews
    WHERE preview_obj_def_id = p_preview_obj_def_id;

Line: 330

  DELETE FROM fem_alloc_preview_stats
  WHERE preview_obj_def_id = p_preview_obj_def_id;

Line: 337

      p_msg_text => 'Deleted '||SQL%ROWCOUNT
                  ||' rows from FEM_ALLOC_PREVIEW_STATS');

Line: 341

  DELETE FROM fem_alloc_preview_maps
  WHERE preview_obj_def_id = p_preview_obj_def_id;

Line: 348

      p_msg_text => 'Deleted '||SQL%ROWCOUNT
                  ||' rows from FEM_ALLOC_PREVIEW_MAPS');

Line: 460

    SELECT f.table_name, f.function_cd, f.sub_object_id,
           m.preview_row_group, m.preview_row_group_display_seq
    FROM fem_alloc_br_formula f, fem_function_cd_mapping m
    WHERE f.function_cd = m.function_cd
    AND f.object_definition_id = cv_obj_def_id
    AND nvl(f.enable_flg,'Y') = 'Y'
    ORDER BY m.preview_row_group_process_seq;

Line: 522

  SELECT object_definition_id, object_id
  INTO v_map_obj_def_id, v_map_obj_id
  FROM fem_objdef_helper_rules
  WHERE helper_obj_def_id = p_preview_obj_def_id
  AND helper_object_type_code = 'MAPPING_PREVIEW';

Line: 540

  SELECT map_rule_type_code
  INTO v_map_rule_type
  FROM fem_alloc_br_objects
  WHERE map_rule_object_id = v_map_obj_id;

Line: 601

        SELECT object_definition_id
        INTO v_stat_obj_def_id
        FROM fem_object_definition_b
        WHERE object_id = row_group.sub_object_id;

Line: 606

        SELECT stat_lookup_table
        INTO v_fact_table_name
        FROM fem_stat_lookups
        WHERE stat_lookup_obj_def_id = v_stat_obj_def_id;

Line: 771

    SELECT fact_table_name, temp_table_name, preview_row_group
    FROM fem_alloc_preview_stats
    WHERE preview_obj_def_id = cv_preview_obj_def_id
    ORDER BY preview_row_group_display_seq;

Line: 822

  SELECT object_id, object_definition_id
  INTO v_map_obj_id, v_map_obj_def_id
  FROM fem_objdef_helper_rules
  WHERE helper_obj_def_id = p_preview_obj_def_id
  AND helper_object_type_code = 'MAPPING_PREVIEW';

Line: 840

  SELECT ledger_id, cal_period_id
  INTO v_ledger_id, v_cal_period_id
  FROM fem_alloc_previews
  WHERE preview_obj_def_id = p_preview_obj_def_id;

Line: 859

    UpdatePreviewStats(
      p_preview_obj_def_id      => p_preview_obj_def_id,
      p_preview_row_group       => row_group.preview_row_group,
      p_temp_table_name         => row_group.temp_table_name,
      p_map_table_type          => v_map_table_type,
      p_map_obj_def_id          => v_map_obj_def_id,
      p_ledger_id               => v_ledger_id,
      p_cal_period_id           => v_cal_period_id);

Line: 966

  v_select_clause      VARCHAR2(16383);

Line: 976

    SELECT c.column_name
    FROM dba_ind_columns c, user_synonyms s, fem_tables_b t
    WHERE t.table_name = cv_table_name
    AND t.table_name = s.synonym_name
    AND s.table_name = c.table_name
    AND t.proc_key_index_owner = c.table_owner
    AND t.proc_key_index_name = c.index_name
    AND t.proc_key_index_owner = c.index_owner
    ORDER BY column_position;

Line: 1044

  GetSelectClause(
    p_preview_obj_def_id    => p_preview_obj_def_id,
    p_preview_obj_id        => p_preview_obj_id,
    p_request_id            => p_request_id,
    p_preview_row_group     => p_preview_row_group,
    p_fact_table_name       => p_fact_table_name,
    x_select_clause         => v_select_clause);

Line: 1082

    p_table_def             => 'AS '||v_select_clause||' '
                             ||v_from_clause||' '||v_where_clause);

Line: 1230

    v_sql := 'SELECT count(*)'
           ||' FROM '||p_fact_table_name||' '||G_FACT_ALIAS;

Line: 1242

  INSERT INTO fem_alloc_preview_stats (
    preview_obj_def_id, preview_row_group, preview_row_group_display_seq,
    fact_table_name, temp_table_name, estimated_rows, created_by_request_id,
    created_by_object_id, last_updated_by_request_id, last_updated_by_object_id)
  VALUES (
    p_preview_obj_def_id, p_preview_row_group, p_preview_display_seq,
    p_fact_table_name, p_temp_table_name, v_row_count, p_request_id,
    p_preview_obj_id, p_request_id, p_preview_obj_id);

Line: 1298

  INSERT INTO fem_alloc_preview_maps(
      preview_obj_def_id, preview_row_group, dim_member_column_name,
      dim_name_column_name, created_by_request_id, created_by_object_id,
      last_updated_by_request_id, last_updated_by_object_id)
    SELECT p_preview_obj_def_id, p_preview_row_group, tc.column_name,
           substr('FEM'||rownum||'_'||p_request_id, 1, 30),
           p_request_id, p_preview_obj_id,
           p_request_id, p_preview_obj_id
    FROM fem_tab_columns_v tc
    WHERE tc.table_name = p_fact_table_name
    AND tc.fem_data_type_code = 'DIMENSION'
    AND tc.column_name IN
      (SELECT tcp.column_name
       FROM fem_tab_column_prop tcp
       WHERE tcp.table_name = tc.table_name
       AND tcp.column_property_code IN
         ('MAPPING_UI_INPUT', 'PROCESSING_KEY'))
    AND tc.column_name <> 'LEDGER_ID';

Line: 1341

PROCEDURE GetSelectClause(
  p_preview_obj_def_id     IN NUMBER,
  p_preview_obj_id         IN NUMBER,
  p_request_id             IN NUMBER,
  p_preview_row_group      IN VARCHAR2,
  p_fact_table_name        IN VARCHAR2,
  x_select_clause          OUT NOCOPY VARCHAR2
)
-------------------------------------------------------------------------------
IS
--
  C_MODULE            CONSTANT FND_LOG_MESSAGES.module%TYPE :=
     'fem.plsql.fem_mapping_preview_util_pkg.GetSelectClause';

Line: 1357

    SELECT dim_name_column_name
    FROM fem_alloc_preview_maps
    WHERE preview_obj_def_id = cv_preview_obj_def_id
    AND preview_row_group = cv_preview_row_group;

Line: 1374

  x_select_clause := 'SELECT '||G_FACT_ALIAS||'.*';

Line: 1382

    x_select_clause := x_select_clause||', '||G_DIM_ALIAS||'.'
                     ||'template_dim_name AS '
                     ||dims.dim_name_column_name;

Line: 1393

      p_msg_text => 'x_select_clause = '||x_select_clause);

Line: 1403

END GetSelectClause;

Line: 1432

    SELECT decode(count(*),0,'F','T')
    FROM fem_table_class_assignmt_v
    WHERE table_name = cv_table_name
    AND substr(table_classification_code,-6) = 'LEDGER';

Line: 1439

    SELECT decode(count(*),0,'F','T')
    FROM fem_table_class_assignmt_v
    WHERE table_name = cv_table_name
    AND table_classification_code IN ('ACCOUNT_PROFITABILITY',
                                      'TRANSACTION_PROFITABILITY');

Line: 1556

    SELECT s.temp_table_name, s.fact_table_name
    FROM fem_alloc_preview_stats s
    WHERE s.preview_obj_def_id = cv_preview_obj_def_id
    AND s.preview_row_group = cv_preview_row_group;

Line: 1752

    SELECT stat_lookup_tbl_col, relational_operand, value
    FROM fem_stat_lookup_rel s
    WHERE s.stat_lookup_obj_def_id = cv_stat_obj_def_id
    AND s.value IS NOT NULL;

Line: 1767

  SELECT FND_DATE.Date_To_Canonical(effective_date),
         ledger_id, cal_period_id, dsg_obj_def_id, query_row_limit,
         decode(p_preview_row_group, G_SOURCE, source_condition_obj_id,
                                               driver_condition_obj_id)
  INTO   v_effective_date, v_ledger_id, v_cal_period_id, v_dsg_obj_def_id,
         v_query_row_limit, v_preview_cond_obj_id
  FROM   fem_alloc_previews
  WHERE  preview_obj_def_id = p_preview_obj_def_id;

Line: 1844

    SELECT object_definition_id
    INTO v_stat_obj_def_id
    FROM fem_object_definition_b
    WHERE object_id = p_sub_obj_id;

Line: 1865

    SELECT condition_obj_def_id
    INTO v_stat_cond_obj_def_id
    FROM fem_stat_lookups
    WHERE stat_lookup_obj_def_id = v_stat_obj_def_id;

Line: 2026

    SELECT alloc_dim_col_name, nvl(to_char(dimension_value),
                                   dimension_value_char) dim_value
    FROM fem_alloc_br_dimensions
    WHERE object_definition_id = cv_map_obj_def_id
    AND function_cd = cv_function_cd
    AND alloc_dim_usage_code = 'VALUE';

Line: 2035

    SELECT o.column_name output_col, m.column_name match_col
    FROM fem_tab_column_prop o, fem_tab_columns_v m
    WHERE o.table_name = cv_fact_table
    AND o.column_property_code = 'PROCESSING_KEY'
    AND m.table_name = cv_match_fact_table
    AND o.column_name = m.column_name
    AND o.column_name NOT IN
      (SELECT alloc_dim_col_name
       FROM fem_alloc_br_dimensions
       WHERE object_definition_id = cv_map_obj_def_id
       AND function_cd = cv_function_cd
       AND alloc_dim_usage_code = 'VALUE');

Line: 2118

                      ||' IN (SELECT '||v_match_pk_sql||' FROM '
                      ||v_output_match_temp_table||' '||G_MATCH_ALIAS||')';

Line: 2245

PROCEDURE UpdatePreviewStats(
  p_preview_obj_def_id      IN NUMBER,
  p_preview_row_group       IN VARCHAR2,
  p_temp_table_name         IN VARCHAR2,
  p_map_table_type          IN VARCHAR2,
  p_map_obj_def_id          IN NUMBER,
  p_ledger_id               IN NUMBER,
  p_cal_period_id           IN NUMBER
)
-------------------------------------------------------------------------------
IS
--
  C_MODULE                  CONSTANT FND_LOG_MESSAGES.module%TYPE :=
     'fem.plsql.fem_mapping_preview_util_pkg.UpdatePreviewStats';

Line: 2287

  UPDATE fem_alloc_preview_stats
  SET preview_amount_total = v_amount_total,
      amount_currency_code = v_functional_currency,
      preview_rows = v_row_count
  WHERE preview_obj_def_id = p_preview_obj_def_id
  AND preview_row_group = p_preview_row_group;

Line: 2301

END UpdatePreviewStats;

Line: 2371

  SELECT dimension_id
  INTO v_dim_id
  FROM fem_dimensions_b
  WHERE dimension_varchar_label = 'LEDGER';

Line: 2396

  SELECT dim_attribute_varchar_member
  INTO x_functional_currency
  FROM fem_ledgers_attr
  WHERE attribute_id  = v_dim_attr_id
  AND version_id    = v_dim_attr_ver_id
  AND ledger_id     = p_ledger_id;

Line: 2417

  SELECT dimension_id
  INTO v_dim_id
  FROM fem_dimensions_b
  WHERE dimension_varchar_label = 'CAL_PERIOD';

Line: 2442

  SELECT date_assign_value
  INTO v_cal_per_end_date
  FROM fem_cal_periods_attr
  WHERE attribute_id  = v_dim_attr_id
  AND version_id    = v_dim_attr_ver_id
  AND cal_period_id = p_cal_period_id;

Line: 2482

    SELECT f.column_name
    INTO v_amount_column
    FROM fem_alloc_br_formula f, fem_function_cd_mapping m
    WHERE f.object_definition_id = p_map_obj_def_id
    AND f.function_cd = m.function_cd
    AND m.preview_row_group = p_preview_row_group;

Line: 2516

  v_sql := 'SELECT SUM('||v_amount_column||'), currency_code'
        ||' FROM '||p_temp_table_name
        ||' GROUP BY currency_code';

Line: 2631

  v_sql := 'SELECT count(*) FROM '||p_temp_table_name;

Line: 2702

    v_sql := 'DELETE FROM '||p_temp_table_name
          ||' WHERE last_updated_by_request_id <> :1'
          ||' AND last_updated_by_object_id <> :2';

Line: 2710

        p_msg_text => 'Delete unprocessed data SQL = '||v_sql);

Line: 2789

    SELECT pm.dim_member_column_name, pm.dim_name_column_name,
           xd.member_name_col, xd.member_vl_object_name, xd.member_col,
           gv.value_set_id
    FROM fem_alloc_preview_maps pm, fem_tab_columns_v tc,
         fem_xdim_dimensions xd, fem_global_vs_combo_defs gv
    WHERE pm.preview_obj_def_id = cv_preview_obj_def_id
      AND pm.preview_row_group = cv_preview_row_group
      AND pm.dim_member_column_name = tc.column_name
      AND tc.table_name = cv_fact_table
      AND xd.dimension_id  = tc.dimension_id
      AND xd.dimension_id  = gv.dimension_id (+)
      AND gv.global_vs_combo_id (+) = cv_global_vs_combo_id;

Line: 2849

           ||'NVL((SELECT '||G_DIM_ALIAS||'.'||dim.member_name_col
           ||' FROM '||dim.member_vl_object_name||' '||G_DIM_ALIAS
           ||' WHERE '||G_DIM_ALIAS||'.'||dim.member_col||'='
           ||G_FACT_ALIAS||'.'||dim.dim_member_column_name;

Line: 2871

    v_sql := 'UPDATE '||p_temp_table_name||' '||G_FACT_ALIAS||' SET '||v_sql;

Line: 2941

    SELECT abd.alloc_dim_col_name, xd.dimension_id,
           decode(xd.member_data_type_code,'NUMBER',abd.dimension_value,
                                           abd.dimension_value_char)
    INTO x_by_dimension_column, x_by_dimension_id, x_by_dimension_value
    FROM fem_alloc_br_dimensions abd, fem_tab_columns_b tc,
         fem_xdim_dimensions xd
    WHERE abd.object_definition_id = p_map_obj_def_id
    AND abd.function_cd = C_BYDIM_FUNCIONCD
    AND tc.table_name = p_fact_table_name
    AND abd.alloc_dim_col_name = tc.column_name
    AND tc.dimension_id = xd.dimension_id;