The Registrant info on the Transactions are keyed in by users, but we found that the users were keying them in on the wrong Transactions.  The BBEC_REGISTRANTS table houses the Registration information for Events in BBEC.  A lot of the accounts will have a Constituency of EVENT REGISTRANT for that event, but they are "Guests" of a "Host."  Only HOSTS are found in the BBEC_REGISTRANT table.  We also found that the Gifts' registration info in TA is being keyed with the Guests' account info.  That is why they do not seem to match up.

Basically, the Sync errors are generated because the BBEC Event/Account/Name ID combo are not in the BBEC_REGISTRANT table (i.e. they are not registered to that Event in CRM or they are Guests and not a Host). 

The resolution is to have the client manually correct all the transactions, to change the Event account to the Host, rather than the Guest.  Using the techniques and queries below, create a spreadsheet with all the Transactions that have this issue (that also has info about the current Event it is setup for and the BBEC Event account it is setup for), and a list of all of the Hosts for those events in the BBEC_REGISTRANT table and send this to the client.

To create a spreadsheet of all the records in question:

1.  Use the ad-hoc query to list out Sync errors in CRM:  How can I view BBEC Sync Error data (from Team Approach Sync Errors table).  Use the following criteria:

Field:    Error Description
Value:    The following fields are invalid:  REGISTRANTID

If the number of records in the results or preview page exceeds 500, follow these steps to create a CSV from the output.  If not, just export the results to a CSV file and skip to Step #2:

1a.  Create or update Ad-Hoc Query to display Sync Error records.
1b.  Create an Export definition in CRM, under Administration -> Export.  Set Export Type = Ad-hoc Query, selecting the Ad-Hoc Query name from Step #1.
1c.  Open the Export and click on "Start Process" in the navigator.
1d.  Click on "Download output" to download the file locally.  Use the "Download to CSV" option.

2.  Create temp table to hold the SyncID's with the following SQL command in PL/SQL:
 
CREATE TABLE {temp table name}
( SYNCID VARCHAR2( 100 ) ) 
TABLESPACE PBDS_DATA_TEMP 
NOLOGGING 
NOCACHE 
NOPARALLEL 

Note:  the table name must be 30 characters or less, and the format can be something like TMP_{Initials}_{Case Number}_SYNC_ERRORS.  For example:  TMP_13517405_RSR_SYNC_ERRORS.

3.  Refer to KB 68389 on importing just the SYNCID column from the CSV file into the temp table:  How do I use the Text Importer in PL/SQL Developer?

4.  Run this query to check for REGISTRANTID values, and confirm that the field = '00':
 
SELECT * FROM T2B_MAP_EVENTREGISTRANTPAYMENT
WHERE SYNCID IN ( SELECT SYNCID FROM {temp table name from Step #2} );

5.  To get a list of the affected BBEC Event IDs from the Transactions table, use the following query (this does not have to be sent to the client):
 
SELECT DISTINCT (T.BBEC_EVENT_ID) "BBEC Event ID"
FROM   BBS_TRANSACTIONS S
INNER  JOIN TRANSACTIONS T
ON     T.ACCOUNT_ID = S.ACCOUNT_ID
      AND T.GIFT_DATE = S.GIFT_DATE
      AND T.GIFT_SEQ = S.GIFT_SEQ
      AND T.ADJUSTMENT_SEQ = S.ADJUSTMENT_SEQ
LEFT   OUTER JOIN BBEC_REGISTRANT BBRR
ON     BBRR.ACCOUNT_ID = T.BBEC_EVENT_ACCOUNT_ID
      AND BBRR.NAME_ID = T.BBEC_EVENT_NAME_ID
      AND BBRR.EVENTID = T.BBEC_EVENT_ID
LEFT   OUTER JOIN BBI_T2B_CONSTITUENT_DATA JCONSTITUENT
ON     JCONSTITUENT.ACCOUNT_ID = S.ACCOUNT_ID
      AND JCONSTITUENT.NAME_ID = S.NAME_ID
WHERE  S.EVENTREGISTRANTPAYMENT_A IN ('I', 'U', 'B')
      AND S.SYNCID IN (SELECT SYNCID
                    FROM   {temp table name from Step #2} )
ORDER  BY T.BBEC_EVENT_ID;

6.  To get a full list of Transactions that caused the error and current Event Account ID, that must be fixed manually:
 
SELECT T.ACCOUNT_ID, T.GIFT_DATE, T.GIFT_SEQ, T.ADJUSTMENT_SEQ, TR.TA_ID1 EVENT_NAME, TR.TA_ID2 EVENT_START,
      TR.TA_ID3 EVENT_END, T.BBEC_EVENT_ACCOUNT_ID, T.BBEC_EVENT_NAME_ID, N.NAME_LINE EVENT_ACCOUNT_ID_NAME
FROM   PBDS.BBS_TRANSACTIONS S
INNER  JOIN TRANSACTIONS T
ON     T.ACCOUNT_ID = S.ACCOUNT_ID
      AND T.GIFT_DATE = S.GIFT_DATE
      AND T.GIFT_SEQ = S.GIFT_SEQ
      AND T.ADJUSTMENT_SEQ = S.ADJUSTMENT_SEQ
LEFT   OUTER JOIN BBEC_REGISTRANT BBRR
ON     BBRR.ACCOUNT_ID = T.BBEC_EVENT_ACCOUNT_ID
      AND BBRR.NAME_ID = T.BBEC_EVENT_NAME_ID
      AND BBRR.EVENTID = T.BBEC_EVENT_ID
LEFT   OUTER JOIN PBDS.BBI_T2B_CONSTITUENT_DATA JCONSTITUENT
ON     JCONSTITUENT.ACCOUNT_ID = S.ACCOUNT_ID
      AND JCONSTITUENT.NAME_ID = S.NAME_ID
INNER  JOIN T2B_ROWS TR
ON     TR.BBEC_ID = T.BBEC_EVENT_ID
      AND TR.TA_TABLE = 'BBEVENT'
LEFT   OUTER JOIN NAMES N
ON     N.ACCOUNT_ID = T.BBEC_EVENT_ACCOUNT_ID
      AND N.NAME_ID = T.BBEC_EVENT_NAME_ID
WHERE  S.EVENTREGISTRANTPAYMENT_A IN ('I', 'U', 'B')
      AND S.SYNCID IN (SELECT SYNCID
                    FROM   PBDS.{temp table name from Step #2} );

7.  To get the full list of from the BBEC Event Registrant table (BBEC_REGISTRANT), with Event Names and Hosts:
 
SELECT T.TA_ID1 EVENT_NAME, T.TA_ID2 EVENT_START, T.TA_ID3 EVENT_END, N.NAME_LINE HOST_NAME, BR.ACCOUNT_ID, BR.NAME_ID
FROM   BBEC_REGISTRANT BR
INNER  JOIN T2B_ROWS T
ON     T.BBEC_ID = BR.EVENTID
      AND TA_TABLE = 'BBEVENT'
INNER  JOIN NAMES N
ON     N.ACCOUNT_ID = BR.ACCOUNT_ID
      AND N.NAME_ID = BR.NAME_ID
WHERE  BR.EVENTID IN (SELECT DISTINCT (T.BBEC_EVENT_ID)
                  FROM   BBS_TRANSACTIONS S
                  INNER  JOIN TRANSACTIONS T
                  ON     T.ACCOUNT_ID = S.ACCOUNT_ID
                        AND T.GIFT_DATE = S.GIFT_DATE
                        AND T.GIFT_SEQ = S.GIFT_SEQ
                        AND T.ADJUSTMENT_SEQ = S.ADJUSTMENT_SEQ
                  LEFT   OUTER JOIN BBEC_REGISTRANT BBRR
                  ON     BBRR.ACCOUNT_ID = T.BBEC_EVENT_ACCOUNT_ID
                        AND BBRR.NAME_ID = T.BBEC_EVENT_NAME_ID
                        AND BBRR.EVENTID = T.BBEC_EVENT_ID
                  LEFT   OUTER JOIN BBI_T2B_CONSTITUENT_DATA JCONSTITUENT
                  ON     JCONSTITUENT.ACCOUNT_ID = S.ACCOUNT_ID
                        AND JCONSTITUENT.NAME_ID = S.NAME_ID
                  WHERE  S.EVENTREGISTRANTPAYMENT_A IN ('I', 'U', 'B')
                        AND S.SYNCID IN (SELECT SYNCID
                                     FROM   {temp table name from Step #2} ) )
ORDER  BY T.TA_ID1 DESC;

8.  Send spreadsheet(s) with lists from Step # 6 and #7 to the client.