---
title: "SQL-001: Cancellation status derivation"
summary: Derives the cancellation-status text displayed in the policy header.
status: draft
owner: SAIF Documentation Generation
last_reviewed: 2026-07-31
library: SAIF.Application.Policy
reference_type: package
---

# SQL-001: Cancellation status derivation

This database call derives the cancellation or collection-status text that `clsStandardPolicyHeaderDataProvider` appends to the rendered policy-header status. The .NET code invokes `SP72268_GET_CANCEL_STATUS` as a stored-procedure-style scalar request, but the analysis shows the Oracle object itself is a standalone function that returns `VARCHAR2`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-001 |
| **Operation type** | Read |
| **Execution method** | Stored procedure-style scalar request to Oracle function |
| **Database** | Oracle |
| **Schema** | `WCIS_DBA`** |
| **Command type** | StoredProcedure |
| **Business object** | `N/A (Application layer)` |
| **Application class** | `clsStandardPolicyHeaderDataProvider` |
| **Criteria property** | `N/A (Application layer)` |
| **Lifecycle method** | `GetCancellationStatusDescription` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:in_policy_num` | Input | `NUMBER` | N/A | Policy number whose cancellation status is being derived. |
| `RetValue` | ReturnValue | `VARCHAR2` | 100 | Function return value bound through `ParameterDirection.ReturnValue`; the PL/SQL function returns up to 13 characters, but the .NET caller allocates 100. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [POLICY](../database-tables.md#policy) | SELECT | `POLICY_NUM`, `POLICY_STATUS_CODE`, `CURRENT_BALANCE` |
| [POLICY_STATUS_CODE](../database-tables.md#policy_status_code) | JOIN | `CODE`, `POLICY_STATUS_GROUP_CODE` |
| [POLICY_DEMAND](../database-tables.md#policy_demand) | SELECT | `POLICY_NUM`, `COLLECTION_ACT_RESPONSE_CODE` |
| [POLICY_PERIOD_PAYROLL_AUDIT](../database-tables.md#policy_period_payroll_audit) | SELECT | `POLICY_NUM`, `AUDIT_STATUS_CODE`, `AUDIT_PERIOD_START_DATE`, `AUDIT_CODE`, `PAYROLL_SEQ_NUM` |
| [AUDIT_CODE](../database-tables.md#audit_code) | JOIN | `CODE`, `AUDIT_TYPE_CODE` |
| [CANCELLATION](../database-tables.md#cancellation) | SELECT | `POLICY_NUM`, `CANCELLATION_SEQ_NUM`, `CANCEL_DATE` |
| [COLLECTION_LEGAL_ACTION](../database-tables.md#collection_legal_action) | SELECT | `POLICY_NUM`, `COLLECTION_ACTION_CODE`, `CANCEL_DATE` |

## Sample SQL

```sql
:RetValue := SP72268_GET_CANCEL_STATUS(
  in_policy_num => :in_policy_num)
```

> **Note:** Statements marked with `**` could not be confirmed; treat as unknown until verified.

## Sources
- `Application\SAIF.Application.Policy\PolicyHeader\clsStandardPolicyHeaderDataProvider.vb`