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

<!-- Begin Data Template -->


<dataTemplate version="1.0" description="description" defaultpackage="XXPQ_PRM_SOURCE_PAL_BALANCE" name="XXPQ_PRM_SOURCE_PAL_BAL">



<!-- Enter Parameters -->


<parameters>

<parameter name="P_END_DATE" dataType="date"/>



</parameters>

<!-- Enter Lexicals -->


<lexicals> </lexicals>

<!-- Enter SQL Query -->



<dataQuery>


<sqlStatement name="PAL_BALANCE_DETAIL">

<![CDATA[ 
		select 
       account_number,
       account_name,
       contract_no,
       start_date,
       end_date,
       plan,
       sum(sub_amount) sub_amount,
       sum(original_pal_amount) original_pal_amount,
       sum(used_pal_amount) used_pal_amount,
       sum(original_pal_amount + used_pal_amount) balance
from       
(
select invoice_no,
       invoice_date,
       account_number,
       account_name,
       contract_no,
       start_date,
       end_date,
       item,
       ps_type,
       plan,
       round(decode(ps_type,'SUB',accounted_value,0),2) sub_amount,       
       round(decode(ps_type,'PAL SHELL',accounted_value,0),2) original_pal_amount,
       round(decode(ps_type,'PAL OFFSET',accounted_value,0),2) used_pal_amount
from               
(
select 
       trx.trx_number invoice_no, 
       trx.trx_date invoice_date,   
       trxln.creation_date,
       trxln.interface_line_attribute1 contract_no,
       to_char(to_date(trxln.interface_line_attribute4,'YYYY/MM/DD')) start_date,
       to_char(to_date(trxln.interface_line_attribute5,'YYYY/MM/DD')) end_date,       
       stca.account_number, 
       stca.account_name, 
       psi.item,
       psi.primary_source_type ps_type,
       decode(substr(psi.primary_source_category,1,3),'PSA','PLAN A','PSB','PLAN B','OTHER PLAN') plan,
       (trxln.extended_amount * nvl(trx.exchange_rate,1)) accounted_value
  from pqinf.xxpq_primary_sources_items_vw psi,
       apps.hz_cust_accounts stca,
       apps.hz_cust_acct_sites_all stcas,
       apps.hz_cust_site_uses_all stcsu,
       apps.ra_customer_trx_all trx,
       apps.ra_customer_trx_lines_all trxln
where stcas.cust_account_id = stca.cust_account_id
   and stcsu.cust_acct_site_id = stcas.cust_acct_site_id
   and trx.ship_to_site_use_id = stcsu.site_use_id
   and primary_source_type IN ('PAL OFFSET', 'PAL SHELL','SUB')
   and trxln.inventory_item_id = psi.inventory_item_id  
   and trxln.customer_trx_id = trx.customer_trx_id
   and trxln.customer_trx_id IN 
        (select customer_trx_id
           from ar.ra_cust_trx_line_gl_dist_all dist
          where nvl(dist.latest_rec_flag, 'Y') = 'Y'
          and dist.account_class = 'REC'
          and dist.gl_posted_date is not null
          and dist.gl_date <= :P_END_DATE
        )
   and trxln.line_type = 'LINE'
) aaa
where plan <> 'OTHER PLAN'
) bbb
group by account_number,
          account_name,
          contract_no,
          start_date,
          end_date,
          plan
order by 2,3,4,5]]>
</sqlStatement>

</dataQuery>

<!-- Enter Grouping -->



<dataStructure>


<group name="G_DETAIL" source="PAL_BALANCE_DETAIL">

<element name="ACCOUNT_NUMBER" value="ACCOUNT_NUMBER"/>

<element name="ACCOUNT_NAME" value="ACCOUNT_NAME"/>

<element name="CONTRACT_NO" value="CONTRACT_NO"/>

<element name="START_DATE" value="START_DATE"/>

<element name="END_DATE" value="END_DATE"/>

<element name="PLAN" value="PLAN"/>

<element name="SUB_AMOUNT" value="SUB_AMOUNT"/>

<element name="ORIGINAL_PAL_AMOUNT" value="ORIGINAL_PAL_AMOUNT"/>

<element name="USED_PAL_AMOUNT" value="USED_PAL_AMOUNT"/>

<element name="BALANCE" value="BALANCE"/>

</group>

</dataStructure>
<dataTrigger name="afterReport" source="XXPQ_PRM_SOURCE_PAL_BALANCE.afterReport_bursting()"/>
</dataTemplate>

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

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


-<xapi:request select="/XXPQ_PRM_SOURCE_PAL_BAL/LIST_G_DETAIL">


-<xapi:delivery>


-<xapi:email id="123" server="XXXXXXXXXXXXx" port="25" from="XXXXXXX">


-<xapi:message id="123" cc="XXXXXXXXXXXXXXXx" attachment="true" subject="Primary Source PAL Balance Report">
-
-<![CDATA[
Please View the attached Primary Source PAL Balance Report.
]]>
</xapi:message>

</xapi:email>

</xapi:delivery>


-<xapi:document output="ProQuest Primary Source PAL Balance Report" output-type="excel" delivery="123">

<xapi:template type="rtf" location="xdo://XXPQCUSAPP.XXPQ_PRM_SOURCE_PAL_BAL.en.US/?getSource=true" filter=""> </xapi:template>

</xapi:document>

</xapi:request>

</xapi:requestset>

CREATE OR REPLACE PACKAGE APPS.XXPQ_PRM_SOURCE_PAL_BALANCE
AS



   FUNCTION afterReport_bursting
   
      RETURN BOOLEAN;
     
      
         
END ;
/


CREATE OR REPLACE PACKAGE BODY APPS.XXPQ_PRM_SOURCE_PAL_BALANCE
AS


   FUNCTION afterReport_bursting
      RETURN BOOLEAN
   IS
      l_conc_req_id   NUMBER;
      l_out_req_id    NUMBER;
      l_count         NUMBER;
   BEGIN
      l_conc_req_id := apps.fnd_global.conc_request_id;

      l_out_req_id :=
         apps.fnd_request.submit_request ('XDO',
                                          'XDOBURSTREP',
                                          '',
                                          '',
                                          FALSE,
                                          'Y',
                                          l_conc_req_id,
                                          'Y',
                                          CHR (0));
                                          
                                          COMMIT;

      RETURN TRUE;
      
   EXCEPTION
      WHEN OTHERS
      THEN
       NULL;
   END afterReport_bursting;
END XXPQ_PRM_SOURCE_PAL_BALANCE;
/