Database

set ANSI_NULLS ON<br />
set QUOTED_IDENTIFIER ON<br />
GO<br />
<br />
ALTER PROCEDURE [dbo].[uprSettlementMgtBDMGetTiedByIntroducer]<br />
@StartDate	datetime=null,<br />
@EndDate	datetime=null,<br />
@BDMID		varchar(10)=null,<br />
@CountryID	int=null,<br />
@IntroducerID int=null,<br />
@UserID		varchar(10)<br />
<br />
AS<br />
SET NOCOUNT ON<br />
Declare @ignore_BDMID bit<br />
Declare @ignore_CountryID bit<br />
Declare @ignore_IntroducerID bit<br />
<br />
if isnull(@EndDate,null) is null Set @EndDate=GetDate()<br />
if isnull(@StartDate,null) is null Set @StartDate=dateadd(d,-1,@EndDate)<br />
if isnull(@BDMID,null) is null Set @ignore_BDMID = 1<br />
if isnull(@CountryID,null) is null Set @ignore_CountryID = 1<br />
if isnull(@IntroducerID,null) is null Set @ignore_IntroducerID = 1<br />
<br />
set @EndDate = dateadd(s,-1,dateadd(d,1,@EndDate))<br />
<br />
Select <br />
	U.Name [BDM],<br />
	SA.Name [SA],<br />
<br />
	CASE<br />
	WHEN CHARINDEX('_', B.TradingName) > 0 <br />
	THEN SUBSTRING(B.TradingName, 1, (CHARINDEX('_', B.TradingName) - 1))<br />
	ELSE ' '<br />
	END [BrokerManager],<br />
	CASE<br />
	WHEN CHARINDEX('_', B.TradingName) > 0 <br />
	THEN SUBSTRING(B.TradingName, CHARINDEX('_', B.TradingName) + 1, LEN(B.TradingName) - CHARINDEX('_', B.TradingName))<br />
	ELSE B.TradingName<br />
	END [Broker],<br />
	SALE.Name [SalesPerson],<br />
	'New Loan' [LoanType],<br />
	L.LoanID,<br />
	M.MarketSegment,<br />
	<br />
	dbo.ufCustomer_GetName(CA.CustomerID) [Customer],<br />
	Case When isnull(L.AblRef,'')='' Then L.LenderRef Else L.AblRef End [Reference],<br />
	dbo.ufLender_GetName(L.LenderID,A.CountryID) [Lender],<br />
	dbo.ufCreateProductName(L.ProductID,@UserID) [Product],<br />
	CASE L.Construction<br />
	WHEN 1 THEN 'Yes'<br />
	ELSE ''<br />
	END [Construction],<br />
	LS2.StageDate [NewLoanDate],<br />
	LS.StageDate [SettlementDate],<br />
	dbo.ufGetMDR(L.LoanID) [MDR %],<br />
	dbo.ufGetMarginRate(L.LoanID) [Margin %],<br />
	L.LoanAmount,<br />
	dbo.ufFEE_Total(L.LoanID,1) [ApplicationFee],<br />
	dbo.ufFEE_Total(L.LoanID,2) [ApplicationFeeRITC],<br />
	0 [LoanIncreaseFee],<br />
	0 [LoanIncreaseFeeRITC]<br />
From<br />
	tblLoan L<br />
	INNER JOIN tblLoanStage AS LS ON LS.LoanID = L.LoanID and LS.LoanStageTypeID = 6 <br />
	INNER JOIN tblApplication AS A ON A.ApplicationID = L.ApplicationID<br />
	INNER JOIN trelCustomerApplication AS CA ON CA.ApplicationID = L.ApplicationID and ApplicantTypeID = 1<br />
	INNER JOIN vwIntroducer B ON B.IntroducerID = A.IntroducerID<br />
	LEFT OUTER JOIN tblLoanStage AS LS2 ON LS2.LoanID = L.LoanID and LS2.LoanStageTypeID = 1		<br />
	LEFT OUTER JOIN zstblUser AS U ON U.UserID in (A.BDMID,A.DirectSalesID)<br />
	LEFT OUTER JOIN zstblUser AS SALE ON SALE.UserID = A.IntroducerSalesPersonID<br />
	LEFT OUTER JOIN zstblUser AS SA ON SA.UserID = A.UserID<br />
	LEFT OUTER JOIN trefMarketSegment AS M ON M.MarketSegmentID = L.MarketSegmentID<br />
Where<br />
	LS.StageDate between @StartDate and @EndDate<br />
and (@ignore_BDMID = 1 OR (A.BDMID = @BDMID OR A.DirectSalesID = @BDMID))<br />
and (@ignore_CountryID = 1 OR  A.CountryID = @CountryID)<br />
and (@ignore_IntroducerID = 1 or B.IntroducerID = @IntroducerID)<br />
and dbo.ufLoan_CheckSecurity(L.LoanID, @UserID) = 1<br />
<br />
UNION ALL<br />
<br />
Select <br />
	U.Name [BDM],<br />
	SA.Name [SA],<br />
<br />
	CASE<br />
	WHEN CHARINDEX('_', B.TradingName) > 0 <br />
	THEN SUBSTRING(B.TradingName, 1, (CHARINDEX('_', B.TradingName) - 1))<br />
	ELSE ' '<br />
	END [BrokerManager],<br />
	CASE<br />
	WHEN CHARINDEX('_', B.TradingName) > 0 <br />
	THEN SUBSTRING(B.TradingName, CHARINDEX('_', B.TradingName) + 1, LEN(B.TradingName) - CHARINDEX('_', B.TradingName))<br />
	ELSE B.TradingName<br />
	END [Broker],<br />
	SALE.Name [SalesPerson],<br />
	'Loan Increase' [LoanType],<br />
	L.LoanID,<br />
	M.MarketSegment,<br />
	dbo.ufCustomer_GetName(CA.CustomerID) [Customer],<br />
	Case When isnull(L.AblRef,'')='' Then L.LenderRef Else L.AblRef End [Reference],<br />
	dbo.ufLender_GetName(L.LenderID,A.CountryID) [Lender],<br />
	dbo.ufCreateProductName(L.ProductID,@UserID) [Product],<br />
	CASE L.Construction<br />
	WHEN 1 THEN 'Yes'<br />
	ELSE ''<br />
	END [Construction],<br />
	LS2.StageDate [NewLoanDate],<br />
	LS.StageDate [SettlementDate],<br />
	dbo.ufGetMDR(L.LoanID) [MDR %],<br />
	dbo.ufGetMarginRate(L.LoanID) [Margin %],<br />
	LI.IncreaseAmount [LoanAmount],<br />
	0 [ApplicationFee],<br />
	0 [ApplicationFeeRITC],<br />
	dbo.ufIncreaseFEE_Total(LI.LoanIncreaseID,1) [LoanIncreaseFee],<br />
	dbo.ufIncreaseFEE_Total(LI.LoanIncreaseID,2) [LoanIncreaseFeeRITC]<br />
From<br />
	tblLoan L<br />
	INNER JOIN tblLoanIncrease AS LI ON LI.LoanID = L.LoanID<br />
	INNER JOIN tblLoanIncreaseStage AS LS ON LS.LoanIncreaseID = LI.LoanIncreaseID and LS.LoanStageTypeID = 6 <br />
	INNER JOIN tblLoanIncreaseStage AS LS2 ON LS2.LoanIncreaseID = LI.LoanIncreaseID and LS2.LoanStageTypeID = 1	<br />
	INNER JOIN tblApplication AS A ON A.ApplicationID = L.ApplicationID<br />
	INNER JOIN vwIntroducer B ON B.IntroducerID = A.IntroducerID<br />
	INNER JOIN trelCustomerApplication AS CA ON CA.ApplicationID = L.ApplicationID and ApplicantTypeID = 1<br />
	LEFT OUTER JOIN zstblUser AS U ON U.UserID in (LI.BDMID,LI.DirectSalesID)<br />
	LEFT OUTER JOIN zstblUser AS SALE ON SALE.UserID = LI.IntroducerSalesPersonID<br />
	LEFT OUTER JOIN zstblUser AS SA ON SA.UserID = LI.CreatedBy<br />
	LEFT OUTER JOIN trefMarketSegment AS M ON M.MarketSegmentID = L.MarketSegmentID<br />
Where<br />
	LS.StageDate between @StartDate and @EndDate<br />
and (@ignore_BDMID = 1 OR (LI.BDMID = @BDMID OR LI.DirectSalesID = @BDMID))<br />
and (@ignore_CountryID = 1 OR  A.CountryID = @CountryID)<br />
and (@ignore_IntroducerID = 1 or A.IntroducerID = @IntroducerID)<br />
and dbo.ufLoan_CheckSecurity(L.LoanID, @UserID) = 1