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