Grade Step Progression

Hi All
I made a presentation about Grade Step Progression functionality to Customers when I was working in Oracle.

You can check it here.
Cheers
Ganesh

Regarding Grades

This is a short note about the Grades Workstructure in Oracle.

People new to Oracle will wonder why we are having so many different workstructures for the same assignment like Organizations, Jobs, Positions, etc.. This is to give flexibility to the users.

Grades signal the level in the organization. It is closely related to pay. For example, in a company two people doing the same kind of work may be paid differently based on their Length of Service and other factors.

Grades are handy in such situations.

To define a Grade we need to give the following details . Grade Name( This is a KFF) Short Name for the Grade (Optional) and Start Date of the Grade.

Once we have defined the Grades like 'Level A' 'Level B' etc, we come to the Grade Rates form.

Here we can define the rates for the Grade in two methods.

1. Salary range
2. Exact Salary


1. Salary Range

We can give the Min Max and Mid value for the Grade. This option is mostly used in private sector as the salary value is not strict there.

2.Exact Salary

We give the exact value for the Grade. This is used in Public Sector companies mostly as the Salary for a particular grade is predefined.


How the salary mentioned here is actually used to pay the employees?

1. We can use the defined Grade Rates defined as database items in our fast formulas
2. We can use the salary range for validation when manually entering the salary in the Salary Administration Screen.
3. We can use this in Grade Step progression functionality.( A more detailed post about this GSP later..)

Cheers
Ganesh

Unable to see WYSIWYG in JSP Visual Editor of JDeveloper

When we design an ADF page in Jdeveloper, the visual editor usually displays WYSIWYG of the page structure. But based under some circustances, we may not be getting the WYSIWYG. But only boxes of the tags in the editor.

Check this metalink note for the full detail of the issue and the resolution.

Cheers,
Ganesh

Auditing in Oracle Applications

Auditing database tables is a mandatory requirement for Enterprise Level applications.
Oracle Applications provides the Auditing capability built-in.

For HRMS there is are some additional steps required, that is in the next post.

The following are the steps to be followed:

1.Choose the System Administrator Responsibility and navigate to Security->AuditTrail->Install and make sure that Audit Enabled checkbox is set for the schema in which the table is present.

2.Goto Security->AuditTrail->Tables and select the table by clicking the Torch icon in the top.Now choose the columns you want to audit by adding the columns one by one.

3.Navigate to Security->AuditTrail->Groups
Either define a new Group and add the table you want to or add it to any existing group for the application of your table.

Steps 2 and 3 can be done in any order.

Now we have to run "AuditTrail Update Tables" concurrent program and Auditing is enabled for the table!

Cheers,
Ganesh

Interesting SQL

I got the following mail from one of my classmates:

Hey Ganesh,

I need some help.
I need a SQL query to perform the following:

Following is the requirement. (Database – Oracle9i , Language - Java)

1. Consider there is a table Table1.

2. Records are to be obtained from table based on three columns. Let us call the columns as Column1, Column2 and Column3. Values for these will be provided by the user.

3. Following is the Rule
Records to be retrieved with the following criteria
1. Rule1 - Records where values given by user
Exactly match Column1 + Exactly match Column2 + Column3 contains the specified string.
2. Rule2 - Records where values given by user
Exactly match Column1 + Exactly match Column2
3. Rule3 - Records where values given by user
Exactly match Column1 + Column3 contains the specified string.
4. Rule4 - Records where values given by user
Column3 contains the specified string.

Records need to be ordered as above list, i.e records for point 1 should come first, for point 2 should come next and so on. And there shouldn’t be any duplicates.


I have a sample query but this will have duplicates (Empno’s selected in query 1 will be repeated in query 2 & 3, empno’s from 2 will be repeated in 3). Assume empno is key

select EMPNO, ename, 11 as colorder from emp where (sal = 100 and deptno = 10 and job = 'CLERK')
union
select EMPNO, ename, 22 as colorder from emp where (sal = 100 and deptno = 10)
union
select EMPNO, ename, 33 as colorder from emp where sal = 100
order by 3

I think the result from the above query can be easily manipulated by a program to remove duplicates. But I wanted try this in SQL itself and as efficiently as possible.
Let me know if you know a way to do this. Thanks.

And I mailed the following query for the same.. Not sure if there is a more sophisticated solution..

select empno,ename, case
when sal >2000 and deptno = 10 and job = 'CLERK' THEN
11
when sal>2000 and deptno = 10 then
22
when sal>2000 then
33
ELSE
0
end dummy
from emp

Cheers
Ganesh

Consolidation Set in Oracle Payroll

A Consolidation Set is a means of labelling a collection of Payroll runs. After a payroll Run is complete, it may be required to do some additional processing on a collection of Payroll runs. One example is the Costing process which needs to be run after the Payroll Run.

Defining a Consolidation Set is probably the simplest thing to do in Oracle HRMS :). Just open the Consolidation Set form, click on the new record icon, enter a new name and you are done.

When we define a new Payroll, the consolidation set name is mandatory. While submitting the Payroll Run process, Consolidation Set associated with the Payroll gets populated by default in the SRS window.
Even after a Payroll Run is complete we can update the Consolidation Set by using the Update Payroll Run form.

Workstructures in Oracle HRMS

This entry will just act as a "Hello World" to the world of HRMS Workstructures.Below are some quick definitions of Oracle HRMS Workstructures. Please note that this definitions are not exhaustive. Refer to the Oracle User Guides if you want detailed information.

Location:

Location is the place where employees work.

Business Group:
A Business Group typically represent the whole company.

Organization:
An Organization is like a department. Typical for a company will be HR,Admin,IT etc.

Job:
A Job is a generic role in a company. Typical jobs are manager,director etc

Position:
To define a position you need to give a job and an Organization. A "Director" job in an "IT" organization becomes a "IT Manager" position.Positions are very cumbersome to implement but more powerful than Jobs.

Grades:
Grades are directly related to Salary. Grades span across all other workstructures.

More detailed information later..