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'

Fast Formula Results Upload

Extract Query:

SELECT 'RULE',
 PET.Element_Name,
 TO_CHAR(PPR.legislation_code) ATTRIBUTE1,
 TO_CHAR(PPR.assignment_status_type_id) ATTRIBUTE2,
   FF.FORMULA_NAME,--  get_formula_name(PETF.Business_group_id,PSPR.Formula_Id) formula_Name,
     --  TO_CHAR(PspR.FORMULA_ID) ATTRIBUTE3,
        NULL ATTRIBUTE4,
       NULL ATTRIBUTE5,
       NULL ATTRIBUTE6,
       null attribute7
from   PAY_STATUS_PROCESSING_RULES_f PPR,
       PAY_ELEMENT_TYPES_F PET,
       PAY_ELEMENT_TYPES_F PETR,
       FF_FORMULAS_F FF,
       PAY_FORMULA_RESULT_RULES_f PFR,
       PAY_INPUT_VALUES_F PIV
where  --PPR.STATUS_PROCESSING_RULE_ID =  502
--AND  
PPR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND    PPR.FORMULA_ID = FF.FORMULA_ID
AND    PPR.BUSINESS_GROUP_ID = FF.BUSINESS_GROUP_ID
AND    PET.BUSINESS_GROUP_ID = PPR.BUSINESS_GROUP_ID
AND    PPR.STATUS_PROCESSING_RULE_ID  = PFR.STATUS_PROCESSING_RULE_ID
AND    PPR.BUSINESS_GROUP_ID = PFR.BUSINESS_GROUP_ID
AND    PFR.ELEMENT_TYPE_ID = PETR.ELEMENT_TYPE_ID
AND    PETR.BUSINESS_GROUP_ID = PPR.BUSINESS_GROUP_ID
AND    PPR.BUSINESS_GROUP_ID = 101
AND    PFR.INPUT_VALUE_ID      = PIV.INPUT_VALUE_ID (+)
AND    PIV.ELEMENT_TYPE_ID     = PETR.ELEMENT_TYPE_ID (+)
AND    PIV.BUSINESS_GROUP_ID    = PETR.BUSINESS_GROUP_ID
AND    SYSDATE BETWEEN FF.EFFECTIVE_START_DATE AND FF.EFFECTIVE_END_DATE
AND    SYSDATE BETWEEN PFR.EFFECTIVE_START_DATE AND PFR.EFFECTIVE_END_DATE
AND    PET.ELEMENT_NAME = 'Accompany Leave'
 UNION
 SELECT 'RESULT',
 PET.ELEMENT_NAME,
    PFR.RESULT_NAME ATTRIBUTE1,
  --  Decode(PFRRF.Result_Rule_Type,'D','Direct Result','I','Indirect Result','M','Message','U','Update Recurring Entry','S','Stop') Result_Rule_Type,
to_char(PFR.RESULT_RULE_TYPE)ATTRIBUTE2,
     PPR.legislation_code ATTRIBUTE3,
     PETR.ELEMENT_NAME  ATTRIBUTE4,
      pPR.legislation_subgroup ATTRIBUTE5,
       pFR.severity_level ATTRIBUTE6,
       piv.NAME ATTRIBUTE7
from   PAY_STATUS_PROCESSING_RULES_f PPR,
       PAY_ELEMENT_TYPES_F PET,
       PAY_ELEMENT_TYPES_F PETR,
       FF_FORMULAS_F FF,
       PAY_FORMULA_RESULT_RULES_f PFR,
       PAY_INPUT_VALUES_F PIV
where  --PPR.STATUS_PROCESSING_RULE_ID =  502
--AND  
PPR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND    PPR.FORMULA_ID = FF.FORMULA_ID
AND    PPR.BUSINESS_GROUP_ID = FF.BUSINESS_GROUP_ID
AND    PET.BUSINESS_GROUP_ID = PPR.BUSINESS_GROUP_ID
AND    PPR.STATUS_PROCESSING_RULE_ID  = PFR.STATUS_PROCESSING_RULE_ID
AND    PPR.BUSINESS_GROUP_ID = PFR.BUSINESS_GROUP_ID
AND    PFR.ELEMENT_TYPE_ID = PETR.ELEMENT_TYPE_ID
AND    PETR.BUSINESS_GROUP_ID = PPR.BUSINESS_GROUP_ID
AND    PPR.BUSINESS_GROUP_ID = 101
AND    PFR.INPUT_VALUE_ID      = PIV.INPUT_VALUE_ID (+)
AND    PIV.ELEMENT_TYPE_ID     = PETR.ELEMENT_TYPE_ID (+)
AND    PIV.BUSINESS_GROUP_ID    = PETR.BUSINESS_GROUP_ID
AND    SYSDATE BETWEEN FF.EFFECTIVE_START_DATE AND FF.EFFECTIVE_END_DATE
AND    SYSDATE BETWEEN PFR.EFFECTIVE_START_DATE AND PFR.EFFECTIVE_END_DATE
AND    PET.ELEMENT_NAME = 'Accompany Leave'



Staging table Structure:

CREATE TABLE XXHR_FORMULA_RESULTS_LOAD
(
  RESULT_TYPE    VARCHAR2(2000 BYTE),
  ELEMENT_NAME   VARCHAR2(2000 BYTE),
  ATTRIBUTE1     VARCHAR2(2000 BYTE),
  ATTRIBUTE2     VARCHAR2(2000 BYTE),
  ATTRIBUTE3     VARCHAR2(2000 BYTE),
  ATTRIBUTE4     VARCHAR2(2000 BYTE),
  ATTRIBUTE5     VARCHAR2(2000 BYTE),
  ATTRIBUTE6     VARCHAR2(2000 BYTE),
  ATTRIBUTE7     VARCHAR2(2000 BYTE),
  UPLOAD_STATUS  VARCHAR2(2000 BYTE),
  ERR_MSG        VARCHAR2(2000 BYTE)
)



Populate the staging table and run the below package by registering it in the application as PL/SQL Executable Concurrent Program.

CREATE OR REPLACE PACKAGE BODY APPS.xxhr_conv_ff_results_pkg IS
      PROCEDURE create_ff_results
      (
            errbuf  IN VARCHAR2
           ,retcode NUMBER
      ) IS
          /* ***********************************************************
    * Procedure Name  : create_ff_results
    * Description  : This procedure is used for updating FF Formula Results.
    * Parameters IN   : none
    * Parameters Out: none
    * Prerequisits:
    ************************************************************** */
 v_tot_cnt         NUMBER DEFAULT 0;
    v_tot_suc         NUMBER DEFAULT 0;
    v_tot_fal         NUMBER DEFAULT 0;
     v_err_flag        VARCHAR2(1);
    v_err_mesg        VARCHAR2(2000);
    v_err_sql_msg     VARCHAR2(2000);
 
            CURSOR cur_ff_rule IS
                  SELECT DISTINCT rule.element_name
                                 ,petf.element_type_id
                                 ,rule.attribute1
                                 ,rule.attribute2
                                 ,rule.attribute3
                                 ,ff.formula_id
                    FROM xxhr_formula_results_load rule
                        ,pay_element_types_f       petf
                        ,ff_formulas_f             ff
                   WHERE --nvl(upload_status, 'N') <> 'Y'
                      petf.element_name = rule.element_name
                      AND rule.attribute3 = ff.formula_name
                      --and ff.formula_name ='XXHR_UNPAID_ACC_LEAVE_DEDUCTION'
                    -- and ff.formula_id=483
                     AND result_type = 'RULE'
                      AND   PETF.Business_Group_Id =1268
                     AND  FF.Business_Group_Id = 1268
                     AND trunc(SYSDATE) BETWEEN petf.effective_start_date AND
                         petf.effective_end_date
                     AND trunc(SYSDATE) BETWEEN ff.effective_start_date AND
                         ff.effective_end_date;
                      --  AND rule.element_name like 'Acting Allowance';

            CURSOR cur_ff_result(c_element_name IN VARCHAR2) IS
                  SELECT rule.attribute1
                        ,rule.attribute2
                        ,rule.attribute3
                         ,rule.attribute4
                        ,petf.element_type_id
                        ,rule.attribute5
                        ,rule.attribute6
                        ,pivf.input_value_id,
                        PETF.BUSINESS_GROUP_ID
                    FROM xxhr_formula_results_load rule
                        ,pay_element_types_f       petf
                        ,pay_input_values_f        pivf
                   WHERE-- nvl(upload_status, 'N') <> 'Y'
                      rule.result_type = 'RESULT'
                     AND rule.element_name = c_element_name
                      AND pivf.NAME = rule.attribute7(+)
                      -- AND  PETF.Business_Group_Id = 1268
                      AND  Pivf.Business_Group_Id = 1268
                      AND pivf.element_type_id = petf.element_type_id(+)
                    --  AND petf.element_name = rule.attribute7
                     --  and rule.attribute4=
                       AND trunc(SYSDATE) BETWEEN petf.effective_start_date AND
                         petf.effective_end_date
                     AND trunc(SYSDATE) BETWEEN pivf.effective_start_date AND
                         pivf.effective_end_date
                          --and pivf.element_type_id=372
                     FOR UPDATE OF upload_status, err_msg;


            v_err_msg                   VARCHAR2(2000);
            v_function_id               NUMBER;
            v_status_processing_rule_id NUMBER;
            v_rule_eff_start_date       DATE;
            v_rule_eff_end_date         DATE;
            v_ff_param_seq              VARCHAR2(2000);
            v_fr_ovn                    NUMBER;
            v_formula_mismatch_warning  BOOLEAN;
            v_business_group_id         NUMBER := fnd_profile.VALUE('PER_BUSINESS_GROUP_ID');
            v_formula_result_rule_id    NUMBER;
            v_result_eff_start_date     DATE;
            v_result_eff_end_date       DATE;
            v_result_ovn                NUMBER;

      BEGIN
       v_business_group_id  := fnd_profile.VALUE('PER_BUSINESS_GROUP_ID');
            FOR rec_ff_rule IN cur_ff_rule
            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;
                  BEGIN
                        v_err_msg := NULL;
                        pay_status_processing_rule_api.create_status_process_rule(p_validate => FALSE,
                                                                                  p_effective_date => '01-Jan-2015',
                                                                                  p_element_type_id => rec_ff_rule.element_type_id,
                                                                                  p_business_group_id => v_business_group_id,
                                                                                  p_legislation_code => rec_ff_rule.attribute1,
                                                                                  p_assignment_status_type_id => NULL,--to_number(rec_ff_rule.attribute2),
                                                                                  p_formula_id =>rec_ff_rule.formula_id,
                                                                                  p_comments => NULL,
                                                                                  p_legislation_subgroup => NULL,
                                                                                  p_status_processing_rule_id => v_status_processing_rule_id,
                                                                                  p_effective_start_date => v_rule_eff_start_date,
                                                                                  p_effective_end_date => v_rule_eff_end_date,
                                                                                  p_object_version_number => v_fr_ovn,
                                                                                  p_formula_mismatch_warning => v_formula_mismatch_warning);
                 
                        FOR rec_ff_result IN cur_ff_result(rec_ff_rule.element_name)
                        LOOP
                              BEGIN

                                    pay_formula_result_rule_api.create_formula_result_rule(p_validate => FALSE,
                                                                                           p_effective_date => '01-Jan-1951',
                                                                                           p_status_processing_rule_id => v_status_processing_rule_id,
                                                                                           p_result_name => rec_ff_result.attribute1,
                                                                                           p_result_rule_type => rec_ff_result.attribute2,
                                                                                           p_business_group_id => v_business_group_id,
                                                                                           p_legislation_code => 'AE',--rec_ff_result.attribute3,
                                                                                           p_element_type_id => rec_ff_result.element_type_id,
                                                                                           p_legislation_subgroup => rec_ff_result.attribute5,
                                                                                           p_severity_level => rec_ff_result.attribute6,
                                                                                           p_input_value_id => rec_ff_result.input_value_id,
                                                                                           p_formula_result_rule_id => v_formula_result_rule_id,
                                                                                           p_effective_start_date => v_result_eff_start_date,
                                                                                           p_effective_end_date => v_result_eff_end_date,
                                                                                           p_object_version_number => v_result_ovn);

                                    UPDATE xxhr_formula_results_load
                                       SET upload_status = 'Y'
                                          ,err_msg       = NULL
                                     WHERE CURRENT OF cur_ff_result;
                                 
                                     v_tot_suc := v_tot_suc + 1;
                                   
                              EXCEPTION
                                    WHEN OTHERS THEN
                                     v_tot_fal  := v_tot_fal + 1;
                                    v_err_flag := 'E';
                                      fnd_file.put_line(fnd_file.log,'Error   : Error Inserting Results_user -         : ' ||v_err_mesg||SQLERRM );
                                            v_err_mesg := 'Error   : Error Inserting Fnd_user - ' || to_char(SQLCODE) || '-' || SQLERRM;
                                             v_err_msg := substr(SQLERRM, 1,
                                                              2000);
                                          UPDATE xxhr_formula_results_load
                                             SET upload_status = 'E'
                                                ,err_msg       = v_err_msg
                                           WHERE CURRENT OF cur_ff_result;
                              END;
                        END LOOP;

                   
                        UPDATE xxhr_formula_results_load
                           SET upload_status = 'Y'
                              ,err_msg       = NULL
                         WHERE element_name = rec_ff_rule.element_name
                           AND result_type = 'RULE';
                          v_tot_suc := v_tot_suc + 1;
                  EXCEPTION
                        WHEN OTHERS THEN
                         v_tot_fal := v_tot_fal + 1;
                              v_err_msg := substr(SQLERRM, 1, 2000);
                              fnd_file.put_line(fnd_file.log,'Error   : Error Inserting rule_         : ' ||v_err_mesg );
                              UPDATE xxhr_formula_results_load
                                 SET upload_status = 'E'
                                    ,err_msg       = v_err_msg
                               WHERE element_name =
                                     rec_ff_rule.element_name;
                  END;
            END LOOP;
            COMMIT;
                                   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 Results');
                        fnd_file.put_line(fnd_file.log,'Error   : Error Inserting rule_         : ' ||v_err_mesg );
      END create_ff_results;
END xxhr_conv_ff_results_pkg;
/

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

Document Of Records Upload

Use the Below Prgram for Document Upload.

The primary Key value(l_pk1_value)is the vaule attached to the title of the Document for each Employee.

DECLARE

l_rowid ROWID;

l_attached_document_id NUMBER;

l_document_id NUMBER;

l_media_id NUMBER;

l_category_id NUMBER;

l_pk1_value number:=40582;---apps.fnd_attached_documents.pk1_value%TYPE;--:=;

l_description VARCHAR2(240):='Test Attachment';

l_filename VARCHAR2(240) ;--:= 'Sankara.pdf';

l_seq_num NUMBER;

l_blob_data BLOB;

l_blob BLOB;

l_bfile BFILE;

l_byte NUMBER;

l_fnd_user_id NUMBER;

l_short_datatype_id NUMBER;

x_blob BLOB;

fils BFILE;

blob_length INTEGER;

l_entity_name VARCHAR2(100) := 'R_DOCUMENT_EXTRA_INFO';

l_category_name VARCHAR2(100) := 'Documents of Record';

BEGIN

--fnd_global.apps_initialize (4659, 50773,800);

SELECT fnd_documents_s.NEXTVAL

INTO l_document_id

FROM DUAL;

SELECT fnd_attached_documents_s.NEXTVAL

INTO l_attached_document_id

FROM DUAL;

SELECT NVL (MAX (seq_num), 0) + 10

INTO l_seq_num

FROM fnd_attached_documents

WHERE pk1_value =40582 AND entity_name = l_entity_name;

-- Select User_id

SELECT user_id

INTO l_fnd_user_id

FROM apps.fnd_user

WHERE employee_id=81399;--83269;--user_name = ;

-- Get Data type id for Short Text types of attachments

SELECT datatype_id

INTO l_short_datatype_id

FROM apps.fnd_document_datatypes

WHERE NAME = 'FILE';

-- Select Category id for Attachments

SELECT category_id

INTO l_category_id

FROM apps.fnd_document_categories_TL

WHERE USER_NAME ='Documents of Record';-- l_category_name;

-- Select nexvalues of document id, attached document id and

-- l_media_id

SELECT apps.fnd_documents_s.NEXTVAL,

apps.fnd_attached_documents_s.NEXTVAL

--apps.fnd_documents_long_text_s.NEXTVAL

INTO l_document_id,

l_attached_document_id

--l_media_id

FROM DUAL;

SELECT MAX (file_id) + 1

INTO l_media_id

FROM fnd_lobs;

fils := BFILENAME ( 'XXHR_FND_BLOBS','Sankara Rao Dasari- Employment Visa.pdf');

-- Obtain the size of the blob file

DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);

blob_length := DBMS_LOB.getlength (fils);

DBMS_LOB.fileclose (fils);

-- Insert a new record into the table containing the

-- filename you have specified and a LOB LOCATOR.

-- Return the LOB LOCATOR and assign it to x_blob.

/*INSERT INTO fnd_lobs

(file_id, file_name, file_content_type, upload_date,

expiration_date, program_name, program_tag, file_data,

LANGUAGE, oracle_charset, file_format

)

VALUES (l_media_id, l_filename, 'application/pdf',--'text/plain',--application/pdf

SYSDATE,

NULL, 'FNDATTCH', NULL, EMPTY_BLOB (), --l_blob_data,

'US', 'UTF8', 'binary'

)

RETURNING file_data

INTO x_blob;*/

INSERT INTO fnd_lobs

(file_id, file_name, file_content_type, upload_date,

expiration_date, program_name, program_tag, file_data,

LANGUAGE, oracle_charset, file_format

)

VALUES (l_media_id, l_filename, 'application/pdf',--'text/plain',--application/pdf

SYSDATE,

NULL, 'FNDATTCH', NULL,EMPTY_BLOB (), --l_blob_data,

NULL, 'UTF8', 'binary'

)

RETURNING file_data

INTO x_blob;

-- Load the file into the database as a BLOB

DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);

DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);

DBMS_LOB.loadfromfile (x_blob, fils, blob_length);

-- Close handles to blob and file

DBMS_LOB.CLOSE (x_blob);

DBMS_LOB.CLOSE (fils);

DBMS_OUTPUT.put_line ('FND_LOBS File Id Created is ' || l_media_id);

COMMIT;

-- This package allows user to share file across multiple orgs or restrict to single org

fnd_documents_pkg.insert_row

(x_rowid => l_rowid,

x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')

x_last_update_date => SYSDATE,

x_last_updated_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_datatype_id => l_short_datatype_id, -- FILE

X_security_id =>NULL,

x_publish_flag => 'N', --This flag allow the file to share across multiple organization

x_category_id => l_category_id,

x_security_type => 1,

x_usage_type => 'S',

x_language => 'US',

x_description => l_filename,--l_description,

x_file_name => l_filename,

x_media_id => l_media_id

);

commit;

fnd_documents_pkg.insert_tl_row

(x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_date => SYSDATE,

x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_language => 'US',

x_description => l_filename--l_description

);

COMMIT;

fnd_attached_documents_pkg.insert_row

(x_rowid => l_rowid,

x_attached_document_id => l_attached_document_id,

x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_date => SYSDATE,

x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_seq_num => l_seq_num,

x_entity_name => l_entity_name,

x_column1 => NULL,

x_pk1_value => l_pk1_value,

x_pk2_value => NULL,

x_pk3_value => NULL,

x_pk4_value => NULL,

x_pk5_value => NULL,

x_automatically_added_flag => 'N',

x_datatype_id => 6,

x_category_id => l_category_id,

x_security_type => 1,

X_security_id =>NULL,

x_publish_flag => 'Y',

x_language => 'US',

x_description => l_filename,--l_description,

x_file_name => l_filename,

x_media_id => l_media_id

);

COMMIT;

DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);

dbms_output.put_line ('attachment_document id is l_attached_document_id '||l_attached_document_id);

dbms_output.put_line ('document id is l_document_id '||l_document_id);

dbms_output.put_line (' l_media_id '||l_media_id);

EXCEPTION

WHEN OTHERS

THEN

dbms_output.put_line('Failed '||sqlerrm);

commit;

end;

,