View: HRFV_MOVEMENTS_SUMMARY

Product:	PER - Human Resources
Description:	Business view template from which the flexfield view is generated.
Implementation/DBA Data:	APPS.HRFV_MOVEMENTS_SUMMARY

View Text

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

Columns