Summary

Dashboard Prompt for BI Publisher Reports

Content

Hi all,

I have a question regarding dashboard prompt for BI Publishers.

It seems to work fine for Person Number but not Business Unit, Hire Date.

Here is the configuration:

And here are some details of the report:

Prompt :

code:

SELECT PN.FULL_NAME

, PP.PERSON_NUMBER

, TO_CHAR(PS.DATE_START, 'MM/DD/YYYY') AS HIRE_DATE

, MN.FULL_NAME AS MANAGER_NAME

, PJ.NAME AS JOB_NAME

, B.BU_NAME AS BUSINESS_UNIT

, OU.NAME AS ORGANIZATION_NAME

, LO.LOCATION_NAME

FROM PER_ALL_ASSIGNMENTS_F AG

INNER JOIN PER_PERIODS_OF_SERVICE PS ON AG.PERIOD_OF_SERVICE_ID = PS.PERIOD_OF_SERVICE_ID

LEFT JOIN PER_PERSON_NAMES_F PN ON PS.PERSON_ID = PN.PERSON_ID

AND TRUNC(SYSDATE) BETWEEN PN.EFFECTIVE_START_DATE AND PN.EFFECTIVE_END_DATE

AND PN.NAME_TYPE = 'GLOBAL'

LEFT JOIN PER_PEOPLE_F PP ON AG.PERSON_ID = PP.PERSON_ID

AND TRUNC(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE

LEFT JOIN PER_ASSIGNMENT_SUPERVISORS_F MR ON AG.ASSIGNMENT_ID = MR.ASSIGNMENT_ID

AND TRUNC(SYSDATE) BETWEEN MR.EFFECTIVE_START_DATE AND MR.EFFECTIVE_END_DATE

AND MR.PRIMARY_FLAG = 'Y'

LEFT JOIN PER_PERSON_NAMES_F MN ON MR.MANAGER_ID = MN.PERSON_ID

AND SYSDATE BETWEEN MN.EFFECTIVE_START_DATE AND MN.EFFECTIVE_END_DATE

AND MN.NAME_TYPE = 'GLOBAL'

LEFT JOIN PER_LOCATION_DETAILS_F_VL LO ON LO.LOCATION_ID = AG.LOCATION_ID

AND TRUNC(SYSDATE) BETWEEN LO.EFFECTIVE_START_DATE AND LO.EFFECTIVE_END_DATE

LEFT JOIN HR_ORGANIZATION_UNITS_F_TL OU ON OU.ORGANIZATION_ID = AG.ORGANIZATION_ID

AND TRUNC(SYSDATE) BETWEEN OU.EFFECTIVE_START_DATE AND OU.EFFECTIVE_END_DATE

AND OU.LANGUAGE = USERENV('LANG')

LEFT JOIN PER_JOBS PJ ON AG.JOB_ID = PJ.JOB_ID

AND TRUNC(SYSDATE) BETWEEN PJ.EFFECTIVE_START_DATE AND PJ.EFFECTIVE_END_DATE

LEFT JOIN FUN_ALL_BUSINESS_UNITS_V B ON B.BU_ID = AG.BUSINESS_UNIT_ID

AND TRUNC(SYSDATE) BETWEEN B.DATE_FROM AND B.DATE_TO

WHERE TRUNC(SYSDATE) BETWEEN AG.EFFECTIVE_START_DATE AND AG.EFFECTIVE_END_DATE

AND AG.ASSIGNMENT_TYPE = 'E'

AND AG.PRIMARY_FLAG = 'Y'

AND AG.EFFECTIVE_LATEST_CHANGE = 'Y'

AND (PS.ACTUAL_TERMINATION_DATE IS NULL OR PS.ACTUAL_TERMINATION_DATE > TRUNC(SYSDATE))

AND (LEAST(:P_BUSINESS_UNIT) IS NULL OR B.BU_NAME IN (:P_BUSINESS_UNIT))

ORDER BY PN.FULL_NAME