How To Spot Subsidence (and It's Potential Causes)

Bit of a departure this blog post but it's quite rate to be able to spot some textbook subsidence for a building and have the most likely cause visible in the same picture.

As you can see in the image to the left the tree (bush?) is growing right next to the brick wall, judging by the tilt it's quite easy to imaging the roots of the plant going under the foundations of the building.

If you zoom in to the corner of the building you'll see that the brick wall next to the bush has been undermined and has dropped by a few millimetres - this causes the practically-vertical crack down the side of the building about half-a-brick in.

As the bush grows it will cause more and more damage to the foundations (the drop it quite likely due to it falling down due to the bush absorbing moisture from the clay-rich soil).

The solution would seem to be to remove the bush and see if that stops the subsidence (it's a garage not a house). Depending on the rate of subsidence it's important to work out the risks involved in any "wait and see" approach.

Clearly the motto here is to check for inappropriate plantings like this every few years!

Noetix: Disabling Material Category (XXK_MTL_CAT) caching

This is slightly more complicated than the other "removal of caching" blog posts I've done earlier as there are multiple views (one for each structure) which all need to be updated and replaced at the same time. I've adopted a fairly simple approach;

• Remove any backup (_BK) views
• Create a new view to replace the root XXK_MTL_CAT view
• Go through the ALL_VIEWS system view looking for the structure-specific views and then individually replace each of them with a view based on the new XXK_MTL_CAT view created above
• Rebuild all the objects that have been invalidated by this change

Here is the script (you'll notice the * instead of the important piece of SQL, more details on that later);

@utlspon xu6_2012_replace_xxk_mtl_cat_view

BEGIN
  for v_Data in (SELECT av.view_name
                   FROM ALL_VIEWS av
                  WHERE av.owner = USER
                    and av.view_name LIKE 'XXK_MTL_CAT_%BK') loop
    execute immediate 'drop view ' || v_Data.view_name; -- Remove an existing backup view if it exists
  end loop;
END;
/

RENAME XXK_MTL_CAT TO XXK_MTL_CAT_BK
/

CREATE OR REPLACE VIEW XXK_MTL_CAT AS

*

/

begin
  for v_view in (select av.*
                   from all_views av
                  where av.owner = USER
                    and av.view_name like 'XXK_MTL_CAT_%') loop
    if instr(v_view.text, 'NOETIX_SYS.N_KFF_MTL_CAT') > 0 then
        execute immediate 'CREATE OR REPLACE VIEW ' || v_view.view_name || '_BK AS ' || v_view.text;
        execute immediate 'CREATE OR REPLACE VIEW ' || v_view.view_name || ' AS ' || replace(v_view.text, 'NOETIX_SYS.N_KFF_MTL_CAT', 'NOETIX_SYS.XXK_MTL_CAT');
        end if;
  end loop;
end;
/

begin
  dbms_output.put_line('Recompiling invalid Views under NOETIX_SYS Schema');
  dbms_output.put_line('-------------------------------------------------');
  dbms_output.put_line('Remove Invalid Noetix Views (that are not set to be ommitted)');
  for v_SQL in (select 'ALTER VIEW NOETIX_SYS.' || do.object_name || ' COMPILE' text
                  from dba_objects do
                 where do.status = 'INVALID'
                   and do.OWNER = 'NOETIX_SYS'
                   and do.OBJECT_TYPE = 'VIEW'
                   and do.OBJECT_NAME not in
                       (select view_name
                          from n_views
                         where nvl(omit_flag, 'N') = 'N')) loop
    dbms_output.put_line('  ' || v_SQL.Text);
    execute immediate v_SQL.Text;
  end loop;
  dbms_output.put_line('END');
end;
/

COMMIT;

@utlspoff

*- Now Noetix owns the code for this bit and as it's going to be distinct to every individual system (as it includes references to ID's) you'll need to do a bit of detective work and work it out yourself. I'll tell you how below.


First of all do a search for the object N_KFF_Mtl_Cat in all the .SQL files in your install directory. You shouldn't find many, the one you're after is named something like N_KFF_Mtl_Cat_pkg.sql, open the file and then scroll down to the line;

INSERT  /*+ APPEND */ INTO N_KFF_Mtl_Cat

You'll see that that the basis for this insert statement is a select statement. You need to take this select statement, take the columns in the table and re-format the SQL to return all the columns as specified in table.

When you've done this you'll notice that the column in the source table STRUCTURE_ID is mapping to both the STRUCTURE_ID and STRUCTURE_NAME in the view. If you scroll down quite a bit further you'll notice that there is also an UPDATE statement;

UPDATE N_KFF_Mtl_Cat dtbl
   SET dtbl.STRUCTURE_NAME =

You need to extract this UPDATE statement and put it into the view you're building (as the STRUCTURE_NAME) column.

In the end you'll have a piece of SQL that you can drop into the script above. If everything works when you run the script there will be no (well, the same number as you started with!) invalid views.

Switching to Google Public DNS (on an Apple Mac OS X Lion)

If you are interested in learning why you should be using a DNS other than that provided by your Service Provider then you might like to see Google Article on the subject;

http://code.google.com/speed/public-dns/docs/intro.html

A couple of things it doesn't mention but are also definite benefits (for me anyway) are;

• Automatically opting out of any Service Provider "search" services (such as those offered by Virgin Media) when you miss-type a URL
• Avoiding any blocks your Service Provider might put on you reaching certain sites by substituting DNS entries. Of course the down side of using a service like Google is that suddenly by using a US-based service you are subject to US law and filtering

Here are the instructions in order to set-up your Mac to use Google DNS.

Go to the top-right of the screen and right-click the Network (Wireless?) network icon;

Apple Network Options

Select "Open Network Preferences";

Network Preferences

Click on the "Advanced" button at the bottom right;

Advanced Network Options

Click on the "DNS" tab (centre-middle at the top);

DNS  Servers

As you can see in the screen shot the Google IP addresses for DNS are 8.8.8.8 and 8.8.4.4 (which are certainly a lot easier to remember than IP addresses generally!).

You need to remove any entries that are already in this box and replace them with the two IP addresses above. 

Noetix: New view template INV_Material_Status_History

INVG0_Material_Status_History

Using Oracle's Process Manufacturing (OPM) module was part of Oracle e-Business Suite is never easy; there are so few users of process manufacturing - as opposed to it's much more commonly used bigger brother discrete manufacturing - that we often get left out.

(What's the difference between Process and Discrete manufacturing? Discrete manufacturing makes individual items (Cars, TV sets, iPads, etc) these are either complete or not complete while Process Manufacturing feeds ingredients into a Process until they are all used up (Bread, Soft Drinks, Pharmaceuticals, etc). You would be amazed at how many times I have had to explain this to various companies!)

The issue has arisen because as part of our month-end processes in Finance we look at the INV_Period_Close_Details view and get our total stock on-hand. The problem is that we need to see the Material Status at the point in time the period was closed as, obviously, items in Quarantine (or Rejected) status have a value of zero whereas items which are Approved have a saleable value.

(We have already customised done some heavy customisation in this area as, unbelievably, several key fields are missing which make *all* reports meaningless - for example the Inventory Type; in what case would you not wish to differentiate between Finished Goods sitting in the Warehouse and Raw Materials?!)

We had the option of adding the table we needed (INV.MTL_Material_Status_History) into the INV_Period_Close_Details template but the more we looked at it the more other uses we could see for the data (i.e. lot genealogy) and we couldn't see a way of marrying up all the different requirements without creating a new, custom, view.

Here is the list of columns we selected (I've removed the A$ and Z$ columns and truncated the item flexfield);

chart_of_accounts_name
item$... <item flexfields>
item$item
item_description
item_long_description
locator$inventory_location_id
locator$organization_id
locator$sv$stock_locator
locator$stock_locator
lot_number
master_organization
master_organization_name
material_status
material_status_code
organization_code
organization_id
organization_name
primary_unit_of_measure
status_end_date
status_start_date
subinventory

The script necessary to create the view is available in Google Docs here. Save this as an SQL file in your Noetix directory and call it from WNOETXU2.sql. It seems to me that this is something the NoetixViews Product should actually include as there is a clear, demonstrable, use for it - the first question any auditor will ask when you show them your month end stock levels is how much it's worth!

I have not had a great deal of luck getting the changes I've been pushing for into the product. If anyone has any suggestions I'd be happy to hear them!

NOTE: While this view has passed IT-testing it is currently with the business for end-user testing. If any issues come up I'll update this blog post with details as well as a fix.

SQL Server: Installing Microsoft SQL Server 2012 On a Development Windows 2008r2 (SP1) System

This is a simple step-by-step guide to installing Microsoft's newest version of SQL Server with Reporting Services in stand-alone mode (the alternative is SharePoint Integrated, I'll deal with all the steps necessary to do that installation separately in a few days/weeks time).

I'm using the developer edition as downloaded from Microsoft MSDN, the ISO name is;

en_sql_server_2012_developer_edition_x86_x64_dvd_813280.iso

I've highlighted the build version. If past experience is anything to go by later releases will follow pretty-much the same installation pattern.

The first step is to install your Windows 2008R2 box and get it fully upgraded to the latest patch levels (SP1). Then you can run the setup.exe on the installation DVD;

SQL Server 2012: Installation Centre

Select "Installation" from the list of options on the left;

SQL Server 2012: Installation Options

Select "New SQL Server stand-alone installation ..." which is the top option on the right. After a few seconds wait (but I guess that probably depends on the speed and power of your system!) the following dialog appears;

SQL Server 2012: Setup Support Rules

The installation program has checked your system and, on the system I'm using, found nothing that prevents the installation from proceeding. If you are interested in seeing the "Detailed Report" I've updated it to Google Docs here (with a slight bit of editing to remove machine names).

Once you've got a good set of passes click "OK". A "please wait" dialog will appear and after a few seconds the next dialog;

SQL Server 2012: Product Key

You now need to enter your Product Key. If you are using the MSDN edition then it will be pre-populated, if you don't have one when you can just select one of the "Free" editions. Once you've entered the details click "Next";

SQL Server 2012: License Terms

No installation process would be complete without a 20-page Licensing Agreement and this one is no different. After you've read the entire document (you do do that right? *cough*) click on the "I accept the license terms". I also select the "Send feature usage data to Microsoft" and if you don't you'll only have yourself to blame when the features I use and tell them about are prioritised for improvement and the features you use but keep secret about aren't ... ;-)

Click "Next";

SQL Server: Install Setup Files

This dialog will only appear for a few seconds, as soon as the installation files have been successfully installed you are presented with the next dialog;

SQL Server: Setup Support Rules

Again I've made the Detailed Report for this check available via Google Docs here. As you'll see there is one Warning related to the "Windows Firewall", I'm going to ignore this and just move on.

Click "Next";

SQL Server: Setup Role

The default option, "SQL Server Feature Installation" is the one I'm after so I'll just click "Next";

SQL Server: Feature Selection

And this is the part where things start to get complicated. What exactly do you want your SQL Server to be doing? I'm going to install pretty much everything except the SharePoint integrated features (the first two shared features; Reporting Services - SharePoint, and Reporting Services Add-in for SharePoint Products). Clearly I'm building a development box so it's usually better to have too much rather than too little so I can use it for many different purposes.

Clearly if you're building a test (or production) machine you need to be a lot more careful about what you select on this page. As you select features configuration pages appear on the left to allow you to configure them during the installation.

Once you're done click "Next";

SQL Server: Installation Rules

So we now have another check, the third, to make sure the system is capable of running the options I've selected. And now it "Failed" as "Microsoft .NET Framework 3.5 Service Pack 1 is required". The instructions for enabling this feature I have also blogged about, click here. When I did this no-reboot was required.

After you've enabled the feature click on "Re-run" in the dialog;

SQL Server: Installation Rules (Attempt 2)

As you can see the check that had previously failed has now passed. The Detailed log is available (via Google Docs) here.

Click "Next";

SQL Server: Instance Configuration

There isn't anything here I need to change so just click "Next". There will be a brief pause while the installation program checks to see if sufficient disk space exists to install the options you have selected. After a few seconds a report will be displayed;

SQL Server: Disk Space Requirements

Click "Next";

SQL Server: Server Configuration

It's pretty unlikely that you'll want to change anything here (all these are services you can always change later). The one thing you might want to check if you are deploying to non-English customers is that the correct options are selected under the "Collation" tab - this is especially true if your system will be used to store multi-byte data such as Chinese, Japanese, Korean, etc. characters.

Click "Next" when you're done;

SQL Server: Database Engine Configuration

I always use "Windows authentication" and (as it's a development machine which will spend 90% of it's time off!) I usually add in a few AD groups representing the entire company (we are an IT Service company) on the basis that I never know who I might want to share it with. This is your chance to secure the system either as tightly or loosely as you wish.

Click "Next";

SQL Server: Analysis Services Configuration

And the same again really. You need to enter the users who will have access to the Analysis Services. Once you're happy with this click "Next";

SQL Server: Reporting Services Configuration

The default option, "Install and Configure", is the option I'm interested in so just click "Next";

SQL Server: Distributed Replay Controller

Whilst appearing to be exactly the same as other "Pick the users" dialog box this one is subtly (invisibly!) different; you cannot select groups. If you try when you click "Next" you get an error;

The specified account 'XX\YYYY' for setting 'CTLRUSERS' is a group account. You can only use a user account.

Add in the users for this feature and click "Next";

SQL Server: Distributed Replay Chat

Enter the name of your controller (or leave blank) and then click "Next";

SQL Server: Error Reporting

I always check this check box (to send error reports to Microsoft) so that errors I encountered will hopefully be dealt with in future releases! Click "Next" when you're done and another round of checks will be executed and after a few seconds you will be presented with a report;

SQL Server: Installation Configuration Rules

Again I have uploaded the detailed report to Google Docs here. Click "Next";

SQL Server: Ready To Install

Click "Install" to begin the install process. The installation itself on the development system I was using took around 25/30 minutes. After the install is complete you will see the following dialog;

SQL Server: Computer Restart Required

Click "OK" and under this is the final installation report;

SQL Server: Complete (Install Report)

I've attached the (edited) log file generated by the installation here.

A restart is required, after the restart running Internet Explorer and pointing to the the SQL Server Reporting Services URL will (after logging in) take you to the standard webpage.

Windows 2008R2: Installing .NET Framework (Enabling the Feature)

2008R2 includes the .NET Framework as a feature that needs to be turned on when required. Numerous installation programs (for example SQL Server 2012) require it and will mostly just error and tell you to turn the feature on.

The blog post is intended as a step-by-step guide to switching the feature on.

Go to the start menu and type "Feature" into the search box, choose the option "Turn Windows Features on or off". The Server Manager will then be displayed;

Windows 2008r2: Server Manager

Click on "Features" in the tree-view on the left;

Windows 2008r2: Features

If you already have any features installed they will be listed here. Click on "Add Features" on the right;

Windows 2008r2: Select Features Dialog

Expand the top node in the list (.NEW Framework 3.5.1 Features) and select the first item ".NET Framework 3.5.1). Click "Next";

Windows 2008r2: Confirm Installation Selections

Click "Install", the next dialog actually shows the installation process which should take about 30 seconds (depending on how powerful your system is!) then you will see the following;

Windows 2008r2: Installation Results

Presuming you have "Installation succeeded" in the dialog click on "Close" to complete the process.

iTunes: How to Download 1080p Content By Default (If Available)

By default iTunes is set to download HD content (TV Shows and Movies) in 720p and while this is the maximum supported resolution for the AppleTV 2 the new Apple TV supports up to 1080p and, if you've paid for the HD content, you might as well see it full size!*.

Start with opening iTunes and bring up "Preferences" and select the green "Store" icon;

iTunes Store Preferences > Store Tab

In the second section at the bottom there is a drop down, showing "720p" in the image above, you can change this to "1080p" to download the maximum high-definition format (well, until maybe the iPad 4 anyway ...).

If you want to check it's working just go to a HD item in the iTunes store and take a look at the settings on the left;

Alcatraz, Season 1 Showing 1080p HD Download as Default (click to enlarge)

Of course not all HD content is currently 1080p but there are a few titles which offer free previews in 1080 like the BBC's Sherlock.

To go straight to the option screen from the TV/ Movie screen just click on the "(Downloading xxx)" indicator on the left, it's actually a link and will take you straight to the settings page to change your option.

*- Disk space permitting of course! A 22 episode series HD will take you around 3.5GB (SD 600mb, HD 720p 1.4Gb, and HD 1080p 1.6Gb) per episode. That's around 80Gb for the complete series. Looking at West Wing that's over half a terabyte for all seven seasons for 1080p!