| Fatal pl/sql error occurred [message #282463] |
Thu, 22 November 2007 01:14  |
anitav
Messages: 6
Registered: September 2006
Location: South Africa
|
Junior Member |
|
|
hi
Please can someone help regard :
I am getting an error in my rdf...'cf_create_bankserv_fileformula' : Fatal PL/SQL error occurred.
in the formula cf_create_bankserv_fileformula I execute a 2 custom written packages each one create a file on the server using utl_file concept.
I am trying to create 2 files in the same formula using 2 different
package and the problem is only one file is being created...
any advice on what to do so that both the files are created
I'm using fopen, putline and flose_all ....in both packages
but I need something else
thanx
|
|
|
|
|
|
| Re: Fatal pl/sql error occurred [message #282488 is a reply to message #282475] |
Thu, 22 November 2007 02:39  |
anitav
Messages: 6
Registered: September 2006
Location: South Africa
|
Junior Member |
|
|
hi
You right its difficult without the code ....below is the code for the formula but not the packages....I copied the one package and created another one... lets call it package pacs and cams in order to explain my problem
the strange thing is pacs runs and creates a pac file
package cams does not create a file
If I reverse the order .....package cams runs and creates a cams file...
package pacs does not
In both variation it errors on the FATAL PL/SQL error
If I run it individually on its own it works...
Any advice will be highly appreciated
thanx
this is the formula code where I am calling the packages
FUNCTION CF_CREATE_BANKSERV_FILEFormula RETURN CHAR IS
l_file_name VARCHAR(300);
BEGIN
SRW.MESSAGE('97','in CF_CREATE_BANKSERV_FILEFormula');
SRW.MESSAGE('97','calling pacs package');
AP_EFT_INTERFACE_PKG.main_prc(:P_PAYMENT_BATCH);
SRW.MESSAGE('97','calling cams package');
AP_EFT_INTERFACE_CAMS_PKG.main_prc(:P_PAYMENT_BATCH); -- anita calling new package
SRW.MESSAGE('97','after package');
l_file_name := 'AP_EFT_'||FND_GLOBAL.CONC_REQUEST_ID||'-' || 'EFT file has been created on Server';
l_file_name := NULL;
SRW.MESSAGE('97','at end CF_CREATE_BANKSERV_FILEFormula');
RETURN(l_file_name);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(1,'Error Code: '||SQLCODE||'Error Message: '||SQLERRM);
END CF_CREATE_BANKSERV_FILEFormula;
[Updated on: Thu, 22 November 2007 06:41] Report message to a moderator |
|
|
|
| Re: Fatal pl/sql error occurred [message #282500 is a reply to message #282488] |
Thu, 22 November 2007 03:19 |
 |
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Do those packages create a file with the same name? Does, perhaps, package starts creating one file and when the second package tries to do the same, finds an open file, opened by another session (i.e. the first package). If so, could you rename one of the files?
You could also remove exception handling section and let Oracle take care about it - the report will fail, of course, but you might be able to see additional Oracle error code(s) which might help identifying an error.
By the way, function always returns NULL.
|
|
|
|
| Re: Fatal pl/sql error occurred [message #282503 is a reply to message #282500] |
Thu, 22 November 2007 03:33 |
anitav
Messages: 6
Registered: September 2006
Location: South Africa
|
Junior Member |
|
|
hi Littlefoot
thanx for ur help
NO, I am creating files with different names
below is the code creating the one file ....this code exist in both packages but the file name is different..
I suspect the 'utl_file.fclose_all' maybe causing the problem
after the first package runs and somehow the second file is not produced.
u suggesting I remove the exception in the formula column ...will try that
this statement 'l_file_name := NULL;' ......hahahahaha
was me when trying to debug error...must be removed
thanx
l_file_name := 'AP_EFT_'||fnd_global.conc_request_id||'.txt';
l_dir_name := '/data01/fin/ap/out';
l_file_id := utl_file.fopen(l_dir_name, l_file_name,'W');
utl_file.put_line (l_file_id,create_inst_hdr_rec_line_fn(l_generation_number));
utl_file.put_line (l_file_id,create_usr_hdr_rec_line_fn(l_generation_number,l_sequence_number));
create_std_trx_rec_line_fn(l_file_id,l_sequence_number,l_hash_total,l_amount_total,l_record_count);
utl_file.put_line (l_file_id,create_contra_rec_line_fn(l_sequence_number,l_amount_total));
utl_file.put_line (l_file_id,create_usr_trailer_rec_line_fn(l_generation_number,l_record_count,l_amount_total,l_hash_total));
utl_file.put_line (l_file_id,create_inst_trail_rec_line_fn(l_generation_number,l_record_count));
utl_file.fclose_all;
[Updated on: Thu, 22 November 2007 06:40] Report message to a moderator |
|
|
|
|
|
| Re: Fatal pl/sql error occurred [message #282524 is a reply to message #282518] |
Thu, 22 November 2007 04:30 |
anitav
Messages: 6
Registered: September 2006
Location: South Africa
|
Junior Member |
|
|
hi
I removed exception handling....but no extra info...see below output ....
am now going to remove the close and try
....I'm at my wits end with this error so am grasping at straws
ciao
+---------------------------------------------------------------------------+
Payables: Version : 11.5.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
APEFT module: EFT Payments Remittance Advice Report
+---------------------------------------------------------------------------+
Current system time is 22-NOV-2007 12:25:20
+---------------------------------------------------------------------------+
+-----------------------------
| Starting concurrent program execution...
+-----------------------------
Arguments
------------
P_PAYMENT_BATCH='anitest22'
------------
Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
AMERICAN_AMERICA.WE8ISO8859P1
'.,'
MSG-00800: Getting Payment Batch details....
MSG-00007: After Custom_Init
MSG-00007: before break
MSG-00097: in CF_CREATE_BANKSERV_FILEFormula
MSG-00097: calling pacs package
MSG-00097: calling cams package
REP-1401: 'cf_create_bankserv_fileformula': Fatal PL/SQL error occurred.
Report Builder: Release 6.0.8.27.0 - Production on Thu Nov 22 12:25:22 2007
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Enter Username:
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
Payment Batch : anitest22
Bank Name :AP Creditors (EFT)
Account Number :620351
Branch :251905
Type :1
User Code :A327
Displaying suppliers details with incorrect Bank Details:
Site Code, Vendor Number, Vendor Name, Bank Number, Account Number, Account Type
Purge Date :071127
First Date :071122
Last Date :071123
Database Name : ERPDV
Directory Name : /data01/fin/ap/out
File Name : AP_EFT_1863546.txt
Request ID : 1863546
Site Code, Vendor Number, Vendor Name, Check Amount, Payment Date
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
Program exited with status 1
Concurrent Manager encountered an error while running Oracle*Report for your concurrent request 1863546.
Review your concurrent request log and/or report output file for more detailed information.
+---------------------------------------------------------------------------+
Executing request completion options...
Finished executing request completion options.
+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 22-NOV-2007 12:25:26
+---------------------------------------------------------------------------+
[Updated on: Thu, 22 November 2007 06:38] Report message to a moderator |
|
|
|
|
|
| Re: Fatal pl/sql error occurred [message #282540 is a reply to message #282538] |
Thu, 22 November 2007 05:04 |
anitav
Messages: 6
Registered: September 2006
Location: South Africa
|
Junior Member |
|
|
hi
finally the end ....see the light at the end of the tunnel ...
its the fclose_all statement....I had commented it out of both packages...and it works...
I initially had two "formula column". then for the past 3 days have been trying all sorts of coding...
thank you for being there to help
THANK GOD
|
|
|
|
|
|
| Re: Fatal pl/sql error occurred [message #282568 is a reply to message #282565] |
Thu, 22 November 2007 06:50 |
anitav
Messages: 6
Registered: September 2006
Location: South Africa
|
Junior Member |
|
|
hi
yes I have changed the fclose_all to fclose...
this happened because I introduced the second package to create another file...although I dont understand YYYYY fclose_all cause it to crash.
we are changing our EFT software to the bank from a PACS software to CAMS software....but intend running parallel for 3 months so both files have to be produced...
This was urgent and is going live DEC 3...
thanx for the quick response and help
ciao from the sunshine land
[Updated on: Thu, 22 November 2007 06:52] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
