#-----------------------------------------------------------
#--申込事業所(TU_M_MSKM_JIGSHO-KK_SELECT_001)
#--番ポ工事当日通知ファイル作成
#--電話番号と機器設置場所住所コードから申込事業所情報を取得
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
  TU0041.MSKM_JIGSHONM
FROM TU_M_MSKM_JIGSHO TU0041

  INNER JOIN ZM_M_TELNO ZM0121
  ON  ZM0121.TELNO = ?

  INNER JOIN ZM_M_KYOKU_NO ZM0301
  ON  ZM0301.KYOKU_NO = ZM0121.KYOKU_NO

  INNER JOIN ZM_M_AD_TG_NO_KUKAKU ZM0091
  ON  ZM0091.AD_CD = ?
  AND ZM0091.AREACD = ZM0301.AREACD

WHERE ? BETWEEN ZM0121.TELNO_TSTAYMD AND ZM0121.TELNO_TENDYMD
AND   ZM0121.MK_FLG = '0'

AND   ? BETWEEN ZM0301.KYOKU_NO_TSTAYMD AND ZM0301.KYOKU_NO_TENDYMD
AND   ZM0301.MK_FLG = '0'

AND   ? BETWEEN ZM0091.AD_TG_NO_KUKAKU_TSTAYMD AND ZM0091.AD_TG_NO_KUKAKU_TENDYMD
AND   ZM0091.RSV_APLY_YMD || ZM0091.GENE_ADD_DTM = (
  SELECT MAX(ZM0091_GENE.RSV_APLY_YMD || ZM0091_GENE.GENE_ADD_DTM) AS G_MAX
  FROM   ZM_M_AD_TG_NO_KUKAKU ZM0091_GENE
  WHERE  ZM0091_GENE.AD_CD = ZM0091.AD_CD
  AND    ZM0091_GENE.NO_KUKAKU_CD = ZM0091.NO_KUKAKU_CD
  AND    ZM0091_GENE.RSV_APLY_YMD <= ?
  AND    ZM0091_GENE.MK_FLG = '0')
AND   TU0041.MSKM_JIGSHO_NO = ZM0091.MSKM_JIGSHO_NO
AND   TU0041.MK_FLG = '0'
GROUP BY TU0041.MSKM_JIGSHONM

#-

#-----------------------------------------------------------
#--申込事業所(TU_M_MSKM_JIGSHO-KK_SELECT_002)
#--番ポ工事当日通知ファイル作成
#--電話番号の上６桁と一致する申込事業者情報を取得
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT
  TU0041.MSKM_JIGSHO_NO
 ,TU0041.MSKM_JIGSHONM
FROM TU_M_MSKM_JIGSHO TU0041

  INNER JOIN TU_M_BMPKJAREA_TGKB TU0121
  ON  TU0121.AREACD || TU0121.SHINAI_KYOKU_NO = ?

WHERE TU0121.BMP_KOJI_MSKM_JIGSHO_NO = TU0041.MSKM_JIGSHO_NO
AND   TU0041.MK_FLG = '0'

#-