[Home] [Help]
PACKAGE BODY: APPS.IGI_EXP_TUS_PKG
Source
1 PACKAGE BODY igi_exp_tus_pkg AS
2 /* $Header: igiexpzb.pls 120.5.12000000.1 2007/09/13 04:25:30 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_tus_all%ROWTYPE;
15 new_references igi_exp_tus_all%ROWTYPE;
16
17 PROCEDURE set_column_values (
18 p_action IN VARCHAR2,
19 x_rowid IN VARCHAR2 ,
20 x_tu_id IN NUMBER ,
21 x_tu_type_header_id IN NUMBER ,
22 x_tu_order_number IN VARCHAR2 ,
23 x_tu_legal_number IN VARCHAR2 ,
24 x_tu_description IN VARCHAR2 ,
25 x_tu_status IN VARCHAR2 ,
26 x_tu_currency_code IN VARCHAR2 ,
27 x_tu_amount IN NUMBER ,
28 x_apprv_profile_id IN NUMBER ,
29 x_next_approver_user_id IN NUMBER ,
30 x_tu_fiscal_year IN NUMBER ,
31 x_tu_by_user_id IN NUMBER ,
32 x_tu_date IN DATE ,
33 x_attribute_category IN VARCHAR2 ,
34 x_attribute1 IN VARCHAR2 ,
35 x_attribute2 IN VARCHAR2 ,
36 x_attribute3 IN VARCHAR2 ,
37 x_attribute4 IN VARCHAR2 ,
38 x_attribute5 IN VARCHAR2 ,
39 x_attribute6 IN VARCHAR2 ,
40 x_attribute7 IN VARCHAR2 ,
41 x_attribute8 IN VARCHAR2 ,
42 x_attribute9 IN VARCHAR2 ,
43 x_attribute10 IN VARCHAR2 ,
44 x_attribute11 IN VARCHAR2 ,
45 x_attribute12 IN VARCHAR2 ,
46 x_attribute13 IN VARCHAR2 ,
47 x_attribute14 IN VARCHAR2 ,
48 x_attribute15 IN VARCHAR2 ,
49 x_creation_date IN DATE ,
50 x_created_by IN NUMBER ,
51 x_last_update_date IN DATE ,
52 x_last_updated_by IN NUMBER ,
53 x_last_update_login IN NUMBER ) AS
54 /*
55 || Created By : [email protected]
56 || Created On : 16-APR-2002
57 || Purpose : Initialises the Old and New references for the columns of the table.
58 || Known limitations, enhancements or remarks :
59 || Change History :
60 || Who When What
61 || (reverse chronological order - newest change first)
62 */
63
64 CURSOR cur_old_ref_values IS
65 SELECT *
66 FROM igi_exp_tus_all
67 WHERE rowid = x_rowid;
68
69 BEGIN
70
71 l_rowid := x_rowid;
72
73 -- Code for setting the Old and New Reference Values.
74 -- Populate Old Values.
75 OPEN cur_old_ref_values;
76 FETCH cur_old_ref_values INTO old_references;
77 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
78 CLOSE cur_old_ref_values;
79 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
80 --bug 3199481 fnd logging changes: sdixit: start block
81 IF (l_error_level >= l_debug_level ) THEN
82 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.set_column_values',FALSE);
83 END IF;
84 --bug 3199481 fnd logging changes: sdixit: end block
85 app_exception.raise_exception;
86 RETURN;
87 END IF;
88 CLOSE cur_old_ref_values;
89
90 -- Populate New Values.
91 new_references.tu_id := x_tu_id;
92 new_references.tu_type_header_id := x_tu_type_header_id;
93 new_references.tu_order_number := x_tu_order_number;
94 new_references.tu_legal_number := x_tu_legal_number;
95 new_references.tu_description := x_tu_description;
96 new_references.tu_status := x_tu_status;
97 new_references.tu_currency_code := x_tu_currency_code;
98 new_references.tu_amount := x_tu_amount;
99 new_references.apprv_profile_id := x_apprv_profile_id;
100 new_references.next_approver_user_id := x_next_approver_user_id;
101 new_references.tu_fiscal_year := x_tu_fiscal_year;
102 new_references.tu_by_user_id := x_tu_by_user_id;
103 new_references.tu_date := x_tu_date;
104 new_references.attribute_category := x_attribute_category;
105 new_references.attribute1 := x_attribute1;
106 new_references.attribute2 := x_attribute2;
107 new_references.attribute3 := x_attribute3;
108 new_references.attribute4 := x_attribute4;
109 new_references.attribute5 := x_attribute5;
110 new_references.attribute6 := x_attribute6;
111 new_references.attribute7 := x_attribute7;
112 new_references.attribute8 := x_attribute8;
113 new_references.attribute9 := x_attribute9;
114 new_references.attribute10 := x_attribute10;
115 new_references.attribute11 := x_attribute11;
116 new_references.attribute12 := x_attribute12;
117 new_references.attribute13 := x_attribute13;
118 new_references.attribute14 := x_attribute14;
119 new_references.attribute15 := x_attribute15;
120
121 IF (p_action = 'UPDATE') THEN
122 new_references.creation_date := old_references.creation_date;
123 new_references.created_by := old_references.created_by;
124 ELSE
125 new_references.creation_date := x_creation_date;
126 new_references.created_by := x_created_by;
127 END IF;
128
129 new_references.last_update_date := x_last_update_date;
130 new_references.last_updated_by := x_last_updated_by;
131 new_references.last_update_login := x_last_update_login;
132
133 END set_column_values;
134
135
136 PROCEDURE check_parent_existance AS
137 /*
138 || Created By : [email protected]
139 || Created On : 16-APR-2002
140 || Purpose : Checks for the existance of Parent records.
141 || Known limitations, enhancements or remarks :
142 || Change History :
143 || Who When What
144 || (reverse chronological order - newest change first)
145 */
146 BEGIN
147
148 IF (((old_references.apprv_profile_id = new_references.apprv_profile_id)) OR
149 ((new_references.apprv_profile_id IS NULL))) THEN
150 NULL;
151 ELSIF NOT igi_exp_apprv_profiles_pkg.get_pk_for_validation (
152 new_references.apprv_profile_id
153 ) THEN
154 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
155 --bug 3199481 fnd logging changes: sdixit: start block
156 IF (l_error_level >= l_debug_level ) THEN
157 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.check_parent_existance.msg1',FALSE);
158 END IF;
159 --bug 3199481 fnd logging changes: sdixit: end block
160 app_exception.raise_exception;
161 END IF;
162
163 IF (((old_references.tu_type_header_id = new_references.tu_type_header_id)) OR
164 ((new_references.tu_type_header_id IS NULL))) THEN
165 NULL;
166 ELSIF NOT igi_exp_tu_type_headers_pkg.get_pk_for_validation (
167 new_references.tu_type_header_id
168 ) THEN
169 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
170 --bug 3199481 fnd logging changes: sdixit: start block
171 IF (l_error_level >= l_debug_level ) THEN
172 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.check_parent_existance.msg2',FALSE);
173 END IF;
174 --bug 3199481 fnd logging changes: sdixit: end block
175 app_exception.raise_exception;
176 END IF;
177
178 END check_parent_existance;
179
180 PROCEDURE check_child_existance IS
181 /*
182 || Created By : [email protected]
183 || Created On : 14-APR-2002
184 || Purpose : Checks for the existance of Child records.
185 || Known limitations, enhancements or remarks :
186 || Change History :
187 || Who When What
188 || (reverse chronological order - newest change first)
189 */
190 BEGIN
191
192 igi_exp_dus_pkg.get_fk_igi_exp_tus (
193 old_references.tu_id
194 );
195
196 END check_child_existance;
197
198
199 FUNCTION get_pk_for_validation (
200 x_tu_id IN NUMBER
201 ) RETURN BOOLEAN AS
202 /*
203 || Created By : [email protected]
204 || Created On : 16-APR-2002
205 || Purpose : Validates the Primary Key of the table.
206 || Known limitations, enhancements or remarks :
207 || Change History :
208 || Who When What
209 || (reverse chronological order - newest change first)
210 */
211 CURSOR cur_rowid IS
212 SELECT rowid
213 FROM igi_exp_tus_all
214 WHERE tu_id = x_tu_id
215 FOR UPDATE NOWAIT;
216
217 lv_rowid cur_rowid%RowType;
218
219 BEGIN
220
221 OPEN cur_rowid;
222 FETCH cur_rowid INTO lv_rowid;
223 IF (cur_rowid%FOUND) THEN
224 CLOSE cur_rowid;
225 RETURN(TRUE);
226 ELSE
227 CLOSE cur_rowid;
228 RETURN(FALSE);
229 END IF;
230
231 END get_pk_for_validation;
232
233
234 PROCEDURE get_fk_igi_exp_apprv_profiles (
235 x_apprv_profile_id IN NUMBER
236 ) AS
237 /*
238 || Created By : [email protected]
239 || Created On : 16-APR-2002
240 || Purpose : Validates the Foreign Keys for the table.
241 || Known limitations, enhancements or remarks :
242 || Change History :
243 || Who When What
244 || (reverse chronological order - newest change first)
245 */
246 CURSOR cur_rowid IS
247 SELECT rowid
248 FROM igi_exp_tus_all
249 WHERE ((apprv_profile_id = x_apprv_profile_id));
250
251 lv_rowid cur_rowid%RowType;
252
253 BEGIN
254
255 OPEN cur_rowid;
256 FETCH cur_rowid INTO lv_rowid;
257 IF (cur_rowid%FOUND) THEN
258 CLOSE cur_rowid;
259 fnd_message.set_name ('FND', 'FND-CANNOT DELETE MASTER');
260 --bug 3199481 fnd logging changes: sdixit: start block
261 IF (l_error_level >= l_debug_level ) THEN
262 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.get_fk_igi_exp_apprv_profiles',FALSE);
263 END IF;
264 --bug 3199481 fnd logging changes: sdixit: end block
265 app_exception.raise_exception;
266 RETURN;
267 END IF;
268 CLOSE cur_rowid;
269
270 END get_fk_igi_exp_apprv_profiles;
271
272
273 PROCEDURE get_fk_igi_exp_tu_type_headers (
274 x_tu_type_header_id IN NUMBER
275 ) AS
276 /*
277 || Created By : [email protected]
278 || Created On : 16-APR-2002
279 || Purpose : Validates the Foreign Keys for the table.
280 || Known limitations, enhancements or remarks :
281 || Change History :
282 || Who When What
283 || (reverse chronological order - newest change first)
284 */
285 CURSOR cur_rowid IS
286 SELECT rowid
287 FROM igi_exp_tus_all
288 WHERE ((tu_type_header_id = x_tu_type_header_id));
289
290 lv_rowid cur_rowid%RowType;
291
292 BEGIN
293
294 OPEN cur_rowid;
295 FETCH cur_rowid INTO lv_rowid;
296 IF (cur_rowid%FOUND) THEN
297 CLOSE cur_rowid;
298 fnd_message.set_name ('FND', 'FND-CANNOT DELETE MASTER');
299 --bug 3199481 fnd logging changes: sdixit: start block
300 IF (l_error_level >= l_debug_level ) THEN
301 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.get_fk_igi_exp_tu_type_headers ',FALSE);
302 END IF;
303 --bug 3199481 fnd logging changes: sdixit: end block
304 app_exception.raise_exception;
305 RETURN;
306 END IF;
307 CLOSE cur_rowid;
308
309 END get_fk_igi_exp_tu_type_headers;
310
311
312 PROCEDURE before_dml (
313 p_action IN VARCHAR2,
314 x_rowid IN VARCHAR2 ,
315 x_tu_id IN NUMBER ,
316 x_tu_type_header_id IN NUMBER ,
317 x_tu_order_number IN VARCHAR2 ,
318 x_tu_legal_number IN VARCHAR2 ,
319 x_tu_description IN VARCHAR2 ,
320 x_tu_status IN VARCHAR2 ,
321 x_tu_currency_code IN VARCHAR2 ,
322 x_tu_amount IN NUMBER ,
323 x_apprv_profile_id IN NUMBER ,
324 x_next_approver_user_id IN NUMBER ,
325 x_tu_fiscal_year IN NUMBER ,
326 x_tu_by_user_id IN NUMBER ,
327 x_tu_date IN DATE ,
328 x_attribute_category IN VARCHAR2 ,
329 x_attribute1 IN VARCHAR2 ,
330 x_attribute2 IN VARCHAR2 ,
331 x_attribute3 IN VARCHAR2 ,
332 x_attribute4 IN VARCHAR2 ,
333 x_attribute5 IN VARCHAR2 ,
334 x_attribute6 IN VARCHAR2 ,
335 x_attribute7 IN VARCHAR2 ,
336 x_attribute8 IN VARCHAR2 ,
337 x_attribute9 IN VARCHAR2 ,
338 x_attribute10 IN VARCHAR2 ,
339 x_attribute11 IN VARCHAR2 ,
340 x_attribute12 IN VARCHAR2 ,
341 x_attribute13 IN VARCHAR2 ,
342 x_attribute14 IN VARCHAR2 ,
343 x_attribute15 IN VARCHAR2 ,
344 x_creation_date IN DATE ,
345 x_created_by IN NUMBER ,
346 x_last_update_date IN DATE ,
347 x_last_updated_by IN NUMBER ,
348 x_last_update_login IN NUMBER ) AS
349 /*
350 || Created By : [email protected]
351 || Created On : 16-APR-2002
352 || Purpose : Initialises the columns, Checks Constraints, Calls the
353 || Trigger Handlers for the table, before any DML operation.
354 || Known limitations, enhancements or remarks :
355 || Change History :
356 || Who When What
357 || (reverse chronological order - newest change first)
358 */
359 BEGIN
360
361 set_column_values (
362 p_action,
363 x_rowid,
364 x_tu_id,
365 x_tu_type_header_id,
366 x_tu_order_number,
367 x_tu_legal_number,
368 x_tu_description,
369 x_tu_status,
370 x_tu_currency_code,
371 x_tu_amount,
372 x_apprv_profile_id,
373 x_next_approver_user_id,
374 x_tu_fiscal_year,
375 x_tu_by_user_id,
376 x_tu_date,
377 x_attribute_category,
378 x_attribute1,
379 x_attribute2,
380 x_attribute3,
381 x_attribute4,
382 x_attribute5,
383 x_attribute6,
384 x_attribute7,
385 x_attribute8,
386 x_attribute9,
387 x_attribute10,
388 x_attribute11,
389 x_attribute12,
390 x_attribute13,
391 x_attribute14,
392 x_attribute15,
393 x_creation_date,
394 x_created_by,
395 x_last_update_date,
396 x_last_updated_by,
397 x_last_update_login
398 );
399
400 IF (p_action = 'INSERT') THEN
401 -- Call all the procedures related to Before Insert.
402 IF ( get_pk_for_validation(
403 new_references.tu_id
404 )
405 ) THEN
406 fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
407 --bug 3199481 fnd logging changes: sdixit: start block
408 IF (l_error_level >= l_debug_level ) THEN
409 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.before_dml.msg1',FALSE);
410 END IF;
411 --bug 3199481 fnd logging changes: sdixit: end block
412 app_exception.raise_exception;
413 END IF;
414 check_parent_existance;
415 ELSIF (p_action = 'UPDATE') THEN
416 -- Call all the procedures related to Before Update.
417 check_parent_existance;
418 ELSIF (p_action = 'VALIDATE_INSERT') THEN
419 -- Call all the procedures related to Before Insert.
420 IF ( get_pk_for_validation (
421 new_references.tu_id
422 )
423 ) THEN
424 fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
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.igiexpzb.IGI_EXP_TUS_PKG.before_dml.msg2',FALSE);
428 END IF;
429 --bug 3199481 fnd logging changes: sdixit: end block
430 app_exception.raise_exception;
431 END IF;
432 END IF;
433
434 END before_dml;
435
436
437 PROCEDURE insert_row (
438 x_org_id IN NUMBER,
439 x_rowid IN OUT NOCOPY VARCHAR2,
440 x_tu_id IN OUT NOCOPY NUMBER,
441 x_tu_type_header_id IN NUMBER,
442 x_tu_order_number IN VARCHAR2,
443 x_tu_legal_number IN VARCHAR2,
444 x_tu_description IN VARCHAR2,
445 x_tu_status IN VARCHAR2,
446 x_tu_currency_code IN VARCHAR2,
447 x_tu_amount IN NUMBER,
448 x_apprv_profile_id IN NUMBER,
449 x_next_approver_user_id IN NUMBER,
450 x_tu_fiscal_year IN NUMBER,
451 x_tu_by_user_id IN NUMBER,
452 x_tu_date IN DATE,
453 x_attribute_category IN VARCHAR2,
454 x_attribute1 IN VARCHAR2,
455 x_attribute2 IN VARCHAR2,
456 x_attribute3 IN VARCHAR2,
457 x_attribute4 IN VARCHAR2,
458 x_attribute5 IN VARCHAR2,
459 x_attribute6 IN VARCHAR2,
460 x_attribute7 IN VARCHAR2,
461 x_attribute8 IN VARCHAR2,
462 x_attribute9 IN VARCHAR2,
463 x_attribute10 IN VARCHAR2,
464 x_attribute11 IN VARCHAR2,
465 x_attribute12 IN VARCHAR2,
466 x_attribute13 IN VARCHAR2,
467 x_attribute14 IN VARCHAR2,
468 x_attribute15 IN VARCHAR2,
469 x_mode IN VARCHAR2 ) AS
470 /*
471 || Created By : [email protected]
472 || Created On : 16-APR-2002
473 || Purpose : Handles the INSERT DML logic for the table.
474 || Known limitations, enhancements or remarks :
475 || Change History :
476 || Who When What
477 || (reverse chronological order - newest change first)
478 */
479 CURSOR c IS
480 SELECT rowid
481 FROM igi_exp_tus_all
482 WHERE tu_id = x_tu_id;
483
484 x_last_update_date DATE;
485 x_last_updated_by NUMBER;
486 x_last_update_login NUMBER;
487
488 BEGIN
489
490 x_last_update_date := SYSDATE;
491 IF (x_mode = 'I') THEN
492 x_last_updated_by := 1;
493 x_last_update_login := 0;
494 ELSIF (x_mode = 'R') THEN
495 x_last_updated_by := fnd_global.user_id;
496 IF (x_last_updated_by IS NULL) THEN
497 x_last_updated_by := -1;
498 END IF;
499 x_last_update_login := fnd_global.login_id;
500 IF (x_last_update_login IS NULL) THEN
501 x_last_update_login := -1;
502 END IF;
503 ELSE
504 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
505 --bug 3199481 fnd logging changes: sdixit: start block
506 IF (l_error_level >= l_debug_level ) THEN
507 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.insert_row',FALSE);
508 END IF;
509 --bug 3199481 fnd logging changes: sdixit: end block
510 app_exception.raise_exception;
511 END IF;
512
513 SELECT igi_exp_tus_s1.NEXTVAL
514 INTO x_tu_id
515 FROM dual;
516
517 new_references.org_id := x_org_id;
518
519 before_dml(
520 p_action => 'INSERT',
521 x_rowid => x_rowid,
522 x_tu_id => x_tu_id,
523 x_tu_type_header_id => x_tu_type_header_id,
524 x_tu_order_number => x_tu_order_number,
525 x_tu_legal_number => x_tu_legal_number,
526 x_tu_description => x_tu_description,
527 x_tu_status => x_tu_status,
528 x_tu_currency_code => x_tu_currency_code,
529 x_tu_amount => x_tu_amount,
530 x_apprv_profile_id => x_apprv_profile_id,
531 x_next_approver_user_id => x_next_approver_user_id,
532 x_tu_fiscal_year => x_tu_fiscal_year,
533 x_tu_by_user_id => x_tu_by_user_id,
534 x_tu_date => x_tu_date,
535 x_attribute_category => x_attribute_category,
536 x_attribute1 => x_attribute1,
537 x_attribute2 => x_attribute2,
538 x_attribute3 => x_attribute3,
539 x_attribute4 => x_attribute4,
540 x_attribute5 => x_attribute5,
541 x_attribute6 => x_attribute6,
542 x_attribute7 => x_attribute7,
543 x_attribute8 => x_attribute8,
544 x_attribute9 => x_attribute9,
545 x_attribute10 => x_attribute10,
546 x_attribute11 => x_attribute11,
547 x_attribute12 => x_attribute12,
548 x_attribute13 => x_attribute13,
549 x_attribute14 => x_attribute14,
550 x_attribute15 => x_attribute15,
551 x_creation_date => x_last_update_date,
552 x_created_by => x_last_updated_by,
553 x_last_update_date => x_last_update_date,
554 x_last_updated_by => x_last_updated_by,
555 x_last_update_login => x_last_update_login
556 );
557
558 INSERT INTO igi_exp_tus_all (
559 tu_id,
560 tu_type_header_id,
561 tu_order_number,
562 tu_legal_number,
563 tu_description,
564 tu_status,
565 tu_currency_code,
566 tu_amount,
567 apprv_profile_id,
568 next_approver_user_id,
569 tu_fiscal_year,
570 tu_by_user_id,
571 tu_date,
572 org_id,
573 attribute_category,
574 attribute1,
575 attribute2,
576 attribute3,
577 attribute4,
578 attribute5,
579 attribute6,
580 attribute7,
581 attribute8,
582 attribute9,
583 attribute10,
584 attribute11,
585 attribute12,
586 attribute13,
587 attribute14,
588 attribute15,
589 creation_date,
590 created_by,
591 last_update_date,
592 last_updated_by,
593 last_update_login
594 ) VALUES (
595 new_references.tu_id,
596 new_references.tu_type_header_id,
597 new_references.tu_order_number,
598 new_references.tu_legal_number,
599 new_references.tu_description,
600 new_references.tu_status,
601 new_references.tu_currency_code,
602 new_references.tu_amount,
603 new_references.apprv_profile_id,
604 new_references.next_approver_user_id,
605 new_references.tu_fiscal_year,
606 new_references.tu_by_user_id,
607 new_references.tu_date,
608 new_references.org_id,
609 new_references.attribute_category,
610 new_references.attribute1,
611 new_references.attribute2,
612 new_references.attribute3,
613 new_references.attribute4,
614 new_references.attribute5,
615 new_references.attribute6,
616 new_references.attribute7,
617 new_references.attribute8,
618 new_references.attribute9,
619 new_references.attribute10,
620 new_references.attribute11,
621 new_references.attribute12,
622 new_references.attribute13,
623 new_references.attribute14,
624 new_references.attribute15,
625 x_last_update_date,
626 x_last_updated_by,
627 x_last_update_date,
628 x_last_updated_by,
629 x_last_update_login
630 );
631
632 OPEN c;
633 FETCH c INTO x_rowid;
634 IF (c%NOTFOUND) THEN
635 CLOSE c;
636 RAISE NO_DATA_FOUND;
637 END IF;
638 CLOSE c;
639
640 END insert_row;
641
642
643 PROCEDURE lock_row (
644 x_rowid IN VARCHAR2,
645 x_tu_id IN NUMBER,
646 x_tu_type_header_id IN NUMBER,
647 x_tu_order_number IN VARCHAR2,
648 x_tu_legal_number IN VARCHAR2,
649 x_tu_description IN VARCHAR2,
650 x_tu_status IN VARCHAR2,
651 x_tu_currency_code IN VARCHAR2,
652 x_tu_amount IN NUMBER,
653 x_apprv_profile_id IN NUMBER,
654 x_next_approver_user_id IN NUMBER,
655 x_tu_fiscal_year IN NUMBER,
656 x_tu_by_user_id IN NUMBER,
657 x_tu_date IN DATE,
658 x_attribute_category IN VARCHAR2,
659 x_attribute1 IN VARCHAR2,
660 x_attribute2 IN VARCHAR2,
661 x_attribute3 IN VARCHAR2,
662 x_attribute4 IN VARCHAR2,
663 x_attribute5 IN VARCHAR2,
664 x_attribute6 IN VARCHAR2,
665 x_attribute7 IN VARCHAR2,
666 x_attribute8 IN VARCHAR2,
667 x_attribute9 IN VARCHAR2,
668 x_attribute10 IN VARCHAR2,
669 x_attribute11 IN VARCHAR2,
670 x_attribute12 IN VARCHAR2,
671 x_attribute13 IN VARCHAR2,
672 x_attribute14 IN VARCHAR2,
673 x_attribute15 IN VARCHAR2
674 ) AS
675 /*
676 || Created By : [email protected]
677 || Created On : 16-APR-2002
678 || Purpose : Handles the LOCK mechanism for the table.
679 || Known limitations, enhancements or remarks :
680 || Change History :
681 || Who When What
682 || (reverse chronological order - newest change first)
683 */
684 CURSOR c1 IS
685 SELECT
686 tu_type_header_id,
687 tu_order_number,
688 tu_legal_number,
689 tu_description,
690 tu_status,
691 tu_currency_code,
692 tu_amount,
693 apprv_profile_id,
694 next_approver_user_id,
695 tu_fiscal_year,
696 tu_by_user_id,
697 tu_date,
698 attribute_category,
699 attribute1,
700 attribute2,
701 attribute3,
702 attribute4,
703 attribute5,
704 attribute6,
705 attribute7,
706 attribute8,
707 attribute9,
708 attribute10,
709 attribute11,
710 attribute12,
711 attribute13,
712 attribute14,
713 attribute15
714 FROM igi_exp_tus_all
715 WHERE rowid = x_rowid
716 FOR UPDATE NOWAIT;
717
718 tlinfo c1%ROWTYPE;
719
720 BEGIN
721
722 OPEN c1;
723 FETCH c1 INTO tlinfo;
724 IF (c1%notfound) THEN
725 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
726 --bug 3199481 fnd logging changes: sdixit: start block
727 IF (l_error_level >= l_debug_level ) THEN
728 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.lock_row',FALSE);
729 END IF;
730 --bug 3199481 fnd logging changes: sdixit: end block
731 CLOSE c1;
732 app_exception.raise_exception;
733 RETURN;
734 END IF;
735 CLOSE c1;
736
737 IF (
738 (tlinfo.tu_type_header_id = x_tu_type_header_id)
739 AND (tlinfo.tu_order_number = x_tu_order_number)
740 AND ((tlinfo.tu_legal_number = x_tu_legal_number) OR ((tlinfo.tu_legal_number IS NULL) AND (X_tu_legal_number IS NULL)))
741 AND ((tlinfo.tu_description = x_tu_description) OR ((tlinfo.tu_description IS NULL) AND (X_tu_description IS NULL)))
742 AND (tlinfo.tu_status = x_tu_status)
743 AND ((tlinfo.tu_currency_code = x_tu_currency_code) OR ((tlinfo.tu_currency_code IS NULL) AND (X_tu_currency_code IS NULL)))
744 AND (tlinfo.tu_amount = x_tu_amount)
745 AND (tlinfo.apprv_profile_id = x_apprv_profile_id)
746 AND ((tlinfo.next_approver_user_id = x_next_approver_user_id) OR ((tlinfo.next_approver_user_id IS NULL) AND (X_next_approver_user_id IS NULL)))
747 AND (tlinfo.tu_fiscal_year = x_tu_fiscal_year)
748 AND (tlinfo.tu_by_user_id = x_tu_by_user_id)
749 AND (tlinfo.tu_date = x_tu_date)
750 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
751 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
752 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
753 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
754 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
755 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
756 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
757 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
758 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
759 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
760 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
761 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
762 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
763 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
764 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
765 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
766 ) THEN
767 NULL;
768 ELSE
769 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
770 --bug 3199481 fnd logging changes: sdixit: start block
771 IF (l_error_level >= l_debug_level ) THEN
772 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.lock_row',FALSE);
773 END IF;
774 --bug 3199481 fnd logging changes: sdixit: end block
775 app_exception.raise_exception;
776 END IF;
777
778 RETURN;
779
780 END lock_row;
781
782
783 PROCEDURE update_row (
784 x_rowid IN VARCHAR2,
785 x_tu_id IN NUMBER,
786 x_tu_type_header_id IN NUMBER,
787 x_tu_order_number IN VARCHAR2,
788 x_tu_legal_number IN VARCHAR2,
789 x_tu_description IN VARCHAR2,
790 x_tu_status IN VARCHAR2,
791 x_tu_currency_code IN VARCHAR2,
792 x_tu_amount IN NUMBER,
793 x_apprv_profile_id IN NUMBER,
794 x_next_approver_user_id IN NUMBER,
795 x_tu_fiscal_year IN NUMBER,
796 x_tu_by_user_id IN NUMBER,
797 x_tu_date IN DATE,
798 x_attribute_category IN VARCHAR2,
799 x_attribute1 IN VARCHAR2,
800 x_attribute2 IN VARCHAR2,
801 x_attribute3 IN VARCHAR2,
802 x_attribute4 IN VARCHAR2,
803 x_attribute5 IN VARCHAR2,
804 x_attribute6 IN VARCHAR2,
805 x_attribute7 IN VARCHAR2,
806 x_attribute8 IN VARCHAR2,
807 x_attribute9 IN VARCHAR2,
808 x_attribute10 IN VARCHAR2,
809 x_attribute11 IN VARCHAR2,
810 x_attribute12 IN VARCHAR2,
811 x_attribute13 IN VARCHAR2,
812 x_attribute14 IN VARCHAR2,
813 x_attribute15 IN VARCHAR2,
814 x_mode IN VARCHAR2 ) AS
815 /*
816 || Created By : [email protected]
817 || Created On : 16-APR-2002
818 || Purpose : Handles the UPDATE DML logic for the table.
819 || Known limitations, enhancements or remarks :
820 || Change History :
821 || Who When What
822 || (reverse chronological order - newest change first)
823 */
824 x_last_update_date DATE ;
825 x_last_updated_by NUMBER;
826 x_last_update_login NUMBER;
827
828 BEGIN
829
830 x_last_update_date := SYSDATE;
831 IF (X_MODE = 'I') THEN
832 x_last_updated_by := 1;
833 x_last_update_login := 0;
834 ELSIF (x_mode = 'R') THEN
835 x_last_updated_by := fnd_global.user_id;
836 IF x_last_updated_by IS NULL THEN
837 x_last_updated_by := -1;
838 END IF;
839 x_last_update_login := fnd_global.login_id;
840 IF (x_last_update_login IS NULL) THEN
841 x_last_update_login := -1;
842 END IF;
843 ELSE
844 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
845 --bug 3199481 fnd logging changes: sdixit: start block
846 IF (l_error_level >= l_debug_level ) THEN
847 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.update_row',FALSE);
848 END IF;
849 --bug 3199481 fnd logging changes: sdixit: end block
850 app_exception.raise_exception;
851 END IF;
852
853 before_dml(
854 p_action => 'UPDATE',
855 x_rowid => x_rowid,
856 x_tu_id => x_tu_id,
857 x_tu_type_header_id => x_tu_type_header_id,
858 x_tu_order_number => x_tu_order_number,
859 x_tu_legal_number => x_tu_legal_number,
860 x_tu_description => x_tu_description,
861 x_tu_status => x_tu_status,
862 x_tu_currency_code => x_tu_currency_code,
863 x_tu_amount => x_tu_amount,
864 x_apprv_profile_id => x_apprv_profile_id,
865 x_next_approver_user_id => x_next_approver_user_id,
866 x_tu_fiscal_year => x_tu_fiscal_year,
867 x_tu_by_user_id => x_tu_by_user_id,
868 x_tu_date => x_tu_date,
869 x_attribute_category => x_attribute_category,
870 x_attribute1 => x_attribute1,
871 x_attribute2 => x_attribute2,
872 x_attribute3 => x_attribute3,
873 x_attribute4 => x_attribute4,
874 x_attribute5 => x_attribute5,
875 x_attribute6 => x_attribute6,
876 x_attribute7 => x_attribute7,
877 x_attribute8 => x_attribute8,
878 x_attribute9 => x_attribute9,
879 x_attribute10 => x_attribute10,
880 x_attribute11 => x_attribute11,
881 x_attribute12 => x_attribute12,
882 x_attribute13 => x_attribute13,
883 x_attribute14 => x_attribute14,
884 x_attribute15 => x_attribute15,
885 x_creation_date => x_last_update_date,
886 x_created_by => x_last_updated_by,
887 x_last_update_date => x_last_update_date,
888 x_last_updated_by => x_last_updated_by,
889 x_last_update_login => x_last_update_login
890 );
891
892 UPDATE igi_exp_tus_all
893 SET
894 tu_type_header_id = new_references.tu_type_header_id,
895 tu_order_number = new_references.tu_order_number,
896 tu_legal_number = new_references.tu_legal_number,
897 tu_description = new_references.tu_description,
898 tu_status = new_references.tu_status,
899 tu_currency_code = new_references.tu_currency_code,
900 tu_amount = new_references.tu_amount,
901 apprv_profile_id = new_references.apprv_profile_id,
902 next_approver_user_id = new_references.next_approver_user_id,
903 tu_fiscal_year = new_references.tu_fiscal_year,
904 tu_by_user_id = new_references.tu_by_user_id,
905 tu_date = new_references.tu_date,
906 attribute_category = new_references.attribute_category,
907 attribute1 = new_references.attribute1,
908 attribute2 = new_references.attribute2,
909 attribute3 = new_references.attribute3,
910 attribute4 = new_references.attribute4,
911 attribute5 = new_references.attribute5,
912 attribute6 = new_references.attribute6,
913 attribute7 = new_references.attribute7,
914 attribute8 = new_references.attribute8,
915 attribute9 = new_references.attribute9,
916 attribute10 = new_references.attribute10,
917 attribute11 = new_references.attribute11,
918 attribute12 = new_references.attribute12,
919 attribute13 = new_references.attribute13,
920 attribute14 = new_references.attribute14,
921 attribute15 = new_references.attribute15,
922 last_update_date = x_last_update_date,
923 last_updated_by = x_last_updated_by,
924 last_update_login = x_last_update_login
925 WHERE rowid = x_rowid;
926
927 IF (SQL%NOTFOUND) THEN
928 RAISE NO_DATA_FOUND;
929 END IF;
930
931 END update_row;
932
933
934 PROCEDURE add_row (
935 x_org_id IN NUMBER,
936 x_rowid IN OUT NOCOPY VARCHAR2,
937 x_tu_id IN OUT NOCOPY NUMBER,
938 x_tu_type_header_id IN NUMBER,
939 x_tu_order_number IN VARCHAR2,
940 x_tu_legal_number IN VARCHAR2,
941 x_tu_description IN VARCHAR2,
942 x_tu_status IN VARCHAR2,
943 x_tu_currency_code IN VARCHAR2,
944 x_tu_amount IN NUMBER,
945 x_apprv_profile_id IN NUMBER,
946 x_next_approver_user_id IN NUMBER,
947 x_tu_fiscal_year IN NUMBER,
948 x_tu_by_user_id IN NUMBER,
949 x_tu_date IN DATE,
950 x_attribute_category IN VARCHAR2,
951 x_attribute1 IN VARCHAR2,
952 x_attribute2 IN VARCHAR2,
953 x_attribute3 IN VARCHAR2,
954 x_attribute4 IN VARCHAR2,
955 x_attribute5 IN VARCHAR2,
956 x_attribute6 IN VARCHAR2,
957 x_attribute7 IN VARCHAR2,
958 x_attribute8 IN VARCHAR2,
959 x_attribute9 IN VARCHAR2,
960 x_attribute10 IN VARCHAR2,
961 x_attribute11 IN VARCHAR2,
962 x_attribute12 IN VARCHAR2,
963 x_attribute13 IN VARCHAR2,
964 x_attribute14 IN VARCHAR2,
965 x_attribute15 IN VARCHAR2,
966 x_mode IN VARCHAR2 ) AS
967 /*
968 || Created By : [email protected]
969 || Created On : 16-APR-2002
970 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
971 || Known limitations, enhancements or remarks :
972 || Change History :
973 || Who When What
974 || (reverse chronological order - newest change first)
975 */
976 CURSOR c1 IS
977 SELECT rowid
978 FROM igi_exp_tus_all
979 WHERE tu_id = x_tu_id;
980
981 BEGIN
982
983 OPEN c1;
984 FETCH c1 INTO x_rowid;
985 IF (c1%NOTFOUND) THEN
986 CLOSE c1;
987
988 insert_row (
989 x_org_id,
990 x_rowid,
991 x_tu_id,
992 x_tu_type_header_id,
993 x_tu_order_number,
994 x_tu_legal_number,
995 x_tu_description,
996 x_tu_status,
997 x_tu_currency_code,
998 x_tu_amount,
999 x_apprv_profile_id,
1000 x_next_approver_user_id,
1001 x_tu_fiscal_year,
1002 x_tu_by_user_id,
1003 x_tu_date,
1004 x_attribute_category,
1005 x_attribute1,
1006 x_attribute2,
1007 x_attribute3,
1008 x_attribute4,
1009 x_attribute5,
1010 x_attribute6,
1011 x_attribute7,
1012 x_attribute8,
1013 x_attribute9,
1014 x_attribute10,
1015 x_attribute11,
1016 x_attribute12,
1017 x_attribute13,
1018 x_attribute14,
1019 x_attribute15,
1020 x_mode
1021 );
1022 RETURN;
1023 END IF;
1024 CLOSE c1;
1025
1026 update_row (
1027 x_rowid,
1028 x_tu_id,
1029 x_tu_type_header_id,
1030 x_tu_order_number,
1031 x_tu_legal_number,
1032 x_tu_description,
1033 x_tu_status,
1034 x_tu_currency_code,
1035 x_tu_amount,
1036 x_apprv_profile_id,
1037 x_next_approver_user_id,
1038 x_tu_fiscal_year,
1039 x_tu_by_user_id,
1040 x_tu_date,
1041 x_attribute_category,
1042 x_attribute1,
1043 x_attribute2,
1044 x_attribute3,
1045 x_attribute4,
1046 x_attribute5,
1047 x_attribute6,
1048 x_attribute7,
1049 x_attribute8,
1050 x_attribute9,
1051 x_attribute10,
1052 x_attribute11,
1053 x_attribute12,
1054 x_attribute13,
1055 x_attribute14,
1056 x_attribute15,
1057 x_mode
1058 );
1059
1060 END add_row;
1061
1062
1063 PROCEDURE delete_row (
1064 x_rowid IN VARCHAR2
1065 ) AS
1066 /*
1067 || Created By : [email protected]
1068 || Created On : 16-APR-2002
1069 || Purpose : Handles the DELETE DML logic for the table.
1070 || Known limitations, enhancements or remarks :
1071 || Change History :
1072 || Who When What
1073 || (reverse chronological order - newest change first)
1074 */
1075
1076 BEGIN
1077
1078 before_dml (
1079 p_action => 'DELETE',
1080 x_rowid => x_rowid
1081 );
1082
1083 DELETE FROM igi_exp_tus_all
1084 WHERE rowid = x_rowid;
1085
1086 IF (SQL%NOTFOUND) THEN
1087 RAISE NO_DATA_FOUND;
1088 END IF;
1089
1090 END delete_row;
1091
1092 /* Commented becase of MOAC changes
1093
1094 FUNCTION get_org_id RETURN NUMBER AS
1095 CURSOR cur_orgid IS
1096 SELECT
1097 NVL ( TO_NUMBER ( DECODE ( SUBSTRB ( USERENV ('CLIENT_INFO' ), 1, 1 ), ' ', NULL, SUBSTRB (USERENV ('CLIENT_INFO'),1,10) ) ), NULL ) org_id FROM dual;
1098
1099 lv_org_id Number(15);
1100
1101 BEGIN
1102
1103 OPEN cur_orgid;
1104 FETCH cur_orgid INTO lv_org_id;
1105 CLOSE cur_orgid;
1106
1107 RETURN lv_org_id ;
1108
1109 END get_org_id ;
1110 */
1111
1112 END igi_exp_tus_pkg;