Usual wisdom till R11i is that install base gets updated or created as customer product when we ship the product. This shipping must go through the inventory transaction (remember that we can ship no inventory items as well) in order for the IB to be updated or created. But to achieve the same for the order lines that do not go through shipping and inventory (like Bill Only lines with fulfillment), we used to extend Order line workflow (supported way) to plug in a function that sends a message to update or create instance. This used to be upon fulfillment.

Stating R12 this is a seeded functionality. The same function that is given to customize the workflow is added to the order line fulfillment code. Meaning all bill only lines (for that matter any line workflow that has fulfill node and item is IB trackable) automatically create or update the install base upon fulfillment. But again this is controlled by a profile option (OM: Automatically Interface Lines to IB on Fulfillment). You can control this behavior by setting this profile option value as Yes or No.

May be we can measure earthquake in scales and numbers, but disaster that ensues is something that cannot be measured in any scale. It is time to come together and help.

Please help the victims of Haiti earthquake. Here is the most reliable way, that is just announced by the President, which ensures your contributions reach actual victims. Please visit and contribute.

http://clintonbushhaitifund.org/

We all know SLA is rule driven to derive accounts for create accounting process. If you are not using standard (seeded) accounting rules, but have created a new one with a new application accounting method for a specific application, how create accounting program uses these rules to derive correct accounting?

We talked about SLA earlier here. If you follow the earlier article, we are talking about using application accounting method (custom) to derive an account for a specific journal line type (debit or credit). To achieve custom accounting you assign this accounting derivation rule to this journal line type. These journal line types roll into event classes (invoices or deposits) and entities (AR transactions) to form Application Specific Accounting Method. This accounting method is assigned to an application of interest where this desired accounting is expected. This accounting method has to be validated (Validation Program) for this to be used.

This validation process actually creates a database package where all these rules are coded and maintained. To identify the package you can use this simple script which gives you the package name. Parameters to this package have to be sourced from the table XLA_PRODUCT_RULES_B. This tables stores the application accounting definitions that we talked about earlier. For example you want to get the custom application accounting method you have created for Receivables, you can simply use this SQL to get that.

Pass the values from the above SQL to this simple function get that package. If you want to see standard rules, change product rule type code from C to S. If you have more than one product rule code (application accounting method), you should know which one you are looking for. If you open this database package your technical eyes can see what is happening in create accounting program.

select application_id,
product_rule_code,
product_rule_type_code,product_rule_hash_id from xla_product_rules_b
where application_id=222 –Receivables
and product_rule_type_code=’C';

DECLARE
c_package_name CONSTANT VARCHAR2 (30) := ‘XLA_$id1$_AAD_$id2$_$id3$_PKG’;
l_package_name VARCHAR2 (1000);

FUNCTION getpackagename (
p_application_id IN NUMBER,
p_product_rule_type_code IN VARCHAR2,
p_product_rule_hash_id IN NUMBER
)
RETURN VARCHAR2
IS
l_name VARCHAR2 (30);
l_hashapplication VARCHAR2 (30);
l_hashrulecode VARCHAR2 (30);
l_log_module VARCHAR2 (240);

BEGIN
l_hashapplication := LPAD (SUBSTR (TO_CHAR (ABS (p_application_id)), 1, 5), 5, ‘0′);
l_hashrulecode := LPAD (SUBSTR (TO_CHAR (p_product_rule_hash_id), 1, 6), 6, ‘0′);
l_name := c_package_name;
l_name := REPLACE (l_name, ‘$id1$’, l_hashapplication);
l_name := REPLACE (l_name, ‘$id2$’, p_product_rule_type_code);
l_name := REPLACE (l_name, ‘$id3$’, l_hashrulecode);
RETURN l_name;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END getpackagename;
BEGIN
l_package_name :=
getpackagename (p_application_id => 222,–application ID
p_product_rule_type_code => ‘C’, –Rule type
p_product_rule_hash_id => 18 –Hash value
);
DBMS_OUTPUT.put_line (l_package_name);
END;

One of many cool features in SLA is creating manual journal entries. Many times we have a need to pass manual entries to reverse the entries happened a source transaction because the account is wrong or date is wrong or even the value is wrong. All sub ledgers give you a way to reverse the transaction and hence reverse the associated accounting entry. But sometimes you may run out of time and luck and resort to manual entries in GL. Once we are GL to enter manual entries for transactions that pertain to sub ledger, we lose an opportunity to reconcile as we do not have primary references for which we have created this accounting entry for. If we have any reports that are based on SLA tables the reconciliation becomes harder as we cannot link anything that happened in the GL with the SLA. After all, the purpose of SLA is to eliminate this risk of reconciliation with GL.

Also, I guess, Oracle Support uses this route (manual journal entries) to fix any entries that were corrupted in the original transaction.

While entering these manual entries we can capture original transaction attributes using supporting references feature where you can capture attributes like asset_id if it is Assets related entry, transaction_id if it is related to Inventory, invoice_Id for AP and customer_trx_id or cash_receipt_id if it is AR invoice or cash respectively, These give meaning to the manual journal entries.

Process comes in three steps: Creating entries, saving them as incomplete entries with in SLA to be posted later and finally completing this so that this will automatically transfer to GL, journal Import and Post them as needed. To create manual entries you need to assign a sub-function SLA: Create Subledger Journal Entry to the menu. It will appear as a button in the view journal entries page.

The same can be achieved with an API XLA_JOURNAL_ENTRIES_PUB_PKG. Also this is immensely useful if you want to create accounting entries related to transactions in legacy or related systems. You can capture the supporting references related to the related system for reconciliation purpose.

The best part of using this API is that, we do not have to separately code for Journal Import and Posting (with complete journals parameter to complete, transfer and post). The value you pass to this parameter takes care of that.

Sources:Oracle Financial Accounting Hub Implementation Guide (Chapter 12 on Manual Sub ledger Journal Entries API) and Oracle Subledger Accounting Implementation Guide (Chapter 6 Sub ledger Journal Entries).

Many times we pass a file name coming from banks or other sources to a program as a parameter to process the file. Usually that field has to be entered manually. Instead with this technique, generously shared by a forum user, can be used to create an LOV of files sitting in a specific directory of a server location. You can get the code from the forum here.

As we all know till recently we cannot change the item in IB once it is created (neither from UI nor from API) from any source (from IB UI or from Inventory). The reason is plain simple: too many dependencies. Serial number from inventory travels across from Inventory to IB when it is used in a transaction. Also if it is created in IB, it goes and sits in Inventory as well. Also counters are associated with inventory item and they get instantiated when IB is created for that item. This list is partial and goes on and on.

As you can see changing item associated with IB instance has some consequences. Nevertheless, people do make mistakes using wrong serial number when item is transacted and IB gets created or updated with that and subsequent issues ensue. Genuinely sometimes there is a need to change the item, if everything else seems OK.

Till R11i it is not possible and hence people have to do some creative work like issuing it out in inventory and receiving correct serial number or other methods like updating the serial number and expire it in IB and recreating it. Without question, this is a bit painful.

Now this is possible in R12. There is a new transaction type introduced for the application Complex Overhaul and Repair called Item Serial Change. If you use this transaction type in item instance update API, you can change item. But if you use CMRO product this transaction type can be used to execute a transaction to change the item. Of course, if there is a depot repair order open on this serial number, CMRO also cannot change the item. I am not fully clear on the business process that demands item change in that application, but if you have used it, please share. You can see a small presentation here and the script used is here.

Do you know that in R12 defaulting rules are extended to Quoting (ASO) (and hence to iStore?)? These defaulting rules work just like the existing defaulting framework in Order Management.

By default you get a number of conditional templates that you can use to default values (like default price list based on Order type). If the attribute is not available for building default condition template, you can have your own custom dependency rules.

To achieve this you have to write your rules into the aso_dependency_mappings table using a sql file (asodepmp.sql). This sql file can be found in $ASO_TOP/patch/115/sql/ directory. Add your dependency there and run the SQL again. This will create rows into the table mentioned above. After you run the SQL you have to run the program Order Capture Dependency Generator. This will create or alter the package bodies for packages ASO_QUOTE_HEADER_DEP_HDLR and ASO_QUOTE_LINE_DEP_HDLR.

Personally I have not tested all these yet, but if you have already tested and proved, please share your experience. This is a very good feature which we have been waiting for years.

Usually I do not post job postings here. But in this economy, I thought, every bit helps. So if you are interested, here is an opportunity.

Position: Oracle Apps CRM Service  Functional

How long: 3 Months

Where: Portland, OR

How much: Please submit your interest using this contact form, so that I can tell you.

Required Application Knowledge: Field Service , Mobile Field Service, Spares Management, Service Contracts

Specific tasks include:

• Coordinate CRP-II testing
• Migrating setups to PROD instance
• Facilitating CRP-III integrated testing
• Resolving any application issues

Please use this contact form, if you are interested or have any questions.

cforms contact form by delicious:days

We all know how we can generate serial numbers in the system. It is governed by the the setup at the organization and item level. A the inventory organization parameters level you decide whether we want serial number generation at the organization level or item level or user defined. If you choose User Defined and at in the item master you can choose Pre-Defined as your control, you are saying you want to generate your own serial numbers. What happens here is that the the serial number generation logic is followed by the serial number pre-fix and and number that is entered in the item master. Which means you cannot truly create your own serial numbers with this approach.

In order to achieve this requirement you have to use a very obscure package called user_pkg_serial.generate_serial_number. If you put your code here, then that serial number will be generated. This is kind of a hook. The same goes with the lot number. The package is user_pkg_lot.

Who knew that there are standard database packages with names like this!!

There are at least 40 search hits on this blog for this error:

“Please define all periods in which revenue is to be recognized or credited. Note that revenue cannot be recognized or credited in closed and close pending periods.”

Just thought throw some light on why this error occurs. This error is defined in Receivables (AR) and here is the background.

We all know about accounting rules accounting rule duration in AR. Accounting rule tells the system how to recognize the revenue (Variable vs. Fixed) and whether it is deferred or not. On the other hand accounting rule duration (in fixed case we define this in Rule and in case of variable we enter this in transaction) decides how many periods into the future the revenue recognition should happen starting from the rule start date (which usually is transaction date).

The key word here is “future”. For example, if you enter 36 as duration (either fixed or variable), Revenue Recognition process tries to create that many distributions with “future” GL date (36 periods, assuming that the accounting rule is not defined as Deferred). But what if that many number of periods are not even defined in the accounting calendar associated with Ledger or Set of Books? That is where this error comes up.

When you get this error, this simply means that all the periods (number of periods in duration) from the accounting rule start date should be defined in accounting calendar (not required to open or future status). Just define that many number of periods in calendar and this error goes away.

For someone who is pursuing a career in functional roles in Oracle® Applications, it is important to have strong general knowledge of industries, business processes within those industries and terms used in different business scenarios. We usually gain and enhance this business process knowledge by providing solutions for different companies in wide variety of industries. After all you become a most sought after resource because of your deep experience in wide variety of industries and companies. But it is not possible to be there in every project implementation to get that kind of experience.

If you have been working in Oracle® applications for some time now, you must have heard of tools like OBM (Oracle® Business Models), Oracle® Business Accelerators (http://accelerators.oracle.com) that give you good insight to general business processes used in software.

SAP® has something similar called Solution Composer that gives you a library of packaged business processes in general in many different industries. This software, as I understand, is used by SAP® consultants to develop customer specific solutions out of these general processes (much like OBM and OBAs of Oracle®).

This software gives you the following among many other features:
• Pre-delivered content as a basis to develop an initial understanding of business processes in wide variety of industries
• Comprehensive portfolio of Industry, Cross-industry and Infrastructure and Service maps

You can use these pre-packaged functional flows in wide variety of industries to understand your customer processes prior to your implementations. Trust me, you gain a good insight into the processes flows and get an opportunity to start mulling over applying the same to Oracle® Applications.

You can register and download the software at http://www28.sap.com/mk/get/g-smcentry.

This long pause was necessary due to bereavement and an irreplaceable loss in my personal life. Will return normal blogging soon.

I had to delete a number of spam comments without sorting through for relevant ones. If your comment is not attended to, it is because of that reason alone. Please resubmit the comment, if you wish.

Prepayments in business are not unusual. In Payables, we prepay employees for their expenses ahead of their trip; we prepay suppliers in advance for the goods to be delivered in future. The same can happen with your customers as well. You customers would like to prepay (commitments) in advance for the goods to be delivered in future. These commitments can be against

·         Pricing agreements that they have contractually agreed for a specific product

·         Only for a specific product without any pricing agreement

·         Or just a commitment in general which can be used against any purchase without any agreement

This article focuses on how these commitments and pricing agreements are used in sales orders and AR transactions. Also it discusses what happens to the customer balances and accounting when this sales order line is invoiced.

We all know the order to cash process. We

·         Enter an Order

·         Ship that order

·         Invoice that order

·         Collect and apply cash against that invoice

But just reverse the cycle where you will see

·         Receive cash from customer in advance

·         Create deposit transaction and apply that cash receipt against the deposit

·         Enter and ship an order. While doing so you specify which deposit transaction (commitment) you are shipping goods against.

·         Invoice the order

·         Invoice balance gets adjusted and applying the prior commitment (invoice adjustment).

Commitments in are created in AR in the form of transactions. Commitments can be of two types: Deposits and Guarantees. User guide defines these two transactions as follows:

Deposits: Customer’s prepayment for goods or services that you will provide in the future.

Guarantees: Contractual agreement with your customer to conduct business over a specified period of time.

Technically there is no difference, but business process wise these two transaction types help identify the transaction in a different perspective. While creating these commitments, you can refer to a pricing agreement created for this customer in OM. This will help qualify when you are referring to commitment in order line.

If a customer pays $10,000 in advance, you get a check without any invoice reference. It can come through the lock box as well. Based on this you create a deposit and apply this check to it making the balance in deposit to zero. Now you have commitment.

Next step is to enter a sales order. While entering the sales order line, you can choose the commitment you just created in AR in the LOV in the commitment field (available in order lines).You ship the goods and invoice the customer for the same. When you run auto-invoice import for the same you will see that Prepayment Matching program automatically kicks off. What this program does is to apply an adjustment to this invoice to the extent of the deposited amount. By doing so this invoice balance is reduced. So now you have

·         Deposit

·         Cash receipt

·         Invoice for sales order

·         Adjustment to reduce the balance on this invoice

Each one of them has accounting as follows. You can follow serial number logic to identify which account is getting cleared in which transaction.

When cash receipt is created:

When Deposit created:

When cash receipt is applied against this deposit:

When order line is invoiced:

At the time invoicing since customer has already paid for these goods, an adjustment is created making this invoice balance to zero

The net entry at the end of all these transactions is:

There are a lot business process steps that need to be filled in (communication of cash receipt and creation of commitment so on) and some automation is possible to create transactions.

 Let me know your views on this process.

Just in time when I was planning to publish this article, the need for the same came from someone in the forums. Here is the requirement. Is there any way to pop up a key flexfield in the DFF segment or even in a key flexfield? Many a times, in mature global implementations, the need for the extra fields in many forms in insatiable. This article gives you simple steps to create a KFF in a DFF. You can extend the same to a KFF too.

Here are the steps:

Step1:

Create a KFF table in your custom schema with segment columns in there using the following SQLs in your custom. I am creating this table in AR schema.

TEST_KFF_TBL.sql

TEST_KFF_SEQ.sql

Step2:

Create a synonym for the same in APPS schema using this.

TEST_KFF_SYN.sql

Step3:

Register the table. You can only register the table using APIs (up until 10.7 GUI we were able to do this manually in Application Developer screens. But now we use API only). Use the following to do that.

TEST_KFF_TBL_REG.sql

Step4:

Verify the table registration. Register the KFF in that application. Check that the segment columns are enabled.

Step5:

Create a structure and enable as many as segments as you want. Make sure dynamic insert is enabled.

Step6:

Create a special value set based on this KFF. For special validation use the following for events:

TEST_KFF_VS.txt

  Step7:

Choose a DFF where you want this KFF be available. Enable a segment in that DFF and assign this value set.

Step8:

Test your setup. If you save this DFF segment, CCID is stored in the DFF segment and a row goes into your table (KFF table) with that CCID and appropriate segments. If you update the segment, it will create another row with new CCID.

 I sincerely thank Shabeer Mohammed, who executed this solution flawlessly, for sharing this solution with the community.