QUEST4APPS

Triggers in XML Data Template

—

by

Arun Raj
in , ,

Today I wanted to show you how to define triggers in XML Data Template. This is in continuation to my blog on generating XML Report from XML Data Template. Check the same here.

So here is my requirement:

  1. Populate a temporary table with the data based on the parameters passed to the Concurrent Program
  2. Fetch the data for the report from the Temporary table.
  3. Truncate the Table after the XML Report is generated

Temporary Table Name – XXRAJ_TEMP_TABLE

Here is how the XML Data Template will look like. I have also marked the changes in this new file.

xml_template_definition_01

Lets talk about how to define the trigger.

  1. Define a pl/sql Package xxraj_xml_triggers_pkg
  2. Define functions beforeReport and afterReport in the Package. This is very important. You have to define it as functions and not as procedures
  3. Define the parameters as variables in the Package Specification. In our case p_orgid and p_vendorid are the parameters. So you need to define these as variables in the Package Spec
  4. Create the package body and define the functions. The beforeReport function will insert the invoive data into the temp table and the afterReport function will truncate the data.

You may download the Package Spec ( xxraj_xml_triggers_pkg_ps ) and Package Body ( xxraj_xml_triggers_pkg_pb) for your reference.

Now looking at the changes in the XML Data Template, you will see that there are following changes:

Default Package
The package that you are using for the Trigger should be defined as the “default Package” in the XML Data Template.

SQL Statement
The SQL Statement has been changed to fetch the data from the temporary table rather than from the AP_INVOICES_ALL Table

Before Report Trigger
Just above the <dataStructure> element, I have defined a new trigger. Since the trigger is defined before the <dataStructure>, this will act as the before Report Trigger. You will notice that I have given the name and source of the trigger.

<dataTrigger name=”beforeReport” source=”xxraj_xml_triggers_pkg.beforeReport(:p_OrgId,:p_VendorId)”/>

name: This can be any value. However to make it easier to understand it will be a good practice to give the name as “beforeReport” to indicate that the trigger fires before the report.
source: Here you give the “package.function” that is to be called for execution. The parameters can be passed to this function.

After Report Trigger
Just below the <dataStructure> element, I have defined the after report Trigger. Since the trigger is defined after the <dataStructure>, this will act as the after Report Trigger.

<dataTrigger name=”afterReport” source=”xxraj_xml_triggers_pkg.afterReport(:p_OrgId)”/>

Upload the new XML file to the Data Definition. Everything else remains the same.

This was a very simple example to show how to define Triggers in XML Data Template. Hope this helps. Feel free to ask any questions you may have. As always your feedback is highly appreciated.

Comments

42 responses to “Triggers in XML Data Template”

  1. Bharathi Avatar
    Bharathi

    Hello

    What do i need to fill in the afterreport function?

    Please help

    Reply
    1. ArunRaj Avatar
      ArunRaj

      Hello..

      You can add any post report processing that you may want to do in the After Report Function.
      For example, you can call bursting program for report delivery.

      If you can ask me any specific questions that you may have, I will be glad to help.

      Thanks,
      Arun

      Reply
  2. hayati89hayati Avatar
    hayati89hayati

    I am calling 2 functions from the package. Is it possible to do the twice in a row?

    Reply
    1. ArunRaj Avatar
      ArunRaj

      As far as I know, you can have one function for “Before Report Trigger” and one function for “After Report Trigger”.
      Any processing that you may want to do should be within those functions.

      Let me know if that answers your question. If you have questions about a specific functionality or requirement that you are trying to implement, please include those and I will try my level best to help you.

      Thanks,
      Arun

      Reply
      1. hayati89hayati Avatar
        hayati89hayati

        Hi Arun, thank you very much for your reply.

        Background :
        I have one BeforeReportTrigger function in my Employee Package which works just fine. Then i created another function in the same package. Once i put it in the XML, it straightaway crashed at certain point.

        What i did was similar to the below:

        Tried a few more ways to fix it, but to no avail. Appreciate your input on this.

        Reply
        1. ArunRaj Avatar
          ArunRaj

          Do you think you can send me a sample file? Send it to arunraj@quest4apps.com
          Send me the XML Template file, the packages and the functions that you are trying to call from the XML template.
          If there are any sensitive data, you can delete it. I just need the bare minimum just to see how you are calling the functions.

          I will try to go through it and help you with the requirements. Sorry for the late reply.

          Thanks,
          Arun

          Reply
  3. SATHISH Avatar
    SATHISH

    my before & after trigger not firing…plz help..i am pasting my data template & objects

    ————————————————————————————————-

    CREATE OR REPLACE PACKAGE APPS.XXHLDATATEMREP_PKG
    AS

    p_org_id number;

    function beforeReport (p_org_id number) return boolean;

    function afterReport return boolean;

    procedure XXHLDATATEMREP_PROC (p_org_id number);
    end;
    /
    ——————————————————————————-
    CREATE OR REPLACE PACKAGE BODY APPS.XXHLDATATEMREP_PKG
    AS

    Function beforeReport (p_org_id number) return boolean
    as
    v_a number:=10;
    BEGIN

    v_a:=p_org_id;

    INSERT INTO XXHLDATATEMREP_TAB
    values(v_a);
    COMMIT;
    RETURN TRUE;
    EXCEPTION
    WHEN OTHERS THEN
    RETURN FALSE;
    END;

    Function afterReport return boolean
    as
    v_a number:=10;
    BEGIN

    –v_a:=p_org_id;

    INSERT INTO XXHLDATATEMREP_TAB
    values(v_a);
    COMMIT;
    RETURN TRUE;
    EXCEPTION
    WHEN OTHERS THEN
    RETURN FALSE;
    END;

    procedure XXHLDATATEMREP_PROC (p_org_id number)
    is

    begin

    insert into XXHLDATATEMREP_TAB
    values(p_org_id);

    commit;
    end;

    end;
    /

    ————————————————————————————————

    CREATE TABLE XXHL.XXHLDATATEMREP_TAB
    (
    ORG_ID NUMBER
    )

    Reply
    1. ArunRaj Avatar
      ArunRaj

      Hi Satish,

      Can you send the Data template file (xml) to my email id @ arunraj@quest4apps.com? I will take a look at it.
      Also include the Functions and package specs.

      Thanks,
      Arun

      Reply
  4. venkat Avatar
    venkat

    Hi,
    how to use the output parameter from function in query template,

    Let say
    .. p_deptno

    ….. select * from emp where deptno = &p_dept_no

    here p_deptno is IN parameter to function and p_dept_no is OUT parameter from function. If I pass 10 to function, the output will be 20 (p_deptno + 10 ) . That 20 should be used in sql query.

    How can I achieve it.

    Reply
    1. ArunRaj Avatar
      ArunRaj

      Hi,

      A very interesting question. Thank you for asking.
      As per Oracle’s documentation it is possible. Please take a look at the below link. If you still have questions let me know.
      Search for “Employee Listing Data Template” in the page.

      http://docs.oracle.com/cd/E12844_01/doc/bip.1013/e12187/T421739T434255.htm

      Hope this helps. If you like the blog it would be great if you can Like / Share the blog.

      Thanks,
      Arun

      Reply
  5. Rahul Avatar
    Rahul

    I have been wondering from a long time about how to take care of other 3 triggers which are available in rdf reports in xml reports (Before/After parameter form, Between pages?

    Reply
    1. ArunRaj Avatar
      ArunRaj

      Hi Rahul,

      I have not personally tried it. However I believe you can have as many triggers as you want.
      You can have two triggers before the SQL and two after the SQL or two before SQL and 1 after the SQL.

      Check this document from Oracle

      Thanks,
      Arun

      Reply
      1. Rahul Avatar
        Rahul

        Arun,

        I understand that we can have as many triggers as we can but when will those fire? Before report and after report fire at specific times, before query parsing and after generating xml, respectively. Now if I add another trigger, when will it fire?

        Rahul

        Reply
        1. ArunRaj Avatar
          ArunRaj

          Rahul,

          Before SQL and After SQL in the sequence in which you define it your data model. Thats my understanding.

          Thanks,
          Arun

          Reply
  6. Arif Avatar
    Arif

    Hi Sir,
    Nice Information you provide. But I have a problem, I Create a function in package that accept date parameter ,I define parameter as varchar2 because I am using canonical date in my function. but when I run the report on apps it either show invalid character or format does not match . Will you please help me to resolve it.I am working on it since last 3 days.

    Reply
    1. ArunRaj Avatar
      ArunRaj

      Hi Arif,

      Can you send me the xml schema definition, the package, concurrent program parameter definition and also the log file for the process?
      Email it to me at arunraj@quest4apps.com

      Thank you for the encouraging words.

      Regards,
      Arun

      Reply
  7. Jitender Avatar
    Jitender

    Hi Arun,

    I am trying to insert data in temp table in before report but before report trigger is not firing. Please help.

    Thanks
    Jitender

    Reply
  8. Jitender Avatar
    Jitender

    Sending you the template and pkg on arunraj@quest4apps.com

    Reply
  9. Jitender Sharma Avatar
    Jitender Sharma

    Hi Arun,

    My before report trigger is not firing in XML publisher report. Please help.

    Sending you the all the objects on your mentioned id : arunraj@quest4apps.com.

    thanks,

    Jitender

    Reply
    1. ArunRaj Avatar
      ArunRaj

      Hi Jitender,

      I was on a sabbatical. So have not checked the emails for some time. Did u resolve this?

      Thanks,
      Arun

      Reply
      1. Jitender Sharma Avatar
        Jitender Sharma

        Ji Arun,
        I am still facing the same issue.

        Reply
      2. Bharat Avatar
        Bharat

        hello Arun,
        we have a data template, and calling afterreport trigger where calling a package.function with parameters, where it went in error… we are not using before report trigger.
        Regards
        Bharat

        Reply
  10. lokesh Avatar
    lokesh

    Hi Arun,

    I have created data template with Data triggers(Before Report and After Report), But i am getting the below error.
    ORA-06550: line 4, column 12:
    PLS-00103: Encountered the symbol “;” when expecting one of the following:

    ( – + case mod new not null

    continue avg count current exists max min prior sql stddev
    sum variance execute forall merge time timestamp interval
    date
    pipe

    <an alternatively

    Please look into it and let me know how to resolve this issue.

    Here are my data template and package.



    Package

    create or replace PACKAGE XX_abc_PKG
    as
    –used to Intialize the org
    FUNCTION beforeReport
    RETURN BOOLEAN;

    — used to call the bursting program
    FUNCTION AfterReport
    RETURN BOOLEAN;

    end XX_abc_PKG;

    create or replace PACKAGE BODY XX_abc_PKG
    AS
    FUNCTION beforeReport
    RETURN BOOLEAN
    IS
    BEGIN
    apps.mo_global.set_policy_context(‘S’,121);
    RETURN TRUE;
    EXCEPTION
    when OTHERS then
    –FND_FILE.put_line (fnd_file.LOG,’Error Occured in Beforreport : ‘|| SQLERRM);
    RETURN FALSE;
    END beforeReport;
    —

    —
    FUNCTION AfterReport
    RETURN BOOLEAN
    is
    v_req_id number;

    v_conc_req_id number;
    begin
    v_conc_req_id:=fnd_global.conc_request_id;
    v_req_id := fnd_request.submit_request(‘XDO’,
    ‘XDOBURSTREP’,
    NULL,
    NULL,
    FALSE,
    ‘Y’,
    v_conc_req_id,
    ‘Y’
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

    if v_req_id is not null then
    FND_FILE.put_line (fnd_file.LOG,’Bursting program Submitted successfully’);
    return (TRUE);
    else
    FND_FILE.put_line (fnd_file.LOG,’Error at Bursting’);
    return (false);
    end if;

    exception
    when OTHERS then
    FND_FILE.PUT_LINE (FND_FILE.log,’exception at Bursting: ‘||SQLERRM);
    RETURN FALSE;

    end AFTERREPORT;

    end XX_abc_PKG;

    Reply
    1. ArunRaj Avatar
      ArunRaj

      Hi Lokesh,

      Has this been resolved? I will take a look at it and let you know.

      Thanks,
      Arun

      Reply
  11. Srinivasan Avatar
    Srinivasan

    can we define beforeparameterform trigger in data template?

    Reply
    1. Arun Raj Avatar
      Arun Raj

      I am not aware of that.
      You can have as many “Before Report” triggers you want.

      Thanks,
      Arun

      Reply
  12. Law Avatar
    Law

    Is it possible to have 2 afterParameterFormTrigger in one xml file?

    Reply
    1. Arun Raj Avatar
      Arun Raj

      Hi,

      AS far as I know. yes it is possible to have multiple “before reprot” and “after report” triggers.
      Here is a link to a pretty good documentation

      Thanks,
      Arun

      Reply
  13. Raghu Avatar
    Raghu

    Hi Arun,
    Can we use Before parameter and After Parameter trigger in data template reports? If Yes! what would be the appropriate event name to use here.

    Reply
    1. Arun Raj Avatar
      Arun Raj

      Hi Raghu,

      You can use both.

      Before Report will fire before the SQL is executed.
      After report will fire after the SQL is executed.

      If you want actions to be performed before SQL is executed, then use Before Report Trigger. For eg, populating data into a temporary table etc.
      If you want actions to be performed after SQL is executed, then use Before Report Trigger. For eg, sending email, bursting etc.

      Cheers,
      Arun

      Reply
  14. deepali bafna Avatar
    deepali bafna

    Hi,

    My before report function is not getting called, Can you help?

    Reply
    1. Arun Raj Avatar
      Arun Raj

      Hi,

      Did you get it working? If not please send me an email (arun.rajs@outlook.com) with your package definition and the xml data definition.

      Thanks,
      Arun

      Reply
  15. Rahul Chauhan Avatar
    Rahul Chauhan

    I am not using before report trigger to fetch data for my report. My report is generated using queries defined in data template. My requirement is to populate a custom table with values generated from the query. Is there a way we can pass the values generated by queries or tag values to default package?

    Reply
  16. ianadankarch Avatar
    ianadankarch

    Hello Run,

    I am launching a rural IEX lambt collection treatment with the customer rental number. this generates an XML file (a mail that needs to be sent to customers). Now I want to save in a DOCUBASE table the information of my treatment except that I do not have the right request_id so it is not back up. could you help me pleaze

    thank you in advance

    Reply
    1. Arun Raj Avatar
      Arun Raj

      Are you generating this from Oracle ERP?

      Reply
  17. doifode Avatar
    doifode

    I want to pass on a P_CONC_REQUEST_ID into the sql query.How should i pass on that.
    I hav already takn it in the before report .
    now i want to pass it into the sql query.

    FUNCTION beforeReport
    RETURN BOOLEAN AS
    P_CONC_REQUEST_ID NUMBER;
    BEGIN

    P_CONC_REQUEST_ID := fnd_global.conc_request_id;

    FND_FILE.PUT_LINE(FND_FILE.log,’P_CONC_REQUEST_ID:-‘||P_CONC_REQUEST_ID);

    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;

    Reply
    1. Arun Raj Avatar
      Arun Raj

      I believe we connected over email and you were able to get this working.

      Reply
  18. Venu Avatar
    Venu

    Hi Arun,

    My before report function is updating the global parameter but the update is not reflecting in the data template. And when I return false in after report function it is failing without XML generation.

    Please help as soon as possible

    Reply
  19. Sateesh Avatar
    Sateesh

    Hi All,
    How to pass select query column value to after report trigger.
    Thanks
    Sateesh

    Reply
  20. puja Avatar
    puja

    I am using after report trigger for calling bursting program.bursting program is running fine but parent prorgam is going in error

    Reply
  21. Shashikant Avatar
    Shashikant

    Hi Arun,
    Can you please help me with I want to pass Data Query column to after report package. How to do this?
    Thanks,
    Shashikant

    Reply
  22. Abdul Samad Avatar
    Abdul Samad

    Hi, When you are using TRUNCATE in package body then what is the use of input parameter p_orgid in afterReport function.

    I think p_orgid is not required in afterReport function.

    Reply

Questions? Comments? Suggestions? Let us know!! Like / Subscribe / Follow for more updates.

Latest Posts

Check out these latest posts that you may be interested in.