EBS – Core HR – Position Elimination API

By | August 22, 2012
Requirement: We need to Eliminate all Vacant and Non Occupied Positions

Assumptions:

  • We have been provided a list of positions to be created.
  • It is assumed that all provided positions are vacant and non occupied.
  • LSG is my custom schema name.
  • API will be run by DBA in APPS Schema.
  • API should be run in UPDATE Mode, Effective Date should be 31-Jul-2012, Hiring Status shoould be made ‘Eliminated’ and Position Status should be made ‘Invalid’

Staging Table Script

CREATE TABLE LSG.LSG_POSITION_UPDATE_MAF
(
POSITION_NAME VARCHAR2(1000 BYTE),
EFFECTIVE_DATE_FROM DATE,
DATE_END DATE,
CREATION_DATE DATE,
BUSINESS_GROUP_ID NUMBER(4),
PROCESS_FLAG VARCHAR2(1 BYTE),
MESSAGE VARCHAR2(4000 BYTE)
)

Sample Data

SET DEFINE OFF;

Insert into LSG.LSG_POSITION_UPDATE_MAF   (POSITION_NAME)

 Values

   (‘Chief Operating Officer.0001.Mafraq Hospital Administration’);
Insert into LSG.LSG_POSITION_UPDATE_MAF   (POSITION_NAME)
 Values
   (‘Charge Nurse.5055.Maternal and Child Health Services’);
Insert into LSG.LSG_POSITION_UPDATE_MAF   (POSITION_NAME)
 Values
   (‘Charge Nurse.5063.ENT Clinic’);
COMMIT;

Some Important Validations to be done before running the API————–
–no same position exists twice in the provided data

select position_name, count(1)from
LSG.LSG_POSITION_UPDATE_MAFGROUP BY POSITION_NAMEHAVING COUNT(1) > 1
 ————–
–checkig if position to be end dated has any Active assignment
select pp.POSITION_ID,f.person_id ,stg.position_name,pas.user_status,ppf.employee_number,ppf.full_name ,ppf.CURRENT_EMPLOYEE_FLAG,npw_number
from apps.per_all_assignments_f f, per_positions pp,LSG.LSG_POSITION_UPDATE_MAF STG ,PER_ASSIGNMENT_STATUS_TYPES pas,apps.per_all_people_f ppf
where trunc(sysdate) between f.effective_start_date and f.effective_end_date
AND f.BUSINESS_GROUP_ID =
and f.BUSINESS_GROUP_ID = pp.BUSINESS_GROUP_ID
and f.position_id = pp.position_id
and pp.name = stg.position_name
and f.ASSIGNMENT_STATUS_TYPE_ID = pas.ASSIGNMENT_STATUS_TYPE_ID
and ppf.person_id = f.person_id
and ppf.BUSINESS_GROUP_ID = f.BUSINESS_GROUP_ID
and trunc(sysdate) between trunc(ppf.effective_start_date) and trunc(ppf.effective_end_date)order by user_status
———–
–API Script Starts Here

DECLAREV_POSITION_ID NUMBER(10);V_POSITION_DEFINITION_ID NUMBER(10);V_NAME VARCHAR2(1000);V_OBJECT_VERSION_NUMBER NUMBER(3);V_MESSAGE VARCHAR2(4000);V_JOB_ID NUMBER(10);V_ORGANIZATION_ID NUMBER(4);V_EFFECTIVE_START_DATE DATE;V_EFFECTIVE_END_DATE DATE;V_LOCATION_ID NUMBER(10);V_EFFECTIVE_DATE DATE;V_VALID_GRADES_CHANGED_WARNING BOOLEAN; V_VALID_GRADES_WARN_MSG
VARCHAR2(10);LN_LOCATION_ID NUMBER;V_ELEM_POSITION_ID NUMBER;V_ELEM_EFFECTIVE_START_DATE DATE;V_ELEM_EFFECTIVE_END_DATE DATE;V_ELEM_POSITION_DEFINITION_ID NUMBER;V_ELEM_VLD_GRADE_CHANGED_WARN BOOLEAN;V_ELEM_NAME_POS VARCHAR2(300);V_ELEM_OBJECT_VERSION_NUMBER NUMBER;V_ELEM_EFFECTIVE_DT DATE;V_OCC_COUNT NUMBER;

 CURSOR C1 IS
    SELECT ROWID,STG.*
    FROM LSG.LSG_POSITION_UPDATE_LSG STG
    WHERE 1=1
    AND STG.PROCESS_FLAG IS  NULL;
–Cursor below is required if we need to remove position from position hierarchy as well
CURSOR C2 IS
    SELECT POS_STRUCTURE_ELEMENT_ID,OBJECT_VERSION_NUMBER
    FROM PER_POS_STRUCTURE_ELEMENTS
    WHERE BUSINESS_GROUP_ID =5040
    and SUBORDINATE_POSITION_ID=v_elem_position_id;

V_POS_STRUCTURE_VERSION_ID  NUMBER;
V_POS_STRUCTURE_ELEMENT_ID  NUMBER;
V_OBJECT_VERSION_NUMBER3    NUMBER;
LN_JOB_ID                   NUMBER;
LN_ORGNAZATION_ID           NUMBER;
LB_DO_ACTION                BOOLEAN:= FALSE;
LV_ERROR_MESSAGE            VARCHAR2(500);
LV_PROCESS_FLAG             VARCHAR2(30);
 BEGIN
 FOR I IN C1
 LOOP

    V_NAME                              := I.POSITION_NAME;
    LB_DO_ACTION               := TRUE;
    LV_PROCESS_FLAG        := ‘Y’ ;
    LV_ERROR_MESSAGE    := NULL;
    v_effective_date                  := ’31-JUL-2012′; 

     –Getting the latest version of position
     Select position_id,pap.object_version_number,pap.position_definition_id,pap.effective_start_date
     into v_elem_position_id,v_elem_object_version_number,v_elem_position_definition_id,v_elem_effective_dt
     from hr_all_positions_f  pap
     where name=I.POSITION_NAME
     and business_group_id=5040
     and pap.effective_start_date = (select max(pap1.effective_start_date) from hr_all_positions_f pap1
                                     where pap1.position_id=pap.position_id
                                     and pap1.business_group_id=pap.business_group_id
                                     );

    –checking if it is assigned to some terminated assignemnt
    select count(*) into v_occ_count from PER_ALL_ASSIGNMENTS_F f
    where trunc(sysdate) between f.effective_start_date and f.effective_end_date
    and  f.position_id=v_elem_position_id
    and f.assignment_status_type_id 3;
    if v_occ_count > 0 then
        LB_DO_ACTION := FALSE;
        LV_PROCESS_FLAG             := ‘E’ ;
        LV_ERROR_MESSAGE := ‘Already Occupied With an Assignment’ ;
        –raise already_occupied;
    end if;

   
–dbms_output.put_line(‘ 4.. ‘);
 BEGIN
    IF (LB_DO_ACTION) THEN
   
—    dbms_output.put_line(‘ 5.. ‘);   
       HR_POSITION_API.UPDATE_POSITION (
        P_VALIDATE                       => FALSE
        ,P_POSITION_ID                    =>V_ELEM_POSITION_ID
        ,P_EFFECTIVE_START_DATE           =>V_EFFECTIVE_START_DATE
        ,P_EFFECTIVE_END_DATE             =>V_EFFECTIVE_END_DATE
        ,P_POSITION_DEFINITION_ID         =>V_ELEM_POSITION_DEFINITION_ID
        ,P_VALID_GRADES_CHANGED_WARNING   =>V_VALID_GRADES_CHANGED_WARNING
        ,P_NAME                           =>I.POSITION_NAME
        ,P_AVAILABILITY_STATUS_ID         =>  5 –SELECT SHARED_TYPE_ID FROM per_shared_types  WHERE SHARED_TYPE_NAME = ‘Eliminated’
        ,P_OBJECT_VERSION_NUMBER          =>V_ELEM_OBJECT_VERSION_NUMBER
        ,P_STATUS                         =>’INVALID’
        ,P_EFFECTIVE_DATE                 =>v_effective_date
        ,P_DATETRACK_MODE                 =>’UPDATE’);   
       
          LV_PROCESS_FLAG                 := ‘Y’ ;
         
         
          IF V_VALID_GRADES_CHANGED_WARNING THEN –TRUE
            v_VALID_GRADES_WARN_MSG := ‘TRUE’;
          ELSE
              v_VALID_GRADES_WARN_MSG := ‘FALSE’;
          END IF;
         
—        required if we want to remove position from position hierachy
—          BEGIN 
—           
—              FOR J IN C2 LOOP
—                  HR_POS_HIERARCHY_ELE_API.DELETE_POS_HIERARCHY_ELE(
—                            P_VALIDATE                  =>false
—                            ,P_POS_STRUCTURE_ELEMENT_ID  =>J.POS_STRUCTURE_ELEMENT_ID
—                            ,P_OBJECT_VERSION_NUMBER     =>J.OBJECT_VERSION_NUMBER
—                            ,P_HR_INSTALLED              =>’I’);
—                  –dbms_output.put_line(‘ POS_STRUCTURE_ELEMENT_ID  Deleted ‘||J.POS_STRUCTURE_ELEMENT_ID||’ For Position ‘||V_ELEM_POSITION_ID ||’  ‘|| I.POSITION_NAME);                       
—                        
—              END LOOP;
—           EXCEPTION WHEN OTHERS THEN
—            NULL;
—           END;  
    END IF;
   
 
      UPDATE LSG.LSG_POSITION_UPDATE_LSG
      SET PROCESS_FLAG=LV_PROCESS_FLAG,
          MESSAGE= LV_ERROR_MESSAGE||’Valid_Grade_Warn_Msg=’||v_VALID_GRADES_WARN_MSG
      WHERE ROWID=I.ROWID;
      COMMIT;
     
       EXCEPTION

     WHEN OTHERS THEN
      rollback;
      V_MESSAGE :=SUBSTR(SQLERRM,1,4000);
        dbms_output.put_line(‘ V_MESSAGE  ‘||V_MESSAGE);   
      UPDATE LSG.LSG_POSITION_UPDATE_LSG
      SET PROCESS_FLAG=’E’,
      MESSAGE =LV_ERROR_MESSAGE||’==When Others Exception==’||V_MESSAGE
      WHERE
      ROWID=I.ROWID;
      COMMIT;
      END;
  END LOOP;
 
 END;

——API Script Ends Here———-