db2_warehouse_packagev2

Head

1
2
3
4
5
6
7
8
CREATE OR REPLACE PACKAGE refreshWH
AS
    -- ##############################################################
    -- # PL/SQL package specification
    -- ##############################################################
    PROCEDURE refreshCarrierDataValidationReportWH;
    PROCEDURE refreshPersonsOfInterestMatchWH;
END refreshWH;

Body

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
CREATE OR REPLACE PACKAGE BODY refreshWH
AS
 
    -- ###################################################################################
    -- # PL/SQL package body
    -- ###################################################################################
    PROCEDURE refreshCarrierDataValidationReportWH
    AS
    cursor C1
    IS
     
    select distinct received_dt, message_type, sender_id, flight_number, departure_airport_code, arrival_airport_code
    from received a, received_passenger b
    where (a.received_id = b.received_id) and a.RECEIVED_DT > (select LAST_RECEIVED_DT from CTRLTABLE where REPORTNAME = 'CarrierDataValidationReport')
    order by received_dt;
     
    stpCount integer := 0;
    rcvDtPrcd timestamp;
    mssgTpPrcd varchar(50);
    sndrPrcd varchar(50);
    flghtPrcd varchar(50);
    dprtrArprtCd varchar(50);
    arrvlArprtCd varchar(50);
         
    BEGIN
    if((select count(reportName) from CTRLTABLE where REPORTNAME='CarrierDataValidationReport') = 0) THEN
    insert into CTRLTABLE values('CarrierDataValidationReport',TIMESTAMP_FORMAT('1950-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'),current timestamp);
    end if;
     
    open C1;
    fetch C1 into rcvDtPrcd,mssgTpPrcd,sndrPrcd,flghtPrcd,dprtrArprtCd,arrvlArprtCd;
     
    WHILE (C1%FOUND)
    LOOP
    insert into CarrierDataValidationReportWH values(rcvDtPrcd,mssgTpPrcd,sndrPrcd,flghtPrcd,dprtrArprtCd,arrvlArprtCd);
         
        if(mod(stpCount,1000) = 0) THEN
            update CTRLTABLE set (LAST_RECEIVED_DT,LATESTUPDATE) = (rcvDtPrcd,CURRENT TIMESTAMP) where reportName='CarrierDataValidationReport';
            commit work;
        end if;
     
    fetch C1 into rcvDtPrcd,mssgTpPrcd,sndrPrcd,flghtPrcd,dprtrArprtCd,arrvlArprtCd;   
    stpCount := stpCount + 1;
     
    END LOOP;
     
    update CTRLTABLE set (LAST_RECEIVED_DT,LATESTUPDATE) = ((select max(RECEIVED_DT) from CarrierDataValidationReportWH),CURRENT TIMESTAMP) where reportName='CarrierDataValidationReport';
    commit work;
    close C1;
 
     
    END refreshCarrierDataValidationReportWH;
     
     
    PROCEDURE refreshPersonsOfInterestMatchWH
    AS
    cursor c2
    IS
    SELECT      
    Scr_ID,
    ACTL_SCR,
    QUALITATIVE_SCR,
    EVENT_TYPE,
    CARRIER_CODE,
    Citizen_of_country,
    FLIGHT_NUMBER,
    CAST (DEPARTURE_DATE AS varchar (50)) || ' ' || CAST (DEPARTURE_TIME AS varchar (50)) AS DepartureDateTime,
    DEPARTURE_DATE,
    DEPARTURE_TIME,
    actual_occurance_timestamp,
    COUNTRY
    FROM apainst1.scr as scr
    left outer join apainst1.risk_inst as risk_inst on scr.risk_inst_id = risk_inst.risk_inst_id
    left outer join apainst1.case_event as case_event on case_event.case_event_id = risk_inst.case_event_id
    left outer join apainst1.M_EVENT_TYPE as M_EVENT_TYPE on case_event.EVENT_TYPE_ID = M_EVENT_TYPE.EVENT_TYPE_ID
    left outer join apainst1.party as party on case_event.party_id = party.party_id
    left outer join apainst1.person as person on party.party_id =   person.party_id
    left outer join apainst1.person_biographics as person_biographics on person.person_id = person_biographics.person_id
    left outer join apainst1.case_party_assoc as case_party_assoc on risk_inst.CASE_PARTY_ID = case_party_assoc.CASE_PARTY_ID
    left outer join apainst1.travel_info as travel_info on case_party_assoc.CASE_PARTY_ID = travel_info.CASE_PARTY_ID
    left outer join apainst1.TRAVEL_ITINERARY as TRAVEL_ITINERARY on TRAVEL_ITINERARY.TRAVEL_ID = travel_info.TRAVEL_ID
    left outer join apainst1.M_AIRPORT as M_AIRPORT on TRAVEL_ITINERARY.Departure_airport_code = M_AIRPORT.Airport_code
    where actual_occurance_timestamp > (select max(LAST_RECEIVED_DT) from CTRLTABLE where REPORTNAME='PersonsOfInterestMatchReport')
    order by Scr_ID;
     
     
    stpCount integer := 0;
    scrD bigint;
    ctlSCR integer;
    qulttvSCR varchar(50);
    vntTP varchar(50);
    crrCD varchar(50);
    ctznFCntr varchar(50);
    flghtNmbr varchar(50);
    dprtrTm varchar(50);
    dprtrDt date;
    dprtrTmv2 time;
    ctlCcrncTmst timestamp;
    cntr varchar(50);
 
    BEGIN
     
    if((select count(reportName) from CTRLTABLE where REPORTNAME='PersonsOfInterestMatchReport') = 0) THEN
    insert into CTRLTABLE values('PersonsOfInterestMatchReport',TIMESTAMP_FORMAT('1950-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'),current timestamp);
    end if;
     
     
    open C2;
    fetch C2 into scrD,ctlSCR,qulttvSCR,vntTP,crrCD,ctznFCntr,flghtNmbr,dprtrTm,dprtrDt,dprtrTmv2,ctlCcrncTmst,cntr;
     
    WHILE (C2%FOUND)
    LOOP
    insert into PersonsOfInterestMatchReportWH values(scrD,ctlSCR,qulttvSCR,vntTP,crrCD,ctznFCntr,flghtNmbr,dprtrTm,dprtrDt,dprtrTmv2,ctlCcrncTmst,cntr);
         
        if(mod(stpCount,1000) = 0) THEN
            update CTRLTABLE set (LAST_RECEIVED_DT,LATESTUPDATE) = (ctlCcrncTmst,CURRENT TIMESTAMP) where reportName='PersonsOfInterestMatchReport';
            commit work;
        end if;
     
    fetch C2 into scrD,ctlSCR,qulttvSCR,vntTP,crrCD,ctznFCntr,flghtNmbr,dprtrTm,dprtrDt,dprtrTmv2,ctlCcrncTmst,cntr;
    stpCount := stpCount + 1;
     
    END LOOP;
 
    update CTRLTABLE set (LAST_RECEIVED_DT,LATESTUPDATE) = ((select max(ACTUAL_OCCURANCE_TIMESTAMP) from PersonsOfInterestMatchReportWH),CURRENT TIMESTAMP) where reportName='PersonsOfInterestMatchReport';
    commit work;
    close C2;
 
    end refreshPersonsOfInterestMatchWH;
     
END;

  • db2_warehouse_packagev2.txt
  • Last modified: 2019/10/18 20:04
  • by 127.0.0.1