APPS.JA_CN_UTILITY SQL Statements

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

Line: 224

      /*      SELECT l_ledger_id
              INTO l_ledger_id
              FROM ja_cn_system_parameters_all
             WHERE legal_entity_id = p_legal_entity_id;

Line: 250

    SELECT Chart_Of_Accounts_Id
      INTO l_Chart_Of_Accounts_Id
      FROM Gl_Sets_Of_Books
     WHERE Set_Of_Books_Id = l_Ledger_Id;

Line: 369

      SELECT Chart_Of_Accounts_Id
        FROM Gl_Access_Sets
       WHERE Access_Set_Id = p_Access_Set_Id;

Line: 419

      SELECT Flv.Meaning
        FROM Fnd_Lookup_Values Flv
       WHERE Flv.LANGUAGE = Userenv('LANG')
         AND Flv.Lookup_Type = 'JA_CN_DUPOBJECTS_TOKENS'
         AND Flv.View_Application_Id = 0
         AND Flv.Security_Group_Id = 0
         AND Flv.Lookup_Code = p_Lookup_Code;

Line: 665

      SELECT Flv.Lookup_Code
        FROM Fnd_Lookup_Values Flv
       WHERE Flv.LANGUAGE = Userenv('LANG')
         AND Flv.Lookup_Type = p_Lookup_Type
         AND Flv.Meaning = p_Lookup_Meaning
         AND Flv.View_Application_Id = p_View_Application_Id
         AND Flv.Security_Group_Id = p_Security_Group_Id;

Line: 1050

      SELECT Id_Flex_Num,
             Concatenated_Segment_Delimiter
        FROM Fnd_Id_Flex_Structures
       WHERE Application_Id = '101'
         AND Id_Flex_Code = 'GL#'
         AND Id_Flex_Num =
             (SELECT Chart_Of_Accounts_Id
                FROM Gl_Sets_Of_Books
               WHERE Set_Of_Books_Id = p_Set_Of_Bks_Id);

Line: 1061

      SELECT Application_Column_Name
        FROM Fnd_Id_Flex_Segments
       WHERE Application_Id = 101
         AND Id_Flex_Code = 'GL#'
         AND Id_Flex_Num = l_Id_Flex_Num
         AND Enabled_Flag = 'Y'
         AND Display_Flag = 'Y'
       ORDER BY Segment_Num;

Line: 1102

      SELECT Attribute_Value
        INTO l_Account_Segment_Flag
        FROM Fnd_Segment_Attribute_Values
       WHERE Application_Id = 101
         AND Id_Flex_Code = 'GL#'
         AND Id_Flex_Num = l_Id_Flex_Num
         AND Application_Column_Name =
             l_Coa_Segment.Application_Column_Name
         AND Segment_Attribute_Type = 'GL_ACCOUNT';

Line: 1116

    SELECT Substr(p_Acc_Flex,
                  Instr(p_Acc_Flex, l_Delimiter, 1, l_Seq_Account - 1) + 1,
                  (Instr(p_Acc_Flex, l_Delimiter, 1, l_Seq_Account) -
                  Instr(p_Acc_Flex, l_Delimiter, 1, l_Seq_Account - 1) - 1))
      INTO l_Account_Segment
      FROM Dual;

Line: 1124

    /*    SELECT COUNT(account_segment_value)
     INTO l_cash_acct_count
     FROM ja_cn_cash_accounts_all
    WHERE set_of_books_id = p_set_of_bks_id
      AND account_segment_value = l_account_segment;*/

Line: 1205

    SELECT Start_Date
      INTO l_Start_Date
      FROM Gl_Period_Statuses
     WHERE ledger_id=p_ledger_id--Set_Of_Books_Id = l_Sob_Id,--updated by lyb
       AND Application_Id = 101
       AND Period_Name = p_Start_Period_Name;

Line: 1212

    SELECT End_Date
      INTO l_End_Date
      FROM Gl_Period_Statuses
     WHERE ledger_id=p_ledger_id--Set_Of_Books_Id = l_Sob_Id--updated by lyb
       AND Application_Id = 101
       AND Period_Name = p_End_Period_Name;

Line: 1219

    SELECT COUNT(*)
      INTO l_All_Period_Number
      FROM Gl_Period_Statuses
     WHERE ledger_id=p_ledger_id--Set_Of_Books_Id = l_Sob_Id--updated by lyb
       AND Application_Id = 101
       AND ((Start_Date BETWEEN l_Start_Date AND l_End_Date) AND
           (End_Date BETWEEN l_Start_Date AND l_End_Date));

Line: 1227

    SELECT COUNT(*)
      INTO l_Closed_Period_Number
      FROM Gl_Period_Statuses
     WHERE ledger_id=p_ledger_id--Set_Of_Books_Id = l_Sob_Id,--updated by lyb
       AND Application_Id = 101
       AND ((Start_Date BETWEEN l_Start_Date AND l_End_Date) AND
           (End_Date BETWEEN l_Start_Date AND l_End_Date))
       AND (Closing_Status = 'C' OR Closing_Status = 'P');

Line: 1261

    l_Sql := 'SELECT nvl(jcask.concatenated_segments, '''')
        FROM ' || l_Acc_Stru_Tablename ||
             ' jcask
           ,ja_cn_system_parameters_all  jcsp
      WHERE jcask.account_structure_id = jcsp.account_structure_id
        AND jcsp.legal_entity_id = :1';

Line: 1305

    l_Insertsql  Dbms_Sql.Varchar2s;

Line: 1338

    SELECT Ledger_Category_Code,
           Nvl(Bal_Seg_Value_Option_Code, 'A'),
           Bal_Seg_Value_Set_Id
      INTO l_Ledger_Category,
           l_Bsv_Option,
           l_Bsv_Vset_Id
      FROM Gl_Ledgers
     WHERE Ledger_Id = p_Ledger_Id;

Line: 1365

      INSERT INTO Ja_Cn_Ledger_Le_Bsv_Gt
        (Ledger_Id,
         Ledger_Category_Code,
         Chart_Of_Accounts_Id,
         Bal_Seg_Value_Option_Code,
         Bal_Seg_Value_Set_Id,
         Bal_Seg_Value,
         Legal_Entity_Id,
         Start_Date,
         End_Date)
      -- XLE uptake: Changed to get the LE name from the new XLE tables
        SELECT Lg.Ledger_Id,
               Lg.Ledger_Category_Code,
               Lg.Chart_Of_Accounts_Id,
               Lg.Bal_Seg_Value_Option_Code,
               Lg.Bal_Seg_Value_Set_Id,
               Bsv.Segment_Value,
               Bsv.Legal_Entity_Id,
               Bsv.Start_Date,
               Bsv.End_Date
          FROM Gl_Ledgers              Lg,
               Gl_Ledger_Relationships Rs,
               Gl_Ledger_Norm_Seg_Vals Bsv,
               Gl_Ledgers              Lgr_c
         WHERE ((Rs.Relationship_Type_Code = 'NONE' AND
               Rs.Target_Ledger_Id = p_Ledger_Id) OR
               (Rs.Target_Ledger_Category_Code = 'ALC' AND
               Rs.Relationship_Type_Code IN ('SUBLEDGER', 'JOURNAL') AND
               Rs.Source_Ledger_Id = p_Ledger_Id))
           AND Rs.Application_Id = 101
           AND Lg.Ledger_Id = Rs.Target_Ledger_Id
           AND Bsv.Ledger_Id = p_Ledger_Id
           AND Rs.Target_Ledger_Id = Lgr_c.Ledger_Id
           AND Nvl(Lgr_c.Complete_Flag, 'Y') = 'Y'
           AND Bsv.Segment_Type_Code = 'B'
              -- We should exclude segment values with status code = 'D' since they
              -- will be deleted by the flatten program when config is confirmed
              --       AND bsv.status_code IS NULL
           AND Nvl(Bsv.Status_Code, 'I') <> 'D'
           AND Bsv.Legal_Entity_Id = p_Legal_Entity_Id;

Line: 1414

      SELECT Nvl(Fvt.Application_Table_Name, 'FND_FLEX_VALUES'),
             Nvl(Fvt.Value_Column_Name, 'FLEX_VALUE'),
             Fvs.Validation_Type
        INTO l_Fv_Table,
             l_Fv_Col,
             l_Fv_Type
        FROM Fnd_Flex_Value_Sets        Fvs,
             Fnd_Flex_Validation_Tables Fvt
       WHERE Fvs.Flex_Value_Set_Id = l_Bsv_Vset_Id
         AND Fvt.Flex_Value_Set_Id(+) = Fvs.Flex_Value_Set_Id;

Line: 1427

      l_Insertsql(l_Line_No) := 'INSERT INTO JA_CN_LEDGER_LE_BSV_GT';

Line: 1429

      l_Insertsql(l_Line_No) := '(LEDGER_ID, LEDGER_CATEGORY_CODE, ';

Line: 1431

      l_Insertsql(l_Line_No) := ' CHART_OF_ACCOUNTS_ID, BAL_SEG_VALUE_OPTION_CODE, BAL_SEG_VALUE_SET_ID, ';

Line: 1433

      l_Insertsql(l_Line_No) := ' BAL_SEG_VALUE, LEGAL_ENTITY_ID, ';

Line: 1435

      l_Insertsql(l_Line_No) := ' START_DATE, END_DATE) ';

Line: 1441

      l_Insertsql(l_Line_No) := 'SELECT lg.LEDGER_ID, lg.LEDGER_CATEGORY_CODE, ';

Line: 1445

      l_Insertsql(l_Line_No) := '       lg.CHART_OF_ACCOUNTS_ID, lg.BAL_SEG_VALUE_OPTION_CODE, ';

Line: 1449

      l_Insertsql(l_Line_No) := '       lg.BAL_SEG_VALUE_SET_ID, bsv.' ||
                                l_Fv_Col || ', ';

Line: 1457

        l_Insertsql(l_Line_No) :=  p_Legal_Entity_Id ||
                                  ', bsv.START_DATE_ACTIVE, bsv.END_DATE_ACTIVE  ';

Line: 1460

        l_Insertsql(l_Line_No) :=  p_Legal_Entity_Id ||
                                  ', NULL, NULL  ';

Line: 1470

      l_Insertsql(l_Line_No) := 'FROM GL_LEDGERS lg, ' || l_Fv_Table ||
                                ' bsv ';

Line: 1475

      l_Insertsql(l_Line_No) := 'WHERE (lg.ledger_id = :lg_id1 ';

Line: 1477

      l_Insertsql(l_Line_No) := '       OR lg.ledger_id IN ( ';

Line: 1479

      l_Insertsql(l_Line_No) := '           SELECT ledger_id FROM GL_ALC_LEDGER_RSHIPS_V ';

Line: 1481

      l_Insertsql(l_Line_No) := '           WHERE application_id = 101 ';

Line: 1483

      l_Insertsql(l_Line_No) := '           AND source_ledger_id = :lg_id2)) ';

Line: 1488

        l_Insertsql(l_Line_No) := 'AND bsv.flex_value_set_id = lg.bal_seg_value_set_id ';

Line: 1490

        l_Insertsql(l_Line_No) := 'AND bsv.summary_flag = ''N'' ';

Line: 1496

                     l_Insertsql,
                     1,
                     l_Line_No,
                     TRUE,
                     Dbms_Sql.Native);

Line: 1567

    SELECT NUM+1 INTO l_num
      FROM FND_SEGMENT_ATTRIBUTE_VALUES FSAV,
           (SELECT ROWNUM NUM, APPLICATION_COLUMN_NAME
              FROM (SELECT APPLICATION_COLUMN_NAME, SEGMENT_NUM
                      FROM FND_ID_FLEX_SEGMENTS
                     WHERE ID_FLEX_NUM = p_coa_id
                       AND ID_FLEX_CODE = 'GL#'
                       AND APPLICATION_ID = 101
                       AND ENABLED_FLAG = 'Y'
                     ORDER BY SEGMENT_NUM)) FIFS
     WHERE FSAV.APPLICATION_ID = 101
       AND FSAV.ID_FLEX_NUM = p_coa_id
       AND ID_FLEX_CODE = 'GL#'
       AND FSAV.ATTRIBUTE_VALUE = 'Y'
       AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
       AND FSAV.APPLICATION_COLUMN_NAME = FIFS.APPLICATION_COLUMN_NAME;

Line: 1634

  SELECT tag
  INTO   ln_xml_encoding
  FROM   fnd_lookup_values
  WHERE  lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
    AND    lookup_code =
           SUBSTR(USERENV('LANGUAGE'), INSTR(USERENV('LANGUAGE'), '.') + 1)
    AND    LANGUAGE = 'US';

Line: 1723

    SELECT meaning
    INTO   lv_xml_tag
    FROM   fnd_lookups
    WHERE  lookup_type = 'JA_CN_XML_TAGS'
    AND    lookup_code = pv_lookup_code;

Line: 1730

    SELECT meaning
    INTO   lv_xml_tag
    FROM   fnd_lookups
    WHERE  lookup_type = 'JA_CN_FA_XML_TAGS'
    AND    lookup_code = pv_lookup_code;

Line: 1737

    SELECT meaning
    INTO   lv_xml_tag
    FROM   fnd_lookups
    WHERE  lookup_type = 'JA_CN_APAR_XML_TAGS' --for testing APAR Chinese XML tags
    AND    lookup_code = pv_lookup_code;

Line: 1985

      SELECT rpad(NVL(NULL, '0'), pn_fixed_length, '0')
      INTO lv_text_node_value
      FROM dual;

Line: 2144

SELECT
  flv.meaning
FROM
  fnd_lookup_values flv
WHERE flv.LANGUAGE = Userenv('LANG')
  AND flv.lookup_type = 'JA_CN_OUTPUTFILE_PREFIX'
  AND flv.view_application_Id = 0
  AND flv.security_group_id = 0
  AND flv.lookup_code = pv_module_name;

Line: 2156

SELECT
  gps.period_num
FROM
  gl_period_statuses gps
WHERE gps.ledger_id = pv_ledger_id
  AND gps.period_name = pv_from_period
  AND gps.application_id = 200;

Line: 2166

SELECT
  gps.period_num
FROM
  gl_period_statuses gps
WHERE gps.ledger_id = pv_ledger_id
  AND gps.period_name = pv_to_period
  AND gps.application_id = 200;

Line: 2311

      SELECT jcspa.book_num,
             jcspa.book_name,
             jcspa.company_name,
             jcspa.organization_id,
             jcspa.ent_quality,
             jcspa.ent_industry,
             jcspa.cnao_stand_ver
        FROM ja_cn_system_parameters_all jcspa
       WHERE jcspa.legal_entity_id = ln_legal_entity_id;

Line: 2554

  SELECT rounding_rule
  INTO   lv_rounding_rule
  FROM   ja_cn_system_parameters_all
  WHERE  legal_entity_id = pn_legal_entity_id;

Line: 2619

SELECT Attribute_Column
 FROM Ja_CN_Dff_Assignments
WHERE Application_Id = pn_application_id --parameter: pn_application_id
  AND Chart_of_Accounts_Id = pn_coa_id --parameter: pn_coa_id
  AND DFF_TITLE_CODE = pv_flexfield_code; --parameter: pv_flexfield_code

Line: 2689

SELECT FFV.Flex_Value   Segment_Value
      ,FFVT.Description Segment_Value_Description
   FROM FND_ID_FLEX_SEGMENTS FIFS,
        Fnd_Flex_Values      FFV,
        FND_FLEX_VALUES_TL   FFVT
  WHERE FIFS.Application_Id = 101
    AND FIFS.Application_Column_Name = Get_Attribute_Column(pv_flexfield_code --parameter: pv_flexfield_code
                                                           ,101
                                                           ,pn_coa_id) -- parameter: pn_coa_id
    AND FIFS.Id_Flex_Num = pn_coa_id --parameter: pn_coa_id
    AND FIFS.Id_Flex_Code = 'GL#'
    AND FFV.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
    AND FFV.Flex_Value = pv_flex_value --parameter: pv_flex_value
    AND FFVT.Flex_Value_Id = FFV.Flex_Value_Id
    AND FFVT.LANGUAGE = userenv('LANG');

Line: 2780

  lv_cursor_sql := 'SELECT FFV.'|| Get_Attribute_Column(pv_flexfield_code
                                                       ,0
                                                       ,pn_coa_id) || ' Attribute_Value';

Line: 2846

SELECT FFV.Flex_Value Attribute_Value
     , FFVT.Description Attribute_Value_Desc
 FROM FND_DESCR_FLEX_COL_USAGE_VL FUV,
      Fnd_Flex_Values      FFV,
      FND_FLEX_VALUES_TL   FFVT
WHERE FUV.Application_Id = 0
  AND FUV.Application_Column_Name = Get_Attribute_Column(pv_flexfield_code
                                                        ,0
                                                        ,pn_coa_id) --Get_Attribute_Column(0,'AFTY'/'ABSO')
  AND FUV.Descriptive_FlexField_Name = 'FND_FLEX_VALUES'
  AND FFV.FLEX_VALUE_SET_ID = FUV.FLEX_VALUE_SET_ID
  AND FFV.Flex_Value = pv_flex_value --parameter: pv_flex_value
  AND FFVT.Flex_Value_Id = FFV.Flex_Value_Id
  AND FFVT.LANGUAGE = userenv('LANG');

Line: 2914

  SELECT
    DECODE(FSAV.APPLICATION_COLUMN_NAME, --segment FSAV.APPLICATION_COLUMN_NAME of gcc
                      'SEGMENT1',GCC.SEGMENT1, 'SEGMENT2',GCC.SEGMENT2, 'SEGMENT3',GCC.SEGMENT3,
                      'SEGMENT4',GCC.SEGMENT4, 'SEGMENT5',GCC.SEGMENT5, 'SEGMENT6',GCC.SEGMENT6,
                      'SEGMENT7',GCC.SEGMENT7, 'SEGMENT8',GCC.SEGMENT8, 'SEGMENT9',GCC.SEGMENT9,
                      'SEGMENT10',GCC.SEGMENT10, 'SEGMENT11',GCC.SEGMENT11, 'SEGMENT12',GCC.SEGMENT12,
                      'SEGMENT13',GCC.SEGMENT13, 'SEGMENT14',GCC.SEGMENT14, 'SEGMENT15',GCC.SEGMENT15,
                      'SEGMENT16',GCC.SEGMENT16, 'SEGMENT17',GCC.SEGMENT17, 'SEGMENT18',GCC.SEGMENT18,
                      'SEGMENT19',GCC.SEGMENT19, 'SEGMENT20',GCC.SEGMENT20, 'SEGMENT21',GCC.SEGMENT21,
                      'SEGMENT22',GCC.SEGMENT22, 'SEGMENT23',GCC.SEGMENT23, 'SEGMENT24',GCC.SEGMENT24,
                      'SEGMENT25',GCC.SEGMENT25, 'SEGMENT26',GCC.SEGMENT26, 'SEGMENT27',GCC.SEGMENT27,
                      'SEGMENT28',GCC.SEGMENT28, 'SEGMENT29',GCC.SEGMENT29, 'SEGMENT30',GCC.SEGMENT30)
      INTO L_BALANCING_SEGMENT
    FROM GL_CODE_COMBINATIONS GCC,
         FND_SEGMENT_ATTRIBUTE_VALUES FSAV
   WHERE GCC.CODE_COMBINATION_ID = P_CC_ID
     AND FSAV.ATTRIBUTE_VALUE = 'Y'
     AND FSAV.APPLICATION_ID = 101
     AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
     AND FSAV.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
     AND FSAV.ID_FLEX_CODE = 'GL#';

Line: 2961

  SELECT DECODE(FSAV.APPLICATION_COLUMN_NAME, --segment FSAV.APPLICATION_COLUMN_NAME of gcc
                'SEGMENT1',
                GCC.SEGMENT1,
                'SEGMENT2',
                GCC.SEGMENT2,
                'SEGMENT3',
                GCC.SEGMENT3,
                'SEGMENT4',
                GCC.SEGMENT4,
                'SEGMENT5',
                GCC.SEGMENT5,
                'SEGMENT6',
                GCC.SEGMENT6,
                'SEGMENT7',
                GCC.SEGMENT7,
                'SEGMENT8',
                GCC.SEGMENT8,
                'SEGMENT9',
                GCC.SEGMENT9,
                'SEGMENT10',
                GCC.SEGMENT10,
                'SEGMENT11',
                GCC.SEGMENT11,
                'SEGMENT12',
                GCC.SEGMENT12,
                'SEGMENT13',
                GCC.SEGMENT13,
                'SEGMENT14',
                GCC.SEGMENT14,
                'SEGMENT15',
                GCC.SEGMENT15,
                'SEGMENT16',
                GCC.SEGMENT16,
                'SEGMENT17',
                GCC.SEGMENT17,
                'SEGMENT18',
                GCC.SEGMENT18,
                'SEGMENT19',
                GCC.SEGMENT19,
                'SEGMENT20',
                GCC.SEGMENT20,
                'SEGMENT21',
                GCC.SEGMENT21,
                'SEGMENT22',
                GCC.SEGMENT22,
                'SEGMENT23',
                GCC.SEGMENT23,
                'SEGMENT24',
                GCC.SEGMENT24,
                'SEGMENT25',
                GCC.SEGMENT25,
                'SEGMENT26',
                GCC.SEGMENT26,
                'SEGMENT27',
                GCC.SEGMENT27,
                'SEGMENT28',
                GCC.SEGMENT28,
                'SEGMENT29',
                GCC.SEGMENT29,
                'SEGMENT30',
                GCC.SEGMENT30)
    INTO L_ACCOUNTING_SEGMENT
    FROM GL_CODE_COMBINATIONS GCC, FND_SEGMENT_ATTRIBUTE_VALUES FSAV
   WHERE GCC.CODE_COMBINATION_ID = P_CC_ID
     AND FSAV.ATTRIBUTE_VALUE = 'Y'
     AND FSAV.APPLICATION_ID = 101
     AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
     AND FSAV.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
     AND FSAV.ID_FLEX_CODE = 'GL#';

Line: 3081

    lv_cursor_sql := 'SELECT FFV.Flex_Value Budget_Type';