[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
gbl_update_end_balance VARCHAR2(1);
PROCEDURE update_facts1_run(p_period_year in VARCHAR2,
p_set_of_books_id in VARCHAR2);
SELECT count(*)
INTO l_row_exists
FROM fv_facts_ending_balances
WHERE fiscal_year = gbl_period_year
AND set_of_books_id = gbl_set_of_books_id
AND rownum = 1;
DELETE FROM fv_facts_ending_balances
WHERE set_of_books_id = gbl_set_of_books_id
AND fiscal_year = gbl_period_year;
fv_utility.log_mesg('Deleted '||SQL%ROWCOUNT ||' recs from fv_facts_ending_balances.');
UPDATE fv_facts1_run
SET status = decode(l_edit_check_status , 'Y', 'S' , 'F'),
run_fed_flag = 'I',
process_date = sysdate,
begin_bal_diff_flag = 'Y',
period_num = gbl_period_num_high
WHERE set_of_books_id = gbl_set_of_books_id
AND fiscal_year = gbl_fiscal_year
AND table_indicator = 'N';
SELECT chart_of_accounts_id
INTO gbl_coa_id
FROM gl_ledgers_public_v
WHERE ledger_id = gbl_set_of_books_id;
SELECT flex_value_set_id
INTO gbl_acc_value_set_id
FROM fnd_id_flex_segments
WHERE application_column_name = gbl_acc_segment_name
AND id_flex_code = 'GL#'
AND id_flex_num = gbl_coa_id;
SELECT MIN(period_num)
INTO gbl_period_num_low
FROM gl_period_statuses
WHERE period_year = gbl_fiscal_year
AND application_id = 101
AND closing_status <> 'F'
AND closing_status <> 'N'
AND adjustment_period_flag = 'N'
AND ledger_id = gbl_set_of_books_id;
SELECT MAX(period_num)
INTO gbl_period_num_high
FROM gl_period_statuses
WHERE period_year = gbl_fiscal_year
AND application_id = 101
AND closing_status <> 'F'
AND closing_status <> 'N'
AND ledger_id = gbl_set_of_books_id;
SELECT period_name
INTO gbl_period_name
FROM gl_period_statuses
WHERE period_num = gbl_period_num_high
AND period_year = gbl_fiscal_year
AND application_id = 101
AND ledger_id = gbl_set_of_books_id;
SELECT period_num, period_year, closing_status
INTO gbl_period_num_high, gbl_fiscal_year, l_closing_status
FROM gl_period_statuses
WHERE period_name = gbl_period_name
AND application_id = 101
AND ledger_id = gbl_set_of_books_id;
SELECT Max(period_num)
INTO gbl_period_num_high
FROM gl_period_statuses
WHERE period_year = gbl_fiscal_year
AND application_id = 101
AND closing_status <> 'F'
AND closing_status <> 'N'
AND period_num <= gbl_period_num_high
AND ledger_id = gbl_set_of_books_id;
SELECT MIN(period_num)
INTO gbl_period_num_low
FROM gl_period_statuses
WHERE period_year = gbl_fiscal_year
AND application_id = 101
AND adjustment_period_flag = 'N'
AND ledger_id = gbl_set_of_books_id;
SELECT MAX(period_num)
INTO l_end_period_num
FROM gl_period_statuses
WHERE period_year = gbl_fiscal_year
AND application_id = 101
AND ledger_id = gbl_set_of_books_id;
gbl_update_end_balance := 'Y';
SELECT period_name
INTO gbl_low_period_name
FROM gl_period_statuses
WHERE period_num = gbl_period_num_low
AND period_year = gbl_fiscal_year
AND application_id = 101
AND ledger_id = gbl_set_of_books_id;
SELECT currency_code
INTO gbl_currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = gbl_set_of_books_id;
SELECT ffp.fund_value fund_val, fts.fund_group_code fund_grp,
fts.department_id dep_id, fts.bureau_id bu_id, fts.federal_acct_symbol_id
FROM fv_treasury_symbols fts, fv_fund_parameters ffp
WHERE ffp.set_of_books_id = gbl_set_of_books_id
AND fts.treasury_symbol_id = ffp.treasury_symbol_id;
SELECT facts1_rollup
FROM fv_facts_federal_accounts
WHERE set_of_books_id = gbl_set_of_books_id
AND federal_acct_symbol_id = ln_federal_acct_symbol_id;
UPDATE fv_fund_parameters
SET department_id = fund_rec.dep_id,
bureau_id = fund_rec.bu_id,
fund_group_code = l_fund_group
WHERE fund_value = fund_rec.fund_val
AND set_of_books_id = gbl_set_of_books_id;
SELECT department_id,
bureau_id,
fund_group_code
INTO p_dept_id,
p_bureau_id,
p_fund_group
FROM fv_fund_parameters
WHERE fund_value = p_fund_value
AND set_of_books_id = gbl_set_of_books_id;
'Inserting a record in T2 for record_category :'||p_record_category||' for ccid: '||p_ccid);
INSERT INTO fv_facts_report_t2
( fund_group,
account_number,
dept_id,
bureau_id,
eliminations_dept,
g_ng_indicator,
amount,
d_c_indicator,
fiscal_year,
record_category,
ussgl_account,
set_of_books_id,
reported_status,
exch_non_exch,
cust_non_cust,
budget_subfunction,
fund_value,
beginning_balance,
ccid,
account_type,
recipient_name,
dr_amount,
cr_amount)
VALUES
( p_fund_group,
p_account_number,
p_dept_id,
p_bureau_id,
p_eliminations_dept,
p_g_ng_indicator,
nvl(p_amount, 0),
DECODE(SIGN(nvl(p_amount, 0)), 0 ,'D', 1, 'D', -1, 'C'),
p_fiscal_year,
p_record_category,
p_ussgl_account,
p_set_of_books_id,
p_reported_status,
p_exch_non_exch,
p_cust_non_cust,
p_budget_subfunction,
p_fund_value,
p_beginning_bal,
p_ccid,
p_account_type,
p_recipient_name,
p_dr_amount,
p_cr_amount);
SELECT 'X', govt_non_govt, exch_non_exch, cust_non_cust, budget_subfunction
INTO l_exists, l_g_ng_ind, l_e_ne_ind, l_c_nc_ind, l_budget_sub_ind
FROM fv_facts_attributes
WHERE facts_acct_number = p_acct_num
AND set_of_books_id = gbl_set_of_books_id;
SELECT 'X', budget_subfunction
INTO l_row_exists, l_budget_sub
FROM fv_fund_parameters
WHERE fund_value = P_FUND_VALUE
AND set_of_books_id = gbl_set_of_books_id;
SELECT 'X', fts.cust_non_cust
INTO l_row_exists, l_c_nc
FROM fv_treasury_symbols fts, fv_fund_parameters ffp
WHERE fts.treasury_symbol_id = ffp.treasury_symbol_id
AND ffp.set_of_books_id = gbl_set_of_books_id
AND ffp.fund_value = P_FUND_VALUE;
SELECT 'X', ffp.cust_non_cust
INTO l_row_exists, l_c_nc
FROM fv_fund_parameters ffp
WHERE ffp.set_of_books_id = gbl_set_of_books_id
AND ffp.fund_value = P_FUND_VALUE;
SELECT 'X', govt_non_govt, exch_non_exch, cust_non_cust, budget_subfunction
INTO l_exists, l_g_ng_ind, l_e_ne_ind, l_c_nc_ind, l_budget_sub_ind
FROM fv_facts_attributes
WHERE facts_acct_number = p_acct_num
AND set_of_books_id = gbl_set_of_books_id;
SELECT parent_flex_value
INTO l_ussgl_acct_num
FROM fnd_flex_value_hierarchies
WHERE (p_acct_num BETWEEN child_flex_value_low
AND child_flex_value_high)
AND flex_value_set_id = gbl_acc_value_set_id
AND parent_flex_value <> 'T'
AND parent_flex_value IN
(SELECT ussgl_account
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = parent_flex_value);
SELECT 'X', budget_subfunction
INTO l_row_exists, l_budget_sub
FROM fv_fund_parameters
WHERE fund_value = P_FUND_VALUE
AND set_of_books_id = gbl_set_of_books_id;
SELECT 'X', fts.cust_non_cust
INTO l_row_exists, l_c_nc
FROM fv_treasury_symbols fts, fv_fund_parameters ffp
WHERE fts.treasury_symbol_id = ffp.treasury_symbol_id
AND ffp.set_of_books_id = gbl_set_of_books_id
AND ffp.fund_value = P_FUND_VALUE;
SELECT 'X', ffp.cust_non_cust
INTO l_row_exists, l_c_nc
FROM fv_fund_parameters ffp
WHERE ffp.set_of_books_id = gbl_set_of_books_id
AND ffp.fund_value = P_FUND_VALUE;
SELECT 'X', govt_non_govt, exch_non_exch, cust_non_cust, budget_subfunction
INTO l_exists, l_g_ng_ind, l_e_ne_ind, l_c_nc_ind, l_budget_sub_ind
FROM fv_facts_attributes
WHERE facts_acct_number = l_ussgl_acct_num
AND set_of_books_id = gbl_set_of_books_id;
SELECT 'X', budget_subfunction
INTO l_row_exists, l_budget_sub
FROM fv_fund_parameters
WHERE fund_value = p_fund_value
AND set_of_books_id = gbl_set_of_books_id;
SELECT 'X', fts.cust_non_cust
INTO l_row_exists, l_c_nc
FROM fv_treasury_symbols fts, fv_fund_parameters ffp
WHERE fts.treasury_symbol_id = ffp.treasury_symbol_id
AND ffp.set_of_books_id = gbl_set_of_books_id
AND ffp.fund_value = P_FUND_VALUE;
SELECT 'X', ffp.cust_non_cust
INTO l_row_exists, l_c_nc
FROM fv_fund_parameters ffp
WHERE ffp.set_of_books_id = gbl_set_of_books_id
AND ffp.fund_value = P_FUND_VALUE;
SELECT SUBSTR(compiled_value_attributes, 5, 1)
INTO l_account_type
FROM fnd_flex_values
WHERE flex_value = p_account_number
AND flex_value_set_id = gbl_acc_value_set_id;
SELECT ussgl_enabled_flag, reporting_type
INTO p_enabled_flag, p_reporting_type
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = p_ussgl_acct_num;
SELECT fund_group, dept_id, bureau_id,
SUM(DECODE(d_c_indicator, 'D', 0, NVL(amount, 0))) credit_amount,
SUM(DECODE(d_c_indicator, 'C', 0, NVL(amount, 0))) debit_amount
FROM FV_FACTS1_PERIOD_BALANCES_V
WHERE set_of_books_id = p_set_of_books_id
AND period_year = p_period_year
and period_num <= p_period_num
GROUP BY fund_group, dept_id, bureau_id;
select name into l_ledger_name
from gl_ledgers_public_v where ledger_id=p_set_of_books_id;
INSERT INTO FV_FACTS_ENDING_BALANCES
(fund_group,
account_number,
dept_id,
bureau_id,
eliminations_dept,
g_ng_indicator,
exch_non_exch,
cust_non_cust,
budget_subfunction,
amount,
d_c_indicator,
fiscal_year,
record_category,
ussgl_account,
set_of_books_id,
reported_status,
fund_value,
beginning_balance,
ccid,
account_type,
recipient_name)
(SELECT /*+ PARALLEL(T2) */
t2.fund_group,
t2.account_number,
t2.dept_id,
t2.bureau_id,
t2.eliminations_dept,
t2.g_ng_indicator,
t2.exch_non_exch,
t2.cust_non_cust,
t2.budget_subfunction,
SUM(NVL(amount,0)),
t2.d_c_indicator,
gbl_fiscal_year,
'ENDING_BAL',
'',
gbl_set_of_books_id,
'',
t2.fund_value,
0,
t2.ccid,
t2.account_type,
t2.recipient_name
FROM fv_facts1_period_balances_v t2
WHERE t2.set_of_books_id = gbl_set_of_books_id
AND t2.end_bal_ind = 'Y'
AND nvl(t2.amount,0) <> 0
and period_year = gbl_fiscal_year
and (period_num <= gbl_period_num_high)
GROUP BY t2.fund_group, t2.account_number, t2.dept_id, t2.bureau_id,
t2.eliminations_dept, t2.g_ng_indicator, t2.exch_non_exch,
t2.cust_non_cust, t2.budget_subfunction, t2.d_c_indicator,
t2.fund_value, t2.ccid, t2.account_type, t2.recipient_name
HAVING SUM(NVL(amount,0)) <> 0) ;
fv_utility.log_mesg('Inserted '||SQL%ROWCOUNT ||' recs into fv_facts_ending_balances.');
DELETE FROM fv_facts_report_t2
WHERE set_of_books_id = gbl_set_of_books_id;
DELETE FROM fv_facts1_diff_balances
WHERE set_of_books_id = gbl_set_of_books_id
and period_year = gbl_period_year
and balance_type IN ('B', 'D');
FV_UTILITY.LOG_MESG('Deleted '||SQL%ROWCOUNT||
' records from fv_facts1_diff_balances.');
l_jrnl_select Varchar2(5000);
fnd_file.put_line(fnd_file.log , 'Inserting records into FV_FACTS_REPORT_T2');
INSERT INTO fv_facts_report_t2
(fund_group,
account_number,
dept_id,
bureau_id,
eliminations_dept,
g_ng_indicator,
amount,
d_c_indicator,
fiscal_year,
record_category,
ussgl_account,
set_of_books_id,
reported_status,
exch_non_exch,
cust_non_cust,
budget_subfunction,
fund_value,
ccid,
account_type,
beginning_balance,
dr_amount,
cr_amount)
SELECT
fund_group,
account_number,
dept_id,
bureau_id,
eliminations_dept,
g_ng_indicator,
0,
'N',
gbl_period_year,
'TRIAL_BALANCE',
ussgl_account,
gbl_set_of_books_id,
'R',
exch_non_exch,
cust_non_cust,
budget_subfunction,
fund_value,
ccid,
account_type,
sum(decode(balance_type,'G',period_begin_bal,
decode(period_num, gbl_period_num_high,0,amount) ) ) begin_balance,
sum(decode(balance_type, 'G' , period_dr,
decode(period_num , gbl_period_num_high,
decode(sign(amount) , 1 , amount , 0),0) ) ) period_dr,
sum(decode(balance_type, 'G' , period_cr,
decode(period_num , gbl_period_num_high,
decode(sign(amount) , 1 , 0 , amount),0) ) ) period_dr
from
fv_facts1_period_balances_v fpb
where fpb.set_of_books_id = gbl_set_of_books_id
and fpb.period_year = gbl_fiscal_year
and period_num <= gbl_period_num_high
and fund_value between gbl_fund_range_low and gbl_fund_range_high
GROUP BY fund_group,
account_number,
dept_id,
bureau_id,
eliminations_dept,
g_ng_indicator,
ussgl_account,
exch_non_exch,
cust_non_cust,
budget_subfunction,
fund_value,
ccid,
account_type,
period_num;
fnd_file.put_line(fnd_file.log , 'Completed inserting records into FV_FACTS_REPORT_T2 ' || SQL%ROWCOUNT);
SELECT period_num, period_year
INTO gbl_period_num_high, gbl_fiscal_year
FROM gl_period_statuses
WHERE period_name = p_period_name
AND application_id = 101
AND closing_status NOT IN ('F','N')
AND ledger_id = gbl_set_of_books_id;
SELECT MIN(period_num)
INTO gbl_period_num_low
FROM gl_period_statuses
WHERE period_year = gbl_fiscal_year
AND application_id = 101
AND closing_status <> 'F'
AND closing_status <> 'N'
AND adjustment_period_flag = 'N'
AND ledger_id = gbl_set_of_books_id;
SELECT name
INTO l_sob_name
FROM gl_ledgers
WHERE ledger_id = gbl_set_of_books_id
AND currency_code = 'USD';
SELECT column_name
FROM fa_rx_rep_columns_b
WHERE report_id = gbl_report_id
AND attribute_set = gbl_attribute_set
AND break = 'Y';
INSERT INTO fv_facts_report_t2
( fund_group,
account_number,
dept_id,
bureau_id,
d_c_indicator,
eliminations_dept,
g_ng_indicator,
amount,
record_category,
ussgl_account,
set_of_books_id,
exch_non_exch,
cust_non_cust,
budget_subfunction,
fund_value,
beginning_balance,
dr_amount,
cr_amount '||replace(l_group_by,'gcc.','')||')
(SELECT 0,
account_number,
'||''''||'0'||''''||',
'||''''||'0'||''''||',
'||''''||'N'||''''||',
eliminations_dept,
g_ng_indicator,
0,
'||''''||'TRIAL_BAL'||''''||',
ussgl_account,
:gbl_set_of_books_id,
exch_non_exch,
cust_non_cust,
budget_subfunction,
fund_value,
--SUM(beginning_balance),
SUM(period_begin_bal),
SUM(nvl(period_dr,0)),
SUM(nvl(period_cr,0)) '|| l_group_by ||'
FROM fv_facts_period_balances_tb_v t2, gl_code_combinations gcc
WHERE t2.set_of_books_id = :gbl_set_of_books_id
AND t2.ccid = gcc.code_combination_id
AND t2.period_num <= :gbl_period_num_high
AND t2.period_year = :gbl_fiscal_year
AND (period_begin_bal <> 0 OR
period_dr <> 0 OR
period_cr <> 0)
AND fund_value BETWEEN :gbl_fund_range_low AND :gbl_fund_range_high
GROUP BY account_number, eliminations_dept,
g_ng_indicator, ussgl_account, exch_non_exch, cust_non_cust, budget_subfunction,
--period_num, bug 8498455
fund_value'|| l_group_by ||')';
DELETE FROM fv_facts_report_t2
WHERE record_category <> 'TRIAL_BAL'
AND set_of_books_id = gbl_set_of_books_id;
l_select_stmt VARCHAR2(10000);
l_select_stmt2 VARCHAR2(10000);
select decode(period_num,null,'Y',0,'Y','N'),period_num into
l_populate_flag, l_int_run_month
from fv_facts1_run
WHERE set_of_books_id = gbl_set_of_books_id
AND fiscal_year = p_period_year;
DELETE FROM fv_facts_report_t2
WHERE set_of_books_id = gbl_set_of_books_id;
SELECT MAX(period_num)
INTO l_period_num_high
FROM gl_period_statuses
WHERE period_year = p_period_year
AND application_id = 101
AND closing_status <> 'F'
AND closing_status <> 'N'
AND ledger_id = gbl_set_of_books_id;
SELECT MIN(period_num)
INTO l_period_num_low
FROM gl_period_statuses
WHERE period_year = p_period_year
AND application_id = 101
AND closing_status <> 'F'
AND closing_status <> 'N'
AND adjustment_period_flag = 'N'
AND ledger_id = gbl_set_of_books_id;
SELECT period_name
INTO gbl_period_name
FROM gl_period_statuses
WHERE period_year = p_period_year
AND application_id = 101
AND period_num = l_period_num_high
AND ledger_id = gbl_set_of_books_id;
SELECT currency_code
INTO gbl_currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = gbl_set_of_books_id;
l_select_stmt2 := ' glb.code_combination_id, ' ||
' glc.' || gbl_bal_segment_name || ' , glc.' || gbl_acc_segment_name ||
', ''NO'', ''#'', ''#'', ''#'', ''#'', ''#'', ''#'', ''#'', ''E'', -99 ,''N'',
SUM (DECODE (period_num, :gbl_period_num_high,
(begin_balance_dr - begin_balance_cr + NVL(period_net_dr,0)
- NVL(period_net_cr,0)),0)) curr_year_bal,
SUM (DECODE (period_num, :gbl_period_num_low,
(begin_balance_dr - begin_balance_cr),0)) begin_bal,
SUM (DECODE (period_num, :gbl_period_num_high,
(NVL(period_net_dr,0)),0)) period_cy_bal,
SUM (DECODE (period_num, :gbl_period_num_high,
(NVL(period_net_cr,0)),0)) period_cy_cr_bal,
SUM (DECODE (period_num, :gbl_period_num_high,
(begin_balance_dr - begin_balance_cr),0)) period_begin_bal '||
' FROM gl_balances glb,gl_code_combinations GLC
WHERE glb.actual_flag = '||''''||'A'||''''||'
AND period_year = :gbl_fiscal_year
AND period_num IN (:gbl_period_num_low, :gbl_period_num_high)
AND glb.ledger_id = :gbl_set_of_books_id
AND glb.template_id is NULL
AND glb.currency_code = :gbl_currency_code
AND glc.code_combination_id = glb.code_combination_id '
|| l_acct_type_condition
||' GROUP BY glb.code_combination_id ,'||'glc.'||gbl_bal_segment_name
||', glc.' || gbl_acc_segment_name
||' ORDER BY '||'glc.'||gbl_bal_segment_name ||', glc.' || gbl_acc_segment_name;
'l_select_stmt: '||l_select_stmt);
l_select_stmt2 := ' SELECT ' || l_select_stmt2;
fnd_file.put_line(fnd_file.log, l_select_stmt2);
select NVL(begin_bal_diff_flag , 'N') into l_diff_flag
from fv_facts1_run
where set_of_books_id = gbl_set_of_books_id
and fiscal_year = gbl_fiscal_year;
DELETE FROM fv_facts1_diff_balances
WHERE set_of_books_id = gbl_set_of_books_id
and period_year = gbl_fiscal_year
and balance_type = 'B';
OPEN t1_record_c for l_select_stmt2 USING
l_period_num_high,
l_period_num_low,
l_period_num_high, l_period_num_high, l_period_num_high,
gbl_fiscal_year, l_period_num_low, l_period_num_high,gbl_set_of_books_id,
gbl_currency_code;
select 'N' into l_new_record_l(i)
from fv_facts1_period_attributes
where ccid = l_ccid_l(i)
and period_year = gbl_fiscal_year
and set_of_books_id = gbl_set_of_books_id;
/* Insert the new ccid */
If l_new_record_l(i) = 'Y' then
l_indx := l_indx + 1;
SELECT NVL(SUM(NVL(t2.amount, 0)), 0)
INTO l_t2_detail_amount
FROM fv_facts1_line_balances t2
WHERE t2.ccid = l_ccid_l(i)
AND t2.set_of_books_id = gbl_set_of_books_id
AND period_num <= gbl_period_num_high
AND period_year = gbl_fiscal_year;
fv_utility.log_mesg('*****inserting detail difference record');
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Inserting into fv_facts1_diff_balances values: ');
INSERT INTO fv_facts1_diff_balances
(
ccid,period_num,period_year,set_of_books_id,
eliminations_dept,
g_ng_indicator,
amount,
d_c_indicator,
balance_type,
recipient_name,
account_number,
fund_value)
VALUES
(l_ccid_l(i),gbl_period_num_high,gbl_fiscal_year,gbl_set_of_books_id,
DECODE(l_govt_non_govt_ind, 'F', '00', ' '),
DECODE(l_govt_non_govt_ind, 'F', l_govt_non_govt_ind, 'N'),
(l_curr_year_balance - l_t2_detail_amount),
DECODE(SIGN(l_curr_year_balance - l_t2_detail_amount),
0, 'D', 1, 'D', -1, 'C'),
'D','Other', l_account_number, l_fund_value);
SELECT NVL(SUM(amount), 0)
INTO l_ending_amount
FROM fv_facts_ending_balances
WHERE ccid = l_ccid_l(i)
AND set_of_books_id = gbl_set_of_books_id
AND fiscal_year = (gbl_fiscal_year - 1)
AND record_category = 'ENDING_BAL'
AND account_number = l_account_number
AND dept_id = l_dept_id
AND bureau_id = l_bureau_id
AND fund_value = l_fund_value
AND account_type IN ('A','L','O');
fv_utility.log_mesg('*****inserting end bal difference record');
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Inserting into fv_facts1_diff_balances values: ');
INSERT INTO fv_facts1_diff_balances
(ccid,period_num,period_year,set_of_books_id,
eliminations_dept,
g_ng_indicator,
amount,
d_c_indicator,
balance_type,
recipient_name,
account_number,
fund_value)
VALUES
(l_ccid_l(i),gbl_period_num_low,gbl_fiscal_year,gbl_set_of_books_id,
DECODE(l_govt_non_govt_ind, 'F', '00', ' '),
DECODE(l_govt_non_govt_ind, 'F', l_govt_non_govt_ind, 'N'),
l_begin_bal_l(i) - l_ending_amount,
DECODE(SIGN(l_begin_bal_l(i) - l_ending_amount), 0, 'D', 1, 'D', -1, 'C'),
'B', 'Other', l_account_number, l_fund_value);
FV_UTILITY.log_MESG('Inserting no of new records ' || l_indx);
INSERT INTO fv_facts1_period_attributes
( period_year,
period_num,
period_name,
set_of_books_id,
ccid,
fund_value,
account_number,
ussgl_account,
exch_non_exch ,
cust_non_cust,
account_type ,
budget_subfunction,
dept_id,
bureau_id,
g_ng_indicator,
reported_group,
fund_group,
new_rec_flag,
BALANCE_AMOUNT,
BEGIN_BALANCE,
PERIOD_CY_DR_BAL,
PERIOD_CY_CR_BAL ,
PERIOD_BEGIN_BAL,
end_bal_ind
)
values (
gbl_fiscal_year,
l_period_num_high,
gbl_period_name,
gbl_set_of_books_id,
l_ccid_n(i),
l_fund_value_n(i),
l_account_number_n(i),
l_sgl_acct_num_n(i),
l_exch_non_exch_n(i),
l_cust_non_cust_n(i),
l_account_type_n(i),
l_budget_subfunction_n(i),
decode(l_dept_id_n(i) ,NULL, '#', l_dept_id_n(i)),
decode(l_bureau_id_n(i),NULL, '#' , l_bureau_id_n(i)),
DECODE(l_govt_non_govt_ind_n(i), 'X', ' ', l_govt_non_govt_ind_n(i)),
l_exception_status_n(i),
decode(l_fund_group_n(i), NULL, -99 ,l_fund_group_n(i)),
'Y',
l_balance_amoun_n(i),
l_begin_bal_n(i),
l_cy_dr_bal_n(i),
l_cy_cr_bal_n(i),
l_per_begin_bal_n(i),
DECODE(l_govt_non_govt_ind_n(i), 'F', 'Y', 'Y', 'Y', 'N')
);
UPDATE fv_facts1_period_attributes
SET ussgl_account = l_sgl_acct_num_l(i),
exch_non_exch = l_exch_non_exch_l(i),
cust_non_cust = l_cust_non_cust_l(i),
account_type = l_account_type_l(i),
budget_subfunction = l_budget_subfunction_l(i),
fund_group = decode(l_fund_group_l(i), NULL, -99 ,l_fund_group_l(i)),
dept_id = decode(l_dept_id_l(i) ,NULL, '#', l_dept_id_l(i)),
bureau_id = decode(l_bureau_id_l(i),NULL, '#' , l_bureau_id_l(i)),
g_ng_indicator = DECODE(l_govt_non_govt_ind_l(i), 'X', ' ', l_govt_non_govt_ind_l(i)),
reported_group = l_exception_status_l(i),
BALANCE_AMOUNT = l_balance_amoun_l(i),
BEGIN_BALANCE = l_begin_bal_l(i),
PERIOD_CY_DR_BAL = l_cy_dr_bal_l(i),
PERIOD_CY_CR_BAL = l_cy_cr_bal_l(i),
PERIOD_BEGIN_BAL = l_per_begin_bal_l(i),
period_num = l_period_num_high,
period_name = gbl_period_name,
end_bal_ind = DECODE(l_govt_non_govt_ind_l(i), 'F', 'Y', 'Y', 'Y', 'N')
WHERE ccid = l_ccid_l(i)
and period_year = gbl_fiscal_year
and set_of_books_id = gbl_set_of_books_id
and l_new_record_l(i) = 'N';
SELECT COUNT(*)
INTO l_exception_count
FROM fv_facts_report_t2
WHERE set_of_books_id = gbl_set_of_books_id
AND reported_status = 'E'
and record_category NOT IN ('PROP_ACCT_NOT_SETUP', 'PROP_ACCT_FACTSII',
'USSGL_DISABLED', 'NO_FUND_GROUP' )
AND amount <> 0 ;
UPDATE fv_facts1_run
SET status = l_run_status,
process_date = sysdate,
run_fed_flag = 'I',
begin_bal_diff_flag = 'Y',
period_num = l_period_num_high
WHERE set_of_books_id = gbl_set_of_books_id
AND fiscal_year = p_period_year
AND table_indicator = 'N';
PROCEDURE update_facts1_run(p_period_year in VARCHAR2,
p_set_of_books_id in VARCHAR2)
is
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'UPDATE_FACTS1_RUN';
UPDATE fv_facts1_run
SET run_fed_flag = 'A',
process_date = sysdate
WHERE set_of_books_id = p_set_of_books_id
AND fiscal_year = p_period_year
AND table_indicator = 'N';
select nvl(min(je_header_id),0)
into l_je_header_id
from gl_je_headers h
WHERE ledger_id = gbl_set_of_books_id
and exists (select'x'
FROM gl_period_statuses g2
WHERE g2.period_year = p_period_year
AND g2.ledger_id = p_set_of_books_id
AND g2.application_id = 101
and g2.period_name = h.period_name);
select nvl(posted_date,creation_date)
into l_posted_date
from gl_je_headers h
WHERE je_header_id = l_je_header_id ;
SELECT currency_code
INTO gbl_currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = gbl_set_of_books_id;
INSERT INTO fv_facts1_run(set_of_books_id, fiscal_year, status, table_indicator,process_date,
run_fed_flag ,je_header_id,posted_date)
values(gbl_set_of_books_id, p_period_year, 'A', 'N',sysdate,'A' ,
l_je_header_id ,l_posted_date);
gbl_error_buf := SQLERRM || 'In UPDATE_FACTS1_RUN - '|| l_stage ;
gbl_error_buf := SQLERRM || 'When others error in UPDATE_FACTS1_RUN - '||SQLERRM;
END update_facts1_run;
SELECT facts_acct_number, govt_non_govt
FROM fv_facts_attributes
WHERE set_of_books_id = p_sob_id --vg_sob_id
AND govt_non_govt in ('F', 'Y');
SELECT child_flex_value_low, child_flex_value_high
FROM fnd_flex_value_hierarchies
WHERE flex_value_set_id = gbl_acc_value_set_id
AND parent_flex_value = vg_sgl_acct_number;
SELECT flex_value
FROM fnd_flex_values
WHERE flex_value_set_id = gbl_acc_value_set_id
AND flex_value BETWEEN vl_child_flex_value_low AND vl_child_flex_value_high;
SELECT parent_flex_value
INTO vg_sgl_acct_number
FROM fnd_flex_value_hierarchies
WHERE vg_acct_number
BETWEEN child_flex_value_low AND child_flex_value_high
AND flex_value_set_id = gbl_acc_value_set_id
AND parent_flex_value <> 'T'
AND parent_flex_value IN
(SELECT ussgl_account
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = parent_flex_value);
-- Insert it into FV_FACTS_FED_ACCOUNTS if not already present.
gbl_parent_flag := 'Y';
UPDATE_FACTS1_RUN(P_RUN_YEAR, GBL_SET_OF_BOOKS_ID);
select nvl(je_header_id,0),nvl(jc_run_month,0) into l_je_header_id,l_period_num
from fv_facts1_RUN
where set_of_books_id = gbl_set_of_books_id
AND fiscal_year = gbl_fiscal_year;
select count(*) into l_no_new_accounts from fv_facts1_fed_accounts
where set_of_books_id = gbl_set_of_books_id
AND fiscal_year = gbl_fiscal_year
and jc_flag = 'N';
select period_name into gbl_period_name
from gl_period_statuses
where ledger_id = gbl_set_of_books_id
AND period_year = gbl_fiscal_year
and application_id = 101
and period_num = l_period_num;
SELECT fed_non_fed
INTO vl_dummy
FROM fv_facts1_fed_accounts
WHERE account_number = vg_acct_number
AND set_of_books_id = gbl_set_of_books_id
AND fiscal_year = gbl_fiscal_year; --vg_sob_id;
UPDATE fv_facts1_fed_accounts
SET fed_non_fed = vg_fed_nonfed
WHERE account_number = vg_acct_number
AND set_of_books_id = gbl_set_of_books_id
AND fiscal_year = gbl_fiscal_year;
INSERT INTO fv_facts1_fed_accounts
(account_number,
sgl_account_number,
set_of_books_id,
fed_non_fed,
last_run_date,
jc_flag,
fiscal_year
)
VALUES
(vg_acct_number,
vg_sgl_acct_number,
gbl_set_of_books_id,
vg_fed_nonfed,
sysdate,
'N',
gbl_fiscal_year
);