--- title: "pk73094_policy_data 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: "pk73094_policy_data" source_type: "PACKAGE" oracle_status: "found" library: SAIF.Business.View.Policy reference_type: package --- # pk73094_policy_data ## 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` | ## Purpose Returns the single-row policy header (identity, status, dates, flags, live-computed cancellation status) consumed by every policy view. ## Procedures / Methods ### getpolicy | Field | Value | | --- | --- | | **Returns** | REF CURSOR — one policy header row | | **Called by** | `clsPolicyData.BeforeRetrieve` | | **SQL document** | [SQL-007](../sql/SQL-007-policy-header.md) | **Description:** Returns the policy header: identity, legal and business names, status, dates, agent and agency numbers, secure-policy code, and a cancellation status computed inline for every row. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `apolicynum` | IN | `NUMBER` | — | Policy number being read. | | `recordset` | IN OUT | `policy_cur` | — | Result set returned to the caller. | ## Business Rules Rules extracted from this package's logic: | Rule ID | Rule | Condition | Action | | --- | --- | --- | --- | | [PROC-RULE-001](../business-rules.md#stored-procedure-business-rules) | The per-row cancellation status is always computed live, not stored | Every row returned by the header cursor | Invokes `sp72268_get_cancel_status(p.policy_num)` inline in the `SELECT` list to populate `policy_cancel_status` | > 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 header | Returns the single-row policy header for a policy number, including a live-computed cancellation status | `clsPolicyData.BeforeRetrieve` | > These operations are also listed under `## Stored procedure operations` in [operations-processes.md](../operations-processes.md). ## Source Code
PL/SQL source ```sql -- Retrieved from the Oracle data dictionary for WCIS_DBA.pk73094_policy_data PACKAGE pk73094_policy_data AS TYPE ref_cursor IS REF CURSOR; TYPE policy_rec IS RECORD ( agent_direct_split_flag POLICY.agent_direct_split_flag%TYPE, agent_num POLICY.agent_num%TYPE, aoi_exclusion_flag POLICY.aoi_exclusion_flag%TYPE, app_received_date POLICY.app_received_date%TYPE, application_date POLICY.application_date%TYPE, application_num POLICY.application_num%TYPE, application_signed_flag POLICY.application_signed_flag%TYPE, billing_cycle_day POLICY.billing_cycle_day%TYPE, bypass_auto_pricing_flag POLICY.bypass_auto_pricing_flag%TYPE, cancellation_track_code POLICY.cancellation_track_code%TYPE, create_date POLICY.create_date%TYPE, create_user POLICY.create_user%TYPE, current_balance POLICY.current_balance%TYPE, customer_service_team_code POLICY.customer_service_team_code%TYPE, donot_auto_reinstate_flag POLICY.donot_auto_reinstate_flag%TYPE, duplicate_claim_check_flag POLICY.duplicate_claim_check_flag%TYPE, entity_code POLICY.entity_code%TYPE, finalize_date POLICY.finalize_date%TYPE, finalize_user POLICY.finalize_user%TYPE, group_enrollment_flag POLICY.group_enrollment_flag%TYPE, hist_cont_coverage_date POLICY.hist_cont_coverage_date%TYPE, hold_cancel_date POLICY.hold_cancel_date%TYPE, hold_cancel_reason_code POLICY.hold_cancel_reason_code%TYPE, hold_cancel_user POLICY.hold_cancel_user%TYPE, hold_invoice_flag POLICY.hold_invoice_flag%TYPE, include_manual_cancel_flag POLICY.include_manual_cancel_flag%TYPE, interstate_risk_id_flag POLICY.interstate_risk_id_flag%TYPE, last_pvt_carrier_cover_eff_dte POLICY.last_pvt_carrier_cover_eff_dte%TYPE, last_rate_survey_date POLICY.last_rate_survey_date%TYPE, legacy_policy_num POLICY.legacy_policy_num%TYPE, legal_name POLICY.legal_name%TYPE, management_experience_date POLICY.management_experience_date%TYPE, market_type_code POLICY.market_type_code%TYPE, mco_restrict_flag POLICY.mco_restrict_flag%TYPE, naics_code POLICY.naics_code%TYPE, ncci_binder_num POLICY.ncci_binder_num%TYPE, ncci_risk_id POLICY.ncci_risk_id%TYPE, office_code POLICY.office_code%TYPE, other_carrier_data_req_flag POLICY.other_carrier_data_req_flag%TYPE, pay_rpt_print_copies POLICY.pay_rpt_print_copies%TYPE, pms_account_subcd POLICY.pms_account_subcd%TYPE, policy_name POLICY.policy_name%TYPE, policy_num POLICY.policy_num%TYPE, policy_orig_eff_date POLICY.policy_orig_eff_date%TYPE, policy_relationship_code POLICY.policy_relationship_code%TYPE, policy_relationship_policy_num POLICY.policy_relationship_policy_num%TYPE, policy_relationship_prt_dtl_fl POLICY.policy_relationship_prt_dtl_fl%TYPE, policy_status_code POLICY.policy_status_code%TYPE, status VARCHAR2 (65), policy_status_group_code VARCHAR2 (5), policy_cancel_status VARCHAR2 (65), premium_estimate POLICY.premium_estimate%TYPE, proposal_date POLICY.proposal_date%TYPE, quote_req_type POLICY.quote_req_type%TYPE, quote_underwriter POLICY.quote_underwriter%TYPE, rate_account POLICY.rate_account%TYPE, rejection_date POLICY.rejection_date%TYPE, rejection_reason_code POLICY.rejection_reason_code%TYPE, sales_rep POLICY.sales_rep%TYPE, seasonal_flag POLICY.seasonal_flag%TYPE, secure_policy_code POLICY.secure_policy_code%TYPE, sic_code POLICY.sic_code%TYPE, surety_bond POLICY.surety_bond%TYPE, tail_coverage_flag POLICY.tail_coverage_flag%TYPE, trading_seq_num POLICY.trading_seq_num%TYPE, update_date POLICY.update_date%TYPE, update_num POLICY.update_num%TYPE, update_user POLICY.update_user%TYPE, vendor_num POLICY.vendor_num%TYPE, voluntary_date POLICY.voluntary_date%TYPE, wcd_num POLICY.wcd_num%TYPE, account_num POLICY.account_num%TYPE, gw_conversion_eff_Date POLICY.gw_conversion_eff_Date%TYPE ); TYPE policy_cur IS REF CURSOR RETURN policy_rec; PROCEDURE getpolicy (apolicynum IN NUMBER, recordset IN OUT policy_cur); FUNCTION get_account_name(in_account_num IN VARCHAR2) RETURN VARCHAR2; END pk73094_policy_data; / PACKAGE BODY pk73094_policy_data AS PROCEDURE getpolicy (apolicynum IN NUMBER, recordset IN OUT policy_cur) AS BEGIN OPEN recordset FOR SELECT agent_direct_split_flag, agent_num, aoi_exclusion_flag, app_received_date, application_date, application_num, application_signed_flag, billing_cycle_day, bypass_auto_pricing_flag, cancellation_track_code, create_date, create_user, NVL (current_balance, 0) current_balance, customer_service_team_code, donot_auto_reinstate_flag, duplicate_claim_check_flag, entity_code, finalize_date, finalize_user, group_enrollment_flag, hist_cont_coverage_date, hold_cancel_date, hold_cancel_reason_code, hold_cancel_user, hold_invoice_flag, include_manual_cancel_flag, interstate_risk_id_flag, last_pvt_carrier_cover_eff_dte, last_rate_survey_date, legacy_policy_num, legal_name, management_experience_date, market_type_code, mco_restrict_flag, naics_code, ncci_binder_num, ncci_risk_id, office_code, other_carrier_data_req_flag, pay_rpt_print_copies, pms_account_subcd, policy_name, policy_num, policy_orig_eff_date, policy_relationship_code, policy_relationship_policy_num, policy_relationship_prt_dtl_fl, policy_status_code, psc.code_descr AS status, psc.policy_status_group_code, sp72268_get_cancel_status (p.policy_num) policy_cancel_status, NVL (premium_estimate, 0) premium_estimate, proposal_date, quote_req_type, quote_underwriter, rate_account, rejection_date, rejection_reason_code, sales_rep, seasonal_flag, secure_policy_code, null as sic_code, surety_bond, tail_coverage_flag, trading_seq_num, update_date, update_num, update_user, vendor_num, voluntary_date, wcd_num, account_num, gw_conversion_eff_Date FROM POLICY p JOIN policy_status_code psc ON (p.policy_status_code = psc.code) WHERE policy_num = apolicynum; END; FUNCTION get_account_name(in_account_num IN VARCHAR2) RETURN VARCHAR2 IS v_account_name VARCHAR2(80); CURSOR p_csr IS SELECT policy_num , legal_name FROM (SELECT p.policy_num , p.legal_name , CASE WHEN p.policy_status_code IN ('0' , '3') THEN 1 WHEN p.policy_status_code = '5' THEN 2 ELSE 3 END policy_status_order , CASE WHEN p.policy_num = NVL(p.policy_relationship_policy_num, p.policy_num) THEN 1 ELSE 2 END policy_relationship_order , CASE WHEN p.policy_status_code = '5' THEN c.cancel_date ELSE NULL END cancel_date FROM policy p LEFT OUTER JOIN cancellation c ON c.policy_num = p.policy_num AND c.cancellation_seq_num = (SELECT MAX( c2.cancellation_seq_num) FROM cancellation c2 WHERE c2.policy_num = p.policy_num) WHERE p.account_num = in_account_num) ORDER BY policy_status_order , policy_relationship_order , cancel_date DESC NULLS LAST , policy_num; p_rec p_csr%ROWTYPE; BEGIN IF in_account_num IS NULL THEN v_account_name := NULL; ELSE OPEN p_csr; FETCH p_csr INTO p_rec; --DBMS_OUTPUT.put_line( 'acct num: ' || in_account_num || ' from pol: ' || p_rec.policy_num || ' name: ' || p_rec.legal_name); v_account_name := p_rec.legal_name; CLOSE p_csr; END IF; RETURN v_account_name; END get_account_name; END pk73094_policy_data; ```
> **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` - Oracle data dictionary source for `WCIS_DBA.pk73094_policy_data`