Employee Payment Methods in Oracle Payroll

NOTE: The table structure and procedure may need to be updated according to your configuration.

Number of Segments on Bank Details KFF can be different for your configuration, kindly update it accordingly.

Here are  the details for uploading Employee' Payment methods.

TABLE

CREATE TABLE APPS.DEV_PERSONAL_PAY_METHOD

(

  LINE_ID            NUMBER,

  EMPLOYEE_NUMBER    VARCHAR2(100 BYTE),

  PAY_METHOD_NAME    VARCHAR2(240 BYTE),

  PERCENT            NUMBER,

  BANK_CODE          VARCHAR2(240 BYTE),

  BANK_NAME          VARCHAR2(240 BYTE),

  BRANCH_NAME        VARCHAR2(240 BYTE),

  ACCT_TITLE         VARCHAR2(240 BYTE),

  ACCT_NUMBER        VARCHAR2(240 BYTE),

  BANK_ADDRESS       VARCHAR2(240 BYTE),

  PAYMENT_MODE       VARCHAR2(240 BYTE),

  PER_PAY_METHOD_ID  NUMBER,

  EXT_ACCT_ID        NUMBER,

  OBJ_VER_NUMBER     NUMBER,

  EFFECTIVE_DATE     DATE,

  EFF_START_DATE     DATE,

  EFF_END_DATE       DATE,

  COMMENT_ID         NUMBER,

  PROCESS_FLAG       CHAR(1 BYTE),

  ERROR_DESCRIPTION  VARCHAR2(240 BYTE),

  PERCENTAGE         NUMBER(20),

  PRIORITY           NUMBER(20),

  ACCOUNT_TYPE       VARCHAR2(20 BYTE),

  PAYMENT_TYPE       VARCHAR2(50 BYTE)

)

PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_ASG_PAYMENT_METHOD

IS

   CURSOR C_EMP_PM

   IS

      SELECT   LINE_ID, EMPLOYEE_NUMBER, PAY_METHOD_NAME, PERCENT,

               PAYMENT_TYPE, PAYMENT_MODE, BANK_NAME, BRANCH_NAME,

               ACCT_TITLE, ACCOUNT_TYPE, ACCT_NUMBER, PRIORITY,

               PER_PAY_METHOD_ID, EXT_ACCT_ID, OBJ_VER_NUMBER,

               EFFECTIVE_DATE, PROCESS_FLAG, ERROR_DESCRIPTION, BANK_ADDRESS,

               BANK_CODE

          FROM DEV_PERSONAL_PAY_METHOD

      --WHERE NVL (PROCESS_FLAG, 'N') <> 'Y' AND ERROR_DESCRIPTION IS NULL

      ORDER BY LINE_ID;

   LC_C_EMP_PM                    C_EMP_PM%ROWTYPE;

   L_PERSONAL_PAYMENT_METHOD_ID   NUMBER;

   L_ORG_PAYMENT_METHOD_ID        NUMBER;

   L_EXTERNAL_ACCOUNT_ID          NUMBER;

   L_OBJECT_VERSION_NUMBER        NUMBER;

   L_EFFECTIVE_START_DATE         DATE;

   L_EFFECTIVE_END_DATE           DATE;

   L_COMMENT_ID                   NUMBER;

   ERR_DESC                       VARCHAR2 (240);

   LV_TERRITORY_CODE              CHAR (2);

   LV_PM_FLAG                     CHAR (1);

   L_EMP_ASSG_ID                  NUMBER (20);

   L_PM_ID                        NUMBER;

   AS_EFFECTIVE_START_DATE        DATE;

   L_PAYMENT_TYPE_ID              NUMBER;

BEGIN

   LV_TERRITORY_CODE := 'PK';       -- CHANGE YOUR TERRITORY CODE ACCORDINGLY

   OPEN C_EMP_PM;

   LOOP

      FETCH C_EMP_PM

       INTO LC_C_EMP_PM;

      EXIT WHEN C_EMP_PM%NOTFOUND;

      BEGIN

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

-- EMPLOYEE ASSIGNMENT ID

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

         BEGIN

            SELECT PAAF.ASSIGNMENT_ID, PAAF.EFFECTIVE_START_DATE

              INTO L_EMP_ASSG_ID, AS_EFFECTIVE_START_DATE

              FROM PER_ALL_ASSIGNMENTS_F PAAF

             WHERE PAAF.PERSON_ID =

                      (SELECT DISTINCT PAPF.PERSON_ID

                                  FROM PER_ALL_PEOPLE_F PAPF

                                 WHERE PAPF.EMPLOYEE_NUMBER =

                                                   LC_C_EMP_PM.EMPLOYEE_NUMBER)

               AND PAAF.PRIMARY_FLAG = 'Y'

               AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE

                               AND PAAF.EFFECTIVE_END_DATE;

         EXCEPTION

            WHEN OTHERS

            THEN

               ERR_DESC := 'EMPLOYEE NOT FOUND';

         END;

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

-- PERSONAL PAYMENT METHOD ID

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

/*         BEGIN

            SELECT PPPM.PERSONAL_PAYMENT_METHOD_ID,

                   PPPM.EFFECTIVE_START_DATE, PPPM.OBJECT_VERSION_NUMBER,

                   PPPM.EXTERNAL_ACCOUNT_ID

              INTO L_PERSONAL_PAYMENT_METHOD_ID,

                   L_EFFECTIVE_START_DATE, L_OBJECT_VERSION_NUMBER,

                   L_EXTERNAL_ACCOUNT_ID

              FROM PAY_PERSONAL_PAYMENT_METHODS_F PPPM

             WHERE SYSDATE BETWEEN PPPM.EFFECTIVE_START_DATE

                               AND PPPM.EFFECTIVE_END_DATE

               AND PPPM.ASSIGNMENT_ID = L_EMP_ASSG_ID;

         EXCEPTION

            WHEN OTHERS

            THEN

               ERR_DESC := 'PERSONAL PAYMENT METHOD NOT FOUND OR ACTIVE';

         END;

*/

         BEGIN

            SELECT OPM.ORG_PAYMENT_METHOD_ID

              INTO L_ORG_PAYMENT_METHOD_ID

              FROM PAY_ORG_PAYMENT_METHODS_F OPM

             WHERE TRIM (OPM.ORG_PAYMENT_METHOD_NAME) =

                                            TRIM (LC_C_EMP_PM.PAY_METHOD_NAME)

--        UPPER (OPM.ORG_PAYMENT_METHOD_NAME) LIKE

--                                           UPPER (LC_C_EMP_PM.PAY_METHOD_NAME)

               AND SYSDATE BETWEEN OPM.EFFECTIVE_START_DATE

                               AND OPM.EFFECTIVE_END_DATE;

         EXCEPTION

            WHEN OTHERS

            THEN

               ERR_DESC := ERR_DESC || 'PAYMENT METHOD NOT FOUND OR ACTIVE.';

         END;

         /*

             IF LC_C_EMP_PM.PAYMENT_TYPE IS NOT NULL THEN

             BEGIN

                SELECT PAYMENT_TYPE_ID

                INTO L_PAYMENT_TYPE_ID

                FROM  PAY_PAYMENT_TYPES

                WHERE CURRENCY_CODE = 'PKR'

                AND PAYMENT_TYPE_NAME = LC_C_EMP_PM.PAYMENT_TYPE;

                EXCEPTION

                WHEN OTHERS

                THEN

                   ERR_DESC :=

                         ERR_DESC

                      || 'PAYMENT TYPE ERROR'

                      || SUBSTR (SQLERRM, 1, 100);

                   L_PAYMENT_TYPE_ID := NULL;

             END ;

             END IF;

           */

         IF AS_EFFECTIVE_START_DATE < LC_C_EMP_PM.EFFECTIVE_DATE

         THEN

            AS_EFFECTIVE_START_DATE := LC_C_EMP_PM.EFFECTIVE_DATE;

         END IF;

         -- IF ERR_DESC IS NULL

         -- THEN

         HR_PERSONAL_PAY_METHOD_API.CREATE_PERSONAL_PAY_METHOD

                (P_VALIDATE                        => FALSE,

                 P_EFFECTIVE_DATE                  => AS_EFFECTIVE_START_DATE,

                 --P_EFFECTIVE_DATE                => LC_C_EMP_PM.EFFECTIVE_DATE,

                 P_ASSIGNMENT_ID                   => L_EMP_ASSG_ID,

                 P_ORG_PAYMENT_METHOD_ID           => L_ORG_PAYMENT_METHOD_ID,

                 --   P_PAYMENT_TYPE_ID               => L_PAYMENT_TYPE_ID,

                 P_PERCENTAGE                      => LC_C_EMP_PM.PERCENT,

                                                                      --- 100,

                 P_PRIORITY                        => LC_C_EMP_PM.PRIORITY,

                                                                         ---1,

                 P_TERRITORY_CODE                  => 'PK',

                                                          --LV_TERRITORY_CODE,

                  --P_ATTRIBUTE_CATEGORY            => 'ABC STOP PAYMENT',

                 -- P_ATTRIBUTE2                    => 'NO',

                 P_SEGMENT1                        => LC_C_EMP_PM.ACCT_NUMBER,

                 P_SEGMENT2                        => LC_C_EMP_PM.ACCT_TITLE,

                 P_SEGMENT3                        => LC_C_EMP_PM.ACCOUNT_TYPE,

                 P_SEGMENT4                        => LC_C_EMP_PM.BANK_CODE,

                 P_SEGMENT5                        => LC_C_EMP_PM.BANK_NAME,

                 P_SEGMENT6                        => LC_C_EMP_PM.BRANCH_NAME,

                 P_SEGMENT7                        => LC_C_EMP_PM.BANK_ADDRESS,

                 P_PERSONAL_PAYMENT_METHOD_ID      => L_PERSONAL_PAYMENT_METHOD_ID,

                 P_EXTERNAL_ACCOUNT_ID             => L_EXTERNAL_ACCOUNT_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_COMMENT_ID                      => L_COMMENT_ID

                );

         -- END IF;

         IF L_PERSONAL_PAYMENT_METHOD_ID IS NOT NULL

         THEN

            LV_PM_FLAG := 'Y';

            ERR_DESC := 'NO ERROR';

         END IF;

         IF LV_PM_FLAG = 'Y'

         THEN

            UPDATE DEV_PERSONAL_PAY_METHOD

               SET ERROR_DESCRIPTION = ERR_DESC,

                   PROCESS_FLAG = LV_PM_FLAG,

                   PER_PAY_METHOD_ID = L_PERSONAL_PAYMENT_METHOD_ID,

                   OBJ_VER_NUMBER = L_OBJECT_VERSION_NUMBER,

                   EFF_START_DATE = L_EFFECTIVE_START_DATE,

                   EFF_END_DATE = L_EFFECTIVE_END_DATE,

                   COMMENT_ID = L_COMMENT_ID,

                   EXT_ACCT_ID = L_EXTERNAL_ACCOUNT_ID

             WHERE EMPLOYEE_NUMBER = LC_C_EMP_PM.EMPLOYEE_NUMBER

               AND LINE_ID = LC_C_EMP_PM.LINE_ID;

         ELSE

            ERR_DESC := '1.' || ERR_DESC || SQLERRM;

            UPDATE DEV_PERSONAL_PAY_METHOD

               SET ERROR_DESCRIPTION = ERR_DESC,

                   PROCESS_FLAG = 'N'

             WHERE EMPLOYEE_NUMBER = LC_C_EMP_PM.EMPLOYEE_NUMBER

               AND LINE_ID = LC_C_EMP_PM.LINE_ID;

         --   DBMS_OUTPUT.PUT_LINE('EMPLOYEE IS CREATED WITHOUT ANY WARNING');

         END IF;

      EXCEPTION

         WHEN OTHERS

         THEN

            LV_PM_FLAG := 'N';

            ERR_DESC := '2.' || ERR_DESC || SQLERRM;

            UPDATE DEV_PERSONAL_PAY_METHOD

               SET ERROR_DESCRIPTION = ERR_DESC,

                   PROCESS_FLAG = LV_PM_FLAG

             WHERE EMPLOYEE_NUMBER = LC_C_EMP_PM.EMPLOYEE_NUMBER

               AND LINE_ID = LC_C_EMP_PM.LINE_ID;

--            DBMS_OUTPUT.PUT_LINE (SQLERRM);

--COMMIT;

      END;

/*      DBMS_OUTPUT.PUT_LINE (   'PERSONAL PAY METHOD ID: '

                            || L_PERSONAL_PAYMENT_METHOD_ID

                            || '- EMP NO: '

                            || LC_C_EMP_PM.EMPLOYEE_NUMBER

                           );

*/

    /*  L_PERSONAL_PAYMENT_METHOD_ID := '';

      L_ORG_PAYMENT_METHOD_ID := '';

      L_EXTERNAL_ACCOUNT_ID := '';

      L_OBJECT_VERSION_NUMBER := '';

      L_EFFECTIVE_START_DATE := '';

      L_EFFECTIVE_END_DATE := '';

      L_COMMENT_ID := '';

      ERR_DESC := '';

      LV_PM_FLAG := '';

      L_EMP_ASSG_ID := '';

      L_PM_ID := '';

      AS_EFFECTIVE_START_DATE := '';*/

      L_PERSONAL_PAYMENT_METHOD_ID := NULL;

      L_ORG_PAYMENT_METHOD_ID := NULL;

      L_EXTERNAL_ACCOUNT_ID := NULL;

      L_OBJECT_VERSION_NUMBER := NULL;

      L_EFFECTIVE_START_DATE := NULL;

      L_EFFECTIVE_END_DATE := NULL;

      L_COMMENT_ID := NULL;

      ERR_DESC := NULL;

      LV_TERRITORY_CODE := NULL;

      LV_PM_FLAG := NULL;

      L_EMP_ASSG_ID := NULL;

      L_PM_ID := NULL;

      AS_EFFECTIVE_START_DATE := NULL;

      L_PAYMENT_TYPE_ID := NULL;

--COMMIT;

   END LOOP;

   CLOSE C_EMP_PM;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 250));

END;

/

HR_PERSONAL_PAY_METHOD_API Errors: ORA-20001: FLEX-DSQL EXCEPTION

If you come across this issue, this Note ID 974396.1 will help you resolve the issue.

if you could not understand anything, you may ask in the comment section.