Here you go!
CREATE VIEW [dbo].[WebAPI_BenefitPlan]
AS
with StartStructure as
(
select min (BenefitRateStructures.BeginDate) as 'StartDate', BenefitPlans.BenefitPlanID
from BenefitRateStructures
inner join BenefitPlans on dbo.BenefitPlans.BenefitPlanID = dbo.BenefitRateStructures.BenefitPl anID
group by BenefitPlans.BenefitPlanID
),
EndStructure as
(
select max (
case BenefitRateStructures.EndDate
WHEN '1900-01-01 00:00:00.000' THEN '12/31/9999'
ELSE BenefitRateStructures.EndDate
END
) as 'EndDate', BenefitPlans.BenefitPlanID
from BenefitRateStructures
inner join BenefitPlans on dbo.BenefitPlans.BenefitPlanID = dbo.BenefitRateStructures.BenefitPl anID
group by BenefitPlans.BenefitPlanID
)
select dbo.WEBAPI_GetID(2, dbo.BenefitPlans.BenefitPlanID) AS ID
, dbo.WEBAPI_GetID(12, dbo.BenefitPlans.SponsorID) AS 'CarrierSponsor_ID'
, [Description] as 'Name'
, [NickName] as 'Code'
, StartStructure.StartDate
, EndStructure.EndDate
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.BenefitPlans.BenefitPlanID)) AS PagingID
from benefitplans
left outer join StartStructure on Benefitplans.benefitplanid = StartStructure.BenefitPlanID
left outer join EndStructure on Benefitplans.benefitplanid = EndStructure.BenefitPlanID
--------------------
CREATE view [dbo].[WebAPI_CarrierSponsor]
as
-- NOTE: You can't edit/save this through SQL 2005 (no SP)
with FirstAddress as
(
SELECT MIN(dbo.Addresses.AddressID)as 'ID', Sponsors.SponsorID
FROM dbo.Sponsors INNER JOIN
dbo.SystemInformationLinkTable ON Sponsors.SponsorID = dbo.SystemInformationLinkTable.ModuleIdent INNER JOIN
dbo.Addresses ON dbo.Addresses.AddressID = dbo.SystemInformationLinkTable.TableLinkIdent
WHERE
(dbo.SystemInformationLinkTable.ModuleTypeID = 2) AND (dbo.SystemInformationLinkTable.InformationTypeID = 1)
GROUP BY Sponsors.SponsorID
)
SELECT
dbo.WEBAPI_GetID(12, dbo.sponsors.SponsorID) AS ID,
dbo.WEBAPI_GetID(1, dbo.sponsors.CompanyID) AS ClientEmployer_ID,
Sponsors.[Name] as 'Name',
Sponsors.NickName as 'Code',
isnull(dbo.Addresses.AddressLine1, '') as AddressLine1,
isnull(dbo.Addresses.AddressLine2, '') as AddressLine2,
isnull(dbo.Addresses.City, '') as City,
isnull(dbo.Addresses.State, '') as State,
isnull(dbo.Addresses.PostalCode, '') AS ZipCode
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.sponsors.SponsorID)) AS PagingID
FROM Sponsors
Left Outer Join FirstAddress on FirstAddress.SponsorID = Sponsors.SponsorID
Left Outer join Addresses on FirstAddress.ID = Addresses.AddressID
---------------------
CREATE view [dbo].[WebAPI_ClientEmployer]
as
with FirstAddress as
(
SELECT MIN(dbo.Addresses.AddressID)as 'ID', Companies.CompanyID
FROM dbo.Companies AS Companies INNER JOIN
dbo.SystemInformationLinkTable ON Companies.CompanyID = dbo.SystemInformationLinkTable.ModuleIdent INNER JOIN
dbo.Addresses ON dbo.Addresses.AddressID = dbo.SystemInformationLinkTable.TableLinkIdent
WHERE
(dbo.SystemInformationLinkTable.ModuleTypeID = 1) AND (dbo.SystemInformationLinkTable.InformationTypeID = 1)
GROUP BY Companies.CompanyID
)
SELECT dbo.WEBAPI_GetID(1, dbo.Companies.CompanyID) AS ID
, dbo.Companies.CompanyCode AS CODE
, dbo.Companies.CompanyName AS NAME
, Addresses_1.AddressLine1
, Addresses_1.AddressLine2
, Addresses_1.City
, Addresses_1.State
, Addresses_1.PostalCode as 'ZipCode'
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.Companies.CompanyID)) AS PagingID
FROM dbo.Companies LEFT OUTER JOIN
FirstAddress ON FirstAddress.CompanyID = dbo.Companies.CompanyID inner JOIN
dbo.Addresses AS Addresses_1 ON Addresses_1.AddressID = FirstAddress.ID
---------------------
CREATE VIEW [dbo].[WebAPI_QB_BenefitPlan]
AS
SELECT
distinct dbo.WEBAPI_GetID(15, dbo.QBBenefitAssignments.BenefitAssignmentID) AS 'ID',
dbo.WEBAPI_GetID(2, dbo.BenefitPlans.BenefitPlanID) AS 'BenefitPlan_ID', dbo.WEBAPI_GetID(3, dbo.QBBenefitAssignments.QBAutoID) AS 'QB_ID',
dbo.QBGroupInformationBenefitCache.CurrentPremium,
dbo.QBGroupInformationBenefitCache.CurrentFee,
dbo.QBBenefitAssignments.FirstDayAfterLossCoverage as 'FirstDayAfterLossOfCoverage'
,CASE dbo.benefitplans.PlanTypeID
WHEN 7 THEN dbo.qbbenefitassignments.FlatFee
ELSE NULL
END as 'LifePlanCoverageAmount'
,
CASE dbo.benefitplans.benefitplancostingtypeid
WHEN 1 THEN BenefitRateTiers.description
WHEN 2 THEN MatrixCostingMatrixInformation.[Name]
WHEN 3 THEN 'Flat Fee'
ELSE NULL
END as 'CoverageLevelDescription'
, dbo.BenefitSubsidy.SubsidyEndDate as 'SubsidyEndDate'
, dbo.BenefitSubsidy.DollarAmount as 'SubsidyDollarAmount'
, dbo.BenefitSubsidy.PercentageAmount as 'SubsidyPercentage'
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.QBBenefitAssignments.BenefitAssignmentID)) AS PagingID
FROM dbo.BenefitPlans
INNER JOIN dbo.QBBenefitAssignments ON dbo.BenefitPlans.BenefitPlanID = dbo.QBBenefitAssignments.BenefitPlanID
INNER JOIN dbo.QBGroupInformationBenefitCache ON dbo.BenefitPlans.BenefitPlanID = dbo.QBGroupInformationBenefitCache.BenefitPlanID
INNER JOIN dbo.QB ON dbo.QBBenefitAssignments.QBAutoID = dbo.QB.QBAutoID
INNER JOIN dbo.QBGroups ON dbo.QB.QBGroupID = dbo.QBGroups.QBGroupID AND dbo.QBGroupInformationBenefitCache.QBGroupID = dbo.QBGroups.QBGroupID
LEFT OUTER JOIN dbo.BenefitSubsidy
ON dbo.qbgroups.qbgroupid = dbo.BenefitSubsidy.qbgroupid
AND dbo.qbbenefitassignments.benefitplanid = dbo.BenefitSubsidy.benefitplanid
left outer join BenefitRateTiers on BenefitRateTiers.TierID = QBBenefitAssignments.TierID
left outer join dbo.MatrixCosting on MatrixCosting.MatrixCostingID = QBBenefitAssignments.MatrixCostingID
left outer JOIN MatrixCostingMatrixInformation on MatrixCostingMatrixInformation.MatrixID = MatrixCosting.MatrixID
------------------------
CREATE view [dbo].[WebAPI_QB]
as
with FirstAddress as
(
SELECT MIN(dbo.Addresses.AddressID)as 'ID', QB.QBAutoID
FROM dbo.QB INNER JOIN
dbo.SystemInformationLinkTable ON QB.QBAutoID = dbo.SystemInformationLinkTable.ModuleIdent INNER JOIN
dbo.Addresses ON dbo.Addresses.AddressID = dbo.SystemInformationLinkTable.TableLinkIdent
WHERE
(dbo.SystemInformationLinkTable.ModuleTypeID = 3) AND (dbo.SystemInformationLinkTable.InformationTypeID = 1)
GROUP BY QB.QBAutoID
)
,
Terminations as
(
SELECT MAX(dbo.QBTermination.QBTerminationID)as 'QBTerminationID', QBGroups.QBGroupID
FROM dbo.QBGroups
INNER JOIN QBTermination on QBTermination.QBGroupID = QBgroups.QBGroupID
INNER JOIN QBTerminationReason on QBTerminationReason.QBTerminationReasonID = QBTermination.QBTerminationReasonID
GROUP BY QBGroups.QBGroupID
)
SELECT dbo.WEBAPI_GetID(3, dbo.QB.QBAutoID) AS ID, dbo.WEBAPI_GetID(1, dbo.QBGroups.CompanyID) AS ClientEmployer_ID, dbo.WEBAPI_GetID(3,
dbo.QBGroups.DesignatedGroupContact) AS BillingQBID, dbo.QB.LastName, dbo.QB.FirstName, dbo.QB.SSN, dbo.QB.QBID,
dbo.QBGroupInformationCache.PremiumsPaidTo, dbo.QBGroupInformationCache.TotalMonthlyPremium,
dbo.QBGroupInformationCache.TotalMonthlyAdminFee, dbo.QBGroupInformationCache.TotalMonthlyDue,
dbo.QBGroupInformationCache.AccountBalance, dbo.QBGroupInformationCache.MinimumMonthlyDue,
dbo.QBGroupInformationCache.PremiumDueDate, dbo.QBGroupInformationCache.EnrollmentDate, dbo.QBGroupInformationCache.EnrollmendEndDate,
dbo.QBGroupInformationCache.TotalDueToCompleteEnrollment, dbo.QBGroupInformationCache.SocialSecurityStartDate,
dbo.QBGroupInformationCache.EnrollmendEndDate as 'EnrollmentEndDate', dbo.QBGroupInformationCache.ExpectedPremiumsPaidThrough, dbo.GetQBModuleData(dbo.QB.QBAutoID, 169) AS DateOfBirth,
dbo.GetAgeFromDOB(dbo.GetQBModuleData(dbo.QB.QBAutoID, 169), GETDATE()) AS Age, dbo.GetQBModuleData(dbo.QB.QBAutoID, 167) AS EmployeeID,
case rtrim(dbo.GetQBModuleData(dbo.QB.QBAutoID, 172))
when '' then '01/01/1900'
else dbo.GetQBModuleData(dbo.QB.QBAutoID, 172)
end as 'HireDate',
dbo.Addresses.AddressLine1, dbo.Addresses.AddressLine2, dbo.Addresses.City, dbo.Addresses.State,
dbo.Addresses.PostalCode AS ZipCode,
case QBGroupCobraCoverageType
when -1 then 'Employee'
when 1 then 'Federal'
when 2 then 'FederalAndState'
when 3 then 'State'
when 4 then 'DeniedCoverage'
else 'NotSpecified'
END as 'ContinuationCoverageType',
case QBGroupStatusID
when 1 then 'AddedNotNotified'
when 2 then 'Notified'
when 3 then 'EnrolledPendingPayment'
when 4 then 'Enrolled'
when 5 then 'Terminated'
when 6 then 'Employee'
when 7 then 'Employee'
when 8 then 'Denial'
when 9 then 'Denial'
when 10 then 'Enrolled'
else 'NotSpecified'
END as 'Status',
isnull(QBgroupAssociations.NickName, '') as 'DepartmentCode',
isnull(QBgroupAssociations.Description, '') as 'DepartmentName',
isnull(QBTermination.EffectiveDate, '01/01/1900') as 'TerminationDate',
isnull(QBTerminationReason.Description, '') as 'TerminationReason',
dbo.GetQBModuleData(dbo.QB.QBAutoID, 173) as 'Gender',
QBTypes.Description as 'Relationship'
,isnull(dbo.GetQBModuleData(dbo.QB.QBAutoID, 170),'') as 'Title'
,isnull(dbo.DateTimeFormat(dbo.qb.CreatedOn),'01/01/1900') as 'EntryDate'
,isnull(dbo.QBGroupInformationCache.SocialSecurityEndDate, '01/01/1900') as 'SocialSecurityEndDate'
,isnull(dbo.qbevents.Description, '') as 'EventType'
,isnull(dbo.qbeventassignments.QualifyingEventDate, '01/01/1900') as 'EventDate'
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.QB.QBAutoID)) AS PagingID
FROM dbo.QBGroupInformationCache
INNER JOIN dbo.QBGroups ON dbo.QBGroupInformationCache.QBGroupID = dbo.QBGroups.QBGroupID
INNER JOIN dbo.QB ON dbo.QBGroups.QBGroupID = dbo.QB.QBGroupID
LEFT OUTER JOIN FirstAddress ON dbo.QB.QBAutoID = FirstAddress.QBAutoID
INNER JOIN dbo.Addresses ON dbo.Addresses.AddressID = FirstAddress.ID
LEFT OUTER JOIN QBGroupMembers on QBGroupMembers.QBgroupID = QBgroups.QBGroupID
LEFT OUTER JOIN QBgroupAssociations on QBgroupAssociations.QBgroupAssociationID = QBGroupMembers.QBgroupAssociationID
LEFT OUTER JOIN Terminations on Terminations.QBGroupID = QBGroups.QBGroupID
LEFT OUTER JOIN QBTermination on Terminations.QBTerminationID = QBTermination.QBTerminationID
LEFT OUTER JOIN QBTerminationReason on QBTerminationReason.QBTerminationReasonID = QBTermination.QBTerminationReasonID
INNER JOIN QBTypes on QBTypes.QBTypeID = QB.QBTypeID
LEFT OUTER JOIN dbo.qbeventassignments
ON dbo.qbeventassignments.qbautoid = dbo.qb.qbautoid
AND dbo.qbeventassignments.currentassignment = 1
LEFT OUTER JOIN dbo.qbevents
ON dbo.qbevents.qbeventid = dbo.qbeventassignments.qbeventid
------------------------
CREATE VIEW [dbo].[WebAPI_QB_Payment]
AS
SELECT dbo.WEBAPI_GetID(20, dbo.AccountingPayment.PaymentHistoryID) AS ID, dbo.WEBAPI_GetID(3, dbo.QB.QBAutoID) AS QB_ID,
dbo.AccountingPayment.AmountPayed AS PaymentAmount, dbo.AccountingPayment.DateEntered, dbo.AccountingPayment.CheckDate,
(SELECT CASE dbo.AccountingPayment.Void WHEN 1 THEN 'True' ELSE 'False' END AS Expr1) AS Void,
(SELECT CASE WHEN dbo.AccountingPayment.VoidedOn IS NULL THEN CAST('01/01/1900' AS datetime) ELSE voidedon END AS Expr1)
AS VoidedOn, dbo.AccountingPaymentMethod.Description AS PaymentMethod, dbo.AccountingPayment.Memo,
dbo.AccountingPayment.TransactionIdent
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.AccountingPayment.PaymentHistoryID)) AS PagingID
FROM dbo.AccountingPayment INNER JOIN
dbo.AccountingPaymentMethod ON dbo.AccountingPayment.PaymentMethodID = dbo.AccountingPaymentMethod.PaymentMethodID INNER JOIN
dbo.QBGroups ON dbo.AccountingPayment.DisconnectedQBGroupID = dbo.QBGroups.QBGroupID INNER JOIN
dbo.QB ON dbo.QBGroups.DesignatedGroupContact = dbo.QB.QBAutoID
GO
------------------------
CREATE VIEW [dbo].[WebAPI_QB_Correspondence]
AS
SELECT dbo.WEBAPI_GetID(0, dbo.CorrespondenceStorage.CorrespondenceStorageID) AS ID
, dbo.WEBAPI_GetID(3, dbo.CorrespondenceStorage.QBAutoID) AS QB_ID
, dbo.CorrespondenceStorage.QBAutoID as LetterTemplateTypeID
, dbo.LetterTemplateType.Name as LetterTemplateType
, dbo.CorrespondenceStorage.PrintedOn as PrintedDate
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.CorrespondenceStorage.CorrespondenceStorageID)) AS PagingID
FROM dbo.CorrespondenceStorage
INNER JOIN LetterTemplateType
ON dbo.CorrespondenceStorage.TemplateTypeID = dbo.LetterTemplateType.TemplateTypeID
WHERE dbo.CorrespondenceStorage.QBAutoID <> -1
Systems should be free (to interact with others) |
