1 PACKAGE BODY igs_ad_code_classes_pkg AS
2 /* $Header: IGSAI75B.pls 120.3 2005/09/21 07:03:17 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_code_classes%RowType;
5 new_references igs_ad_code_classes%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2,
10 x_code_id IN NUMBER,
11 x_name IN VARCHAR2,
12 x_description IN VARCHAR2,
13 x_class IN VARCHAR2,
14 x_closed_ind IN VARCHAR2,
15 x_system_status IN VARCHAR2,
16 x_system_default IN VARCHAR2,
17 x_class_type_code IN VARCHAR2,
18 x_creation_date IN DATE,
19 x_created_by IN NUMBER,
20 x_last_update_date IN DATE,
21 x_last_updated_by IN NUMBER,
22 x_last_update_login IN NUMBER
23 ) AS
24
25 /*************************************************************
26 Created By : amuthu
27 Date Created On : 15-May-2000
28 Purpose :
29 Know limitations, enhancements or remarks
30 Change History
31 Who When What
32 samaresh 28-dec-2001 Bug Number 2158524. Two columns
33 SYSTEM_STATUS and SYSTEM_DEFAULT
34 have been added
35 (reverse chronological order - newest change first)
36 ***************************************************************/
37
38 CURSOR cur_old_ref_values IS
39 SELECT *
40 FROM IGS_AD_CODE_CLASSES
41 WHERE rowid = x_rowid;
42
43 BEGIN
44
45 l_rowid := x_rowid;
46
47 -- Code for setting the Old and New Reference Values.
48 -- Populate Old Values.
49 Open cur_old_ref_values;
50 Fetch cur_old_ref_values INTO old_references;
51 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
52 Close cur_old_ref_values;
53 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
54 IGS_GE_MSG_STACK.ADD;
55 App_Exception.Raise_Exception;
56 Return;
57 END IF;
58 Close cur_old_ref_values;
59
60 -- Populate New Values.
61 new_references.code_id := x_code_id;
62 new_references.name := x_name;
63 new_references.description := x_description;
64 new_references.class := x_class;
65 new_references.closed_ind := x_closed_ind;
66 new_references.system_status := x_system_status;
67 new_references.system_default := x_system_default;
68 new_references.class_type_code := x_class_type_code;
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
75 END IF;
76 new_references.last_update_date := x_last_update_date;
77 new_references.last_updated_by := x_last_updated_by;
78 new_references.last_update_login := x_last_update_login;
79
80 END Set_Column_Values;
81
82 PROCEDURE Check_Constraints (
83 Column_Name IN VARCHAR2,
84 Column_Value IN VARCHAR2) AS
85 /*************************************************************
86 Created By : amuthu
87 Date Created On : 15-May-2000
88 Purpose :
89 Know limitations, enhancements or remarks
90 Change History
91 Who When What
92
93 (reverse chronological order - newest change first)
94 ***************************************************************/
95
96 BEGIN
97 IF column_name IS NULL THEN
98 NULL;
99 ELSIF UPPER(column_name) = 'CLOSED_IND' THEN
100 new_references.closed_ind := column_value;
101 NULL;
102 END IF;
103
104 -- The following code checks for check constraints on the Columns.
105 IF Upper(Column_Name) = 'CLOSED_IND' OR
106 Column_Name IS NULL THEN
107 IF NOT (new_references.closed_ind IN ('Y','N')) THEN
108 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
109 IGS_GE_MSG_STACK.ADD;
110 App_Exception.Raise_Exception;
111 END IF;
112 END IF;
113 END Check_Constraints;
114
115 PROCEDURE Check_Parent_Existance AS
116 /*************************************************************
117 Created By : amuthu
118 Date Created On : 15-May-2000
119 Purpose :
120 Know limitations, enhancements or remarks
121 Change History
122 Who When What
123 rrengara 9-APR-2002 Bugno : 2309311 - Added check for SYS_APPL_SOURCE.
124 pkpatel 20-JUL-2001 Bug no. 1890270 Admissions Standards and Rules Dld_adsr_setup
125 Added code to check for the existence of new lookup_type 'PROBABILITY_DETAILS'
126 samaresh 28-dec-2001 Modified the function lookup_thru_lookup_type to add another parameter
127 Bug Number 2158524. Two columns SYSTEM_STATUS and SYSTEM_DEFAULT have been added
128 Added call to igs_lookups_view_pkg.get_pk_for_validation for the column
129 system_status
130 (reverse chronological order - newest change first)
131 ***************************************************************/
132 BEGIN
133 IF (old_references.class = new_references.class OR
134 new_references.class IS NULL) THEN
135 NULL;
136 ELSIF NOT (Igs_Lookups_view_pkg.Get_PK_For_Validation ('ADM_CODE_CLASSES',new_references.class)OR
137 Igs_Lookups_view_pkg.Get_PK_For_Validation ('IGS_AD_QUAL_TYPE',new_references.class) OR
138 Igs_Lookups_view_pkg.Get_PK_For_Validation (new_references.class_type_code,new_references.class)) THEN
139 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
140 IGS_GE_MSG_STACK.ADD;
141 App_Exception.Raise_Exception;
142 END IF;
143
144 IF (old_references.system_status = new_references.system_status OR
145 new_references.system_status IS NULL) THEN
146 NULL;
147 ELSIF NOT (Igs_Lookups_view_pkg.Get_PK_For_Validation (new_references.class,new_references.system_status)) THEN
148 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
149 IGS_GE_MSG_STACK.ADD;
150 App_Exception.Raise_Exception;
151 END IF;
152 END Check_Parent_Existance;
153
154 PROCEDURE Check_Uniqueness AS
155 /*************************************************************
156 Created By : amuthu
157 Date Created On : 15-May-2000
158 Purpose :
159 Know limitations, enhancements or remarks
160 Change History
161 Who When What
162
163 (reverse chronological order - newest change first)
164 ***************************************************************/
165
166 begin
167 IF Get_Uk_For_Validation (
168 new_references.name
169 ,new_references.class
170 ,new_references.class_type_code
171 ) THEN
172 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
173 IGS_GE_MSG_STACK.ADD;
174 app_exception.raise_exception;
175 END IF;
176 END Check_Uniqueness ;
177
178 PROCEDURE Check_Child_Existance IS
179 /*************************************************************
180 Created By : amuthu
181 Date Created On : 15-May-2000
182 Purpose :
183 Know limitations, enhancements or remarks
184 Change History
185 Who When What
186 nshee 07-feb-2001 Changed the call of IGS_AD_CREDENTIALS
187 as part of Bug#2177686 to IGS_PE_CREDENTIALS
188 agairola 09-AUG-2001 Added the code for Foreign Key with Statistics and IGS_PE_HZ_PARTIES
189 for Bug No. 1872994
190 pkpatel 19-JUL-2001 Bug no. 1890270 Admissions Standards and Rules Dld_adsr_setup
191 Added the call igs_ad_recruit_pi_pkg.get_fk_igs_ad_code_classes for
192 checking child existence in igs_ad_recruit_pi table
193 nsinha Aug 01, 2001 Bug enh no : 1905651 changes.
194 Added Igs_Ad_Appl_Pkg.Get_FK_Igs_Ad_Code_Classes
195 rboddu Oct 10, 2001 Bug no: 2019075 changes
196 Added Igs_Ad_Acad_Honors_Pkg..Get_FK_Igs_Ad_Cod
197 npalanis OCT 31 2002 Bug : 2608360
198 remove get fk for code classes migrated to lookups e_classes for checking
199 child existence in igs_ad_acad_honors table which was missed out NOCOPY earlier from the Check_Child_Existence procedure.
200 Aiyer 04-feb-2003 Bug : 2664699 Removed get_fk for code classes with IGS_AD_I_ENTRY_STATS_PKG (test_source_id column)
201
202
203 (reverse chronological order - newest change first)
204 ***************************************************************/
205
206 BEGIN
207
208 Igs_Ad_Adv_Placement_Pkg.Get_FK_Igs_Ad_Code_Classes (
209 old_references.code_id
210 );
211
212 Igs_Ad_Appl_Eval_Pkg.Get_FK_Igs_Ad_Code_Classes (
213 old_references.code_id
214 );
215
216 Igs_Ad_App_Intent_Pkg.Get_FK_Igs_Ad_Code_Classes (
217 old_references.code_id
218 );
219
220 Igs_Ad_App_Req_Pkg.Get_FK_Igs_Ad_Code_Classes (
221 old_references.code_id
222 );
223
224 Igs_Ad_Conv_Gs_Types_Pkg.Get_FK_Igs_Ad_Code_Classes (
225 old_references.code_id
226 );
227
228 Igs_Ad_Edugoal_Pkg.Get_FK_Igs_Ad_Code_Classes (
229 old_references.code_id
230 );
231
232
233 Igs_Ad_Past_History_Pkg.Get_FK_Igs_Ad_Code_Classes (
234 old_references.code_id
235 );
236
237 Igs_Ad_Ps_Appl_Inst_Pkg.Get_FK_Igs_Ad_Code_Classes (
238 old_references.code_id
239 );
240
241 Igs_Ad_Spl_Talents_Pkg.Get_FK_Igs_Ad_Code_Classes (
242 old_references.code_id
243 );
244
245 Igs_Ad_Term_Unitdtls_Pkg.Get_FK_Igs_Ad_Code_Classes (
246 old_references.code_id
247 );
248
249 Igs_Ad_Test_Results_Pkg.Get_FK_Igs_Ad_Code_Classes (
250 old_references.code_id
251 );
252
253 Igs_Ad_Transcript_Pkg.Get_FK_Igs_Ad_Code_Classes (
254 old_references.code_id
255 );
256
257 Igs_Ad_Tst_Rslt_Dtls_Pkg.Get_FK_Igs_Ad_Code_Classes (
258 old_references.code_id
259 );
260
261 Igs_Ad_Appqual_Code_Pkg.Get_FK_Igs_Ad_Code_Classes(
262 old_references.code_id
263 );
264
265 IF NVL(fnd_profile.value('IGS_RECRUITING_ENABLED'), 'N') = 'Y' THEN
266 EXECUTE IMMEDIATE
267 'begin Igr_I_Appl_Pkg.Get_FK_Igs_Ad_Code_Classes (:1); end;'
268 USING old_references.code_id;
269 END IF;
270
271 igs_ad_recruit_pi_pkg.get_fk_igs_ad_code_classes (
272 old_references.code_id
273 );
274
275 Igs_Ad_Appl_Pkg.Get_FK_Igs_Ad_Code_Classes (
276 old_references.code_id
277 );
278
279
280 IGS_PE_HZ_PARTIES_PKG.Get_Fk_IGS_AD_CODE_CLASSES1(
281 old_references.code_id
282 );
283
284
285 IGS_UC_DEFAULTS_PKG.GET_FK_IGS_AD_CODE_CLASSES(
286 old_references.code_id
287 );
288
289 IGS_AD_PANEL_DTLS_PKG.GET_UFK_IGS_AD_CODE_CLASSES(
290 old_references.name,
291 old_references.class
292 );
293
294 IGS_AD_PNMEMBR_DTLS_PKG.GET_UFK_IGS_AD_CODE_CLASSES(
295 old_references.name,
296 old_references.class
297 );
298
299 IGS_AD_PNL_HIS_DTLS_PKG.GET_UFK_IGS_AD_CODE_CLASSES(
300 old_references.name,
301 old_references.class
302 );
303
304 END Check_Child_Existance;
305
306 FUNCTION Get_PK_For_Validation (
307 x_code_id IN NUMBER,
308 x_closed_ind IN VARCHAR2
309 ) RETURN BOOLEAN AS
310
311 /*************************************************************
312 Created By : amuthu
313 Date Created On : 15-May-2000
314 Purpose :
315 Know limitations, enhancements or remarks
316 Change History
317 Who When What
318
319 (reverse chronological order - newest change first)
320 ***************************************************************/
321
322 CURSOR cur_rowid IS
323 SELECT rowid
324 FROM igs_ad_code_classes
325 WHERE code_id = x_code_id AND
326 closed_ind = NVL(x_closed_ind,closed_ind);
327
328 lv_rowid cur_rowid%RowType;
329
330 BEGIN
331
332 Open cur_rowid;
333 Fetch cur_rowid INTO lv_rowid;
334 IF (cur_rowid%FOUND) THEN
335 Close cur_rowid;
336 Return(TRUE);
337 ELSE
338 Close cur_rowid;
339 Return(FALSE);
340 END IF;
341 END Get_PK_For_Validation;
342
343 FUNCTION Get_UK_For_Validation (
344 x_name IN VARCHAR2,
345 x_class IN VARCHAR2,
346 x_closed_ind IN VARCHAR2,
347 x_class_type_code IN VARCHAR2 DEFAULT NULL
348 ) RETURN BOOLEAN AS
349
350 /*************************************************************
351 Created By : amuthu
352 Date Created On : 15-May-2000
353 Purpose :
354 Know limitations, enhancements or remarks
355 Change History
356 Who When What
357
358 (reverse chronological order - newest change first)
359 ***************************************************************/
360 l_class_type_code VARCHAR2(30);
361
362 CURSOR cur_rowid(cp_class_type_code IN VARCHAR2) IS
363 SELECT rowid
364 FROM igs_ad_code_classes
365 WHERE name = x_name AND
366 class = x_class AND
367 class_type_code = cp_class_type_code AND
368 ((l_rowid is null) or (rowid <> l_rowid)) AND
369 closed_ind = NVL(x_closed_ind,closed_ind);
370
371 lv_rowid cur_rowid%RowType;
372
373
374 BEGIN
375 l_class_type_code :=NVL(x_class_type_code,'ADM_CODE_CLASSES');
376
377 Open cur_rowid(l_class_type_code);
378 Fetch cur_rowid INTO lv_rowid;
379 IF (cur_rowid%FOUND) THEN
380 Close cur_rowid;
381 return (true);
382 ELSE
383 close cur_rowid;
384 return(false);
385 END IF;
386 END Get_UK_For_Validation ;
387
388 FUNCTION Get_UK2_For_Validation (
389 x_code_id IN NUMBER,
390 x_class IN VARCHAR2,
391 x_closed_ind IN VARCHAR2,
392 x_class_type_code IN VARCHAR2 DEFAULT NULL
393 ) RETURN BOOLEAN AS
394
395 /*************************************************************
396 Created By : akadam
397 Date Created On : 11-Nov-2003
398 Purpose :
399 Know limitations, enhancements or remarks
400 Change History
401 Who When What
402
403 (reverse chronological order - newest change first)
404 ***************************************************************/
405 l_class_type_code VARCHAR2(30);
406
407 CURSOR cur_rowid(cp_class_type_code IN VARCHAR2) IS
408 SELECT rowid
409 FROM igs_ad_code_classes
410 WHERE code_id = x_code_id AND
411 class = x_class AND
412 class_type_code = cp_class_type_code AND
413 ((l_rowid is null) or (rowid <> l_rowid)) AND
414 closed_ind = NVL(x_closed_ind,closed_ind);
415
416 lv_rowid cur_rowid%RowType;
417
418 BEGIN
419 l_class_type_code :=NVL(x_class_type_code,'ADM_CODE_CLASSES');
420
421 Open cur_rowid(l_class_type_code);
422 Fetch cur_rowid INTO lv_rowid;
423 IF (cur_rowid%FOUND) THEN
424 Close cur_rowid;
425 return (true);
426 ELSE
427 close cur_rowid;
428 return(false);
429 END IF;
430 END Get_UK2_For_Validation ;
431
432
433 PROCEDURE Before_DML (
434 p_action IN VARCHAR2,
435 x_rowid IN VARCHAR2 ,
436 x_code_id IN NUMBER,
437 x_name IN VARCHAR2,
438 x_description IN VARCHAR2,
439 x_class IN VARCHAR2,
440 x_closed_ind IN VARCHAR2,
441 x_system_status IN VARCHAR2,
442 x_system_default IN VARCHAR2,
443 x_creation_date IN DATE,
444 x_created_by IN NUMBER,
445 x_last_update_date IN DATE,
446 x_last_updated_by IN NUMBER,
447 x_last_update_login IN NUMBER,
448 x_class_type_code IN VARCHAR2 DEFAULT NULL
449 ) AS
450 /*************************************************************
451 Created By : amuthu
452 Date Created On : 15-May-2000
453 Purpose :
454 Know limitations, enhancements or remarks
455 Change History
456 Who When What
457
458 (reverse chronological order - newest change first)
459 ***************************************************************/
460
461 BEGIN
462
463 Set_Column_Values (
464 p_action,
465 x_rowid,
466 x_code_id,
467 x_name,
468 x_description,
469 x_class,
470 x_closed_ind,
471 x_system_status,
472 x_system_default,
473 x_class_type_code,
474 x_creation_date,
475 x_created_by,
476 x_last_update_date,
477 x_last_updated_by,
478 x_last_update_login
479 );
480
481 IF (p_action = 'INSERT') THEN
482 -- Call all the procedures related to Before Insert.
483 Null;
484 IF Get_Pk_For_Validation(
485 new_references.code_id) THEN
486 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
487 IGS_GE_MSG_STACK.ADD;
488 App_Exception.Raise_Exception;
489 END IF;
490 Check_Uniqueness;
491 Check_Parent_Existance;
492 Check_Constraints;
493 ELSIF (p_action = 'UPDATE') THEN
494 -- Call all the procedures related to Before Update.
495 Null;
496 Check_Uniqueness;
497 Check_Parent_Existance;
498 Check_Constraints;
499 ELSIF (p_action = 'DELETE') THEN
500 -- Call all the procedures related to Before Delete.
501 Null;
502 Check_Child_Existance;
503 ELSIF (p_action = 'VALIDATE_INSERT') THEN
504 -- Call all the procedures related to Before Insert.
505 IF Get_PK_For_Validation (
506 new_references.code_id) THEN
507 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
508 IGS_GE_MSG_STACK.ADD;
509 App_Exception.Raise_Exception;
510 END IF;
511 Check_Uniqueness;
512 Check_Constraints;
513 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
514 Check_Uniqueness;
515 Check_Constraints;
516 ELSIF (p_action = 'VALIDATE_DELETE') THEN
517 Check_Child_Existance;
518 END IF;
519
520 l_rowid := NULL;
521 END Before_DML;
522
523 PROCEDURE After_DML (
524 p_action IN VARCHAR2,
525 x_rowid IN VARCHAR2
526 ) IS
527 /*************************************************************
528 Created By : amuthu
529 Date Created On : 15-May-2000
530 Purpose :
531 Know limitations, enhancements or remarks
532 Change History
533 Who When What
534
535 (reverse chronological order - newest change first)
536 ***************************************************************/
537
538 BEGIN
539
540 l_rowid := x_rowid;
541
542 IF (p_action = 'INSERT') THEN
543 -- Call all the procedures related to After Insert.
544 Null;
545 ELSIF (p_action = 'UPDATE') THEN
546 -- Call all the procedures related to After Update.
547 Null;
548 ELSIF (p_action = 'DELETE') THEN
549 -- Call all the procedures related to After Delete.
550 Null;
551 END IF;
552
553 l_rowid:=NULL;
554 END After_DML;
555
556 procedure INSERT_ROW (
557 X_ROWID in out NOCOPY VARCHAR2,
558 x_CODE_ID IN OUT NOCOPY NUMBER,
559 x_NAME IN VARCHAR2,
560 x_DESCRIPTION IN VARCHAR2,
561 x_CLASS IN VARCHAR2,
562 x_CLOSED_IND IN VARCHAR2,
563 x_system_status IN VARCHAR2,
564 x_system_default IN VARCHAR2,
565 X_MODE in VARCHAR2,
566 x_class_type_code IN VARCHAR2 DEFAULT NULL
567 ) AS
568 /*************************************************************
569 Created By : amuthu
570 Date Created On : 15-May-2000
571 Purpose :
572 Know limitations, enhancements or remarks
573 Change History
574 Who When What
575
576 (reverse chronological order - newest change first)
577 ***************************************************************/
578
579 cursor C is select ROWID from IGS_AD_CODE_CLASSES
580 where CODE_ID= X_CODE_ID
581 ;
582 X_LAST_UPDATE_DATE DATE ;
583 X_LAST_UPDATED_BY NUMBER ;
584 X_LAST_UPDATE_LOGIN NUMBER ;
585 begin
586 X_LAST_UPDATE_DATE := SYSDATE;
587 if(X_MODE = 'I') then
588 X_LAST_UPDATED_BY := 1;
589 X_LAST_UPDATE_LOGIN := 0;
590 elsif (X_MODE = 'R') then
591 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
592 if X_LAST_UPDATED_BY is NULL then
593 X_LAST_UPDATED_BY := -1;
594 end if;
595 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
596 if X_LAST_UPDATE_LOGIN is NULL then
597 X_LAST_UPDATE_LOGIN := -1;
598 end if;
599 else
600 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
601 IGS_GE_MSG_STACK.ADD;
602 app_exception.raise_exception;
603 end if;
604
605 X_CODE_ID := -1;
606 Before_DML(
607 p_action=>'INSERT',
608 x_rowid=>X_ROWID,
609 x_code_id=>X_CODE_ID,
610 x_name=>X_NAME,
611 x_description=>X_DESCRIPTION,
612 x_class=>X_CLASS,
613 x_closed_ind=>X_CLOSED_IND,
614 x_system_status => X_SYSTEM_STATUS,
615 x_system_default => X_SYSTEM_DEFAULT,
616 x_class_type_code => x_class_type_code,
617 x_creation_date=>X_LAST_UPDATE_DATE,
618 x_created_by=>X_LAST_UPDATED_BY,
619 x_last_update_date=>X_LAST_UPDATE_DATE,
620 x_last_updated_by=>X_LAST_UPDATED_BY,
621 x_last_update_login=>X_LAST_UPDATE_LOGIN);
622 insert into IGS_AD_CODE_CLASSES (
623 CODE_ID
624 ,NAME
625 ,DESCRIPTION
626 ,CLASS
627 ,CLOSED_IND
628 ,SYSTEM_STATUS
629 ,SYSTEM_DEFAULT
630 ,CREATION_DATE
631 ,CREATED_BY
632 ,LAST_UPDATE_DATE
633 ,LAST_UPDATED_BY
634 ,LAST_UPDATE_LOGIN
635 ,class_type_code
636 ) values (
637 IGS_AD_CODE_CLASSES_S.NEXTVAL
638 ,NEW_REFERENCES.NAME
639 ,NEW_REFERENCES.DESCRIPTION
640 ,NEW_REFERENCES.CLASS
641 ,NEW_REFERENCES.CLOSED_IND
642 ,NEW_REFERENCES.SYSTEM_STATUS
643 ,NEW_REFERENCES.SYSTEM_DEFAULT
644 ,X_LAST_UPDATE_DATE
645 ,X_LAST_UPDATED_BY
646 ,X_LAST_UPDATE_DATE
647 ,X_LAST_UPDATED_BY
648 ,X_LAST_UPDATE_LOGIN
649 ,NEW_REFERENCES.class_type_code
650 )RETURNING CODE_ID INTO X_CODE_ID ;
651 open c;
652 fetch c into X_ROWID;
653 if (c%notfound) then
654 close c;
655 raise no_data_found;
656 end if;
657 close c;
658 After_DML (
659 p_action => 'INSERT' ,
660 x_rowid => X_ROWID );
661 end INSERT_ROW;
662 procedure LOCK_ROW (
663 X_ROWID in VARCHAR2,
664 x_CODE_ID IN NUMBER,
665 x_NAME IN VARCHAR2,
666 x_DESCRIPTION IN VARCHAR2,
667 x_CLASS IN VARCHAR2,
668 x_CLOSED_IND IN VARCHAR2,
669 x_system_status IN VARCHAR2 ,
670 x_system_default IN VARCHAR2,
671 x_class_type_code IN VARCHAR2 DEFAULT NULL
672 ) AS
673 /*************************************************************
674 Created By : amuthu
675 Date Created On : 15-May-2000
676 Purpose :
677 Know limitations, enhancements or remarks
678 Change History
679 Who When What
680
681 (reverse chronological order - newest change first)
682 ***************************************************************/
683
684 cursor c1 is select
685 NAME
686 , DESCRIPTION
687 , CLASS
688 , CLOSED_IND
689 , SYSTEM_STATUS
690 , SYSTEM_DEFAULT
691 , class_type_code
692 from IGS_AD_CODE_CLASSES
693 where ROWID = X_ROWID
694 for update nowait;
695 tlinfo c1%rowtype;
696 begin
697 open c1;
698 fetch c1 into tlinfo;
699 if (c1%notfound) then
700 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
701 IGS_GE_MSG_STACK.ADD;
702 close c1;
703 app_exception.raise_exception;
704 return;
705 end if;
706 close c1;
707 if ( ( tlinfo.NAME = X_NAME)
708 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
709 AND (tlinfo.CLASS = X_CLASS)
710 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
711 AND ((tlinfo.SYSTEM_STATUS = x_system_status)
712 OR ((tlinfo.system_status is null)
713 AND (x_system_status is null)))
714 AND ((tlinfo.SYSTEM_DEFAULT = x_system_default)
715 OR ((tlinfo.system_default is null)
716 AND (x_system_default is null)))
717 AND ((tlinfo.class_type_code = x_class_type_code)
718 OR ((tlinfo.class_type_code is null)
719 AND (x_class_type_code is null)))
720 ) then
721 null;
722 else
723 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
724 IGS_GE_MSG_STACK.ADD;
725 app_exception.raise_exception;
726 end if;
727 return;
728 end LOCK_ROW;
729 Procedure UPDATE_ROW (
730 X_ROWID in VARCHAR2,
731 x_CODE_ID IN NUMBER,
732 x_NAME IN VARCHAR2,
733 x_DESCRIPTION IN VARCHAR2,
734 x_CLASS IN VARCHAR2,
735 x_CLOSED_IND IN VARCHAR2,
736 x_SYSTEM_STATUS IN VARCHAR2,
737 x_SYSTEM_DEFAULT IN VARCHAR2,
738 X_MODE in VARCHAR2,
739 x_class_type_code IN VARCHAR2 DEFAULT NULL
740 ) AS
741 /*************************************************************
742 Created By : amuthu
743 Date Created On : 15-May-2000
744 Purpose :
745 Know limitations, enhancements or remarks
746 Change History
747 Who When What
748
749 (reverse chronological order - newest change first)
750 ***************************************************************/
751
752 X_LAST_UPDATE_DATE DATE ;
753 X_LAST_UPDATED_BY NUMBER ;
754 X_LAST_UPDATE_LOGIN NUMBER ;
755 begin
756 X_LAST_UPDATE_DATE := SYSDATE;
757 if(X_MODE = 'I') then
758 X_LAST_UPDATED_BY := 1;
759 X_LAST_UPDATE_LOGIN := 0;
760 elsif (X_MODE = 'R') then
761 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
762 if X_LAST_UPDATED_BY is NULL then
763 X_LAST_UPDATED_BY := -1;
764 end if;
765 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
766 if X_LAST_UPDATE_LOGIN is NULL then
767 X_LAST_UPDATE_LOGIN := -1;
768 end if;
769 else
770 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
771 IGS_GE_MSG_STACK.ADD;
772 app_exception.raise_exception;
773 end if;
774 Before_DML(
775 p_action=>'UPDATE',
776 x_rowid=>X_ROWID,
777 x_code_id=>X_CODE_ID,
778 x_name=>X_NAME,
779 x_description=>X_DESCRIPTION,
780 x_class=>X_CLASS,
781 x_closed_ind=>X_CLOSED_IND,
782 x_system_status=>X_SYSTEM_STATUS,
783 x_system_default=>X_SYSTEM_DEFAULT,
784 x_class_type_code => x_class_type_code,
785 x_creation_date=>X_LAST_UPDATE_DATE,
786 x_created_by=>X_LAST_UPDATED_BY,
787 x_last_update_date=>X_LAST_UPDATE_DATE,
788 x_last_updated_by=>X_LAST_UPDATED_BY,
789 x_last_update_login=>X_LAST_UPDATE_LOGIN);
790 update IGS_AD_CODE_CLASSES set
791 NAME = NEW_REFERENCES.NAME,
792 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
793 CLASS = NEW_REFERENCES.CLASS,
794 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
795 SYSTEM_STATUS = NEW_REFERENCES.SYSTEM_STATUS,
796 SYSTEM_DEFAULT = NEW_REFERENCES.SYSTEM_DEFAULT,
797 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
798 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
799 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
800 class_type_code = x_class_type_code
801 where ROWID = X_ROWID;
802 if (sql%notfound) then
803 raise no_data_found;
804 end if;
805
806 After_DML (
807 p_action => 'UPDATE' ,
808 x_rowid => X_ROWID
809 );
810 end UPDATE_ROW;
811 procedure ADD_ROW (
812 X_ROWID in out NOCOPY VARCHAR2,
813 x_CODE_ID IN OUT NOCOPY NUMBER,
814 x_NAME IN VARCHAR2,
815 x_DESCRIPTION IN VARCHAR2,
816 x_CLASS IN VARCHAR2,
817 x_CLOSED_IND IN VARCHAR2,
818 x_SYSTEM_STATUS IN VARCHAR2,
819 x_SYSTEM_DEFAULT IN VARCHAR2,
820 X_MODE in VARCHAR2,
821 x_class_type_code IN VARCHAR2 DEFAULT NULL
822 ) AS
823 /*************************************************************
824 Created By : amuthu
825 Date Created On : 15-May-2000
826 Purpose :
827 Know limitations, enhancements or remarks
828 Change History
829 Who When What
830
831 (reverse chronological order - newest change first)
832 ***************************************************************/
833
834 cursor c1 is select ROWID from IGS_AD_CODE_CLASSES
835 where CODE_ID= X_CODE_ID
836 ;
837 begin
838 open c1;
839 fetch c1 into X_ROWID;
840 if (c1%notfound) then
841 close c1;
842 INSERT_ROW (
843 X_ROWID,
844 X_CODE_ID,
845 X_NAME,
846 X_DESCRIPTION,
847 X_CLASS,
848 X_CLOSED_IND,
849 X_SYSTEM_STATUS,
850 X_SYSTEM_DEFAULT,
851 x_class_type_code,
852 X_MODE );
853 return;
854 end if;
855 close c1;
856 UPDATE_ROW (
857 X_ROWID,
858 X_CODE_ID,
859 X_NAME,
860 X_DESCRIPTION,
861 X_CLASS,
862 X_CLOSED_IND,
863 X_SYSTEM_STATUS,
864 X_SYSTEM_DEFAULT,
865 x_class_type_code,
866 X_MODE );
867 end ADD_ROW;
868 procedure DELETE_ROW (
869 X_ROWID in VARCHAR2
870 ) AS
871 /*************************************************************
872 Created By : amuthu
873 Date Created On : 15-May-2000
874 Purpose :
875 Know limitations, enhancements or remarks
876 Change History
877 Who When What
878
879 (reverse chronological order - newest change first)
880 ***************************************************************/
881
882 begin
883 Before_DML (
884 p_action => 'DELETE',
885 x_rowid => X_ROWID
886 );
887 delete from IGS_AD_CODE_CLASSES
888 where ROWID = X_ROWID;
889 if (sql%notfound) then
890 raise no_data_found;
891 end if;
892 After_DML (
893 p_action => 'DELETE',
894 x_rowid => X_ROWID
895 );
896 end DELETE_ROW;
897 END igs_ad_code_classes_pkg;
[Home] [Help]