|
|
منتدى جريت بلينز العام يحتوي على المواضيع التي لا تنتمي الى التقسيمات أدناه. استعرض الأقسام الموجودة قبل كتابة موضوعك في هذا القسم. |
| أدوات الموضوع | انواع عرض الموضوع |
21-Apr-2013, 02:13 PM | #1 | ||
عضو جديد تاريخ التسجيل: Jul 2007 المشاركات: 16
| اريد تقرير المواد غير المتحركة ارجو مساعدتي لعمل تقرير بالمواد التي لم يتم عليها اي حركة سواء استلام او صرف منذ مدة معينة؟؟؟؟ | ||
22-Apr-2013, 09:39 AM | #2 |
مدير عام منتديات جريت بلينز العربية | عليك القراءة من الجداول التالية من قاعدة بيانات Inventory Transaction HistoryInventory و Transaction Amounts History كود PHP: __________________ Monzer Osama Saudia Arabia - Jeddah 00966501826235 Microsoft Certified Business Management Solutions Professional Microsoft Certified Trainer |
04-May-2013, 01:38 PM | #3 | ||
عضو جديد تاريخ التسجيل: Jul 2008 الدولة: Jordan المشاركات: 21
| كود: 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 | ||
04-May-2013, 01:42 PM | #4 | ||
عضو جديد تاريخ التسجيل: Jul 2008 الدولة: Jordan المشاركات: 21
| كود: 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) ارجو ان يكون ما طرحته هو المطلوب فان اصبت فادعو لى وان اخطات فسامحونى | ||
يتصفح الموضوع حالياً : 1 (0 عضو و 1 ضيف) | |
أدوات الموضوع | |
انواع عرض الموضوع | |
|
|
المواضيع المتشابهه | ||||
الموضوع | كاتب الموضوع | المنتدى | مشاركات | آخر مشاركة |
عملية التكويد ( ترميز المواد ) | احمد مشافي | حزمة التشغيل (جزيئ المبيعات - المشتريات - المستودعات ) | 6 | 22-Oct-2015 10:37 PM |
اريد عمل اختصار للحسابات | hanymsaied | تركيب وإعدادات النظام وما يتعلق به توجيهات Installation & Configuration | 4 | 15-Jul-2013 12:42 PM |
A.b.c تحليل المواد في المستودعات | احمد مشافي | حزمة التشغيل (جزيئ المبيعات - المشتريات - المستودعات ) | 2 | 08-Jul-2012 12:00 PM |
تكلفة المواد في المستودعات | alwahidi22 | الجزيئ المالي (GL - AP - AR – BM - FA - AC ... ets.) | 4 | 23-Apr-2012 02:39 PM |
اريد التعرف على مصطلح Mrp عن قرب | alwahidi22 | منتدى جريت بلينز العام | 2 | 30-Jan-2012 03:14 PM |