· 6 years ago · Nov 09, 2019, 04:42 PM
1create or replace PACKAGE BODY pck_transform IS
2
3 e_transformation EXCEPTION;
4
5 -- *********************************************
6 -- * PUTS AN ERROR IN THE FACT TABLE OF ERRORS *
7 -- *********************************************
8 PROCEDURE error_log(p_screen_name t_tel_screen.screen_name%TYPE,
9 p_hora_deteccao DATE,
10 p_source_key t_tel_source.source_key%TYPE,
11 p_iteration_key t_tel_iteration.iteration_key%TYPE,
12 p_record_id t_tel_error.record_id%TYPE) IS
13 v_date_key t_tel_date.date_key%TYPE;
14 v_screen_key t_tel_screen.screen_key%TYPE;
15 BEGIN
16 -- obt¿m o id da dimens¿o ¿date¿ referente ao dia em que o erro foi detectado
17 BEGIN
18 SELECT date_key
19 INTO v_date_key
20 FROM t_tel_date
21 WHERE date_full=TO_CHAR(p_hora_deteccao,'DD-MM-YYYY');
22 EXCEPTION
23 WHEN NO_DATA_FOUND THEN
24 pck_log.write_log(' -- ERROR -- could not find date key from "t_tel_date" ['||sqlerrm||']');
25 RAISE e_transformation;
26 END;
27
28 BEGIN
29 SELECT screen_key
30 INTO v_screen_key
31 FROM t_tel_screen
32 WHERE UPPER(screen_name)=UPPER(p_screen_name);
33 EXCEPTION
34 WHEN NO_DATA_FOUND THEN
35 pck_log.write_log(' -- ERROR -- could not find screen key from "t_tel_screen" ['||sqlerrm||']');
36 RAISE e_transformation;
37 END;
38
39 INSERT INTO t_tel_error (date_key,screen_key,source_key,iteration_key, record_id) VALUES (v_date_key,v_screen_key,p_source_key,p_iteration_key, p_record_id);
40 EXCEPTION
41 WHEN OTHERS THEN
42 pck_log.write_log(' -- ERROR -- could not write quality problem to "t_tel_error" fact table ['||sqlerrm||']');
43 RAISE e_transformation;
44 END;
45
46
47
48 -- *******************************************
49 -- * DUPLICATES THE LAST SCHEDULED ITERATION *
50 -- *******************************************
51 PROCEDURE duplicate_last_iteration(p_start_date t_tel_iteration.iteration_start_date%TYPE) IS
52 v_last_iteration_key t_tel_iteration.iteration_key%TYPE;
53 v_new_iteration_key t_tel_iteration.iteration_key%TYPE;
54
55 CURSOR c_scheduled_screens(p_iteration_key t_tel_iteration.iteration_key%TYPE) IS
56 SELECT s.screen_key as screen_key,screen_name,screen_order, s.source_key
57 FROM t_tel_schedule s, t_tel_screen
58 WHERE iteration_key=p_iteration_key AND
59 s.screen_key = t_tel_screen.screen_key;
60 BEGIN
61 pck_log.write_log(' Creating new iteration by duplicating the previous one');
62
63 -- FIND THE LAST ITERATIONS'S KEY
64 BEGIN
65 SELECT MAX(iteration_key)
66 INTO v_last_iteration_key
67 FROM t_tel_iteration;
68 EXCEPTION
69 WHEN NO_DATA_FOUND THEN
70 pck_log.write_log(' -- ERROR -- could not find iteration key ['||sqlerrm||']');
71 RAISE e_transformation;
72 END;
73
74 INSERT INTO t_tel_iteration(iteration_start_date) VALUES (p_start_date) RETURNING iteration_key INTO v_new_iteration_key;
75 FOR rec IN c_scheduled_screens(v_last_iteration_key) LOOP
76 -- schedule screen
77 INSERT INTO t_tel_schedule(screen_key,iteration_key,source_key,screen_order)
78 VALUES (rec.screen_key,v_new_iteration_key,rec.source_key,rec.screen_order);
79 END LOOP;
80 pck_log.write_log(' Done!');
81 EXCEPTION
82 WHEN NO_DATA_FOUND THEN
83 pck_log.write_log(' -- ERROR -- previous iteration has no screens to reschedule');
84 RAISE e_transformation;
85 WHEN OTHERS THEN
86 pck_log.write_uncomplete_task_msg;
87 RAISE e_transformation;
88 END;
89
90 -- Completude do tamanho das caixas dos produtos
91 PROCEDURE screen_invalid_box_size (p_iteration_key t_tel_iteration.iteration_key%TYPE,
92 p_source_key t_tel_source.source_key%TYPE,
93 p_screen_order t_tel_schedule.screen_order%TYPE) IS
94 CURSOR products_with_problems IS
95 SELECT rowid
96 FROM t_data_products
97 WHERE rejected_by_screen='0'
98 AND UPPER(PACK_TYPE) = 'CAIXA'
99 AND ((HEIGHT = '0' OR WIDTH = '0' OR DEPTH = '0') OR (HEIGHT IS NULL OR WIDTH IS NULL OR DEPTH IS NULL));
100 i PLS_INTEGER:=0;
101 v_screen_name VARCHAR2(30):='screen_invalid_box_size';
102 BEGIN
103 pck_log.write_log(' Starting SCREEN ["'||UPPER(v_screen_name)||'"] with order #'||p_screen_order||'');
104 FOR rec IN products_with_problems LOOP
105 error_log(v_screen_name,SYSDATE,p_source_key,p_iteration_key,rec.rowid);
106 UPDATE t_data_products SET rejected_by_screen='1' WHERE rec.rowid = rowid;
107 i:=i+1;
108 END LOOP;
109 pck_log.write_log(' Data quality problems in '|| i || ' row(s).',' Done!');
110 EXCEPTION
111 WHEN NO_DATA_FOUND THEN
112 pck_log.write_log(' No data quality problems found.',' Done!');
113 WHEN OTHERS THEN
114 pck_log.write_uncomplete_task_msg;
115 RAISE e_transformation;
116 END;
117
118 PROCEDURE screen_null_category (p_iteration_key t_tel_iteration.iteration_key%TYPE,
119 p_source_key t_tel_source.source_key%TYPE,
120 p_screen_order t_tel_schedule.screen_order%TYPE) IS
121 -- EMPLOYEES ARE OK WHEN:
122 -- CATEGORY IS NOT NULL AND DEPARTMENT ARE NOT NULL
123 -- CATEGORY IS NULL IF DEPARMENT IS NULL
124 -- EMPLOYEES HAVE PROBLEMS WHEN:
125 -- DEPARTMENT IS NULL AND CATEGORY IS NOT NULL
126 -- CATEGORY IS NULL AND DEPARTMENT IS NOT NULL
127 CURSOR employees_with_problems IS
128 SELECT rowid
129 FROM t_data_employees E
130 WHERE rejected_by_screen='0'
131 AND E.DEPARTMENT IS NULL AND E.CATEGORY IS NOT NULL
132 OR E.CATEGORY IS NULL AND E.DEPARTMENT IS NOT NULL;
133 i PLS_INTEGER:=0;
134 v_screen_name VARCHAR2(30):='screen_null_category';
135 BEGIN
136 pck_log.write_log(' Starting SCREEN ["'||UPPER(v_screen_name)||'"] with order #'||p_screen_order||'');
137 FOR rec IN employees_with_problems LOOP
138 error_log(v_screen_name,SYSDATE,p_source_key,p_iteration_key,rec.rowid);
139 UPDATE t_data_employees SET rejected_by_screen='1' WHERE rec.rowid = rowid;
140 i:=i+1;
141 END LOOP;
142 pck_log.write_log(' Data quality problems in '|| i || ' row(s).',' Done!');
143 EXCEPTION
144 WHEN NO_DATA_FOUND THEN
145 pck_log.write_log(' No data quality problems found.',' Done!');
146 WHEN OTHERS THEN
147 pck_log.write_uncomplete_task_msg;
148 RAISE e_transformation;
149 END;
150
151 -- *************************************************************************************
152 -- * GOAL: Detect incorrect data in the size of products *
153 -- * QUALITY CRITERIUM: "Corre¿¿o" *
154 -- * PARAMETERS: *
155 -- * p_iteration_key: key of the iteration in which the screen will be run *
156 -- * p_source_key: key of the source system related to the screen's execution *
157 -- * p_screen_order: order number in which the screen is to be executed *
158 -- *************************************************************************************
159 PROCEDURE screen_product_dimensions (p_iteration_key t_tel_iteration.iteration_key%TYPE,
160 p_source_key t_tel_source.source_key%TYPE,
161 p_screen_order t_tel_schedule.screen_order%TYPE) IS
162 -- SEARCH FOR EXTRACTED PRODUCTS CONTAINING PROBLEMS
163 -- Reminder: Um cursor é um ponteiro, contem o endereço de memória do resultado da query
164 -- Neste cursor guardamos os ROW_ID's da tabela de produtos com problemas.
165 CURSOR products_with_problems IS
166 SELECT rowid
167 FROM t_data_products
168 WHERE rejected_by_screen='0'
169 AND (((width IS NULL OR height IS NULL OR depth IS NULL) AND UPPER(pack_type) IN (SELECT pack_type
170 FROM t_lookup_pack_dimensions
171 WHERE has_dimensions='1'))
172 OR ((width>=0 OR height>=0 OR depth>=0 AND UPPER(pack_type) IN (SELECT pack_type
173 FROM t_lookup_pack_dimensions
174 WHERE has_dimensions='0'))));
175 i PLS_INTEGER:=0;
176 v_screen_name VARCHAR2(30):='screen_product_dimensions';
177 BEGIN
178 pck_log.write_log(' Starting SCREEN ["'||UPPER(v_screen_name)||'"] with order #'||p_screen_order||'');
179 FOR rec IN products_with_problems LOOP
180 -- RECORDS THE ERROR IN THE TRANSFORMATION ERROR LOGGER BUT DOES * NOT REJECT THE LINE *
181 error_log(v_screen_name,SYSDATE,p_source_key,p_iteration_key,rec.rowid);
182 i:=i+1;
183 END LOOP;
184 pck_log.write_log(' Data quality problems in '|| i || ' row(s).',' Done!');
185 EXCEPTION
186 WHEN NO_DATA_FOUND THEN
187 pck_log.write_log(' No data quality problems found.',' Done!');
188 WHEN OTHERS THEN
189 pck_log.write_uncomplete_task_msg;
190 RAISE e_transformation;
191 END;
192
193
194 PROCEDURE screen_gender_discrimination (p_iteration_key t_tel_iteration.iteration_key%TYPE,
195 p_source_key t_tel_source.source_key%TYPE,
196 p_screen_order t_tel_schedule.screen_order%TYPE) IS
197
198 CURSOR people_with_problems IS
199 SELECT rowid
200 FROM T_DATA_CUSTOMERS c
201 WHERE SUBSTR(C.name, 1, INSTR(C.name,' ')-1) IN (SELECT lookup.NAME FROM T_LOOKUP_GENDERS lookup)
202 AND c.gender NOT LIKE (SELECT gender FROM T_LOOKUP_GENDERS WHERE NAME LIKE SUBSTR(C.name, 1, INSTR(C.name,' ')-1))
203 AND rejected_by_screen='0';
204
205 i PLS_INTEGER:=0;
206 v_screen_name VARCHAR2(30):='screen_gender_discrimination';
207 BEGIN
208 pck_log.write_log(' Starting SCREEN ["'||UPPER(v_screen_name)||'"] with order #'||p_screen_order||'');
209
210 FOR rec IN people_with_problems LOOP
211 -- RECORDS THE ERROR IN THE TRANSFORMATION ERROR LOGGER BUT DOES * NOT REJECT THE LINE *
212 error_log(v_screen_name,SYSDATE,p_source_key,p_iteration_key,rec.rowid);
213 i:=i+1;
214 --Update table, when rejected change
215 UPDATE t_data_customers
216 SET rejected_by_screen = '1'
217 WHERE rowid = rec.rowid;
218 END LOOP;
219
220 pck_log.write_log(' Data quality problems in '|| i || ' row(s).',' Done!');
221 EXCEPTION
222 WHEN NO_DATA_FOUND THEN
223 pck_log.write_log(' No data quality problems found.',' Done!');
224 WHEN OTHERS THEN
225 pck_log.write_uncomplete_task_msg;
226 RAISE e_transformation;
227 END;
228
229
230
231 -- *************************************************************************************
232 -- * GOAL: detect and reject packed products with an empty liquid weight *
233 -- * QUALITY CRITERIUM: "Completude" *
234 -- * PARAMETERS: *
235 -- * p_iteration_key: key of the iteration in which the screen will be run *
236 -- * p_source_key: key of the source system related to the screen's execution *
237 -- * p_screen_order: order number in which the screen is to be executed *
238 -- *************************************************************************************
239 PROCEDURE screen_null_liq_weight (p_iteration_key t_tel_iteration.iteration_key%TYPE,
240 p_source_key t_tel_source.source_key%TYPE,
241 p_screen_order t_tel_schedule.screen_order%TYPE) IS
242
243 CURSOR products_with_problems IS
244 SELECT rowid
245 FROM t_data_products
246 WHERE rejected_by_screen='0'
247 AND liq_weight IS NULL;
248
249 i PLS_INTEGER:=0;
250 v_screen_name VARCHAR2(30):='screen_null_liq_weight';
251 BEGIN
252 pck_log.write_log(' Starting SCREEN ["'||UPPER(v_screen_name)||'"] with order #'||p_screen_order||'');
253
254 FOR rec IN products_with_problems LOOP
255 -- RECORDS THE ERROR IN THE TRANSFORMATION ERROR LOGGER BUT DOES * NOT REJECT THE LINE *
256 error_log(v_screen_name,SYSDATE,p_source_key,p_iteration_key,rec.rowid);
257 i:=i+1;
258 --Update table, when rejected change
259 UPDATE t_data_products
260 SET rejected_by_screen = '1'
261 WHERE rowid = rec.rowid;
262 END LOOP;
263
264 pck_log.write_log(' Data quality problems in '|| i || ' row(s).',' Done!');
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 pck_log.write_log(' No data quality problems found.',' Done!');
268 WHEN OTHERS THEN
269 pck_log.write_uncomplete_task_msg;
270 RAISE e_transformation;
271 END;
272
273
274 -- *************************************************************************************
275 -- * GOAL: detect incorrect data in products *
276 -- * QUALITY CRITERIUM: "Corre¿¿o" *
277 -- * PARAMETERS: *
278 -- * p_iteration_key: key of the iteration in which the screen will be run *
279 -- * p_source_key: key of the source system related to the screen's execution *
280 -- * p_screen_order: order number in which the screen is to be executed *
281 -- *************************************************************************************
282 PROCEDURE screen_incorrect_products ( p_iteration_key t_tel_iteration.iteration_key%TYPE,
283 p_source_key t_tel_source.source_key%TYPE,
284 p_screen_order t_tel_schedule.screen_order%TYPE) IS
285 CURSOR products_with_problems IS
286 SELECT rowid
287 FROM t_data_products
288 WHERE rejected_by_screen='0'
289 AND brand IS NULL OR brand IN (SELECT BRAND_WRONG FROM T_LOOKUP_BRANDS);
290
291 --ou EXISTS (SELECT * FROM T_LOOKUP_BRANDS WHERE BRAND_WRONG = brand);
292
293 i PLS_INTEGER:=0;
294 v_screen_name VARCHAR2(30):='screen_incorrect_products';
295 BEGIN
296 pck_log.write_log(' Starting SCREEN ["'||UPPER(v_screen_name)||'"] with order #'||p_screen_order||'');
297
298 pck_log.write_log(' Data quality problems in '|| i || ' row(s).',' Done!');
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 pck_log.write_log(' No data quality problems found.',' Done!');
302 WHEN OTHERS THEN
303 pck_log.write_uncomplete_task_msg;
304 RAISE e_transformation;
305 END;
306
307 -- ***************************************************************************************
308 -- * Se t_data_celcius tem falta até 10% valores t_min ou t_max: *
309 -- * Assumir média dos valores diàrios para os valores em falta *
310 -- * Mais de 10% dos valores em falta: *
311 -- * Abortar processo ETL *
312 -- ***************************************************************************************
313
314 PROCEDURE screen_incorrect_temperatures ( p_iteration_key t_tel_iteration.iteration_key%TYPE,
315 p_source_key t_tel_source.source_key%TYPE,
316 p_screen_order t_tel_schedule.screen_order%TYPE) IS
317
318 CURSOR lines_with_problems IS
319 SELECT rowid
320 FROM t_data_celsius
321 WHERE rejected_by_screen='0'
322 AND t_max IS NULL OR t_min IS NULL;
323
324 missing_lines NUMBER := 0;
325 total_lines NUMBER := -1;
326 average_lines NUMBER := 0;
327
328 i PLS_INTEGER:=0;
329 v_screen_name VARCHAR2(30):='screen_incorrect_temperatures';
330 BEGIN
331 pck_log.write_log(' Starting SCREEN ["'||UPPER(v_screen_name)||'"] with order #'||p_screen_order||'');
332
333
334 SELECT COUNT(*) INTO missing_lines FROM t_data_celsius WHERE t_min IS NULL OR t_max IS NULL;
335 SELECT COUNT(*) INTO total_lines FROM t_data_celsius;
336
337 IF total_lines <= 0 THEN RAISE e_transformation; END IF;
338
339 FOR rec IN lines_with_problems LOOP
340 error_log(v_screen_name,SYSDATE,p_source_key,p_iteration_key,rec.rowid);
341 i:=i+1;
342 --Update table, when rejected change
343 UPDATE t_data_celsius
344 SET rejected_by_screen = '1'
345 WHERE rowid = rec.rowid;
346 END LOOP;
347
348 average_lines := missing_lines/total_lines;
349
350 IF average_lines > 0.1 THEN
351 pck_log.write_log(' Data quality problems of missing temperatures in '|| missing_lines || ' row(s).'||average_lines*100||'% (more than 10%!) ',' Halting!');
352 RAISE e_transformation;
353 END IF;
354
355 pck_log.write_log(' Data quality problems of missing temperatures in '|| missing_lines || ' row(s).'||average_lines*100||'% ',' Done!');
356 EXCEPTION
357 WHEN NO_DATA_FOUND THEN
358 pck_log.write_log(' No data quality problems found.',' Done!');
359 WHEN OTHERS THEN
360 pck_log.write_uncomplete_task_msg;
361 RAISE e_transformation;
362 END;
363
364
365
366 -- ####################### TRANSFORMATION ROUTINES #######################
367 -- ***************************************************************
368 -- * TESTE1819 *
369 -- ***************************************************************
370 PROCEDURE transform_employees IS
371 CURSOR employees_with_problems IS
372 SELECT RECORD_ID FROM T_TEL_ERROR
373 WHERE SCREEN_KEY = (SELECT SCREEN_KEY
374 FROM T_TEL_SCREEN WHERE
375 SCREEN_NAME = 'SCREEN_NULL_CATEGORY') AND ITERATION_KEY = (SELECT MAX(iteration_key) FROM t_tel_iteration);
376 BEGIN
377 pck_log.write_log(' Transforming data ["TRANSFORM_EMPLOYEES"]');
378
379 INSERT INTO T_CLEAN_EMPLOYEES (id,NAME,CATEGORY,DEPARTMENT,AGE,MARITAL_STATUS)
380 SELECT ID,NAME,UPPER(CATEGORY),UPPER(DEPARTMENT),ROUND(MONTHS_BETWEEN(SYSDATE,BIRTH_DATE)/12) AS AGE,MARITAL_STATUS
381 FROM T_DATA_EMPLOYEES
382 WHERE REJECTED_BY_SCREEN='0';
383
384 pck_log.write_log(' Done!');
385 EXCEPTION
386 WHEN NO_DATA_FOUND THEN
387 pck_log.write_log(' Found no lines to transform',' Done!');
388 WHEN OTHERS THEN
389 pck_log.write_uncomplete_task_msg;
390 RAISE e_transformation;
391 END;
392
393
394 -- ********************************************************************
395 -- * e) TRANSFORMATION OF IPMA Data ACCORDING TO THE LOGICAL DATA MAP *
396 -- ********************************************************************
397
398 PROCEDURE transform_celcius IS
399 average_daily_temperature NUMBER := 10;
400
401 BEGIN
402 pck_log.write_log(' Transforming data ["TRANSFORM_CELCIUS"]');
403
404
405 SELECT AVG((t_min + t_max) * 0.5) INTO average_daily_temperature
406 FROM t_data_celsius
407 WHERE rejected_by_screen='0';
408
409 UPDATE t_data_celsius
410 SET t_min = average_daily_temperature,rejected_by_screen='0'
411 WHERE rejected_by_screen='1' AND t_min IS NULL;
412
413 UPDATE t_data_celsius
414 SET t_max = average_daily_temperature,rejected_by_screen='0'
415 WHERE rejected_by_screen='1' AND t_max IS NULL;
416
417 INSERT INTO t_clean_celsius(forecast_date,temperature_status)
418 SELECT SYSDATE,
419 CASE
420 WHEN average_daily_temperature<4 THEN 'COLD'
421 WHEN average_daily_temperature<10 THEN 'FRESH'
422 WHEN average_daily_temperature<25 THEN 'NICE'
423 ELSE 'HOT'
424 END
425 FROM t_data_celsius;
426
427 pck_log.write_log(' Done!');
428 EXCEPTION
429 WHEN NO_DATA_FOUND THEN
430 pck_log.write_log(' Found no lines to transform',' Done!');
431 WHEN OTHERS THEN
432 pck_log.write_uncomplete_task_msg;
433 RAISE e_transformation;
434 END;
435
436 -- ****************************************************************
437 -- * TRANSFORMATION OF PRODUCTS ACCORDING TO THE LOGICAL DATA MAP *
438 -- ****************************************************************
439
440 PROCEDURE transform_products IS
441 BEGIN
442 pck_log.write_log(' Transforming data ["TRANSFORM_PRODUCTS"]');
443
444 INSERT INTO t_clean_products(id,name,brand,pack_size,pack_type,diet_type,liq_weight,category_name)
445 SELECT prod.id,prod.name,brand,height||'x'||width||'x'||depth,pack_type,cal.type,liq_weight,categ.name
446 FROM t_data_products prod, t_lookup_calories cal, t_data_categories categ
447 WHERE categ.rejected_by_screen='0'
448 AND prod.rejected_by_screen='0'
449 AND calories_100g>=cal.min_calories_100g
450 AND calories_100g<=cal.max_calories_100g
451 AND categ.id=prod.category_id;
452
453 -- CORRECT MISTAKES ON BRANDS COLUMN
454 -- Falta ir a tabela t_data_products ir buscar dados, transformar a marca quando está errada.
455 UPDATE t_clean_products
456 SET brand = (SELECT brand_transformed FROM t_lookup_brands WHERE brand_wrong = t_clean_products.brand)
457 WHERE brand IN (SELECT brand_wrong FROM t_lookup_brands);
458
459 pck_log.write_log(' Done!');
460 EXCEPTION
461 WHEN NO_DATA_FOUND THEN
462 pck_log.write_log(' Found no lines to transform',' Done!');
463 WHEN OTHERS THEN
464 pck_log.write_uncomplete_task_msg;
465 RAISE e_transformation;
466 END;
467
468
469
470 -- **************************************************************
471 -- * TRANSFORMATION OF STORES ACCORDING TO THE LOGICAL DATA MAP *
472 -- **************************************************************
473 PROCEDURE transform_stores IS
474 BEGIN
475 pck_log.write_log(' Transforming data ["TRANSFORM_STORES"]');
476
477 INSERT INTO t_clean_stores(name,reference,address,zip_code,location,district,telephones,fax,status,manager_name,manager_since)
478 SELECT name,s.reference,CASE building WHEN '-' THEN NULL ELSE building||' - ' END || address||' / '||zip_code||', '||location,zip_code,location,district,SUBSTR(REPLACE(REPLACE(telephones,'.',''),' ',''),1,9),fax,CASE WHEN closure_date IS NULL THEN 'ACTIVE' ELSE 'INACTIVE' END, manager_name,manager_since
479 FROM (SELECT name,reference,building,address,zip_code,location,district,telephones,fax,closure_date
480 FROM t_data_stores_new
481 WHERE rejected_by_screen='0'
482 MINUS
483 SELECT name,reference,building,address,zip_code,location,district,telephones,fax,closure_date
484 FROM t_data_stores_old) s, t_data_managers_new d
485 WHERE s.reference=d.reference AND
486 d.rejected_by_screen='0';
487
488 pck_log.write_log(' Done!');
489 EXCEPTION
490 WHEN NO_DATA_FOUND THEN
491 pck_log.write_log(' Found no lines to transform',' Done!');
492 WHEN OTHERS THEN
493 pck_log.write_uncomplete_task_msg;
494 RAISE e_transformation;
495 END;
496
497
498 -- *********************************************************
499 -- * TRANSFORMATION OF SALES ACCORDING TO LOGICAL DATA MAP *
500 -- *********************************************************
501 PROCEDURE transform_sales IS
502 BEGIN
503 pck_log.write_log(' Transforming data ["TRANSFORM_SALES"]');
504
505 INSERT INTO t_clean_sales(id,sale_date,store_id, customer_id)
506 SELECT id,sale_date,store_id, customer_id
507 FROM t_data_sales
508 WHERE rejected_by_screen='0';
509
510 pck_log.write_log(' Done!');
511 EXCEPTION
512 WHEN NO_DATA_FOUND THEN
513 pck_log.write_log(' Found no lines to transform',' Done!');
514 WHEN OTHERS THEN
515 pck_log.write_uncomplete_task_msg;
516 RAISE e_transformation;
517 END;
518
519
520 -- *************************************************************
521 -- * TRANSFORMATION OF CUSTOMERS ACCORDING TO LOGICAL DATA MAP *
522 -- *************************************************************
523 PROCEDURE transform_customers IS
524 CURSOR invalid_genders_rowids IS
525 SELECT record_id
526 FROM t_tel_error
527 WHERE screen_key = (SELECT screen_key FROM T_TEL_SCREEN WHERE SCREEN_NAME = 'SCREEN_GENDER_DISCRIMINATION');
528 BEGIN
529 pck_log.write_log(' Transforming data ["TRANSFORM_CUSTOMERS"]');
530
531 FOR rec IN invalid_genders_rowids LOOP
532 UPDATE t_data_customers
533 SET gender = (CASE WHEN gender='F' THEN 'M' ELSE 'F' END),rejected_by_screen='0'
534 WHERE rowid = rec.record_id;
535 END LOOP;
536
537 INSERT INTO t_clean_customers(id,card_number,name,address,location,district,zip_code,phone_nr,gender,age,marital_status)
538 SELECT id,card_number,name,address,UPPER(location),UPPER(district),zip_code,phone_nr,
539 CASE UPPER(gender) WHEN 'M' THEN 'MALE' WHEN 'F' THEN 'FEMALE' ELSE 'OTHER' END,
540 age,
541 CASE UPPER(marital_status) WHEN 'C' THEN 'MARRIED' WHEN 'S' THEN 'SINGLE' WHEN 'V' THEN 'WIDOW' WHEN 'D' THEN 'DIVORCED' ELSE 'OTHER' END
542 FROM t_data_customers
543 WHERE rejected_by_screen='0';
544
545 pck_log.write_log(' Done!');
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN
548 pck_log.write_log(' Found no lines to transform',' Done!');
549 WHEN OTHERS THEN
550 pck_log.write_uncomplete_task_msg;
551 RAISE e_transformation;
552 END;
553
554 -- *************************************************************
555 -- * TRANSFORMATION OF PROMOTIONS ACCORDING TO LOGICAL DATA MAP *
556 -- *************************************************************
557 PROCEDURE transform_promotions IS
558 BEGIN
559 pck_log.write_log(' Transforming data ["TRANSFORM_PROMOTIONS"]');
560
561 --No mapa geral da arquitetura a transformação alimenta acaba na t_dim_promotions (mesmo no fim)
562 --logo é esta que procuramos no mapa lógico de dados:
563 --Mapa logico de dados target: t_dim_promotion , (coluna azul 'transformation'):
564 --IF (src_on_tv=1) THEN 'YES' ELSE 'NO'
565 --IF (src_on_outdoor=1) THEN 'YES' ELSE 'NO'
566
567 --Fonte: T_DATA_PROMOTIONS
568 --Destino: T_CLEAN_PROMOTION
569
570 --Em ambiente de avaliação, embora não seja especificado seria melhor fazer o screen e rejeitar a linha.
571
572 --TPC
573 --Devia ha ver um screen para detetar falta de preenchimento em linhas (tirando o end_date, pode haver promocoes a decorrer)
574
575 --No nosso caso por agora metemos NO (antes de implementar o screen)
576
577 INSERT INTO t_clean_promotions (id,name,start_date,end_date,reduction,on_street,on_tv)
578 SELECT id,name,start_date,end_date,reduction,
579 CASE WHEN on_outdoor=1 THEN 'YES' ELSE 'NO' END,
580 CASE WHEN on_tv=1 THEN 'YES' ELSE 'NO' END
581 FROM t_data_promotions
582 WHERE rejected_by_screen='0';
583
584 pck_log.write_log(' Done!');
585 EXCEPTION
586 WHEN NO_DATA_FOUND THEN
587 pck_log.write_log(' Found no lines to transform',' Done!');
588 WHEN OTHERS THEN
589 pck_log.write_uncomplete_task_msg;
590 RAISE e_transformation;
591 END;
592
593
594 -- *********************************************************
595 -- * TRANSFORMATION OF FACTS ACCORDING TO LOGICAL DATA MAP *
596 -- *********************************************************
597 PROCEDURE transform_linesofsale IS
598 BEGIN
599 pck_log.write_log(' Transforming data ["TRANSFORM_LINESOFSALE"]');
600
601 INSERT INTO t_clean_linesofsale(id,sale_id,product_id,promo_id,quantity,ammount_paid,line_date)
602 SELECT los.id,los.sale_id,los.product_id,losp.promo_id,quantity,ammount_paid, los.line_date
603 FROM t_data_linesofsale los LEFT JOIN (SELECT line_id,promo_id
604 FROM t_data_linesofsalepromotions
605 WHERE rejected_by_screen='0') losp ON los.id=losp.line_id, t_data_sales
606 WHERE los.rejected_by_screen='0' AND
607 t_data_sales.id=los.sale_id;
608
609 pck_log.write_log(' Done!');
610 EXCEPTION
611 WHEN NO_DATA_FOUND THEN
612 pck_log.write_log(' Found no lines to transform',' Done!');
613 WHEN OTHERS THEN
614 pck_log.write_uncomplete_task_msg;
615 RAISE e_transformation;
616 END;
617
618
619 -- *****************************************************************************************************
620 -- * MAIN *
621 -- * *
622 -- * EXECUTES THE TRANSFORMATION PROCESS *
623 -- * IN *
624 -- * p_duplicate_last_iteration: TRUE=duplicates last iteration and its schedule (FOR TESTS ONLY!) *
625 -- *****************************************************************************************************
626 PROCEDURE main (p_duplicate_last_iteration BOOLEAN) IS
627 -- checks all scheduled screens
628 cursor scheduled_screens_cursor(p_iteration_key t_tel_iteration.iteration_key%TYPE) IS
629 SELECT UPPER(screen_name) screen_name,source_key,screen_order
630 FROM t_tel_schedule, t_tel_screen
631 WHERE iteration_key=p_iteration_key AND
632 t_tel_schedule.screen_key=t_tel_screen.screen_key;
633
634 v_iteration_key t_tel_iteration.iteration_key%TYPE;
635 v_sql VARCHAR2(200);
636 BEGIN
637 pck_log.write_log(' ','***** TRANSFORM TRANSFORM TRANSFORM TRANSFORM TRANSFORM TRANSFORM *****'); -- DUPLICATES THE LAST ITERATION AND THE CORRESPONDING SCREEN SCHEDULE
638 IF p_duplicate_last_iteration THEN
639 duplicate_last_iteration(SYSDATE);
640 END IF;
641
642 -- CLEANS ALL _clean TABLES
643 -- TPC Optimizar isto dinamicamente!
644 pck_log.write_log(' Deleting old _clean tables');
645 DELETE FROM t_clean_customers;
646 DELETE FROM t_clean_products;
647 DELETE FROM t_clean_linesofsale;
648 DELETE FROM t_clean_stores;
649 DELETE FROM t_clean_promotions;
650 DELETE FROM t_clean_sales;
651 pck_log.write_log(' Done!');
652
653 -- FINDS THE MOST RECENTLY SCHEDULED ITERATION
654 BEGIN
655 SELECT MAX(iteration_key)
656 INTO v_iteration_key
657 FROM t_tel_iteration;
658 EXCEPTION
659 WHEN OTHERS THEN
660 RAISE e_transformation;
661 END;
662
663 -- ### TPC OPTIMIZATION 1 ###
664 --If there is no iteration key, then there are no transformations to do.
665 --If today's date is not on the table T_TEL_ITERATIONS then this should not RUN!
666
667 -- RUNS ALL THE SCHEDULED SCREENS
668 -- Versão estática (deprecated)
669 /*
670 FOR rec IN scheduled_screens_cursor(v_iteration_key) LOOP
671 IF UPPER(rec.screen_name)='SCREEN_PRODUCT_DIMENSIONS' THEN
672 screen_dimensions(v_iteration_key, rec.source_key, rec.screen_order);
673 ELSIF UPPER(rec.screen_name)='SCREEN_NULL_LIQ_WEIGHT' THEN
674 screen_null_liq_weight(v_iteration_key, rec.source_key, rec.screen_order);
675 END IF;
676 */
677
678 -- EXECUÇÃO DINÂMICA DE SCREENS
679 FOR rec IN scheduled_screens_cursor(v_iteration_key) LOOP
680 v_sql:='BEGIN pck_transform.'||rec.screen_name||'('||v_iteration_key||','||rec.source_key||','||rec.screen_order||'); END;';
681 pck_log.write_log('[TRANSFORM] '||v_sql);
682 EXECUTE IMMEDIATE v_sql;
683 END LOOP;
684
685 -- UPDATES TABLE "T_TEL_ITERATION"
686 UPDATE t_tel_iteration
687 SET iteration_end_date = SYSDATE,
688 iteration_duration_real=(SYSDATE-iteration_start_date)/86400
689 WHERE iteration_key = v_iteration_key;
690
691 pck_log.write_log(' All screens have been run.');
692
693 -- ### TPC OPTIMIZATION 2 ###
694 -- Execução dinamica de transformações, tal como fazemos nos screens!
695 -- Duvida de gabinete: (Como fazer o escrito acima) e Nos testes "serão consideradas para avaliação apenas soluções dinâmicas"
696
697 -- EXECUTES THE TRANSFORMATION ROUTINES, order defined by the pdf of ETL Diagram
698 transform_customers;
699 transform_products;
700 transform_promotions;
701 transform_stores;
702 transform_sales;
703 transform_linesofsale;
704 transform_celcius;
705 transform_employees;
706
707 COMMIT;
708 pck_log.write_log(' All transformed data commited to database.');
709 EXCEPTION
710 WHEN e_transformation THEN
711 pck_log.write_halt_msg;
712 ROLLBACK;
713 WHEN OTHERS THEN
714 ROLLBACK;
715 pck_log.write_uncomplete_task_msg;
716 pck_log.write_halt_msg;
717 END;
718
719end pck_transform;