DECLARE
v_comment_id NUMBER;
v_concatenated_segments VARCHAR2(200);
v_business_group_id NUMBER := 1428;
v_group_name VARCHAR2(1000);
v_effective_start_date DATE;
v_effective_end_date DATE;
v_row_count NUMBER := 0;
v_err_count NUMBER := 0;
v_error_msg1 VARCHAR2(240);
v_cagr_grade_def_id NUMBER;
v_no_managers_warning BOOLEAN;
v_other_manager_warning BOOLEAN;
v_cagr_concatenated_segments NUMBER;
v_hourly_salaried_warning BOOLEAN;
v_gsp_post_process_warning VARCHAR2(1000);
v_entries_changed_warning VARCHAR2(1000);
v_tax_district_changed_warning BOOLEAN;
v_salary_basis NUMBER;
v_assignment_id NUMBER;
v_asg_effective_start_date DATE;
v_asg_ovn NUMBER;
v_soft_coding_id NUMBER;
v_empno VARCHAR2(30);
v_count NUMBER;
v_count1 NUMBER;
BEGIN
FOR c_rec IN
(SELECT paf.assignment_id asg_id,
ppf.employee_number,
ppf.attribute1,
paf.effective_start_date,
paf.object_version_number,
paf.soft_coding_keyflex_id
FROM PER_ALL_PEOPLE_F ppf,
PER_ALL_ASSIGNMENTS_F paf
WHERE ppf.person_id = paf.person_id
AND ppf.business_group_id = v_business_group_id
AND paf.pay_basis_id is null
AND paf.payroll_id IS NOT NULL
--AND paf.payroll_id =83
--AND ppf.employee_number LIKE 'P%'
-- AND ppf.employee_number = 'P100977'
--AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date
)
LOOP
v_row_count := v_row_count +1;
v_salary_basis := NULL;
v_concatenated_segments :=NULL;
v_comment_id := NULL;
v_error_msg1 := NULL;
v_effective_start_date :=NULL;
v_effective_end_date :=NULL;
v_no_managers_warning :=NULL;
v_other_manager_warning := NULL;
v_group_name :=NULL;
v_cagr_concatenated_segments := NULL;
v_cagr_grade_def_id :=NULL;
v_hourly_salaried_warning :=NULL;
v_gsp_post_process_warning :=NULL;
v_entries_changed_warning :=NULL;
v_tax_district_changed_warning :=NULL;
v_assignment_id :=NULL;
v_asg_effective_start_date :=NULL;
v_asg_ovn :=NULL;
v_soft_coding_id :=NULL;
v_empno :=NULL;
v_count :=0;
v_count1 :=0;
/*--Check if payroll is attached
BEGIN
SELECT count(*)
INTO v_count1
FROM PER_ALL_ASSIGNMENTS_F paf
WHERE paf.assignment_id = c_rec.asg_id
AND paf.business_group_id = v_business_group_id
AND paf.effective_start_date = c_rec.effective_start_date
AND paf.payroll_id IS NOT NULL
AND paf.payroll_id =83;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 :=v_error_msg1|| SUBSTR(SQLERRM,1,200);
END;
IF v_count1 = 0 THEN
v_error_msg1 :=v_error_msg1||'Payroll Not Attached. ';
END IF;*/
--Validate Salary Basis
BEGIN
SELECT pay_basis_id
INTO v_salary_basis
FROM per_pay_bases
WHERE business_group_id = v_business_group_id
AND upper(NAME) = upper('TCTC Details');
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := v_error_msg1||'Salary Basis Not Found. ';
END;
--Check for Duplicate
SELECT COUNT(*)
INTO v_count
FROM PER_ALL_ASSIGNMENTS_F
WHERE assignment_id = c_rec.asg_id
AND effective_start_date = c_rec.effective_start_date
AND pay_basis_id = v_salary_basis;
IF v_count >0 THEN
v_error_msg1 := 'Salary Basis is already attached to the assignment.';
END IF;
IF v_error_msg1 IS NULL THEN
BEGIN
-- ADDRESS API START
apps.HR_ASSIGNMENT_API.update_emp_asg_criteria(
p_validate => false
,p_effective_date => c_rec.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => c_rec.asg_id
,p_object_version_number => c_rec.object_version_number
,p_soft_coding_keyflex_id => c_rec.soft_coding_keyflex_id
,p_pay_basis_id => v_salary_basis
,p_special_ceiling_step_id => v_cagr_grade_def_id
,p_people_group_id => v_cagr_concatenated_segments
,p_concatenated_segments => v_concatenated_segments
,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_no_managers_warning
,p_other_manager_warning => v_other_manager_warning
,p_spp_delete_warning =>v_hourly_salaried_warning
,p_gsp_post_process_warning => v_gsp_post_process_warning
,p_entries_changed_warning => v_entries_changed_warning
,p_tax_district_changed_warning => v_tax_district_changed_warning );
IF MOD(v_row_count,20) =0 THEN
COMMIT;
END IF;
--TRACE1('ADDRESS UPDATED'||V_ADDRESS_ID);
INSERT
INTO xxarj_salary_proposal
(
ERROR_MESSAGE,
status,
SALARY_BASIS,
NEW_EMPLOYEE_NUMBER,
EMPLOYEE_NUMBER,
pay_basis_id,
CREATION_DATE
)
VALUES
(
v_error_msg1,
'S',
'TCTC Details',
c_rec.employee_number,
c_rec.attribute1,
v_salary_basis,
TRUNC(SYSDATE)
);
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := SUBSTR(SQLERRM,1,200);
v_err_count := v_err_count +1;
INSERT
INTO xxarj_salary_proposal
(
ERROR_MESSAGE,
status,
NEW_EMPLOYEE_NUMBER,
EMPLOYEE_NUMBER,
CREATION_DATE
)
VALUES
(
v_error_msg1,
'E',
c_rec.employee_number,
c_rec.attribute1,
TRUNC(SYSDATE)
);
END;
ELSE
INSERT
INTO xxarj_salary_proposal
(
ERROR_MESSAGE,
status,
NEW_EMPLOYEE_NUMBER,
EMPLOYEE_NUMBER,
CREATION_DATE
)
VALUES
(
v_error_msg1,
'E',
c_rec.employee_number,
c_rec.attribute1,
TRUNC(SYSDATE)
);
v_err_count := v_err_count +1;
END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE
(
'************************************************************'
)
;
DBMS_OUTPUT.PUT_LINE
(
'Total Records to be loaded: '||v_row_count
)
;
DBMS_OUTPUT.PUT_LINE
(
'Total Records errored out: '||v_err_count
)
;
DBMS_OUTPUT.PUT_LINE
(
'************************************************************'
)
;
END;
No comments:
Post a Comment