Error on AGLR Posting

Overview

When Advancement enters a gift(s), it creates a gift session in Colleague (CA_SESSION). Once it is reconciled, the business office will post the session by running AGLR and AGLP.  When Advancement needs to adjust a gift, Colleague creates a new session.  This works fine when the original gift's session has been posted; however, when it has not been posted (typically happens when adjusting the gift on the same day it was created in Colleague) and the adjustment is made, the Business Office will receive an error on AGLR.  This cannot be cleared up until the second gift session is removed from Colleague. 

Symptoms

When this issue occurs either the Business Office or Advancement will contact IT.  The Business Office will receive an error when they try to post (run AGLR in Colleague).

Root Cause

This issue is caused by an adjustment to a gift before it has been posted in Colleague.

Resolution

To resolve the issue, follow these steps...

Find the SQL script in Useful Scripts\Datatel Data Modification\Remove a CA Session.sql.  A copy of the script with the steps are below. This script needs to be run on Coll18_live.

Follow all the steps to find the IDs  Run all the selects and verify that all queries look fine  THEN, run the delete statements from THE BOTTOM to the TOP.  Technically, if you have all your IDs, it doesn't matter the order.  However, it is best to delete the children records before the parent records.

1. Find the session ID based on the Contribution number provided by Advancement. Usually they will also know the session ID number that you can use to verify that your sql returned the correct session.  Replace the number below with the Contribution number
   SELECT CONTRIB_SESSION, * 
   FROM CONTRIBUTION
   WHERE CONTRIBUTION_ID = '303646'
2. Replace the number below with the CONTRIB_SESSION number found in the previous query.  Typically, the amount field on this session will be blank
   SELECT *
   --DELETE
   FROM CA_SESSION
   WHERE CA_SESSION_ID in ( '5901')
3. Replace the number below with the same session id number as above (CONTRIB_SESSION).
   SELECT * 
   --DELETE
   FROM CA_SESSION_LS
   WHERE CA_SESSION_ID in ( '5901')
4. Find all the Contribution Ids that are in the session to be deleted.  Compare the list of these CONTRIBUTION_IDs from the above query to the values found in CAS_CONTRIBUTIONS from the CA_SESSION query.  It should be the same list of IDs.
   SELECT * 
   --DELETE 
   FROM CONTRIBUTION
   WHERE CONTRIB_SESSION = '5901'
5. Replace the numbers below with the CONTRIBUTION_ID's from above.  This query will also give you the IDs used in the next 4 queries.
   SELECT * 
   --DELETE
   FROM CONTRIBUTION_LS
   WHERE CONTRIBUTION_ID in ('303646','303647')
6. Replace the numbers below with all values from the field CONTRIB_DONOR_DESIGS from the CONTRIBUTION_LS query.
   SELECT * 
   --DELETE
   FROM CONTRIB_DONOR_DESIG
   WHERE CONTRIB_DONOR_DESIG_ID in ('381559','381560','381561','381562','381563','381564','381565','381567')
7. Replace the numbers below with all values from the field CONTRIB_DONORS from the CONTRIBUTION_LS query.
   SELECT * 
   --DELETE
   FROM CONTRIB_DONOR
   WHERE CONTRIB_DONOR_ID in ('387526','387527','387528','387529')
8. Replace the numbers below with the same CONTRIB_DONOR_IDs from query above.
   SELECT * 
   --DELETE
   FROM CONTRIB_DONOR_LS
   WHERE CONTRIB_DONOR_ID in ('387526','387527','387528','387529')
9. Replace the numbers below with all values from the field CAS_PAYMENTS from the CA_SESSION_LS query.  Should be the same IDs as found from field CONTRIB_PAYMENTS from the CONTRIBUTION_LS  query.
   SELECT * 
   --DELETE
   FROM CA_PAYMENT
   WHERE CA_PAYMENT_ID in ('285244','285245')
10. Find the original contribution ID and update the CONTRIB_ADJ_BY_CONTRIBUTION field.  This is what tells Colleague that the gift was adjusted.  Since we have removed the adjusted gift, we do want this field to reference the deleted gift. This number can be found on the CONTRIBUTION.CONTRIB_ADJ_TO_CONTRIBUTION field on the record from above.
    UPDATE CONTRIBUTION SET CONTRIB_ADJ_BY_CONTRIBUTION = NULL 
    WHERE CONTRIBUTION_ID = 'original contribution id'
11. Once all the IDs have been replaced with the correct IDs from the query, run the select and then the delete on each table in the following order:

• CA_PAYMENT
• CONTRIB_DONOR_LS
• CONTRIB_DONOR
• CONTRIB_DONOR_DESIG
• CONTRIBUTION_LS
• CONTRIBUTION
• CA_SESSION_LS
• CA_SESSION