Assignment Set and Assignment Set Values API
/* API TO Create Assignment Set */
DECLARE
l_payroll_id NUMBER := NULL;
l_assignment_set_name VARCHAR2 (500) :=
'KING_SALMAN_BONUS_RIYADH';
l_assignment_set_id NUMBER := NULL;
BEGIN
-- Get Payroll ID
BEGIN
SELECT payroll_id
INTO l_payroll_id
FROM pay_all_payrolls_f
WHERE payroll_name = 'Monthly Payroll'
AND TRUNC (SYSDATE)
BETWEEN effective_start_date
AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_payroll_id := NULL;
END;
BEGIN
hr_jp_ast_utility_pkg.create_asg_set
(
p_assignment_set_name =>
l_assignment_set_name,
p_business_group_id => 40,
p_payroll_id => l_payroll_id,
p_assignment_set_id => l_assignment_set_id);
COMMIT;
DBMS_OUTPUT.put_line (
l_assignment_set_id || '
has been Created Successfully !!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
('Inner Exception: ' || SQLERRM);
END;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main
Exception: ' || SQLERRM);
END;
/* API TO Insert Eligible Employees into Assignment
Set */
DECLARE
l_assignment_set_id NUMBER := 0;
l_assignment_id NUMBER := 0;
l_rowid VARCHAR2 (100) := NULL;
l_assignment_set_ctr NUMBER := 0;
CURSOR ELIGIBLE_EMPLOYEE
IS
SELECT pasf.assignment_id,
pgd.segment3,
pgd.segment5,
pgd.segment4,
1,
DECODE
(ffvtl.description,
'Flexible
50%', 0.5,
'Flexible
75%', 0.75,
'Saudi', 1,
1)
FROM
apps.per_all_assignments_f pasf,
apps.per_all_people_f
papf,
apps.pay_people_groups ppg,
apps.fnd_flex_value_sets ffvs,
apps.fnd_flex_values
ffv,
apps.fnd_flex_values_tl
ffvtl,
apps.hr_lookups hl,
apps.per_jobs pj,
apps.per_grades pg,
apps.per_grade_definitions pgd,
apps.per_job_definitions pjd,
apps.per_periods_of_service ppos
WHERE
pasf.person_id = papf.person_id
AND '29-JAN-2015'
BETWEEN pasf.effective_start_date
AND pasf.effective_end_date
AND '29-JAN-2015'
BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND pasf.primary_flag
= 'Y'
AND
pasf.assignment_type = 'E'
AND
papf.person_type_id IN (66, 67)
AND pasf.payroll_id
IS NOT NULL
AND pasf.payroll_id =
62
AND pj.job_id =
pasf.job_id
AND pg.grade_id =
pasf.grade_id
AND
pgd.grade_definition_id = pg.grade_definition_id
AND
pjd.job_definition_id = pj.job_definition_id
AND ppos.period_of_service_id
= pasf.period_of_service_id
AND
ppg.people_group_id = pasf.people_group_id
AND
ffvs.flex_value_set_name = 'CONTRACT_TYPE_D'
AND
ffv.flex_value_set_id = ffvs.flex_value_set_id
AND
ffvtl.flex_value_id = ffv.flex_value_id
AND ffvtl.LANGUAGE =
'US'
AND ppg.segment2 =
ffv.flex_value
AND hl.lookup_type(+)
= 'NATIONALITY'
AND hl.lookup_code(+)
= papf.nationality
AND (
ffvtl.description NOT IN
('Locum',
'Employee on secondment (From the Hospital)')
AND hl.meaning =
'Saudi Arabia'
AND ( ppos.actual_termination_date IS NULL
OR
ppos.actual_termination_date > '29-JAN-2015'))
-- CHECK IF EMPLOYEE
HAS TAKEN UNPAID LEAVE DURING THIS PERIOD THEN IT IS NOT ELIGIBLE
AND PAPF.PERSON_ID
NOT IN
(SELECT
PAPF.PERSON_ID
FROM
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_ABSENCE_ATTENDANCES PAA,
PER_ABSENCE_ATTENDANCE_TYPES PAAT
WHERE PAPF.PERSON_ID = PAAF.PERSON_ID
AND
PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
AND
TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND
TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND
PAAF.PRIMARY_FLAG = 'Y'
AND
PAAF.ASSIGNMENT_TYPE = 'E'
AND
PAAF.PAYROLL_ID = 44
AND
PAAF.ASSIGNMENT_STATUS_TYPE_ID IN (1, 2)
AND
PAPF.PERSON_ID = PAA.PERSON_ID
AND
PAA.ABSENCE_ATTENDANCE_TYPE_ID =
PAAT.ABSENCE_ATTENDANCE_TYPE_ID
AND
PAAT.NAME = 'Unpaid / Unauthorized Leave'
AND
( (
PAA.DATE_START <=
TO_DATE ('21-JAN-2015')
AND PAA.DATE_END >=
TO_DATE ('19-FEB-2015'))
OR ( PAA.DATE_START BETWEEN
TO_DATE (
'21-JAN-2015')
AND TO_DATE (
'19-FEB-2015')
OR PAA.DATE_END
BETWEEN TO_DATE (
'21-JAN-2015')
AND TO_DATE (
'19-FEB-2015')))
UNION
SELECT
PAPF.PERSON_ID
FROM
hr_api_transactions trn,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF
WHERE transaction_ref_table =
'PER_ABSENCE_ATTENDANCES'
AND
selected_person_id = PAPF.PERSON_ID
AND
trn.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND
PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
AND
PAAF.PRIMARY_FLAG = 'Y'
AND
PAAF.ASSIGNMENT_TYPE = 'E'
AND
PAAF.PAYROLL_ID = 44
AND
PAAF.ASSIGNMENT_STATUS_TYPE_ID IN (1, 2)
AND
TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN
PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND
status = 'Y'
AND
transaction_identifier = 'ABSENCES'
AND
apps.ameutil_pkg.get_trn_attr_value_f (
trn.transaction_id,
'AttributeCategory') =
'Unpaid / Unauthorized Leave'
AND
( (
TO_DATE (
apps.ameutil_pkg.get_trn_attr_value_f (
trn.transaction_id,
'DateStart'),
'RRRR-MM-DD') <=
TO_DATE ('21-JAN-2015')
AND TO_DATE (
apps.ameutil_pkg.get_trn_attr_value_f (
trn.transaction_id,
'DateEnd'),
'RRRR-MM-DD') >=
TO_DATE ('19-FEB-2015'))
OR ( TO_DATE (
apps.ameutil_pkg.get_trn_attr_value_f (
trn.transaction_id,
'DateStart'),
'RRRR-MM-DD') BETWEEN TO_DATE (
'21-JAN-2015')
AND TO_DATE
(
'19-FEB-2015')
OR TO_DATE (
apps.ameutil_pkg.get_trn_attr_value_f (
trn.transaction_id,
'DateEnd'),
'RRRR-MM-DD') BETWEEN TO_DATE (
'21-JAN-2015')
AND TO_DATE (
'19-FEB-2015'))))
-- CHECK IF EMPLOYEE
HAS TAKEN Accompany Leave DURING THIS PERIOD THEN IT IS NOT ELIGIBLE
AND PAPF.PERSON_ID
NOT IN
(SELECT
PAPF.PERSON_ID
FROM
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_ABSENCE_ATTENDANCES PAA,
PER_ABSENCE_ATTENDANCE_TYPES PAAT
WHERE PAPF.PERSON_ID = PAAF.PERSON_ID
AND
PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
AND TRUNC
(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND
TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND
PAAF.PRIMARY_FLAG = 'Y'
AND
PAAF.ASSIGNMENT_TYPE = 'E'
AND
PAAF.ASSIGNMENT_STATUS_TYPE_ID IN (1, 2)
AND
PAPF.PERSON_ID = PAA.PERSON_ID
AND
PAAF.PAYROLL_ID = 44
AND
PAA.ABSENCE_ATTENDANCE_TYPE_ID =
PAAT.ABSENCE_ATTENDANCE_TYPE_ID
AND PAAT.NAME = 'Accompany
Leave - Paid'
AND
( (
PAA.DATE_START <=
TO_DATE ('21-JAN-2015')
AND PAA.DATE_END >=
TO_DATE
('19-FEB-2015'))
OR ( PAA.DATE_START BETWEEN
TO_DATE (
'21-JAN-2015')
AND TO_DATE (
'19-FEB-2015')
OR PAA.DATE_END BETWEEN TO_DATE (
'21-JAN-2015')
AND TO_DATE (
'19-FEB-2015')))
UNION
SELECT
PAPF.PERSON_ID
FROM hr_api_transactions
trn,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF
WHERE transaction_ref_table =
'PER_ABSENCE_ATTENDANCES'
AND selected_person_id =
PAPF.PERSON_ID
AND
trn.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND
PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
AND
PAAF.PRIMARY_FLAG = 'Y'
AND
PAAF.ASSIGNMENT_TYPE = 'E'
AND
PAAF.PAYROLL_ID = 44
AND
PAAF.ASSIGNMENT_STATUS_TYPE_ID IN (1, 2)
AND
TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND
TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND status = 'Y'
AND
transaction_identifier = 'ABSENCES'
AND
apps.ameutil_pkg.get_trn_attr_value_f (
trn.transaction_id,
'AttributeCategory')
=
'Accompany Leave - Paid'
AND
( (
TO_DATE (
apps.ameutil_pkg.get_trn_attr_value_f (
trn.transaction_id,
'DateStart'),
'RRRR-MM-DD') <=
TO_DATE ('21-JAN-2015')
AND TO_DATE
(
apps.ameutil_pkg.get_trn_attr_value_f (
trn.transaction_id,
'DateEnd'),
'RRRR-MM-DD') >=
TO_DATE ('19-FEB-2015'))
OR ( TO_DATE (
apps.ameutil_pkg.get_trn_attr_value_f (
trn.transaction_id,
'DateStart'),
'RRRR-MM-DD') BETWEEN TO_DATE (
'21-JAN-2015')
AND TO_DATE (
'19-FEB-2015')
OR TO_DATE (
apps.ameutil_pkg.get_trn_attr_value_f (
trn.transaction_id,
'DateEnd'),
'RRRR-MM-DD')
BETWEEN TO_DATE (
'21-JAN-2015')
AND TO_DATE (
'19-FEB-2015'))));
BEGIN
l_assignment_set_id := 0;
SELECT assignment_set_id
INTO l_assignment_set_id
FROM hr_assignment_sets aa
WHERE aa.assignment_set_name =
'BONUS_RIYADH';
FOR i IN ELIGIBLE_EMPLOYEE
LOOP
l_rowid := NULL;
l_assignment_set_ctr := 0;
SELECT COUNT (*)
INTO l_assignment_set_ctr
FROM
hr_assignment_set_amendments aa
WHERE aa.assignment_set_id = l_assignment_set_id
AND aa.assignment_id
= i.assignment_id;
IF l_assignment_set_ctr = 0
THEN
BEGIN
hr_assignment_set_amds_pkg.insert_row (l_rowid,
i.assignment_id,
l_assignment_set_id,
'I');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (
'ERR DURING
INCLUDE ASSIGNMENT= '
||
I.ASSIGNMENT_ID
|| ' IS '
|| SQLERRM);
END;
END IF;
END LOOP;
COMMIT;
END;
/
No comments:
Post a Comment