Special information Typr in oracle HRMS | Upload Data
Table Definition:
CREATE TABLE DEV_SIT
(
LINE_ID NUMBER,
EMPLOYEE_NUM VARCHAR2(240 BYTE),
SIT_NAME VARCHAR2(240 BYTE),
EFFECTIVE_DATE DATE,
DATE_FROM DATE,
DATE_TO DATE,
ATTRIBUTE_CATEGORY VARCHAR2(240 BYTE),
ATTRIBUTE1 VARCHAR2(240 BYTE),
ATTRIBUTE2 VARCHAR2(240 BYTE),
SEGMENT2 VARCHAR2(240 BYTE),
SEGMENT3 VARCHAR2(240 BYTE),
CONCAT_SEGMENTS VARCHAR2(240 BYTE),
ERROR_DESCRIPTION VARCHAR2(240 BYTE),
P_ANALYSIS_CRITERIA_ID NUMBER,
P_PERSON_ANALYSIS_ID NUMBER,
P_PEA_OBJECT_VERSION_NUMBER NUMBER
)
Procedure:
Note: You can increase number of segments and DFF segments. You need to update procedure according to your requirement.
CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_SIT
IS
CURSOR C_SIT
IS
SELECT LINE_ID, EMPLOYEE_NUM, SIT_NAME, EFFECTIVE_DATE, DATE_FROM,
DATE_TO, ATTRIBUTE_CATEGORY ,ATTRIBUTE1, ATTRIBUTE2, SEGMENT1,SEGMENT2, SEGMENT3,
CONCAT_SEGMENTS
FROM DEV_SIT;
LC_C_SIT C_SIT%ROWTYPE;
L_ID_FLEX_NUM NUMBER;
L_PERSON_ID NUMBER;
L_NAME VARCHAR2 (240);
ERROR_DESC VARCHAR2 (240);
LV_SIT_FLAG CHAR (1);
L_OBJECT_VERSION_NUMBER NUMBER;
L_ANALYSIS_CRITERIA_ID NUMBER;
L_PERSON_ANALYSIS_ID NUMBER;
L_PEA_OBJECT_VERSION_NUMBER NUMBER;
BEGIN
OPEN C_SIT;
LOOP
FETCH C_SIT
INTO LC_C_SIT;
EXIT WHEN C_SIT%NOTFOUND;
---------------------------
-- SIT NAME
---------------------------
SELECT FI.ID_FLEX_NUM
INTO L_ID_FLEX_NUM
FROM FND_ID_FLEX_STRUCTURES_VL FI
WHERE (UPPER (FI.ID_FLEX_STRUCTURE_CODE) = UPPER (LC_C_SIT.SIT_NAME))
AND (APPLICATION_ID = 800)
AND (ID_FLEX_CODE = 'PEA');
---------------------------
-- PERSON ID
---------------------------
SELECT PPF.PERSON_ID
INTO L_PERSON_ID
FROM PER_PEOPLE_F PPF
WHERE SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE ANDPPF.EFFECTIVE_END_DATE
AND PPF.EMPLOYEE_NUMBER = LC_C_SIT.EMPLOYEE_NUM;
BEGIN
HR_SIT_API.CREATE_SIT
(P_VALIDATE => FALSE,
P_PERSON_ID => L_PERSON_ID,
P_BUSINESS_GROUP_ID => 2217,--FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'),
P_ID_FLEX_NUM => L_ID_FLEX_NUM,
P_DATE_FROM => LC_C_SIT.DATE_FROM,
P_DATE_TO => LC_C_SIT.DATE_TO,
P_EFFECTIVE_DATE => LC_C_SIT.EFFECTIVE_DATE,
P_SEGMENT1 => LC_C_SIT.SEGMENT1,
P_SEGMENT2 => LC_C_SIT.SEGMENT2,
P_SEGMENT3 => LC_C_SIT.SEGMENT3,
--,P_CONCAT_SEGMENTS =>
P_ATTRIBUTE_CATEGORY =>LC_C_SIT.ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => LC_C_SIT.ATTRIBUTE1,
P_ATTRIBUTE2 =>LC_C_SIT.ATTRIBUTE2,
P_ANALYSIS_CRITERIA_ID => L_ANALYSIS_CRITERIA_ID,
P_PERSON_ANALYSIS_ID => L_PERSON_ANALYSIS_ID,
P_PEA_OBJECT_VERSION_NUMBER =>L_PEA_OBJECT_VERSION_NUMBER
);
IF L_PERSON_ANALYSIS_ID IS NOT NULL
THEN
LV_SIT_FLAG := 'Y';
ERROR_DESC := 'NO ERROR';
END IF;
IF LV_SIT_FLAG = 'Y'
THEN
UPDATE DEV_SIT
SET ERROR_DESCRIPTION = ERROR_DESC,
P_ANALYSIS_CRITERIA_ID = L_ANALYSIS_CRITERIA_ID,
P_PEA_OBJECT_VERSION_NUMBER = L_PEA_OBJECT_VERSION_NUMBER,
P_PERSON_ANALYSIS_ID = L_PERSON_ANALYSIS_ID
WHERE LINE_ID = LC_C_SIT.LINE_ID;
ELSE
ERROR_DESC := ERROR_DESC || SQLERRM;
UPDATE DEV_SIT
SET ERROR_DESCRIPTION = ERROR_DESC
WHERE LINE_ID = LC_C_SIT.LINE_ID;
END IF;
EXCEPTION
WHEN OTHERS
THEN
LV_SIT_FLAG := 'N';
ERROR_DESC := ERROR_DESC || SQLERRM;
UPDATE DEV_SIT
SET ERROR_DESCRIPTION = ERROR_DESC
WHERE LINE_ID = LC_C_SIT.LINE_ID;
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
L_OBJECT_VERSION_NUMBER := '';
ERROR_DESC := '';
LV_SIT_FLAG := '';
L_OBJECT_VERSION_NUMBER := '';
L_ANALYSIS_CRITERIA_ID := '';
L_PERSON_ANALYSIS_ID := '';
L_PEA_OBJECT_VERSION_NUMBER := '';
-- COMMIT;
END LOOP;
CLOSE C_SIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/
0 comments