Plex DemandCaster Supply Chain Planning

Great Plains SQL Data Tables

item.txt

Item1:

select
i1.itemnmbr,
itemdesc,
qtyonhnd,
ordrpntqty,
PRCHSNGLDTM as stagingldtme,
mnmmordrqty,
primvndr,
uomschdl,
itmclscd,
currcost,
orderpolicy as inventory,
'B' as makebuy,
mnmmordrqty as casepack,
0 as casecube,
sftystckqty
FROM IV00101 i1
INNER JOIN IV00102 i2 ON
i1.itemnmbr = i2.itemnmbr AND i1.locncode=i2.locncode
WHERE itemtype <> 2
ORDER BY i1.itemnmbr

Item2:

select
i1.itemnmbr,
qtyonhnd
FROM IV00101 i1
INNER JOIN IV00102 i2 ON
i1.itemnmbr = i2.itemnmbr
WHERE itemtype <> 2
AND i2.locncode = ''
ORDER BY i1.itemnmbr

productclass.txt

select
ITMCLSCD as 'code',
ITMCLSDC as 'name'
from IV40400

vendors.txt

select vendorid, vendname
from PM00200

customers.txt

select custnmbr, custname
from RM00101

customerorders.txt

CustomerOrders-open:

Select
header.sopnumbe,
'O' as status,
docdate,
custnmbr,
itemnmbr,
qtyremai*qtybsuom as qtyorder,
reqshipdate,
detail.actlship,
QTYFULFI*qtybsuom as shipqty,
unitprce as salesprice,
voidstts ,
0 as cost
from zzctvwSalesHeaderOpenAndHist header
INNER JOIN zzctvwSalesDetailOpenAndHist detail
ON header.sopnumbe = detail.sopnumbe
WHERE docdate > '2009-1-1'
AND voidstts = 0
AND (detail.soptype = 2 OR detail.soptype = 3)
AND PostedToHist = 'N'

CustomerOrders-closed

Select
header.sopnumbe,
'O' as status,
docdate,
custnmbr,
itemnmbr,
qtyremai*qtybsuom as qtyorder,
reqshipdate,
detail.actlship,
QTYFULFI*qtybsuom as shipqty,
unitprce as salesprice,
voidstts ,
0 as cost
from zzctvwSalesHeaderOpenAndHist header
INNER JOIN zzctvwSalesDetailOpenAndHist detail
ON header.sopnumbe = detail.sopnumbe
WHERE docdate > '2009-1-1'
AND voidstts = 0
AND (detail.soptype = 2 OR detail.soptype = 3)
AND PostedToHist = 'N'

vendororders.txt

select
p1.ponumber,
p2.polnesta as postatus,
p1.docdate,
p2.vendorid,
p2.itemnmbr,
p2.qtyorder,
p2.prmdate,
SUM(p3.qtyshppd) as qtyshppd,
MAX(p3.daterecd) as daterecd
from POP10100 p1
INNER JOIN POP10110 p2 ON p1.ponumber = p2.ponumber
LEFT OUTER JOIN POP10500 p3 ON p2.ponumber = p3.ponumber AND p2.ord = p3.polnenum
WHERE p1.docdate > '2005-1-1'
AND p2.polnesta < 6
GROUP BY p1.ponumber, p2.polnesta, p1.docdate, p2.vendorid, p2.itemnmbr, p2.qtyorder, p2.prmdate, p2.ord

Locations.txt (for DRP)

Select
dbo.IV40700.LOCNCODE as code,
dbo.IV40700.LOCNDSCR as name
FROM dbo.IV40700 INNER JOIN
dbo.SOP30300 ON dbo.IV40700.LOCNCODE = dbo.SOP30300.LOCNCODE
GROUP BY dbo.IV40700.LOCNCODE, dbo.IV40700.LOCNDSCR
HAVING (dbo.IV40700.LOCNCODE <> '') AND (dbo.IV40700.LOCNDSCR <> '')

0 Comments

Add your comment