SytelineMaster

Table trigger is important part of Syteline data integrity.  There are many validation logics safe guarded by trigger program.  But in some circumstances, you may want to turn the trigger program off, in order load certain data.  This is specially true during data conversion of your Syteline implementation, you may need to use program to load data from outside data sources.

Below sample program will turn the trigger off, update jobmatl table, then turn the trigger back on again.

declare @SavedState LongListType

, @Infobar InfobarType

EXEC dbo.SetTriggerStateSp

@SkipReplicating  = 1

, @SkipBase         = 1

, @ScopeProcess     = 1

, @PreviousState    = @SavedState OUTPUT

, @Infobar          = @Infobar  OUTPUT

, @SkipAllReplicate = 1

, @SkipAllUpdate    = 1

UPDATE jobmatl

SET pick_date = @Today

FROM #JobmatlForPickList as jm

WHERE jobmatl.RowPointer = jm.RowPointer

exec dbo.RestoreTriggerStateSp

@ScopeProcess    = 1

, @SavedState      =  @SavedState

, @Infobar         = @Infobar OUTPUT

There is simple three steps process for adding/changing replication rules in Syteline.

1) Add/Edit rules in Replication Rules form

Notes: you setup all rules for source site in source site DB.

2) Regen trigger

Open “Replication Management” form,

Regen the replication trigger.

This will make the trigger program in place to replicate transactions from now on.

3) Run the Manual Replication Utility

This will replicate all the existing records, by letting the create date and modified date blank.

Starting from Syteline 8.03, the Pick, Pack, Ship function utilize BarTender for bar code label printing.  

Create label templates in Bartender.

The label template in Bartender need to use text file as database source.

Create a sample text file with data like following,

TC280847|123456789012345|DGM17ALS|03/03/2014

When we done, we should have data source like this,

We can then build whatever label file in Bartender.

Note for Syteline Pick, Pack, Ship

A text file is created that will be the basis of the labels created in BarTender.  This file is delivered with the SL install in the DevSetup folder.

The file should be labeled:

· BarTenderDatabaseFile.txt.

The contents of the file will be:

· ShipmentId|PackageId|Hazardous|RateCode|NMFC|MarksExcept|PackageType|PackageDesc|Weight|Item|Desc|UM|Qty|CustItem|Mfg|MfgItem|MfgItemDesc|Lot|Site|Whse|Location|CustNum|CustSeq|ConsigneeName

Create a task in Commander

In Commander, create a new task,

The command type need to be “Commander Script“.  All the details, such as what label template to use, what printer to print to and such, will be specify in the .dd file provided by Syteline.

In Trigger tab, specify the folder to monitor and the file type (.dd) to monitor.

Maintain the Template and Output Directory in Syteline

In Inventory Parameters form,

The output directory will be the place that Syteline drop the .dd file in, and Bartender Commander will monitor this directory for .dd files, it will then change them to .old for Bartender to process.

Enter the label printer on the Printers form

May also maintain a list of Label Template files in Package Label Templates form

Syteline Pick, Pack, Ship Label Printing

A form called “Print Package Label” can be accessed from “Pack Confirmation” or “Ship Master” form.

Print Package Labels

This form is used to select which package labels print jobs should be submitted for the labeling software to print.  Text files will be created and placed into the Output Directory specified on the Inventory Parameters

Printer – The printer that the label will be sent to.

Template Name – The filename of the label file

Copies – The number of copies of each label that should be printed.

Packages Exist – This checkbox will show whether or not packages have been defined for the shipment.

Print Labels By – This radio button set will determine how information will display in the grid if packages have been defined.

The core logic for putting together the .dd file is in Form script: CreateOutputFiles.

       Sub CreateOutputFiles()

Dim Filename As String

Dim Copies As String

Dim oCollection As IWSIDOCollection

Dim i As Integer

Dim Time As String

Dim TextFile(0 To 2) As String

Dim iDay As String

Dim iMonth As String

Dim iYear As String

Dim FileDate As String

Dim Lot As String

Dim Item As String

Dim Desc As String

Dim Qty As String

Dim UM As String

Dim CustItem As String

Dim Mfg As String

Dim MfgName As String

Dim MfgItem As String

Dim MfgItemDesc As String

Dim Package As String

Dim Hazardous As String

Dim RateCode As String

Dim NMFC As String

Dim MarksExcept As String

Dim PackageType As String

Dim PackageDesc As String

Dim Weight As String

On Error GoTo ErrorHandler

oCollection = ThisForm.PrimaryIDOCollection

If ThisForm.Components(“Printer”).ValidateData(True) = False Then

Exit Sub

End If

If ThisForm.Components(“LabelTemplateName”).ValidateData(True) = False Then

Exit Sub

End If

If ThisForm.Variables(“PackagesExistVar”).Value = “1” Then

If ThisForm.Components(“Copies”).ValidateData(True) = False Then

Exit Sub

End If

End If

If ThisForm.Variables(“PackagesExistVar”).Value = “0” Then

If ThisForm.Components(“NumberOfLabelsGridCol”).ValidateData(True) = False Then

Exit Sub

End If

End If

For i = 0 To oCollection.GetNumEntries – 1

If oCollection.GetObjectProperty(“UbSelect”, i) = “1” Then

If ThisForm.Variables(“PackagesExistVar”).Value = “1” Then

Copies = ThisForm.Variables(“CopiesVar”).Value

ElseIf ThisForm.Variables(“PackagesExistVar”).Value = “0” Then

Copies = oCollection.GetObjectProperty(“UbNumberOfLabels”, i)

Qty = oCollection.GetObjectProperty(“UbQtyPerLabel”, i)

Lot = oCollection.GetObjectProperty(“UbLot”, i)

Item = oCollection.GetObjectProperty(“UbItem”, i)

Desc = oCollection.GetObjectProperty(“UbItemDescription”, i)

UM = oCollection.GetObjectProperty(“UbUM”, i)

CustItem = oCollection.GetObjectProperty(“UbCustItem”, i)

Mfg = oCollection.GetObjectProperty(“UbManufacturerId”, i)

MfgName = oCollection.GetObjectProperty(“UbManufacturerName”, i)

MfgItem = oCollection.GetObjectProperty(“UbManufacturerItem”, i)

MfgItemDesc = oCollection.GetObjectProperty(“UbManufacturerItemDesc”, i)

Package = “”

Hazardous = “”

RateCode = “”

NMFC = “”

MarksExcept = “”

PackageType = “”

PackageDesc = “”

Weight = “”

End If

If ThisForm.Variables(“LabelsByVar”).Value = “L” Then

Lot = oCollection.GetObjectProperty(“UbLot”, i)

Item = oCollection.GetObjectProperty(“UbItem”, i)

Desc = oCollection.GetObjectProperty(“UbItemDescription”, i)

UM = oCollection.GetObjectProperty(“UbUM”, i)

Qty = oCollection.GetObjectProperty(“UbQty”, i)

CustItem = oCollection.GetObjectProperty(“UbCustItem”, i)

Mfg = oCollection.GetObjectProperty(“UbManufacturerId”, i)

MfgName = oCollection.GetObjectProperty(“UbManufacturerName”, i)

MfgItem = oCollection.GetObjectProperty(“UbManufacturerItem”, i)

MfgItemDesc = oCollection.GetObjectProperty(“UbManufacturerItemDesc”, i)

Package = oCollection.GetObjectProperty(“PackageId”, i)

Hazardous = oCollection.GetObjectProperty(“Hazard”, i)

RateCode = oCollection.GetObjectProperty(“RateCode”, i)

NMFC = oCollection.GetObjectProperty(“NMFC”, i)

MarksExcept = oCollection.GetObjectProperty(“MarksExcept”, i)

PackageType = oCollection.GetObjectProperty(“PackageType”, i)

PackageDesc = oCollection.GetObjectProperty(“Description”, i)

Weight = oCollection.GetObjectProperty(“Weight”, i)

ElseIf ThisForm.Variables(“LabelsByVar”).Value = “I” Then

Lot = “”

Item = oCollection.GetObjectProperty(“UbItem”, i)

Desc = oCollection.GetObjectProperty(“UbItemDescription”, i)

UM = oCollection.GetObjectProperty(“UbUM”, i)

Qty = oCollection.GetObjectProperty(“UbQty”, i)

CustItem = oCollection.GetObjectProperty(“UbCustItem”, i)

Mfg = oCollection.GetObjectProperty(“UbManufacturerId”, i)

MfgName = oCollection.GetObjectProperty(“UbManufacturerName”, i)

MfgItem = oCollection.GetObjectProperty(“UbManufacturerItem”, i)

MfgItemDesc = oCollection.GetObjectProperty(“UbManufacturerItemDesc”, i)

Package = oCollection.GetObjectProperty(“PackageId”, i)

Hazardous = oCollection.GetObjectProperty(“Hazard”, i)

RateCode = oCollection.GetObjectProperty(“RateCode”, i)

NMFC = oCollection.GetObjectProperty(“NMFC”, i)

MarksExcept = oCollection.GetObjectProperty(“MarksExcept”, i)

PackageType = oCollection.GetObjectProperty(“PackageType”, i)

PackageDesc = oCollection.GetObjectProperty(“Description”, i)

Weight = oCollection.GetObjectProperty(“Weight”, i)

ElseIf ThisForm.Variables(“LabelsByVar”).Value = “P” Then

Lot = “”

Item = “”

Desc = “”

UM = “”

Qty = “”

CustItem = “”

Mfg = “”

MfgName = “”

MfgItem = “”

MfgItemDesc = “”

Package = oCollection.GetObjectProperty(“PackageId”, i)

Hazardous = oCollection.GetObjectProperty(“Hazard”, i)

RateCode = oCollection.GetObjectProperty(“RateCode”, i)

NMFC = oCollection.GetObjectProperty(“NMFC”, i)

MarksExcept = oCollection.GetObjectProperty(“MarksExcept”, i)

PackageType = oCollection.GetObjectProperty(“PackageType”, i)

PackageDesc = oCollection.GetObjectProperty(“Description”, i)

Weight = oCollection.GetObjectProperty(“Weight”, i)

End If

iDay = CStr(Day(Now))

iMonth = CStr(Month(Now))

iYear = CStr(Year(Now))

Time = Format(Now, “hhmmss”)

FileDate = iMonth + iDay + iYear + Time

Filename = ThisForm.Variables(“OutputPathVar”).Value + “Infor” + ThisForm.Variables(“ShipmentVar”).Value + CStr(i) + FileDate + “.dd”

TextFile(0) = “%BTW% /AF=” & “””” & ThisForm.Variables(“TemplatePathVar”).Value & ThisForm.Variables(“FilenameVar”).Value & “””” & ” /PRN=” & “””” & _

ThisForm.Variables(“PrinterVar”).Value & “””” & ” /P /D=” & “””” & “<Trigger File Name>” & “””” & ” /C=” & Copies & “/R=3”

TextFile(1) = “%END%”

TextFile(2) = oCollection.GetObjectProperty(“ShipmentId”, i) & “|” & Package & “|” & Hazardous & “|” & RateCode & “|” & _

NMFC & “|” & MarksExcept & “|” & PackageType & “|” & PackageDesc & “|” & Weight & “|” & Item & “|” & _

Desc & “|” & UM & “|” & Qty & “|” & CustItem & “|” & Mfg & “|” & MfgName & “|” & MfgItem & “|” & _

MfgItemDesc & “|” & Lot & “|” & oCollection.GetObjectProperty(“UbSite”, i) & “|” & _

oCollection.GetObjectProperty(“UbWhse”, i) & “|” & oCollection.GetObjectProperty(“UbLocation”, i) & “|” & _

oCollection.GetObjectProperty(“UbCustNum”, i) & “|” & oCollection.GetObjectProperty(“UbCustSeq”, i) & “|” & _

oCollection.GetObjectProperty(“UbConsigneeName”, i)

System.IO.File.WriteAllLines(Filename, TextFile)

End If

Next i

ThisForm.CallGlobalScript(“MsgApp”, “Clear”, “Prompt”, “SuccessFailure”, _

“mI=CmdSucceeded”, “Process”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”)

Exit Sub

ErrorHandler:

ThisForm.CallGlobalScript(“MsgApp”, “Clear”, “Prompt”, “SuccessFailure”, _

“mI=CmdFailed”, “Process”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”)

End Sub

Custom Label Printing

We may use the similar logic to construct other custom label printing.

1) Need to define two form variables, and get the values during StdFormPredisply

SLShipmentPackages.LabelPathsExistSp( PARMS(RVAR V(TemplatePathVar), RVAR V(OutputPathVar)) )

2) Form method

      Sub CreateOutputFiles()

Dim Filename As String

Dim Copies As String

Dim oCollection As IWSIDOCollection

Dim i As Integer

Dim Time As String

Dim TextFile(0 To 2) As String

Dim iDay As String

Dim iMonth As String

Dim iYear As String

Dim FileDate As String

Dim Item As String

Dim VendorItem As String

Dim InDate As String

Dim Serial As String

On Error GoTo ErrorHandler

If ThisForm.Components(“Printer”).ValidateData(True) = False Then

Exit Sub

End If

If ThisForm.Components(“LabelTemplateName”).ValidateData(True) = False Then

Exit Sub

End If

If ThisForm.Components(“Copies”).ValidateData(True) = False Then

Exit Sub

End If

Copies = ThisForm.Variables(“CopiesVar”).Value

iDay = CStr(Day(Now))

iMonth = CStr(Month(Now))

iYear = CStr(Year(Now))

Time = Format(Now, “hhmmss”)

FileDate = iMonth + iDay + iYear + Time

Filename = ThisForm.Variables(“OutputPathVar”).Value + “ETE” + ThisForm.Variables(“Serial”).Value + FileDate + “.dd”

TextFile(0) = “%BTW% /AF=” & “””” & ThisForm.Variables(“TemplatePathVar”).Value & ThisForm.Variables(“FilenameVar”).Value & “””” & ” /PRN=” & “””” & _

ThisForm.Variables(“PrinterVar”).Value & “””” & ” /P /D=” & “””” & “<Trigger File Name>” & “””” & ” /C=” & Copies & “/R=3”

TextFile(1) = “%END%”

TextFile(2) = ThisForm.Variables(“Item”).Value  & “|” & ThisForm.Variables(“Serial”).Value & “|” & _

ThisForm.Variables(“VendorItem”).Value  & “|” & ThisForm.Variables(“InDate”).Value

System.IO.File.WriteAllLines(Filename, TextFile)

ThisForm.CallGlobalScript(“MsgApp”, “Clear”, “Prompt”, “SuccessFailure”, _

“mI=CmdSucceeded”, “Process”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”)

Exit Sub

ErrorHandler:

ThisForm.CallGlobalScript(“MsgApp”, “Clear”, “Prompt”, “SuccessFailure”, _

“mI=CmdFailed”, “Process”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”)

End Sub

Project Resource Transaction

Two ways to post: 1) Project Resource Transaction Form; 2) Xref to PO and during PO receiving transactions

Project Labor Transactions

Note: only project rate apply in calculation, pay rate has no impact.

AP Voucher Post to Project

Infor is really terrible sometime when it comes to documentation.  For example, for the application event system to work, you have to make your site name exactly match with your configuration name, and this basic and critical information is not listed in installation guide and event system guide.

You can only discover this when running Log Monitor on the Utility Server for event service (run  infor\Syteline\LogMonitor.exe, then filter with “MGEvent”).

Beside sigh, what can you do?

I have found the Syteline Financial Excel Addon a handy tool for building financial statement, much better than the financial statement writer inside Syteline.  Many long time Syteline user still have hard time understand the old financial statement writer, ever after using it for years.  But the Excel add on is really straight forward, especially for accounting people.

But before it’s latest update, the Addon has two major problems.  One is missing the must need Net Income function; another one is a bug in calculating year-end figures.  The latest update get them both fixed. 

Now the only problem for the Addon is performance.  Since each function, like SLGLBAL() is actually a query to DB, if you are constructing a multiple years detail balance sheet comparison, the report can easily has hundreds of function calls, and it will take time to run.  And if you don’t have direct DB connection, say you are Infor SaaS hosting customer,  you need to rely on web service connection, the performance would ever worst. 

But anyway, for most of user that can run report within LAN, the performance is pretty acceptable.

Just for reference, the following tables are used by the Excel based financial tool:

Ledger
Pertot
chart_bp
periods

parms
chart
site

Product Version

In SyteLine < 8.02.00

unitcd{1..4}

In SyteLine > 8.02.00

chart_unitcd{1}

PO Receive (INV PRCV)

Item ABC current on-hand qty = 100, cost = $1.4

PO purchase price $1.17, with 17% VAT tax, material cost = $1. Receive qty = 100

After received, qty = 200, average cost = $1.2

AP Voucher Generation (APV)

AP Payment (APP)

Misc Receive (MRCP)

Current onhand qty = 100, unit cost = $1.4

Misc receive qty = 100, unit cost = $1

After received, qty = 200, unit cost = $1.2

Misc Issue (MRCP)

Current unit cost $1.2, misc issue out 100 pcs

Job Material Issue (INV JOI)

Current unit cost $1.2, issue qty 100 pcs

Job Labor Posting (INV JOL)

Job Completion (INV JFIN)

Order Shipment (INV CSH)

Order Invoicing (INV JOL)

AR Payment (ARP)

Today, many, if not most, US manufacturers have operation in China.  They either build out manufacturing facilities to take advantage of global supply chain, or setup sales/marketing operation to catch China market potential, and may be both.  With US headquarter and operation running in Syteline, it is nature to put Syteline into China branch as well.

But what do you need to know in order to have a successful Syteline implementation in China?

From 1996 to 2004, I was the country manager for Symix/Frontstep/Mapics in China.  I had involved in over 130 Syteline implementations in China, many of those, especially the early ones, are US manufacturing companies.  I was also leading the major Chinese localization project in Syteline, like Chinese Financial Module, which is still the central part of Syteline Chinese country pack today.   After coming back to US in 2006, my Syteline consulting practice still crosses Pacific ocean often.  Just last year, I completed three Syteline 8 implementation projects for US companies in China.  With all that said, I think I am in a good position to talk about what you need to know for a successful Syteline implementation in China.

Let’s run down the list.

1) What additional software components you will need?

If you need the Chinese user interface, and/or if you want to keep your financial book in Syteline, you will need the China Country Pack, which is an extra software component you need to purchase from Infor.  The Country Pack includes the Chinese UI and a Chinese Financial Module to handle the special regulation requirement for accounting system in China.

2) Where should you put your servers?

You have options of centralizing servers in your US headquarter, or putting server locally in your China branch.  They both have pros and cons.
With centralized servers, you can utilize knowledge and resource in US HQ for system administration, so you don’t need a dedicated IT person in your China branch.  You may also leverage your hardware/software investment that you already made in US HQ.
The biggest drawback for centralizing server is performance.  In general, the remote access from China to your US HQ will be slow.  Increase the internet bandwidth in both of your HQ and China branch would help, but may not fully resolve the problem, due to the bottleneck may be in the internet connection between the two countries.  Also, the so called “Great Wall” firewall setup/controlled by Chinese government may sometime block your connection without reason.  Recently, one of my client even experience connection problem with Infor Saas hosting server.  By the way, proxy server is not a solution to get around the Great Firewall, since Syteline would not work at all with Proxy.

So if you do want to centralize your servers in US HQ, do set it up and have your users in China try it out first, to see if they are OK with the connection and performance.

3) How should you setup your multi-sites environment?

The multi-sites structure would of course be based on your company situation.  Normally, if you need to consolidate financial statement, you will need to setup a site DB and an entity DB, both of them are in CNY currency, for your China branch.

When creating DB for your China site, just remember to use CNY as domestic currency and select proper China time zone for it.  By doing that, your China user will see the correct local time zone, even though your server may located in US.

If you are going to do data replication between your US site and China site, ITAR compliance may need to be taking into consideration.  You may need to modify your replication rule.

4) Multi-currency is a must.

You would most likely need to setup multi-currency for your China branch DB, with CNY as domestic currency and USD/others as foreign currency.  When setting up USD currency, make sure to check the “Rate is Divisor” box.  This will reduce the transaction rounding error.

5) What do you need to know about the China VAT tax system?

In some extent, China VAT tax system may be considered simpler than US sales tax system, since there are not that many differ tax jurisdictions as US does, and tax rates do not change as often as US.

But you do need to setup VAT tax system in both sales and buy side.  Tax paid with your purchasing will be credit against to your tax liability in sales side.  Please check out my another post for detail in setting up China VAT tax system.

In that nature of China VAT tax system, a VAT tax invoice you collected from your supplier actually has cash value, just like bank notes.  And that is why Chinese government has tie control on VAT Invoice.  VAT invoice can only printed out from a dedicated tax control machine that issued from government, and the invoice# is pre-assigned and controlled by government.  Syteline invoice# can only used for internal control, not really the one that you can present to your customer.

So, to help accounting people reconcile Syteline invoice# and official VAT invoice#, a modification would be required to put VAT Invoice# into various AR forms, like “Invoice, Debit/Credit Memo”, “AR posted transaction”, and “AR payment quick application”, and may also need in AR reports like “AR Aging”.

6) What is the Chinese Financial Module, and what is that for?

Chinese accounting regulation requires an unique voucher number for each accounting transaction (combined multiple debit/credit entries), something similar to control number today we have in Syteline 7 & 8.  But the early version of Syteline (before and include SL6) don’t have that function, and that was the major driving force for developing Chinese Financial Module back in 2001.

Today, Chinese Financial Module is still crucial for any Syteline implementation in China.  It is pretty much a GL system that allow user to enter/maintain voucher, to print voucher form, and to print Chinese accounting book.  Those are generally required for compliance with Chinese accounting regulation.

In terms of Financial Statement, local Chinese government do have some standard format requirement.  But I have found that with Syteline Excel Addon, it is quite easy to fulfill whatever format requirement is.

7) Other special consideration during implementation.

There are some other special considerations that need to be taken into account during your Chinese implementation.  Here I list a few.  The solution to each of them may vary, depend on company situation.

• Chinese accounting regulation has some standard on top level chart of account setup.  In most case, you company will need to follow that standard.  If you are going to consolidate financial statement to US headquarter, some sort of account mapping will need to setup properly before hand.
• Duty free imported material keep in bound location.  Custom regulation requires that those duty free imported material to be kept in special bound location, and has full tractability to prove that they are only used on products that will export out later.
• Company may be required to declare their earning and pay income tax in monthly base, instead of yearly based in US.  That means you may need to do GL adjustment to move P/L to retain earning in monthly base.
• It is normal accounting practice in China to fully allocate your actual overhead cost into COGS in monthly based.

8) Implementation, local support consideration.

Needless to say, a suitable consulting team is crucial to your implementation project success.  Capabilities you should look for are,

• Understand both western and Chinese business culture and best practice.
• Deliver training and consulting service in both English and Chinese.
• Able to facilitate better communication between US headquarter and China local team
• Provide long term local support.