Queue | Caseflow is creating duplicate Decass (DAS) records #7461

evankroske · 2018-10-18T20:50:24Z

For example, the case 3289614 has two Decass records that were created 10/16 and 10/17. This isn't supposed to be possible.

Resolves #7461 ### Description Throws an error if Caseflow tries to create a Decass record for a case that already has one. ### Testing Plan - [x] Circleci

evankroske · 2018-10-23T19:03:02Z

Problem

When a judge assigns a case to another judge, Caseflow creates a Decass record for the case. When the second judge assigns the case to an attorney, Caseflow creates another Decass record. The duplicate Decass records may be skewing productivity reports for judges.

Implemented Short-Term Fix

I modified the code that Caseflow uses to assign cases from judges to attorneys to throw an exception if the case already has a Decass record. This prevents the creation of any duplicate Decass records. However, some cases, including OMO cases, should have more than one Decass record, so this code will have to be changed once we add support for OMO cases. In addition, this fix allows Caseflow to create Decass records when cases are assigned by chief judges to judges. These Decass records shouldn't be created because DAS doesn't create them in this case.
#7461

Research Notes

I used the following query to confirm my hypothesis that the duplicate Decass records were created when judges assigned cases to other judges:

select dups.defolder, locdout, LOCSTOUT, s1.svlj, LOCSTTO, s2.svlj
from (select defolder
from decass
where deadtim >= to_date('2018-10-10', 'YYYY-MM-DD') 
group by defolder
having count(*) > 1) dups join
	priorloc on dups.defolder = lockey join
	staff s1 on locstout = s1.stafkey join
	staff s2 on locstto = s2.stafkey
where rownum <= 1000
order by dups.defolder, locdout;

evankroske · 2018-10-23T19:43:37Z

Proposed Fix: Allow Assignee to Specify Assignment Type

As I understand it, DAS requires the user to choose which type of assignment to use. I think that Caseflow should adopt the same approach. Judges would be able to choose whether to create a Decass record for an assignment. We should use the same language as DAS to make the distinction between the two types of assignment clear.

hschallhorn · 2019-10-15T15:17:56Z

Reopening as this was reverted in #7547

hschallhorn · 2019-10-15T15:29:16Z

Relavent Slack thread

Grabbing All Dupes

dupe_ids = ["3221746", "3757939", "3627981", "3973613", "3601179", "3534013", "3082458", "3039471", "3978129", "3061082", "3628051", "3013342", "3656042", "3225510", "3908617", "2921728", "3964786", "2819836", "3891946", "3384780", "3960404", "3951296", "3870519", "2960381", "3947403", "3664082", "3066029", "3752315", "3916347", "3478788", "2814196", "3229623", "2847200", "3842445", "3275347", "3736409", "3606987", "3975666", "3252287", "3410341", "2574460", "2678390", "3956895", "3040559", "3306527", "3942537", "2949844", "3768977", "2948740", "2628638", "3375094", "3210492", "2914520", "3951225", "3121328", "2976186", "3225151", "3604244", "3278559", "3500201", "3620383", "3608484", "2483451", "2465293", "3681579", "3696477", "3437285", "2790724", "3926131", "3642711", "2510627", "2889274", "2813030", "2990898", "3945774", "3406999", "3972882", "3579205", "3450738L", "3000325", "3226784", "3220968", "3512601", "3772735", "3640421", "3100727D2", "3842355", "2915988", "3050972", "3071765", "3747116", "3757718", "2866769D2", "2974462", "2663431", "3924805", "2787362", "3387299", "3811996", "3814381", "2824858", "2969854", "3687653", "3149551", "3157911", "3121162", "2896074", "3332649", "3402792", "3951581", "3888999", "3600313", "3946581", "3814848", "3939784", "3956795", "3059800", "3420979", "3953492", "3165567", "3185209", "3826720", "3151254", "2962491", "3384977", "3721645", "3242809", "3791390", "3150006", "3878456", "2866788", "3975885", "2805986", "2851916", "3613475", "3976193", "3687055", "2947759", "2997009", "3781002", "3490222", "3664663", "3617458", "2847932", "3750677", "3146529", "3743254", "3048337", "3934595", "3926789", "2899704", "3232974", "3956444", "3898068", "2717590", "3607634", "3957085", "3957106", "2744183", "2724357", "2876734", "3589999", "2930883", "3448421", "3664334", "3944644", "3852940", "3147364", "3952476", "3872087", "3537589", "3117609", "3290892", "2989698D2", "3861959", "3251094", "3133252", "3396467", "2442219", "3658913", "2852181D2", "3058750", "3910072", "3658732", "3097688", "3234141", "2913362", "2871811", "3251312", "3447164", "3374582", "3881516", "3180706", "3780536", "2956693", "3429706", "3213061", "3895661", "2971206", "3054242", "3194554", "3979263", "3501378", "3820153", "2779924", "3500454", "3968465", "3468557", "3960986", "3798573", "3007697", "2749369", "2939471", "3983862", "3923877", "3984006", "3285613", "3825367", "3747464", "3736960", "3864446", "3035890", "3696452", "3860239", "2923051", "3013522", "3884483", "3113800", "3971416", "2864514", "2624864", "3219057", "3879017", "3919826", "3779946", "3921532", "3341978", "2725417", "3392236", "2901472", "3363722", "3718814", "2932149", "3099929", "2532488", "3590828", "3372578", "3817568", "3470243", "2979596", "3203710", "3873171", "2658221", "3855566", "2791520", "3468198", "3888699", "3548303", "3667906", "3850518", "3233078", "3268050", "3833618", "3608810", "3959668", "3661222", "2794570", "2795659", "3151594", "3781349", "3944497", "3900274", "3751871", "3694062", "3726361", "3933506", "3387470", "3021357", "3658835", "3127020", "3971382", "3903920", "3977968", "3947184", "2991150", "3215947", "3498918", "3194016", "2894392", "2866122", "2864202", "3526082", "2617902", "3186829", "3965166", "2993073", "3252836", "3268025", "3860025", "3391299", "3747898", "3857891", "3974526", "3830396", "3952301", "3818166", "3827065", "3894252", "3926066", "3578577", "3628524", "2976513", "3932379", "3369687", "3956825", "3483192", "3569882", "3366342", "2365584", "3326706", "2873843", "3921147", "3527445", "2717672", "3812972", "3588408", "3475254", "3556403", "3295083", "3905671", "2845118", "3438820", "3443235", "3799220", "3357514", "3940163", "2750168", "3980710", "3305090", "3986746", "2801499", "3973184", "2949871", "3888585", "3497035", "3553080", "3805055", "3745964", "3653689", "2029080L", "3902010", "3971172", "3017484", "3235248", "3350768", "3980930", "3944662", "3862367", "2607006", "3398817", "3413886", "3464817", "3688380", "3919992", "3960555", "3098230", "3958779", "2885062", "3881701", "3096958", "3957283", "3040496", "3871351", "2190046", "3610934", "2501516", "3890205", "3853592", "3047878", "2754537", "3949280", "3312767", "3877300", "3781367", "3359134", "3624163", "3093328", "3870362", "3116040", "3338069", "3241606", "3987166", "2882110", "3848403", "3159094", "3265837", "3965472", "3944260", "3960212", "2695000", "2908630", "3393697", "3079305", "3179015", "3955298", "3972932", "3044963", "3954428", "3927623", "3586536", "3137335", "3786951", "3919963", "3071555", "3545583", "3005913", "3906505", "3358649", "3858772", "3976792", "3595096", "3923828", "3240992", "3950735", "3922057", "3984559", "2710330", "3855876", "3945709", "2292798", "3510770", "3669460", "3187864", "3967078", "3916918", "3054979", "3167304", "3883062", "2619266", "2923807", "3672279", "3888686", "3361540", "3533427", "2681240", "3021653", "3714813", "3483046", "3917706", "3889552", "3529541", "3887833", "2931694", "3955595", "3280467", "3173119", "3895409", "3848701", "3939332", "3741089", "2728906", "3961346", "3882973", "3950321", "3845418", "3756252", "3921303", "3805322", "3121963", "3923042", "3552655", "3083045", "3361614", "2830195", "3480225", "3947935D2", "2563815", "3970761", "2813792", "3528520", "3049550", "3974875", "3952261", "3296433", "3000089", "2331501", "3439277", "3783068", "3385820", "2569137", "2652584", "3086776", "2704062", "3786798", "3956356", "3985208", "3886512", "3257345", "3233757", "3945907", "3942349", "2928468", "3476648", "3085907", "3180157", "3240907", "3842031", "3222193", "3391316", "3024347", "3926878", "3861206", "3624870", "3804998", "3269435"]
dupe_records = VACOLS::Decass.where(defolder: dupe_ids).order(:defolder)

VACOLS DECASS Table

TABLE NAME	FIELD DESCRIPTION	FIELD NAME	TYPE	RELATED TABLES/REMARKS
DECASS
	Folder number (Primary key)	DEFOLDER	VARCHAR2(12)	Breiff(bfkey), Folder(ticknum)
	Attorney number	DEATTY	VARCHAR2(16)
	Decision Team	DETEAM	VARCHAR2(3)
	Peliminary difficulty	DEPDIFF	VARCHAR2(1)
	Final Difficulty	DEFDIFF	VARCHAR2(1)
	Date Assigned	DEASSIGN	DATE
	Date received by VLJ	DERECEIVE	DATE
	Hours spent on case	DEHOURS	NUMBER(5,2)
	Work Product	DEPROD	VARCHAR2(3)
	Timeliness indicator	DETREM	VARCHAR2(1)	Y or N
	Additional Remarks (obsolete)	DEAREM	VARCHAR2(1)	Y or N
	Overall Quality	DEOQ	VARCHAR2(1)
	Added by	DEADUSR	VARCHAR2(12)
	Added on date/time	DEADTIM	DATE
	Progress review date	DEPROGREV	DATE
	Attorney comments	DEATCOM	VARCHAR2(350)
	VLJ comments	DEBMCOM	VARCHAR2(600)
	Last modified by	DEMDUSR	VARCHAR2(12)
	Last modified on	DEMDTIM	DATE
	DAS locked	DELOCK	VARCHAR2(1)
	VLJ number	DEMEMID	VARCHAR2(16)
	DAS completion date	DECOMP	DATE
	Deadline date	DEDEADLINE	DATE
	Intial Complexity rating	DEICR	NUMBER(5,2)
	Final Complexity rating	DEFCR	NUMBER(5,2)
	Quality Review item 1	DEQR1	VARCHAR2(1)
	Quality Review item 2	DEQR2	VARCHAR2(1)
	Quality Review item 3	DEQR3	VARCHAR2(1)
	Quality Review item 4	DEQR4	VARCHAR2(1)
	Quality Review item 5	DEQR5	VARCHAR2(1)
	Quality Review item 6	DEQR6	VARCHAR2(1)
	Quality Review item 7	DEQR7	VARCHAR2(1)
	Quality Review item 8	DEQR8	VARCHAR2(1)
	Quality Review item 9	DEQR9	VARCHAR2(1)
	Quality Review item 10	DEQR10	VARCHAR2(1)
	Quality Review item 11	DEQR11	VARCHAR2(1)
	Decision document id	DEDOCID	VARCHAR2(30)
	Recommened decision	DERECOMMEND	VARCHAR2(1)
	One touch initiative	DE1TOUCH	VARCHAR2(1)

tomas-nava · 2019-10-16T18:07:36Z

query to find duplicate DAS records:

SQL

SELECT
  defolder,
  COUNT(defolder)
FROM decass
WHERE deassign >= '2018-07-01'
AND (deprod IS NULL OR deprod NOT IN ('REA', 'REU', 'VHA', 'IME', 'AFI', 'OTV', 'OTI'))
GROUP BY defolder
HAVING COUNT(defolder) > 1;

ActiveRecord

VACOLS::Decass.select("defolder").where("deassign >= ?", Date.new(2018, 7, 1)).where("deprod IS NULL OR deprod NOT IN ('REA', 'REU', 'VHA', 'IME', 'AFI', 'OTV', 'OTI')").group("defolder").having("count(*) > 1")

Work product abbreviations

Decass records with the deprod (work product) abbreviations below are excluded in the query because when any of those work products are used, we expect a second work product to follow.

abbreviation	meaning
AFI	Another type of opinion
IME	Independent Medical Exam
OTI	Overtime IME
OTV	Overtime VHA opinion
REA	Reassigned
REU	Reassigned unsat
VHA	VHA opinion

evankroske self-assigned this Oct 18, 2018

lpciferri added Product: caseflow-queue In-Progress labels Oct 19, 2018

evankroske mentioned this issue Oct 23, 2018

Prevents the creation of duplicate Decass records #7502

Merged

1 task

va-bot closed this as completed in #7502 Oct 23, 2018

ghost removed the In-Progress label Oct 23, 2018

va-bot pushed a commit that referenced this issue Oct 23, 2018

Prevents the creation of duplicate Decass records (#7502)

8dacbd0

Resolves #7461 ### Description Throws an error if Caseflow tries to create a Decass record for a case that already has one. ### Testing Plan - [x] Circleci

lpciferri added the Team: Foxtrot 🦊 label Oct 23, 2018

hschallhorn reopened this Oct 15, 2019

msj0nes added Team: Echo 🐬 and removed Team: Foxtrot 🦊 labels Nov 26, 2019

alisan16 unassigned evankroske Apr 1, 2020

ThorntonMatthew closed this as completed Apr 25, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Queue | Caseflow is creating duplicate Decass (DAS) records #7461

Queue | Caseflow is creating duplicate Decass (DAS) records #7461

evankroske commented Oct 18, 2018 •

edited

evankroske commented Oct 23, 2018

evankroske commented Oct 23, 2018

hschallhorn commented Oct 15, 2019 •

edited

hschallhorn commented Oct 15, 2019

tomas-nava commented Oct 16, 2019

Queue | Caseflow is creating duplicate Decass (DAS) records #7461

Queue | Caseflow is creating duplicate Decass (DAS) records #7461

Comments

evankroske commented Oct 18, 2018 • edited

evankroske commented Oct 23, 2018

Problem

Implemented Short-Term Fix

Research Notes

evankroske commented Oct 23, 2018

Proposed Fix: Allow Assignee to Specify Assignment Type

hschallhorn commented Oct 15, 2019 • edited

hschallhorn commented Oct 15, 2019

Grabbing All Dupes

VACOLS DECASS Table

tomas-nava commented Oct 16, 2019

query to find duplicate DAS records:

Work product abbreviations

evankroske commented Oct 18, 2018 •

edited

hschallhorn commented Oct 15, 2019 •

edited