SQL-028 Fee auditor policy list

This SQL retrieves the policy list for a fee auditor by joining active audit assignments to policy names so the UI can present accessible policies.

Classification

Field	Value
SQL ID	SQL-028
Operation type	Read
Execution method	Inline
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsPolicySecurity
Criteria property	GetAuditorPolicyListSQL
Lifecycle method	GetPolicyListForFeeAuditor

Parameters

Parameter	Direction	Type	Max length	Description
:VendorNum	Input	Decimal	N/A	Auditor vendor number used to find accessible policies.

Tables

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

Sample SQL

SELECT
  pa.POLICY_NUM,
  pol.POLICY_NAME
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
JOIN policy pol
  ON pol.POLICY_NUM = pa.POLICY_NUM
WHERE a.VENDOR_NUM = :VendorNum
  AND NVL(pa.cancel_date, SYSDATE + 60) >= SYSDATE + 60
  AND NVL(pa.completed_date, SYSDATE + 60) >= SYSDATE + 60
ORDER BY 1 ASC

Operations and processes

• 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