Saturday, September 27, 2008

Good Practices for Beginners

Most of the developers are like 'accidental developers' and they identify the database performance problems only when the client reports the issues. If you give importants to these guidelines, you can standardise the performance to a stable level. I wrote this article from my own experience and knowledge. Since internet is a wide resource, like most of the professionals I also got such information from the contributions which I read from browsing.

The statements provided here may not be applicable to all the databases since all the features (Eg: Stored Procedures) are not available on all the database systems.

Good Practices for Beginners
1. Divide your large table to small multiple tables. (In SQL terminology we call it as Normalization)
2. Use the lookup tables. This will help you reduce the overload of tables. Eg: if you have a product-sales table and want to store 3 pictures of the same product, rather than adding three columns to the table use one lookup table. This way you have the freedom to add any number of photos.
3. Also, use only necessary columns. Eg: If you have columns A & B and you have the sum in another column C. Simply, you don't need that C since we can re produce the same table effect with statement SELECT A, B, (A+B) AS C FROM TABLE1.
4. The performance of your database increases if the primary key is numeric or small in data width.
5. Do not use database to store your images. Good approach is store only URL in the table and store the image file in desk itself.
6. But sometimes the Normalization may fail if you have a large database, much calculations and frequent calls if the output table is almost static in result. (Eg: salary details of employees for years old data). In this case you can improve the performance by using a De-normalized table. But that too have much demerits like very large database etc.
7. Use the right data types and widths when defining columns. Eg: If you want to store the 'age', then you don't need to use the VARCHAR field since a INT can do the job. (INT can store integer data from 0 to 255. You know no 'age' value exceeds 255)
8. Start! If you don't have the practice of using Primary Key, Foreign Key etc.
9. Use stored procedures and functions instead of writing all the messy code in the program itself. It not only gives you the performance but also a matter of security.
10. Always try to keep one error table to log all the errors which comes from the stored procedures or functions. It helps you save time and avoid tension for finding 'Where is the bug?'
11. Use transactions to avoid loss of data while the execution of a stored procedure. The possibility of failure is high. It can be a data truncation problem, network problem etc. In MSSQL we have BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION available. Use this along with the error handling methods. Regarding performance TRANSACTION is one step down when comparing with ordinary statements but when considering quality of the product it is very high.
12. Avoid using CURSORs. Use only when there is no other way exists. In most cases CURSOR consumes much time since it is a record-by-record process.
13. Avoid using GOTOs. This is not just for SQL but for all the programming languages. GOTO statement is considered unstructured and it have the possibility of jumping out of our hands. But there are circumstances which we cannot avoid GOTO.
14. Avoid IF and start using CASE.
15. Write your code readable.
a. User proper indents.
b. Keep all the statements in upper case.
c. Use lowercase for data-types
d. Use Upper Camel notations (also known as Pascal Casing) for all user created objects. Eg: @EmployeeCode
e. Use meaningful user-defined identifiers. Use only names which contain A-Z, a Z, 0-9 and underscore character. Do not use regional special characters.
f. Specify operation performed also in the name of stored procedure. Eg: spEmployeeUpdate
g. Always use the schema also while calling sql objects. Eg: EXEC dbo.spEmployeeUpdate
h. Use BEGIN and END to specify block of statements
i. User alias where we need operations including multiple tables
j. Even though the AS keyword is optional, use always.
16. Use proper commenting. Also add the purpose, author, date, version etc. details on top of all the procedures, functions etc.
17. Add test data with statements inside stored procedures and comment it. This helps you in future debugging. Also this helps another developer to get a quick start.
18. Do proper INDEX-ing. There will be noticeable difference when applying INDEX on very large tables.
19. Always use column names in SELECT, INSERT, UPDATE statements. Avoid using asterisks –(*).
20. Avoid using dynamic SQL statements inside stored procedures. i.e., Do not use SQL statements to create SQL statements.
21. Be careful while SELECT-ing strings with LIKE clause. If it is not used wisely, it will arise performance problems.


Tuesday, September 23, 2008

Bounce Apache Server in Oracle Apps 11i

Please find the two commands that are used for bouncing the Apache below:

$COMMON_TOP/admin/scripts/$TWO_TASK*/ stop
$COMMON_TOP/admin/scripts/$TWO_TASK*/ start

Profile Options in HRMS

Profile Options provide flexibility to Oracle Apps, they act like global variables .It eliminates the need to hard code the logic.

What is profile option?
The profile option acts like a Global Variable in Oracle.

Why does Oracle provide profile options?
These are provided to keep the application flexible. The business rules in various countries and various companies can be different. Hence the profile options are delivered by Oracle in such a manner to avoid hard-coding of logic, and to let the implementation team at site decide the values of those variables.

An organization have two departments say D1 and D2.We want the Manager of D1 to restrict viewing employees of D2 and vice versa. Then we will set the profile options MO:Department name or the organization name against the profile option.

Some of the scenarios where profile options can be used by Oracle are as follows:
1. There are profile options which can turn the debugging on, to generate debug messages. Say one of 1000 users reports a problem, and hence you wish to enable debugging against just that specific user. In this case you can “Turn On” the debugging profile option "again that specific user".
2. There are profile options that control which user can give discount to their customers at the time of data entry. You can set profile option "Discount Allowed" to a value of either Yes or No against each Order Entry user.
3. Lets assume an Organization has department D1 and D2. Managers of both the Departments have "HRMS Employee View" responsibility. But you do not want Manager of D2 to be able to see the list of Employees in Organization D1. Hence you can set a profile option against the username of each of these users. The value assigned to such profile option will be "Name of the Organization" for which they can see the employees. Of course, the SQL in screen that displays list of employees will filter off the data based on “logged in users profile option value”.

Profile options determines the behaviour of our application in oracle hrms. There are many profile values in Oracle HRMS, But basically we need to set 4 profile values only.

1. HR:Security Profile
2. HR:User Type as Only HR/HR with Payroll
3. HR:Cross Business Group as yes/no, if yes.. then user can access data from other Business group too.
4. MO:Operating Unit, if Multi Org is set up for a BG then
this is mandatory to setup ow optional.

CRP (Conference Room Pilot)

The definition of prototyping, or a conference room pilot (CRP) is “a product model constructed for testing and evaluation to see how the product performs before releasing the product to manufacture.” A conference room pilot is exactly that, a prototyping of the software functionality and customizations prior to releasing the product to the end user.

Each customization should be prototyped and executed during a series of detailed pilots or module walk-throughs.Each pilot should be designed to target a specific stage, event, or business process of the enterprise during an implementation and should determine success or failure as the outcome.

A successful CRP can be divided into three stages, namely Identification, Definition, and Execution.

Identification: -
The first step in most projects is to understand and document the current organization business processes, or the creation of the “as is business model”. Understanding the organizations business processes helps lay the foundation required for a successful implementation.

The Definition stage provides some of the keys to executing the CRP – one of them being the end user testing documentation. This stage consists primarily of documenting the testing scenarios and scripts for execution during the CRP. These scenarios will consist of real organization processes and data. Remember the CRP is a prototype of the production environment and all testing needs to be as realistic as possible.

Business scenarios are your organizations required future business process based on Oracle Application terminology and processes.

During this stage of the project normal implementation activities of creating the master testing plan, scheduling CRP resources and executing the CRP tasks place.This stage is where the rubber meets the road. All testing documents have been approved, the CRP instance is ready and waiting, and all we need to do to get on with the CRP is to organize the testing documentation and assign testing resources.

The CRP should be performed at least three times, or should simulate three business closings. During the execution of the CRPs you will find that problems and issues will arise, sometimes over and over again. Executing the CRP three times will help flush out application issues, train end users, and solidify customizations and data conversion

Original content taken from 'OAUG Insight March 2008' by 'Bill Dunham'. All credits on this document should go to 'Bill Dunham'

Wednesday, September 17, 2008

ORA-06502: PL/SQL: numeric or value error: host bind array too small

ORA-06502: PL/SQL: numeric or value error: host bind array too small

255 characters via DBMS_OUTPUT.PUT_LINE results in the following error when run from 10.1 SQL*Plus or prior, when connected to a 10.2 database

We need to substring of 255 characters for the value passed in the dbms_output statement.

Monday, September 15, 2008

Oracle 6i Reports/Report Builder - Part I

About Report Builder
Report Builder enables you to develop production-quality reports in a client/server or Web environment. You can deploy reports on the Web using the Reports Server and Reports Web CGI or Reports Cartridge. Reports can be run standalone, or you can run them from forms or Graphics Builder displays.

Major concepts
A report is a collection of objects defining its data, layout, and runtime interface. To quickly create new reports, you use the Report Wizard. The wizard guides you through the process of choosing a report type, defining a data model, and laying out the data. You can reenter the Report Wizard after the initial creation of a report, so you may re-invoke it on an existing report, make changes, and re default the report, even if the report was not originally created with the Report Wizard.

Major Features
1. Data model and layout editors in which you can create the structure and format of your report
2. Object navigator to help you navigate among the data and layout objects in your report
3. Packaged functions for creating computations
4. Fully-integrated Previewer for viewing your report output
5. Support for fonts, colors, and graphics

Additional Features :
1. Context-sensitive online help system
2. Conditional printing capabilities
3. Non-procedural Approach: The unique non-procedural approach of Oracle Reports lets you concentrate on design improvements instead of programming. Its' easy- to-use, fill-in-the-form interface and powerful defaults make developing and maintaining even the most complex reports fast and simple.
4. Full Integration with Other Oracle Products: One can integrate Oracle Reports with other Oracle products such as Oracle Forms, Oracle Graphics, and Oracle Mail.
5. Open Architecture: Oracle Reports' open architecture enables incorporation of user-defined routines written in COBOL, C, and most other programming languages, as well as the powerful PL/SQL language.

Object Navigator
1. The Data Model editor, in which you define the data for the report.
Data model is composed of some or all of the following data definition objects:
a. Queries: Queries are ANSI-standard SQL SELECT statements that fetch data from a standard database.
b. Groups: Groups determine the hierarchy of the data appearing in a report, and are used primarily to create breaks. In addition to break reports, you also create groups to define the cross products of matrix reports.
c. Columns: Columns contain the data values for a report. Default report columns corresponding to the table columns included in each query's SELECT list are automatically created by Oracle Reports, then each column is placed in the group associated with the query that selected the column. If you want to perform summaries and computations on database column values, you can create new columns.
d. Parameters: Parameters are variables for your report that enable you to change selection criteria at runtime.
e. Links: Data links are used to establish parent-child relationships between queries and groups via column-matching. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. You can create links with various SQL clauses (i.e., WHERE, HAVING, or START WITH) and conditions. When a report with a data link is executed, the data link is converted into a SQL clause and appended to the child query. The child query is executed with the values of the primary key used by the parent.

2. The Layout editor, in which you create the report layout. Layout objects define a report's format; i.e., the positioning and appearance of data, text, and graphics in the report output.

3. The Parameter Form editor, in which you customize the appearance of the Runtime Parameter Form, a window that optionally appears at runtime and enables you to enter parameter values that affect report execution.
4. The Report Editor's Live Previewer view enables you to preview and inspect your report output page by page.
5. Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report.
6. Program units are packages, functions, or procedures that you can reference from any PL/SQL within the current report.
7. Attached libraries are external PL/SQL libraries that you have associated with a report or another external library.

Data Model – Groups
There are two types of groups in Oracle Reports:
1. Default (created by Oracle Reports)
2. User-created (created by you)
Break groups are created to produce subtotals, print columns in a different direction, create breaks, and so on. A break group suppresses duplicate values in sequential records Cross product groups perform mathematical cross products, which are generally used to create matrix reports.

Data Model – Parameter
Parameter values can be specified in these ways:
1. Accepting the default parameter values (default values are set in the Parameter property sheet, and you can control whether the values are displayed at runtime on the Runtime Parameter Form)
2. Typing the parameter value(s) as arguments on the command line (where applicable)
3. Choosing from a list or entering the parameter value(s) in the Runtime Parameter Form

User Parameters
1. Bind References: Use a bind reference when you want the parameter to substitute only one value at runtime. Precede a bind reference with a colon (:).

2. Lexical References: Use a lexical reference when you want the parameter to substitute multiple values at runtime. Precede a lexical reference with an ampersand (&).

The remaining part would be continued in Part II shortly

Tips in Oracle 6i report customizations

1. Setup client info if views are involved, setup organization context if inventory items are involved.
2. Ensure that report builder/designer is in character mode.
3. Ensure constraints are off for stretching, grid is on. , snap to grid is on/off
4. use srw.message frequently
5. Port the rdf in Binary Mode.
6. Set the trace on for the concurrent program to actually see the sql code executed.
7. Bring the query out and changes should be tested in simple SQLs
8. Set the trace on if there are &lexical parameters if you need to check the query that is changing dynamically.
9. Use srw.message to print the value of lexical parameters after their evaluation
10. Use of Concsub to submit your report concurrent program from UNIX faster.

Some of the Key Flexfields in Oracle Apps

General Ledger

Accounts Receivables
1. Sales Tax KFF(AR_SALES_TAX)
2. Territory KFF(AR_TERRITORY)

Fixed Assets
2. Category KFF
3. Location KFF(FA_LOCATION)

1. Grade(PER_GRADE)
2. Item
4. Position(PER_POSITION)
5. Personal Analysis
6. Soft Coded

1. Account Aliases
2. Item Catalogs
3. Item Categories(MTL_ITEM_CATEGORY)
4. Sales Order
5. Stock Location
6. System Item(MTL_SYSTEM_ITEM)

Pay Roll
1. Bank Details
2. Cost Allocation
3. People Group

Understanding the importance of Object Version Number in HRMS

Nearly every row in every database table is assigned an object_version_number. When a new row is inserted, the API usually sets the object version number to 1. Whenever that row is updated in the database, the object version number is incremented. The row keeps that object version number until it is next updated or deleted. The number is not decremented or reset to a previous value.

Note: The object version number is not unique and does not replace the primary key. There can be many rows in the same table with the same version number. The object version number indicates the version of a specific primary key row. Whenever a database row is transferred (queried) to a client, the existing object version number is always transferred with the other attributes. If the object is modified by the client and saved back to the server, then the current server object version number is compared with the value passed from the client.

• If the two object version number values are the same, then the row on the server is in the same state as when the attributes were transferred to the client. As no other changes have occurred, the current change request can continue and the object version number is incremented.

• If the two values are different, then another user has already changed and committed the row on the server. The current change request is not allowed to continue because the modifications the other user made may be overwritten and lost. (Database locks are used to prevent another user from overwriting uncommitted changes.) The object version number provides similar validation comparison to the online system.

Forms interactively compare all the field values and displays the "Record has been modified by another user" error message if any differences are found. Object version numbers allow transactions to occur across longer periods of time without holding long term database locks. For example, the client application may save the row locally, disconnect from the server and reconnect at a later date to save the change to the database. Additionally, you do not need to check all the values on the client and the server.

Consider creating a new address for a Person. The create_person_address API automatically sets the object_version_number to 1 on the new database row. Then, two separate users query this address at the same time.

User A and user B will both see the same address details with the current object_version_number equal to 1. User A updates the Town field to a different value and calls the update_person_address API passing the current object_version_number equal to 1. As this object_version_number is the same as the value on the database row the update is allowed and the object_version_number is incremented to 2. The new object_version_number is returned to user A and the row is committed in the database.

User B, who has details of the original row, notices that first line of the address is incorrect. User B calls the update_person_address API, passing the new first line and what he thinks is the current object_version_number (1). The API compares this value with the current value on the database row (2). As there is a difference the update is not allowed to continue and an error is returned to user B.

To correct the problem, user B then re-queries this address, seeing the new town and obtains the object_version_number 2. The first line of the address is updated and the update_person_address API is called again. As the object_version_number is the same as the value on the database row the update is allowed to continue.

Therefore both updates have been applied without overwriting the first change.

Friday, September 12, 2008

Off the topic

Fishing at Client Place ;)

Tuesday, September 9, 2008

All about the DUAL table

Found this intresting topic on Dual table. Check these out.

Run a UNIX shell script as a concurrent program

To run a UNIX shell script as a concurrent program, we need to register the shell script as a Host Concurrent Program. Example: Say we register an executable named XXCONC within XXPO Module.

on unix, do

1. cd $XXPO_TOP/bin
2. vi XXCONC.prog and edit and write script and save
3. ln -s $FND_TOP/bin/fndcpesr XXCONC
4. ls -l XXCONC

Also we need to ensure that there is execute permission for executable

Once done register the program with Host and assign to a request group.

Commonly used scripts

Query to get responsibility and attached request groups
SELECT responsibility_name responsibility, request_group_name, frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name

Query to get all Request attached to a responsibility
SELECT responsibility_name, frg.request_group_name, fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg, fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv, fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

Query to Count Module Wise Report
SELECT fa.application_short_name,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
) exe_method, COUNT (concurrent_program_id) COUNT
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code

Query to calculate request time
SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
, DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.description||']',p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;

Query to check responsibility assigned to a specific USER
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
and a.application_name = 'Purchasing'
ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)

Query to print the oracle apps version
SELECT substr(a.application_short_name, 1, 5) application_short_name,
substr(t.application_name, 1, 50) application_name,
p.product_version version
FROM fnd_application a,
fnd_application_tl t,
fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = USERENV('LANG')

Script used to pick up the recent EBS users and their email addresses
SELECT DISTINCT ppf.full_name
, fu.user_name
, NVL(ppf.email_address,fu.email_address) email_address
FROM per_people_f ppf
, fnd_user fu
, fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppf.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('SYSADMIN', 'GUEST')

Script to assign access to Oracle e-Business Suite responsoibilities via Workflow
v_user fnd_user.user_name%TYPE;
v_user_id PLS_INTEGER;

CURSOR c_get_user (cp_username IN VARCHAR2)
SELECT user_orig_system_id user_id, user_orig_system orig_system
FROM wf_local_user_roles
WHERE user_name = cp_username;

CURSOR c_get_resp
SELECT NAME, orig_system, orig_system_id, partition_id
FROM wf_roles
WHERE display_name = 'System Administrator'
AND orig_system = 'FND_RESP';

r_resp c_get_resp%ROWTYPE;
r_user c_get_user%ROWTYPE;
v_user := UPPER ('&username');

OPEN c_get_user (cp_username => v_user);

FETCH c_get_user
INTO r_user;

CLOSE c_get_user;

OPEN c_get_resp;

FETCH c_get_resp
INTO r_resp;

CLOSE c_get_resp;

INSERT INTO wf_local_user_roles
(user_name, role_name, user_orig_system, user_orig_system_id,
role_orig_system, role_orig_system_id, start_date,
expiration_date, security_group_id, partition_id, owner_tag,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login, assignment_type,
parent_orig_system, parent_orig_system_id, user_start_date,
role_start_date, user_end_date, role_end_date,
effective_start_date, effective_end_date
VALUES (v_user, r_resp.NAME, r_user.orig_system, r_user.user_id,
r_resp.orig_system, r_resp.orig_system_id, SYSDATE - 1,
NULL, 0, r_resp.partition_id, 'SYSADMIN',
0, SYSDATE - 1, 0,
SYSDATE - 1, 0, 'D',
r_resp.orig_system, r_resp.orig_system_id, SYSDATE - 1,
TO_DATE ('01/01/1951', 'DD/MM/YYYY'), NULL, NULL,

Query to fetch the managers managers information in HRMS
,papf.person_id employee_person_id
,papf.full_name employee_name
,papf.email_address emp_email_address
,papf.employee_number mgr_employee_number
,papf_mgr.person_id manager_person_id
,papf_mgr.FULL_NAME manager_full_name
,papf_mgr.email_address manager_email_address
per_all_people_f papf,
per_all_people_f papf_mgr
(papf.person_id,papf_mgr.person_id) in
( select
per_all_Assignments_f pasf
start with
( pasf.person_id = :x_person_id and
sysdate between pasf.effective_start_date and pasf.effective_end_date )
connect by prior
pasf.supervisor_id = pasf.person_id
and sysdate between pasf.effective_start_date and pasf.effective_end_date
and sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between papf_mgr.effective_start_date and papf_mgr.effective_end_date

Steps for finding the Request Group of Concurrent Programs

Step 1: Ensure that profile option "Utilities:Diagnostics" is set to Yes at the user level.
Note: We can skip this step in case we have the apps password.

Step 2: Navigate to the responsibility from where we will submit the concurrent program.

Step 3: Navigate to the "Submit New Request" screen, where we will enter the name of the concurrent program to run.

Step 4: Now we must do examine using the help menu.

Step 5: In the block field, we need to enter PARAMETER, and in the field name we need to enter REQUEST_GROUP_CODE. Note down this value

Step 6: Now in the value field enter REQUEST_GROUP_APPL_SHORT_NAME.

Note down the two values from Step 5 and Step 6.

These will indicate the request group to which you must add your concurrent program.

Note: In the submit request screen, if the REQUEST_GROUP_CODE is blank then we need to use the Request group specified in the "Define Responsibility Screen"

R12 Architecture

Friday, September 5, 2008

Key EUL tables for the different Discoverer components.

List of Business Areas
select ba_name "Business Area", ba_created_by "Creator", ba_created_date "Creation Date", ba_updated_by "Updated By ", ba_updated_date "Last Update Date" , ba_id
from disco.eul4_bas
where ba_created_by like 'DISCO'

List of Folders
select b.ba_name, f.obj_name folder_name, f.obj_id, f.obj_ext_owner Owner
from disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where 1=1
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('%')
order by b.ba_name,f.obj_name

List of Folder Items
select i.exp_name item_name, i.exp_id, i.it_ext_column, f.obj_name folder_name, b.ba_name
from disco.eul4_expressions i,
disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where f.obj_id= i.it_obj_id
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(i.exp_name) like upper('%')
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('Products')
order by b.ba_name,

List of Folder Joins
select key_description
from disco.eul4_key_cons
where fk_obj_id_remote=100027 or key_obj_id=100027

(substitute the numeric id from the Folders obj_id (see the List of Folder query)

List of Workbooks
select doc_name "Document",doc_developer_key, doc_description "Description" from disco.eul4_documents

Note: In the above examples, replace "disco" with the appropriate schema owner of EUL tables. In my case it is the disco owner and hence the examples

Thursday, September 4, 2008

Flow of Setup in Order Management

Step 1

1. Flex fields
Define key and descriptive flex fields to capture additional information about orders and transactions.
This step is required for Key Flex fields, and optional if you plan on using the functionality surrounding Descriptive Flex fields. Several defaulting values are provided.

Step 2

2. Multiple Organizations
Define multiple organizations in Oracle Inventory.
This step is optional.

Step 3

3. Inventory Organizations
Define inventory organizations (warehouses), parameters, sub inventories, and Picking rules in Oracle Inventory. You must define at least one item validation organization and at least one organization that acts as an inventory source for orders fulfilled internally. If you plan to drop ship some orders, you must also define at least one logical organization for receiving purposes. Your item validation organization can be the same as your inventory source or your logical receiving organization, but you cannot use one organization for all three purposes. See Step 5 for setting your item validation organization.
This step is required.

Step 4

4. Profile Options
Define profile options to specify certain implementation parameters, processing options, and system options.
This step is required.

Step 5

5. Parameters
Set your Order Management Parameters to validate items, enable customer relationships, and operating unit defaults.
This step is required.

Step 6

6. Invoicing
Define invoicing information, including payment terms, invoicing and accounting rules, Auto accounting parameters, territories, and invoice sources.
This step is required if you plan on transferring invoicing information to Oracle Receivables. Several defaulting values are provided.

Step 7

7. Salespersons
Define information on your sales representatives.
This step is optional.

Step 8

8. Tax
Define tax features, such as codes, rates, exceptions, and exemptions.
This step is required.

Step 9

9. Quick Codes
Define Quick Codes that provide custom values for many lists of values throughout Order Management.
This step is required if you plan on creating user defined Quick codes for utilization within Order Management. Defaulting values are provided.

Step 10

10. Workflow
Define order and line processing flows to meet different order and line type requirements.
This step is required.

Step 11

11. Document Sequences (Order Numbering)
Define Document Sequences for automatic or manual numbering of orders.
This step is required.

Step 12

12. Order Import Sources
Define sources for importing orders into Order Management.
This step is required if you plan on importing orders or returns into Order Management.

Step 13

13. Units of Measure
Define the units of measure in which you supply items.
This step is required.

Step 14

14. Item Information
Define item information, including item attribute controls, categories, and statuses.
This step is required.

Step 15

15. Items
Define the items that you sell, as well as container items.
This step is required.

Step 16

16. Configurations
Define the configurations that you sell.
This step is required if you plan on generating orders or returns for configured items. Several defaulting values are provided.

Step 17

17. Pricing
Define price lists for each combination of item and unit of measure that you sell. Optionally, you can define pricing rules and parameters to add flexibility.
This step is required.

Step 18

18. Customer Classes
Define customer profile classes.
This step is required if you plan on using the functionality surrounding Customer Profiles. Several defaulting values are provided.

Step 19

19. Customers
Define information on your customers.
This step is required.

Step 20

20. Item Cross References
Define item cross references for ordering by customer part number, UPC, or any generic item number.
This step is required if you plan on using the functionality surrounding item cross referencing. Several defaulting values have been provided.

Step 21

21. Sourcing
Define your sourcing rules for scheduling supply chain ATP functions.
This step is optional.

Step 22

22. Order Management Transaction Types (Order and Line Types)
Define Order Management transaction types to classify orders and returns. For each order type, you can assign a default price list, defaulting rules, order lines, return lines, line types, workflow assignments, payment terms, and freight terms.
This step is required.

Note: Previous versions of this user’s guide referred to Order Management Transaction Types as either transaction types or order or line transaction types. In an effort to distinguish between the various Oracle Transaction types available, Order Management Transaction types will now be referred to as either OM Order or Line Transaction Types.

Note: Order Management provides NO seeded OM transaction types. For existing Oracle Order Entry customers, Order Management will update existing Order Types to OM transaction type during the upgrade process.

Step 23

23. Cost of Goods Sold (COGS)
Set up your Cost of Goods Sold Accounting Flex field combination (COGS Account) in Oracle Inventory.

This step is required if you plan on utilizing the functionality surrounding COGS.

Step 24

24. Processing Constraints
Define processing constraints to prevent users from adding updating, deleting, splitting lines, and canceling order or return information beyond certain points in your order cycles. Use the constraints Order Management provides, which prevent data integrity violations, or create your own.

This step is optional. Several default values for processing constraints have been defined.

Step 25

25. Defaulting Rules
Define defaulting rules to determine the source and prioritization for defaulting order information to reduce the amount of information you must enter manually in the Sales Orders window.
This step is optional. Several Defaulting rules and corresponding values for have been defined.

Step 26

26. Credit Checking
Define your credit checking rules.
This step is required if you plan on performing any type of order credit checking.

Step 27

27. Holds
Define automatic holds to apply to orders and returns.
This step is required if you plan on performing automatic hold for orders or returns.

Step 28

28. Attachments
Define standard documents to attach automatically to orders and returns.
This step is optional.

Step 29

29. Freight Charges and Carriers
Define freight charges and freight carriers to specify on orders.
This step is required if you plan on charging customers for freight or additional order charges.

Step 30

30. Shipping
Define shipping parameters in Oracle Shipping Execution.
This step is required.



Flow of Setup in Pricing

Step 1: Perform System Administration Steps
Default: None

Assign users who set up Oracle Advanced Pricing to the Oracle Pricing Manager responsibility.

Step 2: Set Profile Options

You set a value for each user profile option to specify how Oracle Advanced Pricing controls access to and processes data.

Step 3: Set Multi-Currency Profile Options
Default: None

The profile option QP: Multi-Currency Installed enables the multi-currency price list feature. Multi Currency Price Lists enables you to maintain a single price list for multiple currencies.

Step 4: Perform System Sourcing
Default: Predefined Oracle Advanced Pricing record

This step is required if:

■ Your pricing data application source is anything other than Oracle Advanced Pricing.

■ You are integrating Oracle Advanced Pricing with an application other than Oracle Order Management.

Warning: Changing the system source code can severely affect pricing engine behavior.

Step 5: Verify or Create PTE
Default: Order Fulfillment

A Pricing Transaction Entity (PTE) is an ordering structure that has associated Request Types and Source Systems. Request Types and Source Systems in the same PTE share pricing setup data. Additional Source System and Request Types can be added to existing PTE's.

There are very rare instances where it is necessary to create a new PTE. A new PTE needs to be created only if the new request type uses a different ordering structure and a different set of source systems that is not already predefined.

Step 6: Create Qualifier Contexts and Qualifier Attributes
Default: Predefined Oracle Advanced Pricing qualifier contexts

If you skip this step, users will be able to chose only predefined Oracle qualifier contexts and qualifier attributes for price and modifier eligibility.

Qualifiers provide a highly configurable and flexible method of defining the rules which your business uses to manage pricing. Qualifiers are used by the pricing engine to determine eligibility for price lists and modifiers.

Step 7: Create Pricing Contexts, Pricing Attributes, and Product Attributes
Default: Predefined Oracle Advanced Pricing/Product attribute contexts

Pricing/Product attributes are a feature of Oracle Advanced Pricing which enables you to define necessary item attributes in order to price or apply a modifier and attributes used in formulas.

If you do not perform this step, users will be able to select only predefined Oracle Advanced Pricing contexts and associated attribute values for benefit options.

Step 8: Perform Attribute Mapping
Default: Predefined Oracle Advanced Pricing attribute mapping rules.

Step 9: Define Unit of Measure
Default: None

Units of measure (UOM) are used in Oracle Advanced Pricing to determine the unit value for what thepricing engine is pricing, modifying, returning a benefit, or creating an accrual.

Do this step if you have not installed and set up Oracle Inventory or performed this common-applications setup for another Oracle Product.

Step 10: Define Unit of Measure Conversions
Default: None

You must define conversion rates between the base unit of measure and other units of measure within a UOM class if you want to price and discount an item in a UOM other than its primary UOM. Oracle Advanced Pricing uses these conversions to automatically convert transaction quantities to the primary pricing unit of measure defined on the price list when pricing cannot find a price in the transaction unit of measure. In addition, all price adjustments, benefits, and charges need to be defined in the same unit of measure as the unit of measure used on the price list.

Step 11: Define Item Category Sets and Item Categories
Default: Seeded structure name of item categories, category set inventory items, and associated default seeded category code combination MISC

Step 12: Set Up Inventory Organization
Default: None
Required with defaults

You must define at least one item validation organization in Oracle Inventory. This is the organization that items are validated and viewed against when entering items in the Price List and Modifier Setup forms.

Perform this step if you have not installed and set up Oracle Inventory or performed a common-applications setup.

Step 13: Define Item Information
Default: None

Define the items that you wish to price and discount and assign them to the validation organizations defined in step 10. If you want to define qualifier rules which include the seeded qualifiers Line Volume or Line Weight you must set the
volume or weight attributes of each item as this is used by attribute mapping to derive the transaction line, weight, or volume.

Perform this step if you have not installed and set up Oracle Inventory or performed this common-applications setup for another Oracle product.

Step 14: Create Item Relationships
Default: None

This step is required if you wish to give item upgrade benefits. You must define a Promotional Upgrade item relationship from the ordered item to the item you wish to give as an upgrade. Define your item relationships for the item validation organization.

Set up promotional upgrade items as follows:

■ The ordered item and the promotional item need to have the same base unit of measure and unit of measure conversions.

■ The modifier unit of measure and the pricing unit of measure on the order line need to be the same.

If those entities are not the same, the substitution can fail.

Step 15: Define Pricing Lookups
Default: Lookup type dependent

Lookup codes supply many of the lists of values in Oracle Advanced Pricing. Lookup code values are the valid entries that appear in the list of values. They simplify information selection, and ensure that users enter only valid data into
Oracle Advanced Pricing. You can add new lookup values at any time. You can set the Enable flag to No so that it will no longer appear in the list of values, or you can use start and end dates to control when a value will appear in a list.

Step 16: Define Oracle Order Management Lookups
Default: Lookup type dependent

Perform this step if you have not installed and set up Oracle Order Management or performed a common-applications setup.

Step 17: Define Shipping Lookups
Default: Lookup type dependent

Perform this step if you have not installed and set up Oracle Order Management or performed a common-applications setup.

Step 18: Set Up Customer Class and Profile Class
Set Up Customer Class
Default: None

Required if you price, give benefits or charge by customer class.

Do this step if you have not installed and set up Oracle Receivables or performed a common-applications setup.

Set Up Profile Classes
Default: None
Required if you price, give benefits or charge by customer account type.

Perform this step if you have not installed and set up Oracle Receivables or performed a common-applications setup.

Step 19: Define Customers and Customer Sites
Default: None
Required if you price, give benefits or charge by customer.

Perform this step if you have not installed and set up Oracle Receivables or performed a common-applications setup.

Define Customer Sites
Default: None

Required if you price, give benefits or charge by customer site.

Perform this step if you have not installed and set up Oracle Receivables or performed a common-applications setup

Step 20: Define Order Type
Default: None

Required if you price, give benefits or charge by order type.

Step 21: Define Line Type
Default: None

Required if you price, give benefits, or charge by order line type.

Step 22: Define Freight Terms
Default: None
Required if you price, give benefits, including upgrading freight terms, or charge by freight terms.

Step 23: Define Freight Cost Type
Default: None
Required if you price, give benefits or calculate charges using freight cost types.

Step 24: Define Payment Terms
Default: None

This step is required if you price, give benefits, or charge by payment terms.

Perform this step if you have not installed and set up Oracle Receivables or performed a common-applications setup.

Step 25: Enable Currencies
Default: All major currencies predefined with Oracle Applications

The system administrator performs this step. The codes are ISO standard codes for currencies. You must enable the specific currencies you want to use on your price and modifier lists.

Perform this step if you have not installed and set up Oracle General Ledger or performed a common-applications setup.

Step 26: Perform System Sourcing
Default: Predefined Oracle Advanced Pricing record

This step is required if:

■ Your pricing data application source is anything other than Oracle Advanced Pricing.

■ You are integrating Oracle Advanced Pricing with an application other than Oracle Order Management.

Step 27: Create Events and Phases
Default: Seeded Oracle Advanced Pricing phases

Step 28: Set Profile Options

Attachments in Oracle

Attachments are used to link data such as images and word processing documents to your application data. To link non–structured data such as images, word processing documents, spreadsheets, web pages, or video to more structured application data.


The fig(a) shows the toolbar Attachment icon if it is enabled it will allow you attach data to a particular record.

The fig(b) shows the Attachment window. The Attachments window can be used to view or delete existing attachments, and create new attachments.


As we ca see in the figure there are many fields which need to be filled while creating an attachment.


The category controls which forms can access the document. Each form that enables Attachments must list which document categories it can access. A “Miscellaneous” category will be assigned to each form registered for Attachments to facilitate
“cross–form” visibility of Attachments.

An attached document can be:
1. Document Reference: A reference to any type of document stored in a database that is accessed through a Document Management system.
2. Short Text: Text stored in the database containing less than 2000 characters.
3. Long Text: Text stored in the database containing 2000 characters or more.
4. File: A file that requires other server applications to view, such as Microsoft Word or Microsoft Excel, image files such as .JPG files, or other types of files. When you attach or create a File type document, the document is loaded into the database. When a File document is selected, the lower half of the Attachments window displays an ”Open Document” button that invokes a web browser and passes the file to the browser.
5. Web Page: A URL reference to a web page that you can view with your web browser. You must define your web browser in the profile option Web Browser to view a web page attachment.

File or URL

If the document is a file, specify the location of the document. The document will then be loaded into the database. If the document is a web page, specify the web page URL, such as

May be Changed

The May be Changed checkbox indicates if you are able to edit the attachment.

Document Block

The document block is the section of the window below the multi–line attachment block. The document block displays the current document in the attachments block, or it displays an Open Document button that opens your document for viewing with a browser.

Adding an Attachment

In order to add an attachment you can either click on the toolbar icon shown in fig(a) or you could go to View->Attachments.


To attach a new document to an Oracle Applications record:

1. In an application window, query or enter a data record.
2. Choose the Attachment toolbar icon. The Attachments window opens.
3. Enter a category, a description, and a data type.
4. Type in your document text, or specify a file name or web page URL.
5. Save your work.

To attach an existing document to an Oracle Applications record:

1. In an application window, query or enter a data record.
2. Choose the Attachment toolbar icon. The Attachments window opens.
3. Choose the Document Catalog... button. The Document Catalog window opens.
4. Query an existing document.
5. Check one or more documents.
6. Choose the Attach (number of documents) button.
7. Save your work.

Viewing an Attached Document

1. Query a record.
2. Press the Attachment toolbar icon.
3. Move your cursor to the document you want to view. The attachment appears in the document block or you must press the Open Document button to see the document in a browser.

Deleting an Attachment

When you delete an attachment, you merely remove the association between a record and a document. The document itself is not removed from your file system or database. But the option of removing both the record and the attachment is also given.


To delete a record’s attachment:
1. In an application window, query a data record.
2. Choose the Attachment toolbar icon.
3. Move your cursor to the document you want to delete.
4. Choose Delete from the Edit menu.
5. Save your work.

Copying an Attachment from Another Record
Oracle Applications keeps a catalog of documents that have been attached to applications data records so far; therefore, you can take advantage of this catalog if you want to attach an existing document to another data record.


Steps to attach an existing document:

Document Catalog -> Find -> Attach 1

To copy an attachment from another record:
1. In an application window, query the record that you want to attach a document to.
( Don’t query the record that already has the attachment.)
2. Choose the Attachments toolbar icon.
3. Choose Document Catalog....
4. Enter query criteria in the Attached To: field for the application object you want to copy attachments from.
5. Choose Find.
6. Check one or more documents.
7. Choose the Attach (number of documents) button.
8. Save your work.

Uploading and Downloading Attachment Files
Steps to follow:
Select data-type file -> Browse -> OK/Cancel


To attach an existing file to an Oracle Applications record:
1. In an application window, query or enter a data record.
2. Choose the Attachment toolbar icon. The Attachments window opens.
3. Use the List of Values to choose a Category.
4. Optionally enter a Description.
5. Use the List of Values to choose a Data Type.
6. Use the Tab key to open the Upload window in a separate browser window.
7. You can either enter the path for the file you want to attach or use the Browse button to choose a file.
8. Choose OK and follow the instructions in the browser window.
9. Click on Yes in the Decision window as seen in the figure fig(g)


To open an uploaded attachment:

Choose the Open Document ... button

Attachments as seen from backend ( In Order-Management)

When you attach a document to an Order it can be done so at the header level or the line level. The tables/views which are related are

This is a view which has all the details pertaining to an attached document. It gets populated by the following tables :


The important columns of the table are:
1. SEQ_NUM: Gives the sequence of the attachment it follows like 10,20,30..
2. CATEGORY_DESCRIPTION: Gives the description of the category.
3. DOCUMENT_DESCRIPTION: Gives the unique description of the attachment.
4. FILE_NAME: Name of the attached file.
6. DOCUMENT_ID: Identifier.
6. PK1_VALUE: Header_id or Line_id depending on if it is a header level or line level attachment.
7. CATEGORY_ID: Unique identifier sourced from the FND_DOCUMENT_CATEGORIES_S sequence
8. FUNCTION_NAME: Name of the function used while attaching
9. FUNCTION_TYPE: Foreign key to either FND_FORM_FUNCTIONS of FND_FORM, depending on the value of FUNCTION_TYPE.

This table stores information relating a document to an application entity. Its important columns include:
DOCUMENT_ID: Document identifier
PK1_VALUE: Header_id or Line_id depending on if it is a header level or line level

This table stores information about those forms and form functions for which the attach-ment feature is enabled.
ATTACHMENT_FUNCTION_ID: Attachment function identifier
FUNCTION_NAME: Name of the function used while attaching
depending on the value of FUNCTION_TYPE.

This table stores language-independent information about a document
DOCUMENT_ID : Document identifier
CATEGORY_ID: Unique identifier sourced from the FND_DOCUMENT_CATEGORIES_S

FND_LOBS stores information about all LOBs managed by the Generic File Manager(GFM).Each row includes the file identifier, name, content-type, and actual data.
FILE_ID: Identifier that uniquely identifies the file
FILE_NAME: User's name for the file as provided during the uploading process
FILE_DATA: The uploaded data itself, which is a binary LOB

This is an API which can be used to add, copy and delete attachments it has procedures

Add_Attachment: Can be used to attach a new attachment to an order

It’s parameters are:
P_API_VERSION IN (Mandatory)
P_ENTITY_CODE IN (Mandatory)
P_ENTITY_ID IN (Mandatory)
P_DOCUMENT_DESC IN default null,
P_DOCUMENT_TEXT IN default null,
P_CATEGORY_ID IN default null,
P_DOCUMENT_ID IN default null,

Copy_Attachments : Can be used to attach an existing attachment to an

It’s parameters are:
P_ENTITY_CODE IN (Mandatory)
P_TO_ENTITY_ID IN (Mandatory)

Delete_Attachments: Can be used to delete an attachment of an order

It’s parameters are:
P_ENTITY_CODE IN (Mandatory)
P_ENTITY_ID IN (Mandatory)

Apply_Automatic_Attachments :

It’s parameters are:
P_INIT_MSG_LIST IN default fnd_api.g_false
P_ENTITY_CODE IN (Mandatory)
P_ENTITY_ID IN (Mandatory)

Important table information in Oracle


GL Tables




























Script to print the Oracle Apps Version Number

SELECT substr(a.application_short_name, 1, 5) code,
substr(t.application_name, 1, 50) application_name,
p.product_version version
FROM fnd_application a,
fnd_application_tl t,
fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = USERENV('LANG')

Script to display status of all the Concurrent Managers

SELECT DISTINCT concurrent_process_id "Concurrent Process ID",
pid "System Process ID", os_process_id "Oracle Process ID",
q.concurrent_queue_name "Concurrent Manager Name",
p.process_status_code "Status of Concurrent Manager",
) "Concurrent Manager Started at"
FROM fnd_concurrent_processes p,
fnd_concurrent_queues q,
WHERE q.application_id = queue_application_id
AND q.concurrent_queue_id = p.concurrent_queue_id
AND spid = os_process_id
AND process_status_code NOT IN ('K', 'S')
ORDER BY concurrent_process_id, os_process_id, q.concurrent_queue_name

Commit every X records for UPDATE/DELETE in a loop

Generally Huge updates/deletes causes rollback segment or data-files related errors.

Below is the code which is used to comming after every 1000 rows of update/delete to over come the problems. Place the code in below the update or delete statement in the procedure:

if mod(i, 1000)
dbms_output.put_line('Commit issued for rows up to: 'c1%rowcount);
end if;

Script to Encrypt and Decrypt using PL/SQL

function encrypt(i_password varchar2) return varchar2;
function decrypt(i_password varchar2) return varchar2;
show errors


-- key must be exactly 8 bytes long
c_encrypt_key varchar2(8) := 'key45678';

function encrypt (i_password varchar2) return varchar2 is
v_encrypted_val varchar2(38);
v_data varchar2(38);
-- Input data must have a length divisible by eight
v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));

input_string => v_data,
key_string => c_encrypt_key,
encrypted_string => v_encrypted_val);
return v_encrypted_val;
end encrypt;

function decrypt (i_password varchar2) return varchar2 is
v_decrypted_val varchar2(38);
input_string => i_password,
key_string => c_encrypt_key,
decrypted_string => v_decrypted_val);
return v_decrypted_val;
end decrypt;

show errors

-- Test if it is working...
select xx_password.encrypt('PASSWORD1') from dual;
select xx_password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select xx_password.encrypt('PSW2') from dual;
select xx_password.decrypt(app_password.encrypt('PSW2')) from dual;

Monday, September 1, 2008

Apps 11i Keys shortcut

F4 Exit
F5 Clear Field
F6 Clear Record
F7 Clear Block
F8 Clear Form
F11 Query Enter
F12 Count Query
Ctrl + S Save
Ctrl + L List of Values
Ctrl + F11 Query Run
Ctrl + E Edit
Ctrl + Up Delete Record
Ctrl + Down Insert Record
Ctrl + P Print
Ctrl + U Update Record
Ctrl + B Block Menu
Ctrl + K Display list of Keys
Shift + F5 Duplicate Field
Shift + F6 Duplicate Record
Shift + F8 Next Set of Records (Same as Page Down)
Shift + Page Down Next Block
Shift + Tab Previous Field
Shift + Page Up Previous Block
Shift + Ctrl + E Display Error
Page Down Scroll Down (Same as Shift + F8)
Page Up Scroll Up
Tab Next Field
Down Arrow Next Record
Up Arrow Previous Record
Return Return