VIEW: APPS.PER_ALL_ASSIGNMENTS_V

Source

View Text - Preformatted

SELECT A.ASSIGNMENT_NUMBER , P.FULL_NAME , P.ORDER_NAME /* , TTL.USER_PERSON_TYPE */ , hr_person_type_usage_info.get_user_person_type(ses.effective_date,p.person_id) user_person_type , P.PERSON_ID , A.ASSIGNMENT_ID , A.PAYROLL_ID , A.PEOPLE_GROUP_ID , A.ORGANIZATION_ID , A.POSITION_ID , A.GRADE_ID , A.JOB_ID , A.LOCATION_ID , HR_GENERAL.DECODE_ORGANIZATION(A.ORGANIZATION_ID) ORGANIZATION , G.GROUP_NAME PEOPLE_GROUP , JBT.NAME JOB , HR_GENERAL.DECODE_POSITION_LATEST_NAME(A.POSITION_ID) POSITION , PGT.NAME GRADE , PY.PAYROLL_NAME PAYROLL , P.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID , P.CURRENT_EMPLOYEE_FLAG FROM PER_ALL_ASSIGNMENTS_F A , PER_ALL_PEOPLE_F P , PER_PERSON_TYPES T , PER_PERSON_TYPES_TL TTL , PAY_PEOPLE_GROUPS G , PER_JOBS_TL JBT , PER_GRADES_TL PGT , PAY_ALL_PAYROLLS_F PY , FND_SESSIONS SES WHERE A.PERSON_ID = P.PERSON_ID AND A.ASSIGNMENT_TYPE = 'E' AND T.PERSON_TYPE_ID = P.PERSON_TYPE_ID AND G.PEOPLE_GROUP_ID (+) = A.PEOPLE_GROUP_ID AND JBT.JOB_ID (+) = A.JOB_ID AND JBT.LANGUAGE (+) = userenv('LANG') AND PGT.GRADE_ID (+) = A.GRADE_ID AND PGT.LANGUAGE (+) = userenv('LANG') AND PY.PAYROLL_ID (+) = A.PAYROLL_ID AND SES.SESSION_ID = USERENV('sessionid') AND SES.EFFECTIVE_DATE BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE AND SES.EFFECTIVE_DATE BETWEEN PY.EFFECTIVE_START_DATE AND PY.EFFECTIVE_END_DATE AND SES.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE AND T.person_type_id = TTL.person_type_id AND TTL.language = userenv('LANG')

View Text - HTML Formatted

SELECT A.ASSIGNMENT_NUMBER
, P.FULL_NAME
, P.ORDER_NAME /*
, TTL.USER_PERSON_TYPE */
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(SES.EFFECTIVE_DATE
, P.PERSON_ID) USER_PERSON_TYPE
, P.PERSON_ID
, A.ASSIGNMENT_ID
, A.PAYROLL_ID
, A.PEOPLE_GROUP_ID
, A.ORGANIZATION_ID
, A.POSITION_ID
, A.GRADE_ID
, A.JOB_ID
, A.LOCATION_ID
, HR_GENERAL.DECODE_ORGANIZATION(A.ORGANIZATION_ID) ORGANIZATION
, G.GROUP_NAME PEOPLE_GROUP
, JBT.NAME JOB
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(A.POSITION_ID) POSITION
, PGT.NAME GRADE
, PY.PAYROLL_NAME PAYROLL
, P.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, P.CURRENT_EMPLOYEE_FLAG
FROM PER_ALL_ASSIGNMENTS_F A
, PER_ALL_PEOPLE_F P
, PER_PERSON_TYPES T
, PER_PERSON_TYPES_TL TTL
, PAY_PEOPLE_GROUPS G
, PER_JOBS_TL JBT
, PER_GRADES_TL PGT
, PAY_ALL_PAYROLLS_F PY
, FND_SESSIONS SES
WHERE A.PERSON_ID = P.PERSON_ID
AND A.ASSIGNMENT_TYPE = 'E'
AND T.PERSON_TYPE_ID = P.PERSON_TYPE_ID
AND G.PEOPLE_GROUP_ID (+) = A.PEOPLE_GROUP_ID
AND JBT.JOB_ID (+) = A.JOB_ID
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND PGT.GRADE_ID (+) = A.GRADE_ID
AND PGT.LANGUAGE (+) = USERENV('LANG')
AND PY.PAYROLL_ID (+) = A.PAYROLL_ID
AND SES.SESSION_ID = USERENV('SESSIONID')
AND SES.EFFECTIVE_DATE BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN PY.EFFECTIVE_START_DATE
AND PY.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND T.PERSON_TYPE_ID = TTL.PERSON_TYPE_ID
AND TTL.LANGUAGE = USERENV('LANG')