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

Source

View Text - Preformatted


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 (DECODE (gdt.grade_id, NULL, NULL, DECODE (ppb.rate_id, NULL, NULL, DECODE (pro1.change_date, NULL, DECODE (pro2.change_date, NULL, NULL, pe_get_val_pkg.get_grade_value (gdt.grade_id, ppb.rate_id, pro2.change_date, 'MAX' ) ), pe_get_val_pkg.get_grade_value (gdt.grade_id, ppb.rate_id, pro1.change_date, 'MAX' ) ) ) ) ), fnd_number.canonical_to_number (DECODE (gdt.grade_id, NULL, NULL, DECODE (ppb.rate_id, NULL, NULL, DECODE (pro1.change_date, NULL,DECODE (pro2.change_date, NULL, NULL, pe_get_val_pkg.get_grade_value (gdt.grade_id, ppb.rate_id, pro2.change_date, 'MID' ) ), pe_get_val_pkg.get_grade_value (gdt.grade_id, ppb.rate_id, pro1.change_date, 'MID' ) ) ) ) ), fnd_number.canonical_to_number (DECODE (gdt.grade_id, NULL, NULL, DECODE (ppb.rate_id, NULL, NULL, DECODE (pro1.change_date, NULL, DECODE (pro2.change_date, NULL, NULL, pe_get_val_pkg.get_grade_value (gdt.grade_id, ppb.rate_id, pro2.change_date, 'MIN' ) ), pe_get_val_pkg.get_grade_value (gdt.grade_id, ppb.rate_id, pro1.change_date, 'MIN' ) ) ) ) ), fnd_number.canonical_to_number (DECODE (gdt.grade_id, NULL, NULL, DECODE (ppb.rate_id, NULL, NULL, DECODE (pro1.change_date, NULL, DECODE (pro2.change_date, NULL, NULL, pe_get_val_pkg.get_grade_value (gdt.grade_id, ppb.rate_id, pro2.change_date, 'MAX' ) ), pe_get_val_pkg.get_grade_value (gdt.grade_id, ppb.rate_id, pro1.change_date, 'MAX' ) ) ) ) ) * NVL (ppb.grade_annualization_factor, tpt.number_per_fiscal_year), fnd_number.canonical_to_number (DECODE (gdt.grade_id, NULL, NULL, DECODE (ppb.rate_id, NULL, NULL, DECODE (pro1.change_date, NULL, DECODE (pro2.change_date, NULL, NULL, pe_get_val_pkg.get_grade_value (gdt.grade_id, ppb.rate_id, pro2.change_date, 'MID' ) ), pe_get_val_pkg.get_grade_value (gdt.grade_id, ppb.rate_id, pro1.change_date, 'MID' ) ) ) ) ) * NVL (ppb.grade_annualization_factor, tpt.number_per_fiscal_year), fnd_number.canonical_to_number (DECODE (gdt.grade_id, NULL, NULL, DECODE (ppb.rate_id, NULL, NULL, DECODE (pro1.change_date, NULL,DECODE (pro2.change_date, NULL, NULL, pe_get_val_pkg.get_grade_value (gdt.grade_id, ppb.rate_id, pro2.change_date, 'MIN' ) ), pe_get_val_pkg.get_grade_value (gdt.grade_id, ppb.rate_id, pro1.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) *decode(NVL(fnd_profile.value('PER_ANNUAL_SALARY_ON_FTE'),'Y'),'N',1, decode(ppb.PAY_BASIS,'HOURLY',PER_SALADMIN_UTILITY.get_fte_factor(pro2.assignment_id,pro2.CHANGE_DATE),1)), 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) * decode(NVL(fnd_profile.value('PER_ANNUAL_SALARY_ON_FTE'),'Y'),'N',1, decode(ppb.PAY_BASIS,'HOURLY',PER_SALADMIN_UTILITY.get_fte_factor(pro1.assignment_id,pro1.CHANGE_DATE),1)), 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, pro1.attribute_category, pro1.attribute1, pro1.attribute2, pro1.attribute3, pro1.attribute4, pro1.attribute5, pro1.attribute6, pro1.attribute7, pro1.attribute8, pro1.attribute9, pro1.attribute10, pro1.attribute11, pro1.attribute12, pro1.attribute13, pro1.attribute14, pro1.attribute15, pro1.attribute16, pro1.attribute17, pro1.attribute18, pro1.attribute19, pro1.attribute20 , pqh_pp_dff_utils.get_concat_dff_segs(pro1.attribute_category, pro1.attribute1, pro1.attribute2, pro1.attribute3, pro1.attribute4, pro1.attribute5, pro1.attribute6, pro1.attribute7, pro1.attribute8, pro1.attribute9, pro1.attribute10, pro1.attribute11, pro1.attribute12, pro1.attribute13, pro1.attribute14, pro1.attribute15, pro1.attribute16, pro1.attribute17, pro1.attribute18, pro1.attribute19, pro1.attribute20) PAY_PROPOSALS_DESCFLEX FROM pay_element_types_f pet, per_all_people_f per, per_time_period_types tpt, pay_all_payrolls_f prl, hr_all_organization_units org, hr_all_organization_units bgr, per_grades_tl gdt, per_pay_proposal_components com10, per_pay_proposal_components com9, per_pay_proposal_components com8, per_pay_proposal_components com7, per_pay_proposal_components com6, per_pay_proposal_components com5, per_pay_proposal_components com4, per_pay_proposal_components com3, per_pay_proposal_components com2, per_pay_proposal_components com1, hr_lookups lku, hr_lookups lku2, hr_lookups lku3, hr_lookups lku4, hr_lookups lku5, per_performance_reviews prv2, per_performance_reviews prv1, hr_all_positions_f_tl pft, per_jobs_tl jbt, (select ppp.change_date, ppp.proposed_salary_n, ppp.approved, ppp.assignment_id, ppp.forced_ranking, ppp.performance_review_id from per_pay_proposals ppp where approved='Y' ) pro2, per_pay_proposals pro1, per_assignment_status_types ast, per_assignments_f ass, pay_input_values_f piv, per_pay_bases ppb, fnd_sessions fnd WHERE ass.pay_basis_id IS NOT NULL AND 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 AND fnd.effective_date = hr_general.effective_date

View Text - HTML Formatted

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 (DECODE (GDT.GRADE_ID
, NULL
, NULL
, DECODE (PPB.RATE_ID
, NULL
, NULL
, DECODE (PRO1.CHANGE_DATE
, NULL
, DECODE (PRO2.CHANGE_DATE
, NULL
, NULL
, PE_GET_VAL_PKG.GET_GRADE_VALUE (GDT.GRADE_ID
, PPB.RATE_ID
, PRO2.CHANGE_DATE
, 'MAX' ) )
, PE_GET_VAL_PKG.GET_GRADE_VALUE (GDT.GRADE_ID
, PPB.RATE_ID
, PRO1.CHANGE_DATE
, 'MAX' ) ) ) ) )
, FND_NUMBER.CANONICAL_TO_NUMBER (DECODE (GDT.GRADE_ID
, NULL
, NULL
, DECODE (PPB.RATE_ID
, NULL
, NULL
, DECODE (PRO1.CHANGE_DATE
, NULL
, DECODE (PRO2.CHANGE_DATE
, NULL
, NULL
, PE_GET_VAL_PKG.GET_GRADE_VALUE (GDT.GRADE_ID
, PPB.RATE_ID
, PRO2.CHANGE_DATE
, 'MID' ) )
, PE_GET_VAL_PKG.GET_GRADE_VALUE (GDT.GRADE_ID
, PPB.RATE_ID
, PRO1.CHANGE_DATE
, 'MID' ) ) ) ) )
, FND_NUMBER.CANONICAL_TO_NUMBER (DECODE (GDT.GRADE_ID
, NULL
, NULL
, DECODE (PPB.RATE_ID
, NULL
, NULL
, DECODE (PRO1.CHANGE_DATE
, NULL
, DECODE (PRO2.CHANGE_DATE
, NULL
, NULL
, PE_GET_VAL_PKG.GET_GRADE_VALUE (GDT.GRADE_ID
, PPB.RATE_ID
, PRO2.CHANGE_DATE
, 'MIN' ) )
, PE_GET_VAL_PKG.GET_GRADE_VALUE (GDT.GRADE_ID
, PPB.RATE_ID
, PRO1.CHANGE_DATE
, 'MIN' ) ) ) ) )
, FND_NUMBER.CANONICAL_TO_NUMBER (DECODE (GDT.GRADE_ID
, NULL
, NULL
, DECODE (PPB.RATE_ID
, NULL
, NULL
, DECODE (PRO1.CHANGE_DATE
, NULL
, DECODE (PRO2.CHANGE_DATE
, NULL
, NULL
, PE_GET_VAL_PKG.GET_GRADE_VALUE (GDT.GRADE_ID
, PPB.RATE_ID
, PRO2.CHANGE_DATE
, 'MAX' ) )
, PE_GET_VAL_PKG.GET_GRADE_VALUE (GDT.GRADE_ID
, PPB.RATE_ID
, PRO1.CHANGE_DATE
, 'MAX' ) ) ) ) ) * NVL (PPB.GRADE_ANNUALIZATION_FACTOR
, TPT.NUMBER_PER_FISCAL_YEAR)
, FND_NUMBER.CANONICAL_TO_NUMBER (DECODE (GDT.GRADE_ID
, NULL
, NULL
, DECODE (PPB.RATE_ID
, NULL
, NULL
, DECODE (PRO1.CHANGE_DATE
, NULL
, DECODE (PRO2.CHANGE_DATE
, NULL
, NULL
, PE_GET_VAL_PKG.GET_GRADE_VALUE (GDT.GRADE_ID
, PPB.RATE_ID
, PRO2.CHANGE_DATE
, 'MID' ) )
, PE_GET_VAL_PKG.GET_GRADE_VALUE (GDT.GRADE_ID
, PPB.RATE_ID
, PRO1.CHANGE_DATE
, 'MID' ) ) ) ) ) * NVL (PPB.GRADE_ANNUALIZATION_FACTOR
, TPT.NUMBER_PER_FISCAL_YEAR)
, FND_NUMBER.CANONICAL_TO_NUMBER (DECODE (GDT.GRADE_ID
, NULL
, NULL
, DECODE (PPB.RATE_ID
, NULL
, NULL
, DECODE (PRO1.CHANGE_DATE
, NULL
, DECODE (PRO2.CHANGE_DATE
, NULL
, NULL
, PE_GET_VAL_PKG.GET_GRADE_VALUE (GDT.GRADE_ID
, PPB.RATE_ID
, PRO2.CHANGE_DATE
, 'MIN' ) )
, PE_GET_VAL_PKG.GET_GRADE_VALUE (GDT.GRADE_ID
, PPB.RATE_ID
, PRO1.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) *DECODE(NVL(FND_PROFILE.VALUE('PER_ANNUAL_SALARY_ON_FTE')
, 'Y')
, 'N'
, 1
, DECODE(PPB.PAY_BASIS
, 'HOURLY'
, PER_SALADMIN_UTILITY.GET_FTE_FACTOR(PRO2.ASSIGNMENT_ID
, PRO2.CHANGE_DATE)
, 1))
, 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) * DECODE(NVL(FND_PROFILE.VALUE('PER_ANNUAL_SALARY_ON_FTE')
, 'Y')
, 'N'
, 1
, DECODE(PPB.PAY_BASIS
, 'HOURLY'
, PER_SALADMIN_UTILITY.GET_FTE_FACTOR(PRO1.ASSIGNMENT_ID
, PRO1.CHANGE_DATE)
, 1))
, 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
, PRO1.ATTRIBUTE_CATEGORY
, PRO1.ATTRIBUTE1
, PRO1.ATTRIBUTE2
, PRO1.ATTRIBUTE3
, PRO1.ATTRIBUTE4
, PRO1.ATTRIBUTE5
, PRO1.ATTRIBUTE6
, PRO1.ATTRIBUTE7
, PRO1.ATTRIBUTE8
, PRO1.ATTRIBUTE9
, PRO1.ATTRIBUTE10
, PRO1.ATTRIBUTE11
, PRO1.ATTRIBUTE12
, PRO1.ATTRIBUTE13
, PRO1.ATTRIBUTE14
, PRO1.ATTRIBUTE15
, PRO1.ATTRIBUTE16
, PRO1.ATTRIBUTE17
, PRO1.ATTRIBUTE18
, PRO1.ATTRIBUTE19
, PRO1.ATTRIBUTE20
, PQH_PP_DFF_UTILS.GET_CONCAT_DFF_SEGS(PRO1.ATTRIBUTE_CATEGORY
, PRO1.ATTRIBUTE1
, PRO1.ATTRIBUTE2
, PRO1.ATTRIBUTE3
, PRO1.ATTRIBUTE4
, PRO1.ATTRIBUTE5
, PRO1.ATTRIBUTE6
, PRO1.ATTRIBUTE7
, PRO1.ATTRIBUTE8
, PRO1.ATTRIBUTE9
, PRO1.ATTRIBUTE10
, PRO1.ATTRIBUTE11
, PRO1.ATTRIBUTE12
, PRO1.ATTRIBUTE13
, PRO1.ATTRIBUTE14
, PRO1.ATTRIBUTE15
, PRO1.ATTRIBUTE16
, PRO1.ATTRIBUTE17
, PRO1.ATTRIBUTE18
, PRO1.ATTRIBUTE19
, PRO1.ATTRIBUTE20) PAY_PROPOSALS_DESCFLEX
FROM PAY_ELEMENT_TYPES_F PET
, PER_ALL_PEOPLE_F PER
, PER_TIME_PERIOD_TYPES TPT
, PAY_ALL_PAYROLLS_F PRL
, HR_ALL_ORGANIZATION_UNITS ORG
, HR_ALL_ORGANIZATION_UNITS BGR
, PER_GRADES_TL GDT
, PER_PAY_PROPOSAL_COMPONENTS COM10
, PER_PAY_PROPOSAL_COMPONENTS COM9
, PER_PAY_PROPOSAL_COMPONENTS COM8
, PER_PAY_PROPOSAL_COMPONENTS COM7
, PER_PAY_PROPOSAL_COMPONENTS COM6
, PER_PAY_PROPOSAL_COMPONENTS COM5
, PER_PAY_PROPOSAL_COMPONENTS COM4
, PER_PAY_PROPOSAL_COMPONENTS COM3
, PER_PAY_PROPOSAL_COMPONENTS COM2
, PER_PAY_PROPOSAL_COMPONENTS COM1
, HR_LOOKUPS LKU
, HR_LOOKUPS LKU2
, HR_LOOKUPS LKU3
, HR_LOOKUPS LKU4
, HR_LOOKUPS LKU5
, PER_PERFORMANCE_REVIEWS PRV2
, PER_PERFORMANCE_REVIEWS PRV1
, HR_ALL_POSITIONS_F_TL PFT
, PER_JOBS_TL JBT
, (SELECT PPP.CHANGE_DATE
, PPP.PROPOSED_SALARY_N
, PPP.APPROVED
, PPP.ASSIGNMENT_ID
, PPP.FORCED_RANKING
, PPP.PERFORMANCE_REVIEW_ID
FROM PER_PAY_PROPOSALS PPP
WHERE APPROVED='Y' ) PRO2
, PER_PAY_PROPOSALS PRO1
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ASSIGNMENTS_F ASS
, PAY_INPUT_VALUES_F PIV
, PER_PAY_BASES PPB
, FND_SESSIONS FND
WHERE ASS.PAY_BASIS_ID IS NOT NULL
AND 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
AND FND.EFFECTIVE_DATE = HR_GENERAL.EFFECTIVE_DATE