CREATE OR REPLACE
PACKAGE BODY xxarj_pay_element_entry_pkg IS
-------------------------------------------------------------------------------
-- CREATE ELEMENT ENTRY
-------------------------------------------------------------------------------
L_Error_Flag VARCHAR2(1) := 'N';
L_Error_Message VARCHAR2(4000);
L_Employee_Number VARCHAR2(10);
L_rec_count NUMBER;
l_session_id NUMBER;
l_fnd_session_id NUMBER;
-- Main Procedure called from Concurrent Program.
PROCEDURE CREATE_ELEMENT_ENTRY_MAIN(
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
pBatchName IN VARCHAR2
) AS
-- Main Cursor to Select all Unprocessed and Error Records.
CURSOR csr_get_element_info IS
SELECT xpcet.rowid
, xpcet.*
FROM XXARJ_PAY_CON_ELEMENT_TEMP xpcet
WHERE PROCESS_FLAG IN ('U','E')
-- and employee_number in('SCLP49')
AND BATCH_NAME = NVL(pBatchName,BATCH_NAME);
l_assignment_id VARCHAR2(240);
L_Element_Entry_Id pay_element_entries_f.element_entry_id%TYPE;
l_total_records NUMBER;
l_success_records NUMBER;
l_failure_records NUMBER;
L_payroll_id NUMBER;
l_subsidy_reason VARCHAR2(100);
l_disabality_percentage VARCHAR2(100);
l_disability_category VARCHAR2(100);
l_mediclaim_input_value1 VARCHAR2(25);
l_mediclaim_entry_value1 NUMBER;
l_skip_0_val_validation VARCHAR2(1);
l_mult_entries_allowed_flag VARCHAR2(1);
BEGIN
-- get session id from user environment....
BEGIN
SELECT USERENV( 'SESSIONID' )
INTO l_session_id
FROM DUAL;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_session_id '||l_session_id);
INSERT INTO fnd_sessions(SESSION_ID,EFFECTIVE_DATE) VALUES(l_session_id,SYSDATE);
COMMIT;
EXCEPTION WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception while getting sessionid '||sqlerrm);
END;
BEGIN
SELECT SESSION_ID
INTO l_fnd_session_id
FROM fnd_sessions
WHERE SESSION_ID = l_session_id;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_fnd_session_id '||l_fnd_session_id);
EXCEPTION WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception while getting l_fnd_session_id '||sqlerrm);
END;
-- Populate Request ID to all records of temp table.
UPDATE XXARJ_PAY_CON_ELEMENT_TEMP
SET REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
,business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
WHERE process_flag <> 'P';
-- Find Total No. of Elegible Records...
SELECT COUNT(*)
INTO l_total_records
FROM XXARJ_PAY_CON_ELEMENT_TEMP
WHERE REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
AND business_group_id=FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND process_flag <>'P'
AND BATCH_NAME = NVL(pBatchName,BATCH_NAME);
-- Main Loop start
FOR r_csr_get_element_info IN csr_get_element_info
L_Error_Message :=NULL; -- Initialize null for each record..
L_Error_Flag := 'N'; -- Initialize N for each record..
L_Employee_Number := NULL;
l_assignment_id := NULL;
l_subsidy_reason := NULL;
l_disabality_percentage := NULL;
l_disability_category := NULL;
l_mediclaim_input_value1 := 'Amount';
l_mediclaim_entry_value1 := NULL;
l_skip_0_val_validation := 'N';
l_mult_entries_allowed_flag := 'N';
-- New Employee Validation.
BEGIN
SELECT EMPLOYEE_NUMBER
INTO L_Employee_Number
FROM PER_ALL_PEOPLE_F PAPF
WHERE ATTRIBUTE1 = r_csr_get_element_info.EMPLOYEE_NUMBER
AND r_csr_get_element_info.EFFECTIVE_START_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND current_employee_flag = 'Y'; -- Added on 08-Sep by Shekhar/Ganga;
EXCEPTION WHEN TOO_MANY_ROWS THEN
L_Error_Flag := 'Y';
L_Error_Message := 'Multiple New Employee No for this employee ';
WHEN OTHERS THEN
L_Error_Flag := 'Y';
L_Error_Message := 'New Employee Number Not found.';
END;
-- L_Employee_Number :=r_csr_get_element_info.NEW_EMPLOYEE_NUMBER;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'L_Employee_Number : '||L_Employee_Number);
-- Lookup code validation... for Description input of Subsidy Allowance element on 10-Sep-2010 by Ganga/Shekhar
/* IF r_csr_get_element_info.ELEMENT_NAME = 'Subsidy Allowance' THEN
BEGIN
SELECT distinct LOOKUP_CODE
INTO l_subsidy_reason
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'XXARJ_IN_SUBSIDY_REASON'
AND MEANING = r_csr_get_element_info.ENTRY_VALUE_2
AND ENABLED_FLAG = 'Y';
r_csr_get_element_info.ENTRY_VALUE_2 := l_subsidy_reason;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_subsidy_reason : '||l_subsidy_reason);
EXCEPTION WHEN OTHERS THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'Lookup code not found for '||r_csr_get_element_info.ENTRY_VALUE_2;
END;
END IF; */
-- Lookup code validation... for Disability Category input of Deduction under Section 80DD element on 13-Sep-2010 by Ganga/Shekhar
IF r_csr_get_element_info.ELEMENT_NAME = 'Deduction under Section 80DD' THEN
BEGIN
SELECT
FLV.LOOKUP_CODE
INTO l_disability_category
FROM hr_leg_lookups FLV
WHERE flv.lookup_type ='DISABILITY_CATEGORY'
AND flv.enabled_flag='Y'
AND flv.lookup_code not in ('AU','CP','MD','OH')
AND FLV.MEANING = r_csr_get_element_info.ENTRY_VALUE_2;
r_csr_get_element_info.ENTRY_VALUE_2 := l_disability_category;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_disability_category : '||l_disability_category);
EXCEPTION WHEN OTHERS THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'Lookup code not found for '||r_csr_get_element_info.ENTRY_VALUE_2;
END;
END IF;
-- Lookup code validation... for Disability Percentage input of Deduction under Section 80DD element on 13-Sep-2010 by Ganga/Shekhar
IF r_csr_get_element_info.ELEMENT_NAME = 'Deduction under Section 80DD' THEN
BEGIN
SELECT LOOKUP_CODE
INTO l_disabality_percentage
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'IN_DISABLE_PERCENTAGE'
AND MEANING = r_csr_get_element_info.ENTRY_VALUE_3
AND ENABLED_FLAG = 'Y';
r_csr_get_element_info.ENTRY_VALUE_3 := l_disabality_percentage;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_disabality_percentage : '||l_disabality_percentage);
EXCEPTION WHEN OTHERS THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'Lookup code not found for '||r_csr_get_element_info.ENTRY_VALUE_3;
END;
END IF;
-- Assignment Validation..
BEGIN
SELECT assignment_id
INTO l_assignment_id
FROM per_all_assignments_f
WHERE assignment_number = L_Employee_Number
AND business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND primary_flag = 'Y'
AND r_csr_get_element_info.effective_start_Date BETWEEN effective_start_Date AND effective_end_Date;
EXCEPTION WHEN NO_DATA_FOUND THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||' * No Assignment found for this Employee '||L_Employee_Number;
WHEN OTHERS THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||' * Assignemtn Validateion Fail... '||SQLERRM;
END;
--payroll validation...added on 07/09/2010
Begin
select payroll_id
into L_payroll_id
from per_all_assignments_f paaf
where paaf.assignment_id=l_assignment_id
and primary_flag = 'Y'
AND business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND r_csr_get_element_info.effective_start_Date BETWEEN paaf.effective_start_Date AND paaf.effective_end_Date;
IF L_payroll_id IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||' * No payroll is attached for this Employee '||L_Employee_Number;
END IF;
EXCEPTION
WHEN OTHERS THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||' * Payroll Validateion Fail... '||SQLERRM;
END;
-- Element Entry validation for duplicate element entry...
BEGIN
SELECT ee.element_entry_id
, et.multiple_entries_allowed_flag
INTO L_Element_Entry_Id
,l_mult_entries_allowed_flag
FROM pay_element_entries_f ee
,pay_element_types_f et
WHERE ee.assignment_id = l_assignment_id
AND et.element_type_id = ee.element_type_id
AND et.element_name = r_csr_get_element_info.ELEMENT_NAME
AND (et.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
or et.legislation_code='IN')
AND r_csr_get_element_info.effective_start_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND r_csr_get_element_info.effective_start_date BETWEEN et.effective_start_date AND et.effective_end_date;
--AND et.multiple_entries_allowed_flag = 'N' ; -- Added by Ganga Ram
IF l_mult_entries_allowed_flag ='N' THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||' * Element Entry Already Exists.. for this effective date.';
END IF;
EXCEPTION WHEN OTHERS THEN
--FND_FILE.PUT_LINE(FND_FILE.LOG, '');
Dbms_Output.PUT_LINE('Duplicate element entry checking success...');
END;
-- Validation for Zero/Null Entry values.
IF (r_csr_get_element_info.ELEMENT_NAME = 'Superannuation Information'
OR r_csr_get_element_info.BATCH_NAME = 'Attendance Details Corporate'
OR r_csr_get_element_info.BATCH_NAME = 'House Rent Declaration Corporate') THEN
l_skip_0_val_validation := 'Y';
END IF;
BEGIN
IF ((l_skip_0_val_validation <> 'Y' and r_csr_get_element_info.ELEMENT_NAME <> 'Education Allowance'
and r_csr_get_element_info.ELEMENT_NAME <> 'Attendance Details01' and r_csr_get_element_info.ELEMENT_NAME <> 'Attendance Details02'
and r_csr_get_element_info.ELEMENT_NAME <> 'Attendance Details03' and r_csr_get_element_info.ELEMENT_NAME <> 'Other Income' )) THEN
IF (r_csr_get_element_info.INPUT_VALUE_1 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_1,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_1) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_2 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_2,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_2) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_3 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_3,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_3) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_4 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_4,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_4) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_5 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_5,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_5) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_6 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_6,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_6) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_7 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_7,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_7) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_8 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_8,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_8) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_9 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_9,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_9) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_10 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_10,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_10) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_11 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_11,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_11) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_12 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_12,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_12) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_13 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_13,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_13) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_14 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_14,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_14) = TO_CHAR('0.00')))
OR
(r_csr_get_element_info.INPUT_VALUE_15 IS NOT NULL
AND( NVL(r_csr_get_element_info.ENTRY_VALUE_15,'0')='0'
OR TO_CHAR(r_csr_get_element_info.ENTRY_VALUE_15) = TO_CHAR('0.00')))
THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||' * Can not create element entry for 0 or Null entry value..';
END IF;
END IF;
IF (r_csr_get_element_info.ELEMENT_NAME = 'Superannuation Information' AND r_csr_get_element_info.ENTRY_VALUE_1 = 'LIC' AND NVL(r_csr_get_element_info.ENTRY_VALUE_2,'0')='0') THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||' * LIC Should have LICID ';
END IF;
EXCEPTION WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error while validationg zero values element entry...'||SQLERRM);
END;
-- Added on 13-Sep for Deduction under Section 80U element validation...
-- If element entry value is non zero then it should create element entry without any input value...
IF r_csr_get_element_info.ELEMENT_NAME = 'Deduction under Section 80U' THEN
r_csr_get_element_info.input_value_1 := NULL;
r_csr_get_element_info.entry_value_1 := NULL;
END IF;
-- If element is Mediclaim it value should be taken from XXARJ_IN_PLL_MED_INS.
IF r_csr_get_element_info.ELEMENT_NAME = 'Mediclaim' THEN
-- Validation to get Mediclaim amount ...
BEGIN
SELECT pgrf.value
INTO l_mediclaim_entry_value1
FROM per_all_people_f papf,
per_all_assignments_f paaf,
PAY_RATES_v prv,
PAY_GRADE_RULES_f pgrf,
per_grades pg
WHERE papf.person_id = paaf.person_id
AND paaf.grade_id = pg.grade_id
AND paaf.grade_id = pgrf.GRADE_OR_SPINAL_POINT_ID
AND pg.grade_id = pgrf.GRADE_OR_SPINAL_POINT_ID
AND pgrf.rate_id = prv.rate_id
AND prv.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND prv.name = 'XXARJ_IN_PLL_MED_INS'
AND papf.employee_number = L_Employee_Number
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_mediclaim_input_value1 '||l_mediclaim_input_value1||' l_mediclaim_entry_value1 '||l_mediclaim_entry_value1);
r_csr_get_element_info.input_value_1 := l_mediclaim_input_value1;
r_csr_get_element_info.entry_value_1 := l_mediclaim_entry_value1;
EXCEPTION WHEN OTHERS THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'Grade Rate Missing Plz check.. ';
END;
END IF;
-- Update landing table with proper error status.
IF L_Error_Flag = 'Y' THEN
UPDATE XXARJ_PAY_CON_ELEMENT_TEMP
SET ERROR_FLAG = 'E'
,ERROR_MESSAGE = L_Error_Message
,PROCESS_FLAG = 'E'
,new_employee_number = L_Employee_Number
WHERE ROWID = r_csr_get_element_info.ROWID;
COMMIT;
ELSE
L_Error_Message := '';
L_Error_Flag := 'N';
-- Call Create element entry method..
create_element_entry (r_csr_get_element_info.ROWID
,r_csr_get_element_info.ELEMENT_NAME
,r_csr_get_element_info.Effective_Start_date
,FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
,L_Employee_Number
,r_csr_get_element_info.input_value_1
,r_csr_get_element_info.entry_value_1
,r_csr_get_element_info.input_value_2
,r_csr_get_element_info.entry_value_2
,r_csr_get_element_info.input_value_3
,r_csr_get_element_info.entry_value_3
,r_csr_get_element_info.input_value_4
,r_csr_get_element_info.entry_value_4
,r_csr_get_element_info.input_value_5
,r_csr_get_element_info.entry_value_5
,r_csr_get_element_info.input_value_6
,r_csr_get_element_info.entry_value_6
,r_csr_get_element_info.input_value_7
,r_csr_get_element_info.entry_value_7
,r_csr_get_element_info.input_value_8
,r_csr_get_element_info.entry_value_8
,r_csr_get_element_info.input_value_9
,r_csr_get_element_info.entry_value_9
,r_csr_get_element_info.input_value_10
,r_csr_get_element_info.entry_value_10
,r_csr_get_element_info.input_value_11
,r_csr_get_element_info.entry_value_11
,r_csr_get_element_info.input_value_12
,r_csr_get_element_info.entry_value_12
,r_csr_get_element_info.input_value_13
,r_csr_get_element_info.entry_value_13
,r_csr_get_element_info.input_value_14
,r_csr_get_element_info.entry_value_14
,r_csr_get_element_info.input_value_15
,r_csr_get_element_info.entry_value_15
,r_csr_get_element_info.batch_name
);
END IF;
L_rec_count := L_rec_count+1;
IF L_rec_count = 20 THEN
COMMIT;
L_rec_count :=0;
END IF;
END LOOP;
COMMIT;
SELECT COUNT(*)
INTO l_failure_records
FROM XXARJ_PAY_CON_ELEMENT_TEMP
WHERE PROCESS_FLAG = 'E'
AND REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
and business_group_id=FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND BATCH_NAME = NVL(pBatchName,BATCH_NAME);
l_success_records := l_total_records - l_failure_records;
FND_FILE.put_line(FND_FILE.OUTPUT,'End Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
FND_FILE.put_line(FND_FILE.OUTPUT,'#############################################################');
FND_FILE.put_line(FND_FILE.OUTPUT,'Total Records To Be Loaded : ' || l_total_records);
FND_FILE.put_line(FND_FILE.OUTPUT,'Total Success Records : ' || l_success_records);
FND_FILE.put_line(FND_FILE.OUTPUT,'Total Failure Records : ' || l_failure_records);
FND_FILE.put_line(FND_FILE.OUTPUT,'#############################################################');
-- Call Delete API if the the batch name is Tax Declaration
BEGIN
IF pBatchName = 'Tax Declaration Engineering' THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Calling Delete API for Tax Declaration.. ');
DELETE_ELEMENT_ENTRY('Tax Declaration Engineering');
FND_FILE.PUT_LINE(FND_FILE.LOG,'After Delete API.. ');
END IF;
IF pBatchName = 'House Rent Declaration Corporate' THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Calling Delete API for House Rent Declaration.. ');
DELETE_ELEMENT_ENTRY('House Rent Declaration Corporate');
FND_FILE.PUT_LINE(FND_FILE.LOG,'After Delete API.. ');
END IF;
EXCEPTION WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected Error while using delete API');
END;
EXCEPTION WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in CREATE_ELEMENT_ENTRY_MAIN');
END CREATE_ELEMENT_ENTRY_MAIN;
--
-- This procedure calls standard API to load the element entry..
PROCEDURE create_element_entry( p_rowid IN ROWID
,p_element_name IN VARCHAR2
,p_effectivedate IN DATE
,p_businessgroup_id IN NUMBER
,p_employee_num IN VARCHAR2
,p_input_value_1 IN VARCHAR2 default null
,p_entry_value_1 IN VARCHAR2 default null
,p_input_value_2 IN VARCHAR2 default null
,p_entry_value_2 IN VARCHAR2 default null
,p_input_value_3 IN VARCHAR2 default null
,p_entry_value_3 IN VARCHAR2 default null
,p_input_value_4 IN VARCHAR2 default null
,p_entry_value_4 IN VARCHAR2 default null
,p_input_value_5 IN VARCHAR2 default null
,p_entry_value_5 IN VARCHAR2 default null
,p_input_value_6 IN VARCHAR2 default null
,p_entry_value_6 IN VARCHAR2 default null
,p_input_value_7 IN VARCHAR2 default null
,p_entry_value_7 IN VARCHAR2 default null
,p_input_value_8 IN VARCHAR2 default null
,p_entry_value_8 IN VARCHAR2 default null
,p_input_value_9 IN VARCHAR2 default null
,p_entry_value_9 IN VARCHAR2 default null
,p_input_value_10 IN VARCHAR2 default null
,p_entry_value_10 IN VARCHAR2 default null
,p_input_value_11 IN VARCHAR2 default null
,p_entry_value_11 IN VARCHAR2 default null
,p_input_value_12 IN VARCHAR2 default null
,p_entry_value_12 IN VARCHAR2 default null
,p_input_value_13 IN VARCHAR2 default null
,p_entry_value_13 IN VARCHAR2 default null
,p_input_value_14 IN VARCHAR2 default null
,p_entry_value_14 IN VARCHAR2 default null
,p_input_value_15 IN VARCHAR2 default null
,p_entry_value_15 IN VARCHAR2 default null
,p_batch_name IN VARCHAR2 DEFAULT NULL
) IS
--
l_element_type_id pay_element_types_f.element_type_id%TYPE :=Null;
l_assignment_id per_all_assignments_f.assignment_id%TYPE :=Null;
l_element_link_id pay_element_links_f.element_link_id%TYPE :=Null;
--
l_input_value_id1 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id2 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id3 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id4 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id5 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id6 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id7 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id8 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id9 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id10 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id11 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id12 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id13 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id14 pay_input_values_f.input_value_id%TYPE :=Null;
l_input_value_id15 pay_input_values_f.input_value_id%TYPE :=Null;
--
l_effective_start_date DATE;
l_effective_end_date DATE;
l_element_entry_id NUMBER;
l_object_version_number NUMBER;
l_create_warning BOOLEAN;
l_err_txt VARCHAR2(1000);
--
CURSOR csr_get_element_info IS
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = p_element_name
AND (business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') OR LEGISLATION_CODE = 'IN')
AND p_effectivedate BETWEEN effective_start_date AND effective_end_date;
--
CURSOR csr_get_input_value_info(l_input_value_name VARCHAR2
,l_element_type_id NUMBER) IS
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = l_element_type_id
AND name = l_input_value_name
AND p_effectivedate BETWEEN effective_start_date AND effective_end_date;
--
CURSOR csr_get_assignment_info(l_element_type_id NUMBER) IS
SELECT DISTINCT paaf.assignment_id, pelf.element_link_id
FROM per_all_people_f papf
,per_all_assignments_f paaf
,pay_element_links_f pelf
WHERE papf.employee_number = p_employee_num
AND papf.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND papf.person_id = paaf.person_id
AND paaf.primary_flag = 'Y'
AND l_element_type_id = pelf.element_type_id
AND pelf.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND pelf.payroll_id = paaf.payroll_id;
-- OR pelf.link_to_all_payrolls_flag = 'Y' // update for car emi
-- OR pelf.link_to_all_payrolls_flag = 'N'); -- Temporary validation to allow Mediclaim...
--
--
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Element Entry Operation Called -*-*-*-');
FND_FILE.PUT_LINE(FND_FILE.LOG, ' Start Validation....');
OPEN csr_get_element_info;
FETCH csr_get_element_info INTO l_element_type_id;
CLOSE csr_get_element_info;
--
IF l_element_type_id IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := 'Element is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_element_type_id : ' || l_element_type_id);
END IF;
--
IF p_input_value_1 IS NOT NULL AND p_entry_value_1 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_1, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id1;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id1 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message|| ' * Input Value ID 1 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id1 : ' || l_input_value_id1);
END IF;
--
END IF;
--
IF p_input_value_2 IS NOT NULL AND p_entry_value_2 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_2, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id2;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id2 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message|| ' * Input Value ID 2 is missing ';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id2 : ' || l_input_value_id2);
END IF;
--
END IF;
--
IF p_input_value_3 IS NOT NULL AND p_entry_value_3 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_3, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id3;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id3 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'* Input Value ID 3 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id3 : ' || l_input_value_id3);
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Foundv p_input_value_3 : ' || p_input_value_3);
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Foundv p_entry_value_3 : ' || p_entry_value_3 );
END IF;
--
END IF;
--
IF p_input_value_4 IS NOT NULL AND p_entry_value_4 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_4, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id4;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id4 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'Input Value ID 4 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id4 : ' || l_input_value_id4);
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Foundv p_input_value_4 : ' || p_input_value_4);
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Foundv p_entry_value_4 : ' || p_entry_value_4 );
END IF;
--
END IF;
--
IF p_input_value_5 IS NOT NULL AND p_entry_value_5 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_5, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id5;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id5 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'**Input Value ID 5 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id5 : ' || l_input_value_id5);
END IF;
--
END IF;
--
IF p_input_value_6 IS NOT NULL AND p_entry_value_6 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_6, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id6;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id6 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'**Input Value ID 6 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id6 : ' || l_input_value_id6);
END IF;
--
END IF;
--
IF p_input_value_7 IS NOT NULL AND p_entry_value_7 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_7, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id7;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id7 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'**Input Value ID 7 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id7 : ' || l_input_value_id7);
END IF;
--
END IF;
--
IF p_input_value_8 IS NOT NULL AND p_entry_value_8 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_8, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id8;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id8 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'**Input Value ID 8 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id8 : ' || l_input_value_id8);
END IF;
--
END IF;
--
IF p_input_value_9 IS NOT NULL AND p_entry_value_9 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_9, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id9;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id9 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'**Input Value ID 9 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id9 : ' || l_input_value_id9);
END IF;
--
END IF;
--
IF p_input_value_10 IS NOT NULL AND p_entry_value_10 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_10, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id10;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id10 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'**Input Value ID 10 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id10 : ' || l_input_value_id10);
END IF;
--
END IF;
--
IF p_input_value_11 IS NOT NULL AND p_entry_value_11 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_11, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id11;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id11 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'**Input Value ID 11 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id11 : ' || l_input_value_id11);
END IF;
--
END IF;
--
IF p_input_value_12 IS NOT NULL AND p_entry_value_12 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_12, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id12;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id12 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'**Input Value ID 12 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id12 : ' || l_input_value_id12);
END IF;
--
END IF;
--
IF p_input_value_13 IS NOT NULL AND p_entry_value_13 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_13, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id13;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id13 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'**Input Value ID 13 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id13 : ' || l_input_value_id13);
END IF;
--
END IF;
--
IF p_input_value_14 IS NOT NULL AND p_entry_value_14 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_14, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id14;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id14 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'**Input Value ID 14 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id14 : ' || l_input_value_id14);
END IF;
--
END IF;
--
IF p_input_value_15 IS NOT NULL AND p_entry_value_15 IS NOT NULL THEN
OPEN csr_get_input_value_info(p_input_value_15, l_element_type_id);
FETCH csr_get_input_value_info INTO l_input_value_id15;
CLOSE csr_get_input_value_info;
--
IF l_input_value_id15 IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'**Input Value ID 15 is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_input_value_id15 : ' || l_input_value_id15);
END IF;
--
END IF;
--
OPEN csr_get_assignment_info(l_element_type_id);
FETCH csr_get_assignment_info INTO l_assignment_id, l_element_link_id;
CLOSE csr_get_assignment_info;
--
IF l_assignment_id IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'**Assignment ID is missing ';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_assignment_id : ' || l_assignment_id);
END IF;
--
IF l_element_link_id IS NULL THEN
L_Error_Flag := 'Y';
L_Error_Message := L_Error_Message||'**Element Link is missing';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_element_link_id : ' || l_element_link_id);
END IF;
--
--
-- 1 or more validation failed... update the landing table..
IF L_Error_Flag = 'Y' THEN
UPDATE XXARJ_PAY_CON_ELEMENT_TEMP
SET Error_Message = L_Error_Message
, Process_Flag = 'E'
, Error_Flag = 'E'
,new_employee_number=L_Employee_Number
WHERE ROWID = p_rowid;
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,' Calling pay_element_entry_api.create_element_entry ');
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Found l_assignment_id, l_element_link_id : ' || l_assignment_id || ', '|| l_element_link_id);
FND_FILE.PUT_LINE(FND_FILE.LOG, '** l_input_value_id1 : '||l_input_value_id1||' ** l_input_value_id2 : '||l_input_value_id2);
FND_FILE.PUT_LINE(FND_FILE.LOG, '** p_entry_value_1 : '||p_entry_value_1||' ** p_entry_value_2 : '||p_entry_value_2);
pay_element_entry_api.create_element_entry
(p_validate => FALSE
,p_effective_date => p_effectivedate
,p_business_group_id => p_businessgroup_id
,p_original_entry_id => null
,p_assignment_id => l_assignment_id
,p_element_link_id => l_element_link_id
,p_entry_type => 'E'
,p_creator_type => 'F'
,p_input_value_id1 => l_input_value_id1
,p_input_value_id2 => l_input_value_id2
,p_input_value_id3 => l_input_value_id3
,p_input_value_id4 => l_input_value_id4
,p_input_value_id5 => l_input_value_id5
,p_input_value_id6 => l_input_value_id6
,p_input_value_id7 => l_input_value_id7
,p_input_value_id8 => l_input_value_id8
,p_input_value_id9 => l_input_value_id9
,p_input_value_id10 => l_input_value_id10
,p_input_value_id11 => l_input_value_id11
,p_input_value_id12 => l_input_value_id12
,p_input_value_id13 => l_input_value_id13
,p_input_value_id14 => l_input_value_id14
,p_input_value_id15 => l_input_value_id15
,p_entry_value1 => p_entry_value_1
,p_entry_value2 => p_entry_value_2
,p_entry_value3 => p_entry_value_3
,p_entry_value4 => p_entry_value_4
,p_entry_value5 => p_entry_value_5
,p_entry_value6 => p_entry_value_6
,p_entry_value7 => p_entry_value_7
,p_entry_value8 => p_entry_value_8
,p_entry_value9 => p_entry_value_9
,p_entry_value10 => p_entry_value_10
,p_entry_value11 => p_entry_value_11
,p_entry_value12 => p_entry_value_12
,p_entry_value13 => p_entry_value_13
,p_entry_value14 => p_entry_value_14
,p_entry_value15 => p_entry_value_15
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_create_warning => l_create_warning);
--
UPDATE XXARJ_PAY_CON_ELEMENT_TEMP
SET Process_Flag = 'P'
,ERROR_FLAG = NULL
,ERROR_MESSAGE = NULL
,new_employee_number=L_Employee_Number
,element_entry_id = l_element_entry_id
,object_version_number = l_object_version_number
WHERE ROWID = p_rowid ;
FND_FILE.PUT_LINE(FND_FILE.LOG,'-*-*-*- Element Entry Created -*-*-*-');
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_effective_start_date ' || to_char(l_effective_start_date,'DD-Mon-YYYY'));
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_effective_end_date ' || to_char(l_effective_end_date,'DD-Mon-YYYY'));
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_element_entry_id ' || l_element_entry_id);
END IF;
EXCEPTION WHEN OTHERS THEN
l_err_txt := NULL;
l_err_txt := SUBSTR(SQLERRM,1,290);
--
UPDATE XXARJ_PAY_CON_ELEMENT_TEMP
SET Error_Message = 'Exception : '||l_err_txt
, Process_Flag = 'E'
, ERROR_FLAG = 'E'
WHERE rowid = p_rowid ;
FND_FILE.PUT_LINE(FND_FILE.LOG,'~~~~ Operation unsuccessful ~~~~');
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_effective_start_date ' || to_char(l_effective_start_date,'DD-Mon-YYYY'));
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_effective_end_date ' || to_char(l_effective_end_date,'DD-Mon-YYYY'));
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_element_entry_id ' || l_element_entry_id);
END create_element_entry;
PROCEDURE delete_element_entry(p_BATCH_NAME IN VARCHAR2 DEFAULT NULL)
IS
CURSOR c_del_element_entry(p_BATCH_NAME VARCHAR2)IS
SELECT XPCET.ROWID,
XPCET.*
FROM XXARJ_PAY_CON_ELEMENT_TEMP XPCET
WHERE element_entry_id IS NOT NULL
AND BATCH_NAME = p_BATCH_NAME --;
AND delete_flag IS null AND process_flag = 'P';
l_delete_warning BOOLEAN := FALSE;
l_object_version_number NUMBER;
l_effective_start_date DATE;
l_effective_end_date DATE;
v_effective_end_date DATE := '31-MAR-2011';
l_ELEMENT_ENTRY_ID number;
BEGIN
FOR r_del_element_entry IN c_del_element_entry(p_BATCH_NAME)
LOOP
l_object_version_number := r_del_element_entry.object_version_number;
l_ELEMENT_ENTRY_ID := r_del_element_entry.ELEMENT_ENTRY_ID;
BEGIN
pay_element_entry_api.delete_element_entry(
p_validate => FALSE
, p_datetrack_delete_mode => 'DELETE'
, p_effective_date => v_effective_end_date --SYSDATE
, p_element_entry_id => l_ELEMENT_ENTRY_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_delete_warning => l_delete_warning
);
IF l_delete_warning THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while deleting.... '||SQLERRM);
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleted element entry for element entry ID: '||r_del_element_entry.ELEMENT_ENTRY_ID);
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_effective_end_date '||l_effective_end_date);
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_effective_start_date '||l_effective_start_date);
UPDATE XXARJ_PAY_CON_ELEMENT_TEMP
SET DELETE_FLAG = 'Y'
,OBJECT_VERSION_NUMBER = l_object_version_number
WHERE ROWID = r_del_element_entry.ROWID;
COMMIT;
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('XXARJ Unexpected Error while processing element_entry_id : '|| l_ELEMENT_ENTRY_ID ||' Error msg.. '||SQLERRM);
END;
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('XXARJ Unexpected Error ....'||SQLERRM);
END delete_element_entry;
END XXARJ_PAY_ELEMENT_ENTRY_PKG;
No comments:
Post a Comment