Lexical Reference Problems
By Tim Dexter-Oracle on Jul 14, 2008
Been in an interesting forum/email thread over the last few days with Naushad
I have a data template with a lexical parameter that is used in the SELECT caluse of the query.
<parameter name="P_BREAK_COLUMN" dataType="varchar2"/>
<parameter name="P_BREAK" dataType="varchar2" defaultValue="gcc.segment3"/>
SELECT &P_BREAK C_BREAK,
SUM (NVL (exp.accounted_dr, 0) - NVL (exp.accounted_cr, 0)) accounted_amt,
FROM xxcus.XXGL_XGLOGEXP exp,
WHERE exp.code_combination_id = gcc.code_combination_id
AND ffs4.flex_value_set_id(+) = ffv4.flex_value_set_id
AND ffv4.flex_value(+) = gcc.segment4
AND ffs4.flex_value_set_name = 'ACCT_AFF'
GROUP BY &P_BREAK ,
<dataTrigger name="beforeReportTrigger" source="XXGL_XGLOGEXP_PKG.BeforeReport"/>
<group name="G_DATA" dataType="varchar2" source="Q_DATA">
<element name="C_BREAK" dataType="varchar2" value="C_BREAK"/>
<element name="SEGMENT4" dataType="varchar2" value="SEGMENT4"/>
<element name="JE_CATEGORY" dataType="varchar2" value="JE_CATEGORY"/>
The beforeReportTrigger looks like this:
FUNCTION BeforeReport RETURN BOOLEAN IS
IF P_BREAK_COLUMN = 'Location' THEN
P_BREAK := 'gcc.segment2';
ELSIF p_BREAK_COLUMN = 'Department' THEN
P_BREAK := 'gcc.segment3';
fnd_file.put_line(fnd_file.log,'P_BREAK --> '||P_BREAK);
The parameter starts with a default of "gcc.segment3". On execution of this report in EBS, the beforeReportTrigger fires and changes the value of P_BREAK to "gcc.segment2" (I can see that change in the concurrent reqs LOG file).
But when the SQL is executed, it still holds the default value. The fact that beforeReport has changed the value to segment2 is not visible to the SQL in the data XML template.
I have to admit I was fishing around for answer from development but Naushad continued to tinker with it and found the answer. Another gap in our doc we need to fill!
I found the source of my problem.
I have worked in Reports 9i for many years (since 1996 when it was version 4.5) and when I transition to XML data template seems like I have to unlearn some of that.
In Reports 9i, we define parameters in the “User Parameters” section. Those parameters can be assigned different values in the “before report” trigger. Essentially, we change the value of the parameter and the new values are visible to the SQL in the Data Model.
In XML data template, the parameters defined in the
I was able to resolve it by commenting out the declaration of parameter P_BREAK.
P_BREAK is defined as variable in the package spec and the beforeReport trigger changes the value as required.
<!-- <parameter name="P_BREAK" dataType="varchar2" defaultValue="gcc.segment3"/> -->
SELECT &P_BREAK C_BREAK,
FROM gl_code_combinations gcc, …..
The take away from the solution - the parameters in the data template are not updatable and you can declare public variables in your before report trigger and reference them inside the data template without the need to declare them.