Need a Query for Issue & Return of RM

[Former Member]

Dear Experts,

I need a query which should show the Output of Issue for Production and the same return against the production Order with Value wise and the selection parameter should be the from and to date.

Thanks.

KB

[Former Member · Accepted solution]

Hi Kumar......

Try this and make necessary change as you want.....

SELECT T0.[DocNum], T1.[Dscription] 'RetItem', T1.[Quantity] RetQty, T2.[Dscription] IssuItem, T2.[Quantity] IssQty FROM OWOR T0 Inner Join IGN1 T1 On T0.DocNum=T1.BaseRef left Join  IGE1 T2 On T0.DocNum=T2.BaseRef and T1.BaseRef=T2.BaseRef WHERE T1.ItemCode=T2.ItemCode And (T0.DocDate>='[%0]' and T0.DocDate<='[%1]')

Hope this report will give you an idea......

Regards,

Rahul

[kvbalakumar]

Hi KB,

Try this

SELECT T2.[DocNum] [ProdnOrder#], T0.[ItemCode], T0.[Dscription], 
T0.[Quantity] [Issued Qty], (T0.[Quantity] * T0.[Price]) [Issue Value], 
T4.[DocDate] [Issue Date], T4.[DocNum] [Issue#],T3.[Quantity] [Return Qty], 
(T3.[Quantity] * T3.[Price]) [Receipt Value], T5.[DocDate] [Return Date], 
T5.[DocNum] [Return#] 
FROM IGE1 T0 
INNER JOIN WOR1 T1 ON T0.BaseEntry = T1.DocEntry and T0.BaseLine = T1.LineNum 
INNER JOIN OWOR T2 ON T1.DocEntry = T2.DocEntry 
left join IGN1 T3 on T3.BaseEntry = T1.DocEntry and T3.BaseLine = T1.LineNum 
INNER JOIN OIGE T4 ON T0.DocEntry = T4.DocEntry 
LEFT JOIN OIGN T5 ON T3.DocEntry = T5.DocEntry 
WHERE T2.[PostDate] between [%0] and [%1] and T3.TranType is Null

Regards,

Bala

[Former Member]

Hi Bala,

Its working fine but, issue value is not coming.Check once.

Thanks & Regards,

KB.

[kvbalakumar]

Hi Bhoopathy,

Try this now....

SELECT T2.[DocNum] [ProdnOrder#], T0.[ItemCode], T0.[Dscription], 
T0.[Quantity] [Issued Qty], T0.LineTotal [Issue Value], 
T4.[DocDate] [Issue Date], T4.[DocNum] [Issue#],T3.[Quantity] [Return Qty], 
T3.LineTotal [Receipt Value], T5.[DocDate] [Return Date], 
T5.[DocNum] [Return#] 
FROM IGE1 T0 
INNER JOIN WOR1 T1 ON T0.BaseEntry = T1.DocEntry and T0.BaseLine = T1.LineNum 
INNER JOIN OWOR T2 ON T1.DocEntry = T2.DocEntry 
left join IGN1 T3 on T3.BaseEntry = T1.DocEntry and T3.BaseLine = T1.LineNum 
INNER JOIN OIGE T4 ON T0.DocEntry = T4.DocEntry 
LEFT JOIN OIGN T5 ON T3.DocEntry = T5.DocEntry 
WHERE (T2.[PostDate] between [%0] and [%1]) 
and T3.TranType is Null and T0.BaseType = '202'and T3.BaseType = '202'

Regards,

Bala

Edited by: Balakumar Viswanathan on Apr 7, 2011 2:55 PM

[Former Member]

If issue value is not there, you probably have 0 price. Check it out.

Try this to see:

SELECT T2.DocNum ProdnOrder#, T0.ItemCode, T0.Dscription,

T0.Quantity Issued Qty, T0.LineTotal Issue Value,

T4.DocDate Issue Date, T4.DocNum Issue#,T3.Quantity Return Qty,

T3.LineTotal Receipt Value, T5.DocDate Return Date,

T5.DocNum Return#

FROM OWOR T2

INNER JOIN WOR1 T1 ON T1.DocEntry = T2.DocEntry AND T1.IssuedQty > 0

LEFT JOIN IGN1 T3 on T3.BaseEntry = T1.DocEntry and T3.BaseLine = T1.LineNum and T3.BaseType = '202'

LEFT JOIN OIGN T5 ON T3.DocEntry = T5.DocEntry

LEFT JOIN IGE1 T0 ON T0.BaseEntry = T1.DocEntry and T0.BaseLine = T1.LineNum and T0.BaseType = '202'

LEFT JOIN OIGE T4 ON T0.DocEntry = T4.DocEntry

WHERE T2.PostDate between [%0\] and [%1\]

ORDER BY T2.DocNum, T0.ItemCode

Thanks,

Gordon

[Former Member]

I believe the lack of data under 'issued qty' is a result of not including BACKFLUSH items... an issue I've been struggling with pulling together myself (to show manual and backflushed items on the same production order value report); the lines where each backflush item are created upon running the query, but only the ProdnOrder# field is populated.