Need to modify the GRN Report

[Former Member]

Dear All

I created one GRN Query report in this I m getting the data but I need If in 1 GRN if Line item is more then one then system should give ap invoice and its amount more then one time but i want only one time customer vendor detail with AR invoice detail against grpo more then one line item

query is

SELECT OPDN.DocNum, PDN1.Dscription, PDN1.Quantity, PDN1.UomCode, PDN1.VatSum, OPDN.CardName, OPCH.DocNum AS [AP Inv Doc No],

OPOR.DocNum AS [PO Doc No], OPOR.DocDate, POR1.Quantity AS [PO Qty], OPCH.DocTotal, PDN1.FreeTxt, PDN1.Text

FROM OPOR INNER JOIN

POR1 ON OPOR.DocEntry = POR1.DocEntry RIGHT OUTER JOIN

OPCH INNER JOIN

PCH1 ON OPCH.DocEntry = PCH1.DocEntry RIGHT OUTER JOIN

OPDN INNER JOIN

PDN1 ON OPDN.DocEntry = PDN1.DocEntry ON PCH1.BaseEntry = PDN1.DocEntry AND PCH1.BaseLine = PDN1.LineNum ON

POR1.LineNum = PDN1.BaseLine AND POR1.DocEntry = PDN1.BaseEntry

Thanks

Ricky

[Former Member · Accepted solution]

HI Ricky

It is not possible in query report you can use crystal report and use group  by function in crystal.

Thanks

Mohammad Imran

[Former Member · Accepted solution]

Bro I m sorry but I dont know how to work on crystal ...Recently I started to learn Crystal

Could you please help in this ?

Thanks

Ricky

[Former Member · Accepted solution]

Hi

Please provide your email i will send you this report on crystal.

Thanks

Mohammad Imran

[Former Member · Accepted solution]

vaibhav22uk@yahoo,co.in

[Former Member · Accepted solution]

Hi

Ricky Check your mail

Thanks

Mohammad Imran

[kothandaraman_nagarajan]

Hi,

Try this query:

Select t3.docnum as GRPO#,t2.Dscription, t2.Quantity, t2.VatSum,T3.CardName, T5.DocNum AS [AP Inv Doc No],

t1.DocNum AS [PO Doc No], t1.DocDate, t0.Quantity AS [PO Qty], t5.DocTotal, t2.FreeTxt, t2.Text

FROM POR1 T0  INNER JOIN OPOR T1 ON T0.DocEntry = T1.DocEntry 

   left outer join PDN1 T2 on T2.BaseEntry = T0.DocEntry and T2.BaseLine = T0.Linenum  

   left outer join OPDN T3 on T2.DocEntry = T3.DocEntry 

  left Outer join PCH1 T4 on T4.BaseEntry = T3.DocEntry and T4.BaseLine = T2.Linenum  and T4.BaseType = 17 

       OR (T4.Basetype=22 and T4.BaseEntry=T0.DocEntry and T4.BaseLine=T0.LineNum) 

LEFT outer join RPD1 T11 on T11.BaseEntry = T2.DocEntry and T11.BaseLine = T2.LineNum 

LEFT outer join ORPD T12 on T11.DocEntry = T12.DocEntry 

   left outer join OPCH T5 on T5.DocEntry = T4.DocEntry 

   left Outer join RPC1 T6 on T6.BaseEntry = T5.DocEntry and T6.BaseLine = T4.Linenum  

  left outer join ORPC T7 on T6.DocEntry = T7.DocEntry

Thanks & Regards,

Nagarajan