pk73094_policy_data¶
Overview¶
| Field | Value |
|---|---|
| Schema | WCIS_DBA |
| Type | Package |
| Database | Oracle |
| Calling libraries | SAIF.Business.View.Policy |
| Calling classes | clsPolicyData |
Purpose¶
pk73094_policy_data returns the main policy-header record consumed by clsPolicyData. The retrieved package body joins status metadata, derives cancellation status through sp72268_get_cancel_status, and includes a helper that chooses the preferred legal name for an account number.
Procedures / Methods¶
getpolicy¶
| Field | Value |
|---|---|
| Returns | policy_cur |
| Called by | clsPolicyData.GetPolicyData and clsPolicyData.Criteria.RetrieveSQL |
| Description | Returns the core policy header record for one policy number. |
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
apolicynum |
IN | NUMBER |
— | Policy number to retrieve. |
recordset |
IN OUT | policy_cur |
— | Cursor populated with the policy header row. |
get_account_name¶
| Field | Value |
|---|---|
| Returns | VARCHAR2 |
| Called by | pk73094_policy_data helper callers |
| Description | Returns the preferred legal name for an account number by ranking candidate policies. |
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_account_num |
IN | VARCHAR2 |
— | Account number to resolve. |
Business Rules¶
The retrieved database source embeds the following server-side rules.
| Rule ID | Summary |
|---|---|
| BR-VP-026 | getpolicy converts null current_balance and premium_estimate values to zero before returning them. |
| BR-VP-027 | get_account_name orders candidate policies by status, parent and self relationship, and latest cancel date before taking the first row. |
BR-VP-026 Policy header retrieval normalizes null financial amounts to zero¶
| Field | Value |
|---|---|
| Statement | Policy-header retrieval wraps current_balance and premium_estimate in NVL(..., 0) before returning them. |
| Trigger | getpolicy executes. |
| Enforcement | The SELECT list uses NVL(current_balance, 0) and NVL(premium_estimate, 0). |
| Outcome | Callers receive zero instead of null for the retrieved balance and premium-estimate fields. |
| Source | Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json |
BR-VP-027 Account-name lookup ranks active and parent policies first¶
| Field | Value |
|---|---|
| Statement | Account-name lookup ranks policy candidates so active or pending-cancel statuses sort first, cancelled next, parent or self policies ahead of member policies, and later cancel dates ahead of earlier ones. |
| Trigger | get_account_name executes with a non-null account number. |
| Enforcement | The cursor query computes policy_status_order, policy_relationship_order, and cancel_date, orders by those values, and assigns the first fetched legal_name to v_account_name. |
| Outcome | The returned account name comes from the preferred policy row for that account number. |
| Source | Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json |
Operations¶
The retrieved database source implements the following internal operations.
| Operation | Description | Trigger |
|---|---|---|
| Retrieve policy header record | Return the core policy header row together with status text and derived cancellation status. | Caller invokes getpolicy. |
| Resolve preferred account display name | Return the preferred legal name for an account number by ranking related policy rows. | Caller invokes get_account_name. |
Retrieve policy header record¶
| Field | Value |
|---|---|
| Description | Return the core policy header row together with status text and derived cancellation status. |
| Trigger | Caller invokes getpolicy. |
| Source | Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json |
Resolve preferred account display name¶
| Field | Value |
|---|---|
| Description | Return the preferred legal name for an account number by ranking related policy rows. |
| Trigger | Caller invokes get_account_name. |
| Source | Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json |
Calling code references¶
| Class | Method | Source File |
|---|---|---|
clsPolicyData |
GetPolicyData / Criteria.RetrieveSQL (getpolicy) |
SAIF.Business.View.Policy\clsPolicyData.vb |
Source Code¶
PL/SQL package source
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;
Related documentation¶
Sources¶
- Oracle retrieval JSON:
C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json - Calling code:
SAIF.Business.View.Policy\clsPolicyData.vb