[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_insert_count NUMBER;
c_begin_select VARCHAR2(200);
c_end_select VARCHAR2(200);
v_select VARCHAR2(8600);
SELECT UPPER(glflex.segment_name) segment_name,
UPPER(glflex.application_column_name) flex_column_name
FROM fnd_id_flex_segments glflex
WHERE glflex.application_id = 101
AND glflex.id_flex_num = g_chart_of_accounts_id
AND glflex.id_flex_code = 'GL#'
ORDER BY glflex.application_column_name;
SELECT DISTINCT
FTS.treasury_symbol sf133_ts_value,
FTS.treasury_symbol_id sf133_treasury_symbol_id, --Added for Bug 1575992
line.sf133_line_id sf133_line_id,
line.sf133_line_number sf133_line_number,
line.sf133_line_type_code sf133_line_type_code,
line.sf133_natural_balance_type sf133_natural_balance_type,
line.sf133_fund_category sf133_line_category,
line.sf133_report_line_number sf133_report_line_number
FROM fv_sf133_definitions_lines line,
FV_TREASURY_SYMBOLS FTS
WHERE FTS.SET_OF_BOOKS_ID = LINE.SET_OF_BOOKS_ID
AND FTS.Treasury_symbol = parm_treasury_value_r1
AND FTS.set_of_books_id = parm_set_of_books_id
AND (line.sf133_line_type_code) IN ('T', 'D', 'D2')
ORDER BY line.sf133_line_number;
SELECT sf133_line_acct_id,
sf133_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
FROM fv_sf133_definitions_accts
WHERE sf133_line_id = c_sf133_line_id ;
SELECT treasury_symbol, treasury_symbol_id
FROM fv_treasury_symbols
WHERE TIME_FRAME IN ('X','M')--CGAC
AND (FUND_GROUP_CODE NOT BETWEEN '3800' AND '3899')
AND (FUND_GROUP_CODE NOT BETWEEN '6001' AND '6999')
AND treasury_symbol BETWEEN tsymbol_r1 AND tsymbol_r2
AND set_of_books_id = parm_set_of_books_id
ORDER BY treasury_symbol;
select currency_code,
chart_of_accounts_id
into g_currency_code,
g_chart_of_accounts_id
from gl_ledgers
where ledger_id = parm_set_of_books_id;
g_insert_count := 0;
select period_num
into report_period_num
from gl_period_statuses
where application_id = parm_application_id
and set_of_books_id = parm_set_of_books_id
and period_name = parm_gl_period_name
and period_year = parm_gl_period_year;
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- INSERT COUNT('||G_INSERT_COUNT||')');
SELECT chart_of_accounts_id
INTO g_chart_of_accounts_id
FROM gl_ledgers
WHERE ledger_id = parm_set_of_books_id;
SELECT application_column_name
INTO v_balance_column_name
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = g_chart_of_accounts_id
AND segment_attribute_type = 'GL_BALANCING'
AND attribute_value = 'Y';
/* v_select := 'SELECT 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),'||
''''|| 'P' || '''' || ',' || '
DECODE(SIGN(ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
+ (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),-1,0,
ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
+ (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),'||
''''|| 'N' || '''' || ',' || '
DECODE(SIGN(ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
+ (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),1,0,
ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
+ (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2))) '|| '
FROM gl_balances glbal,
gl_code_combinations glcc,
fv_sf133_definitions_accts acct,
fv_sf133_ccids_gt fscg,
fv_fund_parameters FFP
WHERE glbal.ledger_id = :cv_sob_id
AND glbal.period_year = :cv_period_year
AND glbal.period_num = :cv_period
AND glbal.currency_code = :cv_currency_code
AND glbal.actual_flag = '||''''||'A'||''''||'
AND glcc.chart_of_accounts_id = :cv_coa_id
AND glbal.code_combination_id = glcc.code_combination_id
AND acct.sf133_line_id = :cv_sf133_line_id
AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
AND glcc.template_id is null
AND fscg.ccid = glcc.code_combination_id
AND fscg.sf133_line_acct_id = acct.sf133_line_acct_id
AND glcc.' || v_balance_column_name ||' = FFP.fund_value
AND FFP.treasury_symbol_id = :cv_treasury_symbol_id
AND FFP.set_of_books_id = :cv_sob_id
AND fund_category like nvl(:cv_sf133_line_category, ' || '''' ||'%' || ''''||')';*/
v_select:= 'select sum(nvl(amount,0)) from fv_facts_temp fac, fv_sf133_definitions_accts acct
where fac.treasury_symbol_id = :cv_treasury_symbol_id
AND acct.sf133_line_id = :cv_sf133_line_id
AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
AND begin_end = :cv_balance_type and debit_credit is null';
v_select := v_select || '
AND ' || das_where;
SELECT flex_value_set_id
INTO g_seg_value_set_id
FROM fnd_id_flex_segments
WHERE application_column_name = c_flex_column_name
AND application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = g_chart_of_accounts_id;
v_select := v_select || '
AND ( NVL(glcc.'|| c_flex_column_name ||
',' || '''' || '-1' || '''' || ') = ' || 'NVL(acct.' || c_flex_column_name
||',NVL(glcc.'||c_flex_column_name ||
','||''''||'-1'||''''||')) ' || '
OR glcc.'||c_flex_column_name ||' IN (SELECT flex_value '||
'FROM fnd_flex_values ffv, fnd_flex_value_hierarchies ffvh '||
'WHERE ffv.flex_value BETWEEN ffvh.child_flex_value_low
AND ffvh.child_flex_value_high
AND ffv.flex_value_set_id = ' || g_seg_value_set_id ||
' AND ffv.flex_value_set_id = ffvh.flex_value_set_id'||
' AND parent_flex_value = acct.' || c_flex_column_name || '))';
v_select := v_select || '
AND NVL(glcc.'|| c_flex_column_name ||
',' || '''' || '-1' || '''' || ') = ' || 'NVL(acct.' || c_flex_column_name
||',NVL(glcc.'||c_flex_column_name ||
','||''''||'-1'||''''||'))';
dbms_sql.parse(v_cursor_id, v_select, dbms_sql.v7);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,V_SELECT);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, 'testsf133',V_SELECT);
DELETE
FROM fv_sf133_definitions_cols_temp
WHERE (sf133_line_id)
IN
(SELECT sf133_line_id
FROM fv_sf133_definitions_lines
WHERE set_of_books_id = parm_set_of_books_id);
SELECT min(period_num)
INTO g_period_num
FROM gl_period_statuses
WHERE ledger_id = parm_set_of_books_id
AND period_year = parm_gl_period_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 = parm_set_of_books_id AND
PERIOD_YEAR = parm_gl_period_year AND
APPLICATION_ID = '101' AND
CLOSING_STATUS in ('O','C') AND
PERIOD_NAME = parm_gl_period_name;
SELECT count(*)
INTO l_line_cnt
FROM fv_sf133_rep_line_calc
WHERE line_id = c_sf133_line_id;
select upper(resource_type) into c_resource_type
from fv_treasury_symbols
where treasury_symbol = parm_treasury_value_r1
and set_of_books_id = parm_set_of_books_id;
SELECT start_date,
end_date
INTO beg_date,
close_date
FROM gl_period_statuses
WHERE period_year = parm_gl_period_year
AND period_num = report_period_num
AND application_id = 101
AND set_of_books_id = parm_set_of_books_id;
SELECT availability_type_code, fund_type, expiration_Date
INTO availability_type_treas, fund_type_treas, exp_date
FROM fv_treasury_symbols
WHERE treasury_symbol_id=parm_tsymbol_id
AND set_of_books_id = parm_set_of_books_id;
select extract ( year from expiration_date)into expiring_year from fv_treasury_symbols where treasury_symbol_id=parm_tsymbol_id;
SELECT financing_account
INTO financing_account_treas
FROM fv_treasury_symbols
WHERE treasury_symbol_id = parm_tsymbol_id
AND set_of_books_id = parm_set_of_books_id;
query_fetch_bal := 'select sum(nvl(amount,0)) from fv_facts_temp fac, fv_sf133_definitions_accts acct
where fac.treasury_symbol_id = :cv_treasury_symbol_id
AND acct.sf133_line_id = :cv_sf133_line_id
AND acct.sf133_line_acct_id = :cv_sf133_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 begin_end = '''||c_sf133_balance_type||'''';
query_fetch_bal := 'select sum(nvl(amount,0)*(-1)) from fv_facts_temp fac, fv_sf133_definitions_accts acct
where fac.treasury_symbol_id = :cv_treasury_symbol_id
AND acct.sf133_line_id = :cv_sf133_line_id
AND acct.sf133_line_acct_id = :cv_sf133_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 begin_end = '''||c_sf133_balance_type||'''';
query_fetch_bal := ' select
SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) )
from fv_facts_temp fac, fv_sf133_definitions_accts acct
where fac.treasury_symbol_id = :cv_treasury_symbol_id
AND acct.sf133_line_id = :cv_sf133_line_id
AND acct.sf133_line_acct_id = :cv_sf133_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'' ';
query_fetch_bal := ' select
SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) )*(-1)
from fv_facts_temp fac, fv_sf133_definitions_accts acct
where fac.treasury_symbol_id = :cv_treasury_symbol_id
AND acct.sf133_line_id = :cv_sf133_line_id
AND acct.sf133_line_acct_id = :cv_sf133_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'' ';
query_fetch_bal := 'select sum(nvl(amount,0)) from fv_facts_temp fac, fv_sf133_definitions_accts acct
where fac.treasury_symbol_id = :cv_treasury_symbol_id
AND acct.sf133_line_id = :cv_sf133_line_id
AND acct.sf133_line_acct_id = :cv_sf133_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 begin_end = ''E''';
query_fetch_bal := ' select
SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) )
from fv_facts_temp fac, fv_sf133_definitions_accts acct
where fac.treasury_symbol_id = :cv_treasury_symbol_id
AND acct.sf133_line_id = :cv_sf133_line_id
AND acct.sf133_line_acct_id = :cv_sf133_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'' ';
SELECT NVL(SUM(NVL(sf133_amount_not_shown,0)),0)
INTO c_sf133_amount_not_shown
FROM fv_sf133_definitions_cols_temp
WHERE sf133_column_number = g_column_number
AND sf133_fund_value = c_sf133_ts_value
AND (sf133_line_id)
IN
(SELECT sf133_line_id
FROM fv_sf133_definitions_lines
WHERE set_of_books_id = parm_set_of_books_id
AND sf133_line_number >
DECODE(c_sf133_line_type_code, 'T', g_total_start_line_number, g_subtotal_start_line_number)
AND sf133_line_number < c_sf133_line_number);
INSERT
INTO fv_sf133_definitions_cols_temp
(sf133_fund_value,
treasury_symbol_id,
sf133_line_id,
sf133_column_number,
sf133_column_amount,
sf133_amount_not_shown)
VALUES(o_sf133_ts_value,
o_sf133_treasury_symbol_id,
o_sf133_line_id,
o_sf133_column_number,
o_sf133_column_amount,
o_sf133_amt_not_shown);
g_insert_count := g_insert_count + 1;
v_insert_statement VARCHAR2(32000);
SELECT fsda.sf133_line_acct_id,
fsda.sf133_line_id,
fsdl.sf133_fund_category,
fsda.segment1,
fsda.segment2,
fsda.segment3,
fsda.segment4,
fsda.segment5,
fsda.segment6,
fsda.segment7,
fsda.segment8,
fsda.segment9,
fsda.segment10,
fsda.segment11,
fsda.segment12,
fsda.segment13,
fsda.segment14,
fsda.segment15,
fsda.segment16,
fsda.segment17,
fsda.segment18,
fsda.segment19,
fsda.segment20,
fsda.segment21,
fsda.segment22,
fsda.segment23,
fsda.segment24,
fsda.segment25,
fsda.segment26,
fsda.segment27,
fsda.segment28,
fsda.segment29,
fsda.segment30
FROM fv_sf133_definitions_accts fsda,
fv_sf133_definitions_lines fsdl
WHERE fsdl.sf133_line_id = fsda.sf133_line_id
AND fsdl.set_of_books_id=p_sobid
ORDER BY 2,1;
SELECT application_column_name ,
flex_value_set_id
FROM fnd_id_flex_segments
WHERE id_flex_code = 'GL#'
AND id_flex_num = p_chart_of_accounts_id;
SELECT child_flex_value_low,
child_flex_value_high
FROM fnd_flex_value_hierarchies
WHERE parent_FLEX_value = p_seg
AND flex_value_set_id = p_sid;
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_sf133_ccids_gt
(
sf133_line_acct_id,
ccid
)
SELECT :b_sf133_line_acct_id,
gcc.code_combination_id
FROM gl_code_combinations gcc,
fv_fund_parameters FFP
WHERE gcc.' || v_balance_column_name ||' = ffp.fund_value
AND ffp.treasury_symbol_id = :b_treasury_symbol_id
AND ffp.set_of_books_id = :b_set_of_books_id
AND fund_category like nvl(:b_sf133_line_category, ' || '''' ||'%' || ''''||')
AND '|| v_statement || '
AND gcc.template_id is null
AND gcc.chart_of_accounts_id = :b_chart_of_accounts_id
AND NOT EXISTS (SELECT 1
FROM fv_sf133_ccids_gt fct
WHERE fct.sf133_line_acct_id =:b_sf133_line_acct_id
AND fct.ccid = gcc.code_combination_id)';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'v_insert_statement = '||v_insert_statement);
EXECUTE IMMEDIATE v_insert_statement
USING crec_rec.sf133_line_acct_id,
p_treasury_symbol_id,
parm_set_of_books_id,
crec_rec.sf133_fund_category,
g_chart_of_accounts_id,
crec_rec.sf133_line_acct_id;
query_Ending_Indicator := 'select sum(decode(facE.debit_credit,''D'',amount)),
sum(decode(facE.debit_credit,''C'',amount)) from
fv_facts_temp facE, fv_sf133_definitions_accts acct
where facE.treasury_symbol_id = '||parm_tsymbol_id||
'AND acct.sf133_line_id = '||c_sf133_line_id||
'AND acct.sf133_line_acct_id = '||c_sf133_line_acct_id||
'and acct_number like facE.sgl_acct_number||''%'' and facE.sgl_acct_number is not null
and facE.begin_end=''E''' ;
query_Beg_Indicator:= 'select sum(decode(facB.debit_credit,''D'',amount)) ,
sum(decode(facB.debit_credit,''C'',amount)) from
fv_facts_temp facB, fv_sf133_definitions_accts acct
where facB.treasury_symbol_id = '||parm_tsymbol_id||
'AND acct.sf133_line_id = '||c_sf133_line_id||
'AND acct.sf133_line_acct_id = '||c_sf133_line_acct_id||
'and acct_number like facB.sgl_acct_number||''%'' and facB.sgl_acct_number is not null
and facB.begin_end=''B''' ;
SELECT calc_sequence_number, line_low, line_high, line_low_type, line_high_type,
operator
FROM fv_sf133_rep_line_calc
WHERE line_id = c_sf133_line_id
ORDER BY calc_sequence_number;
SELECT sf133_column_amount
FROM fv_sf133_definitions_cols_temp
WHERE sf133_line_id = p_line_id and
treasury_symbol_id = c_sf133_treasury_symbol_id;
SELECT sf133_line_id
FROM fv_sf133_definitions_lines
WHERE sf133_line_number >=
(SELECT sf133_line_number FROM fv_sf133_definitions_lines
WHERE sf133_line_id = p_lineid_1 )
AND sf133_line_number <=
(SELECT sf133_line_number FROM fv_sf133_definitions_lines
WHERE sf133_line_id = p_lineid_2 );