1 Reply Latest reply on Nov 27, 2012 1:27 PM by Ashish_Apps

    How to Customize Employee Number Generation?

    Uqba N. Abduh
      m trying to generate a custom employee,applicant numbers through a dummy business group.

      I've created on Application: Oracle E-Business the work structure and am now on the step of customizing the employee generating number.

      I followed the link steps:

      Below steps already done:

      Technical Side:
      1- Create New Sequence in DB.

      -----Sequence Code----------

      START WITH 1
      MAXVALUE 999999999999999999999999999
      MINVALUE 0

      2- Create New Package for issuing the numbers.

      ----------Package Code--------------

      Create or replace package appslead_package as
      Function get_emp_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;

      end appslead_package;

      Create or replace package body APPSLEAD_PACKAGE as

      Function GET_EMP_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 as

      /*Check if the person id is exists on the system or not*/
      cursor person_exists is
      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;

      lseq_num number;
      l_next varchar2(30);
      l_emp_num varchar2(30);
      l_apl_num varchar2(30);
      l_npw_num varchar2(30);


      open person_exists;
      fetch person_exists into l_emp_num,l_apl_num,l_npw_num;
      if person_exists%notfound then
      Select to_char(Appslead_Cust_Numb.Nextval) Into lseq_num From Dual;
      l_next := 'AppsLead-'||lseq_num;
      return l_next;
      If p_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_apl_num;
      End if;
      End if;
      Close person_exists;
      return l_next;
      end get_emp_custom_number;

      End appslead_package;


      Functional Side:
      1- Create or Modify Business Group Employee numbering method to Manual.
      2- Create New Formula Function.
      3- Create New Formula EMP_NUMBER_GENERATION with Type "Person Number Generation"

      ------Formula Script--------

      Create Custome formula for generation 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)

      Legislation_Code (text),
      Person_Type (text),
      Person_number (text),
      Date_of_birth (date),
      Hire_date (date),
      National_ID (text)

      Next_Number = '0'
      Invalid_msg = ' '

      IF Person_Type = 'EMP' then
      Next_Number = TO_CHAR(Get_Emp_Custom_Number(Legislation_Code,Person_Type,Person_Number,Party_Id,Person_Id,National_ID,Date_Of_Birth))
      Invalid_msg = 'This is not an person_type of Employee!'

      Return Next_Number


      Now, when I create a new employee I need the employee number to appear as

      But its not, and the screen of employee master is not allow to create an employee without filling the number.

      How to do the same?

      What should be the Employee Number Generation ?
      1- Automatic generation with Global Sequencing
      2- Manual

      If your answer is manual, in the employee master screen, the employee id is required and when you create the employee the id should be entered.