Home » Developer & Programmer » JDeveloper, Java & XML » XML Publisher Reports
XML Publisher Reports [message #588958] Mon, 01 July 2013 00:40
vaibhav15211
Messages: 38
Registered: August 2012
Location: Hyderabad
Member
For the following Data Structure, If I don't specify the Group name the data is not fetched, where should I put the Query so that the values are fetched at the header level and not under any group:
<dataStructure>
<element name="address_line_1" value="address_line_1" />
<element name="address_line_2" value="address_line_2" />
<element name="address_line_3" value="address_line_3" />
<element name="cp_country" value="cp_country" />
<element name="loc_information13" value="loc_information13" />
<element name="telephone_num" value="telephone_num" />
<element name="fax_num" value="fax_num" />
<group name="G_HEADER" source="Q1">
<element name="ADDRESS1" value="ADDRESS1"/>
<element name="ADDRESS2" value="ADDRESS2"/>
<element name="ADDRESS3" value="ADDRESS3"/>
<element name="ADDRESS4" value="ADDRESS4"/>
</group>
</dataStructure>

QUERY:
<!-- D A T A Q U E R Y -->
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[
SELECT del.delivery_id,
CASE
WHEN del.number_of_lpn IS NULL
THEN TO_NUMBER (0)
ELSE NVL (del.number_of_lpn, 0)
END number_of_lpn,
party_site.attribute8 party_name,
convert(xxfnd_idautomation_pkg.code128a (:P_DELIVERY_ID),'asd', 'fgfg') del_num,
-- convert(xxfnd_idautomation_pkg.code128a (:P_DELIVERY_ID)) del_num,
ship_to_loc.location_code,
ship_to_loc.address1, ship_to_loc.address2, ship_to_loc.address3,
ship_to_loc.address4, ship_to_loc.city, ship_to_loc.state,
ship_to_terr.territory_code,
ship_to_terr.territory_short_name terr_st_name,
ship_to_loc.postal_code, NVL (ship_to_loc.city,
ship_to_loc.state) town,
ship_to_loc.postal_code
|| ' '
|| NVL (ship_to_loc.city, ship_to_loc.state) postal_city,
flv.meaning,
(SELECT SUBSTR (NAME, 5, 3)
FROM hr_operating_units
WHERE organization_id = fnd_profile.VALUE ('ORG_ID')) org_abbr,
ROWNUM,
(select rtrim(xmlagg(xmlelement(e, CUST_PO_NUM || ',')).extract('//text()').extract('//text()') ,',') CUST_PO_NUM from
(select distinct wdd.cust_po_number CUST_PO_NUM FROM
wsh_new_deliveries del,
wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = del.delivery_id
AND del.delivery_id = :p_delivery_id)) customer_po_num
FROM wsh_new_deliveries del,
fnd_lookup_values_vl flv,
wsh_locations ship_to_loc,
fnd_territories_tl ship_to_terr,
org_organization_definitions ou,
hz_party_sites party_site
WHERE flv.lookup_type(+) = 'SHIP_METHOD'
AND flv.lookup_code(+) = del.ship_method_code
AND ship_to_loc.wsh_location_id = del.ultimate_dropoff_location_id
AND DECODE (ship_to_loc.country,
NULL, USERENV ('LANG'),
ship_to_terr.LANGUAGE
) = USERENV ('LANG')
AND ship_to_loc.country = ship_to_terr.territory_code(+)
AND ou.organization_id = del.organization_id
AND ship_to_loc.location_code = party_site.party_site_number
&g_where_clause
]]>
</sqlStatement>
</dataQuery>

Thanks
Vaibhav

[Updated on: Mon, 01 July 2013 00:44]

Report message to a moderator

Previous Topic: Update an xml element in oracle 10gr1 without using Oracle XDB ?
Next Topic: The Problem of Java Time Synchronization
Goto Forum:
  


Current Time: Thu Mar 28 03:58:11 CDT 2024