Back to Home

Data Update

1. Audit Record

Audit Stage Update for 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.

Steps:
  1. Using the below script, run select query to filter out right claim.
  2. Copy Update Script to the SAFE Script template, save the file as CEM_SAFE_SCRIPT.sql, send to Bud and Alexandra to request SAFE to run overnight.
--ciwsqlxp0099

/****
AuditStage is new, but it has auditor assigned and there is no error -> should be closed
****/
USE CORE_CEM
GO

SELECT * FROM smart.Audit_ErrorCharged WHERE UniqueCallno IN (
select UniqueCallno from  smart.audit_callrecord WHERE StageNo = 0 AND AuditorNo IS NOT NULL and deleted = 0
and previousstageNo = 9
) order by uniquecallNo

select * From smart.Audit_Rebuttal where uniqueerrorNo IN (
SELECT uniqueerrorNo FROM smart.Audit_ErrorCharged WHERE UniqueCallno IN (
select UniqueCallno from  smart.audit_callrecord WHERE StageNo = 0 AND AuditorNo IS NOT NULL and deleted = 0
and previousstageNo = 9
) )

update  smart.audit_callrecord set stageNo = 20  WHERE StageNo = 0 AND AuditorNo IS NOT NULL and deleted = 0
and uniquecallNo NOT IN (SELECT UniqueCallNo FROM smart.Audit_ErrorCharged )

/****
AuditStage is new, but it has auditor assigned and there IS error, depending on the previous stage and the routing template it's using, it should flow to the next stage.
****/

 select * from smart.audit_callrecord WHERE StageNo = 0 AND AuditorNo IS NOT NULL and deleted = 0  and uniquecallNo IN (SELECT UniqueCallNo FROM smart.Audit_ErrorCharged )
 select * from smart.audit_callrecord WHERE PreviousStageNo = 1 and StageNo = 0 AND AuditorNo IS NOT NULL and deleted = 0

 UPDATE a
    SET StageNo = c.NextAuditStageNo
    from smart.audit_callrecord a
        left join smart.pbc_SampleTable b ON a.sampleNo = b.sampleNo
left join smart.sam_Routing_RuleLine c ON b.RoutingTemplateNo = c.RoutingTemplateNo AND CurrentAuditStageNo = a.PreviousStageNo
  WHERE StageNo = 0 AND AuditorNo IS NOT NULL and a.deleted = 0  and uniquecallNo IN (SELECT UniqueCallNo FROM smart.Audit_ErrorCharged )

2. Lookup Table Update

If there are less than 10 lines of update, use the Lookup Table Menu to update the values.

Steps:
  1. Go to Admin -> Lookup Table
  2. Select the category
  3. Click Code # and update the field as requested.

3. Sample Info

Only Sample Group is not updatable via Web Page. It needs to be updated via SAFE Script.

Steps:
  1. Run the Select Query on the information that needs an update.
SELECT * FROM smart.pbc_SampleTable WHERE SampleNo = 1111 OR SampleTitle like '%abc%'
  1. Get the value by running the Select Query below and change the value on the Update query.
SELECT * FROM smart.sam_System_CodeList WHERE LookUpCategory = 'SampleGroup'
  1. Copy Update Query to the SAFE Script template, save the file as CEM_SAFE_SCRIPT.sql, send to Bud and Alexandra to request SAFE to run overnight.
update smart.pbc_SampleTable set SampleGroupNo = ? WHERE SampleNo = 1111 OR SampleTitle like '%abc%'

4. Clean Up

The bank table and audit table needs to be purged every month and reindexed for better performance.

Steps:
  1. Copy the below Query to the SAFE Script template, save the file as CEM_SAFE_SCRIPT.sql, send to Bud and Alexandra to request SAFE to run overnight.
DELETE FROM smart.p13_BankTable WHERE ReportMonth < '1/1/2020'
GO
ALTER INDEX ALL ON smart.p13_BankTable REBUILD
GO
DELETE FROM smart.audit_CallRecord WHERE ReportMonth < '12/1/2019' and StageNo = 50
GO
ALTER INDEX ALL ON smart.audit_CallRecord REBUILD
GO
ALTER INDEX ALL ON smart.Audit_CallRecord_PickUp REBUILD