How to call procedure with parameters in a trigger Oracle PL/SQL

Requirement:

When an absence is entered into System, an SIT should be auto filled based on data available on Absence form.

Here is the procedure to upload data into SIT(Special Information Type)

How to create SIT in Oracle HRMS

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_SPECIAL_INFO(P_PERSON_ID IN NUMBER,P_DATE_START IN DATE,P_DATE_END IN DATE)

IS

   L_ID_FLEX_NUM               NUMBER;

   L_PERSON_ID                 NUMBER;

   L_NAME                      VARCHAR2 (240);

   ERROR_DESC                  VARCHAR2 (240);

   LV_SIT_FLAG                 CHAR (1);

   L_OBJECT_VERSION_NUMBER     NUMBER;

   L_ANALYSIS_CRITERIA_ID      NUMBER;

   L_PERSON_ANALYSIS_ID        NUMBER;

   L_PEA_OBJECT_VERSION_NUMBER NUMBER;

  

  

BEGIN

    L_PERSON_ID := P_PERSON_ID;

   

   

       

       

      BEGIN

         HR_SIT_API.CREATE_SIT

                        (P_VALIDATE                   => FALSE,

                         P_PERSON_ID                  => L_PERSON_ID,

                         P_BUSINESS_GROUP_ID          => 2217,--FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'),

                         P_ID_FLEX_NUM                => 50647,--L_ID_FLEX_NUM,

                         P_DATE_FROM                  => P_DATE_START,

                         P_DATE_TO                    => P_DATE_END,

                         P_EFFECTIVE_DATE             => P_DATE_END,

                         P_SEGMENT1                   => to_char(P_DATE_START,'YYYY/MM/DD HH24:MI:SS'),-- P_DATE_START,

                         P_SEGMENT2                   => to_char(P_DATE_END,'YYYY/MM/DD HH24:MI:SS'),--P_DATE_END,

                         --P_SEGMENT3                   => LC_C_SIT.SEGMENT3,

                         --,P_CONCAT_SEGMENTS           =>

                         --P_ATTRIBUTE_CATEGORY         => LC_C_SIT.ATTRIBUTE_CATEGORY,

                         --P_ATTRIBUTE1                 => LC_C_SIT.ATTRIBUTE1,

                         --P_ATTRIBUTE2                 => LC_C_SIT.ATTRIBUTE2,                        

                         P_ANALYSIS_CRITERIA_ID       => L_ANALYSIS_CRITERIA_ID,

                         P_PERSON_ANALYSIS_ID         => L_PERSON_ANALYSIS_ID,

                         P_PEA_OBJECT_VERSION_NUMBER  => L_PEA_OBJECT_VERSION_NUMBER

                        

                        );

      END;

      L_OBJECT_VERSION_NUMBER := '';

      ERROR_DESC := '';

      LV_SIT_FLAG := '';

      L_OBJECT_VERSION_NUMBER  :=   '';

      L_ANALYSIS_CRITERIA_ID   :=   '';

      L_PERSON_ANALYSIS_ID     :=  '';

      L_PEA_OBJECT_VERSION_NUMBER := '';

  

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

/

Trigger

create or replace trigger Auto_ticket

AFTER insert on per_absence_attendances

for each row

declare

    PRAGMA AUTONOMOUS_TRANSACTION;

begin

  APPS.DEV_CREATE_SPECIAL_INFO(:new.person_id,:new.date_start,:new.date_end);

  COMMIT;

end;

Leave comment for any query.

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.