The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
period_set_name
,accounted_period_type
FROM
gl_ledgers
WHERE ledger_id=l_ledger_id;
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;
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;
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;
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;
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;
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
)
);
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;
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;
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;
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;
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;
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
,''
);
SELECT COUNT(*)
INTO L_GT_COUNTS
FROM ja_cn_cfs_calculations_gt;
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
,''
);
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
);
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
);
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
);
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;
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;
SELECT COUNT(*)
INTO l_gt_counts1
FROM ja_cn_cfs_row_cgs_gt;
SELECT COUNT(*)
INTO L_GT_COUNTS
FROM ja_cn_cfs_calculations_gt;
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
);
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
);
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
);
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;
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
);
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
);
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
);
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;
UPDATE
ja_cn_cfs_row_cgs_gt
SET
type=l_type
WHERE CURRENT OF c_cal_axis_seqs;
SELECT
nvl(PRECISION,0)
FROM
fnd_currencies
WHERE currency_code=(SELECT
currency_code
FROM
gl_ledgers
WHERE ledger_id=l_ledger_id
);
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
);
SELECT
nvl(PRECISION,0)
FROM
fnd_currencies
WHERE currency_code=(SELECT
currency_code
FROM
gl_ledgers
WHERE ledger_id=l_ledger_id
);
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;
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;
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;
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;
UPDATE
ja_cn_cfs_row_cgs_gt
SET
amount=l_amount
WHERE CURRENT OF c_detailed_cfs_rows;
UPDATE
ja_cn_cfs_row_cgs_gt
SET
last_year_amount=l_lastyear_amount
WHERE CURRENT OF c_detailed_cfs_rows;
l_amount:=l_amount-l_cal_seq_amount; --Fix bug# 7481516 updated
l_lastyear_amount:=l_lastyear_amount-l_cal_seq_lastyear_amount; --Fix bug# 7481516 updated
UPDATE
ja_cn_cfs_row_cgs_gt
SET
amount=l_amount
,last_year_amount=l_lastyear_amount
WHERE CURRENT OF c_cal_cfs_rows;
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;
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;
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;
SELECT
currency_code
INTO
l_func_currency_code
FROM
gl_ledgers
WHERE
ledger_id=l_ledger_id ;
UPDATE
ja_cn_cfs_row_cgs_gt
SET
rowcnt=l_rowcnt
,lincnt=l_lincnt
WHERE CURRENT OF c_axis_seq;
SELECT
to_char(nvl(l_amount,0),'FM'||to_char(l_display_format,l_format_mask))
INTO
l_amount_display
FROM dual;
SELECT
to_char(nvl(l_amount,0),l_format_mask)
INTO
l_amount_display
FROM dual;
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;
SELECT
XMLCONCAT(l_xml_output
,l_xml_output_row
)
INTO
l_xml_output
FROM
dual;
SELECT
decode(l_calculation_lines.operator,'+','-','-','+','+')
INTO
l_operator
FROM dual;
SELECT
'FM'||to_char(l_display_format,l_format_mask)
INTO
l_final_display_format
FROM
dual;
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;
SELECT
XMLCONCAT(l_xml_output
,l_xml_output_row
)
INTO
l_xml_output
FROM
dual;
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;
SELECT
XMLCONCAT(l_xml_output
,l_xml_output_row
)
INTO
l_xml_output
FROM
dual;
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;