VIEW: APPS.IRCFV_REC_TEAM_MEMBERS

Source

View Text - Preformatted

SELECT peo.full_name member_name , peo.email_address member_email_address , vac.name vacancy_name , rtm.start_date start_date , rtm.end_date end_date , nvl(rtm.end_date,hr_general.end_of_time) end_date_nn , hr_bis.bis_decode_lookup('IRC_RECRUITMENT_TEAM_SECURITY', decode(rtm.update_allowed , 'Y', DECODE( rtm.delete_allowed, 'Y', 'UD', 'U') , 'N', DECODE( rtm.delete_allowed, 'N', 'V'))) security , hr_bis.bis_decode_lookup('YES_NO', rtm.update_allowed) update_allowed , hr_bis.bis_decode_lookup('YES_NO', rtm.delete_allowed) delete_allowed /* system codes */ , decode(rtm.update_allowed , 'Y', DECODE( rtm.delete_allowed, 'Y', 'UD', 'U') , 'N', DECODE( rtm.delete_allowed, 'N', 'V')) security_code ,rtm.update_allowed update_allowed_code ,rtm.delete_allowed delete_allowed_code /* system ids */ ,rtm.party_id party_id ,rtm.vacancy_id vacancy_id ,rtm.job_id job_id ,peo.person_id person_id FROM irc_rec_team_members rtm, hz_person_profiles par, per_vacancies vac, per_all_people_f peo WHERE rtm.party_id = par.party_id AND rtm.vacancy_id = vac.vacancy_id AND rtm.person_id = peo.person_id AND TRUNC(SYSDATE) BETWEEN peo.effective_start_date AND peo.effective_end_date AND trunc(sysdate) between NVL(par.effective_start_date, hr_general.start_of_time) and NVL(par.effective_end_date , hr_general.end_of_time) WITH READ ONLY

View Text - HTML Formatted

SELECT PEO.FULL_NAME MEMBER_NAME
, PEO.EMAIL_ADDRESS MEMBER_EMAIL_ADDRESS
, VAC.NAME VACANCY_NAME
, RTM.START_DATE START_DATE
, RTM.END_DATE END_DATE
, NVL(RTM.END_DATE
, HR_GENERAL.END_OF_TIME) END_DATE_NN
, HR_BIS.BIS_DECODE_LOOKUP('IRC_RECRUITMENT_TEAM_SECURITY'
, DECODE(RTM.UPDATE_ALLOWED
, 'Y'
, DECODE( RTM.DELETE_ALLOWED
, 'Y'
, 'UD'
, 'U')
, 'N'
, DECODE( RTM.DELETE_ALLOWED
, 'N'
, 'V'))) SECURITY
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, RTM.UPDATE_ALLOWED) UPDATE_ALLOWED
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, RTM.DELETE_ALLOWED) DELETE_ALLOWED /* SYSTEM CODES */
, DECODE(RTM.UPDATE_ALLOWED
, 'Y'
, DECODE( RTM.DELETE_ALLOWED
, 'Y'
, 'UD'
, 'U')
, 'N'
, DECODE( RTM.DELETE_ALLOWED
, 'N'
, 'V')) SECURITY_CODE
, RTM.UPDATE_ALLOWED UPDATE_ALLOWED_CODE
, RTM.DELETE_ALLOWED DELETE_ALLOWED_CODE /* SYSTEM IDS */
, RTM.PARTY_ID PARTY_ID
, RTM.VACANCY_ID VACANCY_ID
, RTM.JOB_ID JOB_ID
, PEO.PERSON_ID PERSON_ID
FROM IRC_REC_TEAM_MEMBERS RTM
, HZ_PERSON_PROFILES PAR
, PER_VACANCIES VAC
, PER_ALL_PEOPLE_F PEO
WHERE RTM.PARTY_ID = PAR.PARTY_ID
AND RTM.VACANCY_ID = VAC.VACANCY_ID
AND RTM.PERSON_ID = PEO.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN NVL(PAR.EFFECTIVE_START_DATE
, HR_GENERAL.START_OF_TIME)
AND NVL(PAR.EFFECTIVE_END_DATE
, HR_GENERAL.END_OF_TIME) WITH READ ONLY