How to generate customize employee number

[mamajid]

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

[mkn_79]

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

[mamajid]

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

[admin]

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.

[mkn_79]

Hi All,
My pleasure.


Regards,
Muhammad Khurram Nawaz

[razib]

Hi Brother,

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

Can anybody solve this problem.