pk73102_policy

Overview

Field	Value
Schema	WCIS_DBA
Type	Package
Database	Oracle (resolved via the caller's IDataPortalInfo; not named in code)
Calling libraries	SAIF.Business.Policy
Calling classes	PolicyInformation

Purpose

Package pk73102_policy exposes submission-status and ownership operations for policies, including reject_submission, unreject_submission, set_submission_to_pending, set_submission_to_quoted, set_app_received_date, is_pms_status, is_pms_status_yn, and insert_dividend_notepad. Only one member, the does_wcis_own_submission function (not a procedure), is invoked by SAIF.Business.Policy — it determines whether WCIS owns a policy's submission based on the policy's current submission-status code.

Procedures / Methods

does_wcis_own_submission

Field	Value
Returns	VARCHAR2 — package function, not a procedure
Called by	PolicyInformation.GetPolicyOwnershipSystem — SAIF.Business.Policy/PolicyInformation.vb:249–274

Description: Returns the Y/N wcis_owns_flag for the policy's current submission status, joining submission_status_code to sub_policy on policy_num. Defaults to 'Y' (WCIS-owned) when no matching row is found or on any other error. The BOF call site is intentionally a CommandType.Text scalar SELECT … FROM dual wrapper (PolicyInformation.vb:258) — the commented-out StoredProcedure form is retained on line 257 as historical context only.

Parameters

Parameter	Direction	Type	Default	Description
in_policy_num	IN	sub_policy.policy_num%TYPE (anchored numeric)	—	Policy number whose submission ownership is being resolved. Bound by the .NET caller from inPolicyNumber As Decimal (PolicyInformation.vb:249, bound at line 261).
(return value)	RETURN	VARCHAR2 (1 character; body assigns into local v_wcis_owns_ind VARCHAR2(1) sourced from submission_status_code.wcis_owns_flag)	—	Y/N flag indicating whether WCIS owns the submission. Consumed by the caller as PortalRequest.DataRequest(0).Result.ToString.ToBoolean (PolicyInformation.vb:268).

Business Rules

Rules extracted from this procedure's logic:

Rule ID	Rule	Condition	Action
PR-BP-006	WCIS ownership of a policy submission is determined by the wcis_owns_flag on the submission-status code currently assigned to the policy's sub-policy record.	Always evaluated for the supplied in_policy_num	SELECT ssc.wcis_owns_flag from submission_status_code ssc joined to sub_policy s on s.submission_status_code = ssc.code where s.policy_num = in_policy_num; return that flag.
PR-BP-007	When ownership cannot be determined — no matching sub-policy/status row, or any other error — the policy defaults to being owned by WCIS.	WHEN NO_DATA_FOUND or WHEN OTHERS	RETURN 'Y' in both handlers. This fail-open default means a missing sub_policy row is indistinguishable from a genuine WCIS-owned policy, and the .NET caller consequently returns Enums.PolicyOwnershipSystems.WCIS (PolicyInformation.vb:268–272).

These rules are documented in business-rules.md. Newly introduced rules must be added to business-rules.md as part of the same writing task.

Operations

Operations and processes implemented in this procedure:

Operation	Description	Trigger
Determine submission ownership system	Returns the Y/N wcis_owns_flag for the policy's current submission status, defaulting to 'Y' on any failure.	PolicyInformation.GetPolicyOwnershipSystem invoked with a policy number (documented as OP-11); executed as a scalar SELECT … FROM dual.

These operations are also listed in operations-processes.md with source reference back to this document.

Source Code

PL/SQL package specification

FUNCTION does_wcis_own_submission(in_policy_num IN sub_policy.policy_num%TYPE)
   RETURN VARCHAR2;

PL/SQL package body

FUNCTION does_wcis_own_submission(in_policy_num IN sub_policy.policy_num%TYPE)
      RETURN VARCHAR2
   IS
      v_wcis_owns_ind   VARCHAR2(1);
   BEGIN
      SELECT wcis_owns_flag
        INTO v_wcis_owns_ind
        FROM submission_status_code ssc
           , sub_policy s
       WHERE s.submission_status_code = ssc.code
             AND s.policy_num = in_policy_num;

      RETURN v_wcis_owns_ind;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN 'Y';
      WHEN OTHERS THEN
         RETURN 'Y';
   END does_wcis_own_submission;

.NET invocation (as executed by BOF):

SELECT pk73102_policy.does_wcis_own_submission(:in_policy_num)
  FROM dual

Related Documentation

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

Sources

• Oracle DBA_SOURCE: WCIS_DBA.pk73102_policy
• Legends CLI query executed 2026-08-31: legends oracle query --name pk73102_policy
• Calling code: SAIF.Business.Policy/PolicyInformation.vb:249–274