--添加acl和权限控制（sql语句执行的方式来执行）
begin
dbms_network_acl_admin.create_acl ( -- 创建访问控制文件（ACL）
acl => 'utl_http.xml', -- 文件名称
description => 'HTTP Access', -- 描述
principal => 'DY', -- 授权或者取消授权账号，大小写敏感
is_grant => TRUE, -- 授权还是取消授权
privilege => 'connect', -- 授权或者取消授权的权限列表
start_date => null, -- 起始日期
end_date => null -- 结束日期
);
dbms_network_acl_admin.add_privilege ( -- 添加访问权限列表项
acl => 'utl_http.xml', -- 刚才创建的acl名称
principal => 'DY', -- 授权或取消授权用户
is_grant => TRUE, -- 与上同
privilege => 'resolve', -- 权限列表
start_date => null,
end_date => null
);
dbms_network_acl_admin.assign_acl ( -- 该段命令意思是允许访问acl名为utl_http.xml下授权的用户，使用oracle网络访问包，所允许访问的目的主机，及其端口范围。
acl => 'utl_http.xml',
host => '127.0.0.1', -- ip地址或者域名，填写https://localhost:9000/hello与https://localhost:9000/是会报host无效的
-- 且建议使用ip地址或者使用域名，若用localhost，当oracle不是安装在本机上的情况下，会出现问题
lower_port => 8080, -- 允许访问的起始端口号
upper_port => Null -- 允许访问的截止端口号
);
commit;
end;
begin
dbms_network_acl_admin.assign_acl ( -- 该段命令意思是允许访问acl名为utl_http.xml下授权的用户，使用oracle网络访问包，所允许访问的目的主机，及其端口范围。
acl => 'utl_http.xml',
host => '218.97.55.25', -- ip地址或者域名，填写https://localhost:9000/hello与https://localhost:9000/是会报host无效的
-- 且建议使用ip地址或者使用域名，若用localhost，当oracle不是安装在本机上的情况下，会出现问题
lower_port => 8080, -- 允许访问的起始端口号
upper_port => Null -- 允许访问的截止端口号
);
commit;
end;使用案例
--创建包名
create or replace package emp_pkg is
FUNCTION post_return(
p_contract_id in NUMBER, p_user_id in NUMBER) RETURN number;
FUNCTION http_request_return(
p_contract_id in NUMBER, p_user_id in NUMBER) RETURN number;
FUNCTION give_up(
p_contract_id in NUMBER, p_user_id in NUMBER) RETURN number;
FUNCTION give_back(
p_contract_id in NUMBER, p_user_id in NUMBER) RETURN number;
procedure post_test(
p_contract_id in NUMBER, p_user_id in NUMBER);
procedure http_request(
p_contract_id in NUMBER, p_user_id in NUMBER);
procedure check_pdf_status(
p_contract_id in NUMBER, p_user_id in NUMBER);
procedure log_new
(p_log VARCHAR2, p_user_id number);
FUNCTION get_table(p_log VARCHAR2) RETURN t_table;
FUNCTION get_table2(p_log VARCHAR2) RETURN t_table;
FUNCTION workflow_approve(p_contract_id in NUMBER) RETURN varchar2;
procedure update_ssq_version(p_contract_id NUMBER);
procedure contract_content_create_new(p_contract_id number,p_user_id number,p_contract_content_sign varchar2 ,p_content_type varchar2 );
FUNCTION return_templeid(p_contract_id in NUMBER) RETURN varchar2;
FUNCTION return_two_tmp(temp_id in NUMBER,p_contract_id in number) RETURN varchar2;
end;
create or replace package body emp_pkg
is
FUNCTION post_return(p_contract_id NUMBER, p_user_id NUMBER) return number IS
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024); -- URL to post to
v_url VARCHAR2(4000) := 'http://218.97.55.25:8080/deewinfl/autowriteoff/adminwordtest';--访问地址
v_param VARCHAR2(4000) := '?contract_id=' || p_contract_id || '&p_user_id=' ||p_user_id;
v_param_length NUMBER := LENGTHB(v_param);
v_json json;
v_result number;
e_error EXCEPTION;
begin
emp_pkg.log_new(p_log => '=====电子签约接口进入=======', p_user_id => p_user_id);
cus_weixin_content_pkg.create_sign_pdf(p_contract_id => p_contract_id);
DBMS_OUTPUT.ENABLE (buffer_size=>null);
req := UTL_HTTP.BEGIN_REQUEST (url=> v_url||v_param, method => 'POST');
UTL_HTTP.SET_BODY_CHARSET('UTF-8');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Type',
value => 'application/x-www-form-urlencoded');
UTL_HTTP.SET_HEADER(req, 'Keep-Alive', ' timeout=1');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Length',
value => v_param_length);
UTL_HTTP.WRITE_RAW (r => req,data => UTL_RAW.CAST_TO_RAW(v_param));
resp := UTL_HTTP.GET_RESPONSE(req);
UTL_HTTP.READ_LINE(resp, value, TRUE);
emp_pkg.log_new(p_log => '电子签约接口触发:'||value, p_user_id => p_user_id);
v_result:=to_number(replace(value,'"',''));
--更新更新目录
emp_pkg.update_ssq_version(p_contract_id => p_contract_id);
/* EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);*/
RETURN v_result;
end;
FUNCTION http_request_return(p_contract_id NUMBER, p_user_id NUMBER) return number IS
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024); -- URL to post to
v_url VARCHAR2(4000) := 'http://218.97.55.25:8080/deewinfl/autowriteoff/adminwordover';--访问地址
v_param VARCHAR2(4000) := '?contract_id=' || p_contract_id || '&p_user_id=' ||p_user_id;
v_param_length NUMBER := LENGTHB(v_param);
v_json json;
v_result number;
e_error EXCEPTION;
begin
emp_pkg.log_new(p_log => '=====电子签约接口进入=======', p_user_id => p_user_id);
cus_weixin_content_pkg.create_sign_pdf(p_contract_id => p_contract_id);
DBMS_OUTPUT.ENABLE (buffer_size=>null);
req := UTL_HTTP.BEGIN_REQUEST (url=> v_url||v_param, method => 'POST');
UTL_HTTP.SET_BODY_CHARSET('UTF-8');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Type',
value => 'application/x-www-form-urlencoded');
UTL_HTTP.SET_HEADER(req, 'Keep-Alive', ' timeout=1');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Length',
value => v_param_length);
UTL_HTTP.WRITE_RAW (r => req,data => UTL_RAW.CAST_TO_RAW(v_param));
resp := UTL_HTTP.GET_RESPONSE(req);
UTL_HTTP.READ_LINE(resp, value, TRUE);
emp_pkg.log_new(p_log => '电子签约接口触发:'||value, p_user_id => p_user_id);
v_result:=to_number(replace(value,'"',''));
/* EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);*/
RETURN v_result;
end;
--作废
FUNCTION give_up(p_contract_id NUMBER, p_user_id NUMBER) return number IS
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024); -- URL to post to
v_url VARCHAR2(4000) := 'http://218.97.55.25:8080/deewinfl/autowriteoff/give_up_back';--访问地址
v_param VARCHAR2(4000) := '?contract_id=' || p_contract_id || '&p_user_id=' ||p_user_id||'&stepcode=B2';
v_param_length NUMBER := LENGTHB(v_param);
v_json json;
v_result number;
e_error EXCEPTION;
begin
emp_pkg.log_new(p_log => '=====电子签约接口进入=======', p_user_id => p_user_id);
--cus_weixin_content_pkg.create_sign_pdf(p_contract_id => p_contract_id);
DBMS_OUTPUT.ENABLE (buffer_size=>null);
req := UTL_HTTP.BEGIN_REQUEST (url=> v_url||v_param, method => 'POST');
UTL_HTTP.SET_BODY_CHARSET('UTF-8');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Type',
value => 'application/x-www-form-urlencoded');
UTL_HTTP.SET_HEADER(req, 'Keep-Alive', ' timeout=1');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Length',
value => v_param_length);
UTL_HTTP.WRITE_RAW (r => req,data => UTL_RAW.CAST_TO_RAW(v_param));
resp := UTL_HTTP.GET_RESPONSE(req);
UTL_HTTP.READ_LINE(resp, value, TRUE);
emp_pkg.log_new(p_log => '电子签约接口触发:'||value, p_user_id => p_user_id);
v_result:=to_number(replace(value,'"',''));
/* EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);*/
RETURN v_result;
end;
--撤回
FUNCTION give_back(p_contract_id NUMBER, p_user_id NUMBER) return number IS
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024); -- URL to post to
v_url VARCHAR2(4000) := 'http://218.97.55.25:8080/deewinfl/autowriteoff/give_up_back';--访问地址
v_param VARCHAR2(4000) := '?contract_id=' || p_contract_id || '&p_user_id=' ||p_user_id||'&stepcode=B1';
v_param_length NUMBER := LENGTHB(v_param);
v_json json;
v_result number;
e_error EXCEPTION;
begin
emp_pkg.log_new(p_log => '=====电子签约接口进入=======', p_user_id => p_user_id);
--cus_weixin_content_pkg.create_sign_pdf(p_contract_id => p_contract_id);
DBMS_OUTPUT.ENABLE (buffer_size=>null);
req := UTL_HTTP.BEGIN_REQUEST (url=> v_url||v_param, method => 'POST');
UTL_HTTP.SET_BODY_CHARSET('UTF-8');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Type',
value => 'application/x-www-form-urlencoded');
UTL_HTTP.SET_HEADER(req, 'Keep-Alive', ' timeout=1');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Length',
value => v_param_length);
UTL_HTTP.WRITE_RAW (r => req,data => UTL_RAW.CAST_TO_RAW(v_param));
resp := UTL_HTTP.GET_RESPONSE(req);
UTL_HTTP.READ_LINE(resp, value, TRUE);
emp_pkg.log_new(p_log => '电子签约接口触发:'||value, p_user_id => p_user_id);
v_result:=to_number(replace(value,'"',''));
/* EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);*/
RETURN v_result;
end;
PROCEDURE post_test(p_contract_id NUMBER, p_user_id NUMBER) IS
v_electronic_flag VARCHAR2(50);
v_bp_id_tenant NUMBER;
r_hls_bp_master hls_bp_master%ROWTYPE;
v_part_response varchar2(500);
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024); -- URL to post to
v_url VARCHAR2(4000) := 'http://218.97.55.25:8080/deewinfl/autowriteoff/adminwordtest';--访问地址
v_param VARCHAR2(4000) := '?contract_id=' || p_contract_id || '&p_user_id=' ||p_user_id;
v_param_length NUMBER := LENGTHB(v_param);
v_json json;
v_result VARCHAR2(2000);
e_error EXCEPTION;
begin
--emp_pkg.log_new(p_log => '=====电子签约接口进入=======' p_user_id => p_user_id);
--cus_weixin_content_pkg.create_sign_pdf(p_contract_id => p_contract_id);
DBMS_OUTPUT.ENABLE (buffer_size=>null);
req := UTL_HTTP.BEGIN_REQUEST (url=> v_url||v_param, method => 'POST');
UTL_HTTP.SET_BODY_CHARSET('UTF-8');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Type',
value => 'application/x-www-form-urlencoded');
UTL_HTTP.SET_HEADER(req, 'Keep-Alive', ' timeout=1');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Length',
value => v_param_length);
--UTL_HTTP.WRITE_RAW (r => req,data => UTL_RAW.CAST_TO_RAW(v_param));
--resp := UTL_HTTP.GET_RESPONSE(req);
--utl_http.read_text(resp, v_part_response);
--UTL_HTTP.READ_LINE(resp, value, TRUE);
--emp_pkg.log_new(p_log => '电子签约接口触发:'||value,p_user_id => p_user_id);
--UTL_HTTP.END_RESPONSE(resp);
value:='0';
IF value!='1' THEN
RAISE e_error;
END IF;
EXCEPTION
/* WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);*/
WHEN e_error THEN
-- utl_http.end_response(resp);
sys_raise_app_error_pkg.raise_sys_others_error(p_message => '电子签约接口异常，code:'||value,
p_created_by => p_user_id,
p_package_name => 'emp_pkg',
p_procedure_function_name => 'post_test');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
WHEN OTHERS THEN
utl_http.end_response(resp);
end;
PROCEDURE http_request(p_contract_id NUMBER, p_user_id NUMBER) IS
v_electronic_flag VARCHAR2(50);
v_bp_id_tenant NUMBER;
r_hls_bp_master hls_bp_master%ROWTYPE;
v_id_number VARCHAR2(200);
v_weixin_open_id VARCHAR2(500);
v_param VARCHAR2(1000);
v_word_to_pdf_url VARCHAR2(2000);
v_push_sign_pdf VARCHAR2(2000);
v_contract_id NUMBER;
v_Parties_Paid_Flag VARCHAR2(20);
v_con_rec con_contract%rowtype;
v_bp_name VARCHAR2(200);
v_vehicle_class VARCHAR2(200);
v_sing_count NUMBER;
v_bp_count NUMBER;
begin
DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024); -- URL to post to
v_url VARCHAR2(4000) := 'http://218.97.55.25:8080/deewinfl/autowriteoff/adminwordover';--访问地址
v_param VARCHAR2(4000) := '?contract_id=' || p_contract_id || '&p_user_id=' ||p_user_id;
v_param_length NUMBER := LENGTHB(v_param);
BEGIN
cus_weixin_content_pkg.create_sign_pdf(p_contract_id => p_contract_id);
DBMS_OUTPUT.ENABLE (buffer_size=>null);
req := UTL_HTTP.BEGIN_REQUEST (url=> v_url||v_param, method => 'POST');
UTL_HTTP.SET_BODY_CHARSET('UTF-8');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Type',
value => 'application/x-www-form-urlencoded');
UTL_HTTP.SET_HEADER(req, 'Keep-Alive', ' timeout=1');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Length',
value => v_param_length);
UTL_HTTP.WRITE_RAW (r => req,
data => UTL_RAW.CAST_TO_RAW(v_param));
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
DBMS_OUTPUT.PUT_LINE(value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
end;
end;
PROCEDURE check_pdf_status(p_contract_id NUMBER, p_user_id NUMBER) IS
v_check_pdf_status VARCHAR2(30);
v_count NUMBER;
e_status_error EXCEPTION;
begin
-- add by 添加签署状态校验
SELECT COUNT(*)
INTO v_count
FROM con_contract_content sta
WHERE sta.CONTRACT_ID = p_contract_id and check_pdf_status<>'Y';
if v_count>0 then
RAISE e_status_error;
end if;
EXCEPTION
WHEN e_status_error THEN
sys_raise_app_error_pkg.raise_sys_others_error(p_message => '请确认签署状态再提交',
p_created_by => p_user_id,
p_package_name => 'emp_pkg',
p_procedure_function_name => 'http_request');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
end;
PROCEDURE log_new(p_log VARCHAR2, p_user_id number) AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_log_id NUMBER;
BEGIN
SELECT emp_pkg_log_s.NEXTVAL INTO v_log_id FROM dual;
INSERT INTO emp_pkg_log
(log_id,
process_date,
log_desc,
created_by,
creation_date)
VALUES
(v_log_id, SYSDATE, p_log, p_user_id, SYSDATE);
COMMIT;
END log_new;
--担保人信息
FUNCTION get_table(p_log VARCHAR2) RETURN t_table IS
rs t_table:= t_table();
BEGIN
for item in (
select rownum ec11_guar_num, t.* from (select (select hb.bp_name
from hls_bp_master hb
where hb.bp_id = t.bp_id) ec11_guar_name,
(select hb.corporate_code
from hls_bp_master hb
where hb.bp_id = t.bp_id) ec11_guar_repre,
(select hba.phone
from hls_bp_master_address hba
where hba.bp_id = t.bp_id
and hba.address_type = 'DOC_SENT_ADDRESS'
and rownum = 1) ec11_guar_phone,
(select (select p.description
from fnd_province p
where p.province_id = hbma.province_id) ||
(select ci.description
from fnd_city ci
where ci.city_id = hbma.city_id) ||
(select d.description
from fnd_district d
where d.district_id = hbma.district_id) ||
hbma.address
from hls_bp_master_address hbma
where hbma.bp_id = t.bp_id
and hbma.address_type = 'DOC_SENT_ADDRESS'
and rownum = 1) ec11_guar_addr
from con_contract_bp t
where t.contract_id =p_log
and bp_category = 'GUARANTOR'
union all
select hbm.bp_name_sp ec11_guar_name,
null ec11_guar_repre,
hbm.cell_phone_sp ec11_guar_phone, (select (select p.description from fnd_province p where p.province_id = hbm.living_add_province) || (select p.description from fnd_province p where p.province_id = hbma.province_id) || (select ci.description from fnd_city ci where ci.city_id = hbma.city_id) || (select d.description from fnd_district d where d.district_id = hbma.district_id) || hbma.address from hls_bp_master_address hbma where hbma.bp_id = hbm.bp_id and hbma.address_type = 'DOC_SENT_ADDRESS' and rownum = 1) ec11_guar_addr from con_contract_bp cp, hls_bp_master hbm where cp.bp_id = hbm.bp_id
and cp.contract_id =p_log and cp.bp_category = 'TENANT' and hbm.bp_class = 'NP' and hbm.marital_status='10' and exists (select * from con_contract c where c.contract_id = cp.contract_id and nvl(c.ten_sp_flag, 'N') = 'Y') union all select hbm.bp_name_sp ec11_guar_name, null ec11_guar_repre, hbm.cell_phone_sp ec11_guar_phone, (select (select p.description from fnd_province p where p.province_id = hbm.living_add_province) || (select p.description from fnd_province p where p.province_id = hbma.province_id) || (select ci.description from fnd_city ci where ci.city_id = hbma.city_id) || (select d.description from fnd_district d where d.district_id = hbma.district_id) || hbma.address from hls_bp_master_address hbma where hbma.bp_id = hbm.bp_id and hbma.address_type = 'DOC_SENT_ADDRESS' and rownum = 1) ec11_guar_addr from con_contract_bp cp, hls_bp_master hbm where cp.bp_id = hbm.bp_id
and cp.contract_id = p_log and cp.bp_category = 'GUARANTOR' and hbm.bp_class = 'NP' and hbm.marital_status='10' and exists (select * from con_contract c where c.contract_id = cp.contract_id
and nvl(c.gua_sp_flag, 'N') = 'Y')) t order by t.ec11_guar_name
) loop
rs.extend;
rs(rs.count) := obj_table('保证人：（以下简称丙方）');
rs.extend;
rs(rs.count) := obj_table('法定代表人：'||item.ec11_guar_name);
/* rs.extend;
rs(rs.count) := obj_table(item.ec11_guar_repre);*/
rs.extend;
rs(rs.count) := obj_table('送达地址：'||item.ec11_guar_addr);
rs.extend;
rs(rs.count) := obj_table('联系电话：'||item.ec11_guar_phone);
rs.extend;
rs(rs.count) := obj_table('');
end loop;
RETURN rs;
END;
FUNCTION get_table2(p_log VARCHAR2) RETURN t_table IS
rs t_table:= t_table();
BEGIN
for item in (
select rownum ec11_guar_count_sign, t.* from
(select (select nvl(hb.invoice_title, hb.bp_name)
from hls_bp_master hb where hb.bp_id = t.bp_id) ec11_guar_name_sign,
(select hb.corporate_code from hls_bp_master hb where hb.bp_id = t.bp_id)
ec11_guar_re_sign, null ec11_guar_auth_sign, null ec11_null, null ec11_null2, null ec11_null3, null ec11_null4, null ec11_null5 from con_contract_bp t
where t.contract_id =p_log and bp_category = 'GUARANTOR' union all select hbm.bp_name_sp ec11_guar_name_sign, null ec11_guar_repre, null ec11_guar_auth_sign, null ec11_null, null ec11_null2, null ec11_null3, null ec11_null4, null ec11_null5 from con_contract_bp cp, hls_bp_master hbm where cp.bp_id = hbm.bp_id
and cp.contract_id =p_log and cp.bp_category = 'TENANT' and hbm.bp_class = 'NP' and hbm.marital_status='10' and exists (select * from con_contract c where c.contract_id = cp.contract_id and nvl(c.ten_sp_flag, 'N') = 'Y') union all select hbm.bp_name_sp ec11_guar_name_sign, null ec11_guar_repre, null ec11_guar_auth_sign, null ec11_null, null ec11_null2, null ec11_null3, null ec11_null4, null ec11_null5 from con_contract_bp cp, hls_bp_master hbm where cp.bp_id = hbm.bp_id
and cp.contract_id =p_log and cp.bp_category = 'GUARANTOR' and hbm.bp_class = 'NP' and hbm.marital_status='10' and exists (select * from con_contract c where c.contract_id = cp.contract_id
and nvl(c.gua_sp_flag, 'N') = 'Y')) t order by t.ec11_guar_name_sign
) loop
/*rs.extend;
rs(rs.count) := obj_table(item.ec11_guar_count_sign);*/
rs.extend;
rs(rs.count) := obj_table('丙方：'||item.ec11_guar_name_sign);
rs.extend;
rs(rs.count) := obj_table('法定代表人：'||item.ec11_guar_name_sign);
rs.extend;
rs(rs.count) := obj_table('授权代表人：'||item.ec11_guar_auth_sign);
rs.extend;
rs(rs.count) := obj_table(item.ec11_null);
/*rs.extend;
rs(rs.count) := obj_table(item.ec11_null2);
rs.extend;
rs(rs.count) := obj_table(item.ec11_null3);
rs.extend;
rs(rs.count) := obj_table(item.ec11_null4);
rs.extend;
rs(rs.count) := obj_table(item.ec11_null5);*/
end loop;
RETURN rs;
END;
FUNCTION workflow_approve(p_contract_id NUMBER) RETURN varchar2 IS
v_count number;
v_instance_id number;
v_workflow_code VARCHAR2(500);
v_record_id NUMBER;
v_node_action_id NUMBER;
v_approved_flag NUMBER;
v_flag_buss NUMBER;
v_ele_flag VARCHAR2(50);
BEGIN
--拿到正常合同v_instance_id
SELECT cc.wfl_sign_instance_id
INTO v_instance_id
FROM con_contract cc
WHERE cc.contract_id = p_contract_id;
--拿到 v_workflow_code
SELECT (SELECT zww.workflow_code
FROM zj_wfl_workflow zww
WHERE zww.workflow_id = t.workflow_id)
INTO v_workflow_code
FROM zj_wfl_workflow_instance t
WHERE t.instance_id = v_instance_id;
--获取v_record_id，v_node_action_id
--根据合同ID找出ID找出instance_id，然后限定为工作流的30节点，10的同意操作
SELECT t1.record_id, n1.node_action_id
INTO v_record_id, v_node_action_id
FROM zj_wfl_instance_node_recipient t1
INNER JOIN zj_wfl_workflow_node n
ON t1.node_id = n.node_id
INNER JOIN zj_wfl_workflow_node_action n1
ON t1.node_id = n1.node_id
WHERE t1.instance_id = v_instance_id
AND n1.action_type = 1
AND n.sequence_num = 30
AND n1.sequence_num = 10
AND rownum = 1;
v_approved_flag := zj_wfl_core_pkg.workflow_approve(p_rcpt_record_id => v_record_id,
p_node_action_id => v_node_action_id,
p_comment => '',
p_comment_text_out => '',
p_user_id => 1);
return v_approved_flag||'';
END;
PROCEDURE update_ssq_version(p_contract_id NUMBER) AS
v_times NUMBER;
v_catalog_name VARCHAR2(500);
v_contract_deewinfl_number VARCHAR2(200);
BEGIN
SELECT tt.contract_deewinfl_number, nvl(tt.ssq_times, 0)
INTO v_contract_deewinfl_number, v_times
FROM con_contract tt
WHERE tt.contract_id = p_contract_id;
v_times := v_times + 1;
v_catalog_name := v_contract_deewinfl_number || '_' || v_times;
hls_sys_log_pkg.log('更新目录：p_contract_id：' || p_contract_id ||
',v_catalog_name:' || v_catalog_name ||
',v_times:' || v_times);
UPDATE con_contract tt
SET tt.ssq_times = v_times, tt.ssq_catalog_name = v_catalog_name
WHERE tt.contract_id = p_contract_id;
COMMIT;
UPDATE cus_weixin_sign_pdf_records tt
SET tt.sign_date=sysdate,tt.ssq_catalog_name = v_catalog_name
WHERE tt.contract_id = p_contract_id;
COMMIT;
END;
--根据新的字段判断是否要生成车辆所有权的两个新合同
PROCEDURE contract_content_create_new(p_contract_id number,
p_user_id number,
p_contract_content_sign varchar2 ,
p_content_type varchar2) is
v_times NUMBER;
v_catalog_name VARCHAR2(500);
v_contract_deewinfl_number VARCHAR2(200);
BEGIN
SELECT tt.contract_deewinfl_number, nvl(tt.ssq_times, 0)
INTO v_contract_deewinfl_number, v_times
FROM con_contract tt
WHERE tt.contract_id = p_contract_id;
v_times := v_times + 1;
v_catalog_name := v_contract_deewinfl_number || '_' || v_times;
hls_sys_log_pkg.log('更新目录：p_contract_id：' || p_contract_id ||
',v_catalog_name:' || v_catalog_name ||
',v_times:' || v_times);
UPDATE con_contract tt
SET tt.ssq_times = v_times, tt.ssq_catalog_name = v_catalog_name
WHERE tt.contract_id = p_contract_id;
COMMIT;
UPDATE cus_weixin_sign_pdf_records tt
SET tt.sign_date=sysdate,tt.ssq_catalog_name = v_catalog_name
WHERE tt.contract_id = p_contract_id;
COMMIT;
END;
FUNCTION return_templeid(p_contract_id NUMBER) RETURN varchar2 IS
v_temp_id number;
v_type VARCHAR2(500);
v_guaranttype VARCHAR2(500);
BEGIN
--拿到类型
SELECT cc.guaranttype
INTO v_guaranttype
FROM con_contract cc
WHERE cc.contract_id = p_contract_id;
if v_guaranttype='ROUTINE' THEN --常规
v_type:='EC21_OWNERSHIP';
end if;
if v_guaranttype='GUARANT' THEN --担保
v_type:='EC21_OWNERSHIP_GUARANTOR';
end if ;
if v_guaranttype='RETURN' THEN --见物回购
v_type:='EC21_OWNERSHIP_SHOP';
end if ;
--v_temp_id
SELECT cc.templet_id
INTO v_temp_id
FROM con_clause_templet cc
where cc.enabled_flag = 'Y' and cc.templet_code=v_type;
return v_temp_id;
END;
FUNCTION return_two_tmp(temp_id NUMBER,p_contract_id NUMBER) RETURN varchar2 IS
v_temp_code VARCHAR2(500);
v_type VARCHAR2(500);
v_guaranttype VARCHAR2(500);
v_tmpt_count NUMBER;
BEGIN
--拿到类型
SELECT cc.templet_code
INTO v_temp_code
FROM con_clause_templet cc
where cc.templet_id=temp_id;
if v_temp_code='DW_CON_PAY_PROOF' THEN
v_type:='Y';
end if;
if v_temp_code='NOTICE_RENT' THEN
v_type:='Y';
end if ;
select count(*)
into v_tmpt_count
from con_contract_content cc
where cc.contract_id = p_contract_id
and cc.templet_id = temp_id;
if v_tmpt_count>0 THEN
v_type:='N';
end if ;
return v_type;
END;
end emp_pkg;
989
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
