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