Update Data Measurements

1. Audit Record

A. Audit Stage Update

Stuck audit
- The users can hit back space or click back button to go to the previous page which will misalign the audit stage. The audit also can get stuck due to incorrect setup of dynamic routing, audits being handled in unpredicted way.
Audit Reopen
- Sometimes the business wants to reopen the closed audit to certain audit stage, and they reopen to the stage they did not intend to now and then.

Steps

Using Audit_Stage_Update.sql, run select query to filter out right claim.
If the audit stage is not provided by the request, ask business which stage the audit should be in.
Get the audit stage no from the script and change the update query accordingly.
Copy update query to the new window and send out to Bud and Alexandra to be sent to DEVOPS.

B. Audit Info Update

Mainly for drop sample, wrong information can be keyed, such as sample date, report month. The script need to run so the auditors can see the correct data.

Steps

Using Audit Info_Update.sql, run select query to filter out right claims.
Copy update query to the new window and send out to Bud and Alexandra to be sent to DEVOPS.

2. Reference Table

A. Lookup Table Update

The business makes a request to update/remove/insert on few of the items which can be done by the setup page, or more than 10 lines needs a script.

Steps

Go to System -> Lookup Code
Select the category
Click Code # and update the field as requested.

B. Other than Lookup Table

If there are less than 10 lines of update, the business will use the existing web page to update. i.e. Root Cause List Setup

C. Bulk insert

If there are more than 10 lines of insert, use Bulk_Insert.sql

Note

Script below is using smart.sam_List_SOP as an example. `sql --Bulk_Insert.sql USE SAM_AUDIT GO INSERT INTO smart.sam_List_SOP (SOPName, Status, AdminNo, DateEntered)

SELECT 'RES43755 EDI Hold Hub',1,3,GETDATE() UNION SELECT 'RES44120 Delaware Infertility',1,3,GETDATE() UNION SELECT 'RES44230 McKinsey Homepage',1,3,GETDATE() UNION SELECT 'RES44235 NPS Reach Out Fields Glossary for Einstein (US / CANADA)',1,3,GETDATE() UNION SELECT 'RES44307 North America Middle East Compliance Training - Billing & Enrollment',1,3,GETDATE() UNION SELECT 'RES44311 Reasonable and Customary International Guidelines for McKinsey Claims (HDITA / ITA note process)',1,3,GETDATE() UNION SELECT 'RES44334 Infertility Benefits - McKinsey Non Partner',1,3,GETDATE() UNION SELECT 'RES44388 Infertility Benefits - McKinsey Partner',1,3,GETDATE() UNION SELECT 'RES44389 Gender Indentity Disorder - Partner McKinsey',1,3,GETDATE() UNION SELECT 'RES44390 Gender Indentity Disorder - Non Partner McKinsey',1,3,GETDATE() UNION SELECT 'RES44515 Diagnostic Services',1,3,GETDATE() UNION SELECT 'RES44606 Preventive Care - McKinsey',1,3,GETDATE() UNION SELECT 'RES44633 Prescription/OTC Guide for McKinsey',1,3,GETDATE() UNION SELECT 'RES44668 Germany � McKinsey Claims Organizational Guide',1,3,GETDATE() UNION SELECT 'RES44669 Diagnostic Services � McKinsey',1,3,GETDATE() UNION SELECT 'RES44729 Elective Egg Preservation Guidelines � Mexico',1,3,GETDATE() UNION SELECT 'RES44732 Elective Egg Preservation Claim Form - Mexico',1,3,GETDATE() UNION SELECT 'RES44775 Elective Egg Preservation Claim Form � Canada',1,3,GETDATE() UNION SELECT 'RES44776 Elective Egg Preservation Guidelines � Canada',1,3,GETDATE() UNION SELECT 'RES44785 NA Disability Tax Project - Claimant Outreach Talking Points',1,3,GETDATE() UNION SELECT 'RES44787 Payment instructions quick guide - McKinsey',1,3,GETDATE() UNION SELECT 'RES44788 Emergency Room diagnosis list � McKinsey',1,3,GETDATE() UNION SELECT 'RES44804 Mailing List - McKinsey',1,3,GETDATE() UNION SELECT 'RES44904 Claim Form - WWA - CLIC - Spanish',1,3,GETDATE() UNION SELECT 'RES44927 Long Term Disability Tax Compliance Claimant Letters',1,3,GETDATE() UNION SELECT 'RES44944 Post Claim Adjudication Audits',1,3,GETDATE()


##### Steps

1. Using Excel, generate the SELECT query as the one from [Bulk_Insert.sql](../../SAM3/Files/Data_Update/Bulk_Insert.sql).
2. Only SOPName needs to be replaced.
3. Save the document as new file name and send out to Bud and Alexandra to be sent to DEVOPS.

## 3. Sample Info

Some information are not updatable due to the risk it has to sampling which sometimes need update due to user error or change requested by the business.  Sample Type, Feed Source, Feed Frequency, Sampling Method, Projection Method, Routing, Error Receiver, Calculation Review Routing, Sample Group, and Sample Level require script.

##### Steps

1. Open [Sample_Info_Date.sql](../../SAM3/Files/Data_Update/Sample_Info_Update.sql) and run the select query on the information that needs an update.
2. Get the value and change the value on the Update query.
3. Copy update query to the new window and send out to Bud and Alexandra to be sent to DEVOPS.

```sql
--Sample_Info_Date.sql
USE SAM_AUDIT
GO
SELECT * FROM smart.sam_system_CodeList WHERE LookUpCategory = 'SampleType'
UPDATE smart.sam_Master_Sample SET SampleTypeNo = 0 WHERE SampleNo = 2988

SELECT * FROM smart.sam_system_CodeList WHERE LookUpCategory = 'FeedSource'
UPDATE smart.sam_Master_Sample SET FeedSourceNo = 0 WHERE SampleNo = 2988

SELECT * FROM smart.sam_system_CodeList WHERE LookUpCategory = 'Frequency'
-- Use Option1
UPDATE smart.sam_Master_Sample SET Frequency = '' WHERE SampleNo = 2988

SELECT * FROM smart.sam_system_CodeList WHERE LookUpCategory = 'SamplingMethod'
UPDATE smart.sam_Master_Sample SET SamplingMethodNo = 0 WHERE SampleNo = 2988

SELECT * FROM smart.sam_system_CodeList WHERE LookUpCategory = 'ProjectionMethod'
UPDATE smart.sam_Master_Sample SET ProjectionMethodNo = 0 WHERE SampleNo = 2988

SELECT * FROM smart.sam_Master_Routing
UPDATE smart.sam_Master_Sample SET RoutingTemplateNo = 0 WHERE SampleNo = 2988

SELECT * FROM smart.sam_system_CodeList WHERE LookUpCategory = 'TeamType' AND LookUpCode IN (1,3,4,5)
UPDATE smart.sam_Master_Sample SET ERTeamTypeNo = 0 WHERE SampleNo = 2988

SELECT * FROM smart.sam_system_CodeList WHERE LookUpCategory = 'VRTeamType'
UPDATE smart.sam_Master_Sample SET VRTeamTypeNo = 0 WHERE SampleNo = 2988

SELECT * FROM smart.sam_system_CodeList WHERE LookUpCategory = 'SampleGroup'
UPDATE smart.sam_Master_Sample SET SampleGroupNo = 0 WHERE SampleNo = 2988

SELECT * FROM smart.sam_system_CodeList WHERE LookUpCategory = 'SampleLevel'
UPDATE smart.sam_Master_Sample SET SampleLevelNo = 0 WHERE SampleNo = 2988

4. OJT Update

Current OJT process does not generate data from the fields needed by performance profile. Therefore, the script needs to run every Tuesday and Thursday, or whenever there is a script that needs to run.

Steps

Send OJT_Update.sql OJT_Update.sql to Bud and Alexandra to be sent to DEVOPS.

USE SAM_AUDIT
GO
UPDATE smart.sam_audit_open SET AuditEnded = DateLastUpdated where sampletypeNo = 9 and auditstageNo = 9

UPDATE AO
    SET AuditStarted = AC.DateLastUpdated FROM smart.sam_audit_open AO
    LEFT JOIN (SELECT UniqueClaimNo, MIN(DateLastUpdated) DateLastUpdated FROM smart.sam_audit_comments WHERE AuditStageNo = 2  GROUP BY UniqueClaimNo) AC
        ON AO.UniqueClaimNo = AC.UniqueClaimNo
      where sampletypeNo = 9 and auditstageNo = 9

update a
SET CPOwned = (CASE WHEN a.ErrorLevelNo = 1 then 1 ELSE 0 END)
From smart.sam_Audit_error a
    inner join smart.sam_audit_open b on a.UniqueClaimNo = b.UniqueClaimNo
 where b.SampleTypeNO = 9 and b.AuditStageNo = 9