In Depth Apps - Oracle eBusiness Suite - Gareth Roberts

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

2011-10-03T11:03:00.010+13:00

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.)

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    pm.ext_pmt_party_id = payee.ext_payee_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    pm.vendor_site_id = ss.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

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

2011-08-11T10:16:00.007+12:00

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:

What requests are currently running?
I have an operating system process that is taking too much CPU - what is it doing?
Can you tell me where the request is at? I've clicked on the log but it doesn't show anything!
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:

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

So without further ado, let's take a look at the following sweet query:

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
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    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 */
;

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
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 = inst.inst_number (+)
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

Enhancement Requests and Bugs for Oracle eBusiness Suite

2010-09-21T05:42:00.010+12:00

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
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:

Login to My Oracle Support
Search on the ER / Bug number in the Search box e.g. 4262148
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:

Go to the Settings tab
Click on Hot-Topics Email under Personal on the left side menu list.
Switch On the Hot Topics email, every 1 days (or suitable), and ensure you have checked "Product Bugs Marked as Favorites"
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

Revisited: Changing the default layout format between RTF/PDF/Excel for BI / XML Publisher 11i Concurrent Requests: Patches 5612820, 7627832 here now!

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

2010-08-12T22:31:00.006+12:00

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

Oracle Forums Thread

Environment Variables from database table - Oracle E-Business Suite

2010-04-06T11:12:00.013+12:00

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

Oracle Forums Thread

Update - Added example output and $APPLTMP output.

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

2010-03-22T16:08:00.002+13:00

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

Clearing Email Notifications and manually setting the Workflow/Java Notification Mailer Override/Test Address via SQL for the Oracle eBusiness Suite

NZOUG Conference 2010 Agenda online now!

2010-03-04T07:04:00.000+13:00

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

NZOUG

Lessons in Loose Coupling - Special Delivery

2009-12-02T10:08:00.006+13:00

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

Special Delivery WTF

Production Upgrade complete... hack obsolete!

2009-11-23T12:43:00.009+13:00

It had to happen. I've moved away from very retro hardware requirements and a couple of hacks to something much simpler, and more applicable to "modern" computers ie. those with USB ;-)

No. 8 wire solution no longer needed ... luckily the wires aren't that thick :-)

Old:

New:

Bonus points to Readers that guess the application of this stuff!

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

References

No. 8 Wire

My Oracle Support - Metalink to MOS survey results

2009-11-19T16:27:00.002+13:00

Ouch! Metalink to My Oracle Support Survey Report.

Revisited: Search Engine Plugins for Firefox to get directly to specific Oracle Patches, MOS Notes, Bugs

2009-11-09T14:29:00.007+13:00

Revisited: Following the upgrade from Metalink to My Oracle Support (MOS) I've updated the Note and Bug search engines (files oranote.xml and orabug.xml) per my prior post.

Revisited again 30-NOV-2010: Following the ARU change I've updated the Patch search engine (file orapatch.xml) per my prior post.

Navigate directly to a specific Oracle Patch, MOS/Metalink Note or Bug, speeding things up & sidestep that Flash! You gotta know the Patch/Note/Bug number you wanna get to:

OraPatch: Javascript Install or grab the XML file.
OraNote: Javascript Install or grab the XML file.
OraBug: Javascript Install or grab the XML file.

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

Bye Bye Metalink, Hello MOS - My Oracle Support. Fingers crossed for continued HTML content!

2009-11-04T14:59:00.009+13:00

Disclaimer: This page may become out of date very quickly!

Only a couple of days of Metalink access left, with the change over to full My Oracle Support due on Friday - 6 November 09.

For me this is a somewhat sad occasion. Metalink has been around for such a long time, and has been a great companion, it will be a shame to see it go.

We now herald in the era of MOS (My Oracle Support). And of course, with any shiny new thing, there have been discussions and more discussions. With that debate there has been some good feedback, some negative. To be honest I'm a bit nervous about this change. I'd be keen to know why the APEX interface of Metalink is on the out, when APEX was just brought in for the latest Oracle Store, and with some very sexy functionality on the horizon. The answer is sure to be a double edged sword ;-)

At the end of the day MOS as I've seen so far just doesn't tick all the boxes for me. That will hopefully change. Hopefully soon. My biggest gripe of course would be Flash versus HTML. Given Oracle's current catchphrase "Open. Complete. Integrated." I'd have thought Flash would be a little further down the list than HTML for many of the MOS components. One issue related to this can be summed up by the following screenshot. The eagle-eyed amongst you will spot the problem in the following picture:

… with the issue being the Firefox "Find" not finding "Font" when it was present many times on the MOS search results presented. A bit of a hassle that something I use regularly ain't gonna work. Guess I'll need to have two sessions up, one Flash, one HTML.

Fortunately, it seems an HTML interface will still be available according to Note 841061.1, with limited functionality including SR Management? BUT WAIT ... while I was writing this post I got another MOS related announcement... No SR Management??? Hmm, this is specific to "On Demand" functionality. Fingers crossed for SR Management through the HTML only interface!

The HTML option will not support the following On Demand functionality:

Service Request management

Change Request Management

Viewing performance reports

And there are some other little things that will probably come out in the wash, like email notifications no longer linking directly to an SR:

Prior:

New:

Oh, and of course, let's hope the powers that be manage to keep the gremlins away...

Exception Gremlin:

I/O Gremlin:

Error 1088 Gremlin:

Internal Gremlin:

Well ... I guess we'll find out where we stand in a couple of days!

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

References

APEX Statement of Direction
Support Blog

HTML Formatting issues in EBS R12 XML Publisher Remittance Advice Emails aka. Use Inline CSS Styles in BIP

2009-11-02T23:09:00.002+13:00

Apologies for the cryptic title on this one. The issue is a simple but subtle one ... and if you're not an eBusiness Suite customer, but interested in the BIP HTML formatting part, please read on as the discussion may be relevant.

In the Oracle eBusiness Suite Release 12 there is an out-of-the-box solution for sending Payables Remitttance Advice notices via Email. The program is "Send Separate Remittance Advices" and is integrated into the Payments Process. The standard solution utilizes XML Publisher under the covers, but (at the time of writing) has been coded to force HTML output for the Email content and uses its own delivery mechanism, rather than a more flexible bursting one that could attach PDFs to emails. Now, this means there are a couple of limitations with the output format for these Remittance Advice notices:

The out-of-the-box HTML generated by XML / BI Publisher uses CSS Stylesheets as opposed to Inline CSS. This causes formatting issues with some specific Email Clients (such as GMail) as described by this "old" but still relevent post. This is the main focus of this article and I'll give details of a workaround.
The use of HTML means from a document printing perspective a pixel-perfect format is tough to achieve.
Images e.g. Company Logos, need to be referenced in the HTML and hence need to be publicly accessible on a web server. This also means that when Supplier's receive the Remittance Advice emails will probably not have the images displayed by default and they'll get the "some pictures have been blocked to help prevent the sender from identifying your computer ... click here to download images" or similar spam/tracking warnings.

So the out-of-the-box solution has these gotcha's until such time as it uses a "fixed-format", "all content embedded in email" delivery method such as attaching a PDF to the email with the advice details...

BUT WAIT, there may be workarounds.

For Issue 1. we can tell XML / BI Publisher to embed Inline CSS rather than CSS Stylesheets using the following undocumented XML Publisher configuration. Place the following configuration in the xdo.cfg file and put it in eBusiness Suite $XDO_TOP/resource directory. Usual caveats apply; please test this before rolling to Production. Also be aware that this may affect all HTML output, with output file sizes likely to increase.

<config version="1.0.0"  xmlns="http://xmlns.oracle.com/oxp/config/">
   <properties>
    <!-- html-css-embedding valid values  embed-to-element | embed-to-header | externalize -->
       <property  name="html-css-embedding">embed-to-element</property>
   </properties>
</config>

For Issue 2. one trick is to place your formatting inside a Table and fix the width / height to that which you require. This may take a smidgen of tweaking, but at least you can get something that looks and prints nicely.

For Issue 3 ... well, I'm still working that one - no workaround from Support yet to embed the images in the HTML. Will keep you posted. UPDATE: Enhancement request (ER) Bug 9834226 has been raised for the issue of inability to embed images in Remittance Advice.

Hope this helps.

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

References

HTML emails vs Gmail, Yahoo & Hotmail
Oracle Forums Thread HTML Format via Email

Concurrent Program parameters in XML / BI Publisher Template - eBusiness Suite

2009-11-02T21:34:00.001+13:00

Just a short post with a note of how to get Oracle eBusiness Suite Concurrent Program Parameters into the output on an XML Publisher RTF Template.

There are two simple options, given an example parameter with token P_ORG_ID:

1. Add the concurrent program parameter as a field in the XML - this may be easier / necessary when you get to bursting or similar. Refer to the parameter using the usual <?P_ORG_ID?> RTF syntax.

2. Add the parameter using the param@begin syntax at the top of your RTF file:

<?param@begin:P_ORG_ID?>

And then refer the the value using dollar notation:

<?$P_ORG_ID?>

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

BI / XML Publisher 5.4pt start-indent problems in XSL-FO blocks on EBS Check Format recursive template

2009-11-02T21:17:00.002+13:00

So you've just finished the first cut of your funky BI Publisher Check (Cheque) format template, and you feel pretty accomplished:

Logos, tables, non-preprinted stationary: Yes
Magnetic ink on printer loaded and ready to go: Yes
MICR font: Yes
Conditional signatures: Yes
Void check handling: Yes
Fixed Row Enumeration filler in tables via recursive templates: Yes

And then it all goes pear shaped … Doh!
Your filler recursive template does a dance to the right, indenting 5.4pt at a time.

With the cause in the XSL-FO template something like this: start-ident="5.4pt"

Well, you aren't the first person, or second to hit this, thanks theFurryOne for prompting this post. There is a workaround ... or two. You could edit the XSL-FO and replace the 5.4pt text with 0.0pt, but then you'd need to work with XSL-FO templates rather than RTF.

Alternatively you could put the following magic in the first cell of the filler recursive template:

<?attribute@incontext:end-indent;'0.0pt'?>
<?attribute@incontext:start-indent;'0.0pt'?>
<?attribute@incontext:padding-start;'0.0pt'?>

And a bit of a re-jig on the recurvsive sub template alignment. For example:

And we're rockin and rolling - no XSL-FO template needed, "pure" RTF:

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

References

Oracle Forums Thread

NZOUG Conference 2010 Call for Papers - 15-16 March

2009-10-21T11:53:00.007+13:00

If you're interesting in presenting please take a moment to read this message from the New Zealand Oracle Users Group, and note the deadline for submissions is at the end of next week 31-Oct-09! For full information please see the NZOUG Website. Disclaimer: I'm on the NZOUG Committee.

The New Zealand Oracle Users Group is pleased to announce…

Call for Papers and Training for the 2010 Conference

15^th and 16^th March 2010

We only require your presentation or training topic and short abstract at this stage.

We invite all users and suppliers of Oracle technology, Oracle applications and related products and services to submit presentations or training sessions including:

Stories from Oracle Users – everyone wants to hear them! Tell us your story, case study or any useful tips
Oracle Partners playing a key role in the Oracle world
Third party suppliers of products and services that will assist Oracle technology and application users

Presentation topics may include but are not limited to Oracle Applications such as EBS, JDE and Peoplesoft, BI Applications, Oracle Development, Middleware and Database technologies. Everything is welcome for submission but delegates are especially interested in hearing case studies of real life scenarios and situations, lessons learnt and best practices.

Both Technical and Business streams will run throughout the conference programme with presentations covering the implementation, use and support of the whole range of Oracle and associated third party products and services.

If your paper is selected for presentation:

You’ll be admitted to the conference free of charge upon submission of your full written paper, and
Your paper will be published in the conference proceedings.

SUBMITTING AN ABSTRACT

How do I submit an abstract? Visit our abstract submission website and complete the Call for Papers submission, by 31 October 2009. We encourage you to submit your topic or interest by this time as last year we had a great selection from the first call.

You are welcome to suggest a topic you’d like to hear at the conference and we’ll try to source a presenter, also any questions, suggestions or comments are welcome at anytime to papers@nzoug.org

Remember to visit our website for all up to date information on regional events, the conference, and other news.

We look forward to receiving your submissions by 31 October 2009

HOW DO I REGISTER FOR THE CONFERENCE?

Simply click here to register for the conference at our online registration page.

WHAT DISCOUNTS CAN WE GET?

There will be an early bird discount for all registrations before the 1st January 2010. After that standard rates apply so get registering now.

Amazing 2010 Offer!!!!

Register 3 Full Registration Attendees and get an additional Full Registration, for another member of your team, absolutely free. So if you have more than 3 people who would like to attend this great exciting educational event then it is even cheaper than ever before.

Conditions Apply.

PRICES

	Early Bird	Standard Rate
Full Registration: NZOUG Member	$695.00	$795.00
Full Registration: Non Member This fee includes the cost of NZOUG Individual Membership	$895.00	$995.00
Day Registration (includes Monday dinner)	$495.00	$495.00
Additional Exhibitor/Sponsor Staff Registration	$350.00	$350.00

VENUE AND DATES

15^th and 16^th March 2010

Rotorua - Energy Events Centre – Same city, new location!

NEED MORE INFORMATION?

Contact us via our website or for any other questions please contact our conference management company, The Conference Company.

By phone on 09 360 1240 or by email at nzoug@tcc.co.nz

OpenWorld 2009 done - back to Windy Wellington

2009-10-21T08:52:00.014+13:00

With OpenWorld 2009 all done plus a few highly enjoyable days in the Valley I'm now back at home. I had a fantastic time once again, second time around was much less daunting - I went for less sessions, more unconference and more shopping!

The first couple of days of OpenWorld this year seemed very light on attendees, but with a flurry of activity in the last few days and a fantastic appreciation event - an awesome performance from Aerosmith - things really picked up. Very few product announcements of essence from the Oracle team so the real highlights for me were catching up with a variety of people/groups at a number of events:

Australia/New Zealand Dinner
Rittman Mead BI Meetup
Bloggers Meetup
Oracle APEX Meetup
Catching up with the BI Publisher crew and meeting Ike
Hanging out with Michael, Greg and Mike - cheers guys.

A huge thanks to all the above for the hospitality.

So only a few notes this year that took my interest to note them down. It seems a bit light especially on the eBusiness Suite side so if I have missed anything major, please post a comment.

Larry's Keynote Announcements:

Linux update - survey (was it by HP?) reported 65% of Oracle Database customers run Oracle Enterprise Linux beneath the database. Interestingly high percentage.
Review of Exadata "v2" (Sun Oracle Database Machine) with statistics showing results twice as fast as "v1".
Product Support System in the works including on site data mining for analyzing system configuration and auto-advising bugs/patches needing to be addressed.
Fusion Applications related:
- Commitment for support of, and new functionality in eBusiness Suite, Peoplesoft, JD Edwards and Siebel applications for the next decade in addition to Fusion Applications
- "Version 1" of Fusion Applications in Testing.
- Fusion Applications are "SaaS ready" and deployed on SOA, BI standards based middleware.

Just the one product release announcement related to my areas of work:

Business Intelligence Applications OBIA 7.9.6.1 Release available

A number of interesting things up and coming, but most set for calendar year 2010.

OBI 11g - yet to be released, but demo'd, with my notes:

Common UI Components across the BI and Fusion product suites
Online BI Publisher Template Builder
A variety of excellent new features addons such as Answers Groups, Selection Steps, Visualizations
Extensions to current fuctionality such as Action Framework
Additional features such as Scorecards

Oracle APEX 4.0

The whole APEX community is getting ready and excited about the APEX 4.0 release due 2010.
It's great to see the new APEX Listener pre-production release out and getting plenty of feedback.

E-Business Suite:

12.1.1 to include a new hompage / navigator + Web 2.0 style components therein
12.1.1+ Integrated SOA Gateway (as an alternative to the EBS Adaptor which is part of SOA Suite).
Enterprise Manager pack addons for Health checks including online Patch and technology component Version comparison and recommendation tools.

Some other interesting tidbits that I noted:

Trial Usage of Advanced Compression on Oracle internal E-Business Suite top 100 objects reduced space usage on average by 3 times. Gotta see how Vision instances go with compression.
BI Publisher Data Template builder in the works (still), along with the demo'd BI Publisher Online Layout Template editor (demo'd as noted above).

Wrap-Up

So another excellent OpenWorld over - thanks to Oracle for putting on another flawless event and appreciate the Bloggers Pass. Even the weather made me feel at home ;-)

Next major event on my calendar is the NZOUG Conference 2010, Rotorua, New Zealand, March 15-16 2010 - looking forward to it. Hope to see some of you there!

PS. On a side note, from a New Zealand perspective, interesting that an IBM data center should fail around OpenWorld given Larry's consistent reiteration about the redundancies in the Oracle Database Machine and lack thereof in IBM's ex TPC-C beating solution! Even more than a week later seems like things are not quite back to the usual high Air New Zealand standards - following pic snapped 20-Oct-09.

Oracle OpenWorld 2009: From Silicon Welly to Silicon Valley Again

2009-10-07T21:44:00.006+13:00

Off to Openworld 2009 in a couple of days. Given that it's my second time I know a lot more of what to expect. But of course my schedule is overflowing again - even more of a mixed bag this time with fewer conference sessions, but more meetups and unconference sessions that I should have looked at more closely last year!

Here's what my main schedule looks like:

Hope to see you there - And a huge thanks to Oracle for the blogger's pass - wouldn't be there without it!

Catch ya!
Gareth

Discoverer with EBS R12 - Sheet contains no data for custom Oracle eBusiness Suite Responsibility

2009-09-11T13:20:00.011+12:00

So you're working with Discoverer 10g integrated with the Oracle eBusiness Suite on Release 12. You've installed and set everything up per Metalink/MOS Note 373634.1 "Using Discoverer 10.1.2 with Oracle E-Business Suite Release 12" plus created a custom application and responsibility to have it's own menu items corresponding to your Discoverer Workbooks/Worksheets.

You login to your new responsibility and click on your new menu entry that you created per Metalink/MOS Note "How to Create a Link to a Discoverer Workbook in Apps R12" and what do you get when you query subledger data such as Payables Invoices, or secured General Ledger data?

This sheet currently contains no data.

Well, its a quick fix. Simply save the following value in the "Initialization SQL Statement - Custom" profile option at Responsibility level for your new Responsibility.

begin gl_security_pkg.init; mo_global.init('M'); end;

Note: this may depend on your setup of the following profile options:

GL Ledger Name
GL: Data Access Set
MO: Operating Unit
MO: Security Profile

All sorted!

References

Oracle EBS Release 12.1 Released: Top 8 New ATG Features

2009-05-05T14:20:00.009+12:00

Oracle has announced the availability of Release 12.1, plenty of buzz around on this and Beehive updates.

Update: Oracle Application Management / Change Management Pack 3.0 also released! See Patch 8333939

Let's take a look at the Top Eight R12.1 new ATG (Applications Technology) features from my perspective.

Out of the box Techstack Upgrades. New R12.1.1 installations are Database 11.1.0.7, Java 6, Application Server (Java/OAF)/JDeveloper Runtime 10.1.3.4, Application Server (Forms/Reports) 10.1.2.3.
OAF: Record History. R12.1.1 OAF allows viewing Row/Blame or Row/Who information similar to that available in Forms interface (who remembers Release 9 character mode key sequences ;-)
OAF: BI Publisher Integration. R12.1.1 provides a personalizable "Print" button for OAF pages. Very Nice.
OAF: Flexfield References support. R12.1.1 gives the same functionality as Forms UI for referencing other fields as part of flexfields.
Attachments APIs. Bulk upload and web services/API for Document Attachments. Sounds great!
Integration / SOA. A bunch of new functionality including Web Services Invocation Framework (WSIF), a step towards easier integration.
Diagnostics upgrades. A variety of changes/functionality to Oracle Diagnostics including BI Publisher enablement for new diagnostics. Oracle Diagnostics is an excellent source of EBS information for techie types!
Autoconfig: Share Applications Tier between environments. R12.1 introduces the ability to share applications executables between instances, like Production and Test. Probably not what you'd want for your typical PROD/DEV/TEST setup, but for a "Production" / "Production Support" mirror setup this is very tempting.

Plenty of other candidates, but those are the ones that took my fancy from the ATG bag of tricks!

So how am I doing against my Chinese New Year predictions?

Release 12.1: AVAILABLE
Patch 5612820 for EBS Release 11i: AVAILABLE
Native Excel Templates for XML / BI Publisher: Still waiting for them!
Further emergence of OBIA with EBS: Release 12.1.1 has specific mention of further integration with Business Intelligence Applications (OBIA), needs a closer look.
Change Management Pack for the eBusiness Suite: AVAILABLE: Patch 8333939.
New Oracle Application Express (APEX) components to allow APEX/EBS Integration/support: Still waiting, but I'm currently looking at alternatives for APEX/Apps integration for R12.x.
Oracle Fusion Applications: As expected delayed until at least 2010.

Disclaimer: The words, ideas and opinions here are my own. Please don't assume they represent the opinion of any other person or organization. This information is based on various sources, so it may not match the actual functionality delivered!

NZOUG Wellington Regional Meet this week 1-May-09: Public Appearance

2009-04-29T23:23:00.004+12:00

NZOUG Regional Events this week and next week!

Yes, I'll be out of the cave for a change on Friday!

If you'd like to discuss anything from my recent bag of tricks:
OBIA/OBIEE with Oracle eBusiness Suite and all things Business Intelligence related, EBS and APEX Integration, advanced BI Publisher techniques, complex EBS auditing and AuditTrail, scanning documents to EBS, EBS and custom JSPs, Discoverer, OAF, Oracle VPD, CPU/Security Updates, EBS R12 on a Laptop, EBS with Firefox/Greasemonkey, hacking adpatch or even MySQL, Drupal, SugarCRM:
come down to the NZOUG Event at the the Oracle Wellington on Friday 3.30pm - make sure you drop through an email to register.

NZOUG Regional Events are happening:

Friday 1st May: Wellington, Hamilton, Auckland,
Tuesday 5th May: Christchurch,
To be confirmed: Dunedin.

Check out the NZOUG website for details.

Photo: Flickr: David

Search Engine Plugins for Firefox to get directly to specific Oracle Patches, Metalink Notes, Bugs

2009-03-04T23:13:00.014+13:00

Revisited again 30-NOV-2010: Following the ARU change I've updated the Patch search engine (file orapatch.xml)

Update: The Note and Bug search engines (files oranote.xml and orabug.xml) have been updated post upgrade to My Oracle Support (MOS).

Navigating directly to a specific Oracle Patch, Metalink Note or Bug is a bit of a chore. Not to mention Metalink / My Oracle Support (MOS) could do with a mobile interface to speed things up & sidestep that Flash! Cut'n'pasting from my text file with the URL templates was getting tedious. So with inspiration from Eddie Awad's posts, I've put together three custom Firefox Search Engines, well, not really Search Engines, but "I'm Feeling Lucky" engines. You gotta know the Patch/Note/Bug number you wanna get to:

OraPatch: Javascript Install or grab the XML file.
OraNote: Javascript Install or grab the XML file.
OraBug: Javascript Install or grab the XML file.

Once you've installed them, hit Control-K, choose the Patch, Note or Bug "search engine" (Control-Down Arrow), enter or paste the exact Patch, Note or Bug number, hit enter and voila, you're there ... if you're logged into the target site!

Give it a try: e.g. Patch 5612820, Note 444524.1, Bug 6074498.

If you get the XML files, put them in your C:\Program Files\Mozilla Firefox\searchplugins folder (or similar), restart your browser and you'll be up and running!

If you need a generic Metalink search engine in the same vain look here.

PS. You will need your Metalink (MOS) username/password to get to the target pages.

PPS. Hoping Oracle doesn't change the URL structures 2 minutes after I post this ;-) Let me know if I don't notice when that happens!

References

Firefox Add-ons Search Engines
Base64 Online for converting search engine icon images (16x16pixel png) to base64 and back.

Eddie's posts:

Revisited: Changing the default layout format between RTF/PDF/Excel for BI / XML Publisher 11i Concurrent Requests: Patches 5612820, 7627832 here now!

2009-02-10T20:54:00.000+13:00

Some good news for eBusiness Suite 11i "bippers" to kick off the year of the Ox. In a previous post I provided a temporary solution for the issue where the default value of the Output Format for a BI Publisher based concurrent request was hardcoded to PDF. Many people have requirements to default the output type to Excel (xls).

Happily, my custom solution is now obsolete.

I'm pleased to advise that on 9-Jan-2009 Oracle provided Patch 5612820 for Oracle eBusiness Suite Release 11i. This patch has a co-requisite, Patch 7627832 released 11-Dec-2008 that took me a while to track down! Now both major current install levels of R11i and R12 are catered for:

R11i and R12 Patch 5612820 DEFAULT OUTPUT FORMAT ATTRIBUTE AT TEMPLATE LEVEL IS REQUIRED
R11i Patch 7627832 1OFF:7017250:ATG RUP6:11.5.10.2:5612820 : BI PUBLISHER LAYOUT DEFAULTS TO 'RTF'

For R11i prerequisites, you'll need to be on ATG RUP 6 Patch 5903765 see Note:444524.1, which I previously wrote about here. Both patch 7627832 and ATG RUP 6 have a number of other prerequisites and postrequisites so you may have some patching to do to get your 11i environment up to the required patch level.

For R12, Patch 5612820 is included in Release Update Pack 12.0.6, otherwise you'll need R12.AD.A.delta.4 and R12.ATG_PF.A.delta.4 before it can be applied.

Here are some screenshots of the new Default Output Type field on the Templates page.

Release 11i:

Release 12:

And the new Profile Option FND: Default Template Output Type, which takes effect if there is no default output type on the XMLP Template setup.

Release 11i:

And verification that the default output format is indeed working...

Release 11i:

Release 12:

Nice, get patching!

A Look Ahead to 2009 - Oracle eBusiness Suite and Happy New Year!

2009-01-24T21:59:00.005+13:00

Gong Xi Fa Cai
Happy Chinese New Year - for January the 26th!

2008 has been and gone, and we're well into 2009. Let's look at some of potential up and coming tidbits for the Oracle eBusiness Suite.

1. Release 12.1: I was expecting this late last year, but we saw 12.0.6 instead. Release 12.1 promises to deliver a number of things, the main one for me will be a whole swag of XML / BI Publisher layouts for standard reports. A couple of Metalink oops My Oracle Support Notes indicate R12.1 is in controlled release. Haven't had a chance to track down the patch number .. anyone have it? For documentation on R12.1 see the Release Content Documentation.

2. Patch 5612820 for EBS Release 11i: This minor piece of functionality to default the Layout Format for BI Publisher based concurrent requests has been nagging me. Its out for R12, and actually its already out for R11i (9-Jan-2009) however ... the Default Layout on the XMLP side is there but the critical concurrent processing portion to default the layout on a concurrent request was missing so its back with Development. I'll keep you posted.

3. Native Excel Templates for XML / BI Publisher: This one may be subtle but for me its a biggie. Release 12 FSGs with native Excel Templates I believe are in controlled release. RTF templates have their moments, but I know a number people are looking for Excel templates. Excel and Accounting live together, and its high time they were standard for XML Publisher with eBusiness Suite. Here's hoping for more than just FSG native Excel templates.

4. Further emergence of OBIA with EBS: I've spent quite a bit of time with the Business Intelligence products lately, and the Oracle Business Intelligence Applications (OBIA) stack is a formidible beast. Albeit complex, there is plenty of sense and underlying power. I think only a handful of people have tapped into this and I'm keen to see how it plays out this year.

5. Change Management Pack for the eBusiness Suite: I'll be watching this closely too - the important parts from my perspective will be automated patching, and the ability to cut your own custom patches for applying using adpatch - nice, but of course I'm assuming you'll need front up with a few $$ too. Watch this space.

6. New Oracle Application Express (APEX) listener: Apparently due in APEX v4, the new listener will hopefully once again push APEX squarely back into the realm of the EBS after mod_plsql's support was tragically cast aside, only to resurface after clarification from Oracle :-) Any update on this David?

7. Oracle Fusion Applications: I wasn't sure whether to put this in, but I think its worth a mention. Perhaps shouldn't include it here with the emphasis on 2009 as my gut feel is that we'll be waiting a tad longer than that. However, if you've heard anything let us know!

8. What's happening for me in 2009? Well, fingers crossed I'll get stuck into a couple of projects that should have seen the light in 2008!

Do you have any hopes/requests/tidbits for Oracle eBusiness Suite action in 2009? Post a comment.

What happened in 2008?

In my neck of the woods a whole lot went on in 2008 including Website launches, Virtuate contract wins, Product demos, a GreaseMonkey Script release, attending OpenWorld for the first time, joining the NZOUG committee and helping to organize the NZOUG Conference, a new phone (Nokia E71 - nice), a new laptop (Toshiba A300 Y01 running Vista 64 ouch) and a ton more. Despite the gloomy economic outlook I'm hugely looking forward to 2009, the year of the Ox.

Disclaimer: The words, ideas and opinions here are my own. Please don't assume they represent the opinion of any other person or organization.

Email Address validation by Forms Personalization, Profile Options and Regular Expressions (regex) in the Oracle eBusiness Suite

2008-12-18T23:02:00.003+13:00

In prior posts I've dealt with Forms Personalizations, and played with email e.g. via BI (XML) Publisher Bursting. In this post we'll come up with a simple Forms Personalization to ensure that data entry of email addresses results in well-formed email addresses. We'll use regular expressions: an underutilized feature in Oracle since 10g. Initially we'll look at the Remittance Email address on Supplier Sites. But the implementation will allow easy re-use for other email address fields in the EBS by storing the regular expression in a Profile Option.

The Regular Expression

Lets take a look at the regular expression I'll use for email address validation. This regular expression is a consolidation from a variety of sources, considers IPv4 and IPv6 addressing, and includes specific formatting to get around an Oracle Regex bug. Note it isn't the "full official" regex for email address validation - I wanted a one-liner! What does the regular expression below mean? Basically allow a bunch of characters before the @ and a bunch of characters after the @ considering IPv4 or IPv6 addressing. If anyone has any suggestions/issues/changes, please feel free to comment!
Update 27-JUL-2010: Changed regex to allow multiple hypens as it was only accepting one hyphen in hostname.

^[-a-zA-Z0-9_\.\+\^!#\$%&*+\/\=\?\`\|\{\}~\']+@((([0-9a-zA-Z]*[-\w]*[0-9a-zA-Z])*\.)+[a-zA-Z]{2,9})|(\[([0-9]{1,3}(\.[0-9]{1,3}){3})|([0-9a-fA-F]{1,4}(\:[0-9a-fA-F]{1,4}){7})\])$

Profile Option

We'll store the regular express as a profile option. This allows a single source of truth for our email address validation logic. We could equally put it in a PL/SQL package, but then updates would require coding ... and no-one wants to code these days ;-)

Navigate to Application Developer, Profile
    Name: XXV8_REGEX_EMAIL
    Application: Virtuate (or your chosen modifications application)
    User Profile Option Name: Virtuate Regular Expression: Email Address
    Set to Site level visible/updatable only
    Save
Navigate to System Administrator, Profile, System
    Query "Virtuate Regular Expression: Email Address"
    Set site level value to the regular expression above
    Save

Forms Personalization

Okay, moving onto the good stuff. Now we'll setup the Forms Personalization to validate the Remittance Email address on the Supplier Sites, Payment tab.

Navigate to Payables Manager, Suppliers, Entry
    Query up an existing supplier and navigate to Sites
    Click Help, Diagnostics, Custom Code, Personalization from the menu.
    Enter the APPS password
Enter the Forms Personalization Header and Condition
    Seq: 10
    Description: Remittance Email Validation
    Trigger Event: WHEN-VALIDATE-RECORD
    Trigger Object: SITE
    Condition: nvl(:SITE.REMITTANCE_EMAIL,'X') != nvl(regexp_substr(:SITE.REMITTANCE_EMAIL,fnd_profile.value('XXV8_REGEX_EMAIL')),'X')
    Save
Enter the Forms Personalization Action
    Seq: 10
    Type: Message
    Description: Remittance Email Invalid
    Message Type: Error
    Message Text: Remittance Email is invalid. Please enter a well-formed email address. Ensure there are no spaces and check that periods are in the correct position. For example: firstname.lastname@gmail.com

Test it out!

Enter junk in the Remittance Email address on the Payment tab and save.

Code Reuse

To implement the same email address validation on other forms, run through the Forms Personalization steps above, identifying the new block and field, replacing SITE.REMITTANCE_EMAIL as required, and update the Error Message action message description / text with the field name.

If you identify a problem with the regular expression, you have one place to change it and it flows through to all the places you implemented the forms personalization the next time your Users log in!

Requirements/Restrictions

Regular Expression functionality (regexp_substr) requires that you are on Oracle Database 10g or higher.
The solution presented doesn't test email account validity via SMTP query for address verification.

References

Regular-Expressions.info - How to Find or Validate an Email Address
Regular Expressions in Oracle Database 10g on OTN
Using Regular Expressions With Oracle Database 10g Documentation
Thanks to email address checks in Drupal, Wordpress and various other Oracle specific sources.

In Depth Apps - Oracle eBusiness Suite - Gareth Roberts

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

Related Posts

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

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

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

3. Find currently spooling temp file from request

Currently held locks per concurrent request

Related Posts

Enhancement Requests and Bugs for Oracle eBusiness Suite

Create a Favorite:

Switch on Hot Topics Emails:

Related Posts

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

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

References

Related Posts

Environment Variables from database table - Oracle E-Business Suite

References

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

Workflow Notification Mailer Outbound SMTP Server

Workflow Notification Mailer Override Address or Test Address

Related Posts

NZOUG Conference 2010 Agenda online now!

References

Lessons in Loose Coupling - Special Delivery

References

Related Posts

Production Upgrade complete... hack obsolete!

References

Related Posts

My Oracle Support - Metalink to MOS survey results

Revisited: Search Engine Plugins for Firefox to get directly to specific Oracle Patches, MOS Notes, Bugs

Related Posts

Bye Bye Metalink, Hello MOS - My Oracle Support. Fingers crossed for continued HTML content!

References

Related Posts

HTML Formatting issues in EBS R12 XML Publisher Remittance Advice Emails aka. Use Inline CSS Styles in BIP

References

Related Posts

Concurrent Program parameters in XML / BI Publisher Template - eBusiness Suite

Related Posts

BI / XML Publisher 5.4pt start-indent problems in XSL-FO blocks on EBS Check Format recursive template

References

Related Posts

NZOUG Conference 2010 Call for Papers - 15-16 March

OpenWorld 2009 done - back to Windy Wellington

Related Posts

Oracle OpenWorld 2009: From Silicon Welly to Silicon Valley Again

Related Posts

Discoverer with EBS R12 - Sheet contains no data for custom Oracle eBusiness Suite Responsibility

References

Related Posts

Oracle EBS Release 12.1 Released: Top 8 New ATG Features

Related Posts

NZOUG Wellington Regional Meet this week 1-May-09: Public Appearance

Search Engine Plugins for Firefox to get directly to specific Oracle Patches, Metalink Notes, Bugs

References

Related Posts

Revisited: Changing the default layout format between RTF/PDF/Excel for BI / XML Publisher 11i Concurrent Requests: Patches 5612820, 7627832 here now!

Related Posts

A Look Ahead to 2009 - Oracle eBusiness Suite and Happy New Year!

What happened in 2008?

Related Posts

Email Address validation by Forms Personalization, Profile Options and Regular Expressions (regex) in the Oracle eBusiness Suite

The Regular Expression

Profile Option

Forms Personalization

Test it out!

Code Reuse

Requirements/Restrictions

References

Related Posts