Managing Household Addresses

A household is comprised of a number of individuals who share the same home mailing address. A household is specifically a set of constituents who share the same address as their primary residence. Household members must have a "HOME"address with the same address ID as the household address ID. The address ID assigned to the household is always a linked address ID. It is okay for the head of household to have a linked address ID to the household address, as long as the owner of the address is a household member. Households will never own an address; instead, a household will always have a linked address to a household member’s "HOME"address.

A household member must be the owner of the HOME address being assigned to the household, although the household member does not have to be the head of household.

When creating a household from an existing constituent, if the constituent has a "GOOD", "HOME" address that the constituent owns, that address will default as the household address. If the constituent has a "GOOD", "HOME" address that is a linked address, that address will default as the household address and all constituents linked to the address will also be automatically pulled in as a household member.

Please keep in mind that if the constituent from which you are creating the household does NOT have a "HOME" address, a "BLANK" address record will be created for the household. Additionally, if the constituent from which you are creating the household has a "BLANK" address, a "BLANK" address record will be created for the household. If later a "HOME" address is added, the "BLANK" address will be status to a status of "BAD".

Linking Addresses

When a customer is added to a household, if they have a "HOME" address with an address ID that is different than the household address ID, the system will create a new linked address for the new household member that links to the household address ID, but the system will not automatically mark the new household member’s other "HOME" address as bad. Instead, the system will prompt the user whether to mark the other HOME address(es) as bad: “The new household member has a different HOME address. A HOME address will be created for the household member for the household’s HOME address. Should the previous HOME address be marked as bad?”

 

If none of the household members exist, when you save the household record, the system will create a new "HOME" address record owned by the Head of Household, create a linked "HOME" address records for non-Head of Household members, and update the household record with address ID of "HOME" address. For a new household where the Head of Household already exists, when you save the household record, if the Head of Household already owned a "HOME" address, the system will create linked address records for the other non-Head of Household household members who did not have a linked address to the Head of Household’s "HOME" address. If the Head of Household did not already own a "HOME" address, the system will create a new "HOME"address record owned by the Head of Household and create linked "HOME"address records for non-Head of Household members. Additionally, when you save the household record, the system will update household record with address ID of "HOME" address, and if any household members had a "HOME"address, update the address status to "BAD" for those "HOME" addresses. For a new household where a household member who is not the head of household but who is the owner of the "HOME" address exists, when you save the household record, the system will create linked address records for the household members who did not have a linked address to the household member’s "HOME" address, update household record with address ID of "HOME" address, and, if any household members had a "HOME" address, update the address status to "BAD" for those "HOME" addresses.

Householding Address Updates Business Scenarios

The following table lists various scenarios regarding household members and address updates:

Scenario

Household Exists?

Head of Household Exists?

Who Owns the Address?

Notes

New Household is being created and no household members exist in the database (HOH will be the owner of HOME address)

N

N

HOH

 

New household is being created and household members exist in the database

N

Y

HOH

The head of household does have a HOME address; the HOH owns the HOME address. Additional HH members are added; all of the additional HH members have a customer record. 

New household is being created and household members exist in the database, HOH also has an address linked to his company address

N

Y

HOH

HOH also has a linked address to the company

Household exists and new household member who does not exist in the database is being added to the household

Y

Y

HOH

 

Household exists and new household member who does exist in the database is being added to the household; household member being added does not have a HOME address

Y

Y

HOH

Household member only has a linked WORK address

Household exists and new household member who does exist in the database is being added to the household; household member being added does have a HOME address

Y

Y

HOH

 

Changing of Head of household with an another household member with in the same household

Y

Y

Former HOH who is staying in the household

Changing HOH within household

Head of household is moving out of the household but not into a household

Y

Y

HOH who is leaving household

HOH is moving out of household but not into a new household

Head of household is moving out of the household into a new household that does not exist yet

Y

Y

HOH who is leaving household

HOH is moving out of household into a new household that doesn't exist yet

Head of household is moving out of the household into an existing household

 

 

HOH who is leaving household

HOH is moving out of household into a new household that already exists

Household member who is not head of household is moving out of the household but not into a household

Y

Y

Household member who is leaving

 

Household member who is not head of household is moving out of the household into a new household that does not exist yet

Y

Y

HOH

 

Household member who is not head of household is moving out of the household into an existing household

Y

Y

HOH

 

Multiple household members one of whom is the head of household are moving out of the household but not into another household

Y

Y

HOH

 

Multiple household members one of whom is the head of household are moving out of the household into a new household that does not exist yet

Y

Y

HOH

 

Multiple household members one of whom is the head of household are moving out of the household into a another household that does exist

Y

Y

HOH

 

Multiple household members none of whom is the head of household are moving out of the household but not into another household

Y

Y

HOH

 

Multiple household members none of whom is the head of household are moving out of the household into a new household that does not exist yet

Y

Y

HOH

 

Two households are being merged

Y

Y

 

 

Household is dissolved

Y

Y

 

 

Mark a household address as bad and add new

Y

Y

HOH or Household member

 

Household Address Update Logic

The following SQL objects are used to manage household addresses:

·            SQL Job – [DatabaseName]_Household_Address_Refresh

·            SQL Stored Procedure – CUS_HH_Address_Refresh

 

This SQL job will be used to perform address refresh for households. The default schedule for this SQL job is to run at 10:30 PM daily. This schedule can be modified as required for each installation.

 

The SQL job executes the stored procedure CUS_HH_Address_Refresh in the database.

 

Input Parameters:

·            @CUS_Household_ID – enter value for one household or leave it at NULL to execute procedure for all households. The latter is generally done when executing the stored procedure from the SQL job.

·            @Old_Address_ID – data for this input parameter is entered when you want to specify the current address that needs to be updated with new address ID for each member of the household. The default value for this input parameter is NULL.

·            @New_Address_ID– data for this input parameter is entered when you want to specify the value for new address ID that will be used for each member of the household. The default value for this input parameter is NULL.

·            @MODOPER – user ID of the account executing the stored procedure.

·            @Process_Orders – specifies if you want to update addresses in any open orders that belong to a main home address that is being updated by the procedure.

 

There are three possible scenarios in which the address refresh stored procedure works. These rules will be applied to existing "HOME" address that are marked "GOOD" unless noted. The system will ignore existing "HOME" addresses that are marked "BAD".

Scenario 1: Address Refresh for New Household

This happens when a new household is created. For this scenario, the input value for @Old_Address_ID = 0 and there will be some value entered for @New_Address_ID <> 0.

 

Processing rules:

1.    Existing "HOME" address with PRIORITY_SEQ = 0 marked "GOOD".

a.    Set PRIORITY_SEQ = N + 1 and mark it "BAD".

b.    Insert new address with PRIORITY_SEQ = 0 marked "GOOD".

2.    Existing "HOME" address with PRIORITY_SEQ > 0 marked "GOOD".

a.    Keep same PRIORITY_SEQ and mark it "BAD".

b.    Insert new address with PRIORITY_SEQ = N + 1 marked "GOOD".

c.    SHIP_TO_FLAG and BILL_TO_FLAG rules:

i.      If existing "HOME" address has SHIP_TO and/or BILL_TO address = "Y", then set it to "N" and set address for new record = "Y".

Scenario 2: Copy Address for Existing Household

This happens when the address owner moves out of the household. For this scenario, the input value for @Old_Address_ID > 0 and input value some value entered for @New_Address_ID = 0.

 

Processing rules:

1.    Existing "HOME" address with PRIORITY_SEQ = 0 marked "GOOD".

a.    Set PRIORITY_SEQ = N + 1 and mark it "BAD".

b.    Insert new address with PRIORITY_SEQ = 0 marked "GOOD".

2.    Existing "HOME" address with PRIORITY_SEQ > 0 marked "GOOD".

a.    Keep same PRIORITY_SEQ and mark it "BAD".

b.    Insert new address with PRIORITY_SEQ = N + 1 marked "GOOD".

c.    SHIP_TO_FLAG and BILL_TO_FLAG rules:

i.      If existing "HOME" address has SHIP_TO and/or BILL_TO address = "Y", then set it to "N" and set address for new record = "Y".

Scenario 3 : Change Address for Existing Household

This happens when the address owner moves out of the household. For this scenario, the input value for @Old_Address_ID > 0 and input value some value entered for @New_Address_ID > 0.

 

Processing rules:

1.    If existing "HOME" address is marked "GOOD":

a.    Existing "HOME" address with PRIORITY_SEQ = 0 marked "GOOD".

i.      Set PRIORITY_SEQ = N + 1 and mark it "BAD".

ii.     Insert new address with PRIORITY_SEQ = 0 marked "GOOD".

b.    Existing "HOME" address with PRIORITY_SEQ > 0 marked "GOOD".

i.      Keep same PRIORITY_SEQ and mark it "BAD".

ii.     Insert new address with PRIORITY_SEQ = N + 1 marked "GOOD".

iii.   SHIP_TO_FLAG and BILL_TO_FLAG rules:

·            If existing "HOME" address has SHIP_TO and/or BILL_TO address = "Y" then set it to "N" and set address for new record = "Y".

2.    If existing "HOME" address is marked "BAD"

a.    Insert new address with PRIORITY_SEQ = N + 1 marked "GOOD".

b.    SHIP_TO_FLAG and BILL_TO_FLAG rules:

i.      Set address flags for new record = "N".