[Home] [Help]
PACKAGE BODY: APPS.IGS_LOOKUPS_VAL_PKG
Source
1 PACKAGE BODY igs_lookups_val_pkg AS
2 /* $Header: IGSMI17B.pls 120.0 2005/06/01 20:41:23 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_lookups_val%ROWTYPE;
6 new_references igs_lookups_val%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_lookup_type IN VARCHAR2 DEFAULT NULL,
12 x_lookup_code IN VARCHAR2 DEFAULT NULL,
13 x_closed_ind IN VARCHAR2 DEFAULT NULL,
14 x_security_allowed_ind IN VARCHAR2 DEFAULT NULL,
15 x_step_type_restriction_num_in IN VARCHAR2 DEFAULT NULL,
16 x_unit_outcome_ind IN VARCHAR2 DEFAULT NULL,
17 x_display_name IN VARCHAR2 DEFAULT NULL,
18 x_display_order IN NUMBER DEFAULT NULL,
19 x_step_order_applicable_ind IN VARCHAR2 DEFAULT NULL,
20 x_academic_transcript_ind IN VARCHAR2 DEFAULT NULL,
21 x_cmpltn_requirements_ind IN VARCHAR2 DEFAULT NULL,
22 x_fee_ass_ind IN VARCHAR2 DEFAULT NULL,
23 x_step_group_type IN VARCHAR2 DEFAULT NULL,
24 x_final_result_ind IN VARCHAR2 DEFAULT NULL,
25 x_system_generated_ind IN VARCHAR2 DEFAULT NULL,
26 x_transaction_cat IN VARCHAR2 DEFAULT NULL,
27 x_encumbrance_level IN NUMBER DEFAULT NULL,
28 x_open_for_enrollments IN VARCHAR2 DEFAULT NULL,
29 x_system_calculated IN VARCHAR2 DEFAULT NULL,
30 x_system_mandatory_ind IN VARCHAR2 DEFAULT NULL,
31 x_default_display_seq IN NUMBER DEFAULT NULL,
32 x_av_transcript_disp_options IN VARCHAR2 DEFAULT NULL,
33 x_creation_date IN DATE DEFAULT NULL,
34 x_created_by IN NUMBER DEFAULT NULL,
35 x_last_update_date IN DATE DEFAULT NULL,
36 x_last_updated_by IN NUMBER DEFAULT NULL,
37 x_last_update_login IN NUMBER DEFAULT NULL
38 ) AS
39 /*
40 || Created By : [email protected]
41 || Created On : 27-SEP-2001
42 || Purpose : Initialises the Old and New references for the columns of the table.
43 || Known limitations, enhancements or remarks :
44 || Change History :
45 || Who When What
46 || (reverse chronological order - newest change first)
47 */
48
49 CURSOR cur_old_ref_values IS
50 SELECT *
51 FROM IGS_LOOKUPS_VAL
52 WHERE rowid = x_rowid;
53
54 BEGIN
55
56 l_rowid := x_rowid;
57
58 -- Code for setting the Old and New Reference Values.
59 -- Populate Old Values.
60 OPEN cur_old_ref_values;
61 FETCH cur_old_ref_values INTO old_references;
62 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
63 CLOSE cur_old_ref_values;
64 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
65 igs_ge_msg_stack.add;
66 app_exception.raise_exception;
67 RETURN;
68 END IF;
69 CLOSE cur_old_ref_values;
70
71 -- Populate New Values.
72 new_references.lookup_type := x_lookup_type;
73 new_references.lookup_code := x_lookup_code;
74 new_references.closed_ind := x_closed_ind;
75 new_references.security_allowed_ind := x_security_allowed_ind;
76 new_references.step_type_restriction_num_ind := x_step_type_restriction_num_in;
77 new_references.unit_outcome_ind := x_unit_outcome_ind;
78 new_references.display_name := x_display_name;
79 new_references.display_order := x_display_order;
80 new_references.step_order_applicable_ind := x_step_order_applicable_ind;
81 new_references.academic_transcript_ind := x_academic_transcript_ind;
82 new_references.cmpltn_requirements_ind := x_cmpltn_requirements_ind;
83 new_references.fee_ass_ind := x_fee_ass_ind;
84 new_references.step_group_type := x_step_group_type;
85 new_references.final_result_ind := x_final_result_ind;
86 new_references.system_generated_ind := x_system_generated_ind;
87 new_references.transaction_cat := x_transaction_cat;
88 new_references.encumbrance_level := x_encumbrance_level;
89 new_references.open_for_enrollments := x_open_for_enrollments;
90 new_references.system_calculated := x_system_calculated;
91 new_references.system_mandatory_ind := x_system_mandatory_ind;
92 new_references.default_display_seq := x_default_display_seq;
93 new_references.av_transcript_disp_options := x_av_transcript_disp_options;
94
95 IF (p_action = 'UPDATE') THEN
96 new_references.creation_date := old_references.creation_date;
97 new_references.created_by := old_references.created_by;
98 ELSE
99 new_references.creation_date := x_creation_date;
100 new_references.created_by := x_created_by;
101 END IF;
102
103 new_references.last_update_date := x_last_update_date;
104 new_references.last_updated_by := x_last_updated_by;
105 new_references.last_update_login := x_last_update_login;
106
107 END set_column_values;
108
109
110 PROCEDURE before_dml (
111 p_action IN VARCHAR2,
112 x_rowid IN VARCHAR2 DEFAULT NULL,
113 x_lookup_type IN VARCHAR2 DEFAULT NULL,
114 x_lookup_code IN VARCHAR2 DEFAULT NULL,
115 x_closed_ind IN VARCHAR2 DEFAULT NULL,
116 x_security_allowed_ind IN VARCHAR2 DEFAULT NULL,
117 x_step_type_restriction_num_in IN VARCHAR2 DEFAULT NULL,
118 x_unit_outcome_ind IN VARCHAR2 DEFAULT NULL,
119 x_display_name IN VARCHAR2 DEFAULT NULL,
120 x_display_order IN NUMBER DEFAULT NULL,
121 x_step_order_applicable_ind IN VARCHAR2 DEFAULT NULL,
122 x_academic_transcript_ind IN VARCHAR2 DEFAULT NULL,
123 x_cmpltn_requirements_ind IN VARCHAR2 DEFAULT NULL,
124 x_fee_ass_ind IN VARCHAR2 DEFAULT NULL,
125 x_step_group_type IN VARCHAR2 DEFAULT NULL,
126 x_final_result_ind IN VARCHAR2 DEFAULT NULL,
127 x_system_generated_ind IN VARCHAR2 DEFAULT NULL,
128 x_transaction_cat IN VARCHAR2 DEFAULT NULL,
129 x_encumbrance_level IN NUMBER DEFAULT NULL,
130 x_open_for_enrollments IN VARCHAR2 DEFAULT NULL,
131 x_system_calculated IN VARCHAR2 DEFAULT NULL,
132 x_system_mandatory_ind IN VARCHAR2 DEFAULT NULL,
133 x_default_display_seq IN NUMBER DEFAULT NULL,
134 x_av_transcript_disp_options IN VARCHAR2 DEFAULT NULL,
135 x_creation_date IN DATE DEFAULT NULL,
136 x_created_by IN NUMBER DEFAULT NULL,
137 x_last_update_date IN DATE DEFAULT NULL,
138 x_last_updated_by IN NUMBER DEFAULT NULL,
139 x_last_update_login IN NUMBER DEFAULT NULL
140 ) AS
141 /*
142 || Created By : [email protected]
143 || Created On : 27-SEP-2001
144 || Purpose : Initialises the columns, Checks Constraints, Calls the
145 || Trigger Handlers for the table, before any DML operation.
146 || Known limitations, enhancements or remarks :
147 || Change History :
148 || Who When What
149 || (reverse chronological order - newest change first)
150 */
151 BEGIN
152
153 set_column_values (
154 p_action,
155 x_rowid,
156 x_lookup_type,
157 x_lookup_code,
158 x_closed_ind,
159 x_security_allowed_ind,
160 x_step_type_restriction_num_in,
161 x_unit_outcome_ind,
162 x_display_name,
163 x_display_order,
164 x_step_order_applicable_ind,
165 x_academic_transcript_ind,
166 x_cmpltn_requirements_ind,
167 x_fee_ass_ind,
168 x_step_group_type,
169 x_final_result_ind,
170 x_system_generated_ind,
171 x_transaction_cat,
172 x_encumbrance_level,
173 x_open_for_enrollments,
174 x_system_calculated,
175 x_system_mandatory_ind,
176 x_default_display_seq,
177 x_av_transcript_disp_options,
178 x_creation_date,
179 x_created_by,
180 x_last_update_date,
181 x_last_updated_by,
182 x_last_update_login
183 );
184
185 IF (p_action = 'INSERT') THEN
186 -- Call all the procedures related to Before Insertlinfo.
187 /*IF ( get_pk_for_validation(x_lookup_type,x_lookup_code
188
189 )
190 ) THEN
191 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
192 igs_ge_msg_stack.add;
193 app_exception.raise_exception;
194 END IF;
195 ELSIF (p_action = 'VALIDATE_INSERT') THEN
196 -- Call all the procedures related to Before Insertlinfo.
197 IF ( get_pk_for_validation (x_lookup_type,x_lookup_code
198
199 )
200 ) THEN
201 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
202 igs_ge_msg_stack.add;
203 app_exception.raise_exception;
204
205 END IF;
206 */
207 NULL;
208 END IF;
209
210 END before_dml;
211
212
213 PROCEDURE insert_row (
214 x_rowid IN OUT NOCOPY VARCHAR2,
215 x_lookup_type IN OUT NOCOPY VARCHAR2,
216 x_lookup_code IN VARCHAR2,
217 x_closed_ind IN VARCHAR2,
218 x_security_allowed_ind IN VARCHAR2,
219 x_step_type_restriction_num_in IN VARCHAR2,
220 x_unit_outcome_ind IN VARCHAR2,
221 x_display_name IN VARCHAR2,
222 x_display_order IN NUMBER,
223 x_step_order_applicable_ind IN VARCHAR2,
224 x_academic_transcript_ind IN VARCHAR2,
225 x_cmpltn_requirements_ind IN VARCHAR2,
226 x_fee_ass_ind IN VARCHAR2,
227 x_step_group_type IN VARCHAR2,
228 x_final_result_ind IN VARCHAR2,
229 x_system_generated_ind IN VARCHAR2,
230 x_transaction_cat IN VARCHAR2,
231 x_encumbrance_level IN NUMBER,
232 x_open_for_enrollments IN VARCHAR2,
233 x_system_calculated IN VARCHAR2,
234 x_system_mandatory_ind IN VARCHAR2,
235 x_default_display_seq IN NUMBER,
236 x_av_transcript_disp_options IN VARCHAR2,
237 x_mode IN VARCHAR2
238 ) AS
239 /*
240 || Created By : [email protected]
241 || Created On : 27-SEP-2001
242 || Purpose : Handles the INSERT DML logic for the table.
243 || Known limitations, enhancements or remarks :
244 || Change History :
245 || Who When What
246 || (reverse chronological order - newest change first)
247 || ssawhney For perf reasons, used DML returning to return the ROWID
248 */
249
250
251
252 x_last_update_date DATE;
253 x_last_updated_by NUMBER;
254 x_last_update_login NUMBER;
255
256 BEGIN
257
258 x_last_update_date := SYSDATE;
259 IF (x_mode = 'I') THEN
260 x_last_updated_by := 1;
261 x_last_update_login := 0;
262 ELSIF (x_mode = 'R') THEN
263 x_last_updated_by := fnd_global.user_id;
264 IF (x_last_updated_by IS NULL) THEN
265 x_last_updated_by := -1;
266 END IF;
267 x_last_update_login := fnd_global.login_id;
268 IF (x_last_update_login IS NULL) THEN
269 x_last_update_login := -1;
270 END IF;
271 ELSE
272 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
273 igs_ge_msg_stack.add;
274 app_exception.raise_exception;
275 END IF;
276
277
278 before_dml(
279 p_action => 'INSERT',
280 x_rowid => x_rowid,
281 x_lookup_type => x_lookup_type,
282 x_lookup_code => x_lookup_code,
283 x_closed_ind => x_closed_ind,
284 x_security_allowed_ind => x_security_allowed_ind,
285 x_step_type_restriction_num_in => x_step_type_restriction_num_in,
286 x_unit_outcome_ind => x_unit_outcome_ind,
287 x_display_name => x_display_name,
288 x_display_order => x_display_order,
289 x_step_order_applicable_ind => x_step_order_applicable_ind,
290 x_academic_transcript_ind => x_academic_transcript_ind,
291 x_cmpltn_requirements_ind => x_cmpltn_requirements_ind,
292 x_fee_ass_ind => x_fee_ass_ind,
293 x_step_group_type => x_step_group_type,
294 x_final_result_ind => x_final_result_ind,
295 x_system_generated_ind => x_system_generated_ind,
296 x_transaction_cat => x_transaction_cat,
297 x_encumbrance_level => x_encumbrance_level,
298 x_open_for_enrollments => x_open_for_enrollments,
299 x_system_calculated => x_system_calculated,
300 x_system_mandatory_ind => x_system_mandatory_ind,
301 x_default_display_seq => x_default_display_seq,
302 x_av_transcript_disp_options => x_av_transcript_disp_options,
303 x_creation_date => x_last_update_date,
304 x_created_by => x_last_updated_by,
305 x_last_update_date => x_last_update_date,
306 x_last_updated_by => x_last_updated_by,
307 x_last_update_login => x_last_update_login
308 );
309
310 INSERT INTO igs_lookups_val (
311 lookup_type,
312 lookup_code,
313 closed_ind,
314 security_allowed_ind,
315 step_type_restriction_num_ind,
316 unit_outcome_ind,
320 academic_transcript_ind,
317 display_name,
318 display_order,
319 step_order_applicable_ind,
321 cmpltn_requirements_ind,
322 fee_ass_ind,
323 step_group_type,
324 final_result_ind,
325 system_generated_ind,
326 transaction_cat,
327 encumbrance_level,
328 open_for_enrollments,
329 system_calculated,
330 system_mandatory_ind,
331 default_display_seq,
332 av_transcript_disp_options,
333 creation_date,
334 created_by,
335 last_update_date,
336 last_updated_by,
337 last_update_login
338 ) VALUES (
339 new_references.lookup_type,
340 new_references.lookup_code,
341 new_references.closed_ind,
342 new_references.security_allowed_ind,
343 new_references.step_type_restriction_num_ind,
344 new_references.unit_outcome_ind,
345 new_references.display_name,
346 new_references.display_order,
347 new_references.step_order_applicable_ind,
348 new_references.academic_transcript_ind,
349 new_references.cmpltn_requirements_ind,
350 new_references.fee_ass_ind,
351 new_references.step_group_type,
352 new_references.final_result_ind,
353 new_references.system_generated_ind,
354 new_references.transaction_cat,
355 new_references.encumbrance_level,
356 new_references.open_for_enrollments,
357 new_references.system_calculated,
358 new_references.system_mandatory_ind,
359 new_references.default_display_seq,
360 new_references.av_transcript_disp_options,
361 x_last_update_date,
362 x_last_updated_by,
363 x_last_update_date,
364 x_last_updated_by,
365 x_last_update_login
366 ) RETURNING ROWID INTO x_rowid;
367
368
369 END insert_row;
370
371 PROCEDURE lock_row (
372 x_rowid IN VARCHAR2,
373 x_lookup_type IN VARCHAR2,
374 x_lookup_code IN VARCHAR2,
375 x_closed_ind IN VARCHAR2,
376 x_security_allowed_ind IN VARCHAR2,
377 x_step_type_restriction_num_in IN VARCHAR2,
378 x_unit_outcome_ind IN VARCHAR2,
379 x_display_name IN VARCHAR2,
380 x_display_order IN NUMBER,
381 x_step_order_applicable_ind IN VARCHAR2,
382 x_academic_transcript_ind IN VARCHAR2,
383 x_cmpltn_requirements_ind IN VARCHAR2,
384 x_fee_ass_ind IN VARCHAR2,
385 x_step_group_type IN VARCHAR2,
386 x_final_result_ind IN VARCHAR2,
387 x_system_generated_ind IN VARCHAR2,
388 x_transaction_cat IN VARCHAR2,
389 x_encumbrance_level IN NUMBER,
390 x_open_for_enrollments IN VARCHAR2,
391 x_system_calculated IN VARCHAR2,
392 x_system_mandatory_ind IN VARCHAR2,
393 x_default_display_seq IN NUMBER,
394 x_av_transcript_disp_options IN VARCHAR2
395 ) AS
396 /*
397 || Created By : [email protected]
398 || Created On : 27-SEP-2001
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 lookup_type,
408 lookup_code,
409 closed_ind,
410 security_allowed_ind,
411 step_type_restriction_num_ind,
412 unit_outcome_ind,
413 display_name,
414 display_order,
415 step_order_applicable_ind,
416 academic_transcript_ind,
417 cmpltn_requirements_ind,
418 fee_ass_ind,
419 step_group_type,
420 final_result_ind,
421 system_generated_ind,
422 transaction_cat,
423 encumbrance_level,
424 open_for_enrollments,
425 system_calculated,
426 system_mandatory_ind,
427 default_display_seq,
428 av_transcript_disp_options
429 FROM igs_lookups_val
430 WHERE rowid = x_rowid
431 FOR UPDATE NOWAIT;
432
433 tlinfo c1%ROWTYPE;
434
435 BEGIN
436
437 OPEN c1;
438 FETCH c1 INTO tlinfo;
439 IF (c1%notfound) THEN
440 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
441 igs_ge_msg_stack.add;
442 CLOSE c1;
443 app_exception.raise_exception;
444 RETURN;
445 END IF;
446 CLOSE c1;
447
448 IF (
449 (tlinfo.lookup_type = x_lookup_type)
450 AND (tlinfo.lookup_code = x_lookup_code)
451 AND ((tlinfo.closed_ind = x_closed_ind) OR ((tlinfo.closed_ind IS NULL) AND (X_closed_ind IS NULL)))
452 AND ((tlinfo.security_allowed_ind = x_security_allowed_ind) OR ((tlinfo.security_allowed_ind IS NULL) AND (X_security_allowed_ind IS NULL)))
456 AND ((tlinfo.display_order = x_display_order) OR ((tlinfo.display_order IS NULL) AND (X_display_order IS NULL)))
453 AND ((tlinfo.step_type_restriction_num_ind = x_step_type_restriction_num_in) OR ((tlinfo.step_type_restriction_num_ind IS NULL) AND (X_step_type_restriction_num_in IS NULL)))
454 AND ((tlinfo.unit_outcome_ind = x_unit_outcome_ind) OR ((tlinfo.unit_outcome_ind IS NULL) AND (X_unit_outcome_ind IS NULL)))
455 AND ((tlinfo.display_name = x_display_name) OR ((tlinfo.display_name IS NULL) AND (X_display_name IS NULL)))
457 AND ((tlinfo.step_order_applicable_ind = x_step_order_applicable_ind) OR ((tlinfo.step_order_applicable_ind IS NULL) AND (X_step_order_applicable_ind IS NULL)))
458 AND ((tlinfo.academic_transcript_ind = x_academic_transcript_ind) OR ((tlinfo.academic_transcript_ind IS NULL) AND (X_academic_transcript_ind IS NULL)))
459 AND ((tlinfo.cmpltn_requirements_ind = x_cmpltn_requirements_ind) OR ((tlinfo.cmpltn_requirements_ind IS NULL) AND (X_cmpltn_requirements_ind IS NULL)))
460 AND ((tlinfo.fee_ass_ind = x_fee_ass_ind) OR ((tlinfo.fee_ass_ind IS NULL) AND (X_fee_ass_ind IS NULL)))
461 AND ((tlinfo.step_group_type = x_step_group_type) OR ((tlinfo.step_group_type IS NULL) AND (X_step_group_type IS NULL)))
462 AND ((tlinfo.final_result_ind = x_final_result_ind) OR ((tlinfo.final_result_ind IS NULL) AND (X_final_result_ind IS NULL)))
463 AND ((tlinfo.system_generated_ind = x_system_generated_ind) OR ((tlinfo.system_generated_ind IS NULL) AND (X_system_generated_ind IS NULL)))
464 AND ((tlinfo.transaction_cat = x_transaction_cat) OR ((tlinfo.transaction_cat IS NULL) AND (X_transaction_cat IS NULL)))
465 AND ((tlinfo.encumbrance_level = x_encumbrance_level) OR ((tlinfo.encumbrance_level IS NULL) AND (X_encumbrance_level IS NULL)))
466 AND ((tlinfo.open_for_enrollments = x_open_for_enrollments) OR ((tlinfo.open_for_enrollments IS NULL) AND (X_open_for_enrollments IS NULL)))
467 AND ((tlinfo.system_calculated = x_system_calculated) OR ((tlinfo.system_calculated IS NULL) AND (X_system_calculated IS NULL)))
468 AND ((tlinfo.system_mandatory_ind = x_system_mandatory_ind) OR ((tlinfo.system_mandatory_ind IS NULL) AND (X_system_mandatory_ind IS NULL)))
469 AND ((tlinfo.default_display_seq = x_default_display_seq) OR ((tlinfo.default_display_seq IS NULL) AND (X_default_display_seq IS NULL)))
470 AND ((tlinfo.av_transcript_disp_options = x_av_transcript_disp_options) OR ((tlinfo.av_transcript_disp_options IS NULL) AND (X_av_transcript_disp_options IS NULL)))
471 ) THEN
472 NULL;
473 ELSE
474 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
475 igs_ge_msg_stack.add;
476 app_exception.raise_exception;
477 END IF;
478
479 RETURN;
480
481 END lock_row;
482
483
484 PROCEDURE update_row (
485 x_rowid IN VARCHAR2,
486 x_lookup_type IN VARCHAR2,
487 x_lookup_code IN VARCHAR2,
488 x_closed_ind IN VARCHAR2,
489 x_security_allowed_ind IN VARCHAR2,
490 x_step_type_restriction_num_in IN VARCHAR2,
491 x_unit_outcome_ind IN VARCHAR2,
492 x_display_name IN VARCHAR2,
493 x_display_order IN NUMBER,
494 x_step_order_applicable_ind IN VARCHAR2,
495 x_academic_transcript_ind IN VARCHAR2,
496 x_cmpltn_requirements_ind IN VARCHAR2,
497 x_fee_ass_ind IN VARCHAR2,
498 x_step_group_type IN VARCHAR2,
499 x_final_result_ind IN VARCHAR2,
500 x_system_generated_ind IN VARCHAR2,
501 x_transaction_cat IN VARCHAR2,
502 x_encumbrance_level IN NUMBER,
503 x_open_for_enrollments IN VARCHAR2,
504 x_system_calculated IN VARCHAR2,
505 x_system_mandatory_ind IN VARCHAR2,
506 x_default_display_seq IN NUMBER,
507 x_av_transcript_disp_options IN VARCHAR2,
508 x_mode IN VARCHAR2
509 ) AS
510 /*
511 || Created By : [email protected]
512 || Created On : 27-SEP-2001
513 || Purpose : Handles the UPDATE DML logic for the table.
514 || Known limitations, enhancements or remarks :
515 || Change History :
516 || Who When What
517 || (reverse chronological order - newest change first)
518 */
519 x_last_update_date DATE ;
520 x_last_updated_by NUMBER;
521 x_last_update_login NUMBER;
522
523 BEGIN
524
525 x_last_update_date := SYSDATE;
526 IF (X_MODE = 'I') THEN
527 x_last_updated_by := 1;
528 x_last_update_login := 0;
529 ELSIF (x_mode = 'R') THEN
530 x_last_updated_by := fnd_global.user_id;
531 IF x_last_updated_by IS NULL THEN
532 x_last_updated_by := -1;
533 END IF;
534 x_last_update_login := fnd_global.login_id;
535 IF (x_last_update_login IS NULL) THEN
536 x_last_update_login := -1;
537 END IF;
538 ELSE
539 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
540 igs_ge_msg_stack.add;
541 app_exception.raise_exception;
542 END IF;
543
544 before_dml(
545 p_action => 'UPDATE',
546 x_rowid => x_rowid,
550 x_security_allowed_ind => x_security_allowed_ind,
547 x_lookup_type => x_lookup_type,
548 x_lookup_code => x_lookup_code,
549 x_closed_ind => x_closed_ind,
551 x_step_type_restriction_num_in => x_step_type_restriction_num_in,
552 x_unit_outcome_ind => x_unit_outcome_ind,
553 x_display_name => x_display_name,
554 x_display_order => x_display_order,
555 x_step_order_applicable_ind => x_step_order_applicable_ind,
556 x_academic_transcript_ind => x_academic_transcript_ind,
557 x_cmpltn_requirements_ind => x_cmpltn_requirements_ind,
558 x_fee_ass_ind => x_fee_ass_ind,
559 x_step_group_type => x_step_group_type,
560 x_final_result_ind => x_final_result_ind,
561 x_system_generated_ind => x_system_generated_ind,
562 x_transaction_cat => x_transaction_cat,
563 x_encumbrance_level => x_encumbrance_level,
564 x_open_for_enrollments => x_open_for_enrollments,
565 x_system_calculated => x_system_calculated,
566 x_system_mandatory_ind => x_system_mandatory_ind,
567 x_default_display_seq => x_default_display_seq,
568 x_av_transcript_disp_options => x_av_transcript_disp_options,
569 x_creation_date => x_last_update_date,
570 x_created_by => x_last_updated_by,
571 x_last_update_date => x_last_update_date,
572 x_last_updated_by => x_last_updated_by,
573 x_last_update_login => x_last_update_login
574 );
575
576 UPDATE igs_lookups_val
577 SET
578 av_transcript_disp_options = new_references.av_transcript_disp_options,
579 last_update_date = x_last_update_date,
580 last_updated_by = x_last_updated_by,
581 last_update_login = x_last_update_login
582 WHERE rowid = x_rowid;
583
584 IF (SQL%NOTFOUND) THEN
585 RAISE NO_DATA_FOUND;
586 END IF;
587
588 END update_row;
589
590
591 PROCEDURE add_row (
592 x_rowid IN OUT NOCOPY VARCHAR2,
593 x_lookup_type IN OUT NOCOPY VARCHAR2,
594 x_lookup_code IN VARCHAR2,
595 x_closed_ind IN VARCHAR2,
596 x_security_allowed_ind IN VARCHAR2,
597 x_step_type_restriction_num_in IN VARCHAR2,
598 x_unit_outcome_ind IN VARCHAR2,
599 x_display_name IN VARCHAR2,
600 x_display_order IN NUMBER,
601 x_step_order_applicable_ind IN VARCHAR2,
602 x_academic_transcript_ind IN VARCHAR2,
603 x_cmpltn_requirements_ind IN VARCHAR2,
604 x_fee_ass_ind IN VARCHAR2,
605 x_step_group_type IN VARCHAR2,
606 x_final_result_ind IN VARCHAR2,
607 x_system_generated_ind IN VARCHAR2,
608 x_transaction_cat IN VARCHAR2,
609 x_encumbrance_level IN NUMBER,
610 x_open_for_enrollments IN VARCHAR2,
611 x_system_calculated IN VARCHAR2,
612 x_system_mandatory_ind IN VARCHAR2,
613 x_default_display_seq IN NUMBER,
614 x_av_transcript_disp_options IN VARCHAR2,
615 x_mode IN VARCHAR2
616 ) AS
617 /*
618 || Created By : [email protected]
619 || Created On : 27-SEP-2001
620 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
621 || Known limitations, enhancements or remarks :
622 || Change History :
623 || Who When What
624 || (reverse chronological order - newest change first)
625 */
626 CURSOR c1 IS
627 SELECT rowid
628 FROM igs_lookups_val
629 WHERE lookup_type = x_lookup_type AND
630 lookup_code = x_lookup_code ;
631
632
633
634 BEGIN
635
636 OPEN c1;
637 FETCH c1 INTO x_rowid;
638 IF (c1%NOTFOUND) THEN
639 CLOSE c1;
640
641 insert_row (
642 x_rowid,
643 x_lookup_type,
644 x_lookup_code,
645 x_closed_ind,
646 x_security_allowed_ind,
647 x_step_type_restriction_num_in,
648 x_unit_outcome_ind,
649 x_display_name,
650 x_display_order,
651 x_step_order_applicable_ind,
652 x_academic_transcript_ind,
653 x_cmpltn_requirements_ind,
654 x_fee_ass_ind,
655 x_step_group_type,
656 x_final_result_ind,
657 x_system_generated_ind,
658 x_transaction_cat,
659 x_encumbrance_level,
660 x_open_for_enrollments,
661 x_system_calculated,
662 x_system_mandatory_ind,
663 x_default_display_seq,
664 x_av_transcript_disp_options,
665 x_mode
666 );
667 RETURN;
668 END IF;
669 CLOSE c1;
670
671 update_row (
672 x_rowid,
673 x_lookup_type,
674 x_lookup_code,
675 x_closed_ind,
676 x_security_allowed_ind,
677 x_step_type_restriction_num_in,
678 x_unit_outcome_ind,
679 x_display_name,
680 x_display_order,
681 x_step_order_applicable_ind,
682 x_academic_transcript_ind,
683 x_cmpltn_requirements_ind,
684 x_fee_ass_ind,
685 x_step_group_type,
686 x_final_result_ind,
687 x_system_generated_ind,
688 x_transaction_cat,
689 x_encumbrance_level,
690 x_open_for_enrollments,
691 x_system_calculated,
692 x_system_mandatory_ind,
693 x_default_display_seq,
694 x_av_transcript_disp_options,
695 x_mode
696 );
697
698 END add_row;
699
700
701 PROCEDURE delete_row (
702 x_rowid IN VARCHAR2
703 ) AS
704 /*
705 || Created By : [email protected]
706 || Created On : 27-SEP-2001
707 || Purpose : Handles the DELETE DML logic for the table.
708 || Known limitations, enhancements or remarks :
709 || Change History :
710 || Who When What
711 || (reverse chronological order - newest change first)
712 */
713 BEGIN
714
715 before_dml (
716 p_action => 'DELETE',
717 x_rowid => x_rowid
718 );
719
720 DELETE FROM igs_lookups_val
721 WHERE rowid = x_rowid;
722
723 IF (SQL%NOTFOUND) THEN
724 RAISE NO_DATA_FOUND;
725 END IF;
726
727 END delete_row;
728
729
730 END igs_lookups_val_pkg;