Making Service Request form view-only for specific responsibility

1. Navigate to the Define Responsibility form. System Administrator>Security>Responsibility>Define
2. Query the specific responsibility. The responsibility  should be associated with the CSX_CUSTOMER_SUPPORT menu
3. Exclude the following functions

Enter Service Request
Update Service Requests
Access Find Product UI
Access Product Configuration Button
Create Note
Update Note
Access Note Detail Button
Access View Log Button
Create Contact and Contact Point
Update Contact and Contact Point
Create Tasks
Update Task
Access Use Template Button
Access Launch Workflow Button
Access Task Details UI
Create Related Document
Update Related Document & Access Delete Link button
Access Customer Profile
Access Customer Update UI
Access AM UI
Access UWQ Buttons
Access UWQ Buttons (Call Info, Call Wrap Up, End Call, Agent on Break
Buttons)
Create Interactions and Activities
Access Folder Button
Access Attachments
Access Translation
Access Fulfillment Special Menu Item
Access Quick Menu Special Menu Item
Access Notes Special Menu Item
Access Launch Service Request Workflow Special Menu Item
Access Abort Service Request Workflow Special Menu Item
Access Service Request Escalation Special Menu Item
Access Task Escalation Special Menu Item
Access Send Message Special Menu Item
Access View Message Special Menu Item
Access Copy Service Request Special Menu Item
Access Counters Special Menu Item
Access Charges Special Menu Item
Access Service Request Audit Special Menu Item
Access Scripting Special Menu Item
Access Outbound Email Special Menu Item
Access Print Request Special Menu Item
Access Create Defect Special Menu Item
Access Create Enhancement Special Menu Item
Access Web Availability Special Menu Item
Access Get Contracts Button
Access Maintain Contact Button
Access Copy Task Button
Create Charges
Update Charges

Using Web ADI to create Service Request

Web Applications Desktop Integrator is a tool that leverages the standard desktop applications to perform some of the Oracle E-Business Suite task. The Web ADI brings Oracle E-Business suite to the desktop where familiar desktop tool like Excel can be used to create spreadsheet, enter and modify data in the spreadsheet and finally upload the data into the Oracle Applications. Web ADI can be very useful for the users who are experienced with Excel and will like to use Excel to enter the data related to Oracle Applications. These users can use the special features of excel i.e. copy, paste, drag cells to increment and various arithmetic functions to optimize their business task and increase their productivity. Data that is entered into the spreadsheet can then be finally be uploaded in to the Oracle Applications with or without validation.

– From the Oracle white paper, Web ADI: Extending E-Business Suite with Desktop Applications

In this post we will see an example of using Web ADI to create Service Requests in Oracle E-Business Suite 11i based on data entered in Microsoft Excel. Though creating a service request isn’t what Web ADI is commonly used for but for the purpose of demonstrating how to create and use custom integrator in Web ADI, it is quite sufficient. Also note that this example is for EBS 11i, there are a few differences in case of R12.

Prerequisite steps to ensure that Web ADI works for you:

1. Set profile option, ‘BNE Allow No Security Rule’ to ‘Yes’
2. Assign ‘Oracle Web ADI’ responsibility to your user. This responsibility has the menu ‘WEBADI MENU’ associated with it
3. Add the submenu, ‘HR ADI Seeded Integrator Form Functions’ to the ‘WEBADI MENU’
4. Enable macros for Microsoft Excel

Once the prerequisite steps are completed, we can start with creating the custom integrator which will upload the service request data from Excel sheet to Oracle E-Business suite and use this data for creating the service request.

The basic steps for creating a custom integrator for Web ADI are:

• Define the custom integrator – This is done by using the Web ADI functionality itself to create a document, populating it with the necessary information and then uploading the document to create the integrator.
• Create a layout for the custom integrator
• Create mappings for the integrator if there is a need to load content from a text file (we will not be performing this step in our example)

1. Create a wrapper package which will use the data uploaded through the Excel sheet to create the service request. I will be using the Create Service Request API in my code. The package specifications for the package which I am using are given below. Note that the procedure xxx_create_sr which creates the Service Requests has five input parameters out of which two have default values. These input parameters will take their values from the Excel sheet.

create or replace package xxx_my_webadi is
 procedure xxx_create_sr(p_consumer_id in varchar2,
 p_complaint_type in varchar2,
 p_problem_summary in varchar2,
 p_optional in varchar2 default null,
 p_comments in varchar2 default null);
end xxx_my_webadi;

2. Define the custom integrator by navigating to Oracle Web ADI>Create Document. Select Excel 2003 as the viewer(do not select the reporting checkbox) and HR Integrator Setup(pre-defined integrator provided by Oracle for creating custom integrators) as the Integrator. Keep the default values for the other options. Review and click on ‘Create Document’

3. A confirmation message will be displayed and an Excel sheet will open. Following details are filled in the Excel sheet:

• Metadata Type- CREATE
• Application Short Name- CS(since I am using the integrator for creating Service Request)
• Integrator User Name- XXX_SR_Test_Intg
• Form Name- GENERAL
• API Package Name- xxx_my_webadi(from Step#1)
• API Procedure Name- xxx_create_sr(from Step#1)
• Interface User Name- XXX_SR_Test_Intf
• Interface Parameter List Name- XXX_SR_Test_Param
• API Type- PROCEDURE.

Note that you can use the List of Values(Add-Ins>Oracle>List of Values) to fill in some of the fields such as Metadata Type, Application Short Name, Form Name and API Type. Double-clicking on these fields will also invoke the List of Values window.

4. Upload the document through the Add-Ins tab(Add-Ins>Oracle>Upload). A confirmation message will be displayed

5. Next the layout needs to be defined by following the navigation Oracle Web ADI>Define Layout. Select the integrator XXX_SR_Test_Intg defined in Step#3 and define the layout. Specify a Layout Name and a Layout Key.

6. The layout has some required fields and some optional fields. The fields are derived from the input parameters of the procedure in Step#1. Note that the input parameters without default values are indicated as required fields here. Default values are specified for the required fields though this is not a mandatory step. Also, I have used one of the optional fields for comments/instructions to the user and I have specified this as a header field so that it is displayed only once in the Excel sheet.

7. The basic steps for creating and using the custom Web ADI integrator is complete and the user can access it by navigating to Oracle Web ADI>Create Document, selecting the integrator and other relevant options. However in order to make this integrator available to other responsibilities and to automatically default other options so that the user does not have to select them every time, we will define a custom function for the integrator by navigating to Application Developer>Application>Function. Details to be provided are:

• Type- SSWA servlet function
• HTML Call- oracle.apps.bne.webui.BneApplicationService
• Parameters- bne:page= BneCreateDoc&bne:viewer= BNE:EXCEL2003&bne:reporting= N&bne:integrator= USER_NAME:XXX_SR_Test_Intg&bne:noreview= Yes (I have inserted spaces after the equals symbol for formatting purposes, please remember to remove them)

8. Assign the function to the appropriate menu

9. Invoke the integrator by accessing the function defined in Step#7 and 8.

10. The following Excel sheet will open. Note the comment in the header section and the default values for the required fields. Double click on the ‘Upl’ column to flag the rows which you want to upload and make necessary changes. Upload the data through Add-Ins>Oracle>Upload. You can select the options as required in the Upload Parameters window.

11. A confirmation message and green smileys will be displayed denoting successful upload.

Resources:

1. Metalink Note#603169.1 -WebADI and Report Manager Webcasts for EBS 11i/R12 — Replay is Now Available
2. Metalink Note#360105.1 -Understanding and Using Web ADI in Oracle HRMS.

Sample code for creating task using jtf_tasks_pub.create_task API

Here is a working(at least in my instance) sample of creating a Task against a specific Service Request using the jtf_tasks_pub.create_task API.

create or replace procedure xxx_create_task as
  v_user_id       number;
  v_resp_id       number;
  v_appl_id       number;
  l_msg_count     number;
  l_msg_data      varchar2(2000);
  l_return_status varchar2(1);
  l_task_id       number;
  v_resource_id   number;
  v_incident_id   number;
begin

  --the task is being created against Service Request#1000004940
  select incident_id
    into v_incident_id
    from cs_incidents_all_b
   where incident_number = '1000004940';

  --Service Request#1000004940 already has some tasks, the task which we are going to create will
  --have the same owner as the previous tasks
  select distinct owner_id
    into v_resource_id
    from jtf_tasks_vl
   where source_object_id = v_incident_id;

  --required for fnd_global.apps_initialize()
  select user_id
    into v_user_id
    from apps.fnd_user
   where upper(user_name) in ('TEST_USER');

  --required for fnd_global.apps_initialize()
  select fa.application_id, frt.responsibility_id
    into v_appl_id, v_resp_id
    from apps.fnd_user_resp_groups  furg,
         apps.fnd_application       fa,
         apps.fnd_responsibility_tl frt
   where fa.application_short_name = 'CSS'
     and upper(frt.responsibility_name) =
         upper('Test Responsibility')
     and fa.application_id = frt.application_id
     and furg.responsibility_application_id = fa.application_id
     and furg.responsibility_id = frt.responsibility_id
     and furg.user_id = v_user_id
     and rownum = 1;

  fnd_global.apps_initialize(user_id      => v_user_id,
                                  resp_id      => v_resp_id,
                                  resp_appl_id => v_appl_id);

  --call the API
  jtf_tasks_pub.create_task(p_api_version             => 1.0,
                            p_init_msg_list           => fnd_api.g_true,
                            p_commit                  => fnd_api.g_false,
                            p_task_name               => 'My Task',
                            --this is the task_type_id for 'My Task' obtained from jtf_task_types_vl
                            p_task_type_id            => 11269,
                            --task_staus_id obtained from jtf_task_statuses_vl
                            p_task_status_id          => 10,
                            --task_priority_id obtained from jtf_task_priorities_vl
                            p_task_priority_id        => 3,
                            p_owner_type_code         => 'RS_EMPLOYEE',
                            p_owner_id                => v_resource_id,
                            p_show_on_calendar        => 'Y',
                            p_planned_start_date      => sysdate,
                            p_planned_end_date        => sysdate,
                            p_source_object_type_code => 'SR',
                            p_source_object_id        => v_incident_id,
                            p_source_object_name      => '1000004940',
                            p_date_selected           => 'P',
                            x_return_status           => l_return_status,
                            x_msg_count               => l_msg_count,
                            x_msg_data                => l_msg_data,
                            x_task_id                 => l_task_id);

  if l_return_status <> fnd_api.g_ret_sts_success then
    dbms_output.put_line('Return Status aa= ' || l_return_status);
    dbms_output.put_line('Return Status dd= ' || fnd_api.g_ret_sts_success);
    dbms_output.put_line('l_msg_count aa= ' || l_msg_count);
    if l_msg_count > 0 then
      l_msg_data := null;
      for i in 1 .. l_msg_count loop
        l_msg_data := l_msg_data || ' ' || fnd_msg_pub.get(1, 'F');
        dbms_output.put_line('l_msg_data aa= ' || l_msg_data);
      end loop;
      fnd_message.set_encoded(l_msg_data);
      dbms_output.put_line(l_msg_data);
    end if;
    rollback;
  else
    dbms_output.put_line('Task Id = ' || l_task_id);
    dbms_output.put_line('Return Status = ' || l_return_status);
    commit;
  end if;
end;

On executing the procedure in my system, I get the following output.

SQL> set serveroutput on;
SQL>
SQL> begin
  2  xxx_create_task;
  3  end;
  4  /

Task Id = 157917
Return Status = S

PL/SQL procedure successfully completed

SQL>