You can write the code something along the lines.
with t(id, meterReading) as (
select 1, 'Normal' from dual
union all
select 2 , 'Border Line' from dual
union all
select 3, 'Very Poor' from dual
)
, t_meter as (
select 1 recordno, 'Normal' meter1, null meter2, null meter3 from dual
union all
select 2 recordno, 'Normal' meter1, 'Very Poor' meter2, null meter3 from dual
union all
select 3 recordno, 'Normal' meter1, null meter2, 'Border Line' meter3 from dual
union all
select 4 recordno, 'Normal' meter1, 'Very Poor' meter2, 'Border Line' meter3 from dual
)
SELECT
  recordno,
  (
    SELECT
      meterreading
    FROM
      t where
      id IN (
        SELECT
          MAX(id)
        FROM
          t
        WHERE
          meterreading = nvl(meter1, 'x')
          OR meterreading = nvl(meter2, 'x') or meterreading = nvl(meter3, 'x')
      )
  )
FROM
  t_meter;

INSERT INTO AHI_CAPDEVIATION_ALLOWABLE_VALUES VALUES(1,'Normal',sysdtae,-1,'MAN');
INSERT INTO AHI_CAPDEVIATION_ALLOWABLE_VALUES VALUES(2,'Borderline',sysdate,-1,'MAN');
INSERT INTO AHI_CAPDEVIATION_ALLOWABLE_VALUES VALUES(3,'Very Poor',sysdate,-1,'MAN');

INSERT INTO ART_INSERT_TRANS
select  
INLINE_VIEW.ASSET_NUMBER AS ASSET_NUMBER,
INLINE_VIEW.ASSET_TYPE AS ASSET_TYPE
,
AHI_CAPDEVIATION_ALLOWABLE_VALUES .meterreadings

from (((((((((((((
SELECT 
 F_AMIS_ASSET_ATTRIBUTE2_1.SRC_ASSETNUM AS SRC_ASSETNUM ,
 F_AMIS_ASSET_ATTRIBUTE2_1.ALN_VALUE AS ALN_VALUE ,
 AHI_ASSETS_INSTRUMENT_TF.ASSET_NUMBER AS ASSET_NUMBER ,
 AHI_ASSETS_INSTRUMENT_TF.DEVICE_POSITION AS DEVICE_POSITION ,
 AHI_ASSETS_INSTRUMENT_TF.SITE AS SITE ,
 AHI_ASSETS_INSTRUMENT_TF.ASSET_TYPE AS ASSET_TYPE ,
 AHI_ASSETS_INSTRUMENT_TF.MANUFACTURER AS MANUFACTURER ,
 AHI_ASSETS_INSTRUMENT_TF.MODEL AS MODEL ,
 AHI_ASSETS_INSTRUMENT_TF.YEAR_OF_MANUFACTURE AS YEAR_OF_MANUFACTURE  
FROM 
 BI_STG.AHI_ASSETS_INSTRUMENT_TF AHI_ASSETS_INSTRUMENT_TF INNER JOIN (
SELECT 
 F_AMIS_ASSET_ATTRIBUTE2.SRC_ASSETNUM AS SRC_ASSETNUM ,
 F_AMIS_ASSET_ATTRIBUTE2.ALN_VALUE AS ALN_VALUE  
FROM 
 BI_ADS.F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE2  
WHERE
 (F_AMIS_ASSET_ATTRIBUTE2.SRC_ASSETATTRID in ('CT-CONFIG/CONSTR','VT-CONFIG/CONSTR','CVT-CONFIG/CONSTR')
and F_AMIS_ASSET_ATTRIBUTE2.IS_DELETED = 0
) 
 ) F_AMIS_ASSET_ATTRIBUTE2_1  
  ON F_AMIS_ASSET_ATTRIBUTE2_1.SRC_ASSETNUM = AHI_ASSETS_INSTRUMENT_TF.ASSET_NUMBER
WHERE
 ((AHI_ASSETS_INSTRUMENT_TF.ASSET_TYPE = 'CT' and F_AMIS_ASSET_ATTRIBUTE2_1.ALN_VALUE in ('OUTDOOR','PORCELAN','STDALONE','STNDALON'))
or
(AHI_ASSETS_INSTRUMENT_TF.ASSET_TYPE = 'VT' and F_AMIS_ASSET_ATTRIBUTE2_1.ALN_VALUE in ('OUTDOOR','STDALONE'))
or
(AHI_ASSETS_INSTRUMENT_TF.ASSET_TYPE = 'CVT' and F_AMIS_ASSET_ATTRIBUTE2_1.ALN_VALUE = ('STDALONE'))
or
(AHI_ASSETS_INSTRUMENT_TF.ASSET_TYPE = 'NCT' and F_AMIS_ASSET_ATTRIBUTE2_1.ALN_VALUE in ('OUTDOOR','PORCELAN','STDALONE','STNDALON'))
) 
 ) INLINE_VIEW LEFT OUTER JOIN BI_STG.STG_AHI_SITE_CORROSION_CODE STG_AHI_SITE_CORROSION_CODE  
  ON STG_AHI_SITE_CORROSION_CODE.SITE = INLINE_VIEW.SITE
   ) LEFT OUTER JOIN (
SELECT 
 DISTINCT 
 F_AMIS_MEASUREMENT.MEASUREMENT AS MEASUREMENT ,
 F_AMIS_MEASUREMENT.SRC_ASSETNUM AS SRC_ASSETNUM  
FROM 
 BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT  
WHERE
 (F_AMIS_MEASUREMENT.CURRENT_READING = 'Y'
AND F_AMIS_MEASUREMENT.CURRENT_READING_EXISTS = 'Y'
AND F_AMIS_MEASUREMENT.IS_DELETED = 0
AND F_AMIS_MEASUREMENT.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )
AND F_AMIS_MEASUREMENT.MEASUREMENT IS NOT NULL
AND F_AMIS_MEASUREMENT.SRC_METERNAME = 'VTIN_PWR-TRNSFMR-1'
) 
 ) DISTINCT_MEASURE  
  ON DISTINCT_MEASURE.SRC_ASSETNUM = INLINE_VIEW.ASSET_NUMBER
   ) LEFT OUTER JOIN (
SELECT 
 DISTINCT 
 F_AMIS_MEASUREMENT1.MEASUREMENT AS MEASUREMENT ,
 F_AMIS_MEASUREMENT1.SRC_ASSETNUM AS SRC_ASSETNUM  
FROM 
 BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT1  
WHERE
 (F_AMIS_MEASUREMENT1.CURRENT_READING = 'Y'
AND F_AMIS_MEASUREMENT1.CURRENT_READING_EXISTS = 'Y'
AND F_AMIS_MEASUREMENT1.IS_DELETED = 0
AND F_AMIS_MEASUREMENT1.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )
AND F_AMIS_MEASUREMENT1.MEASUREMENT IS NOT NULL
AND F_AMIS_MEASUREMENT1.SRC_METERNAME = 'CTIN_PWR-TRNSFMR-1'
) 
 ) DISTINCT_MEASURE_1  
  ON DISTINCT_MEASURE_1.SRC_ASSETNUM = INLINE_VIEW.ASSET_NUMBER
   ) LEFT OUTER JOIN (
SELECT 
 F_AMIS_MEASUREMENT2.SRC_ASSETNUM AS SRC_ASSETNUM ,
 F_AMIS_MEASUREMENT2.MEASUREMENT AS MEASUREMENT  
FROM 
 BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT2  
WHERE
 (F_AMIS_MEASUREMENT2.CURRENT_READING = 'Y'
AND F_AMIS_MEASUREMENT2.CURRENT_READING_EXISTS = 'Y'
AND F_AMIS_MEASUREMENT2.IS_DELETED = 0
AND F_AMIS_MEASUREMENT2.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )
AND F_AMIS_MEASUREMENT2.MEASUREMENT IS NOT NULL
AND F_AMIS_MEASUREMENT2.SRC_METERNAME = 'ITPF_%-POWER-FACTOR'
) 
GROUP BY
 F_AMIS_MEASUREMENT2.SRC_ASSETNUM,F_AMIS_MEASUREMENT2.MEASUREMENT 
 ) F_AMIS_MEASUREMENT2_1  
  ON F_AMIS_MEASUREMENT2_1.SRC_ASSETNUM = INLINE_VIEW.ASSET_NUMBER
   ) LEFT OUTER JOIN (
SELECT 
 SRC_LOCATION SRC_LOCATION ,
 SRC_ASSETATTRI_NUMERIC_VALUE DIRECT_COST_CRITICALITY ,
 SRC_ASSETATT_1_NUMERIC_VALUE PUBLICSAFETY_CRITICALITY ,
 SRC_ASSETATT_2_NUMERIC_VALUE WORKSAFETY_CRITICALITY ,
 SRC_ASSETATT_3_NUMERIC_VALUE SERVICE_PERF_CRITICALITY ,
 SRC_ASSETATT_4_NUMERIC_VALUE ENVIRONMENT_CRITICALITY  
FROM 
 (
SELECT 
 F_AMIS_LOCATION_ATTRIBUTE_1.SRC_LOCATION AS SRC_LOCATION ,
 F_AMIS_LOCATION_ATTRIBUTE_1.NUMERIC_VALUE AS NUMERIC_VALUE ,
 F_AMIS_LOCATION_ATTRIBUTE_1.SRC_ASSETATTRID AS SRC_ASSETATTRID  
FROM 
 BI_ADS.F_AMIS_LOCATION_ATTRIBUTE F_AMIS_LOCATION_ATTRIBUTE_1  
WHERE
 (F_AMIS_LOCATION_ATTRIBUTE_1.SRC_ASSETATTRID IN ('DIRECT-COST-CRITICALITY','PUBLICSAFETY-CRITICALITY','WORKSAFETY-CRITICALITY','SERVICE-PERF-CRITICALITY'
   ,'ENVIRONMENT-CRITICALITY','PRIMARY-SYS-VOLTAGE')
) 
 )  
 PIVOT   
 ( 
   MIN(NUMERIC_VALUE) AS NUMERIC_VALUE  
  for SRC_ASSETATTRID in 
  ( 
       'DIRECT-COST-CRITICALITY' AS SRC_ASSETATTRI ,  'PUBLICSAFETY-CRITICALITY' AS SRC_ASSETATT_1 ,  'WORKSAFETY-CRITICALITY' AS SRC_ASSETATT_2 ,  'SERVICE-PERF-CRITICALITY' AS SRC_ASSETATT_3 ,  'ENVIRONMENT-CRITICALITY' AS SRC_ASSETATT_4 
  )
 )
 ) PIVOT_LOC_NUMERIC_VALUE  
  ON INLINE_VIEW.DEVICE_POSITION = PIVOT_LOC_NUMERIC_VALUE.SRC_LOCATION
   ) LEFT OUTER JOIN (
SELECT 
 F_AMIS_LOCATION_ATTRIBUTE1.SRC_LOCATION AS SRC_LOCATION ,
 F_AMIS_LOCATION_ATTRIBUTE1.ALN_VALUE AS ALN_VALUE  
FROM 
 BI_ADS.F_AMIS_LOCATION_ATTRIBUTE F_AMIS_LOCATION_ATTRIBUTE1  
WHERE
 (F_AMIS_LOCATION_ATTRIBUTE1.SRC_ASSETATTRID = 'PRIMARY-SYS-VOLTAGE'
and F_AMIS_LOCATION_ATTRIBUTE1.ALN_VALUE NOT IN ('350','HVDCNTRL')
AND F_AMIS_LOCATION_ATTRIBUTE1.SRC_STATUS <> 'SCRAPPED'
) 
 ) F_AMIS_LOCATION_ATTRIBUTE1_1  
  ON INLINE_VIEW.DEVICE_POSITION = F_AMIS_LOCATION_ATTRIBUTE1_1.SRC_LOCATION
   ) LEFT OUTER JOIN (
SELECT 
 F_AMIS_MEASUREMENT3_1.SRC_ASSETNUM AS SRC_ASSETNUM ,
 F_AMIS_MEASUREMENT3_1.MEASUREMENT AS MEASUREMENT  
FROM 
 (
SELECT 
 F_AMIS_MEASUREMENT3.SRC_ASSETNUM AS SRC_ASSETNUM ,
 MIN(F_AMIS_MEASUREMENT3.MEASUREMENT) AS MEASUREMENT  
FROM 
 BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT3  
WHERE
 (F_AMIS_MEASUREMENT3.CURRENT_READING = 'Y'
AND F_AMIS_MEASUREMENT3.CURRENT_READING_EXISTS = 'Y'
AND F_AMIS_MEASUREMENT3.IS_DELETED = 0
AND F_AMIS_MEASUREMENT3.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )
AND F_AMIS_MEASUREMENT3.MEASUREMENT IS NOT NULL
AND F_AMIS_MEASUREMENT3.SRC_METERNAME in ('CAVT_EXT_CONDITION1', 'NCT_EXT_CONDITION1', 'NCTA_EXT_CONDITION1', 'CVTA_EXT_CONDITION1','CBCT_EXT_CONDITION1')
) 
GROUP BY
 F_AMIS_MEASUREMENT3.SRC_ASSETNUM 
 ) F_AMIS_MEASUREMENT3_1  
 ) F_AMIS_MEASUREMENT3_2  
  ON INLINE_VIEW.ASSET_NUMBER = F_AMIS_MEASUREMENT3_2.SRC_ASSETNUM
   ) LEFT OUTER JOIN (
SELECT 
 F_AMIS_MEASUREMENT4_1.SRC_ASSETNUM AS SRC_ASSETNUM ,
 F_AMIS_MEASUREMENT4_1.MEASUREMENT AS MEASUREMENT  
FROM 
 (
SELECT 
 F_AMIS_MEASUREMENT4.SRC_ASSETNUM AS SRC_ASSETNUM ,
 MIN(F_AMIS_MEASUREMENT4.MEASUREMENT) AS MEASUREMENT  
FROM 
 BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT4  
WHERE
 (F_AMIS_MEASUREMENT4.CURRENT_READING = 'Y'
AND F_AMIS_MEASUREMENT4.CURRENT_READING_EXISTS = 'Y'
AND F_AMIS_MEASUREMENT4.IS_DELETED = 0
AND F_AMIS_MEASUREMENT4.METER_IS_ACTIVE = 'Y'
AND F_AMIS_MEASUREMENT4.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )
AND F_AMIS_MEASUREMENT4.MEASUREMENT IS NOT NULL
AND F_AMIS_MEASUREMENT4.SRC_METERNAME in ('CAVT_EXT_CONDITION2', 'NCT_EXT_CONDITION2', 'NCTA_EXT_CONDITION2', 'CVTA_EXT_CONDITION2','CBCT_EXT_CONDITION2')
) 
GROUP BY
 F_AMIS_MEASUREMENT4.SRC_ASSETNUM 
 ) F_AMIS_MEASUREMENT4_1  
 ) F_AMIS_MEASUREMENT4_2  
  ON INLINE_VIEW.ASSET_NUMBER = F_AMIS_MEASUREMENT4_2.SRC_ASSETNUM
   ) LEFT OUTER JOIN (
SELECT 
 F_AMIS_MEASUREMENT5_1.SRC_ASSETNUM AS SRC_ASSETNUM ,
 F_AMIS_MEASUREMENT5_1.MEASUREMENT AS MEASUREMENT  
FROM 
 (
SELECT 
 F_AMIS_MEASUREMENT5.SRC_ASSETNUM AS SRC_ASSETNUM ,
 MIN(F_AMIS_MEASUREMENT5.MEASUREMENT) AS MEASUREMENT  
FROM 
 BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT5  
WHERE
 (F_AMIS_MEASUREMENT5.CURRENT_READING = 'Y'
AND F_AMIS_MEASUREMENT5.CURRENT_READING_EXISTS = 'Y'
AND F_AMIS_MEASUREMENT5.IS_DELETED = 0
AND F_AMIS_MEASUREMENT5.METER_IS_ACTIVE = 'Y'
AND F_AMIS_MEASUREMENT5.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )
AND F_AMIS_MEASUREMENT5.MEASUREMENT IS NOT NULL
AND F_AMIS_MEASUREMENT5.SRC_METERNAME in ('CAVT_EXT_CONDITION3', 'NCT_EXT_CONDITION3', 'NCTA_EXT_CONDITION3', 'CVTA_EXT_CONDITION3','CBCT_EXT_CONDITION3')
) 
GROUP BY
 F_AMIS_MEASUREMENT5.SRC_ASSETNUM 
 ) F_AMIS_MEASUREMENT5_1  
 ) F_AMIS_MEASUREMENT5_2  
  ON INLINE_VIEW.ASSET_NUMBER = F_AMIS_MEASUREMENT5_2.SRC_ASSETNUM
   ) LEFT OUTER JOIN (
SELECT 
 F_AMIS_MEASUREMENT6_1.SRC_ASSETNUM AS SRC_ASSETNUM ,
 F_AMIS_MEASUREMENT6_1.MEASUREMENT AS MEASUREMENT  
FROM 
 (
SELECT 
 F_AMIS_MEASUREMENT6.SRC_ASSETNUM AS SRC_ASSETNUM ,
 MIN(F_AMIS_MEASUREMENT6.MEASUREMENT) AS MEASUREMENT  
FROM 
 BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT6  
WHERE
 (F_AMIS_MEASUREMENT6.CURRENT_READING = 'Y'
AND F_AMIS_MEASUREMENT6.CURRENT_READING_EXISTS = 'Y'
AND F_AMIS_MEASUREMENT6.IS_DELETED = 0
AND F_AMIS_MEASUREMENT6.METER_IS_ACTIVE = 'Y'
AND F_AMIS_MEASUREMENT6.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )
AND F_AMIS_MEASUREMENT6.MEASUREMENT IS NOT NULL
AND F_AMIS_MEASUREMENT6.SRC_METERNAME in ('VTIN_PWR-TRNSFMR-2','VTIN_PWR-TRNSFMR-3','CTIN_PWR-TRNSFMR-3','CTIN_PWR-TRNSFMR-4'
,'CTIN_PWR-TRNSFMR-5','CTIN_PWR-TRNSFMR-6','CTIN_PWR-TRNSFMR-7','CTIN_PWR-TRNSFMR-8','CTIN_PWR-TRNSFMR-9')
) 
GROUP BY
 F_AMIS_MEASUREMENT6.SRC_ASSETNUM 
 ) F_AMIS_MEASUREMENT6_1  
 ) F_AMIS_MEASUREMENT6_2  
  ON INLINE_VIEW.ASSET_NUMBER = F_AMIS_MEASUREMENT6_2.SRC_ASSETNUM
   ) LEFT OUTER JOIN (
SELECT 
 PIVOT_ASSETATTR.SRC_ASSETNUM AS SRC_ASSETNUM ,
 PIVOT_ASSETATTR.TOTALCAPACITANCEATT AS TOTALCAPACITANCENVALUE ,
 PIVOT_ASSETATTR.TOPUNITCAPACITANCEATT AS TOPUNITCAPACITANCEATT ,
 PIVOT_ASSETATTR.BOTUNITCAPACITANCEATT AS BOTUNITCAPACITANCEATT  
FROM 
 (
SELECT 
 SRC_ASSETNUM SRC_ASSETNUM ,
 SRC_ASSETATTRI_NUMERIC_VALUE TOTALCAPACITANCEATT ,
 SRC_ASSETATT_1_NUMERIC_VALUE TOPUNITCAPACITANCEATT ,
 SRC_ASSETATT_2_NUMERIC_VALUE BOTUNITCAPACITANCEATT  
FROM 
 (
SELECT 
 F_AMIS_ASSET_ATTRIBUTE.SRC_ASSETNUM AS SRC_ASSETNUM ,
 F_AMIS_ASSET_ATTRIBUTE.NUMERIC_VALUE AS NUMERIC_VALUE ,
 F_AMIS_ASSET_ATTRIBUTE.SRC_ASSETATTRID AS SRC_ASSETATTRID  
FROM 
 BI_ADS.F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE INNER JOIN BI_ADS.D_AMIS_ASSET_ATTRIBUTE D_AMIS_ASSET_ATTRIBUTE  
  ON D_AMIS_ASSET_ATTRIBUTE.AMIS_ASSET_ATTRIBUTE_ID = F_AMIS_ASSET_ATTRIBUTE.AMIS_ASSET_ATTRIBUTE_ID
WHERE
 (F_AMIS_ASSET_ATTRIBUTE.SRC_ASSETATTRID IN('TOTAL-CAPACITANCE','TOP-UNIT-CAPACTNCE','BOT-UNIT-CAPACTNCE')
) 
 )  
 PIVOT   
 ( 
   MIN(NUMERIC_VALUE) AS NUMERIC_VALUE  
  for SRC_ASSETATTRID in 
  ( 
       'TOTAL-CAPACITANCE' AS SRC_ASSETATTRI ,  'TOP-UNIT-CAPACTNCE' AS SRC_ASSETATT_1 ,  'BOT-UNIT-CAPACTNCE' AS SRC_ASSETATT_2 
  )
 )
 ) PIVOT_ASSETATTR  
 ) PIVOT_ASSETATTR_1  
  ON INLINE_VIEW.ASSET_NUMBER = PIVOT_ASSETATTR_1.SRC_ASSETNUM
   ) LEFT OUTER JOIN (
SELECT 
 PIVOT_ASSETMETER.ASSET_NUMBER AS SRC_ASSETNUM ,
 PIVOT_ASSETMETER.COMPLETE_STACK_METER AS COMPLETESTACKMETER ,
 PIVOT_ASSETMETER.C1_CAPACITANCE_METER AS C1CAPACITNCEMETER ,
 PIVOT_ASSETMETER.C2_CAPACITANCE_METER AS C2CAPACITANCEMETER ,
 PIVOT_ASSETMETER.COMPLETE_STACKPREVMETER AS COMPLETE_STACKPREVMETER ,
 PIVOT_ASSETMETER.C1CAPACTIANCEPREVMETER AS C1CAPACTIANCEPREVMETER ,
 PIVOT_ASSETMETER.C2CAPACITANCEPREVMETER AS C2CAPACITANCEPREVMETER  
FROM 
 (
SELECT 
 AMIS_ASSET_ID AMIS_ASSET_ID ,
 ASSET_NUMBER ASSET_NUMBER ,
 METER_NAME_1_MEASUREMENT COMPLETE_STACK_METER ,
 METER_NAME_2_MEASUREMENT C1_CAPACITANCE_METER ,
 METER_NAME_3_MEASUREMENT C2_CAPACITANCE_METER ,
 METER_NAME_1_PREVIOUS_MEASU COMPLETE_STACKPREVMETER ,
 METER_NAME_2_PREVIOUS_MEASU C1CAPACTIANCEPREVMETER ,
 METER_NAME_3_PREVIOUS_MEASU C2CAPACITANCEPREVMETER  
FROM 
 (
SELECT 
 F_AMIS_MEASUREMENT7.AMIS_ASSET_ID AS AMIS_ASSET_ID ,
 F_AMIS_MEASUREMENT7.SRC_ASSETNUM AS ASSET_NUMBER ,
 F_AMIS_MEASUREMENT7.MEASUREMENT AS MEASUREMENT ,
 F_AMIS_MEASUREMENT7.PREVIOUS_MEASUREMENT AS PREVIOUS_MEASU ,
 D_AMIS_METER.METER_NAME AS METER_NAME  
FROM 
 BI_ADS.D_AMIS_METER D_AMIS_METER INNER JOIN BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT7  
  ON D_AMIS_METER.AMIS_METER_ID=F_AMIS_MEASUREMENT7.AMIS_METER_ID
WHERE
 (D_AMIS_METER.METER_NAME IN( 'CCAP_CAPACTNCE-COMPL-STK','CCAP_CAPACTNCE-C1', 'CCAP_CAPACTNCE-C2')
) AND  (F_AMIS_MEASUREMENT7.CURRENT_READING = 'Y' and F_AMIS_MEASUREMENT7.METER_IS_ACTIVE = 'Y' and F_AMIS_MEASUREMENT7.CURRENT_READING_EXISTS = 'Y' AND F_AMIS_MEASUREMENT7.IS_DELETED = 0 AND F_AMIS_MEASUREMENT7.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )
) 
 )  
 PIVOT   
 ( 
   MIN(MEASUREMENT) AS MEASUREMENT ,  MIN(PREVIOUS_MEASU) AS PREVIOUS_MEASU  
  for METER_NAME in 
  ( 
       'CCAP_CAPACTNCE-COMPL-STK' AS METER_NAME_1 ,  'CCAP_CAPACTNCE-C1' AS METER_NAME_2 ,  'CCAP_CAPACTNCE-C2' AS METER_NAME_3 
  )
 )
 ) PIVOT_ASSETMETER  
 ) PIVOT_ASSETMETER_1  
  ON INLINE_VIEW.ASSET_NUMBER = PIVOT_ASSETMETER_1.SRC_ASSETNUM
   ) LEFT OUTER JOIN (
SELECT 
 F_AMIS_ASSET_ATTRIBUTE1.SRC_ASSETNUM AS SRC_ASSETNUM ,
 F_AMIS_ASSET_ATTRIBUTE1.ALN_VALUE AS ALN_VALUE  
FROM 
 BI_ADS.F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE1  
WHERE
 (F_AMIS_ASSET_ATTRIBUTE1.SRC_ASSETATTRID = 'EXT-INSUL'
and F_AMIS_ASSET_ATTRIBUTE1.IS_DELETED = 0
) 
 ) F_AMIS_ASSET_ATTRIBUTE1_1  
  ON INLINE_VIEW.ASSET_NUMBER= F_AMIS_ASSET_ATTRIBUTE1_1.SRC_ASSETNUM
FULL JOIN bi_stg.AHI_CAPDEVIATION_ALLOWABLE_VALUES AHI_CAPDEVIATION_ALLOWABLE_VALUES 
 on meterreadings=NVL((CASE WHEN INLINE_VIEW.ASSET_TYPE='CVT' THEN
CASE
  WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER is not null and 
    PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER is not null 
    AND PIVOT_ASSETMETER_1.COMPLETESTACKMETER != 0 
    AND PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER != 0 THEN
  CASE  
  -- Normal
WHEN (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) between -5 and 9.99 THEN 'Normal'
WHEN (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) between 10 and 14.99 
OR (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) BETWEEN -10 AND -5.1 THEN 'Borderline'
WHEN (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) between 15 and 19.99
OR (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) BETWEEN -15 AND -10.1 THEN 'Very Poor'
WHEN (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) between 20 and 99.99
OR (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) BETWEEN -100 AND -15.1 THEN 'Not Applicable'
END
ELSE
 CASE  
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) between -5 and 9.99 THEN 'Normal'
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) between 10 and 14.99
 OR (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) BETWEEN -10 AND -5.1 THEN 'Borderline'
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) between 15 and 19.99
OR (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) BETWEEN -15 AND -10.1 THEN 'Very Poor'
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) between 20 and 99.99
OR (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) BETWEEN -100 AND -15.1 THEN 'Not Applicable'
END
END 
END),'x') or meterreadings= NVL((CASE WHEN INLINE_VIEW.ASSET_TYPE='CVT' THEN 
CASE  
WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER is not null and  
    PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER is not null  
    AND PIVOT_ASSETMETER_1.C1CAPACITNCEMETER != 0  
    AND PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER != 0  
    THEN 
  CASE   
  -- Normal 
WHEN (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) BETWEEN -5 AND 4.99 THEN 'Normal' 
WHEN (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) BETWEEN 5 AND 9.99 
OR (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) BETWEEN -10 AND -5.1 THEN 'Borderline' 
WHEN (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) BETWEEN 10 AND 14.99 
OR (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) BETWEEN -15 AND -10.1 THEN 'Very Poor' 
WHEN (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) BETWEEN 15 and 99.99  
OR (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) BETWEEN -100 AND -15.1 then 'Not Applicable' 
END 
ELSE 
CASE   
WHEN (((PIVOT_ASSETMETER_1.C1CALC1 - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) BETWEEN -5 AND 4.99 THEN 'Normal' 
WHEN (((PIVOT_ASSETMETER_1.C1CALC1 - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) BETWEEN 5 AND 9.99 
OR (((PIVOT_ASSETMETER_1.C1CALC1 - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) BETWEEN -10 AND -5.1 THEN 'Borderline' 
WHEN (((PIVOT_ASSETMETER_1.C1CALC1 - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) BETWEEN 10 AND 14.99  
OR (((PIVOT_ASSETMETER_1.C1CALC1 - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) BETWEEN -15 AND -10.1 THEN 'Very Poor'  
WHEN (((PIVOT_ASSETMETER_1.C1CALC1 - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) BETWEEN 15 AND 99.99 
OR (((PIVOT_ASSETMETER_1.C1CALC1 - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) BETWEEN -100 AND -15.1 THEN 'Not Applicable' 
END 
END 
END),'x') OR meterreadings=NVL((CASE WHEN INLINE_VIEW.ASSET_TYPE='CVT' THEN
CASE  
WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER is not null and 
    PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER is not null 
    AND PIVOT_ASSETMETER_1.C2CAPACITANCEMETER != 0 
    AND PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER != 0 
    THEN
  CASE  
  -- Normal
WHEN (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) BETWEEN -5 and 4.99 THEN 'Normal'
WHEN (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) BETWEEN 5 AND 9.99
OR (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) BETWEEN -10 AND -5.1 THEN 'Borderline'
WHEN (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) BETWEEN 10 AND 14.99
OR (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) BETWEEN -15 AND -10.1 THEN 'Very Poor'
WHEN (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) BETWEEN 15 and 99.99
OR (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) BETWEEN -100 AND -15.1 then 'Not Applicable'
END
ELSE
CASE  
WHEN (((PIVOT_ASSETMETER_1.C2CALC1 - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) BETWEEN -5 AND 4.99 THEN 'Normal'
WHEN (((PIVOT_ASSETMETER_1.C2CALC1 - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) BETWEEN 5 AND 9.99
OR (((PIVOT_ASSETMETER_1.C2CALC1 - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) BETWEEN -10 AND -5.1 THEN 'Borderline'
WHEN (((PIVOT_ASSETMETER_1.C2CALC1 - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) BETWEEN 10 AND 14.99
OR (((PIVOT_ASSETMETER_1.C2CALC1 - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) BETWEEN -15 AND -10.1 THEN 'Very Poor'
WHEN (((PIVOT_ASSETMETER_1.C2CALC1 - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) BETWEEN 15 AND 99.99
OR (((PIVOT_ASSETMETER_1.C2CALC1 - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) BETWEEN -100 AND -15.1 THEN 'Not Applicable'
END
END
END),'x')
where (1=1)