Query for Receipt from Production

[amol_bairagi]

Dear Experts,

In the below query please add selection criteria Production Date to Receipt Date and Product Code for selection.

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

T0.[PlannedQty], T2.DocEntry 'Rec Doc', T3.DocDate 'Rec Date', T2.[Quantity], T2.LineTotal 'FG Value',

Sum(T4.LineTotal) 'RM Value' 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

--Where  T2.WhsCode='09'

GROUP BY T0.[DocNum] , T0.[ItemCode], T1.[ItemName], T0.[PlannedQty], T2.[Quantity], T2.DocEntry,

T0.status,T0.POSTDATE, T3.DocDate, T2.LineTotal

Regards

Amol

[former_member209066]

Hi,

Check it

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

T0.[PlannedQty], T2.DocEntry 'Rec Doc', T3.DocDate 'Rec Date', T2.[Quantity], T2.LineTotal 'FG Value',

Sum(T4.LineTotal) 'RM Value' 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

--Where  T2.WhsCode='09'

Where T0.PostDate>='[%1]' and T0.PostDate<='[%2]' and T3.DocDate>='[%1]' and T3.DocDate<='[%2]' and T0.ItemCode='[%3]'

GROUP BY T0.[DocNum] , T0.[ItemCode], T1.[ItemName], T0.[PlannedQty], T2.[Quantity], T2.DocEntry,

T0.status,T0.POSTDATE, T3.DocDate, T2.LineTotal

Thanks,

Nithi

[amol_bairagi]

Dear All,

Thanks for Reply but i want add Series also in the present Report

Regards

Amol

[former_member209066]

Hi,

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

T0.[PlannedQty], T2.DocEntry 'Rec Doc', T3.DocDate 'Rec Date', T2.[Quantity], T2.LineTotal 'FG Value',

Sum(T4.LineTotal) 'RM Value' 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

--Where  T2.WhsCode='09'

Where T0.PostDate>='[%1]' and T0.PostDate<='[%2]' and T3.DocDate>='[%1]' and T3.DocDate<='[%2]' and T0.ItemCode='[%3]'

GROUP BY T0.[DocNum] , T0.[ItemCode], T1.[ItemName], T0.[PlannedQty], T2.[Quantity], T2.DocEntry,

T0.status,T0.POSTDATE, T3.DocDate, T2.LineTotal,T6.SeriesName,T6.Series

Thanks,

Nithi

[KennedyT21]

Hi Amol,

Try This

/* select * from dbo. [OIGN] A

INNER JOIN [dbo]. Ign1 B ON A.DOCNUM=B.DOCENTRY */

   DECLARE @D1 DATETIME

   DECLARE @D2 NVARCHAR(25)

   DECLARE @D4 NVARCHAR(25)

SET @D1 = /* A.DOCDATE */ '[%1]'

SET @D2 = /* b.Itemcode */ '[%2]'

SET @D4 = /* a.Series */ '[%3]'

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

   T0.[PlannedQty], T2.DocEntry 'Rec Doc', T3.DocDate 'Rec Date', T2.[Quantity], T2.LineTotal 'FG Value',

   Sum(T4.LineTotal) 'RM Value' 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

Where   T3.CreateDate=@d1 and t4.ItemCode = @d2 and t3.Series =@d4

GROUP BY T0.[DocNum] , T0.[ItemCode], T1.[ItemName], T0.[PlannedQty], T2.[Quantity], T2.DocEntry,T0.status,T0.POSTDATE, T3.DocDate, T2.LineTotal,T3.Series

Regards

Kennedy

[amol_bairagi]

Dear Nithi,

Thanks for Reply

[KennedyT21]

Close the Tread with helpful or correct answer.

Regards

Kennedy

[amol_bairagi]

Dear Experts

I already add below trantype in the query but i want  Rejection Qty & Complete Qty in separete column  & total of Complete & Rjction Qty in Query.

Dear Experts,

In the below query please add selection criteria Production Date to Receipt Date and Product Code for selection.

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

T0.[PlannedQty], T2.DocEntry 'Rec Doc', T3.DocDate 'Rec Date', T2.[TranType],T2.[Quantity],T0.CmpltQty As "Completed Qty", T0.PlannedQty - T0.CmpltQty - t0.RjctQty As "Open Qty",T0.RjctQty As "Rejected Qty",T2.LineTotal 'FG Value',

Sum(T4.LineTotal) 'RM Value' 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

--Where  T2.WhsCode='09'

Where T0.PostDate>='[%1]' and T0.PostDate<='[%2]' and 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], T2.DocEntry,

T0.status,T0.POSTDATE, T3.DocDate, T2.LineTotal,T6.SeriesName,T6.Series,T0.status,T2.[TranType]

Regards

Amol

[KennedyT21]

I think you first question has been answered, close the tread with correct answer and helpful answer and raise a new tread with the new requirement.

Regards

Kennedy

[amol_bairagi]

Dear Kennedy,

Thanks for Reply but i just add in query table IGNI Rejection Amount & Complete Qty in separate column if possible, then i will close the Thread beacuse client requirement not to be fulfill.

Regards

Amol

[KennedyT21]

Yes It is Possible....

[amol_bairagi]

Dear Kennedy,

Please provide the Field in the query.

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], T2.DocEntry 'Rec Doc', T3.DocDate 'Rec Date',

T2.[Quantity],T0.CmpltQty As "Completed Qty", (T0.PlannedQty - T0.CmpltQty - t0.RjctQty )As "Open Qty",

T0.RjctQty As "Rejected Qty",

isnull((case when T2.TranType='R' then t2.linetotal  end),0)  as RejectedVal,
isnull((case when T2.TranType='C' then sum(t2.Quantity)  end ),0) as Cmplqty,

T2.LineTotal 'FG Value',

Sum(T4.LineTotal) 'RM Value' 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

Where T3.docdate>='[%1]' and T0.PostDate<='[%2]' and 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], T2.DocEntry,

T0.status,T0.POSTDATE, T3.DocDate, T2.LineTotal,T6.SeriesName,T6.Series,T0.status,T2.[TranType]

Regards

Kennedy

[amol_bairagi]

As per your query In same Receipt from Production Doc Completed and Rejected comes in same but Compelete Qty & Rejected Qty comes 2 time in Report & Complete Qty was wrong in Report.

For Clear expalnation

Product No.	Item Description	Planned Quantity - Header	Rec Doc	Rec Date	Compete & Rejected Quantity	Open Qty	Rejected Qty	RejectedVal	Cmplqty
CFLW0062	ABC	33,000.00	738	22/07/12	300	0	300	14,342.76	0
CFLW0062	CDE	33,000.00	738	22/07/12	32,700.00	0	300	0	850,200.00

Regards

Amol

[Former Member]

Hi Amol..........

Take a look at this..

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

T0.[PlannedQty], T2.DocEntry 'Rec Doc', T3.DocDate 'Rec Date', T2.[Quantity], T2.LineTotal 'FG Value',

Sum(T4.LineTotal) 'RM Value' 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

Where  T0.Ite,Code='[%0]' and T0.PostDate>='[%1]' and T3.DocDate<='[%2]'

GROUP BY T0.[DocNum] , T0.[ItemCode], T1.[ItemName], T0.[PlannedQty], T2.[Quantity], T2.DocEntry,

T0.status,T0.POSTDATE, T3.DocDate, T2.LineTotal

Regards,

Rahul

[Former Member]

Dear Amol,

Try:

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

T0.[PlannedQty], T2.DocEntry 'Rec Doc', T3.DocDate 'Rec Date', T2.[Quantity], T2.LineTotal 'FG Value',

Sum(T4.LineTotal) 'RM Value' 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

Where   T0.POSTDATE >= [%0] and T3.DocDate<=[%1] and t4.ItemCode = '[%2]'

GROUP BY T0.[DocNum] , T0.[ItemCode], T1.[ItemName],

T0.[PlannedQty], T2.[Quantity], T2.DocEntry,T0.status,T0.POSTDATE, T3.DocDate, T2.LineTotal

Thanks,

Gordon

[KennedyT21]

HI Amol

If you want the same report for given date Range this

/* select * from dbo. [ORDR] A

INNER JOIN [dbo]. RDR1 B ON A.DOCNUM=B.DOCENTRY */

DECLARE @D1 DATETIME

DECLARE @D3 DATETIME

DECLARE @D2 NVARCHAR(25)

SET @D1 = /* A.DOCDATE */ '[%0]'

SET @D3 = /* A.DOCDATE */ '[%1]'

SET @D2 = /* b.Itemcode */ '[%2]'

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

T0.[PlannedQty], T2.DocEntry 'Rec Doc', T3.DocDate 'Rec Date', T2.[Quantity], T2.LineTotal 'FG Value',

Sum(T4.LineTotal) 'RM Value' 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

Where   T3.CreateDate between @d1 and @d3 and t4.ItemCode = @d2

GROUP BY T0.[DocNum] , T0.[ItemCode], T1.[ItemName],

T0.[PlannedQty], T2.[Quantity], T2.DocEntry,T0.status,T0.POSTDATE, T3.DocDate, T2.LineTotal

Regards

Kennedy

[Former Member]

see this link http://scn.sap.com/message/8413492

[KennedyT21]

HI Amol,

Try This

I Have take the Receipt date as Production date ,, if Not revert back

/* select * from dbo. [ORDR] A

INNER JOIN [dbo]. RDR1 B ON A.DOCNUM=B.DOCENTRY */

DECLARE @D1 DATETIME

DECLARE @D2 NVARCHAR(25)

SET @D1 = /* A.DOCDATE */ '[%1]'

SET @D2 = /* b.Itemcode */ '[%2]'

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

T0.[PlannedQty], T2.DocEntry 'Rec Doc', T3.DocDate 'Rec Date', T2.[Quantity], T2.LineTotal 'FG Value',

Sum(T4.LineTotal) 'RM Value' 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

Where   T3.CreateDate=@d1 and t4.ItemCode = @d2

GROUP BY T0.[DocNum] , T0.[ItemCode], T1.[ItemName], T0.[PlannedQty], T2.[Quantity], T2.DocEntry,T0.status,T0.POSTDATE, T3.DocDate, T2.LineTotal

Regards

Kennedy