Query to find Parameters and Value Sets associated with a Concurrent Program

Query to find Parameters and Value Sets associated with a Concurrent Program

Posted by Amar Alam at 02:49 4 comments

 Following query finds the parameters and the value sets that are associated with a Concurrent Program. Change concurrent program name (fcpl.user_concurrent_program_name, see below) according to your search criteria. In this example, my concurrent program name is "XX AR Conversion Program".

-------------------------------------------------------------------------------
-- Query to find Parameters and Value Sets associated with a Concurrent Program
-------------------------------------------------------------------------------

SELECT fcpl.user_concurrent_program_name  "Concurrent Program Name",
       fcp.concurrent_program_name        "Program Short Name",
       fdfcuv.column_seq_num              "Column Seq #",
       fdfcuv.end_user_column_name        "Parameter Name",
       fdfcuv.form_left_prompt            "Prompt Name",
       fdfcuv.enabled_flag                "Enabled Flag",
       fdfcuv.required_flag               "Required Flag",
       fdfcuv.display_flag                "Display Flag",
       fdfcuv.flex_value_set_id           "Value Set ID",
       ffvs.flex_value_set_name           "Value Set Name",
       flv.meaning                        "Default Type",
       fdfcuv.default_value               "Default Value"
  FROM fnd_concurrent_programs      fcp,
       fnd_concurrent_programs_tl   fcpl,
       fnd_descr_flex_col_usage_vl  fdfcuv,
       fnd_flex_value_sets          ffvs,
       fnd_lookup_values            flv
 WHERE fcp.concurrent_program_id          =  fcpl.concurrent_program_id
   AND fdfcuv.descriptive_flexfield_name  =  '$SRS$.' || fcp.concurrent_program_name
   AND ffvs.flex_value_set_id             =  fdfcuv.flex_value_set_id
   AND flv.lookup_type(+)                 =  'FLEX_DEFAULT_TYPE'
   AND flv.lookup_code(+)                 =  fdfcuv.default_type
   AND fcpl.LANGUAGE                      =  USERENV('LANG')
   AND flv.LANGUAGE(+)                    =  USERENV('LANG')
   AND fdfcuv.enabled_flag                =  'Y'
   AND fcpl.user_concurrent_program_name  =  'IFFCO HRMS : Costing Summary Report - Consolidated'  -- 
 ORDER BY fdfcuv.column_seq_num;

D2K Reports Basics

D2K Reports Basics

D2K Reports Basics:

Introduction to Oracle Reports Builder:

Oracle Reports Builder is a powerful enterprise reporting tool used to build reports that dynamically retrieve data from the database, format, display and print quality reports. Reports can be stored in File or Database (Report Builder Tables).

Report file storage formats:

.rdf Report :

• Binary File Full report definition (includes source code and comments)
• Modifiable through Builder. Binary, executable Portable if transferred as binary.
• PL/SQL recompiles on Open/Run

.rep Report :

• Binary Run-Only File
• No source code or comments. Not modifiable binary, executable.
• Report Executables

You can use the Reports File Converter (rwcon60) to convert a .RDF to a .REP file. RWCON60 Report Converter/Compiler [File => Administration => Compile (rdf torep)/Convert]

Oracle Reports Builder Tools:

Oracle Reports Builder comes with the following components

• Object Navigator
• Property Palette
• Data Model Editor
• Layout Model Editor
• 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.

Property Palette:
A Property Palette is a window that displays the settings for defining an Oracle reports object.

Data Model Editor:
To specify data for a report, a data model should be defined. 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.

Formula Columns:
Formulas can be entered in formula columns to create computed columns.
Formulas can be written using PL/SQL syntax. Formula columns are generally preceded by CF_ to distinguish from other columns.

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.

Placeholder Column:
Place holder column is the name of variable which can hold a calculated value like (sum, avg..) or the value can be set by function or by a pl/sql block. Use of place holder column for aggrigate function is not advised, for that use summary column.

Data Links:
Data links are used to establish parent-child relationships between queries and groups via column matching.

Layout Model Editor:
A report layout editor contains the following layout objects

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

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.

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

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

Anchor:
Anchors fasten an edge of one object to an edge of another object, ensuring that they maintain their relative positions.

Parameter Form Editor:
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.

Report Wizard:

• When we create a default Tabular Report using report wizard, the wizard will take you through the below mentioned pages
• Report Style Tabular, Form-Like, Mailing Label, Form Letter, Group Left, Group

Above, Matrix, Matrix with Group

• Query Type Choose whether to build a SQL query or an Express query.
• Data Enter a SELECT statement to retrieve the report data.
• Displayed Fields Select the fields that you want to display in the output.
• Fields to Total Select the fields that you want to summarize.
• Labels for Fields Alter the labels that appear for each field and the width of each field.
• Template Select the template that you want to use for this report. A template contains standard information such as company logo, date, and so on.

Note: The above steps are different for each report style.

Group Left & Have an additional page: ‘Groups’

Group Above styles

Matrix Reports styles Have 3 additional pages:
 ‘Matrix Rows’ ‘Columns’ ‘Cells’ Mailing Label & Have 4 pages: ‘Report Style’ ‘Data’
Form Letter styles ‘Text’ ‘Template’

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.

Triggers in Reports:

Types of Triggers:

Formula Triggers: Formula triggers are PL/SQL functions that populate columns of type Formula.

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.

Action Triggers: Action triggers are used to perform user-defined action. These triggers do not return any value.

Validation Triggers: Validation triggers are PL/SQL functions that are executed when a parameter value is entered and the cursor moves to the next parameter. These triggers return Boolean value TRUE / FALSE.

Report Triggers: Report triggers enable execution of PL/SQL functions at specific time during execution and formatting of report.

Trigger Firing Sequence: 

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

After Parameter:
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.