[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_insert_count NUMBER;
v_select VARCHAR2(30000);
/* Cursor to select treasury symbols which fall in specified range */
CURSOR ts_range_cursor(tsymbol_r1 VARCHAR2,tsymbol_r2 VARCHAR2) IS
SELECT treasury_symbol,treasury_symbol_id
FROM fv_treasury_symbols
WHERE treasury_symbol BETWEEN tsymbol_r1 AND tsymbol_r2
AND time_frame ='A' --CGAC
AND (fund_group_code NOT BETWEEN '3800' AND '3899')
AND (fund_group_code NOT BETWEEN '6001' AND '6999')
AND set_of_books_id = parm_set_of_books_id
ORDER BY treasury_symbol;
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
FTS.treasury_symbol sf133_ts_value,
FTS.treasury_symbol_id sf133_treasury_symbol_id,
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.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')
AND line.set_of_books_id = FTS.set_of_books_id
ORDER BY FTS.treasury_symbol,
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 currency_code,
chart_of_accounts_id
into g_currency_code,
g_chart_of_accounts_id
from gl_ledgers_public_v
where ledger_id = parm_set_of_books_id;
g_insert_count := 0;
SELECT established_fiscal_yr, substr(cancellation_date,8,4)
INTO g_established_year, g_cancellation_year
FROM fv_treasury_symbols
WHERE treasury_symbol = parm_treasury_value_r1
AND set_of_books_id = parm_set_of_books_id ;
SELECT federal_acct_symbol_id
INTO g_federal_acct_symbol_id
FROM fv_treasury_symbols
WHERE set_of_books_id = parm_set_of_books_id
AND treasury_symbol_id = parm_treasury_symbol_id;
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 treasury_symbol,treasury_symbol_id
into c_sf133_ts_value,g_treasury_symbol_id
from fv_treasury_symbols
WHERE set_of_books_id = parm_set_of_books_id
and federal_acct_symbol_id = g_federal_acct_symbol_id
and established_fiscal_yr = l_loop_year
and time_frame = 'A'; --CGAC
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_public_v
WHERE ledger_id = parm_set_of_books_id;
/* SELECT statement brought OUT NOCOPY of the LOOP as it does nto use any of the loop variables */
-- find the balance segment (fund) application_column_name
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,
fv_sf133_definitions_accts acct,
fv_sf133_ccids_gt fscg
WHERE glbal.ledger_id = :cv_set_of_books_id
AND glbal.period_year = :cv_fiscal_year
AND glbal.period_num = :cv_period
AND glbal.currency_code = :cv_currency_code
AND glbal.actual_flag = '||''''||'A'||''''||'
AND glbal.code_combination_id = fscg.ccid
AND acct.sf133_line_id = :cv_sf133_line_id
AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
AND fscg.sf133_line_acct_id = acct.sf133_line_acct_id';
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'||''''||'))';
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,V_SELECT);
fnd_file.put_line (fnd_file.log, v_select);
dbms_sql.parse(v_cursor_id,v_select,dbms_sql.v7);
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 adjustment_period_flag = 'N'
AND period_year = parm_gl_period_year
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 treasury_symbol,treasury_symbol_id
into c_sf133_ts_value,g_treasury_symbol_id
from fv_treasury_symbols
WHERE set_of_books_id = parm_set_of_books_id
and federal_acct_symbol_id = g_federal_acct_symbol_id
and established_fiscal_yr = l_loop_year
and time_frame = 'A';--CGAC
UPDATE FV_SF133_DEFINITIONS_COLS_TEMP
SET
SF133_COLUMN_2_AMOUNT = DECODE(L_YEAR_COUNTER, 1,
O_SF133_COLUMN_AMOUNT, SF133_COLUMN_2_AMOUNT),
SF133_COLUMN_3_AMOUNT = DECODE(L_YEAR_COUNTER, 2,
O_SF133_COLUMN_AMOUNT, SF133_COLUMN_3_AMOUNT),
SF133_COLUMN_4_AMOUNT = DECODE(L_YEAR_COUNTER, 3,
O_SF133_COLUMN_AMOUNT, SF133_COLUMN_4_AMOUNT),
SF133_COLUMN_5_AMOUNT = DECODE(L_YEAR_COUNTER, 4,
O_SF133_COLUMN_AMOUNT, SF133_COLUMN_5_AMOUNT),
SF133_COLUMN_6_AMOUNT = DECODE(L_YEAR_COUNTER, 5,
O_SF133_COLUMN_AMOUNT, SF133_COLUMN_6_AMOUNT),
SF133_AMT_2_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 1,
O_SF133_AMT_NOT_SHOWN, SF133_AMT_2_NOT_SHOWN),
SF133_AMT_3_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 2,
O_SF133_AMT_NOT_SHOWN, SF133_AMT_3_NOT_SHOWN),
SF133_AMT_4_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 3,
O_SF133_AMT_NOT_SHOWN, SF133_AMT_4_NOT_SHOWN),
SF133_AMT_5_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 4,
O_SF133_AMT_NOT_SHOWN, SF133_AMT_5_NOT_SHOWN),
SF133_AMT_6_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 5,
O_SF133_AMT_NOT_SHOWN, SF133_AMT_6_NOT_SHOWN)
WHERE
SF133_FUND_VALUE = L_SF133_TS_VALUE AND
SF133_LINE_ID = O_SF133_LINE_ID ;
UPDATE FV_SF133_DEFINITIONS_COLS_TEMP
SET
SF133_AMT_TOTAL_NOT_SHOWN =
SF133_AMOUNT_NOT_SHOWN + SF133_AMT_2_NOT_SHOWN +
SF133_AMT_3_NOT_SHOWN + SF133_AMT_4_NOT_SHOWN +
SF133_AMT_5_NOT_SHOWN + SF133_AMT_6_NOT_SHOWN ,
SF133_COLUMN_TOTAL_AMT =
SF133_COLUMN_AMOUNT + SF133_COLUMN_2_AMOUNT +
SF133_COLUMN_3_AMOUNT + SF133_COLUMN_4_AMOUNT +
SF133_COLUMN_5_AMOUNT + SF133_COLUMN_6_AMOUNT
WHERE
SF133_FUND_VALUE = L_SF133_TS_VALUE AND
SF133_LINE_ID = O_SF133_LINE_ID ;
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 = parm_gl_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 = c_sf133_treasury_symbol_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=c_sf133_treasury_symbol_id;--g_treasury_symbol_id;
SELECT financing_account
INTO financing_account_treas
FROM fv_treasury_symbols
WHERE treasury_symbol_id = c_sf133_treasury_symbol_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),
NVL(SUM(NVL(sf133_amt_2_not_shown,0)),0),
NVL(SUM(NVL(sf133_amt_3_not_shown,0)),0),
NVL(SUM(NVL(sf133_amt_4_not_shown,0)),0),
NVL(SUM(NVL(sf133_amt_5_not_shown,0)),0),
NVL(SUM(NVL(sf133_amt_6_not_shown,0)),0),
NVL(SUM(NVL(sf133_amt_total_not_shown,0)),0)
INTO c_sf133_amount_not_shown,
c_sf133_amt2_not_shown ,
c_sf133_amt3_not_shown ,
c_sf133_amt4_not_shown ,
c_sf133_amt5_not_shown ,
c_sf133_amt6_not_shown ,
c_sf133_amt_total_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,--Bug 1575992
sf133_line_id,
sf133_column_number,
sf133_column_amount,
sf133_amount_not_shown,
SF133_COLUMN_2_AMOUNT ,
SF133_AMT_2_NOT_SHOWN ,
SF133_COLUMN_3_AMOUNT ,
SF133_AMT_3_NOT_SHOWN ,
SF133_COLUMN_4_AMOUNT ,
SF133_AMT_4_NOT_SHOWN ,
SF133_COLUMN_5_AMOUNT ,
SF133_AMT_5_NOT_SHOWN ,
SF133_COLUMN_6_AMOUNT ,
SF133_AMT_6_NOT_SHOWN ,
SF133_COLUMN_TOTAL_AMT,
SF133_AMT_TOTAL_NOT_SHOWN )
VALUES(
c_sf133_ts_value,
c_sf133_treasury_symbol_id, --Bug 1575992
c_sf133_line_id,
g_column_number,
c_sf133_amount_not_shown,
c_sf133_amount_not_shown,
c_sf133_amt2_not_shown,
c_sf133_amt2_not_shown,
c_sf133_amt3_not_shown,
c_sf133_amt3_not_shown,
c_sf133_amt4_not_shown,
c_sf133_amt4_not_shown,
c_sf133_amt5_not_shown,
c_sf133_amt5_not_shown,
c_sf133_amt6_not_shown,
c_sf133_amt6_not_shown,
c_sf133_amt_total_not_shown,
c_sf133_amt_total_not_shown);
g_insert_count := g_insert_count + 1;
INSERT
INTO fv_sf133_definitions_cols_temp
(sf133_fund_value,
treasury_symbol_id, --Bug 1575992
sf133_line_id,
sf133_column_number,
sf133_column_amount,
sf133_amount_not_shown,
sf133_column_2_amount,
sf133_column_3_amount,
sf133_column_4_amount,
sf133_column_5_amount,
sf133_column_6_amount
)
VALUES(o_sf133_ts_value,
o_sf133_treasury_symbol_id, --Bug 1575992
o_sf133_line_id,
o_sf133_column_number,
o_sf133_amt_not_shown,
o_sf133_amt_not_shown,
c_sf133_column_amount2,
c_sf133_column_amount3,
c_sf133_column_amount4,
c_sf133_column_amount5,
c_sf133_column_amount6
);
g_insert_count := g_insert_count + 1;
SELECT MIN(PERIOD_NUM)
INTO V_BEGIN_PERIOD
FROM gl_period_statuses
WHERE set_of_books_id = parm_set_of_books_id
AND period_year = V_PROCESS_YEAR
AND adjustment_period_flag = 'N'
AND application_id = '101' ;
SELECT MAX(PERIOD_NUM)
INTO V_END_PERIOD
FROM gl_period_statuses
WHERE set_of_books_id = parm_set_of_books_id
AND period_year = V_PROCESS_YEAR
AND closing_status in ('C','O')
AND application_id = '101' ;
v_insert_statement VARCHAR2(30000);
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)';
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 = '||g_treasury_symbol_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 = '||g_treasury_symbol_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, sf133_column_2_amount, sf133_column_3_amount, sf133_column_4_amount, sf133_column_5_amount,
sf133_column_6_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 );
UPDATE FV_SF133_DEFINITIONS_COLS_TEMP
SET
SF133_AMT_TOTAL_NOT_SHOWN =
o_sf133_amt_not_shown + c_sf133_amt2_not_shown +
c_sf133_amt3_not_shown + c_sf133_amt4_not_shown +
c_sf133_amt5_not_shown + c_sf133_amt6_not_shown ,
SF133_COLUMN_TOTAL_AMT =
o_sf133_column_amount + c_sf133_column_amount2 +
c_sf133_column_amount3 + c_sf133_column_amount4 +
c_sf133_column_amount5 + c_sf133_column_amount6
WHERE
SF133_LINE_ID = c_sf133_line_id and
SF133_FUND_VALUE = o_sf133_ts_value ;