Plex DemandCaster Supply Chain Planning

Great Plains SQL Data Tables - Alternate

item.txt

ALTERNATE Item1:

SELECT i2.LOCNCODE AS location ,
i1.ITEMNMBR ,
i1.ITEMDESC ,
i2.QTYONHND ,
i2.ORDRPNTQTY ,
i2.PRCHSNGLDTM AS stagingldtme ,
i2.MNMMORDRQTY ,
i2.PRIMVNDR ,
i1.UOMSCHDL ,
i1.ITMCLSCD ,
i1.CURRCOST ,
i2.ORDERPOLICY AS inventory ,
'B' AS makebuy ,
i2.MNMMORDRQTY AS casepack ,
0 AS casecube ,
i2.SFTYSTCKQTY ,
0 AS unitprice ,
0 AS storage
FROM
tpspt.dbo.IV00101 AS i1 INNER JOIN
tpspt.dbo.IV00102 AS i2
ON i1.ITEMNMBR
=
i2.ITEMNMBR
WHERE i1.ITEMTYPE <> 2
AND i2.LOCNCODE
=
'0IN'
UNION
SELECT i2.LOCNCODE AS location ,
i1.ITEMNMBR ,
i1.ITEMDESC ,
i2.QTYONHND ,
i2.ORDRPNTQTY ,
i2.PRCHSNGLDTM AS stagingldtme ,
i2.MNMMORDRQTY ,
i2.PRIMVNDR ,
i1.UOMSCHDL ,
i1.ITMCLSCD ,
i1.CURRCOST ,
i2.ORDERPOLICY AS inventory ,
'B' AS makebuy ,
i2.MNMMORDRQTY AS casepack ,
0 AS casecube ,
i2.SFTYSTCKQTY ,
0 AS unitprice ,
0 AS storage
FROM
TPEUR.dbo.IV00101 AS i1 INNER JOIN
TPEUR.dbo.IV00102 AS i2
ON i1.ITEMNMBR
=
i2.ITEMNMBR
WHERE i1.ITEMTYPE <> 2
AND i2.LOCNCODE
=
'0EU'
UNION
SELECT i2.LOCNCODE AS location ,
i1.ITEMNMBR ,
i1.ITEMDESC ,
i2.QTYONHND ,
i2.ORDRPNTQTY ,
i2.PRCHSNGLDTM AS stagingldtme ,
i2.MNMMORDRQTY ,
i2.PRIMVNDR ,
i1.UOMSCHDL ,
i1.ITMCLSCD ,
i1.CURRCOST ,
i2.ORDERPOLICY AS inventory ,
'B' AS makebuy ,
i2.MNMMORDRQTY AS casepack ,
0 AS casecube ,
i2.SFTYSTCKQTY ,
0 AS unitprice ,
0 AS storage
FROM
GICAN.dbo.IV00101 AS i1 INNER JOIN
GICAN.dbo.IV00102 AS i2
ON i1.ITEMNMBR
=
i2.ITEMNMBR
WHERE i1.ITEMTYPE <> 2
AND i2.LOCNCODE
=
'0MT';

ALTERNATE Item2:

SELECT
i2.LOCNCODE as location,
i1.itemnmbr,
qtyonhnd
FROM
IV00101 i1 INNER JOIN IV00102 i2
ON i1.itemnmbr
=
i2.itemnmbr
WHERE itemtype<>2
AND i2.locncode='0IN'
UNION
SELECT
i2.LOCNCODE as location,
i1.itemnmbr,
qtyonhnd
FROM
TPEUR.DBO.IV00101 i1 INNER JOIN TPEUR.DBO.IV00102 i2
ON i1.itemnmbr
=
i2.itemnmbr
WHERE itemtype<>2
AND i2.locncode='0EU'
UNION
SELECT i2.LOCNCODE as location,
i1.itemnmbr,
qtyonhnd
FROM
GICAN.DBO.IV00101 i1 INNER JOIN GICAN.DBO.IV00102 i2
ON i1.itemnmbr
=
i2.itemnmbr
WHERE itemtype<>2
AND i2.locncode='0MT'

productclass.txt

ALTERNATE PRODUCT CLASS

select
ITMCLSCD as 'code',
ITMCLSDC as 'name'
from IV40400
UNION
select
ITMCLSCD as 'code',
ITMCLSDC as 'name'
from TPEUR.DBO.IV40400
UNION
select
ITMCLSCD as 'code',
ITMCLSDC as 'name'
from GICAN.DBO.IV40400

vendors.txt

ALTERNATE VENDOR

select vendorid, vendname
from PM00200
UNION
select vendorid, vendname
from TPEUR.DBO.PM00200
UNION
select vendorid, vendname
from GICAN.DBO.PM00200
select vendorid, vendname
from TPEUR.DBO.PM00200

customers.txt

ALTERNATE CUSTOMERS:

select distinct [USERDEF2] as custnmbr,
[USERDEF2] as custname
from RM00101
UNION
select distinct [USERDEF2] as custnmbr,
[USERDEF2] as custname
from TPEUR.DBO.RM00101
UNION
select distinct [USERDEF2] as custnmbr,
[USERDEF2] as custname
from GICAN.DBO.RM00101

CUSTOMER GROUP

SELECT
[CUSTNAME] as cust
,[USERDEF2] as groupnm
FROM [dbo].[RM00101]

customerorders.txt

ALTERNATE CustomerOrders-Open

SELECT
detail.locncode as location,
header.SOPNUMBE,
'O' AS status,
header.DOCDATE,
cust.[USERDEF2] as CUSTNMBR,
detail.ITEMNMBR,
detail.QTYREMAI*detail.QTYBSUOM AS qtyorder,
detail.ReqShipDate,
detail.ACTLSHIP,
detail.QTYFULFI*detail.QTYBSUOM AS shipqty,
detail.UNITPRCE AS salesprice,
detail.UNITCOST AS cost,
header.VOIDSTTS

ALTERNATE CustomerOrders-Closed:

SELECT
detail.locncode as location,
header.SOPNUMBE,
'C' AS status,
header.DOCDATE,
cust.[USERDEF2] as CUSTNMBR,
detail.ITEMNMBR,
detail.QUANTITY*detail.QTYBSUOM AS qtyorder,
header.ReqShipDate,
detail.ACTLSHIP,
detail.QTYPRINV*detail.QTYBSUOM AS shipqty,
detail.UNITPRCE as salesprice,
detail.UNITCOST AS cost,
header.VOIDSTTS
FROM
dbo.SOP30200 AS header
INNER JOIN dbo.SOP30300 AS detail ON header.SOPNUMBE = detail.SOPNUMBE AND header.SOPTYPE = detail.SOPTYPE
INNER JOIN dbo.RM00101 AS cust ON header.CUSTNMBR = cust.CUSTNMBR
WHERE header.DOCDATE > '2012-1-1'
AND header.VOIDSTTS = 0
AND (detail.SOPTYPE = 2
OR detail.SOPTYPE = 3 )
AND header.PSTGSTUS = 0
AND detail.UNITPRCE<>0
UNION
SELECT
detail.locncode as location,
header.SOPNUMBE,
'C' AS status,
header.DOCDATE,
cust.[USERDEF2] as CUSTNMBR,
detail.ITEMNMBR,
detail.QUANTITY*detail.QTYBSUOM AS qtyorder,
header.ReqShipDate,
detail.ACTLSHIP,
detail.QTYPRINV*detail.QTYBSUOM AS shipqty,
detail.UNITPRCE as salesprice,
detail.UNITCOST AS cost,
header.VOIDSTTS
FROM
TPEUR.DBO.SOP30200 AS header
INNER JOIN TPEUR.DBO.SOP30300 AS detail ON header.SOPNUMBE = detail.SOPNUMBE AND header.SOPTYPE = detail.SOPTYPE
INNER JOIN TPEUR.DBO.RM00101 AS cust ON header.CUSTNMBR = cust.CUSTNMBR
WHERE header.DOCDATE > '2012-1-1'
AND header.VOIDSTTS = 0
AND (detail.SOPTYPE = 2
OR detail.SOPTYPE = 3 )
AND header.PSTGSTUS = 0
AND detail.UNITPRCE<>0
UNION
SELECT
detail.locncode as location,
header.SOPNUMBE,
'C' AS status,
header.DOCDATE,
cust.[USERDEF2] as CUSTNMBR,
detail.ITEMNMBR,
detail.QUANTITY*detail.QTYBSUOM AS qtyorder,
header.ReqShipDate,
detail.ACTLSHIP,
detail.QTYPRINV*detail.QTYBSUOM AS shipqty,
detail.UNITPRCE as salesprice,
detail.UNITCOST AS cost,
header.VOIDSTTS
FROM
GICAN.DBO.SOP30200 AS header
INNER JOIN GICAN.DBO.SOP30300 AS detail ON header.SOPNUMBE = detail.SOPNUMBE AND header.SOPTYPE = detail.SOPTYPE
INNER JOIN GICAN.DBO.RM00101 AS cust ON header.CUSTNMBR = cust.CUSTNMBR
WHERE header.DOCDATE > '2015-3-31'
AND header.VOIDSTTS = 0
AND (detail.SOPTYPE = 2
OR detail.SOPTYPE = 3 )
AND header.PSTGSTUS = 0
AND detail.UNITPRCE<>0

vendororders.txt

ALTERNATE VendorOrders:

SELECT
locncode as location,
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

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 <> '')

Plex DemandCaster Supply Chain Planning

Great Plains SQL Data Tables - Alternate

item.txt

productclass.txt

vendors.txt

customers.txt

customerorders.txt

vendororders.txt

Locations.txt (for DRP)

0 Comments

Add your comment

Topics

Last Updated

Other Resources

Knowledge Base