Hi,
We have a requirement to add an employee parameter in the report where the format is "Full Name + (Person Number)" E.g. Smith, John (12345)
The LOV that we created looks like this but some values are not displaying in the LOV of the report itself.
SELECT
(PPNFV.FULL_NAME || ' (' || PPSLV.PERSON_NUMBER || ')'),
PASLV.ASSIGNMENT_NUMBER /*used for binding in main query*/
FROM
PER_PERSON_SECURED_LIST_V PPSLV
JOIN PER_PERSON_NAMES_F_V PPNFV ON PPSLV.PERSON_ID = PPNFV.PERSON_ID AND :pEffectiveDate BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE AND PPNFV.NAME_TYPE = 'GLOBAL'
JOIN PER_ASSIGNMENT_SECURED_LIST_V PASLV ON PPSLV.PERSON_ID = PASLV.PERSON_ID AND :pEffectiveDate BETWEEN PASLV.EFFECTIVE_START_DATE AND PASLV.EFFECTIVE_END_DATE AND PASLV.ASSIGNMENT_TYPE NOT LIKE '%T%' AND PASLV.ASSIGNMENT_STATUS_TYPE <> 'INACTIVE'
WHERE
:pEffectiveDate BETWEEN PPSLV.EFFECTIVE_START_DATE AND PPSLV.EFFECTIVE_END_DATE
ORDER BY
PPNFV.FULL_NAME
We can confirm that this is working when we test via creating this as a separate data set. However, when created as LOV, there are missing values.
Appreciate any advice we can get!
