SalesTableListPage shows no orders
5. November 2014 Leave a comment
I had to resolve an issue where exactly one user did not see any orders in the sales table list page. The user had system administrator role. Default Admin Voodoo (clearing usage data, delete + re-import user) did not solve the problem.
However, opening the SalesTable form shows all orders.
In first step I used Dynamics AX Trace Parser to get the SQL statement for the sales table list page.
In detail the SQL Select statement was:
SELECT T1.SALESID,
T1.SALESNAME,
T1.RESERVATION,
— much more fields here
T2.PERSON,
T2.RECVERSION,
T2.RECID,
T3.NAME,
T3.RECID,
T3.RECVERSION,
T3.INSTANCERELATIONTYPE,
T3.RECVERSION,
T3.RECID
FROM SALESTABLE T1 LEFT
OUTER
JOIN HCMWORKER T2 ON ((T2.PARTITION=5637144576)
AND (T1.WORKERSALESTAKER=T2.RECID)) LEFT
OUTER
JOIN DIRPARTYTABLE T3 ON (((T3.PARTITION=5637144576)
AND (T2.PERSON=T3.RECID))
AND (T3.INSTANCERELATIONTYPE IN (2975) ))
WHERE (((T1.PARTITION=5637144576)
AND (T1.DATAAREAID=’iax’))
AND ((
NOT ((T1.RETURNSTATUS=4))
AND
NOT ((T1.RETURNSTATUS=1)))
AND (T1.CUSTACCOUNT=”)))
ORDER BY T1.SALESID DESC OPTION(FAST 5)
There was this one strange WHERE constraint CUSTACCOUNT=’’ . I compared this statement with the SQL statement for another user who saw all orders in the sales table list page. There was no such constraint regarding the CustAccount.
In a second step I had a detailed look on the SalesTableListPageInteraction class. The initializeQuery() method modifies the query. When the query is passed there was no range on the CustAccount. However, the isCustomer flag was set and the query was converted to a Self-Service Query.
public void initializeQuery(Query _query)
{
QueryBuildDataSource qbds;
ProjInvoiceTable projInvoiceTable;if (isCustomer)
{
EPQuery::makeMyCustomerSelfServiceQuery(_query,
tableNum(SalesTable));
}// more code here
super(_query);
}
The isCustomer flag gets set in the SaleTableListPageInteraction.initializing() method and calls the EP::isCustomer() method. There, the code checks if the actual user is also a contact person for a customer in the system.
select firstonly crossCompany RecId from dirPersonUser
where dirPersonUser.User == curUserId()
exists join dirPartyRelationship
where dirPartyRelationship.ChildParty ==
dirPersonUser.PersonParty
exists join custTable
where custTable.Party ==
dirPartyRelationship.ParentParty;
In my case the problem was solved by removing the user as contact for the customer