Thursday Night Process

Purpose
The purpose of the Thursday Night Process, aka RY/AD Package, is to calculate and invoice Finance charges, Weekly Royalty, Advertising and Additional Funds charges, Master Franchise charges, Brand Remittance charges, and all associated taxes. 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 Thursday nights at 5PM EST. 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 and posted for Weekly Charges and the associated 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 a series of Invoice Session Registers reports from SSRS. Since this relies on Web Services, this section continues to run after disconnect errors. It will try up to five times.

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 EFT Register Files and Finance Charge Reports. This is done dynamically because the file names include the year, month and day.

The initial report step to run is set to Run RY-AD In Parallel also during this task.

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 ARTaxDetail, InvoiceItemPosted, InvoicePosted, InvoicePostedHistory, InvoiceItemPostedHistory, Sales.dbo.WeeklySummary for the UserStamp SSISDev and SSISProd. SSISProd is used in the testing environment.

Check Preconditions RY-AD
This task runs the stored procedure ^^proc_Subway_Royalty_Advertising_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 finance charges or Weekly Charges. 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.

Check Preconditions for Master Franchise
This task runs the stored procedure ^^proc_Subway_Royalty_Advertising_CheckPreconditions_MasterFranchise$$. This stored procedure checks to see if the General Ledger Account Numbers are set up for all the Master Franchisees about to be charged Weekly Charges. 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.

Prepare Database
This task was put in to make the data manipulation queries run faster. It does this by updating the statistics of the following tables


 * AccountBalance
 * InvoiceItemUnposted
 * InvoiceItemUnpostedHistory
 * InvoiceItemUnpostedHistory
 * Sales.dbo.WeeklySummary
 * Sales.dbo.ARSubLedger

It also recompiles the stored procedure ^^proc_Subway_Royalty_Advertising_CreateCharges_Batch$$

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.

Calculate Finance Charges For Stores
This task runs the stored procedure ^^proc_Subway_Royalty_Advertising_CreateFinanceCharges$$. This stored procedure calculates finance charges per Store based upon the previous weeks balance and the entries in the FinanceChargeAssignment table.

The charges are then posted to the Invoice tables using the stored procedure ^^proc_Subway_InvoicePosting_Batch$$. The minimum and Maximum InvoiceIds are returned to the package.

Calculate Finance Charges For DA
This task runs the stored procedure ^^proc_Subway_Royalty_Advertising_CreateFinanceChargesForDA$$. This stored procedure calculates finance charges per Development Agent based upon all the records in the AdjustmentUnposted table that are not Credit Invoice or Cash Receipt.

The charges are then posted to the Invoice tables using the stored procedure ^^proc_Subway_InvoicePosting_Batch$$. The minimum and Maximum InvoiceIds are returned to the package.

Charge RY AD
This task runs the stored procedure ^^proc_Subway_Royalty_Advertising_CreateCharges_Batch$$. This stored procedure creates charges for Royalties and Advertisement weekly fees for all the Stores based upon the Weekly Sales and Sales Corrections. This stored procedure is relies on the information to be populated in the Sales.dbo.SalesInfoHistory table. This procedure also created a negative charge of the entire amount for those stores that fall under a Master Franchisee. Those charges are created in the next step Charge Master Franchise.

The charges are then posted to the Invoice tables using the stored procedure ^^proc_Subway_InvoicePosting_Batch$$. The minimum and Maximum InvoiceIds are returned to the package.

Charge Master Franchise
This task runs the stored procedure ^^proc_Subway_Royalty_Advertising_CreateMasterFranchiserCharges_Batch$$. This stored procedure creates charges for Royalties and Advertisement weekly fees for all the Master Franchisees. The invoice is created in a way so that there is one invoice for each Master Franchisee with Invoice Items for each store they own.

The charges are then posted to the Invoice tables using the stored procedure ^^proc_Subway_InvoicePosting_Batch$$. The minimum and Maximum InvoiceIds are returned to the package.

Charge AF
This task runs the stored procedure ^^proc_Subway_AdditionalFunds_CreateCharges_Batch$$. This stored procedure creates charges for Additional Funds fees for all the Stores. Charges can either be based upon a Fixed Amount or a Percentage Amount of the Weekly Sales.

The charges are then posted to the Invoice tables using the stored procedure ^^proc_Subway_InvoicePosting_Batch$$. The minimum and Maximum InvoiceIds are returned to the package.

Charge Brand Remittance
This task runs the stored procedure ^^proc_Subway_Royalty_Advertising_CreateBrandCharges_Batch$$. This stored procedure calculates the amount to keep and remit for each store and Brand sold as recorded in the table ^^BrandSalesInfoHistory$$. There are four possible type of charges Keep Percent, Remit Percent, Keep Percent Correction and Remit Percent Correction. The item type for all the General Ledger Account Numbers are unique for each Brand. All the available Brands can be found in the ^^BrandMasterFile$$ table.

The charges are then posted to the Invoice tables using the stored procedure ^^proc_Subway_InvoicePosting_Batch$$. The minimum and Maximum InvoiceIds are returned to the package.

Calculate taxes for RY AD AF
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.

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.

EFT Adjustment
This task runs the stored procedure ^^proc_Subway_Royalty_Advertising_EFTAdjustments$$. This stored procedure is run three times in a loop. Once for each balance type Royalties, Advertising, and Additional Funds. 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_Royalty_Advertising_AdditionalFunds_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 Royalties, Advertising, or Additional Funds. 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$$. Because this section relies on the external web service it is susceptible to connection and time out issues.

To mitigate this issue this section can error up to five (5) times before failing the package. This is done by the start task Restart The Reports. This step does nothing by itself but the control flow is directed by the conditional precedence steps proceeding from this step checking the value of the variable RestartStep. In the beginning of the package RestartStep is set to Run RY-AD In Parallel. Then each report is run in linear fashion. When a report fails an event handler fires OnError which records the name of the step that fails. Thus when the container is re-ran after the error the Restart The Reports step directs the control flow to the report that error out and continues from there.

The final step Task Succeeded no need to retry set the NumberOfRetries to 1000 essentially exited out of the container. If five errors are encountered before all the reports finish, the package stops executing.

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: RY AD Report Thread 1
 * Report Name: /Subway/Accounting/Invoicing/Invoice Session Register Batch
 * Parameters: @RYStartInvoiceOne, @RYEndInvoiceOne


 * Task Name: RY AD Report Thread 2
 * Report Name: /Subway/Accounting/Invoicing/Invoice Session Register Batch
 * Parameters: @RYStartInvoiceTwo, @RYEndInvoiceTwo


 * Task Name: MF RY AD Report
 * Report Name: /Subway/Accounting/Invoicing/Invoice Session Register Batch
 * Parameters: @MFStartInvoice, @MFEndInvoice


 * Task Name: Brand RY and AD report
 * Report Name: /Subway/Accounting/Invoicing/Invoice Session Register Batch
 * Parameters: @BrandStartInvoice, @BrandEndInvoice


 * Task Name: Create RY Eft Register File
 * Report Name: /Subway/Accounting/Accounting/Accounts Receivable Activity Register
 * Parameters: BalanceID;26


 * Task Name: Create AD Eft Register Report File
 * Report Name: /Subway/Accounting/Accounting/Accounts Receivable Activity Register
 * Parameters: BalanceID;9


 * Task Name: AF Report Thread
 * Report Name: /Subway/Accounting/Invoicing/Invoice Session Register Batch
 * Parameters: @AFStartInvoiceOne, @AFEndInvoiceTwo


 * Task Name: Create AF Eft Register Report
 * Report Name: /Subway/Accounting/Accounting/Accounts Receivable Activity Register
 * Parameters: BalanceID;10


 * Task Name: Finance Charges Invoices For Store
 * Report Name: /Subway/Accounting/Accounting/Finance Charge Register
 * Parameters: @FinanceChargeStartInvoice, @FinanceChargeEndInvoice


 * Task Name: Finance Charges Invoices For DA
 * Report Name: /Subway/Accounting/Accounting/Finance Charge Register
 * Parameters: @FinanceChargeForDAStartInvoice, @FinanceChargeForDAEndInvoice


 * Task Name: Finance Charges Register RY For Store
 * Report Name: /Subway/Accounting/Accounting/Finance Charge Register
 * Parameters: BalanceId;26~EntityType;7


 * Task Name: Finance Charges Register AD For Store
 * Report Name: /Subway/Accounting/Accounting/Finance Charge Register
 * Parameters: BalanceId;9~EntityType;7


 * Task Name: Finance Charges Register RY For DA
 * Report Name: /Subway/Accounting/Accounting/Finance Charge Register
 * Parameters: BalanceId;26~EntityType;26


 * Task Name: Finance Charges Register AD For DA
 * Report Name: /Subway/Accounting/Accounting/Finance Charge Register
 * Parameters: BalanceId;9~EntityType;26

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.

AR Tax For All Stores
This runs the stored procedure ^^proc_Report_ARTaxInvoices$$ 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 ARTaxFileAll

Get Email List
This runs the stored procedure ^^proc_Subway_EmailList_ArTaxes$$ 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_ARTaxInvoices$$ with a parameter of the coordinator number. The output of the stored procedure is dumped to a text file defined in the connection ARTaxFileStore. 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 @RemoteARTaxFiles to @ImagingPathTaxes

The PDF reports are moved from @RemoteInvoices to @ImagingPathInvoice.

Note the @User::RemoteInvoices is different then the directory used to create the reports. The directory used to create the reports is local to the location the web service runs on. The directory to pick up the reports in relative to the location the package is executed on.

Increment Dates
This runs the stored procedure ^^proc_Subway_Increment_Date$$ three times with for the three different balance types Royalties, Advertising, and Additional Funds. This increments the Week Ending Dates by seven days.

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

ARTaxFileAll
This is the connection to the text file that the AR Tax report for all stores is saved to.

ARTaxFileStore
This is the connection to the text file that the AR 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.