APPS.JA_CN_CFS_CALCULATE_PKG SQL Statements

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

Line: 90

SELECT
  period_set_name
 ,accounted_period_type
FROM
  gl_ledgers
WHERE ledger_id=l_ledger_id;

Line: 99

SELECT
  period_year
 ,period_num
 ,quarter_num
FROM
  gl_periods
WHERE period_set_name=l_period_set_name
  AND period_type=l_accounted_period_type
  AND period_name=l_period_name;

Line: 111

SELECT
  period_name
FROM
  gl_periods
WHERE period_set_name=l_period_set_name
  AND period_type=l_accounted_period_type
  AND period_year=l_period_year
  AND period_num<=l_period_num;

Line: 122

SELECT
  period_name
FROM
  gl_periods
WHERE period_set_name=l_period_set_name
  AND period_type=l_accounted_period_type
  AND period_year=l_period_year
  AND quarter_num=l_quarter_num
  AND period_num<=l_period_num;

Line: 302

SELECT
  DISTINCT
  rra.application_id
 ,rra.axis_seq
 ,rra.display_flag
 ,rra.display_zero_amount_flag
 ,rra.change_sign_flag
FROM
  rg_report_axes rra
 ,rg_report_calculations rrc
WHERE rra.axis_set_id=l_axis_set_id
  AND rra.axis_set_id=rrc.axis_set_id
  AND rra.axis_seq=rrc.axis_seq;

Line: 318

SELECT
  operator
 ,axis_seq_low
 ,axis_seq_high
 ,axis_name_low
 ,constant
FROM
  rg_report_calculations
WHERE application_id=l_application_id
  AND axis_set_id=l_axis_set_id
  AND axis_seq=l_axis_seq
ORDER BY calculation_seq;

Line: 333

SELECT
  rra.axis_seq
FROM
  rg_report_axes rra
WHERE rra.axis_set_id=l_axis_set_id
  AND rra.axis_seq BETWEEN l_axis_seq_low AND l_axis_seq_high
  --Fix bug# 7427067 begin
  AND (EXISTS (SELECT
                 rrac.axis_seq
              FROM
                rg_report_axis_contents rrac
              WHERE rrac.application_id=rra.application_id
                AND rrac.axis_set_id=rra.axis_set_id
                AND rrac.axis_seq=rra.axis_seq
             )
     OR
        EXISTS (SELECT
                  jccaa.axis_seq
               FROM
                 ja_cn_cfs_assignments_all jccaa
               WHERE jccaa.chart_of_accounts_id=l_coa
                 AND rra.axis_set_id=jccaa.axis_set_id
                 AND jccaa.axis_seq=rra.axis_seq
              )
      OR
        EXISTS (SELECT
                  rrc.axis_seq
FROM
                 rg_report_calculations rrc
               WHERE rrc.application_id=rra.application_id
                 AND rra.axis_set_id=rrc.axis_set_id
                 AND rrc.axis_seq=rra.axis_seq
              )
        );

Line: 372

SELECT
  axis_seq
FROM
  rg_report_axes
WHERE application_id=l_application_id
  AND axis_set_id=l_axis_set_id
  AND axis_name=l_axis_name_low;

Line: 382

SELECT
  axis_seq
 ,type
FROM
  ja_cn_cfs_row_cgs_gt
WHERE application_id=l_application_id
  AND axis_set_id=l_axis_set_id
  AND calculation_flag='Y'
ORDER BY axis_seq;

Line: 394

SELECT
  count(DISTINCT ccg.axis_seq)
FROM
  ja_cn_cfs_row_cgs_gt       crcg
 ,ja_cn_cfs_calculations_gt  ccg
WHERE crcg.application_id=l_application_id
  AND crcg.axis_set_id=l_axis_set_id
  AND (crcg.type IS NULL) OR (crcg.type<>'E')
  AND ccg.application_id=crcg.application_id
  AND ccg.axis_set_id=crcg.axis_set_id
  AND ccg.cal_axis_seq=l_cal_axis_seq;

Line: 409

SELECT
  DISTINCT ccg.axis_seq
FROM
  ja_cn_cfs_row_cgs_gt       crcg
 ,ja_cn_cfs_calculations_gt  ccg
WHERE crcg.application_id=l_application_id
  AND crcg.axis_set_id=l_axis_set_id
  AND (crcg.type IS NULL) OR (crcg.type<>'E')
  AND ccg.application_id=crcg.application_id
  AND ccg.axis_set_id=crcg.axis_set_id
  AND ccg.cal_axis_seq=l_cal_axis_seq;

Line: 423

SELECT
  calculation_seq
 ,operator
 ,operator_flag
 ,cal_axis_seq
 ,constant
FROM
  ja_cn_cfs_calculations_gt
WHERE application_id=l_application_id
  AND axis_set_id=l_axis_set_id
  AND axis_seq=l_calculated_axis_seq
  AND cal_axis_seq=l_cal_axis_seq
ORDER BY calculation_seq;

Line: 526

             INSERT
             INTO
               ja_cn_cfs_calculations_gt
               (application_id,axis_set_id
               ,axis_seq
               ,calculation_seq
               ,operator
               ,operator_flag
               ,cal_axis_seq
               ,constant
               )
             VALUES
               (l_application_id
               ,l_axis_set_id
               ,l_axis_seq
               ,l_calculation_seq
               ,l_operator
               ,l_operator_flag
               ,l_cal_axis_seq
               ,''
               );

Line: 549

               SELECT COUNT(*)
               INTO L_GT_COUNTS
               FROM ja_cn_cfs_calculations_gt;

Line: 568

           INSERT
           INTO
             ja_cn_cfs_calculations_gt
               (application_id,axis_set_id
               ,axis_seq
               ,calculation_seq
               ,operator
               ,operator_flag
               ,cal_axis_seq
               ,constant
               )
           VALUES
             (l_application_id
             ,l_axis_set_id
             ,l_axis_seq
             ,l_calculation_seq
             ,l_operator
             ,l_operator_flag
             ,l_cal_axis_seq
             ,''
             );

Line: 597

           INSERT
           INTO
             ja_cn_cfs_calculations_gt
              (application_id,axis_set_id
              ,axis_seq
              ,calculation_seq
              ,operator
              ,operator_flag
              ,cal_axis_seq
              ,constant
               )
           VALUES
             (l_application_id
             ,l_axis_set_id
             ,l_axis_seq
             ,l_calculation_seq
             ,l_operator
             ,l_operator_flag
             ,''
             ,l_constant
             );

Line: 638

    INSERT
      INTO
        ja_cn_cfs_row_cgs_gt
        (application_id
        ,axis_set_id
        ,axis_seq
        ,type
        ,calculation_flag
        ,display_flag
        ,display_zero_amount_flag
        ,change_sign_flag
        )
    VALUES
     (l_application_id
     ,l_axis_set_id
     ,l_axis_seq
     ,l_type
     ,'Y'
     ,l_display_flag
     ,l_display_zero_flag
     ,l_change_sign_flag
     );

Line: 689

        UPDATE
          ja_cn_cfs_row_cgs_gt crcg
        SET
          crcg.type='E'
        WHERE crcg.application_id=l_application_id
          AND crcg.axis_set_id=l_axis_set_id
          AND (crcg.type IS NULL OR crcg.type<>'E')
          AND crcg.axis_seq IN (SELECT
                                DISTINCT ccg.axis_seq
                                FROM
                                  ja_cn_cfs_calculations_gt ccg
                                WHERE ccg.application_id=crcg.application_id
                                  AND ccg.axis_set_id=crcg.axis_set_id
                                  AND ccg.cal_axis_seq=l_cal_axis_seq
                               );

Line: 720

            INSERT
            INTO
              ja_cn_cfs_calculations_gt
              (application_id,axis_set_id
              ,axis_seq
              ,calculation_seq
              ,operator
              ,operator_flag
              ,cal_axis_seq
              ,constant
              )
            SELECT
              l_application_id
             ,l_axis_set_id
             ,l_calculated_axis_seq
             ,l_cal_line.calculation_seq+calculation_seq/10000
             ,decode(l_cal_line.operator_flag*operator_flag
                    ,1
                    ,'+'
                    ,-1
                    ,'-'
                    ,'+'
                    )
             ,l_cal_line.operator_flag*operator_flag
             ,cal_axis_seq
             ,constant
            FROM
              ja_cn_cfs_calculations_gt
            WHERE application_id=l_application_id
              AND axis_set_id=l_axis_set_id
              AND axis_seq=l_cal_axis_seq;

Line: 758

          DELETE
          FROM
            ja_cn_cfs_calculations_gt
          WHERE application_id=l_application_id
            AND axis_set_id=l_axis_set_id
            AND axis_seq=l_calculated_axis_seq
            AND cal_axis_seq=l_cal_axis_seq;

Line: 785

               SELECT COUNT(*)
               INTO l_gt_counts1
               FROM ja_cn_cfs_row_cgs_gt;

Line: 793

               SELECT COUNT(*)
               INTO L_GT_COUNTS
               FROM ja_cn_cfs_calculations_gt;

Line: 860

SELECT
  rra.application_id
 ,rra.axis_seq
 ,rra.display_flag
 ,rra.display_zero_amount_flag
 ,rra.change_sign_flag
 ,rra.display_format
FROM
  rg_report_axes          rra
WHERE rra.axis_set_id=l_axis_set_id
  AND EXISTS (SELECT
                rrac.axis_seq
              FROM
                rg_report_axis_contents rrac
              WHERE rrac.application_id=rra.application_id
                AND rrac.axis_set_id=rra.axis_set_id
                AND rrac.axis_seq=rra.axis_seq
             );

Line: 881

SELECT
  DISTINCT
  rra.application_id
 ,jccaa.axis_seq
 ,rra.display_flag
 ,rra.display_zero_amount_flag
 ,rra.change_sign_flag
 ,rra.display_format
FROM
  ja_cn_cfs_assignments_all jccaa
 ,rg_report_axes            rra
WHERE jccaa.chart_of_accounts_id=l_coa
  AND jccaa.axis_set_id=l_axis_set_id
  AND rra.axis_set_id=jccaa.axis_set_id
  AND jccaa.axis_seq=rra.axis_seq
  AND jccaa.axis_seq NOT IN (SELECT
                               jccrcg.axis_seq
                             FROM
                               ja_cn_cfs_row_cgs_gt jccrcg
                             WHERE axis_set_id=l_axis_set_id
                             );

Line: 905

SELECT
  rra.application_id
 ,rra.axis_seq
 ,rra.display_flag
 ,rra.display_zero_amount_flag
 ,rra.change_sign_flag
 ,rra.display_format
FROM
  rg_report_axes rra
WHERE rra.axis_set_id=l_axis_set_id
  AND NOT EXISTS(SELECT
                   crcg.axis_seq
                 FROM
                   ja_cn_cfs_row_cgs_gt crcg
                 WHERE crcg.axis_set_id=rra.axis_set_id
                   AND crcg.axis_seq=rra.axis_seq
                 );

Line: 925

SELECT
  application_id
 ,axis_seq
FROM
  ja_cn_cfs_row_cgs_gt
WHERE axis_set_id=l_axis_set_id
  AND calculation_flag='Y'
  AND (type IS NULL OR type<>'E')
FOR UPDATE;

Line: 965

    INSERT
    INTO
      ja_cn_cfs_row_cgs_gt
      (application_id
      ,axis_set_id
      ,axis_seq
      ,type
      ,calculation_flag
      ,display_flag
      ,display_zero_amount_flag
      ,change_sign_flag
      ,display_format
      )
    VALUES
      (l_axis_seq_fsg.application_id
      ,l_axis_set_id
      ,l_axis_seq_fsg.axis_seq
      ,'F'
      ,'N'
      ,l_axis_seq_fsg.display_flag
      ,l_axis_seq_fsg.display_zero_amount_flag
      ,l_axis_seq_fsg.change_sign_flag
      ,l_axis_seq_fsg.display_format
      );

Line: 1002

    INSERT
    INTO
      ja_cn_cfs_row_cgs_gt
      (application_id
      ,axis_set_id
      ,axis_seq
      ,type
      ,calculation_flag
      ,display_flag
      ,display_zero_amount_flag
      ,change_sign_flag
      ,display_format
      )
    VALUES
      (l_axis_seq_cfs.application_id
      ,l_axis_set_id
      ,l_axis_seq_cfs.axis_seq
      ,'C'
      ,'N'
      ,l_axis_seq_cfs.display_flag
      ,l_axis_seq_cfs.display_zero_amount_flag
      ,l_axis_seq_cfs.change_sign_flag
      ,l_axis_seq_cfs.display_format
      );

Line: 1038

    INSERT
    INTO
      ja_cn_cfs_row_cgs_gt
      (application_id
      ,axis_set_id
      ,axis_seq
      ,type
      ,calculation_flag
      ,display_flag
      ,display_zero_amount_flag
      ,change_sign_flag
      ,display_format
      )
    VALUES
      (l_axis_seq_desc.application_id
      ,l_axis_set_id
      ,l_axis_seq_desc.axis_seq
      ,'F'
      ,'N'
      ,l_axis_seq_desc.display_flag
      ,l_axis_seq_desc.display_zero_amount_flag
      ,l_axis_seq_desc.change_sign_flag
      ,l_axis_seq_desc.display_format
      );

Line: 1086

      SELECT
        DISTINCT crcg.type
      INTO
        l_cal_type
      FROM
        ja_cn_cfs_row_cgs_gt     crcg
       ,ja_cn_cfs_calculations_gt ccg
      WHERE ccg.application_id=l_cal_axis_seq.application_id
        AND ccg.axis_set_id=l_axis_set_id
        AND ccg.axis_seq=l_cal_axis_seq.axis_seq
        AND crcg.application_id=ccg.application_id
        AND crcg.axis_set_id=ccg.axis_set_id
        AND crcg.axis_seq=ccg.cal_axis_seq;

Line: 1134

    UPDATE
      ja_cn_cfs_row_cgs_gt
    SET
      type=l_type
    WHERE CURRENT OF c_cal_axis_seqs;

Line: 1231

SELECT
  nvl(PRECISION,0)
FROM
  fnd_currencies
WHERE currency_code=(SELECT
                       currency_code
                     FROM
                       gl_ledgers
                     WHERE ledger_id=l_ledger_id
                     );

Line: 1246

SELECT
  nvl(SUM(round(func_amount,decode(l_rounding_option,'R',l_precision,50))),0)
FROM
  ja_cn_cfs_activities_all
WHERE period_name=l_period_name
  AND legal_entity_id=l_legal_entity_id
  -- added for CNAO Enhancement begin
  AND (
       ( l_balancing_segment_value IS NULL
        OR
         l_balancing_segment_value = balancing_segment
       )
       AND EXISTS
        (
           SELECT *
            FROM Gl_Ledgers Lg,
                 Gl_Ledger_Relationships Rs,
                 Gl_Ledger_Norm_Seg_Vals nbsv
            WHERE lg.bal_seg_value_option_code='I'
             AND Rs.Application_Id = 101
             AND Lg.Ledger_Id = l_ledger_id              --Using variable ledger_id
             AND Lg.Ledger_Id = Rs.Target_Ledger_Id
             AND Nvl(Lg.Complete_Flag, 'Y') = 'Y'
             AND nbsv.Segment_Type_Code = 'B'
             AND Nvl(nbsv.Status_Code, 'I') <> 'D'
             AND(( Rs.Relationship_Type_Code = 'NONE'
               AND Rs.Target_Ledger_Id = LG.Ledger_Id
                 )
              OR ( Rs.Target_Ledger_Category_Code = 'ALC'
               AND Rs.Relationship_Type_Code IN ('SUBLEDGER', 'JOURNAL')
               AND Rs.Source_Ledger_Id = LG.Ledger_Id
                 )
               )
             AND nbsv.ledger_id=Lg.Ledger_Id
             AND nbsv.legal_entity_id = l_legal_entity_id  --Using variable legal_entity_id
             AND nbsv.Segment_Value = balancing_segment
          )
        )
  -- added for CNAO Enhancement end
  AND detailed_cfs_item IN (SELECT
                              detailed_cfs_item
                            FROM
                              ja_cn_cfs_assignments_all
                            WHERE chart_of_accounts_id=p_coa --updated by lyb
                              AND axis_set_id=l_axis_set_id
                              AND axis_seq=l_axis_seq
                           );

Line: 1481

SELECT
  nvl(PRECISION,0)
FROM
  fnd_currencies
WHERE currency_code=(SELECT
                       currency_code
                     FROM
                       gl_ledgers
                     WHERE ledger_id=l_ledger_id
                     );

Line: 1494

SELECT
  axis_seq
FROM
  ja_cn_cfs_row_cgs_gt
WHERE axis_set_id=l_axis_set_id
  AND type='C'
  AND calculation_flag='N'
FOR UPDATE;

Line: 1505

SELECT
  axis_seq
FROM
  ja_cn_cfs_row_cgs_gt
WHERE axis_set_id=l_axis_set_id
  AND type='C'
  AND calculation_flag='Y'
FOR UPDATE;

Line: 1516

SELECT
  operator
 ,cal_axis_seq
 ,constant
FROM
 ja_cn_cfs_calculations_gt
WHERE axis_set_id=l_axis_set_id
  AND axis_seq=l_axis_seq
ORDER BY calculation_seq;

Line: 1528

SELECT
  nvl(amount,0)
 ,nvl(last_year_amount,0)
FROM
  ja_cn_cfs_row_cgs_gt
WHERE axis_set_id=l_axis_set_id
  AND axis_seq=l_cal_axis_seq;

Line: 1642

    UPDATE
      ja_cn_cfs_row_cgs_gt
    SET
      amount=l_amount
    WHERE CURRENT OF c_detailed_cfs_rows;

Line: 1664

      UPDATE
        ja_cn_cfs_row_cgs_gt
      SET
        last_year_amount=l_lastyear_amount
      WHERE CURRENT OF c_detailed_cfs_rows;

Line: 1708

        l_amount:=l_amount-l_cal_seq_amount;     --Fix bug# 7481516 updated

Line: 1709

        l_lastyear_amount:=l_lastyear_amount-l_cal_seq_lastyear_amount; --Fix bug# 7481516 updated

Line: 1714

    UPDATE
      ja_cn_cfs_row_cgs_gt
    SET
      amount=l_amount
     ,last_year_amount=l_lastyear_amount
    WHERE CURRENT OF c_cal_cfs_rows;

Line: 1836

SELECT
  axis_seq
FROM
  ja_cn_cfs_row_cgs_gt
WHERE axis_set_id=l_axis_set_id
  AND display_flag='Y'
ORDER BY axis_seq
FOR UPDATE;

Line: 1847

SELECT
  axis_seq
 ,type
 ,calculation_flag
 ,display_zero_amount_flag
 ,change_sign_flag
 ,display_format
 ,amount
 ,rowcnt
 ,lincnt
FROM
  ja_cn_cfs_row_cgs_gt
WHERE axis_set_id=l_axis_set_id
  AND display_flag='Y'
ORDER BY axis_seq;

Line: 1865

SELECT
  jcccg.operator
 ,jccrcg.lincnt
 ,jccrcg.change_sign_flag
FROM
  ja_cn_cfs_calculations_gt jcccg
 ,ja_cn_cfs_row_cgs_gt jccrcg
WHERE jcccg.axis_set_id=l_axis_set_id
  AND jcccg.axis_seq=l_axis_seq
  AND jcccg.axis_set_id=jccrcg.axis_set_id
  AND jcccg.cal_axis_seq=jccrcg.axis_seq
ORDER BY jcccg.calculation_seq;

Line: 1938

  SELECT
    currency_code
  INTO
    l_func_currency_code
  FROM
    gl_ledgers
  WHERE
    ledger_id=l_ledger_id ;

Line: 2006

    UPDATE
      ja_cn_cfs_row_cgs_gt
    SET
      rowcnt=l_rowcnt
     ,lincnt=l_lincnt
    WHERE CURRENT OF c_axis_seq;

Line: 2066

           SELECT
             to_char(nvl(l_amount,0),'FM'||to_char(l_display_format,l_format_mask))
           INTO
             l_amount_display
           FROM dual;

Line: 2072

           SELECT
             to_char(nvl(l_amount,0),l_format_mask)
           INTO
             l_amount_display
           FROM dual;

Line: 2086

       SELECT
         XMLELEMENT("fsgRptLine"
                   ,XMLATTRIBUTES(l_rptcnt AS "RptCnt"
                                 ,l_rowcnt AS "RowCnt"
                                 ,l_lincnt AS "LinCnt"
                                 )
                   ,XMLELEMENT("fsgRptCell"
                                ,XMLATTRIBUTES(l_colcnt AS "ColCnt"
                                               )
                                ,nvl(l_amount_display,0)
                                )
                     )
         INTO
           l_xml_output_row
         FROM
           dual;

Line: 2108

           SELECT
             XMLCONCAT(l_xml_output
                      ,l_xml_output_row
                      )
           INTO
             l_xml_output
           FROM
             dual;

Line: 2142

           SELECT
             decode(l_calculation_lines.operator,'+','-','-','+','+')
           INTO
             l_operator
           FROM dual;

Line: 2167

         SELECT
           'FM'||to_char(l_display_format,l_format_mask)
         INTO
           l_final_display_format
         FROM
           dual;

Line: 2179

       SELECT
           XMLELEMENT("Formula"
                     ,XMLATTRIBUTES(l_display_zero_amount_flag AS "DisplayZero"
                                   ,l_change_sign_flag AS "ChangeSign"
                                   ,l_final_display_format AS "DisplayFormat"
                                   )
                     ,l_formula
                     )
       INTO
         l_xml_output_row
       FROM
         dual;

Line: 2197

         SELECT
           XMLCONCAT(l_xml_output
                    ,l_xml_output_row
                    )
         INTO
           l_xml_output
         FROM
           dual;

Line: 2218

       SELECT
         XMLELEMENT("fsgRptLine"
                   ,XMLATTRIBUTES(l_rptcnt AS "RptCnt"
                                 ,l_rowcnt AS "RowCnt"
                                 ,l_lincnt AS "LinCnt"
                                 )
                   ,XMLELEMENT("fsgRptCell"
                              ,XMLATTRIBUTES(l_colcnt AS "ColCnt"
                                             )
                              ,l_error_message
                              )
                    )
       INTO
         l_xml_output_row
       FROM
         dual;

Line: 2240

         SELECT
           XMLCONCAT(l_xml_output
                    ,l_xml_output_row
                    )
         INTO
          l_xml_output
         FROM
           dual;

Line: 2270

  SELECT XMLELEMENT("MasterReport"
                   ,XMLATTRIBUTES('http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi"
                                 ,'http://www.oracle.com/fsg/2002-03-20/' AS "xmlns:fsg"
                                 ,'http://www.oracle.com/2002-03-20/fsg.xsd' AS "xsi:schemaLocation"
                                 )
                   ,l_xml_output
                   )
  INTO l_xml_output_root
  FROM dual;