1 PACKAGE BODY igs_or_loc_region_pkg AS
2 /* $Header: IGSOI34B.pls 115.1 2003/10/30 13:30:15 rghosh noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_or_loc_region%ROWTYPE;
6 new_references igs_or_loc_region%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_location_cd IN VARCHAR2,
12 x_region_cd IN VARCHAR2,
13 x_creation_date IN DATE,
14 x_created_by IN NUMBER,
15 x_last_update_date IN DATE,
16 x_last_updated_by IN NUMBER,
17 x_last_update_login IN NUMBER
18 ) AS
19 /*
20 || Created By : [email protected]
21 || Created On : 18-APR-2003
22 || Purpose : Initialises the Old and New references for the columns of the table.
23 || Known limitations, enhancements or remarks :
24 || Change History :
25 || Who When What
26 || (reverse chronological order - newest change first)
27 */
28
29 CURSOR cur_old_ref_values IS
30 SELECT *
31 FROM igs_or_loc_region
32 WHERE rowid = x_rowid;
33
34 BEGIN
35
36 l_rowid := x_rowid;
37
38 -- Code for setting the Old and New Reference Values.
39 -- Populate Old Values.
40 OPEN cur_old_ref_values;
41 FETCH cur_old_ref_values INTO old_references;
42 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
43 CLOSE cur_old_ref_values;
44 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
45 igs_ge_msg_stack.add;
46 app_exception.raise_exception;
47 RETURN;
48 END IF;
49 CLOSE cur_old_ref_values;
50
51 -- Populate New Values.
52 new_references.location_cd := x_location_cd;
53 new_references.region_cd := x_region_cd;
54
55 IF (p_action = 'UPDATE') THEN
56 new_references.creation_date := old_references.creation_date;
57 new_references.created_by := old_references.created_by;
58 ELSE
59 new_references.creation_date := x_creation_date;
60 new_references.created_by := x_created_by;
61 END IF;
62
63 new_references.last_update_date := x_last_update_date;
64 new_references.last_updated_by := x_last_updated_by;
65 new_references.last_update_login := x_last_update_login;
66
67 END set_column_values;
68
69
70 PROCEDURE check_parent_existance AS
71 /*
72 || Created By : [email protected]
73 || Created On : 18-APR-2003
74 || Purpose : Checks for the existance of Parent records.
75 || Known limitations, enhancements or remarks :
76 || Change History :
77 || Who When What
78 || (reverse chronological order - newest change first)
79 */
80 BEGIN
81
82 IF (((old_references.location_cd = new_references.location_cd)) OR
83 ((new_references.location_cd IS NULL))) THEN
84 NULL;
85 ELSIF NOT igs_ad_location_pkg.get_pk_for_validation (
86 new_references.location_cd ,
87 'N'
88 ) THEN
89 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
90 igs_ge_msg_stack.add;
91 app_exception.raise_exception;
92 END IF;
93
94 END check_parent_existance;
95
96 FUNCTION get_rg_for_validation (
97 x_location_cd IN VARCHAR2,
98 x_region_cd IN VARCHAR2
99 ) RETURN BOOLEAN AS
100 /*
101 || Created By : [email protected]
102 || Created On : 18-APR-2003
103 || Purpose : Validates the region_code references .
104 || Known limitations, enhancements or remarks :
105 || Change History :
106 || Who When What
107 || (reverse chronological order - newest change first)
108 */
109 CURSOR cur_prcd IS
110 SELECT 1
111 FROM igs_ps_unit_ofr_opt_all uooa,
112 igs_ps_usec_occurs_all uoa
113 WHERE uoa.preferred_region_code = x_region_cd
114 AND uoa.uoo_id = uooa.uoo_id
115 AND uooa.location_cd = x_location_cd;
116
117
118 lv_prcd NUMBER;
119
120 BEGIN
121
122 OPEN cur_prcd;
123 FETCH cur_prcd INTO lv_prcd;
124 IF (cur_prcd%FOUND) THEN
125 CLOSE cur_prcd;
126 RETURN(TRUE);
127 ELSE
128 CLOSE cur_prcd;
129 RETURN(FALSE);
130 END IF;
131
132 END get_rg_for_validation;
133
134 FUNCTION get_pk_for_validation (
135 x_location_cd IN VARCHAR2,
136 x_region_cd IN VARCHAR2
137 ) RETURN BOOLEAN AS
138 /*
139 || Created By : [email protected]
140 || Created On : 18-APR-2003
141 || Purpose : Validates the Primary Key of the table.
142 || Known limitations, enhancements or remarks :
143 || Change History :
144 || Who When What
145 || (reverse chronological order - newest change first)
146 */
147 CURSOR cur_rowid IS
148 SELECT rowid
149 FROM igs_or_loc_region
150 WHERE location_cd = x_location_cd
151 AND region_cd = x_region_cd
152 FOR UPDATE NOWAIT;
153
154 lv_rowid cur_rowid%RowType;
155
156 BEGIN
157
158 OPEN cur_rowid;
159 FETCH cur_rowid INTO lv_rowid;
160 IF (cur_rowid%FOUND) THEN
161 CLOSE cur_rowid;
162 RETURN(TRUE);
163 ELSE
164 CLOSE cur_rowid;
165 RETURN(FALSE);
166 END IF;
167
168 END get_pk_for_validation;
169
170
171 PROCEDURE get_fk_igs_ad_location (
172 x_location_cd IN VARCHAR2
173 ) AS
174 /*
175 || Created By : [email protected]
176 || Created On : 18-APR-2003
177 || Purpose : Validates the Foreign Keys for the table.
178 || Known limitations, enhancements or remarks :
179 || Change History :
180 || Who When What
181 || (reverse chronological order - newest change first)
182 */
183 CURSOR cur_rowid IS
184 SELECT rowid
185 FROM igs_or_loc_region
186 WHERE ((location_cd = x_location_cd));
187
188 lv_rowid cur_rowid%RowType;
189
190 BEGIN
191
192 OPEN cur_rowid;
193 FETCH cur_rowid INTO lv_rowid;
194 IF (cur_rowid%FOUND) THEN
195 CLOSE cur_rowid;
196 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
197 igs_ge_msg_stack.add;
198 app_exception.raise_exception;
199 RETURN;
200 END IF;
201 CLOSE cur_rowid;
202
203 END get_fk_igs_ad_location;
204
205
206 PROCEDURE before_dml (
207 p_action IN VARCHAR2,
208 x_rowid IN VARCHAR2,
209 x_location_cd IN VARCHAR2,
210 x_region_cd IN VARCHAR2,
211 x_creation_date IN DATE,
212 x_created_by IN NUMBER,
213 x_last_update_date IN DATE,
214 x_last_updated_by IN NUMBER,
215 x_last_update_login IN NUMBER
216 ) AS
217 /*
218 || Created By : [email protected]
219 || Created On : 18-APR-2003
220 || Purpose : Initialises the columns, Checks Constraints, Calls the
221 || Trigger Handlers for the table, before any DML operation.
222 || Known limitations, enhancements or remarks :
223 || Change History :
224 || Who When What
225 || (reverse chronological order - newest change first)
226 */
227 BEGIN
228
229 set_column_values (
230 p_action,
231 x_rowid,
232 x_location_cd,
233 x_region_cd,
234 x_creation_date,
235 x_created_by,
236 x_last_update_date,
237 x_last_updated_by,
238 x_last_update_login
239 );
240 IF (p_action = 'INSERT') THEN
241 -- Call all the procedures related to Before Insert.
242 IF ( get_pk_for_validation(
243 new_references.location_cd,
244 new_references.region_cd
245 )
246 ) THEN
247 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
248 igs_ge_msg_stack.add;
249 app_exception.raise_exception;
250 END IF;
251 check_parent_existance;
252 ELSIF (p_action = 'UPDATE') THEN
253 -- Call all the procedures related to Before Update.
254 check_parent_existance;
255 ELSIF (p_action = 'VALIDATE_DELETE') THEN
256 IF ( get_rg_for_validation(
257 old_references.location_cd,
258 old_references.region_cd
259 )) THEN
260 fnd_message.set_name('IGS','IGS_OR_PREF_REGION_EXISTS');
261 igs_ge_msg_stack.add;
262 app_exception.raise_exception;
263 END IF;
264 ELSIF (p_action = 'VALIDATE_INSERT') THEN
265 -- Call all the procedures related to Before Insert.
266 IF ( get_pk_for_validation (
267 new_references.location_cd,
268 new_references.region_cd
269 )
270 ) THEN
271 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
272 igs_ge_msg_stack.add;
273 app_exception.raise_exception;
274 END IF;
275 END IF;
276
277 END before_dml;
278
279
280 PROCEDURE insert_row (
281 x_rowid IN OUT NOCOPY VARCHAR2,
282 x_location_cd IN VARCHAR2,
283 x_region_cd IN VARCHAR2,
284 x_mode IN VARCHAR2
285 ) AS
286 /*
287 || Created By : [email protected]
288 || Created On : 18-APR-2003
289 || Purpose : Handles the INSERT DML logic for the table.
290 || Known limitations, enhancements or remarks :
291 || Change History :
292 || Who When What
293 || (reverse chronological order - newest change first)
294 */
295
296 x_last_update_date DATE;
297 x_last_updated_by NUMBER;
298 x_last_update_login NUMBER;
299
300 BEGIN
301
302 x_last_update_date := SYSDATE;
303 IF (x_mode = 'I') THEN
304 x_last_updated_by := 1;
305 x_last_update_login := 0;
306 ELSIF (x_mode = 'R') THEN
307 x_last_updated_by := fnd_global.user_id;
308 IF (x_last_updated_by IS NULL) THEN
309 x_last_updated_by := -1;
310 END IF;
311 x_last_update_login := fnd_global.login_id;
312 IF (x_last_update_login IS NULL) THEN
313 x_last_update_login := -1;
314 END IF;
315 ELSE
316 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
317 fnd_message.set_token ('ROUTINE', 'IGS_OR_LOC_REGION_PKG.INSERT_ROW');
318 igs_ge_msg_stack.add;
319 app_exception.raise_exception;
320 END IF;
321
322 before_dml(
323 p_action => 'INSERT',
324 x_rowid => x_rowid,
325 x_location_cd => x_location_cd,
326 x_region_cd => x_region_cd,
327 x_creation_date => x_last_update_date,
328 x_created_by => x_last_updated_by,
329 x_last_update_date => x_last_update_date,
330 x_last_updated_by => x_last_updated_by,
331 x_last_update_login => x_last_update_login
332 );
333
334 INSERT INTO igs_or_loc_region (
335 location_cd,
336 region_cd,
337 creation_date,
338 created_by,
339 last_update_date,
340 last_updated_by,
341 last_update_login
342 ) VALUES (
343 new_references.location_cd,
344 new_references.region_cd,
345 x_last_update_date,
346 x_last_updated_by,
347 x_last_update_date,
348 x_last_updated_by,
349 x_last_update_login
350 ) RETURNING ROWID INTO x_rowid;
351
352 END insert_row;
353
354
355 PROCEDURE lock_row (
356 x_rowid IN VARCHAR2,
357 x_location_cd IN VARCHAR2,
358 x_region_cd IN VARCHAR2
359 ) AS
360 /*
361 || Created By : [email protected]
362 || Created On : 18-APR-2003
363 || Purpose : Handles the LOCK mechanism for the table.
364 || Known limitations, enhancements or remarks :
365 || Change History :
366 || Who When What
367 || (reverse chronological order - newest change first)
368 */
369 CURSOR c1 IS
370 SELECT
371 rowid
372 FROM igs_or_loc_region
373 WHERE rowid = x_rowid
374 FOR UPDATE NOWAIT;
375
376 tlinfo c1%ROWTYPE;
377
378 BEGIN
379
380 OPEN c1;
381 FETCH c1 INTO tlinfo;
382 IF (c1%notfound) THEN
383 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
384 igs_ge_msg_stack.add;
385 CLOSE c1;
386 app_exception.raise_exception;
387 RETURN;
388 END IF;
389 CLOSE c1;
390
391
392 RETURN;
393
394 END lock_row;
395
396
397 PROCEDURE delete_row (
398 x_rowid IN VARCHAR2
399 ) AS
400 /*
401 || Created By : [email protected]
402 || Created On : 18-APR-2003
403 || Purpose : Handles the DELETE DML logic for the table.
404 || Known limitations, enhancements or remarks :
405 || Change History :
406 || Who When What
407 || (reverse chronological order - newest change first)
408 */
409 BEGIN
410
411 before_dml (
412 p_action => 'DELETE',
413 x_rowid => x_rowid
414 );
415
416 DELETE FROM igs_or_loc_region
417 WHERE rowid = x_rowid;
418
419 IF (SQL%NOTFOUND) THEN
420 RAISE NO_DATA_FOUND;
421 END IF;
422
423 END delete_row;
424
425
426 END igs_or_loc_region_pkg;
[Home] [Help]