Populate decision text and conflicts from oracle for all pre-Namex NRs

[katiemcgoff]

Need to pull decision text and conflicts from oracle for pre-Namex NRs.

Does not need to be part of the extractor, since as of now (post-launch) the only NRs it will pull will be new ones.

Decision text: name_state.state_comment
Conflicts: name_rule (conf_number, conf_name) where rejection_reason_code='CONFLICT'

Should be run once. Probably a script that can run behind the scenes. Could add to extractor and run the extractor over all NRs (PUT for update) but that involves updating the NAMES_VW view and extractor code.

Per @LJTrent : **ENSURE THE UPDATER IS TURNED OFF WHEN THE DATA FIX IS EXECUTED ****

[katiemcgoff]

Solution - model this on the updater, not extractor, because it has connections set up to oracle (direct, not wrapper) and originates on namex side rather than triggering off an oracle table (extractor). Can also use the flask model functionality nicely from updater-style script.

Create table in postgres with list of records to process (one of the three completed states, last updated by NRO_SERVICE_ACCOUNT). Call this script over that list.

Create view in Oracle with the data I need, then drop when I'm done.

[katiemcgoff]

View to create in Oracle:

CREATE VIEW NAMES_WITH_DECISIONS_VW (request_id,
                                            nr_num,
                                             choice_number,
                                             NAME,
                                              state_comment,
                                              conflict_1_number, conflict_1_name,
                                              conflict_2_number, conflict_2_name,
                                              conflict_3_number, conflict_3_name
                                            )
AS

    with conflicts as (select row_number() over (partition by name_id order by NAME_RULE_ID) 
      as therownum, name_id, conf_number, conf_name from name_rule 
      where REJECT_REASON_CD='CONFLICT')
    SELECT nm.request_id, r.nr_num, ni.choice_number, ni.NAME,
      ns.STATE_COMMENT,
           nr_1.CONF_NUMBER as conflict_1_number, nr_1.CONF_NAME as conflict_1_name,
           nr_2.CONF_NUMBER as conflict_2_number, nr_2.CONF_NAME as conflict_2_name,
           nr_3.CONF_NUMBER as conflict_3_number, nr_3.CONF_NAME as conflict_3_name
      FROM name_instance ni
             LEFT OUTER JOIN NAME nm ON nm.name_id = ni.name_id
           LEFT OUTER JOIN name_state ns ON ns.name_id = ni.name_id
          left outer join conflicts nr_1 on nr_1.therownum=1 and nr_1.name_id=nm.NAME_ID
          left outer join conflicts nr_2 on nr_2.therownum=2 and nr_2.name_id=nm.NAME_ID
          left outer join conflicts nr_3 on nr_3.therownum=3 and nr_3.name_id=nm.NAME_ID
          join request r on r.REQUEST_ID = nm.REQUEST_ID
     WHERE ns.end_event_id IS NULL AND ni.end_event_id IS NULL

;

Above with no line breaks or extra spaces:

CREATE VIEW NAMES_WITH_DECISIONS_VW (request_id,  nr_num,  choice_number,  NAME,  state_comment,  conflict_1_number, conflict_1_name,  conflict_2_number, conflict_2_name,  conflict_3_number, conflict_3_name  ) AS   with conflicts as (select row_number() over (partition by name_id order by NAME_RULE_ID)   as therownum, name_id, conf_number, conf_name from name_rule   where REJECT_REASON_CD='CONFLICT')  SELECT nm.request_id, r.nr_num, ni.choice_number, ni.NAME,  ns.STATE_COMMENT,  nr_1.CONF_NUMBER as conflict_1_number, nr_1.CONF_NAME as conflict_1_name,  nr_2.CONF_NUMBER as conflict_2_number, nr_2.CONF_NAME as conflict_2_name,  nr_3.CONF_NUMBER as conflict_3_number, nr_3.CONF_NAME as conflict_3_name  FROM name_instance ni  LEFT OUTER JOIN NAME nm ON nm.name_id = ni.name_id  LEFT OUTER JOIN name_state ns ON ns.name_id = ni.name_id  left outer join conflicts nr_1 on nr_1.therownum=1 and nr_1.name_id=nm.NAME_ID  left outer join conflicts nr_2 on nr_2.therownum=2 and nr_2.name_id=nm.NAME_ID  left outer join conflicts nr_3 on nr_3.therownum=3 and nr_3.name_id=nm.NAME_ID  join request r on r.REQUEST_ID = nm.REQUEST_ID  WHERE ns.end_event_id IS NULL AND ni.end_event_id IS NULL  ;  

[katiemcgoff]

Check whether we also need to bring in examiner comment, or whether we already have that info from extractor.
IT WAS ALREADY BROUGHT OVER BY EXTRACTOR - NO ACTION NEEDED.

[katiemcgoff]

Table to create in Postgres, to track records to process (and processing status):

CREATE TABLE get_decision_data_table_tracker
(
    nr_num character varying(10) NOT NULL,
    success boolean,
    message text,
    CONSTRAINT get_decision_data_table_pkey PRIMARY KEY (nr_num)
);

grant select on get_decision_data_table_tracker to public;
grant update on get_decision_data_table_tracker to public;

SQL to populate tracking table:

insert into get_decision_data_table_tracker (nr_num, success, message) 
   select nr_num, null, null from requests join users on requests.user_id=users.id 
   where upper(users.username)='NRO_SERVICE_ACCOUNT' 
      and state_cd in ('APPROVED', 'REJECTED', 'CONDITIONAL')

[katiemcgoff]

Deployment:

• create view (oracle), table (postgres), and populate table in DEV/TEST/PROD postgres
  • Oracle done in NAMESD, NAMEST, NAMESP
  • Postgres done in DEV, TEST, PPROD
• run cron job TBD

Tear down:

• drop table, view
• delete code from git or mark as "do not use"

[katiemcgoff]

This script will change data in the names table. Not the requests table. So it will not change the lastUpdate value of the Request record, so it will NOT be picked up by the NRO-Updater, ie: it will not push any data back to Oracle for the changed NRs.

[katiemcgoff]

Replaced by bcgov/entity#19 and completed early December.