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_EXPANDED_ASSIGNMENTS_V1

Source

View Text - Preformatted


SELECT /* * This select gets all the assignments with salaries (non-null pay bases), * assignments with a null pay basis, and assignments with a non-null pay * basis but no element entry. */ distinct p.person_id , p.business_group_id + 0 , p.full_name , p.employee_number , p.applicant_number , p.current_employee_flag , p.current_applicant_flag , a.assignment_id , a.assignment_number , a.assignment_type , a.organization_id , a.payroll_id , a.primary_flag , gdt.name , jbt.name , hr_general.decode_position_latest_name(a.position_id) , orgtl.name , HR_GENERAL.DECODE_PAYROLL(a.payroll_id) payroll_name /* , pttl.user_person_type */ , hr_person_type_usage_info.get_user_person_type(s.effective_date,p.person_id) , a.manager_flag , HR_GENERAL.DECODE_LOCATION(a.location_id) location_code , p2.full_name , nvl(astatl.user_status,asttl.user_status) , a.assignment_status_type_id , pg.segment1 , pg.segment2 , pg.segment3 , pg.segment4 , pg.segment5 , pg.segment6 , pg.segment7 , pg.segment8 , pg.segment9 , pg.segment10 , pg.segment11 , pg.segment12 , pg.segment13 , pg.segment14 , pg.segment15 , pg.segment16 , pg.segment17 , pg.segment18 , pg.segment19 , pg.segment20 , pg.segment21 , pg.segment22 , pg.segment23 , pg.segment24 , pg.segment25 , pg.segment26 , pg.segment27 , pg.segment28 , pg.segment29 , pg.segment30 , gd.segment1 , gd.segment2 , gd.segment3 , gd.segment4 , gd.segment5 , gd.segment6 , gd.segment7 , gd.segment8 , gd.segment9 , gd.segment10 , gd.segment11 , gd.segment12 , gd.segment13 , gd.segment14 , gd.segment15 , gd.segment16 , gd.segment17 , gd.segment18 , gd.segment19 , gd.segment20 , gd.segment21 , gd.segment22 , gd.segment23 , gd.segment24 , gd.segment25 , gd.segment26 , gd.segment27 , gd.segment28 , gd.segment29 , gd.segment30 , jd.segment1 , jd.segment2 , jd.segment3 , jd.segment4 , jd.segment5 , jd.segment6 , jd.segment7 , jd.segment8 , jd.segment9 , jd.segment10 , jd.segment11 , jd.segment12 , jd.segment13 , jd.segment14 , jd.segment15 , jd.segment16 , jd.segment17 , jd.segment18 , jd.segment19 , jd.segment20 , jd.segment21 , jd.segment22 , jd.segment23 , jd.segment24 , jd.segment25 , jd.segment26 , jd.segment27 , jd.segment28 , jd.segment29 , jd.segment30 , pd.segment1 , pd.segment2 , pd.segment3 , pd.segment4 , pd.segment5 , pd.segment6 , pd.segment7 , pd.segment8 , pd.segment9 , pd.segment10 , pd.segment11 , pd.segment12 , pd.segment13 , pd.segment14 , pd.segment15 , pd.segment16 , pd.segment17 , pd.segment18 , pd.segment19 , pd.segment20 , pd.segment21 , pd.segment22 , pd.segment23 , pd.segment24 , pd.segment25 , pd.segment26 , pd.segment27 , pd.segment28 , pd.segment29 , pd.segment30 , HR_GENERAL.GET_SALARY(a.pay_basis_id, a.assignment_id) screen_entry_value , initcap(HR_GENERAL.DECODE_PAY_BASIS(a.pay_basis_id)) pay_basis , a.last_update_date , a.last_updated_by , a.last_update_login , a.created_by , a.creation_date , p.npw_number , p.current_npw_flag from per_position_definitions pd , per_grade_definitions gd , per_grades g , per_grades_tl gdt , per_job_definitions jd , per_jobs j , per_jobs_tl jbt , pay_people_groups pg , per_people_f p , per_all_assignments_f a , hr_all_organization_units_tl orgtl , hr_all_organization_units org , per_person_types_tl pttl , per_person_types pt , per_ass_status_type_amends_tl astatl , per_ass_status_type_amends asta , per_assignment_status_types_tl asttl , per_assignment_status_types ast , fnd_sessions s , per_all_people_f p2 where p.person_id = a.person_id and s.effective_date between p.effective_start_date and p.effective_end_date and s.effective_date between a.effective_start_date and a.effective_end_date and a.people_group_id = pg.people_group_id (+) and a.job_id = j.job_id (+) and a.job_id = jbt.job_id (+) and jbt.language (+) = userenv('LANG') and j.job_definition_id = jd.job_definition_id (+) and a.grade_id = g.grade_id (+) and a.grade_id = gdt.grade_id (+) and gdt.language(+) = userenv('LANG') and g.grade_definition_id = gd.grade_definition_id (+) and HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(a.position_id) = pd.position_definition_id (+) and a.organization_id = org.organization_id (+) and p.person_type_id = pt.person_type_id (+) and a.supervisor_id = p2.person_id (+) and org.organization_id = orgtl.organization_id (+) and decode(orgtl.organization_id,null,'1',orgtl.language) =decode(orgtl.organization_id,null,'1',userenv('LANG')) and pt.person_type_id = pttl.person_type_id (+) and decode(pttl.person_type_id,null,'1',pttl.language) =decode(pttl.person_type_id,null,'1',userenv('LANG')) and ( (a.supervisor_id is not null and (( s.effective_date between p2.effective_start_date and p2.effective_end_date ) or ( p2.start_date > s.effective_date and p2.start_date = p2.effective_start_date )) ) or a.supervisor_id is null ) and a.assignment_status_type_id = ast.assignment_status_type_id and ast.assignment_status_type_id = asttl.assignment_status_type_id and asttl.language = userenv('LANG') and a.assignment_status_type_id = asta.assignment_status_type_id (+) and asta.ass_status_type_amend_id = astatl.ass_status_type_amend_id (+) and decode(astatl.ass_status_type_amend_id,null,'1',astatl.language) =decode(astatl.ass_status_type_amend_id,null,'1',userenv('LANG')) and a.business_group_id +0 = asta.business_group_id(+) +0 and s.session_id = userenv('SESSIONID')

View Text - HTML Formatted

SELECT /* * THIS SELECT GETS ALL THE ASSIGNMENTS WITH SALARIES (NON-NULL PAY BASES)
, * ASSIGNMENTS WITH A NULL PAY BASIS
,
AND ASSIGNMENTS WITH A NON-NULL PAY * BASIS BUT NO ELEMENT ENTRY. */ DISTINCT P.PERSON_ID
, P.BUSINESS_GROUP_ID + 0
, P.FULL_NAME
, P.EMPLOYEE_NUMBER
, P.APPLICANT_NUMBER
, P.CURRENT_EMPLOYEE_FLAG
, P.CURRENT_APPLICANT_FLAG
, A.ASSIGNMENT_ID
, A.ASSIGNMENT_NUMBER
, A.ASSIGNMENT_TYPE
, A.ORGANIZATION_ID
, A.PAYROLL_ID
, A.PRIMARY_FLAG
, GDT.NAME
, JBT.NAME
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(A.POSITION_ID)
, ORGTL.NAME
, HR_GENERAL.DECODE_PAYROLL(A.PAYROLL_ID) PAYROLL_NAME /*
, PTTL.USER_PERSON_TYPE */
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(S.EFFECTIVE_DATE
, P.PERSON_ID)
, A.MANAGER_FLAG
, HR_GENERAL.DECODE_LOCATION(A.LOCATION_ID) LOCATION_CODE
, P2.FULL_NAME
, NVL(ASTATL.USER_STATUS
, ASTTL.USER_STATUS)
, A.ASSIGNMENT_STATUS_TYPE_ID
, PG.SEGMENT1
, PG.SEGMENT2
, PG.SEGMENT3
, PG.SEGMENT4
, PG.SEGMENT5
, PG.SEGMENT6
, PG.SEGMENT7
, PG.SEGMENT8
, PG.SEGMENT9
, PG.SEGMENT10
, PG.SEGMENT11
, PG.SEGMENT12
, PG.SEGMENT13
, PG.SEGMENT14
, PG.SEGMENT15
, PG.SEGMENT16
, PG.SEGMENT17
, PG.SEGMENT18
, PG.SEGMENT19
, PG.SEGMENT20
, PG.SEGMENT21
, PG.SEGMENT22
, PG.SEGMENT23
, PG.SEGMENT24
, PG.SEGMENT25
, PG.SEGMENT26
, PG.SEGMENT27
, PG.SEGMENT28
, PG.SEGMENT29
, PG.SEGMENT30
, GD.SEGMENT1
, GD.SEGMENT2
, GD.SEGMENT3
, GD.SEGMENT4
, GD.SEGMENT5
, GD.SEGMENT6
, GD.SEGMENT7
, GD.SEGMENT8
, GD.SEGMENT9
, GD.SEGMENT10
, GD.SEGMENT11
, GD.SEGMENT12
, GD.SEGMENT13
, GD.SEGMENT14
, GD.SEGMENT15
, GD.SEGMENT16
, GD.SEGMENT17
, GD.SEGMENT18
, GD.SEGMENT19
, GD.SEGMENT20
, GD.SEGMENT21
, GD.SEGMENT22
, GD.SEGMENT23
, GD.SEGMENT24
, GD.SEGMENT25
, GD.SEGMENT26
, GD.SEGMENT27
, GD.SEGMENT28
, GD.SEGMENT29
, GD.SEGMENT30
, JD.SEGMENT1
, JD.SEGMENT2
, JD.SEGMENT3
, JD.SEGMENT4
, JD.SEGMENT5
, JD.SEGMENT6
, JD.SEGMENT7
, JD.SEGMENT8
, JD.SEGMENT9
, JD.SEGMENT10
, JD.SEGMENT11
, JD.SEGMENT12
, JD.SEGMENT13
, JD.SEGMENT14
, JD.SEGMENT15
, JD.SEGMENT16
, JD.SEGMENT17
, JD.SEGMENT18
, JD.SEGMENT19
, JD.SEGMENT20
, JD.SEGMENT21
, JD.SEGMENT22
, JD.SEGMENT23
, JD.SEGMENT24
, JD.SEGMENT25
, JD.SEGMENT26
, JD.SEGMENT27
, JD.SEGMENT28
, JD.SEGMENT29
, JD.SEGMENT30
, PD.SEGMENT1
, PD.SEGMENT2
, PD.SEGMENT3
, PD.SEGMENT4
, PD.SEGMENT5
, PD.SEGMENT6
, PD.SEGMENT7
, PD.SEGMENT8
, PD.SEGMENT9
, PD.SEGMENT10
, PD.SEGMENT11
, PD.SEGMENT12
, PD.SEGMENT13
, PD.SEGMENT14
, PD.SEGMENT15
, PD.SEGMENT16
, PD.SEGMENT17
, PD.SEGMENT18
, PD.SEGMENT19
, PD.SEGMENT20
, PD.SEGMENT21
, PD.SEGMENT22
, PD.SEGMENT23
, PD.SEGMENT24
, PD.SEGMENT25
, PD.SEGMENT26
, PD.SEGMENT27
, PD.SEGMENT28
, PD.SEGMENT29
, PD.SEGMENT30
, HR_GENERAL.GET_SALARY(A.PAY_BASIS_ID
, A.ASSIGNMENT_ID) SCREEN_ENTRY_VALUE
, INITCAP(HR_GENERAL.DECODE_PAY_BASIS(A.PAY_BASIS_ID)) PAY_BASIS
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_LOGIN
, A.CREATED_BY
, A.CREATION_DATE
, P.NPW_NUMBER
, P.CURRENT_NPW_FLAG
FROM PER_POSITION_DEFINITIONS PD
, PER_GRADE_DEFINITIONS GD
, PER_GRADES G
, PER_GRADES_TL GDT
, PER_JOB_DEFINITIONS JD
, PER_JOBS J
, PER_JOBS_TL JBT
, PAY_PEOPLE_GROUPS PG
, PER_PEOPLE_F P
, PER_ALL_ASSIGNMENTS_F A
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL
, HR_ALL_ORGANIZATION_UNITS ORG
, PER_PERSON_TYPES_TL PTTL
, PER_PERSON_TYPES PT
, PER_ASS_STATUS_TYPE_AMENDS_TL ASTATL
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASSIGNMENT_STATUS_TYPES_TL ASTTL
, PER_ASSIGNMENT_STATUS_TYPES AST
, FND_SESSIONS S
, PER_ALL_PEOPLE_F P2
WHERE P.PERSON_ID = A.PERSON_ID
AND S.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND S.EFFECTIVE_DATE BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND A.PEOPLE_GROUP_ID = PG.PEOPLE_GROUP_ID (+)
AND A.JOB_ID = J.JOB_ID (+)
AND A.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND J.JOB_DEFINITION_ID = JD.JOB_DEFINITION_ID (+)
AND A.GRADE_ID = G.GRADE_ID (+)
AND A.GRADE_ID = GDT.GRADE_ID (+)
AND GDT.LANGUAGE(+) = USERENV('LANG')
AND G.GRADE_DEFINITION_ID = GD.GRADE_DEFINITION_ID (+)
AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(A.POSITION_ID) = PD.POSITION_DEFINITION_ID (+)
AND A.ORGANIZATION_ID = ORG.ORGANIZATION_ID (+)
AND P.PERSON_TYPE_ID = PT.PERSON_TYPE_ID (+)
AND A.SUPERVISOR_ID = P2.PERSON_ID (+)
AND ORG.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID (+)
AND DECODE(ORGTL.ORGANIZATION_ID
, NULL
, '1'
, ORGTL.LANGUAGE) =DECODE(ORGTL.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND PT.PERSON_TYPE_ID = PTTL.PERSON_TYPE_ID (+)
AND DECODE(PTTL.PERSON_TYPE_ID
, NULL
, '1'
, PTTL.LANGUAGE) =DECODE(PTTL.PERSON_TYPE_ID
, NULL
, '1'
, USERENV('LANG'))
AND ( (A.SUPERVISOR_ID IS NOT NULL
AND (( S.EFFECTIVE_DATE BETWEEN P2.EFFECTIVE_START_DATE
AND P2.EFFECTIVE_END_DATE ) OR ( P2.START_DATE > S.EFFECTIVE_DATE
AND P2.START_DATE = P2.EFFECTIVE_START_DATE )) ) OR A.SUPERVISOR_ID IS NULL )
AND A.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTTL.ASSIGNMENT_STATUS_TYPE_ID
AND ASTTL.LANGUAGE = USERENV('LANG')
AND A.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND ASTA.ASS_STATUS_TYPE_AMEND_ID = ASTATL.ASS_STATUS_TYPE_AMEND_ID (+)
AND DECODE(ASTATL.ASS_STATUS_TYPE_AMEND_ID
, NULL
, '1'
, ASTATL.LANGUAGE) =DECODE(ASTATL.ASS_STATUS_TYPE_AMEND_ID
, NULL
, '1'
, USERENV('LANG'))
AND A.BUSINESS_GROUP_ID +0 = ASTA.BUSINESS_GROUP_ID(+) +0
AND S.SESSION_ID = USERENV('SESSIONID')