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

PL/SQL package or method excerpt

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

Related documentation

• Procedure catalog
• Business rules
• Operations and processes
• SQL catalog

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