Query Required

[Former Member]

Can Someone Help to Generate Query of Outgoing Payment along with Tax Deduction

[Former Member · Accepted solution]

Hello Raza,

please try following query and let me know if any more you required

Declare @Fromdate as Datetime

Declare @Todate as Datetime

Set @Fromdate=(select MIN(T0.DocDate) from OVPM T0 where T0.DocDate>='[%0]')

Set @Todate=(select MAX(T1.DocDate) from OVPM T1 where T1.DocDate <='[%1]')

SELECT isnull(OCHO.CheckNum,'') as CheckNumber,OVPM.Docdate as PostingDate,OVPM.TaxDate asDocumentDate, isnull(OCHO.CheckSum,0) AS CheckAmount, isnull(OCHO.VendorName,'') asVendorName, isnull(OCHO.VendorCode,'') as vendorCode,

                       OVPM.doctotal + isnull(sum(VPM2.WtAppld),0)  AS DocTotal,isnull(sum(VPM2.WtAppld),0) AS Tax, isnull(VPM4.AcctCode,'') as accountCode,isnull(VPM4.AcctName,'') as AccountName

                       ,case when isnull(OCHO.CheckNum,'') = 0 then 'Cash Payment'

                       else 'Bank Payment'

                       end as PaymentType

FROM         OVPM LEFT OUTER JOIN

                      OCHO ON OVPM.DocNum = OCHO.PmntNum LEFT OUTER JOIN

                      VPM2 ON OVPM.DocNum = VPM2.DocNum LEFT OUTER JOIN

                      VPM4 ON OVPM.DocNum = VPM4.DocNum

where OVPM.Canceled <> 'Y'

and OVPM.DocDate >=@Fromdate and OVPM.DocDate <=@Todate

GROUP BY OVPM.CounterRef, OCHO.CheckNum, OVPM.docdate,OVPM.TaxDate, OCHO.CheckSum,OCHO.PmntDate, OCHO.VendorName, OCHO.VendorCode,OVPM.doctotal, OCHO.CheckAcct,VPM4.AcctCode,VPM4.AcctName,OCHO.PrnConfrm,OCHO.Printed,OCHO.Canceled,OVPm.DocNum,OVpm.DocEntry

order by OVPM.Docdate

[Former Member · Accepted solution]

Doc Number & Doc Nature is also Required ,& it will be as I click on Doc Number can view this doc

[Former Member · Accepted solution]

Hello Raza,

So you want docnum as parameter or just want one column which will show docnumber?

and what means of Doc Nature Is it like Open,Close or cancelled ?

[Former Member · Accepted solution]

Doc Nature is Check Payment or Cash Payment  & Just want column which shows the doc numbers & when I click any of them (doc number ) it should open that document

[Former Member · Accepted solution]

Hello Raza,

Hope this query will fulfill your requirement

Declare @Fromdate as Datetime

Declare @Todate as Datetime

Set @Fromdate=(select MIN(T0.DocDate) from OVPM T0 where T0.DocDate>='[%0]')

Set @Todate=(select MAX(T1.DocDate) from OVPM T1 where T1.DocDate <='[%1]')

SELECT OVPM.DocNum, case when isnull(OCHO.CheckNum,'') = 0 then 'Cash Payment' else 'Bank Payment'end as PaymentType,isnull(OCHO.CheckNum,'') as CheckNumber,

OVPM.Docdate as PostingDate,OVPM.TaxDate asDocumentDate, isnull(OCHO.CheckSum,0) AS CheckAmount, isnull(OCHO.VendorName,'') asVendorName, isnull(OCHO.VendorCode,'') as vendorCode,

                       OVPM.doctotal + isnull(sum(VPM2.WtAppld),0)  AS DocTotal,isnull(sum(VPM2.WtAppld),0) AS Tax, isnull(VPM4.AcctCode,'') as accountCode,isnull(VPM4.AcctName,'') as AccountName

FROM         OVPM LEFT OUTER JOIN

OCHO ON OVPM.DocNum = OCHO.PmntNum LEFT OUTER JOIN

VPM2 ON OVPM.DocNum = VPM2.DocNum LEFT OUTER JOIN

VPM4 ON OVPM.DocNum = VPM4.DocNum

where OVPM.Canceled <> 'Y'

and OVPM.DocDate >=@Fromdate and OVPM.DocDate <=@Todate

GROUP BY OVPM.CounterRef, OCHO.CheckNum, OVPM.docdate,OVPM.TaxDate, OCHO.CheckSum,OCHO.PmntDate, OCHO.VendorName, OCHO.VendorCode,

OVPM.doctotal, OCHO.CheckAcct,VPM4.AcctCode,VPM4.AcctName,OCHO.PrnConfrm,OCHO.Printed,OCHO.Canceled,OVPm.DocNum,OVpm.DocEntry

order by OVPM.DocNum

[Former Member · Accepted solution]

Bundle of Thanks Dear

Could you plz add one more columns. Rather Payment against "PU,JE, or DT.

[Former Member]

Hi Raza,

Try below query

SELECT isnull(OCHO.CheckNum,'') as CheckNumber,OVPM.Docdate as PostingDate,OVPM.TaxDate asDocumentDate, isnull(OCHO.CheckSum,0) AS CheckAmount, isnull(OCHO.VendorName,'') asVendorName, isnull(OCHO.VendorCode,'') as vendorCode,

                       OVPM.doctotal + isnull(sum(VPM2.WtAppld),0)  AS DocTotal,isnull(sum(VPM2.WtAppld),0) AS Tax, isnull(VPM4.AcctCode,'') as accountCode,isnull(VPM4.AcctName,'') as AccountName

                       ,case when isnull(OCHO.CheckNum,'') = 0 then 'Cash Payment'

                       else 'Bank Payment'

                       end as PaymentType

FROM         OVPM LEFT OUTER JOIN

                      OCHO ON OVPM.DocNum = OCHO.PmntNum LEFT OUTER JOIN

                      VPM2 ON OVPM.DocNum = VPM2.DocNum LEFT OUTER JOIN

                      VPM4 ON OVPM.DocNum = VPM4.DocNum

where OVPM.Canceled <> 'Y'

GROUP BY OVPM.CounterRef, OCHO.CheckNum, OVPM.docdate,OVPM.TaxDate, OCHO.CheckSum,OCHO.PmntDate, OCHO.VendorName, OCHO.VendorCode,OVPM.doctotal, OCHO.CheckAcct,VPM4.AcctCode,VPM4.AcctName,OCHO.PrnConfrm,OCHO.Printed,OCHO.Canceled,OVPm.DocNum,OVpm.DocEntry

order by OVPM.Docdate

Thanks,

Harshal

[Former Member]

Thanks Dear it helps a lot .can you create the time bound (From Date to Date ) and Document number also

[Former Member]

Hi Raza,

SELECT isnull(T1.CheckNum,'') as CheckNumber,T0.Docdate as PostingDate,T0.TaxDate asDocumentDate,

isnull(T1.CheckSum,0) AS CheckAmount, isnull(T1.VendorName,'') asVendorName, isnull(T1.VendorCode,'') as vendorCode,

                       T0.doctotal + isnull(sum(T2.WtAppld),0)  AS DocTotal,isnull(sum(T2.WtAppld),0) AS Tax,

     isnull(T3.AcctCode,'') as accountCode,isnull(T3.AcctName,'') as AccountName

                       ,case when isnull(T1.CheckNum,'') = 0 then 'Cash Payment'

                       else 'Bank Payment'

                       end as PaymentType

FROM         OVPM T0 LEFT OUTER JOIN

                      OCHO T1 ON T0.DocNum = T1.PmntNum LEFT OUTER JOIN

                      VPM2 T2 ON T0.DocNum = T2.DocNum LEFT OUTER JOIN

                      VPM4 T3 ON T0.DocNum = T3.DocNum

where T0.Canceled <> 'Y' and T0.DocDate >= [%0] and T0.DocDate <= [%1] and T0.DocNum = [%2]

GROUP BY T0.CounterRef, T1.CheckNum, T0.docdate,T0.TaxDate, T1.CheckSum,T1.PmntDate,

T1.VendorName, T1.VendorCode,T0.doctotal, T1.CheckAcct,T3.AcctCode,T3.AcctName,

T1.PrnConfrm,T1.Printed,T1.Canceled,T0.DocNum,T0.DocEntry

order by T0.Docdate

Thanks,

Harshal

[Former Member]

Thanks Dear

Dated Filter Required only (No Doc Filter) and the remaining Report as attached Your Co Operation is highly appreciated .