[Home] [Help]
PACKAGE BODY: APPS.OE_PC_RSETS_PKG
Source
1 PACKAGE BODY OE_PC_RSETS_PKG AS
2 /* $Header: OEXPCRDB.pls 120.1 2005/07/14 06:12:29 appldev ship $ */
3
4
5 PROCEDURE Insert_Row(
6 x_rowid in out NOCOPY /* file.sql.39 change */ varchar2
7 ,x_record_set_id in out NOCOPY /* file.sql.39 change */ number
8 ,x_entity_id in number
9 ,x_record_set_short_name in varchar2
10 ,x_pk_record_set_flag in varchar2
11 ,x_record_set_display_name in varchar2
12 ,x_record_set_description in varchar2
13 ,x_system_flag in varchar2
14 ,x_created_by in number
15 ,x_creation_date in date
16 ,x_last_updated_by in number
17 ,x_last_update_date in date
18 ,x_last_update_login in number
19 ,x_attribute_category in varchar2
20 ,x_attribute1 in varchar2
21 ,x_attribute2 in varchar2
22 ,x_attribute3 in varchar2
23 ,x_attribute4 in varchar2
24 ,x_attribute5 in varchar2
25 ,x_attribute6 in varchar2
26 ,x_attribute7 in varchar2
27 ,x_attribute8 in varchar2
28 ,x_attribute9 in varchar2
29 ,x_attribute10 in varchar2
30 ,x_attribute11 in varchar2
31 ,x_attribute12 in varchar2
32 ,x_attribute13 in varchar2
33 ,x_attribute14 in varchar2
34 ,x_attribute15 in varchar2
35 )
36 Is
37 CURSOR CROWID IS SELECT rowid FROM oe_pc_rsets
38 WHERE record_set_id = x_record_set_id;
39
40 CURSOR CID IS SELECT oe_pc_rsets_s.nextval
41 FROM sys.dual;
42 Begin
43
44 IF x_record_set_id IS NULL THEN
45 Open CID;
46 Fetch CID into x_record_set_id;
47 if (CID%NOTFOUND) then
48 CLOSE CID;
49 RAISE NO_DATA_FOUND;
50 end if;
51 Close CID;
52 END IF;
53
54 INSERT INTO oe_pc_rsets (
55 record_set_id
56 ,entity_id
57 ,record_set_short_name
58 ,pk_record_set_flag
59 ,system_flag
60 ,created_by
61 ,creation_date
62 ,last_updated_by
63 ,last_update_date
64 ,last_update_login
65 ,attribute_category
66 ,attribute1
67 ,attribute2
68 ,attribute3
69 ,attribute4
70 ,attribute5
71 ,attribute6
72 ,attribute7
73 ,attribute8
74 ,attribute9
75 ,attribute10
76 ,attribute11
77 ,attribute12
78 ,attribute13
79 ,attribute14
80 ,attribute15
81 )
82 Values
83 (
84 x_record_set_id
85 ,x_entity_id
86 ,x_record_set_short_name
87 ,x_pk_record_set_flag
88 ,x_system_flag
89 ,x_created_by
90 ,x_creation_date
91 ,x_last_updated_by
92 ,x_last_update_date
93 ,x_last_update_login
94 ,x_attribute_category
95 ,x_attribute1
96 ,x_attribute2
97 ,x_attribute3
98 ,x_attribute4
99 ,x_attribute5
100 ,x_attribute6
101 ,x_attribute7
102 ,x_attribute8
103 ,x_attribute9
104 ,x_attribute10
105 ,x_attribute11
106 ,x_attribute12
107 ,x_attribute13
108 ,x_attribute14
109 ,x_attribute15
110 );
111
112
113 INSERT INTO oe_pc_rsets_tl (
114 record_set_id
115 ,language
116 ,source_lang
117 ,created_by
118 ,creation_date
119 ,last_updated_by
120 ,last_update_date
121 ,display_name
122 ,description
123 ,last_update_login
124 )
125 SELECT
126 x_record_set_id
127 ,l.language_code
128 ,USERENV('LANG')
129 ,x_created_by
130 ,x_creation_date
131 ,x_last_updated_by
132 ,x_last_update_date
133 ,x_record_set_display_name
134 ,x_record_set_description
135 ,x_last_update_login
136 FROM fnd_languages l
137 WHERE l.installed_flag in ('I', 'B')
138 AND not exists (
139 SELECT null
140 FROM oe_pc_rsets_tl t
141 WHERE t.record_set_id = x_record_set_id
142 AND t.language = l.language_code);
143
144 Open CROWID;
145 Fetch CROWID into x_rowid;
146 if (CROWID%NOTFOUND) then
147 CLOSE CROWID;
148 RAISE NO_DATA_FOUND;
149 end if;
150 CLOSE CROWID;
151
152 End Insert_Row;
153 ------------------------------------------
154 PROCEDURE Lock_Row(
155 x_rowid in varchar2
156 ,x_record_set_id in number
157 ,x_entity_id in number
158 ,x_record_set_short_name in varchar2
159 ,x_pk_record_set_flag in varchar2
160 ,x_record_set_display_name in varchar2
161 ,x_record_set_description in varchar2
162 ,x_system_flag in varchar2
163 ,x_created_by in number
164 ,x_creation_date in date
165 ,x_last_updated_by in number
166 ,x_last_update_date in date
167 ,x_last_update_login in number
168 ,x_attribute_category in varchar2
169 ,x_attribute1 in varchar2
170 ,x_attribute2 in varchar2
171 ,x_attribute3 in varchar2
172 ,x_attribute4 in varchar2
173 ,x_attribute5 in varchar2
174 ,x_attribute6 in varchar2
175 ,x_attribute7 in varchar2
176 ,x_attribute8 in varchar2
177 ,x_attribute9 in varchar2
178 ,x_attribute10 in varchar2
179 ,x_attribute11 in varchar2
180 ,x_attribute12 in varchar2
181 ,x_attribute13 in varchar2
182 ,x_attribute14 in varchar2
183 ,x_attribute15 in varchar2
184 )
185 Is
186 CURSOR C IS
187 SELECT *
188 FROM oe_pc_rsets
189 WHERE rowid = x_rowid
190 FOR UPDATE OF record_set_id NOWAIT;
191
192 Recinfo C%ROWTYPE;
193
194 CURSOR C1 IS
195 SELECT *
196 FROM oe_pc_rsets_tl t
197 WHERE record_set_id = x_record_set_id
198 AND language = userenv('LANG')
199 FOR UPDATE OF record_set_id NOWAIT;
200
201 tlinfo C1%ROWTYPE;
202
203 Begin
204 Open C;
205 Fetch C into Recinfo;
206 if (C%NOTFOUND) then
207 Close C;
208 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
209 APP_EXCEPTION.Raise_Exception;
210 end if;
214 AND (Recinfo.entity_id = x_entity_id)
211 Close C;
212 if (
213 (Recinfo.record_set_id = x_record_set_id)
215 AND (rtrim(Recinfo.record_set_short_name) = x_record_set_short_name)
216 AND (Recinfo.pk_record_set_flag = x_pk_record_set_flag)
217 AND (Recinfo.system_flag = x_system_flag)
218 AND (Recinfo.created_by = x_created_by)
219 AND (Recinfo.creation_date = x_creation_date)
220 AND (Recinfo.last_updated_by = x_last_updated_by)
221 AND (Recinfo.last_update_date = x_last_update_date)
222 AND ( (Recinfo.last_update_login = x_last_update_login)
223 OR ( (recinfo.last_update_login IS NULL)
224 AND(x_last_update_login IS NULL)))
225 ) then
226 return;
227 else
228 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
229 APP_EXCEPTION.Raise_Exception;
230 end if;
231
232 Open C1;
233 Fetch C1 into tlinfo;
234 if (C1%NOTFOUND) then
235 Close C1;
236 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
237 APP_EXCEPTION.Raise_Exception;
238 end if;
239 Close C1;
240
241 if (
242 (rtrim(tlinfo.display_name) = x_record_set_display_name)
243 AND (tlinfo.last_updated_by = x_last_updated_by)
244 AND (tlinfo.last_update_date = x_last_update_date)
245 AND ( (rtrim(tlinfo.description) = x_record_set_description)
246 OR ( (tlinfo.description IS NULL)
247 AND(x_record_set_description IS NULL)))
248 AND ( (tlinfo.last_update_login = x_last_update_login)
249 OR ( (tlinfo.last_update_login IS NULL)
250 AND(x_last_update_login IS NULL)))
251 ) then
252 return;
253 else
254 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
255 APP_EXCEPTION.Raise_Exception;
256 end if;
257
258 End Lock_Row;
259
260 --------------------------------------------------------------------
261 PROCEDURE Update_Row(
262 x_rowid in varchar2
263 ,x_record_set_id in number
264 ,x_entity_id in number
265 ,x_record_set_short_name in varchar2
266 ,x_pk_record_set_flag in varchar2
267 ,x_record_set_display_name in varchar2
268 ,x_record_set_description in varchar2
269 ,x_system_flag in varchar2
270 ,x_created_by in number
271 ,x_creation_date in date
272 ,x_last_updated_by in number
273 ,x_last_update_date in date
274 ,x_last_update_login in number
275 ,x_attribute_category in varchar2
276 ,x_attribute1 in varchar2
277 ,x_attribute2 in varchar2
278 ,x_attribute3 in varchar2
279 ,x_attribute4 in varchar2
280 ,x_attribute5 in varchar2
281 ,x_attribute6 in varchar2
282 ,x_attribute7 in varchar2
283 ,x_attribute8 in varchar2
284 ,x_attribute9 in varchar2
285 ,x_attribute10 in varchar2
286 ,x_attribute11 in varchar2
287 ,x_attribute12 in varchar2
288 ,x_attribute13 in varchar2
289 ,x_attribute14 in varchar2
290 ,x_attribute15 in varchar2
291 )
292 Is
293 Begin
294
295 UPDATE oe_pc_rsets
296 SET
297 record_set_id = x_record_set_id
298 ,entity_id = x_entity_id
299 ,record_set_short_name = x_record_set_short_name
300 ,pk_record_set_flag = x_pk_record_set_flag
301 ,system_flag = x_system_flag
302 ,created_by = x_created_by
303 ,creation_date = x_creation_date
304 ,last_updated_by = x_last_updated_by
305 ,last_update_date = x_last_update_date
306 ,last_update_login = x_last_update_login
307 ,attribute_category = x_attribute_category
308 ,attribute1 = x_attribute1
309 ,attribute2 = x_attribute2
310 ,attribute3 = x_attribute3
311 ,attribute4 = x_attribute4
312 ,attribute5 = x_attribute5
313 ,attribute6 = x_attribute6
314 ,attribute7 = x_attribute7
315 ,attribute8 = x_attribute8
316 ,attribute9 = x_attribute9
317 ,attribute10 = x_attribute10
318 ,attribute11 = x_attribute11
319 ,attribute12 = x_attribute12
320 ,attribute13 = x_attribute13
321 ,attribute14 = x_attribute14
322 ,attribute15 = x_attribute15
323 WHERE record_set_id = x_record_set_id;
324 if (SQL%NOTFOUND) then
325 RAISE NO_DATA_FOUND;
326 end if;
327
328
329 UPDATE oe_pc_rsets_tl
330 SET
331 source_lang = USERENV('LANG')
332 ,last_updated_by = x_last_updated_by
333 ,last_update_date = x_last_update_date
334 ,last_update_login = x_last_update_login
335 ,display_name = x_record_set_display_name
339
336 ,description = x_record_set_description
337 WHERE record_set_id = x_record_set_id
338 AND USERENV('LANG') in (language, source_lang);
340 if (SQL%NOTFOUND) then
341 RAISE NO_DATA_FOUND;
342 end if;
343 End Update_Row;
344 -------------------------------------------------
345
346 PROCEDURE Delete_Row(
347 x_record_set_id in number
348 )
349 Is
350 Begin
351
352
353 -- delete constraint rules if any
354 DELETE FROM OE_PC_RSET_SEL_COLS
355 WHERE record_set_id = x_record_set_id;
356
357 -- delete all the validation pkgs from oe_pc_validation_pkgs table
358 DELETE FROM OE_PC_VALIDATION_PKGS
359 WHERE record_set_id = x_record_set_id;
360
361 -- delete the tl table
362 DELETE FROM OE_PC_RSETS_TL
363 WHERE record_set_id = x_record_set_id;
364
365 DELETE FROM OE_PC_RSETS
366 WHERE record_set_id = x_record_set_id;
367
368 if (SQL%NOTFOUND) then
369 RAISE NO_DATA_FOUND;
370 end if;
371
372 End Delete_Row;
373 -------------------------------------------------------------------
374 PROCEDURE Add_Language
375 IS
376 Begin
377 DELETE FROM oe_pc_rsets_tl t
378 WHERE NOT EXISTS
379 (SELECT null
380 FROM oe_pc_rsets b
381 where record_set_id = t.record_set_id);
382
383 UPDATE oe_pc_rsets_tl t
384 SET
385 (
386 display_name,
387 description
388 ) = (
389 SELECT
390 b.display_name,
391 b.description
392 FROM oe_pc_rsets_tl b
393 WHERE b.record_set_id = t.record_set_id
394 AND b.language = t.source_lang
395 )
396 where
397 (
398 t.record_set_id,
399 t.language
400 ) IN (
401 SELECT
402 subt.record_set_id,
403 subt.language
404 FROM oe_pc_rsets_tl subb, oe_pc_rsets_tl subt
405 WHERE subb.record_set_id = subt.record_set_id
406 AND subb.language = subt.source_lang
407 AND(subb.display_name <> subt.display_name
408 OR subb.DESCRIPTION <> subt.description
409 OR (subb.description IS null AND subt.description IS NOT null)
410 OR (subb.description IS NOT null AND subt.description IS null)
411 )
412 );
413
414 INSERT INTO oe_pc_rsets_tl
415 (
416 record_set_id
417 ,language
418 ,source_lang
419 ,created_by
420 ,creation_date
421 ,last_updated_by
422 ,last_update_date
423 ,display_name
424 ,description
425 ,last_update_login
426 )
427 SELECT
428 b.record_set_id
429 ,l.language_code
430 ,b.source_lang -- bug 2329327
431 ,b.created_by
432 ,b.creation_date
433 ,b.last_updated_by
434 ,b.last_update_date
435 ,b.display_name
436 ,b.description
437 ,b.last_update_login
438 FROM oe_pc_rsets_tl b, fnd_languages l
439 WHERE l.installed_flag IN ('I', 'B')
440 AND b.language = USERENV('LANG')
441 AND NOT EXISTS
442 ( SELECT null
443 FROM oe_pc_rsets_tl t
444 WHERE t.record_set_id = b.record_set_id
445 AND t.language = l.language_code);
446 End Add_Language;
447 -------------------------------------------------------------------
448 PROCEDURE Check_References(
449 x_record_set_id in number
450 )
451 Is
452 dummy number;
453 Begin
454 SELECT 1
455 into dummy
456 FROM dual
457 WHERE NOT EXISTS
458 (SELECT 1
459 FROM oe_pc_conditions
460 WHERE record_set_id = x_record_set_id);
461 EXCEPTION
462 WHEN NO_DATA_FOUND THEN
463 fnd_message.set_name('ONT', 'OE_PC_RS_REF_EXISTS');
464 app_exception.raise_exception;
465 End Check_References;
466
467 -----------------------------------------------------------------------
468 PROCEDURE Check_Unique(
469 x_rowid in varchar2
470 ,x_entity_id in number
471 ,x_record_set_display_name in varchar2
472 ,x_record_set_short_name in varchar2
473 ,x_pk_record_set_flag in varchar2
474 ,x_validation_unit in varchar2
475 )
476 Is
477 dummy number;
478 chk_display_name boolean := FALSE;
479 chk_short_name boolean := FALSE;
480 chk_pk_flag boolean := FALSE;
481 Begin
482 if (x_validation_unit = 'ALL') then
483 chk_display_name := TRUE;
484 chk_short_name := TRUE;
485 chk_pk_flag := TRUE;
486 elsif (x_validation_unit = 'RECORD_SET_DISPLAY_NAME') then
487 chk_display_name := TRUE;
488 elsif (x_validation_unit = 'RECORD_SET_SHORT_NAME') then
489 chk_short_name := TRUE;
490 elsif (x_validation_unit = 'PK_RECORD_SET_FLAG') then
491 chk_pk_flag := TRUE;
492 end if;
493
494 if (chk_display_name = TRUE) then
495 -- record set short name should be unique within an entity
496 SELECT count(1)
497 INTO dummy
498 FROM oe_pc_rsets_vl
499 WHERE entity_id = x_entity_id
500 AND record_set_display_name = x_record_set_display_name
501 AND ((x_rowid IS null) OR (row_id <> x_rowid));
502
503 if (dummy >= 1) then
504 fnd_message.set_name('ONT', 'OE_PC_RS_DUP_DISPLAY_NAME');
505 app_exception.raise_exception;
506 end if;
507 end if;
508
509 if (chk_short_name = TRUE) then
510 -- record set short name should be unique within an entity
511 SELECT count(1)
512 INTO dummy
513 FROM oe_pc_rsets
514 WHERE entity_id = x_entity_id
515 AND record_set_short_name = x_record_set_short_name
516 AND ((x_rowid IS null) OR (rowid <> x_rowid));
517
518 if (dummy >= 1) then
519 fnd_message.set_name('ONT', 'OE_PC_RS_DUP_SHORT_NAME');
520 app_exception.raise_exception;
521 end if;
522 end if;
523 if (chk_pk_flag = TRUE) then
524 -- there can be only one record set with pk_record_set_flag = Y
525 SELECT count(1)
526 INTO dummy
527 FROM oe_pc_rsets
528 WHERE entity_id = x_entity_id
529 AND pk_record_set_flag = 'Y'
530 AND x_pk_record_set_flag = 'Y'
531 AND ((x_rowid IS null) OR (rowid <> x_rowid));
532
533 if (dummy >= 1) then
534 fnd_message.set_name('ONT', 'OE_PC_PK_RS_EXISTS');
535 app_exception.raise_exception;
536 end if;
537 end if;
538
539 End Check_Unique;
540 ------------------------------------------------------------------------
541
542 PROCEDURE Translate_Row(
543 x_record_set_id in varchar2
544 ,x_owner in varchar2
545 ,x_record_set_display_name in varchar2
546 ,x_record_set_description in varchar2
547 )
548 IS
549 l_user_id number :=0;
550 BEGIN
551 l_user_id := fnd_load_util.owner_id(x_owner); --Seed data changes
552 UPDATE oe_pc_rsets_tl
553 SET
554 source_lang = USERENV('LANG')
555 --,last_updated_by = decode(x_OWNER, 'SEED', 1, 0)
556 ,last_updated_by = l_user_id
557 ,last_update_date = sysdate
558 ,last_update_login = 0
559 ,display_name = x_record_set_display_name
560 ,description = x_record_set_description
561 WHERE record_set_id = x_record_set_id
562 AND USERENV('LANG') in (language, source_lang);
563
564 END Translate_Row;
565
566 PROCEDURE Load_Row(
567 x_record_set_id in varchar2
568 ,x_owner in varchar2
569 ,x_record_set_display_name in varchar2
570 ,x_record_set_description in varchar2
571 ,x_entity_id in varchar2
572 ,x_record_set_short_name in varchar2
573 ,x_pk_record_set_flag in varchar2
574 ,x_system_flag in varchar2
575 ,x_attribute_category in varchar2
576 ,x_attribute1 in varchar2
577 ,x_attribute2 in varchar2
578 ,x_attribute3 in varchar2
579 ,x_attribute4 in varchar2
580 ,x_attribute5 in varchar2
581 ,x_attribute6 in varchar2
582 ,x_attribute7 in varchar2
583 ,x_attribute8 in varchar2
584 ,x_attribute9 in varchar2
585 ,x_attribute10 in varchar2
586 ,x_attribute11 in varchar2
587 ,x_attribute12 in varchar2
588 ,x_attribute13 in varchar2
589 ,x_attribute14 in varchar2
590 ,x_attribute15 in varchar2
591 )
592 IS
593 BEGIN
594
595 declare
596 user_id number := 0;
597 row_id varchar2(64);
598 l_record_set_id number;
599 l_db_user_id number := 0;
600 l_valid_release boolean :=false;
601 begin
602
603 if (X_OWNER = 'SEED') then
604 user_id := 1;
605 end if;
606 --seed data changes start
607 user_id :=fnd_load_util.owner_id(x_owner);
608 begin
609 select last_updated_by
610 into l_db_user_id
611 from oe_pc_rsets
612 WHERE record_set_id = x_record_set_id;
613 exception
614 when no_data_found then
615 null;
616 end;
617 if (l_db_user_id <= user_id)
618 or (l_db_user_id in (0,1,2)
619 and user_id in (0,1,2)) then
620 l_valid_release:=true;
621 end if;
622 if l_valid_release then
623 --seed data changes end
624 OE_PC_Rsets_pkg.UPDATE_ROW(
625 x_rowid => row_id
626 ,x_record_set_id => x_record_set_id
627 ,x_record_set_display_name => x_record_set_display_name
628 ,x_record_set_description => x_record_set_description
629 ,x_entity_id => x_entity_id
630 ,x_record_set_short_name => x_record_set_short_name
631 ,x_pk_record_set_flag => x_pk_record_set_flag
632 ,x_system_flag => x_system_flag
633 ,x_created_by => user_id
634 ,x_creation_date => sysdate
635 ,x_last_updated_by => user_id
636 ,x_last_update_date => sysdate
637 ,x_last_update_login => 0
638 ,x_attribute_category => x_attribute_category
639 ,x_attribute1 => x_attribute1
640 ,x_attribute2 => x_attribute2
641 ,x_attribute3 => x_attribute3
642 ,x_attribute4 => x_attribute4
643 ,x_attribute5 => x_attribute5
644 ,x_attribute6 => x_attribute6
645 ,x_attribute7 => x_attribute7
646 ,x_attribute8 => x_attribute8
647 ,x_attribute9 => x_attribute9
648 ,x_attribute10 => x_attribute10
649 ,x_attribute11 => x_attribute11
650 ,x_attribute12 => x_attribute12
651 ,x_attribute13 => x_attribute13
652 ,x_attribute14 => x_attribute14
653 ,x_attribute15 => x_attribute15
654 );
655 end if;
656 exception
657 when NO_DATA_FOUND then
658
659 l_record_set_id := x_record_set_id;
660
661 oe_pc_rsets_pkg.INSERT_ROW(
662 x_rowid => row_id
663 ,x_record_set_id => l_record_set_id
664 ,x_record_set_display_name => x_record_set_display_name
665 ,x_record_set_description => x_record_set_description
666 ,x_entity_id => x_entity_id
667 ,x_record_set_short_name => x_record_set_short_name
668 ,x_pk_record_set_flag => x_pk_record_set_flag
669 ,x_system_flag => x_system_flag
670 ,x_created_by => user_id
671 ,x_creation_date => sysdate
672 ,x_last_updated_by => user_id
673 ,x_last_update_date => sysdate
674 ,x_last_update_login => 0
675 ,x_attribute_category => x_attribute_category
676 ,x_attribute1 => x_attribute1
677 ,x_attribute2 => x_attribute2
678 ,x_attribute3 => x_attribute3
679 ,x_attribute4 => x_attribute4
680 ,x_attribute5 => x_attribute5
681 ,x_attribute6 => x_attribute6
682 ,x_attribute7 => x_attribute7
683 ,x_attribute8 => x_attribute8
684 ,x_attribute9 => x_attribute9
685 ,x_attribute10 => x_attribute10
686 ,x_attribute11 => x_attribute11
687 ,x_attribute12 => x_attribute12
688 ,x_attribute13 => x_attribute13
689 ,x_attribute14 => x_attribute14
690 ,x_attribute15 => x_attribute15
691 );
692 end;
693
694 END LOAD_ROW;
695
696 END OE_PC_RSETS_PKG;