Oracle Applications

Common Errors in Oracle Reports

2011-05-09T23:42:00.001-07:00

REP-1219: 'M_BATCHES_EXP' has no size -- length or width is zero.
REP-1433: 'r_entered_tot_dr_dspformula': Error running user exit 'FND'.
REP-1249: Column 'CF_1' has no PL/SQL formula.
REP-1203: Field 'F_TOT_CR1' references invalid column.
REP-1517: Column 'B_ENTERED_TOT_DR_DSP' references column 'Currency_Code', which has incompatible frequency.
REP-1401: 'r_entered_tot_cr_dspformula': Fatal PL/SQL error occurred.
ORA-06502: PL/SQL: numeric or value error
REP-1213: Field 'F_BAT_22' references column 'B_TOT_DR_DSP' at a frequency below its group.
REP-1814: Report cannot be formatted. Object 'horizontally' can never fit within 'F_BATCH_POSTED_DATE1'
REP-1212: Object 'Margin' is not fully enclosed by its enclosing object 'SET_OF_BOOKS_NAME'.

R12 installation Steps - Video

2011-04-21T21:48:00.000-07:00

http://www.youtube.com/watch?v=fthxDUXiX1w&feature=player_detailpage

http://www.youtube.com/watch?v=Ou-PM3YSU9o&feature=player_detailpage

Unix Commands

2011-04-21T21:43:00.000-07:00

UNIX is case-sensitive.

Files
ls --- lists your files
ls -l --- lists your files in 'long format', which contains lots of useful information, ls -a --- lists all files, including the ones whose filenames begin in a dot,
more filename --- shows the first part of a file
emacs filename --- is an editor that lets you create and edit a file
mv filename1 filename2 --- moves a file
cp filename1 filename2 --- copies a file
rm filename --- removes a file.
diff filename1 filename2 --- compares files, and shows where they differ
wc filename --- tells you how many lines, words, and characters there are in a file
chmod options filename --- lets you change the read, write, and execute permissions on your files.

File Compression
gzip filename --- compresses files, so that they take up much less space.
gunzip filename --- uncompresses files compressed by gzip.

printing
lpr filename --- print. Use the -P option to specify the printer name if you want to use a printer other than your default printer.
lpq --- check out the printer queue, e.g. to get the number needed for removal, or to see how many other files will be printed before yours will come out
lprm jobnumber --- remove something from the printer queue. You can find the job number by using lpq. Theoretically you also have to specify a printer name, but this isn't necessary as long as you use your default printer in the department.
genscript --- converts plain text files into postscript for printing, and gives you some options for formatting.
dvips filename --- print .dvi files (i.e. files produced by LaTeX). You can use dviselect to print only selected pages.

Directories
mkdir dirname --- make a new directory
cd dirname --- change directory. You basically 'go' to another directory, and you will

Error with materialized views

2011-04-20T21:09:00.000-07:00

Error while creating materialized view with refresh complete option and the table for which the view has to be created does not contain any primary keys then an error will be thrown while creating the view.

ORA-12014: 'XXX.CUSTOM_TABLE' does not contain any primary key constraint.

Solution - Create view with ROW ID option should be used with the refresh option

ERP??

2011-04-19T20:33:00.000-07:00

Enterprise resource planning (ERP) is the industry term used to describe a broad set of activities supported by multi-module application software that helps a manufacturer or other business manage the important parts of its business. These parts can include product planning, parts purchasing, maintaining inventories, interacting with suppliers, providing customer service, and tracking orders. ERP can also include application modules for the finance and human resources aspects of a business.

Some of the bigger players in the ERP outsourcing market are SAP, Peoplesoft, and J. D. Edwards. New comers include Oracle, IBM, and Microsoft.

Now we know what is ERP and ERP softwares availables. Most of the top, mid level companies use oracle as SAP is costly comparatively.

What Oracle provides ??

Oracle's Complete, Open and Integrated applications strategy helps customers transform their business—creating value and reducing cost and risk through: The most complete portfolio of core business and industry applications The most open application offerings and architecture The most integrated applications, business intelligence and infrastructure solutions

ERP Flow

2009-10-18T00:16:00.000-07:00

Oracle Discoverer

2009-10-17T11:49:00.000-07:00

Business intelligence?

Business intelligence is the ability to analyze data to answer business questions and predict future trends.

Oracle Business Intelligence Discoverer is a great business intelligence tool because it enables users to analyze data in an ad hoc way. Instead of relying on IT specialists to pre-define queries and reports, Discoverer users can choose the data to analyze and can continue manipulating results until they have the necessary information to take business decisions. Oracle Business Intelligence Discoverer also enables users to share the results of their data analysis with their colleagues in different formats (including charts and Excel spreadsheets).

How does Discoverer work?

Oracle Discoverer is an intuitive ad hoc reporting tool. It enables a developer or knowledgeable end user to create reports with impressive formatting and attractive exporting and publishing functionality. Oracle Discoverer empowers your user population by placing business and decision critical data at their fingertips

Users' requests for information from the database are in the form of worksheets.
When a user creates or opens a worksheet, Discoverer converts the worksheet into the corresponding SQL statements (e.g. by converting folder names and item names to table names and column names respectively) sends the SQL statements to the database displays the result set that is returned from the database

In the case of Discoverer Plus, Discoverer Desktop and Discoverer Viewer, the SQL statements are routed to the database via Discoverer processes running on an application server machine

Purchasing Document Open Interface (PDOI)

2009-10-17T09:54:00.000-07:00

INTRODUCTION - PDOI are used to Import and update price/sales catalog information. It uses Application Program Interfaces (APIs) to process catalog data in the Oracle Applications interface tables. It is done to ensure that it is valid before importing it into Oracle Purchasing.

After validating the price/sales catalog information
1. PDOI program converts the information, including price break information, in the interface tables into blanket purchase agreements.
2. You can also choose to update your item master and apply sourcing rules and release generation methods to the imported item for blanket purchase agreements.
3. Blanket purchase agreements can also be replaced with the latest price/sales catalog information when your supplier sends a replacement catalog, or updated when the supplier sends an updated catalog.

How to import data - One way to import price/sales catalog data is through Electronic Data Interchange (EDI). PDOI supports the EDI transmissions of the price/sales catalogs (ANSI X12 832 or EDIFACT PRICAT).

FUNCTIONAL OVERVIEW - There are different ways that a supplier sends the price/sales catalog to its client. They may use EDI’s, interface, mail etc., The main aim with supplier information is to load the interface tables. we can load them manually or depending on the interface the information is coming through. Flow of price /sales catalog from supplier to client:

The figure above shows the flow of price/sales catalog information from the
supplier or trading partner, to Oracle e-Commerce Gateway, to the Purchasing
Documents Open Interface, and finally into Purchasing.
Interface tables - PO_HEADERS_INTERFACE and PO_LINES_INTERFACE

How the tables are loaded
- Can be done manually
- e-Commerce Gateway ( supplier provides information as flat file using EDI translator according to EDI interface file definitions, then , the EDI Catalog Inbound program loads the information into the interface tables.

How it works - The EDI inbound program and the PDOI program are run as a request set when you choose Submit Request in the EDI import programs window. The EDI inbound program loads the interface tables. Later on, PDOI program validates the data and loads the validated data into Purchasing.

The PDOI program receives the data, derives and defaults any missing data, and validates the data. If no errors are found in the submission process, the data in the PDOI tables is loaded into the PO_HEADERS, PO_LINES, and PO_LINE_LOCATIONS tables in Purchasing to create the blanket purchase agreement

How to check status – first make note of the Request ID number and then select View My Requests from the Help menu.

If PDOI program finds any error in the information, the record identification number and details of error are send to PO_INTERFACE_ERRORS table.

RECORD AND ERROR PROCESSING

Record Processing - PDOI processes a record from parent PO_HEADERS_INTERFACE table then to child PO_LINES_INTERFACE table before going on to the next record. Partial documents are accepted as PDOI saves or errors out line by line.

Error Processing –

1. If an error is found in a header, none of its lines will be processed and sets the column PROCESS_CODE to REJECTED in the PO_HEADERS_INTERFACE table.
2. If no errors are found, then the PROCESS_CODE is set as ACCEPTED.
3. If the supplier sends an updated price/sales catalog, the PDOI sets the PROCESS_CODE column to NOTIFIED for those lines with prices that exceed your price tolerance.

Order to Cash

2009-10-16T23:29:00.000-07:00

This cycle starts with the order management module where the customer places their order. In this module, we check for the availability of items in the inventory module and if there are enough stock in inventory then the items are shipped to the customer along with the invoice which is processed in account receivables module. Later on, this information goes into cash management for reconciliation purpose and in order to complete the business cycle, these invoices are moved to the GL module. Finally after a review of these journey entries in GL module, they are auto posted in the respective accounts, thus ending the cycle.

How to create Purchase Order

2009-10-16T23:13:00.001-07:00

1ST STEP – CREATE AND APPROVE PURCHASE ORDER

Create your purchase order by entering header and line information. You can specify the shipment dates and quantities for each item, and how the shipment should be distributed across different accounts. Submit your order for approval when it is complete.

2ND STEP – COMMUNICATE PURCHASE ORDER TO SUPPLIER

Once your purchase order has been approved, send it to the supplier by Electronic Data Interchange (EDI), the Web, printed hard copy, or fax. If you communicate your purchase order by the Web, Oracle Supplier Management Portal allows your supplier to view and download the purchase order into their system.

3RD STEP – ACKNOWLEDGE PURCHASE ORDER

Enter the supplier's acceptance of the purchase order. Use the Purchase Order Summary window to query your purchase order. Choose Acceptances from the Tools menu and enter the supplier's acceptance of the purchase order.

4TH STEP – CHANGE AND REAPPROVE PURCHASE ORDER

Make changes to a purchase order, even after approval, by querying on the purchase order number and then making the necessary changes. Through the use of Oracle Workflow, Oracle Purchasing can then route the changed purchase order through a different approval hierarchy if necessary.

5TH STEP – COMMUNICATES PURCHASE ORDER CHANGES TO SUPPLIER

Once your changed order has been approved, communicate the change to the supplier by Electronic Data Interchange (EDI), the Web, printed hard copy, or fax. If you communicate your purchase order by the Web, Oracle Supplier Management Portal allows your supplier to download the purchase order into their system.

6TH STEP – ACKNOWLEDGE PURCHASE ORDER CHANGES

Enter your supplier's acceptance of the purchase order changes. Use the Purchase Order Summary window to query your purchase order. Choose Acceptances from the Tools menu and enter the supplier's acceptance of the purchase order changes.

Purchase to Pay

2009-10-16T23:13:00.000-07:00

This cycle starts when there is a demand for inventory. To fulfill the demand, requisition is raised with the specified UOM, quantity, etc., If the requisition is approved, then the purchase order is created in the purchasing module. Then the PO is send to the supplier who indeed sends the receipt. When the supplier sends the consignment, it is received at inventory and the invoice is created in account payable module. Finally payment is done to supplier against the invoice and the details are transferred to cash management module for reconciliation.

Why EDI Evolved ?

2009-10-15T23:55:00.000-07:00

Let us consider a simple business scenario. A Customer from PO department who wants to purchase an item creates a purchase order and then faxes it to the suppliers. At the moment vendor receives the purchase order and manually keys in a sales order. The vendor’s system generates a confirmation date that is sent back to the customer via fax or mail. The vendor then ships the goods via a carrier. The carrier delivers the products to the customer. When the goods are shipped, the vendor invoices the customer. The customer makes the payment by check, and the vendor deposits the check in the bank. Finally, funds are transferred from the customer’s account to the vendor’s account.

Now It clears how much of information needs to be transferred and tracked to run the business, which in reality it is called trading partner. This simple scenario requires the exchange of various documents between several business partners at different times.

All this electronic data was exchanged using floppy disks and other secondary devices. So, ANSI committee was formed to define the standards. Hence the electronic exchange of business documents in a standard format gave birth to what is known as EDI, today the business world had accepted as one of most important entity.

Multi Org

2009-10-14T23:06:00.000-07:00

Multi org functionality enables you to have multiple set of books, sub ledgers, and transaction organization in one instance of an application database.

1. You can use a single installation of the oracle applications to support any number of business organizations using multiple set of books
2. You can define different organization models.
3. You can have any number of legal entities within a single installation of the applications.
4. You can implement organization security by responsibility so that information is available only to users linked to an organization;
5. You can automatically make intercompany transactions across set of books and legal entities.

Data Migration, Conversion, and Interfaces

2009-10-14T22:55:00.000-07:00

Data Migration is a process of moving required (and most often very large) volumes of data from our clients’ existing systems to new systems. Existing systems can be anything from custom-built IT infrastructures to spreadsheets and standalone databases.

Data conversion can be defined as a process of converting data from one structural form to another to suit the requirements of the system to which it is migrated. It is a process where existing data from the client’s old system is extracted, cleansed, formatted, and installed into a new system. These can be manual or automated. The big difference is that these are One-time only process that requires extensive testing and preparation. They must be executed and performed before a system goes into production.

Interfaces are programs for connection between Two Systems In Order To Synchronize the Data. They can be Manual, Batch or Real-Time. Used Repeatedly and Should Therefore Be Designed and Constructed In the Most Efficient Manner Possible. These can Be Triggered by an Event (Such As Running A Concurrent Program) Or It Can Be Scheduled To Run At A Certain Time. Can Be Very Costly To Construct And Maintain.

Inbound Interfaces

An inbound interface receives data from one system (legacy) and inserts into Oracle open interface tables. A typical inbound interface would follow these steps: Extract data from legacy system into a flat file. Use SQL*Loader or equivalent tool to upload information into a temporary table. Write a PL/SQL program to take data from the temp table and insert into the Open Interface Tables. Through the concurrent manager in Oracle Applications, run the standard Oracle Interface program to transform interface tables into Oracle data.

Outbound Interfaces

An outbound interface takes data from Oracle tables and inserts it into an external system (via tables or flat file). A typical outbound interface would follow these steps:- Write a PL/SQL program to extract data from Oracle base tables into a flat file.- Use a custom program to read that data and post it into the legacy system

Software Development Life Cycle

2009-10-14T22:27:00.000-07:00

FND_FILE

2009-10-12T22:35:00.000-07:00

An API that contains procedures to write text to log and output files which can be viewed once the Concurrent program get completed in output or log . These procedures can only be used within PL/SQL Procedures which, are concurrent programs.

Internally its uses the oracle supplied database package UTL_FILE

utl_file_dir parameter must be set to include the log and out directories for the applications that will run concurrent requests using FND_FILE
Database owner must have write privileges on those directories
Take a note that FND_FILE supports a maximum buffer line size of 32K for both log and output files.

FND_FILE.PUT - Useful for writing text to a file without a new line character. Multiple calls will concatenate the text. Does not include new line character

FND_FILE.PUT_LINE This is used to write a line of text to a file followed by a new line character

FND_FILE.NEW_LINE FND_FILE.NEW_LINE( which IN NUMBER, LINES IN NATURAL:= 1) Writes line terminators (new line characters) to a file.Eg. FND_FILE.NEW_LINE(FND_FILE.LOG,2)

FND_File.PUT_NAMES This is used to sets temporary log and out file names and directory, which normally used during test and debug phase.

FND_FILE.CLOSE This will closes open files.

This is normally used only during test and debug phase

AIM

2009-10-11T22:49:00.001-07:00

AIM

2009-10-11T22:49:00.000-07:00

Flex Fields

2009-10-09T22:04:00.000-07:00

Flexfield - A field made up of one or more segments. A segment corresponds to a column in an Oracle table. Each segment has an assigned name and may have assigned data validation rules (Validation Sets).

Key Flexfield - A Key Flexfield represents an intelligent key that uniquely identifies an application entity. These keys identify accounts, part numbers etc. Each key flexfield has an assigned name and a specified set of valid values. Examples: in G/L we have the Accounting Flexfield (Chart of Accounts); in Inventory it is the Item Flexfield, in Projects the Job Flexfield.

Descriptive Flexfield - Allows for the capture of additional information not stored in the default application, or strictly for reference when viewed on a screen. You may create a Flexfield using any of “Attribute(n)” columns. Oracle reserved “Attribute(n)” columns on most of the tables to be setup during installation. These columns are not used by Oracle standard functionality. You may expand the number of Flexfield values by using “context sensitive” feature.

Flexfield Key Terms
Accounting Flexfield : The code you use to identify a general ledger (GL) account in Oracle.
Segment: A subcomponent of a flexfield. Company and natural account are examples of possible segments in the accounting flexfield.

Flexfield Qualifiers : Balancing segment, cost center segment, natural account, inter-company segment.

Segment Values : Numbers or codes attributed to each segment.Value Set: A list of values for one segment, such as the list of companies, list of accounts, or the list of departments.
Code Combination : One full accounting flexfield with all segment values.
Dynamic Insertion / Cross-Validation : Automatic creation of account code combination based on rules defined with code combinations.

GL
REFERENCE1 User Entered or System Auto Generated Batch Name for Every Run
REFERENCE2 User Entered or System Auto Generated Batch Name for Every Run
REFERENCE7 ‘Yes’. This means the following Journal Entry could be reversed
REFERENCE8 Journal Entry Reversal Period in Case it needs to be reversed. This should be the same as Period Name
REFERENCE9 ‘Yes’. To change the Sign for Reversal

AIM

2009-10-07T22:44:00.000-07:00

AIM (application implementation methodology) provides the oracle consultant with an integrated set of templates, procedures, power point presentations, spreadsheets, and project plans for implementing the applications.

It has six phases
Definition phase
Plan the project
Determine business objectives
Operations analysis phase
Documents, and business requirements
Gaps in the software and system architecture requirements.
Results of the analysis should provide a proposal for future business processes
Transition strategy to migrate to the new systems.
Mapping of legacy data to oracle application APIs or open interfaces
Solution design phase
Create designs for solution that meet future business requirements
Design of your future organization comes alive during this phase as customizations and module configurations are finalized
Build phase
Coding and testing of customizations enhancements, interfaces, and data conversions.
One or more conference room pilots test the integrated enterprise system.
Results of the build phase should be a working, tested business system solution.
Transition phase
Project team delivers the finished solution to the enterprise.
End user training and support, management of change, and data conversions are major activities of this phase.
Production phase
Starts when the system goes live.
Technical people work to stabilize and maintain the system under full transaction loads.
Users and the implementation team begin a series of refinements to minimize unfavorable impacts and realize the business objectives identified in the definition phase.

REQUISITION OPEN INTERFACE

2009-10-07T22:38:00.000-07:00

Automatically imports requisitions (from non oracle or oracle environment)
Integrates oracle purchasing with new or existing applications (MRP, INV management, and production control.

TABLES –
PO_REQUISITIONS_INTERFACE_ALL and/or
PO_REQ_DIST_INTERFACE_ALL

PROGRAM – REQUISITION IMPORT

IMPORTANT PARAMETERS – INTERFACE_SOURCE_CODE and BATCH_ID

In Release 11i – use PO_REQ_DIST_INTERFACE_ALL to create multiple distributions only for requisitions created in non-Oracle systems that use multiple distributions.

3 PHASES OF IMPORT PROGRAM

Validates data and generates an error message in PO_INTERFACE_ERRORS table.
Groups and numbers the validated requisition lines (groups based on the value of the columns REQ_NUMBER_SEGMENT1 or GROUP_CODE)
Deletes processed rows in interface tables and creates a report (which lists the number of interface records that were successfully imported and the number that were not imported).

STEPS FOR SETTING UP THE REQUISITIONS INTERFACE

1. You must define a Requisition Import Group-By method in the Default region of the Purchasing Options window.
2. For internally sourced requisitions, you must associate a customer with your deliver-to location using the Customer Addresses window.
3. All processing is initiated through standard report submission using the Submit
Request window. The concurrent manager manages all processing, and as such it
must be set up and running.

Data Modelling

2009-09-05T20:44:00.000-07:00

Data models are a cornerstone of design. Engineers build a model of a car to work out any details before putting it into production. In the same manner, system designers develop models to explore ideas and improve the understanding of the database design.

Purpose of model : The objective is to produce a model that fits a multitude of these uses, can be understood by an end user, and contains sufficient detail for a developer to build a database system.

ER MODEL
An entity relationship (ER) model is an illustration of various entities in a business and the relationships between them. An ER model is derived from business specifications or narratives and built during the analysis phase of the system development life cycle. ER models separate the information required by a business from the activities performed within a business

Benefits : Documents information for the organization in a clear, precise formatProvides a clear picture of the scope of the information requirementProvides an easily understood pictorial map for the database designOffers an effective framework for integrating multiple applications

SQL definitions

2009-09-03T20:27:00.000-07:00

Primary Key: A column in a table whose values uniquely identify the rows in the table. A primary key value cannot be NULL.
Unique Key: Unique Keys are used to uniquely identify each row in an Oracle table. There can be one and only one row for each unique key value.
Surrogate Key: A system generated key with no business value. Usually implemented with database generated sequences.
Foreign key (FK): A column or combination of columns that is used to establish and enforce a link between the data in two tables

Composite key consists of two or more columns, designated together as a table's primary key. Multiple-column primary keys can be defined only as table-level constraints:
Candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data. Each table may have one or more candidate keys. One of these candidate keys is selected as the table primary key.
Rownum is used to order the datas according to their ranks and it is used in inline views.
Level pseudocolumn is used in hierarchial retrival of database.....
An array is a container object that holds a fixed number of values of a single type. The length of an array is established when the array is created.

SQL statements

2009-09-02T20:49:00.000-07:00

Data Retrieval
SELECT

Data Manipulation
INSERT
UPDATE
DELETE
MERGE

Data definition
CREATE
ALTER
DROP
RENAME
TRUNCATE

Transactional control
COMMIT
ROLLBACK
SAVEPOINT

Data Control
GRANT
REVOKE

Eliminating Duplicate rows : Use DISTINCT keyword in SELECT command
Limit rows : Use WHERE clause
Display rows in a range of values : Use BETWEEN clause
Test for values in a list :Use IN clause
Perform wildcard searches of valid search string values :Use LIKE clause
Test for nulls :Use IS NULL operator
Sort rows: Use ORDER BY clause ASC or DESC

SQL Functions

2009-09-01T21:22:00.000-07:00

SQL FUNCTIONS
Perform calculations on data
Modify individual data items
Manipulate output for groups of rows
Format dates and numbers for display
Convert column data types

SINGLE ROW FUNCTIONS
Character functions
1.Case manipulation
2.Character manipulation
Number functions
Date functions

Conversion functions
Implicit data type
Explicit data type

General functions
NVL(expr1,expr2)
NVL2(expr1,expr2,expr3)
NULLIF(expr1,expr2)
COALSECE(expr1,expr2,..exprn)
CASE DECODE

MULTIPLE ROW FUNCTIONS
Functions can manipulate groups of rows to give one result per group of rows. These functions are known as group functions
CARTESIAN PRODUCT When a join condition is invalid or omitted completely, the result is a Cartesian product, in which allcombinations of rows are displayed. All rows in the first table are joined to all rows in the secondtable.

JOINS and prior Equijoin - The relationship between the EMPLOYEES and DEPARTMENTS tables is an equijoin—that is, valuesin the DEPARTMENT_ID column on both tables must be equal. Frequently, this type of join involvesprimary and foreign key complements
Non -equijoin - A non-equijoin is a join condition containing something other than an equality operator.
Outer join (+) - If a row does not satisfy a join condition, the row will not appear in the query result. For example, in the equijoin condition of EMPLOYEES and DEPARTMENTS tables, employee Grant does not appear because there is no department ID recorded for her in the EMPLOYEES table. Instead of seeing 20employees in the result set, you see 19 records.
Self join - Sometimes you need to join a table to itself. To find the name of each employee’s manager, you need to join the EMPLOYEES table to itself, or perform a self join.
Cross join - The CROSS JOIN clause produces the cross product of two tables. This is the same as a Cartesian product between the two tables.
Natural joins - The NATURAL JOIN clause is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in all matched columns.
Using - If several columns have the same names but thedata types do not match, the NATURAL JOINclause can be modified with the USING clause tospecify the columns that should be used for an equijoin.
ON clause - The join condition for the natural join is basically an equijoin of all columns with the same name. To specify arbitrary conditions or specify columns to join, the ON clause is used.
Left outer join - The result of a left outer join for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).
Right outer join - A right outer join closely resembles a left outer join, except with the tables reversed. Every record from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in A.
Full outer join - A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
Group Functions - Unlike single-row functions, group functions operate on sets of rows to give one result per group.These sets may be the whole table or the table split into groups.
Types of G.F - AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE
Having - You use the HAVING clause to restrict groups.• You cannot use group functions in the WHERE clause.
PL/SQL - It is the procedural extension to SQL with design features of programming languages.Data manipulation and query statements of SQL are included within procedural units of code.
Benefits of pl sql
It plays a central role in both the oracle server and oracle development tools.
Improved Performance
Moduralized program development
Portability
Identifiers
Benefits of sub programs Integration :
Easy maintenance
Improved data security and integration
Improved performance
Improved code clarity

PL SQL BLOCK
DECLARE - optional Variables, cursors, user defined exceptions
BEGIN - mandatory-
sql statements-
pl sql statements
EXCEPTION -optional Actions to perform when errors occur
END; - mandatory

VARIABLES
Temporary storage of data
Manipulation of stored values
Reusability
Ease of maintenance

Types of variables
PL SQL
Scalar – char, varchar2, long, long raw, number, binary integer, pls_integer, Boolean, date, timestamp, timestamp with time zone, timestamp with local time zone, interval year to month, interval day to second.
Composite – Table, record, nested table, and varray types
Reference
LOB
NON-PL SQL
Bind
Host