Skip to Content

Report to pull A/P, A/P JE and Outgoing Payment Information

Hello! I have written a query that pulls together our customers A/P JE (To pull in the offset account info), and Outgoing Payment information.The data looks good, however for the last column called 'Total Payment' it is pulling in duplicate lines. It looks like for the A/P JEs that have multiple lines, the total apears 3 times too

Example:

What I see

Outgoing Payment # Cash Amount Credit Card Amount Check Amount Bank Transfer Amount Payment Total

4610 0.000 0.000 0.000 10,000.000 10,000.000

4610 0.000 0.000 0.000 10,000.000 10,000.000

What I want to see

Outgoing Payment #Cash AmountCredit Card AmountCheck AmountBank Transfer AmountPayment Total

4610 0.000 0.000 0.000 10,000.000 10,000.000

4610 0.000 0.000 0.000 0.000 0.000

Query I am using:

Select DISTINCT(T4.Number) as 'JE #' ,T2.DocNum as 'A/P Invoice #' ,t1.RefDate as [JE Posting Date] , T2.DocDate as 'A/P Posting Date' ,t1.ShortName as [GL Account / Vendor Code] , T6.CardName as 'Vendor Name' , T5.FormatCode as 'G/L Account Code' , T5.AcctName as 'G/L Account Name' ,t1.Credit ,t1.Debit ---, T7.FormatCode as 'Offset Account from A/P' ---, T7.AcctName as 'Offset Account Name' ,T9.DocNum as 'Outgoing Payment #' ,t9.CashSum 'Cash Amount' ,t9.CreditSum 'Credit Card Amount' ,T9.CheckSum 'Check Amount' ,t9.TrsfrSum 'Bank Transfer Amount' ,t9.DocTotal 'Payment Total' , Convert(date,getdate(),112) 'As Of Date' from JDT1 t1 ---Inner Join OVPM t2 on t1.transid = T2.transid inner join OJDT T4 on T4.Transid = T1.Transid inner join OPCH T2 on T2.TransId = T4.TransId inner join PCH1 T3 on T3.Docentry = T2.DocEntry left Join OACT T5 on T1.Account = T5.AcctCode Left join OCRD T6 on T1.shortname = T6.CardCode Left join OACT T7 on T3.AcctCode = T7.AcctCode left Join OVPM T9 on T2.ReceiptNum = T9.DocEntry

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers