Ability to modify Last Cycle Count Date

ABC codes and cycle counting. Great fun.

One thing I learned (thanks to @Graemer) about cycle counting is that you have to wipe out every “last cycle count date” before you run Initialize Last Cycle Count Date, and this actually requires a data fix from Epicor since the field (PartWhse.LastCCDate) is read only. So that’s great.

But even beyond that, this is the distribution that Initialize Last Cycle Count Date comes up with even with a blank slate.I’ll ignore July-August because I ran it in August. It’s still all over the place. I did this pivot by value and usage also (not shown; with numbers from the calculate ABC report I did just before this), and it’s all still just as random.

image

Then I did some actual cycles in test. (I excluded zero OH.) It’s even more extreme.

image

So, the algorithm stinks. Fine. But can you at least let me do my own math and set my own dates, Epicor? Why is it read only?!

Jason;

I found that this report does not account for weekends or holidays defined in the calendar. Do you have many parts that are stored in multiple locations? The cycle count process will generate tags for all the bins that a part is stored in. This might explain 108 tags for 28 parts!

I did find that this process is only a starting point. I suspect because a customer would only run this program once for a warehouse, Epicor was not too willing to spend a lot of programming resources on it.
We are now in our second fiscal year of running the cycle counting. The few locations not using this are now clamouring for it.
After running a year of cycles, the process “stabilizes” and has a fairly predictable number of SKUs on the cycle.

Graeme,

Sure, I’m fine with multiple tags - in this case it was serialized parts that generate one tag per serial, but yes we certainly are disorganized. My quibble is counting 30 parts a day in September and 12 parts a day in October.

(Incidentally, I figured the average on the year should be 21/day for us, so this happens to be 9 on either side of that. That’s some kind of standard deviation!)

I think the way that you had stabilized it was by looking at the part selection every month and then moving parts to another period if there were too many. That’s what I inferred from the notes you sent me.

I mean, that’s really the only way to do it right now, but personally, I’d rather just set the dates with my own formula–if they are going to be this bad–and then be done with it.

I really do not understand why the date is read only. I mean, at least let me modify it with DMT or an updatable BAQ/dashboard! What harm do they think I am going to do? And I need to beg for a data fix just to clear the dates anyway.

I wonder how many people actually use cycle counting in Epicor.

1 Like

Jason;

You raise a good point. When I presented this at our user group last year only one company present was even using Epicor to do their annual count!

When we bought Epicor, one of the features we wanted to use was cycle counting, so we were motivated to get this going irrespective of what others were seeing. I sat through the cycle counting session
at the 2018 conference and talked to the Epicor consultants and it seemed easy enough but I don’t think they had many real world installations to draw on or they would have found the two places where I needed to do SQL updates! It sounds like the process
was developed on Vantage and migrated to ERP so it has been around a while. (maybe so marketing could say to potential customers that they had a process!)

I never thought about lot controlled or serialized parts. We only have our final finished goods serialized in Epicor right now but hope to move ahead with lot controlled (eg. sheet aluminum) and
serialized (eg. axels, valves) raw materials in the next couple years.

I would suspect the whole process is not setup to control the number of tags by lots or serial and is only set to trigger a count by part number. I know in our last generation there seems to be
a tendency for the first month in a quarter to have more parts than the last month. Only two or there per cycle but not uniform like you would expect.

We have only been running this for about a year in some of our locations but it has been well received. We just did our year end and the CFO was impressed by how accurate the inventory was for those
locations that are running it. He also loves the ability to monitor it all in Epicor. We might look at some additional reports to nag managers when a cycle is canceled but overall it works well. I personally get involved every quarter to regenerate the
cycles by location. Part of this is to monitor for any strangeness and make sure that right parts are selected. Overall this has been smooth and I could move that to the managers (if I could trust they would remember the steps!).

One piece that I would have liked to see was the ability to include on the scan gun the tag for the count. It should just prompt for the next location to count. Right now you have to print the
tags and then scan the paper and move to the location. Seems like a waste to print out a piece of paper for 20 minutes of use. In discussing with Tim Shoemaker at the conference, he said this was on Vantage but never was carried over to ERP. Other than
that the gun process is used by one of our locations and they like it.

I hope you able to get over these hurtles and get this in place for your company. Given some of the other challenges in your inventory you may need more direct monitoring but I think, at it’s core,
it is a good process.

Good luck and keep us updated on your progress!

1 Like

I need to refactor this code to use BO - I wrote this before I understood Business Objects, and how to call them. You can see it grabs the next tag automatically for the user:

// **************************************************
// Custom code for HHCountEntryForm
// Created: 28/03/2016 10:18:53
// **************************************************
using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using Erp.UI;
using Ice.Lib.Customization;
using Ice.Lib.ExtendedProps;
using Ice.Lib.Framework;
using Ice.Lib.Searches;
using Ice.UI.FormFunctions;

using System.Data.SqlClient;

public class Script
{
	// ** Wizard Insert Location - Do Not Remove 'Begin/End Wizard Added Module Level Variables' Comments! **
	// Begin Wizard Added Module Level Variables **

	// End Wizard Added Module Level Variables **

	// Add Custom Module Level Variables Here **

	public void InitializeCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
		// Begin Wizard Added Variable Initialization

		// End Wizard Added Variable Initialization

		// Begin Wizard Added Custom Method Calls

		this.btnNextTag.Click += new System.EventHandler(this.btnNextTag_Click);
		// End Wizard Added Custom Method Calls
	}

	public void DestroyCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
		// Begin Wizard Added Object Disposal

		this.btnNextTag.Click -= new System.EventHandler(this.btnNextTag_Click);
		// End Wizard Added Object Disposal

		// Begin Custom Code Disposal

		// End Custom Code Disposal
	}

	private void btnNextTag_Click(object sender, System.EventArgs args)
	{
		// ** Place Event Handling Code Here **

		string connString = @"Server=EpicorSQL01;Database=Epicor10Live;Trusted_Connection=True;";
        string queryTags = "select * from Erp.CCTag Where TagStatus = '0' AND TagReturned = '0'";
		string queryOpenCounts = "select * from Erp.CCHdr where (CycleStatus = 2 or CycleStatus = 3)";

		DataTable dtTags = new DataTable();
		DataTable dtCounts = new DataTable();			

		// COUNT HEADERS - Create data adapter, populate data table, then close SQL connection
        SqlConnection conn1 = new SqlConnection(connString);        
        SqlCommand cmd1 = new SqlCommand(queryOpenCounts, conn1);
        conn1.Open();
        
        SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
     
        da1.Fill(dtCounts);
        conn1.Close();
        da1.Dispose();

		if (dtCounts.Rows.Count == 0)
		{
			MessageBox.Show("No Open Count!");
			return;
		}

		if (dtCounts.Rows.Count > 1)
		{
			MessageBox.Show("More than 1 open count, please complete one of them!");
			return;
		}

		// TAGS - Create data adapter, populate data table, then close SQL connection
        SqlConnection conn = new SqlConnection(connString);        
        SqlCommand cmd = new SqlCommand(queryTags, conn);
        conn.Open();
        
        SqlDataAdapter da = new SqlDataAdapter(cmd);
     
        da.Fill(dtTags);
        conn.Close();
        da.Dispose();

		//MessageBox.Show("Before: " + dataTable.Rows.Count.ToString());
		
		//DataRow rowBefore = dataTable.Rows[0];
		//MessageBox.Show("Line 1 AFTER (BIN): " + rowBefore["BinNum"]);
		//MessageBox.Show("Line 1 AFTER (TAG): " + rowBefore["TagNum"]);

		if (dtTags.Rows.Count > 0)
		{
			
			DataView dv = dtTags.DefaultView;
			dv.Sort = "BinNum asc, PartNum asc";
			DataTable sortedDT = dv.ToTable();

			//MessageBox.Show("After: " + sortedDT.Rows.Count.ToString());
	
			DataRow row = sortedDT.Rows[0];
			//MessageBox.Show("Line 1 AFTER (BIN): " + row["BinNum"]);
			//MessageBox.Show("Line 1 AFTER (TAG): " + row["TagNum"]);
	
			EpiTextBox txtWH = (EpiTextBox)csm.GetNativeControlReference("fd6f502a-c4d1-415f-a1a1-14d0818cec48");
			EpiTextBox txtTAG = (EpiTextBox)csm.GetNativeControlReference("5eb35fee-b631-4e4e-8dad-39b46df8b782");
			
			txtWH.Text = row["WarehouseCode"].ToString();
			txtTAG.Text = row["TagNum"].ToString();
			txtTAG.Focus();
	
			SendKeys.Send("{tab}");

		}
		else
		{
			MessageBox.Show("No more TAGs");
		}

	}
}

Just add a button to the screen called “Next Tag”:
image

2 Likes

Wow! Thanks Mark. I will have to try this.

Support has actually been responding intelligently the last couple of days. I think Leigh is watching out for me since that survey I did last week…

Anyhow, this is what I got in response to my request:

The last CC date is updated when the part is manually created in the system and when the part is posted on a regular cycle count. This fields also ties records together on the CC tables so changing this can cause some tracking issues in some cases.

The field is not available to be edited because the system is using the count frequency of the assigned ABC code or manually set count frequency to determine if the part is eligible to be counted in the cycle. Invalid dates in this field can cause issues with part selection.

That last part, “Invalid dates in this field can cause issues with part selection.”

OK, and? I mean, VALID dates cause issues!

And I don’t really buy that PartWhse.LastCCDate “ties records together on the CC tables.” I can’t find that link myself.

So the ticket with Support went well (I was not treated like an idiot :+1:), but me alone is not enough to change this. Hence I need the group’s help.

From what I understood on your post, you’re trying to run and re-run the “Initialize Last Cycle Count Date” multiple times over.

I do tend to agree with support here, that there to me seems no need to be doing this. Once the part has a date, it will get counted the next time based upon the LastCCDate and the ABC code applied to it. If you want to change the frequency or date, then you should be assigning it an ABC code that achieves the count frequency you require. You know that on the Part Selection, you still have the option to move parts to another count or remove them.

We use the process daily, and it’s always worked fairly well - we don’t mess with it, once it was setup it just ticks along. If you want to put effort into getting code changes, then for me it would be relating to the Part Selection algorithm and NOT the Initialize function.

Well, I am rerunning it a lot now, because I am testing.

There is an enhancement request for a checkbox to let “Initialize Last Cycle Count Date” clear out all dates itself. That actually predates me, but I had to get them to reopen it because it said it was complete. (I asked how it had been fixed, and then the next day, magically it was in process again!) And that would go a long way to helping implementers like me.

And still, I’d rather get the distribution the way I want it at the start.

Another desire I have is to block out 4 weeks of no cycle counting - two weeks on either side of our annual physical inventory - to give the same employees time to prep and reconcile the inventory. But the initialize process is no respecter of calendars.

It’s interesting, the initialize process actually kind of does this - it blocks out the 30 days prior to today, the day you run it. Which is great if I did this on July 15 (our year-end is June 30), but not so great if I do this in November or something.

As promised, refactored code to use BOs:

// **************************************************
// Custom code for HHCountEntryForm
// Created: 28/03/2016 10:18:53
// **************************************************

extern alias Erp_Contracts_BO_CountTag;
extern alias Erp_Contracts_BO_CCTagSearch;

using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using Erp.UI;
using Ice.Lib.Customization;
using Ice.Lib.ExtendedProps;
using Ice.Lib.Framework;
using Ice.Lib.Searches;
using Ice.UI.FormFunctions;

using Erp.Adapters;

public class Script
{
	// ** Wizard Insert Location - Do Not Remove 'Begin/End Wizard Added Module Level Variables' Comments! **
	// Begin Wizard Added Module Level Variables **

	// End Wizard Added Module Level Variables **

	// Add Custom Module Level Variables Here **
	
	public void InitializeCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
		// Begin Wizard Added Variable Initialization

		// End Wizard Added Variable Initialization

		// Begin Wizard Added Custom Method Calls

		this.btnNextTag.Click += new System.EventHandler(this.btnNextTag_Click);
		// End Wizard Added Custom Method Calls

	}

	public void DestroyCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
		// Begin Wizard Added Object Disposal

		this.btnNextTag.Click -= new System.EventHandler(this.btnNextTag_Click);
		// End Wizard Added Object Disposal

		// Begin Custom Code Disposal

		// End Custom Code Disposal
	}

	private void btnNextTag_Click(object sender, System.EventArgs args)
	{
		
		// Check to see if the relevant CCHdr record exists first
		CCHdrSearchAdapter adapterCCHdr = new CCHdrSearchAdapter(this.oTrans);
		
		try
		{
			
			adapterCCHdr.BOConnect();

			bool morePages;
	   	 SearchOptions opts = new SearchOptions(SearchMode.AutoSearch);
   	     opts.DataSetMode = DataSetMode.RowsDataSet;
			string whereClause = "CycleStatus IN ('2', '3')";
			opts.NamedSearch.WhereClauses.Add("CCHdr", whereClause);
			opts.PageSize = 1;
   	        	     			
			DataSet dsCCHdrSearch = adapterCCHdr.GetRows(opts, out morePages);
			
			if (dsCCHdrSearch.Tables["CCHdr"].Rows.Count == 0)
			{
				MessageBox.Show("No Open Count!");
				return;
			}
	
			if (dsCCHdrSearch.Tables["CCHdr"].Rows.Count > 1)
			{
				MessageBox.Show("More than 1 open count, please complete one of them!");
				return;
			}

		}
		catch (System.Exception ex)
		{
			MessageBox.Show("Error occured in CCHdrSearch");
		}
		finally
		{
			adapterCCHdr.Dispose();
		}

		
		// Find the next relevant TAG if one exists
		CCTagSearchAdapter adapterCCTag = new CCTagSearchAdapter(this.oTrans);
		
		try
		{
			
			adapterCCTag.BOConnect();

			bool morePages;
	   	 SearchOptions opts = new SearchOptions(SearchMode.AutoSearch);
   	     opts.DataSetMode = DataSetMode.RowsDataSet;
			string whereClause = "TagStatus = 0 AND TagReturned = 0 BY BinNum, PartNum";
			opts.NamedSearch.WhereClauses.Add("CCTag", whereClause);
			opts.PageSize = 1;
   	        	     			
			DataSet dsCCTagSearch = adapterCCTag.GetRows(opts, out morePages);
			
			if (dsCCTagSearch.Tables["CCTag"].Rows.Count > 0)
			{
				//EpiDataView edvTag = ((EpiDataView)(oTrans.EpiDataViews["tagView"]));
				//System.Data.DataRow edvTagRow = edvTag.CurrentDataRow;
		
				EpiTextBox txtWH = (EpiTextBox)csm.GetNativeControlReference("fd6f502a-c4d1-415f-a1a1-14d0818cec48");
				EpiTextBox txtTAG = (EpiTextBox)csm.GetNativeControlReference("5eb35fee-b631-4e4e-8dad-39b46df8b782");
				EpiNumericEditor numQty = (EpiNumericEditor)csm.GetNativeControlReference("a77cfc03-474b-4d4e-b46f-96977f06a1cf");
				
				txtWH.Text = (string)dsCCTagSearch.Tables["CCTag"].Rows[0]["WarehouseCode"];
				txtTAG.Text = (string)dsCCTagSearch.Tables["CCTag"].Rows[0]["TagNum"];
				txtWH.Focus();
				numQty.Focus();
		
				/*if ((edvTagRow != null))
				{			
					MessageBox.Show("WH contains: " + edvTagRow["WarehouseCode"]);
	
					edvTagRow.BeginEdit();
					edvTagRow["WarehouseCode"] = dsCCTagSearch.Tables["CCTag"].Rows[0]["WarehouseCode"];
					edvTagRow["TagNum"] = dsCCTagSearch.Tables["CCTag"].Rows[0]["TagNum"];
					edvTagRow.EndEdit();
				}*/

			}
			else
			{
				MessageBox.Show("No more Tags!");
			}

		}
		catch (System.Exception ex)
		{
			MessageBox.Show("Error occured in CCTagSearch " + ex);
		}
		finally
		{
			adapterCCTag.Dispose();
		}

	}
}



We cycle count during the year, but we still need to do a warehouse-wide physical inventory in December to turn up misplaced parts. Nothing will select for a cycle right after that! Left to its own devices, Epicor would give me nothing but A’s for two months and then dump all the B’s into one cycle, and then a little later dump all the C’s in. In order to get an even spread, I have to tweak the count frequencies every month, the first half of each year. I’d really prefer to keep using the last non-physical cycle count date.

So maybe I don’t need to be able to clear or edit that field, but get some kind of additional or more nuanced control. Maybe the ability to say, in effect, “I want to count these parts twice a year, not including PI, and I want them distributed throughout the year.” I know it wouldn’t be perfect because the codes and the parts will change each month, but it could start with an estimate and adjust.

1 Like

Wow. Didn’t even think of that. So you’d almost need to reinitialize every PI.

Yeah, pretty much. And Repetitive Mode is out of the question. I keep the January cycle light with just A’s for the inventory controller’s sake; after that, I set everything to a frequency of 30 (increasing B and C each month until they hit their normal values) and use Random mode’s quantity limits to trickle parts into the cycles. I suspect it’s not great at ensuring the most overdue parts are counted first, though, and I can’t turn this process over to the inventory controller.