View: HRFV_SALARIES

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

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PPB.PAY_ANNUALIZATION_FACTOR * PRO.PROPOSED_SALARY_N ANNUAL_SALARY
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PRO.CHANGE_DATE SALARY_CHANGE_DATE
, PRO.PROPOSED_SALARY_N SALARY_AMOUNT
, TO_NUMBER(NULL) ABSOLUTE_INCREMENT
, TO_NUMBER(NULL) PERCENTAGE_INCREMENT
, DECODE(PRO.PROPOSAL_REASON
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('PROPOSAL_REASON'
, PRO.PROPOSAL_REASON)) SALARY_CHANGE_REASON
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(PEE.ELEMENT_ENTRY_ID
, NULL
, 'N'
, 'Y')) APPROVED_FLAG
, NVL(EVT.DATE_START
, REV.REVIEW_DATE) PERFORMANCE_REVIEW_DATE
, DECODE(REV.PERFORMANCE_RATING
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('PERFORMANCE_RATING'
, REV.PERFORMANCE_RATING)) PERFORMANCE_RATING
, ASG.NORMAL_HOURS WORKING_HOURS_AMOUNT
, ASG.FREQUENCY WORKING_HOURS_FREQUENCY
, TO_NUMBER(INF.ORG_INFORMATION3) ORGANIZATION_WORKING_HOURS
, POS.WORKING_HOURS POSITION_WORKING_HOURS
, INF.ORG_INFORMATION4 ORG_WORKING_HOURS_FREQUENCY
, POS.FREQUENCY POS_WORKING_HOURS_FREQUENCY
, TPT.NUMBER_PER_FISCAL_YEAR NUMBER_PER_FISCAL_YEAR
, HR_BIS.BIS_DECODE_LOOKUP('PAY_BASIS'
, PPB.PAY_BASIS) PAY_BASIS
, TO_CHAR(NULL) PREV_PAY_BASIS
, PPB.RATE_BASIS GRADE_RATE_BASIS
, PRO.FORCED_RANKING RANKING
, PET.INPUT_CURRENCY_CODE CURRENCY_CODE
, HR_BIS.BIS_DECODE_LOOKUP('UOM'
, PIV.UOM) SALARY_AMOUNT_UNIT_OF_MEASURE
, PER_VIEWS_PKG.PER_CALC_COMPARATIO( ASG.ASSIGNMENT_ID
, PRO.CHANGE_DATE
, PRO.PROPOSED_SALARY_N
, PEE.ELEMENT_ENTRY_ID
, ASG.NORMAL_HOURS
, TO_NUMBER(INF.ORG_INFORMATION3)
, TO_NUMBER(POS.WORKING_HOURS)
, INF.ORG_INFORMATION4
, POS.FREQUENCY
, TPT.NUMBER_PER_FISCAL_YEAR
, ASG.GRADE_ID
, PPB.RATE_ID
, PPB.PAY_BASIS
, PPB.RATE_BASIS
, ASG.BUSINESS_GROUP_ID) GRADE_COMPARATIO
, SER.DATE_START HIRE_DATE
, ROUND(MONTHS_BETWEEN(PEE.EFFECTIVE_START_DATE
, SER.DATE_START)/12) YEARS_SERVICE
, DECODE(PRO.MULTIPLE_COMPONENTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PRO.MULTIPLE_COMPONENTS)) MULTIPLE_COMPONENTS
, ORGT.NAME ORGANIZATION_NAME
, JOB.NAME JOB_NAME
, POS.NAME POSITION_NAME
, GRD.NAME GRADE_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, PRO.NEXT_SAL_REVIEW_DATE NEXT_SALARY_REVIEW_DATE
, PPB.NAME SALARY_BASES
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, NULL
, 'N'
, 'Y')) CURRENT_EMPLOYEE_FLAG
, PET.OUTPUT_CURRENCY_CODE OUTPUT_CURRENCY_CODE
, '_DF:PER:PER_PAY_PROPOSALS:PRO'
, '_DF:PAY:PAY_ELEMENT_ENTRIES:PEE'
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEE.ELEMENT_ENTRY_ID ELEMENT_ENTRY_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, ASG.JOB_ID JOB_ID
, ASG.POSITION_ID POSITION_ID
, ASG.GRADE_ID GRADE_ID
, ASG.LOCATION_ID LOCATION_ID
, PRO.PAY_PROPOSAL_ID PAY_PROPOSAL_ID
, PRO.PERFORMANCE_REVIEW_ID PERFORMANCE_REVIEW_ID
, PEO.PERSON_ID PERSON_ID
, PPB.RATE_ID RATE_ID
FROM PER_EVENTS EVT
, PAY_ALL_PAYROLLS_F PAY
, PER_TIME_PERIOD_TYPES TPT
, PER_ALL_POSITIONS POS
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PERFORMANCE_REVIEWS REV
, PER_JOBS JOB
, PER_GRADES GRD
, HR_LOCATIONS_ALL_TL LOCT
, HR_ORGANIZATION_INFORMATION INF
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PER_PAY_BASES PPB
, PER_ASSIGNMENTS_F ASG
, PER_PEOPLE_F PEO
, PER_PERSON_TYPES PPT
, PAY_ELEMENT_ENTRIES_F PEE
, PER_PAY_PROPOSALS PRO
, PER_PERIODS_OF_SERVICE SER
WHERE PRO.EVENT_ID = EVT.EVENT_ID(+)
AND PAY.PERIOD_TYPE = TPT.PERIOD_TYPE(+)
AND ASG.PAYROLL_ID = PAY.PAYROLL_ID(+)
AND ASG.PERSON_ID = PEO.PERSON_ID
AND PEO.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE IN ('EMP'
, 'EMP_APL'
, 'EX_EMP'
, 'EX_EMP_APL')
AND ASG.EFFECTIVE_START_DATE BETWEEN PAY.EFFECTIVE_START_DATE(+)
AND PAY.EFFECTIVE_END_DATE(+)
AND ASG.POSITION_ID = POS.POSITION_ID(+)
AND ASG.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND ASG.JOB_ID = JOB.JOB_ID(+)
AND ASG.GRADE_ID = GRD.GRADE_ID(+)
AND ASG.LOCATION_ID = LOCT.LOCATION_ID(+)
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND PRO.PERFORMANCE_REVIEW_ID = REV.PERFORMANCE_REVIEW_ID (+)
AND ASG.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASG.ORGANIZATION_ID = INF.ORGANIZATION_ID(+)
AND INF.ORG_INFORMATION_CONTEXT (+) = 'WORK DAY INFORMATION'
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID(+)
AND PIV.EFFECTIVE_START_DATE BETWEEN PET.EFFECTIVE_START_DATE(+)
AND PET.EFFECTIVE_END_DATE(+)
AND PIV.INPUT_VALUE_ID(+) = PPB.INPUT_VALUE_ID
AND (PRO.CHANGE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE OR PPB.PAY_BASIS_ID IS NULL)
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID(+)
AND (PRO.CHANGE_DATE IS NULL OR NOT EXISTS (SELECT 1
FROM PAY_ELEMENT_ENTRY_VALUES_F PEV2
, PAY_ELEMENT_ENTRIES_F PEE2
, PER_PAY_BASES PPB2
, PER_ASSIGNMENTS_F ASG2
WHERE ASG2.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND ASG2.PAY_BASIS_ID = PPB2.PAY_BASIS_ID
AND PEE2.ASSIGNMENT_ID = ASG2.ASSIGNMENT_ID
AND PEE2.ENTRY_TYPE = 'E'
AND PEV2.ELEMENT_ENTRY_ID = PEE2.ELEMENT_ENTRY_ID
AND PEV2.INPUT_VALUE_ID = PPB2.INPUT_VALUE_ID
AND PEV2.EFFECTIVE_START_DATE = PEE2.EFFECTIVE_START_DATE
AND PRO.CHANGE_DATE - 1 BETWEEN ASG2.EFFECTIVE_START_DATE
AND ASG2.EFFECTIVE_END_DATE
AND PRO.CHANGE_DATE - 1 BETWEEN PEE2.EFFECTIVE_START_DATE
AND PEE2.EFFECTIVE_END_DATE ) )
AND PEE.CREATOR_TYPE(+) = 'SP'
AND PEE.CREATOR_ID(+) = PRO.PAY_PROPOSAL_ID
AND PEE.EFFECTIVE_START_DATE(+) = PRO.CHANGE_DATE
AND PEE.ASSIGNMENT_ID(+) = PRO.ASSIGNMENT_ID
AND PRO.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND NVL(PRO.CHANGE_DATE
, NVL(PRO.REVIEW_DATE
, EVT.DATE_START)) BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND NVL(PRO.CHANGE_DATE
, NVL(PRO.REVIEW_DATE
, EVT.DATE_START)) BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND SER.PERIOD_OF_SERVICE_ID = ASG.PERIOD_OF_SERVICE_ID
AND ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) UNION ALL SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PPB.PAY_ANNUALIZATION_FACTOR * PRO.PROPOSED_SALARY_N ANNUAL_SALARY
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PRO.CHANGE_DATE SALARY_CHANGE_DATE
, PRO.PROPOSED_SALARY_N SALARY_AMOUNT
, TO_NUMBER(DECODE(PPB.PAY_BASIS
, PPB2.PAY_BASIS
, TO_CHAR(PRO.PROPOSED_SALARY_N - TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE))
, NULL)) ABSOLUTE_INCREMENT
, TO_NUMBER(DECODE(PPB.PAY_BASIS
, PPB2.PAY_BASIS
, TO_CHAR(ROUND(((PRO.PROPOSED_SALARY_N - TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE))/ DECODE(TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE)
, 0
, NULL
, TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE))) * 100
, 2))
, NULL)) PERCENTAGE_INCREMENT
, DECODE(PRO.PROPOSAL_REASON
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('PROPOSAL_REASON'
, PRO.PROPOSAL_REASON)) SALARY_CHANGE_REASON
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(PEE.ELEMENT_ENTRY_ID
, NULL
, 'N'
, 'Y')) APPROVED_FLAG
, NVL(EVT.DATE_START
, PRO.REVIEW_DATE) PERFORMANCE_REVIEW_DATE
, DECODE(REV.PERFORMANCE_RATING
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('PERFORMANCE_RATING'
, REV.PERFORMANCE_RATING)) PERFORMANCE_RATING
, ASG.NORMAL_HOURS WORKING_HOURS_AMOUNT
, ASG.FREQUENCY WORKING_HOURS_FREQUENCY
, TO_NUMBER(INF.ORG_INFORMATION3) ORGANIZATION_WORKING_HOURS
, POS.WORKING_HOURS POSITION_WORKING_HOURS
, INF.ORG_INFORMATION4 ORG_WORKING_HOURS_FREQUENCY
, POS.FREQUENCY POS_WORKING_HOURS_FREQUENCY
, TPT.NUMBER_PER_FISCAL_YEAR NUMBER_PER_FISCAL_YEAR
, HR_BIS.BIS_DECODE_LOOKUP('PAY_BASIS'
, PPB.PAY_BASIS) PAY_BASIS
, HR_BIS.BIS_DECODE_LOOKUP('PAY_BASIS'
, PPB2.PAY_BASIS) PREV_PAY_BASIS
, PPB.RATE_BASIS GRADE_RATE_BASIS
, PRO.FORCED_RANKING RANKING
, PET.INPUT_CURRENCY_CODE CURRENCY_CODE
, HR_BIS.BIS_DECODE_LOOKUP('UOM'
, PIV.UOM) SALARY_AMOUNT_UNIT_OF_MEASURE
, PER_VIEWS_PKG.PER_CALC_COMPARATIO (ASG.ASSIGNMENT_ID
, PRO.CHANGE_DATE
, PRO.PROPOSED_SALARY_N
, PEE.ELEMENT_ENTRY_ID
, ASG.NORMAL_HOURS
, TO_NUMBER(INF.ORG_INFORMATION3)
, TO_NUMBER(POS.WORKING_HOURS)
, INF.ORG_INFORMATION4
, POS.FREQUENCY
, TPT.NUMBER_PER_FISCAL_YEAR
, ASG.GRADE_ID
, PPB.RATE_ID
, PPB.PAY_BASIS
, PPB.RATE_BASIS
, ASG.BUSINESS_GROUP_ID) GRADE_COMPARATIO
, SER.DATE_START HIRE_DATE
, ROUND(MONTHS_BETWEEN(PEE.EFFECTIVE_START_DATE
, SER.DATE_START)/12) YEARS_SERVICE
, DECODE(PRO.MULTIPLE_COMPONENTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PRO.MULTIPLE_COMPONENTS)) MULTIPLE_COMPONENTS
, ORGT.NAME ORGANIZATION_NAME
, JOB.NAME JOB_NAME
, POS.NAME POSITION_NAME
, GRD.NAME GRADE_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, PRO.NEXT_SAL_REVIEW_DATE NEXT_SALARY_REVIEW_DATE
, PPB.NAME SALARY_BASES
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, NULL
, 'N'
, 'Y')) CURRENT_EMPLOYEE_FLAG
, PET.OUTPUT_CURRENCY_CODE OUTPUT_CURRENCY_CODE
, '_DF:PER:PER_PAY_PROPOSALS:PRO'
, '_DF:PAY:PAY_ELEMENT_ENTRIES:PEE'
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEE.ELEMENT_ENTRY_ID ELEMENT_ENTRY_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, ASG.JOB_ID JOB_ID
, ASG.POSITION_ID POSITION_ID
, ASG.GRADE_ID GRADE_ID
, ASG.LOCATION_ID LOCATION_ID
, PRO.PAY_PROPOSAL_ID PAY_PROPOSAL_ID
, PRO.PERFORMANCE_REVIEW_ID PERFORMANCE_REVIEW_ID
, PEO.PERSON_ID PERSON_ID
, PPB.RATE_ID RATE_ID
FROM PER_EVENTS EVT
, PAY_ALL_PAYROLLS_F PAY
, PER_TIME_PERIOD_TYPES TPT
, PER_ALL_POSITIONS POS
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PERFORMANCE_REVIEWS REV
, PER_JOBS JOB
, PER_GRADES GRD
, HR_LOCATIONS_ALL_TL LOCT
, HR_ORGANIZATION_INFORMATION INF
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PER_PAY_BASES PPB
, PER_PAY_BASES PPB2
, PAY_ELEMENT_ENTRY_VALUES_F PEV2
, PAY_ELEMENT_ENTRIES_F PEE2
, PAY_ELEMENT_ENTRIES_F PEE
, PER_ASSIGNMENTS_F ASG2
, PER_ASSIGNMENTS_F ASG
, PER_PEOPLE_F PEO
, PER_PERSON_TYPES PPT
, PER_PAY_PROPOSALS PRO
, PER_PERIODS_OF_SERVICE SER
WHERE PRO.EVENT_ID = EVT.EVENT_ID(+)
AND PAY.PERIOD_TYPE = TPT.PERIOD_TYPE(+)
AND ASG.PAYROLL_ID = PAY.PAYROLL_ID(+)
AND ASG.PERSON_ID = PEO.PERSON_ID
AND PEO.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE IN ('EMP'
, 'EMP_APL'
, 'EX_EMP'
, 'EX_EMP_APL')
AND ASG.EFFECTIVE_START_DATE BETWEEN PAY.EFFECTIVE_START_DATE(+)
AND PAY.EFFECTIVE_END_DATE(+)
AND ASG.POSITION_ID = POS.POSITION_ID(+)
AND ASG.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND ASG.JOB_ID = JOB.JOB_ID(+)
AND ASG.GRADE_ID = GRD.GRADE_ID(+)
AND ASG.LOCATION_ID = LOCT.LOCATION_ID(+)
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND PRO.PERFORMANCE_REVIEW_ID = REV.PERFORMANCE_REVIEW_ID (+)
AND ASG.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASG.ORGANIZATION_ID = INF.ORGANIZATION_ID(+)
AND INF.ORG_INFORMATION_CONTEXT (+) = 'WORK DAY INFORMATION'
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PIV.EFFECTIVE_START_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PPB.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PRO.CHANGE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PEV2.INPUT_VALUE_ID = PPB2.INPUT_VALUE_ID
AND PEV2.ELEMENT_ENTRY_ID = PEE2.ELEMENT_ENTRY_ID
AND PEV2.EFFECTIVE_START_DATE = PEE2.EFFECTIVE_START_DATE
AND ASG2.PAY_BASIS_ID = PPB2.PAY_BASIS_ID
AND ASG2.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND PRO.CHANGE_DATE - 1 BETWEEN ASG2.EFFECTIVE_START_DATE
AND ASG2.EFFECTIVE_END_DATE
AND PEE2.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND PEE2.ENTRY_TYPE = 'E'
AND PRO.CHANGE_DATE - 1 BETWEEN PEE2.EFFECTIVE_START_DATE
AND PEE2.EFFECTIVE_END_DATE
AND PEE.CREATOR_TYPE(+) = 'SP'
AND PEE.CREATOR_ID(+) = PRO.PAY_PROPOSAL_ID
AND PEE.EFFECTIVE_START_DATE(+) = PRO.CHANGE_DATE
AND PEE.ASSIGNMENT_ID(+) = PRO.ASSIGNMENT_ID
AND PRO.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND PRO.CHANGE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND PRO.CHANGE_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND SER.PERIOD_OF_SERVICE_ID = ASG.PERIOD_OF_SERVICE_ID
AND ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) WITH READ ONLY

Columns