First off, bear with me since I'm a newbie..
I have a function that returns a ref_cursor... I need (within the function) to check if the ref_cirsor is empty, and if it is, call another function to populate the ref_cursor. basically if ithe data isn't in the database, it's gonna be in the archieve database, which has the same table/layouts.
Here's a snipet of code... Any help would be appreciated.
FUNCTION Get_summary_by_policy_number(N_POLICY_IN IN policy.N_POLICY%TYPE)
RETURN REF_CURSOR
AS
claim_summary_cursor ref_cursor;
BEGIN
open claim_summary_cursor for
SELECT CLAIM.N_CLAIM_NUMBER, CLAIM.C_CLAIM_STATUS,
POLICY.N_POLICY,
LOSS.D_DATE,LOSS.T_FNOL_LOSS_DESC, LOSS.C_LOSS_TYPE,
CN.M_FIRST_NAME, CN.M_MIDDLE_NAME, CN.M_LAST_NAME
FROM
claim CLAIM,
policy POLICY,
loss LOSS,
client_name CN,
ins_involvement II,
involvement_role IR
WHERE
CLAIM.N_POLICY_ID = POLICY.N_POLICY_ID AND
CLAIM.N_LOSS_ID = LOSS.N_LOSS_ID AND
CLAIM.N_CLAIM_ID = II.N_ENTITY_ID AND
II.N_INS_INVL_ID = IR.N_INS_INVL_ID AND
II.N_PREF_NAME_ID = CN.N_NAME_ID (+) AND
CLAIM.C_CLAIM_STATUS <> 'ER' AND
CLAIM.C_RCD_DEL <> 'Y' AND
CLAIM.C_CLAIM_FILE_TYPE NOT IN ('E','CN') AND
LOSS.C_RCD_DEL <> 'Y' AND
POLICY.C_RCD_DEL <> 'Y' AND
II.C_RCD_DEL <> 'Y' AND
IR.C_RCD_DEL <> 'Y' AND
IR.C_STATUS <> 'ER' AND
IR.C_INVL_ROLE = 'NIS' AND
POLICY.N_POLICY = RPAD(N_POLICY_IN,12)
ORDER BY D_DATE DESC;
IF*claim_summary_cursor%FOUND THEN
claim_summary_cursor := claiminq_proc.arch_claim_inquiry.Get_summary_by_policy_number(N_POLICY_IN) ;
End If;
return claim_summary_cursor;
END;
