How to upload Medical Assessments in Oracle HRMS

Navigation: Global HRMS Manager -> Others -> Medical Assessments

TABLE:

CREATE TABLE DEV_MEDICAL_ASSESSMENT

(

  LINE_ID                           NUMBER,

  EFFECTIVE_DATE                    DATE,

  EMPLOYEE_NUMBER                   VARCHAR2(240 BYTE),

  CONSULTATION_DATE                 DATE,

  CONSULTATION_TYPE                 VARCHAR2(240 BYTE),

  CONSULTATION_RESULT               VARCHAR2(240 BYTE),

  EXAMINER_NAME                     VARCHAR2(240 BYTE),

  HOSPITAL_NAME                     VARCHAR2(240 BYTE),

  INCIDENT_REFERENCE                VARCHAR2(240 BYTE),

  NEXT_CONSULTATION_DATE            DATE,

  DESCRIPTION                       VARCHAR2(240 BYTE),

  ATTRIBUTE_CATEGORY                VARCHAR2(240 BYTE),

  ATTRIBUTE1                        VARCHAR2(240 BYTE),

  ATTRIBUTE2                        VARCHAR2(240 BYTE),

  ATTRIBUTE3                        VARCHAR2(240 BYTE),

  ERROR_DESCRIPTION                 VARCHAR2(240 BYTE),

  MEDICAL_ASSESSMENT_ID             NUMBER,

  OBJECT_VERSION_NUMBER             NUMBER

)

PROCEDURE:

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_MEDICAL

IS

   CURSOR C_MEDICAL

   IS     

    SELECT LINE_ID, EFFECTIVE_DATE, EMPLOYEE_NUMBER, CONSULTATION_DATE,

           CONSULTATION_TYPE, CONSULTATION_RESULT, EXAMINER_NAME, HOSPITAL_NAME,

           INCIDENT_REFERENCE, NEXT_CONSULTATION_DATE, DESCRIPTION,

           ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3

      FROM DEV_MEDICAL_ASSESSMENT

     WHERE ERROR_DESCRIPTION IS NULL;

   LC_C_MEDICAL                     C_MEDICAL%ROWTYPE;

   L_MEDICAL_ID                     NUMBER;

   L_OBJECT_VERSION_NUMBER          NUMBER;

   L_MEDICAL_ASSESSMENT_ID          NUMBER;

   L_PERSON_ID                      NUMBER;

   L_ORG_ID                         NUMBER;

   L_CONSULTATION_TYPE              VARCHAR2 (240);

   L_CONSULTATION_RESULT            VARCHAR2 (240);

   L_INCIDENT_ID                    NUMBER;  

   ERROR_DESC                       VARCHAR2 (240);

   LV_MEDICAL_FLAG                  CHAR (1);  

BEGIN

   OPEN C_MEDICAL;

   LOOP

      FETCH C_MEDICAL

       INTO LC_C_MEDICAL;

      EXIT WHEN C_MEDICAL%NOTFOUND;

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

-- PERSON ID

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

     

        SELECT PPF.PERSON_ID

          INTO L_PERSON_ID

          FROM PER_PEOPLE_F PPF

         WHERE SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE

         AND PPF.EMPLOYEE_NUMBER = LC_C_MEDICAL.EMPLOYEE_NUMBER;

       

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

-- CONSULTATION TYPE

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

       SELECT LOOKUP_CODE

           INTO L_CONSULTATION_TYPE

           FROM HR_LOOKUPS HR1

          WHERE HR1.LOOKUP_TYPE = 'CONSULTATION_TYPE'

            AND HR1.ENABLED_FLAG = 'Y'

            AND UPPER (HR1.MEANING) LIKE UPPER (LC_C_MEDICAL.CONSULTATION_TYPE);

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

-- CONSULTATION RESULT

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

       SELECT LOOKUP_CODE

           INTO L_CONSULTATION_RESULT

           FROM HR_LOOKUPS HR1

          WHERE HR1.LOOKUP_TYPE = 'CONSULTATION_RESULT'

            AND HR1.ENABLED_FLAG = 'Y'

            AND UPPER (HR1.MEANING) LIKE UPPER (LC_C_MEDICAL.CONSULTATION_RESULT);

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

-- ORGANIZATION ID

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

         SELECT ORGANIZATION_ID

           INTO L_ORG_ID

           FROM HR_ALL_ORGANIZATION_UNITS

          WHERE UPPER (NAME) LIKE UPPER (LC_C_MEDICAL.HOSPITAL_NAME);

         

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

-- INCIDENT ID

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

    /*SELECT PWI.INCIDENT_ID

      INTO L_INCIDENT_ID

      FROM PER_WORK_INCIDENTS PWI

     WHERE PWI.INCIDENT_REFERENCE = LC_C_MEDICAL.INCIDENT_REFERENCE

       AND PWI.PERSON_ID = L_PERSON_ID;*/

      BEGIN

         PER_MEDICAL_ASSESSMENT_API.CREATE_MEDICAL_ASSESSMENT

                          (

                           P_VALIDATE                         => FALSE

                          ,P_EFFECTIVE_DATE                   => LC_C_MEDICAL.EFFECTIVE_DATE

                          ,P_PERSON_ID                        => L_PERSON_ID

                          ,P_CONSULTATION_DATE                => LC_C_MEDICAL.CONSULTATION_DATE

                          ,P_CONSULTATION_TYPE                => L_CONSULTATION_TYPE

                          ,P_EXAMINER_NAME                    => LC_C_MEDICAL.EXAMINER_NAME

                          ,P_ORGANIZATION_ID                  => L_ORG_ID

                          ,P_CONSULTATION_RESULT              => L_CONSULTATION_RESULT

                          --,P_INCIDENT_ID                      => L_INCIDENT_ID                    

                          ,P_NEXT_CONSULTATION_DATE           => LC_C_MEDICAL.NEXT_CONSULTATION_DATE

                          ,P_DESCRIPTION                      => LC_C_MEDICAL.DESCRIPTION

                          ,P_ATTRIBUTE_CATEGORY               => LC_C_MEDICAL.ATTRIBUTE_CATEGORY

                          ,P_ATTRIBUTE1                       => LC_C_MEDICAL.ATTRIBUTE1

                          ,P_ATTRIBUTE2                       => LC_C_MEDICAL.ATTRIBUTE2

                          ,P_ATTRIBUTE3                       => LC_C_MEDICAL.ATTRIBUTE3                          

                          ,P_MEDICAL_ASSESSMENT_ID            => L_MEDICAL_ASSESSMENT_ID

                          ,P_OBJECT_VERSION_NUMBER            => L_OBJECT_VERSION_NUMBER

                          );

         IF L_MEDICAL_ID IS NOT NULL

         THEN

            LV_MEDICAL_FLAG := 'Y';

            ERROR_DESC := 'NO ERROR';

         END IF;

         IF LV_MEDICAL_FLAG = 'Y'

         THEN

            UPDATE DEV_MEDICAL_ASSESSMENT

               SET ERROR_DESCRIPTION = ERROR_DESC,

                   MEDICAL_ASSESSMENT_ID = L_MEDICAL_ASSESSMENT_ID,                  

                   OBJECT_VERSION_NUMBER = L_OBJECT_VERSION_NUMBER                 

             WHERE LINE_ID = LC_C_MEDICAL.LINE_ID;

         ELSE

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_MEDICAL_ASSESSMENT

               SET ERROR_DESCRIPTION = ERROR_DESC

             WHERE LINE_ID = LC_C_MEDICAL.LINE_ID;

         END IF;

         DBMS_OUTPUT.PUT_LINE ('MEDICAL ASSESSMENT ID    : ' || L_MEDICAL_ASSESSMENT_ID);

      EXCEPTION

         WHEN OTHERS

         THEN

            LV_MEDICAL_FLAG := 'N';

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_MEDICAL_ASSESSMENT

               SET ERROR_DESCRIPTION = ERROR_DESC

             WHERE LINE_ID = LC_C_MEDICAL.LINE_ID;

            DBMS_OUTPUT.PUT_LINE (SQLERRM);

      END;

      L_MEDICAL_ASSESSMENT_ID := '';

      L_OBJECT_VERSION_NUMBER := '';           

      ERROR_DESC := '';

      LV_MEDICAL_FLAG := '';     

   END LOOP;

   CLOSE C_MEDICAL;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

/

For any query, leave comment.