Out of the box the Universal Work Queue has a number of nodes that display data relating to Advanced Collections and Delinquencies, however we have a requirement to categorise the Tasks based on Customer Attributes and Collector information.
The first step is to create the Lookup Types that will be used, for this example the custom Lookup Type name that has been created is "XXAPPS IEU Node Labels" (XXAPPS_IEU_NODE_LABELS”). The Lookup Code "Tasks : Customer Category" (XXAPPS_IEU_NEW_TAKS) has been created within this Lookup Type.
Lookup Type:
Name XXAPPS IEU Node Labels
Code XXAPPS_IEU_NODE_LABELS
Description Node Labels for Custom UWQ Nodes
Application XXAPPS Application
Access Extensible
Lookup Codes:
Code XXAPPS_IEU_NEW_TASKS
Description Tasks : Customer Category
The next step is to create the database views that will be used by the custom node, in this example the view is based on the seeded IEU_UWQ_TASKS_V view with additional fields added to return the Customer Attribute information. Next, create the Spreadtable Metadata Definition (within the CRM Administration responsibility), for this example the Datasource Name is XXAPPS_IEU_UWQ_TASKS and the referenced Relational View is XXAPPS_IEU_UWQ_TASKS_V.
Within the CRM Administrator the Work Provider can be created based on the configuration completed so far:
Application Name XXAPPS Application
Node Name Lookup Type XXAPPS_IEU_NODE_LABELS
Node Name Lookup Code XXAPPS_IEU_NEW_TASKS
Node Name Lookup Meaning Tasks
Enumerator UUID XXAPPS_TASK_NODE
Registration Type Non Media
Enumerator Procedure XXAPPS_iex_util_pkg.enumerate_group_uwq_nodes
Refresh Procedure XXAPPS_iex_util_pkg.refresh_group_uwq_nodes
View Name XXAPPS_IEU_UWQ_TASKS_V
The Enumerator and Refresh Procedures defined above reference PL/SQL packages that build the Nodes and populate the UWQ with the relevant data.
The XXAPPS_IEX_UTIL_PKG is defined as:
CREATE OR REPLACE PACKAGE XXAPPS_iex_util_pkg
AS
PROCEDURE enumerate_group_uwq_nodes (P_RESOURCE_ID IN NUMBER
, P_LANGUAGE IN VARCHAR2
, P_SOURCE_LANG IN VARCHAR2
, P_SEL_ENUM_ID IN NUMBER);
PROCEDURE refresh_group_uwq_nodes (P_RESOURCE_ID IN NUMBER
, p_node_id IN NUMBER
, P_COUNT OUT NOCOPY NUMBER);
END XXAPPS_iex_util_pkg;
/
CREATE OR REPLACE PACKAGE BODY XXAPPS_iex_util_pkg
AS
PROCEDURE enumerate_group_uwq_nodes (P_RESOURCE_ID IN NUMBER
, P_LANGUAGE IN VARCHAR2
, P_SOURCE_LANG IN VARCHAR2
, P_SEL_ENUM_ID IN NUMBER)
AS
l_node_label VARCHAR2 (200);
uwq_ld_list IEU_PUB.EnumeratorDataRecordList;
l_bind_list IEU_PUB.BindVariableRecordList;
l_node_counter NUMBER;
v_org_id NUMBER := iex_org_override;
BEGIN
SAVEPOINT start_node_enumeration;
l_node_counter := 0;
uwq_ld_list (l_node_counter).NODE_LABEL := 'Tasks : Customer Category';
uwq_ld_list (l_node_counter).VIEW_NAME := 'XXAPPS_IEU_UWQ_TASKS_V';
uwq_ld_list (l_node_counter).DATA_SOURCE := 'XXAPPS_IEU_UWQ_TASKS';
uwq_ld_list (l_node_counter).MEDIA_TYPE_ID := '';
uwq_ld_list (l_node_counter).WHERE_CLAUSE :=
'CUSTOMER_ORG_ID = ' || v_org_id;
uwq_ld_list (l_node_counter).NODE_TYPE := 0;
uwq_ld_list (l_node_counter).HIDE_IF_EMPTY := '';
uwq_ld_list (l_node_counter).NODE_DEPTH := 1;
DECLARE
CURSOR c1
IS
SELECT customer_type
FROM (SELECT lookup_code customer_type
FROM ar_lookups
WHERE lookup_type = 'ADDRESS_CATEGORY'
UNION
SELECT 'GENERAL' FROM DUAL)
WHERE EXISTS
(SELECT 'x'
FROM jtf_tasks_b tasks_b
, hz_cust_site_uses_all casu
, hz_cust_acct_sites_all cas
WHERE casu.cust_acct_site_id = cas.cust_acct_site_id
AND casu.site_use_id = tasks_b.source_object_id
AND TRUNC (
NVL (tasks_b.planned_start_date, SYSDATE)) <=
SYSDATE
AND NVL (tasks_b.deleted_flag, 'N') = 'N'
AND tasks_b.open_flag = 'Y'
AND tasks_b.entity = 'TASK'
AND cas.org_id = v_org_id
AND NVL (
NVL (cas.customer_category_code
, UPPER (cas.attribute_category))
, 'GENERAL') = customer_type
AND tasks_b.owner_type_code = 'RS_GROUP')
ORDER BY 1;
BEGIN
FOR x IN c1
LOOP
l_node_counter := l_node_counter + 1;
uwq_ld_list (l_node_counter).NODE_LABEL := x.customer_type;
uwq_ld_list (l_node_counter).VIEW_NAME := 'XXAPPS_IEU_UWQ_TASKS_V';
uwq_ld_list (l_node_counter).DATA_SOURCE := 'XXAPPS_IEU_UWQ_TASKS';
uwq_ld_list (l_node_counter).MEDIA_TYPE_ID := '';
uwq_ld_list (l_node_counter).WHERE_CLAUSE :=
'CUSTOMER_TYPE = '
|| ''''
|| x.customer_type
|| ''''
|| ' and CUSTOMER_ORG_ID = '
|| v_org_id;
uwq_ld_list (l_node_counter).NODE_TYPE := 0;
uwq_ld_list (l_node_counter).HIDE_IF_EMPTY := 'Y';
uwq_ld_list (l_node_counter).NODE_DEPTH := 2;
END LOOP;
END;
IEU_PUB.ADD_UWQ_NODE_DATA (P_RESOURCE_ID, P_SEL_ENUM_ID, uwq_ld_list);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO start_node_enumeration;
RAISE;
END enumerate_group_uwq_nodes;
PROCEDURE refresh_group_uwq_nodes (p_resource_id IN NUMBER
, p_node_id IN NUMBER
, p_count OUT NOCOPY NUMBER)
IS
sql_stmt VARCHAR2 (500);
l_view_name VARCHAR2 (500);
l_label VARCHAR2 (500);
l_where VARCHAR2 (500);
l_depth NUMBER;
v_count NUMBER;
v_org_id NUMBER := iex_org_override;
BEGIN
BEGIN
SELECT where_clause
, node_label
, node_depth
INTO l_where
, l_label
, l_depth
FROM IEU_UWQ_SEL_RT_NODES
WHERE node_id = p_node_id
AND resource_id = p_resource_id
AND data_source = 'XXAPPS_IEU_UWQ_TASKS';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_where := NULL;
l_label := NULL;
END;
IF l_depth = 1
THEN
SELECT COUNT ( * )
INTO p_count
FROM jtf_tasks_b tasks_b
, hz_cust_site_uses_all casu
, hz_cust_acct_sites_all cas
WHERE casu.cust_acct_site_id = cas.cust_acct_site_id
AND casu.site_use_id = tasks_b.source_object_id
AND NVL (tasks_b.deleted_flag, 'N') = 'N'
AND tasks_b.open_flag = 'Y'
AND tasks_b.entity = 'TASK'
AND cas.org_id = v_org_id
AND tasks_b.owner_type_code = 'RS_GROUP'
AND TRUNC (NVL (tasks_b.planned_start_date, SYSDATE)) <= SYSDATE;
ELSE
SELECT COUNT ( * )
INTO p_count
FROM jtf_tasks_b tasks_b
, hz_cust_site_uses_all casu
, hz_cust_acct_sites_all cas
WHERE casu.cust_acct_site_id = cas.cust_acct_site_id
AND casu.site_use_id = tasks_b.source_object_id
AND NVL (tasks_b.deleted_flag, 'N') = 'N'
AND tasks_b.open_flag = 'Y'
AND tasks_b.entity = 'TASK'
AND cas.org_id = v_org_id
AND NVL (
NVL (cas.customer_category_code
, UPPER (cas.attribute_category))
, 'GENERAL') = l_label
AND tasks_b.owner_type_code = 'RS_GROUP'
AND TRUNC (NVL (tasks_b.planned_start_date, SYSDATE)) <= SYSDATE;
END IF;
END refresh_group_uwq_nodes;
END XXAPPS_iex_util_pkg;
Now, within System Administrator enable the Profile Option “IEU: Queue: Tasks : Customer Category” (linked to the UWQ Work Provider) and the nodes will be visible within the Universal Work Queue.
No comments:
Post a Comment