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