EL Monthly Process

Purpose
The purpose of the Equipment Lease Monthly Process is to charge all Entities the monthly amount and all associated taxes for a given lease. 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 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.

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 Invoice Registers,  AR Summary Report, Accounts Receivable Activity Register, EL Charge Register Report and EL Charge Register By State reports from SSRS. The EL Terminated report is run before anything else this list all the leases that are their last payments. This is done through the custom competent.

The Closing steps of the process ensures that the the Week Ending Dates for Balance Type Equipment Lease is incremented by one month. An Email is then sent out to the NetDeveloper group indicating the package has completed successfully.

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.

EL Terminated
This report shows all the leases that are about to be charged there last payment.

PreCondition Check
This task runs the stored procedure ^^proc_Subway_EquipmentLease_CheckPreconditions^^. This stored procedure checks to see if the General Ledger Account Numbers are set up for all the Entities about to be charged EL Monthly Charge. 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.

EL Re-evaluate Taxes
This task runs the stored procedure ^^proc_Subway_EquipmentLease_RecalculateTaxes^^. This stored procedure first removed from ELTaxAssigned any taxes that have an end date less than or equal to the date the stored procedure is run. Then new taxes are added that have a start date less than or equal to the date the stored procedure is run and are not currently in the ELTaxAssigned table. For those leases affected by the removal or addition of taxes have their BalanceDueAmt, GrossMonthlyPayment, TaxRate and TaxAmt recalculated based upon the new values in the ELMasterFile table.

EL Create Charges
This task runs the stored procedure ^^proc_Subway_EquipmentLease_CreateCharges_Batch^^. This stored procedure creates charges for each lease Monthly Charge and all associated taxes defined in the table ELTaxAssigned. Each tax is now a separate line item on the invoice. The charges are then posted to the Invoice tables using the stored procedure ^^proc_Subway_InvoicePosting_Batch^^. The ELMonthlyCharges table is then truncated and repopulated with all the leases currently charged. The taxes are summed up for each lease so each lease on one row in the table. Finally the PaymentsBilled is increased by one, that amount charged during this stored procedure is added to the Amount Billed column and subtracted from the BalanceDueAmt column for each lease.

The minimum and Maximum InvoiceIds are returned to the package.

EL Process Adjustments
This task runs the stored procedure ^^proc_Subway_Royalty_Advertising_EFTAdjustments^^. This reused the Royalty procedure since any balance type can be passed into the procedure. 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_EquipmentLeasing_Calculate_BeginningBalance^^. This stored procedure create a Beginning Balance record, WeeklySummaryType of 5, for the next Week Ending Date for each Lease in the ELMasterFile Table with a Balance Type of Equipment Lease. 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 SESSION REGISTER BATCH
 * Report Name: /Subway/Accounting/Invoicing/Invoice Session Register Batch
 * Parameters: @MinInvoiceId, @MaxInvoiceId


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


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


 * Task Name: EL Charge Register
 * Report Name: /Subway/Accounting/EquipmentLeasing/Equipment Lease Charge Register
 * Parameters: ReportType;0~CountryFilter;0


 * Task Name: EL Charge Register By State
 * Report Name: /Subway/Accounting/EquipmentLeasing/Equipment Lease Charge Register
 * Parameters: ReportType;1~CountryFilter;216

Clean Up
The Clean Up phase increments the WeekEnding date by one month using the stored procedure ^^proc_Subway_Increment_Date^^. 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.