[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT
s.ledger_id,
s.name
FROM gl_period_statuses ps,
gl_ledgers_public_v s,
fa_deprn_periods dp,
fa_book_controls bc,
(SELECT DISTINCT slga.ledger_id
FROM fii_slg_assignments slga,
fii_source_ledger_groups fslg
WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code) fset
WHERE s.ledger_id = fset.ledger_id
AND ps.application_id = 101
AND ps.set_of_books_id = fset.ledger_id
AND ps.end_date >= g_global_Start_Date
AND bc.set_of_books_id = fset.ledger_id
AND dp.book_type_code = bc.book_type_code
AND dp.period_name = ps.period_name
AND nvl(dp.xla_conversion_status, 'UA') <> 'UA';
SELECT DISTINCT
currency_code,
decode( prim_conversion_rate,
-3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
least(sysdate, effective_date)) effective_date
FROM fii_fa_exp_t
WHERE prim_conversion_rate < 0;
SELECT DISTINCT
currency_code,
decode( sec_conversion_rate,
-3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
least(sysdate, effective_date) ) effective_date
FROM fii_fa_exp_t
WHERE sec_conversion_rate < 0;
insert into FII_FA_ACCT_CLASS_CODE_GT
(accounting_class_code, ledger_id)
SELECT XACA.accounting_class_code,
fset.ledger_id
FROM xla_post_acct_progs_b XPAP,
xla_assignment_defns_b XAD,
xla_acct_class_assgns XACA,
(SELECT DISTINCT slga.ledger_id
FROM fii_slg_assignments slga,
fii_source_ledger_groups fslg
WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code) fset
WHERE XPAP.program_owner_code = 'S'
AND XPAP.program_code = 'ASSETS DBI EXPENSES'
AND XPAP.application_id = 450
AND XAD.program_code = XPAP.program_code
AND XAD.enabled_flag = 'Y'
AND XAD.ledger_id = fset.ledger_id
AND XACA.program_code = XAD.program_code
AND XACA.assignment_code = XAD.assignment_code
UNION
SELECT XACA.accounting_class_code,
fset.ledger_id
FROM xla_post_acct_progs_b XPAP,
xla_assignment_defns_b XAD,
xla_acct_class_assgns XACA,
(SELECT DISTINCT slga.ledger_id
FROM fii_slg_assignments slga,
fii_source_ledger_groups fslg
WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code) fset
WHERE XPAP.program_owner_code = 'S'
AND XPAP.program_code = 'ASSETS DBI EXPENSES'
AND XPAP.application_id = 450
AND XAD.program_code = XPAP.program_code
AND XAD.enabled_flag = 'Y'
AND XAD.ledger_id is null
AND XACA.program_code = XAD.program_code
AND XACA.assignment_code = XAD.assignment_code
AND not exists
(select 1
from xla_assignment_defns_b XAD2
where xad2.ledger_id = fset.ledger_id);
FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' rows into FII_FA_ACCT_CLASS_CODE_GT');
select user_conversion_type into g_prim_rate_type_name
from gl_daily_conversion_types
where conversion_type = g_prim_rate_type;
select user_conversion_type into g_sec_rate_type_name
from gl_daily_conversion_types
where conversion_type = g_sec_rate_type;
SELECT DECODE(item_value, 'Y', 'TRUE', 'FALSE')
INTO l_slg_chg
FROM fii_change_log
WHERE log_item = 'FA_RESUMMARIZE';
SELECT 1
INTO l_count1
FROM fii_fa_exp_f
WHERE ROWNUM = 1;
SELECT 1
INTO l_count2
FROM fii_fa_exp_t
WHERE ROWNUM = 1;
UPDATE fii_change_log
SET item_value = 'N',
last_update_date = SYSDATE,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE log_item = 'FA_RESUMMARIZE'
AND item_value = 'Y';
g_phase := 'select min and max dist ids';
SELECT NVL(max(record_id), 0), nvl(min(record_id),1)
INTO l_max_number, l_start_number
FROM FII_FA_NEW_EXP_HDR_IDS;
g_phase := 'Loop to insert into FII_FA_WORKER_JOBS: '
|| l_start_number || ', ' || l_end_number;
INSERT INTO FII_FA_WORKER_JOBS (start_range, end_range, worker_number, status)
VALUES (l_start_number, least(l_end_number, l_max_number), 0, 'UNASSIGNED');
FII_UTIL.put_line('Inserted ' || l_count || ' jobs into FII_FA_WORKER_JOBS table');
SELECT MIN(trx_date), MAX(trx_date), sum(decode(sign(prim_conversion_rate), -1, 1, 0)) +
sum(decode(sign(sec_conversion_rate), -1, 1, 0)), count(*)
INTO l_stg_min, l_stg_max, l_conv_rate_cnt, l_row_cnt
FROM FII_GL_REVENUE_RATES_TEMP;
SELECT MIN(effective_date), MAX(effective_date), sum(decode(sign(prim_conversion_rate), -1, 1, 0)) +
sum(decode(sign(sec_conversion_rate), -1, 1, 0)), count(*)
INTO l_stg_min, l_stg_max, l_conv_rate_cnt, l_row_cnt
FROM FII_GL_JE_SUMMARY_STG;
INSERT INTO fii_fa_exp_hdr_ids (
je_header_id,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by)
SELECT distinct
je_header_id,
sysdate,
g_fii_user_id,
sysdate,
g_fii_login_id,
g_fii_user_id
FROM fii_fa_new_exp_hdr_ids;
FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' rows into FII_FA_EXP_HDR_IDS');
FII_UTIL.put_line('Inserting New Journal header ids');
insert into
fii_fa_new_exp_hdr_ids
(JE_HEADER_ID ,
AE_HEADER_ID ,
EVENT_TYPE_CODE ,
EVENT_ID ,
LEDGER_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN,
RECORD_ID)
select nid.je_header_id,
nid.ae_header_id,
nid.event_type_code,
nid.event_id,
nid.ledger_id,
sysdate,
1,
sysdate,
1,
1,
rownum
from (select distinct
glh.JE_HEADER_ID ,
xlah.ae_Header_id ,
xlah.event_type_code ,
xlah.event_id ,
glh.ledger_id
from fii_gl_processed_header_ids fiiglh,
gl_je_headers glh,
gl_import_references gir,
xla_ae_lines xlal,
xla_ae_headers xlah,
xla_subledgers xlasl,
(SELECT p.period_name,
s.ledger_id
FROM gl_periods p,
gl_ledgers_public_v s
WHERE p.end_date >= g_global_Start_Date
AND p.period_set_name = s.period_set_name) per,
(SELECT DISTINCT slga.ledger_id
FROM fii_slg_assignments slga,
fii_source_ledger_groups fslg
WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code) fset
where xlasl.application_id = 140
and glh.JE_SOURCE = xlasl.je_source_name
and fiiglh.je_header_id = glh.je_header_id
and gir.je_header_id = glh.je_header_id
and gir.gl_sl_link_id = xlal.gl_sl_link_id
and xlal.ae_header_id = xlah.ae_header_id
and xlal.application_id = 140
and xlah.application_id = 140
and glh.period_name = per.period_name
and glh.ledger_id = per.ledger_id
and glh.ledger_id = fset.ledger_id
and glh.ledger_id = xlah.ledger_id
and not exists
(select 1
from fii_fa_exp_hdr_ids faph
where faph.je_header_id = fiiglh.je_header_id)) nid;
FII_UTIL.put_line('Inserted '||l_number_of_rows||
' JE header IDs into FII_FA_NEW_EXP_HDR_IDS for new entries');
insert /*+ append parallel(i) */
into fii_fa_new_exp_hdr_ids i
(JE_HEADER_ID ,
LEDGER_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN)
select /*+ parallel(fiiglh) parallel(glh) parallel(xlash) parallel(per) parallel(fset) */
distinct glh.JE_HEADER_ID ,
glh.ledger_id ,
sysdate,
1,
sysdate,
1,
1
from fii_gl_processed_header_ids fiiglh,
gl_je_headers glh,
xla_subledgers xlasl,
(SELECT p.period_name,
s.ledger_id
FROM gl_periods p,
gl_ledgers_public_v s
WHERE p.end_date >= g_global_Start_Date
AND p.period_set_name = s.period_set_name) per,
(SELECT DISTINCT slga.ledger_id
FROM fii_slg_assignments slga,
fii_source_ledger_groups fslg
WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code) fset
where xlasl.application_id = 140
and glh.JE_SOURCE = xlasl.je_source_name
and fiiglh.je_header_id = glh.je_header_id
and glh.period_name = per.period_name
and glh.ledger_id = per.ledger_id
and glh.ledger_id = fset.ledger_id;
FII_UTIL.put_line('Inserted '||l_number_of_rows||
' JE header IDs into FII_FA_NEW_EXP_HDR_IDS for main processing');
PROCEDURE DELETE_FROM_BASE_SUMMARY (p_start_range IN NUMBER,
p_end_range IN NUMBER) IS
l_count number;
FII_UTIL.put_line ('Calling Delete_From_Base_Summary Procedure');
delete from fii_fa_exp_f
where xla_event_id in
(select ev_dep.event_id
from fii_fa_new_exp_hdr_ids nid,
xla_events ev_rb,
xla_events ev_dep
where nid.record_id between p_start_range and p_end_range
and nid.event_type_code = 'ROLLBACK_DEPRECIATION'
and ev_rb.event_id = nid.event_id
and ev_rb.application_id = 140
and ev_dep.entity_id = ev_rb.entity_id
and ev_dep.application_id = 140
and ev_rb.event_id > ev_dep.event_id);
FII_UTIL.put_line('Deleted '|| l_count ||
' lines from FII_FA_EXP_F for rolled back entries');
Error in Delete_From_Base_Summary Procedure
Message: '||sqlerrm);
END Delete_From_Base_Summary;
FII_UTIL.put_line('CAT_ID Dimension is not up to date, calling CAT_ID Dimension update program');
g_phase := 'Calling CAT_ID Dimension update program';
PROCEDURE INSERT_INTO_RATES IS
l_global_prim_curr_code VARCHAR2(30);
g_phase := 'Inserting into fii_fa_exp_rates_temp';
insert into fii_fa_exp_rates_temp
(FUNCTIONAL_CURRENCY,
TRX_DATE,
PRIM_CONVERSION_RATE,
SEC_CONVERSION_RATE)
select cc functional_currency,
dt trx_date,
decode(cc, l_global_prim_curr_code, 1, FII_CURRENCY.GET_GLOBAL_RATE_PRIMARY (cc,least(sysdate, dt))) PRIM_CONVERSION_RATE,
decode(cc, l_global_sec_curr_code, 1, FII_CURRENCY.GET_GLOBAL_RATE_SECONDARY(cc,least(sysdate, dt))) SEC_CONVERSION_RATE
from (
select distinct
FUNCTIONAL_CURRENCY cc,
account_date dt
from fii_fa_exp_t
);
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_gl_revenue_rates_temp');
Error in Function: Insert_Into_Rates
Phase: ' || g_phase || '
Message: '||sqlerrm);
END INSERT_INTO_RATES;
PROCEDURE INSERT_INTO_SUMMARY (p_start_range IN NUMBER,
p_end_range IN NUMBER) IS
l_stmt VARCHAR2(1000);
g_phase := 'Inserting into fii_fa_exp_f-periodic deprn';
insert into fii_fa_exp_f
(LEDGER_ID ,
ACCOUNT_DATE ,
CURRENCY_CODE ,
CHART_OF_ACCOUNTS_ID ,
COMPANY_ID ,
COST_CENTER_ID ,
NATURAL_ACCOUNT_ID ,
user_dim1_id ,
user_dim2_id ,
ASSET_CAT_FLEX_STRUCTURE_ID ,
asset_CAT_ID ,
asset_cat_MAJOR_ID ,
asset_cat_MAJOR_VALUE ,
asset_cat_MINOR_ID ,
asset_cat_MINOR_VALUE ,
BOOK_TYPE_CODE ,
ASSET_ID ,
ASSET_NUMBER ,
DISTRIBUTION_ID ,
DISTRIBUTION_CCID ,
EXPENSE_CCID ,
SOURCE_CODE ,
DEPRN_TYPE ,
AMOUNT_T ,
AMOUNT_B ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
XLA_EVENT_ID ,
XLA_AE_HEADER_ID
)
select bc.set_of_books_id,
dp.calendar_period_close_date,
sob.currency_code,
bc.accounting_flex_structure,
ccid.company_id,
ccid.cost_center_id,
ccid.natural_account_id,
ccid.user_dim1_id,
ccid.user_dim2_id,
cat.flex_structure_id,
cat.category_id,
cat.major_id,
cat.major_value,
cat.minor_id,
cat.minor_value,
bc.book_type_code,
dh.ASSET_ID,
ad.asset_number,
dh.DISTRIBUTION_ID,
dh.CODE_COMBINATION_ID,
lines.code_combination_id,
'DEPRN',
links.source_distribution_type, --decode to this possibly? was EXPENSE
nvl(lines.accounted_dr, 0) - nvl(lines.accounted_cr, 0),
nvl(lines.accounted_dr, 0) - nvl(lines.accounted_cr, 0),
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id,
nid.event_id,
nid.ae_header_id
from fii_fa_new_exp_hdr_ids nid,
xla_ae_lines lines,
fii_fa_acct_class_code_gt acls,
fii_gl_ccid_dimensions ccid,
gl_ledgers_public_v sob,
gl_import_references gir,
xla_distribution_links links,
fa_deprn_detail dd,
fa_distribution_history dh,
fa_additions_b ad,
fa_asset_history ah,
fii_fa_cat_dimensions cat,
fa_deprn_periods dp,
fa_book_controls bc
where nid.record_id between p_start_range and p_end_range
and nid.event_type_code = 'DEPRECIATION'
and lines.ae_header_id = nid.ae_header_id
and lines.application_id = 140
and acls.accounting_class_code = lines.accounting_class_code
and acls.ledger_id = nid.ledger_id
and gir.je_header_id = nid.je_header_id
and gir.gl_sl_link_id = lines.gl_sl_link_id
and sob.ledger_id = nid.ledger_id
and ccid.code_combination_id = lines.code_combination_id
and links.ae_header_id = lines.ae_header_id
and links.ae_line_num = lines.ae_line_num
and links.application_id = 140
and dd.asset_id = links.Source_distribution_id_num_1
and dd.distribution_id = links.Source_distribution_id_num_5
and dd.deprn_run_id = links.Source_distribution_id_num_3
and dd.book_type_code = links.Source_distribution_id_char_4
and dd.period_counter = links.Source_distribution_id_num_2
and dd.distribution_id = dh.distribution_id
and ad.asset_id = dh.asset_id
and ah.asset_id = dh.asset_id
and ah.date_effective <= dh.date_effective
and nvl(ah.date_ineffective, sysdate + 1) > nvl(dh.date_ineffective, sysdate)
and ah.transaction_header_id_in <= dh.transaction_header_id_in
and nvl(ah.transaction_header_id_out,
nvl(dh.transaction_header_id_out + 1, 1)) >
nvl(dh.transaction_header_id_out, 0)
and cat.category_id = ah.category_id
and dp.book_type_code = dd.book_type_code
and dp.period_counter = dd.period_counter
and bc.book_type_code = dp.book_type_code
and bc.set_of_books_id = sob.ledger_id;
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_fa_exp_f');
g_phase := 'Inserting into fii_fa_exp_f-catchup deprn';
insert into fii_fa_exp_f
(LEDGER_ID ,
ACCOUNT_DATE ,
CURRENCY_CODE ,
CHART_OF_ACCOUNTS_ID ,
COMPANY_ID ,
COST_CENTER_ID ,
NATURAL_ACCOUNT_ID ,
user_dim1_id ,
user_dim2_id ,
ASSET_CAT_FLEX_STRUCTURE_ID ,
asset_CAT_ID ,
asset_cat_MAJOR_ID ,
asset_cat_MAJOR_VALUE ,
asset_cat_MINOR_ID ,
asset_cat_MINOR_VALUE ,
BOOK_TYPE_CODE ,
ASSET_ID ,
ASSET_NUMBER ,
DISTRIBUTION_ID ,
DISTRIBUTION_CCID ,
EXPENSE_CCID ,
SOURCE_CODE ,
DEPRN_TYPE ,
AMOUNT_T ,
AMOUNT_B ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
XLA_EVENT_ID ,
XLA_AE_HEADER_ID
)
select bc.set_of_books_id,
dp.calendar_period_close_date,
sob.currency_code,
bc.accounting_flex_structure,
ccid.company_id,
ccid.cost_center_id,
ccid.natural_account_id,
ccid.user_dim1_id,
ccid.user_dim2_id,
cat.flex_structure_id,
cat.category_id,
cat.major_id,
cat.major_value,
cat.minor_id,
cat.minor_value,
bc.book_type_code,
dh.ASSET_ID,
ad.asset_number,
dh.DISTRIBUTION_ID,
dh.CODE_COMBINATION_ID,
lines.code_combination_id,
'TRX',
adj.adjustment_type,
sum(nvl(lines.accounted_dr, 0)) - sum(nvl(lines.accounted_cr, 0)),
sum(nvl(lines.accounted_dr, 0)) - sum(nvl(lines.accounted_cr, 0)),
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id,
nid.event_id,
nid.ae_header_id
from fii_fa_new_exp_hdr_ids nid,
xla_ae_lines lines,
fii_fa_acct_class_code_gt acls,
fii_gl_ccid_dimensions ccid,
gl_ledgers_public_v sob,
gl_import_references gir,
xla_distribution_links links,
fa_adjustments adj,
fa_distribution_history dh,
fa_additions_b ad,
fa_asset_history ah,
fii_fa_cat_dimensions cat,
fa_deprn_periods dp,
fa_book_controls bc
where nid.record_id between p_start_range and p_end_range
and nid.event_type_code not in ('DEPRECIATION', 'ROLLBACK_DEPRECIATION')
and lines.ae_header_id = nid.ae_header_id
and lines.application_id = 140
and acls.accounting_class_code = lines.accounting_class_code
and acls.ledger_id = nid.ledger_id
and gir.je_header_id = nid.je_header_id
and gir.gl_sl_link_id = lines.gl_sl_link_id
and ccid.code_combination_id = lines.code_combination_id
and sob.ledger_id = nid.ledger_id
and links.ae_header_id = lines.ae_header_id
and links.ae_line_num = lines.ae_line_num
and links.application_id = 140
and links.source_distribution_type = 'TRX'
and adj.transaction_header_id = links.Source_distribution_id_num_1
and adj.adjustment_line_id = links.Source_distribution_id_num_2
and dh.asset_id = ah.asset_id
and ah.date_effective <= dh.date_effective
and nvl(ah.date_ineffective, sysdate + 1) > nvl(dh.date_ineffective, sysdate)
and ah.transaction_header_id_in <= dh.transaction_header_id_in
and nvl(ah.transaction_header_id_out,
nvl(dh.transaction_header_id_out + 1, 1)) >
nvl(dh.transaction_header_id_out, 0)
and dh.asset_id = ad.asset_id
and ah.category_id = cat.category_id
and dh.asset_id = adj.asset_id
and dp.book_type_code = adj.book_type_code
and dp.period_counter = adj.period_counter_created
and dh.distribution_id = adj.distribution_id
and nvl(adj.track_member_flag,'N') = 'N'
and adj.adjustment_type in ('EXPENSE', 'BONUS EXPENSE')
and bc.book_type_code = dp.book_type_code
and bc.set_of_books_id = sob.ledger_id
group by bc.set_of_books_id,
dp.calendar_period_close_date,
NULL,
sob.currency_code,
bc.accounting_flex_structure,
ccid.company_id,
ccid.cost_center_id,
ccid.natural_account_id,
ccid.user_dim1_id,
ccid.user_dim2_id,
cat.flex_structure_id,
cat.category_id,
cat.major_id,
cat.major_value,
cat.minor_id,
cat.minor_value,
bc.book_type_code,
dh.ASSET_ID,
ad.asset_number,
dh.DISTRIBUTION_ID,
dh.CODE_COMBINATION_ID,
lines.code_combination_id,
'TRX',
adj.adjustment_type,
sysdate,
g_fii_user_id,
g_fii_login_id,
nid.event_id,
nid.ae_header_id;
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_fa_exp_f');
Error in Function: INSERT_INTO_SUMMARY
Phase: ' || g_phase || '
Message: '||sqlerrm);
END INSERT_INTO_SUMMARY;
PROCEDURE INSERT_INTO_SUMMARY_PAR IS
l_stmt VARCHAR2(1000);
insert
into fii_fa_exp_f bsum
(LEDGER_ID ,
ACCOUNT_DATE ,
CURRENCY_CODE ,
CHART_OF_ACCOUNTS_ID ,
COMPANY_ID ,
COST_CENTER_ID ,
NATURAL_ACCOUNT_ID ,
user_dim1_id ,
user_dim2_id ,
ASSET_CAT_FLEX_STRUCTURE_ID ,
asset_CAT_ID ,
asset_cat_MAJOR_ID ,
asset_cat_MAJOR_VALUE ,
asset_cat_MINOR_ID ,
asset_cat_MINOR_VALUE ,
BOOK_TYPE_CODE ,
ASSET_ID ,
ASSET_NUMBER ,
DISTRIBUTION_ID ,
DISTRIBUTION_CCID ,
EXPENSE_CCID ,
SOURCE_CODE ,
DEPRN_TYPE ,
AMOUNT_T ,
AMOUNT_B ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
XLA_EVENT_ID ,
XLA_AE_HEADER_ID
)
select bc.set_of_books_id,
dp.calendar_period_close_date,
sob.currency_code,
bc.accounting_flex_structure,
ccid.company_id,
ccid.cost_center_id,
ccid.natural_account_id,
ccid.user_dim1_id,
ccid.user_dim2_id,
cat.flex_structure_id,
cat.category_id,
cat.major_id,
cat.major_value,
cat.minor_id,
cat.minor_value,
bc.book_type_code,
dh.ASSET_ID,
ad.asset_number,
dh.DISTRIBUTION_ID,
dh.CODE_COMBINATION_ID,
lines.code_combination_id,
'TRX',
adj.adjustment_type,
sum(nvl(lines.accounted_dr, 0)) - sum(nvl(lines.accounted_cr, 0)),
sum(nvl(lines.accounted_dr, 0)) - sum(nvl(lines.accounted_cr, 0)),
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id,
headers.event_id,
headers.ae_header_id
from fii_fa_new_exp_hdr_ids nid,
gl_import_references gir,
fii_fa_acct_class_code_gt acls,
xla_ae_lines lines,
xla_ae_headers headers,
fii_gl_ccid_dimensions ccid,
gl_ledgers_public_v sob,
xla_distribution_links links,
fa_adjustments adj,
fa_distribution_history dh,
fa_additions_b ad,
fa_asset_history ah,
fii_fa_cat_dimensions cat,
fa_deprn_periods dp,
fa_book_controls bc
where gir.je_header_id = nid.je_header_id
and acls.ledger_id = nid.ledger_id
and lines.application_id = 140
and lines.gl_sl_link_id = gir.gl_sl_link_id
and lines.accounting_class_code = acls.accounting_class_code
and headers.application_id = 140
and headers.ae_header_id = lines.ae_header_id
and headers.ledger_id = nid.ledger_id
and headers.event_type_code not in ('DEPRECIATION', 'ROLLBACK_DEPRECIATION', 'DEFERRED_DEPRECIATION')
and sob.ledger_id = nid.ledger_id
and ccid.code_combination_id = lines.code_combination_id
and links.application_id = 140
and links.source_distribution_type = 'TRX'
and links.ae_header_id = lines.ae_header_id
and links.ae_line_num = lines.ae_line_num
and adj.transaction_header_id = links.Source_distribution_id_num_1
and adj.adjustment_line_id = links.Source_distribution_id_num_2
and dh.asset_id = ah.asset_id
and ah.date_effective <= dh.date_effective
and nvl(ah.date_ineffective, sysdate + 1) > nvl(dh.date_ineffective, sysdate)
and ah.transaction_header_id_in <= dh.transaction_header_id_in
and nvl(ah.transaction_header_id_out,
nvl(dh.transaction_header_id_out + 1, 1)) >
nvl(dh.transaction_header_id_out, 0)
and dh.asset_id = ad.asset_id
and ah.category_id = cat.category_id
and dh.asset_id = adj.asset_id
and dp.book_type_code = adj.book_type_code
and dp.period_counter = adj.period_counter_created
and dh.distribution_id = adj.distribution_id
and nvl(adj.track_member_flag,'N') = 'N'
and adj.adjustment_type in ('EXPENSE', 'BONUS EXPENSE')
and dp.book_type_code = bc.book_type_code
and bc.set_of_books_id = sob.ledger_id
group by bc.set_of_books_id,
dp.calendar_period_close_date,
NULL,
sob.currency_code,
bc.accounting_flex_structure,
ccid.company_id,
ccid.cost_center_id,
ccid.natural_account_id,
ccid.user_dim1_id,
ccid.user_dim2_id,
cat.flex_structure_id,
cat.category_id,
cat.major_id,
cat.major_value,
cat.minor_id,
cat.minor_value,
bc.book_type_code,
dh.ASSET_ID,
ad.asset_number,
dh.DISTRIBUTION_ID,
dh.CODE_COMBINATION_ID,
lines.code_combination_id,
'TRX',
adj.adjustment_type,
sysdate,
g_fii_user_id,
g_fii_login_id,
headers.event_id,
headers.ae_header_id;
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_fa_exp_f');
insert
into fii_fa_exp_f bsum
(LEDGER_ID ,
ACCOUNT_DATE ,
CURRENCY_CODE ,
CHART_OF_ACCOUNTS_ID ,
COMPANY_ID ,
COST_CENTER_ID ,
NATURAL_ACCOUNT_ID ,
user_dim1_id ,
user_dim2_id ,
ASSET_CAT_FLEX_STRUCTURE_ID ,
asset_CAT_ID ,
asset_cat_MAJOR_ID ,
asset_cat_MAJOR_VALUE ,
asset_cat_MINOR_ID ,
asset_cat_MINOR_VALUE ,
BOOK_TYPE_CODE ,
ASSET_ID ,
ASSET_NUMBER ,
DISTRIBUTION_ID ,
DISTRIBUTION_CCID ,
EXPENSE_CCID ,
SOURCE_CODE ,
DEPRN_TYPE ,
AMOUNT_T ,
AMOUNT_B ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
XLA_EVENT_ID ,
XLA_AE_HEADER_ID
)
select bc.set_of_books_id,
dp.calendar_period_close_date,
sob.currency_code,
bc.accounting_flex_structure,
ccid.company_id,
ccid.cost_center_id,
ccid.natural_account_id,
ccid.user_dim1_id,
ccid.user_dim2_id,
cat.flex_structure_id,
cat.category_id,
cat.major_id,
cat.major_value,
cat.minor_id,
cat.minor_value,
bc.book_type_code,
dh.ASSET_ID,
ad.asset_number,
dh.DISTRIBUTION_ID,
dh.CODE_COMBINATION_ID,
lines.code_combination_id,
'DEPRN',
links.source_distribution_type, --decode to this possibly? was EXPENSE
nvl(lines.accounted_dr, 0) - nvl(lines.accounted_cr, 0),
nvl(lines.accounted_dr, 0) - nvl(lines.accounted_cr, 0),
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id,
headers.event_id,
headers.ae_header_id
from fii_fa_new_exp_hdr_ids nid,
gl_import_references gir,
fii_fa_acct_class_code_gt acls,
xla_ae_lines lines,
xla_ae_headers headers,
fii_gl_ccid_dimensions ccid,
gl_ledgers_public_v sob,
xla_distribution_links links,
fa_deprn_detail dd,
fa_distribution_history dh,
fa_additions_b ad,
fa_asset_history ah,
fii_fa_cat_dimensions cat,
fa_deprn_periods dp,
fa_book_controls bc
where gir.je_header_id = nid.je_header_id
and acls.ledger_id = nid.ledger_id
and lines.application_id = 140
and lines.gl_sl_link_id = gir.gl_sl_link_id
and lines.accounting_class_code = acls.accounting_class_code
and headers.application_id = 140
and headers.ae_header_id = lines.ae_header_id
and headers.event_type_code = 'DEPRECIATION'
and ccid.code_combination_id = lines.code_combination_id
and sob.ledger_id = nid.ledger_id
and links.application_id = 140
and links.ae_header_id = lines.ae_header_id
and links.ae_line_num = lines.ae_line_num
and dd.asset_id = links.Source_distribution_id_num_1
and dd.distribution_id = links.Source_distribution_id_num_5
and dd.deprn_run_id = links.Source_distribution_id_num_3
and dd.book_type_code = links.Source_distribution_id_char_4
and dd.period_counter = links.Source_distribution_id_num_2
and dd.distribution_id = dh.distribution_id
and ad.asset_id = dh.asset_id
and ah.asset_id = dh.asset_id
and ah.date_effective <= dh.date_effective
and nvl(ah.date_ineffective, sysdate + 1) > nvl(dh.date_ineffective, sysdate)
and ah.transaction_header_id_in <= dh.transaction_header_id_in
and nvl(ah.transaction_header_id_out,
nvl(dh.transaction_header_id_out + 1, 1)) >
nvl(dh.transaction_header_id_out, 0)
and cat.category_id = ah.category_id
and dp.book_type_code = dd.book_type_code
and dp.period_counter = dd.period_counter
and bc.book_type_code = dp.book_type_code
and bc.set_of_books_id = sob.ledger_id
and headers.ae_header_id not in
(select /*+ hash_aj parallel(headers2, ev_rb, ev_dep) */
headers2.ae_header_id
from xla_ae_headers headers2,
xla_events ev_rb,
xla_events ev_dep
where headers2.application_id = 140
and headers2.event_type_code = 'DEPRECIATION'
and ev_dep.event_id = headers2.event_id
and ev_dep.application_id = 140
and ev_rb.entity_id = ev_dep.entity_id
and ev_rb.application_id = 140
and ev_rb.event_id > ev_dep.event_id);
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_fa_exp_f');
Error in Function: Insert_Into_Summary_Par
Phase: ' || g_phase || '
Message: '||sqlerrm);
END INSERT_INTO_SUMMARY_PAR;
g_phase := 'Update fii_change_log if we are running in Inital Load';
UPDATE fii_change_log
SET item_value = 'N',
last_update_date = SYSDATE,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE log_item = 'FA_RESUMMARIZE'
AND item_value = 'Y';
SELECT NVL(sum(decode(status,'UNASSIGNED',1,0)),0),
NVL(sum(decode(status,'COMPLETED',1,0)),0),
NVL(sum(decode(status,'IN PROCESS',1,0)),0),
NVL(sum(decode(status,'FAILED',1,0)),0),
count(*)
INTO l_unassigned_cnt,
l_completed_cnt,
l_wip_cnt,
l_failed_cnt,
l_tot_cnt
FROM FII_FA_WORKER_JOBS;
SELECT NVL(sum(decode(status,'UNASSIGNED', 1, 0)),0),
NVL(sum(decode(status,'FAILED', 1, 0)),0),
NVL(sum(decode(status,'IN PROCESS', 1, 0)),0),
NVL(sum(decode(status,'COMPLETED',1 , 0)),0),
count(*)
INTO l_unassigned_cnt,
l_failed_cnt,
l_wip_cnt,
l_completed_cnt,
l_total_cnt
FROM FII_FA_WORKER_JOBS;
UPDATE FII_FA_WORKER_JOBS
SET status = 'IN PROCESS',
worker_number = g_worker_num
WHERE status = 'UNASSIGNED'
AND rownum < 2;
SELECT start_range,
end_range
INTO l_start_range,
l_end_range
FROM FII_FA_WORKER_JOBS
WHERE worker_number = g_worker_num
AND status = 'IN PROCESS';
g_phase := 'Inserting into summary table';
INSERT_INTO_SUMMARY(l_start_range,
l_end_range);
DELETE_FROM_BASE_SUMMARY(l_start_range,
l_end_range);
UPDATE FII_FA_WORKER_JOBS
SET status = 'COMPLETED'
WHERE status = 'IN PROCESS'
AND worker_number = g_worker_num;
UPDATE FII_FA_WORKER_JOBS
SET status = 'FAILED'
WHERE worker_number = g_worker_num
AND status = 'IN PROCESS';
g_phase := 'Inserting into staging table';
INSERT_INTO_SUMMARY_PAR;
g_phase := 'Inserting processed JE Header IDs';