Noetix: Reporting AP Credit Card Transactions

If you are, like the company I work at, a user of both Oracle Payables and Oracle Internet Expenses you'll realise that the line between then is incredibly blurred. Especially if you have the situation where some departments/ areas use i-Expenses but other areas have an Excel-based expenses system (that is then sent to Finance who enter the data directly into Payables).

Oracle have made the situation a little worse by licensing Internet Expenses separately to Payables and it looks very much like Noetix have continued the trend.

What makes the Noetix decision even stranger is that the majority of the data for Internet Expenses is stored in the same tables as Payables in Oracle.

After looking at what was offered by the Noetix "Internet Expenses" module and our own reporting requirements the only "gap" that could be identified was for reporting on Credit Card Transactions (primarily around AP.AP_CREDIT_CARD_TRXNS_ALL).

Our reporting requirements are pretty simple, here is a simple list (with properties) of the columns we'd like to report on related to Credit Card Transactions;

CARD_NUMBER    VARCHAR2(30)
DISPUTE_DATE    DATE
EMAIL_ADDRESS    VARCHAR2(240)
EMPLOYEE_FULL_NAME    VARCHAR2(240)
EXPENSED_AMOUNT    NUMBER
LAST_UPDATE_DATE    DATE
MERCHANT_NAME    VARCHAR2(80)
REFERENCE_NUMBER    VARCHAR2(240)
TRANSACTION_AMOUNT    NUMBER
TRANSACTION_DATE    DATE
TRANSACTION_ID    NUMBER(15)

Converting these requirements into a Noetix View took a fair bit of trial and error but the script is available here. As it's quite a large one I won't be copy/pasting it below (as I normally would).

Any questions/ suggestions feel free to post a comment.

Submitting an e-Petition to Cambridgeshire County Council

This blog post gives simple step-by-step instructions on how to create your first e-Petition on Cambridgeshire County Councils (CCC) e-Petition website.

Go to the website;

http://epetition.cambridgeshire.public-i.tv/epetition_core/community/page/index

Cambridgeshire County Council's e-Petitions Website

Select the "Register" link (top-right, highlighted above);

CCC e-Petition Website Registration Pages

 As an alternative to creating an account you can always just login with your accounts from any of the following;

CCC e-Petition Website Supported Sign-In Providers

You will still have to complete your contact details, but you will be able to use your Twitter/ Yahoo/ OpenID/ Google/ Aol account to login to the website.

Once you have registered (or if you just login on the main screen) you will see the following page;

CCC e-Petitions Website "My Activities" Page

Click on "Add a petition" on the left;

CCC e-Petition Website "Add a petition" Page

 

 

You now need to complete the petition details. The following notes maybe useful;

• What do you want to achieve? If you want to present your petition at a meeting of the Full Council then you should look at the County Councils website and pick an end-date at least 2-weeks before a meeting (but still allowing you enough time to collect signatures)
• Is this just an on-line petition or do you want the ability to submit paper signatures as well? Technically there is no reason why you wouldn't tick *both* check-boxes as you can then just submit zero signatures of the type you don't want to use but if you change your mind later it's a lot easier if you check the box at this stage!
• You will be contacted by an Officer from the County Council within a couple of days of creating your petition. It's vital your email address is up to date (and you might want to check your Spam Folder just in case!)
• If you are submitting a petition on behalf of or organisation or group it's vital that you agree a wording with them in advance - this will solve a lot of problems in the long run
• Stay away from politics (unless that's your aim). Saying how rubbish the Conservative administration is at something is unlikely to convince a) them to change their minds, or b) their supporters to sign your petition. Equally declaring what a triumph Liberal Democrat, Labour, Green, or UKIP policy is in a specific area in the text of your petition is unlikely to motivate people of a different political persuasion to sign it even less the Conservative administration to adopt it! The broader appeal you have in your petition the more likely people are to sign it and circulate it to their friends/ colleagues!

Once your petition has been approved all you need to do now is collect signatures!

SSRS: Removing Blank Pages In Your Reports

If you find yourself writing reports for a truly international audience (i.e. US and EU) then you'll know about the nightmare of paper sizes when your users want to hit "Print" but a larger, and much more significant, issue is the blank pages that sometimes get printed either between or at the end of your reports.

This blog post lists a few things you might want to check to make sure your report is printing on as few pages as possible.

NOTE: If you are having problems printing matrix reports then there is a specific solution for you at the bottom (and an explanation of why you're seeing the blank pages).

Formatting For Printing
If you follow the steps to create a sample report here and then right-click the background of your created report and select "Report Properties";

Report Builder 3: Right-click in the Red Hatched Area for "Report Properties"

This will show you the properties for your report;

Report Builder 3: Report Properties > Page Setup

This is the "default" size created by the wizard. You'll notice that it has defaulted Centimetres, the UK-standard of A4 (as I'm in the UK) and that we have 2cm margins all round. These defaults (at least the Centimetres and A4) seem to be there as I've set them previously.

The major change I make on this screen is changing all the margins to 1.27cm which is just large enough for the printer to handle and gives over a centimetre of extra real-estate both vertically and horizontally. Of course another significant change is to switch the paper layout between Portrait and Landscape if that makes sense for your report!

Now assuming you are using A4, Portrait, and you have changed your margins to 1.27cm then the maximum size for a label you will be able to fit onto a single page is 184.6mm (210mm total width - 2 * 12.7 margins). To show this create a label on the main report, set it's location to 0,0 and make it 184.6mm wide. Put some right-aligned text (I'm using &ReportName) into the label;

Report Builder 3: 184.6mm Wide Label (A4, Portrait)

Now run the report;

Report Builder 3: &ReportName as GUID

It's displaying the &ReportName value as a GUID (I haven't yet saved the report) but the point is that it's some way over to the right. To find out what this looks like on paper click the "Print Layout" button in the ribbon above the GUID;

Report Builder 3: Print Layout

As you can see the GUID is now at the very right of the page and the page count is 1. Just as a quick test if you enlarge the label to 184.7mm (adding just .1mm) and then re-run the report;

Report Builder 3: Page Count Increased To TWO

You can see that the total page count for the whole report has increased to two (with the second page actually looking blank!).

Additional Formatting Problems With Matrix Reports
As soon as you start dynamically adding columns based on new data in the query the risk of getting blank pages, if you are using fixed width headers, dramatically increases.

Once again follow the steps given here to create a report only this time use the SQL;

SELECT 'Red' AS COLOUR, 'Pencil' AS ITEM, 3 AS QUANTITY FROM DUAL UNION
SELECT 'Red', 'Ruler', 11 FROM DUAL UNION
SELECT 'Red', 'Rubber', 4 FROM DUAL UNION
SELECT 'Red', 'A4 Folder', 9 FROM DUAL UNION
SELECT 'Green', 'Pen', 14 FROM DUAL UNION
SELECT 'Orange', 'Pencil', 23 FROM DUAL UNION
SELECT 'Cyan', 'Ruler', 21 FROM DUAL UNION
SELECT 'Orange', 'Rubber', 14 FROM DUAL UNION
SELECT 'Cyan', 'A4 Folder', 17 FROM DUAL UNION
SELECT 'Purple', 'Rubber', 14 FROM DUAL

And on the "Arrange fields" dialog instead of adding all three columns into the "Values" box add Colour to "Row Groups" and Item to "Column groups" as below;

Report Builder 3: Arrange Fields using Column and Row Groups

At the end of this process you will have something like this;

Report Builder 3: A Table With Column and Row Groups

When you run this report and look at the Print Layout (assuming you have selected A4, and 1.27mm margins in the Report Properties dialog - see above) you will get something like this;

Report Builder 3:

The point that might surprise you is if you look at the page count in the ribbon you will notice that it is TWO pages long and if you forward on to the second page it's blank.

If you look at the image below you can see the huge amount of white space to the right and below the table;

Report Builder 3: White Space

Now when the table expands the white space is ADDED so in order to not have blanks to the right and below the table we need to re-size the background and remove as much of it as possible. After re-sizing this becomes;

Report Builder 3: Resized Report Minimising White Space

Now when you run the report and look at the Print Layout you will see that we are back down to a single page.

Now a little "quirk" of how this tidying up takes place is that if you expand the width of the title to 184.6mm and then re-run the report you'll find you're back to two pages. It seems that because the amount of white space is now significant it's actually being added back in again. The easiest way round this is to add a new column to the right of "Total";

Report Builder 3: New Column

Now mark the column visibility as "Hide" and re-run the report;

Report Builder 3: All On One Page

And now you're back to having your report on a single page.

SSRS: Creating a Simple Report With An Embedded Dataset

The purpose of this blog post is to put together a simple guide to producing a "test" report. I'm doing this as a separate post so that I can re-use it in Other Posts (rather than having to include basic setup information every time).

Other Report Builder 3 and select "New ..." to trigger the New Report of Dataset wizard;

Report Builder 3: New Report of Dataset Wizard

"New Report" report will be automatically selected on the left, on the right select "Table or Matrix Wizard" (the top item);

Report Builder 3: New Table or Matrix Report Wizard

At the bottom left check the "Create a dataset" radio group and then click "Next >" at the bottom right;

Report Builder 3: Choose a Data Source

From the list you need to select a Data Source connection to use and then click "Next" at the bottom right;

Report Builder 3: Design A Query

Now you can enter the SQL. As this is just a simple test I'm going to use the following (Oracle) SQL;

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') AS DATE_TODAY,
       TO_CHAR(TRUNC(SYSDATE, 'MM'), 'DD-MON-YYYY') AS DATE_FIRST_OF_MONTH,
       TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1) - 1, 'DD-MON-YYYY') AS DATE_LAST_OF_MONTH
  FROM DUAL

This simple piece of SQL just gives us a single row with today's date as well as the first and last days of the current month. Click the Run button (red exclamation mark above where you entered the SQL) to check the SQL works;

Design A Query: Testing the SQL

Once everything works click "Next";

Report Builder 3: Arrange Fields

The three fields in the SQL we've just added are in the "Available Fields" box on the left of the dialog. Drag/Drop them into the "Values" box at the bottom right;

Report Builder 3: Arrange Fields

Click "Next";

Report Builder 3: Choose the Layout

We don't want to make any changes here so just click "Next";

Report Builder 3: Choose a Style

Similarly here we don't want to make any changes so just click "Finish".

The report has now been completely generated and you will be presented with something similar to;

Report Builder 3: Fully Generated Report

Click on "Run" at the top left (to test the report);

Report Builder 3: Testing Generated Report

And you're done ....

SSRS: Adding Calculated Fields To Data Sets

This blog post covers an example of how to add a simple calculated field to a Dataset in SQL Server Reporting Services using Report Builder 3 (against SQL Server 2008R2). It isn't a massively complicated process except that Microsoft seem to be assuming absolutely no-one does this as all the "nice" features of expressions when working with Reports (i.e. being able to select fields by double-clicking a list) seem to be unavailable.

The easiest thing to do is to start Report Builder 3 and select "New Dataset" from the Getting Started wizard;

Report Builder 3: Getting Started Wizard

Select and active data source connection (for the purposes of this example I'll be connecting to an Oracle data source). Once you've selected a data source you'll be presented with the editor window;

Report Builder 3: SQL Editor Window

Now enter the following simple SQL;


SELECT 1 AS VALUE1,
 2 AS VALUE2
FROM DUAL

This is a very simple piece of SQL that will just return a single row with two columns called VALUE1 and VALUE2 which contain the values 1 and 2;

Report Builder 3: Sample SQL with Result

Now we are going to add two Calculated Columns;

• VALUE3 = VALUE1 + VALUE2, and
• VALUE4 = VALUE1/VALUE2

Click on "Set Options" (in the ribbon bar at the top of the window);

Report Builder 3: Shared Dataset Properties

Click "Add" and select "Calculated Field" from the drop down that appears;

Shared Dataset Properties: New Field

Enter the values;

• VALUE3 (Field Name)
• =Fields!VALUE1.Value + Fields!VALUE2.Value (Field Source)
• VALUE4 (Field Name)
• =Fields!VALUE1.Value / Fields!VALUE2.Value (Field Source)

And click "OK" to apply the change.

Re-run the query and you'll notice that your two new fields are NOT being displayed. This is slightly unexpected but if the expression you have entered is incorrect you will see an error message like;

Report Builder 3: Invalid Expression Error

The actual text of the error message is;

The Value expression for the field ‘=Fields!VALUE1.Value1 / Fields!VALUE2.Value’ contains an error: [BC30456] 'Value1' is not a member of 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Field'.

You'll notice that I just added a "1" after the Value property name.

Now you've saved the dataset close and re-open Report Builder and this time from the wizard select "New Report" and then "Table or Matrix Wizard", select the dataset you've just saved, click "Next" and you will see;

Report Builder 3: New Table or Matrix Wizard Screen

Which shows the four available fields. Select them all (i.e. move them into the Values box) and then click "Next" all the way through to the generated report and then run it;

Report Builder 3: Successfully Showing Four Fields

Now, as a final check, if you go through into the Dataset Properties of the Dataset you've just added and look at the fields;

Report Builder 3: Dataset Properties

You can see that the new fields have been added from the Dataset and that the calculation is being done in the dataset (otherwise Field Source would be an expression).

Google Knol: Final Farewell ...

So that's it, I've just migrated the last one of my Google Knols back to Blogger (where I started creating posts many many years ago). To be honest it's a great shame - I can't see how with Googles goals to get the whole of human knowledge online they can justify closing that service. It was convienent, easy to use, and gave me the feeling that I was contributing to something bigger than, say, a blog.

The bit I find most surprising is the lack of loyalty Google seems to be expecting from the many people who used Google Knol. I for one don't have any interest in transferring the documents I have spent literally years creating to a non-Google service. Even one recommended by Google. Not providing a migrating path to Blogger is incredible - I have literally no idea what they were thinking!

Anyway, here's to you Google Knol, it was fun while it lasted and you will, in my household at least, be sorely missed ...

Oracle EBS: Creating New Menu Items in Oracle e-Business Suite

NOTE: Don't do this on a production environment. Did that need saying? Apparently one person who submitted a comment seemed to think so ... You really can completely mess it up. Run it on a test environment and MAKE SURE IT WORKS before you run it anywhere else.The script below was written against 11i, I would be loathe to run it against a different version. The point of this post is to allow you to script a change to 11i - you might be better off just doing this in the UI if you're not managing lots of instances. Anyway ... You have been warned!


This blog post takes you through a step-by-step guide to how to add a new menu item (that will punch out to this Knol) to the root menu of an existing responsibility using the Oracle API's (so the change can be scripted rather than done in the forms).

A completed example script (with error checking and reporting) is included.


Step 1: Getting The Responsibility Details
In order to add a new menu item you need to know which set of menus your responsiblity is currently using. To find this out you need to go into the e-Business Suite and choose the "System Administrator" Responsibility and then under Security > Responsibility choose "Define".

Search for the Responsibility you wish to use. For the purposes of this example I'm going to use the "Alert Manager" responsibility as it should be one that is installed on every instance and will have a fairly limited user base.

When you view the Responsibility you will see something like this;

Figure 1: "Alert Manager" Responsibility

The important piece of information on this screen is the "Menu" (in the middle). You can see that the responsibility is using the "ALR_OAM_NAV_GUI" Menu as it's root. We'll add the new menu item in here.

Stage 2: Adding a Simple HTML-redirect Script To Oracle
See Linking Directly to Microsoft Reporting Services from Oracle e-Business Suite (Stage 2)

Stage 3: Creating a Function using FND_FORM_FUNCTIONS_PKG.INSERT_ROW
This API provides a quick way of creating records in the FND_FORM_FUNCTIONS set of tables, in order to use the API you first need to get a new ID from the the FND_FORM_FUNCTIONS_S sequence. As we're going to be doing nothing more than a simple punch-out to Google the API call will look something like this;

fnd_form_functions_pkg.insert_row(
  x_rowid                    => v_RowID,
  x_function_id              => v_Id,
  x_web_host_name            => null,
  x_web_agent_name           => null,
  x_web_html_call            => 'verysimpleredirect.html?redirect=http://knol.google.com/k/andy-pellew/creating-new-menu-items-in-oracle-e/',
  x_web_encrypt_parameters   => 'N',
  x_web_secured              => 'N',
  x_web_icon                 => null,
  x_object_id                => null,
  x_region_application_id    => null,
  x_region_code              => null,
  x_function_name            => 'GOOGLEKNOL',
  x_application_id           => null,
  x_form_id                  => null,
  x_parameters               => null,
  x_type                     => 'JSP',
  x_user_function_name       => 'Google Knol Viewer', --:x_user_function_name,
  x_description              => 'Google Knol Viewer', --:x_description,
  x_creation_date            => sysdate,
  x_created_by               => 0,
  x_last_update_date         => sysdate,
  x_last_updated_by          => 0,
  x_last_update_login        => -1,
  x_maintenance_mode_support => 'NONE',
  x_context_dependence       => 'RESP',
  x_jrad_ref_path            => null);

I've called the function "GOOGLEKNOL" and it's being created by the System Administrator (if you look in FND_USER it's ID 0). If you have disabled this user then it's best if you create it as someone else. You can always use your ID but I prefer to distance myself from these created objects (it's one less thing to worry about if I ever choose to leave my job and have to hand all this over to someone else!).

Unfortunately there seems to be a bug with this API in the the "Type" (FND_FORM_FUNCTIONS.TYPE) does not appear to be being written correctly into the database. In order to fix this you need to do a SQL update;

 update applsys.fnd_form_functions t
     set type = 'JSP'
   where function_id = v_ID;

Where v_ID is the ID you retrieved from the sequence earlier.

Stage 4: Associating Function with Existing Oracle Menu
This uses the FND_MENU_ENTRIES_PKG.INSERT_ROW API as published by Oracle to hook together the new menu item with the existing menu. In stage 1 we learnt that the menu we wish to alter is called "ALR_OAM_NAV_GUI" and by querying the FND_MENUS and FND_MENU_ENTRIES tables we can get the Menu ID and the next available menu sequence number as follows;

select fm.menu_id, max(entry_sequence) + 1
    from fnd_menus fm, fnd_menu_entries fme
   where fm.menu_name = 'ALR_OAM_NAV_GUI'
     and fm.menu_id = fme.menu_id
   group by fm.menu_id;

Using these values we can call the API;

fnd_menu_entries_pkg.insert_row(
  x_rowid             => v_RowID,
  x_menu_id           => v_MenuId,
  x_entry_sequence    => v_EntrySequence,
  x_sub_menu_id       => null,
  x_function_id       => v_Id,
  x_grant_flag        => 'Y',
  x_prompt            => 'Google Knol Viewer',
  x_description       => 'View Google Knol',
  x_creation_date     => sysdate,
  x_created_by        => 0,
  x_last_update_date  => sysdate,
  x_last_updated_by   => 0,
  x_last_update_login => -1);

Now we have created all the records we're almost there.

Stage 5: Running the "Compile Security" Concurrent Request
This is performed using the FND_REQUEST.SUBMIT_REQUEST concurrent request API;

apps.FND_REQUEST.SUBMIT_REQUEST(
  application => 'FND',
  program     => 'FNDSCMPI',
  argument1   => 'No')

This is function so you'll need to do something with the returned value.

Summary
After completing these steps you'll find that when you log-in and switch to the "Alert Manager" responsiblity you will have a new menu item and clicking on that will bring up this Knol;

Figure 2: Completed System 

A script to perform these changes automatically (with additional error checking and report) is available by clicking here.