CREATE OR REPLACE FUNCTION PREMIUMDO(pPolicyId IN VARCHAR2,
pPolicyRiskId IN VARCHAR2,
pPolicyRiskCoverId IN VARCHAR2,
pRetCode OUT NUMBER,
pRetErr OUT VARCHAR2)
RETURN VARCHAR IS
l_covercode varchar2(1000);
l_plan varchar2(50);
VMETHOD VARCHAR2(100) := '';
V_PREMIUM NUMBER(20, 6);
v_endorse_type varchar2(100);
l_policy_status iims_uwr.t_policy.pol_policy_status%type;
l_product_code VARCHAR2(100);
calcRate NUMBER(20,6);
oldunit Number(20,6);
newunit Number(20,6);
duration_type varchar2(10);
policy_expiry_date DATE;
endorsement_effective_date DATE;
begin
pRetCode := 0;
pRetErr := NULL;
begin
select p.pol_policy_status, p.pol_product_code
into l_policy_status, l_product_code
from iims_uwr.t_policy p
where pol_policy_id = ppolicyid;
exception
when others then
l_policy_status := '';
l_product_code := '';
end;
BEGIN
SELECT NVL(TPP.POP_VALUE_ALPHA_CODE, TPP.POP_VALUE_DESCRIPTION)
INTO l_plan
FROM IIMS_UWR.T_POLICY_PROPERTY TPP
WHERE TPP.POP_POL_POLICY_ID = pPolicyId
AND TPP.POP_PAR_PARAMETER_NAME IN ('Policy Plan')
AND TPP.POP_EFFECTIVE_END_DATE IS NULL;
-- iims_int.testjoy('SAMEone'||pPolicyId||VPLAN);
EXCEPTION
WHEN OTHERS THEN
pRetCode := SQLCODE;
pRetErr := SQLERRM;
END;
IF l_product_code='1913' AND L_PLAN='DO' AND l_policy_status='06' THEN
BEGIN
select aa.prp_value_number
into V_PREMIUM
from t_policy_risk_property aa
where aa.prp_par_parameter_name='Premium'
and aa.prp_prk_pol_policy_id=pPolicyId
and aa.prp_effective_end_date is null;
EXCEPTION
WHEN OTHERS THEN
V_PREMIUM := 0;
END;
BEGIN
select r.pod_policy_expiry_date -- policy expiry date
into policy_expiry_date
from t_policy_detail r
where r.pod_pol_policy_id=pPolicyId
and r.pod_effective_end_date is null;
EXCEPTION
WHEN OTHERS THEN
policy_expiry_date := '01/01/1970';
END;
BEGIN
select r.pod_effective_start_date -- endorsement effective date
into endorsement_effective_date
from t_policy_detail r
where r.pod_pol_policy_id=pPolicyId
and r.pod_effective_end_date is null;
EXCEPTION
WHEN OTHERS THEN
endorsement_effective_date := '01/01/1970';
END;
END;
END IF;
return V_PREMIUM;
exception
when OTHERS then
pRetCode := SQLCODE;
pRetErr := SQLERRM;
RETURN 1;
END PREMIUMDO;
Write, Run & Share PL/SQL code online using OneCompiler's Oracle PL/SQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for Oracle PL/SQL running on latest version 23c (23.3.0.0). Getting started with the OneCompiler's Oracle PL/SQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'PL/SQL' and start writing code to learn and test online without worrying about tedious process of installation.
PL/SQL is procedural extension for SQL created by Oracle. It is by default embedded into the Oracle Database. PL/SQL program units are compiled and stored inside the Oracle Database which results in optimal execution times as the PL/SQL and SQL run within the same server process.
Following is the syntax structure for the PL/SQL code blocks
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
DECLARE
message varchar2(100):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
CREATE OR REPLACE FUNCTION
hello_user
(user_name IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN 'Hello ' || user_name;
END hello_user;
/
BEGIN
dbms_output.put_line(hello_user('Peter'));
END;
/
BEGIN
DBMS_OUTPUT.put_line (1/0);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('error is: ' || SQLERRM);
END;