Hi Yamilka Coca,

We could not work on this today.
We will provide an update on this by 10-Oct-2017.

Thank you!

cc: Gaurav Sodani

Hi Yamilka Coca,

As per analysis done till now, this issue is not similar to WT-9738. For the discrepancies mentioned in this JIRA, the rate is having single age band. Hence there is no issue of age band and cost mismatch which was the issue in WT-9738.
We have not found the root cause yet. Our analysis are in progress.

Thank you!

cc: Gaurav Sodani

Hi Yamilka Coca,

We are done with our analysis.

As per current reports implementation, cost appearing is proper. In reports we round up the cost to two decimal for each election. As we are rounding up the cost election wise, total difference appearing is what you have mentioned.
Following are the discrepancy details due to which cost in report is not as expected:
Basic Life : 105 records whose decimal value has increased due to rounding
Basic AD & D: 104 records whose decimal value has increased due to rounding
Voluntary Child Life : 38 records whose decimal value has decreased due to rounding

For more details, please refer WT-11470_Discrepancy.xlsx where election wise difference is mentioned.
Will check and update you by 16-Oct-2017, if any modification can be done to existing implementation to get the correct cost or not.

Please let us know, if you require any more details.

Thank you!

cc: Gaurav Sodani

Thank you for the update. I will wait to see the feedback provided on 10/16/2017. Thank you.

Hi Yamilka Coca,

Due to unplanned tasks worked on 16-Oct-2017 forgot about this issue and could not work on it.
Also, did not get time today to work on it.
Apologies for this delay.

Will try to provide an update latest by 18-Oct-2017.

Thank you!

cc: Gaurav Sodani

Hi Yamilka Coca,

Have checked the code. We will be modifying the code to round up the cost to 2 decimals at the time of display after all cost calculations are done and not election wise. This will help us in avoiding election wise discrepancies.

We have scheduled this change in Sprint-40, starting from 23-Oct-2017.

Thank you!

cc: Gaurav Sodani, [~parmeshwar.jumbade]

Thank you for the update. After reading to the root cause again, I have a few questions:
1. I am not sure I understand why the rounding only affected those 3 life plans and not all others?
2. Did this recently happen or has the report always worked this way?
3. If it is recent, when did it stop working as expected?
3. How many companies have been impacted by this?

I am happy a fix will be deployed on starting 10/23. To confirm, would this be a permanent fix for this report, so the premium discrepancy will not happen again?

Thank you again.

Hi Yamilka Coca,

Following are the updates for above queries:
1. I am not sure I understand why the rounding only affected those 3 life plans and not all others?
-We have pulled the discrepancies for mentioned plans only. This must have impacted other plans too depending on data present, billing policies getting applied while doing the cost calculation and scenarios when there is need to round up the cost.

2. Did this recently happen or has the report always worked this way?
-Billing Summary report has always worked like this.

3. If it is recent, when did it stop working as expected?
-This is not a recent change and Billing Summary Report has always worked like this.

4. How many companies have been impacted by this?
-All companies who are using the Billing Summary Report and scenario where rounding of cost was required. As mentioned in #1, this happens while processing the data for report when we need to round up the cost to display the output up to 2 decimal places. Cannot pull the exact list of companies, as this is not the issue where data is incorrect, but this is the issue while processing the data.

5. To confirm, would this be a permanent fix for this report, so the premium discrepancy will not happen again?
-Yes. This will be a permanent fix to resolve premium discrepancies related to round up issue.

Thank you!

cc: Gaurav Sodani, [~parmeshwar.jumbade]

Hello - Thank you for responding to my questions. Are we still on track for the fix to start today 10/23? Thank you again.

This is scheduled to start from 25-Oct-2017 and will get complete by 01-Nov-2017 in development environment. After which this will be deployed in QA environment for QA testing.

Thank you!

cc: [~parmeshwar.jumbade], Gaurav Sodani

3Cs:

Concern: Premium was not calculating correctly

Cause: In reports we round up the cost to two decimal for each election. As we are round up the cost election wise, total difference were displaying as like you have mentioned.

Correction: We have modified the script to round up the cost to 2 decimals at the time of display after all cost calculations are done and not election wise. This will help us in avoiding election wise discrepancies. After this changes we are doing cost calculation of elections based on actual cost and rounding up to the final values.

Affected Areas:
1. Billing summary report
2. Billing Reports ( Company Level Standard, DemoGraphics, Columnwise and Global level)

Affected Files:
/MasterCompanyDB/04_Stored Procedures/dbo.OES_SP_GetBillingDetails.StoredProcedure.sql
/MasterCompanyDB/04_Stored Procedures/dbo.OES_SP_GetBillingDetailsMonthly.StoredProcedure.sql
/MasterCompanyDB/04_Stored Procedures/dbo.OES_SP_GetBillingDetailsMonthly_Demographics.sql
/MasterCompanyDB/04_Stored Procedures/dbo.OES_SP_GetRetroBillingDetails.StoredProcedure.sql
/MasterCompanyDB/04_Stored Procedures/dbo.OES_SP_Report_GetBillingSummary.StoredProcedure.sql

Tested Scenarios:
1. Verified calculated premium on Billing summary report with Manually calculated premium.
2. Verified All impacted Billing reports (i.e. Company level and Global level).
3. Checked whether values of Employee and Employer tax are showing with 2 decimal points or not on Billing Report (For billing and retro records).
4. Checked stored procedure results through back-end.

Found some below issues:
1. Billing Summary Report: Premium not calculating correctly when multiple rates mapped
2. Billing Summary Report: Enrollment count mismatched in case of coverage and Age-band cost.
3. Billing Summary Report: Premiun should be show exact value in case of coverage divisor on salary. There is a case for current records and future salary records.
4. Standard Report: Billing Report throwing exception when Column-wise and Demographics check-box checked same time

CC: Prasanna Karlekar.

Thank you for the update. I will review the information provided and also review the report. I will provide feedback once completed.

To confirm the update has been released in Production, correct?

Hi Yamilka Coca,

This changes still in development environment and will get complete by 01-Nov-2017. After which this will be deployed in QA environment for QA testing.

CC: Prasanna Karlekar

Priya Handepatil - Please take it on priority.

[~meghana joshi]

Hi [~parmeshwar.jumbade]

I have verified this issue on LB

Verified with Company " FDU For HSPL"

Its working fine
BillingSummaryReport.xls

Hence, Marking it as ready for stage

cc: Sachin Hingole Prasad Pise Rakesh Roy Hrishikesh Deshpande

Thank You !

Hi Prasanna Karlekar

Billing summary report
Calculate premiums for following plans and compare with manual premium calculation:
1.Basic Life - Basic Life - Lincoln Financial Group-pass

2.Basic AD&D-Basic AD&D-Lincoln Financial Group

*3.Voluntary Child Life-Voluntary Child Life-Lincoln Financial Group-Fail

There is a difference of $0.01.*
4.2016 - 2018 Vol Spouse life-Vol Spouse Life-Pass

Checked whether values of Employee and Employer tax are showing with 2 decimal points or not on Billing Report -Pass

CC: Prasad Pise Hrishikesh Deshpande Rakesh Roy Sachin Hingole

Thanks,
Sanjana Jadhav

Hi Sanjana Jadhav,

I have verified point #3 and this is not an issue. On report we are showing rates only with 4 decimal points (i.e. 0.1933) but in system actual value of the same rate present with more decimal points (i.e. 0.199333).

So, while calculating premium ((0.199333*270000)/1000)= 53.81991 considering actual value of rate as (i.e. 0.199333) and after round-up final value with two decimal it is showing value 53.82 which is correct.

CC: Prasanna Karlekar Gaurav Sodani Sachin Hingole Rakesh Roy Prasad Pise

As per discussion with parmeshwar, this is not an issue.Working properly.
Ready for production.

CC: Prasad PiseRakesh Roy Hrishikesh Deshpande Sachin Hingole

Thanks,
Sanjana Jadhav

Hi All - any update on this?

Jenni

Hi Jennifer Johnson,
The code fix has been done and is ready for production. However, we have kept majority of the code releases on Production or stage on hold during the OE. We would request you to kindly reach out to CB team and take this up for production deployment approval.

We are also taking this up for production deployment approval by our side as well.

CC: Prasanna Karlekar Prasad Pise Gaurav Sodani Satya

Hi All,

Attaching the development approval email for your reference

Regards
Gaurav

Hi All,

This ticket has been approved for stage deployment on 12/21/17.

Regards
Gaurav

Hi Yamilka Coca

We have verified the rate calculations & values of "Billing Summary Report" on production for " Superheat FGH For HSPL" company. No discrepancy observed.

PFA following screenshots.

Could you please confirm.
CC Sanjana Jadhav [~parmeshwar.jumbade]SatyaGaurav Sodani

Hi All,

Attaching the production deployment approval email for reference

Regards
Gaurav