Three-Tier Architecture

Client Tier:
Application Tier:
Data Base Tier:

Concept of E-Business Suite Environments

Master environment This environment is used for the main configuration
setup of the system. Although it does not contain any transactional data, it
is important that the master environment is managed by a very strict change
control as this environment contains production (master) setup.

Development environment This is where developers design and build
extensions and customizations. The developers are usually granted very
powerful access rights for both E-Business Suite and the operating system
that hosts the system. For instance, the developers may be granted System
Administrator or Application Developer responsibilities.

Testing environment (also known as UAT) Developers usually do not
have an APPS database schema password to this environment. This is where
users sign off on customizations and configuration changes.

Deployment environment Once the users have finished their User
Acceptance Testing on a UAT instance, patches/scripts can then be
promoted to a Deployment instance for final checks. Effectively, applying
patches on a Deployment instance is a dry run before applying code
changes to a Production instance.

Patching environment Oracle delivers their code changes, bug fixes,
and product updates through patches. The patches can be downloaded
from the Oracle Support website and applied by E-Business Suite database
administrators (Apps DBAs). Apps DBAs can use the patching environment
to perform sanity checks for patches delivered by Oracle.

Support environment If a user reports an issue on the production system, it is
a good idea to reproduce the problem on a copy of the production system. Such
copied instances are referred to as clones. The support environment is exclusive
to the support staff, where developers do not make changes directly. This
environment is usually the most frequently cloned environment in cases where
E-Business Suite implementation is running a live production instance. Frequent
cloning helps the E-Business Suite support staff to reproduce production issues.

CRP environment The conference room pilot environment is where
someone, usually an implementation team, gets buy-in to their product offering
from the wider user and business community during an implementation. This
environment is usually used for sign-off during new implementations.

Migration environment For new implementations of Oracle Applications,
developers are tasked with migration of data from the old legacy systems
into Oracle E-Business Suite. This is where repeated data migration can take
place before the migration code gets frozen and ready for user testing.

Production environment This is where the business runs its day-to-day
operations.

Generally, E-Business Suite technical developers shouldn't be too concerned
about the variety of environments, as their focus is predominantly concentrated on
the development environment. In very simple terms, the life cycle of extensions and
customization could be summarized as follows: the developer performs the
development and unit testing in the development environment, and the code gets
promoted to the testing environment. Following successful testing, the changes are
applied to the production environment.

Oracle Apps(R12) Technical Architecture

*

UTL_FILE

In Oracle PL/SQL, UTL_FILE is an Oracle supplied package which is used for file operations (read and write) in conjunction with the underlying operating system. UTL_FILE works for both server and client machine systems. A directory has to be created on the server, which points to the target file. For the files located on the server machine, the actual path can be given while creating the directory. For the files which are located on the client machines, however, the relative path is required along with the client machine name. In addition, the relative file path must be in shared mode with read and write access for the required users. A DBA must create the directory and then grant Read/Write access to the required users.

In earlier versions of Oracle, the parameter UTL_FILE_DIR was used to specify the file location path. This parameter is now deprecated and directory creation method is recommended.

UTL_FILE subprograms are listed as below.

• FCLOSE - Closes a file.
  
• FCLOSE_ALL - Closes all open file handles
  
• FCOPY - Copies a contiguous portion of a file to a newly created file. Takes the following parameters:
  src_location, src_filename, dest_location, dest_filename, start_line, and end_line.
  
• FFLUSH - Physically writes all pending output to a file.
  
• FGETATTR - Reads and returns the attributes of a disk file. Returns the following items about the file:
  location, filename, fexists (a boolean), file_length (in bytes), and block_size.
  The location must be either an existing directory on the server AND be in the utl_file_dir parameter, or it may be a directory.
  
• FGETPOS - Returns the current relative offset position within a file, in bytes as a binary_integer.
  
• FOPEN - Opens a file for input or output. FOPEN takes the following parameters:
  the file location, the filename, the open_mode and the max_linesize.
  
• FOPEN_NCHAR - Opens a file in Unicode for input or output.
  
• FREMOVE - Deletes a disk file, assuming that you have sufficient privileges. Takes the following parameters:
  location and filename
  
• FRENAME - Renames an existing file to a new name, similar to the UNIX mv function. FRENAME takes the following parameters:
  the src_location, the src_filename, the dest_location, the dest_filename, and overwrite (a boolean). The overwrite parameter determines whether or not the file, if it already exists, will be overwritten.
  
• FSEEK - Adjusts the file pointer forward or backward within the file by the number of bytes specified. FSEEK takes the following parameters:
  the file, the absolute_offset (a binary_integer), and the relative_offset (a binary_integer).
  
• GET_LINE - Reads text from an open file. GET_LINE takes the following parameters:
  the file (record), buffer (varchar2), and len (a binary_integer).
  
• GET_LINE_NCHAR - Reads text in Unicode from an open file. GET_LINE_NCHAR takes the following parameters:
  the file (record), buffer (nvarchar2), and len (a binary_integer).
  
• GET_RAW - Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read. GET_RAW takes the following parameters:
  file (record), buffer (raw), len (a binary_integer)
  
• IS_OPEN - Determines if a file handle refers to an open file.
  
• NEW_LINE - Writes one or more operating system-specific line terminators to a file. NEW_LINE takes the following parameters:
  file (record), lines (a binary_integer).
  
• PUT - Writes a string to a file. PUT takes the following parameters:
  file (record), buffer (a varchar2).
  
• PUT_LINE - Writes a line to a file, and also appends an operating system-specific line terminator. If a line was already written, it starts the line with CR/LF. This implies that the file, when being written into, does not end with CR/LF. In Oracle 9i the maximum line length that can be written is 32K. PUT_LINE takes the following parameters:
  file (record), buffer (a varchar2).
  
• PUT_LINE_NCHAR - Writes a Unicode line to a file. PUT_LINE takes the following parameters:
  file (record), buffer (a nvarchar2), autoflush (a boolean).
  
• PUT_NCHAR - Writes a Unicode string to a file. PUT takes the following parameters:
  file (record), buffer (an nvarchar2).
  
• PUTF - A PUT procedure with formatting.
  
• PUTF_NCHAR - A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting.
  
• PUT_RAW - Accepts as input a RAW data value and writes the value to the output buffer.

Example Usage:

In the below example, SYSDBA creates a directory MYDIR and grants R/W access to the user SCOTT. The user then creates a text file in the directory and writes a text into it.

--------------SYSDBA-----------------------

SQL> CREATE DIRECTORY MYDIR AS 'C:\TESTLOC';

Directory created.



SQL> GRANT READ, WRITE ON DIRECTORY MYDIR TO SCOTT

Grant succeeded.



--------------SCOTT-------------------------

 

DECLARE

  L_HANDLER UTL_FILE.FILE_TYPE;

BEGIN

  L_HANDLER := UTL_FILE.FOPEN('MYDIR', 'SYS.txt', 'W');

  UTL_FILE.PUTF(L_HANDLER, 'UTL_FILE write mode demonstration');

  UTL_FILE.FCLOSE(L_HANDLER);

END;



PL/SQL procedure successfully completed.

Oracle Apps explained through real time example

  The below example explains a few of the important terms and concepts used in the Oracle E-Business Suite. This would be a good starting point for the beginners to better understand the concepts behind Oracle Applications.

Say Harry is the owner of a wholesale fruit shop. He buys various fruits like apples, oranges, mango's and grapes etc from farmers directly and sells them to retail shop owners and also to the direct customers.


The farmers are referred to as VENDORS/SUPPLIERS in Oracle Applications.

Harry keeps track of all his vendors’ information like addresses, bank account and the amount he owes to them for the fruits that he bought etc, in a book named PAYABLES.

Harry gets an order from a retail shop owner of Fruit Mart, for a shipment of 11 bags of apples, 25 bags of oranges and 32 kgs of grapes. In Oracle Apps, bags and kgs are referred to as
UOM (unit of measure), Fruit Mart is called CUSTOMER and the order is referred to as SALES ORDER. Harry maintains a book called ORDER MANAGEMENT where he writes down all the details of the SALES ORDERS that he gets from his customers.

Say the fruits have been shipped to the customer Fruit Mart. Harry now sends him the details like cost of each bag/fruit, the total amount that the customer has to pay etc on a piece of paper which is called INVOICE / TRANSACTION.

Once the INVOICE has been sent over, the customer then validates this against the actual quantity of fruits that he received and will process the payments accordingly. The invoice amount could be paid as a single amount or could be paid in
installments. Harry’s customer, Fruit Mart pays him in installments (partial payments). So Harry has to make a note of the details like date received, amount received, amount remaining, amount received for what goods/shipments/invoice etc,
when Harry receives the payments. This detail is called

RECEIPT, which will be compared to the invoice by Harry to find how much Fruit

At the end of every month, Harry prepares a balance sheet in a book called GENERAL LEDGER to determine how much profit/loss he

got and keeps track of the money going out and going in.

As the business grows, it becomes impossible to record everything on a paper. To make everybody’s life easier, we have very good tools in the market, 

which help the business men to keep track of everything. One such tool is Oracle E-Business Suite.

Oracle Applications is not a single application, but is a collection of integrated applications. Each application is referred to as a module and has it own functionality trying to serve a business purpose.

Few of the modules are Purchasing, Accounts Payables, Accounts Receivables, Inventory, Order Management, Human Resources, General Ledger, Fixed Assets etc.

Here is a high level business use of various modules:

Oracle Purchasing 

handles all the requisitions and purchase orders to the vendors.

Oracle Accounts Payables 

handles all the payments to the vendors.

Oracle Inventory

deals with the items you maintain in stock, warehouse etc.

Order Management 

helps you collect all the information that your customers order.

Oracle Receivables 

help you collect the money

for the orders that are delivered to the customers.

Oracle Human Resources 

helps maintain the Employee information, helps run paychecks etc.

Oracle General Ledger 

receives information from all the different transaction modules or sub ledgers and summarizes them in order to help you create profit and loss statements, reports for paying Taxes etc. For Example: when you pay your employees that payment is reported back to General Ledgers as cost i.e money going out, when you purchase inventory items and the information is transferred to GL as money going out, and so is the case when you pay your vendors. Similarly when you receive items into your inventory, it is transferred to GL as money coming in, when your customer sends payment

Terminology often used in Oracle Applications:

1.    Invoice

2.    Receipt

3.    Customer

4.    Vendor

5.    Buyer

6.    Supplier

7.    Purchase Order

8.    Requisition

9.    ACH: Account Clearance House

10.    Sales Order

11.    Pack Slip

12.    Pick Slip

13.    Drop Ship

14.    Back Order

15.    ASN: Advance Shipping Notice

16.    ASBN: Advance Shipping Billing Notice

17.    ATP: Available to Promise

18.    Lot/Serial Number

19.    DFF: Descriptive Flex Fields

20.    KFF: Key Flex Fields

21.    Value Sets

22.    Organization 

23.    Business Unit

24.    Multi Org

25.    Folders

26.    WHO Columns

27.    Oracle Reports

28.    Oracle Form

29.    Workflow Builder

30.    Toad

31.    SQL Developer

32.    SQL Navigator

33.    Discoverer Reports

34.    XML/BI Publisher

35.    ADI: Application Desktop Integrator

36.    Winscp

37.    Putty 

SQL Loader Part 2

SQL LOADER is a very powerful tool that lets you load data from a delimited or position based data file into Oracle tables. We have received many questions regarding SQL LOADER features from many users. Here is the brief explanation on the same. 

Please note that the basic knowledge of SQL LOADER is required to understand this article.

This article covers the below topics:

1. Load multiple data files into a single table

2. Load a single data file into multiple tables

3. Skip a column while loading using “FILLER” and Load field in the delimited data file into two different columns in a table using “POSITION”

4. Usage of BOUNDFILLER

5. Load the same record twice into a single table

6. Using WHEN to selectively load the records into the table

7. Run SQLLDR from SQL PLUS

8. Default path for Discard, bad and log files

1) Load multiple files into a single table:

SQL LOADER lets you load multiple data files at once into a single table. But all the data files should be of the same format.

Here is a working example:

Say you have a table named EMP which has the below structure:

Column	Data Type
emp_num	Number
emp_name	Varchar2(25)
department_num	Number
department_name	Varchar2(25)

You are trying to load the below comma delimited data files named eg.dat and eg1.dat:

eg.dat:

7369,SMITH,7902,Accounting

7499,ALLEN,7698,Sales

7521,WARD,7698,Accounting

7566,JONES,7839,Sales

7654,MARTIN,7698,Accounting

eg1.dat:

1234,Tom,2345,Accounting

3456,Berry,8976,Accounting

The Control file should be built as below:

LOAD DATA

INFILE ‘eg.dat’ — File 1

INFILE ‘eg1.dat’ — File 2

APPEND

INTO TABLE emp

FIELDS TERMINATED BY “,”

( emp_num, emp_name, department_num, department_name )

2) Load a single file into multiple tables:

SQL Loader lets you load a single data file into multiple tables using “INTO TABLE” clause.

Here is a working example:

Say you have two tables named EMP and DEPT which have the below structure:

Table	Column	Data Type
EMP	emp_num	Number
EMP	emp_name	Varchar2(25)
DEPT	department_num	Number
DEPT	department_name	Varchar2(25)

You are trying to load the below comma delimited data file named eg.dat which has columns Emp_num and emp_name that need to be loaded into table EMP and columns department_num and department_name that need to be loaded into table DEPT using a single CTL file here.

eg.dat:

7369,SMITH,7902,Accounting

7499,ALLEN,7698,Sales

7521,WARD,7698,Accounting

7566,JONES,7839,Sales

7654,MARTIN,7698,Accounting

The Control file should be built as below:

LOAD DATA

INFILE ‘eg.dat’

APPEND

INTO TABLE emp

FIELDS TERMINATED BY “,”

( emp_num, emp_name )

INTO TABLE dept

FIELDS TERMINATED BY “,”

(department_num, department_name)

You can further use WHEN clause to selectively load the records into the tables which will be explained later in this article.

3) Skip a column while loading using “FILLER” and Load field in the delimited data file into two different columns in a table using “POSITION”

SQL LOADER lets to skip unwanted fields in the data file by using the “FILLER” clause. Filler was introduced in Oracle 8i.

SQL LOADER also lets you load the same field into two different columns of the table.

If the data file is position based, loading the same field into two different columns is pretty straight forward. You can use Position (start_pos:end_pos) keyword

If the data file is a delimited file and it has a header included in it, then this can be achieved by referring the field preceded with “:” eg description “(:emp_name)”.

If the data file is delimited file without a header included in it, Position (start_pos:end_pos) or “(:field)” will not work. This can be achieved using POSITION (1) clause which takes you to the beginning of the record.

Here is a Working Example:

The requirement here is to load the field emp_name in the data field into two columns – emp_name and description of the table EMP. Here is the Working Example:

Say you have a table named EMP which has the below structure:

Column	Data Type
emp_num	Number
emp_name	Varchar2(25)
description	Varchar2(25)
department_num	Number
department_name	Varchar2(25)

You are trying to load the below comma delimited data file named eg.dat which has 4 fields that need to be loaded into 5 columns of the table EMP.

eg.dat:

7369,SMITH,7902,Accounting

7499,ALLEN,7698,Sales

7521,WARD,7698,Accounting

7566,JONES,7839,Sales

7654,MARTIN,7698,Accounting

Control File:

LOAD DATA

INFILE ‘eg.dat’

APPEND

INTO TABLE emp

FIELDS TERMINATED BY “,”

(emp_num,

emp_name,

desc_skip FILLER POSITION(1),

description,

department_num,

department_name)

Explanation on how SQL LOADER processes the above CTL file:

 The first field in the data file is loaded into column emp_num of table EMP

 The second field in the data file is loaded into column emp_name of table EMP

 The field desc_skip enables SQL LOADER to start scanning the same record it is at from the beginning because of the clause POSITION(1) . SQL LOADER again reads the first delimited field and skips it as directed by “FILLER” keyword.

 Now SQL LOADER reads the second field again and loads it into description column of the table EMP.

 SQL LOADER then reads the third field in the data file and loads into column department_num of table EMP

 Finally the fourth field is loaded into column department_name of table EMP.

4) Usage of BOUNDFILLER

BOUNDFILLER is available with Oracle 9i and above and can be used if the skipped column’s value will be required later again.

Here is an example:

The requirement is to load first two fields concatenated with the third field as emp_num into table emp and Fourth field as Emp_name

Data File:

1,15,7369,SMITH

1,15,7499,ALLEN

1,15,7521,WARD

1,18,7566,JONES

1,20,7654,MARTIN

The requirement can be achieved using the below Control File:

LOAD DATA

INFILE ‘C:\eg.dat’

APPEND

INTO TABLE EMP

FIELDS TERMINATED BY “,”

(

Rec_skip BOUNDFILLER,

tmp_skip BOUNDFILLER,

Emp_num “(:Rec_skip||:tmp_skip||:emp_num)”,

Emp_name

)

5) Load the same record twice into a single table:

SQL Loader lets you load record twice using POSITION clause but you have to take into account whether the constraints defined on the table allow you to insert duplicate rows.

Below is the Control file:

LOAD DATA

INFILE ‘eg.dat’

APPEND

INTO TABLE emp

FIELDS TERMINATED BY “,”

( emp_num, emp_name, department_num, department_name )

INTO TABLE emp

FIELDS TERMINATED BY “,”

(emp_num POSITION(1),emp_name,department_num,department_name)

SQL LOADER processes the above control file this way:

First “INTO TABLE” clause loads the 4 fields specified in the first line of the data file into the respective columns (emp_num, emp_name, department_num, department_name)

Field scanning does not start over from the beginning of the record when SQL LOADER encounters the second INTO TABLE clause in the CTL file. Instead, scanning continues where it left off. Statement “emp_num POSITION(1)” in the CTL file forces the SQL LOADER to read the same record from the beginning and loads the first field in the data file into emp_num column again. The remaining fields in the first record of the data file are again loaded into respective columns emp_name, department_num, department_name. Thus the same record can be loaded multiple times into the same table using “INTO TABLE” clause.

6) Using WHEN to selectively load the records into the table

WHEN clause can be used to direct SQL LOADER to load the record only when the condition specified in the WHEN clause is TRUE. WHEN statement can have any number of comparisons preceded by AND. SQL*Loader does not allow the use of OR in the WHEN clause.

Here is a working example which illustrates how to load the records into 2 tables EMP and DEPT based on the record type specified in the data file.

The below is delimited data file eg.dat which has the first field as the record type. The requirement here is to load all the records with record type = 1 into table EMP and all the records with record type = 2 into table DEPT and record with record type =3 which happens to be the trailer record should not be loaded.

1,7369,SMITH

2,7902,Accounting

1,7499,ALLEN

2,7698,Sales

1,7521,WARD

2,7698,Accounting

1,7566,JONES

2,7839,Sales

1,7654,MARTIN

2,7698,Accounting

3,10

Control File:

LOAD DATA

INFILE ‘eg.dat’

APPEND

INTO TABLE emp

WHEN (01) = ’1′

FIELDS TERMINATED BY “,”

( rec_skip filler POSITION(1),emp_num , emp_name )

INTO TABLE dept

WHEN (01) = ’2′

FIELDS TERMINATED BY “,”

(rec_skip filler POSITION(1),department_num,

department_name )

Let’s now see how SQL LOADER processes the CTL file:

 SQL LOADER loads the records into table EMP only when first position (01) of the record which happens to be the record type is ’1′ as directed by command

INTO TABLE emp

WHEN (01) = ’1′

 If condition When (01) = ’1′ holds true for the current record, then SQL LOADER gets to the beginning of the record as directed by command POSITION(1) and skips the first field which is the record type.

 It then loads the second field into emp_num and third field into emp_name column in the table EMP.

 SQL LOADER loads the records into table DEPT only when first position (01) of the record which happens to be the record type is ’2′ as directed by the commands -

INTO TABLE dept

WHEN (01) = ’2′

 If condition When (01) = ’2′ holds true for the current record, then SQL LOADER gets to the beginning of the record as directed by command POSITION(1) and skips the first field which is the record type.

 It then loads the second field into department_num and third field into department_name columns in the table DEPT.

 The records with record type = ’3′ are not loaded into any table.

Thus you can selectively loads the necessary records into various tables using WHEN clause.

7) Run SQLLDR from SQL PLUS

SQL LOADER can be invoked from SQL PLUS using “host” command as shown below:

host sqlldr userid= username/password@host control = C:\eg.ctl log = eg.log

8) Default path for Discard, bad and log files

If bad and discard file paths are not specified in the CTL file and if this SQL Loader is registered as a concurrent program, then they will be created in the directory where the regular Concurrent programs’ output files reside. You can also find the paths where the discard and bad files have been created in the log file of the SQL LOADER concurrent request.