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
How to generate customize employee number
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
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
Who is online
Users browsing this forum: Ahrefs [Bot], Google Adsense [Bot] and 13 guests