[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_COM_INST_PKG
Source
1 PACKAGE BODY igs_uc_com_inst_pkg AS
2 /* $Header: IGSXI09B.pls 115.7 2003/06/11 10:31:05 smaddali noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_com_inst%ROWTYPE;
6 new_references igs_uc_com_inst%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_inst IN VARCHAR2,
12 x_inst_code IN VARCHAR2,
13 x_inst_name IN VARCHAR2,
14 x_ucas IN VARCHAR2,
15 x_gttr IN VARCHAR2,
16 x_swas IN VARCHAR2,
17 x_nmas IN VARCHAR2,
18 x_imported IN VARCHAR2,
19 x_creation_date IN DATE ,
20 x_created_by IN NUMBER ,
21 x_last_update_date IN DATE ,
22 x_last_updated_by IN NUMBER ,
23 x_last_update_login IN NUMBER
24 ) AS
25 /*
26 || Created By : rgopalan
27 || Created On : 01-OCT-2001
28 || Purpose : Initialises the Old and New references for the columns of the table.
29 || Known limitations, enhancements or remarks :
30 || Change History :
31 || Who When What
32 || smaddali 10-jun-03 obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208
33 || (reverse chronological order - newest change first)
34 */
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM IGS_UC_COM_INST
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 OPEN cur_old_ref_values;
48 FETCH cur_old_ref_values INTO old_references;
49 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
50 CLOSE cur_old_ref_values;
51 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
52 igs_ge_msg_stack.add;
53 app_exception.raise_exception;
54 RETURN;
55 END IF;
56 CLOSE cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.inst := x_inst;
60 new_references.inst_code := x_inst_code;
61 new_references.inst_name := x_inst_name;
62 new_references.ucas := x_ucas;
63 new_references.gttr := x_gttr;
64 new_references.swas := x_swas;
65 new_references.nmas := x_nmas;
66 new_references.imported := x_imported;
67
68 IF (p_action = 'UPDATE') THEN
69 new_references.creation_date := old_references.creation_date;
70 new_references.created_by := old_references.created_by;
71 ELSE
72 new_references.creation_date := x_creation_date;
73 new_references.created_by := x_created_by;
74 END IF;
75
76 new_references.last_update_date := x_last_update_date;
77 new_references.last_updated_by := x_last_updated_by;
78 new_references.last_update_login := x_last_update_login;
79
80 END set_column_values;
81
82
83 FUNCTION get_pk_for_validation (
84 x_inst IN VARCHAR2
85 ) RETURN BOOLEAN AS
86 /*
87 || Created By : rgopalan
88 || Created On : 01-OCT-2001
89 || Purpose : Validates the Primary Key of the table.
90 || Known limitations, enhancements or remarks :
91 || Change History :
92 || Who When What
93 || (reverse chronological order - newest change first)
94 */
95 CURSOR cur_rowid IS
96 SELECT rowid
97 FROM igs_uc_com_inst
98 WHERE inst = x_inst ;
99
100 lv_rowid cur_rowid%RowType;
101
102 BEGIN
103
104 OPEN cur_rowid;
105 FETCH cur_rowid INTO lv_rowid;
106 IF (cur_rowid%FOUND) THEN
107 CLOSE cur_rowid;
108 RETURN(TRUE);
109 ELSE
110 CLOSE cur_rowid;
111 RETURN(FALSE);
112 END IF;
113
114 END get_pk_for_validation;
115
116
117 PROCEDURE before_dml (
118 p_action IN VARCHAR2,
119 x_rowid IN VARCHAR2,
120 x_inst IN VARCHAR2,
121 x_inst_code IN VARCHAR2,
122 x_inst_name IN VARCHAR2,
123 x_ucas IN VARCHAR2,
124 x_gttr IN VARCHAR2,
125 x_swas IN VARCHAR2,
126 x_nmas IN VARCHAR2,
127 x_imported IN VARCHAR2,
128 x_creation_date IN DATE ,
129 x_created_by IN NUMBER ,
130 x_last_update_date IN DATE ,
131 x_last_updated_by IN NUMBER ,
132 x_last_update_login IN NUMBER
133 ) AS
134 /*
135 || Created By : rgopalan
136 || Created On : 01-OCT-2001
137 || Purpose : Initialises the columns, Checks Constraints, Calls the
138 || Trigger Handlers for the table, before any DML operation.
139 || Known limitations, enhancements or remarks :
140 || Change History :
141 || Who When What
142 || smaddali 10-jun-03 obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208
143 || (reverse chronological order - newest change first)
144 */
145 BEGIN
146
147 set_column_values (
148 p_action,
149 x_rowid,
150 x_inst,
151 x_inst_code,
152 x_inst_name,
153 x_ucas,
154 x_gttr,
155 x_swas,
156 x_nmas,
157 x_imported,
158 x_creation_date,
159 x_created_by,
160 x_last_update_date,
161 x_last_updated_by,
162 x_last_update_login
163 );
164
165 IF (p_action = 'INSERT') THEN
166 -- Call all the procedures related to Before Insert.
167 IF ( get_pk_for_validation(
168 new_references.inst
169 )
170 ) THEN
171 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
172 igs_ge_msg_stack.add;
173 app_exception.raise_exception;
174 END IF;
175 ELSIF (p_action = 'VALIDATE_INSERT') THEN
176 -- Call all the procedures related to Before Insert.
177 IF ( get_pk_for_validation (
178 new_references.inst
179 )
180 ) THEN
181 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
182 igs_ge_msg_stack.add;
183 app_exception.raise_exception;
184 END IF;
185 END IF;
186
187 END before_dml;
188
189
190 PROCEDURE insert_row (
191 x_rowid IN OUT NOCOPY VARCHAR2,
192 x_inst IN OUT NOCOPY VARCHAR2,
193 x_inst_code IN VARCHAR2,
194 x_inst_name IN VARCHAR2,
195 x_ucas IN VARCHAR2,
196 x_gttr IN VARCHAR2,
197 x_swas IN VARCHAR2,
198 x_nmas IN VARCHAR2,
199 x_imported IN VARCHAR2,
200 x_mode IN VARCHAR2
201 ) AS
202 /*
203 || Created By : rgopalan
204 || Created On : 01-OCT-2001
205 || Purpose : Handles the INSERT DML logic for the table.
206 || Known limitations, enhancements or remarks :
207 || Change History :
208 || Who When What
209 || smaddali 10-jun-03 obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208
210 || (reverse chronological order - newest change first)
211 */
212 CURSOR c IS
213 SELECT rowid
214 FROM igs_uc_com_inst
215 WHERE inst = x_inst;
216
217 x_last_update_date DATE;
218 x_last_updated_by NUMBER;
219 x_last_update_login NUMBER;
220
221 BEGIN
222
223 x_last_update_date := SYSDATE;
224 IF (x_mode = 'I') THEN
225 x_last_updated_by := 1;
226 x_last_update_login := 0;
227 ELSIF (x_mode = 'R') THEN
228 x_last_updated_by := fnd_global.user_id;
229 IF (x_last_updated_by IS NULL) THEN
230 x_last_updated_by := -1;
231 END IF;
232 x_last_update_login := fnd_global.login_id;
233 IF (x_last_update_login IS NULL) THEN
234 x_last_update_login := -1;
235 END IF;
236 ELSE
237 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
238 igs_ge_msg_stack.add;
239 app_exception.raise_exception;
240 END IF;
241
242 before_dml(
243 p_action => 'INSERT',
244 x_rowid => x_rowid,
245 x_inst => x_inst,
246 x_inst_code => x_inst_code,
247 x_inst_name => x_inst_name,
248 x_ucas => x_ucas,
249 x_gttr => x_gttr,
250 x_swas => x_swas,
251 x_nmas => x_nmas,
252 x_imported => x_imported,
253 x_creation_date => x_last_update_date,
254 x_created_by => x_last_updated_by,
255 x_last_update_date => x_last_update_date,
256 x_last_updated_by => x_last_updated_by,
257 x_last_update_login => x_last_update_login
258 );
259
260 INSERT INTO igs_uc_com_inst (
261 inst,
262 inst_code,
263 inst_name,
264 ucas,
265 gttr,
266 swas,
267 nmas,
268 imported,
269 creation_date,
270 created_by,
271 last_update_date,
272 last_updated_by,
273 last_update_login
274 ) VALUES (
275 new_references.inst,
276 new_references.inst_code,
277 new_references.inst_name,
278 new_references.ucas,
279 new_references.gttr,
280 new_references.swas,
281 new_references.nmas,
282 new_references.imported,
283 x_last_update_date,
284 x_last_updated_by,
285 x_last_update_date,
286 x_last_updated_by,
287 x_last_update_login
288 );
289
290 OPEN c;
291 FETCH c INTO x_rowid;
292 IF (c%NOTFOUND) THEN
293 CLOSE c;
294 RAISE NO_DATA_FOUND;
295 END IF;
296 CLOSE c;
297
298 END insert_row;
299
300
301 PROCEDURE lock_row (
302 x_rowid IN VARCHAR2,
303 x_inst IN VARCHAR2,
304 x_inst_code IN VARCHAR2,
305 x_inst_name IN VARCHAR2,
306 x_ucas IN VARCHAR2,
307 x_gttr IN VARCHAR2,
308 x_swas IN VARCHAR2,
309 x_nmas IN VARCHAR2,
310 x_imported IN VARCHAR2
311 ) AS
312 /*
313 || Created By : rgopalan
314 || Created On : 01-OCT-2001
315 || Purpose : Handles the LOCK mechanism for the table.
316 || Known limitations, enhancements or remarks :
317 || Change History :
318 || Who When What
319 || smaddali 10-jun-03 obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208
320 || (reverse chronological order - newest change first)
321 */
322 CURSOR c1 IS
323 SELECT
324 inst_code,
325 inst_name,
326 ucas,
327 gttr,
328 swas,
329 nmas,
330 imported
331 FROM igs_uc_com_inst
332 WHERE rowid = x_rowid
333 FOR UPDATE NOWAIT;
334
335 tlinfo c1%ROWTYPE;
336
337 BEGIN
338
339 OPEN c1;
340 FETCH c1 INTO tlinfo;
341 IF (c1%notfound) THEN
342 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
343 igs_ge_msg_stack.add;
344 CLOSE c1;
345 app_exception.raise_exception;
346 RETURN;
347 END IF;
348 CLOSE c1;
349
350 IF (
351 ((tlinfo.inst_code = x_inst_code) OR ((tlinfo.inst_code IS NULL) AND (X_inst_code IS NULL)))
352 AND ((tlinfo.inst_name = x_inst_name) OR ((tlinfo.inst_name IS NULL) AND (X_inst_name IS NULL)))
353 AND (tlinfo.ucas = x_ucas)
354 AND (tlinfo.gttr = x_gttr)
355 AND (tlinfo.swas = x_swas)
356 AND (tlinfo.nmas = x_nmas)
357 AND (tlinfo.imported = x_imported)
358 ) THEN
359 NULL;
360 ELSE
361 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
362 igs_ge_msg_stack.add;
363 app_exception.raise_exception;
364 END IF;
365
366 RETURN;
367
368 END lock_row;
369
370
371 PROCEDURE update_row (
372 x_rowid IN VARCHAR2,
373 x_inst IN VARCHAR2,
374 x_inst_code IN VARCHAR2,
375 x_inst_name IN VARCHAR2,
376 x_ucas IN VARCHAR2,
377 x_gttr IN VARCHAR2,
378 x_swas IN VARCHAR2,
379 x_nmas IN VARCHAR2,
380 x_imported IN VARCHAR2,
381 x_mode IN VARCHAR2
382 ) AS
383 /*
384 || Created By : rgopalan
385 || Created On : 01-OCT-2001
386 || Purpose : Handles the UPDATE DML logic for the table.
387 || Known limitations, enhancements or remarks :
388 || Change History :
389 || Who When What
390 || smaddali 10-jun-03 obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208
391 || (reverse chronological order - newest change first)
392 */
393 x_last_update_date DATE ;
394 x_last_updated_by NUMBER;
395 x_last_update_login NUMBER;
396
397 BEGIN
398
399 x_last_update_date := SYSDATE;
400 IF (X_MODE = 'I') THEN
401 x_last_updated_by := 1;
402 x_last_update_login := 0;
403 ELSIF (x_mode = 'R') THEN
404 x_last_updated_by := fnd_global.user_id;
405 IF x_last_updated_by IS NULL THEN
406 x_last_updated_by := -1;
407 END IF;
408 x_last_update_login := fnd_global.login_id;
409 IF (x_last_update_login IS NULL) THEN
410 x_last_update_login := -1;
411 END IF;
412 ELSE
413 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
414 igs_ge_msg_stack.add;
415 app_exception.raise_exception;
416 END IF;
417
418 before_dml(
419 p_action => 'UPDATE',
420 x_rowid => x_rowid,
421 x_inst => x_inst,
422 x_inst_code => x_inst_code,
423 x_inst_name => x_inst_name,
424 x_ucas => x_ucas,
425 x_gttr => x_gttr,
426 x_swas => x_swas,
427 x_nmas => x_nmas,
428 x_imported => x_imported,
429 x_creation_date => x_last_update_date,
430 x_created_by => x_last_updated_by,
431 x_last_update_date => x_last_update_date,
432 x_last_updated_by => x_last_updated_by,
433 x_last_update_login => x_last_update_login
434 );
435
436 UPDATE igs_uc_com_inst
437 SET
438 inst_code = new_references.inst_code,
439 inst_name = new_references.inst_name,
440 ucas = new_references.ucas,
441 gttr = new_references.gttr,
442 swas = new_references.swas,
443 nmas = new_references.nmas,
444 imported = new_references.imported,
445 last_update_date = x_last_update_date,
446 last_updated_by = x_last_updated_by,
447 last_update_login = x_last_update_login
448 WHERE rowid = x_rowid;
449
450 IF (SQL%NOTFOUND) THEN
451 RAISE NO_DATA_FOUND;
452 END IF;
453
454 END update_row;
455
456
457 PROCEDURE add_row (
458 x_rowid IN OUT NOCOPY VARCHAR2,
459 x_inst IN OUT NOCOPY VARCHAR2,
460 x_inst_code IN VARCHAR2,
461 x_inst_name IN VARCHAR2,
462 x_ucas IN VARCHAR2,
463 x_gttr IN VARCHAR2,
464 x_swas IN VARCHAR2,
465 x_nmas IN VARCHAR2,
466 x_imported IN VARCHAR2,
467 x_mode IN VARCHAR2
468 ) AS
469 /*
470 || Created By : rgopalan
471 || Created On : 01-OCT-2001
472 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
473 || Known limitations, enhancements or remarks :
474 || Change History :
475 || Who When What
476 || smaddali 10-jun-03 obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208
477 || (reverse chronological order - newest change first)
478 */
479 CURSOR c1 IS
480 SELECT rowid
481 FROM igs_uc_com_inst
482 WHERE inst = x_inst;
483
484 BEGIN
485
486 OPEN c1;
487 FETCH c1 INTO x_rowid;
488 IF (c1%NOTFOUND) THEN
489 CLOSE c1;
490
491 insert_row (
492 x_rowid,
493 x_inst,
494 x_inst_code,
495 x_inst_name,
496 x_ucas,
497 x_gttr,
498 x_swas,
499 x_nmas,
500 x_imported,
501 x_mode
502 );
503 RETURN;
504 END IF;
505 CLOSE c1;
506
507 update_row (
508 x_rowid,
509 x_inst,
510 x_inst_code,
511 x_inst_name,
512 x_ucas,
513 x_gttr,
514 x_swas,
515 x_nmas,
516 x_imported,
517 x_mode
518 );
519
520 END add_row;
521
522
523 PROCEDURE delete_row (
524 x_rowid IN VARCHAR2
525 ) AS
526 /*
527 || Created By : rgopalan
528 || Created On : 01-OCT-2001
529 || Purpose : Handles the DELETE DML logic for the table.
530 || Known limitations, enhancements or remarks :
531 || Change History :
532 || Who When What
533 || (reverse chronological order - newest change first)
534 */
535 BEGIN
536
537 before_dml (
538 p_action => 'DELETE',
539 x_rowid => x_rowid
540 );
541
542 DELETE FROM igs_uc_com_inst
543 WHERE rowid = x_rowid;
544
545 IF (SQL%NOTFOUND) THEN
546 RAISE NO_DATA_FOUND;
547 END IF;
548
549 END delete_row;
550
551
552 END igs_uc_com_inst_pkg;