[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_ANC_INT_PKG
Source
1 PACKAGE BODY igs_fi_anc_int_pkg AS
2 /* $Header: IGSSI81B.pls 115.12 2002/11/29 03:54:31 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_anc_int_all%ROWTYPE;
6 new_references igs_fi_anc_int_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_ancillary_int_id IN NUMBER ,
12 x_person_id IN NUMBER ,
13 x_person_id_type IN VARCHAR2 ,
14 x_api_person_id IN VARCHAR2 ,
15 x_status IN VARCHAR2 ,
16 x_fee_type IN VARCHAR2 ,
17 x_fee_cal_type IN VARCHAR2 ,
18 x_fee_ci_sequence_number IN NUMBER ,
19 x_ancillary_attribute1 IN VARCHAR2 ,
20 x_ancillary_attribute2 IN VARCHAR2 ,
21 x_ancillary_attribute3 IN VARCHAR2 ,
22 x_ancillary_attribute4 IN VARCHAR2 ,
23 x_ancillary_attribute5 IN VARCHAR2 ,
24 x_ancillary_attribute6 IN VARCHAR2 ,
25 x_ancillary_attribute7 IN VARCHAR2 ,
26 x_ancillary_attribute8 IN VARCHAR2 ,
27 x_ancillary_attribute9 IN VARCHAR2 ,
28 x_ancillary_attribute10 IN VARCHAR2 ,
29 x_ancillary_attribute11 IN VARCHAR2 ,
30 x_ancillary_attribute12 IN VARCHAR2 ,
31 x_ancillary_attribute13 IN VARCHAR2 ,
32 x_ancillary_attribute14 IN VARCHAR2 ,
33 x_ancillary_attribute15 IN VARCHAR2 ,
34 x_attribute_category IN VARCHAR2 ,
35 x_attribute1 IN VARCHAR2 ,
36 x_attribute2 IN VARCHAR2 ,
37 x_attribute3 IN VARCHAR2 ,
38 x_attribute4 IN VARCHAR2 ,
39 x_attribute5 IN VARCHAR2 ,
40 x_attribute6 IN VARCHAR2 ,
41 x_attribute7 IN VARCHAR2 ,
42 x_attribute8 IN VARCHAR2 ,
43 x_attribute9 IN VARCHAR2 ,
44 x_attribute10 IN VARCHAR2 ,
45 x_attribute11 IN VARCHAR2 ,
46 x_attribute12 IN VARCHAR2 ,
47 x_attribute13 IN VARCHAR2 ,
48 x_attribute14 IN VARCHAR2 ,
49 x_attribute15 IN VARCHAR2 ,
50 x_attribute16 IN VARCHAR2 ,
51 x_attribute17 IN VARCHAR2 ,
52 x_attribute18 IN VARCHAR2 ,
53 x_attribute19 IN VARCHAR2 ,
54 x_attribute20 IN VARCHAR2 ,
55 x_effective_dt IN DATE ,
56 x_error_msg IN VARCHAR2 ,
57 x_validation_flag IN VARCHAR2 ,
58 x_creation_date IN DATE ,
59 x_created_by IN NUMBER ,
60 x_last_update_date IN DATE ,
61 x_last_updated_by IN NUMBER ,
62 x_last_update_login IN NUMBER
63 ) AS
64 /*
65 || Created By : [email protected]
66 || Created On : 09-APR-2001
67 || Purpose : Initialises the Old and New references for the columns of the table.
68 || Known limitations, enhancements or remarks :
69 || Change History :
70 || Who When What
71 || smadathi 06-nov-2002 Enh. Bug 2584986. Removed Obsoleted
72 || columns as specified in the TD. removed all DEFAULT clause
73 || (reverse chronological order - newest change first)
74 */
75
76 CURSOR cur_old_ref_values IS
77 SELECT *
78 FROM IGS_FI_ANC_INT_ALL
79 WHERE rowid = x_rowid;
80
81 BEGIN
82
83 l_rowid := x_rowid;
84
85 -- Code for setting the Old and New Reference Values.
86 -- Populate Old Values.
87 OPEN cur_old_ref_values;
88 FETCH cur_old_ref_values INTO old_references;
89 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
90 CLOSE cur_old_ref_values;
91 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
92 igs_ge_msg_stack.add;
93 app_exception.raise_exception;
94 RETURN;
95 END IF;
96 CLOSE cur_old_ref_values;
97
98 -- Populate New Values.
99 new_references.ancillary_int_id := x_ancillary_int_id;
100 new_references.person_id := x_person_id;
101 new_references.person_id_type := x_person_id_type;
102 new_references.api_person_id := x_api_person_id;
103 new_references.status := x_status;
104 new_references.fee_type := x_fee_type;
105 new_references.fee_cal_type := x_fee_cal_type;
106 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
107 new_references.ancillary_attribute1 := x_ancillary_attribute1;
108 new_references.ancillary_attribute2 := x_ancillary_attribute2;
109 new_references.ancillary_attribute3 := x_ancillary_attribute3;
110 new_references.ancillary_attribute4 := x_ancillary_attribute4;
111 new_references.ancillary_attribute5 := x_ancillary_attribute5;
112 new_references.ancillary_attribute6 := x_ancillary_attribute6;
113 new_references.ancillary_attribute7 := x_ancillary_attribute7;
114 new_references.ancillary_attribute8 := x_ancillary_attribute8;
115 new_references.ancillary_attribute9 := x_ancillary_attribute9;
116 new_references.ancillary_attribute10 := x_ancillary_attribute10;
117 new_references.ancillary_attribute11 := x_ancillary_attribute11;
118 new_references.ancillary_attribute12 := x_ancillary_attribute12;
119 new_references.ancillary_attribute13 := x_ancillary_attribute13;
120 new_references.ancillary_attribute14 := x_ancillary_attribute14;
121 new_references.ancillary_attribute15 := x_ancillary_attribute15;
122 new_references.attribute_category := x_attribute_category;
123 new_references.attribute1 := x_attribute1;
124 new_references.attribute2 := x_attribute2;
125 new_references.attribute3 := x_attribute3;
126 new_references.attribute4 := x_attribute4;
127 new_references.attribute5 := x_attribute5;
128 new_references.attribute6 := x_attribute6;
129 new_references.attribute7 := x_attribute7;
130 new_references.attribute8 := x_attribute8;
131 new_references.attribute9 := x_attribute9;
132 new_references.attribute10 := x_attribute10;
133 new_references.attribute11 := x_attribute11;
134 new_references.attribute12 := x_attribute12;
135 new_references.attribute13 := x_attribute13;
136 new_references.attribute14 := x_attribute14;
137 new_references.attribute15 := x_attribute15;
138 new_references.attribute16 := x_attribute16;
139 new_references.attribute17 := x_attribute17;
140 new_references.attribute18 := x_attribute18;
141 new_references.attribute19 := x_attribute19;
142 new_references.attribute20 := x_attribute20;
143 new_references.effective_dt := x_effective_dt;
144 new_references.error_msg := x_error_msg;
145 new_references.validation_flag := x_validation_flag;
146
147 IF (p_action = 'UPDATE') THEN
148 new_references.creation_date := old_references.creation_date;
149 new_references.created_by := old_references.created_by;
150 ELSE
151 new_references.creation_date := x_creation_date;
152 new_references.created_by := x_created_by;
153 END IF;
154
155 new_references.last_update_date := x_last_update_date;
156 new_references.last_updated_by := x_last_updated_by;
157 new_references.last_update_login := x_last_update_login;
158
159 END set_column_values;
160
161
162 PROCEDURE check_parent_existance AS
163 /*
164 || Created By : [email protected]
165 || Created On : 09-APR-2001
166 || Purpose : Checks for the existance of Parent records.
167 || Known limitations, enhancements or remarks :
168 || Change History :
169 || Who When What
170 || smadathi 06-nov-2002 Enh. Bug 2584986. Removed references of Obsoleted
171 || columns as specified in the TD
172 || (reverse chronological order - newest change first)
173 */
174 BEGIN
175
176 IF (((old_references.fee_cal_type = new_references.fee_cal_type) AND
177 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
178 (old_references.fee_type = new_references.fee_type)) OR
179 ((new_references.fee_cal_type IS NULL) OR
180 (new_references.fee_ci_sequence_number IS NULL) OR
181 (new_references.fee_type IS NULL))) THEN
182 NULL;
183 ELSIF NOT igs_fi_f_typ_ca_inst_pkg.get_pk_for_validation (
184 new_references.fee_type,
185 new_references.fee_cal_type,
186 new_references.fee_ci_sequence_number
187 ) THEN
188 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
189 igs_ge_msg_stack.add;
190 app_exception.raise_exception;
191 ELSIF NOT Igs_lookups_view_Pkg.Get_PK_For_Validation (
192 'STATUS',
193 new_references.status
194 ) THEN
195 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
196 IGS_GE_MSG_STACK.ADD;
197 App_Exception.Raise_Exception;
198 END IF;
199
200 END check_parent_existance;
201
202
203 FUNCTION get_pk_for_validation (
204 x_ancillary_int_id IN NUMBER
205 ) RETURN BOOLEAN AS
206 /*
207 || Created By : [email protected]
208 || Created On : 09-APR-2001
209 || Purpose : Validates the Primary Key of the table.
210 || Known limitations, enhancements or remarks :
211 || Change History :
212 || Who When What
213 || (reverse chronological order - newest change first)
214 */
215 CURSOR cur_rowid IS
216 SELECT rowid
217 FROM igs_fi_anc_int_all
218 WHERE ancillary_int_id = x_ancillary_int_id
219 FOR UPDATE NOWAIT;
220
221 lv_rowid cur_rowid%RowType;
222
223 BEGIN
224
225 OPEN cur_rowid;
226 FETCH cur_rowid INTO lv_rowid;
227 IF (cur_rowid%FOUND) THEN
228 CLOSE cur_rowid;
229 RETURN(TRUE);
230 ELSE
231 CLOSE cur_rowid;
232 RETURN(FALSE);
233 END IF;
234
235 END get_pk_for_validation;
236
237 PROCEDURE get_fk_igs_fi_f_typ_ca_inst (
238 x_fee_cal_type IN VARCHAR2,
239 x_fee_ci_sequence_number IN NUMBER,
240 x_fee_type IN VARCHAR2
241 ) AS
242 /*
243 || Created By : [email protected]
244 || Created On : 09-APR-2001
245 || Purpose : Validates the Foreign Keys for the table.
246 || Known limitations, enhancements or remarks :
247 || Change History :
248 || Who When What
249 || (reverse chronological order - newest change first)
250 */
251 CURSOR cur_rowid IS
252 SELECT rowid
253 FROM igs_fi_anc_int_all
254 WHERE ((fee_type = x_fee_type) AND
255 (fee_cal_type = x_fee_cal_type) AND
256 (fee_ci_sequence_number = x_fee_ci_sequence_number));
257
258 lv_rowid cur_rowid%RowType;
259
260 BEGIN
261
262 OPEN cur_rowid;
263 FETCH cur_rowid INTO lv_rowid;
264 IF (cur_rowid%FOUND) THEN
265 CLOSE cur_rowid;
266 fnd_message.set_name ('IGS', 'IGS_FI_FAIN_FTCI_FK');
267 igs_ge_msg_stack.add;
268 app_exception.raise_exception;
269 RETURN;
270 END IF;
271 CLOSE cur_rowid;
272
273 END get_fk_igs_fi_f_typ_ca_inst;
274
275
276 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
277 x_status IN VARCHAR2
278 ) AS
279
280 CURSOR cur_rowid IS
281 SELECT rowid
282 FROM IGS_FI_ANC_INT_ALL
283 WHERE status = x_status ;
284
285 lv_rowid cur_rowid%RowType;
286
287 BEGIN
288
289 Open cur_rowid;
290 Fetch cur_rowid INTO lv_rowid;
291 IF (cur_rowid%FOUND) THEN
292 Close cur_rowid;
293 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FAIN_LKUPV_FK');
294 IGS_GE_MSG_STACK.ADD;
295 App_Exception.Raise_Exception;
296 Return;
297 END IF;
298 Close cur_rowid;
299
300 END GET_FK_IGS_LOOKUPS_VIEW;
301
302
303
304 PROCEDURE before_dml (
305 p_action IN VARCHAR2,
306 x_rowid IN VARCHAR2 ,
307 x_ancillary_int_id IN NUMBER ,
308 x_person_id IN NUMBER ,
309 x_person_id_type IN VARCHAR2 ,
310 x_api_person_id IN VARCHAR2 ,
311 x_status IN VARCHAR2 ,
312 x_fee_type IN VARCHAR2 ,
313 x_fee_cal_type IN VARCHAR2 ,
314 x_fee_ci_sequence_number IN NUMBER ,
315 x_ancillary_attribute1 IN VARCHAR2 ,
316 x_ancillary_attribute2 IN VARCHAR2 ,
317 x_ancillary_attribute3 IN VARCHAR2 ,
318 x_ancillary_attribute4 IN VARCHAR2 ,
319 x_ancillary_attribute5 IN VARCHAR2 ,
320 x_ancillary_attribute6 IN VARCHAR2 ,
321 x_ancillary_attribute7 IN VARCHAR2 ,
322 x_ancillary_attribute8 IN VARCHAR2 ,
323 x_ancillary_attribute9 IN VARCHAR2 ,
324 x_ancillary_attribute10 IN VARCHAR2 ,
325 x_ancillary_attribute11 IN VARCHAR2 ,
326 x_ancillary_attribute12 IN VARCHAR2 ,
327 x_ancillary_attribute13 IN VARCHAR2 ,
328 x_ancillary_attribute14 IN VARCHAR2 ,
329 x_ancillary_attribute15 IN VARCHAR2 ,
330 x_attribute_category IN VARCHAR2 ,
331 x_attribute1 IN VARCHAR2 ,
332 x_attribute2 IN VARCHAR2 ,
333 x_attribute3 IN VARCHAR2 ,
334 x_attribute4 IN VARCHAR2 ,
335 x_attribute5 IN VARCHAR2 ,
336 x_attribute6 IN VARCHAR2 ,
337 x_attribute7 IN VARCHAR2 ,
338 x_attribute8 IN VARCHAR2 ,
339 x_attribute9 IN VARCHAR2 ,
340 x_attribute10 IN VARCHAR2 ,
341 x_attribute11 IN VARCHAR2 ,
342 x_attribute12 IN VARCHAR2 ,
343 x_attribute13 IN VARCHAR2 ,
347 x_attribute17 IN VARCHAR2 ,
344 x_attribute14 IN VARCHAR2 ,
345 x_attribute15 IN VARCHAR2 ,
346 x_attribute16 IN VARCHAR2 ,
348 x_attribute18 IN VARCHAR2 ,
349 x_attribute19 IN VARCHAR2 ,
350 x_attribute20 IN VARCHAR2 ,
351 x_effective_dt IN DATE ,
352 x_error_msg IN VARCHAR2 ,
353 x_validation_flag IN VARCHAR2 ,
354 x_creation_date IN DATE ,
355 x_created_by IN NUMBER ,
356 x_last_update_date IN DATE ,
357 x_last_updated_by IN NUMBER ,
358 x_last_update_login IN NUMBER
359 ) AS
360 /*
361 || Created By : [email protected]
362 || Created On : 09-APR-2001
363 || Purpose : Initialises the columns, Checks Constraints, Calls the
364 || Trigger Handlers for the table, before any DML operation.
365 || Known limitations, enhancements or remarks :
366 || Change History :
367 || Who When What
368 || smadathi 06-nov-2002 Enh. Bug 2584986. Removed Obsoleted
369 || columns as specified in the TD. removed all DEFAULT clause
370 || (reverse chronological order - newest change first)
371 */
372 BEGIN
373
374 set_column_values (
375 p_action,
376 x_rowid,
377 x_ancillary_int_id,
378 x_person_id,
379 x_person_id_type,
380 x_api_person_id,
381 x_status,
382 x_fee_type,
383 x_fee_cal_type,
384 x_fee_ci_sequence_number,
385 x_ancillary_attribute1,
386 x_ancillary_attribute2,
387 x_ancillary_attribute3,
388 x_ancillary_attribute4,
389 x_ancillary_attribute5,
390 x_ancillary_attribute6,
391 x_ancillary_attribute7,
392 x_ancillary_attribute8,
393 x_ancillary_attribute9,
394 x_ancillary_attribute10,
395 x_ancillary_attribute11,
396 x_ancillary_attribute12,
397 x_ancillary_attribute13,
398 x_ancillary_attribute14,
399 x_ancillary_attribute15,
400 x_attribute_category,
401 x_attribute1,
402 x_attribute2,
403 x_attribute3,
404 x_attribute4,
405 x_attribute5,
406 x_attribute6,
407 x_attribute7,
408 x_attribute8,
409 x_attribute9,
410 x_attribute10,
411 x_attribute11,
412 x_attribute12,
413 x_attribute13,
414 x_attribute14,
415 x_attribute15,
416 x_attribute16,
417 x_attribute17,
418 x_attribute18,
419 x_attribute19,
420 x_attribute20,
421 x_effective_dt,
422 x_error_msg,
423 x_validation_flag,
424 x_creation_date,
425 x_created_by,
426 x_last_update_date,
427 x_last_updated_by,
428 x_last_update_login
429 );
430
431 IF (p_action = 'INSERT') THEN
432 -- Call all the procedures related to Before Insert.
433
434 IF ( get_pk_for_validation(
435 new_references.ancillary_int_id
436 )
437 ) THEN
438 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
439 igs_ge_msg_stack.add;
440 app_exception.raise_exception;
441 END IF;
442 check_parent_existance;
443 ELSIF (p_action = 'UPDATE') THEN
444 -- Call all the procedures related to Before Update.
445 check_parent_existance;
446 ELSIF (p_action = 'VALIDATE_INSERT') THEN
447 -- Call all the procedures related to Before Insert.
448 IF ( get_pk_for_validation (
449 new_references.ancillary_int_id
450 )
451 ) THEN
452 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
453 igs_ge_msg_stack.add;
454 app_exception.raise_exception;
455 END IF;
456 END IF;
457
458 END before_dml;
459
460
461 PROCEDURE insert_row (
462 x_rowid IN OUT NOCOPY VARCHAR2,
463 x_ancillary_int_id IN OUT NOCOPY NUMBER,
464 x_person_id IN NUMBER,
465 x_person_id_type IN VARCHAR2,
466 x_api_person_id IN VARCHAR2,
467 x_status IN VARCHAR2,
468 x_fee_type IN VARCHAR2,
469 x_fee_cal_type IN VARCHAR2,
470 x_fee_ci_sequence_number IN NUMBER,
471 x_ancillary_attribute1 IN VARCHAR2,
472 x_ancillary_attribute2 IN VARCHAR2,
476 x_ancillary_attribute6 IN VARCHAR2,
473 x_ancillary_attribute3 IN VARCHAR2,
474 x_ancillary_attribute4 IN VARCHAR2,
475 x_ancillary_attribute5 IN VARCHAR2,
477 x_ancillary_attribute7 IN VARCHAR2,
478 x_ancillary_attribute8 IN VARCHAR2,
479 x_ancillary_attribute9 IN VARCHAR2,
480 x_ancillary_attribute10 IN VARCHAR2,
481 x_ancillary_attribute11 IN VARCHAR2,
482 x_ancillary_attribute12 IN VARCHAR2,
483 x_ancillary_attribute13 IN VARCHAR2,
484 x_ancillary_attribute14 IN VARCHAR2,
485 x_ancillary_attribute15 IN VARCHAR2,
486 x_attribute_category IN VARCHAR2,
487 x_attribute1 IN VARCHAR2,
488 x_attribute2 IN VARCHAR2,
489 x_attribute3 IN VARCHAR2,
490 x_attribute4 IN VARCHAR2,
491 x_attribute5 IN VARCHAR2,
492 x_attribute6 IN VARCHAR2,
493 x_attribute7 IN VARCHAR2,
494 x_attribute8 IN VARCHAR2,
495 x_attribute9 IN VARCHAR2,
496 x_attribute10 IN VARCHAR2,
497 x_attribute11 IN VARCHAR2,
498 x_attribute12 IN VARCHAR2,
499 x_attribute13 IN VARCHAR2,
500 x_attribute14 IN VARCHAR2,
501 x_attribute15 IN VARCHAR2,
502 x_attribute16 IN VARCHAR2,
503 x_attribute17 IN VARCHAR2,
504 x_attribute18 IN VARCHAR2,
505 x_attribute19 IN VARCHAR2,
506 x_attribute20 IN VARCHAR2,
507 x_effective_dt IN DATE,
508 x_error_msg IN VARCHAR2,
509 x_validation_flag IN VARCHAR2,
510 x_mode IN VARCHAR2
511 ) AS
512 /*
513 || Created By : [email protected]
514 || Created On : 09-APR-2001
515 || Purpose : Handles the INSERT DML logic for the table.
516 || Known limitations, enhancements or remarks :
517 || Change History :
518 || Who When What
519 || smadathi 06-nov-2002 Enh. Bug 2584986. Removed Obsoleted
520 || columns as specified in the TD. removed all DEFAULT clause
521 || (reverse chronological order - newest change first)
522 */
523 CURSOR c IS
524 SELECT rowid
525 FROM igs_fi_anc_int_all
526 WHERE ancillary_int_id = x_ancillary_int_id;
527
528 x_last_update_date DATE;
529 x_last_updated_by NUMBER;
530 x_last_update_login NUMBER;
531 x_request_id NUMBER;
532 x_program_id NUMBER;
533 x_program_application_id NUMBER;
534 x_program_update_date DATE;
535
536 BEGIN
537
538 x_last_update_date := SYSDATE;
539 IF (x_mode = 'I') THEN
540 x_last_updated_by := 1;
541 x_last_update_login := 0;
542 ELSIF (x_mode = 'R') THEN
543 x_last_updated_by := fnd_global.user_id;
544 IF (x_last_updated_by IS NULL) THEN
545 x_last_updated_by := -1;
546 END IF;
547 x_last_update_login := fnd_global.login_id;
548 IF (x_last_update_login IS NULL) THEN
549 x_last_update_login := -1;
550 END IF;
551 x_request_id := fnd_global.conc_request_id;
552 x_program_id := fnd_global.conc_program_id;
553 x_program_application_id := fnd_global.prog_appl_id;
557 x_program_id := NULL;
554
555 IF (x_request_id = -1) THEN
556 x_request_id := NULL;
558 x_program_application_id := NULL;
559 x_program_update_date := NULL;
560 ELSE
561 x_program_update_date := SYSDATE;
562 END IF;
563 ELSE
564 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
565 igs_ge_msg_stack.add;
566 app_exception.raise_exception;
567 END IF;
568
569 SELECT IGS_FI_ANC_INT_S.NEXTVAL INTO x_ANCILLARY_INT_ID FROM DUAL;
570 new_references.org_id := IGS_GE_GEN_003.Get_Org_Id;
571 before_dml(
572 p_action => 'INSERT',
573 x_rowid => x_rowid,
574 x_ancillary_int_id => x_ancillary_int_id,
575 x_person_id => x_person_id,
576 x_person_id_type => x_person_id_type,
577 x_api_person_id => x_api_person_id,
578 x_status => x_status,
579 x_fee_type => x_fee_type,
580 x_fee_cal_type => x_fee_cal_type,
581 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
582 x_ancillary_attribute1 => x_ancillary_attribute1,
583 x_ancillary_attribute2 => x_ancillary_attribute2,
584 x_ancillary_attribute3 => x_ancillary_attribute3,
585 x_ancillary_attribute4 => x_ancillary_attribute4,
586 x_ancillary_attribute5 => x_ancillary_attribute5,
587 x_ancillary_attribute6 => x_ancillary_attribute6,
588 x_ancillary_attribute7 => x_ancillary_attribute7,
589 x_ancillary_attribute8 => x_ancillary_attribute8,
590 x_ancillary_attribute9 => x_ancillary_attribute9,
591 x_ancillary_attribute10 => x_ancillary_attribute10,
592 x_ancillary_attribute11 => x_ancillary_attribute11,
593 x_ancillary_attribute12 => x_ancillary_attribute12,
594 x_ancillary_attribute13 => x_ancillary_attribute13,
595 x_ancillary_attribute14 => x_ancillary_attribute14,
596 x_ancillary_attribute15 => x_ancillary_attribute15,
597 x_attribute_category => x_attribute_category,
598 x_attribute1 => x_attribute1,
599 x_attribute2 => x_attribute2,
600 x_attribute3 => x_attribute3,
601 x_attribute4 => x_attribute4,
602 x_attribute5 => x_attribute5,
603 x_attribute6 => x_attribute6,
604 x_attribute7 => x_attribute7,
605 x_attribute8 => x_attribute8,
606 x_attribute9 => x_attribute9,
607 x_attribute10 => x_attribute10,
608 x_attribute11 => x_attribute11,
609 x_attribute12 => x_attribute12,
610 x_attribute13 => x_attribute13,
611 x_attribute14 => x_attribute14,
615 x_attribute18 => x_attribute18,
612 x_attribute15 => x_attribute15,
613 x_attribute16 => x_attribute16,
614 x_attribute17 => x_attribute17,
616 x_attribute19 => x_attribute19,
617 x_attribute20 => x_attribute20,
618 x_effective_dt => x_effective_dt,
619 x_error_msg => x_error_msg,
620 x_validation_flag => x_validation_flag,
621 x_creation_date => x_last_update_date,
622 x_created_by => x_last_updated_by,
623 x_last_update_date => x_last_update_date,
624 x_last_updated_by => x_last_updated_by,
625 x_last_update_login => x_last_update_login
626 );
627
628 INSERT INTO igs_fi_anc_int_all (
629 ancillary_int_id,
630 person_id,
631 person_id_type,
632 api_person_id,
633 status,
634 fee_type,
635 fee_cal_type,
636 fee_ci_sequence_number,
637 ancillary_attribute1,
638 ancillary_attribute2,
639 ancillary_attribute3,
640 ancillary_attribute4,
641 ancillary_attribute5,
642 ancillary_attribute6,
643 ancillary_attribute7,
644 ancillary_attribute8,
645 ancillary_attribute9,
646 ancillary_attribute10,
647 ancillary_attribute11,
648 ancillary_attribute12,
649 ancillary_attribute13,
650 ancillary_attribute14,
651 ancillary_attribute15,
652 attribute_category,
653 attribute1,
654 attribute2,
655 attribute3,
656 attribute4,
657 attribute5,
658 attribute6,
659 attribute7,
660 attribute8,
661 attribute9,
662 attribute10,
663 attribute11,
664 attribute12,
665 attribute13,
666 attribute14,
667 attribute15,
668 attribute16,
669 attribute17,
670 attribute18,
671 attribute19,
672 attribute20,
673 org_id,
674 effective_dt,
675 error_msg,
676 validation_flag,
677 creation_date,
678 created_by,
679 last_update_date,
680 last_updated_by,
681 last_update_login,
682 request_id,
683 program_id,
684 program_application_id,
685 program_update_date
686 ) VALUES (
687 new_references.ancillary_int_id,
688 new_references.person_id,
689 new_references.person_id_type,
690 new_references.api_person_id,
691 new_references.status,
692 new_references.fee_type,
693 new_references.fee_cal_type,
697 new_references.ancillary_attribute3,
694 new_references.fee_ci_sequence_number,
695 new_references.ancillary_attribute1,
696 new_references.ancillary_attribute2,
698 new_references.ancillary_attribute4,
699 new_references.ancillary_attribute5,
700 new_references.ancillary_attribute6,
701 new_references.ancillary_attribute7,
702 new_references.ancillary_attribute8,
703 new_references.ancillary_attribute9,
704 new_references.ancillary_attribute10,
705 new_references.ancillary_attribute11,
706 new_references.ancillary_attribute12,
707 new_references.ancillary_attribute13,
708 new_references.ancillary_attribute14,
709 new_references.ancillary_attribute15,
710 new_references.attribute_category,
711 new_references.attribute1,
712 new_references.attribute2,
713 new_references.attribute3,
714 new_references.attribute4,
715 new_references.attribute5,
716 new_references.attribute6,
717 new_references.attribute7,
718 new_references.attribute8,
719 new_references.attribute9,
720 new_references.attribute10,
721 new_references.attribute11,
722 new_references.attribute12,
723 new_references.attribute13,
724 new_references.attribute14,
725 new_references.attribute15,
726 new_references.attribute16,
727 new_references.attribute17,
728 new_references.attribute18,
729 new_references.attribute19,
730 new_references.attribute20,
731 new_references.org_id,
732 new_references.effective_dt,
733 new_references.error_msg,
734 new_references.validation_flag,
735 x_last_update_date,
736 x_last_updated_by,
737 x_last_update_date,
738 x_last_updated_by,
739 x_last_update_login ,
740 x_request_id,
741 x_program_id,
742 x_program_application_id,
743 x_program_update_date
744 );
745
746 OPEN c;
747 FETCH c INTO x_rowid;
748 IF (c%NOTFOUND) THEN
749 CLOSE c;
750 RAISE NO_DATA_FOUND;
751 END IF;
752 CLOSE c;
753
754 l_rowid := NULL;
755
756 END insert_row;
757
758
759 PROCEDURE lock_row (
760 x_rowid IN VARCHAR2,
761 x_ancillary_int_id IN NUMBER,
762 x_person_id IN NUMBER,
763 x_person_id_type IN VARCHAR2,
764 x_api_person_id IN VARCHAR2,
765 x_status IN VARCHAR2,
766 x_fee_type IN VARCHAR2,
767 x_fee_cal_type IN VARCHAR2,
768 x_fee_ci_sequence_number IN NUMBER,
769 x_ancillary_attribute1 IN VARCHAR2,
770 x_ancillary_attribute2 IN VARCHAR2,
771 x_ancillary_attribute3 IN VARCHAR2,
772 x_ancillary_attribute4 IN VARCHAR2,
773 x_ancillary_attribute5 IN VARCHAR2,
774 x_ancillary_attribute6 IN VARCHAR2,
775 x_ancillary_attribute7 IN VARCHAR2,
776 x_ancillary_attribute8 IN VARCHAR2,
777 x_ancillary_attribute9 IN VARCHAR2,
778 x_ancillary_attribute10 IN VARCHAR2,
779 x_ancillary_attribute11 IN VARCHAR2,
780 x_ancillary_attribute12 IN VARCHAR2,
781 x_ancillary_attribute13 IN VARCHAR2,
782 x_ancillary_attribute14 IN VARCHAR2,
783 x_ancillary_attribute15 IN VARCHAR2,
784 x_attribute_category IN VARCHAR2,
785 x_attribute1 IN VARCHAR2,
786 x_attribute2 IN VARCHAR2,
787 x_attribute3 IN VARCHAR2,
788 x_attribute4 IN VARCHAR2,
789 x_attribute5 IN VARCHAR2,
790 x_attribute6 IN VARCHAR2,
791 x_attribute7 IN VARCHAR2,
795 x_attribute11 IN VARCHAR2,
792 x_attribute8 IN VARCHAR2,
793 x_attribute9 IN VARCHAR2,
794 x_attribute10 IN VARCHAR2,
796 x_attribute12 IN VARCHAR2,
797 x_attribute13 IN VARCHAR2,
798 x_attribute14 IN VARCHAR2,
799 x_attribute15 IN VARCHAR2,
800 x_attribute16 IN VARCHAR2,
801 x_attribute17 IN VARCHAR2,
802 x_attribute18 IN VARCHAR2,
803 x_attribute19 IN VARCHAR2,
804 x_attribute20 IN VARCHAR2,
805 x_effective_dt IN DATE,
806 x_error_msg IN VARCHAR2,
807 x_validation_flag IN VARCHAR2
808 ) AS
809 /*
810 || Created By : [email protected]
811 || Created On : 09-APR-2001
812 || Purpose : Handles the LOCK mechanism for the table.
813 || Known limitations, enhancements or remarks :
814 || Change History :
815 || Who When What
816 || smadathi 06-nov-2002 Enh. Bug 2584986. Removed Obsoleted
817 || columns as specified in the TD. removed all DEFAULT clause
818 || (reverse chronological order - newest change first)
819 */
820 CURSOR c1 IS
821 SELECT
822 person_id,
823 person_id_type,
824 api_person_id,
825 status,
826 fee_type,
827 fee_cal_type,
828 fee_ci_sequence_number,
829 ancillary_attribute1,
830 ancillary_attribute2,
831 ancillary_attribute3,
832 ancillary_attribute4,
833 ancillary_attribute5,
834 ancillary_attribute6,
835 ancillary_attribute7,
836 ancillary_attribute8,
837 ancillary_attribute9,
838 ancillary_attribute10,
839 ancillary_attribute11,
840 ancillary_attribute12,
841 ancillary_attribute13,
842 ancillary_attribute14,
843 ancillary_attribute15,
844 attribute_category,
845 attribute1,
846 attribute2,
847 attribute3,
848 attribute4,
849 attribute5,
850 attribute6,
851 attribute7,
852 attribute8,
853 attribute9,
854 attribute10,
855 attribute11,
856 attribute12,
857 attribute13,
858 attribute14,
859 attribute15,
860 attribute16,
861 attribute17,
865 effective_dt,
862 attribute18,
863 attribute19,
864 attribute20,
866 error_msg,
867 validation_flag
868 FROM igs_fi_anc_int_all
869 WHERE rowid = x_rowid
870 FOR UPDATE NOWAIT;
871
872 tlinfo c1%ROWTYPE;
873
874 BEGIN
875
876 OPEN c1;
877 FETCH c1 INTO tlinfo;
878 IF (c1%notfound) THEN
879 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
880 igs_ge_msg_stack.add;
881 CLOSE c1;
882 app_exception.raise_exception;
883 RETURN;
884 END IF;
885 CLOSE c1;
886
887 IF (
888 ((tlinfo.person_id = x_person_id) OR ((tlinfo.person_id IS NULL) AND (X_person_id IS NULL)))
889 AND ((tlinfo.person_id_type = x_person_id_type) OR ((tlinfo.person_id_type IS NULL) AND (X_person_id_type IS NULL)))
890 AND ((tlinfo.api_person_id = x_api_person_id) OR ((tlinfo.api_person_id IS NULL) AND (X_api_person_id IS NULL)))
891 AND (tlinfo.status = x_status)
892 AND (tlinfo.fee_type = x_fee_type)
893 AND (tlinfo.fee_cal_type = x_fee_cal_type)
894 AND (tlinfo.fee_ci_sequence_number = x_fee_ci_sequence_number)
895 AND ((tlinfo.ancillary_attribute1 = x_ancillary_attribute1) OR ((tlinfo.ancillary_attribute1 IS NULL) AND (X_ancillary_attribute1 IS NULL)))
896 AND ((tlinfo.ancillary_attribute2 = x_ancillary_attribute2) OR ((tlinfo.ancillary_attribute2 IS NULL) AND (X_ancillary_attribute2 IS NULL)))
897 AND ((tlinfo.ancillary_attribute3 = x_ancillary_attribute3) OR ((tlinfo.ancillary_attribute3 IS NULL) AND (X_ancillary_attribute3 IS NULL)))
898 AND ((tlinfo.ancillary_attribute4 = x_ancillary_attribute4) OR ((tlinfo.ancillary_attribute4 IS NULL) AND (X_ancillary_attribute4 IS NULL)))
899 AND ((tlinfo.ancillary_attribute5 = x_ancillary_attribute5) OR ((tlinfo.ancillary_attribute5 IS NULL) AND (X_ancillary_attribute5 IS NULL)))
900 AND ((tlinfo.ancillary_attribute6 = x_ancillary_attribute6) OR ((tlinfo.ancillary_attribute6 IS NULL) AND (X_ancillary_attribute6 IS NULL)))
901 AND ((tlinfo.ancillary_attribute7 = x_ancillary_attribute7) OR ((tlinfo.ancillary_attribute7 IS NULL) AND (X_ancillary_attribute7 IS NULL)))
902 AND ((tlinfo.ancillary_attribute8 = x_ancillary_attribute8) OR ((tlinfo.ancillary_attribute8 IS NULL) AND (X_ancillary_attribute8 IS NULL)))
903 AND ((tlinfo.ancillary_attribute9 = x_ancillary_attribute9) OR ((tlinfo.ancillary_attribute9 IS NULL) AND (X_ancillary_attribute9 IS NULL)))
904 AND ((tlinfo.ancillary_attribute10 = x_ancillary_attribute10) OR ((tlinfo.ancillary_attribute10 IS NULL) AND (X_ancillary_attribute10 IS NULL)))
905 AND ((tlinfo.ancillary_attribute11 = x_ancillary_attribute11) OR ((tlinfo.ancillary_attribute11 IS NULL) AND (X_ancillary_attribute11 IS NULL)))
906 AND ((tlinfo.ancillary_attribute12 = x_ancillary_attribute12) OR ((tlinfo.ancillary_attribute12 IS NULL) AND (X_ancillary_attribute12 IS NULL)))
907 AND ((tlinfo.ancillary_attribute13 = x_ancillary_attribute13) OR ((tlinfo.ancillary_attribute13 IS NULL) AND (X_ancillary_attribute13 IS NULL)))
908 AND ((tlinfo.ancillary_attribute14 = x_ancillary_attribute14) OR ((tlinfo.ancillary_attribute14 IS NULL) AND (X_ancillary_attribute14 IS NULL)))
909 AND ((tlinfo.ancillary_attribute15 = x_ancillary_attribute15) OR ((tlinfo.ancillary_attribute15 IS NULL) AND (X_ancillary_attribute15 IS NULL)))
910 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
911 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
912 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
913 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
917 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
914 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
915 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
916 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
918 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
919 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
920 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
921 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
922 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
923 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
924 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
925 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
926 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
927 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
928 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
929 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
930 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
931 AND ((tlinfo.effective_dt = x_effective_dt) OR ((tlinfo.effective_dt IS NULL) AND (X_effective_dt IS NULL)))
932 AND ((tlinfo.error_msg = x_error_msg) OR ((tlinfo.error_msg IS NULL) AND (X_error_msg IS NULL)))
933 AND ((tlinfo.validation_flag = x_validation_flag) OR ((tlinfo.validation_flag IS NULL) AND (X_validation_flag IS NULL)))
934 ) THEN
935 NULL;
936 ELSE
937 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
938 igs_ge_msg_stack.add;
939 app_exception.raise_exception;
940 END IF;
941
942 l_rowid := NULL;
943 RETURN;
944
945 END lock_row;
946
947
948 PROCEDURE update_row (
949 x_rowid IN VARCHAR2,
950 x_ancillary_int_id IN NUMBER,
951 x_person_id IN NUMBER,
952 x_person_id_type IN VARCHAR2,
953 x_api_person_id IN VARCHAR2,
954 x_status IN VARCHAR2,
955 x_fee_type IN VARCHAR2,
956 x_fee_cal_type IN VARCHAR2,
957 x_fee_ci_sequence_number IN NUMBER,
958 x_ancillary_attribute1 IN VARCHAR2,
959 x_ancillary_attribute2 IN VARCHAR2,
960 x_ancillary_attribute3 IN VARCHAR2,
961 x_ancillary_attribute4 IN VARCHAR2,
962 x_ancillary_attribute5 IN VARCHAR2,
963 x_ancillary_attribute6 IN VARCHAR2,
964 x_ancillary_attribute7 IN VARCHAR2,
965 x_ancillary_attribute8 IN VARCHAR2,
966 x_ancillary_attribute9 IN VARCHAR2,
967 x_ancillary_attribute10 IN VARCHAR2,
968 x_ancillary_attribute11 IN VARCHAR2,
969 x_ancillary_attribute12 IN VARCHAR2,
970 x_ancillary_attribute13 IN VARCHAR2,
971 x_ancillary_attribute14 IN VARCHAR2,
972 x_ancillary_attribute15 IN VARCHAR2,
973 x_attribute_category IN VARCHAR2,
974 x_attribute1 IN VARCHAR2,
975 x_attribute2 IN VARCHAR2,
976 x_attribute3 IN VARCHAR2,
977 x_attribute4 IN VARCHAR2,
978 x_attribute5 IN VARCHAR2,
979 x_attribute6 IN VARCHAR2,
980 x_attribute7 IN VARCHAR2,
981 x_attribute8 IN VARCHAR2,
982 x_attribute9 IN VARCHAR2,
983 x_attribute10 IN VARCHAR2,
984 x_attribute11 IN VARCHAR2,
985 x_attribute12 IN VARCHAR2,
986 x_attribute13 IN VARCHAR2,
987 x_attribute14 IN VARCHAR2,
988 x_attribute15 IN VARCHAR2,
989 x_attribute16 IN VARCHAR2,
990 x_attribute17 IN VARCHAR2,
991 x_attribute18 IN VARCHAR2,
992 x_attribute19 IN VARCHAR2,
993 x_attribute20 IN VARCHAR2,
994 x_effective_dt IN DATE,
995 x_error_msg IN VARCHAR2,
996 x_validation_flag IN VARCHAR2,
997 x_mode IN VARCHAR2
998 ) AS
999 /*
1000 || Created By : [email protected]
1001 || Created On : 09-APR-2001
1002 || Purpose : Handles the UPDATE DML logic for the table.
1003 || Known limitations, enhancements or remarks :
1004 || Change History :
1008 || (reverse chronological order - newest change first)
1005 || Who When What
1006 || smadathi 06-nov-2002 Enh. Bug 2584986. Removed Obsoleted
1007 || columns as specified in the TD. removed all DEFAULT clause
1009 */
1010 x_last_update_date DATE ;
1011 x_last_updated_by NUMBER;
1012 x_last_update_login NUMBER;
1013 x_request_id NUMBER;
1014 x_program_id NUMBER;
1015 x_program_application_id NUMBER;
1016 x_program_update_date DATE;
1017
1018 BEGIN
1019
1020 x_last_update_date := SYSDATE;
1021 IF (X_MODE = 'I') THEN
1022 x_last_updated_by := 1;
1023 x_last_update_login := 0;
1024 ELSIF (x_mode = 'R') THEN
1025 x_last_updated_by := fnd_global.user_id;
1026 IF x_last_updated_by IS NULL THEN
1027 x_last_updated_by := -1;
1028 END IF;
1029 x_last_update_login := fnd_global.login_id;
1030 IF (x_last_update_login IS NULL) THEN
1031 x_last_update_login := -1;
1032 END IF;
1033 ELSE
1034 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1035 igs_ge_msg_stack.add;
1036 app_exception.raise_exception;
1037 END IF;
1038
1039 before_dml(
1040 p_action => 'UPDATE',
1041 x_rowid => x_rowid,
1042 x_ancillary_int_id => x_ancillary_int_id,
1043 x_person_id => x_person_id,
1044 x_person_id_type => x_person_id_type,
1045 x_api_person_id => x_api_person_id,
1046 x_status => x_status,
1047 x_fee_type => x_fee_type,
1048 x_fee_cal_type => x_fee_cal_type,
1049 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
1050 x_ancillary_attribute1 => x_ancillary_attribute1,
1051 x_ancillary_attribute2 => x_ancillary_attribute2,
1052 x_ancillary_attribute3 => x_ancillary_attribute3,
1053 x_ancillary_attribute4 => x_ancillary_attribute4,
1054 x_ancillary_attribute5 => x_ancillary_attribute5,
1055 x_ancillary_attribute6 => x_ancillary_attribute6,
1056 x_ancillary_attribute7 => x_ancillary_attribute7,
1057 x_ancillary_attribute8 => x_ancillary_attribute8,
1058 x_ancillary_attribute9 => x_ancillary_attribute9,
1059 x_ancillary_attribute10 => x_ancillary_attribute10,
1060 x_ancillary_attribute11 => x_ancillary_attribute11,
1061 x_ancillary_attribute12 => x_ancillary_attribute12,
1062 x_ancillary_attribute13 => x_ancillary_attribute13,
1063 x_ancillary_attribute14 => x_ancillary_attribute14,
1064 x_ancillary_attribute15 => x_ancillary_attribute15,
1065 x_attribute_category => x_attribute_category,
1066 x_attribute1 => x_attribute1,
1067 x_attribute2 => x_attribute2,
1068 x_attribute3 => x_attribute3,
1069 x_attribute4 => x_attribute4,
1070 x_attribute5 => x_attribute5,
1071 x_attribute6 => x_attribute6,
1072 x_attribute7 => x_attribute7,
1073 x_attribute8 => x_attribute8,
1074 x_attribute9 => x_attribute9,
1075 x_attribute10 => x_attribute10,
1076 x_attribute11 => x_attribute11,
1077 x_attribute12 => x_attribute12,
1078 x_attribute13 => x_attribute13,
1079 x_attribute14 => x_attribute14,
1080 x_attribute15 => x_attribute15,
1081 x_attribute16 => x_attribute16,
1082 x_attribute17 => x_attribute17,
1083 x_attribute18 => x_attribute18,
1084 x_attribute19 => x_attribute19,
1085 x_attribute20 => x_attribute20,
1086 x_effective_dt => x_effective_dt,
1087 x_error_msg => x_error_msg,
1088 x_validation_flag => x_validation_flag,
1089 x_creation_date => x_last_update_date,
1090 x_created_by => x_last_updated_by,
1091 x_last_update_date => x_last_update_date,
1092 x_last_updated_by => x_last_updated_by,
1093 x_last_update_login => x_last_update_login
1094 );
1095
1096 IF (x_mode = 'R') THEN
1097 x_request_id := fnd_global.conc_request_id;
1098 x_program_id := fnd_global.conc_program_id;
1099 x_program_application_id := fnd_global.prog_appl_id;
1100 IF (x_request_id = -1) THEN
1101 x_request_id := old_references.request_id;
1102 x_program_id := old_references.program_id;
1103 x_program_application_id := old_references.program_application_id;
1104 x_program_update_date := old_references.program_update_date;
1105 ELSE
1106 x_program_update_date := SYSDATE;
1107 END IF;
1108 END IF;
1109
1110 UPDATE igs_fi_anc_int_all
1111 SET
1112 person_id = new_references.person_id,
1113 person_id_type = new_references.person_id_type,
1117 fee_cal_type = new_references.fee_cal_type,
1114 api_person_id = new_references.api_person_id,
1115 status = new_references.status,
1116 fee_type = new_references.fee_type,
1118 fee_ci_sequence_number = new_references.fee_ci_sequence_number,
1119 ancillary_attribute1 = new_references.ancillary_attribute1,
1120 ancillary_attribute2 = new_references.ancillary_attribute2,
1121 ancillary_attribute3 = new_references.ancillary_attribute3,
1122 ancillary_attribute4 = new_references.ancillary_attribute4,
1123 ancillary_attribute5 = new_references.ancillary_attribute5,
1124 ancillary_attribute6 = new_references.ancillary_attribute6,
1125 ancillary_attribute7 = new_references.ancillary_attribute7,
1126 ancillary_attribute8 = new_references.ancillary_attribute8,
1127 ancillary_attribute9 = new_references.ancillary_attribute9,
1128 ancillary_attribute10 = new_references.ancillary_attribute10,
1129 ancillary_attribute11 = new_references.ancillary_attribute11,
1130 ancillary_attribute12 = new_references.ancillary_attribute12,
1131 ancillary_attribute13 = new_references.ancillary_attribute13,
1132 ancillary_attribute14 = new_references.ancillary_attribute14,
1133 ancillary_attribute15 = new_references.ancillary_attribute15,
1134 attribute_category = new_references.attribute_category,
1135 attribute1 = new_references.attribute1,
1136 attribute2 = new_references.attribute2,
1137 attribute3 = new_references.attribute3,
1138 attribute4 = new_references.attribute4,
1139 attribute5 = new_references.attribute5,
1140 attribute6 = new_references.attribute6,
1141 attribute7 = new_references.attribute7,
1142 attribute8 = new_references.attribute8,
1143 attribute9 = new_references.attribute9,
1144 attribute10 = new_references.attribute10,
1145 attribute11 = new_references.attribute11,
1146 attribute12 = new_references.attribute12,
1147 attribute13 = new_references.attribute13,
1151 attribute17 = new_references.attribute17,
1148 attribute14 = new_references.attribute14,
1149 attribute15 = new_references.attribute15,
1150 attribute16 = new_references.attribute16,
1152 attribute18 = new_references.attribute18,
1153 attribute19 = new_references.attribute19,
1154 attribute20 = new_references.attribute20,
1155 effective_dt = new_references.effective_dt,
1156 error_msg = new_references.error_msg,
1157 validation_flag = new_references.validation_flag,
1158 last_update_date = x_last_update_date,
1159 last_updated_by = x_last_updated_by,
1160 last_update_login = x_last_update_login ,
1161 request_id = x_request_id,
1162 program_id = x_program_id,
1163 program_application_id = x_program_application_id,
1164 program_update_date = x_program_update_date
1165 WHERE rowid = x_rowid;
1166
1167 IF (SQL%NOTFOUND) THEN
1168 RAISE NO_DATA_FOUND;
1169 END IF;
1170
1171 l_rowid := NULL;
1172
1173 END update_row;
1174
1175
1176 PROCEDURE add_row (
1177 x_rowid IN OUT NOCOPY VARCHAR2,
1178 x_ancillary_int_id IN OUT NOCOPY NUMBER,
1179 x_person_id IN NUMBER,
1180 x_person_id_type IN VARCHAR2,
1181 x_api_person_id IN VARCHAR2,
1182 x_status IN VARCHAR2,
1183 x_fee_type IN VARCHAR2,
1184 x_fee_cal_type IN VARCHAR2,
1185 x_fee_ci_sequence_number IN NUMBER,
1186 x_ancillary_attribute1 IN VARCHAR2,
1187 x_ancillary_attribute2 IN VARCHAR2,
1188 x_ancillary_attribute3 IN VARCHAR2,
1189 x_ancillary_attribute4 IN VARCHAR2,
1190 x_ancillary_attribute5 IN VARCHAR2,
1191 x_ancillary_attribute6 IN VARCHAR2,
1192 x_ancillary_attribute7 IN VARCHAR2,
1193 x_ancillary_attribute8 IN VARCHAR2,
1194 x_ancillary_attribute9 IN VARCHAR2,
1195 x_ancillary_attribute10 IN VARCHAR2,
1196 x_ancillary_attribute11 IN VARCHAR2,
1197 x_ancillary_attribute12 IN VARCHAR2,
1198 x_ancillary_attribute13 IN VARCHAR2,
1199 x_ancillary_attribute14 IN VARCHAR2,
1200 x_ancillary_attribute15 IN VARCHAR2,
1201 x_attribute_category IN VARCHAR2,
1202 x_attribute1 IN VARCHAR2,
1203 x_attribute2 IN VARCHAR2,
1204 x_attribute3 IN VARCHAR2,
1205 x_attribute4 IN VARCHAR2,
1206 x_attribute5 IN VARCHAR2,
1207 x_attribute6 IN VARCHAR2,
1208 x_attribute7 IN VARCHAR2,
1209 x_attribute8 IN VARCHAR2,
1210 x_attribute9 IN VARCHAR2,
1211 x_attribute10 IN VARCHAR2,
1212 x_attribute11 IN VARCHAR2,
1213 x_attribute12 IN VARCHAR2,
1214 x_attribute13 IN VARCHAR2,
1215 x_attribute14 IN VARCHAR2,
1216 x_attribute15 IN VARCHAR2,
1217 x_attribute16 IN VARCHAR2,
1218 x_attribute17 IN VARCHAR2,
1219 x_attribute18 IN VARCHAR2,
1220 x_attribute19 IN VARCHAR2,
1221 x_attribute20 IN VARCHAR2,
1222 x_effective_dt IN DATE,
1223 x_error_msg IN VARCHAR2,
1224 x_validation_flag IN VARCHAR2,
1225 x_mode IN VARCHAR2
1226 ) AS
1227 /*
1228 || Created By : [email protected]
1229 || Created On : 09-APR-2001
1230 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1231 || Known limitations, enhancements or remarks :
1232 || Change History :
1233 || Who When What
1234 || smadathi 06-nov-2002 Enh. Bug 2584986. Removed Obsoleted
1238 CURSOR c1 IS
1235 || columns as specified in the TD. removed all DEFAULT clause
1236 || (reverse chronological order - newest change first)
1237 */
1239 SELECT rowid
1240 FROM igs_fi_anc_int_all
1241 WHERE ancillary_int_id = x_ancillary_int_id;
1242
1243 BEGIN
1244
1245 OPEN c1;
1246 FETCH c1 INTO x_rowid;
1247 IF (c1%NOTFOUND) THEN
1248 CLOSE c1;
1249
1250 insert_row (
1251 x_rowid,
1252 x_ancillary_int_id,
1253 x_person_id,
1254 x_person_id_type,
1255 x_api_person_id,
1256 x_status,
1257 x_fee_type,
1258 x_fee_cal_type,
1259 x_fee_ci_sequence_number,
1260 x_ancillary_attribute1,
1261 x_ancillary_attribute2,
1262 x_ancillary_attribute3,
1263 x_ancillary_attribute4,
1264 x_ancillary_attribute5,
1265 x_ancillary_attribute6,
1266 x_ancillary_attribute7,
1267 x_ancillary_attribute8,
1268 x_ancillary_attribute9,
1269 x_ancillary_attribute10,
1270 x_ancillary_attribute11,
1271 x_ancillary_attribute12,
1272 x_ancillary_attribute13,
1273 x_ancillary_attribute14,
1274 x_ancillary_attribute15,
1275 x_attribute_category,
1276 x_attribute1,
1277 x_attribute2,
1278 x_attribute3,
1279 x_attribute4,
1280 x_attribute5,
1281 x_attribute6,
1282 x_attribute7,
1283 x_attribute8,
1284 x_attribute9,
1285 x_attribute10,
1286 x_attribute11,
1287 x_attribute12,
1288 x_attribute13,
1289 x_attribute14,
1290 x_attribute15,
1291 x_attribute16,
1292 x_attribute17,
1293 x_attribute18,
1294 x_attribute19,
1295 x_attribute20,
1296 x_effective_dt,
1297 x_error_msg,
1298 x_validation_flag,
1299 x_mode
1300 );
1301 RETURN;
1302 END IF;
1303 CLOSE c1;
1304
1305 update_row (
1306 x_rowid,
1307 x_ancillary_int_id,
1308 x_person_id,
1309 x_person_id_type,
1310 x_api_person_id,
1311 x_status,
1312 x_fee_type,
1313 x_fee_cal_type,
1314 x_fee_ci_sequence_number,
1315 x_ancillary_attribute1,
1316 x_ancillary_attribute2,
1317 x_ancillary_attribute3,
1318 x_ancillary_attribute4,
1319 x_ancillary_attribute5,
1320 x_ancillary_attribute6,
1321 x_ancillary_attribute7,
1322 x_ancillary_attribute8,
1323 x_ancillary_attribute9,
1324 x_ancillary_attribute10,
1325 x_ancillary_attribute11,
1326 x_ancillary_attribute12,
1327 x_ancillary_attribute13,
1328 x_ancillary_attribute14,
1329 x_ancillary_attribute15,
1330 x_attribute_category,
1331 x_attribute1,
1332 x_attribute2,
1333 x_attribute3,
1334 x_attribute4,
1335 x_attribute5,
1336 x_attribute6,
1337 x_attribute7,
1338 x_attribute8,
1339 x_attribute9,
1340 x_attribute10,
1341 x_attribute11,
1342 x_attribute12,
1343 x_attribute13,
1344 x_attribute14,
1345 x_attribute15,
1346 x_attribute16,
1347 x_attribute17,
1348 x_attribute18,
1352 x_error_msg,
1349 x_attribute19,
1350 x_attribute20,
1351 x_effective_dt,
1353 x_validation_flag,
1354 x_mode
1355 );
1356
1357 l_rowid := NULL;
1358
1359 END add_row;
1360
1361
1362 PROCEDURE delete_row (
1363 x_rowid IN VARCHAR2
1364 ) AS
1365 /*
1366 || Created By : [email protected]
1367 || Created On : 09-APR-2001
1368 || Purpose : Handles the DELETE DML logic for the table.
1369 || Known limitations, enhancements or remarks :
1370 || Change History :
1371 || Who When What
1372 || (reverse chronological order - newest change first)
1373 */
1374 BEGIN
1375
1376 before_dml (
1377 p_action => 'DELETE',
1378 x_rowid => x_rowid
1379 );
1380
1381 DELETE FROM igs_fi_anc_int_all
1382 WHERE rowid = x_rowid;
1383
1384 IF (SQL%NOTFOUND) THEN
1385 RAISE NO_DATA_FOUND;
1386 END IF;
1387
1388 l_rowid := NULL;
1389
1390 END delete_row;
1391
1392
1393 END igs_fi_anc_int_pkg;