Package Head

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

Package 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_passanger 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);
			commit work;
		end if;
	
	fetch C1 into rcvDtPrcd,mssgTpPrcd,sndrPrcd,flghtPrcd,dprtrArprtCd,arrvlArprtCd;	
	stpCount := stpCount + 1;
	
	END LOOP;
	
	update CTRLTABLE set (LAST_RECEIVED_DT,ROWCOUNT,LATESTUPDATE) = ((select max(RECEIVED_DT) from CarrierDataValidationReportWH),CURRENT TIMESTAMP);
	commit work;
	close C1;

 
	END WHILE;
	
end;