pk73211_premium_audit.GetAuditLocationSeqNum

Overview

Field	Value
Schema	WCIS_DBA
Type	Package function
Database	Oracle / WCIS
Calling libraries	SAIF.Business.Policy.Audit.Premium
Calling classes	clsAudit, clsAuditCollection

Purpose

Picks the single best visitable location for an audit — AUD, then PAY, then POL, then primary BUS — rejecting expired locations and post-office boxes

Procedures / Methods

GetAuditLocationSeqNum

Field	Value
Returns	See Oracle signature / caller behavior
Called by	AuditLocationSeqNum (SQL-018); inline in GenerateRetrieveSQL (SQL-027)

Description: Picks the single best visitable location for an audit — AUD, then PAY, then POL, then primary BUS — rejecting expired locations and post-office boxes

Business Rules

Rule ID	Rule	Condition	Action
PROC-RULE-018	Only four location types can serve as an audit address	l.location_code IN ('AUD','POL','BUS','PAY')	Other locations are never selected
PROC-RULE-019	A business location qualifies only when it is the primary one	l.location_code <> 'BUS' OR (l.location_code = 'BUS' AND l.primary_location_flag = 'Y')	Non-primary business locations are excluded
PROC-RULE-020	Expired locations are never audit locations	l.loc_exp_date IS NULL	Row is excluded
PROC-RULE-021	A post-office box can never be an audit address	UPPER(l.loc_addr1) NOT LIKE any of 'PO BOX%', 'P.O BOX%', 'P.O. BOX%', 'PO. BOX%', 'P O BOX%'	Row is excluded — an auditor must be able to visit the address
PROC-RULE-022	Audit-address preference order is AUD → PAY → POL → everything else	ORDER BY CASE l.location_code WHEN 'AUD' THEN '1' WHEN 'PAY' THEN '2' WHEN 'POL' THEN '3' ELSE '4' END with ROWNUM = 1	Exactly one location sequence number is returned
PROC-RULE-023	A policy with no qualifying location yields no audit location rather than an error	WHEN NO_DATA_FOUND	RETURN NULL
PROC-RULE-024	Any other failure is escalated with full context	WHEN OTHERS	`pk73090_error_handler.raise_an_error('GetAuditLocationSeqNum: ' \

Operations

Operation	Description	Trigger
Determine the audit address location	Picks the single best visitable location for an audit — AUD, then PAY, then POL, then primary BUS — rejecting expired locations and post-office boxes	clsAudit.AuditLocationSeqNum (SQL-018) and inline in clsAuditCollection.GenerateRetrieveSQL (SQL-027); OP-005, OP-006

Source Code

PL/SQL package or method excerpt

   FUNCTION getauditlocationseqnum( in_policynumber IN policy.policy_num%TYPE)
      RETURN NUMBER
   IS
      v_audit_loc_seq_num   NUMBER := NULL;
   BEGIN
      SELECT auditlocationseqnum
        INTO v_audit_loc_seq_num
        FROM (SELECT loc_seq_num AS auditlocationseqnum
                FROM location l
               WHERE     policy_num = in_policynumber
                     AND l.location_code IN ('AUD'
                                           , 'POL'
                                           , 'BUS'
                                           , 'PAY')
                     AND (   l.location_code <> 'BUS'
                          OR (    l.location_code = 'BUS'
                              AND l.primary_location_flag = 'Y'))
                     AND l.loc_exp_date IS NULL
                     AND upper(l.loc_addr1) NOT LIKE 'PO BOX%'
                     AND upper(l.loc_addr1) NOT LIKE 'P.O BOX%'
                     AND upper(l.loc_addr1) NOT LIKE 'P.O. BOX%'
                     AND upper(l.loc_addr1) NOT LIKE 'PO. BOX%'
                     AND upper(l.loc_addr1) NOT LIKE 'P O BOX%'
              ORDER BY CASE l.location_code
                          WHEN 'AUD' THEN '1'
                          WHEN 'PAY' THEN '2'
                          WHEN 'POL' THEN '3'
                          ELSE '4'
                       END)
       WHERE ROWNUM = 1;

      RETURN v_audit_loc_seq_num;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN NULL;
      WHEN OTHERS THEN
         pk73090_error_handler.raise_an_error( 'GetAuditLocationSeqNum: ' || in_policynumber || ',' || SQLERRM);
   END getauditlocationseqnum;

   /********************************************************************
   | Name             : insert_audit_notice_letter
   | Prepared By      : carade
   | Created          : 12/03/2008
   | Description      : Insert endorsement for audit notice letter.
   | Modification History
   | ------------------------------------------------------------------*/

Related documentation

• Procedure catalog
• Business rules
• Operations and processes
• SQL catalog

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

• Oracle DBA_SOURCE: pk73211_premium_audit.GetAuditLocationSeqNum
• Calling code: `analysis\oracle-source\pk73211_premium_audit.sql; clsAudit.vb