CREATE OR REPLACE PROCEDURE P_ADD_PROPERTY_CONTRACT AS
/**
物业合同表数据插入
2012年10月18日14:51:40
liys
**/
cursor contract IS
select * from TMP_PROPERTY_CONTRACT;
id varchar2(20);
con_num varchar2(50);--归属合同号
fac_name varchar2(50);--供应商
fac_code varchar2(50);--供应商编码
p_type varchar2(5);--合同类型
dt varchar2(20);--所在单位
cou_branch varchar2(30);--县级分公司
s_time date;--合同起始
e_time date;--合同结束
eff number(3);--有效月
amount number:=0;
begin
open contract;
loop
fetch contract into id, con_num, fac_name, p_type, dt, cou_branch, s_time, e_time, eff;
exit when contract%NOTFOUND;
select count(1) into amount from supplier s where s.pro_name = fac_name;--判断是否存在
if amount>0 then
select s.pro_code into fac_code from supplier s where s.pro_name = fac_name;--取得供应商编码
end if;
p_type := f_get_dictionary_code('proType',p_type);--取得合同类型编码
dt := f_get_dictionary_code('company',dt);--取得所在单位编码
cou_branch := f_get_dictionary_code('county',cou_branch);--取得县级分公司编码
--存入物业合同表
insert into PROPERTY_CONTRACT(RESOURCEID,Contract_Num,Factory,Factorycode,Pro_Type,Dept,County_Branch,Start_Time,End_Time,Effectivem)
values(SYS_GUID(), con_num, fac_name, fac_code, p_type, dt, cou_branch, s_time, e_time, eff);
commit;
fac_code:='';
end loop;
close contract;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(fac_name);
dbms_output.put_line(SQLERRM);
ROLLBACK;
END;