1 PACKAGE BODY igs_or_org_notes_pkg AS
2 /* $Header: IGSOI18B.pls 115.8 2003/10/30 13:29:55 rghosh ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_or_org_notes%RowType;
5 new_references igs_or_org_notes%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_structure_id IN VARCHAR2 DEFAULT NULL,
11 x_org_structure_type IN VARCHAR2 DEFAULT NULL,
12 x_org_note_sequence IN NUMBER DEFAULT NULL,
13 x_org_note_type IN VARCHAR2 DEFAULT NULL,
14 x_start_date IN DATE DEFAULT NULL,
15 x_end_date IN DATE DEFAULT NULL,
16 x_note_text 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 ) AS
23
24 /*************************************************************
25 Created By :rareddy
26 Date Created By :
27 Purpose : initilizing column values
28 Know limitations, enhancements or remarks
29 Change History
30 Who When What
31
32 (reverse chronological order - newest change first)
33 ***************************************************************/
34
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM IGS_OR_ORG_NOTES
38 WHERE rowid = x_rowid;
39
40 BEGIN
41
42 l_rowid := x_rowid;
43
44 -- Code for setting the Old and New Reference Values.
45 -- Populate Old Values.
46 Open cur_old_ref_values;
47 Fetch cur_old_ref_values INTO old_references;
48 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
49 Close cur_old_ref_values;
50 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
51 IGS_GE_MSG_STACK.ADD;
52 App_Exception.Raise_Exception;
53 Return;
54 END IF;
55 Close cur_old_ref_values;
56
57 -- Populate New Values.
58 new_references.org_structure_id := x_org_structure_id;
59 new_references.org_structure_type := x_org_structure_type;
60 new_references.org_note_sequence := x_org_note_sequence;
61 new_references.org_note_type := x_org_note_type;
62 new_references.start_date := x_start_date;
63 new_references.end_date := x_end_date;
64 new_references.note_text := x_note_text;
65 IF (p_action = 'UPDATE') THEN
66 new_references.creation_date := old_references.creation_date;
67 new_references.created_by := old_references.created_by;
68 ELSE
69 new_references.creation_date := x_creation_date;
70 new_references.created_by := x_created_by;
71 END IF;
72 new_references.last_update_date := x_last_update_date;
73 new_references.last_updated_by := x_last_updated_by;
74 new_references.last_update_login := x_last_update_login;
75
76 END Set_Column_Values;
77
78 PROCEDURE Check_Constraints (
79 Column_Name IN VARCHAR2 DEFAULT NULL,
80 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
81 /*************************************************************
82 Created By :rareddy
83 Date Created By :
84 Purpose : item level validation
85 Know limitations, enhancements or remarks
86 Change History
87 Who When What
88
89 (reverse chronological order - newest change first)
90 ***************************************************************/
91
92 BEGIN
93
94 IF column_name IS NULL THEN
95 NULL;
96 NULL;
97 END IF;
98
99
100
101
102 END Check_Constraints;
103
104 PROCEDURE Check_Parent_Existance AS
105 /*************************************************************
106 Created By :rareddy
107 Date Created By :
108 Purpose : when a DML is made in child
109 Know limitations, enhancements or remarks
110 Change History
111 Who When What
112
113 (reverse chronological order - newest change first)
114 ***************************************************************/
115
116 BEGIN
117
118 IF (((old_references.org_note_type = new_references.org_note_type)) OR
119 ((new_references.org_note_type IS NULL))) THEN
120 NULL;
121 ELSIF NOT Igs_Or_Org_Note_Type_Pkg.Get_PK_For_Validation (
122 new_references.org_note_type
123 ) THEN
124 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
125 IGS_GE_MSG_STACK.ADD;
126 App_Exception.Raise_Exception;
127 END IF;
128 IF NOT Igs_Lookups_View_Pkg.Get_Pk_For_Validation('ORG_STRUCTURE_TYPE',
129 new_references.org_structure_type) THEN
130 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
131 IGS_GE_MSG_STACK.ADD;
132 APP_EXCEPTION.RAISE_EXCEPTION;
133 END IF;
134
135 IF new_references.org_structure_type = 'INSTITUTE' THEN
136 IF NOT Igs_Or_Institution_Pkg.Get_Pk_For_Validation(new_references.org_structure_id) THEN
137 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
138 IGS_GE_MSG_STACK.ADD;
139 APP_EXCEPTION.RAISE_EXCEPTION;
140 END IF;
141 ELSIF new_references.org_structure_type = 'LOCATION' THEN
142 IF NOT Igs_Ad_Location_Pkg.Get_Pk_For_Validation(new_references.org_structure_id,
143 'N') THEN
144 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
145 IGS_GE_MSG_STACK.ADD;
146 APP_EXCEPTION.RAISE_EXCEPTION;
147 END IF;
148 ELSIF new_references.org_structure_type = 'ORG_UNIT' THEN
149 IF NOT Igs_Or_Unit_Pkg.Get_Pk_For_Str_Validation(new_references.org_structure_id) THEN
150 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
151 IGS_GE_MSG_STACK.ADD;
152 APP_EXCEPTION.RAISE_EXCEPTION;
153 END IF;
154 END IF;
155
156 END Check_Parent_Existance;
157
158 FUNCTION Get_PK_For_Validation (
159 x_org_structure_type IN VARCHAR2,
160 x_org_note_sequence IN NUMBER,
161 x_org_note_type IN VARCHAR2,
162 x_org_structure_id IN VARCHAR2
163 ) RETURN BOOLEAN AS
164
165 /*************************************************************
166 Created By :rareddy
167 Date Created By :
168 Purpose : PK check
169 Know limitations, enhancements or remarks
170 Change History
171 Who When What
172
173 (reverse chronological order - newest change first)
174 ***************************************************************/
175
176 CURSOR cur_rowid IS
177 SELECT rowid
178 FROM igs_or_org_notes
179 WHERE org_structure_type = x_org_structure_type
180 AND org_note_sequence = x_org_note_sequence
181 AND org_note_type = x_org_note_type
182 AND org_structure_id = x_org_structure_id
183 FOR UPDATE NOWAIT;
184
185 lv_rowid cur_rowid%RowType;
186
187 BEGIN
188
189 Open cur_rowid;
190 Fetch cur_rowid INTO lv_rowid;
191 IF (cur_rowid%FOUND) THEN
192 Close cur_rowid;
193 Return(TRUE);
194 ELSE
195 Close cur_rowid;
196 Return(FALSE);
197 END IF;
198 END Get_PK_For_Validation;
199
200 PROCEDURE Get_FK_Igs_Or_Org_Note_Type (
201 x_org_notes_type IN VARCHAR2
202 ) AS
203
204 /*************************************************************
205 Created By :rareddy
206 Date Created By :
207 Purpose : FK check
208 Know limitations, enhancements or remarks
209 Change History
210 Who When What
211
212 (reverse chronological order - newest change first)
213 ***************************************************************/
214
215 CURSOR cur_rowid IS
216 SELECT rowid
217 FROM igs_or_org_notes
218 WHERE org_note_type = x_org_notes_type ;
219
220 lv_rowid cur_rowid%RowType;
221
222 BEGIN
223
224 Open cur_rowid;
225 Fetch cur_rowid INTO lv_rowid;
226 IF (cur_rowid%FOUND) THEN
227 Close cur_rowid;
228 Fnd_Message.Set_Name ('IGS', 'IGS_OR_OON_ONT_FK');
229 IGS_GE_MSG_STACK.ADD;
230 App_Exception.Raise_Exception;
231 Return;
232 END IF;
233 Close cur_rowid;
234
235 END Get_FK_Igs_Or_Org_Note_Type;
236
237 PROCEDURE GET_FK_IGS_OR_INSTITUTION (
238 x_institution_cd IN VARCHAR2
239 ) AS
240 CURSOR cur_rowid IS
241 SELECT ROWID
242 FROM IGS_OR_ORG_NOTES
243 WHERE (
244 (org_structure_id = x_institution_cd) AND (org_structure_type = 'INSTITUTE')
245 );
246 lv_rowid cur_rowid%ROWTYPE;
247 BEGIN
248 OPEN cur_rowid;
249 FETCH cur_rowid INTO lv_rowid;
250 IF (cur_rowid%FOUND) THEN
251 CLOSE cur_rowid;
252 Fnd_Message.Set_Name ('IGS', 'IGS_OR_OON_INS_FK');
253 IGS_GE_MSG_STACK.ADD;
254 App_Exception.Raise_Exception;
255 RETURN;
256 END IF;
257 CLOSE cur_rowid;
258 END GET_FK_IGS_OR_INSTITUTION;
259
260 PROCEDURE GET_FK_IGS_AD_LOCATION (
261 x_location_cd IN VARCHAR2
262 ) AS
263 CURSOR cur_rowid IS
264 SELECT ROWID
265 FROM IGS_OR_ORG_NOTES
266 WHERE (
267 (org_structure_id = x_location_cd) AND (org_structure_type = 'LOCATION')
268 );
269 lv_rowid cur_rowid%ROWTYPE;
270 BEGIN
271 OPEN cur_rowid;
272 FETCH cur_rowid INTO lv_rowid;
273 IF (cur_rowid%FOUND) THEN
274 CLOSE cur_rowid;
275 Fnd_Message.Set_Name ('IGS', 'IGS_OR_OON_LOC_FK');
276 IGS_GE_MSG_STACK.ADD;
277 App_Exception.Raise_Exception;
278 RETURN;
279 END IF;
280 CLOSE cur_rowid;
281 END GET_FK_IGS_AD_LOCATION;
282
283 PROCEDURE GET_FK_IGS_OR_UNIT (
284 x_org_unit_cd IN VARCHAR2
285 ) AS
286 CURSOR cur_rowid IS
287 SELECT ROWID
288 FROM IGS_OR_ORG_NOTES
289 WHERE (
290 (org_structure_id = x_org_unit_cd) AND (org_structure_type = 'ORG_UNIT')
291 );
292 lv_rowid cur_rowid%ROWTYPE;
293 BEGIN
294 OPEN cur_rowid;
295 FETCH cur_rowid INTO lv_rowid;
296 IF (cur_rowid%FOUND) THEN
297 CLOSE cur_rowid;
298 Fnd_Message.Set_Name ('IGS', 'IGS_OR_OON_OU_FK');
299 IGS_GE_MSG_STACK.ADD;
300 App_Exception.Raise_Exception;
301 RETURN;
302 END IF;
303 CLOSE cur_rowid;
304 END GET_FK_IGS_OR_UNIT;
305
306
307
308 PROCEDURE Before_DML (
309 p_action IN VARCHAR2,
310 x_rowid IN VARCHAR2 DEFAULT NULL,
311 x_org_structure_id IN VARCHAR2 DEFAULT NULL,
312 x_org_structure_type IN VARCHAR2 DEFAULT NULL,
313 x_org_note_sequence IN NUMBER DEFAULT NULL,
314 x_org_note_type IN VARCHAR2 DEFAULT NULL,
315 x_start_date IN DATE DEFAULT NULL,
316 x_end_date IN DATE DEFAULT NULL,
317 x_note_text IN VARCHAR2 DEFAULT NULL,
318 x_creation_date IN DATE DEFAULT NULL,
319 x_created_by IN NUMBER DEFAULT NULL,
320 x_last_update_date IN DATE DEFAULT NULL,
321 x_last_updated_by IN NUMBER DEFAULT NULL,
322 x_last_update_login IN NUMBER DEFAULT NULL
323 ) AS
324 /*************************************************************
325 Created By :rareddy
326 Date Created By :
327 Purpose : before any DML.,
328 Know limitations, enhancements or remarks
329 Change History
330 Who When What
331
332 (reverse chronological order - newest change first)
333 ***************************************************************/
334
335 BEGIN
336
337 Set_Column_Values (
338 p_action,
339 x_rowid,
340 x_org_structure_id,
341 x_org_structure_type,
342 x_org_note_sequence,
343 x_org_note_type,
344 x_start_date,
345 x_end_date,
346 x_note_text,
347 x_creation_date,
348 x_created_by,
349 x_last_update_date,
350 x_last_updated_by,
351 x_last_update_login
352 );
353
354 IF (p_action = 'INSERT') THEN
355 -- Call all the procedures related to Before Insert.
356 Null;
357 IF Get_Pk_For_Validation(
358 new_references.org_structure_type,
359 new_references.org_note_sequence,
360 new_references.org_note_type,
361 new_references.org_structure_id) THEN
362 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
363 IGS_GE_MSG_STACK.ADD;
364 App_Exception.Raise_Exception;
365 END IF;
366 Check_Constraints;
367 Check_Parent_Existance;
368 ELSIF (p_action = 'UPDATE') THEN
369 -- Call all the procedures related to Before Update.
370 Null;
371 Check_Constraints;
372 Check_Parent_Existance;
373 ELSIF (p_action = 'DELETE') THEN
374 -- Call all the procedures related to Before Delete.
375 Null;
376 ELSIF (p_action = 'VALIDATE_INSERT') THEN
377 -- Call all the procedures related to Before Insert.
378 IF Get_PK_For_Validation (
379 new_references.org_structure_type,
380 new_references.org_note_sequence,
381 new_references.org_note_type,
382 new_references.org_structure_id) THEN
383 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
384 IGS_GE_MSG_STACK.ADD;
385 App_Exception.Raise_Exception;
386 END IF;
387 Check_Constraints;
388 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
389 Check_Constraints;
390 ELSIF (p_action = 'VALIDATE_DELETE') THEN
391 Null;
392 END IF;
393
394 END Before_DML;
395
396 PROCEDURE After_DML (
397 p_action IN VARCHAR2,
398 x_rowid IN VARCHAR2
399 ) IS
400 /*************************************************************
401 Created By :rareddy
402 Date Created By :
403 Purpose : after any DML.,
404 Know limitations, enhancements or remarks
405 Change History
406 Who When What
407
408 (reverse chronological order - newest change first)
409 ***************************************************************/
410
411 BEGIN
412
413 l_rowid := x_rowid;
414
415 IF (p_action = 'INSERT') THEN
416 -- Call all the procedures related to After Insert.
417 Null;
418 ELSIF (p_action = 'UPDATE') THEN
419 -- Call all the procedures related to After Update.
420 Null;
421 ELSIF (p_action = 'DELETE') THEN
422 -- Call all the procedures related to After Delete.
423 Null;
424 END IF;
425
426 END After_DML;
427
428 procedure INSERT_ROW (
429 X_ROWID in out NOCOPY VARCHAR2,
430 x_ORG_STRUCTURE_ID IN VARCHAR2,
431 x_ORG_STRUCTURE_TYPE IN VARCHAR2,
432 x_ORG_NOTE_SEQUENCE IN OUT NOCOPY NUMBER,
433 x_ORG_NOTE_TYPE IN VARCHAR2,
434 x_START_DATE IN DATE,
435 x_END_DATE IN DATE,
436 x_NOTE_TEXT IN VARCHAR2,
437 X_MODE in VARCHAR2 default 'R'
438 ) AS
439 /*************************************************************
440 Created By :rareddy
441 Date Created By :
442 Purpose : BEFORE AND AFTER any INSERT
443 Know limitations, enhancements or remarks
444 Change History
445 Who When What
446
447 (reverse chronological order - newest change first)
448 ***************************************************************/
449
450 cursor C is select ROWID from IGS_OR_ORG_NOTES
451 where ORG_STRUCTURE_TYPE= X_ORG_STRUCTURE_TYPE
452 and ORG_NOTE_SEQUENCE = X_ORG_NOTE_SEQUENCE
453 and ORG_NOTE_TYPE = X_ORG_NOTE_TYPE
454 and ORG_STRUCTURE_ID = X_ORG_STRUCTURE_ID
455 ;
456 X_LAST_UPDATE_DATE DATE ;
457 X_LAST_UPDATED_BY NUMBER ;
458 X_LAST_UPDATE_LOGIN NUMBER ;
459 begin
460 X_LAST_UPDATE_DATE := SYSDATE;
461 if(X_MODE = 'I') then
462 X_LAST_UPDATED_BY := 1;
463 X_LAST_UPDATE_LOGIN := 0;
464 elsif (X_MODE = 'R') then
465 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
466 if X_LAST_UPDATED_BY is NULL then
467 X_LAST_UPDATED_BY := -1;
468 end if;
469 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
470 if X_LAST_UPDATE_LOGIN is NULL then
471 X_LAST_UPDATE_LOGIN := -1;
472 end if;
473 else
474 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
475 IGS_GE_MSG_STACK.ADD;
476 app_exception.raise_exception;
477 end if;
478 SELECT
479 IGS_OR_ORG_NOTE_SEQUENCE_S.NEXTVAL
480 INTO
481 x_org_note_sequence
482 FROM
483 dual;
484
485 Before_DML(
486 p_action=>'INSERT',
487 x_rowid=>X_ROWID,
488 x_org_structure_id=>X_ORG_STRUCTURE_ID,
489 x_org_structure_type=>X_ORG_STRUCTURE_TYPE,
490 x_org_note_sequence=>X_ORG_NOTE_SEQUENCE,
491 x_org_note_type=>X_ORG_NOTE_TYPE,
492 x_start_date=>X_START_DATE,
493 x_end_date=>X_END_DATE,
494 x_note_text=>X_NOTE_TEXT,
495 x_creation_date=>X_LAST_UPDATE_DATE,
496 x_created_by=>X_LAST_UPDATED_BY,
497 x_last_update_date=>X_LAST_UPDATE_DATE,
498 x_last_updated_by=>X_LAST_UPDATED_BY,
499 x_last_update_login=>X_LAST_UPDATE_LOGIN);
500 insert into IGS_OR_ORG_NOTES (
501 ORG_STRUCTURE_ID
502 ,ORG_STRUCTURE_TYPE
503 ,ORG_NOTE_SEQUENCE
504 ,ORG_NOTE_TYPE
505 ,START_DATE
506 ,END_DATE
507 ,NOTE_TEXT
508 ,CREATION_DATE
509 ,CREATED_BY
510 ,LAST_UPDATE_DATE
511 ,LAST_UPDATED_BY
512 ,LAST_UPDATE_LOGIN
513 ) values (
514 NEW_REFERENCES.ORG_STRUCTURE_ID
515 ,NEW_REFERENCES.ORG_STRUCTURE_TYPE
516 ,NEW_REFERENCES.ORG_NOTE_SEQUENCE
517 ,NEW_REFERENCES.ORG_NOTE_TYPE
518 ,NEW_REFERENCES.START_DATE
519 ,NEW_REFERENCES.END_DATE
520 ,NEW_REFERENCES.NOTE_TEXT
521 ,X_LAST_UPDATE_DATE
522 ,X_LAST_UPDATED_BY
523 ,X_LAST_UPDATE_DATE
524 ,X_LAST_UPDATED_BY
525 ,X_LAST_UPDATE_LOGIN
526 );
527 open c;
528 fetch c into X_ROWID;
529 if (c%notfound) then
530 close c;
531 raise no_data_found;
532 end if;
533 close c;
534 After_DML (
535 p_action => 'INSERT' ,
536 x_rowid => X_ROWID );
537 end INSERT_ROW;
538 procedure LOCK_ROW (
539 X_ROWID in VARCHAR2,
540 x_ORG_STRUCTURE_ID IN VARCHAR2,
541 x_ORG_STRUCTURE_TYPE IN VARCHAR2,
542 x_ORG_NOTE_SEQUENCE IN NUMBER,
543 x_ORG_NOTE_TYPE IN VARCHAR2,
544 x_START_DATE IN DATE,
545 x_END_DATE IN DATE,
546 x_NOTE_TEXT IN VARCHAR2 ) AS
547 /*************************************************************
548 Created By :rareddy
549 Date Created By :
550 Purpose : before and after a lock is made
551 Know limitations, enhancements or remarks
552 Change History
553 Who When What
554
555 (reverse chronological order - newest change first)
556 ***************************************************************/
557
558 cursor c1 is select
559 START_DATE
560 , END_DATE
561 , NOTE_TEXT
562 from IGS_OR_ORG_NOTES
563 where ROWID = X_ROWID
564 for update nowait;
565 tlinfo c1%rowtype;
566 begin
567 open c1;
568 fetch c1 into tlinfo;
569 if (c1%notfound) then
570 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
571 IGS_GE_MSG_STACK.ADD;
572 close c1;
573 app_exception.raise_exception;
574 return;
575 end if;
576 close c1;
577 if ( ( tlinfo.START_DATE = X_START_DATE)
578 AND ((tlinfo.END_DATE = X_END_DATE)
579 OR ((tlinfo.END_DATE is null)
580 AND (X_END_DATE is null)))
581 AND ((tlinfo.NOTE_TEXT = X_NOTE_TEXT)
582 OR ((tlinfo.NOTE_TEXT is null)
583 AND (X_NOTE_TEXT is null)))
584 ) then
585 null;
586 else
587 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
588 IGS_GE_MSG_STACK.ADD;
589 app_exception.raise_exception;
590 end if;
591 return;
592 end LOCK_ROW;
593 Procedure UPDATE_ROW (
594 X_ROWID in VARCHAR2,
595 x_ORG_STRUCTURE_ID IN VARCHAR2,
596 x_ORG_STRUCTURE_TYPE IN VARCHAR2,
597 x_ORG_NOTE_SEQUENCE IN NUMBER,
598 x_ORG_NOTE_TYPE IN VARCHAR2,
599 x_START_DATE IN DATE,
600 x_END_DATE IN DATE,
601 x_NOTE_TEXT IN VARCHAR2,
602 X_MODE in VARCHAR2 default 'R'
603 ) AS
604 /*************************************************************
605 Created By :rareddy
606 Date Created By :
607 Purpose :before and after a row update
608 Know limitations, enhancements or remarks
609 Change History
610 Who When What
611
612 (reverse chronological order - newest change first)
613 ***************************************************************/
614
615 X_LAST_UPDATE_DATE DATE ;
616 X_LAST_UPDATED_BY NUMBER ;
617 X_LAST_UPDATE_LOGIN NUMBER ;
618 begin
619 X_LAST_UPDATE_DATE := SYSDATE;
620 if(X_MODE = 'I') then
621 X_LAST_UPDATED_BY := 1;
622 X_LAST_UPDATE_LOGIN := 0;
623 elsif (X_MODE = 'R') then
624 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
625 if X_LAST_UPDATED_BY is NULL then
626 X_LAST_UPDATED_BY := -1;
627 end if;
628 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
629 if X_LAST_UPDATE_LOGIN is NULL then
630 X_LAST_UPDATE_LOGIN := -1;
631 end if;
632 else
633 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
634 IGS_GE_MSG_STACK.ADD;
635 app_exception.raise_exception;
636 end if;
637 Before_DML(
638 p_action=>'UPDATE',
639 x_rowid=>X_ROWID,
640 x_org_structure_id=>X_ORG_STRUCTURE_ID,
641 x_org_structure_type=>X_ORG_STRUCTURE_TYPE,
642 x_org_note_sequence=>X_ORG_NOTE_SEQUENCE,
643 x_org_note_type=>X_ORG_NOTE_TYPE,
644 x_start_date=>X_START_DATE,
645 x_end_date=>X_END_DATE,
646 x_note_text=>X_NOTE_TEXT,
647 x_creation_date=>X_LAST_UPDATE_DATE,
648 x_created_by=>X_LAST_UPDATED_BY,
649 x_last_update_date=>X_LAST_UPDATE_DATE,
650 x_last_updated_by=>X_LAST_UPDATED_BY,
651 x_last_update_login=>X_LAST_UPDATE_LOGIN);
652 update IGS_OR_ORG_NOTES set
653 START_DATE = NEW_REFERENCES.START_DATE,
654 END_DATE = NEW_REFERENCES.END_DATE,
655 NOTE_TEXT = NEW_REFERENCES.NOTE_TEXT,
656 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
657 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
658 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
659 where ROWID = X_ROWID;
660 if (sql%notfound) then
661 raise no_data_found;
662 end if;
663
664 After_DML (
665 p_action => 'UPDATE' ,
666 x_rowid => X_ROWID
667 );
668 end UPDATE_ROW;
669 procedure ADD_ROW (
670 X_ROWID in out NOCOPY VARCHAR2,
671 x_ORG_STRUCTURE_ID IN VARCHAR2,
672 x_ORG_STRUCTURE_TYPE IN VARCHAR2,
673 x_ORG_NOTE_SEQUENCE IN NUMBER,
674 x_ORG_NOTE_TYPE IN VARCHAR2,
675 x_START_DATE IN DATE,
676 x_END_DATE IN DATE,
677 x_NOTE_TEXT IN VARCHAR2,
678 X_MODE in VARCHAR2 default 'R'
679 ) AS
680 /*************************************************************
681 Created By :rareddy
682 Date Created By :
683 Purpose :before and after row addition
684 Know limitations, enhancements or remarks
685 Change History
686 Who When What
687
688 (reverse chronological order - newest change first)
689 ***************************************************************/
690
691 cursor c1 is select ROWID from IGS_OR_ORG_NOTES
692 where ORG_STRUCTURE_TYPE= X_ORG_STRUCTURE_TYPE
693 and ORG_NOTE_SEQUENCE = X_ORG_NOTE_SEQUENCE
694 and ORG_NOTE_TYPE = X_ORG_NOTE_TYPE
695 and ORG_STRUCTURE_ID = X_ORG_STRUCTURE_ID
696 ;
697 XX_ORG_NOTE_SEQUENCE NUMBER := X_ORG_NOTE_SEQUENCE;
698 begin
699 open c1;
700 fetch c1 into X_ROWID;
701 if (c1%notfound) then
702 close c1;
703 INSERT_ROW (
704 X_ROWID,
705 X_ORG_STRUCTURE_ID,
706 X_ORG_STRUCTURE_TYPE,
707 XX_ORG_NOTE_SEQUENCE,
708 X_ORG_NOTE_TYPE,
709 X_START_DATE,
710 X_END_DATE,
711 X_NOTE_TEXT,
712 X_MODE );
713 return;
714 end if;
715 close c1;
716 UPDATE_ROW (
717 X_ROWID,
718 X_ORG_STRUCTURE_ID,
719 X_ORG_STRUCTURE_TYPE,
720 X_ORG_NOTE_SEQUENCE,
721 X_ORG_NOTE_TYPE,
722 X_START_DATE,
723 X_END_DATE,
724 X_NOTE_TEXT,
725 X_MODE );
726 end ADD_ROW;
727 procedure DELETE_ROW (
728 X_ROWID in VARCHAR2
729 ) AS
730 /*************************************************************
731 Created By :rareddy
732 Date Created By :
733 Purpose : before and after a delete
734 Know limitations, enhancements or remarks
735 Change History
736 Who When What
737
738 (reverse chronological order - newest change first)
739 ***************************************************************/
740
741 begin
742 Before_DML (
743 p_action => 'DELETE',
744 x_rowid => X_ROWID
745 );
746 delete from IGS_OR_ORG_NOTES
747 where ROWID = X_ROWID;
748 if (sql%notfound) then
749 raise no_data_found;
750 end if;
751 After_DML (
752 p_action => 'DELETE',
753 x_rowid => X_ROWID
754 );
755 end DELETE_ROW;
756 END igs_or_org_notes_pkg;
[Home] [Help]