Skip to content

Commit b34fd20

Browse files
delcroipdelcroip
authored
Merge pull request #156 from openimis/release/22.10
Release/22.10
2 parents bcedcb1 + ff684aa commit b34fd20

File tree

4 files changed

+128
-33
lines changed

4 files changed

+128
-33
lines changed

‎sql/base/1_schema_tables.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -756,7 +756,7 @@ CREATE TABLE [dbo].[tblClaim](
756756
[Reinsured] [decimal](18, 2) NULL,
757757
[Valuated] [decimal](18, 2) NULL,
758758
[DateClaimed] [date] NOT NULL,
759-
[DateProcessed] [smalldatetime] NULL,
759+
[DateProcessed] [date] NULL,
760760
[Feedback] [bit] NOT NULL,
761761
[FeedbackID] [int] NULL,
762762
[Explanation] [ntext] NULL,

‎sql/migrations/1_migration_latest.sql‎

Lines changed: 100 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3520,3 +3520,103 @@ GO
35203520
IF EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'missing_index_384' AND object_id = OBJECT_ID('tblClaimServices'))
35213521
ALTER INDEX [missing_index_384] ON [dbo].[tblClaimServices] DISABLE
35223522
GO
3523+
3524+
--OTC 697
3525+
DROP INDEX [missing_index_215] ON [dbo].[tblClaim]
3526+
GO
3527+
3528+
DROP INDEX [missing_index_218] ON [dbo].[tblClaim]
3529+
GO
3530+
3531+
DROP INDEX [missing_index_242] ON [dbo].[tblClaim]
3532+
GO
3533+
3534+
DROP INDEX [missing_index_245] ON [dbo].[tblClaim]
3535+
GO
3536+
3537+
DROP INDEX [missing_index_306] ON [dbo].[tblClaim]
3538+
GO
3539+
3540+
DROP INDEX [missing_index_4896] ON [dbo].[tblClaim]
3541+
GO
3542+
3543+
DROP INDEX [missing_index_50] ON [dbo].[tblClaim]
3544+
GO
3545+
3546+
DROP INDEX [NCI_tblClaim_DateProcessed] ON [dbo].[tblClaim]
3547+
GO
3548+
3549+
IF COL_LENGTH(N'tblClaim', N'DateProcessed') IS NOT NULL
3550+
ALTER TABLE tbLClaim
3551+
ALTER COLUMN DateProcessed DATE NULL
3552+
GO
3553+
3554+
CREATE NONCLUSTERED INDEX [missing_index_215] ON [dbo].[tblClaim]
3555+
(
3556+
[ClaimStatus] ASC,
3557+
[ValidityTo] ASC,
3558+
[DateProcessed] ASC
3559+
)
3560+
INCLUDE([ClaimCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3561+
GO
3562+
3563+
CREATE NONCLUSTERED INDEX [missing_index_218] ON [dbo].[tblClaim]
3564+
(
3565+
[ClaimStatus] ASC,
3566+
[ReviewStatus] ASC,
3567+
[ValidityTo] ASC,
3568+
[DateProcessed] ASC
3569+
)
3570+
INCLUDE([ClaimCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3571+
GO
3572+
3573+
CREATE NONCLUSTERED INDEX [missing_index_242] ON [dbo].[tblClaim]
3574+
(
3575+
[ClaimStatus] ASC,
3576+
[ValidityTo] ASC,
3577+
[HFID] ASC,
3578+
[DateProcessed] ASC
3579+
)
3580+
INCLUDE([ClaimCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3581+
GO
3582+
3583+
CREATE NONCLUSTERED INDEX [missing_index_245] ON [dbo].[tblClaim]
3584+
(
3585+
[ClaimStatus] ASC,
3586+
[ValidityTo] ASC,
3587+
[HFID] ASC,
3588+
[DateProcessed] ASC
3589+
)
3590+
INCLUDE([ClaimCode],[Claimed]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3591+
GO
3592+
3593+
/****** Object: Index [missing_index_306] Script Date: 16/09/2022 12:37:04 ******/
3594+
CREATE NONCLUSTERED INDEX [missing_index_306] ON [dbo].[tblClaim]
3595+
(
3596+
[ClaimStatus] ASC,
3597+
[ValidityTo] ASC,
3598+
[DateProcessed] ASC
3599+
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3600+
GO
3601+
3602+
CREATE NONCLUSTERED INDEX [missing_index_4896] ON [dbo].[tblClaim]
3603+
(
3604+
[ClaimStatus] ASC,
3605+
[DateProcessed] ASC
3606+
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3607+
GO
3608+
3609+
CREATE NONCLUSTERED INDEX [missing_index_50] ON [dbo].[tblClaim]
3610+
(
3611+
[ClaimStatus] ASC,
3612+
[ValidityTo] ASC,
3613+
[DateProcessed] ASC
3614+
)
3615+
INCLUDE([ICDID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3616+
GO
3617+
3618+
CREATE NONCLUSTERED INDEX [NCI_tblClaim_DateProcessed] ON [dbo].[tblClaim]
3619+
(
3620+
[DateProcessed] ASC
3621+
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3622+
GO

‎sql/stored_procedures/uspConsumeEnrollments.sql‎

Lines changed: 27 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -584,6 +584,33 @@ CREATE PROCEDURE [dbo].[uspConsumeEnrollments](
584584
WHERE I.ValidityTo IS NULL AND I.IsHead = 1 AND I.CHFID IS NULL)
585585
RAISERROR(N'-1',16,1)
586586

587+
IF EXISTS(SELECT COUNT(1)
588+
FROM @tblInsuree TI
589+
LEFT OUTER JOIN tblInsuree I ON TI.CHFID = I.CHFID
590+
WHERE I.ValidityTo IS NULL
591+
AND I.IsHead = 1
592+
GROUP BY TI.FamilyID
593+
HAVING COUNT(1) > 1)
594+
595+
--Added by Amani
596+
BEGIN
597+
DELETE FROM @tblResult;
598+
SET @FamilyImported = 0;
599+
SET @FamilyRejected =0;
600+
SET @FamiliesUpd =0;
601+
SET @InsureeImported = 0;
602+
SET @InsureeUpd =0;
603+
SET @PolicyImported = 0;
604+
SET @PolicyImported = 0;
605+
SET @PolicyRejected = 0;
606+
SET @PremiumImported = 0
607+
INSERT INTO @tblResult VALUES
608+
(N'<h3 style="color:red;">Double HOF Found. <br />Please contact your IT manager for further assistant.</h3>')
609+
--GOTO EndOfTheProcess;
610+
611+
RAISERROR(N'-5',16,1)
612+
END
613+
587614
--Duplicate Receipt
588615
IF EXISTS(SELECT 1 FROM @tblPremium TPR
589616
INNER JOIN tblPremium PR ON PR.PolicyID = TPR.PolicyID AND TPR.Amount = PR.Amount AND TPR.Receipt = PR.Receipt
@@ -980,38 +1007,6 @@ CREATE PROCEDURE [dbo].[uspConsumeEnrollments](
9801007
END
9811008

9821009
SELECT @PolicyImported = ISNULL(COUNT(1),0) FROM @tblPolicy WHERE isOffline = 1
983-
984-
985-
986-
987-
988-
989-
IF EXISTS(SELECT COUNT(1)
990-
FROM tblInsuree
991-
WHERE ValidityTo IS NULL
992-
AND IsHead = 1
993-
GROUP BY FamilyID
994-
HAVING COUNT(1) > 1)
995-
996-
--Added by Amani
997-
BEGIN
998-
DELETE FROM @tblResult;
999-
SET @FamilyImported = 0;
1000-
SET @FamilyRejected =0;
1001-
SET @FamiliesUpd =0;
1002-
SET @InsureeImported = 0;
1003-
SET @InsureeUpd =0;
1004-
SET @PolicyImported = 0;
1005-
SET @PolicyImported = 0;
1006-
SET @PolicyRejected = 0;
1007-
SET @PremiumImported = 0
1008-
INSERT INTO @tblResult VALUES
1009-
(N'<h3 style="color:red;">Double HOF Found. <br />Please contact your IT manager for further assistant.</h3>')
1010-
--GOTO EndOfTheProcess;
1011-
1012-
RAISERROR(N'-5',16,1)
1013-
END
1014-
10151010

10161011
COMMIT TRAN ENROLL;
10171012

‎sql/stored_procedures/uspSSRSOverviewOfCommissions.sql‎

415 Bytes
Binary file not shown.

0 commit comments

Comments
 (0)