Element Link Creation Procedure in Oracle Payroll
How to find correct API for Data Uploading
Please note that number of segments of General Ledger can be different for each company, i have provided 8 segments(Costing, Balancing) for now, kindly change table definition and procedure accordingly.
Please note that number of segments of General Ledger can be different for each company, i have provided 8 segments(Costing, Balancing) for now, kindly change table definition and procedure accordingly.
Kindly create control file using table structure.
How to create Control file for Sql Loader
1) TABLE
CREATE TABLE DEV_ELEMENTS_LINK
(
LINE_ID NUMBER,
EFFECTIVE_DATE DATE,
ELEMENT_TYPE_ID NUMBER,
ELEMENT_TYPE_NAME VARCHAR2(240 BYTE),
DESCRIPTION VARCHAR2(240 BYTE),
QUALIFYING_LENGTH_OF_SERVICE VARCHAR2(240 BYTE),
QUALIFYING_UNITS VARCHAR2(240 BYTE),
STANDARD_LINK_FLAG VARCHAR2(240 BYTE),
ORG_NAME VARCHAR2(240 BYTE),
PEOPLE_GROUP_NAME VARCHAR2(240 BYTE),
SALARY_BASES VARCHAR2(240 BYTE),
LOCATION_NAME VARCHAR2(240 BYTE),
PAYROLL_ID NUMBER,
PAYROLL_NAME VARCHAR2(240 BYTE),
GRADE_ID NUMBER,
GRADE_NAME VARCHAR2(240 BYTE),
EMPLOYMENT_CATEGORY VARCHAR2(240 BYTE),
COSTABLE_TYPE VARCHAR2(240 BYTE),
TRANSFER_GL_FLAG VARCHAR2(240 BYTE),
COSTING_ALLOC_FLEXFIELD_ID NUMBER,
ELEMENT_LINK_ID_OUT NUMBER,
COMMENT_ID NUMBER,
OBJ_VER_NUMBER NUMBER,
EFFECTIVE_START_DATE DATE,
EFFECTIVE_END_DATE DATE,
P_COST_SEGMENT1 VARCHAR2(240 BYTE),
P_COST_SEGMENT2 VARCHAR2(240 BYTE),
P_COST_SEGMENT3 VARCHAR2(240 BYTE),
P_COST_SEGMENT4 VARCHAR2(240 BYTE),
P_COST_SEGMENT5 VARCHAR2(240 BYTE),
P_COST_SEGMENT6 VARCHAR2(240 BYTE),
P_COST_SEGMENT7 VARCHAR2(240 BYTE),
P_COST_SEGMENT8 VARCHAR2(240 BYTE),
COST_CONCAT_SEGMENT VARCHAR2(240 BYTE),
BALANCE_FLEXFIELD_ID NUMBER,
P_BAL_SEGMENT1 VARCHAR2(240 BYTE),
P_BAL_SEGMENT2 VARCHAR2(240 BYTE),
P_BAL_SEGMENT3 VARCHAR2(240 BYTE),
P_BAL_SEGMENT4 VARCHAR2(240 BYTE),
P_BAL_SEGMENT5 VARCHAR2(240 BYTE),
P_BAL_SEGMENT6 VARCHAR2(240 BYTE),
P_BAL_SEGMENT7 VARCHAR2(240 BYTE),
P_BAL_SEGMENT8 VARCHAR2(240 BYTE),
BALANCE_CONCAT_SEGMENT VARCHAR2(240 BYTE),
BUSINESS_GROUP_ID NUMBER,
ERROR_DESCRIPTION VARCHAR2(240 BYTE),
PROCESS_FLAG VARCHAR2(240 BYTE)
)
2) PROCEDURE
CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_ELEMENTS_LINK
IS
CURSOR C_COMP
IS
SELECT DISTINCT LINE_ID, EFFECTIVE_DATE, ELEMENT_TYPE_ID, ELEMENT_TYPE_NAME,
DESCRIPTION, QUALIFYING_LENGTH_OF_SERVICE, STANDARD_LINK_FLAG,
PAYROLL_ID, PAYROLL_NAME, GRADE_ID, GRADE_NAME,
EMPLOYMENT_CATEGORY, COSTABLE_TYPE, TRANSFER_GL_FLAG,
COSTING_ALLOC_FLEXFIELD_ID,
ELEMENT_LINK_ID_OUT, COMMENT_ID,
OBJ_VER_NUMBER, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE,
PROCESS_FLAG, ERROR_DESCRIPTION, BALANCE_FLEXFIELD_ID,
P_COST_SEGMENT1, P_COST_SEGMENT2, P_COST_SEGMENT3,
P_COST_SEGMENT4, P_COST_SEGMENT5,P_COST_SEGMENT6,P_COST_SEGMENT7,
P_COST_SEGMENT8, P_BAL_SEGMENT1,P_BAL_SEGMENT2, P_BAL_SEGMENT3,
P_BAL_SEGMENT4,P_BAL_SEGMENT5,P_BAL_SEGMENT6, P_BAL_SEGMENT7, P_BAL_SEGMENT8,
QUALIFYING_UNITS, BUSINESS_GROUP_ID,
COST_CONCAT_SEGMENT,
BALANCE_CONCAT_SEGMENT, ORG_NAME,
LOCATION_NAME, PEOPLE_GROUP_NAME, SALARY_BASES
FROM DEV_ELEMENTS_LINK
ORDER BY LINE_ID;
LC_C_COMP C_COMP%ROWTYPE;
L_COMP_ID NUMBER;
L_OBJECT_VERSION_NUMBER NUMBER;
ERROR_DESC VARCHAR2 (2000);
LV_COMP_FLAG CHAR (1);
L_ELEMENT_TYPE_ID NUMBER;
L_PAYROLL_ID NUMBER;
L_GRADE_ID NUMBER;
L_COST_KEYFLEX NUMBER;
L_LEVEL_HIGH_ID NUMBER;
L_COMPT_ID NUMBER;
L_JOB_ID NUMBER;
L_ELEMENT_LINK_ID NUMBER;
L_COMMENT_ID NUMBER;
L_COUNT NUMBER;
L_EFFECTIVE_START_DATE DATE;
L_EFFECTIVE_END_DATE DATE;
L_ORG_ID NUMBER;
L_LOC_ID NUMBER;
L_BASIS_ID NUMBER;
L_PPL_GROUP_ID NUMBER;
L_EMP_CAT VARCHAR2 (2000);
BEGIN
OPEN C_COMP;
LOOP
FETCH C_COMP
INTO LC_C_COMP;
--EXIT WHEN C_COMP%NOTFOUND;
---------------------------QUERY
FOR ELEMENT_TYPE_ID------------------
IF LC_C_COMP.ELEMENT_TYPE_NAME IS NOT NULL
THEN
BEGIN
SELECT PETF.ELEMENT_TYPE_ID
INTO L_ELEMENT_TYPE_ID
FROM PAY_ELEMENT_TYPES_F PETF
WHERE UPPER(PETF.ELEMENT_NAME)= UPPER(LC_C_COMP.ELEMENT_TYPE_NAME)
ORDER BY PETF.ELEMENT_NAME;
EXCEPTION
WHEN OTHERS
THEN
ERROR_DESC :=
ERROR_DESC
|| 'ELEMENT TYPE ERROR'
|| SUBSTR (SQLERRM, 1, 100);
L_ELEMENT_TYPE_ID := NULL;
END;
END IF;
--------------------------QUERY
FOR PAYROLL_ID--------------------------
IF LC_C_COMP.PAYROLL_NAME IS NOT NULL
THEN
BEGIN
SELECT PAYROLL_ID
INTO L_PAYROLL_ID
FROM PAY_PAYROLLS_F
WHERE UPPER(PAYROLL_NAME) = UPPER(LC_C_COMP.PAYROLL_NAME)
ORDER BY PAYROLL_NAME;
EXCEPTION
WHEN OTHERS
THEN
ERROR_DESC :=
ERROR_DESC || 'PAYROLLNAME
ERROR'
|| SUBSTR (SQLERRM, 1, 100);
L_PAYROLL_ID := NULL;
END;
END IF;
----------------------------QUERY
FOR GRADE_ID---------------------------
IF LC_C_COMP.GRADE_NAME IS NOT NULL
THEN
BEGIN
SELECT PG.GRADE_ID
INTO L_GRADE_ID
FROM PER_GRADES PG
WHERE UPPER(PG.NAME) = UPPER(LC_C_COMP.GRADE_NAME)
ORDER BY PG.NAME;
EXCEPTION
WHEN OTHERS
THEN
ERROR_DESC :=
ERROR_DESC || 'GRADENAME
ERROR' || SUBSTR (SQLERRM, 1, 100);
L_GRADE_ID := '';
END;
END IF;
BEGIN
SELECT LOOKUP_CODE
INTO L_EMP_CAT
FROM
FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'EMP_CAT'
AND UPPER (MEANING) = UPPER (TRIM (LC_C_COMP.EMPLOYMENT_CATEGORY));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
ERROR_DESC := ERROR_DESC || 'EMP CATEGORY LOOKUP ERROR';
END;
----------------------------ORGANIZATION_NAME---------------------
IF LC_C_COMP.ORG_NAME IS NOT NULL
THEN
BEGIN
SELECT ORGANIZATION_ID, LOCATION_ID
INTO L_ORG_ID, L_LOC_ID
FROM HR_ALL_ORGANIZATION_UNITS
WHERE UPPER(NAME) LIKE UPPER(LC_C_COMP.ORG_NAME);
EXCEPTION
WHEN OTHERS
THEN
ERROR_DESC :=
ERROR_DESC || 'ORGNAME
ERROR' || SUBSTR (SQLERRM, 1, 100);
L_ORG_ID := '';
END;
END IF;
------------------------SALARY
BASIS------------------------------------
IF LC_C_COMP.SALARY_BASES IS NOT NULL
THEN
BEGIN
SELECT PAY_BASIS_ID
INTO L_BASIS_ID
FROM PER_PAY_BASES
WHERE UPPER(NAME) LIKE UPPER(LC_C_COMP.SALARY_BASES);
EXCEPTION
WHEN OTHERS
THEN
ERROR_DESC :=
ERROR_DESC || 'SALARY_BASIS_NAME
ERROR' || SUBSTR (SQLERRM, 1, 100);
L_BASIS_ID := '';
END;
END IF;
-----------------------PEOPLE
GROUP NAME---------------------------------
IF LC_C_COMP.PEOPLE_GROUP_NAME IS NOT NULL
THEN
BEGIN
SELECT PEOPLE_GROUP_ID
INTO L_PPL_GROUP_ID
FROM PAY_PEOPLE_GROUPS
WHERE UPPER(GROUP_NAME) LIKE UPPER(LC_C_COMP.PEOPLE_GROUP_NAME);
EXCEPTION
WHEN
OTHERS
THEN
ERROR_DESC :=
ERROR_DESC || 'PEOPLE_GROUP_NAME
ERROR' || SUBSTR (SQLERRM, 1, 100);
L_PPL_GROUP_ID := '';
END;
END IF;
--L_COUNT := 0;
-- IF L_COUNT = 0 THEN
BEGIN
PAY_ELEMENT_LINK_API.CREATE_ELEMENT_LINK
(P_VALIDATE => FALSE,
P_EFFECTIVE_DATE
=> LC_C_COMP.EFFECTIVE_DATE,
P_ELEMENT_TYPE_ID
=> L_ELEMENT_TYPE_ID,
P_BUSINESS_GROUP_ID
=> 0,
P_COSTABLE_TYPE
=> LC_C_COMP.COSTABLE_TYPE,
P_PAYROLL_ID
=> L_PAYROLL_ID,
P_GRADE_ID
=> L_GRADE_ID,
P_ORGANIZATION_ID
=> L_ORG_ID,
P_PEOPLE_GROUP_ID
=> L_PPL_GROUP_ID,
P_PAY_BASIS_ID
=> L_BASIS_ID,
P_LOCATION_ID
=> L_LOC_ID,
P_STANDARD_LINK_FLAG
=> LC_C_COMP.STANDARD_LINK_FLAG,
P_TRANSFER_TO_GL_FLAG
=> LC_C_COMP.TRANSFER_GL_FLAG
,
P_EMPLOYMENT_CATEGORY
=> L_EMP_CAT,--LC_C_COMP.EMPLOYMENT_CATEGORY,
P_QUALIFYING_LENGTH_OF_SERVICE
=> LC_C_COMP.QUALIFYING_LENGTH_OF_SERVICE,
P_QUALIFYING_UNITS
=> LC_C_COMP.QUALIFYING_UNITS,
P_COST_SEGMENT1
=> LC_C_COMP.P_COST_SEGMENT1,
P_COST_SEGMENT2
=> LC_C_COMP.P_COST_SEGMENT2,
P_COST_SEGMENT3
=> LC_C_COMP.P_COST_SEGMENT3,
P_COST_SEGMENT4
=> LC_C_COMP.P_COST_SEGMENT4,
P_COST_SEGMENT5
=> LC_C_COMP.P_COST_SEGMENT5,
P_COST_SEGMENT6
=> LC_C_COMP.P_COST_SEGMENT6,
P_COST_SEGMENT7
=> LC_C_COMP.P_COST_SEGMENT7,
P_COST_SEGMENT8
=> LC_C_COMP.P_COST_SEGMENT8,
P_BALANCE_SEGMENT1
=> LC_C_COMP.P_BAL_SEGMENT1,
P_BALANCE_SEGMENT2
=> LC_C_COMP.P_BAL_SEGMENT2,
P_BALANCE_SEGMENT3
=> LC_C_COMP.P_BAL_SEGMENT3,
P_BALANCE_SEGMENT4
=> LC_C_COMP.P_BAL_SEGMENT4,
P_BALANCE_SEGMENT5 => LC_C_COMP.P_BAL_SEGMENT5,
P_BALANCE_SEGMENT6
=> LC_C_COMP.P_BAL_SEGMENT6,
P_BALANCE_SEGMENT7
=> LC_C_COMP.P_BAL_SEGMENT7,
P_BALANCE_SEGMENT8 => LC_C_COMP.P_BAL_SEGMENT8,
P_COST_CONCAT_SEGMENTS
=> LC_C_COMP.COST_CONCAT_SEGMENT,
P_BALANCE_CONCAT_SEGMENTS
=> LC_C_COMP.BALANCE_CONCAT_SEGMENT,
P_ELEMENT_LINK_ID => L_ELEMENT_LINK_ID,
P_COMMENT_ID
=> L_COMMENT_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
);
DBMS_OUTPUT.PUT_LINE('UPLOADED');
IF L_ELEMENT_LINK_ID IS NOT NULL
THEN
LV_COMP_FLAG := 'Y';
ERROR_DESC := 'NO ERROR';
END IF;
IF LV_COMP_FLAG = 'Y'
THEN
UPDATE DEV_ELEMENTS_LINK
SET ERROR_DESCRIPTION = SUBSTR (ERROR_DESC, 1, 200),
--PROCESS_FLAG =
LV_COMP_FLAG,
--ELEMENT_LINK_ID_OUT
= L_ELEMENT_LINK_ID,
--COMMENT_ID =
L_COMMENT_ID,
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_COMP.LINE_ID;
ELSE
LV_COMP_FLAG := 'N';
ERROR_DESC := SUBSTR (SQLERRM, 1, 254);
UPDATE DEV_ELEMENTS_LINK
SET ERROR_DESCRIPTION = SUBSTR (ERROR_DESC, 1, 200),
PROCESS_FLAG = LV_COMP_FLAG
WHERE LINE_ID =
LC_C_COMP.LINE_ID;
END IF;
EXCEPTION
WHEN OTHERS
THEN
LV_COMP_FLAG := 'N';
ERROR_DESC := SUBSTR (SQLERRM, 1, 254);
UPDATE DEV_ELEMENTS_LINK
SET ERROR_DESCRIPTION = SUBSTR (ERROR_DESC, 1, 200),
PROCESS_FLAG = LV_COMP_FLAG
WHERE LINE_ID =
LC_C_COMP.LINE_ID;
END;
L_COMP_ID := 0;
L_LEVEL_HIGH_ID := 0;
L_COMPT_ID := 0;
L_JOB_ID
:= 0;
L_COST_KEYFLEX := 0;
L_COUNT := 0;
LV_COMP_FLAG := NULL;
ERROR_DESC := NULL;
L_ELEMENT_TYPE_ID := NULL;
L_PAYROLL_ID := NULL;
L_GRADE_ID := NULL;
L_ELEMENT_LINK_ID := NULL;
L_COMMENT_ID := NULL;
L_OBJECT_VERSION_NUMBER := NULL;
L_EFFECTIVE_START_DATE := NULL;
L_EFFECTIVE_END_DATE := NULL;
L_ORG_ID
:= NULL;
L_LOC_ID := NULL;
L_BASIS_ID := NULL;
L_PPL_GROUP_ID := NULL;
L_JOB_ID
:= NULL;
END LOOP;
CLOSE C_COMP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE
(SUBSTR (SQLERRM, 1, 254));
END;
/
0 comments: