Item Transactions Analysis

Interested in analyzing your items transaction? Check view below:

SELECT
dbo.SOP10100.DOCDATE,
dbo.SOP10200.LOCNCODE as TRXLOCTN,
CASE WHEN dbo.SOP10200.SOPTYPE = 4 THEN 'Return Unposted' When dbo.SOP10200.SOPTYPE = 3 Then 'Sales Unposted' end AS TrxType,
dbo.SOP10200.SOPNUMBE as DOCNUMBR,
dbo.SOP10200.ITEMNMBR,
dbo.SOP10200.ITEMDESC,
ISNULL((CASE WHEN dbo.SOP10200.SOPTYPE = 4 THEN dbo.SOP10200.QUANTITY ELSE 0 END), 0) AS QTYIN,
ISNULL((CASE WHEN dbo.SOP10200.SOPTYPE = 3 THEN dbo.SOP10200.QUANTITY ELSE 0 END), 0) AS QTYOUT
FROM dbo.SOP10200 INNER JOIN
dbo.IV00101 ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.SOP10100 ON dbo.SOP10200.SOPNUMBE = dbo.SOP10100.SOPNUMBE

UNION ALL


SELECT dbo.SOP30200.DOCDATE, dbo.SOP30300.LOCNCODE as TRXLOCTN, CASE WHEN dbo.SOP30300.SOPTYPE = 3 THEN 'Sales Posted' WHEN dbo.SOP30300.SOPTYPE = 4 THEN 'Return Posted' end AS TrxType, dbo.SOP30300.SOPNUMBE as DOCNUMBR, dbo.SOP30300.ITEMNMBR, dbo.SOP30300.ITEMDESC,
ISNULL((CASE WHEN dbo.SOP30300.SOPTYPE = 4 THEN dbo.SOP30300.QUANTITY ELSE 0 END), 0) AS QTYIN,
ISNULL((CASE WHEN dbo.SOP30300.SOPTYPE = 3 THEN dbo.SOP30300.QUANTITY ELSE 0 END), 0) AS QTYOUT

FROM dbo.SOP30300 INNER JOIN
dbo.IV00101 ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.SOP30200 ON dbo.SOP30300.SOPNUMBE = dbo.SOP30200.SOPNUMBE
UNION ALL
SELECT dbo.IV10000.DOCDATE, dbo.IV10001.TRXLOCTN, 'Adjustment Unposted' AS TrxType, dbo.IV10001.IVDOCNBR as DOCNUMBR, dbo.IV10001.ITEMNMBR, dbo.IV00101.ITEMDESC,
ISNULL(CASE WHEN dbo.IV10001.TRXQTY > 0 THEN dbo.IV10001.TRXQTY ELSE 0 END, 0) AS QTYIN,
ISNULL(CASE WHEN dbo.IV10001.TRXQTY < 0 THEN ABS(dbo.IV10001.TRXQTY) ELSE 0 END, 0) AS QTYOUT

FROM dbo.IV10001 INNER JOIN
dbo.IV00101 ON dbo.IV10001.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.IV10000 ON dbo.IV10001.IVDOCNBR = dbo.IV10000.IVDOCNBR
WHERE (dbo.IV10001.IVDOCTYP = 1)or (dbo.IV10001.IVDOCTYP = 2)
UNION ALL


SELECT dbo.IV30300.DOCDATE, dbo.IV30300.TRXLOCTN, 'Adjustment Posted' AS TrxType, dbo.IV30300.DOCNUMBR, dbo.IV30300.ITEMNMBR, dbo.IV00101.ITEMDESC,
ISNULL(CASE WHEN (dbo.IV30300.TRXQTY) > 0 THEN dbo.IV30300.TRXQTY ELSE 0 END, 0) AS QTYIN,
ISNULL(CASE WHEN (dbo.IV30300.TRXQTY) < 0 THEN ABS(dbo.IV30300.TRXQTY) ELSE 0 END, 0) AS QTYOut

FROM dbo.IV30300 INNER JOIN
dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE (dbo.IV30300.DOCTYPE = 1) or (dbo.IV30300.DOCTYPE = 2)
UNION ALL


SELECT dbo.POP10300.receiptdate as DOCDATE , dbo.POP10310.LOCNCODE as TRXLOCTN,
'Unposted Purchasing' AS TrxType, dbo.POP10300.POPRCTNM as DOCNUMBR, dbo.POP10310.ITEMNMBR,
dbo.POP10310.ITEMDESC,
CASE WHEN dbo.POP10300.POPTYPE <> 2 and dbo.POP10310.UNITCOST <>0 THEN (ISNULL(dbo.POP10310.EXTDCOST / dbo.POP10310.UNITCOST, 0))
ELSE 0 END AS QTYIN,
CASE WHEN dbo.POP10300.POPTYPE = 2 and dbo.POP10310.UNITCOST <>0 THEN ISNULL(dbo.POP10310.EXTDCOST / dbo.POP10310.UNITCOST, 0)
ELSE 0 END AS QTYOUT
FROM dbo.IV00101 INNER JOIN
dbo.POP10310 ON dbo.IV00101.ITEMNMBR = dbo.POP10310.ITEMNMBR INNER JOIN
dbo.POP10300 ON dbo.POP10310.POPRCTNM = dbo.POP10300.POPRCTNM
UNION ALL

SELECT dbo.POP30300.receiptdate as DOCDATE, dbo.POP30310.LOCNCODE as TRXLOCTN, 'Posted Purchasing' AS TrxType, dbo.POP30300.POPRCTNM as DOCNUMBR, dbo.POP30310.ITEMNMBR,
dbo.POP30310.ITEMDESC,
CASE WHEN dbo.POP30300.POPTYPE <> 2 and dbo.POP30310.UNITCOST <>0 THEN (ISNULL(dbo.POP30310.EXTDCOST / dbo.POP30310.UNITCOST, 0))
ELSE 0 END AS QTYIN,
CASE WHEN dbo.POP30300.POPTYPE = 2 and dbo.POP30310.UNITCOST <>0 THEN ISNULL(dbo.POP30310.EXTDCOST / dbo.POP30310.UNITCOST, 0)ELSE 0 END AS QTYOUT
FROM dbo.IV00101 INNER JOIN
dbo.POP30310 ON dbo.IV00101.ITEMNMBR = dbo.POP30310.ITEMNMBR INNER JOIN
dbo.POP30300 ON dbo.POP30310.POPRCTNM = dbo.POP30300.POPRCTNM

UNION ALL

SELECT dbo.IV10000.DOCDATE, dbo.IV10001.TRNSTLOC as TRXLOCTN, 'Unposted Transfer' AS TrxType, dbo.IV10001.IVDOCNBR as DOCNUMBR, dbo.IV00101.ITEMNMBR,
dbo.IV00101.ITEMDESC, ISNULL(dbo.IV10001.TRXQTY, 0) AS QTYIN, 0 AS QTYOUT
FROM dbo.IV10001 INNER JOIN
dbo.IV00101 ON dbo.IV10001.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.IV10000 ON dbo.IV10001.IVDOCNBR = dbo.IV10000.IVDOCNBR

WHERE (dbo.IV10001.IVDOCTYP = 3)

UNION ALL
SELECT dbo.IV30300.DOCDATE, dbo.IV30300.TRNSTLOC as TRXLOCTN, 'Posted Transfer' AS TrxType, dbo.IV30300.DOCNUMBR, dbo.IV30300.ITEMNMBR,
dbo.IV00101.ITEMDESC, ISNULL(dbo.IV30300.TRXQTY, 0) AS QTYIN, 0 AS QTYOUT
FROM dbo.IV30300 INNER JOIN
dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE (dbo.IV30300.DOCTYPE = 3)

UNION ALL
SELECT dbo.IV10000.DOCDATE, dbo.IV10001.TRXLOCTN, 'Unposted Transfer' AS TrxType, dbo.IV10000.IVDOCNBR as DOCNUMBR, dbo.IV00101.ITEMNMBR,
dbo.IV00101.ITEMDESC, 0 AS QTYIN, ISNULL(dbo.IV10001.TRXQTY, 0) AS QTYOUT
FROM dbo.IV10001 INNER JOIN
dbo.IV00101 ON dbo.IV10001.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.IV10000 ON dbo.IV10001.IVDOCNBR = dbo.IV10000.IVDOCNBR
WHERE (dbo.IV10001.IVDOCTYP = 3)


UNION ALL

SELECT dbo.IV30300.DOCDATE, dbo.IV30300.TRXLOCTN, 'Posted Transfer' AS TrxType, dbo.IV30300.DOCNUMBR, dbo.IV00101.ITEMNMBR,
dbo.IV00101.ITEMDESC, 0 AS QTYIN , ISNULL(dbo.IV30300.TRXQTY, 0) AS QTYOUT
FROM dbo.IV30300 INNER JOIN
dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE (dbo.IV30300.DOCTYPE = 3)

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/