Oracle Bursting Program

 1)Create Data definition.

2)Create Bursting File.

3)Template

4 ) After Trigger.

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

<dataTemplate name="QAG_HR_ISP_SEPARATION" description = "QAG HR Separation Report - ISP" Version="1.0" defaultPackage="xxqr_uip_utility_pkg">

  <properties>

<property name="xml_tag_case"      value="UPPER"/>

<property name="debug_mode"        value="ON"/>

<property name="include_rowsettag" value="FALSE"/>

  </properties>

   <parameters>

   </parameters>

<dataQuery>

<sqlstatement name="SEPARATION_REPORT">

<![CDATA[

SELECT papf.employee_number STAFF_NUMBER ,

  INITCAP (papf.title)

  || ' '

  || papf.first_name

  || ' '

  || DECODE (TRIM (papf.middle_names), NULL, TRIM (papf.last_name), TRIM (papf.middle_names)

  || ' '

  || TRIM (papf.last_name)) STAFF_NAME ,

  TO_CHAR(qr_final_settlement_cpnt.qr_service_date( papf.person_id) ,'DD-MON-YYYY') HIRE_DATE ,

  hr_general.decode_organization(paaf.organization_id) DEPARTMENT ,

  TO_CHAR(ppos.actual_termination_date,'DD-MON-YYYY') LWD ,

  TO_CHAR(ppos.LAST_UPDATE_DATE,'DD-MON-YYYY') LAST_UPDATE_DATE ,

  FU.USER_NAME LAST_UPDATED_BY ,

  'wassem@qatarairways.com.qa' EMAIL_TO ,

  'bvijayakumaran@qatarairways.com.qa' EMAIL_CC

FROM per_all_people_f papf,

  per_all_assignments_f paaf,

  per_periods_of_service ppos,

  FND_USER FU

WHERE papf.person_id              = paaf.person_id

AND primary_flag                  = 'Y'

AND ppos.person_id                = papf.person_id

AND ppos.actual_termination_date IS NOT NULL

AND FU.USER_ID                    = PPOS.LAST_UPDATED_BY

AND (trunc(ppos.actual_termination_date) = trunc(sysdate-1)

              or (ppos.actual_termination_date is not null and trunc(ppos.LAST_UPDATE_DATE) = trunc(sysdate-1) and trunc(ppos.actual_termination_date) < trunc(sysdate-1))

             )

AND ppos.actual_termination_date IS NOT NULL

--AND TRUNC(ppos.LAST_UPDATE_DATE)  = TRUNC(sysdate-1)

AND ppos.date_start               =

  (SELECT MAX (date_start)

  FROM per_periods_of_service

  WHERE person_id = ppos.person_id

  )

AND TRUNC(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date

AND TRUNC(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date

AND employee_number NOT LIKE 'TSS%'

AND employee_number NOT IN ('99996','99997','IT001')

AND employee_number     IN

  (SELECT papf.employee_number

  FROM PER_ALL_PEOPLE_F PAPF,

    PER_ALL_ASSIGNMENTS_F PAAF ,

    PAY_ELEMENT_TYPES_F patf ,

    PAY_ELEMENT_ENTRIES_F peef

  WHERE PAPF.PERSON_ID =PAAF.PERSON_ID

  AND patf.element_name='QAG_ISP_INFO'

  AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE

  AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

  AND PATF.ELEMENT_TYPE_ID =PEEF.ELEMENT_TYPE_ID

  AND PEEF.ASSIGNMENT_ID   =PAAF.ASSIGNMENT_ID

  AND TRUNC(sysdate) BETWEEN peef.effective_start_date AND peef.effective_end_date

 -- AND current_employee_flag='Y'

  )

 ORDER BY 1,1 

   ]]> 

</sqlstatement>

<sqlstatement name="ROOT_DATA">

<![CDATA[

     SELECT TRUNC(SYSDATE) FROM DUAL

   ]]> 

</sqlstatement>

</dataQuery>  

<dataTrigger name="afterReport" source="xxqr_uip_utility_pkg.submit_bursting()"></dataTrigger>  

 <dataStructure>

   <group name="ROOT_DATA_GROUP" dataType="varchar2" source="ROOT_DATA">

       <group name="DATA_GROUP" dataType="varchar2" source="SEPARATION_REPORT">

             <element name="STAFF_NUMBER"      dataType="varchar2" value="STAFF_NUMBER"/>

             <element name="STAFF_NAME"        dataType="varchar2" value="STAFF_NAME"/>

             <element name="HIRE_DATE"         dataType="DATE"     value="HIRE_DATE"/>

             <element name="DEPARTMENT"        dataType="varchar2" value="DEPARTMENT"/>

             <element name="LWD"               dataType="varchar2" value="LWD"/>

             <element name="LAST_UPDATED_BY"   dataType="varchar2" value="LAST_UPDATED_BY"/>

             <element name="LAST_UPDATE_DATE"  dataType="varchar2" value="LAST_UPDATE_DATE"/>

             <element name="EMAIL_TO"          dataType="varchar2" value="EMAIL_TO"/>

             <element name="EMAIL_CC"          dataType="varchar2" value="EMAIL_CC"/>

       </group>

   </group>

</dataStructure>

</dataTemplate>

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

<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">

<xapi:request select="/QAG_HR_ISP_SEPARATION/ROOT_DATA_GROUP">

<xapi:delivery>

<xapi:email id="123" server="qrsmtp.gmail.com" port="25" from="erpservices@gmail.com" reply-to="erpservices@gmail.com">

<xapi:message id="123" to="bvijayakumaran@gmail.com" bcc="bvijayakumaran@gmail.com" attachment="true" content-type="excel" subject="Separation Report">

Hi,

Please find the attached Separation Report List.

</xapi:message>

</xapi:email>

</xapi:delivery>

<xapi:document output="QAG_HR_ISP_SEPARATION" output-type="excel" delivery="123">

<xapi:template type="rtf" location="xdo://QAIR.QAG_HR_ISP_SEPARATION.en.US/?getSource=true">

</xapi:template>

</xapi:document>

</xapi:request>

</xapi:requestset>

FUNCTION submit_bursting

  RETURN BOOLEAN;

FUNCTION submit_bursting

  RETURN BOOLEAN

IS

  l_req_id          NUMBER;

  p_conc_request_id NUMBER := fnd_global.conc_request_id;

BEGIN

  l_req_id   := fnd_request.submit_request ('XDO' ,'XDOBURSTREP' ,'' ,'' ,FALSE ,'Y' ,p_conc_request_id ,'N' ,CHR (0) ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' );

  IF l_req_id > 0 THEN

    RETURN TRUE;

  ELSE

    RETURN FALSE;

  END IF;

END submit_bursting;

Staff Number	Staff  Name	Department	Last Working Day
FSTAFF_NUMBER	STAFF_NAME	DEPARTMENT	LWDE

SQL Running in OAF Page

 

1)    Create one Webui page.

package qair.oracle.apps.qaghr.selfservice.dataload.webui;

 

import java.io.Serializable;

import oracle.apps.fnd.common.VersionInfo;

import oracle.apps.fnd.framework.OAApplicationModule;

import oracle.apps.fnd.framework.OAException;

import oracle.apps.fnd.framework.webui.OAControllerImpl;

import oracle.apps.fnd.framework.webui.OAPageContext;

import oracle.apps.fnd.framework.webui.beans.OAWebBean;

 

public class QAGGenericDataLoadCO extends OAControllerImpl

{

  public static final String RCS_ID = "$Header$";

  public static final boolean RCS_ID_RECORDED = VersionInfo.recordClassVersion("$Header$", "%packagename%");

 

  public void processRequest(OAPageContext pageContext, OAWebBean webBean)

  {

    super.processRequest(pageContext, webBean);

  }

 

  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)

  {

    super.processFormRequest(pageContext, webBean);

    if (pageContext.getParameter("QryButton") != null) {

      OAApplicationModule am = pageContext.getApplicationModule(webBean);

      String Query = pageContext.getParameter("TxtQuery");

      Serializable[] param = { Query };

      am.invokeMethod("getquery", param);

      throw new OAException("Record saved successfully", (byte)3);

    }

  }

}

 

 

package qair.oracle.apps.qaghr.selfservice.dataload.server;

 

import java.io.PrintStream;

import java.sql.CallableStatement;

import java.sql.SQLException;

import oracle.apps.fnd.framework.OAException;

import oracle.apps.fnd.framework.OAViewObject;

import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;

import oracle.apps.fnd.framework.server.OADBTransaction;

import oracle.jbo.Row;

 

public class QAGGenericDataLoadAMImpl extends OAApplicationModuleImpl {

    /**This is the default constructor (do not remove)

     */

    public QAGGenericDataLoadAMImpl() {

    }

 

    public static void main(String[] args)

  {

    launchTester("qair.oracle.apps.qaghr.selfservice.dataload.server", "QAGGenericDataLoadAMLocal");

  }

 

  public QAGGenericDataLoadVOImpl getQAGGenericDataLoadVO1()

  {

    return (QAGGenericDataLoadVOImpl)findViewObject("QAGGenericDataLoadVO1");

  }

 

  public void createrow()

  {

    OAViewObject DataVO = getQAGGenericDataLoadVO1();

    Row row = DataVO.createRow();

    DataVO.setCurrentRow(DataVO.last());

    DataVO.next();

    DataVO.insertRow(row);

   // row.setNewRowState(-1);

  }

 

  public void getquery(String Query) {

    System.out.println("Query:" + Query);

    OADBTransaction txn = getOADBTransaction();

    CallableStatement cs = null;

    try {

      cs = txn.createCallableStatement(Query, 1);

      cs.executeUpdate(Query);

      cs.close();

      txn.commit();

    } catch (SQLException e) {

      throw new OAException("Error while executing the Query " + e.getMessage(), (byte)0);

    }

  }

}