command_SYS.mail must be declared

Hi everyone.

Trying to send e-mail based on event action. I’m getting this error. Could someone help please?

Running in plsql it says that the command_sys.mail myst be declared.

--teste
DECLARE

mail_ VARCHAR2(100);
poco_ VARCHAR2(50);
obs_ VARCHAR2(2000);

CURSOR get_mail IS
SELECT E.VALUE
FROM IFSPET.fnd_user_property E
WHERE E.identity IN (select a.person_id
from ifspet.SURVEY_ACCESS a
WHERE A.survey_id = 'CHK-CSB-001')
AND E.NAME = 'SMTP_MAIL_ADDRESS';

CURSOR get_poco IS
SELECT A.ANSWER
FROM IFSPET.SURVEY_ANSWER A
where a.EMP_NO = '000048'
and a.DATE_CREATED =
TO_DATE('17/10/2021 09:50:21', 'DD/MM/YYYY HH:MI:SS')
and a.SURVEY_ID = 'CHK-CSB-001'
AND A.QUESTION_ID = 3584;

CURSOR get_obs IS
SELECT a.REMARK
FROM IFSPET.SURVEY_ANSWER A
where a.EMP_NO = '000048'
and a.DATE_CREATED =
TO_DATE('17/10/2021 09:50:21', 'DD/MM/YYYY HH:MI:SS')
and a.SURVEY_ID = 'CHK-CSB-001'
AND A.QUESTION_ID = 3585;

BEGIN
OPEN get_mail;
FETCH get_mail
INTO mail_;
CLOSE get_mail;

OPEN get_poco;
FETCH get_poco
INTO poco_;
CLOSE get_poco;

OPEN get_obs;
FETCH get_obs
INTO obs_;
CLOSE get_obs;

command_SYS.mail('#USER_EMAIL#',
mail_ ||
' Olá, prezados, Foi identificado uma anomalia no Poço ' ||
poco_ || ' com a seguinte observação: ' || obs_ ||
'. Favor avaliar a situação.
Obrigado' || CHR(13) || CHR(13) || 'Favor verificar.' ||
CHR(13) || CHR(13) || CHR(13) || CHR(13) ||
'Atenciosamente,' || CHR(13) || '#PERSON_NAME#',
NULL,
NULL,
'MAIL_SENDER1');
END;

Page 1 / 1

Hi, I’m not getting the error anymore just by inserting the owner before command.

ifspet.COMMAND_SYS.Mail

I’m testing but not getting the e-mail in my inbox.

Am I missing something?

ifspet.COMMAND_SYS.Mail('IFS Applications!',
'test@test.com.br',
'test@test.com.br',
NULL,
NULL,
'Subject',
'body',
NULL,
NULL,
'test@test.com.br');

============================================================

--teste
DECLARE

mail_ VARCHAR2(2000);
poco_ VARCHAR2(50);
obs_ VARCHAR2(2000);

BEGIN

OPEN get_mail;
loop
fetch get_mail into mail_;
exit when get_mail%notfound;
end loop;
close get_mail;

OPEN get_poco;
FETCH get_poco
INTO poco_;
CLOSE get_poco;

OPEN get_obs;
FETCH get_obs
INTO obs_;
CLOSE get_obs;

--dbms_output.put_line(mail_ || obs_ || poco_);

ifspet.COMMAND_SYS.Mail('IFS Applications!',
'test@test.com.br',
'test@test.com.br',
NULL,
NULL,
'Subject',
'body',
NULL,
NULL,
'test@test.com.br');

END;

Hi @lopespetro,

Please check application messages OUT1 queue and see if you can find relevant application message for sending mail. Any errors while sending the email is displayed there.

Regards,

Damith

Hi @lopespetro,

Please check application messages OUT1 queue and see if you can find relevant application message for sending mail. Any errors while sending the email is displayed there.

Regards,

Damith

Hi, dsj, i’ve got it working using this example from plsql.

ifspet.COMMAND_SYS.Mail('IFS Applications!',

‘’,

' Olá, prezados ' || poco_ || 'Obrigado' || obs_ || CHR(13) || CHR(13) || 'Favor verificar.' ||

CHR(13) || CHR(13) || CHR(13) || CHR(13) ||

'Atenciosamente,' || CHR(13),

NULL,

'CSB DO POCO COM ANOMALIA',

NULL,

'IFS');

But from event action is not working. I thing there is some thing wrong in my code.

if I just run the command sys it works, but not using the code above.

I have a survey just with 2 questions?

if the second is “YES”, the user must put a remark and I need to send e-mail from event action inform the first and second answer and also the remark.

Running the code in plsql the dbms returns the correct value.

poco_ is the first answer;

obs_ is the remark;

mail_ is the users list e-mail in survey access tab;

Tks for attention.

DECLARE

mail_ VARCHAR2(2000);

poco_ VARCHAR2(500);

obs_ VARCHAR2(2000);

CURSOR get_mail IS

SELECT E.VALUE

FROM IFSPET.fnd_user_property E

WHERE E.identity IN

(select a.person_id

from ifspet.SURVEY_ACCESS a

WHERE A.survey_id = 'CHK-CSB-001')

AND E.NAME = 'SMTP_MAIL_ADDRESS';

CURSOR get_poco IS

SELECT b.ANSWER

FROM IFSPET.SURVEY_ANSWER b

where b.EMP_NO = '&NEW:EMP_NO'

and b.DATE_CREATED = TO_DATE('&NEW:DATE_CREATED', 'DD/MM/YYYY HH24:MI:SS')

and b.SURVEY_ID = 'CHK-CSB-001'

AND A.QUESTION_ID = 3584;

CURSOR get_obs IS

SELECT c.REMARK

FROM IFSPET.SURVEY_ANSWER c

where c.EMP_NO = '&NEW:EMP_NO'

and c.DATE_CREATED = TO_DATE('&NEW:DATE_CREATED', 'DD/MM/YYYY HH24:MI:SS')

and c.SURVEY_ID = 'CHK-CSB-001'

AND c.QUESTION_ID = 3585;

BEGIN

OPEN get_mail;

loop

fetch get_mail into mail_;

exit when get_mail%notfound;

end loop;

close get_mail;

OPEN get_poco;

FETCH get_poco

INTO poco_;

CLOSE get_poco;

OPEN get_obs;

FETCH get_obs

INTO obs_;

CLOSE get_obs;

ifspet.COMMAND_SYS.Mail('IFS Applications!',

mail_,

' Olá, prezados ' || poco_ || 'Obrigado' || obs_ || CHR(13) || CHR(13) || 'Favor verificar.' ||

CHR(13) || CHR(13) || CHR(13) || CHR(13) ||

'Atenciosamente,' || CHR(13),

NULL,

'CSB DO POCO COM ANOMALIA',

NULL,

'IFS');

END;

Hi @lopespetro ,

If it works from plsql, it could be the event action conditions.

Remove conditions and see if it works.

specially check survey_answer_tab for what’s the value recoded for ANSWER column. It could be a different value which is in the table than it’s shown in the client.

Regards,

Damith

Hi @lopespetro ,

If it works from plsql, it could be the event action conditions.

Remove conditions and see if it works.

specially check survey_answer_tab for what’s the value recoded for ANSWER column. It could be a different value which is in the table than it’s shown in the client.

Regards,

Damith

Hi,

one thing I could see is the date format in tab is datetime, but using the e-mail event format it shows just date.

The conditions are OK, i’ve already tested them.

Maybe the connections:

I’m taking a look at this and see if it works.

Tks a lot sir.

CURSOR get_obs IS

SELECT c.REMARK

FROM IFSPET.SURVEY_ANSWER c

where c.EMP_NO = '&NEW:EMP_NO'

and c.DATE_CREATED = TO_DATE('&NEW:DATE_CREATED', 'DD/MM/YYYY HH24:MI:SS')

and c.SURVEY_ID = 'CHK-CSB-001'

AND c.QUESTION_ID = 3585;

Hi @lopespetro ,

If it works from plsql, it could be the event action conditions.

Remove conditions and see if it works.

specially check survey_answer_tab for what’s the value recoded for ANSWER column. It could be a different value which is in the table than it’s shown in the client.

Regards,

Damith

Hi,

one thing I could see is the date format in tab is datetime, but using the e-mail event format it shows just date.

The conditions are OK, i’ve already tested them.

Maybe the connections:

I’m taking a look at this and see if it works.

Tks a lot sir.

CURSOR get_obs IS

SELECT c.REMARK

FROM IFSPET.SURVEY_ANSWER c

where c.EMP_NO = '&NEW:EMP_NO'

and c.DATE_CREATED = TO_DATE('&NEW:DATE_CREATED', 'DD/MM/YYYY HH24:MI:SS')

and c.SURVEY_ID = 'CHK-CSB-001'

AND c.QUESTION_ID = 3585;

Hi,

I think what I need it’s not possible this way.

I’m trying to get the first answer.

The key a have is emp_no, survey ID and date created, but I think the system saves the info at the same time and not getting the first info before.

The trigger is based on the second answer.

What you guys think? is this logical correct or Am I missing something?

DECLARE

poco_ VARCHAR2(2000);

CURSOR get_poco IS
SELECT b.ANSWER
FROM IFSPET.SURVEY_ANSWER b
where b.EMP_NO = '&NEW:EMP_NO'
and b.DATE_CREATED = TO_DATE('&NEW:DATE_CREATED', 'DD/MM/YYYY HH24:MI:SS')
and b.SURVEY_ID = 'CHK-CSB-004';

BEGIN

OPEN get_poco;
FETCH get_poco
INTO poco_;
CLOSE get_poco;

Hi @lopespetro ,

If it works from plsql, it could be the event action conditions.

Remove conditions and see if it works.

specially check survey_answer_tab for what’s the value recoded for ANSWER column. It could be a different value which is in the table than it’s shown in the client.

Regards,

Damith

Hi,

one thing I could see is the date format in tab is datetime, but using the e-mail event format it shows just date.

The conditions are OK, i’ve already tested them.

Maybe the connections:

I’m taking a look at this and see if it works.

Tks a lot sir.

CURSOR get_obs IS

SELECT c.REMARK

FROM IFSPET.SURVEY_ANSWER c

where c.EMP_NO = '&NEW:EMP_NO'

and c.DATE_CREATED = TO_DATE('&NEW:DATE_CREATED', 'DD/MM/YYYY HH24:MI:SS')

and c.SURVEY_ID = 'CHK-CSB-001'

AND c.QUESTION_ID = 3585;

Hi,

I think what I need it’s not possible this way.

I’m trying to get the first answer.

The key a have is emp_no, survey ID and date created, but I think the system saves the info at the same time and not getting the first info before.

The trigger is based on the second answer.

What you guys think? is this logical correct or Am I missing something?

DECLARE

poco_ VARCHAR2(2000);

BEGIN

OPEN get_poco;
FETCH get_poco
INTO poco_;
CLOSE get_poco;

Hi Everyone.

Any idea how to solve this situation?

Tks for attention!

Hi all. I could that this situation is not possible because I’m trying to get the info of one line and run the event trigged by another line.

I’ll take a look if I can do this using a schedule task after all the lines are saved in DB.

Tks a lot for attention.

Did you find what you're looking for? If not:

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded