1 Reply Latest reply: Mar 28, 2012 4:33 PM by Jai001 RSS

    Lexical Parameter not working

    Jai001
      Hello,

      In EBS Release 12.1.2, I am trying to use a lexical parameter for my WHERE condition and it is not getting substituted correctly.

      To simplify the question, I have even attempted a simple SQL too and that has given the same java.lang.NullPointerException error. I have browsed the forum and could not get this resolved. Help is appreciated -

      Data Definition is:
      <?xml version = '1.0' encoding = 'UTF-8'?>
      <dataTemplate name="XXGLPAWIPANA" defaultPackage="XXGLPAWIPANA_XMLP_PKG" version="1.0">
      <properties>
      <property name="debug_mode" value="on"/>
      </properties>
      <parameters>
      <parameter name="P_OU_ID" dataType="number"> </parameter>
      <parameter name="LP_WHERE_PROJECT_STATUS" dataType="character" defaultValue="AND 1=1"> </parameter>
      </parameters>
      <dataQuery>
      <sqlStatement name="Q_MAIN">
      <![CDATA[
      SELECT invoice_id
      FROM ap_invoices
      WHERE rownum < 10
      &LP_WHERE_PROJECT_STATUS
      ]]>
      </sqlStatement>
      </dataQuery>
      <dataTrigger name="afterParameterFormTrigger" source="XXGLPAWIPANA_XMLP_PKG.beforereport"/>
      <dataStructure>
      <group name ="G_MAIN" source="Q_MAIN">
      <element name="invoice_id" value="invoice_id"/>
      </group>
      </dataStructure>
      </dataTemplate>

      Package Spec is:
      CREATE OR REPLACE PACKAGE XXGLPAWIPANA_XMLP_PKG AUTHID CURRENT_USER AS

      P_OU_ID NUMBER;
      LP_WHERE_PROJECT_STATUS VARCHAR2(100) := 'AND pay_group_lookup_code = ''EMPLOYEE''';

      FUNCTION BeforeReport RETURN BOOLEAN;

      END XXGLPAWIPANA_XMLP_PKG;

      Package Body is:
      CREATE OR REPLACE PACKAGE BODY XXGLPAWIPANA_XMLP_PKG AS

      FUNCTION BeforeReport RETURN BOOLEAN IS

      BEGIN

      fnd_file.put_line(fnd_file.log, '1 LP_WHERE_PROJECT_STATUS = ' || LP_WHERE_PROJECT_STATUS);

      IF P_OU_ID = 101 THEN
      LP_WHERE_PROJECT_STATUS := 'AND pay_group_lookup_code = ''EMPLOYEE''';
      fnd_file.put_line(fnd_file.log, '2 LP_WHERE_PROJECT_STATUS = ' || LP_WHERE_PROJECT_STATUS);
      ELSE
      LP_WHERE_PROJECT_STATUS := 'AND ppa_o.project_status_code = ' || P_PROJECT_STATUS;
      fnd_file.put_line(fnd_file.log, '3 LP_WHERE_PROJECT_STATUS = ' || LP_WHERE_PROJECT_STATUS);
      END IF;

      fnd_file.put_line(fnd_file.log, 'LP_WHERE_PROJECT_STATUS = ' || LP_WHERE_PROJECT_STATUS);

      RETURN (TRUE);

      END BeforeReport;

      END XXGLPAWIPANA_XMLP_PKG;

      The error I get is shown below:

      XDO Data Engine Version No: 5.6.3
      Resp: 20420
      Org ID : 101
      Request ID: 1831696
      All Parameters: P_OU_ID=101
      Data Template Code: XXGLPAWIPANA
      Data Template Application Short Name: XX
      Debug Flag: N
      {P_OU_ID=101}
      Calling XDO Data Engine...
      [032812_073820102][][STATEMENT] Start process Data
      [032812_073820103][][STATEMENT] Process Data ...
      [032812_073820104][][STATEMENT] Executing data triggers...
      [032812_073820104][][STATEMENT] BEGIN
      XXGLPAWIPANA_XMLP_PKG.P_OU_ID := :P_OU_ID ;
      XXGLPAWIPANA_XMLP_PKG.LP_WHERE_PROJECT_STATUS := :LP_WHERE_PROJECT_STATUS ;

      :XDO_OUT_PARAMETER := 1;
      END;

      [032812_073820104][][STATEMENT] 1:101 :
      [032812_073820104][][STATEMENT] 2:null :
      [032812_073820109][][STATEMENT] Executing data triggers...
      [032812_073820109][][STATEMENT] Declare
      l_flag Boolean;
      BEGIN
      l_flag := XXGLPAWIPANA_XMLP_PKG.beforereport ;
      if (l_flag) then
      :XDO_OUT_PARAMETER := 1;
      end if;
      end;

      [032812_073820121][][STATEMENT] P_OU_ID
      [032812_073820121][][STATEMENT] LP_WHERE_PROJECT_STATUS
      [032812_073820121][][STATEMENT] Writing Data ...
      [032812_073820125][][STATEMENT] &LP_WHERE_PROJECT_STATUS
      java.lang.NullPointerException
           at oracle.apps.xdo.dataengine.DataTemplateParser.getObjectVlaue(DataTemplateParser.java:1754)
           at oracle.apps.xdo.dataengine.DataTemplateParser.replaceSubstituteVariables(DataTemplateParser.java:1473)
           at oracle.apps.xdo.dataengine.XMLPGEN.processSQLDataSource(XMLPGEN.java:456)
           at oracle.apps.xdo.dataengine.XMLPGEN.writeData(XMLPGEN.java:445)
           at oracle.apps.xdo.dataengine.XMLPGEN.writeGroupStructure(XMLPGEN.java:308)
           at oracle.apps.xdo.dataengine.XMLPGEN.processData(XMLPGEN.java:273)
           at oracle.apps.xdo.dataengine.XMLPGEN.processXML(XMLPGEN.java:215)
           at oracle.apps.xdo.dataengine.XMLPGEN.writeXML(XMLPGEN.java:254)
           at oracle.apps.xdo.dataengine.DataProcessor.processDataStructre(DataProcessor.java:390)
           at oracle.apps.xdo.dataengine.DataProcessor.processData(DataProcessor.java:355)
           at oracle.apps.xdo.oa.util.DataTemplate.processData(DataTemplate.java:334)
           at oracle.apps.xdo.oa.cp.JCP4XDODataEngine.runProgram(JCP4XDODataEngine.java:294)
           at oracle.apps.fnd.cp.request.Run.main(Run.java:157)

      ---------------------------------------------------------------------------
      Start of log messages from FND_FILE
      ---------------------------------------------------------------------------
      1 LP_WHERE_PROJECT_STATUS =
      2 LP_WHERE_PROJECT_STATUS = AND pay_group_lookup_code = 'EMPLOYEE'
      LP_WHERE_PROJECT_STATUS = AND pay_group_lookup_code = 'EMPLOYEE'
      ---------------------------------------------------------------------------
      End of log messages from FND_FILE
      ---------------------------------------------------------------------------
        • 1. Re: Lexical Parameter not working
          Jai001
          Resolved issue by dropping the parameter definition for the lexical parameter. I was trying to follow one of the standard FA reports which is not yet enabled (set for a future release) and that had a parameter definition for the lexical variable. But then saw that another R12 enabled FA report just referenced the lexical parameter in the package spec and body (no reference in the parameter definition in the XML).