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