HRMS and PAYROLL Key Flexfields - Part II

Competency KFF

Use this flexfield to create competencies specific to your business group. You define competencies for performance management and appraisals of employees. You can also use the Competence key flexfield to record a hierarchy of competencies.

HRMS and PAYROLL Key Flexfields - Part I

In this document I will try to propose a possible way to configure the KFF’s one by one of HRMS & Payroll. One should keep in mind that this is not something carved in stone and the design should be finalized only after thorough study of business and understanding of client’s requirement is done.

SQL Query - Form Personalisation

Below is the list of tables that are populated when any Forms Personalisation is done

• FND_FORM_CUSTOM_RULES
• FND_FORM_CUSTOM_SCOPES
• FND_FORM_CUSTOM_ACTIONS
• FND_FORM_CUSTOM_PARAMS
• FND_FORM_CUSTOM_PROP_VALUES
• FND_FORM_CUSTOM_PROP_LIST

Viewing Patch History and Details from Front end

Here's how we can see details like whether a patch has been applied or not?, how many patches have been applied in the given days?, patches details and history.

Navigate to System Administrator Responsibility --> System Administration --> Oracle Applications Manager --> Patching and Utilities

Payroll Fast Formula

Oracle Fast Formula is a simple way to write formulas using English words and basic mathematical functions. You can use information from your database in formulas without learning the database structure or a programming language.

Oracle Payroll Taxonomy

For starters, we must know the most common terms associated with Oracle Payroll. We must know the processes and taxonomy in order to understand the basics easily, so here we go.

HR Security Profile - Part II

Restricting Access to Records

You set up a security profile by identifying records of employees, applicants, contingent workers, and candidates in the system which you want users to be able to access. You identify the records by selecting work structures or other criteria in the application to which employees, applicants, contingent workers, or candidates are attached. For example, you could give users access only to the records of employees, applicants, contingent workers, or candidates in a single organization.

HR Security Profile - Part I

As part of your implementation plan, you identify who will use Oracle HRMS, what information they require, and how they use it. You can control a user's access to database elements such as records, fields, forms, and functions, and you can also control a user's access to other user records and data.

Table Joins in SQL – Part III

Moving on, let us see the different types of Oracle joins - 

• Oracle INNER JOIN (or sometimes called simple join)
• Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
• Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)

Table Joins in SQL – Part II

How Many Comparisons Do You Need?

When using Oracle syntax for table joins, a query on data from more than two tables must contain the right number of equality operations to avoid a Cartesian product. To avoid confusion, use this simple rule: If the number of tables to be joined equals N, include at least N-1 equality conditions in the select statement so that each common column is referenced at least once.

Table Joins in SQL - Part I

In Oracle as data is not stored in a single table, we usually have to draw data from multiple tables together in a meaningful way. To show this data from multiple tables in one query, Oracle allows us to perform table joins. 

How to open a Web ADI sheet?

Before working on any Web ADI document the user needs to perform certain prerequisite settings in internet explorer and excel –

Internet Explorer Settings

Navigate to: Internet Explorer à Tools à Internet Options à Security Tab

FND Tables in Oracle Application

FND stands for foundation table which is combination of AOL, SYSTEM, ADMINISTRATOR, MODULES tables and is placed under FND_TOP. Below is a list of few key FND tables that along with a brief description of what is it used for –

Usage of Help Diagnostics in eBS

The Oracle eBusiness Suite / Applications provides a number of useful diagnostic and interrogative tools accessible from the front end. One of these tools is Examine accessible via Help > Diagnostics from the Oracle Forms interface. Below are some of the most frequent usage of these feature –

How to see application version, Form short name and version of form

>> (Help) About Oracle application

Help --> Diagnostics --> Examine

The below steps will guide to on how to enable the Help Diagnostics menu on Oracle Apps Java forms.

Step 1: Navigate to System Administrator responsibility> Profile> System

SQL Query - Find columns of any table

The below query will fetch column details of and database table :

SELECT 

column_id,

column_name,

data_type,

data_length,

data_precision

FROM dba_tab_columns

WHERE table_name = 'PER_ALL_PEOPLE_F'  -- enter the table name

order by column_id

Behavior of DateTracked Forms

This section describes the behavior of forms that incorporate DateTracking. When you begin to update or delete a record on a DateTracked form, you are prompted with a number of choices. This section describes the choices and their effect on the DateTracked table.The term "today" refers to the effective date set by the user.

DateTrack Feature in HRMS

Being an engineer i always had difficulty handling concept of ‘Date’. However through this article I would try to explain my understanding of DateTrack feature in Oracle Apps.

DateTrack adds the dimension of time to an application's database. It is a means of maintaining a history of changes to personnel records. The value of a DateTracked record depends on the date from which you are viewing the data. It applies to the People and Assignment details of a person’s record in Oracle HR. Each of the areas that are date tracked within a record are date tracked independently. 

SQL Query - Accounting Flexfield Details

The below query will fetch the details of accounting flexfield structure:

select sob.name Ledger_Name
, sob.ledger_id Ledger_Id
, sob.chart_of_accounts_id coa_id
, fifst.id_flex_structure_name struct_name

SQL Query - Form Personalisation Details

The below SQL query will fetch details of form level personalisations :

SELECT   ffcr.function_name
       , ffcr.form_name
       , fft.user_form_name
       , fat.application_name application
       , ffcr.sequence seq
       , ffcr.description

SQL Query - Database and Application Info

Below are some of the queries that can be used to get the database and Application information.

>> Get Product Version

SELECT product
     , VERSION
     , status
FROM   product_component_version

>> Get Applications Version and Patch Information

SQL Query - Responsibility list having a specific Concurrent Request

This SQL query can be used to find out which concurrent program attached to which all responsibility

select responsibility_name
from    fnd_responsibility_tl rsp_tl,
        fnd_responsibility fr,
        fnd_request_groups frg,
        fnd_request_group_units frgu,
        fnd_concurrent_programs_tl fcpt

SQL Query - Display Database Version and Server Operating System Name

The below sql query can be used to get database and other details

SELECT banner FROM v$version

SQL Query - List of users with access to a specific responsibility

The below sql query can be used to get the list of users who have access to a specific given responsibility

SELECT
  fu.user_name,
  fu.description,
  frt.responsibility_name,

Table Suffixes

Most of the oracle tables end with a suffix in Oracle Apps database. Did you ever wonder what these denote? Listed below are the different table suffixes and what they mean.

Oracle AIM - Part III

Data Conversion (CV)

CV.010 Define data conversion requirements and strategy

CV.020 Define conversion standards

CV.030 Prepare conversion environment

CV.040 Perform conversion data mapping

CV.050 Define manual conversion procedures

CV.060 Design conversion programs

CV.070 Prepare conversion test plans

CV.080 Develop conversion programs

CV.090 Perform conversion unit tests

CV.100 Perform conversion business objects

CV.110 Perform conversion validation tests

CV.120 Install conversion programs

CV.130 Convert and verify data

Oracle AIM – Part II

Deliverables of AIM are as follows:

Business Process Architecture (BP)

BP.010 Define Business and Process Strategy

BP.020 Catalog and Analyze Potential Changes

BP.030 Determine Data Gathering Requirements

BP.040 Develop Current Process Model

BP.050 Review Leading Practices

BP.060 Develop HighLevel Process Vision

BP.070 Develop HighLevel Process Design

BP.080 Develop Future Process Model

BP.090 Document Business Procedure

Oracle AIM – Part I

The Oracle Application Implementation Methodology (AIM) is a proven approach for implementation of Oracle Applications across business domains. This approach has been developed by oracle in constant collaboration within its partner network. According to Oracle "AIM provides the tools needed to effectively and efficiently plan, conduct, and control project steps to successfully implement new business systems."

There are eleven processes of implementation, viz - 

SQL Queries - Payslips

Here are two queries which can be used for fetching employee salary details. The user has to replace the classifications and element names with appropriate ones. The user can also add more elements in query no.2 to display more elements

1. SQL query to display employee salary (classification wise)

Payroll Costing - Introduction

This is one of the most important and vital feature of Oracle Payroll and HRMS on the whole. It’s through the Costing that Human Resources interact with the Oracle Financials (GL). Costing as a concept is to account the cost incurred on an employee in terms of compensation provided to him/her by the organization. Oracle achieves this through three processes - 

What are APIs?

Application Program Interfaces (APIs) are standard stored procedure, package or functions created for performing specific activities like employee creation, assignment creation, etc There are 3 types of APIs which exists in EBS.

Table vs. View – Any difference?

Many of us might struggle to give a precise answer to this question. This article might just help ..

The difference between a view and a table is that views are definitions built on top of other tables (or views), and do not hold data themselves. To put it in simple words, a view is a virtual table which also consists of rows and columns just like a table. Views have no direct or physical relation with the database.

Imp HR and Payroll Tables - Part V

>> PAY_INPUT_VALUES_F

Each defined element generally has one or multiple input segment, this DateTracked  payroll table holds the definitions of the input values associated with a specific element. Users can add or modify input values for non–legislative elements.

>> PAY_ELEMENT_ENTRIES_F

This payroll DateTracked table holds the list of element entries for each assignment. The actual values for each entry are held in PAY_ELEMENT_ENTRY_VALUES_F.

Imp HR and Payroll Tables - Part IV

>> PAY_PAYMENT_TYPES

This payroll table holds details of the payment types that can be processed by the system. Used to define specific payment methods in PAY_ORG_PAYMENT_METHODS_F

>> PAY_ORG_PAYMENT_METHODS_F

This payroll table holds the definitions that identify business groups and the organization units within a single business group. Additional information about classifications and information types for each organization is held in HR_ORGANIZATION_INFORMATION

Imp HR and Payroll Tables - Part III

>> HR_ORGANIZATION_UNITS

This table holds the definitions that identify business groups and the organization units within a single business group. Additional information about classifications and information types for each organization is held in HR_ORGANIZATION_INFORMATION

Imp HR and Payroll Tables - Part II

>> PER_ADDRESSES

Holds address information for current and ex–employees, current and ex–applicants and employee contacts. Addresses have a specific address STYLE that identifies the address format for a specific country, or territory. A person may have more than one address at any one time, and each address can be in a different style.

The PRIMARY_FLAG identifies the primary address. Primary addresses are not DateTracked, but they are contiguous and cannot overlap. From the first date a person has an address to the last date they have an address, a person must have one primary address. Any secondary address can be disabled by entering a value in DATE_TO.

Imp HR and Payroll Tables - Part I

>> PER_ALL_PEOPLE_F

This is the base table for all date-tracked personal details and provides the very basic information about employee/contingent worker/applicant. The columns START_DATE, EFFECTIVE_START_DATE and EFFECTIVE_END_DATE are all maintained by DateTrack. The START_DATE is the date when the first record for this person was created. The earliest EFFECTIVE_START_DATE for a person is equal to the START_DATE. The very data from the first screen we see when we open the ‘People à Enter and Maintain’ form goes into this table.

Define Organizations - Part IV

Frequently Asked Questions (FAQs)

How can I find the LOV used by a segment in the Information Type I select from the ‘Others’ button?

Query up the DFF ‘Org Developer DF’ and select the context whose name matches the name of the Information type you selected from the ‘Others’ button. The Segments screen lists the fields defined for that information type. The LOV will come from the validation used by the relevant segment.

Define Organizations - Part III

Related Oracle HRMS Tables

HR_ALL_ORGANIZATION_UNITS: The basic details of oracle erp organizations are stored here. There is also a translation table HR_ALL_ORGANIZATION_UNITS_TL for the organization name in each language installed.

Define Organizations - Part II

Business Groups

Business Groups are just organizations which have the classification ‘Business Group’ Each organization will have a BUSINESS_GROUP_ID to identify which business group it was created from. A business group will have identical values for BUSINESS_GROUP_ID and ORGANIZATION_ID. Organizations from all business groups can be queried up in the Define Organizations screen. However only organizations for the current business group and the classification ‘HR Organization’ can be used in the Assignment screen.

Define Organizations - Part I

In the next 3-4 posts (including this one) I’ll discuss about oracle hrms organization, oracle hrms organization classification and relevant oracle hrms tables

Organizations in HRMS

Organizations are used in oracle erp to represent Business Groups (BGs), External organizations (for example, tax offices, insurance carriers, disability organizations, benefit carriers, or recruitment agencies), Internal organizations (for example, departments, sections or cost centers) and GREs/Legal Entities.

Introduction to HRMS

Each and every enterprise needs people to manage and run its business. People are the most basic and the most important ingredient of any business. It could be an NGO, a Bank or a Robotics laboratory, no matter where the enterprise focuses on, it must need people to run it. That is why it becomes necessary for the enterprise to store organizational data along with employee data, and use them to manage the people related to the enterprise effectively. This requirement creates the market for a system called the Human Resource Management System.

SQL vs. PL/SQL

It’s an interesting question, to be sure. Most people who are familiar with Oracle development wouldn't give it a thought but when you come down to it, it’s sometimes confusing to define the demarcation between SQL and PL/SQL. Through this note let us try to understand the differences between SQL and PL/SQL. You never know, this  might just be the starting point of your next oracle hrms interview questions or oracle payroll interview questions :)