Skip to content

Commit 13aa933

Browse files
dragos-dobredragos-dobre
authored
Finish hotfix/perf-capitation
Hotfix/perf capitation
2 parents bb9b95a + d4fe4e1 commit 13aa933

9 files changed

+761
-1511
lines changed

‎sql/base/6_bundle_stored_procedures.sql‎

Lines changed: 0 additions & 719 deletions
Large diffs are not rendered by default.

‎sql/migrations/1_migration_latest.sql‎

Lines changed: 0 additions & 728 deletions
Large diffs are not rendered by default.

‎sql/stored_procedures/uspCleanBatchRun.sql‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
/****** Object: StoredProcedure [dbo].[uspCleanBatchRun] Script Date: 2/6/2022 12:22:44 AM ******/
2+
IF OBJECT_ID('uspCleanBatchRun', 'P') IS NOT NULL
3+
DROP PROCEDURE uspCleanBatchRun
4+
GO
5+
6+
CREATE PROCEDURE [dbo].[uspCleanBatchRun]( @runid int ) AS
7+
BEGIN
8+
declare @ClaimID TABLE (ClaimID int)
9+
-- get the concerned claims
10+
INSERT INTO @ClaimID(ClaimID)
11+
SELECT CLAIMID FROM tblClaim
12+
WHERE RunID = @runid
13+
--remove link and remunerated value for claims, items and services
14+
UPDATE tblClaim SET RunID = NULL, Remunerated = NULL, ClaimStatus = 8
15+
FROM tblClaim C WITH (NOLOCK) inner JOIN @ClaimID tpc on tpc.claimID = C.claimID WHERE c.runid is not null
16+
UPDATE tblClaimItems SET ClaimItemStatus = 1, RemuneratedAmount = NULL
17+
FROM tblClaimItems WITH (NOLOCK) inner JOIN @ClaimID tpc on tpc.claimID = tblClaimItems.claimID
18+
WHERE ValidityTo is NULL
19+
UPDATE tblClaimServices SET ClaimServiceStatus = 1, RemuneratedAmount = NULL
20+
FROM tblClaimServices WITH (NOLOCK) inner JOIN @ClaimID tpc on tpc.claimID = tblClaimServices.claimID
21+
WHERE ValidityTo is NULL
22+
-- remove the related indexes
23+
DELETE idx FROM [tblRelIndex] idx
24+
INNER JOIN tblProduct p on idx.ProdID = p.ProdID
25+
LEFT JOIN tblBatchRun br on p.LocationId = br.LocationId and idx.RelPeriod = br.RunMonth and idx.RelYear = br.RunYear
26+
WHERE br.RunID = @runid
27+
-- remove the run id
28+
DELETE FROM tblBatchRun WHERE tblBatchRun.RunID = @runid or tblBatchRun.LegacyID = @runid
29+
END

‎sql/stored_procedures/uspCreateCapitationPaymentReportData.sql‎

Lines changed: 112 additions & 58 deletions
Original file line numberDiff line numberDiff line change
@@ -34,9 +34,9 @@ BEGIN
3434
@WeightAdjustedAmount DECIMAL(5, 2)
3535

3636

37-
DECLARE @FirstDay DATE = CAST(@Year AS VARCHAR(4)) + '-' + CAST(@Month AS VARCHAR(2)) + '-01';
37+
DECLARE @FirstDay DATE = CAST(@Year AS VARCHAR(4)) + '-' + CAST(@Month AS VARCHAR(2)) + '-01'
3838
DECLARE @LastDay DATE = EOMONTH(CAST(@Year AS VARCHAR(4)) + '-' + CAST(@Month AS VARCHAR(2)) + '-01', 0)
39-
DECLARE @DaysInMonth INT = DATEDIFF(DAY,@FirstDay,DATEADD(MONTH,1,@FirstDay));
39+
DECLARE @DaysInMonth INT = DATEDIFF(DAY,@FirstDay,DATEADD(MONTH,1,@FirstDay))
4040

4141
set @DistrictId = CASE @DistrictId WHEN 0 THEN NULL ELSE @DistrictId END
4242

@@ -45,59 +45,101 @@ BEGIN
4545
LocationName VARCHAR(50),
4646
LocationCode VARCHAR(8),
4747
ParentLocationId INT
48-
);
49-
48+
)
49+
-- fetch all the region and districts
5050
INSERT INTO @Locations
5151
SELECT 0 LocationId, N'National' LocationName, NULL ParentLocationId, 0 LocationCode
5252

5353
UNION ALL
5454

5555
SELECT LocationId,LocationName, LocationCode, ISNULL(ParentLocationId, 0)
5656
FROM tblLocations
57-
WHERE (ValidityTo IS NULL )
58-
AND (LocationId = ISNULL(@DistrictId, @RegionId) OR
59-
(LocationType IN ('R', 'D') AND ParentLocationId = ISNULL(@DistrictId, @RegionId)))
60-
57+
WHERE ValidityTo IS NULL
58+
AND LocationType IN ('R', 'D')
6159

6260
DECLARE @LocationTemp table (LocationId int, RegionId int, RegionCode [nvarchar](8) , RegionName [nvarchar](50), DistrictId int, DistrictCode [nvarchar](8),
6361
DistrictName [nvarchar](50), ParentLocationId int)
6462

65-
63+
-- create table with locaiton hiearchy
6664
INSERT INTO @LocationTemp(LocationId , RegionId , RegionCode , RegionName , DistrictId , DistrictCode ,
67-
DistrictName , ParentLocationId)( SELECT ISNULL(d.LocationId,r.LocationId) LocationId , r.LocationId as RegionId , r.LocationCode as RegionCode , r.LocationName as RegionName , d.LocationId as DistrictId , d.LocationCode as DistrictCode ,
68-
d.LocationName as DistrictName , ISNULL(d.ParentLocationId,r.ParentLocationId) ParentLocationId FROM @Locations d INNER JOIN @Locations r on d.ParentLocationId = r.LocationId
69-
UNION ALL SELECT r.LocationId, r.LocationId as RegionId , r.LocationCode as RegionCode , r.LocationName as RegionName , NULL DistrictId , NULL DistrictCode ,
70-
NULL DistrictName , ParentLocationId FROM @Locations r WHERE ParentLocationId = 0)
71-
;
72-
declare @listOfHF table (id int);
73-
65+
DistrictName , ParentLocationId)
66+
( SELECT ISNULL(d.LocationId,r.LocationId) LocationId ,
67+
r.LocationId as RegionId ,
68+
r.LocationCode as RegionCode ,
69+
r.LocationName as RegionName ,
70+
d.LocationId as DistrictId ,
71+
d.LocationCode as DistrictCode ,
72+
d.LocationName as DistrictName ,
73+
ISNULL(d.ParentLocationId,r.ParentLocationId) ParentLocationId
74+
FROM @Locations d
75+
INNER JOIN @Locations r on d.ParentLocationId = r.LocationId
76+
UNION ALL
77+
SELECT r.LocationId, r.LocationId as RegionId ,
78+
r.LocationCode as RegionCode ,
79+
r.LocationName as RegionName ,
80+
NULL DistrictId ,
81+
NULL DistrictCode ,
82+
NULL DistrictName , ParentLocationId
83+
FROM @Locations r WHERE ParentLocationId = 0)
84+
85+
declare @listOfHF table (id int)
86+
-- get the list of Hf part of the covered regions and config
7487
IF @RegionId IS NULL or @RegionId =0
75-
INSERT INTO @listOfHF(id) SELECT tblHF.HfID FROM tblHF WHERE tblHF.ValidityTo is NULL;
88+
INSERT INTO @listOfHF(id) SELECT HF.HfID FROM tblHF HF WHERE HF.ValidityTo is NULL
89+
AND(
90+
((HF.HFLevel = @Level1) AND (HF.HFSublevel = @Sublevel1 OR @Sublevel1 IS NULL))
91+
OR ((HF.HFLevel = @Level2 ) AND (HF.HFSublevel = @Sublevel2 OR @Sublevel2 IS NULL))
92+
OR ((HF.HFLevel = @Level3) AND (HF.HFSublevel = @Sublevel3 OR @Sublevel3 IS NULL))
93+
OR ((HF.HFLevel = @Level4) AND (HF.HFSublevel = @Sublevel4 OR @Sublevel4 IS NULL))
94+
)
7695
ELSE IF @DistrictId is NULL or @DistrictId =0
77-
INSERT INTO @listOfHF(id) SELECT tblHF.HfID FROM tblHF JOIN tblLocations l on tblHF.LocationId = l.LocationId WHERE l.ParentLocationId = @RegionId ;
96+
INSERT INTO @listOfHF(id) SELECT HF.HfID FROM tblHF HF JOIN tblLocations l on HF.LocationId = l.LocationId WHERE l.ParentLocationId = @RegionId
97+
AND(
98+
((HF.HFLevel = @Level1) AND (HF.HFSublevel = @Sublevel1 OR @Sublevel1 IS NULL))
99+
OR ((HF.HFLevel = @Level2 ) AND (HF.HFSublevel = @Sublevel2 OR @Sublevel2 IS NULL))
100+
OR ((HF.HFLevel = @Level3) AND (HF.HFSublevel = @Sublevel3 OR @Sublevel3 IS NULL))
101+
OR ((HF.HFLevel = @Level4) AND (HF.HFSublevel = @Sublevel4 OR @Sublevel4 IS NULL))
102+
)
78103
ELSE
79-
INSERT INTO @listOfHF(id) SELECT tblHF.HfID FROM tblHF WHERE tblHF.LocationId = @DistrictId and tblHF.ValidityTo is NULL;
80-
81-
82-
SELECT @Level1 = Level1, @Sublevel1 = Sublevel1, @Level2 = Level2, @Sublevel2 = Sublevel2, @Level3 = Level3, @Sublevel3 = Sublevel3,
83-
@Level4 = Level4, @Sublevel4 = Sublevel4, @ShareContribution = ISNULL(ShareContribution, 0), @WeightPopulation = ISNULL(WeightPopulation, 0),
84-
@WeightNumberFamilies = ISNULL(WeightNumberFamilies, 0), @WeightInsuredPopulation = ISNULL(WeightInsuredPopulation, 0), @WeightNumberInsuredFamilies = ISNULL(WeightNumberInsuredFamilies, 0),
85-
@WeightNumberVisits = ISNULL(WeightNumberVisits, 0), @WeightAdjustedAmount = ISNULL(WeightAdjustedAmount, 0)
104+
INSERT INTO @listOfHF(id) SELECT HF.HfID FROM tblHF HF WHERE HF.LocationId = @DistrictId and HF.ValidityTo is NULL
105+
AND(
106+
((HF.HFLevel = @Level1) AND (HF.HFSublevel = @Sublevel1 OR @Sublevel1 IS NULL))
107+
OR ((HF.HFLevel = @Level2 ) AND (HF.HFSublevel = @Sublevel2 OR @Sublevel2 IS NULL))
108+
OR ((HF.HFLevel = @Level3) AND (HF.HFSublevel = @Sublevel3 OR @Sublevel3 IS NULL))
109+
OR ((HF.HFLevel = @Level4) AND (HF.HFSublevel = @Sublevel4 OR @Sublevel4 IS NULL))
110+
)
111+
112+
-- get the product data
113+
SELECT @Level1 = Level1,
114+
@Sublevel1 = Sublevel1,
115+
@Level2 = Level2,
116+
@Sublevel2 = Sublevel2,
117+
@Level3 = Level3,
118+
@Sublevel3 = Sublevel3,
119+
@Level4 = Level4,
120+
@Sublevel4 = Sublevel4,
121+
@ShareContribution = ISNULL(ShareContribution, 0),
122+
@WeightPopulation = ISNULL(WeightPopulation, 0),
123+
@WeightNumberFamilies = ISNULL(WeightNumberFamilies, 0),
124+
@WeightInsuredPopulation = ISNULL(WeightInsuredPopulation, 0),
125+
@WeightNumberInsuredFamilies = ISNULL(WeightNumberInsuredFamilies, 0),
126+
@WeightNumberVisits = ISNULL(WeightNumberVisits, 0),
127+
@WeightAdjustedAmount = ISNULL(WeightAdjustedAmount, 0)
86128
FROM tblProduct Prod
87-
WHERE ProdId = @ProdId;
88-
129+
WHERE ProdId = @ProdId
89130

131+
-- get pop
90132
DECLARE @TotalPopFam TABLE (
91133
HFID INT,
92134
TotalPopulation DECIMAL(18, 6),
93135
TotalFamilies DECIMAL(18, 6)
94-
);
136+
)
95137

96138
INSERT INTO @TotalPopFam
97139

98140
SELECT C.HFID HFID ,
99-
CASE WHEN ISNULL(@DistrictId, @RegionId) IN (D.RegionId, D.DistrictId) THEN 1 ELSE 0 END * SUM((ISNULL(L.MalePopulation, 0) + ISNULL(L.FemalePopulation, 0) + ISNULL(L.OtherPopulation, 0)) *(0.01* Catchment)) TotalPopulation,
100-
CASE WHEN ISNULL(@DistrictId, @RegionId) IN (D.RegionId, D.DistrictId) THEN 1 ELSE 0 END * SUM(ISNULL(((L.Families)*(0.01* Catchment)), 0))TotalFamilies
141+
SUM((ISNULL(L.MalePopulation, 0) + ISNULL(L.FemalePopulation, 0) + ISNULL(L.OtherPopulation, 0)) *(0.01* Catchment)) TotalPopulation,
142+
SUM(ISNULL(((L.Families)*(0.01* Catchment)), 0))TotalFamilies
101143
FROM tblHFCatchment C
102144
LEFT JOIN tblLocations L ON L.LocationId = C.LocationId OR L.LegacyId = C.LocationId
103145
INNER JOIN tblHF HF ON C.HFID = HF.HfID
@@ -109,12 +151,12 @@ BEGIN
109151
GROUP BY C.HFID, D.DistrictId, D.RegionId
110152

111153

112-
154+
-- get insuree
113155
DECLARE @InsuredInsuree TABLE (
114156
HFID INT,
115157
ProdId INT,
116158
TotalInsuredInsuree DECIMAL(18, 6)
117-
);
159+
)
118160

119161
INSERT INTO @InsuredInsuree
120162

@@ -137,11 +179,11 @@ BEGIN
137179

138180

139181

140-
182+
-- get insured family
141183
DECLARE @InsuredFamilies TABLE (
142184
HFID INT,
143185
TotalInsuredFamilies DECIMAL(18, 6)
144-
);
186+
)
145187

146188
INSERT INTO @InsuredFamilies
147189
SELECT HC.HFID, COUNT(DISTINCT F.FamilyID)*(0.01 * Catchment) TotalInsuredFamilies
@@ -161,15 +203,11 @@ BEGIN
161203
AND HC.HFID in (SELECT id FROM @listOfHF)
162204
GROUP BY HC.HFID, Catchment--, L.LocationId
163205

164-
165-
166-
167-
168-
206+
-- get allocated contribution
169207
DECLARE @Allocation TABLE (
170208
ProdId INT,
171209
Allocated DECIMAL(18, 6)
172-
);
210+
)
173211

174212
INSERT INTO @Allocation
175213
SELECT ProdId, CAST(SUM(ISNULL(Allocated, 0)) AS DECIMAL(18, 6)) Allocated
@@ -186,14 +224,14 @@ BEGIN
186224
FROM tblPremium PR
187225
INNER JOIN tblPolicy PL ON PR.PolicyID = PL.PolicyID
188226
INNER JOIN tblProduct Prod ON Prod.ProdId = PL.ProdID
189-
INNER JOIN @Locations L ON ISNULL(Prod.LocationId, 0) = L.LocationId
227+
INNER JOIN @Locations L ON ISNULL(Prod.LocationId, 0) = L.LocationId and (L.LocationId = ISNULL(@DistrictId, @RegionId) OR ( L.ParentLocationId = ISNULL(@DistrictId, @RegionId)))
190228
WHERE PR.ValidityTo IS NULL
191229
AND PL.ValidityTo IS NULL
192230
AND PL.ProdID = @ProdId
193231
AND PL.PolicyStatus <> 1
194232
AND PR.PayDate <= PL.ExpiryDate
195233
GROUP BY PL.ProdID, PL.ExpiryDate, PR.PayDate,PL.EffectiveDate)Alc
196-
GROUP BY ProdId;
234+
GROUP BY ProdId
197235

198236

199237
DECLARE @ReportData TABLE (
@@ -229,35 +267,53 @@ BEGIN
229267
UPAdjustedAmount DECIMAL(18, 6)
230268

231269

232-
);
270+
)
233271

234272
DECLARE @ClaimValues TABLE (
235273
HFID INT,
236274
ProdId INT,
237275
TotalAdjusted DECIMAL(18, 6),
238276
TotalClaims DECIMAL(18, 6)
239-
);
277+
)
240278

241279
INSERT INTO @ClaimValues
242280
SELECT HFID, @ProdId ProdId, SUM(TotalAdjusted)TotalAdjusted, COUNT(DISTINCT ClaimId)TotalClaims FROM
243281
(
244-
SELECT HFID, SUM(PriceValuated)TotalAdjusted, ClaimId
282+
SELECT HFID, SUM(PriceAdjusted)TotalAdjusted, ClaimId
245283
FROM
246-
(SELECT HFID,c.ClaimId, PriceValuated FROM tblClaim C WITH (NOLOCK)
247-
LEFT JOIN tblClaimItems ci ON c.ClaimID = ci.ClaimID and ProdId = @ProdId AND (@WeightAdjustedAmount > 0.0)
248-
WHERE CI.ValidityTo IS NULL AND C.ValidityTo IS NULL
284+
(SELECT C.HFID,c.ClaimId, PriceAdjusted
285+
FROM tblClaim C WITH (NOLOCK)
286+
INNER JOIN tblClaimItems ci ON c.ClaimID = ci.ClaimID
287+
INNER JOIN tblHF HF ON HF.HFID = C.HFID
288+
WHERE CI.ValidityTo IS NULL AND C.ValidityTo IS NULL
249289
AND C.ClaimStatus > 4
250290
AND YEAR(C.DateProcessed) = @Year
251291
AND MONTH(C.DateProcessed) = @Month
252-
AND ci.ValidityTo IS NULL
292+
AND ci.ProdId = @ProdId
293+
AND (@WeightAdjustedAmount > 0.0 or @WeightNumberVisits > 0.0)
294+
AND (
295+
((HF.HFLevel = @Level1) AND (HF.HFSublevel = @Sublevel1 OR @Sublevel1 IS NULL))
296+
OR ((HF.HFLevel = @Level2 ) AND (HF.HFSublevel = @Sublevel2 OR @Sublevel2 IS NULL))
297+
OR ((HF.HFLevel = @Level3) AND (HF.HFSublevel = @Sublevel3 OR @Sublevel3 IS NULL))
298+
OR ((HF.HFLevel = @Level4) AND (HF.HFSublevel = @Sublevel4 OR @Sublevel4 IS NULL))
299+
)
253300
UNION ALL
254-
SELECT HFID, c.ClaimId, PriceValuated FROM tblClaim C WITH (NOLOCK)
255-
LEFT JOIN tblClaimServices cs ON c.ClaimID = cs.ClaimID and ProdId = @ProdId AND (@WeightAdjustedAmount > 0.0)
301+
SELECT C.HFID, c.ClaimId, PriceAdjusted
302+
FROM tblClaim C WITH (NOLOCK)
303+
INNER JOIN tblClaimServices cs ON c.ClaimID = cs.ClaimID
304+
INNER JOIN tblHF HF ON HF.HFID = C.HFID
256305
WHERE cs.ValidityTo IS NULL AND C.ValidityTo IS NULL
257306
AND C.ClaimStatus > 4
258307
AND YEAR(C.DateProcessed) = @Year
259308
AND MONTH(C.DateProcessed) = @Month
260-
AND CS.ValidityTo IS NULL
309+
AND cs.ProdId = @ProdId
310+
AND (@WeightAdjustedAmount > 0.0 or @WeightNumberVisits > 0.0)
311+
AND (
312+
((HF.HFLevel = @Level1) AND (HF.HFSublevel = @Sublevel1 OR @Sublevel1 IS NULL))
313+
OR ((HF.HFLevel = @Level2 ) AND (HF.HFSublevel = @Sublevel2 OR @Sublevel2 IS NULL))
314+
OR ((HF.HFLevel = @Level3) AND (HF.HFSublevel = @Sublevel3 OR @Sublevel3 IS NULL))
315+
OR ((HF.HFLevel = @Level4) AND (HF.HFSublevel = @Sublevel4 OR @Sublevel4 IS NULL))
316+
)
261317
) claimdetails GROUP BY HFID,ClaimId
262318
)claims GROUP by HFID
263319

@@ -297,20 +353,18 @@ BEGIN
297353
LEFT OUTER JOIN @TotalPopFam PF ON PF.HFID = HF.HfID
298354
LEFT OUTER JOIN @InsuredInsuree II ON II.HFID = HF.HfID
299355
LEFT OUTER JOIN @InsuredFamilies IFam ON IFam.HFID = HF.HfID
300-
-- LEFT OUTER JOIN @Claims C ON C.HFID = HF.HfID
356+
--LEFT OUTER JOIN @Claims C ON C.HFID = HF.HfID
301357
LEFT OUTER JOIN @ClaimValues CV ON CV.HFID = HF.HfID
302358
LEFT OUTER JOIN @Allocation A ON A.ProdID = @ProdId
303359

304360
WHERE HF.ValidityTo IS NULL
305361
AND (((L.RegionId = @RegionId OR @RegionId IS NULL) AND (L.DistrictId = @DistrictId OR @DistrictId IS NULL)) OR CV.ProdID IS NOT NULL OR II.ProdId IS NOT NULL)
306-
AND (HF.HFLevel IN (@Level1, @Level2, @Level3, @Level4) OR (@Level1 IS NULL AND @Level2 IS NULL AND @Level3 IS NULL AND @Level4 IS NULL))
307362
AND(
308-
(@Level1 IS NULL AND @Level2 IS NULL AND @Level3 IS NULL AND @Level4 IS NULL)
309-
OR ((HF.HFLevel = @Level1) AND (HF.HFSublevel = @Sublevel1 OR @Sublevel1 IS NULL))
363+
((HF.HFLevel = @Level1) AND (HF.HFSublevel = @Sublevel1 OR @Sublevel1 IS NULL))
310364
OR ((HF.HFLevel = @Level2 ) AND (HF.HFSublevel = @Sublevel2 OR @Sublevel2 IS NULL))
311365
OR ((HF.HFLevel = @Level3) AND (HF.HFSublevel = @Sublevel3 OR @Sublevel3 IS NULL))
312366
OR ((HF.HFLevel = @Level4) AND (HF.HFSublevel = @Sublevel4 OR @Sublevel4 IS NULL))
313-
);
367+
)
314368

315369

316370
INSERT INTO tblCapitationPayment(
@@ -345,7 +399,7 @@ BEGIN
345399
ISNULL(SUM(UPPopulation),0)UPPopulation, ISNULL(SUM(UPNumFamilies),0)UPNumFamilies, ISNULL(SUM(UPInsPopulation),0)UPInsPopulation,
346400
ISNULL(SUM(UPInsFamilies),0)UPInsFamilies, ISNULL(SUM(UPVisits),0)UPVisits,
347401
ISNULL(SUM(UPAdjustedAmount),0)UPAdjustedAmount, ISNULL(SUM(PaymentCathment),0)PaymentCathment, ISNULL(SUM(TotalAdjusted),0)TotalAdjusted
348-
FROM @ReportData GROUP BY HFCode) r;
402+
FROM @ReportData GROUP BY HFCode) r
349403
END
350404
GO
351405

0 commit comments

Comments
 (0)