Loading....

Recently while applying the Financials Family Pack G, we encountered some issues where certain forms and libraries were not compiling and we had to manually create some new tables and packages and also modify some existing tables and view.

We can not only create new database objects, but also verify if the objects currently stored in the database have the right definition which is being expected by the particular patch which we were running.

The object defintions are stored in the odf and xdf files which are located under the patch sub-directory $PRODUCT_TOP/patch/115/odf and $PRODUCT_TOP/patch/115/xdf.

The Java Utility FndXdfCmp is located under the $JAVA_TOP/oracle/apps/fnd/odf2 to verify and create Database Objects, defined in a xdf-File.

To verify and create Database Objects, defined in a odf-File adodfcmp utility is used and it is located under the $AD_TOP/bin.

Using adodfcmp

This is an example of the adodfcmp command:

adodfcmp odffile=arcceh.odf mode=tables changedb=YES userid=AR/ar touser=APPS/appspwd priv_schema=SYSTEM/systempwd

odffile: name of the Object definition odf file

Mode: type of the objects to be compared or created. Valid values include tables, views, sequences

Changedb: toggle between YES and NO

userid: this is the ORACLE database user account who owns the base object

touser: this will typically be the APPS user account

priv_schema: database user account with DBA privileges – we are using SYSTEM in this case

Initially we run it with changedb=NO which will not make any changes in the database but willl just write the actions to the odf log file which we can review.

If we are satisfied with the object creation commands which will be executed, we can use changedb=YES which will cause the objects to be created or modified as the case may be in the database.

Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA
ODF (Object Description File) Comparison Utility
Version 11.5.0
NOTE: You may not use this utility for custom development
unless you have written permission from Oracle Corporation.
************* Start of ODF Comparison Utility session *************
ODF Comparison Utility version: 11.5.0
ODF Comparison Utility started at: Mon Apr 23 2012 17:44:59
Reading objects from ODF file
Reading table AR_CC_ERROR_MAPPINGS …
Reading table AR_CC_ERROR_HISTORY …
*** Reading objects from AR schema

*******************************************************
The database is missing the table AR_CC_ERROR_MAPPINGS.
Create it with the statement(s):
CREATE TABLE AR.AR_CC_ERROR_MAPPINGS (CC_ERROR_CODE VARCHAR2(80) NOT NULL,
CC_ERROR_TEXT VARCHAR2(255), RECEIPT_METHOD_ID NUMBER(15) NOT NULL,
CC_TRX_CATEGORY VARCHAR2(15) NOT NULL, CC_ACTION_CODE VARCHAR2(30) NOT
NULL, NO_DAYS NUMBER, SUBSEQUENT_ACTION_CODE VARCHAR2(30), ERROR_NOTES
VARCHAR2(255), LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER(15)
NOT NULL, LAST_UPDATE_LOGIN NUMBER(15), CREATION_DATE DATE NOT NULL,
CREATED_BY NUMBER(15) NOT NULL, OBJECT_VERSION_NUMBER NUMBER(15))
STORAGE(INITIAL 4K NEXT 128K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE
0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE 10 PCTUSED 60 INITRANS 10
MAXTRANS 255
CREATE UNIQUE INDEX AR.AR_CC_ERROR_MAPPINGS_U1 ON AR.AR_CC_ERROR_MAPPINGS
(CC_ERROR_CODE, CC_TRX_CATEGORY, RECEIPT_METHOD_ID) LOGGING STORAGE
(INITIAL 4K NEXT 32K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0
FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE 10 INITRANS 11 MAXTRANS 255
COMPUTE STATISTICS
*******************************************************
Schema is “AR”, SQL statement is:
GRANT ALL ON AR.AR_CC_ERROR_MAPPINGS TO APPS_APPLFND WITH GRANT OPTION
[did not execute above statement — Mode changedb = NO]

CREATE OR REPLACE SYNONYM APPS_APPLFND.AR_CC_ERROR_MAPPINGS FOR
AR.AR_CC_ERROR_MAPPINGS
Schema is “AR”, SQL statement is:
GRANT ALL ON AR.AR_CC_ERROR_HISTORY TO APPS_APPLFND WITH GRANT OPTION
[did not execute above statement — Mode changedb = NO]

CREATE OR REPLACE SYNONYM APPS_APPLFND.AR_CC_ERROR_HISTORY FOR
AR.AR_CC_ERROR_HISTORY

ODF Comparison Utility is complete.
You should check the file
/u01/home/applprod/patch/3653484/ar/patch/115/odf/adodfcmp.log
for errors.

If we now set changedb=YES, we can see that the statements are executed in the database.

The database is missing the table AR_CC_ERROR_MAPPINGS.
Create it with the statement(s):
CREATE TABLE AR.AR_CC_ERROR_MAPPINGS (CC_ERROR_CODE VARCHAR2(80) NOT NULL,
CC_ERROR_TEXT VARCHAR2(255), RECEIPT_METHOD_ID NUMBER(15) NOT NULL,
CC_TRX_CATEGORY VARCHAR2(15) NOT NULL, CC_ACTION_CODE VARCHAR2(30) NOT
NULL, NO_DAYS NUMBER, SUBSEQUENT_ACTION_CODE VARCHAR2(30), ERROR_NOTES
VARCHAR2(255), LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER(15)
NOT NULL, LAST_UPDATE_LOGIN NUMBER(15), CREATION_DATE DATE NOT NULL,
CREATED_BY NUMBER(15) NOT NULL, OBJECT_VERSION_NUMBER NUMBER(15))
STORAGE(INITIAL 4K NEXT 128K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE
0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE 10 PCTUSED 60 INITRANS 10
MAXTRANS 255
Statement executed.
CREATE UNIQUE INDEX AR.AR_CC_ERROR_MAPPINGS_U1 ON AR.AR_CC_ERROR_MAPPINGS
(CC_ERROR_CODE, CC_TRX_CATEGORY, RECEIPT_METHOD_ID) LOGGING STORAGE
(INITIAL 4K NEXT 32K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0
FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE 10 INITRANS 11 MAXTRANS 255
COMPUTE STATISTICS
Statement executed.

Using FndXdfCmp

This is the usage:

adjava -mx512m -nojit oracle.apps.fnd.odf2.FndXdfCmp [Oracle_Schema] [Oracle_Password] \
[apps_schema] [apps_password] [jdbc protocol] [JDBC_Connect_String] [Object Type] \
[full path to xdf file] [full path of $FND_TOP/patch/115/xdf/xsl

Note: ChangeDb can also be specified as ChangeDb=y/n. The default is y so objects will be created in the database.

For example:

applprod@/u01/home/applprod $ adjava -mx512m -nojit oracle.apps.fnd.odf2.FndXdfCmp fa fa apps appspwd thin \
test-4:1523:CLMTS10G all fa_inquiry_trx_gt.xdf $FND_TOP/patch/115/xdf/xsl

Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA
AD Java
Version 11.5.0
NOTE: You may not use this utility for custom development
unless you have written permission from Oracle Corporation.
Reading product information from file…
Reading language and territory information from file…
Reading language information from applUS.txt …
Temporarily resetting CLASSPATH to:
“/u02/prod/clmfinpr/clmfinprappl/ad/11.5.0/java/adjri.zip:/u01/home/applprod/patch/jdk16/jdk1.6.0_20/jre/lib/rt.jar:/u01/home/applprod/patch/jdk16/jdk1.6.0_20/lib/dt.jar:/u01/home/applprod/patch/jdk16/jdk1.6.0_20/lib/tools.jar:/u01/home/applprod/patch/jdk16/jdk1.6.0_20/jre/lib/charsets.jar:/u02/prod/clmfinpr/clmfinprcomn/java/appsborg2.zip:/u02/prod/clmfinpr/clmfinprora/8.0.6/forms60/java:/u02/prod/clmfinpr/clmfinprcomn/java”
Calling /u01/home/applprod/patch/jdk16/jdk1.6.0_20/bin/java …
================================================================================
Applying XDF file : fa_inquiry_trx_gt.xdf
================================================================================
Copyright (c) 2003 Oracle Corporation
Redwood Shores, California, USA
XDF(XML Object Description File) Comparison Utility
Version 1
NOTE: You may not use this utility for custom development
unless you have written permission from Oracle Corporation.

Creating Table in the XDF file
Table Name is FA_INQUIRY_TRX_GT
Table does not exist in the target database
Executing create Statement
Create Statement is

CREATE GLOBAL TEMPORARY TABLE “FA”.”FA_INQUIRY_TRX_GT”
( “TRANSACTION_HEADER_ID” NUMBER(15,0),
“ASSET_ID” NUMBER(15,0),
“BOOK_TYPE_CODE” VARCHAR2(15)
) ON COMMIT PRESERVE ROWS
Start time for statement above is Tue Apr 24 10:49:10 WST 2012
End time for statement above is Tue Apr 24 10:49:10 WST 2012
Executing grants on FA_INQUIRY_TRX_GT to APPS_APPLFND
GRANT ALL ON FA.FA_INQUIRY_TRX_GT TO APPS_APPLFND WITH GRANT OPTION
Creating synonym FA_INQUIRY_TRX_GT for FA.FA_INQUIRY_TRX_GT
Synonym created
Index hashcode(s) extracted from the XDF.
Hashcodes generated for DB indexes.
Index FA_INQUIRY_TRX_GT_N2 does not exist in FA.
CREATE INDEX “FA”.”FA_INQUIRY_TRX_GT_N2″ ON “FA”.”FA_INQUIRY_TRX_GT” (“ASSET_ID”, “BOOK_TYPE_CODE”)
Start time for statement above is Tue Apr 24 10:49:11 WST 2012
End time for statement above is Tue Apr 24 10:49:11 WST 2012
Index FA_INQUIRY_TRX_GT_N2 Created
Index FA_INQUIRY_TRX_GT_N1 does not exist in FA.
CREATE INDEX “FA”.”FA_INQUIRY_TRX_GT_N1″ ON “FA”.”FA_INQUIRY_TRX_GT” (“TRANSACTION_HEADER_ID”)
Start time for statement above is Tue Apr 24 10:49:11 WST 2012
End time for statement above is Tue Apr 24 10:49:11 WST 2012
Index FA_INQUIRY_TRX_GT_N1 Created

Please follow and like us:

Last Update: June 23, 2020  

April 27, 2012 119 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , , ,

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?