Skip to main content
Skip table of contents

Item data conversion

Content was last updated in 06.23.03-00

Revision History

 

This data conversion can be used to create/update one or more Items (Items, Parts and Serial/Non-serial Kit, Container, and Misc Header details) in R2.

The following table shows the fields which are supposed to be in the CSV format, required to create or Update Item definitions in R2 using Data Conversion.

Table 1.0: List for fields required in CSV

Column Name

Data Type

Description

Default Value

Is Mandatory?

Can be Updated?

ProductId

Varchar2(22)

Product ID of the Item.

 

Yes

 

UPC

Varchar2(22)

UPC of the Item.

Null

 

Yes

Description

Varchar2(100)

Description of the Item.

 

Yes

Yes

Department

Varchar2(40)

Department/Main Group to which the Item belongs.

Null

 

Yes

AvailabilityGroup

Varchar2(40)

Availability Group ID (Support is not given).

Null

 

Yes

ReportGroup

Varchar2(40)

Report Group ID.

Null

 

Yes

CommissionGroup

Varchar2(40)

Commission Group ID

(Not applicable for Parts i.e. Type = 6)

Null

 

Yes

TaxGroup

Varchar2(40)

Tax Group ID

(Not applicable for Parts i.e. Type = 6)

Null

 

Yes

ExchangeGroup

Varchar2(40)

Exchange Group ID (Support is not given)

Null

 

Yes

AttributeGroup

Varchar2(40)

Attribute Group ID.

Null

 

Yes

MeterGroup

Varchar2(40)

Meter Group ID of the Item.

(Not applicable for Parts i.e. Type = 6)

Null

 

Yes

Manufacturer

Varchar2(50)

Manufacturer of the Item.

Null

 

Yes

MfgPartNo.

Varchar2(21)

Manufacturer Part Number for the Item.

While creating/updating an Item, system prompts for ignore_mfr_part_num_duplicate. User can enter either Y or N

  • If the value is 'Y', then system will duplicate the Manufacturer Name in combination with the Manufacturer Part Number for any Item.

  • If the value is 'N', then system will not duplicate the Manufacturer Name in combination with the Manufacturer Part Number for any Item and the system will return an error saying 'Another Product exists with same Man.Part.No of this Manufacturer'.

Null

 

Yes

Model

Varchar2(15)

Model number of the Item.

Null

 

Yes

Active

Char(1)

Is the Item active or not.

Y – Yes, N – No

Y

 

 

MadeIn

Varchar2(20)

The country where the Item was made.

Null

 

Yes

QtySerial

Number(3)

101 - Qty

102 - Serial

 

 

Yes

SellPrice

Number(11,2)

Selling price of the Item.

0

 

 

Cost

Number(11,2)

Cost price of the Item.

0

 

Yes

LowestSellPrice

Number(11,2)

Item’s lowest selling price.

0

 

Yes

ReplaceMentCost

Number(11,2)

Item’s replacement cost.

0

 

Yes

AllowDiscount

Number(15,6)

Discount for the Item. Provide a discount between 1 and 100.

100

 

 

DamageWaiver

Char(1)

Y – Yes, N – No

Y

 

Yes

AllowSubrent

Char(1)

Sub-renting allowed for the Item or not.

Y – Yes, N – No

Y

 

 

AllowConsigned

Char(1)

Y – Yes, N – No

Y

 

 

ShelfLocation

Varchar2(12)

Shelf location of the Item.

Null

 

Yes

BinNumber

Varchar2(200)

Bin number of the Item.

Note: If we are giving multiple binNubmer as comma separated then whole column value should be in Double Quotes

Example: “BIN1,BIN2”

Null

 

Yes

IsPackage

Char(1)

If it is package value will be “Y”.

If it is not a package value will be “N”.

N

 

 

Type

Number(1)

1 – Item

5 - Misc

6 - Parts

1

 

 

Deposit Type

Number(1)

0 - None

1 - Fixed

2 - Percentage

0

 

Yes

Deposit Value

Number(11,2)

Percent for Deposit Type - Percentage.

Amount for Deposit Type - Fixed.

0

 

Yes

Affects Availability

Char(1)

Y – Yes, N – No

Y

 

 

URL

Varchar2(50)

 

Null

 

Yes

PrintOnOrder

Char(1)

Y – Yes, N – No

Y

 

 

IsMiscItem

Char(1)

Y – Yes, N – No

N

 

 

Length

Number(11,2)

Length of the Item.

Null

 

Yes

Width

Number(11,2)

Width of the Item.

Null

 

Yes

Height

Number(11,2)

Height of the Item.

Null

 

Yes

Weight

Number(11,2)

Weight of the Item.

Null

 

Yes

Life

Number(3)

Life of the Item in number of months.

1

 

Yes

Salvage Percentage

Number(11,2)

Salvage value of the Item as percentage of its Cost Price.

(Not applicable for Parts i.e. Type = 6)

Null

 

Yes

MaintenanceGroup

Varchar2(40)

Maintenance Group ID.

(Not applicable for Parts i.e., Type = 6)

Null

 

Yes

LaborType

Number(2)

0 - In House

1 - Freelance

2 - Union ­

Null

 

 

RevenueGroup

Varchar2(40)

Revenue Group ID.

Null

 

Yes

COGS Group

Varchar2(40)

COGS Group ID.

Null

 

Yes

Inventory Account Group

Varchar2(40)

Inventory account group.

Null

 

Yes

Discount Group

Varchar2(40)

Discount group.

Null

 

Yes

Is Freight

Char(1)

 

Null

 

 

Qty

Number(9)

Stock in hand.  Used only if type is ‘Quantity’ and IsPackage is ‘Y’.

0

 

Yes

ReOrderQuntity

Number(9)

Re-Order quantity.

Null

 

Yes

MinQty

Number(9)

Minimum Quantity.

Null

 

Yes

MaxQuantity

Number(9)

Maximum Quantity.

Null

 

Yes

Allow Rent

Char(1)

Allowed for Renting or not.

Y – Yes, N – No

Y

 

Yes

Allow Sell

Char(1)

Saleable or not.

Y – Yes, N – No

Y

 

Yes

PriceGroup

Varchar2(40)

Customer Price Group.

(Not applicable for Parts i.e., Type = 6)

Null

 

Yes

SalesGroup

Varchar2(40)

Sales Account GL Group.

Null

 

Yes

SubRentGroup

Varchar2(40)

Sub-Rent Account GL Group.

(Not applicable for Parts i.e., Type = 6)

Null

 

Yes

SiteID

Varchar2(21)

Site’s ID (It must be there in Site master table.)

Null

 

 

Depreciation Group

Varchar2(20)

Depreciation Life Group ID.

Null

 

Yes

DepreciationGLCode

Varchar2(40)

Depreciation GL Code.

(Not applicable for Parts i.e., Type = 6)

Null

 

Yes

ACCUMDEPRGLCODE

Varchar2(40)

Depreciation Group Accumulated GL Code.

(Not applicable for Parts i.e., Type = 6)

Null

 

Yes

WRITEOFFGLCODE

Varchar2(40)

Depreciation Group Write Off GL Code.

(Not applicable for Parts i.e., Type = 6)

Null

 

Yes

MSRP

Number(11,2)

Maximum Sales Retail Price.

Null

 

Yes

TurnAroundTime

Number(9)

Turnaround time.  (In Hours)

(Not applicable for Parts i.e., Type = 6)

0

 

Yes

SortPriority

Varchar2(40)

Sort Priority.

(Not applicable for Parts i.e., Type = 6)

Null

 

Yes

Allow Depreciation

Char(1)

Y – Yes, N – No

Y

 

 

Allow Statistics

Char(1)

Y – Yes, N – No

N

 

 

SubRent Expense

Varchar2(40)

Sub Rental Expense.

(Not applicable for Parts i.e., Type = 6)

Null

 

Yes

Long Description

Varchar2(2000)

Long Description.

Null

 

Yes

InvSubCategoryID

Varchar2(100)

Inventory Sub-Category ID

Null

 

Yes

DAYSINWEEK

Number(2)

Days in week for Item group.

Null

 

 

DEPRGROUPLIFE

Number(3)

 

Null

 

Yes

Keywords

Varchar2(2000)

Keywords for the item.

Null

 

Yes

WARRENTYPERIOD

Number(5,2)

Warranty period of the Item in number of months.

Null

 

Yes

Pricing Method

Varchar2(100)

Detail or D for detail pricing (Only applicable for package).

Package Pricing

 

 

Hybrid

Varchar2(20)

Y/T - Yes

F - False

F

 

 

QC

Varchar2(20)

Y/T - Yes

F - False

F

 

Yes

PurchaseCost

Number(11,2)

Select Site Purchase Price in Configuration module > Pricing/Billing tab > Set Item/Part PO Price from drop-down list, and:

  • Multi-Currency is enabled in the system – Value provided under this column for a Site is considered as Purchase Cost in Base Currency. As such the value gets converted to equivalent value in Site Currency based on Currency conversion setup and gets displayed in Site Price field for the Item in this Site. If Site ID is not provided this won’t get updated.

  • Multi-Currency is not enabled in the system – The value provided in this column for a Site gets displayed in Site Price field for the Item in this Site. If Site ID is not provided this won’t get updated.

  • When Product Purchase Price is selected in Configuration module > Pricing/Billing tab > Set Item/Part PO Price from drop-down list:

  • Multi-Currency is enabled in the system – Value provided under this column is considered as Purchase Cost in Base Currency. As such the value gets displayed in Price field for the Item.

  • Multi-Currency is not enabled in the system – The value provided in this column gets displayed in Price field for the Item.

0

 

i

SiteDiw

NUMBER(12,3)

DaysInWeek for particular Site on All Site Details

 

 

 

SiteDim

NUMBER(12,3)

DaysInMonth for particular Site on All Site Details

 

 

 

SiteInvDept

VARCHAR2(50)

Department for particular Site on All Site Details

 

 

Yes

IsContainer

Char(1)

Y – Yes

N – No

N

 

Yes

While creating new Item:

  • If Site ID is not provided in CSV file, R2 will create the item definition only.

  • If Depreciation Group is provided in CSV file and there is no definition for any of the Depreciation GLCode, Acc Depreciation GLCode or WriteOff GLCode then, these values will be defaulted from the Item.txt file.

 

While updating an existing Item:

  • Both SKU and Description are mandatory.

  • For non-serial Item – If Site ID is not provided in the CSV file then, it will not update the quantity.

 

While creating/updating an Item, system will ask the value for ignore_mfr_part_num_duplicate. User can enter either Y or N

  • If the value is 'Y', then system will duplicate the Manufacturer Name in combination with the Manufacturer Part Number for any Item.

  • If the value is 'N', then system will not duplicate the Manufacturer Name in combination with the Manufacturer Part Number for any Item and the system will return an error saying 'Another Product exists with same Man.Part.No of this Manufacturer'.

A log file named ItemErrData.log gets generated, if any errors occur while creating/updating the field definitions for Item. The following table shows the fields present in the log file.

Table 2.0: List of fields appearing in the log file

Column Name

Description

SKU

SKU of Item whose creation/updation has failed.

DESCRIPTION

DESCRIPTION of Item whose creation/updation has failed.

SiteID

SiteID of Item whose creation/updation has failed.

ErrorDetails

The reason for failure.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close