SELECT  tmp.id_meter AS meter_code,
                    tmp.id_workload AS job_id,
                    CB.ID_BATCH AS feeder_code,
                    cm.d_gather AS gathered,
                    MB.METER_COUNT AS mtr_count,
                    codes (CM.t_phase) AS phase,
                    codes (cm.o_prepaid) AS Payment,
                    codes (cm.t_tension) AS Tension,
                    CB.ID_FEEDER AS Feeder_Name,
                    CW.ACTIVITY_NAME AS Substation,
                    sl.location as SUB_LOCATION,
                    sl.road as SUB_road,
                    sl.region as sub_region,
                    codes (cm.t_manufacturer) AS Manufacturer,
                    cm.id_premise AS MB_Number,
                    codes (cm.t_usage) AS Usage,
                    cm.n_lect AS Readings,
                    cm.no_meter AS meter_number,
                    tmp.num_apa AS ICS_MTR_NO,
                   get_last_reading (tmp.nis_rad, tmp.num_apa) AS LastReadings,
                   codes(tmp.co_marca) AS Manufacturer,
                    tmp.nis_rad AS SRN,
                  GET_USAGE_TYPE (tmp.nis_rad, tmp.num_apa) AS Usage,
                    tmp.f_inst AS Installed,
                    tmp.f_lvto AS Removed,
                    icl.nom_local AS locality,
                    icm.nom_munic AS municipality,
                    TRIM (
                          icf.num_puerta
                       || ' '
                       || icj.nom_calle
                       || ' '
                       || ict.desc_tipo)
                       AS address,
                    icf.ref_dir AS plot_reference,
                    icf.num_itin AS itinerary,
                    tmp.nif_apa AS prn,
                    tmp.aol_pm As aol_PM,
                    DECODE (TMP.IND_SOURCE,
                            'A', 'Apmedida',
                            'H', 'HApmedida')
                       AS Source,
                    cm.gis_x,
                    cm.gis_y,
                    tmp.REVIEW_TYPE as Review_type,
                    tmp.d_inserted date_inserted
               FROM TMP_ADHOC_REVIEW tmp,
                    cam_meters cm,
                    cam_batches cb,
                    fincas@ics.world icf,
                    tipos@ics.world ict,
                    callejero@ics.world icj,
                    localidades@ics.world icl,
                    municipios@ics.world icm,
                    bdiv10_meterbox mb,
                    cam_workloads cw
                    LEFT JOIN
          (SELECT j.codigo AS job_id, s.LOCATION, s.road, j.region
             FROM bdi_posicional_adminis.bdiv10_historico_trabajos j,
                  bdi_posicional_adminis.bdiv10_sec_local s
            WHERE j.sec_substation_v10 = '3000:' || s.codigo
                  AND s.onis_ver = 0) sl ON cw.id_workload = sl.job_id
              WHERE     tmp.id_meter = cm.id_meter
                    AND tmp.id_workload = cm.id_workload
                    AND cm.id_workload = CW.ID_WORKLOAD
                    AND CW.ID_BATCH = CB.ID_BATCH
                    AND icf.nif = tmp.nif_apa
                    AND icf.cod_calle = icj.cod_calle
                    AND icj.cod_local = icl.cod_local
                    AND icl.cod_munic = icm.cod_munic
                    AND icj.tip_via = ict.tipo
                    and cm.id_premise = mb.codigo
                    AND  tmp.REVIEW_TYPE = 'FLEXI-DATES' and  tmp.f_inst < to_date('201411','yyyymm')
                    AND tmp.d_inserted =  '20151105'
                    order by tmp.id_workload,tmp.nif_apa,tmp.f_inst desc;