[Home] [Help]
PACKAGE BODY: APPS.IGI_EXP_DU_TYPE_DETAILS_PKG
Source
1 PACKAGE BODY igi_exp_du_type_details_pkg AS
2 /* $Header: igiexptb.pls 120.5.12000000.1 2007/09/13 04:24:40 mbremkum ship $ */
3
4 --following variables added for bug 3199481: fnd logging changes: sdixit
5 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 l_state_level number := FND_LOG.LEVEL_STATEMENT;
7 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
8 l_event_level number := FND_LOG.LEVEL_EVENT;
9 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
10 l_error_level number := FND_LOG.LEVEL_ERROR;
11 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
12
13 l_rowid VARCHAR2(25);
14 old_references igi_exp_du_type_details_all%ROWTYPE;
15 new_references igi_exp_du_type_details_all%ROWTYPE;
16
17 PROCEDURE set_column_values (
18 p_action IN VARCHAR2,
19 x_rowid IN VARCHAR2 ,
20 x_du_type_detail_id IN NUMBER ,
21 x_du_type_header_id IN NUMBER ,
22 x_transaction_type IN VARCHAR2 ,
23 x_org_id IN NUMBER ,
24 x_creation_date IN DATE ,
25 x_created_by IN NUMBER ,
26 x_last_update_date IN DATE ,
27 x_last_updated_by IN NUMBER ,
28 x_last_update_login IN NUMBER ) AS
29 /*
30 || Created By : [email protected]
31 || Created On : 18-OCT-2001
32 || Purpose : Initialises the Old and New references for the columns of the table.
33 || Known limitations, enhancements or remarks :
34 || Change History :
35 || Who When What
36 || (reverse chronological order - newest change first)
37 */
38
39 CURSOR cur_old_ref_values IS
40 SELECT *
41 FROM IGI_EXP_DU_TYPE_DETAILS_ALL
42 WHERE rowid = x_rowid;
43
44 BEGIN
45
46 l_rowid := x_rowid;
47
48 -- Code for setting the Old and New Reference Values.
49 -- Populate Old Values.
50 OPEN cur_old_ref_values;
51 FETCH cur_old_ref_values INTO old_references;
52 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
53 CLOSE cur_old_ref_values;
54 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
55 --bug 3199481 fnd logging changes: sdixit: start block
56 IF (l_error_level >= l_debug_level ) THEN
57 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexptb.IGI_EXP_DU_TYPE_DETAILS_PKG.set_column_values',FALSE);
58 END IF;
59 --bug 3199481 fnd logging changes: sdixit: end block
60
61 app_exception.raise_exception;
62 RETURN;
63 END IF;
64 CLOSE cur_old_ref_values;
65
66 -- Populate New Values.
67 new_references.du_type_detail_id := x_du_type_detail_id;
68 new_references.du_type_header_id := x_du_type_header_id;
69 new_references.transaction_type := x_transaction_type;
70 new_references.org_id := x_org_id;
71
72 IF (p_action = 'UPDATE') THEN
73 new_references.creation_date := old_references.creation_date;
74 new_references.created_by := old_references.created_by;
75 ELSE
76 new_references.creation_date := x_creation_date;
77 new_references.created_by := x_created_by;
78 END IF;
79
80 new_references.last_update_date := x_last_update_date;
81 new_references.last_updated_by := x_last_updated_by;
82 new_references.last_update_login := x_last_update_login;
83
84 END set_column_values;
85
86
87 PROCEDURE check_parent_existance AS
88 /*
89 || Created By : [email protected]
90 || Created On : 18-OCT-2001
91 || Purpose : Checks for the existance of Parent records.
92 || Known limitations, enhancements or remarks :
93 || Change History :
94 || Who When What
95 || (reverse chronological order - newest change first)
96 */
97 BEGIN
98
99 IF (((old_references.du_type_header_id = new_references.du_type_header_id)) OR
100 ((new_references.du_type_header_id IS NULL))) THEN
101 NULL;
102 ELSIF NOT igi_exp_du_type_headers_pkg.get_pk_for_validation (
103 new_references.du_type_header_id
104 ) THEN
105 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
106 --bug 3199481 fnd logging changes: sdixit: start block
107 IF (l_error_level >= l_debug_level ) THEN
108 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexptb.IGI_EXP_DU_TYPE_DETAILS_PKG.check_parent_existance',FALSE);
109 END IF;
110 --bug 3199481 fnd logging changes: sdixit: end block
111
112 app_exception.raise_exception;
113 END IF;
114
115 END check_parent_existance;
116
117
118 FUNCTION get_pk_for_validation (
119 x_du_type_detail_id IN NUMBER
120 ) RETURN BOOLEAN AS
121 /*
122 || Created By : [email protected]
123 || Created On : 18-OCT-2001
124 || Purpose : Validates the Primary Key of the table.
125 || Known limitations, enhancements or remarks :
126 || Change History :
127 || Who When What
128 || (reverse chronological order - newest change first)
129 */
130 CURSOR cur_rowid IS
131 SELECT rowid
132 FROM igi_exp_du_type_details_all
133 WHERE du_type_detail_id = x_du_type_detail_id
134 FOR UPDATE NOWAIT;
135
136 lv_rowid cur_rowid%RowType;
137
138 BEGIN
139
140 OPEN cur_rowid;
141 FETCH cur_rowid INTO lv_rowid;
142 IF (cur_rowid%FOUND) THEN
143 CLOSE cur_rowid;
144 RETURN(TRUE);
145 ELSE
146 CLOSE cur_rowid;
147 RETURN(FALSE);
148 END IF;
149
150 END get_pk_for_validation;
151
152
153 PROCEDURE get_fk_igi_exp_du_type_headers (
154 x_du_type_header_id IN NUMBER
155 ) AS
156 /*
157 || Created By : [email protected]
158 || Created On : 18-OCT-2001
159 || Purpose : Validates the Foreign Keys for the table.
160 || Known limitations, enhancements or remarks :
161 || Change History :
162 || Who When What
163 || (reverse chronological order - newest change first)
164 */
165 CURSOR cur_rowid IS
166 SELECT rowid
167 FROM igi_exp_du_type_details_all
168 WHERE ((du_type_header_id = x_du_type_header_id));
169
170 lv_rowid cur_rowid%RowType;
171
172 BEGIN
173
174 OPEN cur_rowid;
175 FETCH cur_rowid INTO lv_rowid;
176 IF (cur_rowid%FOUND) THEN
177 CLOSE cur_rowid;
178 fnd_message.set_name ('FND','FND-CANNOT DELETE MASTER');
179 --bug 3199481 fnd logging changes: sdixit: start block
180 IF (l_error_level >= l_debug_level ) THEN
181 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexptb.IGI_EXP_DU_TYPE_DETAILS_PKG.get_fk_igi_exp_du_type_headers',FALSE);
182 END IF;
183 --bug 3199481 fnd logging changes: sdixit: end block
184
185 app_exception.raise_exception;
186 RETURN;
187 END IF;
188 CLOSE cur_rowid;
189
190 END get_fk_igi_exp_du_type_headers;
191
192
193 PROCEDURE before_dml (
194 p_action IN VARCHAR2,
195 x_rowid IN VARCHAR2 ,
196 x_du_type_detail_id IN NUMBER ,
197 x_du_type_header_id IN NUMBER ,
198 x_transaction_type IN VARCHAR2 ,
199 x_org_id IN NUMBER ,
200 x_creation_date IN DATE ,
201 x_created_by IN NUMBER ,
202 x_last_update_date IN DATE ,
203 x_last_updated_by IN NUMBER ,
204 x_last_update_login IN NUMBER ) AS
205 /*
206 || Created By : [email protected]
207 || Created On : 18-OCT-2001
208 || Purpose : Initialises the columns, Checks Constraints, Calls the
209 || Trigger Handlers for the table, before any DML operation.
210 || Known limitations, enhancements or remarks :
211 || Change History :
212 || Who When What
213 || (reverse chronological order - newest change first)
214 */
215 BEGIN
216
217 set_column_values (
218 p_action,
219 x_rowid,
220 x_du_type_detail_id,
221 x_du_type_header_id,
222 x_transaction_type,
223 x_org_id,
224 x_creation_date,
225 x_created_by,
226 x_last_update_date,
227 x_last_updated_by,
228 x_last_update_login
229 );
230
231 IF (p_action = 'INSERT') THEN
232 -- Call all the procedures related to Before Insert.
233 IF ( get_pk_for_validation(
234 new_references.du_type_detail_id
235 )
236 ) THEN
237 fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
238 --bug 3199481 fnd logging changes: sdixit: start block
239 IF (l_error_level >= l_debug_level ) THEN
240 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexptb.IGI_EXP_DU_TYPE_DETAILS_PKG.before_dml.msg1',FALSE);
241 END IF;
242 --bug 3199481 fnd logging changes: sdixit: end block
243
244 app_exception.raise_exception;
245 END IF;
246 check_parent_existance;
247 ELSIF (p_action = 'UPDATE') THEN
248 -- Call all the procedures related to Before Update.
249 check_parent_existance;
250 ELSIF (p_action = 'VALIDATE_INSERT') THEN
251 -- Call all the procedures related to Before Insert.
252 IF ( get_pk_for_validation (
253 new_references.du_type_detail_id
254 )
255 ) THEN
256 fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
257 --bug 3199481 fnd logging changes: sdixit: start block
258 IF (l_error_level >= l_debug_level ) THEN
259 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexptb.IGI_EXP_DU_TYPE_DETAILS_PKG.before_dml.msg2',FALSE);
260 END IF;
261 --bug 3199481 fnd logging changes: sdixit: end block
262
263 app_exception.raise_exception;
264 END IF;
265 END IF;
266
267 END before_dml;
268
269
270 PROCEDURE insert_row (
271 x_rowid IN OUT NOCOPY VARCHAR2,
272 x_du_type_detail_id IN OUT NOCOPY NUMBER,
273 x_du_type_header_id IN NUMBER,
274 x_transaction_type IN VARCHAR2,
275 x_org_id IN NUMBER,
276 x_mode IN VARCHAR2 ) AS
277 /*
278 || Created By : [email protected]
279 || Created On : 18-OCT-2001
280 || Purpose : Handles the INSERT DML logic for the table.
281 || Known limitations, enhancements or remarks :
282 || Change History :
283 || Who When What
284 || (reverse chronological order - newest change first)
285 */
286 CURSOR c IS
287 SELECT rowid
288 FROM igi_exp_du_type_details_all
289 WHERE du_type_detail_id = x_du_type_detail_id;
290
291 x_last_update_date DATE;
292 x_last_updated_by NUMBER;
293 x_last_update_login NUMBER;
294
295 BEGIN
296
297 x_last_update_date := SYSDATE;
298 IF (x_mode = 'I') THEN
299 x_last_updated_by := 1;
300 x_last_update_login := 0;
301 ELSIF (x_mode = 'R') THEN
302 x_last_updated_by := fnd_global.user_id;
303 IF (x_last_updated_by IS NULL) THEN
304 x_last_updated_by := -1;
305 END IF;
306 x_last_update_login := fnd_global.login_id;
307 IF (x_last_update_login IS NULL) THEN
308 x_last_update_login := -1;
309 END IF;
310 ELSE
311 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
312 --bug 3199481 fnd logging changes: sdixit: start block
313 IF (l_error_level >= l_debug_level ) THEN
314 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexptb.IGI_EXP_DU_TYPE_DETAILS_PKG.insert_row',FALSE);
315 END IF;
316 --bug 3199481 fnd logging changes: sdixit: end block
317
318 app_exception.raise_exception;
319 END IF;
320
321 SELECT igi_exp_du_type_details_s1.NEXTVAL
322 INTO x_du_type_detail_id
323 FROM dual;
324
325 before_dml(
326 p_action => 'INSERT',
327 x_rowid => x_rowid,
328 x_du_type_detail_id => x_du_type_detail_id,
329 x_du_type_header_id => x_du_type_header_id,
330 x_transaction_type => x_transaction_type,
331 x_org_id => x_org_id,
332 x_creation_date => x_last_update_date,
333 x_created_by => x_last_updated_by,
334 x_last_update_date => x_last_update_date,
335 x_last_updated_by => x_last_updated_by,
336 x_last_update_login => x_last_update_login
337 );
338
339 INSERT INTO igi_exp_du_type_details_all (
340 du_type_detail_id,
341 du_type_header_id,
342 transaction_type,
343 org_id,
344 creation_date,
345 created_by,
346 last_update_date,
347 last_updated_by,
348 last_update_login
349 ) VALUES (
350 new_references.du_type_detail_id,
351 new_references.du_type_header_id,
352 new_references.transaction_type,
353 new_references.org_id,
354 x_last_update_date,
355 x_last_updated_by,
356 x_last_update_date,
357 x_last_updated_by,
358 x_last_update_login
359 );
360
361 OPEN c;
362 FETCH c INTO x_rowid;
363 IF (c%NOTFOUND) THEN
364 CLOSE c;
365 RAISE NO_DATA_FOUND;
366 END IF;
367 CLOSE c;
368
369 END insert_row;
370
371
372 PROCEDURE lock_row (
373 x_rowid IN VARCHAR2,
374 x_du_type_detail_id IN NUMBER,
375 x_du_type_header_id IN NUMBER,
376 x_transaction_type IN VARCHAR2,
377 x_org_id IN NUMBER
378 ) AS
379 /*
380 || Created By : [email protected]
381 || Created On : 18-OCT-2001
382 || Purpose : Handles the LOCK mechanism 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 c1 IS
389 SELECT
390 du_type_header_id,
391 transaction_type,
392 org_id
393 FROM igi_exp_du_type_details_all
394 WHERE rowid = x_rowid
395 FOR UPDATE NOWAIT;
396
397 tlinfo c1%ROWTYPE;
398
399 BEGIN
400
401 OPEN c1;
402 FETCH c1 INTO tlinfo;
403 IF (c1%notfound) THEN
404 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
405 --bug 3199481 fnd logging changes: sdixit: start block
409 --bug 3199481 fnd logging changes: sdixit: end block
406 IF (l_error_level >= l_debug_level ) THEN
407 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexptb.IGI_EXP_DU_TYPE_DETAILS_PKG.lock_row',FALSE);
408 END IF;
410
411 CLOSE c1;
412 app_exception.raise_exception;
413 RETURN;
414 END IF;
415 CLOSE c1;
416
417 IF (
418 (tlinfo.du_type_header_id = x_du_type_header_id)
419 AND (tlinfo.transaction_type = x_transaction_type)
420 AND (tlinfo.org_id = x_org_id)
421 ) THEN
422 NULL;
423 ELSE
424 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
425 --bug 3199481 fnd logging changes: sdixit: start block
426 IF (l_error_level >= l_debug_level ) THEN
427 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexptb.IGI_EXP_DU_TYPE_DETAILS_PKG.lock_row',FALSE);
428 END IF;
429 --bug 3199481 fnd logging changes: sdixit: end block
430
431 app_exception.raise_exception;
432 END IF;
433
434 RETURN;
435
436 END lock_row;
437
438
439 PROCEDURE update_row (
440 x_rowid IN VARCHAR2,
441 x_du_type_detail_id IN NUMBER,
442 x_du_type_header_id IN NUMBER,
443 x_transaction_type IN VARCHAR2,
444 x_org_id IN NUMBER,
445 x_mode IN VARCHAR2 ) AS
446 /*
447 || Created By : [email protected]
448 || Created On : 18-OCT-2001
449 || Purpose : Handles the UPDATE DML logic for the table.
450 || Known limitations, enhancements or remarks :
451 || Change History :
452 || Who When What
453 || (reverse chronological order - newest change first)
454 */
455 x_last_update_date DATE ;
456 x_last_updated_by NUMBER;
457 x_last_update_login NUMBER;
458
459 BEGIN
460
461 x_last_update_date := SYSDATE;
462 IF (X_MODE = 'I') THEN
463 x_last_updated_by := 1;
464 x_last_update_login := 0;
465 ELSIF (x_mode = 'R') THEN
466 x_last_updated_by := fnd_global.user_id;
467 IF x_last_updated_by IS NULL THEN
468 x_last_updated_by := -1;
469 END IF;
470 x_last_update_login := fnd_global.login_id;
471 IF (x_last_update_login IS NULL) THEN
472 x_last_update_login := -1;
473 END IF;
474 ELSE
475 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
476 --bug 3199481 fnd logging changes: sdixit: start block
477 IF (l_error_level >= l_debug_level ) THEN
478 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexptb.IGI_EXP_DU_TYPE_DETAILS_PKG.update_row',FALSE);
479 END IF;
480 --bug 3199481 fnd logging changes: sdixit: end block
481
482 app_exception.raise_exception;
483 END IF;
484
485 before_dml(
486 p_action => 'UPDATE',
487 x_rowid => x_rowid,
488 x_du_type_detail_id => x_du_type_detail_id,
489 x_du_type_header_id => x_du_type_header_id,
490 x_transaction_type => x_transaction_type,
491 x_org_id => x_org_id,
492 x_creation_date => x_last_update_date,
493 x_created_by => x_last_updated_by,
494 x_last_update_date => x_last_update_date,
495 x_last_updated_by => x_last_updated_by,
496 x_last_update_login => x_last_update_login
497 );
498
499 UPDATE igi_exp_du_type_details_all
500 SET
501 du_type_header_id = new_references.du_type_header_id,
502 transaction_type = new_references.transaction_type,
503 org_id = new_references.org_id,
504 last_update_date = x_last_update_date,
505 last_updated_by = x_last_updated_by,
506 last_update_login = x_last_update_login
507 WHERE rowid = x_rowid;
508
509 IF (SQL%NOTFOUND) THEN
510 RAISE NO_DATA_FOUND;
511 END IF;
512
513 END update_row;
514
515
516 PROCEDURE add_row (
517 x_rowid IN OUT NOCOPY VARCHAR2,
518 x_du_type_detail_id IN OUT NOCOPY NUMBER,
519 x_du_type_header_id IN NUMBER,
520 x_transaction_type IN VARCHAR2,
521 x_org_id IN NUMBER,
522 x_mode IN VARCHAR2 ) AS
523 /*
524 || Created By : [email protected]
525 || Created On : 18-OCT-2001
526 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
527 || Known limitations, enhancements or remarks :
528 || Change History :
529 || Who When What
530 || (reverse chronological order - newest change first)
531 */
532 CURSOR c1 IS
533 SELECT rowid
534 FROM igi_exp_du_type_details_all
535 WHERE du_type_detail_id = x_du_type_detail_id;
536
537 BEGIN
538
539 OPEN c1;
540 FETCH c1 INTO x_rowid;
541 IF (c1%NOTFOUND) THEN
542 CLOSE c1;
543
544 insert_row (
545 x_rowid,
546 x_du_type_detail_id,
547 x_du_type_header_id,
548 x_transaction_type,
549 x_org_id,
550 x_mode
551 );
552 RETURN;
553 END IF;
554 CLOSE c1;
555
556 update_row (
557 x_rowid,
558 x_du_type_detail_id,
559 x_du_type_header_id,
560 x_transaction_type,
561 x_org_id,
562 x_mode
563 );
564
565 END add_row;
566
567
568 PROCEDURE delete_row (
569 x_rowid IN VARCHAR2
570 ) AS
571 /*
572 || Created By : [email protected]
573 || Created On : 18-OCT-2001
574 || Purpose : Handles the DELETE DML logic for the table.
575 || Known limitations, enhancements or remarks :
576 || Change History :
577 || Who When What
578 || (reverse chronological order - newest change first)
579 */
580 BEGIN
581
582 before_dml (
583 p_action => 'DELETE',
584 x_rowid => x_rowid
585 );
586
587 DELETE FROM igi_exp_du_type_details_all
588 WHERE rowid = x_rowid;
589
590 IF (SQL%NOTFOUND) THEN
591 RAISE NO_DATA_FOUND;
592 END IF;
593
594 END delete_row;
595
596
597 END igi_exp_du_type_details_pkg;