Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
0 Kudos

This is a routine requirement of cost, tax and company audit, that auditors inquire about a lots of data on inventory (Consumption, Valuation Etc).

While I was struggling to produce the data that was needed by auditors, I developed below SQL, which I thought might be useful to everyone.

It is a long and time taking query, which I hope the experts here can optimize further for faster execution.

Being new to the community, it is possible that this is posted at wrong place, moderators, please move the post to appropriate place.

Please post your comments (Good or Bad, whatever)

DECLARE@FROMDate Datetime

DECLARE @ToDate Datetime

SELECT@FROMDate = MIN(S0.Docdate)
FROM dbo.OINM S0
WHERE S0.Docdate >='[%0]'

SELECT@ToDate = MAX(S1.Docdate)
FROM dbo.OINM S1
WHERE S1.Docdate <='[%1]'

--for opening balance

selecta.item1 as Item,
sum(a.Q_OB1) as Q_OB,
sum(a.V_OB1) as V_OB,
sum(a.Q_Pur1) as Q_Pur,
sum(a.V_Pur1) as V_Pur,
sum(a.Q_Pur_Ret1) as Q_Pur_Ret,
sum(a.V_Pur_Ret1) as V_Pur_Ret,
sum(a.Q_Pur1) + sum(a.Q_Pur_Ret1) as Q_Pur_Net,
sum(a.V_Pur1) + sum(a.V_Pur_Ret1) as V_Pur_Net,
sum(a.Q_Prod_In1) as Q_Prod_In,
sum(a.V_Prod_In1) as V_Prod_In,
sum(a.Q_Prod_Out1) as Q_Prod_Out,
sum(a.V_Prod_Out1) as V_Prod_Out,
sum(a.V_Reval_Net1) as V_Reval_Net,
sum(a.Q_Sales1) as Q_Sales,
sum(a.V_Sales1) as V_Sales,
sum(a.Q_Sales_Ret1) as Q_Sales_Ret,
sum(a.V_Sales_Ret1) as V_Sales_Ret,
sum(a.Q_Sales1) + sum(a.Q_Sales_Ret1) as Q_Sales_Net,
sum(a.V_Sales1) + sum(a.V_Sales_Ret1) as V_Sales_Net,
sum(a.Q_OB1) + sum(a.Q_Pur1) + sum(a.Q_Pur_Ret1) + sum(a.Q_Prod_In1) + sum(a.Q_Prod_Out1) + sum(a.Q_Sales1) + sum(a.Q_Sales_Ret1) as Q_CB1,
sum(a.V_OB1) + sum(a.V_Pur1) + sum(a.V_Pur_Ret1) + sum(a.V_Prod_In1) + sum(a.V_Prod_Out1) + sum(a.V_Reval_Net1) + sum(a.V_Sales1) + sum(a.V_Sales_Ret1) as V_CB1,
sum(a.Q_Oth1) as Q_Others,
sum(a.V_Oth1) as V_Others

from (

selectt0.itemcode as Item1,
sum(t0.inqty)-sum(t0.outqty) as Q_OB1,
sum(t0.transvalue) as V_OB1,
0 as Q_Pur1,
0 as V_Pur1,
0 as Q_Pur_Ret1,
0 as V_Pur_Ret1,
0 as Q_Pur_Net1,
0 as V_Pur_Net1,
0 as Q_Prod_In1,
0 as V_Prod_In1,
0 as Q_Prod_Out1,
0 as V_Prod_Out1,
0 as V_Reval_Net1,
0 as Q_Sales1,
0 as V_Sales1,
0 as Q_Sales_Ret1,
0 as V_Sales_Ret1,
0 as Q_Sales_Net1,
0 as V_Sales_Net1,
0 as Q_CB1,
0 as V_CB1,
0 as Q_Oth1,
0 as V_Oth1

from oinm t0

where t0.docdate < @FROMDate

group by t0.itemcode

union all

--for purchases and landed cost additions

selectt0.itemcode as Item1,
0 as Q_OB1,
0 as V_OB1,
sum(t0.inqty)-sum(t0.outqty) as Q_Pur1,
sum(t0.transvalue) as V_Pur1,
0 as Q_Pur_Ret1,
0 as V_Pur_Ret1,
0 as Q_Pur_Net1,
0 as V_Pur_Net1,
0 as Q_Prod_In1,
0 as V_Prod_In1,
0 as Q_Prod_Out1,
0 as V_Prod_Out1,
0 as V_Reval_Net1,
0 as Q_Sales1,
0 as V_Sales1,
0 as Q_Sales_Ret1,
0 as V_Sales_Ret1,
0 as Q_Sales_Net1,
0 as V_Sales_Net1,
0 as Q_CB1,
0 as V_CB1,
0 as Q_Oth1,
0 as V_Oth1

from oinm t0

where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype in (18, 20, 69)

group by t0.itemcode

union all

--for purchase returns

selectt0.itemcode as Item1,
0 as Q_OB1,
0 as V_OB1,
0 as Q_Pur1,
0 as V_Pur1,
sum(t0.inqty)-sum(t0.outqty) as Q_Pur_Ret1,
sum(t0.transvalue) as V_Pur_Ret1,
0 as Q_Pur_Net1,
0 as V_Pur_Net1,
0 as Q_Prod_In1,
0 as V_Prod_In1,
0 as Q_Prod_Out1,
0 as V_Prod_Out1,
0 as V_Reval_Net1,
0 as Q_Sales1,
0 as V_Sales1,
0 as Q_Sales_Ret1,
0 as V_Sales_Ret1,
0 as Q_Sales_Net1,
0as V_Sales_Net1,
0 as Q_CB1,
0 as V_CB1,
0 as Q_Oth1,
0 as V_Oth1

from oinm t0

where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype in (19, 21)

group by t0.itemcode

union all

--for receipt from production

selectt0.itemcode as Item1,
0 as Q_OB1,
0 as V_OB1,
0 as Q_Pur1,
0 as V_Pur1,
0 as Q_Pur_Ret1,
0 as V_Pur_Ret1,
0 as Q_Pur_Net1,
0 as V_Pur_Net1,
sum(t0.inqty) - sum(t0.outqty) as Q_Prod_In1,
sum(t0.transvalue) as V_Prod_In1,
0 as Q_Prod_Out1,
0 as V_Prod_Out1,
0 as V_Reval_Net1,
0 as Q_Sales1,
0 as V_Sales1,
0 as Q_Sales_Ret1,
0 as V_Sales_Ret1,
0 as Q_Sales_Net1,
0 as V_Sales_Net1,
0 as Q_CB1,
0 as V_CB1,
0 as Q_Oth1,
0 as V_Oth1

from oinm t0

where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype in (59)

group by t0.itemcode

union all

--for issue to production

selectt0.itemcode as Item1,
0 as Q_OB1,
0 as V_OB1,
0 as Q_Pur1,
0 as V_Pur1,
0 as Q_Pur_Ret1,
0 as V_Pur_Ret1,
0 as Q_Pur_Net1,
0 as V_Pur_Net1,
0 as Q_Prod_In1,
0 as V_Prod_In1,
sum(t0.inqty) - sum(t0.outqty) as Q_Prod_Out1,
sum(t0.transvalue) as V_Prod_Out1,
0 as V_Reval_Net1,
0 as Q_Sales1,
0 as V_Sales1,
0 as Q_Sales_Ret1,
0 as V_Sales_Ret1,
0 as Q_Sales_Net1,
0 as V_Sales_Net1,
0 as Q_CB1,
0 as V_CB1,
0 as Q_Oth1,
0 as V_Oth1

from oinm t0

where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype in (60)

group by t0.itemcode

union all

--for value change due to inventory revaluation and production variance

selectt0.itemcode as Item1,
0 as Q_OB1,
0 as V_OB1,
0 as Q_Pur1,
0 as V_Pur1,
0 as Q_Pur_Ret1,
0 as V_Pur_Ret1,
0 as Q_Pur_Net1,
0 as V_Pur_Net1,
0 as Q_Prod_In1,
0 as V_Prod_In1,
0 as Q_Prod_Out1,
0 as V_Prod_Out1,
sum(t0.transvalue) as V_Reval_Net1,
0 as Q_Sales1,
0 as V_Sales1,
0 as Q_Sales_Ret1,
0 as V_Sales_Ret1,
0 as Q_Sales_Net1,
0 as V_Sales_Net1,
0 as Q_CB1,
0 as V_CB1,
0 as Q_Oth1,
0 as V_Oth1

from oinm t0

where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype in (162, 202)

group by t0.itemcode

union all

--for sales

selectt0.itemcode as Item1,
0 as Q_OB1,
0 as V_OB1,
0 as Q_Pur1,
0 as V_Pur1,
0 as Q_Pur_Ret1,
0 as V_Pur_Ret1,
0 as Q_Pur_Net1,
0 as V_Pur_Net1,
0 as Q_Prod_In1,
0 as V_Prod_In1,
0 as Q_Prod_Out1,
0 as V_Prod_Out1,
0 as V_Reval_Net1,
sum(t0.outqty) as Q_Sales1,
sum(t0.transvalue) as V_Sales1,
0 as Q_Sales_Ret1,
0 as V_Sales_Ret1,
0 as Q_Sales_Net1,
0 as V_Sales_Net1,
0 as Q_CB1,
0 as V_CB1,
0 as Q_Oth1,
0 as V_Oth1

from oinm t0

where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype in (15)

group by t0.itemcode

union all

--for sales return

selectt0.itemcode as Item1,
0 as Q_OB1,
0 as V_OB1,
0 as Q_Pur1,
0 as V_Pur1,
0 as Q_Pur_Ret1,
0 as V_Pur_Ret1,
0 as Q_Pur_Net1,
0 as V_Pur_Net1,
0 as Q_Prod_In1,
0 as V_Prod_In1,
0 as Q_Prod_Out1,
0 as V_Prod_Out1,
0 as V_Reval_Net1,
0 as Q_Sales1,
0 as V_Sales1,
sum(t0.outqty) as Q_Sales_Ret1,
sum(t0.transvalue) as V_Sales_Ret1,
0 as Q_Sales_Net1,
0 as V_Sales_Net1,
0 as Q_CB1,
0 as V_CB1,
0 as Q_Oth1,
0 as V_Oth1

from oinm t0

where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype not in (14, 15, 18, 20, 69, 19, 21, 59, 60, 162, 202)

group by t0.itemcode

) a

group by a.item1

order by a.item1

Labels in this area