Bad Enrollment Transactions =========================== This script runs from the `OrgSearch BlueToolbar`, and should be run by Admins only. Finally, as with all SqlScript reports, you have a Download to Excel button. The history link takes you to a page showing the details of the transactions for the person/org row. The transactions that are out of the permissible patterns of a valid history timeline, are highlighted in red. From there you can delete transactions to bring the history back into compliance. The rules for a valid history are: - The first transaction must be a Join. - A change can only follow another Change or a Join. - A Drop can only follow a Change or a Join. - A Join can only follow a Drop. - There should never be two Drops in a row. - There should never be two Joins in a row. - The transactions are linked together by a NextTranDate which points to the next transaction in date order. - Each transaction after the first should point back to an associated Join transaction through the Enroll Id. The recommended name is ``BadEnrollmentTransactions`` .. figure:: https://i.tpsdb.com/2016-09-27_20-23-38.png :target: # Sample Report Use the following code to Create the SQL Script. See :doc:`../CreateSqlScript`. .. code-block:: sql --Roles=Admin WITH e AS ( SELECT PeopleId, OrganizationId FROM dbo.EnrollmentTransaction WHERE OrganizationId IN (SELECT Value FROM dbo.SplitInts(@orgids)) GROUP BY PeopleId, OrganizationId ) SELECT p.PeopleId, Name2, o.OrganizationId, o.OrganizationName, linkfornext = '/TransactionHistory/Enrollment/' + CONVERT(VARCHAR(25), p.PeopleId) + '/' + CONVERT(VARCHAR(25), o.OrganizationId), 'history' History FROM e JOIN dbo.People p ON p.PeopleId = e.PeopleId JOIN dbo.Organizations o ON o.OrganizationId = e.OrganizationId WHERE EXISTS(SELECT NULL FROM dbo.EnrollmentHistory(e.PeopleId, e.OrganizationId) WHERE isgood = 0) ORDER BY o.OrganizationName, p.Name2 | | +--------------------+------------------+ | **Latest Update** | **11/13/2020** | +--------------------+------------------+ Modify image link with secure protocol.