Notes

/****** Object: StoredProcedure [dbo].[AnH_EditCart_Get] Script Date: 9/16/2025 6:54:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Shashank Upadhyaya
-- Create Date: 11/25/2024
-- Description: Fetching Premium Data
-- ===========================================
--select * from PolicyCart where PolicyCartId ='b969d2f9-9393-4846-b939-350836b1f740'
--select * from Payment
--select * from ESL_MDSLPremium
-- exec AnH_EditCart_Get 'cd6b59f4-4ace-4ae3-bd91-e9af02227237','ed00e714-e4f9-4171-9dbe-01c76ca6da54','05/2026','de0c7101-b0b6-4b7a-adf3-36327a3f3f8'
ALTER PROCEDURE [dbo].[AnH_EditCart_Get]
(
-- Add the parameters for the stored procedure here
@PolicyId uniqueidentifier,
@UserId uniqueidentifier = null,
--@ReportYear int,
--@ReportMonth int
@Reportperiod nvarchar(10),
@CartId uniqueidentifier
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

select r.CoverageDesc as Coverage
,r.CoverageTypeID
,r.CurrentEnrollment
,r.Adjustment
,r.UpdatedEnrollment
,r.RateTierDesc as SubCoverage
,r.RateTierID
,CAST(r.Rate as decimal(20,2)) as Rate
,r.PaymentDetailLineItemsId
from Payments p
inner join PolicyCart c on p.PolicyCartId = c.PolicyCartId
inner join PaymentDetails s on s.PaymentId = p.PaymentId
inner join PaymentDetailLineItems r on s.PaymentDetailId = r.PaymentDetailId
inner join Policies po on s.PolicyId = po.PolicyId
where po.PolicyId = @PolicyId and c.PolicyCartId = @CartId
and @Reportperiod = CAST(Format(s.ReportingPeriod,'MM/yyyy') as nvarchar(10)) order By r.CoverageTypeID ASC, r.RateTierID ASC

select CASt(p.PercentageOfPremium as decimal(20,2)) as PercentageOfPremium
,CAST(p.RatePerEmployee as decimal(20,2)) as Employee
,CAST(p.FlatAmount AS decimal(20,2)) as FlatRatePerMonth
,CAST(p.PercentageOfPremiumAmt as decimal(20,2)) as PercentageOfPremiumAmt
,CAST(p.RatePerEmployeeAmt as decimal(20,2)) as PerEmployeeAmt
,CAST(p.FlatAmt as decimal(20,2)) as FlatRatePerMonthAmt
,p.CoverateTypeID as CoverageTypeID
,p.PaymentDetailDeductionLineItemsId as PaymentDeductionLineItemId
,p.PaymentDetailID
,p.[IsPercentageOfPremiumSelected]
,p.[IsFlatRateSelected]
,p.[IsPerEmployeeSelected]

,pd.PolicyId
--INTO #TempPremium
from PaymentDetailDeductionLineItems p
inner join PaymentDetails pd on pd.PaymentDetailId = p.PaymentDetailId
inner join Payments py on pd.PaymentId = py.PaymentId
where pd.PolicyId = @PolicyId
and py.PaymentStatus = 'Pending'
and pd.UserId = @UserId and CAST(Format(ReportingPeriod,'MM/yyyy')as nvarchar(10)) = @Reportperiod

--select PolicyNumber,po.PolicyId,PolicyEffectiveDate,ProductType,ESLPolicyId,@Reportperiod as ReportingPeriod,pu.UserId
--,PolicyHolderName,pc.PolicyCartId,pc.CartStatus,pc.PolicyCartId as CartId,pd.PaymentType
--,pd.Deductions as PremiumDeductions,pd.GrossPremium,pd.NetPremium

--from Policies po
--inner join PaymentDetails pd on pd.PolicyId = po.PolicyId
--inner join PolicyUserMapping pu on po.PolicyId = pu.PolicyId
--inner join PolicyCart pc on pu.UserId = pc.UserId where po.PolicyId = @PolicyId and pu.UserId = @UserId
--and CAST(Format(ReportingPeriod,'MM/yyyy')as nvarchar(10)) = @Reportperiod

select
@Reportperiod as ReportingPeriod
,pd.UserId
,pd.PolicyId
,pd.GrossPremium
,pd.NetPremium,
pd.Deductions as PremiumDeductions,
-- Addtinal Fees get details
pd.TotalAddtionalfee as TotalAddtionalFee,
pc.PolicyCartId
,pc.CartStatus
,pd.PaymentType
,po.PolicyNumber
,po.PolicyHolderName
,po.PolicyEffectiveDate
,po.ProductType
,po.ESLPolicyId
,po.CompanyId

from Policies po
inner join PaymentDetails pd on po.PolicyId = pd.PolicyId
inner join Payments py on py.PaymentId = pd.PaymentId
inner join PolicyCart pc on pc.PolicyCartId = py.PolicyCartId
Where CAST(Format(pd.ReportingPeriod,'MM/yyyy')as nvarchar(10)) = @Reportperiod
and po.PolicyId = @PolicyId
and pd.UserId = @UserId
and pc.PolicyCartId = @CartId

select
pd.AdditionalPercentageOfPremium as PercentageOfPremium,
pd.AdditionalRatePerEmployee as RatePerEmployee,
pd.AdditionalFlatAmountPerMonth as FlatAmountPerMonth,
pd.AdditionalPercentageOfPremiumAmount as AddtionalPercentageAmount,
pd.AdditionalRatePerEmployeeAmount as AddtionalRatePerEmployeeAmount,
pd.AdditionalFlatAmount as AddtionalFlatAmount,
Case WHEN IsPercentageOfPremium = 'true' THEN 'ture'
ELSE 'false'
END as IsPercentage,
Case WHEN IsRatePerEmployee = 'true' THEN 'ture'
ELSE 'false'
END as IsRateperEmployee,
Case WHEN IsFlatAmountPerMonth = 'true' THEN 'ture'
ELSE 'false'
END as IsFlatAmountPerMonth
from Policies po
inner join PaymentDetails pd on po.PolicyId = pd.PolicyId
inner join Payments py on py.PaymentId = pd.PaymentId
inner join PolicyCart pc on pc.PolicyCartId = py.PolicyCartId
Where CAST(Format(pd.ReportingPeriod,'MM/yyyy')as nvarchar(10)) = @Reportperiod
and po.PolicyId = @PolicyId
and pd.UserId = @UserId
and pc.PolicyCartId = @CartId
END

exec AnH_EditCart_Get 'cd6b59f4-4ace-4ae3-bd91-e9af02227237','ed00e714-e4f9-4171-9dbe-01c76ca6da54','05/2026','de0c7101-b0b6-4b7a-adf3-36327a3f3f8'

Msg 8114, Level 16, State 5, Procedure AnH_EditCart_Get, Line 0 [Batch Start Line 134]
Error converting data type varchar to uniqueidentifier.