cancel
Showing results for 
Search instead for 
Did you mean: 

Outgoing Payments query

Former Member
0 Kudos

Hi I am trying to see if someone can help me with this query. My problem with it is that I need to account for cancelled outgoing payments. I need my results to NOT sure cancelled outgoing payments. I also have to take into account credits that were taken. so for example, if there was a credit memo attached to the invoice, my query does not take the copy to credit memo into account. Also, is there a way to only get the transfer amount once? i want the sum of the outgoing payment transfer amount to be listed once. ex.

invoice     document total        transfer amount

inv 1       $100

inv 2       $100

inv 3       $100                              $300

SELECT T0.[TrsfrAcct], T0.[Canceled], T0.[DocDate], T0.[TrsfrDate], T0.[DocType], T0.[CardCode], T0.[CardName], T1.[NumAtCard], T1.[DiscPrcnt], T1.[DocTotal], T0.[Dcount],  T0.[TrsfrSum], T0.[TrsfrRef], T0.[JrnlMemo] FROM OVPM T0  INNER JOIN OPCH T1 ON T0.DocEntry = T1.ReceiptNum WHERE T0.[DocDate] >=[%0] and  T0.[DocDate] <=[%1]

Any help is greatly appreciated!!

-Jessica

Accepted Solutions (0)

Answers (1)

Answers (1)

julie_jamieson2
Active Contributor
0 Kudos

To exclude cancelled payments add the following to your WHERE clause:

and ISNULL(T0.Canceled,'N') <> 'Y'

Former Member
0 Kudos

Thanks so much.

julie_jamieson2
Active Contributor
0 Kudos

Please close if you have your answer?