July 4, 2013 at 3:22 am
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,@ImportOriginator
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
July 4, 2013 at 3:42 am
What is this all about?
If your question is about SQL 2008, why not post it in that forum?
If your question is about T-SQL, why post it in an SSIS forum?
What is your question?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 5, 2013 at 2:54 am
I am trying to import a flat file but everytime it fails but I cant fine where is failing ... please help...
oh yah is T-Sql
July 5, 2013 at 3:03 am
How about posting the code for the stored procedures which are called from this one?
There's nothing in this stored procedure related to importing a flat file - how have you determined that it is this stored procedure which is causing the problem?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2013 at 2:07 am
Thank you guys, it took me a while but i found the problem. It was inside the stored procedure usp_InsertPolicyHead. There's stored procedure that runs inside usp_InsertPolicyHead ... that's where it failing ...
Thank you very much ....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply