Oracle EBS: Java "Freezing" When Starting An Oracle Form

This is a very tricky problem and certainly not something you'd want to see when rolling out the Java update across your company. The error manifests itself by a "Warning - Security" dialog appearing and not allowing the user to click on anything on it;

"Frozen" Warning - Security Java Dialog

The only way to proceed beyond this point is to kill the Java process using Task Manager;

Windows Task Manager - Showing the java.exe process

Right-click the java.exe process and choose "End Process" (and then again on the dialog that appears).

Shutdown all Internet Explorer windows.

Start the "Java Control Panel" and select the Advanced tab and expand the "Java Plug-In" node in the tree view;

Java Control Panel - Advanced Tab - Java Plug-in Node Expanded

Check the checkbox and click "Apply" (you will need an Admin account in order to save the change), when it's saved you will see the following dialog;

Java Plug-In Settings Change

Now un-check the checkbox and click "Apply" (again you will need an admin account) and again you will see the settings change dialog.

Start Internet Explorer, Log into Oracle, and select a Form-based Function;

Oracle Form Displayed within Oracle E-Business Suite

Which should now be working correctly.

Read More

Oracle PL/SQL: Working With Oracle Jobs (Showing/ Deleting/ Adding)

Showing All Oracle Jobs
To see a list of the currently configured Oracle Jobs use the SQL;

SELECT job,
       schema_user,
       last_date,
       last_sec,
       next_date,
       next_sec,
       broken,
       interval,
       failures,
       what
  FROM dba_jobs
 ORDER BY next_date DESC

This will produce something that looks like (in SQL Developer);

Query Results in Oracle SQL Developer

The interesting parts of this statement are the two fields BROKEN and WHAT. If the job has failed to run (i.e. raise a PL/SQL error) then it's BROKEN status will be set to Y (as you can see in the top-row of the result set image above).

The WHAT field contains the SQL that the job is going to periodically run - this is stored as a large object so you might want to use an editor like SQL Developer or PL/SQL Developer to display it correctly.

Removing An Existing Job
To remove a job you need the job_id from the first query and then execute;

exec dbms_job.remove(job);

Where "job" is the value from the first query (a unique reference for the job). You then need to do a commit.

Adding A New Job
Let's assume we have the following PL/SQL block we'd like executed at 8am every day;


BEGIN
  FOR v_Row IN (SELECT SYSDATE FROM DUAL) LOOP
    dbms_output.put_line(v_Row.sysdate);
  END LOOP;
END;

This is a pretty simple PL/SQL block that will jsut write todays date to the DBMS_OUTPUT queue. Basically it won't do anything except run without an errors.

To schedule this as a job execute the following PL/SQL;



declare
  v_Job BINARY_INTEGER;
begin
  sys.dbms_job.submit(job       => v_Job,
                      what      => 'BEGIN FOR v_Row IN (SELECT SYSDATE FROM DUAL) LOOP dbms_output.put_line(v_Row.sysdate); END LOOP; END;',
                      next_date => TRUNC(SYSDATE) + 1 + 8 / 24,
                      interval  => 'TRUNC(SYSDATE) + 1 + 8/24');
end;

The only required parameters for the DBMS_JOB.Submit function are the output parameter (for the Job reference) and the what. Everything else has defaults.

Read More

Configuring Wireless Networking On Dell Latitude D600 (Under Windows XP)

I'm not sure if it's incorrect configuration on the Dell website but solving this one seems to be massively more complicated than it needs to be. The solution below worked for me but I'm afraid the "pick the correct wireless driver" part of the solution just seems to be based on a guess (unless you want to unscrew your laptop and read the version information off the physical card!).

Anyway, the problem is that under "Network Connections" on Windows XP there is no option allowing you to configure your wireless network;

Windows XP Network Connections - No Wireless Options

The reason it's not there is that the drivers haven't been installed so the wireless card is not being detected by the operating system.

This is made a little more confusing because if you've entered your Service Tag on the Dell website and got a list of the drivers available in my case the Wireless Network Driver was *not* included in the list. As the machine is quite old I'm guessing this is just down to different configurations shipping and somehow it not all tallying up correctly when the data was loaded by Dell.

The first step therefore is to download the drivers.

Go to the Dell Support website;

http://support.dell.com/support/downloads/

This is the US site, if you look in the top-left corner you'll see the Country - you need to select the Country for which the laptop was produced. In my case this is "United Kingdom", you will then be re-directed to your local support site.

You will then be prompted for some information about your system;

Dell System Prompt

I tried the "Express Service Code" option and that didn't find the Wireless Drivers (in fairness to Dell this is the first and *only* time I've had a problem with using the Code!) so if choose the options;

Choose from a list of Dell products > Laptops > Latitude > Latitude D600

Then you are presented with a web page similar to;

List of all files for Latitude D600

This is a list of all the drivers available for the system. Make sure "Windows XP" is selected under "Operating System" and expand the "Network" list.

Now this is where you need to know the make/model of your Wireless Adapter. If you don't (and I didn't) then the "best" one to select is the Intel Driver below;

Wireless Drivers (from Dell Website)

The reason for picking this one is that it seems to be a more "generic" driver that works with many different adapters so the chance of it working for you is slightly better than picking any of the others that seem to only work for a specific version - that's my logic anyway!

Once you've downloaded and installed this driver (and re-booted your system) then you will have the Intel Wireless Management software at the bottom right on your control panel.

You should now be able to work with Wireless.

Read More

Apple TV (2nd Generation) Buyers FAQ

Q: It says it does HD, is that 1080p?
A: No. It does 720p. There has been a lot of speculation recently about Apple launching "HD+" which would be 1080p content in the iTunes Store. At the moment (December 2011) this is just speculation and nothing has been released yet. It is unlikely that Apple TV will start doing 1080p before Apple has started selling content.

Q: Does it work right out of the box?
A: Yes - and no. You need to have a home network on which there is at least one machine which is running iTunes with home-sharing turned on, the Apple TV needs to then connect to the same network and be able to see this machine. You also need an HDMI cable.

UPDATE (Dec-11): If you have purchased either Music or TV Programmes (*not* Films) from the iTunes Store then these will be available as soon as you start-up your Apple TV and it connects to a network which has access to the internet.

Q: Can I put "x" from my iTunes library on it?
A: No. That's not how it works. The Apple TV streams media from another source (typically your iTunes library, iCloud, or one of the (few) supported internet services like YouTube, Vimeo, etc, or from AirPlay supported devices like the iPhone, iPad, and iPod Touch) it has a very small amount of local storage for buffering but you cannot "put" an item on that storage for viewing later.

Q: Can I use it as an external hard drive for media?
A: No, that's not how it works either - see above.

Q: Does it always have to be connected to the internet?
A: No. But it always has to be able to see it's source. As an example I took it, my wireless router, and my laptop running iTunes and my iPhone on a caravan holiday in South Wales. Despite the wireless router not having an ongoing connection to the internet the Apple TV was able to play streamed media from both the laptop and the iPhone when they were all connected to the router.

Q: Does it have to use Wi-Fi?
A: No, it has an ethernet port (and that's how I use mine). For performance reasons I find it works better with a wired connection (my wireless is provided by a Virgin Media Wireless-N box).

Q: Can I watch BBC iPlayer on it?
A: Directly No, but you could for example use Airplay to mirror iPlayer content  from either an iPad or an iPhone to it.

Q: Can I use my iPhone, iPod Touch, or iPad as a remote control?
A: Yes, just install the Apple Remote application from the iTunes store and connect the device to the same network (so it can see the Apple TV) and turn on Home-sharing in the remote app.

Q: Does it play DVD's?
A: No. There is no DVD slot for a start. If you have the time and inclination you could use some software like HandBrake to convert your DVD's, add them into iTunes and then stream them to the AppleTV but the honest answer has to be "No".

Q: So would you recommend one?
A: Yes. Wholeheartedly.


So what other questions would people considering buying an Apple TV want to ask? Leave a comment and I'll try both to answer questions and to keep this FAQ up to date.

Read More

SSRS: Solving ORA-01795 (Maximum Expressions in a List is 1000)

This blog post shows a simple method of solving the problem of Oracle only allowing 1,000 records to be passed into an IN statement. When you try and pass in 1001 you get the error message;

ORA-01795: maximum number of expressions in a list is 1000

This is no way round this issue from an Oracle view point, the only solution lies in not passing in so many values.

Now in SSRS the most common way this bug has exhibited itself is when you have a drop down list (of Vendors for example) and the user wants to see either a handful of specific vendors or all of them. The user triggers the drop down, hits "Select all" (a check box appears next to every item), submits the report and then gets the error.

The key to solving this problem is the "Select all". If the user has selected all records then rather than passing in the full list if we passed in a single value that the dataset could then interpret as meaning "give me everything".

Looking at our original WHERE-clause in the SQL;

...

AND NID.VENDOR_ID IN (:Vendor_ID_List)

If we change this to;

...

AND (-1 IN (:Vendor_ID_List) OR NID.VENDOR_ID IN (:Vendor_ID_List))

Then if the values we pass in include -1 we will get everything.

Next we need to modify the SSRS report to pass in -1 when the user has selected every value. Open the report and bring up the "Dataset Properties" and click on "Parameters" on the left;

Dataset Properties | Parameters

Click on the "function" button next to the parameter and replace the text;

=Parameters!VENDORLIST.Value

With the function;

=IIF(Parameters!VENDORLIST.Count > 1000, -1, Parameters!VENDORLIST.Value)

Now the one issue with this is that, occassionally, users will want to see everyone EXCEPT one or two vendors. Clearly with the SQL above if you select 1001 of your 1003 Vendors then it will be treated as "All" for the purposes of reporting.

The solution we have found for this is to add another parameter called "Excluded_Vendor_Id_List" which works in very much the same way but allows users to specify a list of Vendors to exclude from the report. Not the most elegant solution (and it does increase the report complexity) but it does work.

Read More

Noetix: Manually Rebuilding Cached KFF Data

Sometimes it's necessary to rebuild the cached KFF data that Noetix uses to speed up it's queries. These are typically the tables under the NOETIX_SYS schema that start with KFF_ followed by a code representing the data to be refreshed (i.e. GL_ACCT for General Ledger Account Code Combinations).

This refresh can take quite a time and it's useful (if you are in a highly controlled environment) to be able to hand over to people some statistics regarding the rebuild. Hence the script below.

The script looks at the packages that have been built under your Noetix schema and then into the tables the packages update to determine the current number of rows then it performs the initial upload (a full refresh) and then re-checks the number of records in the table - hopefully this will have changed!

Here is the script;

declare
  -- Local variables here
  v_Item varchar2(255);
begin
  -- Test statements here
  for v_Data in (SELECT ao.object_name,
                        SUBSTR(ao.object_name,
                               7,
                               LENGTH(ao.object_name) - 10) item_name
                   FROM all_objects ao
                  WHERE ao.owner = 'NOETIX_SYS'
                    AND ao.object_name LIKE 'N_KFF_%_PKG'
                    AND ao.object_type = 'PACKAGE') loop
    dbms_output.put_line(RPAD('===== ', 60, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('Processing ' || v_Data.item_name);
    dbms_output.put_line('  - Package name      : N_KFF_' ||
                         v_Data.item_name || '_PKG');
    dbms_output.put_line('  - Data Table name   : N_KFF_' ||
                         v_Data.item_name);
    EXECUTE IMMEDIATE 'SELECT TO_CHAR(COUNT(*)) FROM N_KFF_' ||
                      v_Data.item_name
      INTO v_Item;
    dbms_output.put_line('  - Cache Table Count : ' || v_Item || ' (BEFORE)');
    dbms_output.put_line(RPAD('== Initial Upload == ', 20, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    EXECUTE IMMEDIATE 'BEGIN N_KFF_' || v_Data.item_name ||
                      '_PKG.Init_Upld(); END;';
    dbms_output.put_line(RPAD('== Initial Upload == ', 20, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('  - Cache Table Count : ' || v_Item || ' (AFTER)');
    dbms_output.put_line(RPAD('===== ', 60, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
  end loop;
end;

And here is a sample of the output generated on our system;


===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
Processing ACCALIAS
  - Package name      : N_KFF_ACCALIAS_PKG
  - Data Table name   : N_KFF_ACCALIAS
  - Cache Table Count : 1 (BEFORE)
== Initial Upload ==16-DEC-2011 15:46:31
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_AccAlias with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 9
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:46:31
  - Cache Table Count : 1 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
Processing CTLG_GRP
  - Package name      : N_KFF_CTLG_GRP_PKG
  - Data Table name   : N_KFF_CTLG_GRP
  - Cache Table Count : 0 (BEFORE)
== Initial Upload ==16-DEC-2011 15:46:31
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Ctlg_Grp with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 23
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:46:31
  - Cache Table Count : 0 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
Processing GL_ACCT
  - Package name      : N_KFF_GL_ACCT_PKG
  - Data Table name   : N_KFF_GL_ACCT
  - Cache Table Count : 639234 (BEFORE)
== Initial Upload ==16-DEC-2011 15:46:31
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_GL_Acct with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 655
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:47:37
  - Cache Table Count : 639234 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:37
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:37
Processing ITEM_LOC
  - Package name      : N_KFF_ITEM_LOC_PKG
  - Data Table name   : N_KFF_ITEM_LOC
  - Cache Table Count : 30845 (BEFORE)
== Initial Upload ==16-DEC-2011 15:47:37
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Item_Loc with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 37
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:47:38
  - Cache Table Count : 30845 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:38
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:38
Processing MTL_CAT
  - Package name      : N_KFF_MTL_CAT_PKG
  - Data Table name   : N_KFF_MTL_CAT
  - Cache Table Count : 1740 (BEFORE)
== Initial Upload ==16-DEC-2011 15:47:38
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Mtl_Cat with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 46
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:47:40
  - Cache Table Count : 1740 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:40
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:40
Processing SYS_ITEM
  - Package name      : N_KFF_SYS_ITEM_PKG
  - Data Table name   : N_KFF_SYS_ITEM
  - Cache Table Count : 690403 (BEFORE)
== Initial Upload ==16-DEC-2011 15:47:40
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Sys_Item with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 189
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:48:35
  - Cache Table Count : 690403 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:48:35

Read More

Noetix: Obsolete Views at Release 12 (With Suggested Replacements)

Below is a list of the obsolete Noetix Views at Release 12 (along with their suggested replacements). We aren't using all the Noetix modules so there might be others but the list below covers the following roles (from the generated Noetix help);

AOLAP_AOL
XXNAO_NOETIX_ADMIN
FA_FIXED_ASSETS
GL_LEDGER
AP_PAYABLES
AR_RECEIVABLES
OE_ORDER_ENTRY
PO_PURCHASING
XIN_INVENTORY
GMD_PRODUCT_DEVELOPMENT
GME_PROCESS_EXECUTION
GMF_OPM_FINANCIALS
GMI_PROCESS_PLANNING
INV_INVENTORY
XXK_KEY_FLEXFIELD

Obsolete views where tracked by looking for views with "obsolete" in their help description at R12. It's worth noting that base views don't have help information and so are never "formerly" listed as obsolete. The inclusion of GMF_Order_Details_Base in the list below was the result of one of our developers using it and us discovering subsequently that it was obsolete.

Similarly the recommended replacement view is from the help text provided or, if nothing useful is forthcoming, from my/our experience;

Views Without any Known Replacement

• GMP_MRP_Mtrl_Unbucketed
• GMP_MRP_Demand_Supply_Dtl
• GMP_MRP_Action_Msg_Pegging
• GMP_Action_Messages
• GMP_Warehouse_Transfer
• GMP_Warehouse_Rules
• GMI_Production_Rules
• GMI_Onhand_Inv_Rollup
• GMI_Lots_Sublots
• GMI_Item_Catalogs
• GMI_Inv_Unallocated_Rollup
• GMI_Inv_Stock_Locators
• GMF_Test_Subledger
• GMF_Cost_Warehouse_Assoc

Views with available replacments

• GMP_Forecast_Details > INV_Forecasts
• GMI_Unallocated_Inventory > INV_Unallocated_Inventory
• GMI_Onhand_Inv_By_Lot > INV_Item_Onhand_By_Lot
• GMI_Onhand_Inv_By_Location > INV_Onhand_Quantities
• GMI_Month_End_Inventory > INV_Period_Close_Details
• GMI_Item_master > INV_Items, INV_Item_Inventory_Attributes, and INV_Category_* views
• GMI_Inv_Transactions > INV_Transactions and INV_Transaction_Details
• GMF_Update_Subledger > GMF_SLA_Cost_Subledger
• GMF_Cost_Warehouse_Assoc > GMF_Cost_Organization_Assoc
• GMF_Order_Details_Base > GMF_SLA_Cost_Subledger

The changes that really made life difficult for us were the GMI_Item_Master being replaced, this dramatically increased the complexity of several previously simple reports, the GMI Onhand view changes, this broke a lot of reports, and while the mapping between GMI_Inv_Transactions and the replacement views wasn't too bad the changes behind the scenes meant that a lot of reporting logic had to change.

I hope this is of use!

Read More

Noetix: Expanding Basic Forecasting Functionality in R12

This blog post contains details of how to expand the basic Noetix Forecasting template (INV_Forecasts) to add in some of the nice new functionality in R12 such as grouping forecasts into Sets (i.e. all export forecasts could be in a set called EXPORTS and domestic forecasts one called DOMESTIC).

Looking at the existing Noetix view it accesses the Forecast data in a single table (MRP.MRP_FORECAST_DATES). The MRP Forecasting schema contains the additional tables MRP_Forecast_Designators (description and set information), MRP_Forecast_Items, MRP_Forecast_Updates, etc.

This change will add in the set and forecast description from the MRP_Forecast_Designators table. Looking at the possible joins between the two the easiest is;

This simply joins the two tables on the Forecast_Designator and Organziation_Id  if they exist in the designators table.

Looking at the INV_Forecasts queries that make up this view using the SQL;

select n.view_label || ', ' || 

       to_char(n.query_position) || ', ' ||
       nvl(n.union_minus_intersection, 'null') || ', ' || 

       n.view_comment "label,position,umi,comment"
  from n_view_query_templates n
 where n.view_label = 'INV_Forecasts'

Shows the following results;


label,position,umi,comment
INV_Forecasts, 1, null, forecast with day buckets
INV_Forecasts, 2, UNION ALL, forecast with week buckets
INV_Forecasts, 3, UNION ALL, forecast with period buckets

In order to get the view working we need to insert the new tables and columns into each of the queries (in effect doing the same thing three times).

Using the XU2 column addition generation script I have blogged about previously with the values INV_Forecasts, Forecast_Designator, and Y (include tables) gives you a script that can be quickly and easily edited.

As I'm sure you're aware you need to add in the table prior to adding in the columns and that each of the column addition scripts needs to be in a separate file. Because of this I'm going to add the table in the script which adds the Forecast_Description column.

The changes required to add the new table (and columns) to the view are;

In the SELECT;

• MFD.Description Forecast_Description,
• MFD.Disable_Date Forecast_Disable_Date
• MFD.FORECAST_SET Forecast_Set

In the FROM clause;

• MRP.Mrp_Forecast_Designators MFD

And finally in the WHERE clause;

• AND FODAT.Forecast_Designator = MFD.Forecast_Designator(+)
• AND FODAT.Organization_Id = MFD.Organization_Id(+) 

The three files are (all files stored in Google Docs);

inv_forecasts_forecast_description_xu2.sql

inv_forecasts_disable_date_xu2.sql
inv_forecasts_forecast_set_xu2.sql

Whilst we're here I'm going to add another two files to show who created the Forecast and when they did it (which I tend to find always useful - especially when identifying people to test changes!).

inv_forecasts_created_by_xu2.sql
inv_forecasts_creation_date_xu2.sql

You'll notice if you look in the "created_by" file that I'm using the AUTOJOIN column type in Noetix to quickly get this information. It can be a quick way of getting one field from another table but it does create problems if, for example, I wanted two fields (like the name and email address) and can introduce interdependencies between files that might cause problems if Noetix ever changes the way it works.

The next three changes I'm going to suggest are  replacing the existing Forecast_Quantity column with the Current and Original Forecast Quantities from the forecast table. It is, frankly, rubbish to try and hide these columns and I feel represents a complete lack of understanding on how companies use forecasting; "How close are we to the forecast?" is a frequent question and so hiding the columns just makes absolutely no logical sense. To make this change requires three files;

inv_forecasts_current_forecast_quantity_xu2
inv_forecasts_original_forecast_quantity_xu2
inv_forecasts_forecast_quantity_upd_xu2

Once you've downloaded these files and added them into your install directory you need to add a call to them in the xu2 file (wnoetxu2.sql) and then you can run a regenerate and do some additional testing. Remember that the forecast description needs to be added first as it adds the table to the view (required by the other two) for example;

@inv_forecasts_created_by_xu2.sql
@inv_forecasts_creation_date_xu2.sql
@inv_forecasts_current_forecast_quantity_xu2.sql
@inv_forecasts_forecast_description_xu2.sql -- Added MRP.MRP_FORECAST_DESIGNATORS(MFD)
@inv_forecasts_forecast_quantity_upd_xu2.sql
@inv_forecasts_disable_date_xu2.sql -- Requires MFD
@inv_forecasts_forecast_set_xu2.sql -- requires MFD
@inv_forecasts_original_forecast_quantity_xu2.sql

Hope this helps! Any questions leave a comment ...

Read More