-- Actual PL/SQL body retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE for WCIS_DBA.pk73102_policy
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;