Friday, August 09, 2013

Oracle BI Publisher Desktop Microsoft Visual Basic System Error &H8000FFFF -2147418113 Catastrophic failure

Not too sure what I changed on my laptop, possibly just installation of another product or perhaps Microsoft or similar updates, but when I went to edit a BI Publisher RTF template I got the following error message on clicking ANY button in the BI Publisher plugin for Microsoft Word.

---------------------------
Microsoft Visual Basic
---------------------------
System Error &H8000FFFF (-2147418113). Catastrophic failure 
---------------------------
OK   Help   
---------------------------

After quite a bit of researching, I found the solution, for me it was the second of the following fixes:

1. Delete the EXD files. I attempted this in the root of my C:\ and D:\ drives, but it didn't fix the error.

DEL /S /A:H /A:-H *.EXD

2. Change security setting, and then reboot Windows.

2a. For Windows 32bit:

Regsvr32 "C:\Windows\System32\MSCOMCTL.OCX"

2b. For Windows 64bit:

Regsvr32 "C:\Windows\SysWOW64\MSCOMCTL.OCX"

Problem solved!

Catch ya!

Gareth

This is a post from Gareth's blog at http://garethroberts.blogspot.com

References

Related Posts

Friday, February 22, 2013

NZOUG 2013 Conference: Agenda online - register while Earlybird Pricing still here!

The New Zealand Oracle Users Group - NZOUG Conference 2013 is open for registrations and Earlybird pricing is still available! The agenda is now available.

NZOUG 2013 is being held in Wellington at the iconic Te Papa Museum on the beautiful waterfront on the 18th and 19th of March, with an additional Workshop Day on the 20th March.

The conference will see a wealth of brilliant speakers from around the world, including Tom Kyte, Graham Wood, Andrew Holdsworth, John Schiff and Nadia Bendjedou and of course top speakers from Middle Earth (NZ) as well. Tom, Andrew and Graham will be presenting their Real World Performance show, which will be a highlight of the conference.

Topics being covered cross a wide range of Oracle solutions, with tracks for Development, DBA, Cloud Computing, Oracle E-Business Suite, a full dedicated JD Edwards Day, Fusion Applications, EPM/BI, Middleware, Management, Infrastructure, Security, Hardware and Professional Development.

I'll be co-presenting one paper on utilizing the Oracle E-Business Suite Integrated SOA Gateway, tips, tricks and a demo, not too much this time around so that I can head along to as many of the other presentations as I can. Drop me an email if you want to catch up at the conference!

So, if you're around New Zealand in mid-March, perhaps following on from the NZ - England Cricket Test, and if you like getting out and networking often with fun consequences, then make sure you come to the New Zealand Oracle event of the year, not to be missed!

Catch ya!

Gareth

This is a post from Gareth's blog at http://garethroberts.blogspot.com

References

Monday, October 03, 2011

R12 E-Business Suite Suppliers Query - SQL to join Suppliers, Contacts, Banks

In the Oracle E-Business Suite (EBS) Release 12 the data model of Suppliers has become much more complex. The base tables have changed (Suppliers, Sites, Bank Accounts, Contacts) and some of the fields have become obsolete.

Here is a query to bring many of the Supplier attributes together, with focus on banks / bank accounts, payment methods, contacts, remittance delivery (email, notification method). Please post comments if you find any issues!

Adjust the WHERE clause on the first WITH query to return the suppliers that you need to report on. Hope this query helps someone out.

Add additional fields to the final query (or WITH queries as required.)

Update 29-Feb-2012: Outer join sites ss to payment methods pm.

with vendors as
(
select vendor_id
from   ap_suppliers
where  1=1
/* COMMENT / UNCOMMENT and UPDATE THE NEXT 5 LINES AS YOU REQUIRE */
--and    vendor_type_lookup_code = 'VENDOR'
--and    upper( vendor_name ) like 'VIRTUATE%'
and    creation_date between '01-JAN-2011' and '01-JAN-2012'
--and    enabled_flag = 'Y'
)
, vend as
(
select pv.vendor_id                 vendor_id
,      pv.vendor_name_alt           vendor_name_alt
,      pv.vendor_name               vendor_name
,      pv.segment1                  vendor_number
,      pv.vendor_type_lookup_code   vendor_type_lookup_code
from   ap_suppliers pv
where  pv.vendor_id in (select v.vendor_id from vendors v)
)
, site as
(
select ss.vendor_id                    vendor_id
,      ss.vendor_site_id               vendor_site_id
,      ss.vendor_site_code             vendor_site_code
,      ss.vendor_site_code_alt         vendor_site_code_alt
,      ss.vat_code                     tax_code
,      ss.vat_registration_num         vat_registration_num
,      t.name                          terms_name
,      ss.address_line1                ss_address_line1
,      ss.address_line2                ss_address_line2
,      ss.address_line3                ss_address_line3
,      ss.zip                          ss_zip          
,      ss.city                         ss_city         
,      ss.state                        ss_state        
,      ss.country                      ss_country      
,      ss.area_code                    ss_area_code    
,      ss.phone                        ss_phone        
,      ss.fax_area_code                ss_fax_area_code
,      ss.fax                          ss_fax          
,      ss.telex                        ss_telex
,      ss.pay_site_flag                ss_pay_site_flag
,      ss.primary_pay_site_flag        ss_primary_pay_site_flag
,      pm.remit_advice_delivery_method ss_remit_advice_deliv_meth
,      pm.remit_advice_email           ss_remit_advice_email
,      pm.remit_advice_fax             ss_remit_advice_fax
,      pm.payment_method_code          ss_payment_method_code
,      ss.remittance_email             ss_remittance_email
,      ss.supplier_notif_method        ss_supplier_notif_method
,      ps.addressee                    ss_addressee
,      ( select hcp.phone_area_code
         from   hz_contact_points hcp
         where  hcp.owner_table_id = ss.party_site_id
         and    hcp.owner_table_name = 'HZ_PARTY_SITES'
         and    hcp.phone_line_type = 'GEN'
         and    hcp.contact_point_type = 'PHONE'
         --and    hcp.created_by_module = 'AP_SUPPLIERS_API'
         and    rownum < 2 -- copied from OAF View Object 
       ) ss_hcp_phone_area_code
,      ( select hcp.phone_number
         from   hz_contact_points hcp
         where  hcp.owner_table_id = ss.party_site_id
         and    hcp.owner_table_name = 'HZ_PARTY_SITES'
         and    hcp.phone_line_type = 'GEN'
         and    hcp.contact_point_type = 'PHONE'
         --and    hcp.created_by_module = 'AP_SUPPLIERS_API'
         and    rownum < 2 -- copied from OAF View Object 
       ) ss_hcp_phone_number
,      ( select hcp.phone_area_code
         from   hz_contact_points hcp
         where  hcp.owner_table_id = ss.party_site_id
         and    hcp.owner_table_name = 'HZ_PARTY_SITES'
         and    hcp.phone_line_type = 'FAX'
         and    hcp.contact_point_type = 'PHONE'
         --and    hcp.created_by_module = 'AP_SUPPLIERS_API'
         and    rownum < 2 -- copied from OAF View Object 
       ) ss_hcp_fax_area_code
,      ( select hcp.phone_number
         from   hz_contact_points hcp
         where  hcp.owner_table_id = ss.party_site_id
         and    hcp.owner_table_name = 'HZ_PARTY_SITES'
         and    hcp.phone_line_type = 'FAX'
         and    hcp.contact_point_type = 'PHONE'
         --and    hcp.created_by_module = 'AP_SUPPLIERS_API'
         and    rownum < 2 -- copied from OAF View Object 
       ) ss_hcp_fax_number
from   ap_supplier_sites_all ss
,      ap_suppliers sup
,      ap_terms t
,      (
         select ss.vendor_site_id
              , payee.remit_advice_delivery_method
              , payee.remit_advice_email
              , payee.remit_advice_fax
              , pm.payment_method_code
         from   iby_external_payees_all payee
         ,      iby_ext_party_pmt_mthds pm
         ,      hz_party_sites ps
         ,      ap_supplier_sites_all ss
         where  payee.payee_party_id = ps.party_id
         and    payee.payment_function = 'PAYABLES_DISB'
         and    payee.party_site_id = ss.party_site_id
         and    payee.supplier_site_id = ss.vendor_site_id
         and    payee.org_id = ss.org_id
         and    payee.org_type = 'OPERATING_UNIT'
         and    ss.party_site_id = ps.party_site_id
         and    payee.ext_payee_id = pm.ext_pmt_party_id (+)
         and    pm.primary_flag (+) = 'N'
         and not exists
                ( select 1
                  from   iby_ext_party_pmt_mthds pm2
                  where  pm.ext_pmt_party_id = pm2.ext_pmt_party_id
                  and    pm2.primary_flag = 'Y'
                )
         union all
         select ss.vendor_site_id
              , payee.remit_advice_delivery_method
              , payee.remit_advice_email
              , payee.remit_advice_fax
              , pm.payment_method_code
         from   iby_external_payees_all payee
         ,      iby_ext_party_pmt_mthds pm
         ,      hz_party_sites ps
         ,      ap_supplier_sites_all ss
         where  payee.payee_party_id = ps.party_id
         and    payee.payment_function = 'PAYABLES_DISB'
         and    payee.party_site_id = ss.party_site_id
         and    payee.supplier_site_id = ss.vendor_site_id
         and    payee.org_id = ss.org_id
         and    payee.org_type = 'OPERATING_UNIT'
         and    ss.party_site_id = ps.party_site_id
         and    pm.ext_pmt_party_id = payee.ext_payee_id
         and    pm.primary_flag = 'Y'
       ) pm
,      hz_party_sites ps
where  sup.vendor_id in (select vendor_id from vendors)
and    sup.vendor_id = ss.vendor_id
and    ss.vendor_site_id = pm.vendor_site_id (+)
and    ss.party_site_id = ps.party_site_id (+)
and    ss.terms_id = t.term_id (+)
)
, cont as
(
select pv.vendor_id           vendor_id
,      pvs.vendor_site_id     vendor_site_id
,      hp.party_id            c_party_id
,      hp.person_first_name   c_first_name
,      hp.person_last_name    c_last_name
,      hp.person_title        c_person_title
,      hcpe.email_address     c_email_address
,      hcpp.phone_area_code   c_phone_area_code
,      hcpp.phone_number      c_phone_number
,      hcpf.phone_area_code   c_fax_area_code
,      hcpf.phone_number      c_fax_number
from   hz_parties hp
,      hz_relationships hzr
,      hz_contact_points hcpp
,      hz_contact_points hcpf
,      hz_contact_points hcpe
,      ap_suppliers pv
,      ap_supplier_sites_all pvs
,      hz_party_sites hps
where  hp.party_id = hzr.subject_id
and    hzr.relationship_type = 'CONTACT'
and    hzr.relationship_code = 'CONTACT_OF'
and    hzr.subject_type = 'PERSON'
and    hzr.subject_table_name = 'HZ_PARTIES'
and    hzr.object_type = 'ORGANIZATION'
and    hzr.object_table_name = 'HZ_PARTIES'
and    hzr.status = 'A'
and    hcpp.owner_table_name(+) = 'HZ_PARTIES'
and    hcpp.owner_table_id(+) = hzr.party_id
and    hcpp.phone_line_type(+) = 'GEN'
and    hcpp.contact_point_type(+) = 'PHONE'
and    hcpf.owner_table_name(+) = 'HZ_PARTIES'
and    hcpf.owner_table_id(+) = hzr.party_id
and    hcpf.phone_line_type(+) = 'FAX'
and    hcpf.contact_point_type(+) = 'PHONE'
and    hcpe.owner_table_name(+) = 'HZ_PARTIES'
and    hcpe.owner_table_id(+) = hzr.party_id
and    hcpe.contact_point_type(+) = 'EMAIL'
and    hcpp.status (+)='A'
and    hcpf.status (+)='A'
and    hcpe.status (+)='A'
and    hps.party_id = hzr.object_id
and    pvs.party_site_id = hps.party_site_id
and    pv.vendor_id = pvs.vendor_id
and    exists
       ( select 1
         from ap_supplier_contacts ascs
         where (ascs.inactive_date is null
         or ascs.inactive_date      > sysdate)
         and hzr.relationship_id    = ascs.relationship_id
         and hzr.party_id           = ascs.rel_party_id
         and hps.party_site_id      = ascs.org_party_site_id
         and hzr.subject_id         = ascs.per_party_id
       )
and    pv.vendor_id in (select vendor_id from vendors)
)
, bank as
(
select  pv.vendor_id                    vendor_id
,       ss.vendor_site_id               vendor_site_id
,       hopbank.bank_or_branch_number   bank_number
,       hopbranch.bank_or_branch_number branch_number
,       eba.bank_account_num            bank_account_num
,       eba.bank_account_name           bank_account_name
,       piu.start_date                  bank_use_start_date
,       piu.end_date                    bank_use_end_date
,       piu.order_of_preference         bank_priority
from    iby_ext_bank_accounts eba
,       iby_external_payees_all payee
,       iby_pmt_instr_uses_all piu
,       ap_supplier_sites_all ss
,       ap_suppliers pv
,       hz_organization_profiles hopbank
,       hz_organization_profiles hopbranch
where   1=1
and     eba.bank_id = hopbank.party_id
and     eba.branch_id = hopbranch.party_id
and     payee.payment_function = 'PAYABLES_DISB'
and     payee.party_site_id = ss.party_site_id
and     payee.supplier_site_id = ss.vendor_site_id
and     payee.org_id = ss.org_id
and     payee.org_type = 'OPERATING_UNIT'
and     payee.ext_payee_id = piu.ext_pmt_party_id
and     piu.payment_flow = 'DISBURSEMENTS'
and     piu.instrument_type = 'BANKACCOUNT'
and     piu.instrument_id = eba.ext_bank_account_id
and     piu.start_date < sysdate
and     ( piu.end_date is null or
          piu.end_date > sysdate
        )
and     ss.vendor_id = pv.vendor_id
and     pv.vendor_id in (select vendor_id from vendors)
)
-- select distinct v.*, s.*, c.*, b.*
select distinct v.vendor_id             supplier_id
,      v.vendor_number                  supplier_num
,      v.vendor_name                    supplier_name
,      v.vendor_type_lookup_code        supplier_type
,      s.terms_name                     terms_name
,      s.tax_code                       invoice_tax_code
,      s.vat_registration_num           vat_registration_num
,      s.vendor_site_code               site_code
,      s.ss_address_line1               address1
,      s.ss_address_line2               address2
,      s.ss_address_line3               address3
,      s.ss_city                        suburb
,      s.ss_state                       state
,      s.ss_zip                         post_code
,      s.ss_country                     country
,      s.ss_payment_method_code         payment_method
,      b.bank_account_name              bank_account_name
,      b.bank_number                    bank_number
,      b.branch_number                  branch_number
,      b.bank_account_num               bank_account_num
,      s.ss_remit_advice_email          remittance_email
,      s.ss_remit_advice_deliv_meth     notification_method
,      c.c_first_name                   contact_first_name
,      c.c_last_name                    contact_last_name
,      c.c_person_title                 contact_title
,      c.c_email_address                contact_email
,      c.c_phone_area_code              contact_ph_area_code
,      c.c_phone_number                 contact_ph_number
,      c.c_fax_area_code                contact_fax_area_code
,      c.c_fax_number                   contact_fax_number
from   vend v
,      site s
,      cont c
,      bank b
where  v.vendor_id = s.vendor_id (+)
and    s.vendor_id = b.vendor_id (+)
and    s.vendor_site_id = b.vendor_site_id (+)
and    s.vendor_id = c.vendor_id (+)
and    s.vendor_site_id = c.vendor_site_id (+)
and    nvl(b.bank_priority,-1) = (select nvl(min(bank_priority),-1)
                                  from   bank b2
                                  where  b2.vendor_id = b.vendor_id
                                  and    b2.vendor_site_id = b.vendor_site_id)
order by 3,1,2,4,5,6,7,8,9,10,11,12,13;

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com

Related Posts

Thursday, August 11, 2011

Track and Trace E-Business Suite Concurrent Request - EBS Logs, O/S Process IDs, Locks

I often get asked to take a look at an Oracle eBusiness Suite concurrent request to see what it is doing, this can come from a few different angles:

  1. What requests are currently running?
  2. I have an operating system process that is taking too much CPU - what is it doing?
  3. Can you tell me where the request is at? I've clicked on the log but it doesn't show anything!
  4. My request is taking too long - can you check for blocking locks?

There are a number of strategies to track and trace where things are at for a running request, these include:

  1. Monitor the currently running requests in fnd_concurrent_requests
  2. Checking the v$sqlarea to see what SQL statement or PL/SQL is running
  3. Tailing the concurrent request log / output file while it is being written to near realtime - prior to request completing
  4. Checking for locks blocking the concurrent request

So without further ado, let's take a look at the following sweet query. UPDATE: 23-Aug-2012 fixed multi rows due to missing join on inst_id between gv$session and gv$process. Also note for non-RAC environments change gv$ to v$ and remove joins to sys.v_$active_instances:

set pages 9999 feed on lines 150
col user_concurrent_program_name format a40 head PROGRAM trunc
col elapsed format 9999
col request_id format 9999999 head REQUEST
col user_name format a12
col oracle_process_id format a5 head OSPID
col inst_name format a10
col sql_text format a30
col outfile_tmp format a30
col logfile_tmp format a30

REM *********************
REM **** RAC VERSION ****
REM *********************
select /*+ ordered */ 
       fcp.user_concurrent_program_name
,      fcr.request_id
,      round(24*60*( sysdate - actual_start_date )) elapsed
,      fu.user_name
,      fcr.oracle_process_id
,      sess.sid
,      sess.serial#
,      inst.inst_name
,      sa.sql_text
,      cp.plsql_dir || '/' || cp.plsql_out outfile_tmp
,      cp.plsql_dir || '/' || cp.plsql_log logfile_tmp
from   apps.fnd_concurrent_requests fcr
,      apps.fnd_concurrent_programs_tl fcp
,      apps.fnd_concurrent_processes cp
,      apps.fnd_user fu
,      gv$process pro
,      gv$session sess
,      gv$sqlarea sa
,      sys.v_$active_instances inst
where  fcp.concurrent_program_id = fcr.concurrent_program_id
and    fcp.application_id = fcr.program_application_id
and    fcr.controlling_manager = cp.concurrent_process_id
and    fcr.requested_by = fu.user_id (+)
and    fcr.oracle_process_id = pro.spid (+)
and    pro.addr = sess.paddr (+)
and    pro.inst_id = sess.inst_id (+)
and    sess.sql_address = sa.address (+)
and    sess.sql_hash_value = sa.hash_value (+)
and    sess.inst_id = inst.inst_number (+)
and    fcr.phase_code = 'R' /* only running requests */
;

REM *********************
REM ** NON-RAC VERSION **
REM *********************
select /*+ ordered */ 
       fcp.user_concurrent_program_name
,      fcr.request_id
,      round(24*60*( sysdate - actual_start_date )) elapsed
,      fu.user_name
,      fcr.oracle_process_id
,      sess.sid
,      sess.serial#
,      sa.sql_text
,      cp.plsql_dir || '/' || cp.plsql_out outfile_tmp
,      cp.plsql_dir || '/' || cp.plsql_log logfile_tmp
from   apps.fnd_concurrent_requests fcr
,      apps.fnd_concurrent_programs_tl fcp
,      apps.fnd_concurrent_processes cp
,      apps.fnd_user fu
,      v$process pro
,      v$session sess
,      v$sqlarea sa
where  fcp.concurrent_program_id = fcr.concurrent_program_id
and    fcp.application_id = fcr.program_application_id
and    fcr.controlling_manager = cp.concurrent_process_id
and    fcr.requested_by = fu.user_id (+)
and    fcr.oracle_process_id = pro.spid (+)
and    pro.addr = sess.paddr (+)
and    sess.sql_address = sa.address (+)
and    sess.sql_hash_value = sa.hash_value (+)
and    fcr.phase_code = 'R' /* only running requests */
;

PROGRAM                                   REQUEST ELAPSED USER_NAME    OSPID        SID    SERIAL# INST_NAME  SQL_TEXT                       OUTFILE_TMP                    LOGFILE_TMP
---------------------------------------- -------- ------- ------------ ----- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------------
Workflow Background Process               2960551       1 VIRTUATE     24814        130      29699 APPLPROD1  BEGIN WF_ENGINE.BACKGROUNDCONC /usr/tmp/o0068194.tmp          /usr/tmp/l0068194.tmp
                                                                                                              URRENT(:errbuf,:rc,:A0,:A1,:A2
                                                                                                              ,:A3,:A4,:A5); END;

1 row selected.

From the above we can see key information:

  • The running Concurrent Request Program Name and Request_ID
  • The database node operating system process id (OSPID) so we can monitor usage via top / iostat / vmstat
  • The SID / Serial in case we want to kill the session via alter system kill session '130,29699';
  • The instance name the database session is running on in a RAC environment
  • The currently running SQL text
  • The temporary files where concurrent request log/out is being written to via utl_file while running. These files are copied over to the Concurrent Tier $APPLCSF/$APPLOUT and $APPLLOG after completion of the request.

We can break out the above into a few queries and procedures to drill into specific information information from the core EBS tables and DBA v$ views

1. Get concurrent requests running, temp files and Operating System PID

col user_concurrent_program_name format a40 head PROGRAM trunc
col elapsed format 9999
col request_id format 9999999 head REQUEST
col user_name format a12
col oracle_process_id format a5 head OSPID
select  fcp.user_concurrent_program_name
,       fcr.request_id
,       round(24*60*( sysdate - actual_start_date )) elapsed
,       fu.user_name
,       fcr.oracle_process_id
from    apps.fnd_concurrent_requests fcr
,       apps.fnd_concurrent_programs_tl fcp
,       apps.fnd_user fu
where   fcp.concurrent_program_id = fcr.concurrent_program_id
and     fcp.application_id = fcr.program_application_id
and     fu.user_id = fcr.requested_by
and     fcr.phase_code = 'R';

PROGRAM                                   REQUEST ELAPSED USER_NAME    OSPID
---------------------------------------- -------- ------- ------------ -----
Virtuate GL OLAP Data Refresh             2960541       5 VIRTUATE     21681

2. Get current running sql from instance (RAC inst_id/name) where request is running

col inst_name format a10
col sql_text format a30
col module format a20

REM *********************
REM **** RAC VERSION ****
REM *********************
select sess.sid
,      sess.serial#
,      sess.module
,      sess.inst_id
,      inst.inst_name
,      sa.fetches
,      sa.runtime_mem
,      sa.sql_text
,      pro.spid
from   gv$sqlarea sa
,      gv$session sess
,      gv$process pro
,      sys.v_$active_instances inst
where  sa.address = sess.sql_address
and    sa.hash_value = sess.sql_hash_value
and    sess.paddr = pro.addr
and    sess.inst_id = pro.inst_id
and    sess.inst_id = inst.inst_number (+)
and    pro.spid = &OSPID_from_running_request;

REM *********************
REM ** NON-RAC VERSION **
REM *********************

select sess.sid
,      sess.serial#
,      sess.module
,      sa.fetches
,      sa.runtime_mem
,      sa.sql_text
,      pro.spid
from   v$sqlarea sa
,      v$session sess
,      v$process pro
where  sa.address = sess.sql_address
and    sa.hash_value = sess.sql_hash_value
and    sess.paddr = pro.addr
and    pro.spid = &OSPID_from_running_request;

If you're running something that has long SQL statements, get the full SQL Statement by selecting from v$sqltext_with_newlines as follows

select t.sql_text
from   v$sqltext_with_newlines t
,      v$session s
where  s.sid = &SID
and    s.sql_address = t.address
order by t.piece

3. Find currently spooling temp file from request

col outfile format a30
col logfile format a30
select cp.plsql_dir || '/' || cp.plsql_out outfile
,      cp.plsql_dir || '/' || cp.plsql_log logfile
from  apps.fnd_concurrent_requests cr
,     apps.fnd_concurrent_processes cp
where cp.concurrent_process_id = cr.controlling_manager
and cr.request_id = &request_id;

OUTFILE                        LOGFILE
------------------------------ ------------------------------
/usr/tmp/PROD/o0068190.tmp     /usr/tmp/PROD/l0068190.tmp

REM Now tail log file on database node to see where it is at, near realtime
REM tail -f /usr/tmp/l0068190.tmp

Then on the Database node you can tail -f the above plsql_out or plsql_log files to see where program is at. Combine this with good logging techniques (date/time stamp on each entry) and you'll be able to know where your program is at.

If locks are the potential problem, then drill into those:

Currently held locks per concurrent request

set lines 150
col object_name format a32
col mode_held format a15
select /*+ ordered */
       fcr.request_id
,      object_name
,      object_type
,      decode( l.block
             , 0, 'Not Blocking'
             , 1, 'Blocking'
             , 2, 'Global'
             ) status
,      decode( v.locked_mode
             , 0, 'None'
             , 1, 'Null'
             , 2, 'Row-S (SS)'
             , 3, 'Row-X (SX)'
             , 4, 'Share'
             , 5, 'S/Row-X (SSX)'
             , 6, 'Exclusive'
             , to_char(lmode)
             ) mode_held
from   apps.fnd_concurrent_requests fcr
,      gv$process pro
,      gv$session sess
,      gv$locked_object v
,      gv$lock l
,      dba_objects d
where  fcr.phase_code = 'R'
and    fcr.oracle_process_id = pro.spid (+)
and    pro.addr = sess.paddr (+)
and    sess.sid = v.session_id (+)
and    v.object_id = d.object_id (+)
and    v.object_id = l.id1 (+)
;

REQUEST_ID OBJECT_NAME                      OBJECT_TYPE         STATUS       MODE_HELD
---------- -------------------------------- ------------------- ------------ ---------------
   1070780 VIRTUATE_GL_OLAP_REFRESH         TABLE               Not Blocking Exclusive

So there you have it - enough tools to keep you happy Track n Tracing! Maybe next time we'll look at tracing with bind / waits or PL/SQL Profiling concurrent programs

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com

Related Posts

Tuesday, September 21, 2010

Enhancement Requests and Bugs for Oracle eBusiness Suite

From time to time I create enhancement requests or bugs, commonly on Oracle eBusiness Suite. Additionally I come across enhancement requests that I think deserve more visibility. This page is dedicated to that cause. If you have an Oracle Enhancement Request (ER) or Bug you would like to raise the visibility on, please feel free to comment or contact me.

If you like the looks of one of the Enhancement Requests noted below, log a Service Request (SR) on My Oracle Support (MOS) and ask for your MOS Customer record to be attached to the ER / Bug.

Created Logged By Bug / ER Status Product Description
2013/10/07 See ER 17069048 Open BIP Enable use of recursive with clause
2013/06/05 See ER 12872320 Open BIP Request to add autofilter functionality to native Excel template (binary)
2012/01/22 Gareth 13616572 Open BIP BI Publisher RTF unable to show a fixed number of lines for a word wrapped cell
2011/05/13 See ER 12423249 Open BIP Adding support of SMTP username/password to "XAPI:EMAIL" bursting for E-Business Suite (EBS) 12.1.X
2010/11/09 See ER 8208646 Open AP/IBY R12 Unable to send Separate Remittance Advice as email attachment in PDF
2010/08/11 Gareth 10019593 Rejected AP/IBY ARXRWMAI - In Receipts Summary form, cannot query by payment server id - PSON (Payment Server Order Number)
2010/07/14 See ER 9908713 Open BIP Need the DeliveryManager API to have a trackback variable for bounced emails
2010/07/18 See ER 9918364 Open BIP Need email body to be set to UTF-8 charset when using East Asian language emails
2010/07/08 Gareth 9891120 Open eAM Need hook to extend EAM Maintenance Work Order Detail Report
2010/06/19 Gareth 9834226 Open AP Need to embed email images Payables/Payments "Send Separate Remittance Advices"
2009/01/07 See ER 7694052 Open AP Does the Supplier Open Interface API Support Updates to Supplier Information?
2005/03/25 See ER 4262148 Open AP Need Public API to manage custom AP Invoice Holds

PS. If you would like to monitor Enhancement Requests, and be notified by Oracle of status updates, create a Favorite in My Oracle Support and ensure Hot Topics (Favorites) emails are on as follows.

Create a Favorite:

  1. Login to My Oracle Support
  2. Search on the ER / Bug number in the Search box e.g. 4262148
  3. Click on the orange star next to the Title in the list view, or click on the article and click on the orange star. You should see "Favorite added" status message.

Switch on Hot Topics Emails:

  1. Go to the Settings tab
  2. Click on Hot-Topics Email under Personal on the left side menu list.
  3. Switch On the Hot Topics email, every 1 days (or suitable), and ensure you have checked "Product Bugs Marked as Favorites"
  4. Save

To see your Favorites, click on the Knowledge tab, then click Favorite Articles in the Recent Activity tab.

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com

Related Posts

 

Thursday, August 12, 2010

EBS Bursting: Filter on XML Elements using XPATH in Bursting Control File

Just a quick post to give an example of a bursting control file that has multiple emails, with a filter based on XML Element in the data to select which email to send.

Oracle EBusiness Suite XML / BI Publisher Bursting Control File Example - Multiple Email Filter

Here it is:

<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
<xapi:globalData location="stream"/>
<xapi:request select="/ARXSGPO_CPG/LIST_G_SETUP/G_SETUP/LIST_G_STATEMENT/G_STATEMENT">
<xapi:delivery>
<xapi:email server="${XXX_SMTP}" port="25" from="${XXX_SEND_FROM}" reply-to ="${XXX_REPLY_TO}">
<xapi:message id="email1" to="${XXX_CUST_EMAIL}" cc="${XXX_ARCHIVE_EMAIL}" attachment="true" content-type="html/text" subject="Statement from ${ORG_NAME} - ${STATEMENT_DATE}">Hello,
Please find attached the Statement for period to ${STATEMENT_DATE}.
${ORG_NAME}
Internal Ref: Customer Email
</xapi:message>
</xapi:email>
<xapi:email server="${XXX_SMTP}" port="25" from="${XXX_SEND_FROM}" reply-to ="${XXX_REPLY_TO}">
<xapi:message id="email2" to="${XXX_AGENT_EMAIL}" cc="${XXX_ARCHIVE_EMAIL}" attachment="true" content-type="html/text" subject="Statement from ${ORG_NAME} - ${STATEMENT_DATE}">Hello,
Please find attached the Statement for period to ${STATEMENT_DATE}.
Regards,
${ORG_NAME}
Internal Ref: Agent Email
</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document key="${CUSTOMER_ID}_1" output="${XXX_SHORTNAME}_Statement_${STATEMENT_DATE}" output-type="pdf" delivery="email1">
<xapi:template type="rtf" location="xdo://AR.XXX_STATEMENT_PRINT.en.00/?getSource=true" filter="/ARXSGPO_CPG/LIST_G_SETUP/G_SETUP/LIST_G_STATEMENT/G_STATEMENT[XXX_CUST_MODE='Email']"/>
</xapi:document>
<xapi:document key="${CUSTOMER_ID}_2" output="${XXX_SHORTNAME}_Statement_${STATEMENT_DATE}_Agent" output-type="pdf" delivery="email2">
<xapi:template type="rtf" location="xdo://AR.XXX_STATEMENT_PRINT.en.00/?getSource=true" filter="/ARXSGPO_CPG/LIST_G_SETUP/G_SETUP/LIST_G_STATEMENT/G_STATEMENT[XXX_AGENT_MODE='Email']"/>
</xapi:document>
</xapi:request>
</xapi:requestset>

Catch ya!
Gareth

This is a post from Gareth's blog at http://garethroberts.blogspot.com

References

Related Posts

Tuesday, April 06, 2010

Environment Variables from database table - Oracle E-Business Suite

Are you running Oracle E-Business Suite (EBS) / Applications and want to get an operating system level environment variable value from a database table, for example for use in PL/SQL? Or perhaps to default a concurrent program parameter? Didn't think environment variables were stored in the database?

Try out out this query that shows you $FND_TOP:

select value
from   fnd_env_context
where  variable_name = 'FND_TOP'
and    concurrent_process_id = 
      ( select max(concurrent_process_id) from fnd_env_context );

VALUE
--------------------------------------------------------------------------------
/d01/oracle/VIS/apps/apps_st/appl/fnd/12.0.0

Or did you want to find out the Product "TOP" directories e.g the full directory path values from fnd_appl_tops under APPL_TOP?

col variable_name format a15
col value format a64
select variable_name, value
from   fnd_env_context
where  variable_name like '%\_TOP' escape '\'
and    concurrent_process_id = 
     ( select max(concurrent_process_id) from fnd_env_context )
order by 1;

VARIABLE_NAME   VALUE
--------------- ----------------------------------------------------------------
AD_TOP          /d01/oracle/VIS/apps/apps_st/appl/ad/12.0.0
AF_JRE_TOP      /d01/oracle/VIS/apps/tech_st/10.1.3/appsutil/jdk/jre
AHL_TOP         /d01/oracle/VIS/apps/apps_st/appl/ahl/12.0.0
AK_TOP          /d01/oracle/VIS/apps/apps_st/appl/ak/12.0.0
ALR_TOP         /d01/oracle/VIS/apps/apps_st/appl/alr/12.0.0
AME_TOP         /d01/oracle/VIS/apps/apps_st/appl/ame/12.0.0
AMS_TOP         /d01/oracle/VIS/apps/apps_st/appl/ams/12.0.0
AMV_TOP         /d01/oracle/VIS/apps/apps_st/appl/amv/12.0.0
AMW_TOP         /d01/oracle/VIS/apps/apps_st/appl/amw/12.0.0
APPL_TOP        /d01/oracle/VIS/apps/apps_st/appl
AP_TOP          /d01/oracle/VIS/apps/apps_st/appl/ap/12.0.0
AR_TOP          /d01/oracle/VIS/apps/apps_st/appl/ar/12.0.0
...

Or perhaps the full directory path to $APPLTMP?

select value
from   fnd_env_context
where  variable_name = 'APPLTMP'
and    concurrent_process_id = 
      ( select max(concurrent_process_id) from fnd_env_context );

VALUE
--------------------------------------------------------------------------------
/d01/oracle/VIS/inst/apps/VIS_demo/appltmp

NB: These queries assume your concurrent managers are running!

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com

References

Update - Added example output and $APPLTMP output.

Monday, March 22, 2010

SQL: Override Address for Workflow Notification Mailer in the Oracle E-Business Suite

In the Oracle E-Business Suite I often need to develop code that sends emails. Easy enough to jump in and code it using a variety of options like workflow, utl_smtp etc. but what sort of things do we need to consider here?

  • Don't hardcode Outbound SMTP Server details in your code
  • Source the SMTP Server details from a single location
  • For test/development environments we don't want to send out email to real email addresses, so use an email address for test purposes and source it from a single location
  • If you select an email address from a record in the database, then put in an IF or CASE or DECODE statement to check if we should be using a test address and use that instead of the real email address!

So what we should do is pickup the settings from the Workflow Notification Mailer service component for the Outbound SMTP Server and Override Address and use them appropriately.

That way emails won't go to recipients from Test / Development environments where the Override / Test Email Address is set.

Workflow Notification Mailer Outbound SMTP Server

We can get the Outbound SMTP Server Name using the following SQL:

select fscpv.parameter_value smtp_server_name
from   fnd_svc_comp_params_tl fscpt
,      fnd_svc_comp_param_vals fscpv
,      fnd_svc_components fsc
where  fscpt.parameter_id = fscpv.parameter_id
and    fscpv.component_id = fsc.component_id
and    fscpt.display_name = 'Outbound Server Name'
and    fsc.component_name = 'Workflow Notification Mailer';

Workflow Notification Mailer Override Address or Test Address

And the Override Address (or Test Address in older terminology) from the following SQL:


select fscpv.parameter_value test_address
from   fnd_svc_comp_params_tl fscpt
,      fnd_svc_comp_param_vals fscpv
,      fnd_svc_components fsc
where  fscpt.parameter_id = fscpv.parameter_id
and    fscpv.component_id = fsc.component_id
and    fscpt.display_name = 'Test Address'
and    fsc.component_name = 'Workflow Notification Mailer';

And then we can use them consistently... perhaps following coding practises and wrapping the above in a PL/SQL function would be good!

Note that the above SQL assumes you are using the default seeded Workflow Notification Mailer queue and haven't set up your own. If you have then replace the name Workflow Notification Mailer in the SQL with the name you used.

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com

Related Posts

Thursday, March 04, 2010

NZOUG Conference 2010 Agenda online now!

I'm pleased to advise that the agenda is now online for the NZOUG 2010 Conference in Rotorua, March 15th / 16th.

The lineup of speakers is excellent, including a stack of Oracle Ace Directors:

  • Tim Hall
  • Chris Muir
  • Daniel Morgan
  • Franciso Munoz Alvarez
  • Robert Freeman

Only a week or so left so register for the conference now, and meet the Aces!

Find out more at the NZOUG Conference 2010 web page .

Disclaimer: I'm on the NZOUG Committee
Note: The speaker lineup/abstract list are subject to change.

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com

References

Wednesday, December 02, 2009

Lessons in Loose Coupling - Special Delivery

Photo credit: Kordite@Flickr

I'm generally nervous when I hear people that deal with software throw around the phrase "loosely coupled" with exhuberance. For me, loose coupling is one of the few phrases that resonates a feeling of ambivalence. An abstraction layer is a necessity, especially in proprietory software, to give you the coding hooks you need. Just be careful though to avoid Special Deliveries of Coal. Classic!

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com

References

Related Posts