Query Changes in Report

[amol_bairagi]

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

Regards

Amol

[kothandaraman_nagarajan]

Hi Amol,

To get clear report, better separate planned and released report.

If you combine planned and released, your completed quantity will be not correct.

In planned status, there is no completed quantity.

[former_member325312]

Hi Amol

Try this 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  T3.DocDate>='[%0]' and T3.DocDate<='[%1]'  and t6.SeriesName ='[%3]'

and (T0.status ='P' Or T0.status ='R')

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,T0.U_SUBCONT,T2.[TranType],T0.COMMENTS,T7.U_NAME

Order By T0.status

Regards

Jenny

[amol_bairagi]

Dear Jenny,

Thanks for Reply but when i execute these query i have not received any record which is in 'Planned Status'.

Regards

Amol

[KennedyT21]

Hi Amol...

Try This

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 outer JOIN IGN1 T2 On T0.DocNum=T2.BaseRef And T0.ItemCode=T2.ItemCode

Left outer Join OIGN T3 On T2.DocEntry=T3.DocEntry

LEFT outer JOIN IGE1 T4 On T0.DocNum=T4.BaseRef

Left  outer 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  T3.DocDate>='[%0]' and T3.DocDate<='[%1]'  and t6.SeriesName ='[%3]'

and (T0.status ='P' Or T0.status ='R')

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,T0.U_SUBCONT,T2.[TranType],T0.COMMENTS,T7.U_NAME

Order By T0.status

Regards

Kennedy

[amol_bairagi]

Dear Kennedy,

I have run these query but i unable to find Planned status Qty in these present query (As per me that is my WIP Qty).

(Only Released status data come in these Report with Duplicate Qty come in Report).

Kindly help me out of these Query Report.

Regards

Amol

[amol_bairagi]

This message was moderated.

[VamcParimisetti]

Hi Amol,

In Where Condition Change the T0.Status ='Released' or  T0.Status='Planned'

[amol_bairagi]

Hi Vamsi,

Thanks for Reply i have change the query but their is no result as such.

Data no found in Report.

Regards

Amol