Home » SQL & PL/SQL » SQL & PL/SQL » XML Special Character (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
XML Special Character [message #671080] |
Tue, 14 August 2018 15:07 |
|
vharish006
Messages: 70 Registered: August 2015 Location: Chicago
|
Member |
|
|
Hi All,
I'm trying to develop a report and facing below error with one Column "Sub_market_type" having some special Characters like '&'
"The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------------------------------------------------------------
An invalid character was found in text content. Error processing resource 'https://dvl-ebs.proquest.com/OA_CGI/FNDWRR.exe?t...
<SUB_MARKET_TYPE>Personal & Household Products
ass="e">"
SELECT DECODE (chrb.org_id, '81', 'US', '82', 'UK', '83', 'SPAIN')AS org,
hca.attribute1 market_type,
(hca.attribute2) sub_market_type,
chrb.contract_number,
cleb.line_number line_number,
(hca.account_number) ship_account_number,
( (SELECT DISTINCT hca.account_name
FROM apps.hz_locations hl,
apps.hz_party_sites hps,
apps.hz_cust_accounts hca,
apps.hz_cust_acct_sites_all hcas,
apps.hz_cust_site_uses_all hcsu
WHERE hcsu.site_use_code = 'BILL_TO'
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcsu.site_use_id = chrb.bill_to_site_use_id))
bill_to_account_name,
(SELECT DISTINCT hca.account_number
FROM apps.hz_locations hl,
apps.hz_party_sites hps,
apps.hz_cust_accounts hca,
apps.hz_cust_acct_sites_all hcas,
apps.hz_cust_site_uses_all hcsu
WHERE hcsu.site_use_code = 'BILL_TO'
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcsu.site_use_id = chrb.bill_to_site_use_id)
bill_to_account_number,
(hca.account_name) ship_account_name,
chrb.cust_po_number_req_yn,
(hl.address4) site_dept,
(hl.address1) address1,
(hl.address2) address2,
--, (hl.address3) address3, (hl.street) street,
(hl.city) city,
DECODE (hl.country, 'CA', hl.province, hl.state) state,
hl.postal_code,
hl.country,
(SELECT geography_name
FROM apps.hz_geographies
WHERE country_code = hl.country AND geography_type = 'COUNTRY')
ship_country,
( (SELECT ffp.description
FROM apps.fnd_flex_values_vl ffp,
apps.fnd_flex_value_sets flex_sets1
WHERE ffp.flex_value = g.segment4
AND flex_sets1.flex_value_set_id = ffp.flex_value_set_id
AND flex_sets1.flex_value_set_name = 'PROQ_BUSINESS_UNIT'))
business_unit,
( (SELECT ffp.description
FROM apps.fnd_flex_values_vl ffp,
apps.fnd_flex_value_sets flex_sets1
WHERE ffp.flex_value = g.segment5
AND flex_sets1.flex_value_set_id = ffp.flex_value_set_id
AND flex_sets1.flex_value_set_name =
'PROQ_PRODUCT_FAMILY'))
product_family,
mtl.segment1 item,
mtl.description,
cleb.start_date,
TO_CHAR (cleb.start_date, 'yyyymm') start_date_formated,
cleb.end_date,
TO_CHAR (cleb.end_date, 'yyyymm') end_date_formated,
cleb.currency_code currency_code,
ROUND (kln.toplvl_adj_price * kln.toplvl_price_qty, 2) netprice,
NVL (
(SELECT conversion_rate
FROM apps.gl_daily_rates
WHERE from_currency = cleb.currency_code
AND conversion_date =
NVL (TRUNC (chrb.date_approved), TRUNC (SYSDATE))
AND to_currency = 'USD'
AND conversion_type = '1024'),
1)
conversion_rate2usd,
NVL (
(SELECT conversion_rate
FROM apps.gl_daily_rates
WHERE from_currency = cleb.currency_code
AND conversion_date =
NVL (TRUNC (chrb.date_approved),
TRUNC (SYSDATE))
AND to_currency = 'USD'
AND conversion_type = '1024'),
1)
* (ROUND (kln.toplvl_adj_price * kln.toplvl_price_qty, 2))
price_in_usd,
chrb.attribute8 batch_number,
(SELECT notice_number
FROM pqinf.xxpq_renewal_stg
WHERE batch_id = chrb.attribute8 AND chrb.org_id = org_id)
renewal_notice_no,
PQINF.xxpq_get_sc_notes (chrb.id) sc_notes,
DECODE (
cleb.sts_code,
'TERMINATED', (SELECT v.meaning
FROM apps.okc_k_lines_b l,
apps.fnd_lookup_values_vl v,
apps.fnd_lookup_types_vl ty
WHERE l.ID = cleb.ID
AND l.trn_code = v.lookup_code
AND v.lookup_type = ty.lookup_type
AND ty.lookup_type IN
('OKC_TERMINATION_REASON')),
NULL)
termination_reason,
DECODE (
cleb.sts_code,
'TERMINATED', NULL,
'ACKNOWLEGED', (SELECT reason_code
FROM apps.okc_k_history_b hist1
WHERE chr_id = chrb.ID
AND hist1.sts_code_to = cleb.sts_code
AND hist1.creation_date =
(SELECT MAX (hist2.creation_date)
FROM apps.okc_k_history_b hist2
WHERE hist2.chr_id =
hist1.chr_id)
AND ROWNUM = 1),
(SELECT v.meaning
FROM apps.okc_k_lines_b l,
apps.fnd_lookup_values_vl v,
apps.fnd_lookup_types_vl ty
WHERE l.ID = cleb.ID
AND l.trn_code = v.lookup_code
AND v.lookup_type = ty.lookup_type
AND ty.lookup_type IN ('OKC_STS_CHG_REASON')
AND ROWNUM = 1))
status_change_reason,
chrb.creation_date,
chrb.date_approved,
DECODE (cleb.sts_code,
'ACKNOWLEGED', 'ACKNOWLEDGED',
cleb.sts_code)
sts_code,
hcsu.LOCATION ship_to_location,
(SELECT btr.trx_date
FROM apps.oks_bill_transactions btr,
apps.oks_bill_txn_lines btl,
apps.oks_bill_cont_lines bcl,
apps.okc_k_headers_all_b hdr,
apps.okc_k_lines_b line
WHERE hdr.ID = chrb.ID
AND hdr.ID = line.chr_id
AND line.ID = bcl.cle_id
AND btr.ID = bcl.btn_id
AND btl.btn_id = btr.ID
AND btl.bcl_id = bcl.ID
AND ROWNUM = 1)
inv_date,
(SELECT btr.trx_number
FROM apps.oks_bill_transactions btr,
apps.oks_bill_txn_lines btl,
apps.oks_bill_cont_lines bcl,
apps.okc_k_headers_all_b hdr,
apps.okc_k_lines_b line
WHERE hdr.ID = chrb.ID
AND hdr.ID = line.chr_id
AND line.ID = bcl.cle_id
AND line.ID = cleb.ID
AND btr.ID = bcl.btn_id
AND btl.btn_id = btr.ID
AND btl.bcl_id = bcl.ID
AND ROWNUM = 1)
inv_number,
DECODE (hl.country, 'US', 'US', 'International') us_international
FROM apps.okc_k_lines_b cleb,
apps.okc_k_lines_tl clet,
apps.oks_k_lines_b kln,
apps.oks_k_lines_tl klt,
apps.okc_k_items citem,
apps.okc_k_headers_all_b chrb,
apps.okc_k_headers_tl chrt,
apps.oks_k_headers_b khr,
-- apps.okc_statuses_v sts,
apps.mtl_system_items_b mtl,
-- apps.okc_subclasses_v cls,
apps.hz_locations hl,
apps.hz_party_sites hps,
apps.hz_cust_accounts hca,
apps.hz_cust_acct_sites_all hcas,
apps.hz_cust_site_uses_all hcsu,
apps.gl_code_combinations_v g
WHERE cleb.ID = clet.ID
AND clet.LANGUAGE = USERENV ('LANG')
AND clet.LANGUAGE = klt.LANGUAGE
AND cleb.ID = kln.cle_id
AND kln.ID = klt.ID
AND cleb.chr_id = chrb.ID
AND citem.object1_id1 = mtl.inventory_item_id
AND mtl.sales_account = g.code_combination_id
AND kln.cle_id = citem.cle_id
AND chrb.ID = chrt.ID
AND chrb.ID = khr.chr_id
AND chrt.LANGUAGE = USERENV ('LANG')
-- AND chrb.sts_code = sts.code
-- AND chrb.scs_code = cls.code
-- AND cls.cls_code = 'SERVICE'
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcsu.site_use_id = cleb.ship_to_site_use_id
AND cleb.sts_code NOT IN ('ACTIVE',
'AMENDED',
'APPROVAL_REJECTED',
'CANCELLED',
'COMPLETE',
'DECLINED',
'ENTERED',
'EXPIRED',
'HOLD',
'MIGRATE',
'PROFORMA',
'RENEWAL_ACTIVE',
'REVERSED',
'REVIEWED',
'SALES_PRICE_REVIEW_REQ',
'SIGNED',
'ABANDONED',
'TERMINATED')
-- and chrb.contract_number='US10042610'
and hca.attribute2 like 'Personal%'
AND cleb.start_date BETWEEN '01-FEB-2018' and '15-FEB-2018'
--AND trunc(cleb.start_date) = trunc(to_date(:p_Start_Date, 'YYYY/MM/DD hh24:mi:ss'))
--AND trunc(cleb.start_date) = trunc(to_date(:p_end_Date, 'YYYY/MM/DD hh24:mi:ss'))
Is there any way i can handle this but still have the "&" in the end output in xml output
Sample Data:
------------------
CREATE TABLE APPS.TEST_H
(
ORG VARCHAR2(5 BYTE),
MARKET_TYPE VARCHAR2(150 BYTE),
SUB_MARKET_TYPE VARCHAR2(150 BYTE),
CONTRACT_NUMBER VARCHAR2(120 BYTE) NOT NULL,
LINE_NUMBER VARCHAR2(150 BYTE) NOT NULL,
SHIP_ACCOUNT_NUMBER VARCHAR2(30 BYTE) NOT NULL,
BILL_TO_ACCOUNT_NAME VARCHAR2(240 BYTE),
BILL_TO_ACCOUNT_NUMBER VARCHAR2(30 BYTE),
SHIP_ACCOUNT_NAME VARCHAR2(240 BYTE),
CUST_PO_NUMBER_REQ_YN VARCHAR2(3 BYTE),
SITE_DEPT VARCHAR2(240 BYTE),
ADDRESS1 VARCHAR2(240 BYTE) NOT NULL,
ADDRESS2 VARCHAR2(240 BYTE),
CITY VARCHAR2(60 BYTE),
STATE VARCHAR2(60 BYTE),
POSTAL_CODE VARCHAR2(60 BYTE),
COUNTRY VARCHAR2(60 BYTE) NOT NULL,
SHIP_COUNTRY VARCHAR2(360 BYTE),
BUSINESS_UNIT VARCHAR2(240 BYTE),
PRODUCT_FAMILY VARCHAR2(240 BYTE),
ITEM VARCHAR2(40 BYTE),
DESCRIPTION VARCHAR2(240 BYTE),
START_DATE DATE,
START_DATE_FORMATED VARCHAR2(6 BYTE),
END_DATE DATE,
END_DATE_FORMATED VARCHAR2(6 BYTE),
CURRENCY_CODE VARCHAR2(15 BYTE),
NETPRICE NUMBER,
CONVERSION_RATE2USD NUMBER,
PRICE_IN_USD NUMBER,
BATCH_NUMBER VARCHAR2(450 BYTE),
RENEWAL_NOTICE_NO VARCHAR2(50 BYTE),
SC_NOTES VARCHAR2(4000 BYTE),
TERMINATION_REASON VARCHAR2(80 BYTE),
STATUS_CHANGE_REASON VARCHAR2(80 BYTE),
CREATION_DATE DATE NOT NULL,
DATE_APPROVED DATE,
STS_CODE VARCHAR2(30 BYTE),
SHIP_TO_LOCATION VARCHAR2(40 BYTE) NOT NULL,
INV_DATE DATE,
INV_NUMBER VARCHAR2(60 BYTE),
US_INTERNATIONAL VARCHAR2(13 BYTE)
);
COMMIT;
Insert into APPS.TEST_H
(ORG, MARKET_TYPE, SUB_MARKET_TYPE, CONTRACT_NUMBER, LINE_NUMBER,
SHIP_ACCOUNT_NUMBER, BILL_TO_ACCOUNT_NAME, BILL_TO_ACCOUNT_NUMBER, SHIP_ACCOUNT_NAME, SITE_DEPT,
ADDRESS1, CITY, POSTAL_CODE, COUNTRY, SHIP_COUNTRY,
BUSINESS_UNIT, PRODUCT_FAMILY, ITEM, DESCRIPTION, START_DATE,
START_DATE_FORMATED, END_DATE, END_DATE_FORMATED, CURRENCY_CODE, NETPRICE,
CONVERSION_RATE2USD, PRICE_IN_USD, SC_NOTES, STATUS_CHANGE_REASON, CREATION_DATE,
STS_CODE, SHIP_TO_LOCATION, US_INTERNATIONAL)
Values
('US', 'Corporate', 'Personal & Household Products ', 'PQ10077640', '3',
'390680', 'Philips International B.V.', '390680', 'Philips International B.V.', 'Research',
'PB 80071', 'Eindhoven', '5600 KA', 'NL', 'Netherlands',
'DIALOG', 'PROQUEST DIALOG', 'PQDALL', 'ProQuest Dialog Database', TO_DATE('02/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'201802', TO_DATE('01/31/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '201901', 'USD', 10000,
1, 10000, '|60 days End of Month|** Invoices should be sent via regular post to the billing address as
indicated on this Purchase Order **
Invoicing has to be done in full conformity with our Purchase Order.We
urge you strongly to indicate our correct billing address and complete
PO number NLY0- (as indicated on this order form) on your invoice(s).
Incorrect invoices cannot be processed by Philips and will be returned.
For invoice related questions/issues you can contact:
Email: vq.ct.nl@philips.com
Phone: +31 40 27 82271 (Dutch / English language)
+31 40 27 82272 (French language)
Please send your order confirmation within three days to the email
address as mentioned in header of this order.', 'CREATE', TO_DATE('02/16/2018 04:02:31', 'MM/DD/YYYY HH24:MI:SS'),
'ACKNOWLEDGED', '2558937', 'International');
Insert into APPS.TEST_H
(ORG, MARKET_TYPE, SUB_MARKET_TYPE, CONTRACT_NUMBER, LINE_NUMBER,
SHIP_ACCOUNT_NUMBER, BILL_TO_ACCOUNT_NAME, BILL_TO_ACCOUNT_NUMBER, SHIP_ACCOUNT_NAME, SITE_DEPT,
ADDRESS1, CITY, POSTAL_CODE, COUNTRY, SHIP_COUNTRY,
BUSINESS_UNIT, PRODUCT_FAMILY, ITEM, DESCRIPTION, START_DATE,
START_DATE_FORMATED, END_DATE, END_DATE_FORMATED, CURRENCY_CODE, NETPRICE,
CONVERSION_RATE2USD, PRICE_IN_USD, SC_NOTES, STATUS_CHANGE_REASON, CREATION_DATE,
STS_CODE, SHIP_TO_LOCATION, US_INTERNATIONAL)
Values
('US', 'Corporate', 'Personal & Household Products ', 'PQ10077640', '1',
'390680', 'Philips International B.V.', '390680', 'Philips International B.V.', 'Research',
'PB 80071', 'Eindhoven', '5600 KA', 'NL', 'Netherlands',
'DIALOG', 'DIALOG', 'INVESTEXT', 'Thomson Reuters Embargoed Research Collection (Shell)', TO_DATE('02/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'201802', TO_DATE('01/31/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '201901', 'USD', 2240,
1, 2240, '|60 days End of Month|** Invoices should be sent via regular post to the billing address as
indicated on this Purchase Order **
Invoicing has to be done in full conformity with our Purchase Order.We
urge you strongly to indicate our correct billing address and complete
PO number NLY0- (as indicated on this order form) on your invoice(s).
Incorrect invoices cannot be processed by Philips and will be returned.
For invoice related questions/issues you can contact:
Email: vq.ct.nl@philips.com
Phone: +31 40 27 82271 (Dutch / English language)
+31 40 27 82272 (French language)
Please send your order confirmation within three days to the email
address as mentioned in header of this order.', 'CREATE', TO_DATE('02/16/2018 04:02:31', 'MM/DD/YYYY HH24:MI:SS'),
'ACKNOWLEDGED', '2558937', 'International');
COMMIT;
-
Attachment: Capture1.PNG
(Size: 79.68KB, Downloaded 1357 times)
|
|
|
Goto Forum:
Current Time: Sat Jun 15 15:55:15 CDT 2024
|