INSERT INTO A_TABLE (TOOL_NAME,
                     SHIFT_START_DT,
                     WARN_ALARM_COUNT,
                     HALT_ALARM_COUNT)
   WITH tmp_distinct_wafer_metrics_gtt
        AS (SELECT DISTINCT tool_name, shift_start_dt
              FROM temp_manu1),
        tmp_alert_count
        AS (  SELECT wm_ps.tool_name,
                     wm_ps.shift_start_dt,
                     SUM (DECODE (TRIM (kpiv_alarm.severity), 'WARN', 1, 0))
                        warn_alarm_count,
                     SUM (DECODE (TRIM (kpiv_alarm.severity), 'HALT', 1, 0))
                        halt_alarm_count
                FROM tbl_fiscal_calendar fiscal_cal
                     INNER JOIN tmp_distinct_wafer_metrics_gtt wm_ps
                        ON (fiscal_cal.startdatetime = wm_ps.shift_start_dt)
                     LEFT OUTER JOIN wafnrm_owner.kpiv_alarm kpiv_alarm
                        ON (    wm_ps.tool_name = kpiv_alarm.mach
                            AND kpiv_alarm.pdatetime BETWEEN TO_DATE (
                                                                wm_ps.shift_start_dt,
                                                                'YYYYMMDD HH24MISS')
                                                         AND   TO_DATE (
                                                                  wm_ps.shift_start_dt,
                                                                  'YYYYMMDD HH24MISS')
                                                             + 1)
                     INNER JOIN
                     WLOSSITE_OWNER.TBL_XSITE_EQP_STATE_CHANGES xsite_eqp_info
                        ON (    (   xsite_eqp_info.state LIKE '%PROD%'
                                 OR xsite_eqp_info.state LIKE '%IDLE%')
                            AND xsite_eqp_info.FAC = 'NRM'
                            AND KPIV_ALARM.MACH = XSITE_EQP_INFO.EQP_NAME
                            AND KPIV_ALARM.PDATETIME BETWEEN XSITE_EQP_INFO.STATE_START_DT
                                                         AND XSITE_EQP_INFO.STATE_END_DT
                            AND xsite_eqp_info.ACTUAL_STATE_START_DT_KEY =
                                   fiscal_cal.fiscalcalendar_key)
            GROUP BY wm_ps.tool_name, wm_ps.shift_start_dt)
   SELECT a.tool_name,
          a.shift_start_dt,
          NVL (b.warn_alarm_count, 0),
          NVL (b.halt_alarm_count, 0)
     FROM tmp_distinct_wafer_metrics_gtt a
          LEFT OUTER JOIN tmp_alert_count b
             ON (    a.tool_name = b.tool_name
                 AND a.shift_start_dt = b.shift_start_dt);