[Home] [Help]
PACKAGE BODY: APPS.AR_INVOICE_TABLE_HANDLER
Source
1 PACKAGE BODY AR_INVOICE_TABLE_HANDLER AS
2 /* $Header: ARXVINTB.pls 120.28.12010000.5 2009/02/13 13:18:38 pbapna ship $ */
3
4 pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
5
6 PROCEDURE generate_default_salescredit(
7 p_cust_trx_id IN NUMBER,
8 p_cust_trx_line_id IN NUMBER,
9 p_trx_lines_rec IN ar_trx_lines_gt%rowtype,
10 x_errmsg OUT NOCOPY VARCHAR2,
11 x_return_status OUT NOCOPY VARCHAR2 ) IS
12
13 l_status1 VARCHAR2(2000);
14 l_cnt NUMBER;
15 BEGIN
16 IF pg_debug = 'Y'
17 THEN
18 ar_invoice_utils.debug ('generate_default_salescredit (+)');
19 END IF;
20 x_return_status := FND_API.G_RET_STS_SUCCESS;
21 -- check whether user has passed any sales credit for this line
22 BEGIN
23 SELECT COUNT(*)
24 INTO l_cnt
25 FROM ar_trx_salescredits_gt
26 WHERE trx_line_id = (
27 SELECT trx_line_id
28 FROM ar_trx_lines_gt
29 WHERE customer_trx_line_id = p_cust_trx_line_id);
30
31 IF l_cnt = 0 -- means user has not passed saleccredit
32 THEN
33 arp_process_salescredit.create_line_salescredits(
34 p_cust_trx_id,
35 p_cust_trx_line_id,
36 null, --p_memo_line_type,
37 'N', -- p_delete_scredits_first_flag
38 'N', -- p_run_autoaccounting_flag
39 l_status1 );
40 IF l_status1 <> 'OK'
41 THEN
42 x_errmsg := l_status1;
43 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
44 return;
45 END IF;
46 END IF;
47 END;
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 x_errmsg := 'Error in AR_INVOICE_TABLE_HANDLER.generate_default_salescredit '||sqlerrm;
52 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
53 return;
54
55
56 END;
57 PROCEDURE INSERT_TRX_HEADER (
58 ar_trx_header_rec IN ar_trx_header_gt%rowtype,
59 p_batch_id IN NUMBER DEFAULT NULL,
60 x_errmsg OUT NOCOPY VARCHAR2,
61 x_return_status OUT NOCOPY VARCHAR2)
62 IS
63 l_ct_reference ra_customer_trx.ct_reference%type;
64 l_trx_number ra_customer_trx.trx_number%type;
65 l_org_id NUMBER;
66 l_org_str VARCHAR2(30);
67 l_trx_str VARCHAR2(2000);
68 l_copy_doc_number_flag varchar2(1):='N';
69 BEGIN
70 IF pg_debug = 'Y'
71 THEN
72 ar_invoice_utils.debug ('AR_INVOICE_TABLE_HANDLER.INSERT_TRX_HEADER(+)' );
73 END IF;
74
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76 /* 5921925 - removed trx_number sequence from here. Moved it to
77 ar_invoice_utils.populate_doc_sequence */
78
79 -- call table handler to insert into ra_customer_trx
80 IF pg_debug = 'Y'
81 THEN
82 ar_invoice_utils.debug ('Before calling AR_TRX_BULK_PROCESS_HEADER.insert_row (+)' );
83 END IF;
84
85 AR_TRX_BULK_PROCESS_HEADER.insert_row(
86 p_trx_header_id => ar_trx_header_rec.trx_header_id,
87 x_errmsg => x_errmsg,
88 x_return_status => x_return_status);
89
90 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
91 THEN
92 return;
93 END IF;
94
95 ar_invoice_api_pub.g_api_outputs.batch_id := p_batch_id;
96
97 IF pg_debug = 'Y'
98 THEN
99 ar_invoice_utils.debug ('Before calling AR_TRX_BULK_PROCESS_HEADER.insert_row (-)' );
100 ar_invoice_utils.debug ('AR_INVOICE_TABLE_HANDLER.INSERT_TRX_HEADER(-)' );
101 END IF;
102 EXCEPTION
103 WHEN OTHERS THEN
104 x_errmsg := 'Error in AR_INVOICE_TABLE_HANDLER.INSERT_TRX_HEADER '||sqlerrm;
105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
106 return;
107 END;
108
109 PROCEDURE INSERT_TRX_LINES (
110 ar_trx_lines_rec IN ar_trx_lines_gt%rowtype,
111 p_cust_trx_id IN NUMBER,
112 p_batch_id NUMBER DEFAULT NULL,
113 x_errmsg OUT NOCOPY VARCHAR2,
114 x_return_status OUT NOCOPY VARCHAR2) IS
115
116 BEGIN
117 IF pg_debug = 'Y'
118 THEN
119 ar_invoice_utils.debug ('AR_INVOICE_TABLE_HANDLER.INSERT_TRX_LINES(+)' );
120 END IF;
121
122 x_return_status := FND_API.G_RET_STS_SUCCESS;
123
124 -- call table handler to insert into ra_customer_trx
125 AR_TRX_BULK_PROCESS_LINES.insert_row(
126 p_trx_header_id => ar_trx_lines_rec.trx_header_id,
127 p_trx_line_id => ar_trx_lines_rec.trx_line_id,
128 x_errmsg => x_errmsg,
129 x_return_status => x_return_status );
130 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
131 THEN
132 return;
133 END IF;
134 IF pg_debug = 'Y'
135 THEN
136 ar_invoice_utils.debug ('AR_INVOICE_TABLE_HANDLER.INSERT_TRX_LINES(-)' );
137 END IF;
138 EXCEPTION
139 WHEN OTHERS THEN
140 x_errmsg := 'Error in AR_INVOICE_TABLE_HANDLER.INSERT_TRX_LINES '||sqlerrm;
141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
142 return;
143 END;
144
145
146 PROCEDURE INSERT_TRX_DIST (
147 p_trx_dist_id IN NUMBER DEFAULT NULL,
148 p_batch_id IN NUMBER DEFAULT NULL,
149 x_errmsg OUT NOCOPY VARCHAR2,
150 x_return_status OUT NOCOPY VARCHAR2) IS
151
152 BEGIN
153 IF pg_debug = 'Y'
154 THEN
155 ar_invoice_utils.debug ('AR_INVOICE_TABLE_HANDLER.INSERT_TRX_DIST(+)' );
156 END IF;
157 x_return_status := FND_API.G_RET_STS_SUCCESS;
158
159 AR_TRX_BULK_PROCESS_DIST.INSERT_ROW (
160 p_trx_dist_id => p_trx_dist_id,
161 x_errmsg => x_errmsg,
162 x_return_status => x_return_status );
163
164 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
165 THEN
166 return;
167 END IF;
168 IF pg_debug = 'Y'
169 THEN
170 ar_invoice_utils.debug ('AR_INVOICE_TABLE_HANDLER.INSERT_TRX_DIST(-)' );
171 END IF;
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 x_errmsg := 'Error in AR_INVOICE_TABLE_HANDLER.INSERT_TRX_DIST '||sqlerrm;
176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
177 return;
178
179 END INSERT_TRX_DIST;
180
181 PROCEDURE INSERT_TRX_SALESCR (
182 p_trx_salescredit_id IN NUMBER DEFAULT NULL,
183 p_batch_id IN NUMBER DEFAULT NULL,
184 x_errmsg OUT NOCOPY VARCHAR2,
185 x_return_status OUT NOCOPY VARCHAR2) IS
186
187 BEGIN
188 IF pg_debug = 'Y'
189 THEN
190 ar_invoice_utils.debug ('AR_INVOICE_TABLE_HANDLER.INSERT_SALESCR(+)' );
191 END IF;
192
193 x_return_status := FND_API.G_RET_STS_SUCCESS;
194
195 UPDATE ar_trx_salescredits_gt
196 SET request_id = -(p_batch_id);
197
198 AR_TRX_BULK_PROCESS_SALESCR.INSERT_ROW (
199 p_trx_salescredit_id => p_trx_salescredit_id,
200 x_errmsg => x_errmsg,
201 x_return_status => x_return_status );
202
203 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
204 THEN
205 return;
206 END IF;
207 IF pg_debug = 'Y'
208 THEN
209 ar_invoice_utils.debug ('AR_INVOICE_TABLE_HANDLER.INSERT_SALESCR(-)' );
210 END IF;
211
212 EXCEPTION
213 WHEN OTHERS THEN
214 x_errmsg := 'Error in AR_INVOICE_TABLE_HANDLER.INSERT_TRX_SALESCR '||sqlerrm;
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
216 return;
217 END INSERT_TRX_SALESCR;
218
219
220 PROCEDURE create_batch(
221 p_trx_system_parameters_rec IN AR_INVOICE_DEFAULT_PVT.trx_system_parameters_rec_type,
222 p_trx_profile_rec IN AR_INVOICE_DEFAULT_PVT.trx_profile_rec_type,
223 p_batch_source_rec IN AR_INVOICE_API_PUB.batch_source_rec_type,
224 p_batch_id OUT NOCOPY NUMBER,
225 x_errmsg OUT NOCOPY VARCHAR2,
226 x_return_status OUT NOCOPY VARCHAR2 ) IS
227
228 l_cnt NUMBER;
229 BEGIN
230 IF pg_debug = 'Y'
231 THEN
232 ar_invoice_utils.debug ('AR_INVOICE_TABLE_HANDLER.create_batch(+)' );
233 END IF;
234
235 x_return_status := FND_API.G_RET_STS_SUCCESS;
236
237 INSERT INTO RA_BATCHES
238 (
239 CREATED_BY,
240 CREATION_DATE,
241 LAST_UPDATED_BY,
242 LAST_UPDATE_DATE,
243 LAST_UPDATE_LOGIN,
244 PROGRAM_APPLICATION_ID,
245 PROGRAM_ID,
246 PROGRAM_UPDATE_DATE,
247 BATCH_ID,
248 request_id,
249 NAME,
250 BATCH_DATE,
251 GL_DATE,
252 TYPE,
253 BATCH_SOURCE_ID,
254 SET_OF_BOOKS_ID
255 ,org_id
256 )
257 values (
258 fnd_global.user_id,
259 sysdate,
260 fnd_global.user_id,
261 sysdate,
262 fnd_global.login_id,
263 fnd_global.prog_appl_id,
264 null,
265 sysdate,
266 RA_BATCHES_S.NEXTVAL,
267 -(RA_BATCHES_S.currval),
268 'AR_INVOICE_API'||'_'||RA_BATCHES_S.currval,
269 sysdate,
270 trunc(nvl(p_batch_source_rec.default_date,trunc(sysdate))),
271 'INV',
272 nvl(p_batch_source_rec.batch_source_id, p_trx_profile_rec.ar_ra_batch_source),
273 p_trx_system_parameters_rec.set_of_books_id
274 ,arp_standard.sysparm.org_id)
275 returning batch_id INTO p_batch_id;
276
277 g_batch_id := p_batch_id;
278 g_request_id := -1 * p_batch_id;
279
280 IF pg_debug = 'Y'
281 THEN
282 ar_invoice_utils.debug ('Batch Id :'|| p_batch_id );
283 ar_invoice_utils.debug ('AR_INVOICE_TABLE_HANDLER.create_batch(-)' );
284 END IF;
285 EXCEPTION
286 WHEN OTHERS THEN
287 x_errmsg := 'Error in AR_INVOICE_TABLE_HANDLER.CREATE_BATCH '||sqlerrm;
288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
289 return;
290
291 END;
292
293 /* 4188835 - This routine is obsolete. For tax purposes, the invoice API
294 is now aligned with autoinvoice (bulk calls and batch processes) rather
295 than the trx workbench (individual calls) for performance reasons. */
296
297 PROCEDURE GET_DEFAULT_TAX_CODE (
298 ar_trx_header_rec IN ar_trx_header_gt%rowtype,
299 ar_trx_lines_rec IN ar_trx_lines_gt%rowtype,
300 p_vat_tax_id OUT NOCOPY NUMBER,
301 p_amt_incl_tax_flag OUT NOCOPY VARCHAR2,
302 x_errmsg OUT NOCOPY VARCHAR2,
303 x_return_status OUT NOCOPY VARCHAR2 ) AS
304
305 l_tax_code ar_vat_tax.tax_code%type;
306 l_amt_incl_tax_override ar_vat_tax.amount_includes_tax_override%type;
307 BEGIN
308 IF pg_debug = 'Y'
309 THEN
310 ar_invoice_utils.debug ('AR_INVOICE_TABLE_HANDLER.GET_DEFAULT_TAX_CODE(+)' );
311 END IF;
312
313 x_return_status := FND_API.G_RET_STS_SUCCESS;
314 IF pg_debug = 'Y'
315 THEN
316 ar_invoice_utils.debug ('Ship to site use Id :' || ar_trx_header_rec.ship_to_site_use_id);
317 ar_invoice_utils.debug ('Bill to site use Id :' || ar_trx_header_rec.bill_to_site_use_id);
318 ar_invoice_utils.debug ('Inventory Item Id :' || ar_trx_lines_rec.inventory_item_id);
319 ar_invoice_utils.debug ('Org Id :' || ar_trx_header_rec.org_id);
320 ar_invoice_utils.debug ('SOB Id :' || ar_trx_header_rec.set_of_books_id);
321 ar_invoice_utils.debug ('Ware House Id :' || ar_trx_lines_rec.warehouse_id);
322 ar_invoice_utils.debug ('trx date :' || ar_trx_header_rec.trx_date);
323 ar_invoice_utils.debug ('trx type id :' || ar_trx_header_rec.cust_trx_type_id);
324 ar_invoice_utils.debug ('memo line id :' || ar_trx_lines_rec.memo_line_id);
325 END IF;
326
327 /* 4188835 - defaulting code removed */
328
329 IF pg_debug = 'Y'
330 THEN
331 ar_invoice_utils.debug ('AR_INVOICE_TABLE_HANDLER.GET_DEFAULT_TAX_CODE(-)' );
332 END IF;
333 EXCEPTION
334 WHEN OTHERS THEN
335 x_errmsg := 'Error during default Tax Code';
336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
337 return;
338 END GET_DEFAULT_TAX_CODE;
339
340 PROCEDURE populate_error_stack (
341 p_trx_header_id NUMBER,
342 p_trx_line_id NUMBER default NULL,
343 p_trx_dist_id NUMBER default NULL,
344 p_trx_salescredit_id NUMBER default NULL,
345 p_error_message VARCHAR2 default NULL,
346 p_invalid_value VARCHAR2 default NULL) AS
347 BEGIN
348
349 INSERT INTO ar_trx_errors_gt (
350 trx_header_id,
351 trx_line_id,
352 trx_dist_id,
353 trx_salescredit_id,
354 error_message,
355 invalid_value)
356 VALUES
357 ( p_trx_header_id,
358 p_trx_line_id,
359 p_trx_dist_id,
360 p_trx_salescredit_id,
361 p_error_message,
362 p_invalid_value);
363
364 END populate_error_stack;
365
366 PROCEDURE cleanup (
367 p_customer_trx_id IN NUMBER ) AS
368
369 l_ret_stat NUMBER;
370 BEGIN
371
372 arp_etax_invapi_util.cleanup_tax(p_customer_trx_id);
373
374 delete from ra_customer_trx where customer_trx_id = p_customer_trx_id;
375 delete from ra_customer_trx_lines where customer_trx_id = p_customer_trx_id;
376 delete from ra_cust_trx_line_gl_dist where customer_trx_id = p_customer_trx_id;
377 delete from ar_payment_schedules where customer_trx_id = p_customer_trx_id;
378 delete from RA_CUST_TRX_LINE_SALESREPS where customer_trx_id = p_customer_trx_id;
379
380 END;
381
382 PROCEDURE cleanup_all AS
383 l_ret_stat NUMBER;
384
385 CURSOR c_failed_trx IS
386 SELECT ct.customer_trx_id
387 FROM ra_customer_trx ct
388 WHERE
389 EXISTS (SELECT 'error'
390 FROM ar_trx_errors_gt err,
391 ar_trx_header_gt head
392 WHERE err.trx_header_id = head.trx_header_id
393 AND head.customer_trx_id = ct.customer_trx_id);
394
395 BEGIN
396 /* Get the tax audit tables */
397 FOR c_bad IN c_failed_trx LOOP
398 arp_etax_invapi_util.cleanup_tax(c_bad.customer_trx_id);
399 END LOOP;
400
401 /* Delete everything else */
402 delete from ra_customer_trx
403 where customer_trx_id in (
404 select distinct th.customer_trx_id
405 from ar_trx_errors_gt err,
406 ar_trx_header_gt th
407 where err.trx_header_id = th.trx_header_id);
408
409 delete from ra_customer_trx_lines
410 where customer_trx_id in (
411 select distinct th.customer_trx_id
412 from ar_trx_errors_gt err,
413 ar_trx_header_gt th
414 where err.trx_header_id = th.trx_header_id);
415
416 delete from ra_cust_trx_line_gl_dist
417 where customer_trx_id in (
418 select distinct th.customer_trx_id
419 from ar_trx_errors_gt err,
420 ar_trx_header_gt th
421 where err.trx_header_id = th.trx_header_id);
422
423 delete from ar_payment_schedules
424 where customer_trx_id in (
425 select distinct th.customer_trx_id
426 from ar_trx_errors_gt err,
427 ar_trx_header_gt th
428 where err.trx_header_id = th.trx_header_id);
429
430 delete from RA_CUST_TRX_LINE_SALESREPS
431 where customer_trx_id in (
432 select distinct th.customer_trx_id
433 from ar_trx_errors_gt err,
434 ar_trx_header_gt th
435 where err.trx_header_id = th.trx_header_id);
436
437 END;
438
439
440 PROCEDURE INSERT_ROW(
441 p_trx_system_parameters_rec IN AR_INVOICE_DEFAULT_PVT.trx_system_parameters_rec_type,
442 p_trx_profile_rec IN AR_INVOICE_DEFAULT_PVT.trx_profile_rec_type,
443 p_batch_source_rec IN AR_INVOICE_API_PUB.batch_source_rec_type,
444 x_errmsg OUT NOCOPY VARCHAR2,
445 x_return_status OUT NOCOPY VARCHAR2) AS
446
447 l_cust_trx_id NUMBER;
448 l_cnt NUMBER;
449 l_ccid number;
450 l_concat_segments varchar2(2000);
451 l_status2 varchar2(2000);
452 l_num_failed_dist_rows number;
453 l_revenue_amount number;
454 l_trx_header_id number;
455 l_customer_trx_line_id number;
456 l_new_tax_amount number;
457 l_link_to_cust_trx_line_id number;
458 l_gross_extended_amount number;
459 l_gross_unit_selling_price number;
460 l_recalculate_tax_flag boolean;
461 l_status1 varchar2(100);
462 l_status varchar2(100);
463 l_tax_line_rec ra_customer_trx_lines%rowtype;
464 l_freight_line_rec ra_customer_trx_lines%rowtype;
465 l_ct_reference ra_customer_trx.ct_reference%type;
466 l_requery_tax_if_visible boolean;
467 l_batch_id NUMBER;
468 lc_request_id VARCHAR2(40);
469 l_rec_dist_exist VARCHAR2(1) := 'N';
470 l_tax_error_flag VARCHAR2(1) := 'N';
471 l_result VARCHAR2(1);
472 l_ok_to_call_tax VARCHAR2(1);
473 l_vat_tax_id ar_vat_tax.vat_tax_id%type;
474 l_amt_incl_tax_flag ar_vat_tax.amount_includes_tax_flag%type;
475 l_num_failed_rows NUMBER;
476 l_etax_error_count NUMBER := 0;
477
478 /* 5921925 */
479 l_scredit_count NUMBER;
480 l_dist_count NUMBER;
481 l_error_message FND_NEW_MESSAGES.message_text%type;
482 l_error_count NUMBER;
483 l_commitment_amt NUMBER;
484 l_prev_cust_old_state AR_BUS_EVENT_COVER.prev_cust_old_state_tab;
485 /* end 5921925 */
486 /* BR Sped Project */
487 l_jgzz_product_code VARCHAR2(100);
488 lcursor NUMBER;
489 lignore NUMBER;
490 sqlstmt VARCHAR2(254);
491 l_return_value_gdf NUMBER;
492 /* BR Sped Project */
493
494 /*7829636*/
495 l_request_id NUMBER;
496
497 l_return_status NUMBER;
498
499 AR_TAX_EXCEPTION EXCEPTION;
500 TAX_NO_RATE EXCEPTION;
501 TAX_NO_CODE EXCEPTION;
502 TAX_NO_AMOUNT EXCEPTION;
503 TAX_NO_PRECISION EXCEPTION;
504 TAX_NO_DATA EXCEPTION;
505 TAX_NEED_POSTAL EXCEPTION;
506 TAX_CODE_INACTIVE EXCEPTION;
507 TAX_BAD_DATA EXCEPTION;
508 TAX_OERR EXCEPTION;
509
510 CURSOR ar_trx_header_c IS
511 SELECT * FROM ar_trx_header_gt gt
512 WHERE NOT EXISTS ( SELECT 'X' FROM
513 ar_trx_errors_gt err
514 WHERE err.trx_header_id = gt.trx_header_id);
515
516 CURSOR ar_trx_lines_c IS
517 SELECT * FROM ar_trx_lines_gt gt
518 WHERE trx_header_id = l_trx_header_id
519 order by trx_header_id, trx_line_id, line_number;
520
521 CURSOR ar_trx_dist_c IS
522 SELECT * FROM ar_trx_dist_gt
523 where trx_header_id = l_trx_header_id
524 AND account_class = 'REC'
525 AND process_flag = 'N';
526
527 BEGIN
528 IF pg_debug = 'Y'
529 THEN
530 ar_invoice_utils.debug ('AR_INVOICE_TABLE_HANDLER.insert_row(+)' );
531 END IF;
532
533 x_return_status := FND_API.G_RET_STS_SUCCESS;
534
535 l_batch_id := g_batch_id;
536
537 IF pg_debug = 'Y'
538 THEN
539 ar_invoice_utils.debug ('Batch Id '|| l_batch_id );
540 END IF;
541 -- This is for mrc call. which takes varchar2.
542 lc_request_id := g_request_id;
543 -- Also assign the global variable in auto_accounting package so that it does
544 -- not call mrc engine from auto_accounting.
545 ARP_AUTO_ACCOUNTING.g_called_from := 'AR_INVOICE_API';
546
547 FOR ar_trx_header_rec IN ar_trx_header_c
548 LOOP
549 l_tax_error_flag := 'N';
550 l_trx_header_id := ar_trx_header_rec.trx_header_id;
551 l_cust_trx_id := ar_trx_header_rec.customer_trx_id;
552
553 -- populate g_customer_trx_id. This is out parameter
554 -- in case the API is called for a singel invoice. Otherwise
555 -- the latest value will be stored in this global value.
556 AR_INVOICE_API_PUB.g_customer_trx_id := ar_trx_header_rec.customer_trx_id;
557
558 IF pg_debug = 'Y'
559 THEN
560 ar_invoice_utils.debug ('In Header Loop' );
561 ar_invoice_utils.debug ('Trx Header Id ' || l_trx_header_id );
562 ar_invoice_utils.debug ('Cust Trx Id ' || l_cust_trx_id );
563 ar_invoice_utils.debug ('calling insert_trx_header (+)' );
564 END IF;
565
566 insert_trx_header ( ar_trx_header_rec => ar_trx_header_rec,
567 p_batch_id => l_batch_id,
568 x_errmsg => x_errmsg,
569 x_return_status => x_return_status);
570
571 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
572 THEN
573 return;
574 END IF;
575
576 IF pg_debug = 'Y'
577 THEN
578 ar_invoice_utils.debug ('calling insert_trx_header (-)' );
579 ar_invoice_utils.debug ('calling auto accounting (+)' );
580 END IF;
581
582 /* 5921925 - Do not call insert unless there were dists rows */
583 IF ar_invoice_api_pub.g_dist_exist
584 THEN
585 BEGIN
586 FOR ar_trx_dist_rec IN ar_trx_dist_c
587 LOOP
588 INSERT_TRX_DIST(
589 p_trx_dist_id => ar_trx_dist_rec.trx_dist_id,
590 p_batch_id => l_batch_id,
591 x_errmsg => x_errmsg,
592 x_return_status => x_return_status);
593
594 END LOOP;
595 END;
596 END IF;
597
598 -- Now insert lines
599 FOR ar_trx_lines_rec IN ar_trx_lines_c
600 LOOP
601 IF pg_debug = 'Y'
602 THEN
603 ar_invoice_utils.debug ('In Lines Loop (+)' );
604 END IF;
605
606 IF ar_trx_lines_rec.line_type in ('LINE','FREIGHT')
607 THEN
608 l_customer_trx_line_id := ar_trx_lines_rec.customer_trx_line_id;
609
610 IF pg_debug = 'Y'
611 THEN
612 ar_invoice_utils.debug ('Line Type = LINE ' );
613 ar_invoice_utils.debug ('Cust. Trx Line Id '|| l_customer_trx_line_id );
614 ar_invoice_utils.debug ('Calling insert_trx_lines (+)' );
615 END IF;
616
617 /* 4188835 - removed tax code defaulting logic */
618
619 insert_trx_lines(ar_trx_lines_rec => ar_trx_lines_rec,
620 p_cust_trx_id => l_cust_trx_id,
621 p_batch_id => l_batch_id,
622 x_errmsg => x_errmsg,
623 x_return_status => x_return_status);
624
625 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
626 THEN
627 return;
628 END IF;
629 IF pg_debug = 'Y'
630 THEN
631 ar_invoice_utils.debug ('Calling insert_trx_lines (-)' );
632 END IF;
633
634 -- derive the GL and trx date if trx_class is not CM
635 IF ( ( ar_trx_header_rec.trx_class <> 'CM' )
636 AND
637 ( ar_trx_lines_rec.accounting_rule_id IS NOT NULL )
638 )
639 THEN
640
641 /*Bug 5884520 reevaluate gl date if gl date is not provided by user*/
642
643 arp_dates.derive_gl_trx_dates_from_rules(
644 l_cust_trx_id,
645 ar_trx_header_rec.gl_date,
646 ar_trx_header_rec.trx_date,
647 l_recalculate_tax_flag);
648 END IF;
649 IF ar_trx_lines_rec.line_type = 'LINE'
650 THEN
651 -- Create Sales Credit
652 IF pg_debug = 'Y'
653 THEN
654 ar_invoice_utils.debug ('Calling sales credit (+)' );
655 END IF;
656 -- before defaulting check whether user has passed
657 -- sales credit or not. If user has passed sales credit
658 -- then don't default the sales credit.
659
660 IF NOT ar_invoice_api_pub.g_sc_exist
661 THEN
662 generate_default_salescredit(
663 p_cust_trx_id => l_cust_trx_id,
664 p_cust_trx_line_id => l_customer_trx_line_id,
665 p_trx_lines_rec => ar_trx_lines_rec,
666 x_errmsg => x_errmsg,
667 x_return_status => x_return_status);
668 END IF;
669
670 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
671 THEN
672 return;
673 END IF;
674 IF pg_debug = 'Y'
675 THEN
676 ar_invoice_utils.debug ('Calling sales credit (-)' );
677 END IF;
678 END IF;
679
680 /* 4188835 - removed line-level call to
681 arp_process_tax.after_insert_line */
682
683 IF ar_trx_lines_rec.line_type = 'FREIGHT' THEN
684 BEGIN
685
686 /* Changes for Bug 5398561 starts. */
687 DECLARE
688 lv_ship_via ar_trx_header_gt.ship_via%type;
689 lv_ship_dt_actual ar_trx_header_gt.ship_date_actual%type;
690 BEGIN
691
692 IF ar_trx_lines_rec.ship_date_actual is null
693 THEN
694 select ship_via, ship_date_actual
695 into lv_ship_via, lv_ship_dt_actual
696 from ar_trx_header_gt
697 where customer_trx_id = l_cust_trx_id;
698
699 ar_trx_lines_rec.ship_via := lv_ship_via;
700 ar_trx_lines_rec.ship_date_actual := lv_ship_dt_actual;
701 ELSE
702 select ship_via
703 into lv_ship_via
704 from ar_trx_header_gt
705 where customer_trx_id = l_cust_trx_id;
706
707 ar_trx_lines_rec.ship_via := lv_ship_via;
708 END IF;
709
710 EXCEPTION
711 WHEN OTHERS THEN
712 IF pg_debug = 'Y'
713 THEN
714 ar_invoice_utils.debug ('Error fetching ship_vi, ship_actual_date : ' ||sqlerrm );
715 END IF;
716 END;
717 /* Changes for Bug 5398561 ends. */
718
719
720 arp_process_header.update_header_freight_cover(
721 'AR_INVOICE_API',
722 1,
723 l_cust_trx_id,
724 'INV',
725 null,
726 ar_trx_lines_rec.ship_via,
727 ar_trx_lines_rec.ship_date_actual,
728 ar_trx_lines_rec.waybill_number,
729 ar_trx_lines_rec.fob_point,
730 l_status);
731
732 EXCEPTION
733 WHEN OTHERS THEN
734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
735 x_errmsg :=
736 'arp_process_header.update_header_freight_cover '
737 || 'raised unexpected error!';
738 RETURN;
739 END;
740
741 END IF;
742 IF pg_debug = 'Y'
743 THEN
744 ar_invoice_utils.debug ('CC id '|| l_ccid);
745 ar_invoice_utils.debug ('Con cat segments '|| l_concat_segments);
746 ar_invoice_utils.debug ('No. of rows failed '|| l_num_failed_dist_rows);
747 ar_invoice_utils.debug ('Calling auto accounting for line (-)' );
748 END IF;
749
750 ELSIF ar_trx_lines_rec.line_type = 'TAX'
751 THEN
752 IF pg_debug = 'Y'
753 THEN
754 ar_invoice_utils.debug ('Line Type = TAX (+)' );
755 END IF;
756
757 /* 4188835 - Removed entire manual tax block. This behavior
758 is now handled in arp_etax_invapi_util.calculate_tax */
759
760 END IF; -- end of if line_type = 'LINE'
761 IF pg_debug = 'Y'
762 THEN
763 ar_invoice_utils.debug ('Line Type = LINE (-)' );
764 END IF;
765 END LOOP;
766
767 IF l_tax_error_flag = 'Y'
768 THEN
769 GOTO main_loop;
770 END IF;
771
772 -- call distributions
773 IF ar_invoice_api_pub.g_dist_exist
774 THEN
775 INSERT_TRX_DIST( p_trx_dist_id => null,
776 p_batch_id => l_batch_id,
777 x_errmsg => x_errmsg,
778 x_return_status => x_return_status );
779 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
780 THEN
781 cleanup(p_customer_trx_id => l_cust_trx_id);
782 return;
783 END IF;
784 END IF;
785
786 IF ar_invoice_api_pub.g_sc_exist
787 THEN
788 INSERT_TRX_SALESCR ( p_trx_salescredit_id => null,
789 p_batch_id => l_batch_id,
790 x_errmsg => x_errmsg,
791 x_return_status => x_return_status );
792
793 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
794 THEN
795 cleanup(p_customer_trx_id => l_cust_trx_id);
796 return;
797 END IF;
798 END IF;
799
800 <<main_loop>>
801 NULL;
802 END LOOP; /* End of Primary Loop */
803
804 /* call autoaccounting for REC first -- needed by etax call below */
805 IF pg_debug = 'Y'
806 THEN
807 ar_invoice_utils.debug('calling autoaccounting for REC');
808 END IF;
809
810 arp_auto_accounting.do_autoaccounting(
811 'I',
812 'REC',
813 null,
814 null,
815 null,
816 g_request_id , --req_id,
817 null, --_gl_date,
818 null,
819 null,
820 null,
821 null,
822 null,
823 null,
824 null,
825 null,
826 l_ccid,
827 l_concat_segments,
828 l_num_failed_dist_rows);
829
830 IF pg_debug = 'Y'
831 THEN
832 ar_invoice_utils.debug('calling etax for entire API batch');
833 END IF;
834
835 arp_etax_invapi_util.calculate_tax(g_request_id, l_etax_error_count,
836 l_return_status);
837
838 /* 6743811 - raise error if etax returns an error (E or U) status
839 the returned values are 0=success, 1=Error, 2=Unexpected Error */
840 IF l_return_status > 0
841 THEN
842 arp_util.debug('EXCEPTION: error calling eBusiness Tax, status = ' ||
843 l_return_status);
844 arp_util.debug('Please review the plsql debug log for additional details.');
845 APP_EXCEPTION.raise_exception;
846 END IF;
847
848 IF (nvl(l_etax_error_count, 0) > 0)
849 THEN
850 ar_invoice_utils.debug('EXCEPTION: etax error count = ' || l_etax_error_count);
851 END IF;
852
853
854 IF pg_debug = 'Y'
855 THEN
856 ar_invoice_utils.debug ('calling autoaccounting for ALL (except REC)' );
857 END IF;
858
859 arp_auto_accounting.do_autoaccounting(
860 'I',
861 'ALL',
862 null,
863 null,
864 null,
865 g_request_id , --req_id,
866 null, --_gl_date,
867 null,
868 null,
869 null,
870 null,
871 null,
872 null,
873 null,
874 null,
875 l_ccid,
876 l_concat_segments,
877 l_num_failed_dist_rows);
878
879 IF pg_debug = 'Y'
880 THEN
881 ar_invoice_utils.debug ('No. of Rows failed ' || l_num_failed_dist_rows);
882 ar_invoice_utils.debug ('returning from arp_auto_accounting.do_autoaccounting(-)' );
883 END IF;
884
885 IF l_num_failed_dist_rows > 0
886 OR l_etax_error_count > 0
887 THEN
888 /* Clean up any transactions that encountered problems
889 in autoaccounting (before calling MRC) */
890 cleanup_all;
891
892 END IF;
893
894 -- call mrc engine
895 IF pg_debug = 'Y'
896 THEN
897 ar_invoice_utils.debug ('Calling MRC Engine (+)' );
898 ar_invoice_utils.debug ('Request Id '|| to_char(g_request_id) );
899 END IF;
900 /*---------------------------------+
901 | Calling central MRC library |
902 | for MRC Integration |
903 +--------------------------------- */
904 ar_mrc_engine.mrc_bulk_process (
905 p_request_id => g_request_id,
906 p_table_name => 'RAXTRX');
907 IF pg_debug = 'Y'
908 THEN
909 ar_invoice_utils.debug ('Calling MRC Engine (-)' );
910 ar_invoice_utils.debug ('callling post commit process with complete flag = Y(+)' );
911 END IF;
912
913 /* Second loop to do final completion tasks */
914 FOR ar_trx_header_rec IN ar_trx_header_c
915 LOOP
916
917 IF pg_debug = 'Y'
918 THEN
919 ar_invoice_utils.debug ('set Term in use (+)' );
920 ar_invoice_utils.debug ('Term Id '|| ar_trx_header_rec.term_id );
921 END IF;
922
923 arp_trx_util.set_term_in_use_flag(
924 p_form_name => 'AR_INVOICE_API',
925 p_form_version => 1,
926 p_term_id => ar_trx_header_rec.term_id,
927 p_term_in_use_flag => null);
928 IF pg_debug = 'Y'
929 THEN
930 ar_invoice_utils.debug ('set Term in use (-)' );
931 ar_invoice_utils.debug ('arp_global.request_id BEFORE CHANGES'||arp_global.request_id );
932 END IF;
933
934 /* 5921925 - Streamlined completion logic */
935 BEGIN
936 arp_rounding.correct_scredit_rounding_errs(ar_trx_header_rec.customer_trx_id,
937 l_scredit_count);
938 /*7829636*/
939 l_request_id := arp_global.request_id;
940
941 IF((arp_global.request_id IS NULL) AND (g_request_id IS NOT NULL)) THEN
942 arp_global.request_id := g_request_id;
943 END IF;
944
945 ar_invoice_utils.debug ('arp_global.request_id AFTER CHANGES'||arp_global.request_id );
946
947 IF (arp_rounding.correct_dist_rounding_errors
948 (null, -- request_id
949 ar_trx_header_rec.customer_trx_id,
950 null, -- customer_trx_line_id
951 l_dist_count,
952 l_error_message,
953 p_trx_system_parameters_rec.precision,
954 p_trx_system_parameters_rec.minimum_accountable_unit,
955 'ALL',
956 'N',
957 null, -- debug_mode
958 p_trx_system_parameters_rec.trx_header_level_rounding,
959 'N' -- activity flag
960 ) = 0) -- FALSE
961 THEN
962 arp_util.debug('EXCEPTION: ar_invoice_table_handler.insert_row()');
963 arp_util.debug(l_error_message);
964 fnd_message.set_name('AR', 'AR_PLCRE_FHLR_CCID');
965
966 APP_EXCEPTION.raise_exception;
967 END IF;
968
969 /*7829636-Resetting arp_global.request_id*/
970 arp_global.request_id := l_request_id;
971
972 arp_trx_complete_chk.do_completion_checking(
973 ar_trx_header_rec.customer_trx_id,
974 NULL,
975 NULL,
976 NULL,
977 l_error_count,
978 'B'
979 );
980
981 IF (l_error_count > 0)
982 THEN
983 app_exception.raise_exception;
984 END IF;
985
986 arp_balance_check.check_transaction_balance(
987 ar_trx_header_rec.customer_trx_id,'Y');
988
989 IF ar_trx_header_rec.accounting_affect_flag = 'Y'
990 THEN
991 arp_maintain_ps.maintain_payment_schedules(
992 'I',
993 ar_trx_header_rec.customer_trx_id,
994 NULL, -- ps_id
995 NULL, -- line_amount
996 NULL, -- tax_amount
997 NULL, -- frt_amount
998 NULL, -- charge_amount
999 l_commitment_amt, -- out parameter, junk
1000 NULL);
1001
1002 AR_BUS_EVENT_COVER.Raise_Trx_Creation_Event
1003 (ar_trx_header_rec.trx_class, -- trx_type.type
1004 ar_trx_header_rec.customer_trx_id,
1005 l_prev_cust_old_state); -- structure
1006
1007 END IF;
1008
1009 /* Call JL for copying GDF Attributes to JL Tables. */
1010 l_jgzz_product_code := AR_GDF_VALIDATION.is_jg_installed;
1011
1012 IF l_jgzz_product_code IS NOT NULL THEN
1013 /* JL_BR_SPED_PKG package is installed, so OK to call the package. */
1014
1015 BEGIN
1016
1017 lcursor := dbms_sql.open_cursor;
1018 sqlstmt :=
1019 'BEGIN :l_return_value_gdf := JL_BR_SPED_PKG.COPY_GDF_ATTRIBUTES_API(:p_customer_trx_id);
1020 END;';
1021
1022 dbms_sql.parse(lcursor, sqlstmt, dbms_sql.native);
1023 dbms_sql.bind_variable(lcursor, ':p_customer_trx_id', ar_trx_header_rec.customer_trx_id);
1024 dbms_sql.bind_variable(lcursor, ':l_return_value_gdf', l_return_value_gdf);
1025
1026 IF PG_DEBUG in ('Y', 'C') THEN
1027 arp_standard.debug('copy_gdf_attributes: Executing Statement: '||sqlstmt);
1028 END IF;
1029
1030 lignore := dbms_sql.execute(lcursor);
1031 dbms_sql.close_cursor(lcursor);
1032
1033 EXCEPTION
1034 WHEN OTHERS THEN
1035 IF PG_DEBUG in ('Y', 'C') THEN
1036 arp_standard.debug('copy_gdf_attributes: Exception calling BEGIN JL_BR_SPED_PKG.copy_gdf_attr_for_api.');
1037 arp_standard.debug('copy_gdf_attributes: ' || SQLERRM);
1038 END IF;
1039
1040 IF dbms_sql.is_open(lcursor)
1041 THEN
1042 dbms_sql.close_cursor(lcursor);
1043 END IF;
1044 END;
1045 END IF;
1046
1047
1048 EXCEPTION
1049 WHEN OTHERS
1050 THEN
1051 cleanup(p_customer_trx_id => l_cust_trx_id);
1052 x_errmsg := sqlerrm;
1053 x_return_status := fnd_api.g_ret_sts_unexp_error;
1054 INSERT INTO ar_trx_errors_gt (
1055 trx_header_id,
1056 error_message)
1057 VALUES
1058 ( ar_trx_header_rec.trx_header_id,
1059 x_errmsg);
1060 RETURN;
1061 END;
1062
1063 END LOOP; /* End of completion loop */
1064
1065 IF pg_debug = 'Y'
1066 THEN
1067 ar_invoice_utils.debug ('Insert_row(-)' );
1068 END IF;
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 cleanup(p_customer_trx_id => l_cust_trx_id);
1072 x_errmsg := 'Fatal Error' || sqlerrm;
1073 x_return_status := fnd_api.g_ret_sts_unexp_error;
1074 return;
1075
1076 END INSERT_ROW;
1077
1078 END AR_INVOICE_TABLE_HANDLER;