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