SUMMARY OF PERSONNEL LICENCES (Transferred to Wiki)
- Sufyan Maghur
- Fabio Rodrigo Pereira
applsstat.pbl
wn_apls_full_statistics_ae WINDOW
dw_apls_lic_full_form_1 DATAWINDOW
ISSUED 12 MONTHS – Data Extraction
AEROPLANE
Medicals with no accompanying license or permit mer_new
FUNCTION fn_new_mer ( p_region IN applicant.owner_region_code%TYPE,
p_dt_sixmthsago IN hist_medical_category.date_printed%TYPE)
SELECT COUNT (*)
INTO ll_new_mer
FROM (SELECT UNIQUE hist_medical_category.file_number
FROM hist_medical_category, hist_medical
WHERE hist_medical_category.file_number =
hist_medical.file_number
AND hist_medical.hist_sequence = 1
AND hist_medical_category.medical_category_code IN
('01', '03', '04')
AND hist_medical_category.suspension_renewal_indicator =
'F'
AND hist_medical_category.deferred_unfit_indicator = 'F'
AND hist_medical_category.file_number IN
(SELECT file_number
FROM applicant
WHERE active_indicator = 'T'
AND owner_region_code = p_region)
AND hist_medical_category.date_printed >=
p_dt_sixmthsago
AND hist_medical_category.hist_sequence = 1
MINUS
SELECT /*+ USE_NL(HIST_LICENCE) */
UNIQUE hist_licence.file_number -- Quest optimizer hint here for improved performance
FROM applicant, hist_licence
WHERE hist_licence.file_number = applicant.file_number + 0 -- Quest optimizer hint here for improved performance
AND applicant.owner_region_code = p_region);
Private Pilots app_new Filter BY LICENCE TYPE = 'PA'
Commercial Pilots acp_new Filter BY LICENCE TYPE = 'CA'
Airline Transport Pilots atp_new Filter BY LICENCE TYPE = 'AA'
Multi-Crew Pilots amp_new Filter BY LICENCE TYPE = 'MA'
PROCEDURE sp_licence_in_force_cnt ( p_region IN applicant.owner_region_code%TYPE,
p_dt_current IN licence.licence_expiry_date%TYPE,
cur_IN_FORCE_CNT OUT T_CURSOR)
SELECT licence_type_code, COUNT (*)
FROM licence
WHERE licence_expiry_date >= p_dt_current AND
suspension_indicator = 'F' AND
EXISTS ( SELECT 'X'
FROM applicant
WHERE active_indicator = 'T' AND
owner_region_code = p_region AND
file_number = licence.file_number )
GROUP BY licence_type_code;
HELICOPTER
Private Pilots hpp_new Filtre BY LICENCE TYPE = 'PH'
Commercial Pilots hcp_new Filter BY LICENCE TYPE = 'CH'
Airline Transport Pilots htp_new Filter BY LICENCE TYPE = 'AH'
PROCEDURE sp_licence_in_force_cnt ( p_region IN applicant.owner_region_code%TYPE,
p_dt_current IN licence.licence_expiry_date%TYPE,
cur_IN_FORCE_CNT OUT T_CURSOR)
SELECT licence_type_code, COUNT (*)
FROM licence
WHERE licence_expiry_date >= p_dt_current AND
suspension_indicator = 'F' AND
EXISTS ( SELECT 'X'
FROM applicant
WHERE active_indicator = 'T' AND
owner_region_code = p_region AND
file_number = licence.file_number )
GROUP BY licence_type_code;
PERMITS
Gyroplane Pilot pgp_new Filter BY LICENCE TYPE = 'PG'
Ultra-light Pilot pup_new Filter BY LICENCE TYPE = 'PU'
Recreational Pilot prp_new Filter BY LICENCE TYPE = 'RP'
PROCEDURE sp_licence_in_force_cnt ( p_region IN applicant.owner_region_code%TYPE,
p_dt_current IN licence.licence_expiry_date%TYPE,
cur_IN_FORCE_CNT OUT T_CURSOR)
SELECT licence_type_code, COUNT (*)
FROM licence
WHERE licence_expiry_date >= p_dt_current AND
suspension_indicator = 'F' AND
EXISTS ( SELECT 'X'
FROM applicant
WHERE active_indicator = 'T' AND
owner_region_code = p_region AND
file_number = licence.file_number )
GROUP BY licence_type_code;
OTHER LICENCES
Glider Pilot ggp_new Filter BY LICENCE TYPE = 'GG'
Balloon Pilot bbp_new Filter BY LICENCE TYPE = 'BB'
Flight Engineers fen_new Filter BY LICENCE TYPE = 'FE'
Air Traffic Controller atc_new Filter BY LICENCE TYPE = 'TC'
PROCEDURE sp_licence_in_force_cnt ( p_region IN applicant.owner_region_code%TYPE,
p_dt_current IN licence.licence_expiry_date%TYPE,
cur_IN_FORCE_CNT OUT T_CURSOR)
SELECT licence_type_code, COUNT (*)
FROM licence
WHERE licence_expiry_date >= p_dt_current AND
suspension_indicator = 'F' AND
EXISTS ( SELECT 'X'
FROM applicant
WHERE active_indicator = 'T' AND
owner_region_code = p_region AND
file_number = licence.file_number )
GROUP BY licence_type_code;