Inventory Adjustment Import
The inventory adjustment record is used to change the quantity and value of an inventory item without entering a purchase order. Inventory adjustments are used to account for clerical errors, changes in cost, thefts or miscounts. You can create and update inventory adjustment records using the CSV Import Assistant.
The inventory adjustment record is available for import when the Inventory feature is enabled on the Items & Inventory subtab at Setup > Company > Setup Tasks > Enable Features (Administrator). When Inventory is enabled, Inventory Adjustment is available on the Record Type dropdown list of the Import Assistant when Import Type is set to Transactions.
If the Multi-Book Accounting and Multi-Location Inventory features are enabled, you can also import accounting book file and inventory detail information as part of the import.
In the user interface, you access the inventory adjustment record at Transactions > Inventory > Adjust Inventory. For more information, see Inventory Adjustments.
For details about fields that can be mapped in the inventory adjustment record, see the SOAP Schema Browser’s inventory adjustment reference page. You can use the field definitions here as a basis for creating your own CSV import template file. For information about working with the SOAP Schema Browser, see SOAP Schema Browser.
Inventory Adjustment Body and Sublist Fields
To prepare inventory adjustment CSV data for import, make sure that your CSV file includes the following fields.
-
Adjustment Account — The account for the adjustment, for example, an expense account.
-
Date — Date of the adjustment.
Most imports also require an External ID field, which is used as a unique identifier for the record. You can specify any value that has not been used as the External ID in previous CSV imports.
Supported Inventory Adjustment Sublist Data Imports
The Inventory Adjustment import supports the import of Adjustments sublist data. Each line in this sublist represents an item requiring adjustment and should include the line, item, and quantity adjustment.
If the Multi-Book Accounting and Multi-Location Inventory features are enabled, the Inventory Adjustment import supports the import of Inventory Detail sublist data.
Not all fields of the Inventory Detail sublist available in the field mapping page are visible in the UI. In the following table you can see all fields of the Inventory Detail sublist with their description.
Field |
Description |
---|---|
Bin |
This field is for the bin number, if you are using Bins. |
Expiration Date |
This field is for the expiration date of the item added to the inventory. |
Inventory Assignment Internal ID |
This field is for the internal ID for the Inventory Assignment sublist that appears on the Inventory Details subrecord. Note that the Inventory Assignment has no UI label. |
Issue Inventory Number |
This is the select field where users pick an issueinventorynumber (Serial/Lot Number) out of inventory. |
Quantity |
This field is for the quantity of items imported in the inventory. |
Receipt Inventory Number |
This field is for entering text of serial/lot number when adding new item to the inventory. |
Status |
This field is for the status of items imported in the inventory. The status can be Damaged, Good, Quarantined, Returned, and Second Grade. |
Surrogate Subrecord ID |
This field is for the external ID of the Inventory Detail subrecord. The Surrogate Subrecord ID is required only for single file import, not a multi-file. In multi-file imports, subrecords are linked to the parent (sublist line) through file linkage. |
To Bins |
This field is binary (yes/no) and you can choose if you want to use bins. |
When using issue inventory numbers in your import, you should distinguish the records you reference in your CSV file by using unique names. Names are handled case insensitively, therefore you must use names that are different not only in their capitalization.
Alternatively, you can refer to the records by their internal IDs.
Inventory Adjustment CSV File Example
The following examples show how you might structure a file for importing inventory adjustment data.
Single File Example
E |
S |
Date |
A |
I |
I |
I |
Inventory A |
Inventory Adjustment Adjustments – Inventory Detail: Inventory Assignment Internal ID |
Inventory Adjustment Adjustments – Inventory Detail: Issue Inventory Number |
Inventory A |
---|---|---|---|---|---|---|---|---|---|---|
Cou |
P |
03/ |
C |
1 |
Location UK |
L |
10 |
InvDet1 |
Count1_1 |
5 |
Cou |
— |
— |
— |
— |
— |
— |
— |
InvDet1 |
Count1_2 |
2 |
Cou |
— |
— |
— |
— |
— |
— |
— |
InvDet1 |
Count1_3 |
3 |
Cou |
— |
— |
— |
2 |
Location UK |
L |
5 |
InvDet2 |
Count2_1 |
5 |
Cou |
P |
04/ |
C |
3 |
Location UK |
L |
3 |
InvDet3 |
Serial |
1 |
Cou |
— |
— |
— |
— |
— |
— |
— |
InvDet3 |
Serial |
1 |
Cou |
— |
— |
— |
— |
— |
— |
— |
InvDet3 |
Serial |
1 |
Multiple Files Example
With a multiple-file import, you use one file for the inventory adjustment fields, one file for the inventory detail fields, and one file for the adjustment fields.
The file holding the body data must include the external ID, date and adjustment account.
External ID |
Subsidiary |
Date |
Adjustment Account |
---|---|---|---|
CouInvtAdj_3 |
Parent Company : SUB UK |
3/30/2016 |
Checking(UK) |
CouInvtAdj_4 |
Parent Company : SUB UK |
3/30/2016 |
Checking(UK) |
Each row in the inventory detail file includes the item key, serial or lot number, and quantity.
Inventory Adjustment Adjustments – Inventory Detail : Item Key |
Inventory Adjustment Adjustments – Inventory Detail: Issue Inventory Number |
Inventory Adjustment Adjustments – Inventory Detail: Quantity |
---|---|---|
ItemKey1 |
Count1_1 |
5 |
ItemKey1 |
Count1_2 |
2 |
ItemKey1 |
Count1_3 |
3 |
ItemKey2 |
Count2_1 |
2 |
ItemKey2 |
Count2_2 |
3 |
ItemKey4 |
Count4_1 |
9 |
ItemKey4 |
Count4_2 |
66 |
ItemKey5 |
Count5_1 |
3 |
ItemKey6 |
Serial_Count1_1 |
1 |
ItemKey6 |
Serial_Count1_2 |
1 |
ItemKey6 |
Serial_Count1_3 |
1 |
The third file contains the Adjustments sublist. The external ID must be included to link the sublist record to the entry in the main file. The adjust quantity by column is also required.
Adjustment: External ID |
Adjustment: Item Key |
Adjustment: Line |
Adjustment: Location |
Adjustment: Item |
Adjustment: Adjust Qty. By |
---|---|---|---|---|---|
CouInvtAdj_1 |
ItemKey1 |
1 |
Location UK |
Lot |
10 |
CouInvtAdj_1 |
ItemKey2 |
2 |
Location UK |
Lot |
5 |
CouInvtAdj_1 |
ItemKey3 |
3 |
Location UK |
Inventory Item UK |
1 |
CouInvtAdj_2 |
ItemKey4 |
1 |
Location UK |
Lot |
675 |
CouInvtAdj_2 |
ItemKey5 |
2 |
Location UK |
Lot |
3 |
CouInvtAdj_2 |
ItemKey6 |
3 |
Location UK |
Serial |
3 |
When naming your columns, you should use the same naming conventions used by the Import Assistant so that your columns map automatically. For columns to map automatically, each sublist column should have prefixes that begin with Adjustment:.
The Import Assistant is available at Setup > Import/Export > Import Tasks > Import CSV Records. After you select the record type for import, you choose the import character encoding. For more information, see Select a Record Type for Import and Choose Import Character Encoding.