Change Community Meal Plans - Different Type of Plan

Objectives

Successfully change meal plans from one type of plan to another. For example, community is switching from a meal plan to declining dollar.

Prerequisites

• Access to fc_obj_owner user on database where community's data resides
• Access to and familiarity with import templates on J drive

Instructions

If a community is changing type of meal plans and would like our help in setting this up, follow the steps below.

Weeks/Months Leading Up to Meal Plan Change

1. Understand the requirements of the new meal plan.
1. When will the new meal plan start?
2. What is allowanceable?
3. Are there any special requirements (carryover, meal generation, etc).
4. What is their transition plan, if any, from one plan to another?
2. If the meal plan 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 meal plan 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 Meal Plan Changes

1. Set up new meal plans under Plans tab according to requirements of customer.
2. 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.
3. Run the following query to get a list of residents who are end-dated, but their accounts are not. Update the accounts with an effective end date. This again, helps us to not create new meal plan accounts for residents who are no longer effective.
1. select account_number, last_name, first_name, rs.effective_end_date from accounts ac inner join resident_accounts ra on ra.account_id = ac.account_id inner join 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;

4. Find the community plan id of the plan that will no longer be effective.
1. select * from community_plans where community_id = <Community Id>  ;

5. Get a count of how many accounts will need to be created.
1. select count(*) from accounts where community_plan_id = <Community Plan Id> and effective_end_date is null;

6. 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.

7. 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.
1. select ra.resident_id, ra.account_id, ra.billing_id  from accounts ac inner join resident_accounts ra on ac.account_id = ra.account_id inner join 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;

8. End date existing accounts. Replace 01/31/2015 with the correct end date.
1. 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;

9. Get list of new account ids to match with template.
1. select account_id, community_plan_id from accounts where community_plan_id = <New Community Plan Id>  and created_by = <your username> and created_date =trunc(sysdate) order by account_id;

10. 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.

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