Become Oracle Apps Technical By || Kamlesh Nikhade

My Favourite Links

2010-02-24T23:17:00.000-08:00

OA Framework
===============
http://oracle.anilpassi.com/oa-framework.html
http://oracle.anilpassi.com/xmlimporter-in-oracle-applications-framework.html
http://apps2fusion.com/at/61-kv/317-oa-framework-page-without-login-guest-no-security
http://www.orafaq.com/wiki/JDeveloper
http://oracle.anilpassi.com/jdr-utils.html
http://oracle.anilpassi.com/oa-framework-tutorials-training.html
http://www.dulcian.com/papers/MAOP-AOTC/2002/Don'tBeAMuggle.htm
http://www.dulcian.com/papers/OracleOpenWorld/2002/What%20You%20Need%20to%20Know%20Before%20Building%20Applications%20with%20JDeveloper%209i.htm
http://apps2fusion.com/apps/oa-framework/14-fwk/151-oa-framework-screen-extension-by-embedding-a-custom-page
http://oracle.anilpassi.com/oa-framework-tutorial-01-2.html
http://oracle.anilpassi.com/oa-framework-table-based-screen-2.html
http://www.dbforums.com/oracle/1630066-jdeveloper-resolving-errors-encountered.html
http://appstechnical.blogspot.com/2007/01/oa-framework-tutorials.html
http://oraclearea51.com/oracle-technical-articles/oa-framework/oa-framework-beginners-guide/322-exporting-oa-page-definitions.html
http://oraclearea51.com/oracle-technical-articles/oa-framework/oa-framework-beginners-guide.html
http://www.oracle.com/technology/products/jdev/tips/muench/designpatterns/index.html
http://www.oracle.com/technology/pub/articles/vohra-jdev-xmlpub.html
http://mukx.blogspot.com/2010/01/upload-file-to-application-server-using.html
http://blog.cholaconsulting.com/search/label/OA%20Framework
http://sabersurge.com/oracleappsdev/index.php?option=com_content&view=article&id=54%3Afile-upload-to-database-server-oa-framework&catid=34%3Aoa-framework&Itemid=1
http://www.tier1inc.com/blog_comments.php?pid=12-----Comparing OA Framework with Forms 6i
http://oracle-applications-rama.blogspot.com/2009/01/how-to-search-apps-documents-in-google.html

Oracle Forms Web
Upload, edit and download files from/to the database with the
Webutil library
================================
http://sheikyerbouti.developpez.com/webutil-docs/Webutil_store_edit_docs.htm

Check Java Version
===============
http://java.com/en/download/installed.jsp?jre_version=1.6.0_07&vendor=Sun+Microsystems+Inc.&os=Windows+2000&os_version=5.0

Linux Commands
===============
http://www.ss64.com/bash/
http://teachmeoracle.com/unixa.html
http://www.nixblog.org/post/2008/03/14/UNIX-ID-ORACLE-SESSION
http://www.unix.com/shell-programming-scripting/84635-unix-script-detect-new-file-entry-directory.html

Register Shell Scripts As Concurrent Program
===================================
http://www.notesbit.com/index.php/scripts-oracle/oracle-applications-steps-to-register-shell-script-as-a-concurrent-program/

UTL_FILE_DIR
======================
http://oracleappstechnology.blogspot.com/2008/03/minimize-usage-of-utlfiledir.html

Oracle Applications
===============
http://becomeappsdba.blogspot.com/
http://www.ddbcinc.com/askDDBC/
http://beginapps.blogspot.com/2007_09_01_archive.html
http://knoworacle.wordpress.com/tag/apps-table/
http://appsdba4u.blogspot.com/2007/08/oracle-apps-dba-interview-questions.html
http://cnubandi.blogspot.com/
http://idbasolutions.com/category/papers/3480000-115102/3480000-final-run/
http://becomeappsdba.blogspot.com/2006/08/startup-shutdown-apps-services.html
http://oracleappss.blogspot.com/2008/07/supplier-additional-information.html
http://erp-consultancy.blogspot.com/2008/03/tds-flow-in-accounts-payable-oracle.html
http://etrm.oracle.com/license/MustLogin.html
http://oracle-magic.blogspot.com/2007/06/concurrent-request-and-its-database-sid.html
http://oracle-applications-rama.blogspot.com/
http://www.oracleappshub.com/11i/purchase-order-approval-hierarchy/
http://confluentminds.com/Trainings/SCM/Topic1.1_Ch1_Part4.html
http://forums.oracle.com/forums/thread.jspa?threadID=457983
http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/alr/multins.htm
http://oracle.ittoolbox.com/groups/technical-functional/oracle-apps-l/
http://www.aboutoracleapps.com/2007/07/oracle-purchasing-po-faq.html
http://forums.oracle.com/forums/thread.jspa?threadID=664806&tstart=0
http://oracle.anilpassi.com/technical-interview-questions-in-oracle-apps.html
http://www.oracleappshub.com/accounts-receivable/ar-back-to-basictechnical-foundation/
http://www.oracleappshub.com/aol/setting-default-to-excel-for-exported-file-from-file-export/
http://asoracle.blogspot.com/2007/11/key-tables-financials.html
http://oracle.ittoolbox.com/groups/technical-functional/oracle-apps-l/switch-responsibility-icon-on-toolbar-283079
http://oracle.anilpassi.com/forms-personalizations.html
http://www.erpschools.com/Oracle_Apps_Form_Customization.asp
http://www.erpschools.com/
http://realworldoracleapps.blogspot.com/search/label/Welcome%20Note
http://oracleappsrealworld.blogspot.com/
http://mastanreddy.blogspot.com/
http://www.scribd.com/doc/3256741/Oracle-Applications-Developers-Guide
http://garethroberts.blogspot.com/2007/10/excel-file-output-from-oracle.html
http://garethroberts.blogspot.com/2008/01/changing-default-layout-format-from-pdf.html
http://erpcrmapps.blogspot.com/2008/01/using-keyboard-shortcuts.html
http://www.hrmsaces.co.uk/ubbthreads.php/forums/8/1
http://bbs.erp100.com/archiver/tid-36506.html
http://edelivery.oracle.com/EPD/WelcomePage/get_form?ARU_LANG=US
http://oraclepitstop.wordpress.com/2007/04/18/versions-of-components-in-oracle-apps/
http://www.aboutoracleapps.com/2007/08/oracle-general-ledger.html
http://oracle.anilpassi.com/oracle-payroll-tables.html
http://confluentminds.com/Trainings/SCM/Topic2.3_Ch2_Part2.html
http://aksenthil.blogspot.com/
http://knoworacle.wordpress.com/category/oracle-applications-technical/oracle-fixed-assets-technical/
https://metalink.oracle.com/
http://solutionbeacon.blogspot.com/2007/07/simple-tutorial-for-publishing-fsg.html
http://sbllc3.solutionbeacon.net/pls/a159vis2/fndgfm/fnd_help.get/US@PSA_US/fnd/@e_cp
http://apps2fusion.com/apps/oracle-hrms/oracle-hr/hrms
http://oracle.anilpassi.com/creating-customer-address-in-tca-step-by-step.html
http://www.aboutoracleapps.com/2007/07/oracle-apps-manufacturingaolforms.html
http://forums.oracle.com/forums/thread.jspa?threadID=590547
(All In one Blog) http://www.sap-img.com/oracle-database/oracle-application-hints-and-tips.htm
http://www.dba-oracle.com/art_dbazine_conc_mgr.htm
http://dineshnair.wordpress.com/
http://oracle.anilpassi.com/basic-concepts-list-of-useful-oracle-apps-articles-2.html
http://chandramatta.blogspot.com/search/label/Concurrent%20Programs------Very Useful Blog for all Topics

Interfaces
===========
(AR Customer Interface Info.)
http://sbllc3.solutionbeacon.net/pls/a159vis2/fndgfm/fnd_help.get/US@PSA_US/ar/@n_tbl_val@PSA_US
http://download-west.oracle.com/docs/cd/A60725_05/html/comnls/us/ar/cusimprt.htm#n_cust_import
http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/ar/autoin05.htm#n_autoval
http://www.erpschools.com/apps/oracle-applications/Articles/General/Interfaces-and-Conversions-in-Oracle-Applications/index.aspx
(Payables)
http://sbllc3.solutionbeacon.net/pls/a159vis2/fndgfm/fnd_help.get/US@PSA_US/AP/@r_openaud@PSA_US
http://irep11i10.oracle.com/OA_HTML/OA.jsp?page=/oracle/apps/fnd/rep/webui/OpenInterfaceDetailsPG&CategoryValue=F&_ti=199580375&retainAM=Y&addBreadCrumb=Y&oapc=7&oas=biKg9_cvMvUQM4gNQIA6ug..
( Customer Interface Error Code Meaning )
http://sbllc3.solutionbeacon.net/pls/a159vis2/fndgfm/fnd_help.get/US@PSA_US/ar/@custerrs@PSA_US

FND LOAD
===========
http://oracle.anilpassi.com/fndload-for-oracle-web-adi.html

R12 Vision Instance Login
=======================
http://vis1200.solutionbeacon.net/OA_HTML/RF.jsp?function_id=1032924&resp_id=-1&resp_appl_id=-1&security_group_id=0&lang_code=US¶ms=gEzpj7eR1-rfQLgP8Ol8DQ3u3xBOeCmcdxx.JgrY94g&oas=-Q6TtBxoQEySwZJoZFr0Fw..

General
==============
http://www.httpsurf.com/
http://www.webindia123.com/history/index.html
http://download.oracle.com/docs/cd/A57673_01/DOC/api/doc/PC_22/ch02.htm
http://www.wapuser.co.cc/2009/12/how-to-hack-others-yahoo-password.html
http://www.osun.org/
http://oracle-applications-rama.blogspot.com/2009/01/how-to-search-apps-documents-in-google.html

Oracle Apps Data Structure
=======================
http://www.scribd.com/doc/404946/Oracle-Apps-Data-Structure

Oracle Apps Scripts
=================
http://www.erpschools.com/Oracle_Apps_Scripts.asp
http://www.erpschools.com/
http://scripts4oracle.blogspot.com/

Metalink Notes
===============
http://teachmeoracle.com/metalink.html
http://www.oracle.com/technology/tech/globalization/index.html

Orcale DB Concepts
===============
http://www.adp-gmbh.ch/ora/concepts/

Autoconfig
===============
http://becomeappsdba.blogspot.com/2006/10/autoconfig-in-apps-template-files.html
http://onlineappsdba.com/index.php/2008/01/28/autoconfig-in-oracle-apps-11i-r12-12i/

TroubleShooting Concurrent Managers
================
http://becomeappsdba.blogspot.com/2006/10/troubleshooting-concurrent-managers.html

NLS Language Parameters
===================
http://it.toolbox.com/wiki/index.php/ORA-12700#NLS_Parameters
http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm#_Toc110410543

Trace Concurrent Request and Generate TKPROF
=======================
http://knoworacle.wordpress.com/2008/06/27/tkprof-trace-a-program/

How to find Processes running in Sunsolaris Operating System
=================================================
http://www.unix.com/sun-solaris/25208-how-find-number-processes.html

Character Set
===================
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/ch11charsetmig.htm
http://download.oracle.com/docs/cd/B10500_01/server.920/a96529/ch2.htm#745
http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/applocaledata.htm#i634428
http://www.oracledba.co.uk/tips/character_set.htm

ASCII Characters
===================
http://www.alcyone.com/max/reference/compsci/ascii.html
http://www.oracle.com/technology/obe/obe9ir2/obe-nls/localbld/localbld.htm

Oracle SMS Getway Guide
=====================
http://www3.ozekisms.com/index.php?ow_page_number=166

Hindustan Times Corporate News
======================
http://www.hindustantimes.com/ListingPage/ListingPage.aspx?Category=Chunk-HT-UI-BusinessSectionPage-Corporate&SectionName=BusinessSectionPage&BC=CorporateNews

MS Word Mail Merge Tutorial
=======================
http://personal-computer-tutor.com/mailmerge.htm
http://www.frontpage2002.com/frontpage_2003_tutorial_guide.htm

How to start perticular Concurrent Manager
============================
http://teachmeoracle.com/forum/viewtopic.php?t=4320

Company Profile & Overview
==========================
http://info.shine.com/company/Infosys-Technologies-Ltd/102.aspx

Letter Generation using WEB-ADI
=============================
http://apps2fusion.com/at/38-ss/351-generate-recruitment-letters-web-adi

Oracle HRMS Fast Farmula Tutorial
=============================
http://www.aboutoracleapps.com/2008/11/oracle-hrms-fast-formula-tutorial.html
http://oracle.anilpassi.com/index.php?Itemid=4&id=117&option=com_content&show=1&task=view
http://www.erpschools.com/Apps/oracle-applications/articles/hrms/fast-formulas-in-hrms/index.aspx
http://www.aboutoracleapps.com/2009/01/how-to-generate-develop-or-edit.html

Oracle Workflow
========
http://oracleappstechnology.blogspot.com/2008/02/workflow-mails-not-moving-after-fresh.html
http://forums.oracle.com/forums/thread.jspa?messageID=2327979
http://apps2fusion.com/at/gt/tc/328-workflow-mailer-debugging-script-for-debugging-emails-issues
http://onlineappsdba.com/index.php/2008/07/16/oracle-workflow-notification-mailer-outbound-processing/
http://oracleebusinesssuite.wordpress.com/2008/10/18/debugging-the-approval-workflow-for-purchase-order-or-purchase-requisition/
http://oracle.anilpassi.com/workflows-business-events-training-lesson-4.html
http://oracleappstechnology.blogspot.com/2008/05/disable-retention-on-workflow-queues.html
http://www.freelists.org/post/ora-apps-dba/EXPIRED-messages-in-the-WF-NOTIFICATION-OUT-queue-THE-SOLUTION
https://csslxa03.hkcss.org.hk:16298/OA_HTML/oam/helpdoc/oam_wfoam_notificationmailerg.htm
http://arunrathod.blogspot.com/2008/08/workflow-wfload-through-unix.html
http://oracle.anilpassi.com/apps-technology/2.html
http://www.erpschools.com/Oracle_Applications_Workflow_Launching.asp
http://www.erpschools.com/Oracle_Applications_Workflow_Tutorial.asp
http://oracleebusinesssuite.wordpress.com/2008/10/18/debugging-the-approval-workflow-for-purchase-order-or-purchase-requisition/

AME
======
http://greenoracleapps.blogspot.com/

Make Packages Valid
====================
http://idbasolutions.com/3480000-115102-first-run/

DB Upgrade Context File Creation
=========================
http://forums.oracle.com/forums/thread.jspa?threadID=672921&tstart=0&messageID=2612978

Oracle PL/SQL SMS
===========================
http://forums.oracle.com/forums/thread.jspa?messageID=1827704
http://www.dbasupport.com/forums/archive/index.php/t-24763.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:839412906735
http://www.erpschools.com/Apps/oracle-applications/Interview-Questions/Tools/PL-SQL/default.aspx

Oracle Apps DBA
============================
http://www.teachmeoracle.com/imptables.html
http://www.dba-oracle.com/art_tr_dyn_sga.htm
http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/alr/multins.htm
http://blogs.oracle.com/gverma/2007/11/accrue_economically_dont_recal.html
http://becomeappsdba.blogspot.com/2007/01/change-oracle-apps-password-fndcpass.html
http://oracleappstechnology.blogspot.com/2007/07/query-to-find-out-your-atgpfh-rollup.html
http://onlineappsdba.blogspot.com/2008/06/personalizing-login-page.html
http://www.oracleutilities.com/
http://www.shutdownabort.com/
http://www.dbatoolz.com/sql-scripts
http://krackoracle.blogspot.com/2009_02_01_archive.html

Insert BOB Object (Image) in Database and Display In Report 6i
===============================================
http://sureshvaishya.blogspot.com/2009/08/insert-blob-image-file-into-database.html
http://oracleappsexperience.blogspot.com/

Tunning Oracle Reports
==========================
http://download.oracle.com/docs/cd/B10464_05/bi.904/b13673/pbr_tune.htm#i1007365

FSG Reports
=======================
http://www.oracleappshub.com/finance/fsg-what-is-it/

XML / BI Publisher
======================
http://www.oracleappshub.com/xml-publisher/migration-of-xmlpub-templates-and-data-definitions-across-instances/#more-1084
http://blogs.oracle.com/xmlpublisher/2007/05/howto_java_concurrent_programs.html
http://blogs.oracle.com/xmlpublisher/templates/templates_rtf/
http://asun.ifmo.ru/docs/XMLP/help/en_US/htmfiles/B25951_01/T421739T421827.htm

Delete Records from Table (OA Page )

2010-02-09T04:22:00.000-08:00

Step 1 :

Create method in application module as

public void deleteitemMethod(String pAction,String pEntityCode)
{

/*System.out.println("xxDebug Inside deleteItemMethod());*/
XxTest01ViewImpl pervo=getXxTest01View1();
System.out.println(pEntityCode);
Row row []=pervo.getAllRowsInRange();
for (int i=0;i {
XxTest01ViewRowImpl rowi=(XxTest01ViewRowImpl)row[i];

if (rowi.getEntityCode().toString().equals(pEntityCode))
{
rowi.remove();
getDBTransaction().commit();
return;

}
}
}

2) Call this method in your controller class's process form request as

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
String actionInMainPage = pageContext.getParameter(EVENT_PARAM);
String paramEntityCode = pageContext.getParameter("paramEntityCode");
if (actionInMainPage.equals("deleteitem"))
{
Serializable paramdelete []={actionInMainPage,paramEntityCode};
OAApplicationModule am =pageContext.getApplicationModule(webBean);
am.invokeMethod("deleteitemMethod",paramdelete);

}
}

Download File From Application Server (OA Framework)

2010-02-09T04:17:00.000-08:00

Download file from Application Server

1) Only Page and controller is different in download file, rest all is same as upload example given earlier.

2) Create one OA page in oracle.apps.download.webui.

3) SET form property to TRUE of main region , Create one region with region style

Message component layout., set AM Defination.

4) create one submit button in main region.

Set properties as below :
Action type =fireAction
Event=download
Prompt=submit

5) create one controller class in oracle.apps.download.webui.

import all below packages :

import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.cabo.ui.data.DataObject;
import java.io.FileOutputStream;
import java.io.InputStream;
import oracle.jbo.domain.BlobDomain;
import java.io.File;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletOutputStream;
import javax.servlet.*;
import oracle.apps.fnd.framework.OAException;
import java.io.*;

6) create two methods in controller class as below :-

public void downloadFileFromServer(OAPageContext pageContext,
String file_name_with_path,
String file_name_with_ext)
{
HttpServletResponse response =
(HttpServletResponse) pageContext.getRenderingContext().getServletResponse();
if (((file_name_with_path == null) ||
("".equals(file_name_with_path))))
{
throw new OAException("File path is invalid.");
}

File fileToDownload = null;
try
{
fileToDownload = new File(file_name_with_path);
}
catch (Exception e)
{
throw new OAException("Invalid File Path or file does not exist.");
}

if (!fileToDownload.exists())
{
throw new OAException("File does not exist.");
}

if (!fileToDownload.canRead())
{
throw new OAException("Not Able to read the file.");
}

String fileType = getMimeType(file_name_with_ext);
response.setContentType(fileType);
response.setContentLength((int)fileToDownload.length());
response.setHeader("Content-Disposition", "attachment; filename=\"" + file_name_with_ext + "\"");

InputStream in = null;
ServletOutputStream outs = null;

try
{
outs = response.getOutputStream();
in = new BufferedInputStream(new FileInputStream(fileToDownload));
int ch;
while ((ch = in.read()) != -1)
{
outs.write(ch);
}

}
catch (IOException e)
{
// TODO
e.printStackTrace();
}
finally
{
try
{
outs.flush();
outs.close();
if (in != null)
{
in.close();
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
}

7) Create another method as below :

public String getMimeType(String s)
{
int i = s.lastIndexOf(".");
if (i > 0 && i < s.length() - 1)
{
String s1 = s.substring(i + 1);
if (s1.equalsIgnoreCase("amr"))
{
return "audio/amr";
}
if (s1.equalsIgnoreCase("mid"))
{
return "audio/midi";
}
if (s1.equalsIgnoreCase("mmf"))
{
return "application/vnd.smaf";
}
if (s1.equalsIgnoreCase("qcp"))
{
return "audio/vnd.qcelp";
}
if (s1.equalsIgnoreCase("hqx"))
{
return "application/mac-binhex40";
}
if (s1.equalsIgnoreCase("cpt"))
{
return "application/mac-compactpro";
}
if (s1.equalsIgnoreCase("doc"))
{
return "application/msword";
}
if (s1.equalsIgnoreCase("jsp"))
{
return "application/jsp";
}
if (s1.equalsIgnoreCase("oda"))
{
return "application/oda";
}
if (s1.equalsIgnoreCase("pdf"))
{
return "application/pdf";
}
if (s1.equalsIgnoreCase("ai"))
{
return "application/postscript";
}
if (s1.equalsIgnoreCase("eps"))
{
return "application/postscript";
}
if (s1.equalsIgnoreCase("ps"))
{
return "application/postscript";
}
if (s1.equalsIgnoreCase("ppt"))
{
return "application/powerpoint";
}
if (s1.equalsIgnoreCase("rtf"))
{
return "application/rtf";
}
if (s1.equalsIgnoreCase("bcpio"))
{
return "application/x-bcpio";
}
if (s1.equalsIgnoreCase("vcd"))
{
return "application/x-cdlink";
}
if (s1.equalsIgnoreCase("Z"))
{
return "application/x-compress";
}
if (s1.equalsIgnoreCase("cpio"))
{
return "application/x-cpio";
}
if (s1.equalsIgnoreCase("csh"))
{
return "application/x-csh";
}
if (s1.equalsIgnoreCase("dcr"))
{
return "application/x-director";
}
if (s1.equalsIgnoreCase("dir"))
{
return "application/x-director";
}
if (s1.equalsIgnoreCase("dxr"))
{
return "application/x-director";
}
if (s1.equalsIgnoreCase("dvi"))
{
return "application/x-dvi";
}
if (s1.equalsIgnoreCase("gtar"))
{
return "application/x-gtar";
}
if (s1.equalsIgnoreCase("gz"))
{
return "application/x-gzip";
}
if (s1.equalsIgnoreCase("hdf"))
{
return "application/x-hdf";
}
if (s1.equalsIgnoreCase("cgi"))
{
return "application/x-httpd-cgi";
}
if (s1.equalsIgnoreCase("jnlp"))
{
return "application/x-java-jnlp-file";
}
if (s1.equalsIgnoreCase("skp"))
{
return "application/x-koan";
}
if (s1.equalsIgnoreCase("skd"))
{
return "application/x-koan";
}
if (s1.equalsIgnoreCase("skt"))
{
return "application/x-koan";
}
if (s1.equalsIgnoreCase("skm"))
{
return "application/x-koan";
}
if (s1.equalsIgnoreCase("latex"))
{
return "application/x-latex";
}
if (s1.equalsIgnoreCase("mif"))
{
return "application/x-mif";
}
if (s1.equalsIgnoreCase("nc"))
{
return "application/x-netcdf";
}
if (s1.equalsIgnoreCase("cdf"))
{
return "application/x-netcdf";
}
if (s1.equalsIgnoreCase("sh"))
{
return "application/x-sh";
}
if (s1.equalsIgnoreCase("shar"))
{
return "application/x-shar";
}
if (s1.equalsIgnoreCase("sit"))
{
return "application/x-stuffit";
}
if (s1.equalsIgnoreCase("sv4cpio"))
{
return "application/x-sv4cpio";
}
if (s1.equalsIgnoreCase("sv4crc"))
{
return "application/x-sv4crc";
}
if (s1.equalsIgnoreCase("tar"))
{
return "application/x-tar";
}
if (s1.equalsIgnoreCase("tcl"))
{
return "application/x-tcl";
}
if (s1.equalsIgnoreCase("tex"))
{
return "application/x-tex";
}
if (s1.equalsIgnoreCase("textinfo"))
{
return "application/x-texinfo";
}
if (s1.equalsIgnoreCase("texi"))
{
return "application/x-texinfo";
}
if (s1.equalsIgnoreCase("t"))
{
return "application/x-troff";
}
if (s1.equalsIgnoreCase("tr"))
{
return "application/x-troff";
}
if (s1.equalsIgnoreCase("roff"))
{
return "application/x-troff";
}
if (s1.equalsIgnoreCase("man"))
{
return "application/x-troff-man";
}
if (s1.equalsIgnoreCase("me"))
{
return "application/x-troff-me";
}
if (s1.equalsIgnoreCase("ms"))
{
return "application/x-troff-ms";
}
if (s1.equalsIgnoreCase("ustar"))
{
return "application/x-ustar";
}
if (s1.equalsIgnoreCase("src"))
{
return "application/x-wais-source";
}
if (s1.equalsIgnoreCase("xml"))
{
return "text/xml";
}
if (s1.equalsIgnoreCase("ent"))
{
return "text/xml";
}
if (s1.equalsIgnoreCase("cat"))
{
return "text/xml";
}
if (s1.equalsIgnoreCase("sty"))
{
return "text/xml";
}
if (s1.equalsIgnoreCase("dtd"))
{
return "text/dtd";
}
if (s1.equalsIgnoreCase("xsl"))
{
return "text/xsl";
}
if (s1.equalsIgnoreCase("zip"))
{
return "application/zip";
}
if (s1.equalsIgnoreCase("au"))
{
return "audio/basic";
}
if (s1.equalsIgnoreCase("snd"))
{
return "audio/basic";
}
if (s1.equalsIgnoreCase("mpga"))
{
return "audio/mpeg";
}
if (s1.equalsIgnoreCase("mp2"))
{
return "audio/mpeg";
}
if (s1.equalsIgnoreCase("mp3"))
{
return "audio/mpeg";
}
if (s1.equalsIgnoreCase("aif"))
{
return "audio/x-aiff";
}
if (s1.equalsIgnoreCase("aiff"))
{
return "audio/x-aiff";
}
if (s1.equalsIgnoreCase("aifc"))
{
return "audio/x-aiff";
}
if (s1.equalsIgnoreCase("ram"))
{
return "audio/x-pn-realaudio";
}
if (s1.equalsIgnoreCase("rpm"))
{
return "audio/x-pn-realaudio-plugin";
}
if (s1.equalsIgnoreCase("ra"))
{
return "audio/x-realaudio";
}
if (s1.equalsIgnoreCase("wav"))
{
return "audio/x-wav";
}
if (s1.equalsIgnoreCase("pdb"))
{
return "chemical/x-pdb";
}
if (s1.equalsIgnoreCase("xyz"))
{
return "chemical/x-pdb";
}
if (s1.equalsIgnoreCase("gif"))
{
return "image/gif";
}
if (s1.equalsIgnoreCase("ief"))
{
return "image/ief";
}
if (s1.equalsIgnoreCase("jpeg"))
{
return "image/jpeg";
}
if (s1.equalsIgnoreCase("jpg"))
{
return "image/jpeg";
}
if (s1.equalsIgnoreCase("jpe"))
{
return "image/jpeg";
}
if (s1.equalsIgnoreCase("png"))
{
return "image/png";
}
if (s1.equalsIgnoreCase("tiff"))
{
return "image/tiff";
}
if (s1.equalsIgnoreCase("tif"))
{
return "image/tiff";
}
if (s1.equalsIgnoreCase("ras"))
{
return "image/x-cmu-raster";
}
if (s1.equalsIgnoreCase("pnm"))
{
return "image/x-portable-anymap";
}
if (s1.equalsIgnoreCase("pbm"))
{
return "image/x-portable-bitmap";
}
if (s1.equalsIgnoreCase("pgm"))
{
return "image/x-portable-graymap";
}
if (s1.equalsIgnoreCase("ppm"))
{
return "image/x-portable-pixmap";
}
if (s1.equalsIgnoreCase("rgb"))
{
return "image/x-rgb";
}
if (s1.equalsIgnoreCase("xbm"))
{
return "image/x-xbitmap";
}
if (s1.equalsIgnoreCase("xpm"))
{
return "image/x-xpixmap";
}
if (s1.equalsIgnoreCase("xwd"))
{
return "image/x-xwindowdump";
}
if (s1.equalsIgnoreCase("html"))
{
return "text/html";
}
if (s1.equalsIgnoreCase("htm"))
{
return "text/html";
}
if (s1.equalsIgnoreCase("txt"))
{
return "text/plain";
}
if (s1.equalsIgnoreCase("rtx"))
{
return "text/richtext";
}
if (s1.equalsIgnoreCase("tsv"))
{
return "text/tab-separated-values";
}
if (s1.equalsIgnoreCase("etx"))
{
return "text/x-setext";
}
if (s1.equalsIgnoreCase("sgml"))
{
return "text/x-sgml";
}
if (s1.equalsIgnoreCase("sgm"))
{
return "text/x-sgml";
}
if (s1.equalsIgnoreCase("mpeg"))
{
return "video/mpeg";
}
if (s1.equalsIgnoreCase("mpg"))
{
return "video/mpeg";
}
if (s1.equalsIgnoreCase("mpe"))
{
return "video/mpeg";
}
if (s1.equalsIgnoreCase("qt"))
{
return "video/quicktime";
}
if (s1.equalsIgnoreCase("mov"))
{
return "video/quicktime";
}
if (s1.equalsIgnoreCase("avi"))
{
return "video/x-msvideo";
}
if (s1.equalsIgnoreCase("movie"))
{
return "video/x-sgi-movie";
}
if (s1.equalsIgnoreCase("ice"))
{
return "x-conference/x-cooltalk";
}
if (s1.equalsIgnoreCase("wrl"))
{
return "x-world/x-vrml";
}
if (s1.equalsIgnoreCase("vrml"))
{
return "x-world/x-vrml";
}
if (s1.equalsIgnoreCase("wml"))
{
return "text/vnd.wap.wml";
}
if (s1.equalsIgnoreCase("wmlc"))
{
return "application/vnd.wap.wmlc";
}
if (s1.equalsIgnoreCase("wmls"))
{
return "text/vnd.wap.wmlscript";
}
if (s1.equalsIgnoreCase("wmlsc"))
{
return "application/vnd.wap.wmlscriptc";
}
if (s1.equalsIgnoreCase("wbmp"))
{
return "image/vnd.wap.wbmp";
}
if (s1.equalsIgnoreCase("css"))
{
return "text/css";
}
if (s1.equalsIgnoreCase("jad"))
{
return "text/vnd.sun.j2me.app-descriptor";
}
if (s1.equalsIgnoreCase("jar"))
{
return "application/java-archive";
}
if (s1.equalsIgnoreCase("3gp"))
{
return "video/3gp";
}
if (s1.equalsIgnoreCase("3g2"))
{
return "video/3gpp2";
}
if (s1.equalsIgnoreCase("mp4"))
{
return "video/3gpp";
}
}
return "application/octet-stream";
}

8) in process form request of the controller class call this method to download

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);

//on button click in process form request
//call this method
downloadFileFromServer(pageContext,"/cmupload/OWF_Clt_2635_WINNT.zip","/cmupload/OWF_Clt_2635_WINNT.zip");
}

9) Your controller class look like

package oracle.apps.ak.download.webui.webui;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.cabo.ui.data.DataObject;
import java.io.FileOutputStream;
import java.io.InputStream;
import oracle.jbo.domain.BlobDomain;
import java.io.File;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletOutputStream;
import javax.servlet.*;
import oracle.apps.fnd.framework.OAException;
import java.io.*;

/**
* Controller for ...
*/
public class MyCO extends OAControllerImpl
{
public static final String RCS_ID="$Header$";
public static final boolean RCS_ID_RECORDED =
VersionInfo.recordClassVersion(RCS_ID, "%packagename%");

/**
* Layout and page setup logic for a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
}

/**
* @param pageContext the current OA page context
* @param file_name_with_path - this is fully qualified file name with its path on unix application
* server. eg "/xxcrp/xxapplcrp/mukul/abc.pdf"
* @param file_name_with_ext - this is file name with extension, you wanna display user
* for download. eg- i wanna display the abc.pdf file download with name five_point_someone.pdf
* then I can pass this as "five_point_someone.pdf"
*/
public void downloadFileFromServer(OAPageContext pageContext,
String file_name_with_path,
String file_name_with_ext)
{
HttpServletResponse response =
(HttpServletResponse) pageContext.getRenderingContext().getServletResponse();
if (((file_name_with_path == null) ||
("".equals(file_name_with_path))))
{
throw new OAException("File path is invalid.");
}

File fileToDownload = null;
try
{
fileToDownload = new File(file_name_with_path);
}
catch (Exception e)
{
throw new OAException("Invalid File Path or file does not exist.");
}

if (!fileToDownload.exists())
{
throw new OAException("File does not exist.");
}

if (!fileToDownload.canRead())
{
throw new OAException("Not Able to read the file.");
}

String fileType = getMimeType(file_name_with_ext);
response.setContentType(fileType);
response.setContentLength((int)fileToDownload.length());
response.setHeader("Content-Disposition", "attachment; filename=\"" + file_name_with_ext + "\"");

InputStream in = null;
ServletOutputStream outs = null;

try
{
outs = response.getOutputStream();
in = new BufferedInputStream(new FileInputStream(fileToDownload));
int ch;
while ((ch = in.read()) != -1)
{
outs.write(ch);
}

}
catch (IOException e)
{
// TODO
e.printStackTrace();
}
finally
{
try
{
outs.flush();
outs.close();
if (in != null)
{
in.close();
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
}

/**
* @param s
* @return file mime type from its name
*/
public String getMimeType(String s)
{
int i = s.lastIndexOf(".");
if (i > 0 && i < s.length() - 1)
{
String s1 = s.substring(i + 1);
if (s1.equalsIgnoreCase("amr"))
{
return "audio/amr";
}
if (s1.equalsIgnoreCase("mid"))
{
return "audio/midi";
}
if (s1.equalsIgnoreCase("mmf"))
{
return "application/vnd.smaf";
}
if (s1.equalsIgnoreCase("qcp"))
{
return "audio/vnd.qcelp";
}
if (s1.equalsIgnoreCase("hqx"))
{
return "application/mac-binhex40";
}
if (s1.equalsIgnoreCase("cpt"))
{
return "application/mac-compactpro";
}
if (s1.equalsIgnoreCase("doc"))
{
return "application/msword";
}
if (s1.equalsIgnoreCase("jsp"))
{
return "application/jsp";
}
if (s1.equalsIgnoreCase("oda"))
{
return "application/oda";
}
if (s1.equalsIgnoreCase("pdf"))
{
return "application/pdf";
}
if (s1.equalsIgnoreCase("ai"))
{
return "application/postscript";
}
if (s1.equalsIgnoreCase("eps"))
{
return "application/postscript";
}
if (s1.equalsIgnoreCase("ps"))
{
return "application/postscript";
}
if (s1.equalsIgnoreCase("ppt"))
{
return "application/powerpoint";
}
if (s1.equalsIgnoreCase("rtf"))
{
return "application/rtf";
}
if (s1.equalsIgnoreCase("bcpio"))
{
return "application/x-bcpio";
}
if (s1.equalsIgnoreCase("vcd"))
{
return "application/x-cdlink";
}
if (s1.equalsIgnoreCase("Z"))
{
return "application/x-compress";
}
if (s1.equalsIgnoreCase("cpio"))
{
return "application/x-cpio";
}
if (s1.equalsIgnoreCase("csh"))
{
return "application/x-csh";
}
if (s1.equalsIgnoreCase("dcr"))
{
return "application/x-director";
}
if (s1.equalsIgnoreCase("dir"))
{
return "application/x-director";
}
if (s1.equalsIgnoreCase("dxr"))
{
return "application/x-director";
}
if (s1.equalsIgnoreCase("dvi"))
{
return "application/x-dvi";
}
if (s1.equalsIgnoreCase("gtar"))
{
return "application/x-gtar";
}
if (s1.equalsIgnoreCase("gz"))
{
return "application/x-gzip";
}
if (s1.equalsIgnoreCase("hdf"))
{
return "application/x-hdf";
}
if (s1.equalsIgnoreCase("cgi"))
{
return "application/x-httpd-cgi";
}
if (s1.equalsIgnoreCase("jnlp"))
{
return "application/x-java-jnlp-file";
}
if (s1.equalsIgnoreCase("skp"))
{
return "application/x-koan";
}
if (s1.equalsIgnoreCase("skd"))
{
return "application/x-koan";
}
if (s1.equalsIgnoreCase("skt"))
{
return "application/x-koan";
}
if (s1.equalsIgnoreCase("skm"))
{
return "application/x-koan";
}
if (s1.equalsIgnoreCase("latex"))
{
return "application/x-latex";
}
if (s1.equalsIgnoreCase("mif"))
{
return "application/x-mif";
}
if (s1.equalsIgnoreCase("nc"))
{
return "application/x-netcdf";
}
if (s1.equalsIgnoreCase("cdf"))
{
return "application/x-netcdf";
}
if (s1.equalsIgnoreCase("sh"))
{
return "application/x-sh";
}
if (s1.equalsIgnoreCase("shar"))
{
return "application/x-shar";
}
if (s1.equalsIgnoreCase("sit"))
{
return "application/x-stuffit";
}
if (s1.equalsIgnoreCase("sv4cpio"))
{
return "application/x-sv4cpio";
}
if (s1.equalsIgnoreCase("sv4crc"))
{
return "application/x-sv4crc";
}
if (s1.equalsIgnoreCase("tar"))
{
return "application/x-tar";
}
if (s1.equalsIgnoreCase("tcl"))
{
return "application/x-tcl";
}
if (s1.equalsIgnoreCase("tex"))
{
return "application/x-tex";
}
if (s1.equalsIgnoreCase("textinfo"))
{
return "application/x-texinfo";
}
if (s1.equalsIgnoreCase("texi"))
{
return "application/x-texinfo";
}
if (s1.equalsIgnoreCase("t"))
{
return "application/x-troff";
}
if (s1.equalsIgnoreCase("tr"))
{
return "application/x-troff";
}
if (s1.equalsIgnoreCase("roff"))
{
return "application/x-troff";
}
if (s1.equalsIgnoreCase("man"))
{
return "application/x-troff-man";
}
if (s1.equalsIgnoreCase("me"))
{
return "application/x-troff-me";
}
if (s1.equalsIgnoreCase("ms"))
{
return "application/x-troff-ms";
}
if (s1.equalsIgnoreCase("ustar"))
{
return "application/x-ustar";
}
if (s1.equalsIgnoreCase("src"))
{
return "application/x-wais-source";
}
if (s1.equalsIgnoreCase("xml"))
{
return "text/xml";
}
if (s1.equalsIgnoreCase("ent"))
{
return "text/xml";
}
if (s1.equalsIgnoreCase("cat"))
{
return "text/xml";
}
if (s1.equalsIgnoreCase("sty"))
{
return "text/xml";
}
if (s1.equalsIgnoreCase("dtd"))
{
return "text/dtd";
}
if (s1.equalsIgnoreCase("xsl"))
{
return "text/xsl";
}
if (s1.equalsIgnoreCase("zip"))
{
return "application/zip";
}
if (s1.equalsIgnoreCase("au"))
{
return "audio/basic";
}
if (s1.equalsIgnoreCase("snd"))
{
return "audio/basic";
}
if (s1.equalsIgnoreCase("mpga"))
{
return "audio/mpeg";
}
if (s1.equalsIgnoreCase("mp2"))
{
return "audio/mpeg";
}
if (s1.equalsIgnoreCase("mp3"))
{
return "audio/mpeg";
}
if (s1.equalsIgnoreCase("aif"))
{
return "audio/x-aiff";
}
if (s1.equalsIgnoreCase("aiff"))
{
return "audio/x-aiff";
}
if (s1.equalsIgnoreCase("aifc"))
{
return "audio/x-aiff";
}
if (s1.equalsIgnoreCase("ram"))
{
return "audio/x-pn-realaudio";
}
if (s1.equalsIgnoreCase("rpm"))
{
return "audio/x-pn-realaudio-plugin";
}
if (s1.equalsIgnoreCase("ra"))
{
return "audio/x-realaudio";
}
if (s1.equalsIgnoreCase("wav"))
{
return "audio/x-wav";
}
if (s1.equalsIgnoreCase("pdb"))
{
return "chemical/x-pdb";
}
if (s1.equalsIgnoreCase("xyz"))
{
return "chemical/x-pdb";
}
if (s1.equalsIgnoreCase("gif"))
{
return "image/gif";
}
if (s1.equalsIgnoreCase("ief"))
{
return "image/ief";
}
if (s1.equalsIgnoreCase("jpeg"))
{
return "image/jpeg";
}
if (s1.equalsIgnoreCase("jpg"))
{
return "image/jpeg";
}
if (s1.equalsIgnoreCase("jpe"))
{
return "image/jpeg";
}
if (s1.equalsIgnoreCase("png"))
{
return "image/png";
}
if (s1.equalsIgnoreCase("tiff"))
{
return "image/tiff";
}
if (s1.equalsIgnoreCase("tif"))
{
return "image/tiff";
}
if (s1.equalsIgnoreCase("ras"))
{
return "image/x-cmu-raster";
}
if (s1.equalsIgnoreCase("pnm"))
{
return "image/x-portable-anymap";
}
if (s1.equalsIgnoreCase("pbm"))
{
return "image/x-portable-bitmap";
}
if (s1.equalsIgnoreCase("pgm"))
{
return "image/x-portable-graymap";
}
if (s1.equalsIgnoreCase("ppm"))
{
return "image/x-portable-pixmap";
}
if (s1.equalsIgnoreCase("rgb"))
{
return "image/x-rgb";
}
if (s1.equalsIgnoreCase("xbm"))
{
return "image/x-xbitmap";
}
if (s1.equalsIgnoreCase("xpm"))
{
return "image/x-xpixmap";
}
if (s1.equalsIgnoreCase("xwd"))
{
return "image/x-xwindowdump";
}
if (s1.equalsIgnoreCase("html"))
{
return "text/html";
}
if (s1.equalsIgnoreCase("htm"))
{
return "text/html";
}
if (s1.equalsIgnoreCase("txt"))
{
return "text/plain";
}
if (s1.equalsIgnoreCase("rtx"))
{
return "text/richtext";
}
if (s1.equalsIgnoreCase("tsv"))
{
return "text/tab-separated-values";
}
if (s1.equalsIgnoreCase("etx"))
{
return "text/x-setext";
}
if (s1.equalsIgnoreCase("sgml"))
{
return "text/x-sgml";
}
if (s1.equalsIgnoreCase("sgm"))
{
return "text/x-sgml";
}
if (s1.equalsIgnoreCase("mpeg"))
{
return "video/mpeg";
}
if (s1.equalsIgnoreCase("mpg"))
{
return "video/mpeg";
}
if (s1.equalsIgnoreCase("mpe"))
{
return "video/mpeg";
}
if (s1.equalsIgnoreCase("qt"))
{
return "video/quicktime";
}
if (s1.equalsIgnoreCase("mov"))
{
return "video/quicktime";
}
if (s1.equalsIgnoreCase("avi"))
{
return "video/x-msvideo";
}
if (s1.equalsIgnoreCase("movie"))
{
return "video/x-sgi-movie";
}
if (s1.equalsIgnoreCase("ice"))
{
return "x-conference/x-cooltalk";
}
if (s1.equalsIgnoreCase("wrl"))
{
return "x-world/x-vrml";
}
if (s1.equalsIgnoreCase("vrml"))
{
return "x-world/x-vrml";
}
if (s1.equalsIgnoreCase("wml"))
{
return "text/vnd.wap.wml";
}
if (s1.equalsIgnoreCase("wmlc"))
{
return "application/vnd.wap.wmlc";
}
if (s1.equalsIgnoreCase("wmls"))
{
return "text/vnd.wap.wmlscript";
}
if (s1.equalsIgnoreCase("wmlsc"))
{
return "application/vnd.wap.wmlscriptc";
}
if (s1.equalsIgnoreCase("wbmp"))
{
return "image/vnd.wap.wbmp";
}
if (s1.equalsIgnoreCase("css"))
{
return "text/css";
}
if (s1.equalsIgnoreCase("jad"))
{
return "text/vnd.sun.j2me.app-descriptor";
}
if (s1.equalsIgnoreCase("jar"))
{
return "application/java-archive";
}
if (s1.equalsIgnoreCase("3gp"))
{
return "video/3gp";
}
if (s1.equalsIgnoreCase("3g2"))
{
return "video/3gpp2";
}
if (s1.equalsIgnoreCase("mp4"))
{
return "video/3gpp";
}
}
return "application/octet-stream";
}

/**
* Procedure to handle form submissions for form elements in
* a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);

//on button click in process form request
//call this method
downloadFileFromServer(pageContext,"/cmupload/OWF_Clt_2635_WINNT.zip","/cmupload/OWF_Clt_2635_WINNT.zip");
}

}

10) Rebuild the .jpr and run the page. Happy Coding…

Upload Files on UNIX Application Server (OA Framework)

2010-02-09T03:39:00.000-08:00

1) Create Project in jdeveloper.
2) Create two business components as
oracle.apps.ak.upload.server and
oracle.apps.ak.upload.webui without any entity,view or application module.
3) in oracle.apps.ak.upload.server create entity object with dual table.
4) create view and application module for the same.
5) in oracle.apps.ak.upload.webui
create one OA page.
6) Also set the form property to TRUE of main region of the type pageLayout.
7) Create one region with messageComponenet Layout
Set AM Defination property.
8) Create one item of type=>messageFileUpload in the messageComponenet Layout region.
9) set prompt,additional text properties of item.
10) create one submit button under the main region.set prompt,additional text,action type=>fire action,event=>uploadFileToServer.
11) under oracle.apps.ak.upload.webui create one controller class.
import following packages in the class.

import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.cabo.ui.data.DataObject;
import java.io.FileOutputStream;
import java.io.InputStream;
import oracle.jbo.domain.BlobDomain;
import java.io.File;

12 ) create one method as below in controller class :

public void fileupload(OAPageContext pageContext, String fileuploadBeanId,String server_dir_path)
{
System.out.println("kamlesh");
DataObject fileUploadData = (DataObject) pageContext.getNamedDataObject(fileuploadBeanId);
if(fileUploadData!=null)
{
String uFileName =(String) fileUploadData.selectValue(null, "UPLOAD_FILE_NAME");
String contentType = (String) fileUploadData.selectValue(null, "UPLOAD_FILE_MIME_TYPE");

File file = new File(server_dir_path, uFileName);
FileOutputStream output = null;
InputStream input = null;

try
{
output = new FileOutputStream(file);
BlobDomain uploadedByteStream =
(BlobDomain) fileUploadData.selectValue(null, uFileName);
input = uploadedByteStream.getInputStream();
for (int bytes = 0; bytes < uploadedByteStream.getLength(); bytes++)
{
output.write(input.read());
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if (input != null)
{
input.close();
}
if (output != null)
{
output.close();
output.flush();
}
}
catch (Exception ez)
{
ez.printStackTrace();
}
}

}
}

13) In process form request write as below

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
--- your coding as below in PFR process form request-----
fileupload(pageContext,"item1","/cmload");
}

14) Rebuild your .jpr and run the page.

15) If giving error or warning paste below in Impl.java file of Entity.

public void setCreatedBy(Number number)
{
}

public void setCreationDate(Date date)
{
}

public void setLastUpdatedBy(Number number)
{
}

public void setLastUpdateDate(Date date)
{
}

public void setLastUpdateLogin(Number number)
{
}

Controller Class will be like :-

package oracle.apps.ak.upload.webui.webui;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.cabo.ui.data.DataObject;
import java.io.FileOutputStream;
import java.io.InputStream;
import oracle.jbo.domain.BlobDomain;
import java.io.File;

/**
* Controller for ...
*/
public class MyCO extends OAControllerImpl
{
public static final String RCS_ID="$Header$";
public static final boolean RCS_ID_RECORDED =
VersionInfo.recordClassVersion(RCS_ID, "%packagename%");

/**
* Layout and page setup logic for a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
}

public void fileupload(OAPageContext pageContext, String fileuploadBeanId,String server_dir_path)
{
System.out.println("kamlesh");
DataObject fileUploadData = (DataObject) pageContext.getNamedDataObject(fileuploadBeanId);
if(fileUploadData!=null)
{
String uFileName =(String) fileUploadData.selectValue(null, "UPLOAD_FILE_NAME");
String contentType = (String) fileUploadData.selectValue(null, "UPLOAD_FILE_MIME_TYPE");

File file = new File(server_dir_path, uFileName);
FileOutputStream output = null;
InputStream input = null;

try
{
output = new FileOutputStream(file);
BlobDomain uploadedByteStream =
(BlobDomain) fileUploadData.selectValue(null, uFileName);
input = uploadedByteStream.getInputStream();
for (int bytes = 0; bytes < uploadedByteStream.getLength(); bytes++)
{
output.write(input.read());
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if (input != null)
{
input.close();
}
if (output != null)
{
output.close();
output.flush();
}
}
catch (Exception ez)
{
ez.printStackTrace();
}
}

}
}

/**
* Procedure to handle form submissions for form elements in
* a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
fileupload(pageContext,"item1","/cmload");
fileupload(pageContext,"item3","/cmload");
fileupload(pageContext,"item4","/cmload");
fileupload(pageContext,"item5","/cmload");
fileupload(pageContext,"item6","/cmload");
fileupload(pageContext,"item7","/cmload");
fileupload(pageContext,"item8","/cmload");
fileupload(pageContext,"item9","/cmload");
fileupload(pageContext,"item10","/cmload");
fileupload(pageContext,"item11","/cmload");
fileupload(pageContext,"item12","/cmload");
fileupload(pageContext,"item13","/cmload");

}

}

Kindly note that if your requirement is uploading files on Database server instead of Application Server then you have to create one NFS MOUNT POINT for that in Unix server. While testing this page you can give here the path of your local machine unless it won’t work because here the path given is the server path.

User Creation - From Personalization To Avoid Special Characters.

2009-12-30T01:19:00.000-08:00

Open User Creation Form , Querry any one of the existing Username.Then Go to
Help => Digonostics => Custom Code => Personalize

Create one function as below :
=========================

seq: 1
description : Avoid Specila Characters in USERNAME.

Create Condition :
=============

Trigger Event :WHEN-NEW-ITEM-INSTANCE
Trigger Object :USER.USER_PASSWORD
Condition :
:USER.USER_NAME like ('%#%') or
:USER.USER_NAME like ('%@%') or
:USER.USER_NAME like ('%!%') or
:USER.USER_NAME like ('%$%') or
:USER.USER_NAME like ('%^%') or
:USER.USER_NAME like ('%&%') or
:USER.USER_NAME like ('%%%') or
:USER.USER_NAME like ('%*%') and ${item.user.user_password.navigable.current}='TRUE'

Processing Mode : Both.

Now Create Actions For the True Condition :
======================================
seq : 1
Type : Builtin
Language : All
Built Type : GO_ITEM
Argument : USER.USER_NAME

seq: 2
Type :Message
Language : All
Message Type : Error
Message Text : Username Should not Contain Special Characters like !,@,#,$,%,^,&,*.

Save.
Open New User Creation Form and try to create user with special characters.
It Throws Error : Username Should not Contain Special Characters like !,@,#,$,%,^,&,*.
when cursor is navigated to password field after entering the username.

Thanks ,
Kamlesh Nikhade
Email : Kamlesh3659@gmail.com

AP Invoice Technical Details with Functional Inputs

2009-12-25T20:54:00.000-08:00

When Invoice Booked and Saved
=============================
One row created in ap_invoices_all and its distribution lines created in ap_invoice_distributions_all

When Invoice Validated :
======================
ap_invoice_distributions_all.MATCH_STATUS_FLAG='A'
ap_invoice_distributions_all.ACCOUNTING_EVENT_ID=NOT NULL(Here 1370092)
one row created in ap_accounting_events_all with accounting_event_id=ap_invoice_distributions_all.ACCOUNTING_EVENT_ID ap_accounting_events_all.EVENT_STATUS_CODE='CREATED' ap_accounting_events_all.SOURCE_TABLE='AP_INVOICES'
ap_accounting_events_all.SOURCE_ID=ap_invoice_distributions_all.INVOICE_ID=
AP_INVOICE_ALL.INVOICE_ID

When Invoice Accounted :
=====================
ap_invoice_distributions_all.ACCRUAL_POSTED_FLAG='Y'
ap_invoice_distributions_all.POSTED_FLAG='Y' ap_accounting_events_all.EVENT_STATUS_CODE='ACCOUNTED'
ONE ROW CREATED IN AP_AE_HEADERS_ALL where AP_AE_HEADERS_ALL. accounting_event_id=ap_accounting_events_all.accounting_event_id Rows created in ap_ae_lines_all
where AP_AE_HEADERS_ALL.ae_header_id=ap_ae_lines_all.ae_header_id as
below The number of rows generally created in ap_ae_lines_all counted as 1)
one row for invoice with ap_ae_lines_all.AE_LINE_TYPE_CODE='LIABILITY'
ap_ae_lines_all.SOURCE_TABLE='AP_INVOICES' ,ap_ae_lines_all.source_id=ap_invoices_all.invoice_id2)
Other rows are created for the invoice distribution lines (one line per invoice distribution line).ap_ae_lines_all.AE_LINE_TYPE_CODE='CHARGE',
SOURCE_TABLE='AP_INVOICE_DISTRIBUTIONS',
ap_ae_lines_all.SOURCE_ID=AP_INVOICE_DISTRIBUTIONS.INVOICE_ID

When Invoice Approved :
========================
ap_invoices_all.WFAPPROVAL_STATUS='MANUALLY APPROVED', initially it was 'REQUIRED'

When Payment Created
=====================
when payment created the one record created in ap_checks_all table.

When Payment Accounted
=============================
When payment document accounted then one row is created in ap_accounting_events_all table.
AP_invoice_payments_all.ACCOUNTING_EVENT_ID=
ap_accounting_events_all.ACCOUNTING_EVENT_ID
ap_accounting_events_all.EVENT_STATUS_CODE='ACCOUNTED'. andap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id.

After Doing the Payment (paid) of invoice with Created Payment Document
=============================================================
ap_invoices_all.payment_status_flag='Y' BEFORE 'N'It creates the linKing between ap_invoices_all and ap_checks_all by AP_INVOICE_PAYMENTS_ALL.one row created in AP_INVOICE_PAYMENTS_ALL with reference of invoice id.
AP_INVOICE_PAYMENTS_ALL.ACCRUAL_POSTED_FLAG='Y'
AP_INVOICE_PAYMENTS_ALL.CASH_POSTED_FLAG='Y'
AP_INVOICE_PAYMENTS_ALL.POSTED_FLAG='Y'and when get void the AP_INVOICE_PAYMENTS_ALL.REVERSAL_FLAG='Y' unless it is 'N'
When payment got accounted the one row created in ap_accounting_events_all with ap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id and AP_INVOICE_PAYMENTS_ALL.source_table='AP_CHECKS'

After Clearing Check from cash management
=====================================
open payment document and create accounting for it, showing partial now.
after successfull accounting of the document:
one line is created in AP_PAYMENT_HISTORY_all with new accounting _event_id.
AP_PAYMENT_HISTORY_all.accounting_event_id=
ap_accounting_events_all.accounting_event_idone new line created in ap_accounting_events_all with EVENT_TYPE_CODE='PAYMENT CLEARING'and AP_INVOICE_PAYMENTS_ALL.source_table='AP_CHECKS'
ap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id andAP_PAYMENT_HISTORY_all.accounting_event_id=
ap_accounting_events_all.accounting_event_id
one row created in AP_AE_HEADERS_ALL with new accounting_event_id and two rows in this case created in ap_ae_lines_all with AE_LINE_TYPE_CODE='CASH CLEARING ' AND 'CASH',SOURCE_TABLE='AP_CHECKS'.

When Posted in GL (GL_POSTING)
===============================
after running the request "Payables Transfer to General Ledger".The ap_ae_lines_all.GL_SL_LINK_ID populates.AP_AE_HEADERS_ALL.GL_TRANSFER_FLAG='Y'AP_AE_HEADERS_ALL.
GL_TRANSFER_RUN_ID IS NOT NULL AP_AE_HEADERS_ALL.TRIAL_BALANCE_FLAG='Y'

Interview Questions Reports 6i

2009-12-16T06:32:00.000-08:00

What are the various types of reports ?
* Tabular, Master Detail, Form, Form Letter, Mailing Labels, Matrix

2. What is the difference between Master - Detail Report and report created by breaks ?
* Master/detail data models are very similar to break report data models. However, a master/detail data model is created using two queries, each of which owns at least one group, and a data link. A break report data model is created using one query and at least two groups. While reports based on a single query are usually more efficient than reports based on multiple queries, sometimes the structure of your data tables may require you to link multiple tables.

3. What are Anchors ?
* An anchor defines the relative position of an object to the object to which it is anchored. Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another.

4. What are the various types of anchors in Reports ?
A There are two types of anchors in Oracle Reports:
* implicit (anchors that Oracle Reports creates when a report is run)
* explicit (anchors you create)

Implicit Anchors : At runtime, Oracle Reports generates an implicit anchor for each layout object that does not already have an explicit anchor. It determines for each layout object which objects, if any, can overwrite it, then creates an anchor from the layout object to the closest object that can overwrite it. This prevents the object from being overwritten. The implicit anchor functionality saves you from having to define the positioning of each object. Implicit anchors are not visible in the Layout editor. However, you can specify that the Object Navigator display anchoring information using the Object Navigator Options dialog.

Explicit Anchors : Create an anchor in the Layout editor by clicking on the Anchor tool, dragging from one edge of the child to the one of the parent's edges, then specifying the anchor's properties in its property sheet. Any anchor you create for an object will override its implicit anchoring. Explicit anchors are always visible in the Layout editor unless you specify otherwise via the Layout Options dialog

9. What are the various report triggers ? What is their order of firing ?
A There are eight report triggers. Of these there are five global triggers called the Report Triggers. They are fired in the following order :
* Before Parameter Form
* After Parameter Form
* Before Report
* Between Pages
* After Report

Apart from the above Five Report Triggers, there are three other types of triggers :
* Validation Triggers
* Format Triggers
* Action Triggers

Before Form : Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. (Note : If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters).

After Form : Fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. (Note : If the Runtime Parameter Form is suppressed, the After Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data).

Before Report : Fires before the report is executed but after queries are parsed and data is fetched.

Between Pages : Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. (Note : In the Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.)

After Report : Fires after you exit the Previewer, or after report output is sent to a specified destination, such as a file, a printer, or an Oracle*Mail userid. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report completed successfully.

Validation Triggers : Validation Triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. (Notice that this means each Validation Trigger may fire twice when you execute the report). Validation Triggers are also used to validate the Initial Value of the parameter in the Parameter property sheet.

Format Triggers : Format Triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object.

Action Triggers : Action Triggers are PL/SQL procedures executed when a button is selected in the Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL.

19. What are Placeholder Columns ?
A A placeholder is a "dummy" column for which you can conditionally set the datatype and value via PL/SQL or a user exit. Placeholder columns are useful when you want to selectively populate a column with a value (e.g., each time the nth record is fetched, or each time a record is fetched containing a specific value, etc.).

20. What are the various Module Types in Reports ?
A You can build three types of modules with Oracle Reports:
* external queries, which are ANSI-standard SQL SELECT statements that can be referenced by modules
* external PL/SQL libraries, which are collections of PL/SQL source code that can be referenced by modules
* reports, which are collections of report-level objects and references to external queries and PL/SQL libraries (optional) that can be referenced by modules

22. What are Physical and Logical pages in Reports ?
A A report page can have any length and any width. Because printer pages may be smaller or larger than your report's "page," the concept of physical and logical pages is used.

Physical Page : A physical page (or panel) is the size of a page that will be output by your printer.

Logical Page : A logical page is the size of one page of your actual report; one logical page may be made up of multiple physical pages. The Previewer displays the logical pages of your report output, one at a time.

23. What are the various page layout sections in Oracle Reports ?
A A report has three sections : the report header pages, report body/margin pages, and report trailer pages.

27. What are various types of parameters ?
A There are two types of parameters:
* default (called system parameters)
* user-created (called bind and lexical parameters)

30. How do you reference parameters and columns in reports ?
A In two ways :
* As bind references
* As lexical references

31. What are Bind Referencing and Lexical Referencing ?
* Bind Referencing : Bind references are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Use bind reference when you want the parameter to substitute only one value at runtime. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries.

Lexical Referencing : Lexical references are placeholders for text that you embed in a SELECT statement. Use Lexical reference when you want the parameter to substitute multiple values at runtime. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

33. Can you create a group without any break columns ?
* No.

35. Types of Matrix report ?
* 1. Single query 2. Multi query 3. Nested Query 4. Matrix Break

37. In Reports, how can you print one record per page in the output ?
* Set the Maximum records per page property of the Repeating frame to 1.

38. How do you print a Report 2.5 report in character mode ?
* Set MODE = 'Character' in the Parameter form

39. What are widow lines ?
* Widow lines are the minimum number of lines of the boilerplate text or field that should appear on the logical page where the text starts to print. If the number of lines specified for this property cannot fit on the logical page, then all lines of the boilerplate are moved to the next page.

40. What are widow records ?
* Widow records are the minimum number of instances (records) that should appear on the logical page where the repeating frame starts to print. If the number of instances specified for this property cannot fit on the logical page where the repeating frame is initially triggered to print, then the repeating frame will start formatting on the next page.

41. What is 'page protect' property for objects ?
* Page protect property for an object indicates whether to try to keep the entire object and its contents on the same logical page. Checking Page Protect means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page.

42. What is the 'Print Condition Type' property ?
* 'Print Condition Type' property specifies the frequency with which you want the object to appear in the report. The Print Condition Type options indicate the logical page(s) on which the object should be triggered to print with regard to the Print Condition Object.

44. What are the various values of the 'Print Condition Type' property in Reports ?
* The various values are :
All : All means the object and all of its contents will be printed on all logical pages of the Print Condition Object. The object will be repeated on any overflow pages of the Print Condition Object and will be truncated at the logical page boundary, if necessary.

All but First : All but First means the object and all of its contents will be printed on all logical pages of the Print Condition Object except the first logical page. The object will be formatted only on overflow pages of the Print Condition Object and will be truncated at the logical page boundary, if necessary.

All but Last : All but Last means the object and all of its contents will be printed on all logical pages of the Print Condition Object except the last logical page. The object will be repeated on any overflow pages of the Print Condition Object except the last one and will be truncated at the logical page boundary, if necessary.

*Default : Default means that Oracle Reports will use object positioning to set the Print Condition Type to either *First or *Last for you. (The asterisk indicates that Oracle Reports specified the setting for you.)

First : First means that the object and all of its contents will only be printed on the first logical page of the Print Condition Object. The object will be formatted and will overflow to subsequent pages, if necessary.

Last : Last means that the object and all of its contents will only be printed on the last logical page of the Print Condition Object. The object will be formatted after the Print Condition Object and will overflow to subsequent pages, if necessary.

* For repeating frames, the print condition type property refers to every logical page of every instance (record) of the repeating frame. If you specify a Print Condition Type setting of All and a Print Condition Object setting of Enclosing Object for a field F_1, it prints in every instance of repeating frame R_1 on every logical page. If you specify a Print Condition Type setting of All but First in the above case , then the field will be printed only in those cases where a single instance (row or record) of the repeating frame spans across more than one page (which is normally not the case since many records are there in a single logical page and not one record in many pages). In such a case the field will be printed on all subsequent pages of this instance except the first page.

43. What is the 'Print Condition Object' property ?
* 'Print Condition Object' property specifies the object on which to base the Print Condition Type of the current object. For example, if you specify a Print Condition Type of All and a Print Condition Object of Anchoring Object, the current object will be triggered to print on every logical page on which its anchoring object (parent object) appears.

45. What are the various values of the 'Print Condition Object' property in Reports ?
* The various values are :
Anchoring Object : Anchoring Object is the parent object to which the current object is implicitly or explicitly anchored.
Enclosing Object : Enclosing Object is the object that encloses the current object.

46. What is the horizontal of vertical sizing property of objects ?
* Horizontal of vertical sizing property specifies how the horizontal or vertical size of the object may change at runtime to accommodate the objects or data within it.

47. What are the various values of the horizontal of vertical sizing property ?
* The various values are :
Contract : Contract means the vertical size of the object decreases, if the formatted objects or data within it are short enough, but it cannot increase to a height greater than that shown in the editor. Note : Truncation of data may occur. (You can think of this option as meaning "only contract, do not expand.")

Expand : Expand means the vertical size of the object increases, if the formatted objects or data within it are tall enough, but it cannot decrease to a height less than that shown in the editor. (You can think of this option as meaning "only expand, do not contract.")

Fixed : Fixed means the height of the object is the same on each logical page, regardless of the size of the objects or data within it. Note : Truncation of data may occur. The height of the object is defined to be its height in the editor.

Variable : Variable means the object may expand or contract vertically to accommodate the objects or data within it (with no extra space), which means the height shown in the editor has no effect on the object's height at runtime.

51. How do you display a message in reports ?
* SRW.Message

54. What are the various values of 'Print Panel Order' property of report ?
* The various values are :
Across/Down : Across/Down means the physical pages of the report body will print left-to-right then top-to-bottom.

Down/Across : Down/Across means the physical pages of the report body will print top-to-bottom and then left-to-right.

55. What is the 'Print Direction' Property of Repeating frames ?
* 'Print Direction' Property specifies the direction in which successive instances of the repeating frame appear.

56. What are the various values of the 'Print Direction' Property of Repeating frames ?
* The various values are :
Across : Across means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance across the logical page.

Across/Down : Across/Down means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance until an entire instance cannot fit between the previous instance and the right margin of the logical page. At that time, Oracle Reports prints the instance below the left-most instance on the logical page, provided there is enough vertical space left on the logical page for the instance to print completely.

Down : Down means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance down the logical page.

Down/Across : Down/Across means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance until an entire instance cannot fit inside the bottom margin of the logical page. At that time, Oracle Reports prints the instance to the right of the topmost instance on the logical page, provided there is enough horizontal space left on the logical page for the instance to print completely.

59. What is the 'Keep with Anchoring Object' object property ?
* 'Keep with Anchoring Object' object property indicates whether to keep an object and the object to which it is anchored on the same logical page. Checking Keep with Anchoring Object means that if the object, its anchoring object, or both cannot fit on the logical page, they will be moved to the next logical page.

60. What is 'Page Break Before' object property ?
* 'Page Break Before' object property indicates that you want the object to be formatted on the page after the page on which it is initially triggered to print. Note that this does not necessarily mean that all the objects below the object with Page Break Before will move to the next page.

61. What is 'Page Break After' object property ?
* 'Page Break After' object property indicates that you want all children of the object to be moved to the next page. In other words, any object that is a child object of an anchor (implicit or explicit) to this object will be treated as if it has Page Break Before set. Note that this does not necessarily mean that all the objects below the object with Page Break After will move to the next page.

63. What is the 'Break Order' property of columns ?
* The 'Break Order' property is the order in which to display the column's values. This property applies only to columns that identify distinct values of user-created groups (i.e., break groups). The order of column values in a default group is determined by the ORDER BY clause of the query. For column values in user-created groups, however, you must use Break Order to specify how to order the break column's values.

64. What are the various types of links ?
* The Data Link tool draws a link between a parent group and a child query. Creating a link is a drag and drop operation. Clicking and dragging from one column to another creates a link between those two columns (column to column link). Clicking and dragging from one query to another creates all possible links between columns selected by the queries based on database constraints (query to query link). Clicking and dragging between two groups creates a group-to-group link (i.e., a link with no columns).

65. Name some of the procedures in the SRW package ?
* SRW.Message, SRW.User_Exit, SRW.Do_Sql, SRW.Run_Report

66. What are the various report layout regions ?
* There are three report regions in the Layout editor :
* header
* body/margin
* trailer

Header : The report header pages appear once at the beginning of each report on a set of separate pages. They can contain text, graphics, data, and computations.

Body/Margin : The body/margin pages appear between the header and trailer pages, and are the bulk of the report. Each physical page in this section consists of a body and a margin. The body contains the majority of the report's text, graphics, data, and computations.
A top and bottom margin appear on each page, until all data within the body has been formatted. A margin may include text, graphics, page numbers, page totals, and grand totals. The default margin size is one half inch each for the top and bottom margins and zero for the left and right margins.

Trailer : The report trailer pages appear once at the end of each report on a set of separate pages. They can contain text, graphics, data, and computations.

70. What is more efficient : Maximum rows or Group Filter ?
* Maximum Rows in the Query property sheet restricts the number of records fetched by the query. A group filter determines which records to include and which records to exclude. Since Maximum Rows actually restricts the amount of data retrieved, it is faster than a group filter in most cases. If you are using a Filter of Last or Conditional, Oracle Reports must retrieve all of the records in the group before applying the filter criteria. As a result, Maximum Rows or a Filter of First is faster.

Procure to Pay Cycle Query

2009-12-15T21:20:00.000-08:00

Includes two scripts to fetch all the transactions information related with in a procure to pay cycle.

Two scripts are provided to use one with receipts and other when receipts are not created.
Few important fields that were included in the script are Requisition Number, Purchase Order Number, Invoice Number, Customer Number, Invoice Amount, GL Transfer flag e.t.c

WITH OUT RECEIPTS

===========================
-- WITH OUT RECEIPTS
-- procure to pay cycle query
select distinct
reqh.segment1 REQ_NUM,
reqh.AUTHORIZATION_STATUS REQ_STATUS,
-- poh.po_header_id,
poh.segment1 PO_NUM,
pol.line_num,
poh.AUTHORIZATION_STATUS PO_STATUS,
-- i.invoice_id,
i.invoice_num,
i.invoice_amount,
i.amount_paid,
i.vendor_id,
-- v.vendor_name,
-- p.check_id,
c.check_number,
h.gl_transfer_flag,
h.period_name
from ap_invoices_all i,
ap_invoice_distributions_all invd,
po_headers_all poh,
po_lines_all pol,
po_distributions_all pod,
po_vendors v,
po_requisition_headers_all reqh,
po_requisition_lines_all reql,
po_req_distributions_all reqd,
ap_invoice_payments_all p,
ap_checks_all c,
ap_ae_headers_all h,
ap_ae_lines_all l
where 1=1
and i.vendor_id = v.vendor_id
and c.check_id = p.check_id
and p.invoice_id = i.invoice_id
and poh.PO_HEADER_ID = pol.PO_HEADER_ID
and reqh.REQUISITION_HEADER_ID = reql.REQUISITION_HEADER_ID
and reqd.REQUISITION_LINE_ID = reql.REQUISITION_LINE_ID
and pod.REQ_DISTRIBUTION_ID = reqd.DISTRIBUTION_ID
and pod.PO_HEADER_ID = poh.PO_HEADER_ID
and pod.PO_DISTRIBUTION_ID = invd.PO_DISTRIBUTION_ID
and invd.INVOICE_ID = i.INVOICE_ID
and h.ae_header_id = l.ae_header_id
and l.SOURCE_TABLE = 'AP_INVOICES'
AND l.SOURCE_ID = i.invoice_id
--and poh.segment1 = 4033816 -- PO NUMBER
and reqh.segment1 = '501' -- REQ NUMBER
--and i.invoice_num = 3114 -- INVOICE NUMBER
--and c.check_number = -- CHECK NUMBER
--and vendor_id = -- VENDOR ID

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

WITH RECEIPTS

===========================================
-- PROCURE TO PAY CYCLE QUERY WITH RECEIPTS
SELECT DISTINCT reqh.segment1 req_num, reqh.authorization_status req_status,
-- POH.PO_HEADER_ID,
poh.segment1 po_num, pol.line_num,
poh.authorization_status po_status, rcvh.receipt_num,
rcv.inspection_status_code,
-- I.INVOICE_ID,
i.invoice_num, i.invoice_amount,
i.amount_paid, i.vendor_id,
-- V.VENDOR_NAME,
-- P.CHECK_ID,
c.check_number, h.gl_transfer_flag,
h.period_name
FROM ap_invoices_all i,
ap_invoice_distributions_all invd,
po_headers_all poh,
po_lines_all pol,
po_distributions_all pod,
po_vendors v,
po_requisition_headers_all reqh,
po_requisition_lines_all reql,
po_req_distributions_all reqd,
rcv_transactions rcv,
rcv_shipment_headers rcvh,
rcv_shipment_lines rcvl,
ap_invoice_payments_all p,
ap_checks_all c,
ap_ae_headers_all h,
ap_ae_lines_all l
WHERE 1 = 1
AND i.vendor_id = v.vendor_id
AND c.check_id = p.check_id
AND p.invoice_id = i.invoice_id
AND poh.po_header_id = pol.po_header_id
AND reqh.requisition_header_id = reql.requisition_header_id
AND reqd.requisition_line_id = reql.requisition_line_id
AND pod.req_distribution_id = reqd.distribution_id
AND pod.po_header_id = poh.po_header_id
--AND POH.PO_HEADER_ID = RCV.PO_HEADER_ID
AND rcvh.shipment_header_id = rcv.shipment_header_id(+)
--AND RCVH.SHIPMENT_HEADER_ID = RCVL.SHIPMENT_HEADER_ID
--AND RCV.TRANSACTION_TYPE = 'RECEIVE'
--AND RCV.SOURCE_DOCUMENT_CODE = 'PO'
--AND POL.PO_LINE_ID = RCV.PO_LINE_ID
--AND POD.PO_DISTRIBUTION_ID = RCV.PO_DISTRIBUTION_ID
AND pod.po_distribution_id = invd.po_distribution_id
AND invd.invoice_id = i.invoice_id
AND h.ae_header_id = l.ae_header_id
AND l.source_table = 'AP_INVOICES'
AND l.source_id = i.invoice_id
--AND POH.SEGMENT1 = 36420 -- PO NUMBER
AND reqh.segment1 = '501' -- REQ NUMBER
--AND I.INVOICE_NUM = 3114 -- INVOICE NUMBER
--AND C.CHECK_NUMBER = -- CHECK NUMBER
--AND VENDOR_ID = -- VENDOR ID
--AND RECEIPT_NUM = 692237

Form Personalization : UPDATE ALLOWED :FALSE

2009-12-15T04:33:00.000-08:00

The requirment is to restrict Libiality code field against updatation , once the invoice validated. The libiality code field is hidden in the form means from show field we have to select that for display. I tried with personalizations on the field as follows :

Go to the Invoice Form with querry one of the invoice :Navigation : Help > Digonostics > Custom Code > Personalize

Condition
==========
Trigger Event : WHEN-NEW-ITEM-INSTANCE
Trigger Object : INV_SUM_FOLDER.APPROVAL_STATUS_DISPLAY
Condition : ${item.inv_sum_folder.approval_status_display.value} ='Validated'
Note : We can use IN operator in Condition like
${item.inv_sum_folder.approval_status_display.value} in ('Validated','Never Validated')
Processing Mode : Both

Action
========
Type : Property
Language : All

Object Type : Item
Target Object : INV_SUM_FOLDER.LIABILITY_ACCOUNT
Property Name : UPDATE_ALLOWED (ITEM-INSTANCE)
Value : False

Thanks & Regards,
Kamlesh Nikhade.

Some Important Links

2009-12-13T12:53:00.000-08:00

1) eTRM ( Electronic Technical Manual )

http://etrm.oracle.com

Open Invoice Interface & How to find the table in Oracle Apps

2009-12-13T12:40:00.001-08:00

Invoice Import Interface Tables

How to find the table in Oracle Apps

select * from dba_objects where owner 'GL' and object_type 'TABLE'

Change owner for respective module table info.

Impotant Tables In Account Payables

2009-12-13T12:32:00.000-08:00

1- AP_INVOICES_ALL

2- AP_INVOICE_DISTRIBUTIONS_ALL

3- AP_PAYMENT_SCHEDULES_ALL

4- AP_HOLDS_ALL

5- AP_AE_LINES_ALL

6- AP_AE_HEADERS_ALL

AP_INVOICES_ALL

AP_INVOICES_ALL contains records for invoices you enter. There is one row for each invoice you enter. An invoice can have one or more invoice distribution lines. An invoice can also have
one or more scheduled payments. An invoice of type EXPENSE REPORT must relate to a row in AP_EXPENSE_REPORT_HEADERS_ALL unless the record has been purged from
AP_EXPENSE_REPORT_HEADERS_ALL. Your Oracle Payables application uses the INTEREST type invoice for interest that itcalculates on invoices that are overdue. Your Oracle Payables
application links the interest invoice to the original invoice by inserting the INVOICE_ID in the AP_INVOICE_RELATIONSHIPS table.

AP_INVOICE_DISTRIBUTIONS_ALL

AP_INVOICE_DISTRIBUTIONS_ALL holds the distribution line information that you enter for invoices. There is a row for each invoice distribution. A distribution line must be associated
with an invoice. An invoice can have multiple distribution lines. Your Oracle Payables application automatically creates rows in this table when:

1) you choose a distribution set at the invoice level

2) you import expense reports

3) you match an invoice to a purchase order or receipt; ituses information from the matched purchase order or receipt

4) you import invoices via the Open Interface Import process

5) you select to automatically calculate tax

6) you select to automatically do withholding.

Each invoice distribution line has its own accounting date. When you account for an invoice, your OraclePayables application creates accounting events, accounting entry headers and
accounting entry lines for those distribution lines that have accounting dates included in the selected accounting date range for the Payables Accounting Process.The accounting entries can
then be transferred over to General Ledger by running the Transfer to General Ledger process which creates journal entries. Values for POSTED_FLAG may be Y for accounted distributions
or N for distributions that have not been accounted. Values for ACCRUAL_POSTED_FLAG may be Y if distribution has been accounted and system is set up for accrual basis accounting or
N if either distribution has not been accounted or accrual basis accounting is not used. Values for CASH_POSTED_FLAG may be Y if distribution has been accounted and system is set up for
cash basis accounting, N if either distribution has not been accounted or system is not set up for cash basis accounting or P if distribution has been partially accounted in the cash set of
books. The MATCH_STATUS_FLAG indicates the approval status for the distribution. Values for the MATCH_STATUS_FLAG can be null or N for invoice distributions that Approval has
not tested or T for distributions that have been tested or A for distributions that have been tested and approved. Invoice distributions may be interfaced over/from Oracle Assets or Oracle
Projects. Your Oracle Payables application sets the ASSETS_ADDITION_FLAG to U for distributions not tested by Oracle Assets; Oracle Assets then adjusts this flag after it tests a
distribution for assignment as an asset.

To avoid the same invoice distribution being interfaced to both Oracle Projects and Oracle Assets, you must interface any project–related invoice distribution to Oracle Projects before you
can interface it to Oracle Assets. If the project–related invoice distribution is charged to a capital project in Oracle Projects, Oracle Projects sets the ASSET_ADDITION_FLAG to P when the
PA_ADDITION_FLAG is set to Y, Z or T. Oracle Assets only picks up invoice distributions with the ASSET_ADDITION_FLAG set to U and if project–related, with the PA_ADDITION_FLAG
set to Y, Z, or T. PA_ADDITION_FLAG tracks the status of project–related supplier invoice distribution lines and expense report distribution lines.

For supplier invoice distributions entered via Oracle Payables, the PA_ADDITION_FLAG is set to N if the distribution is project–related, otherwise it is set to E and it is updated by Oracle
Projects when the distribution is processed by the Oracle Projects Interface Supplier Invoice process. Oracle Projects sets the PA_ADDITION_FLAG to Y or Z after the item is successfully
processed, or may be set to a rejection code if the line is rejected during transfer to Oracle Projects; see QuickCodes listing for all the errors. You must correct the rejection reason an try to
retransfer the line. For supplier invoice adjustment lines interfaced from Oracle Projects to Oracle Payables (which must net to zero with another line), the value for the
PA_ADDITION_FLAG is set to T. For expense report distributions interfaced from Oracle Projects to Oracle Payables via Invoice Import, this value is set to N. This row is never picked up by
the Interface Supplier Invoices process based on the AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE = EXPENSEREPORT. For expense report adjustment lines interfaced from Oracle Projects
to Oracle Payables which net to zero with another line, thisvalue is set to T. Both lines are associated with the original invoice by the Oracle Projects Interface Expense Reports to AP process.
Values for the ENCUMBERED_FLAG are as follows:

- Y indicates aregular distribution that has been successfully encumbered by Payables;

- W indicates a regular distribution that has been encumbered in advisory mode even though insufficient funds existed;

- H indicates a regular distribution that has not been encumbered because it was put on hold;

- Nor null indicates a regular line that has not been encumbered because it has not been looked at yet;

- D is the same as Y for a reversal distribution line;

- X is the same as W for a reversal distribution line;

- P is the same as H for a reversal distribution line;

- R indicates a line to be ignored by encumbrance and approval code because neither the original nor the reversal distributions were looked at and they offset each other so, they can be ignored.

AP_PAYMENT_SCHEDULES_ALL

AP_PAYMENT_SCHEDULES_ALL contains information about scheduled payments for an invoice. You need one row for each time you intend to make a payment on an invoice. Your Oracle

Payables application uses this information to determine when to make payments on an invoice and how much to pay in an automatic payment batch. Values for HOLD_FLAG may be ’Y’ to

place a hold on the scheduled payment, or ’N’ not to do so. Values for PAYMENT_STATUS_FLAG may be ’Y’ for fully paid payment schedules, ’N’ for unpaid scheduled payments, or ’P’ for
partially paid scheduled payments. For converted records, enter a value for AMOUNT_REMAINING.

AP_HOLDS_ALL

AP_HOLDS_ALL contains information about holds that you or your Oracle Payables application place on an invoice. For non–matching holds, there is one row for each hold placed on an
invoice. For matching holds, there is one row for each hold placed on an invoice–shipment match. An invoice may have one or more corresponding rows in this table. Your Oracle Payables
application does not pay invoices that have one or more unreleased holds recorded in this table. This table holds information referenced by the Invoice Holds window. In the strictest sense,
AP_HOLDS_ALL has no primary key. It is possible for your Oracle Payables application to place a certain type of hold on an invoice, then release it, then place another hold of the same type
(if data changes before each submission of Approval), which would result in a duplicate primary key. But for practical purposes, the primary key is a concatenation of INVOICE_ID,
LINE_LOCATION_ID,and HOLD_LOOKUP_CODE.

AP_AE_LINES_ALL

An accounting entry line is an entity containing a proper accounting entry with debits or credits both in transaction currency as well as functional currency along with an account and
other reference information pointing to the transaction data that originated the accounting entry line. An accounting entry line is grouped with other accounting entry lines for a specific
accounting entry header. Any such group of accounting entry lines should result in balanced entries in the functional currency.

AP_AE_HEADERS_ALL

An accounting entry header is an entity grouping all accounting entry lines created for a given accounting event and a particular set of books. An accounting entry header can either be
transferred over to GL or not at all. That is, either all its accounting entry lines are transferred or none at all. The transferred to GL status is marked in the GL_TRANSFER_FLAG. Possible
values for GL_TRANSFER_FLAG are Y, N, or E. Y indicates that the accounting entry header has been transferred to GL. N indicates that the accounting entry header has not been
transferred to GL due to 2 possible reasons: either the transfer process has not run or it has run but the accounting entry had an accounting error on it. E indicates that an error was
encountered during the transfer to GL process

Insert BLOB image file in oracle database table

2009-11-29T22:10:00.000-08:00

Step 1:

CREATE TABLE SV_EMP_PHOTO
(
ID NUMBER(3) NOT NULL,
PHOTO_NAME VARCHAR2(40),
PHOTO_RAW BLOB,
EMP_NAME VARCHAR2(80)
)

Step2:

Create a directory where the photos will be stored. I am creating a directory in UNIX as our database is created in UNIX.

CREATE OR REPLACE DIRECTORY
SV_PHOTO_DIR AS
'/usr/tmp/SV_PHOTO_DIR';

Step 3:

Copy the image file from local machine to the above created Directory using FTP.
Kindly note that Report 6i does not support to all image formats. I used here .bmp file format.

Step4:

Script to create a procedure SV_LOAD_IMAGE that will insert records in the table.

/* Formatted on 2009/11/30 11:12 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE apps.sv_load_image (
p_id NUMBER,
p_emp_name IN VARCHAR2,
p_photo_name IN VARCHAR2
)
IS
l_source BFILE;
l_dest BLOB;
l_length BINARY_INTEGER;
BEGIN
l_source := BFILENAME ('SV_PHOTO_DIR', p_photo_name);

INSERT INTO sv_emp_photo
(ID, photo_name, emp_name, photo_raw
)
VALUES (p_id, p_photo_name, p_emp_name, EMPTY_BLOB ()
)
RETURNING photo_raw
INTO l_dest;
-- lock record
SELECT photo_raw
INTO l_dest
FROM sv_emp_photo
WHERE ID = p_id AND photo_name = p_photo_name
FOR UPDATE;
-- open the file
DBMS_LOB.fileopen (l_source, DBMS_LOB.file_readonly);
-- get length
l_length := DBMS_LOB.getlength (l_source);
-- read the file and store in the destination
DBMS_LOB.loadfromfile (l_dest, l_source, l_length);
-- update the blob field with destination
UPDATE sv_emp_photo
SET photo_raw = l_dest
WHERE ID = p_id AND photo_name = p_photo_name;
-- close file
DBMS_LOB.fileclose (l_source);
END sv_load_image;

Step5:

exec sv_load_image(1,'Mr. Kamlesh Nikhade','Kamlesh.bmp');

Step6 :

select * from SV_EMP_PHOTO;

Display Image In report 6i

We will use same SV_EMP_TABLE as a reference and using Reports 6i create a report that prints ID Card. For simplicity there are no parameters to the report and it will create ID cards for all the employees for which record exists in the table.
In the data model create following SQL Query.

Step 1 :

SELECT ID, PHOTO_NAME, PHOTO_RAW ,emp_name
FROM SV_EMP_PHOTO;

Step2:

The next step is to create a layout for the report. Screenshot below.

The field that stores the value of BLOB column in the properties for the field remember to change the file format to image,
This image data will be retrieved from database and will change with record.
Procedure : Take One text field=>properties=>file format=>image.

Step 3:
Compile & Run the report.

Concurrent Program Registration Scripts

2009-11-22T21:54:00.000-08:00

Concurrent Program Registration Scripts ==========================================================

The scripts in this article can be used to:
1) Register the executable and Program
2) Attach Concurrent program to a Request Group
3) Submit Concurrent program

1) Registering the Executable from back end
================================================

Usually we create executable in the front-end, but this can be done from the database tier i.e. back-end too.
Below is the PL/SQL code to create an executable from back-end.
BEGIN
FND_PROGRAM.executable('XXMZ_ERPSCHOOLS_EMPLOYEE' -- executable
, 'XXMZ Custom' -- application
, 'XXMZ_ERPSCHOOLS_EMPLOYEE' -- short_name
, 'Executable for ERPSCHOOLS Employee INFORMATION' -- description
, 'PL/SQL Stored Procedure' -- execution_method
, 'XXMZ_ERPSCHOOLS_EMPLOYEE' -- execution_file_name
, '' -- subroutine_name
, '' -- Execution File Path
, 'US' -- language_code
, '');
COMMIT;
END;

Query in the front-end to see whether your executable is created or not.

2) Registering the Concurrent program from back end
===========================================================

Usually we create Concurrent program in the front-end, but this can be done from the database tier too.
Below is the program to create a Concurrent program from back-end.
BEGIN
FND_PROGRAM.register('Concurrent program for ErpSchools Employee Information' -- program
, 'XXMZ Custom' -- application
, 'Y' -- enabled
, 'XXMZ_ERPSCHOOLS_EMPLOYEE' -- short_name
, 'ErpSchools Employee Information' -- description
, 'XXMZ_ERPSCHOOLS_EMPLOYEE' -- executable_short_name
, 'XXMZ Custom' -- executable_application
, '' -- execution_options
, '' -- priority
, 'Y' -- save_output
, 'Y' -- print
, '' -- cols
, '' -- rows
, '' -- style
, 'N' -- style_required
, '' -- printer
, '' -- request_type
, '' -- request_type_application
, 'Y' -- use_in_srs
, 'N' -- allow_disabled_values
, 'N' -- run_alone
, 'TEXT' – output_type
, 'N' -- enable_trace
, 'Y' -- restart
, 'Y' -- nls_compliant
, '' -- icon_name
, 'US'); -- language_code
COMMIT;
END;

Query in the front-end to see whether your Concurrent program is created or not.

3) Attaching the concurrent program to the request group
==================================================================

Usually we Attach Concurrent program to the request group in the front-end, but this can be done from database tier too.
Below is the program to Attach Concurrent program to the request group from back-end.
BEGIN
FND_PROGRAM.add_to_group('XXMZ_ERPSCHOOLS_EMPLOYEE' -- program_short_name
, 'XXMZ Custom' -- application
, 'xxmz Request Group' -- Report Group Name
, 'XXMZ'); -- Report Group Application
COMMIT;
END;

Query in the front-end to see whether your Concurrent program is Attached to Request Group or not.

4) Submitting Concurrent Program from Back-end
====================================================

We first need to initialize oracle applications session using
fnd_global.apps_initialize(user_id,responsibility_id,application_responsibility_id) and then run fnd_request.submit_request
DECLARE
l_request_id NUMBER(30);
begin
FND_GLOBAL.APPS_INITIALIZE (user_id => 1318, resp_id => 59966, resp_appl_id => 20064);
l_request_id:= FND_REQUEST.SUBMIT_REQUEST ('XXMZ' --Application Short name,
'VENDOR_FORM'-- Concurrent Program Short Name );
DBMS_OUTPUT.PUT_LINE(l_request_id);
commit;
end;
*****************************************************
To get the resp_id and resp_appl_id use the below queries.
--SELECT APPLICATION_ID, RESPONSIBILITY_ID
FROM FND_RESPONSIBILITY_TL
WHERE RESPONSIBILITY_NAME='xxmz Custom'
--SELECT USER_ID FROM FND_USER WHERE USER_NAME='OPERATIONS'
*****************************************************
Once the concurrent program is submitted from back-end, status of the concurrent program can be checked using below query.
SELECT * FROM FND_CONCURRENT_REQUESTS WHERE REQUEST_ID=2766602
==============================================================================
This document is from ERP SCHOOLS
==============================================================================

2009-11-20T23:01:00.000-08:00

Oracle tuning - Tune individual SQL statements
Collected From Burleson Consulting
________________________________________
Tuning individual Oracle SQL statements
The acronym SQL stands for Structured Query Language. SQL is an industry standard database query language that was adopted in the mid-1980s. It should not be confused with commercial products such as Microsoft SQL Server or open source products such as MySQL, both of which use the acronym as part of the title of their products.
Do this before you start individual SQL statement tuning
This broad-brush approach can save thousands of hours of tedious SQL tuning because you can hundreds of queries at once. Remember, you MUST do this first, else later changes to the optimizer parameters or statistics may un-tune your SQL.

Remember, you must ALWAYS start with system-level SQL tuning, else later changes might undo your tuned execution plans:
• Optimize the server kernel - You must always tune your disk and network I/O subsystem (RAID, DASD bandwidth, network) to optimize the I/O time, network packet size and dispatching frequency.

• Adjusting your optimizer statistics - You must always collect and store optimizer statistics to allow the optimizer to learn more about the distribution of your data to take more intelligent execution plans. Also, histograms can hypercharge SQL in cases of determining optimal table join order, and when making access decisions on skewed WHERE clause predicates.

• Adjust optimizer parameters - Optimizer optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj.

• Optimize your instance - Your choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, &c), can influence SQL performance.

• Tune your SQL Access workload with physical indexes and materialized views - Just as the 10g SQLAccess advisor recommends missing indexes and missing materialized views, you should always optimize your SQL workload with indexes, especially function-based indexes, a Godsend for SQL tuning.
11g Note: The Oracle 11g SQL Performance Analyzer (SPA), is primarily designed to speed up the holistic SQL tuning process.

Once you create a workload (called a SQL Tuning Set, or STS), Oracle will repeatedly execute the workload, using sophisticated predictive models (using a regression testing approach) to accurately identify the salient changes to SQL execution plans, based on your environmental changes. Using SPA, we can predict the impact of system changes on a workload, and we can forecast changes in response times for SQL after making any change, like parameter changes, schema changes, hardware changes, OS changes, or Oracle upgrades. For details, see the book Oracle 11g New Features.
Once the environment, instance, and objects have been tuned, the Oracle administrator can focus on what is probably the single most important aspect of tuning an Oracle database: tuning the individual SQL statements. In this final article in my series on Oracle tuning, I will share some general guidelines for tuning individual SQL statements to improve Oracle performance.
Oracle SQL tuning goals
Oracle SQL tuning is a phenomenally complex subject. Entire books have been written about the nuances of Oracle SQL tuning; however, there are some general guidelines that every Oracle DBA follows in order to improve the performance of their systems. Again, see the book "Oracle Tuning: The Definitive Reference", for complete details.

The goals of SQL tuning focus on improving the execution plan to fetch the rows with the smallest number of database "touches" (LIO buffer gets and PIO physical reads).
• Remove unnecessary large-table full-table scans—Unnecessary full-table scans cause a huge amount of unnecessary I/O and can drag-down an entire database. The tuning expert first evaluates the SQL based on the number of rows returned by the query. The most common tuning remedy for unnecessary full-table scans is adding indexes. Standard b-tree indexes can be added to tables, and bitmapped and function-based indexes can also eliminate full-table scans. In some cases, an unnecessary full-table scan can be forced to use an index by adding an index hint to the SQL statement.

• Cache small-table full-table scans—In cases where a full-table scan is the fastest access method, the administrator should ensure that a dedicated data buffer is available for the rows. In Oracle8 and beyond, a small table can be cached by forcing it into the KEEP pool.

• Verify optimal index usage—Oracle sometimes has a choice of indexes, and the tuning professional must examine each index and ensure that Oracle is using the proper index.

• Materialize your aggregations and summaries for static tables - One features of the Oracle 10g SQLAccess advisor is recommendations for new indexes and suggestions for materialized views. Materialized views pre-join tables and pre-summarize data, a real silver bullet for data mart reporting databases where the data is only updated daily. Again, see the book "Oracle Tuning: The Definitive Reference", for complete details on SQL tuning with materialized views.
These are the goals of SQL tuning in a nutshell. However, they are deceptively simple, and to effectively meet them, we need to have a through understanding of the internals of Oracle SQL. Let's begin with an overview of the Oracle SQL optimizers.
Oracle SQL optimizers
One of the first things the Oracle DBA looks at is the default optimizer mode for the database. The Oracle initialization parameters offer many cost-based optimizer modes as well as the deprecated yet useful rule-based hint:
The cost-based optimizer uses “statistics” that are collected from the table using the “analyze table” command. Oracle uses these metrics about the tables in order to intelligently determine the most efficient way of servicing the SQL query. It is important to recognize that in many cases, the cost-based optimizer may not make the proper decision in terms of the speed of the query. The cost-based optimizer is constantly being improved, but there are still many cases in which the rule-based optimizer will result in faster Oracle queries.
Prior to Oracle 10g, Oracle's default optimizer mode was called “choose.” In the choose optimizer mode, Oracle will execute the rule-based optimizer if there are no statistics present for the table; it will execute the cost-based optimizer if statistics are present. The danger with using the choose optimizer mode is that problems can occur in cases where one Oracle table in a complex query has statistics and the other tables do not.
Starting in Oracle 10g, the default optimizer mode is all_rows, favoring full-table scans over index access. The all_rows optimizer mode is designed to minimize computing resources and it favors full-table scans. Index access (first_rows_n) adds additional I/O overhead, but they return rows faster, back to the originating query:

Full-table scans touch all data blocks
Hence, many OLTP shops will choose first_rows, first_rows_100 or first_rows_10, asking Oracle to use indexes to reduce block touches:

Index scans return rows fast by doing additional I/O
Note: Staring in Oracle9i release 2, the Oracle performance tuning guide says that the first_rows optimizer mode has been deprecated and to use first_rows_n instead.
When only some tables contain CBO statistics, Oracle will use the cost-based optimization and estimate statistics for the other tables in the query at runtime. This can cause significant slowdown in the performance of the individual query.
In sum, the Oracle database administrator will always try changing the optimizer mode for queries as the very first step in Oracle tuning. The foremost tenet of Oracle SQL tuning is avoiding the dreaded full-table scan. One of the hallmarks of an inefficient SQL statement is the failure of the SQL statement to use all of the indexes that are present within the Oracle database in order to speed up the query.

Of course, there are times when a full-table scan is appropriate for a query, such as when you are doing aggregate operations such as a sum or an average, and the majority of the rows within the Oracle table must be read to get the query results. The task of the SQL tuning expert is to evaluate each full-table scan and see if the performance can be improved by adding an index.
In most Oracle systems, a SQL statement will be retrieving only a small subset of the rows within the table. The Oracle optimizers are programmed to check for indexes and to use them whenever possible to avoid excessive I/O. However, if the formulation of a query is inefficient, the cost-based optimizer becomes confused about the best access path to the data, and the cost-based optimizer will sometimes choose to do a full-table scan against the table. Again, the general rule is for the Oracle database administrator to interrogate the SQL and always look for full-table scans.
For the full story, see my book "Oracle Tuning: The Definitive Reference" for details on choosing the right optimizer mode.
A strategic plan for Oracle SQL tuning

Many people ask where they should start when tuning Oracle SQL. Tuning Oracle SQL is like fishing. You must first fish in the Oracle library cache to extract SQL statements and rank the statements by their amount of activity.

Step 1—Identify high-impact SQL

The SQL statements will be ranked according the number of executions and will be tuned in this order. The executions column of the v$sqlarea view and the stats$sql_summary or the dba_hist_sql_summary table can be used to locate the most frequently used SQL. Note that we can display SQL statements by:
• Rows processed—Queries that process a large number of rows will have high I/O and may also have impact on the TEMP tablespace.

• Buffer gets—High buffer gets may indicate a resource-intensive query.

• Disk reads—High disk reads indicate a query that is causing excessive I/O.

• Memory KB—The memory allocation of a SQL statement is useful for identifying statements that are doing in-memory table joins.

• CPU secs—This identifies the SQL statements that use the most processor resources.

• Sorts—Sorts can be a huge slowdown, especially if they’re being done on a disk in the TEMP tablespace.

• Executions—The more frequently executed SQL statements should be tuned first, since they will have the greatest impact on overall performance.

Step 2—Determine the execution plan for SQL

As each SQL statement is identified, it will be “explained” to determine its existing execution plan. There are a host of third-party tools on the market that show the execution plan for SQL statements. The most common way of determining the execution plan for a SQL statement is to use Oracle's explain plan utility. By using explain plan, the Oracle DBA can ask Oracle to parse the statement and display the execution class path without actually executing the SQL statement.

To see the output of an explain plan, you must first create a “plan table.” Oracle provides a script in $ORACLE_HOME/rdbms/admin called utlxplan.sql. Execute utlxplan.sql and create a public synonym for the plan_table:

sqlplus > @utlxplan
Table created.

sqlplus > create public synonym plan_table for sys.plan_table;
Synonym created.
Most relational databases use an explain utility that takes the SQL statement as input, runs the SQL optimizer, and outputs the access path information into a plan_table, which can then be interrogated to see the access methods. Listing 1 runs a complex query against a database.

EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR
SET STATEMENT_ID = 'RUN1'
INTO plan_table
FOR
SELECT 'T'||plansnet.terr_code, 'P'||detplan.pac1
|| detplan.pac2 || detplan.pac3, 'P1', sum(plansnet.ytd_d_ly_tm),
sum(plansnet.ytd_d_ty_tm),
sum(plansnet.jan_d_ly),
sum(plansnet.jan_d_ty),
FROM plansnet, detplan
WHERE
plansnet.mgc = detplan.mktgpm
AND
detplan.pac1 in ('N33','192','195','201','BAI',
'P51','Q27','180','181','183','184','186','188',
'198','204','207','209','211')
GROUP BY 'T'||plansnet.terr_code, 'P'||detplan.pac1 || detplan.pac2 || detplan.pac3;
This syntax is piped into the SQL optimizer, which will analyze the query and store the plan information in a row in the plan table identified by RUN1. Please note that the query will not execute; it will only create the internal access information in the plan table. The plan tables contains the following fields:
• operation—The type of access being performed. Usually table access, table merge, sort, or index operation

• options—Modifiers to the operation, specifying a full table, a range table, or a join

• object_name—The name of the table being used by the query component

• Process ID—The identifier for the query component

• Parent_ID—The parent of the query component. Note that several query components may have the same parent.
Now that the plan_table has been created and populated, you may interrogate it to see your output by running the following query in Listing 2.
plan.sql - displays contents of the explain plan table
SET PAGES 9999;
SELECT lpad(' ',2*(level-1))||operation operation,
options,
object_name,
position
FROM plan_table
START WITH id=0
AND
statement_id = 'RUN1'
CONNECT BY prior id = parent_id
AND
statement_id = 'RUN1';
Listing 3 shows the output from the plan table shown in Listing 1. This is the execution plan for the statement and shows the steps and the order in which they will be executed.
SQL> @list_explain_plan

OPERATION
-------------------------------------------------------------------------------------
OPTIONS OBJECT_NAME POSITION
------------------------------ -------------------------------------------------------
SELECT STATEMENT
SORT
GROUP BY 1
CONCATENATION 1
NESTED LOOPS 1
TABLE ACCESS FULL PLANSNET 1
TABLE ACCESS BY ROWID DETPLAN 2
INDEX RANGE SCAN DETPLAN_INDEX5 1
NESTED LOOPS

From this output, we can see the dreaded TABLE ACCESS FULL on the PLANSNET table. To diagnose the reason for this full-table scan, we return to the SQL and look for any plansnet columns in the WHERE clause. There, we see that the plansnet column called “mgc” is being used as a join column in the query, indicating that an index is necessary on plansnet.mgc to alleviate the full-table scan.

While the plan table is useful for determining the access path to the data, it does not tell the entire story. The configuration of the data is also a consideration. The SQL optimizer is aware of the number of rows in each table (the cardinality) and the presence of indexes on fields, but it is not aware of data distribution factors such as the number of expected rows returned from each query component.

Step 3—Tune the SQL statement

For those SQL statements that possess a sub-optimal execution plan, the SQL will be tuned by one of the following methods:

• Adding SQL “hints” to modify the execution plan

• Re-write SQL with Global Temporary Tables
• Rewriting the SQL in PL/SQL. For certain queries this can result in more than a 20x performance improvement. The SQL would be replaced with a call to a PL/SQL package that contained a stored procedure to perform the query.
Using hints to tune Oracle SQL

Among the most common tools for tuning SQL statements are hints. A hint is a directive that is added to the SQL statement to modify the access path for a SQL query.
Troubleshooting tip! For testing, you can quickly test the effect of another optimizer parameter value at the query level without using an “alter session” command, using the new opt_param SQL hint:
select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .
select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .
Oracle publishes many dozens of SQL hints, and hints become increasingly more complicated through the various releases of Oracle and on into Oracle.
Note: Hints are only used for de-bugging and you should adjust your optimizer statistics to make the CBO replicate the hinted SQL. Let’s look at the most common hints to improve tuning:
• Mode hints: first_rows_10, first_rows_100
• Oracle leading and ordered hints Also see how to tune table join order with histograms

• Dynamic sampling: dynamic_sampling

• Oracle SQL undocumented tuning hints - Guru's only
• The cardinality hint

• Self-order the table joins - If you find that Oracle is joining the tables together in a sub-optimal order, you can use the ORDERED hint to force the tables to be joined in the order that they appear in the FROM clause. See

• Try a first_rows_n hint. Oracle has two cost-based optimizer modes, first_rows_n and all_rows. The first_rows mode will execute to begin returning rows as soon as possible, whereas the all_rows mode is designed to optimize the resources on the entire query before returning rows.

SELECT /*+ first_rows */

A case study in SQL tuning

One of the historic problems with SQL involves formulating SQL queries. Simple queries can be written in many different ways, each variant of the query producing the same result—but with widely different access methods and query speeds.

For example, a simple query such as “What students received an A last semester?” can be written in three ways, as shown in below, each returning an identical result.

A standard join:

SELECT *
FROM STUDENT, REGISTRATION
WHERE
STUDENT.student_id = REGISTRATION.student_id
AND
REGISTRATION.grade = 'A';

A nested query:

SELECT *
FROM STUDENT
WHERE
student_id =
(SELECT student_id
FROM REGISTRATION
WHERE
grade = 'A'
);

A correlated subquery:

SELECT *
FROM STUDENT
WHERE
0 <
(SELECT count(*)
FROM REGISTRATION
WHERE
grade = 'A'
AND
student_id = STUDENT.student_id
);
Let’s wind up with a review of the basic components of a SQL query and see how to optimize a query for remote execution.

Tips for writing more efficient SQL

Space doesn’t permit me to discuss every detail of Oracle tuning, but I can share some general rules for writing efficient SQL in Oracle regardless of the optimizer that is chosen. These rules may seem simplistic but following them in a diligent manner will generally relieve more than half of the SQL tuning problems that are experienced:
• Rewrite complex subqueries with temporary tables - Oracle created the global temporary table (GTT) and the SQL WITH operator to help divide-and-conquer complex SQL sub-queries (especially those with with WHERE clause subqueries, SELECT clause scalar subqueries and FROM clause in-line views). Tuning SQL with temporary tables (and materializations in the WITH clause) can result in amazing performance improvements.

• Use minus instead of EXISTS subqueries - Some say that using the minus operator instead of NOT IN and NOT Exists will result in a faster execution plan.

• Use SQL analytic functions - The Oracle analytic functions can do multiple aggregations (e.g. rollup by cube) with a single pass through the tables, making them very fast for reporting SQL.

• Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins - In many cases of NOT queries (but ONLY where a column is defined as NULL), you can re-write the uncorrelated subqueries into outer joins with IS NULL tests. Note that this is a non-correlated sub-query, but it could be re-written as an outer join.
select book_key from book
where
book_key NOT IN (select book_key from sales);
Below we combine the outer join with a NULL test in the WHERE clause without using a sub-query, giving a faster execution plan.
select b.book_key from book b, sales s
where
b.book_key = s.book_key(+)
and
s.book_key IS NULL;
• Index your NULL values - If you have SQL that frequently tests for NULL, consider creating an index on NULL values. To get around the optimization of SQL queries that choose NULL column values (i.e. where emp_name IS NULL), we can create a function-based index using the null value built-in SQL function to index only on the NULL columns.

• Leave column names alone - Never do a calculation on an indexed column unless you have a matching function-based index (a.k.a. FBI). Better yet, re-design the schema so that common where clause predicates do not need transformation with a BIF:
where salary*5 > :myvalue
where substr(ssn,7,4) = "1234"
where to_char(mydate,mon) = "january"
• Avoid the use of NOT IN or HAVING. Instead, a NOT EXISTS subquery may run faster (when appropriate).

• Avoid the LIKE predicate = Always replace a "like" with an equality, when appropriate.

• Never mix data types - If a WHERE clause column predicate is numeric, do not to use quotes. For char index columns, always use quotes. There are mixed data type predicates:
where cust_nbr = "123"
where substr(ssn,7,4) = 1234
• Use decode and case - Performing complex aggregations with the “decode” or "case" functions can minimize the number of times a table has to be selected.

• Don't fear full-table scans - Not all OLTP queries are optimal when they uses indexes. If your query will return a large percentage of the table rows, a full-table scan may be faster than an index scan. This depends on many factors, including your configuration (values for db_file_multiblock_read_count, db_block_size), query parallelism and the number of table/index blocks in the buffer cache.

• Use those aliases - Always use table aliases when referencing columns.

SQL And PL/SQL Coding Standards

2009-11-16T22:43:00.000-08:00

PL/SQL has become a mature and popular language since its introduction in 1991, yet the language is still relatively young compared to its ancestors (both direct ancestors, like Ada, and indirect ancestors, like COBOL and Fortran). One of the most common questions I am asked is about coding standards for SQL and PL/SQL.
The primary purpose of coding standards has always been to make maintenance easier for developers. In order to satisfy this requirement, PL/SQL coding standards must address several areas of the development process. Table D.1 displays some of the development areas that PL/SQL coding standards address.
Table D.1 Development areas addressed by coding standards. ________________________________________
Development Area Includes
Vertical spacing Spacing between statements; spacing between procedures and functions within package bodies.
Horizontal spacing Spacing between identifiers and operators; number of statements per line; maximum line width.
Procedural calls Use of positional or named notation when calling stored PL/SQL objects.
Commenting Type of comments to be used and the frequency, spacing, positioning, and content of comments.
Code reuse/modularity Contents of procedures and functions; organization of procedures and functions into packages.
Identifiers Rules for naming identifiers; rules for naming stored PL/SQL objects.
SQL statements Formatting rules for embedded SQL statements.
Performance Performance tips (particularly for embedded SQL statements).
Debugging A standard method of handling exceptions inside PL/SQL objects as well as inside other applications that call stored PL/SQL objects.
Testability Rules for writing stored PL/SQL objects so that unit testing can be accomplished.
Development environment Rules determining what tools will be used for application development and what processes must be followed by developers.
Capitalization Rules determining which keywords will be used in UPPER case, Mixed case, and lower case.
Conformance to standards Rules designating when it is allowable for code to not agree with the coding standard.
Documentation Rules that designate whether the coding standards will be applied to design documents and what type of documentation must exist for particular routines.

The level of specifics contained in a coding standard should be fairly strict. While this may seem to be a burden during the development process, the people who do maintenance down the line will appreciate adherence to the standards.
The best way to ensure adherence to standards is to use structured peer review when a code module is completed. If the code is readable, the peer review process will flow more smoothly, and the reviewers will be able to concentrate their review time on understanding the intimacies of code rather than on deciphering an entry for the obfuscated PL/SQL contest! Peer reviews also provide a last line of defense against “sleeper” bugs (often overlooked by even the best developers), which are found a lot more easily when the code is readable.
If your organization does some or all of its own SQL and PL/SQL training, you should make an effort to incorporate standards training into your course materials.
I hope you find the examples presented in this text easy to read and understand. I also hope that you notice the consistency of style in the way examples appear. Every piece of code on the CD-ROM and in the text conforms to the following coding standard.
A Sample SQL And PL/SQL Coding Standard
This document defines the SQL and PL/SQL environment and programming standards and procedures for . The standards established in this document apply to all SQL and PL/SQL development efforts.
These PL/SQL coding standards were written to allow for consistency in PL/SQL code written by various developers while allowing for some individual styles and preferences to be expressed. The central purpose of any coding standard for SQL and PL/SQL must deal with database performance, clarity of code, and maintainability of code. This standard should be considered a guideline for developing easily maintainable SQL and PL/SQL applications in a high-performance Oracle database.
Developers should attempt to meet the spirit of this document by applying good judgment, rather than strictly adhering to the letter of the standard. This standard applies to all developer-written SQL and PL/SQL code (including scripts, stored procedures and functions, database triggers, and stored packages). Generated code is not governed by this standard.
This document is a living document that evolves based on the experiences of you, the developer. You should be aware that changes may occur to this document in the future, based on your (or other developers’) experiences and insights.
The Development Environment And Processes
This section of the standard is highly dependent on the nature of your organization; therefore, I will only give suggestions on the types of material that should be included in this section of your coding standard. The Development Environment And Processes section of the standard should address the following issues:
• Your version control processes—A good working knowledge of PL/SQL is important to a developer, but the inability of a single developer to follow version control processes could be disastrous!
• Standard tools for your development efforts—If you have a standard configuration for your tools, it should be described in painstaking detail. If your tools support central administration of this configuration, so much the better.
• Peer review practices—Many organizations have formal peer review processes in place to validate the quality of design documents, code, and test results. If your organization uses these processes, the coding standard should (at the very least) point developers to your process documents.
• Documentation—Describe the documentation required when developers create new code and what documents need to be updated when maintenance is performed. Provide reasonable facsimiles of this documentation (or excerpts from real documents that satisfy your requirements).
• Testing standards in place—Provide examples of thorough test scripts and plans. Tests for each aspect of your system (front end, stored procedures and functions, packages, and database triggers) should be discussed in detail.
• Standard routines—You should describe whether standard routines, such as error handling, help system calls, and so forth, are available and when these routines should be used. Provide a detailed explanation of each standard call and its interfaces.
These issues can be addressed in appendices to your coding standard or in other documents, as long as your developers receive the necessary information.
________________________________________
References To Other Documents
I personally favor one stop shopping; pack the standard with as much information as possible and reduce the amount of time developers spend tracking down other documents.
________________________________________
Programming Design Standards
Developers should design for modularity. Black box is a term often used in conjunction with modules; each module should perform one (and only one) function using a defined interface and produce a predictable result. So long as the interface for a code module is not changed, the code module may be altered without affecting outside code.
Each module contains one or more routines (and the data structures and variables needed to support the routines). PL/SQL allows developers to implement modularity through the use of packages, which can contain procedures and functions as well as global type, variable, and constant declarations.
Stored functions that use parameters of the IN OUT and OUT types are not allowed. Stored functions should use only the RETURN statement to return a value. Developers are encouraged to identify routines that can be reused. This code can be centralized, tested, and used by other developers to improve the reliability of system code and to reduce development time for more complex modules.
________________________________________
The Modularity Ombudsman
If your organization consists of ten or more people, it might be a good idea to appoint a “Modularity Ombudsman” who has some experience with SQL and PL/SQL. The responsibilities of this position include:
• Identifying code segments that can be reused
• Developing and testing reusable modules
• Documenting modules and promoting their use
The modularity ombudsman should also take part in peer review for new modules to help increase code reuse throughout your project.
________________________________________
Headers
A header should appear at the start of any script, procedure, function, package body, or package spec. Consider this template header:
-- *****************************************************************
-- Description: Describe the purpose of the object. If necessary,
-- describe the design of the object at a very high level.
--
-- Input Parameters:
--
-- Output Parameters:
--
-- Error Conditions Raised:
--
-- Author:
--
-- Revision History
-- Date Author Reason for Change
-- ----------------------------------------------------------------
-- 03 JAN 1997 J.Schmoe Created.
-- *****************************************************************
________________________________________
The Usefulness Of Headers
There are some people who dislike headers or feel that a header is a useless burden to place on a developer. I disagree: At no other point in the code are provisions made for documenting the overall purpose, logic, and interface of a module. In my opinion, a header is the most essential documentation for any piece of stored code.
________________________________________
Formatting Guidelines For SQL And PL/SQL Statements
These guidelines are provided to give code a generally consistent appearance, including indentation, horizontal alignment, and vertical alignment. Adherence to these standards will make code more readable and more easily understood when maintenance is necessary.
Alignment Of Operators
These guidelines enhance the readability of code by adding white space and clarifying complex expressions.
• Arrange series of statements containing similar operators into columns whenever it will not cause excessive white space and you have sufficient room to do so.

Correct:
vFirstName := 'Roger';
vLastName := 'Smith';
vSSN := 999999999;

Incorrect:
vFirstName := 'Roger';
vLastName := 'Smith';
vSSN := 999999999;
• Always use parentheses in expressions containing more than one identifier or literal. This clarifies code for inexperienced developers who are not familiar with operator precedence and helps eliminate the possibility that you’ve overlooked something in your equation.

Correct:
IF (nSSN < 2.5) THEN

END IF;

Incorrect:
IF nSSN < 2.5 THEN

END IF;
• Align the IN and OUT keywords in columns when defining the interface for a procedure or function.

Correct:
PROCEDURE Days_Between (dStartDate IN date,
dEndDate IN date,
nGPA IN OUT number,
nDaysBetween OUT number)

Incorrect:
PROCEDURE Days_Between (dStartDate IN date,
dEndDate IN date,
nGPA IN OUT number,
nDaysBetween OUT number)

• When calling a procedure or function, align the parameters into a column. This reduces the visual clutter around the call, making it stand out from the rest of the code.

Correct:
DaysBetween (dStartDate => dEnrolledDate,
dEndDate => dGraduationDate,
nGPA => nFinalGPA,
nDaysBetween => nDuration);

Incorrect:
DaysBetween (dStartDate => dEnrolledDate,
dEndDate => dGraduationDate,
nGPA => nFinalGPA,
nDaysBetween => nDuration);
Capitalization
Table D.2 contains a list of keywords that should always be fully capitalized when referenced in code. Some of these keywords are commonly used reserved words; reserved words that do not appear on this list should be capitalized as well.
Table D.2 Capitalize these keywords. ________________________________________
ALL FALSE MINUS ROWTYPE
AND FETCH NOT SELECT
AS FOR NOTFOUND SET
BEGIN FOUND NULL SQLCODE
BETWEEN FROM OPEN SQLERRM
BODY FUNCTION OR TABLE
CLOSE GOTO ORDER BY THEN
COMMIT GROUP BY OUT TYPE
CONSTANT HAVING PACKAGE UNION
CREATE IF PROCEDURE UNION ALL
DECLARE IN RAISE UPDATE
DELETE INSERT REPLACE VALUES
ELSE INTERSECT RETURN VIEW
ELSIF INTO ROLLBACK WHEN
END IS ROWCOUNT WHERE
EXCEPTION LIKE ROWID WHILE
EXIT LOOP ROWNUM

The keyword REPLACE is to be used in uppercase only when used as part of the CREATE OR REPLACE clause that is used to create a stored PL/SQL object. Calls to the SQL function replace() should not be presented in uppercase.
In addition to the keywords presented in Table D.2, fully capitalize all of the following:
• The names of all standard exceptions (NO_DATA_FOUND, OTHERS, TOO_MANY_ROWS), and all user-defined exceptions.
• The names of all constants and all user-defined datatypes.
• All acronyms (ANSI, ASCII, HUD, NASA, NOAA, YMCA, and so forth).
• The names of all tables, snapshots, and views, as well as the aliases given to these objects in queries.
• The names of all database triggers.
Use mixed case to refer to the names of user-defined procedures and functions (functions provided by SQL*Plus and PL/SQL are still referenced in lower case). For example:
Calculate_GPA
DBMS_Output.Put_Line
Optionally, use mixed case for user-defined identifiers. If you choose this method, use capital letters to help make the identifier names more meaningful by visually breaking variable names into words; here are some examples:
vString
nBaseSalary
nGPA
iTardyDays
iClassNumber
lComments
rStudentPhoto
All text not handled by these rules should use lowercase. Consider the following:
CREATE OR REPLACE PACKAGE My_Sample_Package AS
PROCEDURE My_Sample_Procedure (nParameter1 IN number,
nParameter2 OUT number)

IS

YES CONSTANT char (1) := 'Y';

BEGIN

IF (some expression) THEN
replace (vString, chr (9), ' ');
END IF;

END My_Sample_Procedure;
END My_Sample_Package;
Comments
As much as some developers dislike the task, commenting code is essential if the code is going to be maintained. There are a number of steps that can be taken to make comments less necessary:
• Use meaningful identifiers for variables, constants, and parameters. If you use abbreviations to compose identifiers, use the abbreviations consistently (e.g., don’t use both ADDR and ADRS to signify ADDRESS).
• Use the named parameter style of executing procedures and functions. This is especially effective if both the parameters and the variables passed to the stored PL/SQL object have meaningful identifiers.
• Comments about revisions belong in the prologue, not in the body of the module.
________________________________________
TIP: Commenting Changes And Problem Tracking
If you’re using a problem-tracking system on your project, it’s better to reference a particular report from that system and provide a brief summary of the changes made to solve that problem. Don’t attempt to include all the information about the problem in the prologue; that’s why you bought a problem tracking system!
________________________________________
• Break complex equations and formulas into several smaller statements.
• Reuse existing functions and procedures to accomplish your tasks. Identify code that can be reused.
There are a number of locations in PL/SQL code where comments should almost always be used, including the following instances:
• Before each loop structure.
• Before each BEGIN…END sub-block.
• Before each conditional logic expression (IF THEN).
• Before any other logically significant statements.
Do not comment each line of code! Only comment important parts of your code, explaining why the code is written in a particular way. Explain business rules if possible. Never use a comment to restate the actions of a piece of code.
PL/SQL supports the following two styles of commenting:
/* We need to determine which students are in academic trouble. */
-- We need to determine which students are in academic trouble.
PL/SQL does not support the nesting of C-style comments; you cannot comment out a C-style comment using other C-style comments. For this reason, it is strongly recommended that only the double-dash (--) style of commenting be used except when commenting out blocks of code.
The exception to this rule is inside 3GL programs that use the Oracle Precompilers. The Oracle Precompilers don’t support single line comments. On these occasions, use the commenting style most appropriate to the 3GL.
If a comment is required, place the comment on the line immediately preceding the line of code. Do not append comments to the end of code; if a comment is warranted by the complexity of the code and you have used meaningful identifiers, the comment should be complicated enough that you need to explain the situation using more than one or two words.
Correct:
--
-- Determine which students might be in trouble academically. We want
-- to help them perform better in school.
--
IF (some condition) THEN
Incorrect:
IF (some condition) THEN -- who's got bad grades?
All comments should use proper grammar, punctuation, and spelling. Comments should be complete, coherent sentences.
________________________________________
TIP: Volume Of Comments
As a general rule, about one-third of your final code should be comments. This figure often varies depending on the size and complexity of the code, but is an excellent rule of thumb.
________________________________________
Indentation
The most important element in readable code is consistent indentation, which illustrates clearly the logic flow of a procedure. Consider these blocks of code:
IF (x < 7) THEN
IF (y < 0) THEN

END IF;
ELSIF (x > 10) THEN

ELSE

END IF;

IF (x < 7) THEN
IF (y < 0) THEN

END IF;
ELSIF (x > 10) THEN

ELSE

END IF;
Horizontal alignment in the second block of code makes it much easier to follow, even though it is syntactically and functionally identical to the first block of code.
You should not use more than three or four levels of indentation in any block of code. If this many levels of indentation become necessary, consider breaking the code into smaller modules. Too many levels of indentation is almost as bad as no indentation at all.
• Code should always be indented consistently, using three spaces for each level of indentation. Variable, type, and constant declarations should all be indented to the first level of indentation. Do not use tab characters.
IF (some expression) THEN
IF (some expression) THEN
IF (some expression) THEN

ELSIF (some expression) THEN

END IF;
END IF;
END IF;

• Statements following the WHEN clause of an exception handler should be indented five spaces, in order to create a column-like effect within the exception handler.

Correct:
EXCEPTION
WHEN OTHERS THEN
DBMS_Output.Put_Line (SQLERRM);

Incorrect:
EXCEPTION
WHEN OTHERS THEN
DBMS_Output.Put_Line (SQLERRM);
Horizontal Spacing
You’ve probably heard of the obfuscated C contest, where the entrants attempt to cram as much code as possible onto a single line that does some type of work while remaining completely unreadable. If you have any experience maintaining code, you’ve probably seen more than a few pieces of code that have a good chance of winning a contest like this. The guidelines presented in this section are an attempt to guide you away from writing hard-to-read code.
• One of the most important elements in creating readable code is the spacing placed around operators. Table D.3 shows common operators and keywords that need to be preceded and followed by a space when they are used in expressions.

Table D.3 Operators and keywords to be preceded and followed by a space when used in expressions. ________________________________________
+ - * /
& < > =
!= <= >= :=
=> || .. :
<> IN OUT AND
OR NOT NULL

Often more than one of the operators and keywords shown in Table D.3 will be adjacent to each other inside an expression. In this instance, it is recommended that only one space lie between the two operators/identifiers. For example:
IF (vMajor IS NOT NULL) THEN
• Spaces should precede and follow character (') literals.
SELECT first_name || ' ' || middle_name || ' ' || last_name
'student_name'
FROM STUDENTS
WHERE ssn = 999999999;
• Do not leave any blank spaces preceding or following the ** operator.
nRaisedNum := nNum**nPower;
• Do not leave blank spaces before or after the plus (+) and minus (-) signs when used as unary operators.
nNumber := -nSecondNumber;
nNumber := +nSecondNumber;
• Do not use spaces between multiple parentheses or semicolons (;). Always precede the first opening parenthesis of a set with a space.
AND (((x < 5) AND (y < 5))
OR ((x > 5) AND (y > 5)));
Vertical Spacing
Vertical spacing helps distance elements in the code from one another, reducing the visual clutter above and below statements. To create appropriate vertical spacing for your code, place a blank line in the locations described in the following list:
• Before lines containing the keywords IF, ELSE, ELSIF, and EXCEPTION. If the line is preceded by a comment, place the blank line before the comment instead of before the line of text.
--
-- If the student's grade point average meets the criteria for
-- mandatory academic counseling, add the student's name and social
-- security number to the list.
--
IF (nRealGPA < 1.5) THEN

--
-- We also want to consider students who are failing two or more
-- classes, even if their GPA is above 1.5.
--
ELSIF Has_Two_Fails (nForSSN => nSSN) THEN

ELSE

END IF;
• Before any line containing the LOOP keyword. Do not place a blank line before source code containing the END LOOP keyword. (As with lines of code containing the IF keyword, keep the comments for a line of code with the comment by placing a blank line before the comment.)
--
-- For each student returned by the query, add the student's social
-- security number to the PL/SQL table.
--
FOR Students_rec IN Students_cur LOOP

END LOOP;
• Before each exception after the first declared within the EXCEPTION section of a PL/SQL block.
EXCEPTION
WHEN NO_DATA_FOUND THEN

WHEN TOO_MANY_ROWS THEN

WHEN OTHERS THEN

• Before and after the variable, constant, and type declarations for a PL/SQL block.
PROCEDURE Update_Student_GPA (nSSN IN number)
IS

BEGIN
;
END Update_Student_GPA;
• Following the declaration of the procedure and its parameters.
PROCEDURE Update_Student_GPA (nSSN IN number)

IS
• Do not place an empty line before a line containing the END IF keyword. Do place blank lines after the last line of code containing the END IF keyword.
IF (some expression) THEN

IF (some expression) THEN

IF (some expression) THEN

END IF;
END IF;
END IF;

Named Vs. Positional Notation
Procedures and functions should always be called using named notation for their parameters. This helps identify the data that is being passed to the stored PL/SQL object (assuming that the identifiers chosen for the parameters are meaningful). Place only one parameter on each line of the call:
DaysBetween (dStartDate => dEnrolledDate,
dEndDate => dGraduationDate,
nGPA => nFinalGPA,
nDaysBetween => nDuration);
Statements Per Line And Line Width
Place only one statement (or part thereof) per line of code. Break compound statements over multiple lines. Do not exceed a maximum line width of 80 characters (including indentation). Due to space limitations, the lines in this book are limited to a maximum of 70 characters.
If your code has a complex equation or formula that is expressed using a single statement or operation, consider breaking the code into several smaller statements to make the operations less intimidating. The equation will be much easier to debug; in addition, the process of breaking the equation into subsections will increase your awareness of any mistakes that you make.
SQL Statement Formatting Rules
The formatting of SQL statements is one of the few areas in which this standard can be “the law.” All SQL statements should conform precisely to these standards to the maximum extent possible; even slight deviations can have a performance impact on an Oracle database.
Oracle caches individual SQL statements within its shared global area (SGA). When a new statement is issued, Oracle does a block comparison of the statement against statements in the SGA. If a match is found, Oracle re-executes the stored version of the statement, rather than parsing the new statement and then executing it.
For instance, presume that the SGA contains the following SQL statement:
SELECT name, ssn, grade
FROM STUDENT_GRADES
WHERE grade > 90;
Now presume that someone executes this statement:
SELECT name, ssn, grade
FROM STUDENT_GRADES
WHERE grade > 90;
The second statement, although functionally identical to the statement already existing in the SGA, would not find a match in the SGA and would be parsed to determine the proper execution plan for the statement, thus increasing the overhead of the statement and slowing the response to the user.
To make matters worse, the first (and the correct) statement might be pushed out of the SGA to make room for the new statement, further impacting the processing of code that does follow the standard. The placement of a single space or shifting the case of a single character will force an SQL statement to be parsed and placed in the SGA instead of finding a matching statement in the SGA, due to the nature of the block comparison algorithm that Oracle uses.
Admittedly, the time required to parse a single statement to determine an execution plan is very small, but with tens or hundreds of users hitting the database at the same time, those milliseconds add up very quickly. Performance improvements are sometimes achieved in one fell swoop, but a lot of performance tuning work is the result of painstakingly wringing small improvements out of a lot of different pieces of code.
A number of examples are listed in the following sections. In each section, one example is labeled Correct and the other examples are labeled Incorrect. The example labeled correct is the only acceptable format for statements of the type, even though the other statements shown (and many variations not shown) are functionally equivalent to the “correct” statement.
DELETE Statements
Correct:
DELETE
FROM STUDENTS
WHERE ssn = 999999999;
Incorrect:
DELETE
FROM STUDENTS
WHERE ssn = 999999999;
Incorrect:
DELETE STUDENTS WHERE ssn = 999999999;
INSERT Statements
Correct:
INSERT
INTO STUDENTS
(ssn,
first_name,
last_name,
...
most_recent_gpa)
VALUES (999999999,
'Roger',
'Smith',
...
NULL);
Incorrect:
INSERT
INTO STUDENTS (ssn, first_name, last_name,... most_recent_gpa)
VALUES (999999999, 'Roger', 'Smith',...NULL);
Incorrect:
INSERT INTO STUDENTS
VALUES (999999999, 'Roger', 'Smith', ... NULL);
Incorrect:
INSERT INTO STUDENTS VALUES (999999999, 'Roger', 'Smith',...NULL);
SELECT Statements
Correct:
SELECT last_name, first_name, middle_name, ssn, most_recent_gpa
FROM STUDENTS
WHERE ssn = 999999999;
Incorrect:
SELECT last_name, first_name FROM STUDENTS
WHERE
ssn = 999999999;
Incorrect:
SELECT ssn, most_recent_gpa
FROM STUDENTS
WHERE most_recent_gpa < 2.0;
UPDATE Statements
Correct:
UPDATE STUDENTS
SET apartment_number = 'H',
street_address = '16 Northwest Main Street'
WHERE ssn = 999999999;
Incorrect:
UPDATE STUDENTS
SET apartment_number = 'H',
street_address = '16 Northwest Main Street'
WHERE ssn = 999999999;
Incorrect:
UPDATE STUDENTS
SET apartment_number = 'H', street_address = '16 Northwest Main
Street'
WHERE ssn = 999999999;
PL/SQL Naming Conventions
Using a set of naming conventions for PL/SQL objects tends to create more meaningful identifiers. This section of the standard will come into play most often when creating identifiers (variables and constants) inside blocks of code.
It is common to utilize abbreviations to shorten identifiers. When doing so, the abbreviations should be meaningful and used consistently (e.g., do not use both ADDR and ADRS as an abbreviation for ADDRESS).
Database Triggers
Database triggers are named using this convention:
table_name + trigger_type_extension
In this example, table_name is the name of the trigger’s base table, and trigger_type_extension represents one of the four types of database triggers listed in Table D.4, plus one or more of the letters shown in Table D.5 to indicate which DML statements cause the trigger to fire.
Table D.4 The four types of database triggers. ________________________________________
Trigger Type Extension Fires
Before statement _B Once, before the DML statement acting on the table.
Before row _BR Once for each row affected by the DML statement, before the DML statement is executed.
After row _AR Once for each row affected by the DML statement, after the DML statement is executed.
After statement _A Once, after the DML statement has finished executing.

Table D.5 Modifications to trigger_type_extension to indicate DML statements handled by the trigger. ________________________________________
Trigger Type Extension DML Statement
D The trigger fires when a DELETE statement modifies the base table.
I The trigger fires when an INSERT statement modifies the base table.
U The trigger fires when an UPDATE statement modifies the base table.

Thus, a BEFORE INSERT or UPDATE row level trigger on the STUDENTS table would be named STUDENTS_BRIU.
If the length of the trigger name exceeds 30 characters when following this standard, abbreviate the name of the trigger’s base table to create the name of the trigger. Under no circumstances should the trigger type extension be abbreviated.
Identifiers
When declaring variables and constants, the developer should preface a meaningful identifier with one of the prefixes shown in Table D.6.
Table D.6 Datatype prefixes for use in identifiers. ________________________________________
Datatype Prefix Example
binary_integer bi biArrayIndex
boolean b bStudentQualifiesForAid
char c cYesOrNo
date d dEnrolledDate
exception x xTABLE_DOES_NOT_EXIST
integer i iCoursesCarried
long l lComments
longraw lr lrStudentPhoto
natural na naArrayIndex
number n nRemainingBalance
raw r rStudentPhoto
rowid row rowStudent
varchar2 v vStudentFirstName

Identifiers should always use mixed-case and capital letters to indicate separation of elements within an identifier. Thus, a variable of type varchar2 that holds a student’s first name would be vStudentFirstName.
The identifiers used for explicitly declared cursors should be meaningful; the suffix _cur should be appended to the identifier. For example:
CURSOR Students_cur
IS
SELECT first_name, middle_name, last_name, overall_gpa, most_recent_gpa
FROM STUDENTS;
Identifiers declared using %TYPE should still include a datatype prefix as part of the identifier name:
nStudentSSN STUDENTS.ssn%TYPE;
Identifiers declared using %ROWTYPE should be named like the object that is lending the variable its structure. These identifiers should always include the _rec suffix as part of the identifier:
Students_rec STUDENTS%ROWTYPE;
FailingStudents_rec FailingStudents_cur%ROWTYPE;
Procedures And Functions
Stored procedures and functions should be named first by the type of action the object performs and then by the object of that action. For instance, a procedure that calculates interest on a student’s remaining balance would be named Calculate_Balance_Interest.
Packages
Packages should be named in accordance with the general purpose of the procedures and functions contained within the package. For instance, a package containing routines used to calculate a student’s GPA would be named GPA_Calculations.
Written Documentation
All documentation pertaining to system design should follow the conventions identified in this standard. This applies especially to pseudocode that is used to document stored PL/SQL objects.
In addition, all written documentation should reference calls to built-in and developer-written procedures and functions using a set of parentheses, as in “will call the Calculate_Semester_GPA() procedure”. The names of built-in and developer-written code modules should also be referenced in bold.
Taking Advantage Of Standardized Code
Oracle stores the source code for PL/SQL objects inside the ALL_SOURCE view of the DBA_SOURCE table in the SYS schema. This allows you to query the most recent source code for a procedure or function from the database.
If the coding standards are followed fairly closely, it’s possible to write scripts that work with your source code to assist you in your documentation efforts. Included on the CD-ROM is a script that takes full advantage of the coding standards to collect information about and document source code.
The Build_SUID_Matrix Package
Oracle stores dependencies in the ALL_DEPENDENCIES view, but this information exists only at the object-to-object and object-to-table levels. The view cannot, for instance, state precisely which objects perform INSERT statements on a specific table.
The Build_SUID_Matrix package was designed to locate all references to tables within a specified PL/SQL object. The procedures and functions in the package locate table references and sort the references by type (SELECT, INSERT, UPDATE, DELETE, and %TYPE or %ROWTYPE). The package populates the SUID_MATRIX table with this information.
Once the SUID_MATRIX table is fully populated, a query can be run to see precisely which objects access a specified table or which tables a specified object references. This is particularly useful when:
• Examining the impact of creating a new index.
• Examining the impact of altering a table’s structure.
• Determining which objects perform a particular type of operation against a table.
Improving The Build_SUID_Matrix Package
There are a number of potential improvements that can be made to the Build_SUID_Matrix package:
• Improving the level of detail determined to the column level. Knowing which objects modify data in a table is very useful, but if 20 routines update a table and only a few routines update a particular column that has a suspect value, it’s even quicker to find the routines that modify the value in that column.
• Recognizing objects within packages. The current implementation of the package only recognizes objects to the package level and doesn’t differentiate between procedures and functions within the package.

Using SQL*Loader to Populate the Payables Open InterfaceTables

2009-11-16T22:41:00.000-08:00

You can create a SQL*Loader program that produces a flat file containing the invoice details you want to bring into Payables. For detailed information on the tables, refer to Open Interface Import Table Descriptions.

To use SQL*Loader to populate the Payables Open Interface tables:

1. For each invoice, the program should identify the supplier by providing a value for at least one of the following columns: VENDOR_ID, VENDOR_NUM, VENDOR_SITE_ID, or PO_NUMBER. In addition, for each record, you must populate the following required columns in AP_INVOICES_INTERFACE:
INVOICE_ID
INVOICE_NUM
INVOICE_AMOUNT
SOURCE. Make certain that you specify a Source type QuickCode exactly as you entered it in the Payables QuickCodes window.
For each invoice line, include the following values to populate the columns in the AP_INVOICE_LINES_INTERFACE:
INVOICE_ID
LINE_TYPE_LOOKUP_CODE
AMOUNT
In addition, if you are importing foreign currency invoices, include information to populate the following columns in AP_INVOICES_INTERFACE:
EXCHANGE_RATE_TYPE
EXCHANGE_RATE (if you use User-defined rate type)
If this exchange rate information is the same for all invoices and invoice distributions you import, you can include this information in your SQL*Loader control file, instead of entering it separately for each invoice.
Finally, ensure that your invoice flat file has the appropriate information to populate AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE as indicated in the preceding table descriptions. If a value is not required for a column, you may leave the column empty.

2. Create a SQL*Loader control file.
Read the SQL*Loader documentation to learn how to write a SQL*Loader control file. The file you write will vary greatly depending on the nature and format of the invoice flat file you use. Your control file must populate AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE as indicated in the previous table descriptions. See also: SQL*Loader (ORACLE8 Server Utilities Guide).
Code your control file to populate the SOURCE column in AP_INVOICES_INTERFACE with the Source code exactly as you defined it in the QuickCodes window.
If all your invoices are in the same currency, you can hard code the value for DEFAULT_CURRENCY_CODE in AP_INVOICES_INTERFACE and CURRENCY_CODE in AP_INVOICE_LINES_INTERFACE in your control file.

3. Use the SQL*Loader program you created to load your invoice information from your flat file into the interface tables. Indicate your control file as the SQL*Loader control file, your invoice flat file as the SQL*Loader input file, and your Payables SQL*Plus username and password as the database sign on. For more information see: Oracle8 Server Utilities Guide.

4. Use the Open Interface Invoices window to review and edit the data in the Payables Open Interface tables.

Payables Open Interface

2009-11-16T21:54:00.000-08:00

http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/ap/openint.htm

Exporting OA_Page from Database

2009-11-01T23:56:00.000-08:00

Exporting OA_Page from Database
=========================
Connect to db server with putty .go to $JAVA_TOPAnd then execute the follwing command.Page exported to this directory created. $JAVA_TOP/chola/this is the "/oracle/apps/per/selfservice/loa/webui/LoaSummaryPG" page.
Exporting Page
========================
java oracle.jrad.tools.xml.exporter.XMLExporter /oracle/apps/per/selfservice/loa/webui/LoaSummaryPG -rootdir $JAVA_TOP/chola -username apps -password password -dbconnection "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST =dbserver ) (PORT = ))) (CONNECT_DATA = (SID = TEST)))"
Exporting Region
========================
java oracle.jrad.tools.xml.exporter.XMLExporter /oracle/apps/per/selfservice/accruals/webui/AccrualBalanceRN -rootdir $JAVA_TOP/chola -username apps -password password -dbconnection "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = dbserver) (PORT = ))) (CONNECT_DATA = (SID = TEST)))"

OA_Page Development & Deployment To Oracle apps

2009-10-14T02:20:00.000-07:00

OA_Page Development & Deployment To Oracle apps
=============================================
Step1 : login to J-Developer by clicking on connection.
Step2: Create Oa workspace and Oa_project and give meaningful name them with default package eg. oracle.apps.ak.hrcosting.server
Step3: Create two new business component packages under oa_project without entity ,view object and application module eg.(Right click on Oa_project =>New business component package)
oracle.apps.ak.hrcosting.server
oracle.apps.ak.hrcosting.webui
step4: It will be looking like fig.
Step5: Entity Object creation eg.
Right click on oracle.apps.ak.hrcosting.server =>New Entity Object =>give the database view name=> create one column as primary key=> check validation.
Step6: View Object Creation eg.
Right click on newly created Entity Object =>New default View object.
Step7 : Rebuld Project
Step8 : Page creation in webui
Right click on project =>new=>Expand Web Tier=>oa componenets=>page
Name that page eg. HRCOSTING
Step9: Rename the below region eg.MainRN and set the properties of that as below :
Name:-MainRN
Region Style :PageLayout
AM Module : Appear automatically after clicking
Window Title and Title.
Step10: Create another region under MainRN
Name :QuerryRN
Region Style :querry
Construction mode: Result based search
Include simple panel-true

Include view panel-true
Include advanced panel-true.
Step11: right click on QuerryRN =>Region using wizard =>select view usage=>check application module=>select column.
Id-viewname
Region style:Table
Records displayed:-6000
Step12: Item properties
Employee no :
Search allowed:true
Sort allowed :asending
Employee Name:
Search allowed :true
Sort allowed : no
Step13: Run the page HRCOSTING.xml
Deploying OA Page in Oracle apps Environment

The steps in brief are :
==============================
· Development of the JSP form in local machine
· Transferring the related source code/files to the Oracle APPS environment.
· Importing the new directory structure/path of the page/form in the MDS repository
· Registration of the form in the Oracle Apps environment

Step1: I’ve developed the HRCOSTING Page in
D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myprojects directory.
It is given at the time of workspace, project and package creation time.
I’ve kept my xml pages in webui and EOP, VO and AM in server folder of the
Hrcosting folder.
HRCOSTING folder is at location :
D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myprojects\oracle\apps\ak
So the root directory of my project is
D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myprojects

On project compilation the class files along with xml files are generated in Myclasses of
D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myclasses folder
Go to myclasses\oracle\apps\ak\hrcosting folder
Eg. D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myclasses\oracle\apps\ak\hrcosting
Structure is as below :

Webui folder structure as :
And server folder structure as :
The next step will be to move the files into the JAVA_TOP of the Application server.
Transfer the folder hrcosting in binary mode from local machine path as :
D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myclasses\oracle\apps\ak\hrcosting
To Java top of the application server as below :
$JAVA_TOP/oracle/apps/ak/
(Transfer of all the class files should be in binary mode )
Give the 755 permission to the entire hrcosting folder transferred by using unix command.
Chmod –R 755 hrcosting

Next step is Importing Xml files :
Open command prompt on your machine(local machine)
Go to the Jdeveloper\jdevbin\jdev\bin directory
D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevbin\jdev\bin
(Kindly note hat the import command is to be run from Jdeveloper/Jdevbin/Jdev/Bin).
The Import from HRCOSTING (HRCOSTING.xml) page is as follows :
import D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myprojects\oracle\apps\ak\hrcosting\webui\HRCOSTING.xml -username apps -password hp75400 -rootdir D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myprojects\ -dbconnection "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 202.88.130.27) (PORT = 1546))) (CONNECT_DATA = (SID = TEST)))"
(The command should be in single line)
You will get the “Importing /oracle/apps/ak/hrcosting/webui/HRCOSTING
Import Completed “ message at the bottom.
Note here : “ /oracle/apps/ak/hrcosting/webui/HRCOSTING” this path.
It is useful when giving html call to form function.

Registering the Page as Form Function
Create a form function by using System Administrator Responsibility .
Enter Properties (Tab) Type as SSWA jsp function.
In Web HTML(tab)
Enter the Main xml page path
OA.jsp?page=/oracle/apps/ak/hrcosting/webui/HRCOSTING
(Don’t append .xml with the path, it results in error)
Attach the function with a menu and then run from the respective responsibility.
You may need to bounce the Apache server.(adapcctl.sh)
When I run the page from menu it appears as :

Thanks,
Kamlesh

Changing the default layout format from PDF to Excel using Profile Option and FNDRSRUN Form Modification - Submitting BI Publisher Report Request

2009-09-26T04:29:00.000-07:00

Update 2: This solution is now obsolete:

Patch 5612820 and 7627832 for R11i have been released for this issue. Applying these patches will overwrite the customization. See this post for details.
Patch 5612820 for R12 has been released. See this post for details.

Well, a few people have been frustrated with the default output format of PDF when submitting BI Publisher based concurrent requests.

Oracle's better solution to provide a default output format on the template definition isn't here yet. For reference see Metalink Note 401328.1, or Bug 5612820 or Bug 5036916, or Forums here or here

I'm not going to hold my breath, so herein lies a solution to set the default based on a profile option value, using an unsupported form modification to FNDRSRUN.fmb (Submit Requests or Standard Request Submission). I don't usually recommend modifications, but in this case its one line of code and the impact is very minor, so if its blown away, we'll just have to get over it ... check the caveats at the bottom of the post too!

Note that it is possible to set the default format to Excel, RTF or whatever your preferred output format is via forms personalization if you always navigate to the Options, Layout block of the Submit Requests screen. But 99 times out of 100 I don't go there.

Onto the instructions.

1. Create profile option "XML Publisher Default Format".

Navigate to Application Developer, Profile

Create new profile option

Name = XXV8_XMLP_DEFAULT_FORMAT
Application = (Your modifications application or Application Object Library)
User Profile Name = XML Publisher Default Format
SQL Validation:

SQL="SELECT MEANING \"Default Output Format\"
, LOOKUP_CODE
INTO :VISIBLE_OPTION_VALUE
, :PROFILE_OPTION_VALUE
FROM FND_LOOKUP_VALUES_VL
WHERE  LOOKUP_TYPE = 'XDO_OUTPUT_TYPE'"
COLUMN="\"Default Output Format\"(50)"

2. Set profile option value to Excel (or RTF etc) at the required levels

Navigate to System Administrator, Profile, System

Find you profile option XML Publisher Default Format and set values as required.

3. Modify Form FNDRSRUN.fmb

Copy and open form $AU_TOP/forms/US/FNDRSRUN.fmb

Open Program Unit WORK_ORDER

Find the line:

:templates.format := 'PDF';

Note this is line/char 349/41 in Release 11i FNDRSRUN.fmb 115.169 or 359/40i in Release 12 FNDRSRUN.fmb 120.29

Change to:

-- GR 24-JAN-08 Override default BI Publisher layout output format
--:templates.format := 'PDF';
:templates.format := nvl(fnd_profile.value('XXV8_XMLP_DEFAULT_FORMAT'),'PDF');

4. Compile Form FNDRSRUN.fmb to FNDRSRUN.fmx

Copy the new FNDRSRUN.fmb to your custom top forms/US directory

Compile the fmb to fmx.

FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US:$AU_TOP/plsql
f60gen Module=FNDRSRUN.fmb Userid=apps/apps > genform.log

5. Replace standard FNDRSRUN.fmx with modified version

Note: Replace XXV8_TOP with your custom top.

cd $FND_TOP/forms/US
mv -i FNDRSRUN.fmx FNDRSRUN.fmx.orig
ln -s $XXV8_TOP/forms/US/FNDRSRUN.fmx FNDRSRUN.fmx

6. Test it out.

Some caveats here:

This is an unsupported form modification that will be blown away when the FNDRSRUN form is upgraded (hopefully when the real solution appears).
The instructions above only replace the executable .fmx version of the FNDRSRUn file, so if FNDRSRUN is regenerated/recompiled via adadmin or similar then the modification will not be in place. Redo the "replace" step.
This modification assumes a template type of RTF. PDF can only produce PDF output, so you need to watch this when you set the profile option value.
If you always click on the Options button, then use a forms personalization instead of this forms modification.

How To Create Custom Web-Adi Integrators

2009-09-26T04:11:00.000-07:00

Web Applications Desktop Integrator (Web ADI) is a self-service Oracle application, accessed using a browser. Unlike ADI, the Web ADI software is not installed on individual client machines, as the processing takes place on the server rather than on individual client PCs.

Web ADI generates Microsoft Excel or Word documents on your desktop, and downloads the data you have selected from the application. You can modify the data in the Microsoft Excel or Word documents, working either online or offline, and upload the data using Web ADI. In addition to the Web ADI integrators supplied with Oracle Applications, you can create your own integrators to allow download and upload data from Oracle windows or direct from Web ADI.

Except for HRMS, it seems to me that other Oracle Development teams have not utilized Web ADI to its full potential. There were some discussions in forums and Oracle blogs on whether this approach is even needed. I feel like every available interface and API that let you integrate with legacy or 3rd party applications should come with a standard Web ADI integrator.

Creating Custom Integrators

Thanks to the HRMS Web ADI, you can define your own custom HRMS and non-HRMS integrators and in a combination with the seeded Oracle APIs and interfaces, implement elegant solutions for downloading or uploading data. Examples for frequently used custom integrators include upload of employees, users, POs and requisitions, AP invoices, project events, AR transactions, etc.

Creating custom integrators is done by a system administrator. Let’s define an integrator to create employees to explain how to create custom integrators. Employees and employee assignments can be created using the standard HR_EMPLOYEE_API. Our custom integrator will upload data to this API.

Grant Access to Required Functions

As System Administrator, navigate to Application > Menu, and query up ‘Desktop Integration Menu’. Make sure to include functions required for respective integrators. In case of custom Create Employee integrator, you’ll need to include the following functions (no prompts are necessary):

HR Integrator Setup Integrator
HR Maintain Integrator Form Functions Integrator

Create ‘HR Integrator Setup’ Web ADI Document

Log in as Desktop Integration responsibility. Select Create Document from the menu. The Settings page of the Web ADI wizard appears.
In the Settings page, select a spreadsheet viewer in the Viewer field. Keep the Reporting check box unchecked, as this integrator is used to upload data to the database.
In the Integrator page, select the seeded integrator “HR Integrator Setup”.
In the Layout page, select the default layout “HR Integrator Setup”.
In the Content page, select None.
In the Review page, you can see full details of the document that will be created. Choose the Back button if you want to change any of the information, or choose the Create Document button to continue.
A spreadsheet document is created, containing all the fields you need to enter to create your own integrator.

Define all fields to create your Create Employee integrator and upload it to the database.

Controlling Access to Custom Integrators

Custom integrators, like predefined integrators, must be associated with form functions. Follow the steps below to associate your Create Employee integrator with the functions required to define Web ADI layout and Web ADI document.

Select Create Document from the Web ADI menu.
Select a spreadsheet viewer. Leave the Reporting check box unselected to download data for updating.
Select HR Maintain Integrator Form Functions Integrator.
Enter the Application Short Name (for example, your custom application name). Optionally, enter the integrator name.
The displayed spreadsheet shows your selected custom integrators, and has a column for associating form functions. Enter the form function required for controlling access to the integrator.
Upload your changes to the database.

Define Layout of Custom Integrator

Each integrator must have at least one layout specifying the columns to appear in the spreadsheet. To define a layout:

Choose Define Layout from the Web ADI menu. The Layout page appears.
Select the integrator for which you want to define the layout, and choose Go.
Choose the Define Layout button to define a new layout.
In the Define Layout page, enter a unique name for the layout. This appears whenever the user is required to select the layout, so should clearly indicate the purpose of the layout.
Enter a unique key. The key is used internally by the system to identify the mapping.
The Field List defaults to the complete list of columns available in the view or API.
Choose Continue. In case of Create Employee integrator, the layout is based on the the HR_EMPLOYEE_API columns. In the next page, you select a subset of these fields to include in your layout.

Create ‘Create Employee’ Spreadsheet

And finally, using the custom integrator and layout defined above, create a new ‘Create Employee’ WebADI upload spreadsheet:

From the Navigator window (Oracle Personal Home Page), click on ‘Desktop Integration’ responsibility. The Settings page of the Web ADI wizard appears.
In the Settings page, select a spreadsheet viewer in the Viewer field. Keep the Reporting check box unchecked, as this integrator is used to upload data to the database.
In the Integrator page, select your new integrator ‘Create Employee’.
In the Layout page, select the default layout “Create Employee”.
In the Content page, select None.
In the Review page, you can see full details of the document that will be created. Choose the Back button if you want to change any of the information, or choose the Create Document button to continue.

A spreadsheet document is created and ready to use for creating employees.
Save a local copy to your PC.

Basic Steps to Enable Multi-Org

2009-09-07T22:32:00.000-07:00

Define the Organization Structure.
=============================================================
Plan and define the entities in your organizational structure.A successful implementation of Multiple Organization Support in Oracle Applications primarily depends on correctly defining your organization structure in the hierarchy used by Oracle Applications. A careful analysis and design of a company’s organization structure is critical for future success.
===================================================================
The following points describe how the Multi-Org model relates organizations:
=============================================================
• A Business Group is the highest level of the structure and has no accounting impact.The Business Group determines which employees will be available to Sets of Books and Operating Units related to that Business Group.
• Set of Books is the highest level which impacts the accounting side of business.
• Set of Books is associated with a single Business Group, multiple Sets of Books may be associated with a single Business Group.
• Each Set of Books may have a different chart of accounts structure, calendar, or functional currency.
• Each Legal Entity is associated with a single Set of Books, multiple Legal Entities may be associated with a single Set of Books.
• Each Operating Unit is associated with a single Legal Entity, multiple Operating Units may be associated with a single Legal Entity.
• An Inventory Organization may be associated with any Operating Unit within the same Set of Books.
===============================================================
Convert to Multi-Org:
===============================================================
After defining at least one operating unit, use ADADMIN to convert to Multi-Org. This step only needs to be perform one time. After ADADMIN is run, the Seed Data Replication program copies the applicable setup data delivered with Oracle Applications for each defined operating unit. If you add additional operating units, you can run the See Data Replication program manually at any time. Note that seed data is only data that comes with the database and does not include any operating unit specific setups you may perform.Run ADADMIN to Convert to Multiple Org
• When you select the adadmin option, Convert to Multiple Organization architecture, the Multi-Org switch is automatically turned on; a flag called multi_org_flag, is set to Y. The seed data replication program is then executed and processes according to the value of the multi_org_flag.
Run Seed Data Replication Seed data is replicated under the following circumstances:
• Submit the Replicate Seed Data concurrent request using the System Administrator responsibility.
The seed data replication program works in two modes:
• Single organization: This mode applies when the seed data replication program is executed within the context of a particular operating unit (or Org_ID).
• All organizations: This mode applies when the seed data replication program is executed without an operating unit context.
===================================================================
The following steps are required to add new operating units to your Organizational Structure:
=============================================================
• Revise the Organization Structure
• Define Sets of Books (optional)
• Define Organizations and Relationships
• Define Responsibilities
• Set Profile Options for each Responsibility
• Define Inventory Organization Security (optional)
• Implement the Application products
• Secure Balancing Segment Values (optional)
• Run the Setup Validation Report (recommended)
• Implement Document Sequencing (optional)
• Define Intercompany Relations (optional)

Changes in Existing Web-Adi Letter Column/Data

2009-09-07T22:18:00.000-07:00

Changes in Existing Web-Adi Letter Column/Data

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

1. Find the Integrator Name of the letter.Form Function name is same as Integrator Name.

2. Find the form function.

3.SELECT integrator_code, application_idFROM bne_integrators_vl vlWHERE user_name IN ('XX Request for further references', 'XX Sorry Interview did not work') ;Here you find the Integrator Code.

4. Use FNDLOAD command to download the Integartor as ================================================================

FNDLOAD apps/hp75400 0 Y DOWNLOAD $BNE_TOP/admin/import/bneint.lct XX_HR_2_INTG.ldt BNE_INTEGRATORS INTEGRATOR_ASN="PER" INTEGRATOR_CODE="HR_2_INTG"

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

The integrator will be downloaded to the current location from where the command issued as XX_HR_2_INTG.ldt for above example.Issue this command to the application server.

5.open XX_HR_2_INTG.ldt file with wordpad here you will find the Integrator Interface code name.

6.Now issue the following commandselect user_name from BNE_INTERFACES_VL where INTEGRATOR_CODE LIKE 'HR_2_INTG'

7.From above querry you will find the view name used for this letter generation.ie, user_name form above querry obtain is nothing but the database view name.

8.Now by opening this view you can modify the select statement used in creation of this view.

9.Recreate the view after changes are done.

10. Finish..now you can check the data by generating letter using web-adi.

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

Very Important Link for this : http://oracle.anilpassi.com/fndload-for-oracle-web-adi.html

Customizing Self Service Applications using Personalizations

2009-04-20T04:56:00.000-07:00

Release 11.5.10 provides a feature called Personalize Page, which gives us the ability to customize the look and feel of the Self Service Applications.

Setting the Profile option Personalize Self-Service Defn to "Yes", enables this feature. If it is set to "No", the Personalize Page link will not be seen prohibiting you from customizing the self-service page.

There are other profile options that need to be turned on depending on the personalizations required.
FND: Personalization Region Link Enabled = Yes
FND: Personalization WYSIWYG Enabled = Yes

Personalizations allow you to make changes at various levels:
Function: One can define functions and can define granular level personalization based upon these functions.
Site: Personalizations that affect all users with access to the given application component.
Operating Unit: Personalizations that affect all users with access to the given operating unit.
Responsibility: Personalizations that affect all users with access to the given responsibility.

Some of the customizations that can be achieved using Personalization:
In all our examples we will be considering iExpense self –service Application.

1) To remove the Cash and Other Expenses Link from the Navigation train (Road Map) on
General Information Entry Page

Navigate to the General Information Entry Page

Click on "Personalize Page" on the right hand corner of the page (shown in the above screenshot).

You will see the following page come up. Select the Complete View Radio button.

Scroll down to the "Location" Section

Click the Pencil Icon next to "Link: Cash and Other Expenses"

Set the Personalization Property "Rendered" to "False" at the Responsibility Level

Click on Apply to save the changes.

On returning back to the Application, you will see that the Cash and Other Expenses link is hidden from the Navigation Train.

Add the below Text to Cash and Other Expenses page:

Cash, Mileage and other out of pocket expenses will not be processed for payment
in iExpenses after December 17th, 2008. Please use SAP system to enter such
expenses.

Navigate to the Cash and Other Expenses Page and click on "Personalize Page" on the right hand corner of the page.

You will see the following page come up. Select the Complete View Radio button.

Scroll down to the "Page Layout" Section and click on Create Item Icon next to it.

Enter the Following information:

Select the Item Style as "Formatted Text".
Specify a name for this item in the field ID.
Enter CSS Class as OraErrorNameText to display the text in Red Color.
Text as Cash, Mileage and other out of pocket expenses will not be processed for payment in iExpenses after December 17th, 2008. Please use SAP system to enter such expenses

Click Apply to save the changes.

Reorder an item: By default, the above text would be displayed at the bottom of the page. To bring it to the top, click Reorder icon next to "Page Layout"

Select the Personalize Radio button under Responsibility section

Move the item that has been created to the first position using the Arrow to the right

Click Apply button to save the changes.

On returning to the application, you will see the text is displayed as seen below,

Hide the Receipt Based Expenses table to prevent the user from entering any expenses in this section.

Navigate to the Cash and Other Expenses Page and click on "Personalize "use this table to enter receipt-Based Expenses" under the Receipt-Based Expenses Header.

You will see the following page come up. Select the Complete View Radio button. Click on the Pencil icon next to the "Table: Use this table to …"

Set the Personalization Properties "Rendered" to False at the responsibility level.

Click on Apply to save the changes.
On returning to the application, you will see that the Receipt based Expenses table is hidden

Similarly anything could be hidden (Buttons, column etc) by setting its property "Rendered" to "False".

To Make the field "Expense Cost Center" Read only in the General Information Page:

Navigate to the General Information Entry Page

Click on "Personalize Page" on the right hand corner of the page (shown in the above screenshot).

You will see the following page come up. Select the Complete View Radio button.

Scroll down to the Name "Message Text Input: Expense Cost Center" and click on the Pencil icon next to it.

Set the property "Read only" to True at the responsibility level. Click on apply to save the changes.

Upon returning to the application, you will see that the Expense cost Center filed has been made Read only making it non-updatable.

To change the Field Name Purpose to Expenses Justification in General Information Page.

Navigate to the General Information Entry Page

Click on "Personalize Page" on the right hand corner of the page (shown in the above screenshot).

You will see the following page come up. Select the Complete View Radio button.

Scroll down to the Name "Message Text Input: Purpose" and click on the Pencil icon next to it.

Change the Property "Prompt" to "Expenses Justification" at responsibility level and click on Apply to save the changes.

Upon returning to the application, you will see that the field name is changed.

Similarly, the display name could be changed for buttons and other objects too.

Thanks & Regards ,
Kamlesh Nikhade