[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Cursor C1 is select major_seg_name
from fii_fa_cat_segments;
l_trunc_sql := 'delete from FII_EA_MAJ_CAT_GT';
l_sqlstmt := 'Insert into FII_EA_MAJ_CAT_GT( asset_cat_major_id,
minor_count )
( select distinct f.asset_cat_major_id,
count(f.asset_cat_minor_id)
from fii_fa_exp_mv f,
fii_time_structures cal
where f.time_id = cal.time_id
and cal.period_type_id = f.period_type_id
and cal.report_date in ( :ASOF_DATE, :PREVIOUS_ASOF_DATE)
and bitand(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND
and f.ledger_id = :LEDGER_ID
and f.company_id = :COMPANY_ID
and f.cost_center_id = :COST_CENTER_ID
and f.natural_account_id = :FIN_CATEGORY_ID
'||l_fud1_where||' '||l_fud2_where||
' group by f.asset_cat_major_id )';
l_sqlstmt2 := ' select maj.fii_ea_major_cat FII_EA_ASSET_CAT_MAJOR,
maj.fii_ea_major_cat_id FII_EA_ASSET_CAT_MAJ_ID,
decode(gtemp.minor_count, 0, '''||l_asset_url||''', '''||l_minor_url||''') FII_EA_ASSET_CAT_MAJOR_DRILL,
maj.fii_xtd_amount FII_EA_XTD,
maj.fii_xtd_prior_amount FII_EA_PRIOR_XTD,
( ((maj.fii_xtd_amount - maj.fii_xtd_prior_amount ) /
DECODE(maj.fii_xtd_prior_amount, 0, to_number(null), maj.fii_xtd_prior_amount)) *100) FII_EA_CHANGE,
sum(maj.fii_xtd_amount) over() FII_EA_GT_XTD,
sum(maj.fii_xtd_prior_amount) over() FII_EA_GT_PRIOR_XTD,
( ( (sum(maj.fii_xtd_amount) over() - sum(maj.fii_xtd_prior_amount) over()) /
decode ( (sum(maj.fii_xtd_prior_amount) over()), 0, to_number(null),
(sum(maj.fii_xtd_prior_amount) over()) )) * 100 ) FII_EA_GT_CHANGE
from ( select f.asset_cat_major_id FII_EA_MAJOR_CAT_ID,
cat.'||l_maj_seg_name||' FII_EA_MAJOR_CAT,
SUM(CASE WHEN cal.report_date = :ASOF_DATE
THEN tot_amount_t ELSE to_number(null) END) FII_XTD_AMOUNT,
SUM(CASE WHEN cal.report_date = :PREVIOUS_ASOF_DATE
THEN tot_amount_t ELSE to_number(null) END) FII_XTD_PRIOR_AMOUNT
from fa_categories cat,
fii_fa_exp_mv f,
fii_time_structures cal
where f.time_id = cal.time_id
and cal.period_type_id = f.period_type_id
and cal.report_date in (:ASOF_DATE, :PREVIOUS_ASOF_DATE)
and f.asset_cat_id = cat.category_id
and bitand(cal.record_type_id, :ACTUAL_BITAND ) = :ACTUAL_BITAND
and f.company_id = &FII_COMPANIES+FII_COMPANIES
and f.ledger_id = &FII_LEDGER+FII_LEDGER
and f.cost_center_id = &ORGANIZATION+HRI_CL_ORGCC
and f.natural_account_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM '||
l_fud1_where||' '||l_fud2_where||
' group by f.asset_cat_major_id, cat.'||l_maj_seg_name||' ) maj,
FII_EA_MAJ_CAT_GT gtemp
where gtemp.asset_cat_major_id = maj.fii_ea_major_cat_id
order by maj.fii_xtd_amount DESC ';
Cursor C1 is select minor_seg_name
from fii_fa_cat_segments;
l_sqlstmt := ' select min.fii_ea_minor_cat FII_EA_ASSET_CAT_MINOR,
min.fii_ea_minor_cat_id FII_EA_ASSET_CAT_MIN_ID,
'''||l_asset_url||''' FII_EA_ASSET_CAT_MINOR_DRILL,
min.fii_xtd_amount FII_EA_XTD,
min.fii_xtd_prior_amount FII_EA_PRIOR_XTD,
( ((min.fii_xtd_amount - min.fii_xtd_prior_amount ) /
DECODE(min.fii_xtd_prior_amount, 0, to_number(null),
min.fii_xtd_prior_amount)) *100) FII_EA_CHANGE,
sum(min.fii_xtd_amount) over() FII_EA_GT_XTD,
sum(min.fii_xtd_prior_amount) over() FII_EA_GT_PRIOR_XTD,
(( (sum(min.fii_xtd_amount) over() - sum(min.fii_xtd_prior_amount) over()) /
decode ( (sum(min.fii_xtd_prior_amount) over()), 0, to_number(null),
(sum(min.fii_xtd_prior_amount) over()) )) * 100 ) FII_EA_GT_CHANGE
from ( select f.asset_cat_minor_id FII_EA_MINOR_CAT_ID,
cat.'||l_min_seg_name||' FII_EA_MINOR_CAT,
SUM(CASE WHEN cal.report_date = :ASOF_DATE
THEN tot_amount_t ELSE to_number(null) END) FII_XTD_AMOUNT,
SUM(CASE WHEN cal.report_date = :PREVIOUS_ASOF_DATE
THEN tot_amount_t ELSE to_number(null) END) FII_XTD_PRIOR_AMOUNT
from fa_categories cat,
fii_fa_exp_mv f,
fii_time_structures cal
where f.time_id = cal.time_id
and cal.period_type_id = f.period_type_id
and cal.report_date in (:ASOF_DATE, :PREVIOUS_ASOF_DATE)
and f.asset_cat_id = cat.category_id
and bitand(cal.record_type_id, :ACTUAL_BITAND ) = :ACTUAL_BITAND
and f.company_id = &FII_COMPANIES+FII_COMPANIES
and f.ledger_id = &FII_LEDGER+FII_LEDGER
and f.cost_center_id = &ORGANIZATION+HRI_CL_ORGCC
and f.natural_account_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM
and f.asset_cat_major_id = &FII_ASSET_CATEGORIES+FII_ASSET_CAT_MAJOR '
||l_fud1_where||' '||l_fud2_where||
' group by f.asset_cat_minor_id, cat.'||l_min_seg_name||' ) min
order by min.fii_xtd_amount DESC ';
l_sqlstmt := ' select av.asset_number FII_EA_ASSET_NUM,
av.asset_id FII_EA_ASSET_ID,
'''||l_asset_url||''' FII_EA_ASSET_DRILL,
sum(av.xtd) FII_EA_XTD,
sum(av.prior_xtd) FII_EA_PRIOR_XTD,
( ((sum(av.xtd) - sum(av.prior_xtd))/
DECODE ( sum(av.prior_xtd), 0, to_number(null), sum(av.prior_xtd))) *100) FII_EA_CHANGE,
av.description FII_EA_ASSET_DESCR,
sum(SUM(av.xtd)) over() FII_EA_GT_XTD,
sum(SUM(av.prior_xtd)) over() FII_EA_GT_PRIOR_XTD,
( ( sum(SUM(av.xtd)) over() - sum(SUM(av.prior_xtd)) over() )/
( decode( (sum(SUM(av.prior_xtd)) over()), 0, to_number(null),
(sum(SUM(av.prior_xtd)) over() ) )) ) *100 FII_EA_GT_CHANGE
from ( select f.asset_id,
f.asset_number,
a.description,
amount_t xtd,
to_number(null) prior_xtd
from fa_additions_tl a,
fii_fa_exp_f f
where f.account_date between :CURR_PERIOD_START AND :ASOF_DATE
and f.asset_id = a.asset_id
and a.language = userenv(''LANG'')
and f.company_id = &FII_COMPANIES+FII_COMPANIES
and f.ledger_id = &FII_LEDGER+FII_LEDGER
and f.cost_center_id = &ORGANIZATION+HRI_CL_ORGCC
and f.natural_account_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM
and f.asset_cat_major_id = &FII_ASSET_CATEGORIES+FII_ASSET_CAT_MAJOR '
||l_cat_min_where||' '||l_fud1_where||' '||l_fud2_where||
' UNION ALL
select f.asset_id,
f.asset_number,
a.description,
to_number(null),
amount_t
from fa_additions_tl a,
fii_fa_exp_f f
where f.account_date between :PRIOR_PERIOD_START AND :PRIOR_PERIOD_END
and f.asset_id = a.asset_id
and a.language = userenv(''LANG'')
and f.company_id = &FII_COMPANIES+FII_COMPANIES
and f.ledger_id = &FII_LEDGER+FII_LEDGER
and f.cost_center_id = &ORGANIZATION+HRI_CL_ORGCC
and f.natural_account_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM
and f.asset_cat_major_id = &FII_ASSET_CATEGORIES+FII_ASSET_CAT_MAJOR '
||l_cat_min_where||' '||l_fud1_where||' '||l_fud2_where||
' ) av
group by av.asset_id,
av.asset_number,
av.description,
'''||l_asset_url||'''
order by 4 DESC ';