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
-
PART 3 - Download/Upload Integrator
-
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
-
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;
-
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;
-
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
Filed Name | Value |
Metadata Type | UPDATE |
Application Short Name | PER |
Integrator User Name | XX – Update Checklist Task Status |
View Name | xx_incomplete_tasks |
Form Name | GENERAL |
API Package Name | xx_hr_webadi_pkg |
API Procedure Name | update_checklist_task_status |
Interface User Name | xx– Update Checklist Task Status |
Interface Parameter List Name | UPDATE_TASK_DETAILS |
API Type | PROCEDURE |
The system will show a Confirmation stating the upload of definition is successful
-
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
-
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.
-
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
-
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.
Comments
Thanks for the great article. I would like to create poplist on 2/3 cloumns in custom integrator, could you please provide the steps/java classes to achieve this? I am unable to find the doc for LOV.
Q2. For Items do you need PLM installed for WedADI to work?
I am working on creating a download/upload integrator in R12. The requirement is such that first there will be a custom search page and depending on the search criteria filled by user in it, the data will be downloaded to the excel sheet. Can you please provide some pointers on how to acheive this functionality of filtering the data and passing the filtered data to the excel sheet?
Thanks,
Saumya
where I can find xx_hr_custom_pk g.get_user_pers on_id
thanks
Is is application specific ? It works only for HR related topics, or applicable to any other custom applications.? Please advise.
Thanks
Pavan Kumar.
I'm quite certain I will learn plenty of new stuff right
here! Best of luck for the next!
RSS feed for comments to this post