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