Advertisement
DataCCIW

Future Address Automation Script Arena

Dec 9th, 2019
761
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 3.32 KB | None | 0 0
  1. --Future Address Automation Script Arena
  2. --Author:   Ethan Jordan/Tony Visconti
  3. --Date:     12/2/19
  4. DECLARE
  5.   @AddressTypeFuture INT = 11554,
  6.   @AddressTypeHome INT = 41,
  7.   @AddressTypePrevious INT = 313,
  8.   @HistoryType INT = 366, -- Person Updates
  9.   @OrgID INT = 1
  10. BEGIN TRAN UpdateAddress;
  11.  
  12. -- Add note to person updates to indicate future address was changed to primary home address
  13. INSERT INTO [dbo].[core_person_history]([date_created],
  14.                                         [date_modified],
  15.                                         [created_by],
  16.                                         [modified_by],
  17.                                         [person_id],
  18.                                         [history_type_luid],
  19.                                         [history_qualifier_id],
  20.                                         [system_history],
  21.                                         [history],
  22.                                         [organization_id],
  23.                                         [display_flag],
  24.                                         [display_expiration],
  25.                                         [private_flag])
  26.        SELECT GETDATE(),
  27.               GETDATE(),
  28.               'Future Address Automation',
  29.               'Future Address Automation',
  30.               PA1.person_id,
  31.               @HistoryType,
  32.               -1,
  33.               1,
  34.               'Primary Address ''' + A2.street_address_1 + ' ' + A2.street_address_2 + ' ' + A2.city + ', ' + A2.state + ' ' + A2.postal_code +
  35.               ''' Changed to ''' + A1.street_address_1 + ' ' + A1.street_address_2 + ' ' + A1.city + ', ' + A1.state + ' ' + A1.postal_code + +'''',
  36.               @OrgID,
  37.               0,
  38.               '1900-01-01',
  39.               0
  40.        FROM   core_person_address PA1
  41.               JOIN core_address A1 ON A1.address_id = PA1.address_id
  42.               JOIN core_person_address PA2 ON PA1.person_id = PA2.person_id
  43.               JOIN core_address A2 ON A2.address_id = PA2.address_id
  44.        WHERE  PA1.address_type_luid = @AddressTypeFuture
  45.               AND PA1.active_date < GETDATE()
  46.               AND PA2.primary_address = 1;
  47.  
  48.  
  49. --Finding old "previous addresses"
  50. WITH PreviousAddressSwapping
  51.      AS (SELECT A3.*
  52.          FROM   core_person_address A1
  53.                 JOIN core_person_address A2 ON A1.person_id = A2.person_id
  54.                 JOIN core_person_address A3 ON A3.person_id = A1.person_id
  55.          WHERE  A1.active_date < GETDATE()
  56.                 AND A1.address_type_luid = @AddressTypeFuture
  57.                 AND A2.primary_address = 1
  58.                 AND A3.address_type_luid = @AddressTypePrevious)
  59.  
  60.  
  61.  
  62.      --Deleting said "previous address" to make room for new previous address
  63.      DELETE core_person_address
  64.      FROM core_person_address A1
  65.           JOIN PreviousAddressSwapping A2 ON A1.person_id = A2.person_id
  66.                                              AND A1.address_id = A2.address_id
  67.                                              AND A1.address_type_luid = A2.address_type_luid;
  68.  
  69.  
  70.  
  71. --Finding primary address with a now active "future address"
  72. WITH PrimaryAddressSwapping
  73.      AS (SELECT A2.*
  74.          FROM   core_person_address A1
  75.                 JOIN core_person_address A2 ON A1.person_id = A2.person_id
  76.          WHERE  A1.active_date < GETDATE()
  77.                 AND A1.address_type_luid = @AddressTypeFuture
  78.                 AND A2.primary_address = 1)
  79.  
  80.  
  81.  
  82.      --Updating said primary addresses to be past addresses
  83.      UPDATE core_person_address
  84.        SET  address_type_luid = @AddressTypePrevious, primary_address = 0
  85.      FROM   core_person_address A1
  86.             JOIN PrimaryAddressSwapping A2 ON A1.person_id = A2.person_id
  87.                                               AND A1.address_id = A2.address_id
  88.                                               AND A1.address_type_luid = A2.address_type_luid;
  89.  
  90.  
  91. --Updates now active "future address" to be home primary addresses
  92. UPDATE core_person_address
  93.   SET  address_type_luid = @AddressTypeHome, primary_address = 1
  94. WHERE  active_date < GETDATE()
  95.        AND address_type_luid = @AddressTypeFuture;
  96. COMMIT TRAN UpdateAddress;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement