CREATE PROCEDURE dbo.Rpt_InspcBPOTrack
AS
BEGIN
SET NOCOUNT ON
-------------------------------
SELECT f.loan_number,b.first_name,b.last_name,bs.description,v.name as vn,pr.city,pr.state,pr.street,pr.zip,
prq.work_order_prep_dt,prs.pir01_insp_complete_date,max(bp.date_ordered) as do,bp.date_performed
FROM fcs_master f(nolock)
inner join
borrower b(nolock)
on (f.loan_id = b.loan_id)
inner join
loaninfo li(nolock)
on(b.loan_id = li.loan_id)
inner join investor_info i (nolock)
on (li.loan_id = i.loan_id)
inner join vendor v (nolock)
on (i.company_id = v.company_id)
inner join
property_info pr(nolock)
on b.loan_id=pr.loan_id
inner join
(select prop_insp_req_id,property_id,work_order_prep_dt =max(work_order_prep_dt)
from prop_insp_requests (nolock)
group by property_id,prop_insp_req_id) prq
on pr.property_id=prq.property_id
inner join
(select prop_insp_req_id,pir01_insp_complete_date =max(pir01_insp_complete_date )
from prop_insp_results(nolock)
group by prop_insp_req_id) prs
on prq.prop_insp_req_id=prs.prop_insp_req_id
left outer join
loss_mitigation r (nolock)
on (r.loan_id =b.loan_id)
left outer join
(select property_id,date_ordered=max(date_ordered),date_performed=max(date_performed)
from bpo(nolock)
group by property_id) bp
on pr.property_id=bp.property_id
inner join
business_area_status bs(nolock)
on r.business_area_status_id=bs.business_area_status_id
WHERE b.other_id = '01'and bs.active_flag='Y'
and prq.work_order_prep_dt
=(select max(work_order_prep_dt )as wd from prop_insp_requests where
prop_insp_requests.prop_insp_req_id=prs.prop_insp_req_id)
and ((bp.date_performed <>null or prs.pir01_insp_complete_date is null)or(bp.date_ordered <> null and bp.date_performed is null) )
group by
f.loan_number, b.first_name,b.last_name,bs.description,v.name
,pr.state,pr.street,pr.city,pr.zip,prq.work_order_prep_dt,bp.date_ordered,prq.prop_insp_req_id,
bp.date_performed,prs.pir01_insp_complete_date,bp.property_id
END
