sp72268_get_cancel_status¶

Overview¶

Field	Value
Schema	`WCIS_DBA`
Type	Standalone function
Database	Oracle
Calling libraries	`SAIF.Business.View.Policy`
Calling classes	`clsPolicyData`

Purpose¶

sp72268_get_cancel_status derives a human-readable cancellation or collection status for a policy by combining policy status-group data with collection, audit, payroll-report, balance, and submission-ownership checks.

Procedures / Methods¶

sp72268_get_cancel_status¶

Field	Value
Returns	`VARCHAR2`
Called by	`clsPolicyData` ad hoc retrieval and `pk73094_policy_data.getpolicy`
Description	Returns the derived cancellation-status label for one policy number.

Parameters¶

Parameter	Direction	Type	Default	Description
`in_policy_num`	IN	`NUMBER`	—	Policy number to evaluate.

Business Rules¶

The retrieved database source embeds the following server-side rules.

Rule ID	Summary
BR-VP-029	Policies in status group `PENDCA` run only the demand-status branch.
BR-VP-030	Cancelled policies evaluate Judgment, Lien, Defaulted, Demanded, Pending Audit, Pending PR, Closed w/Bal, then Closed.
BR-VP-031	`Defaulted` requires response code `D`; `Demanded` requires a policy-demand row with a null response code.
BR-VP-032	Status group `APP` yields `saifQuote` only when `pk73102_policy.does_wcis_own_submission(...) = 'N'`.

BR-VP-029 Pending-cancel policies only derive the demanded status¶

Field	Value
Statement	When the policy status group is `PENDCA`, the function derives cancellation status only by checking for a demand row with `COLLECTION_ACT_RESPONSE_CODE IS NULL`.
Trigger	`SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `PENDCA`.
Enforcement	The main program routes `PENDCA` to `pending_cancel_status`, which calls only `look_for_demand_status`.
Outcome	A pending-cancel policy returns `Demanded` when an open demand exists; otherwise it returns null.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

BR-VP-030 Cancelled policies use a fixed precedence order for derived collection statuses¶

Field	Value
Statement	For cancelled policies, the function derives collection status in a fixed order: Judgment, Lien, Defaulted, Demanded, Pending Audit, Pending PR, Closed w/Bal, and finally Closed.
Trigger	`SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `CANCEL`.
Enforcement	`cancel_status` calls the local derivation procedures only while `v_cancel_status` remains null, then defaults to `Closed`.
Outcome	The first matching collection condition determines the returned cancellation status.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

BR-VP-031 Defaulted and demanded statuses come from policy-demand response codes¶

Field	Value
Statement	The function derives `Defaulted` from a `POLICY_DEMAND` row whose `COLLECTION_ACT_RESPONSE_CODE = 'D'` and derives `Demanded` from a `POLICY_DEMAND` row whose response code is null.
Trigger	`look_for_default_status` or `look_for_demand_status` executes.
Enforcement	Each local procedure opens a cursor over `POLICY_DEMAND` with its respective response-code filter and assigns the corresponding status when a row is found.
Outcome	Demand and default statuses are driven directly by the collection-action response state.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

BR-VP-032 Application policies return saifQuote only when WCIS does not own the submission¶

Field	Value
Statement	For application-status policies, the function returns `saifQuote` only when `pk73102_policy.does_wcis_own_submission(in_policy_num) = 'N'` and no prior status has been assigned.
Trigger	`SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `APP`.
Enforcement	The main program routes `APP` to `saifquote_status`, which tests `pk73102_policy.does_wcis_own_submission` before assigning `v_cancel_status := 'saifQuote'`.
Outcome	Application policies are labeled `saifQuote` only for non-WCIS-owned submissions.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

Operations¶

The retrieved database source implements the following internal operations.

Operation	Description	Trigger
Derive pending-cancel demand status	Check open demand rows and return `Demanded` for pending-cancel policies.	Policy status group is `PENDCA`.
Derive cancelled-policy collection status	Evaluate collection, audit, payroll-report, and balance conditions in precedence order for cancelled policies.	Policy status group is `CANCEL`.
Derive application saifQuote status	Return `saifQuote` for application policies not owned by WCIS.	Policy status group is `APP`.

Derive pending-cancel demand status¶

Field	Value
Description	Check open demand rows and return `Demanded` for pending-cancel policies.
Trigger	Policy status group is `PENDCA`.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

Derive cancelled-policy collection status¶

Field	Value
Description	Evaluate collection, audit, payroll-report, and balance conditions in precedence order for cancelled policies.
Trigger	Policy status group is `CANCEL`.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

Derive application saifQuote status¶

Field	Value
Description	Return `saifQuote` for application policies not owned by WCIS.
Trigger	Policy status group is `APP`.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

Calling code references¶

Class	Method	Source File
`clsPolicyData`	`ad hoc method`	`SAIF.Business.View.Policy\clsPolicyData.vb`

Source Code¶

PL/SQL function source

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

Sources¶

Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json
Calling code: SAIF.Business.View.Policy\clsPolicyData.vb
Calling package: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json

sp72268_get_cancel_status¶

Overview¶

Purpose¶

Procedures / Methods¶

sp72268_get_cancel_status¶

Parameters¶

Business Rules¶

BR-VP-029 Pending-cancel policies only derive the demanded status¶

BR-VP-030 Cancelled policies use a fixed precedence order for derived collection statuses¶

BR-VP-031 Defaulted and demanded statuses come from policy-demand response codes¶

BR-VP-032 Application policies return saifQuote only when WCIS does not own the submission¶

Operations¶

Derive pending-cancel demand status¶

Derive cancelled-policy collection status¶

Derive application saifQuote status¶

Calling code references¶

Source Code¶

Related documentation¶

Sources¶