Email Notification Using UDT(HRMS)

To send email notification for employees by submitting a concurrent program is as fallows:

1.Define the User Defined Table as
XXHR_EMAIL_NOTIFICATIONS (for example)

Add the table values:
e.g:

Under rows for table structure choose the BU to send the email.

Update the user table values

where 60 is the number of days i am passing as parameter in my package to validate the email to be sent.

value looks like :

60-XXHR_JGE_SELF_VISA_MSG
And create the package to select employees whose document going to expire in 60 days

in the package try to call the smtp email server,email list and messgae to be called.

For the messgae login into application developer->choose messages->

Dear &EMP_NAME,

Please be advised that the residence visa of your &DEP_TYPE ( &DEP_NAME) is expiring on &DOC_DATE_TO, kindly renew the same prior to expiry.

Regards,

Human Resources Team

parameters will be passed in query as below:

CREATE OR REPLACE PACKAGE BODY APPS.xxhr_email_notif_pkg

AS

PROCEDURE email_config(

    msg_from      IN VARCHAR2,

    msg_to        IN VARCHAR2,

    msg_cc        IN VARCHAR2,

    msg_subject   IN VARCHAR2,

    msg_text      IN VARCHAR2,

    p_dir_name    IN VARCHAR2,

    p_attach_file IN VARCHAR2,

    x_status OUT VARCHAR2,

    p_err_msg OUT VARCHAR2 )

AS

  c UTL_TCP.connection;

  rc             INTEGER;

  port           NUMBER                  := 25;

  TIMEZONE       CONSTANT VARCHAR2 (4)   := APPS.XXHR_PAY_FUNCTIONS_PKG.global_name (TRUNC(sysdate), 'XXHR_EMAIL_TIMEZONE');

  EMAILSERVER    CONSTANT VARCHAR2 (30)  := APPS.XXHR_PAY_FUNCTIONS_PKG.global_name (TRUNC(sysdate), 'XXHR_EMAIL_SERVER');

  GC_MAIL_SERVER CONSTANT VARCHAR2 (250) := APPS.XXHR_PAY_FUNCTIONS_PKG.global_name (TRUNC(sysdate), 'XXHR_SMTP_SERVER');

  gc_port_number CONSTANT VARCHAR2 (250) := apps.xxhr_pay_functions_pkg.GLOBAL_NAME (TRUNC(sysdate), 'XXHR_SMTP_PORT');

  conn UTL_SMTP.connection;

  crlf      VARCHAR2 (2) := CHR (13) || CHR (10);

  l_subject VARCHAR2 (400);

  v_src_loc BFILE;

  l_buffer RAW (54);

  l_amount BINARY_INTEGER := 54;

  l_pos INTEGER           := 1;

  l_blob BLOB             := EMPTY_BLOB;

  l_blob_len INTEGER;

  v_amount   INTEGER;

  v_cnt      INTEGER;

  v_cc_list varchar2(1000);

cursor csr_cc_list (c_cc_list in varchar2) is

  select meaning

  from applsys.fnd_lookup_values

  where lookup_type =c_cc_list

  and language ='US'

  and enabled_flag ='Y';

cursor csr_cc_list_con (c_cc_list in varchar2) is

select max(ltrim(sys_connect_by_path(meaning,','),',')) cc_list

 from (select lookup_type,meaning,

   row_number() over(partition by lookup_type order by meaning) rn

       from applsys.fnd_lookup_values

where lookup_type   = c_cc_list

  and language      = 'US'

  and enabled_flag ='Y'

  )

 start with rn = 1

 connect by prior rn = rn -1

         and prior lookup_type = lookup_type

 group by lookup_type;

BEGIN

  l_subject        := msg_subject;

  IF p_attach_file IS NULL OR p_dir_name IS NULL THEN

    BEGIN

      conn := UTL_SMTP.open_connection (gc_mail_server, gc_port_number);

      UTL_SMTP.helo (conn, emailserver);

      UTL_SMTP.mail (conn, msg_from);

      UTL_SMTP.rcpt (conn, msg_to);

    fnd_file.put_line (fnd_file.LOG, 'Msg CC: '|| msg_cc);

      for i in csr_cc_list(msg_cc) loop

          UTL_SMTP.rcpt (conn, i.meaning);

          v_cnt := 1;

      end loop;

      for j in csr_cc_list_con(msg_cc) loop

          v_cc_list := j.cc_list;

      end loop;

    fnd_file.put_line (fnd_file.LOG, 'CC List: '|| v_cc_list);

    

      UTL_SMTP.DATA (conn, 'MIME-Version: 1.0' || CHR (13) || CHR (10) || 'Content-type: text/html' || CHR (13) || CHR (10) || 'Date: ' || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss') || '' || TIMEZONE || crlf || 'From:' || msg_from || crlf || 'Subject: ' || l_subject || crlf || 'To: ' || msg_to || crlf || 'Cc: ' || v_cc_list ||' '||crlf ||crlf ||msg_text );

      UTL_SMTP.quit (conn);

      x_status := 'S';

    EXCEPTION

    WHEN OTHERS THEN

      UTL_SMTP.quit (conn);

      p_err_msg := 'Unable to send e-mail message from pl/sql because of: ' || SQLERRM;

      x_status  := 'E';

    END;

  ELSE

    BEGIN

      v_src_loc := BFILENAME (p_dir_name, p_attach_file);

      DBMS_LOB.OPEN (v_src_loc, DBMS_LOB.lob_readonly); --Read the file

      DBMS_LOB.createtemporary (l_blob, TRUE);

      IF DBMS_LOB.fileexists (v_src_loc) = 1 THEN

        --Create temporary LOB to store the file.

        v_amount := DBMS_LOB.getlength (v_src_loc); --Amount to store.

        DBMS_LOB.loadfromfile (l_blob, v_src_loc, v_amount);

        -- Loading from file into temporary LOB

        l_blob_len := DBMS_LOB.getlength (l_blob);

        /*UTL_SMTP related coding. */

        conn := UTL_SMTP.open_connection (HOST => gc_mail_server);

        UTL_SMTP.helo (conn, gc_mail_server);

        UTL_SMTP.mail (conn, msg_from);

        UTL_SMTP.rcpt (conn, msg_to);

        UTL_SMTP.open_data (conn);

        UTL_SMTP.write_data (conn, 'From: ' || msg_from || UTL_TCP.crlf );

        UTL_SMTP.write_data (conn, 'To: ' || msg_to || UTL_TCP.crlf);

        UTL_SMTP.write_data (conn, 'Subject: ' || l_subject || UTL_TCP.crlf );

        --MIME header.

        UTL_SMTP.write_data (conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);

        UTL_SMTP.write_data (conn, 'Content-Type: multipart/mixed; ' || UTL_TCP.crlf );

        UTL_SMTP.write_data (conn, ' boundary= "' || 'DW.SECBOUND' || '"' || UTL_TCP.crlf );

        UTL_SMTP.write_data (conn, UTL_TCP.crlf);

        -- Mail Body

        UTL_SMTP.write_data (conn, '--' || 'DW.SECBOUND' || UTL_TCP.crlf );

        UTL_SMTP.WRITE_DATA (CONN, 'Content-Type: text/plain;' || UTL_TCP.crlf );

        UTL_SMTP.write_data (conn, ' charset=US-ASCII' || UTL_TCP.crlf);

        UTL_SMTP.write_data (conn, UTL_TCP.crlf);

        UTL_SMTP.write_data (conn, msg_text || UTL_TCP.crlf);

        UTL_SMTP.write_data (conn, UTL_TCP.crlf);

        -- Mail Attachment

        UTL_SMTP.write_data (conn, '--' || 'DW.SECBOUND' || UTL_TCP.crlf );

        UTL_SMTP.write_data (conn, 'Content-Type: application/octet-stream' || UTL_TCP.crlf );

        UTL_SMTP.write_data (conn, 'Content-Disposition: attachment; ' || UTL_TCP.crlf );

        UTL_SMTP.write_data (conn, ' filename="' || p_attach_file || '"' || --My filename

        UTL_TCP.crlf );

        UTL_SMTP.write_data (conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf );

        UTL_SMTP.write_data (conn, UTL_TCP.crlf);

        /* Writing the BLOL in chunks */

        WHILE l_pos < l_blob_len

        LOOP

          DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);

          UTL_SMTP.write_raw_data (conn, UTL_ENCODE.base64_encode (l_buffer) );

          UTL_SMTP.write_data (conn, UTL_TCP.crlf);

          l_buffer := NULL;

          l_pos    := l_pos + l_amount;

        END LOOP;

        UTL_SMTP.write_data (conn, UTL_TCP.crlf);

        -- Close Email

        UTL_SMTP.write_data (conn, '--' || 'DW.SECBOUND' || '--' || UTL_TCP.crlf );

        UTL_SMTP.write_data (conn, UTL_TCP.crlf || '.' || UTL_TCP.crlf);

        UTL_SMTP.close_data (conn);

        UTL_SMTP.quit (conn);

        DBMS_LOB.freetemporary (l_blob);

        DBMS_LOB.fileclose (v_src_loc);

        x_status := 'S';

      ELSE

        p_err_msg := 'File Not Exists';

        x_status  := 'E';

      END IF;

    EXCEPTION

    WHEN OTHERS THEN

      p_err_msg := p_err_msg || ' Unable to send e-mail message from pl/sql because of: ' || SQLERRM;

      x_status  := 'E';

      UTL_SMTP.quit (conn);

      DBMS_LOB.freetemporary (l_blob);

      DBMS_LOB.fileclose (v_src_loc);

      RAISE;

    END;

  END IF;

EXCEPTION

WHEN OTHERS THEN

  p_err_msg := p_err_msg || ' Unable to send e-mail message from pl/sql because of: ' || SQLERRM;

  x_status  := 'E';

END email_config;

PROCEDURE send_email(

    errbuf OUT VARCHAR2,

    retcode OUT NUMBER,

    p_source_table_name IN VARCHAR2,

    p_process_type      IN VARCHAR2,

    p_ovrr_email        IN VARCHAR2,

    p_date              IN VARCHAR2 )

AS

  CURSOR c_org (p_eff_date IN DATE)

  IS

    SELECT put.user_table_name,

      puc.user_column_name,

      purf.row_low_range_or_name org_name,

      pucif.VALUE msg_name,

      haou.organization_id,

      haou.TYPE org_type,

      apps.xxhr_pay_functions_pkg.get_table_value

      (

            haou.business_group_id

           ,p_source_table_name

           ,'CC List'

           ,purf.row_low_range_or_name

           ,p_eff_date) c_msg_cc

    FROM pay_user_tables put,

      pay_user_columns puc,

      pay_user_rows_f purf,

      pay_user_column_instances_f pucif,

      apps.hr_all_organization_units haou

    WHERE put.user_table_id      = puc.user_table_id

    AND put.user_table_id        = purf.user_table_id

    AND puc.user_column_id       = pucif.user_column_id

    AND purf.user_row_id         = pucif.user_row_id

    AND UPPER (TRIM (haou.NAME)) = UPPER (TRIM (purf.row_low_range_or_name))

    AND p_eff_date BETWEEN purf.effective_start_date AND purf.effective_end_date

    AND p_eff_date BETWEEN pucif.effective_start_date AND pucif.effective_end_date

    AND put.user_table_name = p_source_table_name

      --'XXHR_EMAIL_NOTIFICATION'

      --   AND UPPER (TRIM (puc.user_column_name)) =

      --                                      UPPER (TRIM (p_process_type));

    AND UPPER (TRIM (puc.user_column_name)) LIKE DECODE (UPPER (TRIM (p_process_type)), 'DOCUMENT OF RECORD', 'DOR%','PROBATION PERIOD END','%PROB%', UPPER (TRIM (p_process_type)) );

  lv_request_id NUMBER;

  l_status      VARCHAR2 (1);

  lc_message    VARCHAR2 (4000);

  rec_emp emp_info;

  lv_err_msg       VARCHAR2 (4000);

  lv_msg_token     VARCHAR2 (500);

  lv_from_address  VARCHAR2 (500);

  lv_msg_cc        VARCHAR2(200);

  lv_eff_date      DATE;

  lv_instance_name VARCHAR2 (100);

  lv_email_address VARCHAR2 (500);

  lv_valid_days    NUMBER;

  lv_msg_name      VARCHAR2 (500);

  lv_dir_name      VARCHAR2 (500);

  lv_attach_file   VARCHAR2 (500);

  lv_doc_type      VARCHAR2 (500);

  lv_subject       VARCHAR2 (2000);

CURSOR c_probation ( p_org_id IN NUMBER, p_org_type IN VARCHAR2, p_eff_date IN DATE,p_valid_days IN NUMBER )

    IS

      SELECT p_process_type process_type,

        papf.employee_number employee_number,

        papf.person_id,

        TO_NUMBER (NULL) absence_attendance_id,

        papf.email_address,

        papf.sex,

        INITCAP (RTRIM (NVL (papf.known_as, papf.full_name), '-') ) employee_name,

        NULL leave_type,

        TO_DATE (NULL) leave_start_date,

        TO_DATE (NULL) leave_end_date,

        TO_DATE (NULL) sch_resumption_date,

        date_of_birth,

        NULL document_type,

        NULL document_number,

         paaf.DATE_PROBATION_END doc_date_to,

        NULL dependent_name,

        NULL dependent_type 

      FROM apps.per_people_f papf,

        APPS.PER_ASSIGNMENTS_F PAAF,

        HR.hr_all_organization_units haou

      WHERE 1                        = 1

      AND papf.person_id             = paaf.person_id

      AND paaf.organization_id       = haou.organization_id

      AND papf.current_employee_flag = 'Y'

      AND paaf.primary_flag          = 'Y'

      AND paaf.assignment_type       = 'E'

      AND p_eff_date BETWEEN papf.effective_start_date AND papf.effective_end_date

      AND p_eff_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date

        --AND papf.employee_number = '103356'

      --AND TO_CHAR(date_of_birth,'DD-MON') = TO_CHAR(p_eff_date,'DD-MON')

      and paaf.DATE_PROBATION_END = p_eff_date + p_valid_days 

      AND p_org_type                     IN ('BU', 'DEP', 'HLD')

      AND paaf.organization_id            = ANY

        (SELECT e.organization_id_child

        FROM per_org_structure_elements e

        WHERE e.org_structure_version_id = ANY

          (SELECT org_structure_version_id

          FROM per_org_structure_versions

          WHERE organization_structure_id = ANY

            (SELECT organization_structure_id

            FROM per_organization_structures

            WHERE primary_structure_flag = 'Y'

            )

        AND p_eff_date BETWEEN date_from AND NVL (date_to, p_eff_date )

          )

          CONNECT BY e.organization_id_parent = PRIOR e.organization_id_child

        AND e.org_structure_version_id        = ANY

          (SELECT org_structure_version_id

          FROM per_org_structure_versions

          WHERE organization_structure_id = ANY

            (SELECT organization_structure_id

            FROM per_organization_structures

            WHERE primary_structure_flag = 'Y'

            )

          AND p_eff_date BETWEEN date_from AND NVL (date_to, p_eff_date )

          )

         START WITH e.organization_id_parent = p_org_id

        AND e.org_structure_version_id        = ANY

          (SELECT org_structure_version_id

          FROM per_org_structure_versions

          WHERE organization_structure_id = ANY

            (SELECT organization_structure_id

            FROM per_organization_structures

            WHERE primary_structure_flag = 'Y'

            )

          AND p_eff_date BETWEEN date_from AND NVL (date_to, p_eff_date )

          )

        )

CURSOR c_dor_type (p_msg VARCHAR2)
          IS
            SELECT a.COLUMN_VALUE FROM TABLE (xxhr_udfs_pkg.in_list (p_msg)) a;
        BEGIN
          lv_request_id := fnd_global.conc_request_id;
          lv_eff_date   := NVL (fnd_date.canonical_to_date (p_date), TRUNC (SYSDATE));
          SELECT apps.xxhr_pay_functions_pkg.GLOBAL_NAME (lv_eff_date, 'XXHR_EMAIL_NOTIF_ADDRESS' )
          INTO lv_from_address
          FROM DUAL;
          SELECT NAME INTO lv_instance_name FROM fnd_apps_system;
          FOR i IN c_org (lv_eff_date)
          LOOP
          lv_msg_cc := i.c_msg_cc;
IF p_process_type = 'Probation Period End' THEN
                FOR k IN c_dor_type (i.msg_name)
              LOOP
                SELECT SUBSTR (k.COLUMN_VALUE, 1, INSTR (k.COLUMN_VALUE, '-') - 1 ),
                  SUBSTR (k.COLUMN_VALUE, INSTR (k.COLUMN_VALUE, '-')         + 1, LENGTH (k.COLUMN_VALUE) )
                INTO lv_valid_days,
                  lv_msg_name
                FROM DUAL;
                    IF lv_msg_name LIKE '%PROB%' THEN
                        LV_SUBJECT  := 'End Of Probation Period';
                 END IF;
                 FOR rec_emp IN c_probation (i.organization_id, i.org_type, lv_eff_date,lv_valid_days )
                 LOOP
                    build_message (rec_emp, lv_msg_name, lc_message);
                   --  fnd_file.put_line (fnd_file.LOG, ' dep, - ' || rec_emp.employee_name );
                    IF lv_instance_name = 'ISWD' THEN
                      lv_email_address := NVL (p_ovrr_email,'example@gmail.com');--rec_emp.email_address);
                    ELSE
                      BEGIN
                        SELECT NVL (p_ovrr_email, description)
                        INTO lv_email_address
                        FROM fnd_lookup_values_vl
                        WHERE lookup_type = 'XXHR_TEST_CONTACTS'
                        AND enabled_flag  = 'Y'
                        AND tag           = lv_instance_name
                        AND lookup_code LIKE '%EMAIL%'
                        AND TRUNC (SYSDATE) BETWEEN start_date_active AND NVL (end_date_active, TRUNC (SYSDATE) );
                      EXCEPTION
                      WHEN OTHERS THEN
                        lv_email_address := NULL;
                      END;
                    END IF;
                    IF lv_email_address IS NOT NULL THEN
                      xxhr_email_notif_pkg.email_config (msg_from => lv_from_address, msg_to => lv_email_address,msg_cc=>lv_msg_cc, msg_subject => lv_subject, msg_text => lc_message, p_dir_name => NULL, p_attach_file => NULL, x_status => l_status, p_err_msg => lv_err_msg );
                      IF l_status   = 'S' THEN
                        lv_err_msg := 'Email Notification has been sent to employee';
                        insert_log (rec_emp, i.org_name, i.org_type, lv_request_id, lv_email_address, lv_err_msg );
                        fnd_file.put_line (fnd_file.LOG, ' Email Notification sent to employee - ' || rec_emp.employee_number );
                        fnd_file.put_line (fnd_file.output, ' Email Notification sent to employee - ' || rec_emp.employee_number );
                      ELSE
                        lv_err_msg := 'Error occurred while sending Email Notification to the employee';
                        insert_log (rec_emp, i.org_name, i.org_type, lv_request_id, lv_email_address, lv_err_msg );
                        fnd_file.put_line (fnd_file.LOG, ' Error :' || lv_err_msg );
                        fnd_file.put_line (fnd_file.output, ' Error :' || lv_err_msg );
                      END IF;
                    ELSE
                      lv_err_msg := 'No Email Address found for the employee';
                      insert_log (rec_emp, i.org_name, i.org_type, lv_request_id, lv_email_address, lv_err_msg );
                    END IF;
                    END LOOP;
                    lv_email_address := NULL;
                    --lv_subject       := NULL;
               
                      --  lv_valid_days := NULL;
                      ---  lv_msg_name   := NULL;
  END LOOP;
            -- *********************************

IF lv_msg_name LIKE '%PROB%' THEN
LV_SUBJECT  := 'Probation Period End';
End If;
 PROCEDURE insert_log(
          p_rec        IN emp_info,
          p_org_name   IN VARCHAR2,
          p_org_type   IN VARCHAR2,
          p_request_id IN NUMBER,
          p_ovrr_email IN VARCHAR2,
          p_err_msg    IN OUT VARCHAR2 )
      AS
      BEGIN
        INSERT
        INTO xxhr.xxhr_email_notif_log
          (
            org_name,
            org_type,
            process_type,
            employee_number,
            person_id,
            employee_name,
            absence_start_date,
            absence_end_date,
            absence_attendance_id,
            absence_type,
            sch_resumption_date,
            email_address,
            override_email_address,
            mail_sent_on,
            conc_request_id,
            comments,
            date_of_birth,
            document_type,
            document_number,
            doc_date_to,
            dependent_name,
            dependent_type,
            attribute1,
            attribute2,
            attribute3,
            attribute4,
            attribute5,
            last_update_date,
            last_updated_by,
            last_update_login,
            created_by,
            creation_date
          )
          VALUES
          (
            p_org_name,
            p_org_type,
            p_rec.process_type,
            p_rec.employee_number,
            p_rec.person_id,
            p_rec.employee_name,
            p_rec.leave_start_date,
            p_rec.leave_end_date,
            p_rec.absence_attendance_id,
            p_rec.leave_type,
            p_rec.sch_resumption_date,
            p_rec.email_address,
            p_ovrr_email,
            SYSDATE,
            p_request_id,
            p_err_msg,
            p_rec.date_of_birth,
            p_rec.document_type,
            p_rec.document_number,
            p_rec.doc_date_to,
            p_rec.dependent_name,
            p_rec.dependent_type,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            SYSDATE,
            fnd_global.user_id,
            fnd_global.login_id,
            fnd_global.user_id,
            SYSDATE
          );
        COMMIT;
      EXCEPTION
      WHEN OTHERS THEN
        p_err_msg := 'Error In Insert Into Log' || SQLERRM;
        -- ROLLBACK;
      END;
    PROCEDURE build_message
      (
        p_rec      IN emp_info,
        p_msg_name IN VARCHAR2,
        p_message OUT VARCHAR2
      )
    AS
    BEGIN
IF p_rec.process_type = 'Probation Period End' THEN
        fnd_message.set_name ('XXHR', p_msg_name);
        fnd_message.set_token ('EMP_NAME', p_rec.employee_name);
        fnd_message.set_token ('PROB_DATE_TO', TO_CHAR (p_rec.doc_date_to, 'DD-MON-RRRR') );

  p_message := fnd_message.get;
      END IF;
    END;
  END xxhr_email_notif_pkg;
/


Register the package as concurrent program

 the effective date is passed as sysdate and fallow as above for other parameter.

Go through the packge understand and modify the smpt as per and try testing the Email Notifications.