1 Reply Latest reply: Nov 27, 2012 7:27 AM by Ashish_Apps RSS

    How to Customize Employee Number Generation?

    728683
      m trying to generate a custom employee,applicant numbers through a dummy business group.

      I've created on Application: Oracle E-Business 11.5.10.2 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----------
      DROP SEQUENCE APPS.APPSLEAD_CUST_NUMB;

      CREATE SEQUENCE APPS.APPSLEAD_CUST_NUMB
      START WITH 1
      MAXVALUE 999999999999999999999999999
      MINVALUE 0
      NOCYCLE
      NOCACHE
      NOORDER;
      ----------------------------


      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);

      begin

      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;
      Else
      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--------

      /*
      EMP_NUMBER_GENERATION
      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)

      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)

      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))
      Else
      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
      "AppsLead-Emp-00001"

      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.