#-----------------------------------------------------------
#--工事費割賦契約(KK_T_KOJIHI_KAP_KEI-KK_SELECT_001)
#--KKPRC00905
#--工事費割賦契約.申込明細番号の申込番号と申込明細_工事案件.申込明細番号の申込番号の一致有無を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
    1
FROM
    KK_T_MSKM_DTL KK0021_1
WHERE
    KK0021_1.MSKM_DTL_NO IN
        (SELECT
             KU0091.MSKM_DTL_NO
         FROM
             KU_T_MSKM_DTL_KOJIAK KU0091
         WHERE
             KU0091.KOJIAK_NO = ?
         AND
             KU0091.MK_FLG = '0')
AND
    KK0021_1.MSKM_NO IN (
        SELECT
            KK0021_2.MSKM_NO
        FROM
            KK_T_MSKM_DTL KK0021_2
        WHERE
            KK0021_2.MSKM_DTL_NO IN
                (SELECT
                     KK3131.MSKM_DTL_NO
                 FROM
                     KK_T_KOJIHI_KAP_KEI KK3131
                 WHERE
                     KK3131.SVC_KEI_KAISEN_UCWK_NO = ?
                 AND
                     KK3131.MK_FLG = '0')
        AND KK0021_2.MK_FLG = '0'
    )
AND KK0021_1.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--工事費割賦契約(KK_T_KOJIHI_KAP_KEI-KK_SELECT_002)
#--KKPRC43902にて使用。
#--工事費割賦契約番号が一致する情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT 
    KK3131.KOJIHI_KAP_KEI_NO,
    KK3131.GENE_ADD_DTM,
    KK3131.KAP_KEI_STAT,
    KK3131.KAP_PLAN_CD,
    KK3131.FIRST_SEIKY_SVC_KEI_NO,
    KK3131.MSKM_DTL_NO,
    KK3131.IDO_DIV,
    KK3131.KAP_IKT_SIKY_SWCH_YM,
    KK3131.KAP_KEI_ENDYMD,
    KK3131.KAP_SEIKY_STA_YM,
    KK3131.KAP_PAY_ZAN_CNT,
    KK3131.KAP_SEIKY_ZUMI_CNT,
    KK3131.KAP_SEIKY_ZUMI_AMNT,
    KK2521.KAP_HAMBAI_FORM_CD
FROM 
    KK_T_KOJIHI_KAP_KEI KK3131
    INNER JOIN
        KK_M_KAP_PLAN KK2521
    ON
        KK2521.KAP_PLAN_CD = KK3131.KAP_PLAN_CD
        AND ? BETWEEN KK2521.KAP_PLAN_TSTAYMD AND KK2521.KAP_PLAN_TENDYMD
        AND KK2521.KAP_HAMBAI_FORM_CD IS NOT NULL
        AND KK2521.RSV_APLY_YMD || KK2521.GENE_ADD_DTM =
        (SELECT
            MAX(KK2521_GENE.RSV_APLY_YMD || KK2521_GENE.GENE_ADD_DTM) AS KK2521_MAX
        FROM
            KK_M_KAP_PLAN KK2521_GENE
        WHERE
            KK2521_GENE.KAP_PLAN_CD = KK2521.KAP_PLAN_CD
            AND KK2521_GENE.RSV_APLY_YMD <= ?
            AND KK2521_GENE.MK_FLG = '0')
WHERE
    KK3131.KOJIHI_KAP_KEI_NO = ?
    AND KK3131.KAP_PAY_ZAN_CNT > '0'
    AND KK3131.GENE_ADD_DTM =
        (
            SELECT  MAX(KK3131_GENE.GENE_ADD_DTM) AS KAP_KEI_MAX
            FROM    KK_T_KOJIHI_KAP_KEI KK3131_GENE
            WHERE   KK3131_GENE.KOJIHI_KAP_KEI_NO = KK3131.KOJIHI_KAP_KEI_NO
            AND     KK3131_GENE.MK_FLG = '0'
        )

#-

#-----------------------------------------------------------
#--工事費割賦契約(KK_T_KOJIHI_KAP_KEI-KK_SELECT_004)
#--ACPRC02423にて売掛金情報を取得する。
#--工事費割賦契約番号が一致する情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_004

     SELECT  /* KK_T_KOJIHI_KAP_KEI.SQL KK_SELECT_004 */
               KK3131.KOJIHI_KAP_KEI_NO
             , KK3131.FIRST_SEIKY_SVC_KEI_NO
             , KK3131.KAP_PLAN_CD
             , KK3131.KAP_PAY_ZAN_CNT
             , KK0081.PRC_GRP_CD
             , KK0081.PCRS_CD
             , AC0611.PRC_KMK_CD
             , AC0611.FIRST_PRC
             , AC0611.KEIZOKU_PRC
             , KK0321.SEIKY_KEI_NO
             , KK0491.SEIKY_WAY_CD
             , KK2521.KAP_PLAN_TTL_AMNT
             , CK0011.KEISHA_TYPE_CD
             , CK0011.SCM_TYPE_CD
       FROM  KK_T_KOJIHI_KAP_KEI KK3131
INNER JOIN  KK_M_KAP_PLAN KK2521
         ON  KK2521.KAP_PLAN_CD = KK3131.KAP_PLAN_CD
        AND  KK2521.RSV_APLY_YMD || KK2521.GENE_ADD_DTM =
             (
                SELECT  MAX(KK2521_GENE.RSV_APLY_YMD || KK2521_GENE.GENE_ADD_DTM) AS KK2521_MAX
                  FROM  KK_M_KAP_PLAN KK2521_GENE
                 WHERE  KK2521_GENE.KAP_PLAN_CD  = KK2521.KAP_PLAN_CD
                   AND  KK2521_GENE.RSV_APLY_YMD <= ?
                   AND  KK2521_GENE.MK_FLG = '0'
             )
INNER JOIN  KK_T_SVC_KEI KK0081
         ON  KK0081.SVC_KEI_NO = KK3131.FIRST_SEIKY_SVC_KEI_NO
INNER JOIN  CK_T_CUST CK0011
         ON  CK0011.SYSID = KK0081.SYSID
INNER JOIN  AC_M_KAP_PRC AC0611
         ON  AC0611.PRC_SVC_CD = KK3131.KAP_PLAN_CD
INNER JOIN  KK_T_KAKINS KK0321
         ON  KK0321.SVC_KEI_NO = KK3131.FIRST_SEIKY_SVC_KEI_NO
INNER JOIN  KK_T_SEIKY_KEI KK0491
         ON  KK0321.SEIKY_KEI_NO = KK0491.SEIKY_KEI_NO
      WHERE
             KK3131.GENE_ADD_DTM =
             (
                SELECT  MAX(KK3131_GENE.GENE_ADD_DTM) AS KAP_KEI_MAX
                  FROM  KK_T_KOJIHI_KAP_KEI KK3131_GENE
                 WHERE  KK3131_GENE.KOJIHI_KAP_KEI_NO = KK3131.KOJIHI_KAP_KEI_NO
                   AND  KK3131_GENE.MK_FLG = '0'
             )
        AND  KK3131.MK_FLG = '0'
        AND  KK3131.BUNKATSU_PAY_KAIK_SUMUP_YM IS NULL
        AND ((KK3131.KAP_KEI_STAT = '030' AND  KK3131.KAP_PAY_ZAN_CNT > '0') OR KK3131.KAP_KEI_STAT = '100')
        AND  KK3131.KAP_KEI_SVC_STAYMD <= 
            (
            SELECT
                 CH0501.EVENT_YMD AS EVENT_YMD_MAX
             FROM
                 CH_M_PRC_SCHDL_TEIGI CH0501
             WHERE
                 CH0501.EVENT_CD = '02'
             AND CH0501.MK_FLG   = '0'
             AND CH0501.SEIKY_YM =
                (SELECT
                    MAX(SEIKY_YM)
                 FROM
                     CH_M_PRC_SCHDL_TEIGI CH0501
                 WHERE
                     CH0501.EVENT_CD   = '21'
                 AND CH0501.MK_FLG     = '0'
                 AND CH0501.EVENT_YMD <= ? )
            )
        AND  KK2521.KAP_HAMBAI_FORM_CD <> '01'
        AND  KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM =
              (
                SELECT  MAX(KK0081.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_MAX
                  FROM  KK_T_SVC_KEI KK0081_GENE
                 WHERE  KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO
                   AND  KK0081_GENE.RSV_APLY_YMD <= ?
                   AND  KK0081_GENE.RSV_APLY_CD  = '2'
                   AND  KK0081_GENE.MK_FLG = '0'
             )
        AND  AC0611.SYS_CD  = 'AC'
        AND  AC0611.PCRS_CD = KK0081.PCRS_CD
        AND  AC0611.KAP_PRC_TSTAYMD <= ?
        AND  AC0611.KAP_PRC_TENDYMD >= ?
        AND  AC0611.MK_FLG = '0'
        AND  KK0321.KAKINS_TSTAYMD  <= ?
        AND  KK0321.KAKINS_TENDYMD  >= ?
        AND  KK0321.MK_FLG = '0'
        AND  KK0491.GENE_ADD_DTM =
             (SELECT  MAX(KK0491_GENE.GENE_ADD_DTM) AS KK0491_MAX
                FROM  KK_T_SEIKY_KEI KK0491_GENE
               WHERE  KK0491_GENE.SEIKY_KEI_NO = KK0491.SEIKY_KEI_NO
                 AND  KK0491_GENE.RSV_APLY_YMD <= ?
                 AND  KK0491_GENE.RSV_APLY_CD   = '2'
                 AND  KK0491_GENE.MK_FLG = '0'
              )
        AND  CK0011.RSV_APLY_YMD || CK0011.GENE_ADD_DTM =
              (
                SELECT  MAX(CK0011_GENE.RSV_APLY_YMD || CK0011_GENE.GENE_ADD_DTM) AS Ck0011_MAX
                  FROM  CK_T_CUST CK0011_GENE
                 WHERE  CK0011_GENE.SYSID= CK0011.SYSID
                   AND  CK0011_GENE.RSV_APLY_YMD <= ?
                   AND  CK0011_GENE.RSV_APLY_CD  = '2'
                   AND  CK0011_GENE.MK_FLG = '0'
             )
#-

#-----------------------------------------------------------
#--工事費割賦契約(KK_T_KOJIHI_KAP_KEI-KK_UPDATE_001)
#--ACPRC02423にて売掛金情報を取得する。
#--工事費割賦契約番号が一致する情報を取得する。
#-----------------------------------------------------------

--KEY:KK_UPDATE_001

UPDATE /* KK_T_KOJIHI_KAP_KEI.SQL KK_UPDATE_001 */
       KK_T_KOJIHI_KAP_KEI KK3131
  SET KK3131.BUNKATSU_PAY_KAIK_SUMUP_YM = ?
     ,KK3131.UPD_DTM = 'UPD_DTM'
     ,KK3131.UPD_OPEACNT = 'UPD_OPE'
     ,KK3131.UPD_UNYO_YMD = 'UPD_UNYO'
     ,KK3131.UPD_TRN_ID   = 'UPD_TRN'
WHERE KK3131.KOJIHI_KAP_KEI_NO = ?
  AND KK3131.KAP_KEI_STAT IN('030','100')

#-

#-----------------------------------------------------------
#--工事費割賦契約(KK_T_KOJIHI_KAP_KEI-KK_SELECT_005)
#--KKPRC51101
#--サービス契約回線内訳番号に紐付く工事費割賦契約のカレントレコードを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_005

SELECT /* KK_T_KOJIHI_KAP_KEI.SQL KK_SELECT_005 */
    KK3131.KOJIHI_KAP_KEI_NO
    , KK3131.GENE_ADD_DTM
    , KK3131.KAP_KEI_STAT
    , KK3131.KAP_PLAN_CD
    , KK3131.SVC_KEI_KAISEN_UCWK_NO
    , KK3131.FIRST_SEIKY_SVC_KEI_NO
    , KK3131.MSKM_DTL_NO
    , KK3131.IDO_DIV
    , KK3131.KAP_IKT_SIKY_SWCH_YM
    , KK3131.KAP_KEI_CNC_YMD
    , KK3131.KAP_KEI_ENDYMD
    , KK3131.KAP_KEI_CANCEL_YMD
    , KK3131.KAP_KEI_SVC_STAYMD
    , KK3131.KAP_KEI_CHRG_STAYMD
    , KK3131.KAP_SEIKY_STA_YM
    , KK3131.KAP_PAY_ZAN_CNT
    , KK3131.KAP_SEIKY_ZUMI_CNT
    , KK3131.KAP_SEIKY_ZUMI_AMNT
    , KK3131.ADD_DTM
    , KK3131.ADD_OPEACNT
    , KK3131.UPD_DTM
    , KK3131.UPD_OPEACNT
    , KK3131.DEL_DTM
    , KK3131.DEL_OPEACNT
    , KK3131.MK_FLG
    , KK3131.ADD_UNYO_YMD
    , KK3131.ADD_TRN_ID
    , KK3131.UPD_UNYO_YMD
    , KK3131.UPD_TRN_ID
    , KK3131.DEL_UNYO_YMD
    , KK3131.DEL_TRN_ID
FROM
    KK_T_KOJIHI_KAP_KEI KK3131
WHERE
    KK3131.SVC_KEI_KAISEN_UCWK_NO = ?
    AND (KK3131.KOJIHI_KAP_KEI_NO, KK3131.GENE_ADD_DTM) = 
        (SELECT KK3131_GENE.KOJIHI_KAP_KEI_NO, MAX(KK3131_GENE.GENE_ADD_DTM) AS KK3131_MAX
        FROM   KK_T_KOJIHI_KAP_KEI KK3131_GENE
        WHERE  KK3131_GENE.KOJIHI_KAP_KEI_NO = KK3131.KOJIHI_KAP_KEI_NO
        AND    KK3131_GENE.MK_FLG = '0'
        GROUP BY KK3131_GENE.KOJIHI_KAP_KEI_NO)
    AND KK3131.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--工事費割賦契約(KK_T_KOJIHI_KAP_KEI-KK_UPDATE_002)
#--KKPRC51101
#--工事費割賦契約番号が一致する工事費割賦契約を論理削除する。
#-----------------------------------------------------------

--KEY:KK_UPDATE_002

UPDATE /* KK_T_KOJIHI_KAP_KEI.SQL KK_UPDATE_002 */
      KK_T_KOJIHI_KAP_KEI KK3131
  SET KK3131.UPD_DTM = ?
     ,KK3131.UPD_OPEACNT = ?
     ,KK3131.DEL_DTM = ?
     ,KK3131.DEL_OPEACNT = ?
     ,KK3131.MK_FLG = '1'
WHERE KK3131.KOJIHI_KAP_KEI_NO = ?

#-

#-----------------------------------------------------------
#--工事費割賦契約(KK_T_KOJIHI_KAP_KEI-KK_SELECT_006)
#--KKPRC00905 
#--サービス契約回線内訳番号に紐付く照査済、締結済の工事費割賦契約を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_006

SELECT
    KK3131.KOJIHI_KAP_KEI_NO
FROM
    KK_T_KOJIHI_KAP_KEI KK3131
WHERE
    KK3131.SVC_KEI_KAISEN_UCWK_NO = ?
AND KK3131.KAP_KEI_STAT IN ('020', '030')
AND (KK3131.KOJIHI_KAP_KEI_NO, KK3131.GENE_ADD_DTM) = 
   (SELECT
        KK3131_GENE.KOJIHI_KAP_KEI_NO, MAX(KK3131_GENE.GENE_ADD_DTM) AS KK3131_MAX
    FROM
        KK_T_KOJIHI_KAP_KEI KK3131_GENE
    WHERE
        KK3131_GENE.KOJIHI_KAP_KEI_NO = KK3131.KOJIHI_KAP_KEI_NO
    AND KK3131_GENE.MK_FLG = '0'
    GROUP BY KK3131_GENE.KOJIHI_KAP_KEI_NO)

#-