1 PACKAGE BODY igr_i_e_orgunits_pkg AS
2 /* $Header: IGSRH10B.pls 120.0 2005/06/01 17:41:41 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igr_i_e_orgunits%ROWTYPE;
6 new_references igr_i_e_orgunits%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_ent_org_unit_id IN NUMBER DEFAULT NULL,
12 x_party_id IN NUMBER DEFAULT NULL,
13 x_closed_ind IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL,
19 x_inquiry_type_id IN NUMBER DEFAULT NULL
20 ) AS
21 /*
22 || Created By : jbegum
23 || Created On : 28-MAY-2001
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 IGR_I_E_ORGUNITS
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.ent_org_unit_id := x_ent_org_unit_id;
55 new_references.inquiry_type_id := x_inquiry_type_id;
56 new_references.party_id := x_party_id;
57 new_references.closed_ind := x_closed_ind;
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_uniqueness AS
75 /*
76 || Created By : jbegum
77 || Created On : 28-MAY-2001
78 || Purpose : Handles the Unique Constraint logic defined for the columns.
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 ( get_uk_for_validation (
87 new_references.party_id,
88 new_references.inquiry_type_id
89 )
90 ) THEN
91 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
92 igs_ge_msg_stack.add;
93 app_exception.raise_exception;
94 END IF;
95
96 END check_uniqueness;
97
98 FUNCTION Check_Party_Existence (
99 x_party_id IN NUMBER
100 ) RETURN BOOLEAN AS
101 CURSOR hz_pk IS
102 SELECT party_id
103 FROM HZ_PARTIES
104 WHERE party_id = x_party_id;
105 lv_hz_pk hz_pk%RowType;
106 BEGIN
107 Open hz_pk;
108 Fetch hz_pk INTO lv_hz_pk;
109 IF (hz_pk%FOUND) THEN
110 Close hz_pk;
111 Return (TRUE);
112 ELSE
113 Close hz_pk;
114 Return (FALSE);
115 END IF;
116 END Check_Party_Existence;
117
118
119 PROCEDURE check_parent_existance AS
120 /*
121 || Created By : jbegum
122 || Created On : 28-MAY-2001
123 || Purpose : Checks for the existance of Parent records.
124 || Known limitations, enhancements or remarks :
125 || Change History :
126 || Who When What
127 || (reverse chronological order - newest change first)
128 */
129 BEGIN
130
131 IF (((old_references.party_id = new_references.party_id)) OR
132 ((new_references.party_id IS NULL))) THEN
133 NULL;
134 ELSIF NOT Check_Party_Existence (
135 new_references.party_id
136 ) THEN
137 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
138 igs_ge_msg_stack.add;
139 app_exception.raise_exception;
140 END IF;
141
142 IF (((old_references.inquiry_type_id = new_references.inquiry_type_id)) OR
143 ((new_references.inquiry_type_id IS NULL))) THEN
144 NULL;
145 ELSIF NOT igr_i_inquiry_types_pkg.get_pk_for_validation (
146 new_references.inquiry_type_id
147 ) THEN
148 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
149 igs_ge_msg_stack.add;
150 app_exception.raise_exception;
151 END IF;
152
153 END check_parent_existance;
154
155
156 FUNCTION get_pk_for_validation (
157 x_ent_org_unit_id IN NUMBER ,
158 x_closed_ind IN VARCHAR2
159 ) RETURN BOOLEAN AS
160 /*
161 || Created By : jbegum
162 || Created On : 28-MAY-2001
163 || Purpose : Validates the Primary Key of the table.
164 || Known limitations, enhancements or remarks :
165 || Change History :
166 || Who When What
167 || (reverse chronological order - newest change first)
168 */
169 CURSOR cur_rowid IS
170 SELECT rowid
171 FROM igr_i_e_orgunits
172 WHERE ent_org_unit_id = x_ent_org_unit_id AND
173 closed_ind = NVL(x_closed_ind,closed_ind)
174 FOR UPDATE NOWAIT;
175
176 lv_rowid cur_rowid%RowType;
177
178 BEGIN
179
180 OPEN cur_rowid;
181 FETCH cur_rowid INTO lv_rowid;
182 IF (cur_rowid%FOUND) THEN
183 CLOSE cur_rowid;
184 RETURN(TRUE);
185 ELSE
186 CLOSE cur_rowid;
187 RETURN(FALSE);
188 END IF;
189
190 END get_pk_for_validation;
191
192
193 FUNCTION get_uk_for_validation (
194 x_party_id IN NUMBER,
195 x_closed_ind IN VARCHAR2,
196 x_inquiry_type_id IN NUMBER
197 ) RETURN BOOLEAN AS
198 /*
199 || Created By : jbegum
200 || Created On : 28-MAY-2001
201 || Purpose : Validates the Unique Keys of the table.
202 || Known limitations, enhancements or remarks :
203 || Change History :
204 || Who When What
205 || (reverse chronological order - newest change first)
206 */
207 CURSOR cur_rowid IS
208 SELECT rowid
209 FROM igr_i_e_orgunits
210 WHERE inquiry_type_id = x_inquiry_type_id
211 AND party_id = x_party_id
212 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
213 AND closed_ind = NVL(x_closed_ind,closed_ind);
214
215 lv_rowid cur_rowid%RowType;
216
217 BEGIN
218
219 OPEN cur_rowid;
220 FETCH cur_rowid INTO lv_rowid;
221 IF (cur_rowid%FOUND) THEN
222 CLOSE cur_rowid;
223 RETURN (true);
224 ELSE
225 CLOSE cur_rowid;
226 RETURN(FALSE);
227 END IF;
228
229 END get_uk_for_validation ;
230
231
232 PROCEDURE get_fk_igr_i_ent_stats (
233 x_inquiry_type_id IN NUMBER
234 ) AS
235 /*
236 || Created By : jbegum
237 || Created On : 28-MAY-2001
238 || Purpose : Validates the Foreign Keys for the table.
239 || Known limitations, enhancements or remarks :
240 || Change History :
241 || Who When What
242 || (reverse chronological order - newest change first)
243 */
244 CURSOR cur_rowid IS
245 SELECT rowid
246 FROM igr_i_e_orgunits
247 WHERE ((inquiry_type_id = x_inquiry_type_id));
248
249 lv_rowid cur_rowid%RowType;
250
251 BEGIN
252
253 OPEN cur_rowid;
254 FETCH cur_rowid INTO lv_rowid;
255 IF (cur_rowid%FOUND) THEN
256 CLOSE cur_rowid;
257 fnd_message.set_name ('IGS', 'IGS_AD_AIEOU_AIEST_FK');
258 igs_ge_msg_stack.add;
259 app_exception.raise_exception;
260 RETURN;
261 END IF;
262 CLOSE cur_rowid;
263
264 END get_fk_igr_i_ent_stats;
265
266
267 PROCEDURE before_dml (
268 p_action IN VARCHAR2,
269 x_rowid IN VARCHAR2 DEFAULT NULL,
273 x_creation_date IN DATE DEFAULT NULL,
270 x_ent_org_unit_id IN NUMBER DEFAULT NULL,
271 x_party_id IN NUMBER DEFAULT NULL,
272 x_closed_ind IN VARCHAR2 DEFAULT NULL,
274 x_created_by IN NUMBER DEFAULT NULL,
275 x_last_update_date IN DATE DEFAULT NULL,
276 x_last_updated_by IN NUMBER DEFAULT NULL,
277 x_last_update_login IN NUMBER DEFAULT NULL,
278 x_inquiry_type_id IN NUMBER DEFAULT NULL
279 ) AS
280 /*
281 || Created By : jbegum
282 || Created On : 28-MAY-2001
283 || Purpose : Initialises the columns, Checks Constraints, Calls the
284 || Trigger Handlers for the table, before any DML operation.
285 || Known limitations, enhancements or remarks :
286 || Change History :
287 || Who When What
288 || (reverse chronological order - newest change first)
289 */
290 BEGIN
291
292 set_column_values (
293 p_action,
294 x_rowid,
295 x_ent_org_unit_id,
296 x_party_id,
297 x_closed_ind,
298 x_creation_date,
299 x_created_by,
300 x_last_update_date,
301 x_last_updated_by,
302 x_last_update_login,
303 x_inquiry_type_id
304 );
305
306 IF (p_action = 'INSERT') THEN
307 -- Call all the procedures related to Before Insert.
308 IF ( get_pk_for_validation(
309 new_references.ent_org_unit_id
310 )
311 ) THEN
312 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
313 igs_ge_msg_stack.add;
314 app_exception.raise_exception;
315 END IF;
316 check_uniqueness;
317 check_parent_existance;
318 ELSIF (p_action = 'UPDATE') THEN
319 -- Call all the procedures related to Before Update.
320 check_uniqueness;
321 check_parent_existance;
322 ELSIF (p_action = 'VALIDATE_INSERT') THEN
323 -- Call all the procedures related to Before Insert.
324 IF ( get_pk_for_validation (
325 new_references.ent_org_unit_id
326 )
327 ) THEN
328 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
329 igs_ge_msg_stack.add;
330 app_exception.raise_exception;
331 END IF;
332 check_uniqueness;
333 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
334 check_uniqueness;
335 END IF;
336
337 END before_dml;
338
339
340 PROCEDURE insert_row (
341 x_rowid IN OUT NOCOPY VARCHAR2,
342 x_ent_org_unit_id IN OUT NOCOPY NUMBER,
343 x_party_id IN NUMBER,
344 x_closed_ind IN VARCHAR2,
345 x_mode IN VARCHAR2 DEFAULT 'R' ,
346 x_inquiry_type_id IN NUMBER DEFAULT NULL
347 ) AS
348 /*
349 || Created By : jbegum
350 || Created On : 28-MAY-2001
351 || Purpose : Handles the INSERT DML logic for the table.
352 || Known limitations, enhancements or remarks :
353 || Change History :
354 || Who When What
355 || (reverse chronological order - newest change first)
356 */
357 CURSOR c IS
358 SELECT rowid
359 FROM igr_i_e_orgunits
360 WHERE ent_org_unit_id = x_ent_org_unit_id;
361
362 x_last_update_date DATE;
363 x_last_updated_by NUMBER;
364 x_last_update_login NUMBER;
365
366 BEGIN
367
368 x_last_update_date := SYSDATE;
369 IF (x_mode = 'I') THEN
370 x_last_updated_by := 1;
371 x_last_update_login := 0;
372 ELSIF (x_mode = 'R') THEN
373 x_last_updated_by := fnd_global.user_id;
374 IF (x_last_updated_by IS NULL) THEN
375 x_last_updated_by := -1;
376 END IF;
377 x_last_update_login := fnd_global.login_id;
378 IF (x_last_update_login IS NULL) THEN
379 x_last_update_login := -1;
380 END IF;
381 ELSE
382 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
383 igs_ge_msg_stack.add;
384 app_exception.raise_exception;
385 END IF;
386
387 X_ENT_ORG_UNIT_ID := -1;
388 before_dml(
389 p_action => 'INSERT',
390 x_rowid => x_rowid,
391 x_ent_org_unit_id => x_ent_org_unit_id,
392 x_party_id => x_party_id,
393 x_closed_ind => x_closed_ind,
394 x_creation_date => x_last_update_date,
395 x_created_by => x_last_updated_by,
396 x_last_update_date => x_last_update_date,
397 x_last_updated_by => x_last_updated_by,
398 x_last_update_login => x_last_update_login,
399 x_inquiry_type_id => x_inquiry_type_id
400 );
401
402 INSERT INTO igr_i_e_orgunits (
403 ent_org_unit_id,
404 party_id,
405 closed_ind,
406 creation_date,
407 created_by,
408 last_update_date,
409 last_updated_by,
410 last_update_login,
411 inquiry_type_id
412 ) VALUES (
413 IGR_I_E_ORGUNITS_S.NEXTVAL,
414 new_references.party_id,
415 new_references.closed_ind,
416 x_last_update_date,
420 x_last_update_login,
417 x_last_updated_by,
418 x_last_update_date,
419 x_last_updated_by,
421 new_references.inquiry_type_id
422 )RETURNING ENT_ORG_UNIT_ID INTO X_ENT_ORG_UNIT_ID;
423
424 OPEN c;
425 FETCH c INTO x_rowid;
426 IF (c%NOTFOUND) THEN
427 CLOSE c;
428 RAISE NO_DATA_FOUND;
429 END IF;
430 CLOSE c;
431
432 END insert_row;
433
434
435 PROCEDURE lock_row (
436 x_rowid IN VARCHAR2,
437 x_ent_org_unit_id IN NUMBER,
438 x_party_id IN NUMBER,
439 x_closed_ind IN VARCHAR2,
440 x_inquiry_type_id IN NUMBER DEFAULT NULL
441 ) AS
442 /*
443 || Created By : jbegum
444 || Created On : 28-MAY-2001
445 || Purpose : Handles the LOCK mechanism for the table.
446 || Known limitations, enhancements or remarks :
447 || Change History :
448 || Who When What
449 || (reverse chronological order - newest change first)
450 */
451 CURSOR c1 IS
452 SELECT
453 inquiry_type_id,
454 party_id,
455 closed_ind
456 FROM igr_i_e_orgunits
457 WHERE rowid = x_rowid
458 FOR UPDATE NOWAIT;
459
460 tlinfo c1%ROWTYPE;
461
462 BEGIN
463
464 OPEN c1;
465 FETCH c1 INTO tlinfo;
466 IF (c1%notfound) THEN
467 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
468 igs_ge_msg_stack.add;
469 CLOSE c1;
470 app_exception.raise_exception;
471 RETURN;
472 END IF;
473 CLOSE c1;
474
475 IF (
476 (tlinfo.inquiry_type_id = x_inquiry_type_id)
477 AND (tlinfo.party_id = x_party_id)
478 AND (tlinfo.closed_ind = x_closed_ind)
479 ) THEN
480 NULL;
481 ELSE
482 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
483 igs_ge_msg_stack.add;
484 app_exception.raise_exception;
485 END IF;
486
487 RETURN;
488
489 END lock_row;
490
491
492 PROCEDURE update_row (
493 x_rowid IN VARCHAR2,
494 x_ent_org_unit_id IN NUMBER,
495 x_party_id IN NUMBER,
496 x_closed_ind IN VARCHAR2,
497 x_mode IN VARCHAR2 DEFAULT 'R',
498 x_inquiry_type_id IN NUMBER
499 ) AS
500 /*
501 || Created By : jbegum
502 || Created On : 28-MAY-2001
503 || Purpose : Handles the UPDATE DML logic for the table.
504 || Known limitations, enhancements or remarks :
505 || Change History :
506 || Who When What
507 || (reverse chronological order - newest change first)
508 */
509 x_last_update_date DATE ;
510 x_last_updated_by NUMBER;
511 x_last_update_login NUMBER;
512
513 BEGIN
514
515 x_last_update_date := SYSDATE;
516 IF (X_MODE = 'I') THEN
517 x_last_updated_by := 1;
518 x_last_update_login := 0;
519 ELSIF (x_mode = 'R') THEN
520 x_last_updated_by := fnd_global.user_id;
521 IF x_last_updated_by IS NULL THEN
522 x_last_updated_by := -1;
523 END IF;
524 x_last_update_login := fnd_global.login_id;
525 IF (x_last_update_login IS NULL) THEN
526 x_last_update_login := -1;
527 END IF;
528 ELSE
529 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
530 igs_ge_msg_stack.add;
531 app_exception.raise_exception;
532 END IF;
533
534 before_dml(
535 p_action => 'UPDATE',
536 x_rowid => x_rowid,
537 x_ent_org_unit_id => x_ent_org_unit_id,
538 x_party_id => x_party_id,
539 x_closed_ind => x_closed_ind,
540 x_creation_date => x_last_update_date,
541 x_created_by => x_last_updated_by,
542 x_last_update_date => x_last_update_date,
543 x_last_updated_by => x_last_updated_by,
544 x_last_update_login => x_last_update_login,
545 x_inquiry_type_id => x_inquiry_type_id
546 );
547
548 UPDATE igr_i_e_orgunits
549 SET
550 inquiry_type_id = new_references.inquiry_type_id,
551 party_id = new_references.party_id,
552 closed_ind = new_references.closed_ind,
553 last_update_date = x_last_update_date,
554 last_updated_by = x_last_updated_by,
555 last_update_login = x_last_update_login
556 WHERE rowid = x_rowid;
557
558 IF (SQL%NOTFOUND) THEN
559 RAISE NO_DATA_FOUND;
560 END IF;
561
562 END update_row;
563
564
565 PROCEDURE add_row (
566 x_rowid IN OUT NOCOPY VARCHAR2,
567 x_ent_org_unit_id IN OUT NOCOPY NUMBER,
568 x_party_id IN NUMBER,
569 x_closed_ind IN VARCHAR2,
570 x_mode IN VARCHAR2 DEFAULT 'R' ,
571 x_inquiry_type_id IN NUMBER DEFAULT NULL
572 ) AS
573 /*
574 || Created By : jbegum
575 || Created On : 28-MAY-2001
576 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
577 || Known limitations, enhancements or remarks :
578 || Change History :
579 || Who When What
580 || (reverse chronological order - newest change first)
581 */
582 CURSOR c1 IS
583 SELECT rowid
584 FROM igr_i_e_orgunits
585 WHERE ent_org_unit_id = x_ent_org_unit_id;
586
587 BEGIN
588
589 OPEN c1;
590 FETCH c1 INTO x_rowid;
591 IF (c1%NOTFOUND) THEN
592 CLOSE c1;
593
594 insert_row (
595 x_rowid,
596 x_ent_org_unit_id,
597 x_party_id,
598 x_closed_ind,
599 x_mode,
600 x_inquiry_type_id
601 );
602 RETURN;
603 END IF;
604 CLOSE c1;
605
606 update_row (
607 x_rowid,
608 x_ent_org_unit_id,
609 x_party_id,
610 x_closed_ind,
611 x_mode,
612 x_inquiry_type_id
613 );
614
615 END add_row;
616
617
618 PROCEDURE delete_row (
619 x_rowid IN VARCHAR2
620 ) AS
621 /*
622 || Created By : jbegum
623 || Created On : 28-MAY-2001
624 || Purpose : Handles the DELETE DML logic for the table.
625 || Known limitations, enhancements or remarks :
626 || Change History :
627 || Who When What
628 || (reverse chronological order - newest change first)
629 */
630 BEGIN
631
632 before_dml (
633 p_action => 'DELETE',
634 x_rowid => x_rowid
635 );
636
637 DELETE FROM igr_i_e_orgunits
638 WHERE rowid = x_rowid;
639
640 IF (SQL%NOTFOUND) THEN
641 RAISE NO_DATA_FOUND;
642 END IF;
643
644 END delete_row;
645
646
647 END igr_i_e_orgunits_pkg;
[Home] [Help]