#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_INSERT_001)
#--【請求情報登録】
#--債権情報を登録する。
#-----------------------------------------------------------

--KEY:CH_INSERT_001

INSERT INTO CH_T_SAIKEN (
    SAIKEN_NO
    ,SEIKY_KEI_NO
    ,SVC_KEI_NO
    ,SAIKEN_YM
    ,SAIKEN_STAT
    ,SEIKY_WAY_CD
    ,PAY_KIGEN_YMD
    ,CHG_PAY_KIGEN_YMD
    ,SEIKY_SBT_CD
    ,SAIKEN_TORIATSU_CD
    ,SAIKEN_FIX_YMD
    ,RCNT_SAIKEN_JUTO_YMD
    ,SAIKEN_ADJ_YMD
    ,SAIKEN_CHG_YMD
    ,JUTO_FIN_YMD
    ,SAIKEN_SHOKYAKU_YMD
    ,SHOKYAKU_RSN_CD
    ,ADD_DTM
    ,ADD_OPEACNT
    ,UPD_DTM
    ,UPD_OPEACNT
    ,DEL_DTM
    ,DEL_OPEACNT
    ,MK_FLG
) VALUES (
    SEQ_SAIKEN_NO.NEXTVAL
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,'0'
)

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_001)
#--【仮入金取消】
#--入金番号を指定し、充当に紐づく債権を取得する。
#-----------------------------------------------------------

--KEY:CH_SELECT_001

SELECT
    CH0011.SAIKEN_NO , 
    CH0011.SAIKEN_STAT
FROM
    CH_T_JUTO CH0201
     , CH_T_SAIKEN CH0011
WHERE
    CH0201.NYUKIN_NO = ?
    AND CH0201.SAIKEN_NO = CH0011.SAIKEN_NO
    AND CH0201.MK_FLG='0'
    AND CH0011.MK_FLG='0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_002)
#--【未収債権情報抽出】
#--入金の消込先データ抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_002

SELECT
    CH0011.SEIKY_KEI_NO
    ,CH0011.SAIKEN_NO
    ,CH0011.SAIKEN_STAT
    ,CH0011.SAIKEN_YM
    ,CH0021.PRC_GRP_CD
    ,CH0021.PCRS_CD
    ,CH0021.PRC_SVC_CD
    ,CH0021.PRC_KMK_CD
    ,CH0021.AMNT
    ,CH0051.SEIKY_YM
    ,CH0051.SEIKY_NO
    ,NVL2(CH0341.TOKUSOKU_NO, CH0231.TOKUSOKU_NO, NULL) AS TOKUSOKU_NO
    ,CH0111.NYUKIN_NO
    ,CH0111.NYUKIN_ROUTE_CD
    ,CH0111.NYUKIN_STAT
FROM
    CH_T_NYUKIN CH0111
    INNER JOIN CH_T_SEIKY CH0051
        ON  CH0111.SEIKY_NO = CH0051.SEIKY_NO
    INNER JOIN CH_T_SEIKY_SKN_KNRN CH0041
        ON  CH0111.SEIKY_NO = CH0041.SEIKY_NO
        AND CH0051.SEIKY_NO = CH0041.SEIKY_NO
    INNER JOIN CH_T_SAIKEN CH0011
        ON  CH0041.SAIKEN_NO = CH0011.SAIKEN_NO
    INNER JOIN CH_T_SAIKEN_UCWK CH0021
        ON CH0041.SAIKEN_NO = CH0021.SAIKEN_NO
        AND  CH0011.SAIKEN_NO = CH0021.SAIKEN_NO
    INNER JOIN
    (
        SELECT
            CH0401_SUB.PRC_GRP_CD
            ,CH0401_SUB.PCRS_CD
            ,CH0401_SUB.PRC_SVC_CD
            ,CH0401_SUB.PRC_KMK_CD
        FROM
            CH_M_PRC_KMK_CS_CHGE CH0401_SUB
            INNER JOIN ZM_M_WORK_PARAM_KNRI ZM0321
                ON  CH0401_SUB.WORK_KINO_SKBT_CD = ZM0321.WORK_PARAM_SETTE_VALUE
        WHERE
            ZM0321.WORK_PARAM_ID = ?
            AND CH0401_SUB.SYS_CD = ?
            AND CH0401_SUB.CHSHT_CHG_CD = ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TSTAYMD <= ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TENDYMD >= ?
            AND CH0401_SUB.MK_FLG = '0'
            AND ZM0321.MK_FLG = '0'
    ) CH0401
        ON  CH0021.PRC_GRP_CD = CH0401.PRC_GRP_CD
        AND CH0021.PCRS_CD = CH0401.PCRS_CD
        AND CH0021.PRC_SVC_CD = CH0401.PRC_SVC_CD
        AND CH0021.PRC_KMK_CD = CH0401.PRC_KMK_CD
    LEFT OUTER JOIN CH_T_TOKUSK_SKN_KNRN CH0341
        ON  CH0011.SAIKEN_NO = CH0341.SAIKEN_NO
        AND CH0051.TOKUSOKU_NO = CH0341.TOKUSOKU_NO
        AND CH0051.MK_FLG = '0'
    LEFT OUTER JOIN  CH_T_TOKUSOKU CH0231
        ON  CH0111.SEIKY_KEI_NO = CH0231.SEIKY_KEI_NO 
        AND CH0231.TOKUSOKU_STAYMD <= ?
        AND CH0231.TOKUSOKU_ENDYMD >  ?
        AND CH0231.MK_FLG = '0' 
WHERE
   CH0111.NYUKIN_STAT = ?
   AND CH0111.NYUKIN_SYRZM_FLG = ?
   AND CH0011.SAIKEN_STAT IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
   AND CH0011.MK_FLG = '0'
   AND CH0021.MK_FLG = '0'
   AND CH0051.MK_FLG = '0'
   AND CH0111.MK_FLG = '0'
   AND CH0041.MK_FLG = '0'
ORDER BY
    CH0011.SEIKY_KEI_NO, CH0111.NYUKIN_NO, CH0011.SAIKEN_YM

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_003)
#--【請求登録（日次）】
#--債権ステータスと債権年月を指定し、債権番号に紐づく請求番号を取得する。
#-----------------------------------------------------------

--KEY:CH_SELECT_003

SELECT
    CH0011.SEIKY_KEI_NO
    ,CH0011.SAIKEN_YM
    ,CH0011.SAIKEN_NO
    ,CH0041.SEIKY_NO
FROM
    CH_T_SAIKEN CH0011
    INNER JOIN CH_T_SEIKY_SKN_KNRN CH0041
        ON  CH0011.SAIKEN_NO = CH0041.SAIKEN_NO
WHERE
    CH0011.SAIKEN_STAT = ?
    AND CH0011.SAIKEN_YM = 
    (
        SELECT
            CH0501_SUB.SEIKY_YM
        FROM
            (
                SELECT
                     CH0501.SEIKY_YM
                FROM 
                    CH_M_PRC_SCHDL_TEIGI CH0501
                WHERE
                        CH0501.EVENT_CD = ?
                    AND CH0501.EVENT_YMD >= ?
                    AND CH0501.MK_FLG = '0'
                ORDER BY CH0501.EVENT_YMD ASC
            ) CH0501_SUB
        WHERE
            ROWNUM = 1
    )
    AND CH0011.MK_FLG = '0'
    AND CH0041.MK_FLG = '0'
ORDER BY
    CH0011.SEIKY_KEI_NO
    ,CH0011.SAIKEN_YM

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_004)
#--【債権充当】
#--債権ステータスを取得する。
#-----------------------------------------------------------

--KEY:CH_SELECT_004

SELECT
    CH0011.SAIKEN_STAT
FROM
    CH_T_SAIKEN CH0011
WHERE
    CH0011.SAIKEN_NO = ?
    AND CH0011.MK_FLG='0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_005)
#--【前受金相殺結果反映】
#-----------------------------------------------------------

--KEY:CH_SELECT_005

SELECT
    CH0011.SAIKEN_NO,
    RCNT_SAIKEN_JUTO_YMD,
    SAIKEN_YM,
    SEIKY_WAY_CD
FROM
    CH_T_SAIKEN CH0011
WHERE
    CH0011.SEIKY_KEI_NO = ?
    AND CH0011.SAIKEN_YM = ?
    AND CH0011.SEIKY_SBT_CD = ?
    AND CH0011.MK_FLG='0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_6_SUB)
#--【未収債権抽出】
#--SUB対応後、SELEC
#--T_006を削除しこちらを使用
#-----------------------------------------------------------

--KEY:CH_SELECT_6_SUB

SELECT
     MINO_ALL.SEIKY_KEI_NO
    ,MINO_ALL.SAIKEN_YM AS SEIKY_YM
    ,SUBSTR(MINO_ALL.PRC_KMK_CD,1,2) AS SIKY_MT_COMP_CD
    ,MINO_ALL.PCRS_CD
    ,MINO_ALL.PRC_SVC_CD
    ,MINO_ALL.PRC_KMK_CD
    ,ZM0171.PCRS_NM AS PCRS_NM
    ,AC0181.PRC_SVC_FORMAL_NM AS PRC_SVC_NM
    ,MINO_ALL.AMNT
FROM
    (
     (
      SELECT
          CH0401_1.SEIKY_KEI_NO
         ,CH0401_1.SAIKEN_YM
         ,CH0401_1.PCRS_CD
         ,CH0401_1.PRC_SVC_CD
         ,CH0401_1.PRC_KMK_CD
         ,CH0401_1.AMNT
      FROM
          (
           SELECT
               CH0401_2.PCRS_CD
              ,CH0401_2.PRC_SVC_CD
              ,CH0401_2.PRC_KMK_CD
              ,CH0401_2.AMNT
              ,CH0011_1.SAIKEN_NO 
              ,CH0011_1.SAIKEN_STAT
              ,CH0011_1.SAIKEN_YM
              ,CH0011_1.SEIKY_KEI_NO
           FROM
               (
                SELECT
                    CH0021_1.SAIKEN_NO
                   ,CH0021_1.PCRS_CD
                   ,CH0021_1.PRC_SVC_CD
                   ,CH0021_1.PRC_KMK_CD
                   ,CH0021_1.AMNT
                FROM
                    (
                     SELECT
                         ZM0311_1_SUB.SYS_PARAM_SETTE_VALUE_1
                     FROM
                         ZM_M_SYS_PARAM_KNRI ZM0311_1_SUB
                     WHERE
                             ZM0311_1_SUB.SYS_PARAM_BUNRUI_CD = ?
                         AND ZM0311_1_SUB.SYS_PARAM_ID        = ?
                         AND ? BETWEEN ZM0311_1_SUB.SYS_PARAM_TSTAYMD AND ZM0311_1_SUB.SYS_PARAM_TENDYMD 
                         AND ZM0311_1_SUB.MK_FLG              = '0'
                    ) ZM0311_1
                    ,CH_M_PRC_KMK_CS_CHGE CH0401_3
                INNER JOIN
                    CH_T_SAIKEN_UCWK CH0021_1
                ON
                        CH0401_3.PCRS_CD    = CH0021_1.PCRS_CD 
                    AND CH0401_3.PRC_SVC_CD = CH0021_1.PRC_SVC_CD 
                    AND CH0401_3.PRC_KMK_CD = CH0021_1.PRC_KMK_CD 
                    AND CH0021_1.MK_FLG = '0'
                WHERE
                        CH0401_3.SYS_CD            = ?
                    AND CH0401_3.WORK_KINO_SKBT_CD = ZM0311_1.SYS_PARAM_SETTE_VALUE_1
                    AND CH0401_3.CHSHT_CHG_CD      = ?
                    AND ? BETWEEN CH0401_3.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401_3.PRC_KMK_CS_CHGE_TENDYMD 
                    AND CH0401_3.MK_FLG            = '0'
               ) CH0401_2
               ,CH_T_SAIKEN CH0011_1
           WHERE
                   CH0401_2.SAIKEN_NO  = CH0011_1.SAIKEN_NO 
               AND CH0011_1.SAIKEN_STAT IN (?, ?, ?, ?) 
               AND CH0011_1.MK_FLG     = '0'
          ) CH0401_1
     )
     UNION ALL
     (
      SELECT
          SHUNO.SEIKY_KEI_NO
         ,SHUNO.SAIKEN_YM
         ,RPAD(' ',?) AS PCRS_CD
         ,RPAD(' ',?) AS PRC_SVC_CD
         ,ZM0321.WORK_PARAM_SETTE_VALUE AS PRC_KMK_CD
         ,SHUNO.JUTO_AMNT AS AMNT
      FROM
          (
           SELECT
               ZM0321_SUB.WORK_PARAM_SETTE_VALUE
           FROM
               ZM_M_WORK_PARAM_KNRI ZM0321_SUB
           WHERE
                   ZM0321_SUB.WORK_PARAM_SETTE_VALUE = ?
               AND ? BETWEEN ZM0321_SUB.WORK_PARAM_TSTAYMD AND ZM0321_SUB.WORK_PARAM_TENDYMD
               AND ZM0321_SUB.MK_FLG = '0'
          ) ZM0321
         ,(
           SELECT
               MIN(CH0501_1_SUB.SEIKY_YM) || '99' AS KAIK_YMD
           FROM 
               CH_M_PRC_SCHDL_TEIGI CH0501_1_SUB
           WHERE
                   CH0501_1_SUB.EVENT_CD = ? AND CH0501_1_SUB.EVENT_YMD >= ?
               AND CH0501_1_SUB.MK_FLG = '0'
          ) CH0501_1
         ,(
           SELECT 
               CH0011_2.SEIKY_KEI_NO
              ,CH0011_2.SAIKEN_YM
              ,CH0201.JUTO_AMNT
              ,CH0111.OUT_ORG_SHUNO_YMD
           FROM
               CH_T_SAIKEN CH0011_2
           INNER JOIN
               (
                SELECT
                    CH0201_SUB.JUTO_AMNT
                   ,(
                     CASE 
                         WHEN CH0201_SUB.NYUKIN_NO IS NOT NULL THEN CH0201_SUB.NYUKIN_NO
                         ELSE CH0191.NYUKIN_NO 
                     END
                    ) AS NYUKIN_NO
                   ,CH0201_SUB.SAIKEN_NO
                FROM
                    CH_T_JUTO CH0201_SUB
                LEFT OUTER JOIN 
                    CH_T_KNK CH0191
                ON
                        CH0191.KNK_NO  = CH0201_SUB.KNK_NO
                    AND CH0201_SUB.MK_FLG = '0'
                    AND CH0191.MK_FLG     = '0'
               ) CH0201
           ON
                   CH0201.SAIKEN_NO = CH0011_2.SAIKEN_NO 
               AND CH0011_2.SAIKEN_STAT IN (?, ?) 
               AND CH0011_2.MK_FLG  = '0' 
           INNER JOIN
               CH_T_NYUKIN CH0111
           ON
                   CH0111.NYUKIN_NO = CH0201.NYUKIN_NO 
               AND CH0111.MK_FLG    = '0'
          ) SHUNO
      WHERE
          SHUNO.OUT_ORG_SHUNO_YMD > CH0501_1.KAIK_YMD
     )
     UNION ALL
     (
      SELECT
          KASHI_1.SEIKY_KEI_NO
         ,KASHI_1.SAIKEN_YM
         ,KASHI_1.PCRS_CD
         ,KASHI_1.PRC_SVC_CD
         ,KASHI_1.PRC_KMK_CD
         ,KASHI_1.AMNT
      FROM
          (
           SELECT
               CH0011_3.SEIKY_KEI_NO
              ,CH0011_3.SAIKEN_YM
              ,KASHI_SUB.PCRS_CD
              ,KASHI_SUB.PRC_SVC_CD
              ,KASHI_SUB.PRC_KMK_CD
              ,KASHI_SUB.AMNT
           FROM
               (
                SELECT
                    CH0401_2.PCRS_CD
                   ,CH0401_2.PRC_SVC_CD
                   ,CH0401_2.OUT_PRC_KMK_CD AS PRC_KMK_CD
                   ,CH0021_3.AMNT
                   ,CH0021_3.SAIKEN_NO
                   ,CH0501_2.KAIK_YMD
                FROM
                    (
                     SELECT
                         CH0401_2_SUB.PCRS_CD
                        ,CH0401_2_SUB.PRC_SVC_CD
                        ,CH0401_2_SUB.PRC_KMK_CD
                        ,CH0401_2_SUB.OUT_PRC_KMK_CD
                     FROM
                         (
                          SELECT
                              ZM0311_2_SUB.SYS_PARAM_SETTE_VALUE_1
                          FROM 
                              ZM_M_SYS_PARAM_KNRI ZM0311_2_SUB
                          WHERE
                                  ZM0311_2_SUB.SYS_PARAM_BUNRUI_CD = ?
                              AND ZM0311_2_SUB.SYS_PARAM_ID        = ?
                              AND ? BETWEEN ZM0311_2_SUB.SYS_PARAM_TSTAYMD AND ZM0311_2_SUB.SYS_PARAM_TENDYMD
                              AND ZM0311_2_SUB.MK_FLG              = '0'
                         ) ZM0311_2
                        ,CH_M_PRC_KMK_CS_CHGE CH0401_2_SUB
                     WHERE
                             CH0401_2_SUB.SYS_CD            = ?
                         AND CH0401_2_SUB.WORK_KINO_SKBT_CD = ZM0311_2.SYS_PARAM_SETTE_VALUE_1
                         AND CH0401_2_SUB.CHSHT_CHG_CD      = ?
                         AND ? BETWEEN CH0401_2_SUB.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401_2_SUB.PRC_KMK_CS_CHGE_TENDYMD 
                         AND CH0401_2_SUB.MK_FLG            = '0'
                    ) CH0401_2
                   ,(
                     SELECT 
                         MIN(CH0501_2_SUB.SEIKY_YM) || 99 AS KAIK_YMD
                     FROM
                         CH_M_PRC_SCHDL_TEIGI CH0501_2_SUB
                     WHERE
                             CH0501_2_SUB.EVENT_CD   = ?
                         AND CH0501_2_SUB.EVENT_YMD >= ?
                         AND CH0501_2_SUB.MK_FLG     = '0'
                    ) CH0501_2
                   ,(
                     SELECT
                         CH0021_2.SAIKEN_NO
                        ,CH0021_2.PCRS_CD
                        ,CH0021_2.PRC_SVC_CD
                        ,CH0021_2.PRC_KMK_CD
                        ,CH0021_2.AMNT
                     FROM
                         CH_T_SAIKEN_UCWK CH0021_2
                     WHERE
                         CH0021_2.MK_FLG = '0'
                    ) CH0021_3
                WHERE
                        CH0401_2.PCRS_CD    = CH0021_3.PCRS_CD
                    AND CH0401_2.PRC_SVC_CD = CH0021_3.PRC_SVC_CD
                    AND CH0401_2.PRC_KMK_CD = CH0021_3.PRC_KMK_CD
               ) KASHI_SUB
              ,CH_T_SAIKEN CH0011_3
           WHERE
                   CH0011_3.SAIKEN_SHOKYAKU_YMD  > KASHI_SUB.KAIK_YMD
               AND KASHI_SUB.SAIKEN_NO           = CH0011_3.SAIKEN_NO
               AND CH0011_3.SAIKEN_STAT          = ?
               AND CH0011_3.MK_FLG               = '0'
          ) KASHI_1
     )
     UNION ALL
     (
      SELECT
          KESHI.SEIKY_KEI_NO
         ,KESHI.SAIKEN_YM
         ,KESHI.PCRS_CD
         ,KESHI.PRC_SVC_CD
         ,KESHI.PRC_KMK_CD
         ,KESHI.AMNT
      FROM
          (
           SELECT
               CH0011_4.SEIKY_KEI_NO
              ,CH0011_4.SAIKEN_YM
              ,CH0011_4.SAIKEN_SHOKYAKU_YMD
              ,KESHI_2.PCRS_CD
              ,KESHI_2.PRC_SVC_CD
              ,KESHI_2.PRC_KMK_CD
              ,KESHI_2.AMNT
           FROM
               (
                SELECT
                    CH0401_3.PCRS_CD
                   ,CH0401_3.PRC_SVC_CD
                   ,CH0401_3.OUT_PRC_KMK_CD AS PRC_KMK_CD
                   ,CH0501_3.KAIK_YMD
                   ,KESHI_1.SAIKEN_NO
                   ,KESHI_1.AMNT
                FROM
                    (
                     SELECT
                         CH0401_3_SUB.PCRS_CD
                        ,CH0401_3_SUB.PRC_SVC_CD
                        ,CH0401_3_SUB.PRC_KMK_CD
                        ,CH0401_3_SUB.OUT_PRC_KMK_CD
                     FROM
                         (
                          SELECT 
                              ZM0311_3_SUB.SYS_PARAM_SETTE_VALUE_1
                          FROM
                              ZM_M_SYS_PARAM_KNRI ZM0311_3_SUB
                          WHERE
                                    ZM0311_3_SUB.SYS_PARAM_BUNRUI_CD = ?
                                AND ZM0311_3_SUB.SYS_PARAM_ID        = ?
                                AND ? BETWEEN ZM0311_3_SUB.SYS_PARAM_TSTAYMD AND ZM0311_3_SUB.SYS_PARAM_TENDYMD 
                                AND ZM0311_3_SUB.MK_FLG = '0'
                         ) ZM0311_3
                        ,CH_M_PRC_KMK_CS_CHGE CH0401_3_SUB
                     WHERE
                             CH0401_3_SUB.SYS_CD            = ?
                         AND CH0401_3_SUB.WORK_KINO_SKBT_CD = ZM0311_3.SYS_PARAM_SETTE_VALUE_1 
                         AND CH0401_3_SUB.CHSHT_CHG_CD      = ?
                         AND ? BETWEEN  CH0401_3_SUB.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401_3_SUB.PRC_KMK_CS_CHGE_TENDYMD 
                         AND CH0401_3_SUB.MK_FLG            = '0'
                    ) CH0401_3
                   ,(
                     SELECT
                         MIN(CH0501_3_SUB.SEIKY_YM) || 99 AS KAIK_YMD
                     FROM
                         CH_M_PRC_SCHDL_TEIGI CH0501_3_SUB
                     WHERE
                             CH0501_3_SUB.EVENT_CD   = ?
                         AND CH0501_3_SUB.EVENT_YMD >= ?
                         AND CH0501_3_SUB.MK_FLG     = '0' 
                    ) CH0501_3
                   ,(
                     SELECT
                         CH0021_3.PCRS_CD
                        ,CH0021_3.PRC_SVC_CD
                        ,CH0021_3.PRC_KMK_CD
                        ,CH0021_3.AMNT
                        ,CH0021_3.SAIKEN_NO
                     FROM
                         CH_T_SAIKEN_UCWK CH0021_3
                     WHERE
                         CH0021_3.MK_FLG = '0'
                    ) KESHI_1
                WHERE
                        KESHI_1.PCRS_CD    = CH0401_3.PCRS_CD 
                    AND KESHI_1.PRC_SVC_CD = CH0401_3.PRC_SVC_CD 
                    AND KESHI_1.PRC_KMK_CD = CH0401_3.PRC_KMK_CD 
               ) KESHI_2
              ,CH_T_SAIKEN CH0011_4
           WHERE
                   CH0011_4.SAIKEN_NO           = KESHI_2.SAIKEN_NO
               AND CH0011_4.SAIKEN_SHOKYAKU_YMD > KESHI_2.KAIK_YMD
               AND CH0011_4.SAIKEN_STAT         = ?
               AND CH0011_4.MK_FLG              = '0'
          ) KESHI
     )
    )MINO_ALL
INNER JOIN
    KK_M_PCRS ZM0171
ON
        ZM0171.PCRS_CD     = MINO_ALL.PCRS_CD 
    AND ZM0171.GENE_ADD_DTM =
        (
         SELECT
             MAX(ZM0171_GENE.GENE_ADD_DTM) AS ZM0171_MAX
         FROM 
             KK_M_PCRS ZM0171_GENE
         WHERE
                 ZM0171_GENE.PCRS_CD = ZM0171.PCRS_CD
             AND ? BETWEEN ZM0171_GENE.PCRS_TSTAYMD AND ZM0171_GENE.PCRS_TENDYMD
             AND ZM0171_GENE.MK_FLG  = '0'
        )
INNER JOIN
    AC_M_PRC_SVC_KANRI AC0181
ON
        AC0181.SYS_CD     = ?
    AND AC0181.PCRS_CD    = MINO_ALL.PCRS_CD
    AND AC0181.PRC_SVC_CD = MINO_ALL.PRC_SVC_CD
        AND AC0181.GENE_ADD_DTM =
            (
             SELECT
                 MAX(AC0181_GENE.GENE_ADD_DTM) AS AC0181_MAX
             FROM 
                 AC_M_PRC_SVC_KANRI AC0181_GENE
             WHERE
                     AC0181_GENE.SYS_CD     =  AC0181.SYS_CD
                 AND AC0181_GENE.PCRS_CD    =  AC0181.PCRS_CD
                 AND AC0181_GENE.PRC_SVC_CD =  AC0181.PRC_SVC_CD
                 AND ? BETWEEN AC0181_GENE.PRC_SVC_KANRI_TSTAYMD AND AC0181_GENE.PRC_SVC_KANRI_TENDYMD
                 AND AC0181_GENE.MK_FLG     = '0'
            )
ORDER BY
     SIKY_MT_COMP_CD ASC
    ,PCRS_CD ASC
    ,SEIKY_YM ASC

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_006)
#--【未収債権抽出】
#--SUB対応後、SELEC
#--T_006を削除しこちらを使用
#-----------------------------------------------------------

--KEY:CH_SELECT_006

SELECT
     MINO_ALL.SEIKY_KEI_NO
    ,MINO_ALL.SAIKEN_YM AS SEIKY_YM
    ,SUBSTR(MINO_ALL.PRC_KMK_CD,1,2) AS SIKY_MT_COMP_CD
    ,MINO_ALL.PRC_GRP_CD
    ,MINO_ALL.PCRS_CD
    ,MINO_ALL.PRC_SVC_CD
    ,MINO_ALL.PRC_KMK_CD
    ,(
        SELECT
            KK0781.PRC_GRP_NM
        FROM 
           (SELECT 
                 KK0781.PRC_GRP_CD
                ,KK0781.PRC_GRP_NM
            FROM
                 KK_M_PRC_GRP KK0781
            INNER JOIN (SELECT
                             KK0781_GENE.PRC_GRP_CD
                            ,MAX(KK0781_GENE.GENE_ADD_DTM) AS GENE_ADD_DTM
                        FROM 
                             KK_M_PRC_GRP KK0781_GENE
                        WHERE KK0781_GENE.RSV_APLY_YMD <= ?
                        AND ? BETWEEN KK0781_GENE.PRC_GRP_TSTAYMD AND KK0781_GENE.PRC_GRP_TENDYMD
                        AND KK0781_GENE.MK_FLG = '0'
                        GROUP BY KK0781_GENE.PRC_GRP_CD
                        )KK0781_GENE
            ON  KK0781.PRC_GRP_CD = KK0781_GENE.PRC_GRP_CD
            AND KK0781.GENE_ADD_DTM = KK0781_GENE.GENE_ADD_DTM
            WHERE KK0781.MK_FLG = '0')KK0781
        WHERE
            KK0781.PRC_GRP_CD = MINO_ALL.PRC_GRP_CD
      ) AS PRC_GRP_NM
    ,(
        SELECT
            ZM0171.PCRS_NM AS PCRS_NM
        FROM
            KK_M_PCRS ZM0171
        WHERE
            ZM0171.PCRS_CD     = MINO_ALL.PCRS_CD 
        AND ZM0171.GENE_ADD_DTM =
        (
           SELECT
               MAX(ZM0171_GENE.GENE_ADD_DTM) AS ZM0171_MAX
           FROM 
               KK_M_PCRS ZM0171_GENE
           WHERE
               ZM0171_GENE.PCRS_CD = ZM0171.PCRS_CD
               AND ? BETWEEN ZM0171_GENE.PCRS_TSTAYMD AND ZM0171_GENE.PCRS_TENDYMD
               AND ZM0171_GENE.MK_FLG  = '0'
        )
    ) AS PCRS_NM
    ,(
        SELECT
            AC0181.PRC_SVC_FORMAL_NM
        FROM
            AC_M_PRC_SVC_KANRI AC0181
        WHERE
            AC0181.SYS_CD     = ?
            AND AC0181.PCRS_CD    = MINO_ALL.PCRS_CD
            AND AC0181.PRC_SVC_CD = MINO_ALL.PRC_SVC_CD
            AND AC0181.GENE_ADD_DTM =
            (
             SELECT
                 MAX(AC0181_GENE.GENE_ADD_DTM) AS AC0181_MAX
             FROM 
                 AC_M_PRC_SVC_KANRI AC0181_GENE
             WHERE
                     AC0181_GENE.SYS_CD     =  AC0181.SYS_CD
                 AND AC0181_GENE.PCRS_CD    =  AC0181.PCRS_CD
                 AND AC0181_GENE.PRC_SVC_CD =  AC0181.PRC_SVC_CD
                 AND ? BETWEEN AC0181_GENE.PRC_SVC_KANRI_TSTAYMD AND AC0181_GENE.PRC_SVC_KANRI_TENDYMD
                 AND AC0181_GENE.MK_FLG     = '0'
            )
    )AS PRC_SVC_NM
    ,MINO_ALL.AMNT
FROM
    (
     (
      SELECT
          CH0401_1.SEIKY_KEI_NO
         ,CH0401_1.SAIKEN_YM
         ,CH0401_1.PRC_GRP_CD
         ,CH0401_1.PCRS_CD
         ,CH0401_1.PRC_SVC_CD
         ,CH0401_1.PRC_KMK_CD
         ,CH0401_1.AMNT
      FROM
          (
           SELECT
               CH0401_2.PRC_GRP_CD
              ,CH0401_2.PCRS_CD
              ,CH0401_2.PRC_SVC_CD
              ,CH0401_2.PRC_KMK_CD
              ,CH0401_2.AMNT
              ,CH0011_1.SAIKEN_NO 
              ,CH0011_1.SAIKEN_STAT
              ,CH0011_1.SAIKEN_YM
              ,CH0011_1.SEIKY_KEI_NO
           FROM
               (
                SELECT
                    CH0021_1.SAIKEN_NO
                   ,CH0021_1.PRC_GRP_CD
                   ,CH0021_1.PCRS_CD
                   ,CH0021_1.PRC_SVC_CD
                   ,CH0021_1.PRC_KMK_CD
                   ,CH0021_1.AMNT
                FROM
                    CH_T_SAIKEN_UCWK CH0021_1
                WHERE
                    CH0021_1.PRC_GRP_CD || CH0021_1.PCRS_CD || CH0021_1.PRC_SVC_CD || PRC_KMK_CD IN
                    (
                        SELECT
                            CH0401_3.PRC_GRP_CD || CH0401_3.PCRS_CD || CH0401_3.PRC_SVC_CD || CH0401_3.PRC_KMK_CD
                        FROM
                            CH_M_PRC_KMK_CS_CHGE CH0401_3
                        WHERE
                            CH0401_3.WORK_KINO_SKBT_CD = (
                                SELECT
                                    ZM0321_1_SUB.WORK_PARAM_SETTE_VALUE
                                FROM
                                    ZM_M_WORK_PARAM_KNRI ZM0321_1_SUB
                                WHERE
                                        ZM0321_1_SUB.WORK_PARAM_ID       = ?
                                    AND ? BETWEEN ZM0321_1_SUB.WORK_PARAM_TSTAYMD AND ZM0321_1_SUB.WORK_PARAM_TENDYMD 
                                    AND ZM0321_1_SUB.MK_FLG              = '0'
                            )
                            AND CH0401_3.SYS_CD            = ?
                            AND CH0401_3.CHSHT_CHG_CD      = ?
                            AND ? BETWEEN CH0401_3.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401_3.PRC_KMK_CS_CHGE_TENDYMD 
                            AND CH0401_3.MK_FLG            = '0'
                    )
                    AND     CH0021_1.MK_FLG = '0'
                               ) CH0401_2
                               ,CH_T_SAIKEN CH0011_1
                           WHERE
                                   CH0401_2.SAIKEN_NO  = CH0011_1.SAIKEN_NO 
                               AND CH0011_1.SAIKEN_STAT IN (?, ?, ?, ?) 
                               AND CH0011_1.MK_FLG     = '0'
            ) CH0401_1
     )
     UNION ALL
     (
      SELECT
          SHUNO.SEIKY_KEI_NO
         ,SHUNO.SAIKEN_YM
         ,RPAD(' ',?) AS PRC_GRP_CD
         ,RPAD(' ',?) AS PCRS_CD
         ,RPAD(' ',?) AS PRC_SVC_CD
         ,CH0401_2.OUT_PRC_KMK_CD AS PRC_KMK_CD
         ,SHUNO.JUTO_AMNT AS AMNT
      FROM
          (
           SELECT
               MIN(CH0501_1_SUB.SEIKY_YM) || '99' AS KAIK_YMD
           FROM 
               CH_M_PRC_SCHDL_TEIGI CH0501_1_SUB
           WHERE
                   CH0501_1_SUB.EVENT_CD = ? AND CH0501_1_SUB.EVENT_YMD >= ?
               AND CH0501_1_SUB.MK_FLG = '0'
          ) CH0501_1
         ,(
           SELECT 
               CH0011_2.SAIKEN_NO
              ,CH0011_2.SEIKY_KEI_NO
              ,CH0011_2.SAIKEN_YM
              ,CH0201.JUTO_AMNT
              ,CH0111.OUT_ORG_SHUNO_YMD
           FROM
               CH_T_SAIKEN CH0011_2
           INNER JOIN
               (
                SELECT
                    CH0201_SUB.JUTO_AMNT
                   ,(
                     CASE 
                         WHEN CH0201_SUB.NYUKIN_NO IS NOT NULL THEN CH0201_SUB.NYUKIN_NO
                         ELSE CH0191.NYUKIN_NO 
                     END
                    ) AS NYUKIN_NO
                   ,CH0201_SUB.SAIKEN_NO
                FROM
                    CH_T_JUTO CH0201_SUB
                LEFT OUTER JOIN 
                    CH_T_KNK CH0191
                ON
                        CH0191.KNK_NO  = CH0201_SUB.KNK_NO
                    AND CH0201_SUB.MK_FLG = '0'
                    AND CH0191.MK_FLG     = '0'
               ) CH0201
           ON
                   CH0201.SAIKEN_NO = CH0011_2.SAIKEN_NO 
               AND CH0011_2.SAIKEN_STAT IN (?, ?) 
               AND CH0011_2.MK_FLG  = '0' 
           INNER JOIN
               CH_T_NYUKIN CH0111
           ON
                   CH0111.NYUKIN_NO = CH0201.NYUKIN_NO 
               AND CH0111.MK_FLG    = '0'
          ) SHUNO
         ,(
             SELECT
                 CH0401_2_SUB.PRC_GRP_CD
                ,CH0401_2_SUB.PCRS_CD
                ,CH0401_2_SUB.PRC_SVC_CD
                ,CH0401_2_SUB.PRC_KMK_CD
                ,CH0401_2_SUB.OUT_PRC_KMK_CD
             FROM
                 (
                  SELECT
                      ZM0321_2_SUB.WORK_PARAM_SETTE_VALUE
                  FROM 
                      ZM_M_WORK_PARAM_KNRI ZM0321_2_SUB
                  WHERE
                          ZM0321_2_SUB.WORK_PARAM_ID        = ?
                      AND ? BETWEEN ZM0321_2_SUB.WORK_PARAM_TSTAYMD AND ZM0321_2_SUB.WORK_PARAM_TENDYMD
                      AND ZM0321_2_SUB.MK_FLG              = '0'
                 ) ZM0321_2
                ,CH_M_PRC_KMK_CS_CHGE CH0401_2_SUB
             WHERE
                     CH0401_2_SUB.SYS_CD            = ?
                 AND CH0401_2_SUB.WORK_KINO_SKBT_CD = ZM0321_2.WORK_PARAM_SETTE_VALUE
                 AND CH0401_2_SUB.CHSHT_CHG_CD      = ?
                 AND ? BETWEEN CH0401_2_SUB.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401_2_SUB.PRC_KMK_CS_CHGE_TENDYMD 
                 AND CH0401_2_SUB.MK_FLG            = '0'
          ) CH0401_2
         ,(
             SELECT
                 CH0021_2.SAIKEN_NO
                ,CH0021_2.PRC_GRP_CD
                ,CH0021_2.PCRS_CD
                ,CH0021_2.PRC_SVC_CD
                ,CH0021_2.PRC_KMK_CD
                ,CH0021_2.AMNT
             FROM
                 CH_T_SAIKEN_UCWK CH0021_2
             WHERE
                 CH0021_2.MK_FLG = '0'
          ) CH0021_3
        WHERE
                CH0401_2.PRC_GRP_CD = CH0021_3.PRC_GRP_CD
            AND CH0401_2.PCRS_CD    = CH0021_3.PCRS_CD
            AND CH0401_2.PRC_SVC_CD = CH0021_3.PRC_SVC_CD
            AND CH0401_2.PRC_KMK_CD = CH0021_3.PRC_KMK_CD
            AND SHUNO.SAIKEN_NO     = CH0021_3.SAIKEN_NO
            AND SHUNO.OUT_ORG_SHUNO_YMD > CH0501_1.KAIK_YMD
     )
     UNION ALL
     (
      SELECT
          KASHI_1.SEIKY_KEI_NO
         ,KASHI_1.SAIKEN_YM
         ,KASHI_1.PRC_GRP_CD
         ,KASHI_1.PCRS_CD
         ,KASHI_1.PRC_SVC_CD
         ,KASHI_1.PRC_KMK_CD
         ,KASHI_1.AMNT
      FROM
          (
           SELECT
               CH0011_3.SEIKY_KEI_NO
              ,CH0011_3.SAIKEN_YM
              ,KASHI_SUB.PRC_GRP_CD
              ,KASHI_SUB.PCRS_CD
              ,KASHI_SUB.PRC_SVC_CD
              ,KASHI_SUB.PRC_KMK_CD
              ,KASHI_SUB.AMNT
           FROM
               (
                SELECT
                    CH0401_2.PRC_GRP_CD
                   ,CH0401_2.PCRS_CD
                   ,CH0401_2.PRC_SVC_CD
                   ,CH0401_2.OUT_PRC_KMK_CD AS PRC_KMK_CD
                   ,CH0021_3.AMNT
                   ,CH0021_3.SAIKEN_NO
                   ,CH0501_2.KAIK_YMD
                FROM
                    (
                     SELECT
                         CH0401_2_SUB.PRC_GRP_CD
                        ,CH0401_2_SUB.PCRS_CD
                        ,CH0401_2_SUB.PRC_SVC_CD
                        ,CH0401_2_SUB.PRC_KMK_CD
                        ,CH0401_2_SUB.OUT_PRC_KMK_CD
                     FROM
                         (
                          SELECT
                              ZM0321_2_SUB.WORK_PARAM_SETTE_VALUE
                          FROM 
                              ZM_M_WORK_PARAM_KNRI ZM0321_2_SUB
                          WHERE
                                  ZM0321_2_SUB.WORK_PARAM_ID        = ?
                              AND ? BETWEEN ZM0321_2_SUB.WORK_PARAM_TSTAYMD AND ZM0321_2_SUB.WORK_PARAM_TENDYMD
                              AND ZM0321_2_SUB.MK_FLG              = '0'
                         ) ZM0321_2
                        ,CH_M_PRC_KMK_CS_CHGE CH0401_2_SUB
                     WHERE
                             CH0401_2_SUB.SYS_CD            = ?
                         AND CH0401_2_SUB.WORK_KINO_SKBT_CD = ZM0321_2.WORK_PARAM_SETTE_VALUE
                         AND CH0401_2_SUB.CHSHT_CHG_CD      = ?
                         AND ? BETWEEN CH0401_2_SUB.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401_2_SUB.PRC_KMK_CS_CHGE_TENDYMD 
                         AND CH0401_2_SUB.MK_FLG            = '0'
                    ) CH0401_2
                   ,(
                     SELECT 
                         MIN(CH0501_2_SUB.SEIKY_YM) || 99 AS KAIK_YMD
                     FROM
                         CH_M_PRC_SCHDL_TEIGI CH0501_2_SUB
                     WHERE
                             CH0501_2_SUB.EVENT_CD   = ?
                         AND CH0501_2_SUB.EVENT_YMD >= ?
                         AND CH0501_2_SUB.MK_FLG     = '0'
                    ) CH0501_2
                   ,(
                     SELECT
                         CH0021_2.SAIKEN_NO
                        ,CH0021_2.PRC_GRP_CD
                        ,CH0021_2.PCRS_CD
                        ,CH0021_2.PRC_SVC_CD
                        ,CH0021_2.PRC_KMK_CD
                        ,CH0021_2.AMNT
                     FROM
                         CH_T_SAIKEN_UCWK CH0021_2
                     WHERE
                         CH0021_2.MK_FLG = '0'
                    ) CH0021_3
                WHERE
                        CH0401_2.PRC_GRP_CD = CH0021_3.PRC_GRP_CD
                    AND CH0401_2.PCRS_CD    = CH0021_3.PCRS_CD
                    AND CH0401_2.PRC_SVC_CD = CH0021_3.PRC_SVC_CD
                    AND CH0401_2.PRC_KMK_CD = CH0021_3.PRC_KMK_CD
               ) KASHI_SUB
              ,CH_T_SAIKEN CH0011_3
           WHERE
                   CH0011_3.SAIKEN_SHOKYAKU_YMD  > KASHI_SUB.KAIK_YMD
               AND KASHI_SUB.SAIKEN_NO           = CH0011_3.SAIKEN_NO
               AND CH0011_3.SAIKEN_STAT          = ?
               AND CH0011_3.MK_FLG               = '0'
          ) KASHI_1
     )
     UNION ALL
     (
      SELECT
          KESHI.SEIKY_KEI_NO
         ,KESHI.SAIKEN_YM
         ,KESHI.PRC_GRP_CD
         ,KESHI.PCRS_CD
         ,KESHI.PRC_SVC_CD
         ,KESHI.PRC_KMK_CD
         ,KESHI.AMNT
      FROM
          (
           SELECT
               CH0011_4.SEIKY_KEI_NO
              ,CH0011_4.SAIKEN_YM
              ,CH0011_4.SAIKEN_SHOKYAKU_YMD
              ,KESHI_2.PRC_GRP_CD
              ,KESHI_2.PCRS_CD
              ,KESHI_2.PRC_SVC_CD
              ,KESHI_2.PRC_KMK_CD
              ,KESHI_2.AMNT
           FROM
               (
                SELECT
                    CH0401_3.PRC_GRP_CD
                   ,CH0401_3.PCRS_CD
                   ,CH0401_3.PRC_SVC_CD
                   ,CH0401_3.OUT_PRC_KMK_CD AS PRC_KMK_CD
                   ,CH0501_3.KAIK_YMD
                   ,KESHI_1.SAIKEN_NO
                   ,KESHI_1.AMNT
                FROM
                    (
                     SELECT
                         CH0401_3_SUB.PRC_GRP_CD
                        ,CH0401_3_SUB.PCRS_CD
                        ,CH0401_3_SUB.PRC_SVC_CD
                        ,CH0401_3_SUB.PRC_KMK_CD
                        ,CH0401_3_SUB.OUT_PRC_KMK_CD
                     FROM
                         (
                          SELECT 
                              ZM0321_3_SUB.WORK_PARAM_SETTE_VALUE
                          FROM
                              ZM_M_WORK_PARAM_KNRI ZM0321_3_SUB
                          WHERE
                                    ZM0321_3_SUB.WORK_PARAM_ID        = ?
                                AND ? BETWEEN ZM0321_3_SUB.WORK_PARAM_TSTAYMD AND ZM0321_3_SUB.WORK_PARAM_TENDYMD 
                                AND ZM0321_3_SUB.MK_FLG = '0'
                         ) ZM0321_3
                        ,CH_M_PRC_KMK_CS_CHGE CH0401_3_SUB
                     WHERE
                             CH0401_3_SUB.SYS_CD            = ?
                         AND CH0401_3_SUB.WORK_KINO_SKBT_CD = ZM0321_3.WORK_PARAM_SETTE_VALUE
                         AND CH0401_3_SUB.CHSHT_CHG_CD      = ?
                         AND ? BETWEEN  CH0401_3_SUB.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401_3_SUB.PRC_KMK_CS_CHGE_TENDYMD 
                         AND CH0401_3_SUB.MK_FLG            = '0'
                    ) CH0401_3
                   ,(
                     SELECT
                         MIN(CH0501_3_SUB.SEIKY_YM) || 99 AS KAIK_YMD
                     FROM
                         CH_M_PRC_SCHDL_TEIGI CH0501_3_SUB
                     WHERE
                             CH0501_3_SUB.EVENT_CD   = ?
                         AND CH0501_3_SUB.EVENT_YMD >= ?
                         AND CH0501_3_SUB.MK_FLG     = '0' 
                    ) CH0501_3
                   ,(
                     SELECT
                         CH0021_3.PRC_GRP_CD
                        ,CH0021_3.PCRS_CD
                        ,CH0021_3.PRC_SVC_CD
                        ,CH0021_3.PRC_KMK_CD
                        ,CH0021_3.AMNT
                        ,CH0021_3.SAIKEN_NO
                     FROM
                         CH_T_SAIKEN_UCWK CH0021_3
                     WHERE
                         CH0021_3.MK_FLG = '0'
                    ) KESHI_1
                WHERE
                        KESHI_1.PRC_GRP_CD = CH0401_3.PRC_GRP_CD 
                    AND KESHI_1.PCRS_CD    = CH0401_3.PCRS_CD 
                    AND KESHI_1.PRC_SVC_CD = CH0401_3.PRC_SVC_CD 
                    AND KESHI_1.PRC_KMK_CD = CH0401_3.PRC_KMK_CD 
               ) KESHI_2
              ,CH_T_SAIKEN CH0011_4
           WHERE
                   CH0011_4.SAIKEN_NO           = KESHI_2.SAIKEN_NO
               AND CH0011_4.SAIKEN_SHOKYAKU_YMD > KESHI_2.KAIK_YMD
               AND CH0011_4.SAIKEN_STAT         = ?
               AND CH0011_4.MK_FLG              = '0'
          ) KESHI
     )
    )MINO_ALL
ORDER BY
     SIKY_MT_COMP_CD ASC
    ,MINO_ALL.PRC_GRP_CD ASC
    ,MINO_ALL.PCRS_CD ASC
    ,MINO_ALL.PRC_SVC_CD ASC
    ,SEIKY_YM ASC

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_007)
#--【合算請求対象内訳情報作成】
#-----------------------------------------------------------

--KEY:CH_SELECT_007

SELECT
     CH0021.PRC_GRP_CD
    ,CH0021.PCRS_CD
    ,CH0021.PRC_SVC_CD
    ,CH0021.PRC_KMK_CD
    ,CH0021.AMNT
    ,CH0011.SAIKEN_YM
    ,CH0011.SAIKEN_NO
FROM
    CH_T_SAIKEN CH0011
    INNER JOIN CH_T_SAIKEN_UCWK CH0021
        ON  CH0011.SAIKEN_NO = CH0021.SAIKEN_NO
        AND CH0011.SAIKEN_YM = CH0021.SAIKEN_FIX_YM
WHERE
        CH0011.SEIKY_KEI_NO   = ?
    AND CH0011.SAIKEN_YM      < ?
    AND CH0011.SAIKEN_STAT in (?,?,?,?) 
    AND CH0011.SEIKY_SBT_CD   = ?
    AND CH0011.MK_FLG         = '0'
    AND CH0021.SAIKEN_FIX_YM  < ?
    AND CH0021.MK_FLG         = '0'
    AND CH0021.PRC_GRP_CD     = ?
    AND CH0021.PCRS_CD        = ?
    AND CH0021.PRC_SVC_CD     = ?
    AND CH0021.PRC_KMK_CD     = ?
ORDER BY
    CH0011.SAIKEN_NO
   ,CH0011.SAIKEN_YM

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_008)
#--【強制窓口登録情報作成処理】
#-----------------------------------------------------------

--KEY:CH_SELECT_008

SELECT DISTINCT
     CH0011.SEIKY_KEI_NO
    ,CH0011.SEIKY_WAY_CD
    ,CH0051.SEIKY_YM
    ,CH0051.KHRI_RSLT_CD
    ,CH0101.AMNT
FROM
    CH_T_SAIKEN CH0011
    INNER JOIN CH_T_SEIKY_SKN_KNRN CH0041
        ON  CH0011.SAIKEN_NO = CH0041.SAIKEN_NO
    INNER JOIN CH_T_SEIKY CH0051
        ON  CH0041.SEIKY_NO = CH0051.SEIKY_NO 
    INNER JOIN CH_T_SEIKY_UCWK CH0101
        ON  CH0051.SEIKY_NO = CH0101.SEIKY_NO
        AND CH0051.SEIKY_KEI_NO = CH0101.SEIKY_KEI_NO
        AND CH0051.SEIKY_YM = CH0101.SEIKY_YM
        AND CH0101.PRC_GRP_CD = ?
        AND CH0101.PCRS_CD    = ?
        AND CH0101.PRC_SVC_CD = ?
        AND CH0101.PRC_KMK_CD = ?
WHERE
        CH0011.SEIKY_KEI_NO   = ?
    AND CH0011.SAIKEN_YM < TO_CHAR(ADD_MONTHS(TO_DATE(?, 'YYYYMM'), -1), 'YYYYMM')
    AND CH0011.SAIKEN_STAT in (?,?) 
    AND CH0051.SEIKY_SBT_CD   = ?
    AND CH0011.MK_FLG         = '0'
    AND CH0041.MK_FLG         = '0'
    AND CH0051.MK_FLG         = '0'
    AND CH0101.MK_FLG         = '0'
ORDER BY
    CH0051.SEIKY_YM DESC

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_009)
#--【督促ステータス遷移更新】
#-----------------------------------------------------------

--KEY:CH_SELECT_009

SELECT
    CH0011.SAIKEN_NO
FROM
    CH_T_SAIKEN CH0011
    INNER JOIN CH_T_SAIKEN_UCWK CH0021
        ON  CH0011.SAIKEN_NO = CH0021.SAIKEN_NO
        AND CH0011.SAIKEN_YM = CH0021.SAIKEN_FIX_YM
WHERE
        CH0011.SEIKY_KEI_NO   = ?
    AND CH0011.SAIKEN_YM      < ?
    AND CH0011.SAIKEN_STAT in (?,?,?,?) 
    AND CH0011.MK_FLG         = '0'
    AND CH0021.SAIKEN_FIX_YM  < ?
    AND CH0021.MK_FLG         = '0'
    AND CH0021.PRC_GRP_CD     = ?
    AND CH0021.PCRS_CD        = ?
    AND CH0021.PRC_SVC_CD     = ?
    AND CH0021.PRC_KMK_CD     = ?
ORDER BY
    CH0011.SAIKEN_NO
   ,CH0011.SAIKEN_YM

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_010)
#--【督促処理】 
#--前月以前の未納債権を取得する。
#-----------------------------------------------------------

--KEY:CH_SELECT_010

SELECT 
    CH0011.SAIKEN_YM 
    ,CH0021.AMNT 
FROM 
    CH_T_SAIKEN CH0011 
    INNER JOIN CH_T_SAIKEN_UCWK CH0021 
        ON  CH0011.SAIKEN_NO = CH0021.SAIKEN_NO 
    INNER JOIN 
    ( 
        SELECT 
            CH0401_SUB.PRC_GRP_CD 
            ,CH0401_SUB.PCRS_CD
            ,CH0401_SUB.PRC_SVC_CD 
            ,CH0401_SUB.PRC_KMK_CD 
        FROM 
            CH_M_PRC_KMK_CS_CHGE CH0401_SUB 
        WHERE 
            CH0401_SUB.WORK_KINO_SKBT_CD = ?
            AND CH0401_SUB.SYS_CD = ? 
            AND CH0401_SUB.CHSHT_CHG_CD = ? 
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TSTAYMD <= ? 
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TENDYMD >= ? 
            AND CH0401_SUB.MK_FLG = '0' 
    ) CH0401 
        ON  CH0021.PRC_GRP_CD = CH0401.PRC_GRP_CD
        AND CH0021.PCRS_CD = CH0401.PCRS_CD 
        AND CH0021.PRC_SVC_CD = CH0401.PRC_SVC_CD 
        AND CH0021.PRC_KMK_CD = CH0401.PRC_KMK_CD 
WHERE 
    CH0011.SEIKY_KEI_NO = ?
    AND CH0011.SAIKEN_YM < ?
    AND CH0011.SAIKEN_STAT in (?,?) 
    AND CH0011.MK_FLG = '0' 
    AND CH0021.MK_FLG = '0' 
ORDER BY
    CH0011.SAIKEN_YM    ASC

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_011)
#--【督促処理】 
#--当月と前月の債権情報を取得する。
#-----------------------------------------------------------

--KEY:CH_SELECT_011

SELECT
    CH0011.SAIKEN_YM
    ,CH0011.SAIKEN_STAT
    ,CH0011.SEIKY_WAY_CD
FROM
    CH_T_SAIKEN CH0011
WHERE
    CH0011.SEIKY_KEI_NO = ?
    AND CH0011.SAIKEN_YM IN( ? , TO_CHAR(ADD_MONTHS(TO_DATE(?, 'YYYYMM'), -1), 'YYYYMM'))
    AND CH0011.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_012)
#--【督促処理】 
#--買取不可債権を保有する債権を検索する。
#-----------------------------------------------------------

--KEY:CH_SELECT_012

SELECT
    CH0011.SAIKEN_YM
FROM
    CH_T_SAIKEN CH0011
WHERE
    CH0011.SEIKY_KEI_NO = ?
    AND CH0011.SAIKEN_YM < ?
    AND CH0011.SAIKEN_TORIATSU_CD != ?
    AND CH0011.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_013)
#--【債権切り離し対象情報作成】
#--未納債権の取得。
#-----------------------------------------------------------

--KEY:CH_SELECT_013

SELECT
     CH0011.SAIKEN_YM
   , CH0231.TOKUSOKU_STAT
FROM
    CH_T_SAIKEN CH0011
    INNER JOIN
        CH_T_TOKUSK_SKN_KNRN CH0341
    ON CH0011.SAIKEN_NO = CH0341.SAIKEN_NO
    INNER JOIN
        CH_T_TOKUSOKU CH0231
    ON CH0341.TOKUSOKU_NO = CH0231.TOKUSOKU_NO
WHERE
        CH0011.SEIKY_KEI_NO = ?
    AND CH0011.SAIKEN_STAT IN (?,?,?)
    AND CH0011.SAIKEN_TORIATSU_CD <> ?
    AND CH0011.SAIKEN_YM <= ?
    AND CH0231.TOKUSOKU_NO = ?
    AND (CH0231.TOKUSOKU_STAT >= ? OR
         CH0011.SAIKEN_YM <= TO_CHAR(ADD_MONTHS(TO_DATE(?, 'YYYYMM'), ?), 'YYYYMM'))
    AND CH0011.MK_FLG = '0'
    AND CH0341.MK_FLG = '0'
    AND CH0231.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_014)
#--【貸倒れ対象者抽出】
#--未収債権の取得。
#-----------------------------------------------------------

--KEY:CH_SELECT_014

SELECT
     CH0021.PRC_GRP_CD
    ,CH0021.SAIKEN_FIX_YM
    ,SUM(CH0021.KAZEI_AMNT)   AS KAZEI_AMNT
    ,SUM(CH0021.HIKAZEI_AMNT) AS HIKAZEI_AMNT
    ,MAX(CH0021.TOKUSOKU_STAT)  AS TOKUSOKU_STAT
FROM
    (
     SELECT /*+ INDEX(CH0011_SUB1 CH_T_SAIKEN_IDX_01) INDEX(CH0021_SUB1 CH_T_SAIKEN_UCWK_IDX_02) */
          CH0021_SUB1.PRC_GRP_CD
         ,CH0021_SUB1.SAIKEN_FIX_YM
         ,CH0021_SUB1.AMNT  AS KAZEI_AMNT
         ,0                 AS HIKAZEI_AMNT
         ,CH0231_SUB1.TOKUSOKU_STAT
     FROM
         CH_T_SAIKEN CH0011_SUB1
         INNER JOIN CH_T_SAIKEN_UCWK CH0021_SUB1
             ON  CH0011_SUB1.SAIKEN_NO  = CH0021_SUB1.SAIKEN_NO
         INNER JOIN CH_M_PRC_KMK_CS_CHGE CH0401_SUB1
             ON  CH0021_SUB1.PRC_GRP_CD = CH0401_SUB1.PRC_GRP_CD
             AND CH0021_SUB1.PCRS_CD    = CH0401_SUB1.PCRS_CD
             AND CH0021_SUB1.PRC_SVC_CD = CH0401_SUB1.PRC_SVC_CD
             AND CH0021_SUB1.PRC_KMK_CD = CH0401_SUB1.PRC_KMK_CD
         INNER JOIN
             (SELECT
                   CH0401_GENE_SUB1.SYS_CD
                  ,CH0401_GENE_SUB1.WORK_KINO_SKBT_CD
                  ,CH0401_GENE_SUB1.CHSHT_CHG_CD
                  ,CH0401_GENE_SUB1.PRC_GRP_CD
                  ,CH0401_GENE_SUB1.PCRS_CD
                  ,CH0401_GENE_SUB1.PRC_SVC_CD
                  ,CH0401_GENE_SUB1.PRC_KMK_CD
                  ,MAX(CH0401_GENE_SUB1.GENE_ADD_DTM) AS MAX_GENE_DTM
              FROM
                  CH_M_PRC_KMK_CS_CHGE CH0401_GENE_SUB1
              WHERE
                      CH0401_GENE_SUB1.SYS_CD                  =  ?
                  AND CH0401_GENE_SUB1.WORK_KINO_SKBT_CD       =  ?
                  AND CH0401_GENE_SUB1.CHSHT_CHG_CD            =  ?
                  AND CH0401_GENE_SUB1.PRC_KMK_CS_CHGE_TSTAYMD <= ?
                  AND CH0401_GENE_SUB1.PRC_KMK_CS_CHGE_TENDYMD >= ?
                  AND CH0401_GENE_SUB1.RSV_APLY_YMD            <= ?
                  AND CH0401_GENE_SUB1.MK_FLG                  =  '0'
              GROUP BY
                   CH0401_GENE_SUB1.SYS_CD
                  ,CH0401_GENE_SUB1.WORK_KINO_SKBT_CD
                  ,CH0401_GENE_SUB1.CHSHT_CHG_CD
                  ,CH0401_GENE_SUB1.PRC_GRP_CD
                  ,CH0401_GENE_SUB1.PCRS_CD
                  ,CH0401_GENE_SUB1.PRC_SVC_CD
                  ,CH0401_GENE_SUB1.PRC_KMK_CD
            ) CH0401_GENE1
            ON  CH0401_SUB1.SYS_CD            = CH0401_GENE1.SYS_CD
            AND CH0401_SUB1.WORK_KINO_SKBT_CD = CH0401_GENE1.WORK_KINO_SKBT_CD
            AND CH0401_SUB1.CHSHT_CHG_CD      = CH0401_GENE1.CHSHT_CHG_CD
            AND CH0401_SUB1.PRC_GRP_CD        = CH0401_GENE1.PRC_GRP_CD
            AND CH0401_SUB1.PCRS_CD           = CH0401_GENE1.PCRS_CD
            AND CH0401_SUB1.PRC_SVC_CD        = CH0401_GENE1.PRC_SVC_CD
            AND CH0401_SUB1.PRC_KMK_CD        = CH0401_GENE1.PRC_KMK_CD
            AND CH0401_SUB1.GENE_ADD_DTM      = CH0401_GENE1.MAX_GENE_DTM
         INNER JOIN CH_T_TOKUSK_SKN_KNRN CH0341_SUB1
             ON  CH0011_SUB1.SAIKEN_NO    = CH0341_SUB1.SAIKEN_NO
         INNER JOIN CH_T_TOKUSOKU CH0231_SUB1
             ON  CH0341_SUB1.TOKUSOKU_NO  = CH0231_SUB1.TOKUSOKU_NO
     WHERE
             CH0011_SUB1.SEIKY_KEI_NO  = ?
         AND CH0011_SUB1.SAIKEN_STAT  IN (?,?)
         AND CH0021_SUB1.SAIKEN_FIX_YM < ?
         AND CH0021_SUB1.AMNT          > 0
         AND CH0231_SUB1.TOKUSOKU_STAYMD <= ?
         AND CH0231_SUB1.TOKUSOKU_ENDYMD > ?
         AND CH0011_SUB1.MK_FLG        = '0'
         AND CH0021_SUB1.MK_FLG        = '0'
         AND CH0341_SUB1.MK_FLG        = '0'
         AND CH0231_SUB1.MK_FLG        = '0'
    UNION ALL
     SELECT /*+ INDEX(CH0011_SUB2 CH_T_SAIKEN_IDX_01) INDEX(CH0021_SUB2 CH_T_SAIKEN_UCWK_IDX_02) */
          CH0021_SUB2.PRC_GRP_CD
         ,CH0021_SUB2.SAIKEN_FIX_YM
         ,0                 AS KAZEI_AMNT
         ,CH0021_SUB2.AMNT  AS HIKAZEI_AMNT
         ,CH0231_SUB2.TOKUSOKU_STAT
     FROM
         CH_T_SAIKEN CH0011_SUB2
         INNER JOIN CH_T_SAIKEN_UCWK CH0021_SUB2
             ON  CH0011_SUB2.SAIKEN_NO  = CH0021_SUB2.SAIKEN_NO
         INNER JOIN CH_M_PRC_KMK_CS_CHGE CH0401_SUB2
             ON  CH0021_SUB2.PRC_GRP_CD = CH0401_SUB2.PRC_GRP_CD
             AND CH0021_SUB2.PCRS_CD    = CH0401_SUB2.PCRS_CD
             AND CH0021_SUB2.PRC_SVC_CD = CH0401_SUB2.PRC_SVC_CD
             AND CH0021_SUB2.PRC_KMK_CD = CH0401_SUB2.PRC_KMK_CD
         INNER JOIN
             (SELECT
                   CH0401_GENE_SUB2.SYS_CD
                  ,CH0401_GENE_SUB2.WORK_KINO_SKBT_CD
                  ,CH0401_GENE_SUB2.CHSHT_CHG_CD
                  ,CH0401_GENE_SUB2.PRC_GRP_CD
                  ,CH0401_GENE_SUB2.PCRS_CD
                  ,CH0401_GENE_SUB2.PRC_SVC_CD
                  ,CH0401_GENE_SUB2.PRC_KMK_CD
                  ,MAX(CH0401_GENE_SUB2.GENE_ADD_DTM) AS MAX_GENE_DTM
              FROM
                  CH_M_PRC_KMK_CS_CHGE CH0401_GENE_SUB2
              WHERE
                      CH0401_GENE_SUB2.SYS_CD                  =  ?
                  AND CH0401_GENE_SUB2.WORK_KINO_SKBT_CD       =  ?
                  AND CH0401_GENE_SUB2.CHSHT_CHG_CD            =  ?
                  AND CH0401_GENE_SUB2.PRC_KMK_CS_CHGE_TSTAYMD <= ?
                  AND CH0401_GENE_SUB2.PRC_KMK_CS_CHGE_TENDYMD >= ?
                  AND CH0401_GENE_SUB2.RSV_APLY_YMD            <= ?
                  AND CH0401_GENE_SUB2.MK_FLG                  =  '0'
              GROUP BY
                   CH0401_GENE_SUB2.SYS_CD
                  ,CH0401_GENE_SUB2.WORK_KINO_SKBT_CD
                  ,CH0401_GENE_SUB2.CHSHT_CHG_CD
                  ,CH0401_GENE_SUB2.PRC_GRP_CD
                  ,CH0401_GENE_SUB2.PCRS_CD
                  ,CH0401_GENE_SUB2.PRC_SVC_CD
                  ,CH0401_GENE_SUB2.PRC_KMK_CD
            ) CH0401_GENE2
            ON  CH0401_SUB2.SYS_CD            = CH0401_GENE2.SYS_CD
            AND CH0401_SUB2.WORK_KINO_SKBT_CD = CH0401_GENE2.WORK_KINO_SKBT_CD
            AND CH0401_SUB2.CHSHT_CHG_CD      = CH0401_GENE2.CHSHT_CHG_CD
            AND CH0401_SUB2.PRC_GRP_CD        = CH0401_GENE2.PRC_GRP_CD
            AND CH0401_SUB2.PCRS_CD           = CH0401_GENE2.PCRS_CD
            AND CH0401_SUB2.PRC_SVC_CD        = CH0401_GENE2.PRC_SVC_CD
            AND CH0401_SUB2.PRC_KMK_CD        = CH0401_GENE2.PRC_KMK_CD
            AND CH0401_SUB2.GENE_ADD_DTM      = CH0401_GENE2.MAX_GENE_DTM
         INNER JOIN CH_T_TOKUSK_SKN_KNRN CH0341_SUB2
             ON  CH0011_SUB2.SAIKEN_NO    = CH0341_SUB2.SAIKEN_NO
         INNER JOIN CH_T_TOKUSOKU CH0231_SUB2
             ON  CH0341_SUB2.TOKUSOKU_NO  = CH0231_SUB2.TOKUSOKU_NO
     WHERE
             CH0011_SUB2.SEIKY_KEI_NO  = ?
         AND CH0011_SUB2.SAIKEN_STAT  IN (?,?)
         AND CH0021_SUB2.SAIKEN_FIX_YM < ?
         AND CH0021_SUB2.AMNT          > 0
         AND CH0231_SUB2.TOKUSOKU_STAYMD <= ?
         AND CH0231_SUB2.TOKUSOKU_ENDYMD > ?
         AND CH0011_SUB2.MK_FLG        = '0'
         AND CH0021_SUB2.MK_FLG        = '0'
         AND CH0341_SUB2.MK_FLG        = '0'
         AND CH0231_SUB2.MK_FLG        = '0'
    ) CH0021
GROUP BY
     CH0021.PRC_GRP_CD
    ,CH0021.SAIKEN_FIX_YM

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_015)
#--【債権情報取得処理】
#--未収債権の取得
#-----------------------------------------------------------

--KEY:CH_SELECT_015

SELECT 
     CH0011.SAIKEN_NO 
    ,CH0021.SAIKEN_FIX_YM
    ,CH0021.AMNT
    ,CH0011.PAY_KIGEN_YMD
    ,CH0011.CHG_PAY_KIGEN_YMD
FROM 
    CH_T_SAIKEN CH0011
    INNER JOIN
        (
        SELECT
             CH0021_SUB.SAIKEN_FIX_YM
            ,CH0021_SUB.SAIKEN_NO
            ,CH0021_SUB.AMNT
        FROM
            CH_T_SAIKEN_UCWK CH0021_SUB
            INNER JOIN
                (
                  SELECT
                       CH0401_GENE.SYS_CD
                       ,CH0401_GENE.WORK_KINO_SKBT_CD
                       ,CH0401_GENE.CHSHT_CHG_CD
                       ,CH0401_GENE.PRC_GRP_CD
                       ,CH0401_GENE.PCRS_CD
                       ,CH0401_GENE.PRC_SVC_CD
                       ,CH0401_GENE.PRC_KMK_CD
                  FROM
                       CH_M_PRC_KMK_CS_CHGE CH0401_GENE
                  WHERE
                       CH0401_GENE.SYS_CD                       = ?
                       AND CH0401_GENE.WORK_KINO_SKBT_CD        = ?
                       AND CH0401_GENE.CHSHT_CHG_CD             = ?
                       AND CH0401_GENE.RSV_APLY_YMD            <= ?
                       AND CH0401_GENE.PRC_KMK_CS_CHGE_TSTAYMD <= ?
                       AND CH0401_GENE.PRC_KMK_CS_CHGE_TENDYMD >= ?
                       AND CH0401_GENE.MK_FLG                   = '0' 
                  GROUP BY
                       CH0401_GENE.SYS_CD
                       ,CH0401_GENE.WORK_KINO_SKBT_CD
                       ,CH0401_GENE.CHSHT_CHG_CD
                       ,CH0401_GENE.PRC_GRP_CD
                       ,CH0401_GENE.PCRS_CD
                       ,CH0401_GENE.PRC_SVC_CD
                       ,CH0401_GENE.PRC_KMK_CD
                ) CH0401
            ON 
                CH0021_SUB.PRC_GRP_CD     = CH0401.PRC_GRP_CD
                AND CH0021_SUB.PCRS_CD    = CH0401.PCRS_CD
                AND CH0021_SUB.PRC_SVC_CD = CH0401.PRC_SVC_CD
                AND CH0021_SUB.PRC_KMK_CD = CH0401.PRC_KMK_CD
        WHERE
            CH0021_SUB.SAIKEN_FIX_YM < ?
            AND CH0021_SUB.MK_FLG    = '0'
        ) CH0021
        ON 
            CH0011.SAIKEN_NO = CH0021.SAIKEN_NO
        AND CH0011.SAIKEN_YM = CH0021.SAIKEN_FIX_YM
WHERE
    CH0011.SEIKY_KEI_NO      = ?
    AND CH0011.SAIKEN_STAT  IN (?, ?)
    AND CH0011.MK_FLG        = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_016)
#--【支払約束履行者リスト作成】
#--当月債権状況取得
#-----------------------------------------------------------

--KEY:CH_SELECT_016

SELECT 
    CH0011.SAIKEN_STAT
FROM 
    CH_T_SAIKEN CH0011
WHERE 
    CH0011.SEIKY_KEI_NO  =  ?
    AND CH0011.SAIKEN_YM =  ?
    AND CH0011.MK_FLG    = '0' 

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_017)
#--【支払約束不履行者リスト作成】
#--債権状況取得
#-----------------------------------------------------------

--KEY:CH_SELECT_017

SELECT
    CH0021.AMNT
FROM
    CH_T_SAIKEN CH0011
    INNER JOIN CH_T_SAIKEN_UCWK CH0021
        ON  CH0011.SAIKEN_NO = CH0021.SAIKEN_NO
        AND CH0011.SAIKEN_YM = CH0021.SAIKEN_FIX_YM
    INNER JOIN
    (
        SELECT
            CH0401.PCRS_CD
            ,CH0401.PRC_SVC_CD
            ,CH0401.PRC_KMK_CD
        FROM
            CH_M_PRC_KMK_CS_CHGE CH0401
        WHERE
            CH0401.SYS_CD = ?
            AND CH0401.WORK_KINO_SKBT_CD = ?
            AND CH0401.CHSHT_CHG_CD = ?
            AND CH0401.PRC_KMK_CS_CHGE_TSTAYMD <= ?
            AND CH0401.PRC_KMK_CS_CHGE_TENDYMD >= ?
            AND CH0401.MK_FLG = '0'
    ) CH0401
        ON  CH0021.PCRS_CD = CH0401.PCRS_CD
        AND CH0021.PRC_SVC_CD = CH0401.PRC_SVC_CD
        AND CH0021.PRC_KMK_CD = CH0401.PRC_KMK_CD
WHERE
    CH0011.SEIKY_KEI_NO = ?
    AND CH0011.SAIKEN_STAT IN (?,?)
    AND CH0011.SAIKEN_YM = ?
    AND CH0011.MK_FLG = '0'
    AND CH0021.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_018)
#--【督促ステータス一括更新】
#--債権ステータスが収納済みではないデータを取得する。
#-----------------------------------------------------------

--KEY:CH_SELECT_018

SELECT
     CH0011.SAIKEN_NO
   , CH0011.SEIKY_KEI_NO
FROM
    CH_T_SAIKEN CH0011
WHERE
        CH0011.SEIKY_KEI_NO =  ?
    AND CH0011.SAIKEN_STAT  <> ?
    AND CH0011.MK_FLG       =  '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_019)
#--UIA17_CHPRC04301_督促処理.xlsx参照
#-----------------------------------------------------------

--KEY:CH_SELECT_019

SELECT 
    CH0011.SEIKY_KEI_NO 
FROM 
    CH_T_SAIKEN CH0011 
WHERE 
    SAIKEN_YM <
        (SELECT
             CH0501.SEIKY_YM
         FROM
             (SELECT
                 CH0501_SUB.SEIKY_YM
             FROM
                 CH_M_PRC_SCHDL_TEIGI CH0501_SUB
             WHERE
                     CH0501_SUB.EVENT_CD IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                 AND CH0501_SUB.EVENT_YMD = ?
                 AND CH0501_SUB.MK_FLG = '0'
             ORDER BY CH0501_SUB.SEIKY_YM DESC
            )CH0501
         WHERE
             ROWNUM = 1
         )
    AND CH0011.SAIKEN_STAT in (?,?,?,?) 
    AND CH0011.MK_FLG = '0'
GROUP BY
    CH0011.SEIKY_KEI_NO
ORDER BY
    CH0011.SEIKY_KEI_NO

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_020)
#--【未収債権情報抽出＿リアル】
#--入金の消込先データ抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_020

SELECT /*+ USE_NL(CH0111 CH0051 CH0041 CH0011 CH0021) INDEX(CH0111 CH_T_NYUKIN_IDX_08, CH0051 CH_T_SEIKY_PK_01, CH0041 CH_T_SEIKY_SKN_KNRN_IDX_02, CH0011 CH_T_SAIKEN_PK_01, CH0021 CH_T_SAIKEN_UCWK_IDX_02) */
    CH0011.SEIKY_KEI_NO
    ,CH0011.SAIKEN_NO
    ,CH0011.SAIKEN_STAT
    ,CH0011.SAIKEN_YM
    ,CH0021.PRC_GRP_CD
    ,CH0021.PCRS_CD
    ,CH0021.PRC_SVC_CD
    ,CH0021.PRC_KMK_CD
    ,CH0021.AMNT
    ,CH0051.SEIKY_YM
    ,CH0051.SEIKY_NO
    ,(SELECT /*+ INDEX(CH0231 CH_T_TOKUSOKU_IDX_01) */
          CH0231.TOKUSOKU_NO
      FROM
          CH_T_TOKUSOKU CH0231
      WHERE
          CH0231.SEIKY_KEI_NO = CH0011.SEIKY_KEI_NO
          AND CH0231.TOKUSOKU_STAYMD <= ?
          AND CH0231.TOKUSOKU_ENDYMD > ?
          AND CH0231.MK_FLG = '0'
    )TOKUSOKU_NO
    ,CH0111.NYUKIN_NO
    ,CH0111.NYUKIN_ROUTE_CD
    ,CH0111.NYUKIN_STAT
FROM
    CH_T_NYUKIN CH0111
    INNER JOIN CH_T_SEIKY CH0051
        ON  CH0111.SEIKY_NO = CH0051.SEIKY_NO
    INNER JOIN CH_T_SEIKY_SKN_KNRN CH0041
        ON  CH0111.SEIKY_NO = CH0041.SEIKY_NO
        AND CH0051.SEIKY_NO = CH0041.SEIKY_NO
    INNER JOIN CH_T_SAIKEN CH0011
        ON  CH0041.SAIKEN_NO = CH0011.SAIKEN_NO
    INNER JOIN CH_T_SAIKEN_UCWK CH0021
        ON CH0041.SAIKEN_NO = CH0021.SAIKEN_NO
        AND  CH0011.SAIKEN_NO = CH0021.SAIKEN_NO
        AND  CH0011.SAIKEN_YM = CH0021.SAIKEN_FIX_YM
    INNER JOIN
    (
        SELECT /*+ (CH0401_SUB CH_M_PRC_KMK_CS_CHGE_IDX_01, ZM0321 ZM_M_WORK_PARAM_KNRI_PK_01) */
            CH0401_SUB.PRC_GRP_CD
            ,CH0401_SUB.PCRS_CD
            ,CH0401_SUB.PRC_SVC_CD
            ,CH0401_SUB.PRC_KMK_CD
        FROM
            CH_M_PRC_KMK_CS_CHGE CH0401_SUB
            INNER JOIN ZM_M_WORK_PARAM_KNRI ZM0321
                ON  CH0401_SUB.WORK_KINO_SKBT_CD = ZM0321.WORK_PARAM_SETTE_VALUE
        WHERE
            ZM0321.WORK_PARAM_ID = ?
            AND CH0401_SUB.SYS_CD = ?
            AND CH0401_SUB.CHSHT_CHG_CD = ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TSTAYMD <= ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TENDYMD >= ?
            AND CH0401_SUB.MK_FLG = '0'
            AND ZM0321.MK_FLG = '0'
    ) CH0401
        ON  CH0021.PRC_GRP_CD = CH0401.PRC_GRP_CD
        AND CH0021.PCRS_CD = CH0401.PCRS_CD
        AND CH0021.PRC_SVC_CD = CH0401.PRC_SVC_CD
        AND CH0021.PRC_KMK_CD = CH0401.PRC_KMK_CD
WHERE
       CH0111.CVS_REAL_SOKHO_SYRZM_FLG = '0'
   AND CH0111.NYUKIN_ROUTE_CD = '80'
   AND CH0011.SAIKEN_STAT IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
   AND CH0011.MK_FLG = '0'
   AND CH0021.MK_FLG = '0'
   AND CH0051.MK_FLG = '0'
   AND CH0111.MK_FLG = '0'
   AND CH0041.MK_FLG = '0'
ORDER BY
    CH0011.SEIKY_KEI_NO, CH0111.NYUKIN_NO, CH0011.SAIKEN_YM
#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_021)
#--【未収債権情報抽出_速報】
#--入金の消込先データ抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_021

SELECT /*+ USE_NL(CH0111 CH0051 CH0041 CH0011 CH0021) INDEX(CH0111 CH_T_NYUKIN_IDX_08, CH0051 CH_T_SEIKY_PK_01, CH0041 CH_T_SEIKY_SKN_KNRN_IDX_02, CH0011 CH_T_SAIKEN_PK_01, CH0021 CH_T_SAIKEN_UCWK_IDX_02) */
    CH0011.SEIKY_KEI_NO
    ,CH0011.SAIKEN_NO
    ,CH0011.SAIKEN_STAT
    ,CH0011.SAIKEN_YM
    ,CH0021.PRC_GRP_CD
    ,CH0021.PCRS_CD
    ,CH0021.PRC_SVC_CD
    ,CH0021.PRC_KMK_CD
    ,CH0021.AMNT
    ,CH0051.SEIKY_YM
    ,CH0051.SEIKY_NO
    ,(SELECT /*+ INDEX(CH0231 CH_T_TOKUSOKU_IDX_01) */
          CH0231.TOKUSOKU_NO
      FROM
          CH_T_TOKUSOKU CH0231
      WHERE
          CH0231.SEIKY_KEI_NO = CH0011.SEIKY_KEI_NO
          AND CH0231.TOKUSOKU_STAYMD <= ?
          AND CH0231.TOKUSOKU_ENDYMD > ?
          AND CH0231.MK_FLG = '0'
    )TOKUSOKU_NO
    ,CH0111.NYUKIN_NO
    ,CH0111.NYUKIN_ROUTE_CD
    ,CH0111.NYUKIN_STAT
FROM
    CH_T_NYUKIN CH0111
    INNER JOIN CH_T_SEIKY CH0051
        ON  CH0111.SEIKY_NO = CH0051.SEIKY_NO
    INNER JOIN CH_T_SEIKY_SKN_KNRN CH0041
        ON  CH0111.SEIKY_NO = CH0041.SEIKY_NO
        AND CH0051.SEIKY_NO = CH0041.SEIKY_NO
    INNER JOIN CH_T_SAIKEN CH0011
        ON  CH0041.SAIKEN_NO = CH0011.SAIKEN_NO
    INNER JOIN CH_T_SAIKEN_UCWK CH0021
        ON CH0041.SAIKEN_NO = CH0021.SAIKEN_NO
        AND  CH0011.SAIKEN_NO = CH0021.SAIKEN_NO
        AND  CH0011.SAIKEN_YM = CH0021.SAIKEN_FIX_YM
    INNER JOIN
    (
        SELECT /*+ (CH0401_SUB CH_M_PRC_KMK_CS_CHGE_IDX_01, ZM0321 ZM_M_WORK_PARAM_KNRI_PK_01) */
            CH0401_SUB.PRC_GRP_CD
            ,CH0401_SUB.PCRS_CD
            ,CH0401_SUB.PRC_SVC_CD
            ,CH0401_SUB.PRC_KMK_CD
        FROM
            CH_M_PRC_KMK_CS_CHGE CH0401_SUB
            INNER JOIN ZM_M_WORK_PARAM_KNRI ZM0321
                ON  CH0401_SUB.WORK_KINO_SKBT_CD = ZM0321.WORK_PARAM_SETTE_VALUE
        WHERE
            ZM0321.WORK_PARAM_ID = ?
            AND CH0401_SUB.SYS_CD = ?
            AND CH0401_SUB.CHSHT_CHG_CD = ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TSTAYMD <= ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TENDYMD >= ?
            AND CH0401_SUB.MK_FLG = '0'
            AND ZM0321.MK_FLG = '0'
    ) CH0401
        ON  CH0021.PRC_GRP_CD = CH0401.PRC_GRP_CD
        AND CH0021.PCRS_CD = CH0401.PCRS_CD
        AND CH0021.PRC_SVC_CD = CH0401.PRC_SVC_CD
        AND CH0021.PRC_KMK_CD = CH0401.PRC_KMK_CD
WHERE
        CH0111.CVSTORE_SOKHO_SYRZM_FLG = '0'
   AND CH0011.SAIKEN_STAT IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
   AND CH0011.MK_FLG = '0'
   AND CH0021.MK_FLG = '0'
   AND CH0051.MK_FLG = '0'
   AND CH0111.MK_FLG = '0'
   AND CH0041.MK_FLG = '0'
ORDER BY
    CH0011.SEIKY_KEI_NO, CH0111.NYUKIN_NO, CH0011.SAIKEN_YM

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_022)
#--【仮入金取消】
#--請求番号を指定し、収納済データの取得を行う。
#-----------------------------------------------------------

--KEY:CH_SELECT_022

SELECT
    CH0011.SAIKEN_NO , 
    CH0011.SAIKEN_STAT
FROM
    CH_T_SEIKY_SKN_KNRN CH0041
     , CH_T_SAIKEN CH0011
WHERE
        CH0041.SEIKY_NO    = ?
    AND CH0041.SAIKEN_NO   = CH0011.SAIKEN_NO
    AND CH0011.SAIKEN_STAT = ?
    AND CH0041.MK_FLG='0'
    AND CH0011.MK_FLG='0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_023)
#--【当月債権無合算抑止請求データ作成】
#--当月債権有無チェックを行う。
#-----------------------------------------------------------

--KEY:CH_SELECT_023

SELECT
    CH0011.SEIKY_KEI_NO
FROM
    CH_T_SAIKEN CH0011
WHERE
    CH0011.SEIKY_KEI_NO = ?
    AND CH0011.SAIKEN_YM = ?
    AND CH0011.SAIKEN_STAT IN (?, ?)
    AND CH0011.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_024)
#--【請求書発行判定】
#--前月・前々月の未納債権の有無をチェックする。
#-----------------------------------------------------------

--KEY:CH_SELECT_024

SELECT
    CH0011.SAIKEN_NO
    ,CH0011.DATE_SKBT_FLG
FROM
    ((SELECT
        CH0011_ZGT.SEIKY_KEI_NO
        ,CH0011_ZGT.SAIKEN_NO
        ,CH0011_ZGT.SAIKEN_YM
        , '1' AS DATE_SKBT_FLG
    FROM
        CH_T_SAIKEN CH0011_ZGT
    WHERE
        CH0011_ZGT.SEIKY_KEI_NO    = ?
        AND CH0011_ZGT.SAIKEN_YM   = ?
        AND CH0011_ZGT.SAIKEN_STAT IN(?, ?)
        AND CH0011_ZGT.MK_FLG      = '0'
        AND ROWNUM                 <= 1
    )
UNION ALL
    (SELECT
        CH0011_ZZGT.SEIKY_KEI_NO
        ,CH0011_ZZGT.SAIKEN_NO
        ,CH0011_ZZGT.SAIKEN_YM
        , '2' AS DATE_SKBT_FLG
    FROM
        CH_T_SAIKEN CH0011_ZZGT
    WHERE
        CH0011_ZZGT.SEIKY_KEI_NO    = ?
        AND CH0011_ZZGT.SAIKEN_YM   <= ?
        AND CH0011_ZZGT.SAIKEN_STAT IN(?, ?)
        AND CH0011_ZZGT.MK_FLG      = '0'
        AND ROWNUM                 <= 1
    )) CH0011

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_025)
#--【前受金相殺結果反映】
#--債権番号の抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_025

SELECT
      CH0011.SAIKEN_NO
    , CH0011.SAIKEN_FIX_YMD
FROM
    CH_T_SAIKEN CH0011
WHERE
        CH0011.SEIKY_KEI_NO = ?
    AND CH0011.SAIKEN_YM    = ?
    AND CH0011.SEIKY_SBT_CD = ?
    AND CH0011.MK_FLG       = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_026)
#--【請求登録（割賦債権）】
#--当月分債権情報抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_026

SELECT
    CH0011.SEIKY_KEI_NO
    ,CH0011.SAIKEN_YM
    ,CH0011.SAIKEN_NO
FROM
    CH_T_SAIKEN CH0011
WHERE
        CH0011.SEIKY_SBT_CD = ?
    AND CH0011.MK_FLG = '0'
    AND CH0011.SAIKEN_YM = 
        (
            SELECT
                CH0501_SUB.SEIKY_YM
            FROM
                (
                    SELECT
                         CH0501.SEIKY_YM
                    FROM 
                        CH_M_PRC_SCHDL_TEIGI CH0501
                    WHERE
                            CH0501.EVENT_CD   = ?
                        AND CH0501.EVENT_YMD >= ?
                        AND CH0501.MK_FLG     = '0'
                    ORDER BY
                        CH0501.EVENT_YMD
                ) CH0501_SUB
            WHERE
                ROWNUM = 1
        )
ORDER BY
    CH0011.SEIKY_KEI_NO

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_027)
#--【債権情報】
#--債権情報の抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_027

SELECT
     CH0011.SAIKEN_YM ,
     CH0021.AMNT,
     CH0011.SAIKEN_STAT ,
     CH0011.SAIKEN_TORIATSU_CD ,
     CH0011.SEIKY_WAY_CD
 FROM
     CH_T_SAIKEN CH0011
         INNER JOIN (SELECT
                         CH0021_SUB.SAIKEN_NO ,
                         CH0021_SUB.AMNT,
                         CH0021_SUB.SAIKEN_FIX_YM
                     FROM
                         CH_T_SAIKEN_UCWK CH0021_SUB
                     WHERE
                             CH0021_SUB.MK_FLG = '0'
                         AND CH0021_SUB.PRC_GRP_CD = ?
                         AND CH0021_SUB.PCRS_CD = ?
                         AND CH0021_SUB.PRC_SVC_CD = ?
                         AND CH0021_SUB.PRC_KMK_CD = ?
                     ) CH0021
         ON  CH0011.SAIKEN_NO = CH0021.SAIKEN_NO
         AND CH0011.SAIKEN_YM = CH0021.SAIKEN_FIX_YM
 WHERE
     CH0011.SEIKY_KEI_NO = ? AND
     CH0011.SAIKEN_YM <= ? AND
     CH0011.MK_FLG = '0'
 ORDER BY
     CH0011.SAIKEN_YM    ASC

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_028)
#--【累積データ削除】
#--債権が二件以上ある請求番号のレコードを抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_028

SELECT
    CH0011.SEIKY_KEI_NO
   ,CH0011.SAIKEN_YM
   ,CH0011.SAIKEN_NO
   ,CH0011.SAIKEN_STAT
   ,CH0011.JUTO_FIN_YMD
   ,CH0011.SAIKEN_SHOKYAKU_YMD
   ,CH0011_2.SEIKY_COUNT
FROM 
    CH_T_SAIKEN CH0011
    INNER JOIN 
    (
    SELECT 
        CH0011.SEIKY_KEI_NO,COUNT(CH0011.SEIKY_KEI_NO) AS SEIKY_COUNT
    FROM 
        CH_T_SAIKEN CH0011 
    GROUP BY
        CH0011.SEIKY_KEI_NO
    ) CH0011_2
    ON CH0011.SEIKY_KEI_NO = CH0011_2.SEIKY_KEI_NO
WHERE
    CH0011_2.SEIKY_COUNT >= 2
AND
    CH0011.MK_FLG = '0'
AND
    CH0011.SAIKEN_YM <= ?
AND
    (
        (
            CH0011.SAIKEN_STAT = '050'
        AND
            CH0011.JUTO_FIN_YMD <= ?
        )
    OR
        (
            CH0011.SAIKEN_STAT IN ('080','090')
        AND
            CH0011.SAIKEN_SHOKYAKU_YMD <= ?
        )
    )
ORDER BY
    CH0011.SEIKY_KEI_NO
   ,CH0011.SAIKEN_YM

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_030)
#--【債権回収会社委託対象者抽出の約定弁済期限年月日の取得】
#--支払期限年月日、変更支払期限年月日の取得
#--債権ステータス'100'(マイナス請求）または債権ステータス'050'かつ充当完了日がnullを対象外にする。
#--→債権ステータス'100'(マイナス請求）以外かつ債権ステータス'050'以外または充当完了日がnull以外を対象にする。
#-----------------------------------------------------------

--KEY:CH_SELECT_030

SELECT 
     CH0011.PAY_KIGEN_YMD
   , CH0011.CHG_PAY_KIGEN_YMD
FROM
     CH_T_SAIKEN   CH0011
WHERE
     CH0011.SEIKY_KEI_NO = ?
 AND CH0011.SAIKEN_YM =
            (SELECT
                 MAX(CH0011_GENE.SAIKEN_YM) AS CH0011_MAX
             FROM
                 CH_T_SAIKEN CH0011_GENE
             WHERE 
                    CH0011_GENE.SEIKY_KEI_NO = CH0011.SEIKY_KEI_NO
                AND CH0011_GENE.MK_FLG = '0'
                AND CH0011_GENE.SAIKEN_STAT <> ?
                AND (CH0011_GENE.SAIKEN_STAT <> ?
                     OR CH0011_GENE.JUTO_FIN_YMD IS NOT NULL
                    )
             )

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_031)
#--【債権充当】
#--債権ステータスを取得する
#-----------------------------------------------------------

--KEY:CH_SELECT_031

SELECT
    CH0011.SAIKEN_NO
    ,CH0011.SAIKEN_STAT
    ,CH0011.SAIKEN_YM
FROM
    CH_T_SAIKEN CH0011
WHERE
        CH0011.SEIKY_KEI_NO   =  ?
    AND CH0011.SAIKEN_STAT    IN (?, ?, ?, ?, ?)
    AND CH0011.MK_FLG         = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_032)
#--【債権仮充当_速報】
#--充当額合計を算出する
#-----------------------------------------------------------

--KEY:CH_SELECT_032

SELECT
     SUM(CH0021.AMNT) AS AMNT
FROM
    CH_T_SAIKEN CH0011
INNER JOIN
    CH_T_SAIKEN_UCWK CH0021
ON  CH0011.SAIKEN_NO = CH0021.SAIKEN_NO
AND CH0011.SAIKEN_YM = CH0021.SAIKEN_FIX_YM
WHERE
        CH0011.SEIKY_KEI_NO   =  ?
    AND CH0011.SAIKEN_YM      <  ?
    AND CH0011.SAIKEN_STAT    =  ?
    AND CH0011.MK_FLG         = '0'
    AND CH0021.PRC_GRP_CD     =  ?
    AND CH0021.PCRS_CD        =  ?
    AND CH0021.PRC_SVC_CD     =  ?
    AND CH0021.PRC_KMK_CD     =  ?
    AND CH0021.MK_FLG         = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_033)
#--【督促ステータス一括更新】
#--ステータスを90:貸倒登録済に変更する際の対象債権情報を取得する
#-----------------------------------------------------------

--KEY:CH_SELECT_033

SELECT 
    CH0011.SAIKEN_NO 
    ,CH0011.SAIKEN_YM
    ,CH0011.SAIKEN_STAT 
    ,CH0011.UPD_DTM 
    ,CH0041.SEIKY_NO 
FROM 
    CH_T_SAIKEN CH0011 
    INNER JOIN 
    ( 
        SELECT 
            CH0041.SEIKY_NO 
            ,CH0041.SAIKEN_NO 
            ,CH0051.KHRI_RSLT_CD 
            ,CH0051.SEIKY_YM 
        FROM 
            CH_T_SEIKY_SKN_KNRN CH0041 
                INNER JOIN 
                    CH_T_SEIKY CH0051 
                        ON CH0051.SEIKY_NO = CH0041.SEIKY_NO 
        WHERE 1=1 
            AND CH0051.SEIKY_KEI_NO = ?
            AND CH0051.MK_FLG = '0' 
            AND CH0041.KKSHI_CNT = 0 
            AND CH0041.MK_FLG = '0' 
    ) CH0041 
        ON CH0041.SAIKEN_NO = CH0011.SAIKEN_NO 
WHERE 
    CH0011.SEIKY_KEI_NO = ? 
    AND CH0011.SAIKEN_STAT IN (?, ?, ?) 
    AND CH0011.MK_FLG = '0' 
ORDER BY 
    SAIKEN_YM DESC

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_034)
#--ｍｉｎｅｏ利用停止予告メール送信（口振）本文に置換する債権月を取得する。
#-----------------------------------------------------------

--KEY:CH_SELECT_034

SELECT
    CH0011.SAIKEN_YM
FROM
    CH_T_SAIKEN CH0011
WHERE
        CH0011.SEIKY_KEI_NO   =  ?
    AND CH0011.SAIKEN_STAT    IN ('020', '060')
    AND CH0011.MK_FLG         = '0'
ORDER BY
    CH0011.SAIKEN_YM

#-


#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_020)
#--【未収債権情報抽出＿リアル】
#--入金の消込先データ抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_035

SELECT /*+ USE_NL(CH0111 CH0051 CH0041 CH0011 CH0021) INDEX(CH0111 CH_T_NYUKIN_IDX_08, CH0051 CH_T_SEIKY_PK_01, CH0041 CH_T_SEIKY_SKN_KNRN_IDX_02, CH0011 CH_T_SAIKEN_PK_01, CH0021 CH_T_SAIKEN_UCWK_IDX_02) */
    CH0011.SEIKY_KEI_NO
    ,CH0011.SAIKEN_NO
    ,CH0011.SAIKEN_STAT
    ,CH0011.SAIKEN_YM
    ,CH0021.PRC_GRP_CD
    ,CH0021.PCRS_CD
    ,CH0021.PRC_SVC_CD
    ,CH0021.PRC_KMK_CD
    ,CH0021.AMNT
    ,CH0051.SEIKY_YM
    ,CH0051.SEIKY_NO
    ,(SELECT /*+ INDEX(CH0231 CH_T_TOKUSOKU_IDX_01) */
          CH0231.TOKUSOKU_NO
      FROM
          CH_T_TOKUSOKU CH0231
      WHERE
          CH0231.SEIKY_KEI_NO = CH0011.SEIKY_KEI_NO
          AND CH0231.TOKUSOKU_STAYMD <= ?
          AND CH0231.TOKUSOKU_ENDYMD > ?
          AND CH0231.MK_FLG = '0'
    )TOKUSOKU_NO
    ,CH0111.NYUKIN_NO
    ,CH0111.NYUKIN_ROUTE_CD
    ,CH0111.NYUKIN_STAT
FROM
    CH_T_NYUKIN CH0111
    INNER JOIN CH_T_SEIKY CH0051
        ON  CH0111.SEIKY_NO = CH0051.SEIKY_NO
    INNER JOIN CH_T_SEIKY_SKN_KNRN CH0041
        ON  CH0111.SEIKY_NO = CH0041.SEIKY_NO
        AND CH0051.SEIKY_NO = CH0041.SEIKY_NO
    INNER JOIN CH_T_SAIKEN CH0011
        ON  CH0041.SAIKEN_NO = CH0011.SAIKEN_NO
    INNER JOIN CH_T_SAIKEN_UCWK CH0021
        ON CH0041.SAIKEN_NO = CH0021.SAIKEN_NO
        AND  CH0011.SAIKEN_NO = CH0021.SAIKEN_NO
        AND  CH0011.SAIKEN_YM = CH0021.SAIKEN_FIX_YM
    INNER JOIN
    (
        SELECT /*+ (CH0401_SUB CH_M_PRC_KMK_CS_CHGE_IDX_01, ZM0321 ZM_M_WORK_PARAM_KNRI_PK_01) */
            CH0401_SUB.PRC_GRP_CD
            ,CH0401_SUB.PCRS_CD
            ,CH0401_SUB.PRC_SVC_CD
            ,CH0401_SUB.PRC_KMK_CD
        FROM
            CH_M_PRC_KMK_CS_CHGE CH0401_SUB
            INNER JOIN ZM_M_WORK_PARAM_KNRI ZM0321
                ON  CH0401_SUB.WORK_KINO_SKBT_CD = ZM0321.WORK_PARAM_SETTE_VALUE
        WHERE
            ZM0321.WORK_PARAM_ID = ?
            AND CH0401_SUB.SYS_CD = ?
            AND CH0401_SUB.CHSHT_CHG_CD = ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TSTAYMD <= ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TENDYMD >= ?
            AND CH0401_SUB.MK_FLG = '0'
            AND ZM0321.MK_FLG = '0'
    ) CH0401
        ON  CH0021.PRC_GRP_CD = CH0401.PRC_GRP_CD
        AND CH0021.PCRS_CD = CH0401.PCRS_CD
        AND CH0021.PRC_SVC_CD = CH0401.PRC_SVC_CD
        AND CH0021.PRC_KMK_CD = CH0401.PRC_KMK_CD
WHERE
       CH0111.CVS_REAL_SOKHO_SYRZM_FLG = '0'
   AND CH0111.NYUKIN_ROUTE_CD = '85'
   AND CH0011.SAIKEN_STAT IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
   AND CH0011.MK_FLG = '0'
   AND CH0021.MK_FLG = '0'
   AND CH0051.MK_FLG = '0'
   AND CH0111.MK_FLG = '0'
   AND CH0041.MK_FLG = '0'
ORDER BY
    CH0011.SEIKY_KEI_NO, CH0111.NYUKIN_NO, CH0011.SAIKEN_YM


#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_036)
#--早期利用停止対象者候補抽出（未収債権）
#-----------------------------------------------------------

--KEY:CH_SELECT_036

SELECT NVL(SUM(CH0021.AMNT),0) AS AMNT 
FROM  
    CH_T_SAIKEN CH0011 
    INNER JOIN 
        ( 
        SELECT 
             CH0021_SUB.SAIKEN_FIX_YM 
            ,CH0021_SUB.SAIKEN_NO 
            ,CH0021_SUB.AMNT 
        FROM 
            CH_T_SAIKEN_UCWK CH0021_SUB 
            INNER JOIN 
                ( 
                  SELECT 
                       CH0401_GENE.SYS_CD 
                       ,CH0401_GENE.WORK_KINO_SKBT_CD 
                       ,CH0401_GENE.CHSHT_CHG_CD 
                       ,CH0401_GENE.PRC_GRP_CD 
                       ,CH0401_GENE.PCRS_CD 
                       ,CH0401_GENE.PRC_SVC_CD 
                       ,CH0401_GENE.PRC_KMK_CD 
                  FROM 
                       CH_M_PRC_KMK_CS_CHGE CH0401_GENE 
                  WHERE 
                       CH0401_GENE.SYS_CD                       = 'CH' 
                       AND CH0401_GENE.WORK_KINO_SKBT_CD        = 'MISH_AMNT' 
                       AND CH0401_GENE.CHSHT_CHG_CD             = '1' 
                       AND CH0401_GENE.RSV_APLY_YMD            <= ?  
                       AND CH0401_GENE.PRC_KMK_CS_CHGE_TSTAYMD <= ?  
                       AND CH0401_GENE.PRC_KMK_CS_CHGE_TENDYMD >= ?  
                       AND CH0401_GENE.MK_FLG                   = '0'  
                  GROUP BY 
                       CH0401_GENE.SYS_CD 
                       ,CH0401_GENE.WORK_KINO_SKBT_CD 
                       ,CH0401_GENE.CHSHT_CHG_CD 
                       ,CH0401_GENE.PRC_GRP_CD 
                       ,CH0401_GENE.PCRS_CD 
                       ,CH0401_GENE.PRC_SVC_CD 
                       ,CH0401_GENE.PRC_KMK_CD 
                ) CH0401 
            ON  
                CH0021_SUB.PRC_GRP_CD     = CH0401.PRC_GRP_CD 
                AND CH0021_SUB.PCRS_CD    = CH0401.PCRS_CD 
                AND CH0021_SUB.PRC_SVC_CD = CH0401.PRC_SVC_CD 
                AND CH0021_SUB.PRC_KMK_CD = CH0401.PRC_KMK_CD 
        WHERE 
            CH0021_SUB.SAIKEN_FIX_YM < ?  
            AND CH0021_SUB.MK_FLG    = '0' 
        ) CH0021 
        ON  
            CH0011.SAIKEN_NO = CH0021.SAIKEN_NO 
        AND CH0011.SAIKEN_YM = CH0021.SAIKEN_FIX_YM 
 WHERE 
    CH0011.SEIKY_KEI_NO      = ?  
    AND CH0011.SAIKEN_STAT  IN ('020', '060') 
    AND CH0011.MK_FLG        = '0' 

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_037)
#--前月以前の未収金額をもつ債権番号を取得する。
#-----------------------------------------------------------

--KEY:CH_SELECT_037

SELECT /* CH_T_SAIKEN-CH_SELECT_037 */
    CH0011.SAIKEN_NO
FROM
    CH_T_SAIKEN CH0011
WHERE
        CH0011.SEIKY_KEI_NO   =  ?
    AND CH0011.SAIKEN_STAT    IN ('020', '030', '040', '060')
    AND CH0011.SAIKEN_YM      <  ?
    AND CH0011.MK_FLG         = '0'
ORDER BY
    CH0011.SAIKEN_YM

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_038)
#--ｍｉｎｅｏ利用停止予告メール送信（クレ）本文に置換する前月以前の債権月を取得する。
#-----------------------------------------------------------

--KEY:CH_SELECT_038

SELECT
    CH0011.SAIKEN_YM
FROM
    CH_T_SAIKEN CH0011
WHERE
        CH0011.SEIKY_KEI_NO   =  ?
    AND CH0011.SAIKEN_STAT    IN ('020', '060')
    AND CH0011.SAIKEN_YM      <  ?
    AND CH0011.MK_FLG         = '0'
ORDER BY
    CH0011.SAIKEN_YM

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_039)
#--載替対象請求抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_039

SELECT  CH0011.SAIKEN_NO
	   ,CH0011.SAIKEN_YM
       ,CH0011.SAIKEN_STAT
	   ,CH0021.PRC_KMK_CD
FROM  CH_T_SAIKEN CH0011
LEFT JOIN (
    SELECT  CH0021.SAIKEN_NO
	       ,CH0401.PRC_KMK_CD 
      FROM  CH_T_SAIKEN_UCWK CH0021
     INNER JOIN CH_M_PRC_KMK_CS_CHGE CH0401
             ON CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD
     WHERE  CH0021.MK_FLG = '0'
       AND  CH0401.SYS_CD = 'CH'
       AND  CH0401.CHSHT_CHG_CD = '1'
       AND  CH0401.WORK_KINO_SKBT_CD = ?
       AND  CH0401.PRC_KMK_CS_CHGE_TSTAYMD <= ?
       AND  CH0401.PRC_KMK_CS_CHGE_TENDYMD >= ?
       AND  CH0401.MK_FLG = '0'
		) CH0021
        ON CH0021.SAIKEN_NO = CH0011.SAIKEN_NO
WHERE  CH0011.SEIKY_KEI_NO = ?
   AND CH0011.SAIKEN_STAT IN ('020','060')
   AND CH0011.MK_FLG = '0'
ORDER BY CH0011.SAIKEN_YM DESC

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_040)
#--【未収債権情報抽出＿リアル】
#--入金の消込先データ抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_040

SELECT /*+ USE_NL(CH0111 CH0051 CH0041 CH0011 CH0021) INDEX(CH0111 CH_T_NYUKIN_IDX_08, CH0051 CH_T_SEIKY_PK_01, CH0041 CH_T_SEIKY_SKN_KNRN_IDX_02, CH0011 CH_T_SAIKEN_PK_01, CH0021 CH_T_SAIKEN_UCWK_IDX_02) */
    CH0011.SEIKY_KEI_NO
    ,CH0011.SAIKEN_NO
    ,CH0011.SAIKEN_STAT
    ,CH0011.SAIKEN_YM
    ,CH0021.PRC_GRP_CD
    ,CH0021.PCRS_CD
    ,CH0021.PRC_SVC_CD
    ,CH0021.PRC_KMK_CD
    ,CH0021.AMNT
    ,CH0051.SEIKY_YM
    ,CH0051.SEIKY_NO
    ,(SELECT /*+ INDEX(CH0231 CH_T_TOKUSOKU_IDX_01) */
          CH0231.TOKUSOKU_NO
      FROM
          CH_T_TOKUSOKU CH0231
      WHERE
          CH0231.SEIKY_KEI_NO = CH0011.SEIKY_KEI_NO
          AND CH0231.TOKUSOKU_STAYMD <= ?
          AND CH0231.TOKUSOKU_ENDYMD > ?
          AND CH0231.MK_FLG = '0'
    )TOKUSOKU_NO
    ,CH0111.NYUKIN_NO
    ,CH0111.NYUKIN_ROUTE_CD
    ,CH0111.NYUKIN_STAT
FROM
    CH_T_NYUKIN CH0111
    INNER JOIN CH_T_SEIKY CH0051
        ON  CH0111.SEIKY_NO = CH0051.SEIKY_NO
    INNER JOIN CH_T_SEIKY_SKN_KNRN CH0041
        ON  CH0111.SEIKY_NO = CH0041.SEIKY_NO
        AND CH0051.SEIKY_NO = CH0041.SEIKY_NO
    INNER JOIN CH_T_SAIKEN CH0011
        ON  CH0041.SAIKEN_NO = CH0011.SAIKEN_NO
    INNER JOIN CH_T_SAIKEN_UCWK CH0021
        ON CH0041.SAIKEN_NO = CH0021.SAIKEN_NO
        AND  CH0011.SAIKEN_NO = CH0021.SAIKEN_NO
        AND  CH0011.SAIKEN_YM = CH0021.SAIKEN_FIX_YM
    INNER JOIN
    (
        SELECT /*+ (CH0401_SUB CH_M_PRC_KMK_CS_CHGE_IDX_01, ZM0321 ZM_M_WORK_PARAM_KNRI_PK_01) */
            CH0401_SUB.PRC_GRP_CD
            ,CH0401_SUB.PCRS_CD
            ,CH0401_SUB.PRC_SVC_CD
            ,CH0401_SUB.PRC_KMK_CD
        FROM
            CH_M_PRC_KMK_CS_CHGE CH0401_SUB
            INNER JOIN ZM_M_WORK_PARAM_KNRI ZM0321
                ON  CH0401_SUB.WORK_KINO_SKBT_CD = ZM0321.WORK_PARAM_SETTE_VALUE
        WHERE
            ZM0321.WORK_PARAM_ID = ?
            AND CH0401_SUB.SYS_CD = ?
            AND CH0401_SUB.CHSHT_CHG_CD = ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TSTAYMD <= ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TENDYMD >= ?
            AND CH0401_SUB.MK_FLG = '0'
            AND ZM0321.MK_FLG = '0'
    ) CH0401
        ON  CH0021.PRC_GRP_CD = CH0401.PRC_GRP_CD
        AND CH0021.PCRS_CD = CH0401.PCRS_CD
        AND CH0021.PRC_SVC_CD = CH0401.PRC_SVC_CD
        AND CH0021.PRC_KMK_CD = CH0401.PRC_KMK_CD
WHERE
       CH0111.CVS_REAL_SOKHO_SYRZM_FLG = '0'
   AND CH0111.NYUKIN_ROUTE_CD = '82'
   AND CH0011.SAIKEN_STAT IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
   AND CH0011.MK_FLG = '0'
   AND CH0021.MK_FLG = '0'
   AND CH0051.MK_FLG = '0'
   AND CH0111.MK_FLG = '0'
   AND CH0041.MK_FLG = '0'
ORDER BY
    CH0011.SEIKY_KEI_NO, CH0111.NYUKIN_NO, CH0011.SAIKEN_YM
#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_UPDATE_001)
#--【ステータス更新】
#--抽出した情報をもとにステータスを反映する。
#-----------------------------------------------------------

--KEY:CH_UPDATE_001

UPDATE
    CH_T_SAIKEN CH0011
SET
     CH0011.SAIKEN_STAT    = ?
    ,CH0011.SAIKEN_FIX_YMD = ?
    ,CH0011.UPD_DTM        = 'UPD_DTM'
    ,CH0011.UPD_UNYO_YMD   = 'UPD_UNYO'
    ,CH0011.UPD_TRN_ID     = 'UPD_TRN'
    ,CH0011.UPD_OPEACNT    = 'UPD_OPE'
WHERE
        CH0011.SAIKEN_YM    = ? 
    AND CH0011.SAIKEN_STAT  = ? 
    AND CH0011.SEIKY_WAY_CD = ?
    AND CH0011.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_UPDATE_002)
#--【支払期限日更新】
#--支払期限日更新
#-----------------------------------------------------------

--KEY:CH_UPDATE_002

UPDATE
    CH_T_SAIKEN CH0011
SET
     CH0011.PAY_KIGEN_YMD  = ?
    ,CH0011.UPD_DTM        = 'UPD_DTM' 
    ,CH0011.UPD_UNYO_YMD   = 'UPD_UNYO'
    ,CH0011.UPD_TRN_ID     = 'UPD_TRN'
    ,CH0011.UPD_OPEACNT    = 'UPD_OPE'
WHERE
        CH0011.SAIKEN_NO IN (SELECT DISTINCT CH0041.SAIKEN_NO FROM CH_T_SEIKY_SKN_KNRN CH0041 WHERE CH0041.SEIKY_NO = ?) 
    AND CH0011.SAIKEN_YM    = ?
    AND CH0011.SEIKY_SBT_CD = ?
    AND CH0011.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_UPDATE_003)
#--【請求契約変更請求反映】
#-----------------------------------------------------------

--KEY:CH_UPDATE_003

UPDATE
    CH_T_SAIKEN CH0011
SET
     CH0011.SEIKY_WAY_CD   = ?
    ,CH0011.UPD_DTM        = 'UPD_DTM'
    ,CH0011.UPD_UNYO_YMD   = 'UPD_UNYO'
    ,CH0011.UPD_TRN_ID     = 'UPD_TRN'
    ,CH0011.UPD_OPEACNT    = 'UPD_OPE'
WHERE
        CH0011.SEIKY_KEI_NO = ? 
    AND CH0011.SAIKEN_YM    = ? 
    AND CH0011.SEIKY_SBT_CD = ? 
    AND CH0011.MK_FLG = '0'

#-


#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_UPDATE_004)
#--【変更支払期限日更新】
#--変更支払期限日更新
#-----------------------------------------------------------

--KEY:CH_UPDATE_004

UPDATE 
     CH_T_SAIKEN CH0011
SET
     CH0011.CHG_PAY_KIGEN_YMD  = ?
    ,CH0011.UPD_DTM        = 'UPD_DTM' 
    ,CH0011.UPD_UNYO_YMD   = 'UPD_UNYO'
    ,CH0011.UPD_TRN_ID     = 'UPD_TRN'
    ,CH0011.UPD_OPEACNT    = 'UPD_OPE'
WHERE
        CH0011.SAIKEN_NO IN (SELECT DISTINCT CH0041.SAIKEN_NO FROM CH_T_SEIKY_SKN_KNRN CH0041 WHERE CH0041.SEIKY_NO = ?) 
    AND CH0011.SAIKEN_YM    < ?
    AND CH0011.SEIKY_SBT_CD = ?
    AND CH0011.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_UPDATE_005)
#--【強制窓口情報更新】
#--請求方法コードを更新する
#-----------------------------------------------------------

--KEY:CH_UPDATE_005

UPDATE
    CH_T_SAIKEN CH0011
SET
     CH0011.SEIKY_WAY_CD   = ?
    ,CH0011.UPD_DTM        = 'UPD_DTM'
    ,CH0011.UPD_UNYO_YMD   = 'UPD_UNYO'
    ,CH0011.UPD_TRN_ID     = 'UPD_TRN'
    ,CH0011.UPD_OPEACNT    = 'UPD_OPE'
WHERE
        CH0011.SEIKY_KEI_NO = ? 
    AND CH0011.SAIKEN_YM    = ? 
    AND CH0011.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-AC_SELECT_001)
#--【ＥＭ契約解除料金回収不能判定】
#--債権ステータス、督促ステータスを取得する
#-----------------------------------------------------------

--KEY:AC_SELECT_001

SELECT 
    CH0011.SAIKEN_STAT, 
    NVL(CH0231.TOKUSOKU_STAT,'00') AS TOKUSOKU_STAT
FROM 
    CH_T_SAIKEN CH0011 
LEFT OUTER JOIN 
    (SELECT
        CH0231_SUB.TOKUSOKU_STAT,
        CH0231_SUB.SEIKY_KEI_NO
     FROM
        CH_T_TOKUSOKU CH0231_SUB
     WHERE
            CH0231_SUB.MK_FLG = '0'
        AND CH0231_SUB.TOKUSOKU_STAYMD <= ?
        AND CH0231_SUB.TOKUSOKU_ENDYMD > ?
        AND CH0231_SUB.TOKUSOKU_IDO_YMD BETWEEN ? AND ?) CH0231
ON  CH0011.SEIKY_KEI_NO = CH0231.SEIKY_KEI_NO 
WHERE 
    CH0011.SEIKY_KEI_NO = ?
    AND CH0011.SAIKEN_YM = ?
    AND CH0011.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-KK_SELECT_001)
#--【保存期間経過データ物理削除】
#--請求契約番号に関連する債権情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
     CH0011.SAIKEN_NO
FROM
    CH_T_SAIKEN CH0011
WHERE
        CH0011.SEIKY_KEI_NO = ?
    AND CH0011.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_035)
#--【パーフェクト口座未収債権情報抽出】
#--入金の消込先データ抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_041

SELECT /*+ USE_NL(CH0111 CH0051 CH0041 CH0011 CH0021) INDEX(CH0111 CH_T_NYUKIN_IDX_08, CH0051 CH_T_SEIKY_PK_01, CH0041 CH_T_SEIKY_SKN_KNRN_IDX_02, CH0011 CH_T_SAIKEN_PK_01, CH0021 CH_T_SAIKEN_UCWK_IDX_02) */
    CH0011.SEIKY_KEI_NO
    ,CH0011.SAIKEN_NO
    ,CH0011.SAIKEN_STAT
    ,CH0011.SAIKEN_YM
    ,CH0021.PRC_GRP_CD
    ,CH0021.PCRS_CD
    ,CH0021.PRC_SVC_CD
    ,CH0021.PRC_KMK_CD
    ,CH0021.AMNT
    ,(SELECT /*+ INDEX(CH0231 CH_T_TOKUSOKU_IDX_01) */
          CH0231.TOKUSOKU_NO
      FROM
          CH_T_TOKUSOKU CH0231
      WHERE
          CH0231.SEIKY_KEI_NO = CH0011.SEIKY_KEI_NO
          AND CH0231.TOKUSOKU_STAYMD <= ?
          AND CH0231.TOKUSOKU_ENDYMD > ?
          AND CH0231.MK_FLG = '0'
    )TOKUSOKU_NO
    ,CH0111.NYUKIN_NO
    ,CH0111.NYUKIN_ROUTE_CD
    ,CH0111.NYUKIN_STAT
FROM
    CH_T_NYUKIN CH0111
    INNER JOIN CH_T_SAIKEN CH0011
        ON  CH0111.SEIKY_KEI_NO = CH0011.SEIKY_KEI_NO
    INNER JOIN CH_T_SAIKEN_UCWK CH0021
        ON   CH0011.SAIKEN_NO = CH0021.SAIKEN_NO
        AND  CH0011.SAIKEN_YM = CH0021.SAIKEN_FIX_YM
    INNER JOIN
    (
        SELECT /*+ (CH0401_SUB CH_M_PRC_KMK_CS_CHGE_IDX_01, ZM0321 ZM_M_WORK_PARAM_KNRI_PK_01) */
            CH0401_SUB.PRC_GRP_CD
            ,CH0401_SUB.PCRS_CD
            ,CH0401_SUB.PRC_SVC_CD
            ,CH0401_SUB.PRC_KMK_CD
        FROM
            CH_M_PRC_KMK_CS_CHGE CH0401_SUB
            INNER JOIN ZM_M_WORK_PARAM_KNRI ZM0321
                ON  CH0401_SUB.WORK_KINO_SKBT_CD = ZM0321.WORK_PARAM_SETTE_VALUE
        WHERE
            ZM0321.WORK_PARAM_ID = ?
            AND CH0401_SUB.SYS_CD = ?
            AND CH0401_SUB.CHSHT_CHG_CD = ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TSTAYMD <= ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TENDYMD >= ?
            AND CH0401_SUB.MK_FLG = '0'
            AND ZM0321.MK_FLG = '0'
    ) CH0401
        ON  CH0021.PRC_GRP_CD = CH0401.PRC_GRP_CD
        AND CH0021.PCRS_CD = CH0401.PCRS_CD
        AND CH0021.PRC_SVC_CD = CH0401.PRC_SVC_CD
        AND CH0021.PRC_KMK_CD = CH0401.PRC_KMK_CD
WHERE
       CH0111.NYUKIN_SYRZM_FLG = '0'
   AND CH0111.NYUKIN_ROUTE_CD = '45'
   AND CH0011.SAIKEN_STAT IN ( ?, ?, ?)
   AND CH0011.MK_FLG = '0'
   AND CH0021.MK_FLG = '0'
   AND CH0111.MK_FLG = '0'
ORDER BY
    CH0011.SEIKY_KEI_NO, CH0111.NYUKIN_NO, CH0011.SAIKEN_YM

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_042)
#--【債権回収委託会社未収債権情報抽出】
#--入金の消込先データ抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_042

SELECT /*+ USE_NL(CH0111 CH0051 CH0041 CH0011 CH0021) INDEX(CH0111 CH_T_NYUKIN_IDX_08, CH0051 CH_T_SEIKY_PK_01, CH0041 CH_T_SEIKY_SKN_KNRN_IDX_02, CH0011 CH_T_SAIKEN_PK_01, CH0021 CH_T_SAIKEN_UCWK_IDX_02) */
    CH0011.SEIKY_KEI_NO
    ,CH0011.SAIKEN_NO
    ,CH0011.SAIKEN_STAT
    ,CH0011.SAIKEN_YM
    ,CH0021.PRC_GRP_CD
    ,CH0021.PCRS_CD
    ,CH0021.PRC_SVC_CD
    ,CH0021.PRC_KMK_CD
    ,CH0021.AMNT
    ,(SELECT /*+ INDEX(CH0231 CH_T_TOKUSOKU_IDX_01) */
          CH0231.TOKUSOKU_NO
      FROM
          CH_T_TOKUSOKU CH0231
      WHERE
          CH0231.SEIKY_KEI_NO = CH0011.SEIKY_KEI_NO
          AND CH0231.TOKUSOKU_STAYMD <= ?
          AND CH0231.TOKUSOKU_ENDYMD > ?
          AND CH0231.MK_FLG = '0'
    )TOKUSOKU_NO
    ,CH0111.NYUKIN_NO
    ,CH0111.NYUKIN_ROUTE_CD
    ,CH0111.NYUKIN_STAT
FROM
    CH_T_NYUKIN CH0111
    INNER JOIN CH_T_SAIKEN CH0011
        ON  CH0111.SEIKY_KEI_NO = CH0011.SEIKY_KEI_NO
    INNER JOIN CH_T_SAIKEN_UCWK CH0021
        ON   CH0011.SAIKEN_NO = CH0021.SAIKEN_NO
        AND  CH0011.SAIKEN_YM = CH0021.SAIKEN_FIX_YM
    INNER JOIN
    (
        SELECT /*+ (CH0401_SUB CH_M_PRC_KMK_CS_CHGE_IDX_01, ZM0321 ZM_M_WORK_PARAM_KNRI_PK_01) */
            CH0401_SUB.PRC_GRP_CD
            ,CH0401_SUB.PCRS_CD
            ,CH0401_SUB.PRC_SVC_CD
            ,CH0401_SUB.PRC_KMK_CD
        FROM
            CH_M_PRC_KMK_CS_CHGE CH0401_SUB
            INNER JOIN ZM_M_WORK_PARAM_KNRI ZM0321
                ON  CH0401_SUB.WORK_KINO_SKBT_CD = ZM0321.WORK_PARAM_SETTE_VALUE
        WHERE
            ZM0321.WORK_PARAM_ID = ?
            AND CH0401_SUB.SYS_CD = ?
            AND CH0401_SUB.CHSHT_CHG_CD = ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TSTAYMD <= ?
            AND CH0401_SUB.PRC_KMK_CS_CHGE_TENDYMD >= ?
            AND CH0401_SUB.MK_FLG = '0'
            AND ZM0321.MK_FLG = '0'
    ) CH0401
        ON  CH0021.PRC_GRP_CD = CH0401.PRC_GRP_CD
        AND CH0021.PCRS_CD = CH0401.PCRS_CD
        AND CH0021.PRC_SVC_CD = CH0401.PRC_SVC_CD
        AND CH0021.PRC_KMK_CD = CH0401.PRC_KMK_CD
WHERE
       CH0111.NYUKIN_SYRZM_FLG = '0'
   AND CH0111.NYUKIN_ROUTE_CD = '99'
   AND CH0011.SAIKEN_STAT IN ( ?, ?, ?)
   AND CH0011.MK_FLG = '0'
   AND CH0021.MK_FLG = '0'
   AND CH0111.MK_FLG = '0'
ORDER BY
    CH0011.SEIKY_KEI_NO, CH0111.NYUKIN_NO, CH0011.SAIKEN_YM

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_043)
#--【最終未収月情報抽出】
#--請求契約番号に紐づく最終未収月情報データ抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_043

SELECT
      CH0011.SEIKY_KEI_NO
    , MAX(CH0011.SAIKEN_YM) AS SAIKEN_YM
FROM
    CH_T_SAIKEN CH0011
    INNER JOIN
        CH_T_SAIKEN_UCWK CH0021
    ON
        CH0021.SAIKEN_NO = CH0011.SAIKEN_NO
        AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM
    INNER JOIN
        (
         SELECT
              CH0401_TGT.PRC_GRP_CD
            , CH0401_TGT.PCRS_CD
            , CH0401_TGT.PRC_SVC_CD
            , CH0401_TGT.PRC_KMK_CD
         FROM
            CH_M_PRC_KMK_CS_CHGE CH0401_TGT
         WHERE
            CH0401_TGT.SYS_CD = 'CH'
            AND CH0401_TGT.WORK_KINO_SKBT_CD        =  'MISH_AMNT'
            AND CH0401_TGT.CHSHT_CHG_CD             =  '1'
            AND CH0401_TGT.RSV_APLY_YMD             <= ?
            AND CH0401_TGT.PRC_KMK_CS_CHGE_TSTAYMD  <= ?
            AND CH0401_TGT.PRC_KMK_CS_CHGE_TENDYMD  >= ?
            AND CH0401_TGT.MK_FLG                   =  '0'
         GROUP BY
              CH0401_TGT.PRC_GRP_CD
            , CH0401_TGT.PCRS_CD
            , CH0401_TGT.PRC_SVC_CD
            , CH0401_TGT.PRC_KMK_CD
        ) CH0401
    ON
        CH0021.PRC_GRP_CD     = CH0401.PRC_GRP_CD
        AND CH0021.PCRS_CD    = CH0401.PCRS_CD
        AND CH0021.PRC_SVC_CD = CH0401.PRC_SVC_CD
        AND CH0021.PRC_KMK_CD = CH0401.PRC_KMK_CD
    INNER JOIN
        CH_T_SEIKY_SKN_KNRN CH0041
    ON
        CH0041.SAIKEN_NO = CH0011.SAIKEN_NO
    INNER JOIN
        CH_T_SEIKY CH0051
    ON
        CH0051.SEIKY_NO = CH0041.SEIKY_NO
        AND CH0051.SEIKY_KEI_NO = CH0011.SEIKY_KEI_NO
        AND CH0051.SEIKY_YM     = CH0011.SAIKEN_YM
WHERE 
    CH0011.SEIKY_KEI_NO    =  ?
    AND CH0011.SAIKEN_STAT IN ('020', '060')
    AND CH0011.MK_FLG      =  '0'
    AND CH0021.MK_FLG       =  '0'
    AND CH0041.MK_FLG       =  '0'
    AND CH0051.SEIKY_SBT_CD =  '1'
    AND CH0051.MK_FLG       =  '0'
GROUP BY
    CH0011.SEIKY_KEI_NO

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_044)
#--CHPRC12501_一括入金登録
#--請求契約番号に紐づく、債権一覧情報を取得する
#-----------------------------------------------------------

--KEY:CH_SELECT_044

SELECT
    CH0011.SAIKEN_NO,
    CH0011.SAIKEN_YM,
    CH0011.SAIKEN_STAT,
    CH0011.SEIKY_WAY_CD,
    CH0011.SEIKY_SBT_CD,
    (SELECT
         CH0021.AMNT
     FROM
         CH_T_SAIKEN_UCWK CH0021
     INNER JOIN (
         SELECT
             CH0401.PRC_GRP_CD,
             CH0401.PCRS_CD,
             CH0401.PRC_SVC_CD,
             CH0401.PRC_KMK_CD
         FROM
             CH_M_PRC_KMK_CS_CHGE CH0401
         WHERE
             CH0401.SYS_CD = 'CH'
         AND CH0401.WORK_KINO_SKBT_CD = ?
         AND CH0401.CHSHT_CHG_CD = '1'
         AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD
         AND CH0401.MK_FLG = '0'
       ) CH0401
          ON CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD
         AND CH0401.PCRS_CD = CH0021.PCRS_CD
         AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD
         AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD
     WHERE
         CH0021.SAIKEN_NO = CH0011.SAIKEN_NO
     AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM
     AND CH0021.MK_FLG = '0'
     AND ROWNUM = 1
    ) AS MINOU_AMNT,
    CH0011.UPD_DTM,
    CH0041.SEIKY_NO
FROM
    CH_T_SAIKEN CH0011
LEFT OUTER JOIN (
    SELECT
        CH0041.SEIKY_NO,
        CH0041.SAIKEN_NO,
        CH0051.KHRI_RSLT_CD,
        CH0051.SEIKY_YM
    FROM
        CH_T_SEIKY_SKN_KNRN CH0041
    INNER JOIN CH_T_SEIKY CH0051
        ON CH0051.SEIKY_NO = CH0041.SEIKY_NO
    WHERE
        CH0051.SEIKY_KEI_NO = ?
    AND CH0051.MK_FLG = '0'
    AND CH0041.KKSHI_CNT = 0
    AND CH0041.MK_FLG = '0'
    AND CH0051.SEIKY_SBT_CD <> '3'
  ) CH0041
     ON CH0041.SAIKEN_NO = CH0011.SAIKEN_NO
    AND CH0041.SEIKY_YM = CH0011.SAIKEN_YM
WHERE
    CH0011.SEIKY_KEI_NO = ?
AND CH0011.SAIKEN_STAT IN ('020', '060')
AND CH0011.MK_FLG = '0'
ORDER BY
    CH0011.SAIKEN_YM ASC,
    CH0011.SEIKY_SBT_CD DESC,
    CH0011.SAIKEN_NO DESC

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_045)
#--CHPRC12601_一括入金取消登録
#--CHPRC12501_一括入金登録
#--指定した債権年月の債権情報を取得する
#-----------------------------------------------------------

--KEY:CH_SELECT_045

SELECT
    CH0011.SAIKEN_NO
FROM
    CH_T_SAIKEN CH0011
WHERE
    CH0011.SEIKY_KEI_NO = ?
AND CH0011.SAIKEN_YM = ?
AND CH0011.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--債権(CH_T_SAIKEN-CH_SELECT_046)
#--CHPRC12601_一括入金取消登録
#--請求契約番号に紐づく、債権一覧情報を取得する
#-----------------------------------------------------------

--KEY:CH_SELECT_046

SELECT
 CH0011.SAIKEN_NO
 ,CH0011.SAIKEN_YM
 ,CH0011.SAIKEN_STAT
 ,CH0011.SEIKY_SBT_CD
  ,(
   SELECT
    CH0021.AMNT
   FROM
    CH_T_SAIKEN_UCWK CH0021
    INNER JOIN
    (
     SELECT
      CH0401.PRC_GRP_CD,
      CH0401.PCRS_CD,
      CH0401.PRC_SVC_CD,
      CH0401.PRC_KMK_CD
     FROM
      CH_M_PRC_KMK_CS_CHGE CH0401
     WHERE
      CH0401.SYS_CD = 'CH'
      AND CH0401.WORK_KINO_SKBT_CD = ?
      AND CH0401.CHSHT_CHG_CD = '1'
      AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD
      AND CH0401.MK_FLG = '0'
    ) CH0401
    ON  CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD
    AND CH0401.PCRS_CD = CH0021.PCRS_CD
    AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD
    AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD
   WHERE
    CH0021.SAIKEN_NO = CH0011.SAIKEN_NO
    AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM
    AND CH0021.MK_FLG = '0'
    AND ROWNUM = 1
  ) SAIKEN_AMNT
 ,(
  SELECT
   CH0021.AMNT
  FROM
   CH_T_SAIKEN_UCWK CH0021
   INNER JOIN
   (
    SELECT
     CH0401.PRC_GRP_CD,
     CH0401.PCRS_CD,
     CH0401.PRC_SVC_CD,
     CH0401.PRC_KMK_CD
    FROM
     CH_M_PRC_KMK_CS_CHGE CH0401
    WHERE
     CH0401.SYS_CD = 'CH'
     AND CH0401.WORK_KINO_SKBT_CD = ?
     AND CH0401.CHSHT_CHG_CD = '1'
     AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD
     AND CH0401.MK_FLG = '0'
   ) CH0401
   ON  CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD
   AND CH0401.PCRS_CD = CH0021.PCRS_CD
   AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD
   AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD
  WHERE
   CH0021.SAIKEN_NO = CH0011.SAIKEN_NO
   AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM
   AND CH0021.MK_FLG = '0'
   AND ROWNUM = 1
 ) JYTO_AMNT
 ,CH0011.UPD_DTM
 ,CH0041.SEIKY_NO
FROM 
 CH_T_SAIKEN CH0011
 INNER JOIN
  (
   SELECT
    CH0041.SEIKY_NO
    ,CH0041.SAIKEN_NO
    ,CH0051.KHRI_RSLT_CD
    ,CH0051.SEIKY_YM
   FROM
    CH_T_SEIKY_SKN_KNRN CH0041
     INNER JOIN
      CH_T_SEIKY CH0051
       ON CH0051.SEIKY_NO = CH0041.SEIKY_NO
   WHERE
     CH0051.SEIKY_KEI_NO = ?
    AND CH0051.MK_FLG = '0'
    AND CH0041.KKSHI_CNT = 0
    AND CH0041.MK_FLG = '0'
    AND CH0051.SEIKY_SBT_CD <> '3'
  ) CH0041
   ON CH0041.SAIKEN_NO = CH0011.SAIKEN_NO
   AND CH0041.SEIKY_YM = CH0011.SAIKEN_YM
  
 WHERE 
   CH0011.SEIKY_KEI_NO = ?
   AND CH0011.SAIKEN_YM = ? 
   AND CH0011.SEIKY_SBT_CD = '0'
   AND CH0011.SAIKEN_STAT = '050'
   AND CH0011.MK_FLG = '0'
   AND EXISTS
   (
    SELECT 1
    FROM
       CH_T_JUTO CH0201
    WHERE
         CH0201.SAIKEN_NO = CH0011.SAIKEN_NO
       AND CH0201.CL_YMD IS NULL
       AND CH0201.MK_FLG = '0'
   )
  ORDER BY
   CH0011.SAIKEN_NO ASC

#-