[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);
g_insert_count NUMBER;
c_begin_select VARCHAR2(200);
c_end_select VARCHAR2(200);
SELECT DISTINCT line.sbr_line_id sbr_line_id ,
line.sbr_line_number sbr_line_number ,
line.sbr_line_type_code sbr_line_type_code ,
line.sbr_natural_balance_type sbr_natural_balance_type,
line.sbr_fund_category sbr_line_category ,
line.sbr_report_line_number sbr_report_line_number ,
line.sbr_line_label sbr_line_label ,
line.sbr_gl_balance sbr_gl_balance
FROM fv_sbr_definitions_lines line
WHERE line.set_of_books_id = v_sob
AND (line.sbr_line_type_code) IN ('T', 'D', 'D2', 'S') -- To handle subtotals
ORDER BY line.sbr_line_number;
SELECT sbr_line_acct_id ,
sbr_balance_type ,
acct_number ,
direct_or_reimb_code ,
apportionment_category_code,
category_b_code ,
prc_code ,
advance_code ,
availability_time ,
bea_category_code ,
borrowing_source_code ,
transaction_partner ,
year_of_budget_authority ,
prior_year_adjustment ,
authority_type ,
tafs_status ,
availability_type ,
expiration_flag ,
fund_type ,
financing_account_code ,
sbr_treasury_symbol_id
FROM fv_sbr_definitions_accts
WHERE sbr_line_id = c_sbr_line_id
and set_of_books_id = v_sob;
SELECT treasury_symbol,
treasury_symbol_id
FROM fv_treasury_symbols
WHERE TIME_FRAME IN ('A','M', 'X')
AND (FUND_GROUP_CODE NOT BETWEEN '3800' AND '3899')
AND (FUND_GROUP_CODE NOT BETWEEN '6001' AND '6999')
-- AND treasury_symbol IN ('33-X-3333','11-08-0110','11-04-0100','03-X-0366','03-06-0333')
AND set_of_books_id = p_sob
ORDER BY treasury_symbol;
select distinct fft.treasury_symbol_id from fv_facts_temp fft,
fv_treasury_symbols fts
where sgl_acct_number like p_acc_num||'%'
and fft.treasury_symbol_id= fts.treasury_symbol_id
and fft.fct_int_record_type='BLK_DTL'
and fts.set_of_books_id=v_sob;
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 = parm_application_id
AND period_name = v_period_name;
SELECT fv_cfs_rep_temp_s.NEXTVAL
INTO v_sequence_id
FROM DUAL;
SELECT count(*) into l_count
FROM fv_sbr_definitions_lines
WHERE set_of_books_id = p_set_of_books_id;
SELECT count(*) into l_count_acct
FROM fv_sbr_definitions_accts
WHERE set_of_books_id = p_set_of_books_id;
SELECT period_num
INTO report_period_num
FROM gl_period_statuses
WHERE application_id = parm_application_id
AND set_of_books_id = v_sob
AND period_name = v_period_name
AND period_year = v_period_fiscal_year;
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 d.sbr_line_id ,
d.sbr_line_acct_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_sbr_definitions_accts d,
fv_sbr_definitions_lines l
WHERE d.sbr_line_id = l.sbr_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_sbr_ccids_gt(
sbr_line_acct_id,
ccid)
select ' || sbr_crec_rec.sbr_line_acct_id || ', code_combination_id ' ||
' from gl_code_combinations WHERE ' ||v_acc_seg_name || 'like '':b_account_number%'''' and template_id is null and '
|| ' chart_of_accounts_id = :B_CHART_OF_ACCOUNTS_ID
and not exists (select code_combination_id
from fv_sbr_ccids_gt FCT ' || 'where fct.detail_id = :b_line_detail_id '|| ')';
EXECUTE immediate v_insert_statement USING V_CHART_OF_ACCOUNTS_ID,
sbr_crec_rec.sbr_line_acct_id;
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);
v_fct1_sel := '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,
scnp_elim,
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
|| ', '
|| v_col_5_amt
|| ', '
|| v_col_6_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 ,
scnp_elim
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 ,
col_5_amt ,
col_6_amt ,
col_7_amt ,
col_8_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;
SELECT calc_sequence_number,
line_low ,
line_high ,
line_low_type ,
line_high_type ,
operator
FROM fv_sbr_rep_line_calc
WHERE line_id = c_sbr_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 sbr_line_id
FROM fv_sbr_definitions_lines
WHERE sbr_line_number >=
(SELECT sbr_line_number
FROM fv_sbr_definitions_lines
WHERE sbr_line_id = p_lineid_1
)
AND sbr_line_number <=
(SELECT sbr_line_number
FROM fv_sbr_definitions_lines
WHERE sbr_line_id = p_lineid_2
);
INSERT
INTO fv_cfs_rep_temp
(
sequence_id,
line_id ,
line_label ,
col_1_amt ,
col_2_amt ,
col_3_amt ,
col_4_amt,
PERIOD_YEAR,
PERIOD_NUM,
REPORTY_TYPE,
LEDGER_ID
)
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)),
v_period_fiscal_year,
v_period_num ,
v_report_type,
v_sob
);
INSERT
INTO fv_cfs_rep_temp
(
sequence_id,
line_id ,
line_label ,
col_1_amt ,
col_2_amt ,
col_3_amt ,
col_4_amt,
PERIOD_YEAR,
PERIOD_NUM,
REPORTY_TYPE,
LEDGER_ID
)
VALUES
(
v_sequence_id ,
v_line_id ,
v_line_label ,
ABS(ROUND(v_col_1_amt)),
ABS(ROUND(v_col_2_amt)),
ABS(ROUND(v_col_3_amt)),
ABS(ROUND(v_col_4_amt)),
v_period_fiscal_year,
v_period_num ,
v_report_type,
v_sob
);
INSERT
INTO fv_cfs_rep_temp
(
sequence_id,
line_id ,
line_label ,
col_1_amt ,
col_2_amt ,
col_3_amt ,
col_4_amt,
PERIOD_YEAR,
PERIOD_NUM,
REPORTY_TYPE,
LEDGER_ID
)
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),
v_period_fiscal_year,
v_period_num ,
v_report_type,
v_sob
);
INSERT
INTO fv_cfs_rep_temp
(
sequence_id,
line_id ,
line_label ,
col_1_amt ,
col_2_amt ,
col_3_amt ,
col_4_amt ,
col_5_amt ,
col_6_amt ,
col_7_amt ,
col_8_amt,
PERIOD_YEAR,
PERIOD_NUM,
REPORTY_TYPE,
LEDGER_ID
)
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)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_5_amt/v_units) * -1, ROUND(v_col_5_amt/v_units)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_6_amt/v_units) * -1, ROUND(v_col_6_amt/v_units)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_7_amt/v_units) * -1, ROUND(v_col_7_amt/v_units)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_8_amt/v_units) * -1, ROUND(v_col_8_amt/v_units)),
v_period_fiscal_year,
v_period_num ,
v_report_type,
v_sob
);
INSERT
INTO fv_cfs_rep_temp
(
sequence_id,
line_id ,
line_label ,
col_1_amt ,
col_2_amt ,
col_3_amt ,
col_4_amt ,
col_5_amt ,
col_6_amt ,
col_7_amt ,
col_8_amt,
PERIOD_YEAR,
PERIOD_NUM,
REPORTY_TYPE,
LEDGER_ID
)
VALUES
(
v_sequence_id ,
v_line_id ,
v_line_label ,
DECODE(v_natural_balance_type, 'C', ROUND(v_col_1_amt) * -1, ROUND(v_col_1_amt)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_2_amt) * -1, ROUND(v_col_2_amt)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_3_amt) * -1, ROUND(v_col_3_amt)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_4_amt) * -1, ROUND(v_col_4_amt)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_5_amt) * -1, ROUND(v_col_5_amt)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_6_amt) * -1, ROUND(v_col_6_amt)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_7_amt) * -1, ROUND(v_col_7_amt)),
DECODE(v_natural_balance_type, 'C', ROUND(v_col_8_amt) * -1, ROUND(v_col_8_amt)),
v_period_fiscal_year,
v_period_num ,
v_report_type,
v_sob
);
fnd_file.log, 'TestMod'||'insert '|| ROUND(v_col_1_amt/v_units) ||' '|| ROUND(v_col_2_amt/v_units) ||' ' || ROUND(v_col_3_amt/v_units) ||' '|| ROUND(v_col_4_amt/v_units)
||' '||ROUND(v_col_5_amt/v_units) ||' '|| ROUND(v_col_6_amt/v_units) ||' ' || ROUND(v_col_7_amt/v_units) ||' '|| ROUND(v_col_8_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 ,
col_5_amt ,
col_6_amt ,
col_7_amt ,
col_8_amt,
PERIOD_YEAR,
PERIOD_NUM,
REPORTY_TYPE,
LEDGER_ID
)
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),
DECODE(v_natural_balance_type, 'C', v_col_5_amt * -1, v_col_5_amt),
DECODE(v_natural_balance_type, 'C', v_col_6_amt * -1, v_col_6_amt),
DECODE(v_natural_balance_type, 'C', v_col_7_amt * -1, v_col_7_amt),
DECODE(v_natural_balance_type, 'C', v_col_8_amt * -1, v_col_8_amt),
v_period_fiscal_year,
v_period_num ,
v_report_type,
v_sob
);
fnd_file.log, 'TestMod'||'insert '|| v_col_1_amt ||' '|| v_col_2_amt ||' ' || v_col_3_amt||' '|| v_col_4_amt
|| v_col_5_amt ||' '|| v_col_6_amt ||' ' || v_col_7_amt||' '|| v_col_8_amt
)
;
UPDATE fv_cfs_rep_temp SET col_4_amt = NVL(col_1_amt,0) + NVL(col_2_amt,0) - NVL(col_3_amt,0),
col_8_amt = NVL(col_5_amt,0) + NVL(col_6_amt,0) - NVL(col_7_amt,0)
WHERE sequence_id = v_sequence_id AND line_id = v_line_id;
fnd_file.log, 'TestMod'||'UPDATE STATEMENT TO SET CONSOLIDATED TOTAL FOR SCNP '|| v_col_1_amt ||' '|| v_col_2_amt ||' ' || v_col_3_amt||' '|| v_col_4_amt
|| v_col_5_amt ||' '|| v_col_6_amt ||' ' || v_col_7_amt||' '|| v_col_8_amt
);
SELECT MIN(period_num)
INTO g_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
INTO parm_gl_period_num
FROM GL_PERIOD_STATUSES
WHERE LEDGER_ID = v_sob
AND PERIOD_YEAR = v_period_fiscal_year
AND APPLICATION_ID = '101'
AND CLOSING_STATUS IN ('O','C')
AND PERIOD_NAME = v_period_name;
/*INSERTING ALL LABELS TO Report table*/
INSERT
INTO fv_cfs_rep_temp
(
SEQUENCE_ID,
LINE_ID ,
LINE_LABEL,
PERIOD_YEAR ,
PERIOD_NUM ,
REPORTY_TYPE,
LEDGER_ID
)
SELECT DISTINCT line.sbr_line_number sbr_line_number,
line.sbr_line_id sbr_line_id ,
line.sbr_line_label sbr_line_label,
v_period_fiscal_year,
v_period_num ,
v_report_type,
v_sob
FROM fv_sbr_definitions_lines line
WHERE line.set_of_books_id = v_sob
AND line.sbr_line_type_code IN ('L','F')
ORDER BY line.sbr_line_number;
SELECT col_1_amt, col_2_amt
INTO v_col_3_amt,v_col_4_amt
FROM fv_cfs_rep_temp
WHERE line_id=v_line_id
AND ledger_id=v_sob
AND reporty_type='SBR'
AND period_num=v_period_num
AND period_year=v_period_fiscal_year-1;
SELECT COUNT(*)
INTO l_line_cnt
FROM fv_sbr_rep_line_calc
WHERE line_id = c_sbr_line_id;
SELECT start_date,
end_date
INTO beg_date,
close_date
FROM gl_period_statuses
WHERE period_year = p_fiscal_year
AND period_num = report_period_num
AND application_id = 101
AND set_of_books_id = v_sob;
SELECT upper(resource_type)
INTO c_resource_type
FROM fv_treasury_symbols
WHERE treasury_symbol_id = get_ts_id_rec.treasury_symbol_id
AND set_of_books_id = v_sob;
SELECT DECODE(time_frame,'X','X',NULL) ,
ffg.fund_type ,
expiration_Date
INTO availability_type_treas,
fund_type_treas ,
exp_date
FROM fv_treasury_symbols fts,
fv_fund_groups ffg
WHERE fts.treasury_symbol_id=get_ts_id_rec.treasury_symbol_id
AND fts.set_of_books_id = v_sob
AND fts.set_of_books_id = ffg.set_of_books_id
AND ffg.fund_group_code = fts.fund_group_code;
SELECT extract ( YEAR FROM expiration_date)
INTO expiring_year
FROM fv_treasury_symbols
WHERE treasury_symbol_id=get_ts_id_rec.treasury_symbol_id
AND set_of_books_id=v_sob;
SELECT fed.financing_account
INTO financing_account_treas
FROM FV_FACTS_FEDERAL_ACCOUNTS fed,
fv_treasury_symbols treas
WHERE fed.federal_acct_symbol_id = treas.federal_acct_symbol_id
AND treas.treasury_symbol_id =get_ts_id_rec.treasury_symbol_id
AND treas.set_of_books_id = v_sob;
query_fetch_bal_bud := 'select sum(nvl(amount,0)),acct.bud_col,acct.nbfa_col from fv_facts_temp fac, fv_sbr_definitions_accts acct
where (acct.sbr_treasury_symbol_id is null or acct.sbr_treasury_symbol_id = :cv_treasury_symbol_id)
AND acct.sbr_line_id = :cv_sbr_line_id
AND acct.sbr_line_acct_id = :cv_sbr_line_acct_id
AND acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
AND fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''
AND fac.treasury_symbol_id = (select treasury_symbol_id from fv_treasury_symbols
where set_of_books_id='||v_sob||' AND treasury_symbol_id='||get_ts_id_rec.treasury_symbol_id||')
AND begin_end = '''||c_sbr_balance_type||'''';
query_fetch_bal_bud := 'select sum(nvl(amount,0)*(-1)),acct.bud_col,acct.nbfa_col from fv_facts_temp fac, fv_sbr_definitions_accts acct
where (acct.sbr_treasury_symbol_id is null or acct.sbr_treasury_symbol_id = :cv_treasury_symbol_id)
AND acct.sbr_line_id = :cv_sbr_line_id
AND acct.sbr_line_acct_id = :cv_sbr_line_acct_id
and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''
AND fac.treasury_symbol_id = (select treasury_symbol_id from fv_treasury_symbols
where set_of_books_id='||v_sob||' AND treasury_symbol_id='||get_ts_id_rec.treasury_symbol_id||')
AND begin_end = '''||c_sbr_balance_type||'''';
query_fetch_bal_bud := ' select
SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) ),
acct.bud_col,acct.nbfa_col
from fv_facts_temp fac, fv_sbr_definitions_accts acct
where (acct.sbr_treasury_symbol_id is null or acct.sbr_treasury_symbol_id = :cv_treasury_symbol_id)
AND acct.sbr_line_id = :cv_sbr_line_id
AND acct.sbr_line_acct_id = :cv_sbr_line_acct_id
and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
AND fac.treasury_symbol_id = (select treasury_symbol_id from fv_treasury_symbols
where set_of_books_id='||v_sob||' AND treasury_symbol_id='||get_ts_id_rec.treasury_symbol_id||')
and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''';
query_fetch_bal_bud := ' select
SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) )*(-1),acct.bud_col,acct.nbfa_col
from fv_facts_temp fac, fv_sbr_definitions_accts acct
where (acct.sbr_treasury_symbol_id is null or acct.sbr_treasury_symbol_id = :cv_treasury_symbol_id)
AND acct.sbr_line_id = :cv_sbr_line_id
AND acct.sbr_line_acct_id = :cv_sbr_line_acct_id
and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
AND fac.treasury_symbol_id = (select treasury_symbol_id from fv_treasury_symbols
where set_of_books_id='||v_sob||' AND treasury_symbol_id='||get_ts_id_rec.treasury_symbol_id||')
and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''';
query_fetch_bal_bud := 'select sum(nvl(amount,0)),acct.bud_col,acct.nbfa_col
from fv_facts_temp fac, fv_sbr_definitions_accts acct
where (acct.sbr_treasury_symbol_id is null or acct.sbr_treasury_symbol_id = :cv_treasury_symbol_id)
AND acct.sbr_line_id = :cv_sbr_line_id
AND acct.sbr_line_acct_id = :cv_sbr_line_acct_id
and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
AND fac.treasury_symbol_id = (select treasury_symbol_id from fv_treasury_symbols
where set_of_books_id='||v_sob||' AND treasury_symbol_id='||get_ts_id_rec.treasury_symbol_id||')
and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''
AND begin_end = ''E''';
query_fetch_bal_bud := ' select
SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) ),acct.bud_col,acct.nbfa_col
from fv_facts_temp fac, fv_sbr_definitions_accts acct
where (acct.sbr_treasury_symbol_id is null or acct.sbr_treasury_symbol_id = :cv_treasury_symbol_id)
AND acct.sbr_line_id = :cv_sbr_line_id
AND acct.sbr_line_acct_id = :cv_sbr_line_acct_id
and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
AND fac.treasury_symbol_id = (select treasury_symbol_id from fv_treasury_symbols
where set_of_books_id='||v_sob||' AND treasury_symbol_id='||get_ts_id_rec.treasury_symbol_id||')
and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''';
SELECT col_1_amt, col_2_amt
INTO v_col_3_amt,v_col_4_amt
FROM fv_cfs_rep_temp
WHERE line_id=v_line_id
AND ledger_id=v_sob
AND reporty_type='SBR'
AND period_num=v_period_num
AND period_year=v_period_fiscal_year-1;
DELETE
FROM fv_cfs_rep_temp
WHERE (line_id) IN
(SELECT sbr_line_id
FROM fv_sbr_definitions_lines
WHERE set_of_books_id = v_sob
)
and upper(reporty_type) <> 'SBR';
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_sbr_definitions_accts dets,
fv_sbr_definitions_lines fsdl ,
fv_sbr_ccids_gt fvcc,
fv_facts1_period_attributes fctbal
WHERE dets.sbr_line_id = :cv_line_id
AND fsdl.sbr_line_id=dets.sbr_line_id
AND dets.sbr_line_acct_id = :cv_line_detail_id
AND dets.sbr_line_acct_id = fvcc.sbr_line_acct_id
AND fctbal.ccid = fvcc.ccid
AND fctbal.set_of_books_id = :b_sob
AND fctbal.period_year = :cv_period_fiscal_year
AND EXISTS
(SELECT 1
FROM fv_fund_parameters ffp
WHERE set_of_books_id = :b_sob
AND fund_category like nvl(fsdl.sbr_fund_category, ''%'')
AND ffp.fund_value = fctbal.fund_value )';
l_out := v_fct1_attr_select;
dbms_sql.parse(v_glbal_curid, v_fct1_attr_select, dbms_sql.v7);
v_fct1_sel := 'SELECT ROUND(SUM(NVL(ffrt.amount,0) ),2) ';
v_glbal_select :=
' SELECT
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_sbr_definitions_accts dets,
fv_sbr_definitions_lines fsdl ,
gl_code_combinations glc,
gl_code_combinations glc1,
gl_balances glbal
WHERE dets.sbr_line_id = :cv_line_id
AND dets.sbr_line_acct_id = :cv_line_detail_id
AND dets.sbr_line_id = fsdl.sbr_line_id
AND glc1.'||v_acc_seg_name|| ' like :cv_acc_num
AND glc.code_combination_id=glc1.code_combination_id
AND glc1.chart_of_accounts_id = :b_chart_of_accounts_id
AND glc.chart_of_accounts_id = :b_chart_of_accounts_id
AND glbal.code_combination_id = glc.code_combination_id
AND glbal.code_combination_id = glc1.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(fsdl.sbr_fund_category, ''%'')
AND ffp.fund_value = glc.'||v_bal_seg_name||')';
l_out := v_glbal_select;
dbms_sql.parse(v_sbr_curid, v_glbal_select, dbms_sql.v7);
SELECT
sbr_line_acct_id,
sbr_balance_type,
bud_col,
nbfa_col,
acct_number
FROM fv_sbr_definitions_accts
WHERE sbr_line_id = v_line_id
and set_of_books_id = v_sob;
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 SUBSTR(compiled_value_attributes, 5, 1)
INTO l_account_type
FROM fnd_flex_values
WHERE flex_value = detail_rec.acct_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.acct_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;
DELETE FROM fv_facts_temp
WHERE treasury_symbol_id = v_purge_ts_id;
DELETE FROM fv_facts_edit_check_status
WHERE treasury_symbol_id = v_purge_ts_id;
DELETE FROM fv_cfs_rep_temp
WHERE ledger_id=v_sob
AND reporty_type='SBR'
AND period_num=v_period_num
AND period_year=v_period_fiscal_year;