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