1 PACKAGE BODY igs_ad_syssrc_cat_pkg AS
2 /* $Header: IGSAI72B.pls 115.13 2003/10/30 13:16:33 akadam ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_syssrc_cat%RowType;
5 new_references igs_ad_syssrc_cat%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_system_source_type IN VARCHAR2 DEFAULT NULL,
11 x_category_name IN VARCHAR2 DEFAULT NULL,
12 x_mandatory_ind IN VARCHAR2 DEFAULT NULL,
13 x_ss_ind IN VARCHAR2 DEFAULT NULL,
14 x_display_sequence IN NUMBER DEFAULT NULL,
15 x_ad_tab_name IN VARCHAR2 DEFAULT NULL,
16 x_int_tab_name IN VARCHAR2 DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL,
22 X_CLOSED_IND IN VARCHAR2 DEFAULT 'N'
23 ) AS
24
25 /*************************************************************
26 Created By : amuthu
27 Date Created On : 16-May-2000
28 Purpose :
29 Know limitations, enhancements or remarks
30 Change History
31 Who When What
32 pkpatel 16-JUN-2001 Added processing for two newly added columns AD_TAB_NAME and INT_TAB_NAME
33 (reverse chronological order - newest change first)
34 ***************************************************************/
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM IGS_AD_SYSSRC_CAT
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 Open cur_old_ref_values;
48 Fetch cur_old_ref_values INTO old_references;
49 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
50 Close cur_old_ref_values;
51 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
52 IGS_GE_MSG_STACK.ADD;
53 App_Exception.Raise_Exception;
54 Return;
55 END IF;
56 Close cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.system_source_type := x_system_source_type;
60 new_references.category_name := x_category_name;
61 new_references.mandatory_ind := x_mandatory_ind;
62 new_references.ss_ind := x_ss_ind;
63 new_references.display_sequence := x_display_sequence;
64 new_references.ad_tab_name := x_ad_tab_name;
65 new_references.int_tab_name := x_int_tab_name;
66 IF (p_action = 'UPDATE') THEN
67 new_references.creation_date := old_references.creation_date;
68 new_references.created_by := old_references.created_by;
69 ELSE
70 new_references.creation_date := x_creation_date;
71 new_references.created_by := x_created_by;
72 END IF;
73 new_references.last_update_date := x_last_update_date;
74 new_references.last_updated_by := x_last_updated_by;
75 new_references.last_update_login := x_last_update_login;
76 new_references.closed_ind := NVL(x_closed_ind,'N');
77
78 END Set_Column_Values;
79
80 PROCEDURE Check_Constraints (
81 Column_Name IN VARCHAR2 DEFAULT NULL,
82 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
83 /*************************************************************
84 Created By : amuthu
85 Date Created On : 16-May-2000
86 Purpose :
87 Know limitations, enhancements or remarks
88 Change History
89 Who When What
90
91 (reverse chronological order - newest change first)
92 ***************************************************************/
93
94 BEGIN
95
96 IF column_name IS NULL THEN
97 NULL;
98 ELSIF UPPER(column_name) = 'MANDATORY_IND' THEN
99 new_references.mandatory_ind := column_value;
100 NULL;
101 END IF;
102 ----Removed the validations for SS_IND,DISPLAY_SEQUENCE ( pbondugu Bug #3032535)
103 -- The following code checks for check constraints on the Columns.
104 IF Upper(Column_Name) = 'MANDATORY_IND' OR
105 Column_Name IS NULL THEN
106 IF NOT (new_references.mandatory_ind IN ('Y','N')) THEN
107 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
108 IGS_GE_MSG_STACK.ADD;
109 App_Exception.Raise_Exception;
110 END IF;
111 END IF;
112
113 END Check_Constraints;
114
115 PROCEDURE Check_Parent_Existance AS
116 /*************************************************************
117 Created By : amuthu
118 Date Created On : 16-May-2000
119 Purpose :
120 Know limitations, enhancements or remarks
121 Change History
122 Who When What
123 hreddych 26-mar-2002 2803604 -To remove dependency on the
124 Igs_lookups_view_Pkg in seed env
125 Lookups are not deleted Hence no need to
126 check for existence.
127 (reverse chronological order - newest change first)
128 ***************************************************************/
129
130 BEGIN
131 NULL;
132
133 /* IF (((old_references.category_name = new_references.category_name)) OR
134 ((new_references.category_name IS NULL))) THEN
135 NULL;
136 ELSIF NOT Igs_lookups_view_Pkg.Get_PK_For_Validation (
137 'SYSTEM_SOURCE_TYPES',
138 new_references.system_source_type
139 ) THEN
140 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
141 IGS_GE_MSG_STACK.ADD;
142 App_Exception.Raise_Exception;
143 END IF;
144
145 */
146 END Check_Parent_Existance;
147
148
149 FUNCTION Get_PK_For_Validation (
150 x_category_name IN VARCHAR2,
151 x_system_source_type IN VARCHAR2,
152 x_closed_ind IN VARCHAR2
153 ) RETURN BOOLEAN AS
154
155 /*************************************************************
156 Created By : amuthu
157 Date Created On : 16-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 CURSOR cur_rowid IS
167 SELECT rowid
168 FROM igs_ad_syssrc_cat
169 WHERE category_name = x_category_name
170 AND system_source_type = x_system_source_type AND
171 closed_ind = NVL(x_closed_ind,closed_ind)
172 FOR UPDATE NOWAIT;
173
174 lv_rowid cur_rowid%RowType;
175
176 BEGIN
177
178 Open cur_rowid;
179 Fetch cur_rowid INTO lv_rowid;
180 IF (cur_rowid%FOUND) THEN
181 Close cur_rowid;
182 Return(TRUE);
183 ELSE
184 Close cur_rowid;
185 Return(FALSE);
186 END IF;
187 END Get_PK_For_Validation;
188
189
190 FUNCTION get_uk_for_validation (
191 x_category_name IN VARCHAR2,
192 x_system_source_type IN VARCHAR2
193 ) RETURN BOOLEAN AS
194 /*
195 || Created By : kumma
196 || Created On : 12-FEB-2003
197 || Purpose : Validates the Unique Keys of the table.
198 || Known limitations, enhancements or remarks :
199 || Change History :
200 || Who When What
201 || (reverse chronological order - newest change first)
202 */
203 CURSOR cur_rowid IS
204 SELECT rowid
205 FROM igs_ad_syssrc_cat
206 WHERE upper(category_name) = upper(x_category_name)
207 AND upper(system_source_type) = upper(x_system_source_type)
208 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
209
210 lv_rowid cur_rowid%RowType;
211
212 BEGIN
213 OPEN cur_rowid;
214 FETCH cur_rowid INTO lv_rowid;
215 IF (cur_rowid%FOUND) THEN
216 CLOSE cur_rowid;
217 RETURN (true);
218 ELSE
219 CLOSE cur_rowid;
220 RETURN(FALSE);
221 END IF;
222
223 END get_uk_for_validation ;
224
225
226 PROCEDURE Check_Uniqueness AS
227 /*************************************************************
228 Created By :
229 Date Created By :
230 Purpose :
231 Know limitations, enhancements or remarks
232 Change History
233 Who When What
234 (reverse chronological order - newest change first)
235 ***************************************************************/
236
237 BEGIN
238 IF Get_Uk_For_Validation (
239 new_references.category_name
240 ,new_references.system_source_type
241 ) THEN
242 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
243 IGS_GE_MSG_STACK.ADD;
244 app_exception.raise_exception;
245 END IF;
246 END Check_Uniqueness ;
247
248
249 PROCEDURE Before_DML (
250 p_action IN VARCHAR2,
251 x_rowid IN VARCHAR2 DEFAULT NULL,
252 x_system_source_type IN VARCHAR2 DEFAULT NULL,
253 x_category_name IN VARCHAR2 DEFAULT NULL,
254 x_mandatory_ind IN VARCHAR2 DEFAULT NULL,
255 x_ss_ind IN VARCHAR2 DEFAULT NULL,
256 x_display_sequence IN NUMBER DEFAULT NULL,
257 x_ad_tab_name IN VARCHAR2 DEFAULT NULL,
258 x_int_tab_name IN VARCHAR2 DEFAULT NULL,
259 x_creation_date IN DATE DEFAULT NULL,
260 x_created_by IN NUMBER DEFAULT NULL,
261 x_last_update_date IN DATE DEFAULT NULL,
262 x_last_updated_by IN NUMBER DEFAULT NULL,
263 x_last_update_login IN NUMBER DEFAULT NULL,
264 X_CLOSED_IND IN VARCHAR2 DEFAULT 'N'
265 ) AS
266 /*************************************************************
267 Created By : amuthu
268 Date Created On : 16-May-2000
269 Purpose :
270 Know limitations, enhancements or remarks
271 Change History
272 Who When What
273 pkpatel 16-JUN-2001 Added processing for two newly added columns AD_TAB_NAME and INT_TAB_NAME
274 (reverse chronological order - newest change first)
275 ***************************************************************/
276
277 BEGIN
278
279 Set_Column_Values (
280 p_action,
281 x_rowid,
282 x_system_source_type,
283 x_category_name,
284 x_mandatory_ind,
285 x_ss_ind,
286 x_display_sequence,
287 x_ad_tab_name,
288 x_int_tab_name,
289 x_creation_date,
290 x_created_by,
291 x_last_update_date,
292 x_last_updated_by,
293 x_last_update_login,
294 x_closed_ind
295 );
296
297 IF (p_action = 'INSERT') THEN
298 -- Call all the procedures related to Before Insert.
299 Null;
300 IF Get_Pk_For_Validation(
301 new_references.category_name,
302 new_references.system_source_type) THEN
303 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
304 IGS_GE_MSG_STACK.ADD;
305 App_Exception.Raise_Exception;
306 END IF;
307 --kumma, called the Check_Uniqueness 2664699
308 Check_Uniqueness;
309 Check_Constraints;
310 Check_Parent_Existance;
311 ELSIF (p_action = 'UPDATE') THEN
312 -- Call all the procedures related to Before Update.
313 Null;
314 --kumma, called the Check_Uniqueness 2664699
315 Check_Uniqueness;
316 Check_Constraints;
317 Check_Parent_Existance;
318 ELSIF (p_action = 'DELETE') THEN
319 -- Call all the procedures related to Before Delete.
320 Null;
321 ELSIF (p_action = 'VALIDATE_INSERT') THEN
322 -- Call all the procedures related to Before Insert.
323 IF Get_PK_For_Validation (
324 new_references.category_name,
325 new_references.system_source_type) THEN
326 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
327 IGS_GE_MSG_STACK.ADD;
328 App_Exception.Raise_Exception;
329 END IF;
330 Check_Constraints;
331 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
332 Check_Constraints;
333 ELSIF (p_action = 'VALIDATE_DELETE') THEN
334 Null;
335 END IF;
336
337 END Before_DML;
338
339 PROCEDURE After_DML (
340 p_action IN VARCHAR2,
341 x_rowid IN VARCHAR2
342 ) IS
343 /*************************************************************
344 Created By : amuthu
345 Date Created On : 16-May-2000
346 Purpose :
347 Know limitations, enhancements or remarks
348 Change History
349 Who When What
350
351 (reverse chronological order - newest change first)
352 ***************************************************************/
353
354 BEGIN
355
356 l_rowid := x_rowid;
357
358 IF (p_action = 'INSERT') THEN
359 -- Call all the procedures related to After Insert.
360 Null;
361 ELSIF (p_action = 'UPDATE') THEN
362 -- Call all the procedures related to After Update.
363 Null;
364 ELSIF (p_action = 'DELETE') THEN
365 -- Call all the procedures related to After Delete.
366 Null;
367 END IF;
368
369 END After_DML;
370
371 procedure INSERT_ROW (
372 X_ROWID in out NOCOPY VARCHAR2,
373 x_SYSTEM_SOURCE_TYPE IN VARCHAR2,
374 x_CATEGORY_NAME IN VARCHAR2,
375 x_MANDATORY_IND IN VARCHAR2,
376 x_SS_IND IN VARCHAR2,
377 x_DISPLAY_SEQUENCE IN NUMBER,
378 x_ad_tab_name IN VARCHAR2 DEFAULT NULL,
379 x_int_tab_name IN VARCHAR2 DEFAULT NULL,
380 X_MODE in VARCHAR2 default 'R',
381 X_CLOSED_IND IN VARCHAR2 DEFAULT 'N'
382 ) AS
383 /*************************************************************
384 Created By : amuthu
385 Date Created On : 16-May-2000
386 Purpose :
387 Know limitations, enhancements or remarks
388 Change History
389 Who When What
390 pkpatel 16-JUN-2001 Added processing for two newly added columns AD_TAB_NAME and INT_TAB_NAME
391 (reverse chronological order - newest change first)
392 ***************************************************************/
393
394 cursor C is select ROWID from IGS_AD_SYSSRC_CAT
395 where CATEGORY_NAME= X_CATEGORY_NAME
396 and SYSTEM_SOURCE_TYPE = X_SYSTEM_SOURCE_TYPE
397 ;
398 X_LAST_UPDATE_DATE DATE ;
399 X_LAST_UPDATED_BY NUMBER ;
400 X_LAST_UPDATE_LOGIN NUMBER ;
401 begin
402 X_LAST_UPDATE_DATE := SYSDATE;
403 if(X_MODE = 'I') then
404 X_LAST_UPDATED_BY := 1;
405 X_LAST_UPDATE_LOGIN := 0;
406 elsif (X_MODE = 'R') then
407 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
408 if X_LAST_UPDATED_BY is NULL then
409 X_LAST_UPDATED_BY := -1;
410 end if;
411 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
412 if X_LAST_UPDATE_LOGIN is NULL then
413 X_LAST_UPDATE_LOGIN := -1;
414 end if;
415 else
416 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
417 IGS_GE_MSG_STACK.ADD;
418 app_exception.raise_exception;
419 end if;
420 Before_DML(
421 p_action=>'INSERT',
422 x_rowid=>X_ROWID,
423 x_system_source_type=>X_SYSTEM_SOURCE_TYPE,
424 x_category_name=>X_CATEGORY_NAME,
425 x_mandatory_ind=>X_MANDATORY_IND,
426 x_ss_ind=>X_SS_IND,
427 x_display_sequence=>X_DISPLAY_SEQUENCE,
431 x_created_by=>X_LAST_UPDATED_BY,
428 x_ad_tab_name=>X_AD_TAB_NAME,
429 x_int_tab_name=>X_INT_TAB_NAME,
430 x_creation_date=>X_LAST_UPDATE_DATE,
432 x_last_update_date=>X_LAST_UPDATE_DATE,
433 x_last_updated_by=>X_LAST_UPDATED_BY,
434 x_last_update_login=>X_LAST_UPDATE_LOGIN,
435 x_closed_ind=>X_CLOSED_IND);
436 insert into IGS_AD_SYSSRC_CAT (
437 SYSTEM_SOURCE_TYPE
438 ,CATEGORY_NAME
439 ,MANDATORY_IND
440 ,SS_IND
441 ,DISPLAY_SEQUENCE
442 ,AD_TAB_NAME
443 ,INT_TAB_NAME
444 ,CREATION_DATE
445 ,CREATED_BY
446 ,LAST_UPDATE_DATE
447 ,LAST_UPDATED_BY
448 ,LAST_UPDATE_LOGIN
449 ,CLOSED_IND
450 ) values (
451 NEW_REFERENCES.SYSTEM_SOURCE_TYPE
452 ,NEW_REFERENCES.CATEGORY_NAME
453 ,NEW_REFERENCES.MANDATORY_IND
454 ,NULL
455 ,NULL
456 ,NEW_REFERENCES.AD_TAB_NAME
457 ,NEW_REFERENCES.INT_TAB_NAME
458 ,X_LAST_UPDATE_DATE
459 ,X_LAST_UPDATED_BY
460 ,X_LAST_UPDATE_DATE
461 ,X_LAST_UPDATED_BY
462 ,X_LAST_UPDATE_LOGIN
463 ,NVL(X_CLOSED_IND,'N')
464 );
465 open c;
466 fetch c into X_ROWID;
467 if (c%notfound) then
468 close c;
469 raise no_data_found;
470 end if;
471 close c;
472 After_DML (
473 p_action => 'INSERT' ,
474 x_rowid => X_ROWID );
475 end INSERT_ROW;
476 procedure LOCK_ROW (
477 X_ROWID in VARCHAR2,
478 x_SYSTEM_SOURCE_TYPE IN VARCHAR2,
479 x_CATEGORY_NAME IN VARCHAR2,
480 x_MANDATORY_IND IN VARCHAR2,
481 x_SS_IND IN VARCHAR2,
482 x_DISPLAY_SEQUENCE IN NUMBER,
483 x_AD_TAB_NAME IN VARCHAR2 DEFAULT NULL,
484 x_INT_TAB_NAME IN VARCHAR2 DEFAULT NULL,
485 X_CLOSED_IND IN VARCHAR2 DEFAULT 'N'
486 ) AS
487 /*************************************************************
488 Created By : amuthu
489 Date Created On : 16-May-2000
490 Purpose :
491 Know limitations, enhancements or remarks
492 Change History
493 Who When What
494 pkpatel 16-JUN-2001 Added processing for two newly added columns AD_TAB_NAME and INT_TAB_NAME
495 (reverse chronological order - newest change first)
496 ***************************************************************/
497
498 cursor c1 is select
499 SYSTEM_SOURCE_TYPE,
500 CATEGORY_NAME,
501 MANDATORY_IND,
502 SS_IND,
503 DISPLAY_SEQUENCE,
504 AD_TAB_NAME,
505 INT_TAB_NAME,
506 CLOSED_IND
507 from IGS_AD_SYSSRC_CAT
508 where ROWID = X_ROWID
509 for update nowait;
510 tlinfo c1%rowtype;
511 begin
512 open c1;
513 fetch c1 into tlinfo;
514 if (c1%notfound) then
515 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
516 IGS_GE_MSG_STACK.ADD;
517 close c1;
518 app_exception.raise_exception;
519 return;
520 end if;
521 close c1;
522 if ( (tlinfo.MANDATORY_IND = X_MANDATORY_IND)
523 AND ((tlinfo.AD_TAB_NAME = X_AD_TAB_NAME) OR ((tlinfo.AD_TAB_NAME IS NULL) AND (X_AD_TAB_NAME IS NULL)))
524 AND ((tlinfo.INT_TAB_NAME = X_INT_TAB_NAME) OR ((tlinfo.INT_TAB_NAME IS NULL) AND (X_INT_TAB_NAME IS NULL)))
525 AND ((tlinfo.CLOSED_IND = X_CLOSED_IND) OR ((tlinfo.CLOSED_IND IS NULL) AND (X_CLOSED_IND IS NULL)))
526 ) then
527 null;
528 else
529 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
530 IGS_GE_MSG_STACK.ADD;
531 app_exception.raise_exception;
532 end if;
533 return;
534 end LOCK_ROW;
535 Procedure UPDATE_ROW (
536 X_ROWID in VARCHAR2,
537 x_SYSTEM_SOURCE_TYPE IN VARCHAR2,
538 x_CATEGORY_NAME IN VARCHAR2,
539 x_MANDATORY_IND IN VARCHAR2,
540 x_SS_IND IN VARCHAR2,
541 x_DISPLAY_SEQUENCE IN NUMBER,
542 x_AD_TAB_NAME IN VARCHAR2 DEFAULT NULL,
543 x_INT_TAB_NAME IN VARCHAR2 DEFAULT NULL,
544 X_MODE in VARCHAR2 default 'R',
545 X_CLOSED_IND IN VARCHAR2 DEFAULT 'N'
546 ) AS
547 /*************************************************************
548 Created By : amuthu
549 Date Created On : 16-May-2000
550 Purpose :
551 Know limitations, enhancements or remarks
552 Change History
553 Who When What
554 pkpatel 16-JUN-2001 Added processing for two newly added columns AD_TAB_NAME and INT_TAB_NAME
555 (reverse chronological order - newest change first)
556 ***************************************************************/
557
558 X_LAST_UPDATE_DATE DATE ;
559 X_LAST_UPDATED_BY NUMBER ;
560 X_LAST_UPDATE_LOGIN NUMBER ;
561 begin
562 X_LAST_UPDATE_DATE := SYSDATE;
563 if(X_MODE = 'I') then
564 X_LAST_UPDATED_BY := 1;
565 X_LAST_UPDATE_LOGIN := 0;
566 elsif (X_MODE = 'R') then
567 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
568 if X_LAST_UPDATED_BY is NULL then
569 X_LAST_UPDATED_BY := -1;
570 end if;
571 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
572 if X_LAST_UPDATE_LOGIN is NULL then
573 X_LAST_UPDATE_LOGIN := -1;
574 end if;
575 else
576 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
577 IGS_GE_MSG_STACK.ADD;
578 app_exception.raise_exception;
579 end if;
580 Before_DML(
581 p_action=>'UPDATE',
582 x_rowid=>X_ROWID,
583 x_system_source_type=>X_SYSTEM_SOURCE_TYPE,
584 x_category_name=>X_CATEGORY_NAME,
585 x_mandatory_ind=>X_MANDATORY_IND,
586 x_ss_ind=>X_SS_IND,
587 x_display_sequence=>X_DISPLAY_SEQUENCE,
591 x_created_by=>X_LAST_UPDATED_BY,
588 x_ad_tab_name=>X_AD_TAB_NAME,
589 x_int_tab_name=>X_INT_TAB_NAME,
590 x_creation_date=>X_LAST_UPDATE_DATE,
592 x_last_update_date=>X_LAST_UPDATE_DATE,
593 x_last_updated_by=>X_LAST_UPDATED_BY,
594 x_last_update_login=>X_LAST_UPDATE_LOGIN,
595 x_closed_ind=>X_CLOSED_IND);
596
597 update IGS_AD_SYSSRC_CAT set
598 mandatory_ind=X_MANDATORY_IND,
599 ss_ind=NULL,
600 display_sequence=NULL,
601 ad_tab_name=X_AD_TAB_NAME,
602 int_tab_name=X_INT_TAB_NAME,
603 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
604 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
605 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
606 CLOSED_IND = NVL(X_CLOSED_IND,'N')
607 where ROWID = X_ROWID;
608 if (sql%notfound) then
609 raise no_data_found;
610 end if;
611
612 After_DML (
613 p_action => 'UPDATE' ,
614 x_rowid => X_ROWID
615 );
616 end UPDATE_ROW;
617 procedure ADD_ROW (
618 X_ROWID in out NOCOPY VARCHAR2,
619 x_SYSTEM_SOURCE_TYPE IN VARCHAR2,
620 x_CATEGORY_NAME IN VARCHAR2,
621 x_MANDATORY_IND IN VARCHAR2,
622 x_SS_IND IN VARCHAR2,
623 x_DISPLAY_SEQUENCE IN NUMBER,
624 x_AD_TAB_NAME IN VARCHAR2 DEFAULT NULL,
625 x_INT_TAB_NAME IN VARCHAR2 DEFAULT NULL,
626 X_MODE in VARCHAR2 default 'R',
627 X_CLOSED_IND IN VARCHAR2 DEFAULT 'N'
628 ) AS
629 /*************************************************************
630 Created By : amuthu
631 Date Created On : 16-May-2000
632 Purpose :
633 Know limitations, enhancements or remarks
634 Change History
635 Who When What
636 pkpatel 16-JUN-2001 Added processing for two newly added columns AD_TAB_NAME and INT_TAB_NAME
637 (reverse chronological order - newest change first)
638 ***************************************************************/
639
640 cursor c1 is select ROWID from IGS_AD_SYSSRC_CAT
641 where CATEGORY_NAME= X_CATEGORY_NAME
642 and SYSTEM_SOURCE_TYPE = X_SYSTEM_SOURCE_TYPE
643 ;
644 begin
645 open c1;
646 fetch c1 into X_ROWID;
647 if (c1%notfound) then
648 close c1;
649 INSERT_ROW (
650 X_ROWID,
651 X_SYSTEM_SOURCE_TYPE,
652 X_CATEGORY_NAME,
653 X_MANDATORY_IND,
654 X_SS_IND,
655 X_DISPLAY_SEQUENCE,
656 X_AD_TAB_NAME,
657 X_INT_TAB_NAME,
658 X_MODE,
659 X_CLOSED_IND);
660 return;
661 end if;
662 close c1;
663 UPDATE_ROW (
664 X_ROWID,
665 X_SYSTEM_SOURCE_TYPE,
666 X_CATEGORY_NAME,
667 X_MANDATORY_IND,
668 X_SS_IND,
669 X_DISPLAY_SEQUENCE,
670 X_AD_TAB_NAME,
671 X_INT_TAB_NAME,
672 X_MODE,
673 X_CLOSED_IND);
674 end ADD_ROW;
675 procedure DELETE_ROW (
676 X_ROWID in VARCHAR2
677 ) AS
678 /*************************************************************
679 Created By : amuthu
680 Date Created On : 16-May-2000
681 Purpose :
682 Know limitations, enhancements or remarks
683 Change History
684 Who When What
685
686 (reverse chronological order - newest change first)
687 ***************************************************************/
688
689 begin
690 Before_DML (
691 p_action => 'DELETE',
692 x_rowid => X_ROWID
693 );
694 delete from IGS_AD_SYSSRC_CAT
695 where ROWID = X_ROWID;
696 if (sql%notfound) then
697 raise no_data_found;
698 end if;
699 After_DML (
700 p_action => 'DELETE',
701 x_rowid => X_ROWID
702 );
703 end DELETE_ROW;
704 END igs_ad_syssrc_cat_pkg;
[Home] [Help]