Multiple Employee Number Generation

[Alihsyed]

I want to generate multiple series of employee numbers e.g.

For Permanent and Probationary employees

the employee number generation should start from 10000

and

For Third Party Contracts

it should start from 20000..


If any one has applied this scenario on E-Business Suite Release 12.0.4 please advice.

[admin]

It is kept at the BG level so it will not allow you such partition. Thanks

[Alihsyed]

if it is so then what is the purpose of Formula type "EMPLOYEE_NUMBER_GENERATION"

[alruwaished]

Hi Dear Alihsyed
In Oracle HRMS you can use this type of employee number.

- Manual
- Auto Number
- Custom Number base on Fast Formulas.

i think the Custom Number will Solve your problem, you can see meta link document for Employee number

thnaks

[Alihsyed]

Create a database sequence called ?CUST_NUMB?


Using SQL*Plus, run the following command:


SQL> CREATE SEQUENCE CUST_NUMB
START WITH 1000
INCREMENT BY 1
NOCYCLE;


You might need to grant access to other users. Contact your DBA to perform the necessary steps.


Step 2:


Create a package Header and body to do the work of finding the next value to be used:


Example of package header ? File named: mygetval.pkh


REM
REM This example will use a DB Sequence to get the next value and it returns an existing
REM person number every time it finds a match in the system using the PARTY_ID parameter.
REM
SET VERIFY OFF
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
CREATE OR REPLACE PACKAGE PER_FF_CUSTOM_NUMBER_GENERATION AS
/* $Header */
--
Function Get_Customd IN number
,p_legislation_code IN varchar2
,p_person_type IN varchar2
,p_person_number IN varchar2
,p_party_id IN number
,p_person_id IN number
,p_national_id IN varchar2
,p_date_of_birth IN date
)
return varchar2;
END PER_FF_CUSTOM_NUMBER_GENERATION;
/
commit;
exit;


Save this file


Step 3:
Create the package body

Example of package body ? File named: mygetval.pkb


CREATE OR REPLACE PACKAGE PER_FF_CUSTOM_NUMBER_GENERATION AS
/* $Header */
Function Get_Custom_Number(p_business_group_id IN number
,p_legislation_code IN varchar2
,p_person_type IN varchar2
,p_person_number IN varchar2
,p_party_id IN number
,p_person_id IN number
,p_national_id IN varchar2
,p_date_of_birth IN date
)
return varchar2 is
--
cursor person_exists is
-- We need to see if this person already exists in the system
--
SELECT employee_number, applicant_number, npw_number
FROM per_all_people_f
WHERE party_id = p_party_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
--
l_seq_num number;
l_next varchar2 (30);
l_emp_num number;
l_apl_num number;
l_npw_num number;
--
begin
open person_exists;
fetch person_exists into into l_emp_num, l_apl_num, l_npw_number;
IF person_exists%NOTFOUND then
--
-- no match was found, therefore use our custom sequence
-- append the first 3 characters of the national identfier
--
SELECT cust_numb.nextval INTO l_seq_num from dual;
l_next :=?XYZ-?||TO_CHAR(l_seq_num,?0009?)||?-?||SUBSTR(p_national_id,1,3);
return l_next;
--
ELSE
--
-- a match was found, therefore return existing person number
--
IF person_type = ?EMP? then
L_next := l_emp_num;
ELSIF p_person_type = ?APL? then
L_next := l_apl_num;
ELSIF p_person_type = ?CWK? then
L_next := l_npw_num;
END IF;
END IF;
close person_exists;
return l_next;
--
END Get_Custom_Number;
/
commit;
exit;



A description of what this should return:

Assumption NEXT sequential number = 1201 (from custom database sequence)

Entering a person with National Identifier = 110-22-1212

And Party_id is null

Should return a person number = XYZ-1201-110

The package Header and Body must now be generated into the system. One way is from sqlplus


SQL> start mygetval.pkh
SQL> start mygetval.pkb


If errors are generated, then compile package manually:


SQL> alter package PER_FF_CUSTOM_NUMBER_GENERATION compile;
SQL> show errors; (hopefully there will be none)
SQL> alter package PER_FF_CUSTOM_NUMBER_GENERATION compile body:
SQL> show errors; (again ? hopefully there are none)

Once the package compiles cleanly we are ready to proceed


Step 4:


Log into the SETUP BUSINESS GROUP

This is a must ? Using any other business group to implement and custom numbering WILL NOT WORK!

Create a Fast Formula Function ?

Navigation = Other Definitions > Formula Functions

Alter the Session date to reflect the earliest date you would want the custom numbering to start (01-JAN-1900 for example).

Enter the following Information ? again this is an example

Name = Get_Custom_Number
Data Type = Text
Class = External Funtion
Alias Name = NULL or what you want the alias to be
Description = Returns the next custom number value
Definition = PER_FF_CUSTOM_NUMBER_GENERATION.Get_Custom_Number


Next click on the Context Usages Button

Enter the following
Number = 1
Context Name = BUSINESS_GROUP_ID
Data Type = Number


NOTE: this is the ONLY context usage that needs to be defined.

Save this and close the form

Click the Parameters button and enter the following:

NUMBER PARAMETER NAME TYPE CLASS
1 p_legislation_code Text Input Only
2 p_person_type Text Input Only
3 p_person_number Text Input Only
4 p_party_id Number Input Only
5 p_national_id Text Input Only
6 p_date_of_birth Date Input Only


Step 5:

Now it is time to create the Fast Formula

Navigation = Total Compensation > Basic > Write Formula

Enter the Following:

Name = EMP_NUMBER_GENERATION
Type = Person Number Generation
Description = Returns next Employee number






Click on the Edit Button and enter the following text






/* -------------------------------------------------------------------------------------------------------*/
/* NAME: EMP_NUMBER_GENRATION */
/* Returns the Next Employee Number. */
/*-------------------------------------------------------------------------------------------------------*/
DEFAULT FOR Person_number IS ? ?
DEFAULT FOR Party_ID IS 0
DEFAULT FOR Person_ID IS 0
DEFAULT FOR National_ID IS ? ?
DEFAULT FOR Date_Of_Birth IS ?1900/01/01 00:00:00? (date)
DEFAULT FOR Hire_Date IS ?1900/01/01 00:00:00? (date)
INPUT ARE
Legislation_Code (text),
Person_Type (text),
Person_number (text),
Party_id,
Person_id,
Date_of_birth (date),
Hire_date (date),
National_ID (text)

Next_number = ?0?
Invalid_msg = ? ?
/*-------------------------------------------------------------------------------------------------------*/
/* Next test is to insure we are processing an EMPLOYEE and */
/* not an Applicant or Contingent Worker */
/*-------------------------------------------------------------------------------------------------------*/
IF person_type = ?EMP? then
(
Next_Number = Get_Custom_Number(Legislation_Code
,Person_Type
,Person_Number
,Party_ID
,Person_ID
,National_ID
,Date_Of_Birth)
ELSE invalid_msg = ?This is not an person_type of Employee!?
RETURN Ne

[eng_ahmad_2007]

Dear Ali,

Thanks for sharing this valuable information, but I wonder why this method will not work with another Business Group? Is not it suppoed to work with it too?

Note: You can also use Custom.PLL to implement Custom Employee Numbering.

Best Regards,

Eng. Ahmad Ghanem
Senior Oracle HRMS Consultant

[Alihsyed]

Dear Ahmed,

Can you elaborate the Custom.PLL further.

We cant use another business group for different person types.

THANKS,
Ali.

[eng_ahmad_2007]

Dear Ali,

I will be back to you to write about the custom.pll in short time in sha2 Allah, ... keep visiting us.

Kind Regards,