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 Go to next message
vharish006
Messages: 68
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 409 times)
Re: SQLException encounter while executing data trigger. [message #671277 is a reply to message #671276] Mon, 20 August 2018 14:16 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
And if you run the query in SQL*Plus? What then?
Re: SQLException encounter while executing data trigger. [message #671278 is a reply to message #671277] Mon, 20 August 2018 14:31 Go to previous messageGo to next message
vharish006
Messages: 68
Registered: August 2015
Location: Chicago
Member
the query retrieved data fine. when run as concurrent program it is erroring out.

Re: SQLException encounter while executing data trigger. [message #671309 is a reply to message #671278] Tue, 21 August 2018 07:23 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
I don't see any trigger, only a package.

I'd start with fnd_request.submit_request and fnd_conc_date.string_to_date.

[Updated on: Tue, 21 August 2018 07:25]

Report message to a moderator

Re: SQLException encounter while executing data trigger. [message #671354 is a reply to message #671309] Thu, 23 August 2018 11:55 Go to previous message
cookiemonster
Messages: 13894
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.
Previous Topic: find circular reference in data
Next Topic: Oracle regular expressions query
Goto Forum:
  


Current Time: Mon Sep 28 18:02:03 CDT 2020