View: PER_PAY_PROPOSALS_V

Product:	PER - Human Resources
Description:	Employee salary proposal and performance review information.
Implementation/DBA Data:	APPS.PER_PAY_PROPOSALS_V

View Text

SELECT /* $HEADER: PEGENHRV.SQL 70.1 96/07/26 11:54:43 JRHODES SHIP $ */ PRO.ROWID
, PRO.PAY_PROPOSAL_ID
, PRO.BUSINESS_GROUP_ID + 0
, PRO.ASSIGNMENT_ID
, NVL(EVT.DATE_START
, PRO.REVIEW_DATE)
, TO_CHAR(PRO.PROPOSED_SALARY_N)
, TO_CHAR(NULL)
, TO_CHAR (NULL)
, TO_CHAR(NULL)
, PRO.CHANGE_DATE
, PRO.PROPOSAL_REASON
, HLU.MEANING
, PRO.LAST_CHANGE_DATE
, PRO.NEXT_SAL_REVIEW_DATE
, PEE.ELEMENT_ENTRY_ID
, PRO.EVENT_ID
, HLU3.MEANING
, LOC.LOCATION_CODE
, PRO.PERFORMANCE_RATING
, HLU2.MEANING
, PRO.NEXT_PERF_REVIEW_DATE
, PRO.ATTRIBUTE_CATEGORY
, PRO.ATTRIBUTE1
, PRO.ATTRIBUTE2
, PRO.ATTRIBUTE3
, PRO.ATTRIBUTE4
, PRO.ATTRIBUTE5
, PRO.ATTRIBUTE6
, PRO.ATTRIBUTE7
, PRO.ATTRIBUTE8
, PRO.ATTRIBUTE9
, PRO.ATTRIBUTE10
, PRO.ATTRIBUTE11
, PRO.ATTRIBUTE12
, PRO.ATTRIBUTE13
, PRO.ATTRIBUTE14
, PRO.ATTRIBUTE15
, PRO.ATTRIBUTE16
, PRO.ATTRIBUTE17
, PRO.ATTRIBUTE18
, PRO.ATTRIBUTE19
, PRO.ATTRIBUTE20
, PRO.MULTIPLE_COMPONENTS
, PRO.APPROVED
, PRO.OBJECT_VERSION_NUMBER
, PET.ELEMENT_TYPE_ID
, PETTL.ELEMENT_NAME
, PIV.INPUT_VALUE_ID
, PIV.UOM
, PET.OUTPUT_CURRENCY_CODE
, PET.INPUT_CURRENCY_CODE
, PPB.PAY_BASIS
, HR_GENERAL.DECODE_LOOKUP('PAY_BASIS'
, PPB.PAY_BASIS)
, PPB.RATE_BASIS
, PPB.ANNUALIZED_HOURS
, TO_CHAR(NULL)
, PRO.LAST_UPDATE_DATE
, PRO.LAST_UPDATED_BY
, PRO.LAST_UPDATE_LOGIN
, PRO.CREATED_BY
, PRO.CREATION_DATE
FROM HR_LOOKUPS HLU
, HR_LOOKUPS HLU2
, HR_LOOKUPS HLU3
, HR_LOCATIONS LOC
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_INPUT_VALUES_F PIV
, PER_PAY_BASES PPB
, PAY_ELEMENT_ENTRIES_F PEE
, PER_EVENTS EVT
, PER_ALL_ASSIGNMENTS_F ASG
, PER_PAY_PROPOSALS PRO
WHERE HLU.LOOKUP_CODE(+) = PRO.PROPOSAL_REASON
AND HLU.LOOKUP_TYPE (+) = 'PROPOSAL_REASON'
AND HLU2.LOOKUP_CODE(+) = PRO.PERFORMANCE_RATING
AND HLU2.LOOKUP_TYPE (+) = 'PERFORMANCE_RATING'
AND HLU3.LOOKUP_CODE(+) = EVT.TYPE
AND HLU3.LOOKUP_TYPE(+) = 'EMP_INTERVIEW_TYPE'
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 PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID (+)
AND DECODE(PETTL.ELEMENT_TYPE_ID
, NULL
, '1'
, PETTL.LANGUAGE) = DECODE (PETTL.ELEMENT_TYPE_ID
, NULL
, '1'
, USERENV('LANG'))
AND PIV.INPUT_VALUE_ID(+) = PPB.INPUT_VALUE_ID
AND ASG.PAY_BASIS_ID + 0 = PPB.PAY_BASIS_ID(+)
AND ( NVL(PRO.CHANGE_DATE
, PIV.EFFECTIVE_START_DATE) BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE OR PPB.PAY_BASIS_ID IS NULL )
AND EVT.LOCATION_ID = LOC.LOCATION_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_ALL_ASSIGNMENTS_F ASG2
WHERE PEV2.INPUT_VALUE_ID + 0 = 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 + 0 = 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.CREATOR_TYPE = 'SP'
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.ASSIGNMENT_ID(+) = PRO.ASSIGNMENT_ID
AND PEE.EFFECTIVE_START_DATE(+) = PRO.CHANGE_DATE
AND PRO.EVENT_ID = EVT.EVENT_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 PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID (+)
AND DECODE (PETTL.ELEMENT_TYPE_ID
, NULL
, '1'
, PETTL.LANGUAGE) = DECODE( PETTL.ELEMENT_TYPE_ID
, NULL
, '1'
, USERENV('LANG')) UNION SELECT PRO.ROWID
, PRO.PAY_PROPOSAL_ID
, PRO.BUSINESS_GROUP_ID + 0
, PRO.ASSIGNMENT_ID
, NVL(EVT.DATE_START
, PRO.REVIEW_DATE)
, TO_CHAR(PRO.PROPOSED_SALARY_N)
, DECODE(PEE2.ELEMENT_ENTRY_ID
, DECODE(PEE.ELEMENT_ENTRY_ID
, NULL
, PEE2.ELEMENT_ENTRY_ID
, PEE.ELEMENT_ENTRY_ID)
, PEV2.SCREEN_ENTRY_VALUE
, NULL)
, DECODE(PEE2.ELEMENT_ENTRY_ID
, DECODE(PEE.ELEMENT_ENTRY_ID
, NULL
, PEE2.ELEMENT_ENTRY_ID
, PEE.ELEMENT_ENTRY_ID)
, TO_CHAR (PRO.PROPOSED_SALARY_N -TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE) )
, NULL)
, DECODE(PEE2.ELEMENT_ENTRY_ID
, DECODE(PEE.ELEMENT_ENTRY_ID
, NULL
, PEE2.ELEMENT_ENTRY_ID
, PEE.ELEMENT_ENTRY_ID)
, 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
, 3))
, NULL)
, PRO.CHANGE_DATE
, PRO.PROPOSAL_REASON
, HLU.MEANING
, PRO.LAST_CHANGE_DATE
, PRO.NEXT_SAL_REVIEW_DATE
, PEE.ELEMENT_ENTRY_ID
, PRO.EVENT_ID
, HLU3.MEANING
, LOC.LOCATION_CODE
, PRO.PERFORMANCE_RATING
, HLU2.MEANING
, PRO.NEXT_PERF_REVIEW_DATE
, PRO.ATTRIBUTE_CATEGORY
, PRO.ATTRIBUTE1
, PRO.ATTRIBUTE2
, PRO.ATTRIBUTE3
, PRO.ATTRIBUTE4
, PRO.ATTRIBUTE5
, PRO.ATTRIBUTE6
, PRO.ATTRIBUTE7
, PRO.ATTRIBUTE8
, PRO.ATTRIBUTE9
, PRO.ATTRIBUTE10
, PRO.ATTRIBUTE11
, PRO.ATTRIBUTE12
, PRO.ATTRIBUTE13
, PRO.ATTRIBUTE14
, PRO.ATTRIBUTE15
, PRO.ATTRIBUTE16
, PRO.ATTRIBUTE17
, PRO.ATTRIBUTE18
, PRO.ATTRIBUTE19
, PRO.ATTRIBUTE20
, PRO.MULTIPLE_COMPONENTS
, PRO.APPROVED
, PRO.OBJECT_VERSION_NUMBER
, PET.ELEMENT_TYPE_ID
, PETTL.ELEMENT_NAME
, PIV.INPUT_VALUE_ID
, PIV.UOM
, PET.OUTPUT_CURRENCY_CODE
, PET.INPUT_CURRENCY_CODE
, PPB.PAY_BASIS
, HR_GENERAL.DECODE_LOOKUP('PAY_BASIS'
, PPB.PAY_BASIS)
, PPB.RATE_BASIS
, PPB.ANNUALIZED_HOURS
, PPB2.PAY_BASIS
, PRO.LAST_UPDATE_DATE
, PRO.LAST_UPDATED_BY
, PRO.LAST_UPDATE_LOGIN
, PRO.CREATED_BY
, PRO.CREATION_DATE
FROM HR_LOOKUPS HLU
, HR_LOOKUPS HLU2
, HR_LOOKUPS HLU3
, HR_LOCATIONS LOC
, PER_EVENTS EVT
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_INPUT_VALUES_F PIV
, PER_PAY_BASES PPB
, PER_PAY_BASES PPB2
, PAY_ELEMENT_ENTRY_VALUES_F PEV2
, PAY_ELEMENT_ENTRIES_F PEE2
, PER_ASSIGNMENTS_F ASG2
, PAY_ELEMENT_ENTRIES_F PEE
, PER_ALL_ASSIGNMENTS_F ASG
, PER_PAY_PROPOSALS PRO
WHERE HLU.LOOKUP_CODE(+) = PRO.PROPOSAL_REASON
AND HLU.LOOKUP_TYPE (+) = 'PROPOSAL_REASON'
AND HLU2.LOOKUP_CODE(+) = PRO.PERFORMANCE_RATING
AND HLU2.LOOKUP_TYPE (+) = 'PERFORMANCE_RATING'
AND HLU3.LOOKUP_CODE(+) = EVT.TYPE
AND HLU3.LOOKUP_TYPE(+) = 'EMP_INTERVIEW_TYPE'
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 + 0 = PPB.PAY_BASIS_ID
AND EVT.LOCATION_ID = LOC.LOCATION_ID(+)
AND PRO.EVENT_ID = EVT.EVENT_ID(+)
AND PEV2.INPUT_VALUE_ID + 0 = 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 + 0 = PPB2.PAY_BASIS_ID
AND PEE2.CREATOR_TYPE = 'SP'
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.ASSIGNMENT_ID(+) = PRO.ASSIGNMENT_ID
AND PEE.EFFECTIVE_START_DATE(+) = PRO.CHANGE_DATE
AND PRO.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND PRO.CHANGE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND DECODE(PETTL.ELEMENT_TYPE_ID
, NULL
, '1'
, PETTL.LANGUAGE) = DECODE (PETTL.ELEMENT_TYPE_ID
, NULL
, '1'
, USERENV('LANG'))

Columns