#-----------------------------------------------------------
#--法人連絡先(CK_T_HOJIN_RRKS-CH_SELECT_001)
#--債権回収会社委託対象者抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_001

SELECT CK0041.HJRRKS_NM
      ,CK0041.HJRRKS_TNTSHA_SHOZKBSHO_NM
FROM CK_T_HOJIN_RRKS CK0041
     INNER JOIN
               (SELECT
                       CK0031.SYSID
                FROM
                       CK_T_CUST_HOJIN CK0031
                WHERE
                       CK0031.SYSID = ?
                  AND  GENE_ADD_DTM = ?
          ) CK0031_1
      ON CK0041.SYSID = CK0031_1.SYSID
      INNER JOIN
                (SELECT
                        CK0041_GENE.SYSID
                      , MAX(CK0041_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM
                 FROM
                        CK_T_HOJIN_RRKS CK0041_GENE
                 WHERE
                        CK0041_GENE.MK_FLG       =  '0'
                 GROUP BY
                        CK0041_GENE.SYSID
                ) CK0041_GENE
        ON  CK0041.SYSID        = CK0041_GENE.SYSID
       AND  CK0041.GENE_ADD_DTM = CK0041_GENE.MAX_GENE_DTM
WHERE
    CK0041.RRKS_CD = '01'

#-

#-------------------TV-START--------------------------------
#-----------------------------------------------------------
#--法人連絡先(CK_T_HOJIN_RRKS-KK_SELECT_001)
#--法人連絡先情報取得
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
 HOJIN_RRK.HJRRKS_PCD,
 HOJIN_RRK.HJRRKS_STATE_NM,
 HOJIN_RRK.HJRRKS_CITY_NM,
 HOJIN_RRK.HJRRKS_OAZTSU_NM,
 HOJIN_RRK.HJRRKS_AZCHO_NM,
 HOJIN_RRK.HJRRKS_BNCHIGO,
 HOJIN_RRK.HJRRKS_ADRTTM,
 HOJIN_RRK.HJRRKS_ADRRM
FROM
 CK_T_HOJIN_RRKS HOJIN_RRK
WHERE
 HOJIN_RRK.SYSID = ?
AND
 HOJIN_RRK.GENE_ADD_DTM = (
  SELECT
     MAX(GENE.GENE_ADD_DTM) AS GENE_MAX 
  FROM
     CK_T_HOJIN_RRKS GENE
  WHERE
     GENE.SYSID = HOJIN_RRK.SYSID
  AND
     GENE.RRKS_CD = HOJIN_RRK.RRKS_CD
  AND
     GENE.MK_FLG = '0')
AND
 HOJIN_RRK.RRKS_CD = '01'

#-

#-----------------------------------------------------------
#--法人連絡先(CK_T_HOJIN_RRKS-KK_SELECT_002)
#--KKPRC02403
#--ＫＣＮ連携契約情報ファイル１のSYSIDに紐づく、お客様法人の連絡先情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT 
  CK0041.HJRRKS_TNTSHA_NM,
  CK0041.HJRRKS_TNTSHA_KANA
  FROM
  CK_T_RRKS CK0201
    INNER JOIN CK_T_HOJIN_RRKS CK0041 ON
 CK0201.SYSID = CK0041.SYSID
   AND CK0201.RRKS_CD = CK0041.RRKS_CD
   AND CK0041.GENE_ADD_DTM =
         (SELECT MAX(CK0041_GENE.GENE_ADD_DTM) AS CK0041_MAX
          FROM   CK_T_HOJIN_RRKS CK0041_GENE
          WHERE  CK0041_GENE.SYSID = CK0041.SYSID 
          AND    CK0041_GENE.RRKS_CD = CK0041.RRKS_CD
          AND    CK0041_GENE.MK_FLG = '0')
  WHERE
   CK0201.SYSID = ?
     AND CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM =
         (SELECT MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
          FROM   
          CK_T_RRKS CK0201_GENE
          WHERE
            CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
          AND    CK0201_GENE.RSV_APLY_YMD <= ?
          AND    CK0201_GENE.RSV_APLY_CD = '2'
          AND    CK0201_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--法人連絡先(CK_T_HOJIN_RRKS-KK_SELECT_004)
#--KKPRC41301
#--法人連絡先情報取得
#-----------------------------------------------------------

--KEY:KK_SELECT_004

SELECT
    CK0041.SYSID,
    CK0041.RRKS_CD,
    CK0041.GENE_ADD_DTM,
    CK0041.HJRRKS_NM,
    CK0041.HJRRKS_AD_CD,
    CK0041.HJRRKS_PCD,
    CK0041.HJRRKS_STATE_NM,
    CK0041.HJRRKS_CITY_NM,
    CK0041.HJRRKS_OAZTSU_NM,
    CK0041.HJRRKS_AZCHO_NM,
    CK0041.HJRRKS_BNCHIGO,
    CK0041.HJRRKS_ADRTTM,
    CK0041.HJRRKS_ADRRM,
    CK0041.HJRRKS_TNTSHA_NM,
    CK0041.HJRRKS_TNTSHA_KANA,
    CK0041.HJRRKS_TNTSHA_SHOZKBSHO_NM
FROM
    CK_T_HOJIN_RRKS CK0041
WHERE
    CK0041.SYSID = ?
    AND CK0041.RRKS_CD = '01'
    AND CK0041.GENE_ADD_DTM = 
                    (SELECT MAX(CK0041_GENE.GENE_ADD_DTM) AS CK0041_MAX
                        FROM   CK_T_HOJIN_RRKS CK0041_GENE
                        WHERE  CK0041_GENE.SYSID = CK0041.SYSID 
                        AND    CK0041_GENE.RRKS_CD = CK0041.RRKS_CD
                        AND    CK0041_GENE.MK_FLG = '0')

#-

#-------------------TV-END----------------------------------

#-----------------------------------------------------------
#--法人連絡先(CK_T_HOJIN_RRKS-KK_SELECT_003)
#--契約情報ファイル作成のための、法人連絡先と連絡先の情報を抽出する。
#-----------------------------------------------------------

--KEY:KK_SELECT_003

SELECT
     CTHR.HJRRKS_TNTSHA_NM
    ,CTHR.HJRRKS_TNTSHA_KANA
    ,TRIM(CTR.MLAD) MLAD
    ,CTR.TELNO
FROM
    CK_T_HOJIN_RRKS CTHR
INNER JOIN
    CK_T_RRKS CTR
ON (
    CTHR.SYSID = CTR.SYSID
AND
    CTHR.RRKS_CD = CTR.RRKS_CD
AND
    CTHR.RRKS_CD = '01'
)
WHERE
    CTHR.SYSID = ?
AND
    CTHR.GENE_ADD_DTM = (
        SELECT
            MAX(CTHR_GENE.GENE_ADD_DTM) AS CTHR_MAX
        FROM
            CK_T_HOJIN_RRKS CTHR_GENE
        WHERE
            CTHR_GENE.SYSID = CTHR.SYSID
        AND
            CTHR_GENE.RRKS_CD = CTHR.RRKS_CD
        AND
            CTHR_GENE.MK_FLG = '0'
    )
AND
    CTR.RSV_APLY_YMD || CTR.GENE_ADD_DTM = (
        SELECT
            MAX(CTR_GENE.RSV_APLY_YMD || CTR_GENE.GENE_ADD_DTM) AS CTR_MAX
        FROM
            CK_T_RRKS CTR_GENE
        WHERE
            CTR_GENE.RRKS_NO = CTR.RRKS_NO
        AND
            CTR_GENE.RSV_APLY_YMD <= ?
        AND
            CTR_GENE.RSV_APLY_CD = '2'
        AND
            CTR_GENE.MK_FLG = '0')

#-