Refine Contract and Grant import rules for status changes and DSA behavior

[jvitorbarros15]

We need to adjust how the Contract and Grant import handles status updates and how it fills the DSA field during Amendments.

Requested fixes:

• Skip records already marked as Not Funded, Purged, or Withdrawn during import.
• When a Pending record later appears as an Awarded Amendment, update, replace or delete the Pending record.
• Stop inserting a blank DSA value when adding an Amendment.

[jvitorbarros15]

For the last two points, here’s some additional context and clarification:

• For the Pending → Awarded/Amendment flow, Pending records are initially created using the base agreement number. When that same record later appears as Awarded (Amendment), it should be matched using that agreement number. Once funds are released, the system should update or replace the existing Pending record rather than creating a duplicate or leaving stale data.

• For the DSA issue, when creating an Amendment, the DSA field is currently being inserted as blank. This seems to be happening because the key is being written to Amendment2_ID instead of Amendment_1_OSPKey. As a result, the correct value is not being populated in the DSA field.

[nmg110]

To find the DSA issue, I pulled the Activity Insight Backup, which would have all data in the Activity Insight as of Saturday/Sunday. I extracted the CONGRANT_AMENDMENT.csv and the CONGRANT file.
First, look at the CONGRANT_AMENDMENT file for records that have a duplicate PARENT_ID, by doing this you will then see all of the records with Amendments. Now, look at the column heading listed ORDINAL, that will have numbers 1-71 depending on how many amendments a record may have is where this number comes from. All records will always have a 1. What you want to do is look for records that have a 1 and 2 or 3 with a blank 1 row and rows 2 or 3 will have the OSPKEY number and other information. Now copy the number from the PARENT_ID column and look for it in the CONGRANT file in the column labeled ID. By doing this you will be able to find the USERNAME. I then took that USERNAME and looked them up in Activity Insight to verify the record has a blank DSA row. If you go to their Yearly Data screen look up their College code. Then go to the Usage Statistics screen in Activity Insight, go to that college, find your user, and then download their report (INDIVIDUAL-ACTIVITIES-University-username). Now take your ID or PARENT_ID number and look for it in the Record ID column of that report. Once you find that, go back to your CONGRANT_AMENDMENT.csv file and copy number in the ID column of row 2 or 3 where your Amendment was written. I then use the Find in Excel to look for that number in the Data column, this will then give you the timestamp of when that row was created and how in Activity Insight. (PHEW!)

[nmg110]

For the Pending → Awarded/Amendment flow, Pending records are initially created using the base agreement number. When that same record later appears as Awarded (Amendment), it should be matched using that agreement number. Once funds are released, the system should update or replace the existing Pending record rather than creating a duplicate or leaving stale data.

CORRECTION: For Pending, records come in from OSP as Pending with NO Base Agreement Number (will be referenced as BAN). The only time they get a BAN is when they are Awarded. If a record is in Activity Insight (AI) as Awarded, we look at that record's BAN to see if it matches one that is already in AI, if a match is made then the "Amendment" is created in a DSA row. If there is no matching BAN, then the record in AI is updated to Awarded and the BAN is then entered. The Problem is we already wrote that record as Pending in Activity Insight, it is still in the AI system as Pending even if it has been added as an Amendment.

[ajkiessl]

This is the logic we want for updating 'Pending' contracts, and dealing with new 'Purged' and 'Withdrawn' contracts:

# We want to update 'Pending' contracts when their status changes to anything 
  # (including but not limited to 'Purged', 'Withdrawn', and 'Not Funded'), 
  # but we don't want to import any _new_ 'Withdrawn' or 'Purged' contracts.