#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-CR_SELECT_001)
#--履歴情報取込_問合せ客存在チェック
#--・履歴情報インポート
#-----------------------------------------------------------

--KEY:CR_SELECT_001

SELECT
 COUNT(*) AS CNT
FROM
 CK_T_PROSCST PROSCST
WHERE
 PROSCST.PROSCST_NO = ?
 AND PROSCST.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-CR_SELECT_002)
#--問合せ客メールアドレスから問合せ客番号を取得する。
#--・問合せメール取込
#-----------------------------------------------------------

--KEY:CR_SELECT_002

SELECT
  PCST.PROSCST_NO,
  PCST.PROSCST_NM,
  PCST.PROSCST_KANA,
  PCST.PROSCST_TELNO
FROM
  CK_T_PROSCST PCST
WHERE
  (
    PCST.PROSCST_MLAD = ?
    OR PCST.SKK_AD = ?
    OR PCST.SKK_AD_2 = ?
    OR PCST.SKK_AD_3 = ?
  )
  AND PCST.MK_FLG = '0'
ORDER BY
  PCST.UPD_DTM DESC

#-

#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-CR_SELECT_003)
#--申込明細の申込連絡先メールアドレスを検索して、申込書番号に紐付く問合せ客番号を取得する。
#--・問合せメール取込
#-----------------------------------------------------------

--KEY:CR_SELECT_003

SELECT
  PCST.PROSCST_NO,
  PCST.PROSCST_NM,
  PCST.PROSCST_KANA,
  PCST.PROSCST_TELNO
FROM
  CK_T_PROSCST PCST
WHERE
  PCST.CUST_TOKUT_MSKMSHO_NO IN (
                                  SELECT
                                    MKDL.MSKMSHO_NO
                                  FROM
                                    KK_T_MSKM_DTL MKDL
                                  WHERE
                                    MKDL.MSKM_RRKS_MLAD = ?
                                    AND MKDL.GENE_ADD_DTM =
                                        (
                                          SELECT 
                                            MAX(MKDL_GENE.GENE_ADD_DTM) AS MKDL_MAX
                                          FROM
                                            KK_T_MSKM_DTL MKDL_GENE
                                          WHERE
                                            MKDL_GENE.MSKM_DTL_NO = MKDL.MSKM_DTL_NO
                                            AND MKDL_GENE.MK_FLG = '0'
                                        )
                                 )
  AND PCST.MK_FLG = '0'
ORDER BY
  PCST.UPD_DTM DESC

#-

#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-CR_SELECT_004)
#--問合せ客存在確認
#--・問合せメール取込
#-----------------------------------------------------------

--KEY:CR_SELECT_004

SELECT
  COUNT(*) AS CNT
FROM
  CK_T_PROSCST PCST
WHERE
  PCST.PROSCST_NO = ?
  AND PCST.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-CR_SELECT_005)
#--既加入問合せ客抽出
#--・問合せ顧客紐付変更処理
#-----------------------------------------------------------

--KEY:CR_SELECT_005

SELECT
  /*+ index(TKRK CR_T_TAIO_KIROK_IDX_10) */
  CUST5.PROSCST_NO,
  CUST5.PROSCST_NM,
  CUST5.DEL_TRGT_GAI_FLG,
  CUST5.SKK_AD,
  CUST5.PROSCST_HSK_BIKO,
  CUST5.SYSID,
  MIN(CUST5.MSKM_DTL_NO) AS MSKM_DTL_NO
FROM
  CR_T_TAIO_KIROK TKRK,
  (
    SELECT
      /*+ index(PCST CK_T_PROSCST_PK_01) */
      PCST.PROSCST_NO,
      PCST.PROSCST_NM,
      PCST.DEL_TRGT_GAI_FLG,
      PCST.SKK_AD,
      PCST.PROSCST_HSK_BIKO,
      CUST4.SYSID,
      CUST4.MSKM_DTL_NO
    FROM
      CK_T_PROSCST PCST,
      (
        SELECT
          CUST3.SYSID,
          CUST3.CUST_KANA,
          MKDL4.PROSCST_NO,
          MKDL4.MSKM_DTL_NO
        FROM
          (
            SELECT
              /*+ index(CUST CK_T_CUST_PK_01) */
              CUST.SYSID,
              CASE WHEN CUST.MEMBER_SBT_CD IN ('11', '21', '30') THEN CUST.CONT_CUST_KANA
              ELSE
                CASE WHEN (CUST.MMB_SBT_TOKUT_FLG = '0' AND CUST.EONETID_MI_IKO_FLG = '1') THEN CUST.CONT_CUST_KANA
                ELSE CUST.CUST_KANA
                END
              END AS CUST_KANA
            FROM
             CK_T_CUST CUST,
             (
               SELECT
                 CUST_GENE.SYSID,
                 MAX(CUST_GENE.RSV_APLY_YMD || CUST_GENE.GENE_ADD_DTM) AS CUST_MAX
               FROM
                 CK_T_CUST CUST_GENE
               WHERE
                 CUST_GENE.RSV_APLY_YMD <= ?
                 AND CUST_GENE.RSV_APLY_CD = '2'
                 AND CUST_GENE.MK_FLG = '0'
               GROUP BY
                 CUST_GENE.SYSID
              ) CUST_GENE2
            WHERE
              CUST.SYSID = CUST_GENE2.SYSID
              AND CUST.RSV_APLY_YMD || CUST.GENE_ADD_DTM = CUST_GENE2.CUST_MAX
              AND (CUST.KEISHA_TYPE_CD <> '5' OR CUST.KEISHA_TYPE_CD IS NULL)
          ) CUST3,
          (
            SELECT
              /*+ index(MKDL3 KK_T_MSKM_DTL_IDX_02) */
              MKDL3.MSKM_DTL_NO,
              MKDL3.SYSID,
              MKDL3.MSKMSHO_NO,
              PCST.PROSCST_NO
            FROM
              CK_T_PROSCST PCST,
              (
                SELECT
                  /*+ index(MKDL KK_T_MSKM_DTL_PK_01) */
                  MKDL.MSKM_DTL_NO,
                  MKDL.SYSID,
                  MKDL.MSKMSHO_NO
                FROM
                  KK_T_MSKM_DTL MKDL
                WHERE
                  (MKDL.MSKM_DTL_NO, MKDL.GENE_ADD_DTM) IN
                  (
                    SELECT
                      MKDL_SUB.MSKM_DTL_NO,
                      MAX(MKDL_SUB.GENE_ADD_DTM) AS GENE_ADD_DTM
                    FROM
                      KK_T_MSKM_DTL MKDL_SUB
                    WHERE
                      MKDL_SUB.MK_FLG = '0'
                    GROUP BY
                      MKDL_SUB.MSKM_DTL_NO
                  )
              ) MKDL3
            WHERE
              PCST.CUST_TOKUT_MSKMSHO_NO = MKDL3.MSKMSHO_NO
              AND PCST.CUST_TOKUT_MSKMSHO_NO IS NOT NULL
              AND PCST.MK_FLG = '0'
          ) MKDL4
        WHERE
          CUST3.SYSID = MKDL4.SYSID
      ) CUST4
    WHERE
      PCST.PROSCST_NO = CUST4.PROSCST_NO
      AND PCST.PROSCST_KANA = CUST4.CUST_KANA
      AND PCST.MK_FLG = '0'
  ) CUST5
WHERE
  TKRK.PROSCST_NO = CUST5.PROSCST_NO
  AND TKRK.PROSCST_NO IS NOT NULL
  AND TKRK.UPD_DTM > ?
  AND TKRK.MK_FLG = '0'
GROUP BY
  CUST5.PROSCST_NO,
  CUST5.PROSCST_NM,
  CUST5.DEL_TRGT_GAI_FLG,
  CUST5.SKK_AD,
  CUST5.PROSCST_HSK_BIKO,
  CUST5.SYSID
ORDER BY
  CUST5.PROSCST_NO

#-

#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-CR_SELECT_006)
#--問合せ客番号から問合せ客住所、性別、生年月日を取得
#--・応対区分抽出
#-----------------------------------------------------------

--KEY:CR_SELECT_006

SELECT 
  PCST.PROSCST_PCD,
  PCST.PROSCST_STATE_NM,
  PCST.PROSCST_CITY_NM,
  PCST.PROSCST_ADRTTM,
  NM_KANRI.CD_DIV_NM,
  PCST.PROSCST_BIRTHD
FROM
  CK_T_PROSCST PCST
  LEFT OUTER JOIN ZM_M_CD_NM_KANRI NM_KANRI
  ON  PCST.PROSCST_SEX_CD = NM_KANRI.CD_DIV
  AND NM_KANRI.CD_SBT_CD = 'CD00245'
  AND NM_KANRI.MK_FLG = '0'
WHERE
  PCST.PROSCST_NO = ?
  AND PCST.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-CR_UPDATE_001)
#--処理対象問合せ客更新
#--・未加入問合せ情報削除
#-----------------------------------------------------------

--KEY:CR_UPDATE_001

UPDATE CK_T_PROSCST PCST
SET
  UPD_DTM = ?,
  UPD_UNYO_YMD = 'UPD_UNYO',
  UPD_TRN_ID = 'UPD_TRN',
  UPD_OPEACNT = ?,
  MK_FLG = '2'
WHERE
  (
   PROSCST_NO IN (
    SELECT
      TKRK_SUB.PROSCST_NO
    FROM (SELECT
              TKRK.PROSCST_NO,
              TKRK.UPD_DTM,
              RANK() OVER (PARTITION BY TKRK.PROSCST_NO
                     ORDER BY TKRK.UPD_DTM DESC) AS TKRK_RANK
          FROM
              CR_T_TAIO_KIROK TKRK
              INNER JOIN CK_T_PROSCST PCST
              ON TKRK.PROSCST_NO = PCST.PROSCST_NO
          WHERE
              PCST.PROSCST_NO > '0000009999'
          AND PCST.DEL_TRGT_GAI_FLG <> '1'
          AND PCST.MK_FLG = '0'
          AND TKRK.MK_FLG = '0') TKRK_SUB
    WHERE
          TKRK_SUB.TKRK_RANK = 1
      AND TKRK_SUB.UPD_DTM < ?
      AND NOT EXISTS (
        SELECT
          1
        FROM
          CR_T_TAIO_KIROK TKRK
        WHERE
          TKRK.PROSCST_NO = TKRK_SUB.PROSCST_NO
          AND TKRK.TOIAWASE_STAT_CD <> '103'
          AND TKRK.TOIAWASE_STAT_CD <> '104'
          AND TKRK.MK_FLG = '0'
    )
   )
   AND PCST.MK_FLG = '0'
  )
  OR
  (
    PCST.UPD_DTM < ?
    AND PCST.PROSCST_NO > '0000009999'
    AND PCST.DEL_TRGT_GAI_FLG <> '1'
    AND PCST.MK_FLG = '0'
    AND NOT EXISTS
    (
       SELECT
         1
       FROM
         CR_T_TAIO_KIROK TKRK
       WHERE
         TKRK.PROSCST_NO = PCST.PROSCST_NO
         AND TKRK.MK_FLG = '0'
     )
  )

#-

#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-CR_UPDATE_002)
#--問合せ客論理削除
#--・未加入問合せ情報削除
#-----------------------------------------------------------

--KEY:CR_UPDATE_002

UPDATE CK_T_PROSCST
SET
  UPD_DTM = ?,
  UPD_UNYO_YMD = 'UPD_UNYO',
  UPD_TRN_ID = 'UPD_TRN',
  UPD_OPEACNT = ?,
  DEL_DTM = ?,
  DEL_UNYO_YMD = 'DEL_UNYO',
  DEL_TRN_ID = 'DEL_TRN',
  DEL_OPEACNT = ?,
  MK_FLG = '1'
WHERE
  MK_FLG = '2'

#-

#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-CR_SELECT_007)
#--削除対象問合せ客退避
#--・未加入問合せ情報削除
#-----------------------------------------------------------

--KEY:CR_SELECT_007

SELECT
  PCST.PROSCST_NO
FROM
  CK_T_PROSCST PCST
WHERE
  PCST.MK_FLG = '2'
ORDER BY
  PCST.PROSCST_NO

#-

#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-CR_SELECT_008)
#--紐付対象問合せ客抽出
#--・問合せ顧客紐付変更処理
#-----------------------------------------------------------

--KEY:CR_SELECT_008

SELECT
  PCST.PROSCST_NM,
  PCST.DEL_TRGT_GAI_FLG,
  PCST.SKK_AD,
  PCST.SKK_AD_2,
  PCST.SKK_AD_3,
  PCST.PROSCST_HSK_BIKO
FROM
  CK_T_PROSCST PCST
WHERE
  PCST.PROSCST_NO = ?
  AND PCST.MK_FLG = '0'
  AND EXISTS (
               SELECT
                 1
               FROM
                 CR_T_TAIO_KIROK TKRK
               WHERE
                 TKRK.PROSCST_NO = PCST.PROSCST_NO
                 AND TKRK.UPD_DTM > ?
                 AND TKRK.MK_FLG = '0'
             )

#-

#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-KK_SELECT_001)
#--問合せ客の更新運用年月日が指定された年月日より後のデータを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
    PROSCST.PROSCST_TELNO,
    PROSCST.PROSCST_KTAI_TELNO,
    PROSCST.PROSCST_NM,
    PROSCST.PROSCST_KANA,
    PROSCST.PROSCST_NO,
    MSKM_DTL_SYSID.SYSID,
    CUST.EOID
FROM
    CK_T_PROSCST PROSCST
LEFT JOIN
    (SELECT 
       MSKM_DTL.MSKMSHO_NO,
       MSKM_DTL.SYSID
     FROM KK_T_MSKM_DTL MSKM_DTL
     WHERE
      MSKM_DTL.GENE_ADD_DTM =
       (SELECT MAX(KK_T_MSKM_DTL_RCNT.GENE_ADD_DTM) AS MSKM_DTL_MAX
        FROM    KK_T_MSKM_DTL KK_T_MSKM_DTL_RCNT
        WHERE   KK_T_MSKM_DTL_RCNT.MSKMSHO_NO = MSKM_DTL.MSKMSHO_NO
        AND     KK_T_MSKM_DTL_RCNT.MK_FLG = '0')
     ) MSKM_DTL_SYSID
ON MSKM_DTL_SYSID.MSKMSHO_NO = PROSCST.CUST_TOKUT_MSKMSHO_NO
LEFT JOIN
    CK_T_CUST CUST
ON
    MSKM_DTL_SYSID.SYSID = CUST.SYSID
WHERE
    PROSCST.UPD_UNYO_YMD >= ?
AND
    PROSCST.MK_FLG = '0'
AND
    CUST.MEMBER_SBT_CD IN ('00', '11', '21', '30')
AND
    CUST.RSV_APLY_YMD || CUST.GENE_ADD_DTM =
        (SELECT MAX(CK_T_CUST_RCNT.RSV_APLY_YMD || CK_T_CUST_RCNT.GENE_ADD_DTM) AS CK_T_CUST_MAX
        FROM    CK_T_CUST CK_T_CUST_RCNT
        WHERE   CK_T_CUST_RCNT.SYSID = CUST.SYSID
        AND     CK_T_CUST_RCNT.RSV_APLY_YMD <= ?
        AND     CK_T_CUST_RCNT.RSV_APLY_CD = '2'
        AND     CK_T_CUST_RCNT.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-KK_SELECT_002)
#--【廃止住所データ取得】
#--問合せ客番号、問合せ客住所コードを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT
    CK0051.PROSCST_NO,
    CK0051.PROSCST_AD_CD
FROM
    CK_T_PROSCST CK0051
WHERE
	CK0051.PROSCST_AD_CD = ?
AND CK0051.MK_FLG ='0'

#-

#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-KK_SELECT_003)
#--【変更住所データ取得】
#--問合せ客住所コードを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_003

SELECT
    CK0051.PROSCST_NO,
    CK0051.PROSCST_AD_CD,
    CK0051.PROSCST_PCD,
    CK0051.PROSCST_STATE_NM,
    CK0051.PROSCST_CITY_NM,
    CK0051.PROSCST_OAZTSU_NM,
    CK0051.PROSCST_AZCHO_NM,
    CK0051.PROSCST_BNCHIGO,
    KK2661.AD_CD,
    KK2661.PCD,
    KK2661.STATE_NM,
    KK2661.CITY_NM,
    KK2661.OAZTSU_NM,
    KK2661.AZCHO_NM,
    KK2661.AD_SHUSEI_CD,
    KK2661.NEW_AD_CD,
    KK2661.NEW_PCD,
    KK2661.NEW_STATE_NM,
    KK2661.NEW_CITY_NM,
    KK2661.NEW_OAZTSU_NM,
    KK2661.NEW_AZCHO_NM,
    KK2661.AD_CHG_SBT_CD
FROM  CK_T_PROSCST CK0051
    INNER JOIN
        KK_T_CHGTGAD_CD_WK KK2661
    ON
        CK0051.PROSCST_AD_CD = KK2661.AD_CD
    AND KK2661.CKADGAI_CHG_AD_TRN_STAT_CD = '0'
WHERE 
	CK0051.PROSCST_AD_CD = ?
    AND CK0051.MK_FLG ='0'
    AND KK2661.MK_FLG ='0'

#-


#-----------------------------------------------------------
#--問合せ客(CK_T_PROSCST-KK_UPDATE_002)
#-----------------------------------------------------------

--KEY:KK_UPDATE_002

UPDATE
    CK_T_PROSCST
SET
    PROSCST_AD_CD = ?,
    PROSCST_PCD = ?,
    PROSCST_STATE_NM = ?,
    PROSCST_CITY_NM = ?,
    PROSCST_OAZTSU_NM = ?,
    PROSCST_AZCHO_NM = ?,
    UPD_DTM = 'UPD_DTM',
    UPD_OPEACNT = 'UPD_OPE',
    UPD_UNYO_YMD = 'UPD_UNYO',
    UPD_TRN_ID = 'UPD_TRN'
WHERE
    PROSCST_NO = ?

#-