Hello.
My function returns if a policy is changed or not.
What i would like to do is to load the POLICY table, to apply the function into the load script, and to add one field at the end of the table that will contain Y/N(changed/not changed) for each row(each policy ID).
Oracle function:
FUNCTION IS_ANNEX_CHANGED (p_policy_id number) return varchar is
cursor c1 is select count(*)
from
(
select cfg.fract_type
from gen_risk_covered grc, cfg_gl_gen_fractions cfg
where grc.cover_type=cfg.cover_type
and grc.insr_type=cfg.insr_type
and risk_state between 0 and 12
and grc.annex_id<>0
and policy_id=p_policy_id
minus
select cfg.fract_type
from gen_risk_covered grc, cfg_gl_gen_fractions cfg
where grc.cover_type=cfg.cover_type
and grc.insr_type=cfg.insr_type
--and risk_state between 0 and 12
and grc.annex_id=0
and policy_id=p_policy_id
);
nr number;
begin
open c1;
fetch c1 into nr;
close c1;
if nvl(nr,0)>0 then
return 'Y';
else return 'N';
end if;
end;
---------------------------------------------------------------------
policy:
LOAD POLICY_ID,
INSR_TYPE,
OFFICE_ID,
CLIENT_ID,
AGENT_ID,
INSR_BEGIN,
INSR_END
...................
FROM
[...\policy.qvd]
(qvd);
.png "Luminary Alumni"){kind=icon}
