Procedure Call using Form Personalization

How to Launch a procedure using form personalization ?

 

Form >> Menu >> Help >> Diagnostics >>Custom Code >> Personalize

 

Create a personalization

Event–        Any trigger

Action –               Built in >> Execute procedure >>

 

 

Syntex –

 

='begin

xxxxx_pkg.call_program('''||${item.<BLOCK_NAME>.<FILED_NAME>.value}||''', '''||${item.<BLOCK_NAME>.<FILED_NAME>.value}||''',1,’Test’);

end'

 

 

Description –

Procedure declaration

XXXX_PKG.call_program(pramaetr1 VARCHAR2,

   parameter2 VARCHAR2,

  parameter3 NUMBER,

  parameter4 VARCHAR2);

 

Parameter1 & 2 , value will be passed form form field.

Parameter3, constant value 1 is being passed.

Parameter4, constant value ‘Test’ is being passed.

                                 

 

LOV in WebADI - Component

Interface property can be used to set the value set. This will provide list option in excel sheet and will have limitation on values.

Usages of component will provide standard EBS LOV option. Steps are as below -

 

 

1)      Desktop Integration Manager (R) >> Manage Component >> Create Component

Provide display name, internal name and application.

Component Type:

LOV-Table -  

Ø  SQL details will be provided into interface column properties

Ø  Component name should be provided in interface column properties

 

LOV-JAVA (Please click here)

Complete component details and apply.

Table_column-alias – Interface column name

Table_column – Value set column name (Meaning)

Table-select-column - Interface column name

 

 

Go to interface >> properties window and provide the component created.

 

 

Make Dependent VS Enterable Without Passing Parent VS

While creating dependent value set in EBS, we found dependent (child) field is greyed (disabled) at beginning until parent field gets some value.

Requirement is to enable child field from beginning even though parent field does not have any value passed.

Supplier site is dependent on supplier and hence is disable initially and get enabled once supplier name will have value.

Above will be achieved by adding below in where condition –

WHERE and ORDER BY

Vendor_id = :$FLEX$.Supplier_test

Solution dependent VS can be enabled since beginning by modifying the above WHERE condition as below –

WHERE and ORDER BY

Vendor_id = :$FLEX$.Supplier_test:NULL

This will enable site value set.

From : Dixidha

More than 2 column values in LOV

To enable LOV on a parameter, a Table value set is associated with parameter field. While running LOV displays Value and Meaning values in LOV window.

Requirement is to add extra additional fields in LOV.

Fox example if we have supplier site LOV along with site code and site name, supplier name will add extra advantage to pick the right site if we have same name sites available.  

Solution is additional columns option available on value set definition form.

Syntax

Column_name1 “Alias” (*),Column_name2 “Alias” (*),….

                                                                                                                                                                                                                       

 

Craeted VS supplier site and added below in additional columns

 segment1 “VendorName”(*),vendor_id ”Vendor_id”(*)

Supplier LOV shows vendor name, vendor number and vendor id.

From : Dixidha

Initialize Apps Context in Java file while creating dependent VS in WEBADI

Oracle provides standard class which can be used to initialize the apps context (set policy context) in java files if required –

 

Class Name – PaMoInit

Method – MoInit

Location - oracle.apps.pa.webadi.utilities

 

Call –

PaMoInit.MoInit(paramBneWebAppsContext);

 

 

This can be simply import if we are in PA module. Please check if same exist for any other modules otherwise creates below method and register (place) at some places.

 

 

package oracle.apps.pa.webadi.utilities;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.SQLException;

import oracle.apps.bne.exception.BneException;

import oracle.apps.bne.framework.BneWebAppsContext;

import oracle.apps.fnd.common.VersionInfo;

 

public class PaMoInit

{

  public static void MoInit(BneWebAppsContext paramBneWebAppsContext) throws SQLException, BneException

  {

    Connection localConnection = paramBneWebAppsContext.getJDBCConnection();

    String str = "";

    CallableStatement localCallableStatement = null;

 

    StringBuffer localStringBuffer = new StringBuffer();

 

    localStringBuffer.append("begin PA_MOAC_UTILS.INITIALIZE; end;");

    localCallableStatement = localConnection.prepareCall(localStringBuffer.toString());

    localCallableStatement.execute();

    localCallableStatement.close();

  }

}

 
 

No Parameter List Definition at Parameter list id

 

Issue Cause – Such issue will occur only when integrator is being created by modifying existing integrator LDT file.

Integrator definition section contains upload (UPL) and import (IMPORT) and import list details. Integrator

 

This error occurs if defined details at integrator level do not have correct definition-  

 

IMP_TRANS_GROUP in XXXXX schema is one of the import list associated to integrator.

 

Import list definition is as below -

 

Seems the definition is also defined in XXXXX schema only still we got above error.

 

Root Cause – There is a difference in STRING_VALUE.

275:IMP_TRANS_GROUP , this is not a simple string this is APPLICATION_ID:IMPORT_List Name

 

Solution –

STRING_VALUE = "2000:IMP_TRANS_GROUP"

Will be the right value since XXXXX, application is 20000.

 

Change the LDT and upload, issue will be resolved.

 
 

Required upload column was not in upload and has no default value defined

Error -

Getting error “Required upload column was not in upload and has no default value defined” while uploading the WebADI.

 

Cause –

There will be one NOT NULL column in interface and passed no values in upload.

 

Resolution -

Either provide the value or define the default values.

 

Select List of Values option (put the cursor on error message in excel) from Menu ( Oracle Add in).

 

This will help to know the problematic column name.

 

 

Remove Control M Character from unix file

Use Dos2unix command to remove control M characters from any shell script or prog files.

Syntex
Dos2unix <file_name>

Example
Dos2unix Sandeep.sh
Dos2unix Sandeep.prog  

Value for the Flexfield Segment does not Exist in the Value Set

 

 

Defining a value set on any DFF will disallow to search records having non eligible (out of value set range) segment values.

 

A good example of this is to create a DFF of segment project task name. Initially associate a value set of Table type which shows all tasks from pa_tasks_all.

We create some records using assigned DFF. After sometime update value set definition to restrict disabled/end dated tasks.

 

Now search for records which has been created earlier and having disabled tasks assigned. Oracle form will throw below error message –

 

 

Approach – Try restricting the validation query while search.  

 

Solution – We faced similar issue and did below steps to overcome issue –

           1) Created a profile (XXXXX_SANDEEP_PRF) and set value as N

           2) Updated the value set query as

 

           Current condition –

           WHERE   sysdate between start_date and nvl(completion_date,sysdate)

 

           Updated Condition –

            WHERE  (sysdate between start_date and nvl(completion_date,sysdate))

                               OR

                               (nvl(:$PROFILES$.XXXXX_SANDEEP_PRF,'Y')='N')

           3) Use form personalization to change the profile value in cache in search and edit mode

           4) Set the profile value =Y, when in Edit mode. This will fail the second condition and only first 

                     condition will be valid and so only active task will appear in LOV.

          Set the profile value = N when in search mode. First condition will be failed since task is  

              end dated but profile condition will pass and so record will appear without error.

            5)  Did not try this in same form. We have created 2 form functions ( Entry (profile Y) and 

                 Enquiry (profile N) mode)

 
 

 

Change the profile value at run time in Core Forms

We have one custom profile which value is set at different levels. Need is to change the value while working on core forms.

                                                

This can be achieved using form personalization. Form personalization has 4 different actions type.

    1)    Property

    2)    Message

    3)    Builtin

    4)   Menu

 

Builtin provides capability to change the profile value during run time in cache.

 

 

This will change the profile value in cache during run time J