hello guys,
I wonder if someone can help me. I've got a proc that writes out a file into a dir. It's been working all along and all of a sudden when I execute the proc I get the error on the subject. Does anybody know how to resolve this error? Please see my code below, i'm executing it through Toad.
<PROCEDURE avs(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_avs_batch IN VARCHAR2) IS
v_eft_date VARCHAR2(100);
BEGIN
v_eft_date := TO_CHAR(SYSDATE, 'DD-MON-RRRR_HH24_MI_SS');
g_payment_batch := p_avs_batch;
g_file_name := 'IS_AVS'||v_eft_date||'.txt';
g_file_id := utl_file.fopen(g_dir_name,
g_file_name,
'W');
utl_file.put_line(g_file_id,
create_avs_hdr_fn);
create_avs_line_fn(g_file_id);
create_avs_trailer(g_file_id);
utl_file.fclose_all;
IF (update_tables != TRUE) THEN
Fnd_File.put_line(Fnd_File.LOG, 'Failed to update payment batch tables. Cancel the batch');
END IF;
EXCEPTION
WHEN OTHERS THEN
Fnd_File.put_line(Fnd_File.LOG, 'Print Error - eft' || SUBSTR(SQLERRM, 1, 250));
END avs;
--AVS Function
FUNCTION create_avs_hdr_fn RETURN CHAR IS
TYPE avs_header_file IS RECORD(l_record_type VARCHAR2(2),
l_service_type VARCHAR2(4),
l_cats_user_id VARCHAR2(5),
l_seq_number VARCHAR2(5),
l_date_created VARCHAR2(8),
l_time_created VARCHAR2(6),
l_indicator VARCHAR2(4),
l_filler VARCHAR2(66) );
avs_hdr_file avs_header_file;
l_blank VARCHAR2(66) := ' ';
l_return VARCHAR2(100) := NULL;
BEGIN
avs_hdr_file.l_record_type := 'AH';
avs_hdr_file.l_service_type := 'ACCV';
avs_hdr_file.l_cats_user_id := 'DIM16';
avs_hdr_file.l_seq_number := '00001';
avs_hdr_file.l_date_created := TO_CHAR(SYSDATE, 'RRRRMMDD');
avs_hdr_file.l_time_created := TO_CHAR(SYSDATE, 'HH24MISS');
avs_hdr_file.l_indicator := 'TEST';
avs_hdr_file.l_filler := LPAD(l_blank, 66, ' ');
l_return := l_return||avs_hdr_file.l_record_type;
l_return := l_return||avs_hdr_file.l_service_type;
l_return := l_return||avs_hdr_file.l_cats_user_id;
l_return := l_return||avs_hdr_file.l_seq_number;
l_return := l_return||avs_hdr_file.l_date_created;
l_return := l_return||avs_hdr_file.l_time_created;
l_return := l_return||avs_hdr_file.l_indicator;
l_return := l_return||avs_hdr_file.l_filler;
l_return := l_return;
RETURN UPPER(l_return);
EXCEPTION
WHEN OTHERS THEN
Fnd_File.put_line(Fnd_File.LOG, 'Print Error - create_avs_hdr_fn'||SUBSTR(SQLERRM, 1, 250));
END create_avs_hdr_fn;
PROCEDURE create_avs_line_fn(p_file_id IN utl_file.file_type) IS
TYPE avs_line_rec IS RECORD( l_record_type VARCHAR2(2),
l_seq_number VARCHAR2(5),
l_branch_code VARCHAR2(6),
l_account_number VARCHAR2(13),
l_acc_type VARCHAR2(1),
l_reg_number VARCHAR2(13),
l_initials VARCHAR2(5),
l_company_name VARCHAR2(30),
l_filler VARCHAR2(76));
avs_ln_rec avs_line_rec;
l_blank VARCHAR2(13) := ' ';
l_blank1 VARCHAR2(5) := ' ';
l_blank2 VARCHAR2(25) := ' ';
l_return VARCHAR2(100) := NULL;
l_branch_code VARCHAR2(20) ;
l_bank_account_num VARCHAR2(30) ;
l_customer_name VARCHAR2(30) ;
CURSOR c_line_detail IS
SELECT ap_bank_accounts_all.attribute1,
ap_bank_accounts_all.bank_account_num,
hz_parties.party_name
FROM apps.hz_cust_accounts hz_cust_accounts,
apps.hz_cust_acct_sites_all hz_cust_acct_sites_all,
apps.hz_cust_site_uses_all hz_cust_site_uses_all,
apps.hz_parties hz_parties,
apps.ra_terms_tl ra_terms_tl,
apps.ap_bank_accounts_all ap_bank_accounts_all,
apps.ap_bank_account_uses_all ap_bank_account_uses_all
WHERE hz_cust_accounts.cust_account_id = hz_cust_acct_sites_all.cust_account_id
AND hz_cust_acct_sites_all.cust_acct_site_id = hz_cust_site_uses_all.cust_acct_site_id
AND hz_parties.party_id = hz_cust_accounts.party_id
AND ra_terms_tl.term_id = hz_cust_site_uses_all.payment_term_id
AND hz_cust_accounts.cust_account_id = ap_bank_account_uses_all.customer_id
AND ap_bank_account_uses_all.external_bank_account_id = ap_bank_accounts_all.bank_account_id
AND ap_bank_account_uses_all.customer_site_use_id IS NULL
AND hz_cust_site_uses_all.site_use_code = 'BILL_TO'
AND hz_cust_site_uses_all.primary_flag = 'Y'
AND ra_terms_tl.NAME LIKE 'D%'
AND ap_bank_accounts_all.bank_account_num /*IN ('1908422416','4051134040','62026988579','53420054107','200455095');*/ = '240157772';
BEGIN
OPEN c_line_detail;
FETCH c_line_detail
INTO l_branch_code,
l_bank_account_num,
l_customer_name;
CLOSE c_line_detail;
avs_ln_rec.l_record_type := 'AV';
avs_ln_rec.l_seq_number := '00001';
avs_ln_rec.l_branch_code := LPAD(l_branch_code, 6, '0');
avs_ln_rec.l_account_number := LPAD(l_bank_account_num, 13, '0');
avs_ln_rec.l_acc_type := '1';
avs_ln_rec.l_reg_number := LPAD(l_blank, 13, ' ');
avs_ln_rec.l_initials := LPAD(l_blank1, 5, ' ');
avs_ln_rec.l_company_name := RPAD(l_customer_name, 30, ' ');
avs_ln_rec.l_filler := LPAD(l_blank2, 25, ' ');
l_return := l_return||avs_ln_rec.l_record_type;
l_return := l_return||avs_ln_rec.l_seq_number;
l_return := l_return||avs_ln_rec.l_branch_code;
l_return := l_return||avs_ln_rec.l_account_number;
l_return := l_return||avs_ln_rec.l_acc_type;
l_return := l_return||avs_ln_rec.l_reg_number;
l_return := l_return||avs_ln_rec.l_initials;
l_return := l_return||avs_ln_rec.l_company_name;
l_return := l_return||avs_ln_rec.l_filler;
l_return := l_return;
utl_file.put_line(p_file_id, UPPER(l_return));
EXCEPTION
WHEN OTHERS THEN
Fnd_File.put_line(Fnd_File.LOG, 'Print Error - create_avs_line_fn'||SUBSTR(SQLERRM, 1, 250));
END create_avs_line_fn;
--avs trailer
PROCEDURE create_avs_trailer(p_file_id IN utl_file.file_type) IS
TYPE avs_trailer_rec IS RECORD( l_record_type VARCHAR2(2),
l_total_trans VARCHAR2(7),
l_filler VARCHAR2(91));
avs_tr_rec avs_trailer_rec;
l_blank VARCHAR2(91) := ' ';
l_return VARCHAR2(100) := NULL;
BEGIN
avs_tr_rec.l_record_type := 'AT';
avs_tr_rec.l_total_trans := '0000001';
avs_tr_rec.l_filler := LPAD(l_blank, 91, ' ');
l_return := l_return||avs_tr_rec.l_record_type;
l_return := l_return||avs_tr_rec.l_total_trans;
l_return := l_return||avs_tr_rec.l_filler;
l_return := l_return;
utl_file.put_line(p_file_id, UPPER(l_return));
EXCEPTION
WHEN OTHERS THEN
Fnd_File.put_line(Fnd_File.LOG, 'Print Error - create_avs_trailer'||SUBSTR(SQLERRM, 1, 250));
END create_avs_trailer;