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