VIEW: APPS.PER_PAY_PROPOSALS_V

Source

View Text - Preformatted

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'))

View Text - HTML Formatted

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'))