VIEW: APPS.HRI_MB_WMV_V

Source

View Text - Preformatted

SELECT wmv.primary_asg_indicator primary_asg_indicator , wmv.asg_indicator asg_indicator , wmv.fte fte , wmv.head head , wmv.hours hours , wmv.money money , wmv.pft pft , wmv.user1 user1 , wmv.user2 user2 , wmv.user3 user3 , wmv.user4 user4 , wmv.user5 user5 , GREATEST(asg.effective_start_date ,wmv.effective_start_date) event_date , GREATEST(asg.effective_start_date ,wmv.effective_start_date) effective_start_date , LEAST(asg.effective_end_date ,wmv.effective_end_date) effective_end_date , asg.assignment_id assignment_id , asg.person_id person_id , asg.business_group_id business_group_id , asg.organization_id organization_id , NVL(asg.location_id,-1) location_id , NVL(asg.job_id,-1) job_id , NVL(asg.grade_id,-1) grade_id , NVL(asg.position_id,-1) position_id , NVL(asg.supervisor_id,-1) supervisor_person_id , NVL(asg.payroll_id,-1) payroll_id , asg.pay_basis_id pay_basis_id , NVL(asg.pay_basis_id,-1) people_group_id , asg.assignment_status_type_id assignment_status_type_id , asg.assignment_type assignment_type_code , ast.per_system_status per_system_status_code , ast.pay_system_status pay_system_status_code , NVL(asg.recruiter_id,-1) recruiter_id , NVL(asg.set_of_books_id,-1) set_of_books_id , asg.period_of_service_id period_of_service_id , asg.employment_category employment_category_code , asg.primary_flag asg_primary_flag_code , GREATEST(wmv.last_change_date ,asg.last_update_date ,ast.last_update_date ) last_change_date , asg.effective_start_date asg_effective_start_date , asg.effective_end_date asg_effective_end_date , wmv.effective_start_date wmv_effective_start_date , wmv.effective_end_date wmv_effective_end_date FROM hri_mb_wmv wmv , per_all_assignments_f asg , per_assignment_status_types ast WHERE ast.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN') AND asg.assignment_id = wmv.assignment_id AND asg.assignment_status_type_id = ast.assignment_status_type_id AND ( (asg.effective_start_date BETWEEN wmv.effective_start_date AND wmv.effective_end_date) OR (wmv.effective_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date) ) WITH READ ONLY

View Text - HTML Formatted

SELECT WMV.PRIMARY_ASG_INDICATOR PRIMARY_ASG_INDICATOR
, WMV.ASG_INDICATOR ASG_INDICATOR
, WMV.FTE FTE
, WMV.HEAD HEAD
, WMV.HOURS HOURS
, WMV.MONEY MONEY
, WMV.PFT PFT
, WMV.USER1 USER1
, WMV.USER2 USER2
, WMV.USER3 USER3
, WMV.USER4 USER4
, WMV.USER5 USER5
, GREATEST(ASG.EFFECTIVE_START_DATE
, WMV.EFFECTIVE_START_DATE) EVENT_DATE
, GREATEST(ASG.EFFECTIVE_START_DATE
, WMV.EFFECTIVE_START_DATE) EFFECTIVE_START_DATE
, LEAST(ASG.EFFECTIVE_END_DATE
, WMV.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.PERSON_ID PERSON_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, NVL(ASG.LOCATION_ID
, -1) LOCATION_ID
, NVL(ASG.JOB_ID
, -1) JOB_ID
, NVL(ASG.GRADE_ID
, -1) GRADE_ID
, NVL(ASG.POSITION_ID
, -1) POSITION_ID
, NVL(ASG.SUPERVISOR_ID
, -1) SUPERVISOR_PERSON_ID
, NVL(ASG.PAYROLL_ID
, -1) PAYROLL_ID
, ASG.PAY_BASIS_ID PAY_BASIS_ID
, NVL(ASG.PAY_BASIS_ID
, -1) PEOPLE_GROUP_ID
, ASG.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_ID
, ASG.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_CODE
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_CODE
, AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_CODE
, NVL(ASG.RECRUITER_ID
, -1) RECRUITER_ID
, NVL(ASG.SET_OF_BOOKS_ID
, -1) SET_OF_BOOKS_ID
, ASG.PERIOD_OF_SERVICE_ID PERIOD_OF_SERVICE_ID
, ASG.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, ASG.PRIMARY_FLAG ASG_PRIMARY_FLAG_CODE
, GREATEST(WMV.LAST_CHANGE_DATE
, ASG.LAST_UPDATE_DATE
, AST.LAST_UPDATE_DATE ) LAST_CHANGE_DATE
, ASG.EFFECTIVE_START_DATE ASG_EFFECTIVE_START_DATE
, ASG.EFFECTIVE_END_DATE ASG_EFFECTIVE_END_DATE
, WMV.EFFECTIVE_START_DATE WMV_EFFECTIVE_START_DATE
, WMV.EFFECTIVE_END_DATE WMV_EFFECTIVE_END_DATE
FROM HRI_MB_WMV WMV
, PER_ALL_ASSIGNMENTS_F ASG
, PER_ASSIGNMENT_STATUS_TYPES AST
WHERE AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'SUSP_ASSIGN')
AND ASG.ASSIGNMENT_ID = WMV.ASSIGNMENT_ID
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND ( (ASG.EFFECTIVE_START_DATE BETWEEN WMV.EFFECTIVE_START_DATE
AND WMV.EFFECTIVE_END_DATE) OR (WMV.EFFECTIVE_START_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE) ) WITH READ ONLY