applsstat.pbl

wn_apls_full_statistics_ae            WINDOW
dw_apls_lic_full_form_1               DATAWINDOW

Open

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;