Translate

Tuesday, 21 June 2011

Oracle: Universal Work Queue – Advanced Collections

The Universal Work Queue can be integrated with many other Oracle applications, in this example we will be using it to display Task information generated from Oracle Advanced Collections.

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