SHARE
TWEET

Untitled

a guest Sep 14th, 2017 77 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. INSERT INTO dbo.tblQBNextPaymentInfo
  2.             (
  3.             MemberID,
  4.             PremiumMonth,
  5.             PremiumYear,
  6.             InsuranceAmount,
  7.             AdminFeeAmount,
  8.             EmployerSubsidyAmount,
  9.             AEI2009SubsidyAmount,
  10.             LatestPostmarkDate
  11.             )
  12.     SELECT  qbit.MemberID,
  13.             PremiumMonth = MONTH(qbit.PremiumDueDate),
  14.             PremiumYear = YEAR(qbit.PremiumDueDate),
  15.             InsuranceAmount = qbit.InsuranceAmount,
  16.             BookableAdminFeeAmount = qbit.BookableAdminFeeAmount + qbit.RemittableAdminFeeAmount,
  17.             EmployerSubsidyAmount = qbit.EmployerSubsidyAmount,
  18.             AEI2009SubsidyAmount = qbit.AEI2009SubsidyAmount,
  19.             LatestPostmarkDate = MAX(   (CASE WHEN  (
  20.                                                     dbo.udfCalculateGracePeriodEndDate(qbit.PremiumFOM, qbit.DaysToMakeSubsequentPayments) <= qbit.FirstPaymentDueDate
  21.                                                     AND (QBITStatus IN ('P','PR','E45','TP') OR (QBITStatus = 'TE' AND TermReason = '45DAYPMTGPEXPIRED'))
  22.                                                     )
  23.                                             THEN qbit.FirstPaymentDueDate
  24.                                             ELSE dbo.udfCalculateGracePeriodEndDate(qbit.PremiumFOM, qbit.DaysToMakeSubsequentPayments)
  25.                                         END))
  26.     FROM    ( -- list of QBInsuranceTypes that have been elected or open-enrolled
  27.             SELECT  pp.MemberID,
  28.                     FirstPaymentDueDate = dbo.udfCalculateGracePeriodEndDate(   COALESCE(aeiqbit.AEI20092ndElectionPostMarkDate, qbit.ElectionPostMarkDate),
  29.                                                                                 qbit.DaysToMakeFirstPayment),
  30.                     qbit.DaysToMakeSubsequentPayments,
  31.                     TermReason = tr.Name,
  32.                     QBITStatus = qbits.Name,
  33.                     PremiumFOM = dbo.udfGetFirstOfMonthForDate(pp.PremiumDueDate),
  34.                     pp.PremiumDueDate,
  35.                     pp.InsuranceAmount,
  36.                     pp.BookableAdminFeeAmount,
  37.                     pp.RemittableAdminFeeAmount,
  38.                     pp.EmployerSubsidyAmount,
  39.                     pp.AEI2009SubsidyAmount                        
  40.             FROM    dbo.tblQBInsuranceType qbit WITH (NOLOCK)
  41.                     JOIN dbo.tblQBInsuranceTypePlan qbitp  WITH (NOLOCK) ON qbit.QBInsuranceTypeID = qbitp.QBInsuranceTypeID
  42.                     JOIN dbo.tblQBInsuranceTypeStatus  qbits WITH (NOLOCK) ON qbit.QBInsuranceTypeStatusID = qbits.QBInsuranceTypeStatusID
  43.                     JOIN (SELECT
  44.                                 MemberID,
  45.                                 dbo.udfGetFirstOfMonthForDate(PremiumDueDate) PremiumDueDate,
  46.                                 SUM(InsuranceAmount) InsuranceAmount,
  47.                                 SUM(BookableAdminFeeAmount) BookableAdminFeeAmount,
  48.                                 SUM(RemittableAdminFeeAmount) RemittableAdminFeeAmount,
  49.                                 SUM(EmployerSubsidyAmount) EmployerSubsidyAmount,
  50.                                 SUM(AEI2009SubsidyAmount) AEI2009SubsidyAmount
  51.                             FROM @tblProjectedPremiumsXml
  52.                             GROUP BY
  53.                                 MemberID,
  54.                                 dbo.udfGetFirstOfMonthForDate(PremiumDueDate)
  55.                         )
  56.                         pp ON qbit.MemberID = pp.MemberID
  57.                     LEFT JOIN dbo.tblQBInsuranceTypePlanTermination qbitpt WITH (NOLOCK) ON qbitp.QBInsuranceTypePlanID = qbitpt.QBInsuranceTypePlanID
  58.                     LEFT JOIN dbo.tblTerminationReason tr WITH (NOLOCK) ON qbitpt.TerminationReasonID = tr.TerminationReasonID
  59.                     LEFT JOIN dbo.tblAEI2009QB2ndElectionQBInsuranceType aeiqbit WITH (NOLOCK) ON qbit.QBInsuranceTypeID = aeiqbit.QBInsuranceTypeID
  60.             WHERE   pp.PremiumDueDate BETWEEN dbo.udfGetFirstOfMonthForDate(qbitp.StartDate) AND qbitp.EndDate
  61.                     AND 1 = dbo.udfQBPlanIsInUse(   COALESCE(aeiqbit.AEI20092ndElectionPostMarkDate, qbit.ElectionPostMarkDate),
  62.                                                     qbitp.StartDate,
  63.                                                     CASE WHEN aeiqbit.AEI20092ndElectionPostMarkDate IS NULL
  64.                                                         THEN qbit.EventFirstDayOfCoverage
  65.                                                         ELSE @AEI2009PlanStartDate
  66.                                                     END)
  67.             ) qbit
  68.     GROUP BY    qbit.MemberID,
  69.                 MONTH(qbit.PremiumDueDate),
  70.                 YEAR(qbit.PremiumDueDate),
  71.                 qbit.InsuranceAmount,
  72.                 qbit.BookableAdminFeeAmount,
  73.                 qbit.RemittableAdminFeeAmount,
  74.                 qbit.EmployerSubsidyAmount,
  75.                 qbit.AEI2009SubsidyAmount
RAW Paste Data
Pastebin PRO Autumn Special!
Get 40% OFF on Pastebin PRO accounts!
Top