Report Queries for SAP Business One (Part 1: Payment Related)

[saad_omair]

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