tag:blogger.com,1999:blog-4792954902353371332024-02-17T03:48:44.760-05:00Oracle E-Business SuiteOracle Application/E-Business Suite(EBS) Articles, Features, Tips, Customization Standards etc...Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.comBlogger31125tag:blogger.com,1999:blog-479295490235337133.post-47120827303216824492012-09-12T11:14:00.007-04:002012-09-12T11:20:05.699-04:00Oracle Enterprise Scheduler<div style="text-align: justify;">
Oracle Enterprise Scheduler (ESS) is an application plugged into Fusion Applications to define, schedule and run different types of jobs and do advance scheduling. This functions similar to Concurrent Manager/Program in EBS, but with lot more new functionalities and of course with more user friendly navigations.</div>
<br />
<div style="text-align: left;">
Oracle Enterprise Scheduler lets you to</div>
<ul style="text-align: justify;">
<li style="text-align: left;">Run various jobs, supports PL/SQL, Java and other binary processes</li>
<li style="text-align: left;">Distribute job request processing across a grid of application servers</li>
<li style="text-align: left;">Group job requests into job sets, like request set in EBS</li>
<li style="text-align: left;">Schedule & Automate job requests based on recurrence expressions</li>
<li style="text-align: left;">Support sophisticated scheduling and workload management</li>
<li style="text-align: left;">Schedule a future time for a step in a business flow for business process management</li>
<li style="text-align: left;">Also to extend the standard application to manage job request submissions</li>
</ul>
Refer <a href="http://docs.oracle.com/cd/E15586_01/fusionapps.1111/e10142/intro1.htm" target="_blank">Fusion Applications Developer's Guide for Oracle Enterprise Scheduler</a> document to learn more about this.Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com33tag:blogger.com,1999:blog-479295490235337133.post-86525593849943208872012-09-11T15:51:00.002-04:002012-09-12T11:16:51.319-04:00Extensible Flexfield (EFF)<div style="text-align: justify;">
Extensible Flexfield (EFF) is a new feature added in Fusion Application with existing Descriptive & Key Flexfields. It is similar to descriptive flexfield, but with the ability to add as many context-sensitive segments (attributes) to a flexfield as they need. </div>
<br />
Some of the key benefits using Extensible Flexfields are<br />
<ol>
<li>Number of configurable segments are not fixed unlike fixed number of segments in DFF</li>
<li>Attributes can be grouped into one or more attribute groups (ie contexts) rather than only one in DFF</li>
<li>EFF supports one-to-many relationships between the row and the extended attribute rows</li>
<li>Improved access controls than using DFF. It is possible to control the view and edit the attributes based on the context configurations.</li>
</ol>
To know more about this, refer Fusion Application Developer’s Guide & Extensibility Guide.Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com27tag:blogger.com,1999:blog-479295490235337133.post-81501717451691408362011-03-07T14:10:00.008-05:002011-03-07T14:24:47.835-05:00How to use ADF in E-Business Suite?Oracle recently announced the different types of supported integration between EBS and ADF.<br /><br />1. Integration to EBS using the Oracle SOA Suite and Application Development Framework 11g (ADF 11g). This type of integration typically uses the AppsDataSource feature but no other parts of EBS SDK.<br />2. Launch ADF Application from EBS, in this case the Oracle ADF user interface is launched from EBS home page. There is no further user interface interaction from ADF application with EBS.<br />3. Integration that allows users to move back and forth between the ADF user interface and EBS. For example, a user could go to an ADF page from an OA Framework page and back again, or ADF components could be embedded into an OAF page. This requires some context being shared between OAF and ADF, as well as session management between the two systems.<br /><br />However option 2 and 3 supported only from R12, 3rd one is still not fully supported. For more detailed information refer Oracle Support Document (<a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1296491.1">1296491.1</a>).Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com10tag:blogger.com,1999:blog-479295490235337133.post-26616239805561911672009-10-27T10:43:00.007-04:002011-03-07T13:57:36.381-05:00Responsibilities using Concurrent ProgramWe know to use any Concurrent Program first it needs to be added to the Request Group and Request Group needs to be attached to Responsibility. Then we can able to submit the program using program request form.<br /><br />One program can be attached to many Request Groups. Again Request Group can be attached to many Responsibilities. Because of One-Many-Many relationship, it is very difficult to find out responsibilities using particular concurrent program in UI forms.<br /><br />But we can get the list from database using the following SQL...<br /><br />SELECT<br /> FRS.responsibility_key<br />,FRS.responsibility_name<br />,FRG.request_group_name<br />,FCP.concurrent_program_name<br />,FCP.user_concurrent_program_name<br />FROM<br /> fnd_concurrent_programs_vl FCP<br />,fnd_request_group_units FRU<br />,fnd_request_groups FRG<br />,fnd_responsibility_vl FRS<br />WHERE FCP.concurrent_program_name = (Program_Name)<br />AND FRU.request_unit_id = FCP.concurrent_program_id<br />AND FRG.application_id = FRU.application_id<br />AND FRG.request_group_id = FRU.request_group_id<br />AND FRS.group_application_id = FRG.application_id<br />AND FRS.request_group_id = FRG.request_group_idRadhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com2tag:blogger.com,1999:blog-479295490235337133.post-77708703785892481792009-10-23T20:21:00.008-04:002011-03-07T13:56:58.957-05:00SQL to get list of Scheduled Concurrent Programs<p><span style="font-family:verdana;">Any Oracle Application (EBS) user can schedule Concurrent Programs, if he has access to it. Down the line, it is very difficult for users/administrators/developers to remember list of schedules concurrent programs. Following SQL will help to get the latest list of Scheduled Concurrent Programs.</span></p><p>SELECT FCR.request_id REQUEST_ID<br /> ,FCP.concurrent_program_name PROGRAM_SHORT_NAME<br /> ,FCP.user_concurrent_program_name PROGRAM_NAME<br /> ,FNU.user_name SUBMITTED_BY <br /> ,TO_CHAR(FCR.requested_start_date<br /> ,'DD-MON-YYYY HH24:MM:SS'<br /> ) REQUEST_START_DATE<br /> ,'Every '|| DECODE(LENGTH(FCL.class_info)<br /> ,39,FCL.class_info<br /> ,SUBSTR(FCL.class_info,1,INSTR(FCL.class_info,':',1)-1)||' '<br /> || DECODE(SUBSTR(FCL.class_info,INSTR(FCL.class_info,':',1)+1,1)<br /> ,'N','Minute(s) '<br /> ,'D','Day(s) '<br /> ,'H','Hour(s) '<br /> ,'M','Month(s) '<br /> )<br /> || 'after '<br /> || DECODE(SUBSTR(FCL.class_info,INSTR(FCL.class_info,':',1,2)+1,1)<br /> ,'S','Start '<br /> ,'C','Completion '<br /> )<br /> || 'of prior request'<br /> ) SCHEDULED_INTERVAL <br /> ,NVL(TO_CHAR(FCL.end_date_active<br /> ,'DD-MON-YYYY'),'forever'<br /> ) ENDING_ON<br />FROM APPS.fnd_concurrent_requests FCR<br /> ,APPS.fnd_concurrent_programs_vl FCP<br /> ,APPS.fnd_user FNU<br /> ,APPS.fnd_conc_release_classes FCL<br />WHERE FCR.phase_code = 'P' <br />AND FCR.status_code IN ('I','Q') <br />AND FCR.program_application_id = FCP.application_id <br />AND FCR.concurrent_program_id = FCP.concurrent_program_id <br />AND FCR.requested_by = FNU.user_id <br />AND FCR.release_class_app_id = FCL.application_id <br />AND FCR.release_class_id = FCL.release_class_id <br />ORDER BY FCP.concurrent_program_name<br /> , FCR.requested_start_date<br /></p>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com4tag:blogger.com,1999:blog-479295490235337133.post-86337788836771539152009-10-21T14:21:00.003-04:002011-03-07T13:58:00.358-05:00Submitting XML Publisher Report Concurrent Program from PL/SQL<span style="font-family:verdana;">XML Publisher template will be attached as layout when we submit concurrent program using request form based on the program short name. Whereas, if we submit the program using <em>FND_REQUEST.submit_request</em> API then we will find only XML output, template won’t be used.<br /></span><br /><span style="font-family:verdana;">To avoid this issue, program layout needs to be set using <em>FND_REQUEST.add_layout</em> API with template application, code, language, territory and output format before submit request.</span><br /><span style="font-family:verdana;"></span>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com2tag:blogger.com,1999:blog-479295490235337133.post-19161623278195707582009-09-30T22:11:00.004-04:002011-03-07T13:58:23.095-05:00Object Types in Integration PLSQL API using OA Adapter<span style="font-family:verdana;">If you are planning to use plsql API with plsql collection parameters for any integration using OA Adapter / DB Adapter then use Object Types rather than plsql collections (ie tables and records).<br /><br />Advantages are</span><br /><ol><li><span style="font-family:verdana;">Adapter can not use plsql tables and records directly. It will create similar database Object Type and use that in xsd creation, transformation etc. It also creates package to assign plsql collection into object types. This can be avoided if we use database object.</span></li><li><span style="font-family:verdana;">If there are any changes then just we need to change the object type and respective xsd. </span><span style="font-family:verdana;">If we use plsql tables and records then we have to make changes in too many places. Some time it creates major issues in particular if we have complex transformations and assignments.</span></li></ol>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com0tag:blogger.com,1999:blog-479295490235337133.post-20794180846007361242009-08-14T11:44:00.008-04:002011-03-07T14:00:45.566-05:00DBMS Change Notification<span style="font-family:verdana;">This is about DBMS Change Notification, enhanced feature introduced from version 10g.<br /><br />DBMS Change Notification can only be used for tables those are having length (including schema name) less than 30 characters. For example, Oracle Project module PA.PA_STD_BILL_RATE_SCHEDULES_ALL table length is more than 30, so this table can not be used for DBMS Change Notification.<br /></span><br /><span style="font-family:verdana;">Also DBMS Change Notification treats particular change as bulk if numbers of changed records are more than 80. In this case ROWID will be empty. So we won’t know the affected records.</span><br /><span style="font-family:Verdana;"></span>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com0tag:blogger.com,1999:blog-479295490235337133.post-90377455494090774612009-08-09T00:07:00.005-04:002009-08-09T00:17:00.818-04:00How BPEL OA Adapter connect to Oracle EBS?<p><span style="font-family:verdana;">In this article, I listed the steps to setup the connection factory for OA Adapter to connect to EBS. There are two major portions in this setup.</span></p><ol><li><span style="font-family:verdana;">JDBC Resource Setups</span></li><li><span style="font-family:verdana;">AppsAdapter (ie OA Adapter) Connection Factory Setups</span></li></ol><p><span style="font-family:verdana;">JDBC setups need to be completed first…</span></p><ol><li><span style="font-family:verdana;">Get Database TNS Entry and APPS schema password</span></li><li><span style="font-family:verdana;">Login as Application Server Enterprise Manager (eg: oc4jadmin) and click oc4j_soa (default OC4J Instance)</span></li><li><span style="font-family:verdana;">Go to <em>Administrator</em> tab and click <em>JDBC Resources</em> Go to Task icon</span></li><li><span style="font-family:verdana;">Create new Connection Pool by giving name and URL. Follow the URL syntax carefully</span></li><li><span style="font-family:verdana;">Enter Credential user as <em>APPS</em> and its password and then test the connection</span></li><li><span style="font-family:verdana;">If test connection is success then create Data Source.</span></li><li><span style="font-family:verdana;">Select Data Source Type as Managed Data Source. Enter data source name and JNDI location. JNDI Location can be start with <em>jdbc/NAME</em> to maintain the consistency.</span></li><li><span style="font-family:verdana;">Note down the JNDI Name, it will be used while defining Connection Factory.</span></li><li><span style="font-family:verdana;">Select the above created Connection Pool and test the connection.</span></li></ol><p><span style="font-family:verdana;">Now we can to use this JDBC Resource in OA Adapter Connection Factory Setups.</span></p><ol><li><span style="font-family:verdana;">Go to Applications tab in oc4j_soa OC4J Instance</span></li><li><span style="font-family:verdana;">Select view as <em>Modules</em> (Default is Applications)</span></li><li><span style="font-family:verdana;">Select <em>AppsAdapter</em> Module and go to Connection Factories tab</span></li><li><span style="font-family:verdana;">Create new Connection Factory. Give meaningful JNDI Location. It can start with <em>eis/Apps/NAME</em>. For example eis/Apps/EBS.</span></li><li><span style="font-family:verdana;">Enter xADataSourceName as JDBC JNDI Name just created. </span></li></ol><p><span style="font-family:verdana;">Now OA Adapter can be used in BPEL Process. Make sure same JNDI Name is entered when Partner Link is created for OA Adapter in JDeveloper.</span></p>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com2tag:blogger.com,1999:blog-479295490235337133.post-91040241983842786452009-08-04T23:26:00.004-04:002009-08-04T23:32:28.402-04:00Java Stored Procedure in Oracle<span style="font-family:verdana;">Though we can write business logic, data validation, exception handling in pl/sql, still we need to use languages like C, Pro*C, Java for complex and system programming in Oracle.<br /><br />Recently we had a requirement to convert the Images and store it into Oracle. As we know pl/sql won’t support graphics I used Java program for image conversion and used it in my plsql.<br /><br />Java program should have public method with required parameters and return statement if needed. For example, let as take addition of two numbers. Two numbers should be input parameters and the method should return sum of these two.<br /><br />Compile the java file and keep the class file in folder/directory registered in Oracle Database. We can create new directory if needed using following syntax.<br /><br /><span style="font-family:courier new;font-size:85%;">CREATE DIRECTORY Bfile_dir AS '/usr/temp';<br /></span><br />Store the class file into database using following syntax.<br /><br /><span style="font-family:courier new;font-size:85%;">CREATE OR REPLACE JAVA CLASS USING BFILE (Bfile_dir, 'Addition.class');<br /></span><br /><em>loadjava</em> executable can also be used instead of the above statement. Now java class is available to use. Create function/package to use in pl/sql.<br /><br /><span style="font-family:courier new;font-size:85%;">CREATE OR REPLACE FUNCTION add_numbers(no1 NUMBER, no2 NUMBER) RETURN NUMBER AS<br />LANGUAGE JAVA NAME 'Addition.get(int,int) return int';<br /></span><br />I used Oracle 10g Release 2. Refer <em>Oracle Database Java Developer’s Guide</em> for more details.</span>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com2tag:blogger.com,1999:blog-479295490235337133.post-15357539852458185012009-08-03T15:12:00.003-04:002009-08-03T22:10:37.909-04:00Single BPEL Process for Multiple Operations using Pick Activity<p><span style="font-family:verdana;">Usually we will create individual BPEL Processes for each Operation in either synchronous or asynchronous. This blog will help to build single BPEL Process for more than one Operation for exceptional cases like requesting system might have restricted number of adapters or ports to connect to other systems. This can be done using <em>Pick</em> activity.</span></p><ol><li><span style="font-family:verdana;">Get target system WSDL file</span></li><li><span style="font-family:verdana;">Create Empty Project</span></li><li><span style="font-family:verdana;">Create Partner Link for the WSDL file</span></li><li><span style="font-family:verdana;">Select Pick Activity and drag and drop into Process</span></li><li><span style="font-family:verdana;">Don’t forget to select <em>Create Instance</em> check box in Pick activity</span></li><li><span style="font-family:verdana;">Add <em>OnMessage</em> Branch as much as the number of Operations as you have</span></li><li><span style="font-family:verdana;">Go to any one OnMessage. Select Partner Link created, select the Operation and create Local Request Variable.</span></li><li><span style="font-family:verdana;">Add activities and partner links you wanted for the selected operation<br />Finally add Reply/Callback activity based on type of Operation.</span></li><li><span style="font-family:verdana;">Repeat this for all remaining Operations</span></li><li><span style="font-family:verdana;">Deploy into BPEL Server</span></li><li><span style="font-family:verdana;">Initiate the process from where ever you want. Note that this process can not be initiated from 10g BPEL Console. May be this is a bug.</span></li></ol><p><span style="font-family:Verdana;"></span></p>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com2tag:blogger.com,1999:blog-479295490235337133.post-49416292587209195642009-07-30T15:24:00.007-04:002009-07-30T17:21:56.782-04:00Validate & Get Values from XML using PL/SQLThere are various methods available in PLSQL to read, write and validate XML. Following sample code has simple approach to read, validate and get element value from XML using <em>DBMS_XMLDOM</em> API. This sample will read employee name and print it in the console.<br /><br /><span style="font-family:courier new;font-size:85%;">DECLARE<br />lc_return_msg VARCHAR2(1000);</span><br /><span style="font-family:courier new;font-size:85%;">lc_xml_buf VARCHAR2(2000);</span><br /><span style="font-family:courier new;font-size:85%;">lc_xml_value VARCHAR2(2000);</span><br /><span style="font-family:courier new;font-size:85%;">lc_resp_xml XMLType;<br />lc_xml_doc DBMS_XMLDOM.DOMDocument;</span><br /><span style="font-family:courier new;font-size:85%;">lc_xml_ndoc DBMS_XMLDOM.DOMNode;</span><br /><span style="font-family:courier new;font-size:85%;">lc_xml_docelem DBMS_XMLDOM.DOMElement;</span><br /><span style="font-family:courier new;font-size:85%;">lc_xml_node DBMS_XMLDOM.DOMNode;</span><br /><span style="font-family:courier new;font-size:85%;">lc_xml_childnode DBMS_XMLDOM.DOMNode;</span><br /><span style="font-family:courier new;font-size:85%;">lc_xml_nodelist DBMS_XMLDOM.DOMNodelist;<br />BEGIN<br /></span><span style="font-family:courier new;font-size:85%;">-- XML Message</span><br /><span style="font-family:courier new;font-size:85%;">lc_return_msg := '<?xml version="1.0" encoding="utf-8"?><br /><Department><br /><Code>001</Code><br /><Name>Sales</Name><br /><Employees><br /><Employee><br /><FirstName>Scott</FirstName><br /><LastName>Tiger</LastName><br /><DoB>01/01/1975</DoB><br /></Employee><br /><Employee><br /><FirstName>Adam</FirstName><br /><LastName>Ford</LastName><br /><DoB>12/03/1983</DoB><br /></Employee><br /></Employees><br /></Department>';</span><br /><span style="font-family:courier new;font-size:85%;"></span><br /><span style="font-family:courier new;font-size:85%;">-- Validate XML</span><br /><span style="font-family:courier new;font-size:85%;">lc_resp_xml := XMLType(lc_return_msg);<br /></span><br /><span style="font-family:courier new;font-size:85%;">-- Create DOMDocument Handle</span><br /><span style="font-family:courier new;font-size:85%;">lc_xml_doc := DBMS_XMLDOM.newDOMDocument(lc_resp_xml);</span><br /><span style="font-family:courier new;font-size:85%;">lc_xml_ndoc := DBMS_XMLDOM.makeNode(lc_xml_doc);<br /></span><br /><span style="font-family:courier new;font-size:85%;">DBMS_XMLDOM.writeToBuffer(lc_xml_ndoc, lc_xml_buf);<br /></span><br /><span style="font-family:courier new;font-size:85%;">-- Get all elements</span><br /><span style="font-family:courier new;font-size:85%;">lc_xml_docelem := DBMS_XMLDOM.getDocumentElement(lc_xml_doc);<br /></span><br /><span style="font-family:courier new;font-size:85%;">-- Get Result Elemement</span><br /><span style="font-family:courier new;font-size:85%;">lc_xml_nodelist := DBMS_XMLDOM.getElementsByTagName(lc_xml_docelem, 'FirstName');</span><br /><span style="font-family:courier new;font-size:85%;"></span><br /><span style="font-family:courier new;font-size:85%;">-- Get Second Employee Name</span><br /><span style="font-family:courier new;font-size:85%;">lc_xml_node := DBMS_XMLDOM.item(lc_xml_nodelist, 1);</span><br /><span style="font-family:courier new;font-size:85%;">lc_xml_childnode := DBMS_XMLDOM.getFirstChild(lc_xml_node);<br />lc_xml_value := DBMS_XMLDOM.getNodeValue(lc_xml_childnode);<br /></span><br /><span style="font-family:courier new;font-size:85%;">-- Print second employee name</span><br /><span style="font-family:courier new;font-size:85%;">DBMS_OUTPUT.put_line(lc_xml_value);<br />END;</span><br /><br /><span style="font-family:Courier New;font-size:85%;"></span>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com1tag:blogger.com,1999:blog-479295490235337133.post-48110929253649054572009-07-30T11:36:00.013-04:002009-07-30T12:12:44.221-04:00APPS Context in BPEL OA Adapter<span style="font-family:verdana;">Oracle Application Adapter (OA Adapter) connects to EBS Database as user <em>SYSADMIN</em> and responsibility <em>System Administrator</em>. This needs to be changed for different reasons like Security, Using Multi Operating Units.<br /><br />If user and responsibility are know and won’t change then this can be hardcoded in OA Adapter Partner Link Created. Search for element <em>Responsibility</em>, change the Username, Responsibility and save the file.<br /><br />If Contexts are dynamic then follow these steps:</span><br /><ol><li><span style="font-family:verdana;">Create new Message Type Variable for <em>Header_msg</em> in <em>AppsContextHeader.wsdl</em> file.</span></li><li><span style="font-family:verdana;">Assign Username, Responsibility & ORG_ID variables</span></li><li><span style="font-family:verdana;">Go to <em>Adapters</em> tab in OA Adapter Partner Link Invoke activity and Select the above created new variable as Input Header Variable.</span></li></ol><p></p>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com4tag:blogger.com,1999:blog-479295490235337133.post-5751603724578167762008-12-19T10:51:00.008-05:002009-07-30T12:26:38.671-04:00Steps to activate User Preferred Time Zone Support<p><span style="font-family:verdana;">From release 11.5.10 CU2, EBS introduced new feature called User Preferred Time Zone Support. Ie each user can specify their time zone preference, and the system will honor this preference for display and entry of date with time fields. Before 11.5.10 CU2, end users had to interact with the system in time zone set at database level.</span></p><p><span style="font-family:verdana;">To activate this feature following steps to be true or setup properly.</span></p><span style="font-family:Verdana;"></span><span style="font-family:verdana;"><ul><li>Database should be Oracle RDBMS 9i or higher</li><li>Environment variable ORA_TZFILE must be configured to use the time zone file timezlrg.dat rather than timezone.dat (Note: database needs to be restarted if this environment is changed). </li><li>The database must be started in the standard corporate time zone</li><li>Profile <em>Server Timezone</em> (SERVER_TIMEZONE_ID) must be set at the Site level, and must be set to the same standard corporate time zone as the database</li><li>Profile <em>Client Timezone</em> (CLIENT_TIMEZONE_ID) must be set at the user level</li><li>Profile <em>Enable Timezone Conversions </em>(ENABLE_TIMEZONE_CONVERSIONS) must be set to <em>Yes</em> at the Site level</li><li>Profile <em>Concurrent: Multiple Time Zones</em> (CONC_MULTI_TZ) should be set to <em>No</em> at the Site level</li><li>Environment variable <em>FORMS60_APPSLIBS</em> must be set in the Forms tier with APPS standards libraries like APPCORE FNDSQF APPDAYPK...</span></li></ul><p><span style="font-family:verdana;">For more details, refer <em>User Preferred Time Zone Support Documents</em> and metalink <em>notes 330075.1 & 340512.1</em>.</span></p>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com1tag:blogger.com,1999:blog-479295490235337133.post-58555456558580116652008-12-12T14:50:00.014-05:002009-07-30T12:26:55.428-04:00XML Publisher Report: Few Tips<span style="font-family:verdana;">Here are few useful tips to design XML Publisher RTF Templates.<br /><br /><strong>1. Printing Report Parameters</strong><br />In report, we need to print Report Parameters and might need to use it multiple times. To declare the parameter, create new field at beginning of the report with help text <em><xsl:param name="<ParameterName>" xdofo:ctx="begin"/></em></span><a name="Text283"><xsl:param name=""><span style="font-family:verdana;"><span style="color:#000000;">. </span><span style="color:#000000;">Create one more new field with help text <em><?$new_field?></em> <?$new_variable?><?$newvariable?>to print wherever parameter needs to be printed.</span></span></a></xsl:param><span style="font-family:verdana;"><span style="color:#000000;"><br /></span><br />For example, assume P_EMPLOYEE_NAME is one of the report parameter. To use that in RTF, create new filed named P_EMP_NAME with help text <em><xsl:param name="P_EMPLOYEE_NAME" xdofo:ctx="begin"/></em></span><?xml:namespace prefix = xsl /><xsl:param name="P_EMPLOYEE_NAME" ctx="begin"><span style="font-family:verdana;">. To print this parameter value create one more new field with name <em><?$P_EMP_NAME?></em> as help text.<?$P_EMP_NAME?><br /><br /></span><span style="font-family:verdana;"><strong>2. Fit into single page<br /></strong>Oracle Report Layout has “Fit to Page” property. If we set this to “Yes” then if the page is not enough to fit the content then entire content will be printed in next page. This is known and wildly used property. Is this possible in RTF Template?<br /><br />Yes. First identify contents that need to fit into a page. Insert a table with only one row and column. Keep the identified contents inside this table. Select Table Property. Navigate to “Row” tab. Uncheck “Allow Row to break across Pages’ check box if it is checked. This will make sure the contents won’t split across pages.<br /><br /><strong>3. Font Support</strong><br />You might have noticed some time Report Output won’t match with RFT Template Font. This is because PDF Engine will support only specific fonts. If you use fonts that are not supported by PDF Engine then engine will use it is default font. So try to use fonts that are PDF engine supports.<br /><br />Some time we have to use specific fonts that PDF engine didn’t support. Best example is company name, logo etc. Mostly we use these in report headers. Simple workaround is copy the logo or name or any text that we want it in specific font. Convert into to image and use that image wherever we need. Of course it will affect the performance if we use too many images in a template.</span></xsl:param>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com2tag:blogger.com,1999:blog-479295490235337133.post-789167231474824692008-10-10T14:02:00.009-04:002009-07-30T12:27:08.113-04:00Dynamic Workflow Notification Attachments<span style="font-family:verdana;">We know Workflow Notification can have attachments also. This is very useful feature and widely used in real-time. Big limitation in this method is if we know the number of attachments then this approach is easy to implement. Otherwise if we do not know the number attachments needs to attach, it is very difficult to use this approach. For example one PO can have multiple attachments. If the requirement is to include all of them in PO Approval notification then it is difficult using this approach.<br /><br />One of the workaround could be listing all the attachments in the notification body (rather than as attachment) with dynamic hyperlink to the attached documents. This is can be done either using PLSQL or OA Framework document type.<br /><br /><strong>Example: </strong><br />Create simple workflow with message and notification. This workflow can have user_id, resp_id, resp_appl_id attributes. This will be used to set application context.<br /><br />Create Document Type attribute to generate notification body.<br /><br />In the PLSQL API which is used to generate notification body:<br />Initialize Application Context using available attribute values in workflow<br /><br />Frame SQL query to get documents that needs to attach from fnd_lobs table.<br /><br />Use FND_GFM and FND_WEB_CONFIG APIs to get document URL.<br /><br />Write HTML TABLE tags and add File Name as one of the column with above hyperlink to the document. Use target as _blank to open the document in new browser.<br /><br />Notification body will be generated with all available attachment documents. Clicking the link will download the document in the browser.<br /><br /></span><span style="font-family:verdana;"><strong>Limitation:<br /></strong>1. If user preference is set to send notifications over e-mail then user should have access to the application when he try to access the attachments from e-mail.<br />2. Document should be available in EBS fnd_lobs table.</span>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com10tag:blogger.com,1999:blog-479295490235337133.post-25352219592660690612008-10-10T10:47:00.007-04:002009-07-30T12:27:26.716-04:00Oracle 11g Database New Features for Developers<ul><li><span style="font-family:verdana;">Table can be altered to Read Only or Read Write.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">A new XMLIndex_clause lets you create an XMLIndex index for XML data.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">In CREATE TABLE, A new virtual_column_definition create a virtual column.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">SELECT has new PIVOT syntax that rotates rows into columns. A new UNPIVOT operation to query data to rotate columns into rows.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">TRUNCATE statement was presented as a single statement with separate syntactic branches for TABLE and CLUSTER. That command has now been divided into TRUNCATE CLUSTER and TRUNCATE TABLE for consistency with other top-level SQL statements.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Enhancements to Regular Expression (REGEXP_INSTR, REGEXP_COUNT and REGEXP_SUBSTR) Built-in SQL Functions.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">The CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration (in contrast with the EXIT statement, which exits a loop and transfers control to the end of the loop).</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Allow Sequences in PL/SQL Expressions. PL/SQL now recognizes the SQL pseudo columns CURRVAL, LEVEL, NEXTVAL, ROWID, and ROWNUM.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Both native dynamic SQL and the DBMS_SQL package have been enhanced and support dynamic SQL statement larger than 32KB by allowing it to be a CLOB.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Named and Mixed Notation in PL/SQL Subprogram Invocations are allowed.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">PL/SQL provides a function result cache. To use it, use the RESULT_CACHE clause in each PL/SQL function whose results you want cached. Because the function result cache is stored in a shared global area (SGA), it is available to any session that runs your application.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">PL/Scope is a compiler-driven tool that collects and organizes data about user-defined identifiers from PL/SQL source code.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Subprogram Inlining replaces a subprogram call (to a subprogram in the same PL/SQL unit) with a copy of the called subprogram.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Oracle Application Express provides an application development tool that is built into the database and is installed in the database by default.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Create Rules and Actions for DML Events With Rules Manager. In 10g only INSERT is supported.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Rules Manager now includes a PL/SQL API to store reusable and shareable rule conditions in SQL WHERE clause format.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Unicode 5.0 Support</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">New Compound Trigger Type: Compound trigger has a section for each of the BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, and AFTER STATEMENT timing points. All of these sections can access a common PL/SQL state.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Client-Side Query Cache feature enables caching of query result sets in client memory. The cached result set data is transparently kept consistent with any changes done on the server side.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Enable the XDB HTTP Server for SOA feature allows Oracle Database to be treated as simply another service provider in a service-oriented architecture (SOA) environment.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Automatic SQL Tuning with Self-Learning Capabilities: Automatically detects high-load SQL statements and then tunes them automatically as needed in the maintenance window by creating appropriate SQL Profiles. It also issues proactive advice to create new access structures such as indexes that will significantly improve the performance of the high-load SQL statements.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Database Resident Connection Pool (DRCP) reduces the resource requirements of applications that currently don't support connection pooling, either because it is not supported by the application infrastructure, or it has not been implemented.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Online table redefinitions no longer invalidate dependent objects (PL/SQL, views, synonyms etc. expect triggers), provided the redefinition does not logically affect them.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Oracle 11g has a new view called DBA_TEMP_FREE_SPACE that displays information about temporary tablespace usage.</span></li></ul><p><span style="font-family:verdana;">Refer </span><a href="http://www.oracle.com/pls/db111/homepage"><span style="font-family:verdana;">Oracle 11g Database Documents</span></a><span style="font-family:verdana;"> to learn more and to find examles </span></p>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com2tag:blogger.com,1999:blog-479295490235337133.post-50231976015612096312008-10-09T16:17:00.010-04:002009-07-30T12:27:39.295-04:00Oracle 10g Database New Features for Developers<ul><li><span style="font-family:verdana;">This version of the Oracle Database has been designed to meet two key goals: Reducing the cost of manageability and Delivering increased performance for all key workloads.</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Major new high-availability features are also provided, including new Flashback capabilities (including new Flashback Database and Flashback Table) that reduce the downtime caused by human errors. In addition, support for rolling upgrades has been provided to reduce the downtime associated with database and application upgrades.</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Application development improvements include a new built-in application development environment, support for a high-performance and highly integrated XML capability across the entire technology stack, and a framework provided with the database that enables desktop and middle-tier applications to retrieve and extract data from the database using standard Web Services mechanisms. </span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Automatic Storage Management (ASM) automates and simplifies the optimal layout of datafiles, control files, and log files. Database files are automatically distributed across all available disks, and database storage is rebalanced whenever the storage configuration changes. </span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Automatic Workload Repository (AWR) is the latest evolution of statspack which provides the central information store for all Oracle 10g self-tuning functionality. </span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Rename already existing tablespace. No longer have to create a new tablespace, copy the contents from the old tablespace, and drop the old tablespace. </span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Automatic Shared Memory Tuning automates the configuration of System Global Area (SGA) memory-related parameters (buffer cache, shared pool) through self-tuning algorithms. </span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">SQL Tuning Advisor is a new server tool that eliminates manual tuning of SQL statements as an input and gives advice in the form of precise SQL actions for tuning the SQL along with their expected performance benefit. </span></li><li><span style="font-family:verdana;">All of the DML statements (INSERT, UPDATE, DELETE, MERGE) now have an error logging clause.</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Conditional Compilation: Enables to selectively include code depending on the values of the conditions evaluated during compilation. Also useful when you want to execute debugging procedures in a development environment, but want to turn off in other instances.</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Relaxation of Line Length and Overall Limits for the DBMS_OUTPUT PL/SQL Package</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Rules Manager is a new feature of Oracle Database 10g Release 2. It enables developers to create applications that process and respond to events of any complexity using rules and policies defined in the database.</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Database Change Notification: Receive notification whenever a change occurs in the database on the result set of registered queries. This feature enables any cache or object holding query results to ensure that it contains the very latest data.</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Enhancements in Spatial and Multimedia data management</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">New top-level SQL statements have been added to support Automatic Storage Management</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">New syntax in ALTER SYSTEM that lets you flushes the buffer cache of the system global area (SGA)</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">New syntax in ALTER TABLE that lets you manually compact the table segment, adjust the high water mark, and free the recuperated space</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">New category of collection functions lets you manipulate nested tables and varrays like SET,CARDINALITY,POWERMULTISET</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">New set of aggregate functions to support statistical analysis of data</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Added hierarchical pseudo columns CONNECT_BY_ISLEAF and CONNECT_BY_ISCYCLE for better usage</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">LOB column can be used in UPDATE clause when creating an update DML trigger</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">New locale-independent format elements (in particular for Number and Date Formats) have been added to the tables</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">New Conditions for nested tables like IS A SET, IS ANY, IS EMPTY, IS PRESENT, MEMBER</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">FORALL statement can handle associative arrays and nested tables with deleted elements</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Oracle can issue warnings when you compile subprograms that produce ambiguous results or use inefficient constructs. PLSQL_WARNINGS initialization parameter is controlling this.</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Supports Unicode 4.0</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">DBMS_FILE_TRANSFER new API to make binary copies of files on the local server or to transfer files between the local server and remote servers</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">MERGE statement enhancement to select rows from one or more sources for update or insertion into a table or view</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Introduces the new IEEE floating-point types BINARY_FLOAT and BINARY_DOUBLE</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">UTL_COMPRESS package provides an API to allow compression and decompression of binary data (RAW, BLOB and BFILE)</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">UTL_MAIL package provides a simple API to allow email to be sent from PL/SQL. In prior versions this was possible using the UTL_SMTP package, but this required knowledge of the SMTP protocol.</span></li></ul><p></p><p><span style="font-family:verdana;">Refer </span><a href="http://www.oracle.com/technology/documentation/database10gr2.html"><span style="font-family:verdana;">Oracle 10g Database Documents</span></a><span style="font-family:verdana;"> to learn more and to find examles.</span></p>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com2tag:blogger.com,1999:blog-479295490235337133.post-42381604082805025502008-10-09T14:05:00.014-04:002009-07-30T12:27:50.251-04:00Oracle 9i Database New Features for Developers<ul><li><span style="font-family:verdana;">New SQL enhancements to bring Oracle in line with the ANSI/ISO SQL</span></li></ul><p><span style="font-family:Verdana;"></span></p><ul><li><span style="font-family:verdana;">Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. This functionality allows comparative reporting over time and recovery from logical corruptions.</span></li></ul><p></p><ul><li><span style="font-family:verdana;">Declarative primary key, unique key and foreign key constraints can now be defined against views</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">Multitable Inserts concept can be used single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables.</span></li></ul><p></p><ul><li><span style="font-family:verdana;">Allows the renaming of table columns and constraints</span></li></ul><p></p><ul><li><span style="font-family:verdana;">Insert into or update a SQL table by specifying a PL/SQL record variable, rather than specifying each record attribute separately. Also select entire rows into a PL/SQL table of records, rather than using a separate PL/SQL table for each SQL column.</span></li></ul><p></p><ul><li><span style="font-family:verdana;">Create collections that are indexed by VARCHAR2 values</span></li></ul><p></p><ul><li><span style="font-family:verdana;">UTL_FILE contains several new functions that useful to perform general file-management operations from PL/SQL</span></li></ul><p></p><ul><li><span style="font-family:verdana;">PL/SQL supports the complete range of syntax for SQL statements, such as INSERT, UPDATE, DELETE, and so on. If received errors for valid SQL syntax in PL/SQL programs before, those statements should now work.</span></li></ul><p></p><ul><li><span style="font-family:verdana;">The new data type TIMESTAMP records time values including fractional seconds. New data types TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE allow adjusting date and timing values to account for time zone differences.</span></li></ul><p></p><ul><li><span style="font-family:verdana;">Perform bulk SQL operations, such as bulk fetches, using native dynamic SQL (the EXECUTE IMMEDIATE statement)</span></li></ul><p></p><ul><li><span style="font-family:verdana;">MERGE Statement: This specialized statement combines insert and update into a single operation. It is intended for data warehousing applications that perform particular patterns of inserts and updates.</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">S</span><span style="font-family:verdana;">ELECT can have multiple groupings in the GROUP BY clause, for selective analysis across multiple dimensions.</span></li></ul><p></p><ul><li><span style="font-family:verdana;">Enhancements in Oracle Text formerly interMedia Text uses standard SQL to index, search, and analyze text and documents stored in the Oracle database.</span></li></ul><p></p><ul><li><span style="font-family:verdana;">Includes Oracle XML DB, which is a set of built-in high-performance storage and retrieval technologies geared toward XML</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">S</span><span style="font-family:verdana;">upports JDK 1.3</span></li></ul><p><span style="font-family:verdana;"></span></p><ul><li><span style="font-family:verdana;">F</span><span style="font-family:verdana;">ull support for ANSI-style CASE statements and expressions</span></li></ul><p></p><ul><li><span style="font-family:verdana;">Custom aggregate functions can be defined for working with complex data</span></li></ul><p></p><ul><li><span style="font-family:verdana;">Oracle Ultra Search is able both to search the contents of a database to find documents, newspaper articles, and other information stored inside a database and to search the contents of static HTML pages.</span></li></ul><p></p><ul><li><span style="font-family:verdana;">Oracle Spatial Enhancements: Location capabilities in Oracle9i and Oracle Spatial have been greatly enhanced. Content stored in Oracle9i can now be associated with related location criteria and services. New support for online mapping, yellow pages, driving directions, traffic, and geocoding services allow online content to be merged with database content.</span></li></ul><p></p><p><span style="font-family:Verdana;">Refer <a href="http://www.oracle.com/technology/documentation/oracle9i.html">Oracle 9i Database Documents</a> to learn more and to find examles.</span></p>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com0tag:blogger.com,1999:blog-479295490235337133.post-85161777122212373212008-06-02T14:16:00.006-04:002009-07-30T12:28:07.105-04:00JDeveloper Patch with OA Framework ExtensionWe know JDeveloper is used for OA Framework (OAF) extensions and customizations for self service forms. This note gives the instruction on how to find right JDeveloper patch for creating OA Extensions with EBS Release 11i and 12.<br /><br />EBS has lots of versions like 11.5.9, 11.5.10, 12 etc. Developer should use the correct version of JDeveloper with OAF extension. When you create extensions or customize EBS OA Framework pages, you must use the version of JDeveloper shipped by the EBS product team. The version of JDeveloper is specific to the ATG patch level, so there is a new version of JDeveloper for each release of ATG patch-set.<br /><br />To determine which patch to use, you can check the framework version in your instance by using <strong>http://(host):(port)<host><port>/OA_HTML/OAInfo.jsp</strong>, then choose the matched JDeveloper patch.<br /><br />For Example if the instance base URL is http://vis.oracle.com:8080/ then type http://vis.oracle.com:8080/OA_HTML/OAInfo.jsp. Note down OA Framework Version. Go to <strong>Metalink Note 416708.1</strong>, find out the JDeveloper patch matching with OAF version and download. Unzip the downloaded file.<br /><br />We can also find useful informations in metalink.oracle.com.Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com2tag:blogger.com,1999:blog-479295490235337133.post-62297834663825319652008-03-19T14:07:00.011-04:002009-07-30T12:29:18.192-04:00Workflow Notification using OA Framework<p>If the notification contents are simple and static then Message body is used to build. Otherwise mostly PLSQL Document Type Attributes are used to build complex and dynamic contents. It is easy to develop. But it has few limitations like </p><ul><li>Look and Feel won’t be like Self Service Pages</li><li>Very difficult to format</li><li>Maintenance is costly</li><li>APIs (PL/SQL Web Toolkit) used won’t be available from R12 </li></ul><p>To avoid these, the notification body can be built using OA Framework. Same document type attribute can be used with small changes in the syntax like</p><p><em>JSP:/OA_HTML/OA.jsp?region=<OAFRegion>&<parameters></em></p><p>Advantages of using this are</p><ul><li>Uniform Look and Feel across all self service pages</li><li>Framework will format the contents</li><li>OA Framework Personalization and Extension can be used for any modifications</li><li>Much better performance than PLSQL</li><li>Upgrade Safe</li></ul><p>In fact Oracle is advising to use OAF to build notification contents. Developer should follow few important steps to use OA Regions in Workflow Notification. Refer Oracle Application Workflow Guide for more details.</p>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com1tag:blogger.com,1999:blog-479295490235337133.post-13322581433512253242008-03-12T15:59:00.018-04:002009-07-30T12:29:32.647-04:00Export from APPSIn Apps existing export function can be used to export the information in the current block. The following solution can be used to export not only from the specific block, can be used to based the information available in the form or not available in the form.<br /><br /><strong>Approach:<br /></strong>- Build a new package using few web toolkit APIs (refer the sample below)<br />- This package has to be registered as Web Plsql to access as web toolkit.<br />- Create new function to call the above package as type "SSWA plsql function" and HTML Call as above API.<br />- Execute the created new function from wherever export needed. It can be from button in a form or can be a special menu<br /><br /><strong>Sample API:<br /></strong>CREATE OR REPLACE PACKAGE BODY EXPORT IS<br /><br />PROCEDURE main (<br />p_user_id NUMBER<br />) IS<br /><br />lc_line VARCHAR2(4000);<br />lc_mime_type VARCHAR2(100) := 'text/csv';<br />lc_char_set VARCHAR2(100) := NULL;<br />ln_length NUMBER := 0;<br />lc_blob BLOB;<br />lc_new_line VARCHAR2(10) := CHR(10);<br /><br />CURSOR lcu_users IS<br />SELECT user_name<br />,start_date<br />,end_date<br />,email_address<br />FROM fnd_user<br />WHERE user_id = p_user_id;<br /><br />BEGIN<br /><br />FND_GLOBAL.apps_initialize(FND_PROFILE.value('USER_ID')<br />,FND_PROFILE.value('RESP_ID')<br />,FND_PROFILE.value('RESP_APPL_ID')<br />);<br /><br />DBMS_LOB.CreateTemporary(lc_blob, TRUE, DBMS_LOB.SESSION);<br /><br />FOR lr_users IN lcu_users<br />LOOP<br /><br />lc_line := lr_users.user_name ','<br />lr_users.start_date ','<br />lr_users.end_date ','<br />lr_users.email_address lc_new_line;<br /><br />-- Covert into RAW<br />lc_line := utl_raw.cast_to_raw(lc_line);<br />ln_length := utl_raw.length(lc_line);<br /><br />-- Write the data into BLOB<br />DBMS_LOB.WriteAppend(lc_blob, ln_length, lc_line);<br /><br />END LOOP;<br /><br />-- Set Mime Type<br />OWA_UTIL.mime_header(lc_mime_type, FALSE, lc_char_set);<br />htp.p( 'Content-length: ' DBMS_LOB.getlength(lc_blob));<br />OWA_UTIL.http_header_close;<br /><br />-- Download it as CSV<br />WPG_DOCLOAD.download_file(lc_blob);<br /><br />EXCEPTION<br />WHEN OTHERS THEN<br />HTP.htmlOpen;<br />HTP.headOpen;<br />HTP.title('404 Not Found');<br />HTP.headClose;<br />HTP.bodyOpen;<br />HTP.hr;<br />HTP.header(nsize=>1, cheader=>SQLERRM);<br />HTP.hr;<br />HTP.p(FND_MESSAGE.Get_String('GMD','LM_BAD_FILENAME'));<br />HTP.bodyClose;<br />HTP.htmlClose;<br />END main;<br />END EXPORT;Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com2tag:blogger.com,1999:blog-479295490235337133.post-57537907894577798592007-11-19T16:02:00.000-05:002007-11-19T16:18:33.494-05:00- Role vs Responsibility -<p>The Responsibility has been used not only to define the application navigation menus, but also to define privileges and permissions. Using this definition of responsibility, it is necessary to create several similar responsibilities in order to effectively carve out data and functional security access for a group of users.<br /><br />Going forward (from EBS 11.5.10 onwards), Users will no longer need to be directly assigned the lower level permissions and responsibilities, as these can be implicitly inherited based upon the roles assigned to the user. Roles can now be defined to consolidate responsibilities and other roles through role inheritance, as well as lower level permissions (functions) and data security policies. </p><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_CgWcgzF5dLMHN04KOhO5ZvDIUcHCV0x_xVpxvEmyqqkOC3b3J6IkiAFKc2m_N6vwcTMrkgXji5FC56-BakpEFUgkFG-MHbLq16A6Opwjtsejho3b9I0hIfbEdekQpEBUYHhCUOTXG9k/s1600-h/RBACFlow.JPG"><img id="BLOGGER_PHOTO_ID_5134661148649865618" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_CgWcgzF5dLMHN04KOhO5ZvDIUcHCV0x_xVpxvEmyqqkOC3b3J6IkiAFKc2m_N6vwcTMrkgXji5FC56-BakpEFUgkFG-MHbLq16A6Opwjtsejho3b9I0hIfbEdekQpEBUYHhCUOTXG9k/s320/RBACFlow.JPG" border="0" /></a><br /></p><p>The benefits of implementing Role Based Access Control (RBAC) are<br />- Structured user access control<br />- Reduced cost of administering user access control system<br />- Streamlined setup and implementation of security policies and rules</p><p>Refer System Administrator's Guide – Security document for more details.<br /></p>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com111tag:blogger.com,1999:blog-479295490235337133.post-43522027574255195912007-10-02T11:56:00.001-04:002008-12-03T16:38:59.257-05:00- XML Publisher Report in EBS -<p><span style="font-family:verdana;font-size:85%;">Oracle XML Publisher is a new Java based product available within the technology stack of the EBS. It provides users with a template-based, easy-to-use publishing solution based on standard well-known technologies and tools. So users can take advantage of it easily to rapidly develop and maintain report formats.<br /><br />This document helps for buddies who developing his first XML Publisher Report. Follow these steps to register your XML Publisher Report in EBS.</span></p><ol><li><span style="font-family:verdana;font-size:85%;">Attach "XML Publisher Administrator" responsibility to APPS User</span></li><li><span style="font-family:verdana;font-size:85%;">Develop simple report; register it in EBS like standard concurrent program. Make sure that the Output Format is set as 'XML'. Pls note that Oracle Reports will be obsolete in coming EBS versions. So it is advisable to use Data Templates rather than Oracle Reports. If you use Data Templates then no need to have Report Concurrent Program. Refer Developer’s guide for more details.</span></li><li><span style="font-family:verdana;font-size:85%;">Add this program to the responsibility; Run the report and download the concurrent program output (XML file) to desktop. This XML will be used as Sample for Template development.</span></li><li><span style="font-family:verdana;font-size:85%;">Download patch 5027437 (p5027437_11i_MSWIN.zip) from Metalink and install it in the Desktop OR identify the patch for EBS version that you have and install it.</span></li><li><span style="font-family:verdana;font-size:85%;">Open Ms Word. You can see new Template Builder toolbar</span></li><li><span style="font-family:verdana;font-size:85%;">Click 'Load XML Data' in Data menu. Give the XML output file that you downloaded from Report Concurrent Program</span></li><li><span style="font-family:verdana;font-size:85%;">Use Table/Form wizard to build template to build the layout</span></li><li><span style="font-family:verdana;font-size:85%;">Save the file in Rich Text Format (RTF)</span></li><li><span style="font-family:verdana;font-size:85%;">Preview the report output from MS Word.</span></li><li><span style="font-family:verdana;"><span style="font-size:85%;">Define new Data Definition (Navigation: XML Publisher Administrator - Data Definitions - Create Data Definition)<br />Enter Name, Application, Code, Preview Data (sample XML output file) and Start Date. <em>Make sure the Code and Report Concurrent Program Short Name are same</em></span></span></li><li><span style="font-family:verdana;font-size:85%;">Define new Template (Navigation: XML Publisher Administrator - Templates - Create Template)<br />- Enter Name, Code, Application, Type (make it RTF), Data Definition (select the one you created)<br />- Enter the File as the RFT file created<br />- Enter Language and Territory</span></li></ol><span style="font-family:verdana;"><p><span style="font-size:85%;">Now XML Publisher Report is registered in EBS and ready to function. This report can be executed in two ways</span></p><ul><li><span style="font-size:85%;">Run the Report concurrent program, note the request ID. Then run the XML Report Publisher concurrent program with the above request id and template as parameter</span></li><li><span style="font-size:85%;">Change Concurrent Program Format to XML and submit the request. Program will pickup the template abd generate the output.</span></li></ul><p><span style="font-size:85%;">Note that XML Publisher Report strategies and setups are kept on changing. So please refer <em>Oracle XML Publisher Administration and Developer's Guide</em> and <em>Oracle XML Publisher Report Designer's Guide</em> documents with respect to your EBS version if you face any problem to register/run the report.<br /><br /><strong>Reference</strong></span></p><ul><li><span style="font-size:85%;">Oracle XML Publisher Administration and Developer's Guide</span></li><li><span style="font-size:85%;">Oracle XML Publisher Report Designer's Guide</span></li><li></span><a href="http://forums.oracle.com/forums/forum.jspa?forumID=245"><span style="font-family:verdana;font-size:85%;">Oracle BI Publisher Discussion Forum</span></a></li></ul>Radhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com2tag:blogger.com,1999:blog-479295490235337133.post-85082657174028963692007-09-20T20:20:00.000-04:002007-10-14T22:47:41.828-04:00- Invoking BPEL Process using Pl/SQL -BPEL process can be invoked from Oracle database in different ways. Following sample can be used to invoke BPEL process using SOAP and pl/sql APIs.<br /><br />- Change Provider URL, WS name in the sample code given below<br />- This program uses only standard String Input parameter. Make the necessary changes if WSDL has different parameter(s).<br /><br />DECLARE<br /> lc_soap_request VARCHAR2(3000);<br /> lc_soap_respond VARCHAR2(3000);<br /><br /> lc_input VARCHAR2(5) := 11; -- Changes this as per BPEL Process (WSDL) definition<br /> lc_namespace VARCHAR2(128) := 'xmlns="http://xmlns.oracle.com/<WS Name>"'; -- Replace <WS Name> with BPEL Process<br /> lc_ws_request VARCHAR2(50) := <BPEL Process Request> --Get it from WSDL file<br /><br /> lc_http_req UTL_HTTP.req;<br /> lc_http_resp UTL_HTTP.resp;<br />BEGIN<br /><br /> lc_soap_request := '<?xml version = "1.0" encoding = "UTF-8"?>' <br /> ||'<SOAP-ENV:Envelope ' <br /> ||'xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" ' <br /> ||'xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" ' <br /> ||'xmlns:xsd="http://www.w3.org/1999/XMLSchema">' <br /> ||'<SOAP-ENV:Body>' <br /> ||'<'||lc_ws_request||' '||lc_namespace||'>'<br /> ||'<input '||lc_namespace||'>'||lc_input||'</input>'<br /> ||'</'||lc_ws_request||'>' <br /> ||'</SOAP-ENV:Body>' <br /> ||'</SOAP-ENV:Envelope>'; <br /><br /> lc_http_req := UTL_HTTP.begin_request(<br /> '<URL>' -- Get this from BPEL Processes->WSDL->Endpoint Location OR this can be get it from WS ESB definition tab<br /> ,'POST'<br /> ,'HTTP/1.1'<br /> );<br /><br /> UTL_HTTP.set_header (<br /> lc_http_req<br /> , 'Content-Type'<br /> , 'text/xml'<br /> );<br /><br /> UTL_HTTP.set_header (<br /> lc_http_req<br /> , 'Content-Length'<br /> , LENGTH(lc_soap_request)<br /> );<br /><br /> UTL_HTTP.set_header (<br /> lc_http_req<br /> , 'SOAPAction'<br /> , 'process'<br /> );<br /><br /> UTL_HTTP.write_text(lc_http_req, lc_soap_request);<br /><br /> lc_http_resp := UTL_HTTP.get_response(lc_http_req);<br /><br /> UTL_HTTP.read_text(lc_http_resp, lc_soap_respond);<br /><br /> UTL_HTTP.end_response(lc_http_resp);<br /><br /> DBMS_OUTPUT.put_line(substr(lc_soap_respond,1 ,250));<br /> DBMS_OUTPUT.put_line(substr(lc_soap_respond,251,500));<br /><br />END;<br /><br /><strong>Limitations:<br /></strong>- Exceptions/Fault handling should be controlled manually<br />- Program will just initiate the Web Service. Auditing or back tracking should be handled manually<br />- This approach might not be as per Oracle EBS standards. Please check EBS and BPEL documents for more information related to standardsRadhakrishnan Chithamalaihttp://www.blogger.com/profile/10040380917063937265noreply@blogger.com2