[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_CLCHSN_DTLS_PKG
Source
1 PACKAGE BODY igf_sl_clchsn_dtls_pkg AS
2 /* $Header: IGFLI40B.pls 120.1 2005/09/15 23:34:53 appldev noship $ */
3 /*=======================================================================+
4 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | |
8 | DESCRIPTION |
9 | PL/SQL body for package: IGF_SL_CLCHSN_DTLS_PKG
10 | |
11 | NOTES |
12 | |
13 | This package has a flag on the end of some of the procedures called |
14 | X_MODE. Pass either 'R' for runtime, or 'I' for Install-time. |
15 | This will control how the who columns are filled in; If you are |
16 | running in runtime mode, they are taken from the profiles, whereas in |
17 | install-time mode they get defaulted with special values to indicate |
18 | that they were inserted by datamerge. |
19 | |
20 | The ADD_ROW routine will see whether a row exists by selecting |
21 | based on the primary key, and updates the row if it exists, |
22 | or inserts the row if it doesn't already exist. |
23 | |
24 | This module is called by AutoInstall (afplss.drv) on install and |
25 | upgrade. The WHENEVER SQLERROR and EXIT (at bottom) are required. |
26 | |
27 | HISTORY |
28 *=======================================================================*/
29
30
31 l_rowid VARCHAR2(25);
32 old_references igf_sl_clchsn_dtls%ROWTYPE;
33 new_references igf_sl_clchsn_dtls%ROWTYPE;
34
35 PROCEDURE set_column_values (
36 p_action IN VARCHAR2,
37 x_rowid IN VARCHAR2,
38 x_clchgsnd_id IN NUMBER,
39 x_award_id IN NUMBER,
40 x_loan_number_txt IN VARCHAR2,
41 x_cl_version_code IN VARCHAR2,
42 x_change_field_code IN VARCHAR2,
43 x_change_record_type_txt IN VARCHAR2,
44 x_change_code_txt IN VARCHAR2,
45 x_status_code IN VARCHAR2,
46 x_status_date IN DATE,
47 x_response_status_code IN VARCHAR2,
48 x_old_value_txt IN VARCHAR2,
49 x_new_value_txt IN VARCHAR2,
50 x_old_date IN DATE,
51 x_new_date IN DATE,
52 x_old_amt IN NUMBER,
53 x_new_amt IN NUMBER,
54 x_disbursement_number IN NUMBER,
55 x_disbursement_date IN DATE,
56 x_change_issue_code IN VARCHAR2,
57 x_disbursement_cancel_date IN DATE,
58 x_disbursement_cancel_amt IN NUMBER,
59 x_disbursement_revised_amt IN NUMBER,
60 x_disbursement_revised_date IN DATE,
61 x_disbursement_reissue_code IN VARCHAR2,
62 x_disbursement_reinst_code IN VARCHAR2,
63 x_disbursement_return_amt IN NUMBER,
64 x_disbursement_return_date IN DATE,
65 x_disbursement_return_code IN VARCHAR2,
66 x_post_with_disb_return_amt IN NUMBER,
67 x_post_with_disb_return_date IN DATE,
68 x_post_with_disb_return_code IN VARCHAR2,
69 x_prev_with_disb_return_amt IN NUMBER,
70 x_prev_with_disb_return_date IN DATE,
71 x_school_use_txt IN VARCHAR2,
72 x_lender_use_txt IN VARCHAR2,
73 x_guarantor_use_txt IN VARCHAR2,
74 x_validation_edit_txt IN VARCHAR2,
75 x_send_record_txt IN VARCHAR2,
76 x_creation_date IN DATE,
77 x_created_by IN NUMBER,
78 x_last_update_date IN DATE,
79 x_last_updated_by IN NUMBER,
80 x_last_update_login IN NUMBER
81 ) AS
82 /*
83 || Created By : [email protected]
84 || Created On : 13-OCT-2004
85 || Purpose : Initialises the Old and New references for the columns of the table.
86 || Known limitations, enhancements or remarks :
87 || Change History :
88 || Who When What
89 || (reverse chronological order - newest change first)
90 */
91
92 CURSOR cur_old_ref_values IS
93 SELECT *
94 FROM igf_sl_clchsn_dtls
95 WHERE rowid = x_rowid;
96
97 BEGIN
98
99 l_rowid := x_rowid;
100
101 -- Code for setting the Old and New Reference Values.
102 -- Populate Old Values.
103 OPEN cur_old_ref_values;
104 FETCH cur_old_ref_values INTO old_references;
105 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
106 CLOSE cur_old_ref_values;
107 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED1');
108 igs_ge_msg_stack.add;
109 app_exception.raise_exception;
110 RETURN;
111 END IF;
112 CLOSE cur_old_ref_values;
113
114 -- Populate New Values.
115 new_references.clchgsnd_id := x_clchgsnd_id;
116 new_references.award_id := x_award_id;
117 new_references.loan_number_txt := x_loan_number_txt;
118 new_references.cl_version_code := x_cl_version_code;
119 new_references.change_field_code := x_change_field_code;
120 new_references.change_record_type_txt := x_change_record_type_txt;
121 new_references.change_code_txt := x_change_code_txt;
122 new_references.status_code := x_status_code;
123 new_references.status_date := x_status_date;
124 new_references.response_status_code := x_response_status_code;
125 new_references.old_value_txt := x_old_value_txt;
126 new_references.new_value_txt := x_new_value_txt;
127 new_references.old_date := x_old_date;
128 new_references.new_date := x_new_date;
129 new_references.old_amt := x_old_amt;
130 new_references.new_amt := x_new_amt;
131 new_references.disbursement_number := x_disbursement_number;
132 new_references.disbursement_date := x_disbursement_date;
133 new_references.change_issue_code := x_change_issue_code;
134 new_references.disbursement_cancel_date := x_disbursement_cancel_date;
135 new_references.disbursement_cancel_amt := x_disbursement_cancel_amt;
136 new_references.disbursement_revised_amt := x_disbursement_revised_amt;
137 new_references.disbursement_revised_date := x_disbursement_revised_date;
138 new_references.disbursement_reissue_code := x_disbursement_reissue_code;
139 new_references.disbursement_reinst_code := x_disbursement_reinst_code;
140 new_references.disbursement_return_amt := x_disbursement_return_amt;
141 new_references.disbursement_return_date := x_disbursement_return_date;
142 new_references.disbursement_return_code := x_disbursement_return_code;
143 new_references.post_with_disb_return_amt := x_post_with_disb_return_amt;
144 new_references.post_with_disb_return_date := x_post_with_disb_return_date;
145 new_references.post_with_disb_return_code := x_post_with_disb_return_code;
146 new_references.prev_with_disb_return_amt := x_prev_with_disb_return_amt;
147 new_references.prev_with_disb_return_date := x_prev_with_disb_return_date;
148 new_references.school_use_txt := x_school_use_txt;
149 new_references.lender_use_txt := x_lender_use_txt;
150 new_references.guarantor_use_txt := x_guarantor_use_txt;
151 new_references.validation_edit_txt := x_validation_edit_txt;
152 new_references.send_record_txt := x_send_record_txt;
153
154 IF (p_action = 'UPDATE') THEN
155 new_references.creation_date := old_references.creation_date;
156 new_references.created_by := old_references.created_by;
157 ELSE
158 new_references.creation_date := x_creation_date;
159 new_references.created_by := x_created_by;
160 END IF;
161
162 new_references.last_update_date := x_last_update_date;
163 new_references.last_updated_by := x_last_updated_by;
164 new_references.last_update_login := x_last_update_login;
165
166 END set_column_values;
167
168 PROCEDURE update_loan_change_status(p_loan_number igf_sl_loans.loan_number%TYPE)
169 /*
170 || Created By : bvisvana
171 || Created On : 13-Sep-2005
172 || Purpose : Bug # 4575843 - Updating the loan change status each time the disbursement is INSERTED or UPDATED
173 || If any one is in 'Not Ready' state the loan change status goes as 'Not Ready'. Else 'Ready to Send'
174 || Known limitations, enhancements or remarks :
175 || Change History :
176 || Who When What
177 || (reverse chronological order - newest change first)
178 */
179
180 AS
181 CURSOR cur_loans(cp_loan_number igf_sl_loans.loan_number%TYPE) IS
182 SELECT igf_sl_loans.* FROM igf_sl_loans
183 WHERE NVL(external_loan_id_txt,loan_number) = cp_loan_number ;
184 loan_rec cur_loans%ROWTYPE;
185
186 CURSOR cur_chng_dtls(cp_loan_number igf_sl_loans.loan_number%TYPE) IS
187 SELECT 'N' FROM igf_sl_clchsn_dtls WHERE loan_number_txt = cp_loan_number AND
188 status_code = 'N';
189 chng_dtls_rec cur_chng_dtls%ROWTYPE;
190
191 loan_chng_status VARCHAR2(1);
192
193 BEGIN
194 OPEN cur_chng_dtls(cp_loan_number => p_loan_number);
195 FETCH cur_chng_dtls INTO chng_dtls_rec;
196
197 IF cur_chng_dtls%NOTFOUND THEN
198 loan_chng_status := 'G';
199 ELSE
200 loan_chng_status := 'N';
201 END IF;
202 CLOSE cur_chng_dtls;
203
204 OPEN cur_loans(cp_loan_number => p_loan_number);
205 FETCH cur_loans INTO loan_rec;
206 igf_sl_loans_pkg.update_row (
207 X_Mode => 'R',
208 x_rowid => loan_rec.row_id,
209 x_loan_id => loan_rec.loan_id,
210 x_award_id => loan_rec.award_id,
211 x_seq_num => loan_rec.seq_num,
212 x_loan_number => loan_rec.loan_number,
213 x_loan_per_begin_date => loan_rec.loan_per_begin_date,
214 x_loan_per_end_date => loan_rec.loan_per_end_date,
215 x_loan_status => loan_rec.loan_status,
216 x_loan_status_date => loan_rec.loan_status_date,
217 x_loan_chg_status => loan_chng_status,
218 x_loan_chg_status_date => TRUNC(SYSDATE),
219 x_active => loan_rec.active,
220 x_active_date => loan_rec.active_date,
221 x_borw_detrm_code => loan_rec.borw_detrm_code,
222 x_legacy_record_flag => NULL,
223 x_external_loan_id_txt => loan_rec.external_loan_id_txt
224 );
225 CLOSE cur_loans;
226 END update_loan_change_status;
227
228 PROCEDURE after_dml(p_action VARCHAR2) AS
229 /*
230 || Created By : bvisvana
231 || Created On : 13-Sep-2005
232 || Purpose : Bug # 4575843 - After DML actions
233 || Known limitations, enhancements or remarks :
234 || Change History :
235 || Who When What
236 || (reverse chronological order - newest change first)
237 */
238 BEGIN
239 IF (p_action IN ('INSERTING','UPDATING')) THEN
240 update_loan_change_status(new_references.loan_number_txt);
241 END IF;
242 END after_dml;
243
244 PROCEDURE check_parent_existance AS
245 /*
246 || Created By : [email protected]
247 || Created On : 13-OCT-2004
248 || Purpose : Checks for the existance of Parent records.
249 || Known limitations, enhancements or remarks :
250 || Change History :
251 || Who When What
252 || (reverse chronological order - newest change first)
253 */
254 BEGIN
255 IF ((old_references.loan_number_txt = new_references.loan_number_txt))
256 OR
257 ((new_references.loan_number_txt IS NULL)) THEN
258 NULL;
259 ELSIF NOT igf_sl_loans_pkg.get_uk_for_validation (
260 x_loan_number => new_references.loan_number_txt
261 ) THEN
262 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED2');
263 igs_ge_msg_stack.add;
264 app_exception.raise_exception;
265 END IF;
266
267 END check_parent_existance;
268
269 FUNCTION get_pk_for_validation (
270 x_clchgsnd_id IN NUMBER
271 ) RETURN BOOLEAN AS
272 /*
273 || Created By : [email protected]
274 || Created On : 13-OCT-2004
275 || Purpose : Validates the Primary Key of the table.
276 || Known limitations, enhancements or remarks :
277 || Change History :
278 || Who When What
279 || (reverse chronological order - newest change first)
280 */
281 CURSOR cur_rowid IS
282 SELECT rowid
283 FROM igf_sl_clchsn_dtls
284 WHERE clchgsnd_id = x_clchgsnd_id
285 FOR UPDATE NOWAIT;
286
287 lv_rowid cur_rowid%RowType;
288
289 BEGIN
290
291 OPEN cur_rowid;
292 FETCH cur_rowid INTO lv_rowid;
293 IF (cur_rowid%FOUND) THEN
294 CLOSE cur_rowid;
295 RETURN(TRUE);
296 ELSE
297 CLOSE cur_rowid;
298 RETURN(FALSE);
299 END IF;
300
301 END get_pk_for_validation;
302
303 PROCEDURE get_ufk_igf_sl_loans (
304 x_loan_number IN VARCHAR2
305 ) AS
306 /*
307 || Created By : [email protected]
308 || Created On : 13-OCT-2004
309 || Purpose : Validates the Foreign Keys for the table.
310 || Known limitations, enhancements or remarks :
311 || Change History :
312 || Who When What
313 || (reverse chronological order - newest change first)
314 */
315
316 CURSOR cur_rowid IS
317 SELECT rowid
318 FROM igf_sl_clchsn_dtls
319 WHERE ((loan_number_txt = x_loan_number ));
320
321 lv_rowid cur_rowid%ROWTYPE;
322 BEGIN
323
324 OPEN cur_rowid;
325 FETCH cur_rowid INTO lv_rowid;
326 IF (cur_rowid%FOUND) THEN
327 CLOSE cur_rowid;
328 fnd_message.set_name ('IGF', 'IGF_SL_CLCHGSND_LAR_UK_FK');
329 igs_ge_msg_stack.add;
330 app_exception.raise_exception;
331 RETURN;
332 END IF;
333 CLOSE cur_rowid;
334
335 END get_ufk_igf_sl_loans;
336
337 PROCEDURE before_dml (
338 p_action IN VARCHAR2,
339 x_rowid IN VARCHAR2,
340 x_clchgsnd_id IN NUMBER,
341 x_award_id IN NUMBER,
342 x_loan_number_txt IN VARCHAR2,
343 x_cl_version_code IN VARCHAR2,
344 x_change_field_code IN VARCHAR2,
345 x_change_record_type_txt IN VARCHAR2,
346 x_change_code_txt IN VARCHAR2,
347 x_status_code IN VARCHAR2,
348 x_status_date IN DATE,
349 x_response_status_code IN VARCHAR2,
350 x_old_value_txt IN VARCHAR2,
351 x_new_value_txt IN VARCHAR2,
352 x_old_date IN DATE,
353 x_new_date IN DATE,
354 x_old_amt IN NUMBER,
355 x_new_amt IN NUMBER,
356 x_disbursement_number IN NUMBER,
357 x_disbursement_date IN DATE,
358 x_change_issue_code IN VARCHAR2,
359 x_disbursement_cancel_date IN DATE,
360 x_disbursement_cancel_amt IN NUMBER,
361 x_disbursement_revised_amt IN NUMBER,
362 x_disbursement_revised_date IN DATE,
363 x_disbursement_reissue_code IN VARCHAR2,
364 x_disbursement_reinst_code IN VARCHAR2,
365 x_disbursement_return_amt IN NUMBER,
366 x_disbursement_return_date IN DATE,
370 x_post_with_disb_return_code IN VARCHAR2,
367 x_disbursement_return_code IN VARCHAR2,
368 x_post_with_disb_return_amt IN NUMBER,
369 x_post_with_disb_return_date IN DATE,
371 x_prev_with_disb_return_amt IN NUMBER,
372 x_prev_with_disb_return_date IN DATE,
373 x_school_use_txt IN VARCHAR2,
374 x_lender_use_txt IN VARCHAR2,
375 x_guarantor_use_txt IN VARCHAR2,
376 x_validation_edit_txt IN VARCHAR2,
377 x_send_record_txt IN VARCHAR2,
378 x_creation_date IN DATE,
379 x_created_by IN NUMBER,
380 x_last_update_date IN DATE,
381 x_last_updated_by IN NUMBER,
382 x_last_update_login IN NUMBER
383 ) AS
384 /*
385 || Created By : [email protected]
386 || Created On : 13-OCT-2004
387 || Purpose : Initialises the columns, Checks Constraints, Calls the
388 || Trigger Handlers for the table, before any DML operation.
389 || Known limitations, enhancements or remarks :
390 || Change History :
391 || Who When What
392 || (reverse chronological order - newest change first)
393 */
394 BEGIN
395
396 set_column_values (
397 p_action,
398 x_rowid,
399 x_clchgsnd_id,
400 x_award_id,
401 x_loan_number_txt,
402 x_cl_version_code,
403 x_change_field_code,
404 x_change_record_type_txt,
405 x_change_code_txt,
406 x_status_code,
407 x_status_date,
408 x_response_status_code,
409 x_old_value_txt,
410 x_new_value_txt,
411 x_old_date,
412 x_new_date,
413 x_old_amt,
414 x_new_amt,
415 x_disbursement_number,
416 x_disbursement_date,
417 x_change_issue_code,
418 x_disbursement_cancel_date,
419 x_disbursement_cancel_amt,
420 x_disbursement_revised_amt,
421 x_disbursement_revised_date,
422 x_disbursement_reissue_code,
423 x_disbursement_reinst_code,
424 x_disbursement_return_amt,
425 x_disbursement_return_date,
426 x_disbursement_return_code,
427 x_post_with_disb_return_amt,
428 x_post_with_disb_return_date,
429 x_post_with_disb_return_code,
430 x_prev_with_disb_return_amt,
431 x_prev_with_disb_return_date,
432 x_school_use_txt,
433 x_lender_use_txt,
434 x_guarantor_use_txt,
435 x_validation_edit_txt,
436 x_send_record_txt,
437 x_creation_date,
438 x_created_by,
439 x_last_update_date,
440 x_last_updated_by,
441 x_last_update_login
442 );
443
444 IF (p_action = 'INSERT') THEN
445 -- Call all the procedures related to Before Insert.
446 IF ( get_pk_for_validation(
447 new_references.clchgsnd_id
448 )
449 ) THEN
450 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
451 igs_ge_msg_stack.add;
452 app_exception.raise_exception;
453 END IF;
454 check_parent_existance;
455 ELSIF (p_action = 'UPDATE') THEN
456 -- Call all the procedures related to Before Update.
457
458 Check_Parent_Existance;
459 ELSIF (p_action = 'VALIDATE_INSERT') THEN
460 -- Call all the procedures related to Before Insert.
461 IF ( get_pk_for_validation(
462 new_references.clchgsnd_id
463 )
464 ) THEN
465 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
466 igs_ge_msg_stack.add;
467 app_exception.raise_exception;
468 END IF;
469 END IF;
470 l_rowid := NULL;
471 END before_dml;
472
473
474 PROCEDURE insert_row (
475 x_rowid IN OUT NOCOPY VARCHAR2,
476 x_clchgsnd_id IN OUT NOCOPY NUMBER,
477 x_award_id IN NUMBER,
478 x_loan_number_txt IN VARCHAR2,
479 x_cl_version_code IN VARCHAR2,
480 x_change_field_code IN VARCHAR2,
481 x_change_record_type_txt IN VARCHAR2,
482 x_change_code_txt IN VARCHAR2,
483 x_status_code IN VARCHAR2,
484 x_status_date IN DATE,
485 x_response_status_code IN VARCHAR2,
486 x_old_value_txt IN VARCHAR2,
487 x_new_value_txt IN VARCHAR2,
488 x_old_date IN DATE,
489 x_new_date IN DATE,
490 x_old_amt IN NUMBER,
491 x_new_amt IN NUMBER,
492 x_disbursement_number IN NUMBER,
493 x_disbursement_date IN DATE,
494 x_change_issue_code IN VARCHAR2,
495 x_disbursement_cancel_date IN DATE,
496 x_disbursement_cancel_amt IN NUMBER,
497 x_disbursement_revised_amt IN NUMBER,
498 x_disbursement_revised_date IN DATE,
499 x_disbursement_reissue_code IN VARCHAR2,
500 x_disbursement_reinst_code IN VARCHAR2,
501 x_disbursement_return_amt IN NUMBER,
502 x_disbursement_return_date IN DATE,
503 x_disbursement_return_code IN VARCHAR2,
504 x_post_with_disb_return_amt IN NUMBER,
508 x_prev_with_disb_return_date IN DATE,
505 x_post_with_disb_return_date IN DATE,
506 x_post_with_disb_return_code IN VARCHAR2,
507 x_prev_with_disb_return_amt IN NUMBER,
509 x_school_use_txt IN VARCHAR2,
510 x_lender_use_txt IN VARCHAR2,
511 x_guarantor_use_txt IN VARCHAR2,
512 x_validation_edit_txt IN VARCHAR2,
513 x_send_record_txt IN VARCHAR2,
514 x_mode IN VARCHAR2
515 ) AS
516 /*
517 || Created By : [email protected]
518 || Created On : 13-OCT-2004
519 || Purpose : Handles the INSERT DML logic for the table.
520 || Known limitations, enhancements or remarks :
521 || Change History :
522 || Who When What
523 || (reverse chronological order - newest change first)
524 */
525
526 x_last_update_date DATE;
527 x_last_updated_by NUMBER;
528 x_last_update_login NUMBER;
529 x_request_id NUMBER;
530 x_program_id NUMBER;
531 x_program_application_id NUMBER;
532 x_program_update_date DATE;
533
534 BEGIN
535
536 x_last_update_date := SYSDATE;
537 IF (x_mode = 'I') THEN
538 x_last_updated_by := 1;
539 x_last_update_login := 0;
540 ELSIF (x_mode = 'R') THEN
541 x_last_updated_by := fnd_global.user_id;
542 IF (x_last_updated_by IS NULL) THEN
543 x_last_updated_by := -1;
544 END IF;
545 x_last_update_login := fnd_global.login_id;
546 IF (x_last_update_login IS NULL) THEN
547 x_last_update_login := -1;
548 END IF;
549 x_request_id := fnd_global.conc_request_id;
550 x_program_id := fnd_global.conc_program_id;
551 x_program_application_id := fnd_global.prog_appl_id;
552
553 IF (x_request_id = -1) THEN
554 x_request_id := NULL;
555 x_program_id := NULL;
556 x_program_application_id := NULL;
557 x_program_update_date := NULL;
558 ELSE
559 x_program_update_date := SYSDATE;
560 END IF;
561 ELSE
562 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
563 fnd_message.set_token ('ROUTINE', 'IGF_SL_CLCHSN_DTLS_PKG.INSERT_ROW');
564 igs_ge_msg_stack.add;
565 app_exception.raise_exception;
566 END IF;
567
568 x_clchgsnd_id := NULL;
569
570 before_dml(
571 p_action => 'INSERT',
572 x_rowid => x_rowid,
573 x_clchgsnd_id => x_clchgsnd_id,
574 x_award_id => x_award_id,
575 x_loan_number_txt => x_loan_number_txt,
576 x_cl_version_code => x_cl_version_code,
577 x_change_field_code => x_change_field_code,
578 x_change_record_type_txt => x_change_record_type_txt,
579 x_change_code_txt => x_change_code_txt,
580 x_status_code => x_status_code,
581 x_status_date => x_status_date,
582 x_response_status_code => x_response_status_code,
583 x_old_value_txt => x_old_value_txt,
584 x_new_value_txt => x_new_value_txt,
585 x_old_date => x_old_date,
586 x_new_date => x_new_date,
587 x_old_amt => x_old_amt,
588 x_new_amt => x_new_amt,
589 x_disbursement_number => x_disbursement_number,
590 x_disbursement_date => x_disbursement_date,
591 x_change_issue_code => x_change_issue_code,
592 x_disbursement_cancel_date => x_disbursement_cancel_date,
593 x_disbursement_cancel_amt => x_disbursement_cancel_amt,
594 x_disbursement_revised_amt => x_disbursement_revised_amt,
595 x_disbursement_revised_date => x_disbursement_revised_date,
596 x_disbursement_reissue_code => x_disbursement_reissue_code,
597 x_disbursement_reinst_code => x_disbursement_reinst_code,
598 x_disbursement_return_amt => x_disbursement_return_amt,
599 x_disbursement_return_date => x_disbursement_return_date,
600 x_disbursement_return_code => x_disbursement_return_code,
601 x_post_with_disb_return_amt => x_post_with_disb_return_amt,
602 x_post_with_disb_return_date => x_post_with_disb_return_date,
603 x_post_with_disb_return_code => x_post_with_disb_return_code,
604 x_prev_with_disb_return_amt => x_prev_with_disb_return_amt,
605 x_prev_with_disb_return_date => x_prev_with_disb_return_date,
606 x_school_use_txt => x_school_use_txt,
607 x_lender_use_txt => x_lender_use_txt,
608 x_guarantor_use_txt => x_guarantor_use_txt,
609 x_validation_edit_txt => x_validation_edit_txt,
610 x_send_record_txt => x_send_record_txt,
611 x_creation_date => x_last_update_date,
612 x_created_by => x_last_updated_by,
613 x_last_update_date => x_last_update_date,
614 x_last_updated_by => x_last_updated_by,
615 x_last_update_login => x_last_update_login
616 );
617
618 INSERT INTO igf_sl_clchsn_dtls (
619 clchgsnd_id,
623 change_field_code,
620 award_id,
621 loan_number_txt,
622 cl_version_code,
624 change_record_type_txt,
625 change_code_txt,
626 status_code,
627 status_date,
628 response_status_code,
629 old_value_txt,
630 new_value_txt,
631 old_date,
632 new_date,
633 old_amt,
634 new_amt,
635 disbursement_number,
636 disbursement_date,
637 change_issue_code,
638 disbursement_cancel_date,
639 disbursement_cancel_amt,
640 disbursement_revised_amt,
641 disbursement_revised_date,
642 disbursement_reissue_code,
643 disbursement_reinst_code,
644 disbursement_return_amt,
645 disbursement_return_date,
646 disbursement_return_code,
647 post_with_disb_return_amt,
648 post_with_disb_return_date,
649 post_with_disb_return_code,
650 prev_with_disb_return_amt,
651 prev_with_disb_return_date,
652 school_use_txt,
653 lender_use_txt,
654 guarantor_use_txt,
655 validation_edit_txt,
656 send_record_txt,
657 creation_date,
658 created_by,
659 last_update_date,
660 last_updated_by,
661 last_update_login,
662 request_id,
663 program_id,
664 program_application_id,
665 program_update_date
666 ) VALUES (
667 igf_sl_clchsn_dtls_s.NEXTVAL,
668 new_references.award_id,
669 new_references.loan_number_txt,
670 new_references.cl_version_code,
671 new_references.change_field_code,
672 new_references.change_record_type_txt,
673 new_references.change_code_txt,
674 new_references.status_code,
675 new_references.status_date,
676 new_references.response_status_code,
677 new_references.old_value_txt,
678 new_references.new_value_txt,
679 new_references.old_date,
680 new_references.new_date,
681 new_references.old_amt,
682 new_references.new_amt,
683 new_references.disbursement_number,
684 new_references.disbursement_date,
685 new_references.change_issue_code,
686 new_references.disbursement_cancel_date,
687 new_references.disbursement_cancel_amt,
688 new_references.disbursement_revised_amt,
689 new_references.disbursement_revised_date,
690 new_references.disbursement_reissue_code,
691 new_references.disbursement_reinst_code,
692 new_references.disbursement_return_amt,
693 new_references.disbursement_return_date,
694 new_references.disbursement_return_code,
695 new_references.post_with_disb_return_amt,
696 new_references.post_with_disb_return_date,
697 new_references.post_with_disb_return_code,
698 new_references.prev_with_disb_return_amt,
699 new_references.prev_with_disb_return_date,
700 new_references.school_use_txt,
701 new_references.lender_use_txt,
702 new_references.guarantor_use_txt,
703 new_references.validation_edit_txt,
704 new_references.send_record_txt,
705 x_last_update_date,
706 x_last_updated_by,
707 x_last_update_date,
708 x_last_updated_by,
709 x_last_update_login ,
710 x_request_id,
711 x_program_id,
712 x_program_application_id,
713 x_program_update_date
714 ) RETURNING ROWID, clchgsnd_id INTO x_rowid, x_clchgsnd_id;
715
716 after_dml(p_action => 'INSERTING');
717
718 END insert_row;
719
720
721 PROCEDURE lock_row (
722 x_rowid IN VARCHAR2,
723 x_clchgsnd_id IN NUMBER,
724 x_award_id IN NUMBER,
725 x_loan_number_txt IN VARCHAR2,
726 x_cl_version_code IN VARCHAR2,
727 x_change_field_code IN VARCHAR2,
728 x_change_record_type_txt IN VARCHAR2,
729 x_change_code_txt IN VARCHAR2,
730 x_status_code IN VARCHAR2,
731 x_status_date IN DATE,
732 x_response_status_code IN VARCHAR2,
733 x_old_value_txt IN VARCHAR2,
734 x_new_value_txt IN VARCHAR2,
735 x_old_date IN DATE,
736 x_new_date IN DATE,
737 x_old_amt IN NUMBER,
741 x_change_issue_code IN VARCHAR2,
738 x_new_amt IN NUMBER,
739 x_disbursement_number IN NUMBER,
740 x_disbursement_date IN DATE,
742 x_disbursement_cancel_date IN DATE,
743 x_disbursement_cancel_amt IN NUMBER,
744 x_disbursement_revised_amt IN NUMBER,
745 x_disbursement_revised_date IN DATE,
746 x_disbursement_reissue_code IN VARCHAR2,
747 x_disbursement_reinst_code IN VARCHAR2,
748 x_disbursement_return_amt IN NUMBER,
749 x_disbursement_return_date IN DATE,
750 x_disbursement_return_code IN VARCHAR2,
751 x_post_with_disb_return_amt IN NUMBER,
752 x_post_with_disb_return_date IN DATE,
753 x_post_with_disb_return_code IN VARCHAR2,
754 x_prev_with_disb_return_amt IN NUMBER,
755 x_prev_with_disb_return_date IN DATE,
756 x_school_use_txt IN VARCHAR2,
757 x_lender_use_txt IN VARCHAR2,
758 x_guarantor_use_txt IN VARCHAR2,
759 x_validation_edit_txt IN VARCHAR2,
760 x_send_record_txt IN VARCHAR2
761 ) AS
762 /*
763 || Created By : [email protected]
764 || Created On : 13-OCT-2004
765 || Purpose : Handles the LOCK mechanism for the table.
766 || Known limitations, enhancements or remarks :
767 || Change History :
768 || Who When What
769 || (reverse chronological order - newest change first)
770 */
771 CURSOR c1 IS
772 SELECT
773 clchgsnd_id,
774 award_id,
775 loan_number_txt,
776 cl_version_code,
777 change_field_code,
778 change_record_type_txt,
779 change_code_txt,
780 status_code,
781 status_date,
782 response_status_code,
783 old_value_txt,
784 new_value_txt,
785 old_date,
786 new_date,
787 old_amt,
788 new_amt,
789 disbursement_number,
790 disbursement_date,
791 change_issue_code,
792 disbursement_cancel_date,
793 disbursement_cancel_amt,
794 disbursement_revised_amt,
795 disbursement_revised_date,
796 disbursement_reissue_code,
797 disbursement_reinst_code,
798 disbursement_return_amt,
799 disbursement_return_date,
800 disbursement_return_code,
801 post_with_disb_return_amt,
802 post_with_disb_return_date,
803 post_with_disb_return_code,
804 prev_with_disb_return_amt,
805 prev_with_disb_return_date,
806 school_use_txt,
807 lender_use_txt,
808 guarantor_use_txt,
809 validation_edit_txt,
810 send_record_txt
811 FROM igf_sl_clchsn_dtls
812 WHERE rowid = x_rowid
813 FOR UPDATE NOWAIT;
814
815 tlinfo c1%ROWTYPE;
816
817 BEGIN
818
819 OPEN c1;
820 FETCH c1 INTO tlinfo;
821 IF (c1%notfound) THEN
822 fnd_message.set_name('FND', 'FORM_RECORD_DELETED3');
823 igs_ge_msg_stack.add;
824 CLOSE c1;
825 app_exception.raise_exception;
826 RETURN;
827 END IF;
828 CLOSE c1;
829
830 IF (
831 (tlinfo.clchgsnd_id = x_clchgsnd_id)
832 AND (tlinfo.award_id = x_award_id)
833 AND (tlinfo.loan_number_txt = x_loan_number_txt)
834 AND (tlinfo.cl_version_code = x_cl_version_code)
835 AND (tlinfo.change_field_code = x_change_field_code)
836 AND (tlinfo.change_record_type_txt = x_change_record_type_txt)
837 AND (tlinfo.change_code_txt = x_change_code_txt)
838 AND (tlinfo.status_code = x_status_code)
839 AND (tlinfo.status_date = x_status_date)
840 AND ((tlinfo.response_status_code = x_response_status_code) OR ((tlinfo.response_status_code IS NULL) AND (X_response_status_code IS NULL)))
841 AND ((tlinfo.old_value_txt = x_old_value_txt) OR ((tlinfo.old_value_txt IS NULL) AND (X_old_value_txt IS NULL)))
842 AND ((tlinfo.new_value_txt = x_new_value_txt) OR ((tlinfo.new_value_txt IS NULL) AND (X_new_value_txt IS NULL)))
843 AND ((tlinfo.old_date = x_old_date) OR ((tlinfo.old_date IS NULL) AND (X_old_date IS NULL)))
844 AND ((tlinfo.new_date = x_new_date) OR ((tlinfo.new_date IS NULL) AND (X_new_date IS NULL)))
845 AND ((tlinfo.old_amt = x_old_amt) OR ((tlinfo.old_amt IS NULL) AND (X_old_amt IS NULL)))
846 AND ((tlinfo.new_amt = x_new_amt) OR ((tlinfo.new_amt IS NULL) AND (X_new_amt IS NULL)))
847 AND ((tlinfo.disbursement_number = x_disbursement_number) OR ((tlinfo.disbursement_number IS NULL) AND (X_disbursement_number IS NULL)))
848 AND ((tlinfo.disbursement_date = x_disbursement_date) OR ((tlinfo.disbursement_date IS NULL) AND (X_disbursement_date IS NULL)))
849 AND ((tlinfo.change_issue_code = x_change_issue_code) OR ((tlinfo.change_issue_code IS NULL) AND (X_change_issue_code IS NULL)))
850 AND ((tlinfo.disbursement_cancel_date = x_disbursement_cancel_date) OR ((tlinfo.disbursement_cancel_date IS NULL) AND (X_disbursement_cancel_date IS NULL)))
851 AND ((tlinfo.disbursement_cancel_amt = x_disbursement_cancel_amt) OR ((tlinfo.disbursement_cancel_amt IS NULL) AND (X_disbursement_cancel_amt IS NULL)))
852 AND ((tlinfo.disbursement_revised_amt = x_disbursement_revised_amt) OR ((tlinfo.disbursement_revised_amt IS NULL) AND (X_disbursement_revised_amt IS NULL)))
853 AND ((tlinfo.disbursement_revised_date = x_disbursement_revised_date) OR ((tlinfo.disbursement_revised_date IS NULL) AND (X_disbursement_revised_date IS NULL)))
854 AND ((tlinfo.disbursement_reissue_code = x_disbursement_reissue_code) OR ((tlinfo.disbursement_reissue_code IS NULL) AND (X_disbursement_reissue_code IS NULL)))
855 AND ((tlinfo.disbursement_reinst_code = x_disbursement_reinst_code) OR ((tlinfo.disbursement_reinst_code IS NULL) AND (X_disbursement_reinst_code IS NULL)))
856 AND ((tlinfo.disbursement_return_amt = x_disbursement_return_amt) OR ((tlinfo.disbursement_return_amt IS NULL) AND (X_disbursement_return_amt IS NULL)))
857 AND ((tlinfo.disbursement_return_date = x_disbursement_return_date) OR ((tlinfo.disbursement_return_date IS NULL) AND (X_disbursement_return_date IS NULL)))
858 AND ((tlinfo.disbursement_return_code = x_disbursement_return_code) OR ((tlinfo.disbursement_return_code IS NULL) AND (X_disbursement_return_code IS NULL)))
859 AND ((tlinfo.post_with_disb_return_amt = x_post_with_disb_return_amt) OR ((tlinfo.post_with_disb_return_amt IS NULL) AND (X_post_with_disb_return_amt IS NULL)))
863 AND ((tlinfo.prev_with_disb_return_date = x_prev_with_disb_return_date) OR ((tlinfo.prev_with_disb_return_date IS NULL) AND (X_prev_with_disb_return_date IS NULL)))
860 AND ((tlinfo.post_with_disb_return_date = x_post_with_disb_return_date) OR ((tlinfo.post_with_disb_return_date IS NULL) AND (X_post_with_disb_return_date IS NULL)))
861 AND ((tlinfo.post_with_disb_return_code = x_post_with_disb_return_code) OR ((tlinfo.post_with_disb_return_code IS NULL) AND (X_post_with_disb_return_code IS NULL)))
862 AND ((tlinfo.prev_with_disb_return_amt = x_prev_with_disb_return_amt) OR ((tlinfo.prev_with_disb_return_amt IS NULL) AND (X_prev_with_disb_return_amt IS NULL)))
864 AND ((tlinfo.school_use_txt = x_school_use_txt) OR ((tlinfo.school_use_txt IS NULL) AND (X_school_use_txt IS NULL)))
865 AND ((tlinfo.lender_use_txt = x_lender_use_txt) OR ((tlinfo.lender_use_txt IS NULL) AND (X_lender_use_txt IS NULL)))
866 AND ((tlinfo.guarantor_use_txt = x_guarantor_use_txt) OR ((tlinfo.guarantor_use_txt IS NULL) AND (X_guarantor_use_txt IS NULL)))
867 AND ((tlinfo.validation_edit_txt = x_validation_edit_txt) OR ((tlinfo.validation_edit_txt IS NULL) AND (X_validation_edit_txt IS NULL)))
868 AND ((RTRIM(LTRIM(tlinfo.send_record_txt)) = RTRIM(LTRIM(x_send_record_txt))) OR ((RTRIM(LTRIM(tlinfo.send_record_txt)) IS NULL) AND (RTRIM(LTRIM(X_send_record_txt)) IS NULL)))
869 ) THEN
870 NULL;
871 ELSE
872 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
873 igs_ge_msg_stack.add;
874 app_exception.raise_exception;
875 END IF;
876
877 RETURN;
878
879 END lock_row;
880
881
882 PROCEDURE update_row (
883 x_rowid IN VARCHAR2,
884 x_clchgsnd_id IN NUMBER,
885 x_award_id IN NUMBER,
886 x_loan_number_txt IN VARCHAR2,
887 x_cl_version_code IN VARCHAR2,
888 x_change_field_code IN VARCHAR2,
889 x_change_record_type_txt IN VARCHAR2,
890 x_change_code_txt IN VARCHAR2,
891 x_status_code IN VARCHAR2,
892 x_status_date IN DATE,
893 x_response_status_code IN VARCHAR2,
894 x_old_value_txt IN VARCHAR2,
895 x_new_value_txt IN VARCHAR2,
896 x_old_date IN DATE,
897 x_new_date IN DATE,
898 x_old_amt IN NUMBER,
899 x_new_amt IN NUMBER,
900 x_disbursement_number IN NUMBER,
901 x_disbursement_date IN DATE,
902 x_change_issue_code IN VARCHAR2,
903 x_disbursement_cancel_date IN DATE,
904 x_disbursement_cancel_amt IN NUMBER,
905 x_disbursement_revised_amt IN NUMBER,
906 x_disbursement_revised_date IN DATE,
907 x_disbursement_reissue_code IN VARCHAR2,
908 x_disbursement_reinst_code IN VARCHAR2,
909 x_disbursement_return_amt IN NUMBER,
910 x_disbursement_return_date IN DATE,
911 x_disbursement_return_code IN VARCHAR2,
912 x_post_with_disb_return_amt IN NUMBER,
913 x_post_with_disb_return_date IN DATE,
914 x_post_with_disb_return_code IN VARCHAR2,
915 x_prev_with_disb_return_amt IN NUMBER,
916 x_prev_with_disb_return_date IN DATE,
917 x_school_use_txt IN VARCHAR2,
918 x_lender_use_txt IN VARCHAR2,
919 x_guarantor_use_txt IN VARCHAR2,
920 x_validation_edit_txt IN VARCHAR2,
921 x_send_record_txt IN VARCHAR2,
922 x_mode IN VARCHAR2
923 ) AS
924 /*
925 || Created By : [email protected]
926 || Created On : 13-OCT-2004
927 || Purpose : Handles the UPDATE DML logic for the table.
928 || Known limitations, enhancements or remarks :
929 || Change History :
930 || Who When What
931 || (reverse chronological order - newest change first)
932 || bvisvana 13-Sept-2005 Bug # 4575843 - Added after_dml after updation is complete.
933 */
934 x_last_update_date DATE ;
935 x_last_updated_by NUMBER;
936 x_last_update_login NUMBER;
937 x_request_id NUMBER;
938 x_program_id NUMBER;
939 x_program_application_id NUMBER;
940 x_program_update_date DATE;
941
942 BEGIN
943
944 x_last_update_date := SYSDATE;
945 IF (X_MODE = 'I') THEN
946 x_last_updated_by := 1;
947 x_last_update_login := 0;
948 ELSIF (x_mode = 'R') THEN
949 x_last_updated_by := fnd_global.user_id;
950 IF x_last_updated_by IS NULL THEN
951 x_last_updated_by := -1;
952 END IF;
953 x_last_update_login := fnd_global.login_id;
954 IF (x_last_update_login IS NULL) THEN
955 x_last_update_login := -1;
956 END IF;
957 ELSE
958 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
959 fnd_message.set_token ('ROUTINE', 'IGF_SL_CLCHSN_DTLS_PKG.UPDATE_ROW');
960 igs_ge_msg_stack.add;
961 app_exception.raise_exception;
962 END IF;
963
964 before_dml(
965 p_action => 'UPDATE',
966 x_rowid => x_rowid,
970 x_cl_version_code => x_cl_version_code,
967 x_clchgsnd_id => x_clchgsnd_id,
968 x_award_id => x_award_id,
969 x_loan_number_txt => x_loan_number_txt,
971 x_change_field_code => x_change_field_code,
972 x_change_record_type_txt => x_change_record_type_txt,
973 x_change_code_txt => x_change_code_txt,
974 x_status_code => x_status_code,
975 x_status_date => x_status_date,
976 x_response_status_code => x_response_status_code,
977 x_old_value_txt => x_old_value_txt,
978 x_new_value_txt => x_new_value_txt,
979 x_old_date => x_old_date,
980 x_new_date => x_new_date,
981 x_old_amt => x_old_amt,
982 x_new_amt => x_new_amt,
983 x_disbursement_number => x_disbursement_number,
984 x_disbursement_date => x_disbursement_date,
985 x_change_issue_code => x_change_issue_code,
986 x_disbursement_cancel_date => x_disbursement_cancel_date,
987 x_disbursement_cancel_amt => x_disbursement_cancel_amt,
988 x_disbursement_revised_amt => x_disbursement_revised_amt,
989 x_disbursement_revised_date => x_disbursement_revised_date,
990 x_disbursement_reissue_code => x_disbursement_reissue_code,
991 x_disbursement_reinst_code => x_disbursement_reinst_code,
992 x_disbursement_return_amt => x_disbursement_return_amt,
993 x_disbursement_return_date => x_disbursement_return_date,
994 x_disbursement_return_code => x_disbursement_return_code,
995 x_post_with_disb_return_amt => x_post_with_disb_return_amt,
996 x_post_with_disb_return_date => x_post_with_disb_return_date,
997 x_post_with_disb_return_code => x_post_with_disb_return_code,
998 x_prev_with_disb_return_amt => x_prev_with_disb_return_amt,
999 x_prev_with_disb_return_date => x_prev_with_disb_return_date,
1000 x_school_use_txt => x_school_use_txt,
1001 x_lender_use_txt => x_lender_use_txt,
1002 x_guarantor_use_txt => x_guarantor_use_txt,
1003 x_validation_edit_txt => x_validation_edit_txt,
1004 x_send_record_txt => x_send_record_txt,
1005 x_creation_date => x_last_update_date,
1006 x_created_by => x_last_updated_by,
1007 x_last_update_date => x_last_update_date,
1008 x_last_updated_by => x_last_updated_by,
1009 x_last_update_login => x_last_update_login
1010 );
1011
1012 IF (x_mode = 'R') THEN
1013 x_request_id := fnd_global.conc_request_id;
1014 x_program_id := fnd_global.conc_program_id;
1015 x_program_application_id := fnd_global.prog_appl_id;
1016 IF (x_request_id = -1) THEN
1017 x_request_id := old_references.request_id;
1018 x_program_id := old_references.program_id;
1019 x_program_application_id := old_references.program_application_id;
1020 x_program_update_date := old_references.program_update_date;
1021 ELSE
1022 x_program_update_date := SYSDATE;
1023 END IF;
1024 END IF;
1025
1026 UPDATE igf_sl_clchsn_dtls
1027 SET
1028 clchgsnd_id = new_references.clchgsnd_id,
1029 award_id = new_references.award_id,
1030 loan_number_txt = new_references.loan_number_txt,
1031 cl_version_code = new_references.cl_version_code,
1032 change_field_code = new_references.change_field_code,
1033 change_record_type_txt = new_references.change_record_type_txt,
1034 change_code_txt = new_references.change_code_txt,
1035 status_code = new_references.status_code,
1036 status_date = new_references.status_date,
1037 response_status_code = new_references.response_status_code,
1038 old_value_txt = new_references.old_value_txt,
1039 new_value_txt = new_references.new_value_txt,
1040 old_date = new_references.old_date,
1041 new_date = new_references.new_date,
1042 old_amt = new_references.old_amt,
1043 new_amt = new_references.new_amt,
1044 disbursement_number = new_references.disbursement_number,
1045 disbursement_date = new_references.disbursement_date,
1046 change_issue_code = new_references.change_issue_code,
1047 disbursement_cancel_date = new_references.disbursement_cancel_date,
1048 disbursement_cancel_amt = new_references.disbursement_cancel_amt,
1049 disbursement_revised_amt = new_references.disbursement_revised_amt,
1050 disbursement_revised_date = new_references.disbursement_revised_date,
1051 disbursement_reissue_code = new_references.disbursement_reissue_code,
1052 disbursement_reinst_code = new_references.disbursement_reinst_code,
1053 disbursement_return_amt = new_references.disbursement_return_amt,
1054 disbursement_return_date = new_references.disbursement_return_date,
1055 disbursement_return_code = new_references.disbursement_return_code,
1056 post_with_disb_return_amt = new_references.post_with_disb_return_amt,
1057 post_with_disb_return_date = new_references.post_with_disb_return_date,
1058 post_with_disb_return_code = new_references.post_with_disb_return_code,
1062 lender_use_txt = new_references.lender_use_txt,
1059 prev_with_disb_return_amt = new_references.prev_with_disb_return_amt,
1060 prev_with_disb_return_date = new_references.prev_with_disb_return_date,
1061 school_use_txt = new_references.school_use_txt,
1063 guarantor_use_txt = new_references.guarantor_use_txt,
1064 validation_edit_txt = new_references.validation_edit_txt,
1065 send_record_txt = new_references.send_record_txt,
1066 last_update_date = x_last_update_date,
1067 last_updated_by = x_last_updated_by,
1068 last_update_login = x_last_update_login ,
1069 request_id = x_request_id,
1070 program_id = x_program_id,
1071 program_application_id = x_program_application_id,
1072 program_update_date = x_program_update_date
1073 WHERE rowid = x_rowid;
1074
1075 IF (SQL%NOTFOUND) THEN
1076 RAISE NO_DATA_FOUND;
1077 END IF;
1078
1079 after_dml(p_action => 'UPDATING');
1080
1081 END update_row;
1082
1083
1084 PROCEDURE add_row (
1085 x_rowid IN OUT NOCOPY VARCHAR2,
1086 x_clchgsnd_id IN OUT NOCOPY NUMBER,
1087 x_award_id IN NUMBER,
1088 x_loan_number_txt IN VARCHAR2,
1089 x_cl_version_code IN VARCHAR2,
1090 x_change_field_code IN VARCHAR2,
1091 x_change_record_type_txt IN VARCHAR2,
1092 x_change_code_txt IN VARCHAR2,
1093 x_status_code IN VARCHAR2,
1094 x_status_date IN DATE,
1095 x_response_status_code IN VARCHAR2,
1096 x_old_value_txt IN VARCHAR2,
1097 x_new_value_txt IN VARCHAR2,
1098 x_old_date IN DATE,
1099 x_new_date IN DATE,
1100 x_old_amt IN NUMBER,
1101 x_new_amt IN NUMBER,
1102 x_disbursement_number IN NUMBER,
1103 x_disbursement_date IN DATE,
1104 x_change_issue_code IN VARCHAR2,
1105 x_disbursement_cancel_date IN DATE,
1106 x_disbursement_cancel_amt IN NUMBER,
1107 x_disbursement_revised_amt IN NUMBER,
1108 x_disbursement_revised_date IN DATE,
1109 x_disbursement_reissue_code IN VARCHAR2,
1110 x_disbursement_reinst_code IN VARCHAR2,
1111 x_disbursement_return_amt IN NUMBER,
1112 x_disbursement_return_date IN DATE,
1113 x_disbursement_return_code IN VARCHAR2,
1114 x_post_with_disb_return_amt IN NUMBER,
1115 x_post_with_disb_return_date IN DATE,
1116 x_post_with_disb_return_code IN VARCHAR2,
1117 x_prev_with_disb_return_amt IN NUMBER,
1118 x_prev_with_disb_return_date IN DATE,
1119 x_school_use_txt IN VARCHAR2,
1120 x_lender_use_txt IN VARCHAR2,
1121 x_guarantor_use_txt IN VARCHAR2,
1122 x_validation_edit_txt IN VARCHAR2,
1123 x_send_record_txt IN VARCHAR2,
1124 x_mode IN VARCHAR2
1125 ) AS
1126 /*
1127 || Created By : [email protected]
1128 || Created On : 13-OCT-2004
1129 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1130 || Known limitations, enhancements or remarks :
1131 || Change History :
1132 || Who When What
1133 || (reverse chronological order - newest change first)
1134 || bvisvana 13-Sept-2005 Bug # 4575843 - Added after_dml after Inserting.
1135 */
1136 CURSOR c1 IS
1137 SELECT rowid
1138 FROM igf_sl_clchsn_dtls
1139 WHERE clchgsnd_id = x_clchgsnd_id;
1140
1141 BEGIN
1142
1143 OPEN c1;
1144 FETCH c1 INTO x_rowid;
1145 IF (c1%NOTFOUND) THEN
1146 CLOSE c1;
1147
1148 insert_row (
1149 x_rowid,
1150 x_clchgsnd_id,
1151 x_award_id,
1152 x_loan_number_txt,
1153 x_cl_version_code,
1154 x_change_field_code,
1155 x_change_record_type_txt,
1156 x_change_code_txt,
1157 x_status_code,
1158 x_status_date,
1159 x_response_status_code,
1160 x_old_value_txt,
1161 x_new_value_txt,
1162 x_old_date,
1163 x_new_date,
1164 x_old_amt,
1165 x_new_amt,
1166 x_disbursement_number,
1167 x_disbursement_date,
1168 x_change_issue_code,
1169 x_disbursement_cancel_date,
1170 x_disbursement_cancel_amt,
1171 x_disbursement_revised_amt,
1172 x_disbursement_revised_date,
1173 x_disbursement_reissue_code,
1174 x_disbursement_reinst_code,
1175 x_disbursement_return_amt,
1176 x_disbursement_return_date,
1177 x_disbursement_return_code,
1178 x_post_with_disb_return_amt,
1179 x_post_with_disb_return_date,
1180 x_post_with_disb_return_code,
1181 x_prev_with_disb_return_amt,
1182 x_prev_with_disb_return_date,
1183 x_school_use_txt,
1184 x_lender_use_txt,
1185 x_guarantor_use_txt,
1186 x_validation_edit_txt,
1187 x_send_record_txt,
1188 x_mode
1189 );
1190 RETURN;
1191 END IF;
1195 x_rowid,
1192 CLOSE c1;
1193
1194 update_row (
1196 x_clchgsnd_id,
1197 x_award_id,
1198 x_loan_number_txt,
1199 x_cl_version_code,
1200 x_change_field_code,
1201 x_change_record_type_txt,
1202 x_change_code_txt,
1203 x_status_code,
1204 x_status_date,
1205 x_response_status_code,
1206 x_old_value_txt,
1207 x_new_value_txt,
1208 x_old_date,
1209 x_new_date,
1210 x_old_amt,
1211 x_new_amt,
1212 x_disbursement_number,
1213 x_disbursement_date,
1214 x_change_issue_code,
1215 x_disbursement_cancel_date,
1216 x_disbursement_cancel_amt,
1217 x_disbursement_revised_amt,
1218 x_disbursement_revised_date,
1219 x_disbursement_reissue_code,
1220 x_disbursement_reinst_code,
1221 x_disbursement_return_amt,
1222 x_disbursement_return_date,
1223 x_disbursement_return_code,
1224 x_post_with_disb_return_amt,
1225 x_post_with_disb_return_date,
1226 x_post_with_disb_return_code,
1227 x_prev_with_disb_return_amt,
1228 x_prev_with_disb_return_date,
1229 x_school_use_txt,
1230 x_lender_use_txt,
1231 x_guarantor_use_txt,
1232 x_validation_edit_txt,
1233 x_send_record_txt,
1234 x_mode
1235 );
1236
1237 END add_row;
1238
1239
1240 PROCEDURE delete_row (
1241 x_rowid IN VARCHAR2
1242 ) AS
1243 /*
1244 || Created By : [email protected]
1245 || Created On : 13-OCT-2004
1246 || Purpose : Handles the DELETE DML logic for the table.
1247 || Known limitations, enhancements or remarks :
1248 || Change History :
1249 || Who When What
1250 || (reverse chronological order - newest change first)
1251 */
1252 BEGIN
1253
1254 before_dml (
1255 p_action => 'DELETE',
1256 x_rowid => x_rowid
1257 );
1258
1259 DELETE FROM igf_sl_clchsn_dtls
1260 WHERE rowid = x_rowid;
1261
1262 IF (SQL%NOTFOUND) THEN
1263 RAISE NO_DATA_FOUND;
1264 END IF;
1265
1266 END delete_row;
1267
1268
1269 END igf_sl_clchsn_dtls_pkg;