SELECT loan..ln.l_ln AS Loan, loan..indv_brw.n_last AS Borrower, loan..ln.l_clndr AS Cust_Number, corp..nw_org_unit.l_au AS Orig_AU, corp..nw_org_unit.l_lpa_geog_regn, ( SELECT l_au FROM corp..nw_org_unit b WHERE b.i_nw_org_unit = loan..ln_2.i_nw_prcs_ofc ) AS FS_AU, loan..ln_2.l_satel_regn_au AS Sat_Reg_AU, ( SELECT l_au FROM corp..nw_org_unit b WHERE b.i_nw_org_unit = loan..ln_2.i_team_id ) AS TEAM_ID, loan..ln_sale_stat_hist.c_ln_sale_stat_typ AS Note_Status, loan..ln_2.c_sale_ctgry AS Sale_Cat_Code, loan..ln_2.c_prcsg_pipe AS Pipeline, loan..ln.c_ln_typ AS Ln_Typ, fndoc..ln_dfcy.c_dfcy_act AS Curr_Def_Act, fndoc..ln_dfcy.h_dfcy_act AS Curr_Def_Act_Dt, fndoc..ln_dfcy.i_dfcy_ctg AS Def_Cat_ID, fndoc..dfcy_ctg.n_dfcy_ctg AS Def_Cat_Name, fndoc..ln_dfcy.i_dfcy_desc AS Def_Desc_ID, fndoc..dfcy_desc.n_dfcy_desc AS Def_Desc_Name, fndoc..ln_dfcy.h_dfcy_ident AS Def_ID_Dt, fndoc..ln_dfcy.c_dfcy_srce AS Def_Srce_Code, fndoc..ln_dfcy.i_ln_dfcy AS Ln_Def_ID, fndoc..ln_dfcy.c_rpt_grp AS Rpt_Grp, CASE WHEN fndoc..ln_dfcy.c_dfcy_act = 'SAT' THEN datediff(day,fndoc..ln_dfcy.h_dfcy_ident,fndoc..ln_dfcy.h_dfcy_act) ELSE datediff(day,fndoc..ln_dfcy.h_dfcy_ident,getdate()) END AS Def_Age, dateadd(day, 61, fndoc..ln_dfcy.h_dfcy_ident) AS Date_At_60, dateadd(day, 91, fndoc..ln_dfcy.h_dfcy_ident) AS Date_At_91, dateadd(day, 121, fndoc..ln_dfcy.h_dfcy_ident) AS Date_At_121, dateadd(day, 181, fndoc..ln_dfcy.h_dfcy_ident) AS Date_At_180, dateadd(day, 366, fndoc..ln_dfcy.h_dfcy_ident) AS Date_At_365, loan..ln.f_acmdt AS Accomm_Ind, loan..ln.i_appl_sys AS Orig_App_Sys_Code, loan..ln_2.c_busn_line AS Bus_Line, loan..ln_2.c_busn_typ AS Bus_Type, loan..ln.m_base_ln AS Ln_Amnt, loan..ln.d_ln_fund AS Fund_Dt, loan..ln_sale_stat_hist.c_ln_sms_stat_typ AS SMS_Stat_Type_Code, loan..ln.d_brw_ln_payoff AS Payoff_Dt, loan..ln_2.d_zy_stat AS ZAZY_Dt, loan..ln.d_ln_invs_payoff AS Purch_Dt, corp..invs.n_invs AS Investor, loan..pd.c_pd_typ AS ProductCode, ( SELECT COUNT(*) FROM fndoc..ln_dfcy b WHERE loan..ln.l_ln = b.l_ln AND i_dfcy_ctg = 3029 AND c_dfcy_act IN ('OPEN','SAT') ) AS Open3029, ( SELECT COUNT(*) FROM fndoc..ln_dfcy b WHERE loan..ln.l_ln = b.l_ln AND c_dfcy_srce = 'COLLR' AND i_dfcy_ctg = 7 ) AS COLLR7, ( SELECT COUNT(*) FROM fndoc..ln_dfcy b WHERE loan..ln.l_ln = b.l_ln AND c_dfcy_srce = 'SFR' AND i_dfcy_ctg = 397 ) AS SFR397, ( SELECT COUNT(*) FROM fndoc..loan_event WHERE fndoc..loan_event.l_ln = loan..ln.l_ln AND i_ev IN (202, 200) ) AS Events200202, loan..ln_2.c_mrtge_fmly, ( SELECT COUNT(*) FROM fndoc..ln_dfcy b WHERE loan..ln.l_ln = b.l_ln AND i_dfcy_ctg = 3016 ) AS Def3016Count, ( SELECT COUNT(*) FROM fndoc..ln_dfcy b WHERE loan..ln.l_ln = b.l_ln AND ( ( b.c_dfcy_srce = 'APR' And b.i_dfcy_ctg = 3138 ) OR ( b.c_dfcy_srce = 'SMS' And b.i_dfcy_ctg = 3030 And b.i_dfcy_desc In (3285,3892) ) OR ( b.c_dfcy_srce = 'SMS' And b.i_dfcy_ctg = 3024 And b.i_dfcy_desc = 3225 ) OR ( b.c_dfcy_srce = 'SMS' And b.i_dfcy_ctg = 3020 ) ) ) AS ZAZYDefx, loan..ln_2.c_sale_ctgry AS SMSSaleStatus, CASE WHEN ( loan..ln.c_ln_typ = 'C' AND loan..ln_2.d_zy_stat IS NULL AND loan..ln.d_brw_ln_payoff IS NULL AND ( loan..ln.d_ln_invs_payoff IS NULL OR loan..pl.l_invs IS NULL ) AND NOT EXISTS ( SELECT l_ln FROM fndoc..loan_event WHERE fndoc..loan_event.l_ln = loan..ln.l_ln AND i_ev IN (31, 202, 200) ) AND ( loan..ln_sale_stat_hist.c_ln_sms_stat_typ IS NULL OR NOT ( loan..ln_sale_stat_hist.c_ln_sms_stat_typ = 'I20' AND ( loan..ln_2.c_sale_ctgry = 'FP' OR loan..ln.l_ln IN ( SELECT b.l_ln FROM fndoc..ln_dfcy b WHERE b.l_ln = loan..ln.l_ln AND b.c_dfcy_srce = 'SMS' AND b.i_dfcy_ctg = 3032 AND b.i_dfcy_desc IN (3447, 3370, 3371) AND b.c_dfcy_act IN ('OPEN', 'SAT') ) ) ) ) ) AND ( ( fndoc..ln_dfcy.c_rpt_grp IN ('E', 'C', 'I') AND ( fndoc..ln_dfcy.c_dfcy_srce = 'HCS' OR ( fndoc..ln_dfcy.c_dfcy_srce = 'COLLR' AND fndoc..ln_dfcy.i_dfcy_ctg = 7 ) OR ( fndoc..ln_dfcy.c_dfcy_srce = 'SMS' AND fndoc..ln_dfcy.i_dfcy_ctg = 3016 AND fndoc..ln_dfcy.i_dfcy_desc IN (3135, 3136, 3137, 3138, 3139, 3140, 3141, 3142) ) ) ) OR ( fndoc..ln_dfcy.c_rpt_grp IN ('C', 'E') AND fndoc..ln_dfcy.c_dfcy_srce IN ('APR', 'BPR', 'COLLR', 'CUSTR', 'DACC', 'DDR', 'NPR', 'RVM', 'SFR', 'SMS', 'SPR', 'NLSF') AND fndoc..ln_dfcy.i_dfcy_ctg IN (3, 5, 7, 18, 28, 32, 34, 37, 38, 50, 51, 56, 60, 66, 74, 82, 83, 103, 105, 112, 114, 118, 119, 136, 138, 139, 140, 158, 168, 172, 176, 178, 180, 185, 190, 196, 197, 199, 200, 205, 212, 215, 226, 227, 231, 232, 233, 237, 241, 244, 245, 246, 251, 254, 259, 260, 261, 266, 267, 268, 275, 278, 299, 305, 307, 315, 316, 318, 325, 330, 336, 339, 341, 343, 345, 346, 347, 348, 351, 352, 353, 356, 357, 358, 369, 379, 385, 386, 388, 389, 396, 397, 398, 399, 404, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 421, 422, 423, 424, 425, 434, 576, 821, 897, 898, 899, 907, 908, 909, 912, 918, 919, 920, 921, 2938, 2961, 2985, 2986, 2987, 2988, 2989, 3012, 3013, 3014, 3015, 3016, 3017, 3018, 3019, 3021, 3022, 3023, 3024, 3025, 3026, 3027, 3029, 3030, 3031, 3034, 3053, 3055, 3056, 3075, 3076, 3078, 3079, 3102, 3539, 3625, 3742, 3743, 3744, 3745, 3746, 3747, 3750, 3751, 3752, 3753, 3754, 3755, 3756, 3757, 3758) ) ) AND NOT ( ( fndoc..ln_dfcy.c_dfcy_srce IN ('APR', 'COLLR', 'PDSR') AND fndoc..ln_dfcy.i_dfcy_ctg = 178 AND fndoc..ln_dfcy.i_dfcy_desc = 404 ) OR ( fndoc..ln_dfcy.c_dfcy_srce = 'SMS' AND ( ( fndoc..ln_dfcy.i_dfcy_ctg = 3020 AND fndoc..ln_dfcy.i_dfcy_desc IN (3178, 3179, 3180, 3181, 3183, 3414) ) OR ( fndoc..ln_dfcy.i_dfcy_ctg = 3030 AND fndoc..ln_dfcy.i_dfcy_desc IN (3285, 3892) ) ) ) OR ( fndoc..ln_dfcy.i_dfcy_ctg = 3024 AND fndoc..ln_dfcy.i_dfcy_desc = 3225 ) OR ( fndoc..ln_dfcy.c_dfcy_srce = 'RVM' AND fndoc..ln_dfcy.i_dfcy_ctg IN (38, 103, 105, 112, 138, 168, 180, 215, 231, 307, 351, 353, 358, 389, 397, 421, 425, 434, 897, 898, 899, 3078, 3742, 3743, 3745, 3746, 3747, 3751, 3752, 3753, 3754, 3755, 3756, 3757, 3758) ) OR ( fndoc..ln_dfcy.c_dfcy_srce IN ('BPR', 'DACC', 'DDR', 'NPR') AND fndoc..ln_dfcy.i_dfcy_ctg = 397 ) ) THEN 1 ELSE 0 END AS ChallengedInventory, case when ( case when loan..ln.d_ln_fund >= '01/01/2003' and loan..ln.c_ln_typ != 'C' and ( case when loan..pd.c_pd_typ = 'C' then 'N' else (case when fndoc..ctg_dfcy_srce.i_dfcy_ctg in (142,153,154,156,338,359,391) then 'N' else (case when fndoc..ln_dfcy.c_dfcy_srce in ('3PR','MPA','RECV','ADP','CNVAL') or (fndoc..ln_dfcy.c_dfcy_srce = 'SPR' and fndoc..ctg_dfcy_srce.i_dfcy_ctg in (56,83,287,288,289,290,291,292,293,294,295,296,297,298,325,2988,2989)) or (fndoc..ln_dfcy.c_dfcy_srce = 'BPR' and fndoc..ctg_dfcy_srce.i_dfcy_ctg in (select distinct i_dfcy_ctg from fndoc..ctg_dfcy_srce where c_dfcy_srce = '3PR')) then 'Y' else 'N' end) end) end ) = 'Y' then 'Y' else 'N' end ) = 'Y' then convert(varchar(4),(Case when (30 - datediff(day, ( case when fndoc..ln_dfcy.i_dfcy_ctg in (45,2885) and fndoc..ln_dfcy.i_dfcy_desc = 2884 then (case when fndoc..ln_dfcy.h_dfcy_ident < dateadd(day,7,loan..ln.d_ln_fund) then (case when dateadd(day,7,loan..ln.d_ln_fund) >= getdate() then getdate() else dateadd(day,7,loan..ln.d_ln_fund) end) else fndoc..ln_dfcy.h_dfcy_ident end) else (case when fndoc..ln_dfcy.h_dfcy_ident < loan..ln.d_ln_fund then loan..ln.d_ln_fund else fndoc..ln_dfcy.h_dfcy_ident end) end ), getdate()) + 1) <= 0 then 0 else (30 - datediff(day, ( case when fndoc..ln_dfcy.i_dfcy_ctg in (45,2885) and fndoc..ln_dfcy.i_dfcy_desc = 2884 then (case when fndoc..ln_dfcy.h_dfcy_ident < dateadd(day,7,loan..ln.d_ln_fund) then (case when dateadd(day,7,loan..ln.d_ln_fund) >= getdate() then getdate() else dateadd(day,7,loan..ln.d_ln_fund) end) else fndoc..ln_dfcy.h_dfcy_ident end) else (case when fndoc..ln_dfcy.h_dfcy_ident < loan..ln.d_ln_fund then loan..ln.d_ln_fund else fndoc..ln_dfcy.h_dfcy_ident end) end ), getdate()) + 1) end)) else 'N/A' end AS Days_to_1_pct, case when ( case when loan..ln.d_ln_fund >= '01/01/2003' and loan..ln.c_ln_typ != 'C' and ( case when loan..pd.c_pd_typ = 'C' then 'N' else (case when fndoc..ctg_dfcy_srce.i_dfcy_ctg in (142,153,154,156,338,359,391) then 'N' else (case when fndoc..ln_dfcy.c_dfcy_srce in ('3PR','MPA','RECV','ADP','CNVAL') or (fndoc..ln_dfcy.c_dfcy_srce = 'SPR' and fndoc..ctg_dfcy_srce.i_dfcy_ctg in (56,83,287,288,289,290,291,292,293,294,295,296,297,298,325,2988,2989)) or (fndoc..ln_dfcy.c_dfcy_srce = 'BPR' and fndoc..ctg_dfcy_srce.i_dfcy_ctg in (select distinct i_dfcy_ctg from fndoc..ctg_dfcy_srce where c_dfcy_srce = '3PR')) then 'Y' else 'N' end) end) end ) = 'Y' then 'Y' else 'N' end ) = 'Y' then convert(varchar(4),(Case when (60 - datediff(day, loan..ln.d_ln_fund, getdate()) + 1) <= 0 then 0 else (60 - datediff(day, loan..ln.d_ln_fund, getdate()) + 1) end)) else 'N/A' end AS Days_to_6_pct, case when ( case when loan..ln.d_ln_fund >= '01/01/2003' and loan..ln.c_ln_typ != 'C' and ( case when loan..pd.c_pd_typ = 'C' then 'N' else (case when fndoc..ctg_dfcy_srce.i_dfcy_ctg in (142,153,154,156,338,359,391) then 'N' else (case when fndoc..ln_dfcy.c_dfcy_srce in ('3PR','MPA','RECV','ADP','CNVAL') or (fndoc..ln_dfcy.c_dfcy_srce = 'SPR' and fndoc..ctg_dfcy_srce.i_dfcy_ctg in (56,83,287,288,289,290,291,292,293,294,295,296,297,298,325,2988,2989)) or (fndoc..ln_dfcy.c_dfcy_srce = 'BPR' and fndoc..ctg_dfcy_srce.i_dfcy_ctg in (select distinct i_dfcy_ctg from fndoc..ctg_dfcy_srce where c_dfcy_srce = '3PR')) then 'Y' else 'N' end) end) end ) = 'Y' then 'Y' else 'N' end ) = 'Y' then convert(varchar(4),(Case when (90 - datediff(day, loan..ln.d_ln_fund, getdate())) <= 0 then 0 else (90 - datediff(day, loan..ln.d_ln_fund, getdate())) end)) else 'N/A' end AS GI_Days_to_90, case when ( case when loan..ln.d_ln_fund >= '01/01/2003' and loan..ln.c_ln_typ != 'C' and ( case when loan..pd.c_pd_typ = 'C' then 'N' else (case when fndoc..ctg_dfcy_srce.i_dfcy_ctg in (142,153,154,156,338,359,391) then 'N' else (case when fndoc..ln_dfcy.c_dfcy_srce in ('3PR','MPA','RECV','ADP','CNVAL') or (fndoc..ln_dfcy.c_dfcy_srce = 'SPR' and fndoc..ctg_dfcy_srce.i_dfcy_ctg in (56,83,287,288,289,290,291,292,293,294,295,296,297,298,325,2988,2989)) or (fndoc..ln_dfcy.c_dfcy_srce = 'BPR' and fndoc..ctg_dfcy_srce.i_dfcy_ctg in (select distinct i_dfcy_ctg from fndoc..ctg_dfcy_srce where c_dfcy_srce = '3PR')) then 'Y' else 'N' end) end) end ) = 'Y' then 'Y' else 'N' end ) = 'Y' then convert(varchar(4),(Case when (180 - datediff(day, loan..ln.d_ln_fund, getdate())) <= 0 then 0 else (180 - datediff(day, loan..ln.d_ln_fund, getdate())) end)) else 'N/A' end AS GI_Days_to_180, case when loan..pd.c_pd_typ = 'C' then 'N' else (case when fndoc..ctg_dfcy_srce.i_dfcy_ctg in (142,153,154,156,338,359,391) then 'N' else (case when fndoc..ln_dfcy.c_dfcy_srce in ('3PR','MPA','RECV','ADP','CNVAL') or (fndoc..ln_dfcy.c_dfcy_srce = 'SPR' and fndoc..ctg_dfcy_srce.i_dfcy_ctg in (56,83,287,288,289,290,291,292,293,294,295,296,297,298,325,2988,2989)) or (fndoc..ln_dfcy.c_dfcy_srce = 'BPR' and fndoc..ctg_dfcy_srce.i_dfcy_ctg in (select distinct i_dfcy_ctg from fndoc..ctg_dfcy_srce where c_dfcy_srce = '3PR')) then 'Y' else 'N' end) end) end AS Govt_Ind, case when loan..ln.d_ln_fund >= '01/01/2003' and loan..ln.c_ln_typ != 'C' and ( case when loan..pd.c_pd_typ = 'C' then 'N' else (case when fndoc..ctg_dfcy_srce.i_dfcy_ctg in (142,153,154,156,338,359,391) then 'N' else (case when fndoc..ln_dfcy.c_dfcy_srce in ('3PR','MPA','RECV','ADP','CNVAL') or (fndoc..ln_dfcy.c_dfcy_srce = 'SPR' and fndoc..ctg_dfcy_srce.i_dfcy_ctg in (56,83,287,288,289,290,291,292,293,294,295,296,297,298,325,2988,2989)) or (fndoc..ln_dfcy.c_dfcy_srce = 'BPR' and fndoc..ctg_dfcy_srce.i_dfcy_ctg in (select distinct i_dfcy_ctg from fndoc..ctg_dfcy_srce where c_dfcy_srce = '3PR')) then 'Y' else 'N' end) end) end ) = 'Y' then 'Y' else 'N' end AS GI_Pen_Eligible, RTRIM(corp..emp.n_1st) + " " + RTRIM(corp..emp.n_mid) + ". " + corp..emp.n_last AS Emp_Name, corp..emp.l_bus_tele AS Emp_Phone, corp..emp.i_mail_stn AS Emp_MAC, loan..ln_2.n_ln_clsr AS Closer, loan..ln.c_ln_prcs AS Proc_Code, loan..stlmt_co.n_stlmt_co AS Settlement_Co, loan..stlmt_co.l_bus_tele AS Settlement_Phone, str_replace(str_replace(fndoc..ln_dfcy.t_dfcy_cltn,char(13),' '),char(10),' ') AS Def_Text, loan..ln.n_prg AS Bond_Code, loan..ln_2.c_ln_srce AS LoanSourceFROM loan..ln JOIN loan..ln_2 ON ( loan..ln.l_ln = loan..ln_2.l_ln AND ( loan..ln_2.c_busn_line IS NULL OR loan..ln_2.c_busn_line <> 'S' ) ) JOIN fndoc..ln_dfcy ON ( loan..ln.l_ln=fndoc..ln_dfcy.l_ln AND ( fndoc..ln_dfcy.c_dfcy_act = 'OPEN' OR ( fndoc..ln_dfcy.c_dfcy_act = 'SAT' AND fndoc..ln_dfcy.h_dfcy_act BETWEEN '10/1/2005' AND '10/31/2005 11:59:59 PM' ) ) AND fndoc..ln_dfcy.c_dfcy_srce NOT IN ('FDR','NLSR') ) JOIN fndoc..dfcy_ctg ON fndoc..ln_dfcy.i_dfcy_ctg = fndoc..dfcy_ctg.i_dfcy_ctg JOIN fndoc..dfcy_desc ON fndoc..ln_dfcy.i_dfcy_desc = fndoc..dfcy_desc.i_dfcy_desc JOIN fndoc..ctg_dfcy_srce ON ( fndoc..ln_dfcy.c_dfcy_srce = fndoc..ctg_dfcy_srce.c_dfcy_srce AND fndoc..ln_dfcy.i_dfcy_ctg = fndoc..ctg_dfcy_srce.i_dfcy_ctg ) JOIN loan..indv_brw ON ( loan..ln.l_ln = loan..indv_brw.l_ln AND loan..indv_brw.c_indv_brw_typ = 'brw' ) JOIN corp..nw_org_unit ON loan..ln.i_nw_org_unit = corp..nw_org_unit.i_nw_org_unit JOIN loan..pd ON loan..ln.i_pd = loan..pd.i_pd JOIN loan..stlmt_co ON loan..ln.l_ln = loan..stlmt_co.l_ln LEFT JOIN fndoc..ln_an_hist ON ( loan..ln.l_ln = fndoc..ln_an_hist.l_ln AND fndoc..ln_an_hist.f_actv = 'Y' AND fndoc..ln_an_hist.c_ln_an_hist_typ = 'O' ) LEFT JOIN corp..emp ON fndoc..ln_an_hist.i_scty_user = corp..emp.n_short LEFT JOIN loan..ln_sale_stat_hist ON ( loan..ln.l_ln = loan..ln_sale_stat_hist.l_ln AND loan..ln_sale_stat_hist.f_curr_stat_actv = 'Y' ) LEFT JOIN loan..pl ON loan..ln.i_pl = loan..pl.i_pl LEFT JOIN corp..invs ON loan..pl.l_invs = corp..invs.l_invs