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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
2 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |