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,
       A90  Y55.ADDRESSNAME_LINE2FIRST_TXT,NM || ' ' || Y55.NAME_LAST_NM AS OWNER,
 A90.CITY_TXT,        A90Y56.PROVINCENAME_COMMON_CDNM,
  
    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.ADDRESSCITY_LINE1_TXT,
  <> A90.ADDRESS_LINE1_UPDATED_TXT Then A90.ADDRESS_LINE1_UPDATED_TXT    A90.PROVINCE_CD,
        When A90.ADDRESSPOSTAL_LINE1CODE_TXTID,
 Is Null and TRIM(A90.ADDRESS_LINE1_UPDATED_TXT) Is not Null Then A90.ADDRESS_LINE1_UPDATED_TXT      TR21.COUNTRY_ETXT,
         -- New Address if ENDanything AS UPDATED_ADDRESS_LINE1,change
         CASE
            WHEN When 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 Null and TRIM(A90.CITY_UPDATED_TXT) Is not Null Then A90.CITY THEN
               A90.ADDRESS_LINE1_UPDATED_TXT
         END
            AS UPDATED_CITYADDRESS_TXTLINE1,
         CASE
            WHEN When A90.PROVINCEADDRESS_LINE2_CDTXT <> A90.PROVINCEADDRESS_LINE2_UPDATED_CDTXT
  Then A90.PROVINCE_UPDATED_CD         THEN
   When A90.PROVINCE_CD Is Null and TRIM(A90.PROVINCE_UPDATED_CD) Is not Null Then A90.PROVINCE_UPDATED_CD            A90.ADDRESS_LINE2_UPDATED_TXT
           END AS UPDATED_PROVINCE_CD,WHEN     A90.ADDRESS_LINE2_TXT IS NULL
  CASE              When A90.POSTAL_CODE_ID <> A90.POSTAL_CODE AND TRIM (A90.ADDRESS_LINE2_UPDATED_IDTXT) Then A90.POSTAL_CODE_UPDATED_IDIS NOT NULL
            THEN
     When A90.POSTAL_CODE_ID Is Null and TRIM(A90.POSTAL_CODE_UPDATED_ID) Is not Null Then A90.POSTALADDRESS_CODELINE2_UPDATED_IDTXT
         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 TR21UA90.COUNTRYCITY_UPDATED_ETXTTXT
        END AS UPDATED_COUNTRY_CD,  WHEN      (SELECT A92.ADDRESS_PROPERTY_A90.CITY_TXT IS NULL
        FROM A92_ADDRESS_PROPERTY A92       AND   WHERE     A92.ADDRESS_ID = A90.ADDRESS_ID
TRIM (A90.CITY_UPDATED_TXT) IS NOT NULL
            THEN
              AND A92A90.ADDRESSCITY_PROPERTY_TYPE_CD = '1'UPDATED_TXT
         END
        AND A92.ORDINAL_NBR = 1 AS UPDATED_CITY_TXT,
     and A92.Last_Mod_timestamp_dte between TO_DATE ('01Dec2023') and TO_DATE ('05Feb2024') CASE
            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.PARTY_ID = Y50.PARTY_IDPROVINCE_CD IS NULL
               JOIN A90_ADDRESS A90AND ONTRIM (A90.ADDRESS_ID = Y90.ADDRESS_ID
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.PARTY_ID = Y50.PARTY_IDPROVINCE_UPDATED_CD
         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 A92.ADDRESS_PROPERTY_TYPE_CD = '3'        and A92.Last_Mod_timestamp_dte between TO_DATE ('01Dec2023') and TO_DATE ('05Feb2024')A90.POSTAL_CODE_UPDATED_ID
            WHEN  LEFT OUTER JOIN TR21_COUNTRY TR21 ON TR21.COUNTRY_CD = A90.COUNTRY_CDA90.POSTAL_CODE_ID IS NULL
          LEFT OUTER JOIN TR21_COUNTRY TR21U ON TR21U.COUNTRY_CD =AND TRIM (A90.COUNTRYPOSTAL_CODE_UPDATED_CDID) IS NOT NULL
            THEN
     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;