View: HRU_SALARY_PROPOSALS

Product:	PER - Human Resources
Description:	Lists salary proposals including their components, also used to upload slary proposals via ADE.
Implementation/DBA Data:	APPS.HRU_SALARY_PROPOSALS

View Text

SELECT PRO1.ROWID
, PER.FULL_NAME
, PER.TITLE
, ASS.BUSINESS_GROUP_ID
, BGR.NAME
, PER.EMPLOYEE_NUMBER
, ASS.ASSIGNMENT_ID
, ASS.ASSIGNMENT_NUMBER
, ASS.EMPLOYMENT_CATEGORY
, ORG.NAME
, ASS.ORGANIZATION_ID
, GDT.NAME
, ASS.GRADE_ID
, FND_NUMBER.CANONICAL_TO_NUMBER(PE_GET_VAL_PKG.GET_GRADE_VALUE(GDT.GRADE_ID
, PPB.RATE_ID
, NVL(PRO1.CHANGE_DATE
, PRO2.CHANGE_DATE)
, 'MAX'))
, FND_NUMBER.CANONICAL_TO_NUMBER(PE_GET_VAL_PKG.GET_GRADE_VALUE(GDT.GRADE_ID
, PPB.RATE_ID
, NVL(PRO1.CHANGE_DATE
, PRO2.CHANGE_DATE)
, 'MID'))
, FND_NUMBER.CANONICAL_TO_NUMBER(PE_GET_VAL_PKG.GET_GRADE_VALUE(GDT.GRADE_ID
, PPB.RATE_ID
, NVL(PRO1.CHANGE_DATE
, PRO2.CHANGE_DATE)
, 'MIN'))
, FND_NUMBER.CANONICAL_TO_NUMBER(PE_GET_VAL_PKG.GET_GRADE_VALUE(GDT.GRADE_ID
, PPB.RATE_ID
, NVL(PRO1.CHANGE_DATE
, PRO2.CHANGE_DATE)
, 'MAX'))*NVL(PPB.GRADE_ANNUALIZATION_FACTOR
, TPT.NUMBER_PER_FISCAL_YEAR)
, FND_NUMBER.CANONICAL_TO_NUMBER(PE_GET_VAL_PKG.GET_GRADE_VALUE(GDT.GRADE_ID
, PPB.RATE_ID
, NVL(PRO1.CHANGE_DATE
, PRO2.CHANGE_DATE)
, 'MID'))*NVL(PPB.GRADE_ANNUALIZATION_FACTOR
, TPT.NUMBER_PER_FISCAL_YEAR)
, FND_NUMBER.CANONICAL_TO_NUMBER( PE_GET_VAL_PKG.GET_GRADE_VALUE(GDT.GRADE_ID
, PPB.RATE_ID
, NVL(PRO1.CHANGE_DATE
, PRO2.CHANGE_DATE)
, 'MIN'))*NVL(PPB.GRADE_ANNUALIZATION_FACTOR
, TPT.NUMBER_PER_FISCAL_YEAR)
, NVL(PPB.GRADE_ANNUALIZATION_FACTOR
, TPT.NUMBER_PER_FISCAL_YEAR)
, NVL(PPB.PAY_ANNUALIZATION_FACTOR
, TPT.NUMBER_PER_FISCAL_YEAR)
, LKU5.MEANING
, LKU4.MEANING
, PPB.NAME
, ASS.PAY_BASIS_ID
, PFT.NAME
, ASS.POSITION_ID
, JBT.NAME
, ASS.JOB_ID
, PRL.PAYROLL_NAME
, ASS.PAYROLL_ID
, PRO2.CHANGE_DATE
, PRO2.PROPOSED_SALARY_N
, PRO2.PROPOSED_SALARY_N*NVL(PPB.PAY_ANNUALIZATION_FACTOR
, TPT.NUMBER_PER_FISCAL_YEAR)
, PRO2.FORCED_RANKING
, PRV2.PERFORMANCE_RATING
, LKU2.MEANING
, PRO1.PROPOSAL_REASON
, LKU.MEANING
, PRV2.REVIEW_DATE
, PRO1.PAY_PROPOSAL_ID
, PRO1.PROPOSED_SALARY_N
, PRO1.PROPOSED_SALARY_N*NVL(PPB.PAY_ANNUALIZATION_FACTOR
, TPT.NUMBER_PER_FISCAL_YEAR)
, PET.INPUT_CURRENCY_CODE
, PRO1.CHANGE_DATE
, NVL(PRO1.APPROVED
, PRO2.APPROVED) APPROVED
, PRV1.PERFORMANCE_RATING
, PRO1.FORCED_RANKING
, LKU3.MEANING
, PRV1.REVIEW_DATE
, PRO1.OBJECT_VERSION_NUMBER
, COM1.COMPONENT_ID
, COM2.COMPONENT_ID
, COM3.COMPONENT_ID
, COM4.COMPONENT_ID
, COM5.COMPONENT_ID
, COM6.COMPONENT_ID
, COM7.COMPONENT_ID
, COM8.COMPONENT_ID
, COM9.COMPONENT_ID
, COM10.COMPONENT_ID
, COM1.CHANGE_AMOUNT_N
, COM2.CHANGE_AMOUNT_N
, COM3.CHANGE_AMOUNT_N
, COM4.CHANGE_AMOUNT_N
, COM5.CHANGE_AMOUNT_N
, COM6.CHANGE_AMOUNT_N
, COM7.CHANGE_AMOUNT_N
, COM8.CHANGE_AMOUNT_N
, COM9.CHANGE_AMOUNT_N
, COM10.CHANGE_AMOUNT_N
, COM1.CHANGE_PERCENTAGE
, COM2.CHANGE_PERCENTAGE
, COM3.CHANGE_PERCENTAGE
, COM4.CHANGE_PERCENTAGE
, COM5.CHANGE_PERCENTAGE
, COM6.CHANGE_PERCENTAGE
, COM7.CHANGE_PERCENTAGE
, COM8.CHANGE_PERCENTAGE
, COM9.CHANGE_PERCENTAGE
, COM10.CHANGE_PERCENTAGE
, 'COL'
, 'JOEV'
, 'LOAD'
, 'MKAD'
, 'NEWH'
, 'PERF'
, 'PERE'
, 'PROG'
, 'PROM'
, 'TRAD'
, COM1.OBJECT_VERSION_NUMBER
, COM2.OBJECT_VERSION_NUMBER
, COM3.OBJECT_VERSION_NUMBER
, COM4.OBJECT_VERSION_NUMBER
, COM5.OBJECT_VERSION_NUMBER
, COM6.OBJECT_VERSION_NUMBER
, COM7.OBJECT_VERSION_NUMBER
, COM8.OBJECT_VERSION_NUMBER
, COM9.OBJECT_VERSION_NUMBER
, COM10.OBJECT_VERSION_NUMBER
, COM1.APPROVED
, COM2.APPROVED
, COM3.APPROVED
, COM4.APPROVED
, COM5.APPROVED
, COM6.APPROVED
, COM7.APPROVED
, COM8.APPROVED
, COM9.APPROVED
, COM10.APPROVED
, PET.ELEMENT_TYPE_ID
, PIV.INPUT_VALUE_ID
FROM PER_PEOPLE_F PER
, HR_LOOKUPS LKU
, HR_LOOKUPS LKU2
, HR_LOOKUPS LKU3
, HR_LOOKUPS LKU4
, HR_LOOKUPS LKU5
, PER_TIME_PERIOD_TYPES TPT
, PAY_ALL_PAYROLLS_F PRL
, PER_ASSIGNMENTS_F ASS
, PER_ASSIGNMENT_STATUS_TYPES AST
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PER_PAY_BASES PPB
, PER_PERFORMANCE_REVIEWS PRV1
, PER_PAY_PROPOSALS PRO1
, PER_PERFORMANCE_REVIEWS PRV2
, PER_PAY_PROPOSALS PRO2
, PER_GRADES_TL GDT
, HR_ALL_ORGANIZATION_UNITS BGR
, HR_ALL_ORGANIZATION_UNITS ORG
, HR_ALL_POSITIONS_F_TL PFT
, PER_JOBS_TL JBT
, PER_PAY_PROPOSAL_COMPONENTS COM1
, PER_PAY_PROPOSAL_COMPONENTS COM2
, PER_PAY_PROPOSAL_COMPONENTS COM3
, PER_PAY_PROPOSAL_COMPONENTS COM4
, PER_PAY_PROPOSAL_COMPONENTS COM5
, PER_PAY_PROPOSAL_COMPONENTS COM6
, PER_PAY_PROPOSAL_COMPONENTS COM7
, PER_PAY_PROPOSAL_COMPONENTS COM8
, PER_PAY_PROPOSAL_COMPONENTS COM9
, PER_PAY_PROPOSAL_COMPONENTS COM10
, FND_SESSIONS FND
WHERE PER.PERSON_ID = ASS.PERSON_ID
AND LKU.LOOKUP_CODE (+) = PRO1.PROPOSAL_REASON
AND LKU.LOOKUP_TYPE (+) = 'PROPOSAL_REASON'
AND LKU3.LOOKUP_CODE (+) = PRV1.PERFORMANCE_RATING
AND LKU3.LOOKUP_TYPE (+) = 'PERFORMANCE_RATING'
AND LKU2.LOOKUP_CODE (+) = PRV2.PERFORMANCE_RATING
AND LKU2.LOOKUP_TYPE (+) = 'PERFORMANCE_RATING'
AND LKU4.LOOKUP_CODE=PPB.PAY_BASIS
AND LKU4.LOOKUP_TYPE='PAY_BASIS'
AND LKU5.LOOKUP_CODE(+)=PPB.RATE_BASIS
AND LKU5.LOOKUP_TYPE(+)='PAY_BASIS'
AND PRV1.PERFORMANCE_REVIEW_ID(+)=PRO1.PERFORMANCE_REVIEW_ID
AND PRV2.PERFORMANCE_REVIEW_ID(+)=PRO2.PERFORMANCE_REVIEW_ID
AND ASS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS != 'TERM_ASSIGN'
AND ASS.GRADE_ID = GDT.GRADE_ID(+)
AND GDT.LANGUAGE(+) = USERENV('LANG')
AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND ASS.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND ASS.POSITION_ID = PFT.POSITION_ID (+)
AND PFT.LANGUAGE(+) = USERENV('LANG')
AND ASS.ASSIGNMENT_TYPE = 'E'
AND ASS.PAYROLL_ID=PRL.PAYROLL_ID (+)
AND (PRL.PAYROLL_ID IS NULL OR ASS.EFFECTIVE_START_DATE BETWEEN PRL.EFFECTIVE_START_DATE
AND PRL.EFFECTIVE_END_DATE)
AND PRL.PERIOD_TYPE=TPT.PERIOD_TYPE (+)
AND ASS.ASSIGNMENT_ID = PRO1.ASSIGNMENT_ID (+)
AND PRO1.APPROVED(+) = 'N'
AND ASS.ASSIGNMENT_ID = PRO2.ASSIGNMENT_ID(+)
AND (PRO2.CHANGE_DATE IS NULL OR PRO2.CHANGE_DATE = (SELECT MAX(CHANGE_DATE)
FROM PER_PAY_PROPOSALS PRO3
WHERE PRO2.ASSIGNMENT_ID = PRO3.ASSIGNMENT_ID
AND PRO3.APPROVED= 'Y') )
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 ( NVL (PRO1.CHANGE_DATE
, ASS.EFFECTIVE_START_DATE) BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE )
AND PRO1.PAY_PROPOSAL_ID = COM1.PAY_PROPOSAL_ID(+)
AND PRO1.PAY_PROPOSAL_ID = COM2.PAY_PROPOSAL_ID(+)
AND PRO1.PAY_PROPOSAL_ID = COM3.PAY_PROPOSAL_ID(+)
AND PRO1.PAY_PROPOSAL_ID = COM4.PAY_PROPOSAL_ID(+)
AND PRO1.PAY_PROPOSAL_ID = COM5.PAY_PROPOSAL_ID(+)
AND PRO1.PAY_PROPOSAL_ID = COM6.PAY_PROPOSAL_ID(+)
AND PRO1.PAY_PROPOSAL_ID = COM7.PAY_PROPOSAL_ID(+)
AND PRO1.PAY_PROPOSAL_ID = COM8.PAY_PROPOSAL_ID(+)
AND PRO1.PAY_PROPOSAL_ID = COM9.PAY_PROPOSAL_ID(+)
AND PRO1.PAY_PROPOSAL_ID = COM10.PAY_PROPOSAL_ID(+)
AND COM1.COMPONENT_REASON(+) = 'COL'
AND COM2.COMPONENT_REASON(+) = 'JOEV'
AND COM3.COMPONENT_REASON(+) = 'LOAD'
AND COM4.COMPONENT_REASON(+) = 'MKAD'
AND COM5.COMPONENT_REASON(+) = 'NEWH'
AND COM6.COMPONENT_REASON(+) = 'PERF'
AND COM7.COMPONENT_REASON(+) = 'PERE'
AND COM8.COMPONENT_REASON(+) = 'PROG'
AND COM9.COMPONENT_REASON(+) = 'PROM'
AND COM10.COMPONENT_REASON(+) = 'TRAD'
AND FND.SESSION_ID = USERENV('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN ASS.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE
AND ASS.BUSINESS_GROUP_ID+0=NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, ASS.BUSINESS_GROUP_ID)
AND ASS.BUSINESS_GROUP_ID+0=BGR.ORGANIZATION_ID

Columns