create or replace procedure vat_invoice_run_tiso
is
report_end_date varchar2(20);
begin
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'YYYYMM')
INTO REPORT_END_DATE
FROM DUAL;
INSERT INTO VMS_TRANS_RUN
select cc.mapping_inst_id instId,
cc.voucherDate accountPeriod,
to_char(sum(cc.x),'fm9999999990.00'),
to_char(sum(cc.y),'fm9999999990.00'),
to_char(cast(nvl(sum(cc.y), 0) / (sum(cc.x)) as decimal(18, 12)),'fm9999999990.0000000') transferRatio
from (select distinct pp.mapping_inst_id,
aa.centerCode,
aa.voucherDate,
sum(aa.aafeeCny) as x,
sum(bb.bbfeeCny) as y
from (select centerCode, voucherDate, sum(feeCny) aafeeCny
from (select t.centercode centerCode,
to_char(to_date(t.voucherdate, 'yyyy-mm-dd'),
'YYYYMM') voucherDate,
nvl(k.planfeecny, t.sumplanfeecny) feeCny,
nvl(k.taxrate, t.taxrate) as taxRate
from VMS_INSURANCE_TRADE_INFO t
left join VMS_INSURANCE_KIND_INFO k
on k.trans_id = t.certiid
where t.hisflag = 'N'
and to_char(to_date(t.voucherdate, 'yyyy-mm-dd'),
'YYYYMM') = report_end_date
)
group by centerCode, voucherDate) aa
left join (select centerCode,
voucherDate,
sum(feeCny) as bbfeeCny,
taxRate
from (select t.centercode centerCode,
to_char(to_date(t.voucherdate,
'yyyy-mm-dd'),
'YYYYMM') voucherDate,
nvl(k.planfeecny, t.sumplanfeecny) feeCny,
nvl(k.taxrate, t.taxrate) as taxRate
from VMS_INSURANCE_TRADE_INFO t
left join VMS_INSURANCE_KIND_INFO k
on k.trans_id = t.certiid
where t.hisflag = 'N'
and to_char(to_date(t.voucherdate,
'yyyy-mm-dd'),
'YYYYMM') = report_end_date
)
where taxRate = 0
group by centerCode, voucherDate, taxRate) bb
on aa.centerCode = bb.centerCode
and aa.voucherDate = bb.voucherDate
left join u_base_mapping_inst pp
on pp.inst_id = aa.centerCode
group by pp.mapping_inst_id, aa.centerCode, aa.voucherDate
order by pp.mapping_inst_id) cc
group by cc.mapping_inst_id, cc.voucherDate
order by cc.mapping_inst_id;
COMMIT;
end vat_invoice_run_tiso;
上面这个存储过程大概意思就是说 建立一个每个月月初-1即上个月固定时间的 查询方法
is
report_end_date varchar2(20);
begin
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'YYYYMM')
INTO REPORT_END_DATE
FROM DUAL;
INSERT INTO VMS_TRANS_RUN
select cc.mapping_inst_id instId,
cc.voucherDate accountPeriod,
to_char(sum(cc.x),'fm9999999990.00'),
to_char(sum(cc.y),'fm9999999990.00'),
to_char(cast(nvl(sum(cc.y), 0) / (sum(cc.x)) as decimal(18, 12)),'fm9999999990.0000000') transferRatio
from (select distinct pp.mapping_inst_id,
aa.centerCode,
aa.voucherDate,
sum(aa.aafeeCny) as x,
sum(bb.bbfeeCny) as y
from (select centerCode, voucherDate, sum(feeCny) aafeeCny
from (select t.centercode centerCode,
to_char(to_date(t.voucherdate, 'yyyy-mm-dd'),
'YYYYMM') voucherDate,
nvl(k.planfeecny, t.sumplanfeecny) feeCny,
nvl(k.taxrate, t.taxrate) as taxRate
from VMS_INSURANCE_TRADE_INFO t
left join VMS_INSURANCE_KIND_INFO k
on k.trans_id = t.certiid
where t.hisflag = 'N'
and to_char(to_date(t.voucherdate, 'yyyy-mm-dd'),
'YYYYMM') = report_end_date
)
group by centerCode, voucherDate) aa
left join (select centerCode,
voucherDate,
sum(feeCny) as bbfeeCny,
taxRate
from (select t.centercode centerCode,
to_char(to_date(t.voucherdate,
'yyyy-mm-dd'),
'YYYYMM') voucherDate,
nvl(k.planfeecny, t.sumplanfeecny) feeCny,
nvl(k.taxrate, t.taxrate) as taxRate
from VMS_INSURANCE_TRADE_INFO t
left join VMS_INSURANCE_KIND_INFO k
on k.trans_id = t.certiid
where t.hisflag = 'N'
and to_char(to_date(t.voucherdate,
'yyyy-mm-dd'),
'YYYYMM') = report_end_date
)
where taxRate = 0
group by centerCode, voucherDate, taxRate) bb
on aa.centerCode = bb.centerCode
and aa.voucherDate = bb.voucherDate
left join u_base_mapping_inst pp
on pp.inst_id = aa.centerCode
group by pp.mapping_inst_id, aa.centerCode, aa.voucherDate
order by pp.mapping_inst_id) cc
group by cc.mapping_inst_id, cc.voucherDate
order by cc.mapping_inst_id;
COMMIT;
end vat_invoice_run_tiso;
上面这个存储过程大概意思就是说 建立一个每个月月初-1即上个月固定时间的 查询方法