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

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

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

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

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

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

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

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

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

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

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

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.

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.

PL/SQL declarations of the called methods

-- 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
  )

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¶

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

pk72846_view_policy¶

Overview¶

Purpose¶

Procedures / Methods¶

getviewdetail¶

Parameters¶

getwrapuplegalname¶

Parameters¶

getparentpolicy¶

Parameters¶

getpolicyaffiliatetype¶

Parameters¶

getgaps¶

Parameters¶

getagency¶

Parameters¶

getpolicyaddress¶

Parameters¶

getpolicyaddressbylocseqnum¶

Parameters¶

getpolicyperiods¶

Parameters¶

getpolicyaffiliatelist¶

Parameters¶

team_assign_query¶

Parameters¶

Business Rules¶

Operations¶

Source Code¶

Related documentation¶

Sources¶