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.
DATA Template
<parameter name.............................../>
<parameter name="P_BREAK_COLUMN" dataType="varchar2"/>
<parameter name="P_BREAK" dataType="varchar2" defaultValue="gcc.segment3"/>
</parameters>
<lexicals>
</lexicals>
<dataQuery>
<sqlStatement name="Q_DATA">
<![CDATA[
SELECT &P_BREAK C_BREAK,
gcc.segment4 ,
exp.je_category ,
exp.vendor_number ,
exp.vendor_name ,
exp.invoice_number ,
exp.invoice_date ,
SUM (NVL (exp.accounted_dr, 0) - NVL (exp.accounted_cr, 0)) accounted_amt,
ffv4.description account_desc
FROM xxcus.XXGL_XGLOGEXP exp,
gl_code_combinations gcc,
fnd_flex_values_vl ffv4,
fnd_flex_value_sets ffs4
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 ,
gcc.segment4 ,
exp.je_category ,
exp.vendor_number ,
exp.vendor_name ,
exp.invoice_number ,
exp.invoice_date ,
ffv4.description
]]>
</sqlStatement>
</dataQuery>
<dataTrigger name="beforeReportTrigger" source="XXGL_XGLOGEXP_PKG.BeforeReport"/>
<dataStructure>
<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
BEGIN
IF P_BREAK_COLUMN = 'Location' THEN
P_BREAK := 'gcc.segment2';
ELSIF p_BREAK_COLUMN = 'Department' THEN
P_BREAK := 'gcc.segment3';
END IF;
fnd_file.put_line(fnd_file.log,'P_BREAK --> '||P_BREAK);
END;
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 a??User Parametersa?? section. Those parameters can be assigned different values in the a??before reporta?? 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 section are in a read only format. Any changes by the beforeReport trigger, to the variables in the parameter section are not visible to the SQL in the section.
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.
<parameters>
a?|a?|a?|a?|a?|a?|a?|a?|..
<!-- <parameter name="P_BREAK" dataType="varchar2" defaultValue="gcc.segment3"/> -->
</parameters>
<lexicals>
</lexicals>
<dataQuery>
<sqlStatement name="Q_DATA">
<
