http://sabersurge.com/oracleappsdev http://moreoracle.com

I will now be posting articles on http://moreoracle.com .
Or you can alternatively go to http://sabersurge.com

Topics are:
1. To read a comma separated value as a Concurrent Program Parameter.
2. Oracle Audit Trail for Custom Table.
3. Multi Org Access Control (MOAC) R12.
4. SOA + Jdeveloper Install.
5. OA Framework Beginner.
6. Upload project to Application server (OA Framework).
7. File Upload to Database Server (OA Framework).
8. File Upload to Applications Server (OA Framework).
9. Migrating Personalizations (OA Framework).
10. Personlize Branding Image (OA Framework).
11. SPEL (OA Framework).
12. OA Framework package naming convention.
13. JDR UTILS (OA Framework).
14. Bounce Server (OA Framework).
15. Oracle to CSV , TSV and Excel creation Tips
16. Execute Fnd_request.submit_request Concurrent Program in future
17. Oracle Accounts Receivables AR Tables
18. Oracle Sequence Gaps By Example
19. Enable Concurrent Program tracing using tkprof and queries for performance tuning
20. Selecting Files from the UNIX directories using find Examples
21. How to find and install the correct JDeveloper version for your Oracle Applications (OA Framework) Framework on Windows or Linux?
22. AP Positive Pay Custom programs for Banks.
23. Finding Un-Attached Tax Lines In Invoice Distributions.
24. Levels of AIM Documents.
25. ERP IMPRESSION
26. Payroll Positive Pay Custom programs for Banks

OA Framework Training

For training in OA Framework please contact vishalsaberwal@gmail.com .
Training will be provided online.

The main goal of the course will be to remove fear of OA Framework.
The course covers the fundamentals of OA Framework-based applications. The attendees will learn to integrate Java, OA Framework, and 11i Oracle Applications to deliver web-based self-service applications. During the training the attendees will create OA Framework applications to perform DML operations on 11i Oracle Applications.

Pre-requisites:
(a) Core Java and J2EE basic knowledge.
(b) Oracle Applications and PL/SQL basic knowledge.
(c) Windows machine.
(d) Basic computer knowledge.

Duration (2-3 weeks course) depends on the knowledge and technical skills of the attendees in the batch. The Course timings will be 7:00 pm PST - 1:00 am PST.

The Course Contents:
==============================

1. Introduction to OAF Architecture
2. MVC
3. Setup the Developer environment (JDeveloper setup).
3. HelloWorld hands-on
4. Search screen hands-on
-- Exercise
5. Drilldown to Details
6. Create
7. Insert, Update, Delete
8. PPR "Partial Page Rendering"
-- Exercise
9. Personalizations
-- Exercise
10. Extensions
-- Exercise
11. Deployment to Apps server
-- Exercise
12. Debugging techniques
=========================================

Migrating personalizations

//////////////////////////////////////////////////////////////////////////////////
MIGRATING PERSONALIZATIONS
============================
Profile Options used for Personalization are:
1. Disable Self-Service Personal: Default is "NO". If set to YES, it disables all personalizations. LINK VISIBLE. Revert back to the vanilla implementation.
2. FND: Personalization Region Link Enabled - Disables Region Personalize Links.
3. Personalize Self-Service Defn - Shows / Hides the global Personalize Page Link.
4. FND: Diagnostics (YES/NO) - Shows / Hides "ABOUT THIS PAGE" link.

Usual Production Profile Option settings -
Disable Self-Service Personal - NO
FND: Personalization Region Link Enabled - NO
Personalize Self-Service Defn - NO
FND: Diagnostics - NO

You have two options:
(a) Do personalizations manually, which is good if there are a very few of these.
(b) Migrate the personalizations to various instances if there are many.

STEPS
=====
1> Get the System Profile values for "FND: Personalization Document Root Path" on Source(X) and Destination(Y) servers. Under this Path, the personalized document is present.
Let us take these values as (X: "/a/b/c/") and (Y: "/p/q/r/") for this blog posting.
Profile value on X=?
Profile value on Y=?
2> On Source:
Logon to Functional Administrator responsibility ->Personalization Tab -> Import / Export -> Select the Page u have personalized -> export to file system
This automatically adds it under the corresponding folder inside "/a/b/c/".
It will show the path where it exported.
For example personalizing IExpense HomePG=> /a/b/c/oracle/apps/ap/oie/webui/customizations/site/0/HomePG.xml
3> Copy the file (PG.xml) and transfer it to the Destination server.
For the HomePG=> /p/q/r/oracle/apps/ap/oie/webui/customizations/site/0/HomePG.xml
4> Logon to Functional Administrator responsibility->Personalization Tab->Import / Export -> Exported Personalizations -> Select the PG.xml -> chose IMPORT FROM FILE SYSTEM.
This now allows the usage of the personalised page.
5> Soft Bounce the server (Clear Cache).

Enable / Disable / Delete Personlization
============================
When there is an error with the page, you will not be able to open the page to use Personalize the page. In this case you use:
Functional Administrator responsibility -> Personalization -> select the path of the doc (/oracle/apps/ap/oie/webui)-> Manage Personalizations -> Deactivate / Delete


Since Personalizations happen in separate folders than the seeded pages, they are not impacted by oracle apps patching.

Personlize Branding Image
===================
Profile Option Name: FND: Branding Size
# cd $OA_MEDIA/FNDSSCORP.gif
Set the profile option Corporate Branding Image for Oracle Applications to the name of an image file (GIF) that contains your corporate image.
Or, create an administrative-level personalization, for a specific page by setting the Image URI property on the corporateBranding page element to adifferent image file. This updates the image only for the specific usage on that page.

Bounce Server
============
1. Soft Bounce: Functional Administrator responsibility -> Core Services -> CACHING FRAMEWORK -> Global configuration -> Clear All Cache
2. Apache Bounce: The self service[OA framework] sessions will get killed. Form sessions will not be killed.
$COMMON_TOP/admin/scripts/adapcctl.sh stop
$COMMON_TOP/admin/scripts/adapcctl.sh start
3. Hard mid-tier Bounce: Not only the self service, but also D2K sessions [jInitiator] will be killed.

What and When to Bounce
===================
1. Changes to files in $JAVA_TOP requires an apache bounce.
2. Changes to files in MDS will require a Hard Bounce. Example, OA pages.
You can avoid this by coding this in CO or VO.
3. Personalization done through screen does not require bounce.
4. Personalizations imported require Soft bounce.
5. BC4J substitutions require Hard Bounce.

JDR_UTILS
==========
1. List all OA framework documents (personalizations, extensions, documents. etc) in a path (partial or full path).
DECLARE
BEGIN
jdr_utils.listdocuments('/oracle/apps/ap/oie',TRUE);
END;
2. List all Personalizations, extentions etc at various levels for a specific object.
DECLARE
BEGIN
jdr_utils.listcustomizations(p_document => '/oracle/apps/ap/oie/test/webui/HomePG');
END;
3. List all personlization and extention details in XML format.
DECLARE
BEGIN
jdr_utils.printdocument(p_document => '/oracle/apps/ap/oie/test/webui/customizations/site/0/HomePG');
END;
4. Delete document, personlization, extentions. You need the full path.
DECLARE
BEGIN
jdr_utils.deletedocument(p_document => '/oracle/apps/per/irc/candidateSelfService/server/customizations/site/0/VisitorAM');
END;

JDeveloper Version
==============
1. select 'Download patch 4573517' from ad_bugs where bug_number = '4125550' ;
If this returns a record, then download patch 4573517.
2. select 'Download patch 4141787' from ad_bugs where bug_number = ‘4017300' ;
If both the above SQL’s do not return a record, then download patch 4045639.
3. Select bug_number from ad_bugs where bug_number in ('4334965','4125550','4676589','5473858');

OA Framework package naming convention
===============================
Default Package name = [Your_Name].oracle.apps.[Module_Name].[Project_Name]
Page (PG) = [Your_Name].oracle.apps.[Module_Name].[Project_Name].webui
Controller (CO) = [Your_Name].oracle.apps.[Module_Name].[Project_Name].webui
Application Module (AM) = [Your_Name].oracle.apps.[Module_Name].[Project_Name].server
View Object (VO) = [Your_Name].oracle.apps.[Module_Name].[Project_Name].server
Entity Object (EO) = [Your_Name].oracle.apps.[Module_Name].[Project_Name].schema.server Association Object (AO) = [Your_Name].oracle.apps.[Module_Name].[Project_Name].schema.server
LOV = [Your_Name].oracle.apps.[Module_Name].[Project_Name].lov.server

Miscellaneous
===========
1. Using Functional Administrator responsibility -> Core Services -> Profiles to set profile "Personalize Self-Service Defn" and "FND: Personalization Region Link Enabled" will have it take effect faster and without soft bounce. This is better than using System Administrator responsibility.
These are used to enable Personalize link for Site and Region.
2. Extended COntroller should call the super() of parent controller class to be upgrade safe. This way, the code in parent class will be run (even after upgrade) and you will be able to do changes as you need.
4. Login screen in Oracle Apps uses APPLSYSPUB to connect and validate credentials. Selfservice screens connect to APPS schema using GUEST username (FND_USER). You can get this password by:
Profile Option: Guest User Password
JAVA: webappscontext.getEnvStore().getEnv("GUEST_USER_PWD")
SQL: select fnd_profile.value('GUEST_USER_PWD') from dual

SPEL (simplest possible expression language)
=================================
>> SPEL returns True/False.
>> SPEL can be used to hide a field conditionally; like field "a" will be displayed only when field "b" value > 0 . This can be achived by using SPEL expression in "Rendered"/"Read Only"/ "Enabled"/"Required" attribute of the field.
>> Examples:
${oa.FunctionSecurity.XX_TESTS} will return true if user is currently accessing OAF screen from function named XX_TESTS.
${oa.ViewObjectName.IsDunningYes} if VO attribute "IsDunningYes" value is FALSE then, the field will be disabled/hidden/non-required, depending upon where what field attribute you are writing the SPEL for.

Training Tutorials

Things to remember
a] New projects:
Project settings=
You should ensure that the developer test mode and OA diagnostics are enabled in your projects to help you identify various coding standards violations and leverage the AOL/J logging feature. OADeveloperMode and OADiagnostic.
b] New Business components Package (BC4J):
Verify that the radio button Entity Objects mapped to database schema objects is selected.
c] New Application Module:
In the Java page, select the Generate Java File(s) checkbox.
Enable passivisation >> Edit AM -->properties -->name=RETENTION_LEVEL, value=MANAGE_STATE.
d] New Entity Object:
Verify that one of the attributes have Primary key checkbox selected.
Deselect the Generate Default View Object check box.
In the Generate Methods region, check the Accessors, Create Method, Validation Method and Remove Method check boxes.
In the Tuning tab, check the Use Update Batching checkbox and set the Threshold field value to 100.
e] New View Object:
Select the Generate Java File for View Object Class: EmployeeSummaryVOImpl and for View Row Class: EmployeeSummaryVORowImpl.

OA Beginner -2

1. Create the page first tomake things easier.
2.For Go button: select the attributeset property to /oracle/apps/fnd/attributesets/Buttons/Go.
3. If the Run process seems to hang, go to Run -> Terminate ->Embedded OC4J server, and then Run again.
4. For exceptions, " import oracle.apps.fnd.framework.OAExceptions; ".
5. Passivation should be enabled for root UI AM.
This can be done by: on the context menu for AM.xml, select Properties. Name=RETENTION_LEVEL, Value=MANAGE_STATE. Click Add and Apply.
6. Attribute Sets can be found in /oracle/apps/fnd/framework/toolbox/attributesets .

OA Beginner -1

1. Pages are composed of one or more regions.
2. Entity Object is a Object Oriented representation of a database table for middle tier or a logical representation of a Business Object.
Attribute validations can be performed here.
3. View Objects are based on one or more Entity Objects.
View Objects represent a database query and passing in and out parameters and results.
4. Application Modules can have one or more View Objects.


GETTING STARTED
================
Introduction>>>>>>>
OA Framework is based on J2EE MVC architecture.
Controller handles user interaction. Model handles business logic.View handles user interface.

Reuse>>>>>>>>
EO can be reused for the attribute validations across multiple application pages.
Reusable regions can be saved in MDS (MetaData Services) repository.

Setup Dev Environment (Windows) >>>>>>>>>
1. Configure JDEV_USER_HOME environment variable to \jdevhome\jdev .
2. Copy the dbc file to \dbc_files\secure directory.
3. Assign Toolbox responsibility for testing:
> OA Framework ToolBox Tutorial (responsibility key is FWK_TBX_TUTORIAL).
> OA Framework ToolBox Tutorial Labs (responsibility key is FWK_TOOLBOX_TUTORIAL_LABS).


OADeveloperMode provides extra code checking and
standards checking at runtime.
OADiagnostic enables the Diagnostics button in the global buttons at the top of the page, overriding any corresponding profile option set for the application.

To track sessions, OA Framework stores the Session ID in a session cookie.


Anatomy >>>>>>>
Page Basics:
Each UI widgets like button,table,tabs etc that renders in the page corresponds to one or more web beans in the hierarchy.
Request for a new page: request for a new page comes in --> OA framework reads the page's metadata --> create web bean hierarchy --> for each bean, OA framework calls code that we write to initialize the page --> When processing complete, OA framework hands web bean hierarchy to UIX framework to generate and send HTML to browser.
Request Form submit: OA framework recreates web bean hierarchy if necessary --> Calls any event handling code written for the page beans -->Page processing completes --> HTML is generated again and sent to browser.

[ MODEL ]
Application Module:
Each OA Framework page has a 'root' application module related to top level page region (pageLayout region). Root application module provides transaction context and database connection. If multiple pages participate in same physical/virtual transaction, they share same root application module. If page functions independently, it should have its own Application Module.

When creating a reusable UI region interacting with database, root application module may contain "nested" application modules, which inturn can nest children to any number of levels.
Root application module has access to all the data/objects held by its children and all children participate in same transaction established by the root.

Entity Objects :
BC4J EOs encapsulates business rules (validations, actions, etc) associated with a row in database table. Example, FWK_TBX_SUPPLIERS table stores supplier definitions, SupplierEO entity object implements all the business rules for inserting, updating and deleting a supplier.
EO can be based on table, views, synonyms, or snapshots.
EO can be of PL/SQL and Java types.
There is a one-to-one mapping between table and EO (between table columns and EO attributes). All EOs should include all columns in associated tables to automatically implement queries, inserts, updates and deletes. All we need to do is add the validation logic.
All validation logic for a EO should be consolidated to consistently implement business rules regardless of OA framework client.

Associated Objects :
These are complex objects by associating multiple EOs; these can be weak association (PO header, line, shipments) or strong associations (PO header owns its lines, which cannot exist outside its context).

View Objects :
BC4J VO encapsulates a database query, provides iteration over and access to result set. Each view row comprised of individual attributes.
Each VO's attribute maps either to columns in simple SQL statement (for small read only VOs), or to EO attribute (used for insert, update, delete), or to populate directly using SQL (calculated attributes or poplists not from EO).

View Links :
Just lile Associate Objects (association of EOs), View Links are VO assocaitions (view link between a PO header VO and a PO lines VO).

OADBTransaction :
It encapsulates JDBC connection / database session associated with root application module and directly owns any EO we create (VOs owned by root AM references the EO).
Access to OADBTransaction is provided by root application module.
It is also used when making callable statements to execute PL/SQL functions/procedures and session level information (user's name, id, current responsibility, etc).

[ VIEW ]
Page :
Pages comprised of Regions comprised of Items.
Items are simple widgets like button, image, etc and has no children, each item points to a style pointing to a web bean like OATableBean for example.
Regions can have items and other regions.
All pages have root level region with PageLayout style.
Sequence in which regions and items appear in JDeveloper page tree (and corresponding XML file) tells framework where to add these objects to the runtime bean hierarchy.

Attribute Sets :
A named reusable collection of properties that can be used by any UI object like regions, items, and other attribute sets.

Component Reuse :
To incorporate shared objects, you need to extend them by setting the Extends property to fully qualified shared region path (/oracle/apps/fnd/framework/toolbox/tutorial/webui/PoSummaryRN).
Shared region is not editable in referenced page.

[ CONTROLLER ]
Controller responds to user actions and directs application flow.
Controller can be associated with view at the Region level.
Controller defined how web beans behave.
Write controller to manipulate/initialize UI at runtime (that are not possible declaratively) and handle user events.

Request Handling:
When browser issues a OA.jsp request for your page -->
oracle.apps.fnd.framework.webui.OAPageBean (the main OA Framework page processing class) uses page name to determine the root AM to check-out from AM pool --> AM checks-out JDBC connection from connection pool --> transaction context for the page is established --> user session is validated --> if valid, OAPageBean evaluates request parameters to identify if this is HTTP POST or GET request.

Get Request:
Get request to server for page --> OA Framework uses declarative UI definition to build web bean hierarchy --> OAPageBean calls processRequest() on (top) PageLayout bean --> calls each web bean in hierarchy recursively --> each web bean instantiates its controller (if any) and calls processRequest() on the controller (complicated beans like OATableBean or OAPageLayoutBean perform post-controller processing by calling prepareForRendering() ) --> Each web bean calls processRequest() on its children --> OAPageBean gives web bean hierarchy to UIX to render and send to the browser.

Post Request:
OAPageBean checks if web bean hierarchy in memory (if not (resources reclaimed, user used back button, etc) recreate the hierarchy) --> OAPageBean calls processFormData() on PageLayout and then this calls processFormData() on all children beans to write form data to Model --> writing data to model invokes attributes and EO validations --> when successful, OAPageBean calls processFormRequest() on all beans in hierarchy to respond to user actions --> if no JSP forwards/redirects and no exceptions thrown in processFormRequest(), page is refreshed.

processRequest(): to construct/modify page layout, set web bean properties and do manual data initialization (like to perform an autoquery when nevigating to a page).
processFormRequest(): to respond to user actions.

The request includes:
1. any URL parameters
2. if it is a POST, any form field values plus the names and events associated with any action/control widgets selected by the user.



processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{

//A>
if (pageContext.getParameter("GoButton") != null)
{
// The user pressed the "Go" button, do something...
}

//B>
OAApplicationModule am =
(OAApplicationModule)pageContext.getRootApplicationModule();

//C>
pageContext.setForwardURL("OA.jsp?page=/oracle/apps/dem/employee/webui/EmpDetails
PG",
null,
OAWebBeanConstants.KEEP_MENU_CONTEXT,
null,
null,
true, // Retain AM
OAWebBeanConstants.ADD_BREAD_CRUMB_YES, // Show breadcrumbs
OAWebBeanConstants.IGNORE_MESSAGES);

//D>
String userName = pageContext.getUserName();
}

OA Framework - 1

Steps when referring to Anil Passi's tutorials:
1. Create or identify the schema / tables / views / procedures that the project will be based on.
Identify the ".dbc" file and copy to your local machine.
2. Open JDeveloper.
3. Create a New OA Workspace (.jws)
Note the Directory where you are saving the new worspace and the Workspace name.
Create a new Project and a new Package.
Use the desired .dbc file, provide username and password, Application name and Responsibility Key.
4. Create three Packages for schema.server, server and webui.
5. Create the Entity Object.
Entity Objects reside in schema.server package, hence use this suffix to the package name.
These link directly to the table / View , etc.
6. Create a View Object.
View Object are based on Entity object; hence it is available in its context menu.
Move this View object (once created) to server package.
7. Create Application Module on server package.
Attach the View Object to Application Module.
8. Create a New Web Tier --> OA Components --> Page in the webui package.
9. Rename and attach the Region created to the Application Module.
10. Create a sub region to the region in Step above for Query.
11. Within the region created above, create a new Sub Region using the Create Region Wizard.
Select the Application Module and the View Object for the Application Module that you want to use for displaying the search result.
Select the fields you want to display, and the form type to use to display (like input text, Text, etc).
Select the fields you want to be searcheable.
12. Rebuild the project and Run the page created.