ERPstuff - Oracle EBS Community

Hi,

I am trying to generate customize employee number and for that i have taken following steps

1. I changed employee number generation from automatic to manual
2. I created a sequence
3. I wrote a pl/sql code to create a customize employee number using the sequence concating it with text like 'EMP||'-'||seq.nextval.
4. I registered the pl/sql code in apps through formula function window.

But when i create a new employee it doesnt generate customize employee number.

Any Idea where am i mistaking.

Regards

Majid

Hi Majid,
For Customized Employee Number Generation. See the below details. Hopfully you will have ur solution with this ....

Custom Employee Number Generation

Use Fast Formula for this. Your pl/sql Function can return the desired Employee Number.


Custom Person Number Generation Fast Formula will ONLY be called if the Organization / Business Group numbering is set to Automatic. If the numbering is set to Manual, the fast formula will not be called.


Step 1:


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 Next_Number

Thanks you very much.

I was doing all this in another business group that made confuse why it is not generating my customized employee number.

Your reply is a great help.

Thanks again.

Regards

Majid

mamajid please create a document on this using screen shots and send to me so it should be uploaded here and thanks to Khurram Nawaz.

Hi All,
My pleasure.


Regards,
Muhammad Khurram Nawaz

Hi Brother,

I did all of the steps.
But it's not working..............

Can anybody solve this problem.