#-----------------------------------------------------------
#--異動区分別一時金生成条件(KV_M_IDVB_ICJKN_CRJK-KV_SELECT_001)
#--契約異動検証、一時金生成チェック。3-1(1)(j)契約異動検証異動区分一時金設定条件マスタ取得 提供方式契約取得
#-----------------------------------------------------------

--KEY:KV_SELECT_001

SELECT
    KV0021.IDVB_ICJKN_CRJK_NO,
    KV0021.TMP_PAY_PRC_NO,
    KV0021.ICJKN_BUNRUI_CD
FROM
    KV_M_IDVB_ICJKN_CRJK  KV0021
WHERE
    KV0021.IDO_DIV = ? AND
    KV0021.PRG_STAT  = ? AND
    KV0021.PRC_GRP_CD IN ( ?, '00') AND
    KV0021.PCRS_CD   = ? AND
    KV0021.OP_SVC_CD = ? AND
    KV0021.MANSION_BUKKEN_CD  = ? AND
    KV0021.TK_HOSHIKI_PATTERN_CD  = ? AND
    KV0021.TK_PRC_SBT_CD  = ? AND
    KV0021.KKTK_SBT_CD = ? AND
    KV0021.BMP_UM   = ? AND
    KV0021.IDO_RSN_CD  = ? AND
    KV0021.DNWACHO_KEISAI_CNT = ? AND
    KV0021.BUNDLE_UM  = ? AND
    ? BETWEEN KV0021.IDVB_ICJKN_CRJK_TSTAYMD AND
    KV0021.IDVB_ICJKN_CRJK_TENDYMD AND
    KV0021.MK_FLG = '0'

#-


#-----------------------------------------------------------
#--異動区分別一時金生成条件(KV_M_IDVB_ICJKN_CRJK-KV_SELECT_002)
#--契約異動検証、一時金過剰生成チェック。
#-----------------------------------------------------------

--KEY:KV_SELECT_002

SELECT DISTINCT
    KV0021.IDO_DIV,
    KV0021.PRC_GRP_CD,
    KV0021.ICJKN_BUNRUI_CD,
    NVL((SELECT ZM0171.CD_DIV_NM FROM ZM_M_CD_NM_KANRI ZM0171 WHERE ZM0171.CD_SBT_CD = 'CD00576' AND ZM0171.CD_DIV = KV0021.IDO_DIV),'') AS IDO_DIV_NM,
    NVL((SELECT KK0781.PRC_GRP_NM FROM KK_M_PRC_GRP KK0781 WHERE KK0781.PRC_GRP_CD = KV0021.PRC_GRP_CD AND KK0781.MK_FLG = '0'),'') AS PRC_GRP_NM
FROM
    KV_M_IDVB_ICJKN_CRJK KV0021
WHERE
    KV0021.TMP_PAY_PRC_NO = ? AND
    KV0021.MK_FLG = '0'

#-