Notes

create or replace package body pck_load is

e_load EXCEPTION;



PROCEDURE init_dimensions IS
BEGIN
pck_log.write_log(' Initializing all dimensions with "invalid" records');
-- 'INVALID AULA'
INSERT INTO t_dim_aula_semana (
aula_semana_key,aula_semana_natural_key, semana, hora_inicio, hora_fim, sala, dia, mes, ano_civil, prof_marcou_presenca, num_presencas, aula_cancelada)
VALUES (pck_error_codes.c_load_invalid_dim_record_key, pck_error_codes.c_load_invalid_dim_record_Nkey,NULL,'INVALID',NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-- 'INVALID DATE'
INSERT INTO t_dim_date (date_key,date_full_date,date_month_full,date_month_name,date_month_short_name,date_month_nr,date_quarter_nr,date_quarter_full,date_semester_nr,date_semester_full,date_event,date_year, date_day_nr,date_is_holiday)
VALUES (pck_error_codes.c_load_invalid_dim_record_key, 'INVALID',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-- 'INVALID TIME'
INSERT INTO t_dim_time (time_key,time_full_time,time_period_of_day,time_minutes_after_midnight,time_hour_nr,time_minute_nr,time_second_nr)
VALUES (pck_error_codes.c_load_invalid_dim_record_key, 'INVALID',NULL,NULL,NULL,NULL,NULL);

-- 'INVALID TURNO'
INSERT INTO T_DIM_TURNO (TURNO_KEY,TURNO_NATURAL_KEY,TURNO_UC,REGIME_UC,MAX_ALUNOS,INSCRITOS)
VALUES (pck_error_codes.c_load_invalid_dim_record_key, pck_error_codes.c_load_invalid_dim_record_Nkey,'INVALID TURNO',NULL,NULL,NULL;

-- 'INVALID UC'
INSERT INTO T_DIM_UC (UC_KEY, UC_NATURAL_KEY, NOME_UC, ABREV_UC, ANO_UC,SEMESTRE_UC,RAMO_UC,DEPARTAMENTO_UC,AREA_CIENTIFICA_UC)
VALUES (pck_error_codes.c_load_invalid_dim_record_key, pck_error_codes.c_load_invalid_dim_record_Nkey, 'INVALID UC', NULL, NULL, NULL, NULL, NULL, NULL);

-- 'INVALID USER'
INSERT INTO T_DIM_UC (USER_KEY, USER_NATURAL_KEY, REGIME, RAMO, TEMPORARIO, HASHORARIOCOMPLETO)
VALUES (pck_error_codes.c_load_invalid_dim_record_key, pck_error_codes.c_load_invalid_dim_record_Nkey, 'INVALID USER', NULL, NULL, NULL, NULL);

pck_log.write_log(' Done!');
EXCEPTION
WHEN OTHERS THEN
pck_log.write_uncomplete_task_msg;
RAISE e_load;
END;

--- *** DISCLAIMER ***
--- ESTÁ A SER USADO MERGE PARA INSERIR ROWS NAS TABLES PARA TER EM CONTA INSERÇÕES REPETIDAS PARA
--- NÃO REBENTAR NOS PK_CONSTRAINTS
--- NOTA: CORRIGIR ESPARGUETE QUE AQUI ESTÁ E VER SE VALE A PENA USAR LEFT JOIN OU INSERT EXCEPT EM VEZ DE MERGE





PROCEDURE LOAD_DIM_AULA_SEMANA IS
BEGIN
pck_log.write_log(' Loading data ["LOAD_DIM_AULA_SEMANA"]');
pck_log.rowcount('t_dim_aula_semana','Before');

merge into t_dim_aula_semana dim using T_CLEAN_AULAS_SEMANA cl
on (dim.aula_semana_natural_key = clean.id)
when matched then
update set
dim.semana = cl.semana,
dim.hora_inicio = cl.horainicio,
dim.hora_fim = cl.horafim
dim.sala = cl.sala
dim.dia = cl.dia
dim.mes = cl.mes
dim.ano_civil = cl.ano_civil
dim.prof_marcou_presenca = cl.prof_marcou_presenca
dim.num_presencas = cl.num_presencas
dim.aula_cancelada = cl.aula_cancelada
when not matched then
insert (aula_semana_key,auka_semana_natural_key,semana,hora_inicio,
hora_fim,sala,dia,mes,ano_civil,
prof_marcou_presenca,num_presencas,aula_cancelada)
values(seq_dim_aula.NEXT_VAL,cl.id,cl.semana,cl.horainicio,cl.horafim,cl.sala,cl.dia,cl.mes,cl.ano_civil
cl.prof_marcou_presenca,cl.num_presencas,cl.aula_cancelada);



pck_log.rowcount('t_dim_promotion','After');
pck_log.write_log(' Done!');
EXCEPTION
WHEN OTHERS THEN
pck_log.write_uncomplete_task_msg;
RAISE e_load;
END;


PROCEDURE LOAD_DIM_TURNO IS
pck_log.write_log(' Loading data ["LOAD_DIM_TURNO"]');
pck_log.rowcount('t_dim_aula_semana','Before');


MERGE INTO T_DIM_TURNO dim USING T_CLEAN_TURNOS clean
ON (dim.turno_natural_key = clean.id)
WHEN MATCHED THEN
UPDATE SET
dim.turno_tipo = clean.turnotipo,
dim.turno_regime = clean.turnoregime,
dim.max_alunos = clean.max_alunos,
dim.inscritos = clean.inscritos
WHEN NOT MATCHED THEN
INSERT (TURNO_KEY, TURNO_NATURAL_KEY, TURNO_TIPO, TURNO_REGIME, MAX_ALUNOS, INSCRITOS)
VALUES (seq_dim_turno.NEXTVAL, clean.id, clean.turnotipo, clean.turnoregime, clean.max_alunos, clean.inscritos)

pck_log.rowcount('t_dim_aula_semana','After');
pck_log.write_log(' Done!');
EXCEPTION
WHEN OTHERS THEN
pck_log.write_uncomplete_task_msg;
RAISE e_load;
END;






PROCEDURE LOAD_DIM_UC IS
pck_log.write_log(' Loading data ["LOAD_DIM_UC"]');
pck_log.rowcount('t_dim_aula_semana','Before');

MERGE INTO T_DIM_UC dim USING T_CLEAN_UCS clean
ON (dim.uc_natural_key = clean.id )
WHEN MATCHED THEN
UPDATE SET
dim.nome_uc = clean.nomeuc,
dim.abrev_uc = clean.abrevuc,
dim.ano_uc = clean.anouc,
dim.semestre_uc = clean.semestreuc,
dim.ramo_uc = clean.ramouc,
dim.departamento_uc = clean.departamento,
dim.area_cientifica_uc = clean.areacientifica
WHEN NOT MATCHED THEN
INSERT (UC_KEY, UC_NATURAL_KEY, NOME_UC, ABREV_UC, ANO_UC, SEMESTRE_UC, RAMO_UC, DEPARTAMENTO_UC, AREA_CIENTIFICA_UC)
VALUES (seq_dim_uc.NEXTVAL, clean.id, clean.nomeuc, clean.abrevuc, clean.anouc, clean.semestreuc, clean.ramouc, clean.departamento, clean.areacientifica)



pck_log.rowcount('t_dim_aula_semana','After');
pck_log.write_log(' Done!');
EXCEPTION
WHEN OTHERS THEN
pck_log.write_uncomplete_task_msg;
RAISE e_load;
END;






PROCEDURE LOAD_DIM_USER IS
pck_log.write_log(' Loading data ["LOAD_DIM_USER"]');
pck_log.rowcount('t_dim_aula_semana','Before');

MERGE INTO


pck_log.rowcount('t_dim_aula_semana','After');
pck_log.write_log(' Done!');
EXCEPTION
WHEN OTHERS THEN
pck_log.write_uncomplete_task_msg;
RAISE e_load;

END;






PROCEDURE LOAD_DIM_DATE IS
pck_log.write_log(' Loading data ["LOAD_DIM_DATE"]');
pck_log.rowcount('t_dim_aula_semana','Before');



pck_log.rowcount('t_dim_aula_semana','After');
pck_log.write_log(' Done!');
EXCEPTION
WHEN OTHERS THEN
pck_log.write_uncomplete_task_msg;
RAISE e_load;

END;






PROCEDURE LOAD_DIM_TIME IS
pck_log.write_log(' Loading data ["LOAD_DIM_TIME"]');
pck_log.rowcount('t_dim_aula_semana','Before');




pck_log.rowcount('t_dim_aula_semana','After');
pck_log.write_log(' Done!');
EXCEPTION
WHEN OTHERS THEN
pck_log.write_uncomplete_task_msg;
RAISE e_load;

END;




PROCEDURE LOAD_FACT_PRESENCA IS
pck_log.write_log(' Loading data ["LOAD_FACT_PRESENCA"]');
pck_log.rowcount('t_dim_aula_semana','Before');






pck_log.write_log(' '||SQL%ROWCOUNT ||' fact(s) loaded',' Done!');
EXCEPTION
WHEN NO_DATA_FOUND THEN
pck_log.write_log(' No facts generated from '||v_source_lines||' source PRESENCAS');
WHEN OTHERS THEN
pck_log.write_uncomplete_task_msg;
RAISE e_load;
END;


PROCEDURE LOAD_FACT_INSCRITO IS
pck_log.write_log(' Loading data ["LOAD_FACT_INSCRITO"]');
pck_log.rowcount('t_dim_aula_semana','Before');






pck_log.write_log(' '||SQL%ROWCOUNT ||' fact(s) loaded',' Done!');
EXCEPTION
WHEN NO_DATA_FOUND THEN
pck_log.write_log(' No facts generated from '||v_source_lines||' source INSCRICOES');
WHEN OTHERS THEN
pck_log.write_uncomplete_task_msg;
RAISE e_load;
END;







PROCEDURE main (p_load_dates BOOLEAN,
p_init_dimensions BOOLEAN) IS
END;

end pck_load;