Query to find the special characters in a Payment Batch
If we have encountered any special characters in Invoice Description or at the supplier bank account level, our payment batch will get affected and it will get failed during the build payments.. we had a scenario where couple of imported AP invoices were containing some special characters in the invoice description and also for some supplier sites has some special characters.
Due to this while business running payment process they encountered an issue(payment failed).. we have enabled the trace and find debug and understand that there were some special characters in the selected invoice/vendor.. so we used the below query to find out such invoices which are having this special characters..
We can run this script by passing the payment process request name so that all the invoices which got selected for that payment will get validated by using this script will let us know the result of the invoices which are having the special characters..
set serveroutput on
declare
l_parameter varchar2(255);
l_pay_instr_id number;
l_where_clause varchar2(2000);
type t_invalid_data_rec is record (
table_name varchar2(30),
index_column varchar2(30),
index_value number,
display_column varchar2(30),
display_value varchar2(256),
invalid_column varchar2(30),
invalid_value varchar2(32767),
dump_value varchar2(32767)
);
type t_invalid_data_array is table of t_invalid_data_rec
index by varchar2(1000);
type t_ref_cursor is REF CURSOR;
procedure PrintInvalidArray(
v_category varchar2,
v_invalid_list t_invalid_data_array
) is
l_last_table varchar2(30) := ‘<null>’;
l_last_value varchar2(256) := ‘<null>’;
l_rec_key varchar2(1000);
l_invalid_rec t_invalid_data_rec;
begin
dbms_output.put_line(‘========================================’);
dbms_output.put_line(‘Category: ‘ || v_category);
dbms_output.put_line(‘========================================’);
l_rec_key := v_invalid_list.first;
while l_rec_key is not null loop
l_invalid_rec := v_invalid_list(l_rec_key);
if(l_invalid_rec.table_name <> l_last_table) then
l_last_table := l_invalid_rec.table_name;
dbms_output.put_line(‘ Table: ‘ || l_invalid_rec.table_name);
end if;
if(l_invalid_rec.display_value <> l_last_value) then
l_last_value := l_invalid_rec.display_value;
dbms_output.put_line(‘ ______________________________’);
dbms_output.put_line(‘ Index: ‘ || l_invalid_rec.index_column || ‘ = ‘ || l_invalid_rec.index_value);
if (l_invalid_rec.index_column <> l_invalid_rec.display_column) then
dbms_output.put_line(‘ Display: ‘ || l_invalid_rec.display_column || ‘ = ‘ || l_invalid_rec.display_value);
end if;
end if;
dbms_output.put_line(‘ Invalid: ‘ || l_invalid_rec.invalid_column || ‘ = “‘ || l_invalid_rec.invalid_value || ‘”‘);
dbms_output.put_line(‘ Dump: ‘ || l_invalid_rec.dump_value);
l_rec_key := v_invalid_list.next(l_rec_key);
end loop;
dbms_output.put_line(‘________________________________________’);
dbms_output.put_line(‘Category: ‘ || v_category);
dbms_output.put_line(‘Found ‘ || v_invalid_list.count || ‘ fields with invalid characters.’);
dbms_output.put_line(”);
end PrintInvalidArray;
procedure GetInvalidChars(
v_category varchar2,
v_owner varchar2,
v_table varchar2,
v_index_col varchar2,
v_where varchar2 default ‘true’,
v_display_col varchar2 default null,
v_ignore_col varchar2 default null
) is
l_base_query varchar2(20000);
l_query varchar2(20000);
l_rec_key varchar2(1000);
l_white_space varchar2(64) := ‘chr(9) || chr(10) || chr(13) || chr(32)’;
l_cursor t_ref_cursor;
l_invalid_data_rec t_invalid_data_rec;
l_invalid_data_list t_invalid_data_array;
cursor cur_column_names is
select column_name
from all_tab_columns
where owner = upper(v_owner)
and table_name = upper(v_table)
and data_type = ‘VARCHAR2’;
begin
l_base_query := ‘SELECT ”#OWNER#.#TABLE#”, ”#INDEX_COL#”, #INDEX_COL#, ”#DISPLAY_COL#”, #DISPLAY_COL#, ”#INVALID_COL#”, #INVALID_COL#, dump(#INVALID_COL#) ‘ ||
‘FROM #OWNER#.#TABLE# ‘ ||
‘WHERE #WHERE_CLAUSE# ‘ ||
‘and LTRIM( TRANSLATE(#INVALID_COL#,” ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789@#$%^&*(){}[]_+-=,!\`”””~./?:;”,” ”), ‘ ||
l_white_space || ‘) IS NOT NULL’;
l_base_query := replace(l_base_query, ‘#OWNER#’, v_owner);
l_base_query := replace(l_base_query, ‘#TABLE#’, v_table);
l_base_query := replace(l_base_query, ‘#INDEX_COL#’, v_index_col);
l_base_query := replace(l_base_query, ‘#DISPLAY_COL#’, nvl(v_display_col, v_index_col) );
l_base_query := replace(l_base_query, ‘#WHERE_CLAUSE#’, v_where);
for l_rec in cur_column_names loop
if (v_ignore_col is not null and l_rec.column_name like v_ignore_col) then
continue;
end if;
l_query := replace(l_base_query, ‘#INVALID_COL#’, l_rec.column_name);
open l_cursor for l_query;
loop
fetch l_cursor into l_invalid_data_rec;
exit when l_cursor%notfound;
l_rec_key := upper(
l_invalid_data_rec.table_name || ‘,’ ||
l_invalid_data_rec.display_value || ‘,’ ||
l_invalid_data_rec.index_value || ‘,’ ||
l_invalid_data_rec.invalid_column
);
l_invalid_data_list(l_rec_key) := l_invalid_data_rec;
end loop; — l_cursor
close l_cursor;
end loop; — cur_column_names
PrintInvalidArray(v_category, l_invalid_data_list);
end GetInvalidChars;
begin
— Validate script parameter
begin
l_parameter := ‘&PAYMENT_INSTRUCTION_ID’;
dbms_output.put_line(‘PAYMENT_INSTRUCTION_ID: ‘ || l_parameter);
select PAYMENT_INSTRUCTION_ID
into l_pay_instr_id
from IBY.IBY_PAY_INSTRUCTIONS_ALL
where PAYMENT_INSTRUCTION_ID = to_number(l_parameter);
exception
when NO_DATA_FOUND then
dbms_output.put_line(‘No matches for payment instruction ID.’);
return;
when others then
dbms_output.put_line(‘Error occurred while validating parameter.’);
dbms_output.put_line(sqlerrm);
return;
end;
— Banking Information (ignores columns for PAYER and PAYEE)
l_where_clause := ‘PAYMENT_INSTRUCTION_ID = ‘ || l_pay_instr_id;
GetInvalidChars(‘Banking Information’, ‘IBY’, ‘IBY_PAYMENTS_ALL’, ‘PAYMENT_ID’, l_where_clause);
— Payer Information
l_where_clause := ‘PARTY_ID in (select ipa.PAYER_PARTY_ID from IBY.IBY_PAYMENTS_ALL ipa where ipa.PAYMENT_INSTRUCTION_ID = ‘ || l_pay_instr_id || ‘)’;
GetInvalidChars(‘Payer Information’,’AR’, ‘HZ_PARTIES’, ‘PARTY_ID’, l_where_clause, ‘PARTY_NAME’);
— Payee Information
l_where_clause := ‘PARTY_ID in (select ipa.PAYEE_PARTY_ID from IBY.IBY_PAYMENTS_ALL ipa where ipa.PAYMENT_INSTRUCTION_ID = ‘ || l_pay_instr_id || ‘)’;
GetInvalidChars(‘Payee Information’,’AR’, ‘HZ_PARTIES’, ‘PARTY_ID’, l_where_clause, ‘PARTY_NAME’);
— Trading Partner Information (ignores duplicates from PAYEE)
l_where_clause := ‘PARTY_ID in (select ipa.INV_PAYEE_PARTY_ID from IBY.IBY_PAYMENTS_ALL ipa where ipa.PAYMENT_INSTRUCTION_ID = ‘ || l_pay_instr_id || ‘ and ipa.PAYEE_PARTY_ID <> ipa.INV_PAYEE_PARTY_ID)’;
GetInvalidChars(‘Trading Partner Information’,’AR’, ‘HZ_PARTIES’, ‘PARTY_ID’, l_where_clause, ‘PARTY_NAME’);
— Invoice Information
l_where_clause := ‘INVOICE_ID in (select idpa.CALLING_APP_DOC_UNIQUE_REF2 from IBY.IBY_PAYMENTS_ALL ipa, IBY.IBY_DOCS_PAYABLE_ALL idpa where ipa.PAYMENT_INSTRUCTION_ID = ‘ || l_pay_instr_id || ‘ and ipa.PAYMENT_ID = idpa.PAYMENT_ID)’;
GetInvalidChars(‘Invoice Information’, ‘AP’, ‘AP_INVOICES_ALL’, ‘INVOICE_ID’, l_where_clause, ‘INVOICE_NUM’);
— Invoice Line Information
l_where_clause := ‘INVOICE_ID in (select idpa.CALLING_APP_DOC_UNIQUE_REF2 from IBY.IBY_PAYMENTS_ALL ipa, IBY.IBY_DOCS_PAYABLE_ALL idpa where ipa.PAYMENT_INSTRUCTION_ID = ‘ || l_pay_instr_id || ‘ and ipa.PAYMENT_ID = idpa.PAYMENT_ID)’;
GetInvalidChars(‘Invoice Line Information’, ‘AP’, ‘AP_INVOICE_LINES_ALL’, ‘INVOICE_ID’, l_where_clause, ‘LINE_NUMBER’);
exception
when others then
dbms_output.put_line(‘Error occurred while checking source data.’);
dbms_output.put_line(sqlerrm);
return;
end;
/
