102 lines
3.8 KiB
SQL
102 lines
3.8 KiB
SQL
create schema EOPERATE;
|
|
|
|
-- Version 1.2 / 25.09.2020
|
|
-- DROP SEQUENCE "EOPERATE"."SEQ_TRANSACTIONDATA_ID";
|
|
-- CREATE SEQUENCE "EOPERATE"."SEQ_TRANSACTIONDATA_ID";
|
|
DROP TABLE "EOPERATE"."CHARGEDATA";
|
|
CREATE COLUMN TABLE "EOPERATE"."CHARGEDATA" ("BDR_ID" NVARCHAR(50) NOT NULL,
|
|
"EMAID" NVARCHAR(50) NOT NULL ,
|
|
"UNIQUE_ID" NVARCHAR(50) ,
|
|
"CREATION_DATE_UTC" NVARCHAR(50),
|
|
"CHARGEPOINT_ID" NVARCHAR(50),
|
|
"CHARGEPOINT_TYPE" NVARCHAR(10),
|
|
"CHARGEPOINT_METER_ID" NVARCHAR(50),
|
|
"CHARGEPOINT_ADDRESS_COUNTRY" NVARCHAR(50),
|
|
"CHARGEPOINT_ADDRESS_POSTALCODE" NVARCHAR(50),
|
|
"CHARGEPOINT_ADDRESS_CITY" NVARCHAR(50),
|
|
"CHARGEPOINT_ADDRESS_STREET" NVARCHAR(50),
|
|
"CHARGEPOINT_OPERATOR_ID" NVARCHAR(50),
|
|
"CHARGEPOINT_OPERATOR_NAME" NVARCHAR(100),
|
|
"EMOBILITY_PROVIDER_ID" NVARCHAR(50),
|
|
"EMOBILITY_PROVIDER_NAME" NVARCHAR(50),
|
|
"CHARGE_DATA_AUTHORIZATION_TYPE" NVARCHAR(50),
|
|
"CHARGE_DATA_CONSUMPTION_KWH" DECIMAL(17,3) CS_FIXED,
|
|
"CHARGE_DATA_BEGIN_UTC" NVARCHAR(50),
|
|
"CHARGE_DATA_END_UTC" NVARCHAR(50),
|
|
"CHARGE_DATA_TIME_ZONE" NVARCHAR(10),
|
|
"CHARGE_DATA_EKA_STATUS" NVARCHAR(10),
|
|
"SUBSCRIPTION_ID" NVARCHAR(50),
|
|
"PRICING_ITEMS_1_TYPE" NVARCHAR(10),
|
|
"PRICING_ITEMS_1_UNIT" NVARCHAR(10),
|
|
"PRICING_ITEMS_1_UNIT_PRICE" DECIMAL(17,3) CS_FIXED,
|
|
"PRICING_ITEMS_1_QUANTITY" DECIMAL(17,3) CS_FIXED,
|
|
"PRICING_ITEMS_1_NET_AMOUNT" DECIMAL(17,3) CS_FIXED,
|
|
"PRICING_ITEMS_1_CURRENCY" NVARCHAR(50),
|
|
"PRICING_ITEMS_2_TYPE" NVARCHAR(10),
|
|
"PRICING_ITEMS_2_UNIT" NVARCHAR(10),
|
|
"PRICING_ITEMS_2_UNIT_PRICE" DECIMAL(17,3) CS_FIXED,
|
|
"PRICING_ITEMS_2_QUANTITY" DECIMAL(17,3) CS_FIXED,
|
|
"PRICING_ITEMS_2_NET_AMOUNT" DECIMAL(17,3) CS_FIXED,
|
|
"PRICING_ITEMS_2_CURRENCY" NVARCHAR(50),
|
|
"PRICING_ITEMS_3_TYPE" NVARCHAR(10),
|
|
"PRICING_ITEMS_3_UNIT" NVARCHAR(10),
|
|
"PRICING_ITEMS_3_UNIT_PRICE" DECIMAL(17,3) CS_FIXED,
|
|
"PRICING_ITEMS_3_QUANTITY" DECIMAL(17,3) CS_FIXED,
|
|
"PRICING_ITEMS_3_NET_AMOUNT" DECIMAL(17,3) CS_FIXED,
|
|
"PRICING_ITEMS_3_CURRENCY" NVARCHAR(50),
|
|
"PRICING_ITEMS_4_TYPE" NVARCHAR(10),
|
|
"PRICING_ITEMS_4_UNIT" NVARCHAR(10),
|
|
"PRICING_ITEMS_4_UNIT_PRICE" DECIMAL(17,3) CS_FIXED,
|
|
"PRICING_ITEMS_4_QUANTITY" DECIMAL(17,3) CS_FIXED,
|
|
"PRICING_ITEMS_4_NET_AMOUNT" DECIMAL(17,3) CS_FIXED,
|
|
"PRICING_ITEMS_4_CURRENCY" NVARCHAR(50),
|
|
"JSONRAW" NCLOB MEMORY THRESHOLD 1000,
|
|
"CREATE_TS" LONGDATE CS_LONGDATE DEFAULT CURRENT_TIMESTAMP NOT NULL ,
|
|
"UPDATE_TS" LONGDATE CS_LONGDATE NOT NULL ,
|
|
PRIMARY KEY ("BDR_ID")) UNLOAD PRIORITY 5 AUTO MERGE;
|
|
|
|
/*
|
|
-- Changes v1.1
|
|
ALTER TABLE "EOPERATE"."CHARGEDATA" ADD (
|
|
"SUBSCRIPTION_ID" NVARCHAR(50)
|
|
);
|
|
*/
|
|
|
|
|
|
ALTER TABLE "EOPERATE"."CHARGEDATA" ADD (
|
|
"CREATE_USER" NVARCHAR(50) DEFAULT CURRENT_USER NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE "EOPERATE"."CHARGEDATA" ADD (
|
|
"UPDATE_USER" NVARCHAR(50) DEFAULT CURRENT_USER NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "EOPERATE"."CHARGEDATA" ADD (
|
|
"BUSINESSVALIDATIONRESULT" NVARCHAR(100),
|
|
"TECHVALIDATIONRESULT" NVARCHAR(100)
|
|
);
|
|
|
|
ALTER TABLE "EOPERATE"."CHARGEDATA" ADD (
|
|
"NOTBILLABLEREASON" NVARCHAR(200)
|
|
);
|
|
|
|
ALTER TABLE "EOPERATE"."CHARGEDATA" ADD (
|
|
"DURATION" DECIMAL AS IFNULL(CEIL((SECONDS_BETWEEN(TO_TIMESTAMP("CHARGE_DATA_BEGIN_UTC"),TO_TIMESTAMP("CHARGE_DATA_END_UTC")) / 60 )),0)
|
|
);
|
|
|
|
ALTER TABLE "EOPERATE"."CHARGEDATA" ALTER (
|
|
"CHARGE_DATA_CONSUMPTION_KWH" DECIMAL(21,6) CS_FIXED,
|
|
"PRICING_ITEMS_1_UNIT_PRICE" DECIMAL(21,6) CS_FIXED,
|
|
"PRICING_ITEMS_1_QUANTITY" DECIMAL(21,6) CS_FIXED,
|
|
"PRICING_ITEMS_1_NET_AMOUNT" DECIMAL(21,6) CS_FIXED,
|
|
"PRICING_ITEMS_2_UNIT_PRICE" DECIMAL(21,6) CS_FIXED,
|
|
"PRICING_ITEMS_2_QUANTITY" DECIMAL(21,6) CS_FIXED,
|
|
"PRICING_ITEMS_2_NET_AMOUNT" DECIMAL(21,6) CS_FIXED,
|
|
"PRICING_ITEMS_3_UNIT_PRICE" DECIMAL(21,6) CS_FIXED,
|
|
"PRICING_ITEMS_3_QUANTITY" DECIMAL(21,6) CS_FIXED,
|
|
"PRICING_ITEMS_3_NET_AMOUNT" DECIMAL(21,6) CS_FIXED,
|
|
"PRICING_ITEMS_4_UNIT_PRICE" DECIMAL(21,6) CS_FIXED,
|
|
"PRICING_ITEMS_4_QUANTITY" DECIMAL(21,6) CS_FIXED,
|
|
"PRICING_ITEMS_4_NET_AMOUNT" DECIMAL(21,6) CS_FIXED
|
|
); |