[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_gl_interface_record(l_amount_dr IN NUMBER,
l_amount_cr IN NUMBER,
l_reference_1 IN VARCHAR2,
l_period_name IN VARCHAR2,
l_trading_partner IN VARCHAR2,
l_public_law_code IN VARCHAR2 DEFAULT NULL,
l_advance_type IN VARCHAR2 DEFAULT NULL,
l_trf_dept_id IN VARCHAR2 DEFAULT NULL,
l_trf_main_acct IN VARCHAR2 DEFAULT NULL);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CHECKING IF DYNAMIC INSERTION IS ON.');
Chk_Dynamic_Insertion;
SELECT currency_code
INTO vg_currency
FROM gl_sets_of_books
WHERE set_of_books_id = vg_sob_id;
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 = vg_sob_id;
SELECT factsi_journal_attribute,
factsii_pub_law_code_attribute,
factsii_advance_type_attribute,
factsii_tr_dept_id_attribute,
factsii_tr_main_acct_attribute
INTO vg_factsi_attribute,
vg_public_law_attribute,
vg_advance_type_attribute,
vg_trf_dept_id_attribute,
vg_trf_main_acct_attribute
FROM Fv_System_Parameters;
'trading partner attributes, delete the journal entries '||
'created by this process, if any, enter the attribute in '||
'the FACTS I Journal Trading Partner field of the Define '||
'Federal System Parameters window, and rerun the Year End '||
'Close Program.');
SELECT MIN(start_date), MAX(end_date)
INTO vg_start_date, vg_end_date
FROM gl_periods glp, gl_sets_of_books gsob
WHERE glp.period_year = vp_closing_fyr
AND glp.period_set_name = gsob.period_set_name
AND gsob.chart_of_accounts_id = vg_coa_id
AND gsob.set_of_books_id = vg_sob_id;
SELECT period_name, period_num
INTO vg_closing_period, vg_closing_period_num
FROM Gl_Period_Statuses
WHERE ledger_id = vg_sob_id
AND application_id = 101
AND period_year = vp_closing_fyr
AND period_num = (SELECT MAX(period_num)
FROM gl_period_statuses
WHERE ledger_id = vg_sob_id
AND application_id = 101
AND period_year = vp_closing_fyr);
SELECT COUNT(*)
INTO vg_factsi_bal_cnt
FROM Fv_Facts1_Run
WHERE period_num = vg_closing_period_num
AND set_of_books_id = vg_sob_id
AND fiscal_year = vp_closing_fyr;
'trading partner attributes, delete the journal entries '||
'created by this process, if any, run the FACTS I Interface '||
'program with all edit checks passed by period '||
vg_closing_period||' and rerun the Year End Close Program.');
PROCEDURE Chk_Dynamic_Insertion IS
l_module_name VARCHAR2(200) ;
e_nodynamic_insert EXCEPTION;
vl_dyn_ins_flag Fnd_Id_Flexs.dynamic_inserts_feasible_flag%TYPE;
|| ' Chk_Dynamic_Insertion';
SELECT dynamic_inserts_feasible_flag
INTO vl_dyn_ins_flag
FROM Fnd_Id_Flexs
WHERE application_id = 101
AND id_flex_code = 'GL#';
RAISE e_nodynamic_insert;
WHEN e_nodynamic_insert THEN
vp_retcode := 1;
vp_errbuf := 'Error in Chk_Dynamic_Insertion:Dynamic Inserts '||
'Feasible Flag is not set to Yes.';
vp_errbuf := SQLERRM ||' -- Error in Chk_Dyanmic_Insertionprocedure.';
END Chk_Dynamic_Insertion;
SELECT application_column_name
FROM fnd_id_flex_segments
WHERE id_flex_code = 'GL#'
AND id_flex_num = vg_coa_id
ORDER BY segment_num;
SELECT flex_value_set_id
INTO vg_acct_val_set_id
FROM Fnd_Id_Flex_Segments
WHERE application_column_name = vg_acct_segment
AND application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = vg_coa_id
AND enabled_flag = 'Y';
SELECT COUNT(*)
INTO vg_num_segs
FROM Fnd_Id_Flex_Segments
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = vg_coa_id
AND enabled_flag = 'Y';
SELECT DISTINCT to_account
FROM Fv_Ye_Sequence_Accounts
WHERE set_of_books_id = vg_sob_id
ORDER BY to_account;
SELECT summary_flag
FROM Fnd_Flex_Values_Vl
WHERE flex_value_set_id = vg_acct_val_set_id
AND flex_value = vl_acct;
DELETE FROM Fv_Ye_Seq_Bal_Temp WHERE set_of_books_id = vg_sob_id;
SELECT COUNT(*)
INTO vl_reccnt
FROM Fv_Ye_Seq_Bal_Temp
WHERE set_of_books_id = vg_sob_id;
'SELECT time_frame,fund_group_code,
expiration_date,cancellation_date
FROM Fv_Treasury_Symbols
WHERE treasury_symbol = :trsymbol
AND set_of_books_id = :sob'
USING vp_trsymbol,vg_sob_id;
-- either expired or cancelled are selected for processsing. For multi-year
-- and no-year timeframes, the unexpired treasury symbols also need to be
-- processed. Bug 2527452.
IF (vp_timeframe = 'SINGLE') THEN -- timeframe 1
OPEN vc_checkpara FOR
'SELECT treasury_symbol
FROM Fv_Treasury_symbols
WHERE set_of_books_id = :sob
AND time_frame = :timeframe
AND fund_group_code = :fundgroup
AND ((expiration_date <= :end_date)
OR (cancellation_date <= :end_date))
ORDER BY treasury_symbol'
USING vg_sob_id,
vp_timeframe,
vp_fundgroup,
vg_end_date,
vg_end_date;
'SELECT treasury_symbol
FROM Fv_Treasury_symbols
WHERE set_of_books_id = :sob_id
AND time_frame = :timeframe
AND fund_group_code = :fundgroup
ORDER BY treasury_symbol'
USING vg_sob_id,vp_timeframe,vp_fundgroup;
-- either expired or cancelled are selected for processsing. For multi-year
-- and no-year timeframes, the unexpired treasury symbols also need to be
-- processed. Bug 2527452.
IF (vp_timeframe = 'SINGLE') THEN -- timeframe 2
OPEN vc_checkpara FOR
'SELECT treasury_symbol,fund_group_code
FROM Fv_Treasury_symbols
WHERE set_of_books_id = :sob_id
AND time_frame = :timeframe
AND ((expiration_date <= :end_date)
OR (cancellation_date <= :end_date))
ORDER BY treasury_symbol'
USING vg_sob_id,vp_timeframe,vg_end_date,vg_end_date;
'SELECT treasury_symbol,fund_group_code
FROM Fv_Treasury_symbols
WHERE set_of_books_id = :sob_id
AND time_frame = :timeframe
ORDER BY treasury_symbol'
USING vg_sob_id,vp_timeframe;
SELECT treasury_symbol_id
FROM Fv_Treasury_Symbols
WHERE treasury_symbol = trsymbol
AND set_of_books_id = vg_sob_id;
'SELECT group_id
FROM Fv_Ye_Groups
WHERE treasury_symbol_id = :trsymbol_id
AND fund_group_code = :fundgroup
AND fund_time_frame = :timeframe
AND set_of_books_id = :sob_id'
USING vg_trsymbol_id,fundgroup,timeframe,vg_sob_id;
'SELECT group_id
FROM Fv_Ye_Groups
WHERE treasury_symbol_id IS NULL
AND fund_group_code = :fundgroup
AND fund_time_frame = :timeframe
AND set_of_books_id = :sob_id'
USING fundgroup,timeframe,vg_sob_id;
'SELECT group_id
FROM Fv_Ye_Groups
WHERE treasury_symbol_id IS NULL
AND fund_group_code IS NULL
AND fund_time_frame = :timeframe
AND set_of_books_id = :sob_id'
USING timeframe,vg_sob_id;
SELECT fund_value
FROM Fv_Fund_Parameters
WHERE treasury_symbol_id = vg_trsymbol_id
AND set_of_books_id = vg_sob_id;
Update_Closing_Status;
SELECT fts.expiration_date,
fts.cancellation_date,
fts.time_frame
FROM Fv_Treasury_Symbols fts, Fv_Fund_Parameters ffp
WHERE fts.treasury_symbol_id = vg_trsymbol_id
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND ffp.fund_value = vg_fund_value
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND fts.set_of_books_id = vg_sob_id
AND ffp.set_of_books_id = fts.set_of_books_id;
SELECT DECODE(i,1,'Expired',2,'Canceled')
INTO vg_acct_flag
FROM DUAL;
SELECT DECODE(vl_status_flag,'E','Expired','C','Canceled','U','Unexpired')
INTO vg_acct_flag
FROM DUAL;
SELECT sequence_id,SEQUENCE
FROM Fv_Ye_Group_Sequences
WHERE group_id = vg_group_id
AND set_of_books_id = vg_sob_id
ORDER BY SEQUENCE;
SELECT from_account,to_account
FROM Fv_Ye_Sequence_Accounts
WHERE sequence_id = vg_seq_id
AND account_flag = vg_acct_flag
AND set_of_books_id = vg_sob_id
ORDER BY order_by_ctr;
SELECT COUNT(*)
FROM Fv_Ye_Sequence_Accounts
WHERE sequence_id = vg_seq_id
AND set_of_books_id = vg_sob_id;
vl_select VARCHAR2(2000);
SELECT child_flex_value_low, child_flex_value_high
FROM Fnd_Flex_Value_Hierarchies
WHERE parent_flex_value = vg_from_acct
AND flex_value_set_id = vg_acct_val_set_id;
SELECT flex_value
FROM Fnd_Flex_Values_Vl
WHERE flex_value_set_id = vg_acct_val_set_id
AND flex_value BETWEEN vl_child_low AND vl_child_high
ORDER BY flex_value;
SELECT COUNT(*)
INTO vl_parent_cnt
FROM Fnd_Flex_Values_Vl
WHERE flex_value_set_id = vg_acct_val_set_id
AND summary_flag = 'Y'
AND flex_value = vg_from_acct;
vl_select := 'SELECT COUNT(*)
FROM Gl_Balances glb,Gl_Code_Combinations gcc
WHERE glb.code_combination_id = gcc.code_combination_id
AND gcc.'||vg_bal_segment||' = :fund_value'||
' AND gcc.'||vg_acct_segment|| ' = :from_acct'||
' AND gcc.summary_flag = '||''''||'N'||''''||
' AND gcc.template_id IS NULL
AND glb.actual_flag = '||''''||'A'||''''||
' AND glb.ledger_id = :sob
AND gcc.chart_of_accounts_id = :coa
AND glb.period_year = :closing_fyr
AND glb.period_name = :closing_period
AND glb.currency_code = :currency';
EXECUTE IMMEDIATE vl_select INTO vl_bal_cnt USING
vg_fund_value,vg_from_acct,vg_sob_id,
vg_coa_id,vp_closing_fyr,vg_closing_period,vg_currency;
SELECT DISTINCT balance_read_flag
INTO vg_balance_read_flag
FROM Fv_Ye_Seq_Bal_Temp
WHERE account_seg = vg_child_acct
AND balance_seg = vg_fund_value
AND set_of_books_id = vg_sob_id
AND fiscal_year = vp_closing_fyr
AND balance_read_flag = 'Y'
AND group_id = vg_group_id;
vl_select VARCHAR2(2000);
SELECT NVL(SUM(bal_seq_amt),0) amt,code_combination_id
FROM Fv_Ye_Seq_Bal_Temp
WHERE account_seg = vg_child_acct
AND balance_seg = vg_fund_value
AND set_of_books_id = vg_sob_id
AND group_id = vg_group_id
AND fiscal_year = vp_closing_fyr
AND balance_read_flag = 'N'
GROUP BY code_combination_id;
SELECT NVL(SUM(bal_seq_amt),0) amt,code_combination_id
FROM Fv_Ye_Seq_Bal_Temp
WHERE account_seg = vg_child_acct
AND balance_seg = vg_fund_value
AND set_of_books_id = vg_sob_id
AND group_id = vg_group_id
AND fiscal_year = vp_closing_fyr
AND balance_read_flag = 'Y'
AND SEQUENCE IN (SELECT MAX(SEQUENCE)
FROM Fv_Ye_Seq_Bal_Temp g
WHERE g.account_seg = vg_from_acct
AND g.balance_seg = vg_fund_value
AND g.set_of_books_id = vg_sob_id
AND g.group_id = vg_group_id
AND g.balance_read_flag = 'Y')
GROUP BY code_combination_id;
SELECT NVL(SUM(bal_seq_amt),0) amt,code_combination_id
FROM Fv_Ye_Seq_Bal_Temp
WHERE account_seg = vg_child_acct
AND balance_seg = vg_fund_value
AND set_of_books_id = vg_sob_id
AND group_id = vg_group_id
AND fiscal_year = vp_closing_fyr
AND balance_read_flag = 'N'
AND SEQUENCE < vg_seq
GROUP BY code_combination_id;
vt_ccid.DELETE;
vl_select := 'SELECT glb.code_combination_id,
NVL(SUM(NVL(begin_balance_dr,0) + NVL(period_net_dr,0)) -
SUM(NVL(begin_balance_cr,0) + NVL(period_net_cr,0)),0)
FROM Gl_Balances glb,Gl_Code_Combinations gcc
WHERE glb.code_combination_id = gcc.code_combination_id
AND gcc.'||vg_bal_segment|| ' = :fund_value'||
' AND gcc.'||vg_acct_segment|| ' = :child_acct'||
' AND gcc.summary_flag = '||''''||'N'||''''||
' AND gcc.template_id IS NULL
AND glb.actual_flag = '||''''||'A'||''''||
' AND glb.ledger_id = :sob
AND gcc.chart_of_accounts_id = :coa
AND glb.period_year = :closing_fyr
AND glb.period_name = :closing_period
AND glb.currency_code = :currency
GROUP BY glb.code_combination_id ';
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,vl_select);
OPEN vc_getbal FOR vl_select USING vg_fund_value,
vg_child_acct,
vg_sob_id,
vg_coa_id,
vp_closing_fyr,
vg_closing_period,
vg_currency;
SELECT NVL(SUM(bal_seq_amt),0)
INTO vg_bal_seq_amt
FROM Fv_Ye_Seq_Bal_Temp
WHERE account_seg = vg_child_acct
AND balance_seg = vg_fund_value
AND set_of_books_id = vg_sob_id
AND group_id = vg_group_id
AND code_combination_id = vl_ccid
AND fiscal_year = vp_closing_fyr
AND balance_read_flag = 'N';
l_ccid_select varchar2(2048);
SELECT fifs.application_column_name
FROM fnd_id_flex_segments fifs
WHERE fifs.application_id = 101
AND fifs.id_flex_code = 'GL#'
AND fifs.id_flex_num = c_coa_id
AND fifs.enabled_flag = 'Y'
ORDER by fifs.segment_num;
l_ccid_select := 'SELECT code_combination_id
FROM gl_code_combinations gcc
WHERE gcc.chart_of_accounts_id = :coa_id ';
l_ccid_select := l_ccid_select ||
' and gcc.'||
flex_rec.application_column_name||
' = :c_'||
flex_rec.application_column_name;
dbms_sql.parse(l_cursor_id, l_ccid_select, dbms_sql.v7);
SELECT DECODE(i,1,vl_dbt_flag,vl_crt_flag)
INTO vl_drcr_flag
FROM DUAL;
SELECT DECODE(i,1,vg_balance_read_flag,'N')
INTO vl_read_flag
FROM DUAL;
-- Call the Insert_Balances procedure.
Insert_Balances(
vl_ccid,
vl_acct,
vg_bal_seq_amt,
vl_drcr_flag,
vl_read_flag,
vl_remaining_bal,
vl_processing_type,
vt_segments);
SELECT COUNT(*)
FROM Fv_Facts_Attributes
WHERE set_of_books_id = vg_sob_id
AND facts_acct_number = p_acct;
SELECT public_law_code,
advance_flag,
transfer_flag,
govt_non_govt
FROM Fv_Facts_Attributes
WHERE set_of_books_id = vg_sob_id
AND facts_acct_number = p_acct;
SELECT parent_flex_value
FROM Fnd_Flex_Value_Norm_Hierarchy
WHERE flex_value_set_id = vg_acct_val_set_id
AND vg_child_acct BETWEEN child_flex_value_low AND child_flex_value_high
ORDER BY parent_flex_value;
PROCEDURE Insert_Balances(ccid NUMBER,
acct VARCHAR2,
bal_amt NUMBER,
dr_cr VARCHAR2,
read_flag VARCHAR2,
remaining_bal NUMBER,
processing_type NUMBER,
segs Fnd_Flex_Ext.SegmentArray) IS
CURSOR flex_fields IS
SELECT application_column_name
FROM fnd_id_flex_segments
WHERE id_flex_code = 'GL#'
AND id_flex_num = vg_coa_id
AND enabled_flag = 'Y'
ORDER BY segment_num;
l_module_name := g_module_name || 'Insert_Balances ';
SELECT DECODE(dr_cr,'D',ABS(bal_amt),0)
INTO vl_period_dr
FROM DUAL;
SELECT DECODE(dr_cr,'D',0,ABS(bal_amt))
INTO vl_period_cr
FROM DUAL;
vp_errbuf := SQLERRM ||' -- Error in Insert_Balances procedure,'||
'while deriving the period_net_dr and period_net_cr.' ;
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN INSERT_BALANCES PROCEDURE,'||
'inserting the following:');
SELECT Fv_Ye_Seq_Bal_Temp_S.NEXTVAL
INTO vl_report_seq
FROM DUAL;
INSERT INTO Fv_Ye_Seq_Bal_Temp(
code_combination_id,
group_id,
SEQUENCE,
account_seg,
balance_seg,
period_net_dr,
period_net_cr,
bal_seq_amt,
period_name,
currency_code,
fiscal_year,
balance_read_flag,
set_of_books_id,
treasury_symbol_id,
account_flag,
report_sequence,
processing_type,
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)
VALUES
(ccid,
vg_group_id,
vg_seq,
acct,
vg_fund_value,
vl_period_dr,
vl_period_cr,
remaining_bal,
vg_closing_period,
vg_currency,
vp_closing_fyr,
read_flag,
vg_sob_id,
vg_trsymbol_id,
vg_acct_flag,
vl_report_seq,
processing_type,
vl_segments(1),vl_segments(2),vl_segments(3),vl_segments(4),vl_segments(5),
vl_segments(6),vl_segments(7),vl_segments(8),vl_segments(9),vl_segments(10),
vl_segments(11),vl_segments(12),vl_segments(13),vl_segments(14),vl_segments(15),
vl_segments(16),vl_segments(17),vl_segments(18),vl_segments(19),vl_segments(20),
vl_segments(21),vl_segments(22),vl_segments(23),vl_segments(24),vl_segments(25),
vl_segments(26),vl_segments(27),vl_segments(28),vl_segments(29),vl_segments(30));
vp_errbuf := SQLERRM ||' -- Error in Insert_Balances procedure.' ;
END Insert_Balances;
PROCEDURE Update_Closing_Status IS
CURSOR get_closereq_cur IS
SELECT ffp.close_requisitions
FROM fv_fund_parameters ffp, fv_treasury_symbols fts
WHERE fts.treasury_symbol = vg_trsymbol
AND ffp.fund_value = vg_fund_value
AND ffp.treasury_symbol_id = fts.treasury_symbol_id
AND ffp.set_of_books_id = vg_sob_id;
vl_dist_select VARCHAR2(2000);
SELECT COUNT(*)
FROM Po_Req_Distributions_All
WHERE requisition_line_id = vt_lines(vl_index)
AND gl_closed_date IS NULL;
SELECT DISTINCT requisition_header_id
FROM Po_Requisition_Lines_All
WHERE requisition_line_id = vt_lines(vl_index);
SELECT COUNT(*)
FROM Po_Requisition_Lines_All
WHERE requisition_header_id = vt_headers(vl_index)
AND closed_code <> 'FINALLY CLOSED';
l_module_name := g_module_name || 'Update_Closing_Status ';
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN THE UPDATE_CLOSING_STATUS PROCEDURE.....');
vl_dist_select := 'SELECT po.distribution_id,
po.requisition_line_id, ph.segment1
FROM Po_Req_Distributions_All po, gl_code_combinations gcc,
Po_Requisition_Lines_All pl, Po_Requisition_Headers_All ph
WHERE po.gl_closed_date IS NULL
AND gcc.code_combination_id = po.code_combination_id
AND gcc.chart_of_accounts_id = :coa
AND po.set_of_books_id = :sob
AND gcc.'||vg_bal_segment|| ' = :fund_value'||
' AND ph.authorization_status = '||''''||'APPROVED'||''''||
' AND po.requisition_line_id = pl.requisition_line_id
AND pl.requisition_header_id = ph.requisition_header_id
AND pl.line_location_id IS NULL
AND po.gl_encumbered_date <= :end_date' ;
--Fv_Utility.Debug_Mesg(vl_dist_select);
OPEN vl_dist_retcur FOR vl_dist_select USING vg_coa_id,vg_sob_id,
vg_fund_value,vg_end_date;
-- In this case,just go ahead and update the Po_Req_Distributions_All table
-- for the new distribution_id,without inseritng into the table.
IF (vl_prev_line_id <> vl_line_id) THEN -- vl_prev_line_id
-- insert into vt_lines table
vt_lines(vl_ctr) := vl_line_id;
-- Update the Po_Req_Distributions_All table
UPDATE Po_Req_Distributions_All
SET gl_closed_date = vg_end_date
WHERE distribution_id = vl_distr_id;
-- Update the Po_Requisition_Lines_All table
UPDATE Po_Requisition_Lines_All
SET closed_code = 'FINALLY CLOSED'
WHERE requisition_line_id = vt_lines(vl_index);
-- insert into the headers table
vt_headers(vl_head_ctr) := vl_header_id;
-- Update the Po_Requisition_Headers_All table
UPDATE Po_Requisition_Headers_All
SET closed_code = 'FINALLY CLOSED'
WHERE requisition_header_id = vt_headers(vl_index);
vt_lines.DELETE;
vt_headers.DELETE;
vp_errbuf := SQLERRM ||' -- Error in Update_Closing_Status procedure.' ;
END Update_Closing_Status;
SELECT
code_combination_id,
account_seg,
balance_seg,
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,
period_net_dr,
period_net_cr,
period_name ,
balance_read_flag,
processing_type
FROM Fv_Ye_Seq_Bal_Temp
WHERE period_net_dr + period_net_cr > 0
AND set_of_books_id = vg_sob_id
ORDER BY report_sequence;
SELECT COUNT(*),SUM(NVL(amount,0))
FROM Fv_Facts1_Period_Balances_v
WHERE set_of_books_id = vg_sob_id
AND period_num <= vg_closing_period_num
AND period_year = vp_closing_fyr
AND ccid = vl_ccid;
SELECT SUM(NVL(amount,0)) amount,eliminations_dept,g_ng_indicator
FROM Fv_Facts1_Period_Balances_v
WHERE set_of_books_id = vg_sob_id
AND period_num <= vg_closing_period_num
AND period_year = vp_closing_fyr
AND ccid = vl_ccid
GROUP BY eliminations_dept,g_ng_indicator;
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,
vg_sob_id);
Insert_Gl_Interface_Record(vc_journals.period_net_dr,
vc_journals.period_net_cr, vl_reference_1,
vl_period_name,vl_trading_partner);
'delete the journal entries created by this process, '||
'if any, run the FACTS I Interface Program with all edit '||
'checks passed by period '||vg_closing_period||
'and rerun the Year End Close Program.');
Insert_Gl_Interface_Record(vc_journals.period_net_dr,
vc_journals.period_net_cr, vl_reference_1,
vl_period_name,vl_trading_partner);
'delete the journal entries created by this process, '||
'if any, run the FACTS I Interface Program with all edit '||
'checks passed by period '||vg_closing_period||
'and rerun the Year End Close Program.');
Insert_Gl_Interface_Record(vc_journals.period_net_dr,
vc_journals.period_net_cr, vl_reference_1,
vl_period_name,vl_trading_partner);
Insert_Gl_Interface_Record(vl_amount_dr,vl_amount_cr,
vl_reference_1,vl_period_name,vl_trading_partner);
Insert_Gl_Interface_Record(vc_journals.period_net_dr,
vc_journals.period_net_cr, vl_reference_1,
vl_period_name,vl_trading_partner);
' SELECT MAX(Fv_Ye_Carryforward.Convert_To_Num (l.reference_1)) reference_1 ,
SUM( NVL(entered_dr, 0) - NVL(entered_cr,0) ) line_amount ' ||
vl_attribute_cols ||
' FROM gl_je_lines l , fv_be_trx_dtls B, gl_je_headers h
WHERE l.code_combination_id = :ccid
AND l.je_header_id = h.je_header_id
AND NVL(h.je_from_sla_flag, ''N'') IN (''N'',''U'')
-- AND l.gl_sl_link_id is null
AND EXISTS (SELECT 1
FROM gl_period_statuses glp
WHERE glp.application_id = 101
AND glp.set_of_books_id = :sob_id
AND glp.ledger_id = :sob_id
AND glp.period_year = :closing_fyr
AND glp.period_name = l.period_name)
AND NVL(l.reference_1, ''-99'') = TO_CHAR(b.transaction_id (+))
AND l.status = :je_status
AND b.set_of_books_id(+) = :sob_id
AND h.ledger_id = :sob_id
AND b.set_of_books_id = h.ledger_id
'|| vl_group_by_clause ||
' UNION
SELECT MAX(xdl.SOURCE_DISTRIBUTION_ID_NUM_1) reference_1 ,
SUM( NVL(xl.entered_dr, 0) - NVL(xl.entered_cr,0) ) line_amount '
||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_import_references gli
WHERE xl.code_combination_id = :ccid
AND xl.ae_header_id = xdl.ae_header_id
AND xl.ae_line_num = xdl.ae_line_num
AND xl.gl_sl_link_id = gli.gl_sl_link_id
and gli.je_batch_id = h.je_batch_id
and gli.je_header_id = h.je_header_id
and gli.je_line_num = l.je_line_num
AND NVL(h.je_from_sla_flag, ''N'') = ''Y''
--l.gl_sl_link_id is not null
AND l.je_header_id = h.je_header_id
AND EXISTS (SELECT 1
FROM gl_period_statuses glp
WHERE glp.application_id = 101
AND glp.set_of_books_id = :sob_id
AND glp.period_year = :closing_fyr
AND glp.period_name = l.period_name)
AND NVL(xdl.SOURCE_DISTRIBUTION_ID_NUM_1, '||''''||'-99'||''''||')
= b.transaction_id (+)
AND l.status = :je_status
AND h.ledger_id = :sob_id
AND b.set_of_books_id = h.LEDGER_id'
||vl_group_by_clause;
Insert_gl_interface_record(vl_amount_dr,vl_amount_cr,vl_reference_1,
vl_period_name,vl_trading_partner, vl_public_law_code,
vl_advance_type, vl_trf_dept_id, vl_trf_main_acct);
FOR facts2_ending_balance_rec IN (SELECT ffeb.ending_balance_cr,
ffeb.ending_balance_dr,
ffeb.transfer_dept_id,
ffeb.public_law,
ffeb.advance_flag,
ffeb.transfer_main_acct
FROM fv_factsii_ending_balances ffeb
WHERE ffeb.set_of_books_id = vg_sob_id
AND ffeb.fiscal_year = vp_closing_fyr-1
AND ffeb.ccid = vl_ccid) LOOP
vl_running_amount := vl_running_amount + NVL(facts2_ending_balance_rec.ending_balance_dr, 0) - NVL(facts2_ending_balance_rec.ending_balance_cr, 0);
Insert_gl_interface_record(facts2_ending_balance_rec.ending_balance_cr,facts2_ending_balance_rec.ending_balance_dr,
NULL,
vl_period_name,vl_trading_partner,
facts2_ending_balance_rec.public_law,
facts2_ending_balance_rec.advance_flag,
facts2_ending_balance_rec.transfer_dept_id,
facts2_ending_balance_rec.transfer_main_acct);
Insert_gl_interface_record(vl_amount_dr , vl_amount_cr, NULL,
vl_period_name,vl_trading_partner);
Insert_Gl_Interface_Record(vc_journals.period_net_dr,
vc_journals.period_net_cr, vl_reference_1,
vl_period_name,vl_trading_partner);
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 ledger_id = vg_sob_id
AND group_id = vg_jrnl_group_id;
PROCEDURE insert_gl_interface_record(l_amount_dr IN NUMBER,
l_amount_cr IN NUMBER,
l_reference_1 IN VARCHAR2,
l_period_name IN VARCHAR2,
l_trading_partner IN VARCHAR2,
l_public_law_code IN VARCHAR2,
l_advance_type IN VARCHAR2,
l_trf_dept_id IN VARCHAR2,
l_trf_main_acct IN VARCHAR2)
IS
TYPE attribtable IS TABLE OF gl_je_lines.attribute1%TYPE
INDEX BY BINARY_INTEGER;
l_module_name := g_module_name || 'insert_gl_interface_record ';
vl_str := '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,
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,
reference21,context,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
attribute16, attribute17, attribute18, attribute19, attribute20
)
VALUES
(
:status, :sob_id, :end_date, :currency,
:current_date, :user_id, :actual_flag, :user_je_category,
:user_je_source, :amount_dr, :amount_cr, :jrnl_group_id,
:period_name, :coa_id,
:vt_segments_1,:vt_segments_2,:vt_segments_3,
:vt_segments_4,:vt_segments_5,:vt_segments_6,
:vt_segments_7,:vt_segments_8,:vt_segments_9,
:vt_segments_10, :vt_segments_11,:vt_segments_12,
:vt_segments_13, :vt_segments_14,:vt_segments_15,
:vt_segments_16, :vt_segments_17,:vt_segments_18,
:vt_segments_19, :vt_segments_20, :vt_segments_21,
:vt_segments_22,:vt_segments_23, :vt_segments_24,
:vt_segments_25,:vt_segments_26, :vt_segments_27,
:vt_segments_28,:vt_segments_29, :vt_segments_30,
:reference_1,:context,
:attribute1, :attribute2, :attribute3, :attribute4, :attribute5,
:attribute6, :attribute7, :attribute8, :attribute9, :attribute10,
:attribute11, :attribute12, :attribute13, :attribute14, :attribute15,
:attribute16, :attribute17, :attribute18, :attribute19, :attribute20
) ' ;
vp_errbuf := SQLERRM || '--Error in Insert_Gl_Interface_Record procedure.';
END insert_gl_interface_record;
l_select_stmt varchar2(2500);
L_select_stmt := ' SELECT ffp.fund_value
FROM fv_fund_parameters ffp,
Fv_Ye_Groups fyg ,
fv_treasury_symbols fts
WHERE fyg.fund_group_code = fts.fund_group_code
AND fts.time_frame = fyg.fund_time_frame
AND fts.treasury_symbol_id = fyg.treasury_symbol_id
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND fyg.fund_group_code = NVL(:fundgroup, fyg.fund_group_code)
AND fyg.fund_time_frame = NVL(:timeframe, fyg.fund_time_frame)
AND fyg.treasury_symbol = NVL(:TSYMBOLID, fyg.treasury_symbol_id)
AND fts.set_of_books_id = :sob_id
AND fts.set_of_books_id = fyg.set_of_books_id
AND fts.set_of_books_id = ffp.set_of_books_id
AND ((fts.expiration_date <= :end_date)
OR (fts.cancellation_date <= :end_date))';
EXECUTE IMMEDIATE L_SELECT_STMT BULK COLLECT INTO v_fund_blk_tbl
USING vp_fund_grp,
vp_time_frame,
vp_tsymbol_id ,
vp_sob_id,
vp_end_date,
vp_end_date;
SELECT 'N'
INTO vl_invalid_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 (+) = v_fund_blk_tbl(i);