· 7 years ago · Nov 20, 2018, 03:48 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
91 -- *************************************************************************************
92 -- * GOAL: Detect incorrect data in the size of products *
93 -- * QUALITY CRITERIUM: "Correção" *
94 -- * PARAMETERS: *
95 -- * p_iteration_key: key of the iteration in which the screen will be run *
96 -- * p_source_key: key of the source system related to the screen's execution *
97 -- * p_screen_order: order number in which the screen is to be executed *
98 -- *************************************************************************************
99 PROCEDURE screen_product_dimensions (p_iteration_key t_tel_iteration.iteration_key%TYPE,
100 p_source_key t_tel_source.source_key%TYPE,
101 p_screen_order t_tel_schedule.screen_order%TYPE) IS
102 -- SEARCH FOR EXTRACTED PRODUCTS CONTAINING PROBLEMS
103 CURSOR products_with_problems IS
104 SELECT rowid
105 FROM t_data_products
106 WHERE rejected_by_screen='0'
107 AND (((width IS NULL OR height IS NULL OR depth IS NULL) AND UPPER(pack_type) IN (SELECT pack_type
108 FROM t_lookup_pack_dimensions
109 WHERE has_dimensions='1'))
110 OR ((width>=0 OR height>=0 OR depth>=0 AND UPPER(pack_type) IN (SELECT pack_type
111 FROM t_lookup_pack_dimensions
112 WHERE has_dimensions='0'))));
113 i PLS_INTEGER:=0;
114 v_screen_name VARCHAR2(30):='screen_product_dimensions';
115 BEGIN
116 pck_log.write_log(' Starting SCREEN ["'||UPPER(v_screen_name)||'"] with order #'||p_screen_order||'');
117 FOR rec IN products_with_problems LOOP
118 -- RECORDS THE ERROR IN THE TRANSFORMATION ERROR LOGGER BUT DOES * NOT REJECT THE LINE *
119 error_log(v_screen_name,SYSDATE,p_source_key,p_iteration_key,rec.rowid);
120 i:=i+1;
121 END LOOP;
122 pck_log.write_log(' Data quality problems in '|| i || ' row(s).',' Done!');
123 EXCEPTION
124 WHEN NO_DATA_FOUND THEN
125 pck_log.write_log(' No data quality problems found.',' Done!');
126 WHEN OTHERS THEN
127 pck_log.write_uncomplete_task_msg;
128 RAISE e_transformation;
129 END;
130
131
132
133 -- *************************************************************************************
134 -- * GOAL: detect and reject packed products with an empty liquid weight *
135 -- * QUALITY CRITERIUM: "Completude" *
136 -- * PARAMETERS: *
137 -- * p_iteration_key: key of the iteration in which the screen will be run *
138 -- * p_source_key: key of the source system related to the screen's execution *
139 -- * p_screen_order: order number in which the screen is to be executed *
140 -- *************************************************************************************
141 PROCEDURE screen_null_liq_weight (p_iteration_key t_tel_iteration.iteration_key%TYPE,
142 p_source_key t_tel_source.source_key%TYPE,
143 p_screen_order t_tel_schedule.screen_order%TYPE) IS
144 -- SOMETHING IS MISSING
145 -- ???
146 CURSOR products_with_problems IS
147 SELECT rowid
148 FROM t_data_products
149 WHERE rejected_by_screen='0' AND ((liq_weight IS NULL AND pack_type IS NOT NULL) OR (liq_weight IS NOT NULL AND pack_type IS NULL));
150
151 i PLS_INTEGER:=0;
152 v_screen_name VARCHAR2(30):='screen_null_liq_weight';
153 BEGIN
154 pck_log.write_log(' Starting SCREEN ["'||UPPER(v_screen_name)||'"] with order #'||p_screen_order||'');
155
156 -- SOMETHING IS MISSING
157 -- null;
158 FOR rec IN products_with_problems LOOP
159 --RECORDS THE ERROR
160 error_log(v_screen_name, SYSDATE, p_source_key, p_iteration_key, rec.rowid);
161 -- SHIT
162 UPDATE t_data_products
163 SET rejected_by_screen='1'
164 WHERE rowid=rec.rowid;
165
166 i:=i+1;
167 END LOOP;
168
169 pck_log.write_log(' Data quality problems in '|| i || ' row(s).',' Done!');
170 EXCEPTION
171 WHEN NO_DATA_FOUND THEN
172 pck_log.write_log(' No data quality problems found.',' Done!');
173 WHEN OTHERS THEN
174 pck_log.write_uncomplete_task_msg;
175 RAISE e_transformation;
176 END;
177
178
179 -- *************************************************************************************
180 -- * GOAL: detect incorrect data in products *
181 -- * QUALITY CRITERIUM: "Correção" *
182 -- * PARAMETERS: *
183 -- * p_iteration_key: key of the iteration in which the screen will be run *
184 -- * p_source_key: key of the source system related to the screen's execution *
185 -- * p_screen_order: order number in which the screen is to be executed *
186 -- *************************************************************************************
187 PROCEDURE screen_incorrect_products ( p_iteration_key t_tel_iteration.iteration_key%TYPE,
188 p_source_key t_tel_source.source_key%TYPE,
189 p_screen_order t_tel_schedule.screen_order%TYPE) IS
190 -- SOMETHING IS MISSING
191 -- ???
192 CURSOR produtcts_with_problems IS
193 SELECT p.rowid
194 FROM t_data_products p JOIN t_lookup_brands b ON b.brand_wrong = p.brand
195 WHERE rejected_by_screen='0';
196
197 i PLS_INTEGER:=0;
198 v_screen_name VARCHAR2(30):='screen_incorrect_products';
199 BEGIN
200 pck_log.write_log(' Starting SCREEN ["'||UPPER(v_screen_name)||'"] with order #'||p_screen_order||'');
201
202 -- SOMETHING IS MISSING
203 -- null;
204 FOR rec IN produtcts_with_problems LOOP
205 error_log(v_screen_name, SYSDATE, p_source_key, p_iteration_key, rec.rowid);
206 END LOOP;
207 pck_log.write_log(' Data quality problems in '|| i || ' row(s).',' Done!');
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 pck_log.write_log(' No data quality problems found.',' Done!');
211 WHEN OTHERS THEN
212 pck_log.write_uncomplete_task_msg;
213 RAISE e_transformation;
214 END;
215
216
217
218
219 -- ####################### TRANSFORMATION ROUTINES #######################
220
221 -- ****************************************************************
222 -- * TRANSFORMATION OF PRODUCTS ACCORDING TO THE LOGICAL DATA MAP *
223 -- ****************************************************************
224
225 PROCEDURE transform_products IS
226 BEGIN
227 pck_log.write_log(' Transforming data ["TRANSFORM_PRODUCTS"]');
228
229 INSERT INTO t_clean_products(id,name,brand,pack_size,pack_type,diet_type,liq_weight,category_name)
230 SELECT prod.id,prod.name,brand,height||'x'||width||'x'||depth,pack_type,cal.type,liq_weight,categ.name
231 FROM t_data_products prod, t_lookup_calories cal, t_data_categories categ
232 WHERE categ.rejected_by_screen='0'
233 AND prod.rejected_by_screen='0'
234 AND calories_100g>=cal.min_calories_100g
235 AND calories_100g<=cal.max_calories_100g
236 AND categ.id=prod.category_id;
237
238 -- SOMETHING IS MISSING
239 --null
240 UPDATE t_clean_products t
241 SET brand = (SELECT b.brand_transformed FROM t_lookup_brands b
242 WHERE t.brand = b.brand_wrong)
243 WHERE EXISTS (SELECT b.brand_transformed FROM t_lookup_brands b
244 WHERE t.brand = b.brand_wrong);
245
246 pck_log.write_log(' Done!');
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 pck_log.write_log(' Found no lines to transform',' Done!');
250 WHEN OTHERS THEN
251 pck_log.write_uncomplete_task_msg;
252 RAISE e_transformation;
253 END;
254
255
256 -- ****************************************************************
257 -- * TRANSFORMATION OF PROMOTIONS ACCORDING TO THE LOGICAL DATA MAP *
258 -- ****************************************************************
259
260 PROCEDURE transform_promotions IS
261 BEGIN
262 pck_log.write_log(' Transforming data ["TRANSFORM_PROMOTIONS"]');
263
264 INSERT INTO t_clean_promotions(id,name,start_date,end_date,reduction,on_street,on_tv)
265 SELECT id, name, start_date, end_date, reduction,
266 CASE WHEN on_outdoor = 1 THEN 'YES' ELSE 'NO' END AS on_outdoor,
267 CASE WHEN on_tv = 1 THEN 'YES' ELSE 'NO' END AS on_tv
268 FROM t_data_promotions
269 WHERE rejected_by_screen ='0';
270
271 -- SOMETHING IS MISSING
272 --null;
273
274 pck_log.write_log(' Done!');
275 EXCEPTION
276 WHEN NO_DATA_FOUND THEN
277 pck_log.write_log(' Found no lines to transform',' Done!');
278 WHEN OTHERS THEN
279 pck_log.write_uncomplete_task_msg;
280 RAISE e_transformation;
281 END;
282
283
284
285 -- **************************************************************
286 -- * TRANSFORMATION OF STORES ACCORDING TO THE LOGICAL DATA MAP *
287 -- **************************************************************
288 PROCEDURE transform_stores IS
289 BEGIN
290 pck_log.write_log(' Transforming data ["TRANSFORM_STORES"]');
291
292 INSERT INTO t_clean_stores(name,reference,address,zip_code,location,district,telephones,fax,status,manager_name,manager_since)
293 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
294 FROM (SELECT name,reference,building,address,zip_code,location,district,telephones,fax,closure_date
295 FROM t_data_stores_new
296 WHERE rejected_by_screen='0'
297 MINUS
298 SELECT name,reference,building,address,zip_code,location,district,telephones,fax,closure_date
299 FROM t_data_stores_old) s, t_data_managers_new d
300 WHERE s.reference=d.reference AND
301 d.rejected_by_screen='0';
302
303 pck_log.write_log(' Done!');
304 EXCEPTION
305 WHEN NO_DATA_FOUND THEN
306 pck_log.write_log(' Found no lines to transform',' Done!');
307 WHEN OTHERS THEN
308 pck_log.write_uncomplete_task_msg;
309 RAISE e_transformation;
310 END;
311
312
313 -- *********************************************************
314 -- * TRANSFORMATION OF SALES ACCORDING TO LOGICAL DATA MAP *
315 -- *********************************************************
316 PROCEDURE transform_sales IS
317 BEGIN
318 pck_log.write_log(' Transforming data ["TRANSFORM_SALES"]');
319
320 INSERT INTO t_clean_sales(id,sale_date,store_id, customer_id)
321 SELECT id,sale_date,store_id, customer_id
322 FROM t_data_sales
323 WHERE rejected_by_screen='0';
324
325 pck_log.write_log(' Done!');
326 EXCEPTION
327 WHEN NO_DATA_FOUND THEN
328 pck_log.write_log(' Found no lines to transform',' Done!');
329 WHEN OTHERS THEN
330 pck_log.write_uncomplete_task_msg;
331 RAISE e_transformation;
332 END;
333
334
335 -- *************************************************************
336 -- * TRANSFORMATION OF CUSTOMERS ACCORDING TO LOGICAL DATA MAP *
337 -- *************************************************************
338 PROCEDURE transform_customers IS
339 BEGIN
340 pck_log.write_log(' Transforming data ["TRANSFORM_CUSTOMERS"]');
341
342 INSERT INTO t_clean_customers(id,card_number,name,address,location,district,zip_code,phone_nr,gender,age,marital_status)
343 SELECT id,card_number,name,address,UPPER(location),UPPER(district),zip_code,phone_nr,
344 CASE UPPER(gender) WHEN 'M' THEN 'MALE' WHEN 'F' THEN 'FEMALE' ELSE 'OTHER' END,
345 age,
346 CASE UPPER(marital_status) WHEN 'C' THEN 'MARRIED' WHEN 'S' THEN 'SINGLE' WHEN 'V' THEN 'WIDOW' WHEN 'D' THEN 'DIVORCED' ELSE 'OTHER' END
347 FROM t_data_customers
348 WHERE rejected_by_screen='0';
349
350 pck_log.write_log(' Done!');
351 EXCEPTION
352 WHEN NO_DATA_FOUND THEN
353 pck_log.write_log(' Found no lines to transform',' Done!');
354 WHEN OTHERS THEN
355 pck_log.write_uncomplete_task_msg;
356 RAISE e_transformation;
357 END;
358
359
360
361 -- *********************************************************
362 -- * TRANSFORMATION OF FACTS ACCORDING TO LOGICAL DATA MAP *
363 -- *********************************************************
364 PROCEDURE transform_linesofsale IS
365 BEGIN
366 pck_log.write_log(' Transforming data ["TRANSFORM_LINESOFSALE"]');
367
368 INSERT INTO t_clean_linesofsale(id,sale_id,product_id,promo_id,quantity,ammount_paid,line_date)
369 SELECT los.id,los.sale_id,los.product_id,losp.promo_id,quantity,ammount_paid, los.line_date
370 FROM t_data_linesofsale los LEFT JOIN (SELECT line_id,promo_id
371 FROM t_data_linesofsalepromotions
372 WHERE rejected_by_screen='0') losp ON los.id=losp.line_id, t_data_sales
373 WHERE los.rejected_by_screen='0' AND
374 t_data_sales.id=los.sale_id;
375
376 pck_log.write_log(' Done!');
377 EXCEPTION
378 WHEN NO_DATA_FOUND THEN
379 pck_log.write_log(' Found no lines to transform',' Done!');
380 WHEN OTHERS THEN
381 pck_log.write_uncomplete_task_msg;
382 RAISE e_transformation;
383 END;
384
385
386
387 -- *********************************************************
388 -- * TRANSFORMATION OF CELSIUS ACCORDING TO LOGICAL DATA MAP *
389 -- *********************************************************
390 PROCEDURE transform_celsius IS
391 BEGIN
392 pck_log.write_log(' Transforming data ["TRANSFORM_CELSIUS"]');
393
394 INSERT INTO t_clean_celsius(temperature_status)
395 SELECT CASE WHEN AVG((t_max+t_min)/2) < 4 THEN 'COLD'
396 WHEN AVG((t_max+t_min)/2) < 10 THEN 'FRESH'
397 WHEN AVG((t_max+t_min)/2) < 25 THEN 'NICE'
398 ELSE 'HOT' END AS temp_status
399 FROM t_data_celsius;
400 --WHERE rejected_by_screen = '0';
401
402 pck_log.write_log(' Done!');
403 EXCEPTION
404 WHEN NO_DATA_FOUND THEN
405 pck_log.write_log(' Found no lines to transform',' Done!');
406 WHEN OTHERS THEN
407 pck_log.write_uncomplete_task_msg;
408 RAISE e_transformation;
409 END;
410
411
412
413 -- *****************************************************************************************************
414 -- * MAIN *
415 -- * *
416 -- * EXECUTES THE TRANSFORMATION PROCESS *
417 -- * IN *
418 -- * p_duplicate_last_iteration: TRUE=duplicates last iteration and its schedule (FOR TESTS ONLY!) *
419 -- *****************************************************************************************************
420 PROCEDURE main (p_duplicate_last_iteration BOOLEAN) IS
421 -- checks all scheduled screens
422 cursor scheduled_screens_cursor(p_iteration_key t_tel_iteration.iteration_key%TYPE) IS
423 SELECT UPPER(screen_name) screen_name,source_key,screen_order
424 FROM t_tel_schedule, t_tel_screen
425 WHERE iteration_key=p_iteration_key AND
426 t_tel_schedule.screen_key=t_tel_screen.screen_key
427 ORDER BY screen_order;
428
429 v_iteration_key t_tel_iteration.iteration_key%TYPE;
430 v_sql VARCHAR2(200);
431 BEGIN
432 pck_log.write_log(' ','***** TRANSFORM TRANSFORM TRANSFORM TRANSFORM TRANSFORM TRANSFORM *****'); -- DUPLICATES THE LAST ITERATION AND THE CORRESPONDING SCREEN SCHEDULE
433 IF p_duplicate_last_iteration THEN
434 duplicate_last_iteration(SYSDATE);
435 END IF;
436
437 -- CLEANS ALL _clean TABLES
438 pck_log.write_log(' Deleting old _clean tables');
439 DELETE FROM t_clean_products;
440 DELETE FROM t_clean_linesofsale;
441 DELETE FROM t_clean_stores;
442 DELETE FROM t_clean_promotions;
443 DELETE FROM t_clean_sales;
444 pck_log.write_log(' Done!');
445
446 -- FINDS THE MOST RECENTLY SCHEDULED ITERATION
447 BEGIN
448 SELECT MAX(iteration_key)
449 INTO v_iteration_key
450 FROM t_tel_iteration;
451 EXCEPTION
452 WHEN OTHERS THEN
453 RAISE e_transformation;
454 END;
455
456 -- RUNS ALL THE SCHEDULED SCREENS
457 -- versão estática
458 /* FOR rec IN scheduled_screens_cursor(v_iteration_key) LOOP
459 IF UPPER(rec.screen_name)='SCREEN_PRODUCT_DIMENSIONS' THEN
460 screen_dimensions(v_iteration_key, rec.source_key, rec.screen_order);
461 ELSIF UPPER(rec.screen_name)='SCREEN_NULL_LIQ_WEIGHT' THEN
462 screen_null_liq_weight(v_iteration_key, rec.source_key, rec.screen_order);
463 END IF;*/
464 -- EXECUÇÃO DINÂMICA DE SCREENS
465 FOR rec IN scheduled_screens_cursor(v_iteration_key) LOOP
466 v_sql:='BEGIN pck_transform.'||rec.screen_name||'('||v_iteration_key||','||rec.source_key||','||rec.screen_order||'); END;';
467 -- pck_log.write_log(v_sql);
468 EXECUTE IMMEDIATE v_sql;
469 END LOOP;
470
471 -- UPDATES TABLE "T_TEL_ITERATION"
472 UPDATE t_tel_iteration
473 SET iteration_end_date = SYSDATE,
474 iteration_duration_real=(SYSDATE-iteration_start_date)/86400
475 WHERE iteration_key = v_iteration_key;
476
477 pck_log.write_log(' All screens have been run.');
478
479 -- EXECUTES THE TRANSFORMATION ROUTINES
480 transform_customers;
481 transform_products;
482 transform_stores;
483 transform_sales;
484 transform_linesofsale;
485 transform_promotions;
486 transform_celsius;
487
488 COMMIT;
489 pck_log.write_log(' All transformed data commited to database.');
490 EXCEPTION
491 WHEN e_transformation THEN
492 pck_log.write_halt_msg;
493 ROLLBACK;
494 WHEN OTHERS THEN
495 ROLLBACK;
496 pck_log.write_uncomplete_task_msg;
497 pck_log.write_halt_msg;
498 END;
499
500end pck_transform;