APPS.FII_FDHM_PKG SQL Statements

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

Line: 13

	SELECT 	parent_flex_value
	FROM	fii_dim_norm_hierarchy
	WHERE	parent_flex_value_set_id = vsid
	AND	child	BETWEEN	child_flex_value_low
			AND	child_flex_value_high
        AND     parent_flex_value_set_id = child_flex_value_set_id;

Line: 57

    SELECT  COUNT(*)
    INTO    range_size
    FROM    FND_FLEX_VALUES
    WHERE   flex_value_set_id = child_value_set_id
    AND     summary_flag = sum_flag
    AND     flex_value BETWEEN range_low AND range_high;

Line: 67

        DELETE  FROM FII_DIM_NORM_HIERARCHY
        WHERE   parent_flex_value_set_id = parent_value_set_id
        AND     child_flex_value_set_id = child_value_set_id
        AND     parent_flex_value = parent
        AND     range_attribute = range_attr
        AND     child_flex_value_low = range_low
        AND     child_flex_value_high = range_high;

Line: 76

            DELETE  FND_FLEX_VALUE_NORM_HIERARCHY
            WHERE   flex_value_set_id = parent_value_set_id
            AND     parent_flex_value = parent
            AND     range_attribute = range_attr
            AND     child_flex_value_low = range_low
            AND     child_flex_value_high = range_high;

Line: 89

        SELECT  MIN(flex_value)
        INTO    new_bound
        FROM    fnd_flex_values
        WHERE   flex_value_set_id = child_value_set_id
        AND     summary_flag = sum_flag
        AND     flex_value > child
        AND     flex_value <= range_high
        ORDER BY flex_value;

Line: 99

            UPDATE  FII_DIM_NORM_HIERARCHY
            SET     child_flex_value_low = new_bound
            WHERE   parent_flex_value_set_id = parent_value_set_id
            AND     child_flex_value_set_id = child_value_set_id
            AND     parent_flex_value = parent
            AND     range_attribute = range_attr
            AND     child_flex_value_low = range_low
            AND     child_flex_value_high = range_high;

Line: 109

               UPDATE  FND_FLEX_VALUE_NORM_HIERARCHY
               SET     child_flex_value_low = new_bound
               WHERE   flex_value_set_id = parent_value_set_id
               AND     parent_flex_value = parent
               AND     range_attribute = range_attr
               AND     child_flex_value_low = range_low
               AND     child_flex_value_high = range_high;

Line: 123

        SELECT  MAX(flex_value)
        INTO    new_bound
        FROM    fnd_flex_values
        WHERE   flex_value_set_id = child_value_set_id
        AND     summary_flag = sum_flag
        AND     flex_value >= range_low
        AND     flex_value < child
        ORDER BY flex_value;

Line: 133

            UPDATE  FII_DIM_NORM_HIERARCHY
            SET     child_flex_value_high = new_bound
            WHERE   parent_flex_value_set_id = parent_value_set_id
            AND     child_flex_value_set_id = child_value_set_id
            AND     parent_flex_value = parent
            AND     range_attribute = range_attr
            AND     child_flex_value_low = range_low
            AND     child_flex_value_high = range_high;

Line: 143

               UPDATE  FND_FLEX_VALUE_NORM_HIERARCHY
               SET     child_flex_value_high = new_bound
               WHERE   flex_value_set_id = parent_value_set_id
               AND     parent_flex_value = parent
               AND     range_attribute = range_attr
               AND     child_flex_value_low = range_low
               AND     child_flex_value_high = range_high;

Line: 158

        DELETE  FROM FII_DIM_NORM_HIERARCHY
        WHERE   parent_flex_value_set_id = parent_value_set_id
        AND     child_flex_value_set_id = child_value_set_id
        AND     parent_flex_value = parent
        AND     range_attribute = range_attr
        AND     child_flex_value_low = range_low
        AND     child_flex_value_high = range_high;

Line: 167

           DELETE  FND_FLEX_VALUE_NORM_HIERARCHY
           WHERE   flex_value_set_id = parent_value_set_id
           AND     parent_flex_value = parent
           AND     range_attribute = range_attr
           AND     child_flex_value_low = range_low
           AND     child_flex_value_high = range_high;

Line: 178

        SELECT  MAX(flex_value)
        INTO    new_bound
        FROM    fnd_flex_values
        WHERE   flex_value_set_id = child_value_set_id
        AND     summary_flag = sum_flag
        AND     flex_value >= range_low
        AND     flex_value < child
        ORDER BY flex_value;

Line: 190

            INSERT INTO FII_DIM_NORM_HIERARCHY
            (   parent_flex_value_set_id,
                child_flex_value_set_id,
                parent_flex_value,
                range_attribute,
                child_flex_value_low,
                child_flex_value_high,
                last_update_date,
                last_updated_by,
                creation_date,
                created_by,
                last_update_login )
            VALUES
            (   parent_value_set_id,
                child_value_set_id,
                parent,
                range_attr,
                range_low,
                new_bound,
                SYSDATE,
                0,
                SYSDATE,
                0,
                fnd_global.login_id);

Line: 216

               INSERT INTO FND_FLEX_VALUE_NORM_HIERARCHY
               (   flex_value_set_id,
                   parent_flex_value,
                   range_attribute,
                   child_flex_value_low,
                   child_flex_value_high,
                   last_update_date,
                   last_updated_by,
                   creation_date,
                   created_by,
                   last_update_login )
               VALUES
               (   parent_value_set_id,
                   parent,
                   range_attr,
                   range_low,
                   new_bound,
                   SYSDATE,
                   0,
                   SYSDATE,
                   0,
                   fnd_global.login_id);

Line: 245

        SELECT  MIN(flex_value)
        INTO    new_bound
        FROM    fnd_flex_values
        WHERE   flex_value_set_id = child_value_set_id
        AND     summary_flag = sum_flag
        AND     flex_value > child
        AND     flex_value <= range_high
        ORDER BY flex_value;

Line: 257

            INSERT INTO FII_DIM_NORM_HIERARCHY
            (   parent_flex_value_set_id,
                child_flex_value_set_id,
                parent_flex_value,
                range_attribute,
                child_flex_value_low,
                child_flex_value_high,
                last_update_date,
                last_updated_by,
                creation_date,
                created_by,
                last_update_login )
            VALUES
            (   parent_value_set_id,
                child_value_set_id,
                parent,
                range_attr,
                new_bound,
                range_high,
                SYSDATE,
                0,
                SYSDATE,
                0,
                fnd_global.login_id );

Line: 283

                INSERT INTO FND_FLEX_VALUE_NORM_HIERARCHY
                (   flex_value_set_id,
                    parent_flex_value,
                    range_attribute,
                    child_flex_value_low,
                    child_flex_value_high,
                    last_update_date,
                    last_updated_by,
                    creation_date,
                    created_by,
                    last_update_login )
                VALUES
                (   parent_value_set_id,
                    parent,
                    range_attr,
                    new_bound,
                    range_high,
                    SYSDATE,
                    0,
                    SYSDATE,
                    0,
                    fnd_global.login_id);

Line: 322

	SELECT 	flex_value
	FROM	fnd_flex_values
	WHERE	flex_value_set_id = vsid
	AND	summary_flag = 'Y'
	AND	flex_value BETWEEN low AND high
    	ORDER	by flex_value;

Line: 359

  SELECT count(*)
  INTO row_count
  FROM fnd_flex_values
  WHERE flex_value_set_id = value_set_id
  AND flex_value = f_value
  AND ((parent_low IS null) OR
       (parent_flex_value_low = parent_low));

Line: 384

    SELECT flex_value_set_id1
    FROM   fii_dim_mapping_rules  r
    WHERE  r.dimension_short_name = dim_short_name
    AND flex_value_set_id1 is not null
    AND    r.chart_of_accounts_id in
         (SELECT chart_of_accounts_id
          FROM   fii_slg_assignments
          WHERE  source_ledger_group_id =source_lgr_group_id)
    UNION
	SELECT master_value_set_id flex_value_set_id1
	FROM   fii_financial_dimensions_v m
	WHERE  m.dimension_short_name = dim_short_name
    ORDER BY flex_value_set_id1;

Line: 454

    SELECT flex_value_set_id1
    FROM   fii_dim_mapping_rules  r
    WHERE  r.dimension_short_name = dim_short_name
    AND flex_value_set_id1 is not null
    AND    r.chart_of_accounts_id in
         (SELECT chart_of_accounts_id
          FROM   fii_slg_assignments
          WHERE  source_ledger_group_id =source_lgr_group_id)
    AND    flex_value_set_id1 < value_set_id
    UNION
    SELECT master_value_set_id flex_value_set_id1
    FROM   fii_financial_dimensions_v m
    WHERE  m.dimension_short_name = dim_short_name
    AND    master_value_set_id < value_set_id
    ORDER BY flex_value_set_id1;

Line: 502

    SELECT flex_value_set_id1
    FROM   fii_dim_mapping_rules  r
    WHERE  r.dimension_short_name = dim_short_name
    AND flex_value_set_id1 is not null
    AND    r.chart_of_accounts_id in
         (SELECT chart_of_accounts_id
          FROM   fii_slg_assignments
          WHERE  source_ledger_group_id =source_lgr_group_id)
    UNION
    SELECT master_value_set_id flex_value_set_id1
    FROM   fii_financial_dimensions_v m
    WHERE  m.dimension_short_name = dim_short_name
    ORDER BY flex_value_set_id1;

Line: 553

PROCEDURE insert_dim_value_sets (dim_short_name VARCHAR2,  source_lgr_group_id NUMBER)
IS
BEGIN
   INSERT INTO FII_DIM_NORM_HIERARCHY
              (parent_flex_value_set_id,
               child_flex_value_set_id,
               parent_flex_value,
               range_attribute,
               child_flex_value_low,
               child_flex_value_high,
               last_update_date,
               last_updated_by,
               creation_date,
               created_by,
               last_update_login )
/*
    SELECT     distinct flex_value_set_id,
               flex_value_set_id,
               parent_flex_value,
               range_attribute,
               child_flex_value_low,
               child_flex_value_high,
               last_update_date,
               last_updated_by,
               creation_date,
               created_by,
               last_update_login
     FROM      FND_FLEX_VALUE_NORM_HIERARCHY
     WHERE     flex_value_set_id in
               (SELECT r.flex_value_set_id1
                FROM   fii_dim_mapping_rules  r
                WHERE  r.dimension_short_name = dim_short_name
                AND    r.chart_of_accounts_id in
                      (SELECT chart_of_accounts_id
                       FROM   fii_slg_assignments
                       WHERE  source_ledger_group_id =source_lgr_group_id))
        OR      flex_value_set_id =
                      (SELECT master_value_set_id
                       FROM   fii_financial_dimensions_v m
                       WHERE  m.dimension_short_name= dim_short_name);

Line: 594

    SELECT     flex_value_set_id,
               flex_value_set_id,
               parent_flex_value,
               range_attribute,
               child_flex_value_low,
               child_flex_value_high,
               last_update_date,
               last_updated_by,
               creation_date,
               created_by,
               last_update_login
     FROM      FND_FLEX_VALUE_NORM_HIERARCHY,
               (
                 SELECT  r.flex_value_set_id1 vs_id
                   FROM  fii_dim_mapping_rules r,
                         (SELECT distinct chart_of_accounts_id
                            FROM fii_slg_assignments
                           WHERE source_ledger_group_id = source_lgr_group_id) s
                  WHERE  r.dimension_short_name = dim_short_name
                    AND  r.chart_of_accounts_id = s.chart_of_accounts_id
                 union
                 SELECT  master_value_set_id vs_id
                   FROM  fii_financial_dimensions m
                  WHERE  m.dimension_short_name = dim_short_name
               )
    WHERE     flex_value_set_id = vs_id;

Line: 621

END insert_dim_value_sets;

Line: 635

    SELECT flex_value_set_id1
    FROM   fii_dim_mapping_rules  r
    WHERE  r.dimension_short_name = dim_short_name
    AND    r.chart_of_accounts_id in
         (SELECT chart_of_accounts_id
          FROM   fii_slg_assignments
          WHERE  source_ledger_group_id =source_lgr_group_id)
    OR    flex_value_set_id1 in
         (SELECT master_value_set_id
          FROM   fii_financial_dimensions_v m
          WHERE  m.dimension_short_name= r.dimension_short_name);

Line: 678

PROCEDURE insert_tl_records is

  cursor installed_lang_cursor is
    select LANGUAGE_CODE from FND_LANGUAGES
    where INSTALLED_FLAG in ('B', 'I');

Line: 695

      insert into FND_FLEX_VALUES_TL (
        FLEX_VALUE_ID,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        DESCRIPTION,
        FLEX_VALUE_MEANING,
        LANGUAGE,
        SOURCE_LANG
     )
        (select
           t1.FLEX_VALUE_ID,
           t1.LAST_UPDATE_DATE,
           t1.LAST_UPDATED_BY,
           t1.CREATION_DATE,
           t1.CREATED_BY,
           t1.LAST_UPDATE_LOGIN,
           t1.DESCRIPTION,
           t1.FLEX_VALUE_MEANING,
           lang_code,
           t1.SOURCE_LANG
         from fnd_flex_values_tl t1 left outer join
	      fnd_flex_values_tl t2
	   on t1.flex_value_id = t2.flex_value_id
	  and t2.language = lang_code
         where t1.language = userenv('LANG')
	 and   t2.flex_value_id is NULL
        );

Line: 729

END insert_tl_records;

Line: 731

PROCEDURE insert_tl_records_for_id(value_id number) is
BEGIN

    insert into FND_FLEX_VALUES_TL(
        FLEX_VALUE_ID,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        DESCRIPTION,
        FLEX_VALUE_MEANING,
        LANGUAGE,
        SOURCE_LANG
    )
    select
        t1.FLEX_VALUE_ID,
        t1.LAST_UPDATE_DATE,
        t1.LAST_UPDATED_BY,
        t1.CREATION_DATE,
        t1.CREATED_BY,
        t1.LAST_UPDATE_LOGIN,
        t1.DESCRIPTION,
        t1.FLEX_VALUE_MEANING,
        ls.LANGUAGE_CODE,
        t1.SOURCE_LANG
    from fnd_flex_values_tl t1, FND_LANGUAGES ls
    where t1.flex_value_id = value_id
    and t1.language = userenv('LANG')
    and ls.INSTALLED_FLAG in ('B', 'I')
    and ls.LANGUAGE_CODE <> userenv('LANG');

Line: 763

END insert_tl_records_for_id;

Line: 765

PROCEDURE delete_tl_records_for_id(value_id number) is
BEGIN

    delete from FND_FLEX_VALUES_TL
    where flex_value_id = value_id
    and language <> userenv('LANG');

Line: 772

END delete_tl_records_for_id;

Line: 779

  SELECT tp.default_value
  FROM   fnd_flex_validation_qualifiers qf,
         fnd_value_attribute_types tp
  WHERE  flex_value_set_id = value_set_id
  AND    qf.value_attribute_type =tp.value_attribute_type
  AND    qf.segment_attribute_type = tp.segment_attribute_type
  AND    qf.id_flex_code = tp.id_flex_code
  AND    qf.id_flex_application_id = tp.application_id
  ORDER BY  qf.assignment_date, qf.value_attribute_type;

Line: 880

PROCEDURE delete_dim_value_sets(dim_short_name VARCHAR2,
                     source_lgr_group_id NUMBER)
IS
BEGIN
   DELETE FROM fii_dim_norm_hierarchy
   WHERE       parent_flex_value_set_id = child_flex_value_set_id
   AND (       child_flex_value_set_id in
        (SELECT r.flex_value_set_id1
         FROM   fii_dim_mapping_rules  r
         WHERE  r.dimension_short_name = dim_short_name
         AND    r.chart_of_accounts_id in
               (SELECT  chart_of_accounts_id
                FROM    fii_slg_assignments
                WHERE   source_ledger_group_id =source_lgr_group_id))
   OR  child_flex_value_set_id =
            (SELECT  master_value_set_id
             FROM    fii_financial_dimensions_v m
             WHERE   m.dimension_short_name= dim_short_name) );

Line: 899

END delete_dim_value_sets;

Line: 902

PROCEDURE insert_fnd_norm_hier_rec(
                        parent          IN      VARCHAR2,
                        child           IN      VARCHAR2,
                        range_attr      IN      VARCHAR2,
                        range_low       IN      VARCHAR2,
                        range_high      IN      VARCHAR2,
                        value_set_id    IN   NUMBER)
IS
BEGIN
               INSERT INTO FND_FLEX_VALUE_NORM_HIERARCHY
               (   flex_value_set_id,
                   parent_flex_value,
                   range_attribute,
                   child_flex_value_low,
                   child_flex_value_high,
                   last_update_date,
                   last_updated_by,
                   creation_date,
                   created_by,
                   last_update_login )
               VALUES
               (   value_set_id,
                   parent,
                   range_attr,
                   range_low,
                   range_high,
                   SYSDATE,
                   0,
                   SYSDATE,
                   0,
                   fnd_global.login_id);

Line: 934

END insert_fnd_norm_hier_rec;

Line: 937

PROCEDURE delete_fnd_norm_hier_rec(
                        parent          IN      VARCHAR2,
                        child           IN      VARCHAR2,
                        range_attr      IN      VARCHAR2,
                        range_low       IN      VARCHAR2,
                        range_high      IN      VARCHAR2,
                        value_set_id    IN   NUMBER)
IS
BEGIN
     DELETE FROM FND_FLEX_VALUE_NORM_HIERARCHY
     WHERE       flex_value_set_id = value_set_id
     AND         parent_flex_value=parent
     AND         range_attribute= range_attr
     AND         child_flex_value_low= range_low
     AND         child_flex_value_high =  range_high;

Line: 953

END delete_fnd_norm_hier_rec;