[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_select1 VARCHAR2(32000);
v_select2 VARCHAR2(32000);
v_select3 VARCHAR2(32000);
v_select4 VARCHAR2(32000);
v_select5 VARCHAR2(32000);
v_glbal_select VARCHAR2(32000);
v_fct1_attr_select VARCHAR2(32000);
SELECT chart_of_accounts_id, currency_code
INTO v_chart_of_accounts_id, v_currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = v_sob ;
SELECT trunc(end_date), period_num, period_year
INTO v_end_date, v_period_num, v_period_fiscal_year
FROM gl_period_statuses
WHERE ledger_id = v_sob
AND application_id = '101'
AND period_name = v_period_name;
SELECT fv_cfs_rep_temp_s.NEXTVAL
INTO v_sequence_id FROM DUAL;
SELECT flex_value_set_id
INTO v_acct_flex_value_set_id
FROM fnd_id_flex_segments_vl
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = v_chart_of_accounts_id
AND enabled_flag = 'Y'
AND segment_num = seg_number;
SELECT flex_value_set_id
INTO v_bal_flex_value_set_id
FROM fnd_id_flex_segments_vl
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = v_chart_of_accounts_id
AND enabled_flag = 'Y'
AND segment_num = seg_number;
v_insert_statement varchar2(25000);
SELECT application_column_name , flex_value_set_id
FROM fnd_id_flex_segments
WHERE id_flex_code = 'GL#'
AND application_id = 101
AND id_flex_num = v_chart_of_accounts_id;
SELECT child_flex_value_low,child_flex_value_high
FROM fnd_flex_value_hierarchies
WHERE parent_FLEX_value = seg
AND flex_value_set_id = sid;
SELECT d.line_id,d.line_detail_id,
segment1, segment2, segment3, segment4,
segment5, segment6, segment7, segment8,
segment9, segment10, segment11, segment12,
segment13, segment14, segment15, segment16,
segment17, segment18, segment19, segment20,
segment21, segment22, segment23, segment24,
segment25, segment26, segment27, segment28,
segment29, segment30
FROM fv_cfs_rep_line_dtl d,
fv_cfs_rep_lines L
WHERE l.report_type = v_report_type
AND d.line_id = l.line_id
AND l.set_of_books_id = v_sob
ORDER BY 2;
select count(*) into l_cnt
FROM fnd_flex_value_hierarchies
where parent_FLEX_value = v_seg(l_segno)
AND flex_value_set_id = flex_rec.flex_value_set_id;
v_insert_statement := 'insert into FV_CCID_CFS_GT(
detail_id,
ccid)
select ' ||
crec_rec.line_detail_id
|| ', code_combination_id '
|| ' from gl_code_combinations WHERE ' || v_statement
|| ' and template_id is null and '
|| ' chart_of_accounts_id = :B_CHART_OF_ACCOUNTS_ID
and not exists (select code_combination_id
from fv_ccid_CFS_GT FCT '
|| 'where fct.detail_id = :b_line_detail_id '|| ')';
execute immediate v_insert_statement
using V_CHART_OF_ACCOUNTS_ID, crec_rec.line_detail_id;
SELECT UPPER(glflex.application_column_name) column_name, flex_value_set_id
FROM fnd_id_flex_segments glflex
WHERE glflex.application_id = 101
AND glflex.id_flex_num = v_chart_of_accounts_id
AND glflex.id_flex_code = 'GL#'
ORDER BY glflex.application_column_name;
v_fct1_attr_select :=
' SELECT SUM(NVL(DECODE(:cv_balance_type,
''B'', ROUND(NVL(fctbal.begin_balance,0),2),
''E'', ROUND(NVL(fctbal.balance_amount,0))),0) )
FROM fv_cfs_rep_line_dtl dets,
fv_ccid_cfs_gt fvcc,
fv_facts1_period_attributes fctbal
WHERE dets.line_id = :cv_line_id
AND dets.line_detail_id = :cv_line_detail_id
AND dets.line_detail_id = fvcc.detail_id
AND fctbal.ccid = fvcc.ccid
AND fctbal.set_of_books_id = :b_sob
AND fctbal.period_year = :cv_period_fiscal_year
AND nvl(dets.cust_non_cust, nvl(fctbal.cust_non_cust, 1)) = nvl(fctbal.cust_non_cust, 1)
AND nvl(dets.exch_non_exch, nvl(fctbal.exch_non_exch, 1)) = nvl(fctbal.exch_non_exch, 1)
AND EXISTS
(SELECT 1
FROM fv_fund_parameters ffp
WHERE set_of_books_id = :b_sob
AND fund_category like nvl(dets.fund_category, ''%'')
AND ffp.fund_value = fctbal.fund_value
AND ( (dets.fund_status = ''E'' and trunc(fund_expire_date) <= :cv_end_date )
OR (dets.fund_status = ''U''
and (trunc(fund_expire_date) >= :cv_end_date or fund_expire_date is null)
and (trunc(fund_cancel_date) > :cv_end_date or fund_cancel_date is null))
OR (nvl(dets.fund_status,''B'') = ''B'' ))) ';
l_out := v_fct1_attr_select;
dbms_sql.parse(v_glbal_curid, v_fct1_attr_select, dbms_sql.v7);
'SELECT ROUND(SUM(NVL(ffrt.amount,0) ),2) ';
v_glbal_select :=
' SELECT /*+ USE_HASH (glbal) */
NVL(DECODE(:cv_balance_type,
''B'', ROUND(NVL(SUM(NVL(glbal.begin_balance_dr,0) -
NVL(glbal.begin_balance_cr,0)),0),2),
''E'', ROUND(NVL(SUM((NVL(glbal.begin_balance_dr,0) -
NVL(glbal.begin_balance_cr,0)) +
(NVL(glbal.period_net_dr,0) -
NVL(glbal.period_net_cr,0))),0),2)),0)
FROM fv_cfs_rep_line_dtl dets,
fv_ccid_cfs_gt fvcc,
gl_code_combinations glc,
gl_balances glbal
WHERE dets.line_id = :cv_line_id
AND dets.line_detail_id = :cv_line_detail_id
AND dets.line_detail_id = fvcc.detail_id
AND glc.code_combination_id = fvcc.ccid
AND glc.chart_of_accounts_id = :b_chart_of_accounts_id
AND glbal.code_combination_id = glc.code_combination_id
AND glbal.ledger_id = :b_sob
AND glbal.period_year = :cv_period_fiscal_year
AND glbal.period_num = :cv_period_num
--AND glbal.currency_code <> ''STAT''
AND glbal.currency_code = :v_currency_code
AND glbal.actual_flag = ''A''
AND EXISTS
(SELECT 1
FROM fv_fund_parameters ffp
WHERE set_of_books_id = :b_sob
AND fund_category like nvl(dets.fund_category, ''%'')
AND ffp.fund_value = glc.'||v_bal_seg_name||'
AND ( (dets.fund_status = ''E'' and trunc(fund_expire_date) <= :cv_end_date )
OR (dets.fund_status = ''U''
and (trunc(fund_expire_date) >= :cv_end_date or fund_expire_date is null)
and (trunc(fund_cancel_date) > :cv_end_date or fund_cancel_date is null))
OR (nvl(dets.fund_status,''B'') = ''B'' ))) ';
l_out := v_glbal_select;
dbms_sql.parse(v_sbr_curid, v_glbal_select, dbms_sql.v7);
SELECT line_id, line_label, sequence_number,
line_number, line_type, natural_balance_type, by_recipient
FROM fv_cfs_rep_lines
WHERE set_of_books_id = v_sob
AND report_type = v_report_type
ORDER BY sequence_number;
SELECT count(*)
INTO l_line_cnt
FROM fv_cfs_rep_line_dtl
WHERE line_id = v_line_id;
SELECT count(*)
INTO l_line_cnt
FROM fv_cfs_rep_line_calc
WHERE line_id = v_line_id;
SELECT chart_of_accounts_id, currency_code
INTO v_chart_of_accounts_id, v_currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = v_sob ;
SELECT trunc(end_date), period_num, period_year, end_date
INTO v_end_date, v_period_num, v_period_fiscal_year, v_end_period_end_date
FROM gl_period_statuses
WHERE ledger_id = v_sob
AND application_id = '101'
AND period_name = v_period_name;
SELECT period_num, period_name, end_date
INTO v_begin_period, v_begin_period_name, v_begin_period_end_date
FROM gl_period_statuses
WHERE ledger_id = v_sob
AND period_year = v_period_fiscal_year
AND adjustment_period_flag = 'N'
AND application_id = '101'
AND period_num =
(SELECT MIN(period_num)
FROM gl_period_statuses
WHERE ledger_id = v_sob
AND period_year = v_period_fiscal_year
AND adjustment_period_flag = 'N'
AND application_id = '101' );
SELECT period_num, period_name, end_date
INTO v_begin_period_1, v_begin_period_name_1, v_begin_period_1_end_date
FROM gl_period_statuses
WHERE ledger_id = v_sob
AND period_year = v_period_fiscal_year-1
AND adjustment_period_flag = 'N'
AND application_id = '101'
AND period_num =
(SELECT MIN(period_num)
FROM gl_period_statuses
WHERE ledger_id = v_sob
AND period_year = v_period_fiscal_year-1
AND adjustment_period_flag = 'N'
AND application_id = '101') ;
SELECT end_date
INTO v_end_period_1_end_date
FROM gl_period_statuses
WHERE ledger_id = v_sob
AND period_year = v_period_fiscal_year-1
AND application_id = '101'
AND period_num = v_period_num;
SELECT line_detail_id, balance_type, cum_res,
unexp_approp, budget_col, nbfa_col, flex_further_def, fed_non_fed, exch_non_exch, cust_non_cust,
DECODE(v_acc_seg_name, 'SEGMENT1', SEGMENT1, 'SEGMENT11', SEGMENT11, 'SEGMENT21', SEGMENT21,
'SEGMENT2', SEGMENT2, 'SEGMENT12', SEGMENT12, 'SEGMENT22', SEGMENT22,
'SEGMENT3', SEGMENT3, 'SEGMENT13', SEGMENT13, 'SEGMENT23', SEGMENT23,
'SEGMENT4', SEGMENT4, 'SEGMENT14', SEGMENT14, 'SEGMENT24', SEGMENT24,
'SEGMENT5', SEGMENT5, 'SEGMENT15', SEGMENT15, 'SEGMENT25', SEGMENT25,
'SEGMENT6', SEGMENT6, 'SEGMENT16', SEGMENT16, 'SEGMENT26', SEGMENT26,
'SEGMENT7', SEGMENT7, 'SEGMENT17', SEGMENT17, 'SEGMENT27', SEGMENT27,
'SEGMENT8', SEGMENT8, 'SEGMENT18', SEGMENT18, 'SEGMENT28', SEGMENT28,
'SEGMENT9', SEGMENT9, 'SEGMENT19', SEGMENT19, 'SEGMENT29', SEGMENT29,
'SEGMENT10', SEGMENT10, 'SEGMENT20', SEGMENT20, 'SEGMENT30', SEGMENT30) account_number,
segment1 || '.' || segment2 || '.' || segment3 || '.' || segment4 || '.' || segment5 || '.' ||
segment6 || '.' || segment7 || '.' || segment8 || '.' || segment9 || '.' || segment10 || '.' ||
segment11 || '.' || segment12 || '.' || segment13 || '.' || segment14 || '.' || segment15 || '.' ||
segment16 || '.' || segment17 || '.' || segment18 || '.' || segment19 || '.' || segment20 || '.' ||
segment21 || '.' || segment22 || '.' || segment23 || '.' || segment24 || '.' || segment25 || '.' ||
segment26 || '.' || segment27 || '.' || segment28 || '.' || segment29 || '.' || segment30
concatenated_segments
FROM fv_cfs_rep_line_dtl
WHERE line_id = v_line_id;
SELECT concatenated_segment_delimiter
INTO l_delimiter
FROM fnd_id_flex_structures
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = v_chart_of_accounts_id;
SELECT RTRIM(REPLACE(detail_rec.concatenated_segments, '.', l_delimiter),l_delimiter)
INTO l_conc_segs
FROM dual;
SELECT SUBSTR(compiled_value_attributes, 5, 1)
INTO l_account_type
FROM fnd_flex_values
WHERE flex_value = detail_rec.account_number
AND flex_value_set_id = v_acct_flex_value_set_id;
SELECT parent_flex_value
INTO l_account_number
FROM fnd_flex_value_hierarchies
WHERE detail_rec.account_number
BETWEEN child_flex_value_low AND child_flex_value_high
AND flex_value_set_id = v_acct_flex_value_set_id
AND ROWNUM = 1;
SELECT SUBSTR(compiled_value_attributes, 5, 1)
INTO l_account_type
FROM fnd_flex_values
WHERE flex_value = l_account_number
AND flex_value_set_id = v_acct_flex_value_set_id;
Please select Natural Balance for any line with a Balance Type
of Net Increase or Net Decrease.';
SELECT REPLACE(l_log_mesg, '*****', v_col_1_amt || ', ' || v_col_2_amt || ', '
|| v_col_3_amt || ', ' || v_col_4_amt)
INTO l_log_mesg FROM dual;
SELECT calc_sequence_number, line_low, line_high, line_low_type, line_high_type,
operator, cum_res, unexp_approp, budget_col, nbfa_col
FROM fv_cfs_rep_line_calc
WHERE line_id = v_line_id
ORDER BY calc_sequence_number;
SELECT col_1_amt, col_2_amt,
col_3_amt, col_4_amt
FROM fv_cfs_rep_temp
WHERE line_id = p_line_id
AND sequence_id = v_sequence_id;
SELECT line_id
FROM fv_cfs_rep_lines
WHERE sequence_number >=
(SELECT sequence_number FROM fv_cfs_rep_lines
WHERE line_id = p_lineid_1 )
AND sequence_number <=
(SELECT sequence_number FROM fv_cfs_rep_lines
WHERE line_id = p_lineid_2 )
AND report_type = v_report_type;
INSERT INTO fv_cfs_rep_temp(sequence_id, line_id, line_label, col_1_amt,
col_2_amt, col_3_amt, col_4_amt)
VALUES(v_sequence_id, v_line_id, v_line_label,
ABS(ROUND(v_col_1_amt/v_units)),
ABS(ROUND(v_col_2_amt/v_units)),
ABS(ROUND(v_col_3_amt/v_units)),
ABS(ROUND(v_col_4_amt/v_units)));
INSERT INTO fv_cfs_rep_temp(sequence_id, line_id, line_label, col_1_amt,
col_2_amt, col_3_amt, col_4_amt)
VALUES(v_sequence_id, v_line_id, v_line_label,
ABS(v_col_1_amt),
ABS(v_col_2_amt),
ABS(v_col_3_amt),
ABS(v_col_4_amt));
INSERT INTO fv_cfs_rep_temp(sequence_id, line_id, line_label, col_1_amt,
col_2_amt, col_3_amt, col_4_amt)
VALUES(v_sequence_id, v_line_id, v_line_label,
DECODE(v_natural_balance_type, 'C', ROUND(v_col_1_amt/v_units) * -1, ROUND(v_col_1_amt/v_units)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_2_amt/v_units) * -1, ROUND(v_col_2_amt/v_units)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_3_amt/v_units) * -1, ROUND(v_col_3_amt/v_units)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_4_amt/v_units) * -1, ROUND(v_col_4_amt/v_units)));
INSERT INTO fv_cfs_rep_temp(sequence_id, line_id, line_label, col_1_amt,
col_2_amt, col_3_amt, col_4_amt)
VALUES(v_sequence_id, v_line_id, v_line_label,
DECODE(v_natural_balance_type, 'C', v_col_1_amt * -1, v_col_1_amt),
DECODE(v_natural_balance_type, 'C', v_col_2_amt * -1, v_col_2_amt),
DECODE(v_natural_balance_type, 'C', v_col_3_amt * -1, v_col_3_amt),
DECODE(v_natural_balance_type, 'C', v_col_4_amt * -1, v_col_4_amt));