Category Archives: ERP

Data Loads in CSI/Syteline

Nothing too extraordinary here, lots of “paste rows append” in the grid view, but I’ll touch on a few notes from our experience.

Our first sets of data loaded were the customer and vendor tables. These were sometimes loaded months before go-live to get the different groups used to going into the system to keep the records up to date. That might not be workable if these records change frequently, but even a week ahead of time should be reasonable.

The issue with Customers in particular is that there is no good way to load the ship-tos in an easy, automatic fashion since the ship-to ranking can only be set after the record is loaded. Our workaround for that was to set the records manually, and that’s something most places will only want to do once. Another issue for both customers and vendors is that there is a lot of tuning to the records during testing and it would be difficult to handle that in a coded export.

The setup for Customers has a generous number of areas where addresses and contacts can be added, but for vendors there is just the one record, plus a second record, of sorts, for the remit-to (only one remit-to can be set). This is an intractable issue with the package since expanding Vendors to work similar to customers would require quite the set of database changes. The only thing I will note here is that for both Customers and Vendors we used a truncated version of the customer’s/vendor’s name instead of auto-numbering (CSI doesn’t do a good job of enabling users to easily find names) and we prepended the vendor’s remit-to with ‘ZZ’ to help in more easily differentiating them from the main address.

Items is the a database table that was usually loaded a couple days ahead of time. Note, this burned me every time since production did a poor job of keeping the records up to date, even over the course of 72 hours. I tried to control for this by having my router export program tell me which parts did not have a match in CSI (the router was exported out of the old system, but it couldn’t find a corresponding part in CSI). Even at our smaller operations it took several hours to paste the Current Operations into the system, but a big tip: refresh after each data segment is pasted in. So for instance, I would generally paste 1,000 rows at a time, and after the paste completed and I saved the records (without an error hopefully!) I would refresh the form so that only 200 records were on the form. If the form is not refreshed CSI gets slower and slower as it has to keep track of progressively more and more data.

The last thing I want to touch on is importing WIP value, and importing shop floor transactions: both of these had to be done by hand. Obviously the goal is to “run out” as many jobs as possible to minimize this work, or at the very least, complete/close operations in the legacy system. For the WIP value we issued a fake part to each job that had the current WIP dollar totals for the job (when asked to create the part, just click Cancel). For the shop floor transactions it was as much fun as it seemed: as many people as could be convinced to come in over the weekend would be trained to both key data into Unposted Job Transactions and close out operations on Job Operations. If possible, this process can be helped greatly by having someone from production helping so as to catch last minute operator keying errors.

CSI/Syteline Stored Procedure Report

I generally like having custom reports within the system but it can be spendy to do a bunch of customized reports for trivial tasks. One issue I faced was with reports (in version ~9) where the data was good, but the layout wasn’t in a dataview (Vouchers Payable, Aging reports, etc). I could get around this myself by running a query inside of SQL Server Management Studio, but that got me thinking: why not just allow users themselves to execute the stored procedures that generate the data from within Excel? This gets around two issues, first is that it is not a customized report as the user is running the same routine that CSI does and secondly they needed to get it into Excel anyway, so in a way I’m skipping the “middle man”.

The first thing to do is to collect the list of parameters needed to run the report. This can be gotten rather easily by “printing” the report and then going to Background Task History and pulling the task parameters:

I then make a note of those and execute the stored procedure inside of SSMS using those parameters, but an important note here: do not leave any of the values as “null” as Excel cannot pass null values to the stored procedure (as I am doing it without any code). After some cleanup I have something like this:

This is also good because the parameters are in the sequence needed for Excel, as we’ll see in this next step where we will put the parameters on a separate tab. Although ranges are required for Excel, and it “kinda sorta” doesn’t do nulls, I’d found that putting a double single quote in the cell accomplishes what I need it to (note: shows up as a single ‘ when viewing the spreadsheet):

Next we can put the query together in Excel, be sure to use “From Microsoft Query” because otherwise we will not be able to pass parameters (dynamically) to the stored procedure:

If needed on the next step add a connector to the database server (“New Data Source”), then select the connector and click OK. On the next step click Cancel and then Yes to edit it in Microsoft Query. Click Close and then click the SQL button:

Then click the Definition tab and then Edit Query. The formatting on this can be a smidge tricky since there are multiple parameters, but you’ll want a question mark for each parameter going to the stored procedure, so in this case I’ll enter a query like this:

{call Rpt_VouchersPayableSp (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}

After you click OK you will get a warning that it can’t be represented graphically, click OK again and you will be prompted to fill out the parameters, just click OK on all of them (leaving them blank). Even if it comes back with no data that’s okay, just go up to File->Return Data to Excel.

When it goes to Excel it will re-prompt for parameters. In this case, it will more than likely want real data or it will error out and dump the query and you’ll have to restart from the Data Connector step. I will usually key this in and fix it afterward, though you can set it up properly at this step by selecting the cells on the Parameters sheet, just be sure to click ‘Use this value’:

Hopefully that ran through okay, but this then gets into a permission issue that I ran into. For users running these stored procedures the only database access I wanted them to have was to the stored procedures themselves. However, when I tried to run it as the user I would get ODBC errors in Excel and in SQL I would get errors like:

Get DataType failed for StoredProcedureParameter
Property DataType is not available for StoredProcedureParameter

This post put me on the right path. The issue is that many CSI stored procedures have custom data types and the user needs to have View Definition permission to the schema where the data types are scoped to, in this case ‘dbo’. So I give the user Execute permission on the stored procedure and View Definition to the dbo schema and then the user can run the query inside of Excel with minimal permissions.

[Note that some of the data returned by these stored procedures can be ungainly. For the Vouchers Payable report I ended up making a new copy of the stored procedure and cleaning up the outputs. This is rather trivial and the advantage here is that as the stored procedure changes, the Excel output will change accordingly without having to change anything on the spreadsheet].

CSI/Syteline “Customer Since”

One of the more lightly requested modifications to the Customers form in Syteline was for there to be a way to tell how long someone had been a customer (for credit check purposes). I rigged this up to work by first adding a field for CreateDate to the Customers form (which is easy since it’s already in the IDO for the form):

I then execute a SQL query to change the CreateDate to be the “customer since” date (going forward these should be the same, but for legacy customers the date would just be whenever they were added to the database):

UPDATE customer_mst SET CreateDate='1/1/20' WHERE cust_num='TEST' AND site_ref='TESTDB';

Notes on Scheduling and Costing in CSI/Syteline

We had some initial struggles getting running on our CSI implementation so we started with one of our smaller operations. We had very little guidance with several nuances with the package (it’s a long story that’s not fit for print) so I’m going to touch upon them here, but, the overarching rule is thus: routers need to be correct so that both costing and scheduling is correct. If the routers are bad, both the costing and scheduling will not work as designed in CSI.

To begin, all manufactured parts in CSI need a router to be setup up in Current Operations and Current Materials. In other systems these are called the “job templates”, though in CSI there is only one ‘true template’. I will point out that this mostly applies to the APS flow in an actual costed environment. I do suppose that it is possible to use a standard costed system and ignore the scheduler and in that case perhaps the ‘Currents’ are not needed. However, I think that is still bad form since the system will then not have a baseline to compare actual production values to. It is possible for the numbers to be compared to the standard but the standard is just an educated guess made by an, no doubt talented, accountant while the “planned cost” that is derived from the router is a ‘guess’ made by production.

Properly constructed routers have another benefit too: if the job cost variance report shows a very odd “planned cost” then that probably means that there is an issue with the router that may show up in scheduling the job. In this way production and accounting get actionable data out of the same single data set.

As well, this planned cost is critical in an actual costed environment. In CSI if a partial job quantity is moved in to inventory, how does CSI know how to cost it since not all the costs have been incurred on the job? The answer: it uses that planned cost. Thus it is critically important that the planned be as close to the actual as possible (think of it like a standard cost that isn’t actually standard). When the job is closed out the difference between the planned and the actual is sent to the inventory adjustment account in CSI. The correct sequence for inventory moves when using actual costing in CSI then is:

  • If the job is being moved into inventory complete, then close the job and move the inventory at the same time.
  • If there have been any partial moves from the job into inventory, then move all the parts as partial and close the job out separately (within the Job Orders form).

I found the operation and tuning of APS itself pretty straightforward with two notable exceptions:

  1. Forecasting does not work. Well, I take that back, there are a narrow set of circumstances where the Forecasting function might work but I’ve been unable to find anyone that it would work for. The workaround for this is to put a bogus sales order into the system that serves as the forecast (and manually deducting the forecast as its consumed).
  2. APS handles Setup times very poorly in that it doesn’t roll the Setup resource group off of the job after the setup is complete. Part of this probably has to do with the fact that CSI has no way to know if a setup is complete. The workaround for this is to put the setup on the router as a separate operation. This can lead to some wonky views in the scheduler since there is no guarantee that the machine being setup is the same one that will be doing the run, BUT, the throughput will be correct.

We also ran into two other issues that were no fault of the package. In one instance, some of our routers were very inaccurate and it was very difficult to get them fixed so the planning people resorted to marking the parts as MRP which forces APS to use whatever lead time it is told. The other issue had to do with a change in methodology since it’s important that the operations are closed out as they are completed, otherwise APS leaves the whole time scheduled. This was something we had to work on with the employees so that they knew when (and when not) to close out an operation.

Notes in CSI / Syteline

With our implementation of CSI/Syteline it was important to bring over our work instructions which meant extracting the notes out of the old system and putting them into the new one. The issue here is that there is no real method to do this within the system so direct SQL access is required in order to pull this off.

Please keep in mind that being a SQL DBA is one of my many part time jobs and not a specialty, so some of these steps may not have been as efficient as they could have been.

For our two different production systems the notes were stored using two different ways, though the process for putting them into CSI was basically the same:

  • For JDE our notes were integrated into the routing line and due to the structure they required some re-processing. Fortunately these routers were very stable so I didn’t have to worry about a “live migration” of the notes. Using a brief piece of code I extracted the lines and formatted them to Excel (although theoretically the same steps could be accomplished in Excel without using code).
  • For our JobBOSS notes I needed to be able to pull these over at or near go-live. This meant that I had to do some very light processing of the note text within Visual Studio.

In CSI there are three tables of importance:

  1. NoteHeaders – in this table is a list of what “token” goes to which table. Since these are work instructions on the router the notes will be associated with the jobroute table. The important step here is that at least one note needs to be created to generate this entry. There are other possible ways, but this is the simplest method: make a note on an operation line, and then check in the NoteHeaders table to see what number goes to jobroute. The note can then be deleted.
  2. SpecificNotes – This table will have the note text (and note title) that will contain the work instructions
  3. ObjectNotes – This table will have the note key associated with the note text in the SpecificNotes table, the table the notes goes to (using the number from the NoteHeaders table) and the row pointer to the record in the table that it is associated with.

A complicating factor for our JobBOSS implementation is that we were required to rework and/or resequence the router lines (for instance something that was “Op100-CNC” in JobBOSS might be “Op80-Mill” in CSI). If nothing was changed on the routers it would be possible to “brute force” the note entry by adding all the notes, pulling the row pointers in sequence and then building a query from that to add the associations in the ObjectNotes table (because if I had 100 router lines, and 100 notes then I’d know that the 50th note in the SpecificNotes table goes to the 50th record in the jobroute table). However due to the changing router I had to handle the notes using the same code in C# in Visual Studio that I was using to put the routers together, since only that code knew what the sequence would be.

The first step then was to extract the router lines from JobBOSS. I used a LINQ query like this:

var operations = from o in jo.Job_Operations
                 join o2 in jo.Jobs on o.Job equals o2.Job1
                 join o3 in jo.Materials on o2.Part_Number equals o3.Material1
                 where o.Status.Trim().Equals("T") && o2.Priority<9 && o3.Status.Trim().Equals("Active")&&o2.Part_Number!=null
                 orderby o3.Material1,o.Sequence
                 select new { o.Job,o3.Material1,o.Sequence,o.Description,o.Est_Setup_Hrs,o.Inside_Oper,o.Operation_Service,
                                   o.Run,o.Run_Method,o.Vendor,o.WC_Vendor,o.Work_Center,o.Last_Updated,o.Last_Updated_By,o.Est_Unit_Cost,
                                    o.Note_Text,o.Cost_Unit};

The “where” clause is limiting the jobBOSS jobs to be pulled over to templates (“T”) that go to Active part numbers.

Many, many lines of code later I get to the note section. Due to the nature of the notes tables I was unable to get the code to insert the notes directly into the database and instead had to write out a query out to a text file that I then ran in SSMS. I cycled through each line in the router and wrote out an Insert query for each note:

string sqlCmd = "INSERT INTO[SpecificNotes](SpecificNoteToken, NoteContent, NoteDesc, NoteExistsFlag, " +
"CreatedBy, UpdatedBy, CreateDate, RecordDate, RowPointer, InWorkflow)" +
"values((SELECT ISNULL(MAX(SpecificNoteToken) + 1, 0) from dbo.[SpecificNotes]),'" +
n.NoteText + "','WORK INSTRUCTIONS', 0, 'sa', 'sa', DEFAULT, DEFAULT, DEFAULT, 0);";

I wrote the lines of text out as they were generated and then copied the contents of the resulting file and pasted them into SSMS. Before running it I had to put the text “SET IDENTITY_INSERT SpecificNotes ON” at the beginning and “SET IDENTITY_INSERT SpecificNotes OFF” at the end.

With my notes added to the database I then rerun the code to generate the Current Operations lines. This time, a different piece of code in the routine goes out to find a note that is now in the CSI database that matches the note line held by the object of the Current Operation line:

//get notes from CSI
var snotes = from s in si.SpecificNotes
            select new { s.NoteContent, s.SpecificNoteToken };
List<sNotes> sNoteList = new List<sNotes>();
foreach (var s in snotes)
            {
                sNoteList.Add(new sNotes());
                sNoteList.Last().Token = (int)s.SpecificNoteToken;
                sNoteList.Last().Note = s.NoteContent.Trim().Replace("'", "''"); ;
            }
//Marry the notes in the note DB to the process lines, OYE!
foreach (var n in syteCurrOps2)
            {
                foreach (var s in sNoteList)
                {
                    if (!s.Used)
                    {
                        //Note: essential that the notes read out of the database match the ones inserted (i.e. avoid whitespace issues!)
                        if (n.NoteText.Trim().Equals(s.Note))
                        { n.NoteNum = s.Token; s.Used = true; worker.ReportProgress(i++); break; } 
                    }
                }
            } 

Now that I know which note in the database goes to which router lines, I need a line in the ObjectNotes table to match them up. Restating a key point here, a normal note must be manually added to a Current Operation line so that CSI will know which table the note is associated with. Just add a line and then query the lines in the NoteHeaders table to see what number must be used on the SQL Insert lines for the ObjectNotes table:

In this case in the code I have a number 4 in the insert statement, but I would want to change it to a 1:

//NOTE: change #4# below to the number specified in the NoteHeaderToken field in the NoteHeaders table
//      This number is set when the "sample" note was created earlier (which is why a sample note must be created before insert)
string sqlCmd = "INSERT INTO [ObjectNotes](ObjectNoteToken,NoteHeaderToken,RefRowPointer,NoteType,SpecificNoteToken," +
"NoteExistsFlag,CreatedBy,UpdatedBy,CreateDate,RecordDate,RowPointer,InWorkflow)" +
"values((SELECT ISNULL(MAX(ObjectNoteToken) + 1, 0) from dbo.[ObjectNotes]),4,'" + n.RefRow + "',0," + n.NoteNum + ",0," +
"'sa','sa',DEFAULT,DEFAULT,DEFAULT,0);";
sw.WriteLine(sqlCmd);

Just as with the notes query, I write this query list out to a text file so that they can be run in SSMS (and again setting the “SET IDENTITY_INSERT SpecificNotes ON/OFF” lines before/after the query).

The last step is that since all these notes were entered in SQL and not the interface, CSI needs to know which specific Current Operation lines have notes. We do this by setting the bit operator for the NoteExistsFlag field to one. So again, I just dump out a a series of lines to put into SSMS to update all of the lines based on the row pointers for the lines that now have notes:

foreach (var n in syteCurrOps2)
    {
        if (!n.RefRow.Equals("") && n.NoteNum > 0)
          {
              string sqlCmd = "UPDATE jobroute_mst SET NoteExistsFlag=1 WHERE RowPointer='" + n.RefRow+ "';";
              sw.WriteLine(sqlCmd);
              worker.ReportProgress(i++);
          }
          else if (n.RefRow.Equals("") && n.NoteNum > 0)
              logger("Rowless line: " + "Item: " + n.Item + " Seq " + n.Seq);
    }

So in the end there are three sets of SQL codes exported, one for each of the two related Notes tables, and one for the table that is having the notes added to it. The last line of code in that snippet logs lines that the code thinks should have a note, but couldn’t find one. This typically happened due to odd characters (‘,”,*, etc.) or white space mismatch issues, but over all it was quite reliable.

Slightly off topic, but you may see the “worker.ReportProgress” line in some of the code. Due to the fact that some of the queries took a long time to run I had to put my main methods into new threads (multi-threading). Every now and then in the code I put these ReportProgress lines in so that my (very) inexact progress meter would update on the interface to let me know it was running (and to give me some idea of where it was at in the execution process). So under my code for my “export button” I had something like this to launch the Current Operation export process:

//extracts have to launch in new a thread so that the parent thread won't hang and progress can be tracked
worker.WorkerSupportsCancellation = true;
worker.WorkerReportsProgress = true; 
if (comboBoxExpType.Text.Equals("CurrentOps"))
                worker.DoWork += new DoWorkEventHandler(exportCurrentOps);
worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);
worker.ProgressChanged += new ProgressChangedEventHandler(worker_ProgressChanged);
worker.RunWorkerAsync();