create or replace procedure SP_MSS_AGING is
amount Number(18, 2);-- 未逾期
overdue1 Number(18, 2); --逾期0-6个月
overdue2 Number(18, 2); --逾期6-12月
overdue3 Number(18, 2); --逾期12-18个月
overdue4 Number(18, 2); --逾期18-24月
overdue5 Number(18, 2); --逾期以上两年
begin
declare
CURSOR companyRow IS
select bf01.scc_company_id,
bf01.SETTLE_TYPE_NO
from TB_MSS_SETT_CONTRACT bf01,
TB_MSS_SC_ACC_RECV_DET bf06,
TB_MSS_SETT_INVOICE bf02,
TB_SCC_WF_PROC_MAIN ac03,
TB_MSS_SC_ACC_RECV bt01
where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
and ac03.target_table_id = bf02.mss_sett_invoice_id
and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
and bf01.NOTE_STATE = 2
and ac03.STATUS = '4'
and not (bt01.AR_STATUS = '2')
and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') <=
to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
aging companyRow%rowtype;
begin
for aging in companyRow loop
begin
select sum(bt01.AR_AMOUNT)
into amount
from TB_MSS_SETT_CONTRACT bf01,
TB_MSS_SC_ACC_RECV_DET bf06,
TB_MSS_SETT_INVOICE bf02,
TB_SCC_WF_PROC_MAIN ac03,
TB_MSS_SC_ACC_RECV bt01
where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
and ac03.target_table_id = bf02.mss_sett_invoice_id
and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
and bt01.OVERDUED = '0'
and bf01.NOTE_STATE = 2
and ac03.STATUS = '4'
and not (bt01.AR_STATUS = '2')
and bf01.scc_company_id = aging.scc_company_id
and bf01.SETTLE_TYPE_NO = aging.SETTLE_TYPE_NO
and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') <=
to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
amount := 0;
end;
begin
select sum(bt01.AR_AMOUNT)
into overdue1
from TB_MSS_SETT_CONTRACT bf01,
TB_MSS_SC_ACC_RECV_DET bf06,
TB_MSS_SETT_INVOICE bf02,
TB_SCC_WF_PROC_MAIN ac03,
TB_MSS_SC_ACC_RECV bt01
where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
and ac03.target_table_id = bf02.mss_sett_invoice_id
and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
and bt01.OVERDUED = '1'
and bf01.NOTE_STATE = 2
and ac03.STATUS = '4'
and not (bt01.AR_STATUS = '2')
and bf01.scc_company_id = aging.scc_company_id
and bf01.SETTLE_TYPE_NO = aging.SETTLE_TYPE_NO
and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') <=
to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
and months_between(sysdate, ACC_RECV_DATE) <= 6
and months_between(sysdate, ACC_RECV_DATE) >= 0
group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
overdue1 := 0;
end;
begin
select sum(bt01.AR_AMOUNT)
into overdue2
from TB_MSS_SETT_CONTRACT bf01,
TB_MSS_SC_ACC_RECV_DET bf06,
TB_MSS_SETT_INVOICE bf02,
TB_SCC_WF_PROC_MAIN ac03,
TB_MSS_SC_ACC_RECV bt01
where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
and ac03.target_table_id = bf02.mss_sett_invoice_id
and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
and bt01.OVERDUED = '1'
and bf01.NOTE_STATE = 2
and ac03.STATUS = '4'
and not (bt01.AR_STATUS = '2')
and bf01.scc_company_id = aging.scc_company_id
and bf01.SETTLE_TYPE_NO = aging.SETTLE_TYPE_NO
and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') <=
to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
and months_between(sysdate, ACC_RECV_DATE) <= 12
and months_between(sysdate, ACC_RECV_DATE) >= 6
group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
overdue2 := 0;
end;
begin
select sum(bt01.AR_AMOUNT)
into overdue3
from TB_MSS_SETT_CONTRACT bf01,
TB_MSS_SC_ACC_RECV_DET bf06,
TB_MSS_SETT_INVOICE bf02,
TB_SCC_WF_PROC_MAIN ac03,
TB_MSS_SC_ACC_RECV bt01
where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
and ac03.target_table_id = bf02.mss_sett_invoice_id
and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
and bt01.OVERDUED = '1'
and bf01.NOTE_STATE = 2
and ac03.STATUS = '4'
and not (bt01.AR_STATUS = '2')
and bf01.scc_company_id = aging.scc_company_id
and bf01.SETTLE_TYPE_NO = aging.SETTLE_TYPE_NO
and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') <=
to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
and months_between(sysdate, ACC_RECV_DATE) <= 18
and months_between(sysdate, ACC_RECV_DATE) >= 12
group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
overdue3 := 0;
end;
begin
select sum(bt01.AR_AMOUNT)
into overdue4
from TB_MSS_SETT_CONTRACT bf01,
TB_MSS_SC_ACC_RECV_DET bf06,
TB_MSS_SETT_INVOICE bf02,
TB_SCC_WF_PROC_MAIN ac03,
TB_MSS_SC_ACC_RECV bt01
where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
and ac03.target_table_id = bf02.mss_sett_invoice_id
and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
and bt01.OVERDUED = '1'
and bf01.NOTE_STATE = 2
and ac03.STATUS = '4'
and not (bt01.AR_STATUS = '2')
and bf01.scc_company_id = aging.scc_company_id
and bf01.SETTLE_TYPE_NO = aging.SETTLE_TYPE_NO
and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') <=
to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
and months_between(sysdate, ACC_RECV_DATE) <= 24
and months_between(sysdate, ACC_RECV_DATE) >= 18
group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
overdue4 := 0;
end;
begin
select sum(bt01.AR_AMOUNT)
into overdue5
from TB_MSS_SETT_CONTRACT bf01,
TB_MSS_SC_ACC_RECV_DET bf06,
TB_MSS_SETT_INVOICE bf02,
TB_SCC_WF_PROC_MAIN ac03,
TB_MSS_SC_ACC_RECV bt01
where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
and ac03.target_table_id = bf02.mss_sett_invoice_id
and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
and bt01.OVERDUED = '1'
and bf01.NOTE_STATE = 2
and ac03.STATUS = '4'
and not (bt01.AR_STATUS = '2')
and bf01.scc_company_id = aging.scc_company_id
and bf01.SETTLE_TYPE_NO = aging.SETTLE_TYPE_NO
and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') <=
to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
and months_between(sysdate, ACC_RECV_DATE) >= 24
group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
overdue5 := 0;
end;
insert into TB_MSS_AGENCY_OD_AGING
(MSS_AGENCY_OD_AGING_ID,
SCC_COMPANY_ID,
YEAR_MONTH,
SETTLE_TYPE_NO,
UN_DUE_AMOUNT,
OD_6_MONTHS,
OD_12_MONTHS,
OD_18_MONTHS,
OD_24_MONTHS,
OD_2_YEARS,
OD_SUMMARY)
values
(sq_mss_agency_od_aging.NEXTVAL,
aging.scc_company_id,
to_char(add_months(trunc(sysdate), -1), 'yyyy-mm'),
aging.SETTLE_TYPE_NO,
amount,
overdue1,
overdue2,
overdue3,
overdue4,
overdue5,
overdue1 + overdue2 + overdue3 + overdue4 + overdue5);
end loop;
end;
end SP_MSS_AGING;
慢的要死 不清楚怎么优化
------解决思路----------------------
overdue1,overdue2,overdue3,overdue4,overdue5
这5个变量获取语句大致相同
可以使用sum(case when)的形式一次性取出
select sum(case when 变量1取值时专有的条件 then bt01.AR_AMOUNT end) overdue1,
……--下面同理overdue1
from
……--那几个表
where 几个变量取值时相同的条件