DBA Data[Home] [Help]

PACKAGE BODY: APPS.IEU_ESCALATIONS_PVT

Source


1 PACKAGE BODY IEU_ESCALATIONS_PVT AS
2 /* $Header: IEUEESVB.pls 120.0 2005/06/02 15:51:06 appldev noship $ */
3 
4 PROCEDURE ENUMERATE_ESC_MYOWN_NODES
5   (P_RESOURCE_ID      IN NUMBER
6   ,P_LANGUAGE         IN VARCHAR2
7   ,P_SOURCE_LANG      IN VARCHAR2
8   ,P_SEL_ENUM_ID      IN NUMBER
9   )
10   AS
11 
12   l_node_counter           NUMBER;
13   l_node_pid               NUMBER;
14   l_node_label             VARCHAR2(100);
15   l_def_where              VARCHAR2(20000);
16   l_tk_list                IEU_PUB.EnumeratorDataRecordList;
17   l_bind_list              IEU_PUB.BindVariableRecordList;
18 
19   CURSOR c_ESC_nodes_1 IS
20     SELECT /*+ index(tasks_b jtf_tasks_b_n2) */
21     distinct lkups.meaning name, tasks_b.escalation_level
22     from jtf_tasks_b tasks_b ,
23     fnd_lookup_values_vl lkups
24     WHERE tasks_b.open_flag = 'Y'
25     and nvl(tasks_b.deleted_flag,'N') = 'N'
26     and tasks_b.entity ='ESCALATION'
27     and lkups.lookup_type = 'JTF_TASK_ESC_LEVEL'
28     and lkups.lookup_code = tasks_b.escalation_level
29     and tasks_b.owner_id = p_resource_id
30     and tasks_b.owner_type_code = 'RS_EMPLOYEE'
31     ORDER BY 1;
32 
33  CURSOR c_ESC_nodes_2 IS
34     SELECT lkups.meaning name, lkups.lookup_code escalation_level
35     from fnd_lookup_values_vl lkups
36     WHERE lkups.lookup_type = 'JTF_TASK_ESC_LEVEL'
37     ORDER BY 1;
38 
39   BEGIN
40 
41   IF (FND_PROFILE.VALUE('IEU_QEN_ESC') = 'N' ) THEN
42     RETURN;
43   END IF;
44 
45   l_node_counter  := 0;
46 
47   SAVEPOINT start_enumeration;
48 
49 
50    Select meaning into l_node_label
51    from fnd_lookup_values_vl
52    where lookup_type = 'IEU_NODE_LABELS'
53    and view_application_id = 696
54    and lookup_code = 'IEU_UWQ_ESC_LBL';
55 
56   l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
57   l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_ESC_MYOWN_V';
58   l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_ESC_MYOWN_DS';
59   l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
60   l_tk_list(l_node_counter).WHERE_CLAUSE := '';
61   l_tk_list(l_node_counter).NODE_TYPE := 0;
62   l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
63   l_tk_list(l_node_counter).NODE_DEPTH := 1;
64   l_tk_list(l_node_counter).BIND_VARS := '';
65   l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'Y';
66 
67   l_node_counter := l_node_counter + 1;
68 
69 --Now build the subnodes
70   IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
71     FOR cur_rec IN c_esc_nodes_1 LOOP
72 
73       l_tk_list(l_node_counter).NODE_LABEL := cur_rec.name;
74       l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_ESC_MYOWN_V';
75       l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_ESC_MYOWN_DS';
76       l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
77       l_tk_list(l_node_counter).WHERE_CLAUSE := 'ESCALATION_LEVEL = '||''''||cur_rec.escalation_level||'''';
78       l_tk_list(l_node_counter).NODE_TYPE := 0;
79       l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
80       l_tk_list(l_node_counter).NODE_DEPTH := 2;
81       l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'Y';
82 
83       l_node_counter := l_node_counter + 1;
84 
85     END LOOP;
86 
87   else
88 
89     FOR cur_rec IN c_esc_nodes_2 LOOP
90 
91       l_tk_list(l_node_counter).NODE_LABEL := cur_rec.name;
92       l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_ESC_MYOWN_V';
93       l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_ESC_MYOWN_DS';
94       l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
95       l_tk_list(l_node_counter).WHERE_CLAUSE := 'ESCALATION_LEVEL = '||''''||cur_rec.escalation_level||'''';
96       l_tk_list(l_node_counter).NODE_TYPE := 0;
97       l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
98       l_tk_list(l_node_counter).NODE_DEPTH := 2;
99       l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'Y';
100 
101       l_node_counter := l_node_counter + 1;
102 
103     END LOOP;
104 
105   end if;
106   IEU_PUB.ADD_UWQ_NODE_DATA
107   (P_RESOURCE_ID,
108    P_SEL_ENUM_ID,
109    l_tk_list
110   );
111 
112 EXCEPTION
113   WHEN OTHERS THEN
114     ROLLBACK TO start_enumeration;
115     RAISE;
116 
117 END ENUMERATE_ESC_MYOWN_NODES;
118 
119 /* Used to refresh Escalation nodes for Forms tree view. */
120 PROCEDURE REFRESH_ESC_MYOWN_NODES( P_RESOURCE_ID IN NUMBER, P_NODE_ID IN NUMBER, P_COUNT OUT NOCOPY NUMBER)
121  AS
122   l_count NUMBER:=0;
123   l_where_clause VARCHAR(1000);
124   l_sql_count VARCHAR2(30000);
125 BEGIN
126 l_where_clause := '';
127 select where_clause
128 into   l_where_clause
129 from   ieu_uwq_sel_rt_nodes
130 where  resource_id=P_RESOURCE_ID
131 and    node_id=P_NODE_ID;
132 if (l_where_clause is NOT NULL) then
133   l_where_clause := ' and '|| l_where_clause;
134 end if;
135 
136   BEGIN
137 -- Get count 1
138 l_sql_count := 'begin
139     SELECT /*+ index(tasks_b jtf_tasks_b_n2) */ count(*) into :l_count
140     from jtf_tasks_b tasks_b ,
141     jtf_task_references_b refs_b
142     WHERE
143     nvl(tasks_b.deleted_flag ,'||''''||'N'||''''||') <> '||''''||'Y'||''''||'
144     and tasks_b.open_flag = '||''''||'Y'||''''||'
145     and tasks_b.entity =  ' ||''''|| 'ESCALATION' ||''''|| '
146     and refs_b.task_id(+) = tasks_b.task_id
147     and tasks_b.owner_type_code = '||''''||'RS_EMPLOYEE'||''''||'
148     and tasks_b.owner_id = :p_Resource_id '||l_where_clause||'; end;';
149 
150 execute immediate  l_sql_count
151 USING OUT l_count, IN p_resource_id;
152 
153   EXCEPTION
154         WHEN OTHERS THEN
155           --dbms_output.put_line(SQLCODE);
156           --dbms_output.put_line(SQLERRM);
157           l_count := 0;
158   END;
159   IF (l_count IS NULL)
160   THEN
161       l_count := 0;
162   END IF;
163   --RETURN l_count;
164   P_COUNT:=l_count;
165 END REFRESH_ESC_MYOWN_NODES;
166 
167 END IEU_ESCALATIONS_PVT;