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 LoanSource


FROM
  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