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,CardName,DocTotal,0 as WGST,sum(VPM6.WTSum) 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 = ‘WH00’

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

UNION ALL

select OVPM.DocNum,CardCode,CardName,DocTotal,0 as WGST,0 as WH00,sum(VPM6.WTSum) 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 = ‘WH01’

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

UNION ALL

select OVPM.DocNum,CardCode,CardName,DocTotal,0 as WGST,0 as WH00,0 as WH01,sum(VPM6.WTSum) 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 = ‘WH02’

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

Union ALL

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

INNER JOIN VPM6 ON OVPM.DocNum = VPM6.DocNum

where vpm6.WTCode = ‘WH03’

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

UNION ALL

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

INNER JOIN VPM6 ON OVPM.DocNum = VPM6.DocNum

where vpm6.WTCode = ‘WH04’

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

UNION ALL

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

INNER JOIN VPM6 ON OVPM.DocNum = VPM6.DocNum

where vpm6.WTCode = ‘WH05’

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

UNION ALL

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

INNER JOIN VPM6 ON OVPM.DocNum = VPM6.DocNum

where vpm6.WTCode = ‘WH06’

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

group by DocNum,Cardcode,CardName,DocTotal


Payment Detail – Transaction Ledger (Parameter: Date Range)


Declare @FromDate datetime

Declare @ToDate datetime

Set @FromDate = ’01-Jan-2014′

Set @ToDate = ’01-Mar-2014′

SELECT      OVPM.DocEntry as VoucherNo,  OVPM.DocDate as PostingDate,OVPM.TaxDate as DocumentDate,  OVPM.CardCode as PartyCode, OVPM.CardName as PartyName, VPM1.CheckNum, OVPM.Comments,

                      OVPM.JrnlMemo,0 as Opening_Amount,OVPM.DocTotal as Outgoing_Payment,0 as Incoming_Payment

FROM         OVPM LEFT OUTER JOIN

                      VPM1 ON OVPM.DocNum = VPM1.DocNum

where TaxDate between @FromDate and @ToDate and OVPM.Canceled <> ‘Y’ and OVPM.Series <> 18

UNION ALL

SELECT ORCT.DocEntry as VoucherNo,  ORCT.DocDate as PostingDate,ORCT.TaxDate as DocumentDate, ORCT.CardCode  as PartyCode, ORCT.CardName  as PartyName, RCT1.CheckNum, ORCT.Comments,

                      ORCT.JrnlMemo,0 as Opening_Amount,0 as Outgoing_Payment, ORCT.DocTotal as Incoming_Payment

FROM         ORCT LEFT OUTER JOIN

                      RCT1 ON ORCT.DocNum = RCT1.DocNum

where TaxDate between @FromDate and @ToDate and ORCT.Canceled <> ‘Y’

UNION ALL

(

Select 0 as DocEntry, @FromDate as DocDate,@FromDate as TaxDate, as CardCode, as CardName, as CheckNum, ‘Opening Balance’ as Comments,

                      as JrnlMemo,sum(Incoming_Payment outgoing_Payment) as Opening_Amount, 0 as Outgoing_Payment,0 as Incoming_Payment from

(

SELECT     OVPM.DocEntry as VoucherNo, OVPM.DocType, OVPM.DocDate as PostingDate, OVPM.TaxDate as DocumentDate, OVPM.DocDueDate, OVPM.CardCode  as PartyCode, OVPM.CardName  as PartyName, VPM1.CheckNum, OVPM.Comments,

                      OVPM.JrnlMemo, OVPM.DocTotal as Outgoing_Payment,0 as Incoming_Payment

FROM         OVPM LEFT OUTER JOIN

            VPM1 ON OVPM.DocNum = VPM1.DocNum

where OVPM.Canceled <> ‘Y’ and OVPM.Series <> 18

UNION ALL

SELECT ORCT.DocEntry as VoucherNo,ORCT.DocType,  ORCT.DocDate as PostingDate, ORCT.DocDueDate,ORCT.TaxDate as DocumentDate, ORCT.CardCode  as PartyCode, ORCT.CardName  as PartyName, RCT1.CheckNum, ORCT.Comments,

                      ORCT.JrnlMemo,0 as Outgoing_Payment, ORCT.DocTotal as Incoming_Payment

FROM         ORCT LEFT OUTER JOIN

                      RCT1 ON ORCT.DocNum = RCT1.DocNum

                      where ORCT.Canceled <> ‘Y’

) Fintab

where DocumentDate <= @FromDate

) order by TaxDate,DocEntry



Vendor Aging – Payable Due


declare @FromDate datetime

set @FromDate = GetDate()

Select CardName,CardName,TotalDays as AllowedDays,NumAtCard,TaxDate,DocTotal,DueOnDate,

isnull(CASE when OverDueDays between 30 and 1 then OverDueDays end,0) ‘0-30’,

ISNULL(CASE when OverDueDays between 60 and 31 then OverDueDays end ,0) ’31-60′,

ISNULL(CASE when OverDueDays between 90 and 61 then OverDueDays end ,0) ’61-90′,

ISNULL(CASE when OverDueDays between 140 and 91 then OverDueDays end ,0) ’91-140′,

ISNULL(CASE when OverDueDays between 250 and 141 then OverDueDays end ,0) ‘250-141’,

ISNULL(CASE when OverDueDays < 251 then OverDueDays end ,0) ‘Above 250’

from

(

SELECT     OPDN.DocEntry,OPDN.TaxDate, OPDN.NumAtCard,OPDN.Comments, OCTG.CredLimit, OPDN.CardCode,

                      OPDN.CardName, (OCTG.ExtraMonth * 30) + OCTG.ExtraDays as TotalDays,DATEDIFF(DAY,getdate(),DATEADD(DAY,(OCTG.ExtraMonth * 30) + OCTG.ExtraDays,OPDN.TaxDate)) as OverDueDays,DATEADD(DAY,(OCTG.ExtraMonth * 30) + OCTG.ExtraDays,OPDN.TaxDate) as DueOnDate,OPDN.DocTotal

FROM         OPDN INNER JOIN

                       OCTG ON OPDN.GroupNum = OCTG.GroupNum INNER JOIN

                       PDN1 on OPDN.DocEntry = PDN1.DocEntry Left Join

                       PCH1 On PDN1.DocEntry = pch1.BaseDocNum INNER JOIN

                       OPCH on PCH1.DocEntry = OPCH.DocEntry

where OPDN.CANCELED <> ‘Y’  and OPCH.DocStatus <> ‘C’ and DATEDIFF(DAY,getdate(),DATEADD(DAY,(OCTG.ExtraMonth * 30) + OCTG.ExtraDays,OPDN.TaxDate)) <= 0

group by OPDN.DocEntry,OPDN.TaxDate, OPDN.NumAtCard,OPDN.Comments, OCTG.CredLimit, OPDN.CardCode,

OPDN.CardName, (OCTG.ExtraMonth * 30) + OCTG.ExtraDays,OPDN.DocTotal

) as Fintab



Current Month – Due Payment


SELECT     OPCH.DocEntry,opch.TaxDate, OPCH.NumAtCard,OPCH.Comments, OCTG.CredLimit, OPCH.CardCode,

                      OPCH.CardName, (OCTG.ExtraMonth * 30) + OCTG.ExtraDays as TotalDays,DATEDIFF(DAY,getdate(),DATEADD(DAY,(OCTG.ExtraMonth * 30) + OCTG.ExtraDays,OPCH.TaxDate)) as OverDueDays,DATEADD(DAY,(OCTG.ExtraMonth * 30) + OCTG.ExtraDays,OPCH.TaxDate) as DueOnDate

FROM         OPCH INNER JOIN

                      OCTG ON OPCH.GroupNum = OCTG.GroupNum

where DocStatus <> ‘C’ and DATEADD(DAY,(OCTG.ExtraMonth * 30) + OCTG.ExtraDays,OPCH.TaxDate) < GETDATE()




Party Ledger – Cancelled and Reversal Excluded

change the Vendor Code and Dates according to your need.


SELECT ’26-Nov-2013′ AS RefDate, as Transtype,

’26-Nov-2013′ AS TaxDate, AS Ref1,   AS BaseRef,

‘Opening Balance’ AS LineMemo, AS TransID, T3.CardName,   ‘OffsetAcct’,

as Account,T1.ShortName,Isnull(Sum(T1.Debit),0) AS Debit,

Isnull(Sum(T1.Credit),0) as Credit,isnull(sum(T1.Debit),0) isnull(sum(T1.Credit),0) as Balance,

       as TransType2, as Status,OPCH.CANCELED FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransID=T1.TransID  

       LEFT OUTER JOIN OACT T2 ON T1.Account=T2.AcctCode INNER JOIN OCRD T3 ON T1.Shortname = T3.CardCode

       LEFT OUTER JOIN dbo.OPCH ON T1.BaseRef = dbo.OPCH.DocEntry  WHERE T1.Account<>T1.ShortName  

       and (isnull(OPCh.CANCELED,) <> ‘Y’) and (isnull(OPCH.CANCELED,) <> ‘C’)

       and (T1.Debit > 0 or T1.Credit > 0)  and T1.ShortName=@Code AND T0.RefDate<=’01-Nov-2013′

       GROUP BY T1.Account, T2.AcctName,T1.ShortName,T3.CardName,OPCH.CANCELED 

       UNION ALL /* Journal Rows */ SELECT  format(T0.RefDate, ‘dd-MMM-yyyy’),T0.TransType, T0.TaxDate, T0.Ref1,  T1.BaseRef,

       CASE  when T0.[TransType]= 18  then isnull((SELECT Comments FROM OPCH AS AP where AP.DocEntry = T0.[BaseRef] and AP.[ObjType]= T0.[TransType]),) 

       when T0.[TransType]= 46  then isnull((SELECT Comments FROM OVPM AS OP where OP.DocEntry = T0.[BaseRef] and OP.[ObjType]= T0.[TransType]),) when T0.[TransType]= 204 

       then isnull((SELECT Comments FROM ODPO AS DP where DP.DocEntry = T0.[BaseRef] and DP.[ObjType]= T0.[TransType]),)  when T0.[TransType]= 24 

       then isnull((SELECT Comments FROM ORCT AS PR where PR.DocEntry = T0.[BaseRef] and PR.[ObjType]= T0.[TransType]),) else T0.[Memo]  END AS LineMemo, 

       T0.TransID, T3.CardName, T1.[ContraAct] ‘OffsetAcct’, Account=(CASE WHEN T2.AcctName IS NULL THEN  t3.cardname else  T2.AcctName end ),  T1.ShortName,     T1.Debit,    

        T1.Credit, isnull(T1.Debit,0) isnull(T1.Credit,0) as Balance ,T0.TransType as TransType2,    isnull(case when (select TransId from OJDT A where TransId in (select StornoToTr from OJDT) and A.TransId = T0.TransId) is not Null

         then ‘Cancelled’  when StornoToTr is not null then ‘Reversed’ End,) as status,OPCH.CANCELED FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransID=T1.TransID  

         LEFT OUTER JOIN OACT T2 ON T1.Account=T2.AcctCode INNER JOIN OCRD T3 ON T1.Shortname = T3.CardCode LEFT OUTER JOIN  dbo.OPCH ON T1.BaseRef = dbo.OPCH.DocEntry

          WHERE T1.Account<>T1.ShortName   and (isnull(OPCh.CANCELED,) <> ‘Y’) and (isnull(OPCH.CANCELED,) <> ‘C’) and (T1.Debit > 0 or T1.Credit > 0)  and  T1.ShortName=@Code  and

           case when (select TransId from OJDT A where TransId in (select StornoToTr from OJDT) and A.TransId = T0.TransId) is not Null then ‘Cancelled’  when StornoToTr is not null then ‘Reversed’ End is null 



x——————————————————–x———————————————————–x

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply