-主要收費(fèi)表的自身數(shù)據(jù)平衡
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)) 門診收費(fèi)主表,
(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)) 住院發(fā)票主表,
(select count(*) from fin_ipb_balancelist t where t.tot_cost <> (t.own_cost + t.pub_cost + t.pay_cost + t.eco_cost)) 住院發(fā)票明細(xì)表,
(select count(*) from fin_ipb_feeinfo t where t.tot_cost <> (t.own_cost + t.pay_cost + t.pub_cost + t.eco_cost)) 住院費(fèi)用主表,
(select count(*) from fin_ipb_medicinelist t where t.tot_cost <> (t.own_cost + t.pay_cost + t.pub_cost + t.eco_cost)) 住院藥品明細(xì)表,
(select count(*) from fin_ipb_itemlist t where t.tot_cost <> (t.own_cost + t.pay_cost + t.pub_cost + t.eco_cost)) 住院項(xiàng)目明細(xì)表
from dual;
--門診發(fā)票主表、明細(xì)表、支付方式表、處方明細(xì)表中金額是否相等
select '發(fā)票主表',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 '發(fā)票明細(xì)',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 '處方明細(xì)表',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;
--住院主表、費(fèi)用匯總表、費(fèi)用明細(xì)表金額是否相等
select sum(t.tot_cost + t.balance_cost) 住院主表,
sum((select sum(tot_cost) from fin_ipb_feeinfo where inpatient_no = t.inpatient_no)) 費(fèi)用匯總表,
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))) 費(fèi)用明細(xì)表
from fin_ipr_inmaininfo t
where t.in_state in ('I','B');
--住院發(fā)票主表、明細(xì)表、費(fèi)用匯總表中金額是否相等
select '發(fā)票主表',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 '發(fā)票明細(xì)',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 '費(fèi)用匯總表',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) 現(xiàn)金,sum(t.pub_cost + t.pay_cost) 統(tǒng)籌
from fin_opr_register t
union all
select '醫(yī)保',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');
--收費(fèi)(上海中冶)
select '發(fā)票匯總' 分類,sum(t.tot_cost) 總金額,sum(t.own_cost) 現(xiàn)金額,sum(t.pub_cost + t.pay_cost) 統(tǒng)籌
from fin_opb_invoiceinfo t
union all
--收費(fèi)明細(xì)
select '發(fā)票明細(xì)',sum(t.own_cost + t.pub_cost + t.pay_cost) tot_cost,0,0
from fin_opb_invoicedetail t
union all
--處方
select '處方明細(xì)',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
--收費(fèi)方式
select '收費(fèi)方式',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 '醫(yī)保',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');
--住院發(fā)票匯總(上海中冶)
select '發(fā)票匯總' 分類,sum(t.tot_cost) 總金額,sum(t.own_cost) 自費(fèi),sum(t.supply_cost - t.return_cost) 實(shí)收金額,sum(t.pub_cost + t.pay_cost) 統(tǒng)籌,sum(t.prepay_cost) 預(yù)交金
from fin_ipb_balancehead t
union all
--住院發(fā)票明細(xì)
select '發(fā)票明細(xì)',sum(t.tot_cost),0,0,0,0
from fin_ipb_balancelist t
union all
--住院結(jié)算
select '收費(fèi)方式',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 '醫(yī)保',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 
