Registration Without Account Codes

Using the code below, you can create a SQL report that will display a list of everyone who has registered online and made a payment to an Involvement without an Accounting Code identified. This can be helpful if you are seeing batch entries without anything in the Fund/Code column. Without this information, some reports, such as the Totals By Fund report, will not be accurate.

Note

If you do not see any results when you run the report, then all of your batch entries are being created as expected and there is nothing to fix.

Report Details

The following information is displayed in the report.

  • PeopleId is a link to the person’s record.

  • Name is the registrant’s name.

  • InvId is the Involvement Id for the Involvement in which the person registered.

  • InvolvementName is the name of the Involvement in which the person registered.

  • Date is the registration date.

  • Amount is the amount paid.

  • BatchId links to the Batch.

Cleanup Process

If you see results when you run this report, follow these steps.

Step 1

Click the BatchId to go directly to the affected batch.

Step 2

Find the batch entries that are missing a description.

Step 3

Select the correct Account Code from the Fund/Code drop down menu.

Note

If there are no account codes to choose from, you will need to create one or more in the Account Code lookup table.

See also

Lookup Codes

To prevent additional batch entries from being created without a description, we recommend you configure the correct Accounting Code for the affected registrations.

Step 1

Click on the InvId to go directly to the affected Involvement.

Step 2

Navigate to the Registration > Fees tab.

Step 3

Click Edit and select the correct option from the Accounting Code drop down menu.

Step 4

Click Save to save your changes.

Create the Report

Use the following code to Create the SQL Script. The recommended name for this report is RegistrationsWithoutAccountCodes.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT
        c.PeopleID,
        p.Name,
        LinkForNext = '/Org/' + convert(varchar(12), t.OrgId), format(t.OrgId, '0') InvId,
        o.OrganizationName AS InvolvementName,
        c.ContributionDate Date,
        c.ContributionAmount Amount,
    LinkForNext = '/Batches/Detail/' + convert(varchar(12), bd.BundleHeaderId),
    format(bd.BundleHeaderId, '0') BatchId
FROM dbo.Contribution c
    JOIN dbo.People p ON p.PeopleId = c.PeopleId
    JOIN dbo.[Transaction] t on t.Id = c.TranId
    JOIN dbo.Organizations o on o.OrganizationId = t.OrgId
    JOIN dbo.BundleDetail bd on bd.ContributionId = c.ContributionId
WHERE ContributionTypeId = 99 and AccountCodeId is null
ORDER BY bd.BundleHeaderId, t.OrgId, c.ContributionDate


Latest Update

12/7/2023

New article.