Gareth Roberts
Oracle EBusiness Suite and Java Webstart
- Java Web Start Available for EBS 12.1 and 12.2
- Using Java Web Start with Oracle E-Business Suite (Doc ID 2188898.1)
Text searching the SQL of BI Publisher Data Model contents in the OBIEE catalog
If you're working in an environment where there are a lot of Business Intelligence Publisher (BIP) reports in an on-site OBIEE instance, sometimes you want to impact assess, or find usages of a certain database table or column. The following shell script is a very simple way to search the Data Model SQL in BI Publisher reports in the filesystem. Note of course this only works if you have an on-site OBIEE instance. For Oracle eBusiness Suite you'd be better to query the CLOBs in stored XML Publisher tables, and for Cloud based Oracle Analytics - you'll need to work that one out!
Change the CAT= like to store the location of the shared folder that you're catalog resides in.
Save the contents to a file called bipdmsearch and remember to chmod 755 bipdmsearch so you can execute it.
#!/bin/sh if [ -z "$1" ]; then echo "Usage: bipdmsearch <search string>" exit 1 fi if [ -z "$CAT" ]; then CAT=/u01/app/obi12c/config12.2.1.2/bi/bidata/service_instances/ssi/metadata/content/catalog/root/shared fi echo "cd $CAT" cd $CAT echo "Search for $1" find . -type f -name \*xdm\* -exec grep -il "$1" {} \; echo "Done"This is a post from Gareth's blog at http://garethroberts.blogspot.com
... and we're back!
Wow - it has been a long time since I posted here! So much so that the world has changed - with an onslaught of content and apps and tools and technologies and Cloud! However that said I've found myself coming back to visit my old posts from time to time as technical references to especially fiddly things are often lost in the mix of content these days. I've always had a list of things to add here, and over the last few years I've been working in-depth with the Oracle Business Intelligence Suite and picked up a bunch of tips and tricks along the way, so the list is even longer now ;-)
Although there are more people documenting more things there are still plenty of techniques that are obscure but warrant being being referenced.
So, we'll start off with a couple of easy ones, maybe something using OBIEE back end catalog updates, then kick into some tougher ones .... maybe Google Earth KML output from Oracle SQL/BI Publisher or something like that :-)
Enjoy ...
This is a post from Gareth's blog at http://garethroberts.blogspot.comHP Mini T210 3F0 boot failure and System Rollback Data disk space issues with Roxio BackOnTrack / aswrvt.sys
If you have an HP Mini 210 or 100 and have had issues with disk running out of space, failures to boot, I feel for you. After a heap of hassle, I sorted my out problems without having to reinstall Windows, and so I'm sharing my experiences as I found many many people with the same issue, but no central resolution. The issues I've had are as follows
- Failure to boot: 3F0 Harddisk does not exist
- Running out of space (see Roxio BackOnTrack)
- Black screen after attempting to uninstall Roxio BackOnTrack
IF you get a failure to startup, screen with 3F0 Harddisk does not exist:
- To access the BIOS, turn on the computer and immediately press the esc key to display the Startup Menu, and then press the F10 key.
- Press F9 to reset the BIOS defaults, and press Enter to confirm the action.
- Press F10 to save the change and exit the BIOS, and then press Enter to confirm.
Hopefully now the computer will restart.
Low disk space - "System Rollback Data" hidden secret directoryThis netbook came preinstalled with Roxio BackOnTrack, which is the primary source of my woes. Recently I found I was running out of disk space and after deleting a heap of data, then finding I was running out again I installed WinDirStat to check what was going on. On inspection I found a super secret hidden folder System Rollback Data, which is not even visible with "show system files" switched on. Note this is not to be confused with Microsoft Windows System Restore. This folder contained 160Gb out of my 250Gb SSD !!
So after reading I found many pointers for Roxio BackOnTrack being the culprit of the System Restore Data directory size. Note that it seems Roxio BackOnTrack and this super secret directory may have a hook into the boot loader. So beware!
Anyway, without further ado here's what I hit and how to fix it.
- Uninstall Roxio BackOnTrack from the Programs & Features or similar. Note this may take a long time if the backup store is large. I didn't wait, and end task'ed the uninstall process. That may have been a bad idea.
- After killing the uninstall and rebooting I got the dreaded 3F0 hard disk does not exist, fix as per above.
- After fixing 3F0 issue, Windows would not boot, I tried safe mode, command prompt, last known good configuration, all that happened was a black screen after the initial Windows splash. On logging I found Windows load stopped at aswrvt.sys, no further progress. After a lot of searching I found a post that mentioned to delete the file delete the syscow32.sys file in WINDOWS/system32/drivers folder. The next issue was trying to get a utility running so I could get access to the C:\ Drive.
- As the HP Mini 210 doesn't have a CD/DVD drive I decided to run a USB Boot program, after trying Windows restore disk, which didn't boot, I decided to use EaseUS Todo Back Emergency Disk, so I did the following:
- Installed EaseUS Todo Backup Free to another computer and ran it.
- Tools > Create Emergency disk, then created USB bootable drive
- Put that in the HP Mini and changed boot options to move USB to top of the list
- When EaseUS started up went to Tools > Windows Command Prompt and ran commands along the lines of the following and found I only had syscow32x.sys, so backed up and deleted that:
c: cd windows\system32\drivers copy syscow32x.sys c:\temp del syscow32x.sys exit
- Closed EaseUS Todo Backup
- Rebooted - filesystem check kicked in, and Windows booted successfully !
- Get rid of Roxio BackOnTrack - run uninstallapp.exe from C:\Program Files\Roxio ... takes a long time (10's of minutes dependeing on data size), watch the disk space free up as it runs :-)
- Remove Roxio BackOnTrack via Control Panel - Programs and Features
After all that, my machine is running sweetly again. Hopefully this post saves someone from throwing a perfectly fine HP Mini 100 / 210 out the window !!
Catch ya!
Gareth
References
- Helpful Pointer to uninstall Roxio BackOnTrack.
- Key identification of problematic syscow32.sys file.
- HP forum with other people's woes.
Enhancement Requests and Bugs for Oracle eBusiness Suite
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 2015/02/22 See ER 9130428 Open AR Need a Standard Way to Export the Aging 7 Buckets By Account Report Output to Excel 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:
- 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.
- 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
Table of Enhancement Request Statuses Status CodeDefinition 11 Code/Hardware Bug (Response/Resolution) 12 To External (User Group) Review 15 Enhancement Req. Internal(Oracle) Review 19 Approved for User Group Voting 20 To Requestor, Need More Info 22 Approved for Future Release 23 Scheduled for Future Release 50 ER Waiting for Base ER Fix 82 QA/PM to Eval ER, Chg Delivered Internal 97 Closed, Enhancement Rejected 98 Closed, Enhancement Implemented
Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com
Related Posts
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
ReferencesRelated Posts
NZOUG 2013 Conference: Agenda online - register while Earlybird Pricing still here!
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
- NZOUG
- NZOUG2013
- NZOUG on Twitter, conference hashtag #NZOUG2013
- NZOUG on LinkedIn
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:
- 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. 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 PIDcol 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 216812. 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.piece3. 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 requestset 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
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
- BI Publisher EBS Bursting 101: A tutorial/case study including Bursting Control File to email Suppliers Remittance Advice in 1 easy step
- Audit Trail Must Do: Bank Accounts
- Email Address validation by Forms Personalization, Profile Options and Regular Expressions (regex) in the Oracle eBusiness Suite
Revisited: Search Engine Plugins for Firefox to get directly to specific Oracle Patches, MOS Notes, Bugs
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.
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com
Related Posts
- Search Engine Plugins for Firefox to get directly to specific Oracle Patches, Metalink Notes, Bugs
- Bye Bye Metalink, Hello MOS - My Oracle Support. Fingers crossed for continued HTML content!
- Firefox with eBusiness Suite: Oracle JInitiator Plugin does not start - always get "must install plugin", Vista, Patch 5397653
- Enhancing the Oracle eBusiness Suite 11i/12 Homepage Menu via Firefox, Greasemonkey, XPath, Javascript & DOM: aka Foxiness Menus
Search Engine Plugins for Firefox to get directly to specific Oracle Patches, Metalink Notes, Bugs
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:
- AskTom Search Engine Plugin Revived
- See How Easily You Can Search Oracle
- Oracle Firefox search plugin
- Oracle Metalink Firefox search plugin
- Firefox with eBusiness Suite: Oracle JInitiator Plugin does not start - always get "must install plugin", Vista, Patch 5397653
- Enhancing the Oracle eBusiness Suite 11i/12 Homepage Menu via Firefox, Greasemonkey, XPath, Javascript & DOM: aka Foxiness Menus
Oracle EBS Release 12.1 Released: Top 8 New ATG Features
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!
Related Posts- OpenWorld 2008 Final Post: Fusion Applications, X, R12 - Change Pack & Payment Manager plus SES
- Openworld Update: XML/BI Publisher & R12, 11g PL/SQL, ADFdi and more
- R12 Patch 5612820 Available: Changing the default layout format between RTF/PDF/Excel when submitting XML / BI Publisher Concurrent Request
- Changing the default layout format from PDF to Excel using Profile Option and FNDRSRUN Form Modification - Submitting BI Publisher Report Request
- Revisited: Why is mod_plsql not supported with the Oracle eBusiness Suite Release 12? Fusion Crossroads #1
Discoverer with EBS R12 - Sheet contains no data for custom Oracle eBusiness Suite Responsibility
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- Using Discoverer 10.1.2 with Oracle E-Business Suite Release 12
- How to Create a Link to a Discoverer Workbook in Apps R12
- Discoverer Plus 10g: contact with Server lost: DiscoApplet Error DiscoNetworkException CORBA.COMM_FAILURE. ICX Territory!
- Discoverer Plus window not appearing or Jinitiator JVM conflicts on the Oracle eBusiness Suite: Java JRE EnableSecureStaticVersioning
- Setting your Oracle Applications session: fnd_global.apps_initialize (org_id)
OpenWorld 2009 done - back to Windy Wellington
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.
Related Posts- Oracle OpenWorld 2009: From Silicon Welly to Silicon Valley Again
- A Look Ahead to 2009 - Oracle eBusiness Suite and Happy New Year!
- OpenWorld 2008 Posts
BI / XML Publisher 5.4pt start-indent problems in XSL-FO blocks on EBS Check Format recursive template
- 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
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 FilterHere 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
References
- Oracle Forums Thread
Concurrent Program parameters in XML / BI Publisher Template - eBusiness Suite
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
HTML Formatting issues in EBS R12 XML Publisher Remittance Advice Emails aka. Use Inline CSS Styles in BIP
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
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
- Oracle Forums Thread
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 ServerWe 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