Sales Revenue Straight Line Calculation Report

<?xml version = '1.0' encoding = 'UTF-8'?>
<!-- +======================================================================+ -->
<!-- |    Copyright (c) 2005, 2015 Oracle and/or its affiliates.           | -->
<!-- |                         All rights reserved.                         | -->
<!-- |                           Version 12.0.0                             | -->
<!-- +======================================================================+ -->

<!--  $Header: NA_CUSTAGRE.xml   2016/07/04  pdharman  $ -->
<!--  GSCC_Category="Xdo Data Definition"   -->

<dataTemplate name="NA_CUSTAGREE" defaultPackage="NA_CUST_AGRE_RPT_PKG" version="1.0">
<properties>
<property name="debug_mode" value="off" />
</properties>
<parameters>
   <parameter name="P_ORG_ID"          dataType="number" include_in_output="true"/>
   <parameter name="P_CUSTID_FROM"     dataType="number" include_in_output="true"/>
   <parameter name="P_AGREEMENT_FROM"  dataType="character" include_in_output="true"/>
   <parameter name="P_AGREEMENT_TO"    dataType="character" include_in_output="true"/>
   <parameter name="P_PERIOD_FROM"     dataType="character" include_in_output="true"/>
   <parameter name="P_PERIOD_TO"       dataType="character" include_in_output="true"/>
</parameters>
<dataQuery>
<sqlStatement name="Q_MAINQRY">
<![CDATA[
select
hca.account_number customer_number,
inv_hp.party_name customer_name,
ios.agreement_number,
shl.line_number,
shl.ordered_item,
msi.description item_desc,
shl.order_quantity_uom uom,
shl.attribute5 Quantity,
shl.attribute6 price,
inv_su.location Ship_to_loc,
ios.line_group,
gcc.concatenated_segments sales_account,
ios.monthly_date,
ios.monthly_bill_amt,
ios.revenue_recogn,
ios.total_value   
from apps.oe_blanket_headers_all sha,
     apps.oe_blanket_lines_all shl,
apps.hz_cust_site_uses_all inv_su,
apps.hz_parties inv_hp,
apps.hz_party_sites inv_ps,
     apps.hz_cust_accounts hca,
     apps.hz_cust_acct_sites_all inv_cas,
     apps.mtl_system_items_b msi,
     apps.gl_code_combinations_kfv gcc,
     iron.irm_om_salagree_stig_tmp ios
where sha.header_id = shl.header_id
and sha.org_id = shl.org_id
and sha.org_id = :P_ORG_ID
and shl.attribute4 = 'S'
and sha.cancelled_flag is null
and shl.cancelled_flag is null
and shl.invoice_to_org_id = inv_su.site_use_id (+)
AND inv_su.cust_acct_site_id = inv_cas.cust_acct_site_id (+)
AND inv_cas.party_site_id = inv_ps.party_site_id (+)
AND inv_hp.party_id = inv_ps.party_id
AND INV_HP.party_id = hca.party_id(+)
and shl.inventory_item_id = msi.inventory_item_id
and shl.ship_from_org_id = msi.organization_id
and msi.sales_account = gcc.code_combination_id
and ios.cust_account_id = hca.cust_account_id
and ios.line_id = shl.line_id
and ios.line_number = shl.line_number
and ios.agreement_number = sha.order_number
order by ios.agreement_number, ios.line_group, to_date(ios.monthly_date, 'DD-MON-YYYY')  ASC
]]>
</sqlStatement>
</dataQuery>
<dataTrigger name="beforeReport" source="IRON_NA_CUST_AGRE_RPT_PKG.BeforeReport(:P_ORG_ID,:P_CUSTID_FROM,:P_AGREEMENT_FROM,:P_AGREEMENT_TO,:P_PERIOD_FROM,:P_PERIOD_TO)"/>
<dataStructure>
<group name="G_AGREEMENT" source="Q_MAINQRY">
  <element name="CUSTOMER_NUMBER" value="CUSTOMER_NUMBER"/>
  <element name="CUSTOMER_NAME" value="CUSTOMER_NAME"/>
  <element name="AGREEMENT_NUMBER" value="AGREEMENT_NUMBER"/>
          <element name="LINE_GROUP" value="LINE_GROUP"/>
          <group name="G_AGRMNT_LINE" source="Q_MAINQRY">
              <element name="LINE_NUMBER" value="LINE_NUMBER"/>
  <element name="ORDERED_ITEM" value="ORDERED_ITEM"/>
  <element name="SALES_ACCOUNT" value="SALES_ACCOUNT"/>
  <element name="MONTH" value="MONTHLY_DATE"/>
  <element name="MONTHLY_BILL_AMOUNT" value="MONTHLY_BILL_AMT"/>
  <element name="REVENUE_RECOGNIZED" value="REVENUE_RECOGN"/>
  <element name="TOTAL_VALUE" value="TOTAL_VALUE"/>
  <element name="ORDERED_ITEM" value="ORDERED_ITEM"/>
  <element name="ITEM_DESC" value="ITEM_DESC"/>
  <element name="UOM" value="UOM"/>
  <element name="QUANTITY" value="QUANTITY"/>
  <element name="PRICE" value="PRICE"/>
  <element name="SHIP_TO_LOC" value="SHIP_TO_LOC"/>
  </group>
</group>
</dataStructure>
<dataTrigger name="AfterReport" source="NA_CUST_AGRE_RPT_PKG.AfterReport"/>
</dataTemplate>