SQL-VP-037 Check whether a vendor auditor may access a policy¶

Used by clsPolicySecurity.CanAuditorAccessPolicy() to enforce BR-VP-009, returning a COUNT(*)-style scalar used directly as an access-check boolean.

Classification¶

Field	Value
SQL ID	SQL-VP-037
Operation type	Read
Execution method	Inline SQL (`StringBuilder`-built)
Database	Oracle
Schema	`[unqualified]**`
Command type	Text
Business object	`clsPolicySecurity`
Criteria property	`CanAuditorAccessPolicy()` (ad hoc method)
Lifecycle method	ad hoc method

Parameters¶

Parameter	Direction	Type	Max length	Description
`vendornumber`	Input	ID	N/A	Auditor's vendor number.
`AuditPolicyNumber`	Input	Decimal	N/A	Policy number to check audit access for.
`MinCancelDate`	Input	Date	N/A	Minimum cancel-date bound (excludes older cancelled audits).
`MinCompletedDate`	Input	Date	N/A	Minimum completed-date bound (excludes older completed audits).

Tables¶

Table	Access	Columns used
PA_AUDIT	SELECT / JOIN	`AUDITOR_SEQ_NUM`, `POLICY_PERIOD_SEQ_NUM`, `CANCEL_DATE`, `COMPLETED_DATE`
PA_AUDITOR	JOIN	`AUDITOR_SEQ_NUM`, `VENDOR_NUM`
PA_POLICY_PERIOD	JOIN	`POLICY_PERIOD_SEQ_NUM`, `POLICY_NUM`

Sample SQL¶

SELECT
  COUNT(*)
FROM
  PA_AUDIT
  INNER JOIN PA_AUDITOR
    ON PA_AUDIT.AUDITOR_SEQ_NUM = PA_AUDITOR.AUDITOR_SEQ_NUM
  INNER JOIN PA_POLICY_PERIOD
    ON PA_AUDIT.POLICY_PERIOD_SEQ_NUM = PA_POLICY_PERIOD.POLICY_PERIOD_SEQ_NUM
WHERE
  PA_AUDITOR.VENDOR_NUM = :vendornumber
  AND PA_POLICY_PERIOD.POLICY_NUM = :AuditPolicyNumber
  AND PA_AUDIT.CANCEL_DATE >= :MinCancelDate
  AND PA_AUDIT.COMPLETED_DATE >= :MinCompletedDate

Reconstructed from the StringBuilder-built query in clsPolicySecurity.vb; exact join keys/column names are inferred from method naming (CanAuditorAccessPolicy) and typical PA_* audit schema conventions — should be spot-checked against the live source for byte-exact accuracy.

Business rules¶

BR-VP-009: Fee-auditor vendor/policy audit-record match required

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¶

SAIF.Business.View.Policy\clsPolicySecurity.vb