---
title: "sp72268_get_cancel_status stored procedure documentation"
summary: "Analysis of the Oracle function called by SAIF.Business.View.Policy, including business rules and operations."
status: draft
owner: "SAIF Doc Gen"
last_reviewed: "2026-07-31"
source_schema: "WCIS_DBA"
source_name: "sp72268_get_cancel_status"
source_type: "FUNCTION"
oracle_status: "found"
library: SAIF.Business.View.Policy
reference_type: package
---

# sp72268_get_cancel_status

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Function (standalone) |
| **Database** | Oracle (TNS alias not declared in this library's source)** |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsPolicyData` |

## Purpose

Derives the human-readable collection and cancellation status shown as a policy's secondary status.

## Procedures / Methods

### sp72268_get_cancel_status

| Field | Value |
| --- | --- |
| **Returns** | VARCHAR2 — derived status text |
| **Called by** | `clsPolicyData.GetSecondaryPolicyStatus` |
| **SQL document** | [SQL-020](../sql/SQL-020-secondary-policy-status.md) |

**Description:** Returns the collection and cancellation condition displayed as a policy's secondary status.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Policy number being read. |

## Business Rules

Rules extracted from this function's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| [PROC-RULE-018](../business-rules.md#stored-procedure-business-rules) | Cancellation and collection status is derived from a fixed priority order gated by the policy's status group | `policy_status_group_code IN ('PENDCA','CANCEL','APP')` | Evaluates demand, judgment, lien, default, pending-audit, pending-payroll-report, and closed-with-balance conditions in order, or returns `saifQuote` for unowned submissions |

> These rules are also listed under `## Stored procedure business rules` in [business-rules.md](../business-rules.md).

## Operations

Operations implemented in this function:

| Operation | Description | Trigger |
| --- | --- | --- |
| Derive secondary cancel status | Derives a human-readable collection and cancellation status for a policy | `clsPolicyData.GetSecondaryPolicyStatus` |

> These operations are also listed under `## Stored procedure operations` in [operations-processes.md](../operations-processes.md).

## Source Code

<details>
<summary>PL/SQL source</summary>

```sql
-- Retrieved from the Oracle data dictionary for WCIS_DBA.sp72268_get_cancel_status
FUNCTION SP72268_GET_CANCEL_STATUS
/****************************************************************
*Procedure Title  : Get cancel status.
*Name             : SP72268_GET_CANCEL_STATUS
*Called by        : forms
*****************************************************************
*PROGRAMMER     ANALYST                 DATE       MOD #
*CARADE         CARADE              01/17/2001     Combo 5.4.1; csr 8
*Description:    This stored procedure will derive the cancellation status
*               for a policy.
*
* INPUTS:          policy number
* RETURNS:         cancel status - varchar2(13)
*
* Maintenance Log:
*   Release         Name    Date      Description
*   7.0.3     genhyd  06/15/2006 - Changed to use the policy_demand table instead of policy_demand_action.
*   7.0.3      valrey  05/22/2006 - Enhanced the collection status.
*   7.0.2     bricot  02/11/2005 - remove references to payroll summary
*                       and audit schedule tables
 * Retro Cancel 5.4.1 Carade 02/26/01  Added derivation of demand and
*                                     default statuses.
*  7.0.3 CSR86 leedas  08/24/2006 - Corrected the spelling Judgment used in look_for_judgement.
*  OSQ         leedas  02/07/2008 - Changed to also derive saifQuote if the wcis system did not own the
*                                   application
***********************************************************************************************/

    ( in_policy_num                 IN       NUMBER)
    RETURN VARCHAR2

IS
/*******************   Local Variables  *************************/

v_cancel_status     VARCHAR2(13);
v_count                NUMBER;

CURSOR pol_cur IS
SELECT pol.POLICY_STATUS_CODE,
       pol.current_balance,
       psc.policy_status_group_code
FROM POLICY pol,
    POLICY_STATUS_CODE psc
WHERE pol.policy_num = in_policy_num
  AND pol.POLICY_STATUS_CODE = psc.code;

pol_rec pol_cur%ROWTYPE;

/********************************************************************/
/***********************   Local Procedures *************************/
/********************************************************************/
    PROCEDURE look_for_default_status
    /* derive the default status */
        IS
        CURSOR default_csr IS
        SELECT policy_num
        FROM POLICY_DEMAND
        WHERE policy_num = in_policy_num
          AND COLLECTION_ACT_RESPONSE_CODE = 'D';

        default_rec default_csr%ROWTYPE;

    BEGIN
        IF default_csr%isopen THEN
            CLOSE default_csr;
        END IF;
        OPEN default_csr;
        FETCH default_csr INTO default_rec;
        IF default_csr%FOUND THEN
            v_cancel_status := 'Defaulted';
        END IF;
        CLOSE default_csr;

    END; /* look_for_default_status */


    PROCEDURE look_for_demand_status
    /* derive the demand status */
        IS
        CURSOR demand_csr IS
        SELECT policy_num
        FROM POLICY_DEMAND
        WHERE policy_num = in_policy_num
          AND COLLECTION_ACT_RESPONSE_CODE IS NULL;

        demand_rec demand_csr%ROWTYPE;

    BEGIN
        IF demand_csr%isopen THEN
            CLOSE demand_csr;
        END IF;
        OPEN demand_csr;
        FETCH demand_csr INTO demand_rec;
        IF demand_csr%FOUND THEN
            v_cancel_status := 'Demanded';
        END IF;
        CLOSE demand_csr;

    END; /* look_for_demand_status */


    PROCEDURE look_for_pending_audit_status
    /* derive the pending audit status */
        IS
        /* Look for assigned or received audit with an effective date
           prior to the cancel date. If the
           cancel date is the same as the audit start date
           then there was no coverage and we don't care about any
           pending audits. Only physical audits are considered. */

        CURSOR au_cur IS
        SELECT policy_num
        FROM POLICY_PERIOD_PAYROLL_AUDIT pppa,
             AUDIT_CODE ac
        WHERE pppa.policy_num = in_policy_num
          AND pppa.AUDIT_STATUS_CODE IN ('A', 'R')
          AND pppa.audit_period_start_date <
                    (SELECT cancel_date
                     FROM CANCELLATION can
                     WHERE policy_num = in_policy_num
                       AND cancellation_seq_num =
                            (SELECT MAX(cancellation_seq_num)
                             FROM CANCELLATION can2
                             WHERE policy_num = in_policy_num))
          AND pppa.AUDIT_CODE = ac.code
          AND ac.AUDIT_TYPE_CODE IN ('PHYS', 'PHYSR');

        au_rec au_cur%ROWTYPE;

    BEGIN
        IF au_cur%isopen THEN
            CLOSE au_cur;
        END IF;

        OPEN au_cur;

        FETCH au_cur INTO au_rec;

        IF au_cur%FOUND THEN
            v_cancel_status := 'Pending Audit';
        END IF;

        CLOSE au_cur;

    END; /* look_for_pending_audit_status */


    PROCEDURE look_for_pending_pr_status
    /* derive the pending payroll report status */
        IS

        /* Look for initial or pending payroll reports for reporting periods
           with a start date prior to the cancel date. If the
           cancel date is the same as the period start date
           then there was no coverage and we don't care about any
           pending payroll reports as of the cancel date.  The payroll
           reports selected are for other than physical audits. */

        CURSOR pr_cur IS
        SELECT policy_num
        FROM POLICY_PERIOD_PAYROLL_AUDIT pppa
        WHERE policy_num = in_policy_num
          AND AUDIT_STATUS_CODE IN ('A','I','R')
          AND audit_period_start_date <
                    (SELECT cancel_date
                     FROM CANCELLATION can
                     WHERE policy_num = in_policy_num
                       AND cancellation_seq_num =
                            (SELECT MAX(cancellation_seq_num)
                             FROM CANCELLATION can2
                             WHERE policy_num = in_policy_num))
          AND NOT EXISTS
                    (SELECT 1
                     FROM POLICY_PERIOD_PAYROLL_AUDIT pppa1,
                          AUDIT_CODE ac
                     WHERE pppa1.payroll_seq_num = pppa.payroll_seq_num
                     AND pppa1.AUDIT_CODE = ac.code
                     AND ac.AUDIT_TYPE_CODE IN ('PHYS', 'PHYSR'));

        pr_rec pr_cur%ROWTYPE;

    BEGIN
        IF pr_cur%isopen THEN
            CLOSE pr_cur;
        END IF;

        OPEN pr_cur;

        FETCH pr_cur INTO pr_rec;

        IF pr_cur%FOUND THEN
            v_cancel_status := 'Pending PR';
        END IF;

        CLOSE pr_cur;

    END; /* look_for_pending_pr_status */


    PROCEDURE look_for_closed_bal_status
    /* derive the pending payroll report status */
        IS
    BEGIN
        /* The policy balance was retrieved with the policy cursor. */

        IF NVL(pol_rec.current_balance,0) = 0 THEN
            NULL;
        ELSE
            v_cancel_status := 'Closed w/Bal';
        END IF;

    END; /* look_for_closed_bal_status */


    PROCEDURE look_for_judgement IS
    BEGIN
         SELECT COUNT(*)
           INTO v_count
           FROM COLLECTION_LEGAL_ACTION cla
           WHERE cla.POLICY_NUM = in_policy_num
             AND cla.COLLECTION_ACTION_CODE = '018'
             AND cla.CANCEL_DATE IS NULL;

         IF v_count > 0 THEN
             v_cancel_status := 'Judgment';
         END IF;
    END;

    PROCEDURE look_for_lien IS
    BEGIN
         SELECT COUNT(*)
           INTO v_count
           FROM COLLECTION_LEGAL_ACTION cla
           WHERE cla.POLICY_NUM = in_policy_num
             AND cla.COLLECTION_ACTION_CODE IN ('016','017')
             AND cla.CANCEL_DATE IS NULL;

         IF v_count > 0 THEN
             v_cancel_status := 'Lien';
         END IF;
    END;



    PROCEDURE pending_cancel_status
    /* derive the cancel status for a pending cancel policy */
        IS
    BEGIN
        look_for_demand_status;
    END; /* pending_cancel_status */


    PROCEDURE cancel_status
    /* derive the cancel status for a cancelled policy */
        IS
    BEGIN

        look_for_judgement;

        IF v_cancel_status IS NULL THEN
           look_for_lien;
        END IF;

        IF v_cancel_status IS NULL THEN
           look_for_default_status;
        END IF;

        IF v_cancel_status IS NULL THEN
            look_for_demand_status;
        END IF;

        IF v_cancel_status IS NULL THEN
            look_for_pending_audit_status;
        END IF;

        IF v_cancel_status IS NULL THEN
            look_for_pending_pr_status;
        END IF;

        IF v_cancel_status IS NULL THEN
            look_for_closed_bal_status;
        END IF;

        /* If no other status was derived then set it closed */
        IF v_cancel_status IS NULL THEN
            v_cancel_status := 'Closed';
        END IF;

    END; /* cancel_status */

   PROCEDURE saifquote_status
    /* derives saifQuote status for an application */
   IS
    BEGIN

		IF pk73102_policy.does_wcis_own_submission(in_policy_num) = 'N' AND v_cancel_status IS NULL THEN
           v_cancel_status := 'saifQuote';
        END IF;


    END; /* saifquote_status */


/********************************************************************/
/***********************   Main Program     *************************/
/********************************************************************/
BEGIN

IF pol_cur%isopen THEN
    CLOSE pol_cur;
END IF;

OPEN pol_cur;
FETCH pol_cur INTO pol_rec;
CLOSE pol_cur;

IF pol_rec.policy_status_group_code = 'PENDCA' THEN
    pending_cancel_status;
ELSIF pol_rec.policy_status_group_code = 'CANCEL' THEN
        cancel_status;
ELSIF pol_rec.policy_status_group_code = 'APP' THEN
        saifquote_status;
END IF;

RETURN v_cancel_status;

EXCEPTION
     WHEN OTHERS THEN
         RAISE_APPLICATION_ERROR(-20002,'Error in sp72268: '
                                  || SQLERRM);

END;   /*SP72268_GET_CANCEL_STATUS */
```

</details>

> **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.

## Related documentation

- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

## Sources

- `Business/SAIF.Business.View.Policy/clsPolicyData.vb`
- Oracle data dictionary source for `WCIS_DBA.sp72268_get_cancel_status`