Inventory Report

[Former Member]

I have required the inventory report as per mention below format with from date -to date input parameter.

ITM_CODE

OPENING BALANCE_QTY

OPENING BALANCE_VALUE

GRPO & AP INVOICE _RECEIVED_QTY

GRPO & AP INVOICE _RECEIVED_VALUE

GOOD RECIPT _RECEIVED_QTY

GOOD RECIPT _ RECEIVED_VALUE

SALES RETUN & A/R CREDIT  MEMO_RECEIVED_QTY

SALES RETUN & A/R CREDIT MEMO_RECEIVED_VALUE

PURCHASE GOOD RETURN & A/P CREDIT MEMO_RETURN_QTY

PURCHASE GOOD RETURN & A/P CREDIT MEMO_RETURN_VALUE

DELIVER CHALLAN & A/R INVOICE _ISSUE _QTY

DELIVER CHALLAN & A/R INVOICE_ISSUE_VALUE

GOOD ISSUE _ ISSUE_QTY

GOOD ISSUE _ ISSUE_VALUE

CLOSING_QTY

CLOSING_VALUE

1

1

1

1

1

1

1

1

Total

[KennedyT21]

Hi

Try This

/* Select t1.docdate from OINV t1 */

DECLARE @d1  AS DATETIME
DECLARE @d2  AS DATETIME
SET @d1 = /* t1.docdate */ '[%0]'
SET @d2 = /* t1.docdate */ '[%1]'

SELECT tt.Itemcode
       ,tt.Dscription AS ItemName
      ,SUM(tt.Opening) AS Opening
      ,SUM(tt.Sales) AS Sales
      ,SUM(tt.SalesReturn) AS SalesReturn
      ,SUM(tt.Delivery) AS Delivery
      ,SUM(tt.DeliveryReturn) AS DeliveryReturn
       ,SUM(tt.Purchase) AS Purchase
      ,SUM(tt.PurchaseReturn) AS PurchaseReturn
      ,SUM(tt.GoodsReceiptsPo) AS GoodsReceiptPo
      ,SUM(tt.GoodsReturnPo) AS GoodsReturnPo
      ,SUM(tt.Receipt) AS Receipt
       ,SUM(tt.Issue) AS Issue
      ,SUM(tt.ProReceipt) AS ProductionReceipt
      ,SUM(tt.ProIssue) AS ProductionIssue
FROM   (
           SELECT Itemcode
                 ,n.Dscription
                 ,SUM(inqty-outqty) AS Opening
                  ,0 AS Sales
                 ,0 AS SalesReturn
                 ,0 AS Delivery
                 ,0 AS DeliveryReturn
                 ,0 AS Purchase
                 ,0 AS PurchaseReturn
                  ,0 GoodsReceiptsPo
                 ,0 GoodsReturnPo
                 ,0 AS Receipt
                 ,0 AS Issue
                 ,0 AS ProReceipt
                 ,0 AS ProIssue
           FROM   oinm n
            WHERE  docdate<@d1
           GROUP BY
                  Itemcode
                 ,n.Dscription
           UNION ALL
           SELECT Itemcode
                 ,Dscription
                 ,SUM(
                       CASE
                           WHEN c.TransType='310000001' THEN (inqty- Outqty)
                           ELSE 0
                      END
                  ) AS Opening
                 ,SUM(CASE WHEN c.TransType='13' THEN (Outqty) ELSE 0 END) AS
                   Sales
                 ,SUM(CASE WHEN c.TransType='14' THEN (Inqty) ELSE 0 END) AS
                  SalesReturn
                 ,SUM(CASE WHEN c.TransType='15' THEN (Outqty) ELSE 0 END) AS
                   Delivery
                 ,SUM(CASE WHEN c.TransType='16' THEN (Inqty) ELSE 0 END) AS
                  DeliveryReturn
                 ,SUM(CASE WHEN c.TransType='18' THEN (Inqty) ELSE 0 END) AS
                   Purchase
                 ,SUM(CASE WHEN c.TransType='19' THEN (outqty) ELSE 0 END) AS
                  PurchaseReturn
                 ,SUM(CASE WHEN c.TransType='20' THEN (Inqty) ELSE 0 END) AS
                   GoodsReceiptsPo
                 ,SUM(CASE WHEN c.TransType='21' THEN (outqty) ELSE 0 END) AS
                  GoodsReturnPo
                 ,SUM(
                      CASE
                           WHEN c.TransType='59'
                       AND c.ApplObj<>'202' THEN (inqty) ELSE 0 END
                  ) AS Receipt
                 ,SUM(
                      CASE
                           WHEN c.TransType='60'
                       AND c.ApplObj<>'202' THEN (outqty) ELSE 0 END
                  ) AS Issue
                 ,SUM(
                      CASE
                           WHEN c.TransType='59'
                       AND c.ApplObj='202' THEN (inqty) ELSE 0 END
                  ) AS ProReceipt
                 ,SUM(
                      CASE
                           WHEN c.TransType='60'
                       AND c.ApplObj='202' THEN (outqty) ELSE 0 END
                  ) AS ProIssue
           FROM   oinm c
           WHERE  c.DocDate BETWEEN @d1 AND @d2
           GROUP BY
                  itemcode
                  ,Dscription
       ) AS TT
GROUP BY
       tt.Itemcode
      ,Dscription
ORDER BY
       tt.Itemcode

Regards

Kennedy

[kothandaraman_nagarajan]

Hi,

Try this report and let me know the result:

SELECT
T0.[ItemCode], T0.[Dscription],T0.[TaxDate], T0.[DocDate],
T0.[DocDueDate], case when T0.[TransType] = 13 then 'AR'

when
T0.[TransType] = 14 then 'ARCredit'

when
T0.[TransType] = 15 then 'Delivery'

when
T0.[TransType] = 16 then 'SalesReturn'

when
T0.[TransType] = 203 then 'ARDown'

when
T0.[TransType] = 20 then 'Goods Receipt'

when
T0.[TransType] = 21 then 'Goods Return'

when
T0.[TransType] = 204 then 'APDown'

when
T0.[TransType] = 18 then 'APInvoice'

when
T0.[TransType] = 13 then 'AP Credit Memo'

when
T0.[TransType] = 69 then 'LandedCost'

when
T0.[TransType] = 24 then 'Incoming Payment'

when
T0.[TransType] = 25 then 'Deposit'

when
T0.[TransType] = 46 then 'VendorPayment'

when
T0.[TransType] = 57 then 'Check for Payment'

when
T0.[TransType] = 76 then 'Postdated Check'

when
T0.[TransType] = 58 then 'Inventory list'

when
T0.[TransType] = 59 then 'Good Receipt'

when
T0.[TransType] = 60 then 'Good Issue'

when
T0.[TransType] = 67 then 'Inventory Transfer'

when
T0.[TransType] = 68 then 'Work Instruction'

when
T0.[TransType] = 162 then 'Inventory Valuation'

when
T0.[TransType] = 202 then 'Production order'

when
T0.[TransType] = -2 then 'Opening Balance'

when
T0.[TransType] = -3 then 'Closing Balance'

when
T0.[TransType] = 30 then 'Journal Entry'

when
T0.[TransType] = 321 then 'Internal Reconilation'

when
T0.[TransType] = 10000046 then 'Data archive'

when
T0.[TransType] = 310000001 then 'Initial Qty'

when
T0.[TransType] = 10000071 then 'Inventory posting' end as Doc,
T0.[BASE_REF], T0.[Warehouse], T0.[InQty], T0.[Price], T0.[Currency],
T1.[U_NAME], sum(T0.[InQty] - T0.[OutQty]) as Cum, T0.[CalcPrice],
T0.[CostMethod] FROM OINM T0  INNER JOIN OUSR T1 ON T0.UserSign =
T1.USERID WHERE T0.[ItemCode] = [%0] GROUP BY T0.[ItemCode],
T0.[Dscription],T0.[DocDate], T0.[DocDueDate], T0.[Warehouse],
T0.[Price], T0.[Currency], T1.[U_NAME],T0.[TransType],T0.[BASE_REF],
T0.[CalcPrice], T0.[CostMethod],T0.[InQty], T0.[TaxDate],
T0.[BASE_REF] ORDER BY T0.[DocDate], T0.[DocDueDate]

Thanks & Regards,

Nagarajan