[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT B.Category_id
FROM FA_Categories_B B,
FA_Categories_TL T
WHERE B.category_id = T.category_id
AND T.language = userenv('LANG')
AND ROWNUM = 1;
SELECT DECODE(v_Category_id,null,'ALL-ALL',DECODE(flex1.application_column_name,'SEGMENT7', C.SEGMENT7, 'SEGMENT6', C.SEGMENT6,
'SEGMENT5', C.SEGMENT5, 'SEGMENT4', C.SEGMENT4, 'SEGMENT3',
C.SEGMENT3, 'SEGMENT2', C.SEGMENT2, C.SEGMENT1) ||
decode(nvl(flex2.application_column_name, ' '), ' ', ' ', '-') ||
DECODE(nvl(flex2.application_column_name, ' '), 'SEGMENT7', C.SEGMENT7, 'SEGMENT6', C.SEGMENT6,
'SEGMENT5', C.SEGMENT5, 'SEGMENT4', C.SEGMENT4, 'SEGMENT3',
C.SEGMENT3, 'SEGMENT2', C.SEGMENT2, 'SEGMENT1', C.SEGMENT1,
' '))
FROM fa_categories C,
fnd_id_flexs flexid,
fnd_id_flex_segments flex1,
fnd_id_flex_segments flex2
WHERE C.Category_id = nvl(C_category_id,C_first_category)
and flexid.application_id = 140
and flexid.id_flex_code = 'CAT#'
and flex1.id_flex_code = flexid.id_flex_code
and flex1.application_id = 140
and flex1.id_flex_code = 'CAT#'
and flex1.id_flex_num = 101
and flex1.application_column_name = 'SEGMENT1'
and flex1.enabled_flag = 'Y'
and flex2.id_flex_code (+) = flexid.id_flex_code
and flex2.application_id(+) = 140
and flex2.id_flex_code(+) = 'CAT#'
and flex2.id_flex_num(+) = 101
and flex2.application_column_name(+) = 'SEGMENT2'
and flex2.enabled_flag (+) = 'Y';
select dp1.period_open_date,
dp1.period_counter,
dp1.calendar_period_open_date,
nvl(dp2.period_close_date, sysdate),
dp2.period_counter,
flex1.application_column_name,
nvl(flex2.application_column_name, ' ')
from fa_deprn_periods dp1,
fa_deprn_periods dp2,
fnd_id_flexs flexid,
fnd_id_flex_segments flex1,
fnd_id_flex_segments flex2
where dp1.book_type_code = C_book_type_code
and dp1.period_name = C_from_period
and dp2.book_type_code = dp1.book_type_code
and dp2.period_name = C_to_period
and flexid.application_id = 140
and flexid.id_flex_code = 'CAT#'
and flex1.id_flex_code = flexid.id_flex_code
and flex1.application_id = 140
and flex1.id_flex_code = 'CAT#'
and flex1.id_flex_num = 101
and flex1.application_column_name = 'SEGMENT1'
and flex1.enabled_flag = 'Y'
and flex2.id_flex_code (+) = flexid.id_flex_code
and flex2.application_id(+) = 140
and flex2.id_flex_code(+) = 'CAT#'
and flex2.id_flex_num(+) = 101
and flex2.application_column_name(+) = 'SEGMENT2'
and flex2.enabled_flag (+) = 'Y';
delete from ja_au_srw_tax_deprn_tmp ;
/* Select candidate records between selected periods */
insert into ja_au_srw_tax_deprn_tmp
( asset_id,
asset_number,
asset_desc,
category_id,
category_number,
category_desc,
original_cost_start,
cost_start,
original_cost_end,
cost_end,
in_service,
deprn_rate,
deprn_basis_rule,
Created_by,
Creation_date,
last_update_date,
last_update_login,
last_updated_by
)
select a.asset_id,
a.asset_number,
a.description,
c.category_id,
decode(v_col1,'SEGMENT7', c.segment7, 'SEGMENT6', c.segment6,
'SEGMENT5', c.segment5, 'SEGMENT4', c.segment4, 'SEGMENT3',
c.segment3, 'SEGMENT2', c.segment2, c.segment1) ||
decode(v_col2,' ','','-') ||
decode(v_col2,'SEGMENT7', c.segment7, 'SEGMENT6', c.segment6,
'SEGMENT5', c.segment5, 'SEGMENT4', c.segment4, 'SEGMENT3',
c.segment3, 'SEGMENT2', c.segment2, 'SEGMENT1', c.segment1, ''),
c.description,
b1.original_cost,
b1.cost,
b2.original_cost,
b2.cost,
b2.date_placed_in_service,
decode(nvl(b2.adjusted_rate,0),
0, decode(b2.life_in_months, 0, 0, null, 0,
1/(b2.life_in_months/12)),
b2.adjusted_rate) * 100,
m.deprn_basis_rule,
uid,
sysdate,
sysdate ,
uid,
uid
from fa_books b1,
fa_books b2,
fa_additions a,
fa_categories c,
fa_methods m
where b1.asset_id = a.asset_id
and b1.book_type_code = v_book_type_code
and b1.date_effective =
(select min(bk.date_effective)
from fa_books bk
where bk.asset_id = a.asset_id
and bk.book_type_code = v_book_type_code
and nvl(bk.date_ineffective,sysdate+2) > v_from_date
and bk.date_effective <= v_to_date)
and nvl(b1.date_ineffective,sysdate+2) > v_from_date
and b2.asset_id = a.asset_id
and b2.book_type_code = v_book_type_code
and b2.date_effective <= v_to_date
and nvl(b2.date_ineffective, sysdate+2) > v_to_date
and m.method_code(+) = b2.deprn_method_code
and nvl(m.life_in_months(+),1) = nvl(b2.life_in_months,1)
and c.category_id = a.asset_category_id
and decode(v_col1, 'SEGMENT7', c.segment7, 'SEGMENT6', c.segment6,
'SEGMENT5', c.segment5, 'SEGMENT4', c.segment4,
'SEGMENT3', c.segment3, 'SEGMENT2', c.segment2, c.segment1) LIKE
decode(substr(v_category,1,(INSTR(v_category,'-')-1)),
'ALL', '%',
'', decode(v_category, '', '%', 'ALL', '%', v_category),
substr(v_category,1,(instr(v_category,'-')-1)))
and (v_col2 = ' ' or
decode(v_col2, 'SEGMENT7', c.segment7, 'SEGMENT6', c.segment6,
'SEGMENT5', c.segment5, 'SEGMENT4', c.segment4,
'SEGMENT3', c.segment3, 'SEGMENT2', c.segment2, c.segment1) LIKE
decode(nvl(INSTR(v_category, '-'),'0'), 0, '%',
decode(substr(v_category,(nvl(instr(v_category,'-'),0)+1)),
'ALL','%', '', '%',
substr(v_category,(nvl(instr(v_category,'-'),0)+1)))));
/* Select the maximum date retired prior to the end of the chosen interval */
update ja_au_srw_tax_deprn_tmp t
set date_retired =
(select max(r.date_retired)
from fa_transaction_headers th,
fa_retirements r
where th.asset_id = t.asset_id
and th.book_type_code = v_book_type_code
and th.date_effective <= v_to_date
and th.transaction_type_code = 'FULL RETIREMENT'
and not exists (select '1'
from fa_transaction_headers th2
where th2.asset_id = t.asset_id
and th2.book_type_code = v_book_type_code
and th2.date_effective <= v_to_date
and th2.transaction_header_id > th.transaction_header_id
and th2.transaction_type_code = 'REINSTATEMENT')
and r.transaction_header_id_in = th.transaction_header_id);
/* Delete records where the maximum date retired is less than the start of the
interval */
delete from ja_au_srw_tax_deprn_tmp t
where t.date_retired < v_from_cal_date;
/* Select the assets that were retired in the interval and calculate the net
book value */
update ja_au_srw_tax_deprn_tmp t
set (date_retired, net_book_value) =
(select max(r.date_retired), sum(nvl(r.nbv_retired,0))
from fa_transaction_headers th,
fa_retirements r
where th.asset_id = t.asset_id
and th.book_type_code = v_book_type_code
and th.date_effective between v_from_date and v_to_date
and th.transaction_type_code in ('PARTIAL RETIREMENT','FULL RETIREMENT')
and not exists (select '1'
from fa_transaction_headers th2
where th2.asset_id = t.asset_id
and th2.book_type_code = v_book_type_code
and th2.date_effective between v_from_date and v_to_date
and th2.transaction_header_id > th.transaction_header_id
and th2.transaction_type_code = 'REINSTATEMENT')
and r.transaction_header_id_in = th.transaction_header_id);
/* Select the depreciation reserve at the start of the interval */
update ja_au_srw_tax_deprn_tmp t
set deprn_rsrve_start =
(select deprn_reserve
from fa_deprn_summary ds
where ds.asset_id = t.asset_id
and ds.period_counter =
(select max(dp2.period_counter)
from fa_deprn_summary ds2,
fa_deprn_periods dp2
where ds2.asset_id = t.asset_id
and ds2.period_counter = dp2.period_counter
and ds2.book_type_code = v_book_type_code
and dp2.book_type_code = v_book_type_code
and dp2.period_counter < v_from_counter)
and ds.book_type_code = v_book_type_code);
/* Select the depreciation reserve at the end of the interval */
update ja_au_srw_tax_deprn_tmp t
set deprn_rsrve_end =
(select deprn_reserve
from fa_deprn_summary ds
where ds.asset_id = t.asset_id
and ds.period_counter =
(select max(dp2.period_counter)
from fa_deprn_summary ds2,
fa_deprn_periods dp2
where ds2.asset_id = t.asset_id
and ds2.period_counter = dp2.period_counter
and ds2.book_type_code = v_book_type_code
and dp2.book_type_code = v_book_type_code
and dp2.period_counter between v_from_counter and
v_to_counter)
and ds.book_type_code = v_book_type_code);
update ja_au_srw_tax_deprn_tmp t
set deprn_amount =
(select sum(ds.deprn_amount)
from fa_deprn_summary ds,
fa_deprn_periods dp
where ds.asset_id = t.asset_id
and ds.period_counter = dp.period_counter
and ds.book_type_code = v_book_type_code
and dp.book_type_code = v_book_type_code
and dp.period_counter between v_from_counter and v_to_counter);
update ja_au_srw_tax_deprn_tmp t
set addition_date =
(select th.date_effective
from fa_transaction_headers th
where th.asset_id = t.asset_id
and th.book_type_code = v_book_type_code
and th.transaction_type_code = 'ADDITION'
and th.date_effective between v_from_date and v_to_date);
update ja_au_srw_tax_deprn_tmp t
set bal_chg_applied =
(select sum(nvl(ap.bal_chg_applied,0))
from ja_au_bal_chg_applied ap
where ap.asset_id = t.asset_id
and ap.book_type_code = v_book_type_code);
Select distinct
r.retirement_id ,
r.asset_id ,
r.status ,
r.date_retired ,
nvl(r.gain_loss_amount,0) gain_loss_amount,
nvl(r.cost_retired,0) - nvl(r.nbv_retired,0) deprn_retired
from FA_DEPRN_PERIODS DP,
FA_TRANSACTION_HEADERS TH,
FA_RETIREMENTS R,
FA_ADDITIONS A,
FA_BOOKS B
where dp.period_name = (v_period_name)
and dp.book_type_code = v_book_type_code
and th.book_type_code = v_book_type_code
and th.transaction_date_entered >=dp.calendar_period_open_date
and th.transaction_date_entered <=nvl(dp.calendar_period_close_date,th.date_effective)
and th.transaction_type_code in ('PARTIAL RETIREMENT',
'FULL RETIREMENT',
'REINSTATEMENT')
and th.transaction_header_id =
decode(th.transaction_type_code,
'PARTIAL RETIREMENT', r.transaction_header_id_in,
'FULL RETIREMENT', r.transaction_header_id_in,
/* REINSTATEMENT */ r.transaction_header_id_out)
and r.asset_id = th.asset_id
and r.book_type_code = v_book_type_code
and a.asset_id = r.asset_id
and nvl(a.property_type_code,'xxxxxxxxx') <> 'DIV 10D'
/* Exclude Div 10D buildings */
and b.asset_id = r.asset_id
and b.date_ineffective is null
and nvl(b.depreciate_flag,'zzz') = 'YES';
select nvl(s.bal_chg_applied,0)
from ja_au_bal_chg_source s
where s.retirement_id = C_retirement_id;
SELECT BAL_CHARGE_ENABLED
FROM JA_AU_FA_BOOK_CONTROLS
WHERE Book_Type_code = C_Book_Type_code;
if C_Retirements_REC.status <> 'DELETED'
AND C_Retirements_REC.Gain_loss_amount > 0 then
-- Recoupment amount is limited by the Deprn retired amount
if C_Retirements_REC.gain_loss_amount >= C_Retirements_REC.deprn_retired then
v_recoupment := C_Retirements_REC.deprn_retired;
select ja_au_bal_chg_source_s.nextval
into v_bal_chg_id
from sys.dual;
insert into JA_AU_BAL_CHG_SOURCE
( bal_chg_id,
book_type_code,
asset_id,
retirement_id,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
bal_chg_amount,
bal_chg_applied,
date_retired,
bal_chg_status)
values
( v_bal_chg_id,
v_book_type_code,
C_Retirements_REC.asset_id,
C_Retirements_REC.retirement_id,
sysdate,
uid,
uid,
sysdate,
uid,
v_recoupment,
0,
C_Retirements_REC.date_retired,
'N'); /* Not applied balance */
FND_FILE.Put_Line (V_Log_Out,'...Deleted Balancing Charge Source ');
DELETE FROM ja_au_bal_chg_source s
WHERE s.retirement_id = C_Retirements_REC.retirement_id;
FND_FILE.Put_Line (V_Log_Out,'...Updated Balancing Charge Source to '||to_Char(v_recoupment));
update ja_au_bal_chg_source s
set bal_chg_amount = v_recoupment,
bal_chg_status =
decode(sign(v_recoupment - v_bal_chg_applied),
+1,
decode(v_bal_chg_applied, 0,
'N', /* Not applied balance charge */
'P'), /* Partially applied BC */
0, 'F', /* Fully applied */
-1, 'R'), /* Reversed balance charge */
last_update_date = sysdate,
last_updated_by = uid,
last_update_login = uid
where s.retirement_id = C_Retirements_REC.retirement_id;