Overview – Oracle Projects

Last few days I have been involved with the Oracle Projects module. I’ll try and put forth some basic learning of this module.

Oracle Projects is meant primarily for organizations that are project-oriented. Using this module, it becomes easy to track costs, budget and track the project status.

 

Oracle Projects consists of the following products:

• Oracle Project Costing

• Oracle Project Billing

• Oracle Project Connect for Microsoft Project

• Oracle Activity Management Gateway

• Oracle Project Analysis Collection Pack

 

Prior to Oracle Projects Setup, one has to setup the Set of Books in GL, setup Organization and Organization hierarchy in Oracle HRMS, define employees and job in HRMS and create customer in Oracle Receivables. However if Oracle Projects is being installed as a standalone package then one needs to define all above in Oracle Projects itself. Some other mandatory setups include defining locations, defining implementation options, defining Project Accounting periods, defining expenditure types and categories, define revenue categories, etc. Also one has to create a burdening hierarchy in Oracle HRMS which may vary from the Project or Task Organization hierarchy.

 

Invariably a Project is broken into a hierarchy of tasks as per Work Breakdown Structure (WBS, a hierarchy of tasks that rollup into a project) to manage project and task related information. One can define as many levels of tasks in a hierarchy in Oracle Projects. However, proper naming conventions need to be followed while naming projects, tasks and sub-tasks. An organization has to be associated with a project and a Task, which may be same or different.

 

One can have three different Project Types for managing the cost of a project: Indirect, Capital and Contract. An Indirect Project Type is used to track the overhead costs and labor hours for overhead activities like Admin, Legal, etc. Capital Project Type is selected to track costs and labor hours related to asset development activities which ultimately results in an asset for the organization. A Contract Project Type is selected in case the costs are reimbursed by a client.

 

Oracle Costing is the used for processing of expenditures for finding out costs which can be attributed to projects and tasks which can then be posted to GL corresponding to different account lines. There are two cost amounts associated to each transaction: Raw and Burdened. The raw cost is the actual cost of the work performed, and the burden cost is the indirect cost or overhead of work performed, like administrative cost. The Burden cost is calculated by multiplying Raw Cost with the Burden Multiplier. The burdened cost is the total cost that is incurred, i.e., the sum of raw cost and burden cost.

Burden Cost = Raw Cost x Burden Multiplier

Burdened Cost = Raw Cost + Burden Cost

More on Oracle Projects, primarily I’ll try and provide inputs on Burdening Cost and Oracle Project Billing in subsequent days.

Understanding Oracle SQL*Loader

SQL*Loader is an Oracle utility that enables you to efficiently load large amounts of data into a database. If you have data in a flat file, such as a comma-delimited text file, and you need to get that data into an Oracle database, SQL*Loader is the tool to use. This chapter introduces you to the SQL*Loader utility, discusses its control file, provides the syntax for using the SQL*Loader command, and provides examples of using SQL*Loader to load data into databases.

 

Using SQL*Loader, you can do the following:

Ø       Load data from a delimited text file, such as a comma delimited file

Ø       Load data from a fixed-width text file

Ø       Load data from a binary file

Ø       Combine multiple input records into one logical record

Ø       Store data from one logical record into one table or into several tables

Ø       Write SQL expressions to validate and transform data as it is being read from a file

Ø       Combine data from multiple data files into one

Ø       Filter the data in the input file, loading only selected records

Ø       Collect bad records—that is, those records that won’t load—into a separate file where you can fix them

 

Control Files

Control files, contain a number of commands and clauses describing the data that SQL*Loader is reading. Control files also tell SQL*Loader where to store that data, and they can define validation expressions for the data. Understanding control file syntax is crucial to using SQL*Loader effectively. The control file is aptly named, because it controls almost every aspect of how SQL*Loader operates. The control file describes the format of the data in the input file and tells SQL*Loader which tables and columns to populate with that data.

 

Understanding the SQL*Loader Commands

The syntax for

the SQL*Loader command looks like this:   sqlldr [param=value[, param=value...]]

 

SQL*Loader Parameters

Parameter Description

userid :   Passes in your username, password, and Net8 service name. The syntax to use is the same as for any other command-line utility, and looks like this: userid=username[/password][@service]

 

control: Passes in the control file name. Here’s an example: control=[path]filename[.ext] The default extension for control files is .ctl.

 

log:  Passes in the log file name. Here’s an example: log=[path]filename[.ext] The default extension used for log files is .log. If you don’t supply a file name, the log file will be named to match the control file.

 

bad Passes in the bad file name. Here’s an example: bad=[path]filename[.ext] The default extension for bad files is .bad. If you don’t supply a file name, the bad file will be named to match the control file. Using this parameter overrides any file name that may be specified in the control file.

 

data Passes in the data file name. Here’s an example: data=[path]filename[.ext] The default extension used for data files is .dat. Specifying a data file name on the command line overrides the name specified in the  control file. If no data file name is specified anywhere, it defaults to the same name as the control file, but with the .dat extension.

 

discard Passes in the discard file name. Here’s an example: discard=[path]filename[.ext]

The default extension used for discard files is .dis. If you don’t supply a file name, the discard file will be named to match the control file. Using this parameter overrides any discard file name that may be specified in the control file.

 

discardmax Optionally places a limit on the number of discarded records that will

be allowed. The syntax looks like this: discardmax=number_of_records If the number of discarded records exceeds this limit, the load is aborted.

 

skip Allows you to skip a specified number of logical records. The syntax looks like this:

skip=number_of_records Use the skip parameter when you want to continue a load that has

been aborted and when you know how far into the file you want to go before you restart.

load Optionally places a limit on the number of logical records to load into the database. The syntax looks like this:

 

load=number_of_records Once the specified limit has been reached, SQL*Loader will stop.

 

Errors Specifies the number of errors to allow before SQL*Loader aborts the load. The syntax looks like this: errors=number_of_records SQL*Loader will stop the load if more than the specified number of errors has been received. The default limit is 50. There is no way to allow an unlimited number. The best you can do is to specify a very high value, such as 999999999.

 

Rows indirectly controls how often commits occur during the load process. The rows parameter specifies the size of the bind array used for conventional-path loads in terms of rows. SQL*Loader will round that value off to be some multiple of the I/O block size. The syntax for the rows parameter looks like this: rows=number_of_rows The default value is 64 for conventional-path loads. Direct-path loads, by default, are saved only when the entire load is done. However, when a direct-path load is done, this parameter can be used to control the commit frequency directly.

4. Run the applypreferences script. 

 

Ø       SQL*Loader is a versatile utility for loading large amounts of data into an Oracle database.

Ø       SQL*Loader control files are used to describe the data being loaded and to specify the table(s) into which that data is stored.

Ø       You can use the INFILE clause to identify the file, or files, that you want SQL*Loader to read.

Ø       You can use the INTO TABLE clause to identify the table, and the columns within that table that you wish to populate using the data read from the input file.

Ø       You can use the APPEND option after the INFILE clause to tell SQL*Loader to insert data into a table that already contains data to begin with.

Ø       You can use SQL*Loader to load delimited data, such as comma-delimited data, or you can use it to load data stored in fixed-width columns.

Ø       SQL*Loader fully supports all of Oracle8i’s datatypes, even to the point of allowing you to populate LOB columns.

 

Thanks Shivmohan Purohit