VIEW: APPS.PER_CAGR_ENTITLEMENT_RESULTS_V

Source

View Text - Preformatted

SELECT er.cagr_entitlement_result_id ,er.CAGR_REQUEST_ID ,er.START_DATE ,er.END_DATE ,er.COLLECTIVE_AGREEMENT_ID ,ca.NAME ,er.CAGR_ENTITLEMENT_ITEM_ID ,ei.ITEM_NAME ,er.ELEMENT_TYPE_ID ,er.INPUT_VALUE_ID ,er.CAGR_API_ID ,er.CAGR_API_PARAM_ID ,er.CATEGORY_NAME ,SUBSTR(hr_general.decode_lookup('CAGR_CATEGORIES', er.CATEGORY_NAME), 1, 80) ,er.CAGR_ENTITLEMENT_ID ,er.CAGR_ENTITLEMENT_LINE_ID ,er.ASSIGNMENT_ID ,decode(er.FLEX_VALUE_SET_ID,NULL,er.VALUE ,per_cagr_utility_pkg.get_name_from_value_set(er.FLEX_VALUE_SET_ID,er.BUSINESS_GROUP_ID,er.VALUE)) ,er.UNITS_OF_MEASURE ,decode(er.UNITS_OF_MEASURE,NULL,NULL,SUBSTR(hr_general.decode_lookup('UNITS', er.UNITS_OF_MEASURE), 1, 80)) ,er.RANGE_FROM ,er.RANGE_TO ,er.PARENT_SPINE_ID ,ps.NAME ,er.GRADE_SPINE_ID ,gs.D_GRADE ,er.STEP_ID ,sp1.D_SPINAL_POINT ,er.FROM_STEP_ID ,sp2.D_SPINAL_POINT ,er.TO_STEP_ID ,sp3.D_SPINAL_POINT ,er.RETAINED_ENT_RESULT_ID ,decode(nvl(crr.cagr_retained_right_id, crr1.cagr_retained_right_id),NULL,'N','Y') ,er.BENEFICIAL_FLAG ,er.CHOSEN_FLAG ,er.COLUMN_TYPE ,er.COLUMN_SIZE ,er.OIPL_ID ,decode(er.OIPL_ID,NULL,ff.FORMULA_NAME ,decode(er.OIPL_ID,0,SUBSTR(hr_general.decode_lookup('CAGR_DEFAULT','DEF'), 1, 80),bep.NAME)) ,decode(er.OIPL_ID,NULL,'F','C') ,er.flex_value_set_id ,er.OBJECT_VERSION_NUMBER ,nvl(crr.cagr_retained_right_id, crr1.cagr_retained_right_id) ,nvl(crr.OBJECT_VERSION_NUMBER,crr1.OBJECT_VERSION_NUMBER) ,nvl(crr.freeze_flag,crr1.freeze_flag) ,nvl(crr.start_date,crr1.start_date) ,nvl(crr.end_date,crr1.end_date) FROM per_cagr_entitlement_results er ,per_cagr_entitlement_items ei ,per_collective_agreements ca ,ben_eligy_prfl_f bep ,ff_formulas_f ff ,per_parent_spines ps ,per_grade_spines_v gs ,per_spinal_point_steps_v sp1 ,per_spinal_point_steps_v sp2 ,per_spinal_point_steps_v sp3 ,per_cagr_retained_rights crr ,per_cagr_retained_rights crr1 ,fnd_sessions ses WHERE er.cagr_entitlement_item_id = ei.cagr_entitlement_item_id AND er.collective_agreement_id = ca.collective_agreement_id AND ses.session_id = userenv('SESSIONID') AND ses.effective_date between er.START_DATE and nvl(er.END_DATE,hr_general.end_of_time) AND er.eligy_prfl_id = bep.eligy_prfl_id (+) AND (ses.effective_date between bep.effective_start_date and bep.effective_end_date OR bep.eligy_prfl_id IS NULL) AND er.formula_id = ff.formula_id (+) AND (ses.effective_date between ff.effective_start_date and ff.effective_end_date OR ff.formula_id IS NULL) AND er.parent_spine_id = ps.parent_spine_id (+) AND er.grade_spine_id = gs.grade_spine_id (+) AND er.step_id = sp1.step_id (+) AND er.from_step_id = sp2.step_id (+) AND er.to_step_id = sp3.step_id (+) AND er.cagr_entitlement_result_id = sp3.spinal_point_id (+) AND er.cagr_entitlement_result_id = crr.cagr_entitlement_result_id (+) AND er.retained_ent_result_id = crr1.cagr_entitlement_result_id (+) order by er.cagr_entitlement_result_id

View Text - HTML Formatted

SELECT ER.CAGR_ENTITLEMENT_RESULT_ID
, ER.CAGR_REQUEST_ID
, ER.START_DATE
, ER.END_DATE
, ER.COLLECTIVE_AGREEMENT_ID
, CA.NAME
, ER.CAGR_ENTITLEMENT_ITEM_ID
, EI.ITEM_NAME
, ER.ELEMENT_TYPE_ID
, ER.INPUT_VALUE_ID
, ER.CAGR_API_ID
, ER.CAGR_API_PARAM_ID
, ER.CATEGORY_NAME
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('CAGR_CATEGORIES'
, ER.CATEGORY_NAME)
, 1
, 80)
, ER.CAGR_ENTITLEMENT_ID
, ER.CAGR_ENTITLEMENT_LINE_ID
, ER.ASSIGNMENT_ID
, DECODE(ER.FLEX_VALUE_SET_ID
, NULL
, ER.VALUE
, PER_CAGR_UTILITY_PKG.GET_NAME_FROM_VALUE_SET(ER.FLEX_VALUE_SET_ID
, ER.BUSINESS_GROUP_ID
, ER.VALUE))
, ER.UNITS_OF_MEASURE
, DECODE(ER.UNITS_OF_MEASURE
, NULL
, NULL
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('UNITS'
, ER.UNITS_OF_MEASURE)
, 1
, 80))
, ER.RANGE_FROM
, ER.RANGE_TO
, ER.PARENT_SPINE_ID
, PS.NAME
, ER.GRADE_SPINE_ID
, GS.D_GRADE
, ER.STEP_ID
, SP1.D_SPINAL_POINT
, ER.FROM_STEP_ID
, SP2.D_SPINAL_POINT
, ER.TO_STEP_ID
, SP3.D_SPINAL_POINT
, ER.RETAINED_ENT_RESULT_ID
, DECODE(NVL(CRR.CAGR_RETAINED_RIGHT_ID
, CRR1.CAGR_RETAINED_RIGHT_ID)
, NULL
, 'N'
, 'Y')
, ER.BENEFICIAL_FLAG
, ER.CHOSEN_FLAG
, ER.COLUMN_TYPE
, ER.COLUMN_SIZE
, ER.OIPL_ID
, DECODE(ER.OIPL_ID
, NULL
, FF.FORMULA_NAME
, DECODE(ER.OIPL_ID
, 0
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('CAGR_DEFAULT'
, 'DEF')
, 1
, 80)
, BEP.NAME))
, DECODE(ER.OIPL_ID
, NULL
, 'F'
, 'C')
, ER.FLEX_VALUE_SET_ID
, ER.OBJECT_VERSION_NUMBER
, NVL(CRR.CAGR_RETAINED_RIGHT_ID
, CRR1.CAGR_RETAINED_RIGHT_ID)
, NVL(CRR.OBJECT_VERSION_NUMBER
, CRR1.OBJECT_VERSION_NUMBER)
, NVL(CRR.FREEZE_FLAG
, CRR1.FREEZE_FLAG)
, NVL(CRR.START_DATE
, CRR1.START_DATE)
, NVL(CRR.END_DATE
, CRR1.END_DATE)
FROM PER_CAGR_ENTITLEMENT_RESULTS ER
, PER_CAGR_ENTITLEMENT_ITEMS EI
, PER_COLLECTIVE_AGREEMENTS CA
, BEN_ELIGY_PRFL_F BEP
, FF_FORMULAS_F FF
, PER_PARENT_SPINES PS
, PER_GRADE_SPINES_V GS
, PER_SPINAL_POINT_STEPS_V SP1
, PER_SPINAL_POINT_STEPS_V SP2
, PER_SPINAL_POINT_STEPS_V SP3
, PER_CAGR_RETAINED_RIGHTS CRR
, PER_CAGR_RETAINED_RIGHTS CRR1
, FND_SESSIONS SES
WHERE ER.CAGR_ENTITLEMENT_ITEM_ID = EI.CAGR_ENTITLEMENT_ITEM_ID
AND ER.COLLECTIVE_AGREEMENT_ID = CA.COLLECTIVE_AGREEMENT_ID
AND SES.SESSION_ID = USERENV('SESSIONID')
AND SES.EFFECTIVE_DATE BETWEEN ER.START_DATE
AND NVL(ER.END_DATE
, HR_GENERAL.END_OF_TIME)
AND ER.ELIGY_PRFL_ID = BEP.ELIGY_PRFL_ID (+)
AND (SES.EFFECTIVE_DATE BETWEEN BEP.EFFECTIVE_START_DATE
AND BEP.EFFECTIVE_END_DATE OR BEP.ELIGY_PRFL_ID IS NULL)
AND ER.FORMULA_ID = FF.FORMULA_ID (+)
AND (SES.EFFECTIVE_DATE BETWEEN FF.EFFECTIVE_START_DATE
AND FF.EFFECTIVE_END_DATE OR FF.FORMULA_ID IS NULL)
AND ER.PARENT_SPINE_ID = PS.PARENT_SPINE_ID (+)
AND ER.GRADE_SPINE_ID = GS.GRADE_SPINE_ID (+)
AND ER.STEP_ID = SP1.STEP_ID (+)
AND ER.FROM_STEP_ID = SP2.STEP_ID (+)
AND ER.TO_STEP_ID = SP3.STEP_ID (+)
AND ER.CAGR_ENTITLEMENT_RESULT_ID = SP3.SPINAL_POINT_ID (+)
AND ER.CAGR_ENTITLEMENT_RESULT_ID = CRR.CAGR_ENTITLEMENT_RESULT_ID (+)
AND ER.RETAINED_ENT_RESULT_ID = CRR1.CAGR_ENTITLEMENT_RESULT_ID (+) ORDER BY ER.CAGR_ENTITLEMENT_RESULT_ID