eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PER_ASSIGNMENTS_V3

Source

View Text - Preformatted


SELECT A.ROWID ROW_ID , A.BUSINESS_GROUP_ID BUSINESS_GROUP_ID , A.PERSON_ID , A.PERIOD_OF_SERVICE_ID , A.ASSIGNMENT_NUMBER , A.EFFECTIVE_START_DATE , A.EFFECTIVE_END_DATE , A.ORGANIZATION_ID , A.JOB_ID , A.POSITION_ID , A.GRADE_ID , A.PEOPLE_GROUP_ID , A.PAYROLL_ID , A.ASSIGNMENT_ID , A.LOCATION_ID , A.ASSIGNMENT_STATUS_TYPE_ID , A.CHANGE_REASON , A.ASSIGNMENT_SEQUENCE , A.ASS_ATTRIBUTE_CATEGORY , A.ASS_ATTRIBUTE1 , A.ASS_ATTRIBUTE2 , A.ASS_ATTRIBUTE3 , A.ASS_ATTRIBUTE4 , A.ASS_ATTRIBUTE5 , A.ASS_ATTRIBUTE6 , A.ASS_ATTRIBUTE7 , A.ASS_ATTRIBUTE8 , A.ASS_ATTRIBUTE9 , A.ASS_ATTRIBUTE10 , A.ASS_ATTRIBUTE11 , A.ASS_ATTRIBUTE12 , A.ASS_ATTRIBUTE13 , A.ASS_ATTRIBUTE14 , A.ASS_ATTRIBUTE15 , A.ASS_ATTRIBUTE16 , A.ASS_ATTRIBUTE17 , A.ASS_ATTRIBUTE18 , A.ASS_ATTRIBUTE19 , A.ASS_ATTRIBUTE20 , A.ASS_ATTRIBUTE21 , A.ASS_ATTRIBUTE22 , A.ASS_ATTRIBUTE23 , A.ASS_ATTRIBUTE24 , A.ASS_ATTRIBUTE25 , A.ASS_ATTRIBUTE26 , A.ASS_ATTRIBUTE27 , A.ASS_ATTRIBUTE28 , A.ASS_ATTRIBUTE29 , A.ASS_ATTRIBUTE30 , A.LAST_UPDATE_DATE , A.LAST_UPDATED_BY , A.LAST_UPDATE_LOGIN , A.CREATED_BY , A.CREATION_DATE , A.ASSIGNMENT_TYPE , HR_GENERAL.DECODE_PAY_BASIS (A.PAY_BASIS_ID) PAY_BASIS , PP.FIRST_NAME , PP.LAST_NAME , PP.FULL_NAME , PP.APPLICANT_NUMBER , PP.EMAIL_ADDRESS , PP.KNOWN_AS , HR_GENERAL.DECODE_LOOKUP('MAR_STATUS',PP.MARITAL_STATUS) MARITAL_STATUS , PP.MIDDLE_NAMES , HR_GENERAL.DECODE_LOOKUP('NATIONALITY',PP.NATIONALITY) NATIONALITY , PP.NATIONAL_IDENTIFIER , PP.PREVIOUS_LAST_NAME , PP.REGISTERED_DISABLED_FLAG , HR_GENERAL.DECODE_LOOKUP('SEX',PP.SEX) SEX , HR_GENERAL.GET_WORK_PHONE (pp.person_id) work_telephone , PP.CURRENT_EMPLOYEE_FLAG CUR_EMP_FLAG /* , --HR_GENERAL.DECODE_LOOKUP('EMP_OR_APL', PP.CURRENT_EMP_OR_APL_FLAG) -- CUR_EMP_OR_APL_FLAG */ , PP.CURRENT_APPLICANT_FLAG CUR_APL_FLAG , HR_GENERAL.DECODE_LOOKUP('EMP_CAT',A.EMPLOYMENT_CATEGORY) EMPLOYMENT_CATEGORY , HR_GENERAL.DECODE_LOOKUP('TITLE',PP.TITLE) TITLE , ORGTL.NAME ORGANIZATION , HR_GENERAL.DECODE_GRADE (A.GRADE_ID) GRADE , HR_GENERAL.DECODE_PAYROLL (A.PAYROLL_ID) PAYROLL ,pjtl.name JOB , HR_GENERAL.DECODE_POSITION (A.POSITION_ID) POSITION , HR_GENERAL.DECODE_LOCATION (A.LOCATION_ID) LOCATION_CODE , NVL(HR_GENERAL.DECODE_ASS_STATUS_TYPE (A.ASSIGNMENT_STATUS_TYPE_ID, A.BUSINESS_GROUP_ID) , ASTTL.USER_STATUS) STATUS , AST.PER_SYSTEM_STATUS , HR_GENERAL.DECODE_LOOKUP(DECODE(A.ASSIGNMENT_TYPE,'A', 'APL_ASSIGN_REASON','EMP_ASSIGN_REASON') ,A.CHANGE_REASON) REASON , PT.SYSTEM_PERSON_TYPE PERSON_TYPE /* , PTTL.USER_PERSON_TYPE USER_PERSON_TYPE */ , hr_person_type_usage_info.get_user_person_type(f.effective_date,pp.person_id) user_person_type , PT.PERSON_TYPE_ID , PP.EMPLOYEE_NUMBER , PP.DATE_OF_BIRTH ,A.PROBATION_PERIOD ,HR_GENERAL.DECODE_LOOKUP('JP_SCHOOL_TYPE', AC.SEGMENT1) SCHOOL_TYPE ,AC.SEGMENT2 SCHOOL_CODE ,AC.SEGMENT3 SCHOOL_NAME ,A.PROBATION_UNIT ,AC.SEGMENT4 SCHOOL_NAME_KANA ,A.NOTICE_PERIOD ,AC.SEGMENT5 MAJOR ,A.NOTICE_PERIOD_UOM ,AC.SEGMENT6 MAJOR_KANA ,AC.SEGMENT7 ADVISOR ,AC.SEGMENT8 DATE_OF_GRADUATION ,HR_GENERAL.DECODE_LOOKUP('EMPLOYEE_CATG',A.EMPLOYEE_CATEGORY) ,AC.SEGMENT9 NOTES ,AC.SEGMENT10 LAST_FLAG ,POS.DATE_START HIRE_DATE ,PP.NPW_NUMBER ,PP.CURRENT_NPW_FLAG ,A.PERIOD_OF_PLACEMENT_DATE_START ,A.VENDOR_ID ,A.VENDOR_EMPLOYEE_NUMBER ,A.VENDOR_ASSIGNMENT_NUMBER ,POV.VENDOR_NAME ,A.ASSIGNMENT_CATEGORY ,A.PROJECT_TITLE ,A.GRADE_LADDER_PGM_ID ,HR_GENERAL.DECODE_GRADE_LADDER(A.GRADE_LADDER_PGM_ID) GRADE_LADDER FROM hr_all_organization_units org, hr_all_organization_units_tl orgtl, per_assignment_status_types ast, per_assignment_status_types_tl asttl, fnd_sessions f, per_assignments_f2 a, per_person_types pt, per_person_types_tl pttl, per_people_f pp, per_periods_of_service pos, per_person_analyses pa, per_analysis_criteria ac, po_vendors pov , fnd_sessions fnd , fnd_id_flex_structures b , per_jobs pj, per_jobs_tl pjtl WHERE ((a.assignment_type IN ('E','C') AND a.effective_end_date < f.effective_date AND a.effective_start_date = (SELECT MAX(a2.effective_start_date) FROM per_all_assignments_f a2 WHERE a.assignment_id = a2.assignment_id /* and a2.effective_end_date < f.effective_date */ )) OR (a.assignment_type IN ('A', 'E', 'C') AND f.effective_date BETWEEN a.effective_start_date AND a.effective_end_date)) AND a.person_id = pp.person_id AND pp.person_type_id + 0 = pt.person_type_id AND f.session_id = USERENV('sessionid') AND f.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date AND a.organization_id = org.organization_id AND a.assignment_status_type_id = ast.assignment_status_type_id AND pos.period_of_service_id (+) = a.period_of_service_id AND org.organization_id = orgtl.organization_id AND orgtl.language = USERENV('LANG') AND pj.job_id (+) = a.job_id AND pjtl.job_id (+) = pj.job_id AND (pjtl.language is null OR pjtl.language = USERENV('LANG')) AND pt.person_type_id = pttl.person_type_id AND pttl.language = USERENV ('LANG') AND ast.assignment_status_type_id = asttl.assignment_status_type_id AND asttl.language = USERENV ('LANG') AND pa.person_id (+) = a.person_id AND ac.analysis_criteria_id (+) = pa.analysis_criteria_id AND pa.id_flex_num (+) = 1 AND b.id_flex_num (+) = pa.id_flex_num AND b.id_flex_code (+) = 'PEA' AND b.application_id (+) = 800 AND b.id_flex_num (+) = 1 AND fnd.session_id = USERENV ('sessionid') AND (pa.date_from IS NULL OR (fnd.effective_date >= pa.date_from AND (pa.date_to IS NULL OR fnd.effective_date <= pa.date_to) ) ) AND a.vendor_id = pov.vendor_id (+)

View Text - HTML Formatted

SELECT A.ROWID ROW_ID
, A.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, A.PERSON_ID
, A.PERIOD_OF_SERVICE_ID
, A.ASSIGNMENT_NUMBER
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE
, A.ORGANIZATION_ID
, A.JOB_ID
, A.POSITION_ID
, A.GRADE_ID
, A.PEOPLE_GROUP_ID
, A.PAYROLL_ID
, A.ASSIGNMENT_ID
, A.LOCATION_ID
, A.ASSIGNMENT_STATUS_TYPE_ID
, A.CHANGE_REASON
, A.ASSIGNMENT_SEQUENCE
, A.ASS_ATTRIBUTE_CATEGORY
, A.ASS_ATTRIBUTE1
, A.ASS_ATTRIBUTE2
, A.ASS_ATTRIBUTE3
, A.ASS_ATTRIBUTE4
, A.ASS_ATTRIBUTE5
, A.ASS_ATTRIBUTE6
, A.ASS_ATTRIBUTE7
, A.ASS_ATTRIBUTE8
, A.ASS_ATTRIBUTE9
, A.ASS_ATTRIBUTE10
, A.ASS_ATTRIBUTE11
, A.ASS_ATTRIBUTE12
, A.ASS_ATTRIBUTE13
, A.ASS_ATTRIBUTE14
, A.ASS_ATTRIBUTE15
, A.ASS_ATTRIBUTE16
, A.ASS_ATTRIBUTE17
, A.ASS_ATTRIBUTE18
, A.ASS_ATTRIBUTE19
, A.ASS_ATTRIBUTE20
, A.ASS_ATTRIBUTE21
, A.ASS_ATTRIBUTE22
, A.ASS_ATTRIBUTE23
, A.ASS_ATTRIBUTE24
, A.ASS_ATTRIBUTE25
, A.ASS_ATTRIBUTE26
, A.ASS_ATTRIBUTE27
, A.ASS_ATTRIBUTE28
, A.ASS_ATTRIBUTE29
, A.ASS_ATTRIBUTE30
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_LOGIN
, A.CREATED_BY
, A.CREATION_DATE
, A.ASSIGNMENT_TYPE
, HR_GENERAL.DECODE_PAY_BASIS (A.PAY_BASIS_ID) PAY_BASIS
, PP.FIRST_NAME
, PP.LAST_NAME
, PP.FULL_NAME
, PP.APPLICANT_NUMBER
, PP.EMAIL_ADDRESS
, PP.KNOWN_AS
, HR_GENERAL.DECODE_LOOKUP('MAR_STATUS'
, PP.MARITAL_STATUS) MARITAL_STATUS
, PP.MIDDLE_NAMES
, HR_GENERAL.DECODE_LOOKUP('NATIONALITY'
, PP.NATIONALITY) NATIONALITY
, PP.NATIONAL_IDENTIFIER
, PP.PREVIOUS_LAST_NAME
, PP.REGISTERED_DISABLED_FLAG
, HR_GENERAL.DECODE_LOOKUP('SEX'
, PP.SEX) SEX
, HR_GENERAL.GET_WORK_PHONE (PP.PERSON_ID) WORK_TELEPHONE
, PP.CURRENT_EMPLOYEE_FLAG CUR_EMP_FLAG /*
, --HR_GENERAL.DECODE_LOOKUP('EMP_OR_APL'
, PP.CURRENT_EMP_OR_APL_FLAG) -- CUR_EMP_OR_APL_FLAG */
, PP.CURRENT_APPLICANT_FLAG CUR_APL_FLAG
, HR_GENERAL.DECODE_LOOKUP('EMP_CAT'
, A.EMPLOYMENT_CATEGORY) EMPLOYMENT_CATEGORY
, HR_GENERAL.DECODE_LOOKUP('TITLE'
, PP.TITLE) TITLE
, ORGTL.NAME ORGANIZATION
, HR_GENERAL.DECODE_GRADE (A.GRADE_ID) GRADE
, HR_GENERAL.DECODE_PAYROLL (A.PAYROLL_ID) PAYROLL
, PJTL.NAME JOB
, HR_GENERAL.DECODE_POSITION (A.POSITION_ID) POSITION
, HR_GENERAL.DECODE_LOCATION (A.LOCATION_ID) LOCATION_CODE
, NVL(HR_GENERAL.DECODE_ASS_STATUS_TYPE (A.ASSIGNMENT_STATUS_TYPE_ID
, A.BUSINESS_GROUP_ID)
, ASTTL.USER_STATUS) STATUS
, AST.PER_SYSTEM_STATUS
, HR_GENERAL.DECODE_LOOKUP(DECODE(A.ASSIGNMENT_TYPE
, 'A'
, 'APL_ASSIGN_REASON'
, 'EMP_ASSIGN_REASON')
, A.CHANGE_REASON) REASON
, PT.SYSTEM_PERSON_TYPE PERSON_TYPE /*
, PTTL.USER_PERSON_TYPE USER_PERSON_TYPE */
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(F.EFFECTIVE_DATE
, PP.PERSON_ID) USER_PERSON_TYPE
, PT.PERSON_TYPE_ID
, PP.EMPLOYEE_NUMBER
, PP.DATE_OF_BIRTH
, A.PROBATION_PERIOD
, HR_GENERAL.DECODE_LOOKUP('JP_SCHOOL_TYPE'
, AC.SEGMENT1) SCHOOL_TYPE
, AC.SEGMENT2 SCHOOL_CODE
, AC.SEGMENT3 SCHOOL_NAME
, A.PROBATION_UNIT
, AC.SEGMENT4 SCHOOL_NAME_KANA
, A.NOTICE_PERIOD
, AC.SEGMENT5 MAJOR
, A.NOTICE_PERIOD_UOM
, AC.SEGMENT6 MAJOR_KANA
, AC.SEGMENT7 ADVISOR
, AC.SEGMENT8 DATE_OF_GRADUATION
, HR_GENERAL.DECODE_LOOKUP('EMPLOYEE_CATG'
, A.EMPLOYEE_CATEGORY)
, AC.SEGMENT9 NOTES
, AC.SEGMENT10 LAST_FLAG
, POS.DATE_START HIRE_DATE
, PP.NPW_NUMBER
, PP.CURRENT_NPW_FLAG
, A.PERIOD_OF_PLACEMENT_DATE_START
, A.VENDOR_ID
, A.VENDOR_EMPLOYEE_NUMBER
, A.VENDOR_ASSIGNMENT_NUMBER
, POV.VENDOR_NAME
, A.ASSIGNMENT_CATEGORY
, A.PROJECT_TITLE
, A.GRADE_LADDER_PGM_ID
, HR_GENERAL.DECODE_GRADE_LADDER(A.GRADE_LADDER_PGM_ID) GRADE_LADDER
FROM HR_ALL_ORGANIZATION_UNITS ORG
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ASSIGNMENT_STATUS_TYPES_TL ASTTL
, FND_SESSIONS F
, PER_ASSIGNMENTS_F2 A
, PER_PERSON_TYPES PT
, PER_PERSON_TYPES_TL PTTL
, PER_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE POS
, PER_PERSON_ANALYSES PA
, PER_ANALYSIS_CRITERIA AC
, PO_VENDORS POV
, FND_SESSIONS FND
, FND_ID_FLEX_STRUCTURES B
, PER_JOBS PJ
, PER_JOBS_TL PJTL
WHERE ((A.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND A.EFFECTIVE_END_DATE < F.EFFECTIVE_DATE
AND A.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE A.ASSIGNMENT_ID = A2.ASSIGNMENT_ID /*
AND A2.EFFECTIVE_END_DATE < F.EFFECTIVE_DATE */ )) OR (A.ASSIGNMENT_TYPE IN ('A'
, 'E'
, 'C')
AND F.EFFECTIVE_DATE BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE))
AND A.PERSON_ID = PP.PERSON_ID
AND PP.PERSON_TYPE_ID + 0 = PT.PERSON_TYPE_ID
AND F.SESSION_ID = USERENV('SESSIONID')
AND F.EFFECTIVE_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND A.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND A.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND POS.PERIOD_OF_SERVICE_ID (+) = A.PERIOD_OF_SERVICE_ID
AND ORG.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID
AND ORGTL.LANGUAGE = USERENV('LANG')
AND PJ.JOB_ID (+) = A.JOB_ID
AND PJTL.JOB_ID (+) = PJ.JOB_ID
AND (PJTL.LANGUAGE IS NULL OR PJTL.LANGUAGE = USERENV('LANG'))
AND PT.PERSON_TYPE_ID = PTTL.PERSON_TYPE_ID
AND PTTL.LANGUAGE = USERENV ('LANG')
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTTL.ASSIGNMENT_STATUS_TYPE_ID
AND ASTTL.LANGUAGE = USERENV ('LANG')
AND PA.PERSON_ID (+) = A.PERSON_ID
AND AC.ANALYSIS_CRITERIA_ID (+) = PA.ANALYSIS_CRITERIA_ID
AND PA.ID_FLEX_NUM (+) = 1
AND B.ID_FLEX_NUM (+) = PA.ID_FLEX_NUM
AND B.ID_FLEX_CODE (+) = 'PEA'
AND B.APPLICATION_ID (+) = 800
AND B.ID_FLEX_NUM (+) = 1
AND FND.SESSION_ID = USERENV ('SESSIONID')
AND (PA.DATE_FROM IS NULL OR (FND.EFFECTIVE_DATE >= PA.DATE_FROM
AND (PA.DATE_TO IS NULL OR FND.EFFECTIVE_DATE <= PA.DATE_TO) ) )
AND A.VENDOR_ID = POV.VENDOR_ID (+)