0 Replies Latest reply: Jul 9, 2008 3:30 PM by 590783 RSS

    Lexical in SELECT clause

    590783
      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