I am new to Dynamics AX and I’ve been tasked with developing a SSRS report to bring in sales for the month in order to pay commissions. The version of AX is 4.0 and running on SQL Server 2008R2.
Based on user requirements, I’m trying to join records in CustSettlement with records in LedgerTrans in order to get the ledger account number used for the cash payment. The problem comes when a payment is made in a prior month that gets backed out and reapplied in current month. I can filter it out of the CustSettlement query but when joining to LedgerJournalTrans and LedgerTrans the record comes back and there does not seem to be a way to filter it back out without filtering out records we actually want.
Below is my query. Any help would be greatly appreciated.
;WITH B1 AS (
SELECT CS.TRANSRECID, CS.TRANSDATE, CS.OFFSETTRANSVOUCHER, CS.ACCOUNTNUM, CS.SETTLEAMOUNTCUR, CS.CREATEDDATE, CS.CREATEDTIME,
CS.OFFSETRECID, CS.RECID, CS.DATAAREAID, CS.CREATEDBY
FROM dbo.CUSTSETTLEMENT CS
WHERE CS.DATAAREAID IN (N’REP’, N’CRN’)
AND (CS.OFFSETTRANSVOUCHER LIKE N’RSV-%’ OR CS.OFFSETTRANSVOUCHER LIKE N’PINV-%’ OR CS.OFFSETTRANSVOUCHER LIKE N’POA-%’)
AND CS.CREATEDDATE BETWEEN @startDate AND @endDate
), B2 AS (
SELECT CSO.OFFSETTRANSVOUCHER, CSO.TRANSRECID, CSO.TRANSDATE,
CSO.ACCOUNTNUM, CSO.SETTLEAMOUNTCUR, CSO.CREATEDDATE,
CSO.CREATEDTIME, CSO.CREATEDBY, CSO.DATAAREAID, CSO.OFFSETRECID
FROM dbo.CUSTSETTLEMENT CSO
INNER JOIN B1 ON CSO.DATAAREAID = B1.DATAAREAID
AND CSO.TRANSRECID = B1.OFFSETRECID
AND CSO.OFFSETRECID = B1.TRANSRECID
WHERE (CSO.OFFSETTRANSVOUCHER LIKE N’RCR-%’ OR CSO.OFFSETTRANSVOUCHER LIKE N’ICO-%’)
AND CSO.TRANSDATE BETWEEN @startDate AND @endDate
)
SELECT B2.OFFSETTRANSVOUCHER AS SettledBy, B1.OFFSETTRANSVOUCHER, B2.TRANSRECID AS OFFSETRECID,
B1.TRANSRECID, B1.TRANSDATE, B1.ACCOUNTNUM, B1.SETTLEAMOUNTCUR,
B1.CREATEDDATE, B1.CREATEDTIME, B1.DATAAREAID, B1.CREATEDBY
INTO #TEMP_COMM
FROM B1
INNER JOIN B2 ON B1.DATAAREAID = B2.DATAAREAID
AND B1.OFFSETRECID = B2.TRANSRECID
AND B1.TRANSRECID = B2.OFFSETRECID
GROUP BY B2.OFFSETTRANSVOUCHER, B1.OFFSETTRANSVOUCHER, B2.TRANSRECID,
B1.TRANSRECID, B1.TRANSDATE, B1.ACCOUNTNUM, B1.SETTLEAMOUNTCUR,
B1.CREATEDDATE, B1.CREATEDTIME, B1.DATAAREAID, B1.CREATEDBY
;WITH SMT AS (
SELECT B.TRANSRECID,B.OFFSETTRANSVOUCHER,B.ACCOUNTNUM,B.SETTLEAMOUNTCUR,B.DATAAREAID,CT.PROJID,CT.INVOICE,B.TRANSDATE,B.CREATEDTIME,B.SettledBy
FROM #TEMP_COMM B
LEFT JOIN CUSTTRANS CT ON CT.DATAAREAID=B.DATAAREAID AND CT.VOUCHER=B.OFFSETTRANSVOUCHER
),INFO AS (
SELECT SMT.ACCOUNTNUM, SMT.OFFSETTRANSVOUCHER AS VOUCHER,SMT.PROJID,SMT.INVOICE,-SMT.SETTLEAMOUNTCUR AS TRANSAMOUNT,SMT.DATAAREAID,LT.ACCOUNTNUM AS CREDITACCOUNT,SMT.SettledBy,CT.NAME AS CUSTNAME
FROM SMT
JOIN LEDGERJOURNALTRANS LJT ON LJT.DATAAREAID=SMT.DATAAREAID AND LJT.VOUCHER=SMT.SettledBy AND LJT.ACCOUNTNUM=SMT.ACCOUNTNUM
LEFT JOIN LEDGERTRANS LT ON LT.JOURNALNUM=LJT.JOURNALNUM AND LT.DATAAREAID=LJT.DATAAREAID AND LT.VOUCHER=LJT.VOUCHER
AND LT.TXT=LJT.TXT AND LT.CREDITING=0 AND LT.POSTING IN (20,14)
LEFT JOIN CUSTTABLE CT ON CT.ACCOUNTNUM=SMT.ACCOUNTNUM AND CT.DATAAREAID=‘vc2’
), PRJ AS (
SELECT PT.CUSTACCOUNT,PT.PROJID, PT.DLVNAME + ‘’ + PT.NAME AS PROJNAME, PIJ.DATAAREAID, PIJ.PROJINVOICEID, PT.PROJINVOICEPROJID AS PROJECT, PT.RESPONSIBLE AS SALESGROUP
FROM PROJTABLE PT
JOIN PROJINVOICEJOUR PIJ ON PIJ.PROJID=PT.PROJID AND PIJ.DATAAREAID=PT.DATAAREAID
WHERE PT.PROJID IN (SELECT PROJID FROM INFO)
GROUP BY PT.CUSTACCOUNT,PT.PROJID, PT.DLVNAME, PT.NAME, PIJ.DATAAREAID, PIJ.PROJINVOICEID, PT.PROJINVOICEPROJID, PT.RESPONSIBLE
)
SELECT SG.GROUPNAME AS SALESPERSON,INFO.ACCOUNTNUM,INFO.CUSTNAME AS CUSTOMER, INFO.VOUCHER, PRJ.PROJECT, PRJ.PROJNAME, ‘’ AS SETTLEDATE, TRANSAMOUNT,INFO.DATAAREAID,INFO.CREDITACCOUNT,INFO.SettledBy AS OFFSETVOUCHER
FROM INFO
LEFT JOIN PRJ ON PRJ.PROJID=INFO.PROJID AND PRJ.PROJINVOICEID=INFO.INVOICE
LEFT JOIN vwCommissionSalesGroup SG ON SG.DATAAREAID=INFO.DATAAREAID AND SG.GROUPID=PRJ.SALESGROUP