Thursday, February 21, 2013

How to Create Custom Employee Number Generation via Fast Formula


1. Create Sequence
create sequence sac_emp_number
start with 1

2. Create Function to Generate Desired Sequence Number
CREATE OR REPLACE FUNCTION SAC_GENERATE_EMP_NUMBER (
   p_business_group_id   IN NUMBER,
   p_person_type         IN VARCHAR2,
   p_party_id            IN NUMBER
)
   RETURN VARCHAR2
IS
   l_emp_number         VARCHAR2 (15) := NULL;
   l_applicant_number   VARCHAR2 (15) := NULL;
   l_npw_number         VARCHAR2 (15) := NULL;
   l_auto_number        VARCHAR2 (15) := NULL;
BEGIN
   BEGIN
      SELECT   employee_number, applicant_number, npw_number
        INTO   l_emp_number, l_applicant_number, l_npw_number
        FROM   per_all_people_f
       WHERE   party_id = p_party_id
               AND TRUNC (SYSDATE) BETWEEN effective_start_date
                                       AND  effective_end_date;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_emp_number := NULL;
         l_applicant_number := NULL;
         l_npw_number := NULL;
   END;

   IF     l_emp_number IS NULL
      AND l_applicant_number IS NULL
      AND l_applicant_number IS NULL
   THEN
      IF p_person_type = 'EMP'
      THEN
         l_auto_number := 'SAC-' || sac_emp_number.NEXTVAL;
      ELSIF p_person_type = 'APL'
      THEN
         l_auto_number := 'APL-' || sac_emp_number.NEXTVAL;
      ELSIF p_person_type = 'CWK'
      THEN
         l_auto_number := 'CONT-' || sac_emp_number.NEXTVAL;
      END IF;
   END IF;

   RETURN l_auto_number;
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/
3. Attach Setup Business Group to Global Super HRMS Manager 
Navigation: System Administrator -> Profile -> System
Responsibility Name: Global Super HRMS Manager
Profile Option Name: HR: Security Profile
Set Profile as “Setup Business Group” at Responsibility Level

Note
The Above Step is Mandatory as one cannot generate Auto Employee Numbering on any custom Business Group. This could be possible on "Setup Business Group"

4. Create Formula Function: 
I. Navigation: Go to Global Super HRMS Manager -> Other Definitions -> Formula Functions
II. Function Name: SAC_NUMBER_GENERATION
III. Parameters:
Data Type: Text
Class: External function
Definition: <Name of the Function> SAC_GENERATE_EMP_NUMBER


Context Usages

Parameters


5. Create Fast Formula: EMP_NUMBER_GENERATION
Navigation: Global Super HRMS Manager -> Total Compensation -> Basic -> Write Formulas

The Below Naming Convention should be used, so as to Generate Custom Auto Numbering Sequence for Employee, Applicant or Contingent Worker

I. Employee EMP_NUMBER_GENERATION Person Number Generation
II. Applicant APL_NUMBER_GENERATION Person Number Generation
III. Employee CWK_NUMBER_GENERATION Person Number Generation


Type: Person Number Generation

Formula (For your Reference):
default for party_id is 0
inputs are
person_type (text),
party_id (number)

next_number = '0'
next_number = sac_number_generation(person_type,party_id)
return next_number

Please note if one has different Numbering Sequence in-case of Multiple business groups, it has to be handles via Fast Formula or PL/SQL Code


11 comments:

  1. What is the next step to test this case after setting up as said above?
    Currently we are using manual numbering like, we are entering employee number also here.
    So what should I do next to test whether the above process is working or not?

    ReplyDelete
  2. Bharat, run the concurrent program 'Change person Numbering to Automatic' . and create a new employee. and then the employee number should be generated automatically with the desired sequence.

    ReplyDelete
  3. Hello-
    I am trying this solution, for some reason the value for PARTY_ID is coming to 0 in the fast formula, and pl/sql function.
    Any idea why this is 0?
    Thanks

    ReplyDelete
  4. Hi,

    Well actually i did the above steps and it is working fine.
    But now my requirement is i need to disable this custom employee number generation and switch back to the employee number generated by oracle. i.e Employee Number generation would still be automatic but i need to disable my custom employee number generation.
    I tried end dating that fast formulas but that is not working 4 me.

    Could you help me please?

    Thanks
    Sahil

    ReplyDelete
  5. Sahil, Run the Concurrent Request "Change Person Numbering to Automatic". Also please note once you switch it from Manual to Automatic, Oracle would enable the Global Sequencing for Employee Numbering i.e. Your FF won't be called. So do take care before you switch employee numbering to Manual

    Thanks
    Bijoy Joseph

    ReplyDelete
  6. Hi Bijoy Joseph,

    While verifying in formula window it is throwing error like 'NEXT_NUMBER is of type TEXT but has NUMBER type assigned into it'. Where did I gone wrong??

    Thanks
    Anil.T

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete