求大神这个怎么报无效数字的错误啊？？？create or replace package body cux_ebs_syn_vender_pkg is procedure insert_system_vender_tmp(p_user_id number) is
begin
delete from pur_system_vender_tmp;
insert into pur_system_vender_tmp
select vendor_id vender_id,
vendor_num vender_code,
vendor_type_lookup_code vender_type_id,
null description_id,
null address ,
null artificial_person ,
null tax_id_number ,
branch_num bank_branch_code,
bank_account_num bank_account_code,
null payment_term_id,
null payment_method,
currency_code,
null tax_type_id,
null approved_vender_flag,
null enabled_flag ,
p_user_id created_by ,
sysdate creation_date,
p_user_id last_updated_by,
sysdate last_update_date,
end_date_active end_date_active,
party_site_id,
party_site_name,
vendor_site_id,
vendor_site_code,
end_date_active site_inactive_date,
org_id,
name org_name,
country,
country_code,
address_line1,
address_line2,
address_line3
from appsebs_CUX_AP_VENDORS v;
exception
when others then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' ||
sqlerrm,
p_created_by => 1,
p_package_name => 'cux_ebs_update_data',
p_procedure_function_name => 'insert_customers_tmp');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
end;
procedure load_system_vender(p_user_id number) is
v_pur_system_vender_tmp pur_system_vender_tmp%rowtype;
begin
insert_system_vender_tmp(p_user_id);
begin
--查询不存在的数据
select *
into v_pur_system_vender_tmp
from pur_system_vender_tmp vt
where not exists
(select 1
from pur_system_venders p
where p.vender_id= vt.vender_id
and p.vendor_site_id=vt.vendor_site_id
and p.bank_account_code= vt.bank_account_code);
--不存在则插入
insert into pur_system_venders p
values v_pur_system_vender_tmp;
for cur_line in (select *
from pur_system_vender_tmp vt
where exists (select 1
from pur_system_venders p
where p.vender_id= vt.vender_id
and p.vendor_site_id=vt.vendor_site_id
and p.bank_account_code= vt.bank_account_code)
and not exists
(select 1
from pur_system_venders p
where p.vender_id=vt.vender_id and
p.vender_code=vt.vender_code and
p.vender_type_id=vt.vender_type_id and
p.description_id=vt.description_id and
p.address = vt.address and
p.artificial_person = vt.artificial_person and
p.tax_id_number = vt.tax_id_number and
p.bank_branch_code= vt.bank_branch_code and
p.bank_account_code=vt.bank_account_code and
p.payment_term_id = vt.payment_term_id and
p.payment_method=vt.payment_method and
p.currency_code=vt.currency_code and
p.tax_type_id=vt.tax_type_id and
p.approved_vender_flag=vt.approved_vender_flag and
p.enabled_flag=vt.enabled_flag and
p.created_by=vt.created_by and
p.creation_date=vt.creation_date and
p.last_updated_by=vt.last_updated_by and
p.last_update_date=vt.last_update_date and
p.end_date_active=vt.end_date_active and
p.vendor_site_id=vt.vendor_site_id and
p.party_site_name=vt.party_site_name and
p.vendor_site_code=vt.vendor_site_code and
p.site_inactive_date=vt.site_inactive_date and
p.org_id=vt.org_id and
p.org_name=vt.org_name and
p.country=vt.country and
p.country_code=vt.country_code and
p.address_line1=vt.address_line1 and
p.address_line2=vt.address_line2 and
p.address_line3=vt.address_line3))
loop
begin
update pur_system_venders p
set p.vender_code=cur_line.vender_code ,
p.vender_type_id=cur_line.vender_type_id ,
p.description_id=cur_line.description_id ,
p.address = cur_line.address ,
p.artificial_person = cur_line.artificial_person ,
p.tax_id_number = cur_line.tax_id_number ,
p.bank_branch_code= cur_line.bank_branch_code ,
p.bank_account_code=cur_line.bank_account_code ,
p.payment_term_id = cur_line.payment_term_id ,
p.payment_method=cur_line.payment_method ,
p.currency_code=cur_line.currency_code ,
p.tax_type_id=cur_line.tax_type_id ,
p.approved_vender_flag=cur_line.approved_vender_flag ,
p.enabled_flag=cur_line.enabled_flag ,
p.created_by=cur_line.created_by ,
p.creation_date=cur_line.creation_date ,
p.last_updated_by=cur_line.last_updated_by ,
p.last_update_date=sysdate ,
p.end_date_active=cur_line.end_date_active ,
p.vendor_site_id=cur_line.vendor_site_id ,
p.party_site_name=cur_line.party_site_name ,
p.vendor_site_code=cur_line.vendor_site_code ,
p.site_inactive_date=cur_line.site_inactive_date ,
p.org_id=cur_line.org_id ,
p.org_name=cur_line.org_name ,
p.country=cur_line.country ,
p.country_code=cur_line.country_code ,
p.address_line1=cur_line.address_line1 ,
p.address_line2=cur_line.address_line2 ,
p.address_line3=cur_line.address_line3
where p.vender_id= cur_line.vender_id
and p.vendor_site_id=cur_line.vendor_site_id
and p.bank_account_code= cur_line.bank_account_code;
end;
end loop;
exception
when others then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' ||
sqlerrm,
p_created_by => 1,
p_package_name => 'cux_data_vender',
p_procedure_function_name => 'vendor_synchronize_event');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
end;
end; end cux_ebs_syn_vender_pkg;
begin
delete from pur_system_vender_tmp;
insert into pur_system_vender_tmp
select vendor_id vender_id,
vendor_num vender_code,
vendor_type_lookup_code vender_type_id,
null description_id,
null address ,
null artificial_person ,
null tax_id_number ,
branch_num bank_branch_code,
bank_account_num bank_account_code,
null payment_term_id,
null payment_method,
currency_code,
null tax_type_id,
null approved_vender_flag,
null enabled_flag ,
p_user_id created_by ,
sysdate creation_date,
p_user_id last_updated_by,
sysdate last_update_date,
end_date_active end_date_active,
party_site_id,
party_site_name,
vendor_site_id,
vendor_site_code,
end_date_active site_inactive_date,
org_id,
name org_name,
country,
country_code,
address_line1,
address_line2,
address_line3
from appsebs_CUX_AP_VENDORS v;
exception
when others then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' ||
sqlerrm,
p_created_by => 1,
p_package_name => 'cux_ebs_update_data',
p_procedure_function_name => 'insert_customers_tmp');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
end;
procedure load_system_vender(p_user_id number) is
v_pur_system_vender_tmp pur_system_vender_tmp%rowtype;
begin
insert_system_vender_tmp(p_user_id);
begin
--查询不存在的数据
select *
into v_pur_system_vender_tmp
from pur_system_vender_tmp vt
where not exists
(select 1
from pur_system_venders p
where p.vender_id= vt.vender_id
and p.vendor_site_id=vt.vendor_site_id
and p.bank_account_code= vt.bank_account_code);
--不存在则插入
insert into pur_system_venders p
values v_pur_system_vender_tmp;
for cur_line in (select *
from pur_system_vender_tmp vt
where exists (select 1
from pur_system_venders p
where p.vender_id= vt.vender_id
and p.vendor_site_id=vt.vendor_site_id
and p.bank_account_code= vt.bank_account_code)
and not exists
(select 1
from pur_system_venders p
where p.vender_id=vt.vender_id and
p.vender_code=vt.vender_code and
p.vender_type_id=vt.vender_type_id and
p.description_id=vt.description_id and
p.address = vt.address and
p.artificial_person = vt.artificial_person and
p.tax_id_number = vt.tax_id_number and
p.bank_branch_code= vt.bank_branch_code and
p.bank_account_code=vt.bank_account_code and
p.payment_term_id = vt.payment_term_id and
p.payment_method=vt.payment_method and
p.currency_code=vt.currency_code and
p.tax_type_id=vt.tax_type_id and
p.approved_vender_flag=vt.approved_vender_flag and
p.enabled_flag=vt.enabled_flag and
p.created_by=vt.created_by and
p.creation_date=vt.creation_date and
p.last_updated_by=vt.last_updated_by and
p.last_update_date=vt.last_update_date and
p.end_date_active=vt.end_date_active and
p.vendor_site_id=vt.vendor_site_id and
p.party_site_name=vt.party_site_name and
p.vendor_site_code=vt.vendor_site_code and
p.site_inactive_date=vt.site_inactive_date and
p.org_id=vt.org_id and
p.org_name=vt.org_name and
p.country=vt.country and
p.country_code=vt.country_code and
p.address_line1=vt.address_line1 and
p.address_line2=vt.address_line2 and
p.address_line3=vt.address_line3))
loop
begin
update pur_system_venders p
set p.vender_code=cur_line.vender_code ,
p.vender_type_id=cur_line.vender_type_id ,
p.description_id=cur_line.description_id ,
p.address = cur_line.address ,
p.artificial_person = cur_line.artificial_person ,
p.tax_id_number = cur_line.tax_id_number ,
p.bank_branch_code= cur_line.bank_branch_code ,
p.bank_account_code=cur_line.bank_account_code ,
p.payment_term_id = cur_line.payment_term_id ,
p.payment_method=cur_line.payment_method ,
p.currency_code=cur_line.currency_code ,
p.tax_type_id=cur_line.tax_type_id ,
p.approved_vender_flag=cur_line.approved_vender_flag ,
p.enabled_flag=cur_line.enabled_flag ,
p.created_by=cur_line.created_by ,
p.creation_date=cur_line.creation_date ,
p.last_updated_by=cur_line.last_updated_by ,
p.last_update_date=sysdate ,
p.end_date_active=cur_line.end_date_active ,
p.vendor_site_id=cur_line.vendor_site_id ,
p.party_site_name=cur_line.party_site_name ,
p.vendor_site_code=cur_line.vendor_site_code ,
p.site_inactive_date=cur_line.site_inactive_date ,
p.org_id=cur_line.org_id ,
p.org_name=cur_line.org_name ,
p.country=cur_line.country ,
p.country_code=cur_line.country_code ,
p.address_line1=cur_line.address_line1 ,
p.address_line2=cur_line.address_line2 ,
p.address_line3=cur_line.address_line3
where p.vender_id= cur_line.vender_id
and p.vendor_site_id=cur_line.vendor_site_id
and p.bank_account_code= cur_line.bank_account_code;
end;
end loop;
exception
when others then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' ||
sqlerrm,
p_created_by => 1,
p_package_name => 'cux_data_vender',
p_procedure_function_name => 'vendor_synchronize_event');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
end;
end; end cux_ebs_syn_vender_pkg;
自己整理一下缩进，按照设计的逻辑，看哪里少了一个 BEGIN。