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

Source

View Text - Preformatted


SELECT ASG.ROWID , ASG.ASSIGNMENT_ID , PEE.EFFECTIVE_START_DATE , PEV.SCREEN_ENTRY_VALUE , DECODE(PEE2.ELEMENT_ENTRY_ID , DECODE(PEE.ELEMENT_ENTRY_ID, NULL, PEE2.ELEMENT_ENTRY_ID, PEE.ELEMENT_ENTRY_ID) , TO_CHAR (TO_NUMBER (PEV.SCREEN_ENTRY_VALUE)- 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 (((TO_NUMBER(PEV.SCREEN_ENTRY_VALUE) - TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE)) / DECODE(TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE) , 0 , NULL , TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE))) * 100 , 2)), NULL) , TO_CHAR(NULL) , PEE.ELEMENT_ENTRY_ID , DECODE(PEE.ELEMENT_ENTRY_ID, NULL, 'N', 'Y') , TO_NUMBER(NULL) , TO_DATE(NULL) , TO_CHAR(NULL) , ASG.NORMAL_HOURS , INF.ORG_INFORMATION3 , /* WORKING HOURS */ TO_CHAR( POS.WORKING_HOURS) , ASG.FREQUENCY , INF.ORG_INFORMATION4 , /* FREQUENCY */ POS.FREQUENCY , TPT.NUMBER_PER_FISCAL_YEAR , ASG.GRADE_ID , PPB.RATE_ID , PPB.PAY_BASIS , HR_GENERAL.DECODE_LOOKUP('PAY_BASIS', PPB.PAY_BASIS) , PPB2.PAY_BASIS , PPB.RATE_BASIS , PET.ELEMENT_TYPE_ID , PETTL.ELEMENT_NAME , PET.OUTPUT_CURRENCY_CODE , PET.INPUT_CURRENCY_CODE , PIV.UOM FROM PER_TIME_PERIOD_TYPES TPT, PAY_ALL_PAYROLLS_F PAY, HR_POSITIONS POS, HR_ORGANIZATION_INFORMATION INF, PAY_ELEMENT_ENTRY_VALUES_F PEV2, PAY_ELEMENT_ENTRY_VALUES_F PEV, PAY_ELEMENT_TYPES_F PET, PAY_ELEMENT_TYPES_F_TL PETTL, PAY_INPUT_VALUES_F PIV, PER_PAY_BASES PPB2, PER_PAY_BASES PPB, PER_ALL_ASSIGNMENTS_F ASG2, PER_ALL_ASSIGNMENTS_F ASG, PAY_ELEMENT_ENTRIES_F PEE2, PAY_ELEMENT_ENTRIES_F PEE, PER_PEOPLE_F PER WHERE ASG.PERSON_ID = PER.PERSON_ID AND NOT EXISTS (SELECT 1 FROM PER_PAY_PROPOSALS PRO WHERE PEE.CREATOR_TYPE = 'SP' AND PEE.CREATOR_ID = PRO.PAY_PROPOSAL_ID AND PEE.EFFECTIVE_START_DATE = PRO.CHANGE_DATE) AND PAY.PERIOD_TYPE = TPT.PERIOD_TYPE(+) AND ASG.PAYROLL_ID = PAY.PAYROLL_ID(+) AND ASG.EFFECTIVE_START_DATE BETWEEN PAY.EFFECTIVE_START_DATE(+) AND PAY.EFFECTIVE_END_DATE(+) AND ASG.POSITION_ID = POS.POSITION_ID(+) AND asg.effective_start_date between POS.EFFECTIVE_START_DATE(+) AND POS.EFFECTIVE_END_DATE(+) AND ASG.ORGANIZATION_ID + 0 = INF.ORGANIZATION_ID(+) AND INF.ORG_INFORMATION_CONTEXT (+) || '' = 'Work Day Information' 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 IS NULL OR ASG2.PAY_BASIS_ID + 0 = PPB2.PAY_BASIS_ID) AND ASG2.ASSIGNMENT_ID(+) = PEE.ASSIGNMENT_ID AND PEE.EFFECTIVE_START_DATE - 1 BETWEEN ASG2.EFFECTIVE_START_DATE(+) AND ASG2.EFFECTIVE_END_DATE(+) AND PEE2.ASSIGNMENT_ID(+) = PEE.ASSIGNMENT_ID AND PEE.EFFECTIVE_START_DATE - 1 BETWEEN PEE2.EFFECTIVE_END_DATE(+) AND PEE2.EFFECTIVE_END_DATE(+) 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 PEV.INPUT_VALUE_ID + 0 = PIV.INPUT_VALUE_ID AND PEV.EFFECTIVE_START_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE AND PEV.INPUT_VALUE_ID + 0 = PPB.INPUT_VALUE_ID AND PEV.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID AND PEV.EFFECTIVE_START_DATE = PEE.EFFECTIVE_START_DATE AND PPB.PAY_BASIS_ID = ASG.PAY_BASIS_ID + 0 AND PEE.EFFECTIVE_START_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE AND PEE.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID 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 /* ************************************************************ * RETRIEVES SALARY DETAILS WHERE EITHER * 1. THE 'proposal' IS PERFORMANCE REVIEW * OR 2. THERE ARE NO PREVIOUSLY * APPROVED SALARY PROPOSALS * NOTE THAT THIS MEANS THAT THE QUERY * DOES NOT ATTEMPT TO * DERIVE VALUES FOR THE CHANGE AMOUNT * AND PERCENTAGE. /*************************************************************/ SELECT ASG.ROWID , ASG.ASSIGNMENT_ID , PRO.CHANGE_DATE , TO_CHAR( PRO.PROPOSED_SALARY_N) , TO_CHAR(NULL) , TO_CHAR(NULL) , HLU.MEANING , PEE.ELEMENT_ENTRY_ID , DECODE(PEE.ELEMENT_ENTRY_ID, NULL, 'N', 'Y') , PRO.PAY_PROPOSAL_ID , NVL(EVT.DATE_START, PRO.REVIEW_DATE) , HLU1.MEANING , ASG.NORMAL_HOURS , INF.ORG_INFORMATION3 , /* WORKING HOURS */ TO_CHAR(POS.WORKING_HOURS) , ASG.FREQUENCY , INF.ORG_INFORMATION4 , /* FREQUENCY */ POS.FREQUENCY , TPT.NUMBER_PER_FISCAL_YEAR , ASG.GRADE_ID , PPB.RATE_ID , PPB.PAY_BASIS , HR_GENERAL.DECODE_LOOKUP('PAY_BASIS', PPB.PAY_BASIS) , TO_CHAR(NULL) , PPB.RATE_BASIS , PET.ELEMENT_TYPE_ID , PETTL.ELEMENT_NAME , PET.OUTPUT_CURRENCY_CODE , PET.INPUT_CURRENCY_CODE , PIV.UOM FROM HR_LOOKUPS HLU, HR_LOOKUPS HLU1, PER_EVENTS EVT, PAY_ALL_PAYROLLS_F PAY, PER_TIME_PERIOD_TYPES TPT, HR_POSITIONS POS, HR_ORGANIZATION_INFORMATION INF, PAY_ELEMENT_TYPES_F PET, PAY_INPUT_VALUES_F PIV, PAY_ELEMENT_TYPES_F_TL PETTL, PER_PAY_BASES PPB, PER_ALL_ASSIGNMENTS_F ASG, PAY_ELEMENT_ENTRIES_F PEE, PER_PAY_PROPOSALS PRO, PER_PEOPLE_F PER WHERE ASG.PERSON_ID = PER.PERSON_ID AND HLU.LOOKUP_CODE(+) = PRO.PROPOSAL_REASON AND HLU.LOOKUP_TYPE (+) = 'PROPOSAL_REASON' AND HLU1.LOOKUP_CODE(+) = PRO.PERFORMANCE_RATING AND HLU1.LOOKUP_TYPE (+) = 'PERFORMANCE_RATING' AND PRO.EVENT_ID = EVT.EVENT_ID(+) AND PAY.PERIOD_TYPE = TPT.PERIOD_TYPE(+) AND ASG.PAYROLL_ID = PAY.PAYROLL_ID(+) AND PET.element_type_id = PETTL.element_type_id (+) AND asg.effective_start_date between POS.EFFECTIVE_START_DATE(+) AND POS.EFFECTIVE_END_DATE(+) AND decode (PETTL.element_type_id, null,'1', PETTL.language) = decode(PETTL.element_type_id, null, '1', userenv('LANG')) 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 + 0 = 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 + 0 = PPB.PAY_BASIS_ID(+) AND (PRO.CHANGE_DATE IS NULL OR /* THERE IS NO PRIOR APPROVED SALARY PROPOSAL */ 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 ASG2.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID AND ASG2.PAY_BASIS_ID + 0 = 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 + 0 = 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.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 /* RETRIEVES SALARY DETAILS WHERE * 1. THERE IS A PREVIOUSLY APPROVED SALARY PROPOSAL */ SELECT ASG.ROWID , ASG.ASSIGNMENT_ID , PRO.CHANGE_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) , 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 , 2)), NULL), HLU.MEANING, PEE.ELEMENT_ENTRY_ID, DECODE(PEE.ELEMENT_ENTRY_ID, NULL, 'N', 'Y'), PRO.PAY_PROPOSAL_ID, NVL(EVT.DATE_START, PRO.REVIEW_DATE), HLU1.MEANING, ASG.NORMAL_HOURS, INF.ORG_INFORMATION3, /* WORKING HOURS */ TO_CHAR(POS.WORKING_HOURS), ASG.FREQUENCY, INF.ORG_INFORMATION4, /* FREQUENCY */ POS.FREQUENCY, TPT.NUMBER_PER_FISCAL_YEAR, ASG.GRADE_ID, PPB.RATE_ID, PPB.PAY_BASIS, HR_GENERAL.DECODE_LOOKUP('PAY_BASIS', PPB.PAY_BASIS), PPB2.PAY_BASIS, PPB.RATE_BASIS, PET.ELEMENT_TYPE_ID, PETTL.ELEMENT_NAME, PET.OUTPUT_CURRENCY_CODE, PET.INPUT_CURRENCY_CODE, PIV.UOM FROM HR_LOOKUPS HLU, HR_LOOKUPS HLU1, PER_EVENTS EVT, PAY_ALL_PAYROLLS_F PAY, PER_TIME_PERIOD_TYPES TPT, HR_POSITIONS POS, HR_ORGANIZATION_INFORMATION INF, PAY_ELEMENT_TYPES_F PET, PAY_INPUT_VALUES_F PIV, PAY_ELEMENT_TYPES_F_TL PETTL, 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_ALL_ASSIGNMENTS_F ASG2, PER_ALL_ASSIGNMENTS_F ASG, PER_PAY_PROPOSALS PRO, PER_PEOPLE_F PER WHERE ASG.PERSON_ID = PER.PERSON_ID AND HLU.LOOKUP_CODE(+) = PRO.PROPOSAL_REASON AND HLU.LOOKUP_TYPE (+) = 'PROPOSAL_REASON' AND HLU1.LOOKUP_CODE(+) = PRO.PERFORMANCE_RATING AND HLU1.LOOKUP_TYPE (+) = 'PERFORMANCE_RATING' AND PRO.EVENT_ID = EVT.EVENT_ID(+) AND PAY.PERIOD_TYPE = TPT.PERIOD_TYPE(+) AND ASG.PAYROLL_ID = PAY.PAYROLL_ID(+) AND asg.effective_start_date between POS.EFFECTIVE_START_DATE(+) AND POS.EFFECTIVE_END_DATE(+) 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 + 0 = 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 + 0 = PPB.PAY_BASIS_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 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 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'))

View Text - HTML Formatted

SELECT ASG.ROWID
, ASG.ASSIGNMENT_ID
, PEE.EFFECTIVE_START_DATE
, PEV.SCREEN_ENTRY_VALUE
, DECODE(PEE2.ELEMENT_ENTRY_ID
, DECODE(PEE.ELEMENT_ENTRY_ID
, NULL
, PEE2.ELEMENT_ENTRY_ID
, PEE.ELEMENT_ENTRY_ID)
, TO_CHAR (TO_NUMBER (PEV.SCREEN_ENTRY_VALUE)- 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 (((TO_NUMBER(PEV.SCREEN_ENTRY_VALUE) - TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE)) / DECODE(TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE)
, 0
, NULL
, TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE))) * 100
, 2))
, NULL)
, TO_CHAR(NULL)
, PEE.ELEMENT_ENTRY_ID
, DECODE(PEE.ELEMENT_ENTRY_ID
, NULL
, 'N'
, 'Y')
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_CHAR(NULL)
, ASG.NORMAL_HOURS
, INF.ORG_INFORMATION3
, /* WORKING HOURS */ TO_CHAR( POS.WORKING_HOURS)
, ASG.FREQUENCY
, INF.ORG_INFORMATION4
, /* FREQUENCY */ POS.FREQUENCY
, TPT.NUMBER_PER_FISCAL_YEAR
, ASG.GRADE_ID
, PPB.RATE_ID
, PPB.PAY_BASIS
, HR_GENERAL.DECODE_LOOKUP('PAY_BASIS'
, PPB.PAY_BASIS)
, PPB2.PAY_BASIS
, PPB.RATE_BASIS
, PET.ELEMENT_TYPE_ID
, PETTL.ELEMENT_NAME
, PET.OUTPUT_CURRENCY_CODE
, PET.INPUT_CURRENCY_CODE
, PIV.UOM
FROM PER_TIME_PERIOD_TYPES TPT
, PAY_ALL_PAYROLLS_F PAY
, HR_POSITIONS POS
, HR_ORGANIZATION_INFORMATION INF
, PAY_ELEMENT_ENTRY_VALUES_F PEV2
, PAY_ELEMENT_ENTRY_VALUES_F PEV
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_INPUT_VALUES_F PIV
, PER_PAY_BASES PPB2
, PER_PAY_BASES PPB
, PER_ALL_ASSIGNMENTS_F ASG2
, PER_ALL_ASSIGNMENTS_F ASG
, PAY_ELEMENT_ENTRIES_F PEE2
, PAY_ELEMENT_ENTRIES_F PEE
, PER_PEOPLE_F PER
WHERE ASG.PERSON_ID = PER.PERSON_ID
AND NOT EXISTS (SELECT 1
FROM PER_PAY_PROPOSALS PRO
WHERE PEE.CREATOR_TYPE = 'SP'
AND PEE.CREATOR_ID = PRO.PAY_PROPOSAL_ID
AND PEE.EFFECTIVE_START_DATE = PRO.CHANGE_DATE)
AND PAY.PERIOD_TYPE = TPT.PERIOD_TYPE(+)
AND ASG.PAYROLL_ID = PAY.PAYROLL_ID(+)
AND ASG.EFFECTIVE_START_DATE BETWEEN PAY.EFFECTIVE_START_DATE(+)
AND PAY.EFFECTIVE_END_DATE(+)
AND ASG.POSITION_ID = POS.POSITION_ID(+)
AND ASG.EFFECTIVE_START_DATE BETWEEN POS.EFFECTIVE_START_DATE(+)
AND POS.EFFECTIVE_END_DATE(+)
AND ASG.ORGANIZATION_ID + 0 = INF.ORGANIZATION_ID(+)
AND INF.ORG_INFORMATION_CONTEXT (+) || '' = 'WORK DAY INFORMATION'
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 IS NULL OR ASG2.PAY_BASIS_ID + 0 = PPB2.PAY_BASIS_ID)
AND ASG2.ASSIGNMENT_ID(+) = PEE.ASSIGNMENT_ID
AND PEE.EFFECTIVE_START_DATE - 1 BETWEEN ASG2.EFFECTIVE_START_DATE(+)
AND ASG2.EFFECTIVE_END_DATE(+)
AND PEE2.ASSIGNMENT_ID(+) = PEE.ASSIGNMENT_ID
AND PEE.EFFECTIVE_START_DATE - 1 BETWEEN PEE2.EFFECTIVE_END_DATE(+)
AND PEE2.EFFECTIVE_END_DATE(+)
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 PEV.INPUT_VALUE_ID + 0 = PIV.INPUT_VALUE_ID
AND PEV.EFFECTIVE_START_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PEV.INPUT_VALUE_ID + 0 = PPB.INPUT_VALUE_ID
AND PEV.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PEV.EFFECTIVE_START_DATE = PEE.EFFECTIVE_START_DATE
AND PPB.PAY_BASIS_ID = ASG.PAY_BASIS_ID + 0
AND PEE.EFFECTIVE_START_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND PEE.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
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 /* ************************************************************ * RETRIEVES SALARY DETAILS
WHERE EITHER * 1. THE 'PROPOSAL' IS PERFORMANCE REVIEW * OR 2. THERE ARE NO PREVIOUSLY * APPROVED SALARY PROPOSALS * NOTE THAT THIS MEANS THAT THE QUERY * DOES NOT ATTEMPT TO * DERIVE VALUES FOR THE CHANGE AMOUNT *
AND PERCENTAGE. /*************************************************************/ SELECT ASG.ROWID
, ASG.ASSIGNMENT_ID
, PRO.CHANGE_DATE
, TO_CHAR( PRO.PROPOSED_SALARY_N)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, HLU.MEANING
, PEE.ELEMENT_ENTRY_ID
, DECODE(PEE.ELEMENT_ENTRY_ID
, NULL
, 'N'
, 'Y')
, PRO.PAY_PROPOSAL_ID
, NVL(EVT.DATE_START
, PRO.REVIEW_DATE)
, HLU1.MEANING
, ASG.NORMAL_HOURS
, INF.ORG_INFORMATION3
, /* WORKING HOURS */ TO_CHAR(POS.WORKING_HOURS)
, ASG.FREQUENCY
, INF.ORG_INFORMATION4
, /* FREQUENCY */ POS.FREQUENCY
, TPT.NUMBER_PER_FISCAL_YEAR
, ASG.GRADE_ID
, PPB.RATE_ID
, PPB.PAY_BASIS
, HR_GENERAL.DECODE_LOOKUP('PAY_BASIS'
, PPB.PAY_BASIS)
, TO_CHAR(NULL)
, PPB.RATE_BASIS
, PET.ELEMENT_TYPE_ID
, PETTL.ELEMENT_NAME
, PET.OUTPUT_CURRENCY_CODE
, PET.INPUT_CURRENCY_CODE
, PIV.UOM
FROM HR_LOOKUPS HLU
, HR_LOOKUPS HLU1
, PER_EVENTS EVT
, PAY_ALL_PAYROLLS_F PAY
, PER_TIME_PERIOD_TYPES TPT
, HR_POSITIONS POS
, HR_ORGANIZATION_INFORMATION INF
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F_TL PETTL
, PER_PAY_BASES PPB
, PER_ALL_ASSIGNMENTS_F ASG
, PAY_ELEMENT_ENTRIES_F PEE
, PER_PAY_PROPOSALS PRO
, PER_PEOPLE_F PER
WHERE ASG.PERSON_ID = PER.PERSON_ID
AND HLU.LOOKUP_CODE(+) = PRO.PROPOSAL_REASON
AND HLU.LOOKUP_TYPE (+) = 'PROPOSAL_REASON'
AND HLU1.LOOKUP_CODE(+) = PRO.PERFORMANCE_RATING
AND HLU1.LOOKUP_TYPE (+) = 'PERFORMANCE_RATING'
AND PRO.EVENT_ID = EVT.EVENT_ID(+)
AND PAY.PERIOD_TYPE = TPT.PERIOD_TYPE(+)
AND ASG.PAYROLL_ID = PAY.PAYROLL_ID(+)
AND PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID (+)
AND ASG.EFFECTIVE_START_DATE BETWEEN POS.EFFECTIVE_START_DATE(+)
AND POS.EFFECTIVE_END_DATE(+)
AND DECODE (PETTL.ELEMENT_TYPE_ID
, NULL
, '1'
, PETTL.LANGUAGE) = DECODE(PETTL.ELEMENT_TYPE_ID
, NULL
, '1'
, USERENV('LANG'))
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 + 0 = 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 + 0 = PPB.PAY_BASIS_ID(+)
AND (PRO.CHANGE_DATE IS NULL OR /* THERE IS NO PRIOR APPROVED SALARY PROPOSAL */ 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 ASG2.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND ASG2.PAY_BASIS_ID + 0 = 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 + 0 = 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.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 /* RETRIEVES SALARY DETAILS
WHERE * 1. THERE IS A PREVIOUSLY APPROVED SALARY PROPOSAL */ SELECT ASG.ROWID
, ASG.ASSIGNMENT_ID
, PRO.CHANGE_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)
, 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
, 2))
, NULL)
, HLU.MEANING
, PEE.ELEMENT_ENTRY_ID
, DECODE(PEE.ELEMENT_ENTRY_ID
, NULL
, 'N'
, 'Y')
, PRO.PAY_PROPOSAL_ID
, NVL(EVT.DATE_START
, PRO.REVIEW_DATE)
, HLU1.MEANING
, ASG.NORMAL_HOURS
, INF.ORG_INFORMATION3
, /* WORKING HOURS */ TO_CHAR(POS.WORKING_HOURS)
, ASG.FREQUENCY
, INF.ORG_INFORMATION4
, /* FREQUENCY */ POS.FREQUENCY
, TPT.NUMBER_PER_FISCAL_YEAR
, ASG.GRADE_ID
, PPB.RATE_ID
, PPB.PAY_BASIS
, HR_GENERAL.DECODE_LOOKUP('PAY_BASIS'
, PPB.PAY_BASIS)
, PPB2.PAY_BASIS
, PPB.RATE_BASIS
, PET.ELEMENT_TYPE_ID
, PETTL.ELEMENT_NAME
, PET.OUTPUT_CURRENCY_CODE
, PET.INPUT_CURRENCY_CODE
, PIV.UOM
FROM HR_LOOKUPS HLU
, HR_LOOKUPS HLU1
, PER_EVENTS EVT
, PAY_ALL_PAYROLLS_F PAY
, PER_TIME_PERIOD_TYPES TPT
, HR_POSITIONS POS
, HR_ORGANIZATION_INFORMATION INF
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F_TL PETTL
, 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_ALL_ASSIGNMENTS_F ASG2
, PER_ALL_ASSIGNMENTS_F ASG
, PER_PAY_PROPOSALS PRO
, PER_PEOPLE_F PER
WHERE ASG.PERSON_ID = PER.PERSON_ID
AND HLU.LOOKUP_CODE(+) = PRO.PROPOSAL_REASON
AND HLU.LOOKUP_TYPE (+) = 'PROPOSAL_REASON'
AND HLU1.LOOKUP_CODE(+) = PRO.PERFORMANCE_RATING
AND HLU1.LOOKUP_TYPE (+) = 'PERFORMANCE_RATING'
AND PRO.EVENT_ID = EVT.EVENT_ID(+)
AND PAY.PERIOD_TYPE = TPT.PERIOD_TYPE(+)
AND ASG.PAYROLL_ID = PAY.PAYROLL_ID(+)
AND ASG.EFFECTIVE_START_DATE BETWEEN POS.EFFECTIVE_START_DATE(+)
AND POS.EFFECTIVE_END_DATE(+)
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 + 0 = 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 + 0 = PPB.PAY_BASIS_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 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 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'))