Employee Bank Details API

For updating employee bank details following code can be used:

PROCEDURE XXHR_BANK_DETAILS
(ERRBUF OUT NOCOPY varchar2,
RETCODE OUT NOCOPY VARCHAR2 )
/* =====================================================================
— NAME : XXHR_BANK_DETAILS

— PROGRAM TYPE : Procedure

— DESCRIPTION : This is used to capture Bank Details
— INPUTS : None
— OUTPUTS : None
— ===================================================================*/

AS

CURSOR CUR_BANK
is
SELECT
EUREHKA_NUMBER,
START_DATE,
BANK_CODE,
BANK_ACC_NUMBER,
BANK_COUNTRY,
PAYMENT_CURRENCY,
IBAN,
SWIFT_CODE,
— BENEFICIARY,
— PURCHASE_ORDER_NUMBER,
— SUPPLIER_NAME,
— PURCHASE_ORDER_LINE,
RECORD_NO
FROM
XXHR_EMP_BANK_STG
WHERE
PROCESS_FLAG IS NULL;
— and EUREHKA_NUMBER = ‘91400000’;

l_num_record_no number;
l_var_process varchar2(100);
l_num_priority number;
l_num_assign_id number;
l_var_pay_method varchar2(100);
l_num_pay_method_id number;
l_var_bank_country varchar2(100);
l_var_bank_currency varchar2(100);
l_err_msg varchar2(32767);
l_num_per_payment_method_id number;
l_num_external_account_id number;
l_num_object_version_number number;
l_dat_effective_start_date DATE;
l_dat_effective_end_date date;
l_num_comment_id number;
l_num_person_id number;
l_var_person_type varchar2(100);
l_num_vendor_id number;
l_num_vendor_site_id number;
l_num_po_header_id number;
l_num_line_id number;
l_bool_org_no_manager_warning boolean;
l_dte_effective_start_date date;
l_dte_effective_end_date date;
l_num_comment_id1 number;
l_bool_no_managers_warning BOOLEAN;
l_bool_other_manager_warning boolean;
l_num_soft_coding_keyflex_id number;
l_var_concatenated_segments varchar2(100);
l_bool_hourly_salaried_warning boolean;
l_num_id_flex_num number;
sql_err varchar2(30000);
l_num_person_analysis_id number;
— l_num_id_flex_num number;
l_num_analysis_criteria_id number;
l_num_pea_ovn NUMBER;
l_var_payroll_present VARCHAR2(100);
l_num_payroll_id NUMBER;
l_dat_hire_date DATE;
L_NUM_FAILED_RECORDS NUMBER;
l_num_successful_records number;
l_dat_effective date;

BEGIN
FOR R_BANK IN CUR_BANK
loop
l_num_record_no :=NULL;
l_var_process :=NULL;
l_num_priority :=NULL;
l_num_assign_id :=NULL;
l_var_pay_method :=NULL;
l_num_pay_method_id :=NULL;
l_var_bank_country :=NULL;
l_var_bank_currency :=NULL;
l_err_msg :=NULL;
l_num_per_payment_method_id :=NULL;
l_num_external_account_id :=NULL;
l_num_object_version_number :=NULL;
l_dat_effective_start_date :=NULL;
l_dat_effective_end_date :=NULL;
l_num_comment_id :=NULL;
l_num_person_id :=NULL;
l_var_person_type :=NULL;
l_num_vendor_id :=NULL;
l_num_vendor_site_id :=NULL;
l_num_po_header_id :=NULL;
l_num_line_id :=NULL;
l_bool_org_no_manager_warning :=NULL;
l_dte_effective_start_date :=NULL;
l_dte_effective_end_date :=NULL;
l_num_comment_id1 :=NULL;
l_bool_no_managers_warning :=NULL;
l_bool_other_manager_warning :=NULL;
l_num_soft_coding_keyflex_id :=NULL;
l_var_concatenated_segments :=NULL;
l_bool_hourly_salaried_warning :=NULL;
l_num_id_flex_num :=NULL;
sql_err :=NULL;
l_num_person_analysis_id :=NULL;
— l_num_id_flex_num :=NULL;
l_num_analysis_criteria_id :=NULL;
l_num_pea_ovn :=NULL;
l_var_payroll_present :=NULL;
l_num_payroll_id :=NULL;
l_dat_hire_date :=null;
l_dat_effective :=NULL;
l_num_record_no := r_bank.record_no;

BEGIN
SELECT paaf.assignment_id, papf.person_id , paaf.object_version_number, payroll_id , papf.start_date
INTO l_num_assign_id ,l_num_person_id, l_num_object_version_number, l_num_payroll_id, l_dat_hire_date
from
per_all_people_f papf,
per_all_assignments_f paaf
WHERE
papf.person_id = paaf.person_id
AND papf.attribute1 = r_bank.EUREHKA_NUMBER
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);
EXCEPTION
WHEN OTHERS THEN
UPDATE XXHR_EMP_BANK_STG
set process_flag = ‘E’,
ERROR_MSG = ERROR_MSG ||’–‘||’unable to fetch EMP’
WHERE RECORD_NO = l_num_record_no;

COMMIT;
l_var_process := ‘N’;
fnd_file.put_line (fnd_file.log, ‘Unable to fetch Emp’ );
END;

/*effective date– since some emp do not have their payroll attached date from date of hire. for attaching bank details to emp
payroll is mandatory*/
begin
select min(paaf.effective_start_date)
INTO l_dat_effective
from
per_all_people_f papf,
per_all_assignments_f paaf
WHERE
papf.person_id = paaf.person_id
AND papf.attribute1 = r_bank.EUREHKA_NUMBER
and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(papf.effective_end_date)
and payroll_id is not null;
— AND trunc(SYSDATE) BETWEEN trunc(paaf.effective_start_date) AND trunc(paaf.effective_end_date);
EXCEPTION
WHEN OTHERS THEN
UPDATE XXHR_EMP_BANK_STG
set process_flag = ‘E’,
ERROR_MSG = ERROR_MSG ||’–‘||’unable to fetch effective date’
WHERE RECORD_NO = l_num_record_no;

COMMIT;
l_var_process := ‘N’;
fnd_file.put_line (fnd_file.log, ‘Unable to fetch Emp’ );
END;

/*
================================================================================
–Get Person Type
================================================================================
*/

BEGIN
select HR_PERSON_TYPE_USAGE_INFO.get_user_person_type(l_dat_hire_date,l_num_person_id)
into l_var_person_type
from dual;
exception when others then
fnd_file.put_line (fnd_file.log, ‘Unable to fetch Person_type’ );
l_var_process := ‘N’;
UPDATE XXHR_EMP_BANK_STG
set process_flag = ‘E’,
error_msg = error_msg ||’–‘||’unable to fetch Person Type’
where record_no = l_num_record_no;

END;

fnd_file.put_line (fnd_file.log, ‘l_var_person_type’ ||l_var_person_type);
/*
================================================================================
–Get Bank Country
================================================================================
*/
— IF r_bank.bank_country = ‘MY’ THEN
— l_num_priority := 1;
— ELSE
— l_num_priority := 2;
— END IF;

begin
— SELECT DECODE(upper(r_bank.BANK_COUNTRY),’MY’,’Technip MY Primary’,’Technip MY Secondary’ )
— INTO l_var_pay_method
— FROM dual;
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘R_BANK.BANK_COUNTRY’||R_BANK.BANK_COUNTRY );
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘R_BANK.PAYMENT_CURRENCY’||R_BANK.PAYMENT_CURRENCY );
IF (UPPER(R_BANK.BANK_COUNTRY) =’MY’ AND upper(R_BANK.PAYMENT_CURRENCY) =’MYR’)
THEN
L_VAR_PAY_METHOD :=’Technip MY Primary’;
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘primary’);
l_num_priority := ‘1’;
ELSE
L_VAR_PAY_METHOD := ‘Technip MY Secondary’;
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘secondary’);
l_num_priority := ‘2’;
END IF;

EXCEPTION
WHEN OTHERS THEN
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
error_msg = error_msg
||’–‘
||’unable to fetch pay method’
WHERE RECORD_NO = l_num_record_no;
fnd_file.put_line (fnd_file.log, ‘issue in getting pay method name’ );
l_var_process := ‘N’;
end;
/*
================================================================================
–Get Pay method Id
================================================================================
*/
BEGIN
SELECT org_payment_method_id
INTO l_num_pay_method_id
FROM pay_org_payment_methods_f
WHERE org_payment_method_name = l_var_pay_method ;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log, ‘issue in getting pay method ID’ );
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
error_msg = error_msg
||’–‘
||’issue in getting pay method ID’
WHERE record_no = l_num_record_no;
l_var_process := ‘N’;
END;
/*
================================================================================
–Get Bank Country
================================================================================
*/
begin
SELECT TERRITORY_CODE
into l_var_bank_country
FROM
FND_TERRITORIES
where TERRITORY_CODE = r_bank.bank_country;
exception
when others then
fnd_file.put_line (fnd_file.log, ‘Bank Country not found’ );
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
error_msg = error_msg
||’–‘
||’Bank Country not found’
where record_no = l_num_record_no;
l_var_process := ‘N’;

end;
/*
================================================================================
–Get Currency
================================================================================
*/
begin
select ffv.flex_value
into l_var_bank_currency
from
fnd_flex_value_sets ffvs,
fnd_flex_values ffv
where
ffvs.flex_value_set_name =’XXHR_MY_BANK_CURRENCY’
and ffv.flex_value_set_id = ffvs.flex_value_set_id
and ffv.flex_value = r_bank.PAYMENT_CURRENCY;
exception
when others then
fnd_file.put_line (fnd_file.log, ‘Bank Currency not found’ );
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
error_msg = error_msg
||’–‘
||’Bank Currency not found’
where record_no = l_num_record_no;
l_var_process := ‘N’;

END;
if nvl(l_var_process,’Y’)!= ‘N’
THEN
if upper(l_var_person_type) =’EMPLOYEE’or upper(l_var_person_type) =’EX-EMPLOYEE’
THEN
fnd_file.put_line (fnd_file.log, ’employee’ );

/*
================================================================================
–Get Payroll
================================================================================
*/
BEGIN
SELECT payroll_id
into l_var_payroll_present
FROM
pay_all_payrolls_f
WHERE
payroll_id = l_num_payroll_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
ERROR_MSG = ERROR_MSG ||’–‘||’No Payroll’
WHERE RECORD_NO = l_num_record_no;

COMMIT;
— l_var_process := ‘N’;
fnd_file.put_line (fnd_file.LOG, ‘No Payroll’ );
END;

BEGIN
–fnd_file.put_line (fnd_file.log, ‘EMP’);
hr_personal_pay_method_api.create_personal_pay_method
( — Input data elementds
— ——————–
p_validate => FALSE,
p_effective_date => l_dat_effective,
p_assignment_id => l_num_assign_id,
p_org_payment_method_id => l_num_pay_method_id,
p_priority => l_num_priority,
p_percentage => 100,
p_segment1 => replace(replace(replace(replace(r_bank.bank_code, chr(10), ”), chr(13), ”), chr(09), ”),’ ‘,”),
p_segment2 => replace(replace(replace(replace(r_bank.bank_acc_number, chr(10), ”), chr(13), ”), chr(09), ”),’ ‘,”),
p_segment3 => l_var_bank_country,
p_segment4 => l_var_bank_currency,
p_segment5 => replace(replace(replace(replace(r_bank.iban, chr(10), ”), chr(13), ”), chr(09), ”),’ ‘,”),
p_segment6 => replace(replace(replace(replace(r_bank.swift_code, chr(10), ”), chr(13), ”), chr(09), ”),’ ‘,”),
p_territory_code => ‘MY’,
— Output data elementds
— ———————
p_personal_payment_method_id => l_num_per_payment_method_id,
p_external_account_id => l_num_external_account_id,
p_object_version_number => l_num_object_version_number,
p_effective_start_date => l_dat_effective_start_date,
p_effective_end_date => l_dat_effective_end_date,
p_comment_id => l_num_comment_id
);
UPDATE XXHR_EMP_BANK_STG
set process_flag = ‘S’
WHERE record_no = r_bank.record_no;

L_NUM_SUCCESSFUL_RECORDS:= L_NUM_SUCCESSFUL_RECORDS + 1;

commit;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
— l_var_process := ‘N’;
— DBMS_OUTPUT.put_line (‘Inner Exception: ‘ || l_err_msg);
UPDATE XXHR_EMP_BANK_STG
SET PROCESS_FLAG = ‘E’,
error_msg =l_err_msg
where record_no = r_bank.record_no;
fnd_file.put_line (fnd_file.log, ‘Bank Details not created for ‘||r_bank.eurehka_number||
‘error: ‘ ||l_err_msg);
commit;
end;
— END IF;END IF;

END IF;

IF L_VAR_PROCESS = ‘N’
THEN
L_NUM_FAILED_RECORDS :=L_NUM_FAILED_RECORDS +1;
END IF;

commit;
END loop;
FND_FILE.PUT_LINE (FND_FILE.LOG, ‘Number of Successful records :’||L_NUM_SUCCESSFUL_RECORDS );
fnd_file.put_line (fnd_file.log, ‘Number of Failed records :’||l_num_failed_records );
commit;
end XXHR_BANK_DETAILS;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s