API-HRMS-II

Simple E.g for Update absence details:

CREATE OR REPLACE PROCEDURE XXRI_UPDATE_ABSENCE(p_person_id in number,
p_date_start in date,
p_date_end in date,
p_absence_attendance_id in number)
AS
   l_effective_date              DATE;
   l_absence_attendance_id       NUMBER;
   l_absence_days                NUMBER;
   l_absence_hours               NUMBER;
   l_object_version_number       NUMBER;
   l_dur_dys_less_warning        BOOLEAN;
   l_dur_hrs_less_warning        BOOLEAN;
   l_exceeds_pto_entit_warning   BOOLEAN;
   l_exceeds_run_total_warning   BOOLEAN;
   l_abs_overlap_warning         BOOLEAN;
   l_abs_day_after_warning       BOOLEAN;
   l_dur_overwritten_warning     BOOLEAN;
   l_del_element_entry_warning   BOOLEAN;
   l_abs_attendance_reason_id    NUMBER;
   l_date_start                  DATE;
   l_date_end                    DATE;
   l_person_id                   NUMBER;
BEGIN
   l_object_version_number := NULL;

   SELECT  person_id,abs_attendance_reason_id, absence_attendance_id
     INTO  l_person_id,l_abs_attendance_reason_id, l_absence_attendance_id
     FROM per_absence_attendances ABS
    WHERE ABS.absence_attendance_id =p_absence_attendance_id;

   BEGIN
      SELECT MAX (object_version_number)
        INTO l_object_version_number
        FROM per_absence_attendances ABS
       WHERE ABS.absence_attendance_id = p_absence_attendance_id;

      hr_person_absence_api.update_person_absence
                  (p_effective_date                 => l_effective_date,
                   p_absence_attendance_id          => l_absence_attendance_id,
                   p_abs_attendance_reason_id       => l_abs_attendance_reason_id,
                   p_date_notification              => l_effective_date,
                   p_date_start                     => p_date_start,
                   p_date_end                       => p_date_end,
                   p_absence_days                   => l_absence_days,
                   p_absence_hours                  => l_absence_hours,
                   p_object_version_number          => l_object_version_number,
                   p_dur_dys_less_warning           => l_dur_dys_less_warning,
                   p_dur_hrs_less_warning           => l_dur_hrs_less_warning,
                   p_exceeds_pto_entit_warning      => l_exceeds_pto_entit_warning,
                   p_exceeds_run_total_warning      => l_exceeds_run_total_warning,
                   p_abs_overlap_warning            => l_abs_overlap_warning,
                   p_abs_day_after_warning          => l_abs_day_after_warning,
                   p_dur_overwritten_warning        => l_dur_overwritten_warning,
                   p_del_element_entry_warning      => l_del_element_entry_warning
                  );
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Inner statement' || ' ' || SQLERRM);
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Outer statement' || ' ' || SQLERRM);
      COMMIT;
END XXRI_UPDATE_ABSENCE;
/
Compile and call the procedure with required parameters

CREATE OR REPLACE PROCEDURE XXRI_CREATE_ABSENCE
(p_person_id IN NUMBER,
p_absence_name IN VARCHAR2,
p_effective_date IN DATE,
p_start_date IN DATE,
p_end_date IN DATE,
P_ABSENCE_ATTENDANCE_ID OUT NUMBER
) AS
-- Essential functionality variables
L_VALIDATE_MODE BOOLEAN := FALSE;
L_PERSON_TYPE_ID NUMBER(15);
L_BUSINESS_GROUP_ID NUMBER;
L_EMPLOYEE_NO NUMBER;
L_DATE1 DATE;

-- API Return Variables
L_PERSON_ID NUMBER;
L_ASSIGNMENT_ID NUMBER;
L_ABS_DAYS NUMBER;
L_ABS_HOURS NUMBER;

L_ABSENCE_ATTENDANCE_ID NUMBER;
L_OBJ_VERSION_NUMBER NUMBER;
L_OCCURRENCE NUMBER;
L_ENTITLEMENT_WARNING BOOLEAN;
L_ABS_DAY_AFTER_WARNING BOOLEAN;
L_ABS_OVERLAP_WARNING BOOLEAN;

-- Constant variables
--L_MODULE_ID CONSTANT VARCHAR2(30) := 'XXFTHR_MIGRATION007';

-- Error Handling variables
L_ERROR_MESSAGE VARCHAR2(150);
L_ERROR_CODE VARCHAR2(30);
L_ERROR_STATEMENT VARCHAR2(50);

-- Count Variables
L_COUNT_TOTAL NUMBER := 0;
L_COUNT_SUCCESS NUMBER := 0; -- Total number of successful rows
L_CNT1 NUMBER := 0;
L_ERRM VARCHAR2(100);
L_ERR_AT_STMT NUMBER;
O_ABSENCE_ATTENDANCE_ID NUMBER;
O_OBJECT_VERSION_NUMBER NUMBER;
O_OCCURRENCE NUMBER;
O_DUR_DYS_LESS_WARNING BOOLEAN;
O_DUR_HRS_LESS_WARNING BOOLEAN;
O_EXCEEDS_PTO_ENTIT_WARNING BOOLEAN;
O_EXCEEDS_RUN_TOTAL_WARNING BOOLEAN;
O_ABS_OVERLAP_WARNING BOOLEAN;
O_ABS_DAY_AFTER_WARNING BOOLEAN;
O_DUR_OVERWRITTEN_WARNING BOOLEAN;
O_ABSENCE_DAYS NUMBER;
O_ABSENCE_HOURS NUMBER;

L_HIRE_DATE NUMBER;
L_BUSINESS_GROUP_ID VARCHAR(50);

L_ABSENCE_ATTENDANCE_TYPE_ID NUMBER;
L_ABS_ATTENDANCE_REASON_ID NUMBER;
L_BATCH_ID INTEGER;
L_CUTOFF_DATE VARCHAR(50) := '23-APR-2008';

X_USER_ID NUMBER;
X_RESP_ID NUMBER;
X_APPL_ID NUMBER;

BEGIN

--L_BUSINESS_GROUP_ID := 102;

--L_ERR_AT_STMT := 10;
--L_EMPLOYEE_NO := '2';
--L_CNT1 := L_CNT1 + 1;

--L_ABS_DAYS := 5;
--L_ABS_HOURS := NULL;
O_ABSENCE_ATTENDANCE_ID := NULL;
O_OBJECT_VERSION_NUMBER := NULL;
O_OCCURRENCE := NULL;
O_DUR_DYS_LESS_WARNING := NULL;
O_DUR_HRS_LESS_WARNING := NULL;
O_EXCEEDS_PTO_ENTIT_WARNING := NULL;
O_EXCEEDS_RUN_TOTAL_WARNING := NULL;
O_ABS_OVERLAP_WARNING := NULL;
O_ABS_DAY_AFTER_WARNING := NULL;
O_DUR_OVERWRITTEN_WARNING := NULL;
O_ABSENCE_DAYS := NULL;
O_ABSENCE_HOURS := NULL;
L_HIRE_DATE := NULL;
--L_PERSON_ID := NULL;
L_ABSENCE_ATTENDANCE_TYPE_ID := NULL;
L_ABS_ATTENDANCE_REASON_ID := NULL;

BEGIN

O_ABSENCE_DAYS := NULL;
L_PERSON_ID := 62;
SELECT USER_ID INTO X_USER_ID FROM FND_USER WHERE USER_NAME = 'SYSADMIN';

SELECT APPLICATION_ID
INTO X_APPL_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'PER';

SELECT RESPONSIBILITY_ID
INTO X_RESP_ID
FROM FND_APPLICATION FA, FND_RESPONSIBILITY_TL FR
WHERE FA.APPLICATION_SHORT_NAME = 'PER'
AND FA.APPLICATION_ID = FR.APPLICATION_ID
AND FR.RESPONSIBILITY_NAME = 'RI HRMS Manager';

FND_GLOBAL.APPS_INITIALIZE(X_USER_ID, X_RESP_ID, X_APPL_ID);

      BEGIN
         SELECT paat.absence_attendance_type_id
           INTO l_absence_attendance_type_id
           FROM per_absence_attendance_types paat
          WHERE UPPER (TRIM (paat.NAME)) = UPPER (TRIM (p_absence_name));
      EXCEPTION
         WHEN OTHERS
         THEN
            L_ERROR_MESSAGE := 'Absence Attendance Type Id is NULL';
       DBMS_OUTPUT.put_line ('E:' || L_ERROR_MESSAGE);
      END;


hr_person_absence_api.create_person_absence
(p_validate => FALSE
,p_effective_date => p_effective_date
,p_person_id => p_person_id
,p_business_group_id => 102
,p_absence_attendance_type_id => l_absence_attendance_type_id
,p_abs_attendance_reason_id => NULL
,p_comments => NULL
,p_date_notification => p_effective_date
,p_date_projected_start => NULL
,p_time_projected_start => NULL
,p_date_projected_end => NULL
,p_time_projected_end => NULL
,p_date_start => p_start_date
,p_time_start => NULL
,p_date_end => p_end_date
,p_time_end => NULL
,p_absence_days => O_ABSENCE_DAYS
,p_absence_hours => O_ABSENCE_HOURS
,p_authorising_person_id => NULL
,p_replacement_person_id => NULL
,p_attribute_category => NULL
,p_attribute1 => NULL
,p_attribute2 => NULL
,p_attribute3 => NULL
,p_attribute4 => NULL
,p_attribute5 => NULL
,p_attribute6 => NULL
,p_attribute7 => NULL
,p_attribute8 => NULL
,p_attribute9 => NULL
,p_attribute10 => NULL
,p_attribute11 => NULL
,p_attribute12 => NULL
,p_attribute13 => NULL
,p_attribute14 => NULL
,p_attribute15 => NULL
,p_attribute16 => NULL
,p_attribute17 => NULL
,p_attribute18 => NULL
,p_attribute19 => NULL
,p_attribute20 => NULL
,p_period_of_incapacity_id => NULL
,p_ssp1_issued => NULL
,p_maternity_id => NULL
,p_sickness_start_date => NULL
,p_sickness_end_date => NULL
,p_pregnancy_related_illness => NULL
,p_reason_for_notification_dela => NULL
,p_accept_late_notification_fla => NULL
,p_linked_absence_id => NULL
,P_ABSENCE_ATTENDANCE_ID => O_ABSENCE_ATTENDANCE_ID,
P_OBJECT_VERSION_NUMBER => O_OBJECT_VERSION_NUMBER,
P_OCCURRENCE => O_OCCURRENCE,
P_DUR_DYS_LESS_WARNING => O_DUR_DYS_LESS_WARNING,
P_DUR_HRS_LESS_WARNING => O_DUR_HRS_LESS_WARNING,
P_EXCEEDS_PTO_ENTIT_WARNING => O_EXCEEDS_PTO_ENTIT_WARNING,
P_EXCEEDS_RUN_TOTAL_WARNING => O_EXCEEDS_RUN_TOTAL_WARNING,
P_ABS_OVERLAP_WARNING => O_ABS_OVERLAP_WARNING,
P_ABS_DAY_AFTER_WARNING => O_ABS_DAY_AFTER_WARNING,
P_DUR_OVERWRITTEN_WARNING => O_DUR_OVERWRITTEN_WARNING);

END;

P_ABSENCE_ATTENDANCE_ID:=O_ABSENCE_ATTENDANCE_ID;
COMMIT;
END;
/