[Home] [Help]
PACKAGE BODY: APPS.IEU_PUB
Source
1 PACKAGE BODY IEU_PUB AS
2 /* $Header: IEU_PB.pls 120.0 2005/06/02 15:49:55 appldev noship $ */
3
4 l_not_valid_flag VARCHAR2(1);
5 l_not_valid VARCHAR2(1);
6
7 /* Used to determine if agent is eligible for ANY media */
8 FUNCTION IS_AGENT_ELIGIBLE_FOR_MEDIA( P_RESOURCE_ID IN NUMBER )
9 RETURN BOOLEAN
10 AS
11
12 BEGIN
13
14 RETURN IEU_PVT.IS_AGENT_ELIGIBLE_FOR_MEDIA( P_RESOURCE_ID );
15
16 END IS_AGENT_ELIGIBLE_FOR_MEDIA;
17
18
19 /* Used to determine if a connection to the UWQ server is required */
20 FUNCTION IS_UWQ_SERVER_REQUIRED( P_RESOURCE_ID IN NUMBER )
21 RETURN BOOLEAN
22 AS
23
24 BEGIN
25
26 RETURN IEU_PVT.IS_UWQ_SERVER_REQUIRED( P_RESOURCE_ID );
27
28 END IS_UWQ_SERVER_REQUIRED;
29
30
31
32 FUNCTION SET_BIND_VAR_DATA( P_BindDataList IN BindVariableRecordList)
33 RETURN VARCHAR2 IS
34
35 bindString Varchar2(4000);
36
37 BEGIN
38
39 for i in P_BindDataList.first .. P_BindDataList.Last
40 loop
41
42 bindString := bindString ||'<'||P_BindDataList(i).bind_var_name
43 ||'|'||P_BindDataList(i).bind_var_value
44 ||'|'||P_BindDataList(i).bind_var_data_type||'>';
45
46 end loop;
47
48 return bindString;
49 END;
50
51 FUNCTION GET_ENUM_RES_CAT(P_SEL_ENUM_ID IN NUMBER)
52 RETURN VARCHAR2 IS
53
54 l_def_where VARCHAR2(20000);
55 l_default_res_cat_id NUMBER;
56 l_profile_res_cat_id NUMBER;
57
58 BEGIN
59
60 BEGIN
61 Select
62 default_res_cat_id
63 into
64 l_default_res_cat_id
65 from
66 ieu_uwq_sel_enumerators
67 where
68 sel_enum_id = p_sel_enum_id;
69 EXCEPTION
70 when no_data_found then
71 l_def_where := '';
72 END;
73
74 --dbms_output.put_line('l_Profile_res_cat_id'||l_profile_res_cat_id);
75 BEGIN
76 Select where_clause
77 into l_def_where
78 from ieu_uwq_res_cats_b
79 where res_cat_id = l_default_res_cat_id;
80 EXCEPTION
81 when NO_DATA_FOUND then null;
82 END;
83
84 RETURN l_def_where;
85
86 END get_enum_res_cat;
87
88 PROCEDURE ADD_UWQ_NODE_DATA
89 (P_RESOURCE_ID IN NUMBER,
90 P_SEL_ENUM_ID IN NUMBER,
91 P_ENUMERATOR_DATAREC_LIST IN IEU_PUB.EnumeratorDataRecordList
92 ) AS
93
94 BEGIN
95
96 IEU_PVT.ADD_UWQ_NODE_DATA(
97 p_resource_id,
98 p_sel_enum_id,
99 p_enumerator_datarec_list );
100
101 END ADD_UWQ_NODE_DATA;
102
103 PROCEDURE GET_UWQ_NODE_DETAILS
104 (P_RESOURCE_ID IN NUMBER,
105 P_NODE_ID IN NUMBER,
106 X_NODE_DETAIL_RECORD OUT NOCOPY IEU_PUB.NodeDetailRecord) IS
107
108 l_where_clause varchar2(4000);
109 l_node_type IEU_UWQ_SEL_RT_NODES.NODE_TYPE%TYPE;
110 l_res_cat_enum_flag IEU_UWQ_SEL_RT_NODES.RES_CAT_ENUM_FLAG%TYPE;
111 l_extra_where_clause IEU_UWQ_SEL_RT_NODES.WHERE_CLAUSE%TYPE;
112 l_sel_rt_node_id IEU_UWQ_SEL_RT_NODES.SEL_RT_NODE_ID%TYPE;
113 l_sel_enum_id IEU_UWQ_SEL_RT_NODES.SEL_ENUM_ID%TYPE;
114 L_RES_CAT_WHERE_CLAUSE IEU_UWQ_RES_CATS_B.WHERE_CLAUSE%TYPE;
115 L_RTNODE_BIND_VAR_FLAG VARCHAR2(10);
116 L_ENUM_BIND_VAR_FLAG VARCHAR2(10);
117
118
119 CURSOR c_bindVal(l_node_id in NUMBER) IS
120 SELECT
121 rt_nodes_bind_val.SEL_RT_NODE_ID,
122 rt_nodes_bind_val.node_id,
123 rt_nodes_bind_val.bind_var_name,
124 rt_nodes_bind_val.bind_var_value
125 FROM
126 ieu_uwq_rtnode_bind_vals rt_nodes_bind_val
127 WHERE
128 (rt_nodes_bind_val.resource_id = p_resource_id) AND
129 (rt_nodes_bind_val.node_id = l_node_id) AND
130 (rt_nodes_bind_val.not_valid_flag = l_not_valid_flag);
131 BEGIN
132 l_not_valid_flag := 'N';
133 l_not_valid := 'N';
134
135 BEGIN
136 Select where_clause,
137 node_type,
138 refresh_view_name,
139 refresh_view_sum_col,
140 view_name,
141 sel_enum_id,
142 sel_rt_node_id,
143 res_cat_enum_flag
144 into l_extra_where_clause,
145 l_node_type,
146 X_NODE_DETAIL_RECORD.REFRESH_VIEW_NAME,
147 X_NODE_DETAIL_RECORD.refresh_view_sum_col,
148 X_NODE_DETAIL_RECORD.view_name,
149 l_sel_enum_id,
150 l_sel_rt_node_id,
151 l_res_cat_enum_flag
152 from ieu_uwq_sel_rt_nodes
153 where resource_id = p_resource_id
154 and node_id = p_node_id
155 and not_valid = l_not_valid;
156 EXCEPTION when no_data_found THEN
157 null;
158 END;
159
160
161 if ( (p_node_id = IEU_CONSTS_PUB.G_SNID_MEDIA) or
162 (p_node_id = IEU_CONSTS_PUB.G_SNID_BLENDED) )
163 then
164 begin
165 select
166 where_clause
167 into
168 l_res_cat_where_clause
169 from
170 ieu_uwq_res_cats_b
171 where
172 res_cat_id = 10001;
173
174 exception
175 when no_data_found then
176 null;
177 end;
178 else
179 l_res_cat_where_clause := ieu_pub.get_enum_res_cat(l_sel_enum_id);
180 end if;
181
182 -- Set the complete Where Clause
183
184 if (l_extra_where_clause is NULL)
185 then
186 l_where_clause := l_res_cat_where_clause;
187 l_rtnode_bind_var_flag := 'F';
188 else
189
190 if (l_res_cat_enum_flag = 'Y') OR (l_res_cat_enum_flag is NULL)
191 then
192 if (l_res_cat_where_clause) is not null
193 then
194 l_where_clause :=
195 l_res_cat_where_clause || ' and ' || l_extra_where_clause;
196 l_rtnode_bind_var_flag := 'F';
197 end if;
198 else
199 l_where_clause := l_extra_where_clause;
200 l_rtnode_bind_var_flag := 'T';
201 end if;
202 end if;
203
204 select
205 decode(
206 (instr(l_res_cat_where_clause, ':resource_id', 1, 1)), 0, 'F','T' )
207 into
208 l_enum_bind_var_flag
209 from
210 dual;
211
212 If (l_rtnode_bind_var_flag = 'T')
213 then
214
215
216 for b in c_bindVal(p_node_id)
217 loop
218
219 if ( (b.sel_rt_node_id = l_sel_rt_node_id) and
220 (b.node_id = p_node_id) )
221 then
222 SELECT REPLACE(l_where_clause,b.bind_var_name,b.bind_var_value)
223 INTO l_where_clause
224 FROM DUAL;
225
226 -- Set the Bind variables for Runtime Where Clause
227 SELECT REPLACE(l_extra_where_clause,b.bind_var_name,b.bind_var_value)
228 INTO l_extra_where_clause
229 FROM DUAL;
230
231
232 end if;
233
234 end loop;
235
236
237 else
238
239 if (l_enum_bind_var_flag = 'T')
240 then
241 SELECT REPLACE(l_where_clause,':resource_id',p_resource_id)
242 INTO l_where_clause
243 FROM DUAL;
244 end if;
245
246 end if;
247
248 X_NODE_DETAIL_RECORD.NODE_TYPE := l_node_type;
249 X_NODE_DETAIL_RECORD.COMPLETE_WHERE_CLAUSE := l_where_clause;
250 X_NODE_DETAIL_RECORD.NODE_RUNTIME_WHERE_CLAUSE := l_extra_where_clause;
251
252 END;
253
254 PROCEDURE GET_UWQ_NODE_DETAILS
255 (P_RESOURCE_ID IN NUMBER,
256 P_NODE_ID IN NUMBER,
257 X_NODE_DETAIL_RECORD OUT NOCOPY IEU_PUB.NodeDetailRecord,
258 X_BIND_VARIABLE_RECORD_LIST OUT NOCOPY IEU_PUB.BindVariableRecordList) IS
259
260 l_where_clause varchar2(4000);
261 l_node_type IEU_UWQ_SEL_RT_NODES.NODE_TYPE%TYPE;
262 l_res_cat_enum_flag IEU_UWQ_SEL_RT_NODES.RES_CAT_ENUM_FLAG%TYPE;
263 l_extra_where_clause IEU_UWQ_SEL_RT_NODES.WHERE_CLAUSE%TYPE;
264 l_sel_rt_node_id IEU_UWQ_SEL_RT_NODES.SEL_RT_NODE_ID%TYPE;
265 l_sel_enum_id IEU_UWQ_SEL_RT_NODES.SEL_ENUM_ID%TYPE;
266 L_RES_CAT_WHERE_CLAUSE IEU_UWQ_RES_CATS_B.WHERE_CLAUSE%TYPE;
267 L_RTNODE_BIND_VAR_FLAG VARCHAR2(10);
268 L_ENUM_BIND_VAR_FLAG VARCHAR2(10);
269 l_rec_count NUMBER;
270
271
272 CURSOR c_bindVal(l_node_id in NUMBER) IS
273 SELECT
274 rt_nodes_bind_val.SEL_RT_NODE_ID,
275 rt_nodes_bind_val.node_id,
276 rt_nodes_bind_val.bind_var_name,
277 rt_nodes_bind_val.bind_var_value,
278 rt_nodes_bind_val.bind_var_datatype
279 FROM
280 ieu_uwq_rtnode_bind_vals rt_nodes_bind_val
281 WHERE
282 (rt_nodes_bind_val.resource_id = p_resource_id) AND
283 (rt_nodes_bind_val.node_id = l_node_id) AND
284 (rt_nodes_bind_val.not_valid_flag = l_not_valid_flag);
285
286 BEGIN
287 l_not_valid_flag := 'N';
288 l_not_valid := 'N';
289
290 BEGIN
291 Select where_clause,
292 node_type,
293 refresh_view_name,
294 refresh_view_sum_col,
295 view_name,
296 sel_enum_id,
297 sel_rt_node_id,
298 res_cat_enum_flag
299 into l_extra_where_clause,
300 l_node_type,
301 X_NODE_DETAIL_RECORD.REFRESH_VIEW_NAME,
302 X_NODE_DETAIL_RECORD.refresh_view_sum_col,
303 X_NODE_DETAIL_RECORD.view_name,
304 l_sel_enum_id,
305 l_sel_rt_node_id,
306 l_res_cat_enum_flag
307 from ieu_uwq_sel_rt_nodes
308 where resource_id = p_resource_id
309 and node_id = p_node_id
310 and not_valid = l_not_valid;
311 EXCEPTION when no_data_found THEN
312 null;
313 END;
314
315
316 if ( (p_node_id = IEU_CONSTS_PUB.G_SNID_MEDIA) or
317 (p_node_id = IEU_CONSTS_PUB.G_SNID_BLENDED) )
318 then
319 begin
320 select
321 where_clause
322 into
323 l_res_cat_where_clause
324 from
325 ieu_uwq_res_cats_b
326 where
327 res_cat_id = 10001;
328
329 exception
330 when no_data_found then
331 null;
332 end;
333 else
334 l_res_cat_where_clause := ieu_pub.get_enum_res_cat(l_sel_enum_id);
335 end if;
336
337 -- Set the complete Where Clause
338
339 if (l_extra_where_clause is NULL)
340 then
341 l_where_clause := l_res_cat_where_clause;
342 l_rtnode_bind_var_flag := 'F';
343 else
344
345 if (l_res_cat_enum_flag = 'Y') OR (l_res_cat_enum_flag is NULL)
346 then
347 if (l_res_cat_where_clause) is not null
348 then
349 l_where_clause :=
350 l_res_cat_where_clause || ' and ' || l_extra_where_clause;
351 l_rtnode_bind_var_flag := 'F';
352 end if;
353 else
354 l_where_clause := l_extra_where_clause;
355 l_rtnode_bind_var_flag := 'T';
356 end if;
357 end if;
358
359 X_NODE_DETAIL_RECORD.RAW_COMPLETE_WHERE_CLAUSE := l_where_clause;
360 X_NODE_DETAIL_RECORD.RAW_NODE_RUNTIME_WHERE_CLAUSE := l_extra_where_clause;
361
362 select
363 decode(
364 (instr(l_res_cat_where_clause, ':resource_id', 1, 1)), 0, 'F','T' )
365 into
366 l_enum_bind_var_flag
367 from
368 dual;
369
370 If (l_rtnode_bind_var_flag = 'T')
371 then
372
373 l_rec_count := 0;
374 for b in c_bindVal(p_node_id)
375 loop
376
377 if ( (b.sel_rt_node_id = l_sel_rt_node_id) and
378 (b.node_id = p_node_id) )
379 then
380 SELECT REPLACE(l_where_clause,b.bind_var_name,b.bind_var_value)
381 INTO l_where_clause
382 FROM DUAL;
383
384 -- Set the Bind variables for Runtime Where Clause
385 SELECT REPLACE(l_extra_where_clause,b.bind_var_name,b.bind_var_value)
386 INTO l_extra_where_clause
387 FROM DUAL;
388
389 l_rec_count := l_rec_count + 1;
390 X_BIND_VARIABLE_RECORD_LIST(l_rec_count).BIND_VAR_NAME := b.bind_var_name;
391 X_BIND_VARIABLE_RECORD_LIST(l_rec_count).BIND_VAR_VALUE := b.bind_var_value;
392 X_BIND_VARIABLE_RECORD_LIST(l_rec_count).BIND_VAR_DATA_TYPE := b.bind_var_datatype;
393 end if;
394
395 end loop;
396
397
398 else
399
400 if (l_enum_bind_var_flag = 'T')
401 then
402 SELECT REPLACE(l_where_clause,':resource_id',p_resource_id)
403 INTO l_where_clause
404 FROM DUAL;
405 end if;
406
407 end if;
408
409 X_NODE_DETAIL_RECORD.NODE_TYPE := l_node_type;
410 X_NODE_DETAIL_RECORD.COMPLETE_WHERE_CLAUSE := l_where_clause;
411 X_NODE_DETAIL_RECORD.NODE_RUNTIME_WHERE_CLAUSE := l_extra_where_clause;
412
413 END;
414 END IEU_PUB;