SQL-019 Last audit date

This SQL retrieves the completed date of the most recent final audit for a policy so policy detail views can display the latest completed audit activity.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:PolicyNumber	Input	Decimal	N/A	Policy number whose latest completed final audit date is requested.

Tables

Table	Access	Columns used
PA_AUDIT	SELECT	policy_num, pa_type_code, pa_status_code, completed_date, end_date, pa_slctn_category_group_code, `pa_audit_seq_num

Sample SQL

SELECT
  completed_date
FROM (
  SELECT
    completed_date
  FROM pa_audit
  WHERE policy_num = :PolicyNumber
    AND pa_type_code = 'FINL'
    AND pa_status_code = 'COMPL'
  ORDER BY
    end_date DESC,
    CASE pa_slctn_category_group_code
      WHEN 'COMP' THEN 1
      WHEN 'BUSR' THEN 2
      WHEN 'GAC' THEN 10
      WHEN 'CYCL' THEN 20
      WHEN 'OTHER' THEN 30
      ELSE 50
    END,
    pa_audit_seq_num DESC)
WHERE rownum = 1

Operations and processes

• Retrieve policy data

Business rules

• BR-VP-010: Demo users see masked data

Sources

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