Oracle EBS: Changing "Justification" to "Description" in Internet Expenses

Now this is a pretty obscure one, I'd be amazed if there were too many people out there who had this problem!

Basically the issue is that I work for a company that rather than having the traditional pyramid structure of management with the people at the top never talking to anyone we have a much flatter structure and if you are brand new in through the door and fancy talking to the MD about something ... then that's fine (even encouraged).

In light of this different culture people who tested our i-Expenses system commented that the word "Justification" wasn't really in keeping with the companies culture. We also didn't require a "justification" to make an expense claim, but a Description would sure help us authorise it. It's a fairly simple change so why not?!

First of all you need to login and select the "System Administrator" responsibility, then go into "Profiles > System" and query for "Personalize Self-Service Defn%". By default this is set to "No", you need to set it to "Yes" (and remember to switch it off after you're done!):

Figure 1: Setting System Profile Options

Next you need to log out and choose the "Internet Expenses" responsibility and then go into the website. You'll notice that there is a "Personalize" link at the the top right of the page and various other links through the website. You can use these to change the way the page displays. You can also use these to make the site completely unusable - so be careful!

The "Description" we are trying to change is on the "Receipt-based Expenses" page. So you need to create a new expense claim and click through to that page (we won't be submitting, so it doesn't matter what you enter). Your screen will look something like this:

Figure 2: Cash and Other Expenses Page

A new link has appeared above the table beginning "Personalize" ... Clicking this link gets you to the field you need to edit quicker, but clicking any link and drilling down will work just as well (well, I'm assuming here ...!).

Now you will need to move down the list of options until you find "Message Text Input: Justification", then click the pencil (edit) to the right:

Figure 3: Editing "Justification"

Clicking on the pencil will display the following screen:

Figure 4: Changing Text

I have highlighted the entry boxes to change "Justification" for your entire organisation. If you just want to change it for your site (or even just for you) then use the boxes immediately to their left.

Clicking on the circular arrow next to the entry boxes restores them to "Inherit" which in effect deletes your change.

NOTE: When you look for these items again (say you wanted to change back to "Justification") then in Figure 3 where it shows "Justification" it would now show "Description".

Read More

Oracle EBS: Editing the FA Account Generator Process (Oracle Financials)

1. Editing Workflows using Oracle Workflow BuilderInstall "Oracle Workflow Builder" from the Oracle.com website. The version you will need depends on the version of Oracle Applications you are currently using. If you are unsure which version to use you can always raise a Service Request (SR) via Metalink and ask Oracle.

To open the workflow select “File > Open” from the menu:

Figure 1: Oracle Workflow Builder, Open Dialog

User and password are for the Oracle APPS account.

If you want a version of the Workflow other than the current version you can enter a date in the “Effective” entry box and it will give you the version of the workflow that was in effect on that date. Unless you’re doing a rollback to a previous version you can leave this blank.

Clicking “OK” will connect to the database and then display the “Show Item Types” dialog:

Figure 2: Oracle Workflow Builder, Show Item Types

The Workflow to edit is called “FA Account Generator”, in order to edit it you need to highlight it in the right-hand list (Hidden) and move it into the left-hand list (Visible) – as shown in Figure 2. Once you’ve selected it click “OK”.

NOW THIS IS IMPORTANT: If you work on a workflow while connected to the database any change you make is immediately committed to the server. As soon as you have opened a Workflow for editing you should save the Workflow locally and work on the local copy rather than working against a database (you can then open the local file and save it back to the database when you’re done).

The Workflow builder uses an MDI interface, a window will open titled “Navigator”:

Figure 3: Oracle Workflow Builder, Navigator

All the items shown in the figure above (beginning "Generate ...") are the workflows provided by Oracle. You should never edit these, if you do there is a risk (well, a practical certainty) of them being overwritten during an upgrade. If you need to make changes to a standard item (i.e. the process “Generate Book Level Account” in Figure 3) then you must create a copy of that item by right-clicking it, selecting “Copy” and then “Paste”. When prompted to enter the details prefix the name with a short-code, for example your company name or initials (there are length limitations, if you go over the length just remove characters from the end until it fits).

Fixing problems with the “Generate Accounts” process requires that you open (double-click) the “XXXX Generate Book Level Account” process (where XXXX is your chosen prefix). The original version (i.e. the “Generate Book Level Account” process) is below:

Figure 4: Generate Book Level Account Process

As you can see the "out of the box" process is fairly straight forward. The most common changes are usually to create "custom" branches coming off the "Get Book Account Name" process (where it shows "" above the arrow). Looking at the company I currently work for our customised process is:

Figure 5: Customised Generate Book Level Account Process

This shows separate flows for Proceeds of Sale (Loss/ Gain/ Clearing), Cost of Removal (Loss/ Gain/ Clearing) and Net Book Value Retired (Gain/ Loss). If you check the first “Assign to Value …” in each of the three flows you’ll see that these route to hard-coded constants:

Figure 6: Process Flow Hard-Coded Values (009) .

If a new branch needs to be created then you will need to copy most of the values from an existing branch (unfortunately there is no “copy/paste” for processes so you need to create new processes by dragging and dropping from the navigator and then set their properties).

2. Configuring Apps to use the New Workflow
After the work flows have been altered and you have uploaded them into Apps you need to configure Oracle E-Business Suite to stop using the Default workflow and switch to the customised version.

To do this you need to connect to Apps and switch to the “Fixed Assets Manager” responsibility. Expand the Financials > Flexfields > Key option in the tree view and select “Accounts” (see below):

Figure 7: Showing the Account Generation Options

Once you have selected this you are presented with an empty grid:

Figure 8: Account Generator Processes

Click on the “Find” button on the toolbar and search for “General Ledger%” and click “Find”. Select the entry with the correct structure (usually the company name)

Click “OK”.

The main Account Generator Process window will now populate with a list of Item Types and Processes. Scroll down and highlight the “FA Account Generator” Item Type:

Figure 9: Selecting the FA Account Generator

Click on the “…” (in the Process Name field) and you are presented with a list of processes from your workflow. Select the correct process (most likely “XXXX Generate Default Account”) and then click the save button on the toolbar.

Read More

Oracle PL/SQL: Searching All VARCHAR2 Fields In A Schema

How frustrating is that? I've entered the data into the Oracle Front-end, the workflow has kicked off, an email has been sent, and *somewhere* in all this mess is a record of an error e-mail being received.

After 4 hours of trying to track down the problem (we actually have 3 emails being received - all "address not found") every 3 minutes ... for the past month and a half. We've got 48,000 emails in the Inbox at the moment. It takes 15 minutes to open in Outlook.

Where do you start?

Now I know the email address that is being used. I also know that the problem persists despite the server being taken down (for backup) every week therefore it *must* be stored somewhere in the database.

The following script runs through every single table in the database that contains a VARCHAR2 column and tries to find a specific string. It takes a while against an Oracle 11i schema (best to leave overnight ... maybe over a weekend if you have that many modules installed!).

declare 
  c_SEARCHTEXT constant varchar2(255) := 'SEARCH TEXT GOES IN HERE';

  cursor c_Tables is 
    select distinct atc.owner, atc.table_name
    from all_tab_columns atc
    where data_type = 'VARCHAR2'
    and DATA_LENGTH >= length(c_SEARCHTEXT)
    and not exists (select 'X' from all_views av where av.owner = atc.owner and av.view_name = atc.table_name);
  
  cursor c_Columns (p_Owner varchar2, p_TableName varchar2) is
    select distinct column_name
    from all_tab_columns
    where owner = p_Owner
    and data_type = 'VARCHAR2'
    and DATA_LENGTH >= length(c_SEARCHTEXT)
    and table_name = p_TableName;
  
  TYPE cv_typ IS REF CURSOR;
  cv cv_typ;
  record_count integer;
  v_SQL varchar2(8124);
begin
  for v_Table in c_Tables loop
    for v_Columns in c_Columns(v_table.owner, v_table.table_name) loop
      v_SQL := 
        'select count(*) ' || chr(13) ||
        'from ' || v_table.owner || '.' || v_Table.table_name || chr(13) ||
        'where upper(' || v_Columns.column_name || ') like upper(''%' || c_SEARCHTEXT || '%'')' || chr(13);
      open cv for
        v_SQL;
      fetch cv into record_count;
      if record_count > 0 then
        dbms_output.put_Line(v_Table.table_name || '.' || v_Columns.column_name || '***** FOUND *****');
      end if;
      close cv;
    end loop;
  end loop;
end;

Now this is unoptimised so it will be sloooooooow. You can always change the initial select to prioritise schemas you are interested in, or add in a "length" check to make sure data of the correct length exists, but the biggest saving will be replacing the "dbms_output" call with something that will send you e-mail messages when it finds something (rather than waiting until the end when it's done!).

Read More