SQL-018: Last completed audit date lookup¶

clsPolicyData uses this statement to report when a policy was last audited. Only completed final audits qualify, and a fixed selection-category priority breaks ties between audits that end on the same date.

Classification¶

Field	Value
SQL ID	SQL-018
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	`[unqualified]`**
Command type	Text
Business object	`clsPolicyData`
Criteria property	N/A (ad hoc method, not `IBusinessCriteria`)
Lifecycle method	`GetLastAuditDate`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:PolicyNumber`	Input	Decimal	not specified	Policy number whose most recent completed final audit is requested; results are ranked by a fixed selection-category priority.

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¶

Lazy-load policy detail

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.View.Policy/clsPolicyData.vb