create or replace procedure EZ_CLTJ(yf_IN CLSYJLB.Yf%Type) is
s_ks CLSYJLB.SYRSSKS%TYPE; --變量科室
i_gls CLSYJLB.Gls%TYPE; --變量本月使用公里數
i_jygl KSSYTJB.DYJY%TYPE; --變量本月結余公里
i_ljjy KSSYTJB.Ljjy%TYPE; --變量累計結余公里
i_syjygl KSSYTJB.DYJY%TYPE; --變量上月結余公里
i_edgl KSYCCSXXB.MYEDSYGL%TYPE; --變量額定使用公里
i_count integer; --計算行數
i_Newcount integer; --計算行數
i_Scount integer; --計算行數
i_syf CLSYJLB.Yf%Type; --變量上月份
s_yf CLSYJLB.Yf%Type; --變量本月份
s_myf CLSYJLB.Yf%Type; --變量最小月份
s_flag Boolean; --標識符
CURSOR c_ks IS
select KSYCCSXXB.KSMC, KSYCCSXXB.MYEDSYGL from KSYCCSXXB;
begin
OPEN c_ks;
LOOP
FETCH c_ks
into s_ks, i_edgl;
exit when c_ks%Notfound;
s_flag := true;
select SUM(CLSYJLB.GLS) --統計當月科室使用公里數
INTO i_gls
from CLSYJLB
where CLSYJLB.SYRSSKS = s_ks
and CLSYJLB.YF = yf_IN;
i_gls := Nvl(i_gls, 0);
i_jygl := i_edgl - i_gls;
if substr(yf_IN, 5, 2) = '01' then
--判斷上月份
i_syf := to_char(to_number(substr(yf_IN, 1, 4)) - 1) + '12';
else
i_syf := to_char(to_number(yf_IN) - 1);
end if;
select min(KSSYTJB.Yf)
into s_myf
from KSSYTJB
where KSSYTJB.KSMC = s_ks;
select count(*)
into i_Scount
from KSSYTJB
where KSSYTJB.Ksmc = s_ks
and KSSYTJB.YF = i_syf;
if yf_IN < s_myf or i_Scount <= 0 then
s_flag := false;
end if;
select count(*) into i_count from KSSYTJB where KSSYTJB.Ksmc = s_ks; --判斷初始情況以及結余公里
If i_count <= 0 Then
--情況一，完全初始化的結余公里
select KSYCCSXXB.CSJY
into i_ljjy
from KSYCCSXXB
where KSYCCSXXB.Ksmc = s_ks;
i_ljjy := Nvl(i_ljjy, 0) + Nvl(i_jygl, 0);
s_flag := true;
end if;
if i_count = 1 then
--情況二，有一條統計記錄
select KSSYTJB.Ljjy, KSSYTJB.Yf
into i_syjygl, s_yf
from KSSYTJB
where KSSYTJB.Ksmc = s_ks;
if s_yf <> yf_IN then
--情況二-1，有一條不是本月的統計記錄
select KSSYTJB.LJJY, KSSYTJB.Yf
into i_ljjy, s_myf
from KSSYTJB
where KSSYTJB.Ksmc = s_ks;
if s_myf > yf_IN then
i_ljjy := Nvl(i_ljjy, 0) + Nvl(i_jygl, 0);
s_flag := true;
end if;
else
--情況二-2，有一條是本月的統計記錄
select KSYCCSXXB.CSJY
into i_ljjy
from KSYCCSXXB
where KSYCCSXXB.Ksmc = s_ks;
i_ljjy := Nvl(i_ljjy, 0) + Nvl(i_jygl, 0);
s_flag := true;
end if;
end if;
if i_count > 1 and s_flag = true then
--情況三，有多條是本月的統計記錄
select KSSYTJB.LJJY
into i_ljjy
from KSSYTJB |
|
資源大小：2.30 KB 
