Self Service HR Script to allocate responsibilities and create users

Self Service HR Script to allocate responsibilities and create users, AskHareesh blog for Oracle Apps

Self Service HR Script to allocate responsibilities and create users

set scan on ;
set scan off ;

CREATE OR REPLACE PACKAGE BODY xx_sshr_allocate_resp_pkg IS
--DO NOT RUN THIS WITHOUT CHANGING XXPRD
--REPLACE XXPRD BY RESULT OF BELOW SQL FROM PRODUCTION
--select instance_name from v$instance ;

  --Created in Nov 06 by Anil Passi
  /*
  When      By           Why
  -----------------------------------------------
  29Nov06     AnilPassi  To allocate responsibilities
  01Dec06     Anil Passi To create new users too
                         Send emails to new users with their password etc
                         Send emails to existing users that they now have sshr
  */
  g_instance_name           VARCHAR2(100) := 'JUNK';
  g_debug_procedure_context VARCHAR2(50);
  g_debug_header_context CONSTANT VARCHAR2(80) := 'xxxx_sshr_allocate_resp_pkg.';

  PROCEDURE debug_begin_procedure IS
  BEGIN
  
    fnd_log.STRING(log_level => fnd_log.level_statement
                  ,module    => g_debug_header_context ||
                                g_debug_procedure_context
                  ,message   => 'Begin ' || g_debug_procedure_context);
  
    IF fnd_global.conc_request_id > 0 AND
       fnd_profile.VALUE('AFLOG_ENABLED') = 'Y'
    THEN
      fnd_file.put_line(which => fnd_file.log
                       ,buff  => 'Begin ' || g_debug_procedure_context);
    END IF;
  
  END debug_begin_procedure;

  PROCEDURE debug_stmt(p_msg IN VARCHAR2) IS
  BEGIN
  
    fnd_log.STRING(log_level => fnd_log.level_statement
                  ,module    => g_debug_header_context ||
                                g_debug_procedure_context
                  ,message   => p_msg);
    IF fnd_global.conc_request_id > 0
    THEN
      fnd_file.put_line(which => fnd_file.log
                       ,buff  => p_msg);
    END IF;
  END debug_stmt;

  PROCEDURE debug_end_procedure IS
  BEGIN
    fnd_log.STRING(log_level => fnd_log.level_statement
                  ,module    => g_debug_header_context ||
                                g_debug_procedure_context
                  ,message   => 'End ' || g_debug_procedure_context);
    IF fnd_global.conc_request_id > 0 AND
       fnd_profile.VALUE('AFLOG_ENABLED') = 'Y'
    THEN
      fnd_file.put_line(which => fnd_file.log
                       ,buff  => 'End ' || g_debug_procedure_context);
    END IF;
  
  END debug_end_procedure;

  PROCEDURE set_debug_context(p_procedure_name IN VARCHAR2) IS
  BEGIN
    g_debug_procedure_context := p_procedure_name;
    debug_begin_procedure;
  END set_debug_context;

  FUNCTION is_user_creation_possible(p_person_id IN INTEGER
                                    ,p_xxdp      OUT xx_windows_logon_table%ROWTYPE)
    RETURN VARCHAR2 IS
    CURSOR c_check IS
      SELECT xxdp.*
      FROM per_people_x ppx, xx_windows_logon_table xxdp
      WHERE ltrim(ppx.employee_number
                 ,'0') = ltrim(xxdp.emp_no
                              ,'0')
      AND ppx.person_id = p_person_id;
    p_check c_check%ROWTYPE;
  BEGIN
    OPEN c_check;
    FETCH c_check
      INTO p_check;
    CLOSE c_check;
    p_xxdp := p_check;
    IF p_check.emp_no IS NULL
    THEN
      RETURN 'No emp_no record in Network Login Table';
    ELSIF p_check.nt_login IS NULL
    THEN
      RETURN 'No NT Login Available for this Person in Network Login Table';
    ELSIF p_check.college_email_address IS NULL
    THEN
      RETURN 'No Email Address for this Person in Network Login Table';
    END IF;
    RETURN NULL;
  END is_user_creation_possible;

  FUNCTION get_email_from_emp_no(p_emp_no_email  IN VARCHAR2
                             ,p_test_email IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
  
    IF g_instance_name = 'XXPRD'
    THEN
    
      RETURN p_emp_no_email;
    ELSE
      RETURN p_test_email;
    END IF;
  END get_email_from_emp_no;

  FUNCTION does_fu_exist(p_fu_name IN VARCHAR2) RETURN BOOLEAN IS
    CURSOR c_check IS
      SELECT 'x' FROM fnd_user fu WHERE fu.user_name = upper(p_fu_name);
  
    p_check c_check%ROWTYPE;
  BEGIN
    OPEN c_check;
    FETCH c_check
      INTO p_check;
    IF c_check%FOUND
    THEN
      CLOSE c_check;
      RETURN TRUE;
    END IF;
    CLOSE c_check;
    RETURN FALSE;
  END does_fu_exist;

  PROCEDURE send_email_to_new_user(p_xxdp       IN xx_windows_logon_table%ROWTYPE
                                  ,p_user_name  IN VARCHAR2
                                  ,p_password   IN VARCHAR2
                                  ,p_test_email IN VARCHAR2) IS
  BEGIN
    DECLARE
    BEGIN
      send_html_email(p_to            => get_email_from_emp_no(p_xxdp.college_email_address
                                                           ,p_test_email)
                     ,p_from          => nvl(p_test_email
                                            ,'xxmail@gmail.com')
                     ,p_subject       => 'Welcome to Self Service HR'
                     ,p_text          => 'Welcome to Self Service HR'
                     ,p_html          => 'Your User Name : ' ||
                                         p_user_name ||
                                         '
Your Password : ' ||
                                         p_password || '

' ||
                                         'This user name and password gives you access the new Self Service HR.' ||
                                         '

Self Service HR enables Company staff to view and update their own personal data. The 
information is current and any changes made will be implemented immediately.' ||
                                         '

Please go to Spectrum following this link 
http://anilpassi.com' ||
                                         '
where you can log into Self Service HR, find out more and read the FAQs.' 
                     ,p_smtp_hostname => 'localhost'
                     ,p_smtp_portnum  => '25');
    
    END;
  END send_email_to_new_user;

  PROCEDURE send_email_to_existing_user(p_xxdp       IN xx_windows_logon_table%ROWTYPE
                                       ,p_test_email IN VARCHAR2) IS
  BEGIN
    DECLARE
    BEGIN
      send_html_email(p_to            => get_email_from_emp_no(p_xxdp.college_email_address
                                                           ,p_test_email)
                     ,p_from          => nvl(p_test_email
                                            ,'xxmail@gmail.com')
                     ,p_subject       => 'Welcome to Self Service HR'
                     ,p_text          => 'Welcome to Self Service HR'
                     ,p_html          => 'We are writing to let you know that the next time you log into Oracle Apps you will see a new
 responsibility, XX HR Employee Self Service. This responsibility gives you access the new
 Self Service HR feature in Oracle Apps.' ||
                                         'Self Service HR enables staff to view and update their own personal data.' ||
                                         'Please go to this link
http://anilpassi.com
to find out more and read the FAQs.' ||
                                         '

' || 'Regards' ||
                                         '

SSHR Rollout Team' ||
                                         '
' ||
                                         'HR Dept'
                     ,p_smtp_hostname => 'localhost'
                     ,p_smtp_portnum  => '25');
    
    END;
  END send_email_to_existing_user;

  FUNCTION get_latest_fu(p_proposed_fu_name IN VARCHAR2
                        ,p_proposed_offset  IN INTEGER) RETURN VARCHAR2 IS
  BEGIN
    IF does_fu_exist(p_proposed_fu_name || p_proposed_offset)
    THEN
      RETURN get_latest_fu(p_proposed_fu_name
                          ,p_proposed_offset + 1);
    END IF;
  
    RETURN upper(p_proposed_fu_name || p_proposed_offset);
  END get_latest_fu;

  FUNCTION get_fu_name(p_nt_login IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    IF NOT does_fu_exist(p_nt_login)
    THEN
      RETURN upper(p_nt_login);
    END IF;
  
    IF NOT does_fu_exist(p_nt_login)
    THEN
      RETURN upper(p_nt_login);
    END IF;
  
    RETURN get_latest_fu(p_nt_login
                        ,1);
  END get_fu_name;

  FUNCTION get_user_name_from_fu_per_id(p_person_id IN VARCHAR2)
    RETURN VARCHAR2 IS
    CURSOR c_get IS
      SELECT fu.user_name
      FROM fnd_user fu
      WHERE fu.employee_id = p_person_id;
  
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
  
    FETCH c_get
      INTO p_get;
  
    CLOSE c_get;
  
    RETURN p_get.user_name;
  END get_user_name_from_fu_per_id;

  FUNCTION get_random_password RETURN VARCHAR2 IS
  BEGIN
    RETURN lower(dbms_random.STRING('X'
                                   ,8));
  END get_random_password;

  FUNCTION get_person_name(p_person_id IN INTEGER) RETURN VARCHAR2 IS
    CURSOR c_get IS
      SELECT full_name
      FROM per_all_people_f
      WHERE person_id = p_person_id
      ORDER BY effective_start_date DESC;
    p_get c_get%ROWTYPE;
  
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
    RETURN p_get.full_name;
  END get_person_name;

  PROCEDURE create_fnd_user_for_emp_no(p_user_name          IN VARCHAR2
                                   ,p_person_id          IN INTEGER
                                   ,p_email_address      IN VARCHAR2
                                   ,p_person_description IN VARCHAR2
                                   ,p_password           OUT VARCHAR2) IS
    v_session_id VARCHAR2(200);
    v_password   VARCHAR2(100) := get_random_password;
  BEGIN
    p_password := v_password;
    fnd_user_pkg.createuser(x_user_name              => p_user_name
                           ,x_owner                  => ''
                           ,x_unencrypted_password   => v_password
                           ,x_description            => p_person_description
                           ,x_password_lifespan_days => 180
                           ,x_employee_id            => p_person_id
                           ,x_email_address          => p_email_address);
  
  END create_fnd_user_for_emp_no;

  FUNCTION get_fu_id(p_fu_name IN VARCHAR2) RETURN VARCHAR2 IS
    CURSOR c_get IS
      SELECT user_id FROM fnd_user WHERE user_name = p_fu_name;
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
    RETURN p_get.user_id;
  END get_fu_id;

  FUNCTION create_fnd_user(p_person_id             IN INTEGER
                          ,p_xxdp                  IN xx_windows_logon_table%ROWTYPE
                          ,p_new_fnd_user_name     OUT VARCHAR2
                          ,p_new_fnd_user_password OUT VARCHAR2)
    RETURN INTEGER IS
    v_user_name fnd_user.user_name%TYPE;
    v_password  VARCHAR2(200);
    v_err       VARCHAR2(2000);
  BEGIN
    v_user_name := get_fu_name(p_nt_login => p_xxdp.nt_login);
    debug_stmt('For p_xxdp.nt_login=>' || p_xxdp.nt_login ||
               ' the username is ' || v_user_name);
    create_fnd_user_for_emp_no(p_user_name          => p_xxdp.nt_login
                           ,p_person_id          => p_person_id
                           ,p_email_address      => p_xxdp.college_email_address
                           ,p_person_description => p_xxdp.title || ' ' ||
                                                    p_xxdp.first_name || ' ' ||
                                                    p_xxdp.last_name
                           ,p_password           => v_password);
    p_new_fnd_user_name     := v_user_name;
    p_new_fnd_user_password := v_password;
    RETURN get_fu_id(p_fu_name => v_user_name);
  EXCEPTION
    WHEN OTHERS THEN
      v_err := substr(SQLERRM
                     ,1
                     ,2000);
      debug_stmt(v_err);
      RETURN NULL;
  END create_fnd_user;

  PROCEDURE send_html_email(p_to            IN VARCHAR2
                           ,p_from          IN VARCHAR2
                           ,p_subject       IN VARCHAR2
                           ,p_text          IN VARCHAR2 DEFAULT NULL
                           ,p_html          IN VARCHAR2 DEFAULT NULL
                           ,p_smtp_hostname IN VARCHAR2
                           ,p_smtp_portnum  IN VARCHAR2) IS
    l_boundary   VARCHAR2(255) DEFAULT 'a1b2c3d4e3f2g1';
    l_connection utl_smtp.connection;
    l_body_html  CLOB := empty_clob; --This LOB will be the email message
    l_offset     NUMBER;
    l_ammount    NUMBER;
    l_temp       VARCHAR2(32767) DEFAULT NULL;
  BEGIN
    /* Usage......
        html_email(p_to          => 'a.passi@Company.ac.uk'
                ,p_from          => 'anilpassi@gmail.com'
                ,p_subject       => 'Testing from anil'
                ,p_text          => 'ABCD'
                ,p_html          => 'IJKLM Testing for the HTML Format of the email'
                ,p_smtp_hostname => 'localhost'
                ,p_smtp_portnum  => '25');
    */
    l_connection := utl_smtp.open_connection(p_smtp_hostname
                                            ,p_smtp_portnum);
    utl_smtp.helo(l_connection
                 ,p_smtp_hostname);
    utl_smtp.mail(l_connection
                 ,p_from);
    utl_smtp.rcpt(l_connection
                 ,p_to);
  
    l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
    l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
    l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
    l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
    l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
    l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
              chr(34) || l_boundary || chr(34) || chr(13) || chr(10);
  
    ----------------------------------------------------
    -- Write the headers
    dbms_lob.createtemporary(l_body_html
                            ,FALSE
                            ,10);
    dbms_lob.WRITE(l_body_html
                  ,length(l_temp)
                  ,1
                  ,l_temp);
  
    ----------------------------------------------------
    -- Write the text boundary
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    l_temp   := '--' || l_boundary || chr(13) || chr(10);
    l_temp   := l_temp || 'content-type: text/plain; charset=us-ascii' ||
                chr(13) || chr(10) || chr(13) || chr(10);
    dbms_lob.WRITE(l_body_html
                  ,length(l_temp)
                  ,l_offset
                  ,l_temp);
  
    ----------------------------------------------------
    -- Write the plain text portion of the email
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.WRITE(l_body_html
                  ,length(p_text)
                  ,l_offset
                  ,p_text);
  
    ----------------------------------------------------
    -- Write the HTML boundary
    l_temp   := chr(13) || chr(10) || chr(13) || chr(10) || '--' ||
                l_boundary || chr(13) || chr(10);
    l_temp   := l_temp || 'content-type: text/html;' || chr(13) || chr(10) ||
                chr(13) || chr(10);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.WRITE(l_body_html
                  ,length(l_temp)
                  ,l_offset
                  ,l_temp);
  
    ----------------------------------------------------
    -- Write the HTML portion of the message
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.WRITE(l_body_html
                  ,length(p_html)
                  ,l_offset
                  ,p_html);
  
    ----------------------------------------------------
    -- Write the final html boundary
    l_temp   := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.WRITE(l_body_html
                  ,length(l_temp)
                  ,l_offset
                  ,l_temp);
  
    ----------------------------------------------------
    -- Send the email in 1900 byte chunks to UTL_SMTP
    l_offset  := 1;
    l_ammount := 1900;
    utl_smtp.open_data(l_connection);
    WHILE l_offset < dbms_lob.getlength(l_body_html)
    LOOP
      utl_smtp.write_data(l_connection
                         ,dbms_lob.substr(l_body_html
                                         ,l_ammount
                                         ,l_offset));
      l_offset  := l_offset + l_ammount;
      l_ammount := least(1900
                        ,dbms_lob.getlength(l_body_html) - l_ammount);
    END LOOP;
    utl_smtp.close_data(l_connection);
    utl_smtp.quit(l_connection);
    dbms_lob.freetemporary(l_body_html);
  END send_html_email;

  PROCEDURE excel_output(p_msg IN VARCHAR2) IS
  BEGIN
    fnd_file.put_line(fnd_file.output
                     ,p_msg);
  END excel_output;

  FUNCTION get_user_name(p_user_id IN INTEGER) RETURN VARCHAR2 IS
    CURSOR c_get IS
      SELECT user_name FROM fnd_user WHERE user_id = p_user_id;
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
    RETURN p_get.user_name;
  END get_user_name;

  FUNCTION get_emp_no(p_person_id IN INTEGER) RETURN VARCHAR2 IS
    CURSOR c_get IS
      SELECT employee_number
      FROM xx_per_all_people_x
      WHERE person_id = p_person_id;
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
    RETURN p_get.employee_number;
  END get_emp_no;

  FUNCTION get_cost_centre_group(p_person_id IN INTEGER) RETURN VARCHAR2 IS
    CURSOR c_get IS
      SELECT hrou1.attribute5
      FROM hr_all_organization_units hrou1
          ,hr_all_organization_units hrou
          ,xx_per_all_asg_x          ass
          ,xx_per_all_people_x       ppx
      WHERE ppx.person_id = p_person_id
      AND ass.person_id = ppx.person_id
      AND ass.assignment_number IS NOT NULL
      AND ass.primary_flag = 'Y'
      AND hrou.organization_id = ass.organization_id
      AND hrou1.NAME = primaryhro_pkg.fn_get_primaryhro(ass.organization_id);
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
    RETURN p_get.attribute5;
  END get_cost_centre_group;

  FUNCTION get_parent_org(p_person_id IN INTEGER) RETURN VARCHAR2 IS
    CURSOR c_get IS
      SELECT primaryhro_pkg.fn_get_primaryhro(ass.organization_id) parent_org
      FROM hr_all_organization_units hrou
          ,xx_per_all_asg_x          ass
          ,xx_per_all_people_x       ppx
      WHERE ppx.person_id = p_person_id
      AND ass.person_id = ppx.person_id
      AND ass.assignment_number IS NOT NULL
      AND ass.primary_flag = 'Y'
      AND hrou.organization_id = ass.organization_id;
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
    RETURN p_get.parent_org;
  END get_parent_org;

  FUNCTION get_grade(p_person_id IN INTEGER) RETURN VARCHAR2 IS
    CURSOR c_get IS
      SELECT pg.NAME
      FROM per_grade_definitions pgd
          ,per_grades            pg
          ,xx_per_all_asg_x      ass
          ,xx_per_all_people_x   ppx
      WHERE ppx.person_id = p_person_id
      AND ass.person_id = ppx.person_id
      AND ass.assignment_number IS NOT NULL
      AND ass.primary_flag = 'Y'
      AND pg.grade_id = ass.grade_id
      AND pgd.grade_definition_id = pg.grade_definition_id;
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
    RETURN p_get.NAME;
  END get_grade;

  PROCEDURE run(errbuf                        OUT VARCHAR2
               ,retcode                       OUT VARCHAR2
               ,p_responsibility_name         IN VARCHAR2
               ,p_person_type                 IN VARCHAR2
               ,p_cost_centre_group_1                   IN VARCHAR2
               ,p_cost_centre_group_2                   IN VARCHAR2
               ,p_parent_org_1                      IN VARCHAR2
               ,p_parent_org_2                      IN VARCHAR2
               ,p_emp_no                         IN VARCHAR2
               ,p_read_only_flag              IN VARCHAR2
               ,p_test_ceration_email_address IN VARCHAR2) IS
    n_count   INTEGER;
    v_sqlerrm VARCHAR2(2000);
    can_not_fnd_create_user EXCEPTION;
    error_in_fnd_user_pkg EXCEPTION;
    v_fnd_user_name VARCHAR2(100);
    CURSOR c_get IS
      SELECT *
      FROM fnd_responsibility_vl
      WHERE responsibility_name =
            nvl('XX HR Employee Self Service'
               ,p_responsibility_name);
  
    p_get c_get%ROWTYPE;
  
    duplicate_responsibility EXCEPTION;
    PRAGMA EXCEPTION_INIT(duplicate_responsibility
                         ,-20001);
    v_hard_password     VARCHAR2(1) := fnd_profile.VALUE('SIGNON_PASSWORD_HARD_TO_GUESS');
    l_xxdp              xx_windows_logon_table%ROWTYPE;
    b_new_user_created  BOOLEAN;
    v_fnd_user_password VARCHAR2(100);
  BEGIN
    set_debug_context('run');
  
    SELECT instance_name INTO g_instance_name FROM v$instance;
    debug_stmt('g_instance_name=>' || g_instance_name);
    fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
                   ,val  => 'N');
  
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
  
    --lets dump the records first into the temp table, 
    --this will be followed by
    --a. see which people do not have Sign On
    --b. Which people already have Responsibility
    INSERT INTO xx_sshr_allocate_resp
      (sshr_allocate_resp_id
      ,person_id
      ,future_dated_employee_flag
      ,responsibillity_name
      ,error_during_resp_allocation
      ,fnd_user_id
      ,fnd_request_id
      ,email_address)
      (SELECT xx_sshr_allocate_resp_s.NEXTVAL
             ,ppx.person_id --PERSON_ID                      
             ,'N' --FUTURE_DATED_EMPLOYEE_FLAG     
             ,p_responsibility_name --responsibillity_name            
             ,NULL --ERROR_DURING_RESP_ALLOCATION   
             ,NULL --FND_USER_ID                    
             ,fnd_global.conc_request_id --FND_REQUEST_ID                 
             ,ppx.email_address
       FROM per_person_types         ppt
           ,per_person_type_usages_x pptux
           ,xx_per_all_people_x      ppx
       WHERE ppx.person_id = pptux.person_id
       AND ppt.person_type_id = pptux.person_type_id
       AND ppx.employee_number = nvl(p_emp_no
                                   ,ppx.employee_number)
       AND ppt.system_person_type = 'EMP'
       AND ppt.user_person_type = p_person_type
       AND ppt.business_group_id =
             fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
       AND EXISTS (SELECT 'x'
              FROM hr_all_organization_units hrou1
                  ,hr_all_organization_units hrou
                  ,xx_per_all_asg_x          pax
              WHERE p_cost_centre_group_1 IS NOT NULL
              AND pax.person_id = ppx.person_id
              AND pax.primary_flag = 'Y'
              AND pax.assignment_number IS NOT NULL
              AND hrou.organization_id = pax.organization_id
              AND hrou1.NAME =
                    primaryhro_pkg.fn_get_primaryhro(pax.organization_id)
              AND hrou1.attribute5 IN
                    (nvl(p_cost_centre_group_1
                     ,'XXXX'), nvl(p_cost_centre_group_2
                               ,'XXXX'))
              UNION ALL
              SELECT 'x'
              FROM dual
              WHERE (p_cost_centre_group_1 IS NULL AND p_cost_centre_group_2 IS NULL))
       AND EXISTS
        (SELECT 'x'
              FROM hr_all_organization_units hrou, xx_per_all_asg_x pax
              WHERE p_parent_org_1 IS NOT NULL
              AND pax.person_id = ppx.person_id
              AND pax.primary_flag = 'Y'
              AND pax.assignment_number IS NOT NULL
              AND hrou.organization_id = pax.organization_id
              AND primaryhro_pkg.fn_get_primaryhro(pax.organization_id) IN
                    (nvl(p_parent_org_1
                     ,'XXXX'), nvl(p_parent_org_2
                               ,'XXXX'))
              UNION ALL
              SELECT 'x'
              FROM dual
              WHERE (p_parent_org_1 IS NULL AND p_parent_org_2 IS NULL)));
    n_count := SQL%ROWCOUNT;
  
    debug_stmt(n_count ||
               ' Records inserted into Temp Table based on Eligibility Criteria');
  
    INSERT INTO xx_sshr_allocate_resp
      (sshr_allocate_resp_id
      ,person_id
      ,future_dated_employee_flag
      ,responsibillity_name
      ,error_during_resp_allocation
      ,fnd_user_id
      ,fnd_request_id
      ,email_address)
      (SELECT xx_sshr_allocate_resp_s.NEXTVAL
             ,ppx.person_id --PERSON_ID                      
             ,'Y' --FUTURE_DATED_EMPLOYEE_FLAG     
             ,p_responsibility_name --responsibillity_name            
             ,'Employee Is a Future Starter' --ERROR_DURING_RESP_ALLOCATION   
             ,NULL --FND_USER_ID                    
             ,fnd_global.conc_request_id --FND_REQUEST_ID                 
             ,ppx.email_address
       FROM per_person_types              ppt
           ,xx_per_person_type_usages_eot pptux
           ,xx_per_all_people_eot         ppx
       WHERE NOT EXISTS
        (SELECT 'x'
              FROM xx_sshr_allocate_resp iar
              WHERE iar.person_id = ppx.person_id
              AND fnd_request_id = fnd_global.conc_request_id)
       AND ppx.person_id = pptux.person_id
       AND ppt.person_type_id = pptux.person_type_id
       AND ppx.employee_number = nvl(p_emp_no
                                   ,ppx.employee_number)
       AND ppt.system_person_type = 'EMP'
       AND ppt.user_person_type = p_person_type
       AND ppt.business_group_id =
             fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
       AND EXISTS (SELECT 'x'
              FROM hr_all_organization_units hrou1
                  ,hr_all_organization_units hrou
                  ,xx_per_all_asg_x          pax
              WHERE p_cost_centre_group_1 IS NOT NULL
              AND pax.person_id = ppx.person_id
              AND pax.primary_flag = 'Y'
              AND pax.assignment_number IS NOT NULL
              AND hrou.organization_id = pax.organization_id
              AND hrou1.NAME =
                    primaryhro_pkg.fn_get_primaryhro(pax.organization_id)
              AND hrou1.attribute5 IN
                    (nvl(p_cost_centre_group_1
                     ,'XXXX'), nvl(p_cost_centre_group_2
                               ,'XXXX'))
              UNION ALL
              SELECT 'x'
              FROM dual
              WHERE (p_cost_centre_group_1 IS NULL AND p_cost_centre_group_2 IS NULL))
       AND EXISTS
        (SELECT 'x'
              FROM hr_all_organization_units hrou, xx_per_all_asg_x pax
              WHERE p_parent_org_1 IS NOT NULL
              AND pax.person_id = ppx.person_id
              AND pax.primary_flag = 'Y'
              AND pax.assignment_number IS NOT NULL
              AND hrou.organization_id = pax.organization_id
              AND primaryhro_pkg.fn_get_primaryhro(pax.organization_id) IN
                    (nvl(p_parent_org_1
                     ,'XXXX'), nvl(p_parent_org_2
                               ,'XXXX'))
              UNION ALL
              SELECT 'x'
              FROM dual
              WHERE (p_parent_org_1 IS NULL AND p_parent_org_2 IS NULL)));
    n_count := SQL%ROWCOUNT;
    debug_stmt(n_count ||
               ' Records inserted into Temp Table that aer eligible but Future Dated');
    --Commenting the below, as we need to create User Accounts for these folks
    /*  UPDATE xx_sshr_allocate_resp isar
        SET error_during_resp_allocation = 'Employee Is Not a User'
        WHERE isar.fnd_request_id = fnd_global.conc_request_id
        AND error_during_resp_allocation IS NULL
        AND NOT EXISTS
         (SELECT 'x' FROM fnd_user fu WHERE fu.employee_id = isar.person_id);
        n_count := SQL%ROWCOUNT;
        put_log(n_count || ' Records errored due to them not being Employee');
    */
    UPDATE xx_sshr_allocate_resp isar
    SET fnd_user_id = (SELECT user_id
                       FROM fnd_user
                       WHERE employee_id = isar.person_id
                       AND rownum < 2)
    WHERE isar.fnd_request_id = fnd_global.conc_request_id
    AND error_during_resp_allocation IS NULL;
  
    UPDATE xx_sshr_allocate_resp isar
    SET responsibility_alloc_date = (SELECT start_date
                                     FROM fnd_user_resp_groups_direct
                                     WHERE user_id = isar.fnd_user_id
                                     AND responsibility_id =
                                           p_get.responsibility_id
                                     AND rownum < 2)
    WHERE isar.fnd_request_id = fnd_global.conc_request_id;
  
    n_count := SQL%ROWCOUNT;
    debug_stmt(n_count ||
               ' Records were attempted to be assigned existing responsibility_alloc_date');
  
    UPDATE xx_sshr_allocate_resp isar
    SET error_during_resp_allocation = 'Responsibility Already Allocated on ' ||
                                       to_char(responsibility_alloc_date
                                              ,'DD-MON-YYYY')
    WHERE isar.fnd_request_id = fnd_global.conc_request_id
    AND responsibility_alloc_date IS NOT NULL;
    n_count := SQL%ROWCOUNT;
    debug_stmt(n_count ||
               ' Records errored as they already have the responsibility');
  
    /*    UPDATE xx_sshr_allocate_resp isar
        SET error_during_resp_allocation = 'Employees User Record is Terminated'
        WHERE isar.fnd_request_id = fnd_global.conc_request_id
        AND error_during_resp_allocation IS NULL
        AND EXISTS (SELECT 'x'
               FROM fnd_user fu
               WHERE fu.employee_id = isar.person_id
               AND NOT (trunc(SYSDATE) BETWEEN
                      nvl(fu.start_date
                          ,trunc(SYSDATE)) AND
                      nvl(fu.start_date
                          ,trunc(SYSDATE))));
        n_count := SQL%ROWCOUNT;
        put_log(n_count || ' Records errored as their FND_USER is end dated');
    */
  
    UPDATE xx_sshr_allocate_resp isar
    SET error_during_resp_allocation = 'No Email Address'
    WHERE isar.fnd_request_id = fnd_global.conc_request_id
    AND isar.email_address IS NULL
    AND error_during_resp_allocation IS NULL;
    n_count := SQL%ROWCOUNT;
    debug_stmt(n_count ||
               ' Records errored as they have no email address in HRMS');
  
    UPDATE xx_sshr_allocate_resp isar
    SET fnd_user_id = (SELECT user_id
                       FROM fnd_user
                       WHERE employee_id = isar.person_id
                       AND rownum < 2)
    WHERE isar.fnd_request_id = fnd_global.conc_request_id
    AND error_during_resp_allocation IS NULL;
  
    n_count := SQL%ROWCOUNT;
    debug_stmt(n_count ||
               ' Records aer unerrored, and hence will be processed further');
  
    excel_output('Action' || chr(9) || 'UserName' || chr(9) || 'emp_no' ||
                 chr(9) || 'Person Full Name' || chr(9) ||
                 'Allocation Date' || chr(9) || 'Error' || chr(9) ||
                 'cost_centre_group' || chr(9) || 'parent_org' || chr(9) || 'Grade');
  
    FOR p_rec IN (SELECT *
                  FROM xx_sshr_allocate_resp isar
                  WHERE isar.fnd_request_id = fnd_global.conc_request_id
                  AND error_during_resp_allocation IS NULL)
    LOOP
      BEGIN
        l_xxdp              := NULL;
        v_fnd_user_password := NULL;
        b_new_user_created  := FALSE;
        v_fnd_user_name     := NULL;
        v_sqlerrm           := is_user_creation_possible(p_person_id => p_rec.person_id
                                                        ,p_xxdp      => l_xxdp);
        debug_stmt('p_rec.fnd_user_id =>' || p_rec.fnd_user_id);
        debug_stmt('Is user creation possible returned => ' || v_sqlerrm);
        IF p_rec.fnd_user_id IS NULL AND v_sqlerrm IS NOT NULL
        THEN
        
          RAISE can_not_fnd_create_user;
        END IF;
      
        IF NOT (p_read_only_flag = 'Y')
        THEN
          debug_stmt('Not read only');
          IF p_rec.fnd_user_id IS NULL
          THEN
            debug_stmt('Looks like new user is needed');
          
            p_rec.fnd_user_id := create_fnd_user(p_person_id             => p_rec.person_id
                                                ,p_xxdp                  => l_xxdp
                                                ,p_new_fnd_user_name     => v_fnd_user_name
                                                ,p_new_fnd_user_password => v_fnd_user_password);
            IF p_rec.fnd_user_id IS NULL
            THEN
              RAISE error_in_fnd_user_pkg;
            ELSE
              UPDATE xx_sshr_allocate_resp ir
              SET ir.fnd_user_id    = p_rec.fnd_user_id
                 ,new_fnd_user_flag = 'Y'
                 ,messsage_code     = v_fnd_user_password
              WHERE ir.sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
              b_new_user_created := TRUE;
            END IF;
          END IF;
          fnd_user_resp_groups_api.insert_assignment(user_id                       => p_rec.fnd_user_id
                                                    ,responsibility_id             => p_get.responsibility_id
                                                    ,responsibility_application_id => p_get.application_id
                                                    ,security_group_id             => 0
                                                    ,start_date                    => trunc(SYSDATE)
                                                    ,end_date                      => NULL
                                                    ,description                   => 'Auto Allocation for SSHR');
          UPDATE xx_sshr_allocate_resp
          SET responsibility_alloc_date = SYSDATE
          WHERE sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
          IF b_new_user_created
          THEN
            excel_output('Allocated[With New User]' || chr(9) ||
                         get_user_name(p_rec.fnd_user_id) || chr(9) ||
                         get_emp_no(p_rec.person_id) || chr(9) ||
                         get_person_name(p_rec.person_id) || chr(9) ||
                         to_char(trunc(SYSDATE)
                                ,'DD-MON-YYYY') || chr(9) || '' || chr(9) ||
                         get_cost_centre_group(p_rec.person_id) || chr(9) ||
                         get_parent_org(p_rec.person_id) || chr(9) ||
                         get_grade(p_rec.person_id));
            send_email_to_new_user(p_xxdp       => l_xxdp
                                  ,p_user_name  => v_fnd_user_name
                                  ,p_password   => v_fnd_user_password
                                  ,p_test_email => p_test_ceration_email_address);
          ELSE
            excel_output('Allocated' || chr(9) ||
                         get_user_name(p_rec.fnd_user_id) || chr(9) ||
                         get_emp_no(p_rec.person_id) || chr(9) ||
                         get_person_name(p_rec.person_id) || chr(9) ||
                         to_char(trunc(SYSDATE)
                                ,'DD-MON-YYYY') || chr(9) || '' || chr(9) ||
                         get_cost_centre_group(p_rec.person_id) || chr(9) ||
                         get_parent_org(p_rec.person_id) || chr(9) ||
                         get_grade(p_rec.person_id));
            send_email_to_existing_user(p_xxdp       => l_xxdp
                                       ,p_test_email => p_test_ceration_email_address);
          END IF;
          COMMIT;
        ELSE
          IF p_rec.fnd_user_id IS NULL
          THEN
            excel_output('Eligible [New User Will Be Created]' || chr(9) ||
                         nvl(get_user_name(p_rec.fnd_user_id)
                            ,get_fu_name(l_xxdp.nt_login)) || chr(9) ||
                         get_emp_no(p_rec.person_id) || chr(9) ||
                         get_person_name(p_rec.person_id) || chr(9) ||
                         chr(9) || chr(9) || get_cost_centre_group(p_rec.person_id) ||
                         chr(9) || get_parent_org(p_rec.person_id) || chr(9) ||
                         get_grade(p_rec.person_id));
          ELSE
            excel_output('Eligible' || chr(9) ||
                         get_user_name(p_rec.fnd_user_id) || chr(9) ||
                         get_emp_no(p_rec.person_id) || chr(9) ||
                         get_person_name(p_rec.person_id) || chr(9) ||
                         chr(9) || chr(9) || get_cost_centre_group(p_rec.person_id) ||
                         chr(9) || get_parent_org(p_rec.person_id) || chr(9) ||
                         get_grade(p_rec.person_id));
          END IF;
        END IF;
      EXCEPTION
        WHEN can_not_fnd_create_user THEN
          UPDATE xx_sshr_allocate_resp ir
          SET ir.error_during_resp_allocation = v_sqlerrm
          WHERE sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
        WHEN error_in_fnd_user_pkg THEN
          UPDATE xx_sshr_allocate_resp ir
          SET ir.error_during_resp_allocation = 'Error while creating FND User. Please see Concurrent Log file for details'
          WHERE sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
        WHEN OTHERS THEN
          v_sqlerrm := SQLERRM;
          UPDATE xx_sshr_allocate_resp
          SET error_during_resp_allocation = substr(v_sqlerrm
                                                   ,1
                                                   ,2000)
          WHERE sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
        
      END;
    END LOOP;
  
    FOR p_recx IN (SELECT *
                   FROM xx_sshr_allocate_resp isar
                   WHERE isar.fnd_request_id = fnd_global.conc_request_id
                   AND error_during_resp_allocation IS NOT NULL)
    LOOP
      excel_output('Error' || chr(9) || get_user_name(p_recx.fnd_user_id) ||
                   chr(9) || get_emp_no(p_recx.person_id) || chr(9) ||
                   get_person_name(p_recx.person_id) || chr(9) ||
                   to_char(p_recx.responsibility_alloc_date
                          ,'DD-MON-YYYY') || chr(9) ||
                   p_recx.error_during_resp_allocation || chr(9) ||
                   get_cost_centre_group(p_recx.person_id) || chr(9) ||
                   get_parent_org(p_recx.person_id) || chr(9) ||
                   get_grade(p_recx.person_id));
    END LOOP;
    fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
                   ,val  => v_hard_password);
    debug_end_procedure;
  EXCEPTION
    WHEN OTHERS THEN
      fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
                     ,val  => v_hard_password);
      RAISE;
  END run;

END xx_sshr_allocate_resp_pkg;

*/

No comments:

Post a Comment