SELECT  
  CH0051.SEIKY_KEI_NO
 ,CH0051.SEIKY_NO
 ,CH0101_02.PRC_GRP_CD
 ,CH0101_02.PCRS_CD
 ,CH0101_02.PRC_SVC_CD
 ,CH0101_02.PRC_KMK_CD
FROM  CH_T_SEIKY CH0051
INNER JOIN CH_T_SEIKY_UCWK CH0101_01
ON    CH0101_01.SEIKY_NO = CH0051.SEIKY_NO
AND   CH0101_01.SEIKY_YM = CH0051.SEIKY_YM
INNER JOIN CH_T_SEIKY_SKN_KNRN CH0041_01
ON    CH0041_01.SEIKY_NO = CH0101_01.SEIKY_NO
INNER JOIN CH_T_SEIKY_SKN_KNRN CH0041_02
ON    CH0041_02.SAIKEN_NO = CH0041_01.SAIKEN_NO
INNER JOIN CH_M_PRC_KMK_CS_CHGE CH0401_01
ON    CH0401_01.PRC_GRP_CD = CH0101_01.PRC_GRP_CD
AND   CH0401_01.PCRS_CD = CH0101_01.PCRS_CD
AND   CH0401_01.PRC_SVC_CD = CH0101_01.PRC_SVC_CD
AND   CH0401_01.PRC_KMK_CD = CH0101_01.PRC_KMK_CD
INNER JOIN CH_T_SEIKY_UCWK CH0101_02
ON    CH0101_02.SEIKY_NO = CH0041_02.SEIKY_NO
AND   CH0101_02.SEIKY_YM = CH0101_01.GSAN_SEIKY_YM
WHERE CH0051.SEIKY_YM = (SELECT CH0501_SUB.SEIKY_YM 
                         FROM (SELECT CH0501.SEIKY_YM 
                               FROM CH_M_PRC_SCHDL_TEIGI CH0501 
                               WHERE CH0501.EVENT_CD = '04' AND CH0501.EVENT_YMD <= 'ZZZZZ' AND CH0501.MK_FLG = '0' ORDER BY CH0501.EVENT_YMD DESC) CH0501_SUB WHERE ROWNUM = 1)
AND   CH0101_01.SEIKY_YM = (SELECT CH0501_SUB.SEIKY_YM 
                            FROM (SELECT CH0501.SEIKY_YM 
                                  FROM CH_M_PRC_SCHDL_TEIGI CH0501 
                                  WHERE CH0501.EVENT_CD = '04' AND CH0501.EVENT_YMD <= 'ZZZZZ' AND CH0501.MK_FLG = '0' ORDER BY CH0501.EVENT_YMD DESC) CH0501_SUB WHERE ROWNUM = 1)
AND   CH0051.SEIKYUS_HAKKO_TG_FLG = '1'
AND   CH0051.MK_FLG = '0'
AND   CH0101_01.MK_FLG = '0'
AND   CH0041_01.KKSHI_CNT > 0
AND   CH0041_01.MK_FLG = '0'
AND   CH0041_02.KKSHI_CNT = 0
AND   CH0041_02.MK_FLG = '0'
AND   CH0401_01.SYS_CD = 'CH'
AND   CH0401_01.WORK_KINO_SKBT_CD = 'MISG_GSKY'
AND   CH0401_01.CHSHT_CHG_CD = '1'
AND   CH0401_01.PRC_KMK_CS_CHGE_TSTAYMD <= 'ZZZZZ'
AND   CH0401_01.PRC_KMK_CS_CHGE_TENDYMD >= 'ZZZZZ'
AND   CH0401_01.MK_FLG = '0'
AND   CH0101_02.MK_FLG = '0'
AND   CH0101_02.PRC_KMK_CD NOT LIKE ('2103%')
AND   CH0101_02.PRC_KMK_CD NOT LIKE ('1103%')
ORDER BY
 SEIKY_KEI_NO