Skip to content

pk73094_policy_data

Overview

Field Value
Schema WCIS_DBA
Type Package
Database Oracle
Status ✅ found — retrieved via Legends CLI
Query executed legends oracle query --name pk73094_policy_data
Calling libraries SAIF.Business.View.Policy
Calling classes clsPolicyData

Purpose

pk73094_policy_data supplies the primary policy-header retrieval for policy view data objects. This library calls its getpolicy member; the package also exposes get_account_name, which is not invoked by this library.

Procedures / Methods

getpolicy

Field Value
Returns policy_cur (IN OUT)
Called by clsPolicyData.vb:2297 (RetrieveSQL)

Description: Opens the policy header cursor, including derived status and cancel status. Monetary header values are null-safe, cancel status is always the shared derivation (never a stored column), and SIC code is deliberately not returned by this view (see rules PR-BVP-017, PR-BVP-018, PR-BVP-019 in Business Rules below).

Parameters

Parameter Direction Type Default Description
apolicynum IN NUMBER — Policy number
recordset IN OUT policy_cur — Policy header result set

get_account_name

Field Value
Returns VARCHAR2 (function return value)
Called by not called by this library — exposed member documented for completeness

Description: Returns the "best" policy's legal name for an account number, chosen by a fixed ranking (see rules PR-BVP-020, PR-BVP-021 in Business Rules below).

Parameters

Parameter Direction Type Default Description
in_account_num IN VARCHAR2 — Account number
(return) RETURN VARCHAR2 — Best policy's legal_name, or NULL if in_account_num is NULL

Business Rules

Rules extracted from this package's logic:

Rule ID Rule Condition Action
PR-BVP-017 Monetary header values are null-safe current_balance / premium_estimate may be NULL Return NVL(current_balance,0) and NVL(premium_estimate,0)
PR-BVP-018 Cancel status on the policy header is always the shared derivation, never a stored column always Select sp72268_get_cancel_status(p.policy_num) as policy_cancel_status
PR-BVP-019 SIC code is deliberately not returned by this view always Project null as sic_code
PR-BVP-020 The "best" policy for an account is chosen by a fixed ranking multiple policies share an account number Order by status rank ('0','3'→1, '5'→2, else 3), then self-referencing-relationship first, then cancel_date DESC NULLS LAST, then policy_num; return that policy's legal_name
PR-BVP-021 A null account number yields no name in_account_num IS NULL Return NULL

These rules are documented in business-rules.md.

Operations

Operations and processes implemented in this package:

Operation Description Trigger
Retrieve policy header Opens the policy header cursor including derived status and cancel status clsPolicyData policy retrieval (clsPolicyData.vb:2297)
Resolve account name Returns the legal name of the best policy for an account number not invoked by this library

These operations are also listed in operations-processes.md with source reference back to this document.

Source Code

PL/SQL package specification 
PROCEDURE getpolicy (apolicynum IN     NUMBER,
                     recordset  IN OUT policy_cur);

FUNCTION  get_account_name (in_account_num IN VARCHAR2)
   RETURN VARCHAR2;
PL/SQL package body (rule-bearing excerpt) 
-- getpolicy: derived header columns
SELECT ...
       NVL (p.current_balance, 0)                      current_balance,
       NVL (p.premium_estimate, 0)                     premium_estimate,
       psc.code_descr                                  status,
       psc.policy_status_group_code,
       sp72268_get_cancel_status (p.policy_num)        policy_cancel_status,
       NULL                                            AS sic_code
  FROM policy p
  JOIN policy_status_code psc
    ON p.policy_status_code = psc.code
 WHERE p.policy_num = apolicynum;

Sources

  • Oracle DBA_SOURCE: WCIS_DBA.pk73094_policy_data
  • Legends CLI query executed 2026-08-31: legends oracle query --name pk73094_policy_data
  • Calling code: SAIF.Business.View.Policy/clsPolicyData.vb:2297