API

What is an API?


The Application Programmatic Interface or API is a PL/SQL packaged procedure that can be


used as an alternative to Application online forms for Application data entry and


manipulation.


The advantage of using an API to update application data is that users can maintain HRMS


information without using manual entry in Oracle application forms.


APIs insure the integrity of the interrelationship of Oracle Applications tables. You can


modify application information without detailed knowledge of the database structure, because


the API updates all the interrelated tables.


APIs help protect customer-specific data from database structural changes. As Oracle changes


table structures, the APIs are modified correspondingly, so that data can continue to be


modified without error or code updates.










How do I use an API to upload data?


The API package usually contains multiple procedures to insert, update, or delete application


data; the API procedures are executed when they are called by other PL/SQL modules, by a


direct SQL*Plus call, or through a front end such as the Data Pump.


The APIs do not issue commits. When a user-defined procedure or script calls the API


procedure, the calling module must manage transaction commit statements. The calling


module should also address exception handling and logging to delivered exception tables.


The API package should never be modified. Oracle cannot support modified APIs nor


systems that have used modified APIs, because HRMS data integrity could be compromised.


APIs can be used as building blocks called within an extensive customer-defined package.


Unique functionality can be addressed with API User Hooks or other user code, providing


that Oracle HRMS data is modified only through the call to the delivered API.
 
 
 
Different API's Present in Oracle Applications :


FND_PROGRAM.EXECUTABLE ( )

FND_PROGRAM.DELETE_EXECUTABLE( )

FND_PROGRAM.REGISTER( )

FND_PROGRAM.DELETE_PROGRAM( )

FND_PROGRAM.PARAMETER( )

FND_PROGRAM.DELETE_PARAMETER( )

FND_PROGRAM.INCOMPATIBILITY( )

FND_PROGRAM.DELETE_INCOMPATIBILITY( )

FND_PROGRAM.REQUEST_GROUP( )

FND_PROGRAM.DELETE_GROUP( )

FND_PROGRAM.ADD_TO_GROUP( )

FND_PROGRAM.REMOVE_FROM_GROUP( )

FND_REQUEST.SUBMIT_REQUEST( )

FND_CONCURRENT.WAIT_FOR_REQUEST( )

FND_REQUEST.SET_PRINT_OPTIONS ( )

FND_GLOBAL.USER_IDFND_GLOBAL.APPS_INITIALIZE(user_id in number,resp_id in number,resp_appl_id in number);

FND_GLOBAL.LOGIN_ID

FND_GLOBAL.CONC_LOGIN_ID

FND_GLOBAL.PROG_APPL_ID

FND_GLOBAL.CONC_PROGRAM_ID

FND_GLOBAL.CONC_REQUEST_ID

FND_PROFILE.PUT(name,value)

FND_PROFILE.GET(name IN varchar2,value out varchar2)

API’S IN APPS

PO

PO_CUSTOM_PRICE_PUB

PO_DOCUMENT_CONTROL_PUB

PO_DOC_MANAGER_PUB

PO_WFDS_PUB

AP

AP_NOTES_PUB

AP_WEB_AUDIT_LIST_PUB

INV

INV_COST_GROUP_PUB

INV_ITEM_CATALOG_ELEM_PUB

INV_ITEM_CATEGORY_PUB

INV_ITEM_PUB

INV_ITEM_REVISION_PUB

INV_ITEM_STATUS_PUB

INV_LOT_API_PUB

INV_MATERIAL_STATUS_PUB

INV_MOVEMENT_STATISTICS_PUB

INV_MOVE_ORDER_PUB

INV_PICK_RELEASE_PUB

INV_PICK_WAVE_PICK_CONFIRM_PUB

INV_RESERVATION_PUB

INV_SERIAL_NUMBER_PUB

INV_SHIPPING_TRANSACTION_PUB

Report Triggers Coding...

How to do Report Triggers Coding?


How to write Formula Trigger in Reports? 

function STATUSFormula return Char is



Cursor lpn is


SELECT meaning from mfg_lookups where lookup_type = 'WIP_JOB_STATUS' and lookup_code = :status_type;


l_num_lpn varchar2(50);


begin


open lpn;


fetch lpn into l_num_lpn;


close lpn;


return(l_num_lpn);


end;




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



How to write Formula Trigger in Reports?


function CF_ORG_NAMEFormula return VARCHAR2 is


V_ORG_NAME VARCHAR2(100);


begin


SELECT organization_name


INTO V_ORG_NAME


FROM org_organization_definitions


WHERE organization_id = (fnd_profile.value('ORG_ID'));






return(V_ORG_NAME);


RETURN NULL;


exception


when others then


return(null);


end;






~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
How to write Function in Trigger in Reports?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~








function CF_UsernameFormula return VARCHAR2 is


uname VARCHAR2(100);






begin






select user_name into uname from fnd_user where user_id=(fnd_profile.value('USER_ID'));


return(uname);


RETURN NULL;








nd;




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~








function CF_trx_qtyFormula return Number is






v_Trx_quantity number;


v_lot_number number;


v_expiration_date date;


begin






Select moq.transaction_quantity


, mln.lot_number


, mln.expiration_date


into


v_Trx_quantity


, :cp_lot_number


, :cp_expiration_date






from mtl_onhand_quantities moq, mtl_lot_numbers mln






where MLN.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID


AND moq.organization_id = mln.organization_id


AND MOQ.ORGANIZATION_ID =:P_ORGANIZATION_ID


AND MOQ.INVENTORY_ITEM_ID =:INVENTORY_ITEM_ID;


--AND moq.locator_id =:locator_id1;






-- :cp_expiration_date :=v_expiration_date;


-- :cp_lot_number :=v_lot_number;


SRW.MESSAGE(021, v_Trx_quantity );


SRW.MESSAGE(022, :cp_lot_number );


SRW.MESSAGE(023, :cp_expiration_date );






return(v_Trx_quantity);




exception


when others then


return(null);


SRW.MESSAGE(024,'Error out!!!' );


end;

What is Materialized View?

If we need to do some complex calculations, some agregrated functions like group calculations, we used Materiazed View.
A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases.


When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.

Syantax :
CREATE MATERIALIZED VIEW schema.name

PCTFREE integer

PCTUSED integer

TABLESPACE tablespace_name

BUILD IMMEDIATE

REFRESH FAST | FORCE ON COMMIT / DEMAND

USING INDEX / USING NO INDEX

INITRANS integer

STORAGE CLAUSE

AS (SQL statement);

Advance SQL Queries....

1. How to Query for finding the nth maximum salary ?

Select distinct a.sal from emp a where (&n-1) = (select count (unique sal ) from emp b where b.sal > a.sal)



2. How to Query for finding the nth minimum salary ?


Select a.sal from emp1 a where (&n-1) = (select count (unique sal) from emp1 b where b.sal < a.sal)



3. What is Query for deleting the duplicate rows in table ?



Delete from emp where rowed not in (select max(rowid) from emp group by empno)



4. What is Query for finding the 2nd maximum ?


Select empno from emp where sal = (select max(sal) from emp where sal <> (select max(sal) from emp));


5. What is Query for finding the 2nd minimum ?

Select empno from emp where sal = (select min(sal) from emp where sal <> (select min(sal) from emp));




6. How to Query to find the cumulative total?


Select sum(x.sal) from emp1 x, emp1 y where y.rowid >= x.rowed group by y.row order by sum(x.sal)




7. How to Query to find the alternate rows ?
Select empno, ename from emp where (empno,rownum) in (select empno, mod(rownum,2) from emp);



8. How to Query to find the other alternate rows ?



Select * from emp where rowed not in (select empno, ename from emp where (empno,rownum) in (select empno,mod(rownum,2) from emp));




9. How to Query to delete alternate rows ?



Delete from emp where (empno,rownum) in (select empno, mod(rownum,2) from emp);

10. What is Query to print some text with the column values ?



Select empno,deptno, decode (mod(rownum,5),0,'*****') print from emp;




11. what is Query to get column without specifying the column name ?



Select &n, &q from emp where deptno = 10;


12. Query to delete duplicate rows by leaving one row deleted on specific condition ?
Delete from emp where deptno = 10 and rowid not in (select min(rowid) from emp where deptno = 10);


13. Query to delete duplicate rows but leaving one row undeleted ?


Delete from emp where deptno = 10 and rowid not in (select min(rowid) from emp where deptno = x.deptno);



14. What is Query to select all columns, rowid with out specifying the column name ?


Select rowid, &a from emp;


15. What is Query to print static text?
Select empno, sal, 'Maximum from emp where sal = (select max(sal) from emp)

More About Oracle Reports....

What are Oracle Report Triggers :

As a general rule, any processing that will affect the data retrieved by the report should be performed in the Before Parameter Form or After Parameter Form triggers. (These are the two report triggers that fire before anything is parsed or fetched.) Any processing that will not affect the data retrieved by the report can be performed in the other triggers.

Report Builder has five global report triggers. You cannot create new global report triggers. The trigger names indicate at what point the trigger fires: 

Before Report trigger :

Description The Before Report trigger fires before the report is executed but after queries are parsed and data is fetched.

Definition Level report

On Failure

Displays an error message and then returns to the place from which you ran the report .

After Report trigger :

Description The After Report trigger fires after you exit the Runtime Previewer, or after report output is sent to a specified destination, such as a file, a printer, or a mailid. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report

completed successfully.

Definition Level report

On Failure

Does not affect formatting because the report is done. You can raise a message, though, to indicate that the report did not run correctly. For example, you could put the system time in a variable in the Before Report trigger and then compare it against the system time in the After Report trigger . If the report took longer than a certain time to run, you could raise an error.

Usage Notes

* The After-Report trigger does not fire when you are in the Live Previewer.

Between Pages trigger :

Description The Between Pages trigger fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Runtime Previewer or Live Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.

Definition Level  report

On Failure

Displays an error message when you try to go to the page for which the trigger returned FALSE. The pages subsequent to the page that returned FALSE are not formatted. If the trigger returns FALSE on the last page, nothing happens because the report is done formatting. The Between Pages trigger does not fire before the first page. If the trigger returns FALSE on the first page, the first page is displayed, but, if you try to go to the second page, an error message is displayed.

Before Parameter Form trigger :

Description The Before Parameter Form trigger fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters.

Definition Level report

On Failure

Displays an error message and then returns to the place from which you ran the report .

After Parameter Form trigger :

Description The After Parameter Form trigger fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data.

Definition Level report

On Failure

Returns to the Runtime Parameter Form. If the Form is suppressed, then returns to place from which you ran the report .

Report trigger must explicitly return TRUE or FALSE. The following table describe what happens when the report trigger returns FALSE.

REPORT TRIGGER	WHAT HAPPENS IF TRIGGER RETURNS FALSE
Before Report	Displays an error message and then returns to the place from where the report was executed.
After Report	Does not effect formatting because the report is done.You can raise a message, though, to indicate that the report did not run correctly.
Between Pages	Displays an error message when you try to go to the page for which the trigger returned FALSE. The pages subsequent to the page that returned FALSE are not formatted.
Before Parameter Form	Displays an error message and then returns to the place from where the report was executed.
After Parameter Form	Returns to the Runtime Parameter Form.If the form is suppressed, then returns to the place from where the report was executed.

Report trigger order of execution :

The order of events when a report is executed is as follows:

Before Parameter Form trigger is fired.

1 Runtime Parameter Form appears (if not suppressed).

2 After Parameter Form trigger is fired (unless the user cancels from the Runtime Parameter Form).

3 Report is “compiled.”

4 Queries are parsed.

5 Before Report trigger is fired.

6 SET TRANSACTION READONLY is executed (if specified via the READONLY argument or setting).

7 The report is executed and the Between Pages trigger fires for each page except the last one. (Note that data can be fetched at any time while the report is being formatted.) COMMITs can occur during this time due to any of the following-- user exit with DDL, SRW.DO_SQL with DDL, or if ONFAILURE=COMMIT, and the report fails.

8 COMMIT is executed (if READONLY is specified) to end the transaction.

9 After Report trigger is fired.

10 COMMIT/ROLLBACK/NOACTION is executed based on what was specified via the ONSUCCESS argument or setting.

Cautions

·        In steps 4 through 9, avoid DDL statements that would modify the tables on which the report is based. Step 3 takes a snapshot of the tables and the snapshot must remain valid throughout the execution of the report . In steps 7 through 9, avoid DML statements that would modify the contents of the tables on which the report is based. Queries may be executed in any order, which makes DML statements unreliable (unless performed on tables not used by the report).

·        If you specify READONLY, you should avoid DDL altogether. When you execute a DDL statement (e.g., via SRW.DO_SQL or a user exit), a COMMIT is automatically issued. If you are using READONLY, this will prematurely end the transaction begun by SET TRANSACTION READONLY.

Report trigger restrictions:

·       If you are sending your report output to the Runtime Previewer or Live Previewer, you should note that some or all of the report triggers may be fired before you see the report output. For example, suppose that you use SRW.MESSAGE to issue a message in the Between Pages trigger when a condition is met. If there are forward references in the report (e.g., a total number of pages displayed before the last page), Report Builder may have to format ahead to compute the forward references. Hence, even though you have not yet seen a page, it may already have been formatted and the trigger fired.

·        In report triggers, you can use the values of report-level columns and parameters.

            For example, you might need to use the value of a parameter called COUNT1 in a condition (e.g., IF :COUNT1       = 10).
Note, though, that you cannot reference any page-dependent columns (i.e., a column with a Reset At of   Page) or columns that rely on page-dependent columns.

·        In the Before and After Parameter Form, and Before and After Report triggers, you can set the values of parameters (e.g., give them a value in an assignment statement, :COUNT1 = 15). In the Before and After Report triggers, you can also set the values of report-level, placeholder columns.

·       In the Between Pages trigger, you cannot set the values of any data model objects.

Note also that the use of PL/SQL global variables to indirectly set the values of columns or parameters is not recommended. If you do this, you may get unpredictable results.

·        If you run a report from Report Builder Runtime (i.e., not the command line or SRW.RUN_REPORT), you should commit database changes you make in the Before Parameter Form, After Parameter Form, and Validation triggers before the report runs. When running in this way, these triggers will share the parent process’ database connection. When the report is actually executed, however, it will establish its own database connection.

·       A lexical reference cannot be used to create additional bind variables after the After Parameter Form trigger fires. For example, suppose you have a query like the following (note that the WHERE clause is replaced by a lexical reference): SELECT ENAME, SAL FROM EMP &where_clause

If the value of the WHERE_CLAUSE parameter contains a reference to a bind variable, you must specify the value in the After Parameter Form trigger or earlier.

You would get an error if you supplied the following value for the parameter in the Before Report trigger. If you supplied this same value in the After Parameter Form trigger, the report would run.

·       GROUP FILTER :

Description: A group filter is a PL/SQL function that determines which records to include in a group , if the Filter Type property is PL/SQL. The function must return a boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current record is included or excluded from the report. You can access group filters from the Object Navigator, the Property Palette (the PL/SQL Filter property), or the PL/SQL Editor.

Definition Level group

On Failure Excludes the current record from the group .

·       FORMULA :

Description: Formulas are PL/SQL functions that populate formula or placeholder columns. You can access the PL/SQL for formulas from the Object Navigator, the PL/SQL Editor, or the Property Palette (i.e., the PL/SQL Formula  property). A column of datatype Number can only have a formula that returns a value of datatype NUMBER. A column of Datatype Date can only have a formula that returns a value of datatype DATE. A column of Datatype Character can only have a formula that returns a value of datatype CHARACTER, VARCHAR, or VARCHAR2.

Definition Level column

On Failure No value is returned for the column.

VALIDATION TRIGGER :

Description: Validation triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. (Notice that this means each validation trigger may fire twice when you execute the report.) Validation triggers are also used to validate the Initial Value

property of the parameter. The function must return a boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the user is  returned to the Runtime Parameter Form. You can access validation triggers from the Object Navigator, the PL/SQL Editor, or the Property Palette (Validation Trigger property).

Definition Level parameter

On Failure The user is returned to the parameter value in the Runtime Parameter Form

where they can either change it or cancel the Runtime Parameter Form.

·       FORMAT TRIGGER :

Description Format triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object. The function must return a Boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current instance of the object is included or excluded from the report output. You can access format triggers from the Object Navigator, the Property Palette, or the PL/SQL Editor.

Definition Level layout object

On Failure Excludes the current instance of the object from the output.

Usage Notes

* Format triggers do not affect the data retrieved by the report. For example, if a format trigger returns FALSE for a field, the data for the field is retrieved even though the field does not appear in the output.

* If a format trigger suppresses report output on the last page of the report, the last page will still be formatted and sent to the appropriate output and the page will be included in the total number of pages.

·       ACTION TRIGGER :

Description Action triggers are PL/SQL procedures executed when a button is selected in the Runtime Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL. You can access action triggers from the Object Navigator, the Property Palette (PL/SQL Trigger property), or the PL/SQL Editor.

Definition Level button

Usage Notes

*  PL/SQL action triggers cannot be tested in the Live Previewer because the buttons are not active there. You must use the Runtime Previewer (choose View Runtime Preview from the Live Previewer).

*  You cannot use the PL/SQL Interpreter to debug action triggers because it is not available from the Runtime Previewer and buttons cannot be activated in the Live Previewer. To get around this, you can move your action trigger code to a report trigger to test it from the Live Previewer.

Which are User Exits used in Apps Reports?

·       FND FLEXSQL

Call this user exit to create a SQL fragment usable by your report to tailor your SELECT statement that retrieves flexfield values. This fragment allows you to SELECT flexfield values or to create a WHERE, ORDER BY, GROUP BY, or HAVING clause to limit or sort the flexfield values returned by your SELECT statement. You call this user exit once for each fragment you need for your select statement. You define all flexfield columns in your report as type CHARACTER even though your table may use NUMBER or DATE or some other datatype.

Syntax:

FND FLEXSQL

CODE=”flexfield code”

APPL_SHORT_NAME=”application short name”

OUTPUT=”:output lexical parameter name”

MODE=”{ SELECT | WHERE | HAVING | ORDER BY}”

[DISPLAY=”{ALL | flexfield qualifier | segment number}”]

[SHOWDEPSEG=”{Y | N}”]

[NUM=”:structure defining lexical” |

MULTINUM=”{Y | N}”]

[TABLEALIAS=”code combination table alias”]

[OPERATOR=”{ = | < | > | <= | >= | != | ”||” |

BETWEEN | QBE}”]

[OPERAND1=”:input parameter or value”]

[OPERAND2=”:input parameter or value”]

FND FLEXIDVAL

Call this user exit to populate fields for display. You pass the key flexfields data retrieved by the query into this exit from the formula column. With this exit you display values, descriptions and prompts by passing appropriate token (any one of VALUE, DESCRIPTION, APROMPT or LPROMPT).

Syntax:

FND FLEXIDVAL

CODE=”flexfield code”

APPL_SHORT_NAME=”application short name”

DATA=”:source column name”

[NUM=”:structure defining source column/lexical”]

[DISPLAY=”{ALL|flexfield qualifier|segment number}”]

[IDISPLAY=”{ALL|flexfield qualifier|segment

number}”]

[SHOWDEPSEG=”{Y | N}”]

[VALUE=”:output column name”]

[DESCRIPTION=”:output column name”]

[APROMPT=”:output column name”]

[LPROMPT=”:output column name”]

[PADDED_VALUE=”:output column name”]

[SECURITY=”:column name”]

·       FND SRWINIT

You call the user exit FND SRWINIT from your Before Report Trigger.FND SRWINIT fetches concurrent request information and sets up profile options. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).

·       FND SRWEXIT

You call the user exit FND SRWEXIT from your After Report Trigger. FND SRWEXIT frees all the memory allocation done in other Oracle Applications user exits. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).

Basic Implementation Steps

Step 1 : Call FND SRWINIT from your Before Report Trigger

You call the user exit FND SRWINIT from your Before Report Trigger. FND SRWINIT fetches concurrent request information and sets up profile options. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).

Step 2 : Call FND SRWEXIT from your After Report Trigger

You call the user exit FND SRWEXIT from your After Report Trigger. FND SRWEXIT frees all the memory allocation done in other Oracle Applications user exits. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).

Step 3 : Call FND FLEXSQL from the Before Report Trigger

You need to pass the concatenated segment values from the underlying  code combinations table to the user exit so that it can display appropriate data and derive any descriptions and values from switched value sets as needed. You get this information by calling the AOL user exit FND FLEXSQL from the Before Report Trigger. This user exit

populates the lexical parameter that you specify with the appropriate column names/SQL fragment at run time. You include this lexical parameter in the SELECT clause of your report query. This enables the report itself to retrieve the concatenated flexfield segment values. You call this user exit once for each lexical to be set. You do not display this

column in your report. You use this ”hidden field” as input to the FND FLEXIDVAL user exit call. This user exit can also handle multi–structure flexfield reporting by generating a decode on the structure column. If your report query uses table joins, this user exit can prepend your code combination table name alias to the column names it returns.

SELECT &LEXICAL alias, column

becomes, for example,

SELECT SEGMENT1||’\n’||SEGMENT2 alias, column

Note: Oracle Reports needs the column alias to keep the name of column fixed for the lexicals in SELECT clauses. Without the alias, Oracle Reports assigns the name of the column as the initial value of the lexical and a discrepancy occurs when the value of the lexical changes at run time.

Step 4 : Restrict report data based upon flexfield values

You call the user exit FND FLEXSQL with MODE=”WHERE” from the Before Report Trigger. This user exit populates a lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the WHERE clause of your report query. You call this user exit once for each lexical to be changed. If your report query uses table joins, you can have this user exit prepend your code combination table name alias to the column names it returns.

WHERE tax_flag = ’Y’ and &LEXICAL < &reportinput

becomes, for example,

WHERE tax_flag = ’Y’ and T1.segment3 < 200    The same procedure can be applied for a HAVING clause.

Step 5 : Order by flexfield columns

You call the user exit FND FLEXSQL with MODE=”ORDER BY” from the Before Report Trigger. This user exit populates the lexical  parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the ORDER BY clause of your report query. You call this user exit once for each lexical to be changed. If your report query uses table joins, you can have this user exit prepend your code combination table name alias to the column names it returns.

ORDER BY column1, &LEXICAL

becomes, for example,

ORDER BY column1, segment1, segment3

Step 6 : Display flexfield segment values, descriptions, and prompts

Create a Formula Column (an Oracle Reports 6.0 data construct that enables you to call a user exit). Call the user exit FND FLEXIDVAL as the Formula for this column. This user exit automatically fetches more complicated information such as descriptions and prompts so that you do not have to use complicated table joins to the flexfield tables. Then

you create a new field (an Oracle Reports 6.0 construct used to format and display Columns), assign the Formula Column as its source, and add this field to your report using the screen painter. You need to include this field on the same Repeating Frame (an Oracle Reports 6.0 construct found in the screen painter that defines the frequency of data

retrieved) as the rest of your data, where data could be actual report data, boilerplate, column headings, etc. The user exit is called and flexfield information retrieved at the frequency of the Repeating Frame that contains your field. In the report data case, the user exit is called and flexfield information retrieved once for every row retrieved with your query. All flexfield segment values and descriptions are displayed left justified. Segment values are not truncated, that is, the Display Size defined in Define Key Segments screen is ignored. Segment value descriptions are truncated to the description size (if one is displayed) or the concatenated description size (for concatenated segments)

defined in the form.

System Parameters:

BACKGROUND       Is whether the report should run in the foreground or the background.

COPIES                      Is the number of report copies that should be made when the report is printed.

CURRENCY              Is the symbol for the currency indicator (e.g., "$").

DECIMAL                 Is the symbol for the decimal indicator (e.g., ".").

DESFORMAT           Is the definition of the output device's format (e.g., landscape mode for a printer).  This                            parameter is used when running a report in a character-mode environment, and when                          sending a bitmap     report to a file (e.g. to create PDF or HTML output).

DESNAME                Is the name of the output device (e.g., the file name, printer's name, mail userid).

DESTYPE                  Is the type of device to which to send the report output (screen, file, mail, printer, or                                                 screen using PostScript format).

MODE                        Is whether the report should run in character mode or bitmap.

ORIENTATION       Is the print direction for the report (landscape, portrait, default).

PRINTJOB                 Is whether the Print Job dialog box should appear before the report is run.

THOUSANDS            Is the symbol for the thousand's indicator (e.g., ",").

Other Columns of Reports:

Placeholder columns :

A placeholder is a column for which you set the datatype and value in PL/SQL that you define.  You can set the value of a placeholder column in the following places:

·       the Before Report Trigger, if the placeholder is a report-level column

·       a report-level formula column, if the placeholder is a report-level column

·       a formula in the placeholder's group or a group below it (the value is set once for each record of the group) 

Summary columns:

A summary column performs a computation on another column's data.  Using the Report Wizard or Data Wizard, you can create the following summaries:  sum, average, count, minimum, maximum, % total.  You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries:  first, last, standard deviation, variance.

Note:  For group reports, the Report Wizard and Data Wizard create n summary fields in the data model for each summary column you define:  one at each group level above the column being summarized, and one at the report level.  For example, if a report is grouped by division, and further grouped by department, then a summary column defined for a salary total would create fields for the sum of salaries for each division and each department group (group-level summaries), and the sum of all salaries (report-level summary).

Formula columns :

A formula column performs a user-defined computation on another column(s) data, including placeholder columns. Formula columns should not be used to set values for parameters.

Description  Formulas are PL/SQL functions that populate formula or placeholder columns

.  You can access the PL/SQL for formulas from the Object Navigator, the PL/SQL Editor, or the Property Palette (i.e., the PL/SQL Formula property).

A column of datatype Number can only have a formula that returns a value of datatype NUMBER.  A column of Datatype Date can only have a formula that returns a value of datatype DATE.  A column of Datatype Character can only have a formula that returns a value of datatype CHARACTER, VARCHAR, or VARCHAR2.

Definition : Level  column

On Failure:  No value is returned for the column.

Thanks Lot
Nilesh