Saturday, December 8, 2007

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







Friday, December 7, 2007

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.

Wednesday, December 5, 2007

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.

Monday, December 3, 2007

Form Development in R11i

Before You Begin


  • Have a good understanding of how your form is to look and function

  • Determine the Tables and Views to be used for your Blocks LOVs.

  • If you need to create a block that will display information from more than one table (i.e., foreign key descriptions, etc.), create a view joining the necessary tables with all of the applicable fields. Be sure to include the ROWID and Who columns from the main table. Also create views as needed for LOVs that will display data from more than one table.

  • Start with TEMPLATE.fmb

Forms Naming Standards


All objects should be named in the plural format for consistency. For example: Customer Names NOT Customer Name. The C located after the object name and before the object type stands for Custom to identify the object as a custom object. For example: CUSTOMER_NAMES_CRG is the customer names custom record group.


Files:

  • File names should be 8 chars in length plus a three character extension (FILENAME.ext).

  • Form Source File Names : XXXDDDDD.fmb, XXXDDDDD.fmx, or XXXDDDDD.fmt or XXXDDDDD.txt

    XXX is the three character product short name that your form is associated with. Use one of your descriptive characters, if your product short name is 4-characters.
    DDDDD is a 5–character abbreviation for the explanation of the purpose.

    For example, the Inventory Custom form for ASC Receipts would be named
    INVCREPT.fmb or ASCXRECP.fmb.

    fmb is the suffix for the source binary file
    fmx is the suffix for the executable binary file
    fmt or txt is the suffix for the source text file

    The .fmb files reside in $AUG_TOP/forms/US and the .fmx files reside in the $ASCX_TOP/forms/US directory.

Modules



  • Your form module name should match your form file name. For example, if a form is called ASCXPOMP.fmb, make sure the Module Name (visible in the Designer) is ASCXPOMP. This is especially important if you reference objects from your form. ZOOM also relies on the Module Name being correct.

Form Global Variables: gc_prod_variable

prod is the product short name, and variable is the name you would normally give to the variable. For Example: GC_PO_SECURITY_LEVEL, GC_MFG_ORGANIZATION


Items: Use logical, meaningful, and concise names.



  • Database Column: Note that table columns based on LOOKUP_CODES should have a ”_CODE” or ”_FLAG” suffix, and the displayed meaning item should have the same name but without the suffix.

  • Mirror: Mirror Items use the name of the item plus a ”_cmir” suffix. So if the item in the detail portion is ”ename ”, name the mirror–item ”ename_cmir”.
    object_CMIR

  • Buttons: object_CBT

  • Checkbox: object_CCB

  • Option Groups AKA Radio Groups: object_COG

  • Non-Database Items: Any non-database text items should have a meaningful name followed by _CND (for custom non-database). It is only necessary to do this if you are modifying a Standard Oracle form. - object_CND

Blocks: object_CB

Object is the name of the objects in the block. The block name should be 14 characters or less.
For Example: ORDERS_CB


Special Blocks
TOOLBAR: A block containing the toolbar will be named TOOLBAR_CB.
CONTROL: A block containing control items will be named CONTROL_CB
CONTEXT: A block containing display-only context items will be named CONTEXT_CB
PROGRAM: Blocks submitting concurrent request will be named program_CB
NON-DATABASE BLOCKS: Examples: Search blocks will be named action_CB or action_object_CB

If the block is shared with other forms, make the block name unique by preceding it with the name of your form.


Canvases:
1. Non-Tab Canvas: object_CCV

2. Tab Canvas: object_TAB_CCV

Object is the name of the object shown on the canvas.

For example: ORDERS_CCV or ORDERS_TAB_CCV


Alternative Region Stacked Canvasses: block_region_CAR

Block is the name of the block that the region fields are associated with. Region describes the fields shown on the tab page. For example, a block LINES has two tab pages, one showing price information and the other showing account information.

For example: The alternative region stacked canvases are named LINES_PRICES_CAR and LINES_ACCOUNTS_CAR

Note: Alternative region stacked canvases are only needed when you can't place tab region items directly on the tab pages. You can't place tab region items directly on the tab pages when you have scrolling or fixed fields within the tabbed region.


Query–Find Canvasses, Windows, and Blocks: QF_object_CCV

To distinguish windows, blocks and canvasses used for Find Windows, prefix the object name with ”QF_”. For example: QF_ORDERS_CCV


Windows: object_CW

Object is the name of the object shown in the window. For example: ORDERS_CW


LOVs: Object_CLOV

Object is the name of the first object shown in the LOV. For example: CUSTOMER_IDS_CLOV


Record Groups: object_CRG or object_criteria_CRG

Object is the name of the objects in the record group, usually the same as the basic item or LOV name. Criteria is a brief description of why specific objects are included in the record group. Use the criteria description only if using object name alone is not unique. Abbreviate the object name or criteria description if object_criteria exceeds 30 characters.


Query LOVs and Related Record Groups: QF_object_CLOV or QF_object_CRG

To distinguish between LOVs and record groups used for entry from those used for querying purposes (such as Find Windows), prefix the object name with ”QF_”.

For example: QF_FREIGHT_CODES_CLOV,
QF_DEMAND_CLASSES_CRG


Relations: master_detail_CREL

Master is the name of the master block in the relation, and detail is the name of the detail block in the relation. For example: ORDER_LINES_CREL


Item and Event Handler Packages and Procedures: Packages are used to maintain PL/SQL executed from a trigger or another package. All packages should start with the block or form name. Code within a trigger at any level (Form, Block, Item) should not call any routine defined in the others package. The code should call the appropriate event or item handler and that handler will call the routine stored in the others package. Code within the trigger should only call procedures or functions stored in the events or items handlers (including the APPS_CUSTOM package) and should not call any oracle provided routines directly.

Each user-defined procedure that accepts an event parameter must display a debug message if the routine was passed an event that is not handled by the procedure.

Block: _Events
A separate package should be created to maintain “event” trigger logic for the block and should be suffixed with _events. This package should be used to handle all block and record level event processing. For example: The event handler package for the Orders block should be orders_events.
Listed below are some common events that would be located in a block level event handler:
Insert_Row
Update_Row
Delete_Row
Lock_Row
PRE-RECORD
PRE-INSERT
PRE-UPDATE
WHEN-VALIDATE-RECORD
POST-QUERY

If more than one package is necessary to handle the events the additional packages should be named orders_events2, orders_events3 … As needed.

Items: _Items
A separate package should be created to maintain item trigger logic and should be suffixed by items. The procedures within the item handler should be named after the item and have a single parameter, EVENT which is VARCHAR2. The logic within the procedure must branch based on the event passed to the procedure. The procedure must display a debug message if an event that is not handled by the procedure is passed in as a parameter.


For example: The item handler package for the orders block should be orders_items.
The item handler package for the lines block should be lines_items
An example of an example of an item handler procedure would be order_number.

If more than one package is necessary to handle the item trigger logic the additional packages should be named orders_items2, orders_items3 … As needed.

Others: _Others or _Others
If package procedure needs to execute another procedure or function, a separate package should be created with a suffix of _others. For procedures and functions that are used in more than one block or control form level activity, the package should be named after the form with the _others suffix. Procedures and functions that only operate on items within one block should be located in the _others package named after the block.

For example: If the orders_items procedure needs to call a function to calculate the total order selling price, a package named orders_others should be created.
If a form named oecorders needs to get profile values, a package named oecorders_others would be created with a procedure named GET_PROFILE_VALUES.

If more than one package is necessary to handle these procedures, the additional packages should be named orders_others2, orders_others3 … or oecorders_others2, oecorders_otheres3 … As needed

Form: _Events
A separate package should be created to maintain “form” level trigger logic for the form and should be suffixed with _events. This package should be used to handle all form level event processing.

For example: The event handler package for the Orders forms should be oecorders_events, where oecorders is the name of the form.


Listed below are some common events that would be located in a form level event handler:
WHEN-NEW-FORM-INSTANCE
PRE-FORM
POST_FORM
WHEN-NEW-BLOCK-INSTANCE
KEY-CLRFRM

If more than one package is necessary to handle the events the additional packages should be named oecorders_events2, oecorders_events3 … As needed.

These additional packages may be needed for two reasons:


1) size of the package should be limited to less than 64K and


2) fewer than 25 procedures.

Procedure and functions names within a package should be meaningful.


Triggers: All triggers not part of the template form or copied from a block in the template form will call either an item handler or event handler package. Standards related to specific triggers are listed below.



  • PRE-FORM :
    · Author: . . i.e. A. Singh
    · Revision: i.e. ERP 010

Form Documentation and Comments
Module Comments: For every modification to a form a comment MUST be added to the MODULE level comments.


ALL FORMS:
All forms should have the following Module Comment:

Copyright ASC Corp. 2007

Form Name:
Original Oracle Form Name: (if applicable)
Initial Release:
Developer:
Purpose:
MODIFICATION BLOCK:

For example:
Form Name: ASCXDSCP
Original Oracle Form Name: N/A
Initial Release: ERP 1.0
Developer: Ajeet Singh
Purpose: Provide the ability to copy delivery sets
MODIFICATION BLOCK:


Modifications to a Form:
For each modification of a form, add the following comment at the beginning of the Modification Block section:
Release - Date of Modification - Developer Name - TAR#


For example:
ERP 1.0 - 8/12/07 - Ajeet Singh - STAR TAR #59632
Add WHEN-VALIDATE-ITEM trigger to sales channel code.



Other Comments
Other comments added to a form object should include the following header:

Release - Date of Modification - Developer Name -STAR TAR#


For example:
ERP 1.0 - 8/12/97 - Ajeet Singh- STAR TAR #59632
Add WHEN-VALIDATE-ITEM trigger to sales channel code.

Containers : Use File/Save As to create your new form (be sure to change the directory as needed. Set the module name to be the same as the file name.
* Create the windows needed for your form. There should be one Window per “page” or screen full of data.
* Set the Property Class to WINDOW
* Change the Window title.
* Set the Window height property to 5 (inches)
* Set the Window width property to 7.8 (inches)
- These are the MAXIMUM sizes for a window, these values may need to be reduced once you have the forms objects on the canvas and then the canvas and the window can be resized to fit the actual size needed.
* Create a canvas for each Window created in the previous step.
* Set the Property Class to ‘CANVAS’
* Set the Window property to the corresponding window created in the previous step.

For each Canvas created in the previous step, go into the Layout Editor and change the following settings:

* Format/Layout Options/Ruler – change the units to Character Cells, Character Cell Size to 7.2 (horizontal) and 18 (vertical), Grid Spacing to 1 and Number of Snap Points to 2 (p.3-3).

Note: Ruler settings determine the spacing and alignment of all elements in the form. It is crucial that these settings are established correctly before any layout is performed and that the "Grid Snap" setting is enabled. These settings allow objects to be drawn snapped to a character cell grid, with well defined ”rows” and ”columns.” Use of this character cell grid facilitates rapid building of, and often more aesthetically pleasing, screens than could otherwise be achieved if no grid were imposed.
* View/Snap to Grid – to turn on grid snap (checked) (p.3-3).
* View/Show Canvas – to turn off canvas viewing (unchecked)
* View/Show View – to turn on view display (checked).

Create the necessary Blocks following the Forms Naming Standards.
* Be sure to change the Canvas to the appropriate canvas created as above

* If the block will display more than one row, Check the Scroll Bar option.
* As the LAST step before pressing the OK button, Change the Block Name under the General tab in the New Block Options

For each Block created in the previous step,
* Set the block property class to ‘BLOCK’.
* If block is based on a table or a single-table view ,Set the Key-Mode property to ‘Unique’;
* For blocks based on more than one table, set the Key-Mode property to ‘Non-Updatable’.
* For blocks based on an Index-Organized Table (IOT)…
* Set the Enforce Primary Key property to 'Yes'. This property indicates that any record inserted or updated in the block must have a unique key in order to avoid committing duplicate rows to the block's base table.
* Set the Key-Mode property to 'Non-Updatable'. Non-Updatable specifies that Oracle forms should not include primary key columns in any UPDATE statements.
* Once the block level properties are set, you must set the property of at least one item on the block to indicate it is the primary key. For example, for the field in your block that is to contain only unique values, set the Primary Key property to 'Yes'.

NOTE: For blocks that are based wholly or in part, on an Index-Organize Table, certain block properties must be set as noted above. This is necessary because Forms was not designed to work on Index-Organized tables (IOT). This limitation is primarily due to Form's dependence on RowIDs. RowIDs are used by Forms to identify the row(s) accessed by the form. IOTs do not have RowIDs. You can run the following SQL query, to determine if a table is an IOT… SELECT table_name, iot_type FROM all_tables where table_name = ; The table is an IOT, if 'IOT' is returned for the iot_type field.

MetaLink Note# 74887.1 "Forms with Blocks Based on Index-Organized Tables".

* Change other block properties such as Insert Allowed, Update Allowed, Delete Allowed, etc., as needed.

. For each block, set the Navigation Style block property to
* ‘Change Record’ for multi-record blocks.
* ‘Same Record’ for single-record blocks with no detail blocks within the same window
* ‘Change Block’ for all other single-record blocks

. In addition, set the Next Block and Previous Block properties. The First Block in a form must have Previous Block set to itself and the Last Block must have Next Block set to itself. Under the Form Module Properties,
* Set the First Navigation Block to the name of the first block to be displayed on the form.

.Create Master-Detail relationships as needed. Before creating the relationships, ensure that your blocks are named exactly the way that you want them. If you have to change a Block name after relationships have been created, you will have to modify
* The join property
* ON‑CHECK-DELETE-MASTER and ON-POPULATE-DETAILS triggers
* And possibly others.

.Modify the form level PRE-FORM trigger.
* Change the parameters in the call to FND_STANDARD.FORM_INFO:
* Version = ERP 1.0.0.0.0 (there should be no $Version in the string)
* Title = The name of the form that appears in the title bar
* Application Short Name = The application short name under which your form is to be registered. For example, ASCX (ASC Custom – General).
* Date Last Modified = The date that you modified/created the form
* Last Modified By = ‘Copyright ASC Corp. 2007’ or the appropriate year(s) that the form was created/modified (there should be no $Author in the string)

Example:
FND_STANDARD.FORM_INFO('ERP 1.0.0.0.0', 'Cancel Delivery Sets', 'ASCRX',
'$Date: 01/11/24 13:29:28 $', 'Copyright ASC Corp. 2007');

* Change the 'BLOCKNAME' string in the call to APP_WINDOW.SET_WINDOW_POSITION. Change it to call your main (first) WINDOW name. For example, app_window.set_window_position('ARC_LOOKUP_VALUES_CW', 'FIRST_WINDOW');

.Modify the FORM First Navigation Block property to be your first block.
.Save your form and do a Program/Compile/All. If there are no errors, follow the steps for Form Registration and Testing. At this point you will only be able to query information on your form.

Items
1. Set the properties for all other items including:
* Property class
* Canvas,
* Width
* Query length (for text items).
* Listed below are property classes for the various widget types and behaviors:

Type of Widget -- Property Class

Check Box - CHECKBOX
Pop List (static list of <15>

Text Item -- TEXT_ITEM, TEXT_ITEM_DISPLAY_ONLY, TEXT_ITEM_MULTILINE, TEXT_ITEM_DATE

Display Item (never accepts focus) -- DISPLAY_ITEM, DYNAMIC_TITLE, DYNAMIC_PROMPT

Option Groups (Radio Buttons) -- RADIO_BUTTON , Button BUTTON, BUTTON_ICONIC

Who Date Fields (CREATION_DATE, LAST_UPDATE_DATE) CREATION_OR_LAST_UPDATE

Other Who Fields (CREATED_BY, LAST_UPDATED_BY, etc.)

WHO_NONDATE_ITEM: This is a custom property class that makes the field a DISPLAY_ITEM and sets the canvas set to ‘’.

1. Set the Primary Key property to True for the primary key field(s) in each block.
2. As needed for each block, copy the descriptive flexfield item from the BLOCKNAME or DETAILBLOCK block depending on whether your block is a single record block or a multi-record block, respectively.

3. For multi-record blocks copy the CURRENT_RECORD_INDICATOR from the DETAILBLOCK.
* Change the canvas name item property.
* Modify the WHEN-NEW-ITEM-INSTANCE trigger to prompt to the first field in the block.

Note: For combination blocks, create a drill-down indicator in place of the current record indicator.

4. For all multi-record blocks, set the scroll bar properties for the block:
* The left edge of scrollbar should be 0.3" inward from the right edge of the canvas
* Scroll Bar Orientation=Vertical
* Scroll Bar Width = 0.2"

5. Arrange the fields in the navigator to correspond to the tabbing sequence (left to right , top to bottom within a region). Displayed fields should be listed first and those on the null canvas listed last.
6. Go into the Layout Editor to position and size fields and scroll bar(s).
* Use Arrange/Align Objects to snap the fields and scroll bars to the grid.
* Align to=Grid,
* Horizontally=Align Left,
* Vertically=Align Center

7. Create field prompts, region boxes/lines and region titles as needed.
* Set the font to MS Sans Serif 10pt (Regular font style for prompts and Bold for region titles).
* Set the settings for the prompts in the "Prompt" section of the field's Property Palette.
* Arrange/Align Object to Snap to Grid.

See chart below for the appropriate settings:

-Prompt Display Style -Justification -Attachment Edge -Alignment -Attachment Offset -Alignment Offset -Snap to Grid___________________________________________


Single Record Block Prompt -First Record -End -Start -Center -0.1" -0.0" -Right, Center
Multi-Record Block Prompt Left Aligned Data * - First Record -Start -Top -Start -0.0" 0.05" -Left, Bottom
Multi-Record Block Prompt Right Aligned Data * -First Record -End -Top -End -0.0" -0.05" -Right, Bottom
Multi-Record Block Prompt – Center Aligned Data -First Record -Center -Top -Center -0.0" -0.0" -Center, Bottom
All Region Titles -N/A -N/A -N/A -N/A -N/A -N/A -Left, Center
All Flexfield Brackets -N/A -N/A -N/A -N/A -N/A -N/A -Center, Center
* Prompts for Multi-Record Blocks are to be placed one-half character cell (0.05”) in from the edge of the associated field.
8. Re-size each canvas to fit around all displayed items. Re-size the canvas’s window to be the same size as the canvas.
9.Save, Compile All and Test the form to ensure that items are displayed properly and have the correct properties. You still will only be able to view and query information on your form.

Logic and Functionality
1. If your form uses profile values, do the following:

* Create an Item in the CONTROL block (you may need to create this block if one does not already exist) to hold the value of the profile that you need.
* Create a procedure named GET_PROFILE_VALUES in the formname_OTHERS package under Program Units. This procedure should use FND_PROFILE.VALUE or FND_PROFILE.GET to assign the profile value to the item that you created in the CONTROL block.

* In the WHEN-NEW-FORM-INSTANCE trigger call GET_PROFILE_VALUES before the standard code in that trigger.
* In the KEY-CLRFRM trigger call GET_PROFILE_VALUES after the standard code in the trigger.

2. Create LOVs and corresponding record groups as needed for field QuickPicks and validation. The record group should order the data by the first column displayed. Assign the LOVs to the appropriate fields and set the LOV position.

3. For every user enterable Date field
* Set the LOV property to ENABLE_LIST_LAMP and
* Set the Use LOV for Validation property to FALSE
* Create a KEY-LISTVAL trigger for each date with an execution style of Override. The trigger should call calendar.show.

4. Create a package(s) as needed For each block you may need separate packages to contain table and event handlers or item handlers or other business rules. * Event Package: The package should be named blockname_EVENTS where blockname is the name of your block without the _CB. For example, you would name this package ORDERS_EVENTS if your block name is ORDERS_CB.
* Item Package: You may also need to create packages for item handlers (blockname_ITEMS).
* Form level packages: (formname_EVENTS) for form level events.
* Business Rules Package: A package to hold procedures for calculations, common functions and other miscellaneous procedures (blockname_OTHERS or formname_OTHERS).

5. For blocks based on a view joining more than one table, you will need to create Insert_Row, Update_Row, Delete_Row, and Lock_Row procedures within the blockname_EVENTS Package created in the previous step.
* Copy view_table_handlers.txt to another file name.
* Modify the procedures to use your form, block and table names using a text editor. Be sure to save the file as plain text because Word and Wordpad quotation will not be recognized correctly.
* In the blockname_EVENTS package body, use Edit, Import, Text to import your saved file containing the handlers.

6. For all blocks allowing insert and/or update, create Pre-Insert and Pre-Update procedures in the blockname_EVENTS package created in Step 3. Both of these procedures should call FND_STANDARD.SET_WHO.

7. Create additional table and event handlers (procedures) as needed. These procedures will reside in the blockname_EVENTS Package created in Step 3.

8. Add logic to the appropriate triggers to call the procedures created in Steps 3-5. Be sure to set the trigger’s execution style correctly. In general it should be set to Before and not Override, the default.

9. Modify the APP_CUSTOM.OPEN_WINDOW package body to reference the form’s window names. Set coordination and window position as needed.

10.Save, Compile All, and Test your form. You should now be able to Insert Update and Delete rows from your form and use your LOVs.

11.Modify the APP_CUSTOM.CLOSE_WINDOW package body. Enter the name of the first window (uppercase) and modify other logic as needed.

12.If your form contains master-detail blocks located in different windows, create a button to open the detail window and a coordination check box along with the corresponding item handler.

13.Create item handlers as needed to handle field validation, initialization and messaging. These procedures should be created in the blockname_ITEMS Package. Call the procedures from the appropriate triggers being careful to set the execution style correctly.

14.Create the appropriate handlers to define and invoke Flexfields in the blockname_ITEMS Package. Call those procedures from the appropriate triggers being careful to set the execution style correctly.

15.Add Query Find functionality:
* Row-LOV - used for a single-row block to show all possible records that a user can query. * Create query find parameters for all primary key fields.
* Create a row LOV for each block.
* Create block level PRE-QUERY and QUERY_FIND triggers.
* Find Window -used for combination blocks and other blocks to allow the user to enter query criteria to locate one or more rows.

16.Delete the unused objects from the Template, BLOCKNAME and DETAILBLOCK blocks, BLOCKNAME canvas and window.

17.Add comments on the module properties sheet that includes the module Name, Original Oracle Form Name, Initial Release, Developer, Purpose and Modification Block. Below is an example:
Copyright ASC Corp. 2007

Form Name: ERP Generic
Original Oracle Form Name: n/a
Initial Release: ERP 1.0
Developer: Ajeet Singh

Purpose: Generic form
MODIFICATION BLOCK:
6/14/01 A. Singh STAR 999999 – Modified Generic_ID field to be VARCHAR2(34) from NUMBER(15).

18.Save, Compile All, and Test your form to ensure all functionality is working as expected.
Form Registration and Testing

1. FTP the form (.fmb) to the AUG_TOP/forms/US directory on the Forms Server. You will need to connect as devmgr to ensure that the files are owned by the correct user.
2. Log on to the Forms server as devmgr and change directories to AUG_TOP/forms/US. Generate the form into the appropriate application forms directory using f60gen or the gen_form shell script.

f60gen OECMASCH apps/xyz output_file=$ASCX_TOP/forms/US/OECMASCH.fmx
OR
gen_form OECMASCH $ASCX_TOP/forms/US/OECMASCH.fmx

where OECMASCH is the name of the form, APPS is the username for the APPS schema, XYZ is the APPS schema password, and ASCX_TOP is the application top directory.
3. If you have not already done so, log into the applications and register the form:
* Using the Application Developer responsibility, navigate to Application à Form. Be sure to remember the user form name because you will need it when you create a form function
* Using the Application Developer responsibility, navigate to Application à Function. From this form create a function that accesses the user form name you created in the previous step. You will need to enter the form name under the FORM tab. Remember the function name because it will be used when you add the function to a menu.
* Navigate to Application à Menu to add the function you created in the previous step to the ERP_DEVELOPMENT_TEST_MENU menu OR to whatever menu you desire.

4. To test the form, switch responsibilities to ERP Development Test Resp. (or to the responsibility associated with the menu that you added your function to, in step 3 above) and select the appropriate option on the menu.

5. When you have completed your form, you will need to add your form function to the appropriate menu. This should be specified in the functional spec/design document.

6. Document your AOL registration.

7. Once you are ready to check your form into the repository, be sure to edit the repository's generate_all_forms script, to add the appropriate statement for your form. This file is located in the $AUGX_REPOS/forms/US directory of the repository.

*** When you generate your form on the box (using either f60gen… or gen_form…) and you get an error that says something like ‘Cannot open file. Segmentation error (coredump)’. Look at the reference information on the items that you are referencing in from the dummy form and make sure the dummy form is displayed in all CAPS. Otherwise, it does not recognize it.

Tabbed Regions
If you have too much information about an object to fit within one standard-sized canvas, you may need to create a tabbed region. A tabbed region is the area of a window that contains a group of related tabs. The group of related tabs and their corresponding tab pages are considered to make up the tabbed region. This is called a tab canvas. Each tab canvas consists of one or more tab pages. The tab control is provided by a widget that positions the tab UI mechanism at the top of a set of regions and allows the user to navigate directly to a specific region by selecting one of those tabs.
From a coding perspective, there are three degrees of coding difficulty for tabbed regions.
*The three degrees of difficulty require different types of layout methods and coding methods. *The layout method differences include using stacked canvases or not, and how many of them. *The coding method differences include extra code that is required for handling the behavior of tabs with stacked canvases.

An overview of the three degrees of difficulty is as follows:

· Simple: No scrolling or fixed fields – This case includes single-row tab pages where no fields are repeated on different pages. These are typically separate blocks of the form. If you have a form with multiple separate multi-row blocks represented as one tabbed region (one block per tab page and separate block scrollbars for each, but no horizontal scrolling of fields), that can also be coded as the simple case. For example, the Users window on the System Administration responsibility fits the simple case. In the simple case, you place items directly onto the tab pages. The simple case does not require any stacked canvases.

· Medium: Scrolling but no fixed fields – This case includes single-row tab pages where no fields are repeated on different pages, but scrollbars are required to allow access to all fields within a tab page. These tab pages are typically each separate blocks of the form. In the medium case, you place items onto stacked canvases, in front of the tab pages, to facilitate scrolling of fields.

· Difficult case: Fixed fields with or without scrolling – This case covers the presence of fixed fields shared across different tab pages. This case includes any multi-row blocks spread across multiple tab pages. Fixed fields usually include context fields, current or drilldown record indicator, descriptive flexfields, and the block scrollbar. In the fixed field case, you place items onto stacked canvases, in front of the tab pages, to facilitate scrolling of fields. An extra stacked canvas is required for the fixed fields, and additional code is required in the tab handler.

Oracle provides two template files for coding your tab handler. The two template files are as follows:
FNDTABS.txt for the simple and medium cases
FNDTABFF.txt for the fixed field case

A high-level overview for creating tabbed regions, is as follows:
1. Make a list of the tabs (field groupings) needed and give each one a name. These names will be used to name the canvases used to display the information. It is important to determine these names before you start coding because changing them later in the process can be difficult.
2. Follow the steps for the Implementing Tabbed Regions coding
3. If any of the tabs other than the first displayed tab is a detail block, you will need to add additional code to control the master-detail coordination.
* Create the appropriate relations with the following properties:
* Masterless Deletes: NonIsolated
* Prevent Masterless Operations: True
* Deferred: True
* Auto Query: True
* Create a procedure to toggle the Deferred property for the relation.

Example:

PROCEDURE toggle_ds_coordination(disp_canvas in VARCHAR2) IS
-- This procedure is used to provide block query coordination
-- for detail blocks located in a tabbed region.
BEGIN
IF (disp_canvas = '') THEN set_relation_property('',DEFERRED_COORDINATION,PROPERTY_FALSE);
ELSE set_relation_property('',DEFERRED_COORDINATION,PROPERTY_TRUE);
END IF;
-- Create the IF-THEN-ELSE statements like those above for each dependent region.
END toggle_ds_coordination;

* Code your Tab Handler, you'd call the toggle coordination procedure created above. For the simple and medium cases (FNDTABS.txt) WHEN-TAB-PAGE-CHANGED Branch, call the toggle coordination procedure

* After the 'end if' (in the event = 'WHEN-TAB-PAGE-CHANGED' section) and before the 'Move to first item on each tab' comment.
For the difficult case (FNDTABFF.txt) WHEN-TAB-PAGE-CHANGED Branch, call
the toggle coordination procedure

* After the "end if" (in the event = 'WHEN-TAB-PAGE-CHANGED' section) and before the "ELSIF (event = 'WHEN-NEW-ITEM-INSTANCE')…" statement.

* After the "end if" (in the event = 'WHEN-NEW-ITEM-INSTANCE' section) and before the "ELSE app_exception.invalid_argument…" statement.
* If the cursor is not within the tabbed region when the tab is changed, you must force the query to occur using the following statements:
rel_id := Find_Relation('');
Query_Master_Details(rel_id, '');

Creating messages in Message Dictionary
1. Log on as Application Developer, and go to the Application/Messages form.

* Type in the name of the error

* Language (US)
* Application (name of the application this error will be used for)
* Select Error in the Type field for and error message
* Fill in Description
* Fill in the Current Message Text with the statement you want to appear when the error occurs. (use & before a name of a field to create variables to pass in )

2. Save
3. Stay as Application Developer responsibility and go to the Other/Requests/Run form and submit a single request. **
* The request name is Generate Messages
* Fill in the parameters language (US)
* Application
* Leave the mode as DB_TO_RUNTIME
* Leave the other fields blank.

4. Submit the request and it should complete successfully.

5. You need to restart the Apps before your messages will appear.

** To generate messages from Unix command line, supply the appropriate application_short_name to the following command:
FNDMDGEN apps/apps 0 Y US application_short_name
So, for example, to generate the messages for the ASC Custom – General (ASCX) Application: FNDMDGEN apps/apps 0 Y US ASCX


* When we upgrade environments, the message file may get copied so your message will work even if you had not put it in the message dictionary in the new environment yet. It will work until the Generate Messages runs for that application. Just remember that you have to put your message in the message dictionary for each environment.


* For a two-tiered environment, the generated message file has to exist on BOTH the Application Tier and Database Tier servers.

What this means…
* After you've submitted the Generate Messages job as noted above, a message file (i.e. US.msb) is created in the appropriate mesg directory (for example, the directory for the "ASC Custom – General" application = $ASCX_TOP/mesg) on the Database Tier box.
* You will have to copy (i.e. using ftp) this file to the appropriate mesg directory on the Application Tier box.
* You will then have to generate the message file from the Unix command line. For example, to generate the messages for the ASC Custom – General (ASCX) Application: FNDMDGEN apps/apps 0 Y US ASCX

Customizing A Standard Oracle Form
Before modifying a standard Oracle form, review the following options for a solution

1. Descriptive Flexfields (DFF)
* Standard attributes (1-5 for local) or 6-?? For global
* If a field is available on the form, but not visible; make a DFF. This field must be available in all blocks/forms where the flexfield is used.
2. Standard Oracle Functionality (profile options, system options, standard value rules)
3. Custom Library and/or Form Personalization
4. Form Function, when form will be accessible through a specific responsibility for the country
5. Database Trigger
6. Call custom library package that performs logic, versus putting code directly into form.

* Create an Oracle application Upgrade/Patch instruction document that identifies the steps necessary to re-implement the changes.
* Library should be maintained in AUG_TOP/resource with a filename of custom_ .
7. If none of the previous options work, customize the Oracle form follow the steps in the Oracle Applications Developer’s Guide, with the following additions/exceptions
* Make a backup copy of the Oracle form. Save a copy the original standard oracle form in the repository in the $ORIG_REPOS directory, named as formname>.orig.fmb
* Do not rename the form or Module if you are just making minor modifications to the form’s functionality. The decision was made to retain the original Oracle form’s .fmb filename and module name to limit analysis and further customizations. For example: Oracle’s standard code in some forms, hard codes form names as part of functionality logic. Also, one Oracle form may reference another and this referencing expects the standard Oracle form name, not a custom name. A custom name will be given to the .fmx file only.

You should only rename the form and module if you are simply using the Oracle form as a starting point for a custom form. (For example, the Cancel Delivery Sets form is based on the standard Oracle Cancel Orders form. It can reuse several pieces of the Oracle code, but has major functionality enhancements or differences. Also, we would never expect or want another Oracle form to unexpectedly access this custom form.)

* Modify the FND_STANDARD.FORM_INFO statement in the PRE-FORM trigger:
* Version = Oracle form version - ERP 1.0.0.0.0 (there should be no $Version in the string). For example, 11.5.3 – ERP 1.0.0.0.0
* Application Short Name = The application short name under which your form is to be registered. For example, ASCX, INFX, MIGX, TRNX, etc.
* Date Last Modified = The date the you modified the form
* Last Modified By = ‘Copyright ASC Corp. 2007’ or the appropriate year(s) that the form was created/modified (there should be no $Author in the string)
The following is an example of what your FORM_INFO call should look like:
FND_STANDARD.FORM_INFO('11.5.3 – ERP 1.0.0.0.0', 'Cancel Orders', 'ASCX', '$Date: 01/11/24 13:29:28 $', 'Copyright ASC Corp. 2007');
* Add the ASC Standard form documentation block to the form level Comments Property.


MODIFICATION BLOCK:
* When you generate the form give the executable a custom name. For example, gen_form OEXOEMOE.fmb $ASCX_TOP/forms/US/OECOEMOE.fmx
* When you register the form the User Form Name, User Function Name, and Navigator Prompt should all be named: Oracle's User Form Name (where is ERP. For example, ERP Enter Orders.
* Create another form with fields/logic and copy/reference the form into the standard Oracle form. This form should be maintained in AUG_TOP/forms/US with a filename of custom__ and does not need to be generated. The purpose of this form is to make reapplying customizations easier after a patch or upgrade.

* Create an Oracle application upgrade/patch instruction document that identifies the steps necessary to re-implement the changes.
8. As a last resort, modify standard oracle database object(s). If you need to modify a standard Oracle view, the vw-info.sql script can be used to compare views from one instance to another. This is useful when checking to see if a patch changes the definition of a view. The script is located in the repository in $ORIG_REPOS/asc_utils.

Custom Library
Oracle provides a library that gives you the ability to alter the functionality of a standard Oracle form without changing the form. The library is named CUSTOM.pll and is attached to all forms. All Oracle forms (are supposed to) call the EVENT procedure in this library from the following triggers:
* WHEN-FORM-NAVIGATE
* WHEN-NEW-FORM-INSTANCE
* WHEN-NEW-BLOCK-INSTANCE
* WHEN-NEW-RECORD-INSTANCE
* WHEN-NEW-ITEM-INSTANCE
* WHEN-VALIDATE-RECORD
Some forms call this procedure from additional triggers (check for calls to CUSTOM.EVENT within the form code). The Custom Library is also called for Special Menu options, Zoom and Export.


CUSTOM Library has custom forms libraries attached to it. There is one custom forms library per form which is called at run time based on the name of the current form. These custom forms libraries are named CUSTOM_XXXXXXXX where XXXXXXXX is the Oracle form name whose functionality is altered by the code in the library. Only one developer can update the CUSTOM library at one time. To ensure that developers do not run into contention with one another, the CUSTOM library (the one that we customize, not the default one supplied by Oracle) cannot be placed on a shared drive where other developers are modifying forms.

The libraries are located in the $AUG_TOP/resource directory to mimic how Oracle maintains its libraries which are in $AU_TOP/resource. The forms server has its FORMS60_PATH set to look in our library directory and then Oracle’s. This allows us to have a CUSTOM library while leaving the Oracle provided version intact and insulates us from patches that may overwrite the CUSTOM library. Any time that we get an Oracle patch that alters the CUSTOM library, we should verify that we do not need to change our CUSTOM library.

CUSTOM Library Setup
As mentioned previously, it is very important that modifications to the custom library be performed with care to prevent disruption to other developers. The following are requirements for maintaining the Custom Library:

1. Create a separate directory for custom library development

2. Prevent other developers from locking the custom forms libraries.

3. In order to modify the CUSTOM library, you must FTP the CUSTOM library along with all of the CUSTOM_XXXXXXXX.pll files to the directory that you set up in Step 1. An alternative to this is to use an X-windows emulator (like exceed) and modify the library directly on the SUN box. Be sure to make a back-up copy of the library before making modifications and DO NOT generate the library from the forms tool. Use f60desm to get into the forms tool from an X-windows session.

4. When you generate libraries, all CUSTOM_XXXXXXXX.pll files must be generated before CUSTOM.pll is generated. Therefore, when you add a new CUSTOM_XXXXXXXX.pll file, you must generate that file before regenerating the updated CUSTOM.pll.

**Note: You may get write protection/sharing violation errors when you try to save a library when it is attached to an open form. That is why it is important your FORMS60_PATH is set correctly.

Creating a Custom Forms Library
If your form requires modification that can be accomplished through the Custom Library, you will need to begin by completing the following steps:

1. Create a CUSTOM_XXXXXXXX.pll file containing a Package by the same name. The package must include a procedure called EVENT. This is the procedure that you will use to handle any of the events passed to the Custom library. Place your library in the $AUG_TOP/resource directory. If one custom form library has errors, it will cause everyone to get errors from the Custom library or possibly APPCORE.
2. Add the copyright and version information for the library.
3. Add additional logic to the library and attach additional libraries, as needed. Remember that you cannot call any APPCORE routines or use SQL statements in the Custom Library.

4. FTP your library to the $AUG_TOP/resource directory and use the gen_lib shell script to generate the library. (For example, to generate the CUSTOM_OEXOEMOE library you would type gen_lib CUSTOM_OEXOEMOE.) This will create your executable .plx file. If you are logged into the apps when you generate your library, you must log out and log back in again to see your changes.
5. If you need to turn off your custom code while testing your form, use the Help/Diagnostics/Custom Code/Off menu option to prevent the Custom Library from being called. The Custom library can be turned of at any point and turned back on again.
6. Once you are ready to check your form into the repository, be sure to edit the repository's generate_all_libraries script, to add the appropriate generate statement for your library. This file is located in the $AUGX_REPOS/resource directory of the repository.

Special Menu
To modify or add options to the special menu through the custom library, you need to use standard Oracle Forms 6i functions. Below are some of the functions
1. set_menu_item_property('SPECIAL.SPECIAL14', ENABLED, PROPERTY_FALSE);
2. set_menu_item_property('SPECIAL.SPECIAL14', DISPLAYED, PROPERTY_FALSE);
3. set_menu_item_property('SPECIAL.SPECIAL14', LABEL, 'Revenue Splits');
4. set_menu_item_property('SPECIAL.SPECIAL14', DISPLAYED, PROPERTY_TRUE);

For examples, review the library for the Enter orders form custom_oexoemoe.pll.

Form Personalization
With 11.5.10 Oracle introduced new functionality for altering the behavior of forms without having to use the forms designer. Form Personalization is similar to the Custom library, using the same events and responds identically to the Custom Code ‘Normal’, ‘Off’ and ‘Core Code’ settings. In general, Oracle recommends using the Form Personalization feature rather than the Custom library whenever possible. .

To use the Form Personalization feature, open the form (or function) that you want to alter. Then select Help -> Diagnostics -> Custom Code -> Personalize from the menu. A form will appear for entering/updating the personalizations related to the form/function. Each personalization has its own sequence number and description. The Seq indicates the order in which the personalizations are to be evaluated and is not required to be unique. The Description should include an overview of what the personalization is for as well as the requirement number. The Level can be Form or Function. It is recommended that the level is set to Form unless the conditions are only being applied to one Function (for example, the Query Only function for the form). This way the form will react the same way regardless of the function used to open it.

On the Condition tab is used to indicate the condition under which the Actions are to take place. For the Context Level it is not recommended that Responsibility be used unless the personalization is for one specific operating unit/responsibility. Otherwise, the personalization would have to be altered each time a new Operating Unit is added. (For example, Customer Implementation Specialist would be a bad Responsibility Level Context because this responsibility exists for each Operating Unit.)

On the Actions tab (shown below), are the actions that are to take place when the Condition (defined on the Condition tab) is met. The Seq is a number that indicates the order in which the actions are to be applied and does not have to be unique. The Type can be Property, Builtin, Message or Menu. The Description should describe the action that is to be performed as well as indicate the requirement number. The right hand side of the screen will change depending on the Type and the selections that are chosen.


When the Apply Now button is presses, the Condition will be evaluated immediately and the Actions will take place accordingly. In the example shown above, pressing the Apply Now button will set the Displayed property on COPY.VERSION_NUMBER to False. Once changes are save on the Form Personalizations screen, the personalizations can be activated by closing and re-opening the form being personalized.

Using a View as the Base Table
It is highly recommended that blocks which contain information from more than one table be based on a view. Whenever possible, you should use a view instead of selects in POST-QUERY triggers. The main reason you should use a view is to reduce network traffic - POST-QUERY logic will not be needed to go back out to the database to lookup field values. Views are also nice because they allow the user to query on fields that may otherwise be display only. The form developer does however have to do a little more work in the form to handle locking, updating, inserting and deleting the appropriate table(s). In general, the extra time spent by the developer will be worth the reduced network traffic and subsequent performance gains. Any time that you create a view to be used as the base table for a block, you must include the ROWID and who columns from main table in the view. These fields will be needed to display who information. In addition, the ROWID is needed for locking, updating and deleting the row.

Creating Functions for Columns in a View
When you are creating a view with a single value from another table, it may be more efficient to create a function to bring back the value and use that function as part of the SELECT for the view definition. For example, if you just need to display the Meaning of a Lookup Code from SOC_LOOKUPS based on a specific Lookup Type and a Code value in your base table, you could create a function similar to the following:

function SOC_LOOKUP_VALUE ( LOOKUP_TYPE_STR in varchar2,
LOOKUP_CODE_STR in varchar2 )
RETURN VARCHAR2
IS
lookup_value VARCHAR2(80) := NULL;
cursor LOOKUP_CURSOR is select MEANING
from SOC_LOOKUPS
where LOOKUP_TYPE = LOOKUP_TYPE_STR
and LOOKUP_CODE = LOOKUP_CODE_STR;
BEGIN
if ( LOOKUP_CODE_STR is not NULL ) then
OPEN LOOKUP_CURSOR;
FETCH LOOKUP_CURSOR INTO LOOKUP_VALUE;
if (LOOKUP_CURSOR%FOUND) then
CLOSE LOOKUP_CURSOR;
return (LOOKUP_VALUE);
else
CLOSE LOOKUP_CURSOR;
return (NULL);
end if;
else
return (NULL);
end if;
END;

Please note that the above function already exists in the package OEC_QUERY. You should not re-create this function – please use the one in OEC_QUERY. Any time that you need a function, you should first check to see if one already exists. In addition, you should always write functions so that they can be reused. For example, in the function above, the LOOKUP_TYPE is passed in as a value rather than being hard-coded. This promotes reusability of the function.

When you are creating functions to be used in views, you must add the following line in the package specification after defining the function:
pragma RESTRICT_REFERENCES (SOC_LOOKUP_VALUE, WNDS, WNPS);
This statement tells the Oracle parser that the function does not change any database values. This statement is important because you cannot use a function in a select statement that alters data within the database (i.e., it cannot update, insert or delete records or alter database objects). If you are calling other procedures or functions in your function, you must remove the WNPS portion of the statement above or you will get an error when you compile your package.

The OEC_QUERY procedure has many other functions that may be useful when defining views. This package is located in the ERP oec_pl.sql script. If you need to create additional functions, these should be created in the appropriate XXX_QUERY package where XXX is the application short name. These packages should be included in the appropriate application SQL script.

Creating a view using functions
Once you have created functions, you can then use those functions to use in the select portion of your view. Below is an example using the function SOC_LOOKUP_VALUE as part of the view for the Apollo Import form. This view was needed because the form must allow the user to enter the Meaning for an Order Action as opposed to a Code and a Pop List could not be used in this case. Also, the Meaning for a Status Code is displayed rather than the code.

create view INF_APOLLO_20_REQUESTS_V
(ROW_ID,
REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
ORDER_ACTION_CODE,
ORDER_ACTION_MEANING,
RQST_ORDER_NUMBER,
RQST_STATUS_CODE,
RQST_STATUS_MEANING,
ORIGINAL_SYSTEM_REF,
SYSTEM_ERROR_DESC)
as select
ROWID,
REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
ORDER_ACTION_CODE,
OEC_QUERY.SOC_LOOKUP_VALUE ('INF_APLO_ORDER_ACTIONS',ORDER_ACTION_CODE),
RQST_ORDER_NUMBER,
RQST_STATUS_CODE,
OEC_QUERY.SOC_LOOKUP_VALUE ('INF_APLO_STATUS',RQST_STATUS_CODE),
ORIGINAL_SYSTEM_REF,
SYSTEM_ERROR_DESC
FROM INF_APOLLO_20_REQUESTS

Please note that the script to create the functions must run before the script that creates views using those functions. For this example, the view is located in inf_vw2.sql. The vw2 indicates that the view script should be run AFTER the function scripts.

When you create a view, you should always ensure that the SELECT portion of the view has been fully optimized. SQLab and other SQL tuning techniques should be used for this purpose.

When Should Functions be used in a View
* When you need one value from another table
* To eliminate outer joins
* When the data cannot be easily selected as part of a join condition
* To simplify the view definition and improve readability

When NOT to use Functions in a View
* When you need more than one column from a particular table. A join should be used in the case. (If you need to do an outer join, you may want to test the performance differences between using multiple functions and using an outer join.)

Using Parameters from the Menu
Scenario: We need to pass into a form a parameter from the menu which will indicate to the 'Revenue Lines Split' form(OECRVSPL.fmb) whether it should display the canvas-views related to header level splitting, or line level splitting. Also we will use the parameter to dynamically set the default where clause for the generic first block.

1. Define PARAMETER in Form. In this example, parameter is called 'TYPE_OF_SPLIT' and is char 30.
* Highlight Parameter
* Press the Create button
* Pull up the Properties sheet to change the name and attributes
2. Add parameter to the Function used by the menu. In Menu function define the value that you want to set it to.
* Query up your form function on the Functions screen
* Select the Form region and place your cursor in the Parameters field.
* Enter the parameter name (make sure that you use the same name that you created in your form) followed by an equal sign and a value. For example, TYPE_OF_SPLIT=LINES
3. Utilize parameter in form logic.
Example in package OECRVSPL_OTHERS (Procedure called from WHEN-NEW-FORM-INSTANCE.), we have the following code:

PROCEDURE DISPLAY_CANVAS IS
BEGIN
if :PARAMETER.TYPE_OF_SPLIT='HEADER' then <----- note, use of ':' hide_view('ord_lines_ccv'); set_block_property('ORDER_INFO_CB',DEFAULT_WHERE,'where line_id is null'); set_block_property('REV_LINES_CB',DEFAULT_WHERE,'where line_id is null'); set_item_property('order_type',next_navigation_item,'order_number'); set_item_property('order_number',previous_navigation_item,'order_type'); elsif :PARAMETER.TYPE_OF_SPLIT='LINES' then set_block_property('ORDER_INFO_CB',DEFAULT_WHERE,'where line_id is not null'); show_view('ord_lines_ccv'); end if; END DISPLAY_CANVAS;

Hints and Tips

* Save frequently. Occasional GPF errors can result in lost work.
* Compile errors may cause the designer to shut down, so you should save changes before you Compile packages and procedures.
* If you do not have ROW_ID as one of the columns in a view, the Who information will not be displayed correctly.
* If a package begins to get too large, split it logically into more than one package. Packages should be limited to less than 64K and fewer than 25 procedures. If your package is too large, create another one by the same name with the number 2 (or 3, 4, 5, etc., as needed) added to the end.
* If you get an error in the PL/SQL Editor that does not make sense (i.e., Error at Line 0, Column 0…), try using File Compile All.
* CTRL-B is the Key Menu key and can be used to get the LOV for Alt Regions.
* CTRL-Shift-<> can be used to add an item.
* To rename a program unit, open the PL/SQL editor and change the definition of the Package or Procedure. When you save the Package or Procedure, the Program Unit name will reflect the Package or Procedure’s new name.
* When a referenced form is changed, you must FTP both the referenced form and the one that is using the reference.
* To find where an object is referenced from, look at the Reference Information property. If a More button does not show up at the top of the window, look at the next higher level or Object Group.
* Alternative Regions are not easy to code.
* Some forms have referenced LOVs and other objects from “standard” application forms (for example, OEXSTAND). These LOVs are returning values into blocks that do not exist in the “standard” form, so you cannot alter them.

* Dependent and/or conditionally mandatory fields may cause problems when querying records because the code does not fire at the right time and you may get a “Field must be entered” error message an some rows will not be returned. See Tara for a work-around.
* You cannot call APPCORE routines from the Custom Library, so putting code in triggers when customizing Oracle forms may be easier than trying to use the Custom library.
* Views are a pain to code, but they are better for performance reasons than POST-QUERY selects.
* You should not use a combination block for header and detail information because the header will be cleared when you query. Use a dummy block to hold the header information.
* When calling messages from the Message Dictionary, be sure that the name is typed exactly as it is defined in the Message Dictionary. Be especially careful of imbedded spaces, which may be hard to see.
* You must FTP forms, libraries and reports as binary. If you use ASCII, you will get a cannot open file message.
* The file extensions must be in lower case (i.e., forms does not recognize .FMB files).
* To ensure that file ownership is correct, FTP and generate files as devmgr.
* You must have write permissions on form (.fmb) and library (.pll) files to be able to generate them. You will get “Cannot create form file” error messages if the permissions are not correct.
* If a library attached to the CUSTOM library is missing, you will get an error message about APPCORE when you try to generate forms.
* If you get the message ‘Record was updated by another user’ when you try to change a value in a block based on a view, check your LOCK_ROW procedure and make sure that you are comparing the fields correctly (fields that can be null must have the null conditions tested; ROWID in the view must be from the same table that your cursor is using; forms removes trailing spaces, so you may need to use rtrim.)
* The APP_ITEM_PROPERTY.SET_PROPERTY routine does not always work correctly for buttons, pop lists, check boxes and display items. Make sure that the properties being set can be set for the item.
* All select statements in triggers should be encapsulated in an exception handling routine especially for NO DATA FOUND or possibly, TOO MANY ROWS.
* Forms 2.3 only returns one value in a select statement even if more than one row exists. To reproduce this in Forms 4.5, you should use a cursor or add where rownum = 1 to the select.
* Field attributes must be set and unset on a record by record basis. Setting an attribute changes it for all records.
* You cannot have an attached library and a referenced form by the same name.
* Excluding a function from an Oracle form does not necessarily prevent navigation to the region or totally disable the functionality. Be sure that you test to ensure the function is disabled using both mouse functions and keyboard shortcuts and keys.
* If you want to disable a trigger, you can delete it (since your code is in a procedure rather than in the trigger) or comment out the procedure call and add a null; statement to the trigger.
* Debug only shows what the changes are in the form and not necessarily each trigger that is being fired.
* When you add new fields to an existing block, it appears that the new fields are automatically placed at the beginning of the block. You will need to move them to the correct location within the navigator.
* A stacked canvas will automatically display when the cursor is in a field on the canvas. To prevent a canvas from displaying automatically out of sequence, make sure that your field prompting sequence is correct.
* If you want a scroll bar to automatically appear on a stacked canvas view, make the Width/Height size smaller than the Display Width/Height and set the View Vertical/Horizontal Scroll Bar to True.
* Any time you get the message ‘Attached library … contains a non-portable directory specification. Remove Path?’, answer YES to remove the path. If you answer No, you will get FRM-99999 errors. You will need to remove and reattach all libraries. (FNDSQF, APPCORE and APPDAYPK are the only libraries attached to the template form – all others are attached to these libraries.)

New Hints/Tips –Forms 6i:

* To convert a form to a text format, use the File-->Administration-->Object List Report option in the Forms Builder (Forms 6i tool). This file (.txt) will show the properties of all form objects, code in triggers, procedures (and so forth), in text format. Note: When using the File-->Administration-->Convert, Form, Binary to Text option, the file (.fmt) that is produced will show the code in the triggers and procedures in hexadecimal format.
* Property Class Names are specified by clicking in the "Subclass Information" property. A "Subclass Information" window opens up. Within this window, you can click on the "Property Class" radio button, to specify the Property Class Name value.
* The "Canvas Type" Property for a TAB Canvas should be set to TAB. If you place a value other than TAB_CANVAS (or leave it null) in the "Subclass Information" Property, the "Canvas Type" Property is auto. flipped to CONTENT. Need to be aware of this because once this is done, your Layout Editor will look WACKY! Also, by that "Canvas Type" Property being flipped to CONTENT, you will no longer see your Tab Pages under the Canvas node. You have to flip the "Canvas Type" Property back to TAB. Then you'll see your Tab Pages under the Canvas node, again.