1 PACKAGE BODY igs_co_ltr_pr_rstcn_pkg AS
2 /* $Header: IGSLI12B.pls 115.9 2002/11/29 01:05:15 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_co_ltr_pr_rstcn_all%ROWTYPE;
6 new_references igs_co_ltr_pr_rstcn_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_org_id IN NUMBER DEFAULT NULL,
12 x_letter_parameter_type IN VARCHAR2 DEFAULT NULL,
13 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL
19 ) AS
20 /*
21 || Created By : [email protected]
22 || Created On : 14-DEC-2000
23 || Purpose : Initialises the Old and New references for the columns of the table.
24 || Known limitations, enhancements or remarks :
25 || Change History :
26 || Who When What
27 || (reverse chronological order - newest change first)
28 */
29
30 CURSOR cur_old_ref_values IS
31 SELECT *
32 FROM IGS_CO_LTR_PR_RSTCN_ALL
33 WHERE rowid = x_rowid;
34
35 BEGIN
36
37 l_rowid := x_rowid;
38
39 -- Code for setting the Old and New Reference Values.
40 -- Populate Old Values.
41 OPEN cur_old_ref_values;
42 FETCH cur_old_ref_values INTO old_references;
43 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
44 CLOSE cur_old_ref_values;
45 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
46 igs_ge_msg_stack.add;
47 app_exception.raise_exception;
48 RETURN;
49 END IF;
50 CLOSE cur_old_ref_values;
51
52 -- Populate New Values.
53 new_references.org_id := x_org_id;
54 new_references.letter_parameter_type := x_letter_parameter_type;
55 new_references.correspondence_type := x_correspondence_type;
56
57 IF (p_action = 'UPDATE') THEN
58 new_references.creation_date := old_references.creation_date;
59 new_references.created_by := old_references.created_by;
60 ELSE
61 new_references.creation_date := x_creation_date;
62 new_references.created_by := x_created_by;
63 END IF;
64
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 PROCEDURE BeforeRowInsert1(
72 p_inserting IN BOOLEAN DEFAULT FALSE,
73 p_updating IN BOOLEAN DEFAULT FALSE,
74 p_deleting IN BOOLEAN DEFAULT FALSE
75 ) AS
76 v_message_name varchar2(30);
77 v_s_letter_parameter_type IGS_CO_LTR_PARM_TYPE.s_letter_parameter_type%TYPE;
78 CURSOR c_lpt (cp_letter_parameter_type
79 IGS_CO_LTR_PARM_TYPE.letter_parameter_type %TYPE) IS
80 SELECT s_letter_parameter_type
81 FROM IGS_CO_LTR_PARM_TYPE
82 WHERE letter_parameter_type = cp_letter_parameter_type;
83 BEGIN
84 IF p_inserting THEN
85 -- Validate Letter Parameter Type closed.
86 IF igs_ad_val_aalp.corp_val_lpt_closed(
87 new_references.letter_parameter_type,
88 v_message_name) = FALSE THEN
89 Fnd_Message.Set_Name('IGS',v_message_name);
90 IGS_GE_MSG_STACK.ADD;
91 App_Exception.Raise_Exception;
92 END IF;
93 -- Validate Correspondence Type closed.
94 IF igs_ad_val_aal.corp_val_cort_closed(
95 new_references.correspondence_type,
96 v_message_name) = FALSE THEN
97 Fnd_Message.Set_Name('IGS',v_message_name);
98 IGS_GE_MSG_STACK.ADD;
99 App_Exception.Raise_Exception;
100 END IF;
101 -- Validate that no System Letter Parameter Type Restrictions exists
102 -- that conflicts with the letter parameter type being restricted to a
103 -- particular correspondence type.
104 OPEN c_lpt(new_references.letter_parameter_type);
105 FETCH c_lpt INTO v_s_letter_parameter_type;
106 CLOSE c_lpt;
107 IF IGS_CO_VAL_LPTR.corp_val_slptr_rstrn(
108 v_s_letter_parameter_type,
109 new_references.correspondence_type,
110 v_message_name) = FALSE THEN
111 Fnd_Message.Set_Name('IGS',v_message_name);
112 IGS_GE_MSG_STACK.ADD;
113 App_Exception.Raise_Exception;
114 END IF;
115 END IF;
116
117
118 END BeforeRowInsert1;
119
120 PROCEDURE Check_Constraints (
121 Column_Name IN VARCHAR2 DEFAULT NULL,
122 Column_Value IN VARCHAR2 DEFAULT NULL
123 ) AS
124 Begin
125 IF column_name is null then
126 NULL;
127 ELSIF upper(Column_name) = 'CORRESPONDENCE_TYPE' then
128 new_references.correspondence_type:= column_value;
129 ELSIF upper(Column_name) = 'LETTER_PARAMETER_TYPE' then
130 new_references.letter_parameter_type:= column_value;
131 END IF;
132
133 IF upper(column_name) = 'CORRESPONDENCE_TYPE' OR
134 column_name is null Then
135 IF new_references.correspondence_type <> UPPER(new_references.correspondence_type) Then
136 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
137 IGS_GE_MSG_STACK.ADD;
138 App_Exception.Raise_Exception;
139 END IF;
140 END IF;
141
142 IF upper(column_name) = 'LETTER_PARAMETER_TYPE' OR
143 column_name is null Then
144 IF new_references.letter_parameter_type <>
145 UPPER(new_references.letter_parameter_type) Then
146 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
147 IGS_GE_MSG_STACK.ADD;
148 App_Exception.Raise_Exception;
149 END IF;
150 END IF;
151
152 END Check_Constraints;
153
154
155 PROCEDURE check_parent_existance AS
156 /*
157 || Created By : [email protected]
158 || Created On : 14-DEC-2000
159 || Purpose : Checks for the existance of Parent records.
160 || Known limitations, enhancements or remarks :
161 || Change History :
162 || Who When What
163 || (reverse chronological order - newest change first)
164 */
165 BEGIN
166
167 IF (((old_references.correspondence_type = new_references.correspondence_type)) OR
168 ((new_references.correspondence_type IS NULL))) THEN
169 NULL;
170 ELSIF NOT igs_co_type_pkg.get_pk_for_validation (
171 new_references.correspondence_type
172 ) THEN
173 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
174 igs_ge_msg_stack.add;
175 app_exception.raise_exception;
176 END IF;
177
178 IF (((old_references.letter_parameter_type = new_references.letter_parameter_type)) OR
179 ((new_references.letter_parameter_type IS NULL))) THEN
180 NULL;
181 ELSIF NOT igs_co_ltr_parm_type_pkg.get_pk_for_validation (
182 new_references.letter_parameter_type
183 ) THEN
184 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
185 igs_ge_msg_stack.add;
186 app_exception.raise_exception;
187 END IF;
188
189 END check_parent_existance;
190
191
192 FUNCTION get_pk_for_validation (
193 x_letter_parameter_type IN VARCHAR2,
194 x_correspondence_type IN VARCHAR2
195 ) RETURN BOOLEAN AS
196 /*
197 || Created By : [email protected]
198 || Created On : 14-DEC-2000
199 || Purpose : Validates the Primary Key of the table.
200 || Known limitations, enhancements or remarks :
201 || Change History :
202 || Who When What
203 || (reverse chronological order - newest change first)
204 */
205 CURSOR cur_rowid IS
206 SELECT rowid
207 FROM igs_co_ltr_pr_rstcn_all
208 WHERE letter_parameter_type = x_letter_parameter_type
209 AND correspondence_type = x_correspondence_type
210 FOR UPDATE NOWAIT;
211
212 lv_rowid cur_rowid%RowType;
213
214 BEGIN
215
216 OPEN cur_rowid;
217 FETCH cur_rowid INTO lv_rowid;
218 IF (cur_rowid%FOUND) THEN
219 CLOSE cur_rowid;
220 RETURN(TRUE);
221 ELSE
222 CLOSE cur_rowid;
223 RETURN(FALSE);
224 END IF;
225
226 END get_pk_for_validation;
227
228
229 PROCEDURE get_fk_igs_co_type (
230 x_correspondence_type IN VARCHAR2
231 ) AS
232 /*
233 || Created By : [email protected]
234 || Created On : 14-DEC-2000
235 || Purpose : Validates the Foreign Keys for the table.
236 || Known limitations, enhancements or remarks :
237 || Change History :
238 || Who When What
239 || (reverse chronological order - newest change first)
240 */
241 CURSOR cur_rowid IS
242 SELECT rowid
243 FROM igs_co_ltr_pr_rstcn_all
244 WHERE ((correspondence_type = x_correspondence_type));
245
246 lv_rowid cur_rowid%RowType;
247
248 BEGIN
249
250 OPEN cur_rowid;
251 FETCH cur_rowid INTO lv_rowid;
252 IF (cur_rowid%FOUND) THEN
253 CLOSE cur_rowid;
254 fnd_message.set_name ('IGS', 'IGS_CO_CORT_LPTR_FK');
255 igs_ge_msg_stack.add;
256 app_exception.raise_exception;
257 RETURN;
258 END IF;
259 CLOSE cur_rowid;
260
261 END get_fk_igs_co_type;
262
263
264 PROCEDURE get_fk_igs_co_ltr_parm_type (
265 x_letter_parameter_type IN VARCHAR2
266 ) AS
267 /*
268 || Created By : [email protected]
269 || Created On : 14-DEC-2000
270 || Purpose : Validates the Foreign Keys for the table.
271 || Known limitations, enhancements or remarks :
272 || Change History :
273 || Who When What
274 || (reverse chronological order - newest change first)
275 */
276 CURSOR cur_rowid IS
277 SELECT rowid
278 FROM igs_co_ltr_pr_rstcn_all
279 WHERE ((letter_parameter_type = x_letter_parameter_type));
280
281 lv_rowid cur_rowid%RowType;
282
283 BEGIN
284
285 OPEN cur_rowid;
286 FETCH cur_rowid INTO lv_rowid;
287 IF (cur_rowid%FOUND) THEN
288 CLOSE cur_rowid;
289 fnd_message.set_name ('IGS', 'IGS_CO_LPT_LPTR_FK');
290 igs_ge_msg_stack.add;
291 app_exception.raise_exception;
292 RETURN;
293 END IF;
294 CLOSE cur_rowid;
295
296 END get_fk_igs_co_ltr_parm_type;
297
298
299 PROCEDURE before_dml (
300 p_action IN VARCHAR2,
301 x_rowid IN VARCHAR2 DEFAULT NULL,
302 x_org_id IN NUMBER DEFAULT NULL,
303 x_letter_parameter_type IN VARCHAR2 DEFAULT NULL,
304 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
305 x_creation_date IN DATE DEFAULT NULL,
306 x_created_by IN NUMBER DEFAULT NULL,
307 x_last_update_date IN DATE DEFAULT NULL,
308 x_last_updated_by IN NUMBER DEFAULT NULL,
309 x_last_update_login IN NUMBER DEFAULT NULL
310 ) AS
311 /*
312 || Created By : [email protected]
313 || Created On : 14-DEC-2000
314 || Purpose : Initialises the columns, Checks Constraints, Calls the
315 || Trigger Handlers for the table, before any DML operation.
316 || Known limitations, enhancements or remarks :
317 || Change History :
318 || Who When What
319 || (reverse chronological order - newest change first)
320 */
321 BEGIN
322
323 set_column_values (
324 p_action,
325 x_rowid,
326 x_org_id,
327 x_letter_parameter_type,
328 x_correspondence_type,
329 x_creation_date,
330 x_created_by,
331 x_last_update_date,
332 x_last_updated_by,
333 x_last_update_login
334 );
335
336 IF (p_action = 'INSERT') THEN
337 -- Call all the procedures related to Before Insert.
338 BeforeRowInsert1 ( p_inserting => TRUE );
339 IF ( get_pk_for_validation(
340 new_references.letter_parameter_type,
341 new_references.correspondence_type
342 )
343 ) THEN
344 fnd_message.set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
345 igs_ge_msg_stack.add;
346 app_exception.raise_exception;
347 END IF;
348 Check_Constraints;
349 check_parent_existance;
350 ELSIF (p_action = 'UPDATE') THEN
351 -- Call all the procedures related to Before Update.
352 check_parent_existance;
353 Check_Constraints;
354
355 ELSIF (p_action = 'VALIDATE_INSERT') THEN
356 -- Call all the procedures related to Before Insert.
357 Check_Constraints;
358 IF ( get_pk_for_validation (
359 new_references.letter_parameter_type,
360 new_references.correspondence_type
361 )
362 ) THEN
363 fnd_message.set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
364 igs_ge_msg_stack.add;
365 app_exception.raise_exception;
366 END IF;
367 END IF;
368
369 END before_dml;
370
371
372 PROCEDURE insert_row (
373 x_rowid IN OUT NOCOPY VARCHAR2,
374 x_org_id IN NUMBER,
375 x_letter_parameter_type IN VARCHAR2,
376 x_correspondence_type IN VARCHAR2,
377 x_mode IN VARCHAR2 DEFAULT 'R'
378 ) AS
379 /*
380 || Created By : [email protected]
381 || Created On : 14-DEC-2000
382 || Purpose : Handles the INSERT DML logic for the table.
383 || Known limitations, enhancements or remarks :
384 || Change History :
385 || Who When What
386 || (reverse chronological order - newest change first)
387 */
388 CURSOR c IS
389 SELECT rowid
390 FROM igs_co_ltr_pr_rstcn_all
391 WHERE letter_parameter_type = x_letter_parameter_type
392 AND correspondence_type = x_correspondence_type;
393
394 x_last_update_date DATE;
395 x_last_updated_by NUMBER;
396 x_last_update_login NUMBER;
397
398 BEGIN
399
400 x_last_update_date := SYSDATE;
401 IF (x_mode = 'I') THEN
402 x_last_updated_by := 1;
403 x_last_update_login := 0;
404 ELSIF (x_mode = 'R') THEN
405 x_last_updated_by := fnd_global.user_id;
406 IF (x_last_updated_by IS NULL) THEN
407 x_last_updated_by := -1;
408 END IF;
409 x_last_update_login := fnd_global.login_id;
410 IF (x_last_update_login IS NULL) THEN
411 x_last_update_login := -1;
412 END IF;
413 ELSE
414 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
415 igs_ge_msg_stack.add;
416 app_exception.raise_exception;
417 END IF;
418
419 before_dml(
420 p_action => 'INSERT',
421 x_rowid => x_rowid,
422 x_org_id => igs_ge_gen_003.get_org_id,
423 x_letter_parameter_type => x_letter_parameter_type,
424 x_correspondence_type => x_correspondence_type,
425 x_creation_date => x_last_update_date,
426 x_created_by => x_last_updated_by,
427 x_last_update_date => x_last_update_date,
428 x_last_updated_by => x_last_updated_by,
429 x_last_update_login => x_last_update_login
430 );
431
432 INSERT INTO igs_co_ltr_pr_rstcn_all (
433 org_id,
434 letter_parameter_type,
435 correspondence_type,
436 creation_date,
437 created_by,
438 last_update_date,
439 last_updated_by,
440 last_update_login
441 ) VALUES (
442 new_references.org_id,
443 new_references.letter_parameter_type,
444 new_references.correspondence_type,
445 x_last_update_date,
446 x_last_updated_by,
447 x_last_update_date,
448 x_last_updated_by,
449 x_last_update_login
450 );
451
452 OPEN c;
453 FETCH c INTO x_rowid;
454 IF (c%NOTFOUND) THEN
455 CLOSE c;
456 RAISE NO_DATA_FOUND;
457 END IF;
458 CLOSE c;
459
460 END insert_row;
461
462 PROCEDURE add_row (
463 x_rowid IN OUT NOCOPY VARCHAR2,
464 x_org_id IN NUMBER,
465 x_letter_parameter_type IN VARCHAR2,
466 x_correspondence_type IN VARCHAR2,
467 x_mode IN VARCHAR2 DEFAULT 'R'
468 ) AS
469 /*
470 || Created By : [email protected]
471 || Created On : 14-DEC-2000
472 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
473 || Known limitations, enhancements or remarks :
474 || Change History :
475 || Who When What
476 || (reverse chronological order - newest change first)
477 */
478 CURSOR c1 IS
479 SELECT rowid
480 FROM igs_co_ltr_pr_rstcn_all
481 WHERE letter_parameter_type = x_letter_parameter_type
482 AND correspondence_type = x_correspondence_type;
483
484 BEGIN
485
486 OPEN c1;
487 FETCH c1 INTO x_rowid;
488 IF (c1%NOTFOUND) THEN
489 CLOSE c1;
490
491 insert_row (
492 x_rowid,
493 x_org_id,
494 x_letter_parameter_type,
495 x_correspondence_type,
496 x_mode
497 );
498 RETURN;
499 END IF;
500 CLOSE c1;
501
502 END add_row;
503
504
505 PROCEDURE delete_row (
506 x_rowid IN VARCHAR2
507 ) AS
508 /*
509 || Created By : [email protected]
510 || Created On : 14-DEC-2000
511 || Purpose : Handles the DELETE DML logic for the table.
512 || Known limitations, enhancements or remarks :
513 || Change History :
514 || Who When What
515 || (reverse chronological order - newest change first)
516 */
517 BEGIN
518
519 before_dml (
520 p_action => 'DELETE',
521 x_rowid => x_rowid
522 );
523
524 DELETE FROM igs_co_ltr_pr_rstcn_all
525 WHERE rowid = x_rowid;
526
527 IF (SQL%NOTFOUND) THEN
528 RAISE NO_DATA_FOUND;
529 END IF;
530
531 END delete_row;
532
533
534 END igs_co_ltr_pr_rstcn_pkg;
[Home] [Help]