CREATE  FUNCTION UPD_NEXT_EXPIRE_DT(in_info_group  INTEGER,

in_use_term  NUMBER) RETURN NUMBER AS

    CURSOR sel_auto_client IS

        SELECT CLIENT_CD, EXPIRE_DT FROM SERVICE_COMMISSION_CHARGE

        WHERE INFO_GROUP = in_info_group AND USED_STS IN (1,2) AND

        FREE_EXTENSION_FLG = 1 AND EXPIRE_DT != '02-12-31';

    out_trade_dt DATE;

    out_contract_dt DATE;

    out_expire_dt DATE;

    today_dt VARCHAR2(10);

    out_upd_count NUMBER;

BEGIN

    FOR S_REC IN sel_auto_client LOOP

        SELECT TO_CHAR(S_REC.EXPIRE_DT+1,'YYYYMMDD') INTO today_dt FROM DUAL;

        INFO_CHARGE_PKG.GET_CONTRACT_DT( in_info_group, in_info_group, today_dt,

          600, out_trade_dt, out_contract_dt );

        SELECT ADD_MONTHS(out_contract_dt, in_use_term)-1 INTO

          out_expire_dt FROM DUAL;

        UPDATE SERVICE_COMMISSION_CHARGE SET NEXT_EXPIRE_DT = out_expire_dt,

          LST_UPD_DT = SYSDATE

            WHERE INFO_GROUP = in_info_group AND CLIENT_CD = S_REC.CLIENT_CD;

        out_upd_count := sel_auto_client%ROWCOUNT;

    END LOOP;

    COMMIT;

    RETURN out_upd_count;

EXCEPTION

        WHEN OTHERS THEN

            ROLLBACK;

            raise_application_error(-20201,SQLERRM);

END UPD_NEXT_EXPIRE_DT;

/