可疑交易报表存储过程

create or replace procedure rp_doubtfultransferdet(in_orgcode varchar2,
in_startdate varchar2,
in_enddate varchar2,
includeSubOrg number,
ResultCursor out
dr_package_sinosteel.rc_report) as
--author：cxjiao
temp_startdate varchar2(20);
temp_enddate varchar2(20);
c_orgCode varchar2(50);
cause varchar2(100);
orglevelcode varchar2(50);
vv_orglevelcode varchar2(50);
--定义一个收款方帐户
type receiveAccount is record(
recaccountno varchar2(100),
countrec number);
receiveAcc receiveAccount;
--定义一个收款方帐户2
type receiveAcc_v2 is record(
recaccountid number,
countrec number,
extaccountno varchar2(100),
countext number);
extaccount receiveAcc_v2;
--定义一个内部付款中线上对线下有相同的收款方
type inaccount is record(
tsdid number,
transferid number,
receiveid number);
in_account inaccount;
--定义一个对外部付款中线上对线下有相同的收款方
type outaccount is record(
tsdid number,
transferid number,
receiveno varchar2(100));
out_account outaccount;
--有相同的收款方账户id且线下支付1(外部收款方)
cursor rec_downtransfer1(v_recaccountno varchar2, v_orglevelcode varchar2) is
select bs.orgname orgname,
bsb.name bankname,
am.accountno accountno,
bsacc.purposename purposename,
tst.amount amount,
bs.orglevelcode orglevelcode,
tst.id transferdetid
from ts_transferdetail tst,
ts_transfer ts,
bs_organization bs,
am_account am,
bs_bank bsb,
bs_accountpurpose bsacc
where tst.status = 1
and tst.extaccountno = v_recaccountno
and tst.bankpaytype = 2
and tst.transferdetailstatus = 7
and ts.id = tst.transferid
and tst.paybankid = bsb.id
and bsb.status = 1
and ts.status = 1
and am.id = tst.payaccountid
and am.status = 1
and am.accountpurposeid=bsacc.id
and bsacc.status=1
and bs.id = tst.payorgid
and bs.id in (select bso.id
from bs_organization bso
where bso.status = 1
and bso.orglevelcode = v_orglevelcode)
and to_date(to_char(ts.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') >=
to_date(in_startdate, 'YYYY-MM-DD')
and to_date(to_char(ts.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <=
to_date(in_enddate, 'YYYY-MM-DD');
--有相同的收款方账户id且线下支付2（内部收款方）
cursor rec_downtransfer2(v_recaccountid number, v_orglevelcode varchar2) is
select bs.orgname orgname,
bsb.name bankname,
am.accountno accountno,
bsacc.purposename purposename,
tst.amount amount,
bs.orglevelcode orglevelcode,
tst.id transferdetid
from ts_transferdetail tst,
ts_transfer ts,
bs_organization bs,
am_account am,
bs_bank bsb,
bs_accountpurpose bsacc
where tst.status = 1
and tst.receiveaccountid = v_recaccountid
and tst.bankpaytype = 2
and tst.transferdetailstatus = 7
and ts.id = tst.transferid
and tst.paybankid = bsb.id
and bsb.status = 1
and ts.status = 1
and am.id = tst.payaccountid
and am.status = 1
and am.accountpurposeid=bsacc.id
and bsacc.status=1
and bs.id = tst.payorgid
and bs.id in (select bso.id
from bs_organization bso
where bso.status = 1
and bso.orglevelcode = v_orglevelcode)
and to_date(to_char(ts.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') >=
to_date(in_startdate, 'YYYY-MM-DD')
and to_date(to_char(ts.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <=
to_date(in_enddate, 'YYYY-MM-DD');
--有相同的收款方账户id且线上支付
cursor rec_uptransfer(v_recaccountno varchar2, v_orglevelcode varchar2) is
select bs.orgname orgname,
bpb.paybranchname bankname,
bpb.payaccountno accountno,
bsacc.purposename purposename,
tsd.amount amount,
bs.orglevelcode orglevelcode,
tsd.id transferdetid

from bp_bankinstrinfo bpb,
ts_transferdetail tsd,
ts_transfer ts,
bs_organization bs,
am_account am,
bs_accountpurpose bsacc
where bpb.recaccountno = v_recaccountno
and bpb.statusid = 4
and tsd.id = bpb.transferdetailid
and tsd.status = 1
and tsd.bankpaytype = 1
and tsd.transferdetailstatus = 4
and ts.id = tsd.transferid
and ts.status = 1
and am.accountno = bpb.payaccountno
and am.status=1
and am.accountpurposeid=bsacc.id
and bsacc.status=1
and bs.id = tsd.payorgid
and bs.id in (select bso.id
from bs_organization bso
where bso.status = 1
and bso.orglevelcode = v_orglevelcode)
and to_date(to_char(ts.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') >=
to_date(in_startdate, 'YYYY-MM-DD')
and to_date(to_char(ts.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <=
to_date(in_enddate, 'YYYY-MM-DD');

---内部付款中线上对线下有相同的收款方查询
cursor in_transferAll(v_tsdid number, v_transferid number, v_receiveid number,

v_orglevelcode varchar2) is
select bs.orgname orgname,
bsb.name bankname,
am.accountno accountno,
bsacc.purposename purposename,
tst.amount amount,
bs.orglevelcode orglevelcode,
tst.id transferdetid
from ts_transferdetail tst,
ts_transfer ts,
bs_organization bs,
am_account am,
bs_bank bsb,
bs_accountpurpose bsacc
where tst.status = 1
and tst.id = v_tsdid
and tst.receiveaccountid = v_receiveid
and ts.id = v_transferid
and tst.paybankid = bsb.id
and bsb.status = 1
and ts.status = 1
and am.id = tst.payaccountid
and am.status = 1
and am.accountpurposeid=bsacc.id
and bsacc.status=1
and bs.id = tst.payorgid
and bs.id in (select bso.id
from bs_organization bso
where bso.status = 1
and bso.orglevelcode = v_orglevelcode)
and to_date(to_char(ts.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') >=
to_date(in_startdate, 'YYYY-MM-DD')
and to_date(to_char(ts.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <=
to_date(in_enddate, 'YYYY-MM-DD')
union all
select bsa.orgname orgname,
bsba.name bankname,
ama.accountno accountno,
bsacco.purposename purposename,
tstt.amount amount,
bsa.orglevelcode orglevelcode,
tstt.id transferdetid
from ts_transferdetail tstt,
bp_bankinstrinfo bpbb,
ts_transfer tsa,
bs_organization bsa,
am_account ama,
bs_bank bsba,
bs_accountpurpose bsacco
where tstt.receiveaccountid = v_receiveid
and tstt.bankpaytype = 1
and tstt.status=1
and tstt.transferdetailstatus = 4
and bpbb.transferdetailid = tstt.id
and bpbb.statusid = 4
and bsa.id = tstt.payorgid
and ama.id = tstt.payaccountid
and ama.status=1
and ama.accountpurposeid=bsacco.id
and bsacco.status=1
and bsba.id = tstt.paybankid
and tsa.id = tstt.transferid
and bsa.id in (select bsoo.id
from bs_organization bsoo
where bsoo.status = 1
and bsoo.orglevelcode = v_orglevelcode)
and to_date(to_char(tsa.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') >=
to_date(in_startdate, 'YYYY-MM-DD')
and to_date(to_char(tsa.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <=
to_date(in_enddate, 'YYYY-MM-DD');

---对外部付款中线上对线下有相同的收款方查询
cursor out_transferAll(v_tsdid number, v_transferid number, v_receiveno varchar2,

v_orglevelcode varchar2) is
select bs.orgname orgname,
bsb.name bankname,
am.accountno accountno,
bsacc.purposename purposename,
tst.amount amount,
bs.orglevelcode orglevelcode,
tst.id transferdetid
from ts_transferdetail tst,
ts_transfer ts,
bs_organization bs,
am_account am,
bs_bank bsb,
bs_accountpurpose bsacc
where tst.status = 1
and tst.id = v_tsdid
and tst.extaccountno = v_receiveno
and ts.id = v_transferid
and tst.paybankid = bsb.id
and bsb.status = 1
and ts.status = 1
and am.id = tst.payaccountid
and am.status = 1
and am.accountpurposeid=bsacc.id
and bsacc.status=1
and bs.id = tst.payorgid
and bs.id in (select bso.id
from bs_organization bso
where bso.status = 1
and bso.orglevelcode = v_orglevelcode)
and to_date(to_char(ts.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') >=
to_date(in_startdate, 'YYYY-MM-DD')
and to_date(to_char(ts.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <=
to_date(in_enddate, 'YYYY-MM-DD')
union all
select bsa.orgname orgname,
bsba.name bankname,
ama.accountno accountno,
bsacco.purposename purposename,
tstt.amount amount,
bsa.orglevelcode orglevelcode,
tstt.id transferdetid
from ts_transferdetail tstt,
bp_bankinstrinfo bpbb,
ts_transfer tsa,
bs_organization bsa,
am_account ama,
bs_bank bsba,
bs_accountpurpose bsacco
where tstt.extaccountno = v_receiveno
and tstt.bankpaytype = 1
and tstt.status=1
and tstt.transferdetailstatus = 4
and bpbb.transferdetailid = tstt.id
and bpbb.statusid = 4
and bsa.id = tstt.payorgid
and ama.id = tstt.payaccountid
and ama.status=1
and ama.accountpurposeid=bsacco.id
and bsacco.status=1
and bsba.id = tstt.paybankid
and tsa.id = tstt.transferid
and bsa.id in (select bsoo.id
from bs_organization bsoo
where bsoo.status = 1
and bsoo.orglevelcode = v_orglevelcode)
and to_date(to_char(tsa.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') >=
to_date(in_startdate, 'YYYY-MM-DD')
and to_date(to_char(tsa.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <=
to_date(in_enddate, 'YYYY-MM-DD');
--------------------------------------------------------------------------
--定义查出有可疑账户的游标(线上支付)
CURSOR doubttransfer is
select bp.recaccountno, count(bp.recaccountno)
from bp_bankinstrinfo bp,
ts_transferdetail tsd,
ts_transfer ts,
bs_organization bsa
where bp.statusid = 4
and tsd.status=1
and ts.status=1
and bsa.status=1
and tsd.id = bp.transferdetailid
and ts.id = tsd.transferid
and tsd.bankpaytype = 1
and tsd.transferdetailstatus = 4
and bsa.id = tsd.payorgid
and bsa.id in (select bsoo.id
from bs_organization bsoo
where bsoo.status = 1
and bsoo.orglevelcode = vv_orglevelcode)
and to_date(to_char(ts.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') >=
to_date(in_startdate, 'YYYY-MM-DD')
and to_date(to_char(ts.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <=
to_date(in_enddate, 'YYYY-MM-DD')
group by bp.recaccountno;
--定义查出有可疑账户的游标(线下支付)
CURSOR doubttransfer_down is
select tst.receiveaccountid,
count(tst.receiveaccountid),
tst.extaccountno,
count(tst.extaccountno)
from ts_transferdetail tst, ts_transfer tsb, bs_organization bsb
where tst.status = 1
and tst.status=1
and tsb.status=1
and bsb.status=1
and tst.bankpaytype = 2
and tst.transferdetailstatus = 7
and tst.transferid = tsb.id
and bsb.id = tst.payorgid
and bsb.id in (select bsoo.id
from bs_organization bsoo
where bsoo.status = 1
and bsoo.orglevelcode = vv_orglevelcode)
and to_date(to_char(tsb.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') >=
to_date(in_startdate, 'YYYY-MM-DD')
and to_date(to_char(tsb.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <=
to_date(in_enddate, 'YYYY-MM-DD')
group by tst.receiveaccountid, tst.extaccountno;
--内部付款中线下对线上有相同的收款方游标
CURSOR in_transfer is
select tsd.id tsdid,
tsd.transferid transferid,
tsd.receiveaccountid receiveaccountid
from ts_transferdetail tsd, ts_transfer tsc, bs_organization bsc
where tsd.bankpaytype = 2
and tsd.status=1
and tsc.status=1
and bsc.status=1
and tsd.transferdetailstatus = 7
and tsc.id = tsd.transferid
and bsc.id = tsd.payorgid
and bsc.id in (select bsoo.id
from bs_organization bsoo
where bsoo.status = 1
and bsoo.orglevelcode = vv_orglevelcode)
and to_date(to_char(tsc.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') >=
to_date(in_startdate, 'YYYY-MM-DD')
and to_date(to_char(tsc.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <=
to_date(in_enddate, 'YYYY-MM-DD')
and tsd.receiveaccountid is not null
and tsd.receiveaccountid in
(select ts.receiveaccountid
from ts_transferdetail ts,
bp_bankinstrinfo bp,
ts_transfer tsh,
bs_organization bsh
where ts.bankpaytype = 1
and ts.status=1
and tsh.status=1
and bsh.status=1
and ts.transferdetailstatus = 4
and bp.transferdetailid = ts.id
and bp.statusid = 4
and tsh.id = ts.transferid
and bsh.id = ts.payorgid
and bsh.id in
(select bso.id
from bs_organization bso
where bso.status = 1
and bso.orglevelcode = vv_orglevelcode)
and to_date(to_char(tsh.transferdate, 'YYYY-MM-DD'),
'YYYY-MM-DD') >=
to_date(in_startdate, 'YYYY-MM-DD')
and to_date(to_char(tsh.transferdate, 'YYYY-MM-DD'),
'YYYY-MM-DD') <=
to_date(in_enddate, 'YYYY-MM-DD')
and ts.receiveaccountid is not null);
----对外部付款线下与线上有相同的收款方
CURSOR out_transfer is
select tsd.id tsdid,
tsd.transferid transferid,
tsd.extaccountno extaccountno
from ts_transferdetail tsd, ts_transfer tse, bs_organization bse
where tsd.bankpaytype = 2
and tsd.status=1
and tse.status=1
and bse.status=1
and tsd.transferdetailstatus = 7
and tse.id = tsd.transferid
and bse.id = tsd.payorgid
and bse.id in (select bsoo.id
from bs_organization bsoo
where bsoo.status = 1
and bsoo.orglevelcode = vv_orglevelcode)
and to_date(to_char(tse.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') >=
to_date(in_startdate, 'YYYY-MM-DD')
and to_date(to_char(tse.transferdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <=
to_date(in_enddate, 'YYYY-MM-DD')
and tsd.extaccountno is not null
and tsd.extaccountno in
(select ts.extaccountno
from ts_transferdetail ts,
bp_bankinstrinfo bp,
ts_transfer tsf,
bs_organization bsf
where ts.bankpaytype = 1
and ts.status=1
and tsf.status=1
and bsf.status=1
and ts.transferdetailstatus = 4
and bp.transferdetailid = ts.id
and bp.statusid = 4
and tsf.id = ts.transferid
and bsf.id = ts.payorgid
and bsf.id in
(select bso.id
from bs_organization bso
where bso.status = 1
and bso.orglevelcode = vv_orglevelcode)
and to_date(to_char(tsf.transferdate, 'YYYY-MM-DD'),
'YYYY-MM-DD') >=
to_date(in_startdate, 'YYYY-MM-DD')
and to_date(to_char(tsf.transferdate, 'YYYY-MM-DD'),
'YYYY-MM-DD') <=
to_date(in_enddate, 'YYYY-MM-DD')
and ts.extaccountno is not null);
--公司游标
CURSOR org(v_orglevelcode varchar2) is
select oo.orglevelcode orglevelcode_v2
from bs_organization oo
where
oo.orglevelcode like v_orglevelcode;
begin
--可疑原因
cause := '';
--处理是否包含子公司
select bss.orglevelcode
into orglevelcode
from (select bs.orglevelcode orglevelcode
from bs_organization bs
where bs.status = 1
and bs.orgcode = in_orgcode) bss;
if (includeSubOrg = 1) then
orglevelcode := orglevelcode || '%';
end if;
--删除临时表数据
delete from temp_rp_doubtfultransferdet;
commit;
--公司循环遍历-----------
for i in org(orglevelcode) loop
vv_orglevelcode := i.orglevelcode_v2;
----------------------
open doubttransfer;
--对线上支付可疑账户进行循环遍历
fetch doubttransfer
into receiveAcc;
while doubttransfer%found loop

temp_startdate := in_startdate;
temp_enddate := in_enddate;
c_orgCode := in_orgcode;
--如有相同的收款方账户id
if (receiveAcc.countrec > 1) then
--将数据保存进临时表
for curr_index in rec_uptransfer(receiveAcc.recaccountno,
vv_orglevelcode) loop

insert into temp_rp_doubtfultransferdet trd
(trd.orgname,
trd.bankname,
trd.accountno,
trd.accounttype,
trd.amount,
trd.cause,
trd.orglevelcode,
trd.transferdetid)
values
(curr_index.orgname,
curr_index.bankname,
curr_index.accountno,
curr_index.purposename,
curr_index.amount,
cause,
curr_index.orglevelcode,
curr_index.transferdetid);
end loop;
end if;
fetch doubttransfer
into receiveAcc;
end loop;
close doubttransfer;
----------------------------------------
open doubttransfer_down;
--对线下支付可疑账户进行循环遍历
fetch doubttransfer_down
into extaccount;
while doubttransfer_down%found loop
if (extaccount.countext > 1) then
for curr_index in rec_downtransfer1(extaccount.extaccountno,
vv_orglevelcode) loop
insert into temp_rp_doubtfultransferdet trd
(trd.orgname,
trd.bankname,
trd.accountno,
trd.accounttype,
trd.amount,
trd.cause,
trd.orglevelcode,
trd.transferdetid)
values
(curr_index.orgname,
curr_index.bankname,
curr_index.accountno,
curr_index.purposename,
curr_index.amount,
cause,
curr_index.orglevelcode,
curr_index.transferdetid);
end loop;
elsif (extaccount.countrec > 1) then
for curr_index in rec_downtransfer2(extaccount.recaccountid,
vv_orglevelcode) loop

insert into temp_rp_doubtfultransferdet trd
(trd.orgname,
trd.bankname,
trd.accountno,
trd.accounttype,
trd.amount,
trd.cause,
trd.orglevelcode,
trd.transferdetid)
values
(curr_index.orgname,
curr_index.bankname,
curr_index.accountno,
curr_index.purposename,
curr_index.amount,
cause,
curr_index.orglevelcode,
curr_index.transferdetid);
end loop;
end if;
fetch doubttransfer_down
into extaccount;
end loop;
close doubttransfer_down;
--------------------------------------------
open in_transfer;
--对内部付款中线上对线下有相同帐号遍历
fetch in_transfer
into in_account;
while in_transfer%found loop
for curr_index in in_transferAll(in_account.tsdid,
in_account.transferid,
in_account.receiveid,
vv_orglevelcode) loop

insert into temp_rp_doubtfultransferdet trd
(trd.orgname,
trd.bankname,
trd.accountno,
trd.accounttype,
trd.amount,
trd.cause,
trd.orglevelcode,
trd.transferdetid)
values
(curr_index.orgname,
curr_index.bankname,
curr_index.accountno,
curr_index.purposename,
curr_index.amount,
cause,
curr_index.orglevelcode,
curr_index.transferdetid);
end loop;
fetch in_transfer
into in_account;
end loop;
close in_transfer;
---------------------------------------------------------------
open out_transfer;
--对外部付款中线上对线下有相同帐号遍历
fetch out_transfer
into out_account;
while out_transfer%found loop
for curr_index in out_transferAll(out_account.tsdid,
out_account.transferid,
out_account.receiveno,
vv_orglevelcode) loop
insert into temp_rp_doubtfultransferdet trd
(trd.orgname,
trd.bankname,
trd.accountno,
trd.accounttype,
trd.amount,
trd.cause,
trd.orglevelcode,
trd.transferdetid)
values
(curr_index.orgname,
curr_index.bankname,
curr_index.accountno,
curr_index.purposename,
curr_index.amount,
cause,
curr_index.orglevelcode,
curr_index.transferdetid);
end loop;
fetch out_transfer
into out_account;
end loop;
close out_transfer;

end loop;
---------------------------------------------------------------
open ResultCursor for
select distinct *
from temp_rp_doubtfultransferdet trdd
order by trdd.orglevelcode, trdd.accountno;

end;