Current Costs By Site

DS0796

Item Current Cost by Site

Problem Definition

ACME Co utilizes Dynamics GP with a multi-Site environment. Dynamics GP stores each Item’s Current Cost at the Company level. ACME Co requires the ability to track an Item’s Current Cost by Site ID.

Design Features

Current Cost by Item/Site

There is no user interface for this enhancement.

A SQL table (WxxxxCurrentCost) will be provided that will store the following detail:

  1. Item Number
  2. Site ID
  3. Current Cost

Each time a “receipt” transaction is posted in Dynamics GP for an Item Number/Site ID AND that “receipt transaction” updates the Current Cost of the Item, the SQL table will be updated for the Item Number/Site ID combination.

Some examples of a “receipt” transaction are:

  1. Positive Inventory Adjustment
  2. Purchase Order Receipt
  3. Purchase Order Invoicing
  4. Manufacturing Order Receipt

Please Note:

  1. The exact SQL table name will be provided with the enhancement’s instructions when the completed enhancement is delivered.
  2. New records will not be added into the SQL table for an Item Number/Site ID combination until a receipt transaction for the Item Number/Site combination has been posted.

Auto-populate WxxxxCurrentCost on Install

There is no user-interface for this portion of the enhancement.

When the enhancement is installed AND the WxxxxCurrentCost SQL table is empty, the enhancement will automatically populate the WxxxxCurrentCost table utilizing records from the IV10200 (Inventory Purchase Receipts table).

The enhancement will find the most recent record in the IV10200 table per Item Number/Site Combination. It will then insert one record per Item Number/Site into the WxxxxCurrentCost table using the following:

  • Item Number: IV10200.ITEMNMBR
  • Site ID: IV10200.TRXLOCTN
  • Current Cost: IV10200.UNITCOST

Please Note: The IV10200 table can be impacted by GP’s rounding principal. For instance, one Receipt of an Item Number can be broken into 2 separate records. For example:

An Item Number has its Currency Decimals set to 2 places. It has a Unit of Measure Schedule with a base UOM of “Each” and the following conversion:

  • Each = 1 Each
  • Case = 6 Each

A PO Line is placed for a quantity of 1 Case with a Unit Cost of $50.00. This would equal a Unit Cost per Each of $8.33333. Since the Currency Decimals of the Item is only set to 2 places when the PO is received, GP will add 2 records into the IV10200 table (the 2nd record is always for a quantity of 1 and will receive the rounding amount).

  1. Quantity of 5 Each – Unit Cost $8.33
  2. Quantity of 1 Each – Unit Cost $8.35

To mitigate the likelihood that the record has been impacted by GP’s rounding principal, the enhancement will utilize the following approach in determining the most recent Item Number/Site/Unit Cost record:

  1. Find the most recent record for the Item Number/Site combination.
  2. If the Quantity Received is NOT equal to 1, the Unit Cost from that record will be used.
  3. If the Quantity Received is equal to 1, the enhancement will check to see if another record exists in the IV10200 table for the same Item Number, Site ID, Receipt Date and Receipt Number.
  4. If another record exists, the Unit Cost from that record will be used.

If another record does NOT exist, the Unit Cost from the original record will be used.