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.

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. 

Just a quick one. Remember the colors we see when we view the requests we submitted as a concurrent request? Green when it is in pending or running status, Yellow for warning and Red for the errors? Do you know these colors are controlled by a profile called FND: Indicator Colors. If you se the value to Yes, you will these colors, if you say No, you will see no colors. Also I noticed, the prameters that are required while submiting the concurrent program usually show up in Yello color (standard for required fields). If you set this profile value to No, even they are becoming white.

Try it and feel the effect for yourself. I did not find any profile to set your own colors.

There are about 39 seeded Key flexfields (KFF) when I last checked 12.0.4 vision instance. For each of these KFFs we can create countless structures (depending the KFF) under respective applications. The beauty of these flexfields is that we can create a combination of values from a number of sources and yet store it in a single field.  We have a number of features around flexfileds like using value sets that are vitually source values from any where in apps, security rules that can impose security of usage of values by a specific person or responsibility, cross validation rules that will not let you create undesired combination of values, if the combination is not created yet and more.

But no matter what Oracle gives as standard feature, we still need more. This question from an unappreciative user of forums resulted in a good find. If you have any need to write your own logic to validate creation of a combination, Oracle gives you a pre-hook for all key flexfields.You can write all your validations that you need (apart from using above mentioned features) before eBusiness suite creates one. If your validations fail, you can stop the creation of that combination.

The package name is fnd_flex_plsql. It comes with no logic in the body, but just returns boolean value of Ture. Specifically as the gl_code_combinations table does not have standard “WHO” columns, this might come handy to solve that issue.

You can find more information in Oracle support portal (note 295208.1) about its usage and support information.

eBusiness suite implementation projects are complex, time consuming and require extreme planning. These projects vary in nature. Fresh implementations, rollout to new divisions, adding new products to the exiting portofolio, projects that are direct result of mergers and acquisitions or simply just enhancements to process and so on. Each project is unique from solution and industry perspective and has different needs of its own.

But whatever is the nature of the project we have different teams working towards a same goal (question of who is doing what). Each project goes through several stages: from gathering business requirements to production go live (question of when). In this process we go through a number of testing cycles to keep the progress in check.

These questions of who, what and when drives the demand for number instances of eBusiness suite and when they are needed. Typically we go through the following process (simplified) before we go live.

Typically we prepare the base instance at a desired application release level. We setup application parameters in this instance for basic out-of-the-box functionality testing.More the number of applications more the setups and complex are the dependendencies. In stage two we copy this instance for testing. This might lead to some patches identification. Usually we apply these patches to patch instance first then to functional instance. If the patch pass the test then it will be applied to the base instance so that the same issue will not recur again in the future cycles of testing.

All these setups have to performed manually. These setups are interdependent and need to be setup carefully as some of them are irreversible. Business Accelerators help in these setups to a great extent. You can find more information in the following links.

Oracleappsblog
Oracle’s Data Sheet
Shivmohan Purohit
Official Site for Accelerators (requires login)

During testing if we make any changes to the setups, we make sure all the changes that we have made in the testing instance are applied back to the gold (base) instance. Without iSetup we have to make sure to document these and apply them back to the gold (base instance). Mistakes can happen here too!

From here the project takes off. We copy this base instance for all different teams in the project for their needs. Development gets a copy to develop code for the conversions and extensions, DBA team gets one for patch testing, change management or system admin team for migration of code or security testing so on. Here is the issue. If the functional team decides to change any of the setups in their instance, they have to be made available for all the teams. Again without iSetup we have to make sure to go and apply the change to base instance and copy that instance all the instances again, or make change in all the instances. Both these options involve considerable amount of resource idling.

iSetup is set to change all that. Uma Prabhala has documented 10 good reasons as to why you should use iSetup. Striking among those are :

• Setup dependencies.Once setup manually, for the rest of the instances you do not have to remember the setup sequence again. The dependencies are taken care of in selection sets.
• Reporting the difference between setups among instances. Great feature to use when we want to check the changes made and migrate only what we need.
• Migrating incremental setups from one instance to another.
• Custom code migration by using custom selection sets
• Download the setups and tranform the data and upload the transformed data. Which means, if you have to change the name, you can download the data from source setups and change names and upload.
• And the FAQ document that you can find in this link tells you that it is absolutely free!

You can clearly see how isetup streamlines the process by comparing the figures with and without isetup.

Just go give an example, in a recent project, we had to collapse three operating units into one as the three different companies in the same line of business got merged.

There were three options :

• Leave the systems as they are and assign three responsibilities of three operating units to the users who are suppose to access information
• Create one more operating unit in the same production instance and convert all the existing three OUs data into newly created one.
• Create totally new instance,setup from scratch and convert what need from old instance.

We chose the third one for many reasons.

Hence we had to copy the setups one by one.If we had iSetup, this could have been painless as far as the setups are concerned (both instances were on same release, which is mandatory for iSetup to be used. It does not support upgrade, only moving the setups between instances at same patch level).

One more thought. Some more creative uses of this product include :

Conversions.The hardest part in conversions is to transfer the files from desktop to servers so that the loader programs can them up. By creating a custom selection set, you can upload the file from your desktop (say your open sales orders). The contents of this file will be loaded into a staging table using the interface method that is registered earlier (this uses the control file is called from the FNDLOAD to load the data from your data file). After it is loaded you can call custom program that either loads into interface tables or calls the Sales Order API to create orders. You can find the procedure on how to do that in user guide and some help in Reshma Shinde’s blog.

From to day to day operations perspective,you can even use this product to load say, AP invoices, from a spreadsheet to interface tables to create invoices. This is an alternative to the Web ADI.

From change management perspective, you can also migrate code from instance to another with some careful planning.

But I am sure this product has a lot of potential and is well on its way to revolutionize the way we are implementing eBusiness suite.

Attachments in Oracle eBusiness Suite provide a great functionality to attach a file of type, text, word, excel or pdf against a specific entity. This entity can be an Item or BOM. For that matter attachments can be created for any entity be it master or transactional data. And from the very screen where this data is seen  attachments are available to view as file.

A number of times when we are bringing this data from either other applications or converting data from the legacy systems, we need to load attachments against that entity. A classic example is to load a PDF file (instruction manual) that gives you a lot of information about an item. Or an assembly diagram of the product against its BOM is also a good example.

This article provides a way on how to bring in an attachment along with that entity.

In this example I will be creating an item using an API first and then attach a PDF file to that item. This article assumes some level of understanding of attachments functionality. Here are few steps in enabling attachments against an entity.

1. Register document entities. Here you need to know what is the primary key of this document entity. In this example it is inventory_item_id and organization_id and document entity is MTL_SYSTEM_ITEMS (The table behind the items).
2. Enable the attachments for a form or a function where you want attachment to be seen. As we have chosen item master, we need to find out the form function of the item master. You can find that from the menu.
3. If there are many blocks in the form you need to know in which block you want this attachment to be seen. Choose that block.
4. If you have any security like Operating Unit, enable that as well.
5. Make sure to choose the primary keys of the document entity in the entity declaration. In this case if you have chose Key1 for organization_id, the value of organization_id from the item master form will be stored in the pk1_value field and so on.
6. Assign attachment categories to form functions. This decides what kind of document cartegories need to be assigned to form functions.

I have used  Vision instance to perform this test (12.0.4). I loaded this item into V1 (master organization of Vision Operations). To load item I have used Engineering API ego_item_pub.process_item.

Here are the steps involved:

First create a directory where you want the file to come and sit. If your partner system is sending the files everyday along with the data, you need to decide where to store that data on the server. Data file should contain the entity name (item number) and file name (file to be attached). File will come and sit on the server along with the data file. From here you can start loading into a staging table and process them.

Step 1:

Decide the location where the file is going to sit and create that directory using this script. In this example I am using  /tmp as my directory. Code is here.

Step 2:

Now we need to read this file in this directory and load that into a table. For that purpose I have created this table. Code is here.

Step 3:

Now load the file into this table. Code is here.

Step 4:

Now that we have file in the staging table, we will start loading the file into FND schema of Oracle Applications using this code.

Step 5:

Now let us use item API to load item and get the item_id. Use this code.

Step 6:

Use this inventory_item_id, organization_Id, x_access_id and x_file_id to load PDF file against this item. Use this code.

Now you should be able to see this attachment in the item master against this item TEST111 which is created.