Scribd
Upload
6 views

03 DML ProcessData STA Log

3

Uploaded by

Raja Rangasamy
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
6 views

03 DML ProcessData STA Log

3

Uploaded by

Raja Rangasamy
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

SQL> --------------------------------------------------------

SQL> -- File created - 2020-03-18


SQL> -- Created by: INDRA
SQL> --------------------------------------------------------
SQL>
SQL> SET TIMI ON
SQL> SET ECHO ON
SQL> SET FEEDB ON
SQL> DEFINE var1 = "TDMI_STA.";
SQL> SPOOL 04.log
SQL>
SQL> BEGIN
2
3 MERGE INTO TDMI_ODS.DT_P127_12701_CONV_DB CONV
4 USING
5 (
6 SELECT DISTINCT
7 NULL AS CONV_PERIOD, -- PERIOD
8 NULL AS COUNTRY, --
PKG_UTILS.GET_LOV_EDPR_CONV_COUNTRY(EXP_REQ_ID, vDate), -- COUNTRY
9 NULL AS FACILITY_NAME, --
PKG_UTILS.GET_LOV_EDPR_CONV_FACILITY(EXP_REQ_ID, vDate), -- FACILITY_NAME
10 NULL AS ADAS_NAME, --
PKG_UTILS.GET_LOV_EDPR_CONV_ADASNAME(EXP_REQ_ID, vDate), -- ADAS_NAME
11 NULL AS VAR_NAME, --
PKG_UTILS.GET_LOV_EDPR_CONV_VARNAME(EXP_REQ_ID, vDate), -- VAR_NAME
12 EXP_REQ_ID AS EXP_REQ_ID,
13 NULL AS TZ_NAME, --
PKG_UTILS.GET_LOV_EDPR_CONV_TZ_NAME(EXP_REQ_ID, vDate), -- TZ_NAME
14 TIMESTAMP_UTC AS TIMESTAMP_UTC,
15 TRUNC(TIMESTAMP_UTC) AS DATE_UTC, -- DATE_UTC
16 TO_NUMBER(TO_CHAR(TIMESTAMP_UTC,'HH24')) AS HOUR_UTC, --
HOUR_UTC
17 UNIT AS UNIT, --
PKG_UTILS.GET_LOV_EDPR_CONV_UNIT(EXP_REQ_ID, vDate),
18 ENERGY_VALUE AS ENERGY_VALUE, --
UTILS.CONVERT_TO_NUMBER(ENERGY_VALUE),
19 METER_LOAD_STATUS AS METER_LOAD_STATUS,
20 TA_NAME AS TA_NAME,
21 DS_NAME AS DS_NAME,
22 RATE_NAME AS RATE_NAME,
23 DATE_CREATED AS DATE_CREATED,
24 DATE_MODIFIED AS DATE_MODIFIED,
25 0 AS LAST_VERSION, -- LASTVERSION
26 381 AS CREATED_BY, -- created by
27 SYSTIMESTAMP AS CREATED_ON, -- created on
28 127 AS CREATED_PROCESS, -- created process
29 381 AS UPDATED_BY, -- updated by
30 SYSTIMESTAMP AS UPDATED_ON, -- updated on
31 127 AS UPDATED_PROCESS -- updated process)
32 FROM TDMI_STA.DT_STG_P127_12701_CONV_DB
33 WHERE ID_EXEC = 2124940
34 AND ID_EXEC_SOURCE = 17660901
35 AND stg_load_indicator = 1
36 AND ods_load_indicator = 0
37 AND MFK = 0
38 ) UP_INS
39 ON (UP_INS.EXP_REQ_ID = CONV.EXP_REQ_ID AND UP_INS.TIMESTAMP_UTC =
CONV.TIMESTAMP_UTC )
40 WHEN MATCHED THEN
41 UPDATE SET
42 "PERIOD" = UP_INS.CONV_PERIOD,
43 COUNTRY = UP_INS.COUNTRY,
44 FACILITY_NAME = UP_INS.FACILITY_NAME,
45 ADAS_NAME = UP_INS.ADAS_NAME,
46 VAR_NAME = UP_INS.VAR_NAME,
47 TZ_NAME = UP_INS.TZ_NAME,
48 DATE_UTC = UP_INS.DATE_UTC,
49 HOUR_UTC = UP_INS.HOUR_UTC,
50 UNIT = UP_INS.UNIT,
51 ENERGY_VALUE = UP_INS.ENERGY_VALUE,
52 METER_LOAD_STATUS = UP_INS.METER_LOAD_STATUS,
53 TA_NAME = UP_INS.TA_NAME,
54 DS_NAME = UP_INS.DS_NAME,
55 RATE_NAME = UP_INS.RATE_NAME,
56 DATE_MODIFIED = UP_INS.DATE_MODIFIED,
57 LAST_VERSION = UP_INS.LAST_VERSION,
58 UPDATED_BY = UP_INS.UPDATED_BY,
59 UPDATED_ON = UP_INS.UPDATED_ON,
60 UPDATED_PROCESS = UP_INS.UPDATED_PROCESS
61 WHEN NOT MATCHED THEN
62 INSERT
(ID,PERIOD,COUNTRY,FACILITY_NAME,ADAS_NAME,VAR_NAME,EXP_REQ_ID,TZ_NAME,TIMESTAMP_UT
C,DATE_UTC,HOUR_UTC,UNIT,ENERGY_VALUE,METER_LOAD_STATUS,TA_NAME,DS_NAME,RATE_NAME,D
ATE_CREATED,DATE_MODIFIED,LAST_VERSION,CREATED_BY,CREATED_ON,CREATED_PROCESS,UPDATE
D_BY,UPDATED_ON,UPDATED_PROCESS)
63 VALUES
(TDMI_ODS.SEQ_P127_12701_CONV_DB.NEXTVAL,UP_INS.CONV_PERIOD,UP_INS.COUNTRY,UP_INS.F
ACILITY_NAME,UP_INS.ADAS_NAME,UP_INS.VAR_NAME,UP_INS.EXP_REQ_ID,UP_INS.TZ_NAME,UP_I
NS.TIMESTAMP_UTC,UP_INS.DATE_UTC,UP_INS.HOUR_UTC,UP_INS.UNIT,UP_INS.ENERGY_VALUE,UP
_INS.METER_LOAD_STATUS,UP_INS.TA_NAME,UP_INS.DS_NAME,UP_INS.RATE_NAME,UP_INS.DATE_C
REATED,UP_INS.DATE_MODIFIED,UP_INS.LAST_VERSION,UP_INS.CREATED_BY,UP_INS.CREATED_ON
,UP_INS.CREATED_PROCESS,UP_INS.UPDATED_BY,UP_INS.UPDATED_ON,UP_INS.UPDATED_PROCESS)
;
64
65 COMMIT;
66
67 END;
68 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:22.407
SQL> spool off;

You might also like