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,