Blog

Our latest posts and rants

Dynamics GP logo

Calculating New Assembly Costs from Existing Component Costs

We recently had to calculate standard costs for some new assemblies based on standard or actual costs for their components.

Rodin's sculpture The Thinker

I made a recursive view in Microsoft SQL Server that traverses the bill of material tree and calculates the total assembly cost.

Be warned, although I’ve included code to do unit conversions, our application did not require any conversions, and that code has not been tested. Use at your own risk.

So here’s the script:

CREATE FUNCTION [dbo].[SSC_BomCost]
(
	@PartNumber char(31)
)
RETURNS @TheResult
TABLE 
(
	OriginalItemNmbr char(31),
	Cost numeric(19,5)
)
AS
BEGIN

with

-- Here is the base item cost and unit of measure information
-- We use current cost if present, otherwise standard cost

ItemCost as (
  select
    i.itemnmbr,
	case when i.currcost>0 then i.currcost when i.stndcost>0 then i.stndcost else 0 end as Cost,
	i.SELNGUOM as UOFM,
	i.UOMSCHDL
  from dbo.IV00101 i
)
,

-- Here we recurse down the bill of material tree until we find non-zero costs, 
-- or hit leaf nodes, whichever comes first on each path

ComponentTree as (
  select
    c.itemnmbr,
	c.Cost,
	cast(d.EQUOMQTY as numeric(19,5)) as Quantity,
	cast(c.UOFM as char(9)) as UOFM,
	c.UOMSCHDL
  from ItemCost c
  join dbo.BM00101 b
  on c.itemnmbr=b.itemnmbr
  join dbo.IV40202 d
  on d.UOMSCHDL=c.UOMSCHDL
  and d.UOFM=c.UOFM
  and d.EQUIVUOM=b.UOFM
  where c.itemnmbr=@PartNumber
    
  union all
  
  select 
	cast(b.cmptitnm as char(31)),
	c.Cost,
	cast(b.design_qty * d.EQUOMQTY as numeric(19,5)) as Quantity,
	cast(b.UOFM as char(9)) as UOFM,
	c.UOMSCHDL
  from ComponentTree a
  join bm00111 b
  on a.itemnmbr=b.itemnmbr
  join ItemCost c
  on b.cmptitnm=c.itemnmbr
  join IV40202 d
  on d.UOMSCHDL=c.UOMSCHDL
  and d.UOFM=c.UOFM
  and d.EQUIVUOM=b.UOFM
  where a.Cost=0
)
,


-- Here we add all the component costs

bom as (
  select
    cast(@PartNumber as char(31)) as OriginalItemNmbr,
    sum(Quantity * Cost) as Cost
  from ComponentTree
)


-- And return the result

insert into @TheResult
  select 
    OriginalItemNmbr,
	Cost
  from bom
;

return;

end;

And here’s how I invoked it for our purpose. All the new assemblies began with “6-“.

select c.*
from dbo.bm00101 b
cross apply dbo.SSC_BomCost(b.itemnmbr) c
where b.ITEMNMBR like '6-%'
and b.ITEMNMBR in (
  select ITEMNMBR
  from dbo.iv00101
  where ITEMTYPE=1
  and CURRCOST=0
  and STNDCOST=0)
order by 1;

Here’s some sample output. I’ve replaced the calculated costs with random numbers in this listing.

Two columns, part number and cost

Sample output (costs random)

Hope this helps someone else!

Dynamics GP logo

Database Query Generates Macro to Assign Dynamics GP Inventory Sites

Here’s a follow-up to a previous post Programatically Construct a Dynamics GP Macro to Disassociate Items from an Inventory Site.

This time we assign a new inventory site to selected items within GP. Once again we use a SQL query to generate a GP macro that does all the tedious repetitive work.

First I did one assignment while recording it as a macro. The macro looks like this.


# DEXVERSION=12.00.0270.000 2 2
CommandExec dictionary 'default' form 'Command_Inventory' command 'IV_Item_Qtys_Locs'
NewActiveWin dictionary 'default' form 'IV_Item_Qtys_Locs' window 'IV_Item_Qtys_Locs'
TypeTo field 'Item Number' , '6-037-31050SLT-EA'
MoveTo field '(L) Display Options' item 0 # 'All'
ClickHit field '(L) Display Options' item 1 # 'Site ID:'
TypeTo field 'Location Code' , 'MAIN'
MoveTo field 'Save Button'
ClickHit field 'Save Button'
CloseWindow dictionary 'default' form 'IV_Item_Qtys_Locs' window 'IV_Item_Qtys_Locs'
NewActiveWin dictionary 'default' form sheLL window sheLL

Then I constructed a GP query to locate all the items I wished to assign to MAIN. In my case the query looked like this:

select itemnmbr
from iv00101
where itemtype=1
and itemnmbr not in (
select itemnmbr from iv00102
where locncode='MAIN')
and itemnmbr like '6-%'

6-% is a particular class of items for us. ITEMTYPE=1 limits the results to active inventory items. The other clause in the WHERE part of the query finds items that are not assigned to the MAIN inventory site.

Then, I combined my query with the macro as so:

with VariableData as (
select itemnmbr
from iv00101
where itemtype=1
and itemnmbr not in (
select itemnmbr from iv00102
where locncode='MAIN')
and itemnmbr like '6-%'
)

select 
  '# DEXVERSION=12.00.0270.000 2 2' as MacroLine,
  -1 as MacroLineNumber,
  '' as itemnmbr

union all

select
  '  CommandExec dictionary ''default''  form ''Command_Inventory'' command ''IV_Item_Qtys_Locs''' as MacroLine,
  0 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  'NewActiveWin dictionary ''default''  form ''IV_Item_Qtys_Locs'' window ''IV_Item_Qtys_Locs''' as MacroLine,
  1 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  '  TypeTo field ''Item Number'' , ''' + rtrim(itemnmbr) + '''' as MacroLine,
  2 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  '  MoveTo field ''(L) Display Options'' item 0  # ''All'' ' as MacroLine,
  3 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  '  ClickHit field ''(L) Display Options'' item 1  # ''Site ID:'' ' as MacroLine,
  4 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  '  TypeTo field ''Location Code'' , ''MAIN''' as MacroLine,
  5 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  '  MoveTo field ''Save Button'' ' as MacroLine,
  6 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  '  ClickHit field ''Save Button'' ' as MacroLine,
  7 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  'CloseWindow dictionary ''default''  form ''IV_Item_Qtys_Locs'' window ''IV_Item_Qtys_Locs'' ' as MacroLine,
  8 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  'NewActiveWin dictionary ''default''  form sheLL window sheLL ' as MacroLine,
  9 as MacroLineNumber,
  itemnmbr
from VariableData

order by itemnmbr,macrolinenumber

As you see, each line of the macro is mapped to a UNION clause in the query. The result set looks like this (I’ll only include 2 item numbers to keep it brief):

Result Set

Result Set

Now save only the first column in a new .mac file:


# DEXVERSION=12.00.0270.000 2 2
CommandExec dictionary 'default' form 'Command_Inventory' command 'IV_Item_Qtys_Locs'
NewActiveWin dictionary 'default' form 'IV_Item_Qtys_Locs' window 'IV_Item_Qtys_Locs'
TypeTo field 'Item Number' , '6-037-31050THR-EA'
MoveTo field '(L) Display Options' item 0 # 'All'
ClickHit field '(L) Display Options' item 1 # 'Site ID:'
TypeTo field 'Location Code' , 'MAIN'
MoveTo field 'Save Button'
ClickHit field 'Save Button'
CloseWindow dictionary 'default' form 'IV_Item_Qtys_Locs' window 'IV_Item_Qtys_Locs'
NewActiveWin dictionary 'default' form sheLL window sheLL
CommandExec dictionary 'default' form 'Command_Inventory' command 'IV_Item_Qtys_Locs'
NewActiveWin dictionary 'default' form 'IV_Item_Qtys_Locs' window 'IV_Item_Qtys_Locs'
TypeTo field 'Item Number' , '6-037-31075R-EA'
MoveTo field '(L) Display Options' item 0 # 'All'
ClickHit field '(L) Display Options' item 1 # 'Site ID:'
TypeTo field 'Location Code' , 'MAIN'
MoveTo field 'Save Button'
ClickHit field 'Save Button'
CloseWindow dictionary 'default' form 'IV_Item_Qtys_Locs' window 'IV_Item_Qtys_Locs'

Run that .mac file as a GP macro, and watch as a lot of tedious work gets done in a few seconds.

Action

Hope this is helpful!

SQL Server

Quick Check for SQL Server Database Recovery Models

If you are like us, you sometimes change your database recovery models depending on your backup method and the volatility of the data in each database.

Once in a while we’ll find that we’ve forgotten to change one.

Here’s a quick query to show you the recovery models for each database in an instance:

select name, recovery_model_desc
from master.sys.databases
order by name;

The result looks like this:

A 2 column table showing database name and recovery model

A sample query result

Not rocket science but maybe it will save someone a few minutes.

Hope this is helpful!

Lucidchart Logo

Lucidchart is a Handy Online Diagramming Tool

I was pleasantly surprised recently when I had to draw some simple diagrams. I came across Lucidchart. It is a pleasure to work with.

I have a pretty extensive CAD background, so my expectations are somewhat high. For a web app still in beta, Lucidchart does a good job. The free level was more than enough to do the charts I needed.

A sample chart

A sample from Lucidchart.com

Their web site is at https://www.lucidchart.com/

I did have an issue with the site freezing at one point. The web app asked me to open a problem ticket, which I did. A short while later I had a response, saying the website had become unresponsive but was now fixed. Attached to the reply was a copy of the PDF file I had been tying to generate. That’s good support for a free beta product.

This is my own personal opinion. I have no connection with Lucidchart. I just enjoy working with good tools and thought I’d pass along news of this one.

Hope this information is helpful!

Dynamics GP logo

Problem with Dynamics GP Primary Vendor Designation

We’ve discovered that out of thousands of items, a few dozen in our GP data have a primary vendor identified in the IV00102 table but not in the IV00103 table for the same item and inventory site.

We report some purchasing thresholds from the IV00103 table. This problem affects those reports.

A worried person

Oh no!

Our fix is to assign the primary vendor to a different vendor on the Item Quantities/Site card, save it, then assign it back to the correct vendor and save it again. This corrects the IV00103 table.

A happy chef

Everything worked out.

Here’s a query that we used to find which parts had this problem:

Sherlock Holmes

The game is afoot, Watson!

select 
  locn.itemnmbr,
  current_timestamp,
  'Site ' + RTRIM(locn.locncode) + 
    ' primary vendor mismatch between IV00102 and IV00103'
from (select * from iv00102 where locncode='MAIN' and primvndr<>'') locn
left join (select * from iv00103 where itmvndty=1) vend
on locn.itemnmbr=vend.itemnmbr
and locn.primvndr=vend.vendorid
where vend.vendorid is null
order by locn.itemnmbr
;

Hope this helps someone!