DECLARE MyCursor CURSOR FOR SELECT pkClaim FROM MedClaims_06_29_2007
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @pkClaim
WHILE (@@FETCH_STATUS <> -1)
BEGIN
-- Initialize the work fields
set @ClaimReserve_Medical = 0.00
set @ClaimReserve_Expense = 0.00
set @ClaimReserve_Comp = 0.00
set @ClaimReserve_Legal = 0.00
set @ReturnMedicalPTD = 0.00
set @ReturnExpensePTD = 0.00
set @ReturnLegalPTD = 0.00
set @ReturnCompPTD= 0.00
set @WriteNoteAndHistory = 0
set @MYfkParent = 0
-- Get the PTD dollars for this claim.
exec cst_ClaimPTD
@fkClaim = @pkClaim,
@SupressSelect = 1,
@MedicalPTD = @ReturnMedicalPTD output,
@ExpensePTD = @ReturnExpensePTD output,
@LegalPTD = @ReturnLegalPTD output,
@CompPTD = @ReturnCompPTD output
-- PROCESS MEDICAL
set @ClaimReserve_Medical =
(select ReserveAmt from ClaimReserve Where fkClaim = @pkClaim and ReserveType = 'Med' and History = 0)
if @ClaimReserve_Medical <> 0
Begin
update claimreserve set History = 1 where fkClaim = @pkClaim and ReserveType = 'Med'
insert into ClaimReserve (fkClaim, ReserveType, ReserveAmt, History, DateLup, UserLup, ChangeReason)
Values(@pkClaim,'Med',@ReturnMedicalPTD,0,@GetDate,'PriceT','Closing claim per policy. System generated per xxxx.')
set @WriteNoteAndHistory = 1
update claim set tpaclaimstatus = 'Closed', fkTPAClaimStatusReason = 15, TPAClaimStatusDate = @GetDate, UserLUP = 'PriceT', DateLUP = @GetDate
where pkclaim = @pkClaim
end
-- PROCESS EXPENSE
set @ClaimReserve_Expense =
(select ReserveAmt from ClaimReserve Where fkClaim = @pkClaim and ReserveType = 'Exp' and History = 0)
if @ClaimReserve_Expense <> 0
Begin
update claimreserve set History = 1 where fkClaim = @pkClaim and ReserveType = 'Exp'
insert into ClaimReserve (fkClaim, ReserveType, ReserveAmt, History, DateLup, UserLup, ChangeReason)
Values(@pkClaim,'Exp',@ReturnExpensePTD,0,@GetDate,'PriceT','Closing claim per policy. System generated per xxxx.')
set @WriteNoteAndHistory = 1
update claim set tpaclaimstatus = 'Closed', fkTPAClaimStatusReason = 15, TPAClaimStatusDate = @GetDate, UserLUP = 'PriceT', DateLUP = @GetDate
where pkclaim = @pkClaim
end
-- PROCESS LEGAL
set @ClaimReserve_Legal =
(select ReserveAmt from ClaimReserve Where fkClaim = @pkClaim and ReserveType = 'legal' and History = 0)
if @ClaimReserve_Legal <> 0
Begin
update claimreserve set History = 1 where fkClaim = @pkClaim and ReserveType = 'legal'
insert into ClaimReserve (fkClaim, ReserveType, ReserveAmt, History, DateLup, UserLup, ChangeReason)
Values(@pkClaim,'legal',@ReturnLegalPTD,0,@GetDate,'PriceT','Closing claim per policy. System generated per xxxx.')
set @WriteNoteAndHistory = 1
update claim set tpaclaimstatus = 'Closed', fkTPAClaimStatusReason = 15, TPAClaimStatusDate = @GetDate, UserLUP = 'PriceT', DateLUP = @GetDate
where pkclaim = @pkClaim
end
-- PROCESS COMP
set @ClaimReserve_Comp =
(select ReserveAmt from ClaimReserve Where fkClaim = @pkClaim and ReserveType = 'Comp' and History = 0)
if @ClaimReserve_Comp <> 0
Begin
update claimreserve set History = 1 where fkClaim = @pkClaim and ReserveType = 'Comp'
insert into ClaimReserve (fkClaim, ReserveType, ReserveAmt, History, DateLup, UserLup, ChangeReason)
Values(@pkClaim,'Comp',@ReturnCompPTD,0,@GetDate,'PriceT','Closing claim per policy. System generated per xxxxx.')
set @WriteNoteAndHistory = 1
update claim set tpaclaimstatus = 'Closed', fkTPAClaimStatusReason = 15, TPAClaimStatusDate = @GetDate, UserLUP = 'PriceT', DateLUP = @GetDate
where pkclaim = @pkClaim
end
-- IF SOMETHING CHANGED WRITE HISTORY AND A NOTE
if @WriteNoteAndHistory = 1
begin
set @MYfkParent = (Select fkLinkClaim from claim where pkclaim = @pkClaim)
--Write a note
exec [COMMONSQL].Notes.dbo.SaveNote
@Action = 'XXXXX', -- Just use anything. The SP doesn't care on insert
@Active = 1,
@CreateDate = @GetDate,
@CreateUserID = 'PriceT',
@fkCategory = 1133,
@fkParent = @MyfkParent,
@Note = 'Manually reviewed by xxxxxx. Closing in system at his request.',
@fkParentLocation = 6710, -- Production is 6710
@pkNote = NULL,
@Subject = 'Change in Reserves - Closing',
@CreateDateOriginal = @GetDate,
@CreateUserIDOriginal = 'PriceT',
@UserLUP = 'PriceT',
@DateLUP = @GetDate,
@WarningNote = 0,
@fkCapNote = null,
@fkCompany = 5,
@fkDepartment = 12,
@CapMemoNumber = NULL,
@HasAttachment = NULL,
@HasWorkingOn = NULL,
@Privilege = 37,
@ReturnPkNote = NULL,
@SelectReturn = NULL
-- Write claim history
exec dbo.cst_WriteClaimHistory @pkClaim,1
end
FETCH NEXT FROM MyCursor INTO @pkClaim
end
Close MyCursor
Deallocate MyCursor