Friday, July 12, 2013

Oracle HRMS API - Special Information Types -SIT

CREATE OR REPLACE PACKAGE APPS.XX_HR_SIT_CREATION
AS
   PROCEDURE XX_SIT_CREATE_DRIVING;
   PROCEDURE XX_SIT_CREATE_Emirates_ID;
   PROCEDURE XX_SIT_CREATE_Health_Card;
   PROCEDURE XX_SIT_CREATE_Labour_Card;
   PROCEDURE XX_SIT_CREATE_Labour_Contract;
   PROCEDURE XX_SIT_CREATE_VISA;
   PROCEDURE XX_SIT_CREATE_PASSPORT;
   PROCEDURE XX_HR_SIT_CREATION_FINAL;
END XX_HR_SIT_CREATION;
/

CREATE OR REPLACE PACKAGE BODY APPS.xx_hr_sit_creation
IS
   PROCEDURE xx_sit_create_driving
   IS
      x_analysis_criteria_id        NUMBER;
      x_person_analysis_id          NUMBER;
      x_pea_object_version_number   NUMBER;

      x_p_person_id                 NUMBER;
      x_p_business_group_id         NUMBER;
      x_p_id_flex_num               VARCHAR2 (100);
      x_p_effective_date            DATE;

      v_error_exists                VARCHAR2 (2000);
      v_error_message               VARCHAR2 (2000);

      CURSOR c1
      IS
         SELECT employee_code,
                trim(driving_license_number)  driving_license_number,
                trim(driving_place_of_issue)   driving_place_of_issue,
                TO_DATE (driving_issue_date, 'MM/DD/YYYY') driving_issue_date,
                TO_DATE (driving_expiry_date, 'MM/DD/YYYY')
                   driving_expiry_date,
                trim(driving_license_category)  driving_license_category,
                trim(driving_license_type)  driving_license_type
           FROM xx_hrms_sit_upload
          WHERE driving_license_number IS NOT NULL  and  employee_code <> '00129';
   BEGIN
      fnd_global.apps_initialize (0, 50637, 800);        -- 0 sysadmin user id

      FOR i IN c1
      LOOP
         BEGIN
            x_analysis_criteria_id := NULL;
            x_person_analysis_id := NULL;
            x_pea_object_version_number := NULL;

            SELECT person_id, business_group_id, effective_start_date
              INTO x_p_person_id, x_p_business_group_id, x_p_effective_date
              FROM per_all_people_f
             WHERE employee_number = i.employee_code;

            SELECT                  --APPLICATION_COLUMN_NAME , SEGMENT_NAME ,
                  DISTINCT a.id_flex_num
              INTO x_p_id_flex_num
              FROM fnd_id_flex_segments_vl a, fnd_id_flex_structures_vl b
             WHERE     a.id_flex_num = b.id_flex_num
                   AND (a.id_flex_code = 'PEA')
                   AND (a.application_id = 800)
                   AND b.id_flex_structure_name = 'Driving License';

            hr_sit_api.create_sit (
               p_validate                    => NULL,
               p_person_id                   => x_p_person_id,
               p_business_group_id           => x_p_business_group_id,
               p_id_flex_num                 => x_p_id_flex_num,
               p_effective_date              => x_p_effective_date,
               p_date_from                   => SYSDATE,
               p_segment1                    => i.driving_license_number,
               p_segment2                    => i.driving_place_of_issue,
               p_segment3                    => TO_CHAR (i.driving_issue_date,
                                                         'YYYY/MM/DD'),
               p_segment4                    => TO_CHAR (i.driving_expiry_date,
                                                         'YYYY/MM/DD'),
               p_segment5                    => i.driving_license_category,
               p_segment6                    => i.driving_license_type,
               p_analysis_criteria_id        => x_analysis_criteria_id,
               p_person_analysis_id          => x_person_analysis_id,
               p_pea_object_version_number   => x_pea_object_version_number);


            UPDATE xx_hrms_sit_upload a
               SET driving_license_type_error =
                         'Analysis Criteria :  '
                      || x_analysis_criteria_id
                      || '   Person Analysis :  '
                      || x_person_analysis_id
             WHERE a.employee_code = i.employee_code;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (
                  'Diving licence is not available for the employee :  '
                  || i.employee_code);

               v_error_message :=
                  'Error Occured during Creation of Person SIT the Employee  : '
                  || '  Error : '
                  || SQLERRM;

               DBMS_OUTPUT.put_line (v_error_message);
         END;



         COMMIT;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_error_exists := 'A';
         v_error_message :=
               'Error Occured during Creation of Person SIT the Employee  : '
            || '  Error : '
            || SQLERRM;
   END;


   PROCEDURE xx_sit_create_emirates_id
   IS
      x_analysis_criteria_id        VARCHAR2 (100);
      x_person_analysis_id          VARCHAR2 (100);
      x_pea_object_version_number   VARCHAR2 (100);

      x_p_person_id                 VARCHAR2 (100);
      x_p_business_group_id         VARCHAR2 (100);
      x_p_id_flex_num               VARCHAR2 (100);
      x_p_effective_date            DATE;

      v_error_exists                VARCHAR2 (2000);
      v_error_message               VARCHAR2 (2000);

      CURSOR c1
      IS
         SELECT ROWID rowids,
                trim(employee_code)  employee_code,
                trim(emirates_id_number)  emirates_id_number,
                TO_DATE (emirates_id_expiry_date, 'MM/DD/YYYY')
                   emirates_id_expiry_date
           FROM xx_hrms_sit_upload
          WHERE 1 = 1 AND emirates_id_number IS NOT NULL   and  employee_code <> '00129';
   BEGIN
      fnd_global.apps_initialize (0, 50637, 800);        -- 0 sysadmin user id

      FOR i IN c1
      LOOP
         BEGIN
            x_analysis_criteria_id := NULL;
            x_person_analysis_id := NULL;
            x_pea_object_version_number := NULL;



            SELECT person_id, business_group_id, effective_start_date
              INTO x_p_person_id, x_p_business_group_id, x_p_effective_date
              FROM per_all_people_f
             WHERE employee_number = i.employee_code;



            SELECT                  --APPLICATION_COLUMN_NAME , SEGMENT_NAME ,
                  DISTINCT a.id_flex_num
              INTO x_p_id_flex_num
              FROM fnd_id_flex_segments_vl a, fnd_id_flex_structures_vl b
             WHERE     a.id_flex_num = b.id_flex_num
                   AND (a.id_flex_code = 'PEA')
                   AND (a.application_id = 800)
                   AND b.id_flex_structure_name = 'Emirates ID';



            hr_sit_api.create_sit (
               p_validate                    => NULL,
               p_person_id                   => x_p_person_id,
               p_business_group_id           => x_p_business_group_id,
               p_id_flex_num                 => x_p_id_flex_num,
               p_effective_date              => SYSDATE,
               p_date_from                   => SYSDATE,
               p_segment1                    => i.emirates_id_number,
               p_segment2                    => TO_CHAR (i.emirates_id_expiry_date,
                                                         'YYYY/MM/DD'),
               p_analysis_criteria_id        => x_analysis_criteria_id,
               p_person_analysis_id          => x_person_analysis_id,
               p_pea_object_version_number   => x_pea_object_version_number);

            UPDATE xx_hrms_sit_upload a
               SET emirates_id_expiry_date_error =
                         'Analysis Criteria :  '
                      || x_analysis_criteria_id
                      || '   Person Analysis :  '
                      || x_person_analysis_id
             WHERE a.employee_code = i.employee_code;

            DBMS_OUTPUT.put_line (
               '  x_analysis_criteria_id' || x_analysis_criteria_id);
            DBMS_OUTPUT.put_line (
               ' x_person_analysis_id' || x_person_analysis_id);
            DBMS_OUTPUT.put_line (
               'x_pea_object_version_number' || x_pea_object_version_number);
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (
                  'Emrites id is not available for the employee :  '
                  || i.employee_code);

               v_error_message :=
                  'Error Occured during Creation of Person SIT the Employee  : '
                  || '  Error : '
                  || SQLERRM;

               DBMS_OUTPUT.put_line (v_error_message);
         END;


         COMMIT;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_error_exists := 'A';
         v_error_message :=
               'Error Occured during Creation of Person SIT the Employee  : '
            || '  Error : '
            || SQLERRM;

         DBMS_OUTPUT.put_line ('v_error_exists' || v_error_exists);
         DBMS_OUTPUT.put_line (' v_error_message' || v_error_message);
   END;

   PROCEDURE xx_sit_create_health_card
   AS
      x_analysis_criteria_id        VARCHAR2 (100);
      x_person_analysis_id          VARCHAR2 (100);
      x_pea_object_version_number   VARCHAR2 (100);

      x_p_person_id                 VARCHAR2 (100);
      x_p_business_group_id         VARCHAR2 (100);
      x_p_id_flex_num               VARCHAR2 (100);
      x_p_effective_date            DATE;

      v_error_exists                VARCHAR2 (2000);
      v_error_message               VARCHAR2 (2000);

      CURSOR c1
      IS
         SELECT employee_code,
                trim(health_card_number)  health_card_number,
                trim(health_place_of_issue)  health_place_of_issue,
                TO_DATE (health_issue_date, 'MM/DD/YYYY') health_issue_date,
                TO_DATE (health_expiry_date, 'MM/DD/YYYY') health_expiry_date
           FROM xx_hrms_sit_upload
          WHERE 1 = 1 AND health_card_number IS NOT NULL   and  employee_code <> '00129';
   BEGIN
      fnd_global.apps_initialize (0, 50637, 800);        -- 0 sysadmin user id

      FOR i IN c1
      LOOP
         BEGIN
            x_analysis_criteria_id := NULL;
            x_person_analysis_id := NULL;
            x_pea_object_version_number := NULL;

            DBMS_OUTPUT.put_line ('  1' || x_analysis_criteria_id);

            SELECT person_id, business_group_id, effective_start_date
              INTO x_p_person_id, x_p_business_group_id, x_p_effective_date
              FROM per_all_people_f
             WHERE employee_number = i.employee_code;

            DBMS_OUTPUT.put_line ('  2' || x_analysis_criteria_id);

            SELECT                  --APPLICATION_COLUMN_NAME , SEGMENT_NAME ,
                  DISTINCT a.id_flex_num
              INTO x_p_id_flex_num
              FROM fnd_id_flex_segments_vl a, fnd_id_flex_structures_vl b
             WHERE     a.id_flex_num = b.id_flex_num
                   AND (a.id_flex_code = 'PEA')
                   AND (a.application_id = 800)
                   AND b.id_flex_structure_name = 'Health Card';

            DBMS_OUTPUT.put_line ('  3' || x_analysis_criteria_id);

            hr_sit_api.create_sit (
               p_validate                    => NULL,
               p_person_id                   => x_p_person_id,
               p_business_group_id           => x_p_business_group_id,
               p_id_flex_num                 => x_p_id_flex_num,
               p_effective_date              => SYSDATE,
               p_date_from                   => SYSDATE,
               p_segment1                    => i.health_card_number,
               p_segment2                    => i.health_place_of_issue,
               p_segment3                    => TO_CHAR (i.health_issue_date,
                                                         'YYYY/MM/DD'),
               p_segment4                    => TO_CHAR (i.health_expiry_date,
                                                         'YYYY/MM/DD'),
               p_analysis_criteria_id        => x_analysis_criteria_id,
               p_person_analysis_id          => x_person_analysis_id,
               p_pea_object_version_number   => x_pea_object_version_number);

            DBMS_OUTPUT.put_line (
               '  x_analysis_criteria_id' || x_analysis_criteria_id);
            DBMS_OUTPUT.put_line (
               ' x_person_analysis_id' || x_person_analysis_id);
            DBMS_OUTPUT.put_line (
               'x_pea_object_version_number' || x_pea_object_version_number);

            UPDATE xx_hrms_sit_upload a
               SET health_card_number_error =
                         'Analysis Criteria :  '
                      || x_analysis_criteria_id
                      || '   Person Analysis :  '
                      || x_person_analysis_id
             WHERE a.employee_code = i.employee_code;


            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (
                  'Health Card is not available for the employee :  '
                  || i.employee_code);

               v_error_message :=
                  'Error Occured during Creation of Person SIT the Employee  : '
                  || '  Error : '
                  || SQLERRM;

               DBMS_OUTPUT.put_line (v_error_message);
         END;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_error_exists := 'A';
         v_error_message :=
               'Error Occured during Creation of Person SIT the Employee  : '
            || '  Error : '
            || SQLERRM;

         DBMS_OUTPUT.put_line ('v_error_exists' || v_error_exists);
         DBMS_OUTPUT.put_line (' v_error_message' || v_error_message);
   END;

   PROCEDURE xx_sit_create_labour_card
   AS
      x_analysis_criteria_id        VARCHAR2 (100);
      x_person_analysis_id          VARCHAR2 (100);
      x_pea_object_version_number   VARCHAR2 (100);

      x_p_person_id                 VARCHAR2 (100);
      x_p_business_group_id         VARCHAR2 (100);
      x_p_id_flex_num               VARCHAR2 (100);
      x_p_effective_date            DATE;

      v_error_exists                VARCHAR2 (2000);
      v_error_message               VARCHAR2 (2000);

      CURSOR c1
      IS
         SELECT employee_code,
                trim(labor_card_number) labor_card_number,
                trim(labor_card_pin_number)  labor_card_pin_number,
                trim(labor_card_place_of_issue)  labor_card_place_of_issue,
                TO_DATE (labor_card_issue_date, 'MM/DD/YYYY')
                   labor_card_issue_date,
                TO_DATE (labor_card_expiry_date, 'MM/DD/YYYY')
                   labor_card_expiry_date,
                trim(labor_card_document_holder)  labor_card_document_holder,
                trim(labor_contract_document_number)  labor_contract_document_number,
                trim(labor_other_description)  labor_other_description
           FROM xx_hrms_sit_upload
          WHERE 1 = 1 AND labor_card_number IS NOT NULL   and  employee_code <> '00129'; --and  employee_code = '00168';
   BEGIN
      fnd_global.apps_initialize (0, 50637, 800);        -- 0 sysadmin user id

      FOR i IN c1
      LOOP
         BEGIN
            x_analysis_criteria_id := NULL;
            x_person_analysis_id := NULL;
            x_pea_object_version_number := NULL;

            DBMS_OUTPUT.put_line ('  1' || x_analysis_criteria_id);

            SELECT person_id, business_group_id, effective_start_date
              INTO x_p_person_id, x_p_business_group_id, x_p_effective_date
              FROM per_all_people_f
             WHERE employee_number = i.employee_code;

            DBMS_OUTPUT.put_line ('  2' || x_analysis_criteria_id);

            SELECT                  --APPLICATION_COLUMN_NAME , SEGMENT_NAME ,
                  DISTINCT a.id_flex_num
              INTO x_p_id_flex_num
              FROM fnd_id_flex_segments_vl a, fnd_id_flex_structures_vl b
             WHERE     a.id_flex_num = b.id_flex_num
                   AND (a.id_flex_code = 'PEA')
                   AND (a.application_id = 800)
                   AND b.id_flex_structure_name = 'Labour Card';

            DBMS_OUTPUT.put_line ('  3' || x_analysis_criteria_id);

            hr_sit_api.create_sit (
               p_validate                    => NULL,
               p_person_id                   => x_p_person_id,
               p_business_group_id           => x_p_business_group_id,
               p_id_flex_num                 => x_p_id_flex_num,
               p_effective_date              => SYSDATE,
               p_date_from                   => SYSDATE,
               p_segment1                    => i.labor_card_number,
               p_segment2                    => i.labor_card_pin_number,
               p_segment3                    => i.labor_card_place_of_issue,
               p_segment4                    => TO_CHAR (i.labor_card_issue_date,
                                                         'YYYY/MM/DD'),
               p_segment5                    => TO_CHAR (i.labor_card_expiry_date,
                                                         'YYYY/MM/DD'),
               p_segment6                    => i.labor_card_document_holder,
               p_segment7                    => i.labor_other_description,
               p_analysis_criteria_id        => x_analysis_criteria_id,
               p_person_analysis_id          => x_person_analysis_id,
               p_pea_object_version_number   => x_pea_object_version_number);

            DBMS_OUTPUT.put_line (
               '  x_analysis_criteria_id' || x_analysis_criteria_id);
            DBMS_OUTPUT.put_line (
               ' x_person_analysis_id' || x_person_analysis_id);
            DBMS_OUTPUT.put_line (
               'x_pea_object_version_number' || x_pea_object_version_number);

            UPDATE xx_hrms_sit_upload a
               SET labor_card_number_error =
                         'Analysis Criteria :  '
                      || x_analysis_criteria_id
                      || '   Person Analysis :  '
                      || x_person_analysis_id
             WHERE a.employee_code = i.employee_code;


            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (
                  'Lebor Card is not available for the employee :  '
                  || i.employee_code);

               v_error_message :=
                  'Error Occured during Creation of Person SIT the Employee  : '
                  || '  Error : '
                  || SQLERRM;

               DBMS_OUTPUT.put_line (v_error_message);
         END;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_error_exists := 'A';
         v_error_message :=
               'Error Occured during Creation of Person SIT the Employee  : '
            || '  Error : '
            || SQLERRM;

         DBMS_OUTPUT.put_line ('v_error_exists' || v_error_exists);
         DBMS_OUTPUT.put_line (' v_error_message' || v_error_message);
   END;

   PROCEDURE xx_sit_create_labour_contract
   AS
      x_analysis_criteria_id        VARCHAR2 (100);
      x_person_analysis_id          VARCHAR2 (100);
      x_pea_object_version_number   VARCHAR2 (100);

      x_p_person_id                 VARCHAR2 (100);
      x_p_business_group_id         VARCHAR2 (100);
      x_p_id_flex_num               VARCHAR2 (100);
      x_p_effective_date            DATE;

      v_error_exists                VARCHAR2 (2000);
      v_error_message               VARCHAR2 (2000);

      CURSOR c1
      IS
         SELECT employee_code,
                trim(labor_contract_document_number)  labor_contract_document_number,
                TO_DATE (labor_contract__issue_date, 'MM/DD/YYYY')
                   labor_contract__issue_date,
                TO_DATE (labor_contract__expiry_date, 'MM/DD/YYYY')
                   labor_contract__expiry_date
           FROM xx_hrms_sit_upload
          WHERE 1 = 1 AND labor_contract_document_number IS NOT NULL  and  employee_code <> '00129';
   BEGIN
      fnd_global.apps_initialize (0, 50637, 800);        -- 0 sysadmin user id

      FOR i IN c1
      LOOP
         BEGIN
            x_analysis_criteria_id := NULL;
            x_person_analysis_id := NULL;
            x_pea_object_version_number := NULL;

            DBMS_OUTPUT.put_line ('  1' || x_analysis_criteria_id);

            SELECT person_id, business_group_id, effective_start_date
              INTO x_p_person_id, x_p_business_group_id, x_p_effective_date
              FROM per_all_people_f
             WHERE employee_number = i.employee_code;

            DBMS_OUTPUT.put_line ('  2' || x_analysis_criteria_id);

            SELECT                  --APPLICATION_COLUMN_NAME , SEGMENT_NAME ,
                  DISTINCT a.id_flex_num
              INTO x_p_id_flex_num
              FROM fnd_id_flex_segments_vl a, fnd_id_flex_structures_vl b
             WHERE     a.id_flex_num = b.id_flex_num
                   AND (a.id_flex_code = 'PEA')
                   AND (a.application_id = 800)
                   AND b.id_flex_structure_name = 'Labour Contract';

            DBMS_OUTPUT.put_line ('  3' || x_analysis_criteria_id);

            hr_sit_api.create_sit (
               p_validate                    => NULL,
               p_person_id                   => x_p_person_id,
               p_business_group_id           => x_p_business_group_id,
               p_id_flex_num                 => x_p_id_flex_num,
               p_effective_date              => SYSDATE,
               p_date_from                   => SYSDATE,
               p_segment1                    => i.labor_contract_document_number,
               p_segment2                    => TO_CHAR (i.labor_contract__issue_date,
                                                         'YYYY/MM/DD'),
               p_segment3                    => TO_CHAR (
                                                  i.labor_contract__expiry_date,
                                                  'YYYY/MM/DD'),
               p_analysis_criteria_id        => x_analysis_criteria_id,
               p_person_analysis_id          => x_person_analysis_id,
               p_pea_object_version_number   => x_pea_object_version_number);

            DBMS_OUTPUT.put_line (
               '  x_analysis_criteria_id' || x_analysis_criteria_id);
            DBMS_OUTPUT.put_line (
               ' x_person_analysis_id' || x_person_analysis_id);
            DBMS_OUTPUT.put_line (
               'x_pea_object_version_number' || x_pea_object_version_number);

            UPDATE xx_hrms_sit_upload a
               SET labor_contract_number_error =
                         'Analysis Criteria :  '
                      || x_analysis_criteria_id
                      || '   Person Analysis :  '
                      || x_person_analysis_id
             WHERE a.employee_code = i.employee_code;


            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (
                  'Labor Contract is not available for the employee :  '
                  || i.employee_code);

               v_error_message :=
                  'Error Occured during Creation of Person SIT the Employee  : '
                  || '  Error : '
                  || SQLERRM;

               DBMS_OUTPUT.put_line (v_error_message);
         END;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_error_exists := 'A';
         v_error_message :=
               'Error Occured during Creation of Person SIT the Employee  : '
            || '  Error : '
            || SQLERRM;

         DBMS_OUTPUT.put_line ('v_error_exists' || v_error_exists);
         DBMS_OUTPUT.put_line (' v_error_message' || v_error_message);
   END;

   PROCEDURE xx_sit_create_visa
   AS
      x_analysis_criteria_id        VARCHAR2 (100);
      x_person_analysis_id          VARCHAR2 (100);
      x_pea_object_version_number   VARCHAR2 (100);

      x_p_person_id                 VARCHAR2 (100);
      x_p_business_group_id         VARCHAR2 (100);
      x_p_id_flex_num               VARCHAR2 (100);
      x_p_effective_date            DATE;

      v_error_exists                VARCHAR2 (2000);
      v_error_message               VARCHAR2 (2000);

      CURSOR c1
      IS
         SELECT employee_code,
                trim(visa_number) visa_number,
               trim( visa_place_of_issue)  visa_place_of_issue,
                TO_DATE (visa_issue_date, 'MM/DD/YYYY') visa_issue_date,
                TO_DATE (visa_expiry_date, 'MM/DD/YYYY') visa_expiry_date
           FROM xx_hrms_sit_upload
          WHERE 1 = 1 AND visa_number IS NOT NULL  and  employee_code <> '00129';
   BEGIN
      fnd_global.apps_initialize (0, 50637, 800);        -- 0 sysadmin user id


      FOR i IN c1
      LOOP
         BEGIN
            x_analysis_criteria_id := NULL;
            x_person_analysis_id := NULL;
            x_pea_object_version_number := NULL;

            DBMS_OUTPUT.put_line ('  1' || x_analysis_criteria_id);

            SELECT person_id, business_group_id, effective_start_date
              INTO x_p_person_id, x_p_business_group_id, x_p_effective_date
              FROM per_all_people_f
             WHERE employee_number = i.employee_code;

            DBMS_OUTPUT.put_line ('  2' || x_analysis_criteria_id);

            SELECT                  --APPLICATION_COLUMN_NAME , SEGMENT_NAME ,
                  DISTINCT a.id_flex_num
              INTO x_p_id_flex_num
              FROM fnd_id_flex_segments_vl a, fnd_id_flex_structures_vl b
             WHERE     a.id_flex_num = b.id_flex_num
                   AND (a.id_flex_code = 'PEA')
                   AND (a.application_id = 800)
                   AND b.id_flex_structure_name = 'Visa';

            DBMS_OUTPUT.put_line ('  3' || x_analysis_criteria_id);

            hr_sit_api.create_sit (
               p_validate                    => NULL,
               p_person_id                   => x_p_person_id,
               p_business_group_id           => x_p_business_group_id,
               p_id_flex_num                 => x_p_id_flex_num,
               p_effective_date              => SYSDATE,
               p_date_from                   => SYSDATE,
               p_segment1                    => i.visa_number,
               p_segment2                    => i.visa_place_of_issue,
               p_segment3                    => TO_CHAR (i.visa_issue_date,
                                                         'YYYY/MM/DD'),
               p_segment4                    => TO_CHAR (i.visa_expiry_date,
                                                         'YYYY/MM/DD'),
               p_analysis_criteria_id        => x_analysis_criteria_id,
               p_person_analysis_id          => x_person_analysis_id,
               p_pea_object_version_number   => x_pea_object_version_number);

            DBMS_OUTPUT.put_line (
               '  x_analysis_criteria_id' || x_analysis_criteria_id);
            DBMS_OUTPUT.put_line (
               ' x_person_analysis_id' || x_person_analysis_id);
            DBMS_OUTPUT.put_line (
               'x_pea_object_version_number' || x_pea_object_version_number);

            UPDATE xx_hrms_sit_upload a
               SET visa_number_error =
                         'Analysis Criteria :  '
                      || x_analysis_criteria_id
                      || '   Person Analysis :  '
                      || x_person_analysis_id
             WHERE a.employee_code = i.employee_code;


            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (
                  'Visa Number is not available for the employee :  '
                  || i.employee_code);

               v_error_message :=
                  'Error Occured during Creation of Person SIT the Employee  : '
                  || '  Error : '
                  || SQLERRM;

               DBMS_OUTPUT.put_line (v_error_message);
         END;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_error_exists := 'A';
         v_error_message :=
               'Error Occured during Creation of Person SIT the Employee  : '
            || '  Error : '
            || SQLERRM;

         DBMS_OUTPUT.put_line ('v_error_exists' || v_error_exists);
         DBMS_OUTPUT.put_line (' v_error_message' || v_error_message);
   END;

   PROCEDURE xx_sit_create_passport
   AS
      x_analysis_criteria_id        VARCHAR2 (100);
      x_person_analysis_id          VARCHAR2 (100);
      x_pea_object_version_number   VARCHAR2 (100);

      x_p_person_id                 VARCHAR2 (100);
      x_p_business_group_id         VARCHAR2 (100);
      x_p_id_flex_num               VARCHAR2 (100);
      x_p_effective_date            DATE;

      v_error_exists                VARCHAR2 (2000);
      v_error_message               VARCHAR2 (2000);

      CURSOR c1
      IS
         SELECT employee_code,
                trim(passport_name_in_passport) passport_name_in_passport,
                trim(passport_number)  passport_number,
                TO_DATE (passport_issue_date, 'MM/DD/YYYY')
                   passport_issue_date,
                TO_DATE (passport_expiry_date, 'MM/DD/YYYY')
                   passport_expiry_date,
                trim(passport_place_of_issue)  passport_place_of_issue,
                trim(passport_family_member_count)  passport_family_member_count,
                trim(passport_family_mothers_name)  passport_family_mothers_name,
               trim(passport_document_holder)  passport_document_holder,
               trim(passport_other_description)  passport_other_description,
                TO_DATE (passport_releasing_date, 'MM/DD/YYYY')
                   passport_releasing_date,
                passport_required_for,
                TO_DATE (passport_return_date, 'MM/DD/YYYY')
                   passport_return_date,
                TO_DATE (passport_expected_return_date, 'MM/DD/YYYY')
                   passport_expected_return_date
           FROM xx_hrms_sit_upload
          WHERE 1 = 1 AND passport_number IS NOT NULL  and  employee_code <> '00129';
   BEGIN
      fnd_global.apps_initialize (0, 50637, 800);        -- 0 sysadmin user id

      FOR i IN c1
      LOOP
         BEGIN
            x_analysis_criteria_id := NULL;
            x_person_analysis_id := NULL;
            x_pea_object_version_number := NULL;

            DBMS_OUTPUT.put_line ('  1' || x_analysis_criteria_id);

            SELECT person_id, business_group_id, effective_start_date
              INTO x_p_person_id, x_p_business_group_id, x_p_effective_date
              FROM per_all_people_f
             WHERE employee_number = i.employee_code;

            DBMS_OUTPUT.put_line ('  2' || x_analysis_criteria_id);

            SELECT                  --APPLICATION_COLUMN_NAME , SEGMENT_NAME ,
                  DISTINCT a.id_flex_num
              INTO x_p_id_flex_num
              FROM fnd_id_flex_segments_vl a, fnd_id_flex_structures_vl b
             WHERE     a.id_flex_num = b.id_flex_num
                   AND (a.id_flex_code = 'PEA')
                   AND (a.application_id = 800)
                   AND b.id_flex_structure_name = 'Passport';

            DBMS_OUTPUT.put_line ('  3' || x_analysis_criteria_id);

            hr_sit_api.create_sit (
               p_validate                    => NULL,
               p_person_id                   => x_p_person_id,
               p_business_group_id           => x_p_business_group_id,
               p_id_flex_num                 => x_p_id_flex_num,
               p_effective_date              => SYSDATE,
               p_date_from                   => SYSDATE,
               p_segment1                    => i.passport_name_in_passport,
               p_segment2                    => i.passport_number,
               p_segment3                    => TO_CHAR (i.passport_issue_date,
                                                         'YYYY/MM/DD'),
               p_segment4                    => TO_CHAR (i.passport_expiry_date,
                                                         'YYYY/MM/DD'),
               p_segment5                    => i.passport_place_of_issue,
               p_segment6                    => i.passport_family_member_count,
               p_segment7                    => i.passport_family_mothers_name,
               p_segment8                    => i.passport_document_holder,
               p_segment9                    => i.passport_other_description,
               p_segment11                   => TO_CHAR (i.passport_releasing_date,
                                                         'YYYY/MM/DD'),
               p_segment12                   => i.passport_required_for,
               p_segment13                   => TO_CHAR (i.passport_return_date,
                                                         'YYYY/MM/DD'),
               p_segment14                   => TO_CHAR (
                                                  i.passport_expected_return_date,
                                                  'YYYY/MM/DD'),
               p_analysis_criteria_id        => x_analysis_criteria_id,
               p_person_analysis_id          => x_person_analysis_id,
               p_pea_object_version_number   => x_pea_object_version_number);

            DBMS_OUTPUT.put_line (
               '  x_analysis_criteria_id' || x_analysis_criteria_id);
            DBMS_OUTPUT.put_line (
               ' x_person_analysis_id' || x_person_analysis_id);
            DBMS_OUTPUT.put_line (
               'x_pea_object_version_number' || x_pea_object_version_number);

            UPDATE xx_hrms_sit_upload a
               SET passport_number_error =
                         'Analysis Criteria :  '
                      || x_analysis_criteria_id
                      || '   Person Analysis :  '
                      || x_person_analysis_id
             WHERE a.employee_code = i.employee_code;


            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (
                  'Passport details is not available for the employee :  '
                  || i.employee_code);

               v_error_message :=
                  'Error Occured during Creation of Person SIT the Employee  : '
                  || '  Error : '
                  || SQLERRM;

               DBMS_OUTPUT.put_line (v_error_message);
         END;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_error_exists := 'A';
         v_error_message :=
               'Error Occured during Creation of Person SIT the Employee  : '
            || '  Error : '
            || SQLERRM;

         DBMS_OUTPUT.put_line ('v_error_exists' || v_error_exists);
         DBMS_OUTPUT.put_line (' v_error_message' || v_error_message);
   END;

   PROCEDURE xx_hr_sit_creation_final
   AS
   BEGIN
      xx_sit_create_driving;
      xx_sit_create_emirates_id;
      --  XX_SIT_CREATE_Health_Card;
      xx_sit_create_labour_card;
      xx_sit_create_labour_contract;
      xx_sit_create_visa;
      xx_sit_create_passport;

      COMMIT;
   END;
END xx_hr_sit_creation;
/

No comments:

Post a Comment