1.    PART 3 - Download/Upload Integrator    

1.1    Business requirement    3

1.2    Create View to download the data in your integrator    3

1.3    Create the wrapper package to update the checklist task status    4

1.4    Define the Custom Integrator    5

1.5    Define Integrator Mapping    7

1.6    Define the Layout for the integrator    9

1.7    Defining the Form Function for Integrator    11

1.8    Assign Function to Custom Menu    11

1. PART 3 - Download/Upload Integrator

1. 1. Business requirement

The business is using Checklist feature to track the tasks for on-boarding, confirmation, transfer and Off-boarding.  There are multiple stake-holders who play different roles in completion of the entire process and HR centrally uses the checklist option to track the status of each of the tasks.
Now let’s take an example of On-boarding and if there are 10 new joining then as per the standard feature the system will send notification and mails to each task owner for each of these new joinees. If as an HR person you are responsible for doing 3-4 on-boarding tasks, then the system would have sent you 30-40 notification for action.
Pain Area – Currently under Notification we cannot filter the Open notification based on Employee or any particular date. Also, asking the user to go and click 30-40 notification one by one and saying completed was a time consuming task. So the user asked us to build a mechanism using which once they are done with the respective tasks, they should have an option to do mass completion of the task
Based on this requirement we proposed an ADI which will allow user to automatically download all the tasks which are still pending and user can mark the tasks which he/she has completed and upload the status into system. This helped the users to mark the completion of the task easily resulting in having the tracker updated
Prerequisites
 

2. 2. Create View to download the data in your integrator

I have attached a sample view which will download all the tasks for which needs users action
CREATE OR REPLACE FORCE VIEW "APPS"."XX_INCOMPLETE_TASKS" ("EMPLOYEE_NUMBER", "FULL_NAME", "EMAIL_ADDRESS", "TASK_NAME", "TARGET_START_DATE", "TARGET_END_DATE", "STATUS", "ALLOCATED_TASK_ID") AS
 select papf.employee_number
  , papf.full_name
  , papf.email_address
  , pat.task_name
  , pat.target_start_date
  , pat.target_end_date
  , pat.status
  ,pat.allocated_task_id
from per_allocated_checklists pac
  , per_allocated_tasks pat
  , per_all_people_f papf
where 1 = 1
and pac.allocated_checklist_id = pat.allocated_checklist_id
and pac.person_id = papf.person_id
and pat.status = 'INI'
and pat.performer_orig_sys_id = xx_hr_custom_pkg.get_user_person_id(fnd_global.user_id)
and trunc(sysdate) BETWEEN papf.effective_start_date and papf.effective_end_date
order by papf.employee_number;

3. 3. Create the wrapper package to update the checklist task status

I have attached a sample code for procedure which updates the status of checklist task
PROCEDURE update_checklist_task_status(
        p_status_pkg                     VARCHAR2
       ,p_allocated_task_id_pkg          NUMBER
       ,p_instruct_2        VARCHAR2
       ,p_instruct_3        VARCHAR2
       ,p_instruct_4        VARCHAR2
       ,p_instruct_5        VARCHAR2
       ,p_instruct_6        VARCHAR2
       ,p_instruct_7        VARCHAR2 DEFAULT NULL
       ,p_instruct_8        VARCHAR2 DEFAULT NULL
       ,p_instruct_9        VARCHAR2 DEFAULT NULL
       ,p_instruct_10       VARCHAR2 DEFAULT NULL
       ,p_instruct_11       VARCHAR2 DEFAULT NULL
       ,p_instruct_12       VARCHAR2 DEFAULT NULL
       ,p_instruct_13       VARCHAR2 DEFAULT NULL
       ,p_instruct_14       VARCHAR2 DEFAULT NULL
       ,p_instruct_15       VARCHAR2 DEFAULT NULL
       ,p_instruct_16       VARCHAR2 DEFAULT NULL
       ,p_instruct_17       VARCHAR2 DEFAULT NULL
       ,p_instruct_18       VARCHAR2 DEFAULT NULL
       ,p_instruct_19       VARCHAR2 DEFAULT NULL
       ,p_instruct_20       VARCHAR2 DEFAULT NULL
      )
AS
  cursor c_get_task_details (c_allocated_task_id    NUMBER)   
  IS
    SELECT * FROM per_allocated_tasks
    where allocated_task_id = c_allocated_task_id;
    
  rec_task_details    c_get_task_details%ROWTYPE;
  l_object_version_number    NUMBER;

begin

OPEN c_get_task_details(p_allocated_task_id_pkg);
FETCH c_get_task_details INTO rec_task_details;
l_object_version_number :=   rec_task_details.object_version_number;

CLOSE c_get_task_details;

PER_ALLOCATED_TASK_API.UPDATE_ALLOC_TASK
 (p_effective_date                => SYSDATE
 ,p_allocated_task_id             => p_allocated_task_id_pkg
 ,p_allocated_checklist_id        => rec_task_details.allocated_checklist_id
 ,p_task_name               =>           rec_task_details.task_name
 ,p_performer_orig_sys_id         => XX_hr_custom_pkg.get_user_person_id(fnd_global.user_id)
 ,p_target_start_date             => rec_task_details.target_start_date
 ,p_target_end_date               => rec_task_details.target_end_date
 ,p_status                        => p_status_pkg
 ,p_task_sequence                 => rec_task_details.task_sequence    
 ,p_actual_start_date             => rec_task_details.actual_start_date
 ,p_actual_end_date               => rec_task_details.actual_end_date
 ,p_action_url                    => rec_task_details.action_url
 ,p_mandatory_flag                => rec_task_details.mandatory_flag
 ,p_object_version_number         => l_object_version_number
 );

end update_checklist_task_status;   

4. 4. Define the Custom Integrator

Navigation -> Desktop Integrator -> Create Document -> Integrator -> HR Integrator Setup
HR Integrator setup is a pre-defined integrator which is provided by Oracle to create new custom integrators

Click on Next.

Select Excel 2003 from the drop down.

Click on Next.
Content as None

Click on Next

Click on Create Document
Enter below Details and click on Oracle -> Upload available under toolbar

The system will show a Confirmation stating the upload of definition is successful

5. 5. Define Integrator Mapping

This is very important step for a download/upload integrator. This is the step in which we tell the system that which of the integrator columns get mapped to the wrapper package input parameter.
Navigation -> Desktop Integrator -> Define Mappings-> Select your custom integrator name

The system by default will have already created a mapping for you. We have an option of deleting and creating a new one. But I have always use the same, so click on update to check the mapping and make the changes as per you requirement

Click on next to open the mapping page
The mapping page has two columns; Source Columns are the columns (fields) which are present in your view definition. Target Column will have the sum of you view fields as well as the input parameter of your wrapper package. You can validate this by clicking on the LOV option present in each of the section.
Now if you refer the procedure, I need the ALLOCATED TASK ID and STATUS as input parameters for my procedure as using this two parameters I am performing the required transaction. So I will map these two parameters of the Source Column to the Package parameter in Target Column.
Once you are done with the mapping, Click on apply to save

6. 6. Define the Layout for the integrator

Once you are done with the mapping, define you layout of the integrator
Navigation -> Desktop Integrator -> Define Layout -> Select your custom integrator name

Click on Create and give a Layout name with some naming convention, which may be used later for reference

The layout is divided into two important sections-
1. Required fields, where all mandatory parameters need to be aligned as per the requirement.
Instruct parameters can be used to provide some tips to users who are using this WEBADI for uploading data
2. Optional fields are non mandatory fields, we need to select the ones which needs to be available in our custom integrator


The instruct parameters added in the wrapper package will be available during the Layout definition and we can add user friendly tips which will be available for user reference

Click Next – To define certain important layout features – This functionality is available only to R12 users

• Data Entry Rows – Using this property we can define the default number of rows which the ADI will create when initialized, in 11i we user had to insert the new row in case of access data
• Read Only – Using this feature we can define that which of the columns in our integrator will be available as read only
• Width – Using this option we can define the width of each of the columns which will be available under the custom ADI
• Move Up/Down – we can re-arrange the order of display of column in our integrator

Once you are done click on Apply to save the Layout definition, system will give a confirmation message.

7. 7. Defining the Form Function for Integrator

Create a form function; please refer the earlier section which explains in detail how to create a form function for your custom Integrator

8. 8. Assign Function to Custom Menu

Go and add the function into the custom responsibility
Navigation-> Application Developer -> Application -> Menu -> Search for your menu where you want to add this ADI

Add, Prompt and Form function created and save. To check the availability of the function, navigate to the responsibility where its been added
Click on the function and press continue to open your integrator and your custom integrator is available to user for uploading data

You can see that all other columns apart from status column are available as read-only so that users cant change any data for those columns. Now user can update the status of tasks which he/she has completed and upload the data into Orcale.

Add comment