Registering SQL*Loader as a Concurrent Program

Registering SQL*Loader as a Concurrent Program

The following steps will describe the process to register a SQL*Loader program as a Concurrent Program in Oracle Apps.

Step 1]

Create the SQL*Loader Control and Data file and place them in Server(ex: $CUSTOM_TOP/bin). Create or check the interface table structures in the backend.

Control file: test.ctl

Data file: test.dat

(deptno NUMBER(2) NOT NULL,
 dname VARCHAR2(14),
 loc VARCHAR2(13));


Step 2]

Go to Application Developer > Concurrent > Executables. Define a Concurrent Program Executable. Choose the Execution Method as SQL*Loader and give the Execution File Name as the name of the SQL*Loader control file. Save your work.

Step 3]

Go to Application Developer > Concurrent > Program. Define the Concurrent Program. Attach the executable defined above.

Step 4]

Go to parameters of the concurrent program. Create a parameter to take the server path of the data file. You can also place the default value.

Step 5]

Attach the Concurrent program to a Responsibility through a Request Group.

Step 6]

Go to that Responsibility and Run the Concurrent Program. If successful check the output file that have all data uploading information.

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table TESTDEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
—————————— ———- —– —- —- ———————
DEPTNO                               FIRST     *   ,  O(“) CHARACTER           
DNAME                                NEXT     *   ,  O(“) CHARACTER           
LOC                                     NEXT     *   ,  O(“) CHARACTER           

  7 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Space allocated for bind array:49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:           0
Total logical records read:               7
Total logical records rejected:          0
Total logical records discarded:        0

Run began on Thu Aug 12 09:41:55 2010
Run ended on Thu Aug 12 09:41:56 2010

Elapsed time was:      00:00:00.11
CPU time was:           00:00:00.01

Step 7]

Check in the backend whether the tables got updated or not.



The Bad and Discard files will be created in /conc/out file of the server.


SQL*Loader Basics

SQL*Loader Basics

SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file into one or more database tables. It has a powerful data parsing engine that supports data present in any format in the data files. It can load data from multiple datafiles during the same load session as well as can load data into multiple tables during the same load session. SQL*Loader can manipulate the data before loading it, using SQL functions.

SQL*Loader’s Capabilities:

  • SQL*Loader can read from multiple input files in a single load session.
  • SQL*Loader can handle files with fixed-length records, variable-length records, and stream-oriented data.
  • SQL*Loader supports a number of different datatypes, including text, numeric, zoned decimal, packed decimal, and various machine-specific binary types.
  • Not only can SQL*Loader read from multiple input files, but it can load that data into several different database tables, all in the same load session.
  • SQL*Loader allows you to use Oracle’s built-in SQL functions to manipulate the data being read from the input file.
  • SQL*Loader includes functionality for dealing with whitespace, delimiters, and null data.
  • In addition to standard relational tables, SQL*Loader can load data into object tables, varying arrays (VARRAYs), and nested tables.
  • SQL*Loader can load data into large object (LOB) columns.
  • SQL*Loader can handle character set translation between the input data file and the database.

An evening at Bushi Dam,Lonavala

An evening at Bushi Dam,Lonavala

Bushi Dam is located a few kilometers from Lonavala and is a very popular weekend hangout for Pune’ites. The pictures below are taken by Samsung mobile phone when I with my few friends reached there in the evening.

Trading Community Architecture (TCA) in Oracle Apps

Trading Community Architecture (TCA) in Oracle Apps


Before TCA:

  • There are multiple customer definitions across the enterprise.
  • It was very difficult to track current and historical information about the customers.
  • There was a lack of support for mixed business.
  • It was quite tough to understand relationships between customers and others (suppliers, partners, competitors).

Customers: More important than anything else!

In any business, Customers and their data are always important. More than that what is important is to understand who your customer interacts with inside and outside the enterprise.

What is Trading Community?

The summation of all entities, inclusive of partners, suppliers, and competitors, that are related to your customers is called a Trading Community.

Trading Community Architecture:

Trading Community Architecture is the implementation of technology and applications to allow users to create and maintain relationships among entities. It is a way to understand who your customer interacts with inside and outside the enterprise.

It’s Main Purpose:

  • Create a central repository for the entire E-Business Suite to store information relating to all members of a trading community versus separate tables for each member-Prospects, Customers, Contacts, Employees, Partners, Distributors, Suppliers, Banks, etc.
  • Record complex business relationships between Trading Community entities (including 3rd party relationships).
  • Support all business models, industries, and geographies.

TCA Data Model Components:

1] Party:

It represents any entity that can enter into business relationships with your organization – Organization, Person, or Group.

  • Person – A unique individual (dead or alive) of interest to the user.
  • Organization  – A legal entity recognized by some government authority.
  • Group  – A combination of two or more people, organizations or groups.

2] Party Relationship:

It is a binary relationship between two parties such as a partnership.

  • Has a Role – Specifies the nature of the relationship between parties (e.g., member of, contact at, married to).
  • Indicates the Nature of the relationship – hierarchy or matrix.
  • Indicates the Direction of the relationship – superior – subordinate.
  • Can become a Party – a Relationship becomes a party in itself.

The relationship model enables you to:

  • Understand the complex relationships among members of your trading community
  • Use this information to make better business decisions

3] Location:

A Location is a point in geographical space described by a street address. In previous releases of Oracle, there was a risk of some data redundancy if more than one customer shared the same site or location. The new model eliminates this redundancy.

  • Any number of location types can be defined. (e.g., bill-to, ship-to, mail-to).
  • There is no duplication of an address.
  • It is possible to maintain Customer History per address.
  • It is also possible to maintain Important Install Base info.

4] Party Site:

It links a Party with a Location

  • Describes the usage of that Location for the Party  (e.g., mailing address, billing address, home address, etc.).
  • Allows Parties to be associated to one or more Locations and any one Location to be associated with Parties.

5] Contact:

A Contact is a person in the context of an organization, modeled as a relationship between an organization and a person or between two people, (this can be either a party contact or an account contact).

6] Contact Point:

A Contact Point is a means of contacting a party, for example, a phone number, e-mail address, or fax number.

This can be applied to:

  • A Party (person, organization, group or relationship)
  • A Site or Location
  • A Party at a Site or Location

An entity may have one or more Contact Points.

7] Customer Account:

A Customer Account represents the business (selling) relationship that a company deploying Oracle Applications has with a party.

  • Stores details about the Financial relationship between a Party and your business.
  • A Party may have one or more Customer Accounts.

8] Customer Account Site:

A Customer Account Site is a party site that is used by a customer account, for example, for billing or shipping purposes.

9] Customer Account Contacts:

A party contact that is used as a means of contacting the customer regarding his/her account.

Parties vs. Accounts

  • From an application perspective, one of the most important things to understand about the TCA model is that the concept of “customer” is separated into two layers: The Party layer and the Account layer. 
  • When CRM applications refer to “Customer” they are referring to the Party Layer.
  • On the other hand, when ERP applications refer to “Customer” they are referring to the Account Layer. 

New Trading Entities in R12

Below are the new entities that are merged in TCA architecture in R12.

  • Banks & Bank Branches
  • Suppliers
  • Legal Entity

 Know More: 

What Happened to My Customers? (Trading Community Architecture) by Jeannie Dobney

Interfaces in Oracle Application: An Introduction

Interfaces in Oracle Application: An Introduction


What are Interfaces?

  • Interfaces are used in Oracle Applications to integrate external systems and Data Conversion.
  • The interfaces are mainly used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.
  • Used extensively at the time of Data Conversion from legacy/ old systems to a fresh implementation of Oracle Applications.
  • Used also at regular intervals when data transfer is from other live systems if the systems are not defined in Oracle Applications implementation.
  • Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.

Types of Interfaces

There are two major types of Interfaces:

  • Inbound Interface : These interfaces are used to transfer data from external systems to Oracle Applications.
  • Outbound Interface :  These interfaces are used to transfer data from Oracle Applications to external systems.

Two other distinctions of Interfaces:

  • Open Interface: If the interface logic is provided by Oracle Applications, it is called an Open Interface.
  • Custom Interface: If the interface logic needs to be developed by the implementation team, it is called a Custom Interface.

Interface Components

Open Interface Logic

  • First the data from the source application is loaded into a database table (called Interface table).
  • Then the provided validation program logic validates the records whether they are correct or not .
  • If the validation fails, the errors are transferred into another table (called Error Table).
  • If the validation succeeds, the correct records are transferred through a process into the destination application table.

Components of an Interface

a] Source Application:

You obtain data from a source application to pass on to a destination application for further processing and/or storage.

b] Source Data Issues:

Type of file, Size, Frequency of upload, Record Length (Variable or fixed), Delimiter, Datatype for each field, Any unwanted data, Naming convention and uniqueness of file, Location of the file, Access on the file.

c] Destination Application:

You send data to a destination application so that the application can perform further processing and/or storage.

d] Interface Table:

For inbound interfaces, the interface table is the intermediary table where the data from your source application temporarily resides until it is validated and processed into the destination application.

e] Identifier columns:

Uniquely identify rows in the interface table provide foreign key reference to both the source and destination applications.

f] Control Columns:

  • Control columns track the status of each row in the interface table, as it is inserted, validated, rejected, processed, and ultimately deleted.
  • WHO columns are also control columns.

g] Data Columns:

  • Stores the data that is being converted.
  • Required columns store the minimum information needed by the destination application to successfully process the interface row.

h] Derived Columns:

Derived columns are created by the destination application from information in the required columns.

i] Optional Columns:

Optional columns are not necessarily required by the destination application, but can be used by the destination application for additional value-added functionality beyond the basics.

j] Error Table:

  • For inbound interfaces, the errors table stores all errors found by the validation and processing functions.
  • In some cases, the errors table is a child of the interface table. This allows each row in the interface table to have many errors, so that you can easily manage multiple errors at once.
  • In other cases, the errors are stored in a column within the interface table, which requires you to fix each error independently.

Developing an Interface

1] Identification:

Find out if there exists an Open Interface to carry out the functionality.

2] Creation of Pre-Interface table ( staging Table):

A table in the format of the data file which can be pruned to load as clean a data into the Interface table.

3] Load data into Pre-Interface table:

SQL*LOADER can be used to load the flat file into the pre-interface table.

4] Validate data in the Pre-Interface table:

Basic validation of the data loaded into the Pre-Interface table can be carried out like:

  • For checking NULL values in required columns
  • Checking for Foreign Key and Quick Code values.
  • Duplication Validation
  • Business Rule validation

5] Mapping the values:

Generated fields in Oracle Applications can be mapped in this step to either default values or sequences.

6] Load data into Interface table:

  • Once the data is as clean as you can get it, the data can be inserted into the Interface table.
  • At such a time, certain columns, which are necessary in Applications but not found in legacy system, need to be populated accordingly like WHO columns.

7] Run the interface program

8] Check for Errors

9] Report on the Interface

Thanks a lot to all the readers..1000 visits crossed!

Thanks a lot to all the readers

Today the Blog has crossed 1000 total visits. With readers from 23 different countries the blog  made this in a little span of 70 days. Today the blog has 44 articles in 14 different categories.   

Visited Countries 0n 5th Aug 2010:


I will try to put various interesting articles in different categories in the coming days. Please don’t forget to put your valuable comments.

Have a nice day!!

Steps to Create Report in Oracle Reports Builder 10g

Steps to Create Report in Oracle Reports Builder 10g

In Oracle Reports, you have two options for building a paper report. The first one is to use the wizards and editors in Reports Builder. The second one is to define the data model and/or layout for your paper report in XML.

Here we will discuss the steps to build a paper report using the Report Wizard.

Step 1: Invoking Reports Builder and the Report Wizard

When you invoke Reports Builder, the Welcome dialog box gives you the option of using the Wizard to build a new report. The Report Wizard provides an easy step-by-step interface to create a new report.

The Report Wizard opens with a Welcome page. To suppress this page, clear the “Display at startup” check box. You can reinstate this page in the same way as the Welcome dialog box in Reports Builder; select the Wizard tab in the Preferences dialog box and then select Report Wizard Welcome Page.

Each page of the Report Wizard asks you for information to help you create your initial report. Step through the wizard pages, selecting Next and Back, until you are satisfied with the initial information that you have entered. On the last page, select Finish.

Welcome to Reports Builder

Report Wizard

Step 2: Choosing the Layout Type

Here you have to specify the type of layout you want the Wizard to generate. The available options are:

  • Web and Paper Layout 
  • Web Layout only 
  • Paper Layout only

Report Layout Type

 Step 3: Choosing a Report Style

This page of the Report Wizard shows the various styles of reports. Select Tabular and then click Next.

Report Style

Step 4: Selecting the Data Source Type

Next, you have to define the data source type for your report. Through the implementation of the Pluggable Data Source (PDS) feature in Oracle Reports, the data for your report can come from any source you choose. Reports Builder provides interface definitions that act as a translator between Reports Builder and a PDS by redefining Reports Builder’s requests in terms your data source uses.

Oracle Express Server, OLAP, JDBC, Text and XML pluggable data sources are shipped with Oracle Reports. You can also define your own data source.

Data Source Type

Step 5: Building a Query using Query Builder

Building your query with the Query Builder GUI saves you time and increases the ease of use for developers not familiar with building SQL statements or with the application tables.

To build a query using Query Builder:

  1. Select Query Builder from the Query page in the Report Wizard.
  2. Enter your username, password, and alias in the Connect dialog box that appears if you have not already connected to the database. 
  3. Select the data tables to build the query. 
  4. Click Include. The tables appear in the selection area. 
  5. Click Close to close the Select Data Tables window. 
  6. In each table, double-click the column names that you want in the query, or use the check boxes. To select all columns, double-click the Table title bar.
  7. Click OK.

Query Builder copies the query syntax into the Report Wizard. You can modify the query by reentering Query Builder or by modifying the SQL query statement text.

Note: If you prefer to write your own SQL statement, enter the syntax directly in the SQL query statement area of the Query page. Alternatively, you can import the contents of a file by clicking Import SQL Query.

Query Builder

Step 6: Selecting Displayed Fields

In the Field page, select each field from the Available Fields list and click >. The selected fields move to the Displayed Fields list. To display all fields, click >>.

You can alter the sequence of displayed fields by dragging one field above or below another in the list. The sequence of fields in this list determines how the fields appear in the report output. In a tabular report, the fields appear in sequence from left to right across the page.

Fields that remain in the Available Fields list are available for you to reference in your report definition as hidden fields or in PL/SQL trigger code.

In the report output, the user sees only those fields that you transfer to the Displayed Fields list.

Displayed Fields

Step 7: Totals and Labels

In the next two pages of the Report Wizard, you can create totals based on any of the displayed fields and modify the labels and width of the displayed fields.

Totals: Standard SQL aggregate functions are provided for creating totals in your report.

Total Fields

Labels: The field label is displayed on one or more lines in the report output. In a tabular report, the labels appear above the field values.

  • If the initial label is wider than the field, Reports Builder allows enough space for the label, or displays it on multiple lines.
  • If you increase the number of characters in the label text in the reentrant Wizard, the label can appear truncated in the report output.


Step 8: Selecting a Report Template

Report Templates enforce corporate standards as well as create professional-looking paper reports easily.

Select a template from the list of predefined template names. In a template, the fonts, styles, and colors are already selected for designated objects. A variety of templates are available with the standard Reports installation.

To select a predefined template:

  1. Select the Predefined Template option button, if it is not already selected.
  2. Select a template from the Template list.
  3. Click Finish.

Report Template

Step 9: Viewing the Paper Report Output

When you finish creating your report in the Report Wizard, the output appears in the Paper Design view of the Report Editor.

Magnifying the Output

The Paper Design view contains a Magnify tool in the vertical toolbar. This provides a view of the area of layout you want to see. You can also use the View menu to magnify or reduce the size of the output. Select View > Zoom to see your options.

Viewing Different Pages

The Paper Design toolbar contains four buttons, and the specific page option, with which you can scroll through the pages of your report.

Report Output

Step 10: Saving the Report Definition

Remember to save the report frequently by selecting Save in the toolbar, or by using the File > Save menu option. The recommended format for storing paper reports is with an .rdf extension.

If you want to make a copy of the report definition in a different filename, use the menu option File > Save As. There is no toolbar button for the Save As option.

Source: Oracle

Adding and Updating Customer Accounts in R12

Adding and Updating Customer Accounts in R12


Use the Customer Overview page in R12 to manage details of your existing customers. 

This page has five subtabs:

  • Accounts.
  • Profile
  • Communication
  • Party Relationships
  • Tax Profile



Use the Accounts subtab of the Customer Overview page to view, add, and update the accounts of existing customers.

  • view and update an account:
  • view and update an account site
  • create an account
  • create an account site

Customer Profiles:

Use the Profile subtab of the Customer Overview page to add and update the profiles of existing customers.

Tax Registration Number

  • The customer’s unique taxpayer registration number, also known as the VAT number.
  • Oracle Receivables prints this number on customer invoices.
  • Receivables provides country-specific validation of the tax registration number.

Credit Classification

  • Displays the credit classification for a particular profile class.

Credit Analyst

  • Indicates who is responsible for monitoring the creditworthiness of the account and for assisting in the resolution of credit-related issues.

Review Cycle

  • Specifies how often to review the credit status of the customer account. For example, you can specify that the creditworthiness of the account is reviewed each month.

Customer Communication Information:

Use the Communication subtab of the Customer Overview page to enter and update contact information, such as phone numbers, e-mail addresses, and URLs, of existing customers.

Phone Numbers:

  • FAX

Email Address:


Party Relationships:

Use the Party Relationship subtab of the Customer Overview page to define, view, and update relationships among existing customers (parties), using predefined relationship types and roles.

Note: Relationship types and roles are defined using Oracle Trading Community Architecture Relationship Manager.

Relationship Role: Describes the role that an entity plays in a relationship.

Customer Tax Profiles:

Use the Tax Profile subtab of the Customer Overview page to set up, view, and update tax profiles for your customers.

AR Tables:A Diagrammatic Relation

AR Tables:A Diagrammatic Relation


A Diagrammatic Relation between AR Tables

Click in the photo to have a better view.