APPS.GL_CHART_OF_ACCOUNTS_API_PKG SQL Statements

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

Line: 31

       SELECT   parent_flex_value, range_attribute
       FROM  fnd_flex_value_norm_hierarchy
       WHERE flex_value_set_id = value_set_id
       AND   target BETWEEN  child_flex_value_low
                        AND  child_flex_value_high;

Line: 97

                               v_dynamic_inserts_allowed_f IN VARCHAR2,
                               v_operation                 IN VARCHAR2 DEFAULT 'DML_INSERT')
  IS
/* NOT NEEDED FOR THE CHART OF ACCOUNTS FLEXFIELD
   ONLY NEEDED FOR OTHER FLEXFIELDS
    v_dynamic_inserts_feasible_f   VARCHAR2(1);

Line: 107

      SELECT dynamic_inserts_feasible_flag,
             set_defining_column_name
      FROM FND_ID_FLEXS
      WHERE application_id = v_application_id
        AND id_flex_code = v_id_flex_code;*/

Line: 117

    IF (v_operation<> 'DML_INSERT' AND v_operation<> 'DML_UPDATE') THEN
      RAISE invalid_dml_mode;

Line: 137

    INTO v_dynamic_inserts_feasible_f,
         v_set_defining_column_name;

Line: 159

    IF (v_operation = 'DML_INSERT' AND ((v_set_defining_column_name IS NULL) OR
                                        (v_id_flex_code IN ('MSTK', 'MTLL', 'MICG', 'MDSP')))) THEN
      RAISE multiflex_not_allowed;

Line: 167

    IF (v_dynamic_inserts_feasible_f = 'N' AND v_dynamic_inserts_allowed_f = 'Y') THEN
      RAISE dynamic_inserts_not_allowed;

Line: 177

        AND v_id_flex_num <> 101 AND v_operation = 'DML_INSERT') THEN
      INSERT INTO FND_FLEX_WORKFLOW_PROCESSES(APPLICATION_ID,
                                              ID_FLEX_CODE,
                                              ID_FLEX_NUM,
                                              WF_ITEM_TYPE,
                                              WF_PROCESS_NAME,
                                              LAST_UPDATE_DATE,
                                              LAST_UPDATED_BY,
                                              CREATION_DATE,
                                              CREATED_BY,
                                              LAST_UPDATE_LOGIN)
      SELECT FS.APPLICATION_ID,
             FS.ID_FLEX_CODE,
             FS.ID_FLEX_NUM,
             FWP.WF_ITEM_TYPE,
             'DEFAULT_ACCOUNT_GENERATION',
             SYSDATE,
             FS.LAST_UPDATED_BY,
             SYSDATE,
             FS.CREATED_BY,
             FS.LAST_UPDATE_LOGIN
      FROM FND_FLEX_WORKFLOW_PROCESSES FWP,
           FND_ID_FLEX_STRUCTURES FS
      WHERE FWP.APPLICATION_ID = v_application_id
        AND FWP.ID_FLEX_CODE = v_id_flex_code
        AND FWP.ID_FLEX_NUM = 101
        AND FS.APPLICATION_ID = v_application_id
        AND FS.ID_FLEX_CODE = v_id_flex_code
        AND FS.ID_FLEX_NUM = v_id_flex_num
        AND NOT EXISTS (SELECT 'Row already exists'
                        FROM FND_FLEX_WORKFLOW_PROCESSES FWP2
                        WHERE FWP2.APPLICATION_ID = v_application_id
                          AND FWP2.ID_FLEX_CODE = v_id_flex_code
                          AND FWP2.ID_FLEX_NUM = v_id_flex_num
                          AND FWP2.WF_ITEM_TYPE = FWP.WF_ITEM_TYPE);

Line: 244

    WHEN dynamic_inserts_not_allowed THEN
      fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');

Line: 247

      fnd_message.set_token('EVENT','dynamic_inserts_not_allowed');

Line: 292

      SELECT count(*)
      INTO   v_count
      FROM   FND_ID_FLEX_STRUCTURES_VL
      WHERE  application_id = v_application_id
        AND  id_flex_code = v_id_flex_code
        AND  id_flex_structure_name = v_id_flex_structure_name;

Line: 373

      SELECT flex_value_set_name,
             validation_type,
             security_enabled_flag,
             format_type,
             maximum_size,
             number_precision,
             alphanumeric_allowed_flag,
             uppercase_only_flag,
             numeric_mode_enabled_flag,
             minimum_value,
             maximum_value
      FROM FND_FLEX_VALUE_SETS
      WHERE flex_value_set_id = v_flex_value_set_id;

Line: 389

       SELECT c.width, c.column_type
         FROM FND_COLUMNS c,
              FND_TABLES t,
              FND_ID_FLEXS f
        WHERE c.application_id = t.application_id
          AND c.table_id = t.table_id
          AND c.column_name = v_application_column_name
          AND c.flexfield_usage_code = 'K'
          AND t.application_id = f.table_application_id
          AND t.table_name = f.application_table_name
          AND f.application_id = v_application_id
          AND f.id_flex_code = v_id_flex_code
          AND ( (v_industry_type = 'G'
                 AND v_id_flex_code = 'GLAT'
                 OR ((v_id_flex_code <> 'GLAT') and (c.column_name not like 'SEGMENT_ATTRIBUTE%')))
               OR NVL(v_industry_type, 'C') <> 'G');

Line: 426

         SELECT fpi.industry
         INTO v_industry_type
         FROM fnd_product_installations fpi
         WHERE fpi.application_id = v_application_id;

Line: 457

    SELECT count(*)
    INTO   v_count
    FROM   FND_ID_FLEX_SEGMENTS
    WHERE  application_id = v_application_id
      AND  id_flex_code = v_id_flex_code
      AND  id_flex_num = v_id_flex_num
      AND  segment_num = v_segment_num;

Line: 602

          UPDATE FND_ID_FLEX_SEGMENTS
          SET default_value = v_storage_value
          WHERE application_id = v_application_id
            AND id_flex_code = v_id_flex_code
            AND id_flex_num = v_id_flex_num
            AND application_column_name = v_application_column_name;

Line: 827

      SELECT global_flag,
             unique_flag,
             segment_prompt
      FROM FND_SEGMENT_ATTRIBUTE_TYPES
      WHERE application_id = v_application_id
        AND id_flex_code = v_id_flex_code
        AND segment_attribute_type = v_segment_attribute_type;

Line: 872

      SELECT count(*)
      INTO   v_count
      FROM   FND_SEGMENT_ATTRIBUTE_VALUES
      WHERE  application_id = v_application_id
        AND  id_flex_code = v_id_flex_code
        AND  id_flex_num = v_id_flex_num
        AND  segment_attribute_type = v_segment_attribute_type
        AND  attribute_value = 'Y';

Line: 985

        UPDATE FND_FLEX_VALUE_SETS
        SET dependant_default_value = v_storage_value
        WHERE flex_value_set_id = v_flex_value_set_id;

Line: 1010

        UPDATE FND_FLEX_VALUE_SETS
        SET minimum_value = v_storage_value
        WHERE flex_value_set_id = v_flex_value_set_id;

Line: 1035

        UPDATE FND_FLEX_VALUE_SETS
        SET maximum_value = v_storage_value
        WHERE flex_value_set_id = v_flex_value_set_id;

Line: 1128

      SELECT 'Exists'
      FROM FND_VALUE_ATTRIBUTE_TYPES
      WHERE application_id = v_id_flex_application_id
        AND id_flex_code = v_id_flex_code
        AND segment_attribute_type = v_segment_attribute_type
        AND value_attribute_type = v_value_attribute_type;

Line: 1138

      SELECT value_attribute_type, assignment_date
      FROM FND_FLEX_VALIDATION_QUALIFIERS
      WHERE id_flex_application_id = v_id_flex_application_id
        AND id_flex_code = v_id_flex_code
        AND flex_value_set_id = v_flex_value_set_id
        AND value_attribute_type IN ('DETAIL_POSTING_ALLOWED',
                                     'GL_ACCOUNT_TYPE',
                                     'GL_CONTROL_ACCOUNT',
                                     'RECONCILIATION FLAG')
        AND assignment_date < v_assignment_date;

Line: 1150

      SELECT value_attribute_type, assignment_date
      FROM FND_FLEX_VALIDATION_QUALIFIERS
      WHERE id_flex_application_id = v_id_flex_application_id
        AND id_flex_code = v_id_flex_code
        AND flex_value_set_id = v_flex_value_set_id
        AND ((value_attribute_type  = 'DETAIL_BUDGETING_ALLOWED'
              AND assignment_date > v_assignment_date)
            OR
             (value_attribute_type IN ('GL_ACCOUNT_TYPE',
                                       'GL_CONTROL_ACCOUNT',
                                       'RECONCILIATION FLAG')
              AND assignment_date < v_assignment_date));

Line: 1164

      SELECT value_attribute_type, assignment_date
      FROM FND_FLEX_VALIDATION_QUALIFIERS
      WHERE id_flex_application_id = v_id_flex_application_id
        AND id_flex_code = v_id_flex_code
        AND flex_value_set_id = v_flex_value_set_id
        AND ((value_attribute_type IN ('DETAIL_BUDGETING_ALLOWED',
                                       'DETAIL_POSTING_ALLOWED')
              AND assignment_date > v_assignment_date)
            OR
             (value_attribute_type IN ('GL_CONTROL_ACCOUNT',
                                       'RECONCILIATION FLAG')
              AND assignment_date < v_assignment_date));

Line: 1178

      SELECT value_attribute_type, assignment_date
      FROM FND_FLEX_VALIDATION_QUALIFIERS
      WHERE id_flex_application_id = v_id_flex_application_id
        AND id_flex_code = v_id_flex_code
        AND flex_value_set_id = v_flex_value_set_id
        AND value_attribute_type IN ('DETAIL_BUDGETING_ALLOWED',
                                     'DETAIL_POSTING_ALLOWED',
                                     'GL_ACCOUNT_TYPE')
        AND assignment_date > v_assignment_date;

Line: 1190

      SELECT value_attribute_type, assignment_date
      FROM FND_FLEX_VALIDATION_QUALIFIERS
      WHERE id_flex_application_id = v_id_flex_application_id
        AND id_flex_code = v_id_flex_code
        AND flex_value_set_id = v_flex_value_set_id
        AND value_attribute_type IN ('DETAIL_BUDGETING_ALLOWED',
                                     'DETAIL_POSTING_ALLOWED',
                                     'GL_ACCOUNT_TYPE')
        AND assignment_date > v_assignment_date;

Line: 1230

    SELECT assignment_date
    INTO v_assignment_date
    FROM FND_FLEX_VALIDATION_QUALIFIERS
    WHERE id_flex_application_id = v_id_flex_application_id
      AND id_flex_code = v_id_flex_code
      AND flex_value_set_id = v_flex_value_set_id
      AND segment_attribute_type = v_segment_attribute_type
      AND value_attribute_type = v_value_attribute_type;

Line: 1380

      SELECT flex_value_set_name,
             validation_type,
             security_enabled_flag,
             format_type,
             maximum_size,
             number_precision,
             alphanumeric_allowed_flag,
             uppercase_only_flag,
             numeric_mode_enabled_flag,
             minimum_value,
             maximum_value
      FROM FND_FLEX_VALUE_SETS
      WHERE flex_value_set_id = v_flex_value_set_id;

Line: 1396

      SELECT vat.required_flag AS required_flag,
             vat.lookup_type AS lookup_type,
             vat.value_attribute_type AS value_attribute_type
      FROM FND_VALUE_ATTRIBUTE_TYPES vat,
           FND_FLEX_VALIDATION_QUALIFIERS fvq
      WHERE fvq.flex_value_set_id = v_flex_value_set_id
        AND vat.id_flex_code = fvq.id_flex_code
        AND vat.application_id = fvq.id_flex_application_id
        AND vat.segment_attribute_type = fvq.segment_attribute_type
        AND vat.value_attribute_type = fvq.value_attribute_type
      ORDER BY fvq.assignment_date, fvq.value_attribute_type;

Line: 1415

      SELECT v_character
      FROM dual
      WHERE v_character IN (SELECT lookup_code
                            FROM FND_LOOKUPS
                            WHERE lookup_type = v_lookup_type)
        OR (v_character = ' ' AND v_required_flag = 'N' );

Line: 1468

      UPDATE FND_FLEX_VALUES
      SET flex_value = v_storage_value
      WHERE flex_value_id = v_flex_value_id;

Line: 1479

    SELECT COUNT(*) INTO v_count_value_attribute_types
    FROM FND_VALUE_ATTRIBUTE_TYPES vat,
         FND_FLEX_VALIDATION_QUALIFIERS fvq
    WHERE fvq.flex_value_set_id = v_flex_value_set_id
      AND vat.id_flex_code = fvq.id_flex_code
      AND vat.application_id = fvq.id_flex_application_id
      AND vat.segment_attribute_type = fvq.segment_attribute_type
      AND vat.value_attribute_type = fvq.value_attribute_type;

Line: 1640

      SELECT vs.flex_value_set_id,
             vs.flex_value_set_name,
             vs.validation_type,
             vs.security_enabled_flag,
             vs.format_type,
             vs.maximum_size,
             vs.number_precision,
             vs.alphanumeric_allowed_flag,
             vs.uppercase_only_flag,
             vs.numeric_mode_enabled_flag,
             vs.minimum_value,
             vs.maximum_value
      FROM   FND_FLEX_VALUE_SETS vs,
             FND_FLEX_VALUES v
      WHERE  v.flex_value_id = v_flex_value_id
        AND  v.flex_value_set_id = vs.flex_value_set_id;

Line: 1691

      SELECT parent_flex_value_low
      INTO v_parent_flex_value_low
      FROM FND_FLEX_VALUES
      WHERE flex_value_id = v_flex_value_id;

Line: 1696

      SELECT count(*)
      INTO   v_count
      FROM   FND_FLEX_VALUES_VL
      WHERE  flex_value_set_id = v_flex_value_set_id
        AND  flex_value_meaning = v_flex_value_meaning
        AND ((v_parent_flex_value_low IS null) OR
             (parent_flex_value_low =
              v_parent_flex_value_low));

Line: 1731

        UPDATE FND_FLEX_VALUES_TL
        SET flex_value_meaning = v_storage_value
        WHERE flex_value_id = v_flex_value_id
          AND language = v_language;

Line: 1801

      SELECT freeze_flex_definition_flag
      FROM FND_ID_FLEX_STRUCTURES
      WHERE application_id = v_application_id
        AND id_flex_code = v_id_flex_code
        AND id_flex_num = v_id_flex_num;

Line: 1810

    SELECT sat.segment_prompt,
           fs.segment_num
    FROM FND_ID_FLEX_SEGMENTS fs,
         FND_SEGMENT_ATTRIBUTE_TYPES sat
    WHERE fs.application_id = v_application_id
      AND fs.id_flex_code = v_id_flex_code
      AND fs.id_flex_num = v_id_flex_num
      AND sat.application_id = v_application_id
      AND sat.id_flex_code = v_id_flex_code
      AND NOT EXISTS (SELECT 'Exist'
                      FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
                      WHERE sav.application_id = v_application_id
                        AND sav.id_flex_code = v_id_flex_code
                        AND sav.id_flex_num = v_id_flex_num
                        AND sav.application_column_name = fs.application_column_name
                        AND sav.segment_attribute_type = sat.segment_attribute_type);

Line: 1830

      SELECT sat.segment_prompt
      FROM FND_ID_FLEX_STRUCTURES ft,
           FND_SEGMENT_ATTRIBUTE_TYPES sat
      WHERE ft.application_id = v_application_id
        AND ft.id_flex_code = v_id_flex_code
        AND ft.id_flex_num = v_id_flex_num
        AND sat.application_id = v_application_id
        AND sat.id_flex_code = v_id_flex_code
        AND sat.required_flag = 'Y'
        AND NOT EXISTS (SELECT 'Exist'
                        FROM FND_SEGMENT_ATTRIBUTE_VALUES sav,
                             FND_ID_FLEX_SEGMENTS fs
                        WHERE sav.application_id = v_application_id
                          AND sav.id_flex_code = v_id_flex_code
                          AND sav.id_flex_num = v_id_flex_num
                          AND sav.segment_attribute_type = sat.segment_attribute_type
                          AND sav.attribute_value = 'Y'
                          AND fs.application_id = v_application_id
                          AND fs.id_flex_code = v_id_flex_code
                          AND fs.id_flex_num = v_id_flex_num
                          AND fs.application_column_name = sav.application_column_name
                          AND fs.enabled_flag = 'Y');

Line: 1856

      SELECT fs.segment_num, fs.range_code
      FROM FND_ID_FLEX_SEGMENTS fs
      WHERE fs.application_id = v_application_id
        AND fs.id_flex_code = v_id_flex_code
        AND fs.id_flex_num = v_id_flex_num
        AND fs.range_code IN ('L','H')
        AND fs.enabled_flag = 'Y';

Line: 1870

      SELECT fs.segment_num
      FROM FND_ID_FLEX_SEGMENTS fs
      WHERE fs.application_id = v_application_id
        AND fs.id_flex_code = v_id_flex_code
        AND fs.id_flex_num = v_id_flex_num
        AND fs.application_column_name IN (SELECT sav1.application_column_name
                                           FROM FND_SEGMENT_ATTRIBUTE_VALUES sav1,
                                                FND_SEGMENT_ATTRIBUTE_VALUES sav2
                                           WHERE sav1.application_id = v_application_id
                                             AND sav1.id_flex_code = v_id_flex_code
                                             AND sav1.id_flex_num = v_id_flex_num
                                             AND sav1.segment_attribute_type = 'GL_ACCOUNT'
                                             AND sav1.attribute_value = 'Y'
                                             AND sav1.application_column_name = sav2.application_column_name
                                             AND sav2.application_id = v_application_id
                                             AND sav2.id_flex_code = v_id_flex_code
                                             AND sav2.id_flex_num = v_id_flex_num
                                             AND sav2.segment_attribute_type = 'GL_BALANCING'
                                             AND sav2.attribute_value = 'Y');

Line: 1921

    SELECT NVL((sum(fv.maximum_size) + count(fs.application_column_name) - 1),0)
    INTO v_count
    FROM FND_ID_FLEX_SEGMENTS fs,
         FND_FLEX_VALUE_SETS fv
    WHERE fs.application_id = v_application_id
      AND fs.id_flex_code = v_id_flex_code
      AND fs.id_flex_num = v_id_flex_num
      AND fv.flex_value_set_id (+) = fs.flex_value_set_id;

Line: 1957

      SELECT max(segment_num),count(segment_num)
      INTO v_max,v_count
      FROM FND_ID_FLEX_SEGMENTS
      WHERE application_id = v_application_id
        AND id_flex_code = v_id_flex_code
        AND id_flex_num = v_id_flex_num;

Line: 2018

          SELECT NVL(min(segment_num),-1000)
          INTO v_range_code_min_high_seg_num
          FROM FND_ID_FLEX_SEGMENTS fs
          WHERE fs.application_id = v_application_id
            AND fs.id_flex_code = v_id_flex_code
            AND fs.id_flex_num = v_id_flex_num
            AND fs.segment_num > v_segment_num
            AND fs.enabled_flag = 'Y'
            AND fs.range_code = 'H';

Line: 2028

          SELECT NVL(min(segment_num),1000)
          INTO v_range_code_min_low_seg_num
          FROM FND_ID_FLEX_SEGMENTS fs
          WHERE fs.application_id = v_application_id
            AND fs.id_flex_code = v_id_flex_code
            AND fs.id_flex_num = v_id_flex_num
            AND fs.segment_num > v_segment_num
            AND fs.enabled_flag = 'Y'
            AND fs.range_code = 'L';

Line: 2044

          SELECT NVL(max(segment_num),1000)
          INTO v_range_code_max_low_seg_num
          FROM FND_ID_FLEX_SEGMENTS fs
          WHERE fs.application_id = v_application_id
            AND fs.id_flex_code = v_id_flex_code
            AND fs.id_flex_num = v_id_flex_num
            AND fs.segment_num < v_segment_num
            AND fs.enabled_flag = 'Y'
            AND fs.range_code = 'L';

Line: 2054

          SELECT NVL(max(segment_num),-1000)
          INTO v_range_code_max_high_seg_num
          FROM FND_ID_FLEX_SEGMENTS fs
          WHERE fs.application_id = v_application_id
            AND fs.id_flex_code = v_id_flex_code
            AND fs.id_flex_num = v_id_flex_num
            AND fs.segment_num < v_segment_num
            AND fs.enabled_flag = 'Y'
            AND fs.range_code = 'H';

Line: 2079

    INSERT INTO fnd_flex_validation_qualifiers( flex_value_set_id,
                                                id_flex_application_id,
                                                id_flex_code,
                                                segment_attribute_type,
                                                value_attribute_type,
                                                assignment_date )
    SELECT seg.flex_value_set_id,
           v_application_id,
           v_id_flex_code,
           sav.segment_attribute_type,
           vat.value_attribute_type,
           sysdate
    FROM fnd_segment_attribute_values sav,
         fnd_value_attribute_types vat,
         fnd_id_flex_segments seg
    WHERE seg.application_id = v_application_id
      AND seg.id_flex_code = v_id_flex_code
      AND seg.id_flex_num = v_id_flex_num
      AND seg.flex_value_set_id IS NOT NULL
      AND seg.enabled_flag = 'Y'
      AND sav.application_id = v_application_id
      AND sav.id_flex_code = v_id_flex_code
      AND sav.id_flex_num = v_id_flex_num
      AND sav.application_column_name = seg.application_column_name
      AND sav.attribute_value = 'Y'
      AND sav.application_id = vat.application_id
      AND sav.id_flex_code = vat.id_flex_code
      AND sav.segment_attribute_type = vat.segment_attribute_type
      AND NOT EXISTS
         (SELECT NULL
          FROM fnd_flex_validation_qualifiers q
          WHERE q.flex_value_set_id = seg.flex_value_set_id
          AND q.id_flex_application_id = v_application_id
          AND q.id_flex_code = v_id_flex_code
          AND q.segment_attribute_type = sav.segment_attribute_type
          AND q.value_attribute_type = vat.value_attribute_type);

Line: 2187

      SELECT application_short_name
      FROM FND_APPLICATION
      WHERE application_id = v_application_id;

Line: 2193

      SELECT id_flex_num,
             structure_view_name
      FROM FND_ID_FLEX_STRUCTURES
      WHERE application_id = v_application_id
        AND id_flex_code = v_id_flex_code
        AND id_flex_structure_code = v_id_flex_structure_code;

Line: 2260

    SELECT count(*)
    INTO v_count
    FROM fnd_product_installations
    WHERE application_id = v_application_id;

Line: 2396

      select 'x'
      from fnd_flex_values
      where flex_value_set_id = p_value_set_id
      and   flex_value        = p_parent
      and   summary_flag      = 'Y';

Line: 2404

     SELECT flex_value
     FROM fnd_flex_values
     WHERE flex_value_set_id = p_value_set_id
     AND   flex_value BETWEEN p_child_low AND p_child_high
     ORDER by flex_value;

Line: 2474

      select flex_value_set_name
      from fnd_flex_value_sets
      where flex_value_set_id = p_flex_value_set_id;