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