Element Inputs Creation Procedure in Oracle Payroll

How to upload Element Links using Procedure

Please refer below.

Note! Control file code is not pasted here, you can obviously create control file using table structure by yourself.

How to create Control file for Sql Loader

1) TABLE

CREATE TABLE DEV_ELEMENT_INPUTS

(

  LINE_ID                  NUMBER(15),

  EFFECTIVE_START_DATE     DATE,

  ELEMENT_NAME             VARCHAR2(150 BYTE),

  INPUT_NAME               VARCHAR2(150 BYTE),

  UOM                      VARCHAR2(150 BYTE),

  GENERATE_DB_ITEMS_FLAG   VARCHAR2(150 BYTE),

  INPUT_PROCESS_FLAG       VARCHAR2(2000 BYTE),

  INPUT_ERROR_DESCRIPTION  VARCHAR2(2000 BYTE),

  INPUT_ID                 NUMBER(15),

  INPUT_DEFINITION_ID      NUMBER(15),

  INPUT_OBJ_VER_NUMBER     NUMBER(15)

)

2) PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_ELEMENT_INPUTS

IS

   CURSOR C_ELEMENT_INPUTS

   IS

      SELECT LINE_ID,EFFECTIVE_START_DATE,ELEMENT_NAME, INPUT_NAME,UOM,GENERATE_DB_ITEMS_FLAG,

              INPUT_PROCESS_FLAG, INPUT_ERROR_DESCRIPTION, INPUT_ID,

             INPUT_DEFINITION_ID, INPUT_OBJ_VER_NUMBER

        FROM DEV_ELEMENT_INPUTS;

      

    

   LC_C_ELEMENT_INPUTS      C_ELEMENT_INPUTS%ROWTYPE;

   L_ELEMENT_TYPE_ID        NUMBER; 

   L_INPUT_VALUE_ID         NUMBER;

   L_OBJECT_VERSION_NUMBER  NUMBER;

   L_EFFECTIVE_START_DATE   DATE;

   L_EFFECTIVE_END_DATE     DATE;

   L_DEFAULT_VAL_WARNING    BOOLEAN;

   L_MIN_MAX_WARNING        BOOLEAN;

   L_PAY_BASIS_WARNING      BOOLEAN;

   L_FORMULA_WARNING        BOOLEAN;

   L_ASSIGNMENT_ID_WARNING  BOOLEAN;

   L_FORMULA_MESSAGE        VARCHAR2(240);

   ERROR_DESC               VARCHAR2 (240);

   LV_JOB_FLAG              CHAR (1);

   L_SEQ                    VARCHAR2 (20);

   L_LOOKUPCODE             VARCHAR2 (240);

BEGIN

   OPEN C_ELEMENT_INPUTS;

  LOOP

    FETCH C_ELEMENT_INPUTS

       INTO LC_C_ELEMENT_INPUTS;

  

      SELECT FLV.LOOKUP_CODE INTO L_LOOKUPCODE FROM FND_LOOKUP_VALUES_VL FLV

      WHERE FLV.LOOKUP_TYPE = 'UNITS'

      AND UPPER(FLV.MEANING) = UPPER(LC_C_ELEMENT_INPUTS.UOM);

  

      

      SELECT PETF.ELEMENT_TYPE_ID INTO L_ELEMENT_TYPE_ID FROM PAY_ELEMENT_TYPES_F PETF

      WHERE UPPER(PETF.ELEMENT_NAME)= UPPER(LC_C_ELEMENT_INPUTS.ELEMENT_NAME);

  

     EXIT WHEN C_ELEMENT_INPUTS%NOTFOUND;

  

  

      BEGIN

  

         PAY_INPUT_VALUE_API.CREATE_INPUT_VALUE

                         (

                            P_VALIDATE                   => FALSE                             

                           ,P_EFFECTIVE_DATE             => LC_C_ELEMENT_INPUTS.EFFECTIVE_START_DATE

                           ,P_ELEMENT_TYPE_ID            => L_ELEMENT_TYPE_ID 

                           ,P_NAME                       => LC_C_ELEMENT_INPUTS.INPUT_NAME

                           ,P_UOM                        => L_LOOKUPCODE

                           ,P_GENERATE_DB_ITEMS_FLAG     => LC_C_ELEMENT_INPUTS.GENERATE_DB_ITEMS_FLAG

                           ,P_INPUT_VALUE_ID             => L_INPUT_VALUE_ID

                           ,P_OBJECT_VERSION_NUMBER      => L_OBJECT_VERSION_NUMBER

                           ,P_EFFECTIVE_START_DATE       => L_EFFECTIVE_START_DATE

                           ,P_EFFECTIVE_END_DATE         => L_EFFECTIVE_END_DATE

                           ,P_DEFAULT_VAL_WARNING        => L_DEFAULT_VAL_WARNING

                           ,P_MIN_MAX_WARNING            => L_MIN_MAX_WARNING

                           ,P_PAY_BASIS_WARNING          => L_PAY_BASIS_WARNING

                           ,P_FORMULA_WARNING            => L_FORMULA_WARNING

                           ,P_ASSIGNMENT_ID_WARNING      => L_ASSIGNMENT_ID_WARNING

                           ,P_FORMULA_MESSAGE            => L_FORMULA_MESSAGE

                           );

                        

                        

            IF L_INPUT_VALUE_ID IS NOT NULL

         THEN

            LV_JOB_FLAG := 'Y';

            ERROR_DESC := 'NO ERROR';

         END IF;

         IF LV_JOB_FLAG = 'Y'

         THEN

            UPDATE DEV_ELEMENT_INPUTS

               SET INPUT_ERROR_DESCRIPTION = ERROR_DESC,

                   INPUT_PROCESS_FLAG = LV_JOB_FLAG,

                   --ELEMENT_TYPE_ID = L_ELEMENT_TYPE_ID,

                   --CLASSIFICATION_ID = L_CLASSIFICATION_ID,

                   INPUT_OBJ_VER_NUMBER = L_OBJECT_VERSION_NUMBER

                   --ELEMENT_DEFINITION_ID = L_ELEMENT_DEFINITION_ID

             WHERE LINE_ID = LC_C_ELEMENT_INPUTS.LINE_ID;

         ELSE

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_ELEMENT_INPUTS

               SET INPUT_ERROR_DESCRIPTION = ERROR_DESC,

                   INPUT_PROCESS_FLAG = 'N'

             WHERE LINE_ID = LC_C_ELEMENT_INPUTS.LINE_ID;

         END IF;

         DBMS_OUTPUT.PUT_LINE ('INPUT VALUE ID    : ' || L_INPUT_VALUE_ID );

      EXCEPTION

         WHEN OTHERS

         THEN

            LV_JOB_FLAG := 'N';

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_ELEMENT_INPUTS

               SET INPUT_ERROR_DESCRIPTION = ERROR_DESC,

                   INPUT_PROCESS_FLAG = LV_JOB_FLAG

             WHERE LINE_ID = LC_C_ELEMENT_INPUTS.LINE_ID;

            --DBMS_OUTPUT.PUT_LINE (SQLERRM);

      END;

      L_INPUT_VALUE_ID  := '';

      L_OBJECT_VERSION_NUMBER := '';

      ERROR_DESC := '';

      LV_JOB_FLAG := '';

                    

    

  END LOOP;

   CLOSE C_ELEMENT_INPUTS;

  

END;

/

Leave a comment for any query.

How to find correct API for Data Uploading