Added new page with Excel Reports

New page with excel reports added:

Excel Reports

Pls. try !!!

Posted in Applications development, Excel Reports, Excel tools, SQL, xls4OracleSQL | Comments Off on Added new page with Excel Reports

xls4OracleSQL is in use as Excel Reporting tool in a validated environment

The xls4Oracle is now in use as a reporting tool.

New features in xls4OracleSQL, is enabling the build of Excel Reports, using xls4OracleSQL. The Excel files is store in a SharePoint folder, with restricted access.

The ProtectData parameter enables protection of both sql sheet and the datasheet, ensuring the report is not altered.

The SmartFormat feature optimizes the column with and heading height to make column width as small as posible for columns with short data and long headings.

The MaxColWidth enabling to define the max width for Long data. Do you want wide columns with fewer line breaks or narrower columns with more line breaks.

New features has been added to enable post formating for PDF or XPS.

Pls. try it out!!!

Thanks for reading…

Posted in Excel Reports, Excel tools, SQL, xls4OracleSQL | Comments Off on xls4OracleSQL is in use as Excel Reporting tool in a validated environment

New Excel tool sheet added for Google Earth Area calculations

A new Excel tool has been added – the xls4Oracle Google Earth Area.

With the Excel tool you can copy / paste the polygon kml data from Google Earth into Excel. A macro will calculate the area and insert a line into the sheet, with area, perimeter and latitude/longitude of the polygon.

About the Area calculation
The Area calculation is done by summarizing all triangles, defined by the starting point and two following points. As long as bearing from last point is higher than previous point triangle area is added. If bearing of the last point is less than the previous point, the triangle area is subtracted.
In the figure below, the starting point is the lower left corner. The first two triangels are positive, the third is negative (because of the counterclockwise bearing change) and the fourth is positive. The tricky triangles are number 2, 3 and 4 – number 2 and 3 include area outside the polygon, but that area is eliminated when triangle number 3 is subtracted. You get the same result by counting the squares. The same rule applies for spheric surfaces like the earth. Triangle sides is calculated as great circle distances.

xls4Oracle Google Earth Area #4

Posted in Excel tools | Comments Off on New Excel tool sheet added for Google Earth Area calculations

Duplicates in a Query

Here is a way to find out why you have two rows in a select, when you only expect one.

In good database design you expect that a unique ID is giving you only one record. But many tables Oracle E-Business Suite has  composite primary keys – like:

– mtl_system_items – pk = iventory_item_id + organization_id

– per_all_people_f – pk = person_id + effective_start_date + effective_end_date


So what to do when you have a query with duplicates. First try to reduce the query to show as few row as possible, the optimum is 2 rows. Then copy the sql into a new xls4OracleSQL sheet.

– Set the Layout = V for vertical.

– Replace the hole select part with select *

– Convert table to range, if data is in a table.

– add a column after the last column, formula “=A3=B3”. This will show TRUE if values are the same and FALSE when values are different, and we want to find those that are different.

– copy formula to all the other lines

– insert table or apply filter

– filter on false

You can now see the columns that differs, and this should enable you to add the needed where clauses to get rid off the duplicates.

Posted in Applications development, SQL, xls4OracleSQL | Comments Off on Duplicates in a Query

Oracle ODAC: NLS_LANG in registry

The Oracle ODAC ( installation is creating an NLS_LANG entry in here:


And that has no effeckt – it should be placed in an oracle_home – like:


The result of having the NLS_LANG in the wrong place is special characters like JP, trademark, copyright and local country characters is shown as question marks.

To fix the issue you need to create the NLS_LANG entry in the oracle_home. We found that, with the database NLS_LANG = AMERICAN_AMERICA.AL32UTF8, client NLS_LANG = AMERICAN_AMERICA.UTF8 – and all characters is now shown correctly.

Posted in Applications development, Installation, News, SQL, xls4oracle | Comments Off on Oracle ODAC: NLS_LANG in registry

mo_global.init raises insufficient privileges

To complete an apps_initialize from a readonly user, the readonly user needs this grant:

grant select, insert, update, delete on mo_glob_org_access_tmp to ;

This has been detected on an 12.1.36 installation with a readonly solution applied.

Posted in Applications development, Developer Scripts, News, R12, SQL | Comments Off on mo_global.init raises insufficient privileges

New features in xls4OracleSQL 5.0

The xls4OracleSQL 5.0is ready to be released soon and will include some new features:

Clever formating of the SQL output. New parameter: MaxColWid enables control of how wide a column may be, before wrap is enabled. Heading width is included in the calculation of the column width. The Heading Wdth is based on
longest word of each the column heading.

Password protection
enables refresh – but alternation of the SQL results in a reset of the password.

Title with datetime
New parameter title that can include the substitution variable $datetime. The datetime is set at initialization and can be used for the title and filename generation.

Parameter form
Several updates has enabled the Parameter form feature. This enables the creation of userfriendly excel sheets, with easy to understand parameters and still using standard Excel features to build the SQL.

The PLSQL feature will be found as a separate installation, requiring separate license keys.
All excisiting users of the xls4Oracle tool, can upgrade to release 5.0 and get two new license keys FREE.

Posted in News, Revision History, SQL, xls4oracle | Comments Off on New features in xls4OracleSQL 5.0

xls4OracleSQL – release 12-apr-2015

The sql4OracleSQL tool is developed for distributed reporting.

Reports developed is “frozen” to the sql.  If sql is changed, the excrypted database password is reset. This enables restricted access to the database and forces the distributed xls4OracleSQL report only to be used for the initial purpose.

Here is the lastest updates: 14-mar-2015: xls4OracleSQL created - New release - Improved sql reporting 26-mar-2015: Added title and improved formating
                       Added print formating
                       Added PDF print 01-apr-2015: bug fixes 06-apr-2015: Added output tab 12-apr-2015: Enabled version check

Multiple new features added:

– title with $datetime – enables to include the same date/time to title as to the output files (pdf and xps).

– improved formating: When heading is wide and data is short – column is adjusted to a minimum width. Long text / wide columns is changed to wrap around and a maximum width.

– PDF and XPS print. Enabled generation of either PDF or XPS files.

– New output tab for the PDF and XPS output controls.

– Added the “Delete Hidden” checkbox. Enables to delete hidden columns.

– Enabled the version check. Once a day the xls4Oracle installation is checking for updates. If a new update is available, a message box is presented showing information about the new version.


Posted in Uncategorized | Comments Off on xls4OracleSQL – release 12-apr-2015

New xls4OracleSQL tool released

The xls4OracleSQL tool enables distribution of reports.

To enable this, some new fetures has been added: Password is protected and connected to the sql. Meaning: if the sql is changed, the password is being reset, and you have to enter the password again. This enables you to distribute xls4OracleSQL reports to you organization, and enables the receivers to re-run the SQL, without having the password, and as long as the SQL has not been changed.

Another feature is that the report receivers can enter new parameters and re-run the report without knowing the password, as long as the core sql has not been changed. This is done by adding sections markings to identify the core sql section and the parameter sql part.

An other new feature has been added: You can now have a parameter table on the sql sheet. In combination with hiding headings, columns and rows, this enables creation of very user friendly and intuitive reporting worksheets – Open the report worksheet, enter parameters on the sql sheet, press the xls4OraceSQL button and reports is generated immediately.

Posted in Applications development, News, SQL, xls4oracle | Comments Off on New xls4OracleSQL tool released

apps initialize needs fnd_global.bless_next_init

The ai.sql script was suddently not working anymore… – apparently after some patching.

But if you add a fnd_global.bless_next_init statement – it will.

I will soon update the ai.sql in the “sql script tools

Posted in Applications development, R12, SQL | Comments Off on apps initialize needs fnd_global.bless_next_init