[Home] [Help]
PACKAGE BODY: APPS.CSR_WIN_PROMIS_PKG
Source
1 package body CSR_WIN_PROMIS_PKG as
2 /*$Header: CSRSIPWB.pls 120.1 2006/03/30 21:13:00 venjayar noship $
3 +========================================================================+
4 | Copyright (c) 1999 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +========================================================================+
8 Name
9 ----
10 CSR_WIN_PROMIS_PKG
11
12 Purpose
13 -------
14 Insert, update, delete or lock tables belonging to view CSR_WIN_PROMIS_VL:
15 - base table CSR_WIN_PROMIS_ALL_B, and
16 - translation table CSR_WIN_PROMIS_ALL_TL.
17 Check uniqueness of columns NAME and START/END_TIME combinations. Restore
18 data integrity to a corrupted base/translation pair.
19
20 History
21 -------
22 10-DEC-1999 E.Kerkhoven First creation
23 3-JAN-2000 M. van Teeseling Translate_row and load_row added
24 13-NOV-2002 J. van Grondelle Bug 2664009.
25 Added NOCOPY hint to procedure
26 out-parameters.
27 +========================================================================+
28 */
29 procedure check_unique
30 (
31 p_win_promis_id IN varchar2
32 , p_name IN varchar2
33 , p_start_time IN date
34 , p_end_time IN date
35 )
36 is
37 cursor c_name
38 is
39 select ''
40 from csr_win_promis_all_tl
41 where ( p_win_promis_id is null
42 or win_promis_id <> p_win_promis_id )
43 and userenv('LANG') in (language, source_lang)
44 and upper(name) = upper(p_name);
45
46 cursor c_time
47 is
48 select ''
49 from csr_win_promis_all_b
50 where ( p_win_promis_id is null
51 or win_promis_id <> p_win_promis_id )
52 and ( to_char(start_time,'hh24:mi') = to_char(p_start_time,'hh24:mi')
53 and to_char(end_time,'hh24:mi') = to_char(p_end_time,'hh24:mi') );
54
55 l_check number;
56 begin
57 open c_name;
58 fetch c_name into l_check;
59 if c_name%found
60 then
61 close c_name;
62 fnd_message.set_name('CSR','PARS_PROM_NAME_NOT_UNIQUE');
63 fnd_message.set_token('NAME',p_name);
64 app_exception.raise_exception;
65 end if;
66 close c_name;
67
68 open c_time;
69 fetch c_time into l_check;
70 if c_time%found
71 then
72 close c_time;
73 fnd_message.set_name('CSR','PARS_PROM_TIME_NOT_UNIQUE');
74 fnd_message.set_token('START',to_char(p_start_time,'hh24:mi'));
75 fnd_message.set_token('END' ,to_char(p_end_time,'hh24:mi'));
76 app_exception.raise_exception;
77 end if;
78 close c_time;
79 end check_unique;
80
81 procedure insert_row
82 (
83 p_row_id IN OUT NOCOPY varchar2
84 , p_win_promis_id IN OUT NOCOPY number
85 , p_name IN varchar2
86 , p_description IN varchar2
87 , p_start_time IN date
88 , p_end_time IN date
89 , p_created_by IN OUT NOCOPY number
90 , p_creation_date IN OUT NOCOPY date
91 , p_last_updated_by IN OUT NOCOPY number
92 , p_last_update_date IN OUT NOCOPY date
93 , p_last_update_login IN OUT NOCOPY number
94 , p_attribute1 IN varchar2
95 , p_attribute2 IN varchar2
96 , p_attribute3 IN varchar2
97 , p_attribute4 IN varchar2
98 , p_attribute5 IN varchar2
99 , p_attribute6 IN varchar2
100 , p_attribute7 IN varchar2
101 , p_attribute8 IN varchar2
102 , p_attribute9 IN varchar2
103 , p_attribute10 IN varchar2
104 , p_attribute11 IN varchar2
105 , p_attribute12 IN varchar2
106 , p_attribute13 IN varchar2
107 , p_attribute14 IN varchar2
108 , p_attribute15 IN varchar2
109 , p_attribute_category IN varchar2
110 , p_org_id IN number
111 )
112 is
113 cursor c_prom ( p_prom_id number )
114 is
115 select row_id
116 from csr_win_promis_vl
117 where win_promis_id = p_prom_id;
118
119 begin
120
121 if p_win_promis_id is null then
122 select csr_win_promis_all_b_s1.nextval
123 into p_win_promis_id
124 from dual;
125 end if;
126
127 if p_created_by is null then
128 p_created_by := fnd_global.user_id;
129 end if;
130
131 if p_last_updated_by is null then
132 p_last_updated_by := fnd_global.user_id;
133 end if;
134
135 if p_last_update_login is null then
136 p_last_update_login := fnd_global.login_id;
137 end if;
138
139 if p_creation_date is null then
140 p_creation_date := sysdate;
141 end if;
142
143 if p_last_update_date is null then
144 p_last_update_date := sysdate;
145 end if;
146
147 insert into csr_win_promis_all_b
148 (
149 win_promis_id
150 , start_time
151 , end_time
152 , created_by
153 , creation_date
154 , last_updated_by
155 , last_update_date
156 , last_update_login
157 , attribute1
158 , attribute2
159 , attribute3
160 , attribute4
161 , attribute5
162 , attribute6
163 , attribute7
164 , attribute8
165 , attribute9
166 , attribute10
167 , attribute11
168 , attribute12
169 , attribute13
170 , attribute14
171 , attribute15
172 , attribute_category
173 , org_id
174 )
175 values
176 (
177 p_win_promis_id
178 , p_start_time
179 , p_end_time
180 , p_created_by
181 , p_creation_date
182 , p_last_updated_by
183 , p_last_update_date
184 , p_last_update_login
185 , p_attribute1
186 , p_attribute2
187 , p_attribute3
188 , p_attribute4
189 , p_attribute5
190 , p_attribute6
191 , p_attribute7
192 , p_attribute8
193 , p_attribute9
194 , p_attribute10
195 , p_attribute11
196 , p_attribute12
197 , p_attribute13
198 , p_attribute14
199 , p_attribute15
200 , p_attribute_category
201 , p_org_id
202 );
203
204 insert into csr_win_promis_all_tl
205 (
206 win_promis_id
207 , name
208 , description
209 , created_by
210 , creation_date
211 , last_updated_by
212 , last_update_date
213 , last_update_login
214 , language
215 , source_lang
216 )
217 select p_win_promis_id
218 , p_name
219 , p_description
220 , p_created_by
221 , p_creation_date
222 , p_last_updated_by
223 , p_last_update_date
224 , p_last_update_login
225 , l.language_code
226 , userenv('LANG')
227 from fnd_languages l
228 where l.installed_flag in ('I','B')
229 and not exists
230 ( select ''
231 from csr_win_promis_all_tl t
232 where t.win_promis_id = p_win_promis_id
233 and t.language = l.language_code );
234
235 open c_prom ( p_win_promis_id );
236 fetch c_prom into p_row_id;
237 if c_prom%notfound
238 then
239 close c_prom;
240 raise NO_DATA_FOUND;
241 end if;
242 close c_prom;
243 end insert_row;
244
245 procedure lock_row
246 (
247 p_win_promis_id IN number
248 , p_name IN varchar2
249 , p_description IN varchar2
250 , p_start_time IN date
251 , p_end_time IN date
252 , p_attribute1 IN varchar2
253 , p_attribute2 IN varchar2
254 , p_attribute3 IN varchar2
255 , p_attribute4 IN varchar2
256 , p_attribute5 IN varchar2
257 , p_attribute6 IN varchar2
258 , p_attribute7 IN varchar2
259 , p_attribute8 IN varchar2
260 , p_attribute9 IN varchar2
261 , p_attribute10 IN varchar2
262 , p_attribute11 IN varchar2
263 , p_attribute12 IN varchar2
264 , p_attribute13 IN varchar2
265 , p_attribute14 IN varchar2
266 , p_attribute15 IN varchar2
267 , p_attribute_category IN varchar2
268 )
269 is
270 cursor c_prom
271 is
272 select *
273 from csr_win_promis_vl
274 where win_promis_id = p_win_promis_id
275 for update nowait;
276
277 l_rec c_prom%rowtype;
278
279 begin
280 open c_prom;
281 fetch c_prom into l_rec;
282
283 if c_prom%notfound
284 then
285 close c_prom;
286 fnd_message.set_name('FND','FORM_RECORD_DELETED');
287 app_exception.raise_exception;
288 end if;
289 close c_prom;
290
291 if to_char(l_rec.start_time,'hh24 mi') <> to_char(p_start_time,'hh24 mi')
292 or to_char(l_rec.end_time ,'hh24 mi') <> to_char(p_end_time ,'hh24 mi')
293 or l_rec.name <> rtrim(p_name)
294 or not csr_utilities.compare_values(rtrim(p_description),l_rec.description)
295 or not csr_utilities.compare_values(rtrim(p_attribute1),l_rec.attribute1)
296 or not csr_utilities.compare_values(rtrim(p_attribute2),l_rec.attribute2)
297 or not csr_utilities.compare_values(rtrim(p_attribute3),l_rec.attribute3)
298 or not csr_utilities.compare_values(rtrim(p_attribute4),l_rec.attribute4)
299 or not csr_utilities.compare_values(rtrim(p_attribute5),l_rec.attribute5)
300 or not csr_utilities.compare_values(rtrim(p_attribute6),l_rec.attribute6)
301 or not csr_utilities.compare_values(rtrim(p_attribute7),l_rec.attribute7)
302 or not csr_utilities.compare_values(rtrim(p_attribute8),l_rec.attribute8)
303 or not csr_utilities.compare_values(rtrim(p_attribute9),l_rec.attribute9)
304 or not csr_utilities.compare_values(rtrim(p_attribute10),l_rec.attribute10)
305 or not csr_utilities.compare_values(rtrim(p_attribute11),l_rec.attribute11)
306 or not csr_utilities.compare_values(rtrim(p_attribute12),l_rec.attribute12)
307 or not csr_utilities.compare_values(rtrim(p_attribute13),l_rec.attribute13)
308 or not csr_utilities.compare_values(rtrim(p_attribute14),l_rec.attribute14)
309 or not csr_utilities.compare_values(rtrim(p_attribute15),l_rec.attribute15)
310 or not csr_utilities.compare_values(rtrim(p_attribute_category),
311 l_rec.attribute_category)
312 then
313 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
314 app_exception.raise_exception;
315 end if;
316 end lock_row;
317
318 procedure update_row
319 (
320 p_win_promis_id IN number
321 , p_name IN varchar2
322 , p_description IN varchar2
323 , p_start_time IN date
324 , p_end_time IN date
325 , p_last_updated_by IN OUT NOCOPY number
326 , p_last_update_date IN OUT NOCOPY date
327 , p_last_update_login IN OUT NOCOPY number
328 , p_attribute1 IN varchar2
329 , p_attribute2 IN varchar2
330 , p_attribute3 IN varchar2
331 , p_attribute4 IN varchar2
332 , p_attribute5 IN varchar2
333 , p_attribute6 IN varchar2
334 , p_attribute7 IN varchar2
335 , p_attribute8 IN varchar2
336 , p_attribute9 IN varchar2
337 , p_attribute10 IN varchar2
338 , p_attribute11 IN varchar2
339 , p_attribute12 IN varchar2
340 , p_attribute13 IN varchar2
341 , p_attribute14 IN varchar2
342 , p_attribute15 IN varchar2
343 , p_attribute_category IN varchar2
344 )
345 is
346 begin
347
348 if p_last_updated_by is null then
349 p_last_updated_by := fnd_global.user_id;
350 end if;
351
352 if p_last_update_login is null then
353 p_last_update_login := fnd_global.login_id;
354 end if;
355
356 if p_last_update_date is null then
357 p_last_update_date := sysdate;
358 end if;
359
360 update csr_win_promis_all_b
361 set start_time = p_start_time
362 , end_time = p_end_time
363 , last_update_date = p_last_update_date
364 , last_updated_by = p_last_updated_by
365 , last_update_login = p_last_update_login
366 , attribute1 = p_attribute1
367 , attribute2 = p_attribute2
368 , attribute3 = p_attribute3
369 , attribute4 = p_attribute4
370 , attribute5 = p_attribute5
371 , attribute6 = p_attribute6
372 , attribute7 = p_attribute7
373 , attribute8 = p_attribute8
374 , attribute9 = p_attribute9
375 , attribute10 = p_attribute10
376 , attribute11 = p_attribute11
377 , attribute12 = p_attribute12
378 , attribute13 = p_attribute13
379 , attribute14 = p_attribute14
380 , attribute15 = p_attribute15
381 , attribute_category = p_attribute_category
382 where win_promis_id = p_win_promis_id;
383
384 if sql%notfound
385 then
386 raise NO_DATA_FOUND;
387 end if;
388
389 update csr_win_promis_all_tl
390 set name = p_name
391 , description = p_description
392 , last_update_date = p_last_update_date
393 , last_updated_by = p_last_updated_by
394 , last_update_login = p_last_update_login
395 , source_lang = userenv('lang')
396 where win_promis_id = p_win_promis_id
397 and userenv('lang') in (language, source_lang);
398
399 if sql%notfound
400 then
401 raise NO_DATA_FOUND;
402 end if;
403 end update_row;
404
405 procedure delete_row
406 (
407 p_win_promis_id IN number
408 )
409 is
410 begin
411 delete from csr_win_promis_all_tl
412 where win_promis_id = p_win_promis_id;
413
414 if sql%notfound
415 then
416 raise NO_DATA_FOUND;
417 end if;
418
419 delete from csr_win_promis_all_b
420 where win_promis_id = p_win_promis_id;
421
422 if sql%notfound
423 then
424 raise NO_DATA_FOUND;
425 end if;
426 end delete_row;
427
428 procedure add_language
429 is
430 begin
431 delete from csr_win_promis_all_tl t
432 where not exists
433 ( select ''
434 from csr_win_promis_all_b b
435 where b.win_promis_id = t.win_promis_id );
436
437 update csr_win_promis_all_tl t
438 set ( name, description ) =
439 ( select b.name
440 , b.description
441 from csr_win_promis_all_tl b
442 where b.win_promis_id = t.win_promis_id
443 and b.language = t.source_lang )
444 where ( t.win_promis_id, t.language ) in
445 ( select subt.win_promis_id
446 , subt.language
447 from csr_win_promis_all_tl subb
448 , csr_win_promis_all_tl subt
449 where subb.win_promis_id = subt.win_promis_id
450 and subb.language = subt.source_lang
454 and subt.description is not null )
451 and ( subb.name <> subt.name
452 or subb.description <> subt.description
453 or ( subb.description is null
455 or ( subb.description is not null
456 and subt.description is null ) ) );
457
458 insert into csr_win_promis_all_tl
459 ( win_promis_id
460 , name
461 , description
462 , created_by
463 , creation_date
464 , last_updated_by
465 , last_update_date
466 , last_update_login
467 , language
468 , source_lang
469 )
470 select b.win_promis_id
471 , b.name
472 , b.description
473 , b.created_by
474 , b.creation_date
475 , b.last_updated_by
476 , b.last_update_date
477 , b.last_update_login
478 , l.language_code
479 , b.source_lang
480 from csr_win_promis_all_tl b
481 , fnd_languages l
482 where l.installed_flag in ('I', 'B')
483 and b.language = userenv('LANG')
484 and not exists
485 ( select null
486 from csr_win_promis_all_tl t
487 where t.win_promis_id = b.win_promis_id
488 and t.language = l.language_code );
489 end add_language;
490
491 procedure translate_row
492 (
493 p_win_promis_id IN varchar2
494 , p_name IN varchar2
495 , p_description IN varchar2
496 , p_owner IN varchar2
497 )
498 is
499 begin
500 update CSR_WIN_PROMIS_ALL_TL
501 set name = p_name,
502 description = p_description,
503 last_update_date = sysdate,
504 last_updated_by = decode(p_owner, 'SEED', 1, 0),
505 last_update_login = 0,
506 source_lang = userenv('LANG')
507 where win_promis_id = to_number(p_win_promis_id)
508 and userenv('LANG') in (language, source_lang);
509 end translate_row;
510
511 procedure load_row
512 (
513 p_win_promis_id IN varchar2
514 , p_name IN varchar2
515 , p_description IN varchar2
516 , p_start_time IN varchar2
517 , p_end_time IN varchar2
518 , p_owner IN varchar2
519 , p_attribute1 IN varchar2
520 , p_attribute2 IN varchar2
521 , p_attribute3 IN varchar2
522 , p_attribute4 IN varchar2
523 , p_attribute5 IN varchar2
524 , p_attribute6 IN varchar2
525 , p_attribute7 IN varchar2
526 , p_attribute8 IN varchar2
527 , p_attribute9 IN varchar2
528 , p_attribute10 IN varchar2
529 , p_attribute11 IN varchar2
530 , p_attribute12 IN varchar2
531 , p_attribute13 IN varchar2
532 , p_attribute14 IN varchar2
533 , p_attribute15 IN varchar2
534 , p_attribute_category IN varchar2
535 , p_org_id IN varchar2
536 )
537 is
538 l_win_promis_id number := to_number(p_win_promis_id);
539 l_update_date date := sysdate;
540 l_row_id varchar2(64);
541 l_user_id number := 0;
542 begin
543 if (p_owner = 'SEED')
544 then
545 l_user_id := 1;
546 end if;
547
548 update_row
549 (
550 p_win_promis_id => l_win_promis_id
551 , p_name => p_name
552 , p_description => p_description
553 , p_start_time => to_date(p_start_time, 'HH24:MI')
554 , p_end_time => to_date(p_end_time, 'HH24:MI')
555 , p_last_updated_by => l_user_id
556 , p_last_update_date => l_update_date
557 , p_last_update_login => l_user_id
558 , p_attribute1 => p_attribute1
559 , p_attribute2 => p_attribute2
560 , p_attribute3 => p_attribute3
561 , p_attribute4 => p_attribute4
562 , p_attribute5 => p_attribute5
563 , p_attribute6 => p_attribute6
564 , p_attribute7 => p_attribute7
565 , p_attribute8 => p_attribute8
566 , p_attribute9 => p_attribute9
567 , p_attribute10 => p_attribute10
568 , p_attribute11 => p_attribute11
569 , p_attribute12 => p_attribute12
570 , p_attribute13 => p_attribute13
571 , p_attribute14 => p_attribute14
572 , p_attribute15 => p_attribute15
573 , p_attribute_category => p_attribute_category
574 );
575 exception
576 when NO_DATA_FOUND then
577 insert_row
578 (
579 p_row_id => l_row_id
580 , p_win_promis_id => l_win_promis_id
581 , p_name => p_name
582 , p_description => p_description
583 , p_start_time => to_date(p_start_time, 'HH24:MI')
584 , p_end_time => to_date(p_end_time, 'HH24:MI')
585 , p_created_by => l_user_id
586 , p_creation_date => l_update_date
587 , p_last_updated_by => l_user_id
588 , p_last_update_date => l_update_date
589 , p_last_update_login => l_user_id
590 , p_attribute1 => p_attribute1
591 , p_attribute2 => p_attribute2
592 , p_attribute3 => p_attribute3
593 , p_attribute4 => p_attribute4
594 , p_attribute5 => p_attribute5
595 , p_attribute6 => p_attribute6
596 , p_attribute7 => p_attribute7
597 , p_attribute8 => p_attribute8
598 , p_attribute9 => p_attribute9
599 , p_attribute10 => p_attribute10
600 , p_attribute11 => p_attribute11
601 , p_attribute12 => p_attribute12
602 , p_attribute13 => p_attribute13
603 , p_attribute14 => p_attribute14
604 , p_attribute15 => p_attribute15
605 , p_attribute_category => p_attribute_category
606 , p_org_id => to_number(p_org_id)
607 );
608 end load_row;
609
610 end CSR_WIN_PROMIS_PKG;