Oracle HRMS API - PHOTO UPLOAD API

Step #1:
Connect to Database Using SYS / SYSTEM.
SELECT VALUE FROM V$PARAMETER WHERE UPPER(NAME) = 'UTL_FILE_DIR';
Step #2:
Connect to Database Server & Create Directory "xxempimages" in one of the 'UTL_FILE_DIR' in this
case "/usr/tmp/".
Grant READ, WRITE & EXECUTE permissions to the Directory "xxempimages".
Move all the Employee Images into this Directory.
Step #3:Connect to Database Using SYS / SYSTEM and Execute the below SQL Statements to Create Directory inside Database also to Grant permissions to APPS Schema.
CREATE DIRECTORY XXCUST_EMP_IMAGES AS '/usr/tmp/xxempimages'; GRANT READ ON DIRECTORY XXCUST_EMP_IMAGES TO APPS; GRANT WRITE ON DIRECTORY XXCUST_EMP_IMAGES TO APPS;
Step #4:
Connect to Database Using APPS and Execute the below PLSQL to Upload the Employee Images into PER_IMAGES Table.

CREATE OR REPLACE PROCEDURE APPS.XX_IMAGE_UPLOAD
AS
   CURSOR CUR_PER
   IS
      SELECT * FROM APPS.PER_ALL_PEOPLE_F;  --where employee_number = '10004';

   V_IMAGE_NAME    VARCHAR2 (240);
   V_DSTN_FILE     BLOB;
   V_SRC_FILE      BFILE;
   V_FILE_EXISTS   INTEGER := 0;
   V_AMT           INTEGER ;
BEGIN
   FOR I IN CUR_PER
   LOOP
      BEGIN
         V_IMAGE_NAME := TO_CHAR (I.attribute1) || '.jpg';
         V_SRC_FILE := BFILENAME ('XXCUST_EMP_IMAGES', V_IMAGE_NAME);
         V_FILE_EXISTS := DBMS_LOB.FILEEXISTS (V_SRC_FILE);
        
          V_AMT := DBMS_LOB.GETLENGTH(V_SRC_FILE) ;

         IF V_FILE_EXISTS = 1
         THEN
            DBMS_LOB.CREATETEMPORARY (V_DSTN_FILE, TRUE, DBMS_LOB.SESSION);
            DBMS_LOB.FILEOPEN (V_SRC_FILE, DBMS_LOB.FILE_READONLY);
            DBMS_LOB.LOADFROMFILE (V_DSTN_FILE,
                                   V_SRC_FILE,
                                    V_AMT ,
                                   1,
                                   1);
            COMMIT;
            DBMS_LOB.FILECLOSE (V_SRC_FILE);

            INSERT INTO APPS.PER_IMAGES (IMAGE_ID,
                                         PARENT_ID,
                                         TABLE_NAME,
                                         IMAGE)
                 VALUES (PER_IMAGES_S.NEXTVAL,
                         I.PERSON_ID,
                         'PER_PEOPLE_F',
                         V_DSTN_FILE);
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
               'Unable to upload image for the employee code : '
               || I.employee_number);
      END;
   END LOOP;

   COMMIT;
END;
/

Oracle HRMS API - Phone API

CREATE OR REPLACE procedure APPS.xx_phone_upload as

   p_date_from               DATE;
   p_date_to                 DATE;
   p_phone_type              VARCHAR2 (200);
   p_phone_number            VARCHAR2 (200);
   p_parent_id               NUMBER;
   p_parent_table            VARCHAR2 (200);
   p_attribute_category      VARCHAR2 (200);
   p_attribute1              VARCHAR2 (200);
   p_attribute2              VARCHAR2 (200);
   p_attribute3              VARCHAR2 (200);
   p_attribute4              VARCHAR2 (200);
   p_attribute5              VARCHAR2 (200);
   p_attribute6              VARCHAR2 (200);
   p_attribute7              VARCHAR2 (200);
   p_attribute8              VARCHAR2 (200);
   p_attribute9              VARCHAR2 (200);
   p_attribute10             VARCHAR2 (200);
   p_attribute11             VARCHAR2 (200);
   p_attribute12             VARCHAR2 (200);
   p_attribute13             VARCHAR2 (200);
   p_attribute14             VARCHAR2 (200);
   p_attribute15             VARCHAR2 (200);
   p_attribute16             VARCHAR2 (200);
   p_attribute17             VARCHAR2 (200);
   p_attribute18             VARCHAR2 (200);
   p_attribute19             VARCHAR2 (200);
   p_attribute20             VARCHAR2 (200);
   p_attribute21             VARCHAR2 (200);
   p_attribute22             VARCHAR2 (200);
   p_attribute23             VARCHAR2 (200);
   p_attribute24             VARCHAR2 (200);
   p_attribute25             VARCHAR2 (200);
   p_attribute26             VARCHAR2 (200);
   p_attribute27             VARCHAR2 (200);
   p_attribute28             VARCHAR2 (200);
   p_attribute29             VARCHAR2 (200);
   p_attribute30             VARCHAR2 (200);
   p_validate                BOOLEAN;
   p_effective_date          DATE;
   p_party_id                NUMBER;
   p_validity                VARCHAR2 (200);
   p_object_version_number   NUMBER;
   p_phone_id                NUMBER;
   v_pid                     NUMBER;
   v_partyid                 NUMBER;
   v_count                   NUMBER;
   x_msg_count               NUMBER;
   v_status                  VARCHAR2 (100);
   v_ph_type                 VARCHAR2 (100);
   errm                      VARCHAR2 (4000);

   CURSOR c1
   IS
      SELECT *
        FROM xx_phone
       WHERE phone_number IS NOT NULL and STATUS is null ;


--- WHERE phone_type <> 'PERM';
BEGIN


fnd_global.apps_initialize (0, 50637, 800);


   FOR i IN c1
   LOOP
      BEGIN
         errm := '';

         SELECT DISTINCT person_id
                    INTO v_pid
                    FROM per_all_people_f
                   WHERE employee_number = i.employee_code;

         v_status := 'S';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_status := 'E';
            errm := 'Person_id Err:' || SQLERRM || SQLCODE;
            DBMS_OUTPUT.put_line ('Employee Number Not Exist ' || errm);
      END;

      IF v_status = 'S'
      THEN
         BEGIN
            SELECT DISTINCT party_id
                       INTO v_partyid
                       FROM per_all_people_f
                      WHERE employee_number = i.employee_code;

            v_status := 'S';
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               v_status := 'E';
               errm := 'party_id :' || SQLERRM || SQLCODE || errm;
               DBMS_OUTPUT.put_line ('Error  in Party ' || errm);
         END;
      END IF;

      IF v_status = 'S'
      THEN
         BEGIN
            SELECT lookup_code
              INTO v_ph_type
              FROM fnd_lookups
             WHERE lookup_type LIKE 'JTA_EC_PHONE_TYPE'
               AND meaning = i.phone_type;

            v_status := 'S';
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               v_status := 'E';
               errm := 'Phone Type :' || SQLERRM || SQLCODE || errm;
               DBMS_OUTPUT.put_line ('Phone Type' || errm);
         END;
      END IF;

      SELECT COUNT (1)
        INTO v_count
        FROM per_phones
       WHERE parent_id = v_pid
         AND phone_number = i.phone_number
         AND phone_type = i.phone_type;

      IF (v_count > 0)
      THEN
         DBMS_OUTPUT.put_line ('Already exist');
         errm := 'Already exists' || errm;
         v_status := 'E';
      END IF;

      IF v_count = 0 AND v_status = 'S'
      THEN
         DBMS_OUTPUT.put_line ('inside block ---');
         ---v_status := 'S';
         p_date_from := i.date_from;
         p_date_to :=null;
         p_phone_type := v_ph_type;                          -- i.phone_type;
         p_phone_number := i.phone_number;
         p_parent_id := v_pid;
         p_parent_table := 'PER_ALL_PEOPLE_F';
         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_attribute21 := NULL;
         p_attribute22 := NULL;
         p_attribute23 := NULL;
         p_attribute24 := NULL;
         p_attribute25 := NULL;
         p_attribute26 := NULL;
         p_attribute27 := NULL;
         p_attribute28 := NULL;
         p_attribute29 := NULL;
         p_attribute30 := NULL;
         p_validate := FALSE;
         p_effective_date := SYSDATE;
         p_party_id := v_partyid;
         p_validity := NULL;
         p_object_version_number := NULL;
         p_phone_id := NULL;

         BEGIN
            apps.hr_phone_api.create_phone (p_date_from,
                                            p_date_to,
                                            p_phone_type,
                                            p_phone_number,
                                            p_parent_id,
                                            p_parent_table,
                                            p_attribute_category,
                                            p_attribute1,
                                            p_attribute2,
                                            p_attribute3,
                                            p_attribute4,
                                            p_attribute5,
                                            p_attribute6,
                                            p_attribute7,
                                            p_attribute8,
                                            p_attribute9,
                                            p_attribute10,
                                            p_attribute11,
                                            p_attribute12,
                                            p_attribute13,
                                            p_attribute14,
                                            p_attribute15,
                                            p_attribute16,
                                            p_attribute17,
                                            p_attribute18,
                                            p_attribute19,
                                            p_attribute20,
                                            p_attribute21,
                                            p_attribute22,
                                            p_attribute23,
                                            p_attribute24,
                                            p_attribute25,
                                            p_attribute26,
                                            p_attribute27,
                                            p_attribute28,
                                            p_attribute29,
                                            p_attribute30,
                                            p_validate,
                                            p_effective_date,
                                            p_party_id,
                                            p_validity,
                                            p_object_version_number,
                                            p_phone_id
                                           );
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (   'The phone number Not Loaded For '

                                     || 'employee number -  '
                                     || i.employee_code

                                    );
               v_status := 'E';
               errm := SQLERRM || SQLCODE || errm;
         END;
      -- ROLLBACK;
      END IF;

      DBMS_OUTPUT.put_line ('v_status -->' || v_status);

      IF (v_status != 'S')
      THEN
         errm := errm || '-' || i.employee_code || '-' || i.phone_type;
      END IF;

      DBMS_OUTPUT.put_line ('errm -->' || errm);

      UPDATE xx_phone
         SET status = v_status,
             err_msg = errm
       WHERE employee_code = i.employee_code

         AND phone_type = i.phone_type
         AND phone_number = i.phone_number
         AND date_from = i.date_from;

   COMMIT;
   END LOOP;
END;
/

Oracle HRMS API - Absence API

CREATE OR REPLACE PROCEDURE APPS.xx_absence_upload
AS
   ---------------------- API Out Parameters -------------
   p_absence_days                NUMBER;
   p_absence_hours               NUMBER;
   p_absence_attendance_id       NUMBER;
   p_object_version_number       NUMBER;
   p_occurrence                  NUMBER;
   p_dur_dys_less_warning        BOOLEAN;
   p_dur_hrs_less_warning        BOOLEAN;
   p_exceeds_pto_entit_warning   BOOLEAN;
   p_exceeds_run_total_warning   BOOLEAN;
   p_abs_overlap_warning         BOOLEAN;
   p_abs_day_after_warning       BOOLEAN;
   p_dur_overwritten_warning     BOOLEAN;
   p_days                        NUMBER;
   p_days_hours                  NUMBER;
  
       error_msg   varchar2(200);

   CURSOR c1
   IS
      SELECT A.ROWID row_id,
             a.employee_code,
             a.absance_type_name,
             (SELECT absence_attendance_type_id
                FROM per_absence_attendance_types
               WHERE name = a.absance_type_name)
                absance_id,
             absence_start_date,
             absence_end_date,
             (absence_end_date - absence_start_date) + 1 days,
             b.person_id,
             b.business_group_id,
             process_flag
        FROM xx_emp_absence_upload a, per_all_people_f b
       WHERE a.employee_code = b.employee_number
             AND TO_CHAR (SYSDATE, 'DD-MON-YYYY') BETWEEN b.effective_start_date
                                                      AND b.effective_end_date AND a.DAYS is  null  ; --AND  EMPLOYEE_CODE = '03213'  ;
--                                                      AND ABSANCE_TYPE_NAME  =  'Annual Leave';
BEGIN
   -------------------------------------------------
  
      fnd_global.apps_initialize (0, 50637, 800);    
     
   FOR i IN c1
   LOOP
      BEGIN
         p_days := i.days;
         hr_person_absence_api.create_person_absence (
            p_validate                     => FALSE,
            p_effective_date               => i.absence_start_date,
            p_person_id                    => i.person_id,
            p_business_group_id            => i.business_group_id,
            p_absence_attendance_type_id   => i.absance_id,
            p_date_start                   => i.absence_start_date,
            p_date_end                     => i.absence_end_date,
--            p_attribute1                   => i.PROCESS_FLAG,
            p_absence_days                 => p_days,
            p_absence_hours                => p_days_hours,
            p_absence_attendance_id        => p_absence_attendance_id,
            p_object_version_number        => p_object_version_number,
            p_occurrence                   => p_occurrence,
            p_dur_dys_less_warning         => p_dur_dys_less_warning,
            p_dur_hrs_less_warning         => p_dur_hrs_less_warning,
            p_exceeds_pto_entit_warning    => p_exceeds_pto_entit_warning,
            p_exceeds_run_total_warning    => p_exceeds_run_total_warning,
            p_abs_overlap_warning          => p_abs_overlap_warning,
            p_abs_day_after_warning        => p_abs_day_after_warning,
            p_dur_overwritten_warning      => p_dur_overwritten_warning);

         DBMS_OUTPUT.put_line (
            ' p_absence_attendance_id' || p_absence_attendance_id);
           
          DBMS_OUTPUT.put_line (
            ' p_occurrence' ||  p_occurrence);

         UPDATE xx_emp_absence_upload
            SET days = p_days
          WHERE ROWID = i.row_id;

         UPDATE xx_emp_absence_upload
            SET days_hours = p_days_hours
          WHERE ROWID = i.row_id;

         UPDATE xx_emp_absence_upload
            SET absence_attendance_id = p_absence_attendance_id
          WHERE ROWID = i.row_id;

         UPDATE xx_emp_absence_upload
            SET object_version_number = p_object_version_number
          WHERE ROWID = i.row_id;

         UPDATE xx_emp_absence_upload
            SET occurrence = p_occurrence
          WHERE ROWID = i.row_id;

--         UPDATE xx_emp_absence_upload
--            SET dur_dys_less_warning = to_char(p_dur_dys_less_warning)
--          WHERE ROWID = i.row_id;
--
--
--         UPDATE xx_emp_absence_upload
--            SET dur_hrs_less_warning = to_char(p_dur_hrs_less_warning)
--          WHERE ROWID = i.row_id;
--
--         UPDATE xx_emp_absence_upload
--            SET exceeds_pto_entit_warning = p_exceeds_pto_entit_warning
--          WHERE ROWID = i.row_id;
--
--
--         UPDATE xx_emp_absence_upload
--            SET exceeds_run_total_warning = to_char(p_exceeds_run_total_warning)
--          WHERE ROWID = i.row_id;
--
--         UPDATE xx_emp_absence_upload
--            SET abs_overlap_warning = to_char(p_abs_overlap_warning)
--          WHERE ROWID = i.row_id;
--
--         UPDATE xx_emp_absence_upload
--            SET abs_day_after_warning = to_char(p_abs_day_after_warning)
--          WHERE ROWID = i.row_id;
--
--         UPDATE xx_emp_absence_upload
--            SET dur_overwritten_warning = to_char(p_dur_overwritten_warning)
--          WHERE ROWID = i.row_id;



         p_absence_days := NULL;
         p_absence_hours := NULL;
         p_absence_attendance_id := NULL;
         p_object_version_number := NULL;
         p_occurrence := NULL;
         p_dur_dys_less_warning := NULL;
         p_dur_hrs_less_warning := NULL;
         p_exceeds_pto_entit_warning := NULL;
         p_exceeds_run_total_warning := NULL;
         p_abs_overlap_warning := NULL;
         p_abs_day_after_warning := NULL;
         p_dur_overwritten_warning := NULL;
      EXCEPTION
         WHEN OTHERS
         THEN
            p_absence_days := NULL;
            p_absence_hours := NULL;
            p_absence_attendance_id := NULL;
            p_object_version_number := NULL;
            p_occurrence := NULL;
            p_dur_dys_less_warning := NULL;
            p_dur_hrs_less_warning := NULL;
            p_exceeds_pto_entit_warning := NULL;
            p_exceeds_run_total_warning := NULL;
            p_abs_overlap_warning := NULL;
            p_abs_day_after_warning := NULL;
            p_dur_overwritten_warning := NULL;
           
              error_msg := SUBSTR (SQLERRM, 1, 99);
           
             UPDATE xx_emp_absence_upload
            SET dur_overwritten_warning =     error_msg
          WHERE ROWID = i.row_id;
      END;
   END LOOP;

   COMMIT;

   DBMS_OUTPUT.put_line (
      ' p_absence_attendance_id' || p_absence_attendance_id);
--EXCEPTION
--   WHEN OTHERS
--   THEN
--      NULL;
END;
/

Oracle HRMS API - Bank API

CREATE OR REPLACE PROCEDURE APPS.xxproc_employee_bank_upload
IS
   --------------- In Parameters Payment Method----------

   v_business_group_id              NUMBER := 81;
   l_per_id                         NUMBER;
   l_obj                            NUMBER;
   method_id                        NUMBER;

   ---------------Out Parameters Payment Method----------

   v_p_personal_payment_method_id   NUMBER;
   v_p_external_account_id          NUMBER;
   v_p_object_version_number        NUMBER;
   v_p_effective_start_date         DATE;
   v_p_effective_end_date           DATE;
   v_p_comment_id                   NUMBER;

   -------------- Internal Parameter Payment Method---------

   invalid_data                     EXCEPTION;
   l_total_records                  NUMBER := 0;
   l_success_records                NUMBER := 0;
   l_failure_records                NUMBER := 0;
   error_msg                        VARCHAR2 (1000);

   ------FOR UPDATE Payment Method---------

   or_object_version_number         NUMBER;
   or_comment_id                    NUMBER;
   or_external_account_id           NUMBER;
   or_effective_start_date          DATE;
   or_effective_end_date            DATE;

   ------------------------------------


   CURSOR cur_pay
   IS
      SELECT assignment.assignment_id,
             assignment.effective_start_date,
             (SELECT ORG_PAYMENT_METHOD_ID
                FROM PAY_ORG_PAYMENT_METHODS_F
               WHERE ORG_PAYMENT_METHOD_NAME = a.PAYMENT_METHOD)
                payment_method_id,
             a.*
        FROM per_all_people_f person,
             per_periods_of_service service,
             per_all_assignments_f assignment,
             xxemp_bank_upload a
       WHERE 1 = 1                         -- '04024' = PERSON.EMPLOYEE_NUMBER
             AND TRUNC (SYSDATE) BETWEEN person.effective_start_date
                                     AND person.effective_end_date
             AND TRUNC (SYSDATE) BETWEEN assignment.effective_start_date
                                     AND assignment.effective_end_date
             AND person.person_id = service.person_id(+)
             AND assignment.primary_flag = 'Y'
             --AND TRUNC(PERSON.EFFECTIVE_START_DATE) = TRUNC(SERVICE.DATE_START)
             AND person.person_id = assignment.person_id
             AND a.empno = assignment.assignment_number
--             AND ROWNUM = 1
             AND a.empno not in ('00002','03038','03045')  and status is null ;
--             AND a.status IS NULL;
BEGIN
   DBMS_OUTPUT.put_line (
      '#############################################################');
   DBMS_OUTPUT.put_line (
      'Data Migration Of Payment Method and Payment Method History :');
   DBMS_OUTPUT.put_line (
      '#############################################################');
   DBMS_OUTPUT.put_line (
      'Start Time : ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

   fnd_global.apps_initialize (0, 50637, 800);



   FOR rec_pay IN cur_pay
   LOOP
      l_total_records := l_total_records + 1;

      BEGIN
         -------------CHECK IS RECORD EXIST OR NOT------------------

         l_per_id := NULL;
         l_obj := NULL;
         method_id := NULL;



         --         BEGIN
         --            SELECT assignment_id,
         --                   object_version_number,
         --                   personal_payment_method_id
         --              INTO l_per_id, l_obj, method_id
         --              FROM pay_personal_payment_methods_f
         --             WHERE (assignment_id, effective_end_date) IN
         --                      (  SELECT assignment_id, MAX (effective_end_date)
         --                           FROM pay_personal_payment_methods_f
         --                          WHERE assignment_id = rec_pay.assignment_id
         --                       GROUP BY assignment_id);
         --         EXCEPTION
         --            WHEN NO_DATA_FOUND
         --            THEN
         --               l_per_id := -1;
         --         END;

         ------------------------------------------------------------------------------------------


         --         IF l_per_id <> -1
         --         THEN
         or_object_version_number := l_obj;

         hr_personal_pay_method_api.create_personal_pay_method (
            p_effective_date               => rec_pay.effective_start_date,
            p_assignment_id                => rec_pay.assignment_id,
            p_org_payment_method_id        => rec_pay.payment_method_id,
            p_percentage                   => 100,
            p_amount                       => NULL,
            p_priority                     => 1,
            p_territory_code               => 'AE',
            p_segment1                     => trim(rec_pay.bank_name),
            p_segment2                     => trim(rec_pay.bank_branch),
            p_segment3                     => trim(rec_pay.account_name),
            p_segment4                     => trim(rec_pay.account_number),
            p_segment5                     => trim(rec_pay.iban_no),
            p_segment6                     => trim(rec_pay.bank_code),
            p_personal_payment_method_id   => method_id,
            p_external_account_id          => or_external_account_id,
            p_object_version_number        => or_object_version_number,
            p_effective_start_date         => or_effective_start_date,
            p_effective_end_date           => or_effective_end_date,
            p_comment_id                   => or_comment_id);

         DBMS_OUTPUT.put_line ('or_comment_id  ' || or_comment_id);

         UPDATE xxemp_bank_upload a
            SET status = 'Y'
          WHERE a.empno = rec_pay.empno;

         UPDATE xxemp_bank_upload a
            SET xp_personal_payment_method_id = method_id
          WHERE a.empno = rec_pay.empno;

         UPDATE xxemp_bank_upload a
            SET xp_external_account_id = or_external_account_id
          WHERE a.empno = rec_pay.empno;

         UPDATE xxemp_bank_upload a
            SET xp_object_version_number = or_object_version_number
          WHERE a.empno = rec_pay.empno;

         UPDATE xxemp_bank_upload a
            SET xp_effective_start_date = or_effective_start_date
          WHERE a.empno = rec_pay.empno;

         UPDATE xxemp_bank_upload a
            SET xp_effective_end_date = or_effective_end_date
          WHERE a.empno = rec_pay.empno;

         UPDATE xxemp_bank_upload a
            SET xp_comment_id = or_comment_id
          WHERE a.empno = rec_pay.empno;

         COMMIT;
         l_success_records := l_success_records + 1;
      --         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_failure_records := l_failure_records + 1;
            error_msg := SUBSTR (SQLERRM, 1, 200);

            UPDATE xxemp_bank_upload a
               SET xerror_msg = error_msg
             WHERE a.empno = rec_pay.empno;


            DBMS_OUTPUT.put_line ('error_msg' || error_msg);

            UPDATE xxemp_bank_upload a
               SET a.status = NULL
             WHERE a.empno = rec_pay.empno;

            COMMIT;
      END;
   END LOOP;

   DBMS_OUTPUT.put_line (
      'End Time : ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
   DBMS_OUTPUT.put_line (
      '#############################################################');
   DBMS_OUTPUT.put_line ('Total Records To Be Loaded : ' || l_total_records);
   DBMS_OUTPUT.put_line (
      'Total Success Records      : ' || l_success_records);
   DBMS_OUTPUT.put_line (
      'Total Failure Records      : ' || l_failure_records);
   DBMS_OUTPUT.put_line (
      '#############################################################');
END;
/

Oracle HRMS API - Assignment

CREATE OR REPLACE PROCEDURE APPS.xx_assign_upload_criteria_proc
 (v_emp_no varchar2 default null, v_update_mode varchar2 default null )
AS
   vl$validate_mode                 BOOLEAN := FALSE;
    vl$assignment_id                 per_assignments_f.assignment_id%TYPE;
   vl$assignment_number             VARCHAR2 (50);
   vl$set_of_books_name             VARCHAR2 (200) := 'COMEX MXN BOOK';
   vl$set_of_books                  NUMBER;
   p_employee_number                VARCHAR2 (50) := '$__19A1099000001'; --'$__19A1099000001';
   vl$person_id                     NUMBER;
   vl$object_version_number         NUMBER;
   vl$job_title                     VARCHAR2 (200);
   vl$job_id                        NUMBER;
   vl$job_name                      VARCHAR2 (200) := 'ELECTRICIAN';
   vl$location_id                   NUMBER := 127;
   vl$called_from_mass_update       BOOLEAN := FALSE;
   V_people_group_id                NUMBER;
   V_object_version_number          NUMBER;
   V_special_ceiling_step_id        NUMBER;
   V_group_name                     VARCHAR2 (4000);
   V_effective_start_date           DATE;
   V_effective_end_date             DATE;
  V_org_now_no_manager_warning     BOOLEAN;
   v_other_manager_warning          BOOLEAN;
   v_spp_delete_warning             BOOLEAN;
   v_entries_changed_warning        VARCHAR2 (4000);
   v_tax_district_changed_warning   BOOLEAN;
   vl$fb_entra                      BOOLEAN := TRUE;
   v_concatenated_segments          VARCHAR2 (200);
   v_gsp_post_process_warning       VARCHAR2 (200);
   v_soft_coding_keyflex_id         NUMBER;

    my_errcode NUMBER;
    my_errm VARCHAR2(32000);

  v_effective_date                DATE := '01-Jan-2013';
   v_datetrack_update_mode        VARCHAR2 (50) := 'UPDATE'; -- CORRECTION|UPDATE
   CURSOR c1
   IS
  SELECT
       ( select organization_id from hr_all_organization_units where type ='LE'
       and name = v.LEGAL_EMPLOYER )
                LEGAL_EMPLOYER,
             (SELECT assignment_id
                FROM per_all_people_f a, per_all_assignments_f b
               WHERE     a.person_id = b.person_id
                     AND a.EMPLOYEE_NUMBER = v.employee_code
                    AND trunc(SYSDATE)  BETWEEN  b.EFFECTIVE_START_DATE AND  b.EFFECTIVE_END_DATE
            AND trunc(SYSDATE)  BETWEEN  a.EFFECTIVE_START_DATE AND  a.EFFECTIVE_END_DATE
                   AND ROWNUM = 1)
                assignment_id,
              (SELECT b.effective_start_date
                FROM per_all_people_f a, per_all_assignments_f b
               WHERE     a.person_id = b.person_id
                     AND a.EMPLOYEE_NUMBER = v.employee_code
                    AND trunc(SYSDATE)  BETWEEN  b.EFFECTIVE_START_DATE AND  b.EFFECTIVE_END_DATE
            AND trunc(SYSDATE)  BETWEEN  a.EFFECTIVE_START_DATE AND  a.EFFECTIVE_END_DATE
                   AND ROWNUM = 1)
                ASG_EFF_ST_date,
             (SELECT B.OBJECT_VERSION_NUMBER
                FROM per_all_people_f a, per_all_assignments_f b
               WHERE     a.person_id = b.person_id
                      AND trunc(SYSDATE)  BETWEEN  b.EFFECTIVE_START_DATE AND  b.EFFECTIVE_END_DATE
                        AND trunc(SYSDATE)  BETWEEN  a.EFFECTIVE_START_DATE AND  a.EFFECTIVE_END_DATE
                        AND a.EMPLOYEE_NUMBER = v.employee_code
                     AND ROWNUM = 1)
                obj_ver_no,
             v.employee_code,
             (SELECT POSITION_ID
                FROM HR_ALL_POSITIONS_F
               WHERE NAME = v.POSITION
               and ROWNUM = 1 )
                position_id,
             (SELECT job_ID
                FROM PER_JOBS
               WHERE NAME = v.JOB_TITLE
               and ROWNUM = 1 )
                job_id,
             v.POSITION,
             (SELECT ORGANIZATION_ID
                FROM hr_all_organization_units
               WHERE NAME = v.ORGANIZATION)
                org_id,
                (SELECT LOCATION_ID
                FROM hr_locations_all
               WHERE LOCATION_CODE = v.location)
                location,
             ASSIGNMENT_STATUS,
             STAFF_TYPE,
             SHIFT_HOURS || ' Hours' SHIFT_HOURS,
             DECODE (ACCOMMODATION_PROVIDED,
                     'Yes', 'Y',
                     'yes', 'Y',
                     'No', 'N',
                     'no', 'N',
                    Null)
                ACCOMMODATION_PROVIDED,
             DECODE (TRANSPORTAION_PROVIDED,
                     'Yes', 'Y',
                     'yes', 'Y',
                     'No', 'N',
                     'no', 'N',
                     Null)
                TRANSPORTAION_PROVIDED,
             DECODE (OVERTIME_ELIGIBLE,'yes', 'Y',  'Yes', 'Y',  'No', 'N', 'no', 'N', Null)
                OVERTIME_ELIGIBLE,
             DECODE (SPECIAL_OT_ELIGIBLE, 'yes', 'Y',  'Yes', 'Y',  'No', 'N', 'no', 'N', Null)
                SPECIAL_OT_ELIGIBLE,
                SPECIAL_OT_RATE,
             DECODE (TIME_SHEET_REQUIRED,  'yes', 'Y',  'Yes', 'Y',  'No', 'N', 'no', 'N',  Null)
                TIME_SHEET_REQUIRED,
           TO_DATE(ASSIGNMENT_EFFICITIVE_DATE,'MM/DD/YYYY')
                ASSIGNMENT_EFFICITIVE_DATE,
             (SELECT person_id
                FROM per_all_people_f
               WHERE supervisor = employee_number AND ROWNUM = 1)
                supervisor_id,
                   (SELECT assignment_id
                FROM per_all_people_f a, per_all_assignments_f b
               WHERE     a.person_id = b.person_id
                     AND a.EMPLOYEE_NUMBER = v.supervisor
                    AND trunc(SYSDATE)  BETWEEN  b.EFFECTIVE_START_DATE AND  b.EFFECTIVE_END_DATE
            AND trunc(SYSDATE)  BETWEEN  a.EFFECTIVE_START_DATE AND  a.EFFECTIVE_END_DATE
                   AND ROWNUM = 1)
                sup_assign_id,
             supervisor,
             Payroll_id,
             People_grp_id
        FROM xx_assign_upload v
       WHERE v.employee_code like nvl(v_emp_no,'%')
       and Record_criteria_processed_flag is null  ;




BEGIN
 
if v_update_mode is not null then
  v_datetrack_update_mode := v_update_mode;
 End if;

   FOR i IN c1
   LOOP

   Begin
    v_effective_date  :=  '01-Jan-2013'; 
   if v_effective_date <= i.asg_eff_st_date then
     v_effective_date := i.asg_eff_st_date ;
     v_datetrack_update_mode := 'CORRECTION' ;
    
   Else
     v_datetrack_update_mode := 'UPDATE' ;
    
   End if ;

        V_people_group_id                := null ;
        V_object_version_number          := i.obj_ver_no;
        V_special_ceiling_step_id        := null;
        V_group_name                     := null;
        V_effective_start_date           := null;
        V_effective_end_date              := null;
        v_entries_changed_warning        := null;
        v_concatenated_segments          := null;
        v_gsp_post_process_warning       := null;
     --   v_soft_coding_keyflex_id         := i.LEGAL_EMPLOYER;
      v_soft_coding_keyflex_id         := null;
     -- v_effective_date := I.ASG_EFF_ST_date;
     
   
     hr_assignment_api.update_emp_asg_criteria (
         p_effective_date                 => v_effective_date,
      p_datetrack_update_mode          => v_datetrack_update_mode,
      p_assignment_id                  => i.ASSIGNMENT_ID,
      p_validate                       => False,
      p_called_from_mass_update        => NULL,
      p_grade_id                       => NULL,
      p_position_id                    => i.POSITION_ID,
      p_job_id                         => i.job_id,
      p_payroll_id                     => i.Payroll_id,
      p_location_id                    => i.LOCATION,
      p_organization_id                => i.ORG_ID,
      p_pay_basis_id                   => NULL,
      p_segment1                       => i.STAFF_TYPE,
      p_segment2                       => i.SHIFT_HOURS,
      p_SEGMENT3                       => i.ACCOMMODATION_PROVIDED, --    Accommodation Provided
      p_SEGMENT4                       => i.TRANSPORTAION_PROVIDED, --    Transportaion Provided
      p_SEGMENT5                       => i.OVERTIME_ELIGIBLE, --    Overtime Eligible
      p_SEGMENT6                       => i.SPECIAL_OT_ELIGIBLE, --    Special OT Eligible
      p_segment7                       => i.SPECIAL_OT_RATE, --   Special OT Rate,
      p_SEGMENT8                       => i.TIME_SHEET_REQUIRED, --    Time Sheet Required
      p_SEGMENT9                       => null,
      p_supervisor_assignment_id       => i.sup_assign_id ,
      p_object_version_number          => v_object_version_number,
      p_special_ceiling_step_id        => v_special_ceiling_step_id,
      p_people_group_id                => v_people_group_id,
      p_soft_coding_keyflex_id         => v_soft_coding_keyflex_id,
      p_group_name                     => v_group_name,
      p_effective_start_date           => v_effective_start_date,
      p_effective_end_date             => v_effective_end_date,
      p_org_now_no_manager_warning     => v_org_now_no_manager_warning,
      p_other_manager_warning          => v_other_manager_warning,
      p_spp_delete_warning             => v_spp_delete_warning,
      p_entries_changed_warning        => v_entries_changed_warning,
      p_tax_district_changed_warning   => v_tax_district_changed_warning,
      p_concatenated_segments          => v_concatenated_segments,
      p_gsp_post_process_warning       => v_gsp_post_process_warning);

      -- If API is Successfull
      Update xx_assign_upload set Record_criteria_processed_flag = 'Y' , Record_criteria_processed_dt = sysdate
      where employee_code = i.employee_code ;
     
        dbms_output.put_line( 'Employee code :'|| i.employee_code || ' Updated ');
    
      Exception
       When Others then
         my_errcode := SQLCODE;
         my_errm := SQLERRM;
         update xx_assign_upload set Record_err_message = substr(('Employee code :'|| i.employee_code||'Ass Eff dt :'||
          to_char(I.ASG_EFF_ST_date,'DD-MON-YYYY') ||
           'Position id ' || i.POSITION_ID|| 'Error code ' || my_errcode || ': ' || my_errm),1, 2000),
                  Record_processed_dt = sysdate
         where employee_code = i.employee_code ;
         
         dbms_output.put_line( 'Employee code :'|| i.employee_code||'Ass Eff dt :'||
          to_char(I.ASG_EFF_ST_date,'DD-MON-YYYY') ||
           'Position id ' || i.POSITION_ID||
          'Error code ' || my_errcode || ': ' || my_errm);
    End ;
   END LOOP;

   COMMIT;
-- Output the results
EXCEPTION
   WHEN OTHERS    THEN
      DBMS_OUTPUT.put_line (
         SUBSTR ('Error ' || TO_CHAR (SQLCODE) || ': ' || SQLERRM, 1, 255));
      RAISE;
END;
/

CREATE OR REPLACE procedure APPS.XX_ASSIGN_UPLOAD_PROC ( v_emp_no varchar2 default null , v_update_mode varchar2 default null ) as

   lc_dt_ud_mode                   VARCHAR2 (100) := NULL;
   ln_assignment_id                NUMBER;
   ln_supervisor_id                NUMBER;
   ln_object_number                NUMBER;
   ln_people_group_id              NUMBER;

   -- Out Variables for Find Date Track Mode API
   -- -----------------------------------------------------------------
   lb_correction                   BOOLEAN;
   lb_update                       BOOLEAN;
   lb_update_override              BOOLEAN;
   lb_update_change_insert         BOOLEAN;

   -- Out Variables for Update Employee Assignment API
   -- ----------------------------------------------------------------------------
   ln_soft_coding_keyflex_id       HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
   lc_concatenated_segments        VARCHAR2 (2000);
   ln_comment_id                   PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
   lb_no_managers_warning          BOOLEAN;

   -- Out Variables for Update Employee Assgment Criteria
   -- -------------------------------------------------------------------------------
   ln_special_ceiling_step_id      PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
   lc_group_name                   VARCHAR2 (30);
   ld_effective_start_date         PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
   ld_effective_end_date           PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
   lb_org_now_no_manager_warning   BOOLEAN;
   lb_other_manager_warning        BOOLEAN;
   lb_spp_delete_warning           BOOLEAN;
   lc_entries_changed_warning      VARCHAR2 (30);
   lb_tax_district_changed_warn    BOOLEAN;
  my_errcode NUMBER;
    my_errm VARCHAR2(32000);
   
   v_cagr_grade_def_id            number  ;

 v_cagr_concatenated_segments     varchar2(200);
  V_concatenated_segments           varchar2 (200);
  V_soft_coding_keyflex_id        number ; -- bug 2359997
  V_comment_id                      number;
  V_effective_start_date             date;
  V_effective_end_date               date;
  v_no_managers_warning              boolean;
  v_other_manager_warning            boolean;
  v_hourly_salaried_warning          boolean;
  v_gsp_post_process_warning        varchar2(4000);
  v_effective_date                DATE := '01-Jan-2013';
  v_datetrack_update_mode        VARCHAR2 (50) := 'UPDATE'; -- CORRECTION|UPDATE

   CURSOR c1
   IS
      SELECT
       ( select organization_id from hr_all_organization_units where type ='LE'
       and name = v.LEGAL_EMPLOYER )
                LEGAL_EMPLOYER,
             (SELECT assignment_id
                FROM per_all_people_f a, per_all_assignments_f b
               WHERE     a.person_id = b.person_id
                     AND a.EMPLOYEE_NUMBER = v.employee_code
                    AND trunc(SYSDATE)  BETWEEN  b.EFFECTIVE_START_DATE AND  b.EFFECTIVE_END_DATE
            AND trunc(SYSDATE)  BETWEEN  a.EFFECTIVE_START_DATE AND  a.EFFECTIVE_END_DATE
                   AND ROWNUM = 1)
                assignment_id,
              (SELECT b.effective_start_date
                FROM per_all_people_f a, per_all_assignments_f b
               WHERE     a.person_id = b.person_id
                     AND a.EMPLOYEE_NUMBER = v.employee_code
                    AND trunc(SYSDATE)  BETWEEN  b.EFFECTIVE_START_DATE AND  b.EFFECTIVE_END_DATE
            AND trunc(SYSDATE)  BETWEEN  a.EFFECTIVE_START_DATE AND  a.EFFECTIVE_END_DATE
                   AND ROWNUM = 1)
                ASG_EFF_ST_date,
             (SELECT B.OBJECT_VERSION_NUMBER
                FROM per_all_people_f a, per_all_assignments_f b
               WHERE     a.person_id = b.person_id
                      AND trunc(SYSDATE)  BETWEEN  b.EFFECTIVE_START_DATE AND  b.EFFECTIVE_END_DATE
                        AND trunc(SYSDATE)  BETWEEN  a.EFFECTIVE_START_DATE AND  a.EFFECTIVE_END_DATE
                        AND a.EMPLOYEE_NUMBER = v.employee_code
                     AND ROWNUM = 1)
                obj_ver_no,
               
                (SELECT  pcf1.CONTRACT_ID
                 from per_all_people_f a,per_contracts_f pcf1
                    Where pcf1.Status ='A-ACTIVE'
                       and a.person_id = pcf1.person_id
                        AND trunc(SYSDATE)  BETWEEN  a.EFFECTIVE_START_DATE AND  a.EFFECTIVE_END_DATE
                        AND a.EMPLOYEE_NUMBER = v.employee_code ) contract_id,
             v.employee_code,
             (SELECT POSITION_ID
                FROM HR_ALL_POSITIONS_F
               WHERE NAME = v.POSITION
               and ROWNUM = 1 )
                position_id,
             (SELECT job_ID
                FROM PER_JOBS
               WHERE NAME = v.JOB_TITLE
               and ROWNUM = 1 )
                job_id,
             v.POSITION,
             (SELECT ORGANIZATION_ID
                FROM hr_all_organization_units
               WHERE NAME = v.ORGANIZATION and rownum=1)
                org_id,
                (SELECT LOCATION_ID
                FROM hr_locations_all
               WHERE LOCATION_CODE = v.location and rownum = 1)
                location,
             ASSIGNMENT_STATUS,
             STAFF_TYPE,
             SHIFT_HOURS || ' Hours' SHIFT_HOURS,
             DECODE (ACCOMMODATION_PROVIDED,
                     'Yes', 'Y',
                     'No', 'N',
                     'Null')
                ACCOMMODATION_PROVIDED,
             DECODE (TRANSPORTAION_PROVIDED,
                     'Yes', 'Y',
                     'No', 'N',
                     'Null')
                TRANSPORTAION_PROVIDED,
             DECODE (OVERTIME_ELIGIBLE,  'Yes', 'Y',  'No', 'N',  'Null')
                OVERTIME_ELIGIBLE,
             DECODE (SPECIAL_OT_ELIGIBLE,  'Yes', 'Y',  'No', 'N',  'Null')
                SPECIAL_OT_ELIGIBLE,
             DECODE (SPECIAL_OT_RATE,  'Yes', 'Y',  'No', 'N',  'Null')
                SPECIAL_OT_RATE,
             DECODE (TIME_SHEET_REQUIRED,  'Yes', 'Y',  'No', 'N',  'Null')
                TIME_SHEET_REQUIRED,
           TO_DATE(ASSIGNMENT_EFFICITIVE_DATE,'MM/DD/YYYY')
                ASSIGNMENT_EFFICITIVE_DATE,
             (SELECT person_id
                FROM per_all_people_f
               WHERE supervisor = employee_number AND ROWNUM = 1)
                supervisor_id,
             supervisor
        FROM xx_assign_upload v
       WHERE v.employee_code like nvl(v_emp_no,'%')
        and Record_processed_flag is null;
BEGIN
  if v_update_mode is not null then
  v_datetrack_update_mode := v_update_mode;
  End if;

   FOR i IN c1
   LOOP

   Begin
    v_effective_date  :=  '01-Jan-2013';
   if v_effective_date <= i.asg_eff_st_date then
     v_effective_date := i.asg_eff_st_date ;
     v_datetrack_update_mode := 'CORRECTION' ;
    
   Else
     v_datetrack_update_mode := 'UPDATE' ;
    
   End if ;
    

   DBMS_OUTPUT.put_line ('Test 2') ;

      ln_object_number  :=   NVL(i.OBJ_VER_NO,1) ;
      V_soft_coding_keyflex_id := null ;
     
   /*   v_effective_date                DATE := '01-Jan-2013';
   v_datetrack_update_mode        VARCHAR2 (50) := 'UPDATE'; -- CORRECTION|UPDATE*/

      hr_assignment_api.update_emp_asg (                -- Input data elements
         -- ------------------------------
         p_validate => False,
         p_effective_date             => v_effective_date,
         p_datetrack_update_mode      => v_datetrack_update_mode,
         p_assignment_id              => to_number(i.ASSIGNMENT_ID),
         p_supervisor_id              => to_number(i.supervisor_id),
  --       p_change_reason              => NULL,
  --       p_manager_flag               => NULL,
  --       p_bargaining_unit_code       => NULL,
 --        p_labour_union_member_flag   => NULL,
        p_segment1                   => I.LEGAL_EMPLOYER,
         p_segment2                   => Null,
        p_segment3                   => Null,
        p_segment4                   => Null,
        p_segment5                  => Null,
        p_segment7                   => Null,
        p_segment8                   => Null,
        p_contract_id      => i.contract_id,      
--         p_segment3                   => NULL,
 --        p_normal_hours               => NULL,
  --       p_frequency                  => NULL,
         -- Output data elements
         -- -------------------------------
        p_object_version_number      =>  ln_object_number,
        p_cagr_grade_def_id      =>      v_cagr_grade_def_id,
  p_cagr_concatenated_segments  =>    v_cagr_concatenated_segments,
  p_concatenated_segments      =>     v_concatenated_segments,
  p_soft_coding_keyflex_id  =>     v_soft_coding_keyflex_id,
          p_comment_id                 => v_comment_id,
         p_effective_start_date       => v_effective_start_date,
         p_effective_end_date         => v_effective_end_date,
         p_no_managers_warning        => v_no_managers_warning,
         p_other_manager_warning      => v_other_manager_warning
);

--         DBMS_OUTPUT.put_line (' ln_comment_id' ||  ln_comment_id);
--         DBMS_OUTPUT.put_line (' lb_no_managers_warning' ||  lb_no_managers_warning);
--         DBMS_OUTPUT.put_line (' lb_other_manager_warning' ||  lb_other_manager_warning);


       Update xx_assign_upload set Record_processed_flag = 'Y' , Record_processed_dt = sysdate
      where employee_code = i.employee_code ;
    
       dbms_output.put_line( 'Employee code :'|| i.employee_code || ' Updated ');
       
      Exception
       When Others then
         my_errcode := SQLCODE;
         my_errm := SQLERRM;
         update xx_assign_upload set Record_err_message = substr(('Employee code :'|| i.employee_code||'Ass Eff dt :'||
          to_char(I.ASG_EFF_ST_date,'DD-MON-YYYY') ||
           'Position id ' || i.POSITION_ID|| 'Error code ' || my_errcode || ': ' || my_errm),1, 2000),
            Record_processed_dt = sysdate
         where employee_code = i.employee_code ;
         
         dbms_output.put_line( 'Employee code :'|| i.employee_code||'Ass Eff dt :'||
          to_char(I.ASG_EFF_ST_date,'DD-MON-YYYY') ||
           'Position id ' || i.POSITION_ID||
          'Error code ' || my_errcode || ': ' || my_errm);

      End ;
     
     
     
   END LOOP;

DBMS_OUTPUT.put_line ('Test 5') ;
   COMMIT;
-- Output the results
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         SUBSTR ('Error ' || TO_CHAR (SQLCODE) || ': ' || SQLERRM, 1, 255));
      RAISE;
END;
/