In R12 the formatting of payments (Checks, ACH, wires) and the output of related documents (positive pay, Separate Remittance Advice) is done by a Java concurrent programs that generate XML output which is then formatted by BI Publisher.

Oracle Payments provides the IBY_FD_EXTRACT_EXT_PUB extensibility package to construct custom XML element structure that can be added to the payment XML extract generated by Oracle Payments. The package specification and body definition can be found in files ibyfdxes.pls and ibyfdxeb.pls respectively. These files are located in the $IBY_TOP/patch/115/sql directory.

The package allows custom elements to be created at five levels within the payment XML extract. You cannot customize the package specification, but the package body contains stubbed functions that you can customize.  The five functions are as follows:

Instruction - FUNCTION Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at instruction level and run only once for the instruction.

Payment - FUNCTION Get_Pmt_Ext_Agg(p_payment_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at payment level and run once for each payment in the instruction.

Document Payable - FUNCTION Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at document payable level and run once for each document payable in the instruction.

Document Payable Line - FUNCTION Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at document payable line level and run once for each document payable line in the instruction.

Payment Process Request - FUNCTION Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at document payable level and run once for each payment process request.

Here are two examples where I utilized this extension:

1) Last Day of Period for Positive Pay file:

The Positive Pay File only provides the tree <PositivePayDataExtract> with <OutboundPayment> for each payment that it captures. In order to add data to the positive pay file XML  you must use the function IBY_FD_EXTRACT_EXT_PUB.Get_Pmt_Ext_Agg.

2) Count of ACH Transmissions per day:

This count was needed once per payment instruction so I utilized IBY_FD_EXTRACT_EXT_PUB.Get_Ins_Ext_Agg to insert the new xml element at the bottom of </OutboundPaymentInstruction> (the root of the XML for payment batches). This funciton also appears in the XML stream for separate remittance advice, checks, etc.

Here is a screen shot of how we coded the Get_Ins_Ext_Agg:

IBY_FD_EXTRACT_EXT_PUB.Get_Ins_Ext_Agg

I recently received a requirement to account for the entire deferred revenue amount of a sale, then recognize the revenue over time, based on consumption or clicks from a customer. Oracle Receivables has two “commitment” transaction classes to support this type of requirement.

The two commitment transaction classes are:

Guarantees: as you associate invoice transactions the amount due “moves” from the guarantee to the invoice associated to it.

Deposits: as you associate invoice transactions the amount due on the invoice is reduced or wiped out and all cash, collections, and aging can be tracked on the single deposit.

In my situation Deposits is the way to go for the following reasons:

One bill to the customer is required; we are billing the entire amount of the “deal” using installment terms (example: 25% of invoice every 30 days). Opposed to guarantees where there is an amount due on each invoice associated to the guarantee (not acceptable in my case).

Also, cash can be applied to the deposit, guarantees do not allow cash applications.

Finally, aging can be “centralized” as the deposit and installment payments are managed and reported under a single transaction. The impression invoice transactions are simply recognizing revenue in my model, in a supported, auditable way.

Accounting Flow for my setup:

1. Create the Deposit with installment terms
Example: $400K, $100K due at 30, 60, 90, 120 days.

Note: Detailed aging would show 4 installments of $100k due every 30 days.

2. Create a cash receipt applied to first installment.
Example: $100K paid for first amount due.

3. Create the impressions Invoice to recognize revenue - Linking it to the Deposit commitment Invoice.
Example: $90K in impressions delivered

Balance goes to $0 once associated to the deposit which has enough remaining dollars to cover entire invoice.

Most organizations leave their mission critical systems vulnerable to security threats and performance degradation. Recent studies show that organizational constraints are at the core of this issue. These constraints range from organizational policies, budgets and staff availability and even competency.

26 percent of organizations apply critical security and performance patches as they are released, 20 percent will eventually apply 6 to 9 months later while another 20 percent have no requirement at all to apply them. That leaves many companies data unprotected.

There are several ways to combat these constraints. One way is gain support from your organization. This can come in the form of support from executives, security / audit team and other critical policy makers. By establishing new and or updating existing security policies, and as a critical component, patching, these guidelines and timeliness of patching removes some of these constraints. A second way to protect and improve data integrity is to use technology. By keeping current on latest software, technology can help you reduce time and budget constraints of getting current applications and databases tested and allow your team to proactively assess the impact to your enterprise.

Centroid believes in 2 degrees of continuous change instead of wide arcs of re-activeness. These small degrees of change allows for continuous improvement across the organization. Whether it be through technology or process slight proactive adjustments make the enterprise perform at a higher degree then you can ever accomplish by large scale reactive measures.

Centroid is in the business of Future Proofing Oracle environments. In fact, Centroid is the leader in Future Proofing your Business and Technology and no one does it better. No one.

Often, in Oracle E-Business Suite Release 12 (R12) during an implementation or after patching, the pre-compiled jsp’s need to be recompiled because you aren’t seeing the change you expect. You can selectively recompile jsp’s using this program, but I like to just blow away everything and start fresh. Support documentation states “AD utilities to perform this action, for example after patches are applied which replaced 1 or more JSP pages” but I frequently have to use this tool manually after patching to see the expected results.

This is my go-to first step after patching or configuration changes that aren’t giving me what I expect.

My process is as follows:

1. Bring down Apache with adapcctl.sh in $INST_TOP/admin/scripts

[appldemo@demoR12 ~]$ $INST_TOP/admin/scripts/adapcctl.sh stop

2. Clear the cache directory by renaming it and recreating it.

[appldemo@demoR12 ~]$ mv $COMMON_TOP/_pages $COMMON_TOP/_pages_07DEC09 [appldemo@demoR12 ~]$ mkdir $COMMON_TOP/_pages

3. Start ojspCompile with the parameters –flush –compile -p [number of parallel processes]

[appldemo@demoR12 ~]$ $FND_TOP/patch/115/bin/ojspCompile.pl –flush –compile -p 8

4. Confirm the process completes successfully

starting…(compiling all) using 10i internal ojsp ver: 10 synchronizing dependency file: loading deplist…7983 enumerating jsps…7983 updating dependency…0 initializing compilation: eliminating children…5894 (-2089) translating and compiling: translating jsps…5894/5894 in 10m29s compiling jsps…5894/5894 in 44m27s Finished!

5. Almost Done! – Just Restart Apache!

[appldemo@demoR12 ~]$ $INST_TOP/admin/scripts/adapcctl.sh start

We have been using jdeveloper 10g to create new OA Framework forms for our R12 Oracle EBS applications 12.0.6 with database version of 10.2.0.4 without issues.

We then upgraded our database to 11.1.0.7.0 and EBS to 12.1.1.  That is when we encountered an issue with prior custom OA forms in EBS.  oracle.apps.fnd.framework.OAException: oracle.jbo.JboException: JBO-29000: Unexpected exception caught: java.lang.IllegalAccessError, msg=oracle/jdbc/driver/OracleCallableStatement.

This issue/error is specifically related to the OracleCallableStatement used in 11g.  With 11g certain features/libraries are not available.  The Oracle Database 10g Release 2 (10.2) will be the last major release to offer the following feature/libraries: classes12.jar, oracle.jdbc.drivers.* and OracleConnectionCacheImpl. In other words these features/libraries will not be available in the next major database release (for example 11g).

Since our java code was using an OracleCallableStatement and also using the oracle.jdbc.drivers on the 11g database we encountered the error above.  This means that public Oracle JDBC driver classes that were referenced by oracle.jdbc.driver package must be referenced by using the oracle.jdbc package . Therefore, Java programs must not import public classes that  have belonged to the oracle.jdbc.driver package - but import  those classes from the  oracle.jdbc package.

Since our code was using “import oracle.jdbc.driver*” we had to replace it with import oracle.jdbc.*

Once we replaced the above statement; the OracleCallableStatement executed as expected.

Recently we upgraded our R12 instance from 12.0.6 to 12.1.1 and encountered several unique errors in Order Management.

The new issues prevent an order line from being created and or order lines from being updates.  The error message presented is: You Are Not Allowed To Create Order Lines.

According to metalink the environment needs patch: 6958955 applied.  Apparently this patch has a sql script ontup251.sql in it that needs to run.  Metalink as of this posting did not have this patch available for download.  Oracle analysts then recommended applying another patch that had the ontup251.sql script in it.

The patch that was recommended to apply was 8310984, which is not ported to 12.1.1 yet.

So how do you fix the issue above?  Simple.  Login into Oracle Applications as Order Management Super User and navigate as follows: Navigated to Setups–> Rules –> Security –> Processing Constraints. Query up Application “Order Management” and Entity “Order Line”.  Once results come back, then query operation only of “Create”. There is a seeded record that has an issue. This record had operation of Create, a User action of “Not Allowed” and the Enabled checkbox was on, and the System checkbox was off. The Condition was “Any” BUT THERE was no validation entity nor any Validation Template. Thus the issue.  Since this was not a system generated constraint and the validation condition was null, we disabled the constraint.  This then allowed us to create an order line.

In addition to the above there is also an “Update” operation that is also null.  However this record is a system generated record.  In our case it was record 87 of 89 “update” operations.  We made a duplicate condition to another update record and saved.  Once this happened we were able to create new lines and update existing lines in OM.

We know this is not the long term solution.  We have evaluated running ontup251.sql script to drop and rebuild the constraint validation packages but at this time we decided against it until Oracle provides correct patch and requests that the script can be run in isolation.

Oracle eBusiness Release 12 introduces the concept of Subledger Accounting (SLA). Subledger Accounting provides an interim step to translate, and derive accounting logic, prior to recording GL entries. SLA is designed to provide an extremely flexible solution to facilitate the accounting needs of a corporation. Whilst SLA continues to be a buzz word, in the Oracle community, it’s regarded with both awe, and disdain.

In the old days, Oracle provided Auto-Accounting, and Account Generator as the options available for deriving accounts. Both were somewhat limited. Any accounting logic outside of a set of predefined rules required customization. Splitting a single accounting entry into multiple accounts required major customizations.

The transition from the individual sub-ledgers to the General Ledger was a single step process. Accounting entries were derived/recorded in the sub ledger, once the entries where completed, they were posted to the General Ledger.

The major concepts within SLA are :

• Rule Based Accounting Engine : With the introduction of SLA, an additional layer has been created. Accounting entries were derived/recorded in the sub ledger, once the entries where completed, they are posted to SLA. During the posting to SLA, the SLA engine derives the accounting entries based on the rules established within SLA. The SLA engine has the ability to derive each segment of the chart of account from any defined location with the Enterprise. The rules can be defined based on pre-populated data, or customized using PL*SQL hooks to the database.
• Multiple Representation : A Single accounting entry can be represented for multiple reporting requirement. Organizations are able to have a primary ledger to represent primary accounting requirements such as GAAP. Accounting representations can be created to represent additional/secondary reporting requirement such as IFRS. Each secondary ledger can have a different subledger accounting method.
• Straight Through accounting processing : Subledger Journal entries can be posted to the GL directly form the transactions screen

SLA whilst on the surface seems to be the holy grail for managing accounting processes, it does hold some drawbacks:

• Visibility of Accounts : Accounting entries generated via the rules engine are only visible in the SLA. The data in the sub ledgers (INV, RA, AP etc.) may not represent the same set of accounts as within SLA
• Debugging : Due to the flexibility of the rules engine, debugging accounting derivations can be a complex and involved process. The rules engine provides both configuration, and SQL based derivation rules. Requiring a combination of functional, and technical knowledge to debug issues.
• Adoption : Industry knowledge of SLA is still very limited, outside of the marketing and sales material. Educational, and professional services knowledge of SLA is sparse, making deployment of SLA a risk for many organizations.

As knowledge of SLA expands, and the continuing changes in accounting practices force more and more corporations to abide by Nations, and International accounting practice, SLA will prove a valuable asset. Oracle is well poised with SLA to meet the future reporting needs.

If you are anything like me you were trained from the beginning that forms development was done on a shared server.  In the cases that you came onto a client where such a mechanism was not enabled you would go through the effort of ftp’ing all of the relevant forms and libraries to your desktop and make your modifications within Oracle Forms Builder installed on your local machine.  You would compile your form to make sure it had no errors, but you would move your form back to the server and generate your fmx there.  Up until recently I never questioned that methodology as it always worked fine.

On a client site part of a consultant’s job is to mentor the legacy staff.  In a lot of cases these individuals have no exposure to Oracle technology.  Recently an employee at a client came to me because he was having several issues working on a form on his desktop.  His issues were as follows:

1.  When opening the form it gave him a message that it was unable to find other forms and another that stated it was unable to find libraries.

This was a simple enough fix.  We had him to go his windows registry (run->regedit) and modify the FORMS_PATH variable to include the location of the forms and libraries.  This was of course after we made sure he downloaded all the forms and libraries in question.

2.  After successfully compiling the form, he attempted to run it and was given an error as follows:  “FRM-10142: The HTTP listener is not running on <local computer name> port 8889.  Please start the listener or check your runtime preferences.”

This again was an easy one.  Forms Builder 10G is web based so the app server instance has to be running to serve up the form.  First we modified the DEFAULT.env file located in the ORACLE_HOME/forms/server folder to make sure that the FORMS_PATH variable matched what we did in the system registry earlier.  Then we went to Oracle Forms Builder from the windows start menu and choose “Start OC4J Instance”.

Once this was done we were able to run the form and the apps server instance served it up, but we promptly received another error:

3.  After app server served up the form, a form trigger error occurred:  “FRM-40375: ON-ERROR trigger raised unhandled exception ORA-06508.”

Now the first thing we checked was ORA-06508 which states the following:

It was only after we opened and compiled all of the attached libraries and forms that this error went away and the form served up correctly.  The error occurs because of an incompatibility between forms and libraries compiled on a Linux box vs. a windows box.  This is why the forms served up just fine on a Linux server implementation of forms but not on the local windows install.  As you can imagine some of the native forms have a large number of forms and libraries attached making it impractical to do this all of the time.

For a custom form that does not leverage many native EBS libraries or forms you would not have a difficult time doing the development on your desktop.  However, for extension to native forms, stick with doing your development on the Linux server.

Recently “The Business Week” June 15, 2009 ran an article titled ‘Taking AIM at outsourcers on US Soil’ If passed, the bill would bar companies with more than 50 employees in the U.S. from landing any additional work visas if more than half their US workforce is made up of H-1B or L-1 visa holders.  It definately appears there will be some controversy up on the hill.  If acted upon it would put a stop to the addition of H-1B resources coming to the US.  Senators Dick Durbin (D-Ill.) and Chuck Grassley (R-Iowa) are the two authoring the bill.  It would be good to hear your opinion on the subject…..I have mine!

After back and forth volleys with Oracle Support in getting Configurator to work correctly in R12 with SSL and load balancers (as well as great support from our DBA Team) I thought it would be good to share what is missing from some documentation within metalink (at least at time of this post) and hope it saves you time in the setups.

1) Review Section 4.4.3.1 AltBatchValidateURL, Oracle® Configurator Implementation Guide, Release 11i, Part No. B13604-03 and also Oracle® Configurator Implementation Guide Release 12 Part No. B28682-02 page 4-15.  Note that the R12 documentation should be updated as the URL needs to be slightly different for R12 as in step 2 below.  This is what caused our issue!

2) Check the value of the setting in cz_db_settings for AltBatchValidateURL. Use this statement to check it:
SELECT * from cz_db_settings where upper(setting_id) = ‘ALTBATCHVALIDATEURL’   If no rows are returned then run the following sql insert statement:
INSERT INTO cz_db_settings (setting_id, section_name, data_type, value, desc_text) VALUES (’AltBatchValidateURL’,’ORAAPPS_INTEGRATE’,4,’http://hostname:portnum/OA_HTML/configurator/UiServlet’,’Non-secure URL’)

If a row is returned then validate that the value is correct based on the above url.  If not then you can update the cz_db_settings table as follows: update cz_db_settings
set value = ‘http://hostname:portnum/OA_HMTL/configurator/UiServlet’ where setting_id = ‘AltBatchValidateURL’

3) Validate the the profile option for “BOM:Configurator URL of UI Manager” is set to: http://hostname:portnum/OA_HTML/configurator/UiServlet

Hopefully the R12 documentation is updated in the near future to guide others in the process.