Cheque Register / Check Payment Register

 

SELECT OCHO.CheckNum, OCHO.CheckDate, OCHO.PmntDate as PaymentDate, OCHO.CheckSum AS CheckAmount, isnull(OCHO.VendorName,) as VendorName, isnull(OCHO.VendorCode,) as VendorName,

                       OVPM.doctotal + isnull(sum(VPM2.WtAppld),0)  AS DocTotal, isnull(sum(VPM2.vatApplied),0) AS WHT, isnull(sum(VPM2.WtAppld),0) AS Tax, isnull(VPM4.AcctCode,) as Control_Account_Code, isnull(VPM4.AcctName,) as Control_Account_Name,CASE when OCHO.PrnConfrm = ‘Y’ then ‘Confirm’ else ‘Not Confirm’ end isConfirmed,CASE when OCHO.Printed = ‘Y’ then ‘Printed’ else ‘Not Printed’ end isPrinted

                     

FROM         OCHO INNER JOIN

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

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

                      VPM2 ON OVPM.DocNum = VPM2.DocNum

where OCHO.Canceled <> ‘Y’

GROUP BY OVPM.CounterRef, OCHO.CheckNum, OCHO.CheckDate,OCHO.CheckSum, OCHO.PmntDate, OCHO.VendorName, OCHO.VendorCode,OVPM.doctotal, OCHO.CheckAcct, VPM4.AcctCode, VPM4.AcctName,OCHO.PrnConfrm,OCHO.Printed,OCHO.Canceled

 

 

Cheque Register / Check Payment Register (Current Month Only)

Declare @FromDate datetime

Set @FromDate = getdate()

SELECT OCHO.CheckNum, OCHO.CheckDate, OCHO.PmntDate as PaymentDate, OCHO.CheckSum AS CheckAmount, isnull(OCHO.VendorName,) as VendorName, isnull(OCHO.VendorCode,) as VendorName,

                       OVPM.doctotal + isnull(sum(VPM2.WtAppld),0)  AS DocTotal, isnull(sum(VPM2.vatApplied),0) AS WHT, isnull(sum(VPM2.WtAppld),0) AS Tax, isnull(VPM4.AcctCode,) as Control_Account_Code, isnull(VPM4.AcctName,) as Control_Account_Name,CASE when OCHO.PrnConfrm= ‘Y’ then ‘Confirm’ else ‘Not Confirm’ end isConfirmed,CASE when OCHO.Printed = ‘Y’ then ‘Printed’ else ‘Not Printed’ end isPrinted

                     

FROM         OCHO INNER JOIN

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

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

                      VPM2 ON OVPM.DocNum = VPM2.DocNum

where OCHO.Canceled <> ‘Y’

and Month(OCHo.CheckDate) = DATEPART(MM,@FromDate)

GROUP BY OVPM.CounterRef, OCHO.CheckNum, OCHO.CheckDate,OCHO.CheckSum, OCHO.PmntDate, OCHO.VendorName, OCHO.VendorCode,OVPM.doctotal, OCHO.CheckAcct, VPM4.AcctCode, VPM4.AcctName,OCHO.PrnConfrm,OCHO.Printed,OCHO.Canceled

 

 

Incoming Payment List (All)

 

SELECT     ORCT.DocEntry as IncomingPaymentNo, ORCT.DocDate as PostingDate, ORCT.TaxDate as DocumentDate, ORCT.JrnlMemo, ORCT.DocTotal, isnull(ORCT.Comments,) as Comments, isnull(RCT4.Descrip,) as Description,

                      isnull(RCT2.DocEntry,) AS AR_Invoice_No,ORCT.TransId as JENo,

                      CASE when isnull(CardName,) = then CardCode else CardName end as Party

FROM         ORCT LEFT OUTER JOIN

                      RCT2 ON ORCT.DocNum = RCT2.DocNum LEFT OUTER JOIN

                      RCT4 ON ORCT.DocNum = RCT4.DocNum

 

 

Outgoing Payment List (Current Month)

 

Declare @FromDate datetime

Set @FromDate = ’01-Jun-2014′

SELECT isnull(OCHO.CheckNum,) as CheckNumber,OVPM.Docdate as PostingDate,OVPM.TaxDate as DocumentDate, isnull(OCHO.CheckSum,0) AS CheckAmount, isnull(OCHO.VendorName,) as VendorName, 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 Month(OVPM.TaxDate) = DATEPART(MM,@FromDate)

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

 

 

Outgoing Payment List (All)

 

SELECT isnull(OCHO.CheckNum,) as CheckNumber,OVPM.Docdate as PostingDate,OVPM.TaxDate as DocumentDate, isnull(OCHO.CheckSum,0) AS CheckAmount, isnull(OCHO.VendorName,) as VendorName, 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

 

Payment Draft Records (Not Posted)

 

SELECT     OPDF.DocEntry, OPDF.DocDate as PostingDate,OPDF.TaxDate as DocumentDate, isnull(OPDF.CardCode,) as SupplierCode, isnull(OPDF.CardName,) as SupplierName, OPDF.CheckSum, OPDF.DocTotal, isnull(OPDF.Comments,),  isnull(PDF1.CheckNum,) as  CheckNumber, isnull(PDF1.BankCode,) as BankCode, isnull(PDF1.Branch,) as BankName, OUSR.U_NAME as UserName, OUSR.USER_CODE as UserCode,OPCH.DocTotal AS InvoiceTotal,opch.TotalExpns,OPDF.DocNum,opdf.DocEntry

FROM         PDF1 RIGHT OUTER JOIN

OPDF INNER JOIN

OUSR ON OPDF.UserSign = OUSR.USERID INNER JOIN

OPCH ON OPDF.DocEntry = OPCH.DocEntry ON PDF1.DocNum = OPDF.DocEntry

WHERE     (OPDF.Canceled <> ‘Y’)

 

 

With-Holding Tax Details

 

select DocNum,Cardcode,CardName,DocTotal,sum(WGST) as WGST,sum(WH00) as WH00,sum(WH01) as WH01,sum(WH02) as WH02,sum(WH03) as WH03,sum(WH04) as WH04,sum(WH05) as WH05,sum(Wh06) as WH06 from

(select OVPM.DocNum,CardCode,CardName,DocTotal,sum(VPM6.WTSum) as WGST,0 as WH00,0 as WH01,0 as WH02,0 as WH03,0 as WH04,0 as WH05,0 as WH06 from OVPM

INNER JOIN VPM6 ON OVPM.DocNum = VPM6.DocNum

where vpm6.WTCode = ‘WGST’

group by OVPM.DocNum,CardCode,CardName,DocTotal,vpm6.WTCode

UNION ALL

select OVPM.DocNum,CardCode,