In this article, lets look into the Payroll table changes that were introduced in 24A update and also look into modifying the reports/valuesets impacted.
| Tables used before 24A | Replacement View to be used from 24A | View Description |
| PAY_RANGE_ITEMS_F | PAY_RANGE_DEFS_V | Retrieves definition (set-up) details for the range item. |
| PAY_RANGE_ITEMS_F | PAY_RANGE_INST_V | Retrieves range item details for an individual employee. This includes employee level overrides such as tax overrides. |
| PAY_VALUE_DEFINITIONS_F | PAY_VALUE_DEFS_V | Retrieves details of a value definition. For example, retrieve set-up details for a value definition such as a timecard, absence, pension, or tax definition. |
| PAY_VALUE_DEFINITIONS_F | PAY_VALUE_INSTANCES_V | Retrieves value definition details for an individual employee. |
The model for value definitions and range items has been updated to deliver performance enhancements for high-volume payroll processes and reports. In Update 24A, all data for value definitions and range items has been migrated to the new model.
It is recommended that we update all user-defined features to use these new views. For example, all Value Sets and BI Publisher reports that retrieve information directly from the PAY_VALUE_DEFINITIONS_F or PAY_RANGE_ITEMS_F tables should be updated to use these views.
In 23D the tables PAY_RANGE_ITEMS_F and PAY_VALUE_DEFINITIONS_F contain both setup/configuration data and transaction data. However, from 24A they will become views. New tables are being created to separate the setup/configuration data from the ongoing transaction data.
The 24A views for pay_range_items_f and pay_value_definitions_f are provided for backward compatibility. This ensures that nothing fundamentally breaks but the views cannot be as efficient as they were as tables.
Instead of referencing PAY_RANGE_ITEMS_F, Oracle recommends changing the custom code to use either PAY_RANGE_DEFS_V (setup/configuration data) or PAY_RANGE_INST_V (transaction data).
Similarly references to PAY_VALUE_DEFINITIONS_F can be changed to PAY_VALUE_DEFS_V (setup/configuration data) or PAY_VALUE_INSTANCES_V (transaction data)
Lastly, should also review the use of PAY_VALUE_DEFINITIONS_VL and replace it with PAY_VALUE_DEFS_V (setup/configuration data) or PAY_VALUE_INSTANCES_V (transaction data).
There isn’t an easy way to get the BI Reports impacted by this table changes and we will have to manually verify references of these tables and modify them accordingly.
Table of Contents
Query to identify impacted value sets:
SELECT 'select /* VSet '||replace(fvs.value_set_code,'/')||' */
'||TO_CLOB(fvt.id_column_name)||' as code,'||fvt.value_column_name||' as
meaning '||
'from '||fvt.from_clause||' '||
decode(fvt.where_clause,NULL,'','WHERE '||fvt.where_clause)||'
'||
decode(fvt.order_by_clause,NULL,'','ORDER BY
'||fvt.order_by_clause) AS sqltxt,
fvs.security_enabled_flag
FROM fusion.FND_VS_VALUE_SETS fvs,
fusion.FND_VS_VT_TABLE_EXT fvt
WHERE fvs.validation_type='TABLE'
AND fvs.value_set_id = fvt.value_set_id
AND fvt.id_column_type IN ('NUMBER','VARCHAR2')
AND fvt.value_column_type in ('NUMBER','VARCHAR2')
AND fvt.value_attributes_tbl_alias IS NULL
AND ((upper(fvt.from_clause) like '%PAY_VALUE_DEFINITIONS%'
OR upper(fvt.from_clause) like '%PAY_RANGE_ITEMS_F%')
OR (upper(fvt.where_clause) like '%PAY_VALUE_DEFINITIONS%'
OR upper(fvt.where_clause) like '%PAY_RANGE_ITEMS_F%'))
and fvs.value_set_code not like 'ORA_%'
Query to identify the Fast Formulas which use the impacted value sets:
select ftv.formula_type_name
, ffv.formula_name
, ffv.effective_start_date
from fusion.ff_formula_types_vl ftv
, fusion.ff_formulas_vl ffv
, fusion.ff_fdi_usages fdi
where ftv.formula_type_id = ffv.formula_type_id
and ffv.formula_id = fdi.formula_id
and ffv.effective_start_date = fdi.effective_start_date
and ffv.effective_end_date = fdi.effective_end_date
and upper(fdi.item_name) like 'GET_VALUE_SET%'
and ffv.formula_text like '%<valueset name>%'
and fdi.usage = 'F'
Once we identify the impacted valuesets and reports, we need to modify them with the replacement views. If we do not modify them, then also the reports might work but they will face performance issues and might result in timeout errors.
Sample Valueset changes:
23D Valueset:
select r.value1 as code,r.value1 as
meaning from pay_value_definitions_vl vd, pay_range_items_f r, FND_CAL_DAY CALD WHERE vd.name ='Canada Pension Plan Maximum Pensionable Earnings'
and r.value_defn_id = vd.value_defn_id
and CALD.REPORT_DATE between vd.effective_start_date and vd.effective_end_date
and CALD.REPORT_DATE between r.effective_start_date and r.effective_end_date
AND CALD.REPORT_DATE = TO_DATE(:{PARAMETER.P_ST_DT},'YYYY-MM-DD')24A Modified Valueset:
select r.value1 as code,r.value1 as meaning
from PAY_VALUE_DEFS_V vd, PAY_RANGE_DEFS_V r, FND_CAL_DAY CALD
WHERE vd.name ='Canada Pension Plan Maximum Pensionable Earnings'
and r.value_defn_id = vd.value_defn_id
and CALD.REPORT_DATE between vd.effective_start_date and vd.effective_end_date
and CALD.REPORT_DATE between r.effective_start_date and r.effective_end_date
AND CALD.REPORT_DATE = TO_DATE(:{PARAMETER.P_ST_DT},'YYYY-MM-DD')As the above is a config and not transactional data, we have used PAY_VALUE_DEFS_V and PAY_RANGE_DEFS_V views.
A Valueset retrieving employee-level data would be selected based on source_id and source_type.
2 A Valueset accessing the definition data would not reference source_id and would likely be selected on the name or base name.
3. When changing references to PAY_VALUE_INSTANCES_V please note that
PAY_VALUE_DEFS_VL contains the name and base name
PAY_VALUE_INSTANCES_V contains just the base name
For example a query using source_id but also using the translated name:
SELECT… FROM PAY_VALUE_DEFINITIONS_VL
WHERE name = ‘Exempt from Medicare’
AND source_id = …
Should change to:
SELECT… FROM PAY_VALUE_INSTANCES_V
WHERE base_name = ‘ORA_HRX_US_FF_FMT_EXEMPT’
AND source_id = …
To find the corresponding base name:
SELECT base_name from PAY_VALUE_DEFS_VL
Where name = ‘<name>’
Sample BI Report Changes:
23D Report Query:
select
vdl.name ,
pri.value1 ,
pri.effective_start_date effective_start_date ,
pri.effective_end_date effective_end_date ,
pcrc.tax_unit_id ,
ptru.estab_name gre ,
pcd.payroll_relationship_id ,
ppnf.full_name last_updated_by
from
pay_dir_cards_f pcd ,
pay_dir_card_definitions_vl pdcd ,
pay_dir_card_comp_defs_vl pdccd ,
pay_dir_rep_cards_f pcrc ,
pay_dir_card_components_f pdcc ,
pay_dir_override_usages_f dou ,
pay_allow_overrides_vl aor ,
pay_range_items_f pri ,
pay_value_definitions_vl vdl ,
per_tax_reporting_units ptru ,
per_users pu ,
per_person_names_f ppnf
where
pcd.dir_card_definition_id = pdcd.dir_card_definition_id
AND pcrc.dir_card_id = pcd.dir_card_id
AND vdl.source_id = pdcc.dir_card_comp_id
AND vdl.dir_override_usage_id = dou.dir_override_usage_id
AND dou.allow_overrides_id = aor.allow_overrides_id
AND pri.value_defn_id = vdl.value_defn_id
AND pdccd.dir_card_comp_def_id = pdcc.dir_card_comp_def_id
AND pdcc.dir_card_id = pcd.dir_card_id
AND ptru.organization_id = pcrc.tax_unit_id
AND pu.person_id = ppnf.person_id
AND pu.username = pri.last_updated_by
AND vdl.source_type = 'PDCC'
AND ppnf.name_type = 'GLOBAL'
AND pdcd.base_display_name = 'US_EMPLOYEE_WITHHOLDING_CERTIFICATE'
AND pdccd.base_component_name = 'ORA_HRX_US_WTH_FEDERAL'
AND TRUNC(PRI.last_update_date) BETWEEN TRUNC(:P_DATE_FROM) AND TRUNC(:P_DATE_TO)
AND TRUNC(:P_DATE_TO) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND vdl.name IN( 'Filing Status' ,
'Exempt from Federal Income Tax' ,
'Multiple Jobs' ,
'Allowance' ,
'Additional Tax Amount' ,
'Qualifying Dependents Amount' ,
'Other Dependents Amount' ,
'Total Dependents Amount' ,
'Other Income Amount' ,
'Deductions Amount')24A modified Report Query:
select
vdl.name ,
pri.value1 ,
pri.effective_start_date effective_start_date ,
pri.effective_end_date effective_end_date ,
pcrc.tax_unit_id ,
ptru.estab_name gre ,
pcd.payroll_relationship_id ,
ppnf.full_name last_updated_by
from
pay_dir_cards_f pcd ,
pay_dir_card_definitions_vl pdcd ,
pay_dir_card_comp_defs_vl pdccd ,
pay_dir_rep_cards_f pcrc ,
pay_dir_card_components_f pdcc ,
pay_dir_override_usages_f dou ,
pay_allow_overrides_vl aor ,
PAY_RANGE_INST_V pri ,
PAY_VALUE_INSTANCES_V vdl ,
per_tax_reporting_units ptru ,
per_users pu ,
per_person_names_f ppnf
where
pcd.dir_card_definition_id = pdcd.dir_card_definition_id
AND pcrc.dir_card_id = pcd.dir_card_id
AND vdl.source_id = pdcc.dir_card_comp_id
AND vdl.dir_override_usage_id = dou.dir_override_usage_id
AND dou.allow_overrides_id = aor.allow_overrides_id
AND pri.value_defn_id = vdl.value_defn_id
AND pdccd.dir_card_comp_def_id = pdcc.dir_card_comp_def_id
AND pdcc.dir_card_id = pcd.dir_card_id
AND ptru.organization_id = pcrc.tax_unit_id
AND pu.person_id = ppnf.person_id
AND pu.username = pri.last_updated_by
AND vdl.source_type = 'PDCC'
AND ppnf.name_type = 'GLOBAL'
AND pdcd.base_display_name = 'US_EMPLOYEE_WITHHOLDING_CERTIFICATE'
AND pdccd.base_component_name = 'ORA_HRX_US_WTH_FEDERAL'
AND TRUNC(PRI.last_update_date) BETWEEN TRUNC(:P_DATE_FROM)
AND TRUNC(:P_DATE_TO)
AND TRUNC(:P_DATE_TO) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND vdl.base_name IN ('ORA_HRX_US_FF_FIT_FEDERAL_FILING_STATUS' ,
'ORA_HRX_US_FF_FIT_EXEMPT' ,
'ORA_HRX_US_FIT_MULTIPLE_JOBS' ,
'ORA_HRX_US_VD_FIT_ALLOWANCE' ,
'ORA_HRX_US_VD_FIT_ADDITIONAL_TAX' ,
'ORA_HRX_US_FIT_QUALIFYING_DEPENDENT' ,
'ORA_HRX_US_FIT_OTHER_DEPENDENT' ,
'ORA_HRX_US_FIT_TOTAL_DEPENDENT' ,
'ORA_HRX_US_FIT_NONWAGE_INCOME' ,
'ORA_HRX_US_FIT_DEDUCTIONS_AMOUNT' ,
'ORA_HRX_US_FF_FIT_FEDERAL_FILING_STATUS')As we can see from above, the view PAY_VALUE_INSTANCES_V, doesn’t have NAME field, we have to get the BASE_NAME value for all the names and then add them in the criteria.
Hope this provides a good overview of the payroll table changes in 24A.
