-主要收费表的自身数据平衡
select (select count(*) from fin_opr_register t where (t.reg_fee + t.diag_fee + t.chck_fee + t.oth_fee) <> (t.own_cost + t.pub_cost + t.pay_cost + t.eco_cost)) 挂号表,
(select count(*) from fin_opb_invoiceinfo t where t.tot_cost <> (t.own_cost + t.pub_cost + t.pay_cost)) 门诊收费主表,
(select count(*) from fin_ipb_balancehead t where t.tot_cost <> (t.own_cost + t.pub_cost + t.pay_cost + t.eco_cost + t.der_cost)) 住院发票主表,
(select count(*) from fin_ipb_balancelist t where t.tot_cost <> (t.own_cost + t.pub_cost + t.pay_cost + t.eco_cost)) 住院发票明细表,
(select count(*) from fin_ipb_feeinfo t where t.tot_cost <> (t.own_cost + t.pay_cost + t.pub_cost + t.eco_cost)) 住院费用主表,
(select count(*) from fin_ipb_medicinelist t where t.tot_cost <> (t.own_cost + t.pay_cost + t.pub_cost + t.eco_cost)) 住院药品明细表,
(select count(*) from fin_ipb_itemlist t where t.tot_cost <> (t.own_cost + t.pay_cost + t.pub_cost + t.eco_cost)) 住院项目明细表
from dual;
--门诊发票主表、明细表、支付方式表、处方明细表中金额是否相等
select '发票主表',sum(t.tot_cost)
from fin_opb_invoiceinfo t
where t.oper_date >= to_date('2010-10-01','yyyy-mm-dd')
and t.oper_date < to_date('2010-10-15','yyyy-mm-dd') + 1
union all
select '发票明细',sum(t.own_cost + t.pub_cost + t.pay_cost)
from fin_opb_invoicedetail t
where t.oper_date >= to_date('2010-10-01','yyyy-mm-dd')
and t.oper_date < to_date('2010-10-15','yyyy-mm-dd') + 1
union all
select '支付方式表',sum(t.tot_cost)
from fin_opb_paymode t
where t.oper_date >= to_date('2010-10-01','yyyy-mm-dd')
and t.oper_date < to_date('2010-10-15','yyyy-mm-dd') + 1
union all
select '处方明细表',sum(t.own_cost + t.pub_cost + t.pay_cost)
from fin_opb_feedetail t
where t.fee_date >= to_date('2010-10-01','yyyy-mm-dd')
and t.fee_date < to_date('2010-10-15','yyyy-mm-dd') + 1;
--住院主表、费用汇总表、费用明细表金额是否相等
select sum(t.tot_cost + t.balance_cost) 住院主表,
sum((select sum(tot_cost) from fin_ipb_feeinfo where inpatient_no = t.inpatient_no)) 费用汇总表,
sum(((select sum(tot_cost) from fin_ipb_itemlist where inpatient_no = t.inpatient_no) + (select sum(tot_cost) from fin_ipb_medicinelist where inpatient_no = t.inpatient_no))) 费用明细表
from fin_ipr_inmaininfo t
where t.in_state in ('I','B');
--住院发票主表、明细表、费用汇总表中金额是否相等
select '发票主表',sum(t.tot_cost)
from fin_ipb_balancehead t
where t.balance_date >= to_date('2010-10-01','yyyy-mm-dd')
and t.balance_date < to_date('2010-10-15','yyyy-mm-dd') + 1
union all
select '发票明细',sum(t.tot_cost)
from fin_ipb_balancelist t
where t.balance_date >= to_date('2010-10-01','yyyy-mm-dd')
and t.balance_date < to_date('2010-10-15','yyyy-mm-dd') + 1
union all
select '费用汇总表',sum(t.tot_cost)
from fin_ipb_feeinfo t
where t.balance_date >= to_date('2010-10-01','yyyy-mm-dd')
and t.balance_date < to_date('2010-10-15','yyyy-mm-dd') + 1;
--挂号(上海中冶)
select '挂号',sum(t.own_cost) 现金,sum(t.pub_cost + t.pay_cost) 统筹
from fin_opr_register t
union all
select '医保',0,sum(t.pub_cost + t.append_cost + (t.curaccount_cost + t.lastaccount_cost + t.beginaccount_cost + t.pubaccount_cost + t.appendaccount_cost))
from sh_return_result t
where t.type in ('1','2','3','51','52','53');
--收费(上海中冶)
select '发票汇总' 分类,sum(t.tot_cost) 总金额,sum(t.own_cost) 现金额,sum(t.pub_cost + t.pay_cost) 统筹
from fin_opb_invoiceinfo t
union all
--收费明细
select '发票明细',sum(t.own_cost + t.pub_cost + t.pay_cost) tot_cost,0,0
from fin_opb_invoicedetail t
union all
--处方
select '处方明细',sum(t.own_cost + t.pub_cost + t.pay_cost) tot_cost,0,0
from fin_opb_feedetail t
where t.pay_flag = '1'
union all
--收费方式
select '收费方式',sum(t.tot_cost),sum(decode(t.mode_code,'CA',t.tot_cost,'CH',t.tot_cost,'CD',t.tot_cost,'DB',t.tot_cost,0)),sum(decode(t..mode_code,'CA',0,'CH',0,'CD',0,'DB',0,t.tot_cost))
from fin_opb_paymode t
union all
select '医保',0,0,sum(t.pub_cost + t.append_cost + (t.curaccount_cost + t.lastaccount_cost + t.beginaccount_cost + t.pubaccount_cost + t.appendaccount_cost))
from sh_return_result t
where t.type in ('4','5','6','7','54','55','56','57');
--住院发票汇总(上海中冶)
select '发票汇总' 分类,sum(t.tot_cost) 总金额,sum(t.own_cost) 自费,sum(t.supply_cost - t.return_cost) 实收金额,sum(t.pub_cost + t.pay_cost) 统筹,sum(t.prepay_cost) 预交金
from fin_ipb_balancehead t
union all
--住院发票明细
select '发票明细',sum(t.tot_cost),0,0,0,0
from fin_ipb_balancelist t
union all
--住院结算
select '收费方式',0,0,sum(decode(t.trans_kind,'1',decode(t.reutrnorsupply_flag,'2',-t.cost,t.cost),0)),0,sum(decode(t.trans_kind,'0',t.cost,0))
from fin_ipb_balancepay t
union all
select '医保',0,0,0,sum(t.pub_cost + t.append_cost + (t.curaccount_cost + t.lastaccount_cost + t.beginaccount_cost + t.pubaccount_cost + t.appendaccount_cost)),0
from sh_return_result t
where t.type in ('8','9','58','59'); |
|
资源大小：1.22 KB 
