Lot Status History View

On Andy’s blog,  http://snippetsandhelp.blogspot.com, he documented his method of creating a material status history view.  I too have had a similar requirement and I think it could be of help to some people if I post the DDL here:

Create a base view:

 CREATE OR REPLACE FORCE VIEW "NOETIX_SYS"."INVG_LOT_STATUS_HIST_BASE" ("A$INVENTORY_ITEM_ID", "A$ZZ__________________________", "CREATION_DATE", "CURRENT_LOT_STATUS_ID", "INVENTORY_ITEM_ID", "LOT_NUMBER", "ORGANIZATION_ID", "PRIMARY_ONHAND", "PRIOR_LOT_STATUS_ID", "REASON_DESCRIPTION", "REASON_NAME", "REASON_UPDATED_BY", "SECONDARY_ONHAND")
AS
  SELECT HIST.INVENTORY_ITEM_ID A$Inventory_Item_ID,
    'A$ZZ__________________________Copyright Noetix Corporation 1992-2010' A$ZZ__________________________,
    HIST.CREATION_DATE Creation_Date,
    HIST.STATUS_ID Current_Lot_Status_ID,
    HIST.INVENTORY_ITEM_ID Inventory_Item_ID,
    HIST.LOT_NUMBER Lot_Number,
    HIST.ORGANIZATION_ID Organization_ID,
    HIST.PRIMARY_ONHAND Primary_Onhand,
    LAG(HIST.STATUS_ID, 1, 0) OVER (PARTITION BY HIST.LOT_NUMBER ORDER BY HIST.STATUS_UPDATE_ID ASC NULLS FIRST) Prior_Lot_Status_ID,
    REASN.DESCRIPTION Reason_Description,
    REASN.REASON_NAME Reason_Name,
    FUSER.USER_NAME Reason_Updated_By,
    HIST.SECONDARY_ONHAND Secondary_Onhand
  FROM APPLSYS.FND_USER FUSER,
    INV.MTL_TRANSACTION_REASONS REASN,
    INV.MTL_MATERIAL_STATUS_HISTORY HIST
  WHERE 'Copyright Noetix Corporation 1992-2010' IS NOT NULL
  AND REASN.REASON_ID(+)                          = HIST.UPDATE_REASON_ID
  AND HIST.LAST_UPDATED_BY                        = FUSER.USER_ID ;

Create the material status view:

 CREATE OR REPLACE FORCE VIEW "NOETIX_SYS"."INVG_LOT_STATUS_HISTORY" ("A$ITEM$ITEM", "A$ITEM_TYPE", "A$LOTS$BILLET_HEAT", "A$LOT_NUMBER", "A$ORGANIZATION_NAME", "A$PARENT_LOT_NUMBER", "A$ZZ__________________________", "CHANGE_REASON_CODE", "CHANGE_REASON_DESCRIPTION", "CHANGE_REASON_UPDATED_BY", "CURRENT_LOT_STATUS", "CURRENT_LOT_STATUS_CODE", "GRADE_CODE", "ITEM$ITEM", "ITEM_DESCRIPTION", "ITEM_TYPE", "LOTS$ACTUAL_BILLET_WEIGHT__LBS", "LOTS$BILLET_CROSS_SECTION", "LOTS$BILLET_HEAT", "LOTS$BILLET_LENGTH__IN", "LOTS$CONVERSION_INDICATOR", "LOTS$CUSTOMER_LOT_NUMBER", "LOTS$CUSTOMER_NAME", "LOTS$DESIRED_CYCLE", "LOTS$DEVIATION_NUMBER", "LOTS$DISPOSITION_CODE", "LOTS$INPUT_CHILD_LOT", "LOTS$INPUT_GC_GRANDPARENT", "LOTS$INPUT_PC_GRANDPARENT", "LOTS$INPUT_PARENT_LOT_NUMBER", "LOTS$PBL_SUPPLIER_PARENT_LOT", "LOTS$PREVIOUS_FURNACE_LOAD", "LOT_AGE", "LOT_BEST_BY_DATE", "LOT_CREATION_DATE", "LOT_DESCRIPTION", "LOT_DISABLED_FLAG", "LOT_EXPIRATION_ACTION_CODE", "LOT_EXPIRATION_ACTION_DATE",
  "LOT_EXPIRATION_DATE", "LOT_HOLD_DATE", "LOT_ITEM_SIZE", "LOT_LENGTH", "LOT_LENGTH_UOM", "LOT_MATURITY_DATE", "LOT_NUMBER", "LOT_ORIGIN", "LOT_ORIGINATION_DATE", "LOT_ORIGINATION_TYPE", "LOT_RECYCLED_CONTENT", "LOT_RETEST_DATE", "LOT_STATUS_CREATION_DATE", "LOT_THICKNESS", "LOT_THICKNESS_UOM", "LOT_VOLUME", "LOT_VOLUME_UOM", "LOT_WIDTH", "LOT_WIDTH_UOM", "ORGANIZATION", "ORGANIZATION_NAME", "PARENT_LOT_NUMBER", "PRIMARY_ONHAND", "PRIOR_LOT_STATUS", "PRIOR_LOT_STATUS_CODE", "SECONDARY_ONHAND", "SUPPLIER_LOT_NUMBER", "SUPPLIER_NAME", "TERRITORY_CODE")
AS
  SELECT ITEM.SEGMENT1 A$ITEM$Item,
    ITEM.ITEM_TYPE A$Item_Type,
    LOTS.C_ATTRIBUTE3 A$LOTS$Billet_Heat,
    LOTS.LOT_NUMBER A$Lot_Number,
    XMAP.ORGANIZATION_NAME A$Organization_Name,
    LOTS.PARENT_LOT_NUMBER A$Parent_Lot_Number,
    'A$ZZ__________________________Copyright Noetix Corporation 1992-2010' A$ZZ__________________________,
    HIST.REASON_NAME CHANGE_REASON_CODE,
    HIST.REASON_DESCRIPTION CHANGE_REASON_DESCRIPTION,
    HIST.REASON_UPDATED_BY CHANGE_REASON_UPDATED_BY,
    MATSC.DESCRIPTION Current_Lot_Status,
    MATSC.STATUS_CODE Current_Lot_Status_Code,
    LOTS.GRADE_CODE Grade_Code,
    ITEM.SEGMENT1 ITEM$Item,
    ITEML.DESCRIPTION Item_Description,
    ITEM.ITEM_TYPE Item_Type,
    LOTS.C_ATTRIBUTE7 LOTS$Actual_Billet_Weight__LBS,
    LOTS.C_ATTRIBUTE4 LOTS$Billet_Cross_Section,
    LOTS.C_ATTRIBUTE3 LOTS$Billet_Heat,
    LOTS.C_ATTRIBUTE1 LOTS$Billet_Length__IN,
    LOTS.C_ATTRIBUTE18 LOTS$Conversion_Indicator,
    LOTS.C_ATTRIBUTE9 LOTS$Customer_Lot_Number,
    LOTS.C_ATTRIBUTE11 LOTS$Customer_Name,
    LOTS.C_ATTRIBUTE13 LOTS$Desired_Cycle,
    LOTS.C_ATTRIBUTE10 LOTS$Deviation_Number,
    LOTS.C_ATTRIBUTE5 LOTS$Disposition_Code,
    LOTS.C_ATTRIBUTE2 LOTS$Input_Child_Lot,
    LOTS.C_ATTRIBUTE20 LOTS$Input_GC_Grandparent,
    LOTS.C_ATTRIBUTE19 LOTS$Input_PC_Grandparent,
    LOTS.C_ATTRIBUTE8 LOTS$Input_Parent_Lot_Number,
    LOTS.C_ATTRIBUTE6 LOTS$PBL_Supplier_Parent_Lot,
    LOTS.C_ATTRIBUTE12 LOTS$Previous_Furnace_Load,
    LOTS.AGE Lot_Age,
    LOTS.BEST_BY_DATE Lot_Best_By_Date,
    LOTS.CREATION_DATE Lot_Creation_Date,
    LOTS.DESCRIPTION Lot_Description,
    (
    CASE
      WHEN LOTS.DISABLE_FLAG = 1
      THEN 'Y'
      ELSE 'N'
    END) Lot_Disabled_Flag,
    LOTS.EXPIRATION_ACTION_CODE Lot_Expiration_Action_Code,
    LOTS.EXPIRATION_ACTION_DATE Lot_Expiration_Action_Date,
    LOTS.EXPIRATION_DATE Lot_Expiration_Date,
    LOTS.HOLD_DATE Lot_Hold_Date,
    LOTS.ITEM_SIZE Lot_Item_Size,
    LOTS.LENGTH Lot_Length,
    DECODE(LOTS.LENGTH_UOM,'BAT','BAT','CF','CF','DAY','DAY','DME','DME','DRM', 'DRM','EA','EA','FT','FT','GAL','GAL','GT','GT','HR','Hour','HRC','HRC','HRS', 'Hours','KG','KG','LB','LB','M','M','MIN','Minute','MT','MT','NKL','NKL','PCS' ,'PCS','PEN','PEN','PSI' ,'PSI','QUA','QUA','TON','TON','USD','USD',LOTS.LENGTH_UOM) Lot_Length_UOM,
    LOTS.MATURITY_DATE Lot_Maturity_Date,
    LOTS.LOT_NUMBER Lot_Number,
    LOTS.PLACE_OF_ORIGIN Lot_Origin,
    LOTS.ORIGINATION_DATE Lot_Origination_Date,
    DECODE(LOTS.ORIGINATION_TYPE,'0','Lot Master','1','Production','2', 'Quantities','3','Receiving','4','Inventory','5','Returns','6','Other','7', 'Lot Split','8' ,'Lot Merge','9','Lot Translate',LOTS.ORIGINATION_TYPE) Lot_Origination_Type,
    LOTS.RECYCLED_CONTENT Lot_Recycled_Content,
    LOTS.RETEST_DATE Lot_Retest_Date,
    HIST.CREATION_DATE Lot_Status_Creation_Date,
    LOTS.THICKNESS Lot_Thickness,
    DECODE(LOTS.THICKNESS_UOM,'BAT','BAT','CF','CF','DAY','DAY','DME','DME', 'DRM','DRM','EA','EA','FT','FT','GAL','GAL','GT','GT','HR','Hour','HRC','HRC', 'HRS','Hours','KG','KG','LB','LB','M','M','MIN','Minute','MT','MT','NKL','NKL' ,'PCS','PCS','PEN','PEN','PSI','PSI' ,'QUA','QUA','TON','TON','USD','USD',LOTS.THICKNESS_UOM) Lot_Thickness_UOM,
    LOTS.VOLUME Lot_Volume,
    DECODE(LOTS.VOLUME_UOM,'BAT','BAT','CF','CF','DAY','DAY','DME','DME','DRM', 'DRM','EA','EA','FT','FT','GAL','GAL','GT','GT','HR','Hour','HRC','HRC','HRS', 'Hours','KG','KG','LB','LB','M','M','MIN','Minute','MT','MT','NKL','NKL','PCS' ,'PCS','PEN','PEN','PSI' ,'PSI','QUA','QUA','TON','TON','USD','USD',LOTS.VOLUME_UOM) Lot_Volume_UOM,
    LOTS.WIDTH Lot_Width,
    DECODE(LOTS.WIDTH_UOM,'BAT','BAT','CF','CF','DAY','DAY','DME','DME','DRM', 'DRM','EA','EA','FT','FT','GAL','GAL','GT','GT','HR','Hour','HRC','HRC','HRS', 'Hours','KG','KG','LB','LB','M','M','MIN','Minute','MT','MT','NKL','NKL','PCS' ,'PCS','PEN','PEN', 'PSI','PSI','QUA','QUA','TON','TON','USD','USD',LOTS.WIDTH_UOM) Lot_Width_UOM,
    MPARM.ORGANIZATION_CODE Organization,
    XMAP.ORGANIZATION_NAME Organization_Name,
    LOTS.PARENT_LOT_NUMBER Parent_Lot_Number,
    HIST.PRIMARY_ONHAND Primary_Onhand,
    MATSP.DESCRIPTION Prior_Lot_Status,
    MATSP.STATUS_CODE Prior_Lot_Status_Code,
    HIST.SECONDARY_ONHAND Secondary_Onhand,
    LOTS.SUPPLIER_LOT_NUMBER Supplier_Lot_Number,
    LOTS.VENDOR_NAME Supplier_Name,
    LOTS.TERRITORY_CODE Territory_Code
  FROM INV.MTL_PARAMETERS MPARM,
    NOETIX_SYS.INVG_Lot_Status_Hist_Base HIST,
    INV.MTL_SYSTEM_ITEMS_TL ITEML,
    INV.MTL_MATERIAL_STATUSES_TL MATSP,
    INV.MTL_MATERIAL_STATUSES_TL MATSC,
    INV.MTL_SYSTEM_ITEMS_B ITEM,
    NOETIX_SYS.INVG_INV_ACL_Map_Base XMAP,
    INV.MTL_LOT_NUMBERS LOTS
  WHERE 'Copyright Noetix Corporation 1992-2010' IS NOT NULL
  AND XMAP.ORGANIZATION_ID                        = LOTS.ORGANIZATION_ID
  AND XMAP.APPLICATION_LABEL                      = 'INV'
  AND XMAP.APPLICATION_INSTANCE                   = 'G0'
  AND ITEM.INVENTORY_ITEM_ID                      = LOTS.INVENTORY_ITEM_ID
  AND ITEM.ORGANIZATION_ID                        = LOTS.ORGANIZATION_ID
  AND HIST.LOT_NUMBER                             = LOTS.LOT_NUMBER
  AND HIST.ORGANIZATION_ID                        = LOTS.ORGANIZATION_ID
  AND HIST.INVENTORY_ITEM_ID                      = LOTS.INVENTORY_ITEM_ID
  AND MATSC.STATUS_ID(+)                          = HIST.CURRENT_LOT_STATUS_ID
  AND MATSP.STATUS_ID(+)                          = HIST.PRIOR_LOT_STATUS_ID
  AND MATSC.LANGUAGE(+) LIKE NOETIX_ENV_PKG.GET_LANGUAGE
  AND MATSP.LANGUAGE(+) LIKE NOETIX_ENV_PKG.GET_LANGUAGE
  AND ITEML.INVENTORY_ITEM_ID(+) = ITEM.INVENTORY_ITEM_ID
  AND ITEML.ORGANIZATION_ID(+)   = ITEM.ORGANIZATION_ID
  AND ITEML.LANGUAGE(+) LIKE NOETIX_ENV_PKG.GET_LANGUAGE
  AND ITEM.ORGANIZATION_ID = MPARM.ORGANIZATION_ID ;

Patrick

Capturing Errors in XU2 Scripts Early in Stage 4 of a View Regeneration Using MS PowerShell

I posted on this previously and documented a MS DOS command to find errors in my XU2 scripts (here).The xu2 scripts are invoked during stage 4 of the Noetix View Administrator's regeneration process.  This is right after SQL Loader is finished loading seeded template table records. The errors that are thrown during this part of the regeneration are typically associated with constraint errors.  If you examine the constraints that are associated with a given Noetix template table, one can see that they are quite robustly written to help make sure that you do not add a record in an erroneous way.

One can typically examine the root cause of these errors if one looks at this query:

SELECT *
FROM DBA_CONS_COLUMNS
WHERE 1        =1
AND OWNER      = 'NOETIX_SYS'
AND TABLE_NAME = 'N_VIEW_COLUMN_TEMPLATES';

Today I wanted to document another way to find these errors.  More than a year ago, I noticed that Microsoft had an updated shell, called PowerShell, and so I placed it on my laptop.  Today, I will go through how one can invoke a command (well, actually a cmdlet) to find Oracle thrown errors that occur while the Noetix View Administrator is in stage 4 of a regeneration.


First, a little background information on PowerShell.

It is rather nice because it is object oriented and its piping is similar to Unix/Linux.  Specifically, the piping can return objects and then the next cmdlet (that is what they call their commands in this new shell) manipulates it, one object at a time through the pipe kind of like an embedded function.  This is very dissimilar to MS DOS where it completes the first command and then pushes the result set to the next command.

I noticed that it was quite different from MS Dos and I looked at it for a little bit and talked to some peers.  My peers heard of it, but thought that it really had gained little traction in terms of use by administrators and other IT staff.

For one reason or another, I stumbled on the blog, http://blogs.technet.com/b/heyscriptingguy/, and it really made me reconsider using this shell environment in lieu of MS DOS where practical.

The two main commands one needs to know in this shell are as follows:

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Get-Alias  MS_DOS_Command

Notes on this:  This cmdlet just helps you translate your MS DOS command to the PowerShell cmdlet.

Here is an example:

PS C:\WINDOWS\system32\windowspowershell\v1.0> Get-Alias cd

This returns:

CommandType     Name                                                                                           Definition                                                                                    

-----------     ----                                                                                           ----------                                                                                    

Alias           cd                                                                                             Set-Location         

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The other command that is necessary is the following:

Get-Help cmdlet

Here is an example

PS C:\WINDOWS\system32\windowspowershell\v1.0> Get-Help Get-ChildItem

NAME

    Get-ChildItem

   

SYNOPSIS

    Gets the items and child items in one or more specified locations.

   

   

SYNTAX

    Get-ChildItem [[-Path] ] [[-Filter] ] [-Exclude ] [-Force] [-Include ] [-Name] [-Recurse] [-UseTransaction] []

   

    Get-ChildItem [-LiteralPath] [[-Filter] ] [-Exclude ] [-Force] [-Include ] [-Name] [-Recurse] [-UseTransaction] []

   

   

DESCRIPTION

    The Get-ChildItem cmdlet gets the items in one or more specified locations. If the item is a container, it gets the items inside the container, known as child items. You can use the Recurse parameter  to get items in all child containers.

   

    A location can be a file system location, such as a directory, or a location exposed by another provider, such as a registry hive or a certificate store.

   

RELATED LINKS

    Online version: http://go.microsoft.com/fwlink/?LinkID=113308

    about_Providers

    Get-Item

    Get-Alias

    Get-Location

    Get-Process

REMARKS

    To see the examples, type: "get-help Get-ChildItem -examples".

    For more information, type: "get-help Get-ChildItem -detailed".

    For technical information, type: "get-help Get-ChildItem -full".

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Anyways, I use this shell as my main shell that I use for various tasks.  Here is my cmdlet to watch for errors being thrown right before GUI prompt for the APPS password (which is right after the wnoetxu2.sql script has run):

PS C:\WINDOWS\system32\windowspowershell\v1.0> Get-ChildItem -Path "\\cmntx02\NoetixViews 6.0.1 - ERP\Installs\NOETIX_SYS_erpdev" -Filter "*xu2.lst" | Select-String -pattern "ORA-"

I invoke this script on my laptop and it peruses the files in the path I have identified.  I suppose you might take a look at this script and think; I will never type a path that long!  Well, if you are not aware, you can drag a file from MS explorer to your shell environment and Windows will “type it in” for you.

                                                                       

Approach to Placing xu2 Script Invocations in a wnoetxu2.sql File

In previous entries, I discussed how the template tables are the tables where the metadata associated with the view "classes" are kept.  These classes are instantiated using the Noetix View Administrator  to correspond with the Noetix Views purchased as well as your implementation of Oracle Application (e.g.  the set-ups associated with your company’s ledger(s)/legal entity (ies)/operating unit(s)/organization(s)).

Most of your hook scripts will be associated with modifications to these tables using the wnoetxu2.sql script.  After more than a trivial amount of invocations are created, some logic and structure is advised.

My approach is to sequentially enter scripts dependent on what modification(s) are being made:

1.       Section for packages, functions and procedures:  The motivations for having this section first is that it is more likely to have other sections below depend on it.  

2.       Section for custom and customizing base view:  Base views are Noetix's answer to the inline view/derived table.  It is useful because it is easier to evaluate its performance than an inline view/derived table.  Again, many of the sections following this might need to depend on this section, consequently I see it as important to lump these base view modifications together.

3.       Section for LOV Views:  While I have created custom views which are specifically for generating LOV (very much like Noetix does), I have not really used this section much.  It is driven by customer requirement (and it has not been required).

4.       Section for Clone Views: In general, I like to clone directly off of the "out of the box" Noetix Views as opposed to a Noetix View which has been modified.  It is important to be cognizant of the sequence of invocation regarding the parent and child views associated with a cloning.  This sequence usually can be the difference between an error being thrown or not.

5.       Section for Custom Views Not Cloned:  I just find it valuable to keep these view separate because whenever a migration needs to occur to another version of Noetix Views or Oracle Applications, this section needs special attention.

6.       Standard Modification of Views:  These are the modifications that one can get certified to change. All the other view changes above (except the base view modifications) really are not encouraged to be modified. That is Noetix really does not encourage clones or custom views nor is it taught in their Noetix View Customization Certification.

Here is an example of a wnoetxu2.sql script following this organizational method:

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- packages/functions/procedures

-- Date 21 APR 10 - F. Flintstone

@N_CS_OE_UTL_PKG_HDR_xu2.pkh;

@N_CS_OE_UTL_PKG_HDR_xu2.pkb;

-- Date 28 SEP 10 - F. Flintstone

-- Note:  these scripts create functions that are prerequisites for OE_Lines_Basic.

--

@N_OE_GET_TRIPS.sql;

@N_OE_GET_DELIVERIES.sql;

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--custom and customizing base views

-- jan 2010 F. Flintstone

@OE_Lot_Serial_Base.sql;

-- Date 13 Jan 2011 - F. Flintstone added

@OE_Delivery_Base_xu2.sql;

-- Date 15 Jun 10 - F. Flintstone

@INV_Lot_Status_Hist_Base_xu2.sql;

-- Date 12 Apr 2011 - F. Flintstone

@PO_Item_Acct_Cost_Base_xu2.sql;

-- Date 12 Apr 2011 - F. Flintstone

@GMF_Item_Acct_Costs_Base_xu2.sql;

-- Date 17 May 2011 F. Flintstone

@OE_Coil_Carrier_Base_xu2.sql;

@OE_Header_Short_Text_Base_xu2.sql;

-- Date 17 Jan 12 F. Flintstone Added

@AR_Customers_Base_xu2.sql;

@OE_Contacts_Base_xu2.sql;

-- Date 02 Feb 12 F. Flintstone Added

@GMD_Category_Processing_Base_xu2.sql;

@GMD_Category_Costing_Base_xu2.sql;

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

--LOV Views

-- feb 2010

@AR_Lov_Cust_Bill_Ship.sql;

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--Clone Views

--Mostly clones with some source views included

--because clone script depends on initial changes to source view

-- RJ Adding additional customizations

-- Date 23 OCT 09

@OE_Lines_xu2.sql;

--was moved to cloned section because clones

--depend on changes made here prior to

--creation of the clones

-- Date 21 OCT 10 - F. Flintstone

@GMD_Formulas_xu2.sql;

--was moved to cloned section because clone

--depends on changes made here prior to

--creation of the clone

-- Date 14 MAY 10 - F. Flintstone

@GME_Batch_Headers_xu2.sql

--was moved to cloned section because clones

--depend on changes made here prior to

--creation of the clones

-- Date 24 AUG 10 - F. Flintstone

@INV_Item_Onhand_By_Lot_Loc_xu2.sql;

-- Date 24 SEP 10 - F. Flintstone

@OE_Lines_Basic_xu2.sql;

-- Date 22 OCT 10 - F. Flintstone

@GME_Batch_Headers_Summary_xu2.sql;

-- Date 01 NOV 10 - F. Flintstone

@GMD_Product_Formulas_xu2.sql;

-- Date 13 Jan 2011 - F. Flintstone

@OE_Lines_Shipping_Basic_xu2.sql;

--17 jan 2012 F. Flintstone

@INV_CS_Lot_Onhand_By_Batch_xu2.sql;

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--Custom Views Not Cloned

-- Date 15 Jun 10 - F. Flintstone

@INV_Lot_Status_History_xu2.sql;

@INV_Alias_Lot_Trnsctns_xu2.sql;

@INV_Batch_Transactions_xu2.sql;

-- Date 08 SEP 10 - F. Flintstone

@AR_CS_Commission_Rates_xu2.sql;

-- Date 10 SEP 10 - F. Flintstone

@AR_CS_Collection_Notes_xu2.sql;

-- Date 16 SEP 10 - F. Flintstone

@GMF_CS_Item_Cost_xu2.sql;

-- Date 20 SEP 10 - F. Flintstone

@INV_Onhand_Period_End_xu2.sql;

-- Date 01 OCT 10 - F. Flintstone

@OE_CS_Order_Book_Changes_xu2.sql;

-- Date 14 OCT 10 - F. Flintstone

@GMD_CS_Customer_Spec_Reviews_xu2.sql;

-- Date 19 OCT 10 - F. Flintstone

@GMD_CS_Cust_Item_Spec_Reviews_xu2.sql;

-- Date 12 Apr 2011 - F. Flintstone

@GMF_Processing_WIP_Batches_xu2.sql;

@PO_Inter_Org_Shipments_xu2.sql;

--@GMD_Billet_Formula_Base_xu2.sql;

--@GMD_CS_Indented_Formulas_xu2.sql;

-- 17 May 2011 F. Flintstone

@OE_CS_Transportation_xu2.sql;

@OE_CS_List_Lines_All_xu2.sql;

-- 18 Aug 2011 F. Flintstone

@AR_CS_Cust_Freight_Details_xu2.sql;

--18 Oct 2011 F. Flintstone

@GME_CS_MES_Melt_Batch_Xfer_xu2.sql;

@GME_CS_MES_Proc_Batch_Xfer_xu2.sql;

@GME_CS_MES_Roll_Batch_Xfer_xu2.sql;

-- Date 12 Mar 12 - F. Flintstone

@OE_CS_Tads_Loads_Details_xu2.sql;

--17 jan 2012 F. Flintstone

@EAM_CS_Asset_Log_xu2.sql;

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

--Standard Modification of Views

-- Date 23 OCT 09

@INV_Items_xu2.sql;

--@OE_Lines_xu2.sql;

--was moved to cloned section because clones

--depend on changes made here prior to

--creation of the clones

@OE_Order_Territories_xu2.sql;

@OE_Backlog_xu2.sql;

-- Dec 2009

@EAM_Work_Orders_xu2.sql;

@PO_Blanket_PO_Lines_xu2.sql;

@OE_Pick_Slips.sql;

@OE_Backordered_Picking_Lines.sql;

@OE_Returns.sql;

@OE_Book_Bill_Backlog.sql;

@OE_Deliveries_xu2.sql;

@PO_PO_Lines.sql;

@RA_Customers_xu2.sql;

--F. Flintstonejan 2010

@OE_Picking_Line_Details_xu2.sql;

--  Date 07 APR 10 - F. Flintstone

@EAM_Asset_Downtime_xu2.sql;

@EAM_Work_Requests_xu2.sql;

@OE_Delivery_Departure_Details_xu2;

@OE_Departures_xu2;

--  Date 08 APR 10 - F. Flintstone

@CS_Service_Requests_xu2.sql;

@INV_Lot_Transactions_xu2.sql;

-- Date 20 APR 10 - F. Flintstone

@EAM_Asset_Activity_Assoc_xu2.sql;

-- Date 28 APR 10 - F. Flintstone

@EAM_Work_Order_Resources_xu2.sql;

-- Date 29 APR 10 - F. Flintstone

@AP_Invoice_Pay_Schedules_xu2.sql;

-- Date 30 APR 10 - F. Flintstone

@OE_Order_Invoices_xu2.sql;

@OE_Returns_xu2.sql;

-- Date 14 MAY 10 - F. Flintstone

--@GME_Batch_Headers_xu2.sql

--was moved to cloned section because clones

--depend on changes made here prior to

--creation of the clones

-- Date 15 Jun 10 - F. Flintstone

@INV_Customer_Items_xu2.sql

-- Date 23 AUG 10 - F. Flintstone

@PO_Receipts_xu2.sql;

-- Date 26 AUG 10 - F. Flintstone

@AR_Disputes_xu2.sql;

-- Date 30 AUG 10 - F. Flintstone

@AR_Transaction_Line_Dtls_xu2.sql;

@AR_Tran_Dist_SLA_GL_Je_xu2.sql;

-- Date 10 SEP 10 - F. Flintstone

@AR_Cash_Receipts_xu2.sql;

-- Date 13 SEP 10 - F. Flintstone

@AP_Checks_xu2.sql;

-- Date 14 SEP 10 - F. Flintstone

@AR_Customer_Balance_Aging_xu2.sql;

-- Date 16 SEP 10 - F. Flintstone

@INV_Item_Onhand_By_Lot_xu2.sql;

-- Date 30 SEP 10 - F. Flintstone

@OE_Delivery_Assignments_xu2.sql;

-- Date 04 OCT 10 - F. Flintstone

@OE_List_Lines_xu2.sql;

-- Date 06 OCT 10 - F. Flintstone

@AP_Inv_Dist_SLA_GL_Je_xu2.sql;

-- Date 07 OCT 10 - F. Flintstone

@CS_Charge_Details_xu2.sql;

-- Date 08 OCT 10 - F. Flintstone

@GMD_QC_Specifications_xu2.sql;

-- Date 12 OCT 10 - F. Flintstone

@GMD_QC_Results_xu2.sql;

@GMD_QC_Tests_xu2.sql;

@INV_Item_Catalogs_xu2.sql;

-- Date 19 OCT 10 - F. Flintstone

@GMD_QC_Cust_Deviation_Samples_xu2.sql;

-- Date 21 OCT 10 - F. Flintstone

@INV_Category_xu2.sql;

--@GMD_Formulas_xu2.sql;

--was moved to cloned section because clone

--depends on changes made here prior to

--creation of the clone

@GMD_Recipes_xu2.sql;

@GMD_Routings_xu2.sql;

-- Date 25 OCT 10 - F. Flintstone

@INV_Lot_Details_xu2.sql;

-- Date 07 Jan 2011 - Praveen/F. Flintstone

@OE_Orders_xu2.sql;

-- 27 Jun 2011 F. Flintstone

@GME_Batch_Step_Rsrc_Actvty_xu2.sql;

@GMF_Resource_Costs_xu2.sql;

-- 22 Jul 2011 F. Flintstone

@B24485.sql;

-- END Additions

- 25 Aug 2011 F. Flintstone

@CSG_Charge_Details_xu2.sql;

--01 nov 2011 F. Flintstone

@PO_Vendors_xu2.sql;

@GL_Journal_Entries_xu2.sql;

-- end wnoetxu2.sql

Developing a Workaround for Date Columns Which are Indexed

With the Noetix EUL Generator for Discoverer, all columns which are indexed are associated with a class.  Classes are used to generate LOVs.  While this approach is generally helpful, end users find that parameters created on these indexed date columns "muck up" the select by calendar feature.  Here is an example of this:

 

Notice that only the current date is available.

Proposed solution:

1. Contact Noetix and request an enhancement.  I probably should do this at some point.
2. Manually correct this after a regeneration using the Discoverer Administrator.    

select

count(1)

from

eul5_expressions

where 1=1
and exp_type = 'CO'

and exp_data_type =4

and it_dom_id is not null;

I calculate 2,255 scenarios where this problem occurs.  I would rather not take this approach.

3. Create a DML script which updates these columns that fit this scenario.

Pros:  Efficient, if the table(s) are modified correctly.

Cons:  Possibly corrupt the EUL. 

Planning for approach #3:

I modified a date column which is indexed (back and forth) using the Discoverer Administrator and the two changes I have identified are as follows:

Nullify the EUL5_EXPRESSIONS.IT_DOM_ID column and increment the NOTM column (number of times modified).

I check the EUL5_EXPRESSIONS table for constraints associated with these columns using this query:

SELECT cols.table_name,

  cols.column_name,

  cols.position,

  CONS.constraint_type

FROM all_constraints cons,

  all_cons_columns cols

WHERE cols.table_name    = 'EUL5_EXPRESSIONS'

AND cons.constraint_name = 'EUL5_IT_DOM_FK'

AND cons.owner           = 'EUL5_US'

AND cons.constraint_type = 'R'

AND cons.constraint_name = cols.constraint_name

AND CONS.OWNER           = COLS.OWNER

ORDER BY cols.table_name,

  cols.position;

I do notice the foreign key constraint, EUL5_IT_DOM_FK, associated with the EUL5_EXPRESSIONS table.  Consequently, if the IT_COM_ID  is not null, then there needs to be referential integrity with the EUL5_DOMAINS table (the DOM_ID column to be exact).

Based on this analysis, I execute and commit this DML script:

update eul5_expressions

set it_dom_id = null,

notm = notm + 1

where 1=1
and exp_type = 'CO'

and exp_data_type =4

and it_dom_id is not null;

I presently am testing this in a development environment and I would like to confer with other Discoverer users (e.g. using the Discoverer Forum).

Summary

Updating the EUL using scripts is usually to be avoided.  In fact, it should be a last resort.  I am going to contact Noetix on this, but I suspect I will be left having to implement approach 3 (after more research and testing).

  

Does an XU2/XU5 Script Produce an ORA-00947 Error?

Have you ever received the ORA-00947 from one of your XU2/XU5 scripts?   This occurs in SQL statements requiring two equal sets of values (very common to occur in an insert statement).   Here is an example from a part of a XU2 script:

SQL> INSERT INTO n_view_column_templates

  2  (view_label

  3  , query_position

  4  , column_label

  5  , table_alias

  6  , column_expression

  7  , column_position

  8  , column_type

  9  , description

 10  , group_by_flag

 11  , gen_search_by_col_flag

 12  , profile_option

 13  , product_version

 14  , created_by

 15  , creation_date

 16  , last_updated_by

 17  , last_update_date

 18  )

 19  VALUES

 20  ('OE_ACME_Blanket_Order_Pricing'    --view_label

 21  ,1     --query_position  number

 22  ,'Line_Revision_Date' --column_label

 23  ,'QPLNS'     --table_alias

 24  ,'LINE_REVISION_DATE'     --COLUMN_EXPRESSION

 25  ,76    --column_position number

 26  ,'COL'    --column_type

 27  ,'The line revision date ' --description

 28  , NULL  --profile_option

 29  ,'*'     --product_version

 30  ,’flintstonef’     -- created_by

 31  , SYSDATE                --creation_date

 32  ,’flintstonef’     --last_updated_by

 33  , SYSDATE                --last_update_date

 34  );

VALUES

*

ERROR at line 19:

ORA-00947: not enough values

With a cursory look, you might not catch the discrepancy, but by juxtaposing the insert columns with the value columns, we see that the insert set has 16 columns whereas the value set has 14 columns:

SQL> INSERT INTO n_view_column_templates	19  VALUES
2 (view_label	20  ('OE_ACME_Blanket_Order_Pricing'    --view_label
3  , query_position	21  ,1     --query_position  number
4  , column_label	22  ,'Line_Revision_Date' --column_label
5  , table_alias	23  ,'QPLNS'     --table_alias
6  , column_expression	24  ,'LINE_REVISION_DATE'     --COLUMN_EXPRESSION
7  , column_position	25  ,76    --column_position number
8  , column_type	26  ,'COL'    --column_type
9  , description	27  ,'The line revision date ' --description
10  , group_by_flag	28  , NULL  --profile_option
11  , gen_search_by_col_flag	29  ,'*'     --product_version
12  , profile_option	30  ,’flintstonef’     -- created_by
13  , product_version	31  , SYSDATE                --creation_date
14  , created_by	32  ,’flintstonef’     --last_updated_by
15  , creation_date	33  , SYSDATE                --last_update_date
16  , last_updated_by	34  );
17  , last_update_date
18  )

This juxtaposition allows us to see that the group_by_flag and the gen_search_by_col_flag columns have been omitted from the values set.

In this context the solution is easy, we just add these columns to the value set as follows:

 INSERT INTO n_view_column_templates

  2  (view_label

  3  , query_position

  4  , column_label

  5  , table_alias

  6  , column_expression

  7  , column_position

  8  , column_type

  9  , description

 10  , group_by_flag

 11  , gen_search_by_col_flag

 12  , profile_option

 13  , product_version

 14  , created_by

 15  , creation_date

 16  , last_updated_by

 17  , last_update_date

 18  )

 19  VALUES

 20  ('OE_ACME_Blanket_Order_Pricing'    --view_label

 21  ,1     --query_position  number

 22  ,'Line_Revision_Date' --column_label

 23  ,'QPLNS'     --table_alias

 24  ,'LINE_REVISION_DATE'     --COLUMN_EXPRESSION

 25  ,76    --column_position number

 26  ,'COL'    --column_type

 27  ,'The line revision date ' –description

28    ,'N'      -- group_by_flag

29    ,'N'      -- gen_search_by_col_flag

 30  , NULL  --profile_option

 31  ,'*'     --product_version

 32  ,’flintstonef’     -- created_by

 33  , SYSDATE                --creation_date

 34  ,’flintstonef’     --last_updated_by

 35  , SYSDATE                --last_update_date

 36  );

While this is not a common error, this has occurred in my development process.  I think the root cause is accidently deleting rows.

Does Your XU2/XU5 Script Produce an ORA-02291 Integrity Constraint Error?

A common error I have encountered when I regenerate my Noetix Views is the ORA-02291 integrity constraint error.  To provide a little more context regarding this entry, the example error I am looking at today is being thrown during a call to the hook script,   'OE_List_Lines_Base_xu2.sql’.

This error occurs during stage 4 of a regeneration and specifically, just after the template tables are loaded (this is when the xu2 scripts are invoked).  As a matter of observation, one can peruse the corresponding ‘.lst’ files generated by your hook scripts like this one, 'OE_List_Lines_Base_xu2.sql’, just before the second prompt for the APPS password (of course, this is using the GUI version of the NVA).  

When I am doing new development, I always perform a search in my ‘.lst’ files generated by my new hook scripts for the characters, ‘ORA-‘, to see if an error is being thrown.   One can just run your new hook scripts without running the NVA and check to see if an error will be thrown (but I would recommend removing the commits and I would recommend a rollback after the script is invoked).  Of course since this is development, we want to do this in a development environment.  Nothing is worse than testing or developing in a production environment.

Okay, so I developed my little hook script and just after I know that SQL Loader has loaded all the template tables with our configuration of the Noetix Views, I start looking at my ‘.lst’ hook scripts being generated and I spot this error in the ‘.lst’ file, 'OE_List_Lines_Base_xu2.lst':

Example:

ERROR at line 1:

ORA-02291: integrity constraint (NOETIX_SYS.N_VIEW_COLUMN_TEMPLATES_FK5)

Violated - parent key not found

Source offending script:

INSERT INTO n_view_column_templates

  2  (view_label

  3  , query_position

  4  , column_label

  5  , table_alias

  6  , column_expression

  7  , column_position

  8  , column_type

  9  , description

 10  , group_by_flag

 11  , gen_search_by_col_flag

 12  , profile_option

 13  , product_version

 14  , created_by

 15  , creation_date

 16  , last_updated_by

 17  , last_update_date

 18  )

 19  VALUES

 20  ('OE_List_Lines_Base'    --view_label

 21  ,1     --query_position  number

 22  ,'Sales_Document_Name' --column_label

 23  ,'OBHA'     --table_alias

 24  ,'SALES_DOCUMENT_NAME'     --column_expression

 25  ,82    --column_position number

 26  ,'COL'    --column_type

 27  ,'The sales document name --XXCMFG'-- description

 28  ,'N'     --group_by_flag

 29  ,'N'     --gen_search_by_col_flag

 30  , NULL  --profile_option

 31  ,'*'     --product_version

 32  ,'flinstonef'     -- created_by

 33  , SYSDATE                --creation_date

 34  ,'flinstonef'     --last_updated_by

 35  , SYSDATE                --last_update_date

 36  );

INSERT INTO n_view_column_templates

When we look up the constraint, NOETIX_SYS.N_VIEW_COLUMN_TEMPLATES_FK5, using the public view, dba_cons_columns with the following query, we see the following results:

select

 table_name,

 column_name

 from

 dba_cons_columns

 where

 constraint_name = 'N_VIEW_COLUMN_TEMPLATES_FK5';

___________________________________________________________

Table                                                                                  Column

___________________________________________________________

N_VIEW_COLUMN_TEMPLATES                               VIEW_LABEL

N_VIEW_COLUMN_TEMPLATES                               QUERY_POSITION

N_VIEW_COLUMN_TEMPLATES                               TABLE_ALIAS

Consequently, we know that there is foreign key constraint composed of these three columns.

This leaves me with three areas to check:

Is there a record in the N_VEIW_TEMPLATES table for this view label?  Yes.

Is there a record in the N_VEIW_QUERY_TEMPLATES table for this view label and query position one?  Yes.

Is there an insertion argument (or existing record) in the N_VEIW_TABLE_TEMPLATES table for this view label, query position and table?  Well, no.

It turns out that the insertion statement for the N_VIEW_TABLE_TEMPLATES was malformed (specifically, I have a typographical error associated with my view label).

To conclude, Noetix template tables are designed in such a way that when records are inserted, if there is a malformed command, the errors being thrown are clear enough that one can do some detective work and fix your error. 

I really appreciate how Noetix has “well instrumented” their code so that I do not need to do a lot of work to figure out where my errors are occurring and why they are occurring.

Of course, you can try to minimize your typographical errors and that will also help in your development of hook scripts.

  

Development and Test Environment: Post Regeneration Set-up Scripts

In a development and test environment, I hate logging in to Oracle Applications and having to run concurrent programs (and check concurrent programs) associated with security and the key flexfields after I have regenerated my Noetix Views.   Specifically, I do not like spending a lot of time after a regeneration to perform set-ups to make the environment end user friendly.  

Consequently, I just created some grant scripts (around Noetix packages to APPS) and initial KFF table uploads sql scripts so that end users can test their reports without errors associated with security or KFF tables being empty. 

Here is my approach:

1.        Run a grant script because some of the Noetix packages which are executed by the Noetix concurrent program have not been properly granted (grant execute) to the APPS database user.  If this grant is not performed in the 6.01 Noetix Views (with Oracle Apps 12.1.3), I have noticed the security and KFF concurrent programs failing.

Here is my script which I spool (and spool results) using my NOETIX_SYS database account:

/*

select

'grant execute on ' || object_name ||' to apps; '

from

all_objects

where

owner = 'NOETIX_SYS'

and object_type = 'PACKAGE'

order by 1;

*/

grant execute on NOETIX_APPS_SECURITY_PKG to apps;

grant execute on NOETIX_AP_ERRORS_PKG to apps;

grant execute on NOETIX_AP_INVOICES_PKG to apps;

grant execute on NOETIX_AR_ERRORS_PKG to apps;

grant execute on NOETIX_AR_PKG to apps;

grant execute on NOETIX_BOM_PKG to apps;

grant execute on NOETIX_CSD_PKG to apps;

grant execute on NOETIX_DBMS_WRAPPER_PKG to apps;

grant execute on NOETIX_DELIVERY_PKG to apps;

grant execute on NOETIX_ENV_PKG to apps;

grant execute on NOETIX_GL_SECURITY_PKG to apps;

grant execute on NOETIX_ORDER_LINE_STATUS_PKG to apps;

grant execute on NOETIX_ORDER_STATUS_PKG to apps;

grant execute on NOETIX_PA_PKG to apps;

grant execute on NOETIX_PREFIX_PKG to apps;

grant execute on NOETIX_QA_PKG to apps;

grant execute on NOETIX_QA_RSLT_POP_PKG to apps;

grant execute on NOETIX_SERVICE_PKG to apps;

grant execute on NOETIX_USER_PKG to apps;

grant execute on NOETIX_UTILITY_PKG to apps;

grant execute on NOETIX_WF_PKG to apps;

grant execute on NOETIX_XOP_PKG to apps;

grant execute on NOETIX_XXNAO_PKG to apps;

grant execute on N_DB_OBJECTS_PKG to apps;

grant execute on N_EUL_GEN_EUL50_2_PKG to apps;

grant execute on N_EUL_GEN_EUL50_PKG to apps;

grant execute on N_EUL_GEN_HELPER_PKG to apps;

grant execute on N_EUL_GEN_ITEM_CLASS_PKG to apps;

grant execute on N_GENERATORS_PKG to apps;

grant execute on N_GEN_COLUMN_SOURCE_PKG to apps;

grant execute on N_GEN_PKG_VALIDATION to apps;

grant execute on N_GSEG_INTEGRATION_PKG to apps;

grant execute on N_GSEG_METADATA_PKG to apps;

grant execute on N_GSEG_PKG to apps;

grant execute on N_GSEG_UTILITY_PKG to apps;

grant execute on N_KFF_ACCALIAS_PKG to apps;

grant execute on N_KFF_CTLG_GRP_PKG to apps;

grant execute on N_KFF_GL_ACCT_PKG to apps;

grant execute on N_KFF_ITEM_LOC_PKG to apps;

grant execute on N_KFF_MTL_CAT_PKG to apps;

grant execute on N_KFF_SYS_ITEM_PKG to apps;

grant execute on N_SEC_MANAGER_API_PKG to apps;

grant execute on N_SEC_MANAGER_DM_PKG to apps;

grant execute on N_SEC_MANAGER_GUI_PKG to apps;

grant execute on N_SEC_MANAGER_MDSEC_PKG to apps;

grant execute on N_VIEW_PARAMETERS_API_PKG to apps;

2.       Here is my scripts to load the KFF tables:

/*

 SELECT

'execute '|| OBJECT_NAME||'.Init_Upld();'

FROM

user_objects

WHERE 1=1

 AND OBJECT_NAME LIKE 'N_KFF_%_PKG'

 AND OBJECT_TYPE= 'PACKAGE'

 ;

 */

execute N_KFF_ACCALIAS_PKG.Init_Upld();

execute N_KFF_CTLG_GRP_PKG.Init_Upld();

execute N_KFF_GL_ACCT_PKG.Init_Upld();

execute N_KFF_ITEM_LOC_PKG.Init_Upld();

execute N_KFF_MTL_CAT_PKG.Init_Upld();

execute N_KFF_SYS_ITEM_PKG.Init_Upld();

I find that this saves me some time.