[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;