i am having problem with this SP, Can anyone help me??
when i execute it, comingup with the error:
Msg 207, Level 16, State 3, Procedure uprSettlementMgtBDMGetTiedByIntroducer, Line 24
Invalid column name 'IntroducerID'.
where did done wrong.
_____________________________
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
Thnaks in Advance
|