Skip to Content
0
Former Member
Oct 29, 2013 at 09:26 AM

Stock With opening and closing Balance

37 Views

I need to write a query for Inventory report with opening and closing Balance.I have already written a query for this purpose.But without serial no,in which table we can get the purchase price details.My query is as follows.

SELECT T1.[ItemCode], T0.[ItemName], T0.[FrgnName], T0.[ItmsGrpCod], T1.[SuppSerial] ChassisNo,

T1.[IntrSerial] EngNo, T1.[InDate], T1.[WhsCode], T2.[WhsName], T1.[Status],SrlP.Price FROM OITM T0

INNER JOIN OSRI T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OWHS T2 ON T1.WhsCode = T2.WhsCode

inner join

(SELECT distinct T0.SuppSerial,grp0.price,grp0.Dentry

FROM [dbo].[OSRI] T0 INNER JOIN

(select P0.price,P0.DocEntry as Dentry,P0.BaseLine, opr.docentry ,op.DocNum,opr.ItemCode,opr.Dscription ,opr.Quantity,opr.LineNum,opr.TrgetEntry from

[dbo].[opdn] op inner join pdn1 opr on op.docentry=opr.docentry

inner join

Pch1 P0 on opr.TrgetEntry=P0.DocEntry and opr.ItemCode=P0.ItemCode and opr.LineNum=P0.BaseLine

) grp0

on grp0.DocEntry=T0.BaseEntry and grp0.LineNum=T0.BaseLinNum and grp0.ItemCode=T0.ItemCode and T0.BaseType=20

union all

SELECT T0.SuppSerial,grp1.price,grp1.DocEntry

FROM [dbo].[OSRI] T0 INNER JOIN

(

select opr.Price, opr.docentry ,op.DocNum,opr.ItemCode,opr.Dscription ,opr.Quantity,opr.LineNum,opr.TrgetEntry from

[dbo].[oign] op inner join ign1 opr on op.docentry=opr.docentry

) grp1

on grp1.DocEntry=T0.BaseEntry and grp1.LineNum=T0.BaseLinNum and T0.BaseType=59) SrlP

on SrlP.SuppSerial=T1.[SuppSerial]

WHERE T1.[Status] = 0 and T0.[ItmsGrpCod] = 101

order by 5

Also I need a query for Stock movement with the following details.

Document type,documentno, itemcode,itemname,quantity how we can write the query

pls help