[Home] [Help]
PACKAGE BODY: APPS.WF_NTFRULE_UTIL
Source
1 package body WF_NTFRULE_UTIL as
2 /* $Header: WFNTFRULEUTB.pls 120.1 2005/07/02 03:16:36 appldev noship $ */
3
4
5 --This Function returns a comma seperated list of display names for the given attributeName
6 --and attributeType which exists in the message types used by the given ruleName
7
8 function getAttrDisplayNameByRule(attributeName in VARCHAR2,
9 attributeType in VARCHAR2,
10 ruleName in VARCHAR2)
11 return VARCHAR2
12 is
13
14 displayName VARCHAR2(32000);
15 cnt INTEGER := 1;
16
17 cursor ntfrule_cursor is
18 select distinct display_name from wf_message_attributes_vl where
19 name = attributeName and type = attributeType and
20 message_type in (select message_type from wf_ntf_rule_criteria where rule_name = ruleName);
21
22 Begin
23
24 FOR rec IN ntfrule_cursor
25 LOOP
26
27 IF cnt = 1 THEN
28 displayName := rec.display_name;
29
30 ELSE
31 displayName := displayName ||', '|| rec.display_name;
32
33 END IF;
34
35 cnt := cnt + 1;
36
37 END LOOP;
38
39
40 return(displayName);
41
42 exception
43 when others then
44 wf_core.context('WF_NTFRULE_UTIL', 'getAttributeDisplayNameByRule', attributeName, attributeType, ruleName);
45 raise;
46
47
48 End getAttrDisplayNameByRule;
49
50
51
52
53 --Used by the Find Notification Rules Conflicts page
54 --This Function returns a comma seperated list of display names for the given attributeName
55 --and attributeType which exists in the given message type.
56
57 function getAttrDisplayNameByMsgType(attributeName in VARCHAR2,
58 attributeType in VARCHAR2,
59 messageType in VARCHAR2)
60 return VARCHAR2
61 is
62
63 displayName VARCHAR2(32000);
64 cnt INTEGER := 1;
65
66 cursor ntfrule_cursor is
67 select distinct display_name from wf_message_attributes_vl where
68 name = attributeName and type = attributeType and
69 message_type = messageType;
70
71 Begin
72
73 FOR rec IN ntfrule_cursor
74 LOOP
75
76 IF cnt = 1 THEN
77 displayName := rec.display_name;
78
79 ELSE
80 displayName := displayName ||', '|| rec.display_name;
81
82 END IF;
83
84 cnt := cnt + 1;
85
86 END LOOP;
87
88
89 return(displayName);
90
91 exception
92 when others then
93 wf_core.context('WF_NTFRULE_UTIL', 'getAttributeDisplayName', attributeName, attributeType, messageType);
94 raise;
95
96
97 End getAttrDisplayNameByMsgType;
98
99
100
101 --This function expects a comma seperated list of messagetypes
102 --i.e 'TSTFWKEM','WFRTFORM'... This value is used in the IN clause of the query
103
104 --We are using REF CURSORS so as to execute a dynamic sql. We need to use dynamic sql as
105 --this sql requires values for its IN clause and there is no straight forward way to bind
106 --mutiple comma sepearated messageTypes values.
107
108 function getMsgDisplayName(attributeName in VARCHAR2,
109 attributeDisplayName in VARCHAR2,
110 attributeType in VARCHAR2,
111 messageTypes in VARCHAR2)
112 return varchar2
113 is
114
115 TYPE cur_typ IS REF CURSOR;
116
117 ntfrule_cursor cur_typ;
118
119 msgDisplayName VARCHAR2(720);
120 displayName VARCHAR2(360);
121 internalName VARCHAR2(360);
122
123 query_stmt VARCHAR2(2000);
124
125
126
127
128 Begin
129
130 query_stmt := 'select distinct display_name, name from wf_messages_vl '||
131 'where ' ||
132 '(name,type) in '||
133 '(select message_name, message_type from wf_message_attributes_vl '||
134 'where '||
135 'name = :1 and '||
136 'display_name = :2 and '||
137 'type = :3 and '||
138 'message_type in ('||messageTypes||'))';
139
140
141
142 OPEN ntfrule_cursor FOR query_stmt using attributeName, attributeDisplayName, attributeType;
143
144 LOOP
145 FETCH ntfrule_cursor into displayName, internalName;
146
147 EXIT WHEN ntfrule_cursor%NOTFOUND;
148
149
150 IF ntfrule_cursor%ROWCOUNT >1 THEN
151 msgDisplayName := '(Occurs in multiple messages)';
152 EXIT;
153
154 ELSE
155 msgDisplayName := displayName ||' ('||internalName||')';
156
157
158 END IF;
159
160 END LOOP;
161
162 close ntfrule_cursor;
163
164
165 return(msgDisplayName);
166
167
168 exception
169 when others then
170 wf_core.context('WF_NTFRULE_UTIL', 'getMessageDisplayName2', attributeName, attributeType, messageTypes);
171 raise;
172
173 end getMsgDisplayName;
174
175
176 --This function raises an event which launches the concurrent program to denormalize the
177 --WF_NOTIFICATIONS table. It also returns the request id of the launched concurrent program.
178
179 function raiseDenormalizeEvent(eventKey in VARCHAR2)
180 return varchar2
181 is
182
183 requestId VARCHAR2(2000);
184 eventName VARCHAR2(50) :='oracle.apps.fnd.wf.attribute.denormalize';
185 param_list wf_parameter_list_t;
186 sendDate DATE := SYSDATE;
187
188
189
190 Begin
191
192 wf_event.raise3(eventName,eventKey,null,param_list,sendDate);
193
194 requestId := wf_event.getValueForParameter('REQUEST_ID',param_list);
195
196 return(requestId);
197
198 exception
199 when others then
200 wf_core.context('WF_NTFRULE_UTIL', 'raiseDenomalizeEvent', eventKey);
201 raise;
202
203 end raiseDenormalizeEvent;
204
205
206
207
208 /*Rosetta Wrapper Generated Code for Simulation -- Start*/
209
210
211 procedure rosetta_table_copy_in_p1(t out nocopy wf_ntf_rule.custom_col_type, a0 JTF_VARCHAR2_TABLE_100
212 , a1 JTF_VARCHAR2_TABLE_100
213 , a2 JTF_VARCHAR2_TABLE_100
214 , a3 JTF_VARCHAR2_TABLE_32767
215 , a4 JTF_VARCHAR2_TABLE_100
216 , a5 JTF_NUMBER_TABLE
217 , a6 JTF_VARCHAR2_TABLE_100
218 ) as
219 ddindx binary_integer; indx binary_integer;
220 begin
221 if a0 is not null and a0.count > 0 then
222 if a0.count > 0 then
223 indx := a0.first;
224 ddindx := 1;
225 while true loop
226 t(ddindx).rule_name := a0(indx);
227 t(ddindx).column_name := a1(indx);
228 t(ddindx).attribute_name := a2(indx);
229 t(ddindx).display_name := a3(indx);
230 t(ddindx).customization_level := a4(indx);
231 t(ddindx).phase := a5(indx);
232 t(ddindx).override := a6(indx);
233 ddindx := ddindx+1;
234 if a0.last =indx
235 then exit;
236 end if;
237 indx := a0.next(indx);
238 end loop;
239 end if;
240 end if;
241 end rosetta_table_copy_in_p1;
242
243 procedure rosetta_table_copy_out_p1(t wf_ntf_rule.custom_col_type, a0 out nocopy JTF_VARCHAR2_TABLE_100
244 , a1 out nocopy JTF_VARCHAR2_TABLE_100
245 , a2 out nocopy JTF_VARCHAR2_TABLE_100
246 , a3 out nocopy JTF_VARCHAR2_TABLE_32767
247 , a4 out nocopy JTF_VARCHAR2_TABLE_100
248 , a5 out nocopy JTF_NUMBER_TABLE
249 , a6 out nocopy JTF_VARCHAR2_TABLE_100
250 ) as
251 ddindx binary_integer; indx binary_integer;
252 begin
253 if t is null or t.count = 0 then
254 a0 := JTF_VARCHAR2_TABLE_100();
255 a1 := JTF_VARCHAR2_TABLE_100();
256 a2 := JTF_VARCHAR2_TABLE_100();
257 a3 := JTF_VARCHAR2_TABLE_32767();
258 a4 := JTF_VARCHAR2_TABLE_100();
259 a5 := JTF_NUMBER_TABLE();
260 a6 := JTF_VARCHAR2_TABLE_100();
261 else
262 a0 := JTF_VARCHAR2_TABLE_100();
263 a1 := JTF_VARCHAR2_TABLE_100();
264 a2 := JTF_VARCHAR2_TABLE_100();
265 a3 := JTF_VARCHAR2_TABLE_32767();
266 a4 := JTF_VARCHAR2_TABLE_100();
267 a5 := JTF_NUMBER_TABLE();
268 a6 := JTF_VARCHAR2_TABLE_100();
269 if t.count > 0 then
270 a0.extend(t.count);
271 a1.extend(t.count);
272 a2.extend(t.count);
273 a3.extend(t.count);
274 a4.extend(t.count);
275 a5.extend(t.count);
276 a6.extend(t.count);
277 ddindx := t.first;
278 indx := 1;
279 while true loop
280 a0(indx) := t(ddindx).rule_name;
281 a1(indx) := t(ddindx).column_name;
282 a2(indx) := t(ddindx).attribute_name;
283 a3(indx) := t(ddindx).display_name;
284 a4(indx) := t(ddindx).customization_level;
285 a5(indx) := t(ddindx).phase;
286 a6(indx) := t(ddindx).override;
287 indx := indx+1;
288 if t.last =ddindx
289 then exit;
290 end if;
291 ddindx := t.next(ddindx);
292 end loop;
293 end if;
294 end if;
295 end rosetta_table_copy_out_p1;
296
297 procedure simulate_rules(p_message_type VARCHAR2
298 , p_message_name VARCHAR2
299 , p_customization_level VARCHAR2
300 , p3_a0 out nocopy JTF_VARCHAR2_TABLE_100
301 , p3_a1 out nocopy JTF_VARCHAR2_TABLE_100
302 , p3_a2 out nocopy JTF_VARCHAR2_TABLE_100
303 , p3_a3 out nocopy JTF_VARCHAR2_TABLE_32767
304 , p3_a4 out nocopy JTF_VARCHAR2_TABLE_100
305 , p3_a5 out nocopy JTF_NUMBER_TABLE
306 , p3_a6 out nocopy JTF_VARCHAR2_TABLE_100
307 )
308
309 as
310 ddx_custom_col_tbl wf_ntf_rule.custom_col_type;
311 ddindx binary_integer; indx binary_integer;
312 begin
313
314 -- copy data to the local IN or IN-OUT args, if any
315
316
317
318
319 -- here's the delegated call to the old PL/SQL routine
320 wf_ntf_rule.simulate_rules(p_message_type,
321 p_message_name,
322 p_customization_level,
323 ddx_custom_col_tbl);
324
325 -- copy data back from the local variables to OUT or IN-OUT args, if any
326
327
328
329 WF_NTFRULE_UTIL.rosetta_table_copy_out_p1(ddx_custom_col_tbl, p3_a0
330 , p3_a1
331 , p3_a2
332 , p3_a3
333 , p3_a4
334 , p3_a5
335 , p3_a6
336 );
337 end;
338
339 /*Rosetta Wrapper Generated Code for Simulation -- Finish*/
340
341 END WF_NTFRULE_UTIL;