在用package方式產生.xml時由於有&造成問題的處理

近日用做omr066x(用package,上掛到oracle erp,輸出excel)時問題多,所謂一波三折.

開始是nescape環境下,view output後會有save it to disk的功能,我的output format為xml.

當輸出內容沒有中文時,可以從oracle erp中直接open成excel.

當輸出內容有中文時,可以把其存到資料夾,然後用記事本開啟,並再其再另存為unicode後,就可以用excel開啟了.

然而臺灣與北美等地區的user,沒有裝nescape,都是用ie瀏覽器.個別user的view　output後並沒有save it to disk的選項.從而出現error

對此,把omr066x的output format由xml改為txt,view output後就可以用瀏覽器的save as存為.xml了,對於帶中文的,再用記事本另存為unicode即可.

今天卻發現又有問題了.error如下:

    B&Q
------------------------------------^

顯然這個&在作怪.

為此,經user協商後,在package中加個處理,把&改為 and .程式碼如下:

CREATE OR REPLACE package body gobo_om_xml_pkg is

procedure omr066x(
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
P_ORG_ID in number,
P_NAME in varchar2,
P_TERRITORY in varchar2,
P_DATE_FROM in varchar2,
P_DATE_TO in varchar2,
P_ACT_FROM in varchar2,
P_ACT_TO in varchar2,
P_SCHEDULE_FROM in varchar2,
P_SCHEDULE_TO in varchar2,
P_CUSTF in number,
P_CUSTT in number,
P_SITE_USE_ID in number
) is
cursor cur is
SELECT
ooh.ship_to_org_id,
(select location from HZ_CUST_SITE_USES_ALL
where 1=1 and site_use_id = ooh.ship_to_org_id
) location,
replace(nvl(TER.TERRITORY,' '),'&',' and ') territory,
replace(rt.NAME,'&',' and ') name, replace(rsa.NAME,'&',' and ') saler_name,
(select replace(description,'&',' and ') from fnd_user where user_id=OOL.CREATED_BY) saler,
TO_CHAR (ooh.ordered_date, 'yyyymm') jdym,
TO_CHAR (ooh.ordered_date, 'yyyy.mm.dd') order_date,
TO_CHAR (ool.REQUEST_DATE, 'yyyy.mm.dd') ship_date,
decode(ool.flow_status_code,
'SHIPPED','SHIPPED',
'CLOSED','SHIPPED',
'NOTSHIPPED'
) SHIP_YN,
TO_CHAR (ool.actual_shipment_date, 'yyyy.mm.dd') actu_date,
ooh.order_number pi_no, ooh.org_id, ooh.sold_to_org_id,
'' VERSION, ACV.CUSTOMER_ID,
replace(NVL(acv.customer_name_phonetic,ACV.CUSTOMER_NAME),'&',' and ') customer_name_phonetic,
ooh.transactional_curr_code currency,
SUM (DECODE (mc.segment1,'Faucet', ool.ordered_quantity,
'Electronic Faucet', ool.ordered_quantity,0)
) f_q,
SUM (DECODE (mc.segment1,'Faucet', ool.ordered_quantity * ool.unit_selling_price,
'Electronic Faucet', ool.ordered_quantity * ool.unit_selling_price,0)
) f_a,
SUM (DECODE (mc.segment1,'Showerhead', ool.ordered_quantity,0)
) sh_q,
SUM (DECODE (mc.segment1,'Showerhead', ool.ordered_quantity * ool.unit_selling_price,0)
) sh_a,
SUM (DECODE (mc.segment1,'Bath Accessories', ool.ordered_quantity,0)
) ba_q,
SUM (DECODE (mc.segment1,'Bath Accessories', ool.ordered_quantity * ool.unit_selling_price,0)
) ba_a,
SUM (DECODE (mc.segment1,'Vitreous China', ool.ordered_quantity,0)
) VC_q,
SUM (DECODE (mc.segment1,'Vitreous China', ool.ordered_quantity * ool.unit_selling_price,0)
) VC_a,
SUM (DECODE (mc.segment1,'Others', ool.ordered_quantity,
'Bath Accessories',0,'Showerhead',0,
'Faucet', 0,'Electronic Faucet', 0,ool.ordered_quantity )
) other_q,
SUM (DECODE (mc.segment1,'Others', ool.ordered_quantity * ool.unit_selling_price,
'Bath Accessories',0,'Showerhead',0,'Faucet', 0,
'Electronic Faucet', 0,ool.ordered_quantity * ool.unit_selling_price)
) other_a,
SUM (ool.ordered_quantity * ool.unit_selling_price) amount
FROM mtl_item_categories_v mic,
mtl_categories mc,
oe_order_headers_all ooh,
oe_order_lines_all ool,
ar_customers acv,
ra_salesreps_all rsa,
gobo_om_territory_v TER,
ra_terms_tl rt
WHERE mc.category_id(+) = mic.category_id
AND mic.inventory_item_id(+) = ool.inventory_item_id
AND mic.category_set_name(+) = 'MPL'
AND mic.organization_id(+) =1
AND rsa.salesrep_id(+) = ooh.salesrep_id
AND rsa.org_id(+) = ooh.org_id
AND TER.ORG_ID(+) = OOH.ORG_ID
AND TER.CUSTOMER_ID(+) = OOH.SOLD_TO_ORG_ID
AND rt.term_id(+) = ooh.payment_term_id
AND rt.LANGUAGE(+) = USERENV ('LANG')
AND acv.customer_id = ooh.sold_to_org_id
AND ACV.CUSTOMER_NAME IS NOT NULL
AND NVL(OOL.CANCELLED_FLAG,'N')='N'
AND ool.org_id = ooh.org_id
AND ool.header_id = ooh.header_id
AND ooh.org_id = NVL(p_org_id,ooh.org_id)
and acv.customer_number>=NVL(p_custf,acv.customer_number)
and acv.customer_number<=NVL(p_custt,acv.customer_number)
AND ACV.CUSTOMER_NUMBER NOT IN (1034,1067,5996,2655,1035,2712,4773,14496,14497,1137,12161,1138,2219,14498,14696,14922,1035,2712,2854,1041,14697,14698)
AND TO_CHAR(OOH.ORDERED_DATE,'YYYYMM')>=NVL(P_DATE_FROM,TO_CHAR(OOH.ORDERED_DATE,'YYYYMM'))
AND TO_CHAR(OOH.ORDERED_DATE,'YYYYMM')<=NVL(P_DATE_TO,TO_CHAR(OOH.ORDERED_DATE,'YYYYMM'))
AND TO_CHAR(OOL.REQUEST_DATE,'YYYYMM')>=NVL(P_SCHEDULE_FROM,TO_CHAR(OOL.REQUEST_DATE,'YYYYMM'))
AND TO_CHAR(OOL.REQUEST_DATE,'YYYYMM')<=NVL(P_SCHEDULE_tO,TO_CHAR(OOL.REQUEST_DATE,'YYYYMM'))
AND NVL(TER.TERRITORY,'--1') = NVL(P_TERRITORY,NVL(TER.TERRITORY,'--1'))
AND OOH.SHIP_TO_ORG_ID = NVL(P_SITE_USE_ID,OOH.SHIP_TO_ORG_ID)
GROUP BY ooh.ship_to_org_id,OOL.CREATED_BY,TER.TERRITORY,
rt.NAME,TO_CHAR (ooh.ordered_date, 'yyyymm'),
TO_CHAR (ooh.ordered_date, 'yyyy.mm.dd'),TO_CHAR (ool.REQUEST_DATE, 'yyyy.mm.dd'),
decode(ool.flow_status_code,'SHIPPED','SHIPPED','CLOSED','SHIPPED','NOTSHIPPED'),
TO_CHAR (ool.actual_shipment_date, 'yyyy.mm.dd'),
ooh.order_number,ooh.org_id, ooh.sold_to_org_id,ACV.CUSTOMER_ID,
NVL(acv.customer_name_phonetic,ACV.CUSTOMER_NAME),ooh.transactional_curr_code,rsa.NAME;

cursor cur_SHIP is
SELECT
ooh.ship_to_org_id,
(select location from HZ_CUST_SITE_USES_ALL
where 1=1 and site_use_id = ooh.ship_to_org_id
) location,
replace(nvl(TER.TERRITORY,' '),'&',' and ') territory,
replace(rt.NAME,'&',' and ') name, replace(rsa.NAME,'&',' and ') saler_name,
(select replace(description,'&',' and ') from fnd_user where user_id=OOL.CREATED_BY) saler,
TO_CHAR (ooh.ordered_date, 'yyyymm') jdym,
TO_CHAR (ooh.ordered_date, 'yyyy.mm.dd') order_date,
TO_CHAR (ool.REQUEST_DATE, 'yyyy.mm.dd') ship_date,
decode(ool.flow_status_code,
'SHIPPED','SHIPPED',
'CLOSED','SHIPPED',
'NOTSHIPPED'
) SHIP_YN,
TO_CHAR (ool.actual_shipment_date, 'yyyy.mm.dd') actu_date,
ooh.order_number pi_no, ooh.org_id, ooh.sold_to_org_id,
'' VERSION, ACV.CUSTOMER_ID,
replace(NVL(acv.customer_name_phonetic,ACV.CUSTOMER_NAME),'&',' and ') customer_name_phonetic,
ooh.transactional_curr_code currency,
SUM (DECODE (mc.segment1,'Faucet', ool.ordered_quantity,
'Electronic Faucet', ool.ordered_quantity,0)
) f_q,
SUM (DECODE (mc.segment1,'Faucet', ool.ordered_quantity * ool.unit_selling_price,
'Electronic Faucet', ool.ordered_quantity * ool.unit_selling_price,0)
) f_a,
SUM (DECODE (mc.segment1,'Showerhead', ool.ordered_quantity,0)
) sh_q,
SUM (DECODE (mc.segment1,'Showerhead', ool.ordered_quantity * ool.unit_selling_price,0)
) sh_a,
SUM (DECODE (mc.segment1,'Bath Accessories', ool.ordered_quantity,0)
) ba_q,
SUM (DECODE (mc.segment1,'Bath Accessories', ool.ordered_quantity * ool.unit_selling_price,0)
) ba_a,
SUM (DECODE (mc.segment1,'Vitreous China', ool.ordered_quantity,0)
) VC_q,
SUM (DECODE (mc.segment1,'Vitreous China', ool.ordered_quantity * ool.unit_selling_price,0)
) VC_a,
SUM (DECODE (mc.segment1,'Others', ool.ordered_quantity,
'Bath Accessories',0,'Showerhead',0,
'Faucet', 0,'Electronic Faucet', 0,ool.ordered_quantity )
) other_q,
SUM (DECODE (mc.segment1,'Others', ool.ordered_quantity * ool.unit_selling_price,
'Bath Accessories',0,'Showerhead',0,'Faucet', 0,
'Electronic Faucet', 0,ool.ordered_quantity * ool.unit_selling_price)
) other_a,
SUM (ool.ordered_quantity * ool.unit_selling_price) amount
FROM mtl_item_categories_v mic,
mtl_categories mc,
oe_order_headers_all ooh,
oe_order_lines_all ool,
ar_customers acv,
ra_salesreps_all rsa,
gobo_om_territory_v TER,
ra_terms_tl rt
WHERE mc.category_id(+) = mic.category_id
AND mic.inventory_item_id(+) = ool.inventory_item_id
AND mic.category_set_name(+) = 'MPL'
AND mic.organization_id(+) =1
AND rsa.salesrep_id(+) = ooh.salesrep_id
AND rsa.org_id(+) = ooh.org_id
AND TER.ORG_ID(+) = OOH.ORG_ID
AND TER.CUSTOMER_ID(+) = OOH.SOLD_TO_ORG_ID
AND rt.term_id(+) = ooh.payment_term_id
AND rt.LANGUAGE(+) = USERENV ('LANG')
AND acv.customer_id = ooh.sold_to_org_id
AND ACV.CUSTOMER_NAME IS NOT NULL
AND NVL(OOL.CANCELLED_FLAG,'N')='N'
AND ool.org_id = ooh.org_id
AND ool.header_id = ooh.header_id
AND ooh.org_id = NVL(p_org_id,ooh.org_id)
and acv.customer_number>=NVL(p_custf,acv.customer_number)
and acv.customer_number<=NVL(p_custt,acv.customer_number)
AND ACV.CUSTOMER_NUMBER NOT IN (1034,1067,5996,2655,1035,2712,4773,14496,14497,1137,12161,1138,2219,14498,14696,14922,1035,2712,2854,1041,14697,14698)
AND TO_CHAR(OOH.ORDERED_DATE,'YYYYMM')>=NVL(P_DATE_FROM,TO_CHAR(OOH.ORDERED_DATE,'YYYYMM'))
AND TO_CHAR(OOH.ORDERED_DATE,'YYYYMM')<=NVL(P_DATE_TO,TO_CHAR(OOH.ORDERED_DATE,'YYYYMM'))
AND TO_CHAR(OOL.ACTUAL_SHIPMENT_DATE,'YYYYMM')>=NVL(P_ACT_FROM,TO_CHAR(OOL.ACTUAL_SHIPMENT_DATE,'YYYYMM'))
aND TO_CHAR(OOL.ACTUAL_SHIPMENT_DATE,'YYYYMM')<=NVL(P_ACT_TO,TO_CHAR(OOL.ACTUAL_SHIPMENT_DATE,'YYYYMM'))
AND TO_CHAR(OOL.REQUEST_DATE,'YYYYMM')>=NVL(P_SCHEDULE_FROM,TO_CHAR(OOL.REQUEST_DATE,'YYYYMM'))
AND TO_CHAR(OOL.REQUEST_DATE,'YYYYMM')<=NVL(P_SCHEDULE_tO,TO_CHAR(OOL.REQUEST_DATE,'YYYYMM'))
AND NVL(TER.TERRITORY,'--1') = NVL(P_TERRITORY,NVL(TER.TERRITORY,'--1'))
AND OOH.SHIP_TO_ORG_ID = NVL(P_SITE_USE_ID,OOH.SHIP_TO_ORG_ID)
GROUP BY ooh.ship_to_org_id,OOL.CREATED_BY,TER.TERRITORY,
rt.NAME,TO_CHAR (ooh.ordered_date, 'yyyymm'),
TO_CHAR (ooh.ordered_date, 'yyyy.mm.dd'),TO_CHAR (ool.REQUEST_DATE, 'yyyy.mm.dd'),
decode(ool.flow_status_code,'SHIPPED','SHIPPED','CLOSED','SHIPPED','NOTSHIPPED'),
TO_CHAR (ool.actual_shipment_date, 'yyyy.mm.dd'),
ooh.order_number,ooh.org_id, ooh.sold_to_org_id,ACV.CUSTOMER_ID,
NVL(acv.customer_name_phonetic,ACV.CUSTOMER_NAME),
ooh.transactional_curr_code,rsa.NAME;

all_count number:=0;
i number:=1;

begin
--NULL;

fnd_file.put_line(fnd_file.log,'P_ORG_ID: ' || to_char(P_ORG_ID));
fnd_file.put_line(fnd_file.log,'P_NAME: ' || P_NAME);
fnd_file.put_line(fnd_file.log,'P_TERRITORY: ' || P_TERRITORY);
fnd_file.put_line(fnd_file.log,'p_date_from: ' || p_date_from);
fnd_file.put_line(fnd_file.log,'p_date_to: ' || p_date_to);
fnd_file.put_line(fnd_file.log,'p_ACT_from: ' || p_ACT_from);
fnd_file.put_line(fnd_file.log,'p_ACT_to: ' || p_ACT_to);
fnd_file.put_line(fnd_file.log,'p_SCHEDULE_from: ' || p_SCHEDULE_from);
fnd_file.put_line(fnd_file.log,'p_SCHEDULE_to: ' || p_SCHEDULE_to);
fnd_file.put_line(fnd_file.log,'c_CUSTF: ' || to_char(p_CUSTF));
fnd_file.put_line(fnd_file.log,'c_CUSTt: ' || to_char(p_CUSTt));
fnd_file.put_line(fnd_file.log,'P_SITE_USE_ID: ' || to_char(P_SITE_USE_ID));

fnd_file.put_line(fnd_file.output,'');
fnd_file.put_line(fnd_file.output,'');
fnd_file.put_line(fnd_file.output,'fnd_file.put_line(fnd_file.output,' xmlns:o="urn:schemas-microsoft-com:office:office"');
fnd_file.put_line(fnd_file.output,' xmlns:x="urn:schemas-microsoft-com:office:excel"');
fnd_file.put_line(fnd_file.output,' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"');
fnd_file.put_line(fnd_file.output,' xmlns:html=");
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' longson.long');
fnd_file.put_line(fnd_file.output,' longson.long');
fnd_file.put_line(fnd_file.output,' 2006-02-16T03:31:41Z');
fnd_file.put_line(fnd_file.output,' globe');
fnd_file.put_line(fnd_file.output,' 11.5606');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' 7500');
fnd_file.put_line(fnd_file.output,' 14940');
fnd_file.put_line(fnd_file.output,' 60');
fnd_file.put_line(fnd_file.output,' 975');
fnd_file.put_line(fnd_file.output,' False');
fnd_file.put_line(fnd_file.output,' False');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,'

fnd_file.put_line(fnd_file.output,' ');

fnd_file.put_line(fnd_file.output,' TERRITORY');
fnd_file.put_line(fnd_file.output,' SALER');
fnd_file.put_line(fnd_file.output,' ORDER DATE');
fnd_file.put_line(fnd_file.output,' SHIP DATE');
fnd_file.put_line(fnd_file.output,' ACTUAL SHIP DATE');
fnd_file.put_line(fnd_file.output,' PI_NO');
fnd_file.put_line(fnd_file.output,' Version');
fnd_file.put_line(fnd_file.output,' Customer');
fnd_file.put_line(fnd_file.output,' Term');
fnd_file.put_line(fnd_file.output,' Currency');
fnd_file.put_line(fnd_file.output,' Fa Qty');
fnd_file.put_line(fnd_file.output,' Fa Amount');
fnd_file.put_line(fnd_file.output,' Sh Qty');
fnd_file.put_line(fnd_file.output,' Sh Amount');
fnd_file.put_line(fnd_file.output,' Ba Qty');
fnd_file.put_line(fnd_file.output,' Ba Amount');
fnd_file.put_line(fnd_file.output,' Vc Qty');
fnd_file.put_line(fnd_file.output,' Vc Amount');
fnd_file.put_line(fnd_file.output,' Other Qty');
fnd_file.put_line(fnd_file.output,' Other Amount');
fnd_file.put_line(fnd_file.output,' Amount');

fnd_file.put_line(fnd_file.output,' ');

IF p_ACT_from IS NULL AND p_ACT_TO IS NULL THEN
for rec in cur loop

fnd_file.put_line(fnd_file.log,'line: '||rec.territory||'.'||rec.saler_name||'.'||rec.pi_no);
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' '||rec.territory||'');
fnd_file.put_line(fnd_file.output,' '||rec.saler||'');
fnd_file.put_line(fnd_file.output,' '||rec.order_date||'');
fnd_file.put_line(fnd_file.output,' '||rec.ship_date||'');
fnd_file.put_line(fnd_file.output,' '||rec.actu_date||'');
fnd_file.put_line(fnd_file.output,' '||rec.pi_no||'');
fnd_file.put_line(fnd_file.output,' '||rec.version||'');
fnd_file.put_line(fnd_file.output,' '||rec.customer_name_phonetic||'');
fnd_file.put_line(fnd_file.output,' '||rec.name||'');
fnd_file.put_line(fnd_file.output,' '||rec.currency||'');
fnd_file.put_line(fnd_file.output,' '||rec.f_q||'');
fnd_file.put_line(fnd_file.output,' '||rec.f_a||'');
fnd_file.put_line(fnd_file.output,' '||rec.sh_q||'');
fnd_file.put_line(fnd_file.output,' '||rec.sh_a||'');
fnd_file.put_line(fnd_file.output,' '||rec.ba_q||'');
fnd_file.put_line(fnd_file.output,' '||rec.ba_a||'');
fnd_file.put_line(fnd_file.output,' '||rec.vc_q||'');
fnd_file.put_line(fnd_file.output,' '||rec.vc_a||'');
fnd_file.put_line(fnd_file.output,' '||rec.other_q||'');
fnd_file.put_line(fnd_file.output,' '||rec.other_a||'');
fnd_file.put_line(fnd_file.output,' '||rec.amount||'');
fnd_file.put_line(fnd_file.output,' ');

end loop;
ELSE
for rec in cur_SHIP loop

fnd_file.put_line(fnd_file.log,'line: '||rec.territory||'.'||rec.saler_name||'.'||rec.pi_no);
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' '||rec.territory||'');
fnd_file.put_line(fnd_file.output,' '||rec.saler||'');
fnd_file.put_line(fnd_file.output,' '||rec.order_date||'');
fnd_file.put_line(fnd_file.output,' '||rec.ship_date||'');
fnd_file.put_line(fnd_file.output,' '||rec.actu_date||'');
fnd_file.put_line(fnd_file.output,' '||rec.pi_no||'');
fnd_file.put_line(fnd_file.output,' '||rec.version||'');
fnd_file.put_line(fnd_file.output,' '||rec.customer_name_phonetic||'');
fnd_file.put_line(fnd_file.output,' '||rec.name||'');
fnd_file.put_line(fnd_file.output,' '||rec.currency||'');
fnd_file.put_line(fnd_file.output,' '||rec.f_q||'');
fnd_file.put_line(fnd_file.output,' '||rec.f_a||'');
fnd_file.put_line(fnd_file.output,' '||rec.sh_q||'');
fnd_file.put_line(fnd_file.output,' '||rec.sh_a||'');
fnd_file.put_line(fnd_file.output,' '||rec.ba_q||'');
fnd_file.put_line(fnd_file.output,' '||rec.ba_a||'');
fnd_file.put_line(fnd_file.output,' '||rec.vc_q||'');
fnd_file.put_line(fnd_file.output,' '||rec.vc_a||'');
fnd_file.put_line(fnd_file.output,' '||rec.other_q||'');
fnd_file.put_line(fnd_file.output,' '||rec.other_a||'');
fnd_file.put_line(fnd_file.output,' '||rec.amount||'');
fnd_file.put_line(fnd_file.output,' ');

end loop;
END IF;

fnd_file.put_line(fnd_file.output,'

end omr066x;

end gobo_om_xml_pkg;
/

最終問題得以解決.