Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Incorrect syntax near the keyword 'as'

Author  Topic 

rahman87
Starting Member

2 Posts

Posted - 2012-02-14 : 07:37:37
facing a strange situation with a procedure. When I compiles and saves it. The query doesnt show any error, when I try to run it following errors are shown.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '<'.

Msg 156, Level 15, State 1,
Line 2

Incorrect syntax near the keyword 'as'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '<'.

Msg 208, Level 16, State 1, Procedure S2_XLLM_AVE, Line 68
Invalid object name 'S2_XLLM_PP_from_ave_gross'.



the first 3 errors points to the Alter Procedure statement. following is the code. Sorry for the long code but I am in desperate need of help


USE [actuarialdb]
GO
/****** Object: StoredProcedure [dbo].[S2_XLLM_AVE] Script Date: 02/14/2012 14:20:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Exec dbo.[S2_XLLM_AVE] 25,'2011Q4',1
ALTER procedure [dbo].[S2_XLLM_AVE]
@ssr as int,
@beg as nvarchar(max),
@quarter as int
AS
--declare @ssr as varchar(max)
--declare @beg as varchar(max)
--declare @quarter as int
declare @a as int
declare @b as int
declare @c as int
declare @d as int
declare @e as int
declare @f as int
declare @start as varchar(max)
declare @end as varchar(max)
--set @ssr='25'
--set @beg='2011Q4'
--set @quarter=1

set @a = @quarter
set @b=cast((select month(vdate)/3 as CurrQtr from S2_XLLM_F2F_Mastertable where cast(year(vdate) as varchar(4))+'Q'+cast(month(vdate)/3 as varchar(2))=@beg
group by vdate) as int)
set @c=@quarter+@b
set @d=cast((select year(vdate) as CurrYear from S2_XLLM_F2F_Mastertable where cast(year(vdate) as varchar(4))+'Q'+cast(month(vdate)/3 as varchar(2))=@beg
group by vdate) as int)
set @e= case when @c%4=0 then 4 else @c%4 end
set @f=4-@a
set @start= cast(@b as varchar(4))+'Q'+left(@beg,4)
set @end=case when @c>4 then cast(@e as varchar(4))+'Q'+cast(@d+1 as varchar(4)) else cast(@c as varchar(4))+'Q'+cast(@d as varchar(4)) end

--From Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_from_ave_gross') drop table dbo.S2_XLLM_PP_from_ave_gross
exec ('select [Reserving Line],[Inc Pattern_Q'+@b+'] as [From Inc Pattern_gross],[Cum Pattern_Q'+@b+'] as [From Cum Pattern_gross],
1-yr1+'+@d+' as [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_from_ave_gross from [G2_XLLM_PPInc_Q_cum_All_Combined]
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''Gross'' and currency=''GBP''')

--To Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_to_ave_gross') drop table dbo.S2_XLLM_PP_to_ave_gross
exec ('select [Reserving Line],[Inc Pattern_Q'+@e+'] as [To Inc Pattern_gross],[Cum Pattern_Q'+@e+'] as [To Cum Pattern_gross],
case when '+@c+'<=4 then 1-yr1+'+@d+' else '+@d+'+2-yr1 end as [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_to_ave_gross from [G2_XLLM_PPInc_Q_cum_All_Combined]
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''Gross'' and currency=''GBP''')

--From Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_from_ave_ceded') drop table dbo.S2_XLLM_PP_from_ave_ceded
exec ('select [Reserving Line],[Inc Pattern_Q'+@b+'] as [From Inc Pattern_ceded],[Cum Pattern_Q'+@b+'] as [From Cum Pattern_ceded],
1-yr1+'+@d+' as [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_from_ave_ceded from [G2_XLLM_PPInc_Q_cum_All_Combined]
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''RI'' and currency=''GBP''')

--To Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_to_ave_ceded') drop table dbo.S2_XLLM_PP_to_ave_ceded
exec ('select [Reserving Line],[Inc Pattern_Q'+@e+'] as [To Inc Pattern_ceded],[Cum Pattern_Q'+@e+'] as [To Cum Pattern_ceded],
case when '+@c+'<=4 then 1-yr1+'+@d+' else '+@d+'+2-yr1 end as [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_to_ave_ceded from [G2_XLLM_PPInc_Q_cum_All_Combined]
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''RI'' and currency=''GBP''')

--Combined Payment Patterns

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_ave') drop table dbo.S2_XLLM_PP_ave
select A.[Reserving Line],A.[ProgramCode/UnderwritingYear],A.[From Inc Pattern_gross],A.[From Cum Pattern_gross],B.[To Inc Pattern_gross],B.[To Cum Pattern_gross],
case when round([From Cum Pattern_gross],10) = 1 then 0 else ([To Cum Pattern_gross]-[From Cum Pattern_gross])/(1-[From Cum Pattern_gross]) end as ExpPP_Gross,
C.[From Inc Pattern_ceded],C.[From Cum Pattern_ceded],D.[To Inc Pattern_ceded],D.[To Cum Pattern_ceded],
case when round([From Cum Pattern_ceded],10) = 1 then 0 else ([To Cum Pattern_gross]-[From Cum Pattern_ceded])/(1-[From Cum Pattern_ceded]) end as ExpPP_ceded
into dbo.[S2_XLLM_PP_ave]
from
S2_XLLM_PP_from_ave_gross A inner join S2_XLLM_PP_to_ave_gross B
on A.[Reserving Line]=B.[Reserving Line] and A.[ProgramCode/UnderwritingYear]=B.[ProgramCode/UnderwritingYear] inner join
S2_XLLM_PP_from_ave_ceded C on
A.[Reserving Line]=C.[Reserving Line] and A.[ProgramCode/UnderwritingYear]=C.[ProgramCode/UnderwritingYear] inner join
S2_XLLM_PP_to_ave_ceded D on
A.[Reserving Line]=D.[Reserving Line] and A.[ProgramCode/UnderwritingYear]=D.[ProgramCode/UnderwritingYear]



--Case+IBNR from S2F2F

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_Exp_ave') drop table dbo.S2_XLLM_Exp_ave
select B.[ProgramCode/UnderwritingYear],B.[Reserving Line] as Profile_ID,ExpPP_Gross, isnull(ExpGRes,0) as ExpGRes,isnull(ExpCRes,0) as ExpCRes,
isnull(ExpGRes*ExpPP_Gross,0) as ExpPaid_Gross ,
[From Cum Pattern_Gross],[To Cum Pattern_Gross],
ExpPP_Ceded,isnull(ExpCRes*ExpPP_Ceded,0) as ExpPaid_Ceded ,[From Cum Pattern_ceded],[To Cum Pattern_ceded]
into dbo.S2_XLLM_Exp_ave
from
(select [ProgramCode/UnderwritingYear],Profile_ID,
sum([Dcase Reserves]+[ACase Reserves Ext]+[ACase Reserves Int]+[DIBNR]+[AIBNR Ext]+[AIBNR Int]) as ExpGRes,
sum([CCase Reserves Ext]+[CCase Reserves Int]+[CIBNR Ext]+[CIBNR Int]) as ExpCRes
from S2_XLLM_F2F_Mastertable where cast(year(vdate) as varchar(4))+'Q'+cast(month(vdate)/3 as varchar(2))=@beg and
[BusinessUnit/Branch] =51209 and [Department/CostCentre] = 806670
group by Profile_ID,[ProgramCode/UnderwritingYear]) A right join S2_XLLM_PP_ave B
on A.Profile_ID=B.[Reserving Line] and A.[ProgramCode/UnderwritingYear]=B.[ProgramCode/UnderwritingYear]


--Actual Paid & AVE

if(@c>4)
Begin
if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_final_ave') drop table dbo.S2_XLLM_final_ave
select A.[Reserving Line],A.YOA,A.[SII Lob],sum(A.[ActPaid]*exch_rate) as [ActPaid],sum([ActPaid_Ceded]*exch_rate) as [ActPaid_Ceded],
isnull(ExpGRes,0)/1000 as ExpGRes,isnull(ExpPaid_Gross,0)/1000 as ExpPaid_Gross,isnull(ExpPaid_Gross,0)/1000-sum(isnull([ActPaid],0)*exch_rate) as AVE_Gross,
isnull([From Cum Pattern_Gross],1) as [From Cum Pattern_Gross],isnull([To Cum Pattern_Gross],1) as [To Cum Pattern_Gross],
case when @d=yoa then (@b*3) else (@d-yoa)*12+(@b*3) end as [From Development Age],
(@d-yoa+1)*12+(@e*3) as [To Development Age],
isnull([To Cum Pattern_Gross],0)- isnull([From Cum Pattern_Gross],0) as [Incremental Paid to ultimate Patterns_Gross],(1-isnull([From Cum Pattern_Gross],1)) as [Undeveloped Paid to ultimate Patterns_Gross],
isnull(ExpCRes,0)/1000 as ExpCRes,isnull(ExpPaid_Ceded,0)/1000 as ExpPaid_Ceded,
isnull(ExpPaid_Ceded,0)/1000-sum(isnull([ActPaid_Ceded],0)*exch_rate) as AVE_Ceded,
isnull([From Cum Pattern_Ceded],1) as [From Cum Pattern_Ceded],isnull([To Cum Pattern_Ceded],1) as [To Cum Pattern_Ceded],
isnull([To Cum Pattern_Ceded],0)- isnull([From Cum Pattern_Ceded],0) as [Incremental Paid to ultimate Patterns_Ceded],
(1-isnull([From Cum Pattern_Ceded],1)) as [Undeveloped Paid to ultimate Patterns_Ceded]
into dbo.S2_XLLM_final_ave from
(select [New SSR] as [Reserving Line],YOA,[SII Lob],[ActPaid],[ActPaid_Ceded],settcurrency from
(select MantriClass as [Reserving Line],YoA,settcurrency,
SUM(case when period =@end then [Gross Paid] else -[Gross Paid] end) as [ActPaid],
SUM(case when period =@end then [Gross Paid]-[Net Paid] else -([Gross Paid]-[Net Paid]) end) as [ActPaid_Ceded]
from [XLLM_T92 - Flat Pack file after earnings]
where period in (@start,@end)
and Syn=1209 group by MantriClass,YoA,settcurrency) A inner join
dbo.[s2_xllm_mapping_mastertable] B
on A.[Reserving Line] =B.[New SSR] where [Reserving Line]=@ssr) A
left join S2_XLLM_Exp_ave B on A.[Reserving Line]=B.Profile_ID and A.yoa=b.[ProgramCode/UnderwritingYear] inner join
(select exch_rate,from_cur from S2_exchange_rates where to_cur='gbp'
and eff_date= (select vdate from S2_XLLM_F2F_Mastertable where cast(year(vdate) as varchar(4))+'Q'+cast(month(vdate)/3 as varchar(2))=@beg group by vdate) ) C
on A.settcurrency=C.from_cur
group by A.[Reserving Line],A.YOA,A.[SII Lob],isnull(ExpGRes,0)/1000,isnull(ExpPaid_Gross,0)/1000,isnull([From Cum Pattern_Gross],1),
isnull([To Cum Pattern_Gross],1),
case when @d=yoa then (@b*3) else (@d-yoa)*12+(@b*3) end ,case when @d=yoa then (@e*3) else (@d-yoa)*12+(@e*3) end ,
isnull([To Cum Pattern_Gross],0)- isnull([From Cum Pattern_Gross],0),(1-isnull([From Cum Pattern_Gross],1)),isnull(ExpCRes,0)/1000,
isnull(ExpPaid_Ceded,0)/1000,isnull([From Cum Pattern_Ceded],1),isnull([To Cum Pattern_Ceded],1),
isnull([To Cum Pattern_Ceded],0)- isnull([From Cum Pattern_Ceded],0),(1-isnull([From Cum Pattern_Ceded],1))
order by yoa
End

if(@c<=4)
Begin
if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_final_ave') drop table dbo.S2_XLLM_final_ave
select A.[Reserving Line],A.YOA,A.[SII Lob],sum(A.[ActPaid]*exch_rate) as [ActPaid],
sum([ActPaid_Ceded]*exch_rate) as [ActPaid_Ceded],
isnull(ExpGRes,0)/1000 as ExpGRes,isnull(ExpPaid_Gross,0)/1000 as ExpPaid_Gross,isnull(ExpPaid_Gross,0)/1000-sum(isnull([ActPaid],0)*exch_rate) as AVE_Gross,
isnull([From Cum Pattern_Gross],1) as [From Cum Pattern_Gross],isnull([To Cum Pattern_Gross],1) as [To Cum Pattern_Gross],
case when @d=yoa then (@b*3) else (@d-yoa)*12+(@b*3) end as [From Development Age],
case when @d=yoa then (@e*3) else (@d-yoa)*12+(@e*3) end as [To Development Age],
isnull([To Cum Pattern_Gross],0)- isnull([From Cum Pattern_Gross],0) as [Incremental Paid to ultimate Patterns_Gross],(1-isnull([From Cum Pattern_Gross],1)) as [Undeveloped Paid to ultimate Patterns_Gross] ,
isnull(ExpCRes,0)/1000 as ExpCRes,isnull(ExpPaid_Ceded,0)/1000 as ExpPaid_Ceded,isnull(ExpPaid_Ceded,0)/1000-sum(isnull([ActPaid_Ceded],0)*exch_rate) as AVE_Ceded,
isnull([From Cum Pattern_Ceded],1) as [From Cum Pattern_Ceded],isnull([To Cum Pattern_Ceded],1) as [To Cum Pattern_Ceded],
isnull([To Cum Pattern_Ceded],0)- isnull([From Cum Pattern_Ceded],0) as [Incremental Paid to ultimate Patterns_Ceded],(1-isnull([From Cum Pattern_Ceded],1)) as [Undeveloped Paid to ultimate Patterns_Ceded]
into dbo.S2_XLLM_final_ave from

(select [New SSR] as [Reserving Line],YOA,[SII Lob],[ActPaid],[ActPaid_Ceded],settcurrency from
(select MantriClass as [Reserving Line],YoA,settcurrency,
SUM(case when period =@end then [Gross Paid] else -[Gross Paid] end) as [ActPaid],
SUM(case when period =@end then [Gross Paid]-[Net Paid] else -([Gross Paid]-[Net Paid]) end) as [ActPaid_Ceded]
from [XLLM_T92 - Flat Pack file after earnings]
where period in (@start,@end)
and Syn=1209 group by MantriClass,YoA,settcurrency ) A inner join dbo.[s2_xllm_mapping_mastertable] B
on A.[Reserving Line] =B.[New SSR] where [Reserving Line]=@ssr) A
left join S2_XLLM_Exp_ave B on A.[Reserving Line]=B.Profile_ID and A.yoa=b.[ProgramCode/UnderwritingYear] inner join
(select exch_rate,from_cur from S2_exchange_rates where to_cur='usd'
and eff_date= (select vdate from S2_XLLM_F2F_Mastertable where cast(year(vdate) as varchar(4))+'Q'+cast(month(vdate)/3 as varchar(2))=@beg group by vdate) ) C
on A.settcurrency=C.from_cur
group by A.[Reserving Line],A.YOA,A.[SII Lob],isnull(ExpGRes,0)/1000,isnull(ExpPaid_Gross,0)/1000,isnull([From Cum Pattern_Gross],1),
isnull([To Cum Pattern_Gross],1),
case when @d=yoa then (@b*3) else (@d-yoa)*12+(@b*3) end ,case when @d=yoa then (@e*3) else (@d-yoa)*12+(@e*3) end ,
isnull([To Cum Pattern_Gross],0)- isnull([From Cum Pattern_Gross],0),(1-isnull([From Cum Pattern_Gross],1)),isnull(ExpCRes,0)/1000,
isnull(ExpPaid_Ceded,0)/1000,isnull([From Cum Pattern_Ceded],1),isnull([To Cum Pattern_Ceded],1),
isnull([To Cum Pattern_Ceded],0)- isnull([From Cum Pattern_Ceded],0),(1-isnull([From Cum Pattern_Ceded],1))

order by yoa
End


--select * from S2_exchange_rates




Command(s) completed successfully

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-14 : 07:45:58
If it doesn't show syntax errors when you save, it means the syntax errors are in the dynamic SQL that you have, so one of these pieces

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_from_ave_gross') drop table dbo.S2_XLLM_PP_from_ave_gross
exec ('select [Reserving Line],[Inc Pattern_Q'+@b+'] as [From Inc Pattern_gross],[Cum Pattern_Q'+@b+'] as [From Cum Pattern_gross],
1-yr1+'+@d+' as [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_from_ave_gross from [G2_XLLM_PPInc_Q_cum_All_Combined]
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''Gross'' and currency=''GBP''')

--To Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_to_ave_gross') drop table dbo.S2_XLLM_PP_to_ave_gross
exec ('select [Reserving Line],[Inc Pattern_Q'+@e+'] as [To Inc Pattern_gross],[Cum Pattern_Q'+@e+'] as [To Cum Pattern_gross],
case when '+@c+'<=4 then 1-yr1+'+@d+' else '+@d+'+2-yr1 end as [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_to_ave_gross from [G2_XLLM_PPInc_Q_cum_All_Combined]
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''Gross'' and currency=''GBP''')

--From Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_from_ave_ceded') drop table dbo.S2_XLLM_PP_from_ave_ceded
exec ('select [Reserving Line],[Inc Pattern_Q'+@b+'] as [From Inc Pattern_ceded],[Cum Pattern_Q'+@b+'] as [From Cum Pattern_ceded],
1-yr1+'+@d+' as [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_from_ave_ceded from [G2_XLLM_PPInc_Q_cum_All_Combined]
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''RI'' and currency=''GBP''')

--To Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_to_ave_ceded') drop table dbo.S2_XLLM_PP_to_ave_ceded
exec ('select [Reserving Line],[Inc Pattern_Q'+@e+'] as [To Inc Pattern_ceded],[Cum Pattern_Q'+@e+'] as [To Cum Pattern_ceded],
case when '+@c+'<=4 then 1-yr1+'+@d+' else '+@d+'+2-yr1 end as [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_to_ave_ceded from [G2_XLLM_PPInc_Q_cum_All_Combined]
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''RI'' and currency=''GBP''')


Rather than EXEC it directly, use a variable to build up the dynamic SQL and use PRINT to return the built-up dynamic SQL as a message. You should be able to see where the errors are in that.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

rahman87
Starting Member

2 Posts

Posted - 2012-02-17 : 04:13:55
Thanks a lot Gail, that was brillilant of you. I rechecked the executable part and found the error. As always it was a small issue with the parameter I am passing.

Command(s) completed successfully
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-17 : 04:22:49
Glad to hear it's sorted

--
Gail Shaw
SQL Server MVP
Go to Top of Page

tksqqo
Starting Member

1 Post

Posted - 2013-07-04 : 07:19:50
Please help ... I am trying to excute this sp_ but keep on failing..

--USE [MIS]
--GO
--/****** Object: StoredProcedure [dbo].[usp_UploadSantam] Script Date: 07/04/2013 11:21:11 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO



--ALTER PROC [dbo].[usp_UploadSantam] (@ImportOriginator varchar (50))
--AS
DECLARE @Headers VARCHAR(1000)
DECLARE @x VARCHAR(8000)
DECLARE @Maxrows int
DECLARE @RowCount int
DECLARE @branch varchar(50)
DECLARE @UploadID int
DECLARE @InsurerID tinyint
DECLARE @RecCount tinyint
DECLARE @PolicyNumber varchar(25)
DECLARE @Type char(3)
DECLARE @BrokerIDSecondary INT
DECLARE @Partnerid INT
DECLARE @Brokerid INT
DECLARE @SegmentID INT
DECLARE @PolicyID INT
DECLARE @CompanyName varchar (255)
DECLARE @ClientID int
DECLARE @BranchID int
DECLARE @AgentcodeData varchar(255)
DECLARE @AgentCodeID int
DECLARE @TransactionDateChar char(8)
DECLARE @TransactionDate datetime
DECLARE @EarningMonth int
DECLARE @EarningYear int
DECLARE @PremiumVested DECIMAL (12,4)
DECLARE @PremiumPolicyFee DECIMAL (12,4)
DECLARE @PremiumBase DECIMAL (12,4)
DECLARE @Commission DECIMAL (12,4)
DECLARE @CommissionPerc DECIMAL (12,4)
DECLARE @FullName varchar(255)
DECLARE @CompanyID INT
DECLARE @ProductID smallint
DECLARE @PortFolioID smallint
DECLARE @Sender varchar (100)
DECLARE @SystemID int
DECLARE @FiscalPeriodID int
--ROLL-UP--

DECLARE @Table TABLE
([UploadID] [int] identity NOT NULL ,
[Branch] [int] ,
[AgentCode] [varchar] (255) ,
[PolicyNo] [varchar] (35) ,
[FullName] [varchar] (255),
[Premium] [decimal](15, 2) NULL ,
[PolicyFee] [decimal](15, 2) NULL ,
[Commission] [decimal](15, 2) NULL ,
[Earning Month] [int] NULL ,
[Earning Year] [int] NULL )

BEGIN
SET @Sender = 'usp_UploadSantam'

--usp_UploadSantam
--usp_UploadSBInvest

DELETE FROM tblMIS_UploadSantam WHERE PolicyNo IS NULL
-------------------------------------------------------------------------------------------------------
-- First validate Branch and agentcode

UPDATE tblMIS_UploadSantam
SET Branch = (case WHEN len(branch) = 1 THEN '00'+Branch
WHEN len(Branch) = 2 THEN '0'+ Branch
ELSE Branch END)

UPDATE tblMIS_UploadSantam
SET AgentCode = LTRIM(RTRIM(Branch)) + '/' + LTRIM(RTRIM(AgentCode))


UPDATE tblMIS_UploadSantam
SET Branch = T.BranchID
FROM tblMIS_AgentCodeTranslation T, tblMIS_UploadSantam U
WHERE T.AgentCode = U.AgentCode


-- ----now process individual records----------------------------------------------------------------------
SELECT @PartnerID = 1, @BrokerIDSecondary = 1, @SegmentID = 1, @ProductID = 1, @PortFolioID = 1, @SystemID = 3


--get brokerID from New Business Table
IF EXISTS (SELECT BrokerID
FROM tblMIS_BrokerNewBusiness
WHERE PolicyNumber = @PolicyNumber)
SELECT @BrokerID = BrokerID
FROM tblMIS_BrokerNewBusiness
WHERE PolicyNumber = @PolicyNumber
ELSE
SET @BrokerID = 1

INSERT INTO @Table (Branch, AgentCode,PolicyNo, FullName,
Premium, PolicyFee, Commission,
[Earning Month], [Earning Year] )
SELECT Branch, AgentCode,PolicyNo, FullName,
Sum(isnull(PremiumVested,0)), sum(isnull([Policy Fee],0)), sum(isnull(Commission,0)),
[Earning Month], [Earning Year]
FROM tblMIS_UploadSantam
GROUP BY Branch, AgentCode, PolicyNo, FullName, [Earning Month], [Earning Year]

Update @Table
set Premium = Premium+PolicyFee

SET @RowCount = 1
SELECT @MaxRows = count(1) FROM @Table

WHILE @RowCount <= @MaxRows
BEGIN
SELECT @Policynumber = PolicyNo,
@BranchID = Branch,
@FullName = FullName,
@AgentCodeData = AgentCode,
@PremiumVested = Premium,
@PremiumPolicyFee = PolicyFee,
@Commission = Commission,
@CommissionPerc = (case when premium <> 0 then (commission/premium)*100 else 0 end),
@EarningYear = [Earning Year],
@EarningMonth = [Earning Month]
FROM @Table
WHERE UploadID = @RowCount

SELECT @InsurerID =
CASE
WHEN SUBSTRING(@AgentcodeData,1,3) IN ('432','433','434','439','527') THEN 6
WHEN SUBSTRING(@AgentcodeData,1,3) IN ('225') THEN 58
ELSE 37
END


--get details for header record
SELECT @ClientID = 1
IF EXISTS (select ClientID FROM tblMIS_Client (nolock) WHERE FullName = @FullName)
SELECT @ClientID = ClientID FROM tblMIS_Client (nolock) WHERE FullName = @FullName
--create new client if necessary
IF @ClientID = 1
EXEC usp_InsertClient null, null, @FullName, @FullName, null, @FullName, NULL, NULL, @ClientID OUTPUT
--if header exists modify else insert
IF EXISTS (SELECT PolicyID FROM tblMIS_PolicyHead (NOLOCK) WHERE PolicyNumber = @PolicyNumber)
SELECT @Type = 'UPD'
ELSE
SELECT @Type = 'INS'
--process header record
EXEC usp_InsertPolicyHead
@Sender, @Type,@PolicyNumber, 3, null, @BrokerID,@PartnerID, @ClientID, 4, @SegmentID, null,
@InsurerID, @SystemID, @AgentCodeData, @ProductID, @PortFolioID, @PolicyID OUTPUT


--------------------------------------------------------------------------------------------------------
--get details for detail record
SELECT @TransactiondateChar = FiscalDateChar , @FiscalPeriodID = FiscalPeriodID,
@TransactionDate = FiscalDate FROM tblMIS_FiscalPeriods WHERE EarningYear = @EarningYear AND EarningMonth = @EarningMonth

IF EXISTS ( SELECT AgentCodeID FROM tblMIS_AgentCodeTranslation WHERE AgentCode = @AgentCodeData AND InsurerID = @InsurerID)
SELECT @AgentCodeID = AgentCodeID FROM tblMIS_AgentCodeTranslation WHERE AgentCode = @AgentCodeData AND InsurerID = @InsurerID
ELSE
SELECT @AgentCodeID = 1

IF isnull(@AgentCodeID,0) = 0
RAISERROR ('AgentCode does NOT EXIST' ,11,1,@AgentCodeData)


IF EXISTS (SELECT DetailID
FROM tblMIS_PolicyDetail
WHERE PolicyID = @PolicyID
AND FiscalPeriodID = @FiscalPeriodID
AND AgentCodeID = @AgentCodeID)
SELECT @Type = 'UPD'
ELSE

SELECT @Type = 'INS'

IF EXISTS ( SELECT PolicyNumber FROM tblMIS_BrokerNewBusiness WHERE PolicyNumber = @PolicyNumber)
SELECT @PremiumBase = PremiumGross FROM tblMIS_BrokerNewBusiness (NOLOCK) WHERE PolicyNumber = @PolicyNumber
ELSE
SELECT @PremiumBase = @PremiumVested

EXEC usp_InsertPolicyDetail
@Type, @PolicyID, @TransactionDate, @TransactionDateChar, @PremiumBase,
@PremiumVested, @PremiumPolicyFee, @Commission, @CommissionPerc, @AgentCodeID,
@BranchID, @InsurerID, @SystemID,'Thabo'

SET @RowCount = @RowCount + 1
END


DELETE FROM tblMIS_PolicyDetail
WHERE PremiumVestedIncl = 0
AND Commission = 0
AND PremiumPolicyFee=0

---santam direct have been unable to provide policy fee details
---since mar 2006. therefore we are copying the policyfee from march to
---subsequent months as a provision
-- update tblMIS_PolicyDetail
-- set PremiumPolicyFee = M.PremiumPolicyFee
-- from tblMIS_SantamDirectPFMar2006 M inner join
-- tblMIS_PolicyDetail on tblMIS_PolicyDetail.PolicyId = M.PolicyId
-- where TransactionDateChar = @TransactionDateChar

END
Go to Top of Page
   

- Advertisement -