Change Community Period Dates

Objectives

Successfully change meal plans, charge accounts, etc. for community when switching period dates.

Prerequisites

• Access to fc_obj_owner on database where community's data resides
• Access to and knowledge of import templates located on the J drive.

Instructions

If a community is changing the period dates for their community and would like our help in setting this up, follow the steps below.

Weeks/Months Leading Up to Period Date Change

1. Understand the requirements of the period date change.
1. What is the existing start day of month for the plans?
2. What will be the new start date of month for the plans?
3. When will the new period start?
4. What is their transition plan from one plan to another? Will the old plan be the short period or will the new plan be the short period.
2. If the period date change is several months off, schedule a meeting with the customer about a month out from transition to confirm their decisions.
3. Schedule an appointment on your calendar for the last week before the period date change to make the changes in the application. This helps to decrease duplicate work by the community in setting up two meal plans for new residents.

Week of/Before Period Date Changes

4. Set up new meal plans under Plans tab with the new dates. You might also want to rename the old meal plans to have an end date on them so customers know not to use them.
1. Example – From ‘1 Meal Per Day’ to ‘1 Meal Per Day (end 12/31/14)’

5. Remove any accounts that do not have customers linked to them. This will help by not creating new meal plans for ones that don’t have customers linked to them. You can find these by doing a search on the Accounts page under Customer Names for No Customer.

6. Run the following query to get a list of residents who are end-dated, but their accounts are not. We will want to clean these up before we create new account. This again, helps us to not create new meal plan accounts for residents who are no longer effective.

• select account_number, last_name, first_name, to_char(rs.effective_end_date,'mm-dd-yyyy'), p_allowance_acct_utils.get_all_allowance_acct_names(ac.community_id, ac.account_id) as account_names from t_accounts ac inner join t_resident_accounts ra on ra.account_id = ac.account_id inner join t_residents rs on rs.resident_id = ra.resident_id where ac.effective_end_date is null and ac.community_id = <Community Id>  and deleted = 'N' and rs.effective_end_date is not null order by account_number;
• Any residents who have multiple names listed under account_names column will need to be fixed manually by end dating the joint account as of the resident's effective end date and then creating a new account for the remaining spouse.
• Once all couples have been updated, re-run the script above to confirm that only single residents remain.
• Then run the following script to get statements to update the remaining single accounts.
• select 'update accounts set effective_end_date = to_date(''' || to_char(rs.effective_end_date,'mm/dd/yyyy') || ''',''mm/dd/yyyy''), modified_by = ''junges'', modified_date = sysdate where account_id = ' || ac.account_id || ';'  from t_accounts ac inner join t_resident_accounts ra on ra.account_id = ac.account_id
  inner join t_residents rs on rs.resident_id = ra.resident_id where ac.effective_end_date is null and ac.community_id = <Community Id>  and deleted = 'N' and rs.effective_end_date is not null
  order by account_number;
• Copy and paste the results of the query and run the statements.
• Commit to the database.

7. Find the community plan id of the plan that will no longer be effective.

select community_plan_id, description from t_community_plans where community_id = <Community Id>  order by community_plan_id;

8. Get a count of how many accounts will need to be created.

select count(*) from t_accounts where community_plan_id = <Community Plan Id> and effective_end_date is null;

9. Use account template to create the number of new accounts needed with the correct effective start date and load into database per instructions on template.

10. Get list of resident accounts for this plan. This will give us the guide to create the new resident account rows. Copy and paste the results into a Customer Account template.

select ra.resident_id, ra.account_id, ra.billing_id  from t_accounts ac inner join t_resident_accounts ra on ac.account_id = ra.account_id inner join t_residents rs on rs.resident_id = ra.resident_id where  ac.effective_end_date is null and community_plan_id = <Old Community Plan Id> order by ac.account_id;

11. End date existing accounts. Replace 01/31/2015 with the correct end date.

update accounts set effective_end_date = to_date('01/31/2015','mm/dd/yyyy'), modified_by = <your username>, modified_date = sysdate where community_plan_id=  <Old Community Plan Id> and effective_end_date is null;

12. Get list of new account ids to match with template.

select account_id, community_plan_id from t_accounts where community_plan_id = <New Community Plan Id>  and created_by = <your username> and created_date =trunc(sysdate) order by account_id;

13. In Excel template match up new account ids with old account ids. You may want to use the data validation in Excel to find the duplicate values on the existing account id to use for the new account ids.

14. Import the customer account data using the instructions on the template.

Update Month End Close Dates

Users will need the ability to close a shortened period, which can happen by giving them the ability to change their close dates.

1. Set ‘Change Close Dates’ to ‘Yes’ on the Communities button for the transition month. This will allow the customer to manually set the date for the close, if there is a short period.
2. Ask customer to inform you when they have closed the short period. Then set ‘Change Close Dates’ to ‘No’.

Update Charge Account Dates

The start day of month for the charge accounts also typically changes when meal plan period dates change. There will be a transition period where this may have different values than the actual charge account, but this saves us from setting up new charge accounts for all customers when this change is made.

1. Ask customer to inform you when they have closed the short period and ran any charge account reports they want to run.
2. Update ‘Start Day of Month’ to be the new start day for the period.