Import Mid-Month Usage Information

Objectives

Import usage from previous point of sale system to allow for a mid-month go-live date. Communities may also have staggered meal plan start dates that require loading in usage for some residents regardless of the go-live date.

Prerequisites

Access to J drive templates

Access to fc_obj_owner schema on database where data resides

Instructions

1. Identify the correct template to use. There is one template for declining dollar plans and one tempate for meals/points plans.
1. Declining Dollar - J:\FullCount\FC - Implementations\Templates - Development\Declining Dollar Usage Load File.xlsx
2. Meal/Points - J:\FullCount\FC - Implementations\Templates - Development\Meal Plan Usage Load File.xlsx
2. Open the appropriate template.
3. Fill in the Set Up tab.
1. Item Code Id - This is the item code id for the item on the transaction you want to create. Typically an item will be set-up specifically for the purpose of loading in mid-month usage. For example, February 2015 Horizon Usage.
2. User Profile Id - This is the user profile id for the user to be associated with the transaction. This can either be the implementer on the project or a power user on the project.
3. Department Id - This is the department id for the department to be associated with the transaction. An existing department or a new department can be used for this.
4. Community Id - This is the community id for the community to be associated with the transaction.
5. Date Format - This is the transaction date for the transactions. It needs to be in MM/DD/YYYY format.
6. Customer Type Id - This is the customer type id for the customer type to be associated with the transaction. This is typically the resident customer type.
7. Price (Meals/Points Only) - This is the price associated with the meal item being generated. For example, if loading in resident meals, the amount might be $10 per meal. The price field would have 10. This matches the Amount column on the Transactions page.
8. Tax Pct (Meals/Points Only) - This is the tax pct associated with the meal item being generated. This matches the Tax Pct column on the Transactions page.
9. Taxed Amount (Meals/Points Only) - This is the taxed amount associated with the meal item being generated. This matches the Taxed Amount column on the Transactions page.
4. Unhide all columns on the Usage tab.
5. Run a query to fill in the RESIDENT_ID, Last Name, and First Name columns. Two sample queries are displayed before. One is for all residents and one is for residents on a specfic meal plan.
1. All Residents - select resident_id, last_name, first_name from residents where community_id = <Community Id> and person_type = 116 order by last_name;
2. Residents on Specific Meal Plan -  select resident_id, last_name, first_name from residents where community_id = <Community Id> and person_type = 116 and resident_id in (select resident_id from resident_accounts ra inner join accounts ac on  ra.account_id = ac.account_id where ac.community_plan_id = <Community Plan Id>) order by last_name;
6. Copy and paste results into the Usage tab.
7. Hide RESIDENT_ID and SQL columns.
8. Hide Instructions and Set Up tab.
9. Save File As and save in community's implementation folder. DO NOT OVERWRITE TEMPLATE.
10. Send file to customer.
11. Upon receiving the file back from the customer, unhide the RESIDENT_ID and SQL columns on the Usage tab. You can also unhide the Instructions and Set Up tabs.
12. Copy the SQL formula down through all columns
13. Remove any rows that do not have the Usage column filled in.
14. Connect to the database where the data should be imported.
15. Copy the values of the SQL column and paste in SQL Developer.
16. Run the queries in SQL developer and commit to the database.