This is the SQL i used to analyze inventory during the counting process.
The frozen values store on the CCtag table.
SELECT
cctag.WarehouseCode,
cctag.PartNum,
p.PartDescription,
p.IUM,
cctag.BinNum,
cctag.TagNum,
cctag.CountedBy,
cctag.FrozenQOH,
cctag.FrozenCost,
cctag.TagReturned,
cctag.FrozenCost * cctag.FrozenQOH AS SnapValue,
cctag.CountedQty,
cctag.CountedQty * cctag.FrozenCost AS CountedValue,
(cctag.CountedQty - cctag.FrozenQOH) AS QtyVariance,
(
CAST(cctag.CountedQty - cctag.FrozenQOH AS decimal(18,6))
/ NULLIF(CAST(cctag.FrozenQOH AS decimal(18,6)), 0)
) * 100 AS VarPercent, -- returns NULL when FrozenQOH = 0
(cctag.CountedQty - cctag.FrozenQOH) * cctag.FrozenCost AS VarValue,
ABS((cctag.CountedQty - cctag.FrozenQOH) * cctag.FrozenCost) AS ABSVarValue,
pw.SystemAbc,
cctag.BlankTag,
cctag.CycleSeq
FROM erp.CCTag AS cctag
LEFT JOIN erp.Part AS p
ON p.PartNum = cctag.PartNum
LEFT JOIN erp.PartWhse AS pw
ON pw.PartNum = cctag.PartNum
AND pw.WarehouseCode = cctag.WarehouseCode
WHERE cctag.FullPhysical = 1
AND CCYear = YEAR(GETDATE())
AND cctag.TagReturned = 1
ORDER BY cctag.PartNum;
-- Optional filters:
-- AND cctag.WarehouseCode = ' '
Yep. Fun, right? Well done in figuring this out already. It was years before I pieced that together. And as a result, I did what you said and made a dashboard called “Serial Number Reconcile.” And people sometimes use it briefly! But mostly they find me to be annoying and ignore that report because, you know, it takes work to go figure out which 19 of the 20 serial numbers are valid before the inventory/cycle. And then the get the variance and ask why, etc. For you, at least it is the opposite - you only need to check one item.
Also, tell no one this, but frozen qty can be edited. Why? No idea and it should not be. I think in Kinetic screens you cannot edit it. But the classic ones let you, if you unhid the column. I still never edited it, and even for your serial issue, I do not think you could avoid it.
When we do hit the issue of too many serial tags, I tell them to falsely report it all as being there, and then we’ll fix the serial numbers afterward.
Or if you want to be slightly less sneaky, put the count as zero and an “activity before count” of -1, and this will give it no variance.
So the variance formula is Count - (freeze + activity) = variance
I ended up writing that in the header. People still usually have no idea what activity does.
Snapshot is -5 (negative five) in bin A and -3 (negative three) in bin B
We physically received 10 of the part yesterday, but did not receive the 10 in the computer until today, after the freeze (maybe to fix a pricing error from the supplier)
We have 2 on hand today, in bin A (and none in bin B). The other 8 were used in making a frame yesterday and were backflushed accordingly
If we report this as is, we will gain 10 of this part (falsely), on top of the 10 just received. Even if we report counts of zero, we will still falsely gain 8 pieces.
Unreceiving the PO will not fix this!
Still report exactly what was counted: 2 in bin A and zero in bin B
But you can set Activity Before Count on bin A to 10 (positive 10)
I saw the activity field but I thought it was calculated if we were doing a cycle count and things changed. I will definitely look into these ideas. You rock!! I don’t think I would’ve figured this out on my own. I owe you!! We were relegated to the idea of just posting the counts as it wants us and then mathing a bunch to figure out what kind of actual adjustment it is so we can actually “reconcile”… because they aren’t going to let us turn things back on until we all agree that the variance is what it is supposed to be. You know? It’s like a chicken and the egg. So I’ve been trying to show the extent of this problem.
Intent of activity is that it automatically sums up inventory transactions that occur after the freeze (start count sequence) yet before the tag is “returned” (the checkbox). (It’s more for cycle counting an active building than for a “nobody move!” physical inventory.)
It’s rather brilliant if you are (1) electronically issuing/shipping/receiving/transferring material all in real time AND (2) your counters are using handhelds to “return” the count (electronic tag) at the moment they do the counting. Then you can actually hit a moving target.
Yeah that’s exactly how I envisioned it was intended. We are not that sophisticated. But good to know how it works so I can actually use it to bail us out here. Thanks again!
The Activity field is so you can populate stuff that happened after the snap shot is taken. we stop all activity during our full physical so no need to include it.
No need until your serial tracked stuff doesn’t match and/or the non-nettable stuff has some variance because it didn’t generate tags where it should’ve.
@JasonMcD@Craig - Did you guys ever run into issues where the first count all the tags have a .1 at the end. And you have a recount so then all your tags have a .2 at the end (that needed to be recounted). But blank tags always seem to still use .1 at the end. By definition, it makes sense. But if I am trying to analyze by count what my totals were for a given part, for example, I can’t simply use the .1 or .2 to sum those tag values. I get it, it is what it is… but wondered if either of you had a better way to try to chase down large variances. Like we’re supposed to get a smaller and smaller variance with each recount but we somehow keep going back and forth from high numbers to low numbers. It’s crazy.
Yeah - maybe next year we won’t either. I could write the changes to a ChangeLog perhaps and then I’d still be able to easily give someone the ability to see what the different counts entered were.
With change log, I think CCTag used to NOT have the ability to log. I forget if (a) Epicor fixed that, or (b) I fixed it. But there’s a supported hack to allow change logs on tables that lack it.
OK team - we’re in the home stretch. I know it’s Halloween. But we manually added some parts on blank tags that evidently did not exist in the same site/warehouse. They were out of tolerance. They require a reason code. But I can’t update it in the Count Discrepancy Reason screen. It’s greyed out. I tried a UBAQ. But there is no entry in CCDtl for me to be able to update. How do I go about adding more instances into CCDtl? It’s a physical inventory so it has locked me from adding more parts. Which is riduclous that it doesn’t include all the parts to begin with. Eff my life. Any ideas?
I’ve got that sorted out. I guess I was typing in the wrong part number in my BAQ and so I thought there was no row in CCDtl. But that was wrong. Turned out to be an issue that I found from @JasonMcD on another post where counted qty was 0 but it had non-zero activity. Got those to go.
Now I’m stuck with a similar issue only there is no obvious answer to this one. It’s a random part. No allocations that I can find. Within tolerance. Just won’t post. Gives the generic error to run the Variance Report and enter discrepancy reason codes. I’ve tried several times to re-run the Variance report. I’ve tried giving it a reason. I’ve left it blank. But I can’t get it to post nor will it tell me what’s wrong. I also checked on open Packs and transfer packs to see if it was sitting in one of those. No luck. Any other things to try?
Somehow our counts + maybe me messing with the activity ended up drawing a handful of parts negative. And those part classes are set to not allow negative quantities. That was the issue there. I am unstuck finally. Wow - I don’t know if I want to do that inside Epicor’s CC program again. There were so many gotchas. Nothing like that ever happened before when we did it outside the CC program.
Thats how its supposed to work. That parts is linked to a tag, the .1 tag is returned so you need a new tag, but it keeps the tag number. Blanks arent linked to any Parts directly so no need to do the .2. you can just make a new blank tag.
I may as well write up some final thoughts and conclusions on our first “Physical Inventory” using Epicor Cycle Count/PI module.
After printing tags, ensure all tags printed and that your stock status matches up with your frozen inventory value. This takes a little doing but it will be well worth it to know ahead of time what tags will need to be manually generated.
1a. If you check the box that says to Include Zero QOH parts, it will generate tags for parts that only exist in Non-Nettable locations. I was unaware that’s what that box did but now I know. Thank you EpicCare.
1b. If there are still any unprinted tags, use the info ascertained in step 1 to have someone create all the manual tags that should’ve automatically been generated. Just put the Counted Qty to 0 and don’t check the returned box. We can also then use the Activity Before Count column to put in the current qty on hand that should’ve shown in the Frozen Qty column.
Before generating any tags, and honestly way before this, ensure all serial numbers in inventory in bin locations match up to the PartBin OnhandQty values. This really makes this go smoother. This was such a pain in the butt when things weren’t where they were supposed to be. People entering serial numbers incorrectly, or the wrong serial numbers, or none at all… It’s a headache. So best to start with accurate serial numbers/bin locations to begin with. If there are any that are incorrect, we can modify the tag with the ActivityBeforeCount column.
As I learned from @JasonMcD, if any should have a counted Qty of 0 with a value in ActivityBeforeCount, make appropriate offsets in both places to allow for the Discrepancy Reason Code to work properly.
Don’t rely on the Tag number suffixes to total up first count/second count/third count variances (or exclude blank tags when we do it).
