====Head====
CREATE OR REPLACE PACKAGE refreshWH
AS
-- ##############################################################
-- # PL/SQL package specification
-- ##############################################################
PROCEDURE refreshCarrierDataValidationReportWH;
PROCEDURE refreshPersonsOfInterestMatchWH;
END refreshWH;
====Body====
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;