VIEW: APPS.HRI_OBI_CL_PER_PERSONSC_V

Source

View Text - Preformatted

SELECT per.person_id || '(' || per.effective_start_date || ',' || per.effective_end_date || ')' PERS_PERSONSC_PK, ( 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) PERS_PERSONSC_NAME, ( CASE fnd_profile.VALUE('HRI_DBI_PERSON_NAME_FORMAT') WHEN 'F_LASTNAME' THEN SUBSTR(per.first_name, 1, 1) || '. ' || per.last_name || '(' || per.person_id || ')' WHEN 'FIRSTNAME_LASTNAME1' THEN per.first_name || ' ' || per.last_name || '(' || per.person_id || ')' WHEN 'FIRSTNAME_LASTNAME2' THEN per.first_name || per.last_name || '(' || per.person_id || ')' WHEN 'LASTNAME_F' THEN per.last_name || ' ' || SUBSTR(per.first_name, 1, 1) || '.' || '(' || per.person_id || ')' WHEN 'LASTNAME_FIRSTNAME1' THEN per.last_name || ' ' || per.first_name || '(' || per.person_id || ')' WHEN 'LASTNAME_FIRSTNAME2' THEN per.last_name || ', ' || per.first_name || '(' || per.person_id || ')' WHEN 'LASTNAME_FIRSTNAME3' THEN per.last_name || per.first_name || '(' || per.person_id || ')' WHEN 'LASTNAME_KNOWN_AS' THEN per.last_name || ', ' || nvl(per.known_as, per.first_name) || '(' || per.person_id || ')' WHEN 'KNOWN_AS_LASTNAME' THEN nvl(per.known_as, per.first_name) || '  ' || per.last_name || '(' || per.person_id || ')' 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) || '(' || per.person_id || ')' WHEN 'L' THEN nvl(per.local_name, SUBSTR(per.first_name, 1, 1) || '. ' || per.last_name) || '(' || per.person_id || ')' ELSE SUBSTR(per.first_name, 1, 1) || '. ' || per.last_name || '(' || per.person_id || ')' END) ELSE SUBSTR(per.first_name, 1, 1) || '. ' || per.last_name || '(' || per.person_id || ')' END) PERS_PERSONSC_NAME_UNQ, ( 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) PERS_PERSON_NAME_CRNT, ( CASE fnd_profile.VALUE('HRI_DBI_PERSON_NAME_FORMAT') WHEN 'F_LASTNAME' THEN SUBSTR(per.first_name, 1, 1) || '. ' || per.last_name || '(' || per.person_id || ')' WHEN 'FIRSTNAME_LASTNAME1' THEN per.first_name || ' ' || per.last_name || '(' || per.person_id || ')' WHEN 'FIRSTNAME_LASTNAME2' THEN per.first_name || per.last_name || '(' || per.person_id || ')' WHEN 'LASTNAME_F' THEN per.last_name || ' ' || SUBSTR(per.first_name, 1, 1) || '.' || '(' || per.person_id || ')' WHEN 'LASTNAME_FIRSTNAME1' THEN per.last_name || ' ' || per.first_name || '(' || per.person_id || ')' WHEN 'LASTNAME_FIRSTNAME2' THEN per.last_name || ', ' || per.first_name || '(' || per.person_id || ')' WHEN 'LASTNAME_FIRSTNAME3' THEN per.last_name || per.first_name || '(' || per.person_id || ')' WHEN 'LASTNAME_KNOWN_AS' THEN per.last_name || ', ' || nvl(per.known_as, per.first_name) || '(' || per.person_id || ')' WHEN 'KNOWN_AS_LASTNAME' THEN nvl(per.known_as, per.first_name) || '  ' || per.last_name || '(' || per.person_id || ')' 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) || '(' || per.person_id || ')' WHEN 'L' THEN nvl(per.local_name, SUBSTR(per.first_name, 1, 1) || '. ' || per.last_name) || '(' || per.person_id || ')' ELSE SUBSTR(per.first_name, 1, 1) || '. ' || per.last_name || '(' || per.person_id || ')' END) ELSE SUBSTR(per.first_name, 1, 1) || '. ' || per.last_name || '(' || per.person_id || ')' END) PERS_PERSONSC_NAME_CRNT_UNQ, per.effective_start_date PERS_DATE_START, per.effective_end_date PERS_DATE_END, ( 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) PERS_ORDER_BY, per.person_id PERS_PERSON_FK, per.last_name PERS_LAST_NAME, per.applicant_number PERS_APPLICANT_NUMBER, per.employee_number PERS_EMPLOYEE_NUMBER, per.npw_number PERS_NPW_NUMBER, nvl(per.current_applicant_flag, 'N') PERS_APL_FLAG_CODE, nvl(per.current_employee_flag, 'N') PERS_EMP_FLAG_CODE, nvl(per.current_npw_flag, 'N') PERS_CWK_FLAG_CODE, nvl(per.current_emp_or_apl_flag, 'N') PERS_EMP_OR_APL_FLAG_CODE, decode(per.current_employee_flag, 'N', nvl(per.current_npw_flag, 'N'), nvl(per.current_employee_flag, 'N')) PERS_WORKER_FLAG_CODE, per.marital_status PERS_MARITAL_STATUS_CODE, per.sex PERS_GENDER_CODE, hr_bis.bis_decode_lookup('YES_NO',nvl(per.current_applicant_flag, 'N')) PERS_APL_FLAG, hr_bis.bis_decode_lookup('YES_NO',nvl(per.current_employee_flag, 'N')) PERS_EMP_FLAG, hr_bis.bis_decode_lookup('YES_NO',nvl(per.current_npw_flag, 'N')) PERS_CWK_FLAG, hr_bis.bis_decode_lookup('YES_NO',nvl(per.current_emp_or_apl_flag, 'N')) PERS_EMP_OR_APL_FLAG, hr_bis.bis_decode_lookup('YES_NO',decode(per.current_employee_flag, 'N', nvl(per.current_npw_flag, 'N'), nvl(per.current_employee_flag, 'N'))) PERS_WORKER_FLAG, hr_bis.bis_decode_lookup('MAR_STATUS',per.marital_status) PERS_MARITAL_STATUS, hr_bis.bis_decode_lookup('SEX',per.sex) PERS_GENDER, per.middle_names PERS_MIDDLE_NAMES 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 UNION ALL Select to_char(id) || '(' || hr_general.start_of_time || ',' || hr_general.end_of_time || ')'PERS_PERSONSC_PK ,hri_oltp_view_message.get_unassigned_msg PERS_PERSONSC_NAME ,hri_oltp_view_message.get_unassigned_msg || '(' || to_char(id) || ')' PERS_PERSONSC_NAME_UNQ ,hri_oltp_view_message.get_unassigned_msg PERS_PERSON_NAME_CRNT ,hri_oltp_view_message.get_unassigned_msg || to_char(id) PERS_PERSONSC_NAME_CRNT_UNQ ,hr_general.start_of_time PERS_DATE_START ,hr_general.end_of_time PERS_DATE_END ,VALUE PERS_ORDER_BY ,id PERS_PERSON_FK ,hri_oltp_view_message.get_unassigned_msg PERS_LAST_NAME ,id_char PERS_APPLICANT_NUMBER ,id_char PERS_EMPLOYEE_NUMBER ,id_char PERS_NPW_NUMBER ,id_char PERS_APL_FLAG_CODE ,id_char PERS_EMP_FLAG_CODE ,id_char PERS_CWK_FLAG_CODE ,id_char PERS_EMP_OR_APL_FLAG_CODE ,id_char PERS_WORKER_FLAG_CODE ,id_char PERS_MARITAL_STATUS_CODE ,id_char PERS_GENDER_CODE ,hri_oltp_view_message.get_unassigned_msg PERS_APL_FLAG ,hri_oltp_view_message.get_unassigned_msg PERS_EMP_FLAG ,hri_oltp_view_message.get_unassigned_msg PERS_CWK_FLAG ,hri_oltp_view_message.get_unassigned_msg PERS_EMP_OR_APL_FLAG ,hri_oltp_view_message.get_unassigned_msg PERS_WORKER_FLAG ,hri_oltp_view_message.get_unassigned_msg PERS_MARITAL_STATUS ,hri_oltp_view_message.get_unassigned_msg PERS_GENDER ,hri_oltp_view_message.get_unassigned_msg PERS_MIDDLE_NAMES from hri_unassigned WITH READ ONLY

View Text - HTML Formatted

SELECT PER.PERSON_ID || '(' || PER.EFFECTIVE_START_DATE || '
, ' || PER.EFFECTIVE_END_DATE || ')' PERS_PERSONSC_PK
, ( 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) PERS_PERSONSC_NAME
, ( CASE FND_PROFILE.VALUE('HRI_DBI_PERSON_NAME_FORMAT') WHEN 'F_LASTNAME' THEN SUBSTR(PER.FIRST_NAME
, 1
, 1) || '. ' || PER.LAST_NAME || '(' || PER.PERSON_ID || ')' WHEN 'FIRSTNAME_LASTNAME1' THEN PER.FIRST_NAME || ' ' || PER.LAST_NAME || '(' || PER.PERSON_ID || ')' WHEN 'FIRSTNAME_LASTNAME2' THEN PER.FIRST_NAME || PER.LAST_NAME || '(' || PER.PERSON_ID || ')' WHEN 'LASTNAME_F' THEN PER.LAST_NAME || ' ' || SUBSTR(PER.FIRST_NAME
, 1
, 1) || '.' || '(' || PER.PERSON_ID || ')' WHEN 'LASTNAME_FIRSTNAME1' THEN PER.LAST_NAME || ' ' || PER.FIRST_NAME || '(' || PER.PERSON_ID || ')' WHEN 'LASTNAME_FIRSTNAME2' THEN PER.LAST_NAME || '
, ' || PER.FIRST_NAME || '(' || PER.PERSON_ID || ')' WHEN 'LASTNAME_FIRSTNAME3' THEN PER.LAST_NAME || PER.FIRST_NAME || '(' || PER.PERSON_ID || ')' WHEN 'LASTNAME_KNOWN_AS' THEN PER.LAST_NAME || '
, ' || NVL(PER.KNOWN_AS
, PER.FIRST_NAME) || '(' || PER.PERSON_ID || ')' WHEN 'KNOWN_AS_LASTNAME' THEN NVL(PER.KNOWN_AS
, PER.FIRST_NAME) || ' ' || PER.LAST_NAME || '(' || PER.PERSON_ID || ')' 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) || '(' || PER.PERSON_ID || ')' WHEN 'L' THEN NVL(PER.LOCAL_NAME
, SUBSTR(PER.FIRST_NAME
, 1
, 1) || '. ' || PER.LAST_NAME) || '(' || PER.PERSON_ID || ')' ELSE SUBSTR(PER.FIRST_NAME
, 1
, 1) || '. ' || PER.LAST_NAME || '(' || PER.PERSON_ID || ')' END) ELSE SUBSTR(PER.FIRST_NAME
, 1
, 1) || '. ' || PER.LAST_NAME || '(' || PER.PERSON_ID || ')' END) PERS_PERSONSC_NAME_UNQ
, ( 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) PERS_PERSON_NAME_CRNT
, ( CASE FND_PROFILE.VALUE('HRI_DBI_PERSON_NAME_FORMAT') WHEN 'F_LASTNAME' THEN SUBSTR(PER.FIRST_NAME
, 1
, 1) || '. ' || PER.LAST_NAME || '(' || PER.PERSON_ID || ')' WHEN 'FIRSTNAME_LASTNAME1' THEN PER.FIRST_NAME || ' ' || PER.LAST_NAME || '(' || PER.PERSON_ID || ')' WHEN 'FIRSTNAME_LASTNAME2' THEN PER.FIRST_NAME || PER.LAST_NAME || '(' || PER.PERSON_ID || ')' WHEN 'LASTNAME_F' THEN PER.LAST_NAME || ' ' || SUBSTR(PER.FIRST_NAME
, 1
, 1) || '.' || '(' || PER.PERSON_ID || ')' WHEN 'LASTNAME_FIRSTNAME1' THEN PER.LAST_NAME || ' ' || PER.FIRST_NAME || '(' || PER.PERSON_ID || ')' WHEN 'LASTNAME_FIRSTNAME2' THEN PER.LAST_NAME || '
, ' || PER.FIRST_NAME || '(' || PER.PERSON_ID || ')' WHEN 'LASTNAME_FIRSTNAME3' THEN PER.LAST_NAME || PER.FIRST_NAME || '(' || PER.PERSON_ID || ')' WHEN 'LASTNAME_KNOWN_AS' THEN PER.LAST_NAME || '
, ' || NVL(PER.KNOWN_AS
, PER.FIRST_NAME) || '(' || PER.PERSON_ID || ')' WHEN 'KNOWN_AS_LASTNAME' THEN NVL(PER.KNOWN_AS
, PER.FIRST_NAME) || ' ' || PER.LAST_NAME || '(' || PER.PERSON_ID || ')' 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) || '(' || PER.PERSON_ID || ')' WHEN 'L' THEN NVL(PER.LOCAL_NAME
, SUBSTR(PER.FIRST_NAME
, 1
, 1) || '. ' || PER.LAST_NAME) || '(' || PER.PERSON_ID || ')' ELSE SUBSTR(PER.FIRST_NAME
, 1
, 1) || '. ' || PER.LAST_NAME || '(' || PER.PERSON_ID || ')' END) ELSE SUBSTR(PER.FIRST_NAME
, 1
, 1) || '. ' || PER.LAST_NAME || '(' || PER.PERSON_ID || ')' END) PERS_PERSONSC_NAME_CRNT_UNQ
, PER.EFFECTIVE_START_DATE PERS_DATE_START
, PER.EFFECTIVE_END_DATE PERS_DATE_END
, ( 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) PERS_ORDER_BY
, PER.PERSON_ID PERS_PERSON_FK
, PER.LAST_NAME PERS_LAST_NAME
, PER.APPLICANT_NUMBER PERS_APPLICANT_NUMBER
, PER.EMPLOYEE_NUMBER PERS_EMPLOYEE_NUMBER
, PER.NPW_NUMBER PERS_NPW_NUMBER
, NVL(PER.CURRENT_APPLICANT_FLAG
, 'N') PERS_APL_FLAG_CODE
, NVL(PER.CURRENT_EMPLOYEE_FLAG
, 'N') PERS_EMP_FLAG_CODE
, NVL(PER.CURRENT_NPW_FLAG
, 'N') PERS_CWK_FLAG_CODE
, NVL(PER.CURRENT_EMP_OR_APL_FLAG
, 'N') PERS_EMP_OR_APL_FLAG_CODE
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'N'
, NVL(PER.CURRENT_NPW_FLAG
, 'N')
, NVL(PER.CURRENT_EMPLOYEE_FLAG
, 'N')) PERS_WORKER_FLAG_CODE
, PER.MARITAL_STATUS PERS_MARITAL_STATUS_CODE
, PER.SEX PERS_GENDER_CODE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PER.CURRENT_APPLICANT_FLAG
, 'N')) PERS_APL_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PER.CURRENT_EMPLOYEE_FLAG
, 'N')) PERS_EMP_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PER.CURRENT_NPW_FLAG
, 'N')) PERS_CWK_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PER.CURRENT_EMP_OR_APL_FLAG
, 'N')) PERS_EMP_OR_APL_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'N'
, NVL(PER.CURRENT_NPW_FLAG
, 'N')
, NVL(PER.CURRENT_EMPLOYEE_FLAG
, 'N'))) PERS_WORKER_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('MAR_STATUS'
, PER.MARITAL_STATUS) PERS_MARITAL_STATUS
, HR_BIS.BIS_DECODE_LOOKUP('SEX'
, PER.SEX) PERS_GENDER
, PER.MIDDLE_NAMES PERS_MIDDLE_NAMES
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 UNION ALL SELECT TO_CHAR(ID) || '(' || HR_GENERAL.START_OF_TIME || '
, ' || HR_GENERAL.END_OF_TIME || ')'PERS_PERSONSC_PK
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG PERS_PERSONSC_NAME
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG || '(' || TO_CHAR(ID) || ')' PERS_PERSONSC_NAME_UNQ
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG PERS_PERSON_NAME_CRNT
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG || TO_CHAR(ID) PERS_PERSONSC_NAME_CRNT_UNQ
, HR_GENERAL.START_OF_TIME PERS_DATE_START
, HR_GENERAL.END_OF_TIME PERS_DATE_END
, VALUE PERS_ORDER_BY
, ID PERS_PERSON_FK
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG PERS_LAST_NAME
, ID_CHAR PERS_APPLICANT_NUMBER
, ID_CHAR PERS_EMPLOYEE_NUMBER
, ID_CHAR PERS_NPW_NUMBER
, ID_CHAR PERS_APL_FLAG_CODE
, ID_CHAR PERS_EMP_FLAG_CODE
, ID_CHAR PERS_CWK_FLAG_CODE
, ID_CHAR PERS_EMP_OR_APL_FLAG_CODE
, ID_CHAR PERS_WORKER_FLAG_CODE
, ID_CHAR PERS_MARITAL_STATUS_CODE
, ID_CHAR PERS_GENDER_CODE
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG PERS_APL_FLAG
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG PERS_EMP_FLAG
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG PERS_CWK_FLAG
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG PERS_EMP_OR_APL_FLAG
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG PERS_WORKER_FLAG
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG PERS_MARITAL_STATUS
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG PERS_GENDER
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG PERS_MIDDLE_NAMES
FROM HRI_UNASSIGNED WITH READ ONLY