How to Create Element Entry Using API : PAY_ELEMENT_ENTRY_API.CREATE_ELEMENT_ENTRY
Create Interface Table :
CREATE TABLE GTM_PAY_CREATE_ELEMENTS_ENTRY
(
ROW_ID NUMBER,
ASSIGNMENT_ID NUMBER,
ELEMENT_NAME VARCHAR2(100 BYTE),
INPUT_NAME1 VARCHAR2(50 BYTE),
INPUT_VAL1 VARCHAR2(50 BYTE),
INPUT_NAME2 VARCHAR2(50 BYTE),
INPUT_VAL2 VARCHAR2(50 BYTE),
INPUT_NAME3 VARCHAR2(50 BYTE),
INPUT_VAL3 VARCHAR2(50 BYTE),
ERROR_DESC VARCHAR2(2000 BYTE),
PROCESS_FLAG CHAR(1 BYTE),
EFFECTIVE_DATE DATE,
DATE_EARNED DATE,
MULTIPLE_ALLOW CHAR(1 BYTE)
)
Insert Data Like This :
Create Procedure Like This:
CREATE OR REPLACE PROCEDURE APPS.GTM_CREATE_ELEMENT_ENTRY
IS
cursor c_element is
SELECT
aa.ERROR_DESC,
aa.ROW_ID,
aa.ASSIGNMENT_ID,
aa.EFFECTIVE_DATE EFFECTIVE_DATE,
aa.DATE_EARNED,
aa.ELEMENT_NAME,
aa.INPUT_NAME1,
aa.INPUT_VAL1,
aa.INPUT_NAME2,
aa.INPUT_VAL2,
aa.INPUT_NAME3,
aa.INPUT_VAL3,
aa.MULTIPLE_ALLOW,
PELF.ELEMENT_LINK_ID,
PETF.ELEMENT_TYPE_ID
FROM
PAY_ELEMENT_LINKS_F PELF
,PAY_ELEMENT_TYPES_F PETF
,PER_ALL_ASSIGNMENTS_F PAAF
,PER_ALL_PEOPLE_F PAPF
,GTM_PAY_CREATE_ELEMENTS_ENTRY aa
WHERE
PELF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND trunc(sysdate) BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND trunc(sysdate) BETWEEN PELF.EFFECTIVE_START_DATE AND PELF.EFFECTIVE_END_DATE
AND PAAF.ORGANIZATION_ID = NVL(PELF.ORGANIZATION_ID,PAAF.ORGANIZATION_ID)
AND PAAF.POSITION_ID = NVL(PELF.POSITION_ID,PAAF.POSITION_ID)
AND PAAF.LOCATION_ID = NVL(PELF.LOCATION_ID,PAAF.LOCATION_ID)
AND PAAF.PAYROLL_ID = NVL(PELF.PAYROLL_ID,PAAF.PAYROLL_ID)
and nvl(paaf.EMPLOYMENT_CATEGORY,'x') = nvl(pelf.EMPLOYMENT_CATEGORY,'x')
AND PAAF.PRIMARY_FLAG = 'Y'
AND trunc(sysdate) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PAAF.PERSON_ID = PAPF.PERSON_ID
AND trunc(sysdate) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
and petf.ELEMENT_NAME = aa.ELEMENT_NAME
and paaf.ASSIGNMENT_ID = aa.ASSIGNMENT_ID
and nvl(aa.PROCESS_FLAG,'N') = 'N'
order by aa.ROW_ID;
V_EFFECTIVE_START_DATE DATE;
V_EFFECTIVE_END_DATE DATE;
V_ELEMENT_ENTRY_ID NUMBER;
V_OBJECT_VERSION_NUMBER NUMBER;
V_CREATE_WARNING BOOLEAN;
V_ELEMENT_TYPE_ID NUMBER;
V_ELEMENT_LINK_ID NUMBER;
V_INPUT_VALUE_ID1 NUMBER;
V_INPUT_VALUE_ID2 NUMBER;
V_INPUT_VALUE_ID3 NUMBER;
V_COST_ALLOCATION_KEYFLEX_ID NUMBER;
FLAG CHAR(1) := 'Y';
V_ERROR VARCHAR2(2000);
REC C_ELEMENT%ROWTYPE;
BEGIN
OPEN C_ELEMENT;
LOOP
FETCH C_ELEMENT INTO REC;
EXIT WHEN C_ELEMENT%NOTFOUND;
IF REC.INPUT_NAME1 IS NOT NULL THEN
BEGIN
SELECT PIVF.INPUT_VALUE_ID
INTO V_INPUT_VALUE_ID1
FROM PAY_INPUT_VALUES_F PIVF
WHERE (rec.effective_date) BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE
AND PIVF.ELEMENT_TYPE_ID = rec.ELEMENT_TYPE_ID
AND UPPER(PIVF.NAME) = UPPER(REC.INPUT_NAME1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
FLAG := 'N';
V_ERROR := V_ERROR|| ' Input value '||REC.INPUT_NAME1||' not found';
WHEN TOO_MANY_ROWS THEN
FLAG := 'N';
WHEN OTHERS THEN
FLAG := 'N';
END;
end if;
IF REC.INPUT_NAME2 IS NOT NULL THEN
BEGIN
SELECT PIVF.INPUT_VALUE_ID
INTO V_INPUT_VALUE_ID2
FROM PAY_INPUT_VALUES_F PIVF
WHERE (rec.effective_date) BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE
AND PIVF.ELEMENT_TYPE_ID = rec.ELEMENT_TYPE_ID
AND UPPER(PIVF.NAME) = UPPER(REC.INPUT_NAME2);
EXCEPTION
WHEN NO_DATA_FOUND THEN
FLAG := 'N';
V_ERROR := V_ERROR|| ' Input value '||REC.INPUT_NAME2||' not found';
WHEN TOO_MANY_ROWS THEN
FLAG := 'N';
WHEN OTHERS THEN
FLAG := 'N';
END;
END IF;
IF REC.INPUT_NAME3 IS NOT NULL THEN
BEGIN
SELECT PIVF.INPUT_VALUE_ID
INTO V_INPUT_VALUE_ID3
FROM PAY_INPUT_VALUES_F PIVF
WHERE (rec.effective_date) BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE
AND PIVF.ELEMENT_TYPE_ID = rec.ELEMENT_TYPE_ID
AND UPPER(PIVF.NAME) = UPPER(REC.INPUT_NAME3);
EXCEPTION
WHEN NO_DATA_FOUND THEN
FLAG := 'N';
V_ERROR := V_ERROR|| ' Input value '||REC.INPUT_NAME3||' not found';
WHEN TOO_MANY_ROWS THEN
FLAG := 'N';
WHEN OTHERS THEN
FLAG := 'N';
END;
END IF;
IF NVL(REC.MULTIPLE_ALLOW,'N') = 'N' THEN
BEGIN
SELECT 'N'
INTO FLAG
FROM PAY_ELEMENT_ENTRIES_F AA
WHERE AA.ELEMENT_LINK_ID = REC.ELEMENT_LINK_ID
AND AA.ELEMENT_TYPE_ID = REC.ELEMENT_TYPE_ID
AND (REC.EFFECTIVE_DATE) BETWEEN AA.EFFECTIVE_START_DATE AND AA.EFFECTIVE_END_DATE
AND AA.ASSIGNMENT_ID = REC.ASSIGNMENT_ID;
V_ERROR := V_ERROR||' ENTRY ALREADY EXIST!';
EXCEPTION
WHEN NO_DATA_FOUND THEN
FLAG := 'Y';
WHEN TOO_MANY_ROWS THEN
FLAG := 'N';
WHEN OTHERS THEN
FLAG := 'N';
END;
END IF;
BEGIN ----------------------------------------------------
dbms_output.PUT_LINE('01'||FLAG);
IF FLAG = 'Y' THEN
PAY_ELEMENT_ENTRY_API.CREATE_ELEMENT_ENTRY
(
p_validate => false
,p_effective_date => rec.effective_date
,p_business_group_id => 0
,p_assignment_id => rec.assignment_id
,p_element_link_id => rec.element_link_id
,p_entry_type => 'E'
-- ,p_cost_allocation_keyflex_id => v_cost_allocation_keyflex_id
,p_date_earned => rec.date_earned
-----------------------------
,p_input_value_id1 => v_input_value_id1
,p_input_value_id2 => v_input_value_id2
,p_input_value_id3 => v_input_value_id3
,p_entry_value1 => rec.input_val1
,p_entry_value2 => rec.input_val2
,p_entry_value3 => rec.input_val3
-----------------------------
,p_effective_start_date => v_effective_start_date
,p_effective_end_date => v_effective_end_date
,p_element_entry_id => v_element_entry_id
,p_object_version_number => v_object_version_number
,p_create_warning => v_create_warning
);
DBMS_OUTPUT.PUT_LINE('Obj Ver No: '||V_OBJECT_VERSION_NUMBER);
DBMS_OUTPUT.PUT_LINE('Element Entry ID: '||V_ELEMENT_ENTRY_ID);
IF V_ELEMENT_ENTRY_ID IS NOT NULL THEN
UPDATE GTM_PAY_CREATE_ELEMENTS_ENTRY AA
SET AA.PROCESS_FLAG = 'Y',
AA.ERROR_DESC = 'No Error'
WHERE AA.ROW_ID = REC.ROW_ID;
ELSE
V_ERROR := V_ERROR||' - '||sqlerrm;
UPDATE GTM_PAY_CREATE_ELEMENTS_ENTRY AA
SET AA.PROCESS_FLAG = 'Y',
AA.ERROR_DESC = 'Error! - '||v_error
WHERE AA.ROW_ID = REC.ROW_ID;
END IF;
ELSE
V_ERROR := V_ERROR||' - '||sqlerrm;
UPDATE GTM_PAY_CREATE_ELEMENTS_ENTRY AA
SET AA.PROCESS_FLAG = 'Y',
AA.ERROR_DESC = 'Error! - '||v_error
WHERE AA.ROW_ID = REC.ROW_ID;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FLAG := 'Y';
WHEN TOO_MANY_ROWS THEN
FLAG := 'N';
WHEN OTHERS THEN
FLAG := 'N';
V_ERROR := V_ERROR||' - '||sqlerrm;
UPDATE GTM_PAY_CREATE_ELEMENTS_ENTRY AA
SET AA.PROCESS_FLAG = 'Y',
AA.ERROR_DESC = 'Error! - '||v_error
WHERE AA.ROW_ID = REC.ROW_ID;
END; ----------------------------------------------------
flag:= 'Y';
v_error:=null;
commit;
end loop;
close c_element;
END;
No comments:
Post a Comment
Thanks for making a comment.