====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;