Simple Custom WebADI Integrator with 12.1.2 Desktop Integration Manager

Desktop Integrator (WebADI) Integrates Oracle Ebusiness suite with desktop applications like Excel and Word. Starting from Release 12.1.2, Oracle introduced new responsibility 'Desktop Integration Manager' which allows developers to create custom web adi using UI.

Below are the simple steps to create.
Step1: Create custom table and sequence(In case if you want to pass sequential number to any columns)


create table xxsw_testadi_tbl
( seq_no NUMBER,
  item_number VARCHAR2(240),
 pos_uom VARCHAR2(1),
 status VARCHAR2(20)
);

create sequence xxsw_test_seq;

Step2: While inserting records from web adi either an API can be used to perform any business validation or insert directly into table. Assuming that we use API in most cases. Code here to create sample package procedure. Note that for every record in your Excel, this package will be executed.



CREATE OR REPLACE PACKAGE xx_testadi_pkg
AS
   PROCEDURE LOAD_ADI (p_seq_no        IN     NUMBER,
                       p_item_number   IN     VARCHAR2,
                       p_pos_uom       IN     VARCHAR2,
                       p_status        IN     VARCHAR2,
                       errbuf             OUT VARCHAR2,
                       retcode            OUT NUMBER);
END xx_testadi_pkg;
/
CREATE OR REPLACE PACKAGE BODY XX_TESTADI_PKG
AS
   PROCEDURE LOAD_ADI (p_seq_no        IN     NUMBER,
                       p_item_number   IN     VARCHAR2,
                       p_pos_uom       IN     VARCHAR2,
                       p_status        IN     VARCHAR2,
                       errbuf             OUT VARCHAR2,
                       retcode            OUT NUMBER)
   AS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
     --Business Validations can be added here
      INSERT INTO xxsw_testadi_tbl
           VALUES (p_seq_no,
                   p_item_number,
                   p_pos_uom,
                   p_status);

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         raise_application_error (
            -20116,
            'Error - Cannot insert/update size code mappings');
   END LOAD_ADI;
END xx_testadi_pkg;



Step3: Create a Form Function
Navigation --> Application Developer --> Application --> Function

Function: XXSW_TEST_ADI
User Function Name: XXSW_TEST_ADI
Type: SSWA servlet function
HTML Call: BneApplicationService
Save and Close
Note: Parameter has to update with Integrator code and Layout. Will come back here later.

Step4: Add the Function to Menu. Save and Close

Step5.1: Define integrator.
Goto Desktop Integration Manager --> Create Integrator and Enter values

Integrator Name: XXSW_TEST_ADI

Internal Name: XXSW_TEST_ADI

Application: Custom Application

Enabled: Yes

Display in Create Document Page: Yes(Check Box)

Add these two functions: 

--> Desktop Integration - Create Document (BNE_ADI_CREATE_DOCUMENT)

--> XXSW_TEST_ADI

Click NEXT

Step5.2: Define Interface

Interface Name: XXSW_TEST_ADI_INT
Interface Type: API - Procedure
Note: Table and API-Function are also available here. Since we wrote a package procedure, use the API-Procedure option
Package Name:XX_TESTADI_PKG
Procedure/Function Name: LOAD_ADI
API Returns: Error Message
Note: FND Message Code also available here. In Order to use this, create message in application developer and use it in package
Note: Make sure you click on 'Apply' before clicking on 'Next'

Step5.3: Click the 'Select' Radio Button of Interface XXSW_TEST_ADI_INT and wait for few seconds
in order to refresh the page

--> This will display all the IN and OUT parameters of the package procedure. Delete the
      OUT attributes using Delete button
--> Change the Prompt if Required.
--> Click on Update and Select 'Not Null' check box in order make mandatory during upload
Click NEXT twice to go Step 4.
Step5.3: Define Uploader
Uploader: From Template
Uploader Name: XXSW_TEST_WEBADI - Uploader
Upload Parameters:  For bne:Validation, enable the check box for Displayed, Enabled and Required. For rest of them Uncheck

Skip the Importer Section
Click  Submit.

Step5.4: Define Layout

Navigation: Desktop Integration Manager -> Manage Integrators -> Define Layout -> Select the Integrator

--> XXSW_TEST_ADI --> Click GO and CREATE

Layout Name: XXSW_TEST_ADI_LAYOUT
Number of Headers:1
NEXT
Change the Placement to 'Line'
In order to generate a sequence number for column SEQ_NO, write sql in Default
Value field and choose SQL as Default Type
select xxsw_test_seq.NEXTVAL from dual

APPLY

Step 6: Update Function Parameters
Run the below SQL Query:
SELECT INTEGRATOR_APP_ID || ':' || INTEGRATOR_CODE INTEGRATORS, APPLICATION_ID || ':' || LAYOUT_CODE LAYOUTS FROM BNE_LAYOUTS_B WHERE INTEGRATOR_APP_ID = 20003 ORDER BY 1, 2

From the Results of above sql, copy the Integrator and layout to below string:
bne:page=BneCreateDoc&bne:integrator=20003:XXSW_TEST_ADI_XINTG&bne:layout=20003:XXSW_TEST_ADI_LAYOUT

Goto Application Developer --> Application --> Function --> Query for XXSW_TEST_ADI
function --> 'Form' tab, Enter the above string and SAVE

Setups are completed now. Its time to test

Multi-Record Update using Forms Personalization

One of my client has an interesting requirement, In Sales order form when user changes the ORDER pricing date then it needs cascade with ALL existing LINES pricing date.

I tried a traditional way by using 'When New Block Instance' or 'When New Record Instance' for block LINE and added an action.

 Its working good only when user enter/update the sales order line.

Cascading is not happening when there are multiple lines exist and user changing the pricing date at header
and closing the form (with out going to Line block).
In order achieve that, used BuiltIn Type to 'Execute a Procedure'.

In Procedure, used API oe_order_pub to update lines.

Thanks, Vijay

Simple OAF Personalizations: How to restrict a field length

Lets see an example to restrict field length using OAFramework in Oracle Applications.
In order to be able to use personalization set the profile profile 'Personalization Self-Service Defn' to Yes.I would strongly recommend to set this only at USER Level. This profile will show the  "Personalize" link on every group on any OAF page.

To restrict the Customer Name to 50 characters in Customer standard form, below are steps.

Click on Personalize "Customer Information" link

Under Personalization Structure, Click on Personalize edit button, for Organization Name field

In Personlization Properties section, change the 'Maximum Length' from 'Inherit' to 50 as shown

Click on Apply and return to application. This personalization will ensure that users cannot enter more than

50 characters for Organization Name field. You set this property at various level like Site, Application, Responsibility and User Levels.

 

Passing NULL Values to Oracle API's

Oracle API's (mostly TCA related) always retains the current value from that table(column) if there is no value passed. Also it will not accept values if explicitly passed like NULL or single quotes ''.

In order to nullify an existing value Oracle has provided the below variables.
FND_API.G_MISS_NUM for NUMBER datatype
FND_API.G_MISS_CHAR for VARCHAR datatype
FND_API.G_MISS_DATE for DATE datatype

Additionally, the same FND_API will provide the values for any standard API statuses as well.

FND_API.G_RET_STS_SUCESS FOR success
FND_API.G_RET_STS_ERROR FOR ERROR
FND_API.G_STS_UNEXP_ERROR FOR ANY unexpected ERROR.

Since we are at API's topic wanted to add the standard API used to retrieve the message stack.
if any API returns errors/warnings. Most of the standard API will have three common OUT variables
x_return_status -- Returns Sucess/Error/Warning
x_msg_data -- Returns error message stack
x_msg_count --Returns messages count.



IF (v_return_status <> fnd_api.g_ret_sts_success)
      THEN
         FOR i IN 1 .. v_msg_count
         LOOP
            fnd_msg_pub.get (p_msg_index          => i,
                             p_data               => v_msg_data,
                             p_encoded            => fnd_api.g_false,
                             p_msg_index_out      => v_msg_index_out
                            );
            FND_FILE.PUT_LINE(fnd_file.log,v_msg_data);
         END LOOP;

END IF;

Let me know any comments or questions.