Inventory Availability Script

[UserofGP]

I have assigned the task of determining future availability of our inventory items in weekly time buckets. Has anyone tried to do this before and if so, can you provide details/scripts that will help out.

 

[jazgeek]

sql I assume.....

here goes

CREATE VIEW dbo.POP_LINE_QTS
AS
SELECT dbo.POP10110.ORD, dbo.POP10110.ITEMNMBR, dbo.POP10110.UOFM, dbo.POP10110.PRMDATE, dbo.POP10110.PONUMBER,
dbo.POP10110.POLNESTA, dbo.POP10110.QTYORDER - dbo.POP10110.QTYCANCE AS QTY, ISNULL(dbo.[1RECEIVEDAMTS].QTYSHIP, 0) AS QTYSHIP,
dbo.POP10100.BUYERID, dbo.SY04200.CMMTTEXT, dbo.POP10110.COMMNTID, dbo.POP10100.SHIPMTHD, dbo.POP10110.ITEMDESC,
dbo.POP10110.PRMSHPDTE, dbo.POP10100.POSTATUS, dbo.POP10100.CREATDDT
FROM dbo.POP10110 LEFT OUTER JOIN
dbo.SY04200 ON dbo.POP10110.COMMNTID = dbo.SY04200.COMMNTID LEFT OUTER JOIN
dbo.POP10100 ON dbo.POP10110.PONUMBER = dbo.POP10100.PONUMBER LEFT OUTER JOIN
dbo.[1RECEIVEDAMTS] ON dbo.POP10110.PONUMBER = dbo.[1RECEIVEDAMTS].PONUMBER AND
dbo.POP10110.ORD = dbo.[1RECEIVEDAMTS].POLIN
WHERE (dbo.POP10110.POLNESTA < 6) AND (dbo.POP10100.POSTATUS = 2 OR
dbo.POP10100.POSTATUS = 3 OR
dbo.POP10100.POSTATUS = 4)


------------------------------------------------
creates a listing of remaining qty's on PO's
------------------------------------------------

CREATE VIEW dbo.MAINONHAND2
AS
SELECT ITEMNMBR, BINNMBR, QTYONHND, ORDRPNTQTY
FROM dbo.IV00102
WHERE (LOCNCODE = 'MAIN')

------------------------------------------------
creates a view for available inventory (we only use one site so do so as you see fit with your individual situation
------------------------------------------------

CREATE VIEW dbo.SOPDEMAND
AS
SELECT ITEMNMBR AS ITEM, QUANTITY AS QTY, UOFM, ReqShipDate AS [DATE], SOPNUMBE AS DOC_NO, LNITMSEQ AS LINE
FROM dbo.SOP10200
WHERE (SOPTYPE = 2)


---------------------------------------------------------
creates a listing of items on sales orders
----------------------------------------------------------
CREATE VIEW dbo.PO_BOM_LINE_QTS2
AS
SELECT ORD AS 'LINE_NO', ITEMNMBR AS 'Item', (QTY - QTYSHIP)
AS 'QTY', UOFM AS 'UOFM', PRMDATE AS 'DATE',
PONUMBER AS 'DOC_NO', 'PO' AS 'DOC_TYPE',
BUYERID AS 'BUYER', '' AS 'JOB',
COMMNTID AS 'COMMENT',"" as 'STAT'
FROM POP_LINE_QTS WHERE QTY-QTYSHIP > 0
UNION
SELECT '0' AS 'LINE_NO', ITEMNMBR AS 'Item',
QTYONHND AS 'QTY', 'EA' AS 'UOFM',
01 / 01 / 1900 AS 'DATE', 'ON HAND' AS 'DOC_NO',
'QOH' AS 'DOC_TYPE', '' AS 'BUYER', '' AS 'JOB',
'' AS 'COMMENT',"" as 'STAT'
FROM MAINONHAND2
UNION
SELECT LINE AS 'LINE_NO', ITEM AS 'Item', QTY AS 'QTY','EA' AS 'UOFM',DATE AS 'DATE',DOC_NO AS 'DOC_NO',
'SOP' AS 'DOC_TYPE','' AS 'BUYER',DOC_NO AS 'JOB','' AS 'COMMENT','' AS 'STAT'
FROM SOPDEMAND


--------------------------------------
this creates a union of the 3 queries for you to report on
group by date and you should have a method of doing a running balance on in's and out's in the future

we actually have 2 more unions in the union query that take into account components in production and finished goods in production but I stripped them out as you probably don't use Horizons Light Manufacturing

have fun



---------------------------------------







-----------
and they wonder why they call it Great Pains!

jaz

 

[jazgeek]

if you're wondering about the 1/1/1900 date on teh mainonhand query, it's to put the quantity on hand now at the beginning of the query if sorted/grouped by date



-----------
and they wonder why they call it Great Pains!

jaz

 

[UserofGP]

thank you for the script. what is 1RECEIVEDAMTS in the first view. Is it another view or is it a custom table. Can you post the script for this.

Scott

 

[jazgeek]

sorry, my bad


CREATE VIEW dbo.[1RECEIVEDAMTS]
AS
SELECT PONUMBER, POLNENUM AS POLIN, ITEMNMBR,
SUM(QTYSHPPD) AS QTYSHIP, SUM(QTYREJ)
AS QTYREJ
FROM POP10500
WHERE (POPTYPE = 1 OR
POPTYPE = 3)
GROUP BY POLNENUM, PONUMBER, ITEMNMBR



-----------
and they wonder why they call it Great Pains!

jaz

 

[UserofGP]

thank you.