Correct version of JDeveloper for OA Extensions to use with E-Business suite 11i to R12.x

As the usage of JDeveloper has increased developing OA Extensions to ADF Faces, we need to understand which version of JDeveloper is to be installed depending on our need.

All the details of which version of JDeveloper to use is explained in ORACLE metalink note: 416708.1

Print This Post

Importing Supplier Bank Accounts using Supplier Open Interface

In R12, You can create Supplier Bank Accounts and associate them to a Payee during Supplier Open Interface Program.

Follow below steps:
1. Load Supplier & Supplier Sites Data to the respective Interface Tables AP_SUPPLIERS_INT and AP_SUPPLIER_SITES_INT.
2. Load the respective Suppliers bank Accounts Data to the ‘IBY_TEMP_EXT_BANK_ACCTS’ Table
3. Run the Suppliers Open Interface Import

In R12, While validating/creating Supplier and Supplier Sites Data and inserting to AP and HZ Tables, a Payee is created in to the IBY Table. If the Payee is created successfully then a check is made for any related rows in ‘IBY_TEMP_EXT_BANK_ACCTS’ Table and an IBY API is called to create a Bank Account and associate it to the respective Payee.

Following conditions are to be met for successful import of Bank Account:
- Suppliers
IBY_TEMP_EXT_BANK_ACCTS.calling_app_unique_ref1 = AP_SUPPLIERS_INT.vendor_interface_id
-Supplier Site
IBY_TEMP_EXT_BANK_ACCTS.calling_app_unique_ref2  = AP_SUPPLIER_SITES_INT.vendor_site_interface_id

Note:  Bank and Bank Branch should have already been defined in the System for the successful Bank Account Import.

Print This Post

Customizing: POR_CUSTOM_PKG

POR_CUSTOM_PKG is an Oracle standard package to allow Customization at the Requisition header, line and Distribution levels, its located at $ICX_TOP/patch/115/sql/PORCUSTB.pls

Following are the profiles related to POR_CUSTOM_PKG to enable the Customizations respectively,

POR: Enable Req Header Customization
POR: Enable Requisition Line Customization
POR: Enable Req Distribution Customization

For the profile changes to have an impact, Apache Bounce is mandatory after they have been changed.

The Custom package has 3 major procedures CUSTOM_DEFAULT_REQ_HEADER, CUSTOM_DEFAULT_REQ_LINE, and CUSTOM_DEFAULT_REQ_DIST referring to a Requisition header, Line and Distributions, where the customization can be implemented.

These procedures are called only when a NEW Requisition Headers, lines and Distributions are created rather than an UPDATE performed on them.

The POR_CUSTOM_PKG can be modified to default Descriptive Flexfield values from header to Line and Distribution and to update other values on Requisitions.
To enable the customization, the user has to navigate to the Page where the Requisition DFF value is active and Requisition Header DFF Additional header Information, Requisition Line DFF Additional Line Information, and Requisition Distribution DFF Additional Distribution Information on the Checkout page

Print This Post

Missing Custom integrators in Create Document in R12

Custom Integrator not available to Create Document is due to Function Security as per changes in R12.

You will be able to view all the Custom Integrators when you set the Profile option 'BNE Allow No Security Rule' to ‘Yes’, but the recommended Value for this Profile option is ‘No’.
                             
                                

To be able to view the Custom Integrator in Create Document, you will have to Add a Function ‘Desktop Integration - Create Document (Code: BNE_ADI_CREATE_DOCUMENT)’ to the Custom Integrator. And also make sure that the Function is added to any Custom Responsibility Menu you want to access it from.

Print This Post

AD_DD Package: Oracle Table Registration

Custom Application Tables are registered using a PL/SQL routing in the AD_DD Package

You only need to register the Table if you are to use them with Oracle Alerts or Flexfields. You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables should you later modify your tables.

If you alter the table later, then you may need to include revised or new calls to the table registration routines. To alter a registration you should first delete the existing registration, then reregister the table or column.

You should delete the column registration first, then the table registration.

The AD_DD API does not check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API.

You need not register views.

Procedures in the AD_DD Package:

procedure register_table (p_appl_short_name in varchar2,
                                         p_tab_name             in varchar2,          
                                         p_tab_type               in varchar2,            
                                         p_next_extent          in number default 512,
                                         p_pct_free                in number default 10,
                                         p_pct_used               in number default 70);

procedure register_column (p_appl_short_name in varchar2,
                                             p_tab_name   in varchar2,
                                             p_col_name   in varchar2,
                                             p_col_seq    in number,
                                             p_col_type   in varchar2,
                                             p_col_width  in number,
                                             p_nullable   in varchar2,
                                             p_translate  in varchar2,
                                             p_precision  in number default null,
                                             p_scale      in number default null);

procedure delete_table  (p_appl_short_name in varchar2,
                                        p_tab_name             in varchar2);

procedure delete_column (p_appl_short_name in varchar2,
                                           p_tab_name             in varchar2,
                                           p_col_name             in varchar2);




Example of Using the AD_DD Package:

Here is an example of using the AD_DD package to register a flexfield table and its columns:

EXECUTE ad_dd.register_table('FND', 'CUST_FLEX_TEST', 'T', 8, 10, 90);
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'APPLICATION_ID', 1, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ID_FLEX_CODE', 2, 'VARCHAR2', 30, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATE_DATE', 3, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATED_BY', 4, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN', 5, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN2', 6, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SET_DEFINING_COLUMN', 7, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SUMMARY_FLAG', 8, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ENABLED_FLAG', 9, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'START_DATE_ACTIVE', 10, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'END_DATE_ACTIVE', 11, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT1', 12, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT2', 13, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT3', 14, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT4', 15, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT5', 16, 'VARCHAR2', 60, 'Y', 'N');


Print This Post

Oracle SLA: Supporting References in R12

Using Support references is an implementation time approach

In 11i additional data is captured using Descriptive Flex fields, but in R12 this feature is enhanced to include additional information at the Event Class level and passed from the Subledgers AP/AR/GL etc., Sources are assigned to the Supporting References to refer to the Supporting References details at the Line Level.

SLA -> Accounting Methods Builder(AMB) -> Journal Entry Setups -> Supporting references

Click on the Create Button and you can define the Supporting references to your need.

You can define maximum of five Supporting Reference details.

Suppose you want to update a Supporting reference, Query the Supporting Reference and click on pencil icon in the Actions section.

These supporting References are stored in XLA_AE_LINES_ACS and relates to XLA_AE_LINES with ae_header_id/ae_line_num.


Print This Post
 

Migrating Web ADI template from one Instance to another

Following are the components that are associated to the Web ADI that can be migrated from one instance to another:

• Integrators
• Layouts
• Mappings
• Contents

Integrators:

Identify the Integrator to migrate as below,

select integrator_code, application_short_name
  from apps.bne_integrators_vl  bnv
      ,apps.fnd_application_vl  fav
 where bnv.user_name = &integrator_name
   and fav.application_id = bnv.application_id;

For the Integrators, you can

Download the Integrator:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct INTEGRATOR_LDTFILE.ldt BNE_INTEGRATORS INTEGRATOR_ASN="appl_short_name" INTEGRATOR_CODE="integrator_name"

Upload the Integrator:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/patch/115/import/bneintegrator.lct INTEGRATOR_LDTFILE.ldt

Layouts:

Identify the Layout to migrate as below,

select layout_code
  from apps.bne_layouts_vl
 where user_name = &integrator_name;

For the Above Layouts, you can

Download the Layout:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnelay.lct LAYOUT_LDT.ldt BNE_LAYOUTS LAYOUT_ASN="appl_short_name" LAYOUT_CODE="Layout_code"

Upload the Layout:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnelay.lct LAYOUT_LDT.ldt

Mappings:

Identify the Mapping to migrate as below,


select mapping_code
  from apps.bne_mappings_vl
 where integrator_code = &integrator_code

For the Above Mappings, you can,

Download the Mapping:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnemap.lct MAPPING_LDT.ldt BNE_MAPPINGS MAPPING_ASN="appl_short_name" MAPPING_CODE="Mapping_code"

Upload the Mapping:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnemap.lct MAPPING_LDT.ldt

Contents:

Identify the Contents to migrate as below,

select content_code
   from apps.bne_contents_vl
  where integrator_code = &integrator_code

For the Above Contents, you can

Download the Contents:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecont.lct CONTENT_LDT.ldt BNE_CONTENTS CONTENT_ASN="appl_short_name" CONTENT_CODE="Content_code"

Upload the Contents:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnecont.lct CONTENT_LDT.ldt

Print This Post

Web ADI: Custom Integrators Update Icon

The Update Icon for Custom Web ADI's is greyed out, reason for this is that the Integrator may be associated to the ORACLE Standard User.

For the Custom Web ADI's to be updateable, the Integrators Source should be set to 'C'(Custom) rather than 'O'(Oracle).

If the Custom integrators source is Set to 'O' - Oracle in the table BNE_INTEGRATORS_B , please reset it to 'C' - Custom to make it updateable as below,

update BNE_INTEGRATORS_B
   set SOURCE = 'C'
 where APPLICATION_ID = &application_id
   and INTEGRATOR_CODE = &integrator_code;

Print This Post

Unable to access the 'Web ADI - Update Interface Column Prompts' integrator

The Integrator 'Web ADI - Update Interface Column Prompts' is designed for HR and if HR is not installed/implemented then there is a chance that you will not be able to find it in the list.
To access the integrator, you have to add the Function ‘HR_ADI_CAG_INTERFACE_COLS’ to the Menu ‘Desktop Integration menu’

  Print This Post

Oracle Web ADI: Custom Integrator Prompts Change

If you are creating a custom Integrator, and most of the times you would not like the Default prompts it keeps from the API parameters, here is the process to change the Prompt names.

Login to Application as Web ADI Responsibility

Desktop Integrator --> Create Document

Select the Integrator ‘Web ADI – Update Interface Column Prompts’ and click on the Next Button as below,

 

Select the specified Excel version and Click on next Button,

 

Select any specific Integrator, for which you want to change the prompts, Here I have selected,

Application: PAY
Integrator: PAY RI – Create US Federal Taxability Rules For Earnings
Language: US

 

Click Continue and Create a Document

 

This Opens the Document with the Prompt Details Above, Left etc. Here you Can update the prompts and upload the changes.

The Changes would then be reflected in the respective Documents.


 

Print This Post

Oracle Web ADI: Creating a Custom Integrator

Just to give you a quick overview of Oracle Web ADI(Oracle Web Application Desktop Integrator), it brings E-business suite functionality to desktop where familiar Microsoft, Excel, Word and Project applications that can be used to complete your Oracle E-Business Suite tasks.

The Oracle Web Applications Desktop Integrator integration with Microsoft Excel enables you to bring your Oracle E-Business Suite data to a spreadsheet where familiar data entry and modeling techniques can be used to complete Oracle E-Business Suite tasks. You can create formatted spreadsheets on your desktop that allow you to download, view, edit, and create Oracle E-Business Suite data. Use data entry shortcuts (such as copying and pasting or dragging and dropping ranges of cells) or Excel formulas to calculate amounts to save time.

Here in this topic I will give some quick steps to create a Custom Integrator:

Firstly, with Web ADI you get the following 2 Standard responsibilities:

1. Desktop Integration Manager

2. Desktop Integrator

 

Desktop Integration manager involves creating/Managing Integrators and parameters

Desktop Integrator lets you create Document, layout, Mapping, Setup options etc.,

Now to create a Custom integrator you need to,

a. Create an integrator
b. Create a Layout

Desktop Integration Manager - > Create Integrator

Define the Integrator as below, and make sure to check the checkbox across ‘Display in Create Documents Page’ and click on the Next Button

 

 And now define the Interface Name, here I am using the Interface Type of Function, and you can return the Error Information from the Function. The Package and Function names are provided. The Function I have used is a Sample insert into a Table function as shown below,

create table test_adi(sno number, sname varchar2(2000));

create or replace package test_adi_pkg as
function test_adi_func(p_sno    in number, p_sname in varchar2) return varchar2;
end test_adi_pkg;
/

create or replace package body test_adi_pkg as
function test_adi_func(p_sno    in number, p_sname in varchar2) return varchar2
is
begin
insert into test_adi values(p_sno, p_sname);
return 'Y';
end;
end test_adi_pkg;
/

Click on the Apply button and the Next button after until you reach the Last Page and Submit the Definition as shown below,

    

 Now you have defined an Integrator and the next step is to Define a Layout to insert data in to the Integrator

Desktop Integrator --> Create a Layout




As shown below select the Test ADI Integrator and click on Go button as there are no Layouts defined it does not display any Layouts.

Now click on create Button, and the next Page asks you the Layout Name and the Number of Headers required for the Spreadsheet,
 

Select the number of Headers and click on next Button and you can now select which fields to appear at Header Level and which fields to appear at Line level. And there are different Types you can use. 

 Now in the Layout Screen you can do all necessary Layout changes, such as you can make a field Read Only, order them, Select the Prompt name etc.,

And once you click on the Apply button it saves the Layout and the spreadsheet is now ready to be entered data for.

And now we will go to the Final Step of creating a document,

Desktop Integrator -> create Document

Select the Integrator and the Layout defined and click on the create Document button to create an Excel Spreadsheet.

 Enter data for SNO and SNAME fields defined and upload them to Oracle Data Table.

An Excel document would be created with name Wen ADI(n).xls

Once the data is entered you can initiate the Upload Macro by clicking Oracle -> Upload

And the successful upload would like as shown below with the number of rows uploaded

 The data is now inserted to the underlying Table as shown below.

 Custom Integrator not only loads the data, you can do much more things to process the data by initiating a concurrent program from the API etc.,

Print This Post

FND: Personalization Seeding Mode

In Oracle Applications Framework, if the Profile option FND: Personalization Seeding Mode is set to NULL/No,it would not allow Standard Personalizations to be updated.

Suppose for this case, it does not allow you to update the Personalization at Site Level for the Page /oracle/apps/icx/por/req/webui/CheckoutUpdateSinglePG as shown below,

Now after setting the Profile option FND: Personalization Seeding Mode to Yes, the Personalization can be updated as below,

Print This Post