Pages

Recently Viewed

Saturday, December 28, 2013

Make Dependent VS Enterable Without Passing Parent VS

While creating dependent value set in EBS, we found dependent (child) field is greyed (disabled) at beginning until parent field gets some value.
Requirement is to enable child field from beginning even though parent field does not have any value passed.
Supplier site is dependent on supplier and hence is disable initially and get enabled once supplier name will have value.
Above will be achieved by adding below in where condition –
WHERE and ORDER BY
Vendor_id = :$FLEX$.Supplier_test
Solution dependent VS can be enabled since beginning by modifying the above WHERE condition as below –
WHERE and ORDER BY
Vendor_id = :$FLEX$.Supplier_test:NULL
This will enable site value set.


From : Dixidha

More than 2 column values in LOV

To enable LOV on a parameter, a Table value set is associated with parameter field. While running LOV displays Value and Meaning values in LOV window.

Requirement is to add extra additional fields in LOV.
Fox example if we have supplier site LOV along with site code and site name, supplier name will add extra advantage to pick the right site if we have same name sites available.  
Solution is additional columns option available on value set definition form.
Syntax
Column_name1 “Alias” (*),Column_name2 “Alias” (*),….
                                                                                                                                                                                                                       
 
Craeted VS supplier site and added below in additional columns
 segment1 “VendorName”(*),vendor_id ”Vendor_id”(*)
Supplier LOV shows vendor name, vendor number and vendor id.

From : Dixidha

Thursday, December 26, 2013

Initialize Apps Context in Java file while creating dependent VS in WEBADI

Oracle provides standard class which can be used to initialize the apps context (set policy context) in java files if required –
 
Class Name – PaMoInit
Method – MoInit
Location - oracle.apps.pa.webadi.utilities
 
Call –
PaMoInit.MoInit(paramBneWebAppsContext);
 
 
This can be simply import if we are in PA module. Please check if same exist for any other modules otherwise creates below method and register (place) at some places.
 
 
package oracle.apps.pa.webadi.utilities;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.apps.bne.exception.BneException;
import oracle.apps.bne.framework.BneWebAppsContext;
import oracle.apps.fnd.common.VersionInfo;
 
public class PaMoInit
{
  public static void MoInit(BneWebAppsContext paramBneWebAppsContext) throws SQLException, BneException
  {
    Connection localConnection = paramBneWebAppsContext.getJDBCConnection();
    String str = "";
    CallableStatement localCallableStatement = null;
 
    StringBuffer localStringBuffer = new StringBuffer();
 
    localStringBuffer.append("begin PA_MOAC_UTILS.INITIALIZE; end;");
    localCallableStatement = localConnection.prepareCall(localStringBuffer.toString());
    localCallableStatement.execute();
    localCallableStatement.close();
  }
}

 

 

Friday, December 20, 2013

No Parameter List Definition at Parameter list id

 
Issue Cause – Such issue will occur only when integrator is being created by modifying existing integrator LDT file.
Integrator definition section contains upload (UPL) and import (IMPORT) and import list details. Integrator
 
This error occurs if defined details at integrator level do not have correct definition-  
 
IMP_TRANS_GROUP in XXXXX schema is one of the import list associated to integrator.
 
Import list definition is as below -
 
Seems the definition is also defined in XXXXX schema only still we got above error.
 
Root Cause – There is a difference in STRING_VALUE.
275:IMP_TRANS_GROUP , this is not a simple string this is APPLICATION_ID:IMPORT_List Name
 
Solution –
STRING_VALUE = "2000:IMP_TRANS_GROUP"
Will be the right value since XXXXX, application is 20000.
 
Change the LDT and upload, issue will be resolved.

 

 

Sunday, October 13, 2013

Required upload column was not in upload and has no default value defined

Error -
Getting error “Required upload column was not in upload and has no default value defined” while uploading the WebADI.
 
Cause –
There will be one NOT NULL column in interface and passed no values in upload.
 
Resolution -
Either provide the value or define the default values.
 
Select List of Values option (put the cursor on error message in excel) from Menu ( Oracle Add in).
 
This will help to know the problematic column name.
 
 

Wednesday, October 2, 2013

Remove Control M Character from unix file


Use Dos2unix command to remove control M characters from any shell script or prog files.

Syntex
Dos2unix <file_name>

Example
Dos2unix Sandeep.sh
Dos2unix Sandeep.prog  

Value for the Flexfield Segment does not Exist in the Value Set

 
 
Defining a value set on any DFF will disallow to search records having non eligible (out of value set range) segment values.
 
A good example of this is to create a DFF of segment project task name. Initially associate a value set of Table type which shows all tasks from pa_tasks_all.
We create some records using assigned DFF. After sometime update value set definition to restrict disabled/end dated tasks.
 
Now search for records which has been created earlier and having disabled tasks assigned. Oracle form will throw below error message –
 
 
Approach – Try restricting the validation query while search.  
 
Solution – We faced similar issue and did below steps to overcome issue –
           1) Created a profile (XXXXX_SANDEEP_PRF) and set value as N
           2) Updated the value set query as
 
           Current condition –
           WHERE   sysdate between start_date and nvl(completion_date,sysdate)
 
           Updated Condition –
            WHERE  (sysdate between start_date and nvl(completion_date,sysdate))
                               OR
                               (nvl(:$PROFILES$.XXXXX_SANDEEP_PRF,'Y')='N')
           3) Use form personalization to change the profile value in cache in search and edit mode
           4) Set the profile value =Y, when in Edit mode. This will fail the second condition and only first 
                     condition will be valid and so only active task will appear in LOV.
          Set the profile value = N when in search mode. First condition will be failed since task is  
              end dated but profile condition will pass and so record will appear without error.
            5)  Did not try this in same form. We have created 2 form functions ( Entry (profile Y) and 
                 Enquiry (profile N) mode)
 
 
 

Thursday, September 5, 2013

Change the profile value at run time in Core Forms

We have one custom profile which value is set at different levels. Need is to change the value while working on core forms.
                                                
This can be achieved using form personalization. Form personalization has 4 different actions type.
    1)    Property
    2)    Message
    3)    Builtin
    4)   Menu
 
Builtin provides capability to change the profile value during run time in cache.
 
 
This will change the profile value in cache during run time J
 
 
 
 

Friday, August 9, 2013

Increase no of Searched Records on OAF page

Requirement:
 OAF page search result appears in table and display by default 10 rows. We need to click next to see another 10 records and keep pressing next.
This will be frustrating when the number of records is more than 100.
 
 
Resolution:
Default search results count can be increased by OAF personalization.
 
Steps:
1)     Enable the personalization link on OAF page ( if not available)
2)     Click on the highlighted link
 
 
3)       Click on pencil icon
 
4)       Set the value at required level
 
5)       Click on apply and return to application
 
6)       Check the result count. It is increased to 49 now J
 

 

 

Saturday, June 15, 2013

FNDLOAD for Audit Configurations

Below is the LCT file and syntex to download the LDT version of oracle Audit configurations


File Name - affaudit.lct

FNDLOAD apps/$apps_Pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/affaudit.lct $file_Name.ldt FND_AUDIT_GROUPS GROUP_NAME="$Group_Name"



 

Saturday, May 4, 2013

oracle.jbo.JboException: JBO-29000: Unexpected exception caught:

Error Message :

Exception Details. 
oracle.apps.fnd.framework.OAException: oracle.jbo.JboException: JBO-29000:
Unexpected exception caught: oracle.classloader.util.AnnotatedNoClassDefFoundError, msg=
  
Missing class: XXXX.oracle.apps.po.document.order.webui.XXXXOrderPaymentDetailsCO (wrong name: oracle.apps.po.document.order.webui.XXXXOrderPaymentDetailsCO)

 Dependent class: XXXX.oracle.apps.po.document.order.webui.XXXXOrderPaymentDetailsCO
          Loader: oacore.root:0.0.0
     Code-Source: /oracle/ebs/apps/apps_st/comn/java/classes/
   Configuration: <library> in /oracle/ebs/apps/apps_st/comn/webapps/oacore/

The missing class is not available from any code-source or loader in the system.


Cause :
Above error message states that extended CO (XXXXOrderPaymentDetailsCO) is not available at ight place. As per EBS practise all custom code should be placed on cstom top  whch reference are provided on personalization (controller path).

In JAVA file, first line denotes package reference,it's like location of CO files, The above error occurs if package name location <> 
cumtom path given in personalization page <>
custom CO is not avaialble at location


Resolution :
Ensure all 3 things ate having same path. i.e.
Custom code are placed @ right location
Same location is mentioned in java file @ first line ( package)
same path is referenced while personalization



 

Sunday, April 28, 2013

Remove VO personalization

Personalization page supports to extend and remove the controller (CO) but no interface to extend the VO. 

VO extension requires Jdeveloper to create substitute, which will generate .jpx and server.xml files.
Later JPXImporter will apply extended VO to system.

To backout the extended VO, we can use database scripts to delete personalizations .

refer OAF migration

Steps to back out VO extension -
1)  Get the original VO name ( with complete path). This will be document path.
      Example /oracle/apps/po/lov/server/DocumentNumbersVO 

===========================================================
Use listcustomization to find the personalization details

BEGINJDR_UTILS.LISTCUSTOMIZATIONS('/oracle/apps/po/lov/server/DocumentNumbersVO');
END;


===========================================================
Use delete script to delete the personalization
Begin
jdr_utils.deletedocument(p_document=>'/oracle/apps/po/lov/server/customizations/site/0/DocumentNumbersVO');
END;

=========================================================== 

Saturday, April 6, 2013

How to restrict LOV data in EBS

Requirement is to restrict some data from LOV available on OAF pages or Core Forms.
 
If LOV is available at Core forms, it’s difficult to restrict data as core form personalization does not support this level restriction.
If we are at OAF page view extension (OAF customization) is the option to restrict LOV data. In next lines we shall discuss an easy method to restrict data in LOV for both Core forms and OAF pages.
 
How to achieve this?
It’s quite easier to restrict LOV data using Oracle provided data restriction method. Oracle recommends VPD for data restriction, which can be a better approach over OAF customization.
This can be useful for core forms as well.
 
Use below steps to implement this –
>Find out the source for LOV data –
OAF Page – Go to about this page >> Find associated VO >> Check the underlying SQL
>> Find the table/view having actual data >> Use VPD to restrict the data on source table/view
 
Core Form – Get the fmb file (from AU_TOP) and open in form builder >> Find the associated LOV >> Find Attached Data Source
>> Find the table/view having actual data >> Use VPD to restrict the data on source table/view
 
Use of VPD over OAF customization will be easy to maintain and deploy to higher instances.
 
Example:
Restrict Create Order options on order (Buyer work center) page:
Create LOV has 2 options 1) SPO and 2) CSPO >> we need to exclude SPO option
 
Find the source for LOV data and got that source table is PO_DOC_STYLE_LINES_TL
 
Created function to restrict SPO on the source table
  
 

                     
                      
                       
 
 
 
 
 
 
Add this function to policy.

 
 
 
 
 
 
 
 
 
 
 

Go to application and check the create option –

 

 

Friday, April 5, 2013

What is Virtual Private Database (VPD)

VPD is new Oracle Database feature to enable policies on database objects ( i.e Table, Synonyms, View etc) This is used to control data access for security reasons.
 
What actually VPD does >> this append additional where condition to objects based on defined policies.  
 
How to use VPD>> below are the steps used to implement VPD in system.
1)     Identify the object:  Lest take APPS.PO_HEADERS_ALL (Synonym) for VPD testing.  
2)     Create the function: Create a function ( or package.function) which will return the required filter condition
3)     Add to policy – Use standard oracle API to create a policy for created function ( step 2)
  
Example
Step#1) Assume PO_HEDAERS_ALL has 100 records. Will update DFF Column (Attribute1) =’Sandeep’ for any 10 records.
Requirement is  to restrict data having DFF value as ‘Sandeep’ for schema ‘xxxxx’.
 
Step#2) Created function Sandeep


 

 
 
 
 
 
 
 Step#3) 
 
 
 

 
 

 
  
Now run the below statement –
 
APPS Schema
Select count (1) from PO_Headers_all  
>> 100 rows selected
 
XXXXX Schema
Select count (1) from PO_Headers_all  
>> 10 rows selected
 
APPSQUERY Schema
Select count (1) from PO_Headers_all  
>> 100 rows selected
 
 
VPD is one of the major changes in MOAC concepts in R12.
 

Saturday, March 16, 2013

Generic UNIX script to import OAF personalizations

Below is the piece of UNIX which will import OAF personalization to system.  This is a common script and will work for all CO, VO extension or other OAF personalization.
 
 
# ***********************************************************************************************************
#  Import OAF Personalizations
# ***********************************************************************************************************
echo "# ******************************************************************************" >> $v_log
echo "# Import OAF Personalizations" >> $v_log
echo "# ******************************************************************************" >> $v_log
echo ""
echo "Immporting OAF Personalizations...."
 
########### Import CO Extension & Other Personalizations ################################
for file in `find unzipped_folder | grep PG.xml`
do
                                v_file_name=`echo $file | rev | cut -d "/" -f1 | rev`
                                lv=`echo $file | sed s/$v_file_name/"@"/g | cut -d "@" -f1`
                                v_MDS_RN=`echo $JAVA_TOP/$lv`
                                v_rootPackage=`echo $lv | sed s/" unzipped_folder "/"@"/g | cut -d "@" -f2`
                                                               
                                echo "File Name : $v_file_name" >>$v_log
                echo "lv :: $lv" >>$v_log
                                echo "MDS_RN : $v_MDS_RN" >>$v_log
                                echo "Root Package : $v_rootPackage" >>$v_log
     
                                 java oracle.jrad.tools.xml.importer.XMLImporter ${v_MDS_RN}${v_file_name} -username apps -password $v_apps_schema -dbconnection "$lv_jdbc" -rootdir ${v_MDS_RN} -rootPackage $v_rootPackage >>$v_log
 
                                if [ $? != 0 ];then
                                                echo "ERROR - Failed while importing $file"   >>$v_log
                                                echo "ERROR - Failed while importing $file"  
                                else
                                                echo "Import $file successfully "  >>$v_log
                                fi
                                echo " " >> $v_log
done
 
 
########### Import VO Extension################################
for file in `find xxxxx | grep .jpx`
do
                                v_file_name=`echo $file | rev | cut -d "/" -f1 | rev`
                                lv=`echo $file | sed s/$v_file_name/"@"/g | cut -d "@" -f1`
                                v_MDS_RN=`echo $JAVA_TOP/$lv`
                               
                                                               
                                echo "File Name : $v_file_name" >>$v_log
                                echo "lv :: $lv" >>$v_log
                                echo "MDS_RN : $v_MDS_RN" >>$v_log
                                      
                                 java oracle.jrad.tools.xml.importer.JPXImporter ${v_MDS_RN}/${v_file_name} -username apps -password $v_apps_schema -dbconnection "$lv_jdbc" >>$v_log
 
                                if [ $? != 0 ];then
                                                echo "ERROR - Failed while importing $file"   >>$v_log
                                                echo "ERROR - Failed while importing $file"  
                                else
                                                echo "Import $file successfully "  >>$v_log
                                fi
                                echo " " >> $v_log
done
fi
# ******************************************************************************
 
 
 
Prerequisite is to place all java,class & XML files at correct folder path. For example if we are extending the agreement CO.  File structures should be –
 
xxxx.oracle.apps.po.documents.agreement.webui
*.class
*.java
xxxx.oracle.apps.po.documents.agreement.webui.customization.site/responsibility.0/responsibility_id
*.xml
 

 

 

Sunday, March 3, 2013

FNDLOAD+

FNDLOAD+ is extended (customized) standard FNDLOAD utility to upload LDT files.
 
How to use -
  • Login to server
  • Go to any directory (/staging/Sandeep)
  • Place all your LDT files
  • Call FNDLOAD+
 
User Inputs -
 FNDLOAD+ will ask for Apps DB password
 
Syntax-
[Unix server sandeep] $ FNDLOAD+
 
What is  +  in this?
  • Not required to use complete FNDLOAD syntax
  • Not required to know the LCT file name for upload
  • Single run is required to upload all LDT files available in current dir
 
 
How to get FNDLOAD+ available in my environments –
  • Check the $PATH variable       (echo $PATH)
  • Go to any of the defined dir  (cd ….)
  • Create file Sandeep.sh
  • Create FNDLOAD+ (cp Sandeep.sh FNDLOAD+)                                                              
***** Only to UPLOAD ldt files*************


 
 

Saturday, March 2, 2013

UPLOAD LDT Files using shell script

Requirement is to migrate the configuration files (LDTs) across environments.
 
Resolution - FNDLOAD is Oracle provided utility to achieve this by passing certain inputs. The required inputs are
1)       DB credentials
2)       Respective LCT name
This is very commonly and frequently used utility in EBD development.
 
Through this post we are sharing a shell script which can exclude LCT name parameter knowledge for LDT migration.
 
# !/bin/sh
 
echo "Enter APPS password : "
stty -echo
read v_apps_schema
stty echo
 
for file in `ls *.ldt`
do
v_ext=`echo | cut -d "." -f2`
v_lct=`grep "patch/115/import/" $file | cut -d "@" -f2`
echo "File Name    :$file" 
echo "Control File :$v_lct"
FNDLOAD apps/$v_apps_schema 0 Y UPLOAD @$v_lct $file
if [ $? != 0 ];then
echo "ERROR - Failed while uploading $file" 
else
echo "Uploaded $file successfully " 
fi
done
 
  • Copy the above script and save this as Sandeep.sh
  • $Dos2unix Sandeep.sh
  • Place the source LDT files and Sandeep.sh
  • $sh Sandeep.sh
 

How to get JDBC Connection details

In EBS JDBC connection are required while migrating XML files to other environments. XML files are source for XML publisher reports and OAF personalization.
Like FNDLOAD Oracle has provided JPXimporter and XDOloder java utilities to inport XML data into standard database tables. Java utilities require JDBC connection in order to execute and hence below are 2 methods to get JDBC connection.
 
 
Method 1 > Using Database
Parameters
Oralce Functions
Host Name/IP Address
UTL_INADDR.get_host_address
SID
sys_context('userenv','instance_name')
Port
Hard Code
 
 
 
Method 2 > Using context files
Server has context file to store all context details. One can connect to server and extract the jdbc connection string using unix command.
 
lv_contxtfile=`echo $CONTEXT_FILE`
echo "CONTEXT_FILE  : $lv_contxtfile"
lv_jdbc=`grep '</jdbc_url>' $lv_contxtfile | cut -d "@" -f2 | cut -d "<" -f1`
echo "JDBC COnnection String : $lv_jdbc"
 
o    Echo $CONTEXT_FILE will list the context file name with full path
o    Grep will search string “</jdbc_url>” in context file and returned line will be cut from literal “@”. –f2 will return the right side portion from “@”.
o     Which will be cut further with literal “<” and -f1 will return the left side portion now.
o The final output should be complete JDBC_STRING, which can be used.
 
 
** Thanks to my colleagues Srini & Manish for above info **

 

 

SQL to find XML Files (RTF/Data Template/RTF ) Details

Given SQL is to fetch the files details uploaded through XML Publisher Administrator Responsibility
 
SELECT
 a.application_short_name,
  a.data_source_code,
  b.lob_code,
  b.lob_type,
  b.file_name,
  b.last_update_date
FROM
   XDO_DS_DEFINITIONS_VL a,
   XDO_LOBS b
WHERE a.data_source_code    =b.lob_code
AND a.application_short_name=’XXXXX’
AND a.data_source_code      =:ConCurrentProgram or Data Defintion Code;
;

 

 

Saturday, February 16, 2013

Import data Template/RTF/Bursting control files

Problem is that any custom XML publisher report requires dependency on application access in PROD (XML Publisher Administrator (R)). All supporting files for XML report are being uploaded through application.
 
Requirement is to have any import method for XML publisher supporting files i.e.
Data Template file (XML file)
Bursting Control File (XML file)
Template (RTF file)
 
Resolution
XDOLoader is the standard OA utility to suffice this requirement. This is similar to unix based FNDLOAD utility and should be run from server with all parameters.  
 
Syntex

java oracle.apps.xdo.oa.util.XDOLoader  
MODE                                                                 UPLOAD/DOWNLOAD
–DB_USERNAME                                             apps user name
–DB_PASSOWRD                                             apps password
–JDBC_CONNECTION                                     jdbc connection string
–LOB_TYPE                                                       TEMPLATE_SOURCE/   
                                                                              DATA_TEMPLATE/
                                                                              BURSTING_FILE             
–APPS_SHORT_NAME                                     xxxxx
–LOB_CODE                                                       Data definition Code
–LANGUAGE                                                      en
–XDO_FILE_TYPE                                             RTF/XML-DATA-TEMPLATE
                                                                              /XML-BURSTING-FILE
[–CUSTOM_MODE]                                            FORCE
[–LOG_FILE]                                                       log file name
[-DEBUG]                                                             true/false
 
 
RTF File >>
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -JDBC_CONNECTION  "(DESCRIPTION= (ADDRESS=(
PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))"  -LOB_TYPE TEMPLATE_SOURCE -APPS_SHORT_NAME XXXXX  -LOB_CODE DATA_DEFINITION_CODE -LANGUAGE en  -XDO_FILE_TYPE RTF -FILE_NAME 123.rtf -CUSTOM_MODE FORCE
 
 
 
Data Template File >>
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -JDBC_CONNECTION  "(DESCRIPTION= (ADDRESS=(
PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))"  -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME XXXXX  -LOB_CODE  DATA_DEFINITION_CODE -LANGUAGE en  -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME 123.xml -CUSTOM_MODE FORCE
 

 
Bursting File >>
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -JDBC_CONNECTION  "(DESCRIPTION= (ADDRESS=(
PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))"  -LOB_TYPE BURSTING_FILE -APPS_SHORT_NAME XXXXX  -LOB_CODE  DATA_DEFINITION_CODE -LANGUAGE en  -XDO_FILE_TYPE XML-BURSTING-FILE -FILE_NAME 123b.xml -CUSTOM_MODE FORCE
 
**Highlighted are user defined inputs