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	SELECT	All columns (see SQL-011 for column list)

Sample 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

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