+---------------------------------------------------------------------------+ Current system time is 16-AUG-2018 14:04:16 +---------------------------------------------------------------------------+ XDO Data Engine Version No: 5.6.3 Resp: 50773 Org ID : 81 Request ID: 33642719 All Parameters: P_START_DATE="2018/01/01 00:00:00":P_END_DATE="2018/02/02 00:00:00" Data Template Code: XXPQ_GBL_SVC_CONT_REN Data Template Application Short Name: XXPQCUSAPP Debug Flag: N {P_END_DATE=2018/02/02 00:00:00, P_START_DATE=2018/01/01 00:00:00} Calling XDO Data Engine... [081618_140438102][][STATEMENT] Start process Data [081618_140438102][][STATEMENT] Process Data ... [081618_140438102][][STATEMENT] Executing data triggers... [081618_140438102][][STATEMENT] BEGIN PQINF.XXPQ_GBL_SVC_CONT_REN_PKG.P_START_DATE := :P_START_DATE ; PQINF.XXPQ_GBL_SVC_CONT_REN_PKG.P_END_DATE := :P_END_DATE ; :XDO_OUT_PARAMETER := 1; END; [081618_140438102][][STATEMENT] 1:2018/01/01 00:00:00 : [081618_140438102][][STATEMENT] 2:2018/02/02 00:00:00 : [081618_140438106][][STATEMENT] Executing data triggers... [081618_140438107][][STATEMENT] P_START_DATE [081618_140438107][][STATEMENT] P_END_DATE [081618_140438107][][STATEMENT] Writing Data ... [081618_140438111][][STATEMENT] Sql Query :ACK_RENEWALS: 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)) [081618_140438111][][STATEMENT] 1: P_START_DATE:2018/01/01 00:00:00 [081618_140438111][][STATEMENT] 2: P_END_DATE:2018/02/02 00:00:00 [081618_140508953][][STATEMENT] Executing data triggers... [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: ( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe