command_SYS.mail must be declared

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);

  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;
  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, 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,

                 NULL,

                 NULL,

                 'CSB DO POCO COM ANOMALIA',

                 NULL,

                 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,

                 NULL,

                 NULL,

                 'CSB DO POCO COM ANOMALIA',

                 NULL,

                 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,

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;

lopespetro wrote:

dsj wrote:

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;

lopespetro wrote:

lopespetro wrote:

dsj wrote:

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 Everyone.

Any idea how to solve this situation?

Tks for attention!