Forum Stats

  • 3,838,237 Users
  • 2,262,343 Discussions


How to get the record count using XML Data Template

Bommi Member Posts: 715 Bronze Badge
edited Jul 27, 2020 1:37AM in Reports

Hi Team,

We are using R12.2.3

We have a requirement to fetch the Items from Inventory which are created in a period of time (We will pass creation date from and To as Concurrent Program parameters). And, if there are any records, then bursting program need to be called and then email that list(It is excel output). They need email only when there are any records fetched.

So, we created XML Data Template as below

<?xml version = '1.0' encoding = 'UTF-8'?><dataTemplate name="XX_ITEMS" defaultPackage="XX_ITEMS_PKG" Version="1.0">  <properties>      <property name="debug_mode" value="on" /> </properties> <parameters><parameter name="ORGANIZATION_ID" dataType="NUMBER"/>     <parameter name="CREATION_DATE_FROM" dataType="DATE"/>     <parameter name="CREATION_DATE_TO" dataType="DATE"/> </parameters>  <dataQuery><sqlStatement name="Q_ORG_ITEMS">       <![CDATA[SELECT    msib.inventory_item_id,msib.segment1, msib.creation_date,msib.last_update_date,msib.descriptionfrom mtl_system_item_b where 1=1 and creation_date between :CREATION_DATE_FROM and :CREATION_DATE_TOand organization_id=:ORGANIZATION_IDORDER BY    msib.ORGANIZATION_ID,    mm_org_f.item]]>    </sqlStatement>  </dataQuery>  <dataTrigger name="beforeReportTrigger" source="XX_ITEMS_PKG.BeforeReport"/> <dataStructure>     <group name="G_ORG_ITEMS" source="Q_ORG_ITEMS"><element name="ORGANIZATION_ID" value="ORGANIZATION_ID"/><element name="INVENTORY_ITEM_ID" value="INVENTORY_ITEM_ID"/><element name="segment1" value="segment1"/><element name="DESCRIPTION" value="DESCRIPTION"/><element name="CREATION_DATE" value="CREATION_DATE"/><element name="LAST_UPDATE_DATE" value="LAST_UPDATE_DATE"/></group> </dataStructure> <dataTrigger name="afterReportTrigger" source="XX_ITEMS_PKG.afterreport"/></dataTemplate>

Now, in package 'afterReportTrigger' function we have to call Bursting program only when the above query returns data. Or else, dont call.

So, question is how to fetch the records count of G_ORG_ITEMS group?


Thanks in Advance,