Notes Weekly/Monthly Process

Purpose
The purpose of the Notes Weekly/Monthly Process is to charge all Entities the notes payments, this can be a fixed amount, interest only or Interest plus a fixed amount. The correct EFT Amounts are set for those Entities that have Electronic Funds Transfer. 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 week 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.

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 the Notes Payment. 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 tow Invoice Registers, one for weekly and one for monthly, Activity Register, Summary Report, Payment File Report and two Interest file reports from SSRS. This is done through the custom competent

The Closing steps of the process ensures that the the Week Ending Dates for Balance Types Notes Receivable is updated along with all the payment dates for the notes that were just processed. 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 report output file names. This is done dynamically because the file names include the year, month and day.

Check Precondition of NR CHARGE INTEREST
This task runs the stored procedure ^^proc_Subway_NotesReceivable_CheckPreconditions^^. This stored procedure checks to see if the General Ledger Account Numbers are set up for all the Entities about to be charged Notes Interest. 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.

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.

NR CHARGE INTEREST Weekly
This task runs the stored procedure ^^proc_Subway_NotesReceivable_ChargeInterest_Batch^^ with the parameter of Payment Type equal to W. This stored procedure creates an invoice for the note's payment total based on the note's adjustment code, fixed, interest only, or interest plus.

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.

NR CHARGE INTEREST Monthly
This task runs the stored procedure ^^proc_Subway_NotesReceivable_ChargeInterest_Batch^^ with the parameter of Payment Type equal to M. This stored procedure creates an invoice for the note's payment total based on the note's adjustment code, fixed, interest only, or interest plus.

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.

NR PAYMENT PROCESS
This task runs the stored procedure ^^proc_Subway_NotesReceivable_PaymentProcess^^. This stored procedure inserts into the table NRPayments which logs all the payments that were calculated in the previous two steps. This is meant to be used as a log to help understand how payments are applied to each note. The EFTFile table is also updated with the correct EFTAmount based upon the Entities Adjustment Type. See the stored procedure page for the specific algorithms for each Adjustment Type.

Create Beginning Balance Records
This task runs the stored procedure ^^proc_Subway_NotesReceivable_Calculate_BeginningBalance^^. This stored procedure create a Beginning Balance record, WeeklySummaryType of 5, for the next Week Ending Date for each Note in the Account Balance Table with a Balance Type of Notes Receivable. 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.

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 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 WEEKLY NOTES
 * Report Name: /Subway/Accounting/Invoicing/Invoice Session Register Batch
 * Parameters: @MinInvoiceIdWeekly, @MaxInvoiceIdWeekly


 * Task Name: INVOICE REGISTER FILE MONTHLY NOTES
 * Report Name: /Subway/Accounting/Invoicing/Invoice Session Register Batch
 * Parameters: @MinInvoiceIdMonthly, @MaxInvoiceIdMonthly


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


 * Task Name: NR PAYMENT FILE
 * Report Name: /Subway/Accounting/Notes Receivable/Notes Payment Register
 * Parameters: {NONE}


 * Task Name: NR WEEKLY INTEREST FILE
 * Report Name: /Subway/Accounting/Notes Receivable/Notes Interest Register
 * Parameters: PaymentInd;W


 * Task Name: NR MONTHLY INTEREST FILE
 * Report Name: /Subway/Accounting/Notes Receivable/Notes Interest Register
 * Parameters: PaymentInd;M

Clean Up
The Clean Up phase increments various dates by the stored procedure ^^proc_Subway_NotesReceivable_ResetDates^^. For those notes with a payment type of Monthly and whose payment date was within the last seven days of the WeekEndingDate have their payment date increased by one month. For those notes with a payment type of Weekly and a payment date equal to the current WeekEndingDate have their payment date increased by seven days. The WeekEndingDate is incremented by seven days for the Balance Type Notes Receivable.

Than an email is sent to the developers informing that the package has completed all the steps successfully.

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