IZ67703: JAVA.SQL.SQLEXCEPTION: ORA-00904: "PLUSAVPOSITIONS"."ORGID": INVALID

APAR status

• Closed as program error.

Error description

• Environment:
  Base Services 7.1.1.5
  10g Release 10.2.0.3.0
  Weblogic 9 & IBM WebSphere Application Server 6.1
  
  
  Version:
  
  Change PMP 7.1.1.00 Build 2--8502D DB Build V710-25
  Maximo Asset Configuration Manager 7.1.0.0 Build 20090525-0010
  DB Build V7110-169
  Config PMP 7.1.1.00 Build 20080424D2 DB Build V710-27,
  Common PMP 7.1.1.5 Build 20090624D2 DB Build V7115-11,
  Transportation 7.1.0.00 Build 20081211-1448 DB Build V7110-504,
  Base Services 7.1.1.5 Build 20090627-0754 DB Build V7115-149,
  
  Server OS: Windows server 2000 35.2 build 3790- Service Pack 2
  
  
  
  
  Ensure you use a maximo user who belongs to a Security Group
  that only has full privileges access to the Applications,
  "Models (CM)", under the Security module, listed under the
  Security Groups, Applications tab.
  
  The application, "Assets (CM)" was also set with  privileges
  for this users security group.
  
  
  The "Authorize Group to All Sites" should be unticked for this
  Security Group listed under the Security Groups, Sites tab.
  
  
  One Site should be listed under the Security Groups, Sites tab,
  with "Active" and "Authorized" fields checkbox ticked.
  
  
  Steps:
  
  Login to Maximo as the maximo user with access only to Asset
  Configuration Manager
  (CM) app.
  
  
  use Go TO menu option to select Asset Configuration Manager (CM)
  
  Select Models (CM)
  
  On the application screen, click on the Model tab to bring up
  the first record.
  
  On the Labels section, click on the lookup magnifying glass
  "Default for Pos"
  
  The window for Select Value pops up and had the single field,
  Position displayed
  with the message, To find records, use the filter fields above
  and then press
  Enter.
  
  
  The Weblogic MOSDOS screen for StarWeblogic.cmd will show:
  
  
  24 Apr 2009 09:33:43:590 [ERROR] Failed SQL query in
  MboSet.getNextRecordData():
  select * from plusavpositions    where (1=1) and (exists (
  select orgid from
  siteauth a,groupuser b  where a.groupname=b.groupname  and
  b.userid =  'AKANDELL'
  and a.orgid = plusavpositions.orgid ))
  java.sql.SQLException: ORA-00904: "PLUSAVPOSITIONS"."ORGID":
  invalid identifier
  
          at
  oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError
  .java:125)
          at
  oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
          at
  oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
          at
  oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
          at
  oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
  
  <SNIP>
  
  From a SQL query in the backend:
  
  Select * from plusavpositions where (1=1) and (exists ( select
  orgid from siteauth
  a,groupuser b where a.groupname=b.groupname and b.userid =
  'DDER00' and a.orgid =
  plusavpositions.orgid ))
  
  
  You will get the following error:
  
  Error encountered
  An error was encountered performing a requested operation:
  
  ORA-00904: "PLUSAVPOSITIONS"."ORGID": invalid identifier
  00904. 00000 - "%s: invalid identifier"
  *Cause:
  *Action
  Error at Line:3 COlumn:91
  
  No results are returned.
  
  However, when you run the same SQL query but removing the last
  line
  
  "and a.orgid = plusavpositions.orgid ))"
  
  the SQL query returnsto the expected full results for the
  POSITION column.
  
  
  Select * from plusavpositions where (1=1) and (exists ( select
  orgid from siteauth
  a,groupuser b where a.groupname=b.groupname and b.userid =
  'DDER00'))
  
  
  
  If you login as the maxadmin user who has access to all sites
  and full privileges, the problem is not reproduced and the
  look-up for "Default for Pos" on the Labels Section of Model tab
  returns the value of records.
  

Local fix

• Developer to provide fix
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION: When trying to select the default for   *
  *                      POS in Model (CM) app, if the user had  *
  *                      permission only in Models(CM) and       *
  *                      Assets(CM) app, an error would be       *
  *                      shown.                                  *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  *                                                              *
  *                                                              *
  *                                                              *
  ****************************************************************
  JAVA.SQL.SQLEXCEPTION: ORA-00904: "PLUSAVPOSITIONS"."ORGID":
  INVALID IDENTIFIER ERROR WHEN DELETING SUBASSEMBLY ROW
  

Problem conclusion

• The ORGID column was added to the PLUSAVPOSITIONS view.
  
  
  
  
  
  The fix for this APAR is contained in the following maintenance
  package:
  
    | release\fix pack | HAMMERHEAD P1
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Modules/Macros

• MAXIMO
  

Fix information

• Fixed component name

  MX TRANSPORTATI

• Fixed component ID

  5724R5500

Applicable component levels

• R711 PSY

     UP