Friday, December 21, 2012

MobileIron: Migrating iPads/iPhones Between Instances

As part of a european integration process we find ourselves wanting to move iPads and iPhones pointing to once instance of MobileIron to another. The reasons for this are long and complicated but suffice to say it has to be done and there doesn't seem to be any way to do it automatically (not such that the "old" server can be switched off at anyrate).

This blog post is simply a series of steps necessary to migrate the servers. There is one "gotcha" if you try and install the new profiles without removing the old ones you get a "Failed" message but the following simple steps should work for most people.

The first thing to note is that if you aren't a IT user, if you're an end-user, and you start messing around with the profiles on your iDevice you can expect a phone call from your IT department (who hopefully will be monitoring for this!) wanting to know what you're doing. If you're an end-user don't touch - this blog post isn't for you.

You will need the server name, active directory username (and password), iTunes Store account password, and the devices passcode in order to make these changes.

The first thing to do is find the MobileIron application the iDevice;

MobileIron Application
When you've found it press and hold it until it "wiggles" and a black cross appears at the top-left of the icon. Press the cross (to remove the application). A dialog will appear;

iPad > Deleting an Application Dialog
Push "Delete" to remove the application.

Next find the "Settings" application and open it;

iPad Settings Application
Highlight "General" on the left-hand side and then on the right scroll all the way down to the bottom and you should see a "Profiles" button - push it;

Settings > Profiles > Installed Profiles

This shows you a list of the profiles that have been installed on the iDevice. It's "likely" that all of these have been installed via MobileIron but there is no way to know for absolute certainty so perform the next steps at your own risk!

Touch on the top profile;

Profile Details
Touch "Remove". You'll be prompted for your Passcode, enter it and the profile will be removed.

On the assumption that the top profile is the "master" profile all the other profiles that are dependant on it should be removed as well;

Settings > Profiles > None available
If you don't remove the existing profiles you can get into trouble when installing a new one so it's best to get them out of the way first. You could, alternatively, de-allocated the device on the old server but this will mean you will be blocking the users access until they perform these steps and - generally - it's better for them to do it when it's convenient for them rather than forcing them to do it when the next turn the iPad on!

Now let's setup the new configuration.

Locate the "App Store" application and open it.

Go to either the Featured, Top Charts, or Genius tabs (at the bottom) and enter “MobileIron” (note no space) into the search box at the top right;

MobileIron Application (in the App Store)
Touch INSTALL (or FREE).

The download and installation will only take a few minutes, you will be prompted for your iTunes password. 

Once it’s complete touch on “OPEN”.

MobileIron Configuration Screen
You'll now be prompted for a username (usually Active Directory), the server name, and password. Once you've entered these touch "Go". A dialog will appear;

MobileIron Login Dialog
Touch "OK".

After a few seconds you will see the standard MobileIron screen;

MobileIron Standard Screen
Touch "Settings" (on the bottom left);

MobileIron > Settings
Touch on "Check for Updates";

MobileIron > Settings > Check For Updates
Touch on "Update Configuration Profile".

A dialog of the "are you sure"-type will appear, touch "YES". A few seconds will pass and you'll be automatically taken to the Settings application and a dialog will appear;

Settings > Install Profile
Touch "Install". A dialog will appear;

"Install Profile" dialog
Touch “Install Now” and enter your Passcode.

A few seconds will pass and then a new dialog will appear;

Mobile Device Management Dialog
Touch “Install”

Touch “Done”

Go into the “Settings” application, then “General” then “Profiles”. Depending on the speed of your internet connection the other profiles will be downloaded from the server, if these profiles haven’t appeared within a few minutes then go back into MobileIron and repeat the steps from “Check for Updates”/ “Update Configuration Profile”.




Wednesday, December 19, 2012

OS X: Resolving UK Keyboard Issues with VMware Fusion

If you happen to be running OS X and using VMware Fusion to run Windows you'll be familiar with the issues regarding the default key-mapping used when you try and type text in windows. Of course if you're using a corporate Windows build (as I am) you get the additional fun of playing "hunt" the special key in password dialog boxes!

Now if you check the VMware Fusion forums you'll notice that they're practically full of advice on how to fix this problem (mostly using customised keyboard within the VM) but I'm going to recommend you do a couple of simple changes to the key-mappings to fix the issue. The example I'm going to use is the dreaded @ (shift-") mapping to " (shift-2) but after seeing this you should be able to quickly add other mappings.

The first thing is to start your VM (we can make this change while the VM is running without rebooting it) and then bring up your VM's settings;

VMware Fusion: VM Settings
3rd from the right on the top row is "Keyboard & Mouse". Click this;

VM Settings > Keyboard & Mouse
On the left side you have a number of profiles - there are usually 3 (Mac Profile, Profile, and Windows 8 Profile). One of them will have "- Default" on the end, this is the one you're currently using. Click on it and then click on the configuration cog at the bottom and choose "Duplicate" (this is just to enable you to get back to a working state if it all goes completely wrong!).

When you've got and named your duplicate (as you can see I've gone with "My Profile") select your new profile and select the configuration cog again and this time select "Make Default Profile".

Now click the cog for a third and final time and select "Edit Profile ...".

Keyboard & Mouse > Edit Profile
Now you can see on the list what key mappings you are currently using.

Click "+" at the bottom;

Edit Profile > Add Key Mappings

Click on "Shift" and enter ' (single-quote) in the top entry box, then click on "Shift" and enter 2 in the bottom box. Click "OK to save the change.

Now click on the "+" again and this time click on both shift buttons but put the single-quote at the bottom and the 2 at the top. Click "OK".

Scroll down to the bottom of your key mappings and you'll see something like;

Key Mappings > New Key Mappings

Open Notepad inside your VM and you should now find that the key mappings and back to the way they're printed on your keyboard.

You can repeat this process with as many key mappings as you want.




Monday, October 22, 2012

Updating NLS_LANG For A Windows Client Installation

Usually you'd expect your Client and Server environments to be in sync but there are specific issues when it isn't the case and this can lead to unexpected conversions when inserting text. We suffer from this issue as our server environment is in "American" whereas our clients are usually in "British".

It doesn't cause massive issues but occasionally, especially with Oracle e-Business Suite, this can catch us out (typically it's due to it trying to look up a UK-English translation of something in an API call that doesn't exist).

Here's a quick guide to update your client-side environment.

First of all open "Registry Editor" (type "regedit" under Start Menu > Run) and search for NLS_LANG. you'll see something like;


I've highlighted the NLS_LANG entry, as you can see in the image it's currently set to;

AMERICAN_AMERICA.WE8MSWIN1252

From here it's just a quick matter of double-clicking the entry and updating it.

NOTE: It's always worth doing a "Find Again" to get the next instance of NLS_LANG just in case you have multiple clients installed.

Sunday, September 30, 2012

Celebrating 30 Years of the ZX Spectrum

Well this is going to be a *slight* departure.

I have been asked to contribute to an article (see here) on the 30th Anniversary of the ZX Spectrum by a local paper and this triggered a trip down memory lane (not to mention a wasted lunchtime looking through the Spectrum archive at World of Spectrum).

Ironically I never actually owned a Spectrum having had parents who brought into the whole "BBC is educational, Spectrum and Commodore are for Games". I was lucky(?) in that I had a group of friends one of which had a Commodore 64 (let's call him "Ian"), the other started with a Spectrum 48 (let's call him "Matt"), then the 48+ which had a "proper keyboard", then went on to a 128+2 and finally stuck with the Spectrum right into it's death throes with the SAM Coupe* (yes, I know *technically* the QL should be in there somewhere but I don't think I ever met anyone who owned one of them!).

On the plus side this always meant that at least two of us could agree that the other's machine wasn't the best! The hours (and hours and hours) we wasted arguing over that one!

As you can imagine, 30 years ago, the games could be described at best as "primitive" by todays standard and while Ian, with his Commodore 64, was blessed with two joystick ports AND two joysticks to plug into them I was stuck with my BBC and a single joystick which worked so rarely that it was just quicker to use the keyboard and finally Matt had a special expansion plug in (Interface II?) at the back of his Spectrum for a couple of joysticks but it was a little loose so occasionally you would knock it and the entire machine would just spontaneously die. Fun times.

Nevertheless my fondest computer-related memories of that time feature some of the classic games on the Spectrum. Whilst the graphics were poor some of the games are absolute classics you could play for hours on. Here's some of the ones I can remember (in no particular order);

Cobra (Ocean Software Ltd)
A sideways scrolling shoot-em-up based (very very loosely!) on the Sylvester Stallone movie of the same name. This is one of the rare examples where the simplicity of the Spectrum graphics actually made for a more enjoyable game than the graphically superior version on the Commodore 64. Luckily this game is available on the World of Spectrum website (click here).


Twin Kingdom Valley (Bug-Byte)
Now this one is a real classic but I'm guessing it's not going to be to everyone's taste! It's a text (ok, there's a few graphics) adventure game. The reason I really liked this one is that the world is populated with Elves, A Giant, and an Innkeeper who were all your friends, and Gorillas, Dragons, Castle Guards, Trolls, Witches, Sand Lurkers, etc who definitely weren't.

Your aim was to collect all the treasure and leave it in your hut (pictured below) but the bit of the game I really enjoyed was helping the Elves and the Giant rid the world of all the "evil" people (you could arm them and they'd help you in a fight). Excellent game and, if memory serves (it may now) D, N, N, N, W, W, S will get you from the metal grate to the cliff edge - if only I'd had that kind of recall during my exams!

Again this title is available from World of Spectrum - click here.


Target Renegade (Imagine Software Ltd)
A successor to the incredibly popular Renegade but introducing multi-player. An absolutely fantastic beat-em-up game where you went through several levels defeating adversaries in order to get rid of the "end of level baddies".

The Spectrum keyboard was pretty small so as you can imagine it would become quick complicated for two people to hammer way on the keyboard at the same time - add in to that the game would actually multi-load (when you completed a level you'd need to wait 2-3 minutes to continue and when you died you had to rewind the tape!) and you'd think no-one would enjoy it but we literally spent entire weeks getting through it (which we did eventually!). Fantastic game!

Here's the link on World of Spectrum.


You might also want to try out the original Renegade here.

Bubble Bobble (Firebird Software Ltd)
A really nice arcade conversion that massively benefited from allowing two players to play simultaneously. You could bounce around popping monsters on this one for ages, sadly at higher levels it just became a complete nightmare to play so while we used to play this one for absolutely ages it's actually one of the few I've not been inclined to play again via an Emulator.

Still, great memories.


And it's available here on World of Spectrum.

And finally (otherwise I'll be here all night);

R-Type (Electric Dreams Software)
Possibly the best (well, with Salamander) shoot-em-up ever. You could pick up power-ups as you went along and there were end of level baddies. Does it get any better than that?!



Sadly while World of Spectrum has a page for this game (here) the makers have specifically denied them the right to distribute it. Still it's good to know that the game has been saved for posterity, even though until the Copyright formally expires I won't be able to access it!

Well that's it, just a simple pick of a few games from my youth. If you're interested in finding out more then I'd recommend browsing the World of Spectrum Archive, especially the Best Games page.

In 30 years I wonder what my kids kids will be playing?!

*- Whereas my progression from BBC directly to A3000 (an Acorn Archimedes) then to a Risc-PC before moving on to the more-familiar PC (and now a MAC). See? It's not just Jeremy Clarkson who can waffle on about old cars - computer geeks can do it too!

Saturday, September 29, 2012

Using iOS 6 Apple Maps "Report A Problem" Feature

As I'm sure you'll be aware Apple are experiencing a few problems with their brand new Apple Maps application for iOS 6 (see here for CEO Tim Cooks' public apology). One of the features of the new Maps application is a "Report A Problem" link, you can use this to report issues to Apple for them to fix - clearly they'll have a lot to do.

Here's how to report an issue;

  • First of all go to the location in Apple Maps which shows the problem you'd like to report;
  • Then click on the bottom-right "page" bit to see the settings;

  • Just above the "Print" button in *very* faint type (makes you wonder if they intended people to spot it doesn't it?!) there is a "Report a Problem" link. Click on this.

  • Now you pick the type of issue you want to report. Here's a tip; if you want your issue dealt with quickly (and that's a relative term!) don't select "My problem isn't listed" - I don't know what black hone that vanishes into but don't expect anyone to get back to you soon! For the sake of this example I'm going to pick "Location is missing" (as it is!);

  • At the top of the screen it says "Drag the pin to the correct location". Do that and then when you're done click "Next" at the top right;

  • Now you can enter as much details as you can for the item you're reporting as being missing. I'm reporting "Bar Hill Tesco" and I've picked the details of their website (I've even included the link so Apple can check it). Then click "Next";

  • Now, after you're spent all that time typing stuff in, it tells you that the map information and problem you've entered will be reported to Apple if you click "OK". You could just click "Cancel" if you want to have wasted your time.

Friday, September 28, 2012

Apple Maps Comes To Bar Hill!


As I'm sure you're aware Apple have updated their iOS devices (iPad, iPhone, iPod) with iOS 6 which replaces the existing Google Maps with *new* Apple Maps which includes Turn-By-Turn navigation as well as a 3D view.

Sadly the new Maps application is not quite up to the standard of Google Maps (in much the same way as an ant is not quite up to the height of a giraffe).

Looking at Bar Hill Tesco is a good example of where this has not quite worked ... The map at the right shows Tesco Car Park with access both to Gladeside and Viking Way (neither of which exists), and a surprising loop in the car park.

If nothing else pondering these anomalies with give the people trapped in the one-way system that the Petrol Station has suddenly turned into something to think about!

There are numerous other issues; "Long Stanton Road" as an example of a typo (should be "Longstanton Road"), and half the businesses are missing including Tesco, the Parish Council Officers, the Church, etc.

I have reported several of these, but fixing everything is going to be a *huge* job and is way beyond anything that Apple have planned - Google Maps after all didn't get anywhere near as good as it is now overnight! It's just a shame this half-finished product is being forced onto people in it's current state. 

So what should Apple do? (let's just pretend they are taking advice from random bloggers!) I've mentioned before in various blogs the open-source mapping information provided by www.openstreetmap.org seems to be a viable alternative. For example;

OpenStreetMap.org - Bar Hill, Cambridgeshire

I'd suggest to Apple that rather than trying to build their own mapping database - which will take years - or buying one of the not-as-good-as-Google mapping companies out there to get access to their data why not work with OpenStreetMap and provide some funding for the project and use open source mapping data? Give it all away free and people will help you build it.

Anyway, here's hoping they do something soon!

Monday, September 24, 2012

SSRS: Scheduling A Report With 2008R2 (In SharePoint Integrated Mode)

In an ideal world you'd go to a report, enter your parameters, run the report, and a few seconds later you'd have all your data in the format you need. Unfortunately whilst going to the report and entering the parameters are (usually!) easy, the "few seconds" can quickly stretch to "a few minutes" and beyond before you get your data - particularly with some of the more complex GL-based reports.

As most reporting is clyclical (month end, year end, financial year, monthly sales, weekly orders, etc) it's possible to predict the reports you'll need for a particular period and it's possible, using Reporting Services and SharePoint to scheduled the reports to run when you know you'll need them.

The first step is to go to your report;
Simple SSRS Reporting Showing GL Segments
At the top-left of the web page is a "Actions" drop down. Select this and choose the "Subscribe" option. If you don't have a subscribe option then you probably need to contact whoever administers your server to see why not; there really isn't any reason why users who run reports shouldn't be able to schedule them to run out-of-office hours.

When you select "Subscribe" you'll be directed to the following web page to select the options for the report;
SSRS Subscription Properties
This page allows you to choose how you want to receive the report. By email is probably the simplest way (and is the default). The available options are;
  • Email (default): You can choose to have a report sent to any email address (for example a distribution list) but you should already remember that only the person who sets up the Subscription will be able to edit it and if they leave and their Active Directory (AD) account is disabled the subscription will automatically stop
  • Windows File Share: Select this to choose to have the report sent to a Windows File Share. You can specify your username and password to grant access. Useful under certain circumstances, but I'd recommend switching to a ...
  • SharePoint Document Library: We like this one. Again it requires some setting up by your system administrator but once it's there then you get all the nice features of SharePoint (sharing, versioning, security, etc) without the permission problems of Windows File Shares. This is our preferred option for in-house reporting
  • Null Delivery Provider: Sometimes it's necessary just to run the report without worrying about the results (triggering caching automatically for example). If that's the case then this is the provider to select.
Once you've picked the "Delivery Extension" (how you want the report delivered) then the next steps are to expand on that by selecting the following;
  • Output Format/ Report Contents/ Render Format (Depends on Delivery Extension selected): Here you can select the format you want the report to be saved in. You can (in Sharepoint 2007 with SQL SSRS 2008R2) only select a single format which means if you want the report in multiple formats (for example PDF for archiving, and Excel for working with) you need to create multiple schedules (if you'd like to show Microsoft you'd like to see this changed you can vote for the item on Connect - see here)
  • Delivery Event: You can select either; a) When a report snapshot is created, b) On a shared schedule, or c) on a Custom Schedule. You need to decide when you want the report and whether it's important or not if it runs at the *exact* time specified or within a few minutes/ hours. If it absolutely has to run at a specific time then select Custom Schedule 
"Delivery Event" Subscription Options*
  • Parameters: Here you select the parameters for your report. Here you'll definitely need the help of the report writers who need to have written the report with scheduling in mind in order for it to work. For example if you have a report which has a Start and End Date then the default values need to be default to, for example, the start and end of last month so that when it's scheduled to run on the 1st of every month the user can just select "defaults" rather than having to hard-code values. With a bit of planning this can work really well (NOTE: The list of parameters includes *all* parameters for the report including ones developers have marked as Hidden - I've raised this as a bug with Microsoft click here)
Now that you've selected all the options click "OK" (at the top) to create the subscription. It won't run immediately but when the Delivery Event comes around it will try, remember to check back for errors!

*- you'll notice, if you squint really hard at the "Shared Schedule" option that we (OK, I) made a mistake when setting these up. The point of shared schedules should be to increase the flexibility of system administrators to juggle the jobs around but make sure they are still available when needed. "Monthly (1st) @ 7am)" isn't exactly flexible, what I should have created is "Monthly (1st) before 8:30" so I'd be able to move the reports around as and when.

Friday, August 31, 2012

Oracle R12 Lot Genealogy and Noetix

If, like me, you work in a process manufacturing* company one of the key things you need to worry about is lot genealogy. Put simply this is the process that a manufacturer uses to determine where the raw ingredients went. When you read about a product recall in the press this is the process the company will have gone through to trace where every product to be recalled went.

Here's a simple diagram of the process;

Simple Lot Genealogy

As is made (hopefully!) obvious in the diagram the main issue with reporting on Lot Genealogy is recursion. In Release 11 Oracle used to provide a lot genealogy report, it seems they have removed this report in Release 12 although the same information is available via the standard Oracle Forms (although if you have to file reports with the FDA or HMRC it's questionable how well "we've got it on the screen" will go down).

Each step in the above process is logged as an item transaction (amongst a lot of other events). There are four steps to the process;
  1. Which lots were created as a result of purchase order W?
  2. Which batches did lot X go into? 
  3. Which lots did batch Y go into?
  4. Which lots fulfilled sales order Z?
Working through these in turn and using the Noetix View INVG0_Lot_Transactions the SQL to see which lots were created by a receipt into inventory from a Purchase Order is;

SELECT ilt.lot_number
  FROM invg0_lot_transactions ilt
 WHERE 1 = 1
   AND ilt.PO_Number = 'XXX'
   AND ilt.transaction_type = 'PO Receipt';

Now the first thing you'll notice if you're familiar with the view is that I'm using a "PO Number" column - a column that doesn't exist in the basic view as provided by Noetix. In order to simplify not just Lot Genealogy but reporting in general we have added a number of customisations to this view. These are available as a single file here (via Google Docs). Strictly speaking it's not necessary to add these customisations as we could do it all in the script I'll show you below, but it does make it a great deal easier (for example do you prefer "DECODE( MATTR.TRANSACTION_SOURCE_TYPE_ID, 5, MATTR.Transaction_Source_ID, null)" or just Batch_Id?).

The second point, which batches did lot X go into, is a little more complicated as we are also at this stage looking to exclude batches where the lot was issues incorrectly and then fully returned (using the "WIP Issue" and "WIP Return" transaction types). See below for the SQL;

SELECT ilt.Batch_Number, SUM(ilt.TRANSACTION_QUANTITY)
  FROM invg0_lot_transactions ilt
 WHERE 1=1
   AND ilt.lot_number = 'XXX'
   AND ilt.Transaction_Source_Type = 'Job or Schedule'
   AND (ilt.transaction_type = 'WIP Issue' OR ilt.Transaction_Type = 'WIP Return') 
 GROUP BY ilt.batch_number
HAVING SUM(ilt.TRANSACTION_QUANTITY) < 0

The third point, which lots did batch X produce, goes back to the simple-style of the first

SELECT itd.LOT_NUMBER
  FROM invg0_lot_transactions itd
 WHERE 1=1
   AND itd.transaction_type = 'WIP Completion'
   AND itd.Transaction_Source_Type = 'Job or Schedule'
   AND itd.Batch_Number = 'XXX'

The key is "WIP Completion". Now this is where it gets a little interesting. With the SQL above we will pick up "Work in Progress" batches. These are where we have a multi-stage manufacturing process - such as granulation and then coating - and need to put the intermediary product on the system. So when the result of granulation is transported for coating it is stored on the system as a new batch and then the batch is emptied and input into the coating process - we could have filtered these out using the "WIP Completion Return" type in the same way we did above with WIP Issue/ WIP Return but the view I took when developing this is that correcting a mistake (as is the case with WIP Issue/ WIP Return) is different from materials passing through a batch as part of a process (WIP Completion). Hope that's clear?

The final part, which lots fulfil a sales order, is probably the simplest piece of SQL yet;

SELECT itd.LOT_NUMBER
  FROM invg0_lot_transactions itd
 WHERE 1=1
   AND itd.Sales_Order_Number = 'XXX'

Now we have the four parts of the story we need to work out lot genealogy it seems like it's just a simple matter of adding in a recursive lookup on batches to lots and we're there. And yes - you could do it that way but in my experience what you end up with is the recursion being done in different ways in different reports (for example if we are wanting to know where a raw material went in a report we might want to exclude packaging lots). You then find yourself having to "debug" a problem with a report and spending hours working out what this specific version of log genealogy is doing.

For this reason I have implemented a solution that processes the transactions as they are done and builds a cache of lot genealogy information (with a few bits of useful reporting information to prevent some of the more obvious lookups).

The first step is to build the tables. The three tables I'm using are;
  • Lot_Genealogy_Settings (this just holds a setting to record the last transaction processed),
  • Lot_Genealogy_WhereUsed (this holds the genealogy information from a "where used" perspective), and
  • Lot_Genealogy_Batch_Product (this holds a list of the products for each batch)
Starting with the simplest table the columns for Lot_Genealogy_Settings are;

Table Description: Lot_Genealogy_Settings

The creation script is available here (via Google Docs).

NOTE: You'll need to insert a single record to be picked up as the minimum transaction number (use -1) see the code for the package below to determine the values!

The columns for the Lot_Genealogy_Batch_Product are;

Table Description: Lot_Genealogy_Batch_Product
The creation script is available here (via Google Docs).

You'll notice when you look at the SQL that this table is indexed in order to speed up the retrieval of data. The time taken will depend on the amount of data you have but with the indexs (on this and the following table) the time taken to process eight months of data for us was about 3 minutes (that's approximately 571,000 lot transactions).

The final table (and the most important one) is Lot_Genealogy_WhereUsed, it's columns are;

Table Description: Lot_Genealogy_WhereUsed
The creation script for this table is available here (via Google Docs).

You'll notice that I'm creating all the tables in the NOETIX_SYS schema. The reason for this is fairly simple; as this package (and process) is dependant on Noetix and the Noetix Views will be rebuilt periodically I think it's better to have the creation script for the *package* (below) to be part of Noetix Regenerate; this way it gets automatically tested each time we do a build.

Now here's the script to create the package header;

create or replace package lot_genealogy is
  procedure doUpdate;
end lot_genealogy;

That's a little simple, here's the body;

create or replace package body lot_genealogy is

  procedure doUpdate as
    v_Exists               BOOLEAN;
    v_MaxTransactionNumber NUMBER;
  BEGIN
    -- SECTION 0100: Remove Any Existing Records From The Cache
    /*DELETE FROM NOETIX_SYS.Lot_Genealogy_WhereUsed;
    DELETE FROM NOETIX_SYS.LOT_GENEALOGY_BATCH_PRODUCT;
    COMMIT;
   
    UPDATE NOETIX_SYS.Lot_Genealogy_Settings LGS
       SET LGS.VALUE_NUMBER = -1
     WHERE LGS.Setting_Name = 'MAX_TRANS_NO';*/
 
    FOR v_Check IN (SELECT VALUE_NUMBER
                      FROM NOETIX_SYS.Lot_Genealogy_Settings LGS
                     WHERE LGS.Setting_Name = 'MAX_TRANS_NO') LOOP
      v_MaxTransactionNumber := v_Check.Value_Number;
    END LOOP;
 
    -- SECTION 1000: Process The Lot Transaction Records From INVG0_Lot_Transactions
    FOR v_Transaction IN (SELECT ilt.Transaction_Number,
                                 ilt.batch_number,
                                 ilt.transaction_quantity,
                                 ilt.lot_number,
                                 ilt.transaction_type,
                                 ilt.ITEM$Item,
                                 ilt.Item_Description,
                                 ilt.Item_Type_Code
                            FROM INVG0_LOT_TRANSACTIONS ilt
                           WHERE ilt.Transaction_Source_Type =
                                 'Job or Schedule'
                             AND (ilt.transaction_type = 'WIP Issue' OR
                                 --ilt.Transaction_Type = 'WIP Return' OR
                                 ilt.Transaction_Type = 'WIP Completion')
                             AND ilt.Transaction_Number >
                                 v_MaxTransactionNumber
                          --AND ilt.lot_number IN ('166130', '035598', '166127')
                          /*AND ilt.lot_number IN
                          ('029996',
                           '165507WIP',
                           '165507C1',
                           '165507',
                           '165583',
                           '167866WIP',
                           '167866',
                           '167951')*/
                          --AND ilt.transaction_number <= 2053180 -- For testing, about 6,000 records
                           ORDER BY ilt.Transaction_Number) LOOP
   
      -- SECTION 1100: Process WIP Completion Transaction
      -- NOTE: This section answers the question "What lots did batch X produce?"
      IF (v_Transaction.Transaction_Type = 'WIP Completion') THEN
        -- If we have already created records for this batch then we can update them with the new
        --   product UNLESS we already have a product for this batch
        UPDATE NOETIX_SYS.Lot_Genealogy_WhereUsed LGI
           SET LGI.PRODUCT_LOT_NUMBER       = v_Transaction.Lot_Number,
               LGI.Product_Item$item        = v_Transaction.Item$item,
               LGI.Product_Item_Description = v_Transaction.Item_Description,
               LGI.Product_Item_Type_Code   = v_Transaction.Item_Type_Code
         WHERE LGI.BATCH_NUMBER = v_Transaction.Batch_Number
           AND (LGI.PRODUCT_LOT_NUMBER IS NULL OR -- ... we haven't updated the product lot before
                LGI.PRODUCT_LOT_NUMBER = v_Transaction.Lot_Number); -- ... we have but this is the same
        IF SQL%ROWCOUNT = 0 THEN
          -- Nothing's been updated. New record?
          -- Insert the new record for each batch number where it already exists in the LG table (providing
          --  it doesn't already exist)
          INSERT INTO NOETIX_SYS.Lot_Genealogy_WhereUsed
            SELECT DISTINCT LGI.Master_Lot_Number, -- Mater_Lot_Number
                            LGI.INGRED_LOT_NUMBER, -- Ingred_Lot_Number
                            LGI.INGRED_ITEM$ITEM, -- INGRED_ITEM$ITEM
                            LGI.INGRED_ITEM_DESCRIPTION, -- INGRED_ITEM_DESCRIPTION
                            LGI.INGRED_ITEM_TYPE_CODE, -- INGRED_ITEM_TYPE_CODE
                            LGI.BATCH_NUMBER,
                            v_Transaction.Lot_Number, -- Product_Lot_Number
                            v_Transaction.ITEM$ITEM, -- PRODUCT_ITEM$ITEM
                            v_Transaction.ITEM_DESCRIPTION, -- PRODUCT_ITEM_DESCRIPTION
                            v_Transaction.ITEM_TYPE_CODE -- PRODUCT_ITEM_TYPE_CODE
              FROM NOETIX_SYS.Lot_Genealogy_WhereUsed LGI
             WHERE LGI.BATCH_NUMBER = v_Transaction.Batch_Number
               AND NOT EXISTS
             (SELECT 1
                      FROM NOETIX_SYS.Lot_Genealogy_WhereUsed LGI1
                     WHERE LGI1.MASTER_LOT_NUMBER = LGI.Master_Lot_Number
                       AND LGI1.Ingred_LOT_NUMBER = LGI.ingred_Lot_Number
                       AND LGI1.BATCH_NUMBER = LGI.Batch_Number
                       AND LGI1.PRODUCT_LOT_NUMBER =
                           v_Transaction.Lot_Number);
        END IF;
     
        -- In case this batch appears in another transaction later let's record
        --    what the completed products were
        INSERT INTO NOETIX_SYS.LOT_GENEALOGY_BATCH_PRODUCT
          SELECT v_Transaction.Batch_Number, -- Batch_Number,
                 v_Transaction.Lot_Number, -- Product_Lot_Number
                 v_Transaction.ITEM$Item, -- PRODUCT_ITEM$ITEM
                 v_Transaction.Item_Description, -- Product_Item_Description
                 v_Transaction.Item_Type_Code -- Product_Item_Type_Code
            FROM DUAL
           WHERE NOT EXISTS
           (SELECT 1
                    FROM NOETIX_SYS.     LGBP
                   WHERE LGBP.BATCH_NUMBER = v_Transaction.Batch_Number
                     AND LGBP.Product_Lot_Number = v_Transaction.Lot_Number);
      ELSIF (v_Transaction.Transaction_Type = 'WIP Issue') THEN
        v_Exists := False;
        -- Was this issue to the Batch reversed?
        FOR v_Check IN (SELECT 'X'
                          FROM INVG0_LOT_TRANSACTIONS ilt
                         WHERE ilt.Transaction_Source_Type =
                               'Job or Schedule'
                           AND ilt.Transaction_Type = 'WIP Return'
                           AND ilt.batch_number = v_Transaction.Batch_Number
                           and ilt.TRANSACTION_QUANTITY =
                               (v_Transaction.Transaction_Quantity * -1)
                           and ilt.LOT_NUMBER = v_Transaction.Lot_Number) LOOP
          v_Exists := True; -- Yes it was (WIP Issued in error)
        END LOOP;
     
        -- If the WIP Issue wasn't reversed ...
        IF (NOT v_Exists) THEN
          -- Get the batch product(s) (if we can) otherwise return NULL
          FOR v_Product IN (SELECT LGBP.PRODUCT_LOT_NUMBER,
                                   LGBP.PRODUCT_ITEM$ITEM,
                                   LGBP.PRODUCT_ITEM_DESCRIPTION,
                                   LGBP.PRODUCT_ITEM_TYPE_CODE
                              FROM NOETIX_SYS.LOT_GENEALOGY_BATCH_PRODUCT LGBP
                             WHERE LGBP.BATCH_NUMBER =
                                   v_Transaction.Batch_Number
                            UNION
                            SELECT NULL, NULL, NULL, NULL
                              FROM DUAL
                             WHERE NOT EXISTS
                             (SELECT 1
                                      FROM NOETIX_SYS.LOT_GENEALOGY_BATCH_PRODUCT LGBP
                                     WHERE LGBP.BATCH_NUMBER =
                                           v_Transaction.Batch_Number)) LOOP
            -- For each batch product create a LG record (if it doesn't already exist)
            INSERT INTO NOETIX_SYS.Lot_Genealogy_WhereUsed
              SELECT v_Transaction.Lot_Number, -- Master_Lot_Number
                     v_Transaction.Lot_Number, -- Ingred_Lot_Number
                     v_Transaction.Item$item, -- INGRED_ITEM$ITEM
                     v_Transaction.Item_Description, -- INGRED_ITEM_DESCRIPTION
                     v_Transaction.Item_Type_Code, -- INGRED_ITEM_TYPE_CODE
                     v_Transaction.Batch_Number, -- Batch_Number
                     v_Product.Product_Lot_Number, -- Product_Lot_Number
                     v_Product.PRODUCT_ITEM$ITEM, -- PRODUCT_ITEM$ITEM
                     v_Product.PRODUCT_ITEM_DESCRIPTION, -- PRODUCT_ITEM_DESCRIPTION
                     v_Product.PRODUCT_ITEM_TYPE_CODE -- PRODUCT_ITEM_TYPE_CODE
                FROM DUAL
               WHERE NOT EXISTS
               (SELECT 1
                        FROM NOETIX_SYS.Lot_Genealogy_WhereUsed LGI
                       WHERE LGI.MASTER_LOT_NUMBER =
                             v_Transaction.Lot_Number
                         AND LGI.Ingred_LOT_NUMBER =
                             v_Transaction.Lot_Number
                         AND LGI.Batch_Number = v_Transaction.Batch_Number
                         AND (v_Product.Product_Lot_Number IS NULL AND
                             LGI.PRODUCT_LOT_NUMBER IS NULL OR
                             v_Product.Product_Lot_Number =
                             LGI.PRODUCT_LOT_NUMBER));
            -- ... and add to existing master lot numbers with the same product lot
            INSERT INTO NOETIX_SYS.Lot_Genealogy_WhereUsed
              SELECT DISTINCT LGI.MASTER_LOT_NUMBER, -- Master_Lot_Number
                              v_Transaction.Lot_Number, -- Ingred_Lot_Number
                              v_Transaction.Item$item, -- INGRED_ITEM$ITEM
                              v_Transaction.Item_Description, -- INGRED_ITEM_DESCRIPTION
                              v_Transaction.Item_Type_Code, -- INGRED_ITEM_TYPE_CODE
                              v_Transaction.Batch_Number, -- Batch_Number
                              v_Product.Product_Lot_Number, -- Product_Lot_Number
                              v_Product.PRODUCT_ITEM$ITEM, -- PRODUCT_ITEM$ITEM
                              v_Product.PRODUCT_ITEM_DESCRIPTION, -- PRODUCT_ITEM_DESCRIPTION
                              v_Product.PRODUCT_ITEM_TYPE_CODE -- PRODUCT_ITEM_TYPE_CODE
                FROM NOETIX_SYS.Lot_Genealogy_WhereUsed LGI
               WHERE LGI.Product_Lot_Number = v_Transaction.Lot_Number
                 AND NOT EXISTS
               (SELECT 1
                        FROM NOETIX_SYS.Lot_Genealogy_WhereUsed LGI1
                       WHERE LGI1.MASTER_LOT_NUMBER = LGI.MASTER_LOT_NUMBER
                         AND LGI1.Ingred_LOT_NUMBER =
                             v_Transaction.Lot_Number
                         AND LGI1.Batch_Number = v_Transaction.Batch_Number
                         AND (v_Product.Product_Lot_Number IS NULL AND
                             LGI1.PRODUCT_LOT_NUMBER IS NULL OR
                             v_Product.Product_Lot_Number =
                             LGI1.PRODUCT_LOT_NUMBER));
          END LOOP;
        END IF;
      END IF;
      -- Record the transaction number we've just processed so we don't process it again
      v_MaxTransactionNumber := v_Transaction.Transaction_Number;
    END LOOP;
 
    -- Record the last transaction number we processed so we can go from there next time
    UPDATE NOETIX_SYS.Lot_Genealogy_Settings LGS
       SET LGS.VALUE_NUMBER = v_MaxTransactionNumber
     WHERE LGS.Setting_Name = 'MAX_TRANS_NO';
 
    COMMIT;
  end doUpdate;

end lot_genealogy;

Now you'll see that I've included a fair amount of comments on what the code is doing and also I've left in (but commented out) some of the debug lines I've been using. you can also tell my clear preference for using FOR ... LOOP instead of SELECT ... INTO statements (mainly so I don't have a temporary variable lying around or end up with a variable called v_Temp).

I hope this is fairly self-explanatory what it's doing. Let me know in the comments below if you have any questions or if anything is not clear.

*- Process Manufacturing means you take ingredients and you feed them into a process and make something as opposed to Discrete Manufacturing where you take parts and assemble them into something. The key difference is that you don't know how much (exactly) you will make in Process Manufacturing whereas for Discrete if you have all the parts of a car you will make precisely one car. Clear?

Thursday, August 23, 2012

PL/SQL: Using R12 Item Open Interface Tables

I'm sure we won't be the only company to need to do a batch update of all the existing Items (in INV.MTL_SYSTEM_ITEMS_B) so I thought I'd share this. Basically using the interface tables and Concurrent Request allows you to make updates to the existing reports in a controlled (and Oracle Approved) way.

I'm going to talk about doing an UPDATE but CREATE will work in pretty much the same way (for various reasons I'm not going to look at DELETE and I imagine - even if oracle supported it - most people would be in the same boat).

The first step is to identify the open interface table;
  • INV.MTL_System_items_Interface
First of all it's worth doing a quick SELECT * ... from the interface table and check to make sure it's empty before you start (ironically when writing this post I discovered two existing rows which had errored during our R12 upgrade 10 months ago so it's definitely worth a look!).

The columns from the MTL_System_Items_Interface table are listed at the every end of this post (just for your reference). The critical columns that have to have values in order for the update to work are;

Organization_Id
Process_Flag (must be 1)
Item_Number (of you can use SEGMENT1)
Transaction_Type (must be either UPDATE or CREATE)

Now if you want to insert records into the interface table it's probably best to use a SELECT as part of the SQL (i.e. have the rows you want to change listed in another table).

The SQL updates details on the item master from a table called PD_Item_Mst stored in a remove database across a database link (REMOTE_DATABASE). This updating flexfields in the item master (attributes 23,25,22, and 11). Here's the sample SQL;

INSERT INTO INV.MTL_SYSTEM_ITEMS_INTERFACE
  (ORGANIZATION_ID,
   PROCESS_FLAG,
   ITEM_NUMBER,
   TRANSACTION_TYPE,
   ATTRIBUTE23,
   ATTRIBUTE25,
   ATTRIBUTE22,
   ATTRIBUTE15)
  SELECT 1, -- organization_id
         1, -- process_flag
         item_no, -- item_number
         'UPDATE', -- transaction_type
         cd_flag, -- attribute23
         in_house, -- attribute25
         inv_class, -- attribute22
         DECODE(no_colours, 'INT', 98, 'CO', 97, 'FG', 99) -- attribute15
    FROM pd_item_mst@REMOTE_DATABASE


Once you've got the items loaded into the interface table you then need to log into Oracle e-Business Suite and perform the import.

Once you have logged in you'll need the "Inventory" reponsiiblity in order to make the changes. Once you have switched to the "Inventory" responsibility select Items then Import and finally "Import Items";

Importing Items


If, like us, you have multiple Organizations you will see the dialog above and have to pick which Organisation you wish to work with. When you've picked an Organization you will then be presented with the Concurrent Request screen;

Importing Items Concurrent Request
Make sure you have changed the Create or Update Option so that it is 1 for Inserting Items, and 2 for Updating Items. When you're ready click "Submit".

When completed check the INV.MTL_SYSTEM_ITEMS_INTERFACE table to see if any lines are remaining and are unprocessed. If any are unprocessed (erroring), then check in the INV.MTL_INTERFACE_ERRORS table for the reason.

Key to this working is that the ORG_ID in the lines is the same as the Concurrent Req (sounds obvious, but there is aflag which seems to indicate it will run for all orgs, but doesn’t). Also that the Transaction type in the lines matches the option in the concurrent request i.e. both Update, or both Create.

Hope you found this useful ...



List of Columns on the INV.MTL_System_Items_Interface table;
SQL> DESC INV.MTL_System_items_Interface
Name                           Type           Nullable Default Comments
------------------------------ -------------- -------- ------- --------
INVENTORY_ITEM_ID              NUMBER         Y                        
ORGANIZATION_ID                NUMBER         Y                        
LAST_UPDATE_DATE               DATE           Y                        
LAST_UPDATED_BY                NUMBER         Y                        
CREATION_DATE                  DATE           Y                        
CREATED_BY                     NUMBER         Y                        
LAST_UPDATE_LOGIN              NUMBER         Y                        
SUMMARY_FLAG                   VARCHAR2(1)    Y                        
ENABLED_FLAG                   VARCHAR2(1)    Y                        
START_DATE_ACTIVE              DATE           Y                        
END_DATE_ACTIVE                DATE           Y                        
DESCRIPTION                    VARCHAR2(240)  Y                        
BUYER_ID                       NUMBER         Y                        
ACCOUNTING_RULE_ID             NUMBER         Y                        
INVOICING_RULE_ID              NUMBER         Y                        
SEGMENT1                       VARCHAR2(40)   Y                        
SEGMENT2                       VARCHAR2(40)   Y                        
SEGMENT3                       VARCHAR2(40)   Y                        
SEGMENT4                       VARCHAR2(40)   Y                        
SEGMENT5                       VARCHAR2(40)   Y                        
SEGMENT6                       VARCHAR2(40)   Y                        
SEGMENT7                       VARCHAR2(40)   Y                        
SEGMENT8                       VARCHAR2(40)   Y                        
SEGMENT9                       VARCHAR2(40)   Y                        
SEGMENT10                      VARCHAR2(40)   Y                        
SEGMENT11                      VARCHAR2(40)   Y                        
SEGMENT12                      VARCHAR2(40)   Y                        
SEGMENT13                      VARCHAR2(40)   Y                        
SEGMENT14                      VARCHAR2(40)   Y                        
SEGMENT15                      VARCHAR2(40)   Y                        
SEGMENT16                      VARCHAR2(40)   Y                        
SEGMENT17                      VARCHAR2(40)   Y                        
SEGMENT18                      VARCHAR2(40)   Y                        
SEGMENT19                      VARCHAR2(40)   Y                        
SEGMENT20                      VARCHAR2(40)   Y                        
ATTRIBUTE_CATEGORY             VARCHAR2(30)   Y                        
ATTRIBUTE1                     VARCHAR2(240)  Y                        
ATTRIBUTE2                     VARCHAR2(240)  Y                        
ATTRIBUTE3                     VARCHAR2(240)  Y                        
ATTRIBUTE4                     VARCHAR2(240)  Y                        
ATTRIBUTE5                     VARCHAR2(240)  Y                        
ATTRIBUTE6                     VARCHAR2(240)  Y                        
ATTRIBUTE7                     VARCHAR2(240)  Y                        
ATTRIBUTE8                     VARCHAR2(240)  Y                        
ATTRIBUTE9                     VARCHAR2(240)  Y                        
ATTRIBUTE10                    VARCHAR2(240)  Y                        
ATTRIBUTE11                    VARCHAR2(240)  Y                        
ATTRIBUTE12                    VARCHAR2(240)  Y                        
ATTRIBUTE13                    VARCHAR2(240)  Y                        
ATTRIBUTE14                    VARCHAR2(240)  Y                        
ATTRIBUTE15                    VARCHAR2(240)  Y                        
PURCHASING_ITEM_FLAG           VARCHAR2(1)    Y                        
SHIPPABLE_ITEM_FLAG            VARCHAR2(1)    Y                        
CUSTOMER_ORDER_FLAG            VARCHAR2(1)    Y                        
INTERNAL_ORDER_FLAG            VARCHAR2(1)    Y                        
SERVICE_ITEM_FLAG              VARCHAR2(1)    Y                        
INVENTORY_ITEM_FLAG            VARCHAR2(1)    Y                        
ENG_ITEM_FLAG                  VARCHAR2(1)    Y                        
INVENTORY_ASSET_FLAG           VARCHAR2(1)    Y                        
PURCHASING_ENABLED_FLAG        VARCHAR2(1)    Y                        
CUSTOMER_ORDER_ENABLED_FLAG    VARCHAR2(1)    Y                        
INTERNAL_ORDER_ENABLED_FLAG    VARCHAR2(1)    Y                        
SO_TRANSACTIONS_FLAG           VARCHAR2(1)    Y                        
MTL_TRANSACTIONS_ENABLED_FLAG  VARCHAR2(1)    Y                        
STOCK_ENABLED_FLAG             VARCHAR2(1)    Y                        
BOM_ENABLED_FLAG               VARCHAR2(1)    Y                        
BUILD_IN_WIP_FLAG              VARCHAR2(1)    Y                        
REVISION_QTY_CONTROL_CODE      NUMBER         Y                        
ITEM_CATALOG_GROUP_ID          NUMBER         Y                        
CATALOG_STATUS_FLAG            VARCHAR2(1)    Y                        
RETURNABLE_FLAG                VARCHAR2(1)    Y                        
DEFAULT_SHIPPING_ORG           NUMBER         Y                        
COLLATERAL_FLAG                VARCHAR2(1)    Y                        
TAXABLE_FLAG                   VARCHAR2(1)    Y                        
QTY_RCV_EXCEPTION_CODE         VARCHAR2(25)   Y                        
ALLOW_ITEM_DESC_UPDATE_FLAG    VARCHAR2(1)    Y                        
INSPECTION_REQUIRED_FLAG       VARCHAR2(1)    Y                        
RECEIPT_REQUIRED_FLAG          VARCHAR2(1)    Y                        
MARKET_PRICE                   NUMBER         Y                        
HAZARD_CLASS_ID                NUMBER         Y                        
RFQ_REQUIRED_FLAG              VARCHAR2(1)    Y                        
QTY_RCV_TOLERANCE              NUMBER         Y                        
LIST_PRICE_PER_UNIT            NUMBER         Y                        
UN_NUMBER_ID                   NUMBER         Y                        
PRICE_TOLERANCE_PERCENT        NUMBER         Y                        
ASSET_CATEGORY_ID              NUMBER         Y                        
ROUNDING_FACTOR                NUMBER         Y                        
UNIT_OF_ISSUE                  VARCHAR2(25)   Y                        
ENFORCE_SHIP_TO_LOCATION_CODE  VARCHAR2(25)   Y                        
ALLOW_SUBSTITUTE_RECEIPTS_FLAG VARCHAR2(1)    Y                        
ALLOW_UNORDERED_RECEIPTS_FLAG  VARCHAR2(1)    Y                        
ALLOW_EXPRESS_DELIVERY_FLAG    VARCHAR2(1)    Y                        
DAYS_EARLY_RECEIPT_ALLOWED     NUMBER         Y                        
DAYS_LATE_RECEIPT_ALLOWED      NUMBER         Y                        
RECEIPT_DAYS_EXCEPTION_CODE    VARCHAR2(25)   Y                        
RECEIVING_ROUTING_ID           NUMBER         Y                        
INVOICE_CLOSE_TOLERANCE        NUMBER         Y                        
RECEIVE_CLOSE_TOLERANCE        NUMBER         Y                        
AUTO_LOT_ALPHA_PREFIX          VARCHAR2(30)   Y                        
START_AUTO_LOT_NUMBER          VARCHAR2(30)   Y                        
LOT_CONTROL_CODE               NUMBER         Y                        
SHELF_LIFE_CODE                NUMBER         Y                        
SHELF_LIFE_DAYS                NUMBER         Y                        
SERIAL_NUMBER_CONTROL_CODE     NUMBER         Y                        
START_AUTO_SERIAL_NUMBER       VARCHAR2(30)   Y                        
AUTO_SERIAL_ALPHA_PREFIX       VARCHAR2(30)   Y                        
SOURCE_TYPE                    NUMBER         Y                        
SOURCE_ORGANIZATION_ID         NUMBER         Y                        
SOURCE_SUBINVENTORY            VARCHAR2(10)   Y                        
EXPENSE_ACCOUNT                NUMBER         Y                        
ENCUMBRANCE_ACCOUNT            NUMBER         Y                        
RESTRICT_SUBINVENTORIES_CODE   NUMBER         Y                        
UNIT_WEIGHT                    NUMBER         Y                        
WEIGHT_UOM_CODE                VARCHAR2(3)    Y                        
VOLUME_UOM_CODE                VARCHAR2(3)    Y                        
UNIT_VOLUME                    NUMBER         Y                        
RESTRICT_LOCATORS_CODE         NUMBER         Y                        
LOCATION_CONTROL_CODE          NUMBER         Y                        
SHRINKAGE_RATE                 NUMBER         Y                        
ACCEPTABLE_EARLY_DAYS          NUMBER         Y                        
PLANNING_TIME_FENCE_CODE       NUMBER         Y                        
DEMAND_TIME_FENCE_CODE         NUMBER         Y                        
LEAD_TIME_LOT_SIZE             NUMBER         Y                        
STD_LOT_SIZE                   NUMBER         Y                        
CUM_MANUFACTURING_LEAD_TIME    NUMBER         Y                        
OVERRUN_PERCENTAGE             NUMBER         Y                        
MRP_CALCULATE_ATP_FLAG         VARCHAR2(1)    Y                        
ACCEPTABLE_RATE_INCREASE       NUMBER         Y                        
ACCEPTABLE_RATE_DECREASE       NUMBER         Y                        
CUMULATIVE_TOTAL_LEAD_TIME     NUMBER         Y                        
PLANNING_TIME_FENCE_DAYS       NUMBER         Y                        
DEMAND_TIME_FENCE_DAYS         NUMBER         Y                        
END_ASSEMBLY_PEGGING_FLAG      VARCHAR2(1)    Y                        
REPETITIVE_PLANNING_FLAG       VARCHAR2(1)    Y                        
PLANNING_EXCEPTION_SET         VARCHAR2(10)   Y                        
BOM_ITEM_TYPE                  NUMBER         Y                        
PICK_COMPONENTS_FLAG           VARCHAR2(1)    Y                        
REPLENISH_TO_ORDER_FLAG        VARCHAR2(1)    Y                        
BASE_ITEM_ID                   NUMBER         Y                        
ATP_COMPONENTS_FLAG            VARCHAR2(1)    Y                        
ATP_FLAG                       VARCHAR2(1)    Y                        
FIXED_LEAD_TIME                NUMBER         Y                        
VARIABLE_LEAD_TIME             NUMBER         Y                        
WIP_SUPPLY_LOCATOR_ID          NUMBER         Y                        
WIP_SUPPLY_TYPE                NUMBER         Y                        
WIP_SUPPLY_SUBINVENTORY        VARCHAR2(10)   Y                        
PRIMARY_UOM_CODE               VARCHAR2(3)    Y                        
PRIMARY_UNIT_OF_MEASURE        VARCHAR2(25)   Y                        
ALLOWED_UNITS_LOOKUP_CODE      NUMBER         Y                        
COST_OF_SALES_ACCOUNT          NUMBER         Y                        
SALES_ACCOUNT                  NUMBER         Y                        
DEFAULT_INCLUDE_IN_ROLLUP_FLAG VARCHAR2(1)    Y                        
INVENTORY_ITEM_STATUS_CODE     VARCHAR2(10)   Y                        
INVENTORY_PLANNING_CODE        NUMBER         Y                        
PLANNER_CODE                   VARCHAR2(10)   Y                        
PLANNING_MAKE_BUY_CODE         NUMBER         Y                        
FIXED_LOT_MULTIPLIER           NUMBER         Y                        
ROUNDING_CONTROL_TYPE          NUMBER         Y                        
CARRYING_COST                  NUMBER         Y                        
POSTPROCESSING_LEAD_TIME       NUMBER         Y                        
PREPROCESSING_LEAD_TIME        NUMBER         Y                        
FULL_LEAD_TIME                 NUMBER         Y                        
ORDER_COST                     NUMBER         Y                        
MRP_SAFETY_STOCK_PERCENT       NUMBER         Y                        
MRP_SAFETY_STOCK_CODE          NUMBER         Y                        
MIN_MINMAX_QUANTITY            NUMBER         Y                        
MAX_MINMAX_QUANTITY            NUMBER         Y                        
MINIMUM_ORDER_QUANTITY         NUMBER         Y                        
FIXED_ORDER_QUANTITY           NUMBER         Y                        
FIXED_DAYS_SUPPLY              NUMBER         Y                        
MAXIMUM_ORDER_QUANTITY         NUMBER         Y                        
ATP_RULE_ID                    NUMBER         Y                        
PICKING_RULE_ID                NUMBER         Y                        
RESERVABLE_TYPE                NUMBER         Y                        
POSITIVE_MEASUREMENT_ERROR     NUMBER         Y                        
NEGATIVE_MEASUREMENT_ERROR     NUMBER         Y                        
ENGINEERING_ECN_CODE           VARCHAR2(50)   Y                        
ENGINEERING_ITEM_ID            NUMBER         Y                        
ENGINEERING_DATE               DATE           Y                        
SERVICE_STARTING_DELAY         NUMBER         Y                        
VENDOR_WARRANTY_FLAG           VARCHAR2(1)    Y                        
SERVICEABLE_COMPONENT_FLAG     VARCHAR2(1)    Y                        
SERVICEABLE_PRODUCT_FLAG       VARCHAR2(1)    Y                        
BASE_WARRANTY_SERVICE_ID       NUMBER         Y                        
PAYMENT_TERMS_ID               NUMBER         Y                        
PREVENTIVE_MAINTENANCE_FLAG    VARCHAR2(1)    Y                        
PRIMARY_SPECIALIST_ID          NUMBER         Y                        
SECONDARY_SPECIALIST_ID        NUMBER         Y                        
SERVICEABLE_ITEM_CLASS_ID      NUMBER         Y                        
TIME_BILLABLE_FLAG             VARCHAR2(1)    Y                        
MATERIAL_BILLABLE_FLAG         VARCHAR2(30)   Y                        
EXPENSE_BILLABLE_FLAG          VARCHAR2(1)    Y                        
PRORATE_SERVICE_FLAG           VARCHAR2(1)    Y                        
COVERAGE_SCHEDULE_ID           NUMBER         Y                        
SERVICE_DURATION_PERIOD_CODE   VARCHAR2(10)   Y                        
SERVICE_DURATION               NUMBER         Y                        
WARRANTY_VENDOR_ID             NUMBER         Y                        
MAX_WARRANTY_AMOUNT            NUMBER         Y                        
RESPONSE_TIME_PERIOD_CODE      VARCHAR2(30)   Y                        
RESPONSE_TIME_VALUE            NUMBER         Y                        
NEW_REVISION_CODE              VARCHAR2(30)   Y                        
INVOICEABLE_ITEM_FLAG          VARCHAR2(1)    Y                        
TAX_CODE                       VARCHAR2(50)   Y                        
INVOICE_ENABLED_FLAG           VARCHAR2(1)    Y                        
MUST_USE_APPROVED_VENDOR_FLAG  VARCHAR2(1)    Y                        
REQUEST_ID                     NUMBER         Y                        
PROGRAM_APPLICATION_ID         NUMBER         Y                        
PROGRAM_ID                     NUMBER         Y                        
PROGRAM_UPDATE_DATE            DATE           Y                        
OUTSIDE_OPERATION_FLAG         VARCHAR2(1)    Y                        
OUTSIDE_OPERATION_UOM_TYPE     VARCHAR2(25)   Y                        
SAFETY_STOCK_BUCKET_DAYS       NUMBER         Y                        
AUTO_REDUCE_MPS                NUMBER(22)     Y                        
COSTING_ENABLED_FLAG           VARCHAR2(1)    Y                        
CYCLE_COUNT_ENABLED_FLAG       VARCHAR2(1)    Y                        
DEMAND_SOURCE_LINE             VARCHAR2(30)   Y                        
COPY_ITEM_ID                   NUMBER         Y                        
SET_ID                         VARCHAR2(10)   Y                        
REVISION                       VARCHAR2(3)    Y                        
AUTO_CREATED_CONFIG_FLAG       VARCHAR2(1)    Y                        
ITEM_TYPE                      VARCHAR2(30)   Y                        
MODEL_CONFIG_CLAUSE_NAME       VARCHAR2(10)   Y                        
SHIP_MODEL_COMPLETE_FLAG       VARCHAR2(1)    Y                        
MRP_PLANNING_CODE              NUMBER         Y                        
RETURN_INSPECTION_REQUIREMENT  NUMBER         Y                        
DEMAND_SOURCE_TYPE             NUMBER         Y                        
DEMAND_SOURCE_HEADER_ID        NUMBER         Y                        
TRANSACTION_ID                 NUMBER         Y                        
PROCESS_FLAG                   NUMBER         Y                        
ORGANIZATION_CODE              VARCHAR2(3)    Y                        
ITEM_NUMBER                    VARCHAR2(700)  Y                        
COPY_ITEM_NUMBER               VARCHAR2(81)   Y                        
TEMPLATE_ID                    NUMBER         Y                        
TEMPLATE_NAME                  VARCHAR2(30)   Y                        
COPY_ORGANIZATION_ID           NUMBER         Y                        
COPY_ORGANIZATION_CODE         VARCHAR2(3)    Y                        
ATO_FORECAST_CONTROL           NUMBER         Y                        
TRANSACTION_TYPE               VARCHAR2(10)   Y                        
MATERIAL_COST                  NUMBER         Y                        
MATERIAL_SUB_ELEM              VARCHAR2(10)   Y                        
MATERIAL_OH_RATE               NUMBER         Y                        
MATERIAL_OH_SUB_ELEM           VARCHAR2(10)   Y                        
MATERIAL_SUB_ELEM_ID           NUMBER         Y                        
MATERIAL_OH_SUB_ELEM_ID        NUMBER         Y                        
AUTO_REL_TIME_FENCE_CODE       NUMBER         Y                        
AUTO_REL_TIME_FENCE_DAYS       NUMBER         Y                        
CONTAINER_ITEM_FLAG            VARCHAR2(1)    Y                        
VEHICLE_ITEM_FLAG              VARCHAR2(1)    Y                        
MAXIMUM_LOAD_WEIGHT            NUMBER         Y                        
MINIMUM_FILL_PERCENT           NUMBER         Y                        
CONTAINER_TYPE_CODE            VARCHAR2(30)   Y                        
INTERNAL_VOLUME                NUMBER         Y                        
SET_PROCESS_ID                 NUMBER                  0               
CHECK_SHORTAGES_FLAG           VARCHAR2(1)    Y                        
RELEASE_TIME_FENCE_CODE        NUMBER         Y                        
RELEASE_TIME_FENCE_DAYS        NUMBER         Y                        
WH_UPDATE_DATE                 DATE           Y                        
PRODUCT_FAMILY_ITEM_ID         NUMBER         Y                        
PURCHASING_TAX_CODE            VARCHAR2(50)   Y                        
OVERCOMPLETION_TOLERANCE_TYPE  NUMBER         Y                        
OVERCOMPLETION_TOLERANCE_VALUE NUMBER         Y                        
EFFECTIVITY_CONTROL            NUMBER         Y                        
GLOBAL_ATTRIBUTE_CATEGORY      VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE1              VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE2              VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE3              VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE4              VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE5              VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE6              VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE7              VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE8              VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE9              VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE10             VARCHAR2(150)  Y                        
OVER_SHIPMENT_TOLERANCE        NUMBER         Y                        
UNDER_SHIPMENT_TOLERANCE       NUMBER         Y                        
OVER_RETURN_TOLERANCE          NUMBER         Y                        
UNDER_RETURN_TOLERANCE         NUMBER         Y                        
EQUIPMENT_TYPE                 NUMBER         Y                        
RECOVERED_PART_DISP_CODE       VARCHAR2(30)   Y                        
DEFECT_TRACKING_ON_FLAG        VARCHAR2(1)    Y                        
USAGE_ITEM_FLAG                VARCHAR2(1)    Y                        
EVENT_FLAG                     VARCHAR2(1)    Y                        
ELECTRONIC_FLAG                VARCHAR2(1)    Y                        
DOWNLOADABLE_FLAG              VARCHAR2(1)    Y                        
VOL_DISCOUNT_EXEMPT_FLAG       VARCHAR2(1)    Y                        
COUPON_EXEMPT_FLAG             VARCHAR2(1)    Y                        
COMMS_NL_TRACKABLE_FLAG        VARCHAR2(1)    Y                        
ASSET_CREATION_CODE            VARCHAR2(30)   Y                        
COMMS_ACTIVATION_REQD_FLAG     VARCHAR2(1)    Y                        
ORDERABLE_ON_WEB_FLAG          VARCHAR2(1)    Y                        
BACK_ORDERABLE_FLAG            VARCHAR2(1)    Y                        
WEB_STATUS                     VARCHAR2(30)   Y                        
INDIVISIBLE_FLAG               VARCHAR2(1)    Y                        
LONG_DESCRIPTION               VARCHAR2(4000) Y                        
DIMENSION_UOM_CODE             VARCHAR2(3)    Y                        
UNIT_LENGTH                    NUMBER         Y                        
UNIT_WIDTH                     NUMBER         Y                        
UNIT_HEIGHT                    NUMBER         Y                        
BULK_PICKED_FLAG               VARCHAR2(1)    Y                        
LOT_STATUS_ENABLED             VARCHAR2(1)    Y                        
DEFAULT_LOT_STATUS_ID          NUMBER         Y                        
SERIAL_STATUS_ENABLED          VARCHAR2(1)    Y                        
DEFAULT_SERIAL_STATUS_ID       NUMBER         Y                        
LOT_SPLIT_ENABLED              VARCHAR2(1)    Y                        
LOT_MERGE_ENABLED              VARCHAR2(1)    Y                        
INVENTORY_CARRY_PENALTY        NUMBER         Y                        
OPERATION_SLACK_PENALTY        NUMBER         Y                        
FINANCING_ALLOWED_FLAG         VARCHAR2(1)    Y                        
EAM_ITEM_TYPE                  NUMBER         Y                        
EAM_ACTIVITY_TYPE_CODE         VARCHAR2(30)   Y                        
EAM_ACTIVITY_CAUSE_CODE        VARCHAR2(30)   Y                        
EAM_ACT_NOTIFICATION_FLAG      VARCHAR2(1)    Y                        
EAM_ACT_SHUTDOWN_STATUS        VARCHAR2(30)   Y                        
DUAL_UOM_CONTROL               NUMBER         Y                        
SECONDARY_UOM_CODE             VARCHAR2(3)    Y                        
DUAL_UOM_DEVIATION_HIGH        NUMBER         Y                        
DUAL_UOM_DEVIATION_LOW         NUMBER         Y                        
CONTRACT_ITEM_TYPE_CODE        VARCHAR2(30)   Y                        
SUBSCRIPTION_DEPEND_FLAG       VARCHAR2(1)    Y                        
SERV_REQ_ENABLED_CODE          VARCHAR2(30)   Y                        
SERV_BILLING_ENABLED_FLAG      VARCHAR2(1)    Y                        
SERV_IMPORTANCE_LEVEL          NUMBER         Y                        
PLANNED_INV_POINT_FLAG         VARCHAR2(1)    Y                        
LOT_TRANSLATE_ENABLED          VARCHAR2(1)    Y                        
DEFAULT_SO_SOURCE_TYPE         VARCHAR2(30)   Y                        
CREATE_SUPPLY_FLAG             VARCHAR2(1)    Y                        
SUBSTITUTION_WINDOW_CODE       NUMBER         Y                        
SUBSTITUTION_WINDOW_DAYS       NUMBER         Y                        
IB_ITEM_INSTANCE_CLASS         VARCHAR2(30)   Y                        
CONFIG_MODEL_TYPE              VARCHAR2(30)   Y                        
LOT_SUBSTITUTION_ENABLED       VARCHAR2(1)    Y                        
MINIMUM_LICENSE_QUANTITY       NUMBER         Y                        
EAM_ACTIVITY_SOURCE_CODE       VARCHAR2(30)   Y                        
LIFECYCLE_ID                   NUMBER         Y                        
CURRENT_PHASE_ID               NUMBER         Y                        
TRACKING_QUANTITY_IND          VARCHAR2(30)   Y                        
ONT_PRICING_QTY_SOURCE         VARCHAR2(30)   Y                        
SECONDARY_DEFAULT_IND          VARCHAR2(30)   Y                        
VMI_MINIMUM_UNITS              NUMBER         Y                        
VMI_MINIMUM_DAYS               NUMBER         Y                        
VMI_MAXIMUM_UNITS              NUMBER         Y                        
VMI_MAXIMUM_DAYS               NUMBER         Y                        
VMI_FIXED_ORDER_QUANTITY       NUMBER         Y                        
SO_AUTHORIZATION_FLAG          NUMBER         Y                        
CONSIGNED_FLAG                 NUMBER         Y                        
ASN_AUTOEXPIRE_FLAG            NUMBER         Y                        
VMI_FORECAST_TYPE              NUMBER         Y                        
FORECAST_HORIZON               NUMBER         Y                        
EXCLUDE_FROM_BUDGET_FLAG       NUMBER         Y                        
DAYS_TGT_INV_SUPPLY            NUMBER         Y                        
DAYS_TGT_INV_WINDOW            NUMBER         Y                        
DAYS_MAX_INV_SUPPLY            NUMBER         Y                        
DAYS_MAX_INV_WINDOW            NUMBER         Y                        
DRP_PLANNED_FLAG               NUMBER         Y                        
CRITICAL_COMPONENT_FLAG        NUMBER         Y                        
CONTINOUS_TRANSFER             NUMBER         Y                        
CONVERGENCE                    NUMBER         Y                        
DIVERGENCE                     NUMBER         Y                        
CONFIG_ORGS                    VARCHAR2(30)   Y                        
CONFIG_MATCH                   VARCHAR2(30)   Y                        
ATTRIBUTE16                    VARCHAR2(240)  Y                        
ATTRIBUTE17                    VARCHAR2(240)  Y                        
ATTRIBUTE18                    VARCHAR2(240)  Y                        
ATTRIBUTE19                    VARCHAR2(240)  Y                        
ATTRIBUTE20                    VARCHAR2(240)  Y                        
ATTRIBUTE21                    VARCHAR2(240)  Y                        
ATTRIBUTE22                    VARCHAR2(240)  Y                        
ATTRIBUTE23                    VARCHAR2(240)  Y                        
ATTRIBUTE24                    VARCHAR2(240)  Y                        
ATTRIBUTE25                    VARCHAR2(240)  Y                        
ATTRIBUTE26                    VARCHAR2(240)  Y                        
ATTRIBUTE27                    VARCHAR2(240)  Y                        
ATTRIBUTE28                    VARCHAR2(240)  Y                        
ATTRIBUTE29                    VARCHAR2(240)  Y                        
ATTRIBUTE30                    VARCHAR2(240)  Y                        
CAS_NUMBER                     VARCHAR2(30)   Y                        
CHILD_LOT_FLAG                 VARCHAR2(1)    Y                        
CHILD_LOT_PREFIX               VARCHAR2(30)   Y                        
CHILD_LOT_STARTING_NUMBER      NUMBER         Y                        
CHILD_LOT_VALIDATION_FLAG      VARCHAR2(1)    Y                        
COPY_LOT_ATTRIBUTE_FLAG        VARCHAR2(1)    Y                        
DEFAULT_GRADE                  VARCHAR2(150)  Y                        
EXPIRATION_ACTION_CODE         VARCHAR2(32)   Y                        
EXPIRATION_ACTION_INTERVAL     NUMBER         Y                        
GRADE_CONTROL_FLAG             VARCHAR2(1)    Y                        
HAZARDOUS_MATERIAL_FLAG        VARCHAR2(1)    Y                        
HOLD_DAYS                      NUMBER         Y                        
LOT_DIVISIBLE_FLAG             VARCHAR2(1)    Y                        
MATURITY_DAYS                  NUMBER         Y                        
PARENT_CHILD_GENERATION_FLAG   VARCHAR2(1)    Y                        
PROCESS_COSTING_ENABLED_FLAG   VARCHAR2(1)    Y                        
PROCESS_EXECUTION_ENABLED_FLAG VARCHAR2(1)    Y                        
PROCESS_QUALITY_ENABLED_FLAG   VARCHAR2(1)    Y                        
PROCESS_SUPPLY_LOCATOR_ID      NUMBER         Y                        
PROCESS_SUPPLY_SUBINVENTORY    VARCHAR2(10)   Y                        
PROCESS_YIELD_LOCATOR_ID       NUMBER         Y                        
PROCESS_YIELD_SUBINVENTORY     VARCHAR2(10)   Y                        
RECIPE_ENABLED_FLAG            VARCHAR2(1)    Y                        
RETEST_INTERVAL                NUMBER         Y                        
CHARGE_PERIODICITY_CODE        VARCHAR2(3)    Y                        
REPAIR_LEADTIME                NUMBER         Y                        
REPAIR_YIELD                   NUMBER         Y                        
PREPOSITION_POINT              VARCHAR2(1)    Y                        
REPAIR_PROGRAM                 NUMBER         Y                        
SUBCONTRACTING_COMPONENT       NUMBER         Y                        
OUTSOURCED_ASSEMBLY            NUMBER         Y                        
SOURCE_SYSTEM_ID               NUMBER         Y                        
SOURCE_SYSTEM_REFERENCE        VARCHAR2(255)  Y                        
SOURCE_SYSTEM_REFERENCE_DESC   VARCHAR2(240)  Y                        
GLOBAL_TRADE_ITEM_NUMBER       VARCHAR2(14)   Y                        
CONFIRM_STATUS                 VARCHAR2(3)    Y                        
CHANGE_ID                      NUMBER         Y                        
CHANGE_LINE_ID                 NUMBER         Y                        
ITEM_CATALOG_GROUP_NAME        VARCHAR2(820)  Y                        
REVISION_IMPORT_POLICY         VARCHAR2(30)   Y                        
GTIN_DESCRIPTION               VARCHAR2(240)  Y                        
INTERFACE_TABLE_UNIQUE_ID      NUMBER         Y                        
GDSN_OUTBOUND_ENABLED_FLAG     VARCHAR2(1)    Y                        
TRADE_ITEM_DESCRIPTOR          VARCHAR2(35)   Y                        
STYLE_ITEM_ID                  NUMBER         Y                        
STYLE_ITEM_FLAG                VARCHAR2(1)    Y                        
STYLE_ITEM_NUMBER              VARCHAR2(700)  Y                        
COPY_REVISION_ID               NUMBER         Y                        
BUNDLE_ID                      NUMBER         Y                        
MESSAGE_TIMESTAMP              DATE           Y                        
MESSAGE_ID                     NUMBER         Y                        
OPERATION                      VARCHAR2(80)   Y                        
TOP_ITEM_FLAG                  VARCHAR2(1)    Y                        
GPC_CODE                       VARCHAR2(8)    Y                        
GLOBAL_ATTRIBUTE11             VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE12             VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE13             VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE14             VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE15             VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE16             VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE17             VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE18             VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE19             VARCHAR2(150)  Y                        
GLOBAL_ATTRIBUTE20             VARCHAR2(150)  Y