[Home] [Help]
PACKAGE BODY: APPS.PO_UTF8TRIGGERS_PVT
Source
1 PACKAGE BODY po_utf8triggers_pvt AS
2 /* $Header: POXVUTFB.pls 120.0 2005/06/01 14:36:25 appldev noship $ */
3
4 /*
5 * Record type for storing UTF8 trigger information. column_display_name represents the
6 * text that is placed into the fnd_message on errors.
7 */
8 TYPE utf8_trg_rec_type IS RECORD (
9 table_name VARCHAR2(50),
10 column_name VARCHAR2(50),
11 column_display_name VARCHAR2(50),
12 max_length VARCHAR2(50),
13 trigger_name VARCHAR2(50)
14 );
15
16 TYPE utf8_trg_tbl_type IS TABLE OF utf8_trg_rec_type
17 INDEX BY BINARY_INTEGER;
18
19 /* Global PL/SQL table within this package */
20 g_utf8_trigger_tbl utf8_trg_tbl_type;
21
22 -- Bug 2766729 START
23 /**
24 * Private Procedure: column_exists
25 * Modifies: none
26 * Effects: Returns TRUE if the specified column exists in the database,
27 * FALSE otherwise.
28 */
29 FUNCTION column_exists (p_trigger_rec UTF8_TRG_REC_TYPE) RETURN BOOLEAN IS
30 invalid_identifier EXCEPTION;
31 PRAGMA EXCEPTION_INIT(invalid_identifier,-00904);
32 BEGIN
33 -- If we can select from the column, then it exists.
34 EXECUTE IMMEDIATE 'SELECT '|| p_trigger_rec.column_name || ' FROM ' ||
35 p_trigger_rec.table_name || ' WHERE rownum = 1';
36 RETURN TRUE;
37 EXCEPTION
38 WHEN invalid_identifier THEN
39 RETURN FALSE;
40 END;
41 -- Bug 2766729 END
42
43 /**
44 * Private Procedure: initialize_globals
45 * Modifies: The PL/SQL table g_utf8_trigger_tbl.
46 * Effects: Initializes g_utf8_trigger_tbl to store all the data for each UTF8
47 * column expanded.
48 */
49 PROCEDURE initialize_globals IS
50
51 n NUMBER := 1; /* Counter for the PL/SQL table. */
52
53 BEGIN
54 -- Clear the table
55 g_utf8_trigger_tbl.DELETE; -- Bug 2766729
56
57 --
58 -- Populate the pl/sql table with details of all the UTF8 triggers
59 -- required for PO.
60 -- All columns from the same table must be grouped together in order to
61 -- process g_utf8_trigger_tbl correctly.
62 -- All trigger names follow a standard naming convention, abbreviating
63 -- the table name to 3 chars per word, with "_UTF8" appended to the end.
64 --
65
66 -- Bug 2766729
67 -- The PL/SQL table should only include columns that exist in the database.
68
69
70 g_utf8_trigger_tbl(n).table_name := 'PO_APPROVAL_LIST_LINES';
71 g_utf8_trigger_tbl(n).column_name := 'COMMENTS';
72 g_utf8_trigger_tbl(n).column_display_name := 'Comments';
73 g_utf8_trigger_tbl(n).max_length := '240';
74 g_utf8_trigger_tbl(n).trigger_name := 'PO_APL_LST_LNS_UTF8';
75 -- Bug 2766729
76 -- Only increment the counter if we want the column to be included.
77 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
78 n := n + 1;
79 END IF;
80
81 g_utf8_trigger_tbl(n).table_name := 'PO_HEADERS_ALL';
82 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_VENDOR';
83 g_utf8_trigger_tbl(n).column_display_name := 'Note To Vendor';
84 g_utf8_trigger_tbl(n).max_length := '240';
85 g_utf8_trigger_tbl(n).trigger_name := 'PO_HDR_ALL_UTF8';
86 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
87 n := n + 1;
88 END IF;
89
90 g_utf8_trigger_tbl(n).table_name := 'PO_HEADERS_ALL';
91 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_RECEIVER';
92 g_utf8_trigger_tbl(n).column_display_name := 'Note To Receiver';
93 g_utf8_trigger_tbl(n).max_length := '240';
94 g_utf8_trigger_tbl(n).trigger_name := 'PO_HDR_ALL_UTF8';
95 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
96 n := n + 1;
97 END IF;
98
99 g_utf8_trigger_tbl(n).table_name := 'PO_HEADERS_ARCHIVE_ALL';
100 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_VENDOR';
101 g_utf8_trigger_tbl(n).column_display_name := 'Note To Vendor';
102 g_utf8_trigger_tbl(n).max_length := '240';
103 g_utf8_trigger_tbl(n).trigger_name := 'PO_HDR_ARC_ALL_UTF8';
104 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
105 n := n + 1;
106 END IF;
107
108 g_utf8_trigger_tbl(n).table_name := 'PO_HEADERS_ARCHIVE_ALL';
109 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_RECEIVER';
110 g_utf8_trigger_tbl(n).column_display_name := 'Note To Receiver';
111 g_utf8_trigger_tbl(n).max_length := '240';
112 g_utf8_trigger_tbl(n).trigger_name := 'PO_HDR_ARC_ALL_UTF8';
113 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
114 n := n + 1;
115 END IF;
116
117 g_utf8_trigger_tbl(n).table_name := 'PO_HEADERS_INTERFACE';
118 g_utf8_trigger_tbl(n).column_name := 'VENDOR_NAME';
119 g_utf8_trigger_tbl(n).column_display_name := 'Vendor Name';
120 g_utf8_trigger_tbl(n).max_length := '80';
121 g_utf8_trigger_tbl(n).trigger_name := 'PO_HDR_INT_UTF8';
122 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
123 n := n + 1;
124 END IF;
125
126 g_utf8_trigger_tbl(n).table_name := 'PO_HEADERS_INTERFACE';
127 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_RECEIVER';
128 g_utf8_trigger_tbl(n).column_display_name := 'Note To Receiver';
129 g_utf8_trigger_tbl(n).max_length := '240';
130 g_utf8_trigger_tbl(n).trigger_name := 'PO_HDR_INT_UTF8';
131 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
132 n := n + 1;
133 END IF;
134
135 g_utf8_trigger_tbl(n).table_name := 'PO_HEADERS_INTERFACE';
136 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_VENDOR';
137 g_utf8_trigger_tbl(n).column_display_name := 'Note To Vendor';
138 g_utf8_trigger_tbl(n).max_length := '240';
139 g_utf8_trigger_tbl(n).trigger_name := 'PO_HDR_INT_UTF8';
140 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
141 n := n + 1;
142 END IF;
143
144 g_utf8_trigger_tbl(n).table_name := 'PO_LINE_LOCATIONS_ALL';
145 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_RECEIVER';
146 g_utf8_trigger_tbl(n).column_display_name := 'Note To Receiver';
147 g_utf8_trigger_tbl(n).max_length := '240';
148 g_utf8_trigger_tbl(n).trigger_name := 'PO_LNE_LOC_ALL_UTF8';
149 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
150 n := n + 1;
151 END IF;
152
153 g_utf8_trigger_tbl(n).table_name := 'PO_LINES_ALL';
154 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_VENDOR';
155 g_utf8_trigger_tbl(n).column_display_name := 'Note To Vendor';
156 g_utf8_trigger_tbl(n).max_length := '240';
157 g_utf8_trigger_tbl(n).trigger_name := 'PO_LNS_ALL_UTF8';
158 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
159 n := n + 1;
160 END IF;
161
162 g_utf8_trigger_tbl(n).table_name := 'PO_LINES_ARCHIVE_ALL';
163 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_VENDOR';
164 g_utf8_trigger_tbl(n).column_display_name := 'Note To Vendor';
165 g_utf8_trigger_tbl(n).max_length := '240';
166 g_utf8_trigger_tbl(n).trigger_name := 'PO_LNS_ARC_ALL_UTF8';
167 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
168 n := n + 1;
169 END IF;
170
171 g_utf8_trigger_tbl(n).table_name := 'PO_LINES_INTERFACE';
172 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_RECEIVER';
173 g_utf8_trigger_tbl(n).column_display_name := 'Note To Receiver';
174 g_utf8_trigger_tbl(n).max_length := '240';
175 g_utf8_trigger_tbl(n).trigger_name := 'PO_LNS_INT_UTF8';
176 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
177 n := n + 1;
178 END IF;
179
180 g_utf8_trigger_tbl(n).table_name := 'PO_LINES_INTERFACE';
181 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_VENDOR';
182 g_utf8_trigger_tbl(n).column_display_name := 'Note To Vendor';
183 g_utf8_trigger_tbl(n).max_length := '240';
184 g_utf8_trigger_tbl(n).trigger_name := 'PO_LNS_INT_UTF8';
185 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
186 n := n + 1;
187 END IF;
188
189 g_utf8_trigger_tbl(n).table_name := 'PO_RELEASES_ALL';
190 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_VENDOR';
191 g_utf8_trigger_tbl(n).column_display_name := 'Note To Vendor';
192 g_utf8_trigger_tbl(n).max_length := '240';
193 g_utf8_trigger_tbl(n).trigger_name := 'PO_REL_ALL_UTF8';
194 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
195 n := n + 1;
196 END IF;
197
198 g_utf8_trigger_tbl(n).table_name := 'PO_RELEASES_ARCHIVE_ALL';
199 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_VENDOR';
200 g_utf8_trigger_tbl(n).column_display_name := 'Note To Vendor';
201 g_utf8_trigger_tbl(n).max_length := '240';
202 g_utf8_trigger_tbl(n).trigger_name := 'PO_REL_ARC_ALL_UTF8';
203 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
204 n := n + 1;
205 END IF;
206
207 g_utf8_trigger_tbl(n).table_name := 'PO_REQUISITIONS_INTERFACE_ALL';
208 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_BUYER';
209 g_utf8_trigger_tbl(n).column_display_name := 'Note To Buyer';
210 g_utf8_trigger_tbl(n).max_length := '240';
211 g_utf8_trigger_tbl(n).trigger_name := 'PO_REQ_INT_ALL_UTF8';
212 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
213 n := n + 1;
214 END IF;
215
216 g_utf8_trigger_tbl(n).table_name := 'PO_REQUISITIONS_INTERFACE_ALL';
217 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_RECEIVER';
218 g_utf8_trigger_tbl(n).column_display_name := 'Note To Receiver';
219 g_utf8_trigger_tbl(n).max_length := '240';
220 g_utf8_trigger_tbl(n).trigger_name := 'PO_REQ_INT_ALL_UTF8';
221 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
222 n := n + 1;
223 END IF;
224
225 g_utf8_trigger_tbl(n).table_name := 'PO_REQUISITIONS_INTERFACE_ALL';
226 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_APPROVER';
227 g_utf8_trigger_tbl(n).column_display_name := 'Note To Approver';
228 g_utf8_trigger_tbl(n).max_length := '240';
229 g_utf8_trigger_tbl(n).trigger_name := 'PO_REQ_INT_ALL_UTF8';
230 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
231 n := n + 1;
232 END IF;
233
234 g_utf8_trigger_tbl(n).table_name := 'PO_REQUISITIONS_INTERFACE_ALL';
235 g_utf8_trigger_tbl(n).column_name := 'JUSTIFICATION';
236 g_utf8_trigger_tbl(n).column_display_name := 'Justification';
237 g_utf8_trigger_tbl(n).max_length := '240';
238 g_utf8_trigger_tbl(n).trigger_name := 'PO_REQ_INT_ALL_UTF8';
239 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
240 n := n + 1;
241 END IF;
242
243 g_utf8_trigger_tbl(n).table_name := 'PO_REQUISITION_LINES_ALL';
244 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_AGENT';
245 g_utf8_trigger_tbl(n).column_display_name := 'Note To Agent';
246 g_utf8_trigger_tbl(n).max_length := '240';
247 g_utf8_trigger_tbl(n).trigger_name := 'PO_REQ_LNS_ALL_UTF8';
248 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
249 n := n + 1;
250 END IF;
251
252 g_utf8_trigger_tbl(n).table_name := 'PO_REQUISITION_LINES_ALL';
253 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_RECEIVER';
254 g_utf8_trigger_tbl(n).column_display_name := 'Note To Receiver';
255 g_utf8_trigger_tbl(n).max_length := '240';
256 g_utf8_trigger_tbl(n).trigger_name := 'PO_REQ_LNS_ALL_UTF8';
257 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
258 n := n + 1;
259 END IF;
260
261 g_utf8_trigger_tbl(n).table_name := 'PO_REQUISITION_LINES_ALL';
262 g_utf8_trigger_tbl(n).column_name := 'AGENT_RETURN_NOTE';
263 g_utf8_trigger_tbl(n).column_display_name := 'Agent Return Note';
264 g_utf8_trigger_tbl(n).max_length := '240';
265 g_utf8_trigger_tbl(n).trigger_name := 'PO_REQ_LNS_ALL_UTF8';
266 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
267 n := n + 1;
268 END IF;
269
270 g_utf8_trigger_tbl(n).table_name := 'PO_REQUISITION_LINES_ALL';
271 g_utf8_trigger_tbl(n).column_name := 'NOTE_TO_VENDOR';
272 g_utf8_trigger_tbl(n).column_display_name := 'Note To Vendor';
273 g_utf8_trigger_tbl(n).max_length := '240';
274 g_utf8_trigger_tbl(n).trigger_name := 'PO_REQ_LNS_ALL_UTF8';
275 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
276 n := n + 1;
277 END IF;
278
279 g_utf8_trigger_tbl(n).table_name := 'PO_REQUISITION_LINES_ALL';
280 g_utf8_trigger_tbl(n).column_name := 'JUSTIFICATION';
281 g_utf8_trigger_tbl(n).column_display_name := 'Justification';
282 g_utf8_trigger_tbl(n).max_length := '240';
283 g_utf8_trigger_tbl(n).trigger_name := 'PO_REQ_LNS_ALL_UTF8';
284 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
285 n := n + 1;
286 END IF;
287
288 g_utf8_trigger_tbl(n).table_name := 'PO_REQUISITION_LINES_ALL';
289 g_utf8_trigger_tbl(n).column_name := 'SUGGESTED_VENDOR_NAME';
290 g_utf8_trigger_tbl(n).column_display_name := 'Suggested Vendor Name';
291 g_utf8_trigger_tbl(n).max_length := '80';
292 g_utf8_trigger_tbl(n).trigger_name := 'PO_REQ_LNS_ALL_UTF8';
293 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
294 n := n + 1;
295 END IF;
296
297 g_utf8_trigger_tbl(n).table_name := 'PO_REQUISITION_LINES_ALL';
298 g_utf8_trigger_tbl(n).column_name := 'MANUFACTURER_NAME';
299 g_utf8_trigger_tbl(n).column_display_name := 'Manufacturer Name';
300 g_utf8_trigger_tbl(n).max_length := '30';
301 g_utf8_trigger_tbl(n).trigger_name := 'PO_REQ_LNS_ALL_UTF8';
302 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
303 n := n + 1;
304 END IF;
305
306 g_utf8_trigger_tbl(n).table_name := 'PO_VENDORS';
307 g_utf8_trigger_tbl(n).column_name := 'VENDOR_NAME';
308 g_utf8_trigger_tbl(n).column_display_name := 'Vendor Name';
309 g_utf8_trigger_tbl(n).max_length := '80';
310 g_utf8_trigger_tbl(n).trigger_name := 'PO_VDR_UTF8';
311 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
312 n := n + 1;
313 END IF;
314
315 g_utf8_trigger_tbl(n).table_name := 'PO_VENDOR_SITES_ALL';
316 g_utf8_trigger_tbl(n).column_name := 'ADDRESS_LINE1';
317 g_utf8_trigger_tbl(n).column_display_name := 'Address Line1';
318 g_utf8_trigger_tbl(n).max_length := '35';
319 g_utf8_trigger_tbl(n).trigger_name := 'PO_VDR_STS_ALL_UTF8';
320 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
321 n := n + 1;
322 END IF;
323
324 g_utf8_trigger_tbl(n).table_name := 'PO_VENDOR_SITES_ALL';
325 g_utf8_trigger_tbl(n).column_name := 'ADDRESS_LINE2';
326 g_utf8_trigger_tbl(n).column_display_name := 'Address Line2';
327 g_utf8_trigger_tbl(n).max_length := '35';
328 g_utf8_trigger_tbl(n).trigger_name := 'PO_VDR_STS_ALL_UTF8';
329 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
330 n := n + 1;
331 END IF;
332
333 g_utf8_trigger_tbl(n).table_name := 'PO_VENDOR_SITES_ALL';
334 g_utf8_trigger_tbl(n).column_name := 'ADDRESS_LINE3';
335 g_utf8_trigger_tbl(n).column_display_name := 'Address Line3';
336 g_utf8_trigger_tbl(n).max_length := '35';
337 g_utf8_trigger_tbl(n).trigger_name := 'PO_VDR_STS_ALL_UTF8';
338 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
339 n := n + 1;
340 END IF;
341
342 g_utf8_trigger_tbl(n).table_name := 'PO_VENDOR_SITES_ALL';
343 g_utf8_trigger_tbl(n).column_name := 'ADDRESS_LINE4';
344 g_utf8_trigger_tbl(n).column_display_name := 'Address Line4';
345 g_utf8_trigger_tbl(n).max_length := '35';
346 g_utf8_trigger_tbl(n).trigger_name := 'PO_VDR_STS_ALL_UTF8';
347 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
348 n := n + 1;
349 END IF;
350
351 g_utf8_trigger_tbl(n).table_name := 'PO_VENDOR_SITES_ALL';
352 g_utf8_trigger_tbl(n).column_name := 'STATE';
353 g_utf8_trigger_tbl(n).column_display_name := 'State';
354 g_utf8_trigger_tbl(n).max_length := '25';
355 g_utf8_trigger_tbl(n).trigger_name := 'PO_VDR_STS_ALL_UTF8';
356 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
357 n := n + 1;
358 END IF;
359
360 g_utf8_trigger_tbl(n).table_name := 'PO_VENDOR_SITES_ALL';
361 g_utf8_trigger_tbl(n).column_name := 'PROVINCE';
362 g_utf8_trigger_tbl(n).column_display_name := 'Province';
363 g_utf8_trigger_tbl(n).max_length := '25';
364 g_utf8_trigger_tbl(n).trigger_name := 'PO_VDR_STS_ALL_UTF8';
365 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
366 n := n + 1;
367 END IF;
368
372 g_utf8_trigger_tbl(n).max_length := '25';
369 g_utf8_trigger_tbl(n).table_name := 'PO_VENDOR_SITES_ALL';
370 g_utf8_trigger_tbl(n).column_name := 'COUNTY';
371 g_utf8_trigger_tbl(n).column_display_name := 'County';
373 g_utf8_trigger_tbl(n).trigger_name := 'PO_VDR_STS_ALL_UTF8';
374 IF column_exists( g_utf8_trigger_tbl(n) ) THEN -- Bug 2766729
375 n := n + 1;
376 END IF;
377
378 -- Bug 2766729 START
379 IF (n = g_utf8_trigger_tbl.LAST) THEN
380 -- The counter was not incremented for the last column, so we should
381 -- not include it.
382 g_utf8_trigger_tbl.DELETE(n);
383 END IF;
384 -- Bug 2766729 END
385
386 END initialize_globals;
387
388 /**
389 * Public Procedure: drop_all_triggers
390 * Modifies: Database triggers.
391 * Effects: Drops all triggers found in g_utf8_trigger_tbl from the database.
392 */
393 PROCEDURE drop_all_triggers IS
394
395 trigger_not_exists EXCEPTION;
396 PRAGMA EXCEPTION_INIT(trigger_not_exists,-04080);
397 l_previous_trigger VARCHAR2(50) := NULL;
398 l_triggers_dropped NUMBER := 0;
399
400 BEGIN
401 initialize_globals; -- Bug 2766729
402
403 --
404 -- Loop through all triggers in the plsql table
405 -- and drop them.
406 --
407 FOR i IN g_utf8_trigger_tbl.FIRST..g_utf8_trigger_tbl.LAST LOOP
408 --
409 -- Remove the current trigger only if it is different
410 -- from the previous loop's trigger.
411 -- This prevents the below code from attempting to
412 -- drop the same trigger twice.
413 --
414 IF (l_previous_trigger <> g_utf8_trigger_tbl(i).trigger_name) OR
415 (l_previous_trigger IS NULL) THEN
416
417 BEGIN
418
419 EXECUTE IMMEDIATE 'DROP TRIGGER '|| g_utf8_trigger_tbl(i).trigger_name;
420 l_triggers_dropped := l_triggers_dropped + 1;
421
422 EXCEPTION
423 WHEN trigger_not_exists THEN
424 NULL;
425 END;
426
427 END IF; -- IF l_previous_trigger ...
428
429 l_previous_trigger := g_utf8_trigger_tbl(i).trigger_name;
430
431 END LOOP;
432
433 END drop_all_triggers;
434
435 /**
436 * Public Procedure: create_all_triggers
437 * Modifies: Database triggers.
438 * Effects: Dynamically creates all triggers found in g_utf8_trigger_tbl in
439 * the database.
440 */
441 PROCEDURE create_all_triggers IS
442
443 l_previous_table VARCHAR2(50) := NULL;
444 l_triggers_created NUMBER := 0;
445 l_cr VARCHAR2(1) := '
446 ';
447 l_trigger_text VARCHAR2(30000);
448 l_previous_index NUMBER;
449
450 BEGIN
451 initialize_globals; -- Bug 2766729
452
453 --
454 -- Loop through all triggers in the cached list
455 -- and create them.
456 --
457 FOR i IN g_utf8_trigger_tbl.FIRST..g_utf8_trigger_tbl.LAST+1 LOOP
458 --
459 -- g_utf8_trigger_tbl.COUNT + 1 will always be the last
460 -- loop, at this point g_utf8_trigger_tbl(i) is null so
461 -- all we need to do is execute l_trigger_text
462 -- from the previous loop. All other processing
463 -- should be ignored.
464 --
465 IF (i <= g_utf8_trigger_tbl.COUNT) AND
466 (l_previous_table = g_utf8_trigger_tbl(i).table_name) THEN
467 --
468 -- More than one column should be validated in the
469 -- trigger. Append the additional trigger text to
470 -- validate this column.
471 --
472 l_trigger_text := l_trigger_text || l_cr ||
473 ' IF LENGTHB(:NEW.'|| g_utf8_trigger_tbl(i).column_name || ') > ' ||
474 g_utf8_trigger_tbl(i).max_length || ' THEN '|| l_cr ||
475 ' fnd_message.set_name(''PO'',''PO_UTF8_LENGTH_EXCEEDED'');' || l_cr ||
476 ' fnd_message.set_token(''COLUMN_DISPLAY_NAME'', ''' ||
477 INITCAP(g_utf8_trigger_tbl(i).column_display_name) || ''');' || l_cr ||
478 ' fnd_message.set_token(''OLD_LENGTH'', ' ||
479 g_utf8_trigger_tbl(i).max_length || ');' || l_cr ||
480 ' fnd_message.set_token(''ENTERED_DATA'', :NEW.' ||
481 g_utf8_trigger_tbl(i).column_name || ');' || l_cr ||
482 ' fnd_message.raise_error;' || l_cr ||
483 ' END IF;' || l_cr;
484 ELSE
485 --
486 -- Execute the dynamic DDL statement only if l_previous_table has
487 -- been set (i.e., not the first loop).
488 --
489 IF (l_previous_table IS NOT NULL) THEN
490 --
491 -- Add the 'end' statement to the trigger.
492 --
493 l_trigger_text := l_trigger_text || l_cr ||
494 'END ' || g_utf8_trigger_tbl(l_previous_index).trigger_name || ';';
495
496 EXECUTE IMMEDIATE l_trigger_text;
497
498 l_triggers_created := l_triggers_created + 1;
499
500 END IF; -- IF l_previous_table ...
501
502 IF i <= g_utf8_trigger_tbl.COUNT THEN
503 --
504 -- Again, only initialize the text for a new trigger
505 -- if not in g_utf8_trigger_tbl.COUNT+1.
506 --
507 l_trigger_text :=
508 'CREATE OR REPLACE TRIGGER ' || g_utf8_trigger_tbl(i).trigger_name || l_cr ||
509 'BEFORE INSERT OR UPDATE ON '|| g_utf8_trigger_tbl(i).table_name || l_cr ||
510 'FOR EACH ROW' || l_cr ||
511 'BEGIN' || l_cr || l_cr ||
512 ' IF LENGTHB(:NEW.' || g_utf8_trigger_tbl(i).column_name || ') > ' ||
513 g_utf8_trigger_tbl(i).max_length || ' THEN ' || l_cr ||
514 ' fnd_message.set_name(''PO'',''PO_UTF8_LENGTH_EXCEEDED'');' || l_cr ||
515 ' fnd_message.set_token(''COLUMN_DISPLAY_NAME'', ''' ||
516 INITCAP(g_utf8_trigger_tbl(i).column_display_name) || ''');' || l_cr ||
517 ' fnd_message.set_token(''OLD_LENGTH'', ' ||
518 g_utf8_trigger_tbl(i).max_length || ');' || l_cr ||
519 ' fnd_message.set_token(''ENTERED_DATA'', :NEW.'||
520 g_utf8_trigger_tbl(i).column_name || ');' || l_cr ||
521 ' fnd_message.raise_error;' || l_cr ||
522 ' END IF;' || l_cr;
523 END IF; -- IF i <= g_utf8_trigger_tbl.COUNT ...
524
525 END IF; -- IF (i <= g_utf8_trigger_tbl.COUNT) AND ...
526
527 --
528 -- Store the values for the next loop.
529 --
530 l_previous_index := i;
531 IF i <= g_utf8_trigger_tbl.COUNT THEN
532 l_previous_table := g_utf8_trigger_tbl(i).table_name;
533 END IF; -- IF i <= g_utf8_trigger_tbl.COUNT
534
535 END LOOP;
536
537 --
538 -- A commit is required because this package can be executed directly from
539 -- sqlplus.
540 --
541 COMMIT;
542
543 END create_all_triggers;
544
545 END po_utf8triggers_pvt;