SQL-024 Auditor access count

This SQL counts qualifying recent audit assignments for a vendor and policy so fee-auditor authorization can grant or deny access within the configured audit window.

Classification

Field	Value
SQL ID	SQL-024
Operation type	Read
Execution method	Inline
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsPolicySecurity
Criteria property	N/A
Lifecycle method	CanAuditorAccessPolicy

Parameters

Parameter	Direction	Type	Max length	Description
:vendornumber	Input	Decimal	N/A	Auditor vendor number used to match the assigned auditor.
:AuditPolicyNumber	Input	Decimal	N/A	Policy number whose audit access is being checked.
:MinCancelDate	Input	Date	N/A	Minimum cancellation date threshold for the 60-day access window.
:MinCompletedDate	Input	Date	N/A	Minimum completed date threshold for the 60-day access window.

Tables

Table	Access	Columns used
PA_AUDIT	SELECT / JOIN	pa_audit_seq_num, pa_revises_seq_num, pa_auditor_id, cancel_date, `completed_date
PA_AUDITOR	JOIN	pa_auditor_id, `vendor_num
PA_POLICY_PERIOD	SELECT	pa_audit_seq_num, `policy_num

Sample SQL

SELECT
  COUNT(*) AS AuditCount
FROM pa_audit pa
LEFT OUTER JOIN pa_audit pa2
  ON (pa2.pa_revises_seq_num = pa.pa_audit_seq_num)
JOIN pa_auditor A
  ON (A.pa_auditor_id = pa.pa_auditor_id
  AND A.vendor_num = :vendornumber)
WHERE pa.pa_audit_seq_num IN (
    SELECT
      pa_audit_seq_num
    FROM pa_policy_period
    WHERE policy_num = :AuditPolicyNumber)
  AND NVL(PA.CANCEL_DATE, :MinCancelDate) >= :MinCancelDate
  AND NVL(PA.COMPLETED_DATE, :MinCompletedDate) >= :MinCompletedDate

Operations and processes

• Authorize user for policy
• Retrieve accessible policy list

Business rules

• BR-VP-005: Access granted per user-access-level matrix
• BR-VP-006: Auditor 60-day access window

Sources

• SAIF.Business.View.Policy/clsPolicySecurity.vb