VIEW: APPS.HRI_CL_PER_N_V

Source

View Text - Preformatted

SELECT per.person_id ID ,(CASE fnd_profile.value('HRI_DBI_PERSON_NAME_FORMAT') WHEN 'F_LASTNAME' THEN substr(per.first_name,1,1) ||'. '|| per.last_name WHEN 'FIRSTNAME_LASTNAME1' THEN per.first_name ||' '|| per.last_name WHEN 'FIRSTNAME_LASTNAME2' THEN per.first_name || per.last_name WHEN 'LASTNAME_F' THEN per.last_name ||' '|| substr(per.first_name,1,1)||'.' WHEN 'LASTNAME_FIRSTNAME1' THEN per.last_name ||' '|| per.first_name WHEN 'LASTNAME_FIRSTNAME2' THEN per.last_name ||', '|| per.first_name WHEN 'LASTNAME_FIRSTNAME3' THEN per.last_name || per.first_name WHEN 'LASTNAME_KNOWN_AS' THEN per.last_name ||', '|| nvl(per.known_as,per.first_name) WHEN 'KNOWN_AS_LASTNAME' THEN nvl(per.known_as,per.first_name) ||'  '|| per.last_name WHEN 'HR_LIST_NAME_FORMAT' THEN (CASE fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT') WHEN 'G' THEN nvl(per.GLOBAL_NAME,substr(per.first_name,1,1) ||'. '|| per.last_name) WHEN 'L' THEN nvl(per.LOCAL_NAME,substr(per.first_name,1,1) ||'. '|| per.last_name) ELSE substr(per.first_name,1,1) ||'. '|| per.last_name END) ELSE substr(per.first_name,1,1) ||'. '|| per.last_name END) VALUE , per.effective_start_date START_DATE , per.effective_end_date END_DATE , per.effective_start_date EFFECTIVE_START_DATE , per.effective_end_date EFFECTIVE_END_DATE , (CASE fnd_profile.value('HRI_DBI_PERSON_NAME_FORMAT') WHEN 'HR_LIST_NAME_FORMAT' THEN (CASE fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT') WHEN 'G' THEN nvl(per.GLOBAL_NAME, nvl(per_name.order_name, per_name.last_name || ' ' || per.first_name)) WHEN 'L' THEN nvl(per.LOCAL_NAME, nvl(per_name.order_name, per_name.last_name || ' ' || per.first_name)) ELSE nvl(per_name.order_name, per_name.last_name || ' ' || per.first_name) END) ELSE nvl(per_name.order_name, per_name.last_name || ' ' || per.first_name) END) ORDER_BY , per.business_group_id BUSINESS_GROUP_ID , NVL(per.current_applicant_flag,'N') APL_FLAG_CODE , NVL(per.current_employee_flag,'N') EMP_FLAG_CODE , NVL(per.current_npw_flag,'N') CWK_FLAG_CODE , NVL(per.current_emp_or_apl_flag,'N') EMP_OR_APL_FLAG_CODE , DECODE(per.current_employee_flag ,'N',NVL(per.current_npw_flag,'N') ,NVL(per.current_employee_flag,'N')) WORKER_FLAG_CODE , per.full_name FULL_NAME , per.last_name LAST_NAME , per.first_name FIRST_NAME , per.middle_names MIDDLE_NAMES , per.title TITLE , per.pre_name_adjunct PRE_NAME_ADJUNCT , per.suffix SUFFIX , per.honors HONORS , per.known_as KNOWN_AS , per.applicant_number APPLICANT_NUMBER , per.employee_number EMPLOYEE_NUMBER , per.npw_number NPW_NUMBER , per.national_identifier NATIONAL_IDENTIFIER , CASE WHEN ( HR_SECURITY.VIEW_ALL = 'Y' OR HR_SECURITY.SHOW_PERSON(per.person_id ,per.current_applicant_flag ,per.current_employee_flag ,per.current_npw_flag ,per.employee_number ,per.applicant_number ,per.npw_number) = 'TRUE' ) AND ( hr_general.get_xbg_profile = 'Y' OR hr_bis.get_sec_profile_bg_id = per.business_group_id ) THEN 'Y' ELSE 'N' END hr_security_flag_code , CASE WHEN ( per.business_group_id IS NULL OR per.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,per.business_group_id) ) THEN 'Y' ELSE 'N' END bg_security_flag_code FROM per_all_people_f per , per_all_people_f per_name WHERE TRUNC(SYSDATE) BETWEEN per_name.effective_start_date AND per_name.effective_end_date AND per.person_id = per_name.person_id WITH READ ONLY

View Text - HTML Formatted

SELECT PER.PERSON_ID ID
, (CASE FND_PROFILE.VALUE('HRI_DBI_PERSON_NAME_FORMAT') WHEN 'F_LASTNAME' THEN SUBSTR(PER.FIRST_NAME
, 1
, 1) ||'. '|| PER.LAST_NAME WHEN 'FIRSTNAME_LASTNAME1' THEN PER.FIRST_NAME ||' '|| PER.LAST_NAME WHEN 'FIRSTNAME_LASTNAME2' THEN PER.FIRST_NAME || PER.LAST_NAME WHEN 'LASTNAME_F' THEN PER.LAST_NAME ||' '|| SUBSTR(PER.FIRST_NAME
, 1
, 1)||'.' WHEN 'LASTNAME_FIRSTNAME1' THEN PER.LAST_NAME ||' '|| PER.FIRST_NAME WHEN 'LASTNAME_FIRSTNAME2' THEN PER.LAST_NAME ||'
, '|| PER.FIRST_NAME WHEN 'LASTNAME_FIRSTNAME3' THEN PER.LAST_NAME || PER.FIRST_NAME WHEN 'LASTNAME_KNOWN_AS' THEN PER.LAST_NAME ||'
, '|| NVL(PER.KNOWN_AS
, PER.FIRST_NAME) WHEN 'KNOWN_AS_LASTNAME' THEN NVL(PER.KNOWN_AS
, PER.FIRST_NAME) ||' '|| PER.LAST_NAME WHEN 'HR_LIST_NAME_FORMAT' THEN (CASE FND_PROFILE.VALUE('HR_LOCAL_OR_GLOBAL_NAME_FORMAT') WHEN 'G' THEN NVL(PER.GLOBAL_NAME
, SUBSTR(PER.FIRST_NAME
, 1
, 1) ||'. '|| PER.LAST_NAME) WHEN 'L' THEN NVL(PER.LOCAL_NAME
, SUBSTR(PER.FIRST_NAME
, 1
, 1) ||'. '|| PER.LAST_NAME) ELSE SUBSTR(PER.FIRST_NAME
, 1
, 1) ||'. '|| PER.LAST_NAME END) ELSE SUBSTR(PER.FIRST_NAME
, 1
, 1) ||'. '|| PER.LAST_NAME END) VALUE
, PER.EFFECTIVE_START_DATE START_DATE
, PER.EFFECTIVE_END_DATE END_DATE
, PER.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, PER.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, (CASE FND_PROFILE.VALUE('HRI_DBI_PERSON_NAME_FORMAT') WHEN 'HR_LIST_NAME_FORMAT' THEN (CASE FND_PROFILE.VALUE('HR_LOCAL_OR_GLOBAL_NAME_FORMAT') WHEN 'G' THEN NVL(PER.GLOBAL_NAME
, NVL(PER_NAME.ORDER_NAME
, PER_NAME.LAST_NAME || ' ' || PER.FIRST_NAME)) WHEN 'L' THEN NVL(PER.LOCAL_NAME
, NVL(PER_NAME.ORDER_NAME
, PER_NAME.LAST_NAME || ' ' || PER.FIRST_NAME)) ELSE NVL(PER_NAME.ORDER_NAME
, PER_NAME.LAST_NAME || ' ' || PER.FIRST_NAME) END) ELSE NVL(PER_NAME.ORDER_NAME
, PER_NAME.LAST_NAME || ' ' || PER.FIRST_NAME) END) ORDER_BY
, PER.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, NVL(PER.CURRENT_APPLICANT_FLAG
, 'N') APL_FLAG_CODE
, NVL(PER.CURRENT_EMPLOYEE_FLAG
, 'N') EMP_FLAG_CODE
, NVL(PER.CURRENT_NPW_FLAG
, 'N') CWK_FLAG_CODE
, NVL(PER.CURRENT_EMP_OR_APL_FLAG
, 'N') EMP_OR_APL_FLAG_CODE
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'N'
, NVL(PER.CURRENT_NPW_FLAG
, 'N')
, NVL(PER.CURRENT_EMPLOYEE_FLAG
, 'N')) WORKER_FLAG_CODE
, PER.FULL_NAME FULL_NAME
, PER.LAST_NAME LAST_NAME
, PER.FIRST_NAME FIRST_NAME
, PER.MIDDLE_NAMES MIDDLE_NAMES
, PER.TITLE TITLE
, PER.PRE_NAME_ADJUNCT PRE_NAME_ADJUNCT
, PER.SUFFIX SUFFIX
, PER.HONORS HONORS
, PER.KNOWN_AS KNOWN_AS
, PER.APPLICANT_NUMBER APPLICANT_NUMBER
, PER.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PER.NPW_NUMBER NPW_NUMBER
, PER.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, CASE WHEN ( HR_SECURITY.VIEW_ALL = 'Y' OR HR_SECURITY.SHOW_PERSON(PER.PERSON_ID
, PER.CURRENT_APPLICANT_FLAG
, PER.CURRENT_EMPLOYEE_FLAG
, PER.CURRENT_NPW_FLAG
, PER.EMPLOYEE_NUMBER
, PER.APPLICANT_NUMBER
, PER.NPW_NUMBER) = 'TRUE' )
AND ( HR_GENERAL.GET_XBG_PROFILE = 'Y' OR HR_BIS.GET_SEC_PROFILE_BG_ID = PER.BUSINESS_GROUP_ID ) THEN 'Y' ELSE 'N' END HR_SECURITY_FLAG_CODE
, CASE WHEN ( PER.BUSINESS_GROUP_ID IS NULL OR PER.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PER.BUSINESS_GROUP_ID) ) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
FROM PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F PER_NAME
WHERE TRUNC(SYSDATE) BETWEEN PER_NAME.EFFECTIVE_START_DATE
AND PER_NAME.EFFECTIVE_END_DATE
AND PER.PERSON_ID = PER_NAME.PERSON_ID WITH READ ONLY