---
title: "pk72846_view_policy stored procedure documentation"
summary: "Analysis of the Oracle package called by SAIF.Business.View.Policy, including business rules and operations."
status: draft
owner: "SAIF Doc Gen"
last_reviewed: "2026-07-31"
source_schema: "WCIS_DBA"
source_name: "pk72846_view_policy"
source_type: "PACKAGE"
oracle_status: "found"
library: SAIF.Business.View.Policy
reference_type: package
---

# pk72846_view_policy

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Package |
| **Database** | Oracle (TNS alias not declared in this library's source)** |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsPolicyData`, `clsAgency`, `clsPolicyAddress`, `clsPolicyPeriodCollection`, `clsAffiliatedPolicyCollection`, `clsTeamAssignmentCollection` |

## Purpose

Primary policy-view package supplying detail, agency, address, period, affiliate-list, gap, wrap-up, parent-policy, and team-assignment reads.

## Procedures / Methods

### getviewdetail

| Field | Value |
| --- | --- |
| **Returns** | REF CURSOR — one policy detail row |
| **Called by** | `clsPolicyData.RetrievePolicyDetail` |

**Description:** Returns the extended policy detail block: group and plan descriptions, gap indicator, combination flags, governing class, and continuous-coverage date. Values that must not be shown for a reduced-form period are masked here rather than in .NET.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number being read. |
| `recordset` | IN OUT | `viewpolicydetail_cursor` | — | Result set returned to the caller. |

### getwrapuplegalname

| Field | Value |
| --- | --- |
| **Returns** | VARCHAR2 — resolved legal name |
| **Called by** | `clsPolicyData.GetWrapupLegalName` |
| **SQL document** | [SQL-021](../sql/SQL-021-wrapup-legal-name.md) |

**Description:** Returns the legal name to display for a wrap-up policy by walking the wrap-up relationship and then the rate-account chain, keeping the last name it can resolve.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Policy number being read. |

### getparentpolicy

| Field | Value |
| --- | --- |
| **Returns** | NUMBER — parent policy number |
| **Called by** | `clsPolicyData.GetParentPolicyData` |
| **SQL document** | [SQL-019](../sql/SQL-019-parent-policy.md) |

**Description:** Returns the parent policy number for an affiliated policy. The lookup performed depends on the affiliation classification returned by `getpolicyaffiliatetype`.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Policy number being read. |
| `in_is_policyholder_user` | IN | `VARCHAR2` | — | `TRUE` or `FALSE`; enables the manual-affiliation check reserved for policyholder users. |

### getpolicyaffiliatetype

| Field | Value |
| --- | --- |
| **Returns** | VARCHAR2 — affiliation classification |
| **Called by** | `clsPolicyData.GetAffiliateType` |
| **SQL document** | [SQL-013](../sql/SQL-013-policy-affiliate-type.md) |

**Description:** Returns the affiliation family a policy belongs to, evaluated in a fixed precedence order and defaulting to non-affiliated.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Policy number being read. |
| `in_is_policyholder_user` | IN | `VARCHAR2` | — | `TRUE` or `FALSE`; enables the manual-affiliation check reserved for policyholder users. |

### getgaps

| Field | Value |
| --- | --- |
| **Returns** | REF CURSOR — gap begin and end dates |
| **Called by** | `clsPolicyData.Gaps` |
| **SQL document** | [SQL-016](../sql/SQL-016-coverage-gaps.md) |

**Description:** Returns the periods during which the policy had no coverage in force, derived from adjacent period boundaries and unioned with explicitly recorded gaps.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number being read. |
| `recordset` | IN OUT | `gap_cursor` | — | Result set returned to the caller. |

### getagency

| Field | Value |
| --- | --- |
| **Returns** | REF CURSOR — one agency row |
| **Called by** | `clsAgency.BeforeRetrieve` |
| **SQL document** | [SQL-002](../sql/SQL-002-agency-retrieval.md) |

**Description:** Returns the agency and producer servicing a policy, resolved through the policy's current agent of record.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number being read. |
| `recordset` | IN OUT | `agency_cursor` | — | Result set returned to the caller. |

### getpolicyaddress

| Field | Value |
| --- | --- |
| **Returns** | REF CURSOR — one address and contact row |
| **Called by** | `clsPolicyAddress.BeforeRetrieve` |
| **SQL document** | [SQL-005](../sql/SQL-005-policy-address-by-type.md) |

**Description:** Returns the address and contact for a policy that best matches the requested address and contact types, with an optional fallback from the claim address to the mailing address.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inpolicynum` | IN | `NUMBER` | — | Policy number being read. |
| `inaddresstype` | IN | `VARCHAR2` | — | Address or location type code requested. |
| `incontacttype` | IN | `VARCHAR2` | — | Contact type code requested. |
| `ingetmailaddrwhennotfound` | IN | `VARCHAR2` | — | `Y` or `N`; requests mailing-address fallback when no active claim address exists. |
| `recordset` | IN OUT | `ref_cursor` | — | Result set returned to the caller. |

### getpolicyaddressbylocseqnum

| Field | Value |
| --- | --- |
| **Returns** | REF CURSOR — one address and contact row |
| **Called by** | `clsPolicyAddress.BeforeRetrieve` (alternate path) |
| **SQL document** | [SQL-006](../sql/SQL-006-policy-address-by-location.md) |

**Description:** Returns the highest-priority contact recorded against a specific location sequence number.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inlocationseqnum` | IN | `NUMBER` | — | Location sequence number to read. |
| `recordset` | IN OUT | `ref_cursor` | — | Result set returned to the caller. |

### getpolicyperiods

| Field | Value |
| --- | --- |
| **Returns** | REF CURSOR — policy period rows |
| **Called by** | `clsPolicyPeriodCollection.BeforeRetrieve` |
| **SQL document** | [SQL-008](../sql/SQL-008-policy-periods.md) |

**Description:** Returns all policy periods, or only the most recent N, with risk, plan, and premium values masked while a reduced-form period's information page remains unprinted.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number being read. |
| `atopnpolper` | IN | `NUMBER` | — | Number of most recent periods to return; zero returns every period. |
| `recordset` | IN OUT | `ref_cursor` | — | Result set returned to the caller. |

### getpolicyaffiliatelist

| Field | Value |
| --- | --- |
| **Returns** | REF CURSOR — affiliated policy rows |
| **Called by** | `clsAffiliatedPolicyCollection.BeforeRetrieve` |
| **SQL document** | [SQL-001](../sql/SQL-001-affiliated-policy-list.md) |

**Description:** Returns the member and parent policies of one affiliation family, selected from the caller's affiliate type prefix, with an optional totals row.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Policy number being read. |
| `in_affiliate_type` | IN | `VARCHAR2` | — | Affiliate-type code whose relationship family is walked. |
| `in_include_affil_totals` | IN | `VARCHAR2` | — | `TRUE` or `FALSE`; controls whether a totals row is appended. |
| `recordset` | OUT | `ref_cursor` | — | Result set returned to the caller. |

### team_assign_query

| Field | Value |
| --- | --- |
| **Returns** | REF CURSOR — team assignment rows |
| **Called by** | `clsTeamAssignmentCollection.BeforeRetrieve` |
| **SQL document** | [SQL-009](../sql/SQL-009-team-assignments.md) |

**Description:** Returns SAIF staff assigned to a policy together with office, customer-service-team, agency, and producer rows presented in the same shape.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number being read. |
| `recordset` | IN OUT | `team_assign_cursor` | — | Result set returned to the caller. |

## Business Rules

Rules extracted from this package's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| [PROC-RULE-002](../business-rules.md#stored-procedure-business-rules) | Sales-plan name is masked for reduced-form (`RF`) policy periods | `ppa.psc = 'RF'` | Returns an empty string for `sales_plan_name`; non-RF periods resolve retro plans to a retro-payment-type description and all others to `plan_code.code_descr` |
| [PROC-RULE-003](../business-rules.md#stored-procedure-business-rules) | A policy has a coverage gap indicator when `getgapindicator` returns non-zero | `DECODE(getgapindicator(p.policy_num), 0, '', '- GAP')` | Appends the literal `- GAP` marker to the result row |
| [PROC-RULE-004](../business-rules.md#stored-procedure-business-rules) | A policy is flagged as a combination-wrap sub-account only when a sibling policy in the same experience-rating combination carries `policy_relationship_code = 'WRAP'` | Correlated subquery on `er_combination_policy` and `POLICY` | Sets `combo_wrap_sub_flag` to `Y`, defaulting to `N` |
| [PROC-RULE-005](../business-rules.md#stored-procedure-business-rules) | Manual (policyholder-portal) affiliation takes priority over all other affiliation types, but only for policyholder users | `UPPER(in_is_policyholder_user) = 'TRUE'` | Checks `policy_relationship` for `affil_typ_cd = 'PHP'` parent then member rows before evaluating any other affiliation family |
| [PROC-RULE-006](../business-rules.md#stored-procedure-business-rules) | Affiliation family precedence after the manual check is wrap-up/leasing, then sub-account/state-agency, then experience combination, then premium-discount combination | Sequential `SELECT ... INTO` blocks, each caught by `WHEN NO_DATA_FOUND` to fall through | Returns the first matching classification; the default is `NONAFFILIATED` |
| [PROC-RULE-007](../business-rules.md#stored-procedure-business-rules) | The parent policy number is derived from the affiliate type rather than stored directly | Result of `getpolicyaffiliatetype` | Selects `policy_relationship.parent_policy_num`, `POLICY.policy_relationship_policy_num`, `POLICY.rate_account`, or the minimum-premium member's `combo_policy_period.policy_num` according to the classification |
| [PROC-RULE-008](../business-rules.md#stored-procedure-business-rules) | The legal name shown for a wrap-up chain cascades from the policy to its wrap-up relationship policy to that policy's rate account | `policy_relationship_code = 'WRAP'` then a non-null `rate_account` | Successively re-queries `POLICY.legal_name` up the chain, keeping the last successfully resolved name |
| [PROC-RULE-009](../business-rules.md#stored-procedure-business-rules) | A coverage gap exists between two consecutive periods unless the period is flat-cancelled or a PMS prospect period | `policy_period_status_code NOT IN ('FC','PPROS')` and no adjacent period shares the cancel/expiration date | Returns a gap row (begin = prior cancel/expiration date, end = next period effective date) and unions explicit `policy_coverage_gap` rows |
| [PROC-RULE-010](../business-rules.md#stored-procedure-business-rules) | The agency and producer shown for a policy are always resolved through the current agent-of-record rather than a stored foreign key | n/a | Filters on `AGENT.agent_num = pk72838_comm_authorization.sp72850_get_agent_of_rec(apolicynum)` and `agent.agent_num <> 0` |
| [PROC-RULE-011](../business-rules.md#stored-procedure-business-rules) | When the caller allows mail-address fallback and no active claim address exists, the mailing address type is substituted for the claim type | `ingetmailaddrwhennotfound = 'Y'`, `inaddresstype = 'CLM'`, and an active-`CLM`-address count of zero | Sets the effective address type to `POL` before the main address query runs |
| [PROC-RULE-012](../business-rules.md#stored-procedure-business-rules) | When multiple contacts exist for a location, only the highest-priority contact type is returned | Fixed `CASE` priority `AUD`, `PRL`, `BKP`, `CPA`, `ACT`, `CTR`, `TAX`, then other | Orders by the priority `CASE` then `contact_eff_date DESC` and keeps `ROWNUM <= 1` |
| [PROC-RULE-013](../business-rules.md#stored-procedure-business-rules) | Risk-type code, sales-plan name, risk-type factor, and standard premium are masked for reduced-form periods until the information page has printed | `p.policy_period_status_code = 'RF'` and `SP72347_INFO_PAGE_PRINTED_YN(...) <> 'Y'` | Substitutes an empty string or `0` for the corresponding column |
| [PROC-RULE-014](../business-rules.md#stored-procedure-business-rules) | The affiliated-policy list and its optional totals row are keyed to a relationship-code family selected from the caller's affiliate-type prefix | `UPPER(in_affiliate_type)` matching `AFFIL%`, `LEAS%`, `WRAP%`, `SUBAC%`, `STATE%`, `COMB%`, or `PREM%` | Maps the prefix to an internal relationship code that drives which query branch executes; the totals row is included only when `UPPER(in_include_affil_totals) = 'TRUE'` |
| [PROC-RULE-015](../business-rules.md#stored-procedure-business-rules) | A team member's employee-status code can be overridden by an alias mapping | `user_alias.user_name IS NOT NULL` and a matching `wcis_employee` row exists | Uses the aliased employee's `employee_status_code` instead of the directly assigned team member's |

> These rules are also listed under `## Stored procedure business rules` in [business-rules.md](../business-rules.md).

## Operations

Operations implemented in this package:

| Operation | Description | Trigger |
| --- | --- | --- |
| Retrieve policy detail | Returns extended detail: group, plan, gap indicator, combination flags, and governing class | `clsPolicyData.RetrievePolicyDetail` |
| Classify policy affiliation (superseded) | Resolves a policy's affiliation classification through a fixed precedence hierarchy | `clsPolicyData.GetAffiliateType` |
| Resolve parent policy (superseded) | Resolves the parent policy number for an affiliated policy | `clsPolicyData.GetParentPolicyData` |
| Retrieve wrap-up legal name | Returns the legal name resolved up the wrap-up and rate-account chain | `clsPolicyData.GetWrapupLegalName` |
| Retrieve coverage gaps | Returns date ranges representing gaps in coverage | `clsPolicyData.Gaps` |
| Retrieve agency and producer | Returns the agent-of-record's agency and producer contact and address details | `clsAgency.BeforeRetrieve` |
| Retrieve policy address by type | Returns the best-matching address and contact row for a policy and address type, with mailing-address fallback | `clsPolicyAddress.BeforeRetrieve` |
| Retrieve policy address by location | Returns the highest-priority contact for a specific location sequence number | `clsPolicyAddress.BeforeRetrieve (alternate branch)` |
| Retrieve policy periods | Returns all or the most recent N policy periods with masked and derived risk, plan, and premium data | `clsPolicyPeriodCollection.BeforeRetrieve` |
| Retrieve affiliated policy list | Returns member and parent policies for an affiliation family, with an optional totals row | `clsAffiliatedPolicyCollection.BeforeRetrieve` |
| Retrieve team assignments | Returns SAIF staff team assignments plus office, customer-service-team, agency, and producer pseudo-rows | `clsTeamAssignmentCollection.BeforeRetrieve` |

> These operations are also listed under `## Stored procedure operations` in [operations-processes.md](../operations-processes.md).

## Source Code

> **Note:** The full `WCIS_DBA.pk72846_view_policy` package body (159,943 characters of PL/SQL across 34 procedures/functions) is too large to reproduce in this document. Only the declarations of the methods this library actually calls are shown below. The complete package body is available from the Oracle data dictionary (`DBA_SOURCE`/`ALL_SOURCE`) for `WCIS_DBA.pk72846_view_policy`.

<details>
<summary>PL/SQL declarations of the called methods</summary>

```sql
-- Retrieved from the Oracle data dictionary for WCIS_DBA.pk72846_view_policy

PROCEDURE getviewdetail (
  apolicynum IN NUMBER,
  recordset IN OUT viewpolicydetail_cursor
  )

FUNCTION getwrapuplegalname (
  in_policy_num IN NUMBER
  )
  RETURN VARCHAR2

FUNCTION getparentpolicy (
  in_policy_num IN NUMBER,
  in_is_policyholder_user IN VARCHAR2
  )
  RETURN NUMBER

FUNCTION getpolicyaffiliatetype (
  in_policy_num IN NUMBER,
  in_is_policyholder_user IN VARCHAR2
  )
  RETURN VARCHAR2

PROCEDURE getgaps (apolicynum IN NUMBER, recordset IN OUT gap_cursor)

PROCEDURE getagency (apolicynum IN NUMBER, recordset IN OUT agency_cursor)

PROCEDURE getpolicyaddress (
  inpolicynum IN NUMBER,
  inaddresstype IN VARCHAR2,
  incontacttype IN VARCHAR2,
  ingetmailaddrwhennotfound IN VARCHAR2,
  recordset IN OUT ref_cursor
  )

PROCEDURE getpolicyaddressbylocseqnum (
  inlocationseqnum IN NUMBER,
  recordset IN OUT ref_cursor
  )

PROCEDURE getpolicyperiods (
  apolicynum IN NUMBER,
  atopnpolper IN NUMBER,
  recordset IN OUT ref_cursor
  )

PROCEDURE getpolicyaffiliatelist (
  in_policy_num IN NUMBER,
  in_affiliate_type IN VARCHAR2,
  in_include_affil_totals IN VARCHAR2,
  recordset OUT ref_cursor
  )
  RETURN NUMBER

PROCEDURE team_assign_query (
  apolicynum IN NUMBER,
  recordset IN OUT team_assign_cursor
  )
```

</details>

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

## Related documentation

- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

## Sources

- `Business/SAIF.Business.View.Policy/clsPolicyData.vb`
- `Business/SAIF.Business.View.Policy/clsAgency.vb`
- `Business/SAIF.Business.View.Policy/clsPolicyAddress.vb`
- `Business/SAIF.Business.View.Policy/clsPolicyPeriodCollection.vb`
- `Business/SAIF.Business.View.Policy/clsAffiliatedPolicyCollection.vb`
- `Business/SAIF.Business.View.Policy/clsTeamAssignmentCollection.vb`
- Oracle data dictionary source for `WCIS_DBA.pk72846_view_policy`