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