FND Design Data [Home] [Help]

View: PER_FULL_SALARY_HISTORY_V

Product: PER - Human Resources
Description: Used to support user interface
Implementation/DBA Data: ViewAPPS.PER_FULL_SALARY_HISTORY_V
View Text

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

Columns

Name
ROW_ID
ASSIGNMENT_ID
CHANGE_DATE
ACTUAL_SALARY
CHANGE_AMOUNT
CHG_PERCENT
REASON
ELEMENT_ENTRY_ID
APPROVED_FLAG
PAY_PROPOSAL_ID
REVIEW_DATE
PERFORMANCE_RATING
NORMAL_HOURS
ORG_WORKING_HOURS
POS_WORKING_HOURS
ASG_FREQUENCY
ORG_FREQUENCY
POS_FREQUENCY
NUMBER_PER_FISCAL_YEAR
GRADE_ID
RATE_ID
PAY_BASIS
PAY_BASIS_MEANING
PREV_PAY_BASIS
RATE_BASIS
INPUT_CURRENCY
ELEMENT_TYPE_ID
ELEMENT_NAME
CURRENCY_CODE
UOM