Docstoc

BDM Change Specification - DOC

Document Sample
BDM Change Specification - DOC Powered By Docstoc
					CSC
2006-06-23 BDM Add Flags Change Specification
Version 1.0 06/23/2006

1. Changes Overview
    Add 3 new flags to the BDM, the Fully Disbursed Flag, the Floor Flag, and the In-School Consolidation Flag. Get a clear understanding of these flags and document the definition in the Data Dictionary accordingly. If the definition is a separate file from the Servicer then we need to define the procedure of getting that definition monthly. Get a definition of the procedure that is going to review and balance the floor flags monthly or a verification process that CSC can do to give floor flag exceptions. Update the BDM exceptions to track the verification of a floor bond code monthly on the CSC process.

2. Change specifications
2.1 Fully Disbursed Flag – This flag is a Loan level flag that identifies whether this loan is fully disbursed or only partially. A flag value of Y is fully and an N is partially. The definition of a fully disbursed loan is one that has no more pending disbursements due. The criteria for each servicer is defined as follows: AFSA 1. The OLA must be less than or equal to all the disbursement note amounts that make up the loan. 2. OR the last disbursement date is now > 180 days old. The original query limited these to having a PBO > 0 but $0.00 PBO loans are also fully disbursed. 3. In order to implement this on a loan level basis the definition of an AFSA loan is going to have to be changed to match the definition given by AFSA and/or currently used by IS reporting. This is implemented since it is so close and Brian is followingup with AFSA to audit their 40 report. BOSS400 1. The Loan must be in a fully disbursed loans list from BOSS400. The current BOSS file does not have the information to determine fully disbursed. Brazos has Riqui Sedoris run a list and send it to them. We will not be able to fix this until BOSS comes off of peak freeze. Brazos will have to make this list available to CSC on the FTP site at the same time that the servicer data files are on the FTP site.

2. I think this approach should be seen as temporary while Great Lakes clearly identifies with loan end dates or nondisbursed amount which loans are truly fully disbursed. GreatLakes 1. The Loan must be in a fully disbursed loans list from GreatLakes (bz060630). Great Lakes sends a monthly list. Brazos will have to make this list available to CSC on the FTP site at the same time that the servicer data files are on the FTP site. 2. I think this approach should be seen as temporary while Great Lakes clearly identifies with loan end dates or nondisbursed amount which loans are truly fully disbursed. PHEAA 1. The WI_LON_FUL_DSB flag is set to Y. SLMA 1. The query seems to be making an exception for In-School or Deferment Stafford loans but these are fully disbursed if the Non-Disbursed Amount is greater than $0.00. 2. To simplify this and since we have the wonderful field called Non-Disbursed Amount, this logic should be any loan that has a Non-Disbursed Amount = 0 is fully disbursed. 3. I think like AFSA the aged loans should be marked fully disbursed if the last disbursement date is greater than 180 days (There are only 57 of these). Suntech 1. All loans from Suntech are fully disbursed. ELP 1. All loans from ELP are NOT fully disbursed. 2.2 Floor Flag – This flag is a Loan level flag that identifies whether this Loan is part of the floor plan. A value of Y indicates it is a part of the floor plan. The definition of a floor loan is - Any loan purchased with tax exempt money that was issued prior to October 1, 1993 and is guaranteed an interest rate of 9.5 percent to be paid by the US Government if the interest rate of the loan is below 9.5%. i.e. the borrower pays 3.5% interest on a floor loan the government pays us 6.0% to make up the 9.5%. General comment: If the bond issue from the servicing file ends in ‘E’, ‘F’, or ‘R’ then it is a floor loan. This applies to primarily to AES and Suntech. All others you must tag by branch codes or lender codes ‘N’ loans are not eligible for the floor. These loans however are tax exempt. That is why we have previously pulled them. On the upcoming LIBOR deal, they are looking at taking some of the N volume. The criteria for each Servicer is defined below: AFSA The Client Codes (= Lender Numbers? ) are: 1. 'WR', '5Y', 'WQ', 'WV', 'UQ', 'WX', 'YD' 2. Corresponding Bonds are : A02H3N, A02H4T, A03A1N, A03D1N, A03D2T, A99A1N, A99A3T – But these Bonds also have loans from other Client Codes from AFSA. Claborn Software Consultants Company Confidential 11/4/2009 2

AES 1. Lender Code = 833806 and Bond Code IN ('A02H4F', 'A03A1R', 'A03D1R', 'A03D2F') 2. Lender Code = 833806AA and Bond Code IN ('A02H4T', 'A03A1N', 'A03D1N', 'A03D2T', 'A1999A1N') 3. Lender Code = 834086 and Bond Code IN ('A1999A1R', 'A1999A3F') 4. Lender Code = 834086AA and Bond Code IN ('A1999A3T') GreatLakes 1. Lender Code IN ('854806', '855806') SLMA 1. Lender Code = 733806 and Bond Code IN ('4405', '5405', '4404', '5404') 2. Lender Code = 833806 and Bond Code IN ('4442', '4443', '4444', '4445','4446', '5442', '5443', '5444', '5445', '5446', '5454', '4433', '5433', '4431', '4432', '4441', '5431', '5432', '4454', '5441') 3. Lender Code = 834086 and Bond Code IN ('4406', '5406', '4414', '5414') 4. Lender Code = 823817 and Bond Code IN ('4406') (For SSN 137427997) Suntech 1. Bond Code IN ('A02H4F', 'A03A1R', 'A03D1R', 'A03D2F', 'A99A3F', 'A99A1R') 2.3 In-School Consolidations – This flag is a Loan level flag that identifies whether this Loan is an In-School Consolidation. A value of Y indicates it is an In-School Consoldation. The definition of an In-School Consolidation is - a consolidation done by a student before he/she has graduated. These borrowers could get additional Stafford loans and reconsolidate at a later time. These loans are characterized by having an in school deferment where the deferment begin date and the first disbursement date are the same. The criteria for each Servicer is defined below: AFSA 1. The Status has to be DEFERMENT (= 40) and the Loan Program has to be a Consolidation (= ‘C’) and the Deferment Category has to be an [DEF CAT] = ‘S’ and the Deferment Begin Date has to equal the day after the [Grace End Date]. (Some are 12 days after, are these In-School Consolidated?) AES 1. The Status has to be DEFERMENT and the Loan Program has to be a Consolidation (= ‘C’) and the Deferment Category has to be an [LC_DFR_TYP] IN (’15,’18’) and the Deferment Begin Date [LD_DEF_BEG] has to equal the day of the First Disbursement [LD_LON_1_DSB]. GreatLakes 1. The Status has to be DEFERMENT and the Loan Program has to be a Consolidation and the Deferment_Status_Type has to be IN (‘FT’,’HT’) and the Repayment_Begin_Date has to equal the Status_Effective_Date. Claborn Software Consultants Company Confidential 11/4/2009 3

SLMA 1. The Status has to be DEFERMENT and the Loan Program has to be a Consolidation and the Deferment_Status_Type has to be IN (‘DSCH’) and the First Disbursement Date has to equal the Status Begin Date. Suntech 1. There are no In-School Consolidations at Suntech ELP 1. There are no In-School Consolidations at ELP. BOSS400 2. There are no In-School Consolidations at BOSS400.

Claborn Software Consultants

Company Confidential

11/4/2009

4

3. Original Flag Definitions:
Below are the original definitions of the flags that this document attempts to clarify in order to maintain the definition in the Data Dictionary and understand the risks of these flags in production.

FLOOR
(Servicer = 'AFSA' and [Bond Code] in ('WR', '5Y', 'WQ', 'WV', 'UQ', 'WX', 'YD'))) or (Servicer = 'PHEAA' and ( ([Lender Code] = '833806' and [Bond COde] in ('A02H4F', 'A03A1R', 'A03D1R', 'A03D2F')) or ([Lender Code] = '833806AA' and [BOnd COde] in ('A02H4T', 'A03A1N', 'A03D1N', 'A03D2T', 'A1999A1N')) or ([Lender Code] = '834086' and [BOnd COde] in ('A1999A1R', 'A1999A3F')) or ([Lender Code] = '834086AA' and [BOnd COde] in ('A1999A3T')) ) ) or (Servicer = 'GreatLakes' and [Lender Code] in ('854806', '855806')) or (Servicer = 'SLMA' and ( ([Lender Code] = '733806' and [Bond Code] in ('4405', '5405', '4404', '5404')) or ([Lender Code] = '833806' and [Bond Code] in ('4442', '4443', '4444', '4445', '4446', '5442', '5443', '5444', '5445', '5446', '5454', '4433', '5433', '4431', '4432', '4441', '5431', '5432', '4454', '5441')) or ([Lender Code] = '834086' and [Bond Code] in ('4406', '5406', '4414', '5414')) or ([Lender Code] = '823817' and [Bond Code] = '4406') --This is to account for one borrower with ssn = 137427997 --He should be eliminated from the floor b/c of his [Lender] number ) ) or (Servicer = 'Suntech' and [Bond Code] in ('A02H4F', 'A03A1R', 'A03D1R', 'A03D2F', 'A99A3F', 'A99A1R')) Claborn Software Consultants Company Confidential 11/4/2009 5

Fully Disbursed
AFSA SELECT [SOC NBR], COUNT([SOC NBR]) AS Disbments, SUM(OLA) AS DISBAMT, SUM(PBO) AS LoanPBO, [NOTE AMT] AS LoanTotal, MAX([DISB DT]) AS FinalDisbDt, DATEDIFF(dd, MAX([DISB DT]), GETDATE()) AS DisbAge, MIN([FULLY DISB]) AS FullyDisbFlag, [LOAN IDENTIFICATION] AS LoanIdentification, [NOTE DT] AS NoteDate, [CLIENT CD] AS ClientCode, [BENEFIT CD] AS BenefitCode, [PKT NBR], [PORT NBR], [LOAN ID] AS LoanID FROM BrazosStaging.dbo.import_AFSA_Disburse GROUP BY [SOC NBR], [NOTE AMT], [NOTE DT], [CLIENT CD], [LOAN ID], [BENEFIT CD], [PKT NBR], [PORT NBR], [LOAN IDENTIFICATION] HAVING (SUM(OLA) >= [NOTE AMT] OR DATEDIFF(dd, MAX([DISB DT]), GETDATE()) > 180) AND (SUM(PBO) > 0) Bels SELECT FROM

dbo.BDM_CURR_VIEW_BELS.* dbo.BDM_CURR_VIEW_BELS RIGHT OUTER JOIN ISDev.dbo.Bels_Fullydisbursed ON dbo.BDM_CURR_VIEW_BELS.[LOAN NUM] = ISDev.dbo.Bels_Fullydisbursed.loan AND dbo.BDM_CURR_VIEW_BELS.SSN = ISDev.dbo.Bels_Fullydisbursed.ssn GreatLakes SELECT dbo.BDM_CURR_VIEW_GREATLAKES.* FROM dbo.BDM_CURR_VIEW_GREATLAKES RIGHT OUTER JOIN ISDev.dbo.GL_FullyDisb_bz060430 ON dbo.BDM_CURR_VIEW_GREATLAKES.Loan_Token_Number = ISDev.dbo.GL_FullyDisb_bz060430.LoanTokenNumber AND dbo.BDM_CURR_VIEW_GREATLAKES.SSN = ISDev.dbo.GL_FullyDisb_bz060430.SSN Pheaa SELECT dbo.BDM_CURR_VIEW_PHEAA.* FROM dbo.BDM_CURR_VIEW_PHEAA WHERE (WI_LON_FUL_DSB = 'y') SLMA SELECT * FROM dbo.BDM_CURR_VIEW_SLMA WHERE (Status IN ('school', 'deferment')) AND (CAST(REPLACE(REPLACE(NONDISBPRIN, '+', ''), ';', '') AS money) = 0) AND (LoanProgram = 'stafford') AND ([Status Code 2] <> 'ca') OR (CAST(REPLACE(REPLACE(NONDISBPRIN, '+', ''), ';', '') AS money) = 0) AND (LoanProgram <> 'stafford') AND ([Status Code 2] <> 'ca') Suntech SELECT * FROM dbo.BDM_CURR_VIEW_SUNTECH Claborn Software Consultants Company Confidential 11/4/2009 6

In School Consolidations
AFSA select BondIssue, Lender, Trust, Firstname, Lastname, BorrowerState, CompanyShortName, CompanyName, Servicer, Guarantor, LenderName, LoanProgram, SSN, OLA, PBO, AccruedInterest, CappedInterest, InterestRate, InterestRateType, BondCode, StatusBeginDate, StatusEndDate, Status, PortStatus, ProgramCode, GuarantorCode, LenderCode, SubsidyIndicator, FirstDisbursementDate, SepDate,RepaymentTerm, RemainingRepaymentTerm, PaymentsMade, DaysDelinquent, SequenceNumber, RepaymentBeginDate, LoanPart1, LoanPart2, LoanPart3, LoanPart4, LoanType, SchoolType, SchoolState, SchoolCode, 6 AS ProjectID, 'E' AS Grid, DefermentMonths, forbearanceenddate FROM ISProd.dbo.BDM_Curr_View_AFSA_Loans_TO_Packet WHERE ((BondIssue NOT IN ('BOSQ-SPG', 'BOSQ-SPL', 'BOSQUE-LC', 'Unidentified', 'EF05H1T')) AND (PBO > 0) AND (DaysDelinquent < 121) AND (Status <> 'claims') AND (Trust IS NULL) AND ([LOAN TYPE] = 'C') AND (AFSAStatus = '40') AND ([DEF CAT] = 's') AND ([DEF BEG DT] = DATEADD(d, 1, [GRACE END DT])) ) OR ( ([LOAN TYPE] = 'C') AND (AFSAStatus = '40') AND ([DEF CAT] = 's') AND ([DEF BEG DT] = DATEADD(d, 1, [GRACE END DT])) AND (BondIssue IN ('star 94', 'star 95', 'star 95-2', 'p01b1t', 'e02g1t', 'f02e1t', 'b02f1t', 't02d1t')) AND (PBO > 0) AND (DaysDelinquent < 121) AND (Status <> 'claims')) Pheaa select BondIssue, Lender, Trust, Firstname, Lastname, BorrowerState, CompanyShortName, CompanyName, Servicer, Guarantor, LenderName, LoanProgram, SSN, OLA, PBO, AccruedInterest, CappedInterest, InterestRate, InterestRateType, BondCode, StatusBeginDate, StatusEndDate, Status, PortStatus, ProgramCode, GuarantorCode, LenderCode, SubsidyIndicator, FirstDisbursementDate, SepDate,RepaymentTerm, RemainingRepaymentTerm, PaymentsMade, DaysDelinquent, SequenceNumber, RepaymentBeginDate, LoanPart1, LoanPart2, LoanPart3, LoanPart4, LoanType, SchoolType, SchoolState, SchoolCode, 6 AS ProjectID, 'E' AS Grid, DefermentMonths, forbearanceenddate from isprod.dbo.bdm_curr_view_pheaa where ((lc_dfr_typ in ('15', '18') and im_pga_sho in ('cnsldn', 'spcnsl', 'subcns', 'subspc', 'uncns', 'unspc') and LD_DFR_BEG = LD_LON_1_DSB)) Claborn Software Consultants Company Confidential 11/4/2009 7

and ((Trust is null and [bondissue] NOT IN ('BOSQ-SPG', 'BOSQ-SPL', 'BOSQUE-LC', 'Unidentified', 'EF05H1T')) or [bondissue] IN ('star 94', 'star 95', 'star 95-2', 'p01b1t', 'e02g1t', 'f02e1t', 'b02f1t', 't02d1t')) and PBO > 0 and daysdelinquent < 121 and status <> 'claims'

SLMA select BondIssue, Lender, Trust, Firstname, Lastname, BorrowerState, CompanyShortName, CompanyName, Servicer, Guarantor, LenderName, LoanProgram, SSN, OLA, PBO, AccruedInterest, CappedInterest, InterestRate, InterestRateType, BondCode, StatusBeginDate, StatusEndDate, Status, PortStatus, ProgramCode, GuarantorCode, LenderCode, SubsidyIndicator, FirstDisbursementDate, SepDate,RepaymentTerm, RemainingRepaymentTerm, PaymentsMade, DaysDelinquent, SequenceNumber, RepaymentBeginDate, LoanPart1, LoanPart2, LoanPart3, LoanPart4, LoanType, SchoolType, SchoolState, SchoolCode, 6 AS ProjectID, 'E' AS Grid, DefermentMonths, forbearanceenddate from isprod.dbo.bdm_curr_view_slma where ((STATUS1 = 'DSCH' and firstdisbdt = statusbegdt)) and ((Trust is null and [bondissue] NOT IN ('BOSQ-SPG', 'BOSQ-SPL', 'BOSQUE-LC', 'Unidentified', 'EF05H1T')) or [bondissue] IN ('star 94', 'star 95', 'star 95-2', 'p01b1t', 'e02g1t', 'f02e1t', 'b02f1t', 't02d1t')) and PBO > 0 and daysdelinquent < 121 and status <> 'claims' and loanprogram = 'consolidation'

GreatLakes select BondIssue, Lender, Trust, Firstname, Lastname, BorrowerState, CompanyShortName, CompanyName, Servicer, Guarantor, LenderName, LoanProgram, SSN, OLA, PBO, AccruedInterest, CappedInterest, InterestRate, InterestRateType, BondCode, StatusBeginDate, StatusEndDate, Status, PortStatus, ProgramCode, GuarantorCode, LenderCode, SubsidyIndicator, FirstDisbursementDate, SepDate,RepaymentTerm, RemainingRepaymentTerm, PaymentsMade, DaysDelinquent, SequenceNumber, RepaymentBeginDate, LoanPart1, LoanPart2, LoanPart3, LoanPart4, LoanType, SchoolType, SchoolState, SchoolCode, 6 AS ProjectID, 'E' AS Grid, DefermentMonths, forbearanceenddate Claborn Software Consultants Company Confidential 11/4/2009 8

from isprod.dbo.bdm_curr_view_greatlakes where (([Borrower's Repayment Begin Date] = Status_Effective_Date and Deferment_Status_Type in ('FT', 'HT') and status = 'deferment' and [loanprogram] = 'consolidation')) and ((Trust is null and [bondissue] NOT IN ('BOSQ-SPG', 'BOSQ-SPL', 'BOSQUE-LC', 'Unidentified', 'EF05H1T')) or [bondissue] IN ('star 94', 'star 95', 'star 95-2', 'p01b1t', 'e02g1t', 'f02e1t', 'b02f1t', 't02d1t')) and PBO > 0 and daysdelinquent < 121 and status <> 'claims'

Claborn Software Consultants

Company Confidential

11/4/2009

9

4. Testing Results:

In School Consolidation
Servicer AFSA Source isprod.dbo.InSchoolCons_Afsa BDM PBO $ 92,329,800.59 $ 92,329,800.59 $ 175,132,731.32 $ 175,233,557.71 $ 99,769,906.49 $ 128,460,939.49 $ 1,721,086.43 $ 1,607,001.19 # Loans 5,017 5,017 # Borrowers 2,658 2,658

Pheaa

isprod.dbo.InSchoolCons_pheaa BDM

10,586 10,594

5,351 5,357

GL

isprod.dbo.InSchoolCons_gl BDM

4,051 7,109

2,130 2,745

SLMA

isprod.dbo.InSchoolCons_slma BDM

122 108

72 60

Fully Disbursed
Servicer AFSA Source isprod.dbo.FullyDisbursed_AFSA BDM PBO $ 2,778,074,439.45 $ 2,719,826,175.43 $ 257,751,201.13 $ 264,749,733.36 $ 1,111,486,495.65 $ 1,111,486,495.65 $ 5,042,056,791.31 $ 5,042,056,791.31 $ 1,097,115,785.26 $ 1,369,902,177.27 # Loans 570,431 1,089,256 # Borrowers 218,927 363,098

Boss400

isprod.dbo.FullyDisbursed_bels BDM isprod.dbo.FullyDisbursed_GreatLakes BDM

78,976 78,713

29,235 29,866

GreatLakes

89,425 101,556

32,782 33,318

Pheaa

isprod.dbo.FullyDisbursed_Pheaa BDM

450,441 450,223

224,201 224,201

SLMA

isprod.dbo.FullyDisbursed_SLMA BDM

142,482 386,259

78,839 148,467

Claborn Software Consultants

Company Confidential

11/4/2009

10

Floor
Servicer AFSA Source select bondcode, sum(PBO), count(*), count(distinct ssn) from brazos.dbo.loans where servicer = 'afsa' and bondcode in ('WR', 'WQ', 'WV', '5Y', 'UQ', 'WX', 'YD') group by bondcode order by bondcode BondCode 5Y UQ WQ WR WV WX YD PBO $ 1,187,639.26 $ 43,570,731.42 $ 1,183,453.38 $ 15,819,567.01 $ 160,934.88 $ 1,527,016.35 $ 17,583,567.76 $ 81,032,910.06 # Loans 1,524 33,489 926 12,876 390 3,739 12,858 65,802 # Loans 1,524 33,489 926 12,876 390 3,739 12,858 65,802 # Borrowers 2,539 34 425 2,189 611 1,223 # Borrowers 583 12,135 351 4,932 195 1,648 5,411

BondCode select bondcode, sum(PBO), count(*), count(distinct ssn) from brazos.dbo.loans where servicer = 'afsa' and Floor = 'y' group by bondcode order by bondcode 5Y UQ WQ WR WV WX YD

PBO $ 1,187,639.26 $ 43,570,731.42 $ 1,183,453.38 $ 15,819,567.01 $ 160,934.88 $ 1,527,016.35 $ 17,583,567.76 $ 81,032,910.06

# Borrowers 583 12135 351 4932 195 1648 5411

Pheaa Servicer = 'Pheaa'

LenderCode and ((lendercode = '833806' and bondcode in ('A02H4F', 'A03A1R', 'A03D1R', 'A03D2F')) (lendercode = '833806AA' and bondcode in ('A02H4T', 'A03A1N', 'A03D1N', 'A03D2T')) (lendercode = '834086' 833806 833806 833806 833806 834086 834086

BondCode A02H4F A03A1R A03D1R A03D2F A1999A1R A1999A3F

or

or

and bondcode in ('A1999A1R', 'A1999A3F')) (lendercode = '834086AA'

PBO $ 64,494,001.03 $ 158,803.85 $ 9,451,893.02 $ 18,827,488.69 $ 3,986,513.34 $ 22,113,912.20 $ 119,032,612.13

# Loans 4,279 78 770 4,687 1,130 1,892 12,836

or

and bondcode in ('A1999A3T', 'A1999A1N'))) LenderCode where servicer = 'pheaa' and Floor = 'y' 833806 833806 833806 833806 834086 834086 # Borrowers 2,539 34 425 2,189 611

BondCode A02H4F A03A1R A03D1R A03D2F A1999A1R A1999A3F

PBO $ 64,494,001.03 $ 158,803.85 $ 9,451,893.02 $ 18,827,488.69 $ 3,986,513.34 $

# Loans 4,279 78 770 4,687 1,130

Claborn Software Consultants

Company Confidential

11/4/2009

11

22,113,912.20 $ 119,032,612.13 LenderCode servicer = 'GreatLakes' and lendercode in ('854806', '855806') 854806 855806

1,892 12,836 # Borrowers 62 638

1,223

GreatLakes

PBO $ 371,049.04 $ 4,252,166.34 $ 4,623,215.38

# Loans 196 2,086 2,282

LenderCode servicer = 'greatlakes' and Floor = 'y' 854806 855806

PBO $ 371,049.04 $ 4,252,166.34 $ 4,623,215.38

# Loans 196 2,086 2,282

# Borrowers 62 638

SLMA servicer = 'SLMA' and ((lendercode = '733806' and bondcode in ('4405', '5405', '4404', '5404')) or ([LenderCode] = '833806' and [BondCode] in ('4442', '4443', '4444', '4445', '4446', '5442', '5443', '5444', '5445', '5446', '5454', '4433', '5433', '4431', '4432', '4441', '5431', or ([LenderCode] = '834086' and [BondCode] in ('4406', '5406', '4414', '5414')))

LenderCode 733806 733806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 834086 834086 834086

BondCode 4404 5404 4431 4432 4433 4441 4442 4443 4444 4445 4446 4454 5431 5432 5442 5443 5444 5445 5446 4406 4414 5414

PBO $ 7,008,925.37 $ 3,915,898.03 $ 284,268.08 $ 6,361,725.27 $ 10,397.67 $ 5,245,566.41 $ 944,160.75 $ 204,462.16 $ 2,950,723.73 $ 4,915,317.62 $ 1,595,422.32 $ 16,145.97 $ 297,995.11 $ 172,914.35 $ 578,789.66 $ 173,036.50 $ 342,656.68 $ 67,928.92 $ 1,314,162.42 $ 645,811.90 $ 207,387.88 $ 107,080.01 $ 37,360,776.81

# Loans 6,029 2,798 188 322 3 310 519 124 356 298 1,040 2 143 72 264 71 90 26 636 178 97 46 13,612

# Borrowers 3,029 1,710 151 168 2 207 333 91 225 194 807 1 111 37 183 49 73 23 494 89 52 29

Claborn Software Consultants

Company Confidential

11/4/2009

12

LenderCode servicer = 'slma' and Floor = 'y' 733806 733806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 833806 834086 834086 834086

BondCode 4404 5404 4431 4432 4433 4441 4442 4443 4444 4445 4446 5431 5432 5442 5443 5444 5445 5446 4406 4414 5414

PBO $ 7,008,925.37 $ 3,915,898.03 $ 284,268.08 $ 6,361,725.27 $ 10,397.67 $ 5,245,566.41 $ 944,160.75 $ 204,462.16 $ 2,950,723.73 $ 4,915,317.62 $ 1,595,422.32 $ 297,995.11 $ 172,914.35 $ 578,789.66 $ 173,036.50 $ 342,656.68 $ 67,928.92 $ 1,314,162.42 $ 645,811.90 $ 207,387.88 $ 107,080.01 $ 37,344,630.84

# Loans 6,029 2,798 188 322 3 310 519 124 356 298 1,040 143 72 264 71 90 26 636 178 97 46 13,610

# Borrowers 3,029 1,710 151 168 2 207 333 91 225 194 807 111 37 183 49 73 23 494 89 52 29

Suntech servicer = 'Suntech' and bondcode in ('A02H4F', 'A03A1R', 'A03D1R', 'A03D2F','A99A3F', 'A99A1R')

BondCode A02H4F A03A1R A03D1R A03D2F A99A1R A99A3F

PBO $ 9,590,744.06 $ 60,059.72 $ 3,149,121.25 $ 110,550.64 $ 73,333.89 $ 5,299,881.56 $ 18,283,691.12

# Loans 1,459 30 283 76 127 1,191 3,166

# Borrowers 1,043 17 175 47 54 858

BondCode servicer = 'suntech' and Floor = 'y' A02H4F A03A1R

PBO $ 9,590,744.06 $

# Loans 1,459

# Borrowers 1,043

Claborn Software Consultants

Company Confidential

11/4/2009

13

A03D1R A03D2F A99A1R A99A3F

60,059.72 $ 3,149,121.25 $ 110,550.64 $ 73,333.89 $ 5,299,881.56 $ 18,283,691.12

30 283 76 127 1,191 3,166

17 175 47 54 858

Hi Dave, I have copied Brian because he is asking me where we are at in testing the Flags that we added. I am approaching each of the differences highlighted in the spreadsheet. The numbers Kristen gave are what I needed to test against. On the BDM here at CSC I have the following numbers for InschoolConsolidation. I mentioned to Kristen that you can get the same numbers there on the BDM by limiting the InSchool Consolidation flag to CONSOLIDATIONS loan program. I have done that here at CSC but have not redelivered the change. PHEAA BDM 175132731.3200 10587 5351 I have one more loan count because it is a $0.00 balance loan. The InschoolCons_pheaa query does not count these $0.00 loans. Perhaps it should as they still meet the definition of an InschoolConsolidation.
Pheaa isprod.dbo.InSchoolCons_pheaa $ 175,132,731.32 10,586 5,351

No difference.
GL isprod.dbo.InSchoolCons_gl $ 99,769,906.49 4,051 2,130

GreatLakes

BDM

99769906.4900 4051

2130

Our counts are different here because the ISPROD.InSchoolCons_SLMA query includes PLUS loans. When I eliminate the PLUS loans from the ISPROD query, I get the same numbers as the BDM.
SLMA isprod.dbo.InSchoolCons_slma $ 1,721,086.43 122 72

SLMA

BDM

1607001.1900 108

60

For Fully Disbursed AFSA, the biggest difference is that the ISPROD query does not count any PBO < 0. This will obviously affect the loan count since the rollup is not the same as the BDM but it should not have affected the PBO total unless some negative loan amounts made that big of a difference but I doubted it. So I searched for SSNs that were in the ISPROD query but not in the BDM as flagged fully disbursed and I found one for Amanda E Gilman. This borrower is listed with loans in the ISProd query for PBO of 2500 and 100 and a corresponding Note Amount of 5000 and 250. The total disbursed amount for these loans is also only 2500 and 100. So these are not fully disbursed loans yet they are listed in the ISPROD query. Please verify these loans and any others. I think my numbers are correctly listed below counting only PBO > 0 (If that is all you want to count).
AFSA isprod.dbo.FullyDisbursed_AFSA $ 2,778,074,439.45 570,431 218,927

AFSA

BDM

2720708000.8700 552971

217287

For Boss400 I looked at the results of the ISPROD query and because the join to the BELS_FullyDisbursed file is and outer join, there are a lot of loans in the BELS_FullyDisbursed file that are not in the BDM_CURR_VIEW_BELS. This inflates the loan count. The PBO though is lower from the query! This I found was because the query is not able to match on the SSNs that start with 0s. I contend that the BDM is probably correct but please verify one the ISPROD query is able to link to 0 starting SSNs.

Claborn Software Consultants

Company Confidential

11/4/2009

14

Boss400

isprod.dbo.FullyDisbursed_bels

$ 257,751,201.13

78,976

d

BOSS400

BDM

264749733.3600 78713

29866

The even PBO triggered me to check my definition. I have $0.00 PBO loans as being fully disbursed. If this needs to change then please let me know. In other words if a loan was not in the GL list of fully disbursed loans but its PBO was $0.00 then I have it marked as fully disbursed.
GreatLakes isprod.dbo.FullyDisbursed_GreatLakes $ 1,111,486,495.65 89,425 32,782

GreatLakes

BDM

1111486495.6500 101556

33318

PHEAA is a simple difference between the loan definition. The ISPROD uses a view BDM_CURR_VIEW_PHEAA that has a total loan count of 450627. The BDM has only 450409 loan for PHEAA. I think the view is using a lot of outer joins and may be bringing in NULL or $0.00 PBO “ghost” loans that are not affecting the PBO of this fully disbursed query but are affecting the loan count. Please check your view on ISPROD and see if there is a problem in its definition. I think the BDM is correct in this case.
Pheaa isprod.dbo.FullyDisbursed_Pheaa $ 5,042,056,791.31 450,441 224,201

PHEAA

BDM

5042056791.3100 450223

224201

There are two differences in SLMA. The first is that this flag is a definition of what is fully disbursed. It is not meant to apply business logic like claimstatus or loanprogram. But with that in mind I reran this query to match the business logic in the ISPROD query and it looked like the following and gave the second line of results: SELECT Sum(PBO),Count(*),Count(DISTINCT SSN) FROM LOANS WHERE (Servicer = 'SLMA') AND (FullyDisbursed = 'Y') AND ((LOANProgram <> 'STAFFORD' AND [StatusCode2] <> 'CA') OR (STATUS IN ('SCHOOL','DEFERMENT') AND LoanProgram = 'STAFFORD' AND [StatusCode2] <> 'CA')) This is closer to your business specific query named FullyDisbursed_SLMA but not exact. The difference could be the way the ISPROD query is written using string replacements on a money field. Not sure. The query written like that took 11:03 minutes to run. Once it completed I found a borrower on the BDM that was not in this query that perhaps should have been. He is Lawrence Robbins at 2611 N Skipwith and he only has one PLUS loan for PBO of $6666 where the BDM has him with a second loan that is also fully disbursed in the amount of PBO $3334. The OLA for this borrower was $10000 so these 2 loans make that up and it is fully disbursed.
SLMA isprod.dbo.FullyDisbursed_SLMA $ 1,097,115,785.26 142,482 78,839

SLMA SLMA

BDM BDM

1369902177.2700 386259 1097222219.2400 142522

148467 78848

Floor flags have an inherent risk in that it is dependent on the manual communication of the change in bonds that move in and out of the floor project. This one was missed visually and Marian is researching how to identify these in any other way. Automation to the BID may simply take care of it.
833806 4454 $ 16,145.97 2 1

I hope this shows clearly the reason for differences and the need for correct definition. I feel good that these flags are representing their title InschoolConsolidation and FullyDisbursed and Floor. I think the testing revealed that perhaps the queries in ISPROD are not capturing the correct answer because of views on the raw tables being slightly off. I think the apples to apples can work with some more effort on refining the base

Claborn Software Consultants

Company Confidential

11/4/2009

15

views but perhaps the better approach is to measure the output of the BDM against the business need and see if the answer is more correct. If so then I think we have good flags. My thoughts and hopefully help in coming to a business decision about these flags. Thanks for testing them, -Bill H.

 10/9/2006 
Hi Dave, I compared the following two queries and I got the 11 SSNs that were different between them. BDM query: SELECT * FROM Loans WHERE Servicer = „SLMA‟ AND FullyDisbursedFlag = „Y‟ ISProd Query: SELECT * FROM ISPROD.dbo.BDM_CURR_VIEW_SLMA WHERE (CAST(REPLACE(REPLACE(NONDISBPRIN,‟+‟,‟‟),‟;‟,‟‟) AS money) = 0) The differences were 11 borrowers as attached. The first borrower with LoanID 1845188 with a PBO of 1313.00 was listed in the BDM but not listed in the ISProd query because we added to the BDM query a date comparison to the LastDisbursementDate where any loan not fully disbursed over 180 days old is marked as fully disbursed. Once I added that to the ISProd query the two queries gave the same result. New ISProd query: SELECT * FROM ISPROD.dbo.BDM_CURR_VIEW_SLMA WHERE (CAST(REPLACE(REPLACE(NONDISBPRIN,‟+‟,‟‟),‟;‟,‟‟) AS money) = 0) OR (DateDiff(d,LastDisbDt,[Snapshot Date]) > 180) So the only test question this raises now is whether the 180 day aging makes these fully disbursed. I hope this helps you complete your testing. I personally think the 180 day aging is a good thing we added but let me know if it is incorrect and I can change it in the BDM. Thanks, -Bill H.

Claborn Software Consultants

Company Confidential

11/4/2009

16


				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:21
posted:11/5/2009
language:English
pages:16