Deloitte Loan Life & Valuation

mc_runoff.bas

Option Compare Database
'
'------------------------------------------------------------------------------>
'

Public Function RunOffData()

' RunOff Data

' Requires dbo_LenderInfoMmmYY for 13 Months
' Requires dbo_CustomerDB, dbo_BankCustLink, dbo_Franchisees, dbo_Lenders
' Requires dbo_RunOff Table Template

    'Variables
    Dim rsLenderInfoTables As Recordset
    Dim strYear As String
    Dim strMonth As String
    Dim strPeriod As String
    Dim strDate As String
    Dim dtDateCur As Date
    Dim dtDatePrior As Date
    Dim dtStart As Date
    Dim dtEnd As Date
    Dim strLenderInfo As String
    Dim strLenderInfoCur As String
    Dim strLenderInfoPrior As String
    Dim strBankCustLink As String
    Dim strCustomerDB As String
    Dim strFranchisees As String
    Dim strVersion As String
    Dim strSQL As String
    
    'Create RunOff Table
    strVersion = Format(Now(), "yyyymmddhhmmss")
    Debug.Print strVersion
    
    'Create Analysis Table
    On Error Resume Next
    CurrentDb.TableDefs.Delete "dbo_RunOff_Analysis"
    DoCmd.CopyObject , "dbo_RunOff_Analysis", acTable, "dbo_RunOff_"
    
    'Create Loanbook Table
    On Error Resume Next
    CurrentDb.TableDefs.Delete "dbo_RunOff_Loanbook"
    DoCmd.CopyObject , "dbo_RunOff_Loanbook", acTable, "dbo_RunOff_"
    
    'Create Query LenderInfo Tables
    On Error Resume Next
    CurrentDb.QueryDefs.Delete "qryLenderInfoTables"
    CurrentDb.CreateQueryDef "qryLenderInfoTables", _
        "SELECT MSysObjects.Name " & _
        "FROM MSysObjects " & _
        "WHERE MSysObjects.Name Like '*_LenderInfo*' " & _
            "And MSysObjects.Name Not Like '*qry*' " & _
        "ORDER BY MSysObjects.Name; "
    
    'Set Recordset LenderInfo Tables
    Set rsLenderInfoTables = CurrentDb.OpenRecordset("qryLenderInfoTables")
    
    'Loop LenderInfo Tables
    While Not rsLenderInfoTables.EOF
        
        'Set Period & Current Date
        strYear = Right(rsLenderInfoTables!Name, 2)
        strMonth = Mid(rsLenderInfoTables!Name, 15, 3)
        strDate = "01-" & strMonth & "-" & strYear
        dtDateCur = strDate
        strPeriod = Format(dtDateCur, "yyyy") & Format(dtDateCur, "mm")
        
        'Set Start Date (US Format)
         dtStart = Format(dtDateCur, "mm/dd/yyyy") 'US Format
        
        'Set Current LenderInfo
        strLenderInfoCur = rsLenderInfoTables!Name
        
        'Set Prior LenderInfo
        strLenderInfo = Left(rsLenderInfoTables!Name, 14)
        dtDatePrior = DateAdd("m", -1, dtDateCur)
        strLenderInfoPrior = strLenderInfo & _
            Format(dtDatePrior, "mmm") & Format(dtDatePrior, "yy")
        
        'Set BankCustLink
        strBankCustLink = "dbo_BankCustLink"
        
        'Set CustomerDB
        strCustomerDB = "dbo_CustomerDB"
        
        'Set Franchisees
        strFranchisees = "dbo_Franchisees"
        
        'Debug
        Debug.Print "Period: " & strPeriod
        
        'Create RunOff_Analysis Query
        On Error Resume Next
        CurrentDb.QueryDefs.Delete "qry_RunOff_Analysis"
        CurrentDb.CreateQueryDef "qry_RunOff_Analysis", _
            "INSERT INTO dbo_RunOff_Analysis ( Period, LenderInfoID, Franchise, LenderID, State, Status, LoanAmount, LoanDate, Duration, Balance_Start, Balance_End, Active ) " & _
            "SELECT '" & strPeriod & "' AS Period, " & strLenderInfoCur & ".LenderInfoID, " & strCustomerDB & ".[Franchisee Old] AS Franchise, " & strLenderInfoCur & ".LenderID, " & strFranchisees & ".State, " & strFranchisees & ".CommissionStatus AS Status, " & strLenderInfoCur & ".LoanAmount, IIf([" & strLenderInfoCur & "].[LoanDate] Is Null,[" & strCustomerDB & "].[SettlementDate],[" & strLenderInfoCur & "].[LoanDate]) AS LoanDate, DateDiff('m',[LoanDate],#" & dtStart & "#) AS Duration, " & strLenderInfoPrior & ".OutstandingBalance AS Balance_Start, " & strLenderInfoCur & ".OutstandingBalance AS Balance_End, IIf([" & strLenderInfoCur & "].[OutstandingBalance]=0,0,1) AS Active " & _
            "FROM (((" & strLenderInfoCur & " LEFT JOIN " & strLenderInfoPrior & " ON " & strLenderInfoCur & ".LenderInfoID = " & strLenderInfoPrior & ".LenderInfoID) LEFT JOIN " & strBankCustLink & " ON " & strLenderInfoCur & ".LenderInfoID = " & strBankCustLink & ".BankInfoID) LEFT JOIN " & strCustomerDB & " ON " & strBankCustLink & ".CustDBID = " & strCustomerDB & ".LoanID) LEFT JOIN " & strFranchisees & " ON " & strCustomerDB & ".[Franchisee Old] = " & strFranchisees & ".FranchiseeCode " & _
            "WHERE (((IIf([" & strLenderInfoCur & "].[LoanDate] Is Null,[" & strCustomerDB & "].[SettlementDate],[" & strLenderInfoCur & "].[LoanDate])) Is Not Null) AND ((" & strLenderInfoPrior & ".OutstandingBalance)>0)); "
        'strSQL = _
            "INSERT INTO dbo_RunOff_Analysis ( Period, LenderInfoID, Franchise, LenderID, State, LoanAmount, LoanDate, Duration, Balance_Start, Balance_End, Active ) " & _
            "SELECT '" & strPeriod & "' AS Period, " & strLenderInfoCur & ".LenderInfoID, " & strCustomerDB & ".[Franchisee Old] AS Franchise, " & strLenderInfoCur & ".LenderID, " & strFranchisees & ".State, " & strLenderInfoCur & ".LoanAmount, IIf([" & strLenderInfoCur & "].[LoanDate] Is Null,[" & strCustomerDB & "].[SettlementDate],[" & strLenderInfoCur & "].[LoanDate]) AS LoanDate, DateDiff('m',[LoanDate],#" & dtStart & "#) AS Duration, " & strLenderInfoPrior & ".OutstandingBalance AS Balance_Start, " & strLenderInfoCur & ".OutstandingBalance AS Balance_End, IIf([" & strLenderInfoCur & "].[OutstandingBalance]=0,0,1) AS Active " & _
            "FROM (((" & strLenderInfoCur & " LEFT JOIN " & strLenderInfoPrior & " ON " & strLenderInfoCur & ".LenderInfoID = " & strLenderInfoPrior & ".LenderInfoID) LEFT JOIN " & strBankCustLink & " ON " & strLenderInfoCur & ".LenderInfoID = " & strBankCustLink & ".BankInfoID) LEFT JOIN " & strCustomerDB & " ON " & strBankCustLink & ".CustDBID = " & strCustomerDB & ".LoanID) LEFT JOIN " & strFranchisees & " ON " & strCustomerDB & ".[Franchisee Old] = " & strFranchisees & ".FranchiseeCode " & _
            "WHERE (((IIf([" & strLenderInfoCur & "].[LoanDate] Is Null,[" & strCustomerDB & "].[SettlementDate],[" & strLenderInfoCur & "].[LoanDate])) Is Not Null) AND ((" & strLenderInfoPrior & ".OutstandingBalance)>0)); "
        'Debug.Print "Analysis: " & strSQL
        
        'Create RunOff_Loanbook Query
        On Error Resume Next
        CurrentDb.QueryDefs.Delete "qry_RunOff_Loanbook"
        CurrentDb.CreateQueryDef "qry_RunOff_Loanbook", _
            "INSERT INTO dbo_RunOff_Loanbook ( Period, LenderInfoID, Franchise, LenderID, State, Status, LoanAmount, LoanDate, Duration, Balance_Start, Balance_End, Active ) " & _
            "SELECT '" & strPeriod & "' AS Period, " & strLenderInfoCur & ".LenderInfoID, " & strCustomerDB & ".[Franchisee Old] AS Franchise, " & strLenderInfoCur & ".LenderID, " & strFranchisees & ".State, " & strFranchisees & ".CommissionStatus AS Status, " & strLenderInfoCur & ".LoanAmount, IIf([" & strLenderInfoCur & "].[LoanDate] Is Null,[" & strCustomerDB & "].[SettlementDate],[" & strLenderInfoCur & "].[LoanDate]) AS LoanDate, DateDiff('m',[LoanDate],#" & dtStart & "#) AS Duration, " & strLenderInfoPrior & ".OutstandingBalance AS Balance_Start, " & strLenderInfoCur & ".OutstandingBalance AS Balance_End, IIf([" & strLenderInfoCur & "].[OutstandingBalance]=0,0,1) AS Active " & _
            "FROM (((" & strLenderInfoCur & " LEFT JOIN " & strLenderInfoPrior & " ON " & strLenderInfoCur & ".LenderInfoID = " & strLenderInfoPrior & ".LenderInfoID) LEFT JOIN " & strBankCustLink & " ON " & strLenderInfoCur & ".LenderInfoID = " & strBankCustLink & ".BankInfoID) LEFT JOIN " & strCustomerDB & " ON " & strBankCustLink & ".CustDBID = " & strCustomerDB & ".LoanID) LEFT JOIN " & strFranchisees & " ON " & strCustomerDB & ".[Franchisee Old] = " & strFranchisees & ".FranchiseeCode ;"
        'strSQL = _
            "INSERT INTO dbo_RunOff_Loanbook ( Period, LenderInfoID, Franchise, LenderID, State, LoanAmount, LoanDate, Duration, Balance_Start, Balance_End, Active ) " & _
            "SELECT '" & strPeriod & "' AS Period, " & strLenderInfoCur & ".LenderInfoID, " & strCustomerDB & ".[Franchisee Old] AS Franchise, " & strLenderInfoCur & ".LenderID, " & strFranchisees & ".State, " & strLenderInfoCur & ".LoanAmount, IIf([" & strLenderInfoCur & "].[LoanDate] Is Null,[" & strCustomerDB & "].[SettlementDate],[" & strLenderInfoCur & "].[LoanDate]) AS LoanDate, DateDiff('m',[LoanDate],#" & dtStart & "#) AS Duration, " & strLenderInfoPrior & ".OutstandingBalance AS Balance_Start, " & strLenderInfoCur & ".OutstandingBalance AS Balance_End, IIf([" & strLenderInfoCur & "].[OutstandingBalance]=0,0,1) AS Active " & _
            "FROM (((" & strLenderInfoCur & " LEFT JOIN " & strLenderInfoPrior & " ON " & strLenderInfoCur & ".LenderInfoID = " & strLenderInfoPrior & ".LenderInfoID) LEFT JOIN " & strBankCustLink & " ON " & strLenderInfoCur & ".LenderInfoID = " & strBankCustLink & ".BankInfoID) LEFT JOIN " & strCustomerDB & " ON " & strBankCustLink & ".CustDBID = " & strCustomerDB & ".LoanID) LEFT JOIN " & strFranchisees & " ON " & strCustomerDB & ".[Franchisee Old] = " & strFranchisees & ".FranchiseeCode ;"
        'Debug.Print "Loanbook: " & strSQL
        
        'Run Query LenderInfo Data
        If strPeriod = "201403" Then 'Dynamic First
            'Skip, No Prior LenderInfo Table
            Debug.Print "Skipping..."
        Else
            CurrentDb.Execute "qry_RunOff_Analysis"
            CurrentDb.Execute "qry_RunOff_Loanbook"
        End If
        
        'Delete Analysis & Loanbook Queries
        CurrentDb.QueryDefs.Delete "qry_RunOff_Analysis"
        CurrentDb.QueryDefs.Delete "qry_RunOff_Loanbook"
        
        'Loop Next Table
        rsLenderInfoTables.MoveNext
        
    Wend
    
    'Delete Query LenderInfo Tables
    On Error Resume Next
    CurrentDb.QueryDefs.Delete "qryLenderInfoTables"
    
    'Debug
    Debug.Print ""
    Debug.Print "Cocktails!"
    
End Function
'