Future Address Automation Script Arena

--Future Address Automation Script Arena
--Author:   Ethan Jordan/Tony Visconti
--Date:     12/2/19
DECLARE
  @AddressTypeFuture INT = 11554,
  @AddressTypeHome INT = 41,
  @AddressTypePrevious INT = 313,
  @HistoryType INT = 366, -- Person Updates
  @OrgID INT = 1
BEGIN TRAN UpdateAddress;

-- Add note to person updates to indicate future address was changed to primary home address
INSERT INTO [dbo].[core_person_history]([date_created],
                                        [date_modified],
                                        [created_by],
                                        [modified_by],
                                        [person_id],
                                        [history_type_luid],
                                        [history_qualifier_id],
                                        [system_history],
                                        [history],
                                        [organization_id],
                                        [display_flag],
                                        [display_expiration],
                                        [private_flag])
       SELECT GETDATE(),
              GETDATE(),
              'Future Address Automation',
              'Future Address Automation',
              PA1.person_id,
              @HistoryType,
              -1,
              1,
              'Primary Address ''' + A2.street_address_1 + ' ' + A2.street_address_2 + ' ' + A2.city + ', ' + A2.state + ' ' + A2.postal_code +
              ''' Changed to ''' + A1.street_address_1 + ' ' + A1.street_address_2 + ' ' + A1.city + ', ' + A1.state + ' ' + A1.postal_code + +'''',
              @OrgID,
              0,
              '1900-01-01',
              0
       FROM   core_person_address PA1
              JOIN core_address A1 ON A1.address_id = PA1.address_id
              JOIN core_person_address PA2 ON PA1.person_id = PA2.person_id
              JOIN core_address A2 ON A2.address_id = PA2.address_id
       WHERE  PA1.address_type_luid = @AddressTypeFuture
              AND PA1.active_date < GETDATE()
              AND PA2.primary_address = 1;


--Finding old "previous addresses"
WITH PreviousAddressSwapping
     AS (SELECT A3.*
         FROM   core_person_address A1
                JOIN core_person_address A2 ON A1.person_id = A2.person_id
                JOIN core_person_address A3 ON A3.person_id = A1.person_id
         WHERE  A1.active_date < GETDATE()
                AND A1.address_type_luid = @AddressTypeFuture
                AND A2.primary_address = 1
                AND A3.address_type_luid = @AddressTypePrevious)



     --Deleting said "previous address" to make room for new previous address
     DELETE core_person_address
     FROM core_person_address A1
          JOIN PreviousAddressSwapping A2 ON A1.person_id = A2.person_id
                                             AND A1.address_id = A2.address_id
                                             AND A1.address_type_luid = A2.address_type_luid;



--Finding primary address with a now active "future address"
WITH PrimaryAddressSwapping
     AS (SELECT A2.*
         FROM   core_person_address A1
                JOIN core_person_address A2 ON A1.person_id = A2.person_id
         WHERE  A1.active_date < GETDATE()
                AND A1.address_type_luid = @AddressTypeFuture
                AND A2.primary_address = 1)



     --Updating said primary addresses to be past addresses
     UPDATE core_person_address
       SET  address_type_luid = @AddressTypePrevious, primary_address = 0
     FROM   core_person_address A1
            JOIN PrimaryAddressSwapping A2 ON A1.person_id = A2.person_id
                                              AND A1.address_id = A2.address_id
                                              AND A1.address_type_luid = A2.address_type_luid;


--Updates now active "future address" to be home primary addresses
UPDATE core_person_address
  SET  address_type_luid = @AddressTypeHome, primary_address = 1
WHERE  active_date < GETDATE()
       AND address_type_luid = @AddressTypeFuture;
COMMIT TRAN UpdateAddress;