SytelineMaster

This is coming out of PCWorld.  Looks pretty positive to our Syteline fellow.

Infor Prepares to Roll out ‘Mongoose’ Development Platform

When specifying unit codes in the Excel formulas, to use both a cell reference in addition to hard-coded values, you need to append the two with ‘&’.  For example, if unit code 1 value is in cell $A$1, and you don’t want to include * for each of the three remaining unit codes, the Unit Code parameter would look like: $A$1 & ",*,*,*".  In a SLGL formula, that would look like:

=SLGL(account, period, year, $A$1 & ",*,*,*")

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

There is also a few useful UDF(User Defined Function) in Syteline

ConvDate (@pInputDate  DateType, @pFormat   NVARCHAR(10))

Sample

SELECT ConvDate(GetDate(), ‘MM/DD/YYYY’)

Another useful one, DayEndOf

ALTER FUNCTION [dbo].[DayEndOf] (

  @Date DATETIME

) RETURNS DATETIME

AS

BEGIN

    –  This function takes the input date and extends the time portion forward

    — to the last possible moment of that day, just before midnight of the next day.

    — A null in yields a null out. It is useful for doing

    — high-range comparisons where only the day matters and offers an alternative to the

    — DATEDIFF built-in. The first 4 bytes of a datetime field are the date and

    — the second four the time.

   
    RETURN case when @Date = CONVERT(DATETIME, ‘9999-12-31 23:59:59.998’, 121) then @Date

       else dateadd(ms, -2, dbo.MidnightOf( dateadd(day, 1, @Date) ) )

       end

END

First, determine the current site, after which you must name a configuration, by convention:

DECLARE @Site SiteType
SELECT @Site = site FROM parms

Then determine the current SessionId:

DECLARE @SessionId RowPointerType
SET @SessionId = dbo.SessionIdSp()

Finally, add the procedure code:

BEGIN TRANSACTION
UPDATE coitem
SET due_date = dbo.CalcDueDate(@Parm1, @Parm2)
WHERE coitem.co_num = @CoNum
AND coitem.co_line = @CoLine
AND coitem.co_release = @CoRelease
SET @MyEventParmId = NEWID()
EXEC InsertEventInputParameterSp @MyEventParmId, ‘CoNum’, @CoNum
EXEC InsertEventInputParameterSp @MyEventParmId, ‘CoLine’, @CoLine
EXEC InsertEventInputParameterSp @MyEventParmId, ‘CoRelease’, @CoRelease

DECLARE
@anyHandlersFailed [tinyint],
@result [nvarchar](4000),
@Infobar [nvarchar](4000)
EXEC @Severity = FireEventSp
@eventName = SetCoitemDueDate’,
@configName = ‘SyteLine’,
@sessionID = @SessionID,
@eventTrxId = null,
@eventParmId = @MyEventParmID OUTPUT,
@transactional = 0,
@anyHandlersFailed = @anyHandlersFailed output,
@result = @result output,
@Infobar = @infobar output
IF @Severity > 0
BEGIN
EXEC RaiseError @Infobar, @Severity
ROLLBACK TRANSACTION
END
…
COMMIT TRANSACTION

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)

Something to remember, in programming in multiple site environment, if you have site that has differ time zone than the server time zone, remember to use GetSiteDate function, instead of directly GETDATE().  Below is code sample.

   SET @PrintDate = dbo.GetSiteDate(GETDATE())

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.

1) Update Statistics

You should always run the below command nightly

Exec sp_updatestats

2) Rebuild Index for key tables

It is worth to rebuild index for key tables regularly, like once a week.

DBCC DBREINDEX (ledger)
GO
DBCC DBREINDEX (item)
GO
DBCC DBREINDEX (customer)
GO
DBCC DBREINDEX (matltran)
GO
DBCC DBREINDEX (journal)
GO
DBCC DBREINDEX (ledger_all)
GO
DBCC DBREINDEX (matltran_amt)
GO

3) Defragment Index

Below script will defragment any index with over 20% logical fragmentation.  This should set to run weekly.

/*Perform a ‘USE <database name>’ to select the database in which to run
the script.*/
— Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
— Decide on the maximum fragmentation to allow
SELECT @maxfrag = 20.0
— Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
— Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
— Open the cursor
OPEN tables
— Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
— Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’)
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’)
FETCH NEXT
FROM tables
INTO @tablename
END
— Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
— Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) > 0
— Open the cursor
OPEN indexes
— loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘Executing DBCC INDEXDEFRAG (0, ‘ + RTRIM(@tablename) + ‘,
‘ + RTRIM(@indexid) + ‘) – fragmentation currently ‘
+ RTRIM(CONVERT(varchar(15),@frag)) + ‘%’
SELECT @execstr = ‘DBCC INDEXDEFRAG (0, ‘ + RTRIM(@objectid) + ‘,
‘ + RTRIM(@indexid) + ‘)’
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
— Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
— Delete the temporary table
DROP TABLE #fraglist
GO

4) Other script that should be run regularly

• SLServerRestartSp – This stored procedure runs whenever the database server is
  restarted (since no one is logged in at that time) and performs general cleanup.
• PurgeNextKeySp – Run this stored procedure to clean up the NextKeys table.
  NextKey records are inserted, never updated to get concurrency. This stored
  procedure cleans out the extra rows. DO NOT run this utility while others are using the
  system. The utility will lock users out, but you should log everyone out of the system
  before running this utility.

5) Scripts that can be used to return information related to performance

• sp_who and sp_who2 – The stored procedure sp_who shows what SPID is blocked;
  sp_who2 shows who is blocking.
• sp_helpindex (table_name) – Gives index information on a table.
• DBCC OPENTRAN – Determines whether an open transaction exists within the log.
• DBCC INPUTBUFFER (SPID) – Displays the last statement sent from a client to SQL
  Server.
• dbcc showcontig (table_name) – This would show fragmentation information for a table.