Skip to end of metadata
Go to start of metadata

When a duplicate PIDM is discovered in Banner's tables, the task of correcting the issue falls to a select group of Duplicate PIDM Processors. Depending on which functional area has the most data involved with the duplicate, that area's processors will typically take responsibility for correcting the problem. Below are the Banner functional areas along with their Duplicate PIDM processors.

Functional AreaBanner Schema(s)

Contact Person(s)

AdvancementALUMNIKaren Neal, Tony Richerson
Financial AidFAISMGRChelsey Giles
Financial ServicesFIMSMGR, FIMSARC, TAISMGRClement Balasundaram, Mandy Zhou
HR/PayrollPAYROLL, POSNCTLLyn McKenzie, Michele Bogart
StudentSATURNBen Metzger, Amy Keene, Julie Spinabella, Jennifer Quinn
Other/Computer ServicesBEARDATA, BANSECR, BANINST1, GENERAL, OTGMGR, WTAILORSreekanth Acharya

The GWAPP software is very effective in correcting duplicate PIDM problems. However, it’s also a powerful tool that has the potential of negatively affecting a student, employee, alumnus, or vendor. Because of this, it is only accessible to this select number of users. The tool accomplishes its task through three overall processes that it manages:

Step-by-step guide

  1. Log into Banner INB.
  2. A separate INB user ID (with the suffix 2; i.e., BJONES2) and password is required.
    1. Enter PROD for database.
  3. Two INB forms are primarily used:
    1. GZAPENT (PSN creation)
    2. GZAPIDM (PSN processing)

Datapull

This first phase of the process involves entering the multiple IDs/PIDMs into GWAPP and performing a "Datapull" that searches Banner’s tables for both good and bad PIDMs.

  1. Determine the good PIDM vs. bad PIDM(s).
    1. The general rule of thumb is the smaller PIDM number is usually elected the “good” one.
    2. The good M-Number/PIDM may be indicated by requester.
    3. However, if one PIDM has any data in the PAYROLL schema that PIDM is automatically designated as the “good” number.
    4. Who/What may provide useful info.
  2. Click the New PSN tab in GZAPENT form (this earmarks the next PSN, “PIDM Set Number”).


    1. In the Comments: field enter anything descriptive (on the second line) to later help identify this specific PSN (such as “Advancement”, “Created on behalf of…”, etc).
    2. Enter good M-Number or good PIDM on first line in the ID column; optionally check mark the Good Ind.
    3. Enter bad M-Number(s) or bad PIDM(s) on following line(s)
    4. When entering each M-Number the New PSN tab will display system information for each functional area at the bottom of the form.
    5. Review the results for each M-Number. The M-Number having the most boxes checked will generally be elected the “good."

      Caution and extra analysis will be required later when one of the following systems appear for the bad PIDM:

      • HR/Payroll (table owner is PAYROLL).  Must contact Lyn McKenzie in HR before merging.
      • Accounts Receivable (table owner is TAISMGR and table prefix is ‘TB’). Must contact Mandy Zhou in Financial Services for assistance in coding these tables.
      • Greenwood Parents (look for charge/payment details codes that begin with ‘J’ in TBRACCD. Also may be indicated by ‘GLSP’ Donr_Code in APRCATG table.) Contact Kim Benton in Bursar before merging.

      • CASHNET (table Z_CNIPP or CASHNET referenced in SPRHOLD). Contact Kim Benton in Bursar’s Office to discuss. Problems occur when balance exists for the bad PIDM.

      • Financial Aid (table owner is FAISMGR). Contact Financial Aid functional lead Chelsey Giles for assistance.
  3. Click the Save icon  to create the PSN (do not use Generate button).
    1. Later review of the results from the Datapull may provide additional details by table/record.
  4. Click the PSN Info tab in the GZAPENT form.
  5. Check mark the PSN just created (in the Select column on the right).
  6. Select "Run Datapull in Background" from the  drop down box.
    1. Status will change to "Waiting for Batch Datapull" or "Running". The status can be checked by exiting to the main menu, then returning back to GZAPENT.
  7. Wait for the Datapull column to say .
    1. Datapulls generally take several minutes to run, depending on the number of tables/rows the PIDMs are found in. Several Datapulls running simultaneously seem to also slow the completion of the process. Occasionally they can take a few hours or more. If one has been languishing in a "Running" state call Tom or a DBA to check on it.
  8. Once the Datapull completes check to see if any West Plains records were found.
    1. This will be indicated in the Comments: field of the PSN Details tab on the initial GZAPENT screen.


    If a suffix other than WP-XX shows with the bad M-Number (e.g., “M012334567-WP-XX : M76543210-WP-SR”), this means West Plains data will be negatively affected by the duplicate PIDM correction.

    • Contact the West Plains DBA (CJ Collins) if the above scenario is true.
    • It is okay to proceed with steps 2 and 3 below, but DON'T run the Script Gen process with the checked until West Plains confirms they’ve finished updating all their relevant tables.

Make Table Decisions

The second phase of the process requires the human analysis, review, and decision-making element. That is, owners of the specific tables must designate what is to occur with each bad PIDM row(s). The creator of the PSN generally records as many of the table decisions as they can, even on tables they aren’t responsible for. Then, later confirmation and/or assistance can be requested from the specific owners of those tables.

  1. Using the PSN Info tab in GZAPENT form highlight the PSN created in step 1 and click on .
    1. This will display the GZAPIDM form.


  2. Click Next Block or the  button to fill the Tables tab information.
  3. Highlight and scroll through the Table Names to display each table’s record details.
  4. The Archived Data tab, at the bottom, will display data from both the good and bad PIDMs.

Reviewing Data, Making Decisions, Entering Actions

When following the steps outlined below click the Save  icon frequently, especially when updating records using the lower level function.

  1. Before taking any actions/decisions on the tables, scroll through the entire list to see how many tables were found with the good and bad PIDMs.

    While scanning through the table list look specifically for the RORLOGM and RORLOGD tables (located in the FAISMGR owner). If either of these two tables are found to have data associated with the bad PIDM those rows will need to be manually deleted by Computer Services. Email Tom or the DBA's with the request to do so. Once those table entries have been deleted processing of the PSN can resume and the Script Gen won't fail from it finding RORLOGM rows.

  2. Click on the  (near the upper-left corner of the GZAPIDM form). 
    1. As its name implies the Auto Det feature will automatically determine as many table action/decisions as it can for the given set of tables in the PSN. Here's part of what it will do:
      1. Selects Change for any bad PIDM records that have no accompanying good PIDM records.
      2. Selects Predefined for all OTGMGR bad PIDMs records.
      3. Select Delete for all bad PIDMs records for these tables (GOBSRID, GOBTPAC, GOBUMAP, GORPRAC, GORCGEO, GORPAUD, FTVVEND, SRTIDEN).
      4. Selects Delete for the bad PIDM record for the SPBPERS table.
      5. Selects Lower level record updating for the SPRIDEN table, while preemptively correcting the SPRIDEN_CHANGE_IND column to avoid having multiple active SPRIDEN records.
  3. Use these general rules when making decisions.
    1. Review the complete list of tables, one at a time.
    2. When all records for a given table are for the good PIDM, no decision is needed (leave the Action blank).
    3. As a general rule choose change for tables in the bad PIDM column.
      1. The thinking is we’d like to keep all the data if possible.
    4. For any table with bad PIDM records an Action must be specified.
      1. The "Ev" check box can be used to mark a table for later review. However the Script Gen will not complete until an Action is selected.
    5. When all records for a given table are for the bad PIDM, they should be reviewed and usually changed to the good PIDM (Action = C).
    6. When records existed under both the good PIDM and the bad PIDM:
      1. Delete (D) duplicate information,
      2. Review non-duplicate information for repeating tables and usually change to the good PIDM (Action = C),
      3. Review non-duplicate information for non-repeating tables and decide if bad or good record can be deleted. If not, lower level record updating may be needed (see item number 4 below).
    7. Base tables (xxBxxxx name) can only have one row per key (PIDM, etc.); therefore Action C can’t be used. So use delete or lower level updates.
    8. When reviewing archived data, be aware of columns marked as primary keys (pk).  
      1. Usually do not want to duplicate primary key fields.
  4. Consider these default rules when making decisions.
    1. Use the action predefined with tables AORCONT, AGRGMEM, AGRPMEM, AGRGSOL, and APREHIS if the action change causes Script Gen to fail on any of those tables.
    2. When the table owner is OTGMGR these are Imaging Tables. Always select predefined for any bad PIDM. Leave the good PIDM blank.
    3. Delete the following tables when they appear for the bad PIDM(s):
      1. GOBSRID - IMS Sourced ID Base Table.
      2. GOBTPAC - CAMS/Login/PIN etc info.
      3. GOBUMAP - Mapping between PIDM and UDC ID.
      4. GORPRAC - Person Race Table.
      5. GORCGEO - Collector table of persons address for the Geographic Regions/Division process.
      6. GORPAUD - Third Party Access Audit Table.
      7. GLAEXTR - Pop Sel Extract (can’t see reasons needed to keep old pop sel results).
      8. FTVVEND - Vendor Validation Table.
      9. SRTIDEN - Always delete this. It holds Prospect Tape load data.
      10. SPRAPIN - Alt PIN – unique constraint – OK to delete info for old terms.
    4. The following table(s) should always be changed from the bad PIDM to good:
      1. GURMAIL - Mail Table.
      2. For SPBPERS, must delete the good or bad. Make lower level record change if needed.
      3. For the SORCONT, SORLCUR, and SORLFOS tables ask Ben Metzger for help resolving Script Gen error.
      4. For SPRIDEN, only one record can be active (CHANGE_IND is null), so use record level updating to resolve. Never choose delete for either the good or bad PIDM(s).
      5. For SPRADDR, retain addresses from the bad PIDM that do not have the corresponding type(s) for the good PIDM. Examples include RE and PO addresses, needed by Financial Services. Evaluate by address types such as PR so that the most current address of each type is retained.
      6. For SPRTELE, you cannot have duplicate sequence numbers. Also, you should not have multiple active primary records with the same type. So use lower level record updating to resolve.
      7. TAISMGR schema specific issues:
      8. Ask Clement or Mandy for help resolving all errors with the TBRACCD and TBRAPPL tables.
      9. For rows in the TBRACCD table look for charge/payment detail codes that begin with "J" (Greenwood Parents). Contact Kim Benton in Bursar before merging.
      10. WF_ROLE_USER and WF_ROLE_USER_AUDIT; contact the Workflow Analyst to manually resolve the duplicate PIDM for these rows.
  5. Certain changes must be coded at the record level.
    1. Use lower level record updating when issues cannot be solved at table level. Mark bad PIDM table action with L (lower level).
    2. Delete or change for each bad PIDM record, or enter L for the record and enter changes for columns.
    3. When PIDMs are found in non-key columns (i.e. someone else’s record), you must change with lower level column changes.
    4. SPRIDEN change indicator is another example of when lower level column updating is needed. Enter “I” (ID change) in the SPRIDEN_CHANGE_IND column to avoid having multiple active SPRIDEN records and to create the bad M-number as an alternate ID. This is a requirement.
    5. Accounts Receivable tables (TBBSTMT and TBRACCD) require record level changes. Contact Financial Services (currently Mandy) to get help with them.
    6. When Financial Aid table entries are found for both IDs, it's best to contact the Financial Aid Office (currently Chelsey) to get help with the resolution.

Script Gen

The last phase of the process is generating and executing the corrective code that consolidates the multiple identities. Using the table/row decisions that were made in phase two the GWAPP tool will generate the necessary SQL statements in a script that is then executed against the Banner data.

Running Script Gen to validate actions and find errors

  1. From the GZAPIDM screen click the Save  icon a final time before running the corrective script.
    1. Click .

      While in the Script Generation panel DONOT check mark the check box for validation runs. Only check mark the “Include Commit?” box for the final Script Gen run.

    2. Click . Review the output and determine what needs to be changed (if any).

      1. The script generation usually runs fairly quickly and will return the result without refreshing.

    3. Click  to exit and return back to the GZAPIDM form.

    4. Make changes as needed in GZAPIDM, then re-Run Script.

      1. To fix errors detected during Script Gen:
        1. Select delete rather than change on the table causing the error.
        2. Delete/update bad PIDM data via the lower Level feature.
        3. Delete/update base table data through standard INB forms (ask for help from table data’s owners).
        4. Call/email Tom when unfamiliar errors show up.
        5. Repeat until all Script Gen errors are gone.
    5. When the Script Gen runs cleanly this is what will display at the tail end of its output:


    6. When the script is ready to run/commit, recheck the Comments: field of the PSN Details tab in the GZAPENT screen for West Plains data being negatively affected (i.e., “M012334567-WP-XX : M76543210-WP-SR”) . Respond accordingly.
      1. If the TBRACCD or TBRAPPL tables are involved, rerun the Datapull prior to committing the Script Gen. This will find any last minute A/R transaction table updates that may have occurred. Update these two table’s decisions as necessary.
      2. Finally, check mark the  check box, then click .
      3. All changes to Banner's tables will be executed and committed.
    7. This is what you should see upon successful execution.


    8. Click  to exit and return back to the GZAPIDM form.
    9. Exit GZAPIDM and return back to the GZAPENT form.
    10. From the PSN Info tab list:
      1. select (check mark) the correct PSN entry.


      2. Then select Mark PSN as Closed from the  drop down list.
      3. This will remove the PSN from the "Open Cases" category. The PSN can still be found by changing the Query Filter to "Closed Cases".
    11. When marking a PSN as Closed make sure you’ve only check marked your own PSN and not accidentally someone else’sNotify the person who reported/discovered the duplicate PIDM that the changes have been made
    12. Try to complete one specific PSN as expeditiously as possible, as changes can occur to Banner data during the table decision-making proc

 


For questions or comments, contact the Computer Services Help Desk
HelpDesk@MissouriState.edu
417-836-5891