Importing ItemCost and a another Datafield (UDF) into ItemMasterData

[former_member370721]

Hi

I am trying to import Item Cost (moving average) and another field (stock location) into the Item Master Data for different Warehouses to have startup data on a new company.

I imported the Items using DTW oItems -> perfect. I now tried the ItemWarehouseInfo1 template to link the UDF and the moving average price.

DTW runs through without any error. However, there is no data in the database afterwards.

Do I use the wrong template?

Thanks

Franz

[former_member583013 · Accepted solution]

Moving Average prices (Item cost) should be imported to a Pricelist and the pricelist should be used when doing an Inventory Reconciliation after you import the stock.

Only if you use Standard cost would you import the cost to the Item Master / Item Warehouses.

Suda

[former_member370721 · Accepted solution]

In my case it has to go to OITW.

Could you please tell me which template I can use to import these prices?

For quantities I use the Stocktaking1 template.

Thanks and best regards,

Franz

[former_member583013 · Accepted solution]

You do not import data to OITW. OITW table is updated through Stock posting transaction within SAP.

For the prices use the Items_Prices template found inside oItems folder

[Former Member]

Hi Franz

My appologies, it is called UnitPrice in the DI API and Price in the template.

Kind regards

Peter Juby

[Former Member]

Hi Franz,

The two sheets you should need for bringing in the cost and the UDF are oItems and oItemWarehouseInfo.

I think the field in Item Warehouse info you need is called StandardAveragePrice.

In your items sheet, in the ManageStockByWarehouse field you will need to put the value "tYES". Add the UDF to the oItemwarehouseInfo sheet and this should work.

[Former Member]

Hi Franz

You need to put the cost in the column UnitPrice.

Kind regards

Peter Juby

[former_member370721]

I'm sorry, but there is no column 'unitPrice' ?!?

Franz

[Former Member]

Hi Franz,

In the "A" version, "Price" must be same as the "UnitPrice" in "B" version. The reason you have not get the Item Cost is: you have to use another template to reevaluate all item with quantities you uploaded.

The template is: MaterialRevaluation

Thanks,

Gordon

[former_member583013]

If you decided to use oInventoryGenEntry you will need to enter the Cost in the Price column of the Document_Lines template

Let me know which template you have filled.

[former_member370721]

@Suda

I tried to use the oInvertoryGenEntry upon a sugggestion. Basically, I am looking for the easiest way to import such a simple thing as stock quantites and related values. I find it hard to belief that this does seem not to be a wellknown standard prcedure.

<snip>

I don't know what Documents is for ... but its needed for DTW. So I just filled the RecordKey (1, 2, 3 ...) and nothing else. In Document_Lines I filled the RecordKey, Currency, ItemCode, Price, Quantity and WarehouseCode.

I got correct results for the quantity in the correct Warehouse. However, I could not find the Item cost (movingAverage) somewhere after import.

<snip>

@Julie

I already tried StandardAveragePrice in ItemWarehouseInfo1 - > it does not work.

@Gordon

I will check this new suggestion.

btw:

I got the UDF thing working with the ItemWarehouseInfo1 template from object oItems, so the remaining issue is bringing in the Item cost (average price).

Thank you guys

Franz

[Former Member]

Trust me. We went to that long road already. There seems no other way around due to the complicated moving average calculation involved. System has to keep its integrity. No one should be blamed.

Hope you can solve your problem soon.

Gordon

[Former Member]

Hi Franz,

What patch level & DIAPI version are you using? Have you logged this with support? It could be a DI Bug, as the method proposed using Standard averageCost should work.

[former_member370721]

I am using B1 2005A SP01 PL31 client, DTW 2005.0.17, API 6.80.319.

I have not logged this with support.

Status Quo:

- With ItemWarehouseInfo1 I can bring in my UDF, but not cost nor quantities

- With StockTaking1 I can bring in quantities via Inventory/Stockposting (multistep proc.)

- With Document_Lines1 I can bring in the Quantities (directly), but not the cost

- With MaterialRevaluation _lines1 I can bring in the cost but not the quantities

I kinda liked the stocktaking thing because after import you have a mask (StockPosting) to double check and make corrections / addons before hitting reconcile.

It seems that I need to make a combination of these things to reach the target of having Item quantities and cost in my database.

Franz

[Former Member]

Franz,

You can try in your test environment to see how system works by manual entry. You can enter some Goods Receipt with price to see.

Thanks,

Gordon

[Former Member]

Hi Franz

Can I simplify the process by suggesting you use the StkGenEntry template which will bring the stock quantity with prices into the system. It is not necessary to create a price list just to be able to import stock.

Kind regards

Peter Juby

[former_member370721]

Hi Peter,

I very much like the idea of simplifying !

I found the oInventoryGenEntry (could not find StkGenEntry) and tried to use the templates Documents and Documents_Lines. I don't know what Documents is for ... but its needed for DTW. So I just filled the RecordKey (1, 2, 3 ...) and nothing else. In Document_Lines I filled the RecordKey, Currency, ItemCode, Price, Quantity and WarehouseCode.

I got correct results for the quantity in the correct Warehouse. However, I could not find the Item cost (movingAverage) somewhere after import.

Am I doing something wrong?

Tnkas for your continuous support.

Franz

[Former Member]

Hi Franz

You can only update the item cost in the system by using a receipting document such as Goods Receipt P/O, A/P Invoice or Miscellaneous Goods Receipt. You can also use the Initial Stock function to post your opening stock into the system. With other words a combination of quantity and cost will update the moving average on these transactions and nothing else.

In the tables OITW/OITM the average cost is a read only field and you CANNOT and SHOULD not manipulate this as it is calculated by SAP Business One internally.

If you do want to post the quantity without a price, you can use the Material Revaluation function to change the price later and offset this to your opening balance account in the General Ledger, though this is not advisable as you could start trading and end up trying to sell items at no cost.

Kind regards

Peter Juby

[former_member370721]

Initial Stock is what I want to do. I have average Item cost from the old system as well as quantities. So, please correct me if this is wrong:

1. Import Item average cost with ItemsPrices (with oItems)

2. Import quantities with StockTaking (oStocktakings)

3. Run Stock Management/Stock Transactions/Initial Quantities, Stock Tracking and Stock Posting

4. On Tab Initial Quantity:

- Make selections and select Price Source (can I select here to get prices imported before ????)

5. After OK the list is shown (is it filled with the imported values ???), check and confirm with Add

6 Done

Thanks for your help

Franz

[former_member370721]

I need to come back to this:

Importing prices works. It goes into a price list, price list 2 in my case - why ever. I can select the price list and the prices show up in "Initial Quantities, Stock Tracking and Stock Posting".

What I don't get is the quantity thing. I imported StockTakings and for every item in there the 'counted' in Stocktaking is checked now. So, it did "something". However, I don't see the imported quantity, nowhere ?!?

I meant them to be filled in in Initial Quantity together with the prices. There must be a way for this instead of filling in the table Initial Quantities manually line by line.

It cant be so difficult to import a given stock in a warehouse with item cost and quantities, does it?

Thanks

Franz

Edited by: Franz Leu on Jul 30, 2008 2:33 PM

[JesperB1]

Hi Franz,

Depending on the setting in Company Details 'Manage Item Cost per Warehouse' the price should be imported to either OITM (Item cost per Item) or to OITW (Item cost per Warehouse).

I am not sure if this is what is wrong but give it a go.

Hope it helps.

Jesper