/* Formatted on 1/31/2020 5:29:26 PM (QP5 v5.185.11230.41888) */
INSERT /*+ APPEND */
      INTO  test_usage_facts_tt (characteristics,
                                 usage_bin,
                                 usage_bin_period,
                                 ui_from,
                                 ui_to,
                                 season_config_id,
                                 program_id,
                                 cust_class_ind,
                                 day_type,
                                 weather_zone,
                                 rate_group_id,
                                 channel_id,
                                 dummy_location_id,
                                 dummy_account_id,
                                 dummy_co_id,
                                 total_usage,
                                 avg_usage,
                                 total_days,
                                 read_date,
                                 next_read_date)
   SELECT characteristics,
          usage_bin,
          usage_bin_period,
          ui_from,
          ui_to,
          season_config_id,
          program_id,
          cust_class_ind,
          day_type,
          weather_zone,
          rate_group_id,
          channel_id,
          dummy_location_id,
          dummy_account_id,
          dummy_co_id,
          total_usage,
          avg_usage,
          total_days,
          read_date,
          next_read_date
     FROM (WITH dt
                AS (SELECT /*+ materialize */
                          billingcycle, read_date, next_read_date
                      FROM (SELECT billingcycle,
                                   read_date,
                                   LEAD (
                                      read_date)
                                   OVER (PARTITION BY billingcycle
                                         ORDER BY read_date)
                                      next_read_date
                              FROM test_dates)
                     WHERE next_read_date BETWEEN   TO_DATE (p_entry_date)
                                                  - parm_days_for_emr_recovery
                                              AND TO_DATE (p_entry_date) - 1),
                dummy_locations
                AS (SELECT /*+ materialize ordered full(cum) parallel(cum,8)  use_nl(md) use_nl(sm) */
                           cum.entry_date,
                           md.device_id,
                           sm.dummy_location_id,
                           sm.dummy_account_id,
                           md.readingcycle,
                           dt.billingcycle,
                           dt.read_date,
                           dt.next_read_date
                      FROM test_vee_override_gateways override,
                           test_cum_reads cum,
                           dummy_device md,
                           dt,
                           dummy_meter sm
                     WHERE     cum.device_id = md.device_id
                           AND p_entry_date <> dt.next_read_date
                           AND cum.entry_date = p_entry_date
                           AND md.readingcycle = dt.billingcycle
                           AND md.device_id = sm.device_id
                           AND TO_DATE (p_entry_date) - 1 / 86400 BETWEEN sm.effective_date
                                                                      AND   sm.end_effective_date
                                                                          -   1
                                                                            / 86400
                           AND cum.gateway_id = override.gateway_id)
           SELECT /*+ ordered  index(mra,dummy_usage_facts_daily_N1 )  use_nl(mra)  */
                  characteristics,
                  usage_bin,
                  usage_bin_period,
                  ui_from,
                  ui_to,
                  day_type,
                  weather_zone,
                  rate_group_id,
                  channel_id,
                  mra.dummy_location_id,
                  mra.dummy_account_id,
                  avg_usage AS total_usage,
                  avg_usage,
                  1 total_days,
                  dummy_locations.read_date,
                  dummy_locations.next_read_date,
                  season_config_id,
                  program_id,
                  cust_class_ind,
                  mra.dummy_co_id
             FROM dummy_locations, dummy_usage_facts_daily mra
            WHERE     1 = 1
                  AND dummy_locations.dummy_location_id =
                         mra.dummy_location_id
                  AND dummy_locations.dummy_account_id = mra.dummy_account_id
                  AND mra.ui_to >= dummy_locations.read_date + 1
                  AND mra.ui_to <= dummy_locations.next_read_date
                  AND mra.readcycle = dummy_locations.readingcycle)
/* Note : I have used dummy_names in above query */