pk73102_policy¶
Overview¶
| Field | Value |
| --- | --- |
| Schema | WCIS_DBA |
| Type | Package |
| Database | Oracle |
| Calling libraries | SAIF.Business.Policy |
| Calling classes | PolicyInformation |
Purpose¶
pk73102_policy determines whether WCIS owns a submission, exposes PMS-status helper functions, proxies several submission-status changes to the remote @POLDB package, and includes a dividend-notepad insert routine.
Procedures and methods¶
does_wcis_own_submission¶
| Field | Value |
| --- | --- |
| Returns | VARCHAR2(1) ownership flag |
| Called by | PolicyInformation.GetPolicyOwnershipSystem |
Description: Looks up submission_status_code.wcis_owns_flag for the submission identified by in_policy_num. If no row is found or another exception occurs, the function returns 'Y'.
Parameters¶
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| in_policy_num | IN | sub_policy.policy_num%TYPE | — | Policy number whose submission ownership flag is needed. |
reject_submission¶
| Field | Value |
| --- | --- |
| Returns | NUMBER |
| Called by | No calling class documented in this library* |
Description: Pass-through wrapper that forwards the reject request to WCIS_DBA.PK73102_POLICY.REJECT_SUBMISSION@POLDB.
Parameters¶
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| in_policy_num | IN | sub_policy.policy_num%TYPE | — | Submission policy number to reject. |
| in_rejection_reason_code | IN | sub_policy.rejection_reason_code%TYPE | — | Rejection reason code forwarded to the remote package. |
| in_update_user | IN | sub_policy.update_user%TYPE | — | User ID recorded for the update. |
unreject_submission¶
| Field | Value |
| --- | --- |
| Returns | NUMBER |
| Called by | No calling class documented in this library* |
Description: Pass-through wrapper that forwards the unreject request to WCIS_DBA.PK73102_POLICY.UNREJECT_SUBMISSION@POLDB.
Parameters¶
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| in_policy_num | IN | sub_policy.policy_num%TYPE | — | Submission policy number to unreject. |
| in_update_user | IN | sub_policy.update_user%TYPE | — | User ID recorded for the update. |
set_submission_to_pending¶
| Field | Value |
| --- | --- |
| Returns | NUMBER |
| Called by | No calling class documented in this library* |
Description: Pass-through wrapper that forwards the pending-status update to WCIS_DBA.PK73102_POLICY.SET_SUBMISSION_TO_PENDING@POLDB.
Parameters¶
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| in_policy_num | IN | sub_policy.policy_num%TYPE | — | Submission policy number to set to pending. |
| in_update_user | IN | sub_policy.update_user%TYPE | — | User ID recorded for the update. |
set_submission_to_quoted¶
| Field | Value |
| --- | --- |
| Returns | NUMBER |
| Called by | No calling class documented in this library* |
Description: Pass-through wrapper that forwards the quoted-status update to WCIS_DBA.PK73102_POLICY.SET_SUBMISSION_TO_QUOTED@POLDB.
Parameters¶
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| in_policy_num | IN | sub_policy.policy_num%TYPE | — | Submission policy number to set to quoted. |
| in_update_user | IN | sub_policy.update_user%TYPE | — | User ID recorded for the update. |
set_app_received_date¶
| Field | Value |
| --- | --- |
| Returns | NUMBER |
| Called by | No calling class documented in this library* |
Description: Pass-through wrapper that forwards the app-received-date update to WCIS_DBA.PK73102_POLICY.SET_APP_RECEIVED_DATE@POLDB.
Parameters¶
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| in_policy_num | IN | sub_policy.policy_num%TYPE | — | Submission policy number to update. |
| in_update_user | IN | sub_policy.update_user%TYPE | — | User ID recorded for the update. |
is_pms_status and is_pms_status_yn¶
| Field | Value |
| --- | --- |
| Returns | BOOLEAN and VARCHAR2(1) |
| Called by | No calling class documented in this library* |
Description: is_pms_status returns TRUE only for policy status codes PP, PA, and PC. is_pms_status_yn wraps that Boolean result and converts it to 'Y' or 'N'.
Parameters¶
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| in_policy_status_code | IN | VARCHAR2 | — | Policy status code being evaluated. |
insert_dividend_notepad¶
| Field | Value |
| --- | --- |
| Returns | Procedure; no return value |
| Called by | No calling class documented in this library* |
Description: Writes a dividend memo entry through WCIS_DBA.SP72155_INSERT_MEMO_ENTRY, passing memo code DVD, description Dividend, caller-supplied note text, and caller-supplied user ID.
Parameters¶
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| in_div_issue_seq_num | IN | NUMBER | — | Dividend issue sequence number received by the procedure. |
| in_policy_num | IN | NUMBER | — | Policy number whose memo entry is being written. |
| in_note_text | IN | VARCHAR2 | — | Free-text dividend note content. |
| in_user | IN | VARCHAR2 | — | User ID recorded on the memo entry. |
Business Rules¶
Submission ownership comes from the status code ownership flag¶
| Field | Value |
| --- | --- |
| Rule ID | BR-POL-006 |
| Statement | Submission ownership is determined by the wcis_owns_flag on the matching submission_status_code row for the submission's current status. |
| Trigger | does_wcis_own_submission is called with a policy number that resolves to a sub_policy row. |
| Enforcement | The function joins sub_policy to submission_status_code and returns ssc.wcis_owns_flag. |
| Outcome | Callers receive the status-driven WCIS ownership indicator for the submission. |
Missing or failed ownership lookups default to WCIS¶
| Field | Value |
| --- | --- |
| Rule ID | BR-POL-007 |
| Statement | When the ownership lookup finds no submission row or raises an unexpected exception, the function returns 'Y' and treats WCIS as the owner. |
| Trigger | does_wcis_own_submission hits NO_DATA_FOUND or WHEN OTHERS. |
| Enforcement | Both exception handlers return 'Y'. |
| Outcome | The caller maps the result to Enums.PolicyOwnershipSystems.WCIS rather than failing the lookup. |
PMS status is limited to PA PC and PP¶
| Field | Value |
| --- | --- |
| Rule ID | BR-POL-008 |
| Statement | Only policy status codes PP, PA, and PC are treated as PMS statuses, and the wrapper function exposes that result as Y or N. |
| Trigger | is_pms_status or is_pms_status_yn evaluates a policy status code. |
| Enforcement | is_pms_status checks membership in ('PP', 'PA', 'PC'); is_pms_status_yn converts the Boolean result into 'Y' or 'N'. |
| Outcome | PMS-specific callers get a consistent Boolean or Y/N answer from the same code list. |
Operations¶
Determine submission ownership¶
| Field | Value |
| --- | --- |
| Purpose | Return the WCIS ownership indicator for a submission so application code can map it to the correct ownership system enum. |
| Trigger | PolicyInformation.GetPolicyOwnershipSystem runs select pk73102_policy.does_wcis_own_submission(:in_policy_num) from dual. |
| Key steps | Join sub_policy to submission_status_code; return wcis_owns_flag; default to 'Y' on missing data or unexpected errors. |
| Outputs | Y or N ownership flag consumed by the .NET caller. |
Proxy submission status transitions to POLDB¶
| Field | Value |
| --- | --- |
| Purpose | Forward reject, unreject, pending, quoted, and app-received-date changes to the remote @POLDB implementation. |
| Trigger | One of the public submission-status functions is called. |
| Key steps | Pass through the received policy number, user ID, and any rejection reason to the corresponding WCIS_DBA.PK73102_POLICY...@POLDB function. |
| Outputs | Numeric return code from the remote package. |
Insert dividend notepad memo¶
| Field | Value |
| --- | --- |
| Purpose | Create a dividend memo entry for a policy. |
| Trigger | insert_dividend_notepad is called with dividend issue, policy, note text, and user inputs. |
| Key steps | Call WCIS_DBA.SP72155_INSERT_MEMO_ENTRY with memo code DVD, description Dividend, the supplied note text, and the supplied user. |
| Outputs | Persisted memo entry created by the downstream procedure. |
Source Code¶
PL/SQL package specification
PACKAGE pk73102_policy
AS
-- FUNCTION get_status_code_descr(in_policy_status_code IN VARCHAR2)
-- RETURN VARCHAR2;
--
-- FUNCTION get_underwriter(in_policy_num IN NUMBER)
-- RETURN VARCHAR2;
--
-- FUNCTION get_market_type_code(in_policy_num IN NUMBER)
-- RETURN VARCHAR2;
--
-- FUNCTION get_legal_name(in_policy_num IN NUMBER)
-- RETURN VARCHAR2;
--
-- FUNCTION get_policy_name(in_policy_num IN NUMBER)
-- RETURN VARCHAR2;
--
-- FUNCTION get_policy_num
-- RETURN NUMBER;
--
-- FUNCTION get_status_code(in_policy_num IN NUMBER)
-- RETURN VARCHAR2;
--
-- FUNCTION get_rate_acct(in_policy_num IN NUMBER)
-- RETURN VARCHAR2;
FUNCTION does_wcis_own_submission(in_policy_num IN sub_policy.policy_num%TYPE)
RETURN VARCHAR2;
FUNCTION reject_submission(in_policy_num IN sub_policy.policy_num%TYPE
, in_rejection_reason_code IN sub_policy.rejection_reason_code%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER;
FUNCTION unreject_submission(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER;
FUNCTION set_submission_to_pending(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER;
FUNCTION set_submission_to_quoted(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER;
FUNCTION set_app_received_date(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER;
FUNCTION is_pms_status(in_policy_status_code IN VARCHAR2)
RETURN BOOLEAN;
FUNCTION is_pms_status_yn(in_policy_status_code IN VARCHAR2)
RETURN VARCHAR2;
PROCEDURE insert_dividend_notepad(in_div_issue_seq_num IN NUMBER
, in_policy_num IN NUMBER
, in_note_text IN VARCHAR2
, in_user IN VARCHAR2);
END;
/
PL/SQL package body
PACKAGE BODY pk73102_policy
AS
/* ==========================================================================
Name : does_wcis_own_submission
Prepared By : leedas
Created : 02/06/2008
Description : Indicates when the WCIS system owns the submission submitted online.
Modification History:
========================================================================*/
FUNCTION does_wcis_own_submission(in_policy_num IN sub_policy.policy_num%TYPE)
RETURN VARCHAR2
IS
v_wcis_owns_ind VARCHAR2(1);
BEGIN
SELECT wcis_owns_flag
INTO v_wcis_owns_ind
FROM submission_status_code ssc
, sub_policy s
WHERE s.submission_status_code = ssc.code
AND s.policy_num = in_policy_num;
RETURN v_wcis_owns_ind;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'Y';
WHEN OTHERS THEN
RETURN 'Y';
END does_wcis_own_submission;
/* ==========================================================================
Name : reject_submission
Prepared By : gremil
Created : 03/06/2008
Description : Updates policy table to reject status.
Modification History:
========================================================================*/
FUNCTION reject_submission(in_policy_num IN sub_policy.policy_num%TYPE
, in_rejection_reason_code IN sub_policy.rejection_reason_code%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN WCIS_DBA.PK73102_POLICY.REJECT_SUBMISSION@POLDB(in_policy_num,in_rejection_reason_code,in_update_user);
END reject_submission;
/* ==========================================================================
Name : unreject_submission
Prepared By : gremil
Created : 03/06/2008
Description : Updates policy table to non-rejection status.
Modification History:
========================================================================*/
FUNCTION unreject_submission(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN WCIS_DBA.PK73102_POLICY.UNREJECT_SUBMISSION@POLDB(in_policy_num,in_update_user);
END unreject_submission;
/* ==========================================================================
Name : set_submission_to_pending
Prepared By : gremil
Created : 03/06/2008
Description : Updates policy table to pending status.
Modification History:
========================================================================*/
FUNCTION set_submission_to_pending(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN WCIS_DBA.PK73102_POLICY.SET_SUBMISSION_TO_PENDING@POLDB(in_policy_num,in_update_user);
END set_submission_to_pending;
/* ==========================================================================
Name : set_submission_to_quoted
Prepared By : gremil
Created : 03/06/2008
Description : Updates policy table to quoted status.
Modification History:
========================================================================*/
FUNCTION set_submission_to_quoted(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN WCIS_DBA.PK73102_POLICY.SET_SUBMISSION_TO_QUOTED@POLDB(in_policy_num,in_update_user);
END set_submission_to_quoted;
/* ==========================================================================
Name : set_app_received_date
Prepared By : gremil
Created : 03/31/2008
Description : Updates policy table.
Modification History:
========================================================================*/
FUNCTION set_app_received_date(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN WCIS_DBA.PK73102_POLICY.SET_APP_RECEIVED_DATE@POLDB(in_policy_num,in_update_user);
END set_app_received_date;
/* ==========================================================================
Name : is_pms_status
Prepared By : elawoh
Created : 06/22/2009
Description : Determines if policy status is a PMS type.
Called by sp62055_batch_processing.sql, fa100s, fa201s. and ub202.
Modification History:
06/22/09 Written for AR Billing Migration project
Returns True when policy is a PMS policy_status_code PA, PC, PP
========================================================================*/
FUNCTION is_pms_status(in_policy_status_code IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
IF in_policy_status_code IN ('PP', 'PA', 'PC') THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
pk73090_error_handler.raise_an_error(
'pk73102.is_PMS_status(' || in_policy_status_code || '): ' || SQLERRM);
END is_pms_status;
/* ==========================================================================
Name : wrapper to determine if policy status is a pms
Prepared By : elawoh
Created : 07/10/2009
Description : Determines if policy status is a PMS type.
Called by sp62055_batch_processing.sql, fa100s, fa201s, and ub202
Modification History:
07/10/09 Written for AR Billing Migration project
Returns True when policy is a PMS policy_status_code PA, PC, PP
========================================================================*/
FUNCTION is_pms_status_yn(in_policy_status_code IN VARCHAR2)
RETURN VARCHAR2
IS
v_return VARCHAR2(1);
BEGIN
IF is_pms_status(in_policy_status_code) THEN
v_return := 'Y';
ELSE
v_return := 'N';
END IF;
-- DBMS_OUTPUT.put_line('pms yn, return ' || v_policy_status_code);
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
pk73090_error_handler.raise_an_error(
'pk73102.is_PMS_status_yn(' || in_policy_status_code || '): ' || SQLERRM);
END is_pms_status_yn;
/* ==========================================================================
Name : insert_dividend_notepad
Prepared By : carade
Created : 07/05/2012
Description : Insert a notepad for dividends.
Modification History:
========================================================================*/
PROCEDURE insert_dividend_notepad(in_div_issue_seq_num IN NUMBER
, in_policy_num IN NUMBER
, in_note_text IN VARCHAR2
, in_user IN VARCHAR2)
IS
v_final_compute_flag VARCHAR2(1);
CURSOR pp_cur
IS
SELECT pp.policy_period_eff_date
FROM policy_period pp
WHERE pp.policy_num = in_policy_num
AND sp72347_info_page_printed_yn(pp.policy_num
, pp.policy_period_eff_date) = 'Y'
ORDER BY pp.policy_period_eff_date DESC;
pp_rec pp_cur%ROWTYPE;
BEGIN
--
WCIS_DBA.SP72155_INSERT_MEMO_ENTRY(in_policy_num
, pp_rec.policy_period_eff_date
, 'DVD' --in_memo_code
, SYSDATE --in_memo_date
, 'Dividend' --in_memo_descr
, in_note_text
, in_user
, NULL --in_notepad_contact_type_code
, NULL --in_notepad_contact_source_code
);
EXCEPTION
WHEN OTHERS THEN
pk73090_error_handler.raise_an_error(
'pk73102.insert_dividend_notepad(' || in_policy_num || '): ' || SQLERRM);
END insert_dividend_notepad;
END pk73102_policy;
Calling Code References¶
| Class | Method | Source File |
| --- | --- | --- |
| PolicyInformation | GetPolicyOwnershipSystem (Get) | SAIF.Business.Policy\PolicyInformation.vb |
Related Documentation¶
Note: Statements marked with
*were inferred from code structure, namingconventions, or contextual evidence where direct confirmation was not available
in source artifacts.
Sources¶
-
Oracle retrieval JSON:
C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73102_policy.json -
SAIF.Business.Policy\PolicyInformation.vb