SytelineMaster

 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>’