1 PACKAGE BODY FUN_GL_BATCH_TRANSFER AS
2 /* $Header: FUNGLTRB.pls 120.32.12010000.7 2008/10/27 08:24:20 makansal 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 FUNCTION get_conversion_type (
30 p_conversion_type IN VARCHAR2) RETURN VARCHAR2
31 IS
32 l_user_conversion_type GL_DAILY_CONVERSION_TYPES.USER_CONVERSION_TYPE%TYPE;
33 BEGIN
34
35 SELECT USER_CONVERSION_TYPE
36 INTO l_user_conversion_type
37 from GL_DAILY_CONVERSION_TYPES
38 where conversion_type = p_conversion_type;
39
40 return l_user_conversion_type;
41 END get_conversion_type;
42
43 procedure gl_batch_transfer
44 (
45 errbuf OUT NOCOPY VARCHAR2,
46 retcode OUT NOCOPY NUMBER,
47 p_date_low IN varchar2 DEFAULT NULL,
48 p_date_high IN varchar2 DEFAULT NULL,
49 p_ledger_low IN varchar2 DEFAULT NULL,
50 p_ledger_high IN varchar2 DEFAULT NULL,
51 p_le_low IN varchar2 DEFAULT NULL,
52 p_le_high IN varchar2 DEFAULT NULL,
53 p_ic_org_low IN varchar2 DEFAULT NULL,
54 p_ic_org_high IN varchar2 DEFAULT NULL,
55 p_run_journal_import IN varchar2 DEFAULT 'N',
56 p_create_summary_journals IN varchar2 DEFAULT 'N'
57
58 )
59 IS
60 p_request_id number;
61 l_source gl_je_sources_tl.user_je_source_name%TYPE;
62 l_category gl_je_categories_tl.user_je_category_name%TYPE;
63 l_date_low date;
64 l_date_high date;
65 l_init_sysdate date;
66 l_parameter_list WF_PARAMETER_LIST_T :=wf_parameter_list_t();
67 l_event_key VARCHAR2(240);
68 v_interface_run_id number;
69 import_request_id number;
70 l_run_journal_import varchar2(3);
71 l_create_summary_journals varchar2(3);
72 l_cur_select VARCHAR2(1200);
73 l_cur_where VARCHAR2(2300);
74 l_cur_main_query VARCHAR2(3500);
75 -- Bug No. 6894340
76 l_ic_org_low VARCHAR2(360);
77 l_ic_org_high VARCHAR2(360);
78 --journal import cursor to be modified
79
80 cursor c_import(p_concreqid number, p_source VARCHAR2, p_category VARCHAR2) is
81 select distinct ledger_id
82 from gl_interface
83 where user_je_category_name = p_category
84 and user_je_source_name = p_source
85 and request_id=p_concreqid;
86
87
88 -- Please dont remove the trunc around GL date as the date
89 -- currently stores a time component.
90 -- In this case the TRUNC in the query does not affect the performance
91 -- as there is currently no index on gl_date
92 -- Also there are transactions in the system with a time
93 -- component in the gl date. This has been rectified for
94 -- bug 5172718
95 -- But there are still some transactions with GL dates that need
96 -- to be processed.
97 -- If an index is later added on gl_date, a script may be required
98 -- to trunc gl and batch date on all exising records.
99 TYPE c_transfer IS REF CURSOR;
100 c_transfer_obj c_transfer;
101
102 TYPE status IS TABLE OF fun_trx_headers.status%TYPE INDEX BY BINARY_INTEGER;
103 TYPE party_type_flag IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
104 TYPE error_mesg IS TABLE OF fnd_new_messages.message_name%TYPE INDEX BY BINARY_INTEGER;
105 TYPE trx_id IS TABLE OF fun_trx_headers.trx_id%TYPE INDEX BY BINARY_INTEGER;
106 TYPE trx_number IS TABLE OF fun_trx_headers.trx_number%TYPE INDEX BY BINARY_INTEGER;
107 TYPE batch_number IS TABLE OF fun_trx_batches.batch_number%TYPE INDEX BY BINARY_INTEGER;
108 TYPE batch_id IS TABLE OF fun_trx_batches.batch_id%TYPE INDEX BY BINARY_INTEGER; -- Bug 6797385.
109
110
111 l_status_tbl status;
112 l_party_type_flag_tbl party_type_flag;
113 l_error_mesg_tbl error_mesg;
114 l_trx_id_tbl trx_id;
115 l_trx_num trx_number;
116 l_batch_num batch_number;
117 l_batch_id_tbl batch_id; -- Bug 6797385.
118 where_clause VARCHAR2(1000) :='';
119 select_clause VARCHAR2(2500) :='';
120 insert_clause VARCHAR2(800) :='';
121
122 gt_insert_clause VARCHAR2(2500) :='';
123 gt_where_clause VARCHAR2(2500) :='';
124
125 BEGIN
126 p_request_id := FND_GLOBAL.CONC_REQUEST_ID;
127
128 select user_je_source_name into l_source from gl_je_sources_tl where
129 je_source_name = 'Global Intercompany' and language = USERENV('LANG');
130
131 select user_je_category_name into l_category from gl_je_categories_tl where
132 je_category_name = 'Global Intercompany' and language = USERENV('LANG');
133
134 l_date_low := TRUNC(fnd_date.canonical_to_date(p_date_low));
135 l_date_high:= TRUNC(fnd_date.canonical_to_date(p_date_high));
136 --Bug No. 6894340
137 l_ic_org_high:=REPLACE(p_ic_org_high, '''', '''''');
138 l_ic_org_low:=REPLACE(p_ic_org_low, '''', '''''');
139 IF p_create_summary_journals = 'Y' THEN
140 l_create_summary_journals:='Yes';
141 ELSE
142 l_create_summary_journals:='No';
143 END IF;
144
145 IF p_run_journal_import = 'Y' THEN
146 l_run_journal_import:= 'Yes';
147 ELSE
148 l_run_journal_import:= 'No';
149 END IF;
150
151 BEGIN
152 -- Build the query for cursor
153 l_cur_select := 'SELECT
154 trxH.status status,
155 gt.party_type_flag party_type_flag,
156 Nvl(nvl(decode(glps.closing_status,''O'','''',''F'','''', ''GL_PERIOD_NOT_OPEN''),
157 decode(FUN_GL_BATCH_TRANSFER.has_valid_conversion_rate(trxB.currency_code,ledgers.currency_code,
158 trxB.exchange_rate_type,TRUNC(trxB.batch_date)),1,'''',0,''FUN_API_CONV_RATE_NOT_FOUND'')),
159 ''FUN_API_TRX_TRANSFERRED'') error_mesg,
160 gt.trx_id trx_id,
161 trxH.trx_number trx_number,
162 trxB.batch_number batch_number,
163 trxB.batch_id batch_id
164 from
165 fun_trx_batches trxB,
166 fun_trx_headers trxH,
167 gl_periods periods,
168 gl_ledgers ledgers,
169 gl_period_statuses glps,
170 fun_transfers gt';
171
172 l_cur_where := ' WHERE trxH.trx_id = gt.trx_id AND
173 trxB.batch_id = gt.batch_id AND
174 GT.request_id = '''||p_request_id||''' AND
175 ledgers.ledger_id = gt.ledger_id AND
176 periods.period_set_name = ledgers.period_set_name AND
177 TRUNC(trxB.gl_date) BETWEEN periods.start_date
178 AND periods.end_date AND
179 periods.adjustment_period_flag <> ''Y'' AND
180 glps.period_name = periods.period_name AND
181 glps.application_id = 101 AND
182 glps.set_of_books_id = ledgers.ledger_id';
183
184 select sysdate into l_init_sysdate from dual;
185
186 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Transfer Intercompany Transactions to General Ledger Report Date:'||to_char(sysdate,'DD-MON-YYYY HH:MM'));
187 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,2 );
188 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' GL Date From: ' || to_char(l_date_low, 'DD-MON-YYYY'));
189 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' GL Date To: ' || to_char(l_date_high,'DD-MON-YYYY'));
190 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Ledger From: ' || p_ledger_low);
191 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Ledger To: ' || p_ledger_high);
192 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Legal Entity From: ' || p_le_low);
193 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Legal Entity To: ' || p_le_high);
194 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Intercompany Organization From: ' || p_ic_org_low);
195 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Intercompany Organization To: ' || p_ic_org_high);
196 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Run Journal Import: ' || l_run_journal_import);
197 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Create Summary Journals: ' || l_create_summary_journals);
198 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,2 );
199 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Batch Number Transaction Number Transfer Status' );
200 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'------------ ------------------ ----------------');
201
202 /* Insert data into temp table */
203 begin
204 gt_insert_clause :='INSERT INTO fun_transfers(BATCH_ID, trx_id, org_name, org_id, le_id,
205 ledger_id, party_type_flag,
206 request_id, trx_status, description)
207 select gt.BATCH_ID, gt.TRX_ID, P.PARTY_NAME,
208 GT.PARTY_ID, GT.LE_ID, gt.LEDGER_ID, gt.party_type_flag,
209 gt.p_request_id,
210 gt.STATUS, gt.description
211 from fun_trx_batches trxb, gl_ledgers ledgers,
212 HZ_PARTIES P,
213 (
214 SELECT TB.BATCH_ID , TH.INITIATOR_ID party_id, TH.TRX_ID, ''I''
215 party_type_flag, '''||p_request_id||''' p_request_id, tB.description Description,
216 TB.FROM_LE_ID LE_ID, TH.STATUS, tB.from_ledger_id LEDGER_ID
217 FROM FUN_TRX_HEADERS TH, FUN_TRX_BATCHES TB
218 WHERE TH.INVOICE_FLAG = ''N''
219 AND NOT EXISTS ( SELECT TRX_ID FROM
220 FUN_TRANSFERS FT WHERE FT.TRX_ID = TH.TRX_ID AND
221 FT.PARTY_TYPE_FLAG = ''I'')
222 AND TH.STATUS IN (''APPROVED'',''XFER_RECI_GL'')
223 AND TH.BATCH_ID = TB.BATCH_ID
224 UNION ALL
225 SELECT TB.BATCH_ID, TH.RECIPIENT_ID party_id, TH.TRX_ID, ''R''
226 party_type_flag, '''||p_request_id||''' p_request_id, tH.description Description,
227 TH.TO_LE_ID LE_ID, TH.STATUS, TH.TO_LEDGER_ID LEDGER_ID
228 FROM FUN_TRX_HEADERS TH, FUN_TRX_BATCHES TB
229 WHERE TH.INVOICE_FLAG = ''N''
230 AND NOT EXISTS ( SELECT TRX_ID FROM
231 FUN_TRANSFERS FT WHERE FT.TRX_ID = TH.TRX_ID AND
232 FT.PARTY_TYPE_FLAG = ''R'')
233 AND TH.STATUS IN (''APPROVED'',''XFER_INI_GL'')
234 AND TH.BATCH_ID = TB.BATCH_ID
235 )gt
236 where trxb.batch_id = gt.batch_id
237 and ledgers.ledger_id = gt.ledger_id
238 AND P.PARTY_ID = GT.party_id ';
239
240
241 IF (l_date_low IS NULL AND l_date_high IS NOT NULL) THEN
242 gt_where_clause := gt_where_clause||' AND TRXB.GL_DATE BETWEEN TRUNC(to_date(trxB.gl_date,''dd-mon-yy''))
243 AND TRUNC(to_date('''||l_date_high||''',''dd-mon-yy'')) +0.99999';
244
245 ELSE
246 IF (l_date_low IS NOT NULL AND l_date_high IS NULL) THEN
247 gt_where_clause := gt_where_clause||' AND TRXB.GL_DATE BETWEEN TRUNC(to_date('''||l_date_low||''',''dd-mon-yy''))
248 AND TRUNC(to_date(trxB.gl_date,''dd-mon-yy'')) +0.99999';
249
250 ELSE
251 IF (l_date_low IS NOT NULL AND l_date_high IS NOT NULL) THEN
252 gt_where_clause := gt_where_clause||' AND TRXB.GL_DATE BETWEEN TRUNC(to_date('''||l_date_low||''',''dd-mon-yy''))
253 AND TRUNC(to_date('''||l_date_high||''',''dd-mon-yy'')) +0.99999';
254
255 END IF;
256 END IF;
257 END IF;
258
259 IF (p_ledger_low IS NULL AND p_ledger_high IS NOT NULL) THEN
260 gt_where_clause := gt_where_clause || ' AND LEDGERS.NAME BETWEEN ledgers.name and '''||p_ledger_high||'''';
261 ELSE
262 IF (p_ledger_low IS NOT NULL AND p_ledger_high IS NULL) THEN
263 gt_where_clause := gt_where_clause || ' AND LEDGERS.NAME BETWEEN '''||p_ledger_low||''' and ledgers.name';
264 ELSE
265 IF (p_ledger_low IS NOT NULL AND p_ledger_high IS NOT NULL) THEN
266 gt_where_clause := gt_where_clause || ' AND LEDGERS.NAME BETWEEN '''||p_ledger_low||''' and '''||p_ledger_high||'''';
267 END IF;
268 END IF;
269 END IF;
270
271 IF (p_ic_org_low IS NULL AND p_ic_org_high IS NOT NULL) THEN
272 gt_where_clause := gt_where_clause || ' AND P.PARTY_NAME BETWEEN P.PARTY_NAME and '''||l_ic_org_high||'''';
273
274 ELSE
275 IF (p_ic_org_low IS NOT NULL AND p_ic_org_high IS NULL) THEN
276 gt_where_clause := gt_where_clause || ' AND P.PARTY_NAME BETWEEN '''||l_ic_org_low||''' and P.PARTY_NAME';
277
278 ELSE
279 IF (p_ic_org_low IS NOT NULL AND p_ic_org_high IS NOT NULL) THEN
280 gt_where_clause := gt_where_clause || ' AND P.PARTY_NAME BETWEEN '''||l_ic_org_low||''' and '''||l_ic_org_high||'''';
281
282 END IF;
283 END IF;
284 END IF;
285
286 IF (p_le_low IS NULL AND p_le_high IS NOT NULL) THEN
287 gt_where_clause := gt_where_clause || ' AND EXISTS (SELECT NULL
288 FROM XLE_ENTITY_PROFILES
289 WHERE LEGAL_ENTITY_ID = gt.LE_ID
290 AND NAME BETWEEN NAME and '''||p_le_high||''')';
291 ELSE
292 IF (p_le_low IS NOT NULL AND p_le_high IS NULL) THEN
293 gt_where_clause := gt_where_clause || ' AND EXISTS (SELECT NULL
294 FROM XLE_ENTITY_PROFILES
295 WHERE LEGAL_ENTITY_ID = gt.LE_ID
296 AND NAME BETWEEN '''||p_le_low||''' and NAME)';
297 ELSE
298 IF (p_le_low IS NOT NULL AND p_le_high IS NOT NULL) THEN
299 gt_where_clause := gt_where_clause || ' AND EXISTS (SELECT NULL
300 FROM XLE_ENTITY_PROFILES
301 WHERE LEGAL_ENTITY_ID = gt.LE_ID
302 AND NAME BETWEEN '''||p_le_low||''' and '''||p_le_high||''')';
303 END IF;
304 END IF;
305 END IF;
306
307 EXECUTE IMMEDIATE gt_insert_clause||gt_where_clause;
308 commit;
309
310 END;
311
312 -- Bug 7173185. Changed the query to populate the REFERENCE4 column also.
313 /* Insert data into temp table ends here */
314 insert_clause := 'INSERT INTO GL_INTERFACE
315 (STATUS,
316 GROUP_ID,
317 SET_OF_BOOKS_ID,
318 ACCOUNTING_DATE,
319 CURRENCY_CODE,
320 DATE_CREATED,
321 CREATED_BY,
322 ACTUAL_FLAG,
323 USER_JE_CATEGORY_NAME,
324 USER_JE_SOURCE_NAME,
325 CURRENCY_CONVERSION_DATE,
326 USER_CURRENCY_CONVERSION_TYPE,
327 ENTERED_DR,
328 ENTERED_CR,
329 REFERENCE10,
330 CODE_COMBINATION_ID,
331 LEDGER_ID,
332 REFERENCE21,
333 REFERENCE22,
334 REFERENCE23,
335 REFERENCE24,
336 REFERENCE25,
337 PERIOD_NAME,
338 CHART_OF_ACCOUNTS_ID,
339 REQUEST_ID,
340 REFERENCE4)';
341 select_clause := ' SELECT ''NEW'','''||
342 p_request_id||''',
343 LEDGERS.LEDGER_ID,
344 TRUNC(TRXB.GL_DATE),
345 TRXB.CURRENCY_CODE,
346 SYSDATE,
347 D.CREATED_BY,
348 ''A'','''||
349 l_category||''','''||
350 l_source||''',
351 TRUNC(TRXB.GL_DATE),
352 FUN_GL_BATCH_TRANSFER.GET_CONVERSION_TYPE(TRXB.EXCHANGE_RATE_TYPE),
353 D.AMOUNT_DR,
354 D.AMOUNT_CR,
355 GT.DESCRIPTION,
356 D.CCID,
357 LEDGERS.LEDGER_ID,
358 ''Intercompany Transaction'',
359 TRXB.BATCH_ID,
360 GT.TRX_ID,
361 T.LINE_ID,
362 D.DIST_ID,
363 PERIODS.PERIOD_NAME,
364 LEDGERS.CHART_OF_ACCOUNTS_ID,'''||
365 p_request_id||''',
366 TRXB.BATCH_NUMBER
367 FROM
368 GL_LEDGERS LEDGERS,
369 GL_PERIOD_STATUSES GLPS,
370 GL_PERIODS PERIODS,
371 FUN_TRX_BATCHES TRXB,
372 FUN_TRX_LINES T,
373 FUN_DIST_LINES D,
374 fun_transfers gt
375 WHERE TRXB.BATCH_ID = GT.BATCH_ID
376 AND GT.request_id = '''||p_request_id||'''
377 AND LEDGERS.LEDGER_ID = gt.LEDGER_ID
378 AND PERIODS.PERIOD_SET_NAME = LEDGERS.PERIOD_SET_NAME
379 AND TRUNC(TRXB.GL_DATE) BETWEEN PERIODS.START_DATE AND PERIODS.END_DATE
380 AND PERIODS.ADJUSTMENT_PERIOD_FLAG <> ''Y''
381 AND GLPS.PERIOD_NAME = PERIODS.PERIOD_NAME
382 AND GLPS.APPLICATION_ID = 101
383 AND GLPS.SET_OF_BOOKS_ID = LEDGERS.LEDGER_ID
384 AND T.TRX_ID = GT.TRX_ID
385 AND D.TRX_ID = T.TRX_ID
386 AND D.PARTY_TYPE_FLAG = GT.PARTY_TYPE_FLAG
387 AND GLPS.CLOSING_STATUS IN (''O'',''F'')
388 AND FUN_GL_BATCH_TRANSFER.HAS_VALID_CONVERSION_RATE(TRXB.CURRENCY_CODE,LEDGERS.CURRENCY_CODE,
389 TRXB.EXCHANGE_RATE_TYPE,TRUNC(TRXB.BATCH_DATE)) = 1';
390
391 l_cur_main_query := l_cur_select||l_cur_where;
392
393 --Insert into GL_INTERFACE table.
394 EXECUTE IMMEDIATE insert_clause || select_clause || where_clause;
395
396 WF_EVENT.AddParameterToList(p_name=>'INIT_SYS_DATE',
397 p_value=>l_init_sysdate,
398 p_parameterlist =>l_parameter_list);
399
400 WF_EVENT.AddParameterToList(p_name=>'TRX_TYPE',
401 p_value=>'Intercompany Transaction',
402 p_parameterlist=>l_parameter_list);
403
404 l_event_key:=FUN_INITIATOR_WF_PKG.GENERATE_KEY(p_batch_id=>p_request_id,
405 p_trx_id => 0
406 );
407
408 WF_EVENT.RAISE(p_event_name =>'oracle.apps.fun.batch.gl.transfer',
409 p_event_key =>l_event_key,
410 p_parameters=>l_parameter_list);
411
412
413 EXCEPTION
414 WHEN NO_DATA_FOUND THEN
415 -- Bug # 6842245
416 -- null
417 FND_FILE.PUT_LINE(FND_FILE.LOG,'INSERT BLOCK: No Data Found');
418 raise;
419
420
421 WHEN DUP_VAL_ON_INDEX THEN
422 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,2 );
423 FND_FILE.PUT_LINE(fnd_file.output,' *****Another GL Transfer process is running with same set of transactions.*****');
424 FND_FILE.PUT_LINE(FND_FILE.LOG,'Another GL Transfer process is running with same set of transactions.');
425 rollback;
426 raise;
427 WHEN OTHERS THEN
428 -- Bug # 6842245
429 FND_FILE.PUT_LINE(FND_FILE.LOG,'INSERT BLOCK: Unexpected error:' || sqlcode || sqlerrm);
430 retcode := 2;
431 raise;
432 END;
433
434
435 -- add journal import code
436
437 if p_run_journal_import = 'Y' then
438
439 -- run journal import for each ledger id
440
441 For o in c_import(p_request_id, l_source, l_category)
442 loop
443 -- get interface run id
444 select gl_journal_import_s.nextval into v_interface_run_id from dual;
445
446 insert into gl_interface_control (je_source_name,
447 status,
448 set_of_books_id,
449 group_id,
450 interface_run_id)
451 values ( 'Global Intercompany',
452 'S',
453 o.ledger_id,
454 p_request_id, -- Bug No : 7215571
455 v_interface_run_id);
456
457
458 /* Launch Concurrent Request to do journal import*/
459
460 import_request_id := fnd_request.submit_request(
461 application => 'SQLGL', -- application short name
462 program => 'GLLEZL', -- program short name
463 description => null, -- program name
464 start_time => null, -- start date
465 sub_request=>FALSE, -- sub-request
466 argument1 => to_char(v_interface_run_id), -- interface run id
467 argument2 => fnd_profile.value('GL_ACCESS_SET_ID'), -- set of books id
468 argument3 => 'N', -- error to suspense flag
469 argument4 => to_char(l_date_low,'YYYY/MM/DD'), -- from accounting date
470 argument5 => to_char(l_date_high,'YYYY/MM/DD'), -- to accounting date
471 argument6 => p_create_summary_journals, -- create summary flag
472 argument7 => 'Y', -- import desc flex flag
473 argument8 => 'Y');
474
475 end loop;
476 --commit; Bug No: 6731040.
477 end if;
478
479 -- do status updates
480 -- Need the decodes here because for each transaction, the IF condition
481 -- executes twice - once for Party Type R and once for 'I'
482 -- so the value of the status in the cursor variable is not really
483 -- the latest value. Prior iteration of the IF condition could have
484 -- set it to a value different to the value in the cursor
485 OPEN c_transfer_obj for l_cur_main_query;
486 FETCH c_transfer_obj BULK COLLECT INTO l_status_tbl,
487 l_party_type_flag_tbl,
488 l_error_mesg_tbl,
489 l_trx_id_tbl,
490 l_trx_num,
491 l_batch_num,
492 l_batch_id_tbl ; -- Bug 6797385.
493 CLOSE c_transfer_obj;
494
495 IF l_trx_id_tbl.COUNT > 0
496 THEN
497 FORALL l_index IN l_trx_id_tbl.FIRST .. l_trx_id_tbl.LAST
498 UPDATE fun_trx_headers
499 SET status = DECODE (l_party_type_flag_tbl(l_index),
500 'I',DECODE (status,
501 'APPROVED','XFER_INI_GL',
502 'XFER_RECI_GL', 'COMPLETE'),
503 'R',DECODE (status,
504 'APPROVED','XFER_RECI_GL',
505 'XFER_INI_GL', 'COMPLETE'))
506 WHERE trx_id = l_trx_id_tbl(l_index)
507 AND l_error_mesg_tbl(l_index) = 'FUN_API_TRX_TRANSFERRED';
508 END IF;
509 -- Bug 6797385. Update the status in fun_trx_batches starts here.
510 IF l_batch_id_tbl.COUNT > 0
511 THEN
512 FORALL l_index IN l_batch_id_tbl.FIRST .. l_batch_id_tbl.LAST
513 UPDATE fun_trx_batches b
514 SET b.status = 'COMPLETE'
515 WHERE NOT EXISTS ( SELECT trx_id
516 FROM fun_trx_headers
517 WHERE fun_trx_headers.batch_id = b.batch_id
518 AND status NOT IN ('COMPLETE', 'REJECTED'))
519 AND b.batch_id = l_batch_id_tbl(l_index)
520 AND b.status <> 'COMPLETE';
521 END IF;
522 -- Bug 6797385. Update the status in fun_trx_batches Ends here.
523 IF l_trx_id_tbl.COUNT > 0 THEN
524 FOR l_index IN l_trx_id_tbl.FIRST .. l_trx_id_tbl.LAST
525 LOOP
526 IF (l_error_mesg_tbl(l_index) like 'GL_PERIOD_NOT_OPEN%') THEN
527 fnd_message.set_name('FUN','GL_PERIOD_NOT_OPEN');
528 fnd_file.put_line(fnd_file.output, rpad(substr(l_batch_num(l_index), 1,15),20)||rpad(substr(l_trx_num(l_index),1,15),20)||fnd_message.get);
529 ELSIF (l_error_mesg_tbl(l_index) like 'FUN_API_CONV_RATE_NOT_FOUND') THEN
530 fnd_message.set_name('FUN','FUN_API_CONV_RATE_NOT_FOUND');
531 fnd_file.put_line(fnd_file.output, rpad(substr(l_batch_num(l_index), 1,15),20)||rpad(substr(l_trx_num(l_index),1,15),20)||fnd_message.get);
532 ELSE
533 fnd_message.set_name('FUN','FUN_API_TRX_TRANSFERRED');
534 fnd_file.put_line(fnd_file.output, rpad(substr(l_batch_num(l_index), 1,15),20)||rpad(substr(l_trx_num(l_index),1,15),20)||fnd_message.get);
535 END IF;
536 END LOOP;
537 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,2 );
538 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' *****End Of Report*****');
539 ELSE
540 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,2 );
541 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' *****No Data Found*****');
542 END IF;
543
544 /* Bug 6797385.
545 UPDATE fun_trx_batches
546 SET status = 'COMPLETE'
547 WHERE status <> 'COMPLETE'
548 AND NOT EXISTS ( SELECT trx_id
549 FROM fun_trx_headers
550 WHERE fun_trx_headers.batch_id = fun_trx_batches.batch_id
551 AND status NOT IN ('COMPLETE', 'REJECTED'));
552 */
553 DELETE fun_transfers
554 WHERE REQUEST_ID = P_REQUEST_ID;
555 commit;
556
557 EXCEPTION
558 WHEN NO_DATA_FOUND THEN
559 FND_FILE.PUT_LINE(FND_FILE.LOG,'No Data Found');
560 -- Bug # 6842245
561 FND_FILE.PUT_LINE(FND_FILE.LOG,'Rolling Back All The Transactions');
562 rollback;
563
564 DELETE fun_transfers
565 WHERE REQUEST_ID = P_REQUEST_ID;
566 commit;
567
568 WHEN OTHERS THEN
569 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error:' || sqlcode || sqlerrm);
570 retcode := 2;
571 -- Bug # 6842245
572 FND_FILE.PUT_LINE(FND_FILE.LOG,'Rolling Back All The Transactions');
573 rollback;
574
575 DELETE fun_transfers
576 WHERE REQUEST_ID = P_REQUEST_ID;
577 commit;
578
579 END GL_BATCH_TRANSFER;
580
581 END FUN_GL_BATCH_TRANSFER;
582
583
[Home] [Help]