اريد تقرير المواد غير المتحركة

[bana_112]

ارجو مساعدتي لعمل تقرير بالمواد التي لم يتم عليها اي حركة سواء استلام او صرف منذ مدة معينة؟؟؟؟

[Monzer Osama]

عليك القراءة من الجداول التالية من قاعدة بيانات
Inventory Transaction HistoryInventory و Transaction Amounts History

select*from IV30300
select*from IV30200

[abuizhery]

SELECT DISTINCT
IV00101.ITEMNMBR, IV00101.ITEMDESC, IV00101.SELNGUOM, IV30300.DOCDATE, IV30300.LOCNCODE, IV40700.LOCNDSCR,
IV00102.LOCNCODE AS Expr1, IV00102.QTYONHND, IV00102.ATYALLOC, IV30300.HSTMODUL, IV30300.TRXQTY, IV30300.QTYBSUOM,
(SELECT MAX(DOCDATE) AS Expr1
FROM EXH_Inventory_Transactions
WHERE (LOCNCODE = IV00102.LOCNCODE) AND (ITEMNMBR = IV00101.ITEMNMBR) AND (TRTQTYTY <> 0)) AS Expr4,
(SELECT MAX(DOCDATE) AS Expr1
FROM EXH_Inventory_Transactions AS EXH_Inventory_Transactions_2
WHERE (ITEMNMBR = IV00101.ITEMNMBR) AND (LOCNCODE = IV40700.LOCNCODE) AND (HSTMODUL = 'SOP') AND (TRXQTY < 0)) AS Expr5,
(SELECT MAX(DOCDATE) AS Expr1
FROM EXH_Inventory_Transactions AS EXH_Inventory_Transactions_1
WHERE (LOCNCODE = IV00102.LOCNCODE) AND (ITEMNMBR = IV00101.ITEMNMBR) AND (TRXQTY > 0)) AS Expr6
FROM IV00102 AS IV00102 INNER JOIN
IV40700 AS IV40700 ON IV00102.LOCNCODE = IV40700.LOCNCODE INNER JOIN
IV00101 AS IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
EXH_Inventory_Transactions AS IV30300 ON IV00101.ITEMNMBR = IV30300.ITEMNMBR AND IV40700.LOCNCODE = IV30300.LOCNCODE
ORDER BY IV30300.LOCNCODE, IV00101.ITEMNMBR

لحركات البطيئة فى المستودعات وسوف ارفق لاحقا حركات على كل المخزون

[abuizhery]

SELECT SOP10100.DOCDATE, SOP10200.LOCNCODE AS TRXLOCTN,
CASE WHEN dbo.SOP10200.SOPTYPE = 4 THEN 'Return Unposted' WHEN dbo.SOP10200.SOPTYPE = 3 THEN 'Sales Unposted' ELSE 'Other Order Type'
END AS TrxType, SOP10200.ITEMNMBR, SOP10200.ITEMDESC,
ISNULL((CASE WHEN dbo.SOP10200.SOPTYPE = 4 THEN dbo.SOP10200.QUANTITY * QTYBSUOM ELSE 0 END), 0) AS QTYIN,
ISNULL((CASE WHEN dbo.SOP10200.SOPTYPE <> 4 THEN dbo.SOP10200.QUANTITY * QTYBSUOM ELSE 0 END), 0) AS QTYOUT, SOP10200.UOFM,
SOP10106.CMMTTEXT AS Reason
FROM SOP10200 INNER JOIN
IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
SOP10100 ON SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
SOP10106 ON SOP10200.SOPTYPE = SOP10106.SOPTYPE
WHERE (ISNULL((CASE WHEN dbo.SOP10200.SOPTYPE = 4 THEN dbo.SOP10200.QUANTITY * QTYBSUOM ELSE 0 END), 0)
+ ISNULL((CASE WHEN dbo.SOP10200.SOPTYPE = 3 THEN dbo.SOP10200.QUANTITY * QTYBSUOM ELSE 0 END), 0) <> 0)

UNION ALL


SELECT SOP30200.DOCDATE, SOP30300.LOCNCODE AS TRXLOCTN,
CASE WHEN dbo.SOP30300.SOPTYPE = 3 THEN 'Sales Posted' WHEN dbo.SOP30300.SOPTYPE = 4 THEN 'Return Posted' END AS TrxType,
SOP30300.ITEMNMBR, SOP30300.ITEMDESC,
ISNULL((CASE WHEN dbo.SOP30300.SOPTYPE = 4 THEN dbo.SOP30300.QUANTITY * QTYBSUOM ELSE 0 END), 0) AS QTYIN,
ISNULL((CASE WHEN dbo.SOP30300.SOPTYPE = 3 THEN dbo.SOP30300.QUANTITY * QTYBSUOM ELSE 0 END), 0) AS QTYOUT, SOP30300.UOFM,
SOP10106.CMMTTEXT AS Reason
FROM SOP30300 INNER JOIN
IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
SOP30200 ON SOP30300.SOPNUMBE = SOP30200.SOPNUMBE INNER JOIN
SOP10106 ON SOP30300.SOPTYPE = SOP10106.SOPTYPE
UNION ALL
SELECT dbo.IV10000.DOCDATE, dbo.IV10001.TRXLOCTN, 'Adjustment Unposted' AS TrxType, dbo.IV10001.ITEMNMBR, dbo.IV00101.ITEMDESC,
ISNULL(CASE WHEN dbo.IV10001.TRXQTY > 0 THEN dbo.IV10001.TRXQTY*QTYBSUOM ELSE 0 END, 0) AS QTYIN,
ISNULL(CASE WHEN dbo.IV10001.TRXQTY < 0 THEN ABS(dbo.IV10001.TRXQTY*QTYBSUOM) ELSE 0 END, 0) AS QTYOUT,UOFM,'' as Reason

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)and (ISNULL(CASE WHEN dbo.IV10001.TRXQTY > 0 THEN dbo.IV10001.TRXQTY ELSE 0 END, 0)) +(ISNULL(CASE WHEN dbo.IV10001.TRXQTY < 0 THEN ABS(dbo.IV10001.TRXQTY) ELSE 0 END, 0)) <> 0
UNION ALL


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

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.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)*UMQTYINB)
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)*UMQTYINB
ELSE 0 END AS QTYOUT,UOFM,'' as Reason
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
where dbo.POP10310.LOCNCODE<>''
UNION ALL

SELECT dbo.POP30300.receiptdate as DOCDATE, dbo.POP30310.LOCNCODE as TRXLOCTN, 'Posted Purchasing' AS TrxType, 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)*UMQTYINB)
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,UOFM,'' as Reason
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
where dbo.POP30310.LOCNCODE<>''

UNION ALL

SELECT dbo.IV10000.DOCDATE, dbo.IV10001.TRNSTLOC as TRXLOCTN, 'Unposted Transfer' AS TrxType, dbo.IV00101.ITEMNMBR,
dbo.IV00101.ITEMDESC, ISNULL(dbo.IV10001.TRXQTY, 0)*QTYBSUOM AS QTYIN, 0 AS QTYOUT,UOFM,'' as Reason
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.ITEMNMBR,
dbo.IV00101.ITEMDESC, ISNULL(dbo.IV30300.TRXQTY, 0)*QTYBSUOM AS QTYIN, 0 AS QTYOUT,UOFM,'' as Reason
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.IV00101.ITEMNMBR,
dbo.IV00101.ITEMDESC, 0 AS QTYIN, ISNULL(dbo.IV10001.TRXQTY, 0)*QTYBSUOM AS QTYOUT,UOFM,'' as Reason
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.IV00101.ITEMNMBR,
dbo.IV00101.ITEMDESC, 0 AS QTYIN , ISNULL(dbo.IV30300.TRXQTY, 0)*QTYBSUOM AS QTYOUT,UOFM,'' as Reason
FROM dbo.IV30300 INNER JOIN
dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE (dbo.IV30300.DOCTYPE = 3)


ارجو ان يكون ما طرحته هو المطلوب فان اصبت فادعو لى وان اخطات فسامحونى