Loading Physical Inventory Data (Oracle Assets Help)


	Previous	Next		Contents	Index	Navigation	Glossary	Library

Loading Physical Inventory Data

To use the Physical Inventory feature in Oracle Assets, you must load physical inventory data that you have collected into the FA_INV_INTERFACE table in Oracle Assets. The Usage column in indicates whether a field is required, optional, or system-generated.

Column Name Type Description Usage

INVENTORY VARCHAR2 (80) The user-defined inventory identification number to which this entry belongs. Required

ASSET_ID NUMBER (15) The system-generated asset identification number. System-generated

ASSET_NUMBER VARCHAR2 (15) The unique asset number assigned by either the user or Oracle Assets during asset setup. Although this field is not required, it is recommended, because it is the first field compared by the Physical Inventory Comparison program when it attempts to match an asset in physical inventory with one in the production system. Conditionally required. When you enter physical inventory data in the Physical Inventory window, either the asset number, tag number, or serial number is required.

ASSET_KEY_CCID NUMBER (15) The asset key flexfield combination. Optional

TAG_NUMBER VARCHAR2 (15) The tag number of the asset. The Physical Inventory Comparison program uses the tag number to uniquely identify an asset if it was unable to do so using the asset number, because it was not provided. Conditionally required. When you enter physical inventory data in the Physical Inventory window, either the asset number, tag number, or serial number is required.

DESCRIPTION VARCHAR2 (80) The description of the asset. Optional

MODEL_NUMBER VARCHAR2 (40) The model number of the asset. Optional

SERIAL_NUMBER VARCHAR2 (35) The serial number of the asset. The Physical Inventory Comparison program uses the serial number to uniquely identify an asset if it was unable to do so using the asset number or tag number, because neither was provided. Conditionally required. When you enter physical inventory data in the Physical Inventory window, either the asset number, tag number, or serial number is required.

MANUFACTURER_NAME VARCHAR2 (30) The name of the manufacturer that made the asset. Optional

ASSET_CATEGORY_ID NUMBER (15) The category to which the asset belongs. Optional

UNITS NUMBER The number of units in physical inventory for the asset. Required

LOCATION_ID NUMBER (15) The location ID that corresponds to the location of the asset. Required

STATUS VARCHAR2 (15) The status of the asset will be one of the following:
NEW
TO RECONCILE
RECONCILED
DIFFERENCE
NO ASSET NUMBER
NON-INVENTORIAL
NOT UNIQUE
See the status codes table. Initially system-generated, but can be manually changed by the user.

UNIT_ADJ VARCHAR2 (1) Indicates whether this entry needs a location adjustment. A NULL value indicates that this physical inventory entry has not been compared. System-generated

UNIT_RECONCILE_MTH VARCHAR2 (20) The unit adjustment reconciliation method must be one of the following:
NONE
ADDITION
UP - UNIT ADJUSTMENT
DOWN - UNIT ADJUSTMENT
REINSTATEMENT
FULL RETIREMENT
PARTIAL RETIREMENT
See the unit reconciliation table. System-generated

LOCATION_ADJ VARCHAR2 (1) Indicates whether this entry needs a location adjustment. If there is no value in this field, it indicates that this physical inventory entry has not been compared. System-generated

LOC_RECONCILE_MTH VARCHAR2 (20) The location adjustment reconciliation method: NONE TRANSFER System-generated

CREATED_BY NUMBER (15) Standard who columns. Typically, the username or user ID of the person running Physical Inventory. System-generated

CREATION_DATE DATE Standard who columns. The date when the physical inventory was opened. System-generated

LAST_UPDATE_DATE DATE Standard who columns. This date corresponds to the last date a user entered physical inventory information. System-generated

LAST_UPDATED_BY NUMBER (15) Standard who columns. The user ID of the last user to update physical inventory information. System-generated

LAST_UPDATE_LOGIN NUMBER (15) Standard who columns. The user ID of the last user to update physical inventory information. System-generated

Table 1 - 7. (Page 3 of 3) FA_INV_INTERFACE Table

Column Name	Type	Description	Usage

INVENTORY	VARCHAR2 (80)	The user-defined inventory identification number to which this entry belongs.	Required
ASSET_ID	NUMBER (15)	The system-generated asset identification number.	System-generated
ASSET_NUMBER	VARCHAR2 (15)	The unique asset number assigned by either the user or Oracle Assets during asset setup. Although this field is not required, it is recommended, because it is the first field compared by the Physical Inventory Comparison program when it attempts to match an asset in physical inventory with one in the production system.	Conditionally required. When you enter physical inventory data in the Physical Inventory window, either the asset number, tag number, or serial number is required.
ASSET_KEY_CCID	NUMBER (15)	The asset key flexfield combination.	Optional
TAG_NUMBER	VARCHAR2 (15)	The tag number of the asset. The Physical Inventory Comparison program uses the tag number to uniquely identify an asset if it was unable to do so using the asset number, because it was not provided.	Conditionally required. When you enter physical inventory data in the Physical Inventory window, either the asset number, tag number, or serial number is required.
DESCRIPTION	VARCHAR2 (80)	The description of the asset.	Optional
MODEL_NUMBER	VARCHAR2 (40)	The model number of the asset.	Optional
SERIAL_NUMBER	VARCHAR2 (35)	The serial number of the asset. The Physical Inventory Comparison program uses the serial number to uniquely identify an asset if it was unable to do so using the asset number or tag number, because neither was provided.	Conditionally required. When you enter physical inventory data in the Physical Inventory window, either the asset number, tag number, or serial number is required.
MANUFACTURER_NAME	VARCHAR2 (30)	The name of the manufacturer that made the asset.	Optional
ASSET_CATEGORY_ID	NUMBER (15)	The category to which the asset belongs.	Optional
UNITS	NUMBER	The number of units in physical inventory for the asset.	Required
LOCATION_ID	NUMBER (15)	The location ID that corresponds to the location of the asset.	Required
STATUS	VARCHAR2 (15)	The status of the asset will be one of the following: NEW TO RECONCILE RECONCILED DIFFERENCE NO ASSET NUMBER NON-INVENTORIAL NOT UNIQUE See the status codes table.	Initially system-generated, but can be manually changed by the user.
UNIT_ADJ	VARCHAR2 (1)	Indicates whether this entry needs a location adjustment. A NULL value indicates that this physical inventory entry has not been compared.	System-generated
UNIT_RECONCILE_MTH	VARCHAR2 (20)	The unit adjustment reconciliation method must be one of the following: NONE ADDITION UP - UNIT ADJUSTMENT DOWN - UNIT ADJUSTMENT REINSTATEMENT FULL RETIREMENT PARTIAL RETIREMENT See the unit reconciliation table.	System-generated
LOCATION_ADJ	VARCHAR2 (1)	Indicates whether this entry needs a location adjustment. If there is no value in this field, it indicates that this physical inventory entry has not been compared.	System-generated
LOC_RECONCILE_MTH	VARCHAR2 (20)	The location adjustment reconciliation method: NONE TRANSFER	System-generated
CREATED_BY	NUMBER (15)	Standard who columns. Typically, the username or user ID of the person running Physical Inventory.	System-generated
CREATION_DATE	DATE	Standard who columns. The date when the physical inventory was opened.	System-generated
LAST_UPDATE_DATE	DATE	Standard who columns. This date corresponds to the last date a user entered physical inventory information.	System-generated
LAST_UPDATED_BY	NUMBER (15)	Standard who columns. The user ID of the last user to update physical inventory information.	System-generated
LAST_UPDATE_LOGIN	NUMBER (15)	Standard who columns. The user ID of the last user to update physical inventory information.	System-generated
Table 1 - 7. (Page 3 of 3) FA_INV_INTERFACE Table

Status Codes

After you have loaded your physical inventory data and run the Physical Inventory comparison program, Oracle Assets generates a status code based on the information it has stored about an asset and the information you provided during the physical inventory process. contains descriptions of these codes.

Status Code Description Set by

DIFFERENCE During the comparison, Oracle Assets identified a difference in the location or number of units for this asset. Oracle Assets

NEW When you enter a new physical inventory entry, the status is automatically set to NEW. A status of NEW indicates the entry has not been compared. After an entry has been compared, you also have the option to reset the status to NEW, so that it will be compared again. Oracle Assets/User

NO ASSET NUMBER The comparison program could not locate the asset number in Oracle Assets. Oracle Assets

NON-INVENTORIAL The asset associated with the physical inventory entry is not designated to be included in physical inventory (the In Physical Inventory check box is not checked). Oracle Assets

NOT UNIQUE During the comparison, more that one asset listed in Oracle Assets matched a single physical inventory entry. Oracle Assets

RECONCILED The asset is the same in Oracle Assets and in physical inventory, and is automatically set to a status of RECONCILED. User/Oracle Assets

TO RECONCILE When a difference is identified in the comparison, you change the status to TO RECONCILE to indicate that the entry is ready to be reconciled. User

Table 1 - 8. (Page 1 of 1) Status Codes in the FA_INV_INTERFACE Table

Status Code	Description	Set by
DIFFERENCE	During the comparison, Oracle Assets identified a difference in the location or number of units for this asset.	Oracle Assets
NEW	When you enter a new physical inventory entry, the status is automatically set to NEW. A status of NEW indicates the entry has not been compared. After an entry has been compared, you also have the option to reset the status to NEW, so that it will be compared again.	Oracle Assets/User
NO ASSET NUMBER	The comparison program could not locate the asset number in Oracle Assets.	Oracle Assets
NON-INVENTORIAL	The asset associated with the physical inventory entry is not designated to be included in physical inventory (the In Physical Inventory check box is not checked).	Oracle Assets
NOT UNIQUE	During the comparison, more that one asset listed in Oracle Assets matched a single physical inventory entry.	Oracle Assets
RECONCILED	The asset is the same in Oracle Assets and in physical inventory, and is automatically set to a status of RECONCILED.	User/Oracle Assets
TO RECONCILE	When a difference is identified in the comparison, you change the status to TO RECONCILE to indicate that the entry is ready to be reconciled.	User
Table 1 - 8. (Page 1 of 1) Status Codes in the FA_INV_INTERFACE Table

Unit Reconciliation Methods

When you run the Physical Inventory Comparison program, Oracle Assets updates the UNIT_RECONCILE_MTH field in the FA_INV_INTERFACE table with one of the codes shown in . The code indicates whether the asset needs an adjustment in the number of units stored in Oracle Assets, and if so, the type of unit adjustment needed.

Code Description

ADDITION An asset was found in physical inventory that is not in Oracle Assets. The asset needs to be added to Oracle Assets.

DOWN - UNIT ADJUSTMENT For a particular asset, more units are listed in Oracle Assets than are found in physical inventory. The number of units needs to be adjusted down in Oracle Assets.

FULL RETIREMENT An asset needs to be fully retired, because it is listed in Oracle Assets but not found in physical inventory.

NONE No unit adjustment is necessary.

PARTIAL RETIREMENT An asset needs to be partially retired. Usually, you would do this when for a particular asset, you found less units in physical inventory than are listed in Oracle Assets.

REINSTATEMENT An asset needs to be reinstated. Although it was retired, during the physical inventory process, it was found that the asset was still being used.

UP - UNIT ADJUSTMENT For a particular asset, less units are listed in Oracle Assets than are found in physical inventory. The number of units needs to be adjusted up in Oracle Assets.

Table 1 - 9. (Page 1 of 1) Codes in the UNIT_RECONCILE_MTH Field

Code	Description
ADDITION	An asset was found in physical inventory that is not in Oracle Assets. The asset needs to be added to Oracle Assets.
DOWN - UNIT ADJUSTMENT	For a particular asset, more units are listed in Oracle Assets than are found in physical inventory. The number of units needs to be adjusted down in Oracle Assets.
FULL RETIREMENT	An asset needs to be fully retired, because it is listed in Oracle Assets but not found in physical inventory.
NONE	No unit adjustment is necessary.
PARTIAL RETIREMENT	An asset needs to be partially retired. Usually, you would do this when for a particular asset, you found less units in physical inventory than are listed in Oracle Assets.
REINSTATEMENT	An asset needs to be reinstated. Although it was retired, during the physical inventory process, it was found that the asset was still being used.
UP - UNIT ADJUSTMENT	For a particular asset, less units are listed in Oracle Assets than are found in physical inventory. The number of units needs to be adjusted up in Oracle Assets.
Table 1 - 9. (Page 1 of 1) Codes in the UNIT_RECONCILE_MTH Field

Methods for Loading Physical Inventory Data

You can load physical inventory data into Oracle Assets using several different methods. You can:

Import data from an Excel spreadsheet using FADI

Enter data in the Physical Inventory Entries window

Import data from a non-Oracle system using SQL*Loader

Import data from an Excel spreadsheet using the FADI

You can load your physical inventory data into an Excel spreadsheet and import the data into Oracle Assets using FADI.

Enter data using the Physical Inventory Entries window

You can enter data for each asset directly into Oracle Assets using the Physical Inventory Entries window. Keep in mind that you can only enter data for one asset at a time when using this method.

**Import data from a non-Oracle system using SQL*Loader**

You can use SQL*Loader to import physical inventory data by completing the following steps:

Define your interim table in the Oracle database.

Use a single interim table if possible. You can use multiple tables if the data exists in multiple tables or files in the system from which you are loading data. In either case, you must eventually place the data in a single table, the FA_INV_INTERFACE table.

If you prefer, you can load data directly into the FA_INV_INTERFACE table, but it is more difficult due to the complexity of the table.

Load your interim table using SQL*Loader.

Use SQL*Loader to import information from outside your Oracle database. SQL*Loader accepts a number of input file formats and loads your physical inventory data into your interim table.

If the data already resides within an Oracle database, there is no need to use SQL*Loader. Simply consolidate the physical inventory information in your interim table using SQL*Plus or import, and go directly to the next step.

Convert the physical inventory information into text form.

Most database or file systems can output data in text form. Usually you can generate a variable or fixed format data file containing comma or space delimiters from the existing system. If you cannot find a way to produce clean text data, try generating a report to disk, using a text editor to format your data. Another option is to have SQL*Loader eliminate unnecessary information during its run. If there is a large volume of information, or if the information is difficult to convert into a loadable format, you can write your own import program. Construct your program to generate a SQL*Loader readable text file.

Create the SQL*Loader control file.

In addition to the actual data text file, you must write a SQL*Loader control file. The control file tells SQL*Loader how to import the data into your interim table. Be sure to specify a discard file if you are planning to use SQL*Loader to filter your data.

Run SQL*Loader to import your physical inventory data.

Once you have created your physical inventory data file and SQL*Loader control file, run SQL*Loader to import your data. SQL*Loader produces a log file with statistics about the import, a bad file containing records that could not be imported due to errors, and a discard file containing all the records that were filtered out of the import by commands placed in the control file.

Compare record counts and check the SQL*Loader files.

Check the number of rows in the interim table against the number of records in your original physical inventory data file or table to ensure that all physical inventory records were imported.

The log file shows if records were rejected during the load, and the bad file shows which records were rejected. Fix and re-import the records in the bad file.

Spot check the interim table.

Check several records throughout the interim table and compare them to the corresponding records in the original physical data file or table. Look for missing or invalid data. This step ensures that your data was imported into the correct columns and that all columns were imported.

Contents

Index

Navigation

Glossary

Library