FA Depreciation Query

Data can come into Oracle Assets via the FA_MASS_ADDITIONS table from the following sources: external sources (i.e. legacy systems), Project Accounting (PA), Accounts Payable (AP) and indirectly from Purchasing (PO).

Once the data is posted in Oracle Assets, it is then considered to be a true asset. If an asset is setup as a capitalized asset, it can be depreciated. When Depreciation has ran successfully and the period has closed, you can then submit the Create Journal Entries program (FAPOST).

The Create Journal Entries program creates the journals for General Ledger (GL) and inserts that data directly into the GL tables, GL_JE_HEADERS, GL_JE_BATCHES, and GL_JE_LINES. Therefore, no journal import from within GL is necessary. If the Create Journals program was successful, the unposted journals can be viewed immediately in GL

SELECT

fp.period_name,

dhcc.segment2 business_unit,

dhcc.segment3 account,

dhcc.segment4 dept,

adt.asset_number,

adt.tag_number,

ltrim(rtrim(cat.segment1)) ||'-'|| ltrim(rtrim(cat.segment2)) ||'-'|| ltrim(rtrim(cat.segment3)) category,

bks.date_placed_in_service acquistion_date,

bks.original_cost,

adt.description,

dh.location_id,

adt.context subject_to_property_tax,

adt.attribute1 property_tax_code,

round(sum(decode(bks.period_counter_fully_retired, '',bks.cost, 0) * dh.units_assigned / ah.units), 2) COST, round(sum(nvl(dn.deprn_amount,0) * dh.units_assigned/ ah.units), 2) deprn,

round(sum(nvl(dn.deprn_reserve,0) * dh.units_assigned/ ah.units), 2) deprn_reserve,

round(sum(nvl(dn.ytd_deprn,0) * dh.units_assigned/ ah.units), 2) YTD_DEP,

round(sum(decode(bks.period_counter_fully_retired, '', (bks.cost - dn.deprn_reserve), 0) * dh.units_assigned/ ah.units), 2) c_nbv

FROM

fa_distribution_history dh,

fa_asset_history ah,

fa_additions adt,

fa_categories_b cat,

fa_books bks,

gl_code_combinations dhcc,

fa_deprn_summary dn,

fa_deprn_periods fp

WHERE

fp.book_type_code = 'XXX'

and dn.book_type_code = 'XXXX'

and dn.period_counter =

( select dp.period_counter from fa_deprn_periods dp where dp.book_type_code = 'XXXX'

and dp.period_counter =

( select max(dpz.period_counter) from fa_deprn_summary dsz, fa_deprn_periods dpz

where dpz.book_type_code = 'XXXX'

and dpz.period_counter <= fp.period_counter

and dsz.book_type_code = 'XXXX'

and dsz.period_counter = dpz.period_counter

and dsz.asset_id = dn.asset_id ))

and bks.book_type_code = 'XXXX'
and bks.asset_id = dn.asset_id

and nvl(bks.date_ineffective,sysdate) > to_date(to_char(nvl(fp.period_close_date, sysdate),'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')

and bks.date_effective < to_date(to_char(nvl(fp.period_close_date, sysdate),'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')

and nvl(bks.period_counter_fully_retired, fp.period_counter) in

( select dpy.period_counter

from fa_deprn_periods dpy

where dpy.book_type_code = 'XXXX'
and dpy.fiscal_year = fp.fiscal_year)
and adt.asset_id = dn.asset_id
and adt.ASSET_CATEGORY_ID = cat.category_id
and adt.asset_id = dh.asset_id
and dh.book_type_code = 'XXXX'
and nvl(dh.date_ineffective,sysdate) > to_date(to_char(nvl(fp.period_close_date, sysdate),'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')

and dh.date_effective < to_date(to_char(nvl(fp.period_close_date, sysdate),'DD-MM-YYYY

HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
and dhcc.code_combination_id(+) = dh.code_combination_id
and ah.asset_id = adt.asset_id
and nvl(ah.date_ineffective,sysdate) > to_date(to_char(nvl(fp.period_close_date, sysdate),'DD-MM-YYYY
HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')

and ah.date_effective < to_date(to_char(nvl(fp.period_close_date, sysdate),'DD-MM-YYYY

HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')

GROUP BY

fp.period_name,

dhcc.segment2,
dhcc.segment4,
dhcc.segment3,
adt.asset_number,
adt.tag_number,

ltrim(rtrim(cat.segment1))||'-'||ltrim(rtrim(cat.segment2))||'-'||ltrim(rtrim(cat.segment3)),

adt.description,

bks.date_placed_in_service,

bks.original_cost,

dh.location_id,

adt.context,

adt.attribute1

Asset Addition API

High Level Steps are described below in brief for Asset Addition Through API:

1) Declare Record and Table Type Variables as below:
l_trans_rec Fa_Api_Types.trans_rec_type;
l_dist_trans_rec Fa_Api_Types.trans_rec_type;
l_asset_hdr_rec Fa_Api_Types.asset_hdr_rec_type;
l_asset_desc_rec Fa_Api_Types.asset_desc_rec_type;
l_asset_cat_rec Fa_Api_Types.asset_cat_rec_type;
l_asset_type_rec Fa_Api_Types.asset_type_rec_type;
l_asset_hierarchy_rec Fa_Api_Types.asset_hierarchy_rec_type;
l_asset_fin_rec Fa_Api_Types.asset_fin_rec_type;
l_asset_deprn_rec Fa_Api_Types.asset_deprn_rec_type;
l_asset_dist_rec Fa_Api_Types.asset_dist_rec_type;
l_asset_dist_tbl Fa_Api_Types.asset_dist_tbl_type;
l_inv_rec Fa_Api_Types.inv_rec_type;
l_inv_tbl Fa_Api_Types.inv_tbl_type;
l_inv_rate_tbl Fa_Api_Types.inv_rate_tbl_type;

2) Declare Cursor for Asset Data Staging Table

3) Open Cursor and Loop through Staging Table Records to assign values to Composite Variables

BEGIN

Fa_Srvr_Msg.Init_Server_Message;
Fa_Debug_Pkg.Initialize;
FOR rec_c_assets IN c_assets

LOOP
l_asset_desc_rec.asset_key_ccid := v_asset_key_ccid;
l_asset_desc_rec.asset_number := rec_c_assets.asset_number;
l_asset_desc_rec.manufacturer_name := rec_c_assets.manufacturer_name;
l_asset_desc_rec.serial_number := rec_c_assets.serial_number;
l_asset_desc_rec.model_number := rec_c_assets.model_number;
l_asset_desc_rec.tag_number := rec_c_assets.tag_number;
l_asset_cat_rec.category_id := v_asset_category_id;

--type info
l_asset_type_rec.asset_type := 'CAPITALIZED';

-- invoice info
l_inv_rec.fixed_assets_cost := rec_c_assets.cost;
l_inv_rec.description := 'Asset Conversion';
l_inv_rec.invoice_number:=rec_c_assets.invoice_number;
l_inv_rec.feeder_system_name := '11.5.8';
l_inv_tbl (1) := l_inv_rec;
l_asset_fin_rec.COST := rec_c_assets.cost;
l_asset_fin_rec.date_placed_in_service :=fnd_conc_date.string_to_date(rec_c_assets.date_placed_in_service);
l_asset_fin_rec.depreciate_flag := 'YES';
l_asset_fin_rec.deprn_method_code := rec_c_assets.depreciation_method;
l_asset_fin_rec.life_in_months := rec_c_assets.life_in_months;

-- deprn info
l_asset_deprn_rec.ytd_deprn := rec_c_assets.ytd_depreciation;
l_asset_deprn_rec.deprn_reserve := rec_c_assets.depreciation_reserve;

-- book / trans info
l_asset_hdr_rec.book_type_code := rec_c_assets.book_type_code;
l_trans_rec.transaction_date_entered := fnd_conc_date.string_to_date(rec_c_assets.date_placed_in_service);
l_trans_rec.who_info.last_updated_by := Fnd_Global.USER_ID;
l_trans_rec.who_info.last_update_date:= sysdate;
l_trans_rec.who_info.created_by:=Fnd_Global.USER_ID;
l_trans_rec.who_info.creation_date:= sysdate;
l_trans_rec.who_info.last_update_login:=Fnd_Global.USER_ID;

-- distribution info
l_asset_dist_rec.units_assigned := rec_c_assets.units;
l_asset_dist_rec.expense_ccid := v_expense_ccid;
l_asset_dist_rec.location_ccid := v_location_ccid;
l_asset_dist_rec.assigned_to := NULL;
l_asset_dist_rec.transaction_units := rec_c_assets.units;
l_asset_dist_tbl(1) := l_asset_dist_rec;

4) Finally Call API

Fa_Addition_Pub.do_addition
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_calling_fn => NULL,
px_trans_rec => l_trans_rec,
px_dist_trans_rec => l_dist_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_desc_rec => l_asset_desc_rec,
px_asset_type_rec => l_asset_type_rec,
px_asset_cat_rec => l_asset_cat_rec,
px_asset_hierarchy_rec => l_asset_hierarchy_rec,
px_asset_fin_rec => l_asset_fin_rec,
px_asset_deprn_rec => l_asset_deprn_rec,
px_asset_dist_tbl => l_asset_dist_tbl,
px_inv_tbl => l_inv_tbl
);

END LOOP;
END;

FA Purging

There are 2 Types of Purging in Oracle Assets:
1) Archive and Purge Transaction and Depreciation data
2) Delete and Purge Mass Additions Data

Archiving & Purging Depreciation Data
Prerequisites are,
- If necessary, Navigate to System Administrator > Profile > System and update the profile option- FA: Archive Table Sizing Factor. You can find out more on this at,
http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/fa/archiv01.htm#p_arc_resize
- Before you perform purge, it’s mandatory to check the Checkbox Allow Purge for the Book in Book Controls window. This can be done by navigating to Setup > Asset System > Book Controls in FA Managers Responsibility

Detailed Steps are as below:1) Change Responsibility to FA PURGE.
2) Open the Archive and Purge window.
3) Enter the Book and Fiscal Year you want to archive. You must archive and purge in chronological order.
4) Click on Archive Button to submit a concurrent request that changes the status from New to Archived and creates temporary archive tables with the data to be purged.
5) Oracle Assets automatically assigns an Archive Number when you save your work. The temporary table name includes a five-digit archive number as below.
FA_ARCHIVE_SUMMARY_
FA_ARCHIVE_DETAIL_
FA_ARCHIVE_ADJUSTMENT_
6) Export the archive tables to a storage device.
-----------------------------------------------------
7) Return to the Archive and Purge window and use the Archive Number to find the archive you want to purge.
8) Click on Purge Button to submit a concurrent request that changes the status from Archived to Purged and removes the archived data from Oracle Assets tables. Now DBA can drop the temporary archive tables.
9) You can only purge definitions with a status of Archived or Restored.

Restoring Data
1. To restore records that you have purged from Oracle Assets, you must first import the tables from your archive, and then perform the restore. Since the archive number is part of the temporary table name, Oracle Assets restores only the records that were archived during that archive you specify.
2. You can only restore the most recently purged fiscal year, so you must restore fiscal years in reverse chronological order.
Tables Deleted from Database after PurgingFA_DEPRN_SUMMARY
FA_DEPRN_DETAIL
FA_ADJUSTMENTS

Archiving & Purging Mass Additions Data
The Delete Mass Additions program removes mass additions in the DELETE and POSTED queues. It also removes SPLIT parents if the split children have been posted or deleted. The program archives mass additions in the DELETE queue only to an audit trail table, FA_DELETED_MASS_ADDITIONS.

Prerequisites are,
If Required, Run the Delete Mass Additions Preview Report from respective FA Managers responsibility

Detailed Steps are as below:
1) Navigate to FA PURGE Responsibility and click on Delete Mass Additions
2) In the Parameters window, specify the corporate book for which you want to clean up mass additions.
3) Choose Submit to submit a concurrent process to remove the lines. When the program completes successfully, Oracle Assets automatically runs the Mass Additions Delete report, which gives you an audit trail of the processed mass addition lines.
4) Oracle Assets maintains an Audit Trail by moving lines from Base table to Interim table- FA_DELETED_MASS_ADDITIONS
5) Review the log file and report after the request completes.
------------------------------------------------
6) Click on Purge Mass Additions from the Navigator.
7) Enter the Batch Number of the Create Mass Additions batch associated with the deleted mass additions for which you want to purge the audit trail from Oracle Assets.
8) Choose Submit to submit a concurrent process that removes archived lines from the Audit trail table for deleted mass additions.
9) When the program completes successfully, Oracle Assets automatically runs the Mass Additions Purge Report, which lists the mass addition lines you purged.
10) Review the log file and report after the request completes.

Tables Deleted from Database after Purging
- FA_MASS_ADDITIONS