Skip to Content
avatar image
Former Member

What is the best way to add Credit Memos to this Query

This is a query we use to obtain a commission on net invoices paid. I would like the query to show credit memos as a line item of the incoming payments. Or if there is a better way to achieve the results I am open.

SELECT T1.[DocDate] as 'Invoice Date',T0.[DocDate] as 'Payment Date', T0.[CreateDate], T3.[GroupCode], T3.[Territory],T0.[CardCode] as 'Customer Code',
T0.[CardName] as 'Customer Name', T1.[Address2], T0.[DocNum] as 'Incoming Payment #', T0.[Jrnlmemo],
T0.[CashSum] as 'Cash Applied', T0.[CheckSum] as 'Check Applied',
T0.[TrsfrSum], T0.[DocTotal],T1.[DocNum] as 'Invoice #', T1.[DocTotal] as 'Invoice Amount', T4.[Dcount], T4.[DcntSum], T4.[SumApplied],
T2.[SlpName] as 'Sales Person', T1.[Trnspcode]
FROM ORCT T0
INNER jOIN RCT2 T4 ON T4.DocNum = T0.DocNum
INNER JOIN OINV T1 ON T1.DocEntry = T4.DocEntry and T4.InvType = 13
INNER JOIN OSLP T2 ON T1.SlpCode = T2.SlpCode
INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode WHERE (T0.DocDate >= '[%0]' and T0.DocDate <= '[%1]')
ORDER BY T0.DocDate

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Feb 06, 2017 at 10:34 AM

    Hi

    I would add:

    LEFT OUTER JOIN ORIN T5 ON T5.DocEntry = T4.docentry and T4..InvType = '14'

    I would change the INNER JOIN with OSLP table for LEFT OUTER JOIN

    Kind regards

    Agustín

    Add comment
    10|10000 characters needed characters exceeded