Home » SQL & PL/SQL » SQL & PL/SQL » SQLException encounter while executing data trigger. (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
SQLException encounter while executing data trigger. [message #671276] |
Mon, 20 August 2018 14:06 |
|
vharish006
Messages: 70 Registered: August 2015 Location: Chicago
|
Member |
|
|
Hi All,
I'm encountering when trying to run a XML Publisher.
Below is the data definition:
------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<!-- Begin Data Template -->
<dataTemplate Version="1.0" defaultPackage="PQINF.XXPQ_GBL_SVC_CONT_REN_PKG" description="Acknowledged Renewals Detail Report" name="XXPQ_GBL_SVC_CONT_REN">
-<properties>
<property name="xml_tag_case" value="upper"/>
<property name="debug_mode" value="on"/>
</properties>
<parameters>
<!-- Enter Parameters -->
<parameter name="P_START_DATE" dataType="VARCHAR2"/>
<parameter name="P_END_DATE" dataType="VARCHAR2"/>
</parameters>
<!-- Enter Lexicals -->
<lexicals> </lexicals>
<!-- Enter SQL Query -->
<dataQuery>
<sqlStatement name="ACK_RENEWALS">
<![CDATA[SELECT DECODE (chrb.org_id, '81', 'US', '82', 'UK', '83', 'SPAIN')AS org,
hca.attribute1 market_type,
proq_trim(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 cleb.start_date BETWEEN '01-OCT-2017' and '31-JUL-2018'
--AND chrb.contract_number='PQ10077646'
AND TRUNC(cleb.START_DATE) BETWEEN TRUNC (apps.fnd_conc_date.string_to_date (:P_START_DATE))
AND TRUNC (apps.fnd_conc_date.string_to_date (:P_END_DATE))]]>
</sqlStatement>
</dataQuery>
<!-- Enter Grouping -->
<dataStructure>
<group name="G_PROQUEST_ACK_RENEWALS" source="ACK_RENEWALS">
<element name="ORG" value="ORG"/>
<element name="MARKET_TYPE" value="MARKET_TYPE"/>
<element name="SUB_MARKET_TYPE" value="SUB_MARKET_TYPE"/>
<element name="CONTRACT_NUMBER" value="CONTRACT_NUMBER"/>
<element name="LINE_NUMBER" value="LINE_NUMBER"/>
<element name="SHIP_ACCOUNT_NUMBER" value="SHIP_ACCOUNT_NUMBER"/>
<element name="BILL_TO_ACCOUNT_NAME" value="BILL_TO_ACCOUNT_NAME"/>
<element name="BILL_TO_ACCOUNT_NUMBER" value="BILL_TO_ACCOUNT_NUMBER"/>
<element name="SHIP_ACCOUNT_NAME" value="SHIP_ACCOUNT_NAME"/>
<element name="CUST_PO_NUMBER_REQ_YN" value="CUST_PO_NUMBER_REQ_YN"/>
<element name="SITE_DEPT" value="SITE_DEPT"/>
<element name="ADDRESS1" value="ADDRESS1"/>
<element name="ADDRESS2" value="ADDRESS2"/>
<element name="CITY" value="CITY"/>
<element name="STATE" value="STATE"/>
<element name="POSTAL_CODE" value="POSTAL_CODE"/>
<element name="COUNTRY" value="COUNTRY"/>
<element name="SHIP_COUNTRY" value="SHIP_COUNTRY"/>
<element name="BUSINESS_UNIT" value="BUSINESS_UNIT"/>
<element name="PRODUCT_FAMILY" value="PRODUCT_FAMILY"/>
<element name="ITEM" value="ITEM"/>
<element name="DESCRIPTION" value="DESCRIPTION"/>
<element name="START_DATE" value="START_DATE"/>
<element name="START_DATE_FORMATED" value="START_DATE_FORMATED"/>
<element name="END_DATE" value="END_DATE"/>
<element name="END_DATE_FORMATED" value="END_DATE_FORMATED"/>
<element name="CURRENCY_CODE" value="CURRENCY_CODE"/>
<element name="NETPRICE" value="NETPRICE"/>
<element name="CONVERSION_RATE2USD" value="CONVERSION_RATE2USD"/>
<element name="PRICE_IN_USD" value="PRICE_IN_USD"/>
<element name="BATCH_NUMBER" value="BATCH_NUMBER"/>
<element name="RENEWAL_NOTICE_NO" value="RENEWAL_NOTICE_NO"/>
<element name="SC_NOTES" value="SC_NOTES"/>
<element name="TERMINATION_REASON" value="TERMINATION_REASON"/>
<element name="STATUS_CHANGE_REASON" value="STATUS_CHANGE_REASON"/>
<element name="CREATION_DATE" value="CREATION_DATE"/>
<element name="DATE_APPROVED" value="DATE_APPROVED"/>
<element name="STS_CODE" value="STS_CODE"/>
<element name="SHIP_TO_LOCATION" value="SHIP_TO_LOCATION"/>
<element name="INV_DATE" value="INV_DATE"/>
<element name="INV_NUMBER" value="INV_NUMBER"/>
<element name="US_INTERNATIONAL" value="US_INTERNATIONAL"/>
</group>
</dataStructure>
<datatrigger name="afterReport" Source="PQINF.XXPQ_GBL_SVC_CONT_REN_PKG.afterReport_bursting()"/>
</dataTemplate>
Below is the Trigger:
[LIST TYPE=1]
[/LIST]
CREATE OR REPLACE PACKAGE PQINF.XXPQ_GBL_SVC_CONT_REN_PKG
AUTHID CURRENT_USER AS
P_START_DATE varchar2(20);
P_END_DATE varchar2(20);
FUNCTION afterReport_bursting
RETURN BOOLEAN;
END XXPQ_GBL_SVC_CONT_REN_PKG;
/
CREATE OR REPLACE PACKAGE BODY PQINF.XXPQ_GBL_SVC_CONT_REN_PKG
AS
FUNCTION afterReport_bursting
RETURN BOOLEAN
IS
l_conc_req_id NUMBER;
l_out_req_id 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),
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
''
);
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
NULL;
END afterReport_bursting;
END XXPQ_GBL_SVC_CONT_REN_PKG;
/
Log file has been uploaded
-
Attachment: Log.txt
(Size: 15.93KB, Downloaded 1776 times)
|
|
|
|
|
|
Re: SQLException encounter while executing data trigger. [message #671354 is a reply to message #671309] |
Thu, 23 August 2018 11:55 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Looking at the log file the error seems obvious:
[081618_140508953][][STATEMENT] Declare
l_flag Boolean;
BEGIN
l_flag := ;
if (l_flag) then
:XDO_OUT_PARAMETER := 1;
end if;
end;
[081618_140508958][][EXCEPTION] SQLException encounter while executing data trigger....
java.sql.SQLException: ORA-06550: line 4, column 12:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
l_flag := ;
isn't valid syntax.
Presumably there's some dynamic code somewhere that is supposed to actually supply a value there, but it isn't in your case.
Unfortunately you don't appear to have posted that code so we can't help further.
|
|
|
Goto Forum:
Current Time: Fri Sep 27 18:08:14 CDT 2024
|