Skip to main content
insightsoftware Documentation insightsoftware Documentation
{%article.title%}
Published:
Was this article helpful?
0 out of 0 found this helpful

NoetixViews for Oracle Human Resources

NoetixViews for Oracle Human Resources involves some special considerations that are not applicable to other Noetix Views (NoetixViews) products.

NoetixViews for Oracle Human Resources provides more than 65 views of Oracle E-Business Suite Human Resources Management System (HRMS) data based on the specific configuration at your site. The exact number of views at your site depends on how many special information types (SITs) you have defined and/or how many extra information types (EITs) are installed at your site. For example, many NoetixViews for Oracle Human Resources customers have more than 100 views.

Each view accesses a predefined set of fields in the database, assigns the data to column names, and provides the user with a variety of ways to view data. The product is a tool for the end user to assist in day-to-day Human Resources functions, such as employee management, recruitment, and payroll processes.

To optimize the product’s performance and maintain the confidentiality of data, those involved in the generation of views should be familiar with the concepts in this section.

The views for Human Resources are of two types: standard and global. Standard views for Human Resources return data for a single business group, and a Noetix role is generated for each detected business group. In contrast, global views for Human Resources provide access to all detected business groups in an instance of Oracle E-Business Suite. While supporting multiple structures of key flexfields, global views can be accessed through a single set of roles.

About Access to Human Resources Data

This section provides information about the roles for NoetixViews for Oracle Human Resources and the security policies supported by those views.

Noetix Roles for Human Resources

NoetixViews uses database roles to group related views and simplify security administration. A Noetix query user who is granted a role can then query all views in that role.

For standard views, the following roles are generated for each detected business group:

  • HR HUMAN RESOURCES: Includes views that return non-confidential data, confidential data (except for salary data), and data for extra information types (EITs) and special information types (SITs). Human Resources supervisors and those who require broad access to Human Resources data should be granted this role.

  • HR MANAGER: Includes views that return nonconfidential data and confidential data (except for salary data). Managers who require limited access to Human Resources data should be granted this role.

  • HR SALARY MANAGER: Includes views that return confidential salary data.

  • HR EXTRA INFO TYPES: Includes views that return data for EITs.

  • HR SPECIAL INFO TYPES: Includes views that return data for SITs.

  • HR USER: Includes views that return nonconfidential data.

For global views, only one set of these roles with the default, configurable prefix of HRG0 will be generated.

Security Policies in Views for HRMS

The views for Oracle Advanced Benefits, Human Resources, Oracle Payroll, and Oracle Time and Labor support the following kinds of security policies:

  • Application-Specific Security of Human Resources: This security policy is the default for Noetix query users of the Oracle E-Business Suite Authenticated User (Type A) and Oracle E-Business Suite Authenticated Responsibility (Type R) types. For these query users, application-specific security is applied in global views when no changes are made on the Business Group tab of the <Noetix query user> Properties dialog box of the NoetixViews Administrator.

    This security policy applies employee data and business group restrictions on Noetix query users that are similar to the restrictions applied on users in Human Resources. Employee data restrictions are always determined by the security profile or global security profile applied to the Noetix query users during logon.

    For standard views, the business group is hard-coded in the view and cannot be changed or overridden. The security profile defined for the Noetix query users of the Oracle E-Business Suite Authenticated User (Type A) and Oracle E-Business Suite Authenticated Responsibility (Type R) types in Oracle E-Business Suite will determine their access to data in standard views. For Noetix query users of the Database User (Type U) and Noetix System Administration User (Type N) types, the security profiles will apply only if the query users are also defined in Human Resources as reporting users for the security profiles. The security profiles cannot be overridden in standard views.

    For global views, the business group is determined by the security model and security profile or global security profile applied to the Noetix query users during logon. If the Standard Human Resources Management System security model is used in Human Resources, the business group assigned to the security profile is used or the business group assigned to the responsibility during logon with the global security profile is used. If the Security Groups Enabled security model is used, the business group is indicated by the security group used during logon. Irrespective of the security model, application-specific security of Human Resources will result in access to a single business group in the global views. For Noetix query users of the Database User (Type U) and Noetix System Administration User (Type N) types, application-specific security of Human Resources will apply in the global views only when these users are also specified as reporting users for security profiles in Human Resources. In such cases, access to employee data will be determined by the security profiles to which the reporting users are assigned.

    For information about security profiles, global security profiles, responsibilities, and security groups, see the Oracle documentation.

  • Custom Security: Custom security is available for only certain global views for Advanced Benefits, Human Resources, Payroll, and Time and Labor. Custom security is established when application-specific security of Human Resources is overridden through settings on the Business Group tab of the <Noetix query user> Properties dialog box. Through custom security, you can override the employee data restrictions of security profiles and global security profiles, allow access to all business groups within the organization hierarchy of a global security profile, and create custom lists of accessible business groups.

The following table indicates how the settings in Human Resources and on the Business Group tab of the <Noetix query user> Properties dialog box collectively secure data in global views for Advanced Benefits, Human Resources, Payroll, and Time and Labor.

Table 1: How HRMS Data is Kept Secure

At logon, when the Noetix query user has a

And it is

The global view will return

Security profile

Not enforced by NoetixViews

All employee data in all business groups in the Assigned Business Groups list on the Business Group tab

Security profile

Enforced by NoetixViews

Employee data restricted by the security profile and the business group assigned to the security profile when the Standard HRMS security model is used so long as the business group is also in the Assigned Business Groups list on the Business Group tab

Or

Employee data restricted by the security profile and the business group for the security group used during logon when the Security Groups Enabled security model is used so long as the business group for the security group is also in the Assigned Business Groups list on the Business Group tab

Global security profile

Enforced by NoetixViews

All employee data in all business groups in the Assigned Business Groups list on the Business Group tab

Global

security

profile

Enforced by

NoetixViews

Employee data restricted by the global security profile and for the business group assigned to the responsibility during logon when the Standard HRMS security model is used so long as the business group is also in the Assigned Business Groups list on the Business Group tab1

Or
Employee data restricted by the global security profile and the business group for the security group used during logon when the Security Groups Enabled security model is used so long as the business group for the security group is also in the Assigned

Business Groups list on the Business Group tabNoetixViews for Oracle Human Resources

Or
Employee data restricted by the global security profile and for all business groups in the organization hierarchy of the global security profile so long as the business groups are also in the Assigned Business Groups list on theBusiness Group tab2

For information about how to control access to data in global views for Advanced Benefits, Human Resources, Payroll, and Time and Labor, see Add a Noetix Query User Using an Existing User in “Noetix Query User Management.”

The following table lists the Noetix views pertaining to Human Resources that provide secured or customizable access to data.

Table 2: NoetixViews for Oracle Human Resources

View name

Human Resources security profile applied at:

HR Accrual Pln Hist

Any level

HR Address Hist

Person and Organization levels

HR AP 1099 Payments

(available for only United States legislation)

Person level

HR Applicant Hist

Person, Assignment, and Vacancy levels

HR Ben Elig Info

Position, Person, and Assignment levels

HR Budgets

Position level

HR Carrier Asg Hist

Security level

HR COBRA Prem Stat

(available for only United States legislation)

Person level

HR COBRA Track

(available only for United States legislation)

Person level

HR Contact Hist

Person level

HR Contingent Worker Info

Person and Assignment levels

HR EI Academic Rank

Person level

HR EI Asg Ben Derived

Person level

HR EI Asg Federal

Person level

HR EI Asg Locality

Person level

HR EI Asg Types

Position, Person, and Assignment levels

HR EI GHR Probations

Person level

HR EI GHR Sep Retire

Person level

HR EI Job Types

Any level

HR EI Loc Types

Any level

HR EI Per Types

Person level

HR EI Pos Types

Any level

HR EI US Add Details

(available for only United States legislation)

Person level

HR EI US Passport Dtls

(available for only United States legislation)

Person level

HR EI US Visa Dtls

(available for only United States legislation)

Person level

HR Element Links

Person, Organization, and Payroll level

HR Emp Absence Hist

Person and Assignment levels

HR Emp ADA Info

(available for only United States legislation)

Person level

HR Emp Asg Details

Person and Assignment levels

HR Emp Assign Costs

Organization, Person, and Assignment levels

HR Emp Assign Costs Hist

Person and Assignment levels

HR Emp Assign Hist

Person and Assignment levels

HR Emp Ben Health

Person and Assignment levels

HR Emp Ben Others

Person and Assignment levels

HR Emp Beneficiary

Person level

HR Emp Element Entry Vals

Person and Assignment levels

HR Emp Emergency

Person and Assignment levels

HR Emp Ethnic Info

(available for only United States legislation)

Person and Assignment levels

HR Emp Headcnt Hist

Person and Assignment levels

HR Emp Headcounts

Person and Assignment levels

HR Emp Info

Person and Assignment levels

HR Emp LOS

Person and Assignment levels

HR Emp Reviews

Person and Assignment levels

HR Emp Sal Analysis

Person and Assignment levels

HR Emp Sal Hist

Person and Assignment levels

HR Emp Sal Pro Current

Person and Assignment levels

HR Emp Sal Pro Hist

Person and Assignment levels

HR Emp Tax Details

(available for only United States legislation)

Person level

HR Emp Terms Hist

Person and Assignment levels

HR Emp Total Comp

Person and Assignment levels

HR Emp Veteran Info

(available for only United States legislation)

Person and Assignment levels

HR Emp Work Hist

Person and Assignment levels

HR Emp Xfers Hist

Person and Assignment levels

HR New Hire Hist

Organization, Person, and Assignment levels

HR Oth Headcnt Hist

Person and Assignment levels

HR Pay Scales

Any level

HR People Grp Hist

Person and Assignment levels

HR Person Hist

Person and Assignment levels

HR Phones Hist

Person level

HR Pos Hierarchies

Position level

HR Pos Requirements

Position level

HR Req Vac Track

Position and Vacancy levels

HR Schools Attended

Any level

HR SI Type

Person level

HR Turnover Hist

Organization, Person, and Assignment levels

HR Vac Job Match

Position and Vacancy levels

HR Vac Pos Match

Person, Position, and Vacancy levels

HR Accrual Pln Hist

Any level

HR Address Hist

Person and Organization levels

The following table lists the Noetix views pertaining to Advanced Benefits that provide secured or customizable access to data.

Table 3: NoetixViews for Oracle Advanced Benefits

View name

Human Resources security profile applied at:

BEN COBRA Beneficiaries

(available for only United States legislation)

Person and Assignment levels

BEN Elig Elec Enrollments

Person and Assignment levels

BEN Emp Dependents

Person level

BEN Life Evnt Workflow

Person and Assignment levels

BEN Payroll Ben Costs

Person level

BEN Potential Life Evnts

Person and Assignment levels

BEN Ptpnt Benefit Costs

Person and Assignment levels

BEN Ptpnt Communications

Person and Assignment levels

BEN Ptpnt Court Orders

Person and Assignment levels

BEN Ptpnt Electabilities

Person and Assignment levels

BEN Ptpnt Eligibilities

Person and Assignment levels

BEN Ptpnt Enroll Actions

Person and Assignment levels

BEN Ptpnt Enrollments

Person and Assignment levels

BEN Ptpnt Flex Credits

Person and Assignment levels

BEN Ptpnt Flex Spending

Person and Assignment levels

BEN Ptpnt Life Events

Person and Assignment levels

BEN Ptpnt Mthly Premiums

Person and Assignment levels

The following table lists the Noetix views pertaining to Payroll that provide secured or customizable access to data.

Table 4: NoetixViews for Oracle Payroll

View name

Human Resources security profile applied at:

PAY Accruals (available for all legislations except for Australia)

Person and Assignment levels

PAY Check Register

Payroll, Person, and Assignment levels

PAY Costing Analysis

Payroll, Person, and Assignment levels

PAY Costing Details

Organization level

PAY Costing Summary

Organization level

PAY Custom Balances
(available for all legislations except for Australia and United Kingdom)

Payroll, Person, and Assignment levels

PAY Deductions Owed

Payroll, Person, and Assignment levels

PAY Emp Not Paid

Payroll, Person, and Assignment levels

PAY Emp Not Paid Vg

Payroll, Person, and Assignment levels

PAY Gre Totals (available for only United States legislation)

Organization level

PAY Gross And Net Balances

Payroll, Person, and Assignment levels

PAY Gross To Net Summary

Organization level

PAY Hours By Cost Center

Organization level

PAY Invalid Addresses
(available for only United States legislation)

Person level

PAY Payment Methods

Person and Assignment levels

PAY Payment Register

Payroll, Person, and Assignment levels

PAY Payroll Activities

Payroll, Person, and Assignment levels

PAY Payroll Audit

Payroll, Person, and Assignment levels

PAY Payroll Messages

Organization, Payroll, Person, and Assignment
levels

PAY Payroll Proc Summary

Payroll level

PAY Run Results

Payroll, Person, and Assignment levels

PAY Tax Balances (available for all legislations except Australia and United Kingdom)

Payroll, Person, and Assignment levels

PAY Third Party Balances (available for only Canadian legislation)

Payroll, Person, and Assignment levels

PAY Third Party Register

Payroll, Person, and Assignment levels

PAY Void Payments

Payroll, Person, and Assignment levels

PAY Accruals (available for all legislations except for Australia)

Person and Assignment levels

PAY US Payroll Register
(available for only United States legislation)

Payroll, Person, and Assignment levels

PAY US W2 Register (available for only United States legislation)

Payroll, Person, and Assignment levels

The following table lists the Noetix views pertaining to Time and Labor that provide secured or customizable access to data.

Table 5: NoetixViews for Oracle Time and Labor

View name

Human Resources security profile applied at:

HXC All Assignment Hist

Person and Assignment levels
HXC All Person HistPerson level
HXC Assignment Time InfoPerson and Assignment levels
HXC BEE Batch HeadersOrganization level
HXC BEE Batch LinesPerson and Assignment levels
HXC BEE Error MessagesOrganization level
HXC PUI Latest TimecardsPerson and Assignment levels
HXC PUI Missing TimecardsPerson and Assignment levels
HXC PUI Time Entry ErrorsPerson level
HXC PUI Timecard HistoryPerson and Assignment levels
HXC SS Latest TimecardsPerson and Assignment levels
HXC SS Missing TimecardsPerson and Assignment levels
HXC SS Time Category HoursPerson level
HXC SS Timecard Action HistPerson and Assignment levels
HXC SS Timecard HistoryPerson and Assignment levels
HXC Timecard SummaryPerson and Assignment levels

SIT Views

NoetixViews for Oracle Human Resources provides the user with various views that maximize the information delivery ability of special information types (SITs) in Human Resources Management System (HRMS). SITs are a collection of personal attributes that usually provide information related to recruitment, such as specific qualifications and skills, which are predefined in the Personal Analysis key flexfield.

NoetixViews for Oracle Human Resources provides views to augment your recruitment, career development, and applicant matching capabilities by using SITs. SIT views are generated during generation based on the SITs defined and enabled at your site.

The format for each view name is HR SI Type STRUCTURE NAME. For example, the views HR SI Type Education or HR SI Type Communication Skills would each return a list of people associated with each SIT defined at your site. These may be grouped by Applicant, Employee, or Employee and Applicant Person Types. The number of HR SI Type views per site is directly related to the number of SITs that were defined in Oracle HRMS.

Views displaying current job and position requirements based on the SIT definitions are also available. The names for these views will look similar to HR Job Requirements or HR Position Requirements.

In addition, the following views are designed to assist you in matching a pool of internal and external applicants to specific vacancies at your firm (regardless of whether your organization has decided to use Job or Position functionality within Oracle HRMS): HR Vac Job Match and HR Vac Pos Match.

Note: SITs defined for Job and Position Requirements or Skills used in the Oracle Training module do not result in NoetixViews HR SI Type views being generated.

Joining to SIT Views

The information in each special information type (SIT) view can be linked to the data in the HR Person Hist view. Therefore, each SIT view contains one Z$ column to allow joins to the HR Person Hist view. You can also join an SIT view to any current view that includes a join to Z$ column for HR Person Hist.

Angles for Oracle Generator for Oracle Discoverer (Angles for Oracle Generator) automatically sets up joins between the HR SI Type views and other current views containing a join to Z$ column to HR All Person Hist, and you can do the same when configuring other query tools.

When joining an SIT view with HR Person Hist, only current results will be returned. This is because SIT views return only current information.

The use of SITs is optional. When an SIT view is joined to another view, the data returned will be limited by the attributes in the particular SIT view. This reduces the information returned from a query involving an SIT view and a join to column. For example, HR Emp Asg Details joined to HR SI Type Education would result in assignment details for all employees meeting the criteria of the special educational information designated in the SIT view. If an employee did not meet the educational criteria, that particular employee record would not be returned.

Joining views using outer joins is not a good option because of the way Oracle executes queries against views that are joined using outer join. This option only works when query criteria is limited by search by (A$) columns in both views involved in an outer join. An equijoin does not have this requirement, and only one search by (A$) column needs to be used as query criteria. (Using two could be faster, however, if query performance is required.)

Note: This special join to consideration is not true for NoetixViews for Oracle Human Resources date-tracked views (view names suffixed with “Hist”). The HR SI Type views are not historical and may cause historical information to be missing from the resulting query.

EIT Views

Extra information type (EIT) views, available with NoetixViews for Oracle Human Resources, maximize the information delivery ability of EITs in Human Resources Management System (HRMS). Introduced in Oracle E-Business Suite 11.0 and further enhanced in 11i, EITs are developer descriptive flexfields available for six key areas (person, assignment, position, job, location, and organization).

In addition, Oracle HRMS allows the addition of unlimited descriptive flexfields for further customization of each EIT. For example, a user may want to use the person EIT to display additional personal information such as passport details, visa details, visit details, etc.

NoetixViews generates views for the person and assignment EIT types and also detects any customized descriptive flexfields. Each EIT view contains only information related to the specific EIT. These simple EIT views are designed to be joined to related Human Resources views (through the use of Noetix Z$ columns) to provide more detailed data. For example, the information in the person EIT views can be linked to the data in the NoetixViews HR Person Hist view or, in addition, to any Human Resources view that includes the Z$HR Person Hist column.

Similarly, the assignment EITs can be linked to the data in the HR Emp Asg Details view or to any Human Resources view that includes the Z$HR Emp Asg Details column in NoetixViews. For more information about how to join views using Z$ columns, see the MagnitudeNoetixViews User Guide.

The format for each view name is HR EI ASSIGNMENT TYPE. For example, the person EIT view HR EI US Passport Dtls will display United States Passport Details. EIT views will be generated only for the Person and Assignment types of EITs supplied by Oracle. Person EITs are available for U.S., Global, and Government categories. Assignment EITs are available for U.S., Federal, and Great Britain categories.

Oracle DateTrack and NoetixViews

The Oracle DateTrack feature allows you to store changes in records that happen over a period of time. The DateTrack feature adds the dimension of time to an application’s database. Some tables in Human Resources use the DateTrack feature to record changes in the employee data and to preserve the historical information in an organized manner. These tables typically contain an employee’s details such as an employee’s personal, assignment or compensation details.

This section provides an overview about the DateTrack feature and information about how the feature is used in NoetixViews for Oracle Human Resources.

The DateTrack feature is also used in the Payroll and Advanced Benefits modules of the Oracle E-Business Suite.

Identifying DateTracked Records

The Oracle table that stores DateTracked information has the following attributes:

The table name ends with F. For example, PER ALL PEOPLE F.

The Effective Start Date and Effective End Date columns exist, and the values in these columns cannot be null. The Effective Start Date column indicates when the record was inserted, and the Effective End Date column indicates when the record was deleted or updated.

In most DateTracked tables, the effective dates form a part of the primary key consisting of an ID and the two effective dates. For example, in the table PER ALL PEOPLE F, Person Id + Effective Start Date + Effective End Date is the primary key.

The effective dates are contiguous. For example, if the effective start date of the current record is 16-JAN-2000, then the effective end date of the previous record will be 15-JAN-2000.

The effective dates do not have any time stamps, and, thus, there can only be one record per day.

Following is an example of a DateTracked table.

Emp No

Name

Effective Start Date

Effective End Date

Salary

E101

James Brown

01-Jan-1990

31-Dec-1995

15,500

E101

James Brown

01-Jan-1996

31-Jul-1998

18,500

E101

James Brown

01-Aug-1998

28-Feb-2002

20,500

E101

James Brown

01-Mar-2002

31-Dec-4712

23,500

Inserting Records in DateTracked Tables

If you insert a record in a DateTracked table, the effective start date is set to the current day and the effective end date is set as 31-DEC-4712. This effective end date value is the default value set by Human Resources if you do not specify an effective end date. However, you can specify an effective end date that is later than the effective start date.

Updating Records in DateTracked Tables

If you update a record in a DateTracked table, a message is displayed asking whether you want to update or correct the record. If you choose:

  • Update: The value in the Effective End Date column of the existing record changes to yesterday. A record is created with the updated values having the Effective Start Date column value as the current date and the Effective End Date column value as 31-Dec-4712.

    If the record you are updating has effective start date as the current day, a message is displayed stating that the previous values will be lost because the Oracle DateTrack feature does not support time stamp values and hence there can be only one record per day. If you want to make a second change on the same day, you must do it in the correction mode.

  • Correction: The existing record is overwritten with the new values and the same effective dates are retained.

Deleting Records in DateTracked Tables

If you delete a record in a DateTracked table, you are prompted to select the type of delete. If you choose:

  • Delete (End Date): The effective end date of the current record is set to today’s date. The record disappears from the dialog box in Human Resources although you can view the record by running a query.

  • Zap (Purge): All records matching the key value are deleted.

  • Future Change (All): Any future-dated changes to the current record are removed. The effective end date of the current record is set to 31-DEC-4712. You can view the record by running a query.

  • Delete Next Change (Next Change): The next change to the current record is deleted. A record may or may not have a future DateTracked record.

If a future DateTracked record exists for the record, the future DateTracked record is deleted, and the effective end date of the deleted row becomes the effective end date of the current record.

If no future DateTracked record exists and the current row has an effective end date other than 31-DEC-4712, then the effective end date is set as 31-DEC-4712. The record disappears from the dialog box in Human Resources although you can view the record by running a query.

Oracle DateTrack in NoetixViews for Oracle Human Resources

NoetixViews for Oracle Human Resources uses the Oracle DateTrack feature. Because all the significant tables in Human Resources are DateTracked tables, the DateTrack feature plays an important role in the NoetixViews architecture. However, there are some views that return only the current record.

With respect to current and historic information, there are three types of views:

  • Current Only: These views have no name suffixes, nor any time period information in their view descriptions. The current views return information that is current as of the time the query is run in NoetixViews.

  • End Dated Historic: These view names have no suffixes. The words “Current” and “Historic” are present in their view description. These views contain information that is not DateTracked but may include historic information, based on the end dates of work structure entities included in the views.

  • DateTracked Historic: These views have the “Hist” suffix in their names and the words “Current” and “Historic” in their view descriptions. These views contain Human Resources DateTracked information — multiple records for the same entity as of current, historic, and future effective dates. More information about the Noetix historical views and the effective dates is provided in the following section.

The attributes of a DateTracked historic view are:

The view name ends with Hist (for example, HR Emp Assign Hist).

The view will contain three effective date columns: Effective Start Date, Effective End Date, and Effective End Date Stored. The effective date columns will be populated from the Oracle base table.

For example, the effective date columns in the HR Emp Assign Hist view are populated from the PER ALL ASSIGNMENTS F table. The PER ALL ASSIGNMENTS F table is the underlying DateTracked table for the HR Emp Assign Hist view, and drives the data for the view.

The views display the effective start date and effective end date only from the underlying DateTracked tables that drive the data for the view. The underlying DateTracked tables are the base tables for these views, although there may be other DateTracked tables that provide additional value to this view. For example, although the HR Emp Assign Hist view is populated with data from other DateTracked tables, such as PER ALL PEOPLE F, the effective dates from tables other than the PER ALL ASSIGNMENTS F table will not appear as columns in the view.

The Effective End Date Stored column is used to store the values from the actual database column, Effective End Date. The Effective End Date column in the view is the pretty version of the database column. It will be null if the value of the Effective End Date column in the Oracle database is 31-DEC-4712.

You must use the Effective End Date Stored column in filters. However, if you want to display the column in reports, you must use the Effective End Date column.

More complex views may be built from more than one DateTracked base table. In this case, there may be more than one set of effective dates in the view. You may need to create a filter for each pair of effective dates to avoid duplicates. Then again, some of these effective dates may be null; so filtering on all of them may cause your report to return no data. Add and test your filters one at a time.

Following are the exceptions in the effective dates in DateTracked historic views:

A few DateTracked historic views have only two columns: Effective Start Date and Effective End Date. The Effective End Date Stored column is missing in these views.

Sometimes, the effective date columns have variant names, such as Benefit Effective Start Date. Such views often do not have the Effective End Date Stored column; therefore, you should filter the records using the NVL function on the effective end date. These column names do not start with Effective and, consequently, will appear elsewhere in the alphabetized column lists of views.

For such views, the expression to display the current record in NWQ is as follows:

TRUNC(now()) BETWEEN Effective Start Date AND NVL(Effective End Date,'31-DEC-4712')

Even though the NWQ date format is <MM/DD/YYYY>, in expressions you use the date format that you see in Oracle E-Business Suite.

Some Human Resources tables do a kind of logical DateTracking without having effective date columns. This might be because of the following reasons:

These tables are mostly used outside the Human Resources arena and perhaps predate the Human Resources date tracking techniques.

These tables contain the Date From and Date To columns and the tables contain records with overlapping dates. For example, in the PER ADDRESSES table only one primary address can exist at a time, but there could be other types of addresses with overlapping dates. These dates will be displayed in Noetix views. Keeping in mind that you might need to filter on the Primary Flag column, your expression can be:

TRUNC(now()) BETWEEN Date From AND NVL(Date To,'31-DEC-4712')

Displaying DateTracked Data in Historical Views

To display the current record in NWQ using the Noetix historical views, you should use the following expression:

TRUNC(now()) BETWEEN Effective Start Date and Effective End Date Stored

Even if you have renamed these columns in your report, you must use the actual column names in the expression. It does not matter which column name you select but for readability it is recommended that you select an effective date rather than some random column. You might want to write the code in Notepad and then paste it in the Expression box of NWQ. It is a good idea to save these files because you are likely to reuse this code.

If you are not using NWQ as your reporting tool, you should use the following expression:

TRUNC(sysdate) BETWEEN Effective Start Date and Effective End Date Stored

No matter which query tool you are using, it is essential to truncate today’s date to avoid loss of data. Remember that the effective dates do not have a time stamp; therefore, if today is 31-AUG-2006 and one of the effective dates is today’s date, you must truncate the Now() or Sysdate() functions, and select this record.

When you are selecting data from a Noetix historical view, keep in mind that the effective date may be a meaningless column to include as a field in your report. If you are listing employees and jobs, the effective start date in the record is not necessarily the date the employee first got that job. It is likely to reflect some other change to the employee assignment, one you do not care about here.

Using Flag Columns in Historical Views

Some DateTracked historical views include flag columns that can eliminate the need to filter on effective dates when you want to select the current record. The most common flag columns are the Current Employee Flag, Current Assignment Flag, and Current Applicant Flag columns. You do not need to worry about null values in the flag columns because these columns will always have a value.

The Current Employee Flag and Current Applicant Flag columns do not limit a query to the current row of data. If you are looking at the HR Emp Assign Hist view and there are six records for a person, all the six records will be flagged as Y or N depending on whether the person is a current employee. The Current Applicant Flag column works in the same way.

As for the Current Assignment Flag column, looking at the same employee with six assignment history records, the value of the column will be Y only for the current assignment record or records.

Human Resources allows an employee to have more than one assignment at a time. So, there can be duplicate records for an employee. If you want a single record, filter on the Primary Flag column. You must do this even if your generation does not use multiple assignments because this feature cannot be turned off and there may be an accidental secondary assignment.

Another thing to keep in mind about the Current Assignment Flag column is that a person may have a current assignment with an assignment status as Terminate Assignment. So you may need to filter on multiple columns to limit your query to single records for the current employees.

Hiding Duplicate Rows in Historical Views

Most of the time, you are not interested in historical data. You want to see the current data and at the same time avoid duplicates.

In NWQ, selecting the Hide Duplicate Rows check box on the Columns page results in display of distinct records, just as using the SELECT DISTINCT SQL statement does. This is useful for eliminating duplicates in reports, but should only be used as a last resort in reports that will be run often, shared with others or become part of your corporate reporting repository. Hiding duplicate records without understanding why they occur may result in reporting errors.

Depending on how you sort, you may not see records that actually are duplicates. For example, if you are using a view like HR Emp Assign Hist and listing employees by job, the same employee might appear under different jobs and not be recognized as a duplicate. Selecting the Hide Duplicate Rows check box will not eliminate this problem.

Actually, there are no duplicates. Each row in the table is created because something has changed. However, there may be no differences in any of the columns in your query. On rare occasions, there will be no change in any data that the view has selected because the change was to a column that is not included in the view.

Displaying Data at a Point of Time

One thing to watch out for is that certain information from non-base tables may be repeated in the view. For example, for an employee with six assignment history rows, the employee’s date of last hire will occur on all six rows. If the repeated field is a date, as this one is, you can take advantage to select data from another period of time. For example, if you are creating a hire report, you might want to select the employee’s job at the time of hire. Your expression would then compare three dates:

Date Of Last Hire BETWEEN Effective Start Date AND Effective End Date Stored

The same technique might be advisable when you are working with views containing more than one pair of effective dates, where you compare one date with another. You may need to test this before you decide the date that should be between other dates.

We are now moving on to more challenging queries. We already saw how there might be another date in the view that you can use as a constant. For example, the actual termination date (the Actual Termination Date column) and the date the person started a job in the company (the Person Start Date column) are constants. There are few Noetix views that have an Effective Start Date column but no Effective End Date column, and the Effective Start Date column is a constant. Here are a few examples:

HR New Hire Hist view:

Service Start Date BETWEEN Effective Start Date AND Effective End Date Stored

HR Emp Reviews view:

Interview Date Start BETWEEN Effective Start Date AND Effective End Date Stored

Suppose you have a specific date in mind that is not today’s date. You have an option to hard code a date in your expression instead of using the Now() or Sysdate() functions:

'01-JAN-2005' BETWEEN Effective Start Date AND Effective End Date Stored

You might not want a hard-coded date. You want to create a report that displays the data as it was a year or two years ago, and you do not want to modify it every time you run it. To do this:

Create a calculated column called Last Year Date. If you do not want to display this column in your report, you can hide it by selecting the Is Hidden check box on the New Calculated Column page in NWQ.

Write the following expression in the Expression box to set the value of this column to last year:

TO CHAR(now(),'YYYY') – 1

You can concatenate the day and month to the year. The following expression concatenates today’s date with the previous year, but you could also use a constant, such as 01-JAN-:

TO CHAR(now(),'DD-MON')||'-'|| (TO CHAR(now(),'YYYY') - 1)

Now you have the date string that is required in your raw filter. Instead of TRUNC(now()) function, use the following expression:

TO CHAR(now(),'DD-MON')||'-'|| (TO CHAR(now(),'YYYY') - 1) BETWEEN Effective Start Date and Effective End Date Stored

You do not need to truncate your date because you have already eliminated any time stamp from your calculated date.

Creating calculated columns is easier than developing complex expressions because the query engine is somewhat less fussy. You can see components of your query and work up to the complexity that you want. You can also sometimes use a calculated column to test your query, provided you are computing with database columns and not creating a constant. Display the column, then run the report and drag up the computed column as a page item. The number of rows displayed should match the number of rows your filter returns. Also, it is easier to see which data gets eliminated.

If you use such dates in a filter, you will limit your query to a single record at some other point in time. You can also use them to perform counts in computed columns and develop a complex report.

Reporting Changes in Historical Data

One of the challenges with historical data is to find out the record where a change in the required value occurred for the first time. For example, you want to create a report displaying the names of the supervisors who changed during the course of work on an assignment. There may be several rows of assignment data where something else changed except the supervisor name. You should use the following expression to limit your query to only those rows where the supervisor is different from the previous row:

EXISTS

SELECT 1 FROM <fully qualified installation name>."HR Emp Assign Hist" a

WHERE a.Assignment Number = "HR Emp Assign Hist"."Assignment Number"

AND a.Business Group = "HR Emp Assign Hist"."Business Group"

AND a.Current Employee Flag = 'Y'

AND a.Primary Flag = 'Y'

AND a.Supervisor Employee Number <> "HR Emp Assign Hist".Supervisor Employee Number

AND a.Effective End Date = "HR Emp Assign Hist".Effective Start Date - 1)

The carriage returns are invalid in expressions. They are added here for readability, and you should remove it in your expression.

This example takes advantage of the fact that effective dates are always contiguous. The effective end date in the subquery will be one day previous to the start date of the main query.

Such techniques require a fairly high level of SQL skill, but once they are developed, it is much easier to clone and adapt them. For example, it would be fairly simple to modify the earlier code to detect job changes.

Creating Intelligent History Queries

You might want to select all the history records for an employee based on a characteristic feature in the employee’s current record. For example, you might want to display the job history for everyone in your organization who is currently working as a director. Assuming that all director jobs will contain the word “DIRECTOR” in the job flexfield, you can write the following expression to do this:

EXISTS

(SELECT 1 FROM "<fully qualified installation name>"."HR Emp Assign Hist" a

WHERE a.assignment number = "HR Emp Assign Hist"."Assignment Number"

AND a.Business Group = "HR Emp Assign Hist"."Business Group"

AND a.Current Employee Flag = 'Y'

AND a.Primary Flag = 'Y'

AND a.Job Name LIKE '%DIRECTOR%'

AND TRUNC(now())BETWEEN a.Effective Start Date AND a.Effective End Date Stored)

The carriage returns are invalid in expressions. They are added here for readability, and you should remove it in your expression.

Unfortunately, NoetixViews for Oracle Human Resources does not support passing parameters to subqueries. Therefore, you must modify the expression to work for a different job.

Creating Data Batches and Handling Stragglers

There is a frequent need in Human Resources to create a report or an outbound interface that runs on a regular basis and reports the change in a record. Let us consider an example to understand this scenario. Usually, a list of department transfers is generated monthly. However, if the list for the previous month needs to be generated on the first few days of this month, the late data entries for the previous month will not be included in this batch. That is, if on 03-APR-2006 you make an entry and set the effective end date value as 29-Mar-2006 for the record, then the record will not be selected if you run a query on 02-APR-2006. The issue is that such records will also not be picked up when you run the query in the following month because the query will be looking at effective dates in April and not March.

NoetixViews for Oracle Human Resources does not currently offer a way around this problem because it does not include Last Update Date or Creation Date columns in the views. You need to filter on the following two conditions: the effective start dates lie within the month in question or the effective start date is in the earlier month, and the last update date is within the month in question. Your SQL expression would look like this:

  • ((AND effective start date BETWEEN ’01-MAR-2006’ and ’31-MAR-2006’)

  • (OR effective start date <’01-MAR-2006’

  • AND last update date BETWEEN ’01-MAR-2006’ AND ’31-MAR-2006’))

The view should be customized to add the Last Update Date or Creation Date columns from the base table to the view. For more information, contact insightsoftwareSupport.

Restricting DateTracking Information

When you edit or delete a DateTracked record, a message is displayed asking you to select the type of update or delete you want to perform. Before displaying the message, the application calls a custom library event called DT SELECT MODE, and the event passes the options that should be displayed to the user.

You can control the options displayed to the user (such as Update or Correct) by using a custom code. If necessary, you can restrict the user to update or delete records and display an error message instead.

For more information about DT SELECT MODE, see the Oracle documentation.

Benefit Classification Assumptions

Views containing benefit element information (such as HR Ben Elig Info, HR Emp Ben Others, and HR Emp Total Comp) will not return all data unless benefit classification information has been entered into Human Resources Management System (HRMS) and benefit elements have been assigned to a benefit classification. For information about how a view may be restricted by this assumption, please read the specific view essay in the Noetix Help File.

Support for Additional Personal Details Descriptive Flexfield

In some Noetix views for Human Resources, columns labeled PEO$<attribute> are generated for only certain contexts of the Additional Personal Details descriptive flexfield (stored in the PER ALL PEOPLE F table of Oracle E-Business Suite).

View label

Supported contexts

HR Applicant Hist

APL, Global Data Elements

HR Carrier Asg Hist

EMP, Global Data Elements

HR Emp ADA Info

EMP, Global Data Elements

HR Emp Absence Hist

EMP, Global Data Elements

HR Emp Asg Details

EMP, Global Data Elements

HR Emp Assign Costs

EMP, Global Data Elements

HR Emp Assign Costs Hist

EMP, Global Data Elements

HR Emp Assign Hist

EMP, Global Data Elements

HR Emp Ben Health

EMP, Global Data Elements

HR Emp Ben Others

EMP, Global Data Elements

HR Emp Beneficiary

EMP, Global Data Elements

HR Emp Ethnic Info

EMP, Global Data Elements

HR Emp Headcnt Hist

EMP, Global Data Elements

HR Emp Headcounts

EMP, Global Data Elements

HR Emp Info

EMP, Global Data Elements

HR Emp LOS

EMP, Global Data Elements

HR Emp Reviews

EMP, Global Data Elements

HR Emp Sal Hist

EMP, Global Data Elements

HR Emp Sal Pro Current

EMP, Global Data Elements

HR Emp Sal Pro Hist

EMP, Global Data Elements

HR Emp Terms Hist

EMP, Global Data Elements

HR Emp Total Comp

EMP, Global Data Elements

HR Emp Veteran Info

EMP, Global Data Elements

HR Emp Work Hist

EMP, Global Data Elements

HR Emp Xfers Hist

EMP, Global Data Elements

HR New Hire Hist

EMP, Global Data Elements

HR Person Hist

EMP, Global Data Elements

For these views, the Noetix Help File also specifies the contexts for which the PEO$<attribute> columns are generated for the Additional Personal Details descriptive flexfield.

Default Hours for Salary Calculations

Information required to complete the calculations for calculated salary-related columns is, in some cases, optional setup information in Human Resources Management System (HRMS). If information is not available in Oracle HRMS to perform the calculations, make the following assumptions for standard full-time employees (hourly or salaried positions):

  • 8 paid working hours per day

  • 5 working days per week

  • 12 working months per year

  • 52 working weeks per year

  • 2,080 paid working hours per year

Please note that paid vacation and holidays are considered working days for these calculations, and the earlier values should not be reduced to account for them.

If one of these assumptions is incorrect for a business group or organization in your configuration, you can define standard working conditions in Oracle HRMS at the business group or organization level to override our assumption. If this cannot be accomplished or if multiple assumptions of ours are false, then a customization must be made to NoetixViews for Oracle Human Resources to provide correct calculations. Please contact insightsoftwareSupport for this customization.

Setup Business Group

NoetixViews for Oracle Human Resources generates a set of views and multiple roles for each business group defined in your Human Resources Management System (HRMS) configuration. Oracle HRMS ships with a predefined business group called the Setup Business Group. If the Setup Business Group has been modified to become an active defined business group, NoetixViews for Oracle Human Resources will work as expected and generate views for the information associated with this business group.

Optional Views and Columns

Some views and columns in NoetixViews for Oracle Human Resources are included only in specific configurations for Oracle Human Resources Management System (HRMS) or a business group within Oracle HRMS. The most common examples are legislative-specific views and columns. For example, business groups operating under U.S. legislation will have additional views and columns that relate to U.S.-specific government-mandated reporting information. No specific actions during the generation process are required to generate these views and columns. As long as your configuration of Oracle HRMS is complete, NoetixViews for Oracle Human Resources will automatically detect the configuration and include or exclude the appropriate columns. If you are still in the process of setting up Oracle HRMS or even a new business group and all the configuration information is not available, some columns relating to the configuration-specific information (such as flexfields) may not be included in NoetixViews for Oracle Human Resources.

Performance-Related Issues

Several concepts affect system performance when retrieving information from Oracle Human Resources Management System (HRMS) using NoetixViews for Oracle Human Resources.

In general, Oracle HRMS has a smaller number of rows than other Oracle E-Business Suite; performance is generally better because there is less information to sort through. Two factors that can negatively affect performance, however, are the use of functions within NoetixViews for Oracle Human Resources and the use of information that is optional within Oracle HRMS.

Functions

NoetixViews for Oracle Human Resources uses functions in almost all views to accomplish several tasks:

  • To take advantage of complex date-tracked information.

  • To perform complex compensation calculations.

  • To create various formats for addresses.

  • To implement row-level security.

Oracle only supports user-defined functions that are usable from standard SQL with Oracle Database version 7.1 and later. NoetixViews for Oracle Human Resources is compatible with Oracle Database version 9.2 or later. Function performance is dependent upon the Oracle Database versions, and the later versions of Oracle Database exhibit higher function performance.

Optional Information

Information that is optional in Oracle Human Resources Management System (HRMS), such as jobs and positions information, should only be included if you intend to use it. Because of the flexibility in defining and assigning jobs and positions inherent in Oracle HRMS, this information may be defined within an Oracle HRMS instance yet never used. When you search for information with an optional field or column, Oracle does not perform the most efficient search. NoetixViews alleviates this problem by associating columns with indexed fields. Again, these columns are search by columns and are identified with the prefix A$.

Published:

NoetixViews for Oracle Human Resources

NoetixViews for Oracle Human Resources involves some special considerations that are not applicable to other Noetix Views (NoetixViews) products.

NoetixViews for Oracle Human Resources provides more than 65 views of Oracle E-Business Suite Human Resources Management System (HRMS) data based on the specific configuration at your site. The exact number of views at your site depends on how many special information types (SITs) you have defined and/or how many extra information types (EITs) are installed at your site. For example, many NoetixViews for Oracle Human Resources customers have more than 100 views.

Each view accesses a predefined set of fields in the database, assigns the data to column names, and provides the user with a variety of ways to view data. The product is a tool for the end user to assist in day-to-day Human Resources functions, such as employee management, recruitment, and payroll processes.

To optimize the product’s performance and maintain the confidentiality of data, those involved in the generation of views should be familiar with the concepts in this section.

The views for Human Resources are of two types: standard and global. Standard views for Human Resources return data for a single business group, and a Noetix role is generated for each detected business group. In contrast, global views for Human Resources provide access to all detected business groups in an instance of Oracle E-Business Suite. While supporting multiple structures of key flexfields, global views can be accessed through a single set of roles.

About Access to Human Resources Data

This section provides information about the roles for NoetixViews for Oracle Human Resources and the security policies supported by those views.

Noetix Roles for Human Resources

NoetixViews uses database roles to group related views and simplify security administration. A Noetix query user who is granted a role can then query all views in that role.

For standard views, the following roles are generated for each detected business group:

  • HR HUMAN RESOURCES: Includes views that return non-confidential data, confidential data (except for salary data), and data for extra information types (EITs) and special information types (SITs). Human Resources supervisors and those who require broad access to Human Resources data should be granted this role.

  • HR MANAGER: Includes views that return nonconfidential data and confidential data (except for salary data). Managers who require limited access to Human Resources data should be granted this role.

  • HR SALARY MANAGER: Includes views that return confidential salary data.

  • HR EXTRA INFO TYPES: Includes views that return data for EITs.

  • HR SPECIAL INFO TYPES: Includes views that return data for SITs.

  • HR USER: Includes views that return nonconfidential data.

For global views, only one set of these roles with the default, configurable prefix of HRG0 will be generated.

Security Policies in Views for HRMS

The views for Oracle Advanced Benefits, Human Resources, Oracle Payroll, and Oracle Time and Labor support the following kinds of security policies:

  • Application-Specific Security of Human Resources: This security policy is the default for Noetix query users of the Oracle E-Business Suite Authenticated User (Type A) and Oracle E-Business Suite Authenticated Responsibility (Type R) types. For these query users, application-specific security is applied in global views when no changes are made on the Business Group tab of the <Noetix query user> Properties dialog box of the NoetixViews Administrator.

    This security policy applies employee data and business group restrictions on Noetix query users that are similar to the restrictions applied on users in Human Resources. Employee data restrictions are always determined by the security profile or global security profile applied to the Noetix query users during logon.

    For standard views, the business group is hard-coded in the view and cannot be changed or overridden. The security profile defined for the Noetix query users of the Oracle E-Business Suite Authenticated User (Type A) and Oracle E-Business Suite Authenticated Responsibility (Type R) types in Oracle E-Business Suite will determine their access to data in standard views. For Noetix query users of the Database User (Type U) and Noetix System Administration User (Type N) types, the security profiles will apply only if the query users are also defined in Human Resources as reporting users for the security profiles. The security profiles cannot be overridden in standard views.

    For global views, the business group is determined by the security model and security profile or global security profile applied to the Noetix query users during logon. If the Standard Human Resources Management System security model is used in Human Resources, the business group assigned to the security profile is used or the business group assigned to the responsibility during logon with the global security profile is used. If the Security Groups Enabled security model is used, the business group is indicated by the security group used during logon. Irrespective of the security model, application-specific security of Human Resources will result in access to a single business group in the global views. For Noetix query users of the Database User (Type U) and Noetix System Administration User (Type N) types, application-specific security of Human Resources will apply in the global views only when these users are also specified as reporting users for security profiles in Human Resources. In such cases, access to employee data will be determined by the security profiles to which the reporting users are assigned.

    For information about security profiles, global security profiles, responsibilities, and security groups, see the Oracle documentation.

  • Custom Security: Custom security is available for only certain global views for Advanced Benefits, Human Resources, Payroll, and Time and Labor. Custom security is established when application-specific security of Human Resources is overridden through settings on the Business Group tab of the <Noetix query user> Properties dialog box. Through custom security, you can override the employee data restrictions of security profiles and global security profiles, allow access to all business groups within the organization hierarchy of a global security profile, and create custom lists of accessible business groups.

The following table indicates how the settings in Human Resources and on the Business Group tab of the <Noetix query user> Properties dialog box collectively secure data in global views for Advanced Benefits, Human Resources, Payroll, and Time and Labor.

Table 1: How HRMS Data is Kept Secure

At logon, when the Noetix query user has a

And it is

The global view will return

Security profile

Not enforced by NoetixViews

All employee data in all business groups in the Assigned Business Groups list on the Business Group tab

Security profile

Enforced by NoetixViews

Employee data restricted by the security profile and the business group assigned to the security profile when the Standard HRMS security model is used so long as the business group is also in the Assigned Business Groups list on the Business Group tab

Or

Employee data restricted by the security profile and the business group for the security group used during logon when the Security Groups Enabled security model is used so long as the business group for the security group is also in the Assigned Business Groups list on the Business Group tab

Global security profile

Enforced by NoetixViews

All employee data in all business groups in the Assigned Business Groups list on the Business Group tab

Global

security

profile

Enforced by

NoetixViews

Employee data restricted by the global security profile and for the business group assigned to the responsibility during logon when the Standard HRMS security model is used so long as the business group is also in the Assigned Business Groups list on the Business Group tab1

Or
Employee data restricted by the global security profile and the business group for the security group used during logon when the Security Groups Enabled security model is used so long as the business group for the security group is also in the Assigned

Business Groups list on the Business Group tabNoetixViews for Oracle Human Resources

Or
Employee data restricted by the global security profile and for all business groups in the organization hierarchy of the global security profile so long as the business groups are also in the Assigned Business Groups list on theBusiness Group tab2

For information about how to control access to data in global views for Advanced Benefits, Human Resources, Payroll, and Time and Labor, see Add a Noetix Query User Using an Existing User in “Noetix Query User Management.”

The following table lists the Noetix views pertaining to Human Resources that provide secured or customizable access to data.

Table 2: NoetixViews for Oracle Human Resources

View name

Human Resources security profile applied at:

HR Accrual Pln Hist

Any level

HR Address Hist

Person and Organization levels

HR AP 1099 Payments

(available for only United States legislation)

Person level

HR Applicant Hist

Person, Assignment, and Vacancy levels

HR Ben Elig Info

Position, Person, and Assignment levels

HR Budgets

Position level

HR Carrier Asg Hist

Security level

HR COBRA Prem Stat

(available for only United States legislation)

Person level

HR COBRA Track

(available only for United States legislation)

Person level

HR Contact Hist

Person level

HR Contingent Worker Info

Person and Assignment levels

HR EI Academic Rank

Person level

HR EI Asg Ben Derived

Person level

HR EI Asg Federal

Person level

HR EI Asg Locality

Person level

HR EI Asg Types

Position, Person, and Assignment levels

HR EI GHR Probations

Person level

HR EI GHR Sep Retire

Person level

HR EI Job Types

Any level

HR EI Loc Types

Any level

HR EI Per Types

Person level

HR EI Pos Types

Any level

HR EI US Add Details

(available for only United States legislation)

Person level

HR EI US Passport Dtls

(available for only United States legislation)

Person level

HR EI US Visa Dtls

(available for only United States legislation)

Person level

HR Element Links

Person, Organization, and Payroll level

HR Emp Absence Hist

Person and Assignment levels

HR Emp ADA Info

(available for only United States legislation)

Person level

HR Emp Asg Details

Person and Assignment levels

HR Emp Assign Costs

Organization, Person, and Assignment levels

HR Emp Assign Costs Hist

Person and Assignment levels

HR Emp Assign Hist

Person and Assignment levels

HR Emp Ben Health

Person and Assignment levels

HR Emp Ben Others

Person and Assignment levels

HR Emp Beneficiary

Person level

HR Emp Element Entry Vals

Person and Assignment levels

HR Emp Emergency

Person and Assignment levels

HR Emp Ethnic Info

(available for only United States legislation)

Person and Assignment levels

HR Emp Headcnt Hist

Person and Assignment levels

HR Emp Headcounts

Person and Assignment levels

HR Emp Info

Person and Assignment levels

HR Emp LOS

Person and Assignment levels

HR Emp Reviews

Person and Assignment levels

HR Emp Sal Analysis

Person and Assignment levels

HR Emp Sal Hist

Person and Assignment levels

HR Emp Sal Pro Current

Person and Assignment levels

HR Emp Sal Pro Hist

Person and Assignment levels

HR Emp Tax Details

(available for only United States legislation)

Person level

HR Emp Terms Hist

Person and Assignment levels

HR Emp Total Comp

Person and Assignment levels

HR Emp Veteran Info

(available for only United States legislation)

Person and Assignment levels

HR Emp Work Hist

Person and Assignment levels

HR Emp Xfers Hist

Person and Assignment levels

HR New Hire Hist

Organization, Person, and Assignment levels

HR Oth Headcnt Hist

Person and Assignment levels

HR Pay Scales

Any level

HR People Grp Hist

Person and Assignment levels

HR Person Hist

Person and Assignment levels

HR Phones Hist

Person level

HR Pos Hierarchies

Position level

HR Pos Requirements

Position level

HR Req Vac Track

Position and Vacancy levels

HR Schools Attended

Any level

HR SI Type

Person level

HR Turnover Hist

Organization, Person, and Assignment levels

HR Vac Job Match

Position and Vacancy levels

HR Vac Pos Match

Person, Position, and Vacancy levels

HR Accrual Pln Hist

Any level

HR Address Hist

Person and Organization levels

The following table lists the Noetix views pertaining to Advanced Benefits that provide secured or customizable access to data.

Table 3: NoetixViews for Oracle Advanced Benefits

View name

Human Resources security profile applied at:

BEN COBRA Beneficiaries

(available for only United States legislation)

Person and Assignment levels

BEN Elig Elec Enrollments

Person and Assignment levels

BEN Emp Dependents

Person level

BEN Life Evnt Workflow

Person and Assignment levels

BEN Payroll Ben Costs

Person level

BEN Potential Life Evnts

Person and Assignment levels

BEN Ptpnt Benefit Costs

Person and Assignment levels

BEN Ptpnt Communications

Person and Assignment levels

BEN Ptpnt Court Orders

Person and Assignment levels

BEN Ptpnt Electabilities

Person and Assignment levels

BEN Ptpnt Eligibilities

Person and Assignment levels

BEN Ptpnt Enroll Actions

Person and Assignment levels

BEN Ptpnt Enrollments

Person and Assignment levels

BEN Ptpnt Flex Credits

Person and Assignment levels

BEN Ptpnt Flex Spending

Person and Assignment levels

BEN Ptpnt Life Events

Person and Assignment levels

BEN Ptpnt Mthly Premiums

Person and Assignment levels

The following table lists the Noetix views pertaining to Payroll that provide secured or customizable access to data.

Table 4: NoetixViews for Oracle Payroll

View name

Human Resources security profile applied at:

PAY Accruals (available for all legislations except for Australia)

Person and Assignment levels

PAY Check Register

Payroll, Person, and Assignment levels

PAY Costing Analysis

Payroll, Person, and Assignment levels

PAY Costing Details

Organization level

PAY Costing Summary

Organization level

PAY Custom Balances
(available for all legislations except for Australia and United Kingdom)

Payroll, Person, and Assignment levels

PAY Deductions Owed

Payroll, Person, and Assignment levels

PAY Emp Not Paid

Payroll, Person, and Assignment levels

PAY Emp Not Paid Vg

Payroll, Person, and Assignment levels

PAY Gre Totals (available for only United States legislation)

Organization level

PAY Gross And Net Balances

Payroll, Person, and Assignment levels

PAY Gross To Net Summary

Organization level

PAY Hours By Cost Center

Organization level

PAY Invalid Addresses
(available for only United States legislation)

Person level

PAY Payment Methods

Person and Assignment levels

PAY Payment Register

Payroll, Person, and Assignment levels

PAY Payroll Activities

Payroll, Person, and Assignment levels

PAY Payroll Audit

Payroll, Person, and Assignment levels

PAY Payroll Messages

Organization, Payroll, Person, and Assignment
levels

PAY Payroll Proc Summary

Payroll level

PAY Run Results

Payroll, Person, and Assignment levels

PAY Tax Balances (available for all legislations except Australia and United Kingdom)

Payroll, Person, and Assignment levels

PAY Third Party Balances (available for only Canadian legislation)

Payroll, Person, and Assignment levels

PAY Third Party Register

Payroll, Person, and Assignment levels

PAY Void Payments

Payroll, Person, and Assignment levels

PAY Accruals (available for all legislations except for Australia)

Person and Assignment levels

PAY US Payroll Register
(available for only United States legislation)

Payroll, Person, and Assignment levels

PAY US W2 Register (available for only United States legislation)

Payroll, Person, and Assignment levels

The following table lists the Noetix views pertaining to Time and Labor that provide secured or customizable access to data.

Table 5: NoetixViews for Oracle Time and Labor

View name

Human Resources security profile applied at:

HXC All Assignment Hist

Person and Assignment levels
HXC All Person HistPerson level
HXC Assignment Time InfoPerson and Assignment levels
HXC BEE Batch HeadersOrganization level
HXC BEE Batch LinesPerson and Assignment levels
HXC BEE Error MessagesOrganization level
HXC PUI Latest TimecardsPerson and Assignment levels
HXC PUI Missing TimecardsPerson and Assignment levels
HXC PUI Time Entry ErrorsPerson level
HXC PUI Timecard HistoryPerson and Assignment levels
HXC SS Latest TimecardsPerson and Assignment levels
HXC SS Missing TimecardsPerson and Assignment levels
HXC SS Time Category HoursPerson level
HXC SS Timecard Action HistPerson and Assignment levels
HXC SS Timecard HistoryPerson and Assignment levels
HXC Timecard SummaryPerson and Assignment levels

SIT Views

NoetixViews for Oracle Human Resources provides the user with various views that maximize the information delivery ability of special information types (SITs) in Human Resources Management System (HRMS). SITs are a collection of personal attributes that usually provide information related to recruitment, such as specific qualifications and skills, which are predefined in the Personal Analysis key flexfield.

NoetixViews for Oracle Human Resources provides views to augment your recruitment, career development, and applicant matching capabilities by using SITs. SIT views are generated during generation based on the SITs defined and enabled at your site.

The format for each view name is HR SI Type STRUCTURE NAME. For example, the views HR SI Type Education or HR SI Type Communication Skills would each return a list of people associated with each SIT defined at your site. These may be grouped by Applicant, Employee, or Employee and Applicant Person Types. The number of HR SI Type views per site is directly related to the number of SITs that were defined in Oracle HRMS.

Views displaying current job and position requirements based on the SIT definitions are also available. The names for these views will look similar to HR Job Requirements or HR Position Requirements.

In addition, the following views are designed to assist you in matching a pool of internal and external applicants to specific vacancies at your firm (regardless of whether your organization has decided to use Job or Position functionality within Oracle HRMS): HR Vac Job Match and HR Vac Pos Match.

Note: SITs defined for Job and Position Requirements or Skills used in the Oracle Training module do not result in NoetixViews HR SI Type views being generated.

Joining to SIT Views

The information in each special information type (SIT) view can be linked to the data in the HR Person Hist view. Therefore, each SIT view contains one Z$ column to allow joins to the HR Person Hist view. You can also join an SIT view to any current view that includes a join to Z$ column for HR Person Hist.

Angles for Oracle Generator for Oracle Discoverer (Angles for Oracle Generator) automatically sets up joins between the HR SI Type views and other current views containing a join to Z$ column to HR All Person Hist, and you can do the same when configuring other query tools.

When joining an SIT view with HR Person Hist, only current results will be returned. This is because SIT views return only current information.

The use of SITs is optional. When an SIT view is joined to another view, the data returned will be limited by the attributes in the particular SIT view. This reduces the information returned from a query involving an SIT view and a join to column. For example, HR Emp Asg Details joined to HR SI Type Education would result in assignment details for all employees meeting the criteria of the special educational information designated in the SIT view. If an employee did not meet the educational criteria, that particular employee record would not be returned.

Joining views using outer joins is not a good option because of the way Oracle executes queries against views that are joined using outer join. This option only works when query criteria is limited by search by (A$) columns in both views involved in an outer join. An equijoin does not have this requirement, and only one search by (A$) column needs to be used as query criteria. (Using two could be faster, however, if query performance is required.)

Note: This special join to consideration is not true for NoetixViews for Oracle Human Resources date-tracked views (view names suffixed with “Hist”). The HR SI Type views are not historical and may cause historical information to be missing from the resulting query.

EIT Views

Extra information type (EIT) views, available with NoetixViews for Oracle Human Resources, maximize the information delivery ability of EITs in Human Resources Management System (HRMS). Introduced in Oracle E-Business Suite 11.0 and further enhanced in 11i, EITs are developer descriptive flexfields available for six key areas (person, assignment, position, job, location, and organization).

In addition, Oracle HRMS allows the addition of unlimited descriptive flexfields for further customization of each EIT. For example, a user may want to use the person EIT to display additional personal information such as passport details, visa details, visit details, etc.

NoetixViews generates views for the person and assignment EIT types and also detects any customized descriptive flexfields. Each EIT view contains only information related to the specific EIT. These simple EIT views are designed to be joined to related Human Resources views (through the use of Noetix Z$ columns) to provide more detailed data. For example, the information in the person EIT views can be linked to the data in the NoetixViews HR Person Hist view or, in addition, to any Human Resources view that includes the Z$HR Person Hist column.

Similarly, the assignment EITs can be linked to the data in the HR Emp Asg Details view or to any Human Resources view that includes the Z$HR Emp Asg Details column in NoetixViews. For more information about how to join views using Z$ columns, see the MagnitudeNoetixViews User Guide.

The format for each view name is HR EI ASSIGNMENT TYPE. For example, the person EIT view HR EI US Passport Dtls will display United States Passport Details. EIT views will be generated only for the Person and Assignment types of EITs supplied by Oracle. Person EITs are available for U.S., Global, and Government categories. Assignment EITs are available for U.S., Federal, and Great Britain categories.

Oracle DateTrack and NoetixViews

The Oracle DateTrack feature allows you to store changes in records that happen over a period of time. The DateTrack feature adds the dimension of time to an application’s database. Some tables in Human Resources use the DateTrack feature to record changes in the employee data and to preserve the historical information in an organized manner. These tables typically contain an employee’s details such as an employee’s personal, assignment or compensation details.

This section provides an overview about the DateTrack feature and information about how the feature is used in NoetixViews for Oracle Human Resources.

The DateTrack feature is also used in the Payroll and Advanced Benefits modules of the Oracle E-Business Suite.

Identifying DateTracked Records

The Oracle table that stores DateTracked information has the following attributes:

The table name ends with F. For example, PER ALL PEOPLE F.

The Effective Start Date and Effective End Date columns exist, and the values in these columns cannot be null. The Effective Start Date column indicates when the record was inserted, and the Effective End Date column indicates when the record was deleted or updated.

In most DateTracked tables, the effective dates form a part of the primary key consisting of an ID and the two effective dates. For example, in the table PER ALL PEOPLE F, Person Id + Effective Start Date + Effective End Date is the primary key.

The effective dates are contiguous. For example, if the effective start date of the current record is 16-JAN-2000, then the effective end date of the previous record will be 15-JAN-2000.

The effective dates do not have any time stamps, and, thus, there can only be one record per day.

Following is an example of a DateTracked table.

Emp No

Name

Effective Start Date

Effective End Date

Salary

E101

James Brown

01-Jan-1990

31-Dec-1995

15,500

E101

James Brown

01-Jan-1996

31-Jul-1998

18,500

E101

James Brown

01-Aug-1998

28-Feb-2002

20,500

E101

James Brown

01-Mar-2002

31-Dec-4712

23,500

Inserting Records in DateTracked Tables

If you insert a record in a DateTracked table, the effective start date is set to the current day and the effective end date is set as 31-DEC-4712. This effective end date value is the default value set by Human Resources if you do not specify an effective end date. However, you can specify an effective end date that is later than the effective start date.

Updating Records in DateTracked Tables

If you update a record in a DateTracked table, a message is displayed asking whether you want to update or correct the record. If you choose:

  • Update: The value in the Effective End Date column of the existing record changes to yesterday. A record is created with the updated values having the Effective Start Date column value as the current date and the Effective End Date column value as 31-Dec-4712.

    If the record you are updating has effective start date as the current day, a message is displayed stating that the previous values will be lost because the Oracle DateTrack feature does not support time stamp values and hence there can be only one record per day. If you want to make a second change on the same day, you must do it in the correction mode.

  • Correction: The existing record is overwritten with the new values and the same effective dates are retained.

Deleting Records in DateTracked Tables

If you delete a record in a DateTracked table, you are prompted to select the type of delete. If you choose:

  • Delete (End Date): The effective end date of the current record is set to today’s date. The record disappears from the dialog box in Human Resources although you can view the record by running a query.

  • Zap (Purge): All records matching the key value are deleted.

  • Future Change (All): Any future-dated changes to the current record are removed. The effective end date of the current record is set to 31-DEC-4712. You can view the record by running a query.

  • Delete Next Change (Next Change): The next change to the current record is deleted. A record may or may not have a future DateTracked record.

If a future DateTracked record exists for the record, the future DateTracked record is deleted, and the effective end date of the deleted row becomes the effective end date of the current record.

If no future DateTracked record exists and the current row has an effective end date other than 31-DEC-4712, then the effective end date is set as 31-DEC-4712. The record disappears from the dialog box in Human Resources although you can view the record by running a query.

Oracle DateTrack in NoetixViews for Oracle Human Resources

NoetixViews for Oracle Human Resources uses the Oracle DateTrack feature. Because all the significant tables in Human Resources are DateTracked tables, the DateTrack feature plays an important role in the NoetixViews architecture. However, there are some views that return only the current record.

With respect to current and historic information, there are three types of views:

  • Current Only: These views have no name suffixes, nor any time period information in their view descriptions. The current views return information that is current as of the time the query is run in NoetixViews.

  • End Dated Historic: These view names have no suffixes. The words “Current” and “Historic” are present in their view description. These views contain information that is not DateTracked but may include historic information, based on the end dates of work structure entities included in the views.

  • DateTracked Historic: These views have the “Hist” suffix in their names and the words “Current” and “Historic” in their view descriptions. These views contain Human Resources DateTracked information — multiple records for the same entity as of current, historic, and future effective dates. More information about the Noetix historical views and the effective dates is provided in the following section.

The attributes of a DateTracked historic view are:

The view name ends with Hist (for example, HR Emp Assign Hist).

The view will contain three effective date columns: Effective Start Date, Effective End Date, and Effective End Date Stored. The effective date columns will be populated from the Oracle base table.

For example, the effective date columns in the HR Emp Assign Hist view are populated from the PER ALL ASSIGNMENTS F table. The PER ALL ASSIGNMENTS F table is the underlying DateTracked table for the HR Emp Assign Hist view, and drives the data for the view.

The views display the effective start date and effective end date only from the underlying DateTracked tables that drive the data for the view. The underlying DateTracked tables are the base tables for these views, although there may be other DateTracked tables that provide additional value to this view. For example, although the HR Emp Assign Hist view is populated with data from other DateTracked tables, such as PER ALL PEOPLE F, the effective dates from tables other than the PER ALL ASSIGNMENTS F table will not appear as columns in the view.

The Effective End Date Stored column is used to store the values from the actual database column, Effective End Date. The Effective End Date column in the view is the pretty version of the database column. It will be null if the value of the Effective End Date column in the Oracle database is 31-DEC-4712.

You must use the Effective End Date Stored column in filters. However, if you want to display the column in reports, you must use the Effective End Date column.

More complex views may be built from more than one DateTracked base table. In this case, there may be more than one set of effective dates in the view. You may need to create a filter for each pair of effective dates to avoid duplicates. Then again, some of these effective dates may be null; so filtering on all of them may cause your report to return no data. Add and test your filters one at a time.

Following are the exceptions in the effective dates in DateTracked historic views:

A few DateTracked historic views have only two columns: Effective Start Date and Effective End Date. The Effective End Date Stored column is missing in these views.

Sometimes, the effective date columns have variant names, such as Benefit Effective Start Date. Such views often do not have the Effective End Date Stored column; therefore, you should filter the records using the NVL function on the effective end date. These column names do not start with Effective and, consequently, will appear elsewhere in the alphabetized column lists of views.

For such views, the expression to display the current record in NWQ is as follows:

TRUNC(now()) BETWEEN Effective Start Date AND NVL(Effective End Date,'31-DEC-4712')

Even though the NWQ date format is <MM/DD/YYYY>, in expressions you use the date format that you see in Oracle E-Business Suite.

Some Human Resources tables do a kind of logical DateTracking without having effective date columns. This might be because of the following reasons:

These tables are mostly used outside the Human Resources arena and perhaps predate the Human Resources date tracking techniques.

These tables contain the Date From and Date To columns and the tables contain records with overlapping dates. For example, in the PER ADDRESSES table only one primary address can exist at a time, but there could be other types of addresses with overlapping dates. These dates will be displayed in Noetix views. Keeping in mind that you might need to filter on the Primary Flag column, your expression can be:

TRUNC(now()) BETWEEN Date From AND NVL(Date To,'31-DEC-4712')

Displaying DateTracked Data in Historical Views

To display the current record in NWQ using the Noetix historical views, you should use the following expression:

TRUNC(now()) BETWEEN Effective Start Date and Effective End Date Stored

Even if you have renamed these columns in your report, you must use the actual column names in the expression. It does not matter which column name you select but for readability it is recommended that you select an effective date rather than some random column. You might want to write the code in Notepad and then paste it in the Expression box of NWQ. It is a good idea to save these files because you are likely to reuse this code.

If you are not using NWQ as your reporting tool, you should use the following expression:

TRUNC(sysdate) BETWEEN Effective Start Date and Effective End Date Stored

No matter which query tool you are using, it is essential to truncate today’s date to avoid loss of data. Remember that the effective dates do not have a time stamp; therefore, if today is 31-AUG-2006 and one of the effective dates is today’s date, you must truncate the Now() or Sysdate() functions, and select this record.

When you are selecting data from a Noetix historical view, keep in mind that the effective date may be a meaningless column to include as a field in your report. If you are listing employees and jobs, the effective start date in the record is not necessarily the date the employee first got that job. It is likely to reflect some other change to the employee assignment, one you do not care about here.

Using Flag Columns in Historical Views

Some DateTracked historical views include flag columns that can eliminate the need to filter on effective dates when you want to select the current record. The most common flag columns are the Current Employee Flag, Current Assignment Flag, and Current Applicant Flag columns. You do not need to worry about null values in the flag columns because these columns will always have a value.

The Current Employee Flag and Current Applicant Flag columns do not limit a query to the current row of data. If you are looking at the HR Emp Assign Hist view and there are six records for a person, all the six records will be flagged as Y or N depending on whether the person is a current employee. The Current Applicant Flag column works in the same way.

As for the Current Assignment Flag column, looking at the same employee with six assignment history records, the value of the column will be Y only for the current assignment record or records.

Human Resources allows an employee to have more than one assignment at a time. So, there can be duplicate records for an employee. If you want a single record, filter on the Primary Flag column. You must do this even if your generation does not use multiple assignments because this feature cannot be turned off and there may be an accidental secondary assignment.

Another thing to keep in mind about the Current Assignment Flag column is that a person may have a current assignment with an assignment status as Terminate Assignment. So you may need to filter on multiple columns to limit your query to single records for the current employees.

Hiding Duplicate Rows in Historical Views

Most of the time, you are not interested in historical data. You want to see the current data and at the same time avoid duplicates.

In NWQ, selecting the Hide Duplicate Rows check box on the Columns page results in display of distinct records, just as using the SELECT DISTINCT SQL statement does. This is useful for eliminating duplicates in reports, but should only be used as a last resort in reports that will be run often, shared with others or become part of your corporate reporting repository. Hiding duplicate records without understanding why they occur may result in reporting errors.

Depending on how you sort, you may not see records that actually are duplicates. For example, if you are using a view like HR Emp Assign Hist and listing employees by job, the same employee might appear under different jobs and not be recognized as a duplicate. Selecting the Hide Duplicate Rows check box will not eliminate this problem.

Actually, there are no duplicates. Each row in the table is created because something has changed. However, there may be no differences in any of the columns in your query. On rare occasions, there will be no change in any data that the view has selected because the change was to a column that is not included in the view.

Displaying Data at a Point of Time

One thing to watch out for is that certain information from non-base tables may be repeated in the view. For example, for an employee with six assignment history rows, the employee’s date of last hire will occur on all six rows. If the repeated field is a date, as this one is, you can take advantage to select data from another period of time. For example, if you are creating a hire report, you might want to select the employee’s job at the time of hire. Your expression would then compare three dates:

Date Of Last Hire BETWEEN Effective Start Date AND Effective End Date Stored

The same technique might be advisable when you are working with views containing more than one pair of effective dates, where you compare one date with another. You may need to test this before you decide the date that should be between other dates.

We are now moving on to more challenging queries. We already saw how there might be another date in the view that you can use as a constant. For example, the actual termination date (the Actual Termination Date column) and the date the person started a job in the company (the Person Start Date column) are constants. There are few Noetix views that have an Effective Start Date column but no Effective End Date column, and the Effective Start Date column is a constant. Here are a few examples:

HR New Hire Hist view:

Service Start Date BETWEEN Effective Start Date AND Effective End Date Stored

HR Emp Reviews view:

Interview Date Start BETWEEN Effective Start Date AND Effective End Date Stored

Suppose you have a specific date in mind that is not today’s date. You have an option to hard code a date in your expression instead of using the Now() or Sysdate() functions:

'01-JAN-2005' BETWEEN Effective Start Date AND Effective End Date Stored

You might not want a hard-coded date. You want to create a report that displays the data as it was a year or two years ago, and you do not want to modify it every time you run it. To do this:

Create a calculated column called Last Year Date. If you do not want to display this column in your report, you can hide it by selecting the Is Hidden check box on the New Calculated Column page in NWQ.

Write the following expression in the Expression box to set the value of this column to last year:

TO CHAR(now(),'YYYY') – 1

You can concatenate the day and month to the year. The following expression concatenates today’s date with the previous year, but you could also use a constant, such as 01-JAN-:

TO CHAR(now(),'DD-MON')||'-'|| (TO CHAR(now(),'YYYY') - 1)

Now you have the date string that is required in your raw filter. Instead of TRUNC(now()) function, use the following expression:

TO CHAR(now(),'DD-MON')||'-'|| (TO CHAR(now(),'YYYY') - 1) BETWEEN Effective Start Date and Effective End Date Stored

You do not need to truncate your date because you have already eliminated any time stamp from your calculated date.

Creating calculated columns is easier than developing complex expressions because the query engine is somewhat less fussy. You can see components of your query and work up to the complexity that you want. You can also sometimes use a calculated column to test your query, provided you are computing with database columns and not creating a constant. Display the column, then run the report and drag up the computed column as a page item. The number of rows displayed should match the number of rows your filter returns. Also, it is easier to see which data gets eliminated.

If you use such dates in a filter, you will limit your query to a single record at some other point in time. You can also use them to perform counts in computed columns and develop a complex report.

Reporting Changes in Historical Data

One of the challenges with historical data is to find out the record where a change in the required value occurred for the first time. For example, you want to create a report displaying the names of the supervisors who changed during the course of work on an assignment. There may be several rows of assignment data where something else changed except the supervisor name. You should use the following expression to limit your query to only those rows where the supervisor is different from the previous row:

EXISTS

SELECT 1 FROM <fully qualified installation name>."HR Emp Assign Hist" a

WHERE a.Assignment Number = "HR Emp Assign Hist"."Assignment Number"

AND a.Business Group = "HR Emp Assign Hist"."Business Group"

AND a.Current Employee Flag = 'Y'

AND a.Primary Flag = 'Y'

AND a.Supervisor Employee Number <> "HR Emp Assign Hist".Supervisor Employee Number

AND a.Effective End Date = "HR Emp Assign Hist".Effective Start Date - 1)

The carriage returns are invalid in expressions. They are added here for readability, and you should remove it in your expression.

This example takes advantage of the fact that effective dates are always contiguous. The effective end date in the subquery will be one day previous to the start date of the main query.

Such techniques require a fairly high level of SQL skill, but once they are developed, it is much easier to clone and adapt them. For example, it would be fairly simple to modify the earlier code to detect job changes.

Creating Intelligent History Queries

You might want to select all the history records for an employee based on a characteristic feature in the employee’s current record. For example, you might want to display the job history for everyone in your organization who is currently working as a director. Assuming that all director jobs will contain the word “DIRECTOR” in the job flexfield, you can write the following expression to do this:

EXISTS

(SELECT 1 FROM "<fully qualified installation name>"."HR Emp Assign Hist" a

WHERE a.assignment number = "HR Emp Assign Hist"."Assignment Number"

AND a.Business Group = "HR Emp Assign Hist"."Business Group"

AND a.Current Employee Flag = 'Y'

AND a.Primary Flag = 'Y'

AND a.Job Name LIKE '%DIRECTOR%'

AND TRUNC(now())BETWEEN a.Effective Start Date AND a.Effective End Date Stored)

The carriage returns are invalid in expressions. They are added here for readability, and you should remove it in your expression.

Unfortunately, NoetixViews for Oracle Human Resources does not support passing parameters to subqueries. Therefore, you must modify the expression to work for a different job.

Creating Data Batches and Handling Stragglers

There is a frequent need in Human Resources to create a report or an outbound interface that runs on a regular basis and reports the change in a record. Let us consider an example to understand this scenario. Usually, a list of department transfers is generated monthly. However, if the list for the previous month needs to be generated on the first few days of this month, the late data entries for the previous month will not be included in this batch. That is, if on 03-APR-2006 you make an entry and set the effective end date value as 29-Mar-2006 for the record, then the record will not be selected if you run a query on 02-APR-2006. The issue is that such records will also not be picked up when you run the query in the following month because the query will be looking at effective dates in April and not March.

NoetixViews for Oracle Human Resources does not currently offer a way around this problem because it does not include Last Update Date or Creation Date columns in the views. You need to filter on the following two conditions: the effective start dates lie within the month in question or the effective start date is in the earlier month, and the last update date is within the month in question. Your SQL expression would look like this:

  • ((AND effective start date BETWEEN ’01-MAR-2006’ and ’31-MAR-2006’)

  • (OR effective start date <’01-MAR-2006’

  • AND last update date BETWEEN ’01-MAR-2006’ AND ’31-MAR-2006’))

The view should be customized to add the Last Update Date or Creation Date columns from the base table to the view. For more information, contact insightsoftwareSupport.

Restricting DateTracking Information

When you edit or delete a DateTracked record, a message is displayed asking you to select the type of update or delete you want to perform. Before displaying the message, the application calls a custom library event called DT SELECT MODE, and the event passes the options that should be displayed to the user.

You can control the options displayed to the user (such as Update or Correct) by using a custom code. If necessary, you can restrict the user to update or delete records and display an error message instead.

For more information about DT SELECT MODE, see the Oracle documentation.

Benefit Classification Assumptions

Views containing benefit element information (such as HR Ben Elig Info, HR Emp Ben Others, and HR Emp Total Comp) will not return all data unless benefit classification information has been entered into Human Resources Management System (HRMS) and benefit elements have been assigned to a benefit classification. For information about how a view may be restricted by this assumption, please read the specific view essay in the Noetix Help File.

Support for Additional Personal Details Descriptive Flexfield

In some Noetix views for Human Resources, columns labeled PEO$<attribute> are generated for only certain contexts of the Additional Personal Details descriptive flexfield (stored in the PER ALL PEOPLE F table of Oracle E-Business Suite).

View label

Supported contexts

HR Applicant Hist

APL, Global Data Elements

HR Carrier Asg Hist

EMP, Global Data Elements

HR Emp ADA Info

EMP, Global Data Elements

HR Emp Absence Hist

EMP, Global Data Elements

HR Emp Asg Details

EMP, Global Data Elements

HR Emp Assign Costs

EMP, Global Data Elements

HR Emp Assign Costs Hist

EMP, Global Data Elements

HR Emp Assign Hist

EMP, Global Data Elements

HR Emp Ben Health

EMP, Global Data Elements

HR Emp Ben Others

EMP, Global Data Elements

HR Emp Beneficiary

EMP, Global Data Elements

HR Emp Ethnic Info

EMP, Global Data Elements

HR Emp Headcnt Hist

EMP, Global Data Elements

HR Emp Headcounts

EMP, Global Data Elements

HR Emp Info

EMP, Global Data Elements

HR Emp LOS

EMP, Global Data Elements

HR Emp Reviews

EMP, Global Data Elements

HR Emp Sal Hist

EMP, Global Data Elements

HR Emp Sal Pro Current

EMP, Global Data Elements

HR Emp Sal Pro Hist

EMP, Global Data Elements

HR Emp Terms Hist

EMP, Global Data Elements

HR Emp Total Comp

EMP, Global Data Elements

HR Emp Veteran Info

EMP, Global Data Elements

HR Emp Work Hist

EMP, Global Data Elements

HR Emp Xfers Hist

EMP, Global Data Elements

HR New Hire Hist

EMP, Global Data Elements

HR Person Hist

EMP, Global Data Elements

For these views, the Noetix Help File also specifies the contexts for which the PEO$<attribute> columns are generated for the Additional Personal Details descriptive flexfield.

Default Hours for Salary Calculations

Information required to complete the calculations for calculated salary-related columns is, in some cases, optional setup information in Human Resources Management System (HRMS). If information is not available in Oracle HRMS to perform the calculations, make the following assumptions for standard full-time employees (hourly or salaried positions):

  • 8 paid working hours per day

  • 5 working days per week

  • 12 working months per year

  • 52 working weeks per year

  • 2,080 paid working hours per year

Please note that paid vacation and holidays are considered working days for these calculations, and the earlier values should not be reduced to account for them.

If one of these assumptions is incorrect for a business group or organization in your configuration, you can define standard working conditions in Oracle HRMS at the business group or organization level to override our assumption. If this cannot be accomplished or if multiple assumptions of ours are false, then a customization must be made to NoetixViews for Oracle Human Resources to provide correct calculations. Please contact insightsoftwareSupport for this customization.

Setup Business Group

NoetixViews for Oracle Human Resources generates a set of views and multiple roles for each business group defined in your Human Resources Management System (HRMS) configuration. Oracle HRMS ships with a predefined business group called the Setup Business Group. If the Setup Business Group has been modified to become an active defined business group, NoetixViews for Oracle Human Resources will work as expected and generate views for the information associated with this business group.

Optional Views and Columns

Some views and columns in NoetixViews for Oracle Human Resources are included only in specific configurations for Oracle Human Resources Management System (HRMS) or a business group within Oracle HRMS. The most common examples are legislative-specific views and columns. For example, business groups operating under U.S. legislation will have additional views and columns that relate to U.S.-specific government-mandated reporting information. No specific actions during the generation process are required to generate these views and columns. As long as your configuration of Oracle HRMS is complete, NoetixViews for Oracle Human Resources will automatically detect the configuration and include or exclude the appropriate columns. If you are still in the process of setting up Oracle HRMS or even a new business group and all the configuration information is not available, some columns relating to the configuration-specific information (such as flexfields) may not be included in NoetixViews for Oracle Human Resources.

Performance-Related Issues

Several concepts affect system performance when retrieving information from Oracle Human Resources Management System (HRMS) using NoetixViews for Oracle Human Resources.

In general, Oracle HRMS has a smaller number of rows than other Oracle E-Business Suite; performance is generally better because there is less information to sort through. Two factors that can negatively affect performance, however, are the use of functions within NoetixViews for Oracle Human Resources and the use of information that is optional within Oracle HRMS.

Functions

NoetixViews for Oracle Human Resources uses functions in almost all views to accomplish several tasks:

  • To take advantage of complex date-tracked information.

  • To perform complex compensation calculations.

  • To create various formats for addresses.

  • To implement row-level security.

Oracle only supports user-defined functions that are usable from standard SQL with Oracle Database version 7.1 and later. NoetixViews for Oracle Human Resources is compatible with Oracle Database version 9.2 or later. Function performance is dependent upon the Oracle Database versions, and the later versions of Oracle Database exhibit higher function performance.

Optional Information

Information that is optional in Oracle Human Resources Management System (HRMS), such as jobs and positions information, should only be included if you intend to use it. Because of the flexibility in defining and assigning jobs and positions inherent in Oracle HRMS, this information may be defined within an Oracle HRMS instance yet never used. When you search for information with an optional field or column, Oracle does not perform the most efficient search. NoetixViews alleviates this problem by associating columns with indexed fields. Again, these columns are search by columns and are identified with the prefix A$.

For an optimal Community experience, Please view on Desktop
Powered by Zendesk