Several months ago, a customer had reached out to me to see if Stat could support automating the WebADI configurations just like it does for the configurations in the $FND_TOP. So, I did some research which led me to the Oracle E-Business Suite Desktop Integration Framework Developer's Guide (Jaeckel, 2009,2010)
Oracle configurations, aka functional setups, can be tricky. But generally, they adhere to the following rules, or at least that is what the Administrator’s guide says. When it comes to data preservation, FNDLOAD uses the OWNER and LAST_UPDATE_DATE attributes to determine whether to overwrite pre-existing data. The rules applied are:
- If the entity being uploaded is not present in the database, a new entity is always inserted.
- Entities uploaded from a file with OWNER=SEED never overwrite entities with OWNER=CUSTOM in the database.
- Entities with OWNER=CUSTOM uploaded from a file always update entities with OWNER=SEED in the database.
- If the owner of the entity is the same in the file and database, the entity is updated only if the LAST_UPDATE_DATE in the file is later than the LAST_UPDATE_DATE in the database. (Wang & Farrington, 2007)
However, Depending on the configuration file, a row that exists in the database but not in the text file (.ldt) may or may not be deleted when the text file is uploaded. You need to refer to the configuration file to determine how such rows are handled or check the product’s developer guide.
The information below is the section of the before mentioned guide I used to configure the WebADI setup in Stat.
EBS Desktop Integration Framework Developer’s Guide: Loading Integrator Definitions
You can use the Generic Loader (FNDLOAD) concurrent program to download integrator definitions from an Oracle E-Business Suite instance to a text file, and to upload integrator definitions from a text file to an Oracle E-Business Suite instance. For example, you can download an integrator definition from a development or test instance and upload it to a production instance. You can also download and store a text file representation of an integrator definition for source control purposes.
Use the bneintegrator.lct configuration file with the Generic Loader to load integrator definitions. The bneintegrator.lct file is located in the $BNE_TOP/patch/115/import/ directory.
The entity for this configuration file is BNE_INTEGRATORS. This entity includes the following sub-entities: BNE_QUERIES, BNE_PARAM_LISTS, BNE_COMPONENTS, BNE_SECURED_OBJECTS, and BNE_VIEWERS.
Downloading Integrator Definitions
To download integrator definitions using the Generic Loader, specify the bneintegrator.lct configuration file, the name of an LDT file in which to save the downloaded data, and the BNE_INTEGRATORS entity. You can choose to download all integrators for an application, a single specific integrator, or two or more specific integrators.
- To download all integrators for an application, specify the application short name for that application in the INTEGRATOR_ASN download parameter. For example:
- $ FNDLOAD apps/apps 0 Y DOWNLOAD bneintegrator.lct bneintegrators.ldt BNE_INTEGRATORS INTEGRATOR_ASN=BNE
- To download a single specific integrator, specify the application short name for that application in the INTEGRATOR_ASN download parameter and the internal name of the integrator in the INTEGRATOR_CODE download parameter. For example:
- $ FNDLOAD apps/apps 0 Y DOWNLOAD bneintegrator.lct bneNewMsgs.ldt BNE_INTEGRATORS INTEGRATOR_ASN=BNE INTEGRATOR_CODE=FND_NEW_MESSAGES_INTG
- To download two or more specific integrators, specify the application short name for that application in the INTEGRATOR_ASN download parameter and a list of the internal names of the integrators in the INTEGRATOR_CODE_LIST download parameter. The integrator internal names in the list should be separated by commas and should not include wildcards. For example:
- $ FNDLOAD apps/apps 0 Y DOWNLOAD bneintegrator.lct bneNewMsgs.ldt BNE_INTEGRATORS INTEGRATOR_ASN=BNE INTEGRATOR_CODE_LIST=FND_NEW_MESSAGES_INTG,JOURNALS_115 (Jaeckel, 2009,2010)
The Developer Guide, in this instance, gives me exactly what I need in order to add the configuration to Stat. The example I am providing here is for a single integrator (INTEGRATOR_CODE) since I want to manage, version, migrate, and track objects individually. The option to do all or a group of integrators could potentially cause locking issues in Stat as well as make it more difficult to track the change history of a specific integrator.
Steps to add Integrator object to Stat
New Oracle Setup objects may be added via the Stat Object Type Maintenance screen. Access to Object Management requires Administrator level access. It is also recommended that you first configure any new setup objects in your non production Stat environment. The navigation to the screen is Maintenance -> General -> Object Types.
To configure support for new object types:
- In the Object Class field, select Oracle Apps Setup Objects.
- Click New.
- Create an entry for BEN Startup Action Types, as illustrated above.
- Mouse click the Apply button to save.
- Mouse click the Setup button at the top right of the window. This opens the Details window.
- Select the version of Oracle Applications you are using. Next, you need to map the contents of the .lct file into Stat
- In the Lct Filename field, enter lct.
- In the Entity Name field, enter BNE_INTEGRATORS.
- Next, you can optionally pass any appropriate parameters to the FNDLOAD utility in the Parameters For new records, it defaults to the value, “CUSTOM_MODE=FORCE.”
- In the Lov Partial Sql field, enter
from BNE_INTEGRATORS_TL bib
where bib.APPLICATION_ID = fa.APPLICATION_ID
This is the sql statement that retrieves the list of objects.
- Next, you need to define the parameters that identify a unique row. Our first parameter name is integrator_asn, column name is PRODUCT_CODE, and the display name is ASN.
- Click the Add button to add the second parameter. The name is integrator_code, column name is INTEGRATOR_CODE, and the display name is Integrator code.
- Click Update to save your changes.
- Restart the Stat Oracle Agent to test our new configuration. Once the agent is restarted, you can perform a Fetch Object list to verify the new object type.
- In the Environment field, select an environment.
- Click Fetch. If the object is defined correctly, a list of objects will be retrieved. for example, the list has 2 columns corresponding to each parameter that you defined. Now you can test the archive for any particular row from the retrieved list.
- Select a row from the list and click Test Archive
- Once agent is done, examine the content of the archive to make sure a unique row was retrieved.
- If content of the archive text box does not identify a unique record or it is empty, you will likely get an error on the Oracle Agent log. In this case, you may need to run your Oracle Agent in Debug mode to identify any problems.
As you can see, extending Stat’s functionality to support additional configurations is pretty straight forward. In all, I estimate I spent about an hour in total. The majority of that time was reading the Oracle documents I used to configure the setup in Stat. Hopefully, it will take you less time since I have provided the steps necessary.
Once configured, it will save you countless hours and frustration in manually moving WebADI objects (Integrators).
I hope this is helpful.
Note: This information is provided as an example only of how Stat can be extended to support configurations not delivered with the product. The example configuration has only been tested in my lab environment and is provided as an example only. If you decide to use the information in this blog to configure support for WebADI, please test thoroughly before using in production. These are considered custom objects and would only be supported by Quest to the same extent in which Oracle supports your customization to EBS.
Jaeckel, C. (2009,2010). Oracle E-Business Suite Desktop Integration Framework Developer's Guide. Retrieved from Oracle: https://docs.oracle.com/cd/E18727_01/doc.121/e15877/title.htm
Wang, M., & Farrington, R. (2007, December). Oracle® ApplicationsSystem Administrator's Guide - ConfigurationRelease 12. Retrieved from Oracle: https://docs.oracle.com/cd/B40089_10/current/acrobat/120sacg.pdf