[Home] [Help]
Skip to content
PACKAGE BODY: APPS.IGS_HE_UCAS_IMP_ERR_PKG
Source
1 PACKAGE BODY igs_he_ucas_imp_err_pkg AS
2 /* $Header: IGSWI32B.pls 115.3 2003/03/05 08:47:15 bayadav noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_ucas_imp_err%ROWTYPE;
6 new_references igs_he_ucas_imp_err%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_error_interface_id IN NUMBER,
12 x_interface_hesa_id IN NUMBER,
13 x_batch_id IN NUMBER,
14 x_error_code IN VARCHAR2,
15 x_error_text IN VARCHAR2,
16 x_creation_date IN DATE,
17 x_created_by IN NUMBER,
18 x_last_update_date IN DATE,
19 x_last_updated_by IN NUMBER,
20 x_last_update_login IN NUMBER
21 ) AS
22 /*
23 || Created By : smaddali
24 || Created On : 05-NOV-2002
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_he_ucas_imp_err
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.error_interface_id := x_error_interface_id;
56 new_references.interface_hesa_id := x_interface_hesa_id;
57 new_references.batch_id := x_batch_id;
58 new_references.error_code := x_error_code;
59 new_references.error_text := x_error_text;
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_parent_existance AS
77 /*
78 || Created By : smaddali
79 || Created On : 05-NOV-2002
80 || Purpose : Checks for the existance of Parent records.
81 || Known limitations, enhancements or remarks :
82 || Change History :
83 || Who When What
84 || (reverse chronological order - newest change first)
85 */
86 CURSOR check_parent IS
87 SELECT rowid
88 FROM igs_he_ucas_imp_int
89 WHERE batch_id = new_references.batch_id AND
90 interface_hesa_id = new_references.interface_hesa_id ;
91 lv_rowid check_parent%ROWTYPE;
92
93 BEGIN
94
95 IF (((old_references.interface_hesa_id = new_references.interface_hesa_id) AND
96 (old_references.batch_id = new_references.batch_id)) OR
97 ((new_references.interface_hesa_id IS NULL) OR
98 (new_references.batch_id IS NULL))) THEN
99 NULL;
100 ELSE
101 OPEN check_parent;
102 FETCH check_parent INTO lv_rowid;
103 IF (check_parent%NOTFOUND) THEN
104 CLOSE check_parent;
105 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
106 igs_ge_msg_stack.add;
107 app_exception.raise_exception;
108 ELSE
109 CLOSE check_parent;
110 END IF;
111 END IF;
112
113 END check_parent_existance;
114
115
116 FUNCTION get_pk_for_validation (
117 x_error_interface_id IN NUMBER
118 ) RETURN BOOLEAN AS
119 /*
120 || Created By : smaddali
121 || Created On : 05-NOV-2002
122 || Purpose : Validates the Primary Key of the table.
123 || Known limitations, enhancements or remarks :
124 || Change History :
125 || Who When What
126 || (reverse chronological order - newest change first)
127 */
128 CURSOR cur_rowid IS
129 SELECT rowid
130 FROM igs_he_ucas_imp_err
131 WHERE error_interface_id = x_error_interface_id
132 FOR UPDATE NOWAIT;
133
134 lv_rowid cur_rowid%RowType;
135
136 BEGIN
137
138 OPEN cur_rowid;
139 FETCH cur_rowid INTO lv_rowid;
140 IF (cur_rowid%FOUND) THEN
141 CLOSE cur_rowid;
142 RETURN(TRUE);
143 ELSE
144 CLOSE cur_rowid;
145 RETURN(FALSE);
146 END IF;
147
148 END get_pk_for_validation;
149
150
151 PROCEDURE before_dml (
152 p_action IN VARCHAR2,
153 x_rowid IN VARCHAR2,
154 x_error_interface_id IN NUMBER,
155 x_interface_hesa_id IN NUMBER,
156 x_batch_id IN NUMBER,
157 x_error_code IN VARCHAR2,
158 x_error_text IN VARCHAR2,
159 x_creation_date IN DATE,
160 x_created_by IN NUMBER,
161 x_last_update_date IN DATE,
162 x_last_updated_by IN NUMBER,
163 x_last_update_login IN NUMBER
164 ) AS
165 /*
166 || Created By : smaddali
167 || Created On : 05-NOV-2002
168 || Purpose : Initialises the columns, Checks Constraints, Calls the
169 || Trigger Handlers for the table, before any DML operation.
170 || Known limitations, enhancements or remarks :
171 || Change History :
172 || Who When What
173 || (reverse chronological order - newest change first)
174 */
175 BEGIN
176
177 set_column_values (
178 p_action,
179 x_rowid,
180 x_error_interface_id,
181 x_interface_hesa_id,
182 x_batch_id,
183 x_error_code,
184 x_error_text,
185 x_creation_date,
186 x_created_by,
187 x_last_update_date,
188 x_last_updated_by,
189 x_last_update_login
190 );
191
192 IF (p_action = 'INSERT') THEN
193 -- Call all the procedures related to Before Insert.
194 IF ( get_pk_for_validation(
195 new_references.error_interface_id
196 )
197 ) THEN
198 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
199 igs_ge_msg_stack.add;
200 app_exception.raise_exception;
201 END IF;
202 check_parent_existance;
203 ELSIF (p_action = 'UPDATE') THEN
204 -- Call all the procedures related to Before Update.
205 check_parent_existance;
206 ELSIF (p_action = 'VALIDATE_INSERT') THEN
207 -- Call all the procedures related to Before Insert.
208 IF ( get_pk_for_validation (
209 new_references.error_interface_id
210 )
211 ) THEN
212 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
213 igs_ge_msg_stack.add;
214 app_exception.raise_exception;
215 END IF;
216 END IF;
217
218 END before_dml;
219
220
221 PROCEDURE insert_row (
222 x_rowid IN OUT NOCOPY VARCHAR2,
223 x_error_interface_id IN OUT NOCOPY NUMBER,
224 x_interface_hesa_id IN NUMBER,
225 x_batch_id IN NUMBER,
226 x_error_code IN VARCHAR2,
227 x_error_text IN VARCHAR2,
228 x_mode IN VARCHAR2
229 ) AS
230 /*
231 || Created By : smaddali
232 || Created On : 05-NOV-2002
233 || Purpose : Handles the INSERT DML logic for the table.
234 || Known limitations, enhancements or remarks :
235 || Change History :
236 || Who When What
237 || (reverse chronological order - newest change first)
238 */
239
240 CURSOR c_imp_err IS
241 SELECT igs_he_ucas_imp_err_s.NEXTVAL
242 FROM dual;
243
244 x_last_update_date DATE;
245 x_last_updated_by NUMBER;
246 x_last_update_login NUMBER;
247
248 BEGIN
249
250 x_last_update_date := SYSDATE;
251 IF (x_mode = 'I') THEN
252 x_last_updated_by := 1;
253 x_last_update_login := 0;
254 ELSIF (x_mode = 'R') THEN
255 x_last_updated_by := fnd_global.user_id;
256 IF (x_last_updated_by IS NULL) THEN
257 x_last_updated_by := -1;
258 END IF;
259 x_last_update_login := fnd_global.login_id;
260 IF (x_last_update_login IS NULL) THEN
261 x_last_update_login := -1;
262 END IF;
263 ELSE
264 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
265 igs_ge_msg_stack.add;
266 app_exception.raise_exception;
267 END IF;
268
269 OPEN c_imp_err;
270 FETCH c_imp_err INTO x_error_interface_id;
271 CLOSE c_imp_err;
272
273
274
275 before_dml(
276 p_action => 'INSERT',
277 x_rowid => x_rowid,
278 x_error_interface_id => x_error_interface_id,
279 x_interface_hesa_id => x_interface_hesa_id,
280 x_batch_id => x_batch_id,
281 x_error_code => x_error_code,
282 x_error_text => x_error_text,
283 x_creation_date => x_last_update_date,
284 x_created_by => x_last_updated_by,
285 x_last_update_date => x_last_update_date,
286 x_last_updated_by => x_last_updated_by,
287 x_last_update_login => x_last_update_login
288 );
289
290 INSERT INTO igs_he_ucas_imp_err (
291 error_interface_id,
292 interface_hesa_id,
293 batch_id,
294 error_code,
295 error_text,
296 creation_date,
297 created_by,
298 last_update_date,
299 last_updated_by,
300 last_update_login
301 ) VALUES (
302 new_references.error_interface_id,
303 new_references.interface_hesa_id,
304 new_references.batch_id,
305 new_references.error_code,
306 new_references.error_text,
307 x_last_update_date,
308 x_last_updated_by,
309 x_last_update_date,
310 x_last_updated_by,
311 x_last_update_login
312 ) RETURNING ROWID, error_interface_id INTO x_rowid, x_error_interface_id;
313
314 END insert_row;
315
316
317 PROCEDURE lock_row (
318 x_rowid IN VARCHAR2,
319 x_error_interface_id IN NUMBER,
320 x_interface_hesa_id IN NUMBER,
321 x_batch_id IN NUMBER,
322 x_error_code IN VARCHAR2,
323 x_error_text IN VARCHAR2
324 ) AS
325 /*
326 || Created By : smaddali
327 || Created On : 05-NOV-2002
328 || Purpose : Handles the LOCK mechanism for the table.
329 || Known limitations, enhancements or remarks :
330 || Change History :
331 || Who When What
332 || (reverse chronological order - newest change first)
333 */
334 CURSOR c1 IS
335 SELECT
336 interface_hesa_id,
337 batch_id,
338 error_code,
339 error_text
340 FROM igs_he_ucas_imp_err
341 WHERE rowid = x_rowid
342 FOR UPDATE NOWAIT;
343
344 tlinfo c1%ROWTYPE;
345
346 BEGIN
347
348 OPEN c1;
349 FETCH c1 INTO tlinfo;
350 IF (c1%notfound) THEN
351 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
352 igs_ge_msg_stack.add;
353 CLOSE c1;
354 app_exception.raise_exception;
355 RETURN;
356 END IF;
357 CLOSE c1;
358
359 IF (
360 (tlinfo.interface_hesa_id = x_interface_hesa_id)
361 AND (tlinfo.batch_id = x_batch_id)
362 AND (tlinfo.error_code = x_error_code)
363 AND ((tlinfo.error_text = x_error_text) OR ((tlinfo.error_text IS NULL) AND (X_error_text IS NULL)))
364 ) THEN
365 NULL;
366 ELSE
367 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
368 igs_ge_msg_stack.add;
369 app_exception.raise_exception;
370 END IF;
371
372 RETURN;
373
374 END lock_row;
375
376
377 PROCEDURE update_row (
378 x_rowid IN VARCHAR2,
379 x_error_interface_id IN NUMBER,
380 x_interface_hesa_id IN NUMBER,
381 x_batch_id IN NUMBER,
382 x_error_code IN VARCHAR2,
383 x_error_text IN VARCHAR2,
384 x_mode IN VARCHAR2
385 ) AS
386 /*
387 || Created By : smaddali
388 || Created On : 05-NOV-2002
389 || Purpose : Handles the UPDATE DML logic for the table.
390 || Known limitations, enhancements or remarks :
391 || Change History :
392 || Who When What
393 || (reverse chronological order - newest change first)
394 */
395 x_last_update_date DATE ;
396 x_last_updated_by NUMBER;
397 x_last_update_login NUMBER;
398
399 BEGIN
400
401 x_last_update_date := SYSDATE;
402 IF (X_MODE = 'I') THEN
403 x_last_updated_by := 1;
404 x_last_update_login := 0;
405 ELSIF (x_mode = 'R') THEN
406 x_last_updated_by := fnd_global.user_id;
407 IF x_last_updated_by IS NULL THEN
408 x_last_updated_by := -1;
409 END IF;
410 x_last_update_login := fnd_global.login_id;
411 IF (x_last_update_login IS NULL) THEN
412 x_last_update_login := -1;
413 END IF;
414 ELSE
415 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
416 igs_ge_msg_stack.add;
417 app_exception.raise_exception;
418 END IF;
419
420 before_dml(
421 p_action => 'UPDATE',
422 x_rowid => x_rowid,
423 x_error_interface_id => x_error_interface_id,
424 x_interface_hesa_id => x_interface_hesa_id,
425 x_batch_id => x_batch_id,
426 x_error_code => x_error_code,
427 x_error_text => x_error_text,
428 x_creation_date => x_last_update_date,
429 x_created_by => x_last_updated_by,
430 x_last_update_date => x_last_update_date,
431 x_last_updated_by => x_last_updated_by,
432 x_last_update_login => x_last_update_login
433 );
434
435 UPDATE igs_he_ucas_imp_err
436 SET
437 interface_hesa_id = new_references.interface_hesa_id,
438 batch_id = new_references.batch_id,
439 error_code = new_references.error_code,
440 error_text = new_references.error_text,
441 last_update_date = x_last_update_date,
442 last_updated_by = x_last_updated_by,
443 last_update_login = x_last_update_login
444 WHERE rowid = x_rowid;
445
446 IF (SQL%NOTFOUND) THEN
447 RAISE NO_DATA_FOUND;
448 END IF;
449
450 END update_row;
451
452
453 PROCEDURE add_row (
454 x_rowid IN OUT NOCOPY VARCHAR2,
455 x_error_interface_id IN OUT NOCOPY NUMBER,
456 x_interface_hesa_id IN NUMBER,
457 x_batch_id IN NUMBER,
458 x_error_code IN VARCHAR2,
459 x_error_text IN VARCHAR2,
460 x_mode IN VARCHAR2
461 ) AS
462 /*
463 || Created By : smaddali
464 || Created On : 05-NOV-2002
465 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
466 || Known limitations, enhancements or remarks :
467 || Change History :
468 || Who When What
469 || (reverse chronological order - newest change first)
470 */
471 CURSOR c1 IS
472 SELECT rowid
473 FROM igs_he_ucas_imp_err
474 WHERE error_interface_id = x_error_interface_id;
475
476 BEGIN
477
478 OPEN c1;
479 FETCH c1 INTO x_rowid;
480 IF (c1%NOTFOUND) THEN
481 CLOSE c1;
482
483 insert_row (
484 x_rowid,
485 x_error_interface_id,
486 x_interface_hesa_id,
487 x_batch_id,
488 x_error_code,
489 x_error_text,
490 x_mode
491 );
492 RETURN;
493 END IF;
494 CLOSE c1;
495
496 update_row (
497 x_rowid,
498 x_error_interface_id,
499 x_interface_hesa_id,
500 x_batch_id,
501 x_error_code,
502 x_error_text,
503 x_mode
504 );
505
506 END add_row;
507
508
509 PROCEDURE delete_row (
510 x_rowid IN VARCHAR2
511 ) AS
512 /*
513 || Created By : smaddali
514 || Created On : 05-NOV-2002
515 || Purpose : Handles the DELETE DML logic for the table.
516 || Known limitations, enhancements or remarks :
517 || Change History :
518 || Who When What
519 || (reverse chronological order - newest change first)
520 */
521 BEGIN
522
523 before_dml (
524 p_action => 'DELETE',
525 x_rowid => x_rowid
526 );
527
528 DELETE FROM igs_he_ucas_imp_err
529 WHERE rowid = x_rowid;
530
531 IF (SQL%NOTFOUND) THEN
532 RAISE NO_DATA_FOUND;
533 END IF;
534
535 END delete_row;
536
537
538 END igs_he_ucas_imp_err_pkg;