Oracle_FUNCTION ——getceflag

CREATE OR REPLACE FUNCTION getceflag
(
       P_PK_PSNDOC IN CHAR,
       P_YEAR IN CHAR,
       P_PERIOD IN CHAR,
       P_PK_ORG IN CHAR
)
RETURN NUMBER IS
       P_YEARPERIOD CHAR(6) := P_YEAR || P_PERIOD;
       C_YEARPERIOD CHAR(6);
       result NUMBER(31,0);
BEGIN
       result := -1;
       
       /*
       select (case when nvl(cyearperiod,'N/A') = 'N/A' then -1 
              when P_YEARPERIOD = cyearperiod then 0
              when P_YEARPERIOD > cyearperiod then 1
              when P_YEARPERIOD < cyearperiod then -1
              else -1 end) into result*/
       select cyearperiod into C_YEARPERIOD
         from (select pk_wa_data from wa_data_temp where rownum=1) tempdata 
    left join (select da.cyearperiod,rownum rowno from (select wdtemp.cyearperiod
                 from wa_data_temp wdtemp 
                where wdtemp.pk_psndoc = (select pk_psndoc from bd_psndoc where code = P_PK_PSNDOC and nvl(dr,0)=0 and rownum = 1)
                  and wdtemp.Pk_Org = (select pk_org from org_orgs where code = P_PK_ORG and nvl(dr,0)=0 and rownum = 1)
                  and wdtemp.cyear = P_YEAR
                  and wdtemp.cperiod < P_PERIOD
                  and (wdtemp.f_310 - wdtemp.f_308) > 0 --  累计奖金 - 4倍投保金额
                  and wdtemp.checkflag = 'Y'
                  and nvl(wdtemp.dr, 0) = 0
             order by wdtemp.cyearperiod asc) da) temp on 1=1 where nvl(temp.rowno,1) = 1;




       if(nvl(C_YEARPERIOD,'N/A') = C_YEARPERIOD) then
             if(P_YEARPERIOD = C_YEARPERIOD) then
                    result := 0;
             elsif(P_YEARPERIOD > C_YEARPERIOD) then
                     result := 1;
             elsif(P_YEARPERIOD < C_YEARPERIOD) then
                     result := -1;
             end if;
       else
             result := -1;
       end if;


       RETURN result;
END;