[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_TRIGGER
Source
1 PACKAGE BODY fnd_flex_trigger AS
2 /* $Header: AFFFSV3B.pls 120.2.12010000.1 2008/07/25 14:14:29 appldev ship $ */
3
4
5 --------
6 -- PRIVATE TYPES
7 --
8 --
9
10 ------------
11 -- PRIVATE CONSTANTS
12 --
13
14
15 -------------
16 -- EXCEPTIONS
17 --
18
19
20 -------------
21 -- GLOBAL VARIABLES
22 --
23
24 ------------
25 -- PRIVATE FUNCTIONS
26 --
27
28 FUNCTION get_segdelim(appid IN NUMBER,
29 flex_code IN VARCHAR2,
30 flex_num IN NUMBER,
31 delimiter OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
32
33 FUNCTION get_segcount(appid IN NUMBER,
34 flex_code IN VARCHAR2,
35 flex_num IN NUMBER,
36 nenabled OUT NOCOPY NUMBER) RETURN BOOLEAN;
37
38 FUNCTION break_segs(catsegs IN VARCHAR2,
39 sepchar IN VARCHAR2,
40 nexpect IN NUMBER,
41 segs OUT NOCOPY FND_FLEX_SERVER1.StringArray)
42 RETURN BOOLEAN;
43
44 /* ----------------------------------------------------------------------- */
45 /* The following functions are called only from triggers on */
46 /* FND_FLEX_VALIDATION_RULES and FND_FLEX_VALIDATION_RULE_LINES. */
47 /* The trigger should use FND_MESSAGE.raise_exception if any of */
48 /* these functions returns error. */
49 /* ----------------------------------------------------------------------- */
50
51 /* ----------------------------------------------------------------------- */
52 /* Updates the FND_FLEX_VALIDATION_RULE_STATS table with the number */
53 /* of new rules, new include rule lines and new exclude rule lines */
54 /* for the given flexfield structure. Creates a new row in the */
55 /* rule stats table if there isn't already one there for this */
56 /* structure. Can input negative numbers to mean rules or lines */
57 /* were deleted. If anything deleted limits counts in rule stats */
58 /* table to >= 0. Does not delete rows from the rule stats table. */
59 /* Also sets the last update date to sysdate whenever it is called */
60 /* even if there were no new rules or lines. This is so that the */
61 /* last update for each flex structure can be set when a rule or line */
62 /* is updated. This is useful for keeping track of when to outdate */
63 /* entries in the cross-validation rules cache. */
64 /* Returns TRUE on success or FALSE and sets FND_MESSAGE if error. */
65 /* ----------------------------------------------------------------------- */
66 FUNCTION update_cvr_stats(appid IN NUMBER,
67 flex_code IN VARCHAR2,
68 flex_num IN NUMBER,
69 n_new_rules IN NUMBER,
70 n_new_incls IN NUMBER,
71 n_new_excls IN NUMBER) RETURN BOOLEAN IS
72 n_rules NUMBER;
73 n_incls NUMBER;
74 n_excls NUMBER;
75
76 CURSOR current_count(apid in NUMBER, fcode in VARCHAR2, fnum in NUMBER) is
77 select RULE_COUNT, INCLUDE_LINE_COUNT, EXCLUDE_LINE_COUNT
78 from FND_FLEX_VALIDATION_RULE_STATS where APPLICATION_ID = apid
79 and ID_FLEX_CODE = fcode and ID_FLEX_NUM = fnum
80 for update;
81 BEGIN
82
83 -- create row for this structure if needed.
84 --
85 INSERT INTO fnd_flex_validation_rule_stats (
86 application_id, id_flex_code, id_flex_num, creation_date,
87 created_by, last_update_date, last_updated_by, last_update_login,
88 rule_count, include_line_count, exclude_line_count)
89 SELECT appid, flex_code, flex_num, sysdate, -1, sysdate, -1, -1, 0, 0, 0
90 FROM dual WHERE NOT EXISTS
91 (SELECT NULL FROM fnd_flex_validation_rule_stats
92 WHERE application_id = appid
93 AND id_flex_code = flex_code
94 AND id_flex_num = flex_num);
95
96 -- If row for this structure is found lock it, and then update it,
97 --
98 open current_count(appid, flex_code, flex_num);
99 fetch current_count into n_rules, n_incls, n_excls;
100 if((current_count%FOUND is not null) and (current_count%FOUND)) then
101 n_rules := greatest(0, n_rules + n_new_rules);
102 n_incls := greatest(0, n_incls + n_new_incls);
103 n_excls := greatest(0, n_excls + n_new_excls);
104 UPDATE fnd_flex_validation_rule_stats
105 SET last_update_date = sysdate, last_updated_by = -1,
106 rule_count = n_rules, include_line_count = n_incls,
107 exclude_line_count = n_excls
108 WHERE application_id = appid
109 AND id_flex_code = flex_code
110 AND id_flex_num = flex_num;
111 end if;
112 close current_count;
113
114 return(TRUE);
115
116 EXCEPTION
117 WHEN OTHERS then
118 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
119 FND_MESSAGE.set_token('MSG', 'update_cvr_stats() exception: '||SQLERRM);
120 return(FALSE);
121
122 END update_cvr_stats;
123
124 /* ----------------------------------------------------------------------- */
125 /* Inserts separated segments of new rule line into */
126 /* the include or exclude lines table. Then updates the line count */
127 /* in the statistics table. */
128 /* Returns TRUE on success or FALSE and sets FND_MESSAGE if error. */
129 /* ----------------------------------------------------------------------- */
130 FUNCTION insert_rule_line(ruleline_id IN NUMBER,
131 appid IN NUMBER,
132 flex_code IN VARCHAR2,
133 flex_num IN NUMBER,
134 rule_name IN VARCHAR2,
135 incl_excl IN VARCHAR2,
136 enab_flag IN VARCHAR2,
137 create_by IN NUMBER,
138 create_date IN DATE,
139 update_date IN DATE,
140 update_by IN NUMBER,
141 update_login IN NUMBER,
142 catsegs_low IN VARCHAR2,
143 catsegs_high IN VARCHAR2) RETURN BOOLEAN IS
144
145 lo FND_FLEX_SERVER1.StringArray;
146 hi FND_FLEX_SERVER1.StringArray;
147 sepchar VARCHAR2(1);
148 new_include_lines NUMBER;
149 new_exclude_lines NUMBER;
150 nsegs NUMBER;
151
152 BEGIN
153
154 if((get_segdelim(appid, flex_code, flex_num, sepchar) = FALSE) or
155 (get_segcount(appid, flex_code, flex_num, nsegs) = FALSE) or
156 (break_segs(catsegs_low, sepchar, nsegs, lo) = FALSE) or
157 (break_segs(catsegs_high, sepchar, nsegs, hi) = FALSE)) then
158 return(FALSE);
159 end if;
160
161 if(incl_excl = 'I') then
162 new_include_lines := 1;
163 new_exclude_lines := 0;
164 insert into FND_FLEX_INCLUDE_RULE_LINES
165 (RULE_LINE_ID, APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM,
166 FLEX_VALIDATION_RULE_NAME, ENABLED_FLAG, CREATED_BY, CREATION_DATE,
167 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
168 SEGMENT1_LOW, SEGMENT1_HIGH, SEGMENT2_LOW, SEGMENT2_HIGH,
169 SEGMENT3_LOW, SEGMENT3_HIGH, SEGMENT4_LOW, SEGMENT4_HIGH,
170 SEGMENT5_LOW, SEGMENT5_HIGH, SEGMENT6_LOW, SEGMENT6_HIGH,
171 SEGMENT7_LOW, SEGMENT7_HIGH, SEGMENT8_LOW, SEGMENT8_HIGH,
172 SEGMENT9_LOW, SEGMENT9_HIGH, SEGMENT10_LOW, SEGMENT10_HIGH,
173 SEGMENT11_LOW, SEGMENT11_HIGH, SEGMENT12_LOW, SEGMENT12_HIGH,
174 SEGMENT13_LOW, SEGMENT13_HIGH, SEGMENT14_LOW, SEGMENT14_HIGH,
175 SEGMENT15_LOW, SEGMENT15_HIGH, SEGMENT16_LOW, SEGMENT16_HIGH,
176 SEGMENT17_LOW, SEGMENT17_HIGH, SEGMENT18_LOW, SEGMENT18_HIGH,
177 SEGMENT19_LOW, SEGMENT19_HIGH, SEGMENT20_LOW, SEGMENT20_HIGH,
178 SEGMENT21_LOW, SEGMENT21_HIGH, SEGMENT22_LOW, SEGMENT22_HIGH,
179 SEGMENT23_LOW, SEGMENT23_HIGH, SEGMENT24_LOW, SEGMENT24_HIGH,
180 SEGMENT25_LOW, SEGMENT25_HIGH, SEGMENT26_LOW, SEGMENT26_HIGH,
181 SEGMENT27_LOW, SEGMENT27_HIGH, SEGMENT28_LOW, SEGMENT28_HIGH,
182 SEGMENT29_LOW, SEGMENT29_HIGH, SEGMENT30_LOW, SEGMENT30_HIGH)
183 values
184 (ruleline_id, appid, flex_code, flex_num, rule_name, enab_flag,
185 create_by, create_date, update_date, update_by, update_login,
186 lo(1), hi(1), lo(2), hi(2), lo(3), hi(3), lo(4), hi(4),
187 lo(5), hi(5), lo(6), hi(6), lo(7), hi(7), lo(8), hi(8),
188 lo(9), hi(9), lo(10), hi(10), lo(11), hi(11), lo(12), hi(12),
189 lo(13), hi(13), lo(14), hi(14), lo(15), hi(15), lo(16), hi(16),
190 lo(17), hi(17), lo(18), hi(18), lo(19), hi(19), lo(20), hi(20),
191 lo(21), hi(21), lo(22), hi(22), lo(23), hi(23), lo(24), hi(24),
192 lo(25), hi(25), lo(26), hi(26), lo(27), hi(27), lo(28), hi(28),
193 lo(29), hi(29), lo(30), hi(30));
194 else
195 new_include_lines := 0;
196 new_exclude_lines := 1;
197 insert into FND_FLEX_EXCLUDE_RULE_LINES
198 (RULE_LINE_ID, APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM,
199 FLEX_VALIDATION_RULE_NAME, ENABLED_FLAG, CREATED_BY, CREATION_DATE,
200 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
201 SEGMENT1_LOW, SEGMENT1_HIGH, SEGMENT2_LOW, SEGMENT2_HIGH,
202 SEGMENT3_LOW, SEGMENT3_HIGH, SEGMENT4_LOW, SEGMENT4_HIGH,
203 SEGMENT5_LOW, SEGMENT5_HIGH, SEGMENT6_LOW, SEGMENT6_HIGH,
204 SEGMENT7_LOW, SEGMENT7_HIGH, SEGMENT8_LOW, SEGMENT8_HIGH,
205 SEGMENT9_LOW, SEGMENT9_HIGH, SEGMENT10_LOW, SEGMENT10_HIGH,
206 SEGMENT11_LOW, SEGMENT11_HIGH, SEGMENT12_LOW, SEGMENT12_HIGH,
207 SEGMENT13_LOW, SEGMENT13_HIGH, SEGMENT14_LOW, SEGMENT14_HIGH,
208 SEGMENT15_LOW, SEGMENT15_HIGH, SEGMENT16_LOW, SEGMENT16_HIGH,
209 SEGMENT17_LOW, SEGMENT17_HIGH, SEGMENT18_LOW, SEGMENT18_HIGH,
210 SEGMENT19_LOW, SEGMENT19_HIGH, SEGMENT20_LOW, SEGMENT20_HIGH,
211 SEGMENT21_LOW, SEGMENT21_HIGH, SEGMENT22_LOW, SEGMENT22_HIGH,
212 SEGMENT23_LOW, SEGMENT23_HIGH, SEGMENT24_LOW, SEGMENT24_HIGH,
213 SEGMENT25_LOW, SEGMENT25_HIGH, SEGMENT26_LOW, SEGMENT26_HIGH,
214 SEGMENT27_LOW, SEGMENT27_HIGH, SEGMENT28_LOW, SEGMENT28_HIGH,
215 SEGMENT29_LOW, SEGMENT29_HIGH, SEGMENT30_LOW, SEGMENT30_HIGH)
216 values
217 (ruleline_id, appid, flex_code, flex_num, rule_name, enab_flag,
218 create_by, create_date, update_date, update_by, update_login,
219 lo(1), hi(1), lo(2), hi(2), lo(3), hi(3), lo(4), hi(4),
220 lo(5), hi(5), lo(6), hi(6), lo(7), hi(7), lo(8), hi(8),
221 lo(9), hi(9), lo(10), hi(10), lo(11), hi(11), lo(12), hi(12),
222 lo(13), hi(13), lo(14), hi(14), lo(15), hi(15), lo(16), hi(16),
223 lo(17), hi(17), lo(18), hi(18), lo(19), hi(19), lo(20), hi(20),
224 lo(21), hi(21), lo(22), hi(22), lo(23), hi(23), lo(24), hi(24),
225 lo(25), hi(25), lo(26), hi(26), lo(27), hi(27), lo(28), hi(28),
226 lo(29), hi(29), lo(30), hi(30));
227 end if;
228
229 return(update_cvr_stats(appid, flex_code, flex_num, 0,
230 new_include_lines, new_exclude_lines));
231
232 EXCEPTION
233 WHEN OTHERS then
234 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
235 FND_MESSAGE.set_token('MSG', 'insert_rule_line() exception: '||SQLERRM);
236 return(FALSE);
237
238 END insert_rule_line;
239
240 /* ----------------------------------------------------------------------- */
241 /* Deletes rule line by rule_line_id from either */
242 /* the include or exclude lines table. Then updates the line count */
243 /* in the statistics table. */
244 /* Returns TRUE on success or FALSE and sets FND_MESSAGE if error. */
245 /* ----------------------------------------------------------------------- */
246 FUNCTION delete_rule_line(ruleline_id IN NUMBER,
247 appid IN NUMBER,
248 flex_code IN VARCHAR2,
249 flex_num IN NUMBER,
250 incl_excl IN VARCHAR2) RETURN BOOLEAN IS
251
252 new_include_lines NUMBER;
253 new_exclude_lines NUMBER;
254
255 BEGIN
256 if(incl_excl = 'I') then
257 new_include_lines := -1;
258 new_exclude_lines := 0;
259 DELETE FROM fnd_flex_include_rule_lines
260 WHERE rule_line_id = ruleline_id;
261 else
262 new_include_lines := 0;
263 new_exclude_lines := -1;
264 DELETE FROM fnd_flex_exclude_rule_lines
265 WHERE rule_line_id = ruleline_id;
266 end if;
267
268 return(update_cvr_stats(appid, flex_code, flex_num, 0,
269 new_include_lines, new_exclude_lines));
270
271 EXCEPTION
272 WHEN OTHERS then
273 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
274 FND_MESSAGE.set_token('MSG', 'delete_rule_line() exception: '||SQLERRM);
275 return(FALSE);
276
277 END delete_rule_line;
278
279 /* ----------------------------------------------------------------------- */
280 /* Updates rule line specified by ruleline_id in either */
281 /* the include or exclude lines table. Then updates the line count */
282 /* in the statistics table (actually just updates last_update_date). */
283 /* Returns TRUE on success or FALSE and sets FND_MESSAGE if error. */
284 /* ----------------------------------------------------------------------- */
285 FUNCTION update_rule_line(ruleline_id IN NUMBER,
286 appid IN NUMBER,
287 flex_code IN VARCHAR2,
288 flex_num IN NUMBER,
289 rule_name IN VARCHAR2,
290 incl_excl IN VARCHAR2,
291 enab_flag IN VARCHAR2,
292 create_by IN NUMBER,
293 create_date IN DATE,
294 update_date IN DATE,
295 update_by IN NUMBER,
296 update_login IN NUMBER,
297 catsegs_low IN VARCHAR2,
298 catsegs_high IN VARCHAR2) RETURN BOOLEAN IS
299
300 lo FND_FLEX_SERVER1.StringArray;
301 hi FND_FLEX_SERVER1.StringArray;
302 sepchar VARCHAR2(1);
303 nsegs NUMBER;
304 nlines NUMBER;
305 not_incl_excl VARCHAR2(1);
306
307 BEGIN
308
309 -- Check for Include <--> Exclude.
310 -- If changed set not_incl_excl to 'I' if include changed to exclude,
311 -- or to 'E' if exclude changed to exclude.
312 --
313 if(incl_excl = 'I') then
314 SELECT count(*) INTO nlines
315 FROM fnd_flex_include_rule_lines
316 WHERE rule_line_id = ruleline_id;
317 if(nlines = 0) then
318 not_incl_excl := 'E';
319 end if;
320 else
321 SELECT count(*) INTO nlines
322 FROM fnd_flex_exclude_rule_lines
323 WHERE rule_line_id = ruleline_id;
324 if(nlines = 0) then
325 not_incl_excl := 'I';
326 end if;
327 end if;
328
329 -- If not_incl_excl is set, then Include <--> exclude.
330 -- Delete from the not_incl_excl table and insert into the incl_excl table.
331 --
332 if(not_incl_excl is not null) then
333 return(delete_rule_line(ruleline_id, appid, flex_code,
334 flex_num, not_incl_excl) AND
335 insert_rule_line(ruleline_id, appid, flex_code, flex_num,
336 rule_name, incl_excl, enab_flag, create_by,
337 create_date, update_date, update_by,
338 update_login, catsegs_low, catsegs_high));
339 end if;
340
341 -- If we did not update the include_exclude indicator, then just
342 -- update the existing row in the include or exclude table.
343 --
344 if((get_segdelim(appid, flex_code, flex_num, sepchar) = FALSE) or
345 (get_segcount(appid, flex_code, flex_num, nsegs) = FALSE) or
346 (break_segs(catsegs_low, sepchar, nsegs, lo) = FALSE) or
347 (break_segs(catsegs_high, sepchar, nsegs, hi) = FALSE)) then
348 return(FALSE);
349 end if;
350
351 if(incl_excl = 'I') then
352 UPDATE fnd_flex_include_rule_lines
353 SET application_id = appid, id_flex_code = flex_code,
354 id_flex_num = flex_num, flex_validation_rule_name = rule_name,
355 enabled_flag = enab_flag, created_by = create_by,
356 creation_date = create_date, last_update_date = update_date,
357 last_updated_by = update_by, last_update_login = update_login,
358 SEGMENT1_LOW = lo(1), SEGMENT1_HIGH = hi(1),
359 SEGMENT2_LOW = lo(2), SEGMENT2_HIGH = hi(2),
360 SEGMENT3_LOW = lo(3), SEGMENT3_HIGH = hi(3),
361 SEGMENT4_LOW = lo(4), SEGMENT4_HIGH = hi(4),
362 SEGMENT5_LOW = lo(5), SEGMENT5_HIGH = hi(5),
363 SEGMENT6_LOW = lo(6), SEGMENT6_HIGH = hi(6),
364 SEGMENT7_LOW = lo(7), SEGMENT7_HIGH = hi(7),
365 SEGMENT8_LOW = lo(8), SEGMENT8_HIGH = hi(8),
366 SEGMENT9_LOW = lo(9), SEGMENT9_HIGH = hi(9),
367 SEGMENT10_LOW = lo(10), SEGMENT10_HIGH = hi(10),
368 SEGMENT11_LOW = lo(11), SEGMENT11_HIGH = hi(11),
369 SEGMENT12_LOW = lo(12), SEGMENT12_HIGH = hi(12),
373 SEGMENT16_LOW = lo(16), SEGMENT16_HIGH = hi(16),
370 SEGMENT13_LOW = lo(13), SEGMENT13_HIGH = hi(13),
371 SEGMENT14_LOW = lo(14), SEGMENT14_HIGH = hi(14),
372 SEGMENT15_LOW = lo(15), SEGMENT15_HIGH = hi(15),
374 SEGMENT17_LOW = lo(17), SEGMENT17_HIGH = hi(17),
375 SEGMENT18_LOW = lo(18), SEGMENT18_HIGH = hi(18),
376 SEGMENT19_LOW = lo(19), SEGMENT19_HIGH = hi(19),
377 SEGMENT20_LOW = lo(20), SEGMENT20_HIGH = hi(20),
378 SEGMENT21_LOW = lo(21), SEGMENT21_HIGH = hi(21),
379 SEGMENT22_LOW = lo(22), SEGMENT22_HIGH = hi(22),
380 SEGMENT23_LOW = lo(23), SEGMENT23_HIGH = hi(23),
381 SEGMENT24_LOW = lo(24), SEGMENT24_HIGH = hi(24),
382 SEGMENT25_LOW = lo(25), SEGMENT25_HIGH = hi(25),
383 SEGMENT26_LOW = lo(26), SEGMENT26_HIGH = hi(26),
384 SEGMENT27_LOW = lo(27), SEGMENT27_HIGH = hi(27),
385 SEGMENT28_LOW = lo(28), SEGMENT28_HIGH = hi(28),
386 SEGMENT29_LOW = lo(29), SEGMENT29_HIGH = hi(29),
387 SEGMENT30_LOW = lo(30), SEGMENT30_HIGH = hi(30)
388 WHERE rule_line_id = ruleline_id;
389 else
390 UPDATE fnd_flex_exclude_rule_lines
391 SET application_id = appid, id_flex_code = flex_code,
392 id_flex_num = flex_num, flex_validation_rule_name = rule_name,
393 enabled_flag = enab_flag, created_by = create_by,
394 creation_date = create_date, last_update_date = update_date,
395 last_updated_by = update_by, last_update_login = update_login,
396 SEGMENT1_LOW = lo(1), SEGMENT1_HIGH = hi(1),
397 SEGMENT2_LOW = lo(2), SEGMENT2_HIGH = hi(2),
398 SEGMENT3_LOW = lo(3), SEGMENT3_HIGH = hi(3),
399 SEGMENT4_LOW = lo(4), SEGMENT4_HIGH = hi(4),
400 SEGMENT5_LOW = lo(5), SEGMENT5_HIGH = hi(5),
401 SEGMENT6_LOW = lo(6), SEGMENT6_HIGH = hi(6),
402 SEGMENT7_LOW = lo(7), SEGMENT7_HIGH = hi(7),
403 SEGMENT8_LOW = lo(8), SEGMENT8_HIGH = hi(8),
404 SEGMENT9_LOW = lo(9), SEGMENT9_HIGH = hi(9),
405 SEGMENT10_LOW = lo(10), SEGMENT10_HIGH = hi(10),
406 SEGMENT11_LOW = lo(11), SEGMENT11_HIGH = hi(11),
407 SEGMENT12_LOW = lo(12), SEGMENT12_HIGH = hi(12),
408 SEGMENT13_LOW = lo(13), SEGMENT13_HIGH = hi(13),
409 SEGMENT14_LOW = lo(14), SEGMENT14_HIGH = hi(14),
410 SEGMENT15_LOW = lo(15), SEGMENT15_HIGH = hi(15),
411 SEGMENT16_LOW = lo(16), SEGMENT16_HIGH = hi(16),
412 SEGMENT17_LOW = lo(17), SEGMENT17_HIGH = hi(17),
413 SEGMENT18_LOW = lo(18), SEGMENT18_HIGH = hi(18),
414 SEGMENT19_LOW = lo(19), SEGMENT19_HIGH = hi(19),
415 SEGMENT20_LOW = lo(20), SEGMENT20_HIGH = hi(20),
416 SEGMENT21_LOW = lo(21), SEGMENT21_HIGH = hi(21),
417 SEGMENT22_LOW = lo(22), SEGMENT22_HIGH = hi(22),
418 SEGMENT23_LOW = lo(23), SEGMENT23_HIGH = hi(23),
419 SEGMENT24_LOW = lo(24), SEGMENT24_HIGH = hi(24),
420 SEGMENT25_LOW = lo(25), SEGMENT25_HIGH = hi(25),
421 SEGMENT26_LOW = lo(26), SEGMENT26_HIGH = hi(26),
422 SEGMENT27_LOW = lo(27), SEGMENT27_HIGH = hi(27),
423 SEGMENT28_LOW = lo(28), SEGMENT28_HIGH = hi(28),
424 SEGMENT29_LOW = lo(29), SEGMENT29_HIGH = hi(29),
425 SEGMENT30_LOW = lo(30), SEGMENT30_HIGH = hi(30)
426 WHERE rule_line_id = ruleline_id;
427 end if;
428
429 return(update_cvr_stats(appid, flex_code, flex_num, 0, 0, 0));
430
431 EXCEPTION
432 WHEN OTHERS then
433 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
434 FND_MESSAGE.set_token('MSG', 'update_rule_line() exception: '||SQLERRM);
435 return(FALSE);
436
437 END update_rule_line;
438
439 /* ----------------------------------------------------------------------- */
440 /* Gets the segment delimiter for the given flexfield structure. */
441 /* Returns TRUE on success, or FALSE and sets FND_MESSAGE if error. */
442 /* ----------------------------------------------------------------------- */
443 FUNCTION get_segdelim(appid IN NUMBER,
444 flex_code IN VARCHAR2,
445 flex_num IN NUMBER,
446 delimiter OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
447 BEGIN
448 SELECT concatenated_segment_delimiter INTO delimiter
449 FROM fnd_id_flex_structures WHERE application_id = appid
450 AND id_flex_code = flex_code AND id_flex_num = flex_num;
451 return(TRUE);
452
453 EXCEPTION
454 WHEN NO_DATA_FOUND then
455 FND_MESSAGE.set_name('FND', 'FLEX-CANNOT FIND STRUCT DEF');
456 FND_MESSAGE.set_token('ROUTINE', 'FND_FLEX_SERVER.GET_SEGDELIM');
457 FND_MESSAGE.set_token('APPL', to_char(appid));
458 FND_MESSAGE.set_token('CODE', flex_code);
459 FND_MESSAGE.set_token('NUM', to_char(flex_num));
460 return(FALSE);
461 WHEN TOO_MANY_ROWS then
462 FND_MESSAGE.set_name('FND', 'FLEX-DUPLICATE STRUCT DEF');
463 FND_MESSAGE.set_token('ROUTINE', 'FND_FLEX_SERVER.GET_SEGDELIM');
464 FND_MESSAGE.set_token('APPL', to_char(appid));
465 FND_MESSAGE.set_token('CODE', flex_code);
466 FND_MESSAGE.set_token('NUM', to_char(flex_num));
467 return(FALSE);
468 WHEN OTHERS then
469 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
470 FND_MESSAGE.set_token('MSG', 'get_segdelim() exception: ' || SQLERRM);
471 return(FALSE);
472 END get_segdelim;
473
474 /* ----------------------------------------------------------------------- */
475 /* Gets the number of enabled segments for the given flexfield. */
476 /* Returns TRUE on success, or FALSE and sets FND_MESSAGE if error. */
477 /* ----------------------------------------------------------------------- */
478 FUNCTION get_segcount(appid IN NUMBER,
479 flex_code IN VARCHAR2,
480 flex_num IN NUMBER,
481 nenabled OUT NOCOPY NUMBER) RETURN BOOLEAN IS
482 BEGIN
483 SELECT count(segment_num) INTO nenabled
484 FROM fnd_id_flex_segments
485 WHERE application_id = appid AND id_flex_code = flex_code
486 AND id_flex_num = flex_num AND enabled_flag = 'Y';
487 return(TRUE);
488 EXCEPTION
489 WHEN OTHERS then
490 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
491 FND_MESSAGE.set_token('MSG', 'get_segcount() exception: ' || SQLERRM);
492 return(FALSE);
493 END get_segcount;
494
495 /* ----------------------------------------------------------------------- */
496 /* Uses the segment delimiter to break up segments into array of */
497 /* exactly 30 segments, some of which are null. */
498 /* Assumes segment delimiter has not been substituted by a newline */
499 /* if only one segment is expected. */
500 /* Returns TRUE on success, or FALSE and sets FND_MESSAGE if error. */
501 /* ----------------------------------------------------------------------- */
502 FUNCTION break_segs(catsegs IN VARCHAR2,
503 sepchar IN VARCHAR2,
504 nexpect IN NUMBER,
505 segs OUT NOCOPY FND_FLEX_SERVER1.StringArray)
506 RETURN BOOLEAN IS
507 n_segs NUMBER;
508 BEGIN
509 -- Do not call to_stringarray if only one segment input because that
510 -- function will substitute the delimiter with newline.
511 --
512 if(nexpect = 1) then
513 segs(1) := catsegs;
514 n_segs := 2;
515 else
516 n_segs := FND_FLEX_SERVER1.to_stringarray(catsegs, sepchar, segs) + 1;
517 end if;
518 for i in n_segs..30 loop
519 segs(i) := NULL;
520 end loop;
521 return(TRUE);
522 EXCEPTION
523 WHEN OTHERS then
524 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
525 FND_MESSAGE.set_token('MSG', 'break_segs() exception: ' || SQLERRM);
526 return(FALSE);
527 END break_segs;
528
529 /* ----------------------------------------------------------------------- */
530
531 END fnd_flex_trigger;