---
title: "SQL-012: Retrieve auditors by vendor/flags"
summary: Retrieves a collection of auditors from PA_AUDITOR filtered by vendor number and scheduling/flag criteria.
status: draft
owner: <owner>
last_reviewed: 2026-07-24
library: SAIF.Business.External.Users
reference_type: package
---

# SQL-012: Retrieve auditors by vendor/flags

Retrieves a collection of premium auditor records from `PA_AUDITOR` filtered by vendor number and optional scheduling, filter, assignment, and reviewer flags. Passing `'x'` for a flag parameter disables that filter.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-012 |
| **Operation type** | Read |
| **Execution method** | Inline SQL |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | Text |
| **Business object** | `clsAuditorCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:VendorNumber` | Input | String | 9 | Vendor number filter; NULL disables vendor filtering. |
| `:AssignFlag` | Input | String | 1 | Assignment flag filter; `'x'` disables. |
| `:FilterFlag` | Input | String | 1 | Filter-list flag filter; `'x'` disables. |
| `:ScheduleFlag` | Input | String | 1 | Schedule flag filter; `'x'` disables. |
| `:ReviewerFlag` | Input | String | 1 | Reviewer flag filter; `'x'` disables. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PA_AUDITOR](../database-tables.md#pa_auditor) | SELECT | All columns (see SQL-011 for column list) |

## Sample SQL

```sql
SELECT
    PA_AUDITOR.ASSIGN_FLAG AS AssignFlag,
    PA_AUDITOR.AUDITOR_ASSIGNEE_ID AS AuditorAssigneeID,
    PA_AUDITOR.AUDITOR_EMAIL_ADDR AS AuditorEmailAddr,
    PA_AUDITOR.AUDITOR_NAME AS AuditorName,
    PA_AUDITOR.AUDITOR_SHORT_NAME AS AuditorShortName,
    PA_AUDITOR.FILTER_LIST_FLAG AS FilterListFlag,
    PA_AUDITOR.MONTHLY_MAXIMUM_COUNT AS MonthlyMaximumCount,
    PA_AUDITOR.PA_AUDITOR_ID AS AuditorID,
    PA_AUDITOR.SCHEDULE_FLAG AS ScheduleFlag,
    PA_AUDITOR.USER_ID AS UserID,
    PA_AUDITOR.VENDOR_NUM AS VendorNumber,
    PA_AUDITOR.REVIEWER_FLAG AS ReviewerFlag,
    PA_AUDITOR.ACTIVE_REVIEWER_FLAG AS ActiveReviewerFlag
FROM PA_AUDITOR
WHERE (:VendorNumber = VENDOR_NUM OR :VendorNumber IS NULL)
  AND (
      (:AssignFlag = ASSIGN_FLAG OR :FilterFlag = FILTER_LIST_FLAG
       OR :ScheduleFlag = SCHEDULE_FLAG OR :ReviewerFlag = REVIEWER_FLAG)
      OR (:AssignFlag = 'x' AND :FilterFlag = 'x'
          AND :ScheduleFlag = 'x' AND :ReviewerFlag = 'x')
  )
```

## Operations and processes
- [Auditor assignment / override](../operations/auditor-assignment-override.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with `**` could not be confirmed; treat as unknown until verified.

## Sources
- `Business\SAIF.Business.External.Users\clsAuditorCollection.vb`