[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT
functional_currency,
decode( prim_conversion_rate,
-3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
least(sysdate, effective_date)) effective_date
FROM fii_gl_je_summary_stg
WHERE prim_conversion_rate < 0;
SELECT DISTINCT
functional_currency,
decode( sec_conversion_rate,
-3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
least(sysdate, effective_date) ) effective_date
FROM fii_gl_je_summary_stg
WHERE sec_conversion_rate < 0;
SELECT DISTINCT
functional_currency,
CASE WHEN prim_conversion_rate < 0 THEN
decode( prim_conversion_rate,
-3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
least(sysdate, effective_date))
ELSE NULL END prim_effective_date,
CASE WHEN sec_conversion_rate < 0 THEN
decode( sec_conversion_rate,
-3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
least(sysdate, effective_date))
ELSE NULL END sec_effective_date
FROM fii_gl_je_summary_stg
WHERE prim_conversion_rate < 0
OR sec_conversion_rate < 0;
SELECT DISTINCT
functional_currency,
decode( prim_conversion_rate,
-3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
least(sysdate, trx_date) ) trx_date
FROM fii_gl_revenue_rates_temp
WHERE prim_conversion_rate < 0;
SELECT DISTINCT
functional_currency,
decode( sec_conversion_rate,
-3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
least(sysdate, trx_date) ) trx_date
FROM fii_gl_revenue_rates_temp
WHERE sec_conversion_rate < 0;
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 = 'GL_RESUMMARIZE';
SELECT 1
INTO l_count1
FROM fii_gl_je_summary_b
WHERE ROWNUM = 1;
SELECT 1
INTO l_count2
FROM fii_gl_je_summary_stg
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 = 'GL_RESUMMARIZE'
AND item_value = 'Y';
SELECT DECODE(item_value, 'Y', 'TRUE', 'FALSE')
INTO l_prd_chg
FROM fii_change_log
WHERE log_item = 'GL_PROD_CHANGE';
SELECT 1
INTO l_count1
FROM fii_gl_je_summary_b
WHERE ROWNUM = 1;
SELECT 1
INTO l_count2
FROM fii_gl_je_summary_stg
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 = 'GL_PROD_CHANGE'
AND item_value = 'Y';
g_phase := 'select min and max sequence IDs from the ID Temp table';
SELECT NVL(max(record_id), 0), nvl(min(record_id),1)
INTO l_max_number, l_start_number
FROM FII_GL_NEW_JRL_HEADER_IDS;
g_phase := 'Loop to insert into FII_GL_WORKER_JOBS: '
|| l_start_number || ', ' || l_end_number;
INSERT INTO FII_GL_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_GL_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;
g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table for week level';
FII_UTIL.put_line('Inserting weekly aggregated data into FII_GL_JE_SUMMARY_STG table');
INSERT INTO fii_gl_je_summary_stg
(
week,
cost_center_id,
fin_category_id,
company_id,
prod_category_id,
user_dim1_id,
user_dim2_id,
je_source,
je_category,
effective_date,
ledger_id,
chart_of_accounts_id,
functional_currency,
amount_b,
prim_amount_g,
sec_amount_g,
committed_amount_b,
committed_amount_prim,
obligated_amount_b,
obligated_amount_prim,
other_amount_b,
other_amount_prim,
posted_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
SELECT
fday.week_id,
stg.cost_center_id,
stg.fin_category_id,
stg.company_id,
stg.prod_category_id,
stg.user_dim1_id,
stg.user_dim2_id,
stg.je_source,
stg.je_category,
MAX(stg.effective_date),
stg.ledger_id,
stg.chart_of_accounts_id,
stg.functional_currency,
SUM(stg.amount_b) amount_b,
SUM(stg.prim_amount_g) prim_amount_g,
SUM(stg.sec_amount_g) sec_amount_g,
SUM(stg.committed_amount_b) committed_amount_b,
SUM(stg.committed_amount_prim) committed_amount_prim,
SUM(stg.obligated_amount_b) obligated_amount_b,
SUM(stg.obligated_amount_prim) obligated_amount_prim,
SUM(stg.other_amount_b) other_amount_b,
SUM(stg.other_amount_prim) other_amount_prim,
stg.posted_date,
stg.last_update_date,
stg.last_updated_by,
stg.creation_date,
stg.created_by,
stg.last_update_login
FROM fii_gl_je_summary_stg stg,
fii_time_day fday
WHERE stg.day = fday.report_date_julian
GROUP BY
stg.cost_center_id,
stg.fin_category_id,
stg.company_id,
stg.prod_category_id,
stg.user_dim1_id,
stg.user_dim2_id,
stg.je_source,
stg.je_category,
stg.ledger_id,
stg.chart_of_accounts_id,
stg.functional_currency,
stg.last_update_date,
stg.last_updated_by,
stg.creation_date,
stg.created_by,
stg.last_update_login,
fday.week_id,
stg.posted_date;
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');
g_phase := 'Update global amount in FII_GL_JE_SUMMARY_STG table';
Update FII_GL_JE_SUMMARY_STG stg
SET stg.prim_amount_g = round((stg.amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau,
stg.sec_amount_g = round((stg.amount_b * sec_conversion_rate)/g_secondary_mau)*g_secondary_mau,
stg.committed_amount_prim = round((stg.committed_amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau,
stg.obligated_amount_prim = round((stg.obligated_amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau,
stg.other_amount_prim = round((stg.other_amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau;
FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records in FII_GL_JE_SUMMARY_STG');
g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table';
FII_UTIL.put_line('Inserting aggregated data into FII_GL_JE_SUMMARY_STG table');
INSERT INTO fii_gl_je_summary_stg
(year,
quarter,
period,
day,
cost_center_id,
fin_category_id,
company_id,
prod_category_id,
user_dim1_id,
user_dim2_id,
je_source,
je_category,
effective_date,
ledger_id,
chart_of_accounts_id,
functional_currency,
amount_b,
prim_amount_g,
sec_amount_g,
committed_amount_b,
committed_amount_prim,
obligated_amount_b,
obligated_amount_prim,
other_amount_b,
other_amount_prim,
posted_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
SELECT fday.ent_year_id,
fday.ent_qtr_id,
fday.ent_period_id,
TO_NUMBER(NULL),
stg.cost_center_id,
stg.fin_category_id,
stg.company_id,
stg.prod_category_id,
stg.user_dim1_id,
stg.user_dim2_id,
stg.je_source,
stg.je_category,
MAX(stg.effective_date),
stg.ledger_id,
stg.chart_of_accounts_id,
stg.functional_currency,
SUM(stg.amount_b) amount_b,
SUM(stg.prim_amount_g) prim_amount_g,
SUM(stg.sec_amount_g) sec_amount_g,
SUM(committed_amount_b) committed_amount_b,
SUM(committed_amount_prim) committed_amount_prim,
SUM(obligated_amount_b) obligated_amount_b,
SUM(obligated_amount_prim) obligated_amount_prim,
SUM(other_amount_b) other_amount_b,
SUM(other_amount_prim) other_amount_prim,
stg.posted_date,
stg.last_update_date,
stg.last_updated_by,
stg.creation_date,
stg.created_by,
stg.last_update_login
FROM fii_gl_je_summary_stg stg,
fii_time_day fday
WHERE stg.day = fday.report_date_julian
GROUP BY
stg.cost_center_id,
stg.fin_category_id,
stg.company_id,
stg.prod_category_id,
stg.user_dim1_id,
stg.user_dim2_id,
stg.je_source,
stg.je_category,
stg.ledger_id,
stg.chart_of_accounts_id,
stg.functional_currency,
stg.posted_date,
stg.last_update_date,
stg.last_updated_by,
stg.creation_date,
stg.created_by,
stg.last_update_login,
ROLLUP (fday.ent_year_id,
fday.ent_qtr_id,
fday.ent_period_id);
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT ||
' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');
(SELECT NVL(day, NVL(week, NVL(period, NVL(quarter, year)))) TIME_ID,
DECODE(day, null,
DECODE(week, null,
DECODE(period, null,
DECODE(quarter, null, 128, 64), 32), 16), 1)
PERIOD_TYPE_ID,
COST_CENTER_ID,
PROD_CATEGORY_ID,
USER_DIM1_ID,
USER_DIM2_ID,
FIN_CATEGORY_ID,
COMPANY_ID,
JE_SOURCE, JE_CATEGORY, LEDGER_ID,
CHART_OF_ACCOUNTS_ID,
FUNCTIONAL_CURRENCY,
SUM(AMOUNT_B) AMOUNT_B,
SUM(PRIM_AMOUNT_G) PRIM_AMOUNT_G,
SUM(SEC_AMOUNT_G) SEC_AMOUNT_G,
SUM(COMMITTED_AMOUNT_B) COMMITTED_AMOUNT_B,
SUM(COMMITTED_AMOUNT_PRIM) COMMITTED_AMOUNT_PRIM,
SUM(OBLIGATED_AMOUNT_B) OBLIGATED_AMOUNT_B,
SUM(OBLIGATED_AMOUNT_PRIM) OBLIGATED_AMOUNT_PRIM,
SUM(OTHER_AMOUNT_B) OTHER_AMOUNT_B,
SUM(OTHER_AMOUNT_PRIM) OTHER_AMOUNT_PRIM,
POSTED_DATE
FROM fii_gl_je_summary_stg
WHERE year IS NOT NULL
OR week IS NOT NULL
GROUP BY
NVL(day, NVL(week, NVL(period, NVL(quarter, year)))),
DECODE(day, null,
DECODE(week, null,
DECODE(period, null,
DECODE(quarter, null, 128, 64), 32), 16), 1),
COST_CENTER_ID,
PROD_CATEGORY_ID,
USER_DIM1_ID,
USER_DIM2_ID,
FIN_CATEGORY_ID,
COMPANY_ID,
JE_SOURCE, JE_CATEGORY, LEDGER_ID,
CHART_OF_ACCOUNTS_ID,
FUNCTIONAL_CURRENCY,
POSTED_DATE) s
ON (bsum.time_id = s.time_id AND
bsum.period_type_id = s.period_type_id AND
bsum.cost_center_id = s.cost_center_id AND
bsum.fin_category_id = s.fin_category_id AND
bsum.je_source = s.je_source AND
bsum.je_category = s.je_category AND
bsum.ledger_id = s.ledger_id AND
bsum.chart_of_accounts_id = s.chart_of_accounts_id AND
bsum.functional_currency = s.functional_currency AND
bsum.company_id = s.company_id AND
bsum.prod_category_id = s.prod_category_id AND
bsum.user_dim1_id = s.user_dim1_id AND
bsum.user_dim2_id = s.user_dim2_id AND
NVL(bsum.posted_date, g_global_start_date) = NVL(s.posted_date, g_global_start_date))
WHEN MATCHED THEN
UPDATE SET bsum.amount_b = bsum.amount_b+ s.amount_b,
bsum.prim_amount_g = bsum.prim_amount_g + s.prim_amount_g,
bsum.sec_amount_g = bsum.sec_amount_g + s.sec_amount_g,
bsum.committed_amount_b = bsum.committed_amount_b+ s.committed_amount_b,
bsum.committed_amount_prim = bsum.committed_amount_prim + s.committed_amount_prim,
bsum.obligated_amount_b = bsum.obligated_amount_b+ s.obligated_amount_b,
bsum.obligated_amount_prim = bsum.obligated_amount_prim + s.obligated_amount_prim,
bsum.other_amount_b = bsum.other_amount_b+ s.other_amount_b,
bsum.other_amount_prim = bsum.other_amount_prim + s.other_amount_prim,
bsum.last_update_date = sysdate,
bsum.last_update_login = g_fii_login_id,
bsum.last_updated_by = g_fii_user_id
WHEN NOT MATCHED THEN INSERT (bsum.time_id,
bsum.period_type_id,
bsum.company_id,
bsum.cost_center_id,
bsum.fin_category_id,
bsum.prod_category_id,
bsum.user_dim1_id,
bsum.user_dim2_id,
bsum.je_source,
bsum.je_category,
bsum.ledger_id,
bsum.chart_of_accounts_id,
bsum.functional_currency,
bsum.amount_B,
bsum.prim_amount_G,
bsum.sec_amount_G,
bsum.committed_amount_b,
bsum.committed_amount_prim,
bsum.obligated_amount_b,
bsum.obligated_amount_prim,
bsum.other_amount_b,
bsum.other_amount_prim,
bsum.posted_date,
bsum.creation_date,
bsum.created_by,
bsum.last_update_date,
bsum.last_update_login,
bsum.last_updated_by)
values (s.time_id,
s.period_type_id,
s.company_id,
s.cost_center_id,
s.fin_category_id,
s.prod_category_id,
s.user_dim1_id,
s.user_dim2_id,
s.je_source,
s.je_category,
s.ledger_id,
s.chart_of_accounts_id,
s.functional_currency,
s.amount_B,
s.prim_amount_G,
s.sec_amount_G,
s.committed_amount_b,
s.committed_amount_prim,
s.obligated_amount_b,
s.obligated_amount_prim,
s.other_amount_b,
s.other_amount_prim,
s.posted_date,
sysdate,
g_fii_user_id,
sysdate,
g_fii_login_id,
g_fii_user_id);
INSERT INTO fii_gl_processed_header_ids (
je_header_id,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by)
SELECT je_header_id,
sysdate,
g_fii_user_id,
sysdate,
g_fii_login_id,
g_fii_user_id
FROM fii_gl_new_jrl_header_ids;
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' rows into FII_GL_PROCESSED_HEADER_IDS');
FII_UTIL.put_line('Inserting New Journal header ids');
INSERT /*+ append */ INTO fii_gl_new_jrl_header_ids
(record_id,
je_header_id,
currency_code,
je_source,
je_category,
encumbrance_type,
actual_flag,
posted_date)
SELECT /*+ use_hash(per, jeh, fset,fgph) parallel(jeh) parallel(fgph) */
rownum,
jeh.je_header_id,
jeh.currency_code,
jeh.je_source,
jeh.je_category,
decode(g_industry, 'G', NVL(etype.encumbrance_type, 'OTHERS'),
'C', NULL) encumbrance_type,
jeh.actual_flag,
decode(g_industry,
'G', decode(jeh.actual_flag, --for Government
'A', g_global_start_date, -- for actuals
per2.start_date), --jeh.posted_date), -- for encumbrances
null) -- for Commercial
FROM (
SELECT p.period_name, s.ledger_id
FROM gl_periods p, gl_ledgers_public_v s
WHERE p.start_date <= NVL(P_End_Date, start_date)
AND p.end_date >= P_Start_Date
AND p.period_set_name = s.period_set_name) per,
(SELECT DISTINCT
slga.ledger_id,
DECODE(slga.je_rule_set_id, NULL, '-1', rule.JE_SOURCE_NAME) je_source_name,
DECODE(slga.je_rule_set_id, NULL, '-1', rule.JE_CATEGORY_NAME) je_category_name
FROM fii_slg_assignments slga,
gl_je_inclusion_rules rule,
fii_source_ledger_groups fslg
WHERE slga.je_rule_set_id = rule.je_rule_set_id (+)
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code) fset,
gl_je_headers jeh,
fii_encum_type_mappings etype,
fii_gl_processed_header_ids fgph,
gl_periods per2,
gl_ledgers_public_v s2
WHERE jeh.ledger_id = fset.ledger_id
AND jeh.encumbrance_type_id = etype.encumbrance_type_id (+)
AND (jeh.je_source = fset.je_source_name OR fset.je_source_name = '-1')
-- Bug 5026804: Exclude the journal source - Closing Journal
AND jeh.je_source <> 'Closing Journal'
AND (jeh.je_category = fset.je_category_name OR fset.je_category_name = '-1')
AND jeh.currency_code <> 'STAT'
AND jeh.period_name = per.period_name
AND jeh.ledger_id = per.ledger_id
AND jeh.je_header_id = fgph.je_header_id(+)
AND fgph.je_header_id IS NULL
AND jeh.status = 'P'
AND decode (jeh.actual_flag,
'A',1,
'E',1,
0) = 1
AND jeh.ledger_id = s2.ledger_id
AND s2.period_set_name = per2.period_set_name
AND trunc(jeh.posted_date) between per2.start_date and per2.end_date
AND per2.period_type = s2.accounted_period_type
AND per2.adjustment_period_flag = 'N' ;
FII_UTIL.put_line('Inserted '||l_number_of_rows||
' JE header IDs into FII_GL_NEW_JRL_HEADER_IDS');
l_stmt:= 'INSERT INTO FII_GL_JE_SUMMARY_STG
(day,
week,
period,
quarter,
year,
company_id,
cost_center_id,
fin_category_id,
prod_category_id,
user_dim1_id,
user_dim2_id,
je_source,
je_category,
ledger_id,
effective_date,
chart_of_accounts_id,
functional_currency,
amount_b,
committed_amount_b,
obligated_amount_b,
other_amount_b,
prim_conversion_rate,
sec_conversion_rate,
posted_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
SELECT /*+ ORDERED USE_NL(njhi line sob fin) */
to_number(to_char(line.effective_date,''J'')) ,
to_number(NULL, 999),
to_number(NULL, 999) ,
to_number(NULL, 999) ,
999, -- Insert 999 for year field so this record is merged into summary
fin.company_id,
fin.cost_center_id,
fin.natural_account_id,
NVL(fin.prod_category_id, -1),
fin.user_dim1_id,
fin.user_dim2_id,
njhi.je_source ,
njhi.je_category ,
sob.ledger_id,
line.effective_date,
sob.chart_of_accounts_id,
sob.currency_code,
decode(njhi.actual_flag,
''A'', sum(NVL(line.accounted_cr, 0) - NVL(line.accounted_dr, 0)),
0),
decode(njhi.actual_flag,
''E'', decode(njhi.encumbrance_type,
''COMMITMENT'', sum(NVL(line.accounted_cr, 0) - NVL(line.accounted_dr, 0)),
0),
0), -- For encumbrances: requisitions (committed_amount)
decode(njhi.actual_flag,
''E'', decode(njhi.encumbrance_type,
''OBLIGATION'', sum(NVL(line.accounted_cr, 0) - NVL(line.accounted_dr, 0)),
0),
0), -- For encumbrances: purchase orders (obligated_amount)
decode(njhi.actual_flag,
''E'', decode(njhi.encumbrance_type,
''OTHERS'', sum(NVL(line.accounted_cr, 0) - NVL(line.accounted_dr, 0)),
0),
0), -- For encumbrances: others (other_amount)
fii_currency.get_global_rate_primary(sob.currency_code, least(sysdate, line.effective_date)),
fii_currency.get_global_rate_secondary(sob.currency_code, least(sysdate, line.effective_date)),
decode('''||g_industry||''',
''G'', decode(njhi.actual_flag, --for Government
''A'', null, -- for actuals
njhi.posted_date), -- for encumbrances
null), -- for Commercial
sysdate, ' ||
g_fii_user_id || ',
sysdate, ' ||
g_fii_user_id || ',' ||
g_fii_login_id || '
FROM fii_gl_new_jrl_header_ids njhi,
gl_je_lines line,
gl_ledgers_public_v sob,
fii_gl_ccid_dimensions fin,
fii_slg_assignments slga,
fii_source_ledger_groups fslg
WHERE njhi.je_header_id = line.je_header_id
AND line.ledger_id = sob.ledger_id
AND line.code_combination_id = fin.code_combination_id
AND ( fin.company_id = slga.bal_seg_value_id OR slga.bal_seg_value_id = -1 )
AND fin.chart_of_accounts_id = slga.chart_of_accounts_id
AND line.ledger_id = slga.ledger_id
AND njhi.record_id >= '|| p_start_range || '
AND njhi.record_id <= ' || p_end_range || '
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = ''' || g_usage_code || '''
GROUP BY line.effective_date,
fin.company_id,
fin.cost_center_id,
fin.natural_account_id,
NVL(fin.prod_category_id, -1),
fin.user_dim1_id,
fin.user_dim2_id,
njhi.je_source,
njhi.je_category,
sob.ledger_id,
sob.chart_of_accounts_id,
sob.currency_code,
njhi.encumbrance_type,
njhi.actual_flag,
decode('''||g_industry||''',
''G'', decode(njhi.actual_flag, --for Government
''A'', null, -- for actuals
njhi.posted_date), -- for encumbrances
null)'; -- for Commercial
FII_UTIL.put_line('Inserted '||l_number_of_rows||' into table FII_GL_JE_SUMMARY_STG with day level data');
FII_UTIL.put_line('CCID Dimension is not up to date, calling CCID Dimension update program');
g_phase := 'Calling CCID Dimension update program';
SELECT a.lookup_code,
decode(a.lookup_type, 'FII_PSI_ENCUM_TYPES_OBLIGATION', 'Obligation',
'FII_PSI_ENCUM_TYPES_COMMITMENT', 'Commitment') lookup_type
FROM fnd_lookup_values a
WHERE a.lookup_type in ( 'FII_PSI_ENCUM_TYPES_OBLIGATION',
'FII_PSI_ENCUM_TYPES_COMMITMENT')
AND a.view_application_id = 450
AND a.language = userenv('LANG')
AND upper(a.lookup_code) not in (select upper(encumbrance_type)
from gl_encumbrance_types);
INSERT INTO fii_encum_type_mappings
(encumbrance_type_id,
encumbrance_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
SELECT b.encumbrance_type_id,
decode(a.lookup_type, 'FII_PSI_ENCUM_TYPES_OBLIGATION', 'OBLIGATION',
'FII_PSI_ENCUM_TYPES_COMMITMENT', 'COMMITMENT'),
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id
FROM fnd_lookup_values a,
gl_encumbrance_types b
WHERE a.lookup_type in ( 'FII_PSI_ENCUM_TYPES_OBLIGATION',
'FII_PSI_ENCUM_TYPES_COMMITMENT')
AND a.view_application_id = 450
AND a.language = userenv('LANG')
AND upper(a.lookup_code) = upper(b.encumbrance_type);
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_encum_type_mappings');
PROCEDURE INSERT_INTO_STG (p_sort_area_size IN NUMBER,
p_hash_area_size IN NUMBER,
l_start_date IN DATE,
l_end_date IN DATE) IS
l_stmt VARCHAR2(1000);
g_phase := 'Inserting into FII_GL_JE_SUMMARY_STG';
INSERT /*+ append parallel(fii_gl_je_summary_stg) */ INTO FII_GL_JE_SUMMARY_STG
(day,
week,
period,
quarter,
year,
company_id,
cost_center_id,
fin_category_id,
prod_category_id,
user_dim1_id,
user_dim2_id,
je_source,
je_category,
ledger_id,
effective_date,
chart_of_accounts_id,
functional_currency,
amount_b,
prim_conversion_rate,
sec_conversion_rate,
committed_amount_b,
obligated_amount_b,
other_amount_b,
posted_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
SELECT /*+ ORDERED parallel(v1) parallel(line) use_hash(line,fset2) use_nl(fin)
swap_join_inputs(sob) swap_join_inputs(fset2) pq_distribute(fset2,none,broadcast) */
to_number(to_char(line.effective_date,'J')) ,
to_number(NULL, 999),
to_number(NULL, 999) ,
to_number(NULL, 999) ,
999, -- Insert value into YEAR field so this day level record can be inserted into summary table
fin.company_id,
fin.cost_center_id,
fin.natural_account_id,
NVL(fin.prod_category_id, -1),
fin.user_dim1_id,
fin.user_dim2_id,
v1.je_source ,
v1.je_category ,
fset2.set_of_books_id,
line.effective_date,
fset2.chart_accs_id_sob,
fset2.currency_code,
decode(v1.actual_flag,
'A', sum(NVL(line.accounted_cr, 0) - NVL(line.accounted_dr, 0)),
0),
-- fii_currency.get_global_rate_primary(sob.currency_code, line.effective_date),
-- fii_currency.get_global_rate_secondary(sob.currency_code, line.effective_date),
-1,
-1,
decode(v1.actual_flag,
'E', decode(v1.encumbrance_type,
'COMMITMENT', sum(NVL(line.accounted_cr, 0) -
NVL(line.accounted_dr, 0)),
0),
0), -- For encumbrances: requisitions (committed_amount)
decode(v1.actual_flag,
'E', decode(v1.encumbrance_type,
'OBLIGATION', sum(NVL(line.accounted_cr, 0) -
NVL(line.accounted_dr, 0)),
0),
0), -- For encumbrances: purchase orders (obligated_amount)
decode(v1.actual_flag,
'E', decode(v1.encumbrance_type,
'OTHERS', sum(NVL(line.accounted_cr, 0) -
NVL(line.accounted_dr, 0)),
0),
0), -- For encumbrances: others (other_amount)
decode(g_industry,
'G', decode(v1.actual_flag,--for Government
'A', null, -- for actuals
v1.posted_date), -- for encumbrances
null), -- for Commercial
trunc(sysdate), -- bug 4323856
g_fii_user_id,
trunc(sysdate), -- bug 4323856
g_fii_user_id,
g_fii_login_id
-- rewrite the v1 inline view beased on perf team's suggestion bug 4214956
-- [old definition of vi inline view]
--
-- FROM (
-- SELECT /*+ no_merge ordered parallel(jeh) parallel(per) parallel(fset) parallel(fgph) use_hash(jeh,per,fset,fgph) */
/* jeh.je_header_id,
jeh.currency_code,
jeh.je_source,
jeh.je_category,
jeh.posted_date, --Added for PSI
jeh.encumbrance_type_id, --Added for PSI
jeh.actual_flag, --Added for PSI
org.req_encumbrance_type_id,
org.purch_encumbrance_type_id
FROM gl_je_headers jeh,
(select distinct hdrs.ledger_id, hdrs.je_batch_id, bat.org_id
from gl_je_headers hdrs, gl_je_batches bat
where hdrs.je_batch_id = bat.je_batch_id
) jeb,
financials_system_params_all org,
(
SELECT p.period_name, s.ledger_id
FROM gl_periods p, gl_ledgers_public_v s
WHERE p.start_date <= l_end_date
AND p.end_date >= l_start_date
AND p.period_set_name = s.period_set_name) per,
(SELECT DISTINCT
slga.ledger_id,
DECODE(slga.je_rule_set_id, NULL, '-1', rule.JE_SOURCE_NAME) je_source_name,
DECODE(slga.je_rule_set_id, NULL, '-1', rule.JE_CATEGORY_NAME) je_category_name
FROM fii_slg_assignments slga,
gl_je_inclusion_rules rule,
fii_source_ledger_groups fslg
WHERE slga.je_rule_set_id = rule.je_rule_set_id (+)
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code) fset,
fii_gl_processed_header_ids fgph
WHERE jeh.ledger_id = fset.ledger_id
AND jeh.je_batch_id = jeb.je_batch_id
AND jeb.org_id = org.org_id (+)
AND jeb.ledger_id = org.set_of_books_id (+)
AND (jeh.je_source = fset.je_source_name OR fset.je_source_name = '-1')
AND (jeh.je_category = fset.je_category_name OR fset.je_category_name = '-1')
AND jeh.currency_code <> 'STAT'
AND jeh.period_name = per.period_name
AND jeh.ledger_id = per.set_of_books_id
AND jeh.je_header_id = fgph.je_header_id(+)
AND fgph.je_header_id IS NULL
AND jeh.status = 'P'
AND decode (jeh.actual_flag,
'A', 1,
'E', 1,
0) = 1
) v1,
*/
-- rewrite the v1 inline view beased on perf team's suggestion bug 4214956
-- [new definition of vi inline view]
--
FROM (
SELECT /*+ no_merge ordered parallel(jeh) parallel(s) parallel(p) parallel(fset) parallel(fgph)
use_hash(jeh ,per ,fset ,fgph) swap_join_inputs(fgph) swap_join_inputs(fset) */
jeh.je_header_id,
jeh.currency_code,
jeh.je_source,
jeh.je_category,
p2.start_date posted_date, --jeh.posted_date, --Added for PSI
jeh.encumbrance_type_id, --Added for PSI
jeh.actual_flag, --Added for PSI
decode(g_industry, 'G', NVL(etype.encumbrance_type, 'OTHERS'),
'C', NULL) encumbrance_type
FROM gl_ledgers_public_v s,
gl_periods p,
gl_periods p2,
gl_je_headers jeh,
fii_encum_type_mappings etype,
(SELECT /*+ no_merge */ DISTINCT
slga.ledger_id,
DECODE(slga.je_rule_set_id, NULL, '-1', rule.JE_SOURCE_NAME) je_source_name,
DECODE(slga.je_rule_set_id, NULL, '-1', rule.JE_CATEGORY_NAME) je_category_name
FROM fii_slg_assignments slga,
gl_je_inclusion_rules rule,
fii_source_ledger_groups fslg
WHERE slga.je_rule_set_id = rule.je_rule_set_id (+)
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code) fset,
fii_gl_processed_header_ids fgph
WHERE jeh.ledger_id = fset.ledger_id
AND (jeh.je_source = fset.je_source_name OR fset.je_source_name = '-1')
-- Bug 5026804: Exclude the journal source - Closing Journal
AND jeh.je_source <> 'Closing Journal'
AND (jeh.je_category = fset.je_category_name OR fset.je_category_name = '-1')
AND jeh.currency_code <> 'STAT'
AND jeh.period_name = p.period_name
AND jeh.ledger_id = s.ledger_id
AND jeh.je_header_id = fgph.je_header_id(+)
AND fgph.je_header_id IS NULL
AND jeh.status = 'P'
AND jeh.actual_flag IN ('A','E')
AND jeh.encumbrance_type_id = etype.encumbrance_type_id (+)
AND p.start_date <= l_end_date --:b3
AND p.end_date >= l_start_date --:b2
AND p.period_set_name = s.period_set_name
AND p2.period_set_name = s.period_set_name
AND trunc(jeh.posted_date) between p2.start_date and p2.end_date
AND p2.period_type = s.accounted_period_type
AND p2.adjustment_period_flag = 'N'
) v1,
gl_je_lines line,
( SELECT /*+ no_merge */
SOB.ledger_id set_of_books_id,
SLGA2.ledger_id,
SLGA2.bal_seg_value_id,
SLGA2.chart_of_accounts_id,
SOB.currency_code,
SOB.CHART_OF_ACCOUNTS_ID chart_accs_id_sob
FROM gl_ledgers_public_v SOB,
FII_SLG_ASSIGNMENTS SLGA2,
FII_SOURCE_LEDGER_GROUPS FSLG2
WHERE SOB.LEDGER_ID = SLGA2.LEDGER_ID
AND SLGA2.SOURCE_LEDGER_GROUP_ID = FSLG2.SOURCE_LEDGER_GROUP_ID
AND FSLG2.USAGE_CODE = 'DBI'
) fset2,
fii_gl_ccid_dimensions fin
WHERE v1.je_header_id = line.je_header_id
AND line.code_combination_id = fin.code_combination_id
AND line.ledger_id = fset2.set_of_books_id
AND line.ledger_id = fset2.ledger_id
AND ( fin.company_id = fset2.bal_seg_value_id
OR fset2.bal_seg_value_id = -1 )
AND fin.chart_of_accounts_id = fset2.chart_of_accounts_id
GROUP BY line.effective_date,
fin.company_id,
fin.cost_center_id,
fin.natural_account_id,
NVL(fin.prod_category_id, -1),
fin.user_dim1_id,
fin.user_dim2_id,
v1.je_source,
v1.je_category,
fset2.set_of_books_id,
fset2.chart_accs_id_sob,
fset2.currency_code,
decode(g_industry,
'G', decode(v1.actual_flag,--for Government
'A', null, -- for actuals
v1.posted_date), -- for encumbrances
null), -- for Commercial
v1.encumbrance_type,
v1.actual_flag;
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_gl_je_summary_stg');
Error in Function: INSERT_INTO_STG
Phase: ' || g_phase || '
Message: '||sqlerrm);
END INSERT_INTO_STG;
g_phase := 'Inserting into fii_gl_je_summary_b';
INSERT /*+ append parallel(fii_gl_je_summary_b) */ INTO fii_gl_je_summary_b
(time_id,
period_type_id,
cost_center_id,
fin_category_id,
company_id,
prod_category_id,
user_dim1_id,
user_dim2_id,
je_source,
je_category,
-- effective_date,
ledger_id,
chart_of_accounts_id,
functional_currency,
amount_b,
prim_amount_g,
sec_amount_g,
committed_amount_b,
committed_amount_prim,
obligated_amount_b,
obligated_amount_prim,
other_amount_b,
other_amount_prim,
posted_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
SELECT /*+ parallel(bsum) parallel(fday) use_hash(fday,stg) */
fday.week_id,
16,
bsum.cost_center_id,
bsum.fin_category_id,
bsum.company_id,
bsum.prod_category_id,
bsum.user_dim1_id,
bsum.user_dim2_id,
bsum.je_source,
bsum.je_category,
-- MAX(stg.effective_date),
bsum.ledger_id,
bsum.chart_of_accounts_id,
bsum.functional_currency,
SUM(bsum.amount_b) amount_b,
SUM(bsum.prim_amount_g) prim_amount_g,
SUM(bsum.sec_amount_g) sec_amount_g,
SUM(bsum.committed_amount_b) committed_amount_b,
SUM(bsum.committed_amount_prim) committed_amount_prim,
SUM(bsum.obligated_amount_b) obligated_amount_b,
SUM(bsum.obligated_amount_prim) obligated_amount_prim,
SUM(bsum.other_amount_b) other_amount_b,
SUM(bsum.other_amount_prim) other_amount_prim,
bsum.posted_date,
bsum.last_update_date,
bsum.last_updated_by,
bsum.creation_date,
bsum.created_by,
bsum.last_update_login
FROM fii_gl_je_summary_b bsum,
fii_time_day fday
WHERE bsum.time_id = fday.report_date_julian
GROUP BY
bsum.cost_center_id,
bsum.fin_category_id,
bsum.company_id,
bsum.prod_category_id,
bsum.user_dim1_id,
bsum.user_dim2_id,
bsum.je_source,
bsum.je_category,
-- stg.effective_date,
bsum.ledger_id,
bsum.chart_of_accounts_id,
bsum.functional_currency,
bsum.last_update_date,
bsum.last_updated_by,
bsum.creation_date,
bsum.created_by,
bsum.last_update_login,
fday.week_id,
bsum.posted_date ;
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
g_phase := 'Inserting into fii_gl_je_summary_b';
INSERT /*+ append parallel(fii_gl_je_summary_b) */ INTO fii_gl_je_summary_b
(time_id,
period_type_id,
cost_center_id,
fin_category_id,
company_id,
prod_category_id,
user_dim1_id,
user_dim2_id,
je_source,
je_category,
-- effective_date,
ledger_id,
chart_of_accounts_id,
functional_currency,
amount_b,
prim_amount_g,
sec_amount_g,
committed_amount_b,
committed_amount_prim,
obligated_amount_b,
obligated_amount_prim,
other_amount_b,
other_amount_prim,
posted_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
Select * From (
SELECT /*+ parallel(bsum) parallel(fday) use_hash(fday,stg) */
NVL(fday.ent_period_id, NVL(fday.ent_qtr_id, fday.ent_year_id)) time_id,
DECODE(fday.ent_period_id, NULL, DECODE(fday.ent_qtr_id, NULL, 128, 64), 32) period_type_id,
bsum.cost_center_id,
bsum.fin_category_id,
bsum.company_id,
bsum.prod_category_id,
bsum.user_dim1_id,
bsum.user_dim2_id,
bsum.je_source,
bsum.je_category,
bsum.ledger_id,
bsum.chart_of_accounts_id,
bsum.functional_currency,
SUM(bsum.amount_b) amount_b,
SUM(bsum.prim_amount_g) prim_amount_g,
SUM(bsum.sec_amount_g) sec_amount_g,
SUM(bsum.committed_amount_b) committed_amount_b,
SUM(bsum.committed_amount_prim) committed_amount_prim,
SUM(bsum.obligated_amount_b) obligated_amount_b,
SUM(bsum.obligated_amount_prim) obligated_amount_prim,
SUM(bsum.other_amount_b) other_amount_b,
SUM(bsum.other_amount_prim) other_amount_prim,
bsum.posted_date,
bsum.last_update_date,
bsum.last_updated_by,
bsum.creation_date,
bsum.created_by,
bsum.last_update_login
FROM fii_gl_je_summary_b bsum,
fii_time_day fday
WHERE bsum.time_id = fday.report_date_julian
GROUP BY
bsum.cost_center_id,
bsum.fin_category_id,
bsum.company_id,
bsum.prod_category_id,
bsum.user_dim1_id,
bsum.user_dim2_id,
bsum.je_source,
bsum.je_category,
bsum.ledger_id,
bsum.chart_of_accounts_id,
bsum.functional_currency,
bsum.posted_date,
bsum.last_update_date,
bsum.last_updated_by,
bsum.creation_date,
bsum.created_by,
bsum.last_update_login,
ROLLUP (fday.ent_year_id,
fday.ent_qtr_id,
fday.ent_period_id))
where time_id is not null;
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
PROCEDURE INSERT_INTO_RATES IS
l_global_prim_curr_code VARCHAR2(30);
g_phase := 'Inserting into fii_gl_revenue_rates_temp';
insert into fii_gl_revenue_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 /*+ no_merge parallel(FII_gl_je_summary_STG)*/ distinct
FUNCTIONAL_CURRENCY cc,
effective_date dt
from FII_gl_je_summary_STG
);
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 IS
l_stmt VARCHAR2(1000);
insert /*+ append parallel(bsum) */ INTO fii_gl_je_summary_b bsum
(bsum.time_id,
bsum.period_type_id,
bsum.company_id,
bsum.cost_center_id,
bsum.fin_category_id,
bsum.prod_category_id,
bsum.user_dim1_id,
bsum.user_dim2_id,
bsum.je_source,
bsum.je_category,
bsum.ledger_id,
bsum.chart_of_accounts_id,
bsum.functional_currency,
bsum.amount_B,
bsum.prim_amount_G,
bsum.sec_amount_G,
bsum.committed_amount_b,
bsum.committed_amount_prim,
bsum.obligated_amount_b,
bsum.obligated_amount_prim,
bsum.other_amount_b,
bsum.other_amount_prim,
bsum.posted_date,
bsum.creation_date,
bsum.created_by,
bsum.last_update_date,
bsum.last_update_login,
bsum.last_updated_by)
SELECT /*+ leading(r) use_hash(stg) parallel(stg) parallel(r) */
stg.day,
1,
stg.company_id,
stg.cost_center_id,
stg.fin_category_id,
stg.prod_category_id,
stg.user_dim1_id,
stg.user_dim2_id,
stg.je_source,
stg.je_category,
stg.ledger_id,
stg.chart_of_accounts_id,
stg.functional_currency,
sum(stg.amount_B),
sum(round((stg.amount_B * r.prim_conversion_rate)/g_primary_mau) * g_primary_mau),
sum(round((stg.amount_B * r.sec_conversion_rate) /g_secondary_mau)*g_secondary_mau),
sum(stg.committed_amount_B),
sum(round((stg.committed_amount_B * r.prim_conversion_rate)/g_primary_mau) * g_primary_mau),
sum(stg.obligated_amount_B),
sum(round((stg.obligated_amount_B * r.prim_conversion_rate)/g_primary_mau) * g_primary_mau),
sum(stg.other_amount_B),
sum(round((stg.other_amount_B * r.prim_conversion_rate)/g_primary_mau) * g_primary_mau),
stg.posted_date,
stg.creation_date,
stg.created_by,
stg.last_update_date,
stg.last_update_login,
stg.last_updated_by
FROM FII_GL_JE_SUMMARY_STG stg, fii_gl_revenue_rates_temp r
where stg.year IS NOT NULL
AND stg.effective_date = r.trx_date
AND stg.functional_currency = r.functional_currency
GROUP BY stg.day,
stg.cost_center_id,
stg.company_id,
stg.fin_category_id,
stg.prod_category_id,
stg.user_dim1_id,
stg.user_dim2_id,
stg.je_source,
stg.je_category,
stg.ledger_id,
stg.chart_of_accounts_id,
stg.functional_currency,
stg.posted_date,
stg.creation_date,
stg.created_by,
stg.last_update_date,
stg.last_update_login,
stg.last_updated_by;
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
Error in Function: Insert_Into_Summary
Phase: ' || g_phase || '
Message: '||sqlerrm);
END INSERT_INTO_SUMMARY;
PROCEDURE INSERT_CARRYFWD_BASE IS
l_sqlstmt VARCHAR2(5000);
fii_util.put_line('Insert carryforward data into fii_gl_enc_carryfwd_f...');
g_phase := 'Inserting encumbrance carry forward into fii_gl_enc_carryfwd_f';
'INSERT /*+ append parallel(bsum) */ INTO fii_gl_enc_carryfwd_f bsum '||
' (bsum.time_id, bsum.period_type_id, bsum.company_id, '||
' bsum.cost_center_id, bsum.fin_category_id, bsum.prod_category_id, '||
' bsum.user_dim1_id, bsum.user_dim2_id, bsum.je_source, '||
' bsum.je_category, bsum.ledger_id, bsum.chart_of_accounts_id, '||
' bsum.functional_currency, '||
' bsum.committed_amount_b, bsum.committed_amount_prim, '||
' bsum.obligated_amount_b, bsum.obligated_amount_prim, '||
' bsum.other_amount_b, bsum.other_amount_prim, '||
' bsum.posted_date, bsum.creation_date, bsum.created_by, '||
' bsum.last_update_date, bsum.last_update_login, '||
' bsum.last_updated_by) '||
' SELECT '||
' NVL(stg.day, NVL(stg.period, NVL(stg.quarter, stg.year))), '||
' DECODE(stg.day, null, '||
' DECODE(stg.period, null, '||
' DECODE(stg.quarter, null, 128, 64), 32), 1), '||
' stg.company_id, '||
' stg.cost_center_id, stg.fin_category_id, stg.prod_category_id, '||
' stg.user_dim1_id, stg.user_dim2_id, stg.je_source, '||
' stg.je_category, stg.ledger_id, stg.chart_of_accounts_id, '||
' stg.functional_currency, '||
' stg.committed_amount_B, stg.committed_amount_B, '||
' stg.obligated_amount_B, stg.obligated_amount_B, '||
' stg.other_amount_B, stg.other_amount_B, '||
' stg.posted_date, stg.creation_date, stg.created_by, '||
' stg.last_update_date, stg.last_update_login, '||
' stg.last_updated_by '||
' FROM FII_GL_ENC_CARRYFWD_T stg '||
' WHERE stg.functional_currency = :global_prim_curr '||
' AND stg.year IS NOT NULL ';
v1 => 'FII_GL_JE_B_C.INSERT_CARRYFWD_BASE()',
t2 => 'VARIABLE',
v2 => 'LENGTH(l_sqlstmt)',
t3 => 'VALUE',
v3 => TO_CHAR(LENGTH(l_sqlstmt)));
fii_util.put_line('Inserted '||SQL%ROWCOUNT||
' rows into FII_GL_ENC_CARRYFWD_F');
Error in Function: INSERT_CARRYFWD_BASE
Phase: ' || g_phase || '
Message: '||sqlerrm);
END INSERT_CARRYFWD_BASE;
fii_util.put_line('Delete data from fact table if time/dimension no longer exists in the currency run...');
g_phase := 'Delete carryforward data from fact not included in currency run';
' DELETE '||
' FROM fii_gl_enc_carryfwd_f '||
' WHERE (time_id, company_id, cost_center_id, fin_category_id, '||
' prod_category_id, user_dim1_id, user_dim2_id, '||
' committed_amount_b, obligated_amount_b, other_amount_b) '||
' NOT IN '||
' (SELECT '||
' NVL(stg.day, NVL(stg.period, NVL(stg.quarter, stg.year))), '||
' company_id, cost_center_id, fin_category_id, '||
' prod_category_id, user_dim1_id, user_dim2_id, '||
' committed_amount_b, obligated_amount_b, other_amount_b '||
' FROM fii_gl_enc_carryfwd_t stg '||
' WHERE functional_currency = :global_primary) ';
v1 => 'FII_GL_JE_B_C.INSERT_CARRYFWD_BASE()',
t2 => 'VARIABLE',
v2 => 'LENGTH(l_sqlstmt)',
t3 => 'VALUE',
v3 => TO_CHAR(LENGTH(l_sqlstmt)));
fii_util.put_line('Deleted '||SQL%ROWCOUNT||
' rows from FII_GL_ENC_CARRYFWD_F');
fii_util.put_line('Insert new data into fii_gl_enc_carryfwd_f...');
g_phase := 'Insert new data into fii_gl_enc_carryfwd_f';
' INSERT INTO fii_gl_enc_carryfwd_f '||
' (time_id, period_type_id, company_id, cost_center_id, '||
' fin_category_id, prod_category_id, user_dim1_id, '||
' user_dim2_id, je_source, je_category, '||
' ledger_id, chart_of_accounts_id, functional_currency, '||
' committed_amount_b, committed_amount_prim, '||
' obligated_amount_b, obligated_amount_prim, '||
' other_amount_b, other_amount_prim, posted_date, '||
' creation_date, created_by, last_update_date, '||
' last_update_login, last_updated_by) '||
' SELECT '||
' NVL(day, NVL(period, NVL(quarter, year))), '||
' DECODE(day, null, '||
' DECODE(period, null, '||
' DECODE(quarter, null, 128, 64), 32), 1), '||
' company_id, cost_center_id, fin_category_id, '||
' prod_category_id, user_dim1_id, user_dim2_id, '||
' je_source, je_category, ledger_id, '||
' chart_of_accounts_id, functional_currency, '||
' committed_amount_B, committed_amount_B, '||
' obligated_amount_B, obligated_amount_B, '||
' other_amount_B, other_amount_B, posted_date, '||
' creation_date, created_by, last_update_date, '||
' last_update_login, last_updated_by '||
' FROM FII_GL_ENC_CARRYFWD_T '||
' WHERE functional_currency = :global_primary '||
' AND year IS NOT NULL '||
' AND (NVL(day, NVL(period, NVL(quarter, year))), '||
' company_id, cost_center_id, fin_category_id, '||
' prod_category_id, user_dim1_id, user_dim2_id, '||
' committed_amount_B, obligated_amount_b, other_amount_b) '||
' NOT IN '||
' (SELECT '||
' time_id, company_id, cost_center_id, fin_category_id, '||
' prod_category_id, user_dim1_id, user_dim2_id, '||
' committed_amount_b, obligated_amount_b, other_amount_b '||
' FROM fii_gl_enc_carryfwd_f) ';
v1 => 'FII_GL_JE_B_C.INSERT_CARRYFWD_BASE()',
t2 => 'VARIABLE',
v2 => 'LENGTH(l_sqlstmt)',
t3 => 'VALUE',
v3 => TO_CHAR(LENGTH(l_sqlstmt)));
fii_util.put_line('Inserted '||SQL%ROWCOUNT||
' rows into FII_GL_ENC_CARRYFWD_F');
PROCEDURE INSERT_ENC_CARRYFWD (l_ret_code IN OUT NOCOPY VARCHAR2,
l_program_type IN VARCHAR2,
l_start_date IN DATE,
l_end_date IN DATE) IS
l_sqlstmt VARCHAR2(5000);
SELECT DISTINCT sob.name, t.functional_currency
FROM fii_gl_enc_carryfwd_t t,
gl_ledgers_public_v sob
WHERE t.functional_currency NOT IN (global_prim_curr)
AND t.ledger_id = sob.ledger_id;
g_phase := 'Inserting encumbrance carry forward into fii_gl_enc_carryfwd_t';
fii_util.put_line('Insert carryforward data into fii_gl_enc_carryfwd_t...');
SELECT encumbrance_type_id
INTO l_obtype_id
FROM gl_encumbrance_types
WHERE encumbrance_type = 'Obligation';
SELECT encumbrance_type_id
INTO l_comtype_id
FROM gl_encumbrance_types
WHERE encumbrance_type = 'Commitment';
'INSERT /*+ append parallel(fii_gl_enc_carryfwd_t) */ '||
' INTO fii_gl_enc_carryfwd_t '||
' (day, period, quarter, year, '||
' company_id, cost_center_id, fin_category_id, '||
' prod_category_id, user_dim1_id, user_dim2_id, '||
' je_source, je_category, '||
' ledger_id, chart_of_accounts_id, '||
' functional_currency, '||
' committed_amount_b, obligated_amount_b, '||
' other_amount_b, posted_date, last_update_date, '||
' last_updated_by, creation_date, created_by, last_update_login) '||
'SELECT day, to_number(NULL, 999), to_number(NULL, 999), 999, '||
' company_id, cost_center_id, natural_account_id, '||
' prod_category_id, user_dim1_id, user_dim2_id, '||
' ''Manual'', ''Carry Forward'', '||
' ledger_id, chart_of_accounts_id, '||
' currency_code, '||
' sum(committed_amount_b) committed_amount_b, '||
' sum(obligated_amount_b) obligated_amount_b, '||
' sum(other_amount_b) other_amount_b, year_start_date, sysdate, '||
' :user_id, sysdate, :user_id, :login_id '||
' FROM ( '||
'SELECT /*+ parallel(per) parallel(sob) pq_distribute(sob hash,hash) '||
' pq_distribute(fset hash,hash) parallel(b) '||
' use_hash(fin,slga2,fslg2) parallel(fin) parallel(slga2) '||
' parallel(fslg2) pq_distribute(slga2 hash,hash) '||
' pq_distribute(fslg2 hash,hash) '||
' pq_distribute(fin hash,hash) */ '||
' to_char(per.start_date, ''J'') day, '||
' fin.company_id, fin.cost_center_id, fin.natural_account_id, '||
' NVL(fin.prod_category_id, -1) prod_category_id, '||
' fin.user_dim1_id, fin.user_dim2_id, '||
' sob.ledger_id, sob.chart_of_accounts_id, '||
' sob.currency_code, '||
' decode( '||
' b.encumbrance_type_id, '||
' :comtype_id, '||
' NVL(b.begin_balance_cr, 0) - NVL(b.begin_balance_dr, 0), '||
' 0) committed_amount_b, '||
' decode( '||
' b.encumbrance_type_id, '||
' :obtype_id, '||
' NVL(b.begin_balance_cr, 0) - NVL(b.begin_balance_dr, 0), '||
' 0) obligated_amount_b, '||
' decode( '||
' b.encumbrance_type_id, '||
' :comtype_id, 0, '||
' :obtype_id, 0, '||
' NVL(b.begin_balance_cr, 0) - NVL(b.begin_balance_dr, 0)) '||
' other_amount_b, '||
' per.year_start_date '||
'FROM gl_balances b, '||
' gl_ledgers_public_v sob, '||
' gl_periods per, '||
' (SELECT /*+ full(slga) */ 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 = ''DBI'') fset, '||
' fii_gl_ccid_dimensions fin, '||
' fii_slg_assignments slga2, '||
' fii_source_ledger_groups fslg2 '||
' WHERE sob.ledger_id = fset.ledger_id ';
v1 => 'FII_GL_JE_B_C.INSERT_ENC_CARRYFWD()',
t2 => 'VARIABLE',
v2 => 'LENGTH(l_sqlstmt)',
t3 => 'VALUE',
v3 => TO_CHAR(LENGTH(l_sqlstmt)));
fii_util.put_line('Inserted '||SQL%ROWCOUNT||
' rows into FII_GL_ENC_CARRYFWD_T');
' INSERT /*+ append parallel(fii_gl_enc_carryfwd_t) */ '||
' INTO fii_gl_enc_carryfwd_t '||
' (period, quarter, year, '||
' company_id, cost_center_id, fin_category_id, '||
' prod_category_id, user_dim1_id, user_dim2_id, '||
' je_source, je_category, '||
' ledger_id, chart_of_accounts_id, functional_currency, '||
' committed_amount_b, obligated_amount_b, other_amount_b, '||
' posted_date, last_update_date, '||
' last_updated_by, creation_date, created_by, last_update_login) '||
' SELECT * FROM ( '||
' SELECT /*+ parallel(t) parallel(fday) use_hash(fday,t) */ '||
' fday.ent_period_id, fday.ent_qtr_id, fday.ent_year_id, '||
' t.company_id, t.cost_center_id, t.fin_category_id, '||
' t.prod_category_id, t.user_dim1_id, t.user_dim2_id, '||
' t.je_source, t.je_category, '||
' t.ledger_id, t.chart_of_accounts_id, t.functional_currency, '||
' SUM(t.committed_amount_b) committed_amount_b, '||
' SUM(t.obligated_amount_b) obligated_amount_b, '||
' SUM(t.other_amount_b) other_amount_b, '||
' t.posted_date, t.last_update_date, '||
' t.last_updated_by, t.creation_date, t.created_by, '||
' t.last_update_login '||
' FROM fii_gl_enc_carryfwd_t t, '||
' fii_time_day fday '||
' WHERE t.day = fday.report_date_julian '||
' AND t.functional_currency = :global_primary '||
' GROUP BY t.company_id, t.cost_center_id, t.fin_category_id, '||
' t.prod_category_id, t.user_dim1_id, t.user_dim2_id, '||
' t.je_source, t.je_category, t.ledger_id, '||
' t.chart_of_accounts_id, t.functional_currency, '||
' t.posted_date, t.last_update_date, t.last_updated_by, '||
' t.creation_date, t.created_by, t.last_update_login, '||
' ROLLUP (fday.ent_year_id, '||
' fday.ent_qtr_id, '||
' fday.ent_period_id)) '||
' WHERE ent_year_id IS NOT NULL ';
v1 => 'FII_GL_JE_B_C.INSERT_ENC_CARRYFWD()',
t2 => 'VARIABLE',
v2 => 'LENGTH(l_sqlstmt)',
t3 => 'VALUE',
v3 => TO_CHAR(LENGTH(l_sqlstmt)));
fii_util.put_line('Inserted '|| SQL%ROWCOUNT||
' rows into FII_GL_ENC_CARRYFWD_T');
INSERT_CARRYFWD_BASE;
Error in Function: INSERT_ENC_CARRYFWD
Phase: ' || g_phase || '
Message: '||sqlerrm);
END INSERT_ENC_CARRYFWD;
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 = 'GL_RESUMMARIZE'
AND item_value = 'Y';
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 = 'GL_PROD_CHANGE'
AND item_value = 'Y';
SELECT trunc(min(stu.start_date))
INTO l_start_date
FROM gl_period_statuses stu,
fii_slg_assignments slga,
fii_source_ledger_groups fslg
WHERE slga.ledger_id = stu.set_of_books_id
AND stu.application_id = 101
AND (stu.closing_status = 'O' OR (stu.closing_status IN ('C', 'P')
AND stu.last_update_date > l_last_start_date))
AND stu.start_date >= l_global_start_date
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code;
SELECT COUNT(*)
INTO stg_count
FROM fii_gl_je_summary_stg;
INSERT_INTO_STG(p_sort_area_size, p_hash_area_size,l_start_date, l_end_date);
INSERT_INTO_RATES;
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_GL_WORKER_JOBS;
Update FII_GL_JE_SUMMARY_STG stg
SET prim_conversion_rate =
fii_currency.get_global_rate_primary(stg.functional_currency,least(sysdate, stg.effective_date))
WHERE stg.prim_conversion_rate < 0;
FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for primary currency rates in staging table');
Update FII_GL_JE_SUMMARY_STG stg
SET sec_conversion_rate =
fii_currency.get_global_rate_secondary(stg.functional_currency,least(sysdate, stg.effective_date))
WHERE stg.sec_conversion_rate < 0;
FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for secondary currency rates in staging table');
INSERT_INTO_SUMMARY;
g_phase := 'Inserting processed JE Header IDs';
INSERT_ENC_CARRYFWD(l_ret_code, p_program_type,
l_start_date, l_end_date);
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_GL_WORKER_JOBS;
UPDATE FII_GL_WORKER_JOBS
SET status = 'IN PROCESS',
worker_number = p_worker_no
WHERE status = 'UNASSIGNED'
AND rownum < 2;
SELECT start_range,
end_range
INTO l_start_range,
l_end_range
FROM FII_GL_WORKER_JOBS
WHERE worker_number = p_worker_no
AND status = 'IN PROCESS';
g_phase := 'Inserting day level summarized records';
UPDATE FII_GL_WORKER_JOBS
SET status = 'COMPLETED'
WHERE status = 'IN PROCESS'
AND worker_number = p_worker_no;
UPDATE FII_GL_WORKER_JOBS
SET status = 'FAILED'
WHERE worker_number = p_worker_no
AND status = 'IN PROCESS';