[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_APPL_LTR_PKG
Source
1 package body IGS_AD_APPL_LTR_PKG as
2 /* $Header: IGSAI06B.pls 115.4 2002/11/28 21:54:11 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AD_APPL_LTR%RowType;
5 new_references IGS_AD_APPL_LTR%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_person_id IN NUMBER DEFAULT NULL,
11 x_admission_appl_number IN NUMBER DEFAULT NULL,
12 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
13 x_sequence_number IN NUMBER DEFAULT NULL,
14 x_composed_ind IN VARCHAR2 DEFAULT NULL,
15 x_letter_reference_number IN NUMBER DEFAULT NULL,
16 x_spl_sequence_number IN NUMBER DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL
22 ) as
23
24 CURSOR cur_old_ref_values IS
25 SELECT *
26 FROM IGS_AD_APPL_LTR
27 WHERE rowid = x_rowid;
28
29 BEGIN
30
31 l_rowid := x_rowid;
32
33 -- Code for setting the Old and New Reference Values.
34 -- Populate Old Values.
35 Open cur_old_ref_values;
36 Fetch cur_old_ref_values INTO old_references;
37 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
38 Close cur_old_ref_values;
39 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
40 IGS_GE_MSG_STACK.ADD;
41 App_Exception.Raise_Exception;
42 Return;
43 END IF;
44 Close cur_old_ref_values;
45
46 -- Populate New Values.
47 new_references.person_id := x_person_id;
48 new_references.admission_appl_number := x_admission_appl_number;
49 new_references.correspondence_type := x_correspondence_type;
50 new_references.sequence_number := x_sequence_number;
51 new_references.composed_ind := x_composed_ind;
52 new_references.letter_reference_number := x_letter_reference_number;
53 new_references.spl_sequence_number := x_spl_sequence_number;
54 IF (p_action = 'UPDATE') THEN
55 new_references.creation_date := old_references.creation_date;
56 new_references.created_by := old_references.created_by;
57 ELSE
58 new_references.creation_date := x_creation_date;
59 new_references.created_by := x_created_by;
60 END IF;
61 new_references.last_update_date := x_last_update_date;
62 new_references.last_updated_by := x_last_updated_by;
63 new_references.last_update_login := x_last_update_login;
64
65 END Set_Column_Values;
66
67 -- Trigger description :-
68 -- "OSS_TST".trg_aal_br_iu
69 -- BEFORE INSERT OR UPDATE
70 -- ON IGS_AD_APPL_LTR
71 -- FOR EACH ROW
72
73 PROCEDURE BeforeRowInsertUpdate1(
74 p_inserting IN BOOLEAN DEFAULT FALSE,
75 p_updating IN BOOLEAN DEFAULT FALSE,
76 p_deleting IN BOOLEAN DEFAULT FALSE
77 ) as
78 v_admission_cat IGS_AD_APPL.admission_cat%TYPE;
79 v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
80 v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
81 v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
82 v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
83 v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
84 v_appl_dt IGS_AD_APPL.appl_dt%TYPE;
85 v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
86 v_adm_fee_status IGS_AD_APPL.adm_fee_status%TYPE;
87 v_message_name VARCHAR2(30);
88 v_issue_dt DATE;
89 BEGIN
90 -- Validate correspondence type
91 IF p_inserting THEN
92 IF IGS_AD_VAL_AAL.corp_val_cort_closed(new_references.correspondence_type,
93 v_message_name) = FALSE THEN
94 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
95 FND_MESSAGE.SET_NAME('IGS',v_message_name);
96 IGS_GE_MSG_STACK.ADD;
97 APP_EXCEPTION.RAISE_EXCEPTION;
98 END IF;
99 IF IGS_AD_VAL_AAL.admp_val_aal_exists(new_references.person_id,
100 new_references.admission_appl_number,
101 new_references.correspondence_type,
102 v_message_name) = FALSE THEN
103 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
104 FND_MESSAGE.SET_NAME('IGS',v_message_name);
105 IGS_GE_MSG_STACK.ADD;
106 APP_EXCEPTION.RAISE_EXCEPTION;
107 END IF;
108 IGS_AD_GEN_002.ADMP_GET_AA_DTL(new_references.person_id,
109 new_references.admission_appl_number,
110 v_admission_cat,
111 v_s_admission_process_type,
112 v_acad_cal_type,
113 v_acad_ci_sequence_number,
114 v_adm_cal_type,
115 v_adm_ci_sequence_number,
116 v_appl_dt ,
117 v_adm_appl_status,
118 v_adm_fee_status);
119 IF IGS_AD_VAL_AAL.admp_val_aal_cort(new_references.correspondence_type,
120 v_admission_cat,
121 v_s_admission_process_type,
122 v_message_name) = FALSE THEN
123 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
124 FND_MESSAGE.SET_NAME('IGS',v_message_name);
125 IGS_GE_MSG_STACK.ADD;
126 APP_EXCEPTION.RAISE_EXCEPTION;
127 END IF;
128 END IF;
129 IF ( new_references.composed_ind <> old_references.composed_ind) AND
130 new_references.spl_sequence_number IS NOT NULL THEN
131 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(3086));
132 FND_MESSAGE.SET_NAME('IGS','IGS_AD_CANNOT_ALTER_LETTER');
133 IGS_GE_MSG_STACK.ADD;
134 APP_EXCEPTION.RAISE_EXCEPTION;
135 END IF;
136
137
138 END BeforeRowInsertUpdate1;
139
140 PROCEDURE Check_Parent_Existance as
141 BEGIN
142
143 IF (((old_references.person_id = new_references.person_id) AND
144 (old_references.admission_appl_number = new_references.admission_appl_number)) OR
145 ((new_references.person_id IS NULL) OR
146 (new_references.admission_appl_number IS NULL))) THEN
147 NULL;
148 ELSE
149 IF NOT IGS_AD_APPL_PKG.Get_PK_For_Validation (
150 new_references.person_id,
151 new_references.admission_appl_number
152 )THEN
153 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
154 IGS_GE_MSG_STACK.ADD;
155 APP_EXCEPTION.RAISE_EXCEPTION;
156 END IF;
157 END IF;
158
159 IF (((old_references.correspondence_type = new_references.correspondence_type)) OR
160 ((new_references.correspondence_type IS NULL))) THEN
161 NULL;
162 ELSE
163 IF NOT IGS_CO_TYPE_PKG.Get_PK_For_Validation (
164 new_references.correspondence_type
165 )THEN
166 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
167 IGS_GE_MSG_STACK.ADD;
168 APP_EXCEPTION.RAISE_EXCEPTION;
169 END IF;
170
171 END IF;
172
173 IF (((old_references.person_id = new_references.person_id) AND
174 (old_references.correspondence_type = new_references.correspondence_type) AND
175 (old_references.letter_reference_number = new_references.letter_reference_number) AND
176 (old_references.spl_sequence_number = new_references.spl_sequence_number)) OR
177 ((new_references.person_id IS NULL) OR
178 (new_references.correspondence_type IS NULL) OR
179 (new_references.letter_reference_number IS NULL) OR
180 (new_references.spl_sequence_number IS NULL))) THEN
181 NULL;
182 ELSE
183 IF NOT IGS_CO_S_PER_LTR_PKG.Get_PK_For_Validation(
184 new_references.person_id,new_references.correspondence_type,new_references.letter_reference_number,
185 new_references.spl_sequence_number )THEN
186 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
187 IGS_GE_MSG_STACK.ADD;
188 APP_EXCEPTION.RAISE_EXCEPTION;
189 END IF;
190
191 END IF;
192
193
194 END Check_Parent_Existance;
195
196 PROCEDURE Check_Child_Existance as
197 BEGIN
198
199 IGS_AD_APPL_LTR_PHR_PKG.GET_FK_IGS_AD_APPL_LTR (
200 old_references.person_id,
201 old_references.admission_appl_number,
202 old_references.correspondence_type,
203 old_references.sequence_number
204 );
205
206 END Check_Child_Existance;
207
208 FUNCTION Get_PK_For_Validation (
209 x_person_id IN NUMBER,
210 x_admission_appl_number IN NUMBER,
211 x_correspondence_type IN VARCHAR2,
212 x_sequence_number IN NUMBER
213 )
214 RETURN BOOLEAN as
215
216 CURSOR cur_rowid IS
217 SELECT rowid
218 FROM IGS_AD_APPL_LTR
219 WHERE person_id = x_person_id
220 AND admission_appl_number = x_admission_appl_number
221 AND correspondence_type = x_correspondence_type
222 AND sequence_number = x_sequence_number
223 FOR UPDATE NOWAIT;
224
225 lv_rowid cur_rowid%RowType;
226
227 BEGIN
228
229 Open cur_rowid;
230 Fetch cur_rowid INTO lv_rowid;
231 IF (cur_rowid%FOUND) THEN
232 Close cur_rowid;
233 Return TRUE;
234 ELSE
235 Close cur_rowid;
236 Return FALSE;
237 END IF;
238
239 END Get_PK_For_Validation;
240
241 PROCEDURE GET_FK_IGS_AD_APPL (
242 x_person_id IN NUMBER,
243 x_admission_appl_number IN NUMBER
244 ) as
245
246 CURSOR cur_rowid IS
247 SELECT rowid
248 FROM IGS_AD_APPL_LTR
249 WHERE person_id = x_person_id
250 AND admission_appl_number = x_admission_appl_number ;
251
252 lv_rowid cur_rowid%RowType;
253
254 BEGIN
255
256 Open cur_rowid;
257 Fetch cur_rowid INTO lv_rowid;
258 IF (cur_rowid%FOUND) THEN
259 Close cur_rowid;
260 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAL_AA_FK');
261 IGS_GE_MSG_STACK.ADD;
262 App_Exception.Raise_Exception;
263 Return;
264 END IF;
265 Close cur_rowid;
266
267 END GET_FK_IGS_AD_APPL;
268
269 PROCEDURE GET_FK_IGS_CO_TYPE (
270 x_correspondence_type IN VARCHAR2
271 ) as
272
273 CURSOR cur_rowid IS
274 SELECT rowid
275 FROM IGS_AD_APPL_LTR
276 WHERE correspondence_type = x_correspondence_type ;
277
278 lv_rowid cur_rowid%RowType;
279
280 BEGIN
281
282 Open cur_rowid;
283 Fetch cur_rowid INTO lv_rowid;
284 IF (cur_rowid%FOUND) THEN
285 Close cur_rowid;
286 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAL_CORT_FK');
287 IGS_GE_MSG_STACK.ADD;
288 App_Exception.Raise_Exception;
289 Return;
290 END IF;
291 Close cur_rowid;
292
293 END GET_FK_IGS_CO_TYPE;
294
295 PROCEDURE GET_FK_IGS_CO_S_PER_LTR (
296 x_person_id IN NUMBER,
297 x_correspondence_type IN VARCHAR2,
298 x_letter_reference_number IN NUMBER,
299 x_sequence_number IN NUMBER
300 ) as
301
302 CURSOR cur_rowid IS
303 SELECT rowid
304 FROM IGS_AD_APPL_LTR
305 WHERE person_id = x_person_id
306 AND correspondence_type = x_correspondence_type
307 AND letter_reference_number = x_letter_reference_number
308 AND spl_sequence_number = x_sequence_number ;
309
310 lv_rowid cur_rowid%RowType;
311
312 BEGIN
313
314 Open cur_rowid;
315 Fetch cur_rowid INTO lv_rowid;
316 IF (cur_rowid%FOUND) THEN
317 Close cur_rowid;
318 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAL_SPL_FK');
319 IGS_GE_MSG_STACK.ADD;
320 App_Exception.Raise_Exception;
321 Return;
322 END IF;
323 Close cur_rowid;
324
325 END GET_FK_IGS_CO_S_PER_LTR ;
326
327 -- procedure to check constraints
328 PROCEDURE CHECK_CONSTRAINTS(
329 column_name IN VARCHAR2 DEFAULT NULL,
330 column_value IN VARCHAR2 DEFAULT NULL
331 ) as
332 BEGIN
333 IF column_name is null THEN
334 NULL;
335 ELSIF upper(column_name) = 'COMPOSED_IND' THEN
336 new_references.composed_ind := column_value;
337 ELSIF upper(column_name) = 'CORRESPONDENCE_TYPE' THEN
338 new_references.correspondence_type := column_value;
339 ELSIF upper(column_name) = 'SPL_SEQUENCE_NUMBER' THEN
340 new_references.spl_sequence_number := igs_ge_number.to_num(column_value);
341 END IF;
342
343 IF upper(column_name) = 'COMPOSED_IND' OR column_name IS NULL THEN
344 IF new_references.composed_ind NOT IN ('Y','N') THEN
345 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
346 IGS_GE_MSG_STACK.ADD;
347 APP_EXCEPTION.RAISE_EXCEPTION;
348 END IF;
349 END IF;
350 IF upper(column_name) = 'SPL_SEQUENCE_NUMBER' OR column_name IS NULL THEN
351 IF new_references.spl_sequence_number < 1 OR new_references.spl_sequence_number > 9999999999 THEN
352 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
353 IGS_GE_MSG_STACK.ADD;
354 APP_EXCEPTION.RAISE_EXCEPTION;
355 END IF;
356 END IF;
357
358 IF upper(column_name) = 'COMPOSED_IND' OR column_name IS NULL THEN
359 IF new_references.composed_ind <> UPPER(new_references.composed_ind) THEN
360 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
361 IGS_GE_MSG_STACK.ADD;
362 APP_EXCEPTION.RAISE_EXCEPTION;
363 END IF;
364 END IF;
365 IF upper(column_name) = 'CORRESPONDENCE_TYPE' OR column_name IS NULL THEN
366 IF new_references.correspondence_type <> UPPER(new_references.correspondence_type) THEN
367 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
368 IGS_GE_MSG_STACK.ADD;
369 APP_EXCEPTION.RAISE_EXCEPTION;
370 END IF;
371 END IF;
372 END CHECK_CONSTRAINTS;
376 p_action IN VARCHAR2,
373
374
375 PROCEDURE Before_DML (
377 x_rowid IN VARCHAR2 DEFAULT NULL,
378 x_person_id IN NUMBER DEFAULT NULL,
379 x_admission_appl_number IN NUMBER DEFAULT NULL,
380 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
381 x_sequence_number IN NUMBER DEFAULT NULL,
382 x_composed_ind IN VARCHAR2 DEFAULT NULL,
383 x_letter_reference_number IN NUMBER DEFAULT NULL,
384 x_spl_sequence_number IN NUMBER DEFAULT NULL,
385 x_creation_date IN DATE DEFAULT NULL,
386 x_created_by IN NUMBER DEFAULT NULL,
387 x_last_update_date IN DATE DEFAULT NULL,
388 x_last_updated_by IN NUMBER DEFAULT NULL,
389 x_last_update_login IN NUMBER DEFAULT NULL
390 ) AS
391 BEGIN
392
393 Set_Column_Values (
394 p_action,
395 x_rowid,
396 x_person_id,
397 x_admission_appl_number,
398 x_correspondence_type,
399 x_sequence_number,
400 x_composed_ind,
401 x_letter_reference_number,
402 x_spl_sequence_number,
403 x_creation_date,
404 x_created_by,
405 x_last_update_date,
406 x_last_updated_by,
407 x_last_update_login
408 );
409
410 IF (p_action = 'INSERT') THEN
411 -- Call all the procedures related to Before Insert.
412 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
413
414 IF GET_PK_FOR_VALIDATION(
415 new_references.person_id,
416 new_references.admission_appl_number,
417 new_references.correspondence_type,
418 new_references.sequence_number
419 )THEN
420 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
421 IGS_GE_MSG_STACK.ADD;
422 APP_EXCEPTION.RAISE_EXCEPTION;
423 END IF;
424 Check_Constraints;
425 Check_Parent_Existance;
426 ELSIF (p_action = 'UPDATE') THEN
427 -- Call all the procedures related to Before Update.
428 BeforeRowInsertUpdate1 ( p_updating => TRUE );
429
430 Check_Constraints;
431 Check_Parent_Existance;
432 ELSIF (p_action = 'DELETE') THEN
433 -- Call all the procedures related to Before Delete.
434 Check_Child_Existance;
435 ELSIF (p_action = 'VALIDATE_INSERT') THEN
436 -- Call all the procedures related to Before Delete.
437 IF GET_PK_FOR_VALIDATION(
438 new_references.person_id,
439 new_references.admission_appl_number,
440 new_references.correspondence_type,
441 new_references.sequence_number
442 )THEN
443 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
444 IGS_GE_MSG_STACK.ADD;
445 APP_EXCEPTION.RAISE_EXCEPTION;
446 END IF;
447 Check_Constraints;
448 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
449 -- Call all the procedures related to Before Delete.
450 check_constraints;
451 ELSIF (p_action = 'VALIDATE_DELETE') THEN
452 -- Call all the procedures related to Before Delete.
453 Check_Child_Existance;
454 END IF;
455
456 END Before_DML;
457
458 PROCEDURE After_DML (
459 p_action IN VARCHAR2,
460 x_rowid IN VARCHAR2
461 ) AS
462 BEGIN
463
464 l_rowid := x_rowid;
465
466 END After_DML;
467
468 procedure INSERT_ROW (
469 X_ROWID in out NOCOPY VARCHAR2,
470 X_PERSON_ID in NUMBER,
471 X_ADMISSION_APPL_NUMBER in NUMBER,
472 X_CORRESPONDENCE_TYPE in VARCHAR2,
473 X_SEQUENCE_NUMBER in NUMBER,
474 X_COMPOSED_IND in VARCHAR2,
475 X_LETTER_REFERENCE_NUMBER in NUMBER,
476 X_SPL_SEQUENCE_NUMBER in NUMBER,
477 X_MODE in VARCHAR2 default 'R'
478 ) as
479 cursor C is select ROWID from IGS_AD_APPL_LTR
480 where PERSON_ID = X_PERSON_ID
481 and ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER
482 and CORRESPONDENCE_TYPE = X_CORRESPONDENCE_TYPE
483 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
484 X_LAST_UPDATE_DATE DATE;
485 X_LAST_UPDATED_BY NUMBER;
486 X_LAST_UPDATE_LOGIN NUMBER;
487 X_REQUEST_ID NUMBER;
488 X_PROGRAM_ID NUMBER;
489 X_PROGRAM_APPLICATION_ID NUMBER;
490 X_PROGRAM_UPDATE_DATE DATE;
491 begin
492 X_LAST_UPDATE_DATE := SYSDATE;
493 if(X_MODE = 'I') then
494 X_LAST_UPDATED_BY := 1;
495 X_LAST_UPDATE_LOGIN := 0;
496 elsif (X_MODE = 'R') then
497 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
498 if X_LAST_UPDATED_BY is NULL then
499 X_LAST_UPDATED_BY := -1;
500 end if;
501 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
502 if X_LAST_UPDATE_LOGIN is NULL then
503 X_LAST_UPDATE_LOGIN := -1;
504 end if;
505 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
506 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
507 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
508 if (X_REQUEST_ID = -1) then
509 X_REQUEST_ID := NULL;
510 X_PROGRAM_ID := NULL;
511 X_PROGRAM_APPLICATION_ID := NULL;
512 X_PROGRAM_UPDATE_DATE := NULL;
513 else
514 X_PROGRAM_UPDATE_DATE := SYSDATE;
515 end if;
516 else
520 end if;
517 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
518 IGS_GE_MSG_STACK.ADD;
519 app_exception.raise_exception;
521
522 Before_DML (
523 p_action => 'INSERT',
524 x_rowid => X_ROWID,
525 x_person_id=> X_PERSON_ID,
526 x_admission_appl_number=> X_ADMISSION_APPL_NUMBER,
527 x_correspondence_type =>X_CORRESPONDENCE_TYPE,
528 x_sequence_number =>X_SEQUENCE_NUMBER,
529 x_composed_ind=> Nvl(X_COMPOSED_IND, 'Y'),
530 x_letter_reference_number =>X_LETTER_REFERENCE_NUMBER,
531 x_spl_sequence_number =>X_SPL_SEQUENCE_NUMBER,
532 x_creation_date =>X_LAST_UPDATE_DATE,
533 x_created_by =>X_LAST_UPDATED_BY,
534 x_last_update_date =>X_LAST_UPDATE_DATE,
535 x_last_updated_by =>X_LAST_UPDATED_BY,
536 x_last_update_login=> X_LAST_UPDATE_LOGIN
537 );
538
539 insert into IGS_AD_APPL_LTR (
540 PERSON_ID,
541 ADMISSION_APPL_NUMBER,
542 CORRESPONDENCE_TYPE,
543 SEQUENCE_NUMBER,
544 COMPOSED_IND,
545 LETTER_REFERENCE_NUMBER,
546 SPL_SEQUENCE_NUMBER,
547 CREATION_DATE,
548 CREATED_BY,
549 LAST_UPDATE_DATE,
550 LAST_UPDATED_BY,
551 LAST_UPDATE_LOGIN,
552 REQUEST_ID,
553 PROGRAM_ID,
554 PROGRAM_APPLICATION_ID,
555 PROGRAM_UPDATE_DATE
556 ) values (
557 NEW_REFERENCES.PERSON_ID,
558 NEW_REFERENCES.ADMISSION_APPL_NUMBER,
559 NEW_REFERENCES.CORRESPONDENCE_TYPE,
560 NEW_REFERENCES.SEQUENCE_NUMBER,
561 NEW_REFERENCES.COMPOSED_IND,
562 NEW_REFERENCES.LETTER_REFERENCE_NUMBER,
563 NEW_REFERENCES.SPL_SEQUENCE_NUMBER,
564 X_LAST_UPDATE_DATE,
565 X_LAST_UPDATED_BY,
566 X_LAST_UPDATE_DATE,
567 X_LAST_UPDATED_BY,
568 X_LAST_UPDATE_LOGIN,
569 X_REQUEST_ID,
570 X_PROGRAM_ID,
571 X_PROGRAM_APPLICATION_ID,
572 X_PROGRAM_UPDATE_DATE
573 );
574
575 open c;
576 fetch c into X_ROWID;
577 if (c%notfound) then
578 close c;
579 raise no_data_found;
580 end if;
581 close c;
582 After_DML (
583 p_action => 'INSERT',
584 x_rowid => X_ROWID
585 );
586
587 end INSERT_ROW;
588
589 procedure LOCK_ROW (
590 X_ROWID in VARCHAR2,
591 X_PERSON_ID in NUMBER,
592 X_ADMISSION_APPL_NUMBER in NUMBER,
593 X_CORRESPONDENCE_TYPE in VARCHAR2,
594 X_SEQUENCE_NUMBER in NUMBER,
595 X_COMPOSED_IND in VARCHAR2,
596 X_LETTER_REFERENCE_NUMBER in NUMBER,
597 X_SPL_SEQUENCE_NUMBER in NUMBER
598 ) as
599 cursor c1 is select
600 COMPOSED_IND,
601 LETTER_REFERENCE_NUMBER,
602 SPL_SEQUENCE_NUMBER
603 from IGS_AD_APPL_LTR
604 where ROWID = X_ROWID
605 for update nowait;
606 tlinfo c1%rowtype;
607
608 begin
609 open c1;
610 fetch c1 into tlinfo;
611 if (c1%notfound) then
612 close c1;
613 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
614 IGS_GE_MSG_STACK.ADD;
615 app_exception.raise_exception;
616 return;
617 end if;
618 close c1;
619
620 if ( (tlinfo.COMPOSED_IND = X_COMPOSED_IND)
621 AND ((tlinfo.LETTER_REFERENCE_NUMBER = X_LETTER_REFERENCE_NUMBER)
622 OR ((tlinfo.LETTER_REFERENCE_NUMBER is null)
623 AND (X_LETTER_REFERENCE_NUMBER is null)))
624 AND ((tlinfo.SPL_SEQUENCE_NUMBER = X_SPL_SEQUENCE_NUMBER)
625 OR ((tlinfo.SPL_SEQUENCE_NUMBER is null)
626 AND (X_SPL_SEQUENCE_NUMBER is null)))
627 ) then
628 null;
629 else
630 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
631 IGS_GE_MSG_STACK.ADD;
632 app_exception.raise_exception;
633 end if;
634 return;
635 end LOCK_ROW;
636
637 procedure UPDATE_ROW (
638 X_ROWID in VARCHAR2,
639 X_PERSON_ID in NUMBER,
640 X_ADMISSION_APPL_NUMBER in NUMBER,
641 X_CORRESPONDENCE_TYPE in VARCHAR2,
642 X_SEQUENCE_NUMBER in NUMBER,
643 X_COMPOSED_IND in VARCHAR2,
644 X_LETTER_REFERENCE_NUMBER in NUMBER,
645 X_SPL_SEQUENCE_NUMBER in NUMBER,
646 X_MODE in VARCHAR2 default 'R'
647 ) as
648 X_LAST_UPDATE_DATE DATE;
649 X_LAST_UPDATED_BY NUMBER;
650 X_LAST_UPDATE_LOGIN NUMBER;
651 X_REQUEST_ID NUMBER;
652 X_PROGRAM_ID NUMBER;
653 X_PROGRAM_APPLICATION_ID NUMBER;
654 X_PROGRAM_UPDATE_DATE DATE;
655 begin
656 X_LAST_UPDATE_DATE := SYSDATE;
657 if(X_MODE = 'I') then
658 X_LAST_UPDATED_BY := 1;
659 X_LAST_UPDATE_LOGIN := 0;
660 elsif (X_MODE = 'R') then
661 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
662 if X_LAST_UPDATED_BY is NULL then
663 X_LAST_UPDATED_BY := -1;
664 end if;
665 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
666 if X_LAST_UPDATE_LOGIN is NULL then
667 X_LAST_UPDATE_LOGIN := -1;
668 end if;
669 else
670 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
671 IGS_GE_MSG_STACK.ADD;
672 app_exception.raise_exception;
673 end if;
674
675 Before_DML (
676 p_action => 'UPDATE',
677 x_rowid => X_ROWID,
678 x_person_id=> X_PERSON_ID,
679 x_admission_appl_number=> X_ADMISSION_APPL_NUMBER,
680 x_correspondence_type =>X_CORRESPONDENCE_TYPE,
681 x_sequence_number =>X_SEQUENCE_NUMBER,
682 x_composed_ind=> X_COMPOSED_IND,
683 x_letter_reference_number =>X_LETTER_REFERENCE_NUMBER,
684 x_spl_sequence_number =>X_SPL_SEQUENCE_NUMBER,
685 x_creation_date =>X_LAST_UPDATE_DATE,
686 x_created_by =>X_LAST_UPDATED_BY,
687 x_last_update_date =>X_LAST_UPDATE_DATE,
688 x_last_updated_by =>X_LAST_UPDATED_BY,
689 x_last_update_login=> X_LAST_UPDATE_LOGIN
690 );
691
692
693 if (X_MODE = 'R') then
694 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
695 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
696 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
697 if (X_REQUEST_ID = -1) then
701 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
698 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
699 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
700 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
702 else
703 X_PROGRAM_UPDATE_DATE := SYSDATE;
704 end if;
705 end if;
706 update IGS_AD_APPL_LTR set
707 COMPOSED_IND = NEW_REFERENCES.COMPOSED_IND,
708 LETTER_REFERENCE_NUMBER = NEW_REFERENCES.LETTER_REFERENCE_NUMBER,
709 SPL_SEQUENCE_NUMBER = NEW_REFERENCES.SPL_SEQUENCE_NUMBER,
710 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
711 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
712 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
713 REQUEST_ID = X_REQUEST_ID,
714 PROGRAM_ID = X_PROGRAM_ID,
715 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
716 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
717 where ROWID = X_ROWID
718 ;
719 if (sql%notfound) then
720 raise no_data_found;
721 end if;
722 After_DML (
723 p_action => 'UPDATE',
724 x_rowid => X_ROWID
725 );
726
727 end UPDATE_ROW;
728
729 procedure ADD_ROW (
730 X_ROWID in out NOCOPY VARCHAR2,
731 X_PERSON_ID in NUMBER,
732 X_ADMISSION_APPL_NUMBER in NUMBER,
733 X_CORRESPONDENCE_TYPE in VARCHAR2,
734 X_SEQUENCE_NUMBER in NUMBER,
735 X_COMPOSED_IND in VARCHAR2,
736 X_LETTER_REFERENCE_NUMBER in NUMBER,
737 X_SPL_SEQUENCE_NUMBER in NUMBER,
738 X_MODE in VARCHAR2 default 'R'
739 ) as
740 cursor c1 is select rowid from IGS_AD_APPL_LTR
741 where PERSON_ID = X_PERSON_ID
742 and ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER
743 and CORRESPONDENCE_TYPE = X_CORRESPONDENCE_TYPE
744 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
745 ;
746 begin
747 open c1;
748 fetch c1 into X_ROWID;
749 if (c1%notfound) then
750 close c1;
751 INSERT_ROW (
752 X_ROWID,
753 X_PERSON_ID,
754 X_ADMISSION_APPL_NUMBER,
755 X_CORRESPONDENCE_TYPE,
756 X_SEQUENCE_NUMBER,
757 X_COMPOSED_IND,
758 X_LETTER_REFERENCE_NUMBER,
759 X_SPL_SEQUENCE_NUMBER,
760 X_MODE);
761 return;
762 end if;
763 close c1;
764 UPDATE_ROW (
765 X_ROWID,
766 X_PERSON_ID,
767 X_ADMISSION_APPL_NUMBER,
768 X_CORRESPONDENCE_TYPE,
769 X_SEQUENCE_NUMBER,
770 X_COMPOSED_IND,
771 X_LETTER_REFERENCE_NUMBER,
772 X_SPL_SEQUENCE_NUMBER,
773 X_MODE);
774 end ADD_ROW;
775
776 procedure DELETE_ROW (
777 X_ROWID in VARCHAR2
778 )as
779 begin
780 Before_DML (
781 p_action => 'DELETE',
782 x_rowid => X_ROWID
783 );
784
785 delete from IGS_AD_APPL_LTR
786 where ROWID = X_ROWID;
787 if (sql%notfound) then
791 p_action => 'DELETE',
788 raise no_data_found;
789 end if;
790 After_DML (
792 x_rowid => X_ROWID
793 );
794
795 end DELETE_ROW;
796
797 end IGS_AD_APPL_LTR_PKG;