Loading asset data with source data from an external source

最新推荐文章于 2025-12-01 17:13:11 发布

转载最新推荐文章于 2025-12-01 17:13:11 发布 · 3.3k 阅读

文章标签：

#oracle #combinations #table #flex #books #structure

ORACLE ERP 专栏收录该内容

98 篇文章

订阅专栏

本文档提供使用 SQL*Loader、PL/SQL 或其他工具将来自遗留系统的 Oracle Assets 资产信息导入 Oracle Assets 的指导。适用于 Oracle Assets 11.0 和 11i 版本。介绍了通过 Mass Additions 接口进行数据迁移的步骤，包括定义导入标准、构建并运行导入程序等。

Loading asset data with source data from an external source
	Doc ID:	200077.1	Type:	BULLETIN
	Modified Date :	20-APR-2005	Status:	PUBLISHED

PURPOSE
-------
This document provides guidance in populating Oracle
Assets with asset information obtained from a legacy
system (i.e., a feeder system other than Oracle Payables
or Oracle Projects) using SQL*Loader, PL/SQL and/or other
tools. The general steps apply to both 11.0 and 11i
installations of Oracle Assets.

This document does not cover the Create Assets Feature
in the Applications Desktop Integrator (ADI), which
imports data from an Excel spreadsheet. Refer to the
Oracle Applications Desktop Integrator User's Guide
(Chapter 9 - Asset Wizard: Create Assets Feature) for
information regarding loading legacy asset data via ADI.

SCOPE & APPLICATION
-------------------
Intermediate users with knowledge of SQL or SQL*Loader

USING THE MASS ADDITIONS INTERFACE TO TRANSFER ASSET
DATA FROM EXTERNAL SYSTEMS
----------------------------------------------------

Legacy asset data is loaded into a single table -- the
Mass Additions Interface table (FA_MASS_ADDITIONS).
The Mass Additions Prepare form in Oracle Assets allows
you to manually manipulate records loaded in
FA_MASS_ADDITIONS to prepare the data for import to
the application. The Mass Additions Post process populates
multiple internal tables from data in FA_MASS_ADDITIONS.

To import asset information from another Payables system,
load data into FA_MASS_ADDITIONS and use the Prepare and
Post processes to import data into Oracle Assets.

To import asset data from another Assets system, load
data into FA_MASS_ADDITIONS and use the only the Post
process to import data directly into Oracle Assets.

Since the Mass Additions Prepare and Mass Additions Post
processes are not automatically reversible processes,
the conversion must be carefully planned and executed.

DEFINE IMPORT CRITERIA
----------------------

Complete the following Oracle Assets setup steps to
define legacy asset import criteria (reference:
Oracle Assets Users Guide Release 11.i: Page 2-55):

1. Define the accounting flexfield structure, or
identify the accounting flexfield structure already
existing in Oracle General Ledger that will be used
by Oracle Assets.

2. Define the Oracle Assets location flexfield,
which typically includes the country, state, city
and building or site.

3. Define the Oracle Assets category flexfield.

4. Define your asset key flexfield. If you do not
intend to track assets using the asset key, define
a one segment asset key flexfield without validation.

5. Define in Oracle Assets the locations, cost centers
and associated expense code combinations, asset key
flexfield combinations and suppliers that exist in
your legacy system. If this information is not tracked
in your legacy system, define default values.

A. Default Location: If location is not tracked in
your legacy system, then set up a default location
to be used in the import. Assets can be transferred
from the default location to the actual location when
this information is determined by performing a physical
inventory.

B. Default Cost Center: To define the default cost
center, create a code combination in each
depreciation expense account for the default cost
center. Assets can be transferred from the default
to the actual cost center when this information
is known.

C. Default Asset Key Flexfield Combination: Define
a default combination to be used for incoming assets
and assign the default an inactive date to avoid
inadvertent use in the future. The default combination
can be changed though the asset workbench if an actual
asset key is defined.

D. Default Suppliers: Define a default supplier with
an inactive date to avoid inadvertent future use.
Again, the supplier can be changed through the Asset
Workbench. If you use Oracle Payables or Oracle
Purchasing, the suppliers defined in these applications
are shared by Oracle Assets shares the information.

6. Define first depreciation period, and whether assets
will be entered with or without accumulated depreciation
from the legacy system.

The typical choice is last period in the fiscal year
preceding the current fiscal year, since this will
provide accumulated depreciation numbers that can be
reconciled with financial statements, and will also
ensure that the year-to-date numbers on reports are
correct for the current fiscal year. If accumulated
depreciation is not specified, the application calculates
accumulated depreciation and revaluation reserve the first
time depreciation is run. In this case, the depreciation
expense is equal to the accumulated depreciation, and the
first depreciation period absorbs that one-time expense.
This expense is not posted to GL and year-to-date numbers
should be correct for the next fiscal year.

If accumulated depreciation is specified in
FA_MASS_ADDITIONS, Oracle Assets will not recalculate it
unless you make an adjustment to that asset. In this case,
the first depreciation period should be the first period
of the current fiscal year. Otherwise, year-to-date
depreciation values will not include the year-to-date
depreciation specified in FA_MASS_ADDITIONS.

7. Define the asset numbering scheme -- user-defined or
the automatic -- and the starting automatic number.
Regardless of whether automatic numbering is not used
for the conversion, Oracle Assets uses automatic
numbering internally for the ASSET_ID. The starting
number should therefore be a value larger than the
number of converted assets to insure that the application
will not try to assign an existing asset ASSET_ID to a
newly added asset after the conversion.

8. Define your depreciation methods, prorate conventions
and other depreciation rules (e.g., ceilings, ITC rates)
for all assets in all books -- including custom
depreciation methods definition.

9. Define your asset categories. Use category names
that match the corresponding chart asset account.
Define subcategories so that all the assets in a
subcategory have the same depreciation method,
prorate convention, and other depreciation rules.
For assets in tax books that were acquired under
different (past) tax laws, set up the asset category
with different depreciation rule defaults for different
DPIS ranges. The asset category and date placed in
service determine the depreciation rule defaults for
an asset. Depreciation information can be changed for
individual assets within each category, but the
objective is to define categories that require fewest
manual modifications.

10. Complete the standard Oracle Assets installation
using criteria defined in the previous steps.

BUILD AND RUN IMPORT PROGRAM TO POPULATE INTERIM TABLE
------------------------------------------------------

In most cases, legacy asset data will be exported
from a non-Oracle file system into an interim table,
then imported into Oracle tables using SQL*Loader
(reference: Oracle Assets Users Guide Release 11i:
Page 2-58):

1. Define an interim table in the Oracle database.
Since data must eventually be placed in a single
table (FA_MASS_ADDITIONS), use a single interim
table if possible. Data can be loaded directly
into FA_MASS_ADDITIONS, but this approach is more
difficult due to the complexity of the table.

2. If asset data is external to the Oracle database,
load your interim table using SQL*Loader.

A. Export asset data from the legacy system to
a variable or fixed format text file (e.g.,
with comma or space field value delimiters)
that can be parsed by SQL*Loader. If it is
not possible to export clean text data, asset
reports can be generated in the legacy system
and edited to manually format data. Alternately,
a custom program can be written to export data
from the legacy file system into a SQL*Loader-
readable text file. The SQL*Loader filter
feature can be used to ignore unnecessary data.

B. Create the SQL*Loader control file that
specifies how data will be imported to the
interim table. If SQL*Loader is used to filter
data, specify a discard file.

C. Run SQL*Loader to import legacy asset data.
SQL*Loader produces a log file with import
statistics, an error file containing records
that could not be imported, and a discard file
containing records filtered via control file
commands.

If the data already resides within an Oracle
database, there is no need to use SQL*Loader.
Simply consolidate the asset information in
your interim table using SQL*Plus or the Import
utility.

3. Check the number of rows in the interim table
against the number of records in your original
asset data file or table to ensure that all
asset records are imported. Compare record
counts and check the SQL*Loader files if
SQL*Loader was used to load the interim table.
The SQL*Loader log file indicates whether
records were rejected during the load, and
the error file indicates which records were
rejected. Fix and reimport the records in the
error file.

4. Spot check the interim table to verify that
data was imported into the correct columns and
that all columns were imported. Compare interim
table rows to the corresponding records in the
original asset data file or table.

RUN SQL*LOADER TO POPULATE MASS ADDITIONS
-----------------------------------------

(Reference: Oracle Assets Users Guide Release 11i:
Page 2-72):

If you are converting asset information from a
legacy asset system, use SQL*Plus to move data
from the interim table into FA_MASS_ADDITIONS.
When the table is loaded, run the Mass Additions
Status Report and the Unposted Mass Additions
Report to verify data. Additional manual data
preparation can be accomplished in the Prepare
Mass Additions form prior to running the Mass
Additions Post process.

Note: If you are using Multiple Reporting
Currencies (MRC) and are loading data from a
legacy asset system or feeder system other than
Oracle Payables and Oracle Receivables, the
FA_MC_MASS_RATES table must be loaded in addition
to the FA_MASS_ADDITIONS table. For each mass addition
line in FA_MASS_ADDITIONS, exchange rate information
must be provided in the FA_MC_MASS_RATES table for
each reporting set of books associated with the
corporate book into which the assets will be added.

To populate the FA_MASS_ADDITIONS table:

1. Load asset data from the interim table into the
FA_MASS_ADDITIONS table using SQL*Plus. Load the
LAST_UPDATE_DATE column and all the other columns
that are the same for all your assets. Suggestion:
Load your data into the FA_MASS_ADDITIONS
table in stages, posting and cleaning the table,
to avoid exceeding tablespace allocations.

2. Load expense code combination IDs. Use SQL
to match expense account information in the
interim table with the correct segments of
the GL_CODE_COMBINATIONS table. To do this,
first determine the mapping between segment
numbers and segment names for the chart of
accounts defined for the depreciation book
in the Book Controls window. For example,
run a variant of the following script:

select segment_name, application_column_name
from fnd_id_flex_segments
where id_flex_code = 'GL#'
and enabled_flag = 'Y'
and id_flex_num in
(select id_flex_num
from fnd_id_flex_structures
where id_flex_structure_code =
'&accounting_flexfield_name'
and id_flex_code = 'GL#');

Match the information in the interim table with
the appropriate segments to determine the correct
code combination id for each asset. Ensure that
the SQL script that performs the match selects
only one code combination id for each asset.
Combinations that are required but non-existent
can be created using the Account Flexfield
combinations window.

3. Load category IDs. The asset category determines
asset accounts and, with the DPIS, determines the
default depreciation method, prorate convention,
and other depreciation rules. Unless the interim
table contains explicit information about the
category to which each asset belongs, available
asset information must be used to determine asset
category. The asset account and reserve account
are often useful for determining an assetâ€™s major
category.

After determining the name of the category flexfield
structure using the System Controls window, perform
the following SQL:

select segment_name, application_column_name
from fnd_id_flex_segments
where id_flex_code = 'CAT#'
and enabled_flag = 'Y'
and id_flex_num in (
select id_flex_num
from fnd_id_flex_structures
where id_flex_structure_code =
'&category_flexfield_name'
and id_flex_code = 'CAT#');

Match information for each asset with the corresponding
columns in the FA_CATEGORIES table, the FA_CATEGORY_BOOKS
table, and the FA_CATEGORY_BOOK_DEFAULTS table.
These tables join using the CATEGORY_ID column.
The FA_CATEGORY_BOOKS table contains information
about a category that is specific to a book, (e.g.,
accounts). The FA_CATEGORY_BOOK_DEFAULTS table contains
information about a category that is specific to a book
and DPIS range (e.g., depreciation method). The
FA_CATEGORIES table contains information about a
category that is common for all books, including
the category flexfield segment values. New categories
can be set up in the Asset Categories window.

4. Load location IDs. Use SQL to match location
information in your interim table with the location
segments in the FA_LOCATIONS table. Again, the
mapping between segment names and segment numbers
must be determined. Determine the name of the
appropriate location flexfield structure and
run the following SQL:

select segment_name, application_column_name
from fnd_id_flex_segments
where id_flex_code = 'LOC#'
and enabled_flag = 'Y'
and id_flex_num in (
select id_flex_num
from fnd_id_flex_structures
where id_flex_structure_code =
'&location_flexfield_name'
Your Location Flexfield Name
and id_flex_code = 'LOC#');

Match the location information in the interim
table with the location segments in the FA_LOCATIONS
table. Load the LOCATION_ID of the matching location
record into the FA_MASS_ADDITIONS table. Be certain
to select only one location id for each asset. Set
up new locations using the Locations window.

5. Load supplier information. If supplier information
is available for legacy assets, use SQL to match the
supplier name in the interim table with the
PO_VENDOR_NAME column in the PO_VENDORS table.
Load the PO_VENDOR_ID of the matching record
into the FA_MASS_ADDITIONS table. Set up suppliers
in the Suppliers window if neither Oracle Payables
nor Oracle Purchasing is being used. Note that
PO_VENDOR_ID is the unique internal identifier,
and not the same as the external identifier
supplier number.

6. Prepare the lines in the loaded FA_MASS_ADDITIONS
table for posting in the Prepare Mass Additions
window. For example, change the POSTING_STATUS
column to HOLD for any assets that have a date
placed in service after the end of the conversion
period (e.g., last period of the previous fiscal
year).

Run Mass Additions Post to post the FA_MASS_ADDITIONS
table contents to Oracle Assets.

1. Review the Mass Additions Posting Report to compare
actual with expected results of the posting process.
Check the PERIOD_FULLY_RESERVED column in FA_BOOKS
for fully reserved assets. Assets that were placed
in service after the conversion period can be posted
via Mass Additions Post (queue changed from ON HOLD
to POST) after depreciation is run and the
appropriate period is opened.

2. Fix exceptions that were not properly imported
using Mass Additions:

A) Assets with multiple distributions: For assets
that should have multiple distribution lines, use
the Assignments window to correct the distribution
information for each of these assets.

B) Assets with investment tax credits: After
assets have been posted via Mass Additions Post,
use the Assign Investment Tax Credit window to
add ITC information.

C) Leased assets and leasehold improvements:
After assets have been posted via Mass Additions
Post, use the Asset Details window to add leasing
information to leased assets and leasehold
improvements. Verify that the life of leasehold
improvements is correct when depreciation is run.

D) Assets with parent or child assets: After
assets have been posted, use the Asset Details
window to add parent asset information to each
child asset.

E) Units of Production Assets: Units of production
assets cannot be loaded with accumulated
depreciation. (See Assets Depreciating Under
Units of Production: page 5-27). For uploading
a large number of units of production assets,
use the Production interface instead of the Mass
Additions interface.

FINISH THE MASS ADDITIONS IMPORT
--------------------------------

Complete the following steps to finish the
Mass Additions import:

1. Verify assets data by running the Asset
Additions Report. Use this report to verify
that each asset has the correct depreciation
method, life, and date placed in service. Also
verify that each asset has the correct cost and
accumulated depreciation and that the totals for
each asset account are correct. If errors are
encountered, make adjustments using the Books
window and reclassifications using the Asset
Details window. For additional verification,
project depreciation to the asset and cost
center level to verify that the expense
projections agree with estimates.

2. Run depreciation for the conversion period.
After depreciation completes, Oracle Assets
automatically runs the Journal Entry Reserve
Ledger report.

3. Reconcile corporate book depreciation amounts.
Use the Journal Entry Reserve Ledger Report from
Step 2 to verify that the depreciation amounts
are correct. If Oracle Assets calculated depreciation
for you, verify that the calculated amount is correct.
If errors are encountered, make adjustments using the
Books window and reclassifications using the Asset
Details window.

4. Run Mass Additions for post-dated assets. If
necessary, run Mass Additions to add assets to
periods following the import period. Add any other
new assets, perform transactions and run depreciation
until the current open period is correct.

5. Copy assets to your tax books using Mass Copy.
When the corporate book is correct, use Mass Copy
to copy assets into tax books. Set up tax books
so that the first period starts at the same time
as the associated corporate book. If the import
period is the last period of the previous fiscal
year, use Initial Mass Copy. If the import period
is the first period of the current fiscal year,
use Periodic Mass Copy since Oracle Assets was
not populated with historical data.

6. Reconcile tax books using the Tax Reserve
Ledger Report (in place of the Journal
Entry Reserve Ledger Report in corporate book
reconciliation). Run Periodic Mass Copy each
period to copy any new assets, cost adjustments,
retirements, and reinstatements from the corporate
book.

7. Clean up the Mass Additions holding area.
After a group of assets has been successfully
imported, they should be removed from the mass
additions holding area. First, run the Unposted
Mass Additions Report and verify the status of
any unposted mass additions. Afterward, use the
Delete Mass Additions window, and the Purge Mass
Additions window if necessary.