Reality Monthly Process

Purpose
The purpose of the Reality Monthly Process (aka RE Package) is to charge those Stores that lease their property the correct Rent, Upcharge and Taxes. This amount is then set for those stores that have EFT transfers such that the amount may be pulled on a weekly bases. After there charges are created a series of reports are generated and sent to imaging, including Invoice Registers and Activity Registers.

Process
The entire process is executed from an SSIS package. This package will be run on once a month when **nobody knows right now**. The process is broken down into a few major components.

The preparations steps check to ensure all the General Ledger Account Numbers are in place. It also prepares the database to run large data creation queries.

The Data Manipulation component starts a new transaction. If any of the individual steps fail all the data will be rollback. In this portion of the package the invoices are created for Rent, Upcharges, and Taxes. Once this section is completed the transaction is committed. If the package were to fail after this point the data is still stored and the invoices numbers created is logged to a SSIS database.

The Reports section run reports for Invoice Registers, Activity Register, Summary Report, and a Non Preauthorized Stores report from SSRS. This is done through the custom competent

The Tax Report section is separated from the other report section because these are text file reports instead of PDF reports. The first report is all taxes in one large report. Then a report is created and emailed for each individual Coordinator.

The Closing steps of the process include copying all the reports created locally over to the Imaging server, deleting the local copies of the reports and incrementing the Week Ending Dates for Balance Types Royalty, Advertising, and Additional Funds. An Email is then sent out to the NetDeveloper group indicating the package has completed successfully.

Preparation Steps
The Preparation Steps where put in place to set up dynamic variables, check the integrity of the data, and reorganized the tables so they are queried with maximum efficiency.

Set FilePaths
This task sets up the file name for the Activity Register, Summary and Non Preauthorized Stores Reports. This is done dynamically because the file names include the year, month and day.

Precondition Check
This task runs the stored procedure ^^proc_Subway_Realty_CheckPreconditions^^. This stored procedure checks to see if the General Ledger Account Numbers are set up for all the stores about to be charged either Rent, Upcharges or Tax. If there are some General Ledger Account Numbers missing that information is passed back in a result set. The result set is then emailed to the NetDeveloper group and the package terminates at that point.

Clear Database DEV ONLY
This task was added to make the package re-runable in a development environment. This task only executes if the parameter ProductionMode is 0(zero). This task removes records from the tables RECharges, ARTaxDetail, InvoiceItemPosted InvoicePosted, InvoicePostedHistory, InvoiceItemPostedHistory, Sales.dbo.WeeklySummary for the UserStamp SSISDev and SSISProd for Balance Type Reality (23). SSISProd is used in the testing environment.

Data Manipulation
The Data Manipulation steps were place in a container that starts a transaction. This allows all of the steps to be under one transaction and if one step fails all previous steps are rolled back. One other advantage is after the container is run successfully a Post Execute step can run and log the state of all the variables created.

RE Charge UpCharges
This task runs the stored procedure ^^proc_Subway_Realty_CreateCharges_Batch^^. This stored procedure creates the Base Rent for stores that lease there properties every month. The data of which store lease their properties comes from the table StoreLease.

The charges are then posted to the Invoice tables using the stored procedure ^^proc_Subway_InvoicePosting_Batch^^. The charges created are also logged to the table ^^ARTaxDetail^^ The minimum and Maximum InvoiceIds are returned to the package.

Calculate taxes for RE
This task runs the stored procedure ^^proc_Subway_ARTaxInvoices_CreateCharges_Batch^^. This stored procedure creates taxes for any taxable Invoice Account Type item created for the current week ending date for all the any of the three balance types, Royalties, Advertising, and Additional Funds. Stores are match to taxes in the table ^^ARTaxMasterfile^^ based upon there tax address. Taxes can be applied at the country, state, county or city level.

Realty Process Adjustments
This task runs the stored procedure ^^proc_Subway_Royalty_Advertising_EFTAdjustments^^. The same stored procedure that is used for Royalty is used for Reality since we can pass any balance type into this procedure. The parameter of Balance Type of 'RE' is used for this task. This stored procedure updates the EFTFile table with the correct EFTAmount based upon the Entities Adjustment Type. See the stored procedure page for the specific algorithms for each Adjustment Type.

Add Beginning Balance
This task runs the stored procedure ^^proc_Subway_Realty_Calculate_BeginningBalance^^. This stored procedure create a Beginning Balance record, WeeklySummaryType of 5, for the next Week Ending Date for each Entity in the Account Balance Table with a Balance Type of Reality. The Beginning Balance for the next week is equal to the Beginning Balance of the previous week plus all the Invoices Posted minus all the Cash Receipts posted.

Logging
The Logging can be found under Event Handlers for the Executable Data Manipulation for OnPostExecute. This event handler saves the state of all variables after Data Manipulation completes successfully and the transaction is committed. This will allow the development team to see the invoices that were created and manually re-run a report if needed.

This process has three steps

First Step (This does nothing)
This first step does nothing. It only exist so a conditional precedence can be created. This event handler is called by the container Data Manipulation and all of its children steps. The conditional precedence checks to make sure the caller of the event handler is the container Data Manipulation and not one of the children which allows logging to only occur after the entire container is executed successfully.

Create XML
This creates a stored procedure. call with an xml formatted string as the parameter. The xml formatted string has the name and value of the variables passed into the script. To log any new variables they must be added to the Read-only Variable collection of this task.

The stored procedure name is called ^^InsertIntermediateResults^^

Execute Sql Task
This runs the sql statement created in the step Create XML. This stored procedure takes the xml formatted string and saves its contents to the table ^^ETL.SSISPackageVariableState^^

Note: The stored procedure ^^InsertIntermediateResults^^ and table ^^ETL.SSISPackageVariableState^^ must be in the database in the _db_SSISConfig connection, not the _db_CookieSubprod connection

Reports
This container runs a series of task that call a web service which executes various reports and creates a PDF output. This is done by using a custom in-house SSIS component, ^^Web Method Call Task^^. These reports are run in sequential order to ensure that only one report is run at a time. Otherwise there is a greater change of the web service and the report server would get overloaded with multiple request at the same time. Because this step is run outside of the transaction in the previous step a failure at this point in the package will not cause a rollback. The values used to run the report can be found in the log table ETL.SSISPackageVariableState

Below is a list of each report run and the Parameter passed in. Values starting with @ represents a dynamic parameter created in the Data Manipulation container.


 * Task Name: INVOICE REGISTER FILE
 * Report Name: /Subway/Accounting/Invoicing/Invoice Session Register Batch
 * Parameters: @MinInvoiceId, @MaxInvoiceId


 * Task Name: AR Activity Register
 * Report Name: /Subway/Accounting/Accounting/Accounts Receivable Activity Register
 * Parameters: BalanceID;23


 * Task Name: AR Summary Report
 * Report Name: /Subway/Accounting/Accounting/AR Summary Report
 * Parameters: BalanceID;23


 * Task Name: Non Preauthorized Stores
 * Report Name: /Subway/Legal/Collections/Non Preauthorized Stores
 * Parameters: BalanceID;23

Tax Reports
This section generates tax receipt reports in plain text format. One report is created for all Coordinators and also one file for each individual coordinator.

RE Tax For All Stores
This runs the stored procedure ^^proc_Report_RETaxInvoices^^ with a parameter of 0, which represents all stores. The output of the stored procedure is dumped to a text file defined in the connection RETaxFileAll

Get Email List
This runs the stored procedure ^^proc_Subway_EmailList_RETaxes^^ which returns a list of Person Number, Email Address, and File Name format for each coordinator. This recordset is then loop through in the next step to create a tax receipt file for each coordinator.

AR Taxes for Individual Stores
This runs the stored procedure ^^proc_Report_RETaxInvoices^^ with a parameter of the coordinator number. The output of the stored procedure is dumped to a text file defined in the connection RETaxFileStore. This file is then email to each individual coordinator at the email provided. If a email is not available for the coordinator the file is emailed to Carol Peterson.

Clean Up
The Clean Up phase takes all the report files which were created locally and moves them into imaging. Then it removes the local version of the files. It also increments the Week Ending Dates by seven days and sends an email to the developers informing that the package has completed all the steps successfully.

The tax reports are moved from @RootRETaxFiles to @ImagingPathTaxes

Increment Dates
This runs the stored procedure ^^proc_Subway_Increment_Date^^ with the balance type or Reality, RE. This increments the Week Ending Dates by one month.

_db_CookieSubprod
This is the connection to the AR/Subs database

_db_SSISConfig
This is the setting to the FWHDBA database which contains all the other settings for the package. This connection is defined by the server located in the Environment variable SSIS_CONFIG

_LogFile
This is the default Log file which logs the process of each step in the package. This is equal to the log that you see in the Progress window.

_SMTP_Connection_Mgr
This is the default connection for each SSIS Mail Task. This package has three mail task, Send Error, Send AR Tax Report, Send Completion.

RETaxFileAll
This is the connection to the text file that the RE Tax report for all stores is saved to.

RETaxFileStore
This is the connection to the text file that the RE Tax report for individual stores is saved to. The connection string, which includes the file path and file name, changes each time through the loop.