[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_ORDER_HDR_PKG
Source
1 PACKAGE BODY Igs_As_Order_Hdr_Pkg AS
2 /* $Header: IGSDI70B.pls 120.2 2006/05/26 05:16:53 shimitta ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_as_order_hdr%ROWTYPE;
5 new_references igs_as_order_hdr%ROWTYPE;
6 PROCEDURE set_column_values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 ,
9 x_order_number IN NUMBER ,
10 x_order_status IN VARCHAR2 ,
11 x_date_completed IN DATE ,
12 x_person_id IN NUMBER ,
13 x_addr_line_1 IN VARCHAR2 ,
14 x_addr_line_2 IN VARCHAR2 ,
15 x_addr_line_3 IN VARCHAR2 ,
16 x_addr_line_4 IN VARCHAR2 ,
17 x_city IN VARCHAR2 ,
18 x_state IN VARCHAR2 ,
19 x_province IN VARCHAR2 ,
20 x_county IN VARCHAR2 ,
21 x_country IN VARCHAR2 ,
22 x_postal_code IN VARCHAR2 ,
23 x_email_address IN VARCHAR2 ,
24 x_phone_country_code IN VARCHAR2 ,
25 x_phone_area_code IN VARCHAR2 ,
26 x_phone_number IN VARCHAR2 ,
27 x_phone_extension IN VARCHAR2 ,
28 x_fax_country_code IN VARCHAR2 ,
29 x_fax_area_code IN VARCHAR2 ,
30 x_fax_number IN VARCHAR2 ,
31 x_delivery_fee IN NUMBER ,
32 x_order_fee IN NUMBER ,
33 x_request_type IN VARCHAR2 ,
34 x_submit_method IN VARCHAR2 ,
35 x_invoice_id IN NUMBER ,
36 x_creation_date IN DATE ,
37 x_created_by IN NUMBER ,
38 x_last_update_date IN DATE ,
39 x_last_updated_by IN NUMBER ,
40 x_last_update_login IN NUMBER ,
41 x_order_description IN VARCHAR2 ,
42 x_order_placed_by IN NUMBER
43 ) AS
44 /*
45 || Created By : [email protected]
46 || Created On : 25-JAN-2002
47 || Purpose : Initialises the Old and New references for the columns of the table.
48 || Known limitations, enhancements or remarks :
49 || Change History :
50 || Who When What
51 || (reverse chronological order - newest change first)
52 */
53 CURSOR cur_old_ref_values IS
54 SELECT *
55 FROM igs_as_order_hdr
56 WHERE ROWID = x_rowid;
57 BEGIN
58 l_rowid := x_rowid;
59 -- Code for setting the Old and New Reference Values.
60 -- Populate Old Values.
61 OPEN cur_old_ref_values;
62 FETCH cur_old_ref_values INTO old_references;
63 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
64 CLOSE cur_old_ref_values;
65 FND_MSG_PUB.ADD;
66 RAISE FND_API.G_EXC_ERROR;
67 RETURN;
68 END IF;
69 CLOSE cur_old_ref_values;
70 -- Populate New Values.
71 new_references.order_number := x_order_number;
72 new_references.order_status := x_order_status;
73 new_references.date_completed := x_date_completed;
74 new_references.person_id := x_person_id;
75 new_references.addr_line_1 := x_addr_line_1;
76 new_references.addr_line_2 := x_addr_line_2;
77 new_references.addr_line_3 := x_addr_line_3;
78 new_references.addr_line_4 := x_addr_line_4;
79 new_references.city := x_city;
80 new_references.state := x_state;
81 new_references.province := x_province;
82 new_references.county := x_county;
83 new_references.country := x_country;
84 new_references.postal_code := x_postal_code;
85 new_references.email_address := x_email_address;
86 new_references.phone_country_code := x_phone_country_code;
87 new_references.phone_area_code := x_phone_area_code;
88 new_references.phone_number := x_phone_number;
89 new_references.phone_extension := x_phone_extension;
90 new_references.fax_country_code := x_fax_country_code;
91 new_references.fax_area_code := x_fax_area_code;
92 new_references.fax_number := x_fax_number;
93 new_references.delivery_fee := x_delivery_fee;
94 new_references.order_fee := x_order_fee;
95 new_references.request_type := x_request_type;
96 new_references.submit_method := x_submit_method;
97 new_references.invoice_id := x_invoice_id;
98 IF (p_action = 'UPDATE') THEN
99 new_references.creation_date := old_references.creation_date;
100 new_references.created_by := old_references.created_by;
101 ELSE
102 new_references.creation_date := x_creation_date;
103 new_references.created_by := x_created_by;
104 END IF;
105 new_references.last_update_date := x_last_update_date;
106 new_references.last_updated_by := x_last_updated_by;
107 new_references.last_update_login := x_last_update_login;
108 new_references.order_description := x_order_description;
109 new_references.order_placed_by := x_order_placed_by;
110 END set_column_values;
111 PROCEDURE check_parent_existance AS
112 /*
113 || Created By : [email protected]
114 || Created On : 25-JAN-2002
115 || Purpose : Checks for the existance of Parent records.
116 || Known limitations, enhancements or remarks :
117 || Change History :
118 || Who When What
119 || (reverse chronological order - newest change first)
120 */
121 BEGIN
122 IF (((old_references.invoice_id = new_references.invoice_id)) OR
123 ((new_references.invoice_id IS NULL))) THEN
124 NULL;
125 ELSIF NOT igs_fi_inv_int_pkg.get_pk_for_validation (
126 new_references.invoice_id
127 ) THEN
128 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
129 FND_MSG_PUB.ADD;
130 RAISE FND_API.G_EXC_ERROR;
131 END IF;
132 IF (((old_references.person_id = new_references.person_id)) OR
133 ((new_references.person_id IS NULL))) THEN
134 NULL;
135 ELSIF NOT igs_pe_person_pkg.get_pk_for_validation (
136 new_references.person_id
137 ) THEN
138 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
139 FND_MSG_PUB.ADD;
140 RAISE FND_API.G_EXC_ERROR;
141 END IF;
142 IF (((old_references.ORDER_PLACED_BY = new_references.ORDER_PLACED_BY)) OR
143 ((new_references.ORDER_PLACED_BY IS NULL))) THEN
144 NULL;
145 ELSIF NOT igs_pe_person_pkg.get_pk_for_validation (
146 new_references.ORDER_PLACED_BY
147 ) THEN
148 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
149 FND_MSG_PUB.ADD;
150 RAISE FND_API.G_EXC_ERROR;
151 END IF;
152
153 END check_parent_existance;
154
155 PROCEDURE check_child_existance IS
156 /*
157 || Created By : [email protected]
158 || Created On : 25-JAN-2002
159 || Purpose : Checks for the existance of Child records.
160 || Known limitations, enhancements or remarks :
161 || Change History :
162 || Who When What
163 || (reverse chronological order - newest change first)
164 */
165 BEGIN
166 igs_as_doc_details_pkg.get_fk_igs_as_order_hdr (
167 old_references.order_number
168 );
169 NULL;
170 --commented for testing by sjalasut
171 END check_child_existance;
172 FUNCTION get_pk_for_validation (
173 x_order_number IN NUMBER
174 ) RETURN BOOLEAN AS
175 /*
176 || Created By : [email protected]
177 || Created On : 25-JAN-2002
178 || Purpose : Validates the Primary Key of the table.
179 || Known limitations, enhancements or remarks :
180 || Change History :
181 || Who When What
182 || (reverse chronological order - newest change first)
183 */
184 CURSOR cur_rowid IS
185 SELECT ROWID
186 FROM igs_as_order_hdr
187 WHERE order_number = x_order_number
188 FOR UPDATE NOWAIT;
189 lv_rowid cur_rowid%ROWTYPE;
190 BEGIN
191 OPEN cur_rowid;
192 FETCH cur_rowid INTO lv_rowid;
193 IF (cur_rowid%FOUND) THEN
194 CLOSE cur_rowid;
195 RETURN(TRUE);
196 ELSE
197 CLOSE cur_rowid;
198 RETURN(FALSE);
199 END IF;
200 END get_pk_for_validation;
201 PROCEDURE get_fk_igs_fi_inv_int (
202 x_invoice_id IN NUMBER
203 ) AS
204 /*
205 || Created By : [email protected]
206 || Created On : 25-JAN-2002
207 || Purpose : Validates the Foreign Keys for the table.
208 || Known limitations, enhancements or remarks :
209 || Change History :
210 || Who When What
211 || (reverse chronological order - newest change first)
212 */
213 CURSOR cur_rowid IS
214 SELECT ROWID
215 FROM igs_as_order_hdr
216 WHERE ((invoice_id = x_invoice_id));
217 lv_rowid cur_rowid%ROWTYPE;
218 BEGIN
219 OPEN cur_rowid;
220 FETCH cur_rowid INTO lv_rowid;
221 IF (cur_rowid%FOUND) THEN
222 CLOSE cur_rowid;
223 fnd_message.set_name ('IGS', 'IGS_AS_TORD_INVI_FK');
224 FND_MSG_PUB.ADD;
225 RAISE FND_API.G_EXC_ERROR;
226 RETURN;
227 END IF;
228 CLOSE cur_rowid;
229 END get_fk_igs_fi_inv_int;
230 PROCEDURE before_dml (
231 p_action IN VARCHAR2,
232 x_rowid IN VARCHAR2 ,
233 x_order_number IN NUMBER ,
234 x_order_status IN VARCHAR2 ,
235 x_date_completed IN DATE ,
236 x_person_id IN NUMBER ,
237 x_addr_line_1 IN VARCHAR2 ,
238 x_addr_line_2 IN VARCHAR2 ,
239 x_addr_line_3 IN VARCHAR2 ,
240 x_addr_line_4 IN VARCHAR2 ,
241 x_city IN VARCHAR2 ,
242 x_state IN VARCHAR2 ,
243 x_province IN VARCHAR2 ,
244 x_county IN VARCHAR2 ,
245 x_country IN VARCHAR2 ,
246 x_postal_code IN VARCHAR2 ,
247 x_email_address IN VARCHAR2 ,
248 x_phone_country_code IN VARCHAR2 ,
249 x_phone_area_code IN VARCHAR2 ,
250 x_phone_number IN VARCHAR2 ,
251 x_phone_extension IN VARCHAR2 ,
252 x_fax_country_code IN VARCHAR2 ,
253 x_fax_area_code IN VARCHAR2 ,
254 x_fax_number IN VARCHAR2 ,
255 x_delivery_fee IN NUMBER ,
256 x_order_fee IN NUMBER ,
257 x_request_type IN VARCHAR2 ,
258 x_submit_method IN VARCHAR2 ,
259 x_invoice_id IN NUMBER ,
260 x_creation_date IN DATE ,
261 x_created_by IN NUMBER ,
262 x_last_update_date IN DATE ,
263 x_last_updated_by IN NUMBER ,
264 x_last_update_login IN NUMBER ,
265 x_order_description IN VARCHAR2 ,
266 x_order_placed_by IN NUMBER
267 ) AS
268 /*
269 || Created By : [email protected]
270 || Created On : 25-JAN-2002
271 || Purpose : Initialises the columns, Checks Constraints, Calls the
272 || Trigger Handlers for the table, before any DML operation.
273 || Known limitations, enhancements or remarks :
274 || Change History :
275 || Who When What
276 || (reverse chronological order - newest change first)
277 */
278 BEGIN
279 set_column_values (
280 p_action,
281 x_rowid,
282 x_order_number,
283 x_order_status,
284 x_date_completed,
285 x_person_id,
286 x_addr_line_1,
287 x_addr_line_2,
288 x_addr_line_3,
289 x_addr_line_4,
290 x_city,
291 x_state,
292 x_province,
293 x_county,
294 x_country,
295 x_postal_code,
296 x_email_address,
297 x_phone_country_code,
298 x_phone_area_code,
299 x_phone_number,
300 x_phone_extension,
301 x_fax_country_code,
302 x_fax_area_code,
303 x_fax_number,
304 x_delivery_fee,
305 x_order_fee,
306 x_request_type,
307 x_submit_method,
308 x_invoice_id,
309 x_creation_date,
310 x_created_by,
311 x_last_update_date,
312 x_last_updated_by,
313 x_last_update_login ,
314 x_order_description ,
315 x_order_placed_by
316 );
317 IF (p_action = 'INSERT') THEN
318 -- Call all the procedures related to Before Insert.
319 IF ( get_pk_for_validation(
320 new_references.order_number
321 )
322 ) THEN
323 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
324 FND_MSG_PUB.ADD;
325 RAISE FND_API.G_EXC_ERROR;
326 END IF;
327 check_parent_existance;
328 ELSIF (p_action = 'UPDATE') THEN
329 -- Call all the procedures related to Before Update.
330 check_parent_existance;
331 ELSIF (p_action = 'DELETE') THEN
332 -- Call all the procedures related to Before Delete.
333 check_child_existance;
334 ELSIF (p_action = 'VALIDATE_INSERT') THEN
335 -- Call all the procedures related to Before Insert.
336 IF ( get_pk_for_validation (
337 new_references.order_number
338 )
339 ) THEN
340 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
341 FND_MSG_PUB.ADD;
342 RAISE FND_API.G_EXC_ERROR;
343 END IF;
344 ELSIF (p_action = 'VALIDATE_DELETE') THEN
345 check_child_existance;
346 END IF;
347 END before_dml;
348
349 PROCEDURE insert_row (
350 x_msg_count OUT NOCOPY NUMBER,
351 x_msg_data OUT NOCOPY VARCHAR2,
352 x_return_status OUT NOCOPY VARCHAR2,
353 x_rowid IN OUT NOCOPY VARCHAR2,
354 x_order_number IN OUT NOCOPY NUMBER,
355 x_order_status IN VARCHAR2,
356 x_date_completed IN DATE,
357 x_person_id IN NUMBER,
358 x_addr_line_1 IN VARCHAR2,
359 x_addr_line_2 IN VARCHAR2,
360 x_addr_line_3 IN VARCHAR2,
361 x_addr_line_4 IN VARCHAR2,
362 x_city IN VARCHAR2,
363 x_state IN VARCHAR2,
364 x_province IN VARCHAR2,
365 x_county IN VARCHAR2,
366 x_country IN VARCHAR2,
367 x_postal_code IN VARCHAR2,
368 x_email_address IN VARCHAR2,
369 x_phone_country_code IN VARCHAR2,
370 x_phone_area_code IN VARCHAR2,
371 x_phone_number IN VARCHAR2,
372 x_phone_extension IN VARCHAR2,
373 x_fax_country_code IN VARCHAR2,
374 x_fax_area_code IN VARCHAR2,
375 x_fax_number IN VARCHAR2,
376 x_delivery_fee IN NUMBER,
377 x_order_fee IN NUMBER,
378 x_request_type IN VARCHAR2,
379 x_submit_method IN VARCHAR2,
380 x_invoice_id IN NUMBER,
381 x_mode IN VARCHAR2 ,
382 x_order_description IN VARCHAR2 ,
383 x_order_placed_by IN NUMBER
384 ) AS
385 /*
386 || Created By : [email protected]
387 || Created On : 25-JAN-2002
388 || Purpose : Handles the INSERT DML logic for the table.
389 || Known limitations, enhancements or remarks :
390 || Change History :
391 || Who When What
392 || (reverse chronological order - newest change first)
393 */
394 CURSOR c IS
395 SELECT ROWID
396 FROM igs_as_order_hdr
397 WHERE order_number = x_order_number;
398 x_last_update_date DATE;
399 x_last_updated_by NUMBER;
400 x_last_update_login NUMBER;
401 x_request_id NUMBER;
402 x_program_id NUMBER;
403 x_program_application_id NUMBER;
404 x_program_update_date DATE;
405 BEGIN
406
407 -- Initailize the error count, error data etc.
408 FND_MSG_PUB.initialize;
409
410 x_last_update_date := SYSDATE;
411 IF (x_mode = 'I') THEN
412 x_last_updated_by := 1;
413 x_last_update_login := 0;
414 ELSIF (x_mode = 'R') THEN
415 x_last_updated_by := fnd_global.user_id;
416 IF (x_last_updated_by IS NULL) THEN
417 x_last_updated_by := -1;
418 END IF;
419 x_last_update_login := fnd_global.login_id;
420 IF (x_last_update_login IS NULL) THEN
421 x_last_update_login := -1;
422 END IF;
423 x_request_id := fnd_global.conc_request_id;
424 x_program_id := fnd_global.conc_program_id;
425 x_program_application_id := fnd_global.prog_appl_id;
426 IF (x_request_id = -1) THEN
427 x_request_id := NULL;
428 x_program_id := NULL;
429 x_program_application_id := NULL;
430 x_program_update_date := NULL;
431 ELSE
432 x_program_update_date := SYSDATE;
433 END IF;
434 ELSE
435 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
436 FND_MSG_PUB.ADD;
437 RAISE FND_API.G_EXC_ERROR;
438
439 END IF;
440 -- adding this if codition to avoid Lock row problem from self service pages
441 IF x_order_number IS NULL THEN
442 SELECT igs_as_order_hdr_s.NEXTVAL
443 INTO x_order_number
444 FROM dual;
445 END IF;
446 before_dml(
447 p_action => 'INSERT',
448 x_rowid => x_rowid,
449 x_order_number => x_order_number,
450 x_order_status => x_order_status,
451 x_date_completed => x_date_completed,
452 x_person_id => x_person_id,
453 x_addr_line_1 => x_addr_line_1,
454 x_addr_line_2 => x_addr_line_2,
455 x_addr_line_3 => x_addr_line_3,
456 x_addr_line_4 => x_addr_line_4,
457 x_city => x_city,
458 x_state => x_state,
459 x_province => x_province,
460 x_county => x_county,
461 x_country => x_country,
462 x_postal_code => x_postal_code,
463 x_email_address => x_email_address,
464 x_phone_country_code => x_phone_country_code,
465 x_phone_area_code => x_phone_area_code,
466 x_phone_number => x_phone_number,
467 x_phone_extension => x_phone_extension,
468 x_fax_country_code => x_fax_country_code,
469 x_fax_area_code => x_fax_area_code,
470 x_fax_number => x_fax_number,
471 x_delivery_fee => x_delivery_fee,
472 x_order_fee => x_order_fee,
473 x_request_type => x_request_type,
474 x_submit_method => x_submit_method,
475 x_invoice_id => x_invoice_id,
476 x_creation_date => x_last_update_date,
477 x_created_by => x_last_updated_by,
478 x_last_update_date => x_last_update_date,
479 x_last_updated_by => x_last_updated_by,
480 x_last_update_login => x_last_update_login ,
481 x_order_description => x_order_description,
482 x_order_placed_by => x_order_placed_by
483 );
484 INSERT INTO igs_as_order_hdr (
485 order_number,
486 order_status,
487 date_completed,
488 person_id,
489 addr_line_1,
490 addr_line_2,
491 addr_line_3,
492 addr_line_4,
493 city,
494 state,
495 province,
496 county,
497 country,
498 postal_code,
499 email_address,
500 phone_country_code,
501 phone_area_code,
502 phone_number,
503 phone_extension,
504 fax_country_code,
505 fax_area_code,
506 fax_number,
507 delivery_fee,
508 order_fee,
509 request_type,
510 submit_method,
511 invoice_id,
512 creation_date,
513 created_by,
514 last_update_date,
515 last_updated_by,
516 last_update_login,
517 request_id,
518 program_id,
519 program_application_id,
520 program_update_date ,
521 order_description ,
522 order_placed_by
523
524 ) VALUES (
525 new_references.order_number,
526 new_references.order_status,
527 new_references.date_completed,
528 new_references.person_id,
529 new_references.addr_line_1,
530 new_references.addr_line_2,
531 new_references.addr_line_3,
532 new_references.addr_line_4,
533 new_references.city,
534 new_references.state,
535 new_references.province,
536 new_references.county,
537 new_references.country,
538 new_references.postal_code,
539 new_references.email_address,
540 new_references.phone_country_code,
541 new_references.phone_area_code,
542 new_references.phone_number,
543 new_references.phone_extension,
544 new_references.fax_country_code,
545 new_references.fax_area_code,
546 new_references.fax_number,
547 new_references.delivery_fee,
548 new_references.order_fee,
549 new_references.request_type,
550 new_references.submit_method,
551 new_references.invoice_id,
552 x_last_update_date,
553 x_last_updated_by,
554 x_last_update_date,
555 x_last_updated_by,
556 x_last_update_login ,
557 x_request_id,
558 x_program_id,
559 x_program_application_id,
560 x_program_update_date ,
561 new_references.order_description ,
562 new_references.order_placed_by
563
564 );
565 OPEN c;
566 FETCH c INTO x_rowid;
567 IF (c%NOTFOUND) THEN
568 CLOSE c;
569 RAISE NO_DATA_FOUND;
570 END IF;
571 CLOSE c;
572
573 -- Initialize API return status to success.
574 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
575 -- Standard call to get message count and if count is 1, get message
576 -- info.
577 FND_MSG_PUB.Count_And_Get(
578 p_encoded => FND_API.G_FALSE,
579 p_count => x_MSG_COUNT,
580 p_data => X_MSG_DATA);
581
582 EXCEPTION
583 WHEN FND_API.G_EXC_ERROR THEN
584 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
585 FND_MSG_PUB.Count_And_Get(
586 p_encoded => FND_API.G_FALSE,
587 p_count => x_MSG_COUNT,
588 p_data => X_MSG_DATA);
589 RETURN;
590 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
591 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
592 FND_MSG_PUB.Count_And_Get(
593 p_encoded => FND_API.G_FALSE,
594 p_count => x_MSG_COUNT,
595 p_data => X_MSG_DATA);
596 RETURN;
597 WHEN OTHERS THEN
598 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
599 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
600 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
601 FND_MSG_PUB.ADD;
602 FND_MSG_PUB.Count_And_Get(
603 p_encoded => FND_API.G_FALSE,
604 p_count => x_MSG_COUNT,
605 p_data => X_MSG_DATA);
606 RETURN;
607
608 END insert_row;
609
610
611 PROCEDURE lock_row (
612 x_msg_count OUT NOCOPY NUMBER,
613 x_msg_data OUT NOCOPY VARCHAR2,
614 x_return_status OUT NOCOPY VARCHAR2,
615 x_rowid IN VARCHAR2,
616 x_order_number IN NUMBER,
617 x_order_status IN VARCHAR2,
618 x_date_completed IN DATE,
619 x_person_id IN NUMBER,
620 x_addr_line_1 IN VARCHAR2,
621 x_addr_line_2 IN VARCHAR2,
622 x_addr_line_3 IN VARCHAR2,
623 x_addr_line_4 IN VARCHAR2,
624 x_city IN VARCHAR2,
625 x_state IN VARCHAR2,
626 x_province IN VARCHAR2,
627 x_county IN VARCHAR2,
628 x_country IN VARCHAR2,
629 x_postal_code IN VARCHAR2,
630 x_email_address IN VARCHAR2,
631 x_phone_country_code IN VARCHAR2,
632 x_phone_area_code IN VARCHAR2,
633 x_phone_number IN VARCHAR2,
634 x_phone_extension IN VARCHAR2,
635 x_fax_country_code IN VARCHAR2,
636 x_fax_area_code IN VARCHAR2,
637 x_fax_number IN VARCHAR2,
638 x_delivery_fee IN NUMBER,
639 x_order_fee IN NUMBER,
640 x_request_type IN VARCHAR2,
641 x_submit_method IN VARCHAR2,
642 x_invoice_id IN NUMBER,
643 x_order_description IN VARCHAR2 ,
644 x_order_placed_by IN NUMBER
645
646 ) AS
647 /*
648 || Created By : [email protected]
649 || Created On : 25-JAN-2002
650 || Purpose : Handles the LOCK mechanism for the table.
651 || Known limitations, enhancements or remarks :
652 || Change History :
653 || Who When What
654 || (reverse chronological order - newest change first)
655 */
656 CURSOR c1 IS
657 SELECT
658 order_status,
659 date_completed,
660 person_id,
661 addr_line_1,
662 addr_line_2,
663 addr_line_3,
664 addr_line_4,
665 city,
666 state,
667 province,
668 county,
669 country,
670 postal_code,
671 email_address,
672 phone_country_code,
673 phone_area_code,
674 phone_number,
675 phone_extension,
676 fax_country_code,
677 fax_area_code,
678 fax_number,
679 delivery_fee,
680 order_fee,
681 request_type,
682 submit_method,
683 invoice_id,
684 order_description ,
685 order_placed_by
686
687 FROM igs_as_order_hdr
688 WHERE ROWID = x_rowid
689 FOR UPDATE NOWAIT;
690 tlinfo c1%ROWTYPE;
691 BEGIN
692
693 -- Initailize the error count, error data etc.
694 FND_MSG_PUB.initialize;
695
696 OPEN c1;
697 FETCH c1 INTO tlinfo;
698 IF (c1%NOTFOUND) THEN
699 CLOSE c1;
700 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
701 FND_MSG_PUB.ADD;
702 RAISE FND_API.G_EXC_ERROR;
703
704 END IF;
705 CLOSE c1;
706 IF (
707 ((tlinfo.order_status = x_order_status) OR ((tlinfo.order_status IS NULL) AND (X_order_status IS NULL)))
708 AND ((tlinfo.date_completed = x_date_completed) OR ((tlinfo.date_completed IS NULL) AND (X_date_completed IS NULL)))
709 AND (tlinfo.person_id = x_person_id)
710 AND (tlinfo.addr_line_1 = x_addr_line_1)
711 AND ((tlinfo.addr_line_2 = x_addr_line_2) OR ((tlinfo.addr_line_2 IS NULL) AND (X_addr_line_2 IS NULL)))
712 AND ((tlinfo.addr_line_3 = x_addr_line_3) OR ((tlinfo.addr_line_3 IS NULL) AND (X_addr_line_3 IS NULL)))
713 AND ((tlinfo.addr_line_4 = x_addr_line_4) OR ((tlinfo.addr_line_4 IS NULL) AND (X_addr_line_4 IS NULL)))
714 AND ((tlinfo.city = x_city) OR ((tlinfo.city IS NULL) AND (X_city IS NULL)))
715 AND ((tlinfo.state = x_state) OR ((tlinfo.state IS NULL) AND (X_state IS NULL)))
716 AND ((tlinfo.province = x_province) OR ((tlinfo.province IS NULL) AND (X_province IS NULL)))
717 AND ((tlinfo.county = x_county) OR ((tlinfo.county IS NULL) AND (X_county IS NULL)))
718 AND (tlinfo.country = x_country)
719 AND ((tlinfo.postal_code = x_postal_code) OR ((tlinfo.postal_code IS NULL) AND (X_postal_code IS NULL)))
720 AND ((tlinfo.email_address = x_email_address) OR ((tlinfo.email_address IS NULL) AND (X_email_address IS NULL)))
721 AND ((tlinfo.phone_country_code = x_phone_country_code) OR ((tlinfo.phone_country_code IS NULL) AND (X_phone_country_code IS NULL)))
722 AND ((tlinfo.phone_area_code = x_phone_area_code) OR ((tlinfo.phone_area_code IS NULL) AND (X_phone_area_code IS NULL)))
723 AND ((tlinfo.phone_number = x_phone_number) OR ((tlinfo.phone_number IS NULL) AND (X_phone_number IS NULL)))
724 AND ((tlinfo.phone_extension = x_phone_extension) OR ((tlinfo.phone_extension IS NULL) AND (X_phone_extension IS NULL)))
725 AND ((tlinfo.fax_country_code = x_fax_country_code) OR ((tlinfo.fax_country_code IS NULL) AND (X_fax_country_code IS NULL)))
726 AND ((tlinfo.fax_area_code = x_fax_area_code) OR ((tlinfo.fax_area_code IS NULL) AND (X_fax_area_code IS NULL)))
727 AND ((tlinfo.fax_number = x_fax_number) OR ((tlinfo.fax_number IS NULL) AND (X_fax_number IS NULL)))
728 AND ((tlinfo.delivery_fee = x_delivery_fee) OR ((tlinfo.delivery_fee IS NULL) AND (X_delivery_fee IS NULL)))
729 AND ((tlinfo.order_fee = x_order_fee) OR ((tlinfo.order_fee IS NULL) AND (X_order_fee IS NULL)))
730 AND ((tlinfo.request_type = x_request_type) OR ((tlinfo.request_type IS NULL) AND (X_request_type IS NULL)))
731 AND ((tlinfo.submit_method = x_submit_method) OR ((tlinfo.submit_method IS NULL) AND (X_submit_method IS NULL)))
732 AND ((tlinfo.invoice_id = x_invoice_id) OR ((tlinfo.invoice_id IS NULL) AND (X_invoice_id IS NULL)))
733 AND ((tlinfo.order_description = x_order_description) OR ((tlinfo.order_description IS NULL) AND (x_order_description IS NULL)))
734 AND ((tlinfo.order_placed_by = x_order_placed_by) OR ((tlinfo.order_placed_by IS NULL) AND (x_order_placed_by IS NULL)))
735 ) THEN
736 NULL;
737 ELSE
738 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
739 FND_MSG_PUB.ADD;
740 RAISE FND_API.G_EXC_ERROR;
741 END IF;
742 RETURN;
743 -- Initialize API return status to success.
744 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
745 -- Standard call to get message count and if count is 1, get message
746 -- info.
747 FND_MSG_PUB.Count_And_Get(
748 p_encoded => FND_API.G_FALSE,
749 p_count => x_MSG_COUNT,
750 p_data => X_MSG_DATA);
751
752 EXCEPTION
753 WHEN FND_API.G_EXC_ERROR THEN
754 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
755 FND_MSG_PUB.Count_And_Get(
756 p_encoded => FND_API.G_FALSE,
757 p_count => x_MSG_COUNT,
758 p_data => X_MSG_DATA);
759 RETURN;
760 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
761 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
762 FND_MSG_PUB.Count_And_Get(
763 p_encoded => FND_API.G_FALSE,
764 p_count => x_MSG_COUNT,
765 p_data => X_MSG_DATA);
766 RETURN;
767 WHEN OTHERS THEN
768 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
769 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
770 FND_MESSAGE.SET_TOKEN('NAME','Lock_Row : '||SQLERRM);
771 FND_MSG_PUB.ADD;
772 FND_MSG_PUB.Count_And_Get(
773 p_encoded => FND_API.G_FALSE,
774 p_count => x_MSG_COUNT,
775 p_data => X_MSG_DATA);
776 RETURN;
777
778 END lock_row;
779
780 PROCEDURE update_row (
781 x_msg_count OUT NOCOPY NUMBER,
782 x_msg_data OUT NOCOPY VARCHAR2,
783 x_return_status OUT NOCOPY VARCHAR2,
784 x_rowid IN VARCHAR2,
785 x_order_number IN NUMBER,
786 x_order_status IN VARCHAR2,
787 x_date_completed IN DATE,
788 x_person_id IN NUMBER,
789 x_addr_line_1 IN VARCHAR2,
790 x_addr_line_2 IN VARCHAR2,
791 x_addr_line_3 IN VARCHAR2,
792 x_addr_line_4 IN VARCHAR2,
793 x_city IN VARCHAR2,
794 x_state IN VARCHAR2,
795 x_province IN VARCHAR2,
796 x_county IN VARCHAR2,
797 x_country IN VARCHAR2,
798 x_postal_code IN VARCHAR2,
799 x_email_address IN VARCHAR2,
800 x_phone_country_code IN VARCHAR2,
801 x_phone_area_code IN VARCHAR2,
802 x_phone_number IN VARCHAR2,
803 x_phone_extension IN VARCHAR2,
804 x_fax_country_code IN VARCHAR2,
805 x_fax_area_code IN VARCHAR2,
806 x_fax_number IN VARCHAR2,
807 x_delivery_fee IN NUMBER,
808 x_order_fee IN NUMBER,
809 x_request_type IN VARCHAR2,
810 x_submit_method IN VARCHAR2,
811 x_invoice_id IN NUMBER,
812 x_mode IN VARCHAR2 ,
813 x_order_description IN VARCHAR2 ,
814 x_order_placed_by IN NUMBER,
815 p_init_msg_list IN VARCHAR2
816 ) AS
817 /*
818 || Created By : [email protected]
819 || Created On : 25-JAN-2002
820 || Purpose : Handles the UPDATE DML logic for the table.
821 || Known limitations, enhancements or remarks :
822 || Change History :
823 || Who When What
824 || (reverse chronological order - newest change first)
825 */
826 x_last_update_date DATE ;
827 x_last_updated_by NUMBER;
828 x_last_update_login NUMBER;
829 x_request_id NUMBER;
830 x_program_id NUMBER;
831 x_program_application_id NUMBER;
832 x_program_update_date DATE;
833 BEGIN
834 -- Initailize the error count, error data etc.
835 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
836 FND_MSG_PUB.INITIALIZE;
837 END IF;
838 x_last_update_date := SYSDATE;
839 IF (X_MODE = 'I') THEN
840 x_last_updated_by := 1;
841 x_last_update_login := 0;
842 ELSIF (x_mode = 'R') THEN
843 x_last_updated_by := fnd_global.user_id;
844 IF x_last_updated_by IS NULL THEN
845 x_last_updated_by := -1;
846 END IF;
847 x_last_update_login := fnd_global.login_id;
848 IF (x_last_update_login IS NULL) THEN
849 x_last_update_login := -1;
850 END IF;
851 ELSE
852 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
853 FND_MSG_PUB.ADD;
854 RAISE FND_API.G_EXC_ERROR;
855 END IF;
856 before_dml(
857 p_action => 'UPDATE',
858 x_rowid => x_rowid,
859 x_order_number => x_order_number,
860 x_order_status => x_order_status,
861 x_date_completed => x_date_completed,
862 x_person_id => x_person_id,
863 x_addr_line_1 => x_addr_line_1,
864 x_addr_line_2 => x_addr_line_2,
865 x_addr_line_3 => x_addr_line_3,
866 x_addr_line_4 => x_addr_line_4,
867 x_city => x_city,
868 x_state => x_state,
869 x_province => x_province,
870 x_county => x_county,
871 x_country => x_country,
872 x_postal_code => x_postal_code,
873 x_email_address => x_email_address,
874 x_phone_country_code => x_phone_country_code,
875 x_phone_area_code => x_phone_area_code,
876 x_phone_number => x_phone_number,
877 x_phone_extension => x_phone_extension,
878 x_fax_country_code => x_fax_country_code,
879 x_fax_area_code => x_fax_area_code,
880 x_fax_number => x_fax_number,
881 x_delivery_fee => x_delivery_fee,
882 x_order_fee => x_order_fee,
883 x_request_type => x_request_type,
884 x_submit_method => x_submit_method,
885 x_invoice_id => x_invoice_id,
886 x_creation_date => x_last_update_date,
887 x_created_by => x_last_updated_by,
888 x_last_update_date => x_last_update_date,
889 x_last_updated_by => x_last_updated_by,
890 x_last_update_login => x_last_update_login ,
891 x_order_description => x_order_description,
892 x_order_placed_by => x_order_placed_by
893
894 );
895 IF (x_mode = 'R') THEN
896 x_request_id := fnd_global.conc_request_id;
897 x_program_id := fnd_global.conc_program_id;
898 x_program_application_id := fnd_global.prog_appl_id;
899 IF (x_request_id = -1) THEN
900 x_request_id := old_references.request_id;
901 x_program_id := old_references.program_id;
902 x_program_application_id := old_references.program_application_id;
903 x_program_update_date := old_references.program_update_date;
904 ELSE
905 x_program_update_date := SYSDATE;
906 END IF;
907 END IF;
908 UPDATE igs_as_order_hdr
909 SET
910 order_status = new_references.order_status,
911 date_completed = new_references.date_completed,
912 person_id = new_references.person_id,
913 addr_line_1 = new_references.addr_line_1,
914 addr_line_2 = new_references.addr_line_2,
915 addr_line_3 = new_references.addr_line_3,
916 addr_line_4 = new_references.addr_line_4,
917 city = new_references.city,
918 state = new_references.state,
919 province = new_references.province,
920 county = new_references.county,
921 country = new_references.country,
922 postal_code = new_references.postal_code,
923 email_address = new_references.email_address,
924 phone_country_code = new_references.phone_country_code,
925 phone_area_code = new_references.phone_area_code,
926 phone_number = new_references.phone_number,
927 phone_extension = new_references.phone_extension,
928 fax_country_code = new_references.fax_country_code,
929 fax_area_code = new_references.fax_area_code,
930 fax_number = new_references.fax_number,
931 delivery_fee = new_references.delivery_fee,
932 order_fee = new_references.order_fee,
933 request_type = new_references.request_type,
934 submit_method = new_references.submit_method,
935 invoice_id = new_references.invoice_id,
936 last_update_date = x_last_update_date,
937 last_updated_by = x_last_updated_by,
938 last_update_login = x_last_update_login ,
939 request_id = x_request_id,
940 program_id = x_program_id,
941 program_application_id = x_program_application_id,
942 program_update_date = x_program_update_date ,
943 order_description = x_order_description,
944 order_placed_by = x_order_placed_by
945
946 WHERE ROWID = x_rowid;
947 IF (SQL%NOTFOUND) THEN
948 RAISE NO_DATA_FOUND;
949 END IF;
950
951 -- Initialize API return status to success.
952 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
953 -- Standard call to get message count and if count is 1, get message
954 -- info.
955 FND_MSG_PUB.Count_And_Get(
956 p_encoded => FND_API.G_FALSE,
957 p_count => x_MSG_COUNT,
958 p_data => X_MSG_DATA);
959
960 EXCEPTION
961 WHEN FND_API.G_EXC_ERROR THEN
962 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
963 FND_MSG_PUB.Count_And_Get(
964 p_encoded => FND_API.G_FALSE,
965 p_count => x_MSG_COUNT,
966 p_data => X_MSG_DATA);
967 RETURN;
968 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
969 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
970 FND_MSG_PUB.Count_And_Get(
971 p_encoded => FND_API.G_FALSE,
972 p_count => x_MSG_COUNT,
973 p_data => X_MSG_DATA);
974 RETURN;
975 WHEN OTHERS THEN
976 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
977 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
978 FND_MESSAGE.SET_TOKEN('NAME','Update_Row : '||SQLERRM);
979 FND_MSG_PUB.ADD;
980 FND_MSG_PUB.Count_And_Get(
981 p_encoded => FND_API.G_FALSE,
982 p_count => x_MSG_COUNT,
983 p_data => X_MSG_DATA);
984 RETURN;
985
986 END update_row;
987
988 PROCEDURE add_row (
989 x_msg_count OUT NOCOPY NUMBER,
990 x_msg_data OUT NOCOPY VARCHAR2,
991 x_return_status OUT NOCOPY VARCHAR2,
992 x_rowid IN OUT NOCOPY VARCHAR2,
993 x_order_number IN OUT NOCOPY NUMBER,
994 x_order_status IN VARCHAR2,
995 x_date_completed IN DATE,
996 x_person_id IN NUMBER,
997 x_addr_line_1 IN VARCHAR2,
998 x_addr_line_2 IN VARCHAR2,
999 x_addr_line_3 IN VARCHAR2,
1000 x_addr_line_4 IN VARCHAR2,
1001 x_city IN VARCHAR2,
1002 x_state IN VARCHAR2,
1003 x_province IN VARCHAR2,
1004 x_county IN VARCHAR2,
1005 x_country IN VARCHAR2,
1006 x_postal_code IN VARCHAR2,
1007 x_email_address IN VARCHAR2,
1008 x_phone_country_code IN VARCHAR2,
1009 x_phone_area_code IN VARCHAR2,
1010 x_phone_number IN VARCHAR2,
1011 x_phone_extension IN VARCHAR2,
1012 x_fax_country_code IN VARCHAR2,
1013 x_fax_area_code IN VARCHAR2,
1014 x_fax_number IN VARCHAR2,
1015 x_delivery_fee IN NUMBER,
1016 x_order_fee IN NUMBER,
1017 x_request_type IN VARCHAR2,
1018 x_submit_method IN VARCHAR2,
1019 x_invoice_id IN NUMBER,
1020 x_mode IN VARCHAR2 ,
1021 x_order_description IN VARCHAR2 ,
1022 x_order_placed_by IN NUMBER
1023 ) AS
1024 /*
1025 || Created By : [email protected]
1026 || Created On : 25-JAN-2002
1027 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1028 || Known limitations, enhancements or remarks :
1029 || Change History :
1030 || Who When What
1031 || (reverse chronological order - newest change first)
1032 */
1033 CURSOR c1 IS
1034 SELECT ROWID
1035 FROM igs_as_order_hdr
1036 WHERE order_number = x_order_number;
1037 BEGIN
1038
1039 -- Initailize the error count, error data etc.
1040 FND_MSG_PUB.initialize;
1041
1042 OPEN c1;
1043 FETCH c1 INTO x_rowid;
1044 IF (c1%NOTFOUND) THEN
1045 CLOSE c1;
1046 insert_row (
1047 x_msg_count,
1048 x_msg_data,
1049 x_return_status,
1050 x_rowid,
1051 x_order_number,
1052 x_order_status,
1053 x_date_completed,
1054 x_person_id,
1055 x_addr_line_1,
1056 x_addr_line_2,
1057 x_addr_line_3,
1058 x_addr_line_4,
1059 x_city,
1060 x_state,
1061 x_province,
1062 x_county,
1063 x_country,
1064 x_postal_code,
1065 x_email_address,
1066 x_phone_country_code,
1067 x_phone_area_code,
1068 x_phone_number,
1069 x_phone_extension,
1070 x_fax_country_code,
1071 x_fax_area_code,
1072 x_fax_number,
1073 x_delivery_fee,
1074 x_order_fee,
1075 x_request_type,
1076 x_submit_method,
1077 x_invoice_id,
1078 x_mode ,
1079 x_order_description ,
1080 x_order_placed_by
1081 );
1082 RETURN;
1083 END IF;
1084 CLOSE c1;
1085 update_row (
1086 x_msg_count,
1087 x_msg_data,
1088 x_return_status,
1089 x_rowid,
1090 x_order_number,
1091 x_order_status,
1092 x_date_completed,
1093 x_person_id,
1094 x_addr_line_1,
1095 x_addr_line_2,
1096 x_addr_line_3,
1097 x_addr_line_4,
1098 x_city,
1099 x_state,
1100 x_province,
1101 x_county,
1102 x_country,
1103 x_postal_code,
1104 x_email_address,
1105 x_phone_country_code,
1106 x_phone_area_code,
1107 x_phone_number,
1108 x_phone_extension,
1109 x_fax_country_code,
1110 x_fax_area_code,
1111 x_fax_number,
1112 x_delivery_fee,
1113 x_order_fee,
1114 x_request_type,
1115 x_submit_method,
1116 x_invoice_id,
1117 x_mode ,
1118 x_order_description ,
1119 x_order_placed_by
1120
1121 );
1122
1123 -- Initialize API return status to success.
1124 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1125 -- Standard call to get message count and if count is 1, get message
1126 -- info.
1127 FND_MSG_PUB.Count_And_Get(
1128 p_encoded => FND_API.G_FALSE,
1129 p_count => x_MSG_COUNT,
1130 p_data => X_MSG_DATA);
1131
1132 EXCEPTION
1133 WHEN FND_API.G_EXC_ERROR THEN
1134 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1135 FND_MSG_PUB.Count_And_Get(
1136 p_encoded => FND_API.G_FALSE,
1137 p_count => x_MSG_COUNT,
1138 p_data => X_MSG_DATA);
1139 RETURN;
1140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1141 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1142 FND_MSG_PUB.Count_And_Get(
1143 p_encoded => FND_API.G_FALSE,
1144 p_count => x_MSG_COUNT,
1145 p_data => X_MSG_DATA);
1146 RETURN;
1147 WHEN OTHERS THEN
1148 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1149 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1150 FND_MESSAGE.SET_TOKEN('NAME','Add_Row : '||SQLERRM);
1151 FND_MSG_PUB.ADD;
1152 FND_MSG_PUB.Count_And_Get(
1153 p_encoded => FND_API.G_FALSE,
1154 p_count => x_MSG_COUNT,
1155 p_data => X_MSG_DATA);
1156 RETURN;
1157 END add_row;
1158
1159
1160 PROCEDURE delete_row (
1161 x_msg_count OUT NOCOPY NUMBER,
1162 x_msg_data OUT NOCOPY VARCHAR2,
1163 x_return_status OUT NOCOPY VARCHAR2,
1164 x_rowid IN VARCHAR2
1165 ) AS
1166 /*
1167 || Created By : [email protected]
1168 || Created On : 25-JAN-2002
1169 || Purpose : Handles the DELETE DML logic for the table.
1170 || Known limitations, enhancements or remarks :
1171 || Change History :
1172 || Who When What
1173 || shimitta 5/25/2006 Bug 5076185. OD <=> SF integration
1174 || (reverse chronological order - newest change first)
1175 */
1176 l_waiver_amount NUMBER;
1177 l_n_invoice_id NUMBER;
1178 p_return_status VARCHAR2 (1);
1179 CURSOR c_inv
1180 IS
1181 SELECT inv.*
1182 FROM igs_fi_inv_int_v inv
1183 WHERE inv.invoice_id = old_references.invoice_id;
1184 rec_c_inv c_inv%ROWTYPE;
1185 BEGIN
1186
1187 -- Initailize the error count, error data etc.
1188 FND_MSG_PUB.initialize;
1189
1190 before_dml (
1191 p_action => 'DELETE',
1192 x_rowid => x_rowid
1193 );
1194 IF old_references.invoice_id IS NOT NULL THEN
1195 OPEN c_inv;
1196 FETCH c_inv INTO rec_c_inv;
1197 CLOSE c_inv;
1198 IGS_FI_SS_CHARGES_API_PVT.create_charge(
1199 p_api_version => 2.0,
1200 p_init_msg_list => 'T',
1201 p_commit => 'F',
1202 p_person_id => rec_c_inv.person_id,
1203 p_fee_type => rec_c_inv.fee_type,
1204 p_fee_cat => rec_c_inv.fee_cat,
1205 p_fee_cal_type => rec_c_inv.fee_cal_type,
1206 p_fee_ci_sequence_number => rec_c_inv.fee_ci_sequence_number,
1207 p_course_cd => NULL,
1208 p_attendance_type => NULL,
1209 p_attendance_mode => NULL,
1210 p_invoice_amount => - (rec_c_inv.invoice_amount),
1211 p_invoice_creation_date => rec_c_inv.invoice_creation_date,
1212 p_invoice_desc => rec_c_inv.invoice_desc,
1213 p_transaction_type => rec_c_inv.transaction_type,
1214 p_currency_cd => rec_c_inv.currency_cd,
1215 p_exchange_rate => rec_c_inv.exchange_rate,
1216 p_effective_date => rec_c_inv.effective_date,
1217 p_waiver_flag => 'Y',
1218 p_waiver_reason => 'ORDER_DOCUMENT',
1219 p_source_transaction_id => rec_c_inv.invoice_id,
1220 p_invoice_id => l_n_invoice_id,
1221 x_return_status => p_return_status,
1222 x_msg_count => x_msg_count,
1223 x_msg_data => x_msg_data,
1224 x_waiver_amount => l_waiver_amount
1225 ) ;
1226 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
1227 RAISE FND_API.G_EXC_ERROR;
1228 END IF;
1229 END IF;
1230 DELETE FROM igs_as_order_hdr
1231 WHERE ROWID = x_rowid;
1232 IF (SQL%NOTFOUND) THEN
1233 RAISE NO_DATA_FOUND;
1234 END IF;
1235
1236 -- Initialize API return status to success.
1237 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1238 -- Standard call to get message count and if count is 1, get message
1239 -- info.
1240 FND_MSG_PUB.Count_And_Get(
1241 p_encoded => FND_API.G_FALSE,
1242 p_count => x_MSG_COUNT,
1243 p_data => X_MSG_DATA);
1244
1245 EXCEPTION
1246 WHEN FND_API.G_EXC_ERROR THEN
1247 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1248 FND_MSG_PUB.Count_And_Get(
1249 p_encoded => FND_API.G_FALSE,
1250 p_count => x_MSG_COUNT,
1251 p_data => X_MSG_DATA);
1252 RETURN;
1253 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1254 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1255 FND_MSG_PUB.Count_And_Get(
1256 p_encoded => FND_API.G_FALSE,
1257 p_count => x_MSG_COUNT,
1258 p_data => X_MSG_DATA);
1259 RETURN;
1260 WHEN OTHERS THEN
1261 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1262 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1263 FND_MESSAGE.SET_TOKEN('NAME','Delete_Row : '||SQLERRM);
1264 FND_MSG_PUB.ADD;
1265 FND_MSG_PUB.Count_And_Get(
1266 p_encoded => FND_API.G_FALSE,
1267 p_count => x_MSG_COUNT,
1268 p_data => X_MSG_DATA);
1269 RETURN;
1270 END delete_row;
1271 END Igs_As_Order_Hdr_Pkg;