SELECT
    AC0391.SVC_KEI_NO                                                        AS SVC_KEI_NO
  , AC0391.SVC_KEI_UCWK_NO                                                   AS SVC_KEI_UCWK_NO
  , AC0391.PCRS_CD                                                           AS PCRS_CD
  , AC0391.PRC_SVC_CD                                                        AS PRC_SVC_CD
  , AC0391.SEIKY_KEI_NO                                                      AS SEIKY_KEI_NO
  , AC0391.KAKINS_TELNO                                                      AS KAKINS_TELNO
  , AC0391.SEIKY_YM                                                          AS SEIKY_YM
  , DECODE(AC0391.KAZEI_HIKAZEI_CD,'1',TO_CHAR(AC0391.PCALL_PRC),'0')        AS KAZEI_PRC
  , DECODE(AC0391.KAZEI_HIKAZEI_CD,'2',TO_CHAR(AC0391.PCALL_PRC),'0')        AS HIKAZEI_PRC
  , DECODE(AC0391.KAZEI_HIKAZEI_CD,'1',TO_CHAR(AC0391.PCALL_SEC_CNT),'0')    AS KAZEI_SEC_CNT
  , DECODE(AC0391.KAZEI_HIKAZEI_CD,'2',TO_CHAR(AC0391.PCALL_SEC_CNT),'0')    AS HIKAZEI_SEC_CNT
  , DECODE(AC0391.KAZEI_HIKAZEI_CD,'1','1','0')                              AS KAZEI_CNT
  , DECODE(AC0391.KAZEI_HIKAZEI_CD,'2','1','0')                              AS HIKAZEI_CNT
  , AC0391.PCALL_ENDYMD                                                      AS PCALL_ENDYMD
  , AC0401.CHOK_TCHI_CD                                                      AS CHOK_TCHI_CD
  , AC0401.PCRS_CD                                                           AS GK_PCRS_CD
  , AC0401.PRC_SVC_CD                                                        AS GK_PRC_SVC_CD
  , AC0391.PRC_KMK_CD                                                        AS PRC_KMK_CD
FROM
    AC_T_EOH_TEL_TW_DTL AC0391
    INNER JOIN
        AC_T_TRGT_KEY_WK AC0561
        ON  AC0391.SEIKY_YM        = AC0561.TRGT_KEY_1
        AND AC0391.SEIKY_KEI_NO    = AC0561.TRGT_KEY_2
        AND AC0391.SVC_KEI_NO      = AC0561.TRGT_KEY_3
        AND AC0391.SVC_KEI_UCWK_NO = AC0561.TRGT_KEY_4
    INNER JOIN
        (SELECT
            ZM0321.WORK_PARAM_SETTE_VALUE
        FROM
            ZM_M_WORK_PARAM_KNRI ZM0321
        WHERE
            ZM0321.WORK_PARAM_ID       = :WORK_PARAM_ID
        AND ZM0321.WORK_PARAM_TSTAYMD <= :OPE_DATE
        AND ZM0321.WORK_PARAM_TENDYMD >= :OPE_DATE
        AND ZM0321.MK_FLG              = '0') ZM0321
        ON  AC0561.TRGT_KEY_WK_USE_KINO_CD = RPAD(ZM0321.WORK_PARAM_SETTE_VALUE, 9, ' ')
    LEFT OUTER JOIN
        (SELECT
            AC0401.SVC_KEI_NO
           ,AC0401.SVC_KEI_UCWK_NO
           ,AC0401.SEIKY_YM
           ,AC0401.KAKINS_TELNO
           ,AC0401.CHOK_TCHI_CD
           ,AC0401.PCRS_CD
           ,AC0401.PRC_SVC_CD
        FROM
            AC_T_EOH_TEL_TW_GK AC0401
        WHERE
            AC0401.MK_FLG = '0'
        ) AC0401
        ON  AC0391.SVC_KEI_NO       = AC0401.SVC_KEI_NO
        AND AC0391.SVC_KEI_UCWK_NO  = AC0401.SVC_KEI_UCWK_NO
        AND AC0391.SEIKY_YM         = AC0401.SEIKY_YM
        AND AC0391.KAKINS_TELNO     = AC0401.KAKINS_TELNO
WHERE
    AC0391.CHRG_KH                 = '1'
AND AC0391.MK_FLG                  = '0'
AND AC0561.MK_FLG                  = '0'