VIEW: APPS.PER_POSITIONS_LOV_V2

Source

View Text - Preformatted

SELECT pft2.name, p1.position_id, p1.organization_id, p1.job_id, p1.business_group_id from hr_all_positions_f p1, hr_all_positions_f_tl pft2, per_shared_types pst, fnd_sessions s where s.effective_date between p1.effective_start_date and p1.effective_end_date and p1.position_id = pft2.position_id and pft2.language = userenv('LANG') and (p1.status = 'VALID' OR p1.status is null) and s.session_id = userenv('SESSIONID') and pst.shared_type_id = p1.availability_status_id and pst.lookup_type = 'POSITION_AVAILABILITY_STATUS' and (pst.business_group_id = p1.business_group_id or pst.business_group_id is null) and system_type_cd = 'ACTIVE' and decode(HR_SECURITY.VIEW_ALL ,'Y', 'TRUE', HR_SECURITY.SHOW_RECORD('PER_ALL_POSITIONS', p1.POSITION_ID)) = 'TRUE' and decode(HR_SECURITY.VIEW_ALL ,'Y', 'TRUE', HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS',p1.ORGANIZATION_ID)) ='TRUE' and decode(hr_general.get_xbg_profile,'Y',p1.business_group_id , hr_general.get_business_group_id) = p1.business_group_id

View Text - HTML Formatted

SELECT PFT2.NAME
, P1.POSITION_ID
, P1.ORGANIZATION_ID
, P1.JOB_ID
, P1.BUSINESS_GROUP_ID
FROM HR_ALL_POSITIONS_F P1
, HR_ALL_POSITIONS_F_TL PFT2
, PER_SHARED_TYPES PST
, FND_SESSIONS S
WHERE S.EFFECTIVE_DATE BETWEEN P1.EFFECTIVE_START_DATE
AND P1.EFFECTIVE_END_DATE
AND P1.POSITION_ID = PFT2.POSITION_ID
AND PFT2.LANGUAGE = USERENV('LANG')
AND (P1.STATUS = 'VALID' OR P1.STATUS IS NULL)
AND S.SESSION_ID = USERENV('SESSIONID')
AND PST.SHARED_TYPE_ID = P1.AVAILABILITY_STATUS_ID
AND PST.LOOKUP_TYPE = 'POSITION_AVAILABILITY_STATUS'
AND (PST.BUSINESS_GROUP_ID = P1.BUSINESS_GROUP_ID OR PST.BUSINESS_GROUP_ID IS NULL)
AND SYSTEM_TYPE_CD = 'ACTIVE'
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('PER_ALL_POSITIONS'
, P1.POSITION_ID)) = 'TRUE'
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS'
, P1.ORGANIZATION_ID)) ='TRUE'
AND DECODE(HR_GENERAL.GET_XBG_PROFILE
, 'Y'
, P1.BUSINESS_GROUP_ID
, HR_GENERAL.GET_BUSINESS_GROUP_ID) = P1.BUSINESS_GROUP_ID