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