XXCF
XXCF
XXCF
AS
PROCEDURE create_person_extra_info_b (
p_person_id NUMBER,
p_information_type VARCHAR2,
p_pei_attribute_category VARCHAR2,
p_pei_attribute1 VARCHAR2,
p_pei_attribute2 VARCHAR2,
p_pei_attribute3 VARCHAR2,
p_pei_attribute4 VARCHAR2,
p_pei_attribute5 VARCHAR2,
p_pei_attribute6 VARCHAR2,
p_pei_attribute7 VARCHAR2,
p_pei_attribute8 VARCHAR2,
p_pei_attribute9 VARCHAR2,
p_pei_attribute10 VARCHAR2,
p_pei_attribute11 VARCHAR2,
p_pei_attribute12 VARCHAR2,
p_pei_attribute13 VARCHAR2,
p_pei_attribute14 VARCHAR2,
p_pei_attribute15 VARCHAR2,
p_pei_attribute16 VARCHAR2,
p_pei_attribute17 VARCHAR2,
p_pei_attribute18 VARCHAR2,
p_pei_attribute19 VARCHAR2,
p_pei_attribute20 VARCHAR2,
p_pei_information_category VARCHAR2,
p_pei_information1 VARCHAR2,
p_pei_information2 VARCHAR2,
p_pei_information3 VARCHAR2,
p_pei_information4 VARCHAR2,
p_pei_information5 VARCHAR2,
p_pei_information6 VARCHAR2,
p_pei_information7 VARCHAR2,
p_pei_information8 VARCHAR2,
p_pei_information9 VARCHAR2,
p_pei_information10 VARCHAR2,
p_pei_information11 VARCHAR2,
p_pei_information12 VARCHAR2,
p_pei_information13 VARCHAR2,
p_pei_information14 VARCHAR2,
p_pei_information15 VARCHAR2,
p_pei_information16 VARCHAR2,
p_pei_information17 VARCHAR2,
p_pei_information18 VARCHAR2,
p_pei_information19 VARCHAR2,
p_pei_information20 VARCHAR2,
p_pei_information21 VARCHAR2,
p_pei_information22 VARCHAR2,
p_pei_information23 VARCHAR2,
p_pei_information24 VARCHAR2,
p_pei_information25 VARCHAR2,
p_pei_information26 VARCHAR2,
p_pei_information27 VARCHAR2,
p_pei_information28 VARCHAR2,
p_pei_information29 VARCHAR2,
p_pei_information30 VARCHAR2)
IS
v_nat VARCHAR2 (30000);
v_user_id NUMBER;
v_count NUMBER;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_assignment_id NUMBER
:= xxqsc_hr_wf_pkg.get_person_assignmnet_id (p_person_id);
v_validate_date NUMBER;
v_sponser VARCHAR2 (500);
v_sponser_exit_permt VARCHAR2 (500);
CURSOR c_valid
IS
SELECT DECODE (grade_id, NULL, -1, grade_id) grade,
DECODE (payroll_id, NULL, -1, payroll_id) payroll,
DECODE (pay_basis_id, NULL, -1, pay_basis_id) salary_basis
FROM per_assignments_f
WHERE person_id = p_person_id
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date;
CURSOR c_prob_period
IS
/*SELECT ass_attribute1
FROM per_assignments_f
WHERE person_id = p_person_id
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date;*/
SELECT CASE
WHEN TRUNC (
TO_DATE (a.attribute7, 'RRRR/MM/DD HH24:MI:SS')) >=
TRUNC (SYSDATE)
OR a.attribute7 IS NULL
THEN
'N'
WHEN TRUNC (
TO_DATE (a.attribute7, 'RRRR/MM/DD HH24:MI:SS')) <
TRUNC (SYSDATE)
THEN
'Y'
END
prob_pass
FROM per_all_people_f a
WHERE SYSDATE BETWEEN a.effective_start_date
AND a.effective_end_date
AND a.person_id = p_person_id;
CURSOR c_start
IS
SELECT MONTHS_BETWEEN (
TO_DATE (TO_CHAR (TRUNC (SYSDATE), 'MM/DD/RRRR'),
'MM/DD/RRRR'),
TO_DATE (TO_CHAR (TRUNC (start_date), 'MM/DD/RRRR'),
'MM/DD/RRRR'))
hiredate
FROM per_people_f
WHERE person_id = p_person_id;
v_hire NUMBER;
CURSOR c_ticket
IS
SELECT segment5
FROM pay_people_groups
WHERE people_group_id =
(SELECT people_group_id
FROM per_assignments_f
WHERE person_id = p_person_id
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date);
CURSOR c_school
IS
SELECT segment7
FROM pay_people_groups
WHERE people_group_id =
(SELECT people_group_id
FROM per_assignments_f
WHERE person_id = p_person_id
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date);
CURSOR c_effective_date
IS
SELECT MONTHS_BETWEEN (
TO_DATE (TO_CHAR (TRUNC (SYSDATE), 'MM/DD/RRRR'),
'MM/DD/RRRR'),
TO_DATE (
TO_CHAR (TRUNC (f.effective_start_date), 'MM/DD/RRRR'),
'MM/DD/RRRR'))
total,
f.effective_start_date,
elementtl.element_name,
f.element_entry_id
FROM pay_element_entries_f f,
pay_element_types_f_tl elementtl,
pay_element_types_f ELEMENT
WHERE f.assignment_id =
(SELECT assignment_id
FROM per_assignments_f
WHERE person_id = p_person_id
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
AND ELEMENT.element_type_id = elementtl.element_type_id
AND elementtl.LANGUAGE = USERENV ('LANG')
AND ELEMENT.element_type_id = f.element_type_id
AND elementtl.element_name = 'Furniture Grant Payment'
AND f.element_entry_id =
(SELECT MAX (element_entry_id)
FROM pay_element_entries_f f1,
pay_element_types_f_tl elementtl1,
pay_element_types_f element1
WHERE f1.assignment_id =
(SELECT assignment_id
FROM per_assignments_f
WHERE person_id = p_person_id
AND TRUNC (SYSDATE) BETWEEN
effective_start_date
AND
effective_end_date)
AND element1.element_type_id =
elementtl1.element_type_id
AND elementtl1.LANGUAGE = USERENV ('LANG')
AND element1.element_type_id =
f1.element_type_id
AND elementtl1.element_name =
'Furniture Grant Payment');
v_effective_date DATE;
v_num NUMBER;
v_ticket VARCHAR2 (300);
v_school VARCHAR2 (300);
v_exit_eligible_date DATE;
------------------Exit Permit------------------------------
IF p_information_type = 'QSC_SS_EXIT_PERMIT_REQUEST'
THEN
v_nat := xxqsc_hr_wf_pkg.get_employee_nationality (81, p_person_id);
-- v_exit_eligible_date :=
-- fnd_date.canonical_to_date (P_PEI_INFORMATION3) - 6;
-- v_validate_date :=
-- ( fnd_date.canonical_to_date (P_PEI_INFORMATION3)
-- - TRUNC (SYSDATE))
-- + 1;
-------------------------EDIT -----------------------
/* intejar--- st */
BEGIN
SELECT DISTINCT ATTRIBUTE5
INTO v_sponser
FROM per_all_people_f
WHERE person_id = p_person_id
AND TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_end_DATE;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/* intejar--- end */
----IF (v_nat = 'QATAR' THEN INTEJAR ADDED BELOW
v_exit_eligible_date := NULL;
END IF;
BEGIN
SELECT DISTINCT ATTRIBUTE5
INTO v_sponser
FROM per_all_people_f
WHERE person_id = p_person_id
AND TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_end_DATE;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
IF NVL (v_sponser, 'N') <> 'ESHAILSAT'
THEN
hr_utility.set_message (800, 'XXQSC_RENWAL_NON_SPONSER_MSG');
---hr_utility.set_message ('You are not a valid sponser for apply this
document');
hr_utility.raise_error;
END IF;
---ATTRIBUTE5 <> 'ESHAILSAT'
END IF;
--------------------------carloan-------------------------------
IF p_information_type = 'QSC_SS_CAR_LOAN_REQUEST'
THEN
FOR j IN c_prob_period
LOOP
v_prob := j.prob_pass;
END LOOP;
FOR o IN c_start
LOOP
v_hire := o.hiredate;
END LOOP;
/* IF (v_nat <> 'QATAR' AND v_hire < 12)
THEN
hr_utility.set_message (800, 'XXQSC_CAR_LOAN_VALIDATION');
hr_utility.raise_error;
END IF;*/
END IF;
END IF;
IF v_count > 0
THEN
-- raise_application_error (
-- -20200,
-- 'Unfortunately you cannot be able to Create another
Request as it was being applied before');
-- -- hr_utility.set_message (800, 'XX_ONE_TIME_REQ');
-- hr_utility.raise_error;
------------Ticket Request---------------------------------------------
IF p_information_type = 'QSC_SS_ANNUAL_TICKET_HR_REQ'
THEN
v_nat := xxqsc_hr_wf_pkg.get_employee_nationality (81, p_person_id);
FOR w IN c_ticket
LOOP
v_ticket := w.segment5;
END LOOP;
IF v_nat = 'QATAR'
THEN
hr_utility.set_message (800, 'XXQSC_RENWAL_NON_QATARI_MSG');
hr_utility.raise_error;
ELSE
IF (NVL (v_ticket, 'N') <> 'Y')
THEN
hr_utility.set_message (800, 'XXQSC_TICKET_ENTITLED_VALIE');
hr_utility.raise_error;
END IF;
END IF;
END IF;
IF v_num < 48
THEN
hr_utility.set_message (800, 'XXQSC_FUR_MAIN_VALI');
hr_utility.raise_error;
END IF;
END IF;
END IF;
-----------------------------------------Schooling Allowance Request
--------------------------------
IF p_information_type = 'QSC_SS_SCHOOL_ALLOWANCE_REQ'
THEN
DECLARE
v_proc NUMBER;
v_unproc NUMBER;
v_entitled_smstr NUMBER := 0;
p_hire_date DATE
:= TO_DATE (p_pei_information8, 'YYYY-MM-DD HH24:MI:SS'); --
fnd_date.date_to_canonical(p_pei_information8);
BEGIN
-- IF TO_DATE (p_pei_information8) <= TO_DATE ('31-12-' ||
SUBSTR (p_pei_information4, 1, 4), 'dd-mm-yyyy')
-- THEN
-- v_entitled_smstr := 3;
-- ELSIF TO_DATE (p_pei_information8) > TO_DATE ( '31-
12-' || SUBSTR (p_pei_information4, 1, 4), 'dd-mm-yyyy')
-- AND TO_DATE (p_pei_information8) <= TO_DATE ( '31-
03-' || SUBSTR (p_pei_information4, 6, 4), 'dd-mm-yyyy')
-- THEN
-- v_entitled_smstr := 2;
-- ELSIF TO_DATE (p_pei_information8) > TO_DATE ( '31-
03-' || SUBSTR (p_pei_information4, 6, 4), 'dd-mm-yyyy')
-- AND TO_DATE (p_pei_information8) <= TO_DATE ( '30-
06-' || SUBSTR (p_pei_information4, 6, 4), 'dd-mm-yyyy')
-- THEN
-- v_entitled_smstr := 1;
-- END IF;
IF p_hire_date <=
TO_DATE ('31-12-' || SUBSTR (p_pei_information4, 1, 4),
'dd-mm-rrrr')
THEN
v_entitled_smstr := 3;
ELSIF p_hire_date >
TO_DATE (
'31-12-' || SUBSTR (p_pei_information4, 1, 4),
'dd-mm-rrrr')
AND p_hire_date <=
TO_DATE (
'31-03-' || SUBSTR (p_pei_information4, 6, 4),
'dd-mm-rrrr')
THEN
v_entitled_smstr := 2;
ELSIF p_hire_date >
TO_DATE (
'31-03-' || SUBSTR (p_pei_information4, 6, 4),
'dd-mm-rrrr')
AND p_hire_date <=
TO_DATE (
'30-06-' || SUBSTR (p_pei_information4, 6, 4),
'dd-mm-rrrr')
THEN
v_entitled_smstr := 1;
END IF;
-- check entitled
IF xxqsc_hr_wf_pkg.get_ppg_segment_val (p_person_id, 7) <> 'Y'
THEN
hr_utility.set_message (800, 'XXQSC_SS_NOT_ENTITLED');
hr_utility.raise_error;
END IF;
IF NVL (p_pei_information1, 0)
+ NVL (v_proc, 0)
+ NVL (v_unproc, 0) >
xxqsc_hr_wf_pkg.get_entitled_edu_allow (v_assignment_id,
SYSDATE)
* (v_entitled_smstr / 3)
THEN
hr_utility.set_message (800, 'XXQSC_SS_EXCEED_AMOUNT');
fnd_message.set_token (
'msg',
ROUND (
( xxqsc_hr_wf_pkg.get_entitled_edu_allow (
v_assignment_id,
SYSDATE)
* (v_entitled_smstr / 3))
- (NVL (v_proc, 0) + NVL (v_unproc, 0)),
2)
|| ' are entitled only for '
|| v_entitled_smstr
|| ' Semester(s)');
hr_utility.raise_error;
END IF;
END;
END IF;
-----------------------------------------
QSC_SS_TRAVEL_ADVANCED----------------------
DECLARE
v_eligible_date DATE;
v_working_days NUMBER := 15;
BEGIN
SELECT ( fnd_date.canonical_to_date (P_PEI_INFORMATION10)
- v_working_days)
- (xxqsc_payroll.get_holidays (
p_start_date => fnd_date.canonical_to_date (
P_PEI_INFORMATION10)
- v_working_days,
p_end_date => fnd_date.canonical_to_date (
P_PEI_INFORMATION10)))
INTO v_eligible_date
FROM DUAL;
IF P_INFORMATION_TYPE = 'QSC_SS_BTR_ADVANC_PAY_REQ'
THEN
IF fnd_date.canonical_to_date (P_PEI_INFORMATION10)
- TRUNC (SYSDATE)
- xxqsc_payroll.get_holidays (
SYSDATE,
fnd_date.canonical_to_date (P_PEI_INFORMATION10)) >
v_working_days
THEN
-- raise_application_error (
-- -20555,
-- 'You are not Eligible to submit Advanced Per
Diem Request before : '
-- || v_eligible_date);
-- HR_UTILITY.RAISE_ERROR;
-----------------------------------------QSC_SS_DUTY_RESUMP_REQ
--------------------------------
IF p_information_type = 'QSC_SS_TRAVEL_RESUMP_REQ'
THEN
DECLARE
CURSOR c5
IS
SELECT 'X'
FROM per_people_extra_info a
WHERE a.person_id = p_person_id
AND a.information_type = 'QSC_SS_TRAVEL_RESUMP_REQ'
AND ( fnd_date.canonical_to_date (p_pei_information2)
BETWEEN fnd_date.canonical_to_date (
a.pei_information2)
AND fnd_date.canonical_to_date (
a.pei_information3)
OR fnd_date.canonical_to_date (p_pei_information3)
BETWEEN fnd_date.canonical_to_date (
a.pei_information2)
AND fnd_date.canonical_to_date (
a.pei_information3)
OR fnd_date.canonical_to_date (
p_pei_information10) BETWEEN
fnd_date.canonical_to_date (
a.pei_information10)
AND
fnd_date.canonical_to_date (
a.pei_information11)
OR fnd_date.canonical_to_date (
p_pei_information11) BETWEEN
fnd_date.canonical_to_date (
a.pei_information10)
AND
fnd_date.canonical_to_date (
a.pei_information11))
HAVING COUNT (*) > 0;
--
x5 CHAR (1);
--
CURSOR c_days
IS
SELECT NVL (a.nights_by_host_eshailsat, 0) host_days
FROM xxqsc_travel_authorization_v a
WHERE a.person_extra_info_id = p_pei_information13;
v_inf14 DATE
:= CASE
WHEN fnd_date.canonical_to_date (p_pei_information14) =
'01-Jan-2010'
THEN
NULL
ELSE
fnd_date.canonical_to_date (p_pei_information14)
END;
v_inf15 DATE
:= CASE
WHEN fnd_date.canonical_to_date (p_pei_information15) =
'01-Jan-2010'
THEN
NULL
ELSE
fnd_date.canonical_to_date (p_pei_information15)
END;
v_inf16 DATE
:= CASE
WHEN fnd_date.canonical_to_date (p_pei_information16) =
'01-Jan-2010'
THEN
NULL
ELSE
fnd_date.canonical_to_date (p_pei_information16)
END;
v_inf17 DATE
:= CASE
WHEN fnd_date.canonical_to_date (p_pei_information17) =
'01-Jan-2010'
THEN
NULL
ELSE
fnd_date.canonical_to_date (p_pei_information17)
END;
v_inf19 DATE
:= CASE
WHEN fnd_date.canonical_to_date (p_pei_information19) =
'01-Jan-2010'
THEN
NULL
ELSE
fnd_date.canonical_to_date (p_pei_information19)
END;
v_inf20 DATE
:= CASE
WHEN fnd_date.canonical_to_date (p_pei_information20) =
'01-Jan-2010'
THEN
NULL
ELSE
fnd_date.canonical_to_date (p_pei_information20)
END;
BEGIN
-------- Check if last day of bs date from greater than first day of bs
-- date
IF TRUNC (TO_DATE (p_pei_information2, 'RRRR-MM-DD HH24:MI:SS')) >
TRUNC (
TO_DATE (p_pei_information3, 'RRRR-MM-DD HH24:MI:SS'))
THEN
hr_utility.set_message (800, 'XXQSC_DUTY_RESUMP_ERR');
hr_utility.raise_error;
END IF;
-------- Check if first check in < departure date or < first day of bs
/* IF P_PEI_INFORMATION15 < P_PEI_INFORMATION10 or
P_PEI_INFORMATION15 < P_PEI_INFORMATION2 THEN
HR_UTILITY.SET_MESSAGE(800, 'XXQSC_DUTY_RESUMP_ERR');
HR_UTILITY.RAISE_ERROR;
END IF; */
-------- Check if return date < departure date
IF TRUNC (TO_DATE (p_pei_information10, 'RRRR-MM-DD HH24:MI:SS')) >
TRUNC (
TO_DATE (p_pei_information11, 'RRRR-MM-DD HH24:MI:SS'))
THEN
hr_utility.set_message (800, 'XXQSC_DUTY_RESUMP_ERR');
hr_utility.raise_error;
END IF;
IF c5%FOUND
THEN
CLOSE c5;
a.pei_information10)
AND
fnd_date.canonical_to_date (
a.pei_information11)
OR fnd_date.canonical_to_date (
p_pei_information11) BETWEEN
fnd_date.canonical_to_date (
a.pei_information10)
AND
fnd_date.canonical_to_date (
a.pei_information11)
OR fnd_date.canonical_to_date (p_pei_information8)
BETWEEN fnd_date.canonical_to_date (
a.pei_information8)
AND fnd_date.canonical_to_date (
a.pei_information9)
OR fnd_date.canonical_to_date (p_pei_information9)
BETWEEN fnd_date.canonical_to_date (
a.pei_information8)
AND fnd_date.canonical_to_date (
a.pei_information9))
AND NVL (a.pei_information30, 'EMPTY') NOT IN
('CANCELED')
HAVING COUNT (*) > 0;
x CHAR (1);
v_departure_days NUMBER
:= GREATEST (
NVL (
xxqsc_hr_utility2.get_dest_days_per_diem (
p_person_id,
p_pei_information20),
0),
1);
BEGIN
--- check if departure date = first day of business and travel days >1
IF v_departure_days > 1
AND (p_pei_information8 = p_pei_information10)
THEN
hr_utility.set_message (800, 'XXQSC_TRAVEL_DAYS_ERR');
hr_utility.raise_error;
END IF;
-- Last day of business < departure date or last day of business >
return
-- date
IF p_pei_information11 < p_pei_information8
OR p_pei_information11 > p_pei_information9
THEN
hr_utility.set_message (800, 'XXQSC_LAST_DATE<DEPARTURE_ERR');
hr_utility.raise_error;
END IF;
-- -- First day of business < departure date or First day of business >
-- return date
IF p_pei_information10 < p_pei_information8
OR p_pei_information10 > p_pei_information9
THEN
hr_utility.set_message (800, 'XXQSC_FIRST_DATE<DEPARTURE_ERR');
hr_utility.raise_error;
END IF;
----- Arranged By
IF p_pei_information7 = 'EMPLOYEE'
AND NVL (p_pei_information24, 0) > 0
THEN
hr_utility.set_message (800, 'XXQSC_ARRANGED_BY_EMP_ERR');
hr_utility.raise_error;
ELSIF p_pei_information7 IN ('EMP_HOST', 'EMP_ESHAILSAT')
AND ( NVL (p_pei_information21, 0) = 0
OR NVL (p_pei_information24, 0) = 0)
THEN
hr_utility.set_message (800, 'XXQSC_ARRANGED_HOST_ZERO_ERR');
hr_utility.raise_error;
--ELSIF P_PEI_INFORMATION7 IN ('ESHAILSAT','HOST') AND NVL(
-- P_PEI_INFORMATION21,0)>0 THEN
-- HR_UTILITY.SET_MESSAGE(800, 'XXQSC_ARRANGED_BY_HOST_ERR');
--HR_UTILITY.RAISE_ERROR;
END IF;
---------- No Duplicate
OPEN c1;
FETCH c1 INTO x;
IF c1%FOUND
THEN
CLOSE c1;
--
IF v_adult_bag_val < NVL (p_pei_information8, 0)
OR v_child_bag_val < NVL (p_pei_information9, 0)
THEN
hr_utility.set_message (
800,
'Adult kg to be '
|| v_adult_bag_val
|| ' Child kg '
|| v_child_bag_val);
hr_utility.raise_error;
END IF;
END;
END IF;
IF p_pei_information3 < 1
THEN
hr_utility.set_message (800, 'XXQSC_DYNAMIC_MESSAGE');
hr_utility.set_message_token (
'MESSAGE',
'You are not Eligible to submit Long Term Incentive Request,
before completing 5 years of service');
hr_utility.raise_error;
END IF;
END IF;
END IF;
IF p_information_type = 'QSC_SS_ANNUAL_LEAVE_PLAN_REQ'
THEN
V_RESULT :=
XX_HR_PKG.GET_VACATION_PLAN_EXIST (
p_person_id,
TO_DATE (p_pei_information2, 'RRRR/MM/DD HH24:MI:SS'),
TO_DATE (p_pei_information3, 'RRRR/MM/DD HH24:MI:SS'),
-- TO_DATE (FND_DATE.CANONICAL_TO_DATE
(p_pei_information2), 'DD/MM/RRRR'),
-- TO_DATE (FND_DATE.CANONICAL_TO_DATE
(p_pei_information3), 'DD/MM/RRRR'),
V_START_DATE,
V_END_dATE);
IF V_RESULT = 1
THEN
V_START_DATE :=
INITCAP (TO_CHAR (TO_DATE (V_START_DATE), 'DD-MON-RRRR'));
V_END_dATE :=
INITCAP (TO_CHAR (TO_DATE (V_END_dATE), 'DD-MON-RRRR'));
---------------------
PROCEDURE create_person_extra_info_a (
p_person_id NUMBER,
p_information_type VARCHAR2,
p_pei_attribute_category VARCHAR2,
p_pei_attribute1 VARCHAR2,
p_pei_attribute2 VARCHAR2,
p_pei_attribute3 VARCHAR2,
p_pei_attribute4 VARCHAR2,
p_pei_attribute5 VARCHAR2,
p_pei_attribute6 VARCHAR2,
p_pei_attribute7 VARCHAR2,
p_pei_attribute8 VARCHAR2,
p_pei_attribute9 VARCHAR2,
p_pei_attribute10 VARCHAR2,
p_pei_attribute11 VARCHAR2,
p_pei_attribute12 VARCHAR2,
p_pei_attribute13 VARCHAR2,
p_pei_attribute14 VARCHAR2,
p_pei_attribute15 VARCHAR2,
p_pei_attribute16 VARCHAR2,
p_pei_attribute17 VARCHAR2,
p_pei_attribute18 VARCHAR2,
p_pei_attribute19 VARCHAR2,
p_pei_attribute20 VARCHAR2,
p_pei_information_category VARCHAR2,
p_pei_information1 VARCHAR2,
p_pei_information2 VARCHAR2,
p_pei_information3 VARCHAR2,
p_pei_information4 VARCHAR2,
p_pei_information5 VARCHAR2,
p_pei_information6 VARCHAR2,
p_pei_information7 VARCHAR2,
p_pei_information8 VARCHAR2,
p_pei_information9 VARCHAR2,
p_pei_information10 VARCHAR2,
p_pei_information11 VARCHAR2,
p_pei_information12 VARCHAR2,
p_pei_information13 VARCHAR2,
p_pei_information14 VARCHAR2,
p_pei_information15 VARCHAR2,
p_pei_information16 VARCHAR2,
p_pei_information17 VARCHAR2,
p_pei_information18 VARCHAR2,
p_pei_information19 VARCHAR2,
p_pei_information20 VARCHAR2,
p_pei_information21 VARCHAR2,
p_pei_information22 VARCHAR2,
p_pei_information23 VARCHAR2,
p_pei_information24 VARCHAR2,
p_pei_information25 VARCHAR2,
p_pei_information26 VARCHAR2,
p_pei_information27 VARCHAR2,
p_pei_information28 VARCHAR2,
p_pei_information29 VARCHAR2,
p_pei_information30 VARCHAR2)
AS
--v_assignment_id number := xxqsc_hr_wf_pkg.get_person_assignmnet_id(
-- p_person_id);
CURSOR c_get_assignment_id
IS
SELECT f.assignment_id
FROM per_all_assignments_f f
WHERE f.person_id = p_person_id
AND SYSDATE BETWEEN f.effective_start_date
AND f.effective_end_date
AND f.primary_flag = 'Y';
CURSOR c_pay_id (
p_ass_id NUMBER)
IS
SELECT personal_payment_method_id,
external_account_id,
effective_start_date,
effective_end_date,
object_version_number
FROM pay_personal_payment_methods_f
WHERE assignment_id = p_ass_id
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date;
v_assignment_id NUMBER
:= xxqsc_hr_wf_pkg.get_person_assignmnet_id (p_person_id);
v_element_link_id NUMBER;
v_effective_start_date DATE;
v_effective_end_date DATE;
v_element_entry_id NUMBER;
v_create_warning BOOLEAN;
v_update_warning BOOLEAN;
v_object_version_number NUMBER;
v_effective_date DATE := SYSDATE;
v_pay_id NUMBER;
v_ext_id NUMBER;
o_comment_id NUMBER;
o_effective_start_date DATE;
o_effective_end_date DATE;
o_external_account_id NUMBER;
v_mean VARCHAR2 (100);
v_number NUMBER := 2;
v_abs NUMBER;
v_version_number NUMBER;
v_occurence NUMBER;
v_days BOOLEAN;
v_hours BOOLEAN;
v_extend BOOLEAN;
v_run BOOLEAN;
v_override BOOLEAN;
v_abs_day BOOLEAN;
v_warning BOOLEAN;
--v_number_days NUMBER;
CURSOR c_abs
IS
SELECT absence_attendance_id,
business_group_id,
absence_attendance_type_id,
abs_attendance_reason_id,
person_id,
authorising_person_id,
replacement_person_id,
period_of_incapacity_id,
absence_days,
absence_hours,
date_end,
date_notification,
date_projected_end,
date_projected_start,
date_start,
occurrence,
ssp1_issued,
time_end,
time_projected_end,
time_projected_start,
time_start,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
maternity_id,
sickness_start_date,
sickness_end_date,
pregnancy_related_illness,
reason_for_notification_delay,
accept_late_notification_flag,
linked_absence_id,
batch_id,
abs_information_category,
abs_information1,
abs_information2,
abs_information3,
abs_information4,
abs_information5,
abs_information6,
abs_information7,
abs_information8,
abs_information9,
abs_information10,
abs_information11,
abs_information12,
abs_information13,
abs_information14,
abs_information15,
abs_information16,
abs_information17,
abs_information18,
abs_information19,
abs_information20,
abs_information21,
abs_information22,
abs_information23,
abs_information24,
abs_information25,
abs_information26,
abs_information27,
abs_information28,
abs_information29,
abs_information30,
object_version_number,
comments,
absence_case_id,
approval_status,
confirmed_until,
SOURCE,
advance_pay
FROM xxper_absence_attendances
WHERE absence_attendance_id = p_pei_information1;
BEGIN
FOR i IN c_get_assignment_id
LOOP
v_assignment_id := i.assignment_id;
END LOOP;
IF p_information_type = 'QSC_SS_MOBILE_SET_REQUEST'
THEN
DECLARE
BEGIN
IF TO_NUMBER (TO_CHAR (TRUNC (SYSDATE), 'DD')) > 22
THEN
v_effective_date :=
TRUNC (ADD_MONTHS (TRUNC (SYSDATE, 'MM'), 1), 'MM');
v_effective_start_date :=
TRUNC (ADD_MONTHS (TRUNC (SYSDATE, 'MM'), 1), 'MM');
v_effective_end_date :=
LAST_DAY (TRUNC (ADD_MONTHS (TRUNC (SYSDATE), 1)));
END IF;
v_element_link_id :=
hr_entry_api.get_link (
p_assignment_id => v_assignment_id,
p_element_type_id => xxqsc_payroll.get_element_type_id (
p_business_group_id => 81,
p_element_name => 'Mobile Set
Allowance'),
p_session_date => v_effective_date);
pay_element_entry_api.create_element_entry (
p_effective_date => v_effective_date,
p_business_group_id => 81,
p_assignment_id => v_assignment_id,
p_element_link_id => v_element_link_id,
p_entry_type => 'E',
p_creator_type => 'F',
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);
/* hr_utility.set_message
(800,
'date'||v_effective_date|| v_assignment_id ||V_ELEMENT_LINK_ID||
V_ELEMENT_ENTRY_ID
);
hr_utility.raise_error;
*/
END;
END IF;
------------------------ticket allowance-------------------------------
IF p_information_type = 'QSC_SS_FIRST_FINAL_TKT_HR_REQ'
THEN
DECLARE
v_ticket_val NUMBER := 0;
v_input1 NUMBER;
v_input2 NUMBER;
BEGIN
/*hr_utility.set_message (800,p_information_type);
hr_utility.raise_error;*/
IF p_pei_information2 = 'FAMILY_JOIN'
THEN
v_element_link_id :=
hr_entry_api.get_link (
p_assignment_id => v_assignment_id,
p_element_type_id => xxqsc_payroll.get_element_type_id (
p_business_group_id => 81,
p_element_name => 'Family
Joining Ticket Payment'),
p_session_date => v_effective_date);
v_input2 :=
xxqsc_hr_wf_pkg.get_input_id (
'Family Joining Ticket Payment',
'Ticket Price'); --828;
v_input1 :=
xxqsc_hr_wf_pkg.get_input_id (
'Family Joining Ticket Payment',
'Name of Beneficiary'); --1056;
ELSIF p_pei_information2 = 'FIRST_ARRIVAL'
THEN
v_element_link_id :=
hr_entry_api.get_link (
p_assignment_id => v_assignment_id,
p_element_type_id => xxqsc_payroll.get_element_type_id (
p_business_group_id => 81,
p_element_name => 'First
Arrival Ticket Payment'),
p_session_date => v_effective_date);
v_input2 :=
xxqsc_hr_wf_pkg.get_input_id (
'First Arrival Ticket Payment',
'Ticket Price'); --828;
v_input1 :=
xxqsc_hr_wf_pkg.get_input_id (
'First Arrival Ticket Payment',
'Name of Beneficiary'); --1056;
ELSIF p_pei_information2 = 'LAST_DEPARTURE'
THEN
v_element_link_id :=
hr_entry_api.get_link (
p_assignment_id => v_assignment_id,
p_element_type_id => xxqsc_payroll.get_element_type_id (
p_business_group_id => 81,
p_element_name => 'Last
Departure Ticket Payment'),
p_session_date => v_effective_date);
v_input2 :=
xxqsc_hr_wf_pkg.get_input_id (
'Last Departure Ticket Payment',
'Ticket Price'); --828;
v_input1 :=
xxqsc_hr_wf_pkg.get_input_id (
'Last Departure Ticket Payment',
'Name of Beneficiary'); --1056;
END IF;
-- Check if the amount iss correct
IF p_pei_information4 IS NOT NULL
THEN
-- PARENT
pay_element_entry_api.create_element_entry (
p_effective_date => v_effective_date,
p_business_group_id => 81,
p_assignment_id => v_assignment_id,
p_element_link_id => v_element_link_id,
p_entry_type => 'E',
--p_input_value_id1 => 713,
p_input_value_id2 => v_input1,
p_input_value_id3 => v_input2,
--p_entry_value1 => P_PEI_INFORMATION2,
p_entry_value2 => p_pei_information3,
p_entry_value3 => p_pei_information4,
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);
END IF;
v_element_link_id :=
hr_entry_api.get_link (
p_assignment_id => v_assignment_id,
p_element_type_id => xxqsc_payroll.get_element_type_id (
p_business_group_id => 81,
p_element_name => 'Furniture
Allowance' -- 'Furniture Grant
Calculation'
),
p_session_date => v_effective_date);
pay_element_entry_api.create_element_entry (
p_effective_date => v_effective_date,
p_business_group_id => 81,
p_assignment_id => v_assignment_id,
p_element_link_id => v_element_link_id,
p_entry_type => 'E',
-- p_input_value_id1 => 274,
-- p_entry_value1 => 'No',
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);
END;
END IF;
v_element_link_id :=
hr_entry_api.get_link (
p_assignment_id => v_assignment_id,
p_element_type_id => xxqsc_payroll.get_element_type_id (
p_business_group_id => 81,
p_element_name => 'Social Loan'),
p_session_date => v_effective_date);
pay_element_entry_api.create_element_entry (
p_effective_date => v_effective_date,
p_business_group_id => 81,
p_assignment_id => v_assignment_id,
p_element_link_id => v_element_link_id,
p_entry_type => 'E',
/* p_input_value_id1 => 372,
p_input_value_id2 => 373,
p_entry_value1 => p_pei_information2,
p_entry_value2 => p_pei_information3,*/
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);
END;
END IF;
IF p_information_type = 'QSC_SS_UPDATE_PAYROLL_INFO_REQ'
THEN
FOR k IN c_pay_id (v_assignment_id)
LOOP
NULL;
v_pay_id := k.personal_payment_method_id;
--v_ext_id:=k.EXTERNAL_ACCOUNT_ID;
--v_effective_start_date:=k.EFFECTIVE_START_DATE;
--v_effective_end_date:=k.EFFECTIVE_END_DATE;
v_number := k.object_version_number;
END LOOP;
IF v_pay_id IS NULL
THEN
DECLARE
ln_method_id
pay_personal_payment_methods_f.personal_payment_method_id%TYPE;
ln_ext_acc_id pay_external_accounts.external_account_id%TYPE;
ln_obj_ver_num
pay_personal_payment_methods_f.object_version_number%TYPE;
ld_eff_start_date DATE;
ld_eff_end_date DATE;
ln_comment_id NUMBER;
v_method NUMBER;
BEGIN
/* SELECT hr.pay_personal_payment_methods_s.NEXTVAL
INTO ln_method_id
FROM DUAL;*/
/* SELECT org_payment_method_id
INTO v_method
FROM pay_org_payment_methods_f_tl
WHERE org_payment_method_name = p_pei_information4
AND LANGUAGE = USERENV ('LANG');*/
hr_personal_pay_method_api.create_personal_pay_method ( -- Input
data elements
-- ------------------------------
p_effective_date => TRUNC (SYSDATE),
p_assignment_id => v_assignment_id,
p_org_payment_method_id => 61, --v_method,
p_priority => 1 /*p_pei_information5
- 1*/
,
p_percentage => 100,
p_territory_code => 'QA',
p_segment1 => p_pei_information3,
--- bank
p_segment3 => p_pei_information4,
-- Account number
p_segment2 => p_pei_information5,
-- Branch
p_segment5 => p_pei_information6,
--- account type
p_segment4 => p_pei_information7,
-- swift
p_segment6 => p_pei_information8,
-- IBAN --Added By Ravindra(19-02-2020)
--p_attribute1 => 'Y',
-- Output data elements
-- --------------------------------
p_personal_payment_method_id => ln_method_id,
p_external_account_id => ln_ext_acc_id,
p_object_version_number => ln_obj_ver_num,
p_effective_start_date => ld_eff_start_date,
p_effective_end_date => ld_eff_end_date,
p_comment_id => ln_comment_id);
END;
ELSE
DECLARE
BEGIN
/*select meaning into v_mean from FND_LOOKUP_VALUES
Where lookup_type='QSC_PAY_BANK' and ENABLED_FLAG='Y' and LANGUAGE=
USERENV('LANG')
and lookup_code=p_PEI_INFORMATION1;
*/
hr_personal_pay_method_api.update_personal_pay_method (
p_validate => FALSE,
p_effective_date => TRUNC (SYSDATE),
p_datetrack_update_mode => 'UPDATE',
p_personal_payment_method_id => v_pay_id,
p_object_version_number => v_number,
p_comments => 'Updated by USER HOOK',
p_percentage => 100,
p_priority => 1,
p_territory_code => 'QA',
p_segment1 => p_pei_information3,
--- bank
p_segment3 => p_pei_information4,
-- Account number
p_segment2 => p_pei_information5,
-- Branch
p_segment5 => p_pei_information6,
--- account type
p_segment4 => p_pei_information7,
-- swift
p_segment6 => p_pei_information8,
-- IBAN --Added By Ravindra(19-02-2020)
p_comment_id => o_comment_id,
p_external_account_id => v_ext_id,
p_effective_start_date => v_effective_start_date,
p_effective_end_date => v_effective_end_date);
END;
END IF;
/* hr_utility.set_message
(800,
'date'||v_effective_date|| v_assignment_id ||'update'
);
hr_utility.raise_error;
*/
END IF;
CURSOR c_abs
IS
SELECT absence_attendance_id,
business_group_id,
absence_attendance_type_id,
abs_attendance_reason_id,
person_id,
authorising_person_id,
replacement_person_id,
period_of_incapacity_id,
absence_days,
absence_hours,
date_end,
date_notification,
date_projected_end,
date_projected_start,
date_start,
occurrence,
ssp1_issued,
time_end,
time_projected_end,
time_projected_start,
time_start,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
maternity_id,
sickness_start_date,
sickness_end_date,
pregnancy_related_illness,
reason_for_notification_delay,
accept_late_notification_flag,
linked_absence_id,
batch_id,
abs_information_category,
abs_information1,
abs_information2,
abs_information3,
abs_information4,
abs_information5,
abs_information6,
abs_information7,
abs_information8,
abs_information9,
abs_information10,
abs_information11,
abs_information12,
abs_information13,
abs_information14,
abs_information15,
abs_information16,
abs_information17,
abs_information18,
abs_information19,
abs_information20,
abs_information21,
abs_information22,
abs_information23,
abs_information24,
abs_information25,
abs_information26,
abs_information27,
abs_information28,
abs_information29,
abs_information30,
object_version_number,
absence_case_id,
approval_status,
confirmed_until,
SOURCE,
advance_pay
FROM xxper_absence_attendances
WHERE absence_attendance_id = p_pei_information1;
CURSOR c2
IS
SELECT paa.absence_days
FROM per_absence_attendances paa
WHERE paa.absence_attendance_id = p_pei_information1;
CURSOR c3
IS
SELECT paat.absence_attendance_type_id
FROM per_absence_attendance_types paat
WHERE paat.NAME = 'Unpaid Leave';
BEGIN
OPEN c2;
CLOSE c2;
OPEN c3;
CLOSE c3;
v_actual_days :=
/*TO_DATE
(TO_CHAR
(TO_DATE
(p_pei_information3,
'RRRR/MM/DD HH24:MI:SS'
),
'MM/DD/RRRR'
),
'MM/DD/RRRR'
) - TO_DATE
(TO_CHAR
(TO_DATE
(p_pei_information2,
'RRRR/MM/DD HH24:MI:SS'
),
'MM/DD/RRRR'
),
'MM/DD/RRRR'
)+1;*/
xxqsc_payroll.get_return_working_days (
TO_DATE (
TO_CHAR (
TO_DATE (p_pei_information2, 'RRRR/MM/DD HH24:MI:SS'),
'MM/DD/RRRR'),
'MM/DD/RRRR'),
TO_DATE (
TO_CHAR (
TO_DATE (p_pei_information3, 'RRRR/MM/DD HH24:MI:SS'),
'MM/DD/RRRR'),
'MM/DD/RRRR'));
v_extra_days := v_actual_days - v_requested_days;
FOR r1
IN (SELECT paa.object_version_number,
paa.absence_attendance_id
FROM per_absence_attendances paa
WHERE paa.absence_attendance_id = p_pei_information1)
LOOP
hr_person_absence_api.delete_person_absence (
p_validate => FALSE,
p_absence_attendance_id => r1.absence_attendance_id,
p_object_version_number => r1.object_version_number);
END LOOP;
v_balance :=
xxqsc_payroll.get_net_accrual (81,
v_assignment_id,
SYSDATE,
'Annual Leave Accrual');
-- Move attachments
UPDATE applsys.fnd_attached_documents t
SET t.pk1_value = v_abs
WHERE t.pk1_value = TO_CHAR (l.absence_attendance_id)
AND t.entity_name = 'PER_ABSENCE_ATTENDANCES';
END LOOP;
ELSIF v_extra_days > v_balance - v_actual_days
THEN
v_unpaid_days := v_actual_days - v_balance;
v_annual_days := v_balance;
-- Test Messages
/*hr_utility.set_message
(800,'- to'||TO_DATE
(TO_CHAR
(TO_DATE
(p_pei_information3,
'RRRR/MM/DD HH24:MI:SS'
),
'MM/DD/RRRR'
),
'MM/DD/RRRR'
)-v_unpaid_days);
hr_utility.raise_error;
hr_utility.set_message
(800,
v_balance||'-'||v_actual_days||'-'||v_requested_days||'-'||
v_extra_days
||'-'||v_unpaid_days||'-'||v_annual_days
);
hr_utility.raise_error;*/
-- Test Messages
FOR l IN c_abs
LOOP
IF v_annual_days > 0
THEN
hr_person_absence_api.create_person_absence (
p_validate => FALSE,
p_effective_date => v_effective_date,
p_person_id => p_person_id,
p_business_group_id => 81,
p_absence_attendance_type_id =>
l.absence_attendance_type_id,
p_abs_attendance_reason_id =>
l.abs_attendance_reason_id,
p_comments => NULL,
p_date_notification => l.date_notification,
p_date_projected_start => l.date_projected_start,
p_time_projected_start => l.time_projected_start,
p_date_projected_end => l.date_projected_start,
p_time_projected_end => l.time_projected_end,
p_date_start => TO_DATE (
TO_CHAR (
TO_DATE (
p_pei_information2,
'RRRR/MM/DD
HH24:MI:SS'),
'MM/DD/RRRR'),
'MM/DD/RRRR'),
p_time_start => l.time_start,
p_date_end => TO_DATE (
TO_CHAR (
TO_DATE (
p_pei_information3,
'RRRR/MM/DD
HH24:MI:SS'),
'MM/DD/RRRR'),
'MM/DD/RRRR')
- v_unpaid_days,
p_time_end => l.time_end,
p_absence_days => v_annual_days,
p_absence_hours => l.absence_hours,
p_authorising_person_id => l.authorising_person_id,
p_replacement_person_id => l.replacement_person_id,
p_attribute_category => '63',
--l.attribute_category,
p_attribute1 => 'Y',
p_attribute4 => l.attribute4,
p_absence_attendance_id => v_abs,
p_object_version_number => v_version_number,
p_occurrence => v_occurence,
p_dur_dys_less_warning => v_days,
p_dur_hrs_less_warning => v_hours,
p_exceeds_pto_entit_warning => v_extend,
p_exceeds_run_total_warning => v_run,
p_abs_overlap_warning => v_override,
p_abs_day_after_warning => v_abs_day,
p_dur_overwritten_warning => v_warning);
END IF;
-- unpaid
hr_person_absence_api.create_person_absence (
p_validate => FALSE,
p_effective_date => v_effective_date,
p_person_id => p_person_id,
p_business_group_id => 81,
p_absence_attendance_type_id => v_unpaid_leave_id,
p_abs_attendance_reason_id => l.abs_attendance_reason_id,
p_comments => NULL,
p_date_notification => l.date_notification,
p_date_projected_start => l.date_projected_start,
p_time_projected_start => l.time_projected_start,
p_date_projected_end => l.date_projected_start,
p_time_projected_end => l.time_projected_end,
p_date_start => TO_DATE (
TO_CHAR (
TO_DATE (
p_pei_information3,
'RRRR/MM/DD
HH24:MI:SS'),
'MM/DD/RRRR'),
'MM/DD/RRRR')
- v_unpaid_days
+ 1,
p_time_start => l.time_start,
p_date_end => TO_DATE (
TO_CHAR (
TO_DATE (
p_pei_information3,
'RRRR/MM/DD
HH24:MI:SS'),
'MM/DD/RRRR'),
'MM/DD/RRRR'),
p_time_end => l.time_end,
p_absence_days => v_unpaid_days,
p_absence_hours => l.absence_hours,
p_authorising_person_id => l.authorising_person_id,
p_replacement_person_id => l.replacement_person_id,
--p_attribute_category => l.attribute_category,
--p_attribute1 => l.attribute1,
p_absence_attendance_id => v_abs_unpaid,
--v_abs,
p_object_version_number => v_version_number,
p_occurrence => v_occurence,
p_dur_dys_less_warning => v_days,
p_dur_hrs_less_warning => v_hours,
p_exceeds_pto_entit_warning => v_extend,
p_exceeds_run_total_warning => v_run,
p_abs_overlap_warning => v_override,
p_abs_day_after_warning => v_abs_day,
p_dur_overwritten_warning => v_warning);
-- Move attachments
UPDATE applsys.fnd_attached_documents t
SET t.pk1_value = v_abs
WHERE t.pk1_value = TO_CHAR (l.absence_attendance_id)
AND t.entity_name = 'PER_ABSENCE_ATTENDANCES';
END LOOP;
END IF;
END;
END IF;
IF p_information_type = 'QSC_SS_ANNUAL_LEAVE_CANCEL_REQ'
THEN
INSERT INTO XXPER_ABS_ATTENDANCES_CANCELED (
absence_attendance_id,
business_group_id,
absence_attendance_type_id,
abs_attendance_reason_id,
person_id,
authorising_person_id,
replacement_person_id,
period_of_incapacity_id,
absence_days,
absence_hours,
date_end,
date_notification,
date_projected_end,
date_projected_start,
date_start,
occurrence,
ssp1_issued,
time_end,
time_projected_end,
time_projected_start,
time_start,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
maternity_id,
sickness_start_date,
sickness_end_date,
pregnancy_related_illness,
reason_for_notification_delay,
accept_late_notification_flag,
linked_absence_id,
batch_id,
abs_information_category,
abs_information1,
abs_information2,
abs_information3,
abs_information4,
abs_information5,
abs_information6,
abs_information7,
abs_information8,
abs_information9,
abs_information10,
abs_information11,
abs_information12,
abs_information13,
abs_information14,
abs_information15,
abs_information16,
abs_information17,
abs_information18,
abs_information19,
abs_information20,
abs_information21,
abs_information22,
abs_information23,
abs_information24,
abs_information25,
abs_information26,
abs_information27,
abs_information28,
abs_information29,
abs_information30,
object_version_number,
absence_case_id,
approval_status,
confirmed_until,
SOURCE,
advance_pay)
(SELECT absence_attendance_id,
business_group_id,
absence_attendance_type_id,
abs_attendance_reason_id,
person_id,
authorising_person_id,
replacement_person_id,
period_of_incapacity_id,
absence_days,
absence_hours,
date_end,
date_notification,
date_projected_end,
date_projected_start,
date_start,
occurrence,
ssp1_issued,
time_end,
time_projected_end,
time_projected_start,
time_start,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
maternity_id,
sickness_start_date,
sickness_end_date,
pregnancy_related_illness,
reason_for_notification_delay,
accept_late_notification_flag,
linked_absence_id,
batch_id,
abs_information_category,
abs_information1,
abs_information2,
abs_information3,
abs_information4,
abs_information5,
abs_information6,
abs_information7,
abs_information8,
abs_information9,
abs_information10,
abs_information11,
abs_information12,
abs_information13,
abs_information14,
abs_information15,
abs_information16,
abs_information17,
abs_information18,
abs_information19,
abs_information20,
abs_information21,
abs_information22,
abs_information23,
abs_information24,
abs_information25,
abs_information26,
abs_information27,
abs_information28,
abs_information29,
abs_information30,
object_version_number,
absence_case_id,
approval_status,
confirmed_until,
SOURCE,
advance_pay
FROM hr.per_absence_attendances
WHERE absence_attendance_id = p_pei_information1);
FOR r1
IN (SELECT paa.object_version_number, paa.absence_attendance_id
FROM per_absence_attendances paa
WHERE paa.absence_attendance_id = p_pei_information1)
LOOP
hr_person_absence_api.delete_person_absence (
p_validate => FALSE,
p_absence_attendance_id => r1.absence_attendance_id,
p_object_version_number => r1.object_version_number);
END LOOP;
END IF;
-----------------------------QSC_SS_DUTY_RESUMP_REQ
---------------------------------
/*IF P_INFORMATION_TYPE = 'QSC_SS_TRAVEL_RESUMP_REQ' THEN
DECLARE
V_TRIP_TYPE varchar2(100);
BEGIN
IF P_PEI_INFORMATION1 in ('RFBT','TRAINING','CONFERENCE') THEN
IF NVL(P_PEI_INFORMATION9,0) > 0 THEN
IF P_PEI_INFORMATION1= 'RFBT' THEN V_TRIP_TYPE := 'Business Trip Per Diem';
ELSIF P_PEI_INFORMATION1='TRAINING' THEN V_TRIP_TYPE := 'Training Per Diem';
ELSIF P_PEI_INFORMATION1='CONFERENCE' THEN V_TRIP_TYPE := 'Conference Seminar
and Trade Fairs Per Diem';
END IF;
V_ELEMENT_LINK_ID := HR_ENTRY_API.GET_LINK(P_ASSIGNMENT_ID =>
V_ASSIGNMENT_ID,
P_ELEMENT_TYPE_ID => XXQSC_PAYROLL.GET_ELEMENT_TYPE_ID(P_BUSINESS_GROUP_ID =>
81,
P_ELEMENT_NAME => V_TRIP_TYPE),
P_SESSION_DATE => V_EFFECTIVE_DATE);
PAY_ELEMENT_ENTRY_API.CREATE_ELEMENT_ENTRY(P_EFFECTIVE_DATE =>
V_EFFECTIVE_DATE,
P_BUSINESS_GROUP_ID => 81,
P_ASSIGNMENT_ID => V_ASSIGNMENT_ID,
P_ELEMENT_LINK_ID => V_ELEMENT_LINK_ID,
P_ENTRY_TYPE => 'E',
p_input_value_id1 => xxqsc_hr_wf_pkg.get_input_id(V_TRIP_TYPE,'Amount'
),
p_input_value_id2 => xxqsc_hr_wf_pkg.get_input_id(V_TRIP_TYPE,'
Official Travel Description'),
p_input_value_id3 => xxqsc_hr_wf_pkg.get_input_id(V_TRIP_TYPE,'
Departure Date'),
p_input_value_id4 => xxqsc_hr_wf_pkg.get_input_id(V_TRIP_TYPE,'Return
Date'),
p_input_value_id5 => xxqsc_hr_wf_pkg.get_input_id(V_TRIP_TYPE,'First
Day Of Business'),
p_input_value_id6 => xxqsc_hr_wf_pkg.get_input_id(V_TRIP_TYPE,'Last
Day Of Business'),
p_input_value_id7 => xxqsc_hr_wf_pkg.get_input_id(V_TRIP_TYPE,'Source'
),
p_entry_value1 => P_PEI_INFORMATION9,
p_entry_value2 => P_PEI_INFORMATION1,
p_entry_value3 => fnd_date.canonical_to_date(P_PEI_INFORMATION10),
p_entry_value4 => fnd_date.canonical_to_date(P_PEI_INFORMATION11),
p_entry_value5 => fnd_date.canonical_to_date(P_PEI_INFORMATION2),
p_entry_value6 => fnd_date.canonical_to_date(P_PEI_INFORMATION3),
p_entry_value7 => 'Travel Duty Resumption',
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);
END IF;
END IF;
END;
END IF; */
-----------------------------QSC_SS_TRAVEL_RESUMP_REQ
---------------------------------
IF p_information_type = 'QSC_SS_TRAVEL_RESUMP_REQ'
THEN
DECLARE
v_trip_type VARCHAR2 (100);
v_ele_date DATE;
BEGIN
/*IF SYSDATE > TO_DATE(NVL(XXQSC_HR_UTILITY2.GET_GLOBAL_VAL('
GV_CREATE_TRAVEL_ELEMENT_DAY'),'19')
||TO_CHAR(SYSDATE,'MM-YYYY'),'DD-MM-YYYY')
THEN
V_ELE_DATE := TRUNC(ADD_MONTHS(SYSDATE,1),'MONTH');
ELSE*/
v_ele_date := SYSDATE;
--END IF;
---------- changed by anas to accept minus value 23.11.2015
IF p_pei_information1 IN ('RFBT', 'TRAINING', 'CONFERENCE')
THEN
IF NVL (p_pei_information9, 0) IS NOT NULL
THEN -- NVL(P_PEI_INFORMATION9,0) > 0 THEN
IF p_pei_information1 = 'RFBT'
THEN
v_trip_type := 'Business Trip Per Diem';
ELSIF p_pei_information1 = 'TRAINING'
THEN
v_trip_type := 'Training Per Diem';
ELSIF p_pei_information1 = 'CONFERENCE'
THEN
v_trip_type :=
'Conference Seminar and Trade Fairs Per Diem';
END IF;
v_element_link_id :=
hr_entry_api.get_link (
p_assignment_id => v_assignment_id,
p_element_type_id => xxqsc_payroll.get_element_type_id (
p_business_group_id => 81,
p_element_name =>
v_trip_type),
p_session_date => v_effective_date);
pay_element_entry_api.create_element_entry (
p_effective_date => TO_CHAR (v_ele_date, 'DD-MON-
RRRR'),
p_business_group_id => 81,
p_assignment_id => v_assignment_id,
p_element_link_id => v_element_link_id,
p_entry_type => 'E',
p_input_value_id1 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Amount'),
p_input_value_id2 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Official Travel Description'),
p_input_value_id3 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Departure Date'),
p_input_value_id4 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Return Date'),
p_input_value_id5 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'First Day Of Business'),
p_input_value_id6 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Last Day Of Business'),
p_input_value_id7 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Source'),
p_entry_value1 => p_pei_information28,
p_entry_value2 => p_pei_information1,
p_entry_value3 => fnd_date.canonical_to_date (
p_pei_information10),
p_entry_value4 => fnd_date.canonical_to_date (
p_pei_information11),
p_entry_value5 => fnd_date.canonical_to_date (
p_pei_information2),
p_entry_value6 => fnd_date.canonical_to_date (
p_pei_information3),
p_entry_value7 => 'Travel Duty Self Service',
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);
END IF;
END IF;
END;
-------------------
UPDATE per_people_extra_info a
SET a.pei_information30 = 'RETURNED'
WHERE a.person_extra_info_id = p_pei_information13;
--------------------
END IF;
V_ELEMENT_LINK_ID :=
HR_ENTRY_API.GET_LINK (
P_ASSIGNMENT_ID => V_ASSIGNMENT_ID,
P_ELEMENT_TYPE_ID => XXQSC_PAYROLL.GET_ELEMENT_TYPE_ID (
P_BUSINESS_GROUP_ID => 81,
P_ELEMENT_NAME =>
V_TRIP_TYPE),
P_SESSION_DATE => V_EFFECTIVE_DATE);
PAY_ELEMENT_ENTRY_API.CREATE_ELEMENT_ENTRY (
P_EFFECTIVE_DATE => V_ELE_DATE,
P_BUSINESS_GROUP_ID => 81,
P_ASSIGNMENT_ID => V_ASSIGNMENT_ID,
P_ELEMENT_LINK_ID => V_ELEMENT_LINK_ID,
P_ENTRY_TYPE => 'E',
p_input_value_id1 => xxqsc_hr_wf_pkg.get_input_id (
V_TRIP_TYPE,
'Amount'),
p_input_value_id2 => xxqsc_hr_wf_pkg.get_input_id (
V_TRIP_TYPE,
'Official Travel Description'),
p_input_value_id3 => xxqsc_hr_wf_pkg.get_input_id (
V_TRIP_TYPE,
'Departure Date'),
p_input_value_id4 => xxqsc_hr_wf_pkg.get_input_id (
V_TRIP_TYPE,
'Return Date'),
p_input_value_id5 => xxqsc_hr_wf_pkg.get_input_id (
V_TRIP_TYPE,
'First Day Of Business'),
p_input_value_id6 => xxqsc_hr_wf_pkg.get_input_id (
V_TRIP_TYPE,
'Last Day Of Business'),
p_input_value_id7 => xxqsc_hr_wf_pkg.get_input_id (
V_TRIP_TYPE,
'Source'),
p_entry_value1 => P_PEI_INFORMATION27,
p_entry_value2 => V_P_ENTRY_VALUE2,
p_entry_value3 => fnd_date.canonical_to_date (
P_PEI_INFORMATION10),
p_entry_value4 => fnd_date.canonical_to_date (
P_PEI_INFORMATION11),
p_entry_value5 => fnd_date.canonical_to_date (
P_PEI_INFORMATION2),
p_entry_value6 => fnd_date.canonical_to_date (
P_PEI_INFORMATION3),
p_entry_value7 => 'Travel Advance Self Service',
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);
END IF;
END IF;
END;
-------------------
UPDATE PER_PEOPLE_EXTRA_INFO a
SET A.PEI_INFORMATION28 = 'PAID' --- Advanced Status Field----
WHERE A.PERSON_EXTRA_INFO_ID = P_PEI_INFORMATION13;
--------------------
END IF;
-----------------------------------------------------------------------------------
---------------------------------------------
-----------------------------QSC_SS_TRAVEL_AUTHORIZATION_REQ
---------------------------------
IF p_information_type = 'QSC_SS_TRAVEL_AUTHORIZE_REQ'
THEN
DECLARE
v_trip_type VARCHAR2 (100);
v_ele_date DATE;
BEGIN
/*IF SYSDATE > TO_DATE(NVL(XXQSC_HR_UTILITY2.GET_GLOBAL_VAL('
GV_CREATE_TRAVEL_ELEMENT_DAY'),'19')
||TO_CHAR(SYSDATE,'MM-YYYY'),'DD-MM-YYYY')
THEN
V_ELE_DATE := TRUNC(ADD_MONTHS(SYSDATE,1),'MONTH');
ELSE*/
v_ele_date := SYSDATE;
--END IF;
----------
IF p_pei_information2 IN ('BT', 'TRAINING', 'CONFERENCE')
THEN
IF NVL (p_pei_information23, 0) > 0
THEN
IF p_pei_information2 = 'BT'
THEN
v_trip_type := 'Business Trip Per Diem';
ELSIF p_pei_information2 = 'TRAINING'
THEN
v_trip_type := 'Training Per Diem';
ELSIF p_pei_information2 = 'CONFERENCE'
THEN
v_trip_type :=
'Conference Seminar and Trade Fairs Per Diem';
END IF;
v_element_link_id :=
hr_entry_api.get_link (
p_assignment_id => v_assignment_id,
p_element_type_id => xxqsc_payroll.get_element_type_id (
p_business_group_id => 81,
p_element_name =>
v_trip_type),
p_session_date => v_effective_date);
pay_element_entry_api.create_element_entry (
p_effective_date => v_ele_date,
p_business_group_id => 81,
p_assignment_id => v_assignment_id,
p_element_link_id => v_element_link_id,
p_entry_type => 'E',
p_input_value_id1 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Amount'),
p_input_value_id2 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Official Travel Description'),
p_input_value_id3 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Departure Date'),
p_input_value_id4 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Return Date'),
p_input_value_id5 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'First Day Of Business'),
p_input_value_id6 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Last Day Of Business'),
p_input_value_id7 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Source'),
p_entry_value1 => p_pei_information23,
p_entry_value2 => p_pei_information2,
p_entry_value3 => fnd_date.canonical_to_date (
p_pei_information8),
p_entry_value4 => fnd_date.canonical_to_date (
p_pei_information9),
p_entry_value5 => fnd_date.canonical_to_date (
p_pei_information10),
p_entry_value6 => fnd_date.canonical_to_date (
p_pei_information11),
p_entry_value7 => 'Travel Authorization Self
Service',
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);
END IF;
END IF;
-----------
UPDATE per_people_extra_info a
SET a.pei_information30 = 'APPROVED'
WHERE a.person_id = p_person_id
AND a.information_type = 'QSC_SS_TRAVEL_AUTHORIZE_REQ'
AND fnd_date.canonical_to_date (p_pei_information10) =
fnd_date.canonical_to_date (a.pei_information10)
AND fnd_date.canonical_to_date (p_pei_information11) =
fnd_date.canonical_to_date (a.pei_information11)
AND fnd_date.canonical_to_date (p_pei_information8) =
fnd_date.canonical_to_date (a.pei_information8)
AND fnd_date.canonical_to_date (p_pei_information9) =
fnd_date.canonical_to_date (a.pei_information9)
AND NVL (a.pei_information30, 'EMPTY') <> 'CANCELED';
END;
END IF;
-----------------------------QSC_SS_TRAVEL_CANCEL_REQ
---------------------------------
IF p_information_type = 'QSC_SS_TRAVEL_CANCEL_REQ'
THEN
DECLARE
CURSOR c1
IS
SELECT *
FROM xxqsc_travel_authorization_v
WHERE person_extra_info_id = p_pei_information1;
BEGIN
FOR r1 IN c1
LOOP
/*hr_utility.set_message (800,
XXQSC_HR_UTILITY2.get_element_process_status(v_assignment_id,
r1.Official_Travel_Description,r1.Departure_Date) );
hr_utility.raise_error;*/
IF NVL (r1.PER_DIEM_PAID_AMOUNT, r1.Per_Diem_Amount) > 0
--xxqsc_hr_utility2.get_element_process_status (
--v_assignment_id,
--r1.official_travel_description,
--r1.departure_date) = 'Y'
THEN
--------
DECLARE
v_trip_type VARCHAR2 (100);
BEGIN
IF r1.official_travel_description = 'BT'
THEN
v_trip_type := 'Business Trip Per Diem';
ELSIF r1.official_travel_description = 'TRAINING'
THEN
v_trip_type := 'Training Per Diem';
ELSIF r1.official_travel_description = 'CONFERENCE'
THEN
v_trip_type :=
'Conference Seminar and Trade Fairs Per Diem';
END IF;
v_element_link_id :=
hr_entry_api.get_link (
p_assignment_id => v_assignment_id,
p_element_type_id => xxqsc_payroll.get_element_type_id
(
p_business_group_id => 81,
p_element_name =>
v_trip_type),
p_session_date => v_effective_date);
pay_element_entry_api.create_element_entry (
p_effective_date => v_effective_date,
p_business_group_id => 81,
p_assignment_id => v_assignment_id,
p_element_link_id => v_element_link_id,
p_entry_type => 'E',
p_input_value_id1 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Amount'),
p_input_value_id2 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Official Travel
Description'),
p_input_value_id3 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Departure Date'),
p_input_value_id4 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Return Date'),
p_input_value_id5 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'First Day Of Business'),
p_input_value_id6 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Last Day Of Business'),
p_input_value_id7 => xxqsc_hr_wf_pkg.get_input_id (
v_trip_type,
'Source'),
p_entry_value1 => NVL (
r1.PER_DIEM_PAID_AMOUNT,
r1.Per_Diem_Amount)
* -1,
p_entry_value2 =>
r1.official_travel_description,
p_entry_value3 => fnd_date.canonical_to_date (
r1.departure_date),
p_entry_value4 => fnd_date.canonical_to_date (
r1.return_date),
p_entry_value5 => fnd_date.canonical_to_date (
r1.first_day_of_business),
p_entry_value6 => fnd_date.canonical_to_date (
r1.last_day_of_business),
p_entry_value7 => 'Canceled Self Service TA',
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);
END;
-------
ELSIF xxqsc_hr_utility2.get_element_process_status (
v_assignment_id,
r1.official_travel_description,
r1.departure_date) = 'N'
THEN
DECLARE
v_start DATE;
v_end DATE;
v_del_w BOOLEAN;
v_entry_id NUMBER
:= xxqsc_hr_utility2.get_element_entry_id (
v_assignment_id,
r1.official_travel_description,
r1.departure_date);
v_ovn NUMBER;
BEGIN
--- get ovn number;
BEGIN
SELECT c.object_version_number
INTO v_ovn
FROM pay_element_entries_f c
WHERE c.element_entry_id = v_entry_id;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
UPDATE per_people_extra_info a
SET a.pei_information30 = 'CANCELED'
WHERE a.person_extra_info_id = p_pei_information1;
END;
END IF;
IF v_ticket_val > 0
THEN
pay_element_entry_api.create_element_entry (
p_effective_date => v_effective_date,
p_business_group_id => 81,
p_assignment_id => v_assignment_id,
p_element_link_id => v_element_link_id,
p_entry_type => 'E',
p_input_value_id1 => v_input1,
p_input_value_id2 => v_input2,
p_input_value_id3 => v_input3,
p_input_value_id4 => v_input4,
p_input_value_id5 => v_input5,
p_input_value_id6 => v_input6,
p_input_value_id7 => v_input7,
p_entry_value1 => v_ticket_val,
p_entry_value2 => p_pei_information2,
p_entry_value3 => p_pei_information4,
p_entry_value4 => p_pei_information6,
p_entry_value5 => p_pei_information8,
p_entry_value6 => p_pei_information10,
p_entry_value7 => p_pei_information12,
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);
END IF;
END;
END IF;
v_input9 :=
xxqsc_hr_wf_pkg.get_input_id ('Long Term Incentive', 'Note');
v_input10 :=
xxqsc_hr_wf_pkg.get_input_id ('Long Term Incentive', 'Source');
v_element_link_id :=
hr_entry_api.get_link (
p_assignment_id => v_assignment_id,
p_element_type_id => xxqsc_payroll.get_element_type_id (
p_business_group_id => 81,
p_element_name => 'Long Term
Incentive'),
p_session_date => v_effective_date);
pay_element_entry_api.create_element_entry (
p_effective_date => v_effective_date,
p_business_group_id => 81,
p_assignment_id => v_assignment_id,
p_element_link_id => v_element_link_id,
p_entry_type => 'E',
p_input_value_id1 => v_input1,
p_input_value_id2 => v_input2,
p_input_value_id3 => v_input3,
p_input_value_id4 => v_input4,
p_input_value_id5 => v_input5,
p_input_value_id6 => v_input6,
p_input_value_id7 => v_input7,
p_input_value_id8 => v_input8,
p_input_value_id9 => v_input9,
p_input_value_id10 => v_input10,
p_entry_value1 => NULL,
p_entry_value2 => NULL,
p_entry_value3 => (fnd_date.canonical_to_date (
p_pei_information6)),
p_entry_value4 => p_pei_information2,
p_entry_value5 => p_pei_information3,
p_entry_value6 => p_pei_information4,
p_entry_value7 => p_pei_information7,
p_entry_value8 => TO_CHAR (SYSDATE, 'MM-RRRR'),
p_entry_value9 => p_pei_information5,
p_entry_value10 => 'SSHR Long Term Incentive Request',
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);
END;
END IF;
v_element_link_id :=
hr_entry_api.get_link (p_assignment_id => v_assignment_id,
p_element_type_id => 174
/*xxqsc_payroll.get_element_type_id (
p_business_group_id
=> 81,
p_element_name
=> 'Acting Allowance')*/
,
p_session_date => v_effective_date);
pay_element_entry_api.create_element_entry (
p_effective_date => v_effective_date,
p_business_group_id => 81,
p_assignment_id => v_assignment_id,
p_element_link_id => v_element_link_id,
p_entry_type => 'E',
p_input_value_id1 => v_input1,
p_input_value_id2 => v_input2,
p_input_value_id3 => v_input3,
p_input_value_id4 => v_input4,
p_input_value_id5 => v_input5,
p_entry_value1 => 'BEHALF',
p_entry_value2 => fnd_date.canonical_to_date (
p_PEI_INFORMATION2),
p_entry_value3 => fnd_date.canonical_to_date (
p_PEI_INFORMATION3),
p_entry_value4 => XXCONTRACT_Pkg.Get_Employee_Name_EC (
p_PEI_INFORMATION5,
'US'),
p_entry_value5 => p_PEI_INFORMATION29,
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);
END;
END IF;
END;
CURSOR leaves (
p_selected_leave VARCHAR2)
IS
SELECT TRIM (meaning) Leave_Type
FROM fnd_lookup_values
WHERE lookup_type = 'XXQSC_LEAVE_ATTACH_REQ'
AND language = 'US'
AND TRIM (meaning) = p_selected_leave;
BEGIN
v_nat := xxqsc_hr_wf_pkg.get_employee_nationality (v_bg, p_person_id);