#-----------------------------------------------------------
#--工程進捗メール送信実績(KK_T_STPPRML_SND_JSK-KK_SELECT_001)
#--同一工事案件番号中でメール送信番号が設定されているかつメール送信ステータスが未送信のものを抽出する
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
    KK1461.STPPRML_SND_JSK_NO,
    KK1461.SVC_KEI_NO,
    KK1461.MAIL_SEND_NO_KTAI,
    KK1461.MAIL_SEND_STAT_KTAI,
    KK1461.MAIL_SEND_NO_PC,
    KK1461.MAIL_SEND_STAT_PC,
    KK1461.MSKM_DTL_NO
FROM
    KK_T_STPPRML_SND_JSK KK1461,
    (
    SELECT
        MAX(IN_KK1461.STPPRML_SND_JSK_NO) AS STPPRML_SND_JSK_NO
    FROM
        KK_T_STPPRML_SND_JSK IN_KK1461
    WHERE
        IN_KK1461.MK_FLG ='0'
    GROUP BY
        IN_KK1461.KOJIAK_NO
    ) KEYS
WHERE
    KK1461.STPPRML_SND_JSK_NO = KEYS.STPPRML_SND_JSK_NO AND
    ((KK1461.MAIL_SEND_NO_KTAI IS NOT NULL AND
    KK1461.MAIL_SEND_STAT_KTAI = '001') OR
    (KK1461.MAIL_SEND_NO_PC IS NOT NULL AND
    KK1461.MAIL_SEND_STAT_PC = '001')) AND
    KK1461.MK_FLG ='0'

#-