eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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;