Employee Cost Allocation Procedure in Oracle Payroll

How to find Cost Allocation Flexfield

Please refer to the step by step guide below. I m giving the code of control file, table structure and creation procedure.

Note: Change the segment number according to your requirement. You may also change the business_group_id in procedure.

1) TABLE

CREATE TABLE DEV_COST_ALLOC

(

  LINE_ID               NUMBER,

  EFFECTIVE_DATE        DATE,

  EMP_NUM               VARCHAR2(240 BYTE),

  EMP_NAME              VARCHAR2(240 BYTE),

  LOCATION              VARCHAR2(240 BYTE), -- Change your segment accordingly

  CC_DESCRIPTION        VARCHAR2(240 BYTE),

  PROPORTION            NUMBER,

  ERROR_DESCRIPTION     VARCHAR2(240 BYTE),

  PROCESS_FLAG          VARCHAR2(240 BYTE),

  COST_ALLOCATION_ID    NUMBER,

  COMBINATION_NAME      NUMBER,

  EFFECTIVE_START_DATE  DATE,

  EFFECTIVE_END_DATE    DATE,

  OBJ_VER_NUMBER        NUMBER

)

2) CONTROL FILE

How to create control file for sql loader in Oracle

LOAD DATA
        INFILE 'Locations.csv'
INSERT
        INTO TABLE DEV_ELEMENT
        REPLACE FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
  LINE_ID               ,
  EFFECTIVE_DATE        ,
  EMP_NUM               ,
  EMP_NAME              ,
  LOCATION              ,
  CC_DESCRIPTION        ,
  PROPORTION            ,
  ERROR_DESCRIPTION     ,
  PROCESS_FLAG          ,
  COST_ALLOCATION_ID    ,
  COMBINATION_NAME      ,
  EFFECTIVE_START_DATE  ,
  EFFECTIVE_END_DATE    ,
  OBJ_VER_NUMBER      
)


3) CREATION PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_COST_ALLOCATION

IS

   CURSOR C_COST_ALLOC

   IS

      SELECT LINE_ID, EFFECTIVE_DATE, EMP_NUM, EMP_NAME, LOCATION,

             CC_DESCRIPTION, PROPORTION

        FROM DEV_COST_ALLOC;

   LC_C_COST_ALLOC                C_COST_ALLOC%ROWTYPE;

   L_COMBINATION_NAME             VARCHAR2 (240);

   L_COST_ALLOCATION_ID           NUMBER;

   L_EFFECTIVE_START_DATE         DATE;

   L_EFFECTIVE_END_DATE           DATE;

   L_COST_ALLOCATION_KEYFLEX_ID   NUMBER;

   L_OBJECT_VERSION_NUMBER        NUMBER;

   L_ATT_EFFECTIVE_DATE           DATE;

   L_PERSON_ID                    NUMBER;

   L_ASSIGNMENT_ID                NUMBER;

   ERROR_DESC                     VARCHAR2 (2000);

   LV_COMP_FLAG                   CHAR (1);

BEGIN

   OPEN C_COST_ALLOC;

   LOOP

      FETCH C_COST_ALLOC

       INTO LC_C_COST_ALLOC;

      EXIT WHEN C_COST_ALLOC%NOTFOUND;

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

-- SEQUENCE NUMBER

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

      SELECT PAPF.EFFECTIVE_START_DATE, PAPF.PERSON_ID

        INTO L_ATT_EFFECTIVE_DATE, L_PERSON_ID

        FROM PER_ALL_PEOPLE_F PAPF

       WHERE PAPF.EMPLOYEE_NUMBER = LC_C_COST_ALLOC.EMP_NUM

         AND TO_CHAR (PAPF.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712';

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

      SELECT PAAF.ASSIGNMENT_ID

        INTO L_ASSIGNMENT_ID

        FROM PER_ALL_ASSIGNMENTS_F PAAF

       WHERE PAAF.PERSON_ID = L_PERSON_ID

         AND TO_CHAR (PAAF.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712';

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

      BEGIN

         PAY_COST_ALLOCATION_API.CREATE_COST_ALLOCATION

               (P_VALIDATE                        => FALSE,

                P_EFFECTIVE_DATE                  => L_ATT_EFFECTIVE_DATE,

                P_ASSIGNMENT_ID                   => L_ASSIGNMENT_ID,

                P_PROPORTION                      => LC_C_COST_ALLOC.PROPORTION,

                P_BUSINESS_GROUP_ID               => 81,

                P_SEGMENT4                        => LC_C_COST_ALLOC.LOCATION,

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

                P_COMBINATION_NAME                => L_COMBINATION_NAME,

                P_COST_ALLOCATION_ID              => L_COST_ALLOCATION_ID,

                P_EFFECTIVE_START_DATE            => L_EFFECTIVE_START_DATE,

                P_EFFECTIVE_END_DATE              => L_EFFECTIVE_END_DATE,

                P_COST_ALLOCATION_KEYFLEX_ID      => L_COST_ALLOCATION_KEYFLEX_ID,

                P_OBJECT_VERSION_NUMBER           => L_OBJECT_VERSION_NUMBER

               );

         IF L_COST_ALLOCATION_ID IS NOT NULL

         THEN

            LV_COMP_FLAG := 'Y';

            ERROR_DESC := 'NO ERROR';

         END IF;

         IF LV_COMP_FLAG = 'Y'

         THEN

            UPDATE DEV_COST_ALLOC

               SET ERROR_DESCRIPTION = SUBSTR (ERROR_DESC, 1, 200)

             -- PROCESS_FLAG = LV_COMP_FLAG,

             --COST_ALLOCATION_ID = L_COST_ALLOCATION_ID,

             --COMBINATION_NAME = L_COMBINATION_NAME,

             -- EFFECTIVE_START_DATE = L_EFFECTIVE_START_DATE,

              --EFFECTIVE_END_DATE = L_EFFECTIVE_END_DATE,

              --OBJ_VER_NUMBER = L_OBJECT_VERSION_NUMBER

            WHERE  LINE_ID = LC_C_COST_ALLOC.LINE_ID;

         ---       OUTER LOOP ELSE CONDITION STARTS HERE ----------------------

         ELSE

            -- LV_COMP_FLAG := 'N';

            ERROR_DESC := SUBSTR (SQLERRM, 1, 254);

            UPDATE DEV_COST_ALLOC

               SET ERROR_DESCRIPTION = SUBSTR (ERROR_DESC, 1, 200)

             -- PROCESS_FLAG = LV_COMP_FLAG

            WHERE  LINE_ID = LC_C_COST_ALLOC.LINE_ID;

         END IF;

      END;

      L_COMBINATION_NAME := '';

      L_COST_ALLOCATION_ID := '';

      L_EFFECTIVE_START_DATE := '';

      L_EFFECTIVE_END_DATE := '';

      L_COST_ALLOCATION_KEYFLEX_ID := '';

      L_OBJECT_VERSION_NUMBER := '';

      L_ATT_EFFECTIVE_DATE := '';

      L_PERSON_ID := '';

      L_ASSIGNMENT_ID := '';

      ERROR_DESC := '';

      LV_COMP_FLAG := '';

   --COMMIT;

   END LOOP;

   CLOSE C_COST_ALLOC;

--DBMS_OUTPUT.PUT_LINE('LOCATION ID        : '||L_LOCATION_ID);

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

/

Please provide your feedback.

How to find correct API for Data Uploading