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

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


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

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


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

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

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

Figure 1: "Alert Manager" Responsibility

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

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

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

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

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

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

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

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

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

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

Using these values we can call the API;

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

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

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

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

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

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

Figure 2: Completed System 

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

Enabling Auto-Login Under Windows 7

First of all this is a MASSIVE security hole if you choose to enable it - anyone getting their hands on your machine will have full control over everything (logged into you bank? your Facebook account? your photos? the lot).

There are, however, very good reasons to actually do this; for example I have setup a Windows 7 machine which just runs iTunes because that's all I need - clearly it's pointless to password protect this as all my data is on (non-protected) NAS drive.

So here are the instructions;
1) Go to the start menu
2) type into the "search programs and files" text box the words; "control userpasswords2" and hit return
3) This dialog will appear;

User Accounts Dialog (Windows 7)

4) Make sure the checkbox at the very top (titled "Users must enter a user name and password to use this computer") is unchecked. You will then be prompted to enter your password.

And that's it, when you reboot your machine you will no longer be prompted for a password.

Oracle EBS: Cloning Script To Clear Email Addresses

We use the following cloning script to clear down email addresses on each of the cloned systems we create from our production EBS system. In exchange we have setup a mailbox which all the test systems feed into and each tiem we create a new clone we add an email address to the mailbox so that it receives emails from the new clone (this way you can filter the messages in the mailbox to get the ones you're interested in for the clone system you are looking at).

The script itself sends an email with what it's done to the mail box (so we know when the system was last cloned), it also includes a check to make sure it's not being run against production (PROD) by accident!

The actual script (which was written against Oracle version 12.1.3) is below;

declare
  c_EMAIL constant varchar2(60) := '%DATABASE%_APPSTEST@<your domain>';

  v_Email    PO_VENDOR_SITES_ALL.EMAIL_ADDRESS%TYPE;
  v_Database v$Database.NAME%TYPE;
  v_Body     CLOB;
  procedure addToBody(p_Text in varchar2) as
  begin
    dbms_lob.writeappend(v_Body, length(p_Text) + 4, p_Text || '<br>');
    dbms_output.put_line(p_Text);
  end;
begin
  dbms_lob.createtemporary(lob_loc => v_Body, cache => False);

  addToBody('0010 Checking database instance ... ');
  select name into v_Database from v$Database;
  dbms_output.put_line('020 Database instance = "' || v_Database || '"');
  if v_Database = 'PROD' then
    raise_application_error(-20005,
                            'This script must NOT be run against PROD.');
  end if;

  v_Email := replace(c_EMAIL, '%DATABASE%', v_Database);
  addToBody('0100 All emails will be set to "' || v_Email || '"');

  addToBody('0110 Updating AP supplier contacts (AP_SUPPLIER_CONTACTS) records ... ');
  update AP_SUPPLIER_CONTACTS
     set EMAIL_ADDRESS = v_Email
   where EMAIL_ADDRESS is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0120 Updating AP supplier site details (AP_SUPPLIER_SITES_ALL) records ... ');
  update AP_SUPPLIER_SITES_ALL
     set EMAIL_ADDRESS = v_Email
   where EMAIL_ADDRESS is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) ||
            ' records updated (EMAIL_ADDRESS)');
  update AP_SUPPLIER_SITES_ALL
     set REMITTANCE_EMAIL = v_Email
   where REMITTANCE_EMAIL is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) ||
            ' records updated (REMITTANCE_EMAIL)');

  addToBody('0130 Updating User (FND_USER) records ... ');
  update fnd_user
     set email_address = v_Email
   where email_address is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0140 Updating Vendor Contact Points (HZ_CONTACT_POINTS) records ... ');
  update AR.HZ_CONTACT_POINTS
     set EMAIL_ADDRESS = v_Email
   where EMAIL_ADDRESS is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0150 Updating Parties (HZ_PARTIES) records ... ');
  update HZ_PARTIES
     set email_address = v_Email
   where email_address is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0160 Updating external payees (IBY_EXTERNAL_PAYEES_ALL) records ... ');
  update IBY.IBY_EXTERNAL_PAYEES_ALL
     set remit_advice_email = v_Email
   where remit_advice_email is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0170 Updating Employee (PER_PEOPLE_X) records ... ');
  update PER_PEOPLE_X
     set email_address = v_Email
   where email_address is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0180 Updating points (RA_CONTACT_PHONES_INT_ALL) records ... ');
  update RA_CONTACT_PHONES_INT_ALL
     set EMAIL_ADDRESS = v_Email
   where EMAIL_ADDRESS is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0190 Updating Supplier (PO_VENDOR_SITES_ALL) records ... ');
  update PO_VENDOR_SITES_ALL
     set email_address = v_Email
   where email_address is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  commit;

  -- Send an e-mail to the mailbox so you know when it was last refreshed.
  napp_emailmanager.addEmail(p_From    => v_Email,
                             p_To      => v_Email,
                             p_Subject => v_Database || ' refreshed on ' ||
                                          to_char(SYSDATE, 'DD-MON-YYYY'),
                             p_Body    => v_Body);
  napp_emailmanager.processMails;
end;

Have I missed anything?

Noetix: XU2/XU5 Scripts run but generate no logfiles!

This problem has been vexing me for the last 30 minutes. After I found the solution I thought I'd post it here just in case other people are experiencing the same issue!

The first thing to do is to open the file for which you aren't seeing a logfile and check to make sure that the first line begins with;

@utlspon <filename>

Where the name of the file is the same as the file you're running without any extension.

Assuming everything in your file is correct then you need to open the *previous* file in XU2/XU5 and make sure that it ends with;

@utlspoff

If this is missing the log details for your script will just be appended to those of the previous script.

Now assuming that you have used @utlspon/off correctly and you're still not seeing a log file and you're certain your script is running (the situation I found myself in) then check to see if you have used;

SET SCAN OFF

In any of your files. If you turn this off (usually so you can insert some special characters into a field) then you must make sure that before you call @utlspoff you turn it back on by executing SET SCAN ON. If you don't then, for some reason, you no longer get logfiles.

This proved to be the cause of the problem we were seeing.

Noetix: Removing Item Information Caching @ Release 12

We use Oracle E-Business Suite to manage our (pharmaceutical) production facility. Because of the nature of the work we do we create items, batches, and lots every single day of the week. Normally this isn't a problem but in Noetix views item information is cached in the N_KFF_SYS_Item table and is only refreshable by using a supplied concurrent processes.

Clearly as we're creating multiple items every day it's just not practical for us to add a "Now run the concurrent process" step across our business to make sure that the reporting information is up to date for those running reports. It also creates an issue of "trust" - if users are looking at cached data is it up to date or not?

Looking at the source code for the Noetix views which access the cached information rather than going directly to the cached table they instead go via a view; XXK_Sys_Item which just does a direct select from the table  (I'm making a point here of not just copy/pasting blocks of Noetix source code so you'll find a lot of this is instructions on how to find the code rather than the code itself). The benefit of this approach is that if we want to stop using caching we just replace the view with a new view that goes directly to the table. You may be thinking that it might actually be easier to replace the references to the view in the Noetix configuration tables but unfortunately this isn't actually possible. If you look in the table N_View_Table_Templates (or just N_View_Tables) for the view_label INV_Items you will notice that the only NOETIX table in use is N_MFG_LOOKUPS_VL - no mention of the XXK_Sys_Item view. This must be added "elsewhere".

Doing a search of the source code (.sql) files in the Noetix install directory for the table name reveals that the regenerate process builds a package called N_KFF_SYS_Item_Pkg to handle the management of the cached table.

So in summary here is the structure (with INVG0_Items being the public view we're going to use for testing);

Looking in more detail at the N_Kff_SYS_Item_Pkg package we can see that the routine called Init_Upld (which is the basis for the concurrent request) does the initial upload of rows into the table - it's the SQL to do this that we need to build into a view to replace the XXK_Sys_Item view.

In the source code (in the N_KFF_Sys_Item_pkg.sql file) I'm looking at line 145 which does the insert (it reads "INSERT  /*+ APPEND */ INTO N_KFF_Sys_Item" if you want to search for it in your Source code. The format for the insert is a simple SELECT ... which makes life a lot easier when you're looking at creating a view!

Looking at the columns inserted not all of them are making it through to the view (last_update_date for instance) so this column isn't needed in our replacement view. Clearly some clever logic has gone into building this view which would pick up multiple segments and structures but in our system it seems a lot of this is wasted as the data (probably explaining why we feel caching is unnecessary) seems to default to a single value. Looking at this code seems to indicate that the SQL we need to build the replacement view is;

CREATE OR REPLACE VIEW XXK_SYS_ITEM AS
SELECT
       msib.SEGMENT1 CV$System_Item_KFF,
       msib.INVENTORY_ITEM_ID,
       msib.ORGANIZATION_ID,
       msib.SEGMENT1 SV$ITEM,
       'Item' SEGMENT_NAME_LIST,
       101 STRUCTURE_ID,
       'System Items' STRUCTURE_NAME,
       null Z$XXK_SYS_ITEM
  FROM INV.MTL_SYSTEM_ITEMS_B msib;

Clearly this is quite a substantial change so what we need to do is provide us with a way to back it out quickly after it's gone live in the event of substantial problems being found (low level-testing has been done but this will never cover 350+ reports) so the script to make the update will rename the existing view rather than just overwriting it so we can do a quick rollback*. Here is the script;

@utlspon xu6_replace_xxk_sys_item_view

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

RENAME XXK_SYS_ITEM TO XXK_SYS_ITEM_BK
/

CREATE OR REPLACE VIEW XXK_SYS_ITEM AS
SELECT
       msib.SEGMENT1 CV$System_Item_KFF,
       msib.INVENTORY_ITEM_ID,
       msib.ORGANIZATION_ID,
       msib.SEGMENT1 SV$ITEM,
       'Item' SEGMENT_NAME_LIST,
       101 STRUCTURE_ID,
       'System Items' STRUCTURE_NAME,
       null Z$XXK_SYS_ITEM
  FROM INV.MTL_SYSTEM_ITEMS_B msib
/


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

This is available as a file here (via Google Docs).

You then need to add a call in XU6 to call the new file.

*- Just drop the new view and reverse the "RENAME ..."

UPDATE (11-JAN-2012): I've added in a section to the script to do a COMPILE of the views that are invalidated by this change. Normally it wouldn't be required as the first query will automatically recompile the view - however we have a check in XU6 for invalid views so they need to be valid by that stage otherwise our regenerate fails.

Noetix: Checking Incremental Refresh

The following script will generate a piece of SQL that will check the current status (and any pending records) for the Noetix Key Flex Field (KFF) Incremental Refresh process;

begin
  for v_Kff in (SELECT N.*,
                       (select max(data_table_key) from n_f_kff_flex_sources) as max_data_table_key
                  FROM n_f_kff_flex_sources N
                 ORDER BY N.data_table_key) loop
    dbms_output.put_line('SELECT ''' || v_kff.kff_view_description ||
                         ''' kff_view_description, ');
    dbms_output.put_line('       ''' || v_kff.target_value_object_name ||
                         ''' kff_cache_table_name, ');
    dbms_output.put_line('       ''' || v_kff.data_application_table_name ||
                         ''' oracle_cache_source_table, ');
    dbms_output.put_line('       (SELECT ut.status ');
    dbms_output.put_line('          FROM user_triggers ut ');
    dbms_output.put_line('         WHERE ut.trigger_name = ''' ||
                         UPPER(v_kff.cd_trigger_name) ||
                         ''') trigger_status, ');
    dbms_output.put_line('       (SELECT COUNT(*) FROM ' ||
                         v_kff.target_value_object_name ||
                         ') current_kff_cached_rowcount, ');
    dbms_output.put_line('       (SELECT COUNT(*) FROM APPS.' ||
                         v_kff.data_application_table_name ||
                         ') current_oracle_source_rowcount, ');
    dbms_output.put_line('       (SELECT COUNT(*) FROM ' ||
                         v_kff.cd_table_name || ') pending_cd_rowcount, ');
    dbms_output.put_line('       (SELECT COUNT(*) FROM ' ||
                         v_kff.cde_table_name ||
                         ') pending_cde_rowcount, ');
    dbms_output.put_line('       (SELECT COUNT(*) FROM ' ||
                         v_kff.cdeh_table_name ||
                         ') pending_cdeh_rowcount ');
    dbms_output.put_line('FROM DUAL ');
    if (v_Kff.Max_Data_Table_Key != v_Kff.Data_Table_Key) then
      dbms_output.put_line('UNION');
    end if;
  end loop;
end;

On our system this generates the following SQL;

SELECT 'Accounting Key Flexfield' kff_view_description,
       'N_KFF_GL_Acct' kff_cache_table_name,
       'GL_CODE_COMBINATIONS' oracle_cache_source_table,
       (SELECT ut.status
          FROM user_triggers ut
         WHERE ut.trigger_name = 'N_CDT_GL_ACCT') trigger_status,
       (SELECT COUNT(*) FROM N_KFF_GL_Acct) current_kff_cached_rowcount,
       (SELECT COUNT(*) FROM APPS.GL_CODE_COMBINATIONS) current_oracle_source_rowcount,
       (SELECT COUNT(*) FROM N_CD_GL_Acct) pending_cd_rowcount,
       (SELECT COUNT(*) FROM N_CDE_GL_Acct) pending_cde_rowcount,
       (SELECT COUNT(*) FROM N_CDEH_GL_Acct) pending_cdeh_rowcount
FROM DUAL
UNION
SELECT 'Item Categories Key Flexfield' kff_view_description,
       'N_KFF_Mtl_Cat' kff_cache_table_name,
       'MTL_CATEGORIES_B' oracle_cache_source_table,
       (SELECT ut.status
          FROM user_triggers ut
         WHERE ut.trigger_name = 'N_CDT_MTL_CAT') trigger_status,
       (SELECT COUNT(*) FROM N_KFF_Mtl_Cat) current_kff_cached_rowcount,
       (SELECT COUNT(*) FROM APPS.MTL_CATEGORIES_B) current_oracle_source_rowcount,
       (SELECT COUNT(*) FROM N_CD_Mtl_Cat) pending_cd_rowcount,
       (SELECT COUNT(*) FROM N_CDE_Mtl_Cat) pending_cde_rowcount,
       (SELECT COUNT(*) FROM N_CDEH_Mtl_Cat) pending_cdeh_rowcount
FROM DUAL
UNION
SELECT 'Item Catalogs Key Flexfield' kff_view_description,
       'N_KFF_Ctlg_Grp' kff_cache_table_name,
       'MTL_ITEM_CATALOG_GROUPS_B' oracle_cache_source_table,
       (SELECT ut.status
          FROM user_triggers ut
         WHERE ut.trigger_name = 'N_CDT_CTLG_GRP') trigger_status,
       (SELECT COUNT(*) FROM N_KFF_Ctlg_Grp) current_kff_cached_rowcount,
       (SELECT COUNT(*) FROM APPS.MTL_ITEM_CATALOG_GROUPS_B) current_oracle_source_rowcount,
       (SELECT COUNT(*) FROM N_CD_Ctlg_Grp) pending_cd_rowcount,
       (SELECT COUNT(*) FROM N_CDE_Ctlg_Grp) pending_cde_rowcount,
       (SELECT COUNT(*) FROM N_CDEH_Ctlg_Grp) pending_cdeh_rowcount
FROM DUAL
UNION
SELECT 'System Items Key Flexfield' kff_view_description,
       'N_KFF_Sys_Item' kff_cache_table_name,
       'MTL_SYSTEM_ITEMS_B' oracle_cache_source_table,
       (SELECT ut.status
          FROM user_triggers ut
         WHERE ut.trigger_name = 'N_CDT_SYS_ITEM') trigger_status,
       (SELECT COUNT(*) FROM N_KFF_Sys_Item) current_kff_cached_rowcount,
       (SELECT COUNT(*) FROM APPS.MTL_SYSTEM_ITEMS_B) current_oracle_source_rowcount,
       (SELECT COUNT(*) FROM N_CD_Sys_Item) pending_cd_rowcount,
       (SELECT COUNT(*) FROM N_CDE_Sys_Item) pending_cde_rowcount,
       (SELECT COUNT(*) FROM N_CDEH_Sys_Item) pending_cdeh_rowcount
FROM DUAL
UNION
SELECT 'Stock Locators Key Flexfield' kff_view_description,
       'N_KFF_Item_Loc' kff_cache_table_name,
       'MTL_ITEM_LOCATIONS' oracle_cache_source_table,
       (SELECT ut.status
          FROM user_triggers ut
         WHERE ut.trigger_name = 'N_CDT_ITEM_LOC') trigger_status,
       (SELECT COUNT(*) FROM N_KFF_Item_Loc) current_kff_cached_rowcount,
       (SELECT COUNT(*) FROM APPS.MTL_ITEM_LOCATIONS) current_oracle_source_rowcount,
       (SELECT COUNT(*) FROM N_CD_Item_Loc) pending_cd_rowcount,
       (SELECT COUNT(*) FROM N_CDE_Item_Loc) pending_cde_rowcount,
       (SELECT COUNT(*) FROM N_CDEH_Item_Loc) pending_cdeh_rowcount
FROM DUAL

This generates output similar to the following (on our production system);

Noetix: INV_Period_Close_Details and Process Manufacturing (at Release 12)

If you are one of Oracles (few) Process Manufacturing module customers like us you will already have noticed that there are substantual changes to Noetix when you go from Release 11 to Release 12. One of the most significant is the change from using the GMI_Month_End_Inventory view (which has been rendered obsolete by Oracles changes) to using the INV_Period_Close_Details template.

The first thing you'll notice when running against R12 is that if you do;

SELECT *

  FROM INV_Period_Close_Details;

In your test system you will get ZERO rows (unless you are also an Oracle Discrete Manufacturing customer in which case you will just get your rows from Discrete manufacturing).

The reason for this is the inclusion of the table BOM.CST_PERIOD_CLOSE_SUMMARY as the source for the costing data. This table is not used by Process Manufacturing (the correct table in GMF.GMF_PERIOD_BALANCES).

You have to wonder exactly what Process Manufacturing testing Noetix has done as this was *clearly* never going to work.

Luckily (for everyone else) we have been through the support loop with Noetix and they have produced a modified version of the INV_Period_Close_Details views which not only includes Process Manufacturing Costs but also includes Lot details - something that will be necessary if you are migrating from the Release 11 view to the Release 12 view.

The support reference number is B27089.

What the file does (for those of you trying to test/understand it) is to;

1. Insert a new query (n_view_query_templates) based on the existing query, 
2. Copies across all the tables from the existing query into the new query (including the CST_Period_Close_Summary table), 
3. Add in the MTL tables (MTL_material_Statuses_tl, MTL_Grades_TL, MTL_Item_Locations, and MTL_Lot_Numbers
4. Update the CPCS table (CST_Period_Close_Summary) replacing it with GMF.GMF_PERIOD_BALANCES in the new query,
5. Copy across all the columns from the existing query that are associated with any table included in the new query,
6. Update the Accounted_On_Hand and Period_Close_Quantity columns to use the correct values,
7. Insert new columns based on the new tables (i.e. Lot_Grade_Code, Lot_Number, etc),
8. Insert the requried n_view_col_property_templates records for flexfields,
9. Copy across the existing WHERE-clause records,
10. Insert new where clause components to join up new tables, and finally
11. Update the version for the first query to be pre-Release 12