[Home] [Help]
PACKAGE BODY: APPS.AR_INVOICE_API_PUB
Source
1 PACKAGE BODY AR_INVOICE_API_PUB AS
2 /* $Header: ARXPINVB.pls 120.34 2011/07/08 00:43:49 dgaurab ship $ */
3
4 pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
5
6 g_one_time_init_org number;
7 g_single_invoice boolean := FALSE;
8
9 PROCEDURE populate_header (
10 p_trx_header_tbl IN trx_header_tbl_type,
11 p_trx_system_param_rec IN AR_INVOICE_DEFAULT_PVT.trx_system_parameters_rec_type,
12 p_trx_profile_rec IN AR_INVOICE_DEFAULT_PVT.trx_profile_rec_type,
13 p_batch_source_rec IN batch_source_rec_type,
14 x_errmsg OUT NOCOPY VARCHAR2,
15 x_return_status OUT NOCOPY VARCHAR2) IS
16
17 BEGIN
18
19 x_return_status := FND_API.G_RET_STS_SUCCESS;
20 IF pg_debug = 'Y' THEN
21 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.populate_header(+)' );
22 ar_invoice_utils.debug ('All Default Values ' );
23 ar_invoice_utils.debug ('Set of Books Id '||
24 p_trx_system_param_rec.set_of_books_id);
25 ar_invoice_utils.debug ('Trx Currency '||
26 p_trx_system_param_rec.base_currency_code);
27 ar_invoice_utils.debug ('Batch Source '||
28 p_trx_profile_rec.ar_ra_batch_source);
29 ar_invoice_utils.debug ('GL Date '|| p_batch_source_rec.default_date);
30 ar_invoice_utils.debug ('Exchange Rate Type '
31 || p_trx_profile_rec.default_exchange_rate_type);
32 END IF;
33
34 -- First populate the global header table with the user parameter.
35 ar_trx_global_process_header.insert_row(
36 p_trx_header_tbl => p_trx_header_tbl,
37 p_batch_source_rec => p_batch_source_rec,
38 x_errmsg => x_errmsg,
39 x_return_status => x_return_status);
40
41 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
42 RETURN;
43 END IF;
44
45 /* 5921925 - Removed defaulting UPDATE statement and integrated it
46 into ar_trx_global_process_header.insert_row */
47
48 IF pg_debug = 'Y' THEN
49 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.populate_header(-)' );
50 END IF;
51
52 EXCEPTION
53 WHEN OTHERS THEN
54 x_errmsg := 'Error in AR_INVOICE_API_PUB.populate_header '||sqlerrm;
55 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
56 RETURN;
57
58 END;
59
60 PROCEDURE populate_lines (
61 p_trx_lines_tbl trx_line_tbl_type,
62 p_trx_system_param_rec ar_invoice_default_pvt.trx_system_parameters_rec_type,
63 x_errmsg OUT NOCOPY VARCHAR2,
64 x_return_status OUT NOCOPY VARCHAR2) IS
65
66 l_customer_trx_line_id NUMBER;
67
68 CURSOR clink IS
69 SELECT customer_trx_line_id, link_to_trx_line_id, trx_line_id,
70 link_to_cust_trx_line_id
71 FROM ar_trx_lines_gt
72 WHERE link_to_trx_line_id IS NOT NULL
73 AND line_type in ( 'TAX', 'FREIGHT');
74
75 CURSOR line (p_link_line_id NUMBER) IS
76 SELECT customer_trx_line_id
77 FROM ar_trx_lines_gt
78 WHERE trx_line_id = p_link_line_id;
79
80 BEGIN
81
82 IF pg_debug = 'Y' THEN
83 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.populate_lines(+)' );
84 END IF;
85
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87
88 AR_TRX_GLOBAL_PROCESS_LINES.INSERT_ROW(
89 p_trx_lines_tbl => p_trx_lines_tbl,
90 x_errmsg => x_errmsg,
91 x_return_status => x_return_status);
92
93 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
94 THEN
95 return;
96 END IF;
97
98 -- check if there are any freight and tax lines and whether it is linked
99 -- to any of the lines or not.
100 -- need to do bulk enable
101
102 /* 5921925 - considered rewriting this, but I don't see an easy way
103 to do anything since both trx_id and trx_line_id are dynamically assigned
104 in the GT inserts. */
105
106 FOR clinkRec IN clink
107 LOOP
108 OPEN line(clinkRec.link_to_trx_line_id);
109 FETCH line INTO l_customer_trx_line_id;
110 CLOSE line;
111
112 IF pg_debug = 'Y' THEN
113 ar_invoice_utils.debug ('Cust Trx Line Id in Link Loop ' ||
114 l_customer_trx_line_id);
115 ar_invoice_utils.debug ('Link to Line Id in Link Loop ' ||
116 clinkRec.link_to_trx_line_id );
117 ar_invoice_utils.debug ('Trx Line Id in Link Loop ' ||
118 clinkRec.trx_line_id );
119 END IF;
120
121 UPDATE ar_trx_lines_gt gt
122 set link_to_cust_trx_line_id = l_customer_trx_line_id
123 WHERE customer_trx_line_id = clinkRec.customer_trx_line_id;
124
125 END LOOP;
126
127 UPDATE ar_trx_lines_gt lgt
128 SET (customer_trx_id, trx_date, org_id, set_of_books_id,currency_code) =
129 ( SELECT hgt.customer_trx_id, hgt.trx_date, hgt.org_id,
130 hgt.set_of_books_id, trx_currency
131 FROM ar_trx_header_gt hgt
132 WHERE lgt.trx_header_id = trx_header_id),
133 request_id = AR_INVOICE_TABLE_HANDLER.g_request_id;
134
135 IF pg_debug = 'Y'
136 THEN
137 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.populate_lines(-)' );
138 END IF;
139
140 EXCEPTION
141 WHEN OTHERS THEN
142 x_errmsg := 'Error in AR_INVOICE_API_PUB.populate_lines '||sqlerrm;
143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144 return;
145 END;
146
147
148 PROCEDURE populate_distributions (
149 p_trx_dist_tbl IN trx_dist_tbl_type,
150 p_batch_source_rec IN batch_source_rec_type,
151 p_trx_system_param_rec IN
152 ar_invoice_default_pvt.trx_system_parameters_rec_type,
153 x_errmsg OUT NOCOPY VARCHAR2,
154 x_return_status OUT NOCOPY VARCHAR2 ) IS
155
156 -- To work around an issue in 8i.
157 -- ORASHID
158 -- 16-OCT-2003
159
160 null_column NUMBER := NULL;
161
162 BEGIN
163
164 IF pg_debug = 'Y' THEN
165 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.populate_distributions(+)' );
166 END IF;
167
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170 --populate global dist. table
171 ar_trx_global_process_dist.insert_row(
172 p_trx_dist_tbl => p_trx_dist_tbl,
173 x_errmsg => x_errmsg,
174 x_return_status => x_return_status);
175
176 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
177 return;
178 END IF;
179
180 /* 5921925 - Should remove this but it would require us to rewrite
181 the internals of ar_trx_global_process_dist.insert_row */
182 UPDATE ar_trx_dist_gt dist
183 SET set_of_books_id = nvl(set_of_books_id,
184 p_trx_system_param_rec.set_of_books_id),
185 request_id = AR_INVOICE_TABLE_HANDLER.g_request_id,
186 /* gl_date = nvl(gl_date, nvl(p_batch_source_rec.default_date,
187 sysdate)),
188 Bug 3361235*/
189 (customer_trx_id, customer_trx_line_id, trx_header_id, gl_date) =
190 (
191 SELECT line.customer_trx_id,
192 line.customer_trx_line_id,
193 line.trx_header_id,
194 h.gl_date
195 FROM ar_trx_lines_gt line,
196 ar_trx_header_gt h
197 WHERE line.trx_line_id = dist.trx_line_id
198 AND line.trx_header_id = h.trx_header_id
199 UNION
200 SELECT h.customer_trx_id, null_column, h.trx_header_id, h.gl_date
201 FROM ar_trx_header_gt h
202 WHERE h.trx_header_id = dist.trx_header_id
203 AND dist.account_class = 'REC');
204
205
206 IF pg_debug = 'Y' THEN
207 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.populate_distributions(-)' );
208 END IF;
209
210 EXCEPTION
211 WHEN OTHERS THEN
212 x_errmsg := 'Error in AR_INVOICE_API_PUB.populate distributions'
213 || sqlerrm;
214 x_return_status := fnd_api.g_ret_sts_unexp_error;
215 RETURN;
216
217 END populate_distributions;
218
219
220 PROCEDURE populate_salescredits (
221 p_trx_salescredits_tbl IN trx_salescredits_tbl_type,
222 p_trx_system_param_rec IN AR_INVOICE_DEFAULT_PVT.trx_system_parameters_rec_type,
223 x_errmsg OUT NOCOPY VARCHAR2,
224 x_return_status OUT NOCOPY VARCHAR2 ) IS
225
226 BEGIN
227 IF pg_debug = 'Y'
228 THEN
229 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.populate_salescredits(+)' );
230 END IF;
231 x_return_status := FND_API.G_RET_STS_SUCCESS;
232 --populate global dist. table
233 AR_TRX_GLOBAL_PROCESS_SALESCR.INSERT_ROW(
234 p_trx_salescredits_tbl => p_trx_salescredits_tbl,
235 x_errmsg => x_errmsg,
236 x_return_status => x_return_status
237 );
238
239 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
240 THEN
241 return;
242 END IF;
243
244 /* 5921925 - Should remove this. Have to rewrite
245 ar_trx_global_process_salescr.insert_row */
246 UPDATE ar_trx_salescredits_gt sc
247 SET org_id = nvl(org_id, p_trx_system_param_rec.org_id),
248 request_id = AR_INVOICE_TABLE_HANDLER.g_request_id,
249 (customer_trx_id, customer_trx_line_id, trx_header_id) = (
250 SELECT line.customer_trx_id, line.customer_trx_line_id,
251 trx_header_id
252 FROM ar_trx_lines_gt line
253 WHERE line.trx_line_id = sc.trx_line_id
254 AND rownum = 1);
255
256
257 IF pg_debug = 'Y'
258 THEN
259 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.populate_salescredits(-)' );
260 END IF;
261 EXCEPTION
262 WHEN OTHERS THEN
263 x_errmsg := 'Error in AR_INVOICE_API_PUB.populate sales credits '||sqlerrm;
264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
265 return;
266
267 END populate_salescredits;
268
269 -- new subroutine introduced for "Payment Based Revenue Managment" project
270 -- ORASHID 20-September-2004
271
272 PROCEDURE populate_contingencies (
273 p_trx_contingencies_tbl trx_contingencies_tbl_type,
274 p_trx_system_param_rec ar_invoice_default_pvt.trx_system_parameters_rec_type,
275 x_errmsg OUT NOCOPY VARCHAR2,
276 x_return_status OUT NOCOPY VARCHAR2 ) IS
277
278 BEGIN
279
280 IF pg_debug = 'Y' THEN
281 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.populate_contingencies(+)' );
282 END IF;
283
284 x_return_status := FND_API.G_RET_STS_SUCCESS;
285 -- populate global contingencies table
286
287 ar_trx_global_process_cont.insert_row
288 (
289 p_trx_contingencies_tbl => p_trx_contingencies_tbl,
290 x_errmsg => x_errmsg,
291 x_return_status => x_return_status
292 );
293
294 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
295 RETURN;
296 END IF;
297
298 /* 5921925 - Should remove this.. Have to rewrite
299 ar_trx_global_process_cont.insert_row */
300 UPDATE ar_trx_contingencies_gt tcg
301 SET org_id = nvl(org_id, p_trx_system_param_rec.org_id),
302 request_id = ar_invoice_table_handler.g_request_id,
303 trx_header_id = (SELECT trx_header_id
304 FROM ar_trx_lines_gt tlg
305 WHERE tlg.trx_line_id = tcg.trx_line_id
306 AND rownum = 1);
307
308 IF pg_debug = 'Y' THEN
309 ar_invoice_utils.debug ('ar_invoice_api_pub.populate_contingencies(-)' );
310 END IF;
311
312 EXCEPTION
313 WHEN OTHERS THEN
314 x_errmsg := 'error ar_invoice_api_pub.populate_contingencies: '||sqlerrm;
315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
316 RETURN;
317
318 END populate_contingencies;
319
320 PROCEDURE clean_gt IS
321
322 BEGIN
323 delete from ar_trx_header_gt;
324 delete from ar_trx_lines_gt;
325 delete from ar_trx_dist_gt;
326 delete from ar_trx_salescredits_gt;
327 --delete from ar_trx_errors_gt;
328 DELETE FROM ZX_TRX_HEADERS_GT;
329 DELETE FROM ZX_TRANSACTION_LINES_GT;
330 DELETE FROM ZX_IMPORT_TAX_LINES_GT;
331 END;
332
333 PROCEDURE clean_tmp_gt IS
334
335 BEGIN
336 delete from ar_trx_header_tmp_gt;
337 delete from ar_trx_lines_tmp_gt;
338 delete from ar_trx_dist_tmp_gt;
339 delete from ar_trx_salescredits_tmp_gt;
340 --delete from ar_trx_errors_gt;
341 END;
342
343 PROCEDURE CREATE_INVOICE(
344 p_api_version IN NUMBER,
345 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
346 p_commit IN VARCHAR2 := FND_API.G_FALSE,
347 p_batch_source_rec IN batch_source_rec_type DEFAULT NULL,
348 p_trx_header_tbl IN trx_header_tbl_type,
349 p_trx_lines_tbl IN trx_line_tbl_type,
350 p_trx_dist_tbl IN trx_dist_tbl_type,
351 p_trx_salescredits_tbl IN trx_salescredits_tbl_type,
352 p_trx_contingencies_tbl IN trx_contingencies_tbl_type,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_msg_count OUT NOCOPY NUMBER,
355 x_msg_data OUT NOCOPY VARCHAR2) IS
356
357 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INVOICE';
358 l_api_version CONSTANT NUMBER := 1.0;
359
360 l_trx_system_parameters_rec AR_INVOICE_DEFAULT_PVT.trx_system_parameters_rec_type;
361 l_trx_profile_rec AR_INVOICE_DEFAULT_PVT.trx_profile_rec_type;
362 l_trx_date ra_customer_trx.trx_date%type;
363 x_errmsg VARCHAR2(2000);
364 l_batch_id NUMBER;
365 --anuj
366 cursor org_cur is
367 select org_id
368 from ar_trx_header_tmp_gt
369 group by org_id;
370 l_trx_header_tbl trx_header_tbl_type;
371 l_trx_lines_tbl trx_line_tbl_type;
372 l_trx_dist_tbl trx_dist_tbl_type;
373 l_trx_salescredits_tbl trx_salescredits_tbl_type;
374 l_org_return_status VARCHAR2(1);
375 l_org_id NUMBER;
376 --anuj
377
378 BEGIN
379
380 IF pg_debug = 'Y'
381 THEN
382 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.CREATE_INVOICE(2)(+)' );
383 END IF;
384
385 SAVEPOINT Create_Invoice;
386 /*--------------------------------------------------+
387 | Standard call to check for call compatibility |
388 +--------------------------------------------------*/
389
390 IF NOT FND_API.Compatible_API_Call(
391 l_api_version,
392 p_api_version,
393 l_api_name,
394 G_PKG_NAME
395 )
396 THEN
397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
398 END IF;
399
400 /*--------------------------------------------------------------+
401 | Initialize message list if p_init_msg_list is set to TRUE |
402 +--------------------------------------------------------------*/
403
404 IF FND_API.to_Boolean( p_init_msg_list )
405 THEN
406 FND_MSG_PUB.initialize;
407 END IF;
408
409 --anuj
410 clean_tmp_gt;
411 AR_TRX_GLOBAL_PROCESS_TMP.INSERT_ROWS (
412 p_trx_header_tbl =>p_trx_header_tbl,
413 p_trx_lines_tbl =>p_trx_lines_tbl,
414 p_trx_dist_tbl =>p_trx_dist_tbl,
415 p_trx_salescredits_tbl =>p_trx_salescredits_tbl,
416 x_errmsg =>x_errmsg,
417 x_return_status =>x_return_status);
418
419
420 IF pg_debug = 'Y'
421 THEN
422 ar_invoice_utils.debug ('Before looping thru invoice headers....' );
423 ar_invoice_utils.debug ('x_return_status = '|| x_return_status);
424 ar_invoice_utils.debug ('x_errmsg = '|| x_errmsg);
425 END IF;
426
427 FOR org_rec in org_cur LOOP
428 AR_TRX_GLOBAL_PROCESS_TMP.GET_ROWS (
429 p_org_id => org_rec.org_id,
430 p_trx_header_tbl => l_trx_header_tbl,
431 p_trx_lines_tbl => l_trx_lines_tbl,
432 p_trx_dist_tbl => l_trx_dist_tbl,
433 p_trx_salescredits_tbl => l_trx_salescredits_tbl,
434 x_errmsg => x_errmsg,
435 x_return_status => x_return_status);
436
437 ar_invoice_utils.debug ('Looping thru invoice headers....' );
438 ar_invoice_utils.debug ('x_return_status = '|| x_return_status);
439 ar_invoice_utils.debug ('x_errmsg = '|| x_errmsg);
440
441 l_org_id := org_rec.org_id;
442 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
443 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
444 p_return_status =>l_org_return_status);
445 ar_invoice_utils.debug ('l_org_id = '|| l_org_id);
446 ar_invoice_utils.debug ('l_org_return_status = '|| l_org_return_status);
447
448 /* 6006015 - this logic differs from 11.5 because
449 the transactions are processed by org. So we
450 have to init each time the org changes */
451 IF g_one_time_init_org <> l_org_id
452 THEN
453 ar_invoice_default_pvt.get_system_parameters(
454 p_trx_system_parameters_rec => l_trx_system_parameters_rec,
455 x_errmsg => x_errmsg,
456 x_return_status => x_return_status);
457
458 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
459 THEN
460 ROLLBACK to Create_Invoice;
461 x_msg_data := x_errmsg;
462 return;
463 END IF;
464
465 -- Get the default values from profile options;
466 ar_invoice_default_pvt.Get_profile_values(
467 p_trx_profile_rec => l_trx_profile_rec,
468 x_errmsg => x_errmsg,
469 x_return_status => x_return_status) ;
470
471 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
472 THEN
473 ROLLBACK to Create_Invoice;
474 x_msg_data := x_errmsg;
475 return;
476 END IF;
477 END IF;
478
479 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
480 x_return_status := FND_API.G_RET_STS_ERROR;
481 ELSE
482
483 -- first clean all global temporary tables to start with
484 clean_gt;
485
486 IF (l_trx_header_tbl.COUNT = 0) OR (l_trx_lines_tbl.COUNT = 0) THEN
487 ROLLBACK to Create_Invoice;
488 x_return_status := fnd_api.g_ret_sts_unexp_error;
489 x_msg_data := arp_standard.fnd_message('AR_INAPI_TABLES_EMPTY');
490 RETURN;
491 END IF;
492
493 IF pg_debug = 'Y'
494 THEN
495 ar_invoice_utils.debug ('Calling Default Rtn from ar_invoice_pub(-)' );
496 ar_invoice_utils.debug ('Create Batch(+)' );
497 END IF;
498
499 /* 5921925 - only create a batch if they are not calling in single invoice
500 mode */
501 IF NOT g_single_invoice
502 THEN
503 -- Create a batch
504 AR_INVOICE_TABLE_HANDLER.create_batch(
505 p_trx_system_parameters_rec => l_trx_system_parameters_rec,
506 p_trx_profile_rec => l_trx_profile_rec,
507 p_batch_source_rec => p_batch_source_rec,
508 p_batch_id => l_batch_id,
509 x_errmsg => x_errmsg,
510 x_return_status => x_return_status);
511
512 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
513 THEN
514 ROLLBACK to Create_Invoice;
515 x_msg_data := x_errmsg;
516 return;
517 END IF;
518 ELSE
519 /* 5921925 - insure that we have a viable request_id for this batch
520 even if its only one transaction */
521 SELECT ra_batches_s.nextval * -1
522 INTO ar_invoice_table_handler.g_request_id
523 FROM dual;
524 END IF;
525
526 --first popolate the global temp. table based on user passed variables and
527 -- default system parameters and profile values.
528 IF pg_debug = 'Y'
529 THEN
530 ar_invoice_utils.debug ('Create Batch(-)' );
531 ar_invoice_utils.debug ('populate header(+)' );
532 END IF;
533 populate_header( p_trx_header_tbl => l_trx_header_tbl,
534 p_trx_system_param_rec => l_trx_system_parameters_rec,
535 p_trx_profile_rec => l_trx_profile_rec,
536 p_batch_source_rec => p_batch_source_rec,
537 x_errmsg => x_errmsg,
538 x_return_status => x_return_status);
539 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
540 THEN
541 ROLLBACK to Create_Invoice;
542 x_msg_data := x_errmsg;
543 return;
544 END IF;
545 IF pg_debug = 'Y'
546 THEN
547 ar_invoice_utils.debug ('populate header (-)' );
548 ar_invoice_utils.debug ('populate lines (+)' );
549 END IF;
550
551 populate_lines ( p_trx_lines_tbl => l_trx_lines_tbl,
552 p_trx_system_param_rec => l_trx_system_parameters_rec,
553 x_errmsg => x_errmsg,
554 x_return_status => x_return_status);
555
556 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
557 ROLLBACK to Create_Invoice;
558 x_msg_data := x_errmsg;
559 RETURN;
560 END IF;
561
562 -- Check for validations that spans across header and lines.
563
564 IF pg_debug = 'Y' THEN
565 ar_invoice_utils.debug ('validate_master_detail' );
566 END IF;
567
568 ar_invoice_utils.validate_master_detail
569 ( x_errmsg => x_errmsg,
570 x_return_status => x_return_status);
571
572 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
573 ROLLBACK to Create_Invoice;
574 x_msg_data := x_errmsg;
575 RETURN;
576 END IF;
577
578 IF pg_debug = 'Y'
579 THEN
580 ar_invoice_utils.debug ('populate lines (-)' );
581 ar_invoice_utils.debug ('populate distributions (+)' );
582 END IF;
583
584 /* 5921925 - only execute if there are rows */
585 IF p_trx_dist_tbl.count > 0
586 THEN
587 g_dist_exist := TRUE;
588 populate_distributions (p_trx_dist_tbl => p_trx_dist_tbl,
589 p_batch_source_rec => p_batch_source_rec,
590 p_trx_system_param_rec => l_trx_system_parameters_rec,
591 x_errmsg => x_errmsg,
592 x_return_status => x_return_status );
593
594 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
595 THEN
596 ROLLBACK to Create_Invoice;
597 x_msg_data := x_errmsg;
598 return;
599 END IF;
600 ELSE
601 g_dist_exist := FALSE;
602 END IF;
603
604 IF pg_debug = 'Y'
605 THEN
606 ar_invoice_utils.debug ('populate distributions (-)' );
607 ar_invoice_utils.debug ('populate sales credits (+)' );
608 END IF;
609
610 /* 5921925 - only execute if rows exist */
611 IF p_trx_salescredits_tbl.count > 0
612 THEN
613 g_sc_exist := TRUE;
614 populate_salescredits (p_trx_salescredits_tbl => p_trx_salescredits_tbl,
615 p_trx_system_param_rec => l_trx_system_parameters_rec,
616 x_errmsg => x_errmsg,
617 x_return_status => x_return_status );
618
619 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
620 THEN
621 ROLLBACK to Create_Invoice;
622 x_msg_data := x_errmsg;
623 return;
624 END IF;
625 ELSE
626 g_sc_exist := FALSE;
627 END IF;
628
629 IF pg_debug = 'Y' THEN
630 ar_invoice_utils.debug('populate sales credits(-)' );
631 ar_invoice_utils.debug('populate contingencies(+)' );
632 END IF;
633
634 /* 5921925 - only populate if rows exist */
635 IF p_trx_contingencies_tbl.count > 0
636 THEN
637 g_cont_exist := TRUE;
638 populate_contingencies(
639 p_trx_contingencies_tbl => p_trx_contingencies_tbl,
640 p_trx_system_param_rec => l_trx_system_parameters_rec,
641 x_errmsg => x_errmsg,
642 x_return_status => x_return_status);
643
644 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
645 THEN
646 ROLLBACK to Create_Invoice;
647 x_msg_data := x_errmsg;
648 return;
649 END IF;
650 ELSE
651 g_cont_exist := FALSE;
652 END IF;
653
654 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
655 ROLLBACK TO Create_Invoice;
656 x_msg_data := x_errmsg;
657 RETURN;
658 END IF;
659
660 -- ORASHID 20-Sep-2004
661 -- END
662
663 IF pg_debug = 'Y' THEN
664 ar_invoice_utils.debug ('populate contingencies(-)' );
665 END IF;
666
667 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
668 ROLLBACK to Create_Invoice;
669 x_msg_data := x_errmsg;
670 RETURN;
671 END IF;
672
673 -- Validate all inter-dependent parameters
674 IF pg_debug = 'Y'
675 THEN
676 ar_invoice_utils.debug ('validate_dependend_parameter' );
677 END IF;
678 ar_invoice_utils.validate_dependent_parameters
679 ( p_trx_system_param_rec => l_trx_system_parameters_rec,
680 x_errmsg => x_errmsg,
681 x_return_status => x_return_status);
682
683 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
684 THEN
685 ROLLBACK to Create_Invoice;
686 x_msg_data := x_errmsg;
687 return;
688 END IF;
689
690 -- Now validate all the values which user has passed and populate
691 -- any dependent fields.
692 IF pg_debug = 'Y'
693 THEN
694 ar_invoice_utils.debug ('validate_header from ar_invoice_pub (+)' );
695 END IF;
696
697 ar_invoice_utils.validate_header
698 ( p_trx_system_param_rec => l_trx_system_parameters_rec,
699 p_trx_profile_rec => l_trx_profile_rec,
700 x_errmsg => x_errmsg,
701 x_return_status => x_return_status);
702
703 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
704 THEN
705 ROLLBACK to Create_Invoice;
706 x_msg_data := x_errmsg;
707 return;
708 END IF;
709
710
711 IF pg_debug = 'Y'
712 THEN
713 ar_invoice_utils.debug ('validate_header from ar_invoice_pub (-)' );
714 ar_invoice_utils.debug ('validate_lines from ar_invoice_pub (+)' );
715 END IF;
716
717 ar_invoice_utils.validate_lines
718 ( x_errmsg => x_errmsg,
719 x_return_status => x_return_status);
720
721 /* Bug9356903 When creating Credit Memo, Quantity_Credited
722 should be populated. Quantity_Invocied must be made NULL. */
723 UPDATE ar_trx_lines_gt
724 SET quantity_credited = quantity_invoiced,
725 quantity_invoiced = null
726 WHERE trx_line_id in
727 (SELECT gt.trx_line_id
728 FROM ar_trx_lines_gt gt ,
729 ar_trx_header_gt gt2
730 WHERE gt.trx_header_id = gt2.trx_header_id
731 AND gt2.trx_class = 'CM'
732 AND gt.line_type = 'LINE');
733
734 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
735 THEN
736 ROLLBACK to Create_Invoice;
737 x_msg_data := x_errmsg;
738 return;
739 END IF;
740 IF pg_debug = 'Y'
741 THEN
742 ar_invoice_utils.debug ('validate_lines from ar_invoice_pub (-)' );
743 ar_invoice_utils.debug ('validate_distributions from ar_invoice_pub (+)' );
744 END IF;
745
746 /* 5921925 - prevent this call if no distributions passed */
747 IF g_dist_exist
748 THEN
749 ar_invoice_utils.validate_distributions
750 ( p_trx_system_parameters_rec => l_trx_system_parameters_rec,
751 x_errmsg => x_errmsg,
752 x_return_status => x_return_status);
753
754 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
755 THEN
756 ROLLBACK to Create_Invoice;
757 x_msg_data := x_errmsg;
758 return;
759 END IF;
760 END IF;
761
762 IF pg_debug = 'Y'
763 THEN
764 ar_invoice_utils.debug ('validate_distributions from ar_invoice_pub (-)' );
765 ar_invoice_utils.debug ('validate_salescredits from ar_invoice_pub (+)' );
766 END IF;
767
768 IF g_sc_exist
769 THEN
770 ar_invoice_utils.validate_salescredits(
771 p_trx_system_param_rec => l_trx_system_parameters_rec,
772 x_errmsg => x_errmsg,
773 x_return_status => x_return_status);
774
775 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
776 THEN
777 ROLLBACK to Create_Invoice;
778 x_msg_data := x_errmsg;
779 return;
780 END IF;
781 END IF;
782
783 IF pg_debug = 'Y'
784 THEN
785 ar_invoice_utils.debug ('validate_salescredits from ar_invoice_pub (-)' );
786 ar_invoice_utils.debug ('vaidate_gdf (+) ');
787 END IF;
788
789 ar_invoice_utils.validate_gdf(
790 p_request_id => AR_INVOICE_TABLE_HANDLER.g_request_id,
791 x_errmsg => x_errmsg,
792 x_return_status => x_return_status);
793
794 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
795 THEN
796 ROLLBACK to Create_Invoice;
797 x_msg_data := x_errmsg;
798 return;
799 END IF;
800 IF pg_debug = 'Y'
801 THEN
802 ar_invoice_utils.debug ('validate_gdf (-)' );
803 ar_invoice_utils.debug ('Calling Table Handler ar_invoice_table_handler.insert_row (+) ');
804 END IF;
805
806 AR_INVOICE_TABLE_HANDLER.insert_row(
807 p_trx_system_parameters_rec => l_trx_system_parameters_rec,
808 p_trx_profile_rec => l_trx_profile_rec,
809 p_batch_source_rec => p_batch_source_rec,
810 x_errmsg => x_errmsg,
811 x_return_status => x_return_status) ;
812
813 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
814 THEN
815 ROLLBACK to Create_Invoice;
816 x_msg_data := x_errmsg;
817 return;
818 END IF;
819 IF jg_zz_ar_trx_pkg.is_reg_loc_enabled THEN
820 ar_invoice_utils.debug('calling: jg_zz_ar_trx_pkg.is_reg_loc_enabled');
821 DECLARE
822 temp_customer_trx_id ra_customer_trx_all.customer_trx_id%type;
823 p_return_flag boolean;
824 p_return_code number;
825 p_return_message varchar2(1024);
826 BEGIN
827 temp_customer_trx_id := AR_INVOICE_API_PUB.g_customer_trx_id;
828
829 jg_zz_ar_trx_pkg.do_trx_complete(null,temp_customer_trx_id,'INVOICEAPI',p_return_flag, p_return_code,p_return_message);
830
831 IF (p_return_flag = FALSE) THEN
832 ar_invoice_utils.debug('Creating the invoices in Complete Status ');
833 ar_invoice_utils.debug('Issue at the time of signature generation for localization');
834 ar_invoice_utils.debug('Message : '||p_return_message);
835 x_msg_data := p_return_message;
836 x_return_status :=FND_API.G_RET_STS_ERROR;
837 x_msg_count :=-99;
838 END IF;
839
840 END;
841 ar_invoice_utils.debug (' Exiting from PT Code Hook');
842 END IF;
843
844 /*
845 Fixed bug 12562413 and add JL TRANSACTION NUMBERING MECHANISM while execting the
846 Invoice AIP to create transaction
847 */
848 IF JG_ZZ_AR_AUTO_INVOICE.is_reg_loc_enabled THEN
849 ar_invoice_utils.debug (' Calling JL Code Hook');
850 DECLARE
851 temp_customer_trx_id ra_customer_trx_all.customer_trx_id%type;
852 t_code number;
853 t_errmsg varchar2(1024);
854 BEGIN
855 temp_customer_trx_id := AR_INVOICE_API_PUB.g_customer_trx_id;
856 JG_ZZ_AR_AUTO_INVOICE.Trx_Num_Upd_Single_API(temp_customer_trx_id);
857 UPDATE ar_payment_schedules ps
858 SET trx_number = (SELECT ct.trx_number
859 FROM ra_customer_trx ct
860 WHERE ct.customer_trx_id =temp_customer_trx_id)
861 WHERE ps.customer_trx_id = temp_customer_trx_id;
862 EXCEPTION
863 WHEN OTHERS THEN
864 t_code :=sqlcode;
865 t_errmsg := sqlerrm;
866 ROLLBACK to Create_Invoice;
867 ar_invoice_utils.debug (' Error Calling JL Code Hook');
868 ar_invoice_utils.debug (t_code||t_errmsg);
869 raise;
870 END;
871 ar_invoice_utils.debug (' Exiting from JL Code Hook');
872 END IF;
873
874 -- Standard check of p_commit.
875 IF FND_API.To_Boolean( p_commit )
876 THEN
877 COMMIT;
878 END IF;
879 END IF;
880
881 END LOOP; --anuj
882
883
884 --{Call creation of the events should outside the loop as not org striped
885 --
886 ar_invoice_utils.debug ('Call creation of XLA events in bulk mode +' );
887 ar_invoice_utils.debug (' Using the request_id :'||AR_INVOICE_TABLE_HANDLER.g_request_id);
888
889 arp_xla_events.Create_Events_Req(p_request_id => AR_INVOICE_TABLE_HANDLER.g_request_id,
890 p_doc_table => 'CT',
891 p_mode => 'B',
892 p_call => 'B');
893 ar_invoice_utils.debug ('Call creation of XLA events in bulk mode -' );
894 --}
895
896 IF pg_debug = 'Y'
897 THEN
898 ar_invoice_utils.debug ('Calling Table Handler ar_invoice_table_handler.insert_row (-) ');
899 ar_invoice_utils.debug ('ar_invoice_api_pub.create_invoice(-)' );
900 END IF;
901
902
903 END CREATE_INVOICE;
904
905 PROCEDURE CREATE_SINGLE_INVOICE(
906 p_api_version IN NUMBER,
907 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
908 p_commit IN VARCHAR2 := FND_API.G_FALSE,
909 p_batch_source_rec IN batch_source_rec_type DEFAULT NULL,
910 p_trx_header_tbl IN trx_header_tbl_type,
911 p_trx_lines_tbl IN trx_line_tbl_type,
912 p_trx_dist_tbl IN trx_dist_tbl_type,
913 p_trx_salescredits_tbl IN trx_salescredits_tbl_type,
914 p_trx_contingencies_tbl IN trx_contingencies_tbl_type,
915 x_customer_trx_id OUT NOCOPY NUMBER,
916 x_return_status OUT NOCOPY VARCHAR2,
917 x_msg_count OUT NOCOPY NUMBER,
918 x_msg_data OUT NOCOPY VARCHAR2) IS
919
920 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INVOICE';
921 l_api_version CONSTANT NUMBER := 1.0;
922 l_no_of_records NUMBER := 0;
923
924
925 BEGIN
926 IF pg_debug = 'Y'
927 THEN
928 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE(2)(+)' );
929 END IF;
930
931 x_return_status := FND_API.G_RET_STS_SUCCESS;
932
933 l_no_of_records := p_trx_header_tbl.COUNT;
934
935 /* 5921925 - global variable to track single invoice call */
936 g_single_invoice := TRUE;
937
938 IF ( nvl(l_no_of_records,0) > 1 )
939 THEN
940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
941 x_msg_data := arp_standard.fnd_message('AR_INAPI_MULTIPLE_HEADERS');
942 return;
943 END IF;
944
945 AR_INVOICE_API_PUB.g_customer_trx_id := NULL; -- manishri, Bug 9915107.
946
947 CREATE_INVOICE (
948 p_api_version => p_api_version,
949 p_init_msg_list => p_init_msg_list,
950 p_commit => p_commit,
951 p_batch_source_rec => p_batch_source_rec,
952 p_trx_header_tbl => p_trx_header_tbl,
953 p_trx_lines_tbl => p_trx_lines_tbl,
954 p_trx_dist_tbl => p_trx_dist_tbl,
955 p_trx_salescredits_tbl => p_trx_salescredits_tbl,
956 p_trx_contingencies_tbl => p_trx_contingencies_tbl,
957 x_return_status => x_return_status,
958 x_msg_count => x_msg_count,
959 x_msg_data => x_msg_data);
960
961 -- added code nvl(x_msg_count,0)=-99 for bug10349867
962 IF x_return_status = FND_API.G_RET_STS_SUCCESS
963 THEN
964 -- get the value of cust_trx_id
965 x_customer_trx_id := AR_INVOICE_API_PUB.g_customer_trx_id;
966 END IF;
967
968 IF pg_debug = 'Y'
969 THEN
970 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE(-)' );
971 END IF;
972
973 END CREATE_SINGLE_INVOICE;
974
975
976 -- added the overloaded procedures to make the api backward compatible
977 -- ORASHID
978 -- 11-OCT-2004
979
980 PROCEDURE create_invoice(
981 p_api_version IN NUMBER,
982 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
983 p_commit IN VARCHAR2 := FND_API.G_FALSE,
984 p_batch_source_rec IN batch_source_rec_type DEFAULT NULL,
985 p_trx_header_tbl IN trx_header_tbl_type,
986 p_trx_lines_tbl IN trx_line_tbl_type,
987 p_trx_dist_tbl IN trx_dist_tbl_type,
988 p_trx_salescredits_tbl IN trx_salescredits_tbl_type,
989 x_return_status OUT NOCOPY VARCHAR2,
990 x_msg_count OUT NOCOPY NUMBER,
991 x_msg_data OUT NOCOPY VARCHAR2) IS
992
993 l_trx_contingencies_tbl trx_contingencies_tbl_type;
994
995 BEGIN
996
997 IF pg_debug = 'Y' THEN
998 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE(+)' );
999 END IF;
1000
1001 -- call the api with a null trx_contingencies_tbl
1002
1003 create_invoice (
1004 p_api_version => p_api_version,
1005 p_init_msg_list => p_init_msg_list,
1006 p_commit => p_commit,
1007 p_batch_source_rec => p_batch_source_rec,
1008 p_trx_header_tbl => p_trx_header_tbl,
1009 p_trx_lines_tbl => p_trx_lines_tbl,
1010 p_trx_dist_tbl => p_trx_dist_tbl,
1011 p_trx_salescredits_tbl => p_trx_salescredits_tbl,
1012 p_trx_contingencies_tbl => l_trx_contingencies_tbl,
1013 x_return_status => x_return_status,
1014 x_msg_count => x_msg_count,
1015 x_msg_data => x_msg_data);
1016
1017 IF pg_debug = 'Y' THEN
1018 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE(-)' );
1019 END IF;
1020
1021 END create_invoice;
1022
1023
1024 PROCEDURE create_single_invoice (
1025 p_api_version IN NUMBER,
1026 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1027 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1028 p_batch_source_rec IN batch_source_rec_type DEFAULT NULL,
1029 p_trx_header_tbl IN trx_header_tbl_type,
1030 p_trx_lines_tbl IN trx_line_tbl_type,
1031 p_trx_dist_tbl IN trx_dist_tbl_type,
1032 p_trx_salescredits_tbl IN trx_salescredits_tbl_type,
1033 x_customer_trx_id OUT NOCOPY NUMBER,
1034 x_return_status OUT NOCOPY VARCHAR2,
1035 x_msg_count OUT NOCOPY NUMBER,
1036 x_msg_data OUT NOCOPY VARCHAR2) IS
1037
1038 l_trx_contingencies_tbl trx_contingencies_tbl_type;
1039
1040 BEGIN
1041
1042 IF pg_debug = 'Y' THEN
1043 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE(+)' );
1044 END IF;
1045
1046 -- call the api with a null trx_contingencies_tbl
1047
1048 create_single_invoice (
1049 p_api_version => p_api_version,
1050 p_init_msg_list => p_init_msg_list,
1051 p_commit => p_commit,
1052 p_batch_source_rec => p_batch_source_rec,
1053 p_trx_header_tbl => p_trx_header_tbl,
1054 p_trx_lines_tbl => p_trx_lines_tbl,
1055 p_trx_dist_tbl => p_trx_dist_tbl,
1056 p_trx_salescredits_tbl => p_trx_salescredits_tbl,
1057 p_trx_contingencies_tbl => l_trx_contingencies_tbl,
1058 x_customer_trx_id => x_customer_trx_id,
1059 x_return_status => x_return_status,
1060 x_msg_count => x_msg_count,
1061 x_msg_data => x_msg_data);
1062
1063 IF pg_debug = 'Y' THEN
1064 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE(-)' );
1065 END IF;
1066
1067 END create_single_invoice;
1068
1069 -- Bug 7194381 Start
1070
1071 /*===========================================================================+
1072 | PROCEDURE |
1073 | Cache_Transaction_Type |
1074 | |
1075 | DESCRIPTION |
1076 | Caches each transaction type when it is first used so that |
1077 | type values can easily be accessed later and so that the |
1078 | type record does not have to be fetched from the database |
1079 | for future transactions. |
1080 | The whole table is not cached at startup because it might be |
1081 | very large on some sites, and because it is likely that a |
1082 | few types will be used many times. |
1083 | |
1084 | SCOPE - PRIVATE |
1085 | |
1086 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1087 | |
1088 | ARGUMENTS : IN: |
1089 | p_context_rec |
1090 | p_cust_trx_type_id |
1091 | OUT: |
1092 | IN/ OUT: |
1093 | |
1094 | RETURNS : NONE |
1095 | |
1096 | NOTES |
1097 | |
1098 | MODIFICATION HISTORY |
1099 | mpsingh 20-JUN-08 Created |
1100 | |
1101 +===========================================================================*/
1102
1103 PROCEDURE Cache_Transaction_Type(
1104 p_context_rec IN Context_Rec_Type,
1105 p_cust_trx_type_id IN
1106 ra_cust_trx_types.cust_trx_type_id%type
1107 ) IS
1108
1109 l_temp BINARY_INTEGER;
1110 l_dummy_type ra_cust_trx_types%rowtype;
1111
1112 BEGIN
1113
1114 ar_invoice_utils.debug('Cache_Transaction_Type()+');
1115
1116 IF ( p_cust_trx_type_id IS NOT NULL )
1117 THEN
1118
1119 /*------------------------------------------------------+
1120 | Add the current transaction type to the type cache |
1121 | if it does not already exist in the cache. |
1122 +------------------------------------------------------*/
1123
1124 IF ( NOT Type_Cache_Tbl.EXISTS( p_cust_trx_type_id ) )
1125 THEN
1126
1127 BEGIN
1128 SELECT *
1129 INTO Type_Cache_Tbl( p_cust_trx_type_id )
1130 FROM ra_cust_trx_types
1131 WHERE cust_trx_type_id = p_cust_trx_type_id;
1132
1133 ar_invoice_utils.debug('Transaction Type: ' ||
1134 Type_Cache_Tbl( p_cust_trx_type_id ).name ||
1135 ' found.');
1136
1137 EXCEPTION
1138 WHEN NO_DATA_FOUND
1139 THEN
1140 /*---------------------------------------------+
1141 | If the type does not exist, assign a |
1142 | null type record to its place in the cache |
1143 | to avoid NO_DATA_FOUND errors later on. |
1144 | |
1145 | The invalid data will be caught later in |
1146 | the validation routines. |
1147 +---------------------------------------------*/
1148
1149 Type_Cache_Tbl( p_cust_trx_type_id ) :=
1150 l_dummy_type;
1151 ar_invoice_utils.debug('Transaction Type not found');
1152
1153 WHEN OTHERS THEN
1154 RAISE;
1155 END;
1156
1157 END IF;
1158
1159 END IF;
1160
1161 ar_invoice_utils.debug('Cache_Transaction_type()-');
1162
1163 EXCEPTION
1164 WHEN NO_DATA_FOUND THEN NULL;
1165
1166 WHEN OTHERS THEN
1167 ar_invoice_utils.debug('EXCEPTION: Cache_Transaction_Type() ');
1168 ar_invoice_utils.debug('p_cust_trx_type_id = ' ||
1169 TO_CHAR(p_cust_trx_type_id));
1170 RAISE;
1171
1172 END Cache_Transaction_Type;
1173
1174 /*===========================================================================+
1175 | PROCEDURE Get_Flags |
1176 | |
1177 | DESCRIPTION |
1178 | Gets information about the transaction that is used in determining if |
1179 | it can be updated. |
1180 | |
1181 | SCOPE - PRIVATE |
1182 | |
1183 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1184 | |
1185 | ARGUMENTS : IN: |
1186 | p_context_rec |
1187 | OUT: |
1188 | p_dm_reversal |
1189 | p_cb |
1190 | IN/ OUT: |
1191 | p_trx_rec |
1192 | p_type_rec |
1193 | p_posted_flag |
1194 | p_activity_flag |
1195 | p_printed_flag |
1196 | p_rev_recog_run_flag |
1197 | |
1198 | RETURNS : None |
1199 | |
1200 | NOTES |
1201 | |
1202 | MODIFICATION HISTORY |
1203 | mpsingh 20-jun-2008 Created
1204 | |
1205 +===========================================================================*/
1206
1207 PROCEDURE Get_Flags(
1208 p_context_rec IN Context_Rec_Type,
1209 p_trx_rec IN OUT NOCOPY ra_customer_trx%rowtype,
1210 p_type_rec IN OUT NOCOPY ra_cust_trx_types%rowtype,
1211 p_posted_flag IN OUT NOCOPY VARCHAR2,
1212 p_activity_flag IN OUT NOCOPY VARCHAR2,
1213 p_printed_flag IN OUT NOCOPY VARCHAR2,
1214 p_rev_recog_run_flag IN OUT NOCOPY VARCHAR2,
1215 p_dm_reversal OUT NOCOPY VARCHAR2,
1216 p_cb OUT NOCOPY VARCHAR2
1217 ) IS
1218
1219 l_index BINARY_INTEGER;
1220
1221 BEGIN
1222
1223 ar_invoice_utils.debug('Get_Flags()+');
1224
1225 p_posted_flag := 'N';
1226 p_activity_flag := 'N';
1227 p_dm_reversal := 'N';
1228 p_cb := 'N';
1229 p_printed_flag := 'N';
1230 p_rev_recog_run_flag := 'N';
1231
1232 p_posted_flag := arpt_sql_func_util.get_posted_flag(
1233 p_trx_rec.customer_trx_id,
1234 p_type_rec.post_to_gl,
1235 p_trx_rec.complete_flag );
1236
1237 p_activity_flag := arpt_sql_func_util.get_activity_flag(
1238 p_trx_rec.customer_trx_id,
1239 p_type_rec.accounting_affect_flag,
1240 p_trx_rec.complete_flag,
1241 p_type_rec.type,
1242 p_trx_rec.initial_customer_trx_id,
1243 p_trx_rec.previous_customer_trx_id
1244 );
1245
1246 IF ( NVL(p_trx_rec.printing_count, 0) > 0 )
1247 THEN p_printed_flag := 'Y';
1248 END IF;
1249
1250 /*--------------------------------------------------------------------+
1251 | Determine if Revenue Recognition has been run for any of the lines |
1252 +--------------------------------------------------------------------*/
1253
1254 FOR l_index IN 1..G_lines_tbl.count LOOP
1255
1256 IF ( G_lines_tbl(l_index).autorule_duration_processed > 0 )
1257 THEN p_rev_recog_run_flag := 'Y';
1258 END IF;
1259
1260 END LOOP;
1261
1262 ar_invoice_utils.debug('. posted_flag = ' ||
1263 p_posted_flag);
1264
1265 ar_invoice_utils.debug('. activity_flag = ' ||
1266 p_activity_flag);
1267
1268 ar_invoice_utils.debug('. printed_flag = ' ||
1269 p_printed_flag);
1270
1271 ar_invoice_utils.debug('. p_rev_recog_run_flag = ' ||
1272 p_rev_recog_run_flag);
1273
1274 ar_invoice_utils.debug('. Class = ' ||
1275 p_type_rec.type);
1276
1277 ar_invoice_utils.debug('. created_from = ' ||
1278 p_trx_rec.created_from);
1279
1280 IF ( p_trx_rec.created_from IN ('ARXREV', 'REL9_ARXREV') )
1281 THEN
1282 p_dm_reversal := 'Y';
1283 END IF;
1284
1285 IF ( p_type_rec.type = 'CB' )
1286 THEN
1287 p_cb := 'Y';
1288 END IF;
1289
1290 ar_invoice_utils.debug('Get_Flags()-');
1291
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294
1295 ar_invoice_utils.debug('EXCEPTION: Get_Flags()');
1296 RAISE;
1297
1298 END Get_Flags;
1299
1300 /*===========================================================================+
1301 | PROCEDURE Validate_Delete_Transaction |
1302 | |
1303 | DESCRIPTION |
1304 | Validates that the information provided for a transaction |
1305 | deletion does not violate any of the validation rules. |
1306 | |
1307 | SCOPE - PRIVATE |
1308 | |
1309 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1310 | |
1311 | ARGUMENTS : IN: |
1312 | p_customer_trx_id |
1313 | p_trx_rec |
1314 | p_type_rec |
1315 | OUT: |
1316 | p_return_status |
1317 | IN/ OUT: |
1318 | |
1319 | RETURNS : NONE |
1320 | |
1321 | NOTES |
1322 | |
1323 | MODIFICATION HISTORY |
1324 | mpsingh 20-Jun-08 Created |
1325 | |
1326 +===========================================================================*/
1327
1328 PROCEDURE Validate_Delete_Transaction(
1329 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
1330 p_trx_rec IN OUT NOCOPY ra_customer_trx%rowtype,
1331 p_type_rec IN OUT NOCOPY ra_cust_trx_types%rowtype,
1332 p_return_status OUT NOCOPY VARCHAR2
1333 ) IS
1334
1335 l_context_rec Context_Rec_Type;
1336 l_dummy BINARY_INTEGER;
1337 l_posted_flag VARCHAR2(1);
1338 l_activity_flag VARCHAR2(1);
1339 l_printed_flag VARCHAR2(1);
1340 l_rev_recog_run_flag VARCHAR2(1);
1341 l_dm_reversal_flag VARCHAR2(1);
1342 l_cb_flag VARCHAR2(1);
1343
1344 BEGIN
1345
1346 ar_invoice_utils.debug('Validate_Delete_Transaction()+');
1347
1348 p_return_status := FND_API.G_RET_STS_SUCCESS;
1349
1350 /*------------------------------------------------------------+
1351 | Get the header record for the transaction to be deleted. |
1352 +------------------------------------------------------------*/
1353
1354 BEGIN
1355 arp_ct_pkg.fetch_p(
1356 p_trx_rec,
1357 p_customer_trx_id
1358 );
1359
1360 EXCEPTION
1361 WHEN NO_DATA_FOUND THEN
1362 p_return_status := FND_API.G_RET_STS_ERROR;
1363
1364 arp_trx_validate.Add_To_Error_List(
1365 p_mode => 'PL/SQL',
1366 P_error_count => l_dummy,
1367 p_customer_trx_id => p_customer_trx_id,
1368 p_trx_number => null,
1369 p_line_number => null,
1370 p_other_line_number => null,
1371 p_message_name => 'AR_TAPI_TRANS_NOT_EXIST',
1372 p_token_name_1 => 'CUSTOMER_TRX_ID',
1373 p_token_1 => p_customer_trx_id );
1374
1375 RAISE;
1376
1377 WHEN OTHERS THEN RAISE;
1378 END;
1379
1380 /*--------------------------------------------------------------------+
1381 | The invoice_deletion_flag must be Y for transactions to be deleted |
1382 +--------------------------------------------------------------------*/
1383
1384 ar_invoice_utils.debug('. invoice_deletion_flag = ' ||
1385 arp_global.sysparam.invoice_deletion_flag);
1386
1387 IF ( arp_global.sysparam.invoice_deletion_flag <> 'Y' )
1388 THEN
1389
1390 p_return_status := FND_API.G_RET_STS_ERROR;
1391
1392 arp_trx_validate.Add_To_Error_List(
1393 p_mode => 'PL/SQL',
1394 P_error_count => l_dummy,
1395 p_customer_trx_id => p_trx_rec.customer_trx_id,
1396 p_trx_number => p_trx_rec.trx_number,
1397 p_line_number => null,
1398 p_other_line_number => null,
1399 p_message_name => 'AR_CANT_DELETE_IF_COMPLETE');
1400
1401 ELSE
1402
1403 Cache_Transaction_Type(
1404 l_context_rec,
1405 p_trx_rec.cust_trx_type_id
1406 );
1407
1408 p_type_rec := Type_Cache_Tbl(
1409 p_trx_rec.cust_trx_type_id
1410 );
1411
1412 /*--------------------------------------------------------------+
1413 | If the transaction is complete, it must be made incomplete |
1414 | before it can be deleted. |
1415 | |
1416 | A transaction can only be made incomplete if: |
1417 | o It is not a chargeback and |
1418 | o It is not a debit memo reversal and |
1419 | o It has not been posted to GL and |
1420 | o There is no activity against it. |
1421 +--------------------------------------------------------------*/
1422
1423 ar_invoice_utils.debug('. complete_flag = ' ||
1424 p_trx_rec.complete_flag);
1425
1426 IF ( p_trx_rec.complete_flag = 'Y' )
1427 THEN
1428
1429 Get_Flags(
1430 l_context_rec,
1431 p_trx_rec,
1432 p_type_rec,
1433 l_posted_flag,
1434 l_activity_flag,
1435 l_printed_flag,
1436 l_rev_recog_run_flag,
1437 l_dm_reversal_flag,
1438 l_cb_flag
1439 );
1440
1441 IF ( l_dm_reversal_flag = 'Y' )
1442 THEN
1443 p_return_status := FND_API.G_RET_STS_ERROR;
1444
1445 arp_trx_validate.Add_To_Error_List(
1446 p_mode => 'PL/SQL',
1447 p_error_count => l_dummy,
1448 p_customer_trx_id => p_trx_rec.customer_trx_id,
1449 p_trx_number => p_trx_rec.trx_number,
1450 p_line_number => null,
1451 p_other_line_number => null,
1452 p_message_name =>
1453 'AR_TAPI_CANT_DELETE_DM_REV');
1454
1455 END IF;
1456
1457 IF ( l_cb_flag = 'Y' )
1458 THEN
1459 p_return_status := FND_API.G_RET_STS_ERROR;
1460
1461 arp_trx_validate.Add_To_Error_List(
1462 p_mode => 'PL/SQL',
1463 p_error_count => l_dummy,
1464 p_customer_trx_id => p_trx_rec.customer_trx_id,
1465 p_trx_number => p_trx_rec.trx_number,
1466 p_line_number => null,
1467 p_other_line_number => null,
1468 p_message_name => 'AR_TAPI_CANT_DELETE_CB');
1469
1470 END IF;
1471
1472 IF ( l_posted_flag = 'Y' )
1473 THEN
1474 p_return_status := FND_API.G_RET_STS_ERROR;
1475
1476 arp_trx_validate.Add_To_Error_List(
1477 p_mode => 'PL/SQL',
1478 p_error_count => l_dummy,
1479 p_customer_trx_id => p_trx_rec.customer_trx_id,
1480 p_trx_number => p_trx_rec.trx_number,
1481 p_line_number => null,
1482 p_other_line_number => null,
1483 p_message_name =>
1484 'AR_ALL_CANT_DELETE_IF_POSTED');
1485
1486 END IF;
1487
1488 IF ( l_activity_flag = 'Y' )
1489 THEN
1490 p_return_status := FND_API.G_RET_STS_ERROR;
1491
1492 arp_trx_validate.Add_To_Error_List(
1493 p_mode => 'PL/SQL',
1494 p_error_count => l_dummy,
1495 p_customer_trx_id => p_trx_rec.customer_trx_id,
1496 p_trx_number => p_trx_rec.trx_number,
1497 p_line_number => null,
1498 p_other_line_number => null,
1499 p_message_name =>
1500 'AR_TAPI_CANT_DELETE_ACTIVITY');
1501
1502 END IF;
1503
1504
1505 END IF; -- complete case
1506
1507 END IF;
1508
1509 ar_invoice_utils.debug('Validate_Delete_Transaction()-');
1510
1511 EXCEPTION
1512 WHEN OTHERS THEN
1513 ar_invoice_utils.debug('EXCEPTION: Validate_Delete_Transaction()');
1514 ar_invoice_utils.debug('p_customer_trx_id = ' ||
1515 p_customer_trx_id);
1516
1517 RAISE;
1518
1519 END Validate_Delete_Transaction;
1520 /*===========================================================================+
1521 | PROCEDURE |
1522 | Delete_Trxn_Extn_Details |
1523 | |
1524 | DESCRIPTION |
1525 | This is the main routine that deletes the |
1526 | PAYMENT_TRXN_EXTENSION details of transactions. |
1527 | |
1528 | SCOPE - PUBLIC |
1529 | |
1530 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1531 | IBY_FNDCPT_TRXN_PUB.delete_transaction_extension |
1532 | |
1533 | ARGUMENTS : IN: |
1534 | p_trx_rec |
1535 | OUT: |
1536 | p_return_status |
1537 | |
1538 | RETURNS : NONE |
1539 | |
1540 | NOTES |
1541 | |
1542 | MODIFICATION HISTORY |
1543 | mpsingh 20-jun-2008 Created |
1544 | |
1545 +===========================================================================*/
1546
1547 PROCEDURE Delete_Trxn_Extn_Details(p_trx_rec IN ra_customer_trx%rowtype,
1548 p_return_status OUT NOCOPY VARCHAR2
1549 ) IS
1550
1551 l_payer_rec IBY_FNDCPT_COMMON_PUB.payercontext_rec_type;
1552 l_trxn_attribs_rec IBY_FNDCPT_TRXN_PUB.trxnextension_rec_type;
1553 l_response IBY_FNDCPT_COMMON_PUB.result_rec_type;
1554 l_payment_channel ar_receipt_methods.payment_channel_code%type;
1555 x_msg_count NUMBER;
1556 x_msg_data VARCHAR2(240);
1557 x_return_status VARCHAR2(240);
1558 l_payment_trxn_extn_id ra_customer_trx.PAYMENT_TRXN_EXTENSION_ID%TYPE;
1559 l_fnd_api_constants_rec ar_bills_main.fnd_api_constants_type := ar_bills_main.get_fnd_api_constants_rec;
1560 Begin
1561 ar_invoice_utils.debug('AR_INVOICE_API_PUB.Delete_Trxn_Extn_Details()+ ');
1562
1563 p_return_status := FND_API.G_RET_STS_SUCCESS;
1564
1565
1566 x_msg_count := NULL;
1567 x_msg_data := NULL;
1568 x_return_status := l_fnd_api_constants_rec.G_RET_STS_SUCCESS;
1569 l_payer_rec.party_id := arp_trx_defaults_3.get_party_Id(p_trx_rec.BILL_TO_CUSTOMER_ID);
1570 l_payer_rec.payment_function := 'CUSTOMER_PAYMENT';
1571 l_payer_rec.org_type := 'OPERATING_UNIT';
1572 l_payer_rec.cust_account_id := p_trx_rec.BILL_TO_CUSTOMER_ID;
1573 l_payer_rec.org_id := p_trx_rec.ORG_ID;
1574 l_payer_rec.account_site_id := p_trx_rec.BILL_TO_SITE_USE_ID;
1575 l_payment_trxn_extn_id := p_trx_rec.PAYMENT_TRXN_EXTENSION_ID;
1576
1577 /*-------------------------+
1578 | Call the IBY API |
1579 +-------------------------*/
1580 ar_invoice_utils.debug('Call TO IBY API ()+ ');
1581
1582
1583 IBY_FNDCPT_TRXN_PUB.delete_transaction_extension(
1584 p_api_version => 1.0,
1585 p_init_msg_list => AR_BILLS_MAIN.GTRUE,
1586 p_commit => AR_BILLS_MAIN.GFALSE,
1587 x_return_status => x_return_status,
1588 x_msg_count => x_msg_count,
1589 x_msg_data => x_msg_data,
1590 p_payer => l_payer_rec,
1591 p_payer_equivalency => 'UPWARD',
1592 p_entity_id => l_payment_trxn_extn_id,
1593 x_response => l_response);
1594
1595
1596
1597 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1598 THEN
1599 ar_invoice_utils.debug('Payment_Trxn_Extension_Id : ' || l_payment_trxn_extn_id);
1600 Else
1601 ar_invoice_utils.debug('Errors Reported by IBY API:Delete Transaction Extension ');
1602 p_return_status := FND_API.G_RET_STS_ERROR;
1603 END IF;
1604 EXCEPTION
1605 WHEN OTHERS THEN
1606 ar_invoice_utils.debug('exception in AR_INVOICE_API_PUB.Delete_Trxn_Extn_Detail ');
1607 p_return_status := FND_API.G_RET_STS_ERROR;
1608 END Delete_Trxn_Extn_Details;
1609
1610
1611 /*===========================================================================+
1612 | PROCEDURE |
1613 | Delete_Transaction |
1614 | |
1615 | DESCRIPTION |
1616 | This is the main routine that deletes the transactions. |
1617 | It is called once for each transaction to be deleted. |
1618 | |
1619 | SCOPE - PUBLIC |
1620 | |
1621 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1622 | ar_transaction_val2_pub.Validate_Delete_Transaction |
1623 | arp_process_header.update_header |
1624 | arp_process_header_post_commit.post_commit |
1625 | arp_trx_validate.Add_To_Error_List |
1626 | arp_process_header.delete_header |
1627 | arp_util.disable_debug |
1628 | arp_util.enable_debug |
1629 | fnd_api.compatible_api_call |
1630 | fnd_api.g_exc_unexpected_error |
1631 | fnd_api.g_ret_sts_error |
1632 | fnd_api.g_ret_sts_error |
1633 | fnd_api.g_ret_sts_success |
1634 | fnd_api.to_boolean |
1635 | fnd_msg_pub.check_msg_level |
1636 | fnd_msg_pub.count_and_get |
1637 | fnd_msg_pub.initialize |
1638 | |
1639 | ARGUMENTS : IN: |
1640 | p_api_name |
1641 | p_api_version |
1642 | p_init_msg_list |
1643 | p_commit |
1644 | p_validation_level |
1645 | p_customer_trx_id |
1646 | OUT: |
1647 | p_return_status |
1648 | IN/ OUT: |
1649 | p_msg_count |
1650 | p_msg_data |
1651 | p_errors |
1652 | |
1653 | RETURNS : NONE |
1654 | |
1655 | NOTES |
1656 | |
1657 | MODIFICATION HISTORY |
1658 | mpsingh 20-jun-2008 Created |
1659 | |
1660 +===========================================================================*/
1661
1662 PROCEDURE Delete_Transaction(
1663 p_api_name IN varchar2,
1664 p_api_version IN number,
1665 p_init_msg_list IN varchar2 := FND_API.G_FALSE,
1666 p_commit IN varchar2 := FND_API.G_FALSE,
1667 p_validation_level IN varchar2 := FND_API.G_VALID_LEVEL_FULL,
1668 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
1669 p_return_status OUT NOCOPY varchar2,
1670 p_msg_count IN OUT NOCOPY NUMBER,
1671 p_msg_data IN OUT NOCOPY varchar2,
1672 p_errors IN OUT NOCOPY arp_trx_validate.Message_Tbl_Type
1673 ) IS
1674
1675 l_api_name CONSTANT VARCHAR2(20) := 'Delete_Transaction';
1676 l_api_version CONSTANT NUMBER := 1.0;
1677 l_message VARCHAR2(1000);
1678 l_return_status VARCHAR2(10);
1679 l_trx_rec ra_customer_trx%rowtype;
1680 l_type_rec ra_cust_trx_types%rowtype;
1681 l_commitment_rec arp_process_commitment.commitment_rec_type;
1682 l_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
1683 l_amount ra_cust_trx_line_gl_dist.amount%type;
1684 l_dummy BINARY_INTEGER;
1685 l_update_status VARCHAR2(50) := 'OK';
1686 l_delete_status VARCHAR2(50) := 'OK';
1687 l_validation_status VARCHAR2(10);
1688 l_delete_pmt_ext_status VARCHAR2(10);
1689
1690 PROCEDURE Display_Parameters IS
1691 BEGIN
1692 ar_invoice_utils.debug('p_api_name = ' || p_api_name);
1693 ar_invoice_utils.debug('p_api_version = ' || p_api_version);
1694 ar_invoice_utils.debug('p_init_msg_list = ' || p_init_msg_list);
1695 ar_invoice_utils.debug('p_commit = ' || p_commit);
1696 ar_invoice_utils.debug('p_validation_level = ' || p_validation_level);
1697 ar_invoice_utils.debug('p_customer_trx_id = ' || p_customer_trx_id);
1698 END;
1699
1700 BEGIN
1701
1702
1703 /*------------------------------------+
1704 | Standard start of API savepoint |
1705 +------------------------------------*/
1706
1707 SAVEPOINT Delete_Transaction_Pub;
1708
1709 /*--------------------------------------------------+
1710 | Standard call to check for call compatibility |
1711 +--------------------------------------------------*/
1712
1713 IF NOT FND_API.Compatible_API_Call(
1714 l_api_version,
1715 p_api_version,
1716 l_api_name,
1717 G_PKG_NAME
1718 )
1719 THEN
1720 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1721 END IF;
1722
1723 /*--------------------------------------------------------------+
1724 | Initialize message list if p_init_msg_list is set to TRUE |
1725 +--------------------------------------------------------------*/
1726
1727 IF FND_API.to_Boolean( p_init_msg_list )
1728 THEN
1729 FND_MSG_PUB.initialize;
1730 arp_trx_validate.pg_message_tbl.DELETE;
1731 END IF;
1732
1733 /*-----------------------------------------+
1734 | Initialize return status to SUCCESS |
1735 +-----------------------------------------*/
1736
1737 l_return_status := FND_API.G_RET_STS_SUCCESS;
1738
1739 /*---------------------------------------------+
1740 | ========== Start of API Body ========== |
1741 +---------------------------------------------*/
1742
1743 ar_invoice_utils.debug('AR_INVOICE_API_PUB.Delete_Transaction(+)' );
1744
1745
1746 Validate_Delete_Transaction(
1747 p_customer_trx_id,
1748 l_trx_rec,
1749 l_type_rec,
1750 l_validation_status
1751 );
1752
1753 IF ( l_validation_status = FND_API.G_RET_STS_SUCCESS )
1754 THEN
1755 IF ( l_trx_rec.complete_flag = 'Y' )
1756 THEN
1757
1758 IF pg_debug = 'Y'
1759 THEN
1760 ar_invoice_utils.debug('Making the transaction incomplete ' );
1761 END IF;
1762
1763 /*-------------------------------------------+
1764 | Try to make the transaction incomplete. |
1765 +-------------------------------------------*/
1766
1767 SELECT trunc(gl_date),
1768 amount
1769 INTO l_gl_date,
1770 l_amount
1771 FROM ra_cust_trx_line_gl_dist
1772 WHERE customer_trx_id = p_customer_trx_id
1773 AND account_class = 'REC'
1774 AND latest_rec_flag = 'Y';
1775
1776 IF pg_debug = 'Y'
1777 THEN
1778 ar_invoice_utils.debug ('. gl_date = ' || TO_CHAR(l_gl_date, 'DD-MON-YYYY'));
1779 END IF;
1780
1781 IF pg_debug = 'Y'
1782 THEN
1783 ar_invoice_utils.debug ('. amount = ' ||TO_CHAR(l_amount));
1784 END IF;
1785
1786 l_trx_rec.complete_flag := 'N';
1787
1788 arp_process_header.update_header(
1789 p_api_name,
1790 p_api_version,
1791 l_trx_rec,
1792 p_customer_trx_id,
1793 l_amount,
1794 l_type_rec.type,
1795 l_gl_date,
1796 l_trx_rec.initial_customer_trx_id,
1797 l_commitment_rec,
1798 l_type_rec.accounting_affect_flag,
1799 'Y',
1800 FALSE,
1801 FALSE,
1802 NULL,
1803 NULL,
1804 l_update_status);
1805
1806
1807 /*-----------------------+
1808 | Do postcommit logic |
1809 +-----------------------*/
1810
1811 IF pg_debug = 'Y'
1812 THEN
1813 ar_invoice_utils.debug('post_commit()+ ');
1814 END IF;
1815
1816 arp_process_header_post_commit.post_commit(
1817 'ARTPTRXB',
1818 p_api_version,
1819 p_customer_trx_id,
1820 l_trx_rec.previous_customer_trx_id,
1821 'N',
1822 l_type_rec.accounting_affect_flag,
1823 NULL,
1824 l_type_rec.creation_sign,
1825 l_type_rec.allow_overapplication_flag,
1826 l_type_rec.natural_application_only_flag,
1827 NULL,
1828 'PL/SQL'
1829 );
1830
1831
1832 IF pg_debug = 'Y'
1833 THEN
1834 ar_invoice_utils.debug('post_commit()- ');
1835 END IF;
1836
1837 END IF; -- end complete_flag = 'Y' case
1838
1839 /*-----------------------------------------------------+
1840 | Delete the transaction if no errors have occurred |
1841 | in the previous steps. |
1842 +-----------------------------------------------------*/
1843
1844 IF pg_debug = 'Y'
1845 THEN
1846 ar_invoice_utils.debug ('. update_status = ' || l_update_status);
1847 END IF;
1848
1849 IF (
1850 l_return_status = FND_API.G_RET_STS_SUCCESS
1851 AND l_update_status = 'OK'
1852 )
1853 THEN
1854 IF pg_debug = 'Y'
1855 THEN
1856 ar_invoice_utils.debug('Deleting the transaction');
1857 END IF;
1858
1859
1860 IF l_trx_rec.payment_trxn_extension_id IS NOT NULL THEN
1861 Delete_Trxn_Extn_Details(l_trx_rec,
1862 l_delete_pmt_ext_status);
1863 ELSE
1864 l_delete_pmt_ext_status := FND_API.G_RET_STS_SUCCESS;
1865 END IF;
1866
1867 IF l_delete_pmt_ext_status = FND_API.G_RET_STS_SUCCESS THEN
1868
1869 arp_process_header.delete_header(
1870 p_api_name,
1871 p_api_version,
1872 p_customer_trx_id,
1873 l_type_rec.type,
1874 l_delete_status);
1875 END IF;
1876
1877 END IF;
1878
1879 END IF; -- validation was successfull case
1880
1881 /*-------------------------------------------------------------------+
1882 | Get any messages that have been put on the regular message stack |
1883 | and add them to the error list. |
1884 +-------------------------------------------------------------------*/
1885
1886 l_message := fnd_message.get;
1887
1888 WHILE l_message IS NOT NULL LOOP
1889
1890 arp_trx_validate.Add_To_Error_List(
1891 p_mode => 'PL/SQL',
1892 P_error_count => l_dummy,
1893 p_customer_trx_id => null,
1894 p_trx_number => null,
1895 p_line_number => null,
1896 p_other_line_number => null,
1897 p_message_name => 'GENERIC_MESSAGE',
1898 p_token_name_1 => 'GENERIC_TEXT',
1899 p_token_1 => l_message );
1900
1901 l_message := fnd_message.get;
1902
1903 END LOOP;
1904
1905
1906 /*-------------------------------------------+
1907 | ========== End of API Body ========== |
1908 +-------------------------------------------*/
1909
1910 p_return_status := l_return_status;
1911
1912 /*---------------------------------------------------+
1913 | Get message count and if 1, return message data |
1914 +---------------------------------------------------*/
1915
1916 FND_MSG_PUB.Count_And_Get(
1917 p_count => p_msg_count,
1918 p_data => p_msg_data
1919 );
1920
1921
1922 /*------------------------------------------------------------+
1923 | If any errors - including validation failures - occurred, |
1924 | rollback any changes and return an error status. |
1925 +------------------------------------------------------------*/
1926
1927 IF (
1928 NVL( arp_trx_validate.pg_message_tbl.COUNT, 0) > 0
1929 OR l_update_status <> 'OK'
1930 OR l_delete_status <> 'OK'
1931 OR l_delete_pmt_ext_status <> FND_API.G_RET_STS_SUCCESS
1932 OR l_return_status <> FND_API.G_RET_STS_SUCCESS
1933 )
1934 THEN
1935
1936 p_errors := arp_trx_validate.pg_message_tbl;
1937
1938 ROLLBACK TO Delete_Transaction_PUB;
1939
1940 p_return_status := FND_API.G_RET_STS_ERROR ;
1941
1942 IF pg_debug = 'Y'
1943 THEN
1944 ar_invoice_utils.debug ('Error(s) occurred. Rolling back and setting status to ERROR');
1945 ar_invoice_utils.debug ('Number Of Messages: ' || TO_CHAR( arp_trx_validate.pg_message_tbl.COUNT) );
1946 END IF;
1947
1948
1949
1950 END IF;
1951
1952 /*--------------------------------+
1953 | Standard check of p_commit |
1954 +--------------------------------*/
1955
1956 IF FND_API.To_Boolean( p_commit )
1957 THEN ar_invoice_utils.debug('committing');
1958 Commit;
1959 END IF;
1960
1961 ar_invoice_utils.debug('AR_INVOICE_API_PUB.Delete_Transaction(-)' );
1962
1963
1964 EXCEPTION
1965 WHEN NO_DATA_FOUND THEN
1966
1967 ROLLBACK TO Delete_Transaction_PUB;
1968 p_return_status := FND_API.G_RET_STS_ERROR;
1969 Display_Parameters;
1970
1971 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1972 p_data => p_msg_data
1973 );
1974
1975 WHEN FND_API.G_EXC_ERROR THEN
1976 ar_invoice_utils.debug(SQLCODE);
1977 ar_invoice_utils.debug(SQLERRM);
1978
1979
1980 ROLLBACK TO Delete_Transaction_PUB;
1981 p_return_status := FND_API.G_RET_STS_ERROR ;
1982 Display_Parameters;
1983
1984 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1985 p_data => p_msg_data
1986 );
1987
1988 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1989
1990 ar_invoice_utils.debug(SQLCODE);
1991 ar_invoice_utils.debug(SQLERRM);
1992 ROLLBACK TO Delete_Transaction_PUB;
1993 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1994 Display_Parameters;
1995
1996 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1997 p_data => p_msg_data
1998 );
1999
2000 WHEN OTHERS THEN
2001 ar_invoice_utils.debug(SQLCODE);
2002 ar_invoice_utils.debug(SQLERRM);
2003 /*-------------------------------------------------------+
2004 | Handle application errors that result from trapable |
2005 | error conditions. The error messages have already |
2006 | been put on the error stack. |
2007 +-------------------------------------------------------*/
2008
2009 IF (SQLCODE = -20001)
2010 THEN
2011 p_errors := arp_trx_validate.pg_message_tbl;
2012
2013 ROLLBACK TO Delete_Transaction_PUB;
2014
2015 p_return_status := FND_API.G_RET_STS_ERROR ;
2016
2017 ar_invoice_utils.debug('Completion validation error(s) occurred. ' ||
2018 'Rolling back and setting status to ERROR');
2019
2020 RETURN;
2021
2022 ELSE NULL;
2023 END IF;
2024
2025 ROLLBACK TO Delete_Transaction_PUB;
2026
2027 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2028 Display_Parameters;
2029
2030 IF FND_MSG_PUB.Check_Msg_Level
2031 THEN
2032 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
2033 l_api_name
2034 );
2035 END IF;
2036
2037 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
2038 p_data => p_msg_data
2039 );
2040
2041
2042 END Delete_Transaction;
2043
2044
2045 -- Bug 7194381 End
2046
2047 -- Bug12668756
2048 PROCEDURE GET_TRXN_LINE_BALANCE(
2049 p_api_version IN NUMBER,
2050 p_customer_trx_id IN NUMBER,
2051 p_interface_line_attribute6 IN ra_customer_trx_lines.interface_line_attribute6%type DEFAULT NULL,
2052 x_amount_due_remaining OUT NOCOPY NUMBER,
2053 x_acctd_amount_due_remaining OUT NOCOPY NUMBER,
2054 x_return_status OUT NOCOPY VARCHAR2,
2055 x_msg_count OUT NOCOPY NUMBER,
2056 x_msg_data OUT NOCOPY VARCHAR2) IS
2057
2058 l_count NUMBER;
2059
2060 BEGIN
2061 IF pg_debug = 'Y' THEN
2062 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.GET_TRXN_LINE_BALANCE (+)' );
2063 ar_invoice_utils.debug ('Customer_trx_id: ' || p_customer_trx_id);
2064 ar_invoice_utils.debug ('interface_line_attribute6: ' || p_interface_line_attribute6);
2065 END IF;
2066
2067 x_return_status := FND_API.G_RET_STS_SUCCESS;
2068
2069 IF p_interface_line_attribute6 IS NULL THEN
2070 BEGIN
2071 SELECT count(1)
2072 INTO l_count
2073 FROM RA_CUSTOMER_TRX
2074 WHERE customer_trx_id = p_customer_trx_id;
2075
2076 IF l_count = 0 THEN
2077 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2078 x_msg_data := 'There is no record with this customer_trx_id.';
2079 return;
2080 END IF;
2081
2082 SELECT SUM(NVL(PS.AMOUNT_DUE_REMAINING, 0 )),
2083 SUM(NVL(PS.ACCTD_AMOUNT_DUE_REMAINING, 0 ))
2084 INTO x_amount_due_remaining,
2085 x_acctd_amount_due_remaining
2086 FROM ra_cust_Trx_types ctt,
2087 ra_customer_trx ct,
2088 ar_payment_schedules ps
2089 WHERE ctt.cust_Trx_type_id = ct.cust_trx_type_id
2090 AND ct.customer_trx_id = ps.customer_trx_id
2091 AND ctt.ACCOUNTING_AFFECT_FLAG = 'Y'
2092 AND ct.customer_trx_id = p_customer_trx_id;
2093 EXCEPTION
2094 WHEN OTHERS THEN
2095 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2096 x_msg_data := 'Error in AR_INVOICE_API_PUB.GET_TRXN_LINE_BALANCE: '||sqlerrm;
2097 return;
2098 END;
2099
2100 ELSE
2101 BEGIN
2102 SELECT count(1)
2103 INTO l_count
2104 FROM RA_CUSTOMER_TRX_LINES
2105 WHERE customer_trx_id = p_customer_trx_id
2106 AND INTERFACE_LINE_ATTRIBUTE6 = p_interface_line_attribute6;
2107
2108 IF l_count = 0 THEN
2109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2110 x_msg_data := 'There is no record with this customer_trx_id and interface_line_attribute6.';
2111 return;
2112 END IF;
2113
2114 SELECT SUM(NVL(AMOUNT_DUE_REMAINING, 0 )),
2115 SUM(NVL(ACCTD_AMOUNT_DUE_REMAINING, 0 ))
2116 INTO x_amount_due_remaining,
2117 x_acctd_amount_due_remaining
2118 FROM ra_batch_sources ra,
2119 ra_customer_trx ct,
2120 ra_customer_trx_lines ctl
2121 WHERE ra.batch_source_id = ct.batch_source_id
2122 AND ct.customer_trx_id = ctl.customer_trx_id
2123 AND nvl(gen_line_level_bal_flag,'N') = 'Y'
2124 AND ctl.line_type = 'LINE'
2125 AND ct.customer_trx_id = p_customer_trx_id
2126 and ctl.INTERFACE_LINE_ATTRIBUTE6 = p_interface_line_attribute6;
2127 EXCEPTION
2128 WHEN OTHERS THEN
2129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2130 x_msg_data := 'Error in AR_INVOICE_API_PUB.GET_TRXN_LINE_BALANCE: '||sqlerrm;
2131 return;
2132 END;
2133 END IF;
2134
2135 IF pg_debug = 'Y' THEN
2136 ar_invoice_utils.debug ('AR_INVOICE_API_PUB.GET_TRXN_LINE_BALANCE (-)' );
2137 END IF;
2138 END;
2139
2140
2141 BEGIN
2142 g_one_time_init_org := -98;
2143 END AR_INVOICE_API_PUB;