Below are details :-

Affected Files :

Database Objects/MasterCompanyDB/01_DDL/727_WT-13053_WTB_Report_ModTable_InvoiceBenefitwiseBilling.sql
Database Objects/MasterCompanyDB/04_Stored Procedures/OES_SP_GetBillingDetails_InvoiceBenefitWise.sql
Database Objects/MasterCompanyDB/04_Stored Procedures/OES_SP_GetRetroBillingDetails_InvoiceBenefitWise.sql
Database Objects/MasterCompanyDB/04_Stored Procedures/OES_SP_Report_GetBillingDetailsColumnWise_InvoiceBenefitWise.sql

Affected Area :
InvoiceBenefitwiseBilling table structure
Adjustment section for plans with prorated Add/Term policy

Tested Scenarios :
Plans Considered :
1) Health - Plan with Billed Rate
Plan With Self Insured Component rate
2 )Non Health
Billing Policies Combination for above plans :
1) Add - Prorated Term -Prorated
2)Add - Entire Term - Entire
3)Add - Entire Term - Prorated
4)Add - Prorated Term -Entire
5)Add - Entire Term - Waived Cut off
6) Add - Waived Cut off Term - Entire
7) Add - Waived Cut off Term - Waived Cut off
For all above plans and billing policies combination, verified whether proper adjustments are being displayed on invoice when 2-3 times mid month election changes are done.
Also considered plans with multiple rate with different effective dates

3 C's :
Concern : Incorrect adjustments appearing for mid month coverage changes done multiple times for plan having Prorated policy set
Cause: When 4-5 times mid month election changes happens for plans having prorated billing Add/Term policy set, the logic to calculate proper returns is failing for this scenario. The failure happened as there is no track of the original monthly cost and last term date.
Correction: Store monthly cost and previous election termination date for every record in the invoice tracking table.
For calculating returns, consider monthly cost from last term date for plans with prorated add/term policy.

Hi Venkatesh Pujari,

Assigning this ticket to you as it is LB deployed.

cc:Sachin Hingole

Hi Jyoti Agrawal,

Tested this fix on Codemap on For QA CHMC Azure company. All the scenarios worked as per expectation. This is ready for Stage.

Prasanna Karlekar,Sachin Hingole

Thanks,
Venkatesh

Hi Jyoti Agrawal,

Tested this fix on Production on City Of Bellevue Of Hspl company. All the scenarios worked as per expectation.

Sachin Hingole

Thanks,
Venkatesh