Seeting Up OAF

Application:

• Functional grouping of forms, reports, programs, menus, libraries, and other objects

Application Short Name:

• Identifies application in directory and file names, and in application code such as PL/SQL routines.

Oracle Schema:

• Database user name used by application to access the database. Also known as 'Oracle ID' or 'Oracle User'.

Environment Variables:

1. $APPL_TOP
2. Application Basepath

Setup Application Directory Structure:

• Place application components in appropriate directories on appropriate machines so that AOL can find them.

Register Application:

• Register application's name, short name, basepath and application description with AOL. AOL uses this information to identify application objects such as responsibilities, forms, etc.
• To reduce risk that your custom application short name could conflict with a future Oracle Applications short name, name it as XX___

Add Application to a Data Group:

• Oracle Applications are installed as part of the Standard Data Group. Use Data Groups window to make a copy of standard data group and add your Application-Oracle ID pair to new data group.
• Specify APPS as Oracle ID (instead of custome schema) if you have integrated your application tables with the APPS schema.
• (N) : System Administrator responsibility- Security -> ORACLE -> Data Group

Application window:

• Register application to get AOL identify your application objects (e.g responsibilities, forms etc.) as belonging to your application. This identification with custom application allows Oracle Applications to preserve your application objects and customizations during upgrades.
• Define an environment variable that translates to your application basepath.
• Setup a directory structure for your custome application.
• Create a database if custome custom application resides in a database other than the database where Oracle Alert resides.

1. Application:
2. Short Name: XX_
3. Basepath: Name of environment variable that represents the directory of your application's directory tree. Oracle Applications search specific directories beneath the basepath for executables. [Correspond to GL_TOP,AR_TOP etc. e.g. ACX_TOP]

Building Database Objects:

• Oracle Applications upgrade technology relies on Record History(WHO) information to detect and preserve customization.
• Add WHO columns to tables, and WHO logic to custom forms and stored procedures, to track changes made to application data.
• Represent each of WHO columns as hidden fields in each block of custom form. Call FND_STANDARD.SET_WHO in PRE-UPDATE and PRE-INSERT.

1. CREATED_BY: TO_NUMBER(FND_PROFILE.VALUE(USER_ID))
2. CREATION_DATE: SYSDATE
3. LAST_UPDATED_BY:
4. LAST_UPDATE_DATE: SYSDATE
5. LAST_UPDATE_LOGIN: TO_NUMBER(FND_PROFILE.VALUE(LOGIN_ID)), NULL allowed.

• Any table that may be updated by a concurrent program also needs additional columns used for Record History.

1. REQUEST_ID: is foreign key to table- FND_CONCURRENT_REQUESTS
2. PROGRAM_APPLICATION_ID: is foreign key to table- FND_CONCURRENT_PROGRAMS
3. PROGRAM_ID: is foreign key to table- FND_CONCURRENT_PROGRAMS
4. PROGRAM_UPDATE_DATE: is foreign key to table- PROGRAM_UPDATE_DATE

• To set Record History information, use event handlers in forms. Store logic in PRE-INSERT and PRE-UPDATE event handlers, which are called from PRE_INSERT and PRE_UPDATE block level triggers.
• Any constraint that is associated with a table should be duplicated in form so that user receive immediate feedback if a constraint is being violated.
• If you create additional constraints on Oracle Applications tables, disable them before upgrading Oracle Applications.

1. NOT NULL: Declare corresponding field in Oracle form as "Required"=True.
2. DEFAULT: Do not use this feature due to locking problems with Oracle Forms. * Defaulting column values can make batch programs simpler. Possible default values are SYSDATE, USER, UID, USERENV(), or any constant value.
3. UNIQUE: Only fields that user can enter should have a uniqueness check within form. System generated unique values should be derived from sequences.
4. CHECK:
5. PRIMARY KEY: Define a primary key for all the tables.

Standard Request Submission

• Provides standard interface for running and monitoring reports.
• Single form to submit any request, concurrent program. Another form to see report's progress and to review report online.
• Submit Request form: easy-to-use interface.
• Automatic Resubmission.
• Request Sets: define sets of reports, Submit an entire set at same time.
• Request Set options: Run order, printer, no. of copies etc.
• Request Set Log File:Single log file containing the completion status of all reports in set.
• Viewing Requests: Monitor report's progress using View Request form.

• Child Request: or a sub-request submitted by any other concurrent request(Parent Request)

• Parameter: A value you specify when you run a request.

* A request set is parent request and all reports in set are child requests

Value Set: A set of values against which AOL validates values, the ned user enters when running a concurrent program.

Controlling Access to Reports:

• Create related group of reports and request sets.
• Define responsibility and assign a report security group to that responsibility.

Request Sets:

• A collection of reports/programs that are grouped together. Allow to submit reports or programs in a request set all at once using a single transaction.
• Request set can be defined to submit requests depending on the completion status of previously submitted requests in the set.

Stage:

• Component of a request set used to group requests within a set. All requests in a stage are executed in parallel. Request set executes one stage at a time., following links from stage to stage.
• Links that are followed depend on the completion status of the individual stages. A stage completes when all the requests contained in the stage have completed.
• PL/SQL functions calculate the completion status of a stage
• Three status of a stage:

1. Success
2. Warning
3. Error

Stage Evaluation function:

• Provided by AOL, computes the stage completion status from the completion statuses of the specified requests in set.
• Function returns a

1. Success: If all requests completed with a status of "Success".
2. Warning: If one or more requests in set completed with warning and no request completed with error.
3. Error: If any request in set completed with error.

ERP Release Principals

•Planned releases are sequentially installed
•Unplanned fixes require specific management approval to be an exception
•Only changed code is released

Package Contents

•Customized Code (includes registering of concurrent programs, report sets, report groups, etc)
• Database Objects (tables, indexes, procedures, etc)
• Forms
• Menus
• Responsibilities
• Messages
• Value Sets
• Profile Options
• Descriptive Flexfields
• Folders
• Exclusion Rules


Standard Release Process

1. Approved Requirement or Problem
2. Developer creates fix - Development
3. Record in log document & put in repository
4. Create/update installation package
5. Install package for CIT/SAT
6. Developer or user tests fix
7. Install package for UAT
8. Execute UAT test plans
9. Package final release
10. Final install test - Package Test
11. Install on Production
    
    
    
    
    
    
    
    

Apps Fundamentals

Three environments, each with a different URL and different database instance. These are

• Development environment; where developers usually have System Administrator responsibility and also the apps password. If not System Administrator then at least they should have access to "Application Developer" responsibility.
• Testing environment; Developers usually do not and must not have apps password to this environment . This is where users sign-off customizations or even setup changes.
• Production environment; This is where the business runs

When you logs in the user is authenticated against a table named fnd_user for the username and password.

Oracle internally uses a login named GUEST, prior to invoking validation of actual username. Oracle uses a DB User account named applsyspub to which it first connects during validation of LOGIN. This user account has very restricted privileges and has access to below objects (primarily for authentication purposes):-

• FND_APPLICATION
• FND_UNSUCCESSFUL_LOGINS
• FND_SESSIONS
• FND_PRODUCT_INSTALLATIONS
• FND_PRODUCT_GROUPS
• FND_MESSAGES
• FND_LANGUAGES_TL
• FND_APPLICATION_TL
• FND_APPLICATION_VL
• FND_LANGUAGES_VL
• FND_SIGNONFND_PUB_MESSAGE
• FND_WEBFILEPUB
• FND_DISCONNECTED
• FND_MESSAGE
• FND_SECURITY_PKG
• FND_LOOKUPS

1. All the pl/sql packages will be created in APPS Schema
2. All the views will be created in APPS Schema
3. For each table in individual schema, there will exist one synonym in APPS schema
4. Tables are not created in APPS schema, every implementation has at least 1 custom schema, where custom tables are created.
5. For each custom table created by you, you will need to create a Synonym in APPS schema

Custom tables are generally required in Oracle ERP because:

1. You wish to create a custom screens ( your own screen to capture some info) for a functionality that is not delivered by Oracle.

2. Pre-Interface tables

3. Temp processing

4. Staging of data for third party extract interfaces

To create a new table named, the steps are

Step 1. Create table in custom schema .

Step2. Grant all on schema.table_name to apps

Step 3. connect to apps/apps, and

Create or replace synonym for schema.table_name

Oracle Apps Deployment

• Any executable that has intense database operations is stored at database tier e.g. oracle reports, sql files, sql*loader.
• Any executable that has intense UI operations is deployed at forms tier. Examples are Oracle Forms fmx files, jsp files, pll/plx etc.
• A form that has intense database processing, must handle most of the database processing within pl/sql packages. The api's that you build in pl/sql must have well defined parameters.
• Multiple mid-tiers distributes the user load. The user requests are first sent to a load balancer switch, this switch the decides which middle tier to use.
• You will need to deploy your forms file to each middle tier machine (unles shared APPL_TOP) has been implemented.
• FMB files delivered by oracle are picked from $AU_TOP/forms/us.
• All of the pl/sql packages are installed in apps schema.This includes your custom packages and also oracle delivered packages.
• The forms are attached to form functions and it is the form function that is attached to an application
• To generate CUSTOM.pll: cd $AU_TOP/resourcef60gen module=$AU_TOP/resource/CUSTOM.pll userid=apps/apps output_file=./CUSTOM.plx module_type=LIBRARY

Optional environments:-

• CRP environment: Conference room pilot environment is where usually implementation team gets buying to their product offering from user community, during implementation. This environment is usually used for sign off during new implementations.
• Patching environment: This is where all new Oracle Patches are sanity tested.
• Support environment: This environment is exclusive to support staff. This environment is usually the most frequently cloned environment in Oracle Apps site that has gone live. Frequent cloning helps the Oracle Applications support staff to reproduce production issues
• Migration Environment: This is where repeated data migration can take place before migration code gets frozen and ready for UAT.

E-Business Essentials

The company has two local administrators in separate locations. They need to ensure that only a relevant set of users is visible to each of these local administrators. Which action will meet the requirement?
Create a separate role for each local administrator

Which tier(s) in Oracle E-Business Suite is responsible for storing application data?
Application tier only

If the scenario is:
* X is a legal entity defined in a multilevel company structure.
*There are three Operating Units A, B, and C rolled into it.
*Paul, an assigned user of Oracle Payables, logs in from the Oracle Payables
responsibility and is able to see information that is relevant to Operating unit A.
* The MO:Operating Unit profile option has been set.

All transactions created by Paul are automatically assigned to Operating Unit A.
The Oracle Payables responsibility is associated with an organization classified as Operating Unit A.
The Oracle Payables responsibility is linked to the Operating Unit A by the MO: Operating Unit profile option.

Actions to be taken after defining a structure but before entering values.
1. Select the "Freeze Flexfield Definition" check box.
2. Save your changes before freezing the flexfield definition.
3. Submit a request to build the structure view by selecting the "Compile" button.

A Balancing entity is a:
1. A Legal Entity may comprise one or more than one balancing segments.
2. It is represented as a balancing segment value in the accounting flexfield structure.


Flexfields in 11iOracle Applications:
1. Flexfields consist of segments.
2. Flexfields are used only in Oracle Financials suite of products.

ASC Corp decides to implement Oracle Receivables, Oracle Payables, and Oracle
Purchasing. To access any of these applications, a user must be given access to a
responsibility associated with which type of organization?
An Operating Unit

Shared entities:
1. Enable one-time definition of objects
2. Are referenced by several Oracle Application modules
3. Are owned by a single Oracle Application module for table purposes only
Explanation: Shared Entities are not formally defined as such within any single products user's guide. You will find as you implement multiple products that the same entity is referenced by multiple products

Can a menu be copied?
No

The accounting flexfield of ABC Corp is: Company.Department.Account.Cost Center. Then-
1. Company is a segment in the combination.
2. The account code combination is a key flexfield.
3. The account code combination is a key flexfield as well as a descriptive flexfield.
4. Existing values for the accounting flexfield appear as a concatenated value having
segment separators.


If a user profile option value were to be changed, when would the change take
effect?

1. as soon as the user logs in
2. as soon as the user changes responsibility
3. when the Administration server is restarted
4. as soon as the system administrator exits the instance


As a system administrator, you need to provide additional reporting and summarized information to some of your application users. How would you do this?

1. by using Menus
2. by using Data Groups.
3. by using Request Groups


Can a defined profile value be referenced anywhere in Oracle Applications?
Yes



Event Manager enables you to register subscriptions for events that are significant to an 11iOracle Application instance.


To run reports across organizations in a multi-org environment, the MO: Top Reporting Level profile option should be set at which level?
Legal Entity level or Set of Books level


ASC is in the business of manufacturing cosmetics. It obtains and pays for raw
materials. Which business flow in Oracle E-Business Suite would map to the above activities?
Demand to Build
Explanation: Procure to Pay, since in other options making payments are not covered


Demand to Build: Advanced Supply Chain Planning (ASCP)/Material Requirements Planning (MRP) Create constraint-based or optimized plans, create requisitions (purchase or internal) to
replenish Inventory
Cost Management: Supply cost information for optimized planning
WIP: Use discrete, project, repetitive, assemble-to-order, work order-less, or a combination of
manufacturing methods. Inquiries and reports give you a complete picture of
transactions, materials, resources, costs, and job and schedule progress.
Capacity: Calculate your capacity load ratio by resource or production line. Ensure that you have sufficient capacity to meet your production requirements.
BOM: Store lists of items that are associated with a parent item and information about how each
item is related to its parent


Ownership of shared entities?

1. Set of books is owned by Oracle General Ledger; Locations is owned by Oracle Human
Resources.
2. Suppliers is owned by Oracle Purchasing; Units of Measure is owned by Oracle
Inventory.

ASC has set up the Asset Category key flexfield. It has also set up a descriptive flexfield to store vehicle information associated with the asset category "Vehicle." Identify two correct statements.

The Asset Category flexfield is owned by the Oracle Assets module.
The descriptive flexfield is associated with tables in the Oracle Assets module.

Explanation: While most Oracle Applications products require that you set up
particular key flexfields, many descriptive flexfields are optional.


Parameters in a Daily Business Intelligence Report.

1. They are based on responsibilities.
2. The Primary Dimension parameter is different for each page in a Daily Business
Intelligence report.


Oracle Workflow.
1. each activity can be a PL/SQL function or a notification or a subprocess
2. can integrate business processes within an enterprise as well as between different organizations and different systems


"None" value set.
1. It has an approved list of values associated with it
2. Users can enter any value that they want even if it does not meet formatting
requirements.


Oracle E-Business Suite.
1. It can run entirely on the Internet.
2. A minimum of two modules need to be implemented for an organization.
3. It supports multi-directional flows of business information within an organization.


ASC is installing 11iOracleApplications on two nodes: A and B. On Node A, it has installed the Forms server, Web server, and Administration server. On Node B, it has installed the Concurrent Processing server. By default, on which node would the reports server be installed?
Node B


Oracle Workflow engine does not
1. generates a history of the items
2. is embedded in the Oracle database
3. maintains the state information for a workflow item


Explanation: The workflow engine is a set of tables and PL/SQL stored procedures that manages the execution of a workflow process and tracks work in process. It maintains the state information of a workflow item and generates a complete history for the item (including an audit trail). It executes workflow rules and functions (PL/SQL stored procedures) automatically


An activity in a workflow process can be a PL/SQL function, a notification, a subprocess, or a business event


Determining the size of a value set
1. Your value set size must be less than or equal to the size of the underlying segment
column in the flexfield table.
2. Choosing the maximum size for your value set depends on which flexfield you plan to
use with your value set.


Sequence for enabling multi-org:
1. Define Organization Structure
2. Convert to Multiorg
3. Perform Setups unique Operating Unit.


Explanation: Basic Steps to Enable Multi-Org : Define rganization structure Convert to Multi-Org Perform setups unique to each operating unit ie. Financials options, customer address setup, supplier site setup, bank accounts)


How are code combinations of a key flexfield stored?

1. There is one column for every key flexfield segment.
2. They are stored in a database table called a code combinations table.
3. Each unique combination of segment values is identified by a unique ID value stored in a
unique ID column.


Workflow Monitor

1. enables users to view their own workflows
2. displays status information for the process instance
3. enables administrators to perform control operations0
4. enables you to search for a workflow process instance


In Permanent Persistence values in Workflow purging : Items are deleted only when forced.


ASC has shipped goods against purchase orders to ASC. ASC now has to pay for the goods received, and also account for and reconcile the transaction. Which are the 11i Oracle Application modules involved in these activities for ASC?
1. Cash Managment
2. Order Management
3. Purchasing


Two required components of a responsibility.
1. Menu
2. Data Group


The organization classification that is a segment value in the Accounting flexfield
structure. : Balancing Entity


Exception is a specified condition found during an alert check.


ASC has implemented Oracle Manufacturing applications. How is the data in
these applications partitioned? - by Operating Unit


Identify three features of a value set.

1. The same value set can be shared between different flexfields.
2. Value sets control the values for many of the report parameters.
3. A value set is a definition of the values approved for entry by a particular flexfield
segment.


Can you create an application user with start and end dates that are earlier than the current date?

Yes, the login can be created, but it will not work.


Can you create an application user with start and end dates that are earlier than the current date?

No, the login cannot be created with start and end dates that are earlier than the current
date.

ASC is running on 11i Oracle Applications and wants to ensure that supply records of one supplier are not visible to another supplier. Which action will meet the requirement?

By assigning the same attribute value forVENDOR_CONTACT_ID


Subscription processing.
1. Sends event information to a workflow process
2. Include the task of executing custom code on the event information


A table-validated value set
1. A table-validated value set has no list of approved values associated with it.
2. Table-validated value sets obtain their lists of approved values from existing application tables.
3. A table-validated value set enables you to use your own application tables as value sets
for flexfield segments.


In order for organizations to post to the same set of books, at a minimum they must share the same accounting flexfield structure


Explanation: Those key flexfields that allow multiple structures may use different mechanisms to determine which structure a user sees. For example, the Accounting Flexfield uses multiple structures if you have multiple sets of books with differing charts of accounts. Your forms determine which Accounting Flexfield structure to display by using the value of the GL_SET_OF_BOOKS_ID profile option associated with your current responsibility


Three features of key flexfields.
1. They are used as identifiers for entities.v
2. The structure is comprised of segments.
3. Each segment of a key flexfield will usually contain meaningful information


ASC wants to track and analyze call volumes in its enterprise call centers. Which Daily Business Intelligence product can help achieve this objective?
Interaction Center Intelligence


Which three options are determined by a responsibility in Oracle Applications?
1. Accessible Reports
2. Accessible Applications
3. Associated Set of Books

Functions and menus.
1. Functions can be excluded at any level.
2. Excluding a menu excludes all its child functions
3. Excluding a menu excludes all the functions and menus nested in that menu.

Three types of regions in Daily Business Intelligence
1. Link
2. Parameter
3. Key performance indicator

Explanation: Region: A region is a container for a unique set of information on an dashboard. There are five different types of regions in Daily Business Intelligence: table, graph, parameter, KPI, and links.


A Global segment:
1. It is implemented in descriptive flexfields.
2. It uses the same column for all rows in the table.
3. It is a segment that always appears regardless of the context.


The interaction between the Notification System and the Workflow Engine?

1. The Workflow Engine sends information to the Notification Mailer program, and then informs the Notification System about this communication.
2. The Workflow Engine informs the Notification System to deliver notifications to a user if
an activity requires human intervention.


Prerequisites for viewing notifications through Oracle Workflow Web pages.

1. You must have a Web browser that supports frames and JavaScript.
2. Oracle HTTP Server should be installed as the Web server for workflow.

What would happen if you try saving an existing menu under a different name?
The new menu will overwrite the original menu.

Features of Multi-Org.
1. The data can be logically partitioned.
2. Users can be assigned to specific organizations.
3. It defines organizations and the relationships between them.
4. It enables at most two business units to use a single installation of Oracle Applications.


Four business process integration requirements that can be addressed by Oracle Workflow.
1. Point-to-point system integration
2. System integration messaging hub
3. Distributed applications messaging
4. Business event-based workflow processes

Three features of Oracle Workflow builder
1. Saves workflow definition as a flat file
2. Defines and customizes workflow definitions
3. Has Navigator tree and Process diagram as its components


Flexfield structure describes a group of segments.


As a system administrator, you have to set a profile option specific to an operating unit. Which Profile Hierarchy Level would you choose : Organization Level

Which three tasks do alerts perform?
1. Send an e-mail message.
2. Submit a concurrent request
3. Run an operating system script

Three features of Daily Business Intelligence
1. Provides a cross-enterprise reporting capability
2. Creates a separate subset of transactions in a new database
3. Provides planning, execution, and reporting capabilities to optimize business performance

A region is a container for a unique set of information on an Daily Business Intelligence
overview page.

Explanation: Region: A region is a container for a unique set of information on an dashboard. There are five different types of regions in Daily Business Intelligence: table, graph, parameter, KPI, and links.
Reports: You can drill to a report from any linked value in a dashboard. In general, reports
contain detailed information on one or more KPIs. Reports contain graphs and a table region and
they contain more parameters than a dashboard. Reports do not contain truncated values. You
can drill from some reports directly into the transactional system.
KPI: A KPI is a strategic business factor used for reporting. KPIs are designed for comparing and judging performance on strategic business factors such as Revenue or Operating Margin. Each dashboard contains a set of KPIs that the content of the dashboard is based on. For example, the Revenue and Expenses KPIs serve as the basis for the Revenue and Expense regions and reports.
Dashboard: A dashboard is a collection of content that is designed to meet the needs of a
particular responsibility. Dashboards contain parameters, regions, and Key Performance
Indicators (KPIs)

At which level can you exclude a function from a responsibility?
Menu level, submenu level or function level

Oracle Application Security layers from bottom to top: Function Security, Data Security, and Role Based Access Control

Oracle Workflow and/or Oracle Alerts:
1. Workflow can support complex conditional logic.
2. Alerts can wait for a user response either through email or through a Web page.

Securing Attributes:
1. Is defined at the responsibility level
2. Specifies access to database rows
3. Specifies the database column which the security would be based on in a Web inquiry

Explanation: You may assign any number of securing attributes to the responsibility.Securing attributes are used by Oracle HTML-based applications to allow rows (records) of data to be visible to specified users or responsibilities based on the specific data (attribute values) contained in the row.

Two descriptive flexfield components whose values are used to determine contexts.
1. Context field
2. The Reference field

SQL Performance in ERP 11i

SQL Statement Flow

• Create Cursor - The cursor is the area in memory that holds the definition of a currently open SQL statement.
• Parse SQL - Parsing SQL involves checking the SQL for syntax and objecct references. A check for a matching statement that is already in shared pool is performed. If none is found, then an entry in the shared pool is created and an execution plan for the SQL determined.
• Bind Variables - Before the SQL statement can be executed, the values of any Bind variables- placeholders that represent values to be supplied by the calling program - must be associated with the SQL.
• Execute SQL - If the SQL statement is not a query, then execute involves processing the DML or DDL statement in its entirety. If the SQL statement is a query, then execute prepares the statement for fetch operations.
• Fetch Rows - For queries, fetching involves retrieving one or more rows from the result set.
• Close Cursor - This invloves releasing all the memory and resources associated with the cursor.

•Components of a SQL Statement
–Parse
–Execute
–Fetch

Parsing is the process of preparing your SQL statement for execution. This process is analogous to the process a language compiler or interpreter must undertake in order to translate high-level statements into machine code.
The parse process will check that the SQL statement is syntactically valid, that the SQL conforms to the rules of the SQL language, and that all keywords and operators are valid and correctly used”

Parse’s Duties:
• Translating a SQL statement, verifying it to be a valid statement
• Performing data dictionary lookups to check table and column definitions
• Acquiring parse locks on required objects so that their definitions do not change during the statement’s parsing
• Checking privileges to access referenced schema objects
• Determining the optimal execution plan for the statement
• Loading it into a shared SQL area
• Routing all or part of distributed statements to remote nodes that contain referenced data

Reducing Parse overheads:

1. Always use Bind variables in preference to literals for SQL statements that will be reexecuted.
2. Dont needlessly discard cursors that may be reexecuted later in your program.

Execute is the processing phase of query flow.
•If the sql statement is not a query , then execute involves processing the DDL or DML statement in its entirety.
•If the sql statement is a query, then execute prepares the statement for fetch operations

Performance Considerations: A review of the number of joins and whether necessary and the selection criteria will ultimately affect the run time of this phase.

Fetch: In the fetch stage, rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result until the last row has been fetched.

Cost Based Optimizer

•The CBO is the decision maker on how a sql query is actually processed.
•Uses information from within the database and the sql statement to come up with the best plan.

The CBO performs the following steps:
•CBO The optimizer generates a set of potential plans for the SQL statement based on available access paths and hints.
•The optimizer estimates the cost of each plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables, indexes, and partitions accessed by the statement. The cost is an estimated value proportional to the expected resource use needed to execute the statement with a particular plan. The optimizer calculates the cost of access paths and join orders based on the estimated computer resources, which includes I/O, CPU, and memory. Serial plans with higher costs take more time to execute than those with smaller costs. When using a parallel plan, however, resource use is not directly related to elapsed time.
•The optimizer compares the costs of the plans and chooses the one with the lowest cost.

The CBO is the decision maker on how a sql query is going to be processed. It makes decisions on what tables will be joined and in what order. It also decides on which selection criteria within a sql statement is used and in what order.
The CBO takes the query and also information from a number of different sources within the database to decide the best plan of attack for a query. The CBO will review over a 1000 combinations and with each providing a costing algorithm based on the information it pulled from the database, and then selecting the best plan according to its costing algorithm.
Within this framework, the CBO can be wrong for a number of reasons that range from information provided being incorrect ie. old statistics to not reviewing enough plans on a complex query and missing the optimal plan.

Query Transformer : The input to the query transformer is a parsed query, which is represented by a set of query blocks. The query blocks are nested or interrelated to each other. The form of the query determines how the query blocks are interrelated to each other. The
main objective of the query transformer is to determine if it is advantageous to change the form of the query so that it enables generation of a better query plan.


STATISTICS

–Tools of the CBO to make decisions. It is the primary tool used by the CBO to decide how to breakdown the query and process the table interactions.
–The CBO has other tools including histograms, which provide more info built on the basic statistics.

• A developer should be able to tell when the last time statistics were run on a table and how many rows the CBO thinks are in a table.
• Can look in DBA_TABLES at num_rows and last_analyzed

The statistics are incorrect?

• Statistics can be refreshed
• Request DBA team to generate statistics and let them know the specifics:
–Do you just need one object or the whole schema to be regenerated
• It is possible to generate statistics yourself, but it is generally not recommended even if you have the access, due to the potential system performance issues.
• Since 8i, Oracle has provided a package, DBMS_STATS that can be used to gather statistics for tables, indexes, and schemas. If interested one can review metalink for further details.
• Also within the Apps, there are certain concurrent programs that can also perform the function.

Families of tools

SQL Toolset : Those that can be implemented in SQL and SQL tools

The following tools are from the family of SQL tools and are the most utilized when gathering data on a performance issue. The tools many times are used in conjunction with each other to solve/analyze a problem.
•EXPLAIN PLAN
•METHODS OF TRACE
•TKPROF
•SQL QUERIES FOR PERFORMANCE

•SQL – explain plan
•Allows you to see what the CBO is seeing
•Can modify code and see how it changes the way the CBO will process
•Can let you know what indexes and tables are going to be used and how the data connections between tables are going to being handled

–When to use it?
•It gives a quick snapshot on a query to determine how it will run
•Lets you see if you are missing an index you thought you would hit
•Can be run in an environment without having to compile code or having access to compile code


–What it cannot do?
•It cannot tell you what adding an index will do. There is no way with these tools to do hypothetical analysis.
•There are other tools via OEM like virtual index and Hotsos Laredo that can allow some forms of impact analysis.

SQL – Explain Plan

–What is underlying structure?
A script called utlxplan.sql creates a table that defaults to PLAN_TABLE. In SQL Navigator, the tool usually utilizes a plan table called SQLN_EXPLAIN_PLAN. The SQL Navigator plan table will not potentially exist in all environments.

Explain Plan - Manual

1. Create plan for your query:

EXPLAIN PLAN

SET statement_id=id

INTO table_name

FOR sql_statement

Example: -- Use apps.SQLN_EXPLAIN_PLAN for the table_name and anything you would like for the id.

EXPLAIN plan set statement_id = 'JPG123'
INTO apps.SQLN_EXPLAIN_PLAN
FOR
SELECT count(*)
FROM OKC_k_LINES_B A, OKC_K_LINES_B B
WHERE B.LSE_ID = 10 AND A.ID = B.CLE_ID
AND A.ID = 10

2. Select explain text from database:

set heading off;
set pagesize 0;
SELECT rtrim(ltrim(plan_table_output,' '),' ')
FROM TABLE (DBMS_XPLAN.DISPLAY('SQLN_EXPLAIN_PLAN',,'TYPICAL'))

Example: --Statement id from above was JPG123....
set heading off;
set pagesize 0;
SELECT rtrim(ltrim(plan_table_output,' '),' ')
FROM TABLE (DBMS_XPLAN.DISPLAY('SQLN_EXPLAIN_PLAN','JPG123','TYPICAL'));


SQL – Trace

–Data gathering tool that records information to be used by other tools
–In certain situations can provide valuable information from raw trace including bind information.
–just recording the SQL and not any PL/SQL. This is ok for most cases since SQL accounts for 90% of all performance issues.

–Types of Tracing (Trace, trace w/ binds, trace w/ waits, trace w/ binds,waits)
•You can initiate with different info …
•What is a wait?
•What is a bind value?
•You can rebuild a query with binds to see what data was actually ran

–Tips/Tricks with Trace files?
•Can find out what values used (binds)
–Important Note - the bind numbers do not coordinate between the query numbers and the bind values
•Can use to estimate how fast code moving
–take a 15 min trace and find a query that you know how many rows need processing and see how many it got done in 15 min… can help to extrapolate…
•Can use to find out where something is at
–If you have the whole trace going, then run a tkprof at any time and review how many times a specific query has run… for example, if I know I have a query running that runs once per line, and I have 10,000 lines, and when I run trace/tkprof I have 7200 executions then I know it has completed 7200/10000

Methods of Turning on Trace
•Current SQL Session
•Another SQL Session
•Concurrent Program
•Individual instance of Concurrent Program
•Form

Tracing your own session is simple.
SET IDENTIFIER FOR SESSION (Optional)
SQL> alter session set tracefile_identifier='MYSESSION‘
ENABLE TRACE
SQL> alter session set events '10046 trace name context forever, level 8'
- sets extended SQL trace to level 8.
DISABLE TRACE
SQL> alter session set events '10046 trace name context off'


Alter session set
tracefile_identifier=‘Garringer’;
•You have up to 64 characters that will be tacked on to the end of your trace file.
•These traces are created in the User Dump Destination.
•Check the User Dump Destination parameter on your system for the location of these trace files.

DBMS_SUPPORT package is preferred over DBMS_SYSTEM and is fully supported by Oracle.
The base install script is dbmssupp.sql script and is run as sysdba. Located in $ORACLE_HOME/rdbms/admin directory.
Start Tracing -- equivalent to level 8 tracing, bind=>true would be equivalent to level 12 tracing SQL> execute sys.dbms_support.start_trace_in_session(&&SID, &&SERIAL, waits=>true, binds=>false); .
Stop Tracing SQL> execute sys.dbms_support.stop_trace_in_session(&&SID, &&SERIAL);

How do I find my trace file?
General Method
Find server you are on
select instance_number, instance_name, host_name from v$instance;
Find directory for trace files
select value from v$parameter where name = 'user_dump_dest';

From Unix Box
cd to $TRACE_TOP
Important: Need to make sure you are on right Unix server

WAIT ANALYSIS
•For timed wait event collecting you must have the parameter timed_statistics=true. You can do this at the system level or session level.

TRACE FILE SIZE
•You should also very the max_dump_file_size. If it is too small, you will receive a message like the following in your trace file……

WAIT #7: nam='db file sequential read' ela= 88 p1=6 p2=37500 p3=1 WAIT #7: nam='db fil *** DUMP FILE SIZE IS LIMITED TO 10000000 BYTES ***


SQL Trace & Concurrent Program
How to Start/Stop for all instances of a concurrent program
Steps:
1.Login to System Adminstrator Responsibility
2.Go to Concurrent : Program : Define
3.Query up concurrent program
4.Click Enable Trace Button
Note: This enables trace for all instances of this concurrent program run after the button is selected
In production, you will need to request this from the DBA team.

How to Start/Stop for a particular instance of a concurrent program
1.Verify that Profile value for “Concurrent: Allow Debugging” is set to Yes
2.When submitting a concurrent program If profile is Yes, then the debug options will be enabled
3. Click on Debug Options
4. Check SQL Trace and select type of trace and then click on OK

Concurrent Program - How to locate the trace file
1.Determine the instance the program is running on
select Logfile_node_name
from fnd_concurrent_requests
where request_id = l_request_id;
2.Translate the instance name into the inst_id
– select * from gv$instance shows mapping between Inst_id and the instance name
IF l_logfile_node_name = 'SUN4769' THEN l_inst := 1;
ELSIF l_logfile_node_name = 'SUN4229' THEN l_inst := 2;
ELSIF l_logfile_node_name = 'SUN4676' THEN l_inst := 3;
ELSE l_inst:= 4;
END IF;
3. Go to $TRACE_TOP on that server and grep for your request id

Form Trace:

Initiate a Trace
1.Select Help
2.Select Diagnostics
3.Select Trace
4.Select type of Trace you would like

Stop a Trace
To stop the trace perform the same steps
as the initiate but select “No Trace”


How to locate trace file
– When Trace is initiated a message window will appear with the trace file information. In the location, the server name and the file name are provided.

SQL – TKPROF

Tkprof is an executable that 'parses' Oracle trace files to produce more readable output. Remember that all the information in TkProf is available from the base trace file.
If you have a system that is performing badly, a good way to identify problem SQL statements is to trace a typical user session and then use TkProf to format the output using the sort functions on the tkprof command line.
There are a huge number of sort options that can be accessed by simply typing 'TkProf' at the command prompt. A useful starting point is the 'fchela' sort option which orders the output by elapsed time fetching (remember that timing information is only available with timed_statistics set to true in the "init.ora" file). The resultant .prf file will contain the most time consuming SQL statement at the start of the file.

Column Meanings
===============
call : Statisics for each cursor's activity are divided in to 3 areas:
Parse: statisitics from parsing the cursor. This includes information for plan generation etc.
Execute: statisitics for the exection phase of a cursor
Fetch : statistics for actually fetching the rows
count : number of times we have performed a particular activity on this particular cursor
cpu: cpu time used by this cursor
elapsed: elapsed time for this cursor
disk: This indicates the number of blocks read from disk. Generally you want to see blocks being read from the buffer cache rather than disk.
query : This column is incremented if a buffer is read in Consistent mode. A Consistent mode buffer is one that has been generated to give a consistent read snapshot for a long running transaction. The buffer actually contains this status in its header.
current: This column is incremented if a buffer found in the buffer cache that is new enough for the current transaction and is in current mode (and it is not a CR buffer). This applies to buffers that have been read in to the cache as well as buffers that already exist in the cache in current mode.
rows: Rows retrieved by this step

How to Run a TKPROF?
TKPROF COMMAND LINE
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAIN.
print=integer List only the first 'integer' SQL statements.
aggregate=yesno
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
sort=option Set of zero or more sort options

–Potential Issues
•Run . oraenv or timing will be incorrect
•Explain plans are now not when they ran


SQL QUERIES FOR PERFORMANCE

Difference between v$ and gv$ tables –
In the database, oracle provides a number of tables that begin with v$ and gv$. An example is v$session and gv$session. The main difference between the v$ and the gv$ tables is the scope that they view. The v$ tables only return information for the server it is executed from.
If you are on sun1089 and select * from v$session you will only see information on sessions from sun1089. On the other hand, if you select * from gv$session, you will get session information on all sessions on all servers in the RAC’d environment. Each record will contain an instance id that differentiates the server.

–Useful Queries
•General Query for bad explain plans
–sort by elapsed time
–search for full table scans
–can search by schema
–can search by table
–can search by a specific module or specific query
•Query that looks at concurrent requests
•Query to find large number of reparses

Find out what SQL a concurrent program is currently running
1.Determine the oracle process id and the instance the program is running on
select oracle_process_id , Logfile_node_name
into l_oracle_process_id, l_logfile_node_name
from fnd_concurrent_requests
where request_id = l_request_id;
dbms_output.put_line('****************************************************');
dbms_output.put_line('Request ID: 'l_request_id);
dbms_output.put_line('Oracle Process ID(SPID): 'l_oracle_process_id);
dbms_output.put_line('Logfile Node: 'l_logfile_node_name);
2.Translate the instance name into the inst_id
– select * from gv$instance shows mapping between Inst_id and the instance name
IF l_logfile_node_name = 'SUN4769' THEN l_inst := 1;
ELSIF l_logfile_node_name = 'SUN4229' THEN l_inst := 2;
ELSIF l_logfile_node_name = 'SUN4676' THEN l_inst := 3;
ELSE l_inst:= 4;
END IF;
3. Use oracle process id and instance id to get the SID value
select to_char(b.sid)
into l_sid
from gv$process a, gv$session b
where addr = paddr
and a.inst_id = b.inst_id
and b.inst_id = l_inst
and a.spid = l_oracle_process_id;
dbms_output.put_line('SID: 'l_sid);
4 Use Sid and Inst_id to determine sql statement that is currently running….
BEGIN
SELECT a.sql_text, a.hash_value, a.address, a.executions
Into l_sql_text, l_hash_value, l_address, l_executions
from gv$session s, gv$sqlarea a
where s.sql_address = a.address
and a.inst_id = s.inst_id
and a.inst_id = l_inst
and s.sid = l_sid;
dbms_output.put_line('Hash value: 'l_hash_value);
dbms_output.put_line('Address: 'l_address);
dbms_output.put_line('Executions: 'l_executions);
dbms_output.put_line('sql_text: ');

•--To find your session's sid and oracle spid
select to_char(b.sid), a.spid
from gv$process a, gv$session b
where addr = paddr
and a.inst_id = b.inst_id
and b.sid in (select sid from v$session
where audsid =sys_context('userenv','SESSIONID'))

PROFILER TOOL

•Since SQL is usually attributable to over 90% of a performance problem a developer fixes, this tool performs basically the same function as SQL trace.
•The major advantage is that is provides a quicker path to the bad queries using a percentage of time spent base algorithm.

Profiler Comprised of
–Oracle DBMS_PROFILER package which is installed by a DBA and is owned by the SYS user
–Three Tables that are created either in the users schema or a shared schema with synonyms.. This is where the information from the profiling goes.

Profiler Table Structure

1. PLSQL_PROFILER_UNITS
   -- UNIT_NAME (NAME)
   -- UNIT_TYPE (TYPE)
   -- UNIT_OWNER (OWNER)
2. PLSQL_PROFILER_DATA
   -- Total Occurences
   -- Total Time
   -- Minimum Time
   -- Maximum Time
   -- Line# (Line)
3. PLSQL_PROFILER_RUNS
   -- Run Id
   -- Run Date
   -- Run Comment
4. ALL_SOURCE
   

Standard setup for DBMS_Profiler
Only use the dbms_profiler.start and dbms_profiler.stop
Do not use the other commands including flush, which limits the size of the profiled code. However, in our testing, it has not inhibited our ability to perform a profile

Profiler Example:

declare
TheRetcode varchar2 (1);
TheErrbuf varchar2 (2000);
err number;
BEGIN
err:=DBMS_PROFILER.START_PROFILER ('PrevalTest: 'to_char(sysdate,'dd-Mon-YYYY hh:mi:ss'));
dbms_output.put_line('PROFILER START= 'err);
ASC_CFS_IB_INTF_VALIDAPI_PKG.ASC_CFS_IB_INTF_MAINBODY_RUN (TheErrbuf, TheRetcode);
dbms_output.put_line('ErrBuf= 'TheErrbuf);
dbms_output.put_line('Retcode= 'TheRetcode );
err:=DBMS_PROFILER.STOP_PROFILER ;
dbms_output.put_line('PROFILER STOP= 'err);
END;

DBMS_Profiler.Start

•Command starts the profiler run
•The passed in parameter gives an user designated name to the profiler run



DBMS_Profiler.Stop


•This command stops the profile being run
•It also flushes the information stored in memory into the three tables.

Forms Runtime Diagnostic (FRD)

JSP Diagnostic Trace

1.Set profile FND : Diagnostics to Yes at user level
2.Login to Self Service as the above user
3.Click on Diagnostics icon at the top of page. Select Set Trace Level and click Go Select the desired trace level (trace with bind and wait) and click Save
4.Perform the activity that you want to trace (pick up owner)
5.Exit application
6.Go to user_dump_dest to find out trace file

Performance Analysis

–Analysis
•Define/Determine the issue – may not be possible to understand why an issue, but should be able to narrow to it using data tools…
•Locate the poor performing statement within the code and try to understand the context when it is used.
•Determine if it is an issue with the code or whether it is the sql

Assumptions –
statistics are accurate....
Baseline data has been gathered and analyzed

Improving the code statements:

Add indexes –
No impact on what rows are returned from the query, just how the data is being gathered.
This can be risky, as it has the potential to impact other statements on the same table.
In addition to impacting other statements, is that indexes will slow Insert, Update, and Delete operations.
Therefore any table that is heavily pounded with Inserts, Updates, and Deletes could cause some code to slow down.
Also, will not necessarily improve the situation if dealing with a heavily skewed column.


Adding hints --
Many times bad performance can be attributed to the cost based optimizer choosing a bad explain plan.
The hints have the capability of helping to direct Oracle on what path is the best. The reason that you might have a better idea, is that you know the data makeup better than Oracle.
One benefit of this approach is that it is localized to a particular statement. However, there is no guarantee that the optimizer will use the hints.

Rewriting Queries –
After working with the indexes and the hints, you still determine that the query is too slow, then you can attempt to rewrite the query using performance tips and tricks.
These tricks range from utilizing Exists instead of IN, using decode & sign functions instead of multiple or loops, using SQL With clause to reduce redundant processing in queries, etc. There are many more ways that you can modify a query without changing the fundamental joins that will cause it to run faster.
Also in rewriting the query is evaluating the actual tables involved and determining if they are all necessary, and whether there are better tables.
A good example, is you are utilizing a view in a query that has 5 table joins in it, and all you are looking for is a small subset from the smallest table in the view. In these cases, a lot of times the view was chosen for ease in coding, and the whole query can be rewritten to go to the single table and that alone will provide a large improvement.


Redesigning the User perception of performance –
We will look at the contracts QA process. Currently the QA process requires users to move a contract from QA hold to Active on the screen and forces them to be active participants in the process. Until the QA finishes the user cannot do anything else on their pc. If it takes 15 minutes to perform a QA then they wait 15 minutes and lose that from other productive activities.
The performance question is how do I make this passive instead of active, or how do I move it to the background. A user would be much happier with the 15 minutes of processing time, if it is running in the background and they can go on with other activities and return to it in a half hour.
Therefore, a performance gain can be obtained by a shift in the paradigm of the user activity. By taking the QA to a concurrent program we are shifting the impact away from the user, and in the process increasing their willingness to wait and in that regards decreasing the amount of pain they receive from 15 min QA's.
Although performance has not really been improved, it has been shifted in such a way, that the end result is a performance improvement at the user level for their experience with the software they are working with.

Reducing Number of times a query runs --

In the coding process, there are many instances of code where the code is efficient, but because of the pl/sql design is run an exhorbinant amount of times. In these cases, you can attempt to tune the query all day, but you just are not going to get enough incremental gain to warrant the effort. What really needs to happen is you need to review the whole code block and determine a more efficient way of running through the code, or reducing the amount of times it is necessary.
There are two quick examples of this type of activity. The first is the embedded for loops. They are very easy to develop and follow logic but many times produce a multiplier in the inner most for loop's queries. For example, you have a for loop that has a for loop in it that has a for loop in it and in that one is a query. That query is going to run (outer loop * 1st inner loop count * 2nd inner loop count) times. Many times it is slightly more complex but thousands of times more efficient to get all the data in one cursor loop and then programmically in pl/sql loop through the code. Calls to the database attribute to over 90% of the time a piece of code runs for, and are the most likely to cause performance issues.
The second example is a higher level overall design example. Currently in Oracle contracts we run a QA process, a set of pl/sql scripts that validate a contract is OK, when we take a contract from entered status to Active Status, or when we make changes to a contract from QA Hold status back to Active status. If someone goes in and updates a few rows on a contract or adds a few rows, the current QA process not only interrogates those rows it also revalidates every other row on the contract. For example, a contract with a 100,000 rows where 5,000 are added would cause the QA to revalidate all 105,000 rows. This is excessive since the original 100,000 did not change. Now if we take a step back and say how can we only validate the 5000. This will produce a magnitude of performance improvement that exceeds anything we can do by improving a couple of queries because in contracts the not reviewing 100,000 rows is equivalent to not performing 1,000,000 queries.


Analysis

Review possible modifications that can improve – tend to peel back layers and ask lots of questions of what trying to do and what it is doing.
–Full table scans not always bad, but if not expected probably are
–Using views with too many extra tables
–Implicit datatype conversions
–Selectivity of data skewed – Histogram
–Indexes not as selective as you thought – Just because it used as index does not mean that is the best it can be
–Bad SQL – can you rewrite to improve
–Index Potential – can I help with existing indexes
–Index Potential -- can I add a new index
–Can I avoid doing this query altogether
–Hints – benefits and dangers
•Can improve a query, but is susceptible to CBO changes
•How to implement
•Different kinds of hints

The Process:

•Step #1 – Understand the problem
-- Usually something is too slow… but what do they mean by that….
Examples: Going from A to B is slow
-- Is it always the case or just during certain times … When is it bad? Does it happen in test or just in production
-- Does everyone experience it or just particular business units or locations?


Step #2 – gather the data for analysis
-- Need trace information
-- Understand what is happening in GV$SQL_PLAN
This is dependent on the problem but the goal is to acquire all relevant information using methods described earlier
Example: A long running concurrent request set… I tend to work from the worst timings inward… so I will look at the concurrent programs and determine which one is the worst and get analysis for that one….

Step #3 – Review the Data asking lots of questions
-- The information here is in the context of what you found in Steps #1 and #2.
This is where we start analyzing the explain plan, the trace file, tkprof, and the internal perf tables like gv$sql_plan.
Some key elements:
-- Look for the obvious… Full table scans when the query has an equality predicate ie. Line_number = 123456.
-- Look for mismatch between number of rows evaluated versus the plan estimate ie. Skewed data that causes an index a large number of rows returned when only should have been a small number

Step #4 -- Brainstorm on solution
I will try to come up with some different possibilities. Usually this is actually the hardest part of the process and the one requiring the most creativity.
The first question is usually how intrusive the solution will be, which usually relates directly to the risk of impacting other areas or data integrity.
This tends to be very problem specific, but the usual list I go through is:
1.Will an index help
2.Can I add a hint to the query
3.Would a histogram help
4.Can I rewrite the query ie. Change dnz_chr_id to chr_id or remove a view
5.Can I rewrite the code to avoid problem
6.Can I change the way this code runs…

DBA team tasks:
–Can initate a trace in a different session
–Have access to other tables that can help find overall bad queries
–Monitor System wide setups and settings

Oracle parameters for trace
•Init.ora
–udump
–max size of tracefile
•V$session

•There are many tools that can be utilized to help better understand what is happening inside of code.
•Proper use of these tools will point you in the right direction and help you narrow down to the problem.
•Basic tuning concepts can alleviate a large number of performance problems

Concurrent Requests in 11i

Concurrent Requests

Setting Up the Concurrent Executable : The steps to register the executable for a report are as follows:

1. Log on to the Oracle Application, choosing the System Administrator or Application Developer responsibility.
2. The path to follow to the Concurrent Program Executable form is:

System Administrator - Concurrent --> Program --> Executable
Application Developer - Concurrent --> Executable

3. In the Executable field, enter the name of the executable file, leaving off the extension.
4. The Short Name should be the same as the value entered in the Executable field, with the exception of an executable that will be used multiple times.
5. Depending on which function the program is proform,ing the Application field should be
‘ASC Custom – General’
‘ASC Custom – Interfaces’
‘ASC Custom – Invoice Engine’
‘ASC Custom – Migration’
‘ASC Custom – Transition’
6. The Description field should be a user-defined description.
7. The appropriate method should be entered for Execution Method. Possible methods include:

FlexRpt The execution file is written using the FlexReport API.
FlexSql The execution file is written using the FlexSql API.
Host The execution file is a host script (e.g. unix shell script)
Oracle Reports The execution file is an Oracle Reports file.
PL/SQL Stored Procedure The execution file is a stored procedure or function (optionally in a package)
SQL*Loader The execution file is a SQL script.
SQL*Plus The execution file is a SQL*Plus script.
Spawned The execution file is a C or Pro*C program.
Immediate The execution file is a program written to run as a subroutine of the concurrent manager.

8. The Execution File Name is identical to the value entered in the Executable and Short Name fields.

Once this information is saved, Oracle will recognize the executable file.


Setting Up The Concurrent Program : The steps to register the form for a report are as follows:

1. Log on to the Oracle Application, choosing the System Administrator or Application Developer responsibility.
2. The path to follow to the Concurrent Programs form is:

System Administrator – Concurrent -> Program -> Define
Application Developer - Concurrent -> Program

3. The Program Name is the name the user will use to reference the report. It should start with ASC Program Name. If the Program is a statutory requirement for a country, it should end with -Country Code.
4. The Short Name is the name associated with the executable.
5. The Application is the same described in step 5 in the previous section.
6. The Description should be the name of the report entered as the Program Name.
7. The Name in the Executable block should be the name of the executable. The Method will default to the value entered as the Execution Method for the executable.
8. In the Request block, if you want to associate the program with a predefined request type, enter the name of the request type in the Type field. The request type can limit which concurrent managers can run your concurrent program (e.g. this is how the US production installation enforces some programs to be able to be run through a “Batch” concurrent manager only).
9. Set the Use In SRS checkbox if users should be able to submit a request to run the program from a Standard Request Submission window (e.g. from a “Reports” menu). If unchecked, the program would probably be started from another program or from a special form, only.
10. If you check the Use In SRS box, you can also check the Allow Disabled Values box to allow a user to enter disabled or outdated values as parameter values.
11. To indicate that the program should run alone relative to all other programs in the same logical database, set the Run Alone box. In most situations, this should remain unchecked.
12. Setting the Enable Trace box turns on SQL tracing when the program runs. This should only be checked on during adhoc, diagnostic purposes.
13. Set the Restart On System Failure box to indicate that the program should automatically restart after a system failure.
14. In the Output block…For Reports, the Format should be text, and the Save and Print boxes should be checked. The Columns, Rows, and Style depend on the layout of the output. Current standards are:

Portrait – 80 columns by 66 lines
Landscape – 132 columns by 45 lines

For programs that are not reports, the Save and Print boxes do not need to be checked.

15. Style Required and Printer are optional. The printer should not be set at this level. This should be done when submitting the request.

Save the form. The report is now registered within the Oracle application.

Adding Parameters to Concurrent Programs : The steps to add parameters to a report are as follows:

1. Log on to the Oracle Application, choosing the System Administrator or Application Developer responsibility.
2. The path to follow to the Concurrent Program form is:

System Administrator – Concurrent -> Program -> Define
Application Developer - Concurrent -> Program

3. Run a query to view the desired report. Click on the Parameters button on the lower right to open the Parameters form.


4. The Program and Application fields are populated automatically.
5. Enter Seq number, Parameter name, and Description. The Sequence number specifies the order in which the program receives parameter values from the concurrent manager. The Parameter name is the name that will display when the application prompts for parameter values. The Description is optional. The Enabled box will be checked by default.
6. In the Validation block, enter the name of the value set you want the parameter to use for validation in the Value Set field. The description will be populated automatically.
7. If you want to set a default value for this parameter, enter it in the Default Type field. Valid types include: Constant, Current Date, Current Time, Profile, SQL Statement, and Segment.
8. If you have chosen a default value other than Current Date or Current Time, enter a default in the Default Value field.
9. If the program executable file requires an argument, check the Required box for the parameter.
10. If the value set allows security rules, you can apply any security rules defined for the value set. If it does not allow security rules, this field is display only.
11. In the Range field, choose either ‘Low’ or ‘High’ if you want to validate your parameter value against the value of another parameter in this structure. Parameters with a range of ‘Low’ must appear before parameters with a range of ‘High’ (the low parameter must have a lower sequence number than the high parameter). For example, if you plan two parameters named "Start Date" and "End Date," you may want to force users to enter an end date later than the start date. You could assign "Start Date" a range of ‘Low’ and "End Date" a range of ‘High’. In this example, the parameter you name "Start Date" must appear before the parameter you name "End Date." If you choose ‘Low’ for one parameter, you must also choose ‘High’ for another parameter in that structure (and vice versa). Otherwise you cannot commit your changes.
12. If this parameter needs to be displayed in the Parameters window when a user submits a request to run the program from the Submit Requests window, check the Display box. The length in characters should be entered in the Display Size, Description Size, and Concatenated Description Size fields. The Prompt field should contain the label the user will see in the Parameters window when submitting a request.
13. For a parameter in an Oracle Reports program, the keyword or parameter appears in the Token field. The value is case insensitive.
14. To enter another parameter, set focus on the Seq field immediately below the one just entered.

Note: The values in the Validation block, Display block, and Token fields are specific to the parameter being entered. When setting focus on the next line to enter Seq and Parameter, these fields will have to be entered again for the new parameter.

15. After all parameters are entered, save the information.

Setting Incompatibilities For A Concurrent Program

This screen identifies programs that should not run simultaneously with your concurrent program because they might interfere with its execution.

1. Log on to the Oracle Application, choosing the System Administrator or Application Developer responsibility.
2. The path to follow to the Concurrent Program form is:

System Administrator – Concurrent -> Program -> Define
Application Developer - Concurrent -> Program

3. Run a query to view the desired report. Click on the Incompatibilities button to open the Incompatible Programs form.



As with the Parameters screen, the Program and Application fields at the top are automatically populated.

4. Although the default for the Application field is the application of the concurrent program, any valid application name can be entered.
5. A value must be entered in the Name field that, with the application, uniquely identifies a concurrent program.
6. Enter ‘Set’ or ‘Program Only’ in the Scope field to specify whether the concurrent program is incompatible with this program and all its child requests (Set) or only with this program (Program Only).
7. Save the form.

Adding The Report To A Program Group

1. Log on to the Oracle Application, choosing the System Administrator responsibility.
2. The path to follow to the Concurrent Program form is: Security -> Responsibility -> Request
3. Run a query using the Application field, entering the application the report will be run in (Oracle Assets - GADFMS, Order Entry Custom, etc.) Alternatively, the default request group can be determined by querying the responsibility (The path to this screen is Security -> Responsibility -> Define). A query can then be run for the Responsibility to add the report to, and then retrieve the default Request Group Name.
Each application should have a general report group defined, usually called ‘All Reports’. Custom request groups can be set up by the System Administrator which allow the report to be run only by certain user responsibilities.
4. Tab the cursor to the first record under the Type column and select ‘New Record’ from the Edit menu option. This will allow a new row to be entered.
5. Set the Type to ‘Program’ or ‘Set’ to add one item.
6. Enter the report name in the Name field, and the application it was registered in (Oracle Assets – GADFMS, etc.) will be populated in the Application field.
7. Save the new record. After logging on to the appropriate application, the user should now see the report listed when submitting a request.


Concurrent Request Sets
Responsibility: (N) Requests->Set

When creating a new Set there are now two options of how the developer may choose to have the individual requests complete. The requests may run in sequence, like in 10.7, or if determined to be more efficient the requests may run in unison.
The best way to create a Set is through the Request Wizard which is located at the bottom of the Request Set block.

Organizing Requests with Stages : Request sets are divided into one or more "stages" which are linked to determine the sequence in which your requests are run. Each stage consists of one or more requests that you want to run in parallel (at the same time in any order). For example, in the simplest request set structure, all requests are assigned to one stage. This allows all of the requests to run in parallel.

To run requests in sequence, you assign requests to different stages, and then link the stages in the order you want the requests to run.

The concurrent manager allows only one stage in a request set to run at a time. When one stage is complete the following stage is submitted. A stage is not considered to be complete until all of the requests in the stage are complete. One advantage of using stages is the ability to run several requests in parallel and then move sequentially to the next stage. This allows for a more versatile and efficient request set.



Using Stage Status : Like request sets and concurrent requests, stages can complete with different statuses. Each stage can complete with a status of Success, Warning, or Error. You can use these completion statuses to structure your request set, by defining which stage will follow the current stage based on its completion status. For example, the request set in the figure below always begins with Stage 1. If Stage 1 were to complete with the status Warning, then the Warning link would be followed, and Stage 3 would be submitted. After Stage 3 completes, the set ends, since there are no links that may be followed.



Linking of Stages : There are no restrictions on linking stages within a request set. Any stage may be linked to any other stage, including itself. Two or more links can point to the same stage. For example, Stage 1 can link to Stage 2 if the completion status of Stage 1 is Success or Warning, and link to Stage 3 if the status is Error.



The developer determines the end of a request set by not specifying a follow-up stage for each completion status. You can end a request set after any stage in the request set. When any stage completes with a status that does not link to another stage, the request set ends.
You can use the linking of stages to control your request set. In previous releases you had three options: run in parallel, run sequentially, and run sequentially but abort on error. All of these are easy to recreate using the request set wizard. You can use the Request Set Wizard button in the Request Set window to start the wizard. The wizard takes your input and creates the request set as follows:

Run in Parallel : Creates one stage containing all of the requests you wish to run in parallel.
Run Sequentially Creates a separate stage containing the request or requests for each step in the sequence and link in the appropriate order.
Run Sequentially but abort on Error Sets up your sequence the same as it did for Run Sequentially, but when it links the stages, it does not enter a follow up stage as a link in the Error completion status field.


Other : There is now an option available to notify an individual(s) of the completion of specific Request Sets. This option is located under Completion Options in the Request submission block.

Printing : Print Options (Style, Printer, and Number of Copies) are located under Completion Options on the Request submission block.
It is possible to reprint a Request without rerunning the Request. From the Request submission block access Special->Reprint. The cursor must be on the Request the user wants to reprint

Output and Statuses : It is still possible to view the Details, Report, or Log of the set on the Request submission block. These are located at the bottom of the block.

It is possible to put on a Hold, or to Cancel a Request from the Request summary block by the buttons located at the bottom of the screen.

Concurrent Managers
Concurrent Managers and the Concurrent Managers Queue can be located through the Request summary block by accessing Special->Managers located on the blocks’ toolbar.

The Requestor field is now under the Details button located on the Request summary screen or the Request Queue which can be located from the Request summary block by accessing: Special->Managers->Managers Queue

The Priority of the Set is displayed under two different views now: the View Details block and the System Administrator responsibility. It is no longer visible in the Request summary block.
The System Administrator responsibility gives both the requestor and priority in the Request summary block. These fields replace the “Parameters” field shown in other responsibilities.

Reports/Report Sets are now termed Requests/Request Sets.

Concurrent Managers Queue : Located in the Concurrent Managers Queue block and also in the Request Queue block is a check box on the upper right side. If this box is checked it will allow the screen to be refreshed automatically. If left unchecked it will refresh only upon a new visit to the block.

Copying a Prior Request : This option is available on the Request submission screen. In addition to the name of the set it also provides dates for which the Set was submitted and also the Parent ID of the Request Set. Copying the Request will also copy the parameters that were entered originally for that report and create a unique Request ID.

Submitting another Request Set :After submission of the first Request Set the user will be brought to the Request summary block. To enter another Request click on ‘Submit Request’. This will bring the user back to the Submit a New Request block.

Submitting Multiple Requests

Profile Option: Concurrent: Show Requests Summary After Each Request Submission

If there is a need to submit multiple Requests quickly there is a User Profile Option that may be switched to ‘No’. This will allow the user to skip the summary block and stay on the submission block to quickly enter Requests. This option will display the Request Set ID as it is submitted and prompt the user to enter another Request or Quit.


Scheduling of a Request Set : Run time of each set can be determined under the heading of Schedule located under the Request summary block. Here the user has 5 options: As Soon As Possible, Once, Periodically, On Specific Days, or Advanced. The capabilities of R.11 are similar to 10.7. It is possible to apply a previously defined and saved schedule to the Set. A schedule must be applied every time a Request is submitted if the schedule is something other that As Soon As Possible.

As Soon As Possible : Will allow the request to begin running immediately following submission.
Once : Provides the user with a start date and time for the request to begin running which the user may change.
Periodically : Provides the user with a start date and time for the request to begin running, which the user may change. There is also a field for a possible end date, though not required. There is an option to re-run the request in an almost infinite range. The user may choose every minute, every twelve months, or greater. It is even possible to have it run every few seconds. If you do not specify a start time, Oracle Applications uses the value from your user profile option Concurrent: Request Start Time or the current time as the default.
The user may also apply the interval from the start or completion of the prior run of that request.
There is an option to increment date parameters on each run. Simply check off the box located on the bottom of the Schedule block. If your request contains date parameters, you can choose "Increment date parameters each run" to have the value for that parameter be adjusted to match the resubmission interval. For example, if the value for the parameter is 25-JUL-1997 07:00:00 and your interval is monthly, the parameter is adjusted to 25-AUG-1997 07:00:00 for the next submission.
On Specific Days : As with the Once and Periodically options, On Specific Days allows you to specify a start time and date and additionally an end time and date if so desired. Also, the user may select to increment the date parameters each run. With this option comes the possibility of choosing to run requests on pre-specified days and times. It is possible to run everyday all the time, or simply at month end. And/or the user may specify which single day of the week to run or multiple days in one week or month, using one schedule. The option to increment date parameters is also available here as in the Periodically option.
Advanced : Reserved for a future release.

Documenting Request Scheduling for Deployment : If a concurrent request or request set should be scheduled to run on a consistent basis at each deployed site, the installation document “Scheduling.doc” MUST be updated.

Request Querying and Viewing :
Profile Options - Concurrent: Show Request Stages

If set to ‘No’, this default operation will allow you to Query->Enter
Query with the Parent Request ID of the completed Set in the Parent field. This will return only the Request Set children that completed normal.

In order to query on a Parent and have it return the Request Stages, the box ‘Include Request Set Stages in Query’, located in the Find Requests block, must be checked.

If any part of your request completes with error, neither the stage nor the stages’ child report will be retrieved when running a specific query for that Request or Set. In order to view the erred request the button ‘Include Request Set Stages’ must be checked and you must perform a ‘Find All’.

Concurrent:Report Access Level

If set to “Responsibility”, you will be able to see the requests and reports of any other user who has submitted a request from the same responsibility.
Access Levels for Viewing Request Information

Viewing ALL Users’ Requests : Responsibility: System Administrator
(N) Concurrent->Requests

Will allow the user to search and retrieve requests submitted by ALL USERS. This is the ONLY responsibility/menu that will show all users’ requests. On this screen, you will be able to enter a “%” in the Request Name field and the screen will accept the wildcard character and run the query for ALL requestors.


Viewing a Specific Individual’s Requests : Responsibility: System Administrator
(N) Requests->View

NOTE: There are comparable menu paths in all applications that provide access to this same screen. It can also be accessed from the Toolbar menu (Help, View My Requests).

Will allow the user to search and retrieve requests submitted only under a specific username.

Reports Development in 11i

Before You Begin
1. Have a good understanding of how your report is to look and function.
2. Obtain a copy of both the Oracle Applications Developer’s Guide and Oracle Applications User Interface Standards. You will need both of these manuals for reference while creating your report.
3. Determine the Tables and Views to be used for your queries. If the tables being created are for the sole purpose of report generation, the standard row who columns are not required.
4. Naming Standards : All file names must be no greater than 8 chars in length plus a three character extension (FILENAME.ext). However, global localizations (country specific) must have the 2 character country code prefix and be no greater than 11 chars in length. For example, a purchasing report for UK should be UK_POCALERT.rdf
For MRC and EFC reports, first 3 character prefix should be either MRC or EFC and be no greater than 12 characters length.
The files names must be all caps except for the extension (FILENAME.ext).. This will provide a consistent naming scheme across all platforms.

Global Report Source File Names
XXXDDDDD.rdf

XXX = custom application shortname
DDDDD = Description

Localized Report Source File Names
CC_XXXDDDDD.rdf

· CC = 2 character country code

MRC or EFC Report Source File Names
MRC_XXXDDDDD.rdf or
EFC_XXXDDDDD.rdf.

Data Model

User Parameters – Report parameters must be identified by the following:
P_parameter
P_ = signifies parameter
parameter = the descriptive name you would give to the parameter

Queries – Queries must be identified by the following:
Q_query_name
Q_ = signifies query
query_name = the descriptive name you would give to the query
The main query should be noted as such with a _main

Formula Columns -
CF_formula
CF_ = signifies formula column
formula = the descriptive name you would give to the formula column

Summary Columns -
CS_summary
CS_ = signifies summary column
summary = the descriptive name you would give to the summary column

Placeholder Columns -
CP_placeholder
CP_ = signifies placeholder column
placeholder = the descriptive name you would give to the summary column


There is another way to show the name of the report in the report header. By using this method, you don’t have to worry about modifying report name in the header section. In order to see the actual report name comes out, the report has to be registered in the application.

* Create a place holder named, ‘RP_REPORT_NAME’, and a formula column named ‘Report Name’ In the formula column, use the following script:

DECLARE
l_report_name CHAR(80);
BEGIN
:RP_Company_Name := :Company_Name;
SELECT cp.user_concurrent_program_name
INTO l_report_name
FROM FND_CONCURRENT_PROGRAMS_TL cp,
FND_CONCURRENT_REQUESTS cr
WHERE cr.request_id = :P_CONC_REQUEST_ID
AND cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id;

:RP_Report_Name := l_report_name ;

RETURN(l_report_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN :RP_REPORT_NAME := ‘’;
RETURN(‘’);
END;

Layout Editor

· Header Section: The report header section should consist of the following lines


ASC - CONFIDENTIAL AND PROPRIETARY
Use pursuant to Company instructions

The report header section will contain the parameters issued by the user to run the report, this information is printed once for the report run. The parameters block should:

a. Have the parameter names printed with capitalized initial letters;
b. Have the parameter name and the user entered value separated by a colon (:);
c. Be centered within the report and left justified within the parameters block.
d. Have the separating colons line up;
e. Have characters left-justified and numeric/monetary values right-justified.

Main Section:

· Page Headers : The page header consists of two lines and will display the following information:

a. Company Name: it is anticipated that this will be country dependent;
b. Report Name: full name in block capitals; (Program short name can be included if it is necessary)
c. Date and time of report run
d. Page number.

· Column Headings: Column headings should:

a. Have their initial letters capitalized;
b. Have the same justification as the data they display;
c. Not be significantly longer than the data they contain;
d. Be underlined;
e. Be easily recognizable as the heading of the column they refer to - not always easy to do, especially with multi-column headings.

· Column Justification : Columns should:

a. Be right-justified for numeric/monetary values;
b. Be left-justified and be able to column wrap, where necessary, for character values;
c. Be in standard Oracle 9 character date report date (DD-MON-RR) for dates.

· Page Dimensions : The dimensions of pages should be:

a. Portrait Reports - 80 columns by 66 lines;
b. Landscape Reports - 132 columns by 55 lines.
c. Click on “Edit, Select All” and change the font to Courier 8

There should be a 2 line top margin and a 2-line bottom margin.

· Page Footers : If there is no data for the report, print two blank lines under the column headings, then the following line
(centered horizontally):
***No Data Found***


· Trailer Section : The report footer should consist of the following line (centered horizontally):
***End of Report***

Maintaining Copyright and Version Control :

· Add the ASC Standard report documentation block to the report level Comments Property. Here is an example:

$Header:ARGXAGR.rdf FMS 8.0.0.0.0 01-JUL-00 12:00:00

Name : ARGXAGR.rdf
Description : Aged Trial Balance - ASC - based on ARXAGR 7.0.11
Version : 8.0.0.0
Type : Reports 2.5
Application : Oracle Receivables - GADFMS
Called by : Standard Report Submission
Calls to :

History :

Date Ver Author Reason
----------- --- ------------------- -----------------------------
11-MAY-1998 1.0 Ajeet Singh Converted from Reports 2.0
10-DEC-1999 1.1 Ajeet Singh Added Master Customer
Name and Master Customer
Number. Made global for
Release 11.

· The last (5th) digit of the version number must be changed each time you check the report out of the repository. This digit can then be used to verify that the correct version of the report has been packaged and/or installed on a particular instance. When customizing a standard Oracle report, the Oracle version should be referenced in the description.

· Register the report as a new custom report. Please refer to the R11i CONC REQUESTS GUIDE for instructions on registering a report.
Customizing an Oracle Standard Report

· Make a backup copy of the Oracle report. Save a copy the original standard oracle report in the repository, named as reportname>.orig.rdf.

· Register the report as a new custom report.



Reports Documentation
· Open report
· Save the report in the database.
· Then open the report designer. In the object navigator go to tool and choose tools option.
· In the tools option dialogue, choose runtime parameter Tab.
· In the destination, set Type as “file”, name as your directory where you need to save the file and select Mode as “Character”.
· Then close this tools option dialogue box.
· Now in the object navigator go to file, administration, report doc, then choose portrait or landscape. This will open the runtime parameter form of Oracle Reports documentation Report.
· Here Name as “your report”(which are the one you need to do the documentation).
· If the destination as Screen, then change this to file.
· Change the layout information “Y” to “N”
· Parameter information “Y” to “N.
· Run the report. Now the report is saved as a file.
· Open these files in the word pad, and at the end of this document attach the output of the report, which we run through the application.
Hints and Tips

· When transferring files from the remote unix machine to the desktop or vice versa, use FTP. Transfer the file in BINARY reportat. Select the “Default” menu option and click on “Binary”. FTP will rename the file’s extension to uppercase “.RDF”. If you double-click the file, a popup screen will allow you to rename the file’s extension to lowercase “.rdf”
· When opening the Layout Model., the layout may appear with strange fonts, etc. Click on “Edit, Select All” and change the font to Courier 8. Be careful when adjusting frame sizes and page sizes. This is tedious and takes a lot of practice.
· Object and frame borders in the Layout Model can often times be hard to see. Since the location of the objects in the Layout Model is so critical it helps to use the coloring/patterning functions in Oracle Reports to distinguish one object or frame from another. To do this, choose the object by clicking on it once, then click the ‘Fill Color’ button on the toolbar and choose a color.
· Editing the text that displays on a label on the Layout Model can sometimes be confusing. To do it, choose the ‘Text’ button on the toolbar then click the object you want to edit.
· To add a user parameter to a report navigate to the ‘Object Navigator’ window, expand the ‘Data Model’, expand the ‘User Parameters’, then click the ‘Create’ button on the toolbar.