Skip to content

sp72268_get_cancel_status

Overview

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

Purpose

sp72268_get_cancel_status derives a display cancel status for a policy from its status group, demand history, audit state, and open collection legal actions. It is called standalone by clsPolicyData and also from inside pk73094_policy_data.getpolicy (PR-BVP-018).

Procedures / Methods

sp72268_get_cancel_status

Field Value
Returns VARCHAR2 (function return value)
Called by clsPolicyData cancel-status retrieval (clsPolicyData.vb:2320, used at 1728); also called inside pk73094_policy_data.getpolicy

Description: Dispatches on the policy status group to derive a cancel-status label, covering pending-cancel, cancelled, and application-status branches (see rules PR-BVP-001 through PR-BVP-011 in Business Rules below).

Parameters

Parameter Direction Type Default Description
in_policy_num IN NUMBER — Policy number
(return) RETURN VARCHAR2 — Cancel status label

Business Rules

Rules extracted from this function's logic:

Rule ID Rule Condition Action
PR-BVP-001 Cancel-status derivation is dispatched on the policy status group POLICY_STATUS_CODE.policy_status_group_code = PENDCA Evaluate pending_cancel_status (demand check only)
PR-BVP-002 Cancelled policies use the full cancel-status precedence chain status group = CANCEL Evaluate cancel_status in order: Judgment → Lien → Defaulted → Demanded → Pending Audit → Pending PR → Closed with Balance → default Closed
PR-BVP-003 A policy is Judgment status when a judgment legal action is open COLLECTION_LEGAL_ACTION.COLLECTION_ACTION_CODE = '018' AND CANCEL_DATE IS NULL Return Judgment status
PR-BVP-004 A policy is Lien status when an open lien legal action exists COLLECTION_LEGAL_ACTION.COLLECTION_ACTION_CODE IN ('016','017') AND CANCEL_DATE IS NULL Return Lien status
PR-BVP-005 A policy is Defaulted when the demand response indicates default POLICY_DEMAND.COLLECTION_ACT_RESPONSE_CODE = 'D' Return Defaulted status
PR-BVP-006 A policy is Demanded when a demand exists with no response recorded POLICY_DEMAND.COLLECTION_ACT_RESPONSE_CODE IS NULL Return Demanded status
PR-BVP-007 A policy is Pending Audit when a physical audit is outstanding before cancellation POLICY_PERIOD_PAYROLL_AUDIT.AUDIT_STATUS_CODE IN ('A','R') AND audit_period_start_date < cancel_date (latest cancellation_seq_num) AND AUDIT_CODE.AUDIT_TYPE_CODE IN ('PHYS','PHYSR') Return Pending Audit status
PR-BVP-008 A policy is Pending PR when an audit is outstanding but is not a physical audit AUDIT_STATUS_CODE IN ('A','I','R') AND NOT EXISTS a matching physical audit Return Pending PR status
PR-BVP-009 A cancelled policy carrying a balance is reported as Closed with Balance NVL(pol_rec.current_balance, 0) <> 0 Return Closed-with-Balance status; otherwise default Closed
PR-BVP-010 Application-status policies not owned by WCIS are reported as saifQuote status group = APP AND pk73102_policy.does_wcis_own_submission(in_policy_num) = 'N' AND derived cancel status IS NULL Return saifQuote
PR-BVP-011 Unhandled errors are surfaced as an application error, not silently swallowed EXCEPTION WHEN others RAISE_APPLICATION_ERROR(-20002, 'Error in sp72268: ' \|\| SQLERRM)

These rules are documented in business-rules.md.

Operations

Operations and processes implemented in this function:

Operation Description Trigger
Derive cancel status Derives a display cancel status from policy status group and collection/audit state clsPolicyData cancel-status retrieval (clsPolicyData.vb:1728, SQL at 2320); also called inside pk73094_policy_data.getpolicy

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

Source Code

PL/SQL function (specification and body) 
FUNCTION sp72268_get_cancel_status (in_policy_num IN NUMBER)
   RETURN VARCHAR2
IS
BEGIN
   -- dispatch on policy status group
   IF v_status_group = 'PENDCA' THEN
      v_result := pending_cancel_status;      -- demand check only
   ELSIF v_status_group = 'CANCEL' THEN
      v_result := cancel_status;              -- Judgment -> Lien -> Defaulted ->
                                              -- Demanded -> Pending Audit ->
                                              -- Pending PR -> Closed w/Bal -> Closed
   ELSIF v_status_group = 'APP' THEN
      IF pk73102_policy.does_wcis_own_submission (in_policy_num) = 'N'
         AND v_cancel_status IS NULL
      THEN
         v_result := 'saifQuote';
      END IF;
   END IF;

   RETURN v_result;
EXCEPTION
   WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR (-20002, 'Error in sp72268: ' || SQLERRM);
END;
Call site (`clsPolicyData.vb:2320`): 
select sp72268_get_cancel_status(:in_policy_num) from dual

Sources

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