How to design XML Report with Dependent ValueSet Parameters – Oracle E-Business Suite R12.*

Data Model XML

<?xml version="1.0" encoding="WINDOWS-1252" ?>
<dataTemplate name="XXORA_ARINV_REP" description="AR Invoice Data Report" Version="1.0">
	<properties>
		<property name="debug_mode" value="on"/>
	</properties>
	<parameters>
		<parameter name="P_ORG_ID" dataType="number"/>
		<parameter name="P_CUST_ACCOUNT_ID" dataType="number"/>
	</parameters>
	<dataQuery>
		<sqlStatement name="Q_AR_INV">
		   <![CDATA[ 
				 SELECT  RCTA.CUSTOMER_TRX_ID, RCTA.TRX_NUMBER, RCTA.TRX_DATE 
, RCTA.SOLD_TO_CUSTOMER_ID
, RCTA.ORG_ID
, HOU.NAME BU_NAME
,HCA.ACCOUNT_NUMBER
FROM RA_CUSTOMER_TRX_ALL  RCTA ,HR_OPERATING_UNITS HOU 
, HZ_CUST_ACCOUNTS HCA 
WHERE 1=1
AND HOU.ORGANIZATION_ID = RCTA.ORG_ID
AND HCA.CUST_ACCOUNT_ID = RCTA.SOLD_TO_CUSTOMER_ID 
AND RCTA.ORG_ID = :P_ORG_ID
AND HCA.CUST_ACCOUNT_ID = NVL(:P_CUST_ACCOUNT_ID , HCA.CUST_ACCOUNT_ID)
 ]]></sqlStatement>
	</dataQuery>
	<dataStructure>
		<group name="G_ARINV" source="Q_AR_INV">
			<element name="BU_NAME" value="BU_NAME"/>
			<element name="TRX_NUMBER" value="TRX_NUMBER"/>
			<element name="ACCOUNT_NUMBER" value="ACCOUNT_NUMBER"/>
			<element name="TRX_DATE" value="TRX_DATE"/>
		</group>
	</dataStructure>
</dataTemplate>

ValueSet : XXORA_AR_INV_BU_ID

ValueSet 2 : XXORA_AR_INV_CUST_ID

WHERE Clause : org_id = NVL(:$FLEX$.XXORA_AR_INV_BU_ID ,org_id )

Concurrent Program : XXORA AR Invoice XML Report

Parameters :

Leave a Reply

Your email address will not be published.