Feed aggregator

New Oracle SOA Support Blog

Ramkumar Menon - Fri, 2012-03-09 00:02
Check out the new Oracle SOA Suite Proactive Support Team blog at https://blogs.oracle.com/soaproactive/

Oracle Book Giveaway

Robert Vollman - Sat, 2012-03-03 17:33
I've got seven Oracle books available for giveaway. Any unclaimed books will get recycled (assuming the library doesn't want them).If you'd like one or more, just send an email to my gmail account (my account id is just my last name, Vollman). Since I don't want to be out of pocket, I'll ask that you arrange to pay for the shipping, please.In the unlikely event that there is high demand for oneRobert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com9

Oracle Fusion Applications 101: BI & Reporting

Andries Hanekom - Wed, 2012-02-29 12:53
Oracle Fusion Applications provides an extensive range of BI Reporting tools and capabilities all tightly integrated with the FA user experience (UX).

The Business Intelligence and Reporting infrastructure is delivered by Oracle Business Intelligence Enterprise Edition, OBIEE provides analysis tools, interactive dashboards, alerts and an enterprise reporting engine that lies at the heart of the BI and Reporting capability for FA. A number of BI products are integrated with OBIEE to deliver the FA BI and Reporting expierience:

Oracle Business Intelligence Publisher (BIP) - The latest incarnation of the old XML Publisher, a pixel perfect reporting tool, providing a complete enterprise solution for authoring, managing, and delivering reports from multiple data sources (SQL, ADF-BC View Objects, XML) in multiple formats (PDF, Excel, HTML) through multiple channels (E-Mail, Print, File).

Oracle Business Intelligence Applications (OBIA) - A prebuild data warehouse for FA transactional data, provides the warehouse database schema and the logic that extracts data from the Oracle Fusion Applications transactional database and loads it to the warehouse; OBIEE reporting tools like Answers and Dashboards are used to query data.

Oracle Transactional Business Intelligence (OTBI) - Allot of confusion and hype around OTBI, will dedicate my next post to it, follow me on twitter @FusionApps101.

ESSBASE"Extended Spread Sheet dataBASE", selectively used to enhance the FA reporting capability, used by Fusion Financials in the Fusion Accounting Hub application to delivery real-time analytic reporting on General Ledger Balances.

Like most things with Fusion Applications, the BI Reporting strategy and capabilities are extensive and initially somewhat bewildering. It takes time to wrap your head around Oracle's master plan for OLTP BI and Reporting, but WOW what a brave new world!

Oracle JHeadstart 11.1.1.4 Now Available

JHeadstart - Wed, 2012-02-29 12:03

Oracle JHeadstart 11.1.1.4 is now available for download (build 11.1.1.4.26).
See the support matrix for supported JDeveloper releases.
Customers who own a JHeadstart supplement option license can download it from the Consulting Supplement Option portal.

This release is mainly a bug fix release. In addition, some small small enhancements have been added: 

  • Enhanced Support for Dynamic Tabs: The implementation of dynamic tabs has been changed and enhanced. There is no longer a dependency on the standard oracle uishell dynamic tabs implementation. A new oracle.jheadstart.view.dyntab.DyntabContext class is now used instead of the oracle.ui.pattern.dynamicShell.TabContext class that supports additional features like updating the document title based on the current tab, setting the maximum number of tabs, and displaying initial tabs.
  • Enhanced Support for Function Keys: New function keys have been added for first row, previous row, next row, and last row, and go to details. In addition when changing a page fragment or tab within a page fragment, the cursor focus will be set on the first item.
  • Control over Generated Value Bindings: At the service level, two new properties have been added. The property Always Generate Form Value Bindings controls whether individual attributeValues bindings are generated for a group that does not have form layout. The property Generate Value Bindings for Hidden Items controls whether a value binding is generated whe the item is hidden. To ensure upwards compatibility the default value of both properties is true, however, if you create a new service definition, both properies are set to false. If one or both if these service-level properties are set to false, you can still force generation of an individual value binding using the two new item properties Generate Form Value Binding and Generate Table Value Binding. Both these new service properties, and new item properties are only visible in advanced mode in the Application Definition Editor.
  • Enhanced Implementation of Dependent Items: In previous releases, dependent items were refreshed using the partialTriggers property. This caused the dependent items to be validated as well as part of the optimized lifecycle, forcing usage of showRequired property rather than the required property to prevent premature client-side validations. In this release, the dependent items are refreshed programtically by adding them as partial target inside the DependsOnItem bean. This means all items can have client-side validations as desired.
  • Enhanced Implementation of Display New Row on Entry: The check whether a new row must be displayed is now performed inside the task flow itself, instead of in the calling task flow. This allows for a proper implementation of the "when no rows found" option in this property.
  • Enhanced Implementation of JHeadstart LOV's: The JHeadstart web-tier LOV implementation has been completely rewritten. The new implementation uses one generic LOV popup per page, with one associated generic LOV task flow binding and one genwric LOV Task flow manager bean. LOV for validation has been implemented using a generic dynamic task flow call (in the task flow template). This new implementation is easier, more robust, and generates significantly less managed beans. The pages will also load faster since every page has only one LOV popup included, instead of separate popups for each and every lov item (which were even repeated for each row in a table)
  • Enhanced Implementation of Custom Properties: Custom properties can now also be accessed using the name of the property (previously labeled description). There are three expression you can now use now, for example for group custom properties you can use:
    • ${JHS.current.group.property1}, this is the existing expression that also works when no name is entered for the property.
    • ${JHS.current.group.getCustomProperty("propertyName")}, where propertyName should be substituted with the name you entered for this property.
    • ${JHS.current.group.propertyName}, where propertyName should be substituted with the name you entered for this property. If you use this expression and the group does not have this custom property defined, you will get an error during generation.
We recommend to use the new second or third expression, these expressions dot not care which custom property number you used to enter the name and value. In addition, if you use the second or third expression, you never run out of custom properties, because you can also enter a comma-delimited list of names and values in the name and value property.

For a complete list of all existing features, use this link. For detailed information on all the features, check out the comprehensive JHeadstart Developer's guide. Want to try JHeadstart yourself? Do the tutorial using the JHeadstart evaluation version.

Categories: Development

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

Gareth Roberts - Wed, 2012-02-29 04:26

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

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

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

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

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

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

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

Related Posts

What do we mean by Graphical, Visual SQL?

Sue Harper - Tue, 2012-02-28 11:44
I have written a few entries about Microgen DBClarity Developer, the new graphical SQL tool we've just launched and would love to hear what you think. Have you taken a look yet? Have you read some of the material and said, "I already have a SQL tool" and gone on to read another blog? What does drag-and-drop SQL code conjure up in your head?

Just today I was looking at this code:

I used another SQL tool too - I used Oracle SQL Developer to run my scripts and to populate and verify the data in the tables - why change old habits when they work well?! The fun thing is that I can use DBClarity Developer to produce that SQL by creating this diagram, using easy drag-and-drop...


When last did you write a more complex piece of SQL and scratch your head just a little to start with? How often do you execute the same piece of code? The graphical aspect of DBClarity Developer means that you don't need to worry about the code, the product does that. You build up the graphical flow and we create the code.  The good thing is that you don't need to decide on the database at the outset; you can use this same graphical representation to produce the SQL for Oracle, SQL Server or Teradata.  These graphical SQL rules are part of your project, so you can reuse them.

Visit the site today.  DBClarity Developer is production, why not download and try it out? 

NetApp Crash-Consistent Snapshots

Mike Rothouse - Mon, 2012-02-27 20:13
We performed a disaster recovery (DR) test at our DR site in October 2011 which involved recovering Oracle database VMs with storage on a  SAN.  Once the database VM was recovered from a NetApp volume snapshot, it was started and we discovered Oracle went through its usual crash recovery and the database was running as […]

Major Memory en CPU improvement on OFM

Marc Kelderman - Mon, 2012-02-27 13:34
The last few weeks we had some strange behaviour on our Oracle Fusion Middleware stack. This was first related to two different SOA 11g production clusters. One 2-node cluster and one 4-node cluster. Both environments run into a Java out-of-memory.

While he have running this production environments for a long time, we thought we have tuned this very well; on database as on application server. As I spoke earlier on Java Garbage collection on http://orasoa.blogspot.com/2011/09/soa-11g-improved-memory-and-garbage.html, and on tuning the SOA Suite 11g; http://orasoa.blogspot.com/2011/08/tuning-soa-11g-in-nutshell.html.

We first thought, it had to do the the applications we made; Composites, Custom Java Classes, Services. Is something changed in the meantime? We also thought it had to do with the amount of data we are processing, is there more data processed than normal? Something changed in the database? The network? The physical storage? All of them were not changed, looking in the program code; we did not find anything special.

We tried to analyse the a heap dump, digged into log files, Oracle Support Bug Database and many-many-other-things. I stared a long time to this

#
# A fatal error has been detected by the Java Runtime Environment:
#
# java.lang.OutOfMemoryError: requested 32756 bytes for ChunkPool::allocate. Out of swap space?
#

The I was triggered by a bug in the Oracle/Sun Java; http://bugs.sun.com/view_bug.do?bug_id=6973402 . For some reason I downloaded the latest Java 1.6 SDK and applied this on one managed server on one the SOA 11g Suite.

The result was amazing!
  • Less CPU
  • Less Heap usage
  • Normal garbage collections
We applied this on our SOA 11g Suite clusters and had no issues.

We see this difference also on de Oracle Service Bus (!).  My advise, roll out Java 1.6.0 build 30 (or later) on your existing production environments, SOA Suite, WebCenter, ServiceBus, etc!


Here are the results of our instance running without Java 1.6.0_b30 and at the bottom with Java 1.6.0_b30. And see the incredible difference. I did not show the CPU graphs with the garbage collections, but those are also different.


Java Heap Before applying Java 1.6.0 Build 30


Java Heap After applying Java 1.6.0 Build 30







Oracle Identity Management (OID, OVD, OIF) 11gR1 Patchset 5 (11.1.1.6) Released.

Mark Wilcox - Thu, 2012-02-23 15:51
I'm sure you've seen the flood of announcements from the other Fusion Middleware products about the 11.1.1.6 release. We got in on the fun too.You can download it here. And for a fresh install - you can start directly from 11.1.1.6. For the most part this is just a bug fix release for us.But there are a couple of enhancements I would like to share.Oracle Virtual DirectoryThe biggest enhancement I would highlight is that we have dramatically simplified configuring OVD for Enterprise User Security (EUS). EUS has been something that has always worked but required to execute lots of individual steps. We now have this setup as a wizard and OVD's own Local Store Adapter holds most of the meta-data. So less work on the enterprise LDAP and fewer steps. It should mean initial EUS configuration by most people can now be done in less than a day. Directory Integration PlatformDIP has been part of Oracle for over a decade but until 11.1.1.6 it required OID. Now it can be used with DSEE or OUD as its metadata store.This now means that if you want to deploy DSEE or OUD but need to synchronize groups & users from AD - you can do it without needing any type of custom code or bringing in a full provisioning product.

Oracle Identity Management (OID, OVD, OIF) 11gR1 Patchset 5 (11.1.1.6) Released.

Mark Wilcox - Thu, 2012-02-23 15:51
I'm sure you've seen the flood of announcements from the other Fusion Middleware products about the 11.1.1.6 release. We got in on the fun too. You can download it here. And for a fresh install - you can start directly from 11.1.1.6. For the most part this is just a bug fix release for us. But there are a couple of enhancements I would like to share. Oracle Virtual Directory The biggest enhancement I would highlight is that we have dramatically simplified configuring OVD for Enterprise User Security (EUS). EUS has been something that has always worked but required to execute lots of individual steps. We now have this setup as a wizard and OVD's own Local Store Adapter holds most of the meta-data. So less work on the enterprise LDAP and fewer steps. It should mean initial EUS configuration by most people can now be done in less than a day. Directory Integration Platform DIP has been part of Oracle for over a decade but until 11.1.1.6 it required OID. Now it can be used with DSEE or OUD as its metadata store. This now means that if you want to deploy DSEE or OUD but need to synchronize groups & users from AD - you can do it without needing any type of custom code or bringing in a full provisioning product.

Dynamic Images in PDF - What 32k Limit?

Marc Sewtz - Thu, 2012-02-23 14:36
Looks like it's time to follow up on a Blog posting I wrote in 2008 about including dynamic images in PDF reports. After being called out on that good old 32k limit in a recent Blog posting by Roel Hartman, and reading through the recent OTN Forum posts and tweets on that topic, it would be rude not to respond ;-)

While I did state that we were looking to lift this 32k limit, this has not yet made it in, i.e. the limit is still in place. However in my posting I was also saying that if the XML data is generated by some other way, and the PDF rendering is done using the print API, then the use of larger images would be possible. And that is certainly the case, so let's take a look at how this could be done.

The key piece is our Print API (apex_util.download_print_document and apex_util.get_print_document), with this API you can generate PDF and other documents through a simple PL/SQL API call. This API is taking care of all the communication with BI Publisher or FOP for you. The apex_util.get_print_document API can be called to generate and retrieve the print document as a BLOB in the database for further processing, like storing the document in tables, etc. The apex_util.download_print_document API can be called in an APEX page process to generate and download the print document straight to your client. Both APIs have three different signatures, they allow for programmatically downloading report queries while dynamically associating stored report layouts at runtime, downloading report queries with custom templates stored in your own tables, and generating PDF based on your own custom XML using your own custom templates.

This last scenario is what we want to use for our dynamic images sample, i.e. we're going to generate the report data in XML format ourselves, thus getting around that 32k limit and store the report data in a CLOB. We're then also going to store our report layouts in our own tables, and query them up dynamically at runtime. The API for this looks as follows (for further details, reference the Oracle Application Express API Reference):

-- -----------------------------------------------------------------------------------------------
procedure download_print_document (
--
-- This procedure initiates the download of a print document using XML based report data and RTF or XSL-FO based report layout.
--
-- Arguments:
-- p_file_name Defines the filename of the print document
-- p_content_disposition: Specifies whether to download the print document or display inline ("attachment", "inline")
-- p_report_data: XML based report data
-- p_report_layout: Report layout in XSL-FO or RTF format
-- p_report_layout_type: Defines the report layout type, that is "xsl-fo" or "rtf"
-- p_document_format: Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml"
-- p_print_server: URL of of the print server. If not specified, the print server will be derived from preferences
-- example: http://myserver.mydomain.com:8888/xmlpserver/convert
--
p_file_name in varchar,
p_content_disposition in varchar default 'attachment',
p_report_data in clob,
p_report_layout in clob,
p_report_layout_type in varchar2 default 'xsl-fo',
p_document_format in varchar2 default 'pdf',
p_print_server in varchar2 default null
);


Now of course the question is, how do we get our data into XML format, if we don't have APEX take care of that for us, and where do we get the images from and how to we include them in this API call? Getting our data into XML format is the easy part, let's say you want to generate XML data for this query:

select * from emp

You could simply call dbms_xmlgen.getxml, supply the query and retrieve the XML back as a CLOB:

select dbms_xmlgen.getxml('select * from emp') xml_data from dual

Now assuming you have your images stored in a BLOB column, you would need to convert the images into base64 encoded data in order to include them in your XML. I have a blob2clobase64 function included in my sample application, which basically does just that. It should be noted though, that ultimately our API call is going to reach out to BI Publisher via utl_http, meaning you're going to do send your XML data via http to another service, which requires certain characters to be encoded, you can find more information on this here:

http://en.wikipedia.org/wiki/Base64

APEX typically takes care of this for you, in our scenario though, you generate the XML yourself, so you need to encode the base64 data on your own. I have taken care of this in blob2clobase64 function that ships with my sample app.

create or replace function blob2clobase64 (
p_blob in blob,
p_escape in char default 'N'
) return clob is
l_pos pls_integer := 1;
l_buffer varchar2 (32767);
l_res clob;
l_lob_len integer := dbms_lob.getlength (p_blob);
begin
dbms_lob.createtemporary (l_res, true);
dbms_lob.open (l_res, dbms_lob.lob_readwrite);
loop

l_buffer := utl_raw.cast_to_varchar2 (
utl_encode.base64_encode (
dbms_lob.substr (p_blob, 48, l_pos)
)
);
if (p_escape = 'Y') then
l_buffer := replace(l_buffer,'+','%2B');
l_buffer := replace(l_buffer,'/','%2F');
l_buffer := replace(l_buffer,'=','%3D');
end if;

if length (l_buffer) > 0 then
dbms_lob.writeappend (l_res, length (l_buffer), l_buffer);
end if;
l_pos := l_pos + 48;

exit when l_pos > l_lob_len;

end loop;
return l_res;

end blob2clobase64;

So in my example, using a table called eba_pdfimg_images, that includes the data and images I want to print, the XML generation would look like this:
   -- generate XML data
for c2 in (
select dbms_xmlgen.getxml('
select
id,
file_name,
mime_type,
description,
blob2clobase64(image,''Y'') image
from eba_pdfimg_images
') xml_data from dual
) loop
l_xml_data := c2.xml_data;
end loop;

Once I query up my report layout, converted that to a CLOB, and generated my XML data with images as outlined above, I can simply call our API:
    apex_util.download_print_document (
p_file_name => 'image_demo',
p_content_disposition => 'ATTACHMENT',
p_report_data => l_xml_data ,
p_report_layout => l_print_layout,
p_report_layout_type => 'rtf',
p_document_format => :P1_FORMAT
);
This will generate a file called image_demo, and based on whether you choose to generate Word or PDF, you'll get ‘.rtf' or ‘.pdf' file back.

So that's it. You can generate your RTF templates with the BI Publisher Word Plug-In as you normally would. And you can take your report query SQL and wrap it into a dbms_xmlgen.getxml call, and then you'll be able to include much larger images in your PDF dynamically. Want to give it a try? Here's my update sample app (logon as demo/demo123):

Dynamic Images in PDF Reports

I uploaded three images, and two report layouts. If you're trying out your own images or templates, I ask that you please remove when done, and report back if you encounter any issues. Also, if you want to try this out locally, you can download the app (sample_pdf_with_images.sql) along with the RTF layouts and images here:

Download Sample App

Please note, this app requires BI Publisher to be configured as your print server, and the current release of APEX 4.1.1 (my sample is an APEX 4.1.1 export, but you should be able to use the same technique on APEX 4.0 and above).

This blog has moved!

Chris Muir - Wed, 2012-02-22 19:48
No really, it has! You can find my new Oracle blog here.

For the record this older blog will stay to allow readers to continue to benefit from the existing posts.

I hope to see you on the new blog!

Oracle Fusion Middleware PS5 is out

Marc Kelderman - Wed, 2012-02-22 14:00
A short article, but Oracle Fusion Middleware patchset #5 is out! Now downloadble via:

Oracle Fusion Applications 101: Enterprise Scheduling Service

Andries Hanekom - Sat, 2012-02-18 22:58
I suspect these posts will continually try to relate functionality in Fusion Applications (FA) with my background in Oracle e-Business Suite (EBS), so apologies if your from the JD Edwards, PeopleSoft or Siebel crowd.

SAP? Well no apologies or sympathy, we all have our problems.

The Enterprise Scheduling Service (ESS) provides the EBS Concurrent Manager equivalent service in FA but with an extended list of features and functionality. The Enterprise Scheduler is a J2EE application that provides schedule based callbacks to other applications to run their jobs, it does not execute the jobs itself.

Here is some interesting points to note:
  • Completely Metadata driven: the metadata consists of job definitions, including the executable class, parameters and schedules. All Metadata is created using Oracle JDeveloper, limited functionality via FA ESS Job Definition UI to register a Job.
  • Job Definition or Job = Concurrent Program in EBS.
  • Job Set = Request Set in EBS, sequential or parallel set of Job Steps, where a Job Step can be a single Job or another Job Set.
  • Different job types, including JAVA, SQL, PL/SQL, SQL*LOADER, C, HOST and BIPublisher.
  • Includes features like Incompatibility, Schedules and Workshifts.
  • Monitoring of jobs through ESS Monitor UI and/or Oracle Fusion Applications Control (Enterprise Manager)
Want to know more, see the Oracle® Fusion Applications Developer's Guide for Oracle Enterprise Scheduler for more detail.

#OracleFusionApplicationLesson 2 for Oracle EBS Developers: Your going to be defining concurrent programs through JDeveloper.


Looking for Technical Writer

Elena's Blog - Wed, 2012-02-15 00:23
We are looking for a talented technical writer, experienced in Linux and Virtualization. It is important that the candidate knows Docbook XML in depth. Basics requirements:
  • 3 or more years previous experience in writing software documentation (please provide URLs of your writings)
  • Experience with writing documentation for system level software and operating systems
  • Strong knowledge of the Linux operating system
  • Strong knowledge of XML, DocBook XML, and XSL style sheets (and motivation to help maintain and expand our tools and infrastructure)
  • Ability to administer own workstation and test environment
  • Good experience with distributed working environments and versioning systems such as SVN
The location where we are looking to hire is mainly Ireland, possibly considering also Scandinavian countries. Position Description

Microgen DBClarity Developer is now Production

Sue Harper - Mon, 2012-02-13 16:42
I'm pleased to announce that Microgen DBClarity Developer is now production and available for download from the Microgen site, where you can also find an overview flyer and presentations, tutorials, short feature videos, and more.

For other news, read the following press release: Microgen releases DBClarity Developer.

You can also watch the full set of  videos on YouTube.


Have fun!  Let us know how it goes.  All contact details are shown on the website.

Working on a few more demos: EDFs and the SQL Editor

Sue Harper - Mon, 2012-02-13 06:52
I tried adding a new entry to my blog a few times since my last entry. My plan is to highlight a few key areas in DBClarity Developer so that you can learn a little more about what the product does. I have a list of around 10 topics, which morphs and expands as my ideas change. Having shown you how to “Get Started” in my first video, the next step is to write about the SQL Editor, as described in the presentation, Tutorial 1 - Introducing the SQL Editor , which is on the DBClarity website. There’s the rub..., for those of you who know me and my back ground, I can almost hear you say, “SQL Developer does this and more” or even “TOAD has a richer editor than this”, and so what am I up to? Well, that’s the point, this is a different product.


I have created a new video, Microgen DBClarity Developer: SQL Editor, which instead of focusing on the SQL Editor, shows you where it fits in, and then goes on to show you how you can start creating the visual queries, what we call SQL Rules. Take a look.

In the next video I’ll take a look at how to deploy your project.

Welcome to 2012! Is it Spring in the Air?

Sue Harper - Mon, 2012-02-13 06:51
I'm not sure we've had a winter here, well in the south eastern reaches of the UK at least.  It's true that we're walking the dogs in the dark, which means it's winter, but my hands don't stick to the icy metal railings, instead we're walking in the dark to the tune of the dawn chorus!  Yesterday a severe storm wrenched trees from their roots and blew things about the garden, but we're still in double digit degrees (that's Celcius) and so definitely too mild to really be called winter; or am I finally getting used to the weather in the UK?

Still we're back and at 'em now I hope the break was good, restful and fun.  I'm back in London working with the team preparing for the launch of new software.  To that end I have done a mini series of videos on how to do a number of activities in Microgen DBClarity Developer. Instead of listing each video, I have created a YouTube playlist - the videos include "Creating Database Connection", "Adding files to Projects" and "Deploying Projects". 

Have fun!

New SQL Developer 3.0 Webinar

Sue Harper - Mon, 2012-02-13 06:51
I see that Lewis Cunningham is doing a free webinar on SQL Developer 3.0 for ODTUG in January.  Here is his post on the news http://it.toolbox.com/blogs/oracle-guide/sql-developer-30-new-features-webinar-43499 There is a link to register and a bit on how he uses SQL Developer in his day-to-day tasks.

Here http://www.odtug.com/apex/f?p=500:235:0::::P235_NEWS_ID:3061 is a list of free ODTUG webinars posted for 2011.

First Webinar on DBClarity Developer - Learn More!

Sue Harper - Mon, 2012-02-13 06:51
Conference season may be over and you may all be into the swing of the end of year activities, but things are still happening here. DBClarity Developer Beta 1 has been out for just under 2 months and we're about to release a second beta.  Before we do that, we thought we'd show you more about the product and give you the opportunity of seeing what it does and to ask questions.  Register for the live Microgen DBClarity Developer Webinar to be run on Thursday 15th December, and because this is a webinar, it's "at a city near you"; just join in, watch, listen, learn, and ask questions.

DBClarity Developer is a standalone product that provides a graphical interface for creating SQL queries, procedures and functions. The beauty of the graphical interface is that you can create the code graphically and let the product build the SQL, once done, you can revisit the diagram.  It's great for maintenance because you don't need to trawl through lines of code, in an attempt to figure out what it's designed to do, and it's great for collaborative work, because the visual aspect helps when you're explaining how the code works. DBClarity Developer generates SQL code for Oracle, Teradata and SQL Server and works alongside your other development tools.

Why not join the webinar, take a look and see what it's all about? Register here:  Microgen DBClarity Developer Webinar.

Pages

Subscribe to Oracle FAQ aggregator