ALTER VIEW "EMOBILITY"."V_CHARGEDATA_EMP_EOPERATE" ( "ID", "CALLID", "CDRID", "EVSEID", "CONTRACTID", "RFID", "INSTANCE", "TOKENTYPE", "CONSUMEDENERGY", 
"OPERATORID", "OPERATORNAME", "OPERATORSUBMANDANT", "FREECHARGE", "CHARGEPOINTTYPE", "METERID", "MAXSOCKETPOWER", "ADDRESS", "ZIP", "CITY", "COUNTRY", "PROVIDERID", 
"PROVIDERNAME", "CHARGINGSTARTTIME", "CHARGINGSTOPTIME",  "PARKINGSTARTTIME", "PARKINGSTOPTIME", "METERSTART", "METERSTOP", "TARIFF", "CURRENCY", "VAT", 
"BILLINGVALUEBRUTTO", "BILLINGVALUENETTO", "PARKINGTIMEPRICEBRUTTO", "PARKINGTIMEPRICENETTO", "ADITIONALPRICEBRUTTO", "ADITIONALPRICENETTO", "KWHNETPRICE", 
"MINUTENETPRICE", "SESSIONNETPRICE", "DURATION", "EICHRECHTTYPE", "MILEAGE", "SUBSCRIPTION_ID", "CREATE_TS", "UPDATE_TS", "STATUS", "BILLING", "SYSTYPE", "VIEWVERSION"
 ) AS 
select
	'EOP' || "BDR_ID",
	 "BDR_ID",
	 "BDR_ID",
	 "CHARGEPOINT_ID",
	 "EMAID",
     UPPER("UNIQUE_ID"),
     UPPER("UNIQUE_ID"),
	 "CHARGE_DATA_AUTHORIZATION_TYPE",
	 "CHARGE_DATA_CONSUMPTION_KWH" * 1000,
	 "CHARGEPOINT_OPERATOR_ID",
	 "CHARGEPOINT_OPERATOR_NAME",
	 'OPERATORSUBMANDANT',
	 'false',
	 "CHARGEPOINT_TYPE",
	 "CHARGEPOINT_METER_ID",
	 0,
"CHARGEPOINT_ADDRESS_STREET",
	 "CHARGEPOINT_ADDRESS_POSTALCODE",
 "CHARGEPOINT_ADDRESS_CITY",
	 "CHARGEPOINT_ADDRESS_COUNTRY",
	 "EMOBILITY_PROVIDER_ID",
	 "EMOBILITY_PROVIDER_NAME",
"CHARGE_DATA_BEGIN_UTC",
"CHARGE_DATA_END_UTC",
"CHARGE_DATA_BEGIN_UTC",
"CHARGE_DATA_END_UTC",
	 0,
	 0,
	 'TARIFF',
	CASE 
	    WHEN "PRICING_ITEMS_1_TYPE" = 'CUS' THEN "PRICING_ITEMS_1_CURRENCY" 
	    WHEN "PRICING_ITEMS_2_TYPE" = 'CUS' THEN "PRICING_ITEMS_2_CURRENCY" 
	    WHEN "PRICING_ITEMS_3_TYPE" = 'CUS' THEN "PRICING_ITEMS_3_CURRENCY" 
	    WHEN "PRICING_ITEMS_4_TYPE" = 'CUS' THEN "PRICING_ITEMS_4_CURRENCY" 
	    ELSE NULL 
	END,
	 0,
0,
	CASE 
	    WHEN "PRICING_ITEMS_1_TYPE" = 'CUS' THEN "PRICING_ITEMS_1_NET_AMOUNT" 
	    WHEN "PRICING_ITEMS_2_TYPE" = 'CUS' THEN "PRICING_ITEMS_2_NET_AMOUNT" 
	    WHEN "PRICING_ITEMS_3_TYPE" = 'CUS' THEN "PRICING_ITEMS_3_NET_AMOUNT" 
	    WHEN "PRICING_ITEMS_4_TYPE" = 'CUS' THEN "PRICING_ITEMS_4_NET_AMOUNT" 
	    ELSE NULL 
	END,
0,
0,
0,
0,
	CASE 
	    WHEN "PRICING_ITEMS_1_TYPE" = 'CUS' AND "PRICING_ITEMS_1_UNIT" = 'KWH' THEN "PRICING_ITEMS_1_UNIT_PRICE" 
	    WHEN "PRICING_ITEMS_2_TYPE" = 'CUS' AND "PRICING_ITEMS_2_UNIT" = 'KWH' THEN "PRICING_ITEMS_2_UNIT_PRICE" 
	    WHEN "PRICING_ITEMS_3_TYPE" = 'CUS' AND "PRICING_ITEMS_3_UNIT" = 'KWH' THEN "PRICING_ITEMS_3_UNIT_PRICE" 
	    WHEN "PRICING_ITEMS_4_TYPE" = 'CUS' AND "PRICING_ITEMS_4_UNIT" = 'KWH' THEN "PRICING_ITEMS_4_UNIT_PRICE" 
	    ELSE NULL 
	END,
    CASE 
        WHEN "PRICING_ITEMS_1_TYPE" = 'CUS' AND "PRICING_ITEMS_1_UNIT" = 'MIN' THEN "PRICING_ITEMS_1_UNIT_PRICE" 
        WHEN "PRICING_ITEMS_2_TYPE" = 'CUS' AND "PRICING_ITEMS_2_UNIT" = 'MIN' THEN "PRICING_ITEMS_2_UNIT_PRICE" 
        WHEN "PRICING_ITEMS_3_TYPE" = 'CUS' AND "PRICING_ITEMS_3_UNIT" = 'MIN' THEN "PRICING_ITEMS_3_UNIT_PRICE" 
        WHEN "PRICING_ITEMS_4_TYPE" = 'CUS' AND "PRICING_ITEMS_4_UNIT" = 'MIN' THEN "PRICING_ITEMS_4_UNIT_PRICE" 
        ELSE NULL 
    END,
    CASE 
        WHEN "PRICING_ITEMS_1_TYPE" = 'CUS' AND "PRICING_ITEMS_1_UNIT" = 'ST' THEN "PRICING_ITEMS_1_UNIT_PRICE" 
        WHEN "PRICING_ITEMS_2_TYPE" = 'CUS' AND "PRICING_ITEMS_2_UNIT" = 'ST' THEN "PRICING_ITEMS_2_UNIT_PRICE" 
        WHEN "PRICING_ITEMS_3_TYPE" = 'CUS' AND "PRICING_ITEMS_3_UNIT" = 'ST' THEN "PRICING_ITEMS_3_UNIT_PRICE" 
        WHEN "PRICING_ITEMS_4_TYPE" = 'CUS' AND "PRICING_ITEMS_4_UNIT" = 'ST' THEN "PRICING_ITEMS_4_UNIT_PRICE" 
        ELSE NULL 
    END,
    CASE 
        WHEN "PRICING_ITEMS_1_TYPE" = 'CUS' AND "PRICING_ITEMS_1_UNIT" = 'MIN' THEN "PRICING_ITEMS_1_QUANTITY" 
        WHEN "PRICING_ITEMS_2_TYPE" = 'CUS' AND "PRICING_ITEMS_2_UNIT" = 'MIN' THEN "PRICING_ITEMS_2_QUANTITY" 
        WHEN "PRICING_ITEMS_3_TYPE" = 'CUS' AND "PRICING_ITEMS_3_UNIT" = 'MIN' THEN "PRICING_ITEMS_3_QUANTITY" 
        WHEN "PRICING_ITEMS_4_TYPE" = 'CUS' AND "PRICING_ITEMS_4_UNIT" = 'MIN' THEN "PRICING_ITEMS_4_QUANTITY" 
        ELSE 0 
    END,
	 "CHARGE_DATA_EKA_STATUS",
	 0,
	 "SUBSCRIPTION_ID",
"CREATE_TS",
"UPDATE_TS",
'','Y',
	 'EOPERATE',
	 'V 1.4/28.09.2020'
from "EOPERATE"."CHARGEDATA" 
where 
    (
        "PRICING_ITEMS_1_TYPE" = 'CUS' AND ("PRICING_ITEMS_1_QUANTITY" > 2 OR "PRICING_ITEMS_1_UNIT" != 'MIN')
        OR
        "PRICING_ITEMS_2_TYPE" = 'CUS' AND ("PRICING_ITEMS_2_QUANTITY" > 2 OR "PRICING_ITEMS_2_UNIT" != 'MIN')
        OR
        "PRICING_ITEMS_3_TYPE" = 'CUS' AND ("PRICING_ITEMS_3_QUANTITY" > 2 OR "PRICING_ITEMS_3_UNIT" != 'MIN')
        OR
        "PRICING_ITEMS_4_TYPE" = 'CUS' AND ("PRICING_ITEMS_4_QUANTITY" > 2 OR "PRICING_ITEMS_4_UNIT" != 'MIN')
    )
and "CHARGE_DATA_CONSUMPTION_KWH" > 0 
and "SUBSCRIPTION_ID" <> 'SU20200729002' 
--and "PLUGTYPE" IS NOT NULL