SP72268_GET_CANCEL_STATUS

Overview

Field	Value
Schema	WCIS_DBA
Type	Standalone function referenced from .NET as a stored procedure
Database	Oracle**
Calling libraries	SAIF.Application.Policy
Calling classes	clsStandardPolicyHeaderDataProvider

Purpose

Derive the human-readable cancellation or collection-status text appended to the policy-header status description.

Procedures / Methods

SP72268_GET_CANCEL_STATUS

Field	Value
Returns	VARCHAR2 status text
Called by	clsStandardPolicyHeaderDataProvider.GetCancellationStatusDescription
SQL document	SQL-001

Description: Returns the derived cancellation-status text for one policy number.

Parameters

Parameter	Direction	Type	Default	Description
in_policy_num	IN	NUMBER	—	Policy number being evaluated.

Business Rules

Rule ID	Rule	Condition	Action
PROC-RULE-001	Cancellation status is derived only for PENDCA, CANCEL, or APP status groups	POLICY_STATUS_GROUP_CODE is PENDCA, CANCEL, or APP	Execute only the matching branch; all other status groups return no cancellation status
PROC-RULE-002	Pending-cancel policies can only derive Demanded	Status group is PENDCA	Run only the demand-status check
PROC-RULE-003	Cancelled-policy status derivation uses strict first-match precedence	Status group is CANCEL	Evaluate judgment, lien, defaulted, demanded, pending audit, pending payroll report, and closed-with-balance in order
PROC-RULE-004	Open judgment action yields Judgment	COLLECTION_ACTION_CODE = '018' and CANCEL_DATE IS NULL	Set the status to Judgment
PROC-RULE-005	Open lien action yields Lien	COLLECTION_ACTION_CODE IN ('016','017') and CANCEL_DATE IS NULL	Set the status to Lien
PROC-RULE-006	Demand response D yields Defaulted	A POLICY_DEMAND row exists with COLLECTION_ACT_RESPONSE_CODE = 'D'	Set the status to Defaulted
PROC-RULE-007	Null demand response yields Demanded	A POLICY_DEMAND row exists with COLLECTION_ACT_RESPONSE_CODE IS NULL	Set the status to Demanded
PROC-RULE-008	Only physical outstanding audits before cancel date yield Pending Audit	Matching audit rows are A or R, start before the latest cancel date, and use physical audit codes	Set the status to Pending Audit
PROC-RULE-009	Audit or payroll periods on or after the cancel date are ignored	AUDIT_PERIOD_START_DATE >= cancel date	Exclude the row from pending-audit and pending-payroll-report derivation
PROC-RULE-010	Non-physical outstanding payroll reports yield Pending PR	Matching rows are A, I, or R, start before the latest cancel date, and are not physical audits	Set the status to Pending PR
PROC-RULE-011	Non-zero current balance yields Closed w/Bal	NVL(POLICY.CURRENT_BALANCE, 0) <> 0	Set the status to Closed w/Bal
PROC-RULE-012	Cancelled policies with no earlier match become Closed	All earlier cancelled-policy checks leave the status null	Set the status to Closed
PROC-RULE-013	Unowned applications become saifQuote	Status group is APP and pk73102_policy.does_wcis_own_submission(in_policy_num) = 'N'	Set the status to saifQuote
PROC-RULE-014	Unexpected errors raise tagged Oracle application error -20002	WHEN OTHERS handler runs	Raise RAISE_APPLICATION_ERROR(-20002, 'Error in sp72268: ' \|\| SQLERRM)

These rules are also listed under ## Stored procedure business rules in business-rules.md.

Operations

Operation	Description	Trigger
PROC-OP-01	Load policy status context from POLICY and POLICY_STATUS_CODE	Function invoked with in_policy_num
PROC-OP-02	Derive pending-cancel status from unanswered demand rows	Policy status group is PENDCA
PROC-OP-03	Derive cancelled-policy status from judgment, lien, demand, audit, payroll-report, and balance checks	Policy status group is CANCEL
PROC-OP-04	Derive saifQuote status for applications not owned by WCIS	Policy status group is APP
PROC-OP-05	Return the derived status text to the .NET caller	Branch evaluation completes

These operations are also listed under ## Stored procedure operations in operations-processes.md.

Source Code

PL/SQL function source

-- Actual PL/SQL source retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE 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 */

Notes

• The .NET caller dispatches this object as CommandType.StoredProcedure, but the database object itself is an Oracle function that returns VARCHAR2.

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

Related documentation

• Procedures
• Business rules
• Operations and processes
• Database tables
• SQL catalog

Sources

• Oracle DBA_SOURCE: WCIS_DBA.SP72268_GET_CANCEL_STATUS
• Application\SAIF.Application.Policy\PolicyHeader\clsStandardPolicyHeaderDataProvider.vb