CREATE OR REPLACE PACKAGE refreshWH
AS
	-- ##############################################################
	-- # PL/SQL package specification
	-- ##############################################################
	PROCEDURE refreshCarrierDataValidationReportWH;
	PROCEDURE refreshPersonsOfInterestMatchWH;
END refreshWH;

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;