, , ,

SRW Package in Oracle Report

SRW (Sql Report Writer) Package is a built in package in Oracle Reports Builder. It is a collection of PL/SQL constructs that include many functions, procedures, and exceptions you can reference in any of your libraries or reports.
The PL/SQL provided by the SRW package enables you to perform such actions as change the formatting of fields, run reports from within other reports, create customized messages to display in the event of report error, and execute SQL statements. There are nearly 70 functions, procedures, and exceptions are there in this package. Here I am giving brief information and uses of few important functions, procedures, and exceptions.
SRW.MESSAGE:
It is a Procedure that displays a message with the message number and text that you specify. It is mainly used to debug a report in Reports Builder.
SRW.MESSAGE(msg_number NUMBER, msg_text CHAR);
Example:
function foo return boolean is
begin
  if :sal < 0 then
    SRW.MESSAGE(100, 'Found a negative salary. Check the EMP table.');
    raise SRW.PROGRAM_ABORT;
  else
    :bonus := :sal * .01;
  end if;
  return(true);
end;




SRW.PROGRAM_ABORT:


This exception stops the report execution and raises the following error message: REP-1419: PL/SQL program aborted. SRW.PROGRAM_ABORT stops report execution when you raise it.


SRW.DO_SQL:


This procedure executes the specified SQL statement from within Reports Builder. The SQL statement can be DDL (statements that define data), or DML (statements that manipulate data). DML statements are usually faster when they are in PL/SQL, instead of in SRW.DO_SQL.


Since you cannot perform DDL statements in PL/SQL, the SRW.DO_SQL packaged procedure is especially useful for performing them within Reports Builder.


Example:




FUNCTION CREATETABLE RETURN BOOLEAN IS
BEGIN
  SRW.DO_SQL('CREATE TABLE TEST_EMP (EMPNO NUMBER NOT NULL
    PRIMARY KEY, SAL NUMBER (10,2)) PCTFREE 5 PCTUSED 75');
    RETURN (TRUE);
   EXCEPTION
    WHEN SRW.DO_SQL_FAILURE  THEN
    SRW.MESSAGE(100, 'ERROR WHILE CREATING TEST_EMP TABLE.');
    RAISE
    SRW.PROGRAM_ABORT;
END;






SRW.DO_SQL_FAILURE:


Reports Builder raises this exception when the SRW.DO_SQL packaged procedure fails. This exception stops the report execution and raises the following error message:


REP-1425: Error running DO_SQL package – REP-msg ORA-msg.


SRW.GET_REPORT_NAME:


This function returns the file name of the report being executed.


SRW.GET_REPORT_NAME (report_name);


Example:




function AfterPForm return boolean is
    my_var varchar2(80);
  BEGIN
    SRW.GET_REPORT_NAME (my_var);
    SRW.MESSAGE(0,'Report Filename = '||my_var);
   RETURN (TRUE);
  END;






SRW.RUN_REPORT:


This procedure synchronously executes the specified report within the context of the currently running report.


SRW.RUN_REPORT (“report=test.rdf … “)


SRW.SET_FIELD:


This procedure sets the value of a character, number, or date field. This is useful when you want to conditionally change a field’s value.


SRW.SET_FIELD (object_id, text CHAR | number NUM | date DATE);


Example:


Suppose you want to conditionally change the number of a field, based on each employee’s salary. In the format trigger for the field, you could type the following:




FUNCTION CHGFIELD RETURN BOOLEAN IS
TMP NUMBER;
 BEGIN
  if :sal >= 5000 then
  tmp := :sal * 1.10;
  srw.set_field (0, tmp);
 else
  srw.set_field (0, 4000);
 end if;
 RETURN (TRUE);
END;





SRW.SET_FIELD should be used only to change the contents of a field’s datatype, not change the field to a different datatype.


Others in Brief:


  • SRW.SET_FONT_FACE: This procedure specifies font face for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_FACE(‘arial’);

  • SRW.SET_FONT_SIZE: This procedure specifies font size for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_SIZE(10);

  • SRW.SET_FONT_STYLE: This procedure specifies font style for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_STYLE(SRW.ITALIC_STYLE);

  • SRW.SET_FORMAT_MASK: This procedure specifies the format mask for the DATE or NUMBER field. SRW.SET_FORMAT_MASK(‘mask’);

  • SRW.SET_TEXT_COLOR: This procedure specifies the global text color of the CHAR, DATE, or NUMBER field. SRW.SET_TEXT_COLOR(‘color’);






/by 
, 
Bind & Lexical Parameters in Reports


Bind Parameters:


Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses. You create a bind reference by entering a colon (:) followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.


Lexical Parameters:


Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH. You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL.




/by 
, , 
Calling a Report from a Form


Using RUN_PRODUCT Built-in we can call a Report from a Form.


It will take 7 Parameters as follows


1) Product name :: REPORT/GRAPHICS


2) Module name  :: Report name


3) Communication Mode :: Synchronous /Asynchronous


SYN :: we cannot work with the Form until and unless you closes the Report.


ASYN:: We can work with Form & Reprot (Navigate)


4) Exec :: Batch/Run time


Batch :: it will fetch all records at a time


Run time :: It will fetch only one Record at a time


5) File name :: database/Filesystem


6) Parameters :: Report Parameters


7) Display :: For Graphics (Mostly null)


Ex :: If we have :p_deptno parameter then we can call as Follows


RUN_PRODUCT (‘REPORT’,’C:TEST1.rep’,SYN,BATCH,DATABASE,p_deptno,’ ‘);




/by 
, , 
User Exits in Oracle Reports


We can integrate Oracle reports with Oracle Application Object Library, and run them as concurrent programs from your forms or through standard request submission.


These are the user exits available in Oracle Reports that makes AOL integration.


FND SRWINIT
FND SRWEXIT
FND FORMAT_CURRENCY
FND FLEXIDVAL
FND FLEXSQL


FND SRWINIT


    

  • This is a User Exit which sets your profile option values and allows Oracle AOL user exits to detect that they have been called by oracle repots.
    • 

  • It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing).
    • 

  • FND SRWINIT also allows your report to use the correct organization automatically.
    • 

  • Can be used in BEFORE-REPORT Trigger.
    • 

  • Syntax is : SRW.USER_EXIT(‘FND SRWINIT’);
    • 



FND SRWEXIT


    

  • This user exit ensures that all the memory allocated for AOL user exits has been freed up properly.
    • 

  • Can be used in AFTER- REPORT Trigger.
    • 

  • Syntax is : SRW.USER_EXIT(‘FND SRWEXIT’);
    • 



FND FORMAT_CURRENCY


    

  • To format the currency amount dynamically depending upon the precision of the actual currency value, standard precision, users profile values and location (country) of the site.
    • 

  • You obtain the currency value from the database into an Oracle Reports column.
    • 

  • Define another Oracle Reports column, a formula column of type CHAR, which executes the FORMAT_CURRENCY user exit to format the currency value.
    • 

  • A displayed field has this formula column as its source so that the formatted value is automatically copied into the field for display.
    • 



Syntax:


FND FORMAT_CURRENCY
CODE=:column containing currency
DISPLAY_WIDTH=field width for display
AMOUNT=:source column name
DISPLAY=:display column name
[MINIMUM_PRECISION=:P_MIN_PRECISION]
[PRECISION={STANDARD|EXTENDED}]
[DISPLAY_SCALING_FACTOR=:P_SCALING_FACTOR]


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”]




/by 
, , , 
Registering Reports in Oracle Applications


Steps Required for Registering a Simple Report


1. Create a Report using Report Builder.


2. Compile and copy .RDF file in module specific directory.


3. Register the executable with System Administrator Module.


4. Define the Concurrent Program.


5. Assign the executable to Concurrent Program.


6. Assign the Concurrent Program to Request Group.


7. Assign the Request Group to the Responsibility.


8. Assign the Responsibility to the User. 


Registering Parametric Reports


1. Create a Report using Report Builder with parameters.


2. Compile and copy .RDF file in module specific directory.


3. Register the executable with System Administrator Module.


4. Define the Value set to validate the parameters.


5. Define the Concurrent Program.


6. Assign the executable to Concurrent Program.


7. Define Parameters.


8. Assign Value Set to the Parameters.


9. Assign bind parameter of yours to the TOKEN.


Note: Token is used to map bind parameters with the formal parameters of the Concurrent Program.


10. Assign the Concurrent Program to Request Group.


11. Assign the Request Group to the Responsibility.


12. Assign the Responsibility to the User.


To reference the values of Prior Parameters of a particular program into the values of other parameter is based on the Value Set. The value of the 1st parameter is to be referenced in where clause of the 2nd Parameter.


WHERE Deptno = :$FLEX$.First_Parameter




/by