Microsoft.com Home
Welcome Guest | Join Now! | Help | Sign in

Update Standard Costs from Pending 11.530.00 Error

Last post 09-24-2008 10:42 AM by Gail J-N. 5 replies.
Page 1 of 1 (6 items)
Sort Posts: Previous Next

  • 08-28-2008 6:47 AM

    Update Standard Costs from Pending 11.530.00 Error

    Reply Contact

     Okay, So I run all the prerequisite update the pending costs in BOM's and run the Compute Cost roll Up but when I run the Update Std costs from pending , I get the error message that no BOM's have matche the siteid etc etc. This used to work fine in SOLIV 2.06 now it just does not cooperate , I have been updating the pending to std costs via SQL but I would like to know if there is an update to the screen. I think there was a BUG somewhere. The only BOM's that show up in the grid are BOM's with a STD COST valuation I know it's something with that because most of our BOM's have an AVG cost valuation method.

  • 09-15-2008 6:19 AM In reply to

    Re: Update Standard Costs from Pending 11.530.00 Error

    Reply Contact

    What version are you on now?   We have run into a number of problems with updating pending costs into standard on 11.530, but they don't quite match your problem.

    In current Dynamics-SL, BOM is site specific, which means that the BOM tables Kit & Component must have a siteid populated at both levels.   At first glance it sounds like there's a data problem somewhere in matching up site ID's.

    Also, rolling costs in both Kits & BOM's requires that the item/site combination for every component and every KitID must exist, or the cost will not roll up.  

     Finally, on the itemsite record, in order to "roll over" from pending to current, the standard cost date must be populated with a date <= [today's date].

     We also had some issues in 6.5 and 5.5 where the batch would create with a batch status of "C" but didn't actually post; we used to have to go into the batch record, reset it to "S"uspended, and then re-release the batch when it would post.

    I hope one of these ideas is useful.

     Gail J-N

  • 09-15-2008 10:05 AM In reply to

    Re: Update Standard Costs from Pending 11.530.00 Error

    Reply Contact

    We are running 6.50.3050 sp2

    I checked all the kit & component tables and they all have our siteid value of MCG (that is the siteid we use and was converted from our old SOL4 2.06 database to the new 6.5 version. I checked out my itemsite table and it seems the standard cost data value is 01/01/1900 some default value from dynamics probably because the conversion did not bring this field into the new database.

    Queries: If I populate all the 1/1/1900 values with a value of say 09/30/2007 (Date of our conversion from sol4 then re run all the roll up and cost processes, do you think this will correct the problem in 11.530.00

    I haven'r run into any Batch issue problems but then again i haven't been able to run the rollups yet.

    thanks

    kevin

     

  • 09-15-2008 12:17 PM In reply to

    Re: Update Standard Costs from Pending 11.530.00 Error

    Reply Contact

    By all means, go ahead and populate missing standard cost dates.    We have a nightly job that checks if the standard cost date is missing for itemsite records with a pending cost, and populates the standard cost date field if that error condition exists.    The standard cost date field is a little funky so we turned off making that field "required"; the SQL query was our work around.

    Once you populate the standard cost date field and are sure that all of your item/site cost records exist, you will definitely need to reroll your standard costs (up).  (11.540)

    Here's another "gotcha":   The generic standard cost rollup works differently for kits than for BOM's, and I don't know if that's how 2.06 worked.   In D-SL, kits roll up based on current standard cost fields.   That means that raw material costs must be rolled over (10.530) before the rollup.

    In BOM, BOM's roll up based on pending costs.   That means that pending costs MUST be populated for all of your raw materials, even those not getting a new cost - otherwise the cost is treated as zero.   In BOM's, you'll need to rollover the raw material costs (10.530) after the rollup of the BOM.

    [What we did to deal with this is that we updated a couple of D-SL views so that both Kits & BOM's use the same logic for calculating "pending cost" - and both do it same way; we calculate a pending cost as [if itemsite.pstdcst <> 0 then pstdcst else stdcost].   We used that same logic for all components of cost (direct, fixed, variable).    What that has allowed us to do is to run both inventory kit assemblies as well as true BOM's in our environment (our FG are kits and our subassemblies are BOM's), and the process works well now.]

    In 6.5, we occasionally had a problem where the cost calculated in the BOM cost rollup was not copied correctly from the kit table to the itemsite table.   We have an hourly query that checks for this (see below).    This problem is fixed in V7.0.

    Also, be aware that it is possible to rollover costs calculated in the BOM from the Kit screen!  Thus it matters exactly where in the sequence you run 10.530.   Running it at the wrong time can cause pending costs to show up in the BOM pending cost fields on the standard cost tab, while the pending costs in the itemsite tab are 0.

    BTW:   You might also want to check the BOM Setup screen to make sure that the "Site Information" tab is check correctly.    Since you only have one site, you might try unchecking that field (in a test database of course).   We don't  run that way so I haven't tested it; what it should do is to force pulling costs from the inventory master instead of the itemsite record.

    Hope this helps.   The comparison of BOM pending costs on the kit table vs. itemsite table follows.

    Best regards,

    Gail J-N

    Declare @Exist Integer

    set @exist = (select count(*) from

    (

    select left(KitID,7) as InvtID, SiteID, ProdSite,

    Convert(char(12),CstdCst,6) AS KitStdCst,

    Convert(char(12),StdCost,6) as ITStdCst,

    Convert(char(12),CalcStdCost,6) as CalcStdCost,

    Convert(char(12),KitPStdCst,6) as KitPStdCst,

    Convert(char(12),PstdCst,6) as ITPStdCst,

    Convert(char(12),PCalcStdCost,6) as PCalcStdCost

    from (

    select kitid,kit.siteid, xinventory.dfltprodsite as ProdSite,

    CDirMatlCst, kit.cdirlbrcst, CDirOthCst, itemsite.dirstdcst,

    CFOvhLbrCst, CFOvhMachCst, CFovhMatlCst,

    (CFovhLbrCst + CFOvhMachCst + CFovhMatlCst) as CalcFOvhStdCst, itemsite.FovhStdCst,

    CVOvhLbrCst, CVovhMachCst, CVovhMatlCst,

    (CVovhLbrCst + CVOvhMachCst + CVovhMatlCst) as CalcVOvhStdCst,

    itemsite.VovhStdCst,

    CstdCst, itemsite.StdCost, itemsite.dirstdcst + itemsite.fovhstdcst + itemsite.vovhstdcst as CalcStdCost,

    PDirMatlCst, kit.pdirlbrcst, PDirOthCst, itemsite.pdirstdcst,

    PFOvhLbrCst, PFOvhMachCst, PFovhMatlCst,

    (PFovhLbrCst + PFOvhMachCst + PFovhMatlCst) as PCalcFOvhStdCst, itemsite.PFovhStdCst,

    PVOvhLbrCst, PVovhMachCst, PVovhMatlCst,

    (PVovhLbrCst + PVOvhMachCst + PVovhMatlCst) as PCalcVOvhStdCst,

    itemsite.PVovhStdCst,

    kit.PstdCst as KitPStdCst, itemsite.PStdCst, itemsite.pdirstdcst + itemsite.pfovhstdcst + itemsite.pvovhstdcst as PCalcStdCost

    from DBNameapp.dbo.kit kit (nolock)

    inner loop join DBNameapp.dbo.inventory inventory (nolock) on kit.kitid = inventory.invtid

    inner loop join DBNameapp.dbo.Xinventory xinventory (nolock) on kit.kitid = xinventory.invtid

    inner join DBNameapp.dbo.itemsite itemsite (nolock) on kit.kitid = itemsite.invtid and kit.siteid = itemsite.siteid

    where inventory.transtatuscode <> 'IN' and kit.status = 'A' and kittype = 'B') S1

    where (((abs(CalcFovhStdCst - FovhStdCst) > .0001

    or abs(CalcVovhStdCst - VovhStdCst) > .0001

    or abs(calcstdcost - StdCost) > .0001)

    and CstdCst <> 0)

    or ( (abs(pCalcFovhStdCst - pFovhStdCst) > .0001

    or abs(pCalcVovhStdCst - pVovhStdCst) > .0001

    or abs(pcalcstdcost - pStdCst) > .0001)

    and pStdCst <> 0))

    and siteid = prodsite

    ) S1 )

    If @exist >0

    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLPROFILENAME',

    @recipients = 'username@DBName.com',

    @query = 'select left(KitID,21) as InvtID, SiteID, ProdSite,

    Convert(char(12),CstdCst,6) AS KitStdCst,

    Convert(char(12),StdCost,6) as ITStdCst,

    Convert(char(12),CalcStdCost,6) as CalcStdCost,

    Convert(char(12),KitPStdCst,6) as KitPStdCst,

    Convert(char(12),PstdCst,6) as ITPStdCst,

    Convert(char(12),PCalcStdCost,6) as PCalcStdCost

    from (

    select kitid,kit.siteid, xinventory.dfltprodsite as ProdSite,

    CDirMatlCst, kit.cdirlbrcst, CDirOthCst, itemsite.dirstdcst,

    CFOvhLbrCst, CFOvhMachCst, CFovhMatlCst,

    (CFovhLbrCst + CFOvhMachCst + CFovhMatlCst) as CalcFOvhStdCst, itemsite.FovhStdCst,

    CVOvhLbrCst, CVovhMachCst, CVovhMatlCst,

    (CVovhLbrCst + CVOvhMachCst + CVovhMatlCst) as CalcVOvhStdCst,

    itemsite.VovhStdCst,

    CstdCst, itemsite.StdCost, itemsite.dirstdcst + itemsite.fovhstdcst + itemsite.vovhstdcst as CalcStdCost,

    PDirMatlCst, kit.pdirlbrcst, PDirOthCst, itemsite.pdirstdcst,

    PFOvhLbrCst, PFOvhMachCst, PFovhMatlCst,

    (PFovhLbrCst + PFOvhMachCst + PFovhMatlCst) as PCalcFOvhStdCst, itemsite.PFovhStdCst,

    PVOvhLbrCst, PVovhMachCst, PVovhMatlCst,

    (PVovhLbrCst + PVOvhMachCst + PVovhMatlCst) as PCalcVOvhStdCst,

    itemsite.PVovhStdCst,

    kit.PstdCst as KitPStdCst, itemsite.PStdCst, itemsite.pdirstdcst + itemsite.pfovhstdcst + itemsite.pvovhstdcst as PCalcStdCost

    from DBNameapp.dbo.kit kit (nolock)

    inner loop join DBNAME.dbo.inventory inventory (nolock) on kit.kitid = inventory.invtid

    inner loop join DBNameapp.dbo.Xinventory xinventory (nolock) on kit.kitid = xinventory.invtid

    inner join DBNameapp.dbo.itemsite itemsite (nolock) on kit.kitid = itemsite.invtid and kit.siteid = itemsite.siteid

    where inventory.transtatuscode <> ''IN'' and kit.status = ''A'' and kittype = ''B'') S1

    where (((abs(CalcFovhStdCst - FovhStdCst) > .0001

    or abs(CalcVovhStdCst - VovhStdCst) > .0001

    or abs(calcstdcost - StdCost) > .0001)

    and CstdCst <> 0)

    or ( (abs(pCalcFovhStdCst - pFovhStdCst) > .0001

    or abs(pCalcVovhStdCst - pVovhStdCst) > .0001

    or abs(pcalcstdcost - pStdCst) > .0001)

    and pStdCst <> 0))

    and siteid = prodsite

    ',

    @subject = 'BOM Cost Rollup calculated incorrectly.',

    @body = 'The BOM has incorrectly transferred a cost from the Kit record to the Itemsite Record.

    The itemsite pending cost or current cost does not match the amount of the BOM components.

    Please correct ASAP. '    ,

    @query_result_width =125,

    @attach_query_result_as_file = false

     

     

  • 09-24-2008 10:25 AM In reply to

    Re: Update Standard Costs from Pending 11.530.00 Error

    Reply Contact

    In the old 2.06 days I used to have to runsort of the same thing nightly scripts to fix the missing costs. 

    I reset all the dates but something is still amiss, In your comparison sql script i see that table "Xinventory" we do not use that anywhere in our APP DB is this from some customization.

    Thanks

    Kevin

  • 09-24-2008 10:42 AM In reply to

    Re: Update Standard Costs from Pending 11.530.00 Error

    Reply Contact

    Sorry about that!   Yes, for us the custom table XInventory carries a "ProdSite" field that identifies the primary production location, and we only look at BOM's from the primary production location.

    Removing the XInventory & ProdSite references has no impact on the validity of the query.

     SQL code follows. 

    Best regards,

    Gail J-N

    Declare @Exist Integer

    set @exist = (select count(*) from

    (

    select left(KitID,7) as InvtID, SiteID,

    Convert(char(12),CstdCst,6) AS KitStdCst,

    Convert(char(12),StdCost,6) as ITStdCst,

    Convert(char(12),CalcStdCost,6) as CalcStdCost,

    Convert(char(12),KitPStdCst,6) as KitPStdCst,

    Convert(char(12),PstdCst,6) as ITPStdCst,

    Convert(char(12),PCalcStdCost,6) as PCalcStdCost

    from ( select kitid,kit.siteid, CDirMatlCst, kit.cdirlbrcst, CDirOthCst, itemsite.dirstdcst,

    CFOvhLbrCst, CFOvhMachCst, CFovhMatlCst, (CFovhLbrCst + CFOvhMachCst + CFovhMatlCst) as CalcFOvhStdCst,

    itemsite.FovhStdCst, CVOvhLbrCst, CVovhMachCst, CVovhMatlCst, (CVovhLbrCst + CVOvhMachCst + CVovhMatlCst) as CalcVOvhStdCst,

    itemsite.VovhStdCst, CstdCst, itemsite.StdCost, itemsite.dirstdcst + itemsite.fovhstdcst + itemsite.vovhstdcst as CalcStdCost,

    PDirMatlCst, kit.pdirlbrcst, PDirOthCst, itemsite.pdirstdcst, PFOvhLbrCst, PFOvhMachCst, PFovhMatlCst,

    (PFovhLbrCst + PFOvhMachCst + PFovhMatlCst) as PCalcFOvhStdCst, itemsite.PFovhStdCst,

    PVOvhLbrCst, PVovhMachCst, PVovhMatlCst, (PVovhLbrCst + PVOvhMachCst + PVovhMatlCst) as PCalcVOvhStdCst,

    itemsite.PVovhStdCst, kit.PstdCst as KitPStdCst, itemsite.PStdCst, itemsite.pdirstdcst + itemsite.pfovhstdcst + itemsite.pvovhstdcst as PCalcStdCost

    from kit (nolock)

    inner loop join inventory (nolock) on kit.kitid = inventory.invtid

    inner join itemsite (nolock) on kit.kitid = itemsite.invtid and kit.siteid = itemsite.siteid

    where inventory.transtatuscode <> 'IN' and kit.status = 'A' and kittype = 'B') S1

    where (((abs(CalcFovhStdCst - FovhStdCst) > .0001

    or abs(CalcVovhStdCst - VovhStdCst) > .0001

    or abs(calcstdcost - StdCost) > .0001)

    and CstdCst <> 0)

    or ( (abs(pCalcFovhStdCst - pFovhStdCst) > .0001

    or abs(pCalcVovhStdCst - pVovhStdCst) > .0001

    or abs(pcalcstdcost - pStdCst) > .0001)

    and pStdCst <> 0))

    ) S1 )

    If @exist >0

    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLPROFILENAME',

    @recipients = 'username@DBName.com',

    @query = '

    select left(KitID,7) as InvtID, SiteID,

    Convert(char(12),CstdCst,6) AS KitStdCst,

    Convert(char(12),StdCost,6) as ITStdCst,

    Convert(char(12),CalcStdCost,6) as CalcStdCost,

    Convert(char(12),KitPStdCst,6) as KitPStdCst,

    Convert(char(12),PstdCst,6) as ITPStdCst,

    Convert(char(12),PCalcStdCost,6) as PCalcStdCost

    from ( select kitid,kit.siteid, CDirMatlCst, kit.cdirlbrcst, CDirOthCst, itemsite.dirstdcst,

    CFOvhLbrCst, CFOvhMachCst, CFovhMatlCst, (CFovhLbrCst + CFOvhMachCst + CFovhMatlCst) as CalcFOvhStdCst,

    itemsite.FovhStdCst, CVOvhLbrCst, CVovhMachCst, CVovhMatlCst, (CVovhLbrCst + CVOvhMachCst + CVovhMatlCst) as CalcVOvhStdCst,

    itemsite.VovhStdCst, CstdCst, itemsite.StdCost, itemsite.dirstdcst + itemsite.fovhstdcst + itemsite.vovhstdcst as CalcStdCost,

    PDirMatlCst, kit.pdirlbrcst, PDirOthCst, itemsite.pdirstdcst, PFOvhLbrCst, PFOvhMachCst, PFovhMatlCst,

    (PFovhLbrCst + PFOvhMachCst + PFovhMatlCst) as PCalcFOvhStdCst, itemsite.PFovhStdCst,

    PVOvhLbrCst, PVovhMachCst, PVovhMatlCst, (PVovhLbrCst + PVOvhMachCst + PVovhMatlCst) as PCalcVOvhStdCst,

    itemsite.PVovhStdCst, kit.PstdCst as KitPStdCst, itemsite.PStdCst, itemsite.pdirstdcst + itemsite.pfovhstdcst + itemsite.pvovhstdcst as PCalcStdCost

    from dbname.dbo.kit kit (nolock)

    inner loop join dbname.dbo.inventory inventory (nolock) on kit.kitid = inventory.invtid

    inner join dbname.dbo.itemsite (nolock) on kit.kitid = itemsite.invtid and kit.siteid = itemsite.siteid

    where inventory.transtatuscode <> ''IN'' and kit.status = ''A'' and kittype = ''B'') S1

    where (((abs(CalcFovhStdCst - FovhStdCst) > .0001

    or abs(CalcVovhStdCst - VovhStdCst) > .0001

    or abs(calcstdcost - StdCost) > .0001)

    and CstdCst <> 0)

    or ( (abs(pCalcFovhStdCst - pFovhStdCst) > .0001

    or abs(pCalcVovhStdCst - pVovhStdCst) > .0001

    or abs(pcalcstdcost - pStdCst) > .0001)

    and pStdCst <> 0))

    ) S1 )

    ',

    @subject = 'BOM Cost Rollup calculated incorrectly.',

    @body = 'The BOM has incorrectly transferred a cost from the Kit record to the Itemsite Record.

    The itemsite pending cost or current cost does not match the amount of the BOM components.

    Please correct ASAP. '    ,

    @query_result_width =125,

    @attach_query_result_as_file = false
Page 1 of 1 (6 items)
© 2008 Microsoft Corporation Code of Conduct | Terms of Use | Trademarks | Privacy Statement | Contact Us