Page Comparison

SELECT --Y02.aircraft_id,
       Y50.CLIENT_ID,/* Formatted on 2024-02-27 10:41:38 AM (QP5 v5.294) */
  SELECT        Y55.NAME_FIRST_NM || ' ' || Y55.NAME_LAST_NM as OWNER,                     Y56.NAME_COMMON_NM,        --Existing Address        --A90Y02.ADDRESSaircraft_IDid,
       A90 Y50.ADDRESSCLIENT_LINE1_TXTID,
         A90Y55.ADDRESSNAME_LINE2FIRST_TXT,NM || ' '     A90.CITY_TXT|| Y55.NAME_LAST_NM AS OWNER,
       A90.PROVINCE_CD  Y56.NAME_COMMON_NM,
       A90.POSTAL_CODE_ID,  --Existing Address
    TR21.COUNTRY_ETXT,        -- New Address if anything changeA90.ADDRESS_ID,
         A90.ADDRESS_LINE1_TXT,
    CASE     A90.ADDRESS_LINE2_TXT,
        When A90.ADDRESS_LINE1CITY_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_TXTPROVINCE_CD,
         A90.POSTAL_CODE_ID,
        END AS UPDATED_ADDRESS_LINE1 TR21.COUNTRY_ETXT,
         -- New Address if anything change
         CASE
             WhenWHEN A90.ADDRESS_LINE2LINE1_TXT <> A90.ADDRESS_LINE2LINE1_UPDATED_TXT
Then A90.ADDRESS_LINE2_UPDATED_TXT            THEN
     When A90.ADDRESS_LINE2_TXT Is Null and TRIM(A90.ADDRESS_LINE2_UPDATED_TXT) Is not Null Then A90.ADDRESS_LINE2LINE1_UPDATED_TXT
         END AS UPDATED_ADDRESS_LINE2, WHEN     A90.ADDRESS_LINE1_TXT IS NULL
CASE              When A90.CITY_TXT <> A90.CITY_UPDATED_TXT Then A90.CITY AND TRIM (A90.ADDRESS_LINE1_UPDATED_TXT) IS NOT NULL
         When A90.CITY_TXT Is NullTHEN
and TRIM(A90.CITY_UPDATED_TXT) Is not Null Then A90.CITY_UPDATED_TXT         END AS A90.ADDRESS_LINE1_UPDATED_CITY_TXT,
        CASE END
            When A90.PROVINCE_CD <> A90.PROVINCE_UPDATED_CD Then A90.PROVINCE_UPDATED_CDAS UPDATED_ADDRESS_LINE1,
         CASE
        When A90.PROVINCE_CD Is Null andWHEN TRIM(A90.PROVINCEADDRESS_UPDATEDLINE2_CD) Is not Null Then A90.PROVINCETXT <> A90.ADDRESS_LINE2_UPDATED_CDTXT
        END AS UPDATED_PROVINCE_CD,   THEN
               CASEA90.ADDRESS_LINE2_UPDATED_TXT
            WHEN When A90.POSTAL_CODE_ID <>    A90.POSTALADDRESS_CODE_UPDATED_ID Then A90.POSTAL_CODE_UPDATED_IDLINE2_TXT IS NULL
             When A90.POSTAL_CODE_ID Is Null andAND TRIM (A90.POSTALADDRESS_CODELINE2_UPDATED_ID) Is not Null Then A90.POSTAL_CODE_UPDATED_IDTXT) IS NOT NULL
            THEN
               A90.ADDRESS_LINE2_UPDATED_TXT
         END
            AS UPDATED_POSTALADDRESS_CODELINE2,
         CASE
            WHEN When A90.COUNTRYCITY_CDTXT <> A90.COUNTRYCITY_UPDATED_CDTXT
Then TR21U.COUNTRY_ETXT           THEN
 When A90.COUNTRY_CD Is Null and TRIM(A90.COUNTRY_UPDATED_CD) Is not Null Then TR21U.COUNTRY_ETXT              A90.CITY_UPDATED_TXT
            WHEN   END AS UPDATED_COUNTRY_CD,
A90.CITY_TXT IS NULL
      (SELECT A92.ADDRESS_PROPERTY_TXT          AND FROM A92_ADDRESS_PROPERTY A92
 TRIM (A90.CITY_UPDATED_TXT) IS NOT NULL
       WHERE     A92.ADDRESS_ID = A90.ADDRESS_IDTHEN
               AND A92A90.ADDRESSCITY_PROPERTYUPDATED_TYPE_CDTXT
= '1'        END
       AND A92.ORDINAL_NBR = 1  AS UPDATED_CITY_TXT,
    and A92.Last_Mod_timestamp_dte between TO_DATE ('01Dec2023') andCASE
TO_DATE ('05Feb2024')       )    WHEN A90.PROVINCE_CD <> A90.PROVINCE_UPDATED_CD
      AS ADDR_PHONE,      THEN
     A92.ADDRESS_PROPERTY_TXT AS ADDR_EMAIL,        A90.LastPROVINCE_ModUPDATED_timestamp_dteCD
  FROM Y50_PARTY Y50        WHEN LEFT JOIN Y90_PARTY_CONTACT Y90 ON Y90A90.PARTYPROVINCE_IDCD = Y50.PARTY_IDIS NULL
       JOIN A90_ADDRESS A90 ON A90.ADDRESS_ID = Y90.ADDRESS_ID
          AND TRIM (A90.PROVINCE_UPDATED_CD) IS NOT NULL
      LEFT OUTER JOIN Y55_INDIVIDUAL Y55 ON Y55.PARTY_IDTHEN
= Y50.PARTY_ID        LEFT OUTER JOIN Y56_ORGANIZATION Y56 ON Y56A90.PARTYPROVINCE_UPDATED_IDCD
= Y50.PARTY_ID        END
   LEFT JOIN A92_ADDRESS_PROPERTY A92 ON A90.ADDRESS_ID = A92.ADDRESS_ID        AS UPDATED_PROVINCE_CD,
         CASE
            WHEN A90.POSTAL_CODE_ID <> A90.POSTAL_CODE_UPDATED_ID
            THEN
           AND    A92A90.ADDRESSPOSTAL_PROPERTYCODE_TYPEUPDATED_CDID
 = '3'        and A92.Last_Mod_timestamp_dte between TO_DATE ('01Dec2023') and TO_DATE ('05Feb2024') WHEN     A90.POSTAL_CODE_ID IS NULL
           LEFT OUTER JOIN TR21_COUNTRY TR21 ON TR21.COUNTRY_CD = A90.COUNTRY_CD
 AND TRIM (A90.POSTAL_CODE_UPDATED_ID) IS NOT NULL
     LEFT OUTER JOIN TR21_COUNTRY TR21U ON TR21U.COUNTRY_CD = A90.COUNTRY_UPDATED_CDTHEN
     WHERE A90.Last_Mod_timestamp_dte between TO_DATE ('01Dec2023') and TO_DATE ('05Feb2024')  And TRIM(A90.ADDRESSPOSTAL_LINE1CODE_UPDATED_TXT)ID
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 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
                     FROM Y02_AIRCRAFT_HISTORY Y02           (SELECT 1
 JOIN Y53_PARTY_LIST Y53                 ON Y53.AIRCRAFT_PARTY_LIST_ID = Y02.AIRCRAFT_ID              JOINFROM Y50_PARTY Y50 ON Y50.PARTY_ID = Y53.PARTY_IDY02_AIRCRAFT_HISTORY Y02a
                     LEFT JOIN Y90_PARTY_CONTACT Y90 ON Y90.PARTY_ID = Y50.PARTY_ID        WHERE   --  Y02Y02a.AIRCRAFT_ID =
14633 AND               NOT EXISTS                     (SELECT 1          Y02.AIRCRAFT_ID
             FROM Y02_AIRCRAFT_HISTORY Y02a                       WHERE    AND Y02aY02A.AIRCRAFT_ID = Y02.AIRCRAFT_IDDATE_START_DTE >
                                              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')
orderORDER byBY A90.Last_Mod_timestamp_dte descDESC;