[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 ='SINGLE'
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', 'S', 'N')
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,
sf133_additional_info
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;
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 = 'SINGLE';
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 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 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_column_amount,
c_sf133_amount_not_shown,
c_sf133_column_amount2,
c_sf133_amt2_not_shown,
c_sf133_column_amount3,
c_sf133_amt3_not_shown,
c_sf133_column_amount4,
c_sf133_amt4_not_shown,
c_sf133_column_amount5,
c_sf133_amt5_not_shown,
c_sf133_column_amount6,
c_sf133_amt6_not_shown,
c_sf133_column_amount_total,
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)
VALUES(o_sf133_ts_value,
o_sf133_treasury_symbol_id, --Bug 1575992
o_sf133_line_id,
o_sf133_column_number,
o_sf133_column_amount,
o_sf133_amt_not_shown);
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;