In this example, we create a simple task manager app that allows users to see a list of their tasks, add new tasks, and mark tasks as complete.
Pre-requisites
Before completing this activity, you will need to have a few basics in place:
- You'll need access to the APPS schema to create a custom table to store tasks
- You'll need a basic grasp of SQL and PL/SQL
- You'll need to be able to use an IDE such as Oracle SQL Developer
- You'll need an Oracle Applications User with Applaud Administrator access
- Your user will need to be attached to a person
Create the schema
- Let's get the basic schema in place by connecting to the APPS schema and creating a table and a sequence:
CREATE TABLE XXX_TODO_ITEMS (todo_id number NOT NULL PRIMARY KEY ,person_id number NOT NULL ,task varchar2(4000 char) NOT NULL ,status_code varchar2(30) NOT NULL ,private_flag varchar2(30) NOT NULL ,due_date date ,priority_code varchar2(30) ,created_by number NOT NULL ,creation_date date NOT NULL ,last_update_date date NOT NULL ,last_updated_by number NOT NULL ,last_update_login number NOT NULL); /
CREATE SEQUENCE XXX_TODO_ITEMS_S START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE NOCYCLE NOORDER; /It's against Oracle Applications standards to create tables and sequences in the APPS schema. It would be best to create this in a custom schema with synonyms for each. Here we're just creating them in the APPS schema for demonstration purposes.
Create a table handler (PL/SQL package)
- The table handler includes the main to-do actions:
- Create a to-do (create_todo)
- Update a to-do (update_todo)
- Delete a to-do (delete_todo)
- Mark a to-do as completed (complete_todo)
- The package specification:
CREATE OR REPLACE PACKAGE xxx_todo_pkg AS -- ------------------------------------------------------------------------ * -- Name : create_todo -- ------------------------------------------------------------------------ * PROCEDURE create_todo (p_person_id IN number ,p_task IN varchar2 ,p_status_code IN varchar2 DEFAULT 'O' ,p_private_flag IN varchar2 DEFAULT 'N' ,p_due_date IN date DEFAULT null ,p_priority_code IN varchar2 DEFAULT null ,p_todo_id OUT NOCOPY number); -- ------------------------------------------------------------------------ * -- Name : update_todo -- ------------------------------------------------------------------------ * PROCEDURE update_todo (p_todo_id IN number ,p_task IN varchar2 ,p_status_code IN varchar2 ,p_private_flag IN varchar2 ,p_due_date IN date ,p_priority_code IN varchar2); -- ------------------------------------------------------------------------ * -- Name : delete_todo -- ------------------------------------------------------------------------ * PROCEDURE delete_todo (p_todo_id IN number); -- ------------------------------------------------------------------------ * -- Name : complete_todo -- ------------------------------------------------------------------------ * PROCEDURE complete_todo (p_todo_id IN number); -- ------------------------------------------------------------------------ * -- Name : ui_form_handler -- ------------------------------------------------------------------------ * PROCEDURE ui_form_handler (p_function_id IN NUMBER ,p_form_data_xml IN XMLType ,p_user_submitted IN VARCHAR2 DEFAULT 'N' ,p_response_xml OUT XMLType); END xxx_todo_pkg; /And the package body:
CREATE OR REPLACE PACKAGE BODY xxx_todo_pkg AS -- ------------------------------------------------------------------------ * -- Name : create_todo -- ------------------------------------------------------------------------ * PROCEDURE create_todo (p_person_id IN number ,p_task IN varchar2 ,p_status_code IN varchar2 DEFAULT 'O' ,p_private_flag IN varchar2 DEFAULT 'N' ,p_due_date IN date DEFAULT null ,p_priority_code IN varchar2 DEFAULT null ,p_todo_id OUT NOCOPY number) IS BEGIN SELECT xxx_todo_items_s.nextval INTO p_todo_id FROM dual; INSERT INTO xxx_todo_items (todo_id ,person_id ,task ,status_code ,private_flag ,due_date ,priority_code ,created_by ,creation_date ,last_update_date ,last_updated_by ,last_update_login) VALUES (p_todo_id ,p_person_id ,p_task ,p_status_code ,p_private_flag ,p_due_date ,p_priority_code ,fnd_global.user_id ,sysdate ,sysdate ,fnd_global.user_id ,fnd_global.login_id); END create_todo; -- ------------------------------------------------------------------------ * -- Name : update_todo -- ------------------------------------------------------------------------ * PROCEDURE update_todo (p_todo_id IN number ,p_task IN varchar2 ,p_status_code IN varchar2 ,p_private_flag IN varchar2 ,p_due_date IN date ,p_priority_code IN varchar2) IS BEGIN UPDATE xxx_todo_items SET task = p_task ,status_code = p_status_code ,private_flag = p_private_flag ,due_date = p_due_date ,priority_code = p_priority_code ,last_update_date = sysdate ,last_updated_by = fnd_global.user_id ,last_update_login = fnd_global.login_id WHERE todo_id = p_todo_id; END update_todo; -- ------------------------------------------------------------------------ * -- Name : delete_todo -- ------------------------------------------------------------------------ * PROCEDURE delete_todo (p_todo_id IN number) IS BEGIN DELETE FROM xxx_todo_items WHERE todo_id = p_todo_id; END delete_todo; -- ------------------------------------------------------------------------ * -- Name : complete_todo -- ------------------------------------------------------------------------ * PROCEDURE complete_todo (p_todo_id IN number) IS BEGIN UPDATE xxx_todo_items SET status_code = 'C' ,last_update_date = sysdate ,last_updated_by = fnd_global.user_id ,last_update_login = fnd_global.login_id WHERE todo_id = p_todo_id; END complete_todo; -- ------------------------------------------------------------------------ * -- Name : ui_form_handler -- ------------------------------------------------------------------------ * PROCEDURE ui_form_handler (p_function_id IN NUMBER ,p_form_data_xml IN XMLType ,p_user_submitted IN VARCHAR2 DEFAULT 'N' ,p_response_xml OUT XMLType) IS l_data xxas_emp_view_ui_pkg.xxas_data_table; l_todo_id number; BEGIN -- -- Converts the submitted form data into an easily accessible set -- of name-value pairs -- l_data := xxas_emp_view_ui_pkg.transform_jsonxml_form_data(p_form_data_xml); -- -- Create or update -- l_todo_id := l_data('TODO_ID'); IF l_todo_id IS NULL THEN create_todo (p_person_id => l_data('PERSON_ID') ,p_task => l_data('TASK') ,p_status_code => l_data('STATUS_CODE') ,p_private_flag => l_data('PRIVATE_FLAG') ,p_due_date => to_date(l_data('DUE_DATE'), 'YYYY-MM-DD') ,p_priority_code => l_data('PRIORITY_CODE') ,p_todo_id => l_todo_id); ELSE update_todo (p_todo_id => l_todo_id ,p_task => l_data('TASK') ,p_status_code => l_data('STATUS_CODE') ,p_private_flag => l_data('PRIVATE_FLAG') ,p_due_date => to_date(l_data('DUE_DATE'), 'YYYY-MM-DD') ,p_priority_code => l_data('PRIORITY_CODE')); END IF; -- -- Close the modal and reload the the base page -- p_response_xml := xxas_com_rest_util_pkg.app_route_reload_response; END ui_form_handler; END xxx_todo_pkg; /Note the ui_form_handler procedure. There will be more on this later.
Create a couple of Application Lookups
- These are used for the status (XXX_TODO_STATUSES) and priority (XXX_TODO_PRIORITIES) of a to-do item.
DECLARE l_rowid varchar2(400); BEGIN -- -- XXX_TODO_STATUSES lookup with 2 values -- fnd_lookup_types_pkg.insert_row (x_rowid => l_rowid ,x_lookup_type => 'XXX_TODO_STATUSES' ,x_security_group_id => 0 ,x_view_application_id => 3 ,x_application_id => xxas_util_pkg.app_id ,x_customization_level => 'S' ,x_meaning => 'XXX: To-do statuses' ,x_description => 'XXX: To-do statuses' ,x_creation_date => sysdate ,x_created_by => fnd_global.user_id ,x_last_update_date => sysdate ,x_last_updated_by => fnd_global.user_id ,x_last_update_login => fnd_global.login_id); l_rowid := null; fnd_lookup_values_pkg.insert_row (x_rowid => l_rowid ,x_lookup_type => 'XXX_TODO_STATUSES' ,x_security_group_id => 0 ,x_view_application_id => 3 ,x_lookup_code => 'O' ,x_tag => null ,x_attribute_category => null ,x_attribute1 => null ,x_attribute2 => null ,x_attribute3 => null ,x_attribute4 => null ,x_enabled_flag => 'Y' ,x_start_date_active => to_date('01/01/1900','DD/MM/YYYY') ,x_end_date_active => null ,x_territory_code => null ,x_attribute5 => null ,x_attribute6 => null ,x_attribute7 => null ,x_attribute8 => null ,x_attribute9 => null ,x_attribute10 => null ,x_attribute11 => null ,x_attribute12 => null ,x_attribute13 => null ,x_attribute14 => null ,x_attribute15 => null ,x_meaning => 'Open' ,x_description => null ,x_creation_date => sysdate ,x_created_by => fnd_global.user_id ,x_last_update_date => sysdate ,x_last_updated_by => fnd_global.user_id ,x_last_update_login => fnd_global.login_id); l_rowid := null; fnd_lookup_values_pkg.insert_row (x_rowid => l_rowid ,x_lookup_type => 'XXX_TODO_STATUSES' ,x_security_group_id => 0 ,x_view_application_id => 3 ,x_lookup_code => 'C' ,x_tag => null ,x_attribute_category => null ,x_attribute1 => null ,x_attribute2 => null ,x_attribute3 => null ,x_attribute4 => null ,x_enabled_flag => 'Y' ,x_start_date_active => to_date('01/01/1900','DD/MM/YYYY') ,x_end_date_active => null ,x_territory_code => null ,x_attribute5 => null ,x_attribute6 => null ,x_attribute7 => null ,x_attribute8 => null ,x_attribute9 => null ,x_attribute10 => null ,x_attribute11 => null ,x_attribute12 => null ,x_attribute13 => null ,x_attribute14 => null ,x_attribute15 => null ,x_meaning => 'Completed' ,x_description => null ,x_creation_date => sysdate ,x_created_by => fnd_global.user_id ,x_last_update_date => sysdate ,x_last_updated_by => fnd_global.user_id ,x_last_update_login => fnd_global.login_id); -- -- XXX_TODO_PRIORITIES lookup with 3 values -- fnd_lookup_types_pkg.insert_row (x_rowid => l_rowid ,x_lookup_type => 'XXX_TODO_PRIORITIES' ,x_security_group_id => 0 ,x_view_application_id => 3 ,x_application_id => xxas_util_pkg.app_id ,x_customization_level => 'S' ,x_meaning => 'XXX: To-do priorities' ,x_description => 'XXX: To-do priorities' ,x_creation_date => sysdate ,x_created_by => fnd_global.user_id ,x_last_update_date => sysdate ,x_last_updated_by => fnd_global.user_id ,x_last_update_login => fnd_global.login_id); l_rowid := null; fnd_lookup_values_pkg.insert_row (x_rowid => l_rowid ,x_lookup_type => 'XXX_TODO_PRIORITIES' ,x_security_group_id => 0 ,x_view_application_id => 3 ,x_lookup_code => '1' ,x_tag => null ,x_attribute_category => null ,x_attribute1 => null ,x_attribute2 => null ,x_attribute3 => null ,x_attribute4 => null ,x_enabled_flag => 'Y' ,x_start_date_active => to_date('01/01/1900','DD/MM/YYYY') ,x_end_date_active => null ,x_territory_code => null ,x_attribute5 => null ,x_attribute6 => null ,x_attribute7 => null ,x_attribute8 => null ,x_attribute9 => null ,x_attribute10 => null ,x_attribute11 => null ,x_attribute12 => null ,x_attribute13 => null ,x_attribute14 => null ,x_attribute15 => null ,x_meaning => '!' ,x_description => null ,x_creation_date => sysdate ,x_created_by => fnd_global.user_id ,x_last_update_date => sysdate ,x_last_updated_by => fnd_global.user_id ,x_last_update_login => fnd_global.login_id); l_rowid := null; fnd_lookup_values_pkg.insert_row (x_rowid => l_rowid ,x_lookup_type => 'XXX_TODO_PRIORITIES' ,x_security_group_id => 0 ,x_view_application_id => 3 ,x_lookup_code => '2' ,x_tag => null ,x_attribute_category => null ,x_attribute1 => null ,x_attribute2 => null ,x_attribute3 => null ,x_attribute4 => null ,x_enabled_flag => 'Y' ,x_start_date_active => to_date('01/01/1900','DD/MM/YYYY') ,x_end_date_active => null ,x_territory_code => null ,x_attribute5 => null ,x_attribute6 => null ,x_attribute7 => null ,x_attribute8 => null ,x_attribute9 => null ,x_attribute10 => null ,x_attribute11 => null ,x_attribute12 => null ,x_attribute13 => null ,x_attribute14 => null ,x_attribute15 => null ,x_meaning => '!!' ,x_description => null ,x_creation_date => sysdate ,x_created_by => fnd_global.user_id ,x_last_update_date => sysdate ,x_last_updated_by => fnd_global.user_id ,x_last_update_login => fnd_global.login_id); l_rowid := null; fnd_lookup_values_pkg.insert_row (x_rowid => l_rowid ,x_lookup_type => 'XXX_TODO_PRIORITIES' ,x_security_group_id => 0 ,x_view_application_id => 3 ,x_lookup_code => '3' ,x_tag => null ,x_attribute_category => null ,x_attribute1 => null ,x_attribute2 => null ,x_attribute3 => null ,x_attribute4 => null ,x_enabled_flag => 'Y' ,x_start_date_active => to_date('01/01/1900','DD/MM/YYYY') ,x_end_date_active => null ,x_territory_code => null ,x_attribute5 => null ,x_attribute6 => null ,x_attribute7 => null ,x_attribute8 => null ,x_attribute9 => null ,x_attribute10 => null ,x_attribute11 => null ,x_attribute12 => null ,x_attribute13 => null ,x_attribute14 => null ,x_attribute15 => null ,x_meaning => '!!!' ,x_description => null ,x_creation_date => sysdate ,x_created_by => fnd_global.user_id ,x_last_update_date => sysdate ,x_last_updated_by => fnd_global.user_id ,x_last_update_login => fnd_global.login_id); COMMIT; END; /
Create a Data Object as a view to the custom table
This allows the object to be used as the source for forms, lists, and other types of blocks.
- Navigate to Applaud Administrator: Setup: Data objects, hit Add, and specify the following properties:
Property Value Type New object Name XXX_TODO_ITEMS_V Application Applaud Solutions Database object name XXX_TODO_ITEMS_V
This will create a new Database View - see the below note.View SQL <see below> Primary key column 1 TODO_ID Use this SQL:
SELECT t.todo_id ,t.person_id ,t.task ,t.status_code ,t.private_flag ,t.due_date ,t.priority_code ,t.created_by ,t.creation_date ,t.last_update_date ,t.last_updated_by ,t.last_update_login FROM xxx_todo_items t WHERE t.status_code <> 'C'This creates a new Database View in the APPS schema and automatically registers this view and all its columns in fnd_views and fnd_view_columns.
Create REST APIs
Creating REST APIs is often not required, especially for simple lists of data that can be populated from a database view. You can often use one of the existing utility REST APIs instead, such as function-data (see REST APIs). For completeness here though, we'll create a set of REST APIs.
Create the REST service
- Navigate to Applaud Administrator: Setup: REST APIs and hit Add
- Set the Service name to ToDoItems, the Path to custom/todos and Save
Create a GET method
This method will be used to return a list of open to-do items for a given person.
- Navigate to the Methods section and hit Add to create the first method. Set the following properties:
Property Value Method name GetToDos Method GET Path custom/todos/by-person/{personId} SQL <see below> Use this for the GET SQL:
SELECT XMLElement ("data" ,XMLAttributes ('true' as "json:force-array" ,'http://json.org/' as "xmlns:json") ,XMLAgg (XMLElement ("item" ,XMLForest ('flexible' "type" ,t.task "line1" ,'to-do-view' "pageId" ,t.todo_id "objectId" ,'modal' "target" ) ) ) ) "data" FROM xxx_todo_items_v t WHERE t.person_id = {pathParam.personId} AND t.person_id = fnd_global.employee_idA few notes about this REST API:
- A REST API path must be unique across all REST APIs defined in your system. You cannot defined two REST API methods that have conflicting paths; otherwise how would the REST framework know which one to use? Here it uses a by-person path qualifier so that later methods can be created to retrieve values by other types of Ids. For example, by-id (below), by-organization-id, by-days-overdue, etc.
- The {pathParam.personId} variable will use the value of the {personId} specified when calling the REST API
- The use of fnd_global.employee is important to secure the REST API. Without this, any logged-in user could see anyone else's to-do items. All REST APIs must implement some kind of security.
- The XMLAttributes hint is important to ensure the results are treated as a JSON array when only one item is returned; do not remove this
Create a DELETE method
This method will be used for two purposes: to flag a to-do item as being completed and to delete an item entirely.
- Navigate to the Methods section and hit Add to create the method. Set the following properties:
Property Value Method name CompleteDeleteToDos Method DELETE Path custom/todos/by-id/{todoId} Provider PLSQL PLSQL <see below> Use this for the PL/SQL:
DECLARE l_response xxas_http_resp_obj; l_person_id number; CURSOR csr_access_chk IS SELECT t.person_id FROM xxx_todo_items t WHERE t.todo_id = {pathParam.todoId} AND t.person_id = fnd_global.employee_id; BEGIN OPEN csr_access_chk; FETCH csr_access_chk INTO l_person_id; CLOSE csr_access_chk; IF l_person_id IS NULL THEN l_response := xxas_http_response .status(xxas_http_resp_status_pkg.unauthorised) .build(); ELSE IF {requestParam.purge} = 'true' THEN xxx_todo_pkg.delete_todo({pathParam.todoId}); ELSE xxx_todo_pkg.complete_todo({pathParam.todoId}); END IF; l_response := xxas_http_response .ok(xxas_com_rest_util_pkg.app_route_redirect_response ('to-dos/' || to_char(l_person_id))) .build(); END IF; {response} := l_response; END; /A few notes about this REST API:
- It uses a by-id path qualifier so that this doesn't conflict with the existing by-person qualifier. Here it's clear that the next path parameter is the Id of the Task, not the Id of a person (in the previous REST API it used by-person).
- The to-do's id is passed in as a path parameter
- It checks if the task belongs to the logged-on person to secure the REST API. It returns a 401 unauthorized response if the user doesn't have access.
- It uses a request parameter called purge=true to determine whether to delete or simply complete the to-do item
Define a form to add/view/edit a to-do item
- Navigate to Applaud Administrator: Setup: Forms and hit Add
- Set the Name to To-do item, the Id to todo-item, and the Form handler to xxx_todo_pkg.ui_form_handler. Save.
- Create the form fields as below:
Type Id Other properties Hidden TODO_ID Hidden PERSON_ID TextInput TASK Label: Task
Required: Checked
Placeholder: Enter task name
Max length: 4000Checkbox PRIVATE_FLAG Label: Private
List provider type: Lookup
Lookup Type: YES_NO
Positive boolean value: YesCheckbox STATUS_CODE Label: Completed
List provider type: Lookup
Lookup Type: XXX_TODO_STATUSES
Positive boolean value: CompletedDate DUE_DATE Label: Due date Select-Single PRIORITY_CODE Label: Priority
List provider type: Lookup
Lookup Type: XXX_TODO_PRIORITIES
Create a to-dos page
This page provides a user with a list of their existing to-do items.
- Navigate to Applaud Administrator: Setup: Pages and hit Add
- Enter these properties and save:
Property Value Notes Title To-dos Icon check_box Icon color Red Page Id to-dos Object Id format {PERSON_ID} Object Id handler xxas_per_pkg.per_and_primary_asg_ctx Sub-menu Person sub-nav (custom) The name of your person sub-nav will probably vary from this one here. Pick the one that has most of your existing menu items on it. Add this page into the submenu Checked Location Root (top-level menu item) - Create a list block of to-do items:
- Navigate down to the Blocks section and create a new block with the following properties:
Property Value Notes Title To-do Type List Security Type Role and data If you don't see this field, double-check that the page has its Object Id Format set.
If you still don't see this field, try temporarily putting in any Object Id Handler function on the page (copy from any other page). If you do this, you must remove the temporary Object Id Handler once you've saved this block.Object Applaud HR Person REST Data source custom/todos/by-person/{INSTANCE_PK1_VALUE} { INSTANCE_PK1_VALUE } is PERSON_ID on the Applaud HR Person object Layouts Row No items text Woohoo, no tasks. - Create a grant to see the list block:
- Navigate to the Grants section and hit Add
- Choose your employee role and use the Instance Set Applaud HR Person Restricted to Logged on Employee
- Test access to make sure your user has access to this block. Remember to enter the Person Id when testing accessing.
- Navigate down to the Blocks section and create a new block with the following properties:
Create a to-do-add page
This page allows a user to add a new to-do item.
- Navigate to Applaud Administrator: Setup: Pages and hit Add
- Enter these properties and save:
Property Value Notes Title Add to-do Icon check_box Icon color Red Page Id to-do-add Object Id format {PERSON_ID} Object Id handler xxas_per_pkg.per_and_primary_asg_ctx Sub-menu <leave blank> You do not want the add-page to be in the menu Anchor title bar Checked This will ensure the Save button is always anchored, even when scrolling down - Add a form block for adding a to-do item:
- This block houses the form that allows a new to-do item to be added.
- Navigate down to the Blocks section and create a new block with the following properties:
Property Value Notes Title Add to-do Type Form Security Type Role and data If you don't see this field, double-check that the page has its Object Id Format set.
If you still don't see this field, try temporarily putting in any Object Id Handler function on the page (copy from any other page). If you do this, you must remove the temporary Object Id Handler once you've saved this block.Object Applaud HR Person Form To-do item (/public/xxas/custom/emp/view/todo-item.xml) Form handler xxx_todo_pkg.ui_form_handler - Create a grant to see the block:
- Navigate to the Grants section and hit Add
- Choose your employee role and use the Instance Set Applaud HR Person Restricted to Logged on Employee
- Test access to make sure your user has access to this block. Remember to enter the Person Id when testing accessing.
- Create an add block action:
- Navigate to Applaud Administrator: Setup: Pages: <to-dos page>: <to-dos list block>, navigate down to Block-level actions, hit Add, and specify the following properties:
Property Value Notes Title Add Operation LINK Type Applaud HCM Page Page to-do-add Object Id {INSTANCE_PK1_VALUE} Show in modal Checked - Create a grant to see the block action:
- Navigate to the Grants section within the block action (not the grants for the block itself) and hit Add
- Choose your employee role
- Choose the objectIds Applaud HR Person Restricted to Logged On Employee
- Navigate to Applaud Administrator: Setup: Pages: <to-dos page>: <to-dos list block>, navigate down to Block-level actions, hit Add, and specify the following properties:
Create a to-do-view page
This page allows a user to view the details of an existing to-do item.
- Navigate to Applaud Administrator: Setup: Pages and hit Add
- Enter these properties and save:
Property Value Notes Title View to-do Icon check_box Icon color Red Page Id to-do-view Object Id format {TODO_ID} The block will automatically use this value to join to the correct to-do item Object Id handler Sub-menu <leave blank> You do not want the detail page to be in the menu - Add a form block for viewing a to-do item:
This block houses the form that allows an existing to-do item to be viewed. Here the read-only mode is used to provide a non-editable view.
-
Navigate down to the Blocks section and create a new block with the following properties:
Property Value Notes Title View to-do Type Form Security Type Role and data If you don't see this field, double-check that the page has its Object Id Format set.
If you still don't see this field, try temporarily putting in any Object Id Handler function on the page (copy from any other page). If you do this, you must remove the temporary Object Id Handler once you've saved this block.Object XXX_TODO_ITEMS_V Form To-do item (/public/xxas/custom/emp/view/todo-item.xml) Read-only Checked - Create a grant to see the block:
- Navigate to the Grants section and hit Add
- Choose your employee role
- Choose the objectIds Create a new one
- Specify the Title as Logged on person and the Predicate as &TABLE_ALIAS.person_id = fnd_global.employee_id
-
This ensures that a user can only view/edit their own to-do items.
Create a to-do-edit page
This page allows a user to edit the details of an existing to-do item.
- Navigate to Applaud Administrator: Setup: Pages and hit Add
- Enter these properties and save:
Property Value Notes Title Edit to-do Icon check_box Icon color Red Page Id to-do-edit Object Id format {TODO_ID} Object Id handler Sub-menu <leave blank> You do not want the detail page to be in the menu Anchor title bar Checked - Add a form block for editing a to-do item:
This block contains the form that allows an existing to-do item to be edited. It's pretty much the same as the view one, except Read-only isn't checked and it specifies the form handler.
- Navigate down to the Blocks section and create a new block with the following properties:
Property Value Title Edit to-do Type Form Security Type Role and data Object XXX_TODO_ITEMS_V Form To-do item (/public/xxas/custom/emp/view/todo-item.xml) Form handler xxx_todo_pkg.ui_form_handler - Create a grant to see the block:
- Navigate to the Grants section and hit Add
- Choose your employee role
- Choose the objectIds Logged on person
- Add an edit block action:
- Navigate to Applaud Administrator: Setup: Pages: <to-do-view page>: <to-do-view form block>.
- Navigate down to Block-level actions, hit Add, and specify the following properties:
Property Value Title Edit Operation LINK Type Applaud HCM Page Page to-do-edit Object Id {INSTANCE_PK1_VALUE} Show in modal Checked - Create a grant to see the block action:
- Navigate to the Grants section within the block action (not the grants for the block itself) and hit Add
- Choose your employee role.
- Choose the objectIds Logged on person.
- Add a delete block action:
- Navigate to Applaud Administrator: Setup: Pages: <to-do-view page>: <to-do-view form block>.
- Navigate down to Block-level actions, hit Add, and specify the following properties:
Property Value Title Delete Operation DELETE REST Endpoint custom/todos/by-id/{INSTANCE_PK1_VALUE}?purge=true Color assertive Require confirmation Checked Confirmation message This will permanently delete this item. - Create a grant to see the block action:
- Navigate to the Grants section within the block action (not the grants for the block itself) and hit Add
- Choose your employee role
- Choose the objectIds Logged on person
- Navigate down to the Blocks section and create a new block with the following properties:
In this example, we've created a basic to-do list, with the ability to read, create, update and delete tasks. However, we can make it even better.
Let's make a couple of changes to the GET REST API to show the priority, due date, and a couple of item actions. Update your REST API as follows (the bold bits are new):
SELECT XMLElement
("data"
,XMLAttributes
('true' as "json:force-array"
,'http://json.org/' as "xmlns:json")
,XMLAgg
(XMLElement
("item"
,XMLForest
('flexible' "type"
,'<strong><font color="'
|| xxas_rslt_cache_pkg.resolve_color_name('red')
|| '">'
|| xxas_util_pkg.decode_lookup
('XXX_TODO_PRIORITIES', t.priority_code)
|| '</font></strong> '
|| t.task "line1"
,CASE WHEN t.due_date IS NULL THEN
null
WHEN t.due_date < trunc(sysdate) THEN
'<font color="'
|| xxas_rslt_cache_pkg.resolve_color_name('orange')
|| '">'
|| 'Due on '
|| to_char(t.due_date, 'FMDd Mon YYYY')
|| '</font>'
WHEN t.due_date = trunc(sysdate) THEN
'<font color="'
|| xxas_rslt_cache_pkg.resolve_color_name('orange')
|| '">'
|| 'Due today'
|| '</font>'
WHEN t.due_date = trunc(sysdate) + 1 THEN
'<font color="'
|| xxas_rslt_cache_pkg.resolve_color_name('grey')
|| '">'
|| 'Due tomorrow'
|| '</font>'
ELSE
'<font color="'
|| xxas_rslt_cache_pkg.resolve_color_name('grey')
|| '">'
|| 'Due in '
|| to_char(t.due_date - trunc(sysdate))
|| ' days'
|| '</font>'
END "line3"
,'to-do-view' "pageId"
,t.todo_id "objectId"
,'modal' "target"
,xxas_item_actions_builder()
.link_action
(p_item_id => t.todo_id
,p_title => 'Edit'
,p_page_id => 'to-do-edit'
,p_object_id => t.todo_id
,p_target => 'modal'
,p_color => 'positive'
,p_icon => 'edit')
.delete_action
(p_item_id => t.todo_id
,p_title => 'Complete'
,p_api_path => 'custom/todos/by-id/'
|| t.todo_id
,p_color => 'balanced'
,p_icon => 'tick')
.get_xml() "linked"
)
) ORDER BY t.due_date, t.priority_code DESC
)
) "data"
FROM xxx_todo_items_v t
WHERE t.person_id = {pathParam.personId}
AND t.person_id = fnd_global.employee_idA few notes:
- xxas_rslt_cache_pkg.resolve_color_name is used to turn color names to hex values. This uses a hand-picked palette of colors that look great and work well together.
- The xxas_item_actions_builder object is used to create a couple of item actions to edit and complete to-do items. This works great on mobile and touch devices with a swipe to complete.
- The line3 property of the flexible item template is used to show a supplementary line of text below the mainline.
- An ORDER BY sorts the items. XMLAgg is used here, so the ORDER BY must go as above to sort the XML items. If it's placed after the WHERE clause in the normal place you'll find it won't work.
Add to the home page
We deliberately built a new to-dos page so you can see how to create a custom page. However, this doesn't really need its own page. It's a neat little piece of functionality that would fit nicely on an existing home page.
To add this to an existing page:
- Navigate to Applaud Administrator: Setup: Pages and find the page to which you wish to add this block, such as a home, at-a-glance or welcome page
- Check this page includes the PERSON_ID key in its Object Id Format or Object Id handler; this key is required to retrieve the list of to-do items for a given person
- Navigate to the Blocks section and hit Re-use existing
- Find the block and hit Save
Here are some suggestions you could try to make it even better:
- Make it collaborative. The access control has been configured to only show tasks belonging to the logged-in person. Wouldn't it be great if a manager could push tasks to an individual, or review their tasks in regular status calls? Remember you have that PRIVATE_FLAG, which isn't used in the above.
- Create a list that shows completed to-do items. You could, for example, create a Blocks block with two child list blocks: Open (as-built above) and Closed.
- Create a manager list that shows tasks due today and overdue tasks across their direct reports.
- Create an Item Action called Postpone, which delays overdue tasks and tasks due today until tomorrow.
- Automatically populate users' to-do lists with employee actions. For example, create a to-do list item when a performance review is open.
Remember that the table was not created using Oracle Application Development standards.
Before you go live:
- Create the table and sequence in your custom schema.
- Create synonyms for both the table and sequence in the APPS schema.
- You'll need some indexes to keep it snappy. We recommend an index on the PERSON_ID column and STATUS_CODE column (one index with both columns specified in this order).