[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_SS_APPL_TYP_PKG
Source
1 PACKAGE BODY igs_ad_ss_appl_typ_pkg AS
2 /* $Header: IGSAIF8B.pls 120.2 2005/12/15 03:52:25 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ad_ss_appl_typ%ROWTYPE;
6 new_references igs_ad_ss_appl_typ%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_admission_application_type IN VARCHAR2,
12 x_description IN VARCHAR2,
13 x_admission_cat IN VARCHAR2,
14 x_s_admission_process_type IN VARCHAR2,
15 x_configurability_func_name IN VARCHAR2,
16 x_application_fee_amount IN NUMBER,
17 x_gl_rev_acct_ccid IN NUMBER,
18 x_gl_cash_acct_ccid IN NUMBER,
19 x_rev_account_code IN VARCHAR2,
20 x_cash_account_code IN VARCHAR2,
21 x_closed_ind IN VARCHAR2,
22 x_creation_date IN DATE,
23 x_created_by IN NUMBER,
24 x_last_update_date IN DATE,
25 x_last_updated_by IN NUMBER,
26 x_last_update_login IN NUMBER,
27 x_system_default IN VARCHAR2,
28 x_enroll_deposit_amount IN NUMBER,
29 x_enroll_deposit_level IN VARCHAR2,
30 x_use_in_appl_self_srvc IN VARCHAR2,
31 x_crt_rev_instr IN VARCHAR2,
32 x_submit_instr IN VARCHAR2,
33 x_submit_err_instr IN VARCHAR2
34 ) AS
35 /*
36 || Created By : [email protected]
37 || Created On : 10-DEC-2001
38 || Purpose : Initialises the Old and New references for the columns of the table.
39 || Known limitations, enhancements or remarks :
40 || Change History :
41 || Who When What
42 || (reverse chronological order - newest change first)
43 */
44
45 CURSOR cur_old_ref_values IS
46 SELECT *
47 FROM igs_ad_ss_appl_typ
48 WHERE rowid = x_rowid;
49
50 BEGIN
51
52 l_rowid := x_rowid;
53
54 -- Code for setting the Old and New Reference Values.
55 -- Populate Old Values.
56 OPEN cur_old_ref_values;
57 FETCH cur_old_ref_values INTO old_references;
58 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
59 CLOSE cur_old_ref_values;
60 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
61 igs_ge_msg_stack.add;
62 app_exception.raise_exception;
63 RETURN;
64 END IF;
65 CLOSE cur_old_ref_values;
66
67 -- Populate New Values.
68 new_references.admission_application_type := x_admission_application_type;
69 new_references.description := x_description;
70 new_references.admission_cat := x_admission_cat;
71 new_references.s_admission_process_type := x_s_admission_process_type;
72 new_references.configurability_func_name := x_configurability_func_name;
73 new_references.application_fee_amount := x_application_fee_amount;
74 new_references.gl_rev_acct_ccid := x_gl_rev_acct_ccid;
75 new_references.gl_cash_acct_ccid := x_gl_cash_acct_ccid;
76 new_references.rev_account_code := x_rev_account_code;
77 new_references.cash_account_code := x_cash_account_code;
78 new_references.closed_ind := x_closed_ind;
79 new_references.system_default := x_system_default;
80 new_references.enroll_deposit_amount := x_enroll_deposit_amount;
81 new_references.enroll_deposit_level := x_enroll_deposit_level;
82 new_references.use_in_appl_self_srvc := x_use_in_appl_self_srvc;
83 IF (p_action = 'UPDATE') THEN
84 new_references.creation_date := old_references.creation_date;
85 new_references.created_by := old_references.created_by;
86 ELSE
87 new_references.creation_date := x_creation_date;
88 new_references.created_by := x_created_by;
89 END IF;
90
91 new_references.last_update_date := x_last_update_date;
92 new_references.last_updated_by := x_last_updated_by;
93 new_references.last_update_login := x_last_update_login;
94 new_references.crt_rev_instr := x_crt_rev_instr;
95 new_references.submit_instr := x_submit_instr;
96 new_references.submit_err_instr := x_submit_err_instr;
97
98 END set_column_values;
99
100 PROCEDURE check_parent_existance AS
101 /*
102 || Created By : rghosh
103 || Created On : 04-oct-2002
104 || Purpose : Checks for the existance of Parent records.
105 || Known limitations, enhancements or remarks :
106 || Change History :
107 || Who When What
108 || (reverse chronological order - newest change first)
109 */
110 BEGIN
111
112 IF (((old_references.admission_cat = new_references.admission_cat) AND
113 (old_references.s_admission_process_type = new_references.s_admission_process_type)) OR
114 ((new_references.admission_cat IS NULL) OR
115 (new_references.s_admission_process_type IS NULL))) THEN
116 NULL;
117 ELSE
118 IF NOT IGS_AD_PRCS_CAT_PKG.Get_PK_For_Validation (
119 new_references.admission_cat,
120 new_references.s_admission_process_type,
121 'N' ) THEN
122 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
123 IGS_GE_MSG_STACK.ADD;
124 App_Exception.Raise_Exception;
125 END IF;
126 END IF;
127
128 END Check_Parent_Existance;
129
130
131 PROCEDURE check_child_existance IS
132 /*
133 || Created By : [email protected]
134 || Created On : 10-DEC-2001
135 || Purpose : Checks for the existance of Child records.
136 || Known limitations, enhancements or remarks :
137 || Change History :
138 || Who When What
139 || (reverse chronological order - newest change first)
140 || vdixit 09-Jan-2001 Calls to igs_ad_aptyp_pestat_pkg.get_fk_igs_ad_ss_appl_typ
141 || and igs_ad_pestat_group_pkg.get_fk_igs_ad_ss_appl_typ added
142 || a part of Enh. 2152871
143 || rboddu 26-dec-2001 added call to igs_ad_appl_pkg.get_fk_igs_ad_ss_appl_typ procedure.
144 || Bug no : 2158524
145 || rghosh 28-oct-2002 Added the get fk calls to the tables IGS_AD_DEPLVL_PRG and
146 || IGS_AD_DEPLVL_PRGTY for Bug #2602077
147 */
148 BEGIN
149
150 igs_ad_appl_pkg.get_fk_igs_ad_ss_appl_typ(
151 old_references.admission_application_type
152 );
153
154 igs_ad_aptyp_pestat_pkg.get_fk_igs_ad_ss_appl_typ(
155 old_references.admission_application_type
156 );
157
158 igs_ad_pestat_group_pkg.get_fk_igs_ad_ss_appl_typ(
159 old_references.admission_application_type
160 );
161 igs_ad_deplvl_prgty_pkg.get_fk_igs_ad_ss_appl_typ(
162 old_references.admission_application_type
163 );
164 igs_ad_deplvl_prg_pkg.get_fk_igs_ad_ss_appl_typ(
165 old_references.admission_application_type
166 );
167
168 igs_uc_defaults_pkg.get_fk_igs_ad_ss_appl_typ(
169 old_references.admission_application_type );
170
171
172 END check_child_existance;
173
174
175 FUNCTION get_pk_for_validation (
176 x_admission_application_type IN VARCHAR2,
177 x_closed_ind IN VARCHAR2
178 ) RETURN BOOLEAN AS
179 /*
180 || Created By : [email protected]
181 || Created On : 10-DEC-2001
182 || Purpose : Validates the Primary Key of the table.
183 || Known limitations, enhancements or remarks :
184 || Change History :
185 || Who When What
186 || (reverse chronological order - newest change first)
187 */
188 CURSOR cur_rowid IS
189 SELECT rowid
190 FROM igs_ad_ss_appl_typ
191 WHERE UPPER(admission_application_type) = UPPER(x_admission_application_type) AND
192 closed_ind = NVL(x_closed_ind,closed_ind);
193
194 lv_rowid cur_rowid%RowType;
195
196 BEGIN
197
198 OPEN cur_rowid;
199 FETCH cur_rowid INTO lv_rowid;
200 IF (cur_rowid%FOUND) THEN
201 CLOSE cur_rowid;
202 RETURN(TRUE);
203 ELSE
204 CLOSE cur_rowid;
205 RETURN(FALSE);
206 END IF;
207
208 END get_pk_for_validation;
209
210
211 PROCEDURE get_fk_igs_ad_prcs_cat (
212 x_admission_cat IN VARCHAR2,
213 x_s_admission_process_type IN VARCHAR2
214 ) AS
215 /*************************************************************
216 Created By :rghosh
217 Date Created By : 04-oct-2002
218 Purpose :checks for the presence of child record when parent record is deleted
219 Know limitations, enhancements or remarks
220 Change History
221 Who When What
222 (reverse chronological order - newest change first)
223 ***************************************************************/
224 CURSOR cur_rowid IS
225 SELECT rowid
226 FROM igs_ad_ss_appl_typ
227 WHERE admission_cat = x_admission_cat
228 AND s_admission_process_type = x_s_admission_process_type ;
229
230 lv_rowid cur_rowid%RowType;
231
232 BEGIN
233
234 OPEN cur_rowid;
235 FETCH cur_rowid INTO lv_rowid;
236 IF (cur_rowid%FOUND) THEN
237 CLOSE cur_rowid;
238 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_SSAT_PRC_FK');
239 IGS_GE_MSG_STACK.ADD;
240 App_Exception.Raise_Exception;
241 Return;
242 END IF;
243 CLOSE cur_rowid;
244
245 END get_fk_igs_ad_prcs_cat;
246
247
248
249 PROCEDURE Check_Constraints (
250 Column_Name IN VARCHAR2,
251 Column_Value IN VARCHAR2
252 )
253 AS
254 BEGIN
255 IF column_name is null then
256 NULL;
257 ELSIF upper(Column_name) = 'CLOSED_IND' then
258 new_references.closed_ind := column_value;
259 END IF;
260
261 IF upper(column_name) = 'CLOSED_IND' OR
262 column_name is null Then
263 IF NOT (new_references.closed_ind IN ('Y','N')) THEN
264 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
265 IGS_GE_MSG_STACK.ADD;
266 App_Exception.Raise_Exception;
267 END IF;
268 END IF;
269
270 END Check_Constraints;
271
272
273
274 PROCEDURE before_dml (
275 p_action IN VARCHAR2,
276 x_rowid IN VARCHAR2,
277 x_admission_application_type IN VARCHAR2,
278 x_description IN VARCHAR2,
279 x_admission_cat IN VARCHAR2,
280 x_s_admission_process_type IN VARCHAR2,
281 x_configurability_func_name IN VARCHAR2,
282 x_application_fee_amount IN NUMBER,
283 x_gl_rev_acct_ccid IN NUMBER,
284 x_gl_cash_acct_ccid IN NUMBER,
285 x_rev_account_code IN VARCHAR2,
286 x_cash_account_code IN VARCHAR2,
287 x_closed_ind IN VARCHAR2,
288 x_creation_date IN DATE,
289 x_created_by IN NUMBER,
290 x_last_update_date IN DATE,
291 x_last_updated_by IN NUMBER,
292 x_last_update_login IN NUMBER,
293 x_system_default IN VARCHAR2,
294 x_enroll_deposit_amount IN NUMBER,
295 x_enroll_deposit_level IN VARCHAR2,
296 x_use_in_appl_self_srvc IN VARCHAR2,
297 x_crt_rev_instr IN VARCHAR2,
298 x_submit_instr IN VARCHAR2,
299 x_submit_err_instr IN VARCHAR2
300 ) AS
301 /*
302 || Created By : [email protected]
303 || Created On : 10-DEC-2001
304 || Purpose : Initialises the columns, Checks Constraints, Calls the
305 || Trigger Handlers for the table, before any DML operation.
306 || Known limitations, enhancements or remarks :
307 || Change History :
308 || Who When What
309 || rghosh 04-oct-2002 added the PROCEDURE GET_FK_IGS_AD_PRCS_CAT
310 || and added the column system default for
311 || Bug # 2599457
312 || rghosh 17-oct-2002 added the columns enroll_deposit_amount and
313 || enroll_deposit_level columns for Bug
314 || #2602077
315 || (reverse chronological order - newest change first)
316 */
317 BEGIN
318
319 set_column_values (
320 p_action,
321 x_rowid,
322 x_admission_application_type,
323 x_description,
324 x_admission_cat,
325 x_s_admission_process_type,
326 x_configurability_func_name,
327 x_application_fee_amount,
328 x_gl_rev_acct_ccid,
329 x_gl_cash_acct_ccid,
330 x_rev_account_code,
331 x_cash_account_code,
332 x_closed_ind,
333 x_creation_date,
334 x_created_by,
335 x_last_update_date,
336 x_last_updated_by,
337 x_last_update_login,
338 x_system_default,
339 x_enroll_deposit_amount,
340 x_enroll_deposit_level,
341 x_use_in_appl_self_srvc,
342 x_crt_rev_instr,
343 x_submit_instr,
344 x_submit_err_instr
345 );
346
347 IF (p_action = 'INSERT') THEN
348 -- Call all the procedures related to Before Insert.
349 IF ( get_pk_for_validation(
350 new_references.admission_application_type
351 )
352 ) THEN
353 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
354 igs_ge_msg_stack.add;
358 -- Call all the procedures related to Before Delete.
355 app_exception.raise_exception;
356 END IF;
357 ELSIF (p_action = 'DELETE') THEN
359 check_child_existance;
360 ELSIF (p_action = 'VALIDATE_INSERT') THEN
361 -- Call all the procedures related to Before Insert.
362 IF ( get_pk_for_validation (
363 new_references.admission_application_type
364 )
365 ) THEN
366 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
367 igs_ge_msg_stack.add;
368 app_exception.raise_exception;
369 END IF;
370 ELSIF (p_action = 'VALIDATE_DELETE') THEN
371 check_child_existance;
372 END IF;
373
374 END before_dml;
375
376
377 PROCEDURE insert_row (
378 x_rowid IN OUT NOCOPY VARCHAR2,
379 x_admission_application_type IN VARCHAR2,
380 x_description IN VARCHAR2,
381 x_admission_cat IN VARCHAR2,
382 x_s_admission_process_type IN VARCHAR2,
383 x_configurability_func_name IN VARCHAR2,
384 x_application_fee_amount IN NUMBER,
385 x_gl_rev_acct_ccid IN NUMBER,
386 x_gl_cash_acct_ccid IN NUMBER,
387 x_rev_account_code IN VARCHAR2,
388 x_cash_account_code IN VARCHAR2,
389 x_closed_ind IN VARCHAR2,
390 x_mode IN VARCHAR2,
391 x_system_default IN VARCHAR2,
392 x_enroll_deposit_amount IN NUMBER,
393 x_enroll_deposit_level IN VARCHAR2,
394 x_use_in_appl_self_srvc IN VARCHAR2,
395 x_crt_rev_instr IN VARCHAR2,
396 x_submit_instr IN VARCHAR2,
397 x_submit_err_instr IN VARCHAR2
398 ) AS
399 /*
400 || Created By : [email protected]
401 || Created On : 10-DEC-2001
402 || Purpose : Handles the INSERT DML logic for the table.
403 || Known limitations, enhancements or remarks :
404 || Change History :
405 || Who When What
406 || rghosh 04-oct-2002 added the PROCEDURE GET_FK_IGS_AD_PRCS_CAT
407 || and added the column system default for
408 || Bug # 2599457
409 || rghosh 17-oct-2002 added the columns enroll_deposit_amount and
410 || enroll_deposit_level columns for Bug
411 || #2602077
412 || (reverse chronological order - newest change first)
413 */
414 CURSOR c IS
415 SELECT rowid
416 FROM igs_ad_ss_appl_typ
417 WHERE admission_application_type = x_admission_application_type;
418
419 x_last_update_date DATE;
420 x_last_updated_by NUMBER;
421 x_last_update_login NUMBER;
422
423 BEGIN
424
425 x_last_update_date := SYSDATE;
426 IF (x_mode = 'I') THEN
427 x_last_updated_by := 1;
428 x_last_update_login := 0;
429 ELSIF (x_mode = 'R') THEN
430 x_last_updated_by := fnd_global.user_id;
431 IF (x_last_updated_by IS NULL) THEN
432 x_last_updated_by := -1;
433 END IF;
434 x_last_update_login := fnd_global.login_id;
435 IF (x_last_update_login IS NULL) THEN
436 x_last_update_login := -1;
437 END IF;
438 ELSE
439 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
440 igs_ge_msg_stack.add;
441 app_exception.raise_exception;
442 END IF;
443
444 before_dml(
445 p_action => 'INSERT',
446 x_rowid => x_rowid,
447 x_admission_application_type => x_admission_application_type,
448 x_description => x_description,
449 x_admission_cat => x_admission_cat,
450 x_s_admission_process_type => x_s_admission_process_type,
451 x_configurability_func_name => x_configurability_func_name,
452 x_application_fee_amount => x_application_fee_amount,
453 x_gl_rev_acct_ccid => x_gl_rev_acct_ccid,
454 x_gl_cash_acct_ccid => x_gl_cash_acct_ccid,
455 x_rev_account_code => x_rev_account_code,
456 x_cash_account_code => x_cash_account_code,
457 x_closed_ind => x_closed_ind,
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 x_system_default => x_system_default,
464 x_enroll_deposit_amount => x_enroll_deposit_amount,
465 x_enroll_deposit_level => x_enroll_deposit_level,
466 x_use_in_appl_self_srvc => x_use_in_appl_self_srvc,
467 x_crt_rev_instr => x_crt_rev_instr,
471
468 x_submit_instr => x_submit_instr,
469 x_submit_err_instr => x_submit_err_instr
470 );
472 INSERT INTO igs_ad_ss_appl_typ (
473 admission_application_type,
474 description,
475 admission_cat,
476 s_admission_process_type,
477 configurability_func_name,
478 application_fee_amount,
479 gl_rev_acct_ccid,
480 gl_cash_acct_ccid,
481 rev_account_code,
482 cash_account_code,
483 closed_ind,
484 creation_date,
485 created_by,
486 last_update_date,
487 last_updated_by,
488 last_update_login,
489 system_default,
490 enroll_deposit_amount,
491 enroll_deposit_level,
492 use_in_appl_self_srvc,
493 crt_rev_instr,
494 submit_instr,
495 submit_err_instr
496 ) VALUES (
497 new_references.admission_application_type,
498 new_references.description,
499 new_references.admission_cat,
500 new_references.s_admission_process_type,
501 new_references.configurability_func_name,
502 new_references.application_fee_amount,
503 new_references.gl_rev_acct_ccid,
504 new_references.gl_cash_acct_ccid,
505 new_references.rev_account_code,
506 new_references.cash_account_code,
507 new_references.closed_ind,
508 x_last_update_date,
509 x_last_updated_by,
510 x_last_update_date,
511 x_last_updated_by,
512 x_last_update_login,
513 new_references.system_default,
514 new_references.enroll_deposit_amount,
515 new_references.enroll_deposit_level,
516 new_references.use_in_appl_self_srvc,
517 new_references.crt_rev_instr,
518 new_references.submit_instr,
519 new_references.submit_err_instr
520 );
521
522 OPEN c;
523 FETCH c INTO x_rowid;
524 IF (c%NOTFOUND) THEN
525 CLOSE c;
526 RAISE NO_DATA_FOUND;
527 END IF;
528 CLOSE c;
529
530 END insert_row;
531
532
533 PROCEDURE lock_row (
534 x_rowid IN VARCHAR2,
535 x_admission_application_type IN VARCHAR2,
536 x_description IN VARCHAR2,
537 x_admission_cat IN VARCHAR2,
538 x_s_admission_process_type IN VARCHAR2,
539 x_configurability_func_name IN VARCHAR2,
540 x_application_fee_amount IN NUMBER,
541 x_gl_rev_acct_ccid IN NUMBER,
542 x_gl_cash_acct_ccid IN NUMBER,
543 x_rev_account_code IN VARCHAR2,
544 x_cash_account_code IN VARCHAR2,
545 x_closed_ind IN VARCHAR2,
546 x_system_default IN VARCHAR2,
547 x_enroll_deposit_amount IN NUMBER,
548 x_enroll_deposit_level IN VARCHAR2,
549 x_use_in_appl_self_srvc IN VARCHAR2,
550 x_crt_rev_instr IN VARCHAR2,
551 x_submit_instr IN VARCHAR2,
552 x_submit_err_instr IN VARCHAR2
553 ) AS
554 /*
555 || Created By : [email protected]
556 || Created On : 10-DEC-2001
557 || Purpose : Handles the LOCK mechanism for the table.
558 || Known limitations, enhancements or remarks :
559 || Change History :
560 || Who When What
561 || rghosh 04-oct-2002 added the PROCEDURE GET_FK_IGS_AD_PRCS_CAT
562 || and added the column system default for
563 || Bug # 2599457
564 || rghosh 17-oct-2002 added the columns enroll_deposit_amount and
565 || enroll_deposit_level columns for Bug
566 || #2602077
567 || (reverse chronological order - newest change first)
568 */
569 CURSOR c1 IS
570 SELECT
571 description,
572 admission_cat,
573 s_admission_process_type,
574 configurability_func_name,
575 application_fee_amount,
576 gl_rev_acct_ccid,
577 gl_cash_acct_ccid,
578 rev_account_code,
579 cash_account_code,
580 closed_ind,
581 system_default,
582 enroll_deposit_amount,
583 enroll_deposit_level,
584 use_in_appl_self_srvc,
585 crt_rev_instr,
586 submit_instr,
587 submit_err_instr
588 FROM igs_ad_ss_appl_typ
589 WHERE rowid = x_rowid
590 FOR UPDATE NOWAIT;
591
592 tlinfo c1%ROWTYPE;
593
594 BEGIN
595
596 OPEN c1;
597 FETCH c1 INTO tlinfo;
598 IF (c1%notfound) THEN
599 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
600 igs_ge_msg_stack.add;
601 CLOSE c1;
602 app_exception.raise_exception;
603 RETURN;
604 END IF;
605 CLOSE c1;
606
607 IF (
608 ((tlinfo.description = x_description) OR ((tlinfo.description IS NULL) AND (X_description IS NULL)))
609 AND (tlinfo.admission_cat = x_admission_cat)
610 AND (tlinfo.s_admission_process_type = x_s_admission_process_type)
611 AND ((tlinfo.configurability_func_name = x_configurability_func_name) OR ((tlinfo.configurability_func_name IS NULL) AND (X_configurability_func_name IS NULL)))
615 AND ((tlinfo.rev_account_code = x_rev_account_code) OR ((tlinfo.rev_account_code IS NULL) AND (X_rev_account_code IS NULL)))
612 AND ((tlinfo.application_fee_amount = x_application_fee_amount) OR ((tlinfo.application_fee_amount IS NULL) AND (X_application_fee_amount IS NULL)))
613 AND ((tlinfo.gl_rev_acct_ccid = x_gl_rev_acct_ccid) OR ((tlinfo.gl_rev_acct_ccid IS NULL) AND (X_gl_rev_acct_ccid IS NULL)))
614 AND ((tlinfo.gl_cash_acct_ccid = x_gl_cash_acct_ccid) OR ((tlinfo.gl_cash_acct_ccid IS NULL) AND (X_gl_cash_acct_ccid IS NULL)))
616 AND ((tlinfo.cash_account_code = x_cash_account_code) OR ((tlinfo.cash_account_code IS NULL) AND (X_cash_account_code IS NULL)))
617 AND ((tlinfo.closed_ind = x_closed_ind) OR ((tlinfo.closed_ind IS NULL) AND (X_closed_ind IS NULL)))
618 AND ((tlinfo.system_default = x_system_default) OR ((tlinfo.system_default IS NULL) AND (X_system_default IS NULL)))
619 AND ((tlinfo.enroll_deposit_amount = x_enroll_deposit_amount) OR ((tlinfo.enroll_deposit_amount IS NULL) AND (X_enroll_deposit_amount IS NULL)))
620 AND ((tlinfo.enroll_deposit_level = x_enroll_deposit_level) OR ((tlinfo.enroll_deposit_level IS NULL) AND (X_enroll_deposit_level IS NULL)))
621 AND ((tlinfo.use_in_appl_self_srvc = x_use_in_appl_self_srvc) OR ((tlinfo.use_in_appl_self_srvc IS NULL) AND (X_use_in_appl_self_srvc IS NULL)))
622 AND ((tlinfo.crt_rev_instr = x_crt_rev_instr) OR ((tlinfo.crt_rev_instr IS NULL) AND (x_crt_rev_instr IS NULL)))
623 AND ((tlinfo.submit_instr = x_submit_instr) OR ((tlinfo.submit_instr IS NULL) AND (x_submit_instr IS NULL)))
624 AND ((tlinfo.submit_err_instr = x_submit_err_instr) OR ((tlinfo.submit_err_instr IS NULL) AND (x_submit_err_instr IS NULL)))
625 ) THEN
626 NULL;
627 ELSE
628 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
629 igs_ge_msg_stack.add;
630 app_exception.raise_exception;
631 END IF;
632
633 RETURN;
634
635 END lock_row;
636
637
638 PROCEDURE update_row (
639 x_rowid IN VARCHAR2,
640 x_admission_application_type IN VARCHAR2,
641 x_description IN VARCHAR2,
642 x_admission_cat IN VARCHAR2,
643 x_s_admission_process_type IN VARCHAR2,
644 x_configurability_func_name IN VARCHAR2,
645 x_application_fee_amount IN NUMBER,
646 x_gl_rev_acct_ccid IN NUMBER,
647 x_gl_cash_acct_ccid IN NUMBER,
648 x_rev_account_code IN VARCHAR2,
649 x_cash_account_code IN VARCHAR2,
650 x_closed_ind IN VARCHAR2,
651 x_mode IN VARCHAR2,
652 x_system_default IN VARCHAR2,
653 x_enroll_deposit_amount IN NUMBER,
654 x_enroll_deposit_level IN VARCHAR2,
655 x_use_in_appl_self_srvc IN VARCHAR2,
656 x_crt_rev_instr IN VARCHAR2,
657 x_submit_instr IN VARCHAR2,
658 x_submit_err_instr IN VARCHAR2
659 ) AS
660 /*
661 || Created By : [email protected]
662 || Created On : 10-DEC-2001
663 || Purpose : Handles the UPDATE DML logic for the table.
664 || Known limitations, enhancements or remarks :
665 || Change History :
666 || Who When What
667 || rghosh 04-oct-2002 added the PROCEDURE GET_FK_IGS_AD_PRCS_CAT
668 || and added the column system default for
669 || Bug # 2599457
670 || rghosh 17-oct-2002 added the columns enroll_deposit_amount and
671 || enroll_deposit_level columns for Bug
672 || #2602077
673 || (reverse chronological order - newest change first)
674 */
675 x_last_update_date DATE ;
676 x_last_updated_by NUMBER;
677 x_last_update_login NUMBER;
678
679 BEGIN
680
681 x_last_update_date := SYSDATE;
682 IF (X_MODE = 'I') THEN
683 x_last_updated_by := 1;
684 x_last_update_login := 0;
685 ELSIF (x_mode = 'R') THEN
686 x_last_updated_by := fnd_global.user_id;
687 IF x_last_updated_by IS NULL THEN
688 x_last_updated_by := -1;
689 END IF;
690 x_last_update_login := fnd_global.login_id;
691 IF (x_last_update_login IS NULL) THEN
692 x_last_update_login := -1;
693 END IF;
694 ELSE
695 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
696 igs_ge_msg_stack.add;
697 app_exception.raise_exception;
698 END IF;
699
700 before_dml(
701 p_action => 'UPDATE',
702 x_rowid => x_rowid,
703 x_admission_application_type => x_admission_application_type,
704 x_description => x_description,
705 x_admission_cat => x_admission_cat,
706 x_s_admission_process_type => x_s_admission_process_type,
707 x_configurability_func_name => x_configurability_func_name,
708 x_application_fee_amount => x_application_fee_amount,
709 x_gl_rev_acct_ccid => x_gl_rev_acct_ccid,
710 x_gl_cash_acct_ccid => x_gl_cash_acct_ccid,
711 x_rev_account_code => x_rev_account_code,
712 x_cash_account_code => x_cash_account_code,
713 x_closed_ind => x_closed_ind,
714 x_creation_date => x_last_update_date,
715 x_created_by => x_last_updated_by,
716 x_last_update_date => x_last_update_date,
717 x_last_updated_by => x_last_updated_by,
718 x_last_update_login => x_last_update_login,
719 x_system_default => x_system_default,
720 x_enroll_deposit_amount => x_enroll_deposit_amount,
721 x_enroll_deposit_level => x_enroll_deposit_level,
722 x_use_in_appl_self_srvc => x_use_in_appl_self_srvc,
723 x_crt_rev_instr => x_crt_rev_instr,
724 x_submit_instr => x_submit_instr,
725 x_submit_err_instr => x_submit_err_instr
726 );
727
728 UPDATE igs_ad_ss_appl_typ
729 SET
730 description = new_references.description,
731 admission_cat = new_references.admission_cat,
732 s_admission_process_type = new_references.s_admission_process_type,
733 configurability_func_name = new_references.configurability_func_name,
734 application_fee_amount = new_references.application_fee_amount,
735 gl_rev_acct_ccid = new_references.gl_rev_acct_ccid,
736 gl_cash_acct_ccid = new_references.gl_cash_acct_ccid,
737 rev_account_code = new_references.rev_account_code,
738 cash_account_code = new_references.cash_account_code,
739 closed_ind = new_references.closed_ind,
740 last_update_date = x_last_update_date,
741 last_updated_by = x_last_updated_by,
742 last_update_login = x_last_update_login,
743 system_default = new_references.system_default,
744 enroll_deposit_amount = new_references.enroll_deposit_amount,
745 enroll_deposit_level = new_references.enroll_deposit_level ,
746 use_in_appl_self_srvc = new_references.use_in_appl_self_srvc,
747 crt_rev_instr = new_references.crt_rev_instr,
748 submit_instr = new_references.submit_instr,
749 submit_err_instr = new_references.submit_err_instr
750 WHERE rowid = x_rowid;
751
752 IF (SQL%NOTFOUND) THEN
753 RAISE NO_DATA_FOUND;
754 END IF;
755
756 END update_row;
757
758
759 PROCEDURE add_row (
760 x_rowid IN OUT NOCOPY VARCHAR2,
761 x_admission_application_type IN VARCHAR2,
762 x_description IN VARCHAR2,
763 x_admission_cat IN VARCHAR2,
764 x_s_admission_process_type IN VARCHAR2,
765 x_configurability_func_name IN VARCHAR2,
766 x_application_fee_amount IN NUMBER,
767 x_gl_rev_acct_ccid IN NUMBER,
768 x_gl_cash_acct_ccid IN NUMBER,
769 x_rev_account_code IN VARCHAR2,
770 x_cash_account_code IN VARCHAR2,
771 x_closed_ind IN VARCHAR2,
772 x_mode IN VARCHAR2,
773 x_system_default IN VARCHAR2,
774 x_enroll_deposit_amount IN NUMBER,
775 x_enroll_deposit_level IN VARCHAR2,
776 x_use_in_appl_self_srvc IN VARCHAR2,
777 x_crt_rev_instr IN VARCHAR2,
778 x_submit_instr IN VARCHAR2,
779 x_submit_err_instr IN VARCHAR2
780 ) AS
781 /*
782 || Created By : [email protected]
783 || Created On : 10-DEC-2001
784 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
785 || Known limitations, enhancements or remarks :
786 || Change History :
787 || Who When What
788 || rghosh 04-oct-2002 added the PROCEDURE GET_FK_IGS_AD_PRCS_CAT
789 || and added the column system default for
790 || Bug # 2599457
791 || rghosh 17-oct-2002 added the columns enroll_deposit_amount and
792 || enroll_deposit_level columns for Bug
793 || #2602077
794 || (reverse chronological order - newest change first)
795 */
796 CURSOR c1 IS
797 SELECT rowid
798 FROM igs_ad_ss_appl_typ
799 WHERE admission_application_type = x_admission_application_type;
800
801 BEGIN
802
803 OPEN c1;
804 FETCH c1 INTO x_rowid;
805 IF (c1%NOTFOUND) THEN
806 CLOSE c1;
807
808 insert_row (
809 x_rowid,
810 x_admission_application_type,
811 x_description,
812 x_admission_cat,
813 x_s_admission_process_type,
814 x_configurability_func_name,
815 x_application_fee_amount,
816 x_gl_rev_acct_ccid,
817 x_gl_cash_acct_ccid,
818 x_rev_account_code,
819 x_cash_account_code,
820 x_closed_ind,
821 x_mode,
822 x_system_default,
823 x_enroll_deposit_amount,
824 x_enroll_deposit_level,
825 x_use_in_appl_self_srvc,
826 x_crt_rev_instr,
827 x_submit_instr,
828 x_submit_err_instr
829 );
830 RETURN;
831 END IF;
832 CLOSE c1;
833
834 update_row (
835 x_rowid,
836 x_admission_application_type,
837 x_description,
838 x_admission_cat,
839 x_s_admission_process_type,
840 x_configurability_func_name,
841 x_application_fee_amount,
842 x_gl_rev_acct_ccid,
843 x_gl_cash_acct_ccid,
844 x_rev_account_code,
845 x_cash_account_code,
846 x_closed_ind,
847 x_mode,
848 x_system_default,
849 x_enroll_deposit_amount,
850 x_enroll_deposit_level,
851 x_use_in_appl_self_srvc,
852 x_crt_rev_instr,
853 x_submit_instr,
854 x_submit_err_instr
855 );
856
857 END add_row;
858
859
860 PROCEDURE delete_row (
861 x_rowid IN VARCHAR2
862 ) AS
863 /*
864 || Created By : [email protected]
865 || Created On : 10-DEC-2001
866 || Purpose : Handles the DELETE DML logic for the table.
867 || Known limitations, enhancements or remarks :
868 || Change History :
869 || Who When What
870 || (reverse chronological order - newest change first)
871 */
872
873 CURSOR c_del_at_ss_pgs (v_rowid VARCHAR2) IS
874 SELECT admission_application_type
875 FROM igs_ad_ss_appl_typ
876 WHERE rowid=v_rowid;
877
878 CURSOR c_del_sspgs(v_atyp VARCHAR2) IS
879 SELECT ROWID
880 FROM igs_ad_ss_appl_pgs
881 WHERE admission_application_type=v_atyp;
882
883 rec_c_del_sspgs c_del_sspgs%ROWTYPE;
884
885 v_aat igs_ad_ss_appl_typ.admission_application_type%TYPE;
886 BEGIN
887
888 before_dml (
889 p_action => 'DELETE',
890 x_rowid => x_rowid
891 );
892
893 OPEN c_del_at_ss_pgs(x_rowid);
894 FETCH c_del_at_ss_pgs INTO v_aat;
895 CLOSE c_del_at_ss_pgs;
896
897 FOR rec_c_del_sspgs IN c_del_sspgs(v_aat) LOOP
898 igs_ad_ss_appl_pgs_pkg.delete_row(x_rowid => rec_c_del_sspgs.rowid);
899 END LOOP;
900
901
902 DELETE FROM igs_ad_ss_appl_typ
903 WHERE rowid = x_rowid;
904
905 IF (SQL%NOTFOUND) THEN
906 RAISE NO_DATA_FOUND;
907 END IF;
908
909 END delete_row;
910
911 -- Called from Application Types form (IGSAD085)
912 -- Do not call from TBH
913 PROCEDURE check_child_existance_apc (
914 p_admission_application_type IN VARCHAR2,
915 p_message_name OUT NOCOPY VARCHAR2) is
916 BEGIN
917 old_references.admission_application_type := p_admission_application_type;
918 BEGIN
919 check_child_existance;
920 EXCEPTION
921 WHEN OTHERS THEN
922 p_message_name := 'IGS_AD_CNT_UPD_APC';
923 END;
924 old_references.admission_application_type := NULL;
925
926 END check_child_existance_apc;
927
928
929 END igs_ad_ss_appl_typ_pkg;