select
adb.asset_number,
adb.tag_number,
adt.description,
bks.date_placed_in_service,
adb.attribute3 ,
adb.attribute4 ,
adb.serial_number,
adb.current_units,
bks.prorate_convention_code,
bks.deprn_method_code,
bks.life_in_months,
bks.original_cost,
to_number(substr(get_asset_depren(bks.asset_id),1,instr(get_asset_depren(bks.asset_id),'-')-1 )) YEAR_To_DATE_DEPRN,
to_number(substr(get_asset_depren(bks.asset_id),instr(get_asset_depren(bks.asset_id),'-')+1 )) Acc_DEPRN,
bks.original_cost-substr(wtc_get_asset_depren(bks.asset_id),instr(get_asset_depren(bks.asset_id),'-')+1 )NBV
from
fa_books bks,
fa_additions_b adb,
fa_additions_tl adt,
fa_categories_b cata,
fa_transaction_headers ts
where
bks.transaction_header_id_out is null
and bks.date_ineffective is null
and bks.book_type_code='YourASSETS BOOK'
and bks.asset_id=ts.asset_id
and adb.asset_id=ts.asset_id
and adt.asset_id=bks.asset_id
and adb.asset_category_id=cata.category_id
and ts.transaction_type_code='ADDITION'
and ts.date_effective between (select
to_char(p.period_open_date,'DD-MON-YY')
from
fa_deprn_periods p
where
p.calendar_period_open_date>=&fromDate
and p.calendar_period_close_date<=&toDate) and (select
to_char(p.period_close_date,'DD-MON-YY')
from
fa_deprn_periods p
where
p.calendar_period_open_date>=&fromDate
and p.calendar_period_close_date<=&toDate); -- period open date and period close date --period name=NOV-14,OCT-14,SEP-14
-----------------------------------------------
select bks.asset_id,bks.date_effective
from
fa_books bks
where bks.date_effective between '22-NOV-13' and '10-DEC-13' -- period open date and period close date --period name
---------------------------------------------------------------------------------------------------------------------------------
select
to_char(p.period_open_date,'DD-MON-YY')
from
fa_deprn_periods p
where
p.calendar_period_open_date>=&fromDate
and p.calendar_period_close_date<=&toDate;
----------------------------------------------------------------------------------------------------------------------------------
select
to_char(p.period_close_date,'DD-MON-YY')
from
fa_deprn_periods p
where
p.calendar_period_open_date>=&fromDate
and p.calendar_period_close_date<=&toDate;
-----------------------------------------------
--Note
create or replace function Get_Asset_Depren(xxAssetId in number) return varchar2
is
l_deprn_reserve NUMBER;
l_ytd_deprn NUMBER; -- Year-to-Date Depreciation
l_bonus_deprn_reserve NUMBER;
l_bonus_ytd_deprn NUMBER;
l_reval_reserve NUMBER;
l_real_cost NUMBER;
dummy_num NUMBER;
dummy_char VARCHAR2 (100);
dummy_bool BOOLEAN;
l_run_mode VARCHAR2 (20) := 'STANDARD';
V_SOB_ID NUMBER:=0000;
V_log_level_rec APPS.FA_API_TYPES.log_level_rec_type;
begin
apps.fa_query_balances_pkg.query_balances(x_asset_id => xxAssetId,
x_book => 'YOUR ASSETS BOOK',
x_period_ctr =>0,
x_dist_id =>0,
x_run_mode => l_run_mode,
x_cost => l_real_cost,
x_deprn_rsv => l_deprn_reserve,
x_reval_rsv => l_reval_reserve,
x_ytd_deprn => l_ytd_deprn,
x_ytd_reval_exp => dummy_num,
x_reval_deprn_exp => dummy_num,
x_deprn_exp => dummy_num,
x_reval_amo => dummy_num,
x_prod => dummy_num,
x_ytd_prod => dummy_num,
x_ltd_prod => dummy_num,
x_adj_cost => dummy_num,
x_reval_amo_basis => dummy_num,
x_bonus_rate => dummy_num,
x_deprn_source_code => dummy_char,
x_adjusted_flag => dummy_bool,
x_transaction_header_id => -1,
x_bonus_deprn_rsv => l_bonus_deprn_reserve,
x_bonus_ytd_deprn => l_bonus_ytd_deprn,
x_bonus_deprn_amount => dummy_num,
X_IMPAIRMENT_RSV => dummy_num,
X_YTD_IMPAIRMENT => dummy_num,
X_IMPAIRMENT_AMOUNT => dummy_num,
X_CAPITAL_ADJUSTMENT => dummy_num,
X_GENERAL_FUND => dummy_num,
X_MRC_SOB_TYPE_CODE => NULL,
X_SET_OF_BOOKS_ID => V_SOB_ID,
p_log_level_rec => V_log_level_rec
);
--dbms_output.put_line(l_ytd_deprn||' '||l_deprn_reserve);
return l_ytd_deprn||'-'||l_deprn_reserve;
END;
--Summary for the above.
/*First get the asset id from the fa_transaction_headers table where transaction_type_code='ADDITION' and also
where the date_effective of the fa_transaction_headers is between the period_open_date and period_close_date taken from
the fa_deprn_period table i.e; when the fa_derpn period is opened and closed and shown above */
No comments:
Post a Comment