Dynamics Community Forum Thread Details

Customer Aging Report not working with XDS Policy

I have a policy linked to a query that has CustTable as a primary table and joined with another table that stores a few customers (AccountNum).. So when the policy is applied the user can only see only few customers.

It works fine but I have a problem in Customer Aging Report... the report runs smoothly but when I go to "records to include" tab and choose Employee Responsible field the report crashes with this error:

and here's the query that fails:

"INSERT INTO tempdb."DBO".t13749IISdvhd7481vm_718228_97F2BCF42E8E47F7BCD2A58802DCF4DD (ACCOUNTNUM,GROUPID,ACCOUNTNUM,GROUPID) 
SELECT T1.ACCOUNTNUM AS f1,T1.CUSTGROUP AS f2,1 AS RECVERSION,5637144576 AS PARTITION FROM CUSTTABLE T1 
LEFT OUTER JOIN HCMWORKER T2 ON ((T2.PARTITION=5637144576) AND (T1.MAINCONTACTWORKER=T2.RECID)) 
LEFT OUTER JOIN DIRPARTYTABLE T3 ON (((T3.PARTITION=5637144576) AND (T2.PERSON=T3.RECID)) 
AND (T3.INSTANCERELATIONTYPE IN (3738) )) WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'atc')) AND (T3.NAME=N'John Daper')) 
AND EXISTS (SELECT 'CustTablePolicy' FROM CUSTTABLE T4 CROSS JOIN XDSCUSTTABLEREGULAR T5 
WHERE (((T4.PARTITION=5637144576) AND (T4.DATAAREAID=N'atc')) AND (T1.RECID=T4.RECID)) 
AND (((T5.PARTITION=5637144576) AND (T5.DATAAREAID=N'atc')) 
AND (((T5.USERGROUPID=N'FIN') OR (T5.USERGROUPID=N'test2')) AND (T4.ACCOUNTNUM=T5.CUSTACCOUNT)))) 
ORDER BY T1.MAINCONTACTWORKER"

when I try to remove the Employee Responsible filter from "records to include" tab and run the report it runs successfully..
and here's the succeeded query:

"INSERT INTO tempdb."DBO".t13749IISdvhd7481vm_718228_E46B6BA859E84F658642F5E53359DDEA (ACCOUNTNUM,GROUPID,RECVERSION,PARTITION) 
SELECT T1.ACCOUNTNUM AS f1,T1.CUSTGROUP AS f2,1 AS RECVERSION,5637144576 AS PARTITION 
FROM CUSTTABLE T1 WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'atc')) AND (T1.ACCOUNTNUM=N'101100000001')) 
AND EXISTS (SELECT 'CustTablePolicy' FROM CUSTTABLE T2 CROSS JOIN XDSCUSTTABLEREGULAR T3 
WHERE (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'atc')) AND (T1.RECID=T2.RECID)) 
AND (((T3.PARTITION=5637144576) AND (T3.DATAAREAID=N'atc')) AND (((T3.USERGROUPID=N'FIN') 
OR (T3.USERGROUPID=N'test2')) AND (T2.ACCOUNTNUM=T3.CUSTACCOUNT)))) 
ORDER BY T1.ACCOUNTNUM"

why in the query that fails "ACCOUNTNUM","GROUPID" is duplicated?!

Categories:

Dynamics 365 Finance

Quick Links