erro no cursor

[gilsonalves]

Ajudem-me

PLS-00386: type mismatach found at 'V_PASSPORT_EXPIRATION_DATE' between FETCH cursor and INTO variables.

Obrigado,

Gilson

[victorhugomuniz]

cade a declaração? cade a estrutura dos dados que você ta colocando nele?

[gilsonalves]

meu código

CREATE OR REPLACE PROCEDURE cria_funcionario AS

-- v_count NUMBER;
v_total_pessoa number := 0;
v_err_pessoa number := 0;
v_total_doc number := 0;
v_err_doc number := 0;
V_COMM NUMBER := 0;
v_err_msg varchar2(512);
-- v_rowid rowid;

--parametros

v_validate boolean default false;
-- v_hire_date date;
v_business_group_id number;
v_last_name hr.per_all_people_f.last_name%type;
v_sex hr.per_all_people_f.sex%type;
v_person_type_id number(15); -- hr.per_all_people_f.person_type_id;
v_per_comments varchar2(15) := null;
v_date_employee_data_verified date;
v_date_of_birth date;
v_email_address hr.per_all_people_f.email_address%type;
v_employee_number hr.per_all_people_f.employee_number%type;
v_expense_check_send_to_addres hr.per_all_people_f.expense_check_send_to_address%type;
v_first_name hr.per_all_people_f.first_name%type;
v_known_as hr.per_all_people_f.known_as%type;
v_marital_status hr.per_all_people_f.marital_status%type;
v_middle_names hr.per_all_people_f.middle_names%type;
v_nationality hr.per_all_people_f.nationality%type;
v_national_identifier hr.per_all_people_f.national_identifier%type;
v_previous_last_name hr.per_all_people_f.previous_last_name%type;
v_registered_disabled_flag hr.per_all_people_f.registered_disabled_flag%type;
v_title hr.per_all_people_f.title%type;
v_vendor_id hr.per_all_people_f.vendor_id%type;
v_work_telephone hr.per_all_people_f.work_telephone%type;
v_attribute_category hr.per_all_people_f.attribute_category%type;
v_attribute1 hr.per_all_people_f.attribute1%type;
v_attribute2 hr.per_all_people_f.attribute2%type;
v_attribute3 hr.per_all_people_f.attribute3%type;
v_attribute4 hr.per_all_people_f.attribute4%type;
v_attribute5 hr.per_all_people_f.attribute5%type;
v_attribute6 hr.per_all_people_f.attribute6%type;
v_attribute7 hr.per_all_people_f.attribute7%type;
v_attribute8 hr.per_all_people_f.attribute8%type;
v_attribute9 hr.per_all_people_f.attribute9%type;
v_attribute10 hr.per_all_people_f.attribute10%type;
v_attribute11 hr.per_all_people_f.attribute11%type;
v_attribute12 hr.per_all_people_f.attribute12%type;
v_attribute13 hr.per_all_people_f.attribute13%type;
v_attribute14 hr.per_all_people_f.attribute14%type;
v_attribute15 hr.per_all_people_f.attribute15%type;
v_attribute16 hr.per_all_people_f.attribute16%type;
v_attribute17 hr.per_all_people_f.attribute17%type;
v_attribute18 hr.per_all_people_f.attribute18%type;
v_attribute19 hr.per_all_people_f.attribute19%type;
v_attribute20 hr.per_all_people_f.attribute20%type;
v_attribute21 hr.per_all_people_f.attribute21%type;
v_attribute22 hr.per_all_people_f.attribute22%type;
v_attribute23 hr.per_all_people_f.attribute23%type;
v_attribute24 hr.per_all_people_f.attribute24%type;
v_attribute25 hr.per_all_people_f.attribute25%type;
v_attribute26 hr.per_all_people_f.attribute26%type;
v_attribute27 hr.per_all_people_f.attribute27%type;
v_attribute28 hr.per_all_people_f.attribute28%type;
v_attribute29 hr.per_all_people_f.attribute29%type;
v_attribute30 hr.per_all_people_f.attribute30%type;
v_per_information_category hr.per_all_people_f.per_information_category%type;
v_per_information1 hr.per_all_people_f.per_information1%type;
v_per_information2 hr.per_all_people_f.per_information2%type;
v_per_information3 hr.per_all_people_f.per_information3%type;
v_per_information4 hr.per_all_people_f.per_information4%type;
v_per_information5 hr.per_all_people_f.per_information5%type;
v_per_information6 hr.per_all_people_f.per_information6%type;
v_per_information7 hr.per_all_people_f.per_information7%type;
v_per_information8 hr.per_all_people_f.per_information8%type;
v_per_information9 hr.per_all_people_f.per_information9%type;
v_per_information10 hr.per_all_people_f.per_information10%type;
v_per_information11 hr.per_all_people_f.per_information11%type;
v_per_information12 hr.per_all_people_f.per_information12%type;
v_per_information13 hr.per_all_people_f.per_information13%type;
v_per_information14 hr.per_all_people_f.per_information14%type;
v_per_information15 hr.per_all_people_f.per_information15%type;
v_per_information16 hr.per_all_people_f.per_information16%type;
v_per_information17 hr.per_all_people_f.per_information17%type;
v_per_information18 hr.per_all_people_f.per_information18%type;
v_per_information19 hr.per_all_people_f.per_information19%type;
v_per_information20 hr.per_all_people_f.per_information20%type;
v_per_information21 hr.per_all_people_f.per_information21%type;
v_per_information22 hr.per_all_people_f.per_information22%type;
v_per_information23 hr.per_all_people_f.per_information23%type;
v_per_information24 hr.per_all_people_f.per_information24%type;
v_per_information25 hr.per_all_people_f.per_information25%type;
v_per_information26 hr.per_all_people_f.per_information26%type;
v_per_information27 hr.per_all_people_f.per_information27%type;
v_per_information28 hr.per_all_people_f.per_information28%type;
v_per_information29 hr.per_all_people_f.per_information29%type;
v_per_information30 hr.per_all_people_f.per_information30%type;
v_date_of_death date;
v_background_check_status hr.per_all_people_f.background_check_status%type;
v_background_date_check date;
v_blood_type hr.per_all_people_f.blood_type%type;
v_correspondence_language hr.per_all_people_f.correspondence_language%type;
v_fast_path_employee hr.per_all_people_f.fast_path_employee%type;
v_fte_capacity hr.per_all_people_f.fte_capacity%type;
v_honors hr.per_all_people_f.honors%type;
v_internal_location hr.per_all_people_f.internal_location%type;
v_last_medical_test_by hr.per_all_people_f.last_medical_test_by%type;
v_last_medical_test_date date;
v_mailstop hr.per_all_people_f.mailstop%type;
v_office_number hr.per_all_people_f.office_number%type;
v_on_military_service hr.per_all_people_f.on_military_service%type;
v_pre_name_adjunct hr.per_all_people_f.pre_name_adjunct%type;
v_rehire_recommendation hr.per_all_people_f.rehire_recommendation%type;
v_projected_start_date date;
v_resume_exists hr.per_all_people_f.resume_exists%type;
v_resume_last_updated date;
v_second_passport_exists hr.per_all_people_f.second_passport_exists%type;
v_student_status hr.per_all_people_f.student_status%type;
v_work_schedule hr.per_all_people_f.work_schedule%type;
v_suffix hr.per_all_people_f.suffix%type;
v_benefit_group_id number(15); -- hr.per_all_people_f.benefit_group_id;
v_receipt_of_death_cert_date date;
v_coord_ben_med_pln_no hr.per_all_people_f.coord_ben_med_pln_no%type;
v_coord_ben_no_cvg_flag hr.per_all_people_f.coord_ben_no_cvg_flag%type;
v_coord_ben_med_ext_er hr.per_all_people_f.coord_ben_med_ext_er%type;
v_coord_ben_med_pl_name hr.per_all_people_f.coord_ben_med_pl_name%type;
v_coord_ben_med_insr_crr_name hr.per_all_people_f.coord_ben_med_insr_crr_name%type;
v_coord_ben_med_insr_crr_ident hr.per_all_people_f.coord_ben_med_insr_crr_ident%type;
v_coord_ben_med_cvg_strt_dt date;
v_coord_ben_med_cvg_end_dt date;
v_uses_tobacco_flag hr.per_all_people_f.uses_tobacco_flag%type;
v_dpdnt_adoption_date date;
v_dpdnt_vlntry_svce_flag hr.per_all_people_f.dpdnt_vlntry_svce_flag%type;
v_original_date_of_hire date;
v_adjusted_svc_date date;
v_town_of_birth hr.per_all_people_f.town_of_birth%type;
v_region_of_birth hr.per_all_people_f.region_of_birth%type;
v_country_of_birth hr.per_all_people_f.country_of_birth%type;
v_global_person_id hr.per_all_people_f.global_person_id%type;
v_party_id hr.per_all_people_f.party_id%type;
v_person_id hr.per_all_people_f.person_id%type;
v_person_id_new hr.per_all_people_f.person_id%type;
v_assignment_id hr.per_all_assignments_f.assignment_id%type;
v_object_version_number hr.per_all_assignments_f.object_version_number%type;
v_asg_object_version_number number(10);
v_effective_start_date date;
v_effective_end_date date;
v_full_name hr.per_all_people_f.full_name%type;
v_comment_id hr.per_all_people_f.comment_id%type;
v_assignment_sequence hr.per_all_assignments_f.assignment_sequence%type;
v_assignment_number hr.per_all_assignments_f.assignment_number%type;
v_name_combination_warning boolean default false;
v_assign_payroll_warning boolean default false;
v_orig_hire_warning boolean default false;

---------variaveis person data-----------------

v_EDUCATION_LEVEL bs.bs_hr_person_data.education_level%type;
v_CITY_OF_BIRTH bs.bs_hr_person_data.city_of_birth%type;
v_STATE_OF_BIRTH bs.bs_hr_person_data.state_of_birth%type;
v_NATURALIZED_FLAG bs.bs_hr_person_data.naturalized_flag%type;
v_ARRIVAL_DATE_COUNTRY date;
v_RNE bs.bs_hr_person_data.rne%type;
v_VISA_TYPE bs.bs_hr_person_data.visa_type%type;
v_VISA_EXPIRATION_DATE date;
v_PASSPORT_NUMBER bs.bs_hr_person_data.passport_number%type;
v_PASSPORT_EXPIRATION_DATE date;
v_CTPS_NUMBER bs.bs_hr_person_data.ctps_number%type;
v_CTPS_SERIAL_NUMBER bs.bs_hr_person_data.ctps_serial_number%type;
v_CTPS_LETTER bs.bs_hr_person_data.ctps_letter%type;
v_CTPS_STATE bs.bs_hr_person_data.ctps_state%type;
v_CTPS_ISSUE_DATE date;
v_CTPS_EXPIRATION_DATE date;
v_PIS_PASEP_PROGRAM_TYPE bs.bs_hr_person_data.pis_pasep_program_type%type;
v_PIS_PASEP_NUMBER number;
v_PIS_PASEP_ISSUE_DATE date;
v_PIS_PASEP_BANK_NUMBER number;
v_PIS_PASEP_BRANCH_NUMBER bs.bs_hr_person_data.pis_pasep_branch_number%type;
v_PIS_PASEP_BRANCH_CHECK_DIGIT bs.bs_hr_person_data.pis_pasep_branch_check_digit%type;
v_RG_NUMBER bs.bs_hr_person_data.rg_number%type;
v_RG_CHECK_DIGIT bs.bs_hr_person_data.rg_check_digit%type;
v_RG_STATE bs.bs_hr_person_data.rg_state%type;
v_RG_ENTITY bs.bs_hr_person_data.rg_entity%type;
v_RG_ISSUE_DATE date;
v_RG_LOCATION_ISSUE bs.bs_hr_person_data.rg_location_issue%type;
v_OWN_CPF_FLAG bs.bs_hr_person_data.own_cpf_flag%type;
v_CPF_NUMBER bs.bs_hr_person_data.cpf_number%type;
v_CPF_CHECK_DIGIT bs.bs_hr_person_data.cpf_check_digit%type;
v_ELECTOR_NUMBER bs.bs_hr_person_data.elector_number%type;
v_ELECTOR_NUMBER_SESSION bs.bs_hr_person_data.elector_number_session%type;
v_ELECTOR_NUMBER_ZONE bs.bs_hr_person_data.elector_number_state%type;
v_ELECTOR_NUMBER_STATE bs.bs_hr_person_data.elector_number%type;
v_ELECTOR_NUMBER_ISSUE_DATE date;
v_RESERVIST_NUMBER bs.bs_hr_person_data.reservist_number%type;
v_RESERVIST_SERIAL_NUMBER bs.bs_hr_person_data.reservist_serial_number%type;
v_RESERVIST_CATEGORY bs.bs_hr_person_data.reservist_category%type;
v_DRIVER_LICENCE_NUMBER number;
v_DRIVER_LICENCE_CATEGORY bs.bs_hr_person_data.driver_licence_category%type;
v_DVER_LICENCE_EXPIRATION_DATE date;
v_PROFESSIONAL_ASSOCIATION bs.bs_hr_person_data.professional_association%type;
v_PROFESSIONAL_ASSOC_REGION bs.bs_hr_person_data.professional_assoc_region%type;
v_PROFESSIONAL_ASSOC_RECORD bs.bs_hr_person_data.professional_assoc_record%type;
v_RETIRED_FLAG bs.bs_hr_person_data.retired_flag%type;
v_RETIREMENT_DATE date;
v_RETIREMENT_REASON bs.bs_hr_person_data.retirement_reason%type;
v_INSS_NUMBER number;
v_CREATION_DATE_bs date;
v_CREATED_BY_bs number;
v_LAST_UPDATE_DATE_bs date;
v_LAST_UPDATED_BY_bs number;
v_LAST_UPDATE_LOGIN_bs number;
v_REQUEST_ID_bs number;
v_PROGRAM_APPLICATION_ID_bs number;
v_PROGRAM_ID_bs number;
v_PROGRAM_UPDATE_DATE_bs date;
v_ATTRIBUTE_CATEGORY_bs bs.bs_hr_person_data.attribute_category%type;
v_ATTRIBUTE1_bs bs.bs_hr_person_data.attribute1%type;
v_ATTRIBUTE2_bs bs.bs_hr_person_data.attribute2%type;
v_ATTRIBUTE3_bs bs.bs_hr_person_data.attribute3%type;
v_ATTRIBUTE4_bs bs.bs_hr_person_data.attribute4%type;
v_ATTRIBUTE5_bs bs.bs_hr_person_data.attribute5%type;
v_ATTRIBUTE6_bs bs.bs_hr_person_data.attribute6%type;
v_ATTRIBUTE7_bs bs.bs_hr_person_data.attribute7%type;
v_ATTRIBUTE8_bs bs.bs_hr_person_data.attribute8%type;
v_ATTRIBUTE9_bs bs.bs_hr_person_data.attribute9%type;
v_ATTRIBUTE10_bs bs.bs_hr_person_data.attribute10%type;
v_ATTRIBUTE11_bs bs.bs_hr_person_data.attribute11%type;
v_ATTRIBUTE12_bs bs.bs_hr_person_data.attribute12%type;
v_ATTRIBUTE13_bs bs.bs_hr_person_data.attribute13%type;
v_ATTRIBUTE14_bs bs.bs_hr_person_data.attribute14%type;
v_ATTRIBUTE15_bs bs.bs_hr_person_data.attribute15%type;
v_ATTRIBUTE16_bs bs.bs_hr_person_data.attribute16%type;
v_ATTRIBUTE17_bs bs.bs_hr_person_data.attribute17%type;
v_ATTRIBUTE18_bs bs.bs_hr_person_data.attribute18%type;
v_ATTRIBUTE19_bs bs.bs_hr_person_data.attribute19%type;
v_ATTRIBUTE20_bs bs.bs_hr_person_data.attribute20%type;
--
v_current_emp_or_apl_flag hr.per_all_people_f.current_emp_or_apl_flag%type;
v_current_employee_flag hr.per_all_people_f.current_employee_flag%type;
v_hold_applicant_date_until date;
v_order_name hr.per_all_people_f.order_name%type;
v_rehire_authorizor hr.per_all_people_f.rehire_authorizor%type;
v_rehire_reason hr.per_all_people_f.rehire_reason%type;
v_request_id number;
v_program_application_id number;
v_program_id number;
v_program_update_date date;
v_last_update_date date;
v_last_updated_by number;
v_last_update_login number;
v_created_by number;
v_creation_date date;
v_npw_number number;
v_current_npw_flag number;
v_applicant_number hr.per_all_people_f.applicant_number%type;
v_current_applicant_flag hr.per_all_people_f.current_applicant_flag%type;
v_start_date date;
v_per_comment_id number := null;

CURSOR funcionario_info IS
select
f.person_id, f.effective_start_date, f.effective_end_date,
f.business_group_id, f.person_type_id, f.last_name,
f.start_date, f.applicant_number, f.background_check_status,
f.background_date_check, f.blood_type, f.comment_id,
f.correspondence_language, f.current_applicant_flag, f.current_emp_or_apl_flag,
f.current_employee_flag, f.date_employee_data_verified, f.date_of_birth,
f.email_address, f.employee_number,
f.fast_path_employee, f.first_name, f.fte_capacity,
f.full_name, f.hold_applicant_date_until, f.honors,
f.internal_location, f.known_as, f.last_medical_test_by,
f.last_medical_test_date, f.mailstop, f.marital_status,
f.middle_names, f.nationality, f.national_identifier,
f.office_number, f.on_military_service, f.order_name,
f.pre_name_adjunct, f.previous_last_name, f.projected_start_date,
f.rehire_authorizor, f.rehire_reason, f.rehire_recommendation,
f.resume_exists, f.resume_last_updated, f.registered_disabled_flag,
f.second_passport_exists, f.sex, f.student_status,
f.suffix, f.title, f.vendor_id,
f.work_schedule, f.work_telephone, f.coord_ben_med_pln_no,
f.coord_ben_no_cvg_flag, f.dpdnt_adoption_date, f.dpdnt_vlntry_svce_flag,
f.receipt_of_death_cert_date, f.uses_tobacco_flag, f.benefit_group_id,
f.request_id, f.program_application_id, f.program_id,
f.program_update_date, f.attribute_category, f.attribute1,
f.attribute2, f.attribute3, f.attribute4,
f.attribute5, f.attribute6, f.attribute7,
f.attribute8, f.attribute9, f.attribute10,
f.attribute11, f.attribute12, f.attribute13,
f.attribute14, f.attribute15, f.attribute16,
f.attribute17, f.attribute18, f.attribute19,
f.attribute20, f.attribute21, f.attribute22,
f.attribute23, f.attribute24, f.attribute25,
f.attribute26, f.attribute27, f.attribute28,
f.attribute29, f.attribute30, f.last_update_date,
f.last_updated_by, f.last_update_login, f.created_by,
f.creation_date, f.per_information_category, f.per_information1,
f.per_information2, f.per_information3, f.per_information4,
f.per_information5, f.per_information6, f.per_information7,
f.per_information8, f.per_information9, f.per_information10,
f.per_information11, f.per_information12, f.per_information13,
f.per_information14, f.per_information15, f.per_information16,
f.per_information17, f.per_information18, f.per_information19,
f.per_information20, f.per_information21, f.per_information22,
f.per_information23, f.per_information24, f.per_information25,
f.per_information26, f.per_information27, f.per_information28,
f.per_information29, f.per_information30, f.object_version_number,
f.date_of_death, f.original_date_of_hire, f.town_of_birth,
f.region_of_birth, f.country_of_birth, f.global_person_id,
f.coord_ben_med_pl_name, f.coord_ben_med_insr_crr_name, f.coord_ben_med_insr_crr_ident,
f.coord_ben_med_ext_er, f.coord_ben_med_cvg_strt_dt, f.coord_ben_med_cvg_end_dt,
f.party_id, f.npw_number, f.current_npw_flag
--
from apps.per_all_people_f@dbl_crp2_ebsp.basa.com f
,apps.per_all_assignments_f@dbl_crp2_ebsp.basa.com ass
,apps.hr_all_organization_units@dbl_crp2_ebsp.basa.com org
,apps.hr_locations@dbl_crp2_ebsp.basa.com loc
,apps.per_jobs@dbl_crp2_ebsp.basa.com j
,apps.per_all_positions@dbl_crp2_ebsp.basa.com p
,pay_people_groups@dbl_crp2_ebsp.basa.com pg
,PER_PERIODS_OF_SERVICE@dbl_crp2_ebsp.basa.com ps
--
where f.person_id = ass.person_id
and f.business_group_id = ass.business_group_id
and f.effective_end_date = ass.effective_end_date
and ass.organization_id = org.organization_id
and ass.location_id = loc.location_id
and j.job_id = ass.job_id
and p.position_id (+) = ass.position_id
and pg.people_group_id = ass.people_group_id
and f.PERSON_ID = PS.PERSON_ID
and ORG.TYPE = '3'
and ass.effective_end_date = '31-DEC-4712'
and org.attribute20 in ('073') --'000','007','033','046','047','048','073','078','124','134','135')
and ps.actual_termination_date is null
and not exists (select 1 from hr.per_all_people_f fd where f.employee_number = fd.employee_number);


CURSOR dadosPessoais_func IS
select
PERSON_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
EDUCATION_LEVEL, CITY_OF_BIRTH, STATE_OF_BIRTH,
NATURALIZED_FLAG, ARRIVAL_DATE_COUNTRY, RNE,
VISA_TYPE, VISA_EXPIRATION_DATE, PASSPORT_NUMBER,
PASSPORT_EXPIRATION_DATE, CTPS_NUMBER, CTPS_SERIAL_NUMBER,
CTPS_LETTER, CTPS_STATE, CTPS_ISSUE_DATE,
CTPS_EXPIRATION_DATE, PIS_PASEP_PROGRAM_TYPE, PIS_PASEP_NUMBER,
PIS_PASEP_ISSUE_DATE, PIS_PASEP_BANK_NUMBER, PIS_PASEP_BRANCH_NUMBER,
PIS_PASEP_BRANCH_CHECK_DIGIT, RG_NUMBER, RG_CHECK_DIGIT,
RG_STATE, RG_ENTITY, RG_ISSUE_DATE,
RG_LOCATION_ISSUE, OWN_CPF_FLAG, CPF_NUMBER,
CPF_CHECK_DIGIT, ELECTOR_NUMBER, ELECTOR_NUMBER_SESSION,
ELECTOR_NUMBER_ZONE, ELECTOR_NUMBER_STATE, ELECTOR_NUMBER_ISSUE_DATE,
RESERVIST_NUMBER, RESERVIST_SERIAL_NUMBER, RESERVIST_CATEGORY
DRIVER_LICENCE_NUMBER, DRIVER_LICENCE_CATEGORY, DRIVER_LICENCE_EXPIRATION_DATE,
PROFESSIONAL_ASSOCIATION, PROFESSIONAL_ASSOC_REGION, PROFESSIONAL_ASSOC_RECORD,
RETIRED_FLAG, RETIREMENT_DATE, RETIREMENT_REASON
INSS_NUMBER, CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16,
ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19,
ATTRIBUTE20
from bs.bs_hr_person_data@dbl_crp2_ebsp.basa.com
where PERSON_ID = v_person_id_new;

BEGIN
OPEN funcionario_info;

LOOP
FETCH funcionario_info INTO
v_person_id, v_effective_start_date, v_effective_end_date,
v_business_group_id, v_person_type_id, v_last_name,
v_start_date, v_applicant_number, v_background_check_status,
v_background_date_check, v_blood_type, v_comment_id,
v_correspondence_language, v_current_applicant_flag, v_current_emp_or_apl_flag,
v_current_employee_flag, v_date_employee_data_verified, v_date_of_birth,
v_email_address, v_employee_number,
v_fast_path_employee, v_first_name, v_fte_capacity,
v_full_name, v_hold_applicant_date_until, v_honors,
v_internal_location, v_known_as, v_last_medical_test_by,
v_last_medical_test_date, v_mailstop, v_marital_status,
v_middle_names, v_nationality, v_national_identifier,
v_office_number, v_on_military_service, v_order_name,
v_pre_name_adjunct, v_previous_last_name, v_projected_start_date,
v_rehire_authorizor, v_rehire_reason, v_rehire_recommendation,
v_resume_exists, v_resume_last_updated, v_registered_disabled_flag,
v_second_passport_exists, v_sex, v_student_status,
v_suffix, v_title, v_vendor_id,
v_work_schedule, v_work_telephone, v_coord_ben_med_pln_no,
v_coord_ben_no_cvg_flag, v_dpdnt_adoption_date, v_dpdnt_vlntry_svce_flag,
v_receipt_of_death_cert_date, v_uses_tobacco_flag, v_benefit_group_id,
v_request_id, v_program_application_id, v_program_id,
v_program_update_date, v_attribute_category, v_attribute1,
v_attribute2, v_attribute3, v_attribute4,
v_attribute5, v_attribute6, v_attribute7,
v_attribute8, v_attribute9, v_attribute10,
v_attribute11, v_attribute12, v_attribute13,
v_attribute14, v_attribute15, v_attribute16,
v_attribute17, v_attribute18, v_attribute19,
v_attribute20, v_attribute21, v_attribute22,
v_attribute23, v_attribute24, v_attribute25,
v_attribute26, v_attribute27, v_attribute28,
v_attribute29, v_attribute30, v_last_update_date,
v_last_updated_by, v_last_update_login, v_created_by,
v_creation_date, v_per_information_category, v_per_information1,
v_per_information2, v_per_information3, v_per_information4,
v_per_information5, v_per_information6, v_per_information7,
v_per_information8, v_per_information9, v_per_information10,
v_per_information11, v_per_information12, v_per_information13,
v_per_information14, v_per_information15, v_per_information16,
v_per_information17, v_per_information18, v_per_information19,
v_per_information20, v_per_information21, v_per_information22,
v_per_information23, v_per_information24, v_per_information25,
v_per_information26, v_per_information27, v_per_information28,
v_per_information29, v_per_information30, v_object_version_number,
v_date_of_death, v_original_date_of_hire, v_town_of_birth,
v_region_of_birth, v_country_of_birth, v_global_person_id,
v_coord_ben_med_pl_name, v_coord_ben_med_insr_crr_name, v_coord_ben_med_insr_crr_ident,
v_coord_ben_med_ext_er, v_coord_ben_med_cvg_strt_dt, v_coord_ben_med_cvg_end_dt,
v_party_id, v_npw_number, v_current_npw_flag;
EXIT WHEN funcionario_info%NOTFOUND;

if v_date_of_birth > v_start_date then
v_start_date := v_date_of_birth + 1;
end if;

BEGIN
apps.hr_employee_api.create_employee (
p_validate => v_validate,
p_hire_date => v_start_date,
p_business_group_id => v_business_group_id,
p_last_name => v_last_name,
p_sex => v_sex,
p_person_type_id => v_person_type_id,
p_per_comments => v_per_comments,
p_date_employee_data_verified => v_date_employee_data_verified,
p_date_of_birth => v_date_of_birth,
p_email_address => v_email_address,
p_employee_number => v_employee_number,
p_expense_check_send_to_addres => v_expense_check_send_to_addres,
p_first_name => v_first_name,
p_known_as => v_known_as,
p_marital_status => v_marital_status,
p_middle_names => v_middle_names,
p_nationality => v_nationality,
p_national_identifier => v_national_identifier,
p_previous_last_name => v_previous_last_name,
p_registered_disabled_flag => v_registered_disabled_flag,
p_title => v_title,
p_vendor_id => v_vendor_id,
p_work_telephone => v_work_telephone,
p_attribute_category => v_attribute_category,
p_attribute1 => v_attribute1,
p_attribute2 => v_attribute2,
p_attribute3 => v_attribute3,
p_attribute4 => v_attribute4,
p_attribute5 => v_attribute5,
p_attribute6 => v_attribute6,
p_attribute7 => v_attribute7,
p_attribute8 => v_attribute8,
p_attribute9 => v_attribute9,
p_attribute10 => v_attribute10,
p_attribute11 => v_attribute11,
p_attribute12 => v_attribute12,
p_attribute13 => v_attribute13,
p_attribute14 => v_attribute14,
p_attribute15 => v_attribute15,
p_attribute16 => v_attribute16,
p_attribute17 => v_attribute17,
p_attribute18 => v_attribute18,
p_attribute19 => v_attribute19,
p_attribute20 => v_attribute20,
p_attribute21 => v_attribute21,
p_attribute22 => v_attribute22,
p_attribute23 => v_attribute23,
p_attribute24 => v_attribute24,
p_attribute25 => v_attribute25,
p_attribute26 => v_attribute26,
p_attribute27 => v_attribute27,
p_attribute28 => v_attribute28,
p_attribute29 => v_attribute29,
p_attribute30 => v_attribute30,
P_PER_INFORMATION_CATEGORY => v_PER_INFORMATION_CATEGORY,
p_per_information1 => v_per_information1,
p_per_information2 => v_per_information2,
p_per_information3 => v_per_information3,
p_per_information4 => v_per_information4,
p_per_information5 => v_per_information5,
p_per_information6 => v_per_information6,
p_per_information7 => v_per_information7,
p_per_information8 => v_per_information8,
p_per_information9 => v_per_information9,
p_per_information10 => v_per_information10,
p_per_information11 => v_per_information11,
p_per_information12 => v_per_information12,
p_per_information13 => v_per_information13,
p_per_information14 => v_per_information14,
p_per_information15 => v_per_information15,
p_per_information16 => v_per_information16,
p_per_information17 => v_per_information17,
p_per_information18 => v_per_information18,
p_per_information19 => v_per_information19,
p_per_information20 => v_per_information20,
p_per_information21 => v_per_information21,
p_per_information22 => v_per_information22,
p_per_information23 => v_per_information23,
p_per_information24 => v_per_information24,
p_per_information25 => v_per_information25,
p_per_information26 => v_per_information26,
p_per_information27 => v_per_information27,
p_per_information28 => v_per_information28,
p_per_information29 => v_per_information29,
p_per_information30 => v_per_information30,
p_date_of_death => v_date_of_death,
p_background_check_status => v_background_check_status,
p_background_date_check => v_background_date_check,
p_blood_type => v_blood_type,
p_correspondence_language => v_correspondence_language,
p_fast_path_employee => v_fast_path_employee,
p_fte_capacity => v_fte_capacity,
p_honors => v_honors,
p_internal_location => v_internal_location,
p_last_medical_test_by => v_last_medical_test_by,
p_last_medical_test_date => v_last_medical_test_date,
p_mailstop => v_mailstop,
p_office_number => v_office_number,
p_on_military_service => v_on_military_service,
p_pre_name_adjunct => v_pre_name_adjunct,
p_rehire_recommendation => v_rehire_recommendation,
p_projected_start_date => v_projected_start_date,
p_resume_exists => v_resume_exists,
p_resume_last_updated => v_resume_last_updated,
p_second_passport_exists => v_second_passport_exists,
p_student_status => v_student_status,
p_work_schedule => v_work_schedule,
p_suffix => v_suffix,
p_benefit_group_id => v_benefit_group_id,
p_receipt_of_death_cert_date => v_receipt_of_death_cert_date,
p_coord_ben_med_pln_no => v_coord_ben_med_pln_no,
p_coord_ben_no_cvg_flag => v_coord_ben_no_cvg_flag,
p_coord_ben_med_cvg_strt_dt => v_coord_ben_med_cvg_strt_dt,
p_coord_ben_med_ext_er => v_coord_ben_med_ext_er,
p_coord_ben_med_pl_name => v_coord_ben_med_pl_name,
p_coord_ben_med_insr_crr_name => v_coord_ben_med_insr_crr_name,
p_coord_ben_med_insr_crr_ident => v_coord_ben_med_insr_crr_ident,
p_uses_tobacco_flag => v_uses_tobacco_flag,
p_dpdnt_adoption_date => v_dpdnt_adoption_date,
p_dpdnt_vlntry_svce_flag => v_dpdnt_vlntry_svce_flag,
p_original_date_of_hire => v_original_date_of_hire,
p_adjusted_svc_date => v_adjusted_svc_date,
p_town_of_birth => v_town_of_birth,
p_region_of_birth => v_region_of_birth,
p_country_of_birth => v_country_of_birth,
p_global_person_id => v_global_person_id,
p_party_id => v_party_id,
p_person_id => v_person_id,
p_assignment_id => v_assignment_id,
p_per_object_version_number => v_object_version_number,
p_asg_object_version_number => v_asg_object_version_number,
p_per_effective_start_date => v_effective_start_date,
p_per_effective_end_date => v_effective_end_date,
p_full_name => v_full_name,
p_per_comment_id => v_per_comment_id,
p_assignment_sequence => v_assignment_sequence,
p_assignment_number => v_assignment_number,
p_name_combination_warning => v_name_combination_warning ,
p_assign_payroll_warning => v_assign_payroll_warning,
p_orig_hire_warning => v_orig_hire_warning
);

v_total_pessoa := v_total_pessoa + 1;

EXCEPTION
WHEN others THEN
v_err_pessoa:= v_err_pessoa + 1;
v_err_msg := sqlerrm;
v_err_msg := substr(v_err_msg,1,200);
dbms_output.put_line('Funcionario : '||v_employee_number||' Erro : '||v_err_msg);
dbms_output.put_line('Funcionario : '||v_employee_number||' Erro : '||sqlerrm);
END;

-- Tratamento de dados para inserção na tabela bs_hr_person_data

select person_id
into v_person_id_new
from hr.per_all_people_f
where employee_number = v_employee_number;

BEGIN
if dadosPessoais_func%ISOPEN then
close dadosPessoais_func;
end if;
open dadosPessoais_func;

LOOP
fetch dadosPessoais_func into
v_PERSON_ID, v_EFFECTIVE_START_DATE, v_EFFECTIVE_END_DATE,
v_EDUCATION_LEVEL, v_CITY_OF_BIRTH, v_STATE_OF_BIRTH,
v_NATURALIZED_FLAG, v_ARRIVAL_DATE_COUNTRY, v_RNE,
v_VISA_TYPE, v_VISA_EXPIRATION_DATE, v_PASSPORT_NUMBER,
v_PASSPORT_EXPIRATION_DATE, v_CTPS_NUMBER, v_CTPS_SERIAL_NUMBER,
v_CTPS_LETTER, v_CTPS_STATE, v_CTPS_ISSUE_DATE,
v_CTPS_EXPIRATION_DATE, v_PIS_PASEP_PROGRAM_TYPE, v_PIS_PASEP_NUMBER,
v_PIS_PASEP_ISSUE_DATE, v_PIS_PASEP_BANK_NUMBER, v_PIS_PASEP_BRANCH_NUMBER,
v_PIS_PASEP_BRANCH_CHECK_DIGIT, v_RG_NUMBER, v_RG_CHECK_DIGIT,
v_RG_STATE, v_RG_ENTITY, v_RG_ISSUE_DATE,
v_RG_LOCATION_ISSUE, v_OWN_CPF_FLAG, v_CPF_NUMBER,
v_CPF_CHECK_DIGIT, v_ELECTOR_NUMBER, v_ELECTOR_NUMBER_SESSION,
v_ELECTOR_NUMBER_ZONE, v_ELECTOR_NUMBER_STATE, v_ELECTOR_NUMBER_ISSUE_DATE,
v_RESERVIST_NUMBER, v_RESERVIST_SERIAL_NUMBER, v_RESERVIST_CATEGORY,
v_DRIVER_LICENCE_NUMBER, v_DRIVER_LICENCE_CATEGORY, v_DVER_LICENCE_EXPIRATION_DATE,
v_PROFESSIONAL_ASSOCIATION, v_PROFESSIONAL_ASSOC_REGION, v_PROFESSIONAL_ASSOC_RECORD,
v_RETIRED_FLAG, v_RETIREMENT_DATE, v_RETIREMENT_REASON,
v_INSS_NUMBER, v_CREATION_DATE_bs, v_CREATED_BY_bs,
v_LAST_UPDATE_DATE_bs, v_LAST_UPDATED_BY_bs, v_LAST_UPDATE_LOGIN_bs,
v_REQUEST_ID_bs, v_PROGRAM_APPLICATION_ID_bs, v_PROGRAM_ID_bs,
v_PROGRAM_UPDATE_DATE_bs, v_ATTRIBUTE_CATEGORY_bs, v_ATTRIBUTE1_bs,
v_ATTRIBUTE2_bs, v_ATTRIBUTE3_bs, v_ATTRIBUTE4_bs,
v_ATTRIBUTE5_bs, v_ATTRIBUTE6_bs, v_ATTRIBUTE7_bs,
v_ATTRIBUTE8_bs, v_ATTRIBUTE9_bs, v_ATTRIBUTE10_bs,
v_ATTRIBUTE11_bs, v_ATTRIBUTE12_bs, v_ATTRIBUTE13_bs,
v_ATTRIBUTE14_bs, v_ATTRIBUTE15_bs, v_ATTRIBUTE16_bs,
v_ATTRIBUTE17_bs, v_ATTRIBUTE18_bs, v_ATTRIBUTE19_bs,
v_ATTRIBUTE20_bs;

EXIT WHEN dadosPessoais_func%NOTFOUND;

end loop;

CLOSE dadosPessoais_func;

end;

begin
insert into bs.bs_hr_person_data
(
person_id, effective_start_date, effective_end_date,
education_level, city_of_birth, state_of_birth,
naturalized_flag, arrival_date_country, rne,
visa_type, visa_expiration_date, passport_number,
passport_expiration_date, ctps_number, ctps_serial_number,
ctps_letter, ctps_state, ctps_issue_date,
ctps_expiration_date, pis_pasep_program_type, pis_pasep_number,
pis_pasep_issue_date, pis_pasep_bank_number, pis_pasep_branch_number,
pis_pasep_branch_check_digit, rg_number, rg_check_digit,
rg_state, rg_entity, rg_issue_date,
rg_location_issue, own_cpf_flag, cpf_number,
cpf_check_digit, elector_number, elector_number_session,
elector_number_zone, elector_number_state, elector_number_issue_date,
reservist_number, reservist_serial_number, reservist_category,
driver_licence_number, driver_licence_category, driver_licence_expiration_date,
professional_association, professional_assoc_region, professional_assoc_record,
retired_flag, retirement_date, retirement_reason,
inss_number, creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
request_id, program_application_id, program_id,
program_update_date, attribute_category, attribute1,
attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13,
attribute14, attribute15, attribute16,
attribute17, attribute18, attribute19,
attribute20, disabled_person
)
values (
v_PERSON_ID, v_EFFECTIVE_START_DATE, v_EFFECTIVE_END_DATE,
v_EDUCATION_LEVEL, v_CITY_OF_BIRTH, v_STATE_OF_BIRTH,
v_NATURALIZED_FLAG, v_ARRIVAL_DATE_COUNTRY, v_RNE,
v_VISA_TYPE, v_VISA_EXPIRATION_DATE, v_PASSPORT_NUMBER,
v_PASSPORT_EXPIRATION_DATE , v_CTPS_NUMBER, v_CTPS_SERIAL_NUMBER,
v_CTPS_LETTER, v_CTPS_STATE, v_CTPS_ISSUE_DATE,
v_CTPS_EXPIRATION_DATE, v_PIS_PASEP_PROGRAM_TYPE, v_PIS_PASEP_NUMBER,
v_PIS_PASEP_ISSUE_DATE, v_PIS_PASEP_BANK_NUMBER, v_PIS_PASEP_BRANCH_NUMBER,
v_PIS_PASEP_BRANCH_CHECK_DIGIT, v_RG_NUMBER, v_RG_CHECK_DIGIT,
v_RG_STATE, v_RG_ENTITY, v_RG_ISSUE_DATE,
v_RG_LOCATION_ISSUE, v_OWN_CPF_FLAG, v_CPF_NUMBER,
v_CPF_CHECK_DIGIT, v_ELECTOR_NUMBER, v_ELECTOR_NUMBER_SESSION,
v_ELECTOR_NUMBER_ZONE, v_ELECTOR_NUMBER_STATE, v_ELECTOR_NUMBER_ISSUE_DATE,
v_RESERVIST_NUMBER, v_RESERVIST_SERIAL_NUMBER, v_RESERVIST_CATEGORY,
v_DRIVER_LICENCE_NUMBER, v_DRIVER_LICENCE_CATEGORY, v_DVER_LICENCE_EXPIRATION_DATE,
v_PROFESSIONAL_ASSOCIATION, v_PROFESSIONAL_ASSOC_REGION, v_PROFESSIONAL_ASSOC_RECORD,
v_RETIRED_FLAG, v_RETIREMENT_DATE, v_RETIREMENT_REASON,
v_INSS_NUMBER, v_CREATION_DATE_bs, v_CREATED_BY_bs,
v_LAST_UPDATE_DATE_bs, v_LAST_UPDATED_BY_bs, v_LAST_UPDATE_LOGIN_bs,
v_REQUEST_ID_bs, v_PROGRAM_APPLICATION_ID_bs, v_PROGRAM_ID_bs,
v_PROGRAM_UPDATE_DATE_bs, v_ATTRIBUTE_CATEGORY_bs, v_ATTRIBUTE1_bs,
v_ATTRIBUTE2_bs, v_ATTRIBUTE3_bs, v_ATTRIBUTE4_bs,
v_ATTRIBUTE5_bs, v_ATTRIBUTE6_bs, v_ATTRIBUTE7_bs,
v_ATTRIBUTE8_bs, v_ATTRIBUTE9_bs, v_ATTRIBUTE10_bs,
v_ATTRIBUTE11_bs, v_ATTRIBUTE12_bs, v_ATTRIBUTE13_bs,
v_ATTRIBUTE14_bs, v_ATTRIBUTE15_bs, v_ATTRIBUTE16_bs,
v_ATTRIBUTE17_bs, v_ATTRIBUTE18_bs, v_ATTRIBUTE19_bs,
v_ATTRIBUTE20_bs, null
);
exception
when OTHERS then
null;
v_err_doc := v_err_doc + 1;
v_err_msg := sqlerrm;
v_err_msg := substr(v_err_msg,1,200);
dbms_output.put_line
('ERRO NO INSERT DOS DADOS PESSOAIS - FUNCIONARIO : '||v_employee_number||v_err_msg);
dbms_output.put_line
('ERRO NO SELECT DOS DADOS PESSOAIS - START_DATE : '||v_effective_start_date);
dbms_output.put_line
('ERRO NO SELECT DOS DADOS PESSOAIS - END_DATE : '||v_effective_end_date);
dbms_output.put_line('ERRO NO SELECT DOS DADOS PESSOAIS - PERSON_ID : '||v_person_id);

END;

V_COMM := V_COMM + 1;

IF V_COMM > 10 THEN
V_COMM := 0;
COMMIT;
END IF;
END LOOP;

CLOSE funcionario_info;

-- COMMIT;

dbms_output.put_line('Total de registros rejeitados pessoa --> '||v_err_pessoa);
dbms_output.put_line('Total de registros processados pessoa --> '||v_total_pessoa);
dbms_output.put_line('Total de registros rejeitados doc --> '||v_err_doc);
dbms_output.put_line('Total de registros processados doc --> '||v_total_doc);

END cria_funcionario;

[diegolenhardt]

muito provavelmente o valor que esta retornando no cursor para a coluna V_PASSPORT_EXPIRATION_DATE não está vindo Date, então não está conseguindo atribuir a variavel.

[victorhugomuniz]

Selecionar tudo

LOOP
fetch dadosPessoais_func into
v_PERSON_ID, v_EFFECTIVE_START_DATE, v_EFFECTIVE_END_DATE,
v_EDUCATION_LEVEL, v_CITY_OF_BIRTH, v_STATE_OF_BIRTH,
v_NATURALIZED_FLAG, v_ARRIVAL_DATE_COUNTRY, v_RNE,
v_VISA_TYPE, v_VISA_EXPIRATION_DATE, v_PASSPORT_NUMBER,
v_PASSPORT_EXPIRATION_DATE,

esse cara
dadosPessoais_func
não esta colocando uma data em v_PASSPORT_EXPIRATION_DATE

[fsitja]

Caramba...

Cara, declara uma variável assim lá no seu DECLARE:

Selecionar tudo

var_dadosPessoais_func dadosPessoais_func%rowtype;

E dá o fetch para dentro dela. Vai ficar muito mais limpo e menos tenebroso o código.