1 PACKAGE BODY igs_co_s_per_ltr_pkg AS
2 /* $Header: IGSLI25B.pls 115.3 2002/11/29 01:07:57 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_co_s_per_ltr_all%ROWTYPE;
6 new_references igs_co_s_per_ltr_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_person_id IN NUMBER DEFAULT NULL,
13 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
14 x_letter_reference_number IN NUMBER DEFAULT NULL,
15 x_sequence_number IN NUMBER DEFAULT NULL,
16 x_creation_date IN DATE DEFAULT NULL,
17 x_created_by IN NUMBER DEFAULT NULL,
18 x_last_update_date IN DATE DEFAULT NULL,
19 x_last_updated_by IN NUMBER DEFAULT NULL,
20 x_last_update_login IN NUMBER DEFAULT NULL
21 ) AS
22 /*
23 || Created By : [email protected]
24 || Created On : 14-DEC-2000
25 || Purpose : Initialises the Old and New references for the columns of the table.
26 || Known limitations, enhancements or remarks :
27 || Change History :
28 || Who When What
29 || (reverse chronological order - newest change first)
30 */
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM IGS_CO_S_PER_LTR_ALL
35 WHERE rowid = x_rowid;
36
37 BEGIN
38
39 l_rowid := x_rowid;
40
41 -- Code for setting the Old and New Reference Values.
42 -- Populate Old Values.
43 OPEN cur_old_ref_values;
44 FETCH cur_old_ref_values INTO old_references;
45 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
46 CLOSE cur_old_ref_values;
47 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
48 igs_ge_msg_stack.add;
49 app_exception.raise_exception;
50 RETURN;
51 END IF;
52 CLOSE cur_old_ref_values;
53
54 -- Populate New Values.
55 new_references.org_id := x_org_id;
56 new_references.person_id := x_person_id;
57 new_references.correspondence_type := x_correspondence_type;
58 new_references.letter_reference_number := x_letter_reference_number;
59 new_references.sequence_number := x_sequence_number;
60
61 IF (p_action = 'UPDATE') THEN
62 new_references.creation_date := old_references.creation_date;
63 new_references.created_by := old_references.created_by;
64 ELSE
65 new_references.creation_date := x_creation_date;
66 new_references.created_by := x_created_by;
67 END IF;
68
69 new_references.last_update_date := x_last_update_date;
70 new_references.last_updated_by := x_last_updated_by;
71 new_references.last_update_login := x_last_update_login;
72
73 END set_column_values;
74
75
76 PROCEDURE check_constraints (
77 column_name IN VARCHAR2 DEFAULT NULL,
78 column_value IN VARCHAR2 DEFAULT NULL
79 ) AS
80 /*
81 || Created By : [email protected]
82 || Created On : 14-DEC-2000
83 || Purpose : Handles the Check Constraint logic for the the columns.
84 || Known limitations, enhancements or remarks :
85 || Change History :
86 || Who When What
87 || (reverse chronological order - newest change first)
88 */
89 BEGIN
90
91 IF (column_name IS NULL) THEN
92 NULL;
93 ELSIF (UPPER(column_name) = 'SEQUENCE_NUMBER') THEN
94 new_references.sequence_number := igs_ge_number.to_num (column_value);
95 END IF;
96
97 IF (UPPER(column_name) = 'SEQUENCE_NUMBER' OR column_name IS NULL) THEN
98 IF NOT (new_references.sequence_number BETWEEN 1
99 AND 9999999999) THEN
100 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
101 igs_ge_msg_stack.add;
102 app_exception.raise_exception;
103 END IF;
104 END IF;
105
106 END check_constraints;
107
108
109 PROCEDURE check_child_existance IS
110 /*
111 || Created By : [email protected]
112 || Created On : 14-DEC-2000
113 || Purpose : Checks for the existance of Child records.
114 || Known limitations, enhancements or remarks :
115 || Change History :
116 || Who When What
117 || (reverse chronological order - newest change first)
118 */
119 BEGIN
120
121 igs_co_s_perlt_rptgp_pkg.get_fk_igs_co_s_per_ltr (
122 old_references.person_id,
123 old_references.correspondence_type,
124 old_references.letter_reference_number,
125 old_references.sequence_number
126 );
127
128 igs_co_s_per_lt_parm_pkg.get_fk_igs_co_s_per_ltr (
129 old_references.person_id,
130 old_references.correspondence_type,
131 old_references.letter_reference_number,
132 old_references.sequence_number
133 );
134
135 END check_child_existance;
136
137
138 FUNCTION get_pk_for_validation (
139 x_person_id IN NUMBER,
140 x_correspondence_type IN VARCHAR2,
141 x_letter_reference_number IN NUMBER,
142 x_sequence_number IN NUMBER
143 ) RETURN BOOLEAN AS
144 /*
145 || Created By : [email protected]
146 || Created On : 14-DEC-2000
147 || Purpose : Validates the Primary Key of the table.
148 || Known limitations, enhancements or remarks :
149 || Change History :
150 || Who When What
151 || (reverse chronological order - newest change first)
152 */
153 CURSOR cur_rowid IS
154 SELECT rowid
155 FROM igs_co_s_per_ltr_all
156 WHERE person_id = x_person_id
157 AND correspondence_type = x_correspondence_type
158 AND letter_reference_number = x_letter_reference_number
159 AND sequence_number = x_sequence_number
160 FOR UPDATE NOWAIT;
161
162 lv_rowid cur_rowid%RowType;
163
164 BEGIN
165
166 OPEN cur_rowid;
167 FETCH cur_rowid INTO lv_rowid;
168 IF (cur_rowid%FOUND) THEN
169 CLOSE cur_rowid;
170 RETURN(TRUE);
171 ELSE
172 CLOSE cur_rowid;
173 RETURN(FALSE);
174 END IF;
175
176 END get_pk_for_validation;
177
178
179 PROCEDURE before_dml (
180 p_action IN VARCHAR2,
181 x_rowid IN VARCHAR2 DEFAULT NULL,
182 x_org_id IN NUMBER DEFAULT NULL,
183 x_person_id IN NUMBER DEFAULT NULL,
184 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
185 x_letter_reference_number IN NUMBER DEFAULT NULL,
186 x_sequence_number IN NUMBER DEFAULT NULL,
187 x_creation_date IN DATE DEFAULT NULL,
188 x_created_by IN NUMBER DEFAULT NULL,
189 x_last_update_date IN DATE DEFAULT NULL,
190 x_last_updated_by IN NUMBER DEFAULT NULL,
191 x_last_update_login IN NUMBER DEFAULT NULL
192 ) AS
193 /*
194 || Created By : [email protected]
195 || Created On : 14-DEC-2000
196 || Purpose : Initialises the columns, Checks Constraints, Calls the
197 || Trigger Handlers for the table, before any DML operation.
198 || Known limitations, enhancements or remarks :
199 || Change History :
200 || Who When What
201 || (reverse chronological order - newest change first)
202 */
203 BEGIN
204
205 set_column_values (
206 p_action,
207 x_rowid,
208 x_org_id,
209 x_person_id,
210 x_correspondence_type,
211 x_letter_reference_number,
212 x_sequence_number,
213 x_creation_date,
214 x_created_by,
215 x_last_update_date,
216 x_last_updated_by,
217 x_last_update_login
218 );
219
220 IF (p_action = 'INSERT') THEN
221 -- Call all the procedures related to Before Insert.
222 IF ( get_pk_for_validation(
223 new_references.person_id,
224 new_references.correspondence_type,
225 new_references.letter_reference_number,
226 new_references.sequence_number
227 )
228 ) THEN
229 fnd_message.set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
230 igs_ge_msg_stack.add;
231 app_exception.raise_exception;
232 END IF;
233 check_constraints;
234 ELSIF (p_action = 'UPDATE') THEN
235 -- Call all the procedures related to Before Update.
236 check_constraints;
237 ELSIF (p_action = 'DELETE') THEN
238 -- Call all the procedures related to Before Delete.
239 check_child_existance;
240 ELSIF (p_action = 'VALIDATE_INSERT') THEN
241 -- Call all the procedures related to Before Insert.
242 IF ( get_pk_for_validation (
243 new_references.person_id,
244 new_references.correspondence_type,
245 new_references.letter_reference_number,
246 new_references.sequence_number
247 )
248 ) THEN
249 fnd_message.set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
250 igs_ge_msg_stack.add;
251 app_exception.raise_exception;
252 END IF;
253 check_constraints;
254 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
255 check_constraints;
256 ELSIF (p_action = 'VALIDATE_DELETE') THEN
257 check_child_existance;
258 END IF;
259
260 END before_dml;
261
262
263 PROCEDURE insert_row (
264 x_rowid IN OUT NOCOPY VARCHAR2,
265 x_org_id IN NUMBER,
266 x_person_id IN NUMBER,
267 x_correspondence_type IN VARCHAR2,
268 x_letter_reference_number IN NUMBER,
269 x_sequence_number IN NUMBER,
270 x_mode IN VARCHAR2 DEFAULT 'R'
271 ) AS
272 /*
273 || Created By : [email protected]
274 || Created On : 14-DEC-2000
275 || Purpose : Handles the INSERT DML logic for the table.
276 || Known limitations, enhancements or remarks :
277 || Change History :
278 || Who When What
279 || (reverse chronological order - newest change first)
280 */
281 CURSOR c IS
282 SELECT rowid
283 FROM igs_co_s_per_ltr_all
284 WHERE person_id = x_person_id
285 AND correspondence_type = x_correspondence_type
286 AND letter_reference_number = x_letter_reference_number
287 AND sequence_number = x_sequence_number;
288
289 x_last_update_date DATE;
290 x_last_updated_by NUMBER;
291 x_last_update_login NUMBER;
292 x_request_id NUMBER;
293 x_program_id NUMBER;
294 x_program_application_id NUMBER;
295 x_program_update_date DATE;
296
297 BEGIN
298
299 x_last_update_date := SYSDATE;
300 IF (x_mode = 'I') THEN
301 x_last_updated_by := 1;
302 x_last_update_login := 0;
303 ELSIF (x_mode = 'R') THEN
304 x_last_updated_by := fnd_global.user_id;
305 IF (x_last_updated_by IS NULL) THEN
306 x_last_updated_by := -1;
307 END IF;
308 x_last_update_login := fnd_global.login_id;
309 IF (x_last_update_login IS NULL) THEN
310 x_last_update_login := -1;
311 END IF;
312 x_request_id := fnd_global.conc_request_id;
313 x_program_id := fnd_global.conc_program_id;
314 x_program_application_id := fnd_global.prog_appl_id;
315
316 IF (x_request_id = -1) THEN
317 x_request_id := NULL;
318 x_program_id := NULL;
319 x_program_application_id := NULL;
320 x_program_update_date := NULL;
321 ELSE
322 x_program_update_date := SYSDATE;
323 END IF;
324 ELSE
325 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
326 igs_ge_msg_stack.add;
327 app_exception.raise_exception;
328 END IF;
329
330 before_dml(
331 p_action => 'INSERT',
332 x_rowid => x_rowid,
333 x_org_id => igs_ge_gen_003.get_org_id,
334 x_person_id => x_person_id,
335 x_correspondence_type => x_correspondence_type,
336 x_letter_reference_number => x_letter_reference_number,
337 x_sequence_number => x_sequence_number,
338 x_creation_date => x_last_update_date,
339 x_created_by => x_last_updated_by,
340 x_last_update_date => x_last_update_date,
341 x_last_updated_by => x_last_updated_by,
342 x_last_update_login => x_last_update_login
343 );
344
345 INSERT INTO igs_co_s_per_ltr_all (
346 org_id,
347 person_id,
348 correspondence_type,
349 letter_reference_number,
350 sequence_number,
351 creation_date,
352 created_by,
353 last_update_date,
354 last_updated_by,
355 last_update_login,
356 request_id,
357 program_id,
358 program_application_id,
359 program_update_date
360 ) VALUES (
361 new_references.org_id,
362 new_references.person_id,
363 new_references.correspondence_type,
364 new_references.letter_reference_number,
365 new_references.sequence_number,
366 x_last_update_date,
367 x_last_updated_by,
368 x_last_update_date,
369 x_last_updated_by,
370 x_last_update_login ,
371 x_request_id,
372 x_program_id,
373 x_program_application_id,
374 x_program_update_date
375 );
376
377 OPEN c;
378 FETCH c INTO x_rowid;
379 IF (c%NOTFOUND) THEN
380 CLOSE c;
381 RAISE NO_DATA_FOUND;
382 END IF;
383 CLOSE c;
384
385 END insert_row;
386
387 PROCEDURE add_row (
388 x_rowid IN OUT NOCOPY VARCHAR2,
389 x_org_id IN NUMBER,
390 x_person_id IN NUMBER,
391 x_correspondence_type IN VARCHAR2,
392 x_letter_reference_number IN NUMBER,
393 x_sequence_number IN NUMBER,
394 x_mode IN VARCHAR2 DEFAULT 'R'
395 ) AS
396 /*
397 || Created By : [email protected]
398 || Created On : 14-DEC-2000
399 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
400 || Known limitations, enhancements or remarks :
401 || Change History :
402 || Who When What
403 || (reverse chronological order - newest change first)
404 */
405 CURSOR c1 IS
406 SELECT rowid
407 FROM igs_co_s_per_ltr_all
408 WHERE person_id = x_person_id
409 AND correspondence_type = x_correspondence_type
410 AND letter_reference_number = x_letter_reference_number
411 AND sequence_number = x_sequence_number;
412
413 BEGIN
414
415 OPEN c1;
416 FETCH c1 INTO x_rowid;
417 IF (c1%NOTFOUND) THEN
418 CLOSE c1;
419
420 insert_row (
421 x_rowid,
422 x_org_id,
423 x_person_id,
424 x_correspondence_type,
425 x_letter_reference_number,
426 x_sequence_number,
427 x_mode
428 );
429 RETURN;
430 END IF;
431 CLOSE c1;
432
433
434 END add_row;
435
436
437 PROCEDURE delete_row (
438 x_rowid IN VARCHAR2
439 ) AS
440 /*
441 || Created By : [email protected]
442 || Created On : 14-DEC-2000
443 || Purpose : Handles the DELETE DML logic for the table.
444 || Known limitations, enhancements or remarks :
445 || Change History :
446 || Who When What
447 || (reverse chronological order - newest change first)
448 */
449 BEGIN
450
451 before_dml (
452 p_action => 'DELETE',
453 x_rowid => x_rowid
454 );
455
456 DELETE FROM igs_co_s_per_ltr_all
457 WHERE rowid = x_rowid;
458
459 IF (SQL%NOTFOUND) THEN
460 RAISE NO_DATA_FOUND;
461 END IF;
462
463 END delete_row;
464
465
466 END igs_co_s_per_ltr_pkg;
[Home] [Help]