Oracle Reports

Oracle Reports: Oracle Reports is a report generated by Oracle Report Builder tool using data stored in an Oracle database. 

• Way to provide a certain set of data in a specified format to an individual or a group of audience.
• Reports are often used to display the result of an experiment, investigation, or inquiry.
• The audience may be public or private, an individual or a group.

Report Builder:

Oracle Reports Builder is a powerful enterprise reporting tool used to build reports that dynamically retrieve data from Oracle database, format, display and print quality reports. When you first start Oracle Reports Developer, the Reports Wizard will open automatically. You can also access it by selecting File | New | Report from the menu.

 First, you need to select a report style from the following choices:

Tabular            : a simple spreadsheet-like report

 Form                : displays one form-like record

 Group Left      : Selected group fields are displayed on the left of the report

 Group Above : Selected group fields are displayed on top of the report

 Matrix : Summary report that will calculate values corresponding to a horizontal and a vertical grouping

Matrix with Group : A grouping field added to the matrix report

 Mailing Labels & Form Letter : similar to the Mail Merge capability.

     

      ** The difference between Mailing Labels and Form Letters is, Mailing Label shows multiple records on one page while Form Letter shows one record on each page.

Components in Report Builder:

Oracle Reports Builder comes with the following components.

       1. Object Navigator

2. Data Model Editor

3. Layout Model Editor

4. Parameter Form Editor

Object Navigator

The Object Navigator shows a hierarchical view of objects in the report. Each item listed is called a node and represents an object or type of object the report can contain or reference.

Data Model Editor: The Data Model contains the logical grouping of data for the report. This is the place where you define the source data, add additional queries, and link them.

A data model is composed of some or all of the following data definition objects.

Queries
Queries are SQL Select statements that fetch data from the oracle database. These statements are fired each time the report is run.

 Groups
Groups determine the hierarchy of data appearing in the report and are primarily used to group columns selected in the query. Oracle report automatically creates a group for each query.

Data Columns
Data columns contain the data values for a report. Default data columns, corresponding to the table columns included in each query’s SELECT list are automatically created by oracle reports. Each column is placed in the group associated with the query that selected the column.

Summary Columns
Summary columns are used for calculating summary information like sum, average etc. This column uses a set of predefined oracle aggregate functions. Summary columns are generally preceded by CS_ to distinguish them from other columns.

Data Links
Data Link: Join queries for complex data relationships.

Data links relate the results of multiple queries.  A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group

Formula Columns

                Formula Column Performs a user-defined computation.

Characteristics:

. Executes a PL/SQL function
. Must return a value

n  Can be a Character, Number, Date

n  Returned value must match datatype

Example:

function CF_3Formula return Number is

begin

  return(:taxable_total+:tax_total);

end;

Placeholder:

n  An empty container at design time

n  Populated by another object at run time

n  Before report trigger

n  Formula column at report level

n  Formula column in same group or below placeholder

Bind Variable and Lexical Parameter:

n  Bind reference replaces a value

n  :parameter_name

n  parameter object is created by default

n  Lexical reference replaces a clause

n  &Lparameter_name

n  parameter object is never created by default

Use to substitute any part of the query.

                SELECT NAME, SALES_REP_ID

                FROM S_CUSTOMER

                &LP_WHERE_CLAUSE

                &LP_ORD_CLAUSE

LAYOUT MODEL: The Layout Model displays the physical layout of the data.

 A report layout editor contains the following layout objects

n  Frames : Frames surround other layout objects, enabling control of multiple objects simultaneously.

n  Repeating Frames : Repeating frames acts as placeholders for groups (I.e repeating values) and present rows of data retrieved from the database. Repeating frames repeat as often as the number of rows retrieved.

n  Fields : Fields acts as placeholders for columns values. They define the formatting attributes for all columns displayed in the report.

n  Boilerplate : Boilerplate consists of text (label of the column) and graphics that appear in a report each time it is run.

Parameter form is a runtime form used to accept inputs from the user.

Parameters : Parameters are variables for a report that accept input from the user at runtime. These parameter values can then be used in the SQL select statements to retrieve data conditionally. Oracle reports creates a set of system parameters at runtime namely report destination type, number of copies etc.

Commonly Used Field and Frame Properties

	Property	Usage
Field	Source	Select a column from the Data Model
	Vertical Elasticity	If the field content will expand beyond its physical width, the object can be truncated (Fixed), expanded vertically (Expand, Variable). If the object is smaller than the allocated, its height can be shrunk (Contract, Variable). In the Layout Model, the vertically expandable fields (and frames) are symbolized with an = sign on the vertical lines.
	Horizontal Elasticity	The same as the Vertical Elasticity, but the object will expand horizontally. This property is used less often than the Vertical Elasticity. Italic fonts might be truncated if you use the Variable or Contract setting
	Conditional Formatting	You can format or even hide the field depending upon various conditions Developer-friendly code writer.
Repeating Frame	Source	Select a group in the Data Model.
	Maximum Records per Page	Allows you to display a fixed number of records per page, usually “1” for Form-like reports
	Page Protect	If set to “Yes,” will not allow the separation of the field contained in the frame, unless the frame extends beyond one page
	Elasticity, Conditional Formatting	Similar to fields If the fields have variable lengths, it is reasonable to make the frames variable too.
Frames	Page Protect, Elasticity, Conditional Formatting	Similar to Repeating Frames

n  Format Triggers: Format triggers are PL/SQL functions executed before the object is formatted. These triggers are used to dynamically change the formatting attributes and used to conditionally print and not to print a report column value. These triggers return Boolean values TRUE or FALSE. If the return value of the format trigger is FALSE, the value is not displayed.

n  You can find these triggers in property Palette of layout objects

Example:

function F_TAX_CODE1FormatTrigger return boolean is

begin

    if cond ... then

                return (TRUE);

  else

                return (false);

  end;

Below are the list triggers which are available in report builder.

      Before Parameter Form:- Fires before the Runtime Parameter Form are displayed. Can access the PL/SQL global variables, report level columns and manipulate accordingly.

      After Parameter Form:- Fires after the Runtime Parameter form are displayed. Used to validate the parameter values.

      Before Report:- Fires before the report is executed but after the queries is parsed and date is fetched.

      Between Pages:- Fires before each page of the report are formatted, except the very first page. This page is used to customize page formatting.

      After Report:- Fires after the report previewer are exited, or after report output is sent to a specified destination.

Generating Warning and Errors:

Warning

WHEN <exception> THEN

 SRW.MESSAGE(999, ‘Warning: report continues’);

Error

WHEN <exception> THEN

 SRW.MESSAGE(999, ‘Error: report terminated’);

 RAISE SRW.PROGRAM_ABORT;

SRW.DO_SQL(‘CREATE TABLE SRW_LOG

                                                (RPT_NAME VARCHAR2(40),

                                                REC_NUM NUMBER,

                                                MSG_TEXT VARCHAR2(80))’);

SRW.DO_SQL(‘INSERT INTO SRW_LOG

                                (RPT_NAME REC_NUM, MSG_TEXT)

                                VALUES

                                (“PAY_REPORT”, TO_CHAR(:ID),

                                :LAST_NAME||”PAY REPORT RUN”)’);

Purchase Order

Oracle Purchasing Module ( P.O) :-

Purchasing Module will capture the complete Purchasing process information where we can find out different types of roles like

            Requesters

            Preparer

            Buyer

Requester:-     Requester is an employee who is going to request the items.

Preparer:-       Preparer is an employee who is going to prepare documents as per Requester Requirement. Before going to work with P.O module we need to follow these steps.

1.      Item creation in Inventory Module.

2.      Supplier creation in P.O module.

3.      Buyer setup in P.O module.

When ever create the item we can use these tables

For supplier     à        PO_VENDORS,

                                    PO_VENDOR_SITES_ALL

                                    PO_VENDOR_CONTACTS

For Buyer        à        PER_ALL_PEOAB_F_ALLEMP

                                    PO_AGENTS

            Select segment1, description from mtl_sys_items_b where segment1=’computer’

Item Creation:-           Navigation steps for Item creation is as follows

           

            Navigation    à   Inventory vision operations, USA

                       

                                  Items

                                         

                                       Master Items

 

                                            Select Any Organization

            Select Organization then enter the items and description. Go to Purchasing Tab select Check box called Purchase, Purchasable. Select Inventory Tab make it items as Inventory Items, Transact able, Reservable by checking the Checkboxes. Once we define the item we can assign this item to Multiple Organizations by selecting Tools à Organization Assignment. Item value stored in the column called ‘Segment1’.

To view Item details:-

Select * from HR_OPERATING_UNITS;

Creation Of Suppliers :- We will create Supplier in P.O.Module.

            Supplier Base  à        Suppliers

            Once we create the Supplier then select the Supplier Sites button then enter the Supplier Site Information. Select Contacts Tab where we can enter Supplier Contact Details.

To view Supplier details:-

            Select vendor_name from po_vendors where Segment1=5082;

Buyer Setup :-

1.      User should be assigned to Employee Name.

2.      Employee Name should available in the Buyer List.

While creating the User in System Administrator we will assign the Employee Name in  Person (label field) Page. We will go to Purchase Order Vision Operations

            Navigation  à Purchasing Super Vision Operations (USA)

                                Setup

                                                                                          

                                                  Personal

                                                    

                                                                        Buyer      

  

Here we will query the Employee Name that Employee become to User. This User can open Purchase Order Forms and can enter Purchase Order Transactions.

Purchase Order Process :-

Requisition

                                                                           

                                                                   

RFQ (Request For Quotation)                                                             

                                                        

                         

Quotations

Purchase Order

                                                                                                                                              

                                                                                                                                                  

Receipts

 

                                           

                                                                                                             

                                     Account Payables        Inventory       

Purchasing Process,which starts with Requisition. Once the Requisition is Approved then RFQ (Request For Quotation) will be created it will be sending to the different Suppliers. Suppliers will send the Quotations as per Quote Analysis. One Quotation will be created as Purchase Order. Once Purchase Order is approved then Supplier will supply the Goods to the Company. While receiving the goods Company will generate a document called Receipt. It will be gives to the Supplier as per this Receipt, Company will make the Payments in A.P (Accounts Payable) module and Inventory module gets affected with updating quantity.

REQUISITION:-       Requisition document will be created when ever Company requires items. We will have 2 types of Requisitions. They were

1.      Internal

2.      Purchase

Internal:-        We will create this Requisition when ever Company requires goods from one Organization to another Organization inside of the Company.

Purchase:-      We will create the Purchase Requisition when ever Company requires goods from outside means from Supplier.

            Navigation :  Purchasing Vision Operations (USA)

                                   

  

                                         Requisitions                                                                                            

                                                            

                                                               Requisitions

We will create the information in Requisition at 3 levels. They were

1.      Header level

2.      Lines level

3.      Distributions level

To view Requisition in Header level

Select * from PO_REQUISITION_HEADERS_ALL WHERE Segment1=1651;

                                                                        Output is = 11438

To view Requisition Lines

Select * from PO_REQUISITION_LINES_ALL WHERE

REQUISITION_HEADER_ID = 11438

                                                                        Output is = 9902, 9901, 9903

To view Requisition in Header level

Select * from PO_REQUISITION_DISTRIBUTION_ALL WHERE

REQUESITION_LINE_ID = 9902

After finishing the Requisition save it then you can get one ID number. Once we create the Requisition we will send this Requisition for approval by using the Requisition summary form we can find out whether Requisition has been approved or not. Enter the Requisition number in Requisition summary form select find button it will shows the Requisition details. If we want to cancel the Requisition go to Tools menu Control option. Here we can select the option called ’Finally Close’ Requisition or ‘Cancel Requisition’.

RFQ (Request For Quotation):-        RFQ’s will be created as per Requisition document. We will have 3 types of RFQ’s. They were

1.      Bid

2.      Catalog

3.      Standard

Bid:-    Bid will be used for fixed quantity, location and date. It will be used for large or expensive peace of equipment.

Catalog:-         Catalog will be used for high volume items. Here we will have price breaks and the price will be going to break at different quantity levels.

Standard:-       Standard RFQ will be used for items here you will need only once or not very often, we can include price breaks at different quantity levels.

Note:-  For all these 3 RFQ’s we will have 3 types of quotations . for Bid RFQ we have the Bid Quotation, for Catalog RFQ we have Catalog Quotation and for Standard RFQ we have Standard Quotation.

            Navigation:

                       

                   RFQ’s and Quotations                

 

                                                   RFQ’s

To view header RFQ

Select * from PO_HEADERS_ALL where Segment1=306 and     type_lookup_code=’RFQ’;

                                                                                                Output is = 11785

To view lines in a RFQ

           

            Select * from PO_LINES_ALL where po_header_id = 11785;

                                                                        Output is = 12173, 12174, 12175,12176

            Select * from PO_LINE_LOCATIONS_ALL where po_line_id = 12173;

            Create the RFQ at header, lines and Price breaks. We also enter the terms and conditions. They are

1. Payment Terms:-    It is nothing but Payment Method.

2. Freight Terms:-      It is nothing but Transportation charges.

3. Carrier:-      It is noting but Transportation Company Name.

4. FOB (Foot On Board);-    It is nothing but the Responsibility of Vendor or Buyer.

            Once we prepare the RFQ. We will select Suppliers button to enter the Suppliers List. Here we can find out how many Suppliers has received RFQ’s and the number of Responses and number of Quotations received from the Supplier.

QUOTAIONS:- Quotations will be received from the Suppliers. All these quotation information will be capture in the Quotation form.

To view the Quotation:-

Select * from PO_HEADERS_ALL where Segment1=306 and  type_lookup_code=’Quotation’;

Purchase Order:-       when ever we are creating the Purchase Order we will enter information at 4 levels.

           

1.      Headers

2.      Lines

3.      Shipments

4.      Distributions

One Header can have at least 1 line or multiple levels of lines. One line can have at least 1 shipment or, multiple levels of shipments. One shipment can have at least 1 distribution or multiple levels of distribution.

To view Purchase Order at header level

            Select * from PO_HEADERS_ALL WHERE Segment1=3439;

                                                                                    Output is = 11805

To view Line level

            Select * from PO_LINES_ALL WHERE po_header_id = 11805;

                                                                                    Output is = 12193, 12194

            Select * from PO_LINES_LOCATIONS_ALL WHERE po_line_id = 12194;

                                                                                    Output is = 14373, 14374, 14375

To view at Distributions

            Select * from PO_DISTRIBUTIONS_ALL WHERE line_location_id = 14374;