Hi,

I have a data template with a lexical parameter that is used in the SELECT caluse of the query.

DATA Template
<parameters>
<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.

Can someone help?

Thanks