create or replace procedure COMMI_BANK_VALIDATION_PROC(p_month IN varchar2,
p_operator IN varchar2,
p_state OUT char)
/************************************************************************
过程名称：COMMI_BANK_VALIDATION_PROC
输入参数：
p_month:验证月份，格式：201112
p_operator:操作人ID
输出参数：p_state(0：未同步数据，无法验证，1：没有验证失败数据， 2：验证完成存在失败数据)
返回值： 无
功能描述：佣金模块，每月银行信息验证，包含：基本验证、以及银行信息错误上次支付失败本次没有更新银行信息。
操作流程：1、查询当月生成的银行信息，如果佣金月份表中状态为还未计算则返回0。
2、银行信息验证
3、将验证失败的数据核对状态进行修改为核对失败，并将对应的描述信息更新
---------------------------------------------------------------------
版本： V1.0.0
创建人: lifeng
创建日期: 2011-12-31
修改人： lifeng
修改日期：
修改说明：
************************************************************************/
is
l_is_sync tb_commi_month_agent_bill.is_sync%type; --是否可以同步：默认0不能同步，1：可以同步。从月份表中获取
l_last_month varchar(6); --当前操作月份的上一月份
l_last_month_paid_fail number; --上月是否支付失败
l_bank_is_update number; --银行信息是否进行修改
l_bank_no tb_commi_agent_bill.bank_no%type; --银行卡号
/*需修改账单表的变量*/
l_bill_state tb_commi_agent_bill.bill_state%type; --账单状态,1代表核对失败
l_bill_msg tb_commi_agent_bill.bill_msg%type; --账单描述
l_check_type tb_commi_agent_bill.check_type%type := '0'; --检查类型，0代表系统自动验证
--自定义账单类型定义
type commi_bill_type is record(
bill_id tb_commi_agent_bill.bill_id%type,
EMP_NO tb_commi_agent_bill.emp_no%type,
bank_no tb_commi_agent_bill.bank_no%type,
bank_name tb_commi_agent_bill.bank_name%type,
bank_branches tb_commi_agent_bill.bank_branches%type,
bank_account tb_commi_agent_bill.bank_account%type);
--账单类型对象创建
commi_bill commi_bill_type := null;
--账单游标定义
cursor commi_bill_cursor is
select ab.bill_id,
ab.emp_no,
ab.bank_no,
ab.bank_name,
ab.bank_branches,
ab.bank_account
from tb_commi_agent_bill ab
where ab.bill_month = p_month;
cursor commi_bank_cursor is
select bank_no
from (select bank_no, count(*) c
from (select bank_no
from tb_commi_agent_bill b
where b.bill_month = p_month
group by b.bank_no,
b.bank_name,
b.bank_branches,
b.bank_account) s
group by s.bank_no) s1
where s1.c > 1;
begin
begin
select is_sync
into l_is_sync
from tb_commi_month_agent_bill ab
where ab.bill_month = p_month;
exception
when no_data_found then
l_is_sync := '0';
end;
--是否满足同步条件
if l_is_sync is null or l_is_sync = '0' then
p_state := '0';
else
p_state := '1';
--提前将当月系统进行核对失败的数据清除
begin
update tb_commi_agent_bill ab
set check_type = '', ab.bill_state = 0, ab.bill_msg = ''
where check_type = '0'
and ab.bill_month = p_month
and ab.bill_state = 1;
commit;
exception
when others then
rollback;
end;
--卡号重复，其他信息不一致验证
open commi_bank_cursor;
loop
fetch commi_bank_cursor
into l_bank_no;
exit when commi_bank_cursor%NOTFOUND;
begin
update tb_commi_agent_bill
set bill_state = 1,
bill_msg = '重复的银行卡号，但开户信息不一致',
check_type = '0',
check_time = to_char(sysdate, 'yyyymmddhh24miss'),
check_staff = p_operator,
oper_time = to_char(sysdate, 'yyyymmddhh24miss'),
operator = p_operator
where bank_no = l_bank_no
and bill_state <= 2 and bill_month=p_month;
commit;
exception
when others then
rollback;
end;
p_state := '2';
end loop;
close commi_bank_cursor;
--满足同步条件
open commi_bill_cursor;
loop
fetch commi_bill_cursor
into commi_bill;
exit when commi_bill_cursor%NOTFOUND;
l_bill_state := 0;
l_bill_msg := '';
--银行信息基本验证
if commi_bill.bank_no is null or commi_bill.bank_no = '' then
l_bill_state := 1;
l_bill_msg := '银行卡号有误';
elsif commi_bill.bank_name is null or commi_bill.bank_name = '' then
l_bill_state := 1;
l_bill_msg := '银行名称有误';
elsif commi_bill.bank_branches is null or
commi_bill.bank_branches = '' then
l_bill_state := 1;
l_bill_msg := '银行支行有误';
elsif commi_bill.bank_account is null or commi_bill.bank_account = '' then
l_bill_state := 1;
l_bill_msg := '开户人姓名有误';
end if;
--基本信息验证成功，则继续验证上月是否支付失败没有修改银行信息
if l_bill_state != 1 then
--上月月份
select to_char(to_date(p_month, 'yyyymm') - interval '1' month,
'yyyymm')
into l_last_month
from dual;
--上月支付失败查询
select count(bill_id)
into l_last_month_paid_fail
from tb_commi_agent_bill ab
where ab.bill_month = l_last_month
and ab.emp_no = commi_bill.emp_no
and ab.bill_state = 4;
--上月是否存在支付失败
if l_last_month_paid_fail > 0 then
--银行信息是否修改
select count(*)
into l_bank_is_update
from (select ab.bank_no
from tb_commi_agent_bill ab
where ab.emp_no = commi_bill.emp_no
and ab.bill_month in (l_last_month, p_month)
group by ab.bank_no,
ab.bank_name,
ab.bank_branches,
ab.bank_account) a;
if l_bank_is_update = 1 then
l_bill_state := 1;
l_bill_msg := '上月存在支付失败退回记录，本次银行信息无更改，不能进行正常发放';
end if;
end if;
end if;
if (l_bill_state = 1) then
--状态小于等于1代表核对失败和未核对，修改条件成立
/*修改账单动作*/
begin
update tb_commi_agent_bill
set bill_state = l_bill_state,
bill_msg = l_bill_msg,
check_type = l_check_type,
check_time = to_char(sysdate, 'yyyymmddhh24miss'),
check_staff = p_operator,
oper_time = to_char(sysdate, 'yyyymmddhh24miss'),
operator = p_operator
where bill_id = commi_bill.bill_id;
commit;
exception
when others then
rollback;
end;
p_state := '2';
end if;
end loop;
close commi_bill_cursor;
end if;
end COMMI_BANK_VALIDATION_PROC;