Untitled

CREATE OR REPLACE PROCEDURE  sp_qc_persona_volumes_report()
    RETURNS INTEGER
    LANGUAGE NZPLSQL
AS BEGIN_PROC
--########################################################################
-- Stored Procedure Name :    sp_qc_persona_volumes_report
--
-- Description           :    QC report for dim_emea_personas
--
-- Input parameter       :    None
--
-- Returns               :    0 if successful
--                            255 if any errors
--
-- Called from            :   Tidal
--
-- Created By             :   Blues Mu
-- Date                   :   2018-07-05
--
-- Mod log                :

--########################################################################
DECLARE
    vTaskComment    VARCHAR(500);
    --users_rec record;
       users_rec record AS spqcpersona;  --Migration for loop
/* [COMPATIBILITY_ISSUE] "RECORD TYPE SUPPORT IS LIMITED". Line number in original file:33801 */
    v_sql VARCHAR(8000);
    v_sql_dyn VARCHAR(8000);
    v_create_dt DATETIME;
    v_batch_date DATETIME;
    v_date DATETIME;
    v_pre_rak INTEGER;
    v_rak INTEGER;
    v_pre_object VARCHAR(512);
    v_object VARCHAR(512);
    v_column_name VARCHAR(100);
    v_count INTEGER;
    v_column_str VARCHAR(6000);
    vEndNow             DATETIME;
    vBeginNow           DATETIME;

BEGIN
vBeginNow       := (SELECT now() FROM SYSIBM.SYSDUMMY1 );
v_create_dt :=now();
/* [COMPATIBILITY_NOTE] "ANOTHER PROCEDURE BEING CALLED". Line number in original file:33819 */
v_column_str := '';

SELECT MAX(batch_date)
INTO v_batch_date
FROM meta_ctl_batch;


DELETE FROM qc_persona_volumes_summary
WHERE batch_date = v_batch_date;


vTaskComment    := 'create qc_persona_volumes_current table';

CALL sp_reset_tbl_if_exists('qc_persona_volumes_current'); /* Migration/sp_drop_if_exists replaced by sp_reset_tbl_if_exists(Reset Table instead of dropping it)*/

--call sp_drop_if_exists('qc_persona_volumes_current');

/* [COMPATIBILITY_NOTE] "ANOTHER PROCEDURE BEING CALLED". Line number in original file:33834 */
/*create table qc_persona_volumes_current
(
 reporting_order INTEGER,
 reporting_column VARCHAR(500),
 data_value VARCHAR(255),
 output VARCHAR(100)
)
distribute on random
;*/

INSERT INTO qc_persona_volumes_current
SELECT 1 AS reporting_order, 'Total_rows' AS reporting_column, 'COUNT OF' AS data_value, COUNT(1) AS output FROM dim_emea_personas
UNION ALL
SELECT 2 AS reporting_order, 'Count_distinct_dim_plat_person_id' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas
UNION ALL
SELECT 3 AS reporting_order, 'Total_P01 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P01 = 1
UNION ALL
SELECT 4 AS reporting_order, 'Total_P02 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P02 = 1
UNION ALL
SELECT 5 AS reporting_order, 'Total_P03 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P03 = 1
UNION ALL
SELECT 6 AS reporting_order, 'Total_P04 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P04 = 1
UNION ALL
SELECT 7 AS reporting_order, 'Total_P05 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P05 = 1
UNION ALL
SELECT 8 AS reporting_order, 'Total_P06 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P06 = 1
UNION ALL
SELECT 9 AS reporting_order, 'Total_P07 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P07 = 1
UNION ALL
SELECT 10 AS reporting_order, 'Total_P08 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P08 = 1
UNION ALL
SELECT 11 AS reporting_order, 'Total_P09 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P09 = 1
UNION ALL
SELECT 12 AS reporting_order, 'Total_P10 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P10 = 1
UNION ALL
SELECT 13 AS reporting_order, 'Total_P11 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P11 = 1
UNION ALL
SELECT 14 AS reporting_order, 'Total_P12 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P12 = 1
UNION ALL
SELECT 15 AS reporting_order, 'Total_P13 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P13 = 1
UNION ALL
SELECT 16 AS reporting_order, 'Total_P14 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P14 = 1
UNION ALL
SELECT 17 AS reporting_order, 'Total_P15 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P15 = 1
UNION ALL
SELECT 18 AS reporting_order, 'Total_P16 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P16 = 1
UNION ALL
SELECT 19 AS reporting_order, 'Total_P17 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P17 = 1
UNION ALL
SELECT 20 AS reporting_order, 'Total_P18 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P18 = 1
UNION ALL
SELECT 21 AS reporting_order, 'Total_P19 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P19 = 1
UNION ALL
SELECT 22 AS reporting_order, 'Total_P20 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P20 = 1
UNION ALL
SELECT 23 AS reporting_order, 'Total_P21 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P21 = 1
UNION ALL
SELECT 24 AS reporting_order, 'Total_P22 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P22 = 1
UNION ALL
SELECT 25 AS reporting_order, 'Total_P23 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P23 = 1
UNION ALL
SELECT 26 AS reporting_order, 'Total_P24 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P24 = 1
UNION ALL
SELECT 27 AS reporting_order, 'Total_P25 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P25 = 1
UNION ALL
SELECT 28 AS reporting_order, 'Total_P26 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P26 = 1
UNION ALL
SELECT 29 AS reporting_order, 'Total_P27 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P27 = 1
UNION ALL
SELECT 30 AS reporting_order, 'Total_P28 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P28 = 1
UNION ALL
SELECT 31 AS reporting_order, 'Total_P29 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P29 = 1
UNION ALL
SELECT 32 AS reporting_order, 'Total_P30 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P30 = 1
UNION ALL
SELECT 33 AS reporting_order, 'Total_P31 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P31 = 1
UNION ALL
SELECT 34 AS reporting_order, 'Total_P32 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P32 = 1
UNION ALL
SELECT 35 AS reporting_order, 'Total_P33 ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P33 = 1
UNION ALL
SELECT 36 AS reporting_order, 'Total_MAPPED ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas
    WHERE    P01 = 1   OR P02 = 1  OR P03 = 1  OR P04 = 1  OR P05 = 1  OR P06 = 1  OR P07 = 1  OR P08 = 1  OR P09 = 1  OR P10 = 1  OR P11 = 1  OR P12 = 1  OR P13 = 1  OR P14 = 1  OR P15 = 1  OR P16 = 1  OR P17 = 1  OR P18 = 1  OR P19 = 1  OR P20 = 1  OR P21 = 1  OR P22 = 1  OR P23 = 1  OR P24 = 1  OR P25 = 1  OR P26 = 1  OR P27 = 1  OR P28 = 1  OR P29 = 1  OR P30 = 1  OR P31 = 1  OR P32 = 1  OR P33 = 1
UNION ALL
SELECT 37 AS reporting_order, 'Total_UNMAPPED ' AS reporting_column, 'COUNT DISTINCT' AS data_value, COUNT(DISTINCT dim_plat_person_id) AS output FROM dim_emea_personas WHERE  P34 = 1
;


vTaskComment    := 'insert into qc_persona_volumes_summary table';
INSERT INTO qc_persona_volumes_summary
SELECT
    (SELECT MAX(batch_date) FROM meta_ctl_batch) AS batch_date,
    reporting_order,
    reporting_column,
    nvl(data_value,'') AS data_value,
    output,
    v_create_dt AS create_dt,
    CURRENT_USER AS create_user
FROM qc_persona_volumes_current a
;

vTaskComment    := 'create tmp_create_dt_rank_1 table';

--call sp_drop_if_exists('tmp_create_dt_rank_1');
CALL sp_reset_tbl_if_exists('tmp_create_dt_rank_1'); /* Migration/sp_drop_if_exists replaced by sp_reset_tbl_if_exists(Reset Table instead of dropping it)*/


/* [COMPATIBILITY_NOTE] "ANOTHER PROCEDURE BEING CALLED". Line number in original file:33937 */
--create table tmp_create_dt_rank_1 as /*Migration/Create statement is replaced by Insert to insert data after creating TABLE */
INSERT INTO tmp_create_dt_rank_1(create_dt,rak)
(SELECT create_dt, rank() OVER(ORDER BY create_dt DESC) AS rak
FROM (
    SELECT DISTINCT create_dt
    FROM qc_persona_volumes_summary
)a) --WITH DATA
;
/* [COMPATIBILITY_NOTE] "OLAP FUNCTION RESTRICTION". Line number in original file:33939 */

vTaskComment    := 'create tmp_qc_report_persona_volumes_rak_0 table';

CALL sp_reset_tbl_if_exists('tmp_qc_report_persona_volumes_rak_0'); /* Migration/sp_drop_if_exists replaced by sp_reset_tbl_if_exists(Reset Table instead of dropping it)*/

--call sp_drop_if_exists('tmp_qc_report_persona_volumes_rak_0');

/* [COMPATIBILITY_NOTE] "ANOTHER PROCEDURE BEING CALLED". Line number in original file:33946 */
--create table tmp_qc_report_persona_volumes_rak_0 as /*Migration/Create statement is replaced by Insert to insert data after creating TABLE */

INSERT INTO tmp_qc_report_persona_volumes_rak_0
(SELECT
    DISTINCT
    reporting_order, reporting_column, data_value
FROM qc_persona_volumes_summary) --WITH DATA
;


vTaskComment    := 'begin Loop tmp_create_dt_rank_1 table ';
v_sql := 'select create_dt, rak, ''date_''||to_char(create_dt,''YYYYMMDD'') as column_name from tmp_create_dt_rank_1 where rak<13 order by rak;';

FOR users_rec IN EXECUTE v_sql
LOOP
    v_date := users_rec.create_dt;
    v_pre_rak := users_rec.rak - 1;
    v_rak := users_rec.rak;
    v_column_name=users_rec.column_name;
    vTaskComment    := 'v_object assignment';
    v_pre_object='tmp_qc_report_persona_volumes_rak_'||v_pre_rak;
    v_object='tmp_qc_report_persona_volumes_rak_'||v_rak;

EXECUTE IMMEDIATE 'sp_drop_if_exists('||quote_literal(v_object)||');';


vTaskComment    := 'create tmp rank table';

v_sql_dyn='create table '||v_object||' as
            select a.*, b.output as '||v_column_name||'
            from '||v_pre_object||' a
            left join qc_persona_volumes_summary b
            on
            a.reporting_column=b.reporting_column
            and a.data_value=b.data_value
            and b.create_dt='''||v_date||'''
            ;';

EXECUTE IMMEDIATE v_sql_dyn;

EXECUTE IMMEDIATE 'EXECUTE sp_drop_if_exists('||quote_literal(v_pre_object)||');';

END LOOP;

CALL sp_reset_tbl_if_exists('qc_persona_volumes_report');
--call sp_drop_if_exists('qc_persona_volumes_report');

/* [COMPATIBILITY_NOTE] "ANOTHER PROCEDURE BEING CALLED". Line number in original file:33988 */

EXECUTE IMMEDIATE 'alter table '||v_object||' rename to qc_persona_volumes_report;';


vTaskComment := 'Insert Job Control Process Table and cleanup';
-------------------------------migration---------------
--drop tmp table
CALL sp_reset_tbl_if_exists('qc_persona_volumes_current'); /* Migration/sp_drop_if_exists replaced by sp_reset_tbl_if_exists(Reset Table instead of dropping it)*/

--call sp_drop_if_exists('qc_persona_volumes_current');

/* [COMPATIBILITY_NOTE] "ANOTHER PROCEDURE BEING CALLED". Line number in original file:33997 */
CALL sp_reset_tbl_if_exists('tmp_create_dt_rank_1'); /* Migration/sp_drop_if_exists replaced by sp_reset_tbl_if_exists(Reset Table instead of dropping it)*/

--call sp_drop_if_exists('tmp_create_dt_rank_1');

/* [COMPATIBILITY_NOTE] "ANOTHER PROCEDURE BEING CALLED". Line number in original file:33998 */
CALL sp_reset_tbl_if_exists('tmp_qc_report_persona_volumes_rak_0'); /* Migration/sp_drop_if_exists replaced by sp_reset_tbl_if_exists(Reset Table instead of dropping it)*/

--call sp_drop_if_exists('tmp_qc_report_persona_volumes_rak_0');

/* [COMPATIBILITY_NOTE] "ANOTHER PROCEDURE BEING CALLED". Line number in original file:33999 */

vEndNow :=(SELECT now() FROM SYSIBM.SYSDUMMY1 );
CALL sp_log_batch_job('INSERT','SMB NA',-1,'sp_qc_persona_volumes_report','SMB',vBeginNow,-1,vEndNow,-1,'DONE','',0,0);
/* [COMPATIBILITY_NOTE] "ANOTHER PROCEDURE BEING CALLED". Line number in original file:34002 */

RETURN 0;

EXCEPTION WHEN OTHERS THEN
raise exception 'SP FAILED DUE TO ERROR %. Location: %', SQLERRM, vTaskComment;
RETURN 255;
END;

END_PROC