CREATE PROCEDURE [dbo].[STP_Agent_upload]

(

-- List Input Parameters

@Agent_Number VARCHAR(50), --column A

@Date_Last_Payment DATETIME,--column C

@policy_number VARCHAR (50),--column D

@Agent_Payment_Amount SMALLMONEY,-- [Net] -> this is column M

@Row_Number VARCHAR (100),--PASS this from application!

@Return_Message VARCHAR (255) OUTPUT

)

AS BEGIN

--List Variables Here

DECLARE @Record_Count AS VARCHAR (100)--Used to flag whether there is an existing record

DECLARE @productAS VARCHAR (100)

DECLARE @Agent_URNAS VARCHAR (100)

DECLARE @InsurerAS VARCHAR (100)

DECLARE @Transaction_CounterAS VARCHAR (100)

DECLARE @Agent_Introducer_URNAS VARCHAR (100)

DECLARE @CompanyAS VARCHAR (100)

--Initialize the variables

SET @Record_Count =''

SET @product=''

SET @Agent_URN=''

SET @Insurer=''

SET @Transaction_Counter=''

SET @Agent_Introducer_URN=''

SET @Company=''

---------------------------------------------Calculations---------------------------------------------

--1. Find Product

SELECT @product = [Cover type] FROM policytype WHERE [policy prefix] = LEFT (@policy_number, 2)

IF @product = '' SET @Return_Message = @policy_number + ' failed, Policy prefix not found, this was row number ' + @Row_Number

--2. Find Agent

IF @Return_Message <> ''

SELECT @Agent_URN = [Agent URN] FROM [AgentDetails] WHERE [Agent Number] = @Agent_Number

IF @Agent_URN = '' SET @Return_Message = @policy_number +' failed, agent not found, this was row number ' + @Row_Number

--3. Find Insurer

IF @Return_Message <> ''

SELECT @Insurer = MAX ([Transaction date]) FROM Accounts WHERE [transaction date] = '@Date_Last_Payment' AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation') AND [policy number] = @policy_number

IF @Insurer = '' SET @Return_Message = @policy_number +' failed, insurer not found, this was row number ' + @Row_Number

--4. Find Transaction Counter

IF @Return_Message <> ''

SELECT @Transaction_Counter = [Transaction Counter] FROM Accounts WHERE [transaction date] = '@Date_Last_Payment' AND [policy number] = @policy_number AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation')

IF @Transaction_Counter = '' SET @Return_Message = @policy_number + ' failed, Transaction Counter not found, this was row number ' + @Row_Number

--5. Find Agent Introducer

IF @Return_Message <> ''

SELECT @Agent_Introducer_URN = [agents introducer] FROM Accounts WHERE [transaction date] = '@Date_Last_Payment' AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation') AND [policy number] = @policy_number

IF @Agent_Introducer_URN = '' SET @Return_Message = @policy_number +' failed, agent introducer not found, this was row number ' + @Row_Number

--6. Company

IF @Return_Message <> ''

SELECT @Company = [company name] FROM Accounts WHERE [transaction date] = '@Date_Last_Payment' AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation') AND [policy number] = @policy_number

IF @Company = '' SET @Return_Message = @policy_number +' failed, company not found, this was row number ' + @Row_Number

----------------------------------------------------------------validation----------------------------------------------------------------------

--1. Agent Paymnt

IF @Agent_Payment_Amount >0 AND @product <> '' and @Agent_URN <> ''

SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: agent payment needs to be a negative amount.'

--2. @Agent_Number

IF @Agent_Number = ''

SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: there is no agent number.'

--3. @Date_Last_Payment

IF @Agent_Number = ''

SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: there is no transaction date.'

--4. @policy_number

IF @Agent_Number = ''

SET @Return_Message = 'Failure this was row number: ' + @Row_Number + ' Reason: there is no policy number.'

--5. @Agent_Payment_Amount

IF @Agent_Number = ''

SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: there is no agent payment amount.'

--6. Search for combination of inputs

IF @Return_Message <> ''

SELECT @Record_Count = COUNT ([Policy number]) FROM accounts WHERE [Policy number] = @policy_number AND [Agent] = @Agent_Number AND [transaction date] = @Date_Last_Payment AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation')

IF @Record_Count = '0'

SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: there is no transaction that matches the policy number, agent and transaction date specified.'