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

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.

Deferred COGS is a new feature introduced in Release 12. The basic fundamental behind the enhancement is that the COGS is now directly matched to the Revenue.

Prior to this enhancement, the value of goods shipped from inventory were expensed to COGS upon ship confirm, despite the fact that revenue may not yet have been earned on that shipment. With this enhancement, the value of goods shipped from inventory will be put in a Deferred COGS account. As percentages of Revenue are recognized, a matching percentage of the value of goods shipped from inventory will be moved from the Deferred COGS account to the COGS account, thus synchronizing the recognition of revenue and COGS in accordance with the recommendations of generally accepted accounting principles.

Whist this helps solve some key accounting issues, There are some key issues one needs to be aware of:

• Currently Deferred COGS accounting cannot be turned off in release 12.

• The activity of recording COGS recognition is now a multi-step process

• Run AR Revenue Recognition, and Submit Accounting Processes

• Run a set of concurrent processes in Cost Manager to record Sales Order and revenue recognition transactions and to create and cost COGS recognition transactions. These COGS recognition transactions adjust deferred and earned COGS in an amount that synchronizes the % of earned COGS to earned revenue on Sales Order shipment lines.

• Record Order Management Transactions: records new sales order transaction activity such as shipments and RMA returns in Oracle Order Management.

• Collect Revenue Recognition Information: determines the percentage of recognized or earned revenue related to invoiced sales order shipment lines in Oracle Receivables.

• Generate COGS Recognition Events: creates and costs COGS recognition events for new sales order shipments/returns and changes in revenue recognition and credits for invoiced sales order shipment lines.

The end result of these activities is a series of COGS Recognition Material Distributions. However these distributions will not be visible on the Material Transaction screen, unless the ‘Include Logical Transaction’ checkbox is checked.

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!

Recently on a R12 project the business had a requirement to enable the attachment functionality on one of the WIP forms.  I would like to share 4 steps that can be done to enable attachments to work on just about any Oracle EBS form.

First, verify that you have the “Application Developer” responsibility assigned to your userid.  If not, then log in with System Administrator responsibility and grant yourself the “Application Developer” responsibility or have someone with System Administrator rights grant you that responsibility.

Steps to enable Attachments in WIP “View Move Transactions” Form (WIPTQSFM).

1. Determine which form, block and base table the attachments will be for.  Log into Oracle R12 with WIP responsibility and navigate to “View Move Transactions” form.  Navigation: Work in Process –> Move Transactions –> View Move Transactions.  Query up a record.  Using the “Examine” functionality under the “Help” Menu and “Diagnostics” sub-menu find the block where you want to have attachments enabled.  In this case W_MOVE_TXNS_V is the block for form WIPTQSFM.  In addition, determine what the base table is for this form.  Once you have noted what form, block and base table to use it’s time to switch to “Application Developer” responsibility as the following steps assume you have switched responsibilities.

2. Create Document Entity -  Navigation: Attachments –> Document Entities.  Query up the base table you determined in step one.  If there is no record then add a record for the base table.  In this case we have inserted a new record for “WIP_MOVE_TRANSACTIONS” base table.  The entity ID we used “WIP_MOVE” and for the entity name we used “Move Transactions” and the prompt also “Move Transactions”.  The application should be self explanatory, but just in case use “Work in Process”.

3. Create Document Category (Document categories provide security by restricting the documents that can be viewed or added via a specific form or form function. When a user defines a document, the user assigns a category to the document. The attachments form can only query documents that are assigned to a category with which the form or form function is associated).

Navigation: Attachments — > Document Categories.  Query up the category starting with “WIP%”.  If no category exists then create a category.  In this example I have used “WIP Move” as the category name and assigned to the Work in Process application.  Save the record

4. Create Attachment Function - Navigation: Attachments –> Attachment Functions.  Query up functions starting with “WIP%” if the function you are looking for does not exist you will need to create new record.  In this example I have created a new function called “WIP_WIPTQSFM” designating the form from step 1 above.  The User Name field we called “View Move Transactions” and verified the “enabled” checkbox was checked.  Next click on the Categories button and assign the category from the previous step to this function. (We used “WIP Move”.  Next click on the Blocks button and enter the block name from step 1 above (”W_MOVE_TXNS_V”) and set the method to be “Allow Change”.  Next click on the entities button.  Use the LOV and select “Move Transactions” as the Entity defined in step 2.  The display method can be set to “Main Entity”.  You can leave the “Privileges” and SQL statement tabs default.  However, you will want to change the primary key fields to the following: Key 1: PARAMETER.ORG_ID and Key 2: W_MOVE_TXNS_V.TRANSACTION_ID.  Note: You must have at least one key defined.  Enter the names of the fields in the calling form from which the primary keys for the entity can be derived. Use the syntax block.field. You must include the block name (for example, W_MOVE_TXNS_V.TRANSACTION_ID). Use the unique column on the base table if you must.

Once you have completed these steps you can test out the “enabled” attachment functionality by switching to a Work in Process responsibility and navigate to “View Move Transactions”, query up a transaction and the attachment icon should now be active.

You can also find out more on attachments by searching metalink.

In the current economy you’re probably looking under every rock to see where you can cut costs and software support and maintenance fees may be something for you to consider. These fees can be significant. Take your Oracle support fees for example, roughly 22% of original license fees paid annually.

If it’s been a while since you last reviewed your support agreements, start there. You may be paying support fees for applications you no longer use. Those should be a no-brainer, get them on the chopping block ASAP. Others may not be so straightforward. A common example are those modules you purchased licenses for, but shelved for whatever reason. They were cut from the scope of the original implementation or maybe they were purchased with thoughts of using down the road. Now you are paying support fees for something you’re not using and may or may not use in the future. What should you do now?

If you’re considering terminating the support on these modules, there are a few things to keep in mind. Oracle requires that you drop the software licenses when you stop paying for annual support, which means, when/if you decide to implement those modules down the road, you’ll need to purchase new licenses. Oracle will also hit you up with reinstatement fees including retroactive support and maintenance fees and additional penalties. So keep this in mind when kicking around the options.

So I guess the answer to my original question is….it depends. It depends on many factors that you’ll need to consider before making any changes to your support agreements. Before making any changes, review your current agreements and read over the latest cancellation policies and do what makes sense for you.