My Oracle World: 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

CURSOR C_EMP_PM

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.

My Oracle World

Employee Payment Methods in Oracle Payroll

2 comments:

Popular Posts

Total Pageviews

Blog Archive