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.HRFV_MOVEMENTS_SUMMARY

Source

View Text - Preformatted


SELECT bgrT.name business_group_name ,hr_bis.bis_decode_lookup('BUDGET_MEASUREMENT_TYPE',hrl.lookup_code) budget_measurement_type ,hrl.lookup_code budget_measurement_code ,houT.name organization_name ,ppos.date_start movement_date ,'Hire' movement_type ,paf.assignment_id assignment_id ,paf.business_group_id business_group_id ,paf.organization_id organization_id FROM hr_all_organization_units_tl houT ,per_periods_of_service ppos ,hr_all_organization_units_tl bgrT ,per_assignments_f paf ,hr_lookups hrl WHERE paf.period_of_service_id = ppos.period_of_service_id AND paf.assignment_type = 'E' AND paf.primary_flag = 'Y' AND paf.effective_start_date = ppos.date_start AND paf.organization_id = houT.organization_id AND houT.language = userenv('LANG') AND paf.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND hrl.lookup_type = 'BUDGET_MEASUREMENT_TYPE' AND paf.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,paf.business_group_id) UNION ALL SELECT bgrT.name business_group_name ,hr_bis.bis_decode_lookup('BUDGET_MEASUREMENT_TYPE',hrl.lookup_code) budget_measurement_type ,hrl.lookup_code budget_measurement_code ,houT.name organization_name ,ppos.actual_termination_date movement_date ,'Termination' movement_type ,paf.assignment_id assignment_id ,paf.business_group_id business_group_id ,paf.organization_id organization_id FROM hr_all_organization_units_tl houT ,hr_all_organization_units_tl bgrT ,per_periods_of_service ppos ,hr_lookups hrl ,per_assignments_f paf WHERE paf.period_of_service_id = ppos.period_of_service_id AND paf.organization_id = houT.organization_id AND houT.language = userenv('LANG') AND paf.assignment_type = 'E' AND paf.primary_flag = 'Y' AND paf.effective_end_date = ppos.actual_termination_date AND paf.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND hrl.lookup_type = 'BUDGET_MEASUREMENT_TYPE' AND paf.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,paf.business_group_id) UNION ALL SELECT bgrT.name business_group_name ,hr_bis.bis_decode_lookup('BUDGET_MEASUREMENT_TYPE',hrl.lookup_code) budget_measurement_type ,hrl.lookup_code budget_measurement_code ,houT.name organization_name ,paf.effective_start_date movement_date ,'Transfer In' movement_type ,paf.assignment_id assignment_id ,paf.business_group_id business_group_id ,paf.organization_id organization_id FROM per_periods_of_service ppos ,hr_all_organization_units_tl houT ,hr_all_organization_units_tl bgrT ,per_all_assignments_f paf ,per_assignments_f paf2 ,hr_lookups hrl WHERE paf.period_of_service_id = ppos.period_of_service_id AND paf.organization_id = houT.organization_id AND houT.language = userenv('LANG') AND paf.assignment_type = 'E' AND paf.primary_flag = 'Y' AND paf.effective_start_date <> ppos.date_start AND paf.assignment_id = paf2.assignment_id AND paf.effective_start_date-1 = paf2.effective_end_date AND paf.organization_id <> paf2.organization_id AND paf2.assignment_type = 'E' AND paf2.primary_flag = 'Y' AND paf.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND hrl.lookup_type = 'BUDGET_MEASUREMENT_TYPE' AND paf.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,paf.business_group_id) UNION ALL SELECT bgrT.name business_group_name ,hr_bis.bis_decode_lookup('BUDGET_MEASUREMENT_TYPE',hrl.lookup_code) budget_measurement_type ,hrl.lookup_code budget_measurement_code ,houT.name organization_name ,hr_discoverer.check_end_date(paf.effective_end_date) movement_date ,'Transfer Out' movement_type ,paf.assignment_id assignment_id ,paf.business_group_id business_group_id ,paf.organization_id organization_id FROM per_periods_of_service ppos ,hr_all_organization_units_tl houT ,hr_all_organization_units_tl bgrT ,per_assignment_status_types past ,per_assignments_f paf /*Asg in Org */ ,per_all_assignments_f paf2 /*Asg Out of Org*/ ,hr_lookups hrl WHERE paf.period_of_service_id = ppos.period_of_service_id AND paf.organization_id = houT.organization_id AND houT.language = userenv('LANG') AND paf.assignment_type = 'E' AND paf.primary_flag = 'Y' AND paf.assignment_status_type_id = past.assignment_status_type_id AND past.per_system_status = 'ACTIVE_ASSIGN' AND paf.assignment_id = paf2.assignment_id AND paf.effective_end_date + 1 = paf2.effective_start_date AND paf.organization_id <> paf2.organization_id AND paf2.assignment_type = 'E' AND paf2.primary_flag = 'Y' AND paf.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND hrl.lookup_type = 'BUDGET_MEASUREMENT_TYPE' AND paf.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,paf.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, HR_BIS.BIS_DECODE_LOOKUP('BUDGET_MEASUREMENT_TYPE'
, HRL.LOOKUP_CODE) BUDGET_MEASUREMENT_TYPE
, HRL.LOOKUP_CODE BUDGET_MEASUREMENT_CODE
, HOUT.NAME ORGANIZATION_NAME
, PPOS.DATE_START MOVEMENT_DATE
, 'HIRE' MOVEMENT_TYPE
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PAF.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PAF.ORGANIZATION_ID ORGANIZATION_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL HOUT
, PER_PERIODS_OF_SERVICE PPOS
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_ASSIGNMENTS_F PAF
, HR_LOOKUPS HRL
WHERE PAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.EFFECTIVE_START_DATE = PPOS.DATE_START
AND PAF.ORGANIZATION_ID = HOUT.ORGANIZATION_ID
AND HOUT.LANGUAGE = USERENV('LANG')
AND PAF.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND HRL.LOOKUP_TYPE = 'BUDGET_MEASUREMENT_TYPE'
AND PAF.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PAF.BUSINESS_GROUP_ID) UNION ALL SELECT BGRT.NAME BUSINESS_GROUP_NAME
, HR_BIS.BIS_DECODE_LOOKUP('BUDGET_MEASUREMENT_TYPE'
, HRL.LOOKUP_CODE) BUDGET_MEASUREMENT_TYPE
, HRL.LOOKUP_CODE BUDGET_MEASUREMENT_CODE
, HOUT.NAME ORGANIZATION_NAME
, PPOS.ACTUAL_TERMINATION_DATE MOVEMENT_DATE
, 'TERMINATION' MOVEMENT_TYPE
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PAF.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PAF.ORGANIZATION_ID ORGANIZATION_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL HOUT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PERIODS_OF_SERVICE PPOS
, HR_LOOKUPS HRL
, PER_ASSIGNMENTS_F PAF
WHERE PAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PAF.ORGANIZATION_ID = HOUT.ORGANIZATION_ID
AND HOUT.LANGUAGE = USERENV('LANG')
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.EFFECTIVE_END_DATE = PPOS.ACTUAL_TERMINATION_DATE
AND PAF.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND HRL.LOOKUP_TYPE = 'BUDGET_MEASUREMENT_TYPE'
AND PAF.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PAF.BUSINESS_GROUP_ID) UNION ALL SELECT BGRT.NAME BUSINESS_GROUP_NAME
, HR_BIS.BIS_DECODE_LOOKUP('BUDGET_MEASUREMENT_TYPE'
, HRL.LOOKUP_CODE) BUDGET_MEASUREMENT_TYPE
, HRL.LOOKUP_CODE BUDGET_MEASUREMENT_CODE
, HOUT.NAME ORGANIZATION_NAME
, PAF.EFFECTIVE_START_DATE MOVEMENT_DATE
, 'TRANSFER IN' MOVEMENT_TYPE
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PAF.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PAF.ORGANIZATION_ID ORGANIZATION_ID
FROM PER_PERIODS_OF_SERVICE PPOS
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_ALL_ASSIGNMENTS_F PAF
, PER_ASSIGNMENTS_F PAF2
, HR_LOOKUPS HRL
WHERE PAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PAF.ORGANIZATION_ID = HOUT.ORGANIZATION_ID
AND HOUT.LANGUAGE = USERENV('LANG')
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.EFFECTIVE_START_DATE <> PPOS.DATE_START
AND PAF.ASSIGNMENT_ID = PAF2.ASSIGNMENT_ID
AND PAF.EFFECTIVE_START_DATE-1 = PAF2.EFFECTIVE_END_DATE
AND PAF.ORGANIZATION_ID <> PAF2.ORGANIZATION_ID
AND PAF2.ASSIGNMENT_TYPE = 'E'
AND PAF2.PRIMARY_FLAG = 'Y'
AND PAF.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND HRL.LOOKUP_TYPE = 'BUDGET_MEASUREMENT_TYPE'
AND PAF.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PAF.BUSINESS_GROUP_ID) UNION ALL SELECT BGRT.NAME BUSINESS_GROUP_NAME
, HR_BIS.BIS_DECODE_LOOKUP('BUDGET_MEASUREMENT_TYPE'
, HRL.LOOKUP_CODE) BUDGET_MEASUREMENT_TYPE
, HRL.LOOKUP_CODE BUDGET_MEASUREMENT_CODE
, HOUT.NAME ORGANIZATION_NAME
, HR_DISCOVERER.CHECK_END_DATE(PAF.EFFECTIVE_END_DATE) MOVEMENT_DATE
, 'TRANSFER OUT' MOVEMENT_TYPE
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PAF.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PAF.ORGANIZATION_ID ORGANIZATION_ID
FROM PER_PERIODS_OF_SERVICE PPOS
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_ASSIGNMENT_STATUS_TYPES PAST
, PER_ASSIGNMENTS_F PAF /*ASG IN ORG */
, PER_ALL_ASSIGNMENTS_F PAF2 /*ASG OUT OF ORG*/
, HR_LOOKUPS HRL
WHERE PAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PAF.ORGANIZATION_ID = HOUT.ORGANIZATION_ID
AND HOUT.LANGUAGE = USERENV('LANG')
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
AND PAST.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
AND PAF.ASSIGNMENT_ID = PAF2.ASSIGNMENT_ID
AND PAF.EFFECTIVE_END_DATE + 1 = PAF2.EFFECTIVE_START_DATE
AND PAF.ORGANIZATION_ID <> PAF2.ORGANIZATION_ID
AND PAF2.ASSIGNMENT_TYPE = 'E'
AND PAF2.PRIMARY_FLAG = 'Y'
AND PAF.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND HRL.LOOKUP_TYPE = 'BUDGET_MEASUREMENT_TYPE'
AND PAF.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PAF.BUSINESS_GROUP_ID) WITH READ ONLY