Submit Concurrent Program and email output to employee using PL/SQL Code

CREATE OR REPLACE PROCEDURE APPS.XXHR_auto_sumbit_certificate (

   p_person_id               NUMBER,

   p_person_extra_info_id    NUMBER,

   p_pei_information1        VARCHAR2                           -- Letter Type

)

/*******************************************************************************

   Name :

   requirment: Automate Request XXHR Salary Certificate for Bahraini Employees

   reference:

   dependenceies:

   Concurrent Programs Called:

   1) XXHR Salary Certificate for Bahraini Employees

   created by                : Bijoy Joseph

   creation date            : 03-OCT-2012

   last modified by            : --------------------

   last modified date         : --------------------

   purpose of modification    :

   revisions:

   ver        date        author                description

   ---------  ----------  ------------------    ------------------------------------

   1.0        03-OCT-2012  Bijoy Joseph

   notes:

******************************************************************************/

IS

   l_program_name           VARCHAR2 (100) := NULL;

   l_program_short_name     VARCHAR2 (100) := NULL;

   l_employee_number        VARCHAR2 (50) := NULL;

   l_request_number         NUMBER := NULL;

   l_responsibility_id      NUMBER := NULL;

   l_application_id         NUMBER := NULL;

   l_update_user_id         NUMBER := NULL;

   l_update_user_name       VARCHAR2 (50) := NULL;

   l_error_msg              VARCHAR2 (500) := NULL;

   l_xml_layout             BOOLEAN;

   l_notify_last_approver   BOOLEAN;

   l_email_last_approver    BOOLEAN;

   l_nationality            VARCHAR2 (500) := NULL;

   l_update_email           VARCHAR2 (500) := NULL;

BEGIN

   BEGIN

      SELECT   per.employee_number, UPPER (hl.meaning)

        INTO   l_employee_number, l_nationality

        FROM   per_people_x per, hr_lookups hl

       WHERE       per.person_id = p_person_id

               AND hl.lookup_type = 'NATIONALITY'

               AND hl.lookup_code = per.nationality;

   EXCEPTION

      WHEN OTHERS

      THEN

         l_employee_number := NULL;

         l_nationality := NULL;

   END;

   IF UPPER (p_pei_information1) = 'SALARY CERTIFICATE'

   THEN

      IF l_nationality = 'BAHRAINI'

      THEN

         l_program_name := 'XXHR Salary Certificate for Bahraini Employees';

         l_program_short_name := 'XXHR_BAHRAINI_SALARY_CERT';

      ELSE

         l_program_name :=

            'XXHR Salary Certificate for Non Bahraini Employees';

         l_program_short_name := 'XXHR_NON_BAHRAINI_SALARY_CERT';

      END IF;

   END IF;

   DBMS_OUTPUT.put_line ('l_program_name: ' || l_program_name);

   DBMS_OUTPUT.put_line ('l_program_short_name : ' || l_program_short_name);

   -- Get Resp ID, APPLI ID

   BEGIN

      SELECT   responsibility_id, application_id

        INTO   l_responsibility_id, l_application_id

        FROM   fnd_responsibility_tl

       WHERE   language = 'US'

               AND UPPER (responsibility_name) =

                     'XXHR EMPLOYEE RELATION USER';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_responsibility_id := NULL;

         l_application_id := NULL;

   END;

   -- Get Creator & User ID for Absence

   BEGIN

      SELECT   ppei.last_updated_by, fu.user_name, per.email_address

        INTO   l_update_user_id, l_update_user_name, l_update_email

        FROM   per_people_extra_info ppei, fnd_user fu, per_people_x per

       WHERE       ppei.person_extra_info_id = p_person_extra_info_id

               AND fu.employee_id = per.person_id

               AND fu.user_id = ppei.last_updated_by

               AND TRUNC (SYSDATE) BETWEEN fu.start_date

                                       AND  NVL (

                                               fu.end_date,

                                               TO_DATE ('31-DEC-4712',

                                                        'DD-MON-YYYY')

                                            );

   EXCEPTION

      WHEN OTHERS

      THEN

         l_update_user_id := NULL;

   END;

   DBMS_OUTPUT.put_line ('l_update_email : ' || l_update_email);

   -- Intitialize  User/ Responsiblity

   FND_GLOBAL.APPS_INITIALIZE (user_id        => l_update_user_id,

                               resp_id        => l_responsibility_id,

                               resp_appl_id   => l_application_id);

   --Set the Layout

   l_xml_layout :=

      fnd_request.add_layout ('XXHR',

                              l_program_short_name,

                              'en',

                              'US',

                              'PDF');

   -- Notify Last Approver

   l_notify_last_approver :=

      fnd_request.add_notification (l_update_user_name,

                                    'Y',                       -- Success Y/ N

                                    'Y',                       -- Warning Y/ N

                                    'Y'                          -- Error Y/ N

                                       );

   --Sends Email with Attachment

   l_email_last_approver :=

      fnd_delivery.add_email ('Alert: ' || l_program_name,

                              'amira.farouk@XXHR.org.bh',

--                              l_update_email

                              'bijoyj@mycompany.co.in'

                              );

   --Submit Request

   l_request_number :=

      fnd_request.submit_request (application   => 'XXHR',

                                  program       => l_program_short_name,

                                  description   => l_program_name,

                                  start_time    => SYSDATE,

                                  sub_request   => FALSE,

                                  argument1     => 0,

                                  argument2     => l_employee_number);

   COMMIT;

   DBMS_OUTPUT.put_line ('l_request_number: ' || l_request_number);

   IF l_request_number = 0

   THEN

      l_error_msg := fnd_message.get;

      DBMS_OUTPUT.put_line ('l_error_msg: ' || l_error_msg);

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('l_request_number: ' || SQLERRM);

      l_request_number := NULL;

END;