-
-

Pay Rate Definitions

CORE HCM

Pay Rate Definitions

select PRDFV.name rate_name_mrd,PRCF.RATE_DEFINITION_ID, PRDFV.short_name rate_short_name_mrd, (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'PAY_RATE_DEFINITION_TYPES' AND language = 'US' AND lookup_code = PRDFV.type) rate_type_mrd, (select name from PER_LEGISLATIVE_DATA_GROUPS_VL where legislative_data_group_id = PRDFV.legislative_data_group_id) legislative_data_group_mrd, TO_CHAR(PRDFV.effective_start_date,'MM/DD/YYYY') effective_start_date_mrd, TO_CHAR(PRDFV.effective_end_date,'MM/DD/YYYY') effective_end_date_mrd, HR_GENERAL.DECODE_LOOKUP('ACTIVE_INACTIVE', PRDFV.status) status_mrd, (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'PAY_TMPLT_ET_TIME_DEFINITION' AND language = 'US' AND lookup_code = PRDFV.default_rtn_periodicity) default_rtn_periodicity_mrd, (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'PAY_TMPLT_ET_TIME_DEFINITION' AND language = 'US' AND lookup_code = PRDFV.default_calc_periodicity) default_calc_periodicity_mrd, HR_GENERAL.DECODE_LOOKUP('PAY_RATE_MIN_MAX_TYPES',PRDFV.min_type) min_type_mrd, Case WHEN PRDFV.min_type = 'VAL' THEN PRDFV.min_value WHEN PRDFV.min_type = 'RD' THEN (SELECT NAME FROM PAY_RATE_DEFINITIONS_F_VL PRD WHERE PRD.RATE_DEFINITION_ID = PRDFV.min_value AND sysdate BETWEEN PRD.EFFECTIVE_START_DATE AND PRD.EFFECTIVE_END_DATE) ELSE (SELECT NAME FROM PAY_VALUE_DEFINITIONS_VL PVDV WHERE VALUE_DEFN_ID = PRDFV.min_value AND SYSDATE BETWEEN PVDV.EFFECTIVE_sTART_dATE AND PVDV.EFFECTIVE_END_dATE ) end min_value_mrd, HR_GENERAL.DECODE_LOOKUP('PAY_RATE_MIN_MAX_TYPES',PRDFV.max_type) max_type_mrd, Case WHEN PRDFV.max_type = 'VAL' THEN PRDFV.max_value WHEN PRDFV.max_type = 'RD' THEN (SELECT NAME FROM PAY_RATE_DEFINITIONS_F_VL PRD WHERE PRD.RATE_DEFINITION_ID = PRDFV.max_value AND sysdate BETWEEN PRD.EFFECTIVE_START_DATE AND PRD.EFFECTIVE_END_DATE) ELSE (SELECT NAME FROM PAY_VALUE_DEFINITIONS_VL PVDV WHERE VALUE_DEFN_ID = PRDFV.max_value AND SYSDATE BETWEEN PVDV.EFFECTIVE_sTART_dATE AND PVDV.EFFECTIVE_END_dATE ) end max_value_mrd, (select formula_name from FF_FORMULAS_VL where formula_id = PRDFV.conversion_formula_id) conversion_formula_id_mrd, PRDFV.return_precision return_precision_mrd, HR_GENERAL.DECODE_LOOKUP('ORA_PAY_RATE_ROUNDING',PRDFV.return_rounding ) return_rounding_mrd, NVL((SELECT description from PSC_COM_CURRENCY_VL where currency_code = PRDFV.default_rtn_currency),DECODE(PRDFV.default_rtn_currency,'GBP','Pound Sterling')) default_rtn_currency_mrd, HR_GENERAL.DECODE_LOOKUP('PAY_RATE_FACTOR_TYPES',PRDFV.return_factor_type) return_factor_type_mrd, Case WHEN PRDFV.return_factor_type = 'VAL' THEN PRDFV.return_factor_value ELSE (SELECT NAME FROM PAY_VALUE_DEFINITIONS_VL PVDV WHERE VALUE_DEFN_ID = PRDFV.return_factor_value AND SYSDATE BETWEEN PVDV.EFFECTIVE_sTART_dATE AND PVDV.EFFECTIVE_END_dATE ) end return_factor_value_mrd, PRDFV.Storage_type storage_type_mrd, DECODE(PRDFV.Base_salary_flag,'Y','Yes','N','No') base_salary_flag_mrd, DECODE(prdfv.overall_salary_flag,'Y','Yes','N','No') overall_salary_flag_mrd, DECODE(PRDFV.reporting_rate_flag,'Y','Yes','N','No') reporting_rate_flag_mrd, HR_GENERAL.DECODE_LOOKUP('ORA_PAY_RATE_LIMIT_MODE',prdfv.limit_mode) limit_violation_action_mrd, DECODE(prdfv.fte_flag,'Y','Yes','N','No') fte_rate_mrd, (SELECT ELEMENT_NAME FROM PAY_ELEMENT_TYPES_VL PETV WHERE PETV.ELEMENT_TYPE_ID = PRDFV.ELEMENT_TYPE_ID AND sysdate BETWEEN PETV.EFFECTIVE_START_DATE AND PETV.EFFECTIVE_END_DATE ) Element_Name_MRD , DECODE(PRDF.USE_REPORT_VALUE_FOR_CALC,'Y','No','N','Yes') CAL_LIVE_RATE_mrd, (select HR_GENERAL.DECODE_LOOKUP('PAY_DIMENSION_LEVEL',dimension_level) from PAY_BALANCE_DIMENSIONS where balance_dimension_id =PRDFV.DEFAULT_BAL_DIMENSION_ID) EMP_LEVEL_mrd, /* Rate Contributor Section*/ (select MEANING FROM HR_LOOKUPS WHERE LOOKUP_CODE = prcf.contributor_type AND LOOKUP_TYPE = 'ORA_ELE_RATE_CONTRIBUTOR_TYPES') contributor_type_mrd, Case WHEN prcf.contributor_type = 'S' THEN (SELECT BALANCE_NAME FROM PAY_BALANCE_TYPES_VL PBTV WHERE BALANCE_TYPE_ID = PRCF.BALANCE_TYPE_ID ) WHEN prcf.contributor_type = 'RD' THEN (SELECT NAME FROM PAY_RATE_DEFINITIONS_F_VL PRD WHERE PRD.RATE_DEFINITION_ID = PRCF.CONTRIBUTOR_ID AND sysdate BETWEEN PRD.EFFECTIVE_START_DATE AND PRD.EFFECTIVE_END_DATE) ELSE null END CONTRIUTOR_NAME_mrd, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_CODE = PRCF.PERIODICITY AND LOOKUP_TYPE = 'PAY_PROC_PERIOD_TYPE') contributor_PERIODICITY_mrd, (select MEANING FROM HR_LOOKUPS WHERE LOOKUP_CODE =PRCF.feed AND LOOKUP_TYPE ='PAY_ADD_SUBTRACT') Add_or_Sub_mrd, to_char(prcf.effective_start_date, 'MM/DD/YYYY') contributor_start_date_mrd, to_char(prcf.effective_end_date, 'MM/DD/YYYY') contributor_end_date_mrd, (select DEFINITION_NAME FROM PAY_TIME_DEFINITIONS WHERE TIME_dEFINITION_ID = PRCF.AS_OF_DATE) CONTRI_REF_DATE_MRD, (select HR_GENERAL.DECODE_LOOKUP('PAY_DIMENSION_LEVEL',dimension_level) from PAY_BALANCE_DIMENSIONS where balance_dimension_id =PRCF.BALANCE_DIMENSION_ID) CONTRI_EMP_LEVEL_mrd, HR_GENERAL.DECODE_LOOKUP('ORA_PAY_RATE_CONT_FACTOR_TYPES',PRCF.factor_type) CONTRI_factor_type_mrd, Case WHEN prcf.FACTOR_TYPE = 'V' THEN prcf.FACTOR_VALUE ELSE (SELECT NAME FROM PAY_VALUE_DEFINITIONS_VL PVDV WHERE VALUE_DEFN_ID = PRCF.FACTOR_VALUE AND SYSDATE BETWEEN PVDV.EFFECTIVE_sTART_dATE AND PVDV.EFFECTIVE_END_dATE ) end CONTRI_FACTOR_VALUE, NULL CONTRI_FACTOR_CRITERIA_VALUE, HR_GENERAL.DECODE_LOOKUP('PAY_RATE_MIN_MAX_TYPES',prcf.min_type) contri_min_type_mrd, Case WHEN prcf.min_type = 'VAL' THEN prcf.min_value WHEN prcf.min_type = 'RD' THEN (SELECT NAME FROM PAY_RATE_DEFINITIONS_F_VL PRD WHERE PRD.RATE_DEFINITION_ID = prcf.min_value AND sysdate BETWEEN PRD.EFFECTIVE_START_DATE AND PRD.EFFECTIVE_END_DATE) ELSE (SELECT NAME FROM PAY_VALUE_DEFINITIONS_VL PVDV WHERE VALUE_DEFN_ID = prcf.min_value AND SYSDATE BETWEEN PVDV.EFFECTIVE_sTART_dATE AND PVDV.EFFECTIVE_END_dATE ) end contri_min_value_mrd, HR_GENERAL.DECODE_LOOKUP('PAY_RATE_MIN_MAX_TYPES',prcf.max_type) contri_max_type_mrd, Case WHEN prcf.max_type = 'VAL' THEN prcf.max_value WHEN prcf.max_type = 'RD' THEN (SELECT NAME FROM PAY_RATE_DEFINITIONS_F_VL PRD WHERE PRD.RATE_DEFINITION_ID = prcf.max_value AND sysdate BETWEEN PRD.EFFECTIVE_START_DATE AND PRD.EFFECTIVE_END_DATE) ELSE (SELECT NAME FROM PAY_VALUE_DEFINITIONS_VL PVDV WHERE VALUE_DEFN_ID = prcf.max_value AND SYSDATE BETWEEN PVDV.EFFECTIVE_sTART_dATE AND PVDV.EFFECTIVE_END_dATE ) end contri_max_value_mrd, HR_GENERAL.DECODE_LOOKUP('ORA_PAY_RATE_LIMIT_MODE',PRCF.limit_mode) contri_limit_vio_action_mrd, DECODE(PRCF.fte_flag,'Y','Yes','N','No') contri_fte_rate_mrd, (SELECT BALANCE_NAME FROM PAY_BALANCE_TYPES_VL PBTV WHERE BALANCE_TYPE_ID = PRCF.DIVISIONAL_BAL_TYPE_ID ) CONTRI_DIV_BAL_MRD From PAY_RATE_DEFINITIONS_F_VL PRDFV, PAY_RATE_DEFINITIONS_F PRDF , PAY_RATE_CONTRIBUTORS_F PRCF WHERE sysdate BETWEEN PRDFV.EFFECTIVE_START_DATE AND PRDFV.EFFECTIVE_END_DATE AND PRDFV.RATE_DEFINITION_ID = PRDF.RATE_DEFINITION_ID AND sysdate BETWEEN PRDF.EFFECTIVE_START_DATE AND PRDF.EFFECTIVE_END_DATE AND PRCF.RATE_DEFINITION_ID = PRDFV.RATE_DEFINITION_ID AND sysdate BETWEEN PRCF.EFFECTIVE_START_DATE AND PRCF.EFFECTIVE_END_DATE AND ( (:P_CREATION_TYPE= 'Custom' and PRDF.created_by <> 'SEED_DATA_FROM_APPLICATION' ) OR (:P_CREATION_TYPE = 'Seeded' and PRDF.created_by = 'SEED_DATA_FROM_APPLICATION' ) OR (:P_CREATION_TYPE = 'Seeded Modified' and PRDF.created_by = 'SEED_DATA_FROM_APPLICATION' and PRDF. last_updated_by <> 'SEED_DATA_FROM_APPLICATION') OR (:P_CREATION_TYPE = 'ALL') )