I want to select all employees that the contract is about to expire then if I get one employee or more then I want to send notification to their managers using email , SMS ,ESS or mobile after checking the value of them and must equal yes then I call the sending function I tried to do that using cursor for loop but I got more than one error can anyone help me?
create or replace function NOTIFICATION_SENDING(P_NOTIF_ID NUMBER) return NUMBER
IS
V_EMP NUMBER;
V_USER NUMBER;
V_EMAIL VARCHAR2(1);
V_SMS VARCHAR2(1);
V_ESS VARCHAR2(1);
V_MOBILE VARCHAR2(1);
CURSOR C1 IS
SELECT EMP_ID FROM HR_EMP_CONTRACT_HEADER
where months_between(EDATE,sysdate) >=1
and months_between(EDATE,sysdate) <= 3;
CURSOR C2 IS
SELECT USER_ID from NL_NOTIF_EMP_HEADER
where NOTIF_ID = P_NOTIF_ID;
BEGIN
for i in C1 LOOP
V_EMP := i.EMP_ID;
END LOOP;
IF V_EMP is not null THEN
for i in C2 LOOP
V_USER := i.USER_ID;
END LOOP;
IF V_USER IS NOT NULL THEN
SELECT EMAIL , SMS , ESS , MOBILE
INTO V_EMAIL , V_SMS , V_ESS , V_MOBILE
from NL_NOTIF_EMP_HEADER
where NOTIF_ID = P_NOTIF_ID;
IF V_EMAIL = 'Y' THEN
NOTIF_EMAIL_SENDING(P_NOTIF_ID ,V_USER );
END IF;
IF V_SMS = 'Y' THEN
NOTIF_SMS_SENDING(P_NOTIF_ID ,V_USER );
END IF;
IF V_ESS = 'Y' THEN
NOTIF_ESS_SENDING(P_NOTIF_ID ,V_USER );
END IF;
IF V_MOBILE = 'Y' THEN
NOTIF_MOBILE_SENDING(P_NOTIF_ID ,V_USER );
END IF;
END IF;
END IF;
END NOTIFICATION_SENDING;
This the code I used
As of your code:
user_id
in cursor and all other values in standalone select
statement; seems to be unnecessaryif
s suggest that manager wants to be (and will be) notified in many possible ways. If that's not what you wanted, consider using IF-ELSIF insteadTo me, it is unclear what nl_notif_emp_header
contains nor what p_notif_id
parameter represents. I think that it contains only one row for each p_notif_id
value, saying that manager identified by user_id
wants to get notification by ANY (can be none, one, or all four) way available.
As you aren't returning anything, I'd say that procedure suits better.
In my example, I'm
cur_n
) through nl_notif_emp_header
, just to avoid declaration of 5 local variables and use cursor variable insteadcur_e
) through employees that satisfy condition and - depending on all ways of notification - sending it to manager
notif_xxx_sending
procedures do. I'd expect them to accept manager (who is to be notified) and employee info (so that manager knows which employee it is about), while you're passing p_notif_id
parameter value (?!? Why?) and manager ID (that's OK). Therefore, I added yet another parameter in my example; can't tell whether that's OK or notSo:
create or replace procedure NOTIFICATION_SENDING (P_NOTIF_ID in number)
is
begin
-- CUR_N returns only one row, but - instead of declaring bunch of local
-- variables, I'm using a one-time cursor loop
for cur_n in (select user_id, email, sms, ess, mobile
from nl_notif_emp_header
where notif_id = p_notif_id
)
loop
-- employees that satisfy condition
for cur_e in (select emp_id
from hr_emp_contract_header
where months_between(edate, sysdate) between 1 and 3
)
loop
if cur_n.email = 'Y' then
notif_email_sending (p_notif_id, cur_n.user_id, cur_e.emp_id);
end if;
if cur_n.sms = 'Y' then
notif_sms_sending (p_notif_id, cur_n.user_id, cur_e.emp_id);
end if;
-- etc., for all ways of notification
end loop;
end loop;
end;
/