Find eBS table based on value in column

Sometimes I know the value that is used somewhere in a screen, but I have no idea what tables are behind it. So I wrote a dynamic sql script to run through a number of tables to find the table and column that holds the value.

Run it using the value you are searching for and a restriction on the tables.
For example

@FINDVALUE 3004 AR

To find all AR tables that have any column with a value 3004. Currently I only look for CHARs and NUMBERs, but of course you can expand if desired.


SET SERVEROUTPUT ON SIZE 1000000
SET VERIFY OFF

DECLARE
  p_table_name   VARCHAR2(240) := '&&2';
  p_value        VARCHAR2(240) := '&&1';
  v_statement    VARCHAR2(8000);
  v_base_statement VARCHAR2(8000);
  v_found        VARCHAR2(240);
  TheResult      NUMBER;
 
  CURSOR C_Tables
  IS
  SELECT Table_Name
  FROM   ALL_TABLES
  WHERE  Table_Name like p_table_name || '%';
 
  CURSOR C_Table_Cols
  (
    cp_table_name VARCHAR2
  )
  IS
  SELECT Column_Name, Table_Name, Data_Type
  FROM   ALL_TAB_COLUMNS
  WHERE Table_Name = cp_table_name
  AND   Data_Type IN ('NUMBER', 'VARCHAR2')
  ;
 
 
BEGIN

  DBMS_OUTPUT.Put_Line ('List of tables with value '|| p_value);
  DBMS_OUTPUT.Put_Line ('---------------------------------------------------------------------');

  <<Table_Def>>
  FOR T IN C_Tables
  LOOP

      v_base_statement := 'SELECT 1 FROM ' || T.Table_Name || ' WHERE ';
     
      <<Table_Cols>>
      FOR C IN C_Table_Cols (cp_table_name => T.Table_Name)
      LOOP
        IF C.Data_Type = 'NUMBER'
        THEN
          v_statement := v_base_statement || C.Column_Name || ' = ' || p_value;
        ELSIF C.Data_Type = 'VARCHAR2'
        THEN
          v_statement := v_base_statement || C.Column_Name || ' = ''' || p_value || '''';
        END IF;
        TheResult := 0;
        BEGIN
          --dbms_output.put_line ('Testing ... ' || v_statement);
          execute immediate (v_statement) INTO TheResult;
          --dbms_output.put_line ('... '||  v_statement || ' returns TRUE');
          --dbms_output.put_line ('Result: ' || TheResult);
          EXCEPTION
            WHEN Others THEN
              --dbms_output.put_line ('Error: ' || SQLERRM);
              TheResult := 0;
        END;
       
        IF TheResult = 1
        THEN
          v_found := T.Table_Name || '.' || C.Column_Name || ' (' || C.Data_Type || ')';
          DBMS_OUTPUT.Put_Line (v_found);
        END IF;
      END LOOP Table_Cols;
     
  END LOOP Table_Def;
 
END;
/

Another sample for the same issue is as follows from Saubhik

SELECT   DISTINCT
                 SUBSTR(:val, 1, 11) "Searchword",
                  SUBSTR(table_name, 1, 14) "Table",
                 SUBSTR(t.COLUMN_VALUE.getstringval(), 1, 50) "Column/Value"
      FROM     cols,
                   table(XMLSEQUENCE(DBMS_XMLGEN.getxmltype(   'select '
                                                           || column_name
                                                           || ' from '
                                                           || table_name
                                                        || ' where (UPPER('''
                                                         || :val
                                                         || ''')=UPPER('
                                                        || '))').EXTRACT('ROWSET/ROW/*'))) t
    WHERE    table_name IN ('EMP', 'DEPT','EMPLOYEES') --limiting the table names, you can omit this.
     ORDER BY "Table"

  /

Banking Gateway Fusion Financials Cloud - a necessary add on

Although Oracle Financials Cloud delivers very rich functionality, it does not provide out of the box seamless integration with banks around the globe. Partly this is due to the fact that there is simply too many different banks with their own banking formats, but also .. even when standards are enforced like SEPA, each bank uses these standards in their own slightly different way.
Next to that, payments made by customers differ in the US from how this is done in Europe. In the US check payments are used, where Europe handles payments electronically.

In Oracle Financials Cloud you can upload your bank statements, reconcile them and create payment receipts. However this is a time consuming manual process and you must match the payment format required for Fusion Financials. We at Profource have developed an add-on for the Oracle Financials Cloud to remove the need for this manual exercise using the Profource Banking Cloud.

Now what does it do?

Actually there are two challenges to be solved. The first challenge is to deliver the correct outgoing payment instructions to the bank for paying suppliers, employees and trading partners.
Second is to process the incoming bank statement files and match them against open customer transactions. The higher the matching grade, the less manual work, so optimization of the matching procedure is key in this process.

Outbound payment instructions

From Oracle Financials Cloud the user submits the payment request to select approved due invoices for payment. The standard payment format delivered by Fusion should be amended slightly to match the payment format required by the specific bank like Rabobank, ABN Amro, etc. But also foreign payment formats should be amended to match the payment format BACS for the US and ISABEL for Belgium. The Profource Banking Cloud delivers a predefined set of payment formats ready for use.
We provide templates for all SEPA formats including CAMT for Rabobank, ING, ABN Amro, DNB Telepay, BACS and ISABEL.

Inbound bank statement and matching

The second key part of the banking gateway solution is the automation process for uploading, transforming and matching your bank statement files in an efficient and effective manor.
The bank statement is usually a combination of payments related to supplier invoices, incoming payments from your customers and miscellaneous entries like bank charges, interest, etc.
Matching the incoming payments to open transactions is complicated by the fact customers can use free format descriptions while paying their invoices, not always specifying the invoices or combining payments of several invoices in one line.

Oracle Financials Cloud does not provide you a standard mechanism to match invoice numbers specified in the line description, thus resulting in a high number of unmatched statement lines and extensive manual work.
The Profource Banking Cloud allows you to upload your bank statement, performs the transformation to the correct bank format required by the Financials Cloud and adds advanced matching algorithms to match the payments to the correct transactions, including matching on statement line description and invoice amount combinations. This results in much higher matching rates and reduces the manual labor required by the user.
The Profource Banking Cloud uploads both the bank statements in Cash Management and creates the receipts in Receivables.

 

The Profource Banking Cloud itself is displayed as an ADF page accessible from the Fusion environment. The user chooses the correct business unit and bank account and the bank statement to be uploaded.

The user can see the progress of the file processing in the file upload history and is automatically notified when the files have been processed within Oracle Fusion using BPEL processes on the background. The next thing to do for the user is to open the Bank Statements Reconciliation window in Fusion to check the unreconciled statement lines.

In the example above two lines have not yet been reconciled and must be reconciled manually.
The receipts are automatically created including bank statement description available as comments on the receipt for audit and reference purposes.

Summary

The Profource Banking Cloud is a necessary add-on to the Oracle Financials Cloud and

• provides you with the right payment file format tailored for the bank you are using,
• removes the time you need to enter your bank statements and receipts,
• reduces the time you need to spend on unreconciled statement lines due to the advanced matching algorithms.

With thanks to Mathieu Kamp, Hakan Biroglu, Martijn Rijpkema and Marcel Mossel!