Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Publishing to acceptance and production

CAWIS External: use the Staging profile to publish to acceptance and production. It does not contain the admin modules. Using the incorrect profile will expose protected B info.

CAWIS Internal: use the Acc profile to publish to acceptance and production

Refer to the following document:
CAWIS EXT - VS - CAWIS INT - BSD - Civil Aviation - Confluence (atlassian.net)

Updated Messages, Labels, etc.

The scripts are backed up to: \\Tc4s0a\groups\AARA\AARAD\CAWIS\Developer_Documents\_backups\317538_CAWIS_Notification_Emails_RDIMS_Link

AAIR Accounts on development and acceptance:

SCENARIO

USER ID

Mark (tailmark)

Password for all accounts is Clark2005! for development and acceptance

005256

Single Aircraft

005197

Owner not reported in years

596873

Regular user

014885 

Fleet

558885 

FALE

Fleet

003530

One of them is GDCL

Goes directly to Modify mode without having to press a Modify button.

004084

007278

...

Code Block
SET DEFINE OFF;

update CAWIS.A42_EMAIL_CONTENT
set 

EMAIL_CONTENT_NAME_NM = 'AAIR_ACCESS_CODE_REQUEST',
EMAIL_FROM_TXT = 'AAIR/RAINA(TC)<tc.aair-raina.tc@tc.gc.ca>',
EMAIL_SUBJECT_ETXT = 'AAIR Notification - Forgotten User ID or Password',
EMAIL_SUBJECT_FTXT = 'Notification du RAINA - ID utilisateur ou mot de passe oublié',
LAST_MOD_BY_USER_ID ='ADMIN',
IS_IMPORTANT_IND = 'N',
LAST_MOD_TIMESTAMP_DTE = SYSDATE,
DATE_CURRENT_DTE = SYSDATE,

EMAIL_EBODY_LOB = 

'<!DOCTYPE html>
<html lang="en">
<head><title>AAIR Notification - Forgotten<p>Good day, [%1%],</p>

<p>Your User ID is [%6%]. Please record this User ID or Password</title>
<meta name="viewport" content="width=device-width,initial-scale=1" /><meta name="dc.title" content="CAWIS V3.0" /><meta name="dc.language" content="eng" /><meta name="dc.subject" content="CAWIS V3.0" /><meta name="dc.creator" content="Application Development and Technology Management" /><meta name="dcterms.modified" content="2014-03-01" /><meta name="dcterms.issued" content="2014-03-01" />
<script src="src/common.js"></script>
    <link href="Styles/cawis.css" rel="stylesheet" type="text/css" />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
        <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css" />
</head>

<body>
<p>Good day, [%1%],</p>

<p>Your User ID is [%6%]. Please record this User ID as it will remain the same each year.</p> 

Please click the link to log into the AAIR portal:<br />
https://catcappsx.tc.gc.ca/Saf-Sec-Sur/2/cawis-swimn/a_l.aspx?a=&lang=eng<br />

<br />
If you have forgotten your password, are a newly registered aircraft owner or are new to this AAIR portal, please create a new password by clicking the link below:<br />
https://catcappsx.tc.gc.ca/Saf-Sec-Sur/2/CAWIS-SWIMN/ForPss.aspx?m=A&lang=eng&hsh=[%2%]<br />

<p>Please be aware that:</p>
<ul>
<li> The password link is valid for the next 24 hours from the receipt of this email;</li>
<li> The password link can only be used once;</li>
<li> This request nullifies any previous request; </li>
<li> If you''re having trouble with the link, copy and paste it into your web browser.</li>
</ul>

<p>For security reasons, this request was received at [%5%]. If you did not initiate this request,
please notify the AAIR team in writing at <a href="mailto:tc.aair-raina.tc@tc.gc.ca">tc.aair-raina.tc@tc.gc.ca</a>.</p>

<p>Thank you,</p>

<p>The AAIR team</p>

</body>

</html>',


EMAIL_FBODY_LOB = 
'<!DOCTYPE html>
<html lang="fr">
<head><title>Notification du RAINA - ID utilisateur ou mot de passe oublié</title>
<meta name="viewport" content="width=device-width,initial-scale=1" /><meta name="dc.title" content="CAWIS V3.0" /><meta name="dc.language" content="eng" /><meta name="dc.subject" content="CAWIS V3.0" /><meta name="dc.creator" content="Application Development and Technology Management" /><meta name="dcterms.modified" content="2014-03-01" /><meta name="dcterms.issued" content="2014-03-01" />
<script src="src/common.js"></script>
    <link href="Styles/cawis.css" rel="stylesheet" type="text/css" />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
        <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css" />
</head>

<body>

<p>Bonjour, [%1%],</p>

<p>Votre identifiant d’utilisateur est [%6%]. Veuillez noter votre identifiant d’utilisateur car il restera le même chaque année.</p>

Cliquez sur le lien suivant pour vous connecter au portail RAINA :<br />
https://catcappsx.tc.gc.ca/Saf-Sec-Sur/2/cawis-swimn/a_l.aspx?a=&lang=fra<br />

<br />
Si vous avez oublié votre mot de passe, si vous êtes un propriétaire d''aéronef nouvellement enregistré ou si vous êtes nouveau sur ce portail RAINA, veuillez créer un nouveau mot de passe en cliquant sur le lien ci-dessous :<br />
https://catcappsx.tc.gc.ca/Saf-Sec-Sur/2/CAWIS-SWIMN/ForPss.aspx?m=A&lang=fra&hsh=[%2%]<br />

<p>Sachez que:</p>
<ul>
<li> Le lien vers le mot de passe est valable pendant les 24 heures qui suivent la réception de ce courriel;</li>
<li> Ce lien vers le mot de passe ne peut être utilisé qu''une seule fois;</li>
<li> Cette requête annule toute requête précédente;</li>
<li> Si vous rencontrez des problèmes avec le lien, copiez-le et collez-le dans votre navigateur Web.</li>
</ul>

<p>Pour des raisons de sécurité, cette requête a été reçue à [%5%]. Si vous n''êtes pas à l''origine de la demande,
veuillez en informer l''équipe RAINA par écrit à l''adresse suivante :  <a href="mailto:tc.aair-raina.tc@tc.gc.ca">tc.aair-raina.tc@tc.gc.ca</a>.</p>

<p>Merci,</p>

<p>L''équipe RAINA</p>
 
    
</body>
</html>'

where EMAIL_CONTENT_ID = '44';

commit;

Configuration:

...

Info

The setting COPY EMAIL ADDRESSES should be set to the current product owner on Acceptance and Production. See the script provided below.

Code Block
languagesql
--Acceptance and Production
--Update the EMAIL COPY ADDRESSES (an AAIR setting) to current product owner.
UPDATE TR47_CAWIS_GENERAL_PROPS
SET PROPERTY_VALUE = 'GENEVIEVE.BOURGEOIS@TC.GC.CA',
LAST_MOD_TIMESTAMP_DTE = SYSDATE,
LAST_MOD_BY_USER_ID = 'ADMIN'
WHERE      UPPER(PROPERTY_NAME) = UPPER('EMAIL COPY ADDRESSES');

Shared Mailboxes

  • The following shared accounts are applicable on both development and acceptance. All members of the maintenance team have access:

    1. On development, the email address for these shared accounts is set to our shared CAWIS development mailbox.

    2. On acceptance the address is: cawis-acc-swimn@tc.gc.ca

    3. On production the address is: tc.aair-raina.tc@tc.gc.ca

See: Shared Mailbox Mapping Procedure

Updated texts for AAIR Owner form with contact info only (short form)

...

Updated contact PDFs are posted to dev and acc, were also deployed to Production

...

EXTERNAL ACC - \\wwwapps2test\wwwappsroot\Saf-Sec-Sur\2\AWD-CN\documents

INTERNAL DEV - \\ncrws499\catcappsx\Saf-Sec-Sur\2\AWD-CN\documents

INTERNAL ACC - \\tcapps2test\tcappsroot\saf-sec-sur\2\AWD-CN\documents

Business Rules:

see also

  • AIRCRAFT CANNOT BE SALE REPORTED OR INVALID ADDRESS       

  • THE AIRCRAFT CANNOT BE A MEMBER OF A FLEET                 

  • THE AIRCRAFT CANNOT BE LISTED AS OUT-OF-SERVICE           

  • THE AIRCRAFT CANNOT HAVE HOURS ALREADY RECORDED FOR THE REPORTING PERIOD (CURRENT YEAR - 1) 

  • THE AIRCRAFT CANNOT BE LISTED WITH A YEAR OF MANUFACTURE EQUAL TO THE CURRENT YEAR

  • THE AIRCRAFT CANNOT BE ULTRALIGHT/ADVANCED ULTRALIGHT     

  • THE AIRCRAFT CANNOT BE DEREGISTERED

Creating an AAIR Account:

  • Login to CAWIS Internal as a TC User.

  • To obtain current tail marks run the following report: 5008 Files by Owner.

...

  • Select 5008 Files from the left-side menu.

  • Do a quick search by tail mark:

    • type in 2 letters - for example "FE"

  • Change email address to one that you can access.

  • Save.

  • Logout.

  • On the external website, Login AAIR

  • Click forgot user id or password.

    • It will ask for the tail mark and email address that you changed in the 5008

  • CAWIS will send you an email with a link and click open link

  • It will ask to create a password

  • Save

  • Take note of the client id in the email.

  • Login AAIR: client id & password (that you created)

AAIR info from October 2023 discussions onward:

...

TC login uses the TC login ID but the password is not the same.

...

For TC Login, the same credential works on both CAWIS internal and external. 

...

I have provided access for the TC Login option on CAWIS development and acceptance to all members of the maintenance team. 

...

Maintenance team have access to CAWIS prod for maintenance purposes only (Only for work related to tasks submitted by the client. No changes can be made without approval from the client)

...

AAIR does not use our TC Login credentials.

...

AAIR login is unlocked on CAWIS external for development and acceptance.

AAIR login is locked on all CAWIS internal instances (development, acceptance and production).  

...

See Subtask 301363 https://dev.azure.com/transport-canada/DSD-CIVAV%20Support/_boards/board/t/AAIR/Backlog%20items

...

as it will remain the same each year.</p> 

Please click the link to log into the AAIR portal:<br />
https://catcappsx.tc.gc.ca/Saf-Sec-Sur/2/cawis-swimn/a_l.aspx?a=&lang=eng<br />

<br />
If you have forgotten your password, are a newly registered aircraft owner or are new to this AAIR portal, please create a new password by clicking the link below:<br />
https://catcappsx.tc.gc.ca/Saf-Sec-Sur/2/CAWIS-SWIMN/ForPss.aspx?m=A&lang=eng&hsh=[%2%]<br />

<p>Please be aware that:</p>
<ul>
<li> The password link is valid for the next 24 hours from the receipt of this email;</li>
<li> The password link can only be used once;</li>
<li> This request nullifies any previous request; </li>
<li> If you''re having trouble with the link, copy and paste it into your web browser.</li>
</ul>

<p>For security reasons, this request was received at [%5%]. If you did not initiate this request,
please notify the AAIR team in writing at <a href="mailto:tc.aair-raina.tc@tc.gc.ca">tc.aair-raina.tc@tc.gc.ca</a>.</p>

<p>Thank you,</p>

<p>The AAIR team</p>',


EMAIL_FBODY_LOB = 
'<p>Bonjour, [%1%],</p>

<p>Votre identifiant d’utilisateur est [%6%]. Veuillez noter votre identifiant d’utilisateur car il restera le même chaque année.</p>

Cliquez sur le lien suivant pour vous connecter au portail RAINA :<br />
https://catcappsx.tc.gc.ca/Saf-Sec-Sur/2/cawis-swimn/a_l.aspx?a=&lang=fra<br />

<br />
Si vous avez oublié votre mot de passe, si vous êtes un propriétaire d''aéronef nouvellement enregistré ou si vous êtes nouveau sur ce portail RAINA, veuillez créer un nouveau mot de passe en cliquant sur le lien ci-dessous :<br />
https://catcappsx.tc.gc.ca/Saf-Sec-Sur/2/CAWIS-SWIMN/ForPss.aspx?m=A&lang=fra&hsh=[%2%]<br />

<p>Sachez que:</p>
<ul>
<li> Le lien vers le mot de passe est valable pendant les 24 heures qui suivent la réception de ce courriel;</li>
<li> Ce lien vers le mot de passe ne peut être utilisé qu''une seule fois;</li>
<li> Cette requête annule toute requête précédente;</li>
<li> Si vous rencontrez des problèmes avec le lien, copiez-le et collez-le dans votre navigateur Web.</li>
</ul>

<p>Pour des raisons de sécurité, cette requête a été reçue à [%5%]. Si vous n''êtes pas à l''origine de la demande,
veuillez en informer l''équipe RAINA par écrit à l''adresse suivante :  <a href="mailto:tc.aair-raina.tc@tc.gc.ca">tc.aair-raina.tc@tc.gc.ca</a>.</p>

<p>Merci,</p>

<p>L''équipe RAINA</p>'

where EMAIL_CONTENT_ID = '44';

commit;

Configuration:

See: CAWIS - configuration settings for Dev and Acc (TR47_CAWIS_GENERAL_PROPS and web.config)

Info

The setting COPY EMAIL ADDRESSES should be set to the current product owner on Acceptance and Production. See the script provided below.

Code Block
languagesql
--Acceptance and Production
--Update the EMAIL COPY ADDRESSES (an AAIR setting) to current product owner.
UPDATE TR47_CAWIS_GENERAL_PROPS
SET PROPERTY_VALUE = 'GENEVIEVE.BOURGEOIS@TC.GC.CA',
LAST_MOD_TIMESTAMP_DTE = SYSDATE,
LAST_MOD_BY_USER_ID = 'ADMIN'
WHERE      UPPER(PROPERTY_NAME) = UPPER('EMAIL COPY ADDRESSES');

Shared Mailboxes

  • The following shared accounts are applicable on both development and acceptance. All members of the maintenance team have access:

    1. On development, the email address for these shared accounts is set to our shared CAWIS development mailbox.

    2. On acceptance the address is: cawis-acc-swimn@tc.gc.ca

    3. On production the address is: tc.aair-raina.tc@tc.gc.ca

See: Shared Mailbox Mapping Procedure

Updated texts for AAIR Owner form with contact info only (short form)


All new messaging that should appear on various AAIR pages is contained within this document - AAIR - November notification to aircraft owners.docx

Updated contact PDFs are posted to dev and acc, were also deployed to Production

See subtask 311255 Replace existing PDF under Contact List menu with new PDF

Document locations on dev and acc

EXTERNAL DEV - \\ncrws499\cawwwappsx\saf-sec-sur\2\AWD-CN\documents

EXTERNAL ACC - \\wwwapps2test\wwwappsroot\Saf-Sec-Sur\2\AWD-CN\documents

INTERNAL DEV - \\ncrws499\catcappsx\Saf-Sec-Sur\2\AWD-CN\documents

INTERNAL ACC - \\tcapps2test\tcappsroot\saf-sec-sur\2\AWD-CN\documents

Business Rules:

see also

  • AIRCRAFT CANNOT BE SALE REPORTED OR INVALID ADDRESS       

  • THE AIRCRAFT CANNOT BE A MEMBER OF A FLEET                 

  • THE AIRCRAFT CANNOT BE LISTED AS OUT-OF-SERVICE           

  • THE AIRCRAFT CANNOT HAVE HOURS ALREADY RECORDED FOR THE REPORTING PERIOD (CURRENT YEAR - 1) 

  • THE AIRCRAFT CANNOT BE LISTED WITH A YEAR OF MANUFACTURE EQUAL TO THE CURRENT YEAR

  • THE AIRCRAFT CANNOT BE ULTRALIGHT/ADVANCED ULTRALIGHT     

  • THE AIRCRAFT CANNOT BE DEREGISTERED

Creating an AAIR Account:

  • Login to CAWIS Internal as a TC User.

  • To obtain current tail marks run the following report: 5008 Files by Owner.

...

  • Select 5008 Files from the left-side menu.

  • Do a quick search by tail mark:

    • type in 2 letters - for example "FE"

  • Change email address to one that you can access.

  • Save.

  • Logout.

  • On the external website, Login AAIR

  • Click forgot user id or password.

    • It will ask for the tail mark and email address that you changed in the 5008

  • CAWIS will send you an email with a link and click open link

  • It will ask to create a password

  • Save

  • Take note of the client id in the email.

  • Login AAIR: client id & password (that you created)

AAIR info from October 2023 discussions onward:

  • TC login uses the TC login ID but the password is not the same.

  • For TC Login, the same credential works on both CAWIS internal and external. 

  • I have provided access for the TC Login option on CAWIS development and acceptance to all members of the maintenance team. 

  • Maintenance team have access to CAWIS prod for maintenance purposes only (Only for work related to tasks submitted by the client. No changes can be made without approval from the client)

  • AAIR does not use our TC Login credentials.

  • AAIR login is unlocked on CAWIS external for development and acceptance.

  • AAIR login is locked on all CAWIS internal instances (development, acceptance and production).  

AAIR - Export Query for AAIR Owner’s Update List

  • By using this query, we can generate the updated data regarding the addresses of aircraft owners.

  • Need to modify according to the requirement of start date and end date.

  • Export the result in Excel or CSV through TOAD.

Code Block
languagesql
/* Formatted on 2024-02-27 10:41:38 AM (QP5 v5.294) */
  SELECT                                                    --Y02.aircraft_id,
        Y50.CLIENT_ID,
         Y55.NAME_FIRST_NM || ' ' || Y55.NAME_LAST_NM AS OWNER,
         Y56.NAME_COMMON_NM,
         --Existing Address
         --A90.ADDRESS_ID,
         A90.ADDRESS_LINE1_TXT,
         A90.ADDRESS_LINE2_TXT,
         A90.CITY_TXT,
         A90.PROVINCE_CD,
         A90.POSTAL_CODE_ID,
         TR21.COUNTRY_ETXT,
         -- New Address if anything change
         CASE
            WHEN A90.ADDRESS_LINE1_TXT <> A90.ADDRESS_LINE1_UPDATED_TXT
            THEN
               A90.ADDRESS_LINE1_UPDATED_TXT
            WHEN     A90.ADDRESS_LINE1_TXT IS NULL
                 AND TRIM (A90.ADDRESS_LINE1_UPDATED_TXT) IS NOT NULL
            THEN
               A90.ADDRESS_LINE1_UPDATED_TXT
         END
            AS UPDATED_ADDRESS_LINE1,
         CASE
            WHEN A90.ADDRESS_LINE2_TXT <> A90.ADDRESS_LINE2_UPDATED_TXT
            THEN
               A90.ADDRESS_LINE2_UPDATED_TXT
            WHEN     A90.ADDRESS_LINE2_TXT IS NULL
                 AND TRIM (A90.ADDRESS_LINE2_UPDATED_TXT) IS NOT NULL
            THEN
               A90.ADDRESS_LINE2_UPDATED_TXT
         END
            AS UPDATED_ADDRESS_LINE2,
         CASE
            WHEN A90.CITY_TXT <> A90.CITY_UPDATED_TXT
            THEN
               A90.CITY_UPDATED_TXT
            WHEN     A90.CITY_TXT IS NULL
                 AND TRIM (A90.CITY_UPDATED_TXT) IS NOT NULL
            THEN
               A90.CITY_UPDATED_TXT
         END
            AS UPDATED_CITY_TXT,
         CASE
            WHEN A90.PROVINCE_CD <> A90.PROVINCE_UPDATED_CD
            THEN
               A90.PROVINCE_UPDATED_CD
            WHEN     A90.PROVINCE_CD IS NULL
                 AND TRIM (A90.PROVINCE_UPDATED_CD) IS NOT NULL
            THEN
               A90.PROVINCE_UPDATED_CD
         END
            AS UPDATED_PROVINCE_CD,
         CASE
            WHEN A90.POSTAL_CODE_ID <> A90.POSTAL_CODE_UPDATED_ID
            THEN
               A90.POSTAL_CODE_UPDATED_ID
            WHEN     A90.POSTAL_CODE_ID IS NULL
                 AND TRIM (A90.POSTAL_CODE_UPDATED_ID) IS NOT NULL
            THEN
               A90.POSTAL_CODE_UPDATED_ID
         END
            AS UPDATED_POSTAL_CODE,
         CASE
            WHEN A90.COUNTRY_CD <> A90.COUNTRY_UPDATED_CD
            THEN
               TR21U.COUNTRY_ETXT
            WHEN     A90.COUNTRY_CD IS NULL
                 AND TRIM (A90.COUNTRY_UPDATED_CD) IS NOT NULL
            THEN
               TR21U.COUNTRY_ETXT
         END
            AS UPDATED_COUNTRY_CD,
         (SELECT A92.ADDRESS_PROPERTY_TXT
            FROM A92_ADDRESS_PROPERTY A92
           WHERE     A92.ADDRESS_ID = A90.ADDRESS_ID
                 AND A92.ADDRESS_PROPERTY_TYPE_CD = '1'
                 AND A92.ORDINAL_NBR = 1
                 AND A92.Last_Mod_timestamp_dte BETWEEN TO_DATE ('01Dec2023')
                                                    AND TO_DATE ('05Feb2024'))
            AS ADDR_PHONE,
         A92.ADDRESS_PROPERTY_TXT                   AS ADDR_EMAIL,
         A90.Last_Mod_timestamp_dte
    FROM Y50_PARTY Y50
         LEFT JOIN Y90_PARTY_CONTACT Y90 ON Y90.PARTY_ID = Y50.PARTY_ID
         JOIN A90_ADDRESS A90
            ON     A90.ADDRESS_ID = Y90.ADDRESS_ID
               AND A90.Last_Mod_timestamp_dte BETWEEN TO_DATE ('01Dec2023')
                                                  AND TO_DATE ('05Feb2024')
         LEFT OUTER JOIN Y55_INDIVIDUAL Y55 ON Y55.PARTY_ID = Y50.PARTY_ID
         LEFT OUTER JOIN Y56_ORGANIZATION Y56 ON Y56.PARTY_ID = Y50.PARTY_ID
         LEFT JOIN A92_ADDRESS_PROPERTY A92
            ON     A90.ADDRESS_ID = A92.ADDRESS_ID
               AND A92.ADDRESS_PROPERTY_TYPE_CD = '3'
               AND A92.Last_Mod_timestamp_dte BETWEEN TO_DATE ('01Dec2023')
                                                  AND TO_DATE ('05Feb2024')
         LEFT OUTER JOIN TR21_COUNTRY TR21 ON TR21.COUNTRY_CD = A90.COUNTRY_CD
         LEFT OUTER JOIN TR21_COUNTRY TR21U
            ON TR21U.COUNTRY_CD = A90.COUNTRY_UPDATED_CD
   WHERE    TRIM (A90.ADDRESS_LINE1_UPDATED_TXT) IS NOT NULL
         OR TRIM (A90.ADDRESS_LINE2_UPDATED_TXT) IS NOT NULL
         OR TRIM (A90.CITY_UPDATED_TXT) IS NOT NULL
         OR TRIM (A90.COUNTRY_UPDATED_CD) IS NOT NULL
         OR TRIM (A90.POSTAL_CODE_UPDATED_ID) IS NOT NULL
         OR     TRIM (A90.PROVINCE_UPDATED_CD) IS NOT NULL
            AND A90.ADDRESS_ID IN
                   (SELECT Y90.ADDRESS_ID
                      FROM Y02_AIRCRAFT_HISTORY Y02
                           JOIN Y53_PARTY_LIST Y53
                              ON Y53.AIRCRAFT_PARTY_LIST_ID = Y02.AIRCRAFT_ID
                           JOIN Y50_PARTY Y50 ON Y50.PARTY_ID = Y53.PARTY_ID
                           LEFT JOIN Y90_PARTY_CONTACT Y90
                              ON Y90.PARTY_ID = Y50.PARTY_ID
                     WHERE                     --  Y02.AIRCRAFT_ID = 14633 AND
                          NOT   EXISTS
                                  (SELECT 1
                                     FROM Y02_AIRCRAFT_HISTORY Y02a
                                    WHERE     Y02a.AIRCRAFT_ID =
                                                 Y02.AIRCRAFT_ID
                                          AND Y02A.DATE_START_DTE >
                                                 Y02.DATE_START_DTE)
                           AND Y90.ADDRESS_TYPE_CD = '1'
                           AND Y53.DATE_STOP_DTE IS NULL
                           AND Y02.ACTIVE_INACTIVE_IND = 'A'
                           AND Y53.REGISTERED_OWNER_IND = 'Y')
ORDER BY A90.Last_Mod_timestamp_dte DESC;