2 Replies Latest reply on Jul 27, 2020 5:37 AM by Bommi

    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.description
      from mtl_system_item_b 
      where 1=1 
      and creation_date between :CREATION_DATE_FROM and :CREATION_DATE_TO
      and organization_id=:ORGANIZATION_ID
      ORDER 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