SRAVAN ORACLE APPS HRMS: 2015

Monday, October 5, 2015

To delete pending leaves

To delete pending leaves
As far as i know there is no API to delete pending leaves.

Solution1:
This is my own way of deleting the leaves

CREATE OR REPLACE PROCEDURE xx_delete_pending_leaves
AS
CURSOR c1
IS
SELECT hat.transaction_id, hats.transaction_step_id
FROM hr_api_transactions hat, hr_api_transaction_steps hats
WHERE hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
AND hat.transaction_group = 'ABSENCE_MGMT'
AND hat.transaction_identifier = 'ABSENCES'
AND hat.transaction_ref_id IS NOT NULL
AND hat.status = 'Y'
AND hat.transaction_id = hats.transaction_id;
--For a particular employee/pass the person_id values as a cursor parameter
--AND hat.CREATOR_PERSON_ID = 11923;
--AND hats.creator_person_id = p_person_id; -- Add date conditon later and person ID if required

l_transaction_id NUMBER;
l_transaction_step_id NUMBER;
l_error VARCHAR2 (300);
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO l_transaction_id, l_transaction_step_id;
EXIT WHEN c1%NOTFOUND;
IF l_transaction_step_id IS NOT NULL
THEN
DELETE FROM hr_api_transaction_values
WHERE transaction_step_id = l_transaction_step_id;
DBMS_OUTPUT.put_line
( 'Deleted Transaction Value of transaction step id: '
|| l_transaction_step_id
);
END IF;

IF l_transaction_id IS NOT NULL
THEN
DELETE FROM hr_api_transaction_steps
WHERE transaction_id = l_transaction_id;
DBMS_OUTPUT.put_line
( 'Deleted Transaction step of transaction id: '
|| l_transaction_id
);

DELETE FROM hr_api_transactions
WHERE transaction_id = l_transaction_id;

DBMS_OUTPUT.put_line ('Deleted Transaction ID: ' || l_transaction_id);
END IF;
END LOOP;

CLOSE c1;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error := SUBSTR (SQLERRM, 1, 200);
DBMS_OUTPUT.put_line ( 'Other Error in xx_delete_pending_leaves- '
|| l_error
);
--p_out := 'Other Error in xx_delete_pending_leaves- '||l_error; -- Removed OUT parameter
-- Write FND_LOG for concurrent program log
END xx_delete_pending_leaves;

Solution 2:
We can enable/disable the update/delete icon in Absence Management - HR self service responsibility. How ?
Solution: Get query from jsp page (OAF) (See Standard Query below) and change the decode value of cancel_icon, Confirm_icon and update_icon values by passing 'HrCancelEnabled/HrCancelDisabled' OR 'HrUpdateEnabled/HrUpdateDisabled' values and replace with the custom query.

Tables Used in Standard Query are:
per_absence_attendances,
per_absence_attendance_types,
per_abs_attendance_types_tl,
hr_lookups,
hr_api_transactions,
hr_api_transaction_steps

Standard Query:

SELECT NVL(paa.date_start,paa.date_projected_start) start_date
,NVL(paa.date_end,paa.date_projected_end) end_date
,paattl.name absence_type
,paat.absence_attendance_type_id absence_attendance_type_id
,fcl.meaning absence_category
,paat.absence_category absence_category_code
,paa.absence_hours
,(SELECT meaning from hr_lookups where 'A' = lookup_code(+) and 'LEAVE_STATUS' = lookup_type(+))
approval_status
, 'A' approval_status_code
,decode(paa.date_start,null,
(SELECT meaning from fnd_lookup_values
where lookup_type ='ABSENCE_STATUS' and lookup_code ='PLANNED'
and language = userenv('LANG')),
(SELECT meaning from fnd_lookup_values
where lookup_type ='ABSENCE_STATUS' and lookup_code ='CONFIRMED'
and language = userenv('LANG')))
absence_status
, decode(paa.date_start,null,'PLANNED','CONFIRMED') absence_status_code
,(nvl((SELECT 'Y'
from fnd_attached_documents
where entity_name='PER_ABSENCE_ATTENDANCES'
and pk1_value = to_char(paa.absence_attendance_id) and rownum = 1),'N')) supporting_documents
,decode (
paa.date_start,null,
decode(paa.date_projected_start, null, 'HrCancelDisabled', 'HrCancelEnabled'),
decode(
sign(trunc(paa.date_start)-trunc(sysdate)),1,'HrCancelEnabled',
decode(paa.date_end,null,'HrCancelEnabled','HrCancelDisabled')
)
)
cancel_icon
,decode(paa.date_end,NULL,
decode(paa.date_start,Null, 'HrConfirmEnabled','HrConfirmDisabled'), 'HrConfirmDisabled' )
confirm_icon
,decode ( paa.date_start,null,
'HrUpdateEnabled',
decode(sign(nvl(paa.date_end,sysdate+1)-sysdate),1,'HrUpdateEnabled','HrUpdateDisabled'))
update_icon
,NULL
details_icon
,paa.absence_attendance_id
,null transaction_id
,to_char(paa.absence_attendance_id) supportingDocKey
,paa.absence_days
FROM per_absence_attendances paa
,per_absence_attendance_types paat
,per_abs_attendance_types_tl paattl
,hr_lookups fcl
WHERE 1=1
and paa.person_id = :1
and paa.business_group_id+0 = :2
and paa.absence_attendance_type_id = paat.absence_attendance_type_id
and paat.absence_attendance_type_id = paattl.absence_attendance_type_id
and paattl.language = userenv('LANG')
and fcl.lookup_type(+) = 'ABSENCE_CATEGORY'
and paat.absence_category = fcl.lookup_code(+)
and ((hr_api.return_legislation_code(paat.business_group_id) = 'GB'
and nvl(paat.absence_category,'#') not in
('M','GB_PAT_ADO','GB_PAT_BIRTH','GB_ADO'))
or
(hr_api.return_legislation_code(paat.business_group_id) <> 'GB'
and nvl(paat.absence_category,'#') not in
('GB_PAT_ADO','GB_PAT_BIRTH','GB_ADO')))
and not exists
( select 'e'
from hr_api_transactions t
WHERE
t.selected_person_id = paa.person_id
and t.CREATOR_PERSON_ID = nvl(:3,t.CREATOR_PERSON_ID)
and t.transaction_ref_table='PER_ABSENCE_ATTENDANCES'
and t.transaction_ref_id = paa.absence_attendance_id
and not(hr_absutil_ss.getabsencetype(t.transaction_id, NULL) IS NULL
and t.status = 'W')
and t.status not in ('D','E')
)
union all
select hr_absutil_ss.getStartDate(hat.transaction_id,null) start_date
,hr_absutil_ss.getEndDate(hat.transaction_id,null) end_date
,hr_absutil_ss.getAbsenceType(hat.transaction_id,null) absence_type
, to_number(hats.Information5) absence_attendance_type_id
,hr_absutil_ss.getAbsenceCategory(hat.transaction_id,null) absence_category
, hats.Information6 absence_category_code
,hr_absutil_ss.getAbsenceHoursDuration(hat.transaction_id,null) absence_hours
,hr_absutil_ss.getApprovalStatus(hat.transaction_id,null) approval_status
,hr_absutil_ss.getApprovalStatusCode(hat.transaction_id,null) approval_status_code
,hr_absutil_ss.getAbsenceStatus(hat.transaction_id,null) absence_status
, hats.Information9 absence_status_code
,hr_absutil_ss.hasSupportingDocuments(hat.transaction_id,hat.TRANSACTION_REF_ID) supporting_documents
,hr_absutil_ss.isCancelAllowed(hat.transaction_id,null,hat.status) cancel_icon
,hr_absutil_ss.isConfirmAllowed(hat.transaction_id,null) confirm_icon
,hr_absutil_ss.isUpdateAllowed(hat.transaction_id,null,hat.status) update_icon
,null details_icon
,hat.TRANSACTION_REF_ID absence_attendance_id
,hat.transaction_id transaction_id
,to_char(hat.TRANSACTION_REF_ID||'_'||hat.transaction_id) supportingDocKey
,hr_absutil_ss.getAbsenceDaysDuration(hat.transaction_id,null) absence_days
from hr_api_transactions hat
,hr_api_transaction_steps hats
where hat.TRANSACTION_REF_TABLE='PER_ABSENCE_ATTENDANCES'
and hat.TRANSACTION_GROUP='ABSENCE_MGMT'
and hat.TRANSACTION_IDENTIFIER='ABSENCES'
and hat.TRANSACTION_REF_ID is not null
--and hat.SELECTED_PERSON_ID =:4
--and hat.CREATOR_PERSON_ID = nvl(:5,hat.CREATOR_PERSON_ID)
and hat.transaction_id=hats.transaction_id(+)
and hat.status not in ('D','E')
and not (hr_absutil_ss.getabsencetype(hat.transaction_id,null) is null and hat.status='W')

To get pending leaves

SELECT hat.transaction_id, hats.transaction_step_id
FROM hr_api_transactions hat, hr_api_transaction_steps hats
WHERE hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
AND hat.transaction_group = 'ABSENCE_MGMT'
AND hat.transaction_identifier = 'ABSENCES'
AND hat.transaction_ref_id IS NOT NULL
AND hat.status = 'Y'
AND hat.transaction_id = hats.transaction_id
AND hat.CREATOR_PERSON_ID = :p_person_id;

To get approved leaves

SELECT pat.NAME absence_type, paa.date_start leave_start_date,
paa.date_end leave_end_date, paa.absence_days, paa.abs_information_category,
paa.abs_information2, paa.abs_information3,paa.abs_information4
FROM per_absence_attendances paa,
per_absence_attendance_types pat,
per_all_people_f papf
WHERE paa.absence_attendance_type_id =
pat.absence_attendance_type_id
AND papf.person_id = paa.person_id
AND TRUNC (paa.date_start) BETWEEN TRUNC
(papf.effective_start_date)
AND TRUNC (papf.effective_end_date)
AND TRUNC (p_date) BETWEEN TRUNC (paa.date_start)
AND TRUNC (NVL (paa.date_end,
paa.date_start
)
)
AND papf.person_id = :p_person_id;

Friday, July 24, 2015

Oracle HRMS Query to Get Latest Basic Salary Change Date

SELECT ppps.proposed_salary_n from per_pay_proposals ppps
where paaf.assignment_id = ppps.assignment_id
AND ppps.last_change_date is not null
and ppps.change_date = (SELECT TO_CHAR(max(ppp.change_date),'DD-MON-RRRR') as change_effective_date
from per_pay_proposals ppp
where 1=1
and paaf.assignment_id = ppp.assignment_id

AND last_change_date is not null )

Oracle HRMS Query for Grade and Salary History

Oracle HRMS Query to Get Previous Grade

SELECT
pg_old.name
from apps.per_all_people_f papf,
apps.per_all_assignments_f paaf_old,
apps.per_grades pg_old,
apps.per_all_assignments_f paaf_new,
apps.per_grades pg_new
where 1=1
and papf.person_id = paaf_old.person_id
and papf.person_id = paaf_new.person_id
and paaf_old.person_id = paaf_new.person_id
and paaf_old.assignment_type = 'E'
and paaf_old.primary_flag = 'Y'
and paaf_new.assignment_type = 'E'
and paaf_new.primary_flag = 'Y'
and pg_old.grade_id = paaf_old.grade_id
and pg_new.grade_id = paaf_new.grade_id
and paaf_old.grade_id <> paaf_new.grade_id
and trunc(paaf_old.effective_end_date)+1 =trunc(paaf_new.effective_start_date)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(paaf_new.effective_start_date) between paaf_new.effective_start_date and paaf_new.effective_end_date
--and papf.person_id = 162
and papf.person_id = paaf.person_id
and paaf_new.effective_start_date =(select
((to_char(max(paaf_new.effective_start_date),'DD-MON-RRRR')))
from apps.per_all_people_f papf,
apps.per_all_assignments_f paaf_old,
apps.per_grades pg_old,
apps.per_all_assignments_f paaf_new,
apps.per_grades pg_new
where 1=1
and papf.person_id = paaf_old.person_id
and papf.person_id = paaf_new.person_id
and paaf_old.person_id = paaf_new.person_id
and paaf_old.assignment_type = 'E'
and paaf_old.primary_flag = 'Y'
and paaf_new.assignment_type = 'E'
and paaf_new.primary_flag = 'Y'
and pg_old.grade_id = paaf_old.grade_id
and pg_new.grade_id = paaf_new.grade_id
and paaf_old.grade_id <> paaf_new.grade_id
and trunc(paaf_old.effective_end_date)+1 =trunc(paaf_new.effective_start_date)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(paaf_new.effective_start_date) between paaf_new.effective_start_date and paaf_new.effective_end_date
--and papf.person_id = 162
and papf.person_id = paaf.person_id )

Terminate Employee Oracle EBS (Calling API)

Employee data could be terminated from back end using oracle HRMS API (HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP).

Declare
CURSOR c_emp_cur
IS
SELECT ppos.period_of_service_id, ppos.object_version_number,
papf.person_type_id, yte.termination_date end_date, yte.ID
FROM per_all_people_f papf,
per_periods_of_service ppos,
ynppo_temp_employee yte
WHERE papf.person_id = ppos.person_id
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND SYSDATE BETWEEN ppos.date_start
AND COALESCE (ppos.projected_termination_date,
actual_termination_date,
SYSDATE
)
AND yte.employee_code = papf.employee_number
AND yte.flag = 'T'
AND yte.status IS NULL;

l_validate BOOLEAN := FALSE;
l_period_of_service_id NUMBER;
l_object_version_number NUMBER;
l_actual_notice_period_date DATE;
l_effective_date DATE;
l_supervisor_warning BOOLEAN;
l_event_warning BOOLEAN;
l_interview_warning BOOLEAN;
l_review_warning BOOLEAN;
l_recruiter_warning BOOLEAN;
l_asg_future_changes_warning BOOLEAN;
l_f_asg_future_changes_warning BOOLEAN;
l_pay_proposal_warning BOOLEAN;
l_dod_warning BOOLEAN;
l_final_process_date DATE;
l_org_now_no_manager_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (255);
l_f_entries_changed_warning VARCHAR2 (255);
l_alu_change_warning VARCHAR2 (255);
l_person_type_id NUMBER;
l_last_std_process_date_out DATE;
BEGIN
FOR c_emp_rec IN c_emp_cur
LOOP
l_period_of_service_id := c_emp_rec.period_of_service_id;
l_object_version_number := c_emp_rec.object_version_number;
l_actual_notice_period_date := c_emp_rec.end_date;
l_effective_date := c_emp_rec.end_date;
l_person_type_id := c_emp_rec.person_type_id;

BEGIN
hr_ex_employee_api.actual_termination_emp
(p_validate => l_validate,
p_effective_date => l_effective_date,
p_period_of_service_id => l_period_of_service_id,
p_object_version_number => l_object_version_number,
p_actual_termination_date => l_actual_notice_period_date,
p_last_standard_process_date => l_actual_notice_period_date,
--p_person_type_id => l_person_type_id,
--,p_leaving_reason => 'RESS'
p_last_std_process_date_out => l_last_std_process_date_out,
p_supervisor_warning => l_supervisor_warning,
p_event_warning => l_event_warning,
p_interview_warning => l_interview_warning,
p_review_warning => l_review_warning,
p_recruiter_warning => l_recruiter_warning,
p_asg_future_changes_warning => l_asg_future_changes_warning,
p_entries_changed_warning => l_entries_changed_warning,
p_pay_proposal_warning => l_pay_proposal_warning,
p_dod_warning => l_dod_warning,
p_alu_change_warning => l_alu_change_warning
);

UPDATE ynppo_temp_employee
SET status = 'Terminated'
WHERE ID = c_emp_rec.ID;

EXCEPTION
WHEN OTHERS
THEN
UPDATE ynppo_temp_employee
SET status = 'Not Terminated'
WHERE ID = c_emp_rec.ID;
dbms_output.put_line(SQLERRM);
END;
END LOOP;
END;

The YNPPO_TEMP_EMPLOYEE is staging table to store employee data which will be terminated using API.
Diposkan oleh Imam Tri Harsono di 22:40
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest

Label: Human Resource API
Reaksi:
Update Employee Oracle EBS (Calling API)

Employee data could be inserted from back end using oracle HRMS API (HR_PERSON_API.UPDATE_PERSON).

Declare
CURSOR c_emp
IS
SELECT *
FROM ynppo_temp_employee yte
WHERE yte.status IS NULL AND yte.flag = 'U';

ln_object_version_number per_all_people_f.object_version_number%TYPE;
lc_dt_ud_mode VARCHAR2 (100) := NULL;
ln_assignment_id per_all_assignments_f.assignment_id%TYPE;
lc_employee_number per_all_people_f.employee_number%TYPE;

lb_correction BOOLEAN;
lb_update BOOLEAN;
lb_update_override BOOLEAN;
lb_update_change_insert BOOLEAN;

ld_effective_start_date DATE;
ld_effective_end_date DATE;
lc_full_name per_all_people_f.full_name%TYPE;
ln_comment_id per_all_people_f.comment_id%TYPE;
lb_name_combination_warning BOOLEAN;
lb_assign_payroll_warning BOOLEAN;
lb_orig_hire_warning BOOLEAN;
v_person_id NUMBER;
v_object_version_number NUMBER;
v_effective_start_date DATE;
v_assignment_id NUMBER;
BEGIN
FOR r_emp IN c_emp
LOOP
lc_employee_number := r_emp.employee_code;

BEGIN
SELECT papf.person_id, papf.object_version_number,
papf.effective_start_date, paaf.assignment_id
INTO v_person_id, v_object_version_number,
v_effective_start_date, v_assignment_id
FROM per_all_people_f papf, per_all_assignments_f paaf
WHERE papf.employee_number = r_emp.employee_code
AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.effective_start_date)
AND TRUNC (papf.effective_end_date)
AND TRUNC (SYSDATE) BETWEEN TRUNC (paaf.effective_start_date)
AND TRUNC (paaf.effective_end_date)
AND papf.person_id = paaf.person_id;
EXCEPTION
WHEN OTHERS
THEN
v_person_id := 0;
v_person_id := NULL;
END;

IF v_person_id = 0
THEN
NULL;
ELSE

-- Update Employee API
-- ---------------------------------
IF TRUNC (r_emp.effective_start_date) <>
TRUNC (v_effective_start_date)
THEN
lc_dt_ud_mode := 'UPDATE';
ELSE
lc_dt_ud_mode := 'CORRECTION';
END IF;

BEGIN
hr_person_api.update_person
( -- Input Data Elements
-- ------------------------------
p_effective_date => TRUNC (r_emp.effective_start_date),
p_datetrack_update_mode => lc_dt_ud_mode,
p_person_id => v_person_id,
p_first_name => r_emp.first_name,
p_last_name => r_emp.last_name,
p_middle_names => r_emp.middle_names,
p_sex => r_emp.gender,
p_date_of_birth => r_emp.date_of_birth,
p_title => r_emp.title,
p_email_address => r_emp.email,
p_national_identifier => lc_employee_number,
p_attribute1 => r_emp.LEVELS,
p_attribute2 => r_emp.level_desc,
p_attribute3 => r_emp.position_code,
p_attribute4 => r_emp.position_desc,
p_attribute5 => r_emp.dept_code,
p_attribute6 => r_emp.department,
p_attribute7 => r_emp.sub_dept_code,
p_attribute8 => r_emp.sub_depat,
p_attribute9 => r_emp.plant_code,
p_attribute10 => r_emp.plant,
p_attribute11 => r_emp.supervise_emp_code,
p_attribute12 => r_emp.old_employee_code,
-- Output Data Elements
-- ----------------------------------
p_employee_number => lc_employee_number,
p_object_version_number => v_object_version_number,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_full_name => lc_full_name,
p_comment_id => ln_comment_id,
p_name_combination_warning => lb_name_combination_warning,
p_assign_payroll_warning => lb_assign_payroll_warning,
p_orig_hire_warning => lb_orig_hire_warning
);

UPDATE ynppo_temp_employee
SET status = 'Updated'
WHERE ID = r_emp.ID;
EXCEPTION
WHEN OTHERS
THEN
--ROLLBACK;
UPDATE ynppo_temp_employee
SET status = 'Not Updated'
WHERE ID = r_emp.ID;

DBMS_OUTPUT.put_line (SQLERRM);
END;
-- COMMIT;
END IF;
END LOOP;
END;

Query to get Absence Detail Employee wise. Approved Absence details.

SELECT papf.EMPLOYEE_NUMBER
,papf.FULL_NAME
,pat.NAME absence_type
, paa.date_start leave_start_date
,paa.date_end leave_end_date
, paa.absence_days
, paa.abs_information_category
,paa.abs_information2
, paa.abs_information3
,paa.abs_information4
FROM per_absence_attendances paa,
per_absence_attendance_types pat,
per_all_people_f papf
WHERE paa.absence_attendance_type_id = pat.absence_attendance_type_id
AND papf.person_id = paa.person_id
AND TRUNC (paa.date_start) BETWEEN TRUNC(papf.effective_start_date)
AND TRUNC (papf.effective_end_date)
AND papf.EMPLOYEE_NUMBER=nvl(:p_employee_number,papf.EMPLOYEE_NUMBER)
order by papf.EMPLOYEE_NUMBER,paa.date_start

Oracle HRMS API – Update Employee Assignment & Supervisor Adding To Update Employee

-------------------------
Supervisor, Manager Flag, Bargaining Unit, Labour Union Member Flag, Time Card, Work Schedule, Normal Hours, Frequency, Time Normal Finish, Time Normal Start, Default Code Combination, Set of Books Id
API -- hr_assignment_api.update_emp_asg

To Update Employee
--------------------------
Grade, Location, Job, Payroll, Organization, Employee Category, People Group
API -- hr_assignment_api.update_emp_asg_criteria

Staging Table
-----------------
CREATE TABLE APPS.XXAT_EMP_ASSIGNMENTS
(
EMP_NUM NUMBER,
ORG_NAME VARCHAR2(2000 BYTE),
GCC_GROUP VARCHAR2(1000 BYTE),
POSITION VARCHAR2(2000 BYTE),
JOB VARCHAR2(2000 BYTE),
PAYROLL VARCHAR2(2000 BYTE),
LOCATION VARCHAR2(2000 BYTE),
STATUS VARCHAR2(2000 BYTE),
SAL_BASIS VARCHAR2(2000 BYTE),
SUPERVISOR_NUM NUMBER,
GOSI_EMP VARCHAR2(1000 BYTE),
PROCESS_FLAG VARCHAR2(1 BYTE),
ERROR_MSG VARCHAR2(3000 BYTE)
);

Flat File Format
------------------------------
Emp Num Organization GCC Group Position Job Payroll Location Status Salary Basis Supervisor Number GOSI - Employer Process Flag Error Message
3 Chief Business Support Office No.No.Yes Chief Business Support Officer.Chief Business Support Office Chief Business Support Officer EATC Payroll Riyadh Head Office Active Assignment EATC Monthly Salary 911 Etihad Atheeb Telecommunication Company N

Sample Code - Example
------------------------------
Package Specification
--------------------------

CREATE OR REPLACE PACKAGE APPS.xxat_emp_assign_pkg
AS
PROCEDURE xxxat_emp_assign_update;
END xxat_emp_assign_pkg;

Package Body
----------------

CREATE OR REPLACE PACKAGE BODY APPS.xxat_emp_assign_pkg
AS
PROCEDURE xxxat_emp_assign_update
IS
-- lc_c_emp_assg c_emp_assg%ROWTYPE;
l_object_version_number NUMBER;
l_special_ceiling_step_id NUMBER;
l_people_group_id NUMBER;
l_soft_coding_keyflex_id NUMBER;
l_supervisor_id NUMBER;
l_assg_id NUMBER;
l_group_name VARCHAR2 (100);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_org_now_no_manager_warning BOOLEAN;
l_other_manager_warning BOOLEAN;
l_no_managers_warning BOOLEAN;
l_spp_delete_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (100);
l_tax_district_changed_warning BOOLEAN;
l_concatenated_segments VARCHAR2 (100);
l_gsp_post_process_warning VARCHAR2 (100);
l_job_id VARCHAR2 (20);
l_position_id VARCHAR2 (20);
l_grade_id VARCHAR2 (20);
l_org_id VARCHAR2 (20);
l_loc_id VARCHAR2 (20);
l_assg_cat VARCHAR2 (50);
l_assg_start_date DATE;
l_pf_flag CHAR (1);
l_segment1 VARCHAR2 (50);
l_segment2 VARCHAR2 (50);
l_segment3 VARCHAR2 (50);
l_person_id NUMBER;
--
l_error NUMBER;
l_error_msg VARCHAR2 (3000);
v_emp_num VARCHAR2 (100);
l_assign_date VARCHAR2 (100);
l_sup_hire_date DATE;
l_emp_hire_date DATE;
l_payroll_id NUMBER;
l_paybasis_id NUMBER;
l_comment_id NUMBER;

CURSOR c1
IS
SELECT *
FROM xxat_emp_assignments
WHERE process_flag = 'N'
AND emp_num = 82;
BEGIN
DBMS_OUTPUT.put_line (' ****** Process Validation Start ******');

FOR i IN c1
LOOP
l_error := 0;
l_person_id := NULL;

IF i.emp_num IS NULL
-- OR i.assign_start_date IS NULL
OR i.org_name IS NULL
OR i.gcc_group IS NULL
OR i.POSITION IS NULL
OR i.job IS NULL
OR i.payroll IS NULL
OR i.LOCATION IS NULL
OR i.status IS NULL
OR i.sal_basis IS NULL
OR i.supervisor_num IS NULL
OR i.gosi_emp IS NULL
THEN
l_error_msg :=
'All Fields are mandatory & should not be null';
l_error := 1;
-- p_retcode := 2;
--fnd_file.put_line (fnd_file.LOG,i.relationship_id || '-' || l_error_msg);
DBMS_OUTPUT.put_line (i.emp_num || '-' || l_error_msg);
-- p_errbuf := l_error_msg;
ELSE
l_object_version_number := NULL;
l_special_ceiling_step_id := NULL;
l_people_group_id := NULL;
l_soft_coding_keyflex_id := NULL;
l_group_name := NULL;
l_effective_start_date := NULL;
l_effective_end_date := NULL;
l_org_now_no_manager_warning := FALSE;
l_other_manager_warning := FALSE;
l_spp_delete_warning := FALSE;
l_entries_changed_warning := NULL;
l_tax_district_changed_warning := FALSE;
l_concatenated_segments := NULL;
l_gsp_post_process_warning := NULL;
-- error_desc := '';
-- lv_assg_flag := '';
l_assg_cat := NULL;
l_job_id := NULL;
l_position_id := NULL;
l_grade_id := NULL;
l_org_id := NULL;
l_assign_date := NULL;
l_sup_hire_date := NULL;
l_emp_hire_date := NULL;

---------------------------
-- AssignmentID
---------------------------
BEGIN
SELECT paf.assignment_id
INTO l_assg_id
FROM per_assignments_f paf
WHERE paf.assignment_number = i.emp_num
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
-- l_assg_id := NULL;
l_error_msg := 'Assignment not found';
l_error := 1;
END;

---------------------------------------
-- Employee Hire Date / Effective Start Date
---------------------------------------
BEGIN
SELECT paf.start_date
INTO l_emp_hire_date
FROM per_all_people_f paf
WHERE paf.employee_number = i.emp_num
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date;

SELECT TO_DATE (TO_CHAR (l_assign_date,
'RRRR/MM/DD HH24:MI:SS'),
'RRRR/MM/DD HH24:MI:SS'
)
INTO l_assign_date
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg :=
'Employee start date not found';
l_error := 1;
END;

-------------------------------------------------
-- Supervisor Hire Date / Effective Start Date
--------------------------------------------------
BEGIN
SELECT paf.start_date
INTO l_sup_hire_date
FROM per_all_people_f paf
WHERE paf.employee_number = i.supervisor_num
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date;

SELECT TO_DATE (TO_CHAR (l_assign_date,
'RRRR/MM/DD HH24:MI:SS'),
'RRRR/MM/DD HH24:MI:SS'
)
INTO l_assign_date
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN

l_error_msg :=
'Employee start date not found';
l_error := 1;
END;

IF l_emp_hire_date < l_sup_hire_date
THEN
l_assign_date := l_sup_hire_date;
ELSE
l_assign_date := l_emp_hire_date;
END IF;

--dbms_output.put_line('emp date'||l_emp_hire_date||'super date'||l_sup_hire_date||'final'||l_assign_date);
----------------------------------
-- Organization ID
----------------------------------
BEGIN
SELECT organization_id
INTO l_org_id
FROM hr_all_organization_units
WHERE UPPER (NAME) LIKE UPPER (i.org_name)
AND business_group_id = 81;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Organization not found';
l_error := 1;
END;

----------------------
-- Job ID
----------------------
BEGIN
SELECT pj.job_id
INTO l_job_id
FROM per_jobs pj,
per_job_definitions pjd
WHERE pj.job_definition_id = pjd.job_definition_id
AND pj.NAME = i.job;
EXCEPTION
WHEN OTHERS
THEN
-- l_job_id := NULL;
l_error_msg := 'Job not found';
l_error := 1;
END;

----------------------------------------
-- Position ID
----------------------------------------
BEGIN
SELECT pap.position_id
INTO l_position_id
FROM per_all_positions pap,
per_position_definitions ppd
WHERE pap.position_definition_id = ppd.position_definition_id
AND pap.status = 'VALID'
AND pap.NAME LIKE i.POSITION || '%';
EXCEPTION
WHEN OTHERS
THEN
-- l_position_id := NULL;
l_error_msg := 'Position not found';
l_error := 1;
END;

---------------------------
-- Grade ID
---------------------------
/* BEGIN
SELECT pg.grade_id
INTO l_grade_id
FROM per_grades pg,
per_grade_definitions pgd
WHERE pg.grade_definition_id = pgd.grade_definition_id
AND UPPER (pgd.segment1) =
UPPER (lc_c_emp_assg.grad_segment1)
AND UPPER (pgd.segment2) =
UPPER (lc_c_emp_assg.grad_segment2)
AND UPPER (pgd.segment3) =
UPPER (lc_c_emp_assg.grad_segment3);
EXCEPTION
WHEN OTHERS
THEN
l_grade_id := NULL;
END;*/

---------------------------------
-- Payroll ID
---------------------------------
BEGIN
SELECT payroll_id
INTO l_payroll_id
FROM pay_all_payrolls_f
WHERE 1 = 1
AND UPPER (payroll_name) LIKE UPPER (i.payroll)
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
-- l_pay_id := NULL;
l_error_msg := 'Payroll ID not found';
l_error := 1;
END;

---------------------------------
-- Location ID
---------------------------------
BEGIN
SELECT location_id
INTO l_loc_id
FROM hr_locations_all
WHERE UPPER (location_code) LIKE UPPER (i.LOCATION);
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Location ID not found';
l_error := 1;
END;

-----------------------------------------------
-- Salary Information (T) > Salary Basis ID
-----------------------------------------------
BEGIN
SELECT pay_basis_id
INTO l_paybasis_id
FROM per_pay_bases
WHERE 1 = 1
AND UPPER (NAME) LIKE UPPER (i.sal_basis);
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Pay/Salary basis not found';
l_error := 1;
END;

---------------------------------
-- Supervisor ID
---------------------------------
BEGIN
SELECT person_id
INTO l_supervisor_id
FROM per_all_people_f ppf
WHERE employee_number = i.supervisor_num
AND SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Supervisor ID not found';
l_error := 1;
END;

-----------------------------
-- Assignment Category
-----------------------------

-- BEGIN

-- SELECT distinct lookup_code
-- INTO l_assg_cat
-- FROM fnd_lookup_values_vl
-- WHERE lookup_type = 'EMP_CAT'
-- AND UPPER (meaning) LIKE UPPER (lc_c_emp_assg.ASSG_CATG)
-- AND DESCRIPTION IS NULL;
--
-- EXCEPTION
-- WHEN OTHERS
-- THEN
-- l_assg_cat := NULL;
-- END;

--------------------------
-- People Group ID & NAME Number
--------------------------
BEGIN
SELECT people_group_id,
group_name
INTO l_people_group_id,
l_group_name
FROM pay_people_groups a
WHERE segment1 || '.' || segment2 || '.' || segment3 IN (
SELECT segment1 || '.' || segment2 || '.' || segment3
FROM pay_people_groups a
WHERE 1 = 1
-- AND people_group_id = 'No.No.Yes'
AND group_name = i.gcc_group);
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Group ID/Name not found';
l_error := 1;
END;

--------------------------
-- Object Version Number
--------------------------
BEGIN
SELECT paaf.object_version_number
INTO l_object_version_number
FROM per_all_assignments_f paaf
WHERE SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.assignment_id = l_assg_id;

-- DBMS_OUTPUT.put_line ( 'object version no.'
-- || l_object_version_number
-- );
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Object Version not found';
l_error := 1;
END;

IF l_error = 0
THEN
BEGIN

-- Update Employee Assgment Criteria
-- -----------------------------------------------------
hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE,
p_effective_date => TO_DATE
(l_assign_date,
'DD-MON-RRRR'
),
-- TRUNC (SYSDATE),
--l_assg_start_date,
p_datetrack_update_mode => 'CORRECTION',
--'UPDATE',
p_assignment_id => l_assg_id,
p_position_id => l_position_id,
p_job_id => l_job_id,
p_payroll_id => l_payroll_id,
-- p_grade_id => l_grade_id,
p_location_id => l_loc_id,
p_organization_id => l_org_id,
p_pay_basis_id => l_paybasis_id,
--p_employment_category => l_assg_cat ,
p_segment1 => 81,
-- GOSI Tab - Emloyer Name
-- p_segment2 => lc_c_emp_assg.pg_segment2,
-- p_segment3 => lc_c_emp_assg.pg_segment3,
-- p_supervisor_assignment_id => l_supervisor_id,
--Outparatmers
p_people_group_id => l_people_group_id,
p_object_version_number => l_object_version_number,
p_special_ceiling_step_id => l_special_ceiling_step_id,
p_group_name => l_group_name,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
--lc_c_emp_assg.people_group,
p_org_now_no_manager_warning => l_org_now_no_manager_warning,
p_other_manager_warning => l_other_manager_warning,
p_spp_delete_warning => l_spp_delete_warning,
p_entries_changed_warning => l_entries_changed_warning,
p_tax_district_changed_warning => l_tax_district_changed_warning
-- p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,
-- p_concatenated_segments => l_concatenated_segments,
-- p_gsp_post_process_warning => l_gsp_post_process_warning
);
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg :=
'Api Exception ' || SQLERRM;
END;
-- dbms_output.put_line(TO_DATE (l_assign_date,'DD-MON-RRRR' ));
BEGIN

-- Update Employee Assignment
-- ---------------------------------------------
hr_assignment_api.update_emp_asg
(
-- Input data elements
p_effective_date => TO_DATE (l_assign_date,
'DD-MON-RRRR'
),
-- TRUNC (SYSDATE),
p_datetrack_update_mode => 'UPDATE',
p_assignment_id => l_assg_id,
p_supervisor_id => l_supervisor_id,
p_change_reason => NULL,
p_manager_flag => 'Y',
-- p_bargaining_unit_code => NULL,
-- p_labour_union_member_flag => NULL,
-- p_segment1 => 204,
-- p_segment3 => 'N',
-- p_normal_hours => null,
-- p_frequency => NULL,
-- Output data elements
-- -------------------------------
p_object_version_number => l_object_version_number,
p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,
p_concatenated_segments => l_concatenated_segments,
p_comment_id => l_comment_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_no_managers_warning => l_no_managers_warning,
p_other_manager_warning => l_other_manager_warning
);
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg :=
'Error In Supervisors API ' || SQLERRM;
END;

IF l_error = 1
THEN
UPDATE xxat_emp_assignments
SET process_flag = 'E',
error_msg = l_error_msg
WHERE emp_num = i.emp_num;
ELSE
UPDATE xxat_emp_assignments
SET process_flag = 'Y'
WHERE emp_num = i.emp_num;
END IF;
ELSIF l_error = 1
THEN
UPDATE xxat_emp_assignments
SET process_flag = 'E',
error_msg = l_error_msg
WHERE emp_num = i.emp_num;
END IF;

COMMIT;
--fnd_file.put_line (fnd_file.LOG, '****** Process Validation End ******');
END IF;
END LOOP;

DBMS_OUTPUT.put_line ('****** Process Validation End ******');
COMMIT;
END xxxat_emp_assign_update;
END xxat_emp_assign_pkg;
/

Friday, July 3, 2015

Payroll Employee Balances

--Payroll Employee Balances
Select * from
PAY_BALANCE_VALUES_V PBV
where
PBV.ASSIGNMENT_ID = 106
and pbv.BALANCE_NAME like 'Housing Loan%'
and pbv.Dimension_name like 'Assignment Inception To Date'

--Payroll
Select papf.EMPLOYEE_NUMBER, pbv.*
from
per_all_people_f papf,
per_all_assignments_f paaf,
PAY_ASSIGNMENT_ACTIONS pyaa,
PAY_BALANCE_VALUES_V PBV
where
papf.PERSON_ID = paaf.PERSON_ID
and paaf.ASSIGNMENT_ID = pbv.ASSIGNMENT_ID
and pyaa.ASSIGNMENT_ACTION_ID = pbv.ASSIGNMENT_ACTION_ID
and pyaa.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
--PBV.ASSIGNMENT_ID = 106
and pbv.BALANCE_NAME like 'Housing Loan%'
and pbv.Dimension_name like 'Assignment Inception To Date'

--Payroll
Select papf.EMPLOYEE_NUMBER,
pbv.*
from
per_all_people_f papf,
per_all_assignments_f paaf,
PAY_ASSIGNMENT_ACTIONS pyaa,
PAY_BALANCE_VALUES_V PBV
where
papf.PERSON_ID = paaf.PERSON_ID
and paaf.ASSIGNMENT_ID = pbv.ASSIGNMENT_ID
and pyaa.ASSIGNMENT_ACTION_ID = pbv.ASSIGNMENT_ACTION_ID
and pyaa.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
and PBV.ASSIGNMENT_ID = '1005'
--and pbv.BALANCE_NAME like 'Housing Loan%'
and pbv.Dimension_name like 'Assignment Inception To Date'
and pbv.EFFECTIVE_DATE > to_date ('06/15/2009','MM/DD/YYYY')

Query: Salary Management

select
ppf.employee_number "Employee Number",
--
ppf.full_name "Full Name",
psf.assignment_number "Assignment Number",
ppb.name "Salary basis",
ppp.change_date "Approved Date",
ppp.proposed_salary_n "Approved Salary",
pro1.change_date "Change Date",
pro1.proposed_salary_n "Proposed Salary",
proposal_reason "Proposal Reason",
pppc.change_percentage "COL %",
pppc.change_amount_n "COL Amount",
psf.employment_category "Assignment Category", -- lookup;EMP_CAT
--
ppf.person_id,
ppb.pay_basis_id
from
per_all_people_f ppf,
per_assignments_f psf,
per_pay_bases ppb,
per_pay_proposals pro1,
(select ppp.change_date,
ppp.proposed_salary_n,
ppp.approved,
ppp.assignment_id
from per_pay_proposals ppp
where approved = 'Y') ppp,
per_pay_proposal_components pppc
where
1=1
and ppf.person_id = psf.person_id
and psf.pay_basis_id = ppb.pay_basis_id
and psf.assignment_id = pro1.assignment_id(+)
and pro1.approved(+) = 'N'
and psf.assignment_id = ppp.assignment_id(+)
and (ppp.change_date is null
or ppp.change_date = (select max (change_date)
from per_pay_proposals pro3
where ppp.assignment_id = pro3.assignment_id
and pro3.approved = 'Y'))
and pro1.pay_proposal_id = pppc.pay_proposal_id(+)
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between psf.effective_start_date and psf.effective_end_date
--
and ppf.employee_number = '1309'
order by
1,2;