-主要收費表的自身數據平衡
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 
