Oracle Analytics and AI

Vinithasaraswathy

Summary

Trigger not firing in xml reports

Content

Hii all,

Trigger is not firing in xml reports.Following is my xml.When i call the function in sql it is working.

<?xml version="1.0" encoding="UTF-8"?>

<dataTemplate name="XX_INX_TESTFNL" description="Demo Details" defaultpackage="inx_emp_test5"

version="1.0">

<parameters>

<parameter name="p_department_id" datatype="number"/>

</parameters>

<dataQuery>

<sqlStatement name="DQ1">

<![CDATA[SELECT first_name,last_name,salary FROM empl  WHERE department_id =:p_department_id ]]>

</sqlStatement>

</dataQuery>

<dataTrigger name="beforeReport" source="inx_emp_test5.beforeReport(:p_department_id)"/>

<dataStructure>

<group name="G_1" source="DQ1">

<element name="first_name" value="first_name"/>

<element name="last_name" value="last_name"/>

<element name="salary" value="salary"/>

</group>

</dataStructure>

</dataTemplate>

Thank you

Vinithasaraswathy

create or replace PACKAGE inx_emp_test5

       AS 

          p_department_id NUMBER;

          where_clause VARCHAR2(3200);

   FUNCTION beforeReport( p_department_id IN NUMBER)

           return boolean;

  /* FUNCTION afterReport( p_department_id IN NUMBER)

          return boolean;*/

END inx_emp_test5;

create or replace PACKAGE BODY inx_emp_test5

IS

FUNCTION beforeReport(p_department_id IN NUMBER)

      RETURN BOOLEAN AS

  

BEGIN FOR invRec IN

   (SELECT first_name,last_name,salary

    FROM empl

     WHERE department_id = p_department_id

)

LOOP

insert into  xx_temp_table ( first_name,last_name,salary  )

                         VALUES ( invRec.first_name, invRec.last_name, invRec.salary); END LOOP;

COMMIT;

RETURN TRUE;

EXCEPTION WHEN OTHERS

        THEN fnd_file.put_line(fnd_file.log,'Error in beforeReport Proc.');                    

                   fnd_file.put_line(fnd_file.log,'Error : '||SQLERRM);

      RETURN FALSE;

 

END beforeReport;

END inx_emp_test5;

this is my package