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.

Document of records for Contacts

Query to list the document of records for Contacts-Can modfiy the query and use to list only contacts for the employee:

select
hdei.document_number,
         papfc.full_name,
papf.full_name Employee_name,PAPF.EMAIL_ADDRESS
,hdt.system_document_type document_type
    , to_char(hdei.date_to,'DD-MON-YYYY')date_to
    FROM
    hr_document_types hdt,
    hr_document_extra_info hdei,
    per_ALL_people_f papf,
   per_ALL_people_f papfc,
    per_contact_relationships pcr
WHERE
  hdt.document_type_id= hdei.document_type_id
and papfc.person_id=pcr.contact_PERSON_ID
and papf.person_id =pcr.PERSON_ID
and  papfc.person_id=hdei.PERSON_Id
  --and papf.employee_number='200343'
  -- and papf.business_group_id=101
and papf.current_employee_flag = 'Y'
-- and pcr.DEPENDENT_FLAG= 'Y'
  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN papfc.effective_start_date AND papfc.effective_end_date
--  and trunc(sysdate) between hdei.date_from and hdei.date_to
-- and trunc(sysdate) between pcr.date_start and pcr.date_end
--and hdei.DATE_to  =ADD_MONTHS(TRUNC(SYSDATE), 2 )
group  by
  hdei.document_number,
         papfc.full_name,
         papf.full_name,PAPF.EMAIL_ADDRESS
,hdt.system_document_type
    ,hdei.date_from
    ,hdei.date_to,
     hdei.document_number,
    pcr.contact_type

Query to check the security Profile

select fa.APPLICATION_SHORT_NAME,fa.APPLICATION_ID,fu.user_id,
     fu.employee_id emp_id,
     ppx.employee_number emp_no,
     PPX.FULL_NAME,
     (select haou.name from apps.hr_all_organization_units haou where haou.organization_id = psf.organization_id)cc,
     fu.user_name,
     fu.start_date usr_st_dt,
     fu.end_date usr_ed_dt,  
     frv.responsibility_name,
     fur.start_date res_st_dt,
     fur.end_date res_ed_dt,
     psf.security_profile_name,
--     fu.encrypted_user_password passwrd  
     apps.xxhr_reports_pkg.get_parent_organization_id(psf.organization_id,'BU') BU ,
       frv.responsibility_id,psf.security_profile_id,
       fu.last_update_date
from
    apps.fnd_user fu,
    apps.per_people_x ppx,
    apps.fnd_user_resp_groups_direct fur,
    apps.fnd_responsibility_tl frv,
    apps.fnd_application fa,
    apps.fnd_profile_option_values fpov,
    apps.fnd_profile_options fpo,
    apps.fnd_profile_options_tl fpot,
    apps.per_security_profiles psf
where 1=1
  and fu.employee_id = ppx.person_id(+)
  --and TRUNC(SYSDATE) between fu.start_date and NVL(fu.end_date,TRUNC(SYSDATE))
  --and TRUNC(SYSDATE) between fur.start_date and NVL(fur.end_date,TRUNC(SYSDATE))
  and fu.user_id = fur.user_id
  and frv.responsibility_id = fur.responsibility_id
  and fa.application_id     =frv.application_id
--and fa.application_short_name IN('PAY','PER','XXHR')
and fa.application_short_name like 'PER'
  and fpov.application_id  = fa.application_id
and fpov.level_value = frv.responsibility_id(+)
and fpov.profile_option_id = fpo.profile_option_id
and fpov.level_id        = 10003
   and fpo.profile_option_name = fpot.profile_option_name
and ppx.business_group_id = 1268
and ppx.current_employee_flag = 'Y'
and psf.security_profile_name like XXHR BU Security Profile New'--Profile Name


Supervisor HR Security Profile

select * from fnd_responsibility where responsibility_id=53256

select * from fnd_security_groups frg

Add New Responsibility from Back End

Run the below query giving the user name and responsibility needs to be added.,


DECLARE
   v_user_name                  VARCHAR2 (100) :='xyz.abc';
   v_responsibility_name  VARCHAR2 (100) := 'System Administrator';
   v_application_name      VARCHAR2 (100) := NULL;
   v_responsibility_key      VARCHAR2 (100) := NULL;
   v_security_group           VARCHAR2 (100) := NULL;
   v_description                  VARCHAR2 (100) := NULL;
BEGIN
   SELECT fa.application_short_name,
                 fr.responsibility_key,
                 frg.security_group_key,                  
                 frt.description
      INTO  v_application_name,
                 v_responsibility_key,
                 v_security_group,
                 v_description
     FROM fnd_responsibility fr,
                 fnd_application fa,
                 fnd_security_groups frg,
                 fnd_responsibility_tl frt
    WHERE fr.application_id = fa.application_id
      AND    fr.data_group_id = frg.security_group_id
      AND    fr.responsibility_id = frt.responsibility_id
     -- AND    frt.LANGUAGE = USERENV ('LANG')
      AND    upper(frt.responsibility_name) = upper(v_responsibility_name);

   fnd_user_pkg.addresp (username          => v_user_name,
                                              resp_app           => v_application_name,
                                              resp_key            => v_responsibility_key,
                                              security_group => v_security_group,
                                              description        => v_description,
                                              start_date          => SYSDATE,
                                              end_date            => NULL
                                             );
   COMMIT;

   DBMS_OUTPUT.put_line (   'Responsiblity '
                         || v_responsibility_name
                         || ' is attached to the user '
                         || v_user_name
                         || ' Successfully'
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
         (   'Error encountered while attaching responsibilty to the user and the error is '
          || SQLERRM
         );
END;

For Checking query as below.,

SELECT  fu.user_name,
               frt.responsibility_name,fu.employee_id
  FROM fnd_user_resp_groups furg,
              fnd_user fu,
              fnd_responsibility_tl frt
 WHERE furg.user_id = fu.user_id
   AND    furg.responsibility_id = frt.responsibility_id
   AND    fu.user_name = 'xyz.abc'

FND User Update

CREATE OR REPLACE PACKAGE APPS.XXHR_CONV_FND_USER_PKG IS
PROCEDURE FND_PRc(p_err_buf OUT     VARCHAR2
                                    ,p_retcode OUT     NUMBER);
end;
/
CREATE OR REPLACE PACKAGE BODY APPS.XXHR_CONV_FND_USER_PKG IS
  PROCEDURE FND_prc
  (
    p_err_buf OUT VARCHAR2,
    p_retcode OUT NUMBER
  ) IS
    /* ***********************************************************
    * Procedure Name  : UPD_PRF
    * Description  : This procedure is used for updating the security Profile Option.
    * Parameters IN   : none
    * Parameters Out: none
    * Prerequisits:
    ************************************************************** */
   l_user_name         fnd_user.user_name%type;
   l_employee_id       per_all_people_f.person_id%type;
   l_begin_date        date := trunc(sysdate);
    v_buss_id         NUMBER;
    v_err_flag        VARCHAR2(1);
    v_err_mesg        VARCHAR2(2000);
    v_err_sql_msg     VARCHAR2(2000);
     v_tot_cnt         NUMBER DEFAULT 0;
    v_tot_suc         NUMBER DEFAULT 0;
    v_tot_fal         NUMBER DEFAULT 0;
    v_application_name varchar2(100);
    v_responsibility_key varchar2(100);
               
   cursor cur_sec is
   select * from xxhr_fnd_user_upload
   where nvl(upload_status, 'n') <> 'y'
         for update of upload_status;
 
 
   CURSOR cur_sec_err IS
      SELECT * FROM xxhr_fnd_user_upload WHERE upload_status = 'E';
   
 
     BEGIN
    v_buss_id := fnd_profile.VALUE('PER_BUSINESS_GROUP_ID');
    FOR c_rec IN cur_sec
    LOOP
      v_tot_cnt := v_tot_cnt + 1;
      fnd_file.put_line(fnd_file.log,
                        '*****************************************************************************************' ||
                        v_tot_cnt);

      v_err_mesg   := NULL;
      v_err_flag   := NULL;
   
      IF c_rec.new_employee_id IS NULL THEN
        v_err_flag := 'E';
        v_err_mesg := 'Employee id cannot be NULL';
              else
                begin
                             select
                             papf.person_id into
                             l_employee_id from
                             per_all_people_f papf
                             where
                             person_id=c_rec.new_employee_id
                              and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
                              and papf.business_group_id= v_buss_id
                                and papf.current_employee_flag='Y';
       
         exception
                  when others then
                    v_err_flag := 'E';
                    v_err_mesg := 'Invalid Employee';
 
        END;
      END IF;
   
      IF nvl(v_err_flag, 'N') <> 'E' THEN
   
        begin

     
                                                      fnd_user_pkg.updateuser(
                                                         x_user_name      => c_rec.user_name
                                                       , x_owner          => NULL
                                                       ,x_employee_id   => l_employee_id
                                                      );

                                                    commit;  

     UPDATE xxhr_fnd_user_upload
             SET upload_status = 'Y', comments = NULL
           WHERE CURRENT OF cur_sec;

          v_tot_suc := v_tot_suc + 1;
        EXCEPTION
          WHEN OTHERS THEN
            v_tot_fal  := v_tot_fal + 1;
            v_err_flag := 'E';
            v_err_mesg := 'Error   : Error Inserting Fnd_user - ' ||
                          to_char(SQLCODE) || '-' || SQLERRM;
            UPDATE xxhr_fnd_user_upload
               SET upload_status = 'E', comments = nvl(v_err_mesg, 'Error')
             WHERE CURRENT OF cur_sec;
        END;
      ELSE
        v_tot_fal := v_tot_fal + 1;
        UPDATE xxhr_secur_pro_upload
           SET upload_status = 'E', comments = nvl(v_err_mesg, 'error')
         WHERE CURRENT OF cur_sec;
      END IF;
      v_err_flag := NULL;
    END LOOP;
    COMMIT;
    fnd_file.put_line(fnd_file.log,
                      '*****************************************************************************************');
    fnd_file.put_line(fnd_file.log,
                      '**************************Security UPLOAD REPORT*******************************');
    fnd_file.put_line(fnd_file.log,
                      '*****************************************************************************************');
    fnd_file.put_line(fnd_file.log,
                      'Total Numbers of Records Read        : ' ||
                      v_tot_cnt);
    fnd_file.put_line(fnd_file.log,
                      'Total Numbers of Records Inserted    : ' ||
                      v_tot_suc);
    fnd_file.put_line(fnd_file.log,
                      'Total Numbers of Records Errored Out : ' ||
                      v_tot_fal);
    fnd_file.put_line(fnd_file.log,
                      '*****************************************************************************************');
    fnd_file.put_line(fnd_file.log, 'ERRORED OUT Profiles ');
    FOR cur_rec IN cur_sec_err
    LOOP
      fnd_file.put_line(fnd_file.log,
                        'Employee Name : ' || cur_rec.user_name || ': ' ||
                        cur_rec.comments);
    END LOOP;
  END fnd_prc;
END XXHR_CONV_FND_USER_PKG;
/

Consult the OPP service log for details.

For XML reports with layout or Cross tab type template errors will encounter with Complete/warning with Error Msg Consult the OPP service log for details.

Need to Check log file for error:

The path is

/u01/DZWD(service SID)/inst/apps/DZWD_jvezdap1/logs/appl/conc/log/filename.txt

To find the file Path  use the query

SELECT 

fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name 

FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp 

WHERE fcpp.processor_id = fcp.concurrent_process_id

 AND fcpp.action_type = 6

 AND fcpp.concurrent_request_id = 20918150(&concurrent program request_id)

;

Mostly the error will come when the template code given differs.
Note:Concurrent program short name and data definition short name should be the same and template code can differ from concurrent program short name.

FNDLOAD

To load template

FNDLOAD apps/DI6E24BW O Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXAYE_SALES_CONFIRMATION.ldt XDO_DS_DEFINITIONS

To load value sets

FNDLOAD apps/DI6E24BW O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXAYE_PO_CONFIRMATION_VALUESET2.ldt VALUE_SET_VALUE


1 - Printer Styles

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLEPRINTER_STYLE_NAME="printer style name"



2 - Lookups

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPEAPPLICATION_SHORT_NAME="FND"

LOOKUP_TYPE="lookup name"



3 - Descriptive Flexfield with all of specific Contexts

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lctfile_name.ldt DESC_FLEX P_LEVEL=COL_ALL:REF_ALL:CTX_ONE:SEG_ALLAPPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="descflex name" P_CONTEXT_CODE="context name"



4 - Key Flexfield Structures

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEXP_LEVEL=COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALLAPPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code"P_STRUCTURE_CODE="structure name"



5 - Concurrent Programs

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAMAPPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrentname"



6 - Value Sets

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SETFLEX_VALUE_SET_NAME='value set name'



7 - Value Sets with values

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUEFLEX_VALUE_SET_NAME='value set name'



8 - Profile Options

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILEPROFILE_NAME="profile option" APPLICATION_SHORT_NAME="FND"



9 - Request Groups

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUPREQUEST_GROUP_NAME="request group"APPLICATION_SHORT_NAME="FND"



10 - Request Sets

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET

APPLICATION_SHORT_NAME="FND"REQUEST_SET_NAME="request set"



11 - Responsibilities

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITYRESP_KEY="responsibility"



12 - Menus

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afsload.lct file_name.ldt MENUMENU_NAME="menu_name"



13 - Alerts

FNDLOAD apps/apps 0 Y DOWNLOAD$ALR_TOP/patch/115/import/alr.lct <'File Name'>.ldt ALR_ALERTSAPPLICATION_SHORT_NAME=<'APP SHORT NAME'> ALERT_NAME=<'Alert name todownload'>



14 - WEB ADI

FNDLOAD apps/apps 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct<'YOUR LDT FILE NAME'>.ldt BNE_INTEGRATORSINTEGRATOR_ASN="XXALY" INTEGRATOR_CODE="YOUR INTEGRATORNAME"

current GL Period

select max(gl_date) from
ra_cust_trx_line_gl_dist_all

To find Responsibilities

-- Query to find the Responsibilities based on Concurrent Request set Name

SELECT UNIQUE frt.responsibility_name, frg.request_group_name,
              frsv.user_request_set_name
         FROM fnd_responsibility fr,
              fnd_responsibility_tl frt,
              fnd_request_groups frg,
              fnd_request_group_units frgu,
              fnd_request_sets_vl frsv
        WHERE fr.request_group_id = frg.request_group_id
          AND fr.responsibility_id = frt.responsibility_id
          AND frg.request_group_id = frgu.request_group_id
          AND frgu.request_unit_id = frsv.request_set_id
          AND frsv.user_request_set_name LIKE 'give user request set name'
          AND frsv.request_set_name LIKE 'request set name';
       

-- Query to find the Responsibilities based on Concurrent Program Name        
     
SELECT UNIQUE frt.responsibility_name, frg.request_group_name,
              fcpt.user_concurrent_program_name
         FROM fnd_responsibility fr,
              fnd_responsibility_tl frt,
              fnd_request_groups frg,
              fnd_request_group_units frgu,
              fnd_concurrent_programs_tl fcpt
        WHERE fr.responsibility_id = frt.responsibility_id
          AND fr.request_group_id = frg.request_group_id
          AND frgu.request_group_id = frg.request_group_id
          AND frgu.request_unit_id = fcpt.concurrent_program_id
          AND fcpt.LANGUAGE = 'US'
          AND frt.LANGUAGE = 'US'
          AND fcpt.user_concurrent_program_name LIKE
                                                'Your Concurrent Program Name'        

FND_CANONICAL

In HRMS we do use date with timestamp for EIT ans SIT segments.

To change the format od the date we can use fnd_date.canonical_to_date default function:

e.g:

before using fnd_date function

SELECT pei_information2 Visit_date from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

After using fnd_date function:

SELECT fnd_date.canonical_to_date(pei_information2) from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

 And you can use to_chat over uit to make it in words as

SELECT TO_CHAR(fnd_date.canonical_to_date(pei_information2),'Month ddTH, YYYY') from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

 

Find log using Request id

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = 515172

Life cycle of AR,AP,GL

Starts from supplier->invoice->validate->payment->createje->post to gl->cash basis

Have a look at

http://oracleappscommunity.com/oracle/blog/modules/oracle-accounts-receivables/page/2/

Version

To Find out the DB Version Used...

SELECT banner from v$version;  

To find out the Application release used

SELECT product_group_id GroupID, product_group_name GroupName, release_name Release, product_group_type GroupType FROM fnd_product_groups;



Copying a Concurrent program

Let us assume that we are going to register a report.

E.g:-

Customizing seeded report:

1.Copy the rdf for the module top.
2.open it in the report bulider ad save it in different name.
3.Do the changes and save it.
4.Create a executable for the same.While creating the concurrent program... Retrive the seeded program and go to optiom copy to,give your concurrent program name and save it.
--So that the parameters defined for the seeded program will be reflected for the customized report.
5.Now change the executable and add it to the responsibilty and submit it.

Changing a seeded report will be more complicated and replacing the same is not recommended,so we can do in this way.

FND PROFILE VALUE

To Get Organisation Id
fnd_profile.value('MFG-ORGANISATION_ID');
select fnd_profile.value('MFG-ORGANISATION_ID') from dual;
To Get responsibility id
fnd_profile.value('RESP_ID');

like wise we can get all environment variables.....................

Apps Initialize

Invoke the session before subiting any concurrent program from back end.
select the user is,responsibility id and application id to invoke the session,
select fnd.user_id ,
fresp.responsibility_id,RESPONSIBILITY_NAME,
fresp.application_id
from fnd_user fnd
, fnd_responsibility_tl fresp where fnd.user_name = 'OPERATIONS' and fresp.responsibility_name like 'Inv%';
E.g:
fnd_global.apps_initialize (user_id, resp_id, appl_resp_id);fnd_global.apps_initialize(1318,20004,51710);
example for submitting a request from back end

declare
Submit_Request boolean;
req_id number;
ln_header_id number;
ln_transaction_mode varchar2(10);
submitset_failed exception;
l_phase varchar2 (10);
l_status varchar2 (10);
l_dev_phase varchar2 (10);
l_dev_status varchar2 (10);
l_message varchar2 (10);
ls_resp_id number;
ls_user_id number;
ls_appl_resp_id number;
begin
ls_resp_id := fnd_profile.VALUE ('RESP_ID');
ls_user_id := fnd_profile.VALUE ('USER_ID');
ls_appl_resp_id := fnd_profile.VALUE ('APPL_RESP_ID');
/*Apps Initialize*/
fnd_global.apps_initialize (ls_user_id, ls_resp_id, ls_appl_resp_id);
/* Submit Reqest*/
REQ_ID := fnd_request.submit_request ('INV', 'WICTMS');
/* Module short name and program short name */
/*Checking the concurrent program and request to wait */
SUBMIT_REQUEST :=
APPS.FND_CONCURRENT.WAIT_FOR_REQUEST (request_id => req_id,
                                                                                        interval => 5 -- Sleep 5 seconds between checks.
                                                                                       ,max_wait => 0 -- Wait indefinately.
                                                                                       ,phase => 0 l_phase,
                                                                                        status => 0 l_status,
                                                                                       dev_phase => 0 l_dev_phase,
                                                                                      dev_status => 0 l_dev_status,
                                                                                      MESSAGE => 0 l_message);
  IF l_dev_status = 'NORMAL'
  THEN
 fnd_file.put_line(fnd_file.log,'Success');
 ELSE
 fnd_file.put_line(fnd_file.log,FAILED');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
fnd_file.put_line (fnd_file.LOG, 'Error in Caling Subprogram');
END;
fnd_file.put_line(fnd_file.log,'Successfully Completed');

Data Validation Test Scripts

Data Validations is for Auditing the successful completition of implementation which had legacy data migrations:

• Check Journal Balances for a Period
• Check Supplier Trial Balances
• Checking Customer Balances
• Checking Order Counts(Open, Closed)
• Checking AR Invoice Counts(Open, Paid)
• Checking AP Invoice Counts(Open,Paid)
• Checking Inventory Onhand Balances
• Checking Item Counts by Status
• Checking Customer Counts
• Checking Vendor Counts
• Checking PO Counts

Create Table form a Report

I got a Scenerio that to create a table and insert values through a concurrent program:

To achieve this,we can do the fallowing:
1.create a procedure in DB to create a table
CREATE OR REPLACE PROCEDURE Create_Table
(errbuf      OUT VARCHAR2,retcode   
OUT VARCHAR2) IS
BEGIN
   Create table table_name(column_name);
END Create_Table;
2.Register this procedure in application as pl/sql stored procedure
3.Now run the program and it will create a table in DB

Now we can call this procedure in our reports sql query so while running the report we can create the table.

select Create_Table(Procedure name) from dual;

Difference between org_id and organisation_id

Organization ID refers to a unique id of an inventory organization (mtl_parameters)

Org_id refers to a operating unit (hr_operating_units)

Attached Documents

Fnd documents can be accessed using Pk_value defined in FND_ATTACHED_DOCUMENTS table.
this will refer to primary key of the refering table.E.g,if po_headers_all then po_hearder_id will be the pk_value1.


sample code to find out pk value:






select * from fnd_document_entities e,fnd_document_entities_tl t,
fnd_attached_documents fad,po_LINES_all ph,FND_DOCUMENTS_SHORT_TEXT FSL
where e.DOCUMENT_ENTITY_ID=t.DOCUMENT_ENTITY_ID and E.table_name='PO_LINES' and e.DATA_OBJECT_CODE='PO_LINES' and fad.ENTITY_NAME=e.ENTITY_NAME and ph.PO_LINE_ID=fad.PK1_VALUE

Base tables for Documents are

fnd_document_datatypes FND_DOCUMENTS_SHORT_TEXT

fnd_document_entities_tl
fnd_documents_tl
fnd_documents
fnd_document_categories_tl
fnd_doc_category_usages
fnd_attachment_functions
fnd_attached_documents

FND_DOCUMENTS_LONG_TEXT