on 05-03-2016 2:34 PM
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
To exclude cancelled payments add the following to your WHERE clause:
and ISNULL(T0.Canceled,'N') <> 'Y'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
90 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.