1 PACKAGE igf_db_cl_disb_resp_pkg AS
2 /* $Header: IGFDI04S.pls 120.1 2006/08/08 06:28:14 ridas noship $ */
3
4 PROCEDURE insert_row (
5 x_rowid IN OUT NOCOPY VARCHAR2,
6 x_cdbr_id IN OUT NOCOPY NUMBER,
7 x_cbth_id IN NUMBER,
8 x_record_type IN VARCHAR2,
9 x_loan_number IN VARCHAR2,
10 x_cl_seq_number IN NUMBER,
11 x_b_last_name IN VARCHAR2,
12 x_b_first_name IN VARCHAR2,
13 x_b_middle_name IN VARCHAR2,
14 x_b_ssn IN VARCHAR2,
15 x_b_addr_line_1 IN VARCHAR2,
16 x_b_addr_line_2 IN VARCHAR2,
17 x_b_city IN VARCHAR2,
18 x_b_state IN VARCHAR2,
19 x_b_zip IN NUMBER,
20 x_b_zip_suffix IN NUMBER,
21 x_b_addr_chg_date IN DATE,
22 x_eft_auth_code IN VARCHAR2,
23 x_s_last_name IN VARCHAR2,
24 x_s_first_name IN VARCHAR2,
25 x_s_middle_initial IN VARCHAR2,
26 x_s_ssn IN VARCHAR2,
27 x_school_id IN NUMBER,
28 x_school_use IN VARCHAR2,
29 x_loan_per_start_date IN DATE,
30 x_loan_per_end_date IN DATE,
31 x_cl_loan_type IN VARCHAR2,
32 x_alt_prog_type_code IN VARCHAR2,
33 x_lender_id IN VARCHAR2,
34 x_lend_non_ed_brc_id IN VARCHAR2,
35 x_lender_use IN VARCHAR2,
36 x_borw_confirm_ind IN VARCHAR2,
37 x_tot_sched_disb IN NUMBER,
38 x_fund_release_date IN DATE,
39 x_disb_num IN NUMBER,
40 x_guarantor_id IN VARCHAR2,
41 x_guarantor_use IN VARCHAR2,
42 x_guarantee_date IN DATE,
43 x_guarantee_amt IN NUMBER,
44 x_gross_disb_amt IN NUMBER,
45 x_fee_1 IN NUMBER,
46 x_fee_2 IN NUMBER,
47 x_net_disb_amt IN NUMBER,
48 x_fund_dist_mthd IN VARCHAR2,
49 x_check_number IN VARCHAR2,
50 x_late_disb_ind IN VARCHAR2,
51 x_prev_reported_ind IN VARCHAR2,
52 x_err_code1 IN VARCHAR2,
53 x_err_code2 IN VARCHAR2,
54 x_err_code3 IN VARCHAR2,
55 x_err_code4 IN VARCHAR2,
56 x_err_code5 IN VARCHAR2,
57 x_fee_paid_2 IN NUMBER,
58 x_lender_name IN VARCHAR2,
59 x_net_cancel_amt IN NUMBER,
60 x_duns_lender_id IN VARCHAR2,
61 x_duns_guarnt_id IN VARCHAR2,
62 x_hold_rel_ind IN VARCHAR2,
63 x_pnote_code IN VARCHAR2,
64 x_pnote_status_date IN DATE,
65 x_fee_paid_1 IN NUMBER,
66 x_netted_cancel_amt IN NUMBER,
67 x_outstd_cancel_amt IN NUMBER,
68 x_sch_non_ed_brc_id IN VARCHAR2,
69 x_status IN VARCHAR2,
70 x_mode IN VARCHAR2 DEFAULT 'R' ,
71 x_esign_src_typ_cd IN VARCHAR2 DEFAULT NULL,
72 x_direct_to_borr_flag IN VARCHAR2 DEFAULT NULL
73 );
74
75 PROCEDURE lock_row (
76 x_rowid IN VARCHAR2,
77 x_cdbr_id IN NUMBER,
78 x_cbth_id IN NUMBER,
79 x_record_type IN VARCHAR2,
80 x_loan_number IN VARCHAR2,
81 x_cl_seq_number IN NUMBER,
82 x_b_last_name IN VARCHAR2,
83 x_b_first_name IN VARCHAR2,
84 x_b_middle_name IN VARCHAR2,
85 x_b_ssn IN VARCHAR2,
86 x_b_addr_line_1 IN VARCHAR2,
87 x_b_addr_line_2 IN VARCHAR2,
88 x_b_city IN VARCHAR2,
89 x_b_state IN VARCHAR2,
90 x_b_zip IN NUMBER,
91 x_b_zip_suffix IN NUMBER,
92 x_b_addr_chg_date IN DATE,
93 x_eft_auth_code IN VARCHAR2,
94 x_s_last_name IN VARCHAR2,
95 x_s_first_name IN VARCHAR2,
96 x_s_middle_initial IN VARCHAR2,
97 x_s_ssn IN VARCHAR2,
98 x_school_id IN NUMBER,
99 x_school_use IN VARCHAR2,
100 x_loan_per_start_date IN DATE,
101 x_loan_per_end_date IN DATE,
102 x_cl_loan_type IN VARCHAR2,
103 x_alt_prog_type_code IN VARCHAR2,
104 x_lender_id IN VARCHAR2,
105 x_lend_non_ed_brc_id IN VARCHAR2,
106 x_lender_use IN VARCHAR2,
107 x_borw_confirm_ind IN VARCHAR2,
108 x_tot_sched_disb IN NUMBER,
109 x_fund_release_date IN DATE,
110 x_disb_num IN NUMBER,
111 x_guarantor_id IN VARCHAR2,
112 x_guarantor_use IN VARCHAR2,
113 x_guarantee_date IN DATE,
114 x_guarantee_amt IN NUMBER,
115 x_gross_disb_amt IN NUMBER,
116 x_fee_1 IN NUMBER,
117 x_fee_2 IN NUMBER,
118 x_net_disb_amt IN NUMBER,
119 x_fund_dist_mthd IN VARCHAR2,
120 x_check_number IN VARCHAR2,
121 x_late_disb_ind IN VARCHAR2,
122 x_prev_reported_ind IN VARCHAR2,
123 x_err_code1 IN VARCHAR2,
124 x_err_code2 IN VARCHAR2,
125 x_err_code3 IN VARCHAR2,
126 x_err_code4 IN VARCHAR2,
127 x_err_code5 IN VARCHAR2,
128 x_fee_paid_2 IN NUMBER,
129 x_lender_name IN VARCHAR2,
130 x_net_cancel_amt IN NUMBER,
131 x_duns_lender_id IN VARCHAR2,
132 x_duns_guarnt_id IN VARCHAR2,
133 x_hold_rel_ind IN VARCHAR2,
134 x_pnote_code IN VARCHAR2,
135 x_pnote_status_date IN DATE,
136 x_fee_paid_1 IN NUMBER,
137 x_netted_cancel_amt IN NUMBER,
138 x_outstd_cancel_amt IN NUMBER,
139 x_sch_non_ed_brc_id IN VARCHAR2,
140 x_status IN VARCHAR2,
141 x_esign_src_typ_cd IN VARCHAR2 DEFAULT NULL,
142 x_direct_to_borr_flag IN VARCHAR2 DEFAULT NULL
143 );
144
145 PROCEDURE update_row (
146 x_rowid IN VARCHAR2,
147 x_cdbr_id IN NUMBER,
148 x_cbth_id IN NUMBER,
149 x_record_type IN VARCHAR2,
150 x_loan_number IN VARCHAR2,
151 x_cl_seq_number IN NUMBER,
152 x_b_last_name IN VARCHAR2,
153 x_b_first_name IN VARCHAR2,
154 x_b_middle_name IN VARCHAR2,
155 x_b_ssn IN VARCHAR2,
156 x_b_addr_line_1 IN VARCHAR2,
157 x_b_addr_line_2 IN VARCHAR2,
158 x_b_city IN VARCHAR2,
159 x_b_state IN VARCHAR2,
160 x_b_zip IN NUMBER,
161 x_b_zip_suffix IN NUMBER,
162 x_b_addr_chg_date IN DATE,
163 x_eft_auth_code IN VARCHAR2,
164 x_s_last_name IN VARCHAR2,
165 x_s_first_name IN VARCHAR2,
166 x_s_middle_initial IN VARCHAR2,
167 x_s_ssn IN VARCHAR2,
168 x_school_id IN NUMBER,
169 x_school_use IN VARCHAR2,
170 x_loan_per_start_date IN DATE,
171 x_loan_per_end_date IN DATE,
172 x_cl_loan_type IN VARCHAR2,
173 x_alt_prog_type_code IN VARCHAR2,
174 x_lender_id IN VARCHAR2,
175 x_lend_non_ed_brc_id IN VARCHAR2,
176 x_lender_use IN VARCHAR2,
177 x_borw_confirm_ind IN VARCHAR2,
178 x_tot_sched_disb IN NUMBER,
179 x_fund_release_date IN DATE,
180 x_disb_num IN NUMBER,
181 x_guarantor_id IN VARCHAR2,
182 x_guarantor_use IN VARCHAR2,
183 x_guarantee_date IN DATE,
184 x_guarantee_amt IN NUMBER,
185 x_gross_disb_amt IN NUMBER,
186 x_fee_1 IN NUMBER,
187 x_fee_2 IN NUMBER,
188 x_net_disb_amt IN NUMBER,
189 x_fund_dist_mthd IN VARCHAR2,
190 x_check_number IN VARCHAR2,
191 x_late_disb_ind IN VARCHAR2,
192 x_prev_reported_ind IN VARCHAR2,
193 x_err_code1 IN VARCHAR2,
194 x_err_code2 IN VARCHAR2,
195 x_err_code3 IN VARCHAR2,
196 x_err_code4 IN VARCHAR2,
197 x_err_code5 IN VARCHAR2,
198 x_fee_paid_2 IN NUMBER,
199 x_lender_name IN VARCHAR2,
200 x_net_cancel_amt IN NUMBER,
201 x_duns_lender_id IN VARCHAR2,
202 x_duns_guarnt_id IN VARCHAR2,
203 x_hold_rel_ind IN VARCHAR2,
204 x_pnote_code IN VARCHAR2,
205 x_pnote_status_date IN DATE,
206 x_fee_paid_1 IN NUMBER,
207 x_netted_cancel_amt IN NUMBER,
208 x_outstd_cancel_amt IN NUMBER,
209 x_sch_non_ed_brc_id IN VARCHAR2,
210 x_status IN VARCHAR2,
211 x_mode IN VARCHAR2 DEFAULT 'R' ,
212 x_esign_src_typ_cd IN VARCHAR2 DEFAULT NULL,
213 x_direct_to_borr_flag IN VARCHAR2 DEFAULT NULL
214 );
215
216 PROCEDURE add_row (
217 x_rowid IN OUT NOCOPY VARCHAR2,
218 x_cdbr_id IN OUT NOCOPY NUMBER,
219 x_cbth_id IN NUMBER,
220 x_record_type IN VARCHAR2,
221 x_loan_number IN VARCHAR2,
222 x_cl_seq_number IN NUMBER,
223 x_b_last_name IN VARCHAR2,
224 x_b_first_name IN VARCHAR2,
225 x_b_middle_name IN VARCHAR2,
226 x_b_ssn IN VARCHAR2,
227 x_b_addr_line_1 IN VARCHAR2,
228 x_b_addr_line_2 IN VARCHAR2,
229 x_b_city IN VARCHAR2,
230 x_b_state IN VARCHAR2,
231 x_b_zip IN NUMBER,
232 x_b_zip_suffix IN NUMBER,
233 x_b_addr_chg_date IN DATE,
234 x_eft_auth_code IN VARCHAR2,
235 x_s_last_name IN VARCHAR2,
236 x_s_first_name IN VARCHAR2,
237 x_s_middle_initial IN VARCHAR2,
238 x_s_ssn IN VARCHAR2,
239 x_school_id IN NUMBER,
240 x_school_use IN VARCHAR2,
241 x_loan_per_start_date IN DATE,
242 x_loan_per_end_date IN DATE,
243 x_cl_loan_type IN VARCHAR2,
244 x_alt_prog_type_code IN VARCHAR2,
245 x_lender_id IN VARCHAR2,
246 x_lend_non_ed_brc_id IN VARCHAR2,
247 x_lender_use IN VARCHAR2,
248 x_borw_confirm_ind IN VARCHAR2,
249 x_tot_sched_disb IN NUMBER,
250 x_fund_release_date IN DATE,
251 x_disb_num IN NUMBER,
252 x_guarantor_id IN VARCHAR2,
253 x_guarantor_use IN VARCHAR2,
254 x_guarantee_date IN DATE,
255 x_guarantee_amt IN NUMBER,
256 x_gross_disb_amt IN NUMBER,
257 x_fee_1 IN NUMBER,
258 x_fee_2 IN NUMBER,
259 x_net_disb_amt IN NUMBER,
260 x_fund_dist_mthd IN VARCHAR2,
261 x_check_number IN VARCHAR2,
262 x_late_disb_ind IN VARCHAR2,
263 x_prev_reported_ind IN VARCHAR2,
264 x_err_code1 IN VARCHAR2,
265 x_err_code2 IN VARCHAR2,
266 x_err_code3 IN VARCHAR2,
267 x_err_code4 IN VARCHAR2,
268 x_err_code5 IN VARCHAR2,
269 x_fee_paid_2 IN NUMBER,
270 x_lender_name IN VARCHAR2,
271 x_net_cancel_amt IN NUMBER,
272 x_duns_lender_id IN VARCHAR2,
273 x_duns_guarnt_id IN VARCHAR2,
274 x_hold_rel_ind IN VARCHAR2,
275 x_pnote_code IN VARCHAR2,
276 x_pnote_status_date IN DATE,
277 x_fee_paid_1 IN NUMBER,
278 x_netted_cancel_amt IN NUMBER,
279 x_outstd_cancel_amt IN NUMBER,
280 x_sch_non_ed_brc_id IN VARCHAR2,
281 x_status IN VARCHAR2,
282 x_mode IN VARCHAR2 DEFAULT 'R',
283 x_esign_src_typ_cd IN VARCHAR2 DEFAULT NULL,
284 x_direct_to_borr_flag IN VARCHAR2 DEFAULT NULL
285 );
286
287 PROCEDURE delete_row (
288 x_rowid IN VARCHAR2
289 );
290
291 FUNCTION get_pk_for_validation (
292 x_cdbr_id IN NUMBER
293 ) RETURN BOOLEAN;
294
295 PROCEDURE get_fk_igf_sl_cl_batch (
296 x_cbth_id IN NUMBER
297 );
298
299 PROCEDURE before_dml (
300 p_action IN VARCHAR2,
301 x_rowid IN VARCHAR2 DEFAULT NULL,
305 x_loan_number IN VARCHAR2 DEFAULT NULL,
302 x_cdbr_id IN NUMBER DEFAULT NULL,
303 x_cbth_id IN NUMBER DEFAULT NULL,
304 x_record_type IN VARCHAR2 DEFAULT NULL,
306 x_cl_seq_number IN NUMBER DEFAULT NULL,
307 x_b_last_name IN VARCHAR2 DEFAULT NULL,
308 x_b_first_name IN VARCHAR2 DEFAULT NULL,
309 x_b_middle_name IN VARCHAR2 DEFAULT NULL,
310 x_b_ssn IN VARCHAR2 DEFAULT NULL,
311 x_b_addr_line_1 IN VARCHAR2 DEFAULT NULL,
312 x_b_addr_line_2 IN VARCHAR2 DEFAULT NULL,
313 x_b_city IN VARCHAR2 DEFAULT NULL,
314 x_b_state IN VARCHAR2 DEFAULT NULL,
315 x_b_zip IN NUMBER DEFAULT NULL,
316 x_b_zip_suffix IN NUMBER DEFAULT NULL,
317 x_b_addr_chg_date IN DATE DEFAULT NULL,
318 x_eft_auth_code IN VARCHAR2 DEFAULT NULL,
319 x_s_last_name IN VARCHAR2 DEFAULT NULL,
320 x_s_first_name IN VARCHAR2 DEFAULT NULL,
321 x_s_middle_initial IN VARCHAR2 DEFAULT NULL,
322 x_s_ssn IN VARCHAR2 DEFAULT NULL,
323 x_school_id IN NUMBER DEFAULT NULL,
324 x_school_use IN VARCHAR2 DEFAULT NULL,
325 x_loan_per_start_date IN DATE DEFAULT NULL,
326 x_loan_per_end_date IN DATE DEFAULT NULL,
327 x_cl_loan_type IN VARCHAR2 DEFAULT NULL,
328 x_alt_prog_type_code IN VARCHAR2 DEFAULT NULL,
329 x_lender_id IN VARCHAR2 DEFAULT NULL,
330 x_lend_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
331 x_lender_use IN VARCHAR2 DEFAULT NULL,
332 x_borw_confirm_ind IN VARCHAR2 DEFAULT NULL,
333 x_tot_sched_disb IN NUMBER DEFAULT NULL,
334 x_fund_release_date IN DATE DEFAULT NULL,
335 x_disb_num IN NUMBER DEFAULT NULL,
336 x_guarantor_id IN VARCHAR2 DEFAULT NULL,
337 x_guarantor_use IN VARCHAR2 DEFAULT NULL,
338 x_guarantee_date IN DATE DEFAULT NULL,
339 x_guarantee_amt IN NUMBER DEFAULT NULL,
340 x_gross_disb_amt IN NUMBER DEFAULT NULL,
341 x_fee_1 IN NUMBER DEFAULT NULL,
342 x_fee_2 IN NUMBER DEFAULT NULL,
343 x_net_disb_amt IN NUMBER DEFAULT NULL,
344 x_fund_dist_mthd IN VARCHAR2 DEFAULT NULL,
345 x_check_number IN VARCHAR2 DEFAULT NULL,
346 x_late_disb_ind IN VARCHAR2 DEFAULT NULL,
347 x_prev_reported_ind IN VARCHAR2 DEFAULT NULL,
348 x_err_code1 IN VARCHAR2 DEFAULT NULL,
349 x_err_code2 IN VARCHAR2 DEFAULT NULL,
350 x_err_code3 IN VARCHAR2 DEFAULT NULL,
351 x_err_code4 IN VARCHAR2 DEFAULT NULL,
352 x_err_code5 IN VARCHAR2 DEFAULT NULL,
353 x_fee_paid_2 IN NUMBER DEFAULT NULL,
354 x_lender_name IN VARCHAR2 DEFAULT NULL,
355 x_net_cancel_amt IN NUMBER DEFAULT NULL,
356 x_duns_lender_id IN VARCHAR2 DEFAULT NULL,
357 x_duns_guarnt_id IN VARCHAR2 DEFAULT NULL,
358 x_hold_rel_ind IN VARCHAR2 DEFAULT NULL,
359 x_pnote_code IN VARCHAR2 DEFAULT NULL,
360 x_pnote_status_date IN DATE DEFAULT NULL,
361 x_fee_paid_1 IN NUMBER DEFAULT NULL,
362 x_netted_cancel_amt IN NUMBER DEFAULT NULL,
363 x_outstd_cancel_amt IN NUMBER DEFAULT NULL,
364 x_sch_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
365 x_status IN VARCHAR2 DEFAULT NULL,
366 x_creation_date IN DATE DEFAULT NULL,
367 x_created_by IN NUMBER DEFAULT NULL,
368 x_last_update_date IN DATE DEFAULT NULL,
369 x_last_updated_by IN NUMBER DEFAULT NULL,
370 x_last_update_login IN NUMBER DEFAULT NULL,
371 x_esign_src_typ_cd IN VARCHAR2 DEFAULT NULL,
372 x_direct_to_borr_flag IN VARCHAR2 DEFAULT NULL
373 );
374
375 END igf_db_cl_disb_resp_pkg;