eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_PE_ABORG_TORESCD_PKG

Source


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