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_SUBURB_POSTCD_PKG

Source


1 package body IGS_PE_SUBURB_POSTCD_PKG AS
2     /* $Header: IGSNI36B.pls 115.4 2002/11/29 01:23:06 nsidana ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_PE_SUBURB_POSTCD%RowType;
6   new_references IGS_PE_SUBURB_POSTCD%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_postcode IN NUMBER DEFAULT NULL,
12     x_suburbs IN VARCHAR2 DEFAULT NULL,
13     x_closed_ind IN VARCHAR2 DEFAULT NULL,
14     x_creation_date IN DATE DEFAULT NULL,
15     x_created_by IN NUMBER DEFAULT NULL,
16     x_last_update_date IN DATE DEFAULT NULL,
17     x_last_updated_by IN NUMBER DEFAULT NULL,
18     x_last_update_login IN NUMBER DEFAULT NULL
19   ) AS
20 
21     CURSOR cur_old_ref_values IS
22       SELECT   *
23       FROM     IGS_PE_SUBURB_POSTCD
24       WHERE    rowid = x_rowid;
25 
26   BEGIN
27 
28     l_rowid := x_rowid;
29 
30     -- Code for setting the Old and New Reference Values.
31     -- Populate Old Values.
32     Open cur_old_ref_values;
33     Fetch cur_old_ref_values INTO old_references;
34     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN( 'INSERT','VALIDATE_INSERT')) THEN
35       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
36       IGS_GE_MSG_STACK.ADD;
37       Close cur_old_ref_values;
38       App_Exception.Raise_Exception;
39       Return;
40     END IF;
41     Close cur_old_ref_values;
42 
43     -- Populate New Values.
44     new_references.postcode := x_postcode;
45     new_references.suburbs := x_suburbs;
46     new_references.closed_ind := x_closed_ind;
47     IF (p_action = 'UPDATE') THEN
48       new_references.creation_date := old_references.creation_date;
49       new_references.created_by := old_references.created_by;
50     ELSE
51       new_references.creation_date := x_creation_date;
52       new_references.created_by := x_created_by;
53     END IF;
54     new_references.last_update_date := x_last_update_date;
55     new_references.last_updated_by := x_last_updated_by;
56     new_references.last_update_login := x_last_update_login;
57 
58   END Set_Column_Values;
59 
60   -- Trigger description :-
61   -- "OSS_TST".trg_sp_br_d
62   -- BEFORE DELETE
63   -- ON IGS_PE_SUBURB_POSTCD
64   -- FOR EACH ROW
65 
66   PROCEDURE BeforeRowDelete1(
67     p_inserting IN BOOLEAN DEFAULT FALSE,
68     p_updating IN BOOLEAN DEFAULT FALSE,
69     p_deleting IN BOOLEAN DEFAULT FALSE
70     ) AS
71 v_message_name  varchar2(30);
72   BEGIN
73 	-- Validate if value is used on another table that does
74 	-- not have a foreign key to this table.  Eg. IGS_PE_PERSON Statistics
75 	IF IGS_EN_VAL_SP.enrp_val_sp_del (
76 			old_references.postcode,
77 			v_message_name) = FALSE THEN
78 		Fnd_Message.Set_Name('IGS', v_message_name);
79 		IGS_GE_MSG_STACK.ADD;
80                          App_Exception.Raise_Exception;
81 	END IF;
82 
83 
84   END BeforeRowDelete1;
85 
86 
87     PROCEDURE Check_Constraints (
88  Column_Name	IN	VARCHAR2	DEFAULT NULL,
89  Column_Value 	IN	VARCHAR2	DEFAULT NULL
90  )
91  AS
92  BEGIN
93     IF  column_name is null then
94      NULL;
95 
96  ELSIF upper(Column_name) = 'POSTCODE' then
97      new_references.postcode := IGS_GE_NUMBER.to_num(column_value);
98 ELSIF upper(Column_name) = 'CLOSED_IND' then
99      new_references.closed_ind := column_value;
100   END IF;
101 IF upper(column_name) = 'POSTCODE' OR
102      column_name is null Then
103      IF new_references.postcode < 0 OR new_references.postcode > 9999 Then
104        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
105        IGS_GE_MSG_STACK.ADD;
106        App_Exception.Raise_Exception;
107                    END IF;
108          END IF;
109  IF upper(column_name) = 'CLOSED_IND' OR
110      column_name is null Then
111      IF new_references.closed_ind NOT IN ( 'Y' , 'N' )Then
112        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
113        IGS_GE_MSG_STACK.ADD;
114        App_Exception.Raise_Exception;
115                    END IF;
116               END IF;
117 
118  END Check_Constraints;
119 
120 
121   PROCEDURE Check_Child_Existance AS
122   BEGIN
123   NULL;
124 /* The call to the Child tables has been intentionally commented because the
125    column Postcode has been removed from all these forms */
126 
127    /* IGS_OR_INST_ADDR_PKG.GET_FK_IGS_PE_SUBURB_POSTCD (
128       old_references.postcode
129       );
130 
131     IGS_AD_LOCATION_ADDR_PKG.GET_FK_IGS_PE_SUBURB_POSTCD (
132       old_references.postcode
133       );
134 
135     IGS_OR_ADDR_PKG.GET_FK_IGS_PE_SUBURB_POSTCD (
136       old_references.postcode
137       );
138 
139     IGS_PE_PERSON_ADDR_PKG.GET_FK_IGS_PE_SUBURB_POSTCD (
140       old_references.postcode
141       );
142 
143     IGS_GR_VENUE_ADDR_PKG.GET_FK_IGS_PE_SUBURB_POSTCD (
144       old_references.postcode
145       ); */
146 
147   END Check_Child_Existance;
148 
149   FUNCTION Get_PK_For_Validation (
150     x_postcode IN NUMBER
151     ) RETURN BOOLEAN AS
152 
153     CURSOR cur_rowid IS
154       SELECT   rowid
155       FROM     IGS_PE_SUBURB_POSTCD
156       WHERE    postcode = x_postcode
157       FOR UPDATE NOWAIT;
158 
159     lv_rowid cur_rowid%RowType;
160 
161   BEGIN
162 
163     Open cur_rowid;
164     Fetch cur_rowid INTO lv_rowid;
165      IF (cur_rowid%FOUND) THEN
166        Close cur_rowid;
167        Return (TRUE);
168  	ELSE
169        Close cur_rowid;
170        Return (FALSE);
171     END IF;
172   END Get_PK_For_Validation;
173 
174   PROCEDURE Before_DML (
175     p_action IN VARCHAR2,
176     x_rowid IN VARCHAR2 DEFAULT NULL,
177     x_postcode IN NUMBER DEFAULT NULL,
178     x_suburbs IN VARCHAR2 DEFAULT NULL,
179     x_closed_ind IN VARCHAR2 DEFAULT NULL,
180     x_creation_date IN DATE DEFAULT NULL,
181     x_created_by IN NUMBER DEFAULT NULL,
182     x_last_update_date IN DATE DEFAULT NULL,
183     x_last_updated_by IN NUMBER DEFAULT NULL,
184     x_last_update_login IN NUMBER DEFAULT NULL
185   ) AS
186   BEGIN
187 
188     Set_Column_Values (
189       p_action,
190       x_rowid,
191       x_postcode,
192       x_suburbs,
193       x_closed_ind,
194       x_creation_date,
195       x_created_by,
196       x_last_update_date,
197       x_last_updated_by,
198       x_last_update_login
199     );
200 
201      IF (p_action = 'INSERT') THEN
202        -- Call all the procedures related to Before Insert.
203      BeforeRowDelete1 ( p_inserting => TRUE );
204       IF  Get_PK_For_Validation (
205           new_references.postcode ) THEN
206          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
207          IGS_GE_MSG_STACK.ADD;
208           App_Exception.Raise_Exception;
209       END IF;
210 
211       Check_Constraints; -- if procedure present
212 
213  ELSIF (p_action = 'UPDATE') THEN
214        -- Call all the procedures related to Before Update.
215        BeforeRowDelete1( p_updating => TRUE );
216 
217        Check_Constraints; -- if procedure present
218 
219  ELSIF (p_action = 'DELETE') THEN
220        -- Call all the procedures related to Before Delete.
221       BeforeRowDelete1( p_deleting => TRUE );
222        Check_Child_Existance; -- if procedure present
223  ELSIF (p_action = 'VALIDATE_INSERT') THEN
224       IF  Get_PK_For_Validation (
225           new_references.postcode ) THEN
226          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
227          IGS_GE_MSG_STACK.ADD;
228           App_Exception.Raise_Exception;
229       END IF;
230 
231       Check_Constraints; -- if procedure present
232  ELSIF (p_action = 'VALIDATE_UPDATE') THEN
233 
234        Check_Constraints; -- if procedure present
235 
236 ELSIF (p_action = 'VALIDATE_DELETE') THEN
237       Check_Child_Existance; -- if procedure present
238  END IF;
239 
240   END Before_DML;
241 
242   PROCEDURE After_DML (
243     p_action IN VARCHAR2,
244     x_rowid IN VARCHAR2
245   ) AS
246   BEGIN
247 
248     l_rowid := x_rowid;
249 
250     IF (p_action = 'INSERT') THEN
251       -- Call all the procedures related to After Insert.
252       Null;
253     ELSIF (p_action = 'UPDATE') THEN
254       -- Call all the procedures related to After Update.
255       Null;
256     ELSIF (p_action = 'DELETE') THEN
257       -- Call all the procedures related to After Delete.
258       Null;
259     END IF;
260 
261   END After_DML;
262 
263 --
264  /* $HEADER$ */
265 procedure INSERT_ROW (
266   X_ROWID in out NOCOPY VARCHAR2,
267   X_POSTCODE in NUMBER,
268   X_SUBURBS in VARCHAR2,
269   X_CLOSED_IND in VARCHAR2,
270   X_MODE in VARCHAR2 default 'R'
271   ) AS
272     cursor C is select ROWID from IGS_PE_SUBURB_POSTCD
273       where POSTCODE = X_POSTCODE;
274     X_LAST_UPDATE_DATE DATE;
275     X_LAST_UPDATED_BY NUMBER;
276     X_LAST_UPDATE_LOGIN NUMBER;
277 begin
278   X_LAST_UPDATE_DATE := SYSDATE;
279   if(X_MODE = 'I') then
280     X_LAST_UPDATED_BY := 1;
281     X_LAST_UPDATE_LOGIN := 0;
282   elsif (X_MODE = 'R') then
283     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
284     if X_LAST_UPDATED_BY is NULL then
285       X_LAST_UPDATED_BY := -1;
286     end if;
287     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
288     if X_LAST_UPDATE_LOGIN is NULL then
289       X_LAST_UPDATE_LOGIN := -1;
290     end if;
291   else
292     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
293     IGS_GE_MSG_STACK.ADD;
294     app_exception.raise_exception;
295   end if;
296 --
297    Before_DML(
298     p_action=>'INSERT',
299     x_rowid=>X_ROWID,
300     x_closed_ind=> NVL(X_CLOSED_IND,'N'),
301     x_postcode=>X_POSTCODE,
302     x_suburbs=>X_SUBURBS,
303     x_creation_date=>X_LAST_UPDATE_DATE,
304     x_created_by=>X_LAST_UPDATED_BY,
305     x_last_update_date=>X_LAST_UPDATE_DATE,
306     x_last_updated_by=>X_LAST_UPDATED_BY,
307     x_last_update_login=>X_LAST_UPDATE_LOGIN
308     );
309 --
310   insert into IGS_PE_SUBURB_POSTCD (
311     POSTCODE,
312     SUBURBS,
313     CLOSED_IND,
314     CREATION_DATE,
315     CREATED_BY,
316     LAST_UPDATE_DATE,
317     LAST_UPDATED_BY,
318     LAST_UPDATE_LOGIN
319   ) values (
320     NEW_REFERENCES.POSTCODE,
321     NEW_REFERENCES.SUBURBS,
322     NEW_REFERENCES.CLOSED_IND,
323     X_LAST_UPDATE_DATE,
324     X_LAST_UPDATED_BY,
325     X_LAST_UPDATE_DATE,
326     X_LAST_UPDATED_BY,
327     X_LAST_UPDATE_LOGIN
328   );
329 
330   open c;
331   fetch c into X_ROWID;
332   if (c%notfound) then
333     close c;
334     raise no_data_found;
335   end if;
336   close c;
337 --
338  After_DML(
339   p_action => 'INSERT',
340   x_rowid => X_ROWID
341   );
342 --
343 end INSERT_ROW;
344 
345 procedure LOCK_ROW (
346   X_ROWID in VARCHAR2,
347   X_POSTCODE in NUMBER,
348   X_SUBURBS in VARCHAR2,
349   X_CLOSED_IND in VARCHAR2
350 ) AS
351   cursor c1 is select
352       SUBURBS,
353       CLOSED_IND
354     from IGS_PE_SUBURB_POSTCD
355     where ROWID = X_ROWID
356     for update nowait;
357   tlinfo c1%rowtype;
358 
359 begin
360   open c1;
361   fetch c1 into tlinfo;
362   if (c1%notfound) then
363     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
364 
365     close c1;
366     app_exception.raise_exception;
367     return;
368   end if;
369   close c1;
370 
371   if ( (tlinfo.SUBURBS = X_SUBURBS)
372       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
373   ) then
374     null;
375   else
376     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
377     app_exception.raise_exception;
378   end if;
379   return;
380 end LOCK_ROW;
381 
382 procedure UPDATE_ROW (
383   X_ROWID in VARCHAR2,
384   X_POSTCODE in NUMBER,
385   X_SUBURBS in VARCHAR2,
386   X_CLOSED_IND in VARCHAR2,
387   X_MODE in VARCHAR2 default 'R'
388   ) AS
389     X_LAST_UPDATE_DATE DATE;
390     X_LAST_UPDATED_BY NUMBER;
391     X_LAST_UPDATE_LOGIN NUMBER;
392 begin
393   X_LAST_UPDATE_DATE := SYSDATE;
394   if(X_MODE = 'I') then
395     X_LAST_UPDATED_BY := 1;
396     X_LAST_UPDATE_LOGIN := 0;
397   elsif (X_MODE = 'R') then
398     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
399     if X_LAST_UPDATED_BY is NULL then
400       X_LAST_UPDATED_BY := -1;
401     end if;
402     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
403     if X_LAST_UPDATE_LOGIN is NULL then
404       X_LAST_UPDATE_LOGIN := -1;
405     end if;
406   else
407     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
408     IGS_GE_MSG_STACK.ADD;
409     app_exception.raise_exception;
410   end if;
411 --
412    Before_DML(
413     p_action=>'UPDATE',
414     x_rowid=>X_ROWID,
415     x_closed_ind=>X_CLOSED_IND,
416     x_postcode=>X_POSTCODE,
417     x_suburbs=>X_SUBURBS,
418     x_creation_date=>X_LAST_UPDATE_DATE,
419     x_created_by=>X_LAST_UPDATED_BY,
420     x_last_update_date=>X_LAST_UPDATE_DATE,
421     x_last_updated_by=>X_LAST_UPDATED_BY,
422     x_last_update_login=>X_LAST_UPDATE_LOGIN
423     );
424 --
425   update IGS_PE_SUBURB_POSTCD set
426     SUBURBS = NEW_REFERENCES.SUBURBS,
427     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
428     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
429     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
430     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
431   where ROWID = X_ROWID
432   ;
433   if (sql%notfound) then
434     raise no_data_found;
435   end if;
436 --
437  After_DML(
438   p_action => 'UPDATE',
439   x_rowid => X_ROWID
440   );
441 --
442 end UPDATE_ROW;
443 
444 procedure ADD_ROW (
445   X_ROWID in out NOCOPY VARCHAR2,
446   X_POSTCODE in NUMBER,
447   X_SUBURBS in VARCHAR2,
448   X_CLOSED_IND in VARCHAR2,
449   X_MODE in VARCHAR2 default 'R'
450   ) AS
451   cursor c1 is select rowid from IGS_PE_SUBURB_POSTCD
452      where POSTCODE = X_POSTCODE
453   ;
454 
455 begin
456   open c1;
457   fetch c1 into X_ROWID;
458   if (c1%notfound) then
459     close c1;
460     INSERT_ROW (
461      X_ROWID,
462      X_POSTCODE,
463      X_SUBURBS,
464      X_CLOSED_IND,
465      X_MODE);
466     return;
467   end if;
468   close c1;
469   UPDATE_ROW (
470    X_ROWID,
471    X_POSTCODE,
472    X_SUBURBS,
473    X_CLOSED_IND,
474    X_MODE);
475 end ADD_ROW;
476 
477 procedure DELETE_ROW (
478   X_ROWID in VARCHAR2
479 ) AS
480 begin
481 --
482  Before_DML(
483   p_action => 'DELETE',
484   x_rowid => X_ROWID
485   );
486 --
487   delete from IGS_PE_SUBURB_POSTCD
488   where ROWID = X_ROWID;
489   if (sql%notfound) then
490     raise no_data_found;
491   end if;
492 --
493 After_DML(
494   p_action => 'DELETE',
495   x_rowid => X_ROWID
496   );
497 --
498 end DELETE_ROW;
499 
500 end IGS_PE_SUBURB_POSTCD_PKG;