SytelineMaster

Sometimes a user will accidentally type in a wrong number in the starting check number in A/P check printing and posting. If that check number is higher than the last check number used, the system will prompt to void all the check numbers in between. If that prompt is answered "ok", this will result in a large number of voided checks in the Bank Reconciliation and the system will not allow those numbers to be used.

This problem can be fixed in one of two ways.

The first is to go into the bank reconciliation and delete those voided checks one by one until they get back to the correct starting check number.

The second way is to use SQL Query to programmatically delete those voided checks.

 

/* This script is used to delete a range of check numbers from the glbank table */

declare @commit int, @rows int, @check_num GlCheckNumType

——————————————————————-

/* INSTRUCTIONS: CHANGE THE @commit VARIABLE TO VIEW OR COMMIT CHANGES

AND ENTER A MAX CHECK NUMBER. */

SET @commit = 0 — 0=View; 1=Commit the update changes

SET @check_num = 1055 — Enter highest check number wanted in SL

——————————————————————-

— VIEW CHECK RECORDS THAT WILL BE DELETED

select * from glbank

where check_num > @check_num and type = ‘C’

set @rows=@@rowcount

if @commit=0

begin

if @rows > 0 select [Results]=’Viewing record(s) to be deleted.’

else select [Results]=’No matching records found.’

end

else

begin

— DELETE CHECK RECORDS

delete from glbank

where check_num > @check_num and type = ‘C’

select [Results]= dbo.cstr(@@rowcount) + ‘ Glbank record(s) deleted.’

end

 

Progress version

for each (database name).glbank where check-num > xxxxxxxxx and type = "C":

display glbank with 2 col.

Run the query. If the checks that display are the ones that need to be deleted, then run the following:

for each (database name).glbank where check-num > xxxxxxxx and type = "C":

delete glbank.

This will reset the starting check number back to the original check.

Syteline multi-site supports centralized AR payment process.  Here are the basic set-up steps

1.  Site Groups

Create a site group that contains all sites that will be sharing cross-site A/R Payments

NOTE:  All sites within the group must have the same domestic currency

2.  Replication Rules

Set up Replication Rule for category A/R for each site within the Site Group

Interval Type = Transactional

3.  Inter-Site Parameters

Add record for all From Site/To Site combinations

Populate Inter-Site Accounts on Payment Tab

4.  Accounts Receivable Parameters

Populate the Site Group field

Once the Basic Set-Up has been performed, you should be able to see invoices from all sites within the Site Group in the A/R Quick Payment Application grid.   However, Open Credits from remote sites will not appear in the A/R Quick Payment grid.   Those credits should be applied to invoices in remote site prior to entering payment in Payment site.

Example of journal entries for cross-site payment posting:

Three sites exist for Entity XYZ. Site A is designated as the centralized collection site where all customer payments are collected. If Site A receives a $300 payment from a customer to pay 3 invoices, each from a different site, the following journal entries must occur.

1. Invoice customer – $100 from each entity.

Site A transactions:	Debit	Credit
A/R	$100
Sales		$100

Site B transactions:	Debit	Credit
A/R	$100
Sales		$100

Site C transactions:	Debit	Credit
A/R	$100
Sales		$100

1. Post payment of $300 – distribute $100 to each entity.

Site A transactions:	Debit	Credit
Cash	$300
A/R		$100
Inter-Site Liab (Entity B)		$100
InterEntity Liab (Entity C)		$100

Site B transactions:	Debit	Credit
InterEntity Asset	$100
A/R		$100

Site C transactions:	Debit	Credit
InterEntity Asset	$100
A/R		$100

NOTE: To reverse the inter-site transactions; each site will create a manual entry when corporate pays cash to the sites.

To repair an item that you have received through a return material authorization (RMA), you create a rework job order (or estimate job order). The rework order’s bill of material includes the item as the end item and as a component. This recursive BOM structure is allowed only on a bill of material for a rework order.

After receiving a returned, defective item (through an RMA return transaction), follow these steps to create a rework order and repair the item:

1. Open the Job Orders (or Estimate Job Orders) form and create a new order. We recommend you decide on a special prefix to use in the job numbers for rework orders. Using a special prefix will allow you to filter your job reports to include only rework orders, if necessary.

2. In the Item field, select the returned item that you want to repair.

3. Select the Rework field. Selecting this field will allow this job’s bill of material to be recursive.

4. Enter or select the appropriate values for the job in the remaining fields as desired.

5. Enter the job operations (click the Operations button to open the Job Operations form).

6. Define the job bill of material (click the Materials button to open the Job Materials form). Enter the item to be repaired as a component, along with any other materials necessary for the repair.

7. On the Job Material Transactions form, issue the item to be repaired (from its non-nettable location where you originally received the RMA order) to the rework order.

8. Issue any other needed materials to the rework order.

9. Enter job operation transactions to record setup, labor or machine time, and overhead, and to move the completed, reworked item to inventory.

NOTES:

• Only job orders and estimate job orders can be rework orders. You can also create a rework job order on the Engineering Workbench form (if you copy an estimate job to a job and the estimate job is a rework order).

• When defining the bill of material for the rework order, the rework item can be recursive only two levels down in the BOM. For example, if end-item A is made from material B, A and B can be the same item. However, if A is made from B and B is made from C, C cannot be the same item as A.

• Once you add a recursive BOM to the rework order, you cannot clear the Rework field on that job or estimate job (that is, you cannot change the rework order back into a normal job or estimate job).

• When issuing the returned material to the rework order, you must enter the job material transaction on the Job Material Transactions form. You cannot use backflushing and you cannot use the Post Material Issues option on the Job Pick List report.

• For MRP and APS planning, the end item of the rework order is considered as a planned supply/receipt. The same (recursive) item in the bill of material is not considered as a demand/requirement. If the rework order has no routing/BOM, MRP and APS ignore the rework order completely.

• If you use Standard costing for a rework item, you may encounter large routing and/or material usage variances, because the routing and materials used to repair the item may vary greatly from those used to build the original item.

 Order Types

The ORDTYPE field in the ORDER table distinguishes different types of orders. The value in this field affects the database data you need to retrieve for various APS functions.

10 reserved by the Scheduler to identify planned manufacturing orders

50 supply delivery (reserved for internal use)

100 released, scheduled work order

200 customer order

210 EDI order

220 Web order

230 MPS order

240 firmed work order

245 component demand for firmed scheduled job (used in Infor APS)

250 released work order (not scheduled)

255 component demand for released scheduled job (used in Infor APS)

260 production order

270 transfer order

300 forecast order

310 replenishment/safety stock order

Retrieving the Projected Completion Date for Order X

After an order has been planned, you can retrieve the projected completion date.

SELECT CALCDATE

FROM ORDPLAN000

WHERE ORDERID = ‘X’

Retrieving Planned Work Orders

SELECT a.ORDERID, a.MATERIALID, d.QUANTITY, a.STARTDATE, a.ENDDATE

FROM MATLPLAN000 a, INVPLAN000 b, ORDER000 c, JOBPLAN000 d

WHERE a.MATLTAG = b.MATLTAG

AND a.ORDERID = c.ORDERID

AND a.MATLTAG = d.MATLTAG

AND d.SEQNO = 1

AND b.SCHTYPE = 6

AND ((a.PMATLTAG <> 0)

OR (c.ORDTYPE <> 100 AND c.ORDTYPE <> 240 AND c.ORDTYPE <> 250))

The final condition in the where clause results in the exclusion of planned manufacture for the end items of work orders.

Retrieving Planned Purchase Orders

SELECT a.ORDERID, a.MATERIALID, b.DEMAND, b.SCHDATE,

(c.FLEADTIME + b.DEMAND * c.VLEADTIME) AS LEADTIME

FROM MATLPLAN000 a, INVPLAN000 b, MATL000 c

WHERE a.MATLTAG = b.MATLTAG

AND a.MATERIALID = c.MATERIALID

AND b.SCHTYPE IN (7,8)

Retrieving Late Orders for Order Type X

SELECT a.ORDERID, a.DUEDATE, b.CALCDATE

FROM ORDER000 a, ORDPLAN000 b

WHERE a.ORDERID = b.ORDERID

AND b.CALCDATE > a.DUEDATE

AND a.ORDTYPE = X

Retrieving Out-of-Range Orders

(Out-of-range orders have a promise date later than the planning horizon.)

SELECT a.ORDERID, a.DUEDATE

FROM ORDER000 a, ALTPLAN b

WHERE b.ALTNO = 0

AND a.ORDTYPE <> 10

AND DATEDIFF(hh, GETDATE(),a.DUEDATE) > b.PLANHORIZ

Retrieving Inventory Exceptions

Inventory exceptions are unused supply orders and minimum-inventory violations. To identify inventory exceptions, you must retrieve the beginning inventory level, all inventory events, past-due exceptions, expedited exceptions, and supply tolerance exceptions for a given part.

Retrieve beginning inventory for part X

SELECT STARTLEV

FROM MSLPLAN000

WHERE MATERIALID = ‘X’

Retrieve all events affecting the inventory level of part X

SELECT b.SCHDATE, b.SUPPLY, b.DEMAND,

dbo.DecodeSCHTYPE(b.SCHTYPE) as SCHTYPE,

dbo.DecodeSCHFLAGS(b.SCHFLAGS) as SCHFLAGS

FROM MATLPLAN000 a, INVPLAN000 b

WHERE a.MATLTAG = b.MATLTAG

AND a.MATERIALID = ‘X’

ORDER BY b.SCHDATE

Retrieve past-due exceptions

SELECT b.ORDERID, b.DELVDATE, b.AMOUNT, (a.FLEADTIME + a.VLEADTIME * b.AMOUNT) AS

LEADTIME

FROM MATL000 a, MATLDELV b

WHERE a.MATERIALID = b.MATERIALID

AND b.DELVDATE < GETDATE()

AND a.MATERIALID = ‘x’

UNION ALL

SELECT b.ORDERID, b.DUEDATE AS DELVDATE, b.ORDSIZE AS AMOUNT, (a.FLEADTIME +

a.VLEADTIME * b.ORDSIZE) AS LEADTIME

FROM MATL000 a, ORDER000 b

WHERE a.MATERIALID = b.MATERIALID

AND (b.FLAGS & 1) <> 0

AND b.DUEDATE < GETDATE()

AND a.MATERIALID = ‘x’

Retrieve expedited exceptions

SELECT c.ORDERID, b.SCHDATE, b.DEMAND – b.SUPPLY AS AMOUNT, (a.FLEADTIME +

a.VLEADTIME * b.DEMAND) AS LEADTIME

FROM MATL000 a, INVPLAN000 b, MATLPLAN000 c

WHERE a.MATERIALID = c.MATERIALID

AND b.MATLTAG = c.MATLTAG

AND (b.SCHTYPE = 12 OR (b.SCHFLAGS & 16) <> 0)

AND a.MATERIALID = ‘x’

Retrieve supply tolerance exceptions

SELECT b.ORDERID, c.SCHDATE, c.DEMAND, (a.FLEADTIME + a.VLEADTIME * c.DEMAND) AS

LEADTIME, e.SCHDATE AS DELVDATE

FROM MATL000 a

INNER JOIN MATLPLAN000 b ON a.MATERIALID = b.MATERIALID

INNER JOIN INVPLAN000 c ON b.MATLTAG = c.MATLTAG AND c.SCHTYPE = 3

INNER JOIN MATLPLAN000 d ON c.SUPMATLTAG = d.MATLTAG

INNER JOIN INVPLAN000 e on d.MATLTAG = e.MATLTAG AND e.SCHTYPE = 5

WHERE c.SCHDATE < e.SCHDATE

AND a.MATERIALID = ‘x’

Query 1

select m.matltag, m.orderid, i.schdate, i.supply, i.demand,

m2.orderid as suporder, dbo.DecodeSCHTYPE(i.schtype) as schtype,

dbo.DecodeSCHFLAGS(i.schflags) as schflags, m.pmatltag,

m.startdate, m.enddate, m.passcd, m.passiters,

dbo.DecodeMPNFLAGS(m.flags) as flags

from matlplan000 m

inner join invplan000 i on m.matltag = i.matltag

left join matlplan000 m2 on i.supmatltag = m2.matltag

where m.materialid = ‘<item id>’

order by m.matltag, i.schtype

Query 2

select *

from MSLPLAN000

where materialid = ‘<item id>’

Query 3

select *

from MATL000

where materialid = ‘<item id>’

ITEM MAINTENANCE WINDOW – GENERAL TAB

 

Cost Type: item.cost

Cost Method: item.cost-method

(Standard) Unit Cost: item.unit-cost

Current Unit Cost: item.cur-u-cost

Notes:

• The Cost Type can be either Actual or Standard with the stored value being "A" or "S".

• The Cost Method and the associated field values can be (L)IFO, (F)IFO, (A)verage, (S)pecific, (C)Standard.

• The label and usage of the Unit Cost field varies depending on the item’s Cost Type and Method. If the item is standard-costed, the label "Standard Unit Cost". Otherwise, it is just "Unit Cost". Usage of the field is the following:

1. Standard cost if the item’s Cost Type is Standard

2. Average cost if the item is Actual/Average

3. Cost at the top of the stack (next issue cost) if Actual/FIFO

4. Cost at the bottom of the stack if Actual/LIFO

5. Last receipt cost if Actual/Specific

6. This field is primarily informational if Actual with LIFO, FIFO or Specific as the actual cost data used when for the item is stored in the LIFO/FIFO stacks or the item’s locations (or lots) if the item is Specific.

• The Current Unit Cost is used as follows:

  1. For purchased items: Holds the Purchased Current Unit Cost fields from the Cost Maintenance screen. That can be manually entered and may be updated by PO receiving (depending on how the Update Current Cost PO parameter is set).

  2. For manufactured items: Holds the Manufactured Current Unit Cost from the Cost Maintenance screen. That is the unit cost for the item as calculated based on it’s current routing and BOM. Updated when you run the BOM Cost Rollup or do maintenance to the item’s current routing/BOM.

ITEM MASTER COST MAINTENANCE WINDOW – COST DETAIL TAB

Assembly column:

Setup: item.asm-setup

Run: item.asm-run

Material: item.asm-matl

Tool: item.asm-tool

Fixture: item.asm-fixture

Other: item.asm-other

Fixed: item.asm-fixed

Variable: item.asm-var

Outside: item.asm-outside

Accumulated column:

Setup: item.comp-setup

Run: item.comp-run

Material: item.comp-matl

Tool: item.comp-tool

Fixture: item.comp-fixture

Other: item.comp-other

Fixed: item.comp-fixed

Variable: item.comp-var

Outside: item.comp-outside

Unit Costs area:

Current Unit Cost: item.cur-u-cost (same as on I/M general screen)

(Standard) Unit Cost: item.unit-cost (same as on I/M general screen)

Cost Totals area:

Subtotal: Calculated as the sum of the assembly column

Material Subtotal: item.sub-matl

Assembly Total: Calculated as Subtotal plus Material Subtotal

Accumulated Total: Calculated as the total of the Accumulated column

Notes:

• The ASSEMBLY column is the cost of the item’s current operations and of purchased materials in its current BOM.

• The ACCUMULATED columns is the ASSEMBLY values plus the cost of any manufactured items which exists in the item’s current BOM.

• The BOM Cost Rollup rolls costs from the bottom of your indented BOMs up and populates these columns.

• They are updated on-the-fly when you make manual changes to the item’s current routing and BOM. Those updates just reflect the changes you are making. If lower level items’ costs change, you must run the BOM Cost Rollup to have those changes rolled up into parent items.

• The ECN posting process does not change these costs on-the-fly. Those changes are not reflected until the BOM Cost Rollup is run.

• For purchased items, the cost being rolled from that item up into its parent items is also put into the Material row of both columns of this screen when you run the BOM Cost Rollup. That will be the item’s Purchased Current Unit Cost if it is an actual-costed item or if it is standard-costed and you answer No to the "Use Std Cost for Purchased Items" option. It will be the item’s standard cost if you answer Yes to that option.

ITEM MASTER COST MAINTENANCE WINDOW – COST MAINTENANCE TAB

Unit Cost area:

Material Cost: item.matl-cost

Material: item.unit-mat-cost

Freight: item.unit-freight-cost

Duty: item.unit-duty-cost

Brokerage: item.unit-brokerage-cost

Labor Cost: item.lbr-cost

Fix Ovhd Cost: item.fovhd-cost

Var Ovhd Cost: item.vovhd-cost

Outside Cost: item.out-cost

Total Unit Cost: item.unit-cost (same as on I/M General screen)

Manufactured Current Unit Cost area:

Cur Material Cost: item.cur-matl-cost

Cur Labor Cost: item.cur-lbr-cost

Cur Fix Ovhd Cost: item.cur-fovhd-cost

Cur Var Ovhd Cost: item.cur-vovhd-cost

Cur Outside Cost: item.cur-out-cost

Cur Unit Cost: Calculated as sum of the above fields

Purchased Current Unit Cost area:

Material: cur-mat-cost

Freight: cur-freight-cost

Duty: cur-duty-cost

Brokerage: cur-brokerage-cost

Cur Unit Cost: Calculated as sum of the above fields

Notes:

• The unit-*-cost fields under Material Cost will only be filled if the item is purchased with Cost Type of Standard.

• The item’s Current Unit Cost on the general screen (item.cur-u-cost) will hold either the sum of the Purchased Current Unit Cost fields if the item has a Source of Purchased or Transferred, or the Manufactured Current Unit Cost fields if the Source is Manufactured.

• The Purchased Current Unit Cost is updated when you process a PO receipt if the "Update Current Cost" PO parameter is set to Last or Average.

• The Manufactured Current Unit Cost is populated by the BOM Cost Rollup utility and is updated on-the-fly when you make manual changes to the item’s current routing or BOM.

• For Standard-costed items, the Unit Cost can only be manually changed if the item has zero on hand and has no transactions in the material transaction table. Otherwise, it can only be changed by running the Roll Current Cost to Std Cost utility.

• For Actual-costed items, the Unit Cost can only be updated if it has a Cost Method of Average since that is where the item’s average cost is stored. If the item is LIFO, FIFO or Specific costed, the item’s cost data is stored in other tables and this field can not be updated since it is informational only.

ITEM MASTER COST MAINTENANCE WINDOW – COMPARE TAB

Current column:

The "item.comp-" fields listed above as being on the Cost Detail tab are displayed in the Current column of this screen. The Total is calculated by the program.

Standard column:

Setup: frzcost.comp-setup

Run: frzcost.comp-run

Material: frzcost.comp-matl

Tool: frzcost.comp-tool

Fixture: frzcost.comp-fixture

Other: frzcost.comp-other

Fixed: frzcost.comp-fixed

Variable: frzcost.comp-var

Outside: frzcost.comp-outside

Total: Calculated by the program

Unit Costs area:

Current Unit Cost: item.cur-u-cost (same as on I/M general screen)

(Standard) Unit Cost: item.unit-cost (same as on I/M general screen)

Effective Dates:

Current: job.rollup-date

Standard: frzcost.freeze-date

Notes:

• An frzcost record is created when you run the Roll Current Cost to Std Cost utility for an item.

• The rollup-date is from the item’s current job and is the last time the BOM Cost Rollup processed the item. When you first add a current operation for an item, the system creates a behind-the-scenes job record to which to link the item’s current routing and BOM. That rollup-date is updated each time the BOM Cost Rollup is run.

• The freeze-date is from the frzcost record and is the last time the Roll Current Cost to Std Cost utility was run for the item.

ITEM MASTER COST MAINTENANCE WINDOW – STD COST TAB

Assembly column:

The fields shown are all of the fields that begin "asm-" found in the frzcost table.

Accumulated column:

The fields shown are all of the fields that begin "comp-" found in the frzcost table.

Unit Costs area:

Current Unit Cost: item.cur-u-cost (same as on I/M general screen)

(Standard) Unit Cost: item.unit-cost (same as on I/M general screen)

Effective Dates:

Current: job.rollup-date

Standard: frzcost.freeze-date

Cost Totals area:

Subtotal: Calculated as sum of the frzcost.asm-* fields

Material Subtotal: frzcost.sum-matl

Assembly Total: Calculated as sum of Assembly column plus the Material Subtotal

Accumulated Total: Calculated as sum of Accumulated column

Notes:

• The rollup-date which is from the item’s standard job. When you run the Roll Current Cost to Std Cost utility, the system creates a standard job. The rollup-date in the standard job is set to the rollup date in the current job and represents the last time the BOM Cost Rollup utility processed the item before Roll Current Cost to Std Cost was run.