WEB ADI - DESKTOP INTEGRATION FRAMEWORK

create table XX_TRAINING_DETAILS (

     employee_number   VARCHAR2(50),

     test_name         VARCHAR2(50),

     test_status       VARCHAR2(50),

     test_score          VARCHAR2(50) DEFAULT NULL,

     hear_rating          VARCHAR2(50) DEFAULT NULL,

     exam_date          VARCHAR2(50) DEFAULT NULL,

     exam_expiry_date  VARCHAR2(50) DEFAULT NULL,

     instruct        VARCHAR2(50) )

    

SELECT * FROM XX_TRAINING_DETAILS

/* Formatted on 2012/05/24 15:26 (Formatter Plus v4.8.8) */

CREATE OR REPLACE PACKAGE xx_hr_webadi_pkg

AS

   PROCEDURE upload_training_details (

      p_employee_number    VARCHAR2,

      p_test_name          VARCHAR2,

      p_test_status        VARCHAR2,

      p_test_score         VARCHAR2 DEFAULT NULL,

      p_hear_rating        VARCHAR2 DEFAULT NULL,

      p_exam_date          VARCHAR2 DEFAULT NULL,

      p_exam_expiry_date   VARCHAR2 DEFAULT NULL,

      p_instruct           VARCHAR2

   );

END xx_hr_webadi_pkg;

/

CREATE OR REPLACE PACKAGE BODY xx_hr_webadi_pkg

AS

   PROCEDURE upload_training_details (

      p_employee_number    VARCHAR2,

      p_test_name          VARCHAR2,

      p_test_status        VARCHAR2,

      p_test_score         VARCHAR2 DEFAULT NULL,

      p_hear_rating        VARCHAR2 DEFAULT NULL,

      p_exam_date          VARCHAR2 DEFAULT NULL,

      p_exam_expiry_date   VARCHAR2 DEFAULT NULL,

      p_instruct           VARCHAR2

   )

   IS

      l_person_extra_info_id    NUMBER;

      l_object_version_number   NUMBER;

      l_information_type        VARCHAR2 (100) := 'XX_TRAINING_DTLS';

      l_person_id               NUMBER;

      l_error_flag              VARCHAR2 (10)  := 'NO';

      e_emp_no_null             EXCEPTION;

      e_invalid_emp             EXCEPTION;

      e_year_null               EXCEPTION;

   BEGIN

      IF p_employee_number IS NULL

      THEN

         l_error_flag := 'YES';

         RAISE e_emp_no_null;

      END IF;

      IF p_test_name IS NULL OR p_test_status IS NULL

      THEN

         l_error_flag := 'YES';

         RAISE e_year_null;

      END IF;

      IF l_error_flag = 'NO'

      THEN

         INSERT INTO xx_training_details

              VALUES (p_employee_number, p_test_name, p_test_status,

                      p_test_score, p_hear_rating, p_exam_date,

                      p_exam_expiry_date, p_instruct);

         COMMIT;

      END IF;

   EXCEPTION

      WHEN e_emp_no_null

      THEN

         raise_application_error (-20000,

                                  'Employee Number is a Mandatory Parameter.'

                                 );

      WHEN e_invalid_emp

      THEN

         raise_application_error

            (-20000,

             'Employee Number provided doesnt exists, Please check and reload.'

            );

      WHEN e_year_null

      THEN

         raise_application_error

            (-20000,

             'Test Name and Status parameter is Mandatory. Please re-enter and upload again'

            );

   END upload_training_details;

END xx_hr_webadi_pkg;

/

BEGIN

 xx_hr_webadi_pkg.upload_training_details (

      p_employee_number => '101'

      ,p_test_name        => 'AP'

      ,p_test_status      => 'P'

      ,p_test_score       => '52'

      ,p_hear_rating      => '6'

      ,p_exam_date        => '23-DEC-11' 

      ,p_exam_expiry_date  => '23-DEC-11'

      ,p_instruct          => 'AJIT'

   );

END xx_hr_webadi_pkg;

/

COMMIT

 

AA_TRAINING_DATA_UPLOAD