In Oracle HRMS R12.2.8, If you are finding an requirement to customize the Auto system generated employee code based on the user defined Custom person types please refer the below steps to achieve it.
Requirement:
In My Current Organization, Based on the User defined Person Types we need to customize the employee codes.
List of Person Types:
· XXX – Recruited Employee
· YYY – Recruited Employee.
If it is XXX – Recruited Employee then we need to add B as a Prefix then followed by 5 digits of Numeric Number. (i.e) B00001 , B00002, B00003 etc.
If it is YYY – Recruited Employee then we need to add K as a Prefix then followed by 5 digits of Numeric Number. (i.e) K00001 , K00002, K00003 etc.
Step 1: Create Userdefined Person Types.
Navigation: India HRMS Manager --> Other Definition --> Person Types
Step 2 : Create Custom Sequence
We need to Create two custom separate Sequence . One is for XXX – Recruit and Second one is for YYY – Recruit.
CREATE SEQUENCE "APPS"."XX_EMP_NUMBER_XXX" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 27 NOCACHE NOORDER NOCYCLE NOPARTITION ;
CREATE SEQUENCE "APPS"."XX_EMP_NUMBER_YYY" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 27 NOCACHE NOORDER NOCYCLE NOPARTITION ;
Step 3: Create Custom Profile
Navigation: Application Developer --> Profile
Profile Name : XX_USER_PEROSN_TYPE
Step 4: Custom Form Personalization
This personalization is to be done to bring the User Selected Person type value and get it stored agaist Profile Value. So that this can be called during the Custom Employee Number Generation Fast formula.
We can’t directly bring the user selected field value into Formula.
Navigation : India HRMS Manager --> People --> Enter & Maintain --> Help --> Diagnostics --> Custom Code --> Personalize.
Create the Form Personalization as mentioned in the above screenshot in the Action Tab.
Argument :
='BEGIN FND_PROFILE.put('''||'XX_USER_PEROSN_TYPE'||''','''||:PERSON.D_PTU_USER_PERSON_TYPE||'''); END'
Step 5: Create Custom Package Spec with 2 functions.
create or replace PACKAGE XX_HR_UTILITY
AS
FUNCTION GENERATE_EMP_NUMBER (p_business_group_id NUMBER
,p_user_person_type VARCHAR2
,p_sequence_name VARCHAR2
,x_err_msg OUT VARCHAR2)return varchar2;
FUNCTION get_user_person_type (x_err_msg OUT VARCHAR2) RETURN VARCHAR2;
END XX_HR_UTILITY;
Package Body:
create or replace PACKAGE BODY XX_HR_UTILITY
AS
--------Fucntion to Get User Person Type---------------------------
FUNCTION get_user_person_type (x_err_msg OUT VARCHAR2)
RETURN VARCHAR2
IS
lv_user_person_type VARCHAR2(50);
BEGIN
SELECT FND_PROFILE.value('XX_USER_PEROSN_TYPE') INTO lv_user_person_type FROM DUAL;
IF lv_user_person_type IS NULL THEN
x_err_msg := 'Unable to get the User Person Type for the employee';
END IF;
RETURN lv_user_person_type;
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_err_msg := 'XX_USER_PEROSN_TYPE: Profile XX_USER_PEROSN_TYPE is not defined';
RETURN NULL;
END get_user_person_type;
--------------------------Employee Number Generation --------------
FUNCTION GENERATE_EMP_NUMBER(p_business_group_id NUMBER
,p_user_person_type VARCHAR2
,p_sequence_name VARCHAR2
,x_err_msg OUT VARCHAR2
)
RETURN VARCHAR2
IS
CURSOR Emp_type(p_emp_num VARCHAR2)
IS
SELECT COUNT(*)
FROM per_all_people_f PAPF
WHERE PAPF.employee_number = p_emp_num;
-- Local Variables
lv_employee_number per_all_people_f.employee_number%TYPE;
lv_sql_statement VARCHAR2(1000);
ln_empnum_exists NUMBER := 2;
BEGIN
--
lv_sql_statement := 'SELECT '||p_sequence_name||'.NEXTVAL FROM DUAL';
WHILE ln_empnum_exists >= 1
LOOP
EXECUTE IMMEDIATE lv_sql_statement INTO lv_employee_number;
IF p_user_person_type = 'YY - Recruit' then
lv_employee_number := 'K' || LPAD(lv_employee_number, 5,'0');
ELSIF p_user_person_type = 'XX - Recruit' THEN
lv_employee_number := 'B' || LPAD(lv_employee_number, 5,'0');
END IF;
OPEN emp_type(lv_employee_number);
FETCH emp_type INTO ln_empnum_exists;
CLOSE emp_type;
END LOOP;
COMMIT;
RETURN lv_employee_number ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_err_msg := 'XX_HR_UTILITY:'||p_sequence_name||' database sequence is not defined';
IF emp_type%ISOPEN THEN
CLOSE emp_type;
END IF;
RETURN NULL;
WHEN OTHERS THEN
x_err_msg := 'XX_HR_UTILITY Unknown exception:'||SUBSTR(SQLERRM,1,500);
IF emp_type%ISOPEN THEN
CLOSE emp_type;
END IF;
RETURN NULL;
End;
END XX_HR_UTILITY;
Step 6: Assign Setup Business Group to Global Super HRMS Manager.
Navigation: System responsibility à Profile à System
Responsibility Name : Global Super HRMS Manager.
Profile Name : HR: Security Profile
Set Profile as Setup Business Group @ Responsibility level.
Step 7: Create Two Formula Function
Navigation : Global Super HRMS Manager à Other Definition à Formula Function
As per the below Screenshot, Create Formula Function.
Name : XX_USER_PERSON_TYPE
Data Type : Text
Class : External function
Definition : XX_HR_UTILITY.get_user_person_type
Parameter Usage :
Parameter Name : x_err_msg
Type : Text
Class : Output Only
-------------
Name : XX_EMP_NUMBER_GEN
Data Type : Text
Class : External function
Definition : XX_HR_UTILITY.generate_emp_number
Context Usage :
Context Name : Business_group_id
Data TYpe ; Number
Parameter Usage :
p_user_person_type , Text, Input Only
p_sequence_name, Text, Input Only
x_err_msg , Text, Output Only
Step 6: Create Fast Formula.
Navigation : Global Super HRMS Manager à Total Compensation à Basic à Write Formulas
Query Formula Name : EMP_NUMBER_GENERATION
Click Edit.
/* ------------------------------------------------------------*/
/* NAME: EMP_NUMBER_GENERATION */
/* Returns the Next Number for given Person Type. */
/* */
/* Change History */
/*-------------------------------------------------------------*/
/* Author Date Remarks */
/*-------------------------------------------------------------*/
/* 16-Aug-2021 Initial Version */
/* */
/*-------------------------------------------------------------*/
INPUTS ARE
Legislation_Code (text),
Person_Type (text),
Person_number (text),
Party_id,
Person_id,
Date_of_birth (date),
Hire_date (date),
National_ID (text)
/*Local Variables
Start */
Next_Number (TEXT) = '0'
Invalid_msg (TEXT) = ' '
lv_user_person_type (TEXT) = ' '
lv_err_msg (TEXT) = ' '
lv_sequence(TEXT) = ' '
/*
Local Variables End
*/
/*Get the user person type of the person for whom employee number is to be generated */
lv_user_person_type =XX_USER_PERSON_TYPE (lv_err_msg)
IF ( ISNULL(lv_user_person_type) = 'N') THEN
(
IF (lv_user_person_type = 'YY - Recruit') THEN
(
lv_sequence = 'XX_EMP_NUMBER_YYY'
)
ELSE IF (lv_user_person_type = 'XX - Recruit') THEN
(
lv_sequence = 'XX_EMP_NUMBER_XXX'
)
/*Call a function to generate Custom Employee Number based on user person type*/
Next_Number = XX_EMP_NUMBER_GEN(lv_user_person_type
,lv_sequence
,lv_err_msg
)
/* If error then raise fatal error */
IF ISNULL(lv_err_msg) = 'N' THEN
(
invalid_msg = lv_err_msg
RETURN invalid_msg
)
)
ELSE
(
invalid_msg = 'Employee Number Cannot be generated,Invalid Person Type'
RETURN invalid_msg
)
RETURN Next_Number
No comments:
Post a Comment