Create SIT API

procedure xxhr_bank_details
/* =====================================================================
— NAME : xxhr_bank_details

— PROGRAM TYPE : Procedure

— DESCRIPTION : This procedure is used to update the bank details
— INPUTS : None
— OUTPUTS : None
— ===================================================================*/
is

/*
================================================================================
–Cursor to get data from Staging Table
================================================================================
*/
CURSOR c_bank
IS
SELECT
RECORD_NO,
EMPLOYEE_NUMBER ,
BANK_CODE ,
BANK_ACC_NUMBER ,
START_DATE ,
IBAN ,
SWIFT_CODE ,
PAYMENT_CURRENCY
FROM xxhr_emp_bank_stg
WHERE process_flag IS NULL;

sql_err VARCHAR2(3000);
l_num_id_flex_num NUMBER;
l_num_analysis_criteria_id NUMBER;
l_num_person_analysis_id NUMBER;
l_num_pea_ovn NUMBER;
l_num_person_id NUMBER;
l_num_SUCCESSFUL_REC NUMBER;
l_num_record_no NUMBER;
l_var_flag VARCHAR2(10);
end_time DATE;
l_num_failed_rec NUMBER;
l_num_discarded_records NUMBER;
start_time DATE;
l_var_bank_code VARCHAR2(100);
l_var_payment_currency VARCHAR2(100);
BANK_CODE_NULL EXCEPTION;
BANK_ACC_NUMBER_NULL EXCEPTION;

begin
start_time := sysdate;

Begin
SELECT
ID_FLEX_NUM
into l_num_id_flex_num
FROM FND_ID_FLEX_SEGMENTS_VL
WHERE 1=1
AND (ID_FLEX_CODE =’PEA’)
AND (APPLICATION_ID=800)
and upper(SEGMENT_NAME)like ‘%BANK%’;
Exception when others then
sql_err:= sqlerrm;
DBMS_OUTPUT.put_line (‘exception’||sql_err);

end;

FOR r_bank IN c_bank
LOOP

l_num_record_no := r_bank.record_no;

/*
================================================================================
–getting person id
================================================================================
*/
begin
select person_id
into l_num_person_id
from per_all_people_f
where
employee_number = r_bank.employee_number
and sysdate between effective_start_date and effective_end_date
and business_group_id = fnd_profile.value(‘PER_BUSINESS_GROUP_ID’);– 82;
exception when others then
l_var_flag := ‘N’;
UPDATE xxhr_emp_bank_stg
SET PROCESS_FLAG = ‘E’,
ERROR_MSG = ’employee does not exists’
WHERE record_no = l_num_record_no;
DBMS_OUTPUT.put_line (’employee does not exists’);
fnd_file.put_line(fnd_file.LOG,’employee does not exists for emp no ‘||r_bank.employee_number);
end;

/*
================================================================================
–Validation for Bank Code
================================================================================
*/
BEGIN

IF r_bank.BANK_CODE IS NULL THEN
RAISE BANK_CODE_NULL;
END IF;
EXCEPTION
when BANK_CODE_NULL then
l_var_flag := ‘N’;
UPDATE xxhr_emp_bank_stg
SET PROCESS_FLAG = ‘E’,
ERROR_MSG = ‘Bank Code is null’
WHERE record_no = l_num_record_no;
DBMS_OUTPUT.put_line (‘Bank Code cannot be null’);
fnd_file.put_line(fnd_file.LOG,’Bank Code is null for for emp no ‘||r_bank.employee_number);
END;

/*
================================================================================
–Validation for BANK_ACC_NUMBER
================================================================================
*/
BEGIN

IF r_bank.BANK_ACC_NUMBER IS NULL THEN
RAISE BANK_ACC_NUMBER_NULL;
END IF;
EXCEPTION
when BANK_ACC_NUMBER_NULL then
l_var_flag := ‘N’;
UPDATE xxhr_emp_bank_stg
SET PROCESS_FLAG = ‘E’,
ERROR_MSG = ‘Bank A/c number is null’
WHERE record_no = l_num_record_no;
DBMS_OUTPUT.put_line (‘Bank A/c number cannot be null’);
fnd_file.put_line(fnd_file.LOG,’Bank A/c number is null for for emp no ‘||r_bank.employee_number);
END;

/*
================================================================================
–Validation for BANK CODE
================================================================================
*/

IF r_bank.BANK_CODE IS NOT NULL THEN
BEGIN
select ffv.flex_value
into l_var_bank_code
from
fnd_flex_value_sets ffvs,
fnd_flex_values ffv
where ffvs.flex_value_set_name =’TEC_KL_BANK_CODE’
and ffv.flex_value_set_id = ffvs.flex_value_set_id;
EXCEPTION
when others then
l_var_flag := ‘N’;
UPDATE xxhr_emp_bank_stg
SET PROCESS_FLAG = ‘E’,
ERROR_MSG = ‘Bank code incorrect’
WHERE record_no = l_num_record_no;
DBMS_OUTPUT.put_line (‘Bank code incorrect’);
fnd_file.put_line(fnd_file.LOG,’Bank code incorrect for for emp no ‘||r_bank.employee_number);

END;
END IF;

/*
================================================================================
–Validation for Payment Currency
================================================================================
*/

IF r_bank.PAYMENT_CURRENCY IS NOT NULL THEN
BEGIN
select ffv.flex_value
into l_var_payment_currency
from
fnd_flex_value_sets ffvs,
fnd_flex_values ffv
where ffvs.flex_value_set_name =’TEC_KL_PAY_CURR’
and ffv.flex_value_set_id = ffvs.flex_value_set_id;
EXCEPTION
when others then
l_var_flag := ‘N’;
UPDATE xxhr_emp_bank_stg
SET PROCESS_FLAG = ‘E’,
ERROR_MSG = ‘Bank code incorrect’
WHERE record_no = l_num_record_no;
DBMS_OUTPUT.put_line (‘Bank code incorrect’);
fnd_file.put_line(fnd_file.LOG,’Payment Currency incorrect for for emp no ‘||r_bank.employee_number);

END;
END IF;

/*
================================================================================
–Update Local Employee Number
================================================================================
*/

BEGIN
— DBMS_OUTPUT.put_line (‘XXSSSSSSS’);
l_num_person_analysis_id :=0;
hr_sit_api.create_sit
(p_validate => FALSE,
p_person_id => l_num_person_id,
p_business_group_id => fnd_profile.value(‘PER_BUSINESS_GROUP_ID’),–82,
p_id_flex_num => l_num_id_flex_num,
p_effective_date => SYSDATE,
p_comments => NULL,
p_date_from => SYSDATE,
— p_date_to => NULL,
p_request_id => NULL,
p_program_application_id => NULL,
p_program_id => NULL,
p_program_update_date => NULL,
p_attribute_category => NULL,
p_segment1 => l_var_bank_code,
p_segment2 => r_bank.BANK_ACC_NUMBER,
p_segment3 => r_bank.iban,
p_segment4 => r_bank.swift_code,
p_segment5 => l_var_payment_currency,
p_analysis_criteria_id => l_num_analysis_criteria_id,
p_person_analysis_id => l_num_person_analysis_id,
p_pea_object_version_number => l_num_pea_ovn
);

l_num_SUCCESSFUL_REC := l_num_SUCCESSFUL_REC + 1;
/*
================================================================================
–Update XXHR_EMP_MASTER_STG staging table
================================================================================
*/
UPDATE xxhr_emp_bank_stg
SET PROCESS_FLAG = ‘S’
WHERE record_no = l_num_record_no;
commit;

exception when others then
l_var_flag := ‘N’;
fnd_file.put_line (fnd_file.LOG,
‘unable to insert bank details for employee Number ‘||r_bank.employee_number
);
UPDATE xxhr_emp_bank_stg
SET
PROCESS_FLAG = ‘E’,
ERROR_MSG =’unable to insert bank detailsn for employee Number ‘||r_bank.employee_number
WHERE record_no = l_num_record_no;
commit;

end;
IF l_var_flag = ‘N’
THEN
l_num_FAILED_REC := l_num_FAILED_REC + 1;
END IF;

end loop;
end_time:=sysdate;
DBMS_OUTPUT.put_line ( ‘ Number of successful records is ‘
|| l_num_successful_rec
|| ‘ , failed records is ‘
|| l_num_failed_rec
|| ‘ and discarded records is ‘
|| l_num_discarded_records||’ Time taken was ‘||to_char(24*60*(end_time-start_time))
);
fnd_file.put_line (fnd_file.LOG,
‘Number of successful records is ‘
|| l_num_successful_rec
|| ‘ , failed records is ‘
|| l_num_failed_rec
|| ‘ and discarded records is ‘
|| l_num_discarded_records
||’, Time taken was ‘||to_char(24*60*(end_time-start_time))||’ mins’
);

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