/*------------------------------------------------------------
-- 請求契約配下契約状態判定
-- オプションサービス契約スキーマのアンロード
------------------------------------------------------------*/
SELECT
     KK0351.OP_SVC_KEI_STAT
    ,KK0351.SVC_DLRE_CD
    ,nvl(KK0351.SVC_CHRG_ENDYMD,'99999999')
    ,nvl(KK0351.SVC_ENDYMD,'99999999')
    ,KK0351.GENE_ADD_DTM
    ,'40' AS YOUSEN_JUN
    ,KK0351.OP_SVC_KEI_NO
    ,KK0351.SVC_KEI_NO
    ,KK0351.SVC_KEI_UCWK_NO
FROM
    KK_T_OP_SVC_KEI KK0351
WHERE
        KK0351.RSV_APLY_CD = '2'
    AND KK0351.RSV_APLY_YMD <= ':SHORI_YMD'
    AND KK0351.MK_FLG = '0'
ORDER BY
     KK0351.OP_SVC_KEI_NO
    ,KK0351.RSV_APLY_YMD
    ,KK0351.GENE_ADD_DTM