#-----------------------------------------------------------
#--他事業者割引契約(KK_T_TAJGS_WRIB_KEI-KK_SELECT_001)
#--指定したバリューコードに該当するカレントで有効な他事業者割引契約を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT 
    KK2441.TAJGS_WRIB_KEI_NO
    ,KK2441.GENE_ADD_DTM
    ,KK2441.TAJGS_WRIB_TK_JGS_CD
    ,KK2441.KDDI_VAL_CD
    ,KK2441.KDDI_JGS_KEI_KANRI_NO
    ,KK2441.TAJGS_WRIB_KEI_MKM_UK_YMD
    ,KK2441.TAJGS_WRIB_KEI_CNCL_YMD
    ,KK2441.TAJGS_WRIB_KEI_CNC_YMD
    ,KK2441.TAJGS_WRIB_KEI_DSL_YMD
    ,KK2441.TAJGS_TCH_YOKSI_UM
    ,KK2441.UPD_DTM
FROM 
    KK_T_TAJGS_WRIB_KEI KK2441
WHERE 
    KK2441.KDDI_VAL_CD = ?
    AND (KK2441.TAJGS_WRIB_KEI_CNCL_YMD IS NULL OR KK2441.TAJGS_WRIB_KEI_CNCL_YMD = '20991231')
    AND KK2441.GENE_ADD_DTM =  
        (SELECT MAX(KK2441_GENE.GENE_ADD_DTM) AS KK2441_MAX  
         FROM   KK_T_TAJGS_WRIB_KEI KK2441_GENE 
         WHERE  KK2441_GENE.TAJGS_WRIB_KEI_NO = KK2441.TAJGS_WRIB_KEI_NO 
         AND    KK2441_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--他事業者割引契約(KK_T_TAJGS_WRIB_KEI-KK_SELECT_002)
#--【保存期間経過データ物理削除】
#--削除対象の申込明細番号を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT /*+ INDEX (KK2441 KK_T_TAJGS_WRIB_KEI_PK_01) */
    DISTINCT
    KK2441.MSKM_DTL_NO
FROM
    KK_T_TAJGS_WRIB_KEI KK2441
WHERE
    KK2441.TAJGS_WRIB_KEI_NO = ?
AND KK2441.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--他事業者割引契約(KK_T_TAJGS_WRIB_KEI-KK_SELECT_003)
#--指定したサービス契約番号に紐付くカレントで有効な他事業者割引契約を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_003

SELECT
    KK2441.TAJGS_WRIB_KEI_NO
    ,KK2441.GENE_ADD_DTM
    ,KK2441.TAJGS_WRIB_TK_JGS_CD
    ,KK2441.KDDI_VAL_CD
    ,KK2441.KDDI_JGS_KEI_KANRI_NO
    ,KK2441.TAJGS_WRIB_KEI_MKM_UK_YMD
    ,KK2441.TAJGS_WRIB_KEI_CNCL_YMD
    ,KK2441.TAJGS_WRIB_KEI_CNC_YMD
    ,KK2441.TAJGS_WRIB_KEI_DSL_YMD
    ,KK2441.TAJGS_TCH_YOKSI_UM
    ,KK2441.UPD_DTM
FROM KK_T_TAJGS_WRIB_KEI KK2441
INNER JOIN 
     KK_T_TAJGSWKEI_TGKEI KK2451
  ON KK2451.TAJGS_WRIB_KEI_NO = KK2441.TAJGS_WRIB_KEI_NO
WHERE
 (KK2441.TAJGS_WRIB_KEI_CNCL_YMD IS NULL OR KK2441.TAJGS_WRIB_KEI_CNCL_YMD = '20991231')
  AND KK2441.GENE_ADD_DTM =
       (SELECT MAX(KK2441_GENE.GENE_ADD_DTM) AS KK2441_MAX
        FROM KK_T_TAJGS_WRIB_KEI KK2441_GENE
        WHERE KK2441_GENE.TAJGS_WRIB_KEI_NO = KK2441.TAJGS_WRIB_KEI_NO
          AND KK2441_GENE.MK_FLG = '0' )
  AND KK2451.SVC_KEI_NO = ?
  AND KK2451.TAJGSWKEI_TGKEI_TSTAYMD <= ?
  AND KK2451.TAJGSWKEI_TGKEI_TENDYMD >= ?
  AND KK2451.GENE_ADD_DTM =
       (SELECT MAX(KK2451_GENE.GENE_ADD_DTM) AS KK2451_MAX
          FROM KK_T_TAJGSWKEI_TGKEI KK2451_GENE
          WHERE KK2451_GENE.TAJGS_WRIB_KEI_NO = KK2451.TAJGS_WRIB_KEI_NO
          AND KK2451_GENE.TAJGSWKEI_TGKEI_NO = KK2451.TAJGSWKEI_TGKEI_NO
          AND KK2451_GENE.MK_FLG = '0' )

#-

#-----------------------------------------------------------
#--他事業者割引契約(KK_T_TAJGS_WRIB_KEI-KK_SELECT_004)
#--指定した他事業者割引契約番号に紐付くカレントで有効な他事業者割引契約を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_004

SELECT
    KK2441.TAJGS_WRIB_KEI_NO
    ,KK2441.GENE_ADD_DTM
    ,KK2441.TAJGS_WRIB_TK_JGS_CD
    ,KK2441.KDDI_VAL_CD
    ,KK2441.KDDI_JGS_KEI_KANRI_NO
    ,KK2441.TAJGS_WRIB_KEI_MKM_UK_YMD
    ,KK2441.TAJGS_WRIB_KEI_CNCL_YMD
    ,KK2441.TAJGS_WRIB_KEI_CNC_YMD
    ,KK2441.TAJGS_WRIB_KEI_DSL_YMD
    ,KK2441.TAJGS_TCH_YOKSI_UM
    ,KK2451.TAJGSWKEI_TGKEI_NO
    ,KK2451.GENE_ADD_DTM
    ,KK2451.DSL_TAJGS_TCH_YMD
    ,KK2451.PPCHG_TAJGS_TCH_YMD
FROM KK_T_TAJGS_WRIB_KEI KK2441
INNER JOIN 
     KK_T_TAJGSWKEI_TGKEI KK2451
  ON KK2451.TAJGS_WRIB_KEI_NO = KK2441.TAJGS_WRIB_KEI_NO
WHERE
  KK2441.TAJGS_WRIB_KEI_NO = ?
  AND (KK2441.TAJGS_WRIB_KEI_NO, KK2441.GENE_ADD_DTM) =
        (SELECT KK2441_GENE.TAJGS_WRIB_KEI_NO, MAX(KK2441_GENE.GENE_ADD_DTM)
         FROM   KK_T_TAJGS_WRIB_KEI KK2441_GENE
         WHERE  KK2441_GENE.TAJGS_WRIB_KEI_NO = KK2441.TAJGS_WRIB_KEI_NO
         AND    KK2441_GENE.MK_FLG = '0'
         GROUP BY KK2441_GENE.TAJGS_WRIB_KEI_NO)
  AND (KK2451.TAJGS_WRIB_KEI_NO, KK2451.TAJGSWKEI_TGKEI_NO, KK2451.GENE_ADD_DTM) IN 
        (SELECT KK2451_GENE.TAJGS_WRIB_KEI_NO, KK2451_GENE.TAJGSWKEI_TGKEI_NO, MAX(KK2451_GENE.GENE_ADD_DTM) 
         FROM   KK_T_TAJGSWKEI_TGKEI KK2451_GENE
         WHERE  KK2451_GENE.TAJGS_WRIB_KEI_NO = KK2451.TAJGS_WRIB_KEI_NO
         AND    KK2451_GENE.TAJGSWKEI_TGKEI_NO = KK2451.TAJGSWKEI_TGKEI_NO
         AND    KK2451_GENE.MK_FLG = '0'
         GROUP BY KK2451_GENE.TAJGS_WRIB_KEI_NO, KK2451_GENE.TAJGSWKEI_TGKEI_NO)
  AND KK2451.TAJGSWKEI_TGKEI_TSTAYMD <= ?
  AND KK2451.TAJGSWKEI_TGKEI_TENDYMD >= ?

#-