CREATE OR REPLACE PROCEDURE APPS.xx_assign_upload_criteria_proc
(v_emp_no varchar2 default null, v_update_mode varchar2 default null )
AS
vl$validate_mode BOOLEAN := FALSE;
vl$assignment_id per_assignments_f.assignment_id%TYPE;
vl$assignment_number VARCHAR2 (50);
vl$set_of_books_name VARCHAR2 (200) := 'COMEX MXN BOOK';
vl$set_of_books NUMBER;
p_employee_number VARCHAR2 (50) := '$__19A1099000001'; --'$__19A1099000001';
vl$person_id NUMBER;
vl$object_version_number NUMBER;
vl$job_title VARCHAR2 (200);
vl$job_id NUMBER;
vl$job_name VARCHAR2 (200) := 'ELECTRICIAN';
vl$location_id NUMBER := 127;
vl$called_from_mass_update BOOLEAN := FALSE;
V_people_group_id NUMBER;
V_object_version_number NUMBER;
V_special_ceiling_step_id NUMBER;
V_group_name VARCHAR2 (4000);
V_effective_start_date DATE;
V_effective_end_date DATE;
V_org_now_no_manager_warning BOOLEAN;
v_other_manager_warning BOOLEAN;
v_spp_delete_warning BOOLEAN;
v_entries_changed_warning VARCHAR2 (4000);
v_tax_district_changed_warning BOOLEAN;
vl$fb_entra BOOLEAN := TRUE;
v_concatenated_segments VARCHAR2 (200);
v_gsp_post_process_warning VARCHAR2 (200);
v_soft_coding_keyflex_id NUMBER;
my_errcode NUMBER;
my_errm VARCHAR2(32000);
v_effective_date DATE := '01-Jan-2013';
v_datetrack_update_mode VARCHAR2 (50) := 'UPDATE'; -- CORRECTION|UPDATE
CURSOR c1
IS
SELECT
( select organization_id from hr_all_organization_units where type ='LE'
and name = v.LEGAL_EMPLOYER )
LEGAL_EMPLOYER,
(SELECT assignment_id
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND a.EMPLOYEE_NUMBER = v.employee_code
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND ROWNUM = 1)
assignment_id,
(SELECT b.effective_start_date
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND a.EMPLOYEE_NUMBER = v.employee_code
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND ROWNUM = 1)
ASG_EFF_ST_date,
(SELECT B.OBJECT_VERSION_NUMBER
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND a.EMPLOYEE_NUMBER = v.employee_code
AND ROWNUM = 1)
obj_ver_no,
v.employee_code,
(SELECT POSITION_ID
FROM HR_ALL_POSITIONS_F
WHERE NAME = v.POSITION
and ROWNUM = 1 )
position_id,
(SELECT job_ID
FROM PER_JOBS
WHERE NAME = v.JOB_TITLE
and ROWNUM = 1 )
job_id,
v.POSITION,
(SELECT ORGANIZATION_ID
FROM hr_all_organization_units
WHERE NAME = v.ORGANIZATION)
org_id,
(SELECT LOCATION_ID
FROM hr_locations_all
WHERE LOCATION_CODE = v.location)
location,
ASSIGNMENT_STATUS,
STAFF_TYPE,
SHIFT_HOURS || ' Hours' SHIFT_HOURS,
DECODE (ACCOMMODATION_PROVIDED,
'Yes', 'Y',
'yes', 'Y',
'No', 'N',
'no', 'N',
Null)
ACCOMMODATION_PROVIDED,
DECODE (TRANSPORTAION_PROVIDED,
'Yes', 'Y',
'yes', 'Y',
'No', 'N',
'no', 'N',
Null)
TRANSPORTAION_PROVIDED,
DECODE (OVERTIME_ELIGIBLE,'yes', 'Y', 'Yes', 'Y', 'No', 'N', 'no', 'N', Null)
OVERTIME_ELIGIBLE,
DECODE (SPECIAL_OT_ELIGIBLE, 'yes', 'Y', 'Yes', 'Y', 'No', 'N', 'no', 'N', Null)
SPECIAL_OT_ELIGIBLE,
SPECIAL_OT_RATE,
DECODE (TIME_SHEET_REQUIRED, 'yes', 'Y', 'Yes', 'Y', 'No', 'N', 'no', 'N', Null)
TIME_SHEET_REQUIRED,
TO_DATE(ASSIGNMENT_EFFICITIVE_DATE,'MM/DD/YYYY')
ASSIGNMENT_EFFICITIVE_DATE,
(SELECT person_id
FROM per_all_people_f
WHERE supervisor = employee_number AND ROWNUM = 1)
supervisor_id,
(SELECT assignment_id
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND a.EMPLOYEE_NUMBER = v.supervisor
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND ROWNUM = 1)
sup_assign_id,
supervisor,
Payroll_id,
People_grp_id
FROM xx_assign_upload v
WHERE v.employee_code like nvl(v_emp_no,'%')
and Record_criteria_processed_flag is null ;
BEGIN
if v_update_mode is not null then
v_datetrack_update_mode := v_update_mode;
End if;
FOR i IN c1
LOOP
Begin
v_effective_date := '01-Jan-2013';
if v_effective_date <= i.asg_eff_st_date then
v_effective_date := i.asg_eff_st_date ;
v_datetrack_update_mode := 'CORRECTION' ;
Else
v_datetrack_update_mode := 'UPDATE' ;
End if ;
V_people_group_id := null ;
V_object_version_number := i.obj_ver_no;
V_special_ceiling_step_id := null;
V_group_name := null;
V_effective_start_date := null;
V_effective_end_date := null;
v_entries_changed_warning := null;
v_concatenated_segments := null;
v_gsp_post_process_warning := null;
-- v_soft_coding_keyflex_id := i.LEGAL_EMPLOYER;
v_soft_coding_keyflex_id := null;
-- v_effective_date := I.ASG_EFF_ST_date;
hr_assignment_api.update_emp_asg_criteria (
p_effective_date => v_effective_date,
p_datetrack_update_mode => v_datetrack_update_mode,
p_assignment_id => i.ASSIGNMENT_ID,
p_validate => False,
p_called_from_mass_update => NULL,
p_grade_id => NULL,
p_position_id => i.POSITION_ID,
p_job_id => i.job_id,
p_payroll_id => i.Payroll_id,
p_location_id => i.LOCATION,
p_organization_id => i.ORG_ID,
p_pay_basis_id => NULL,
p_segment1 => i.STAFF_TYPE,
p_segment2 => i.SHIFT_HOURS,
p_SEGMENT3 => i.ACCOMMODATION_PROVIDED, -- Accommodation Provided
p_SEGMENT4 => i.TRANSPORTAION_PROVIDED, -- Transportaion Provided
p_SEGMENT5 => i.OVERTIME_ELIGIBLE, -- Overtime Eligible
p_SEGMENT6 => i.SPECIAL_OT_ELIGIBLE, -- Special OT Eligible
p_segment7 => i.SPECIAL_OT_RATE, -- Special OT Rate,
p_SEGMENT8 => i.TIME_SHEET_REQUIRED, -- Time Sheet Required
p_SEGMENT9 => null,
p_supervisor_assignment_id => i.sup_assign_id ,
p_object_version_number => v_object_version_number,
p_special_ceiling_step_id => v_special_ceiling_step_id,
p_people_group_id => v_people_group_id,
p_soft_coding_keyflex_id => v_soft_coding_keyflex_id,
p_group_name => v_group_name,
p_effective_start_date => v_effective_start_date,
p_effective_end_date => v_effective_end_date,
p_org_now_no_manager_warning => v_org_now_no_manager_warning,
p_other_manager_warning => v_other_manager_warning,
p_spp_delete_warning => v_spp_delete_warning,
p_entries_changed_warning => v_entries_changed_warning,
p_tax_district_changed_warning => v_tax_district_changed_warning,
p_concatenated_segments => v_concatenated_segments,
p_gsp_post_process_warning => v_gsp_post_process_warning);
-- If API is Successfull
Update xx_assign_upload set Record_criteria_processed_flag = 'Y' , Record_criteria_processed_dt = sysdate
where employee_code = i.employee_code ;
dbms_output.put_line( 'Employee code :'|| i.employee_code || ' Updated ');
Exception
When Others then
my_errcode := SQLCODE;
my_errm := SQLERRM;
update xx_assign_upload set Record_err_message = substr(('Employee code :'|| i.employee_code||'Ass Eff dt :'||
to_char(I.ASG_EFF_ST_date,'DD-MON-YYYY') ||
'Position id ' || i.POSITION_ID|| 'Error code ' || my_errcode || ': ' || my_errm),1, 2000),
Record_processed_dt = sysdate
where employee_code = i.employee_code ;
dbms_output.put_line( 'Employee code :'|| i.employee_code||'Ass Eff dt :'||
to_char(I.ASG_EFF_ST_date,'DD-MON-YYYY') ||
'Position id ' || i.POSITION_ID||
'Error code ' || my_errcode || ': ' || my_errm);
End ;
END LOOP;
COMMIT;
-- Output the results
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (
SUBSTR ('Error ' || TO_CHAR (SQLCODE) || ': ' || SQLERRM, 1, 255));
RAISE;
END;
/
CREATE OR REPLACE procedure APPS.XX_ASSIGN_UPLOAD_PROC ( v_emp_no varchar2 default null , v_update_mode varchar2 default null ) as
lc_dt_ud_mode VARCHAR2 (100) := NULL;
ln_assignment_id NUMBER;
ln_supervisor_id NUMBER;
ln_object_number NUMBER;
ln_people_group_id NUMBER;
-- Out Variables for Find Date Track Mode API
-- -----------------------------------------------------------------
lb_correction BOOLEAN;
lb_update BOOLEAN;
lb_update_override BOOLEAN;
lb_update_change_insert BOOLEAN;
-- Out Variables for Update Employee Assignment API
-- ----------------------------------------------------------------------------
ln_soft_coding_keyflex_id HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
lc_concatenated_segments VARCHAR2 (2000);
ln_comment_id PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
lb_no_managers_warning BOOLEAN;
-- Out Variables for Update Employee Assgment Criteria
-- -------------------------------------------------------------------------------
ln_special_ceiling_step_id PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
lc_group_name VARCHAR2 (30);
ld_effective_start_date PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
ld_effective_end_date PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
lb_org_now_no_manager_warning BOOLEAN;
lb_other_manager_warning BOOLEAN;
lb_spp_delete_warning BOOLEAN;
lc_entries_changed_warning VARCHAR2 (30);
lb_tax_district_changed_warn BOOLEAN;
my_errcode NUMBER;
my_errm VARCHAR2(32000);
v_cagr_grade_def_id number ;
v_cagr_concatenated_segments varchar2(200);
V_concatenated_segments varchar2 (200);
V_soft_coding_keyflex_id number ; -- bug 2359997
V_comment_id number;
V_effective_start_date date;
V_effective_end_date date;
v_no_managers_warning boolean;
v_other_manager_warning boolean;
v_hourly_salaried_warning boolean;
v_gsp_post_process_warning varchar2(4000);
v_effective_date DATE := '01-Jan-2013';
v_datetrack_update_mode VARCHAR2 (50) := 'UPDATE'; -- CORRECTION|UPDATE
CURSOR c1
IS
SELECT
( select organization_id from hr_all_organization_units where type ='LE'
and name = v.LEGAL_EMPLOYER )
LEGAL_EMPLOYER,
(SELECT assignment_id
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND a.EMPLOYEE_NUMBER = v.employee_code
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND ROWNUM = 1)
assignment_id,
(SELECT b.effective_start_date
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND a.EMPLOYEE_NUMBER = v.employee_code
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND ROWNUM = 1)
ASG_EFF_ST_date,
(SELECT B.OBJECT_VERSION_NUMBER
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND a.EMPLOYEE_NUMBER = v.employee_code
AND ROWNUM = 1)
obj_ver_no,
(SELECT pcf1.CONTRACT_ID
from per_all_people_f a,per_contracts_f pcf1
Where pcf1.Status ='A-ACTIVE'
and a.person_id = pcf1.person_id
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND a.EMPLOYEE_NUMBER = v.employee_code ) contract_id,
v.employee_code,
(SELECT POSITION_ID
FROM HR_ALL_POSITIONS_F
WHERE NAME = v.POSITION
and ROWNUM = 1 )
position_id,
(SELECT job_ID
FROM PER_JOBS
WHERE NAME = v.JOB_TITLE
and ROWNUM = 1 )
job_id,
v.POSITION,
(SELECT ORGANIZATION_ID
FROM hr_all_organization_units
WHERE NAME = v.ORGANIZATION and rownum=1)
org_id,
(SELECT LOCATION_ID
FROM hr_locations_all
WHERE LOCATION_CODE = v.location and rownum = 1)
location,
ASSIGNMENT_STATUS,
STAFF_TYPE,
SHIFT_HOURS || ' Hours' SHIFT_HOURS,
DECODE (ACCOMMODATION_PROVIDED,
'Yes', 'Y',
'No', 'N',
'Null')
ACCOMMODATION_PROVIDED,
DECODE (TRANSPORTAION_PROVIDED,
'Yes', 'Y',
'No', 'N',
'Null')
TRANSPORTAION_PROVIDED,
DECODE (OVERTIME_ELIGIBLE, 'Yes', 'Y', 'No', 'N', 'Null')
OVERTIME_ELIGIBLE,
DECODE (SPECIAL_OT_ELIGIBLE, 'Yes', 'Y', 'No', 'N', 'Null')
SPECIAL_OT_ELIGIBLE,
DECODE (SPECIAL_OT_RATE, 'Yes', 'Y', 'No', 'N', 'Null')
SPECIAL_OT_RATE,
DECODE (TIME_SHEET_REQUIRED, 'Yes', 'Y', 'No', 'N', 'Null')
TIME_SHEET_REQUIRED,
TO_DATE(ASSIGNMENT_EFFICITIVE_DATE,'MM/DD/YYYY')
ASSIGNMENT_EFFICITIVE_DATE,
(SELECT person_id
FROM per_all_people_f
WHERE supervisor = employee_number AND ROWNUM = 1)
supervisor_id,
supervisor
FROM xx_assign_upload v
WHERE v.employee_code like nvl(v_emp_no,'%')
and Record_processed_flag is null;
BEGIN
if v_update_mode is not null then
v_datetrack_update_mode := v_update_mode;
End if;
FOR i IN c1
LOOP
Begin
v_effective_date := '01-Jan-2013';
if v_effective_date <= i.asg_eff_st_date then
v_effective_date := i.asg_eff_st_date ;
v_datetrack_update_mode := 'CORRECTION' ;
Else
v_datetrack_update_mode := 'UPDATE' ;
End if ;
DBMS_OUTPUT.put_line ('Test 2') ;
ln_object_number := NVL(i.OBJ_VER_NO,1) ;
V_soft_coding_keyflex_id := null ;
/* v_effective_date DATE := '01-Jan-2013';
v_datetrack_update_mode VARCHAR2 (50) := 'UPDATE'; -- CORRECTION|UPDATE*/
hr_assignment_api.update_emp_asg ( -- Input data elements
-- ------------------------------
p_validate => False,
p_effective_date => v_effective_date,
p_datetrack_update_mode => v_datetrack_update_mode,
p_assignment_id => to_number(i.ASSIGNMENT_ID),
p_supervisor_id => to_number(i.supervisor_id),
-- p_change_reason => NULL,
-- p_manager_flag => NULL,
-- p_bargaining_unit_code => NULL,
-- p_labour_union_member_flag => NULL,
p_segment1 => I.LEGAL_EMPLOYER,
p_segment2 => Null,
p_segment3 => Null,
p_segment4 => Null,
p_segment5 => Null,
p_segment7 => Null,
p_segment8 => Null,
p_contract_id => i.contract_id,
-- p_segment3 => NULL,
-- p_normal_hours => NULL,
-- p_frequency => NULL,
-- Output data elements
-- -------------------------------
p_object_version_number => ln_object_number,
p_cagr_grade_def_id => v_cagr_grade_def_id,
p_cagr_concatenated_segments => v_cagr_concatenated_segments,
p_concatenated_segments => v_concatenated_segments,
p_soft_coding_keyflex_id => v_soft_coding_keyflex_id,
p_comment_id => v_comment_id,
p_effective_start_date => v_effective_start_date,
p_effective_end_date => v_effective_end_date,
p_no_managers_warning => v_no_managers_warning,
p_other_manager_warning => v_other_manager_warning
);
-- DBMS_OUTPUT.put_line (' ln_comment_id' || ln_comment_id);
-- DBMS_OUTPUT.put_line (' lb_no_managers_warning' || lb_no_managers_warning);
-- DBMS_OUTPUT.put_line (' lb_other_manager_warning' || lb_other_manager_warning);
Update xx_assign_upload set Record_processed_flag = 'Y' , Record_processed_dt = sysdate
where employee_code = i.employee_code ;
dbms_output.put_line( 'Employee code :'|| i.employee_code || ' Updated ');
Exception
When Others then
my_errcode := SQLCODE;
my_errm := SQLERRM;
update xx_assign_upload set Record_err_message = substr(('Employee code :'|| i.employee_code||'Ass Eff dt :'||
to_char(I.ASG_EFF_ST_date,'DD-MON-YYYY') ||
'Position id ' || i.POSITION_ID|| 'Error code ' || my_errcode || ': ' || my_errm),1, 2000),
Record_processed_dt = sysdate
where employee_code = i.employee_code ;
dbms_output.put_line( 'Employee code :'|| i.employee_code||'Ass Eff dt :'||
to_char(I.ASG_EFF_ST_date,'DD-MON-YYYY') ||
'Position id ' || i.POSITION_ID||
'Error code ' || my_errcode || ': ' || my_errm);
End ;
END LOOP;
DBMS_OUTPUT.put_line ('Test 5') ;
COMMIT;
-- Output the results
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
SUBSTR ('Error ' || TO_CHAR (SQLCODE) || ': ' || SQLERRM, 1, 255));
RAISE;
END;
/
(v_emp_no varchar2 default null, v_update_mode varchar2 default null )
AS
vl$validate_mode BOOLEAN := FALSE;
vl$assignment_id per_assignments_f.assignment_id%TYPE;
vl$assignment_number VARCHAR2 (50);
vl$set_of_books_name VARCHAR2 (200) := 'COMEX MXN BOOK';
vl$set_of_books NUMBER;
p_employee_number VARCHAR2 (50) := '$__19A1099000001'; --'$__19A1099000001';
vl$person_id NUMBER;
vl$object_version_number NUMBER;
vl$job_title VARCHAR2 (200);
vl$job_id NUMBER;
vl$job_name VARCHAR2 (200) := 'ELECTRICIAN';
vl$location_id NUMBER := 127;
vl$called_from_mass_update BOOLEAN := FALSE;
V_people_group_id NUMBER;
V_object_version_number NUMBER;
V_special_ceiling_step_id NUMBER;
V_group_name VARCHAR2 (4000);
V_effective_start_date DATE;
V_effective_end_date DATE;
V_org_now_no_manager_warning BOOLEAN;
v_other_manager_warning BOOLEAN;
v_spp_delete_warning BOOLEAN;
v_entries_changed_warning VARCHAR2 (4000);
v_tax_district_changed_warning BOOLEAN;
vl$fb_entra BOOLEAN := TRUE;
v_concatenated_segments VARCHAR2 (200);
v_gsp_post_process_warning VARCHAR2 (200);
v_soft_coding_keyflex_id NUMBER;
my_errcode NUMBER;
my_errm VARCHAR2(32000);
v_effective_date DATE := '01-Jan-2013';
v_datetrack_update_mode VARCHAR2 (50) := 'UPDATE'; -- CORRECTION|UPDATE
CURSOR c1
IS
SELECT
( select organization_id from hr_all_organization_units where type ='LE'
and name = v.LEGAL_EMPLOYER )
LEGAL_EMPLOYER,
(SELECT assignment_id
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND a.EMPLOYEE_NUMBER = v.employee_code
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND ROWNUM = 1)
assignment_id,
(SELECT b.effective_start_date
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND a.EMPLOYEE_NUMBER = v.employee_code
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND ROWNUM = 1)
ASG_EFF_ST_date,
(SELECT B.OBJECT_VERSION_NUMBER
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND a.EMPLOYEE_NUMBER = v.employee_code
AND ROWNUM = 1)
obj_ver_no,
v.employee_code,
(SELECT POSITION_ID
FROM HR_ALL_POSITIONS_F
WHERE NAME = v.POSITION
and ROWNUM = 1 )
position_id,
(SELECT job_ID
FROM PER_JOBS
WHERE NAME = v.JOB_TITLE
and ROWNUM = 1 )
job_id,
v.POSITION,
(SELECT ORGANIZATION_ID
FROM hr_all_organization_units
WHERE NAME = v.ORGANIZATION)
org_id,
(SELECT LOCATION_ID
FROM hr_locations_all
WHERE LOCATION_CODE = v.location)
location,
ASSIGNMENT_STATUS,
STAFF_TYPE,
SHIFT_HOURS || ' Hours' SHIFT_HOURS,
DECODE (ACCOMMODATION_PROVIDED,
'Yes', 'Y',
'yes', 'Y',
'No', 'N',
'no', 'N',
Null)
ACCOMMODATION_PROVIDED,
DECODE (TRANSPORTAION_PROVIDED,
'Yes', 'Y',
'yes', 'Y',
'No', 'N',
'no', 'N',
Null)
TRANSPORTAION_PROVIDED,
DECODE (OVERTIME_ELIGIBLE,'yes', 'Y', 'Yes', 'Y', 'No', 'N', 'no', 'N', Null)
OVERTIME_ELIGIBLE,
DECODE (SPECIAL_OT_ELIGIBLE, 'yes', 'Y', 'Yes', 'Y', 'No', 'N', 'no', 'N', Null)
SPECIAL_OT_ELIGIBLE,
SPECIAL_OT_RATE,
DECODE (TIME_SHEET_REQUIRED, 'yes', 'Y', 'Yes', 'Y', 'No', 'N', 'no', 'N', Null)
TIME_SHEET_REQUIRED,
TO_DATE(ASSIGNMENT_EFFICITIVE_DATE,'MM/DD/YYYY')
ASSIGNMENT_EFFICITIVE_DATE,
(SELECT person_id
FROM per_all_people_f
WHERE supervisor = employee_number AND ROWNUM = 1)
supervisor_id,
(SELECT assignment_id
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND a.EMPLOYEE_NUMBER = v.supervisor
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND ROWNUM = 1)
sup_assign_id,
supervisor,
Payroll_id,
People_grp_id
FROM xx_assign_upload v
WHERE v.employee_code like nvl(v_emp_no,'%')
and Record_criteria_processed_flag is null ;
BEGIN
if v_update_mode is not null then
v_datetrack_update_mode := v_update_mode;
End if;
FOR i IN c1
LOOP
Begin
v_effective_date := '01-Jan-2013';
if v_effective_date <= i.asg_eff_st_date then
v_effective_date := i.asg_eff_st_date ;
v_datetrack_update_mode := 'CORRECTION' ;
Else
v_datetrack_update_mode := 'UPDATE' ;
End if ;
V_people_group_id := null ;
V_object_version_number := i.obj_ver_no;
V_special_ceiling_step_id := null;
V_group_name := null;
V_effective_start_date := null;
V_effective_end_date := null;
v_entries_changed_warning := null;
v_concatenated_segments := null;
v_gsp_post_process_warning := null;
-- v_soft_coding_keyflex_id := i.LEGAL_EMPLOYER;
v_soft_coding_keyflex_id := null;
-- v_effective_date := I.ASG_EFF_ST_date;
hr_assignment_api.update_emp_asg_criteria (
p_effective_date => v_effective_date,
p_datetrack_update_mode => v_datetrack_update_mode,
p_assignment_id => i.ASSIGNMENT_ID,
p_validate => False,
p_called_from_mass_update => NULL,
p_grade_id => NULL,
p_position_id => i.POSITION_ID,
p_job_id => i.job_id,
p_payroll_id => i.Payroll_id,
p_location_id => i.LOCATION,
p_organization_id => i.ORG_ID,
p_pay_basis_id => NULL,
p_segment1 => i.STAFF_TYPE,
p_segment2 => i.SHIFT_HOURS,
p_SEGMENT3 => i.ACCOMMODATION_PROVIDED, -- Accommodation Provided
p_SEGMENT4 => i.TRANSPORTAION_PROVIDED, -- Transportaion Provided
p_SEGMENT5 => i.OVERTIME_ELIGIBLE, -- Overtime Eligible
p_SEGMENT6 => i.SPECIAL_OT_ELIGIBLE, -- Special OT Eligible
p_segment7 => i.SPECIAL_OT_RATE, -- Special OT Rate,
p_SEGMENT8 => i.TIME_SHEET_REQUIRED, -- Time Sheet Required
p_SEGMENT9 => null,
p_supervisor_assignment_id => i.sup_assign_id ,
p_object_version_number => v_object_version_number,
p_special_ceiling_step_id => v_special_ceiling_step_id,
p_people_group_id => v_people_group_id,
p_soft_coding_keyflex_id => v_soft_coding_keyflex_id,
p_group_name => v_group_name,
p_effective_start_date => v_effective_start_date,
p_effective_end_date => v_effective_end_date,
p_org_now_no_manager_warning => v_org_now_no_manager_warning,
p_other_manager_warning => v_other_manager_warning,
p_spp_delete_warning => v_spp_delete_warning,
p_entries_changed_warning => v_entries_changed_warning,
p_tax_district_changed_warning => v_tax_district_changed_warning,
p_concatenated_segments => v_concatenated_segments,
p_gsp_post_process_warning => v_gsp_post_process_warning);
-- If API is Successfull
Update xx_assign_upload set Record_criteria_processed_flag = 'Y' , Record_criteria_processed_dt = sysdate
where employee_code = i.employee_code ;
dbms_output.put_line( 'Employee code :'|| i.employee_code || ' Updated ');
Exception
When Others then
my_errcode := SQLCODE;
my_errm := SQLERRM;
update xx_assign_upload set Record_err_message = substr(('Employee code :'|| i.employee_code||'Ass Eff dt :'||
to_char(I.ASG_EFF_ST_date,'DD-MON-YYYY') ||
'Position id ' || i.POSITION_ID|| 'Error code ' || my_errcode || ': ' || my_errm),1, 2000),
Record_processed_dt = sysdate
where employee_code = i.employee_code ;
dbms_output.put_line( 'Employee code :'|| i.employee_code||'Ass Eff dt :'||
to_char(I.ASG_EFF_ST_date,'DD-MON-YYYY') ||
'Position id ' || i.POSITION_ID||
'Error code ' || my_errcode || ': ' || my_errm);
End ;
END LOOP;
COMMIT;
-- Output the results
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (
SUBSTR ('Error ' || TO_CHAR (SQLCODE) || ': ' || SQLERRM, 1, 255));
RAISE;
END;
/
CREATE OR REPLACE procedure APPS.XX_ASSIGN_UPLOAD_PROC ( v_emp_no varchar2 default null , v_update_mode varchar2 default null ) as
lc_dt_ud_mode VARCHAR2 (100) := NULL;
ln_assignment_id NUMBER;
ln_supervisor_id NUMBER;
ln_object_number NUMBER;
ln_people_group_id NUMBER;
-- Out Variables for Find Date Track Mode API
-- -----------------------------------------------------------------
lb_correction BOOLEAN;
lb_update BOOLEAN;
lb_update_override BOOLEAN;
lb_update_change_insert BOOLEAN;
-- Out Variables for Update Employee Assignment API
-- ----------------------------------------------------------------------------
ln_soft_coding_keyflex_id HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
lc_concatenated_segments VARCHAR2 (2000);
ln_comment_id PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
lb_no_managers_warning BOOLEAN;
-- Out Variables for Update Employee Assgment Criteria
-- -------------------------------------------------------------------------------
ln_special_ceiling_step_id PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
lc_group_name VARCHAR2 (30);
ld_effective_start_date PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
ld_effective_end_date PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
lb_org_now_no_manager_warning BOOLEAN;
lb_other_manager_warning BOOLEAN;
lb_spp_delete_warning BOOLEAN;
lc_entries_changed_warning VARCHAR2 (30);
lb_tax_district_changed_warn BOOLEAN;
my_errcode NUMBER;
my_errm VARCHAR2(32000);
v_cagr_grade_def_id number ;
v_cagr_concatenated_segments varchar2(200);
V_concatenated_segments varchar2 (200);
V_soft_coding_keyflex_id number ; -- bug 2359997
V_comment_id number;
V_effective_start_date date;
V_effective_end_date date;
v_no_managers_warning boolean;
v_other_manager_warning boolean;
v_hourly_salaried_warning boolean;
v_gsp_post_process_warning varchar2(4000);
v_effective_date DATE := '01-Jan-2013';
v_datetrack_update_mode VARCHAR2 (50) := 'UPDATE'; -- CORRECTION|UPDATE
CURSOR c1
IS
SELECT
( select organization_id from hr_all_organization_units where type ='LE'
and name = v.LEGAL_EMPLOYER )
LEGAL_EMPLOYER,
(SELECT assignment_id
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND a.EMPLOYEE_NUMBER = v.employee_code
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND ROWNUM = 1)
assignment_id,
(SELECT b.effective_start_date
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND a.EMPLOYEE_NUMBER = v.employee_code
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND ROWNUM = 1)
ASG_EFF_ST_date,
(SELECT B.OBJECT_VERSION_NUMBER
FROM per_all_people_f a, per_all_assignments_f b
WHERE a.person_id = b.person_id
AND trunc(SYSDATE) BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND a.EMPLOYEE_NUMBER = v.employee_code
AND ROWNUM = 1)
obj_ver_no,
(SELECT pcf1.CONTRACT_ID
from per_all_people_f a,per_contracts_f pcf1
Where pcf1.Status ='A-ACTIVE'
and a.person_id = pcf1.person_id
AND trunc(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
AND a.EMPLOYEE_NUMBER = v.employee_code ) contract_id,
v.employee_code,
(SELECT POSITION_ID
FROM HR_ALL_POSITIONS_F
WHERE NAME = v.POSITION
and ROWNUM = 1 )
position_id,
(SELECT job_ID
FROM PER_JOBS
WHERE NAME = v.JOB_TITLE
and ROWNUM = 1 )
job_id,
v.POSITION,
(SELECT ORGANIZATION_ID
FROM hr_all_organization_units
WHERE NAME = v.ORGANIZATION and rownum=1)
org_id,
(SELECT LOCATION_ID
FROM hr_locations_all
WHERE LOCATION_CODE = v.location and rownum = 1)
location,
ASSIGNMENT_STATUS,
STAFF_TYPE,
SHIFT_HOURS || ' Hours' SHIFT_HOURS,
DECODE (ACCOMMODATION_PROVIDED,
'Yes', 'Y',
'No', 'N',
'Null')
ACCOMMODATION_PROVIDED,
DECODE (TRANSPORTAION_PROVIDED,
'Yes', 'Y',
'No', 'N',
'Null')
TRANSPORTAION_PROVIDED,
DECODE (OVERTIME_ELIGIBLE, 'Yes', 'Y', 'No', 'N', 'Null')
OVERTIME_ELIGIBLE,
DECODE (SPECIAL_OT_ELIGIBLE, 'Yes', 'Y', 'No', 'N', 'Null')
SPECIAL_OT_ELIGIBLE,
DECODE (SPECIAL_OT_RATE, 'Yes', 'Y', 'No', 'N', 'Null')
SPECIAL_OT_RATE,
DECODE (TIME_SHEET_REQUIRED, 'Yes', 'Y', 'No', 'N', 'Null')
TIME_SHEET_REQUIRED,
TO_DATE(ASSIGNMENT_EFFICITIVE_DATE,'MM/DD/YYYY')
ASSIGNMENT_EFFICITIVE_DATE,
(SELECT person_id
FROM per_all_people_f
WHERE supervisor = employee_number AND ROWNUM = 1)
supervisor_id,
supervisor
FROM xx_assign_upload v
WHERE v.employee_code like nvl(v_emp_no,'%')
and Record_processed_flag is null;
BEGIN
if v_update_mode is not null then
v_datetrack_update_mode := v_update_mode;
End if;
FOR i IN c1
LOOP
Begin
v_effective_date := '01-Jan-2013';
if v_effective_date <= i.asg_eff_st_date then
v_effective_date := i.asg_eff_st_date ;
v_datetrack_update_mode := 'CORRECTION' ;
Else
v_datetrack_update_mode := 'UPDATE' ;
End if ;
DBMS_OUTPUT.put_line ('Test 2') ;
ln_object_number := NVL(i.OBJ_VER_NO,1) ;
V_soft_coding_keyflex_id := null ;
/* v_effective_date DATE := '01-Jan-2013';
v_datetrack_update_mode VARCHAR2 (50) := 'UPDATE'; -- CORRECTION|UPDATE*/
hr_assignment_api.update_emp_asg ( -- Input data elements
-- ------------------------------
p_validate => False,
p_effective_date => v_effective_date,
p_datetrack_update_mode => v_datetrack_update_mode,
p_assignment_id => to_number(i.ASSIGNMENT_ID),
p_supervisor_id => to_number(i.supervisor_id),
-- p_change_reason => NULL,
-- p_manager_flag => NULL,
-- p_bargaining_unit_code => NULL,
-- p_labour_union_member_flag => NULL,
p_segment1 => I.LEGAL_EMPLOYER,
p_segment2 => Null,
p_segment3 => Null,
p_segment4 => Null,
p_segment5 => Null,
p_segment7 => Null,
p_segment8 => Null,
p_contract_id => i.contract_id,
-- p_segment3 => NULL,
-- p_normal_hours => NULL,
-- p_frequency => NULL,
-- Output data elements
-- -------------------------------
p_object_version_number => ln_object_number,
p_cagr_grade_def_id => v_cagr_grade_def_id,
p_cagr_concatenated_segments => v_cagr_concatenated_segments,
p_concatenated_segments => v_concatenated_segments,
p_soft_coding_keyflex_id => v_soft_coding_keyflex_id,
p_comment_id => v_comment_id,
p_effective_start_date => v_effective_start_date,
p_effective_end_date => v_effective_end_date,
p_no_managers_warning => v_no_managers_warning,
p_other_manager_warning => v_other_manager_warning
);
-- DBMS_OUTPUT.put_line (' ln_comment_id' || ln_comment_id);
-- DBMS_OUTPUT.put_line (' lb_no_managers_warning' || lb_no_managers_warning);
-- DBMS_OUTPUT.put_line (' lb_other_manager_warning' || lb_other_manager_warning);
Update xx_assign_upload set Record_processed_flag = 'Y' , Record_processed_dt = sysdate
where employee_code = i.employee_code ;
dbms_output.put_line( 'Employee code :'|| i.employee_code || ' Updated ');
Exception
When Others then
my_errcode := SQLCODE;
my_errm := SQLERRM;
update xx_assign_upload set Record_err_message = substr(('Employee code :'|| i.employee_code||'Ass Eff dt :'||
to_char(I.ASG_EFF_ST_date,'DD-MON-YYYY') ||
'Position id ' || i.POSITION_ID|| 'Error code ' || my_errcode || ': ' || my_errm),1, 2000),
Record_processed_dt = sysdate
where employee_code = i.employee_code ;
dbms_output.put_line( 'Employee code :'|| i.employee_code||'Ass Eff dt :'||
to_char(I.ASG_EFF_ST_date,'DD-MON-YYYY') ||
'Position id ' || i.POSITION_ID||
'Error code ' || my_errcode || ': ' || my_errm);
End ;
END LOOP;
DBMS_OUTPUT.put_line ('Test 5') ;
COMMIT;
-- Output the results
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
SUBSTR ('Error ' || TO_CHAR (SQLCODE) || ': ' || SQLERRM, 1, 255));
RAISE;
END;
/
this is very nice article and very good information for Oracle Learners. our Cubtraining also provide all Oracle Courses
ReplyDeletei am getting -20001ORA-20001: The primary key specified is invalid
ReplyDeleteCause: The primary key values specified are invalid and do not exist in the schema.
Action: Check the primary key values before attempting to carry out the operation again this error while using hr_assignment_api.update_emp_asg_criteria.
i am passign assignemnt id payroll id and org id effetcve start date same as employee start date. please help