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;
Thursday 7 July 2011
Remove special CH from Temp table
declare
TYPE cur_typ IS REF CURSOR;
c_rec cur_typ;
l_table_name varchar2(1000);
l_column_name varchar2(1000);
query_str varchar2(1000);
update_query_str varchar2(1000);
begin
l_table_name:='TEMP_TABLE_NAME';
query_str := 'SELECT column_name FROM ALL_COL_COMMENTS where table_name =:p_table_name ';
begin
OPEN c_rec FOR query_str using l_table_name ;
LOOP
FETCH c_rec INTO l_column_name;
EXIT WHEN c_rec%NOTFOUND;
dbms_output.put_line(l_table_name||' '||l_column_name);
update_query_str:='update '||l_table_name||' set "'||l_column_name ||'" = substr("'||l_column_name||'",1, instr("'||l_column_name||'",CHR(13))-1) where instr("'||l_column_name||'",CHR(13)) <>0' ;
--update_query_str:='update '||l_table_name||' set "'||l_column_name ||'" = trunc(;
EXECUTE IMMEDIATE update_query_str ;
END LOOP;
CLOSE c_rec;
end;
end;
TYPE cur_typ IS REF CURSOR;
c_rec cur_typ;
l_table_name varchar2(1000);
l_column_name varchar2(1000);
query_str varchar2(1000);
update_query_str varchar2(1000);
begin
l_table_name:='TEMP_TABLE_NAME';
query_str := 'SELECT column_name FROM ALL_COL_COMMENTS where table_name =:p_table_name ';
begin
OPEN c_rec FOR query_str using l_table_name ;
LOOP
FETCH c_rec INTO l_column_name;
EXIT WHEN c_rec%NOTFOUND;
dbms_output.put_line(l_table_name||' '||l_column_name);
update_query_str:='update '||l_table_name||' set "'||l_column_name ||'" = substr("'||l_column_name||'",1, instr("'||l_column_name||'",CHR(13))-1) where instr("'||l_column_name||'",CHR(13)) <>0' ;
--update_query_str:='update '||l_table_name||' set "'||l_column_name ||'" = trunc(;
EXECUTE IMMEDIATE update_query_str ;
END LOOP;
CLOSE c_rec;
end;
end;
Employee Update Script
DECLARE
v_comment_id NUMBER;
v_blood_grp VARCHAR2(10);
v_object_version_number NUMBER;
v_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
v_asg_eff_start_date DATE;
v_business_group_id NUMBER := 81;
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_full_name VARCHAR2(240);
v_no_managers_warning BOOLEAN;
v_other_manager_warning BOOLEAN;
v_hourly_salaried_warning BOOLEAN;
v_employee_number VARCHAR2(30);
v_nationality VARCHAR2(240);
BEGIN
FOR c_rec IN
(SELECT t.rowid row_id,
t.*
FROM XXARJ_HR_CNV_PEOPLE_TMP t
WHERE NVL(PROCESS_FLAG,'N') IN ('N','E')
--AND employee_number IN ('P1002039')
)
LOOP
v_row_count := v_row_count +1;
v_person_id := NULL;
v_comment_id := NULL;
v_error_msg1 := NULL;
v_object_version_number := NULL;
v_asg_eff_start_date :=NULL;
v_effective_start_date :=NULL;
v_effective_end_date :=NULL;
v_no_managers_warning :=NULL;
v_other_manager_warning := NULL;
v_full_name := NULL;
v_hourly_salaried_warning :=NULL;
v_employee_number := NULL;
v_nationality :=null;
BEGIN
SELECT ppf.person_id, ppf.employee_number,
ppf.effective_start_date,
ppf.object_version_number
INTO v_person_id,v_employee_number,
v_asg_eff_start_date,
v_object_version_number
FROM PER_ALL_PEOPLE_F ppf
WHERE ppf.employee_number = c_rec.employee_number
AND ppf.business_group_id = 81
AND ppf.current_employee_flag = 'Y'
AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := ' Employee Not Found.';
v_err_count := v_err_count +1;
UPDATE XXARJ_HR_CNV_PEOPLE_TMP
SET ERROR_MESSAGE = v_error_msg1,
PROCESS_FLAG = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
END;
IF v_error_msg1 IS NULL THEN
BEGIN
--Validate Nationality
SELECT lookup_code
INTO v_nationality
FROM apps.hr_lookups
WHERE upper(lookup_type) = 'NATIONALITY'
AND upper(meaning) = upper(c_rec.nationality)
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := v_error_msg1||' Invalid Nationality.';
END;
/* BEGIN
--Find PAN Number
SELECT lookup_code
INTO v_blood_grp
FROM hr_lookups
WHERE upper(lookup_type) = 'BLOOD_TYPE'
AND lookup_code = c_rec.Blood_type;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := 'Invalid Blood Group.';
END;*/
BEGIN
-- ADDRESS API START
apps.HR_PERSON_API.update_person(
p_validate => false
,p_effective_date => v_asg_eff_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_object_version_number => v_object_version_number
,p_email_address => c_rec.email_address
,p_person_id => v_person_id
,p_marital_status =>c_rec.marital_status
,p_nationality =>v_nationality --c_rec.nationality
,p_country_of_birth =>replace(c_rec.country_of_birth,'India','IN')
,p_employee_number =>v_employee_number
,p_per_information4 =>c_rec.PAN_NUMBER
,p_per_information7 => c_rec.residential_status
,p_per_information8 =>c_rec.PF_NUMBER
,p_comment_id => v_comment_id
,p_effective_start_date => v_effective_start_date
,p_effective_end_date => v_effective_end_date
,p_full_name => v_full_name
,p_name_combination_warning => v_no_managers_warning
,p_assign_payroll_warning => v_other_manager_warning
,p_orig_hire_warning =>v_hourly_salaried_warning
);
--TRACE1('ADDRESS UPDATED'||V_ADDRESS_ID);
UPDATE XXARJ_HR_CNV_PEOPLE_TMP
SET ERROR_MESSAGE = v_error_msg1,
PROCESS_FLAG = 'S',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := SUBSTR(SQLERRM,1,200);
DBMS_OUTPUT.PUT_LINE('Total Records errored out: '||SQLERRM);
v_err_count := v_err_count +1;
UPDATE XXARJ_HR_CNV_PEOPLE_TMP
SET ERROR_MESSAGE = v_error_msg1,
PROCESS_FLAG = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
END;
ELSE
v_err_count := v_err_count +1;
UPDATE XXARJ_HR_CNV_PEOPLE_TMP
SET ERROR_MESSAGE = v_error_msg1,
PROCESS_FLAG = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
END IF;
IF MOD(v_row_count,20) = 0 THEN
COMMIT;
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;
v_comment_id NUMBER;
v_blood_grp VARCHAR2(10);
v_object_version_number NUMBER;
v_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
v_asg_eff_start_date DATE;
v_business_group_id NUMBER := 81;
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_full_name VARCHAR2(240);
v_no_managers_warning BOOLEAN;
v_other_manager_warning BOOLEAN;
v_hourly_salaried_warning BOOLEAN;
v_employee_number VARCHAR2(30);
v_nationality VARCHAR2(240);
BEGIN
FOR c_rec IN
(SELECT t.rowid row_id,
t.*
FROM XXARJ_HR_CNV_PEOPLE_TMP t
WHERE NVL(PROCESS_FLAG,'N') IN ('N','E')
--AND employee_number IN ('P1002039')
)
LOOP
v_row_count := v_row_count +1;
v_person_id := NULL;
v_comment_id := NULL;
v_error_msg1 := NULL;
v_object_version_number := NULL;
v_asg_eff_start_date :=NULL;
v_effective_start_date :=NULL;
v_effective_end_date :=NULL;
v_no_managers_warning :=NULL;
v_other_manager_warning := NULL;
v_full_name := NULL;
v_hourly_salaried_warning :=NULL;
v_employee_number := NULL;
v_nationality :=null;
BEGIN
SELECT ppf.person_id, ppf.employee_number,
ppf.effective_start_date,
ppf.object_version_number
INTO v_person_id,v_employee_number,
v_asg_eff_start_date,
v_object_version_number
FROM PER_ALL_PEOPLE_F ppf
WHERE ppf.employee_number = c_rec.employee_number
AND ppf.business_group_id = 81
AND ppf.current_employee_flag = 'Y'
AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := ' Employee Not Found.';
v_err_count := v_err_count +1;
UPDATE XXARJ_HR_CNV_PEOPLE_TMP
SET ERROR_MESSAGE = v_error_msg1,
PROCESS_FLAG = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
END;
IF v_error_msg1 IS NULL THEN
BEGIN
--Validate Nationality
SELECT lookup_code
INTO v_nationality
FROM apps.hr_lookups
WHERE upper(lookup_type) = 'NATIONALITY'
AND upper(meaning) = upper(c_rec.nationality)
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := v_error_msg1||' Invalid Nationality.';
END;
/* BEGIN
--Find PAN Number
SELECT lookup_code
INTO v_blood_grp
FROM hr_lookups
WHERE upper(lookup_type) = 'BLOOD_TYPE'
AND lookup_code = c_rec.Blood_type;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := 'Invalid Blood Group.';
END;*/
BEGIN
-- ADDRESS API START
apps.HR_PERSON_API.update_person(
p_validate => false
,p_effective_date => v_asg_eff_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_object_version_number => v_object_version_number
,p_email_address => c_rec.email_address
,p_person_id => v_person_id
,p_marital_status =>c_rec.marital_status
,p_nationality =>v_nationality --c_rec.nationality
,p_country_of_birth =>replace(c_rec.country_of_birth,'India','IN')
,p_employee_number =>v_employee_number
,p_per_information4 =>c_rec.PAN_NUMBER
,p_per_information7 => c_rec.residential_status
,p_per_information8 =>c_rec.PF_NUMBER
,p_comment_id => v_comment_id
,p_effective_start_date => v_effective_start_date
,p_effective_end_date => v_effective_end_date
,p_full_name => v_full_name
,p_name_combination_warning => v_no_managers_warning
,p_assign_payroll_warning => v_other_manager_warning
,p_orig_hire_warning =>v_hourly_salaried_warning
);
--TRACE1('ADDRESS UPDATED'||V_ADDRESS_ID);
UPDATE XXARJ_HR_CNV_PEOPLE_TMP
SET ERROR_MESSAGE = v_error_msg1,
PROCESS_FLAG = 'S',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := SUBSTR(SQLERRM,1,200);
DBMS_OUTPUT.PUT_LINE('Total Records errored out: '||SQLERRM);
v_err_count := v_err_count +1;
UPDATE XXARJ_HR_CNV_PEOPLE_TMP
SET ERROR_MESSAGE = v_error_msg1,
PROCESS_FLAG = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
END;
ELSE
v_err_count := v_err_count +1;
UPDATE XXARJ_HR_CNV_PEOPLE_TMP
SET ERROR_MESSAGE = v_error_msg1,
PROCESS_FLAG = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
END IF;
IF MOD(v_row_count,20) = 0 THEN
COMMIT;
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;
Wednesday 6 July 2011
Add Responsibility from backend
select * from fnd_responsibility_tl where responsibility_name like 'Sys%Adm%';
select * from fnd_responsibility where responsibility_id = 20420 --20872; 20420
select * from fnd_application where application_id = 1 --178; 1
BEGIN
fnd_user_pkg.addresp ('USERNAME'
, 'SYSADMIN'
, 'SYSTEM_ADMINISTRATOR'
, 'STANDARD'
, 'Add Responsibility to USER'
, '01-JAN-1900'
, null
);
COMMIT;
DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Responsibility is not added due to'|| SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;
---------------------------------------------------------------------------
BEGIN
fnd_user_pkg.DelResp ('CONVERSION'
, 'SYSADMIN'
, 'SYSTEM_ADMINISTRATOR'
, 'STANDARD'
);
COMMIT;
DBMS_OUTPUT.put_line ('Responsibility Deleted Successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Responsibility is not deleted due to'|| SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;
Subscribe to:
Posts (Atom)