I’m trying to build a GI to show current Std Cost, with the Effective Date, and Last Standard Cost with the date of the last cost.
I can’t seem to find the date of the Last Standard Cost. Am I missing a field/table somewhere? I am looking at InventoryItemCurySettings and INItemCost.
Should I just assume that the effective date is the Last Standard Cost Date?
Best answer by Rakshanda
Hi
I think Yes, we can assume the date of the last standard cost from the Pending Cost Date field, because after processing the Update Standard Cost, this field is updated with the new pending cost's date. we don't have specific Last Standard Cost
Hope this helps!!
Or ideally, I see the Std cost at the start of the year, the end of the year, and the date of the change.
Hi
I think Yes, we can assume the date of the last standard cost from the Pending Cost Date field, because after processing the Update Standard Cost, this field is updated with the new pending cost's date. we don't have specific Last Standard Cost
Hope this helps!!
I ran the data and it returned the last update from the Audit.
select InventoryItemCurySettings.laststdcost,
InventoryItemCurySettings.stdcost as currentcost,
InventoryItemCurySettings.StdCostDate as lastupdate,
InventoryItemCurySettings.pendingstdcost,
InventoryItemCurySettings.PendingStdCostDate
from InventoryItem
join InventoryItemCurySettings
on InventoryItemCurySettings.InventoryID = InventoryItem.InventoryID
and InventoryItemCurySettings.CompanyID = InventoryItem.CompanyID
/* where InventoryItem.inventorycd = 'xxxxxxx' -- TEST SKU
and InventoryItem.companyid = 2 */laststdcost currentcost lastupdate pendingstdcost PendingStdCostDate
2.248700 2.392930 2024-01-27 00:00 0.000000 NULL
If you have InventoryItem auditing enabled you could pull a Generic Inquiry off the Audit.
select InventoryItemCurySettings.laststdcost,
max(AuditHistory.ChangeDate) as previousdate,
InventoryItemCurySettings.stdcost as currentcost,
InventoryItemCurySettings.StdCostDate as lastupdate,
InventoryItemCurySettings.pendingstdcost,
InventoryItemCurySettings.PendingStdCostDate
from InventoryItem
join InventoryItemCurySettings
on InventoryItemCurySettings.InventoryID = InventoryItem.InventoryID
and InventoryItemCurySettings.CompanyID = InventoryItem.CompanyID
left
join AuditHistory
on AuditHistory.ScreenID = 'IN202500'
and AuditHistory.TableName = 'InventoryItem'
and AuditHistory.ModifiedFields like 'PendingStdCost%'
and AuditHistory.CombinedKey = InventoryItem.InventoryCD
and AuditHistory.CompanyID = InventoryItem.CompanyID
/* where InventoryItem.inventorycd = 'xxxxxx' -- TEST SKU
and InventoryItem.companyid = 2 */
group by InventoryItemCurySettings.laststdcost,
InventoryItemCurySettings.stdcost,
InventoryItemCurySettings.StdCostDate,
InventoryItemCurySettings.pendingstdcost,
InventoryItemCurySettings.PendingStdCostDate
laststdcost previousdate currentcost lastupdate pendingstdcost PendingStdCostDate
2.248700 2024-01-27 22:47 2.392930 2024-01-27 00:00 0.000000 NULL
You could do some logic where you track years on change date, etc.
ChangeID ScreenID UserID ChangeDate
40338494 IN202500 56DED2C7-EB6E-4D14-8D01-241D0383BE0D 2024-01-27 22:47:26.990
40279636 IN202500 56DED2C7-EB6E-4D14-8D01-241D0383BE0D 2024-01-27 03:02:03.330
38706294 IN202500 56DED2C7-EB6E-4D14-8D01-241D0383BE0D 2023-12-11 20:08:58.320
38657133 IN202500 56DED2C7-EB6E-4D14-8D01-241D0383BE0D 2023-12-11 19:38:29.993
38142020 IN202500 56DED2C7-EB6E-4D14-8D01-241D0383BE0D 2023-11-29 19:46:56.820
22242860 IN202500 56DED2C7-EB6E-4D14-8D01-241D0383BE0D 2022-10-22 06:26:41.273
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.