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.
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 2Incorrect syntax near the keyword 'as'.Msg 102, Level 15, State 1, Line 2Incorrect syntax near '<'.Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'as'.Msg 102, Level 15, State 1, Line 2Incorrect syntax near '<'.Msg 208, Level 16, State 1, Procedure S2_XLLM_AVE, Line 68Invalid 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 helpUSE [actuarialdb]GO/****** Object: StoredProcedure [dbo].[S2_XLLM_AVE] Script Date: 02/14/2012 14:20:57 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--Exec dbo.[S2_XLLM_AVE] 25,'2011Q4',1ALTER procedure [dbo].[S2_XLLM_AVE]@ssr as int,@beg as nvarchar(max),@quarter as intAS--declare @ssr as varchar(max) --declare @beg as varchar(max)--declare @quarter as intdeclare @a as intdeclare @b as intdeclare @c as intdeclare @d as intdeclare @e as intdeclare @f as intdeclare @start as varchar(max)declare @end as varchar(max)--set @ssr='25'--set @beg='2011Q4'--set @quarter=1set @a = @quarterset @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))=@beggroup by vdate) as int)set @c=@quarter+@bset @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))=@beggroup by vdate) as int)set @e= case when @c%4=0 then 4 else @c%4 endset @f=4-@aset @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 procedureif exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_from_ave_gross') drop table dbo.S2_XLLM_PP_from_ave_grossexec ('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 procedureif exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_to_ave_gross') drop table dbo.S2_XLLM_PP_to_ave_grossexec ('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 procedureif exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_from_ave_ceded') drop table dbo.S2_XLLM_PP_from_ave_cededexec ('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 procedureif exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_to_ave_ceded') drop table dbo.S2_XLLM_PP_to_ave_cededexec ('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 Patternsif exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_ave') drop table dbo.S2_XLLM_PP_aveselect 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_cededinto dbo.[S2_XLLM_PP_ave]fromS2_XLLM_PP_from_ave_gross A inner join S2_XLLM_PP_to_ave_gross Bon A.[Reserving Line]=B.[Reserving Line] and A.[ProgramCode/UnderwritingYear]=B.[ProgramCode/UnderwritingYear] inner joinS2_XLLM_PP_from_ave_ceded C onA.[Reserving Line]=C.[Reserving Line] and A.[ProgramCode/UnderwritingYear]=C.[ProgramCode/UnderwritingYear] inner joinS2_XLLM_PP_to_ave_ceded D onA.[Reserving Line]=D.[Reserving Line] and A.[ProgramCode/UnderwritingYear]=D.[ProgramCode/UnderwritingYear] --Case+IBNR from S2F2Fif exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_Exp_ave') drop table dbo.S2_XLLM_Exp_aveselect 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_avefrom (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 ExpCResfrom 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] = 806670group by Profile_ID,[ProgramCode/UnderwritingYear]) A right join S2_XLLM_PP_ave Bon A.Profile_ID=B.[Reserving Line] and A.[ProgramCode/UnderwritingYear]=B.[ProgramCode/UnderwritingYear]--Actual Paid & AVEif(@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 yoaEndif(@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 yoaEnd--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 piecesif exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_from_ave_gross') drop table dbo.S2_XLLM_PP_from_ave_grossexec ('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 procedureif exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_to_ave_gross') drop table dbo.S2_XLLM_PP_to_ave_grossexec ('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 procedureif exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_from_ave_ceded') drop table dbo.S2_XLLM_PP_from_ave_cededexec ('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 procedureif exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_to_ave_ceded') drop table dbo.S2_XLLM_PP_to_ave_cededexec ('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 ShawSQL Server MVP |
|
|
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 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-17 : 04:22:49
|
Glad to hear it's sorted--Gail ShawSQL Server MVP |
|
|
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))--ASDECLARE @Headers VARCHAR(1000)DECLARE @x VARCHAR(8000)DECLARE @Maxrows intDECLARE @RowCount intDECLARE @branch varchar(50)DECLARE @UploadID intDECLARE @InsurerID tinyintDECLARE @RecCount tinyintDECLARE @PolicyNumber varchar(25)DECLARE @Type char(3)DECLARE @BrokerIDSecondary INTDECLARE @Partnerid INTDECLARE @Brokerid INTDECLARE @SegmentID INT DECLARE @PolicyID INTDECLARE @CompanyName varchar (255) DECLARE @ClientID intDECLARE @BranchID intDECLARE @AgentcodeData varchar(255)DECLARE @AgentCodeID intDECLARE @TransactionDateChar char(8)DECLARE @TransactionDate datetimeDECLARE @EarningMonth intDECLARE @EarningYear intDECLARE @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 INTDECLARE @ProductID smallint DECLARE @PortFolioID smallintDECLARE @Sender varchar (100)DECLARE @SystemID intDECLARE @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 |
|
|
|
|
|
|
|