SQL-021: Read Pa Audit Data

This statement supports the premium-audit library by read Pa Audit Data.

Classification

Field	Value
SQL ID	SQL-021
Operation type	Read
Execution method	CommandType.Text / DataRequestType.DataResultSet
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsAudit
Criteria property	n/a — application layer
Lifecycle method	clsAudit.GetPriorAuditFields (application layer)

Parameters

Parameter	Direction	Type	Max length	Description
:CurrentAuditSeqNum	Input	bound from AuditSeqNum	—	Bound in clsAudit.SetPriorAuditDataRequest (line 2721)

Tables

Table	Access	Columns used
PA_AUDIT	JOIN	See sample SQL.
PA_POLICY_PERIOD	JOIN	See sample SQL.

Sample SQL

SELECT
  *
FROM
 (
      SELECT
        :CurrentAuditSeqNum as CurrentAuditSeqNum,
        CASE pri.pa_type_code
          WHEN 'FINL' THEN 1
          WHEN 'INT' THEN 2
          WHEN 'NEW' THEN 3
          WHEN 'PRLM' THEN 3
          WHEN 'TEST' THEN 4
          ELSE 5
        END as Priority,
        pri.end_date as LastAuditDate,
        pri.pa_type_code as PriorAuditTypeCode,
        pri.pa_auditor_id as PriorAuditorID,
        max(pri.completed_date) as PriorAuditCompleteDate,
        max(pri.pa_audit_seq_num) as PriorAuditSeqNum,
        sum(nvl(pp.after_audit_std_prem_amount - pp.before_audit_std_prem_amount,0)) as PriorAuditResultAmount
      FROM
        pa_audit pri
      JOIN
        pa_policy_period pp on(pri.pa_audit_seq_num=pp.pa_audit_seq_num)
      WHERE
        pri.pa_audit_seq_num in (
            SELECT
              priora.pa_audit_seq_num
            FROM
              pa_audit cura,
              pa_policy_period curpp,
              pa_policy_period priorpp,
              pa_audit priora
            WHERE
              cura.pa_audit_seq_num = :CurrentAuditSeqNum
              AND curpp.pa_audit_seq_num = cura.pa_audit_seq_num
              AND priorpp.policy_num = curpp.policy_num
              AND priorpp.pa_audit_seq_num = priora.pa_audit_seq_num
              AND priora.pa_status_code = 'COMPL'
              AND priora.pa_audit_seq_num < curpp.pa_audit_seq_num
              AND priora.end_date <= cura.end_date
          )
      GROUP BY
        pri.end_date,
        pri.pa_type_code,
        pri.pa_auditor_id
      ORDER BY
        pri.end_date desc,
        priority
    )
WHERE
  rownum = 1

Operations and processes

• Assign an audit to an auditor
• Synchronize an audit with WCIS policy-period changes
• Save a premium audit
• Audit lifecycle status transitions

Business rules

• BR-020
• BR-021
• BR-032
• BR-035
• BR-036
• BR-037
• BR-057

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.Policy.Audit.Premium/clsAudit.vb:2707-2745, 5021-5060