DateTrack History is available in most windows where you can enter date tracked information. DateTrack History enables you to track changes made to records and fields, and by whom. You can select the fields you want to focus on and view the changed values in those fields over time.
DateTrack History is available from a button on the toolbar.
You can create new views or modify existing views to customize the information displayed. You can:
Create a view to join to other tables. This enables you to use a meaningful table name as a column header. By contrast, the base table can only display an ID of another table.
Determine the fields to display, by modifying the views.
Modify views to display column names aliases for the meaningful names you have defined for descriptive flexfield segments.
Determine which view to use dependent on criteria of your choice, such as the Business Group ID.
When you request DateTrack History, Oracle HRMS extracts the information from one of three sources. The application looks first for the alternative view specified by the custom library and if one exists, extracts the information from there. If there isn't an alternative view specified, it looks next for a default DateTrack History view from which to extract the information, and if that doesn't exist, it extracts the information from the base table. It then displays the information in the DateTrack History Change Field Summary window.
The name of the default DateTrack History view is the same as that of the base table, except that the suffix _F is replaced by _D. For example, if the base table is PER_ALL_PEOPLE_F, the application looks for a view called PER_ALL_PEOPLE_D.
Note: It is possible to define more than one History view for each datetracked table, so there might be examples where the History view name does not follow this naming convention.
When a view exists, the application reads the information about the entity name and column prompts from the DateTrack tables:
DT_TITLE_PROMPTS_TL
DT_DATE_PROMPTS_TL
DT_COLUMN_PROMPTS_TL
If the column information is not available in the DT_COLUMN_PROMPTS_TL table, the information is obtained from the view definition. The DateTrack History code modifies the column names of the table or view before presenting them. Underscores are replaced by spaces and the first letter of each word appears in upper case.
DateTrack History views should have the same name as the corresponding base table, wherever possible, except that the suffix _F is replaced by _D. If you are using custom library to specify an alternative view, the view name is different, but you should still use the _D suffix.
All views must contain the following columns:
The primary key of the base table
The effective start date of the base table
The effective end date of the base table
The last updated date column
The last updated by column (obtain the actual user name by an outer join to FND_USER_VIEW).
Note: There is a limit of 35 columns in Date Track History views. The primary key, effective start date, and effective end date columns must be present in the view but cannot be seen in the DateTrack History windows.
Do not edit the supplied DateTrack History view creation scripts. If you want to customize the supplied DateTrack History views, copy the scripts and modify the copies instead. After an upgrade, you should check that your customizations are consistent with the new views supplied with the upgrade. If so, you can rerun your customized view creation scripts to recreate your customized views.
Adding an additional column to DateTrack History views can affect the column order, and if you have previously saved folders, the data displayed and the prompts might no longer match up. This is because the Date Track History Change Field Summary window displays the column names in alphabetical order, but with the effective date values in the first two columns.
We recommend that you update any folder definitions straight after you apply the new view to the database, otherwise the data displayed and the prompts in folders might not match up in future.
In this example, the base table is PAY_GRADE_RULES_F.
create or replace view pay_grade_rules_d
(grade_rule_id,
effective_start_date,
effective_end_date,
maximum,
mid_value,
minimum,
grade,
rate_type,
last_update_date,
last_updated_by)
AS
select GRULE.grade_rule_id,
GRULE.effective_start_date,
GRULE.effective_end_date,
GRULE.maximum,
GRULE.mid_value,
GRULE.minimum,
GRADE.name,
HR1.meaning,
GRULE.last_update_date,
FUSER.user_name
from pay_grade_rules_f GRULE
, per_grades GRADE
, hr_lookups HR1
, fnd_user_view FUSER
where GRADE.grade_id = GRULE.grade_or_spinal_point_id
and HR1.lookup_code (+)= GRULE.rate_type
and HR1.lookup_type (+)= 'RATE_TYPE'
and FUSER.user_id (+)= GRULE.last_updated_by
Before the DateTrack History Change Field Summary window displays, the system calls a custom library event (called DT_ CALL_HISTORY). It passes in details of the current record and which DateTrack view the product normally uses. You can write custom code to change the name of the view DateTrack History should use. Your code can include IF statements that determine which view to use in different circumstances.
Note: It is your responsibility to ensure that the alternative view exists in your database and the relevant users have select access to it.
For each additional view, you need to insert extra rows into the DT_TITLE_PROMPTS_TL and DT_COLUMN_PROMPTS_TL tables, based on the view name. Use SQL*Plus scripts to maintain the extra table contents and view definitions.
The following global variables can used at the DT_CALL_HISTORY event. They are not available at any other CUSTOM library event.
| Global Variable Name | Read/Write | Description |
|---|---|---|
| g_dt_basetable | Read only | Name of the database table where the data is held. For example: PER_ALL_PEOPLE_F |
| g_dt_uidfield | Read only | Name of the surrogate ID on the database table. For example: PERSON_ID |
| g_dt_uidvalue | Read only | The surrogate ID value for the current record. |
| g_dt_alternative_history_view | Read and Write | Usually DateTrack History queries the history data from a database view that has the same name as the database table, except the _F suffix is changed to _D. In that case this global variable is null. For example when the database table is PER_ALL_PEOPLE_F, the PER_ALL_PEOPLE_D view is used. If you want to use a different view, set this global variable to the actual view name (even if the variable is initially null). |
To enable the DT_CALL_HISTORY event add the following code to the STYLE procedure in the CUSTOM package, CUSTOM library:
if event_name = 'DT_CALL_HISTORY' then
return custom.after;
else
return custom.standard;
end if;
Suppose you want to use a different view whenever the standard product would normally use the PER_ALL_PEOPLE_D view. Add the following code to the EVENT procedure in the CUSTOM package, CUSTOM library:
if (event_name = 'DT_CALL_HISTORY') then
if name_in('global.g_dt_basetable') = 'PER_ALL_PEOPLE_F' then
copy
('NAME_OF_OTHER_VIEW'
,'global.g_dt_alternative_history_view'
);
end if;
end if;
The supplied views and view creation scripts are as follows:
| View Name | Based on (table) | View Creation Script |
|---|---|---|
| BEN_BENEFIT_CONTRIBUTIONS_D | BEN_BENEFIT_CONTRIBUTIONS_F | pedtbbcf.sql |
| HXT_ADD_ASSIGN_INFO_D | HXT_ADD_ASSIGN_INFO_F | hxtdtaas.sql |
| HXT_ADD_ELEM_INFO_D | HXT_ADD_ELEM_INFO_F | hxtdtael.sql |
| HXT_SUM_HOURS_WORKED_D | HXT_SUM_HOURS_WORKED_F | hxtdtsum.sql |
| HXT_TIMECARDS_D | HXT_TIMECARDS_F | hxtdttim.sql |
| PAY_ALL_PAYROLLS_D | PAY_ALL_PAYROLLS_F | pydtpayr.sql |
| PAY_BALANCE_FEEDS_D | PAY_BALANCE_FEEDS_F | pydtbalf.sql |
| PAY_CA_EMP_FED_TAX_INFO_D | PAY_CA_EMP_FED_TAX_INFO_F | pycadtfd.sql |
| PAY_CA_EMP_PROV_TAX_INFO_D | PAY_CA_EMP_PROV_TAX_INFO_F | pycadtpv.sql |
| PAY_COST_ALLOCATIONS_D | PAY_COST_ALLOCATIONS_F | pydtpcst.sql |
| PAY_ELEMENT_LINKS_D | PAY_ELEMENT_LINKS_F | pydtelin.sql |
| PAY_ELEMENT_TYPES_D | PAY_ELEMENT_TYPES_F | pydtetyp.sql |
| PAY_FORMULA_RESULT_RULES_D | PAY_FORMULA_RESULT_RULES_F | pydtfmrr.sql |
| PAY_GRADE_RULES_D | PAY_GRADE_RULES_F | pydtgrdt.sql |
| PAY_INPUT_VALUES_D | PAY_INPUT_VALUES_F | pydtinpv.sql |
| PAY_LINK_INPUT_VALUES_D | PAY_LINK_INPUT_VALIES_F | pydtliiv.sql |
| PAY_ORG_PAYMENT_METHODS_D | PAY_ORG_PAYMENT_METHODS_F | pydtpaym.sql |
| PAY_PERSONAL_PAYMENT_METHODS_D | PAY_PERSONAL_PAYMENT_METHODS_F | pydtppym.sql |
| PAY_STATUS_PROCESSING_RULES_D | PAY_STATUS_PROCESSING_RULES_F | pydtstpr.sql |
| PAY_USER_COLUMN_INSTANCES_D | PAY_USER_COLUMN_INSTANCES_F | pydtucin.sql |
| PAY_USER_ROWS_D | PAY_USER_ROWS_F | pydtussrr.sql |
| PER_ALL_ASSIGNMENTS_D | PER_ALL_ASSIGNMENTS_F | pedtasgn.sql |
| PER_ALL_PEOPLE_D | PER_ALL_PEOPLE_F | pedtpepl.sq |
| PER_ASSIGNMENT_BUDGET_VALUES_D | PER_ASSIGNMENT_BUDGET_VALUES_F | pedtabv.sql |
| PER_COBRA_COVERAGE_BENEFITS_D | PER_COBRA_COVERAGE_BENEFITS_F | pedtccbf.sql |
| PER_GRADE_SPINES_D | PER_GRADE_SPINES_F | pedtgrsp.sql |
| PER_SPINAL_POINT_PLACEMENTS_D | PER_SPINAL_POINT_PLACEMENTS_F | pedtsppp.sql |
| PER_SPINAL_POINT_STEPS_D | PER_SPINAL_POINT_STEPS_F | pedtspst.sql |
| PER_PERSON_TYPE_USAGES_D | PER_PERSON_TYPE_USAGES_F | pedtptu.sql |
| PER_CONTRACTS_D | PER_CONTRACTS_F | pedtctc.sql |