Error While Creating Customer

I was trying to create customer in my newly imported EBS 12.1.3 and while trying to create the customer I got the below error “Provide a positive integer for minimum customer balance amount or percent when balance amount overdue type is amount or percent respectively”

 

Troubleshooting Error - FRM-40212: Invalid value for field REFERENCE_NUMBER

While trying to add serial number / instance to service contract we would have often seen this error :-
‘FRM-40212: Invalid value for field REFERENCE_NUMBER’
OR
‘FRM-40212: Invalid value for field SERIAL_NUMBER’.
There could be many reasons why the error is coming. Lets try to understand what it is and what could be done to resolve this error.

• Serial / Instance number doesn’t exist => Validate serial in present in Install Base. If its there create the item instance and then try to add.
• Serial / Instance is in EXPIRED status => Expired serial/instance can not be covered and added, Unexpire the instance and try adding it again. There could be scenario where serial is in status other than EXPIRED but the status is configured to not to allow coverage. Validate the instance status configuration.

 

Error while importing EBS 12.2.4 VM Template / Virtual Appliance

While working on EBS12.2.4 installation on Virtual Box using Oracle Virtual Appliance (.OVA) file, I received an error when I tried to import the Virtual Appliance file. The error was very generic and given me a tuff time to figure out what was the actual cause.

Failed to import appliance D:\EBSTemplates\12.2.4\Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.
Seeking within the archive failed (VERR_TAR_CHKSUM_MISMATCH).
Result Code: VBOX_E_IPRT_ERROR (0x80BB0005)
Component: Appliance
Interface: IAppliance {3059cf9e-25c7-4f0b-9fa5-3c42e441670b}

OR

Installing E-Business Suite Release 12.2.4 Oracle VM Template

I was wondering when I will have my first hand’s on E-Business Suite 12.2.4… and here it comes. You can also make this true, just follow these simple steps and you will be there. It will be a fun journey and result will be fruitful.

The entire installation is divided into following sections :-

• Section 1 – Hardware and Software Pre-requisites
• Section 2 –  Downloading software and preparation
• Section 3 –  Importing Virtual Machine
• Section 4 –  Configuring EBS 12.2.4 Virtual Machine
• Section 5 –  Usage and Maintenance

Section 1 – Hardware and Software Pre-requisites

I have used my personal laptop to implement this guide and it works fine. My laptop runs on Windows 7, I have used virtual machines inside my Host OS to run EBS 12.2.4 visions instance.

E-Business Suite Release 12.2.4 Oracle VM Template

For those who love to experience latest on E-Business Suite here is the good news… Oracle has released its latest E-Business Suite 12.2.4 on VM Virtual Appliance. That means a preinstalled VISION / PROD environment ready to be used. Isn’t this a great!!!

The Virtual appliances provides full software stack, which includes Oracle Linux 6.5 operating system, Oracle E-Business Suite and additional required technology components.  These can be easily imported into Oracle VM Manager deploy E-Business Suite Linux 64-bit environments on compatible server-class machines running Oracle VM Server OR imported into Oracle VM VirtualBox to create virtual machines on a desktop / laptop. Just logon to https://edelivery.oracle.com/linux and select product pack as Oracle VM templates.

Using Service Contracts Test Import Program

After the Service Contract Import functionality has been introduced, its been a rough ride to understand the interface table and identify bare minimum fields that are required to populate to import a contract successfully. To overcome that, the developer’s of Service Contract Import program built a Service Contract Import Test Program that uses a sample contract and populates required columns in interface tables that can create a replica of the original contract. This program is just a testing tool to understand the import functionality behavior.

To get more details about Service Contract Import Program you can visit following posts :-

• Intro to Service Contracts Import Program
• Service Contracts Import Scope and Limitations
• Architecture of Service Contract Import Program
• Troubleshooting Common Service Contract Import Error
• Debugging Tip for Service Contracts Import Errors

There are SIX interface tables created for this interface :-

OKS_HEADERS_INTERFACE
OKS_LINES_INTERFACE
OKS_COVERED_LEVELS_INTERFACE
OKS_USAGE_COUNTERS_INTERFACE
OKS_SALES_CREDITS_INTERFACE
OKS_NOTES_INTERFACE

Debugging Tip for Service Contracts Import Errors

Since Service Contract Import program is NEW, some times finding and fixing the issue while importing contract can be difficult.  When all other attempts fail, completing these steps might lead to a place where you can determine what is the issue. Based on that you can determine what fix is needed. This is an example for the error 'Enter a valid value for one and only one type of covered levels'. The message is intended to report that the interface entry in oks_covered_levels_interface has information for multiple covered levels.

For example, the error will occur if the interface table entry has information for a covered item and for a covered system (or party, site, etc.). This message is also displayed when no covered level information is entered.

Let's explore an example where the covered_item_id and the covered_item_name are populated in oks_covered_levels_interface, but something is wrong with that information.  How can the real error be identified?

Two of the primary programs that are used for importing contract information are defined in the files

• OKSPKIMPVALB.pls
• OKSPKIMPWRB.pls.
  

Troubleshooting Common Service Contract Import Errors

There are many errors that you may face while importing Service Contracts using Service Contracts Import Program. Few of the errors and solutions are as follows :-

• HEADER INTERFACE ERRORS

ERROR MESSAGE	PROBABLE CAUSE / SOLUTION
There are no records processed by request id 'xxxxxx' in the interface tables.	The batch_id in oks_headers_interface might be wrong OR the interface_status would be ‘E’ or some thing else, It should be NULL or 'R'.
The combination of contract number and modifier is entered for more than one record in the interface table.	Two entries exist in oks_headers_interface with the same batch_id and contract_number. The same contract_number cannot exist multiple times in the import table.
Enter a valid operating unit.	Correct the operating_unit_id in oks_headers_interface.
Enter a valid Bill To Site Usage Id. Enter a valid Ship To Site Usage Id.	Correct the bill_to_site_usage_id or ship_to_site_usage_id in oks_headers_interface.  If the values are known to be correct, verify that the customer account information is correct.
The given customer contract administrator is invalid.	Correct the customer_contract_admin_id or customer_contract_admin_name in oks_headers_interface.  The transaction is accepted if any one of the columns has valid information.

Architecture of Service Contract Import Program

High Level Process :-

• Populate interface tables. Apart from the relevant columns that contain contract information, populate a unique batch id  in the batch_id column.
• Run Service Contracts Import program with batch id, mode, number of workers, commit size as input.
• Run the program in Validate only mode .
• Review the output/log file which summarize the successful and failed records.
• Re-run the Program in Import Mode to get the data into the base tables
• Review the imported contract in the Authoring Form.
• Validate the contract by running QA Check.
• Run the Service Contracts import Purge Program to purge the Successful records.

Service Contracts Import Scope and Limitations

Scope / Possibility :-

• Bulk import all types of Service contracts (Service / Warranty and Extended Warranty / Subscription / Usage Contracts)
• Import all type of covered levels (Service / Warranty / Extended Warranty / Subscription / Usage)
• Import contracts in different status types (Entered, Canceled, Signed, Active, Expired)
• Import of Sales Person, Contract Administrator at the header level.
• Import of customer Billing contact at line level.
• Ability to associate a contract with contract group
• Import of sales credits for the contract header.
• Import of only Fully billed of Fully Unbilled contracts.
• Import of Standard coverage possible.
• Instantiation of counters for imported contracts
• Import only Intangible Subscription contracts.
• Import of header Notes is possible.

Intro to Service Contracts Import Program

Before release of EBS 12.1 there was no supported way to import contracts into the EBS Service Contracts application - there were no public APIs nor contract load programs provided.  From release 12.1 onwards the 'Service Contracts Import Program' is provided to load service contracts into the application. This note explains the program architecture and details the interface table definitions.

How To Track A Serial Number Across Organizations

While we transact serial in organization, after transaction it only appears in the current organization.

The On-hand Quantity -> Serial Number query screen displays the current state of the serial number. It queries the serial number table (MTL_SERIAL_NUMBERS), Since the serial number table will only have one record for the serial number and item combination. MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID hold the current organization of the serial number. This maintains only the last organization to have the serial number.

• If the serial number changes organizations, the serial number will show only in the new organization.
• If a serial number is shipped to a customer, the serial is visible with status 'issued out of stores' in the last organization that owned the serial.

 

XDOException: No corresponding LOB data found

Hello There.. while I was working yesterday on XML template, I came across below error and would like to share with all of you. After the template is uploaded and report is ran the request completed in WARNING. When you check the log you will get message that OPP is failed for the request. When you check the OPP log and you will see following error for the request as below :-

[8/22/14 11:47:42 PM] [OPPServiceThread0] Post-processing request 62447806.
[8/22/14 11:47:42 PM] [1246813:RT62447806] Executing post-processing actions for request 62447806.
[8/22/14 11:47:42 PM] [1246813:RT62447806] Starting XML Publisher post-processing action.
[8/22/14 11:47:42 PM] [1246813:RT62447806]
Template code: C_CSD_PACKLIST
Template app:  C_APPS
Language:      en
Territory:     US
Output type:   PDF
[8/22/14 11:47:43 PM] [1246813:RT62447806] Output file was found but is zero sized - Deleted
[8/22/14 11:47:43 PM] [UNEXPECTED] [1246813:RT62447806] java.lang.reflect.InvocationTargetException
        at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at oracle.apps.xdo.common.xml.XSLT10gR1.invokeNewXSLStylesheet(XSLT10gR1.java:611)
        at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:239)
        at oracle.apps.xdo.common.xml.XSLTWrapper.transform(XSLTWrapper.java:182)
        at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:1044)
        at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:997)
        at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:212)
        at oracle.apps.xdo.template.FOProcessor.createFO(FOProcessor.java:1665)
        at oracle.apps.xdo.template.FOProcessor.generate(FOProcessor.java:975)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.runProcessTemplate(TemplateHelper.java:5936)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3459)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3548)
        at oracle.apps.fnd.cp.opp.XMLPublisherProcessor.process(XMLPublisherProcessor.java:302)
        at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:176)
Caused by: java.io.IOException: Root problem: oracle.apps.xdo.XDOException:No corresponding LOB data found :SELECT L.FILE_DATA

Generating Serial Numbers

While setting up the serial control of an item If you specified predefined as the serial number control type for an item, you must generate the serial numbers for the item. If you specified entry at inventory receipt or at sales order issue, you can optionally predefine serial numbers for the item. 

The process of generating serial numbers does not assign serial numbers to units in inventory, but reserves specific serial numbers for an item, for later use. When you run the serial generation program, if you have the same serial prefix number across organizations, the concurrent program searches across the organizations, and generates serial numbers using the highest start number across the organizations.

• Navigate to the Generate Serial Numbers window / All Reports window / Go to View –> Request –> Single Request –> Serial Generation.

Setting Up Serial Number Control in Oracle Inventory

Oracle Inventory provides robust serial control for your organization. Before you start using serial number control, you must set the item attributes and organization parameters:

• Setting Up Serial Number Control for an item :-
  We establish serial number control for an inventory item when you define it on the Item Master. If we don’t want to control serial attribute at Master Organization level we can set at child / transaction organization. You can choose from No control, Predefined, At inventory receipt, or At sales order issue.
  • No Control – If set to ‘No Control’, item will be termed as Non-Serial controlled
  • Predefined – If set to ‘Predefined’, serial number will be set for item before its used. In this case we generate the serial number first and then use it.
  • At Inventory Receipt – If set to ‘At Inventory Receipt’, serial number will be generated when the device is received into that organization. This is mostly used in Service/Repair business.
  • At Sales Order Issue – If set to ‘At Sales Order Issue’, serial number will be generated when we ship device for the organization. Serial Number will be generate based on the prefix and suffix setup.

Serial Number Restrictions for Transferring Between Organizations

Oracle Inventory places certain restrictions on serial controlled items that we transfer between organizations where the serial control in the source and destination organizations is different. The following tables explain the expected results that occurs in source and destination organization.

Direct Inter organization Shipment

Source Org / Destination Org	At Receipt or Predefined	At Sales Order Issue	None
At Receipt or Predefined	Allowed, serial moved to destination	Allowed, serial number created as sales order issue	Allowed, serial entered at issue and set to issued from stores in source organization. No serial received in destination organization.
At Sales Order Issue	Not allowed	Allowed, serial number dynamic entered at issue	Allowed, no serial created or moved.
None	Not allowed	Allowed, serial number created at sales order issue	Allowed, no serial created or moved

Understanding MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE

Oracle Inventory provides complete serial number support for inventory transactions. We can enable serial number control for specific items in your inventory organization. For items under serial number control, we assign unique serial numbers to individual units and thereafter reference the same serial numbers each time you perform material transactions

There are various level of serialization control Oracle Inventory provides. If we query mtl_system_items_b.serial_number_control_code it provides items serialization status. Here they are…

Serialization Code	Meaning
1	No Serial Number Control
2	Predefined Serial Numbers
5	Dynamic Entry at Inventory Receipt
6	Dynamic Entry at Sales Order Issue.

Understanding MTL_SERIAL_NUMBERS.CURRENT_STATUS

So here I am after a long time with a very short post..don’t mind !!!

We often came across using serial number in oracle applications though its shipping the serial for a sales order, a repair order or receiving a serial into inventory. There would be many cases where we have a device in hand with a serial number ready to be shipping but system didn’t allow its saying some error message.. I am not going deep in what all error message comes but the bottom line is where is the serial and what is its current status.

If we query mtl_serial_numbers.current_status it gives back some number which represents its actual status. So lets find out what those numbers represent.

Serial Status	Meaning
1	Defined but not used
3	Resides in stores
4	Issued out of stores
5	Resides in intransit
7	Resides in receiving
8	Resides in WIP

How To Apply Patch In Oracle Application

Patching is one of the very basic task that DBA perform. Well I am not out here as DBA, though I like stuffs what they do. I believe many out there love to have there own Vision Demo instance to check out seeded functionality and explore new solutions. Vision Demo comes with basic setups and don’t carry latest patches and fixes. So we may end up having very basic functionality and not able to explore new features. That brings us here to understand how can we apply patch.

Lets get started…

There are steps involved in applying patch to Oracle Applications.

• Validate Patch In Oracle Application :- Before proceeding any further first check if the patch is already applied to the instance or not. To check if the patch is already applied follow the this post - Check Patch in Oracle Application
• Download Patch : Get the patch from Oracle Support (Best know as - Metalink) and go through the readme file. This will tell you if any pre-requisite is there and if you need to perform any activity after patching. Following screenshots will tell you how to down and go through readme file.

How To Check Patch Applied In Oracle Applications

In this section we will try to find out if a patch is applied in Oracle Application or not. I believe you all know what a patch and why we use it. Without further delay lets get stared.

There are two ways you can identify if the patch is applied or not.

• Validation From Application

To validate the patch from Oracle Application please follow the below step :-

• Navigate to System Administration / System Administrator –> Oracle Application Manager –> Patching and Utilities.

• Enter the Patch Number in Patch filed in search page that opened.
• Enter additional criteria if you want to.
• Click on GO.

• You can see the result in bottom section.

• Validation From Database

To validate the patch from Database please follow the below steps :-

• Login to database using SQL Plus or any other tool like SQL Developer / Toad etc.
• Run the below query and validate the result.

  select * from ad_bugs
  where bug_number = :pi_patch_number

  

Register Custom Table Using Script In Oracle Applications

In my last post I talked about how to register table in Oracle Application. The steps involved are too much and will take lot of time if we have to create multiple tables or a single table is having too many columns.

To Save time you can use below script which will do the job for you. I am considering the your table is already created.

DECLARE
   vc_appl_short_name   CONSTANT VARCHAR2 (40) := 'C_APPS';
   vc_tab_name          CONSTANT VARCHAR2 (32) := 'C_FND_INTERFACES';
   vc_tab_type          CONSTANT VARCHAR2 (50) := 'T';
   vc_next_extent       CONSTANT NUMBER        := 512;
   vc_pct_free          CONSTANT NUMBER        := 10;
   vc_pct_used          CONSTANT NUMBER        := 70;
BEGIN

Register Custom Table In Oracle Applications

All you technical people out there very well know the purpose of creating custom table. Its basically when we need to store data before pulling into standard table (In case of inbound interface) or putting extract of data to share with external entity (In case of outbound interface). All these activities can be accomplished without registering custom table in oracle apps.

If you have a need to use these custom tables in standard functionality in frond end like using in Alerts / Audits, you must register them. Otherwise your custom table will not be visible in front end.

Lets start this by creating a sample custom table.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 
Connected as c_apps
 
SQL> 
SQL> CREATE TABLE C_FND_INTERFACES
  2  ( INTERFACE_ID            NUMBER               NOT NULL PRIMARY KEY,
  3    SERVICE_CHANNEL         VARCHAR2(64 BYTE)    NOT NULL,
  4    SOURCE                  VARCHAR2(64 BYTE) NOT NULL,
  5    ATTRIBUTE1              VARCHAR2(2000 BYTE),
  6    ATTRIBUTE2              VARCHAR2(2000 BYTE),

Register Custom Application In Oracle Applications

As a standard practice when ever you need to write custom code/forms/reports in Oracle Applications you must put then under custom application TOP. This is to avoid impact of patching on custom objects. If we put custom object under stand TOP it might create issue and after patching it may start malfunctioning. If we have all custom code at one place it easy to maintain as we know exactly how many custom objects we have and where they are.

Before staring you need to first decide what should be your application name and short name. Here we go ....

SCHEMA NAME    : C_APPS
TOP NAME       : C_APPS_TOP
Application    : Custom Application
Data Group     : Standard
Request Group  : Custom Request Group

#1 – Create directory under application top

• Login to Application Server and Source the environment variable.
  

  login as: oracle
  oracle@192.168.56.200's password:
  Last login: Sun Feb  2 12:53:38 2014 from 192.168.56.1
  [oracle@ebsapp ~]$ 
  [oracle@ebsapp ~]$ pwd
  /home/oracle
  [oracle@ebsapp ~]$ . /u01/E-BIZ/apps/apps_st/appl/APPSVIS_ebsapp.env
  [oracle@ebsapp ~]$ cd $APPL_TOP
  [oracle@ebsapp appl]$ pwd
  /u01/E-BIZ/apps/apps_st/appl
  [oracle@ebsapp appl]$

Accessing Approval Management Engine (AME)

Accessing Approval Management Engine (AME) is not possible as we access other responsibility / functions in Oracle Applications like Order Management / Application Developer etc.

AME is controlled by RBAC (Role Based Access Control). So in this case even you assign responsibility to the user but if user don't have the appropriate roles then you will not be able to access Approval Management functions.

There are two seeded responsibility for AME :-

• Approvals Management Administrator
• Approvals Management Business Analyst

Let's assign the responsibility directly to user and see what happens.

Service Contract Setup - Part : 8

Contract Approvals

Contract Approval is one of the most important setups. It determines who will the approver of the contract and upon successful approval only a contract become Active and entitled to offer service to customer. Prior to R12 contract approval is determined through a profile option 'OKC: Contract Approver'.

But in R12 the standard contract approval workflow integrated with Approval Management Engine (AME) to control and run the approval process. Key attributes are integrated in AME for Service Contracts. Below list provides bunch of service contract seeded attributes available in AME. If required you can create your own attributes as well.

Understanding Oracle Apps Form Personalization

Form Personalization is a feature in Oracle E-Business Suite 11.5.10 onwards that allows you to change the behavior of Forms-based screens. Prior to this release if we need to change the behavior of Oracle Forms we need to change the "custom.pll". That's indeed requires lot of effort and time compared to Forms Personalization which gives you a interactive user interface to implement the changes needed on the forms.

With the help of Form Personalization we can change properties of form, executing built-ins functions, display custom messages, add menu entries to call another forms, add rules, conditions and actions.

To open form personalization go to Help -> Diagnostics -> Custom Code -> Personalize