[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO l_bc_count
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = vp_sob_id
AND period_year = vp_carryfor_fyr
AND NVL(track_bc_ytd_flag, 'N') = 'Y';
SELECT currency_code ,
CHART_OF_ACCOUNTS_ID,
BAL_SEG_VALUE_OPTION_CODE
INTO vg_currency ,
vg_coa_id ,
vg_bal_seg_val_opt_code
FROM gl_ledgers_public_v
WHERE ledger_id = vp_sob_id;
SELECT factsii_pub_law_code_attribute,
factsii_advance_type_attribute, factsii_tr_dept_id_attribute,
factsii_tr_main_acct_attribute
INTO vg_public_law_attribute,
vg_advance_type_attribute, vg_trf_dept_id_attribute,
vg_trf_main_acct_attribute
FROM Fv_System_Parameters;
SELECT period_name
INTO vg_closing_period
FROM Gl_Period_Statuses
WHERE ledger_id = vp_sob_id
AND application_id = 101
AND period_year = vg_closing_fyr
AND period_num = (SELECT MAX(period_num)
FROM gl_period_statuses
WHERE ledger_id = vp_sob_id
AND application_id = 101
AND period_year = vg_closing_fyr);
SELECT period_name, adjustment_period_flag,
closing_status,start_date
INTO vg_carryfor_period,vl_adj_flag,
vl_closing_status, vg_start_date
FROM Gl_Period_Statuses
WHERE ledger_id = vp_sob_id
AND application_id = 101
AND period_year = vp_carryfor_fyr
AND period_num = (SELECT MIN(period_num)
FROM gl_period_statuses
WHERE ledger_id = vp_sob_id
AND application_id = 101
AND period_year = vp_carryfor_fyr);
SELECT period_set_name
INTO vg_period_set_name
FROM Gl_Sets_Of_Books
WHERE set_of_books_id = vp_sob_id;
SELECT COUNT(*)
FROM Gl_Je_Headers
WHERE ledger_id = vp_sob_id
AND je_source = 'Year End Close'
AND je_category = 'Federal Carry Forward'
AND period_name = vg_carryfor_period;
SELECT je_header_id,accrual_rev_status,
accrual_rev_je_header_id,status
FROM Gl_Je_Headers
WHERE ledger_id = vp_sob_id
AND je_source = 'Year End Close'
AND je_category = 'Federal Carry Forward'
AND period_name = vg_carryfor_period;
SELECT status
FROM Gl_Je_Headers
WHERE ledger_id = vp_sob_id
AND je_header_id = vl_rev_header_id;
'Please delete the unposted journal entries and '||
'then re-run the Carry Forward process.';
l_insert_stmt VARCHAR2(2000);
l_select_stmt VARCHAR2(6000);
l_insert_stmt := ' INSERT INTO Gl_Interface(status,
ledger_id ,
accounting_date ,
currency_code ,
date_created ,
created_by ,
actual_flag ,
user_je_category_name,
user_je_source_name ,
entered_dr ,
entered_cr ,
group_id ,
period_name ,
chart_of_accounts_id,
code_combination_id ,
reference21 ,
attribute' || NVL(SUBSTR(vg_public_law_attribute , 10),17) || ' ,
attribute' || NVL(SUBSTR(vg_advance_type_attribute , 10),18) || ' ,
attribute' || NVL(SUBSTR(vg_trf_dept_id_attribute , 10),19) || ' ,
attribute' || NVL(SUBSTR(vg_trf_main_acct_attribute, 10),20) || ' ) ';
l_select_stmt :=
' SELECT
''NEW'' , '
|| vp_sob_id || ' , '''
|| vg_start_date || ''' , '''
|| vg_currency
|| ''' , SYSDATE , ' ||
l_user_id || ' ,
''A'' ,
''Federal Carry Forward'' ,
''Year End Close'' , '
|| ' SUM(ROUND(NVL(xdl.unrounded_accounted_dr,0),2)) debit,
SUM(ROUND(NVL(xdl.unrounded_accounted_cr,0),2)) credit , '
|| vg_jrnl_group_id || ' , '''
|| vg_carryfor_period || ''' , '
|| vg_coa_id
|| ' , gcc.code_combination_id , '
|| ' MAX(xdl.SOURCE_DISTRIBUTION_ID_NUM_1) reference_1 '
|| vl_attribute_cols || '
FROM fv_be_trx_dtls B, xla_ae_lines xl ,
xla_distribution_links xdl, gl_je_lines l,
gl_je_headers h , gl_code_combinations gcc,
gl_import_references gir
WHERE l.code_combination_id = gcc.code_combination_id
AND l.je_header_id = h.je_header_id
AND FV_YE_CARRYFORWARD.Check_bal_seg_value(GCC.'||VG_BAL_SEG_VALUE||',
:sob_id,
:bal_seg_val_opt_code) = '||''''||'Y'||''''||
' AND xl.code_combination_id = l.code_combination_id
AND gir.je_header_id = h.je_header_id
AND gir.je_batch_id = h.je_batch_id
AND gir.je_line_num = l.je_line_num
AND xl.ae_header_id = xdl.ae_header_id
AND xl.ae_line_num = xdl.ae_line_num
AND xl.gl_sl_link_id = gir.gl_sl_link_id
AND xl.currency_code = h.currency_code
AND NVL(h.je_from_sla_flag, ''N'') = ''Y''
AND EXISTS (SELECT 1
FROM gl_periods
WHERE period_year = :closing_fyr
AND period_set_name = :period_set_name
AND period_name = l.period_name)
AND nvl(xdl.SOURCE_DISTRIBUTION_ID_NUM_1,-99)=b.transaction_id(+)
AND b.set_of_books_id (+) = :sob_id
AND l.status = ''P''
AND h.actual_flag = ''A''
AND h.ledger_id = :sob_id
--AND h.currency_code = :vg_currency --bug 5570564
AND h.currency_code <> ''STAT''
AND gcc.summary_flag = '||''''||'N'||''''||
' AND gcc.template_id IS NULL
AND gcc.chart_of_accounts_id = :c_coa
AND gcc.account_type IN ('||''''||'C'||''''||','||
''''||'D'||''''||')'|| vl_group_by_clause ||
' UNION
SELECT
''NEW'' , '
|| vp_sob_id || ' , '''
|| vg_start_date || ''' , '''
|| vg_currency
|| ''' , SYSDATE , ' ||
l_user_id || ' ,
''A'' ,
''Federal Carry Forward'' ,
''Year End Close'' , '
|| ' SUM(NVL(l.accounted_dr,0)) debit,
SUM(NVL(l.accounted_cr,0)) credit , '
|| vg_jrnl_group_id || ' , '''
|| vg_carryfor_period || ''' , '
|| vg_coa_id
|| ' , gcc.code_combination_id , '
|| ' MAX(fv_ye_carryforward.convert_to_num(l.reference_1)) '
|| vl_attribute_cols || '
FROM gl_je_lines l , fv_be_trx_dtls b, gl_je_headers h , gl_code_combinations gcc
WHERE l.code_combination_id = gcc.code_combination_id
AND FV_YE_CARRYFORWARD.Check_bal_seg_value(GCC.'|| VG_BAL_SEG_VALUE||', :sob_id,:bal_seg_val_opt_code ) = '||''''||'Y'||''''||
' AND l.je_header_id = h.je_header_id
AND NVL(h.je_from_sla_flag, ''N'') IN (''N'',''U'')
AND EXISTS (SELECT 1
FROM gl_periods
WHERE period_year = :closing_fyr
AND period_set_name = :period_set_name
AND period_name = l.period_name)
AND nvl(Fv_Ye_Carryforward.Convert_To_Num(l.reference_1),-99)=b.transaction_id(+)
AND l.ledger_id = b.set_of_books_id(+)
AND l.status = ''P''
AND h.actual_flag = ''A''
AND h.ledger_id = :sob_id
-- AND h.currency_code = :vg_currency --bug 5570564
AND h.currency_code <> ''STAT''
AND gcc.summary_flag = '||''''||'N'||''''||
' AND gcc.template_id IS NULL
AND gcc.chart_of_accounts_id = :c_coa
AND gcc.account_type IN ('||''''||'C'||''''||','||
''''||'D'||''''||')'|| vl_group_by_clause ;
l_select_stmt := l_insert_stmt || '( ' || l_select_stmt || ')';
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_select_stmt);
dbms_sql.parse(c_gl_line_cur, l_select_stmt, DBMS_SQL.V7) ;
fnd_file.put_line(FND_FILE.LOG,'No of Records inserted into GL_interface :' || to_char(vl_exec_cur));
vp_errbuf := l_select_stmt || SQLERRM||' -- Error in Get_Balances procedure.';
SELECT Gl_Interface_Control_S.NEXTVAL
INTO vg_jrnl_group_id
FROM DUAL;
INSERT INTO Gl_Interface_Control
(je_source_name,
status,
interface_run_id,
group_id,
set_of_books_id)
VALUES ('Year End Close',
'S',
vg_interface_run_id,
vg_jrnl_group_id,
vp_sob_id);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' AFTER INSERTING INTO GL_INTERFACE....');
DELETE FROM Gl_Interface
WHERE user_je_source_name = 'Year End Close'
AND user_je_category_name = 'Federal Carry Forward'
AND ledger_id = vp_sob_id;
SELECT 'Y'
INTO vl_valid_fund
FROM gl_ledger_segment_values glsv
WHERE glsv.ledger_id = vp_sob_id
AND glsv.segment_type_code (+) = 'B'
AND NVL(glsv.status_code (+), 'X') <> 'I'
AND NVL(glsv.start_date (+),TO_DATE('1950/01/01','YYYY/MM/DD'))
<= NVL(sysdate,TO_DATE('9999/12/31','YYYY/MM/DD'))
AND NVL(glsv.end_date (+),TO_DATE('9999/12/31','YYYY/MM/DD'))
>= NVL(sysdate, TO_DATE('1950/01/01','YYYY/MM/DD'))
AND glsv.segment_value = Vp_fund_value;