Incorrect Tax Code in P45 Output..

What happened?
After applying the HR Family Pack K-RUP3 , the P45 is showing incorrect tax code.

How do we fix it?

Oracle has released couple of patches for this issue. One for 11i and a forward port for R12.

7410767-P45 SHOWING WRONG TAX CODE AFTER APPLYING SEPTEMBER 08 PATCH 7246613
7420661-HRMSR12: FWD PORT OF 7410767

Cheers,
Ganesh

Statutory Sick Pay(SSP)

What is SSP?
SSP stands for Statutory Sick Pay.
Why should an employer pay this to employees?
According to UK legislation, provided an employee satisfies certain conditions, whenever he takes certain types of leaves, the employer has to pay him the SSP.
What are all the Steps involved?
1.Defining the Time Unit
2.Defining the Qualifying Pattern
3.Defining the calendar
4.Assign the calendar to the Business Group or to a particular employee.
5.Define the elements and element links
6.Define the Absence Types
7.Enter the absences for employees
8.This creates the corresponding element entries
9.Run the quick pay and make sure that the employee is paid the SSP accordingly.

We will see all these steps in detail below
1. Time Unit & Qualifying pattern: To understand this, we need to understand the Qualifying pattern first.
If an employee is on leave on Monday and if he is Sick, then we have to pay SSP. But if is sick on a Sunday there is no requirement to pay SSP. So the weekdays will be Qualifying days while weekends will be non-qualifying days. The time unit acts as a component for the same.



3.Defining the calendar
Here we are defining the date range in which the Qualifying pattern is valid


4.Assign the calendar to the Business Group or to a particular employee.
We have to attach the calendar to the Business Group itself or to an employee alone.

5.Define the elements and element links
There are two elements provided by Oracle for SSP. "Statutory Sick Pay" and "SSP Corrections". But they are non-payments. So we have to define our own Earnings element and pass the seeded element's run result value to our element using Fast Formula.



6.Define the Absence Types

7.Enter the absences for employees

8.This creates the corresponding element entries

9. Run the quick pay and make sure that the employee is paid the correct SSP.

Discoverer 10G Download from OTN

There is no direct link to download Discoverer 10G from OTN. The download index doesn't show it unfortunately.

http://www.oracle.com/technology/software/products/ids/htdocs/101202winsoft.html

Click on Oracle Business Intelligence Tools 10g (10.1.2.0.2) for Microsoft Windows in this link.

Cheers
Ganesh

Retro Pay

What is Retro?
Retro means an out-dated thing from the past which is active now.

What is Retro Pay?
Retro Pay stands for some old Payment due for the employee

How can we land in such scenario?
There are two things which can happen..

1. Incorrect Element Entry Value-Some times we realise after some time that the element entry entered and processed in the past is not correct.
2. Back Dated Salary Increments: The union agreement or the Higher Management decides to revise salary effective from a date in the past

So what shall we do now?

Now that we have corrected the old Element Entry, the next Step is how to give this amount to the employee.

What Oracle Gives me for this?
Oracle gives the RetroPay process but in four flavours leading to confusions for the unarmed.

What are those four?

Here is the list
1. RetroPay by Run
2. RetroPay by Aggregate
3. RetroPay by Element
4. Enhanced RetroPay

Out of these four which one we are going to see in this?
In this entry, I am going to cover Retro Pay by Element. A new element entry will be created with the difference amount.

Sample Scenario
Assume an employee gets paid 1,500 for August.
Now payroll comes to know that his salary is actually 2,000 and not 1,500.

The following are the steps required to be performed.

1. Define a new element which can be used to pay the Retro amount
2. Make sure that this element is Non-recurring and Multiple Entries allowed.
3. Attach this Element to the retro tab of the Salary element
4. Define the Assignment Set

An assignment set needs to be defined with the affected Employees

5. Element Set
An element set needs to be defined with the Salary element(Don't include the Retro Element here.You will get an Error while submitting the Concurrent Request) .

6. Now run the Retro Pay by Element with the following parameters

a. Effective Start Date: This is the date from which we want to compute the difference
b. Effective End Date: A date in the latest Pay Period. The retro entries get created in this period.
c. Assignment Set: The Assignment Set we created
d. Element Set: The element set we created

Submit the request.. And check from the log that the number of assignments processed is correct.

Now if we check the element entries screen, we can see the retro elements getting created.

We can process them as usual.

Cheers
Ganesh

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..

Running ftp commands from a script

Most of the times the things we do from an FTP server will be the same.

• Login to the ftp server.
  
• Navigate to some directory.
• Change the file type to binary/ascii.
• Get the file and close the connection.

We can automate the task by doing the following

open ftpserver
username
password
cd /temp
get somefile
bye

Now from the command prompt give ftp-s:Your_file_name and you are done :)

Multi-Org mandatory for Oracle Applications R12

Multi-Org is mandatory for Oracle Applications R12. For a HRMS customer wishing to move to R12, multi-org needs to be enabled even if they are not using Financial modules.

The following query can be run to determine if Multi-Org is enabled already.

select multi_org_flag
from fnd_product_groups;

Oracle has provided a patch 5965493 which creates a dummy Operating unit named
'Non Usable Operating Unit for Multi-Org Conversion'
Once the dummy operating unit is created we can run adadmin and choose the correct options to convert to Multi-Org.