[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_CL_RESP_R4_PKG
Source
1 PACKAGE BODY igf_sl_cl_resp_r4_pkg AS
2 /* $Header: IGFLI33B.pls 120.0 2005/06/02 15:53:38 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sl_cl_resp_r4_all%ROWTYPE;
6 new_references igf_sl_cl_resp_r4_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_clrp1_id IN NUMBER DEFAULT NULL,
12 x_loan_number IN VARCHAR2 DEFAULT NULL,
13 x_fed_stafford_loan_debt IN NUMBER DEFAULT NULL,
14 x_fed_sls_debt IN NUMBER DEFAULT NULL,
15 x_heal_debt IN NUMBER DEFAULT NULL,
16 x_perkins_debt IN NUMBER DEFAULT NULL,
17 x_other_debt IN NUMBER DEFAULT NULL,
18 x_crdt_undr_difft_name IN VARCHAR2 DEFAULT NULL,
19 x_borw_gross_annual_sal IN NUMBER DEFAULT NULL,
20 x_borw_other_income IN NUMBER DEFAULT NULL,
21 x_student_major IN VARCHAR2 DEFAULT NULL,
22 x_int_rate_opt IN VARCHAR2 DEFAULT NULL,
23 x_repayment_opt_code IN VARCHAR2 DEFAULT NULL,
24 x_stud_mth_housing_pymt IN NUMBER DEFAULT NULL,
25 x_stud_mth_crdtcard_pymt IN NUMBER DEFAULT NULL,
26 x_stud_mth_auto_pymt IN NUMBER DEFAULT NULL,
27 x_stud_mth_ed_loan_pymt IN NUMBER DEFAULT NULL,
28 x_stud_mth_other_pymt IN NUMBER DEFAULT NULL,
29 x_cosnr_1_last_name IN VARCHAR2 DEFAULT NULL,
30 x_cosnr_1_first_name IN VARCHAR2 DEFAULT NULL,
31 x_cosnr_1_middle_name IN VARCHAR2 DEFAULT NULL,
32 x_cosnr_1_ssn IN VARCHAR2 DEFAULT NULL,
33 x_cosnr_1_citizenship IN VARCHAR2 DEFAULT NULL,
34 x_cosnr_1_addr_line1 IN VARCHAR2 DEFAULT NULL,
35 x_cosnr_1_addr_line2 IN VARCHAR2 DEFAULT NULL,
36 x_cosnr_1_city IN VARCHAR2 DEFAULT NULL,
37 x_cosnr_1_state IN VARCHAR2 DEFAULT NULL,
38 x_cosnr_1_zip IN VARCHAR2 DEFAULT NULL,
39 x_cosnr_1_zip_suffix IN VARCHAR2 DEFAULT NULL,
40 x_cosnr_1_phone IN VARCHAR2 DEFAULT NULL,
41 x_cosnr_1_sig_code IN VARCHAR2 DEFAULT NULL,
42 x_cosnr_1_gross_anl_sal IN NUMBER DEFAULT NULL,
43 x_cosnr_1_other_income IN NUMBER DEFAULT NULL,
44 x_cosnr_1_forn_postal_code IN VARCHAR2 DEFAULT NULL,
45 x_cosnr_1_forn_phone_prefix IN VARCHAR2 DEFAULT NULL,
46 x_cosnr_1_dob IN DATE DEFAULT NULL,
47 x_cosnr_1_license_state IN VARCHAR2 DEFAULT NULL,
48 x_cosnr_1_license_num IN VARCHAR2 DEFAULT NULL,
49 x_cosnr_1_relationship_to IN VARCHAR2 DEFAULT NULL,
50 x_cosnr_1_years_at_addr IN VARCHAR2 DEFAULT NULL,
51 x_cosnr_1_mth_housing_pymt IN NUMBER DEFAULT NULL,
52 x_cosnr_1_mth_crdtcard_pymt IN NUMBER DEFAULT NULL,
53 x_cosnr_1_mth_auto_pymt IN NUMBER DEFAULT NULL,
54 x_cosnr_1_mth_ed_loan_pymt IN NUMBER DEFAULT NULL,
55 x_cosnr_1_mth_other_pymt IN NUMBER DEFAULT NULL,
56 x_cosnr_1_crdt_auth_code IN VARCHAR2 DEFAULT NULL,
57 x_cosnr_2_last_name IN VARCHAR2 DEFAULT NULL,
58 x_cosnr_2_first_name IN VARCHAR2 DEFAULT NULL,
59 x_cosnr_2_middle_name IN VARCHAR2 DEFAULT NULL,
60 x_cosnr_2_ssn IN VARCHAR2 DEFAULT NULL,
61 x_cosnr_2_citizenship IN VARCHAR2 DEFAULT NULL,
62 x_cosnr_2_addr_line1 IN VARCHAR2 DEFAULT NULL,
63 x_cosnr_2_addr_line2 IN VARCHAR2 DEFAULT NULL,
64 x_cosnr_2_city IN VARCHAR2 DEFAULT NULL,
65 x_cosnr_2_state IN VARCHAR2 DEFAULT NULL,
66 x_cosnr_2_zip IN VARCHAR2 DEFAULT NULL,
67 x_cosnr_2_zip_suffix IN VARCHAR2 DEFAULT NULL,
68 x_cosnr_2_phone IN VARCHAR2 DEFAULT NULL,
69 x_cosnr_2_sig_code IN VARCHAR2 DEFAULT NULL,
70 x_cosnr_2_gross_anl_sal IN NUMBER DEFAULT NULL,
71 x_cosnr_2_other_income IN NUMBER DEFAULT NULL,
72 x_cosnr_2_forn_postal_code IN VARCHAR2 DEFAULT NULL,
73 x_cosnr_2_forn_phone_prefix IN VARCHAR2 DEFAULT NULL,
74 x_cosnr_2_dob IN DATE DEFAULT NULL,
75 x_cosnr_2_license_state IN VARCHAR2 DEFAULT NULL,
76 x_cosnr_2_license_num IN VARCHAR2 DEFAULT NULL,
77 x_cosnr_2_relationship_to IN VARCHAR2 DEFAULT NULL,
78 x_cosnr_2_years_at_addr IN VARCHAR2 DEFAULT NULL,
79 x_cosnr_2_mth_housing_pymt IN NUMBER DEFAULT NULL,
80 x_cosnr_2_mth_crdtcard_pymt IN NUMBER DEFAULT NULL,
81 x_cosnr_2_mth_auto_pymt IN NUMBER DEFAULT NULL,
82 x_cosnr_2_mth_ed_loan_pymt IN NUMBER DEFAULT NULL,
83 x_cosnr_2_mth_other_pymt IN NUMBER DEFAULT NULL,
84 x_cosnr_2_crdt_auth_code IN VARCHAR2 DEFAULT NULL,
85 x_other_loan_amt IN NUMBER DEFAULT NULL,
86 x_alt_layout_owner_code_txt IN VARCHAR2 DEFAULT NULL,
87 x_alt_layout_identi_code_txt IN VARCHAR2 DEFAULT NULL,
88 x_student_school_phone_txt IN VARCHAR2 DEFAULT NULL,
89 x_first_csgnr_elec_sign_flag IN VARCHAR2 DEFAULT NULL,
90 x_second_csgnr_elec_sign_flag IN VARCHAR2 DEFAULT NULL,
91 x_creation_date IN DATE DEFAULT NULL,
92 x_created_by IN NUMBER DEFAULT NULL,
93 x_last_update_date IN DATE DEFAULT NULL,
94 x_last_updated_by IN NUMBER DEFAULT NULL,
95 x_last_update_login IN NUMBER DEFAULT NULL
96 ) AS
97 /*
98 || Created By : viramali
99 || Created On : 10-MAY-2001
100 || Purpose : Initialises the Old and New references for the columns of the table.
101 || Known limitations, enhancements or remarks :
102 || Change History :
103 || Who When What
104 || (reverse chronological order - newest change first)
105 */
106
107 CURSOR cur_old_ref_values IS
108 SELECT *
109 FROM IGF_SL_CL_RESP_R4_ALL
110 WHERE rowid = x_rowid;
111
112 BEGIN
113
114 l_rowid := x_rowid;
115
116 -- Code for setting the Old and New Reference Values.
117 -- Populate Old Values.
118 OPEN cur_old_ref_values;
119 FETCH cur_old_ref_values INTO old_references;
120 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
121 CLOSE cur_old_ref_values;
122 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
123 igs_ge_msg_stack.add;
124 app_exception.raise_exception;
125 RETURN;
126 END IF;
127 CLOSE cur_old_ref_values;
128
129 -- Populate New Values.
130 new_references.clrp1_id := x_clrp1_id;
131 new_references.loan_number := x_loan_number;
132 new_references.fed_stafford_loan_debt := x_fed_stafford_loan_debt;
133 new_references.fed_sls_debt := x_fed_sls_debt;
134 new_references.heal_debt := x_heal_debt;
135 new_references.perkins_debt := x_perkins_debt;
136 new_references.other_debt := x_other_debt;
137 new_references.crdt_undr_difft_name := x_crdt_undr_difft_name;
138 new_references.borw_gross_annual_sal := x_borw_gross_annual_sal;
139 new_references.borw_other_income := x_borw_other_income;
140 new_references.student_major := x_student_major;
141 new_references.int_rate_opt := x_int_rate_opt;
142 new_references.repayment_opt_code := x_repayment_opt_code;
143 new_references.stud_mth_housing_pymt := x_stud_mth_housing_pymt;
144 new_references.stud_mth_crdtcard_pymt := x_stud_mth_crdtcard_pymt;
145 new_references.stud_mth_auto_pymt := x_stud_mth_auto_pymt;
146 new_references.stud_mth_ed_loan_pymt := x_stud_mth_ed_loan_pymt;
147 new_references.stud_mth_other_pymt := x_stud_mth_other_pymt;
148 new_references.cosnr_1_last_name := x_cosnr_1_last_name;
149 new_references.cosnr_1_first_name := x_cosnr_1_first_name;
150 new_references.cosnr_1_middle_name := x_cosnr_1_middle_name;
151 new_references.cosnr_1_ssn := x_cosnr_1_ssn;
152 new_references.cosnr_1_citizenship := x_cosnr_1_citizenship;
153 new_references.cosnr_1_addr_line1 := x_cosnr_1_addr_line1;
154 new_references.cosnr_1_addr_line2 := x_cosnr_1_addr_line2;
155 new_references.cosnr_1_city := x_cosnr_1_city;
156 new_references.cosnr_1_state := x_cosnr_1_state;
157 new_references.cosnr_1_zip := x_cosnr_1_zip;
158 new_references.cosnr_1_zip_suffix := x_cosnr_1_zip_suffix;
159 new_references.cosnr_1_phone := x_cosnr_1_phone;
160 new_references.cosnr_1_sig_code := x_cosnr_1_sig_code;
161 new_references.cosnr_1_gross_anl_sal := x_cosnr_1_gross_anl_sal;
162 new_references.cosnr_1_other_income := x_cosnr_1_other_income;
163 new_references.cosnr_1_forn_postal_code := x_cosnr_1_forn_postal_code;
164 new_references.cosnr_1_forn_phone_prefix := x_cosnr_1_forn_phone_prefix;
165 new_references.cosnr_1_dob := x_cosnr_1_dob;
166 new_references.cosnr_1_license_state := x_cosnr_1_license_state;
167 new_references.cosnr_1_license_num := x_cosnr_1_license_num;
168 new_references.cosnr_1_relationship_to := x_cosnr_1_relationship_to;
169 new_references.cosnr_1_years_at_addr := x_cosnr_1_years_at_addr;
170 new_references.cosnr_1_mth_housing_pymt := x_cosnr_1_mth_housing_pymt;
171 new_references.cosnr_1_mth_crdtcard_pymt := x_cosnr_1_mth_crdtcard_pymt;
172 new_references.cosnr_1_mth_auto_pymt := x_cosnr_1_mth_auto_pymt;
173 new_references.cosnr_1_mth_ed_loan_pymt := x_cosnr_1_mth_ed_loan_pymt;
174 new_references.cosnr_1_mth_other_pymt := x_cosnr_1_mth_other_pymt;
175 new_references.cosnr_1_crdt_auth_code := x_cosnr_1_crdt_auth_code;
176 new_references.cosnr_2_last_name := x_cosnr_2_last_name;
177 new_references.cosnr_2_first_name := x_cosnr_2_first_name;
178 new_references.cosnr_2_middle_name := x_cosnr_2_middle_name;
179 new_references.cosnr_2_ssn := x_cosnr_2_ssn;
180 new_references.cosnr_2_citizenship := x_cosnr_2_citizenship;
181 new_references.cosnr_2_addr_line1 := x_cosnr_2_addr_line1;
182 new_references.cosnr_2_addr_line2 := x_cosnr_2_addr_line2;
183 new_references.cosnr_2_city := x_cosnr_2_city;
184 new_references.cosnr_2_state := x_cosnr_2_state;
185 new_references.cosnr_2_zip := x_cosnr_2_zip;
186 new_references.cosnr_2_zip_suffix := x_cosnr_2_zip_suffix;
187 new_references.cosnr_2_phone := x_cosnr_2_phone;
188 new_references.cosnr_2_sig_code := x_cosnr_2_sig_code;
189 new_references.cosnr_2_gross_anl_sal := x_cosnr_2_gross_anl_sal;
190 new_references.cosnr_2_other_income := x_cosnr_2_other_income;
191 new_references.cosnr_2_forn_postal_code := x_cosnr_2_forn_postal_code;
192 new_references.cosnr_2_forn_phone_prefix := x_cosnr_2_forn_phone_prefix;
193 new_references.cosnr_2_dob := x_cosnr_2_dob;
194 new_references.cosnr_2_license_state := x_cosnr_2_license_state;
195 new_references.cosnr_2_license_num := x_cosnr_2_license_num;
196 new_references.cosnr_2_relationship_to := x_cosnr_2_relationship_to;
197 new_references.cosnr_2_years_at_addr := x_cosnr_2_years_at_addr;
198 new_references.cosnr_2_mth_housing_pymt := x_cosnr_2_mth_housing_pymt;
199 new_references.cosnr_2_mth_crdtcard_pymt := x_cosnr_2_mth_crdtcard_pymt;
200 new_references.cosnr_2_mth_auto_pymt := x_cosnr_2_mth_auto_pymt;
201 new_references.cosnr_2_mth_ed_loan_pymt := x_cosnr_2_mth_ed_loan_pymt;
202 new_references.cosnr_2_mth_other_pymt := x_cosnr_2_mth_other_pymt;
203 new_references.cosnr_2_crdt_auth_code := x_cosnr_2_crdt_auth_code;
204 new_references.other_loan_amt := x_other_loan_amt ;
205 new_references.alt_layout_owner_code_txt := x_alt_layout_owner_code_txt;
206 new_references.alt_layout_identi_code_txt := x_alt_layout_identi_code_txt;
207 new_references.student_school_phone_txt := x_student_school_phone_txt;
208 new_references.first_csgnr_elec_sign_flag := x_first_csgnr_elec_sign_flag;
209 new_references.second_csgnr_elec_sign_flag := x_second_csgnr_elec_sign_flag;
210
211
212
213 IF (p_action = 'UPDATE') THEN
214 new_references.creation_date := old_references.creation_date;
215 new_references.created_by := old_references.created_by;
216 ELSE
217 new_references.creation_date := x_creation_date;
218 new_references.created_by := x_created_by;
219 END IF;
220
221 new_references.last_update_date := x_last_update_date;
222 new_references.last_updated_by := x_last_updated_by;
223 new_references.last_update_login := x_last_update_login;
224
225 END set_column_values;
226
227
228 PROCEDURE check_parent_existance AS
229 /*
230 || Created By : viramali
231 || Created On : 10-MAY-2001
232 || Purpose : Checks for the existance of Parent records.
233 || Known limitations, enhancements or remarks :
234 || Change History :
235 || Who When What
236 || (reverse chronological order - newest change first)
237 */
238 BEGIN
239
240 IF (((old_references.clrp1_id = new_references.clrp1_id)) OR
241 ((new_references.clrp1_id IS NULL))) THEN
242 NULL;
243 ELSIF NOT igf_sl_cl_resp_r1_pkg.get_pk_for_validation (
244 new_references.clrp1_id
245 ) THEN
246 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
247 igs_ge_msg_stack.add;
248 app_exception.raise_exception;
249 END IF;
250
251 END check_parent_existance;
252
253 PROCEDURE check_child_existance IS
254
255 BEGIN
256 igf_sl_cl_resp_r7_dtls_pkg.get_fk_igf_sl_cl_resp_r4 (
257 old_references.clrp1_id
258 );
259 END check_child_existance;
260
261 FUNCTION get_pk_for_validation (
262 x_clrp1_id IN NUMBER
263 ) RETURN BOOLEAN AS
264 /*
265 || Created By : viramali
266 || Created On : 10-MAY-2001
267 || Purpose : Validates the Primary Key of the table.
268 || Known limitations, enhancements or remarks :
269 || Change History :
270 || Who When What
271 || (reverse chronological order - newest change first)
272 */
273 CURSOR cur_rowid IS
274 SELECT rowid
275 FROM igf_sl_cl_resp_r4_all
276 WHERE clrp1_id = x_clrp1_id
277 FOR UPDATE NOWAIT;
278
279 lv_rowid cur_rowid%RowType;
280
281 BEGIN
282
283 OPEN cur_rowid;
284 FETCH cur_rowid INTO lv_rowid;
285 IF (cur_rowid%FOUND) THEN
286 CLOSE cur_rowid;
287 RETURN(TRUE);
288 ELSE
289 CLOSE cur_rowid;
290 RETURN(FALSE);
291 END IF;
292
293 END get_pk_for_validation;
294
295
296 PROCEDURE get_fk_igf_sl_cl_resp_r1 (
297 x_clrp1_id IN NUMBER
298 ) AS
299 /*
300 || Created By : viramali
301 || Created On : 10-MAY-2001
302 || Purpose : Validates the Foreign Keys for the table.
303 || Known limitations, enhancements or remarks :
304 || Change History :
305 || Who When What
306 || (reverse chronological order - newest change first)
307 */
308 CURSOR cur_rowid IS
309 SELECT rowid
310 FROM igf_sl_cl_resp_r4_all
311 WHERE ((clrp1_id = x_clrp1_id));
312
313 lv_rowid cur_rowid%RowType;
314
315 BEGIN
316
317 OPEN cur_rowid;
318 FETCH cur_rowid INTO lv_rowid;
319 IF (cur_rowid%FOUND) THEN
320 CLOSE cur_rowid;
321 fnd_message.set_name ('IGF', 'IGF_SL_CLRP4_CLRP1_FK');
322 igs_ge_msg_stack.add;
323 app_exception.raise_exception;
324 RETURN;
325 END IF;
326 CLOSE cur_rowid;
327
328 END get_fk_igf_sl_cl_resp_r1;
329
330
331 PROCEDURE before_dml (
332 p_action IN VARCHAR2,
333 x_rowid IN VARCHAR2 DEFAULT NULL,
334 x_clrp1_id IN NUMBER DEFAULT NULL,
335 x_loan_number IN VARCHAR2 DEFAULT NULL,
336 x_fed_stafford_loan_debt IN NUMBER DEFAULT NULL,
337 x_fed_sls_debt IN NUMBER DEFAULT NULL,
338 x_heal_debt IN NUMBER DEFAULT NULL,
339 x_perkins_debt IN NUMBER DEFAULT NULL,
340 x_other_debt IN NUMBER DEFAULT NULL,
341 x_crdt_undr_difft_name IN VARCHAR2 DEFAULT NULL,
342 x_borw_gross_annual_sal IN NUMBER DEFAULT NULL,
343 x_borw_other_income IN NUMBER DEFAULT NULL,
344 x_student_major IN VARCHAR2 DEFAULT NULL,
345 x_int_rate_opt IN VARCHAR2 DEFAULT NULL,
346 x_repayment_opt_code IN VARCHAR2 DEFAULT NULL,
347 x_stud_mth_housing_pymt IN NUMBER DEFAULT NULL,
348 x_stud_mth_crdtcard_pymt IN NUMBER DEFAULT NULL,
349 x_stud_mth_auto_pymt IN NUMBER DEFAULT NULL,
350 x_stud_mth_ed_loan_pymt IN NUMBER DEFAULT NULL,
351 x_stud_mth_other_pymt IN NUMBER DEFAULT NULL,
352 x_cosnr_1_last_name IN VARCHAR2 DEFAULT NULL,
353 x_cosnr_1_first_name IN VARCHAR2 DEFAULT NULL,
354 x_cosnr_1_middle_name IN VARCHAR2 DEFAULT NULL,
355 x_cosnr_1_ssn IN VARCHAR2 DEFAULT NULL,
356 x_cosnr_1_citizenship IN VARCHAR2 DEFAULT NULL,
357 x_cosnr_1_addr_line1 IN VARCHAR2 DEFAULT NULL,
358 x_cosnr_1_addr_line2 IN VARCHAR2 DEFAULT NULL,
359 x_cosnr_1_city IN VARCHAR2 DEFAULT NULL,
360 x_cosnr_1_state IN VARCHAR2 DEFAULT NULL,
361 x_cosnr_1_zip IN VARCHAR2 DEFAULT NULL,
362 x_cosnr_1_zip_suffix IN VARCHAR2 DEFAULT NULL,
363 x_cosnr_1_phone IN VARCHAR2 DEFAULT NULL,
364 x_cosnr_1_sig_code IN VARCHAR2 DEFAULT NULL,
365 x_cosnr_1_gross_anl_sal IN NUMBER DEFAULT NULL,
366 x_cosnr_1_other_income IN NUMBER DEFAULT NULL,
367 x_cosnr_1_forn_postal_code IN VARCHAR2 DEFAULT NULL,
368 x_cosnr_1_forn_phone_prefix IN VARCHAR2 DEFAULT NULL,
369 x_cosnr_1_dob IN DATE DEFAULT NULL,
370 x_cosnr_1_license_state IN VARCHAR2 DEFAULT NULL,
371 x_cosnr_1_license_num IN VARCHAR2 DEFAULT NULL,
372 x_cosnr_1_relationship_to IN VARCHAR2 DEFAULT NULL,
373 x_cosnr_1_years_at_addr IN VARCHAR2 DEFAULT NULL,
374 x_cosnr_1_mth_housing_pymt IN NUMBER DEFAULT NULL,
375 x_cosnr_1_mth_crdtcard_pymt IN NUMBER DEFAULT NULL,
376 x_cosnr_1_mth_auto_pymt IN NUMBER DEFAULT NULL,
377 x_cosnr_1_mth_ed_loan_pymt IN NUMBER DEFAULT NULL,
378 x_cosnr_1_mth_other_pymt IN NUMBER DEFAULT NULL,
379 x_cosnr_1_crdt_auth_code IN VARCHAR2 DEFAULT NULL,
380 x_cosnr_2_last_name IN VARCHAR2 DEFAULT NULL,
381 x_cosnr_2_first_name IN VARCHAR2 DEFAULT NULL,
382 x_cosnr_2_middle_name IN VARCHAR2 DEFAULT NULL,
383 x_cosnr_2_ssn IN VARCHAR2 DEFAULT NULL,
384 x_cosnr_2_citizenship IN VARCHAR2 DEFAULT NULL,
385 x_cosnr_2_addr_line1 IN VARCHAR2 DEFAULT NULL,
386 x_cosnr_2_addr_line2 IN VARCHAR2 DEFAULT NULL,
387 x_cosnr_2_city IN VARCHAR2 DEFAULT NULL,
388 x_cosnr_2_state IN VARCHAR2 DEFAULT NULL,
389 x_cosnr_2_zip IN VARCHAR2 DEFAULT NULL,
390 x_cosnr_2_zip_suffix IN VARCHAR2 DEFAULT NULL,
391 x_cosnr_2_phone IN VARCHAR2 DEFAULT NULL,
392 x_cosnr_2_sig_code IN VARCHAR2 DEFAULT NULL,
393 x_cosnr_2_gross_anl_sal IN NUMBER DEFAULT NULL,
394 x_cosnr_2_other_income IN NUMBER DEFAULT NULL,
395 x_cosnr_2_forn_postal_code IN VARCHAR2 DEFAULT NULL,
396 x_cosnr_2_forn_phone_prefix IN VARCHAR2 DEFAULT NULL,
397 x_cosnr_2_dob IN DATE DEFAULT NULL,
398 x_cosnr_2_license_state IN VARCHAR2 DEFAULT NULL,
399 x_cosnr_2_license_num IN VARCHAR2 DEFAULT NULL,
400 x_cosnr_2_relationship_to IN VARCHAR2 DEFAULT NULL,
401 x_cosnr_2_years_at_addr IN VARCHAR2 DEFAULT NULL,
402 x_cosnr_2_mth_housing_pymt IN NUMBER DEFAULT NULL,
403 x_cosnr_2_mth_crdtcard_pymt IN NUMBER DEFAULT NULL,
404 x_cosnr_2_mth_auto_pymt IN NUMBER DEFAULT NULL,
405 x_cosnr_2_mth_ed_loan_pymt IN NUMBER DEFAULT NULL,
406 x_cosnr_2_mth_other_pymt IN NUMBER DEFAULT NULL,
407 x_cosnr_2_crdt_auth_code IN VARCHAR2 DEFAULT NULL,
408 x_other_loan_amt IN NUMBER DEFAULT NULL,
409 x_alt_layout_owner_code_txt IN VARCHAR2 DEFAULT NULL,
410 x_alt_layout_identi_code_txt IN VARCHAR2 DEFAULT NULL,
411 x_student_school_phone_txt IN VARCHAR2 DEFAULT NULL,
412 x_first_csgnr_elec_sign_flag IN VARCHAR2 DEFAULT NULL,
413 x_second_csgnr_elec_sign_flag IN VARCHAR2 DEFAULT NULL,
414 x_creation_date IN DATE DEFAULT NULL,
415 x_created_by IN NUMBER DEFAULT NULL,
416 x_last_update_date IN DATE DEFAULT NULL,
417 x_last_updated_by IN NUMBER DEFAULT NULL,
418 x_last_update_login IN NUMBER DEFAULT NULL
419 ) AS
420 /*
421 || Created By : viramali
422 || Created On : 10-MAY-2001
423 || Purpose : Initialises the columns, Checks Constraints, Calls the
424 || Trigger Handlers for the table, before any DML operation.
425 || Known limitations, enhancements or remarks :
426 || Change History :
427 || Who When What
428 || (reverse chronological order - newest change first)
429 */
430 BEGIN
431
432 set_column_values (
433 p_action,
434 x_rowid,
435 x_clrp1_id,
436 x_loan_number,
437 x_fed_stafford_loan_debt,
438 x_fed_sls_debt,
439 x_heal_debt,
440 x_perkins_debt,
441 x_other_debt,
442 x_crdt_undr_difft_name,
443 x_borw_gross_annual_sal,
444 x_borw_other_income,
445 x_student_major,
446 x_int_rate_opt,
447 x_repayment_opt_code,
448 x_stud_mth_housing_pymt,
449 x_stud_mth_crdtcard_pymt,
450 x_stud_mth_auto_pymt,
451 x_stud_mth_ed_loan_pymt,
452 x_stud_mth_other_pymt,
453 x_cosnr_1_last_name,
454 x_cosnr_1_first_name,
455 x_cosnr_1_middle_name,
456 x_cosnr_1_ssn,
457 x_cosnr_1_citizenship,
458 x_cosnr_1_addr_line1,
459 x_cosnr_1_addr_line2,
460 x_cosnr_1_city,
461 x_cosnr_1_state,
462 x_cosnr_1_zip,
463 x_cosnr_1_zip_suffix,
464 x_cosnr_1_phone,
465 x_cosnr_1_sig_code,
466 x_cosnr_1_gross_anl_sal,
467 x_cosnr_1_other_income,
468 x_cosnr_1_forn_postal_code,
469 x_cosnr_1_forn_phone_prefix,
470 x_cosnr_1_dob,
471 x_cosnr_1_license_state,
472 x_cosnr_1_license_num,
473 x_cosnr_1_relationship_to,
474 x_cosnr_1_years_at_addr,
475 x_cosnr_1_mth_housing_pymt,
476 x_cosnr_1_mth_crdtcard_pymt,
477 x_cosnr_1_mth_auto_pymt,
478 x_cosnr_1_mth_ed_loan_pymt,
479 x_cosnr_1_mth_other_pymt,
480 x_cosnr_1_crdt_auth_code,
481 x_cosnr_2_last_name,
482 x_cosnr_2_first_name,
483 x_cosnr_2_middle_name,
484 x_cosnr_2_ssn,
485 x_cosnr_2_citizenship,
486 x_cosnr_2_addr_line1,
487 x_cosnr_2_addr_line2,
488 x_cosnr_2_city,
489 x_cosnr_2_state,
490 x_cosnr_2_zip,
491 x_cosnr_2_zip_suffix,
492 x_cosnr_2_phone,
493 x_cosnr_2_sig_code,
494 x_cosnr_2_gross_anl_sal,
495 x_cosnr_2_other_income,
496 x_cosnr_2_forn_postal_code,
497 x_cosnr_2_forn_phone_prefix,
498 x_cosnr_2_dob,
499 x_cosnr_2_license_state,
500 x_cosnr_2_license_num,
501 x_cosnr_2_relationship_to,
502 x_cosnr_2_years_at_addr,
503 x_cosnr_2_mth_housing_pymt,
504 x_cosnr_2_mth_crdtcard_pymt,
505 x_cosnr_2_mth_auto_pymt,
506 x_cosnr_2_mth_ed_loan_pymt,
507 x_cosnr_2_mth_other_pymt,
508 x_cosnr_2_crdt_auth_code,
509 x_other_loan_amt,
510 x_alt_layout_owner_code_txt,
511 x_alt_layout_identi_code_txt,
512 x_student_school_phone_txt,
513 x_first_csgnr_elec_sign_flag,
514 x_second_csgnr_elec_sign_flag,
515 x_creation_date,
516 x_created_by,
517 x_last_update_date,
518 x_last_updated_by,
519 x_last_update_login
520 );
521
522 IF (p_action = 'INSERT') THEN
523 -- Call all the procedures related to Before Insert.
524 IF ( get_pk_for_validation(
525 new_references.clrp1_id
526 )
527 ) THEN
528 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
529 igs_ge_msg_stack.add;
530 app_exception.raise_exception;
531 END IF;
532 check_parent_existance;
533 ELSIF (p_action = 'UPDATE') THEN
534 -- Call all the procedures related to Before Update.
535 check_parent_existance;
536
537 ELSIF (p_action = 'DELETE') THEN
538 -- Call all the procedures related to Before Delete.
539 check_child_existance;
540 ELSIF (p_action = 'VALIDATE_INSERT') THEN
541 -- Call all the procedures related to Before Insert.
542 IF ( get_pk_for_validation (
543 new_references.clrp1_id
544 )
545 ) THEN
546 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
547 igs_ge_msg_stack.add;
548 app_exception.raise_exception;
549 END IF;
550
551 ELSIF p_action IN ('DELETE','VALIDATE_DELETE') THEN
552 check_child_existance;
553 END IF;
554
555 END before_dml;
556
557
558 PROCEDURE insert_row (
559 x_rowid IN OUT NOCOPY VARCHAR2,
560 x_clrp1_id IN NUMBER,
561 x_loan_number IN VARCHAR2,
562 x_fed_stafford_loan_debt IN NUMBER,
563 x_fed_sls_debt IN NUMBER,
564 x_heal_debt IN NUMBER,
565 x_perkins_debt IN NUMBER,
566 x_other_debt IN NUMBER,
567 x_crdt_undr_difft_name IN VARCHAR2,
568 x_borw_gross_annual_sal IN NUMBER,
569 x_borw_other_income IN NUMBER,
570 x_student_major IN VARCHAR2,
571 x_int_rate_opt IN VARCHAR2,
572 x_repayment_opt_code IN VARCHAR2,
573 x_stud_mth_housing_pymt IN NUMBER,
574 x_stud_mth_crdtcard_pymt IN NUMBER,
575 x_stud_mth_auto_pymt IN NUMBER,
576 x_stud_mth_ed_loan_pymt IN NUMBER,
577 x_stud_mth_other_pymt IN NUMBER,
578 x_cosnr_1_last_name IN VARCHAR2,
579 x_cosnr_1_first_name IN VARCHAR2,
580 x_cosnr_1_middle_name IN VARCHAR2,
581 x_cosnr_1_ssn IN VARCHAR2,
582 x_cosnr_1_citizenship IN VARCHAR2,
583 x_cosnr_1_addr_line1 IN VARCHAR2,
584 x_cosnr_1_addr_line2 IN VARCHAR2,
585 x_cosnr_1_city IN VARCHAR2,
586 x_cosnr_1_state IN VARCHAR2,
587 x_cosnr_1_zip IN VARCHAR2,
588 x_cosnr_1_zip_suffix IN VARCHAR2,
589 x_cosnr_1_phone IN VARCHAR2,
590 x_cosnr_1_sig_code IN VARCHAR2,
591 x_cosnr_1_gross_anl_sal IN NUMBER,
592 x_cosnr_1_other_income IN NUMBER,
593 x_cosnr_1_forn_postal_code IN VARCHAR2,
594 x_cosnr_1_forn_phone_prefix IN VARCHAR2,
595 x_cosnr_1_dob IN DATE,
596 x_cosnr_1_license_state IN VARCHAR2,
597 x_cosnr_1_license_num IN VARCHAR2,
598 x_cosnr_1_relationship_to IN VARCHAR2,
599 x_cosnr_1_years_at_addr IN VARCHAR2,
600 x_cosnr_1_mth_housing_pymt IN NUMBER,
601 x_cosnr_1_mth_crdtcard_pymt IN NUMBER,
602 x_cosnr_1_mth_auto_pymt IN NUMBER,
603 x_cosnr_1_mth_ed_loan_pymt IN NUMBER,
604 x_cosnr_1_mth_other_pymt IN NUMBER,
605 x_cosnr_1_crdt_auth_code IN VARCHAR2,
606 x_cosnr_2_last_name IN VARCHAR2,
607 x_cosnr_2_first_name IN VARCHAR2,
608 x_cosnr_2_middle_name IN VARCHAR2,
609 x_cosnr_2_ssn IN VARCHAR2,
610 x_cosnr_2_citizenship IN VARCHAR2,
611 x_cosnr_2_addr_line1 IN VARCHAR2,
612 x_cosnr_2_addr_line2 IN VARCHAR2,
613 x_cosnr_2_city IN VARCHAR2,
614 x_cosnr_2_state IN VARCHAR2,
615 x_cosnr_2_zip IN VARCHAR2,
616 x_cosnr_2_zip_suffix IN VARCHAR2,
617 x_cosnr_2_phone IN VARCHAR2,
618 x_cosnr_2_sig_code IN VARCHAR2,
619 x_cosnr_2_gross_anl_sal IN NUMBER,
620 x_cosnr_2_other_income IN NUMBER,
621 x_cosnr_2_forn_postal_code IN VARCHAR2,
622 x_cosnr_2_forn_phone_prefix IN VARCHAR2,
623 x_cosnr_2_dob IN DATE,
624 x_cosnr_2_license_state IN VARCHAR2,
625 x_cosnr_2_license_num IN VARCHAR2,
626 x_cosnr_2_relationship_to IN VARCHAR2,
627 x_cosnr_2_years_at_addr IN VARCHAR2,
628 x_cosnr_2_mth_housing_pymt IN NUMBER,
629 x_cosnr_2_mth_crdtcard_pymt IN NUMBER,
630 x_cosnr_2_mth_auto_pymt IN NUMBER,
631 x_cosnr_2_mth_ed_loan_pymt IN NUMBER,
632 x_cosnr_2_mth_other_pymt IN NUMBER,
633 x_cosnr_2_crdt_auth_code IN VARCHAR2,
634 x_other_loan_amt IN NUMBER ,
635 x_alt_layout_owner_code_txt IN VARCHAR2,
636 x_alt_layout_identi_code_txt IN VARCHAR2,
637 x_student_school_phone_txt IN VARCHAR2,
638 x_first_csgnr_elec_sign_flag IN VARCHAR2,
639 x_second_csgnr_elec_sign_flag IN VARCHAR2,
640 x_mode IN VARCHAR2 DEFAULT 'R'
641 ) AS
642 /*
643 || Created By : viramali
644 || Created On : 10-MAY-2001
645 || Purpose : Handles the INSERT DML logic for the table.
646 || Known limitations, enhancements or remarks :
647 || Change History :
648 || Who When What
649 || (reverse chronological order - newest change first)
650 */
651 CURSOR c IS
652 SELECT rowid
653 FROM igf_sl_cl_resp_r4_all
654 WHERE clrp1_id = x_clrp1_id;
655
656 x_last_update_date DATE;
657 x_last_updated_by NUMBER;
658 x_last_update_login NUMBER;
659 x_request_id NUMBER;
660 x_program_id NUMBER;
661 x_program_application_id NUMBER;
662 x_program_update_date DATE;
663
664 BEGIN
665
666 x_last_update_date := SYSDATE;
667 IF (x_mode = 'I') THEN
668 x_last_updated_by := 1;
669 x_last_update_login := 0;
670 ELSIF (x_mode = 'R') THEN
671 x_last_updated_by := fnd_global.user_id;
672 IF (x_last_updated_by IS NULL) THEN
673 x_last_updated_by := -1;
674 END IF;
675 x_last_update_login := fnd_global.login_id;
676 IF (x_last_update_login IS NULL) THEN
677 x_last_update_login := -1;
678 END IF;
679 x_request_id := fnd_global.conc_request_id;
680 x_program_id := fnd_global.conc_program_id;
681 x_program_application_id := fnd_global.prog_appl_id;
682
683 IF (x_request_id = -1) THEN
684 x_request_id := NULL;
685 x_program_id := NULL;
686 x_program_application_id := NULL;
687 x_program_update_date := NULL;
688 ELSE
689 x_program_update_date := SYSDATE;
690 END IF;
691 ELSE
692 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
693 igs_ge_msg_stack.add;
694 app_exception.raise_exception;
695 END IF;
696
697 new_references.org_id := igs_ge_gen_003.get_org_id;
698
699 before_dml(
700 p_action => 'INSERT',
701 x_rowid => x_rowid,
702 x_clrp1_id => x_clrp1_id,
703 x_loan_number => x_loan_number,
704 x_fed_stafford_loan_debt => x_fed_stafford_loan_debt,
705 x_fed_sls_debt => x_fed_sls_debt,
706 x_heal_debt => x_heal_debt,
707 x_perkins_debt => x_perkins_debt,
708 x_other_debt => x_other_debt,
709 x_crdt_undr_difft_name => x_crdt_undr_difft_name,
710 x_borw_gross_annual_sal => x_borw_gross_annual_sal,
711 x_borw_other_income => x_borw_other_income,
712 x_student_major => x_student_major,
713 x_int_rate_opt => x_int_rate_opt,
714 x_repayment_opt_code => x_repayment_opt_code,
715 x_stud_mth_housing_pymt => x_stud_mth_housing_pymt,
716 x_stud_mth_crdtcard_pymt => x_stud_mth_crdtcard_pymt,
717 x_stud_mth_auto_pymt => x_stud_mth_auto_pymt,
718 x_stud_mth_ed_loan_pymt => x_stud_mth_ed_loan_pymt,
719 x_stud_mth_other_pymt => x_stud_mth_other_pymt,
720 x_cosnr_1_last_name => x_cosnr_1_last_name,
721 x_cosnr_1_first_name => x_cosnr_1_first_name,
722 x_cosnr_1_middle_name => x_cosnr_1_middle_name,
723 x_cosnr_1_ssn => x_cosnr_1_ssn,
724 x_cosnr_1_citizenship => x_cosnr_1_citizenship,
725 x_cosnr_1_addr_line1 => x_cosnr_1_addr_line1,
726 x_cosnr_1_addr_line2 => x_cosnr_1_addr_line2,
727 x_cosnr_1_city => x_cosnr_1_city,
728 x_cosnr_1_state => x_cosnr_1_state,
729 x_cosnr_1_zip => x_cosnr_1_zip,
730 x_cosnr_1_zip_suffix => x_cosnr_1_zip_suffix,
731 x_cosnr_1_phone => x_cosnr_1_phone,
732 x_cosnr_1_sig_code => x_cosnr_1_sig_code,
733 x_cosnr_1_gross_anl_sal => x_cosnr_1_gross_anl_sal,
734 x_cosnr_1_other_income => x_cosnr_1_other_income,
735 x_cosnr_1_forn_postal_code => x_cosnr_1_forn_postal_code,
736 x_cosnr_1_forn_phone_prefix => x_cosnr_1_forn_phone_prefix,
737 x_cosnr_1_dob => x_cosnr_1_dob,
738 x_cosnr_1_license_state => x_cosnr_1_license_state,
739 x_cosnr_1_license_num => x_cosnr_1_license_num,
740 x_cosnr_1_relationship_to => x_cosnr_1_relationship_to,
741 x_cosnr_1_years_at_addr => x_cosnr_1_years_at_addr,
742 x_cosnr_1_mth_housing_pymt => x_cosnr_1_mth_housing_pymt,
743 x_cosnr_1_mth_crdtcard_pymt => x_cosnr_1_mth_crdtcard_pymt,
744 x_cosnr_1_mth_auto_pymt => x_cosnr_1_mth_auto_pymt,
745 x_cosnr_1_mth_ed_loan_pymt => x_cosnr_1_mth_ed_loan_pymt,
746 x_cosnr_1_mth_other_pymt => x_cosnr_1_mth_other_pymt,
747 x_cosnr_1_crdt_auth_code => x_cosnr_1_crdt_auth_code,
748 x_cosnr_2_last_name => x_cosnr_2_last_name,
749 x_cosnr_2_first_name => x_cosnr_2_first_name,
750 x_cosnr_2_middle_name => x_cosnr_2_middle_name,
751 x_cosnr_2_ssn => x_cosnr_2_ssn,
752 x_cosnr_2_citizenship => x_cosnr_2_citizenship,
753 x_cosnr_2_addr_line1 => x_cosnr_2_addr_line1,
754 x_cosnr_2_addr_line2 => x_cosnr_2_addr_line2,
755 x_cosnr_2_city => x_cosnr_2_city,
756 x_cosnr_2_state => x_cosnr_2_state,
757 x_cosnr_2_zip => x_cosnr_2_zip,
758 x_cosnr_2_zip_suffix => x_cosnr_2_zip_suffix,
759 x_cosnr_2_phone => x_cosnr_2_phone,
760 x_cosnr_2_sig_code => x_cosnr_2_sig_code,
761 x_cosnr_2_gross_anl_sal => x_cosnr_2_gross_anl_sal,
762 x_cosnr_2_other_income => x_cosnr_2_other_income,
763 x_cosnr_2_forn_postal_code => x_cosnr_2_forn_postal_code,
764 x_cosnr_2_forn_phone_prefix => x_cosnr_2_forn_phone_prefix,
765 x_cosnr_2_dob => x_cosnr_2_dob,
766 x_cosnr_2_license_state => x_cosnr_2_license_state,
767 x_cosnr_2_license_num => x_cosnr_2_license_num,
768 x_cosnr_2_relationship_to => x_cosnr_2_relationship_to,
769 x_cosnr_2_years_at_addr => x_cosnr_2_years_at_addr,
770 x_cosnr_2_mth_housing_pymt => x_cosnr_2_mth_housing_pymt,
771 x_cosnr_2_mth_crdtcard_pymt => x_cosnr_2_mth_crdtcard_pymt,
772 x_cosnr_2_mth_auto_pymt => x_cosnr_2_mth_auto_pymt,
773 x_cosnr_2_mth_ed_loan_pymt => x_cosnr_2_mth_ed_loan_pymt,
774 x_cosnr_2_mth_other_pymt => x_cosnr_2_mth_other_pymt,
775 x_cosnr_2_crdt_auth_code => x_cosnr_2_crdt_auth_code,
776 x_other_loan_amt => x_other_loan_amt,
777 x_alt_layout_owner_code_txt => x_alt_layout_owner_code_txt,
778 x_alt_layout_identi_code_txt => x_alt_layout_identi_code_txt,
779 x_student_school_phone_txt => x_student_school_phone_txt,
780 x_first_csgnr_elec_sign_flag => x_first_csgnr_elec_sign_flag,
781 x_second_csgnr_elec_sign_flag => x_second_csgnr_elec_sign_flag,
782
783 x_creation_date => x_last_update_date,
784 x_created_by => x_last_updated_by,
785 x_last_update_date => x_last_update_date,
786 x_last_updated_by => x_last_updated_by,
787 x_last_update_login => x_last_update_login
788 );
789
790 INSERT INTO igf_sl_cl_resp_r4_all (
791 clrp1_id,
792 loan_number,
793 fed_stafford_loan_debt,
794 fed_sls_debt,
795 heal_debt,
796 perkins_debt,
797 other_debt,
798 crdt_undr_difft_name,
799 borw_gross_annual_sal,
800 borw_other_income,
801 student_major,
802 int_rate_opt,
803 repayment_opt_code,
804 stud_mth_housing_pymt,
805 stud_mth_crdtcard_pymt,
806 stud_mth_auto_pymt,
807 stud_mth_ed_loan_pymt,
808 stud_mth_other_pymt,
809 cosnr_1_last_name,
810 cosnr_1_first_name,
811 cosnr_1_middle_name,
812 cosnr_1_ssn,
813 cosnr_1_citizenship,
814 cosnr_1_addr_line1,
815 cosnr_1_addr_line2,
816 cosnr_1_city,
817 cosnr_1_state,
818 cosnr_1_zip,
819 cosnr_1_zip_suffix,
820 cosnr_1_phone,
821 cosnr_1_sig_code,
822 cosnr_1_gross_anl_sal,
823 cosnr_1_other_income,
824 cosnr_1_forn_postal_code,
825 cosnr_1_forn_phone_prefix,
826 cosnr_1_dob,
827 cosnr_1_license_state,
828 cosnr_1_license_num,
829 cosnr_1_relationship_to,
830 cosnr_1_years_at_addr,
831 cosnr_1_mth_housing_pymt,
832 cosnr_1_mth_crdtcard_pymt,
833 cosnr_1_mth_auto_pymt,
834 cosnr_1_mth_ed_loan_pymt,
835 cosnr_1_mth_other_pymt,
836 cosnr_1_crdt_auth_code,
837 cosnr_2_last_name,
838 cosnr_2_first_name,
839 cosnr_2_middle_name,
840 cosnr_2_ssn,
841 cosnr_2_citizenship,
842 cosnr_2_addr_line1,
843 cosnr_2_addr_line2,
844 cosnr_2_city,
845 cosnr_2_state,
846 cosnr_2_zip,
847 cosnr_2_zip_suffix,
848 cosnr_2_phone,
849 cosnr_2_sig_code,
850 cosnr_2_gross_anl_sal,
851 cosnr_2_other_income,
852 cosnr_2_forn_postal_code,
853 cosnr_2_forn_phone_prefix,
854 cosnr_2_dob,
855 cosnr_2_license_state,
856 cosnr_2_license_num,
857 cosnr_2_relationship_to,
858 cosnr_2_years_at_addr,
859 cosnr_2_mth_housing_pymt,
860 cosnr_2_mth_crdtcard_pymt,
861 cosnr_2_mth_auto_pymt,
862 cosnr_2_mth_ed_loan_pymt,
863 cosnr_2_mth_other_pymt,
864 cosnr_2_crdt_auth_code,
865 other_loan_amt,
866 alt_layout_owner_code_txt,
867 alt_layout_identi_code_txt,
868 student_school_phone_txt,
869 first_csgnr_elec_sign_flag,
870 second_csgnr_elec_sign_flag,
871 org_id,
872 creation_date,
873 created_by,
874 last_update_date,
875 last_updated_by,
876 last_update_login,
877 request_id,
878 program_id,
879 program_application_id,
880 program_update_date
881 ) VALUES (
882 new_references.clrp1_id,
883 new_references.loan_number,
884 new_references.fed_stafford_loan_debt,
885 new_references.fed_sls_debt,
886 new_references.heal_debt,
887 new_references.perkins_debt,
888 new_references.other_debt,
889 new_references.crdt_undr_difft_name,
890 new_references.borw_gross_annual_sal,
891 new_references.borw_other_income,
892 new_references.student_major,
893 new_references.int_rate_opt,
894 new_references.repayment_opt_code,
895 new_references.stud_mth_housing_pymt,
896 new_references.stud_mth_crdtcard_pymt,
897 new_references.stud_mth_auto_pymt,
898 new_references.stud_mth_ed_loan_pymt,
899 new_references.stud_mth_other_pymt,
900 new_references.cosnr_1_last_name,
901 new_references.cosnr_1_first_name,
902 new_references.cosnr_1_middle_name,
903 new_references.cosnr_1_ssn,
904 new_references.cosnr_1_citizenship,
905 new_references.cosnr_1_addr_line1,
906 new_references.cosnr_1_addr_line2,
907 new_references.cosnr_1_city,
908 new_references.cosnr_1_state,
909 new_references.cosnr_1_zip,
910 new_references.cosnr_1_zip_suffix,
911 new_references.cosnr_1_phone,
912 new_references.cosnr_1_sig_code,
913 new_references.cosnr_1_gross_anl_sal,
914 new_references.cosnr_1_other_income,
915 new_references.cosnr_1_forn_postal_code,
916 new_references.cosnr_1_forn_phone_prefix,
917 new_references.cosnr_1_dob,
918 new_references.cosnr_1_license_state,
919 new_references.cosnr_1_license_num,
920 new_references.cosnr_1_relationship_to,
921 new_references.cosnr_1_years_at_addr,
922 new_references.cosnr_1_mth_housing_pymt,
923 new_references.cosnr_1_mth_crdtcard_pymt,
924 new_references.cosnr_1_mth_auto_pymt,
925 new_references.cosnr_1_mth_ed_loan_pymt,
926 new_references.cosnr_1_mth_other_pymt,
927 new_references.cosnr_1_crdt_auth_code,
928 new_references.cosnr_2_last_name,
929 new_references.cosnr_2_first_name,
930 new_references.cosnr_2_middle_name,
931 new_references.cosnr_2_ssn,
932 new_references.cosnr_2_citizenship,
933 new_references.cosnr_2_addr_line1,
934 new_references.cosnr_2_addr_line2,
935 new_references.cosnr_2_city,
936 new_references.cosnr_2_state,
937 new_references.cosnr_2_zip,
938 new_references.cosnr_2_zip_suffix,
939 new_references.cosnr_2_phone,
940 new_references.cosnr_2_sig_code,
941 new_references.cosnr_2_gross_anl_sal,
942 new_references.cosnr_2_other_income,
943 new_references.cosnr_2_forn_postal_code,
944 new_references.cosnr_2_forn_phone_prefix,
945 new_references.cosnr_2_dob,
946 new_references.cosnr_2_license_state,
947 new_references.cosnr_2_license_num,
948 new_references.cosnr_2_relationship_to,
949 new_references.cosnr_2_years_at_addr,
950 new_references.cosnr_2_mth_housing_pymt,
951 new_references.cosnr_2_mth_crdtcard_pymt,
952 new_references.cosnr_2_mth_auto_pymt,
953 new_references.cosnr_2_mth_ed_loan_pymt,
954 new_references.cosnr_2_mth_other_pymt,
955 new_references.cosnr_2_crdt_auth_code,
956 new_references.other_loan_amt,
957 new_references.alt_layout_owner_code_txt,
958 new_references.alt_layout_identi_code_txt,
959 new_references.student_school_phone_txt,
960 new_references.first_csgnr_elec_sign_flag,
961 new_references.second_csgnr_elec_sign_flag,
962 new_references.org_id,
963 x_last_update_date,
964 x_last_updated_by,
965 x_last_update_date,
966 x_last_updated_by,
967 x_last_update_login ,
968 x_request_id,
969 x_program_id,
970 x_program_application_id,
971 x_program_update_date
972 );
973
974 OPEN c;
975 FETCH c INTO x_rowid;
976 IF (c%NOTFOUND) THEN
977 CLOSE c;
978 RAISE NO_DATA_FOUND;
979 END IF;
980 CLOSE c;
981
982 END insert_row;
983
984
985 PROCEDURE lock_row (
986 x_rowid IN VARCHAR2,
987 x_clrp1_id IN NUMBER,
988 x_loan_number IN VARCHAR2,
989 x_fed_stafford_loan_debt IN NUMBER,
990 x_fed_sls_debt IN NUMBER,
991 x_heal_debt IN NUMBER,
992 x_perkins_debt IN NUMBER,
993 x_other_debt IN NUMBER,
994 x_crdt_undr_difft_name IN VARCHAR2,
995 x_borw_gross_annual_sal IN NUMBER,
996 x_borw_other_income IN NUMBER,
997 x_student_major IN VARCHAR2,
998 x_int_rate_opt IN VARCHAR2,
999 x_repayment_opt_code IN VARCHAR2,
1000 x_stud_mth_housing_pymt IN NUMBER,
1001 x_stud_mth_crdtcard_pymt IN NUMBER,
1002 x_stud_mth_auto_pymt IN NUMBER,
1003 x_stud_mth_ed_loan_pymt IN NUMBER,
1004 x_stud_mth_other_pymt IN NUMBER,
1005 x_cosnr_1_last_name IN VARCHAR2,
1006 x_cosnr_1_first_name IN VARCHAR2,
1007 x_cosnr_1_middle_name IN VARCHAR2,
1008 x_cosnr_1_ssn IN VARCHAR2,
1009 x_cosnr_1_citizenship IN VARCHAR2,
1010 x_cosnr_1_addr_line1 IN VARCHAR2,
1011 x_cosnr_1_addr_line2 IN VARCHAR2,
1012 x_cosnr_1_city IN VARCHAR2,
1013 x_cosnr_1_state IN VARCHAR2,
1014 x_cosnr_1_zip IN VARCHAR2,
1015 x_cosnr_1_zip_suffix IN VARCHAR2,
1016 x_cosnr_1_phone IN VARCHAR2,
1017 x_cosnr_1_sig_code IN VARCHAR2,
1018 x_cosnr_1_gross_anl_sal IN NUMBER,
1019 x_cosnr_1_other_income IN NUMBER,
1020 x_cosnr_1_forn_postal_code IN VARCHAR2,
1021 x_cosnr_1_forn_phone_prefix IN VARCHAR2,
1022 x_cosnr_1_dob IN DATE,
1023 x_cosnr_1_license_state IN VARCHAR2,
1024 x_cosnr_1_license_num IN VARCHAR2,
1025 x_cosnr_1_relationship_to IN VARCHAR2,
1026 x_cosnr_1_years_at_addr IN VARCHAR2,
1027 x_cosnr_1_mth_housing_pymt IN NUMBER,
1028 x_cosnr_1_mth_crdtcard_pymt IN NUMBER,
1029 x_cosnr_1_mth_auto_pymt IN NUMBER,
1030 x_cosnr_1_mth_ed_loan_pymt IN NUMBER,
1031 x_cosnr_1_mth_other_pymt IN NUMBER,
1032 x_cosnr_1_crdt_auth_code IN VARCHAR2,
1033 x_cosnr_2_last_name IN VARCHAR2,
1034 x_cosnr_2_first_name IN VARCHAR2,
1035 x_cosnr_2_middle_name IN VARCHAR2,
1036 x_cosnr_2_ssn IN VARCHAR2,
1037 x_cosnr_2_citizenship IN VARCHAR2,
1038 x_cosnr_2_addr_line1 IN VARCHAR2,
1039 x_cosnr_2_addr_line2 IN VARCHAR2,
1040 x_cosnr_2_city IN VARCHAR2,
1041 x_cosnr_2_state IN VARCHAR2,
1042 x_cosnr_2_zip IN VARCHAR2,
1043 x_cosnr_2_zip_suffix IN VARCHAR2,
1044 x_cosnr_2_phone IN VARCHAR2,
1045 x_cosnr_2_sig_code IN VARCHAR2,
1046 x_cosnr_2_gross_anl_sal IN NUMBER,
1047 x_cosnr_2_other_income IN NUMBER,
1048 x_cosnr_2_forn_postal_code IN VARCHAR2,
1049 x_cosnr_2_forn_phone_prefix IN VARCHAR2,
1050 x_cosnr_2_dob IN DATE,
1051 x_cosnr_2_license_state IN VARCHAR2,
1052 x_cosnr_2_license_num IN VARCHAR2,
1053 x_cosnr_2_relationship_to IN VARCHAR2,
1054 x_cosnr_2_years_at_addr IN VARCHAR2,
1055 x_cosnr_2_mth_housing_pymt IN NUMBER,
1056 x_cosnr_2_mth_crdtcard_pymt IN NUMBER,
1057 x_cosnr_2_mth_auto_pymt IN NUMBER,
1058 x_cosnr_2_mth_ed_loan_pymt IN NUMBER,
1059 x_cosnr_2_mth_other_pymt IN NUMBER,
1060 x_cosnr_2_crdt_auth_code IN VARCHAR2,
1061 x_other_loan_amt IN NUMBER ,
1062 x_alt_layout_owner_code_txt IN VARCHAR2,
1063 x_alt_layout_identi_code_txt IN VARCHAR2,
1064 x_student_school_phone_txt IN VARCHAR2,
1065 x_first_csgnr_elec_sign_flag IN VARCHAR2,
1066 x_second_csgnr_elec_sign_flag IN VARCHAR2
1067 ) AS
1068 /*
1069 || Created By : viramali
1070 || Created On : 10-MAY-2001
1071 || Purpose : Handles the LOCK mechanism for the table.
1072 || Known limitations, enhancements or remarks :
1073 || Change History :
1074 || Who When What
1075 || (reverse chronological order - newest change first)
1076 */
1077 CURSOR c1 IS
1078 SELECT
1079 loan_number,
1080 fed_stafford_loan_debt,
1081 fed_sls_debt,
1082 heal_debt,
1083 perkins_debt,
1084 other_debt,
1085 crdt_undr_difft_name,
1086 borw_gross_annual_sal,
1087 borw_other_income,
1088 student_major,
1089 int_rate_opt,
1090 repayment_opt_code,
1091 stud_mth_housing_pymt,
1092 stud_mth_crdtcard_pymt,
1093 stud_mth_auto_pymt,
1094 stud_mth_ed_loan_pymt,
1095 stud_mth_other_pymt,
1096 cosnr_1_last_name,
1097 cosnr_1_first_name,
1098 cosnr_1_middle_name,
1099 cosnr_1_ssn,
1100 cosnr_1_citizenship,
1101 cosnr_1_addr_line1,
1102 cosnr_1_addr_line2,
1103 cosnr_1_city,
1104 cosnr_1_state,
1105 cosnr_1_zip,
1106 cosnr_1_zip_suffix,
1107 cosnr_1_phone,
1108 cosnr_1_sig_code,
1109 cosnr_1_gross_anl_sal,
1110 cosnr_1_other_income,
1111 cosnr_1_forn_postal_code,
1112 cosnr_1_forn_phone_prefix,
1113 cosnr_1_dob,
1114 cosnr_1_license_state,
1115 cosnr_1_license_num,
1116 cosnr_1_relationship_to,
1117 cosnr_1_years_at_addr,
1118 cosnr_1_mth_housing_pymt,
1119 cosnr_1_mth_crdtcard_pymt,
1120 cosnr_1_mth_auto_pymt,
1121 cosnr_1_mth_ed_loan_pymt,
1122 cosnr_1_mth_other_pymt,
1123 cosnr_1_crdt_auth_code,
1124 cosnr_2_last_name,
1125 cosnr_2_first_name,
1126 cosnr_2_middle_name,
1127 cosnr_2_ssn,
1128 cosnr_2_citizenship,
1129 cosnr_2_addr_line1,
1130 cosnr_2_addr_line2,
1131 cosnr_2_city,
1132 cosnr_2_state,
1133 cosnr_2_zip,
1134 cosnr_2_zip_suffix,
1135 cosnr_2_phone,
1136 cosnr_2_sig_code,
1137 cosnr_2_gross_anl_sal,
1138 cosnr_2_other_income,
1139 cosnr_2_forn_postal_code,
1140 cosnr_2_forn_phone_prefix,
1141 cosnr_2_dob,
1142 cosnr_2_license_state,
1143 cosnr_2_license_num,
1144 cosnr_2_relationship_to,
1145 cosnr_2_years_at_addr,
1146 cosnr_2_mth_housing_pymt,
1147 cosnr_2_mth_crdtcard_pymt,
1148 cosnr_2_mth_auto_pymt,
1149 cosnr_2_mth_ed_loan_pymt,
1150 cosnr_2_mth_other_pymt,
1151 cosnr_2_crdt_auth_code,
1152 other_loan_amt,
1153 alt_layout_owner_code_txt,
1154 alt_layout_identi_code_txt,
1155 student_school_phone_txt,
1156 first_csgnr_elec_sign_flag,
1157 second_csgnr_elec_sign_flag
1158
1159 FROM igf_sl_cl_resp_r4_all
1160 WHERE rowid = x_rowid
1161 FOR UPDATE NOWAIT;
1162
1163 tlinfo c1%ROWTYPE;
1164
1165 BEGIN
1166
1167 OPEN c1;
1168 FETCH c1 INTO tlinfo;
1169 IF (c1%notfound) THEN
1170 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1171 igs_ge_msg_stack.add;
1172 CLOSE c1;
1173 app_exception.raise_exception;
1174 RETURN;
1175 END IF;
1176 CLOSE c1;
1177
1178 IF (
1179 (tlinfo.loan_number = x_loan_number)
1180 AND ((tlinfo.fed_stafford_loan_debt = x_fed_stafford_loan_debt) OR ((tlinfo.fed_stafford_loan_debt IS NULL) AND (X_fed_stafford_loan_debt IS NULL)))
1181 AND ((tlinfo.fed_sls_debt = x_fed_sls_debt) OR ((tlinfo.fed_sls_debt IS NULL) AND (X_fed_sls_debt IS NULL)))
1182 AND ((tlinfo.heal_debt = x_heal_debt) OR ((tlinfo.heal_debt IS NULL) AND (X_heal_debt IS NULL)))
1183 AND ((tlinfo.perkins_debt = x_perkins_debt) OR ((tlinfo.perkins_debt IS NULL) AND (X_perkins_debt IS NULL)))
1184 AND ((tlinfo.other_debt = x_other_debt) OR ((tlinfo.other_debt IS NULL) AND (X_other_debt IS NULL)))
1185 AND ((tlinfo.crdt_undr_difft_name = x_crdt_undr_difft_name) OR ((tlinfo.crdt_undr_difft_name IS NULL) AND (X_crdt_undr_difft_name IS NULL)))
1186 AND ((tlinfo.borw_gross_annual_sal = x_borw_gross_annual_sal) OR ((tlinfo.borw_gross_annual_sal IS NULL) AND (X_borw_gross_annual_sal IS NULL)))
1187 AND ((tlinfo.borw_other_income = x_borw_other_income) OR ((tlinfo.borw_other_income IS NULL) AND (X_borw_other_income IS NULL)))
1188 AND ((tlinfo.student_major = x_student_major) OR ((tlinfo.student_major IS NULL) AND (X_student_major IS NULL)))
1189 AND ((tlinfo.int_rate_opt = x_int_rate_opt) OR ((tlinfo.int_rate_opt IS NULL) AND (X_int_rate_opt IS NULL)))
1190 AND ((tlinfo.repayment_opt_code = x_repayment_opt_code) OR ((tlinfo.repayment_opt_code IS NULL) AND (X_repayment_opt_code IS NULL)))
1191 AND ((tlinfo.stud_mth_housing_pymt = x_stud_mth_housing_pymt) OR ((tlinfo.stud_mth_housing_pymt IS NULL) AND (X_stud_mth_housing_pymt IS NULL)))
1192 AND ((tlinfo.stud_mth_crdtcard_pymt = x_stud_mth_crdtcard_pymt) OR ((tlinfo.stud_mth_crdtcard_pymt IS NULL) AND (X_stud_mth_crdtcard_pymt IS NULL)))
1193 AND ((tlinfo.stud_mth_auto_pymt = x_stud_mth_auto_pymt) OR ((tlinfo.stud_mth_auto_pymt IS NULL) AND (X_stud_mth_auto_pymt IS NULL)))
1194 AND ((tlinfo.stud_mth_ed_loan_pymt = x_stud_mth_ed_loan_pymt) OR ((tlinfo.stud_mth_ed_loan_pymt IS NULL) AND (X_stud_mth_ed_loan_pymt IS NULL)))
1195 AND ((tlinfo.stud_mth_other_pymt = x_stud_mth_other_pymt) OR ((tlinfo.stud_mth_other_pymt IS NULL) AND (X_stud_mth_other_pymt IS NULL)))
1196 AND ((tlinfo.cosnr_1_last_name = x_cosnr_1_last_name) OR ((tlinfo.cosnr_1_last_name IS NULL) AND (X_cosnr_1_last_name IS NULL)))
1197 AND ((tlinfo.cosnr_1_first_name = x_cosnr_1_first_name) OR ((tlinfo.cosnr_1_first_name IS NULL) AND (X_cosnr_1_first_name IS NULL)))
1198 AND ((tlinfo.cosnr_1_middle_name = x_cosnr_1_middle_name) OR ((tlinfo.cosnr_1_middle_name IS NULL) AND (X_cosnr_1_middle_name IS NULL)))
1199 AND ((tlinfo.cosnr_1_ssn = x_cosnr_1_ssn) OR ((tlinfo.cosnr_1_ssn IS NULL) AND (X_cosnr_1_ssn IS NULL)))
1200 AND ((tlinfo.cosnr_1_citizenship = x_cosnr_1_citizenship) OR ((tlinfo.cosnr_1_citizenship IS NULL) AND (X_cosnr_1_citizenship IS NULL)))
1201 AND ((tlinfo.cosnr_1_addr_line1 = x_cosnr_1_addr_line1) OR ((tlinfo.cosnr_1_addr_line1 IS NULL) AND (X_cosnr_1_addr_line1 IS NULL)))
1202 AND ((tlinfo.cosnr_1_addr_line2 = x_cosnr_1_addr_line2) OR ((tlinfo.cosnr_1_addr_line2 IS NULL) AND (X_cosnr_1_addr_line2 IS NULL)))
1203 AND ((tlinfo.cosnr_1_city = x_cosnr_1_city) OR ((tlinfo.cosnr_1_city IS NULL) AND (X_cosnr_1_city IS NULL)))
1204 AND ((tlinfo.cosnr_1_state = x_cosnr_1_state) OR ((tlinfo.cosnr_1_state IS NULL) AND (X_cosnr_1_state IS NULL)))
1205 AND ((tlinfo.cosnr_1_zip = x_cosnr_1_zip) OR ((tlinfo.cosnr_1_zip IS NULL) AND (X_cosnr_1_zip IS NULL)))
1206 AND ((tlinfo.cosnr_1_zip_suffix = x_cosnr_1_zip_suffix) OR ((tlinfo.cosnr_1_zip_suffix IS NULL) AND (X_cosnr_1_zip_suffix IS NULL)))
1207 AND ((tlinfo.cosnr_1_phone = x_cosnr_1_phone) OR ((tlinfo.cosnr_1_phone IS NULL) AND (X_cosnr_1_phone IS NULL)))
1208 AND ((tlinfo.cosnr_1_sig_code = x_cosnr_1_sig_code) OR ((tlinfo.cosnr_1_sig_code IS NULL) AND (X_cosnr_1_sig_code IS NULL)))
1209 AND ((tlinfo.cosnr_1_gross_anl_sal = x_cosnr_1_gross_anl_sal) OR ((tlinfo.cosnr_1_gross_anl_sal IS NULL) AND (X_cosnr_1_gross_anl_sal IS NULL)))
1210 AND ((tlinfo.cosnr_1_other_income = x_cosnr_1_other_income) OR ((tlinfo.cosnr_1_other_income IS NULL) AND (X_cosnr_1_other_income IS NULL)))
1211 AND ((tlinfo.cosnr_1_forn_postal_code = x_cosnr_1_forn_postal_code) OR ((tlinfo.cosnr_1_forn_postal_code IS NULL) AND (X_cosnr_1_forn_postal_code IS NULL)))
1212 AND ((tlinfo.cosnr_1_forn_phone_prefix = x_cosnr_1_forn_phone_prefix) OR ((tlinfo.cosnr_1_forn_phone_prefix IS NULL) AND (X_cosnr_1_forn_phone_prefix IS NULL)))
1213 AND ((tlinfo.cosnr_1_dob = x_cosnr_1_dob) OR ((tlinfo.cosnr_1_dob IS NULL) AND (X_cosnr_1_dob IS NULL)))
1214 AND ((tlinfo.cosnr_1_license_state = x_cosnr_1_license_state) OR ((tlinfo.cosnr_1_license_state IS NULL) AND (X_cosnr_1_license_state IS NULL)))
1215 AND ((tlinfo.cosnr_1_license_num = x_cosnr_1_license_num) OR ((tlinfo.cosnr_1_license_num IS NULL) AND (X_cosnr_1_license_num IS NULL)))
1216 AND ((tlinfo.cosnr_1_relationship_to = x_cosnr_1_relationship_to) OR ((tlinfo.cosnr_1_relationship_to IS NULL) AND (X_cosnr_1_relationship_to IS NULL)))
1217 AND ((tlinfo.cosnr_1_years_at_addr = x_cosnr_1_years_at_addr) OR ((tlinfo.cosnr_1_years_at_addr IS NULL) AND (X_cosnr_1_years_at_addr IS NULL)))
1218 AND ((tlinfo.cosnr_1_mth_housing_pymt = x_cosnr_1_mth_housing_pymt) OR ((tlinfo.cosnr_1_mth_housing_pymt IS NULL) AND (X_cosnr_1_mth_housing_pymt IS NULL)))
1219 AND ((tlinfo.cosnr_1_mth_crdtcard_pymt = x_cosnr_1_mth_crdtcard_pymt) OR ((tlinfo.cosnr_1_mth_crdtcard_pymt IS NULL) AND (X_cosnr_1_mth_crdtcard_pymt IS NULL)))
1220 AND ((tlinfo.cosnr_1_mth_auto_pymt = x_cosnr_1_mth_auto_pymt) OR ((tlinfo.cosnr_1_mth_auto_pymt IS NULL) AND (X_cosnr_1_mth_auto_pymt IS NULL)))
1221 AND ((tlinfo.cosnr_1_mth_ed_loan_pymt = x_cosnr_1_mth_ed_loan_pymt) OR ((tlinfo.cosnr_1_mth_ed_loan_pymt IS NULL) AND (X_cosnr_1_mth_ed_loan_pymt IS NULL)))
1222 AND ((tlinfo.cosnr_1_mth_other_pymt = x_cosnr_1_mth_other_pymt) OR ((tlinfo.cosnr_1_mth_other_pymt IS NULL) AND (X_cosnr_1_mth_other_pymt IS NULL)))
1223 AND ((tlinfo.cosnr_1_crdt_auth_code = x_cosnr_1_crdt_auth_code) OR ((tlinfo.cosnr_1_crdt_auth_code IS NULL) AND (X_cosnr_1_crdt_auth_code IS NULL)))
1224 AND ((tlinfo.cosnr_2_last_name = x_cosnr_2_last_name) OR ((tlinfo.cosnr_2_last_name IS NULL) AND (X_cosnr_2_last_name IS NULL)))
1225 AND ((tlinfo.cosnr_2_first_name = x_cosnr_2_first_name) OR ((tlinfo.cosnr_2_first_name IS NULL) AND (X_cosnr_2_first_name IS NULL)))
1226 AND ((tlinfo.cosnr_2_middle_name = x_cosnr_2_middle_name) OR ((tlinfo.cosnr_2_middle_name IS NULL) AND (X_cosnr_2_middle_name IS NULL)))
1227 AND ((tlinfo.cosnr_2_ssn = x_cosnr_2_ssn) OR ((tlinfo.cosnr_2_ssn IS NULL) AND (X_cosnr_2_ssn IS NULL)))
1228 AND ((tlinfo.cosnr_2_citizenship = x_cosnr_2_citizenship) OR ((tlinfo.cosnr_2_citizenship IS NULL) AND (X_cosnr_2_citizenship IS NULL)))
1229 AND ((tlinfo.cosnr_2_addr_line1 = x_cosnr_2_addr_line1) OR ((tlinfo.cosnr_2_addr_line1 IS NULL) AND (X_cosnr_2_addr_line1 IS NULL)))
1230 AND ((tlinfo.cosnr_2_addr_line2 = x_cosnr_2_addr_line2) OR ((tlinfo.cosnr_2_addr_line2 IS NULL) AND (X_cosnr_2_addr_line2 IS NULL)))
1231 AND ((tlinfo.cosnr_2_city = x_cosnr_2_city) OR ((tlinfo.cosnr_2_city IS NULL) AND (X_cosnr_2_city IS NULL)))
1232 AND ((tlinfo.cosnr_2_state = x_cosnr_2_state) OR ((tlinfo.cosnr_2_state IS NULL) AND (X_cosnr_2_state IS NULL)))
1233 AND ((tlinfo.cosnr_2_zip = x_cosnr_2_zip) OR ((tlinfo.cosnr_2_zip IS NULL) AND (X_cosnr_2_zip IS NULL)))
1234 AND ((tlinfo.cosnr_2_zip_suffix = x_cosnr_2_zip_suffix) OR ((tlinfo.cosnr_2_zip_suffix IS NULL) AND (X_cosnr_2_zip_suffix IS NULL)))
1235 AND ((tlinfo.cosnr_2_phone = x_cosnr_2_phone) OR ((tlinfo.cosnr_2_phone IS NULL) AND (X_cosnr_2_phone IS NULL)))
1236 AND ((tlinfo.cosnr_2_sig_code = x_cosnr_2_sig_code) OR ((tlinfo.cosnr_2_sig_code IS NULL) AND (X_cosnr_2_sig_code IS NULL)))
1237 AND ((tlinfo.cosnr_2_gross_anl_sal = x_cosnr_2_gross_anl_sal) OR ((tlinfo.cosnr_2_gross_anl_sal IS NULL) AND (X_cosnr_2_gross_anl_sal IS NULL)))
1238 AND ((tlinfo.cosnr_2_other_income = x_cosnr_2_other_income) OR ((tlinfo.cosnr_2_other_income IS NULL) AND (X_cosnr_2_other_income IS NULL)))
1239 AND ((tlinfo.cosnr_2_forn_postal_code = x_cosnr_2_forn_postal_code) OR ((tlinfo.cosnr_2_forn_postal_code IS NULL) AND (X_cosnr_2_forn_postal_code IS NULL)))
1240 AND ((tlinfo.cosnr_2_forn_phone_prefix = x_cosnr_2_forn_phone_prefix) OR ((tlinfo.cosnr_2_forn_phone_prefix IS NULL) AND (X_cosnr_2_forn_phone_prefix IS NULL)))
1241 AND ((tlinfo.cosnr_2_dob = x_cosnr_2_dob) OR ((tlinfo.cosnr_2_dob IS NULL) AND (X_cosnr_2_dob IS NULL)))
1242 AND ((tlinfo.cosnr_2_license_state = x_cosnr_2_license_state) OR ((tlinfo.cosnr_2_license_state IS NULL) AND (X_cosnr_2_license_state IS NULL)))
1243 AND ((tlinfo.cosnr_2_license_num = x_cosnr_2_license_num) OR ((tlinfo.cosnr_2_license_num IS NULL) AND (X_cosnr_2_license_num IS NULL)))
1244 AND ((tlinfo.cosnr_2_relationship_to = x_cosnr_2_relationship_to) OR ((tlinfo.cosnr_2_relationship_to IS NULL) AND (X_cosnr_2_relationship_to IS NULL)))
1245 AND ((tlinfo.cosnr_2_years_at_addr = x_cosnr_2_years_at_addr) OR ((tlinfo.cosnr_2_years_at_addr IS NULL) AND (X_cosnr_2_years_at_addr IS NULL)))
1246 AND ((tlinfo.cosnr_2_mth_housing_pymt = x_cosnr_2_mth_housing_pymt) OR ((tlinfo.cosnr_2_mth_housing_pymt IS NULL) AND (X_cosnr_2_mth_housing_pymt IS NULL)))
1247 AND ((tlinfo.cosnr_2_mth_crdtcard_pymt = x_cosnr_2_mth_crdtcard_pymt) OR ((tlinfo.cosnr_2_mth_crdtcard_pymt IS NULL) AND (X_cosnr_2_mth_crdtcard_pymt IS NULL)))
1248 AND ((tlinfo.cosnr_2_mth_auto_pymt = x_cosnr_2_mth_auto_pymt) OR ((tlinfo.cosnr_2_mth_auto_pymt IS NULL) AND (X_cosnr_2_mth_auto_pymt IS NULL)))
1249 AND ((tlinfo.cosnr_2_mth_ed_loan_pymt = x_cosnr_2_mth_ed_loan_pymt) OR ((tlinfo.cosnr_2_mth_ed_loan_pymt IS NULL) AND (X_cosnr_2_mth_ed_loan_pymt IS NULL)))
1250 AND ((tlinfo.cosnr_2_mth_other_pymt = x_cosnr_2_mth_other_pymt) OR ((tlinfo.cosnr_2_mth_other_pymt IS NULL) AND (X_cosnr_2_mth_other_pymt IS NULL)))
1251 AND ((tlinfo.cosnr_2_crdt_auth_code = x_cosnr_2_crdt_auth_code) OR ((tlinfo.cosnr_2_crdt_auth_code IS NULL) AND (X_cosnr_2_crdt_auth_code IS NULL)))
1252 AND ((tlinfo.other_loan_amt = x_other_loan_amt) OR ((tlinfo.other_loan_amt IS NULL) AND (X_other_loan_amt IS NULL)))
1253 AND ((tlinfo.alt_layout_owner_code_txt = x_alt_layout_owner_code_txt) OR ((tlinfo.alt_layout_owner_code_txt IS NULL) AND (X_alt_layout_owner_code_txt IS NULL)))
1254 AND ((tlinfo.student_school_phone_txt = x_student_school_phone_txt) OR ((tlinfo.student_school_phone_txt IS NULL) AND (X_student_school_phone_txt IS NULL)))
1255 AND ((tlinfo.first_csgnr_elec_sign_flag = x_first_csgnr_elec_sign_flag) OR ((tlinfo.first_csgnr_elec_sign_flag IS NULL) AND (X_first_csgnr_elec_sign_flag IS NULL)))
1256 AND ((tlinfo.second_csgnr_elec_sign_flag = x_second_csgnr_elec_sign_flag) OR ((tlinfo.second_csgnr_elec_sign_flag IS NULL) AND (X_second_csgnr_elec_sign_flag IS NULL)))
1257
1258 ) THEN
1259 NULL;
1260 ELSE
1261 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1262 igs_ge_msg_stack.add;
1263 app_exception.raise_exception;
1264 END IF;
1265
1266 RETURN;
1267
1268 END lock_row;
1269
1270
1271 PROCEDURE update_row (
1272 x_rowid IN VARCHAR2,
1273 x_clrp1_id IN NUMBER,
1274 x_loan_number IN VARCHAR2,
1275 x_fed_stafford_loan_debt IN NUMBER,
1276 x_fed_sls_debt IN NUMBER,
1277 x_heal_debt IN NUMBER,
1278 x_perkins_debt IN NUMBER,
1279 x_other_debt IN NUMBER,
1280 x_crdt_undr_difft_name IN VARCHAR2,
1281 x_borw_gross_annual_sal IN NUMBER,
1282 x_borw_other_income IN NUMBER,
1283 x_student_major IN VARCHAR2,
1284 x_int_rate_opt IN VARCHAR2,
1285 x_repayment_opt_code IN VARCHAR2,
1286 x_stud_mth_housing_pymt IN NUMBER,
1287 x_stud_mth_crdtcard_pymt IN NUMBER,
1288 x_stud_mth_auto_pymt IN NUMBER,
1289 x_stud_mth_ed_loan_pymt IN NUMBER,
1290 x_stud_mth_other_pymt IN NUMBER,
1291 x_cosnr_1_last_name IN VARCHAR2,
1292 x_cosnr_1_first_name IN VARCHAR2,
1293 x_cosnr_1_middle_name IN VARCHAR2,
1294 x_cosnr_1_ssn IN VARCHAR2,
1295 x_cosnr_1_citizenship IN VARCHAR2,
1296 x_cosnr_1_addr_line1 IN VARCHAR2,
1297 x_cosnr_1_addr_line2 IN VARCHAR2,
1298 x_cosnr_1_city IN VARCHAR2,
1299 x_cosnr_1_state IN VARCHAR2,
1300 x_cosnr_1_zip IN VARCHAR2,
1301 x_cosnr_1_zip_suffix IN VARCHAR2,
1302 x_cosnr_1_phone IN VARCHAR2,
1303 x_cosnr_1_sig_code IN VARCHAR2,
1304 x_cosnr_1_gross_anl_sal IN NUMBER,
1305 x_cosnr_1_other_income IN NUMBER,
1306 x_cosnr_1_forn_postal_code IN VARCHAR2,
1307 x_cosnr_1_forn_phone_prefix IN VARCHAR2,
1308 x_cosnr_1_dob IN DATE,
1309 x_cosnr_1_license_state IN VARCHAR2,
1310 x_cosnr_1_license_num IN VARCHAR2,
1311 x_cosnr_1_relationship_to IN VARCHAR2,
1312 x_cosnr_1_years_at_addr IN VARCHAR2,
1313 x_cosnr_1_mth_housing_pymt IN NUMBER,
1314 x_cosnr_1_mth_crdtcard_pymt IN NUMBER,
1315 x_cosnr_1_mth_auto_pymt IN NUMBER,
1316 x_cosnr_1_mth_ed_loan_pymt IN NUMBER,
1317 x_cosnr_1_mth_other_pymt IN NUMBER,
1318 x_cosnr_1_crdt_auth_code IN VARCHAR2,
1319 x_cosnr_2_last_name IN VARCHAR2,
1320 x_cosnr_2_first_name IN VARCHAR2,
1321 x_cosnr_2_middle_name IN VARCHAR2,
1322 x_cosnr_2_ssn IN VARCHAR2,
1323 x_cosnr_2_citizenship IN VARCHAR2,
1324 x_cosnr_2_addr_line1 IN VARCHAR2,
1325 x_cosnr_2_addr_line2 IN VARCHAR2,
1326 x_cosnr_2_city IN VARCHAR2,
1327 x_cosnr_2_state IN VARCHAR2,
1328 x_cosnr_2_zip IN VARCHAR2,
1329 x_cosnr_2_zip_suffix IN VARCHAR2,
1330 x_cosnr_2_phone IN VARCHAR2,
1331 x_cosnr_2_sig_code IN VARCHAR2,
1332 x_cosnr_2_gross_anl_sal IN NUMBER,
1333 x_cosnr_2_other_income IN NUMBER,
1334 x_cosnr_2_forn_postal_code IN VARCHAR2,
1335 x_cosnr_2_forn_phone_prefix IN VARCHAR2,
1336 x_cosnr_2_dob IN DATE,
1337 x_cosnr_2_license_state IN VARCHAR2,
1338 x_cosnr_2_license_num IN VARCHAR2,
1339 x_cosnr_2_relationship_to IN VARCHAR2,
1340 x_cosnr_2_years_at_addr IN VARCHAR2,
1341 x_cosnr_2_mth_housing_pymt IN NUMBER,
1342 x_cosnr_2_mth_crdtcard_pymt IN NUMBER,
1343 x_cosnr_2_mth_auto_pymt IN NUMBER,
1344 x_cosnr_2_mth_ed_loan_pymt IN NUMBER,
1345 x_cosnr_2_mth_other_pymt IN NUMBER,
1346 x_cosnr_2_crdt_auth_code IN VARCHAR2,
1347 x_other_loan_amt IN NUMBER ,
1348 x_alt_layout_owner_code_txt IN VARCHAR2 ,
1349 x_alt_layout_identi_code_txt IN VARCHAR2 ,
1350 x_student_school_phone_txt IN VARCHAR2 ,
1351 x_first_csgnr_elec_sign_flag IN VARCHAR2 ,
1352 x_second_csgnr_elec_sign_flag IN VARCHAR2 ,
1353 x_mode IN VARCHAR2 DEFAULT 'R'
1354 ) AS
1355 /*
1356 || Created By : viramali
1357 || Created On : 10-MAY-2001
1358 || Purpose : Handles the UPDATE DML logic for the table.
1359 || Known limitations, enhancements or remarks :
1360 || Change History :
1361 || Who When What
1362 || (reverse chronological order - newest change first)
1363 */
1364 x_last_update_date DATE ;
1365 x_last_updated_by NUMBER;
1366 x_last_update_login NUMBER;
1367 x_request_id NUMBER;
1368 x_program_id NUMBER;
1369 x_program_application_id NUMBER;
1370 x_program_update_date DATE;
1371
1372 BEGIN
1373
1374 x_last_update_date := SYSDATE;
1375 IF (X_MODE = 'I') THEN
1376 x_last_updated_by := 1;
1377 x_last_update_login := 0;
1378 ELSIF (x_mode = 'R') THEN
1379 x_last_updated_by := fnd_global.user_id;
1380 IF x_last_updated_by IS NULL THEN
1381 x_last_updated_by := -1;
1382 END IF;
1383 x_last_update_login := fnd_global.login_id;
1384 IF (x_last_update_login IS NULL) THEN
1385 x_last_update_login := -1;
1386 END IF;
1387 ELSE
1388 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1389 igs_ge_msg_stack.add;
1390 app_exception.raise_exception;
1391 END IF;
1392
1393 before_dml(
1394 p_action => 'UPDATE',
1395 x_rowid => x_rowid,
1396 x_clrp1_id => x_clrp1_id,
1397 x_loan_number => x_loan_number,
1398 x_fed_stafford_loan_debt => x_fed_stafford_loan_debt,
1399 x_fed_sls_debt => x_fed_sls_debt,
1400 x_heal_debt => x_heal_debt,
1401 x_perkins_debt => x_perkins_debt,
1402 x_other_debt => x_other_debt,
1403 x_crdt_undr_difft_name => x_crdt_undr_difft_name,
1404 x_borw_gross_annual_sal => x_borw_gross_annual_sal,
1405 x_borw_other_income => x_borw_other_income,
1406 x_student_major => x_student_major,
1407 x_int_rate_opt => x_int_rate_opt,
1408 x_repayment_opt_code => x_repayment_opt_code,
1409 x_stud_mth_housing_pymt => x_stud_mth_housing_pymt,
1410 x_stud_mth_crdtcard_pymt => x_stud_mth_crdtcard_pymt,
1411 x_stud_mth_auto_pymt => x_stud_mth_auto_pymt,
1412 x_stud_mth_ed_loan_pymt => x_stud_mth_ed_loan_pymt,
1413 x_stud_mth_other_pymt => x_stud_mth_other_pymt,
1414 x_cosnr_1_last_name => x_cosnr_1_last_name,
1415 x_cosnr_1_first_name => x_cosnr_1_first_name,
1416 x_cosnr_1_middle_name => x_cosnr_1_middle_name,
1417 x_cosnr_1_ssn => x_cosnr_1_ssn,
1418 x_cosnr_1_citizenship => x_cosnr_1_citizenship,
1419 x_cosnr_1_addr_line1 => x_cosnr_1_addr_line1,
1420 x_cosnr_1_addr_line2 => x_cosnr_1_addr_line2,
1421 x_cosnr_1_city => x_cosnr_1_city,
1422 x_cosnr_1_state => x_cosnr_1_state,
1423 x_cosnr_1_zip => x_cosnr_1_zip,
1424 x_cosnr_1_zip_suffix => x_cosnr_1_zip_suffix,
1425 x_cosnr_1_phone => x_cosnr_1_phone,
1426 x_cosnr_1_sig_code => x_cosnr_1_sig_code,
1427 x_cosnr_1_gross_anl_sal => x_cosnr_1_gross_anl_sal,
1428 x_cosnr_1_other_income => x_cosnr_1_other_income,
1429 x_cosnr_1_forn_postal_code => x_cosnr_1_forn_postal_code,
1430 x_cosnr_1_forn_phone_prefix => x_cosnr_1_forn_phone_prefix,
1431 x_cosnr_1_dob => x_cosnr_1_dob,
1432 x_cosnr_1_license_state => x_cosnr_1_license_state,
1433 x_cosnr_1_license_num => x_cosnr_1_license_num,
1434 x_cosnr_1_relationship_to => x_cosnr_1_relationship_to,
1435 x_cosnr_1_years_at_addr => x_cosnr_1_years_at_addr,
1436 x_cosnr_1_mth_housing_pymt => x_cosnr_1_mth_housing_pymt,
1437 x_cosnr_1_mth_crdtcard_pymt => x_cosnr_1_mth_crdtcard_pymt,
1438 x_cosnr_1_mth_auto_pymt => x_cosnr_1_mth_auto_pymt,
1439 x_cosnr_1_mth_ed_loan_pymt => x_cosnr_1_mth_ed_loan_pymt,
1440 x_cosnr_1_mth_other_pymt => x_cosnr_1_mth_other_pymt,
1441 x_cosnr_1_crdt_auth_code => x_cosnr_1_crdt_auth_code,
1442 x_cosnr_2_last_name => x_cosnr_2_last_name,
1443 x_cosnr_2_first_name => x_cosnr_2_first_name,
1444 x_cosnr_2_middle_name => x_cosnr_2_middle_name,
1445 x_cosnr_2_ssn => x_cosnr_2_ssn,
1446 x_cosnr_2_citizenship => x_cosnr_2_citizenship,
1447 x_cosnr_2_addr_line1 => x_cosnr_2_addr_line1,
1448 x_cosnr_2_addr_line2 => x_cosnr_2_addr_line2,
1449 x_cosnr_2_city => x_cosnr_2_city,
1450 x_cosnr_2_state => x_cosnr_2_state,
1451 x_cosnr_2_zip => x_cosnr_2_zip,
1452 x_cosnr_2_zip_suffix => x_cosnr_2_zip_suffix,
1453 x_cosnr_2_phone => x_cosnr_2_phone,
1454 x_cosnr_2_sig_code => x_cosnr_2_sig_code,
1455 x_cosnr_2_gross_anl_sal => x_cosnr_2_gross_anl_sal,
1456 x_cosnr_2_other_income => x_cosnr_2_other_income,
1457 x_cosnr_2_forn_postal_code => x_cosnr_2_forn_postal_code,
1458 x_cosnr_2_forn_phone_prefix => x_cosnr_2_forn_phone_prefix,
1459 x_cosnr_2_dob => x_cosnr_2_dob,
1460 x_cosnr_2_license_state => x_cosnr_2_license_state,
1461 x_cosnr_2_license_num => x_cosnr_2_license_num,
1462 x_cosnr_2_relationship_to => x_cosnr_2_relationship_to,
1463 x_cosnr_2_years_at_addr => x_cosnr_2_years_at_addr,
1464 x_cosnr_2_mth_housing_pymt => x_cosnr_2_mth_housing_pymt,
1465 x_cosnr_2_mth_crdtcard_pymt => x_cosnr_2_mth_crdtcard_pymt,
1466 x_cosnr_2_mth_auto_pymt => x_cosnr_2_mth_auto_pymt,
1467 x_cosnr_2_mth_ed_loan_pymt => x_cosnr_2_mth_ed_loan_pymt,
1468 x_cosnr_2_mth_other_pymt => x_cosnr_2_mth_other_pymt,
1469 x_cosnr_2_crdt_auth_code => x_cosnr_2_crdt_auth_code,
1470 x_other_loan_amt => x_other_loan_amt,
1471 x_alt_layout_owner_code_txt => x_alt_layout_owner_code_txt,
1472 x_alt_layout_identi_code_txt => x_alt_layout_identi_code_txt,
1473 x_student_school_phone_txt => x_student_school_phone_txt,
1474 x_first_csgnr_elec_sign_flag => x_first_csgnr_elec_sign_flag,
1475 x_second_csgnr_elec_sign_flag => x_second_csgnr_elec_sign_flag,
1476
1477 x_creation_date => x_last_update_date,
1478 x_created_by => x_last_updated_by,
1479 x_last_update_date => x_last_update_date,
1480 x_last_updated_by => x_last_updated_by,
1481 x_last_update_login => x_last_update_login
1482 );
1483
1484 IF (x_mode = 'R') THEN
1485 x_request_id := fnd_global.conc_request_id;
1486 x_program_id := fnd_global.conc_program_id;
1487 x_program_application_id := fnd_global.prog_appl_id;
1488 IF (x_request_id = -1) THEN
1489 x_request_id := old_references.request_id;
1490 x_program_id := old_references.program_id;
1491 x_program_application_id := old_references.program_application_id;
1492 x_program_update_date := old_references.program_update_date;
1493 ELSE
1494 x_program_update_date := SYSDATE;
1495 END IF;
1496 END IF;
1497
1498 UPDATE igf_sl_cl_resp_r4_all
1499 SET
1500 loan_number = new_references.loan_number,
1501 fed_stafford_loan_debt = new_references.fed_stafford_loan_debt,
1502 fed_sls_debt = new_references.fed_sls_debt,
1503 heal_debt = new_references.heal_debt,
1504 perkins_debt = new_references.perkins_debt,
1505 other_debt = new_references.other_debt,
1506 crdt_undr_difft_name = new_references.crdt_undr_difft_name,
1507 borw_gross_annual_sal = new_references.borw_gross_annual_sal,
1508 borw_other_income = new_references.borw_other_income,
1509 student_major = new_references.student_major,
1510 int_rate_opt = new_references.int_rate_opt,
1511 repayment_opt_code = new_references.repayment_opt_code,
1512 stud_mth_housing_pymt = new_references.stud_mth_housing_pymt,
1513 stud_mth_crdtcard_pymt = new_references.stud_mth_crdtcard_pymt,
1514 stud_mth_auto_pymt = new_references.stud_mth_auto_pymt,
1515 stud_mth_ed_loan_pymt = new_references.stud_mth_ed_loan_pymt,
1516 stud_mth_other_pymt = new_references.stud_mth_other_pymt,
1517 cosnr_1_last_name = new_references.cosnr_1_last_name,
1518 cosnr_1_first_name = new_references.cosnr_1_first_name,
1519 cosnr_1_middle_name = new_references.cosnr_1_middle_name,
1520 cosnr_1_ssn = new_references.cosnr_1_ssn,
1521 cosnr_1_citizenship = new_references.cosnr_1_citizenship,
1522 cosnr_1_addr_line1 = new_references.cosnr_1_addr_line1,
1523 cosnr_1_addr_line2 = new_references.cosnr_1_addr_line2,
1524 cosnr_1_city = new_references.cosnr_1_city,
1525 cosnr_1_state = new_references.cosnr_1_state,
1526 cosnr_1_zip = new_references.cosnr_1_zip,
1527 cosnr_1_zip_suffix = new_references.cosnr_1_zip_suffix,
1528 cosnr_1_phone = new_references.cosnr_1_phone,
1529 cosnr_1_sig_code = new_references.cosnr_1_sig_code,
1530 cosnr_1_gross_anl_sal = new_references.cosnr_1_gross_anl_sal,
1531 cosnr_1_other_income = new_references.cosnr_1_other_income,
1532 cosnr_1_forn_postal_code = new_references.cosnr_1_forn_postal_code,
1533 cosnr_1_forn_phone_prefix = new_references.cosnr_1_forn_phone_prefix,
1534 cosnr_1_dob = new_references.cosnr_1_dob,
1535 cosnr_1_license_state = new_references.cosnr_1_license_state,
1536 cosnr_1_license_num = new_references.cosnr_1_license_num,
1537 cosnr_1_relationship_to = new_references.cosnr_1_relationship_to,
1538 cosnr_1_years_at_addr = new_references.cosnr_1_years_at_addr,
1539 cosnr_1_mth_housing_pymt = new_references.cosnr_1_mth_housing_pymt,
1540 cosnr_1_mth_crdtcard_pymt = new_references.cosnr_1_mth_crdtcard_pymt,
1541 cosnr_1_mth_auto_pymt = new_references.cosnr_1_mth_auto_pymt,
1542 cosnr_1_mth_ed_loan_pymt = new_references.cosnr_1_mth_ed_loan_pymt,
1543 cosnr_1_mth_other_pymt = new_references.cosnr_1_mth_other_pymt,
1544 cosnr_1_crdt_auth_code = new_references.cosnr_1_crdt_auth_code,
1545 cosnr_2_last_name = new_references.cosnr_2_last_name,
1546 cosnr_2_first_name = new_references.cosnr_2_first_name,
1547 cosnr_2_middle_name = new_references.cosnr_2_middle_name,
1548 cosnr_2_ssn = new_references.cosnr_2_ssn,
1549 cosnr_2_citizenship = new_references.cosnr_2_citizenship,
1550 cosnr_2_addr_line1 = new_references.cosnr_2_addr_line1,
1551 cosnr_2_addr_line2 = new_references.cosnr_2_addr_line2,
1552 cosnr_2_city = new_references.cosnr_2_city,
1553 cosnr_2_state = new_references.cosnr_2_state,
1554 cosnr_2_zip = new_references.cosnr_2_zip,
1555 cosnr_2_zip_suffix = new_references.cosnr_2_zip_suffix,
1556 cosnr_2_phone = new_references.cosnr_2_phone,
1557 cosnr_2_sig_code = new_references.cosnr_2_sig_code,
1558 cosnr_2_gross_anl_sal = new_references.cosnr_2_gross_anl_sal,
1559 cosnr_2_other_income = new_references.cosnr_2_other_income,
1560 cosnr_2_forn_postal_code = new_references.cosnr_2_forn_postal_code,
1561 cosnr_2_forn_phone_prefix = new_references.cosnr_2_forn_phone_prefix,
1562 cosnr_2_dob = new_references.cosnr_2_dob,
1563 cosnr_2_license_state = new_references.cosnr_2_license_state,
1564 cosnr_2_license_num = new_references.cosnr_2_license_num,
1565 cosnr_2_relationship_to = new_references.cosnr_2_relationship_to,
1566 cosnr_2_years_at_addr = new_references.cosnr_2_years_at_addr,
1567 cosnr_2_mth_housing_pymt = new_references.cosnr_2_mth_housing_pymt,
1568 cosnr_2_mth_crdtcard_pymt = new_references.cosnr_2_mth_crdtcard_pymt,
1569 cosnr_2_mth_auto_pymt = new_references.cosnr_2_mth_auto_pymt,
1570 cosnr_2_mth_ed_loan_pymt = new_references.cosnr_2_mth_ed_loan_pymt,
1571 cosnr_2_mth_other_pymt = new_references.cosnr_2_mth_other_pymt,
1572 cosnr_2_crdt_auth_code = new_references.cosnr_2_crdt_auth_code,
1573
1574 other_loan_amt = new_references.other_loan_amt,
1575 alt_layout_owner_code_txt = new_references.alt_layout_owner_code_txt,
1576 alt_layout_identi_code_txt = new_references.alt_layout_identi_code_txt,
1577 student_school_phone_txt = new_references.student_school_phone_txt,
1578 first_csgnr_elec_sign_flag = new_references.first_csgnr_elec_sign_flag,
1579 second_csgnr_elec_sign_flag = new_references.second_csgnr_elec_sign_flag,
1580
1581 last_update_date = x_last_update_date,
1582 last_updated_by = x_last_updated_by,
1583 last_update_login = x_last_update_login ,
1584 request_id = x_request_id,
1585 program_id = x_program_id,
1586 program_application_id = x_program_application_id,
1587 program_update_date = x_program_update_date
1588 WHERE rowid = x_rowid;
1589
1590 IF (SQL%NOTFOUND) THEN
1591 RAISE NO_DATA_FOUND;
1592 END IF;
1593
1594 END update_row;
1595
1596
1597 PROCEDURE add_row (
1598 x_rowid IN OUT NOCOPY VARCHAR2,
1599 x_clrp1_id IN NUMBER,
1600 x_loan_number IN VARCHAR2,
1601 x_fed_stafford_loan_debt IN NUMBER,
1602 x_fed_sls_debt IN NUMBER,
1603 x_heal_debt IN NUMBER,
1604 x_perkins_debt IN NUMBER,
1605 x_other_debt IN NUMBER,
1606 x_crdt_undr_difft_name IN VARCHAR2,
1607 x_borw_gross_annual_sal IN NUMBER,
1608 x_borw_other_income IN NUMBER,
1609 x_student_major IN VARCHAR2,
1610 x_int_rate_opt IN VARCHAR2,
1611 x_repayment_opt_code IN VARCHAR2,
1612 x_stud_mth_housing_pymt IN NUMBER,
1613 x_stud_mth_crdtcard_pymt IN NUMBER,
1614 x_stud_mth_auto_pymt IN NUMBER,
1615 x_stud_mth_ed_loan_pymt IN NUMBER,
1616 x_stud_mth_other_pymt IN NUMBER,
1617 x_cosnr_1_last_name IN VARCHAR2,
1618 x_cosnr_1_first_name IN VARCHAR2,
1619 x_cosnr_1_middle_name IN VARCHAR2,
1620 x_cosnr_1_ssn IN VARCHAR2,
1621 x_cosnr_1_citizenship IN VARCHAR2,
1622 x_cosnr_1_addr_line1 IN VARCHAR2,
1623 x_cosnr_1_addr_line2 IN VARCHAR2,
1624 x_cosnr_1_city IN VARCHAR2,
1625 x_cosnr_1_state IN VARCHAR2,
1626 x_cosnr_1_zip IN VARCHAR2,
1627 x_cosnr_1_zip_suffix IN VARCHAR2,
1628 x_cosnr_1_phone IN VARCHAR2,
1629 x_cosnr_1_sig_code IN VARCHAR2,
1630 x_cosnr_1_gross_anl_sal IN NUMBER,
1631 x_cosnr_1_other_income IN NUMBER,
1632 x_cosnr_1_forn_postal_code IN VARCHAR2,
1633 x_cosnr_1_forn_phone_prefix IN VARCHAR2,
1634 x_cosnr_1_dob IN DATE,
1635 x_cosnr_1_license_state IN VARCHAR2,
1636 x_cosnr_1_license_num IN VARCHAR2,
1637 x_cosnr_1_relationship_to IN VARCHAR2,
1638 x_cosnr_1_years_at_addr IN VARCHAR2,
1639 x_cosnr_1_mth_housing_pymt IN NUMBER,
1640 x_cosnr_1_mth_crdtcard_pymt IN NUMBER,
1641 x_cosnr_1_mth_auto_pymt IN NUMBER,
1642 x_cosnr_1_mth_ed_loan_pymt IN NUMBER,
1643 x_cosnr_1_mth_other_pymt IN NUMBER,
1644 x_cosnr_1_crdt_auth_code IN VARCHAR2,
1645 x_cosnr_2_last_name IN VARCHAR2,
1646 x_cosnr_2_first_name IN VARCHAR2,
1647 x_cosnr_2_middle_name IN VARCHAR2,
1648 x_cosnr_2_ssn IN VARCHAR2,
1649 x_cosnr_2_citizenship IN VARCHAR2,
1650 x_cosnr_2_addr_line1 IN VARCHAR2,
1651 x_cosnr_2_addr_line2 IN VARCHAR2,
1652 x_cosnr_2_city IN VARCHAR2,
1653 x_cosnr_2_state IN VARCHAR2,
1654 x_cosnr_2_zip IN VARCHAR2,
1655 x_cosnr_2_zip_suffix IN VARCHAR2,
1656 x_cosnr_2_phone IN VARCHAR2,
1657 x_cosnr_2_sig_code IN VARCHAR2,
1658 x_cosnr_2_gross_anl_sal IN NUMBER,
1659 x_cosnr_2_other_income IN NUMBER,
1660 x_cosnr_2_forn_postal_code IN VARCHAR2,
1661 x_cosnr_2_forn_phone_prefix IN VARCHAR2,
1662 x_cosnr_2_dob IN DATE,
1663 x_cosnr_2_license_state IN VARCHAR2,
1664 x_cosnr_2_license_num IN VARCHAR2,
1665 x_cosnr_2_relationship_to IN VARCHAR2,
1666 x_cosnr_2_years_at_addr IN VARCHAR2,
1667 x_cosnr_2_mth_housing_pymt IN NUMBER,
1668 x_cosnr_2_mth_crdtcard_pymt IN NUMBER,
1669 x_cosnr_2_mth_auto_pymt IN NUMBER,
1670 x_cosnr_2_mth_ed_loan_pymt IN NUMBER,
1671 x_cosnr_2_mth_other_pymt IN NUMBER,
1672 x_cosnr_2_crdt_auth_code IN VARCHAR2,
1673 x_other_loan_amt IN NUMBER ,
1674 x_alt_layout_owner_code_txt IN VARCHAR2 ,
1675 x_alt_layout_identi_code_txt IN VARCHAR2 ,
1676 x_student_school_phone_txt IN VARCHAR2 ,
1677 x_first_csgnr_elec_sign_flag IN VARCHAR2 ,
1678 x_second_csgnr_elec_sign_flag IN VARCHAR2 ,
1679 x_mode IN VARCHAR2 DEFAULT 'R'
1680 ) AS
1681 /*
1682 || Created By : viramali
1683 || Created On : 10-MAY-2001
1684 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1685 || Known limitations, enhancements or remarks :
1686 || Change History :
1687 || Who When What
1688 || (reverse chronological order - newest change first)
1689 */
1690 CURSOR c1 IS
1691 SELECT rowid
1692 FROM igf_sl_cl_resp_r4_all
1693 WHERE clrp1_id = x_clrp1_id;
1694
1695 BEGIN
1696
1697 OPEN c1;
1698 FETCH c1 INTO x_rowid;
1699 IF (c1%NOTFOUND) THEN
1700 CLOSE c1;
1701
1702 insert_row (
1703 x_rowid,
1704 x_clrp1_id,
1705 x_loan_number,
1706 x_fed_stafford_loan_debt,
1707 x_fed_sls_debt,
1708 x_heal_debt,
1709 x_perkins_debt,
1710 x_other_debt,
1711 x_crdt_undr_difft_name,
1712 x_borw_gross_annual_sal,
1713 x_borw_other_income,
1714 x_student_major,
1715 x_int_rate_opt,
1716 x_repayment_opt_code,
1717 x_stud_mth_housing_pymt,
1718 x_stud_mth_crdtcard_pymt,
1719 x_stud_mth_auto_pymt,
1720 x_stud_mth_ed_loan_pymt,
1721 x_stud_mth_other_pymt,
1722 x_cosnr_1_last_name,
1723 x_cosnr_1_first_name,
1724 x_cosnr_1_middle_name,
1725 x_cosnr_1_ssn,
1726 x_cosnr_1_citizenship,
1727 x_cosnr_1_addr_line1,
1728 x_cosnr_1_addr_line2,
1729 x_cosnr_1_city,
1730 x_cosnr_1_state,
1731 x_cosnr_1_zip,
1732 x_cosnr_1_zip_suffix,
1733 x_cosnr_1_phone,
1734 x_cosnr_1_sig_code,
1735 x_cosnr_1_gross_anl_sal,
1736 x_cosnr_1_other_income,
1737 x_cosnr_1_forn_postal_code,
1738 x_cosnr_1_forn_phone_prefix,
1739 x_cosnr_1_dob,
1740 x_cosnr_1_license_state,
1741 x_cosnr_1_license_num,
1742 x_cosnr_1_relationship_to,
1743 x_cosnr_1_years_at_addr,
1744 x_cosnr_1_mth_housing_pymt,
1745 x_cosnr_1_mth_crdtcard_pymt,
1746 x_cosnr_1_mth_auto_pymt,
1747 x_cosnr_1_mth_ed_loan_pymt,
1748 x_cosnr_1_mth_other_pymt,
1749 x_cosnr_1_crdt_auth_code,
1750 x_cosnr_2_last_name,
1751 x_cosnr_2_first_name,
1752 x_cosnr_2_middle_name,
1753 x_cosnr_2_ssn,
1754 x_cosnr_2_citizenship,
1755 x_cosnr_2_addr_line1,
1756 x_cosnr_2_addr_line2,
1757 x_cosnr_2_city,
1758 x_cosnr_2_state,
1759 x_cosnr_2_zip,
1760 x_cosnr_2_zip_suffix,
1761 x_cosnr_2_phone,
1762 x_cosnr_2_sig_code,
1763 x_cosnr_2_gross_anl_sal,
1764 x_cosnr_2_other_income,
1765 x_cosnr_2_forn_postal_code,
1766 x_cosnr_2_forn_phone_prefix,
1767 x_cosnr_2_dob,
1768 x_cosnr_2_license_state,
1769 x_cosnr_2_license_num,
1770 x_cosnr_2_relationship_to,
1771 x_cosnr_2_years_at_addr,
1772 x_cosnr_2_mth_housing_pymt,
1773 x_cosnr_2_mth_crdtcard_pymt,
1774 x_cosnr_2_mth_auto_pymt,
1775 x_cosnr_2_mth_ed_loan_pymt,
1776 x_cosnr_2_mth_other_pymt,
1777 x_cosnr_2_crdt_auth_code,
1778 x_other_loan_amt,
1779 x_alt_layout_owner_code_txt,
1780 x_alt_layout_identi_code_txt,
1781 x_student_school_phone_txt ,
1782 x_first_csgnr_elec_sign_flag ,
1783 x_second_csgnr_elec_sign_flag,
1784 x_mode
1785 );
1786 RETURN;
1787 END IF;
1788 CLOSE c1;
1789
1790 update_row (
1791 x_rowid,
1792 x_clrp1_id,
1793 x_loan_number,
1794 x_fed_stafford_loan_debt,
1795 x_fed_sls_debt,
1796 x_heal_debt,
1797 x_perkins_debt,
1798 x_other_debt,
1799 x_crdt_undr_difft_name,
1800 x_borw_gross_annual_sal,
1801 x_borw_other_income,
1802 x_student_major,
1803 x_int_rate_opt,
1804 x_repayment_opt_code,
1805 x_stud_mth_housing_pymt,
1806 x_stud_mth_crdtcard_pymt,
1807 x_stud_mth_auto_pymt,
1808 x_stud_mth_ed_loan_pymt,
1809 x_stud_mth_other_pymt,
1810 x_cosnr_1_last_name,
1811 x_cosnr_1_first_name,
1812 x_cosnr_1_middle_name,
1813 x_cosnr_1_ssn,
1814 x_cosnr_1_citizenship,
1815 x_cosnr_1_addr_line1,
1816 x_cosnr_1_addr_line2,
1817 x_cosnr_1_city,
1818 x_cosnr_1_state,
1819 x_cosnr_1_zip,
1820 x_cosnr_1_zip_suffix,
1821 x_cosnr_1_phone,
1822 x_cosnr_1_sig_code,
1823 x_cosnr_1_gross_anl_sal,
1824 x_cosnr_1_other_income,
1825 x_cosnr_1_forn_postal_code,
1826 x_cosnr_1_forn_phone_prefix,
1827 x_cosnr_1_dob,
1828 x_cosnr_1_license_state,
1829 x_cosnr_1_license_num,
1830 x_cosnr_1_relationship_to,
1831 x_cosnr_1_years_at_addr,
1832 x_cosnr_1_mth_housing_pymt,
1833 x_cosnr_1_mth_crdtcard_pymt,
1834 x_cosnr_1_mth_auto_pymt,
1835 x_cosnr_1_mth_ed_loan_pymt,
1836 x_cosnr_1_mth_other_pymt,
1837 x_cosnr_1_crdt_auth_code,
1838 x_cosnr_2_last_name,
1839 x_cosnr_2_first_name,
1840 x_cosnr_2_middle_name,
1841 x_cosnr_2_ssn,
1842 x_cosnr_2_citizenship,
1843 x_cosnr_2_addr_line1,
1844 x_cosnr_2_addr_line2,
1845 x_cosnr_2_city,
1846 x_cosnr_2_state,
1847 x_cosnr_2_zip,
1848 x_cosnr_2_zip_suffix,
1849 x_cosnr_2_phone,
1850 x_cosnr_2_sig_code,
1851 x_cosnr_2_gross_anl_sal,
1852 x_cosnr_2_other_income,
1853 x_cosnr_2_forn_postal_code,
1854 x_cosnr_2_forn_phone_prefix,
1855 x_cosnr_2_dob,
1856 x_cosnr_2_license_state,
1857 x_cosnr_2_license_num,
1858 x_cosnr_2_relationship_to,
1859 x_cosnr_2_years_at_addr,
1860 x_cosnr_2_mth_housing_pymt,
1861 x_cosnr_2_mth_crdtcard_pymt,
1862 x_cosnr_2_mth_auto_pymt,
1863 x_cosnr_2_mth_ed_loan_pymt,
1864 x_cosnr_2_mth_other_pymt,
1865 x_cosnr_2_crdt_auth_code,
1866 x_other_loan_amt,
1867 x_alt_layout_owner_code_txt,
1868 x_alt_layout_identi_code_txt,
1869 x_student_school_phone_txt ,
1870 x_first_csgnr_elec_sign_flag ,
1871 x_second_csgnr_elec_sign_flag,
1872 x_mode
1873 );
1874
1875 END add_row;
1876
1877
1878 PROCEDURE delete_row (
1879 x_rowid IN VARCHAR2
1880 ) AS
1881 /*
1882 || Created By : viramali
1883 || Created On : 10-MAY-2001
1884 || Purpose : Handles the DELETE DML logic for the table.
1885 || Known limitations, enhancements or remarks :
1886 || Change History :
1887 || Who When What
1888 || (reverse chronological order - newest change first)
1889 */
1890 BEGIN
1891
1892 before_dml (
1893 p_action => 'DELETE',
1894 x_rowid => x_rowid
1895 );
1896
1897 DELETE FROM igf_sl_cl_resp_r4_all
1898 WHERE rowid = x_rowid;
1899
1900 IF (SQL%NOTFOUND) THEN
1901 RAISE NO_DATA_FOUND;
1902 END IF;
1903
1904 END delete_row;
1905
1906
1907 END igf_sl_cl_resp_r4_pkg;