---注册job 设置时间 必须在命令窗口内执行
/* ---注册job 设置时间 必须在命令窗口内执行
Variable N Number;
Begin
DBMS_JOB.Submit(:N,'P_SCQBFY;',Trunc(Sysdate)+23/24,'Trunc(Sysdate+1)+23/24');
Commit;
End;*/
CREATE OR REPLACE PROCEDURE "P_SCQBFY"
As
C_JFRQ ChuangWXX.JiFRQ % Type := To_Char ( Sysdate , 'yyyy-mm-dd' );
C_LRRQ JZD.LuRRQ % Type := To_Char ( Sysdate , 'yyyy-mm-dd,HH24:Mi:SS' ); --||',23:50:50';
C_KeSBM ChuangWXX.KeSBM % Type ;
C_FangH ChuangWXX.FangH % Type ;
C_ChuangH ChuangWXX.Chuangh % Type ;
C_ChuangWLB ChuangWXX.ChuangWLB % Type ;
C_ZhuYH ZYXX.ZhuYH % Type ;
ZT ZYXX.ZhuangT % Type ;
IsCWLBFY Number ( 1 );
Cursor CC_BRXX Is Select KeSBM , FangH , ChuangH , ZhuYH , JiFRQ , ChuangWLB From ChuangWXX
Where ZhuYH Is Not Null And (( JiFRQ < C_JFRQ ) Or ( JiFRQ Is Null ));
Cursor CC_FYXX Is Select * From V_MeiRJSFY
Where KeSBM = C_KeSBM And Fangh = C_FangH And ChuangH = C_ChuangH And QiY = '√' ;
Cursor CC_LBFY Is Select * From V_MeiRCWLBFY
Where LeiBBH = C_ChuangWLB And QiY = '√' ;
Begin
Select NVL ( NeiR , 0 ) Into IsCWLBFY From CanSXX Where CanSMC = '床位类别产生收费明细' ;
If IsCWLBFY Is Null Then
IsCWLBFY := 0 ;
End If ;
For B1 In CC_BRXX Loop
C_KeSBM := B1.KeSBM ;
C_FangH := B1.FangH ;
C_ChuangH := B1.ChuangH ;
C_ZhuYH := B1.ZhuyH ;
C_ChuangWLB := B1.ChuangWLB ;
Select ZhuangT Into ZT From ZYXX Where ZhuYH = B1.ZhuYH ;
If ZT < 10 then
If IsCWLBFY = 1 Then --床位类别费用
For L1 In CC_LBFY Loop
Insert Into JZD ( ZhuYH , FenL , FenL2 , BianH , BiaoZBM , MingC , GuiG , DanW , DanJ , ShuL , JinE , ChuFH , KaiFKS ,
ZhiXKS , YiJZX , LuRRQ , CaoZY , YiJTF , PiH ) Values
( C_ZhuYH , L1.FenL , L1.FenL2 , L1.BianH , L1.BiaoZBM , L1.MingC , L1.GuiG , L1.DanW , L1.DanJ ,
L1.ShuL , L1.ShuL * L1.DanJ , 1 , C_KeSBM , C_KeSBM , 0 , C_LRRQ , '0000' , 0 , C_FangH || '(' || C_ChuangH || ')' );
End Loop ;
--更改chaungwxx中的JiFRQ
Update ChuangWXX Set JiFRQ = C_JFRQ Where KeSBM = C_KeSBM And FangH = C_FangH And ChuangH = C_ChuangH ;
--更改ZYXX中的计费日期
Update ZYXX Set JiFRQ = C_JFRQ Where ZhuYH = C_ZhuYH ;
Else --床位费用
For F1 In CC_FYXX Loop
Insert Into JZD ( ZhuYH , FenL , FenL2 , BianH , BiaoZBM , MingC , GuiG , DanW , DanJ , ShuL , JinE , ChuFH , KaiFKS ,
ZhiXKS , YiJZX , LuRRQ , CaoZY , YiJTF , PiH ) Values
( C_ZhuYH , F1.FenL , F1.FenL2 , F1.BianH , F1.BiaoZBM , F1.MingC , F1.GuiG , F1.DanW , F1.DanJ ,
F1.ShuL , F1.ShuL * F1.DanJ , 1 , F1.KeSBM , F1.ZhiXKS , 0 , C_LRRQ , '0000' , 0 , C_FangH || '(' || C_ChuangH || ')' );
End Loop ;
--更改chaungwxx中的JiFRQ
Update ChuangWXX Set JiFRQ = C_JFRQ Where KeSBM = C_KeSBM And FangH = C_FangH And ChuangH = C_ChuangH ;
--更改ZYXX中的计费日期
Update ZYXX Set JiFRQ = C_JFRQ Where ZhuYH = C_ZhuYH ;
End If ;
End If ;
-- P_SCDGFR(b1.kesbm,b1.fangh,b1.chuangh,b1.zhuyh,C_lrrq,C_jfrq);
End Loop ;
--- 提交
Commit ;
/* ---注册job 设置时间
Variable N Number;
Begin
DBMS_JOB.Submit(:N,'P_SCQBFY;',Trunc(Sysdate)+23/24,'Trunc(Sysdate+1)+23/24');
Commit;
End;*/
End ;