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 |
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 | 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 rulesin 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 operationsin operations-processes.md.
Source Code¶
PL/SQL source
-- 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¶
Sources¶
Business/SAIF.Business.View.Policy/clsPolicyData.vb- Oracle data dictionary source for
WCIS_DBA.pk73094_policy_data