INVESTIGATION - COBRS Name Request/Name records missed in namex

[eve-git]

When addressing missing historical transactions before 2018 (Issue #16316), I identified two distinct scenarios involving missing data from the 'namex' database:

1. NRs Without Corresponding Names Records: There are 12 NRs that lack corresponding names records. These can be retrieved using the following query:

   select * 
   from requests r inner join cobrs_request c on r.nr_num = c.rquestno
   left outer join names n on r.id = n.nr_id
   where n.id is null;

2. NRs Missing in 'namex.requests': Additionally, 762 NRs are present in the 'cobrs_request' table but may not exist in the 'namex.requests' table. To identify these NRs, use the following query:

   select count(*) from cobrs_request c left join requests r on c.rquestno=r.nr_num
   where r.id is null;

   Please investigate the issue. If those missing NRs are not required, please remove the following data from events table:

   
   -- missing names record
   SELECT *
   FROM events
   WHERE ((event_json->>'names') IS NULL 
   OR jsonb_typeof(event_json->'names') <> 'array') 
   AND action='patch'
   and nr_id is  null
   AND event_json->>'note' = 'inserted manually for ticket 16316';

-- missing requests records SELECT * FROM events WHERE ((event_json->>'names') IS NULL OR jsonb_typeof(event_json->'names') <> 'array') AND action='patch' and nr_id is null AND event_json->>'note' = 'inserted manually for ticket 16316'; '''

[eve-git]

The following NRs do not have corresponding names rexords:

"nr_num"    "request_type_cd"   "furnished" "submitted_date"    "state_cd"
"NR 6852537"    "SO"    "Y" "2016-10-25 20:44:19+00"    "CANCELLED"
"NR 5222840"    "SO"    "Y" "2016-10-20 23:07:00+00"    "CANCELLED"
"NR 5285570"    "SO"    "Y" "2016-10-17 20:06:05+00"    "CANCELLED"
"NR 7999101"    "SO"    "Y" "2016-10-11 18:07:23+00"    "CANCELLED"
"NR 6436341"    "SO"    "Y" "2016-11-29 17:52:35+00"    "CANCELLED"
"NR 4722714"    "SO"    "Y" "2016-10-28 22:17:14+00"    "CANCELLED"
"NR 8432655"    "SO"    "Y" "2016-11-09 19:46:10+00"    "CANCELLED"
"NR 9841510"    "RSO"   "Y" "2015-11-26 17:42:38+00"    "CANCELLED"
"NR 2262393"    "SO"    "Y" "2016-10-12 19:02:47+00"    "CANCELLED"
"NR 3589432"    "SO"    "Y" "2016-11-21 21:17:26+00"    "CANCELLED"
"NR 1631134"    "CR"    "Y" "2014-02-14 21:10:17+00"    "COMPLETED"
"NR 0777924"    "SO"    "Y" "2016-10-04 17:31:09+00"    "CANCELLED"

[eve-git]

The following NRs do not exist in Namex and only in COBRS:

-- BC NRs
"rquestno"  "reqstats"  "entrydat"  "examinda"  "rqsttype"  "extrapro"
"NR 4597145"    "A"     "2016-03-02"    "2016-03-02"    "SO"        "N"
"NR 8533801"    "A"     "2009-01-16"    "2009-01-20"    "CR"        "N"
"NR 1028611"    "A"     "2012-01-05"    "2012-01-11"    "CR"        "N"
"NR 8866707"    "A"     "2010-09-29"    "2010-09-30"    "SO"        "N"
"NR 7852778"    "A"     "2011-01-22"    "2011-01-22"    "FR"        "N"
"NR 7187419"    "A"     "2010-06-30"    "2010-06-30"    "FR"        "N"
"NR 5655525"    "A"     "2009-08-17"    "2009-10-14"    "SO"        "N"
"NR 0902009"    "A"     "2009-03-11"    "2009-03-13"    "FR"        "N"
"NR 3014688"    "A"     "2009-09-08"    "2009-09-08"    "FR"        "N"
"NR 4750133"    "R"     "2009-04-29"    "2009-05-04"    "FR"        "N"

-- Ex-provincial NRs
"rquestno"  "reqstats"  "entrydat"  "examinda"  "rqsttype"  "extrapro"  "jurisdic"
"NR 7767589"    "A"     "2013-10-11"    "2013-10-15"    "CR"        "Y"     "FEDERAL"
"NR 1312803"    "A"     "2012-01-06"    "2012-01-11"    "CR"        "Y"     "FEDERAL"
"NR 0508471"    "A"     "2012-04-27"    "2012-04-27"    "CR"        "Y"     "FEDERAL"
"NR 0718899"    "C"     "2012-08-20"    "2012-08-23"    "CR"        "Y"     "FEDERAL"
"NR 0788018"    "A"     "2012-09-25"    "2012-09-26"    "CR"        "Y"     "FEDERAL"
"NR 0825000"    "A"     "2012-09-25"    "2012-09-25"    "CR"        "Y"     "FEDERAL"
"NR 0902198"    "R"     "2012-05-06"    "2012-05-10"    "CR"        "Y"     "FEDERAL"
"NR 0990272"    "A"     "2012-11-30"    "2012-11-30"    "CR"        "Y"     "FEDERAL"
"NR 0273713"    "A"     "2012-08-13"    "2012-08-17"    "CR"        "Y"     "FEDERAL"
"NR 0431538"    "A"     "2012-05-24"    "2012-05-24"    "CR"        "Y"     "FEDERAL"