How to get the record count using XML Data Template

Bommi

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,

Bommi

Bommi

Can i get any help on this

Bommi

Can I get any help on this

User_CJ4N9

1. Add following just before </dataStructure>

<element name="g_count" value="G_ORG_ITEMS.INVENTORY_ITEM_ID" dataType="number" function="count"/>

2. And then in your package XX_ITEMS_PKG handle the condition check for g_count before submitting bursting program like

if g_count>1 then 

submit bursting program

end if

Thanks,

SB