Things to consider when moving from job orders to production schedules

March 21st, 2016 No comments

If you are using the Syteline job order system for tracking and reporting production and are thinking of changing to use production schedules, here are some things to consider:

1) An item must be standard costed before you can enter it on a production schedule.  If you are currently using actual costing, this would be a major change and there is a lot to be considered…accuracy of your routings/BOMs, creating and setting up all of the variance accounts, etc.

2) When using production schedules, to get material, labor, labor overhead and machine overhead issued or posted, you either have to backflush it all or enter work center material, work center labor and work center machine transactions for all of it.  Most users opt for backflushing.  If you choose backflushing, you have to set up all of the current materials and operations to backflush (if they aren’t already) for each parent item that you will be processing with production schedules.

3) You report production with production schedule complete transactions.  You  would need to determine if you want to enter a PS complete transaction to report production at each step in the routing, selected steps, or only the last step and then set the operation control points accordingly.   For example, if you want to report at each operation, you would want to make them all control points.  If you only want to enter a transaction against the last operation, it would need to be a control and all of the others must not be so the entire routing/BOM is backflushed.

4) If you are currently using standard costing on jobs, you would need to be aware that the timing of variances are different with production schedules.  With jobs, labor rate, labor usage and overhead usage variances are recorded as transactions are posted and material usage variances are booked when jobs are closed.  With production schedules, no variances are recognized as transactions are posted. Differences between “actual” costs (what is posted into WC WIP based on the PS routing/BOM) and “earned” costs (what is removed from WC WIP based on the standard routing/BOM) remains in the WC WIP buckets until you run the EOP Costing utility.  At that time, all variances are posted and the WC WIP buckets are cleared.

5) A major difference from jobs is that reporting for jobs is job or item centric.  With production schedules, cost reporting is all work center centric.  i.e. they show the labor, material, overhead, etc. posted against work centers.

6) You should absolutely setup some test production schedules in a demo or test database, process them, review the accounting entries, check out the available reports, run EOP Costing, etc. to make sure you are very clear on the functionality before switching from jobs

Categories: Application Tags:

SQL query to check locking process.

May 17th, 2015 1 comment

Here is a useful query to check user section that cause blocking / locking.

SELECT
d1.session_id,
d3.[text],
d1.login_time,
d1.login_name,
d2.wait_time,
d2.blocking_session_id,
d2.cpu_time,
d1.memory_usage,
d2.total_elapsed_time,
d2.reads,d2.writes,
d2.logical_reads,
d2.sql_handle
FROM sys.dm_exec_sessions d1
JOIN sys.dm_exec_requests d2 ON d1.session_id=d2.session_id
CROSS APPLY sys.dm_exec_sql_text(d2.sql_handle) d3

Categories: Development, SQL Tags: ,

Applying patch on a modified form.

February 19th, 2015 1 comment

 

  1. Read the cover letter, highlights forms that have been modified, and Sps that have EXTGEN on.

  2. For EXTGEN SPs, will need to manually apply the new changes.

  3. For modified Forms, make a backup of the Form scripts use FormSync.

  4. Apply the patch to unused configuration, such as Demo.

  5. Merge the new vendor version of form from Demo to target configuration, form by form.

    1. Start FormSync with

    2. Filter with single form.

    3. Do not apply change to any other objects.

    4. Keep other object unchanged.

Remember to uncheck all the filters here.

  1. Run Synchronization

  1. Test the newly merged, modified form.  May need to work out conflict if there is any.

Categories: Application Tags: , ,

Some notes on Job Planned Cost

February 19th, 2015 No comments

1) The “Job BOM Cost Roll Up” utility and the “Update Planned Cost” button in Job form, Cost Detail tab are pretty much doing the same thing, they will update the material cost in job material.

ScreenHunter_04 Feb. 19 09.33

 

 

 

 

 

 

ScreenHunter_05 Feb. 19 09.38

 

2) Syteline system didn’t maintain a field for total job planned cost, you have to run job costing reports like “Job Cost Various Report” to see the total planned cost.

3) Job BOM Cost Roll Up use the item Unit Cost, and Current BOM Cost Roll Up use item Current Unit Cost.  So even you just copy the Job BOM from Current BOM, the current cost of job item may be differ than the job planned cost.

4) Cost roll up utilities rely on Unit Cost and Current Unit Cost maintained in Item Cost.  For a new item, say it is a purchase item, Syteline system would not assign Unit Cost and Current Unit Cost, until you conduct the first inventory transaction for the item (the transaction can be a PO receive, a Job complete receive or Misc receipt).   So your job material plan cost would not include these new materials.  Even you create a PO for the new item, system would not save the PO cost back into item cost, until you actually receive that PO.   However, for a new item, with or without a PO, you can use Item Cost form, Cost Maint tab to assign initial Unit Cost and Current Unit Cost.

Inline image 2

You will see that the Unit Cost fields are open for edit for this new item.  Once you conduct a receiving transaction and has qty on hand, the Unit Cost fields are No Longer editable.  It will be maintained by the system.
Note 1: these initial unit costs and current unit costs will have no effect on your inventory as you have qty-on-hand of 0.  These purely just for your job plan cost.
So for a new purchase item, it is suggested that you a) setup vendor contract price, b) setup initial unit cost and current unit cost in item cost maintenance, c) do cost roll up.
5) There is checkbox in PO line form, called “Update Job Material Unit Cost”, this only works for cross-referenced job material.  It will update the job material cost, when the PO cost change.

Setup and process 1099 in Syteline

February 17th, 2015 No comments

1) Setup Tax Parameter, enter your own company’s Tax ID.

clip_image002[7]

2) In your Tax System setup, make sure the Tax ID Prompt Location set to included Vendor (Vendor or Both).

clip_image004[5]

3) In Accounts Payable Parameters, under 1099 tab, enter your company information. These are info will be print on your 1099 form.

clip_image006[5]

4) Now, for each vendor that you need to send 1099, enter their Tax ID.

clip_image008[5]

5) Part of your year end procedure, you should run the “Rebalance Vendor Payment History” utility.

clip_image010[5]

This will update the Payment History tab on your Vendors form.

clip_image012[5]

6) Now you are ready to print the 1099 form.

clip_image014[5]

Categories: Application, Implementation Tags: , ,

SQL Query Syteline 9

February 17th, 2015 No comments

In Syteline 9, there is one significant database schema change.  All major tables like item are replaced with a view and _mst table.  There are some context variables need to be set, before you can run query against to the views.  Otherwise query to the view will return no data.  And here is the variable declaration, with sample query after that.

DECLARE @Br NVARCHAR(8)

SET @Br = ‘SiteName’’

DECLARE @Context NVARCHAR(100)

SET @Context = @Br

DECLARE @BinVar VARBINARY(128)

SET @BinVar = CONVERT (VARBINARY(128), @Context)

set CONTEXT_INFO @BinVar

 

 

select co.slsman,co.co_num,co.type, co.order_date, coitem.co_line, coitem.item, coitem.qty_ordered_conv,coitem.price_conv,co.exch_rate

, ca.name, coitem.qty_ordered_conv * coitem.price_conv ‘Amount’, coitem.co_release

from coitem

join co on co.co_num = coitem.co_num

left join custaddr ca on ca.cust_num = co.cust_num and ca.cust_seq = co.cust_seq

where isnull(co.slsman,”) = ”

Update 2/10/2015

You may now just call this Sp.

DECLARE @Infobar InfobarType;
EXEC [dbo].[SetSiteSp] ‘CCSSL’, @Infobar OUTPUT

Categories: Development, SQL Tags:

Overhead Rate in Syteline

February 16th, 2015 No comments

Fixing YTD number in Item warehouse.

February 3rd, 2015 No comments

Part of the Syteline year end procedure is to run an utility to reset the YTD number in item warehouse form.  This needs to be done in timely manner.  If you forget to run it during your year-end, there is no rebalance utility to recalculate that these YTD number in Syteline.

The below small script is to help re-calculate and update that YTD numbers.

——- Checking ———-
select top 100
iw.item,
iw.whse,
(select SUM(qty) from matltran where item = iw.item and whse = iw.whse and trans_date > ‘1/1/2015’ and
((trans_type = ‘R’ and ref_type <> ‘K’ and ref_type <> ‘S’) — PO receiptor
or
(trans_type = ‘W’ and ref_type = ‘P’) — PO return.
) ) ‘YTD Purchase’,
(select SUM(qty) from matltran where item = iw.item and whse = iw.whse and trans_date > ‘1/1/2015’ and
((trans_type = ‘S’) — CO Shipment
or
(trans_type = ‘W’ and ref_type = ‘O’) — CO return.
) ) ‘YTD Sold’
from itemwhse iw
where item = ‘512653’

———– Actual Update ————-
update itemwhse
set qty_pur_ytd = (select SUM(qty) from matltran where item = itemwhse.item and whse = itemwhse.whse and trans_date > ‘1/1/2015’ and
((trans_type = ‘R’ and ref_type <> ‘K’ and ref_type <> ‘S’) — PO receiptor
or
(trans_type = ‘W’ and ref_type = ‘P’) — PO return.
) ),
qty_sold_ytd = (select SUM(qty) from matltran where item = itemwhse.item and whse = itemwhse.whse and trans_date > ‘1/1/2015’ and
((trans_type = ‘S’) — CO Shipment
or
(trans_type = ‘W’ and ref_type = ‘O’) — CO return.
) )

Send Email from SQl Database

August 7th, 2014 No comments

This is a great blog post regarding how to setup SQL Mail.  Need to keep for reference.

SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database

Categories: SQL Tags: ,

Some suggested routine after a Syteline Upgrade

July 6th, 2014 No comments

They are not stated in Syteline manual, but from my experience, I would suggest the following routine after upgrade a Syteline DB.

1) Regenerate _all table, by using “Update _All Tables” form.

2) Regenerate trigger, by using “Trigger management” form.

3) Regenerate Replication Triggers, if you are using multi-site replication.

4) Drop all the _tt and _tmp tables.  These would force system to rebuild those temp tables.  Please refer to Information on the different temporary tables in a SyteLine database for a script to drop all those temp table.