[Home] [Help]
PACKAGE BODY: APPS.FUN_AR_BATCH_TRANSFER
Source
1 PACKAGE BODY FUN_AR_BATCH_TRANSFER AS
2 /* $Header: funartrb.pls 120.30 2011/08/12 08:09:12 srampure ship $ */
3
4 FUNCTION has_valid_conversion_rate (
5 p_from_currency IN varchar2,
6 p_to_currency IN varchar2,
7 p_exchange_type IN varchar2,
8 p_exchange_date IN date) RETURN number
9 IS
10 l_has_rate number;
11 BEGIN
12 IF (p_from_currency = p_to_currency) THEN
13 RETURN 1;
14 END IF;
15
16 SELECT COUNT(conversion_rate) INTO l_has_rate
17 FROM gl_daily_rates
18 WHERE from_currency = p_from_currency AND
19 to_currency = p_to_currency AND
20 conversion_type = p_exchange_type AND
21 conversion_date = p_exchange_date;
22
23 IF (l_has_rate = 0) THEN
24 RETURN 0;
25 END IF;
26 RETURN 1;
27 END has_valid_conversion_rate;
28
29 Procedure ar_batch_transfer (errbuf OUT NOCOPY VARCHAR2,
30 retcode OUT NOCOPY NUMBER,
31 p_org_id IN VARCHAR2 DEFAULT NULL,
32 p_le_id IN VARCHAR2 DEFAULT NULL,
33 p_date_low IN VARCHAR2 DEFAULT NULL,
34 p_date_high IN VARCHAR2 DEFAULT NULL,
35 p_run_autoinvoice_import IN VARCHAR2 DEFAULT 'N'
36 ) is
37
38
39 -- AR Batch Transfer Program
40
41 l_date_low date;
42 l_date_high date;
43 l_initiator_id number;
44 l_le_id number;
45 l_ledger_id number;
46 l_recipient_id number;
47 l_ap_le_id number;
48 l_trx_type_id number;
49 l_ou_id number;
50 l_ap_ou_id number;
51 l_error number;
52 l_memo_line_name varchar2(2000);
53 l_ar_trx_type_name varchar2(2000);
54 l_memo_line_id number;
55 l_ar_trx_type_id number;
56 l_default_term_id number;
57 l_term_id number;
58 l_ar_period_count number;
59 l_batch_id number;
60 l_trx_id number;
61 l_count number := 0;
62 l_line AR_INTERFACE_LINE;
63 l_dist_line AR_INTERFACE_DIST_LINE;
64 l_success boolean;
65 l_customer_id number;
66 l_address_id number;
67 l_site_use_id number;
68 x_msg_data varchar2(1000);
69 l_return_status varchar2(1);
70 l_message_count number;
71 l_message_data varchar2(1000);
72 l_counter number;
73 l_org_name varchar2(240);
74 l_le_name varchar2(240);
75 l_batch_num varchar2(20);
76 l_trx_num varchar2(15);
77 l_ledger_currency_code varchar2(15);
78 l_request_id number;
79 Request_Submission_Failure EXCEPTION;
80 TYPE ORG_ID_TAB_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
81 org_id_table ORG_ID_TAB_TYPE;
82 l_src_name varchar2(50);
83 l_previous_org_id number;
84 l_run_autoinvoice_import varchar2(3);
85
86
87 -- Cursor to retrieve the line information
88 CURSOR c_line (p_batch_id IN NUMBER,
89 p_trx_id IN NUMBER) IS
90 SELECT
91 decode(ftl.init_amount_cr,0, ftl.init_amount_dr,
92 NULL, ftl.init_amount_dr,
93 (ftl.init_amount_cr * (-1))),
94 ftl.line_id
95 FROM FUN_TRX_LINES ftl
96 WHERE p_trx_id = ftl.trx_id;
97
98 -- cursor to retrieve the initiator and recipient party / LE ID
99
100 cursor c_info IS
101 SELECT ftb.batch_id,
102 ftb.batch_number,
103 fth.trx_id,
104 fth.trx_number,
105 ftb.initiator_id,
106 ftb.from_le_id,
107 ftb.from_ledger_id,
108 fth.recipient_id,
109 fth.to_le_id,
110 ftb.trx_type_id,
111 ftb.exchange_rate_type,
112 ftb.currency_code,
113 ledgers.currency_code,
114 ftb.description,
115 ftb.gl_date,
116 ftb.batch_id,
117 fth.trx_id,
118 ftb.from_ledger_id,
119 ftb.batch_date
120
121 FROM FUN_TRX_BATCHES ftb,
122 FUN_TRX_HEADERS fth,
123 GL_LEDGERS ledgers
124
125 WHERE fth.batch_id= ftb.batch_id
126 AND fth.status='APPROVED'
127 AND ledgers.ledger_id = ftb.from_ledger_id
128 AND trunc(ftb.gl_date) between trunc(nvl(l_date_low,ftb.gl_date-1))
129 and trunc(nvl(l_date_high, ftb.gl_date+1))
130 AND nvl(p_org_id,1) = nvl2(p_org_id,fun_tca_pkg.get_ou_id(ftb.initiator_id),1)
131 AND ftb.from_le_id = nvl(p_le_id,ftb.from_le_id)
132 AND fth.invoice_flag = 'Y'
133 ORDER BY ftb.initiator_id;
134
135 -- cursor to retrieve the distribution information
136
137 CURSOR c_dist (p_trx_id IN NUMBER) IS
138 SELECT
139 DECODE(FDL.dist_type_flag, 'L',
140 decode(fdl.amount_cr,
141 0, fdl.amount_dr * (-1),
142 NULL, fdl.amount_dr * (-1),
143 fdl.amount_cr),
144 'R', NULL,
145 NULL),
146 DECODE(FDL.dist_type_flag, 'L', NULL,
147 'R', 100,
148 NULL),
149 DECODE(FDL.dist_type_flag, 'R', 'REC',
150 'L', 'REV',
151 NULL),
152 fdl.ccid,
153 fth.batch_id,
154 fth.trx_id,
155 ftl.line_id
156 FROM FUN_TRX_HEADERS fth,
157 FUN_TRX_LINES ftl,
158 FUN_DIST_LINES fdl
159 WHERE ftl.trx_id=fth.trx_id
160 AND fth.trx_id = p_trx_id
161 AND ftl.line_id=fdl.line_id
162 AND fdl.party_type_flag='I';
163
164 CURSOR period_open_csr (p_trx_date DATE,
165 p_ledger_id NUMBER) IS
166 SELECT COUNT(*)
167 FROM gl_period_statuses glps
168 WHERE TRUNC(p_trx_date) BETWEEN glps.start_date AND glps.end_date
169 AND glps.application_id = 222
170 AND glps.set_of_books_id = p_ledger_id
171 AND glps.adjustment_period_flag <> 'Y'
172 AND glps.closing_status IN ('O','F');
173
174 CURSOR ou_valid_csr (p_ou_id NUMBER,
175 p_trx_date DATE) IS
176 SELECT count(*)
177 FROM hr_operating_units ou
178 WHERE organization_id = p_ou_id
179 AND date_from <= p_trx_date
180 AND NVL(date_to, p_trx_date) >= p_trx_date;
181
182 --Bug: 9052792. Cursor to get the term_id from site level.
183
184 CURSOR c_site_term(p_site_use_id NUMBER) IS
185 select PAYMENT_TERM_ID
186 from HZ_CUST_SITE_USES_ALL
187 where site_use_code = 'BILL_TO'
188 and site_use_id = p_site_use_id;
189
190 --Bug: 9052792. Cursor to get the term_id from customer account level.
191
192 CURSOR c_account_term(p_cust_acct_id NUMBER) IS
193 select STANDARD_TERMS
194 from HZ_CUSTOMER_PROFILES
195 where cust_account_id = p_cust_acct_id;
196 BEGIN
197
198 l_error := 1;
199 l_counter := 0;
200 l_date_low := TRUNC(fnd_date.canonical_to_date(p_date_low));
201 l_date_high:= TRUNC(fnd_date.canonical_to_date(p_date_high));
202 l_previous_org_id := 0;
203 l_ou_id := 0;
204
205 IF (p_run_autoinvoice_import = 'Y') THEN
206 l_run_autoinvoice_import := 'Yes';
207 ELSE
208 l_run_autoinvoice_import := 'No';
209 END IF;
210
211 IF (p_org_id is not null) THEN
212 select hr.name into l_org_name from hr_operating_units hr
213 where hr.organization_id = p_org_id;
214 END IF;
215
216 IF (p_le_id is not null) THEN
217 select xle.name into l_le_name from xle_entity_profiles xle
218 where xle.legal_entity_id = p_le_id;
219 END IF;
220
221 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Transfer Intercompany Transactions to Receivables Report Date: '||to_char(sysdate,'DD-MON-YYYY HH:MM'));
222 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,2 );
223 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Operating Unit: ' || l_org_name);
224 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Legal Entity: ' || l_le_name);
225 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' GL Date From: ' || to_char(l_date_low, 'DD-MON-YYYY'));
226 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' GL Date To: ' || to_char(l_date_high,'DD-MON-YYYY'));
227 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Submit AR Auto Invoice: ' || l_run_autoinvoice_import);
228 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,2 );
229 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Batch Number Transaction Number Transfer Status' );
230 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'------------ ------------------ ----------------');
231
232
233 open c_info;
234
235 Loop
236
237 fetch c_info into
238 l_batch_id,
239 l_batch_num,
240 l_trx_id,
241 l_trx_num,
242 l_initiator_id,
243 l_le_id,
244 l_ledger_id,
245 l_recipient_id,
246 l_ap_le_id,
247 l_trx_type_id,
248 l_line.conversion_type,
249 l_line.currency_code,
250 l_ledger_currency_code,
251 l_line.description,
252 l_line.gl_date,
253 l_line.interface_line_attribute1,
254 l_line.interface_line_attribute2,
255 l_line.set_of_books_id,
256 l_line.trx_date;
257
258 exit when c_info%NOTFOUND;
259 l_counter := l_counter + 1;
260
261 -- check AR Setup for records
262
263 -- retrieve l_ou_id and l_ap_ou_id
264 l_ou_id := FUN_TCA_PKG.get_ou_id (l_initiator_id);
265 l_ap_ou_id := FUN_TCA_PKG.get_ou_id(l_recipient_id);
266
267 IF (l_ou_id is null) then
268 l_error := 2;
269 fnd_message.set_name('FUN','FUN_XFER_AR_IOU_NULL');
270 END IF;
271
272 IF (l_ap_ou_id is null) then
273 l_error := 2;
274 fnd_message.set_name('FUN','FUN_XFER_AR_ROU_NULL');
275 END IF;
276
277 -- Validate AR OU Id.
278 OPEN ou_valid_csr( l_ou_id, l_line.trx_date);
279 FETCH ou_valid_csr INTO l_count;
280 CLOSE ou_valid_csr;
281 IF l_count < 1
282 THEN
283 l_error := 2;
284 fnd_message.set_name('FUN','FUN_XFER_AR_IOU_NULL');
285 END IF;
286 -- ER:8288979. Passing l_trx_id.
287 -- Retrieve memo line, ar trx type
288 fun_trx_types_pub.Get_Trx_Type_Map(l_ou_id, l_trx_type_id,
289 l_line.trx_date, l_trx_id,
290 l_memo_line_id, l_memo_line_name,
291 l_ar_trx_type_id, l_ar_trx_type_name,
292 l_default_term_id);
293
294 IF (l_memo_line_name IS NULL) OR (l_ar_trx_type_name IS NULL) THEN
295 l_error := 2;
296 fnd_message.set_name('FUN','FUN_XFER_AR_MEMO_NULL');
297 END IF;
298
299
300 -- Check if AR period open
301
302 l_ar_period_count := 0;
303
304 -- Bug 9970500 modified beloe cursor to check for gl_date instead of trx_date
305
306 OPEN period_open_csr(l_line.gl_date,
307 l_ledger_id);
308
309 FETCH period_open_csr INTO l_ar_period_count;
310
311 IF l_ar_period_count < 1 THEN
312 l_error := 2;
313 fnd_message.set_name('FUN','FUN_XFER_AR_PERIOD');
314 END IF;
315
316 CLOSE period_open_csr;
317
318 -- Obtain the customer_id and address_id
319
320
321 -- For customer association, transacting LE is actually the
322 -- recipient LE.
323 l_success :=
324 FUN_TRADING_RELATION.get_customer(
325 p_source => 'INTERCOMPANY',
326 p_trans_le_id => l_ap_le_id,
327 p_tp_le_id => l_le_id,
328 p_trans_org_id => l_ap_ou_id,
329 p_tp_org_id => l_ou_id,
330 p_trans_organization_id => l_recipient_id,
331 p_tp_organization_id => l_initiator_id,
332 x_msg_data => x_msg_data,
333 x_cust_acct_id => l_customer_id,
334 x_cust_acct_site_id => l_address_id,
335 x_site_use_id => l_site_use_id);
336
337 IF (l_success<>true) OR (l_customer_id is NULL) THEN
338 l_error := 2;
339 fnd_message.set_name('FUN','FUN_XFER_AR_CST_ID_NULL');
340 END IF;
341 IF (l_address_id is NULL) THEN
342 l_error := 2;
343 fnd_message.set_name('FUN','FUN_XFER_AR_ADD_ID_NULL');
344 END IF;
345
346 IF (has_valid_conversion_rate(l_line.currency_code,l_ledger_currency_code,l_line.conversion_type,l_line.gl_date)=0) THEN
347 l_error := 2;
348 fnd_message.set_name('FUN', 'FUN_CONV_RATE_NOT_FOUND');
349 END IF;
350 --Bug: 9052792.
351 l_term_id := NULL;
352
353 OPEN c_site_term (l_site_use_id);
354 FETCH c_site_term INTO l_term_id;
355 IF c_site_term%NOTFOUND THEN
356 NULL;
357 END IF;
358 CLOSE c_site_term;
359
360 IF l_term_id IS NULL THEN
361 OPEN c_account_term (l_customer_id);
362 FETCH c_account_term INTO l_term_id;
363 IF c_account_term%NOTFOUND THEN
364 NULL;
365 END IF;
366 CLOSE c_account_term;
367 END IF;
368 --Bug: 9126518
369 IF (l_term_id IS NOT NULL AND l_default_term_id IS NOT NULL) THEN
370 l_default_term_id := l_term_id;
371 END IF;
372
373 -- transfer to RA_INTERFACE_LINES
374
375
376 IF l_error = 1 THEN
377
378 open c_line(l_batch_id, l_trx_id);
379
380 LOOP
381
382 -- Amounts Transferred to AR should be
383 -- Init Trx Amount: 1000 Cr, AR Amount: -1000
384 -- Init Trx Amount: -1000 Cr, AR Amount: 1000
385 -- Init Trx Amount: 1000 Dr, AR Amount: 1000
386 -- Init Trx Amount: -1000 Dr, AR Amount: -1000
387
388 FETCH c_line INTO
389
390 l_line.amount,
391 l_line.interface_line_attribute3;
392
393 EXIT WHEN c_line%NOTFOUND;
394
395 l_line.org_id:= l_ou_id;
396
397 -- Bug 9634573 fetched src name from the table
398
399 SELECT name into l_line.BATCH_SOURCE_NAME FROM
400 RA_BATCH_SOURCES_ALL WHERE BATCH_SOURCE_ID = 22 AND org_id = l_ou_id;
401
402
403 l_line.INTERFACE_LINE_CONTEXT:='INTERNAL_ALLOCATIONS';
404 l_line.LINE_TYPE:='LINE';
405 -- l_line.UOM_NAME :='Each'; -- Bug No: 8291939
406 l_count:=l_count+1;
407
408
409 l_line.orig_system_bill_customer_id:= l_customer_id;
410 l_line.orig_system_bill_address_id := l_address_id;
411
412
413
414 -- Bug: 6788142 Added PRIMARY_SALESREP_ID field in the insert query.
415 -- insert into AR Interface table
416 -- Bug: 7271703 Populating the INTERFACE_LINE_ATTRIBUTE4 with the
417 -- batch number.
418 INSERT INTO RA_INTERFACE_LINES_ALL
419 (
420 AMOUNT,
421 BATCH_SOURCE_NAME,
422 CONVERSION_TYPE,
423 CURRENCY_CODE,
424 CUST_TRX_TYPE_ID,
425 CUST_TRX_TYPE_NAME,
426 DESCRIPTION,
427 GL_DATE,
428 INTERFACE_LINE_ATTRIBUTE1,
429 INTERFACE_LINE_ATTRIBUTE2,
430 INTERFACE_LINE_ATTRIBUTE3,
431 INTERFACE_LINE_ATTRIBUTE4,
432 INTERFACE_LINE_CONTEXT,
433 LINE_TYPE,
434 MEMO_LINE_ID,
435 MEMO_LINE_NAME,
436 ORG_ID,
437 ORIG_SYSTEM_BILL_ADDRESS_ID,
438 ORIG_SYSTEM_BILL_CUSTOMER_ID,
439 SET_OF_BOOKS_ID,
440 TRX_DATE,
441 TAXABLE_FLAG,
442 TERM_ID,
443 LEGAL_ENTITY_ID,
444 SOURCE_EVENT_CLASS_CODE,
445 PRIMARY_SALESREP_ID
446 )
447 VALUES
448 (
449 l_line.AMOUNT,
450 l_line.BATCH_SOURCE_NAME,
451 l_line.CONVERSION_TYPE,
452 l_line.CURRENCY_CODE,
453 l_ar_trx_type_id,
454 l_ar_trx_type_name,
455 NVL(l_line.DESCRIPTION,
456 'Transactions from Global Intercompany'),
457 l_line.GL_DATE,
458 l_line.INTERFACE_LINE_ATTRIBUTE1,
459 l_line.INTERFACE_LINE_ATTRIBUTE2,
460 l_line.INTERFACE_LINE_ATTRIBUTE3,
461 l_batch_num,
462 l_line.INTERFACE_LINE_CONTEXT,
463 l_line.LINE_TYPE,
464 l_memo_line_id,
465 l_memo_line_name,
466 l_line.ORG_ID,
467 l_line.ORIG_SYSTEM_BILL_ADDRESS_ID,
468 l_line.ORIG_SYSTEM_BILL_CUSTOMER_ID,
469 l_line.SET_OF_BOOKS_ID,
470 l_line.TRX_DATE,
471 --Bug 9285035: Changed the value From 'S' to 'Y'
472 --'S' ,
473 'Y' ,
474 l_default_term_id,
475 l_le_id,
476 'INTERCOMPANY_TRX',
477 '-3'
478 );
479
480 -- Bug No. 6788142. Inserting into RA_INTERFACE_SALESCREDITS_ALL table
481
482 INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
483 (
484 INTERFACE_LINE_CONTEXT ,
485 INTERFACE_LINE_ATTRIBUTE1 ,
486 INTERFACE_LINE_ATTRIBUTE2 ,
487 INTERFACE_LINE_ATTRIBUTE3 ,
488 INTERFACE_LINE_ATTRIBUTE4 ,
489 INTERFACE_LINE_ATTRIBUTE5 ,
490 INTERFACE_LINE_ATTRIBUTE6 ,
491 INTERFACE_LINE_ATTRIBUTE7 ,
492 INTERFACE_LINE_ATTRIBUTE8 ,
493 INTERFACE_LINE_ATTRIBUTE9 ,
494 INTERFACE_LINE_ATTRIBUTE10 ,
495 INTERFACE_LINE_ATTRIBUTE11 ,
496 INTERFACE_LINE_ATTRIBUTE12 ,
497 INTERFACE_LINE_ATTRIBUTE13 ,
498 INTERFACE_LINE_ATTRIBUTE14 ,
499 INTERFACE_LINE_ATTRIBUTE15,
500 SALES_CREDIT_PERCENT_SPLIT,
501 SALES_CREDIT_TYPE_ID,
502 SALESREP_ID,
503 ORG_ID
504 )
505 VALUES
506 (
507 l_line.INTERFACE_LINE_CONTEXT,
508 l_line.INTERFACE_LINE_ATTRIBUTE1,
509 l_line.INTERFACE_LINE_ATTRIBUTE2,
510 l_line.INTERFACE_LINE_ATTRIBUTE3,
511 NULL,
512 NULL,
513 NULL,
514 NULL,
515 NULL,
516 NULL,
517 NULL,
518 NULL,
519 NULL,
520 NULL,
521 NULL,
522 NULL,
523 '100',
524 '1',
525 '-3',
526 l_line.ORG_ID
527 );
528
529 IF l_previous_org_id <> l_ou_id THEN
530 ORG_ID_TABLE(org_id_table.count+1) := l_ou_id;
531 l_previous_org_id := l_ou_id;
532 END IF;
533
534 End Loop; --c_line
535 Close C_Line;
536
537
538 -- Insert into the AR distribution table
539
540 -- Amounts Transferred to AR should be
541 -- Ini Dst Amount: 1000 Dr, AR Amount: -1000
542 -- Ini Dst Amount: -1000 Dr, AR Amount: 1000
543 -- Ini Dst Amount: 1000 Cr, AR Amount: 1000
544 -- Ini Dst Amount: -1000 Cr, AR Amount: -1000
545
546 open c_dist(l_trx_id);
547 LOOP
548
549 FETCH c_dist INTO
550 l_dist_line.AMOUNT,
551 l_dist_line.percent,
552 l_dist_line.account_class,
553 l_dist_line.CODE_COMBINATION_ID,
554 l_dist_line.INTERFACE_LINE_ATTRIBUTE1,
555 l_dist_line.INTERFACE_LINE_ATTRIBUTE2,
556 l_dist_line.INTERFACE_LINE_ATTRIBUTE3;
557
558 exit when c_dist%NOTFOUND;
559
560 l_dist_line.ORG_ID :=l_ou_id;
561 l_dist_line.INTERFACE_LINE_CONTEXT:='INTERNAL_ALLOCATIONS';
562
563 -- Insert the value into the distribution table
564
565 INSERT INTO RA_INTERFACE_DISTRIBUTIONS_ALL
566 (
567 ACCOUNT_CLASS,
568 AMOUNT,
569 percent,
570 CODE_COMBINATION_ID,
571 INTERFACE_LINE_ATTRIBUTE1,
572 INTERFACE_LINE_ATTRIBUTE2,
573 INTERFACE_LINE_ATTRIBUTE3,
574 INTERFACE_LINE_ATTRIBUTE4,
575 INTERFACE_LINE_CONTEXT,
576 ORG_ID
577 )
578 VALUES
579 (
580 l_dist_line.ACCOUNT_CLASS,
581 l_dist_line.AMOUNT,
582 l_dist_line.percent,
583 l_dist_line.CODE_COMBINATION_ID,
584 l_dist_line.INTERFACE_LINE_ATTRIBUTE1,
585 l_dist_line.INTERFACE_LINE_ATTRIBUTE2,
586 l_dist_line.INTERFACE_LINE_ATTRIBUTE3,
587 l_batch_num,
588 l_dist_line.INTERFACE_LINE_CONTEXT,
589 l_dist_line.ORG_ID
590 );
591
592 END LOOP; --c_dist
593 CLOSE c_dist;
594
595 fnd_message.set_name('FUN','FUN_XFER_SUCCESS');
596 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(l_batch_num,1,20),20)||rpad(substr(l_trx_num,1,15),20)||fnd_message.get);
597
598
599 -- update transaction status
600
601 FUN_TRX_PVT.update_trx_status(p_api_version =>1.0,
602 x_return_status =>l_return_status,
603 x_msg_count => l_message_count,
604 x_msg_data => l_message_data,
605 p_trx_id => l_trx_id,
606 p_update_status_to => 'XFER_AR');
607
608 -- Handle the API call return
609
610 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
611
612 raise FND_API.G_EXC_ERROR;
613 END IF;
614
615
616 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
617
618 raise FND_API.G_EXC_UNEXPECTED_ERROR;
619 END IF;
620
621 --Bug:12703241. Cancel open workflw activities and the notifications
622
623 FUN_WF_COMMON.wf_abort(l_batch_id, l_trx_id, 'FUNARINT');
624
625 ELSIF l_error=2 THEN
626 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(l_batch_num,1,20),20)||rpad(substr(l_trx_num,1,15),20)||fnd_message.get);
627 END IF; -- l_error
628 l_error := 1;
629
630 End Loop; -- c_info
631
632 close c_info;
633 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,2 );
634 IF (l_counter = 0) THEN
635 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' *****No Data Found*****');
636 ELSE
637 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' *****End Of Report*****');
638 END IF;
639
640 COMMIT;
641
642
643 IF p_run_autoinvoice_import='Y' and org_id_table.count>0 THEN
644 FOR I in org_id_table.First .. org_id_table.last
645 LOOP
646 -- Bug 9634573 fetched src name from the table
647
648 SELECT name into l_src_name FROM
649 RA_BATCH_SOURCES_ALL WHERE BATCH_SOURCE_ID = 22 AND org_id = org_id_table(I);
650
651 FND_REQUEST.set_org_id(org_id_table(I));
652 l_request_id := FND_REQUEST.SUBMIT_REQUEST('AR', 'RAXMTR','','', FALSE,
653 '1',org_id_table(I),22,l_src_name, trunc(sysdate),
654 '','','','','','','','','','','',
655 '','','','','','','','','', 'YES','');
656
657 IF l_request_id <> 0 THEN
658 fnd_file.put_line(fnd_file.log,'AR Auto Invoice Program submitted for Org ID: ' || org_id_table(I)||' with Request id: ' || l_request_id);
659 commit;
660 ELSE
661 RAISE Request_Submission_Failure;
662 END IF;
663 END LOOP;
664 END IF;
665
666
667 EXCEPTION
668 WHEN Request_Submission_Failure THEN
669 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in submitting AutoInovice Import Process');
670 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error:' || sqlcode || sqlerrm);
671 WHEN NO_DATA_FOUND THEN
672 FND_FILE.PUT_LINE(FND_FILE.LOG,'No Data Found');
673 WHEN OTHERS THEN
674 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error:' || sqlcode || sqlerrm);
675 retcode := 2;
676 end;
677
678 END FUN_AR_BATCH_TRANSFER;
679
680