· 6 years ago · Jul 24, 2019, 12:38 PM
1-- META config table
2-- 5 records, 1 for each query
3DROP TABLE DQT_CFG_TBL_META;
4CREATE TABLE DQT_CFG_TBL_META
5(DQT_CFG_TBL_META_ID NUMERIC(10,0)
6,OPERATION VARCHAR(500)
7,CATEGORY_TYPE VARCHAR(500)
8,MAIN_SCRIPT CLOB
9,TEXT_CREATE_TBL CLOB
10,TEXT_CREATE_VIEW CLOB
11,TEXT_SQL_WHERE CLOB
12,TEXT_SQL_PREDICATES CLOB
13,TEXT_SQL_FETCHSIZE CLOB
14, SOURCE_SYSTEM VARCHAR(200)
15,PRE_SCRIPT CLOB
16,POST_SCRIPT CLOB
17,PROCESS_AREA VARCHAR(4000)
18,F_TBL_META_ACTIVE NUMERIC(1,0)
19,DQT_CFG_TBL_META_INFO CLOB
20,LAST_UPDATED TIMESTAMP
21) ;
22
23-- Runtime config table
24-- 5 records, 1->5, 1, 1->#numqueries, null -> filled in by script
25DROP TABLE DQT_CFG_JOBS_RUNTIME;
26CREATE TABLE DQT_CFG_JOBS_RUNTIME
27( DQT_CFG_JOBS_RUNTIME_ID NUMERIC(10,0)
28, DQT_CFG_ACTIONS_ID NUMERIC(10,0)
29, DQT_CFG_TBL_META_ID NUMERIC(10,0)
30, ACTION_TYPE VARCHAR(4000)
31, CALL_COMMAND CLOB
32, RUN_PARAMETERS CLOB
33, RUN_STATUS CLOB
34, RUN_OUTPUT CLOB
35, F_JOBS_RUNTIME_ACTIVE NUMERIC(1,0)
36, COMPLETED NUMERIC(1,0)
37);
38
39-- 1 record: 1, ingest_salto, null, 1
40DROP TABLE DQT_CFG_ACTIONS;
41CREATE TABLE DQT_CFG_ACTIONS
42(DQT_CFG_ACTIONS_ID NUMERIC(10,0)
43, ACTION_NAME VARCHAR(4000)
44, ACTION_CALL VARCHAR(4000)
45, F_ACTIONS_ACTIVE NUMERIC(1,0)
46);
47
48DROP TABLE DQT_CFG_DSN_SETUP;
49CREATE TABLE DQT_CFG_DSN_SETUP
50(DQT_CFG_DSN_SETUP_ID NUMERIC(10,0)
51,SOURCE_SYSTEM VARCHAR(4000)
52,CONNECTION_STRING VARCHAR(4000)
53,ENVIRONMENT VARCHAR(4000)
54,DQT_CFG_DSN_SETUP_INFO CLOB
55,F_DSN_SETUP_ACTIVE NUMERIC(1,0)
56);
57
58DROP TABLE DQT_CFG_TBL_META_TARGET_OBJECT;
59CREATE TABLE DQT_CFG_TBL_META_TARGET_OBJECT
60( DQT_CFG_TBL_META_ID NUMERIC(10,0)
61, DQT_CFG_TARGET_OBJECT_ID NUMERIC(10,0)
62, F_TBL_META_TGT_OBJ_ACTIVE NUMERIC(1,0)
63);
64
65DROP TABLE DQT_CFG_TARGET_OBJECT;
66CREATE TABLE DQT_CFG_TARGET_OBJECT
67(DQT_CFG_TARGET_OBJECT_ID NUMERIC(10,0)
68, DATABASE_NAME VARCHAR(200)
69, SCHEMA_NAME VARCHAR(200)
70, OBJECT_NAME VARCHAR(2000)
71, SOURCE_SYSTEM VARCHAR(200)
72, PRE_SCRIPT CLOB
73, POST_SCRIPT CLOB
74, DQT_CFG_TARGET_OBJECT_INFO CLOB
75, F_TARGET_OBJECT_ACT NUMERIC(1,0)
76);
77
78
79DROP TABLE DQT_CFG_SEQUENCE;
80CREATE TABLE DQT_CFG_SEQUENCE
81( DQT_CFG_SEQUENCE_ID NUMERIC(10,0)
82 ,DQT_CFG_JOBS_RUNTIME_ID NUMERIC(10,0)
83 ,DQT_CFG_SEQUENCE_NO NUMERIC(10,0)
84 ,DQT_CFG_NEXT_SEQUENCE_NO NUMERIC(10,0)
85 ,M3_DQTOOL_RUN_ID VARCHAR(4)
86 ,DQT_CFG_SEQUENCE_INFO CLOB
87 ,F_SEQUENCE_ACTIVE NUMERIC(1,0));
88
89-- ADD QUERIES TO META TABLE
90
91-- S_ASSET
92INSERT INTO DQT_CFG_TBL_META
93(DQT_CFG_TBL_META_id,operation,CATEGORY_type ,MAIN_SCRIPT ,text_create_tbl ,text_create_view ,text_sql_where ,text_sql_predicates ,text_sql_fetchsize,source_system,pre_script,post_script,process_area ,F_TBL_META_ACTIVE ,DQT_CFG_TBL_META_info ,last_updated)
94VALUES( 1
95 ,'ingestion'
96 ,'m3_salto_input'
97 ,'select /*+ FULL(s) parallel(s, 2) */ s.row_id as ROW_ID , s.prod_id as PROD_ID , s.asset_num as ASSET_NUM , s.integration_id as INTEGRATION_ID , s.serial_num as SERIAL_NUM , s.install_dt as INSTALL_DT , s.end_dt as END_DT , s.x_installation_id as X_INSTALLATION_ID , s.status_cd as STATUS_CD , s.bill_profile_id as BILL_PROFILE_ID , s.prom_integ_id as PROM_INTEG_ID , s.root_asset_id as ROOT_ASSET_ID , s.par_asset_id as PAR_ASSET_ID , s.owner_accnt_id as OWNER_ACCNT_ID , s.assembly_port_id as ASSEMBLY_PORT_ID from Siebel.S_ASSET s'
98 ,'CREATE TABLE `slt_s_asset`'
99 ,''
100 ,''
101 ,' ["row_id < |1-4LDKU5A|","row_id >= |1-4LDKU5A| and row_id < |1-59ZP9AER|","row_id >= |1-59ZP9AER| and row_id < |1-5HA308SJ|","row_id >= |1-5HA308SJ| and row_id < |1-8G2Z-2466|","row_id >= |1-8G2Z-2466| and row_id < |1-C8HR-1143|","row_id >= |1-C8HR-1143| and row_id < |1-JFQQOSJ|","row_id >= |1-JFQQOSJ| and row_id < |1-PZE4CZJ|","row_id >= |1-PZE4CZJ|"]'
102 ,''
103 ,'SALTO'
104 , ''
105 ,''
106 ,'PROD_COPY'
107 ,1
108 ,''
109 ,'');
110
111UPDATE DQT_CFG_TBL_META
112SET text_sql_predicates ='"row_id < |1-4LDKU5A|","row_id >= |1-4LDKU5A| and row_id < |1-59ZP9AER|","row_id >= |1-59ZP9AER| and row_id < |1-5HA308SJ|","row_id >= |1-5HA308SJ| and row_id < |1-8G2Z-2466|","row_id >= |1-8G2Z-2466| and row_id < |1-C8HR-1143|","row_id >= |1-C8HR-1143| and row_id < |1-JFQQOSJ|","row_id >= |1-JFQQOSJ| and row_id < |1-PZE4CZJ|","row_id >= |1-PZE4CZJ|"'
113WHERE dqt_cfg_tbl_meta_id = 1;
114
115
116-- S_ASSET_XA
117INSERT INTO DQT_CFG_TBL_META
118(DQT_CFG_TBL_META_id,operation,CATEGORY_type ,MAIN_SCRIPT ,text_create_tbl ,text_create_view ,text_sql_where ,text_sql_predicates ,text_sql_fetchsize,source_system,pre_script,post_script,process_area ,F_TBL_META_ACTIVE ,DQT_CFG_TBL_META_info ,last_updated)
119VALUES( 2
120 ,'ingestion'
121 ,'m3_salto_input'
122 ,'SELECT /*+ parallel(4) */ ASSET_ID as ROW_ID, case when lower(a.attr_name) = "access type" then a.char_val else NULL end as ACCESS_TYPE, case when lower(a.attr_name) = "acquisitiontype" then a.char_val else NULL end as ACQUISITION_TYPE, case when lower(a.attr_name) = "region" then a.char_val else NULL end as REGION, case when lower(a.attr_name) = "tvserviceid" then a.char_val else NULL end as TVSERVICEID, case when lower(a.attr_name) = "connectivityid" then a.char_val else NULL end as CONNECTIVITYID, case when lower(a.attr_name) = "numberoflicenses" then a.char_val else NULL end as NUMBEROFLICENSES, case when lower(a.attr_name) = "office365serviceid" then a.char_val else NULL end as OFFICE365SERVICEID, case when lower(a.attr_name) = "contract number" then a.char_val else NULL end as CONTRACT_NUMBER, case when lower(a.attr_name) = "fiaserviceid" then a.char_val else NULL end as FIASERVICEID, case when lower(a.attr_name) = "decodersequence" then a.char_val else NULL end as DECODERSEQUENCE, case when lower(a.attr_name) = "joinitialdiscount" then a.char_val else NULL end as JOINTINITIALDISCOUNT, case when lower(a.attr_name) = "serial number" then a.char_val else NULL end as SERIALNUMBER, case when lower(a.attr_name) = "phonenumber" then a.char_val else NULL end as PHONENUMBER, case when lower(a.attr_name) = "secret number" then a.char_val else NULL end as SECRETNUMBER, case when lower(a.attr_name) = "cugid" then a.char_val else NULL end as CUGID, case when lower(a.attr_name) = "sim card number" then a.char_val else NULL end as SIMCARDNUMBER, case WHEN LOWER(a.attr_name) = "ipaddress" THEN a.char_val ELSE NULL end AS IPADDRESS FROM SIEBEL.S_ASSET_XA a WHERE lower(a.attr_name) in ("access type", "acquisitiontype", "region", "tvserviceid", "connectivityid", "numberoflicenses", "office365serviceid", "contract number", "fiaserviceid", "decodersequence", "joinitialdiscount", "serial number", "phonenumber", "secret number", "cugid", "sim card number", "ipaddress") and a.asset_id is not null'
123 ,'CREATE TABLE IF NOT EXISTS `SLT_S_ASSET_XA` (`ROW_ID` STRING, `ACCESS_TYPE` STRING, `ACQUISITION_TYPE` STRING, `REGION` STRING, `TVSERVICEID` STRING, `CONNECTIVITYID` STRING, `NUMBEROFLICENSES` STRING, `OFFICE365SERVICEID` STRING, `CONTRACT_NUMBER` STRING, `FIASERVICEID` STRING, `DECODERSEQUENCE` STRING, `JOINTINITIALDISCOUNT` STRING, `SERIALNUMBER` STRING, `PHONENUMBER` STRING, `SECRETNUMBER` STRING, `CUGID` STRING, `SIMCARDNUMBER` STRING, `IPADDRESS` STRING)'
124 ,''
125 ,''
126 ,''
127 ,''
128 ,'SALTO'
129 ,''
130 ,''
131 ,'PROD_COPY'
132 ,1
133 ,''
134 ,'');
135
136-- S_ASSET_OM
137INSERT INTO DQT_CFG_TBL_META
138(DQT_CFG_TBL_META_id,operation,CATEGORY_type ,MAIN_SCRIPT ,text_create_tbl ,text_create_view ,text_sql_where ,text_sql_predicates ,text_sql_fetchsize,source_system,pre_script,post_script,process_area ,F_TBL_META_ACTIVE ,DQT_CFG_TBL_META_info ,last_updated)
139VALUES( 3
140 ,'ingestion'
141 ,'m3_salto_input'
142 ,'select OM.PAR_ROW_ID as ROW_ID, OM.TO_ADDR_ID as ADDR_ID from SIEBEL.S_ASSET_OM OM'
143 ,'CREATE TABLE IF NOT EXISTS `SLT_S_ASSET_OM` (`ROW_ID` STRING, `ADDR_ID` STRING)'
144 ,''
145 ,''
146 ,''
147 ,''
148 ,'SALTO'
149 ,''
150 ,''
151 ,'PROD_COPY'
152 ,1
153 ,''
154 ,'');
155
156-- S_ADDR_PER
157INSERT INTO DQT_CFG_TBL_META
158(DQT_CFG_TBL_META_id,operation,CATEGORY_type ,MAIN_SCRIPT ,text_create_tbl ,text_create_view ,text_sql_where ,text_sql_predicates ,text_sql_fetchsize,source_system,pre_script,post_script,process_area ,F_TBL_META_ACTIVE ,DQT_CFG_TBL_META_info ,last_updated)
159VALUES( 4
160 ,'ingestion'
161 ,'m3_salto_input'
162 ,'select ADDR.ADDR_NAME as ADDR_NAME, ADDR.ROW_ID as ADDR_ID, ADDR.x_lam_key_main as X_LAM_KEY_MAIN, ADDR.x_lam_key_sub as X_LAM_KEY_SUB from Siebel.s_ADDR_PER ADDR'
163 ,'CREATE TABLE IF NOT EXISTS `SLT_LKP_ADDR_DETS` (`ADDR_NAME` STRING, `ADDR_ID` STRING, `X_LAM_KEY_MAIN` STRING, `X_LAM_KEY_SUB` STRING)'
164 ,''
165 ,''
166 ,''
167 ,''
168 ,'SALTO'
169 ,''
170 ,''
171 ,'PROD_COPY'
172 ,1
173 ,''
174 ,'');
175
176-- S_INV_PROF
177INSERT INTO DQT_CFG_TBL_META
178(DQT_CFG_TBL_META_id,operation,CATEGORY_type ,MAIN_SCRIPT ,text_create_tbl ,text_create_view ,text_sql_where ,text_sql_predicates ,text_sql_fetchsize,source_system,pre_script,post_script,process_area ,F_TBL_META_ACTIVE ,DQT_CFG_TBL_META_info ,last_updated)
179VALUES( 5
180 ,'ingestion'
181 ,'m3_salto_input'
182 ,'SELECT row_id as ROOT_BILL_PROFILE_ID , name AS SLT_BILLING_ACCOUNT , accnt_id as SLT_ACCNT_ID , x_pa_number as SLT_CUST_PAYMENT_AGREEMENT , payment_type_cd as SLT_PAYMENT_TYPE_CD , addr_id as SLT_BILLING_ADDR_ID FROM SIEBEL.S_INV_PROF'
183 ,'CREATE TABLE IF NOT EXISTS `SLT_S_INV_PROF` (`ROOT_BILL_PROFILE_ID` STRING, `SLT_BILLING_ACCOUNT` STRING, `SLT_ACCNT_ID` STRING, `SLT_CUST_PAYMENT_AGREEMENT` DECIMAL(10,0), `SLT_PAYMENT_TYPE_CD` STRING, `SLT_BILLING_ADDR_ID` STRING)'
184 ,''
185 ,''
186 ,''
187 ,''
188 ,'SALTO'
189 ,''
190 ,''
191 ,'PROD_COPY'
192 ,1
193 ,''
194 ,'');
195
196INSERT INTO DQT_CFG_TBL_META
197(DQT_CFG_TBL_META_id,operation,CATEGORY_type ,MAIN_SCRIPT ,text_create_tbl ,text_create_view ,text_sql_where ,text_sql_predicates ,text_sql_fetchsize,source_system,pre_script,post_script,process_area ,F_TBL_META_ACTIVE ,DQT_CFG_TBL_META_info ,last_updated)
198VALUES( 6
199 ,'ingestion'
200 ,'m3_salto_input'
201 ,'select type as type, name, val from SIEBEL.S_LST_OF_VAL where type in(''FINCORP_CLIENT_LEGAL_FORM'', ''ACCOUNT_TYPE'', ''CONTACT_LANGUAGE'', ''BGC_LEGAL_LANGUAGE'', ''COUNTRY'', ''BGC_SUB_SEGMENT'') and lang_id = ''ENU'' '
202 ,''
203 ,''
204 ,''
205 ,''
206 ,''
207 ,'SALTO'
208 ,''
209 ,''
210 ,'PROD_COPY'
211 ,1
212 ,''
213 ,'');
214
215-- ADD JOBS TO JOBS_RUNTIME TABLE
216
217INSERT INTO DQT_CFG_JOBS_RUNTIME
218(DQT_CFG_JOBS_RUNTIME_id, dqt_cfg_actions_id, dqt_cfg_tbl_meta_id, action_type, call_command, run_parameters, run_status, run_output, F_JOBS_RUNTIME_ACTIVE, completed)
219VALUES( 1 -- ID
220 , 1 -- DQT_CFG_ACTIONS_ID
221 , 1 -- DQT_CFG_TBL_META_ID
222 , '' -- ACTION_TYPE
223 , '' -- CALL_COMMAND
224 , '' -- RUN_PARAMETERS
225 , '' -- RUN_STATUS
226 , '' -- RUN_OUTPUT
227 , 1 -- IS_ACTIVE
228 , '' -- COMPLETED
229 );
230
231INSERT INTO DQT_CFG_JOBS_RUNTIME
232(DQT_CFG_JOBS_RUNTIME_id, dqt_cfg_actions_id, dqt_cfg_tbl_meta_id, action_type, call_command, run_parameters, run_status, run_output, F_JOBS_RUNTIME_ACTIVE, completed)
233VALUES( 2 -- ID
234 , 1 -- DQT_CFG_ACTIONS_ID
235 , 2 -- DQT_CFG_TBL_META_ID
236 , '' -- ACTION_TYPE
237 , '' -- CALL_COMMAND
238 , '' -- RUN_PARAMETERS
239 , '' -- RUN_STATUS
240 , '' -- RUN_OUTPUT
241 , 1 -- IS_ACTIVE
242 , '' -- COMPLETED
243 );
244
245INSERT INTO DQT_CFG_JOBS_RUNTIME
246(DQT_CFG_JOBS_RUNTIME_id, dqt_cfg_actions_id, dqt_cfg_tbl_meta_id, action_type, call_command, run_parameters, run_status, run_output, F_JOBS_RUNTIME_ACTIVE, completed)
247VALUES( 3 -- ID
248 , 1 -- DQT_CFG_ACTIONS_ID
249 , 3 -- DQT_CFG_TBL_META_ID
250 , '' -- ACTION_TYPE
251 , '' -- CALL_COMMAND
252 , '' -- RUN_PARAMETERS
253 , '' -- RUN_STATUS
254 , '' -- RUN_OUTPUT
255 , 1 -- IS_ACTIVE
256 , '' -- COMPLETED
257 );
258
259INSERT INTO DQT_CFG_JOBS_RUNTIME
260(DQT_CFG_JOBS_RUNTIME_id, dqt_cfg_actions_id, dqt_cfg_tbl_meta_id, action_type, call_command, run_parameters, run_status, run_output, F_JOBS_RUNTIME_ACTIVE, completed)
261VALUES( 4 -- ID
262 , 1 -- DQT_CFG_ACTIONS_ID
263 , 4 -- DQT_CFG_TBL_META_ID
264 , '' -- ACTION_TYPE
265 , '' -- CALL_COMMAND
266 , '' -- RUN_PARAMETERS
267 , '' -- RUN_STATUS
268 , '' -- RUN_OUTPUT
269 , 1 -- IS_ACTIVE
270 , '' -- COMPLETED
271 );
272
273INSERT INTO DQT_CFG_JOBS_RUNTIME
274(DQT_CFG_JOBS_RUNTIME_id, dqt_cfg_actions_id, dqt_cfg_tbl_meta_id, action_type, call_command, run_parameters, run_status, run_output, F_JOBS_RUNTIME_ACTIVE, completed)
275VALUES( 5 -- ID
276 , 1 -- DQT_CFG_ACTIONS_ID
277 , 5 -- DQT_CFG_TBL_META_ID
278 , '' -- ACTION_TYPE
279 , '' -- CALL_COMMAND
280 , '' -- RUN_PARAMETERS
281 , '' -- RUN_STATUS
282 , '' -- RUN_OUTPUT
283 , 1 -- IS_ACTIVE
284 , '' -- COMPLETED
285 );
286
287INSERT INTO DQT_CFG_JOBS_RUNTIME
288(DQT_CFG_JOBS_RUNTIME_id, dqt_cfg_actions_id, dqt_cfg_tbl_meta_id, action_type, call_command, run_parameters, run_status, run_output, F_JOBS_RUNTIME_ACTIVE, completed)
289VALUES( 6 -- ID
290 , 1 -- DQT_CFG_ACTIONS_ID
291 , 6 -- DQT_CFG_TBL_META_ID
292 , '' -- ACTION_TYPE
293 , '' -- CALL_COMMAND
294 , '' -- RUN_PARAMETERS
295 , '' -- RUN_STATUS
296 , '' -- RUN_OUTPUT
297 , 1 -- IS_ACTIVE
298 , '' -- COMPLETED
299 );
300
301-- Add action to actions table
302INSERT INTO DQT_CFG_ACTIONS
303(DQT_CFG_ACTIONS_id, action_name, action_call, F_ACTIONS_ACTIVE)
304VALUES(1
305 , 'ingest_salto'
306 , NULL
307 , 1
308);
309
310-- Add jdbc info to jdbc table
311INSERT INTO DQT_CFG_DSN_SETUP
312(DQT_CFG_DSN_SETUP_ID, SOURCE_SYSTEM, CONNECTION_STRING, ENVIRONMENT,DQT_CFG_DSN_SETUP_INFO, F_DSN_SETUP_ACTIVE)
313VALUES(1
314 ,'SALTO'
315 , 'jdbc:oracle:thin:ID994412/yi3ctg9WiNheyMs@ip-qora225.bc:1540:CQQ1SHE'
316 , 'PROD_COPY'
317 , ''
318 ,1);
319
320INSERT INTO DQT_CFG_DSN_SETUP
321(DQT_CFG_DSN_SETUP_ID, SOURCE_SYSTEM, CONNECTION_STRING, ENVIRONMENT,DQT_CFG_DSN_SETUP_INFO, F_DSN_SETUP_ACTIVE)
322VALUES(1
323 ,'SALTO'
324 , 'jdbc:oracle:thin:id994413/xAc4vEXGUtJa7qN@ip-uora164.bc:1540:CSS1SHE'
325 , 'UAT'
326 , ''
327 ,1);
328
329-- Add targets to target table
330INSERT INTO DQT_CFG_TARGET_OBJECT
331(DQT_CFG_TARGET_OBJECT_ID , DATABASE_NAME, SCHEMA_NAME, OBJECT_NAME, SOURCE_SYSTEM,PRE_SCRIPT,POST_SCRIPT, DQT_CFG_TARGET_OBJECT_INFO, F_TARGET_OBJECT_ACT)
332VALUES(1
333 , ''
334 , 'DEFAULT'
335 , 'SLT_S_ASSET'
336 ,'SALTO'
337 ,''
338 ,''
339 ,''
340 ,1);
341
342INSERT INTO DQT_CFG_TARGET_OBJECT
343(DQT_CFG_TARGET_OBJECT_ID , DATABASE_NAME, SCHEMA_NAME, OBJECT_NAME, SOURCE_SYSTEM,PRE_SCRIPT,POST_SCRIPT, DQT_CFG_TARGET_OBJECT_INFO, F_TARGET_OBJECT_ACT)
344VALUES(2
345 , ''
346 , 'DEFAULT'
347 , 'SLT_S_ASSET_XA'
348 ,'SALTO'
349 ,''
350 ,''
351 ,''
352 ,1);
353
354INSERT INTO DQT_CFG_TARGET_OBJECT
355(DQT_CFG_TARGET_OBJECT_ID , DATABASE_NAME, SCHEMA_NAME, OBJECT_NAME, SOURCE_SYSTEM,PRE_SCRIPT,POST_SCRIPT, DQT_CFG_TARGET_OBJECT_INFO, F_TARGET_OBJECT_ACT)
356VALUES(3
357 , ''
358 , 'DEFAULT'
359 , 'SLT_S_ASSET_OM'
360 ,'SALTO'
361 ,''
362 ,''
363 ,''
364 ,1);
365
366INSERT INTO DQT_CFG_TARGET_OBJECT
367(DQT_CFG_TARGET_OBJECT_ID , DATABASE_NAME, SCHEMA_NAME, OBJECT_NAME, SOURCE_SYSTEM,PRE_SCRIPT,POST_SCRIPT, DQT_CFG_TARGET_OBJECT_INFO, F_TARGET_OBJECT_ACT)
368VALUES(4
369 , ''
370 , 'DEFAULT'
371 , 'SLT_S_INV_PROF'
372 ,'SALTO'
373 ,''
374 ,''
375 ,''
376 ,1);
377
378INSERT INTO DQT_CFG_TARGET_OBJECT
379(DQT_CFG_TARGET_OBJECT_ID , DATABASE_NAME, SCHEMA_NAME, OBJECT_NAME, SOURCE_SYSTEM,PRE_SCRIPT,POST_SCRIPT, DQT_CFG_TARGET_OBJECT_INFO, F_TARGET_OBJECT_ACT)
380VALUES(5
381 , ''
382 , 'DEFAULT'
383 , 'SLT_ADDR_DETS'
384 ,'SALTO'
385 ,''
386 ,''
387 ,''
388 ,1);
389
390INSERT INTO DQT_CFG_TARGET_OBJECT
391(DQT_CFG_TARGET_OBJECT_ID , DATABASE_NAME, SCHEMA_NAME, OBJECT_NAME, SOURCE_SYSTEM,PRE_SCRIPT,POST_SCRIPT, DQT_CFG_TARGET_OBJECT_INFO, F_TARGET_OBJECT_ACT)
392VALUES(6
393 , ''
394 , 'DEFAULT'
395 , 'SLT_S_LST_OF_VAL'
396 ,'SALTO'
397 ,''
398 ,''
399 ,''
400 ,1);
401
402-- Add relations between objects and meta
403INSERT INTO DQT_CFG_TBL_META_TARGET_OBJECT
404( DQT_CFG_TBL_META_ID, DQT_CFG_TARGET_OBJECT_ID, F_TBL_META_TGT_OBJ_ACTIVE)
405VALUES(1,1,1);
406
407INSERT INTO DQT_CFG_TBL_META_TARGET_OBJECT
408( DQT_CFG_TBL_META_ID, DQT_CFG_TARGET_OBJECT_ID, F_TBL_META_TGT_OBJ_ACTIVE)
409VALUES(2,2,1);
410
411INSERT INTO DQT_CFG_TBL_META_TARGET_OBJECT
412( DQT_CFG_TBL_META_ID, DQT_CFG_TARGET_OBJECT_ID, F_TBL_META_TGT_OBJ_ACTIVE)
413VALUES(3,3,1);
414
415INSERT INTO DQT_CFG_TBL_META_TARGET_OBJECT
416( DQT_CFG_TBL_META_ID, DQT_CFG_TARGET_OBJECT_ID, F_TBL_META_TGT_OBJ_ACTIVE)
417VALUES(4,5,1);
418
419INSERT INTO DQT_CFG_TBL_META_TARGET_OBJECT
420( DQT_CFG_TBL_META_ID, DQT_CFG_TARGET_OBJECT_ID, F_TBL_META_TGT_OBJ_ACTIVE)
421VALUES(5,4,1);
422
423INSERT INTO DQT_CFG_TBL_META_TARGET_OBJECT
424( DQT_CFG_TBL_META_ID, DQT_CFG_TARGET_OBJECT_ID, F_TBL_META_TGT_OBJ_ACTIVE)
425VALUES(6,6,1);
426
427-- Enter values in DQT_CFG_SEQUENCE
428INSERT INTO DQT_CFG_SEQUENCE
429( DQT_CFG_SEQUENCE_ID ,DQT_CFG_JOBS_RUNTIME_ID, DQT_CFG_SEQUENCE_NO, DQT_CFG_NEXT_SEQUENCE_NO,M3_DQTOOL_RUN_ID, DQT_CFG_SEQUENCE_INFO ,F_SEQUENCE_ACTIVE)
430VALUES(1,6,1,2,'ALLC','',1);
431
432INSERT INTO DQT_CFG_SEQUENCE
433( DQT_CFG_SEQUENCE_ID ,DQT_CFG_JOBS_RUNTIME_ID, DQT_CFG_SEQUENCE_NO, DQT_CFG_NEXT_SEQUENCE_NO,M3_DQTOOL_RUN_ID, DQT_CFG_SEQUENCE_INFO ,F_SEQUENCE_ACTIVE)
434VALUES(2,4,2,3,'ALLC','',1);
435
436INSERT INTO DQT_CFG_SEQUENCE
437( DQT_CFG_SEQUENCE_ID ,DQT_CFG_JOBS_RUNTIME_ID, DQT_CFG_SEQUENCE_NO, DQT_CFG_NEXT_SEQUENCE_NO,M3_DQTOOL_RUN_ID, DQT_CFG_SEQUENCE_INFO ,F_SEQUENCE_ACTIVE)
438VALUES(3,2,3,NULL,'ALLC','',1);
439
440INSERT INTO DQT_CFG_SEQUENCE
441( DQT_CFG_SEQUENCE_ID ,DQT_CFG_JOBS_RUNTIME_ID, DQT_CFG_SEQUENCE_NO, DQT_CFG_NEXT_SEQUENCE_NO,M3_DQTOOL_RUN_ID, DQT_CFG_SEQUENCE_INFO ,F_SEQUENCE_ACTIVE)
442VALUES(4,1,1,3,'ALLC','',1);
443
444-- Active flag on C not taken into account? -> I think we can add to query here, since it's merely mapping?
445DROP VIEW V_DQT_CFG_SRC_TGT_META;
446CREATE VIEW V_DQT_CFG_SRC_TGT_META AS
447SELECT META.DQT_CFG_TBL_META_ID
448 , META.OPERATION
449 , META.CATEGORY_TYPE
450 , META.MAIN_SCRIPT
451 , META.TEXT_CREATE_TBL
452 , META.TEXT_CREATE_VIEW
453 , META.TEXT_SQL_WHERE
454 , META.TEXT_SQL_PREDICATES
455 , META.TEXT_SQL_FETCHSIZE
456 , META.SOURCE_SYSTEM
457 , META.PRE_SCRIPT AS META_PRE_SCRIPT
458 , META.POST_SCRIPT AS META_POST_SCRIPT
459 , META.PROCESS_AREA
460 , META.DQT_CFG_TBL_META_INFO
461 , META.LAST_UPDATED
462 , DSN.CONNECTION_STRING AS META_CONNECTION_STRING
463 , DSN.DQT_CFG_DSN_SETUP_INFO AS META_DSN_SETUP_INFO
464 , DSN2.CONNECTION_STRING AS TGT_CONNECTION_STRING
465 , DSN2.DQT_CFG_DSN_SETUP_INFO AS TGT_DSN_SETUP_INFO
466 , DSN.ENVIRONMENT AS META_ENVIRONMENT
467 , DSN2.ENVIRONMENT AS TGT_ENVIRONMENT
468 , TGTO.DQT_CFG_TARGET_OBJECT_ID
469 , TGTO.DATABASE_NAME
470 , TGTO.SCHEMA_NAME
471 , TGTO.OBJECT_NAME
472 , TGTO.PRE_SCRIPT AS TGT_PRE_SCRIPT
473 , TGTO.POST_SCRIPT AS TGT_POST_SCRIPT
474 , DQT_CFG_TARGET_OBJECT_INFO
475 , META.F_TBL_META_ACTIVE
476 , DSN.F_DSN_SETUP_ACTIVE
477 , TGTO.F_TARGET_OBJECT_ACT
478FROM DQT_CFG_TBL_META META
479JOIN DQT_CFG_DSN_SETUP DSN ON META.SOURCE_SYSTEM = DSN.SOURCE_SYSTEM
480JOIN DQT_CFG_TBL_META_TARGET_OBJECT MTO ON META.DQT_CFG_TBL_META_ID = MTO.DQT_CFG_TBL_META_ID
481JOIN DQT_CFG_TARGET_OBJECT TGTO ON MTO.DQT_CFG_TARGET_OBJECT_ID = TGTO.DQT_CFG_TARGET_OBJECT_ID
482JOIN DQT_CFG_DSN_SETUP DSN2 ON TGTO.SOURCE_SYSTEM = DSN2.SOURCE_SYSTEM
483WHERE MTO.F_TBL_META_TGT_OBJ_ACTIVE = 1;
484
485CREATE OR REPLACE PROCEDURE DQT_CFG_UPDATE_OBJ_RUNTIME
486(INPUT_ID NUMERIC(10,0)
487, INPUT_ACTION_TYPE NUMERIC(10,0)
488, INPUT_CALL_COMMAND CLOB
489, INPUT_RUN_PARAMETERS CLOB
490, INPUT_RUN_STATUS CLOB
491, INPUT_RUN_OUTPUT CLOB
492, INPUT_COMPLETED NUMERIC(1,0))
493IS
494BEGIN
495 UPDATE DQT_CFG_OBJECTS_RUNTIME SET ACTION_TYPE = INPUT_ACTION_TYPE
496 , CALL_COMMAND = INPUT_CALL_COMMAND
497 , RUN_PARAMETERS = INPUT_RUN_PARAMETERS
498 , RUN_STATUS = INPUT_RUN_STATUS
499 , RUN_OUTPUT = INPUT_RUN_OUTPUT
500 , COMPLETED = INPUT_RUN_COMPLETED
501 WHERE DQT_CFG_JOBS_RUNTIME_ID = INPUT_ID
502END;
503
504
505--SELECT * FROM V_DQT_CFG_SRC_TGT_META
506
507
508
509
510--SELECT * FROM DQT_CFG_JOBS_RUNTIME;
511--SELECT * FROM DQT_CFG_TBL_META;
512--SELECT * FROM DQT_CFG_ACTIONS;