Background Checks
=================

This script shows the background checks requested through **Protect My Ministry** and **MinistrySafe**.
The report will indicate the last date a check was run, the Approval Status, Service Code,
Report Type, Label (f the church uses those), the Status of the check, and the date it was created.
.
If you run the script from Special Content it will run on the last search results.
You have the option to add it to the blue Toolbar.
Then you can run the report for an organization, Tag, or a Search Builder results.
The report will include only people that have a processed date and/or a status.
In other words, if a person is included in the organization or search results
and they do not have a processed date or a status they will not be included in the report.

Sample Report
-------------
Here is what the report looks like.

.. figure:: https://i.tpsdb.com/2017-06-29_16-52-57.png
    :target: #

    Background Check Report

SQL Code for Background Checks
------------------------------

The recommended name is ``BackgroundChecks``

Use the following code to create the SQL Script.
If you are not familiar with creating scripts,
see the help article below.

See :doc:`../CreateSqlScript`.

.. code-block:: sql

    --Roles=ManageApplication
    ;WITH backgroundchecks AS (
        SELECT
             v.PeopleId
            ,[LastCheck] = v.ProcessedDate
            ,[ApprovalStatus] = vs.Description
            ,bg.Created
            ,[CheckStatus] = (CASE bg.StatusID WHEN 1 THEN 'Not Submitted' WHEN 0 THEN 'Error'
                    WHEN 2 THEN 'Submitted' WHEN 3 THEN 'Complete' ELSE '' END)
            ,[ReportType] = (CASE bg.ReportTypeID WHEN 1 THEN 'Background' WHEN 2 THEN 'Credit' ELSE '' END)
            ,[Label] = lb.Description
            ,bg.ServiceCode
        FROM dbo.Volunteer v
        LEFT JOIN dbo.BackgroundChecks bg ON bg.PeopleID = v.PeopleId
        LEFT JOIN lookup.BackgroundCheckLabels lb ON lb.Id = bg.ReportLabelID
        LEFT JOIN lookup.VolApplicationStatus vs ON vs.Id = v.StatusId
        WHERE (bg.ReportTypeID IS NULL OR bg.ReportTypeID IN (1,2))
            AND (v.ProcessedDate IS NOT NULL OR v.StatusId IS NOT NULL OR LEN(v.Comments) > 0)
    )
    SELECT
         b.PeopleID
        ,p.Name2
        ,b.LastCheck
        ,b.ApprovalStatus
        ,b.ServiceCode
        ,b.ReportType
        ,b.Label
        ,b.CheckStatus
        ,b.Created
    FROM backgroundchecks b
    JOIN dbo.People p ON p.PeopleId = b.PeopleID
    JOIN dbo.TagPerson tp ON tp.PeopleId = p.PeopleId AND tp.Id = @qtagid
    ORDER BY p.Name2, b.PeopleID, b.LastCheck


|
|

+--------------------+------------------+
| **Latest Update**  | **10/13/2022**   |
+--------------------+------------------+

Added MinistrySafe.