Skip to Content
Former Member
Mar 29, 2013 at 06:14 AM

Query Changes in Report


Dear Experts,

I want some modification in Query please provide the same.

1. Actually when i execute these query in Production Order Status Report with Released its give the Result but i want changes that both Released & Planned Status report should come in single report merging of Released.

2.I want only Last Receipt from production WIP Open Qty in Report (Qty Repeated in Report).

3.Total of Qty Column & WIP Qty Column in Query

SELECT T6.SeriesName,T6.Series,T0.[DocNum] 'PO No.', T0.status 'Status',T0.POSTDATE, T0.[ItemCode], T1.[ItemName],

T0.[PlannedQty], T3.docNum 'Rec Doc', T3.DocDate 'Rec Date', T2.[TranType] as "Complete/Rejection",T2.[Quantity], T0.PlannedQty - T0.CmpltQty - t0.RjctQty As "WIP Qty",T0.U_SUBCONT AS "Job Worker Name",T0.COMMENTS,T7.U_NAME As "User Name" FROM OWOR T0 Inner Join OITM T1 On T0.ItemCode=T1.ItemCode

LEFT JOIN IGN1 T2 On T0.DocNum=T2.BaseRef And T0.ItemCode=T2.ItemCode Left Join OIGN T3

On T2.DocEntry=T3.DocEntry LEFT JOIN IGE1 T4 On T0.DocNum=T4.BaseRef Left Join OIGE T5

On T4.DocEntry=T5.DocEntry INNER JOIN NNM1 T6 ON T0.Series=T6.Series INNER JOIN OUSR T7 ON T0.UserSign = T7.userId

--Where T2.WhsCode='09'

Where T3.DocDate>='[%1]' and T3.DocDate<='[%2]' and t6.SeriesName ='[%3]' and T0.status ='[%4]'

GROUP BY T0.[DocNum] , T0.[ItemCode], T1.[ItemName], T0.[PlannedQty],T0.CmpltQty,t0.RjctQty, T2.[Quantity], T3.docNum,

T0.status,T0.POSTDATE, T3.DocDate, T2.LineTotal,T6.SeriesName,T6.Series,T0.status,T2.[TranType],T0.U_SUBCONT,T0.COMMENTS,T7.U_NAME

Prod No Status Date Item Des Planned Completed Open QTY 40401 R 10/2/2013 ABSCSC 14,500.00 1,475.00 375 40401 R 10/2/2013 CGAGGH 14,500.00 12,650.00 375