oracle和db2存储过程语法的区别
1、oracle语法
--存储过程名称
CREATE OR REPLACE procedure PH_table
(
--定义传入的参数
cs1 VARCHAR2, --参数1
cs2 TIMESTAMP, --参数2
cs3 INT --参数3
)
AS
--如果要定义参数 这块写参数
v_cs1 INT;
v_cs2 VARCHAR2(20);
v_cs3 decimal(18,6);
----------------------------定义游标---------------------------------
CURSOR cur_1 IS select * From table
EchargeKind cur_1%rowtype;
BEGIN
--循环
FOR EchargeKind IN cur_1 LOOP
SELECT 字段 INTO v_cs1 FROM T_SYSTEM where 字段=cs1
if v_cs1 =0 then
v_cs2=’你好‘；
else
v_cs2=’你不好‘；
end if;
END PH_table;
CREATE OR REPLACE procedure PH_FinancingReport
(
OperID VARCHAR2, --操作员编号；如果操作员编号只有一个则统计单个操作员，有多个则统计多个操作员。
seitleoperatorId VARCHAR2, --结账操作员
StartTime TIMESTAMP, --上次结帐时间
EndTime TIMESTAMP, --本次结帐时间
gatheringId VARCHAR2, --报账单序号
ispreview INT,
ERR OUT NUMBER
)
AS
v_RegisterMark INT; --True(0)：统计挂号金额，诊金，工本费；False(1)不统计挂号
v_RegisterInvoiceType VARCHAR2(20); --挂号、门诊发票是否使用同一号段
v_ReckonAccountsMan VARCHAR2(16); --结帐人
v_Operator VARCHAR2(20); --操作员姓名
v_OperatorCodeNo VARCHAR2(10); --操作员代码
v_MangleSum int; --报损数量
--v_OverTypeSum int; --重打数量
v_ClinicOverTypeSum int; --门诊发票重打数量
v_RegOverTypeSum int; --挂号发票重打数量
--v_PrintNumSum int; --统一结算发票补打数量
v_ClinicPrintNumSum int; --门诊发票补打数量
v_RegPrintNumSum int; --挂号发票补打数量
--v_ReturnedVouchers int; --退票张数
v_ClinicReturnedVouchers int; --门诊退票张数
v_RegReturnedVouchers int; --挂号退票张数
v_PrintInvoiceConfig VARCHAR2(20); --门诊退费是否打印负票
--v_RVMoney decimal(18,6); --退票应收金额
--v_RVSELFMoney decimal(18,6); --退票实收金额
v_ClinicRVMoney decimal(18,6); --门诊退票应收金额
v_ClinicRVSELFMoney decimal(18,6); --门诊退票实收金额
v_RegRVMoney decimal(18,6); --挂号退票应收金额
v_RegRVSELFMoney decimal(18,6); --挂号退票实收金额
--v_InvalidatedTicket int; --废票张数
v_ClinicInvalidatedTicket int; --门诊发票废票张数
v_RegInvalidatedTicket int; --挂号发票废票张数
--v_ITMoney decimal(18,6); --废票应收金额
--v_ITSELFMoney decimal(18,6); --废票实收金额
v_ClinicITMoney decimal(18,6); --门诊废票应收金额
v_ClinicITSELFMoney decimal(18,6); --门诊废票实收金额
v_RegITMoney decimal(18,6); --挂号废票应收金额
v_RegITSELFMoney decimal(18,6); --挂号废票实收金额
v_BeginInvoice VARCHAR2(30); --起始发票号
v_EndInvoice VARCHAR2(30); --截至发票号
v_ChargeBeginInvoice VARCHAR2(30); --收费起始号
v_ChargeEndInvoice VARCHAR2(30); --收费终止号
v_RegBeginInvoice VARCHAR2(30); --挂号起始号
v_RegEndInvoice VARCHAR2(30); --挂号终止号
v_ChargeInvoiceSum int; --收费发票数
v_RegInvoiceSum int; --挂号发票数
v_InvoiceSum int; --发票总数
v_InvoiceArea varchar2(8000); --发票号段
v_TempMinInvoiceID varchar2(30); --临时变量，发票号段最小发票号
v_TempMaxInvoiceID varchar2(30); --临时变量，发票号段最大发票号
v_RegisterFee decimal(18,6); --挂号金额
v_ExamineMoney decimal(18,6); --诊金
v_CaseCost decimal(18,6); --工本费
v_BespeakMoney decimal(18,6); --挂号预约费
v_CHECKFEE decimal(18,6); --挂号检查费
v_GHTC decimal(18,6); --挂号统筹金额
v_GHZH decimal(18,6); --挂号账户金额
v_RegisterFeeRefund decimal(18,6);
v_ExamineMoneyRefund decimal(18,6);
v_CaseCostRefund decimal(18,6);
v_BespeakMoneyRefund decimal(18,6);
v_CHECKFEERefund decimal(18,6);
v_GHTCRefund decimal(18,6); --挂号退统筹
v_GHZHRefund decimal(18,6); --挂号退账户
v_SumMoney decimal(18,6); --应收合计金额
v_ReturnMoney decimal(18,6); --应收退费金额
v_SumSelfMoney decimal(18,6); --实收合计金额
v_ReturnSelfMoney decimal(18,6); --实收退费金额
v_HangMoney decimal(18,6); --挂帐金额
v_ReportItemMoney varchar2(10000); --报表项目中的金额合计
v_RealIncomeSum decimal(18,6); --实收合计
v_ReceivablesSum decimal(18,6); --应收合计
v_HangAccountSum decimal(18,6); --挂帐合计
v_BankCardMoney decimal(18,6); --银医直联卡收费金额
v_BankCardTypeMoneystr varchar2(200); --银医直联卡金额
v_Recharge decimal(18,6); --门诊预交金收款金额
v_RechargeReturn decimal(18,6); --门诊预交金退款金额
v_RechargeInhos decimal(18,6); --住院转门诊预交金
v_RechargeReturnInhos decimal(18,6); --门诊转住院预交金
v_BalanceReturnMoney decimal(18,6); --已结算退费金额
v_Cash decimal(18,6); --实收现金
v_YL decimal(18,6); --银联卡
v_YJJ decimal(18,6); --预交金
v_ZP decimal(18,6); --支票
v_TCZF decimal(18,6); --统筹支付
v_ZHZF decimal(18,6); --账户支付
v_NoPrintYJJ decimal(18,6); --不打票预交金
v_strChargeKindMoney varchar2(9000); --费用类别金额
v_ChargeKindTCZF decimal(18,6); --费用类别统筹
v_ChargeKindZHZF decimal(18,6); --费用类别账户
v_ChargeKindXJ decimal(18,6); --费用类别现金
v_ChargeKindYLK decimal(18,6); --费用类别银联卡
v_ChargeKindYJJ decimal(18,6); --费用类别预交金
v_ChargeKindZP decimal(18,6); --费用类别支票
v_ReturnSumMoney decimal(18,6); --应收退费合计金额
v_ReturnSumSelfMoney decimal(18,6); --实收退费合计金额
v_POSReceivables decimal(18,6); --POS应收金额
v_POSRealIncome decimal(18,6); --POS实收金额
v_POSHangAccount decimal(18,6); --POS挂帐金额
v_OptionFeeReceivables decimal(18,6); --附加费应收金额
v_OptionFeeRealIncome decimal(18,6); --附加费实收金额
v_OptionFeeHangAccount decimal(18,6); --附加费挂帐金额
v_NoPrintMoney decimal(18,6); --不打票应收(实际不收取金额部分)
v_NoPrintSelfMoney decimal(18,6); --不打票实收(实际不收取金额部分)
v_MXBTC decimal(18,6); --慢性病统筹
v_TCGZ decimal(18,6); --统筹挂账金额
v_YHDZ decimal(18,6); --优惠打折金额
v_CardMoney decimal(18,6); --卡金额
v_BalanceModeMoney varchar2(20000); --结算方式金额
v_BMsumMoney decimal(18,6); --结算方式应收
v_BMselfMoney decimal(18,6); --结算方式实收
v_BMhangMoney decimal(18,6); --结算方式挂账
v_gatheringId VARCHAR2(30); --报账单序号
-----定义游标----------------------------------------------------------
CURSOR cur_1 IS --费用类别
select * From S_CHARGE_KIND
where (S_CHARGE_KIND.ISNOTMEDINSURANCE = '是' or S_CHARGE_KIND.ISNOTMEDINSURANCE = '不打票' OR ISNOTMEDINSURANCE = '院内医保')
and S_CHARGE_KIND.USECONFINE <> '住院' AND S_CHARGE_KIND.STOPFLAG = 0;
EchargeKind cur_1%rowtype;
CURSOR cur_2 IS --卡类型
select * from T_CARD_TYPES where ISBANKCARD = 1 order by TYPE_NO;
CardTypes cur_2%rowtype;
CURSOR cur_3 IS
SELECT StartNumber,EndNumber FROM T_OPERATOR_INVOICE WHERE OperatorID=OperID AND InvoiceType='挂号发票' ORDER BY StartNumber ASC;
InvoiceArea cur_3%rowtype;
CURSOR cur_4 IS
SELECT StartNumber,EndNumber FROM T_OPERATOR_INVOICE WHERE OperatorID=OperID AND InvoiceType='门诊发票' ORDER BY StartNumber ASC;
InvoiceAreas cur_4%rowtype;
CURSOR cur_5 IS--获取收费项目中的发票项目
--SELECT Distinct ReportItem FROM d_summary_info order by ReportItem;
SELECT Distinct INVOICEITEM ReportItem FROM T_CLINICINVOICEITEM order by INVOICEITEM;
ReportItem cur_5%rowtype;
CURSOR cur_6 IS
SELECT Distinct BALANCEMODE FROM D_BALANCE_MODE order by BALANCEMODE;
BalanceMode cur_6%rowtype;
BEGIN
v_gatheringId:=gatheringId ;
INSERT INTO SESSION_OPERATORMZJZ(GATHERINGID,STARTDATE,ENDDATE,OPERATORID,OPERATEDATE) VALUES(gatheringId,STARTTIME,EndTime,OperID,sysdate);
FOR EchargeKind IN cur_1 LOOP
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,EchargeKind.CHARGEKIND,OperID,0);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,EchargeKind.CHARGEKIND || '统筹支付',OperID,0);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,EchargeKind.CHARGEKIND || '账户支付',OperID,0);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,EchargeKind.CHARGEKIND || '现金',OperID,0);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,EchargeKind.CHARGEKIND || '银联卡',OperID,0);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,EchargeKind.CHARGEKIND || '预交金',OperID,0);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,EchargeKind.CHARGEKIND || '支票',OperID,0);
end loop;
FOR CardTypes IN cur_2 LOOP
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,CardTypes.TYPE_NAME,OperID,0);
end loop;
FOR ReportItem IN cur_5 LOOP
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,ReportItem.ReportItem||'应收',OperID,0);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,ReportItem.ReportItem||'应收退费',OperID,0);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,ReportItem.ReportItem||'实收',OperID,0);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,ReportItem.ReportItem||'实收退费',OperID,0);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,ReportItem.ReportItem||'挂账',OperID,0);
end loop;
FOR BalanceMode IN cur_6 LOOP
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,BalanceMode.BALANCEMODE||'应收',OperID,0);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,BalanceMode.BALANCEMODE||'实收',OperID,0);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,OPERATORID,INFACTMONEY)
VALUES (gatheringId,BalanceMode.BALANCEMODE||'挂账',OperID,0);
end loop;
--读取是否统计挂号费
SELECT defaultValue INTO v_RegisterMark FROM T_SYSTEM_CONFIG where setcontent='门诊交款报表是否统计挂号费用';
--读取发票是否使用同一号段配置
SELECT defaultValue INTO v_RegisterInvoiceType FROM T_SYSTEM_CONFIG where setcontent='挂号、门诊发票是否使用同一号段';
If v_RegisterInvoiceType = '0' Then
v_RegisterInvoiceType := '门诊发票';
else
v_RegisterInvoiceType := '挂号发票';
end if;
--获得报损数量
SELECT COALESCE(COUNT(t_InvoiceUseDetail.OldInvoice),0) INTO v_MangleSum FROM t_InvoiceUseDetail WHERE OperatorID=OperID AND InvoiceType='门诊发票' AND InvoiceState=1 AND OperateDate >= STARTTIME AND OperateDate < ENDTIME;
UPDATE SESSION_OPERATORMZJZ SET REJECTCOUNT=v_MangleSum WHERE OPERATORID=OperID;
--获取门诊发票重打数量
SELECT COALESCE(COUNT(t_InvoiceUseDetail.OldInvoice),0) INTO v_ClinicOverTypeSum FROM t_InvoiceUseDetail WHERE OperatorID=OperID AND InvoiceType='门诊发票' AND InvoiceState=0 AND OperateDate >= STARTTIME AND OperateDate < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'门诊发票重打数量',v_ClinicOverTypeSum,v_ClinicOverTypeSum,OperID);
--获取挂号发票重打数量
SELECT COALESCE(COUNT(CurrentInvoice),0) INTO v_RegOverTypeSum FROM t_InvoiceUseDetail WHERE OperatorID=OperID AND InvoiceType='挂号发票' AND InvoiceState=0 AND OperateDate >= STARTTIME AND OperateDate < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号发票重打数量',v_RegOverTypeSum,v_RegOverTypeSum,OperID);
--获取全部重打数量
UPDATE SESSION_OPERATORMZJZ SET REPRINTCOUNT=v_ClinicOverTypeSum+v_RegOverTypeSum WHERE OPERATORID=OperID;
--获取门诊发票补打数量
SELECT COALESCE(COUNT(OldInvoice),0) INTO v_ClinicPrintNumSum FROM t_InvoiceUseDetail WHERE OperatorID=OperID AND InvoiceType='门诊发票' AND InvoiceState=4 AND OperateDate >= STARTTIME AND OperateDate < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'门诊发票补打数量',v_ClinicPrintNumSum,v_ClinicPrintNumSum,OperID);
--获取挂号发票补打数量
SELECT COALESCE(COUNT(CurrentInvoice),0) INTO v_RegPrintNumSum FROM t_InvoiceUseDetail WHERE OperatorID=OperID AND InvoiceType='挂号发票' AND InvoiceState=4 AND OperateDate >= STARTTIME AND OperateDate < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号发票补打数量',v_RegPrintNumSum,v_RegPrintNumSum,OperID);
--获取全部统一结算发票补打数量
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'发票补打数量',v_ClinicPrintNumSum+v_RegPrintNumSum,v_ClinicPrintNumSum+v_RegPrintNumSum,OperID);
--获取门诊发票退票张数
SELECT COALESCE(COUNT(Distinct OldInvoice),0) INTO v_ClinicReturnedVouchers FROM t_InvoiceUseDetail WHERE OperatorID=OperID AND InvoiceType='门诊发票' AND InvoiceState=2 AND OperateDate >= STARTTIME AND OperateDate < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'门诊发票退票张数',v_ClinicReturnedVouchers,v_ClinicReturnedVouchers,OperID);
--获取挂号发票退票张数
SELECT COALESCE(COUNT(Distinct OldInvoice),0) INTO v_RegReturnedVouchers FROM t_InvoiceUseDetail B WHERE b.operatorid=OperID AND InvoiceType='挂号发票' AND InvoiceState=2 AND OperateDate >= STARTTIME AND OperateDate < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号发票退票张数',v_RegReturnedVouchers,v_RegReturnedVouchers,OperID);
--获取全部退票张数
UPDATE SESSION_OPERATORMZJZ SET RETURNCOUNT=v_ClinicReturnedVouchers+v_RegReturnedVouchers WHERE OPERATORID=OperID;
SELECT defaultValue INTO v_PrintInvoiceConfig FROM T_SYSTEM_CONFIG where setcontent='门诊退费是否打印负票';
--获取门诊退票应收金额,获取门诊退票实收金额
if v_PrintInvoiceConfig = '0' then
SELECT COALESCE(SUM(Money),0) * -1,COALESCE(SUM(SelfMoney),0) * -1 into v_ClinicRVMoney,v_ClinicRVSELFMoney FROM (SELECT d_Detail_Account.Money,d_Detail_Account.SelfMoney FROM t_InvoiceUseDetail,d_Detail_Account WHERE t_InvoiceUseDetail.OperatorID=OperID AND InvoiceState=2 AND InvoiceType='门诊发票' AND CancelMark=2 AND t_InvoiceUseDetail.OperateDate >= STARTTIME AND t_InvoiceUseDetail.OperateDate < ENDTIME AND t_InvoiceUseDetail.OldInvoice=d_Detail_Account.InvoiceID)T;
else
SELECT COALESCE(SUM(Money),0) * -1,COALESCE(SUM(SelfMoney),0) * -1 into v_ClinicRVMoney,v_ClinicRVSELFMoney FROM (SELECT d_Detail_Account.Money * -1 as Money,d_Detail_Account.SelfMoney * -1 as SelfMoney FROM t_InvoiceUseDetail,d_Detail_Account WHERE t_InvoiceUseDetail.OperatorID=OperID AND InvoiceState=2 AND InvoiceType='门诊发票' AND t_InvoiceUseDetail.OperateDate >= STARTTIME AND t_InvoiceUseDetail.OperateDate < ENDTIME AND t_InvoiceUseDetail.OldInvoice=d_Detail_Account.InvoiceID)T;
end if;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'门诊退票应退金额',v_ClinicRVMoney,v_ClinicRVMoney,OperID);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'门诊退票实退金额',v_ClinicRVSELFMoney,v_ClinicRVSELFMoney,OperID);
--获取挂号退票应收金额,获取挂号退票实收金额
if v_PrintInvoiceConfig = '0' then
SELECT COALESCE(SUM(Money),0) * -1,COALESCE(SUM(SelfMoney),0) * -1 into v_RegRVMoney,v_RegRVSELFMoney FROM (SELECT COALESCE(SUM(RegisterFee + ExamineMoney + CaseCost + CHECKFEE + BESPEAKMONEY),0) AS MONEY,COALESCE(SUM( RegisterFee + ExamineMoney + CaseCost + CHECKFEE + BESPEAKMONEY-DISCOUNTDEFRAY-ACCOUNTDEFRAY),0) AS SelfMoney FROM t_Register_Info A, t_InvoiceUseDetail B WHERE A.InvoiceID=B.OldInvoice AND CancelMark=2 AND b.operatorid=OperID AND InvoiceType=v_RegisterInvoiceType AND InvoiceState=2 AND B.OperateDate >= STARTTIME AND B.OperateDate < ENDTIME)T;
else
SELECT COALESCE(SUM(Money),0) * -1,COALESCE(SUM(SelfMoney),0) * -1 into v_RegRVMoney,v_RegRVSELFMoney FROM (SELECT COALESCE(SUM(RegisterFee + ExamineMoney + CaseCost + CHECKFEE + BESPEAKMONEY),0) AS MONEY,COALESCE(SUM( RegisterFee + ExamineMoney + CaseCost + CHECKFEE + BESPEAKMONEY-DISCOUNTDEFRAY-ACCOUNTDEFRAY),0) AS SelfMoney FROM t_Register_Info A, t_InvoiceUseDetail B WHERE A.InvoiceID=B.OldInvoice AND CancelMark=2 AND b.operatorid=OperID AND InvoiceType=v_RegisterInvoiceType AND InvoiceState=2 AND B.OperateDate >= STARTTIME AND B.OperateDate < ENDTIME)T;
end if;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号退票应退金额',v_RegRVMoney,v_RegRVMoney,OperID);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号退票实退金额',v_RegRVSELFMoney,v_RegRVSELFMoney,OperID);
--获取全部退票应收金额,获取全部退票实收金额
UPDATE SESSION_OPERATORMZJZ SET RETURNPAYMENT=v_ClinicRVMoney+v_RegRVMoney,RETURNMONEY=v_ClinicRVSELFMoney+v_RegRVSELFMoney WHERE OPERATORID=OperID;
--获取门诊发票废票张数
SELECT COALESCE(COUNT(Distinct OldInvoice),0) INTO v_ClinicInvalidatedTicket FROM t_InvoiceUseDetail WHERE OperatorID=OperID AND InvoiceType='门诊发票' AND InvoiceState=3 AND OperateDate >= STARTTIME AND OperateDate < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'门诊发票废票张数',v_ClinicInvalidatedTicket,v_ClinicInvalidatedTicket,OperID);
--获取挂号发票废票张数
SELECT COALESCE(COUNT(Distinct OldInvoice),0) INTO v_RegInvalidatedTicket FROM t_InvoiceUseDetail B WHERE b.operatorid=OperID AND InvoiceType='挂号发票' AND InvoiceState=3 AND OperateDate >= STARTTIME AND OperateDate < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号发票废票张数',v_RegInvalidatedTicket,v_RegInvalidatedTicket,OperID);
--获取全部废票张数
UPDATE SESSION_OPERATORMZJZ SET NULLITYCOUNT=v_ClinicInvalidatedTicket+v_RegInvalidatedTicket WHERE OPERATORID=OperID;
--获取门诊废票应收金额,获取门诊废票实收金额
if v_PrintInvoiceConfig = '0' then
SELECT COALESCE(SUM(Money),0) * -1, COALESCE(SUM(SelfMoney),0) * -1 into v_ClinicITMoney,v_ClinicITSELFMoney FROM (SELECT d_Detail_Account.Money,d_Detail_Account.SelfMoney FROM t_InvoiceUseDetail,d_Detail_Account WHERE t_InvoiceUseDetail.OperatorID=OperID AND InvoiceState=3 AND t_InvoiceUseDetail.OperateDate >= STARTTIME AND t_InvoiceUseDetail.OperateDate < ENDTIME AND t_InvoiceUseDetail.OldInvoice=d_Detail_Account.InvoiceID AND InvoiceType='门诊发票' AND CancelMark=2)T;
else
SELECT COALESCE(SUM(Money),0) * -1, COALESCE(SUM(SelfMoney),0) * -1 into v_ClinicITMoney,v_ClinicITSELFMoney FROM (SELECT d_Detail_Account.Money * -1 as Money,d_Detail_Account.SelfMoney * -1 as SelfMoney FROM t_InvoiceUseDetail,d_Detail_Account WHERE t_InvoiceUseDetail.OperatorID=OperID AND InvoiceState=3 AND t_InvoiceUseDetail.OperateDate >= STARTTIME AND t_InvoiceUseDetail.OperateDate < ENDTIME AND t_InvoiceUseDetail.OldInvoice=d_Detail_Account.InvoiceID AND InvoiceType='门诊发票')T;
end if;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'门诊废票应废金额',v_ClinicITMoney,v_ClinicITMoney,OperID);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'门诊废票实废金额',v_ClinicITSELFMoney,v_ClinicITSELFMoney,OperID);
--获取挂号废票应收金额,获取挂号废票实收金额
if v_PrintInvoiceConfig = '0' then
SELECT COALESCE(SUM(Money),0) * -1, COALESCE(SUM(SelfMoney),0) * -1 into v_RegITMoney,v_RegITSELFMoney FROM (SELECT COALESCE(SUM(RegisterFee + ExamineMoney + CaseCost + CHECKFEE + BESPEAKMONEY),0) AS MONEY,COALESCE(SUM(RegisterFee + ExamineMoney + CaseCost + CHECKFEE + BESPEAKMONEY-DISCOUNTDEFRAY-ACCOUNTDEFRAY),0) AS SelfMoney FROM t_Register_Info A, t_InvoiceUseDetail B WHERE A.InvoiceID=B.OldInvoice AND CancelMark=2 AND b.operatorid=OperID AND InvoiceType=v_RegisterInvoiceType AND InvoiceState=3 AND B.OperateDate >= STARTTIME AND B.OperateDate < ENDTIME)T;
else
SELECT COALESCE(SUM(Money),0) * -1, COALESCE(SUM(SelfMoney),0) * -1 into v_RegITMoney,v_RegITSELFMoney FROM (SELECT COALESCE(SUM(RegisterFee + ExamineMoney + CaseCost + CHECKFEE + BESPEAKMONEY),0) AS MONEY,COALESCE(SUM(RegisterFee + ExamineMoney + CaseCost + CHECKFEE + BESPEAKMONEY-DISCOUNTDEFRAY-ACCOUNTDEFRAY),0) AS SelfMoney FROM t_Register_Info A, t_InvoiceUseDetail B WHERE A.InvoiceID=B.OldInvoice AND CancelMark=2 AND b.operatorid=OperID AND InvoiceType=v_RegisterInvoiceType AND InvoiceState=3 AND B.OperateDate >= STARTTIME AND B.OperateDate < ENDTIME)T;
end if;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号废票应废金额',v_RegITMoney,v_RegITMoney,OperID);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号废票实废金额',v_RegITMoney,v_RegITMoney,OperID);
--获取全部废票应收金额,获取全部废票实收金额
UPDATE SESSION_OPERATORMZJZ SET NULLITYPAYMENT=v_ClinicITMoney+v_RegITMoney,NULLITYMONEY=v_ClinicITSELFMoney+v_RegITSELFMoney WHERE OPERATORID=OperID;
--获取起始发票号,获取截止发票号
if v_PrintInvoiceConfig = '0' then
SELECT COALESCE(Min(Distinct INVOICEID),' '),COALESCE(Max(Distinct INVOICEID),' ') into v_BeginInvoice,v_EndInvoice FROM (
SELECT a.InvoiceID FROM d_Detail_Account a inner join S_CHARGE_KIND b on a.SICKTYPEID=b.CHARGEKINDID WHERE b.ISNOTMEDINSURANCE<>'不打票' and a.INVOICEOPERATOR=OperID AND (a.INVOICEDATE >= STARTTIME AND a.INVOICEDATE < ENDTIME) and BALANCESTATE <> 0 and CANCELMARK <> 2
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState=1 AND OperatorID=OperID
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState =0 AND OperatorID=OperID
UNION ALL
SELECT InvoiceID AS INVOICEID FROM t_Register_Info WHERE INVOICEOPERATOR=OperID AND INVOICEDATE >= STARTTIME AND INVOICEDATE < ENDTIME and BALANCESTATE <> 0 and CANCELMARK <> 2
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND InvoiceState =0 AND OperatorID=OperID
)T where InvoiceID<>'0000000000' and InvoiceID not like 'y%';
SELECT COALESCE(Min(Distinct INVOICEID),' '),COALESCE(Max(Distinct INVOICEID),' ') into v_ChargeBeginInvoice,v_ChargeEndInvoice FROM (
SELECT a.InvoiceID FROM d_Detail_Account a inner join S_CHARGE_KIND b on a.SICKTYPEID=b.CHARGEKINDID WHERE b.ISNOTMEDINSURANCE<>'不打票' and a.INVOICEOPERATOR=OperID AND a.INVOICEDATE >= STARTTIME AND a.INVOICEDATE < ENDTIME and BALANCESTATE <> 0 and CANCELMARK <> 2
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState=1 AND OperatorID=OperID
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState =0 AND OperatorID=OperID
)T where InvoiceID<>'0000000000' and InvoiceID not like 'y%';
SELECT COALESCE(Min(Distinct INVOICEID),' '),COALESCE(Max(Distinct INVOICEID),' ') into v_RegBeginInvoice,v_RegEndInvoice FROM (
SELECT A.InvoiceID AS INVOICEID FROM t_Register_Info A WHERE A.INVOICEOPERATOR=OperID AND INVOICEDATE >= STARTTIME AND INVOICEDATE < ENDTIME and BALANCESTATE <> 0 and CANCELMARK <> 2
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND InvoiceState =0 AND OperatorID=OperID
)T where InvoiceID<>'0000000000' and InvoiceID not like 'y%';
else
SELECT COALESCE(Min(Distinct INVOICEID),' '),COALESCE(Max(Distinct INVOICEID),' ') into v_BeginInvoice,v_EndInvoice FROM (
SELECT a.InvoiceID FROM d_Detail_Account a inner join S_CHARGE_KIND b on a.SICKTYPEID=b.CHARGEKINDID WHERE b.ISNOTMEDINSURANCE<>'不打票' and a.INVOICEOPERATOR=OperID AND (a.INVOICEDATE >= STARTTIME AND a.INVOICEDATE < ENDTIME) and BALANCESTATE <> 0
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState=1 AND OperatorID=OperID
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState =0 AND OperatorID=OperID
UNION ALL
SELECT A.InvoiceID AS INVOICEID FROM t_Register_Info A WHERE A.INVOICEOPERATOR=OperID AND INVOICEDATE >= STARTTIME AND INVOICEDATE < ENDTIME and BALANCESTATE <> 0 and CANCELMARK <> 2
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND InvoiceState =0 AND OperatorID=OperID
)T where InvoiceID<>'0000000000' and InvoiceID not like 'y%';
SELECT COALESCE(Min(Distinct INVOICEID),' '),COALESCE(Max(Distinct INVOICEID),' ') into v_ChargeBeginInvoice,v_ChargeEndInvoice FROM (
SELECT a.InvoiceID FROM d_Detail_Account a inner join S_CHARGE_KIND b on a.SICKTYPEID=b.CHARGEKINDID WHERE b.ISNOTMEDINSURANCE<>'不打票' and a.INVOICEOPERATOR=OperID AND (a.INVOICEDATE >= STARTTIME AND a.INVOICEDATE < ENDTIME) and BALANCESTATE <> 0
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState=1 AND OperatorID=OperID
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState =0 AND OperatorID=OperID
)T where InvoiceID<>'0000000000' and InvoiceID not like 'y%';
SELECT COALESCE(Min(Distinct INVOICEID),' '),COALESCE(Max(Distinct INVOICEID),' ') into v_RegBeginInvoice,v_RegEndInvoice FROM (
SELECT A.InvoiceID AS INVOICEID FROM t_Register_Info A WHERE A.INVOICEOPERATOR=OperID AND INVOICEDATE >= STARTTIME AND INVOICEDATE < ENDTIME and BALANCESTATE <> 0 and CANCELMARK <> 2
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND InvoiceState =0 AND OperatorID=OperID
)T where InvoiceID<>'0000000000' and InvoiceID not like 'y%';
End If;
UPDATE SESSION_OPERATORMZJZ SET STARTINVOICE=v_BeginInvoice,ENDINVOICE=v_EndInvoice,CHARGEBEGININVOICE=v_ChargeBeginInvoice,CHARGEENDINVOICE=v_ChargeEndInvoice,REGBEGININVOICE=v_RegBeginInvoice,REGENDINVOICE=v_RegEndInvoice WHERE OPERATORID=OperID;
--获取发票总数
if v_PrintInvoiceConfig = '0' then
SELECT COALESCE(COUNT(distinct invoiceid),0) INTO v_InvoiceSum FROM (
SELECT a.InvoiceID FROM d_Detail_Account a inner join S_CHARGE_KIND b on a.SICKTYPEID=b.CHARGEKINDID WHERE b.ISNOTMEDINSURANCE<>'不打票' and a.INVOICEOPERATOR=OperID AND a.INVOICEDATE >= STARTTIME AND a.INVOICEDATE < ENDTIME and BALANCESTATE <> 0 AND CANCELMARK <> 2
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND (INVOICESTATE=1 or INVOICESTATE=3) AND OperatorID=OperID
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState =0 AND OperatorID=OperID
UNION ALL
SELECT InvoiceID AS INVOICEID FROM t_Register_Info WHERE INVOICEOPERATOR=OperID AND INVOICEDATE >= STARTTIME AND INVOICEDATE < ENDTIME and BALANCESTATE <> 0
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND InvoiceState =0 AND OperatorID=OperID
)T where InvoiceID<>'0000000000' and InvoiceID not like 'y%';
SELECT COALESCE(COUNT(distinct invoiceid),0) INTO v_ChargeInvoiceSum FROM (
SELECT a.InvoiceID FROM d_Detail_Account a inner join S_CHARGE_KIND b on a.SICKTYPEID=b.CHARGEKINDID WHERE b.ISNOTMEDINSURANCE<>'不打票' and a.INVOICEOPERATOR=OperID AND a.INVOICEDATE >= STARTTIME AND a.INVOICEDATE < ENDTIME and BALANCESTATE <> 0 AND CANCELMARK <> 2
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND (INVOICESTATE=1 or INVOICESTATE=3) AND OperatorID=OperID
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState =0 AND OperatorID=OperID
)T where InvoiceID<>'0000000000' and InvoiceID not like 'y%';
SELECT COALESCE(COUNT(distinct invoiceid),0) INTO v_RegInvoiceSum FROM (
SELECT A.InvoiceID AS INVOICEID FROM t_Register_Info A WHERE A.INVOICEOPERATOR=OperID AND (INVOICEDATE >= STARTTIME AND INVOICEDATE < ENDTIME) and BALANCESTATE <> 0
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND InvoiceState =0 AND OperatorID=OperID
)T where InvoiceID<>'0000000000' and InvoiceID not like 'y%';
else
SELECT COALESCE(COUNT(distinct invoiceid),0) INTO v_InvoiceSum FROM (
SELECT a.InvoiceID FROM d_Detail_Account a inner join S_CHARGE_KIND b on a.SICKTYPEID=b.CHARGEKINDID WHERE b.ISNOTMEDINSURANCE<>'不打票' and a.INVOICEOPERATOR=OperID AND (a.INVOICEDATE >= STARTTIME AND a.INVOICEDATE < ENDTIME) and BALANCESTATE <> 0
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND (INVOICESTATE=1 or INVOICESTATE=3) AND OperatorID=OperID
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState =0 AND OperatorID=OperID
UNION ALL
SELECT InvoiceID AS INVOICEID FROM t_Register_Info WHERE INVOICEOPERATOR=OperID AND (INVOICEDATE >= STARTTIME AND INVOICEDATE < ENDTIME) and BALANCESTATE <> 0
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND InvoiceState =0 AND OperatorID=OperID
)T where InvoiceID<>'0000000000' and InvoiceID not like 'y%';
SELECT COALESCE(COUNT(distinct invoiceid),0) INTO v_ChargeInvoiceSum FROM (
SELECT a.InvoiceID FROM d_Detail_Account a inner join S_CHARGE_KIND b on a.SICKTYPEID=b.CHARGEKINDID WHERE b.ISNOTMEDINSURANCE<>'不打票' and a.INVOICEOPERATOR=OperID AND (a.INVOICEDATE >= STARTTIME AND a.INVOICEDATE < ENDTIME) and BALANCESTATE <> 0
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND (INVOICESTATE=1 or INVOICESTATE=3) AND OperatorID=OperID
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState =0 AND OperatorID=OperID
)T where InvoiceID<>'0000000000' and InvoiceID not like 'y%';
SELECT COALESCE(COUNT(distinct invoiceid),0) INTO v_RegInvoiceSum FROM (
SELECT InvoiceID AS INVOICEID FROM t_Register_Info WHERE INVOICEOPERATOR=OperID AND INVOICEDATE >= STARTTIME AND INVOICEDATE < ENDTIME and BALANCESTATE <> 0
UNION ALL
SELECT CurrentInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND (InvoiceState =0 or InvoiceState =4) AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND InvoiceState =0 AND OperatorID=OperID
)T where InvoiceID<>'0000000000' and InvoiceID not like 'y%';
End IF;
UPDATE SESSION_OPERATORMZJZ SET INVOICECOUNT=v_InvoiceSum,CHARGEINVOICESUM=v_ChargeInvoiceSum,REGINVOICESUM=v_RegInvoiceSum WHERE OPERATORID=OperID;
--获得发票号段
SELECT defaultValue INTO v_RegisterInvoiceType FROM T_SYSTEM_CONFIG where setcontent='挂号、门诊发票是否使用同一号段';
If v_RegisterMark = 0 Then
FOR InvoiceArea IN cur_3 LOOP
if v_PrintInvoiceConfig = '0' then
--门诊退费不打印负票--
Select COALESCE(Max(distinct InvoiceID),''),COALESCE(Min(distinct InvoiceID),'') Into v_TempMaxInvoiceID,v_TempMinInvoiceID From (
SELECT a.InvoiceID,a.OperateDate FROM d_Detail_Account a inner join S_CHARGE_KIND b on a.SICKTYPEID=b.CHARGEKINDID WHERE b.ISNOTMEDINSURANCE<>'不打票' and a.INVOICEOPERATOR=OperID AND a.INVOICEDATE >= STARTTIME AND a.INVOICEDATE < ENDTIME and a.CANCELMARK <> 2
UNION ALL
SELECT InvoiceID AS INVOICEID,OperateDate FROM t_Register_Info WHERE INVOICEOPERATOR=OperID AND INVOICEDATE >= STARTTIME AND INVOICEDATE < ENDTIME
UNION ALL
SELECT OldInvoice AS INVOICEID,OperateDate FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND InvoiceState =0 AND OperatorID=OperID
UNION ALL
SELECT CURRENTINVOICE AS INVOICEID,OperateDate FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND InvoiceState =0 AND OperatorID=OperID
)t
Where InvoiceID Between InvoiceArea.StartNumber And InvoiceArea.EndNumber and InvoiceID<>'0000000000' and InvoiceID not like 'y%';
else
--门诊退费打印负票--
Select COALESCE(Max(distinct InvoiceID),''),COALESCE(Min(distinct InvoiceID),'') Into v_TempMaxInvoiceID,v_TempMinInvoiceID From (
SELECT a.InvoiceID,a.OperateDate FROM d_Detail_Account a inner join S_CHARGE_KIND b on a.SICKTYPEID=b.CHARGEKINDID WHERE b.ISNOTMEDINSURANCE<>'不打票' and a.INVOICEOPERATOR=OperID AND (a.INVOICEDATE >= STARTTIME AND a.INVOICEDATE < ENDTIME)
UNION ALL
SELECT InvoiceID AS INVOICEID,OperateDate FROM t_Register_Info WHERE INVOICEOPERATOR=OperID AND (INVOICEDATE >= STARTTIME AND INVOICEDATE < ENDTIME)
UNION ALL
SELECT OldInvoice AS INVOICEID,OperateDate FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND InvoiceState =0 AND OperatorID=OperID
UNION ALL
SELECT CURRENTINVOICE AS INVOICEID,OperateDate FROM t_InvoiceUseDetail
WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='挂号发票' AND InvoiceState =0 AND OperatorID=OperID
)t
Where InvoiceID Between InvoiceArea.StartNumber And InvoiceArea.EndNumber and InvoiceID<>'0000000000' and InvoiceID not like 'y%';
End if;
If v_TempMaxInvoiceID != '' Then
If v_TempMinInvoiceID != '' Then
v_InvoiceArea := v_InvoiceArea||v_TempMinInvoiceID||'-'||v_TempMaxInvoiceID||',';
End If;
End If;
end loop;
END IF;
FOR InvoiceAreas IN cur_4 LOOP
if v_PrintInvoiceConfig = '0' then
--门诊退费不打印负票--
Select COALESCE(Max(distinct InvoiceID),''),COALESCE(Min(distinct InvoiceID),'') Into v_TempMaxInvoiceID,v_TempMinInvoiceID From (
SELECT a.InvoiceID,a.OperateDate FROM d_Detail_Account a inner join S_CHARGE_KIND b on a.SICKTYPEID=b.CHARGEKINDID WHERE b.ISNOTMEDINSURANCE<>'不打票' and a.INVOICEOPERATOR=OperID AND a.INVOICEDATE >= STARTTIME AND a.INVOICEDATE < ENDTIME and a.CANCELMARK <> 2
UNION ALL
SELECT OldInvoice AS INVOICEID,OperateDate FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState=1 AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID,OperateDate FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState =0 AND OperatorID=OperID
UNION ALL
SELECT CURRENTINVOICE AS INVOICEID,OperateDate FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState =0 AND OperatorID=OperID
)t
Where InvoiceID Between InvoiceAreas.StartNumber And InvoiceAreas.EndNumber and InvoiceID<>'0000000000' and InvoiceID not like 'y%';
else
--门诊退费打印负票--
Select COALESCE(Max(distinct InvoiceID),''),COALESCE(Min(distinct InvoiceID),'') Into v_TempMaxInvoiceID,v_TempMinInvoiceID From (
SELECT a.InvoiceID,a.OperateDate FROM d_Detail_Account a inner join S_CHARGE_KIND b on a.SICKTYPEID=b.CHARGEKINDID WHERE b.ISNOTMEDINSURANCE<>'不打票' and a.INVOICEOPERATOR=OperID AND (a.INVOICEDATE >= STARTTIME AND a.INVOICEDATE < ENDTIME)
UNION ALL
SELECT OldInvoice AS INVOICEID,OperateDate FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState=1 AND OperatorID=OperID
UNION ALL
SELECT OldInvoice AS INVOICEID,OperateDate FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState =0 AND OperatorID=OperID
UNION ALL
SELECT CURRENTINVOICE AS INVOICEID,OperateDate FROM t_InvoiceUseDetail WHERE OperateDate >= STARTTIME AND OperateDate < ENDTIME AND InvoiceType='门诊发票' AND InvoiceState =0 AND OperatorID=OperID
)t
Where InvoiceID Between InvoiceAreas.StartNumber And InvoiceAreas.EndNumber and InvoiceID<>'0000000000' and InvoiceID not like 'y%';
End if;
If v_TempMaxInvoiceID != '' Then
If v_TempMinInvoiceID != '' Then
v_InvoiceArea := v_InvoiceArea||v_TempMinInvoiceID||'-'||v_TempMaxInvoiceID||',';
End If;
End If;
end loop;
UPDATE SESSION_OPERATORMZJZ SET INVOICESEGMENT=v_InvoiceArea WHERE OPERATORID=OperID;
--获取挂号金额,获取诊金,获取工本费,获取挂号预约费,挂号检查费
SELECT sum(RegisterFee),sum(ExamineMoney),sum(BESPEAKMONEY),sum(CHECKFEE),sum(billmoney),sum(GHTC),SUM(GHZH)
into v_RegisterFee,v_ExamineMoney,v_BespeakMoney,v_CHECKFEE,v_CaseCost,v_GHTC,V_GHZH from (
SELECT COALESCE(SUM(A.RegisterFee),0) as RegisterFee,COALESCE(SUM(A.ExamineMoney),0) as ExamineMoney,COALESCE(SUM(A.BESPEAKMONEY),0) as BESPEAKMONEY,COALESCE(SUM(A.CHECKFEE),0) as CHECKFEE, COALESCE(SUM(A.CaseCost),0) as billmoney,COALESCE(SUM(A.DISCOUNTDEFRAY),0) as GHTC,COALESCE(SUM(A.ACCOUNTDEFRAY),0) as GHZH FROM t_Register_Info A WHERE A.BALANCEOPERATOR=OperID AND BALANCEDATE >= STARTTIME AND BALANCEDATE < ENDTIME
union all
SELECT 0 as RegisterFee,0 as ExamineMoney,0 as BESPEAKMONEY,0 as CHECKFEE,COALESCE(SUM(A.BILLFEE),0) as billmoney,0 as GHTC,0 as GHZH FROM d_Detail_Account A WHERE A.BALANCEOPERATOR=OperID AND BALANCEDATE >= STARTTIME AND BALANCEDATE < ENDTIME
) t;
UPDATE SESSION_OPERATORMZJZ SET REGISTERMONEY=v_RegisterFee,DIAGNOSISMONEY=v_ExamineMoney,BESPEAKMONEY=v_BespeakMoney,STUFFMONEY=v_CaseCost WHERE OPERATORID=OperID;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号检查费',v_CHECKFEE,v_CHECKFEE,OperID);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号统筹金额',v_GHTC,v_GHTC,OperID);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号账户金额',V_GHZH,V_GHZH,OperID);
--获取挂号退金额,获取退诊金,获取退工本费,获取挂号退预约费,挂号退检查费
select sum(RegisterFee),sum(ExamineMoney),sum(BESPEAKMONEY),sum(CHECKFEE),sum(billmoney),sum(GHTC),SUM(GHZH)
into v_RegisterFeeRefund,v_ExamineMoneyRefund,v_BespeakMoneyRefund,v_CHECKFEERefund,v_CaseCostRefund,v_GHTCRefund,v_GHZHRefund from (
SELECT COALESCE(SUM(A.RegisterFee),0) as RegisterFee,COALESCE(SUM(A.ExamineMoney),0) as ExamineMoney,COALESCE(SUM(A.BESPEAKMONEY),0) as BESPEAKMONEY,COALESCE(SUM(A.CHECKFEE),0) as CHECKFEE, COALESCE(SUM(A.CaseCost),0) as billmoney,COALESCE(SUM(A.DISCOUNTDEFRAY),0) as GHTC,COALESCE(SUM(A.ACCOUNTDEFRAY),0) as GHZH FROM t_Register_Info A WHERE A.BALANCEOPERATOR=OperID AND BALANCEDATE >= STARTTIME AND BALANCEDATE < ENDTIME and CANCELMARK = 2
union all
SELECT 0 as RegisterFee,0 as ExamineMoney,0 as BESPEAKMONEY,0 as CHECKFEE,COALESCE(SUM(A.BILLFEE),0) as billmoney,0 AS GHTC,0 AS GHZH FROM d_Detail_Account A WHERE A.BALANCEOPERATOR=OperID AND BALANCEDATE >= STARTTIME AND BALANCEDATE < ENDTIME and CANCELMARK = 2
) t;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号金额退费',v_RegisterFeeRefund,v_RegisterFeeRefund,OperID);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'诊金退费',v_ExamineMoneyRefund,v_ExamineMoneyRefund,OperID);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号预约费退费',v_BespeakMoneyRefund,v_BespeakMoneyRefund,OperID);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号检查费退费',v_CHECKFEERefund,v_CHECKFEERefund,OperID);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'工本费退费',v_CaseCostRefund,v_CaseCostRefund,OperID);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号退统筹',v_GHTCRefund,v_GHTCRefund,OperID);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'挂号退账户',v_GHZHRefund,v_GHZHRefund,OperID);
FOR ReportItem IN cur_5 LOOP
--获得该摘要的应收合计金额,获得该摘要的实收合计金额
SELECT COALESCE(SUM(b.Money),0) AS Money,COALESCE(SUM(b.SelfMoney),0) AS SelfMoney into v_SumMoney,v_SumSelfMoney FROM D_SUMMARY_INFO a, d_Detail_Account b,T_CLINICINVOICEITEM c WHERE b.BALANCEOPERATOR=OperID AND a.CLINICINVOICEID=c.INVOICEID and c.INVOICEITEM = ReportItem.ReportItem AND a.ItemID=b.ItemID AND b.BALANCEDATE >= STARTTIME AND BALANCEDATE < ENDTIME;
--获得该摘要的应收退费金额,获得该摘要的实收退费金额
SELECT COALESCE(SUM(b.Money),0) AS Money,COALESCE(SUM(b.SelfMoney),0) AS SelfMoney into v_ReturnMoney,v_ReturnSelfMoney FROM D_SUMMARY_INFO a, d_Detail_Account b,T_CLINICINVOICEITEM c WHERE b.BALANCEOPERATOR=OperID AND a.CLINICINVOICEID=c.INVOICEID and c.INVOICEITEM = ReportItem.ReportItem AND a.ItemID=b.ItemID AND (b.CancelMark=2 ) AND b.BALANCEDATE >= STARTTIME AND BALANCEDATE < ENDTIME;
--获得该摘要的挂账金额
SELECT COALESCE(sum(ACCOUNTDEFRAY),0)+COALESCE(sum(DISCOUNTDEFRAY),0) INTO v_HangMoney FROM D_SUMMARY_INFO a, d_Detail_Account b,S_CHARGE_KIND,T_CLINICINVOICEITEM c WHERE b.BALANCEOPERATOR=OperID AND a.CLINICINVOICEID=c.INVOICEID and c.INVOICEITEM = ReportItem.ReportItem AND a.ItemID=b.ItemID AND b.BALANCEDATE >= STARTTIME AND BALANCEDATE < ENDTIME and b.SICKTYPEID =S_CHARGE_KIND. CHARGEKINDID and (S_CHARGE_KIND.ISNOTMEDINSURANCE = '是' or S_CHARGE_KIND.ISNOTMEDINSURANCE = '不打票') and BALANCESTATE <> 0;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_SumMoney,INFACTMONEY=v_SumMoney
WHERE GATHERINGID=gatheringId AND ITEM = ReportItem.ReportItem||'应收' AND OPERATORID=OperID;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_ReturnMoney,INFACTMONEY=v_ReturnMoney
WHERE GATHERINGID=gatheringId AND ITEM = ReportItem.ReportItem||'应收退费' AND OPERATORID=OperID;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_SumSelfMoney,INFACTMONEY=v_SumSelfMoney
WHERE GATHERINGID=gatheringId AND ITEM = ReportItem.ReportItem||'实收' AND OPERATORID=OperID;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_ReturnSelfMoney,INFACTMONEY=v_ReturnSelfMoney
WHERE GATHERINGID=gatheringId AND ITEM = ReportItem.ReportItem||'实收退费' AND OPERATORID=OperID;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_HangMoney,INFACTMONEY=v_HangMoney
WHERE GATHERINGID=gatheringId AND ITEM = ReportItem.ReportItem||'挂账' AND OPERATORID=OperID;
end loop;
--获取银医直联卡中每项收费的金额
FOR CardTypes IN cur_2 LOOP
SELECT COALESCE(SUM(money),0) Money INTO v_BankCardMoney FROM D_CLINICBALANCE WHERE operatorid=OperID AND operatedate >= STARTTIME AND operatedate < ENDTIME and BALANCEMODE=CardTypes.TYPE_NAME;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_BankCardMoney,INFACTMONEY=v_BankCardMoney
WHERE GATHERINGID=gatheringId AND ITEM=CardTypes.TYPE_NAME AND OPERATORID=OperID;
end loop;
--获取银医直联卡中每项收费的金额
--预交金收款金额
SELECT COALESCE(sum(addmoney),0) INTO v_Recharge FROM T_CARD_SAVING where BUSINESSTYPE = '充值' and OPERATOR = OperID and ADDMONEYDATE >= STARTTIME AND ADDMONEYDATE < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'预交金收款金额',v_Recharge,v_Recharge,OperID);
--住院转门诊预交金（对门诊是充值）
SELECT COALESCE(sum(addmoney),0) INTO v_RechargeInhos FROM T_CARD_SAVING where BUSINESSTYPE = '充值' and MODETYPE = '退住院预交金' and OPERATOR = OperID and ADDMONEYDATE >= STARTTIME AND ADDMONEYDATE < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'住院转门诊预交金',v_RechargeInhos,v_RechargeInhos,OperID);
--预交金退款金额
SELECT COALESCE(sum(addmoney),0) INTO v_RechargeReturn FROM T_CARD_SAVING where BUSINESSTYPE = '退款' and OPERATOR = OperID and ADDMONEYDATE >= STARTTIME AND ADDMONEYDATE < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'预交金退款金额',v_RechargeReturn,v_RechargeReturn,OperID);
--门诊转住院预交金（对门诊是退款）
SELECT COALESCE(sum(addmoney),0) INTO v_RechargeReturnInhos FROM T_CARD_SAVING where BUSINESSTYPE = '退款' and MODETYPE = '转住院预交金' and OPERATOR = OperID and ADDMONEYDATE >= STARTTIME AND ADDMONEYDATE < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'门诊转住院预交金',v_RechargeReturnInhos,v_RechargeReturnInhos,OperID);
--已结算退费金额
SELECT COALESCE(sum(addmoney),0) INTO v_BalanceReturnMoney FROM T_CARD_SAVING where BUSINESSTYPE = '退费' and OPERATOR = OperID and ADDMONEYDATE >= STARTTIME AND ADDMONEYDATE < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'已结算退费金额',v_BalanceReturnMoney,v_BalanceReturnMoney,OperID);
--实收现金
SELECT COALESCE(sum(money),0) INTO v_Cash FROM D_CLINICBALANCE WHERE operatorid = OperID AND operatedate >= STARTTIME AND operatedate < ENDTIME and BALANCEMODE='现金';
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'实收现金',v_Cash,v_Cash,OperID);
SELECT COALESCE(sum(money),0) INTO v_YL FROM D_CLINICBALANCE WHERE operatorid=OperID AND operatedate >= STARTTIME AND operatedate < ENDTIME and BALANCEMODE='银联卡';
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'银联卡支付',v_YL,v_YL,OperID);
SELECT COALESCE(sum(money),0) INTO v_YJJ FROM D_CLINICBALANCE WHERE operatorid=OperID AND operatedate >= STARTTIME AND operatedate < ENDTIME and BALANCEMODE='预交金';
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'预交金支付',v_YJJ,v_YJJ,OperID);
SELECT COALESCE(sum(money),0) INTO v_ZP FROM D_CLINICBALANCE WHERE operatorid=OperID AND operatedate >= STARTTIME AND operatedate < ENDTIME and BALANCEMODE='支票';
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'支票支付',v_ZP,v_ZP,OperID);
SELECT COALESCE(sum(money),0) INTO v_TCZF FROM D_CLINICBALANCE WHERE operatorid=OperID AND operatedate >= STARTTIME AND operatedate < ENDTIME and BALANCEMODE='统筹支付';
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'统筹支付',v_TCZF,v_TCZF,OperID);
SELECT COALESCE(sum(money),0) INTO v_ZHZF FROM D_CLINICBALANCE WHERE operatorid=OperID AND operatedate >= STARTTIME AND operatedate < ENDTIME and BALANCEMODE='账户支付';
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'账户支付',v_ZHZF,v_ZHZF,OperID);
SELECT COALESCE(sum(money),0) INTO v_NoPrintYJJ FROM D_CLINICBALANCE,S_CHARGE_KIND WHERE D_CLINICBALANCE.SICKTYPEID=S_CHARGE_KIND.CHARGEKINDID and S_CHARGE_KIND.ISNOTMEDINSURANCE='不打票' and D_CLINICBALANCE.operatorid=OperID AND D_CLINICBALANCE.operatedate >= STARTTIME AND D_CLINICBALANCE.operatedate < ENDTIME and D_CLINICBALANCE.BALANCEMODE='预交金';
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'不打票预交金',v_NoPrintYJJ,v_NoPrintYJJ,OperID);
FOR EchargeKind IN cur_1 LOOP
SELECT COALESCE(sum(money),0) INTO v_ChargeKindTCZF FROM D_CLINICBALANCE WHERE operatorid=OperID AND operatedate >= STARTTIME AND operatedate < ENDTIME AND BALANCEMODE='统筹支付' AND SICKTYPEID = EchargeKind.CHARGEKINDID;
SELECT COALESCE(sum(money),0) INTO v_ChargeKindZHZF FROM D_CLINICBALANCE WHERE operatorid=OperID AND operatedate >= STARTTIME AND operatedate < ENDTIME and BALANCEMODE='账户支付' AND SICKTYPEID = EchargeKind.CHARGEKINDID;
SELECT COALESCE(sum(money),0) INTO v_ChargeKindXJ FROM D_CLINICBALANCE WHERE operatorid=OperID AND operatedate >= STARTTIME AND operatedate < ENDTIME AND BALANCEMODE='现金' and SICKTYPEID = EchargeKind.CHARGEKINDID;
SELECT COALESCE(sum(money),0) INTO v_ChargeKindYLK FROM D_CLINICBALANCE WHERE operatorid=OperID AND operatedate >= STARTTIME AND operatedate < ENDTIME AND BALANCEMODE='银联卡' and SICKTYPEID = EchargeKind.CHARGEKINDID;
SELECT COALESCE(sum(money),0) INTO v_ChargeKindYJJ FROM D_CLINICBALANCE WHERE operatorid=OperID AND operatedate >= STARTTIME AND operatedate < ENDTIME AND BALANCEMODE='预交金' and SICKTYPEID = EchargeKind.CHARGEKINDID;
SELECT COALESCE(sum(money),0) INTO v_ChargeKindZP FROM D_CLINICBALANCE WHERE operatorid=OperID AND operatedate >= STARTTIME AND operatedate < ENDTIME AND BALANCEMODE='支票' and SICKTYPEID = EchargeKind.CHARGEKINDID;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_ChargeKindTCZF,INFACTMONEY=v_ChargeKindTCZF
WHERE GATHERINGID=gatheringId AND ITEM = EchargeKind.CHARGEKINDID||'统筹支付' AND OPERATORID=OperID;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_ChargeKindZHZF,INFACTMONEY=v_ChargeKindZHZF
WHERE GATHERINGID=gatheringId AND ITEM = EchargeKind.CHARGEKINDID||'账户支付' AND OPERATORID=OperID;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_ChargeKindXJ,INFACTMONEY=v_ChargeKindXJ
WHERE GATHERINGID=gatheringId AND ITEM = EchargeKind.CHARGEKINDID||'现金' AND OPERATORID=OperID;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_ChargeKindYLK,INFACTMONEY=v_ChargeKindYLK
WHERE GATHERINGID=gatheringId AND ITEM = EchargeKind.CHARGEKINDID||'银联卡' AND OPERATORID=OperID;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_ChargeKindYJJ,INFACTMONEY=v_ChargeKindYJJ
WHERE GATHERINGID=gatheringId AND ITEM = EchargeKind.CHARGEKINDID||'预交金' AND OPERATORID=OperID;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_ChargeKindZP,INFACTMONEY=v_ChargeKindZP
WHERE GATHERINGID=gatheringId AND ITEM = EchargeKind.CHARGEKINDID||'支票' AND OPERATORID=OperID;
end loop;
--应收退费合计 ,实收退费合计
SELECT COALESCE(SUM(Money),0),COALESCE(SUM(SELFMONEY),0) into v_ReturnSumMoney,v_ReturnSumSelfMoney FROM d_Detail_Account b WHERE OPERATORID=OperID AND (b.CancelMark=2 ) AND OPERATEDATE >= STARTTIME AND OPERATEDATE < ENDTIME;
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'应收退费合计',v_ReturnSumMoney,v_ReturnSumMoney,OperID);
INSERT INTO SESSION_OPERATORMZJZMX(GATHERINGID,ITEM,PAYMENTMONEY,INFACTMONEY,OPERATORID) VALUES (gatheringId,'实收退费合计',v_ReturnSumSelfMoney,v_ReturnSumSelfMoney,OperID);
--应收合计
v_ReceivablesSum:=v_ReceivablesSum+v_RegisterFee+v_ExamineMoney+v_CaseCost + v_BespeakMoney + v_CHECKFEE;
UPDATE SESSION_OPERATORMZJZ SET PAYMENTMONEY=COALESCE(v_ReceivablesSum,0) WHERE OPERATORID=OperID;
--实收合计
v_RealIncomeSum:=v_RealIncomeSum+v_RegisterFee+v_ExamineMoney+v_CaseCost + v_BespeakMoney + v_CHECKFEE-v_GHTC-v_GHZH;
UPDATE SESSION_OPERATORMZJZ SET INFACTMONEY=COALESCE(v_RealIncomeSum,0) WHERE OPERATORID=OperID;
--挂帐合计
v_HangAccountSum:=v_ReceivablesSum-v_RealIncomeSum;
UPDATE SESSION_OPERATORMZJZ SET HANGMONEY=COALESCE(v_HangAccountSum,0) WHERE OPERATORID=OperID;
--POS应收金额
v_POSReceivables:=0;
UPDATE SESSION_OPERATORMZJZ SET POSPAYMENT=COALESCE(v_POSReceivables,0) WHERE OPERATORID=OperID;
--POS实收金额
v_POSRealIncome:=0;
UPDATE SESSION_OPERATORMZJZ SET POSMONEY=COALESCE(v_POSRealIncome,0) WHERE OPERATORID=OperID;
--POS挂帐金额
v_POSHangAccount:=0;
UPDATE SESSION_OPERATORMZJZ SET POSHANG=COALESCE(v_POSHangAccount,0) WHERE OPERATORID=OperID;
--附加费应收金额
v_OptionFeeReceivables:=0;
UPDATE SESSION_OPERATORMZJZ SET SUBJOINPAYMENT=COALESCE(v_OptionFeeReceivables,0) WHERE OPERATORID=OperID;
--附加费实收金额
v_OptionFeeRealIncome:=0;
UPDATE SESSION_OPERATORMZJZ SET SUBJOINMONEY=COALESCE(v_OptionFeeRealIncome,0) WHERE OPERATORID=OperID;
--附加费挂帐金额
v_OptionFeeHangAccount:=0;
UPDATE SESSION_OPERATORMZJZ SET SUBJOINHANG=COALESCE(v_OptionFeeHangAccount,0) WHERE OPERATORID=OperID;
--不打票应收,不打票实收
SELECT COALESCE(SUM(b.Money),0), COALESCE(SUM(b.SelfMoney),0) INTO v_NoPrintMoney,v_NoPrintSelfMoney
FROM D_SUMMARY_INFO a, d_Detail_Account b, S_CHARGE_KIND c WHERE b.BALANCEOPERATOR=OperID AND c.ISNOTMEDINSURANCE='不打票' AND a.ItemID=b.ItemID AND b.SICKTYPEID=c.CHARGEKINDID AND BALANCEDATE >= STARTTIME AND BALANCEDATE < ENDTIME;
UPDATE SESSION_OPERATORMZJZ SET NOPRINTMONEY=COALESCE(v_NoPrintMoney,0),NOPRINTSELFMONEY=COALESCE(v_NoPrintSelfMoney,0) WHERE OPERATORID=OperID;
--统筹挂账金额
select COALESCE(sum(DiscountDefray),0) INTO v_TCGZ
FROM d_Detail_Account,S_CHARGE_KIND
WHERE d_Detail_Account.SICKTYPEID =S_CHARGE_KIND. CHARGEKINDID and (S_CHARGE_KIND.ISNOTMEDINSURANCE = '是' or S_CHARGE_KIND.ISNOTMEDINSURANCE = '不打票') and BALANCEDATE >= STARTTIME AND BALANCEDATE < ENDTIME AND BALANCEOPERATOR=OperID;
UPDATE SESSION_OPERATORMZJZ SET PLANMONEY=COALESCE(v_TCGZ,0) WHERE OPERATORID=OperID;
--卡支付额
select COALESCE(sum(ACCOUNTDEFRAY),0) INTO v_CardMoney
FROM d_Detail_Account,S_CHARGE_KIND
WHERE d_Detail_Account.SICKTYPEID =S_CHARGE_KIND. CHARGEKINDID and BALANCEDATE >= STARTTIME AND BALANCEDATE < ENDTIME AND BALANCEOPERATOR=OperID;
UPDATE SESSION_OPERATORMZJZ SET CARDPAY=COALESCE(v_CardMoney,0) WHERE OPERATORID=OperID;
--优惠打折金额
select COALESCE(sum(DiscountDefray),0) INTO v_YHDZ
FROM d_Detail_Account,S_CHARGE_KIND
WHERE d_Detail_Account.SICKTYPEID =S_CHARGE_KIND. CHARGEKINDID and CHARGEKIND not like '%医%' and BALANCEDATE >= STARTTIME AND BALANCEDATE < ENDTIME AND BALANCEOPERATOR=OperID;
UPDATE SESSION_OPERATORMZJZ SET REBATEMONEY=COALESCE(v_YHDZ,0) WHERE OPERATORID=OperID;
--慢性病统筹
select COALESCE(sum(DiscountDefray),0) INTO v_MXBTC
FROM d_Detail_Account,S_CHARGE_KIND
WHERE d_Detail_Account.SICKTYPEID =S_CHARGE_KIND. CHARGEKINDID and CHARGEKIND like '%慢%' and BALANCEDATE >= STARTTIME AND BALANCEDATE < ENDTIME AND BALANCEOPERATOR=OperID;
UPDATE SESSION_OPERATORMZJZ SET CHRONICPLAN=COALESCE(v_MXBTC,0) WHERE OPERATORID=OperID;
FOR BalanceMode IN cur_6 LOOP
SELECT COALESCE(SUM(b.Money),0),COALESCE(SUM(b.SelfMoney),0) into v_BMsumMoney,v_BMselfMoney FROM d_Detail_Account b WHERE b.BALANCEOPERATOR=OperID and BALANCEMODE = BalanceMode.BALANCEMODE and BALANCEDATE >= STARTTIME AND BALANCEDATE < ENDTIME;
v_BMhangMoney := v_BMsumMoney - v_BMselfMoney;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_BMsumMoney,INFACTMONEY=v_BMsumMoney
WHERE GATHERINGID=gatheringId AND ITEM = BalanceMode.BALANCEMODE||'应收' AND OPERATORID=OperID;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_BMselfMoney,INFACTMONEY=v_BMselfMoney
WHERE GATHERINGID=gatheringId AND ITEM = BalanceMode.BALANCEMODE||'实收' AND OPERATORID=OperID;
UPDATE SESSION_OPERATORMZJZMX SET PAYMENTMONEY=v_BMhangMoney,INFACTMONEY=v_BMhangMoney
WHERE GATHERINGID=gatheringId AND ITEM = BalanceMode.BALANCEMODE||'挂账' AND OPERATORID=OperID;
end loop;
---集成第二个存储过程，插入发票使用明细表---------------------------------------
If v_RegisterMark = 0 Then
--统计挂号退票 `
INSERT INTO SESSION_OPERATORMZJZUSEDMX(ID,GATHERINGID,OPERATORID,OPERATETYPE,CURRENTINVOICE,QUONDAMINVOICE,SUMMARY,MONEY,ACTUALMONEY)
SELECT to_char(sysdate,'yyyymmddhh24mi')||TRUNC(100+900*dbms_random.value),gatheringId,T.OperatorID,'退票',T.CurrentInvoice,T.OldInvoice,'挂号费',COALESCE(SUM(R.RegisterFee+R.ExamineMoney+R.CaseCost),0),
COALESCE(SUM(R.RegisterFee+R.ExamineMoney+R.CaseCost),0) FROM t_InvoiceUseDetail T, t_Register_Info R
Where T.OperateDate >= STARTTIME AND T.OperateDate < ENDTIME AND T.OldInvoice=R.InvoiceID AND T.InvoiceState=2
AND T.InvoiceType=v_RegisterInvoiceType AND R.CancelMark=1 AND T.OperatorID=OperID GROUP BY T.CurrentInvoice,T.OldInvoice,T.OperatorID ORDER BY T.OperatorID,T.OldInvoice;
--统计挂号废票
INSERT INTO SESSION_OPERATORMZJZUSEDMX(ID,GATHERINGID,OPERATORID,OPERATETYPE,CURRENTINVOICE,QUONDAMINVOICE,SUMMARY,MONEY,ACTUALMONEY)
SELECT to_char(sysdate,'yyyymmddhh24mi')||TRUNC(100+900*dbms_random.value),gatheringId,T.OperatorID,'废票',T.CurrentInvoice,T.OldInvoice,'挂号费',COALESCE(SUM(R.RegisterFee+R.ExamineMoney+R.CaseCost),0),
COALESCE(SUM(R.RegisterFee+R.ExamineMoney+R.CaseCost),0) FROM t_InvoiceUseDetail T, t_Register_Info R
WHERE T.OperateDate >= STARTTIME AND T.OperateDate < ENDTIME AND T.OldInvoice=R.InvoiceID AND T.InvoiceState=3
AND T.InvoiceType=v_RegisterInvoiceType AND R.CancelMark=1 AND T.OperatorID=OperID GROUP BY T.CurrentInvoice,T.OldInvoice,T.OperatorID ORDER BY T.OperatorID,T.OldInvoice;
--统计挂号重打费票
INSERT INTO SESSION_OPERATORMZJZUSEDMX(ID,GATHERINGID,OPERATORID,OPERATETYPE,CURRENTINVOICE,QUONDAMINVOICE,SUMMARY,MONEY,ACTUALMONEY)
SELECT to_char(sysdate,'yyyymmddhh24mi')||TRUNC(100+900*dbms_random.value),gatheringId,T.OperatorID,'废票','' CurrentInvoice,T.OldInvoice,'挂号费',COALESCE(SUM(R.RegisterFee+R.ExamineMoney+R.CaseCost),0),
COALESCE(SUM(R.RegisterFee+R.ExamineMoney+R.CaseCost),0) FROM t_InvoiceUseDetail T, t_Register_Info R
WHERE T.OperateDate >= STARTTIME AND T.OperateDate < ENDTIME AND T.CurrentInvoice=R.InvoiceID AND T.InvoiceState=0
AND T.InvoiceType=v_RegisterInvoiceType AND R.CancelMark=0 AND T.OperatorID=OperID GROUP BY T.CurrentInvoice,T.OldInvoice,T.OperatorID ORDER BY T.OperatorID,T.OldInvoice;
--统计挂号重打
INSERT INTO SESSION_OPERATORMZJZUSEDMX(ID,GATHERINGID,OPERATORID,OPERATETYPE,CURRENTINVOICE,QUONDAMINVOICE,SUMMARY,MONEY,ACTUALMONEY)
SELECT to_char(sysdate,'yyyymmddhh24mi')||TRUNC(100+900*dbms_random.value),gatheringId,T.OperatorID,'重打',T.CurrentInvoice,T.OldInvoice,'挂号费',COALESCE(SUM(R.RegisterFee+R.ExamineMoney+R.CaseCost),0),
COALESCE(SUM(R.RegisterFee+R.ExamineMoney+R.CaseCost),0) FROM t_InvoiceUseDetail T, t_Register_Info R
WHERE T.OperateDate >= STARTTIME AND T.OperateDate < ENDTIME AND T.OldInvoice=R.InvoiceID AND T.InvoiceState=0
AND T.InvoiceType=v_RegisterInvoiceType AND R.CancelMark<>2 AND T.OperatorID=OperID GROUP BY T.CurrentInvoice,T.OldInvoice,T.OperatorID ORDER BY T.OperatorID,T.OldInvoice;
end if;
--统计正常收费的废票
--INSERT INTO SESSION_OPERATORMZJZUSEDMX(ID,GATHERINGID,OPERATORID,OPERATETYPE,CURRENTINVOICE,QUONDAMINVOICE,SUMMARY,MONEY,ACTUALMONEY)
--SELECT to_char(sysdate,'yyyymmddhh24mi')||TRUNC(100+900*dbms_random.value),gatheringId,T.OperatorID,'废票' 类型,T.CurrentInvoice,T.OldInvoice,B.Summary,Sum(D.SelfMoney) SelfMoney,Sum(D.Money) Money
--FROM t_InvoiceUseDetail T, d_Detail_Account D,D_SUMMARY_INFO B
--WHERE T.OperateDate >= STARTTIME AND T.OperateDate < ENDTIME AND T.OldInvoice=D.InvoiceID AND D.ItemID=B.ItemID
--AND T.InvoiceState=3 AND D.CancelMark=1 AND T.InvoiceType='门诊发票' AND T.OperatorID=OperID GROUP BY T.CurrentInvoice,T.OldInvoice,B.Summary,T.OperatorID ORDER BY T.OperatorID,T.OldInvoice;
INSERT INTO SESSION_OPERATORMZJZUSEDMX(ID,GATHERINGID,OPERATORID,OPERATETYPE,CURRENTINVOICE,QUONDAMINVOICE,SUMMARY,MONEY,ACTUALMONEY)
SELECT to_char(sysdate,'yyyymmddhh24mi')||TRUNC(100+900*dbms_random.value),gatheringId,T.OperatorID,'废票' 类型,T.CurrentInvoice,T.OldInvoice,B.Summary,Sum(D.SelfMoney) SelfMoney,Sum(D.Money) Money
FROM t_InvoiceUseDetail T left join d_Detail_Account D on T.CurrentInvoice=D.InvoiceID AND D.CancelMark=0 AND T.InvoiceType='门诊发票',D_SUMMARY_INFO B
WHERE T.OperateDate >= STARTTIME AND T.OperateDate < ENDTIME AND D.ItemID=B.ItemID
AND T.InvoiceState=0 AND T.OperatorID=OperID GROUP BY T.CurrentInvoice,T.OldInvoice,B.Summary,T.OperatorID ORDER BY OperatorID,OldInvoice;
--统计退票
INSERT INTO SESSION_OPERATORMZJZUSEDMX(ID,GATHERINGID,OPERATORID,OPERATETYPE,CURRENTINVOICE,QUONDAMINVOICE,SUMMARY,MONEY,ACTUALMONEY)
SELECT to_char(sysdate,'yyyymmddhh24mi')||TRUNC(100+900*dbms_random.value),gatheringId,T.OperatorID,'退票',T.CurrentInvoice,T.OldInvoice,B.Summary,Sum(D.SelfMoney),Sum(D.Money) Money
FROM t_InvoiceUseDetail T, d_Detail_Account D,D_SUMMARY_INFO B
WHERE T.OperateDate >= STARTTIME AND T.OperateDate < ENDTIME AND D.ItemID=B.ItemID
AND T.OldInvoice=D.InvoiceID AND T.InvoiceState=2 AND T.InvoiceType='门诊发票' AND D.CancelMark=1 AND T.OperatorID=OperID
GROUP BY T.CurrentInvoice,T.OldInvoice,B.Summary,T.OperatorID ORDER BY T.OperatorID,T.OldInvoice;
--统计未使用发票作废
INSERT INTO SESSION_OPERATORMZJZUSEDMX(ID,GATHERINGID,OPERATORID,OPERATETYPE,CURRENTINVOICE,QUONDAMINVOICE)
SELECT to_char(sysdate,'yyyymmddhh24mi')||TRUNC(100+900*dbms_random.value),gatheringId,T.OperatorID,'作废',T.CurrentInvoice,T.OldInvoice FROM t_InvoiceUseDetail T
WHERE T.OperateDate >= STARTTIME AND T.OperateDate < ENDTIME AND T.InvoiceType='门诊发票' AND T.InvoiceState=1
AND T.OperatorID=OperID GROUP BY T.CurrentInvoice,T.OldInvoice,T.OperatorID ORDER BY T.OperatorID,T.OldInvoice;
--统计发票重打
INSERT INTO SESSION_OPERATORMZJZUSEDMX(ID,GATHERINGID,OPERATORID,OPERATETYPE,CURRENTINVOICE,QUONDAMINVOICE,SUMMARY,MONEY,ACTUALMONEY)
SELECT to_char(sysdate,'yyyymmddhh24mi')||TRUNC(100+900*dbms_random.value),gatheringId,T.OperatorID,'重打',T.CurrentInvoice,T.OldInvoice,B.Summary,Sum(D.SelfMoney),Sum(D.Money) Money
FROM t_InvoiceUseDetail T, d_Detail_Account D,D_SUMMARY_INFO B
WHERE T.OperateDate >= STARTTIME AND T.OperateDate < ENDTIME AND (D.CancelMark=0 OR D.CancelMark=1) AND D.ItemID=B.ItemID
AND T.OldInvoice=D.InvoiceID AND T.INVOICESTATE=0 AND T.InvoiceType='门诊发票' AND T.OperatorID=OperID
GROUP BY T.CurrentInvoice,T.OldInvoice,B.Summary,T.OperatorID ORDER BY T.OperatorID,T.CurrentInvoice,T.OldInvoice;
IF(ispreview=1) THEN
--报账单总表
INSERT INTO D_GATHERING_MASTER SELECT * FROM SESSION_OPERATORMZJZ WHERE GATHERINGID =v_gatheringId AND OPERATORID=OperID;
--报账单明细
INSERT INTO D_GATHERING_DETAIL SELECT * FROM SESSION_OPERATORMZJZMX WHERE GATHERINGID =v_gatheringId AND OPERATORID=OperID;
--报账单发票明细
INSERT INTO D_GATHERING_INVOICEUSEDETAIL SELECT * FROM SESSION_OPERATORMZJZUSEDMX WHERE GATHERINGID =v_gatheringId AND OPERATORID=OperID;
--结账区间
INSERT INTO SAIPADMIN.T_RECKONACCOUNTS_TIME(OPERATORID,STARTTIME,ENDTIME,RECKONACCOUNTSTYPE,ACCOUNTMARK,PHARMACYBALANCETIME,SETTLEOPERATORID,SETTLEID) VALUES (OperID,StartTime,EndTime,'门诊',0,SYSDATE,seitleoperatorId,v_gatheringId);
END IF ;
err := 0;
end PH_FinancingReport;
2、db2语法
--存储过程名称
CREATE PROCEDURE PH_table
（
--定义传入的参数
cs1 VARCHAR2(200), --参数1 **区别
cs2 TIMESTAMP, --参数2
cs3 INT --参数3
）
CREATE PROCEDURE ph_MC_ZDPD
DYNAMIC RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
--定义最后一次盘点时间
DECLARE v_INVENTORYDATE TIMESTAMP;
--上次库存调整是否完成的行数
DECLARE v_HangShu INT;
--有数据但是未审核的行数
DECLARE v_SL INT;
--科室名称
DECLARE v_office varchar(50);
--科室类型
DECLARE v_officetype varchar(10);
--现在时间
DECLARE datenow TIMESTAMP;
SET datenow=current TIMESTAMP;
FOR rec AS SELECT DISTINCT A.DRUGSTOREID OFFICEID FROM T_COUNT_STORAGE A,T_OFFICE B WHERE A.DRUGSTOREID=B.OFFICEID AND LEECHDOMNO LIKE '04%' AND AMOUNT >0 UNION ALL SELECT DISTINCT PHARMACYID OFFICEID FROM T_MEDIC_STORAGE A,T_OFFICE B WHERE A.PHARMACYID=B.OFFICEID AND B.OFFICETYPE='库房'
DO
--科室名称
SET v_office=(SELECT OFFICEID FROM T_OFFICE C WHERE C.OFFICEID=rec.OFFICEID );
--科室类型
SET v_officetype=(SELECT OFFICETYPE FROM T_OFFICE C WHERE C.OFFICEID=rec.OFFICEID );
--获取最后一次盘点数据的盘点时间
SET v_INVENTORYDATE = (SELECT MAX(INVENTORYDATE) FROM MC_STORAGEINVENTORY WHERE INVENTORYDEPTID=rec.OFFICEID);
--判断上次库存调整是否完成
SET v_HangShu = (select count(*) from MC_STORAGEINVENTORY where MINUSVALUE<>0 and ADJUSTMARK=1 and INVENTORYDEPTID =rec.OFFICEID and INVENTORYDATE BETWEEN v_INVENTORYDATE AND v_INVENTORYDATE);
IF v_HangShu=0 THEN
-- 有数据但是未审核的行数
SET v_SL= (SELECT COUNT(*) FROM MC_STORAGEINVENTORY WHERE (ADJUSTMARK=0 or ADJUSTMARK=3) AND INVENTORYDEPTID=rec.OFFICEID AND INVENTORYDATE=v_INVENTORYDATE);
IF v_SL >0 THEN
--删除未审核的数据
DELETE FROM MC_STORAGEINVENTORY WHERE INVENTORYDEPTID=rec.OFFICEID AND INVENTORYDATE=v_INVENTORYDATE AND (ADJUSTMARK=0 or ADJUSTMARK=3);
END IF;
--插入盘点数据
IF v_officetype='库房' THEN
INSERT INTO MC_STORAGEINVENTORY (ID, INVENTORYDATE, INVENTORYDEPTID, LEECHDOMNO, BATCHSERIAL, BUYPRICE,WHOLESALEPRICE,RETAILPRICE, EXISTINGSTORAGE,FACTSTORAGE,PUTPLACE,
OPERATORID, AUDITINGPERSONID, MINUSVALUE, ADJUSTMARK, SUMMARK, REMARK, NOTSENDSTORAGE, NOTSENDBUYMONEY, NOTSENDRETAILMONEY)
SELECT ROW_NUMBER() over(order by A.LEECHDOMNO) ID,
datenow INVENTORYDATE,
rec.OFFICEID INVENTORYDEPTID,
A.LEECHDOMNO LEECHDOMNO,
'' BATCHSERIAL,
B.BUYPRICE BUYPRICE,
B.WHOLESALEPRICE WHOLESALEPRICE,
B.RETAILPRICE RETAILPRICE,
SUM(A.AMOUNT) AS EXISTINGSTORAGE,
SUM(A.AMOUNT) AS FACTSTORAGE,
A.PUTPLACE PUTPLACE,
'自动盘点' OPERATORID,
'0634' AUDITINGPERSONID,
'0' MINUSVALUE,
'1' ADJUSTMARK, --0:未审核 1：已审核 2：已调整 3 已录入
'0' SUMMARK,
'' REMARK,
NULL NOTSENDSTORAGE,
NULL NOTSENDBUYMONEY,
NULL NOTSENDRETAILMONEY
FROM T_MEDIC_STORAGE A, (SELECT CURRENCYNAME, LEECHDOMCHEMNAME, STROKENO, LEECHDOMCLASS, LEECHDOMUSE
, RECIPEHEADSHIP, DOSAGETYPE, TOXICITY, EXPLAIN, ARCHIVESTIME
, STOPTIME, LEECHDOMALIAS, CHARACTER, FUNCTIONSORT, DRUGTRUTHACTION
, FITDISEASE, TABU, ADVERT, RECIPROCITY, BADNESSFEEDBACK
, STORAGE, LEECHDOMDETAILNO AS LEECHDOMNO, SPECS, PACK, UNIT
, DOSAGEUNIT, CHANGERATIO, BARCODE, GMP, PRODUCTAREA
, RATIFYNO, REGISTERBRAND, OUTPATIENTUNIT, INHOSPITALUNIT, WORTH
, SOURCE, BUYPRICE, MEDICARETYPE, BID, RATIO
, WHOLESALEPRICE, USAGEDOSAGE, ENHANCERATIO, RETAILPRICE, BATCHCALCULATE
, TRADENAME AS LEECHDOMNAME, T_CODEX_DETAIL.SPELLNO, T_CODEX_DETAIL.ALIASNO, T_CODEX_DETAIL.CUSTOMNO, T_CODEX.SPELLNO AS CODENO
FROM T_CODEX, T_CODEX_DETAIL
WHERE T_CODEX.LEECHDOMNO = T_CODEX_DETAIL.LEECHDOMNO
) B, T_OFFICE C
WHERE A.LEECHDOMNO = B.LEECHDOMNO
AND A.PHARMACYID = C.OFFICEID
AND A.PHARMACYID = rec.OFFICEID
GROUP BY A.LEECHDOMNO, A.PHARMACYID, A.PUTPLACE, B.LEECHDOMNAME, B.CURRENCYNAME, B.SPECS, B.PACK, B.UNIT, B.PRODUCTAREA,
B.CHANGERATIO, B.BUYPRICE, B.WHOLESALEPRICE, B.RETAILPRICE, C.OFFICE;
ELSE
INSERT INTO MC_STORAGEINVENTORY (ID, INVENTORYDATE, INVENTORYDEPTID, LEECHDOMNO, BATCHSERIAL, BUYPRICE,WHOLESALEPRICE,RETAILPRICE, EXISTINGSTORAGE,FACTSTORAGE,PUTPLACE,
OPERATORID, AUDITINGPERSONID, MINUSVALUE, ADJUSTMARK, SUMMARK, REMARK, NOTSENDSTORAGE, NOTSENDBUYMONEY, NOTSENDRETAILMONEY)
SELECT
ROW_NUMBER() over(order by A.LEECHDOMNO) ID,
datenow INVENTORYDATE,
rec.OFFICEID INVENTORYDEPTID,
A.LEECHDOMNO LEECHDOMNO,
'' BATCHSERIAL,
B.BUYPRICE BUYPRICE,
B.WHOLESALEPRICE WHOLESALEPRICE,
B.RETAILPRICE RETAILPRICE,
A.AMOUNT AS EXISTINGSTORAGE,
A.AMOUNT AS FACTSTORAGE,
A.PUTPLACE PUTPLACE,
'自动盘点' OPERATORID,
'0634' AUDITINGPERSONID,
'0' MINUSVALUE,
'1' ADJUSTMARK, --0:未审核 1：已审核 2：已调整 3 已录入
'0' SUMMARK,
'' REMARK,
NULL NOTSENDSTORAGE,
NULL NOTSENDBUYMONEY,
NULL NOTSENDRETAILMONEY
FROM T_COUNT_STORAGE A, (SELECT CURRENCYNAME, LEECHDOMCHEMNAME, STROKENO, LEECHDOMCLASS, LEECHDOMUSE
, RECIPEHEADSHIP, DOSAGETYPE, TOXICITY, EXPLAIN, ARCHIVESTIME
, STOPTIME, LEECHDOMALIAS, CHARACTER, FUNCTIONSORT, DRUGTRUTHACTION
, FITDISEASE, TABU, ADVERT, RECIPROCITY, BADNESSFEEDBACK
, STORAGE, LEECHDOMDETAILNO AS LEECHDOMNO, SPECS, PACK, UNIT
, DOSAGEUNIT, CHANGERATIO, BARCODE, GMP, PRODUCTAREA
, RATIFYNO, REGISTERBRAND, OUTPATIENTUNIT, INHOSPITALUNIT, WORTH
, SOURCE, BUYPRICE, MEDICARETYPE, BID, RATIO
, WHOLESALEPRICE, USAGEDOSAGE, ENHANCERATIO, RETAILPRICE, BATCHCALCULATE
, TRADENAME AS LEECHDOMNAME, T_CODEX_DETAIL.SPELLNO, T_CODEX_DETAIL.ALIASNO, T_CODEX_DETAIL.CUSTOMNO, T_CODEX.SPELLNO AS CODENO
FROM T_CODEX, T_CODEX_DETAIL
WHERE T_CODEX.LEECHDOMNO = T_CODEX_DETAIL.LEECHDOMNO
AND T_CODEX.LEECHDOMNO LIKE '04%'
) B, T_OFFICE C
WHERE A.LEECHDOMNO = B.LEECHDOMNO
AND A.DRUGSTOREID = C.OFFICEID
AND A.DRUGSTOREID =rec.OFFICEID;
END IF;
END IF;
END FOR;
END;
posted on 2018-11-01 15:51 AbelAngelo 阅读(297) 评论(0) 收藏 举报
浙公网安备 33010602011771号