Element Creation Procedure in Oracle Payroll

How to upload Element Inputs using Procedure

Please see below for step by step guide.

Note: You may change the POST_TERMINATION_RULE in procedure and also change the LEGISLATION_CODE in procedure.

1) TABLE

CREATE TABLE DEV_ELEMENT

(

  LINE_ID                       NUMBER(15),

  EFFECTIVE_START_DATE          DATE,

  ELEMENT_NAME                  VARCHAR2(150 BYTE),

  CLASSIFICATION_NAME           VARCHAR2(150 BYTE),

  INPUT_CURRENCY_CODE           VARCHAR2(150 BYTE),

  OUTPUT_CURRENCY_CODE          VARCHAR2(150 BYTE),

  PROCESSING_PRIORITY           VARCHAR2(150 BYTE),

  MULTIPLE_ENTRIES_ALLOWED_FLA  VARCHAR2(150 BYTE),

  PROCESSING_TYPE               VARCHAR2(150 BYTE),

  ADDITIONAL_ENTRY_ALLOWED_FLA  VARCHAR2(150 BYTE),

  REPORTING_NAME                VARCHAR2(150 BYTE),

  DESCRIPTION                   VARCHAR2(150 BYTE),

  POST_TERMINATION_RULE         VARCHAR2(150 BYTE),

  PROCESS_IN_RUN_FLAG           VARCHAR2(150 BYTE),

  ELEMENT_PROCESS_FLAG          VARCHAR2(30 BYTE),

  ELEMENT_ERROR_DESCRIPTION     VARCHAR2(150 BYTE),

  ELEMENT_ID                    NUMBER(15),

  ELEMENT_DEFINITION_ID         NUMBER(15),

  ELEMENT_OBJ_VER_NUMBER        NUMBER(9)

)

2) CONTROL FILE


LOAD DATA
        INFILE 'Element.csv'
INSERT
        INTO TABLE DEV_ELEMENT
        REPLACE FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
  LINE_ID                       ,
  EFFECTIVE_START_DATE          ,
  ELEMENT_NAME                  ,
  CLASSIFICATION_NAME           ,
  INPUT_CURRENCY_CODE           ,
  OUTPUT_CURRENCY_CODE          ,
  PROCESSING_PRIORITY           ,
  MULTIPLE_ENTRIES_ALLOWED_FLA  ,
  PROCESSING_TYPE               ,
  ADDITIONAL_ENTRY_ALLOWED_FLA  ,
  REPORTING_NAME                ,
  DESCRIPTION                   ,
  POST_TERMINATION_RULE         ,
  PROCESS_IN_RUN_FLAG           ,
  ELEMENT_PROCESS_FLAG          ,
  ELEMENT_ERROR_DESCRIPTION     ,
  ELEMENT_ID                    ,
  ELEMENT_DEFINITION_ID         ,
  ELEMENT_OBJ_VER_NUMBER
)


3) PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_ELEMENT

IS

   CURSOR C_ELEMENT

   IS

      SELECT LINE_ID,EFFECTIVE_START_DATE, CLASSIFICATION_NAME,

             ELEMENT_NAME,INPUT_CURRENCY_CODE,OUTPUT_CURRENCY_CODE ,MULTIPLE_ENTRIES_ALLOWED_FLA,

             PROCESSING_TYPE,ADDITIONAL_ENTRY_ALLOWED_FLA,REPORTING_NAME,DESCRIPTION,PROCESS_IN_RUN_FLAG,

             PROCESSING_PRIORITY

             ,ELEMENT_PROCESS_FLAG, ELEMENT_ERROR_DESCRIPTION, ELEMENT_ID,

             ELEMENT_DEFINITION_ID, ELEMENT_OBJ_VER_NUMBER

        FROM DEV_ELEMENT;

       

      

    

   LC_C_ELEMENT                    C_ELEMENT%ROWTYPE;

   L_ELEMENT_TYPE_ID               NUMBER; 

   L_ELEMENT_DEFINITION_ID         NUMBER;                         

   L_EFFECTIVE_START_DATE          DATE;

   L_EFFECTIVE_END_DATE            DATE;

   L_OBJECT_VERSION_NUMBER         NUMBER;

   L_COMMENT_ID                     NUMBER;

   L_PROCESSING_PRIORITY_WARNING   BOOLEAN;

   L_CLASSIFICATION_ID             NUMBER;

   ERROR_DESC                      VARCHAR2 (240);

   LV_JOB_FLAG                     CHAR (1);

   L_SEQ                           VARCHAR2 (20);

BEGIN

   OPEN C_ELEMENT;

  LOOP

    FETCH C_ELEMENT

       INTO LC_C_ELEMENT;

    

  

        SELECT PEC.CLASSIFICATION_ID

          INTO L_CLASSIFICATION_ID

          FROM PAY_ELEMENT_CLASSIFICATIONS PEC

         WHERE UPPER (PEC.CLASSIFICATION_NAME) =

                                              UPPER (LC_C_ELEMENT.CLASSIFICATION_NAME)

           AND PEC.LEGISLATION_CODE = 'PK';

     EXIT WHEN C_ELEMENT%NOTFOUND;

  

  

      BEGIN

  

         PAY_ELEMENT_TYPES_API.CREATE_ELEMENT_TYPE

                         (

                             P_VALIDATE                      => FALSE   

                            ,P_BUSINESS_GROUP_ID             => 0

                            ,P_EFFECTIVE_DATE                => LC_C_ELEMENT.EFFECTIVE_START_DATE 

                            ,P_CLASSIFICATION_ID             => L_CLASSIFICATION_ID

                            ,P_ELEMENT_NAME                  => LC_C_ELEMENT.ELEMENT_NAME           

                            ,P_INPUT_CURRENCY_CODE           => LC_C_ELEMENT.INPUT_CURRENCY_CODE         

                            ,P_OUTPUT_CURRENCY_CODE          => LC_C_ELEMENT.OUTPUT_CURRENCY_CODE       

                            ,P_MULTIPLE_ENTRIES_ALLOWED_FLA  => LC_C_ELEMENT.MULTIPLE_ENTRIES_ALLOWED_FLA

                            ,P_ADDITIONAL_ENTRY_ALLOWED_FLA  => LC_C_ELEMENT.ADDITIONAL_ENTRY_ALLOWED_FLA

                            ,P_PROCESSING_TYPE               => LC_C_ELEMENT.PROCESSING_TYPE

                            ,P_REPORTING_NAME                => LC_C_ELEMENT.REPORTING_NAME

                            ,P_DESCRIPTION                   => LC_C_ELEMENT.DESCRIPTION                       

                            ,P_ELEMENT_TYPE_ID               => L_ELEMENT_TYPE_ID 

                            ,P_EFFECTIVE_START_DATE          => L_EFFECTIVE_START_DATE

                            ,P_EFFECTIVE_END_DATE            => L_EFFECTIVE_END_DATE

                            ,P_OBJECT_VERSION_NUMBER         => L_OBJECT_VERSION_NUMBER

                            ,P_COMMENT_ID                    => L_COMMENT_ID

                            ,P_PROCESSING_PRIORITY_WARNING   => L_PROCESSING_PRIORITY_WARNING

                            ,P_POST_TERMINATION_RULE         => 'F' -- Post termination rule has been hard-coded here...you can change it as per your requirement

                           );

                        

                        

            IF L_ELEMENT_TYPE_ID IS NOT NULL

         THEN

            LV_JOB_FLAG := 'Y';

            ERROR_DESC := 'No Error';

         END IF;

         IF LV_JOB_FLAG = 'Y'

         THEN

            UPDATE DEV_ELEMENT

               SET ELEMENT_ERROR_DESCRIPTION = ERROR_DESC,

                   ELEMENT_PROCESS_FLAG = LV_JOB_FLAG,

                   --ELEMENT_TYPE_ID = L_ELEMENT_TYPE_ID,

                   --CLASSIFICATION_ID = L_CLASSIFICATION_ID,

                   ELEMENT_OBJ_VER_NUMBER = L_OBJECT_VERSION_NUMBER,

                   ELEMENT_DEFINITION_ID = L_ELEMENT_DEFINITION_ID

             WHERE LINE_ID = LC_C_ELEMENT.LINE_ID;

         ELSE

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_ELEMENT

               SET ELEMENT_ERROR_DESCRIPTION = ERROR_DESC,

                   ELEMENT_PROCESS_FLAG = 'N'

             WHERE LINE_ID = LC_C_ELEMENT.LINE_ID;

         END IF;

         DBMS_OUTPUT.PUT_LINE ('Element Type ID    : ' || L_ELEMENT_TYPE_ID);

      EXCEPTION

         WHEN OTHERS

         THEN

            LV_JOB_FLAG := 'N';

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_ELEMENT

               SET ELEMENT_ERROR_DESCRIPTION = ERROR_DESC,

                   ELEMENT_PROCESS_FLAG = LV_JOB_FLAG

             WHERE LINE_ID = LC_C_ELEMENT.LINE_ID;

            DBMS_OUTPUT.PUT_LINE (SQLERRM);

      END;

      L_ELEMENT_TYPE_ID := '';

      L_OBJECT_VERSION_NUMBER := '';

      L_ELEMENT_DEFINITION_ID := '';

      L_CLASSIFICATION_ID := '';

      ERROR_DESC := '';

      LV_JOB_FLAG := '';

      --COMMIT;               

    

  END LOOP;

   CLOSE C_ELEMENT;

  

END;

/

Enjoy!.

How to find correct API for Data Uploading