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