---
title: "pk73057_billing_master.is_on_arrears_plan stored procedure documentation"
summary: "Analysis of Oracle package/procedure including business rules and operations."
status: draft
owner: "Technical Writer"
last_reviewed: "2026-07-31"
library: SAIF.Business.Policy.Audit.Premium
reference_type: package
source_schema: "`WCIS_DBA`"
source_name: "pk73057_billing_master.is_on_arrears_plan"
source_type: "Package function"
oracle_status: "found"
---

# pk73057_billing_master.is_on_arrears_plan

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Package function |
| **Database** | Oracle / WCIS |
| **Calling libraries** | `SAIF.Business.Policy.Audit.Premium` |
| **Calling classes** | `clsAuditPolicyCollection` |

## Purpose

Walks billing master → billing defaults → payment plan type and reports `'Y'` unless the plan generates instalment or pre-pay billing

## Procedures / Methods

### is_on_arrears_plan

| Field | Value |
| --- | --- |
| **Returns** | See Oracle signature / caller behavior |
| **Called by** | `GetArrearsFlag` (SQL-035) |

**Description:** Walks billing master → billing defaults → payment plan type and reports `'Y'` unless the plan generates instalment or pre-pay billing

## Business Rules

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PROC-RULE-039 | A policy is on an arrears plan unless its payment plan generates instalment or pre-pay billing | `v_generate_billing_code NOT IN ('INST','PPAY')` | `RETURN 'Y'`, else `RETURN 'N'` |
| PROC-RULE-040 | The plan is resolved through the billing-default chain, not stored on the policy | `billing_master → billing_defaults → payment_plan_type_code` joined on `billing_default_code` and `payment_plan_type` | `generate_billing_code` is read from `payment_plan_type_code` |
| PROC-RULE-041 | A policy period with no billing master is treated as *not* on arrears | `WHEN NO_DATA_FOUND` | `RETURN 'N'` |

## Operations

| Operation | Description | Trigger |
| --- | --- | --- |
| Determine whether a policy period bills in arrears | Walks billing master → billing defaults → payment plan type and reports `'Y'` unless the plan generates instalment or pre-pay billing | `clsAuditPolicyCollection.GetArrearsFlag` (SQL-035); OP-008 |

## Source Code

<details>
<summary>PL/SQL package or method excerpt</summary>

```sql
   FUNCTION is_on_arrears_plan(in_policy_num               IN NUMBER
                             , in_policy_period_eff_date   IN DATE)
      RETURN VARCHAR2
   IS
      v_generate_billing_code   payment_plan_type_code.generate_billing_code%TYPE;
   BEGIN
      SELECT pptc.generate_billing_code
        INTO v_generate_billing_code
        FROM billing_master bm
           , billing_defaults bd
           , payment_plan_type_code pptc
       WHERE     bm.policy_num = in_policy_num
             AND bm.policy_period_eff_date = in_policy_period_eff_date
             AND bm.billing_default_code = bd.billing_default_code
             AND bd.payment_plan_type = pptc.code;

      IF v_generate_billing_code NOT IN ('INST', 'PPAY') THEN
         RETURN 'Y';
      ELSE
         RETURN 'N';
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN 'N';
      WHEN OTHERS THEN
         pk73090_error_handler.raise_an_error(
            'is_on_arrears_plan ' || in_policy_num || ',' || in_policy_period_eff_date || ',' || SQLERRM);
   END is_on_arrears_plan;

   /***********************************************************
    * PROCEDURE NAME : create_osc_billing_history
    * Create billing history for osc
    *
    * *****************************************************************
    * PROGRAMMER     ANALYST     DATE       MOD #
    *************************************************************/
```

</details>

## Related documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)



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

## Sources
- Oracle DBA_SOURCE: pk73057_billing_master.is_on_arrears_plan
- Calling code: ``analysis\oracle-source\pk73057_billing_master.sql`; `clsAuditPolicyCollection.vb`