Empty screens at 'Time and Materials Journal' > ‘Mileage’ and ‘Materials’ despite existing data

[z_mamlouk]

Symptom
In 'Time & Material Journal', 'Mileages' & 'Materials' display an empty list despite proper configurations and creation of 'Mileages' from both the mobile app and directly in 'Time & Material Journal'. Although approvals for the 'Mileages' have been created by running the business rule, they are not appearing in the 'Time & Material Journal', indicating a potential issue with the UI display or integration.

Cause
In the 'Time & Material Journal' system, a query fails to return results because of an issue with the UnifiedPerson JOIN, where the created person ID differs from the unified person reference ID. This discrepancy leads to inconsistencies, preventing the display of results on the UI. 

Time & Material Journal Query Explanation:

SELECT mileage.travelStartDateTimeTimeZoneId, mileage.travelEndDateTimeTimeZoneId, serviceCall.code, serviceCall.id, serviceCall.subject, serviceCall.statusCode, serviceCall.statusName, mileage.udfValues
FROM Mileage mileage
LEFT JOIN Activity activity ON mileage.object = activity
LEFT JOIN ServiceCall serviceCall ON mileage.object = serviceCall OR activity.object.objectId = serviceCall.id
LEFT JOIN BusinessPartner businessPartner1 ON (activity.businessPartner = businessPartner1)
LEFT JOIN BusinessPartner businessPartner2 ON (serviceCall.businessPartner = businessPartner2)
JOIN UnifiedPerson person ON mileage.createPerson = person.refId
LEFT JOIN MileageType type ON mileage.type = type
LEFT JOIN Approval approval ON approval.object = mileage
WHERE (serviceCall.id IS NOT NULL)
AND mileage.object.objectType IN ('ACTIVITY', 'SERVICECALL')
ORDER BY mileage.id

This comprehensive query aims to retrieve various details from the 'Mileage', 'Activity', 'ServiceCall', 'BusinessPartner', and related tables, aiming to compile data for 'Time & Material Journal' UI screen. However, it doesn't return results for the UI because of an issue identified in the 'UnifiedPerson JOIN', where the mileage's 'createPerson' is supposed to match 'person.refId', but no matches are found.

Matching Query Analysis:

SELECT m.createPerson, person.refId, person.id
FROM Mileage m
JOIN UnifiedPerson person ON m.createPerson = person.id

This query is used to diagnose the issue by checking the relationships between IDs. It highlights that the 'createPerson' ID must exactly match the 'unifiedPerson' ID rather than the 'refId', to maintain data integrity and ensure accurate data reflection on the UI.

Resolution
To resolve this, ensure the unifiedPerson object has matching IDs (id == refId) to achieve data consistency and the desired UI outcomes.