Daniel Borgstrom talking about Oracle

This post describes how to create a report using XML Publisher in Oracle E-Business Suite. Traditionally when a XML Publisher report has been created Oracle Reports have been the engine to produce the XML. A template has then been developed to use the created XML. This way of working is only recommended when the Oracle Report is already available and a new layout is created. If a new report is created from scratch you should use the XML Publisher engine to create both the XML and the layout. It is possible to use this method in both 11.5.10 and R12. How to create a native XML Publisher report The query and parameters is created in a XML-document called Data Template. Example of a Data Template: <?xml version="1.0" encoding="WINDOWS-1252" ?> <dataTemplate name="CUST_LIST" description="Orderdetail per customer" version="1.0"> <parameters> <parameter name="p_fr_datum" datatype="character"/...

To list your organizations in EBS run select * from hr_all_organization_units; select * from hr_all_organization_units_tl;

To set the MOAC (Multiorg Access Control) context in your database session (SQL*PLUS, TOAD, SQL Developer etc) use: begin mo_global.set_policy_context('S', <orgID>); end; When policy context is set to M (Multi), data from all accessible Operating Units will be returned. When policy context is set to S (Single), then only data from the specified org_id will be returned. Set language: alter session set nls_language = 'SWEDISH';

When you are working with performance issues you need to have an empty database cache to get a "real" result from your query. If you run a query which runs for 2 minutes the first time it would run very much faster the second time. To prevent the database to fetch the result from the cache you need to clear the buffer cache. Run the command below to flush the cache (you need a DB-user which has sufficient privileges, for example system) alter system flush buffer_cache; Note: this should not be done in any production enviroment.

Out of the box can we not create a webservice that have a parameter (in or out) of type XMLType using SOA Gateway. When the service is called you will get an error. In-parameter: Error Description: Error occured while service was being generated. Error Details: oracle.apps.fnd.soa.util.SOAException: ServiceGenerationError: Exception returned from JCA Service Runtime. Missing class: oracle.xdb.XMLType Dependent class: oracle.tip.adapter.db.sp.oracle.TypeConverter Loader: AppsSOAAdapter:0.0.0 Code-Source: /u02/oracle/VISION2/apps/tech_st/10.1.3/j2ee/oafm/connectors/AppsSOAAdapter/AppsSOAAdapter/DBAdapter.jar Configuration: in /u02/oracle/VISION2/apps/tech_st/10.1.3/j2ee/oafm/connectors/AppsSOAAdapter/AppsSOAAdapter The missing class is available from the following locations: 1. Code-Source: /u02/oracle/VISION2/apps/tech_st/10.1.3/rdbms/jlib/xdb.jar (from in /u02/oracle/VISION2/apps/apps_st/comn/webapps/oafm/) This code-source is available in ...

When you are extending a VO you can quite often run in to the error " Each row in the Query Result Columns must be mapped to a unique query attribute in the Mapped Entity columns ". The error is related to the view object you are trying to extend and indicates that there is something wrong with it. If you search for this error on My Oracle Support you will see that the fix is to get a patch that replaces the base VO that the problem is related to. One potential error is that there are some CASE problems in the base VO declaration. For example, a selected column is named PartyId in the query but under Attributes the name is partyId. Though java is case-sensitive you can run into problems. If you google this problem you will find a few different potential solutions, but of the ones I have found, the solution below is the most reliable. Create your extended view object without changing anything. Open up you newly created VO and click on the tree node "Attributes...

When you are extending a view object (VO) in OA Framework it's possible that you will run in to the problem  java.sql.SQLException: Invalid column type , especially if it's an LOV VO you are trying to extend. When you click on the LOV torch for the first time it is working fine. But whenever you click on the GO button in the LOV Region it gives an error. The error can look like this: oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.   Statement: SELECT * FROM (SELECT ca.location LocId, ca.party_site_number LocNumber, ca.party_name PartyName, ca.party_number PartyNumber, ca.address1 Address1, ca.address2 Address2, ca.address3 Address3, ca.address4 Address4, ca.city City, ca.state State, ca.zip PostalCode, ca.country Country, ca.party_id partyId, hzp.site_use_type CurrSiteUsage FROM csi_hzpty_addresses_v ca, hz_party_site_uses hzp, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu W...

You may need to increase the timeout setting for the Apache server if you have long running webservices. Our test server was extremly slow so we needed to update this number. The below instructions will cahnge the timeout value from 300 seconds to 900. Open  $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/httpd.conf Look for the value Timeout Change  Timeout 300 to  Timeout 900 Restart opmn $ADMIN_SCRIPTS_HOME/adopmnctl.sh stopall $ADMIN_SCRIPTS_HOME/adopmnctl.sh startall

One problem you can face when you are working with a webservice that returns thousands of rows is that you will get memory problems. In the webservice SOAP response you will see something like SERVICE ERROR. When you go into eBusiness Suite and look in the SOA Monitor you will see a java exception: Error description SYSTEM_ERROR Error details java.lang.OutOfMemoryError: Java heap space at java.util.Arrays.copyOfRange(Arrays.java:3209) at java.lang.String.<init>(String.java:216) at java.lang.StringBuilder.toString(StringBuilder.java:430).... To fix this problem we need to increase the java heap space for the JVM oafm (the default value is 256MB). The below instructions will set it to 1GB. Open $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml Find the row  that starts with <process-type id="oafm" module-id="OC4J" status="enabl ... Under start-parameters change: - <data id="java-options" value="-server -verbose:gc -Xmx256M... to...

This example will illustrate on how to develop a simple interface using PL/SQL. The Integration Repository will recognize integrations through annotations in the code (details about annotations can be found in the Integrated SOA Gateway Developer's Guide, Appendix A). You can annotate *.pls and *.pkh files. For PL/SQL packages, only the package spec should be annotated. Do not annotate the body. Example of a annotated package spec: CREATE OR REPLACE PACKAGE xx_test_soagway AS /* $Header: $ */ /*# * This package returns different data from Financials (GL). * @rep:scope public * @rep:product gl * @rep:displayname xx_test_soagway * @rep:lifecycle active * @rep:compatibility S * @rep:category BUSINESS_ENTITY GL_ACCOUNT_COMBINATION */ /*# * Returns CCID * @param P_SEGMENT1 varchar2 Segment 1 * @param P_SEGMENT2 varchar2 Segment 2 * @param P_SEGMENT3 varchar2 Segment 3 * @param P_SEGMENT4 varchar2 Segment 4 * @param P_SEGMENT5 varchar2 Segment 5 * @return CCID ...

The Integrated SOA Gateway is a product released with Oracle E-Business Suite version 12. SOA Gateway is built on top of Oracle Fusion Middleware and service-oriented architecture (SOA) technology. This infrastructure not only allows greater and effective business integration between heterogeneous applications, but also facilitates the development and execution of complex business processes into highly flexible and reusable Web services. With this standardized and interoperable Web service platform, Oracle E-Business Suite Integrated SOA Gateway provides a powerful framework that accelerates dynamic business processes and service integration between applications over the Web. An integration developer can perform end-to-end service integration activities including creating and annotating custom integration interfaces, orchestrating discrete Web services into meaningful end-to-end business processes, defining Web service invocation metadata, and testing the Web service invocation. Ap...