25 Using the Advanced Features of Key Flexfields

This chapter discusses the development activities for taking advantage of key flexfield partial usages, code-combination filters, and other advanced features.

This chapter includes the following sections:

• Section 25.1, "Introduction to Advanced Features of Key Flexfields"

• Section 25.2, "Using Key Flexfield Advanced Features in Reference Mode"

• Section 25.3, "Completing the Development Tasks for Key Flexfields in Secondary Mode"

• Section 25.4, "Working with Code-Combination Filters for Key Flexfields"

25.1 Introduction to Advanced Features of Key Flexfields

Key Flexfields have advanced features like code combination filters and reference mode. The criteria for filtering the list of code combinations that can be referenced in a given combinations table are known as code combination constraints. Combination constraints are view object properties and are not applied on any entity objects.The type of usage when there is no direct relationship between the product table and the key flexfields combinations table is known as secondary storage.

25.2 Using Key Flexfield Advanced Features in Reference Mode

Key flexfield advanced features include code combination constraints, programmatic access to segment labels, making key flexfields available for use in Oracle Business Intelligence, and working with flexfields from a worksheet using ADF Desktop Integration.

25.2.1 How to Define Code Combination Constraints

Code combination constraints are criteria for filtering the list of code combinations that can be referenced in a given combinations table. While the set of code combinations in the table is not changed, each reference table (product table with foreign key references to these code combinations) can have its own associated code combination constraints.

For example, the key flexfield MTL_SYSTEM_ITEMS has a Purchasable flag, which can be set to the value Y or N. You can implement an extra WHERE clause on the Oracle Purchasing product view object that enables Order Management to restrict the displayed items to only those with Purchasable set to Y.

Code combination constraints are applied in the following situations:

• When an end user opens a key flexfield popup window to search for a code combination.

• When the code-combination ID attribute of a view object for a reference table is set programmatically.

Code combination constraints are not applied when an existing foreign key reference to a code combination is resolved into individual segments (or a concatenated string) for display.

Combination constraints are view object properties and are not applied on any entity objects.

You create a view accessor to define a code combination constraint. You can define the following types of code combination constraints:

• Extra WHERE clause

• Validation date

• Validation rules

• Dynamic combination creation allowed

25.2.1.1 Creating a View Accessor to Define a Code Combination Constraint

In a key flexfield ADF Business Components model, the code combination constraints are defined in a view object that references the code combinations (the reference view object). Although these constraints are, in a way, validation rules for code combinations, they are not ADF Business Components validators.

You define code combination constraints as bind parameter values in a view accessor. The name of this view accessor is derived from the name of the view link accessor to the key flexfield view object for which you want to constrain code combinations.

To define a code combination constraint: 

1. Open the foreign key view object that references the code combinations.

2. Click the Add icon in the View Accessors section to display the View Accessors dialog.

3. Create a view accessor to the key flexfield's base view object, as shown in Figure 25-1.

   Figure 25-1 Code Combination Constraint View Accessor

   
   

   The destination of the view accessor is the key flexfield's base view object The name of the view accessor must be derived from the name of the view link accessor to the key flexfield view object, and must take the following form:

   viewlinkaccessornameConstraints
   

   For example, for a view link accessor named AcctKff, Figure 25-1 shows the accessor name AcctKffConstraints.

4. Edit the view accessor to define the bind parameter values, as shown in Figure 25-2.

   Note:

   You do not need to select any view criteria for this activity. Only the bind parameter values are needed to define a code combination constraint.

   If you do not see any bind parameters, it is likely that you have just re-created the business components and overwritten the old ones. You can close the application and remove it from Oracle JDeveloper. When you open the application again, JDeveloper should load the latest definitions, including the bind parameters.

   Figure 25-2 Code Combination Constraint Bind Parameter Values

   
   

   There are four types of code combination constraints. To apply a constraint type, provide a value for the appropriate bind parameter for the constraint type as shown in the following list. If no value is provided, that constraint type is not enabled.

   • Extra WHERE clause: This constraint is invoked with the bind parameter Bind_ExtraWhereClause. You can also incorporate the predefined bind parameters BindVar0 through BindVar9.

     For information about how to provide a value for this bind parameter, see Section 25.2.1.2, "Constraining Code Combinations by an Extra WHERE Clause."

   • Validation date: This constraint is invoked with the bind parameter Bind_ValidationDate.

     For information about how to provide a value for this bind parameter, see Section 25.2.1.3, "Constraining Code Combinations by Validation Date."

   • Value attribute validation rules: This constraint is invoked with the bind parameter Bind_ValidationRules.

     For information about how to provide a value for this bind parameter, see Section 25.2.1.4, "Constraining Code Combinations by Validation Rules."

   • Dynamic combination creation allowed: This constraint is invoked with the bind parameter Bind_DynamicCombinationCreationAllowed.

     For information about how to provide a value for this bind parameter, see Section 25.2.1.5, "Enabling or Disabling Dynamic Combination Creation for a Specific Usage."

   Edit only the bind parameters that you need, and leave the others blank. You can use Groovy expressions as bind-parameter values. This means that the constraints can come indirectly from a view attribute, the view object, or a Java method.

   Note:

   You can ignore the Row-level bind values exist option, because the frequency of evaluation of bind parameters is predetermined, as follows:

   • View object level (evaluated once)

     • Bind_ExtraWhereClause

     • Bind_ValidationRules

   • Row level (evaluated every time)

     • BindVar0 through BindVar9

     • Bind_ValidationDate

     • Bind_DynamicCombinationCreationAllowed

25.2.1.2 Constraining Code Combinations by an Extra WHERE Clause

You can use the view accessor's Bind_ExtraWhereClause parameter to filter the list of code combinations that can be referenced in a given combinations table. The extra WHERE clause is appended to the existing WHERE clause of the key flexfield view object.

To set the Bind_ExtraWhereClause parameter: 

1. Create the view accessor and open it for editing as described in Section 25.2.1, "How to Define Code Combination Constraints."

2. In the Edit View Accessor dialog, set the Bind_ExtraWhereClause value.

   The extra WHERE clause can use bind parameters. The value of Bind_ExtraWhereClause should be a SQL fragment that may contain references to columns of the combinations table, or the predefined bind parameters.

   To refer to the combinations table, use ${COMBINATION_TABLE}]; for example, ${COMBINATION_TABLE}.MY_COLUMN.

   To refer to one of the 10 predefined bind parameters, BindVar0 through BindVar9, use a colon and the bind parameter name; for example, :BindVar3.

   The following is an example of an extra WHERE clause code combination constraint as a SQL expression:

   (:BindVar0 IS NULL) OR (${COMBINATION_TABLE}.MY_COLUMN = :BindVar0)
   

   You can also express this as a Groovy string constant. Be sure to escape the dollar sign with a backslash:

   "(:BindVar0 IS NULL) OR (\${COMBINATION_TABLE}.MY_COLUMN = :BindVar0)"
   

25.2.1.3 Constraining Code Combinations by Validation Date

You can use the view accessor's Bind_ValidationDate parameter to filter the list of code combinations that can be referenced in a given combinations table. If you provide a value for Bind_ValidationDate, then this validation date is used instead of the current database date when searching for a code combination. The code combinations returned are those that are active on the specified date.

If a code combination's start_date_active attribute is NULL, it is considered to have always been active in the past, up to its end_date_active date. If a code combination's end_date_active attribute is NULL, it is considered to be active starting from its start_date_active date indefinitely into the future.

Note:

Note that a date constraint is always required when searching for a code combination. If you do not supply a validation date, the current database date will be used.

To set the Bind_ValidationDate parameter: 

1. Create the view accessor and open it for editing as described in Section 25.2.1, "How to Define Code Combination Constraints."

2. In the Edit View Accessor dialog, set the Bind_ValidationDate value.

   The value of Bind_ValidationDate should be a normalized java.sql.Date object; that is, the hour, minute, second, and millisecond should be set to zero. You can use the method oracle.apps.fnd.applcore.oaext.model.OAUtility#getSQLDate to normalize the date.

   One way to construct a normalized date for testing purposes is to use java.sql.Date.valueOf(String s) with the date as a literal string in the form yyyy-mm-dd.

   In a search user interface, the supplied validation date also affects the list of values of a segment. For example, the end user may pick a value for a segment from a list of values, then use the segment value to search for a code combination. The list of values of the segment will be constrained by the supplied validation date.

25.2.1.4 Constraining Code Combinations by Validation Rules

You use validation rules to constrain code combinations. The validation rules for a given key flexfield are authored by the product team that owns the flexfield. They are valid only for use as code combination constraints, and should not be confused with other types of validation rules. Validation rules are stored in the flexfield metadata table FND_KF_VRULES and are delivered along with the key flexfield definition. The rule authors are your best source of information about the applicability of the validation rules for a flexfield, and the rule codes you should use to reference them.

You can use the view accessor's Bind_ValidationRules parameter to filter the list of code combinations that can be referenced in a given combinations table. If you provide a value for Bind_ValidationRules, the validation rules are translated into a SQL fragment, and the SQL fragment is appended to the WHERE clause of the key flexfield view object.

Note:

Because key flexfield secondary usages do not include a code combination, and validation rule constraints currently apply only to code combinations, they do not apply in the case of key flexfield secondary usages.

25.2.1.4.1 How to Create Validation Rules

Use the create_vrule(...) procedure from the FND_FLEX_KF_SETUP_APIS PL/SQL package to register a flexfield segment's validation rule. Validation rules apply only to segments that are validated against a list-validated value set. If the segment is validated against a format-only value set, the validation rules are ignored.

Note that when a segment is labeled with multiple segment labels, its validation rules are joined with an AND operator in the WHERE clause.

When the ALWAYS_APPLIED_FLAG is set to Y, the validation rule is always applied, such as when a combination is validated by C or PL/SQL validation APIs or a combination is validated by a business component. When the ALWAYS_APPLIED_FLAG is set to N, the validation rule is applied only when the rule is included in the list of validation rules as an argument to C or PL/SQL validation APIs or as a Bind_ValidationRules parameter as described in Section 25.2.1.4.2, "How to Set the Bind_ValidationRules Parameter."

Because the names of the segment columns that the customer will use for the code combinations are not known during development, you must use the lexical references listed in Table 25-1 to refer to the segment column and value attributes in the rule's WHERE clause. In addition to the lexical references, the FLEXFIELD.VALIDATION_DATE bind variable can be used in validation rule WHERE clauses. No other flexfield bind variables can be used.

Table 25-1 Lexical References

Lexical Type	Lexical Code	Example	Notes
VALUE	VALUE	&{VALUE.VALUE}	Represents the VALUE column in segment lists of values and represents the segment column in combination lists of values.
VALUE_ATTRIBUTE	value attribute code	&{VALUE_ATTRIBUTE.GL_ACCOUNT_TYPE}	Represents the value table value attribute column in segment lists of values and represents the combination table value attribute column in combination lists of values.

For example, if the following WHERE clause were registered as a validation rule for a segment, then the derived SQL query to retrieve the segment's list of values would be similar to Example 25-1, and the derived SQL query to retrieve the combination list of values would be similar to Example 25-2.

GL_AFF_AWC_API_PKG.gl_valid_flex_values(
  :{FLEXFIELD.VALIDATION_DATE}, &{VALUE.VALUE}) = 'Y')

Example 25-1 SQL Query to Retrieve Segment's List of Values

SELECT ...
FROM fnd_vs_values_vl fvvv
WHERE fvvv.value_set_id = :Bind_ValueSetId
AND fvvv.value like :Bind_Value
AND (GL_AFF_AWC_API_PKG.gl_valid_flex_values(
  :Bind_ValidationDate, fvvv.value) = 'Y'))

Example 25-2 SQL Query to Retrieve Combination List of Values

SELECT ...
FROM gl_code_combinations glcc
WHERE glcc.chart_of_accounts_id = :Bind_SIN
AND ...
AND 
(GL_AFF_AWC_API_PKG.gl_valid_flex_values(
  :Bind_ValidationDate, glcc.segment5) = 'Y'))

Tip:

To learn how to generate documentation about using the FND_FLEX_KF_SETUP_APIS PL/SQL package, see Section 24.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

25.2.1.4.2 How to Set the Bind_ValidationRules Parameter

Edit the view accessor's Bind_ValidationRules parameter to specify the validation rules to be applied to constrain the code-combination filters.

To set the Bind_ValidationRules parameter:

1. Create the view accessor and open it for editing as described in Section 25.2.1, "How to Define Code Combination Constraints."

2. In the Edit View Accessor dialog, set the Bind_ValidationRules value.

   The value of Bind_ValidationRules should be a semicolon-separated list of rule codes; for example:

   VALIDATION_RULE1;VALIDATION_RULE2
   

   The validation rules are predefined as part of the key flexfield definition. The supplied list is the list of rules must be applied when searching for a code combination.

   Note the following cautions when constructing this list:

   • The validation rule codes are case-sensitive.

   • Space characters are preserved. For example, "VRULE1; VRULE2" will be parsed into "VRULE1" and " VRULE2" (with a leading space).

   • Unrecognized and unused rules are discarded silently. For example, if you have a validation rule for an optional label, and the label has not been assigned yet in the current flexfield definition, then the rule will be ignored at runtime.

   In a search user interface, the supplied validation rules also affect the list of values of a segment. For example, the end user may pick a value for a segment from a list of values, then use the segment value to search for a code combination. The list of values of the segment will be constrained by the supplied validation rules.

25.2.1.5 Enabling or Disabling Dynamic Combination Creation for a Specific Usage

You can use the Bind_DynamicCombinationCreationAllowed parameter to control the runtime entry of new code combinations for a key flexfield usage. This constraint type takes effect only when the key flexfield allows dynamic combination insertion. For more information, see Section 24.2.4.2, "Enabling Dynamic Combination Insertion."

To set the Bind_DynamicCombinationCreationAllowed parameter: 

1. Create the view accessor and open it for editing as described in Section 25.2.1, "How to Define Code Combination Constraints."

2. In the Edit View Accessor dialog, set the Bind_DynamicCombinationCreationAllowed value.

   The value of Bind_DynamicCombinationCreationAllowed can be TRUE, FALSE, or null.

   By setting this value to TRUE or FALSE, you can control whether your specific usage of the key flexfield allows dynamic insertion even though the key flexfield as a whole is enabled for dynamic insertion. Set the value to TRUE if you want your usage of the key flexfield to allow dynamic insertion. Set the value to FALSE if you do not want your usage of the key flexfield to allow dynamic insertion. Set the value to null to indicate that the key flexfield itself should determine whether dynamic combination insertion is allowed or not.

   If the key flexfield does not allow dynamic combination insertion, this constraint is ignored. Bind_DynamicCombinationCreationAllowed is a row-level bind parameter.

25.2.2 How to Access Segment Labels Using the Java API

Segment labels (previously known as key flexfield qualifiers) that have been assigned to segments by customers can be accessed programmatically. The information can be accessed using the flexfield application module or the flexfield view row.

Example 25-3 is an example of Java code that retrieves segment label information from a deployed flexfield using the flexfield application module.

Example 25-3 Retrieving Segment Label Information Using the Flexfield Application Module

// First find the flexfield application module:
FlexfieldApplicationModuleImpl flexAM = (FlexfieldApplicationModuleImpl)
  rootAM.findApplicationModule("Kff1nAM1");
 
// Find the attributes labeled as "SEGMENT_LABEL_G1" in structure
// "VS_FRM_CHR_ON_CHR".
// If you wish to use a structure instance number, you must further
// cast the application module into KFFApplicationModuleImpl and call
// getStructureCode(long) to find the code.
// For example,
// KFFApplicationModuleImpl kffAM = (KFFApplicationModuleImpl) flexAM;
// String code = kffAM.getStructureCode(12345);
List<AttributeDef> attrs = flexAM.getLabeledAttributes("VS_FRM_CHR_ON_CHR",
                                                       "SEGMENT_LABEL_G1");
for (AttributeDef attr: attrs)
{
  System.out.println(attr.getName());
 
  // You can get the segment code through a static method.
  System.out.println(FlexfieldViewDefImpl.getSegmentCode(attr));
 
  // If you somehow need to construct a WHERE clause using this attribute,
  // this is the identifier you should use.
  System.out.println(attr.getColumnNameForQuery());
 
  // You can find the "column name" defined in the entity.  The column name
  // is typically the database column name.
  System.out.println(attr.getColumnName());
}

Example 25-4 is an example of Java code that retrieves segment label information from a deployed flexfield using the flexfield view row.

Example 25-4 Retrieving Segment Label Information Using the View Row

// This is just for illustration.  In a real application, the 
// flexfield view row should be retrieved through the view link accessor.
ViewObject vo = rootAM.findViewObject("Kff1nAM1.DefaultFlexViewUsage");
vo.executeQuery();
while (vo.hasNext())
{
  FlexfieldViewRowImpl row = (FlexfieldViewRowImpl) vo.next();
  // Given a KFF view row, you can find the labeled attributes through
  // the view def. An empty list is returned if the given label is not used
  // in the row.
  List<AttributeDef> labeledAttrs =
    row.getFlexfieldViewDef().getLabeledAttributes("SEGMENT_LABEL_RU1");
  for (AttributeDef attr: labeledAttrs)
  {
    System.out.print(attr.getName() + "=" + 
      row.getAttribute(attr.getName()) + ";");
  }
  System.out.println();
}

For more information about segment labels, see Section 24.2.2, "How to Implement Key Flexfield Segment Labels." For more information about the Java API, see the Javadoc.

25.2.3 How to Prepare Key Flexfield Business Components for Oracle Business Intelligence

Oracle Business Intelligence is a comprehensive collection of enterprise business intelligence functionality that provides the full range of business intelligence capabilities including interactive dashboards, full ad hoc, proactive intelligence and alerts, enterprise and financial reporting, real-time predictive intelligence, and more.

While key flexfields are modeled using polymorphic view objects, flexfield technology is not compatible with Oracle Business Intelligence, which also requires reference data, such as lookups, to be modeled as view-linked child view objects. For a key flexfield to be used by Oracle Business Intelligence, it must be flattened into a usable static form. To make this possible, you must designate the flexfield as business intelligence-enabled. When you create business components for this key flexfield, the business component modeler recognizes the business intelligence-enabled setting, and a view object that is flattened for business intelligence is generated alongside the standard key flexfield polymorphic view object. You must also slightly modify the process of creating key flexfield view links and application modules.

When the business intelligence-enabled and flattened key flexfield is configured as part of an application, the implementor can select which individual flexfield segments to make available for use with Oracle Business Intelligence. Only the segments that are business-intelligence enabled are included in the flattened view object.

25.2.3.1 Enabling a Key Flexfield for Oracle Business Intelligence

If you want customers to be able to do business intelligence queries on whatever segments they configure for a flexfield, you must business-intelligence enable the flexfield and its segments. A flattened view object is generated only if the key flexfield is business-intelligence enabled. A segment is included in the flattened view object only if the segment is business-intelligence enabled.

You can set the flexfield's business intelligence-enabled flag at registration time using the fnd_flex_kf_setup_apis.create_flexfield(...) procedure, or you can set the flag later using the fnd_flex_kf_setup_apis.update_flexfield(...) procedure. To learn how to generate documentation about using these procedures, see Section 24.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

You can optionally provide flattened fact names for the flexfield's entity details. This helps to automate the process for importing the key flexfield into Oracle Business Intelligence. To provide the flattened fact name, set the BI_FLATTENED_FACT_NAME value when you register the entity details using the create_adfbc_usage(...) procedure. You can also set the flag later using the update_adfbc_usage(...) procedure.

Use the Manage Key Flexfields task, which is accessed from the Setup and Maintenance work area of any Oracle Fusion Setup application, to enable the segments for business intelligence. Only the segments that are business-intelligence enabled are included in the flattened view object.

If you want to map the business intelligence-enabled segments to logical dimensions in the Oracle Business Intelligence logical model, use the Manage Key Flexfields task to create segment labels and to map the labels to the logical dimensions. Then assign the labels to the appropriate flexfield segments. By mapping the segments to the dimensions, you minimize the steps for importing the flexfield into Oracle Business Intelligence. For information about the logical model, see the "Working with Logical Tables, Joins, and Columns" chapter in the Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition (Oracle Fusion Applications Edition)

25.2.3.2 Producing a Flattened Model for a Business Intelligence-Enabled Key Flexfield

When you create business components for a business intelligence-enabled key flexfield, the business component modeler recognizes the business intelligence-enabled setting, and a view object that is flattened for Oracle Business Intelligence is generated alongside the standard key flexfield polymorphic view object. You must also slightly modify the process of creating key flexfield view links and application modules.

Note:

When you make changes to a business intelligence-enabled flexfield, use the Import Metadata wizard to import the changes into the Oracle Business Intelligence repository as described in the "Using Incremental Import to Propagate Flex Object Changes" section in the Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition (Oracle Fusion Applications Edition).

Before you begin:

1. Enable the flexfield and the desired segments for Oracle Business Intelligence as described in Section 25.2.3.1, "Enabling a Key Flexfield for Oracle Business Intelligence."

2. If your flexfield will use hierarchical (tree structured) value sets, create column-flattened versions of the affected view objects and import them into your project before continuing.

   If the flattened tree view objects are not in your project, the Create Flexfield Business Components wizard will report the missing view objects as errors.

   For more information, see Section 60.8.1, "Designing a Column-Flattened View Object for Oracle Business Intelligence."

To produce a flattened model for a business intelligence-enabled key flexfield:

1. Create key flexfield business components as described in Section 24.2.4, "How to Create Key Flexfield Business Components."

   When a flexfield is business intelligence-enabled, the Create Flexfield Business Components wizard generates a business intelligence-specific view object and other business components under a directory called analytics in the package root directory. These are generated in addition to the typical key flexfield view object. The business intelligence-specific view object is distinguished from the typical key flexfield view object by the "BI:" prefix.

2. Create a view link using the procedure described in Section 24.3.1, "How to Create Key Flexfield View Links." Keep the following in mind:

   • The view object that you use for the source view object can be the same source view object that you used for the base key flexfield.

   • Create the view link from the source view object to the business intelligence-specific view object, which is the view object with the "BI:" prefix as shown in Figure 25-3.

     Figure 25-3 Create Flexfield View Link Wizard — View Objects Page

     
     

3. Create an application module for use with Oracle Business Intelligence, as described in Section 24.2.4.1.3, "How to Create the Maintenance Application Module." Make the following changes:

   1. On the Data Model page of the Create Application Module wizard, when you create an instance of the master view object, there is no need for a child view object.

   2. On the Application Modules page of the wizard, add an instance of the key flexfield Oracle Business Intelligence application module as a nested instance of this application module. You can identify the Oracle Business Intelligence application module by the analytics subpackage under the package root.

   Note:

   If you already have a product Oracle Business Intelligence application module, you may use it.

4. Define the custom properties required to link the master view object instance to the default view instance.

   Do this in the General navigation tab of the nested instance definition of the business intelligence-enabled flexfield application module, as shown in Figure 25-4.

   Figure 25-4 Custom Properties for Business Intelligence-Enabled Application Module

   
   

   As you do this, keep the following points in mind:

   • The default view instance inside the business intelligence-enabled flexfield application module is typically called DefaultFlexViewUsage.

   • The custom property names should be formatted as BI_VIEW_LINK_mypropertyname.

   • The custom property values should be formatted as source_viewobjectinstance_name, viewlink_definition_name, destination_viewobjectinstance_name.

   • Use the fully qualified view object instance names for the source view object and destination view object, and the fully qualified package name for the view link definition.

   • Business intelligence joins between the view object instances you specify in different application modules are created during import from Oracle Application Development Framework (Oracle ADF).

25.2.4 How to Publish Key Flexfield Application Modules as Web Services

You can make access to a key flexfield available through web services, which will enable you to perform create, read, update, and delete (CRUD) operations on the flexfield data rows. You accomplish this by exposing a key flexfield application module as a web service and adding support utility methods for the flexfield service data object to the product application module.

When you generate a flexfield business component, the key flexfield business component and other artifacts are developed based on the information in the flexfield metadata. As illustrated in Figure 24-2, a base view object is created for the code combination id (CCID) and structure instance number (SIN) segments. If any contexts have been configured, subtype view objects are generated for each configured context.

As an example, suppose that an application module has the master view object Fkt1 and a view link from the master view object to the detailed key flexfield view object, Global1, which is a polymorphic view object.

The Business Component Browser view shown in Figure 25-5 corresponds to a particular row in the master view object, displaying the segment structure in the key flexfield with a SIN of 11.

Figure 25-5 Business Component Browser View of Flexfield Row with SIN = 11

The Business Component Browser view shown in Figure 25-6 corresponds to a different row in the master view object, displaying the segment structure in the key flexfield with a SIN of 25.

Figure 25-6 Business Component Browser View of Flexfield Row with SIN = 25

To make a key flexfield accessible through a web service:

1. Expose the key flexfield application module as a web service.

2. Test the web service.

25.2.4.1 Exposing a Key Flexfield Application Module as a Web Service

You make key flexfield access available through web services by doing the following:

1. Setting a custom property for the flexfield view link.

2. Adding a transient attribute to the master view object to store the concatenated flexfield key.

3. Service-enabling the master view object.

4. Creating the service interface for the product application module within which the key flexfield application module is nested.

5. Adding flexfield service data object support utility methods to the product application module.

Note:

In this section, master view object refers to the view object for the reference table as illustrated by Figure 24-1.

Before you begin: 

1. Create the master entity object and view object for the product table that references the key flexfield.

2. Create the flexfield business component as described in Section 24.2.4.3, "Building a Read-Only Reference Model."

   Note:

   When you generate a flexfield business component, JDeveloper automatically service-enables the business component by generating a Service Data Object (SDO) for the base view object and for every subtype view object.

3. Create a flexfield view link between the master view object and the flexfield business component as described in Section 24.3.1, "How to Create Key Flexfield View Links."

4. Nest the key flexfield application module instance in the product application module as described in Section 24.3.2, "How to Nest an Instance of the Key Flexfield Application Module in the Product Application Module."

5. Add the key flexfield view object instance to the application module as described in Section 24.3.3, "How to Add an Instance of a Key Flexfield View Object to the Product Application Module."

To expose a key flexfield application module as a web service: 

1. Complete the following steps to ensure that Service Data Objects (SDOs) exist for all subtype objects.

   1. In the Application Navigator verify that .xsd files exist for all flexfield subtype view objects.

   2. Open the .xsd file for the key flexfield's base view object and verify that <include> elements exist for all the flexfield subtype view objects.

      Figure 25-7 The Include Elements for the Flexfield Subtype SDOs

      
      

   3. If any subtype view object SDOs are missing, edit the key flexfield's base view object, and, on the Java navigation tab, click the Edit Java options icon.

      In the Select Java Options dialog shown in Figure 25-8, select Generate Service Data Object Class, ensure that the namespace is the same location that contains the flexfield view object XML files, and click OK.

      When the SDO is generated for the base view object of the key flexfield polymorphic view object, generic SDOs are automatically generated for all the base view object subtypes.

      Figure 25-8 Generating the SDO for the Key Flexfield Base View Object

      
      

2. Edit the view link between the master view object and the flexfield view object.

3. Click the General navigation tab in the overview editor, expand the Custom Properties section, and add a SERVICE_PROCESS_CHILDREN property set to true, if one does not already exist.

4. Edit the master view object.

5. In the overview editor, add a transient attribute to store the key flexfield concatenated string.

   1. Click the Attributes navigation tab, and click the Add icon in the Attributes section.

   2. In the View Attribute dialog shown in Figure 25-9, enter a name for the attribute.

      Figure 25-9 Adding a Transient Attribute to the Master View Object

      
      

   3. Set the Java attribute type to String.

   4. Leave the Mapped to Column or SQL checkbox unselected.

      A transient attribute does not include a SQL expression.

   5. Select the Always radio button.

   6. Leave the Expression blank.

   7. Click OK.

6. Click the Java navigation tab and click the Edit icon in the Java Classes section to generate classes for the master view object.

7. In the Select Java Options dialog shown in Figure 25-10, select the following checkboxes and click OK:

   • Generate View Object Class

   • Include bind variable accessors

   • Include custom Java data source methods

   • Generate View Row Class

   • Include accessors

   • Generate View Object Definition Class

   • Generate Service Data Object Class

   Figure 25-10 Generating Java Classes for the Master View Object

   
   

8. In the Java navigation tab, click the link for the View Row Class to open it in the editor.

9. Add the code shown in bold in Example 25-5 to the setter method for the transient attribute that you created. Set the viewAccessorName to the name of the view accessor from the view link between the master view object and the key flexfield view object.

   The added code stores the key flexfield concatenated string.

   Example 25-5 Setter Method for the Transient Attribute

   /**
      * Sets <code>value</code> as the attribute value for the calculated attribute KffConcatSegment
      * @param value value to set the  KffConcatSegment
      */
     public void setKffConcatSegment(String value) {
      
       String concatAttrPrefix = FlexfieldProperty.PREFIX;
       String concatAttrPostfix =
         FlexfieldProperty.CONCATENATED_STORAGE_ATTR_POSTFIX;
       // Modify next line to set viewAccessorName to name of the VL
       String viewAccessorName = "put KFF view link acccessor name here";
       String concatAttrName = 
         concatAttrPrefix + viewAccessorName + concatAttrPostfix;
       setAttributeInternal(concatAttrName, value);
   
       setAttributeInternal(KFFCONCATSEGMENT, value);
     }
   

10. Edit the product application module in which the key flexfield application module instance, master view object instance, and flexfield view object instance are nested.

11. Click the Web Service navigation tab and click the Add icon to enable support for the service interface. If the icon is not enabled, click the Edit icon instead and edit the pages that are named in the following steps.

12. In the Service Interface page of the Create Service Interface wizard, the Web Service Name and Target Namespace fields are automatically populated with appropriate values for this application module.

    Click Next to continue.

13. In the Service Custom Methods page, select the client methods in the Available list that you want to expose as part of the service interface and move them to the Selected list.

14. Click Next to continue.

15. In the Service View Instances page select the view objects in the Available list that you want to expose as part of the service interface and move them to the Selected list.

16. Highlight each view object on the Selected list to display the Basic Operations and View Criteria Find Operations lists for the operations that are available for that view object, as shown in Figure 25-11.

    Select the checkbox for each operation of the view object that you want to expose in the service interface and clear the rest.

    Figure 25-11 Create Service Interface Wizard — Service View Instances Page

    
    

17. Click Next to continue.

18. In the Summary page, review your choices and click Finish to generate the web service from the application module. You should see that the Web Service navigation tab now reflects the custom methods, view instances, basic operations, and view criteria find operations that you chose to expose, as shown in Figure 25-12.

    Figure 25-12 Application Module Service Interface Properties

    
    

19. Click Finish.

    The generated service interface components appear below the application module in the Application Navigator, as shown in Figure 25-13.

    Figure 25-13 Application Module Service Interface Structure

    
    

20. In the overview editor for the product application module, click the Java navigation tab.

21. If the Application Module Class and the Application Module Definition Class do not appear in the list of Java classes, click the Edit icon and generate the classes.

22. In the Java Classes section, click the link for the Application Module Class.

23. Add the utility methods shown in Example 25-6 to the application module class. These utility methods enable web service clients to obtain FlexfieldSdoSupport objects to access the flexfield's information.

    Replace Flexfield with the appropriate string for the flexfield that you are working with. Use a string that describes how the flexfield will be used by the customers. For example, getLedgerSdoNamespaceAndName is better than getGLSubtypeSdoNamespaceAndName.

    In the getFlexfieldSdoSupport and getFlexfieldStructureInstanceNumber methods, replace getKffMAM1 with the name of the getter method for the nested maintenance application module instance.

    Example 25-6 Utility Methods for Flexfield Service Data Object Support

    // Change method name as appropriate
        public List<String> getFlexfieldSdoNamespaceAndName(
              String structureInstanceCode) {
            FlexfieldSdoSupport ss= getFlexfieldSdoSupport(structureInstanceCode);
            if (ss == null) {
                return null;
            }
            return Arrays.asList(ss.getSdoNamespace(), ss.getSdoName());
        }
    
        // Change method name as appropriate
        public String getFlexfieldSdoPath() {
            FlexfieldSdoSupport ss = getFlexfieldSdoSupport(null);
            if (ss == null) {
                return null;
            }
            return ss.getDiscriminatorSdoPath();
        }
    
        // Change method name as appropriate
        public List<String> getFlexfieldSegmentSdoPaths(
              String structureInstanceCode,
              List<String> segmentCodes) {
            FlexfieldSdoSupport ss = getFlexfieldSdoSupport(structureInstanceCode);
            if (ss == null) {
                return null;
            }
            ArrayList r = new ArrayList(segmentCodes.size());
            for (String segmentCode : segmentCodes) {
                r.add(ss.getSegmentSdoPath(segmentCode));
            }
            return r;
        }
    
        // Change method name as appropriate
        private FlexfieldSdoSupport getFlexfieldSdoSupport(
              String structureInstanceCode)
        {
            /**
             * @param structureInstanceCode set to null to get the parent (base)
             */
            // Find the nested maintenance application module instance
            KFFMApplicationModuleImpl am;
            // Change getKffMAM1 to name of the getter method for the nested KFF AM
            am = (KFFMApplicationModuleImpl) getKffMAM1();
            return am.getSdoSupport(structureInstanceCode);
        } 
    
        // Change method name as appropriate
        public Long getFlexfieldStructureInstanceNumber(
            String structureInstanceCode) {
            // Find the maintenance application module instance
            KFFMApplicationModuleImpl am;
            // Change getKffMAM1 to name of the getter method for the nested KFF AM
            am = (KFFMApplicationModuleImpl) getKffMAM1();
            return am.getStructureInstanceNumber(structureInstanceCode);
        }
    

24. In the overview editor for the product application module, click the Web Service navigation tab for the product application module and click the Edit icon in the Service Interface Custom Methods section.

25. In the Service Custom Methods page, move the newly added public methods to the Selected list to make them available for clients and click OK.

    The application module's remote server implementation class will be modified to expose these methods.

25.2.4.2 Testing the Web Service

You can test the key flexfield web service access by providing web server connection information, deploying and manually testing the web service, and optionally writing Java client programs to call the support utility methods for the flexfield service data object to test the service.

Before you begin: 

1. Ensure that the BC4J Service Client and BC4J Service Runtime libraries are included in your project.

2. Create a writable maintenance model as described in Section 24.2.4.1, "Building a Writable Maintenance Model."

3. Expose the key flexfield maintenance application module as a web service as described in Section 25.2.4.1, "Exposing a Key Flexfield Application Module as a Web Service."

To test the web service: 

1. Expand Application Resources > Descriptors > ADF Meta-INF, and open the connections.xml file.

2. Locate the Reference element for the product application module's service (ApplicationService in this example).

   This is the service that you created in Section 25.2.4.1, "Exposing a Key Flexfield Application Module as a Web Service" for the product application module in which the key flexfield maintenance application module instance, master view object instance, and flexfield view object instance are nested.

3. Add the StringRefAddr elements that are shown in bold in Example 25-7. Modify the host and port number in the jndiProviderURL entry to point to an instance of Oracle WebLogic Server. The port number is typically 7101.

   Example 25-7 StringRefAddr Elements to Add to Application Module Reference in connections.xml

   <Reference name="{http://xmlns.oracle.com/oracle/apps/fnd/applcore/flex/test/kff1/model/}ApplicationService"
                 className="oracle.jbo.client.svc.Service" xmlns="">
         <Factory className="oracle.jbo.client.svc.ServiceFactory"/>
         <RefAddresses>
            <StringRefAddr addrType="serviceInterfaceName">
               <Contents>oracle.apps.fnd.applcore.flex.test.kff1.model.ApplicationService</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="serviceEndpointProvider">
               <Contents>ADFBC</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="jndiName">
              <Contents>ApplicationServiceBean#oracle.apps.fnd.applcore.flex.test.kff1.model.ApplicationService</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="serviceSchemaName">
               <Contents>ApplicationService.xsd</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="serviceSchemaLocation">
               <Contents>oracle/apps/fnd/applcore/flex/test/kff1/model/</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="jndiFactoryInitial">
               <Contents>weblogic.jndi.WLInitialContextFactory</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="jndiProviderURL">
               <Contents>t3://localhost:port_number</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="jndiSecurityPrincipal">
               <Contents>weblogic</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="jndiSecurityCredentials">
               <Contents>weblogic1</Contents>
            </StringRefAddr>
         </RefAddresses>
      </Reference>
    
   

4. Run the remote server class for the product application module to deploy the service to an Integrated WebLogic Server instance and to manually test the web service.

   Note:

   The remote server class was generated when you exposed the key flexfield as a web service in Section 25.2.4.1, "Exposing a Key Flexfield Application Module as a Web Service." This class has a name that ends with ServiceImpl.java.

5. Optionally, create and run Java client programs to test invoking the web service.

   Example 25-8 is an example of how a client test program would use the support utility methods for the flexfield service data object that you added in Section 25.2.4.1, "Exposing a Key Flexfield Application Module as a Web Service."

   Example 25-8 Sample Java Code to Test the Web Service

   public void testSDOAPIs()
     {
       // Flexfield service data objects are created automatically based on the
       // flexfield definition. Certain information such as segment codes must be
       // transformed to be used in a service data object definition. 
       AcctKffMaintService acctKffMaintService =
         (AcctKffMaintService)
         ServiceFactory.getServiceProxy(AcctKffMaintService.NAME);
       DataFactory dataFactory =
         ServiceFactory.getDataFactory(acctKffMaintService);
     
       // Get the namespace and name corresponding to a particular
       // structure instance code.
       List<String> accountSdoInfo =
         acctKffMaintService.getAcctSdoNamespaceAndName("CC_ACCT_LOC_PRJ_SI");
       System.out.println(accountSdoInfo);
      
       DataObject accountDo =
         dataFactory.create(accountSdoInfo.get(0), accountSdoInfo.get(1));
       System.out.println(accountDo);
       accountDo.set(acctKffMaintService.getAcctSDOPath(),
                     acctKffMaintService.getAcctStructureInstanceNumber(
                     "CC_ACCT_LOC_PRJ_SI"));
    
       // Get segment paths for attributes COST_CENTER and LOCATION.
       List<String> segmentPaths =
         acctKffMaintService.getAcctSegmentSdoPaths("CC_ACCT_LOC_PRJ_SI",
                                                    Arrays.asList("COST_CENTER",
                                                                  "LOCATION"));
    
       System.out.println(segmentPaths);
    
       //Update COST_CENTER and LOCATION after obtaining their segmentPaths.
       accountDo.set(segmentPaths.get(0), "A12");
       accountDo.set(segmentPaths.get(1), "UK");
      
     }
   

25.2.5 How to Access Key Flexfields from an ADF Desktop Integration Excel Workbook

ADF Desktop Integration makes it possible to combine desktop productivity applications with Oracle Fusion applications, so you can use a program such as Microsoft Excel as an interface to access application data.

Using ADF Desktop Integration, you can incorporate key flexfields into an integrated Excel workbook, so you can work with the flexfield data from within the workbook.

For more general information about integrating Oracle Fusion applications with desktop applications, see the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework. This guide provides most of the information you need to complete the required activities, including the following:

• Preparing your development environment for desktop integration.

• Creating a page definition file that will expose the Oracle ADF bindings for use in Excel.

• Creating an Excel workbook to integrate with the key flexfield.

• Preparing your Excel workbook to access the column containing the flexfield.

• Incorporating a key flexfield as a dynamic or static column in an ADF Desktop Integration Table on a worksheet in the workbook.

Note:

The Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework does not make explicit reference to technologies documented in this Oracle Fusion Applications Developer's Guide, and this guide does not repeat the content in the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework. Therefore, you must read the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework for a full understanding of how to use ADF Desktop Integration technology in general.

In addition to the standard implementation steps covered in that guide, you must modify your implementation to accommodate flexfields, as discussed in the following sections.

There are two ways to access a key flexfield in Excel:

• Using a dynamic column in an ADF Desktop Integration Table.

  A web page in a popup dialog can be associated with a dynamic column, enabling end users to pick flexfield segment values. Alternatively, users can enter values directly into the segment fields. No custom code is required in either case.

  This is the most typical scenario. Each key flexfield segment is displayed as a distinct column in the ADF Desktop Integration Table. First you configure ADF Desktop Integration with a dynamic column key flexfield, and then, if necessary, you handle user-initiated structure code changes.

• Using a static column in a popup dialog associated with a single cell. Use this approach for either of the following reasons:

  • The key flexfield is in a non-table area of the worksheet.

  • The ADF Desktop Integration Table needs to expose the same key flexfield instance more than once. In this case, only one instance can be dynamic. All other instances should be exposed as static columns.

  In addition to using the popup dialog, end users can enter values directly into the segment field, with the values separated by an appropriate delimiter that you specify.

  Note:

  A static column is any column for which the DynamicColumn property is set to False.

  Individual flexfield segments do not appear in the worksheet. Instead, ADF Desktop Integration invokes a separate JSPX page on which the flexfield will be visible. You can use this scenario with an ADF Desktop Integration form, or either table type, by configuring ADF Desktop Integration with a static column key flexfield.

Note:

The titles of the popup dialog components must be set to the name of the key flexfield, such as "Account," to be consistent across Oracle Fusion Applications.

The key flexfield's segments are part of your database table, so the flexfield is generated against the same entity object on which your worksheet view object is based.

In addition to configuring ADF Desktop Integration with the dynamic or static column key flexfield, you might also need to call a custom application module to handle the update or insert of a key flexfield data row.

25.2.5.1 Configuring ADF Desktop Integration with a Dynamic Column Key Flexfield

When you configure the ADF Desktop Integration Table, make the following changes:

• Add the ADF Desktop Integration Model API library to your data model project.

• In your page definition for the worksheet, add the key flexfield that you want to access to the master worksheet view object as a child node. Do not add any display attribute to the child node, which expands as a dynamic column in the worksheet.

  For more information about how to create a page definition file for a desktop integration project, see the "Working with Page Definition Files for an Integrated Excel Workbook" section of the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework.

• To make the key flexfield column of the ADF Desktop Integration Table component dynamic, set the DynamicColumn property in the TableColumn array of the ADF Desktop Integration Table to True. A dynamic column in the TableColumn array is a column that is bound to a tree binding or tree node binding whose attribute names are not known at design time. A dynamic column can expand to more than a single worksheet column at runtime.

  For more information about the binding syntax for dynamic columns, see the "Adding a Dynamic Column to Your ADF Table Component" section of the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework.

• For the table's UpdateComponent and InsertComponent properties, specify one of the following as the subcomponent to use:

  • Inputtext

  • OutputText

  • ModelDrivenColumnComponent

• For the subcomponent's Value property, access the Expression Builder, expand the Bindings node and your tree binding for the table, and select the flexfield node.

• For the subcomponent's Label property, access the Expression Builder, expand the Bindings node and your tree binding for the table, and select the flexfield node.

For information about the Expression Builder, see the "Using the Expression Builder" section in the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework.

25.2.5.2 Handling User-Initiated Structure Code Value Changes in a Dynamic Column Key Flexfield

ADF Desktop Integration requires that to use a dynamic column implementation, the structure of the key flexfield should remain constant for all rows in a given result set. However, each time a new result set is downloaded into the table, the structure code value (and thus the structure) can be changed.

If the structure code value is set globally for the end user of the workbook, changes are not an issue. However, if the end user can control the structure code value (for example, using an LOV in a "header" form), your application must be able to respond appropriately to update the key flexfield structure.

After the end user specifies a structure code value, you must invoke the worksheet UpSync method to get the new value into the model. Then you can use the ADF Desktop Integration Table Download method to get fresh data with the new key flexfield structure.

Note:

For an insert-only table, the Download method is undesirable. For these cases, use either the ADF Desktop Integration Table DownloadForInsert method or the Initialize method to enable the ADF Desktop Integration Table component to reconfigure to accommodate the new flexfield structure.

25.2.5.3 Configuring ADF Desktop Integration with a Static Column Key Flexfield

ADF Desktop Integration supports key flexfields by using tree bindings in an ADF Desktop Integration Table. If you are adding your key flexfield as a static column, you can alternatively use an ADF Read-Only Table. Keep in mind that ADF Read-Only Tables support static columns, but not dynamic columns. Popup dialogs support both types.

Note:

A key flexfield appears as a node in the tree binding at design time. Because flexfields are built up dynamically at runtime, you will not see any attributes under the flexfield node in the page definition, but the node itself is present.

When you configure the popup dialog, make the following changes:

• You can use the column's action set properties to make the key flexfield web page available for editing. You should include the attributes used in the web page in the table's cached attributes unless the row will be committed immediately.

• You must choose a fixed attribute (the key flexfield CCID) to represent the flexfield in the worksheet. Add a Dialog action to the DoubleClickActionSet of an InputText or OutputText component, then connect the Dialog action to a JSPX page that will display the key flexfield.

  For more information about how to create a page definition file for a desktop integration project, see the "Working with Page Definition Files for an Integrated Excel Workbook" section of the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework.

For static display of a key flexfield in an ADF Desktop Integration workbook, you must create an updatable transient attribute in the view object on which the ADF Desktop Integration table is based. This transient attribute will hold the concatenated value of the key flexfield segments, separated by a delimiter. If one purpose of the worksheet is to display existing data from the database, the transient attribute should be populated using custom application module methods upon returning from a popup dialog or opening the worksheet.

25.2.5.4 Handling Update or Insert of a Key Flexfield Data Row

To handle updating or inserting a data row containing a key flexfield in an ADF Desktop Integration table, you call a custom application module method that contains appropriate code, as follows:

• To update an existing row, add your code to the UpdateRowActionId property of the table.

• To insert a new row, add your code to the InsertAfterRowActionId property of the table.

The following examples demonstrate the code needed to accomplish these tasks. Example 25-9 and Example 25-10 apply to an ADF Desktop Integration implementation with the key flexfield exposed as a dynamic column. Example 25-11 and Example 25-12 apply to an ADF Desktop Integration implementation with the key flexfield exposed as a static column.

Example 25-9 Updating an Existing Row with a Key Flexfield Dynamic Column

You add this code as an application module method that will be invoked from the UpdateRowActionId property of an ADF Desktop Integration Table. This code will be invoked for every row that is updated.

Row tempRow = null;
// Get KFF child row information based on the KFF view link accessor.
KFFViewRowImpl acctRow;
ViewRowImpl kffAcctRow = (KFFViewRowImpl)linerow.getAccountLineKff();
 
// If it is not child row (for a new row case or cases where 
// KFF data is not invalid/present for existing DB Row)
// get a dummy row from ADF Desktop Integration helper class.
 
if (kffAcctRow == null) {
          tempRow = ModelHelper.getAdfdiTempChildRow(linerow, "AccountLineKff");
           kffAcctRow = (ViewRowImpl)tempRow;
       }
 
Long kffAcctId = null;
String acctSeg=null;
 
// Check whether the KFF row is an instance of KFFViewRowImpl, 
// which means you are updating valid KFF data.
// If not, it means you are creating new KFF data.
// Based on that, logic of deriving updated segments from worksheet differs.
 
if (kffAcctRow instanceof KFFViewRowImpl){
 acctRow = (KFFViewRowImpl)fkRow.getAccountLineKff();
 acctSeg = acctRow.getBufferedConcatenatedSegments();
} else {
 acctSeg = KFFViewRowImpl.getConcatenatedSegments(kffAcctRow);
}
kffAcctId = linerow.getKeyFlexfieldCombinationID("AccountLineKff",acctSeg);
 
 
// If you need dynamic insert (the creation of new segment combinations) 
// from the ADF Desktop Integration worksheet, make sure the end user
// supplied a valid value for at least one segment.
 
if (kffAcctId==null) {  
  // If there was not valid CCID obtained earlier, that means you 
  // are trying to add a new combination.
 
   String delimiter = FlexfieldViewDefImpl.getDelimiter(kffAcctRow.getViewDef());
   List segments =
      FlexfieldViewDefImpl.getFlexfieldAttributes(kffAcctRow.getViewDef());
    StringBuffer delim = new StringBuffer();
   for (int i = 0; i < segments.size() - 1; i++) {
      delim.append(delimiter);
    }
 
    // If getConcatenatedSegments() returns only delimiter information,
    // that means end user has not supplied valid KFF Segment values.
 
if (!acctSeg .equals(delim.toString())) {
   linerow.setKeyFlexfieldCombinationID("AccountLineKff",
                                       acctSeg);
                                      
//Get CCID value based on segment information
 
kffAcctId =
    linerow.getKeyFlexfieldCombinationID("AccountLineKff", acctSeg );
}
} //if kffAcctId is null
 
//setting CCID column with CCID value
 
     linerow.setDistCodeCombinationId(kffAcctId);
 

Example 25-10 Inserting a New Row with a Key Flexfield Dynamic Column

Add this code as an application module method that will be invoked from the InsertAfterRowActionId property of an ADF Desktop Integration Table. This code will be invoked for every row that is inserted.

Row tempRow = null;
// Retrieve key flexfield child row information based on 
// the KFF view link accessor
ViewRowImpl kffAcctRow = (ViewRowImpl)linerow.getAccountLineKff();
 
// If not a child row (for new row case or cases where 
// KFF data is not invalid/present for existing DB Row),
// get a dummy row from ADF Desktop Integration helper class
 
if (kffAcctRow == null) {
      tempRow = ModelHelper.getAdfdiTempChildRow(linerow, "AccountLineKff");
      kffAcctRow = (ViewRowImpl)tempRow;
 
}
Long kffAcctId = null;
kffAcctId =
    linerow.getKeyFlexfieldCombinationID("AccountLineKff",
KFFViewRowImpl.getConcatenatedSegments(kffAcctRow));
 
// If you need dynamic insert (creating new segment combinations) 
// in the ADF Desktop Integration worksheet, 
// make sure the end user supplies a valid value for at least one segment.
if (kffAcctId==null) {
    String delimiter = FlexfieldViewDefImpl.getDelimiter(kffAcctRow.getViewDef());
    List segments =
    FlexfieldViewDefImpl.getFlexfieldAttributes(kffAcctRow.getViewDef());
    StringBuffer delim = new StringBuffer();
    for (int i = 0; i < segments.size() - 1; i++) {
        delim.append(delimiter);
    }
 
    // If getConcatenatedSegments() return only delimiter information,
    // that means end user has not supplied valid KFF Segment values.
 
    if
 (!KFFViewRowImpl.getConcatenatedSegments(kffAcctRow).equals(delim.toString())){
        linerow.setKeyFlexfieldCombinationID("AccountLineKff",
        KFFViewRowImpl.getConcatenatedSegments(kffAcctRow));
                                      
        // Get CCID value based on segment information.
 
        kffAcctId =
            linerow.getKeyFlexfieldCombinationID("AccountLineKff",
                KFFViewRowImpl.getConcatenatedSegments(kffAcctRow));
    }
}
// Set CCID column with CCID value.
 
linerow.setDistCodeCombinationId(kffAcctId);
 

Example 25-11 Updating or Inserting a Row with a Key Flexfield Static Column

This code should be added to the setter method of the transient attribute in your view object RowImpl.

setAttributeInternal(TRANSIENTACCOUNT, value); 
// Get KFF child row information based on the KFF view link accessor.
 
ViewRowImpl kffAcctRow = (ViewRowImpl)linerow.getAccountKff();
Row tempRow;
 
// If not child row (for new row case or cases where 
// KFF data is not invalid/present for existing DB Row),
// Get a dummy row from ADF Desktop Integration helper class
if (kffAcctRow == null) {
    tempRow = ModelHelper.getAdfdiTempChildRow(this, "AccountKff");
    kffAcctRow = (ViewRowImpl)tempRow;
}
Long kffAcctId = null;
 
// If you need dynamic insert (creating new segment combinations) 
// in the ADF Desktop Integration  worksheet, 
// make sure the end user supplies a valid value for at least one segment.
 
String delimiter = FlexfieldViewDefImpl.getDelimiter(kffAcctRow.getViewDef());
List segments =
FlexfieldViewDefImpl.getFlexfieldAttributes(kffAcctRow.getViewDef());
StringBuffer delim = new StringBuffer();
for (int i = 0; i < segments.size() - 1; i++) {
    delim.append(delimiter);
}
 
// If getConcatenatedSegments() returns only delimiter information,
// that means the end user has not supplied a valid segment value.
 
       if (value!=null){
          if (!KFFViewRowImpl.getConcatenatedSegments(kffAcctRow).equals(delim.toString()))
                this.setKeyFlexfieldCombinationID("AccountKff",value);
 
       kffAcctId =  this.getKeyFlexfieldCombinationID("AccountKff", value);
 
              //set your orignal attribute with CCID value.
              this.setAcctsPayCodeCombinationId(kffAcctId);
                           }     }
 

Example 25-12 Applying Modified Segment Values to a Cell in a Key Flexfield Static Column

You add this code as a custom application module method that will be invoked from the ActionListener property of the OK button in the popup dialog JSPX page.

// Get a reference to your ADF DesktopIntegration table view object.
DesktopQuickInvoicesHeaderVOImpl headerVO = 
           this.getDesktopQuickInvoicesHeader();
 
// Get a reference to the current row, which is the row from which 
// popup dialog is opened
       DesktopQuickInvoicesHeaderVORowImpl headerRow = 
           (DesktopQuickInvoicesHeaderVORowImpl)headerVO.getCurrentRow(); 
// Get a reference to your key flexfield row
       ViewRowImpl kffAcctRow = (ViewRowImpl)headerRow.getAccountKff(); 
       Row tempRow;
 
       // If that is null (for a null CCID or an invalid CCID or a new row), 
       // get temp row from ADF Desktop Integration.
       if (kffAcctRow == null) { 
           tempRow = ModelHelper.getAdfdiTempChildRow(headerRow, "AccountKff"); 
           kffAcctRow = (ViewRowImpl)tempRow; 
 
       } 
// Derive and assign value of segments to your transient attribute 
// that is created for single cell display in the ADF Desktop Integration Table.
       headerRow.setTransientAccount(KFFViewRowImpl.getConcatenatedSegments(kffAcctRow));

25.3 Completing the Development Tasks for Key Flexfields in Secondary Mode

The most common use of a key flexfield is for a product table to have a foreign key to the primary key of the combinations table. This provides the flexibility of storing all the combinations in a single table and having references to these combinations from the product tables. However, there are circumstances where application developers might need to capture segment values in a transaction table or a setup table. In this case, the key flexfield becomes a data capturing tool, and the captured data is stored in a product table. There is no direct relationship between the product table and the key flexfields combinations table. This type of usage is called secondary usage.

There are two types of secondary usage:

• single-segment secondary usage: Use this mode when you want to capture only one segment value in a transaction or setup table. For example, if you want to capture the default cost center value for an employee in the EMPLOYEES table in the DEFAULT_COST_CENTER column.

• all-segment secondary usage: Use this mode when you want to capture all the flexfield's segment values in a transaction or setup table. For example, if you want to capture all the segment values in a general ledger setup table for use in filling in missing values in the subledger account engine.

Note:

To incorporate a key flexfield secondary usage into your application, you must have already defined and registered the key flexfield primary usage on which it is based. See Section 24.2.1.5, "Registering and Defining Key Flexfields Using the Setup APIs," before continuing.

The development tasks for key flexfields in secondary mode consist of the following steps:

1. Complete the registration of a key flexfield secondary usage (all-segments or single-segment).

2. Create key flexfield business components that are based on the secondary usage for use in secondary mode development tasks.

3. Create a view link between your product view object and the secondary mode key flexfield.

4. The remainder of the development process is essentially the same as the consumer development process for key flexfield primary usages. You can skip the section on creating key flexfield view links and continue with the tasks described in the following sections:

   1. Section 24.3.2, "How to Nest an Instance of the Key Flexfield Application Module in the Product Application Module"

   2. Section 24.3.3, "How to Add an Instance of a Key Flexfield View Object to the Product Application Module"

   3. Section 24.4, "Employing Key Flexfield UI Components on a Page"

      Note:

      This section contains additional information specific to key flexfield secondary usages.

   4. Section 24.4.3, "How to Configure Key Flexfield UI Components"

      Note:

      This section contains additional information specific to key flexfield secondary usages.

After you have completed the development tasks for secondary usages, you can incorporate the secondary usages in the application user interface as described in Section 24.4, "Employing Key Flexfield UI Components on a Page."

25.3.1 How to Register a Key Flexfield All-Segment Secondary Usage

All-segment secondary usages have a column in the product table for every segment column in the combinations table.

To register an all-segment secondary usage:

1. Add columns to your product table to represent all of the key flexfield segment columns in the combinations table. The columns that you add must match exactly in number, data type, and size, the corresponding columns in the combinations table.

   Furthermore, the column names must also be the same as in the combinations table, except for an optional prefix.

   For example, if the column names are A1 and A2 in the combinations table, then in the secondary usage they could again be A1 and A2, respectively, or with a prefix they could be X_A1 and X_A2. They cannot be B1 and Y_B2, nor any variation that does not end in the names of the combinations table columns.

2. Use the PL/SQL registration APIs in the FND_FLEX_KF_SETUP_APIS package to register the secondary usage.

   To learn how to generate documentation about using the APIs, see Section 24.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

3. Create an ADF Business Components usage for the flexfield table usage as described in Section 24.2.1.9, "Registering Entity Details Using the Setup APIs."

To implement a key flexfield secondary usage, you select the usage at design time. For more information, see Section 24.2.4, "How to Create Key Flexfield Business Components."

If you need to change a table usage after creating it, you must delete the table usage, then re-create it.

25.3.2 How to Register a Key Flexfield Single-Segment Secondary Usage

Single-segment secondary usages have one column in the product table to capture a single segment column in the combinations table.

To register a single-segment secondary usage:

1. Add the key flexfield segment column that you want to capture to your product table. The table cannot be the combinations table for the flexfield.

2. Define a segment label for the segment that you want to capture.

   The segment label must be defined as Unique to ensure that only one segment in a given structure can be associated with this label.

   For more information about segment labels, see Section 24.2.2, "How to Implement Key Flexfield Segment Labels."

3. Use the PL/SQL registration APIs in the FND_FLEX_KF_SETUP_APIS package to register the secondary usage.

   You must supply the SEGMENT_LABEL_CODE value to identify the unique segment label, and the COLUMN_NAME value to identify the column in your table in which the segment value will be stored.

   To learn how to generate documentation about using the APIs, see Section 24.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

4. Create an ADF Business Components usage for the flexfield table usage as described in Section 24.2.1.9, "Registering Entity Details Using the Setup APIs."

To implement a key flexfield secondary usage, you select the usage at design time. For more information, see Section 24.2.4, "How to Create Key Flexfield Business Components."

If you need to change a table usage after creating it, you must delete the table usage, then re-create it.

25.3.3 How to Create Key Flexfield Business Components for Secondary Usage

Zero or more secondary usages can be defined for a given flexfield, each one potentially on a different product table.

Before you begin:

1. One or more required libraries might have not been automatically included in your project. Ensure that all required libraries, notably the BC4J Service Runtime, Java EE 1.5 and Java EE 1.5 API libraries, are included.

2. Using the Create Entity Object wizard, create entity objects for the combinations tables that you have defined. Verify the following:

   • At least one customization class is included in the adf-config.xml file.

     Note:

     This serves to ensure correct application behavior. It does not matter which customization class you include.

     For information about customization layers, see the "Understanding Customization Layers" section in the Oracle Fusion Applications Extensibility Guide for Developers.

   • These entity objects are directly modeled on the combinations tables; hence, they contain the fixed (nonflexfield) columns, if any, along with all of the flexfield columns. In general, all columns should be included.

   • The entity objects have primary keys defined.

   • The CCID column is of type data type java.lang.Long.

   • The SIN column, if it exists, is of data type java.lang.Long.

     Caution:

     The SIN attribute cannot be transient with a calculated value. It can be based on a database table column, or it can be SQL-derived.

   • The NUMBER type segment columns are of data type java.math.BigDecimal.

   • The VARCHAR2 type segment columns are of data type java.lang.String.

   • The package name and the object name prefix for each entity object are registered with the flexfield usage to which it will provide data, as described in Section 24.2.1.9, "Registering Entity Details Using the Setup APIs."

3. Build your project to ensure that the entity objects are available in classes. The modeler relies on what is in your classes.

To create key flexfield business components for a secondary usage:

1. In the Application Navigator, right-click the project and choose New.

2. In the New Gallery, expand Business Tier, select ADF Business Components and then Flexfield Business Components, and click OK.

3. On the Flexfield page of the Create Flexfield Business Components wizard, select key from the Type dropdown list as shown in Figure 25-14.

   Figure 25-14 Create Flexfield Business Components Wizard — Flexfield Page

   
   

4. In the Application field, specify the full name of the application to which your key flexfield belongs.

   You can browse for the name, and filter by ID, Short Name, or Name.

5. In the Code field, specify the code of the key flexfield you want to use.

   You can browse for and filter by Code.

6. In the Usage section, select the table row that contains the desired secondary key flexfield usage. Key flexfield usage can be one of the following types:

   • An all-segment secondary usage of the key flexfield on a product table other than the combinations table. Zero or more all-segment secondary usages can be defined for a given flexfield, each one potentially on a different product table. You can identify all-segment secondary usages by the presence of the prefix (Partial) in the Description field.

   • A single-segment secondary usage of the key flexfield on a product table other than the combinations table. Zero or more single-segment secondary usages can be defined for a given flexfield, each one potentially on a different product table. You can identify single-segment secondary usages by the presence of the prefix (Partial Single) in the Description field.

   Do not select a flexfield usage without a prefix in the Description field. For more information about key flexfield secondary usages, see Section 25.3.1, "How to Register a Key Flexfield All-Segment Secondary Usage."

7. Click Next.

8. On the Path page, select the path of the directory structure in which to create the flexfield business components and click Next.

9. On the Entity Object page, select the entity object to use as the data source for the key flexfield Figure 25-15.

   Because you selected a secondary usage on the Flexfield page, you must select the entity object for the table where that usage is defined.

   The entity object you select must include all of the attributes that will be referenced by the flexfield. For secondary usages, this includes attributes that represent the SIN column, the DSN column if it exists in the combinations table, and all of the flexfield segment columns.

   Note:

   If you select a polymorphic entity object, ensure that the InheritPersonalization property for every subtype entity is set to true.

   Caution:

   The Create Flexfield Business Components wizard is case-sensitive. All column names — and the names of the flexfield entity object attributes associated with them — must be uppercase.

   Figure 25-15 Create Flexfield Business Components Wizard — Entity Object Page

   
   

10. You might wish to select an entity object for which the key flexfield attributes are defined as transient (not based on database table columns). If you need to do this, then select The names of the flexfield attributes match the names of registered columns exactly; do not check the underlying columns.

    When a key flexfield entity object attribute is transient, there is no matching underlying column name. When you select this option, the system will match the entity object attribute names to the key flexfield column names, and use the matching attributes to access the flexfield data. Ensure that the entity object has a full set of attributes with matching names before you select this option.

    Caution:

    The transient SIN attribute cannot be a calculated value; it must be SQL-derived (computed using a SQL expression).

    This entity object must be registered under the primary usage as described in Section 24.2.1.9, "Registering Entity Details Using the Setup APIs." There is no need to register another table for this purpose, even if the entity object is based on some other table.

    Note:

    If the entity object with transient key flexfield attributes is not based on the primary usage, the transient attributes must be named using the same prefix as the other attributes of that entity object (and the corresponding table columns). For more information, see Section 25.3.1, "How to Register a Key Flexfield All-Segment Secondary Usage."

11. Click Next.

12. On the Usage Settings page, select Mark All Segments Optional as shown in Figure 25-16 if this secondary usage is simply to add extra information for a product row and the user should not be required to provide information for every segment.

    Figure 25-16 Create Flexfield Business Components Wizard — Usage Settings Page

    
    Description of "Figure 25-16 Create Flexfield Business Components Wizard — Usage Settings Page"
    
    

13. From the Structure Instance Number dropdown list, select the entity attribute that corresponds to the key flexfield SIN for the secondary usage. The SIN must be an attribute of type java.lang.Long.

    If the key flexfield is data set-enabled, this page will also contain a Data Set Number dropdown list. From the dropdown list, select the entity attribute that corresponds to the DSN for the secondary usage. The DSN must be an attribute of type java.lang.Long.

14. Click Next.

15. The Naming page displays the entity object's package name and object name that you registered for the usage, as described in Section 24.2.1.9, "Registering Entity Details Using the Setup APIs." Click Next.

16. On the Summary page, review your choices and click Finish.

    The business components generated will replace any existing ones that are based on the same flexfield.

    Note:

    This wizard might fail with a "ClassNotFound" exception message. This indicates that one or more required libraries have not been automatically included in your project, notably the BC4J Service Runtime, Java EE 1.5 and Java EE 1.5 API libraries. You can resolve this issue by manually adding any missing libraries; then you can complete this procedure successfully.

17. Refresh the project to see the newly created flexfield business components in the Application Navigator.

25.3.4 How to Create Key Flexfield View Links for a Secondary Usage

A view link is needed whenever a product view object references your key flexfield. The base view object can have many incoming view links from various product view objects, as a key flexfield is usually shared by many product tables.

Before you begin:

You should have already created a master view object for your entity object using the standard wizard.

Ensure that the view object does not include flexfield attributes such as SEGMENT1_VARCHAR2, SEGMENT2_NUMBER, and so on. Ensure that you include the attributes that are needed for the foreign key reference, such as CCID, SIN, and, if present, DSN. Ensure that the CCID attribute's Display control hint is set to Hide.

To create a key flexfield view link for a secondary usage:

1. In the Application Navigator, right-click the project and choose New.

2. In the New Gallery, expand Business Tier, select ADF Business Components and then Flexfield View Link, and click OK.

3. In the Create Flexfield View Link wizard, on the Name page, provide a package name as shown in Figure 25-17.

   Figure 25-17 Create Flexfield View Link Wizard — Name Page

   
   

4. Enter a name for the secondary mode view link.

5. Click Next. The View Objects page appears, as shown in Figure 25-18.

   Figure 25-18 Create Flexfield View Link Wizard — View Objects Page

   
   

6. In the Select Source View Object tree, select a secondary usage view object.

7. In the Select Destination Flexfield tree, expand the available flexfield view objects from your project and select the key flexfield's base view object.

8. In the View Link Accessor Name field, enter an appropriate name for the view link accessor.

9. Click Next. The Source Attributes page appears, as shown in Figure 25-19.

   Figure 25-19 Create Flexfield View Link Wizard — Source Attributes Page for Secondary Mode Key Flexfields

   
   

   This page is informational only. The key attributes of the source view object will be used to define the view link. The primary key attribute should be listed for this selection.

10. Click Finish to go to the Summary page.

    Note:

    You can skip the Properties page because view link-specific properties are not supported.

11. On the Summary page, review the summary, then click Finish.

    The secondary mode key flexfield view link is generated.

25.4 Working with Code-Combination Filters for Key Flexfields

A code-combination filter for a key flexfield is a set of query criteria that can be applied to a combinations table to specify a subset of code combinations. After you incorporate a code-combination filter into your application, end users can select key flexfield values in the user interface from the subset produced by the filter.

For example, consider the rows that are listed in Table 25-2. Note that not all columns are shown in the table.

Table 25-2 Example Combinations Table

SIN	CCID	Summary_Flag	Enabled_Flag	Segment1_Varchar2	Segment2_Varchar2
11	77	N	Y	8.5X12	YEL
11	78	N	Y	8.5x14	GRN
14	2	N	Y	ERGO	NYLON
14	3	N	Y	HGBAK	LEATHER

You could define a filter with the following conditional logic:

SIN=14 and Segment1_Varchar2='ERGO'

When you apply this filter condition to the combinations table, the result listed in Table 25-3 is presented. Note that not all columns are shown in the table.

Table 25-3 Example Filter Result

SIN	CCID	Summary_Flag	Enabled_Flag	Segment1_Varchar2	Segment2_Varchar2
14	2	N	Y	ERGO	NYLON

Combination filter conditions for key flexfields are stored in a database column of type XMLType. This column is referred to a a filter-condition column.

There are three types of code-combination filters that you can use in your application — standard code-combination filters, code-combination filters for Oracle Business Intelligence Publisher (Oracle BI Publisher) reports, and cross-validation filters.

25.4.1 How to Use Standard Combination Filters

With standard code-combination filters, you determine which key flexfields your end users should be able to filter. Then you define a dedicated filter-condition column in your application database for each filter that you want to include in the application user interface. This column can be defined in an existing reference table. You can also create one or more dedicated tables just to store filter-condition columns.

The filter condition is stored in the filter-condition column as XML. At runtime, the filter condition in the XML is converted to a ViewCriteria object and applied to the key flexfield's base view object so that when the view object is executed, the filter condition is applied and the filtered query results are produced.

In JDeveloper, you prepare business objects based on the table containing the filter-condition column, then you associate a combination filter view object attribute with the key flexfield. You can associate zero, one, or many combination filters with a given key flexfield, but only one flexfield can be addressed by a given filter.

To make the code-combination filter accessible to application implementors or administrators, add a code-combination filter UI component to an application page. Each row contains a different filter definition that can be applied to the associated key flexfield. The implementors or administrators will be responsible for populating the table with filter criteria using a provided utility.

Code-combination filters for key flexfields are supported by the XML schema FndFilter.xsd. This XML schema binds the filter XML that is defined. This schema is registered with the FUSION database schema at the following URI:

http://www.oracle.com/apps/fnd/applcore/filter/FndFilter.xsd

The XML schema is registered to the database as BINARY_XML.

You can test the filter definitions by inserting predefined XML filter criteria into the filter-condition column.

Note:

A PL/SQL API is provided so that you can apply filters to your SQL statements as WHERE clause conditions rather than applying them to the user interface. For more information, see Section 25.4.8, "How to Apply Code-Combination Filters Using the PL/SQL Filter APIs."

Code-combination filters are removed from an application by removing their accessors.

25.4.2 How to Use Code-Combination Filters for Oracle BI Publisher Reports

The Applications Core key flexfield filter repository enables Oracle Fusion Applications developers to include selected key flexfield segments as available parameters in an Oracle BI Publisher report submission user interface. The filter-repository mechanism translates report parameters for those segments into code-combination filter criteria, which are then translated into SQL for inclusion in the report. You accomplish this by first creating a flexfield filter view object for the public entity object FndKfEssFiltersPEO to access a provided common filter repository table, and then by adding to the report submission page a filter UI component that is based on the filter view object.

When the report job is submitted, the flexfield filter XML definition produced by the filter input criteria is saved to the filter repository. Oracle Enterprise Scheduler Service start the reporting job with the report parameters including the filter key. The filter key is passed to the flexfield lexical API, which returns the filter criteria as a SQL WHERE clause, which Oracle BI Publisher integrates into the SQL statement for its report.

To incorporate code-combination filters for Oracle BI Publisher reports into a maintenance user interface, you follow much of the same process as you would to implement standard filters. The code-combination filter procedures that follow note which procedures do not apply to these types of filters.

The kff_filter_purge(...) procedure from the fnd_flex_xml_publisher_apis PL/SQL package enables you to remove unused filters from the filter repository.

25.4.3 How to Use Cross-Validation Filters

A cross-validation rule applies a pair of filters to new code combinations that are proposed for a key flexfield by administrators or end users, when you have enabled the rule to work with maintenance mode or dynamic combination insertion.

After enabling cross-validation for a key flexfield at registration time, you must build a maintenance user interface that administrators can use to maintain the implementation-specific filters that make up each rule. All filter combinations that an administrator defines for a given key flexfield are applied automatically to cross validate new code combinations as they are entered.

Note:

Cross-validation rule criteria should generally be created and modified only by application implementors and administrators. For end users, these rules automatically validate new code combinations in the same way that value sets automatically validate new segment values.

To incorporate cross-validation filters, you follow much of the same process as you would to implement standard filters. The code-combination filter procedures that follow note which procedures do not apply to these types of filters.

For more information about implementing cross-validation rules, see Section 24.2.3, "How to Implement Cross-Validation Rules and Custom Validation."

25.4.4 How to Prepare the Database for Standard Code-Combination Filters

A database column of type XMLType is required to store filter data in your database. This column is referred to as the filter-condition column. For standard code-combination filters, you must define a filter-condition column for the filter data in your database before you can associate code-combination filters with key flexfields in your application.

Note:

If you are implementing code-combination filters to support cross-validation rules, the required filter-condition columns already exist in the FND_KF_CROSS_VAL_RULES repository table. If you are implementing code-combination filters for use in the key flexfield filter repository for Oracle BI Publisher reports, these columns exist in the repository.

To prepare a standard code-combination filter for modeling:

1. Select an existing table to contain your filter, or create a new one.

   To create a table called, for example, FND_MYFILTER_KFF1, execute the following script:

   Create table FND_MYFILTER_KFF1 (ID Number primary key, Info Varchar2(1000));
   

2. Use the following Alter script to add a filter-condition column (for example, Filter) of type XMLType to your table:

   Alter table FND_MYFILTER_KFF1 add Filter xmltype
   XMLType column Filter
   Store as BINARY XML
   XMLSCHEMA "http://www.oracle.com/apps/fnd/applcore/flex/kff/FndFilter.xsd"
   ELEMENT  "KeyFlexCodeCombinationFilter";
   

   Note:

   Your new filter-condition column must be configured as nullable.

   This script is necessary because the Database Schema Deployment framework does not support the XMLType data type.

25.4.5 How to Add Code-Combination Filters to Your Application

To add code-combination filters to your application, you complete the following tasks:

1. For standard filters only, create an entity object for the table containing the filter-condition column.

2. Create a view object for the filter entity object.

3. Associate the code-combination filters with key flexfields.

4. Configure, deploy, and test the code-combination filters.

25.4.5.1 Creating a Filter Entity Object for a Standard Filter

For standard filters, you must create a filter-specific entity object for the table containing the filter-condition column.

Note:

You do not need to create a filter view object if you are implementing one of the following types of filters:

• If you are implementing code-combination filters for use in the key flexfield filter repository for Oracle BI Publisher reports, use the existing public entity object oracle.apps.fnd.applcore.flex.kff.model.publicEntity.FndKfEssFiltersPEO, which became available when you added the Applications Core library to your data model project.

• If you are implementing code-combination filters to support cross-validation rules, use the provided configured entity object:

  oracle.apps.fnd.applcore.flex.kff.model.entity.KeyFlexfieldCrossValidationRuleEO
   
  

Before you begin: 

1. Define a database column of XMLType to store the filter as described in Section 25.4.4, "How to Prepare the Database for Standard Code-Combination Filters."

2. To use code-combination filters, you must first have completed the Create Flexfield Business Components wizard for at least one key flexfield, so that your project contains one or more key flexfield business components.

To create the filter entity object: 

1. Create an entity object (for example, Kff1Fltr1EO) for the table containing your filter-condition column.

2. Open the entity object, and, in the overview editor, click the General navigation tab.

3. Expand the Custom Properties section and add the FND_FILTER property with a value of Y.

   This property enables the base classes (OAEntityImpl) to recognize that the entity object contains a filter attribute.

4. Because the column type of the filter attribute is XMLType, which is not natively supported by ADF Business Components, you must edit the attribute to make it a transient attribute that is computed using a SQL expression.

   Click the Attributes navigation tab, select the filter attribute, and click the Edit icon to open the Edit Attribute dialog.

5. In the Entity Attribute dialog, click the Entity Attribute node and change the attribute from a persistent type to a calculated type, as shown in Figure 25-20.

   1. Specify the ClobDomain type.

   2. Select Derived from SQL Expression.

   3. Enter an Expression such as the following expression:

      Kff1Fltr1EO.filter.getClobVal()
      

   Note:

   The GetClobVal() method is needed to manage the XMLType column in the database because ADF Business Components currently does not support the XMLType data type natively.

   Figure 25-20 Edit Filter Attribute — Entity Attribute Page

   
   

6. Click the Custom Properties node, as shown in Figure 25-21.

   Figure 25-21 Edit Filter Attribute — Custom Properties Page

   
   

7. Add the custom filter properties that are listed in Table 25-4 to the filter attribute.

   Table 25-4 Custom Filter Properties

   Name	Value	Description
   FND_FILTER	Y	Indicates that the entity attribute is a filter attribute.
   FND_FILTER_TABLE	table-name	Indicates the name of the underlying table on which this filter attribute is based.
   FND_FILTER_COL	column-name	Indicates the name of the column on which this attribute is based in the filter table. This is needed because the entity object could be based on a database view.
   FND_FILTER_TABLE_COL_PKn	primary-key-column-id	Indicates the primary key column of the underlying filter table. If the table has a composite primary key (for example: ID1, ID2), you must add an entry for each key. For example: FND_FILTER_TABLE_COL_PK1=ID1 FND_FILTER_TABLE_COL_PK2=ID2
   FND_FILTER_TABLE_ATTR_PKn	view-object-attribute-name	Indicates the name of the view object attribute that corresponds to the attribute in the entity object that represents the filter table primary key. If the view object has attributes that correspond to multiple entity object primary key attributes, you must add an entry for each key. For example: FND_FILTER_TABLE_ATTR_PK1=ID1 FND_FILTER_TABLE_ATTR_PK2=ID2

   
   

25.4.5.2 Creating a Filter View Object

You must create a filter view object (for example, Kff1Fltr1VO) for the filter entity object. How you create the view object depends on how you will use the filter:

• If you are implementing a standard code-combination filter, then create the view object for the entity object that you created in Section 25.4.5.1, "Creating a Filter Entity Object for a Standard Filter."

• If you are implementing the filter for use in the key flexfield filter repository for Oracle BI Publisher reports, then create the view object for the public entity object:

  oracle.apps.fnd.applcore.flex.kff.model.publicEntity.FndKfEssFiltersPEO
  

• If you are implementing the filter to support cross-validation rules, then create the view object for the provided cross-validation entity object:

  oracle.apps.fnd.applcore.flex.kff.model.entity.KeyFlexfieldCrossValidationRuleEO
  

  In the view object for the cross-validation rules, define view criteria to set the APPLICATION_ID and KEY_FLEXFIELD_CODE attributes to static values for your application and key flexfield.

25.4.5.3 Associating Code-Combination Filters with Key Flexfields

You use the Create Flexfield Filter wizard to create a view accessor from the filter view object's code-combination filter attribute to a key flexfield view object definition.

Note:

If you are implementing filters to support cross-validation rules, you must complete this procedure twice — once for the condition filter attribute and once for the validation filter attribute.

To associate a code-combination filter with a key flexfield: 

1. In the Application Navigator, right-click the project and choose New.

2. In the New Gallery, expand Business Tier, select ADF Business Components and then Flexfield Filter, and click OK.

3. In the Filter Accessor dialog, expand the available view objects in your current project on the left-hand list and select the view object attribute that corresponds to the XML Filter column as shown in Figure 25-22.

   Figure 25-22 Filter Accessor Dialog

   
   

4. Expand the available flexfields in your current project on the right-hand list and select a key flexfield to be filtered.

5. Enter a name for the filter accessor (with no spaces), then click OK.

25.4.5.4 Configuring, Deploying, and Testing Code-Combination Filters

The final task is to configure the view object, add it to a new application module for the filter, and test it.

To configure, deploy, and test a code-combination filter: 

1. Open the filter view object and click the General navigation tab.

   This property enables the base classes (OAViewRowImpl) to recognize that the view object row contains a filter attribute.

2. Expand the Custom Properties section and add the property FND_FILTER with the Value set to Y.

3. Click the Attributes navigation tab and select the filter attribute.

4. Expand the Custom Properties section and add the property FND_ACFF_SIN for the selected filter attribute. Set the Value to the structure instance number (SIN).

   This property indicates the view object's SIN attribute that is associated with this filter attribute.

5. Create an application module for the filter. In the Data Model page, move the filter view object to the Data Model list. In the Application Modules page, move the flexfield application module, which was created when you created the flexfield business component, to the Selected list.

   Tip:

   You also can add the flexfield application module from the Application Module Instances section in the Data Model navigation tab for the application module.

6. Run the Business Component Browser to ensure that all attributes appear.

25.4.6 How to Employ Code-Combination Filters on an Application Page

You must add the filter view accessors that you created to an application page. This procedure applies to conventional key flexfield filters as well as to filters that you are implementing for use in the filter repository.

25.4.6.1 Adding Your Key Flexfield Filter to an Application Page

You add a key flexfield filter to an application page by dropping the filter view object onto the page and modifying the XML code for the filter component.

Note:

This procedure is also used to produce a user interface for defining and maintaining cross-validation rules. Complete the procedure twice — once for the accessor of the condition filter and once for the accessor of the validation filter. Both filters can be exposed on the same page.

Before you begin: 

• Ensure that the Applications Core (ViewController) tag library has been added to the user interface project, as described in Section 3.4, "Adding the Applications Core Tag Library to Your User Interface Project."

• Create the view object, view accessor, and application module for the code-combination filter as described in Section 25.4.5, "How to Add Code-Combination Filters to Your Application."

To add your key flexfield filter to an application page: 

1. In your project, create a new JSPX page.

2. Drag and drop the view object that contains your filter from the Data Controls panel onto the page as an ADF Form or an ADF Table. Figure 25-23 shows the filter view object dropped onto the page as a form.

   Figure 25-23 Filter Dropped onto a Page as an ADF Form

   
   

3. Ensure the CreateInsert and Commit actions are included on the page.

   Note:

   These actions enable dynamic creation of new filter definitions at runtime; they also enable you to insert new records into the filter repository.

4. If you dropped the view object onto the page as an ADF Table, select the filter column and, in the Property Inspector, set Sortable to false.

   Note:

   The sorting of filter columns is not supported.

5. If you dropped the view object onto the page as an ADF Form, select the filter component and, in the Property Inspector, enter the name of the flexfield in the Label field. This name is used in the title of the filter popup dialog.

6. By default, end users can choose to match on all conditions or any condition, as shown in Figure 25-24 and Figure 25-25. If you want to restrict the filter to match on all conditions only, add the RestrictConjunctionToAND property and set it to true, as shown in Example 25-13 and Example 25-14

   Example 25-13 Modified Form-Based Filter Code

   <fnd:keyFlexFilter value="#{bindings.Kff1Fltr1_1Iterator}"
       accessor="kff1"
       label="#{bindings.Filter.hints.label}" 
       id="kff1"
       restrictConjunctionToAND="true"/>
   

   Example 25-14 Modified Table-Based Filter Code

   <af:column sortProperty="Filter" sortable="false"
              headerText="#{bindings.Kff1Fltr1_1.hints.Filter.label}"
              id="c2">
     <fnd:keyFlexFilter value="#{bindings.Kff1Fltr1_1Iterator}"
                        accessor="kff1" id="kff1"
       restrictConjunctionToAND="true"/>
   </af:column>
   

   When this property is set to true, the Filter dialog box does not display the Match options, as shown in Figure 25-26. All of the conditions for the same segment are joined with an OR operator and then combined with the conditions for other segments using an AND operator, as shown in Example 25-15.

   Example 25-15 Joining of Segments When restrictConjunctionToAND is true

   (SEGMENT1 = 'A' or SEGMENT = 'B') AND (SEGMENT2 = 10 OR SEGMENT2 = 20)
   

   Figure 25-24 Form-Based Code-Combination Filter User Interface

   
   

   Figure 25-25 Table-Based Code-Combination Filter User Interface

   
   

   Figure 25-26 Filter Dialog When RestrictConjunctionToAND is Set to true

   
   

25.4.6.2 What Happens When You Add a Filter-Repository Filter to an Application Page

When you add a filter based on the public entity object FndKfEssFiltersPEO to your application page as an ADF Form, the resulting Oracle BI Publisher report submission user interface appears as shown in Figure 25-27.

Figure 25-27 Form-Based Report Submission Code-Combination Filter UI

When you add a filter-repository filter to the application page as an ADF Table, the report submission user interface appears as shown in Figure 25-28.

Figure 25-28 Table-Based Report Submission Code-Combination Filter UI

When you click CreateInsert, a new row is added that includes the filter XML and other required input, along with the default values for some of the columns. Your application must provide defaults for the columns as described in Table 25-5.

Table 25-5 Filter Repository Filter Attribute Columns

Column	Description
KeyFlexfieldCode	The code identifying the key flexfield to which this filter will be applicable.This is a read-only value.
StructureInstanceNumber	This is the SIN, the discriminator attribute for the key flexfield that is used in the key flexfield filter. While creating a new filter definition or submitting a new job, a valid value should be the default for this attribute at the view object level. The SIN is required for capturing the filter XML. This is a read-only attribute.
DataSetNumber	The data set number (DSN) is a secondary discriminator to the SIN. If the key flexfield is data set-enabled, a valid DSN value should be the default in the filter view object. This is a read-only attribute.
FilterId	The FilterId is the primary key attribute and is a unique identifier for each filter that is inserted in the filter repository. This value can be generated using a sequence or other methods for generating unique identifiers.
ApplicationShortName	This is the application short name of the application with which the flexfield filter is associated. You should set the default value to be the application with which your key flexfield is associated. For example, if you are using flexfield KFF1, which is associated with the Application Object Library application, your filter repository should set the default value for ApplicationShortName to be FND. This is a read-only attribute.
Filter	This is the XML attribute containing the WHERE condition that is set for a particular FilterId. The WHERE condition has to be populated by using the filter user interface. Depending on the SIN, the filter user interface displays the related segments for a particular key flexfield structure. Various conditions for each of the segments can be applied to generate the WHERE condition.

When you click Commit, the new row is inserted in the FND_KF_ESS_FILTERS database table.

25.4.7 How to Create Code-Combination Filter Definitions for Testing

For testing, you can use INSERT scripts to insert predefined XML filter criteria into the filter-condition column of your product table.

Note:

You can insert this data at any time after the filter-condition column has been added to the product table, and before the filter is invoked.

Use the following form to build an INSERT script:

Insert into filtercolumnname values(indexnum, 'filterconditionname', XMLType('filter_xml_code'))

The operators supported for code-combination filters are the operators supported in the Query panel. This includes the following data types and their operators:

• STRING data type: EQUALTO, NOTEQUALTO, CONTAINS, DOESNOTCONTAIN, LIKE, STARTSWITH, ENDSWITH, ISNULL, ISNOTNULL

• NUMBER data type: EQUALTO, NOTEQUALTO, NULL, ISNOTNULL, GREATERTHAN, LESSTHAN, GREATERTHANEQUALTO, LESSTHANEQUALTO, BETWEEN, NOTBETWEEN

• DATE data type: ISNULL, ISNOTNULL

You can also use the following hierarchical operators to query tree structures in your filter: IS_CHILD_OF, IS_DESCENDENT_OF, IS_LAST_DESCENDENT_OF, IS_PARENT_OF, IS_ANCESTOR_OF, IS_FIRST_ANCESTOR_OF, IS_SIBLING_OF.

For more information about trees, see Chapter 19, "Organizing Hierarchical Data with Tree Structures."

Example 25-16 shows some example scripts. The first one inserts a filter condition that selects for SEGMENT1_VARCHAR2 = 'Value04', and the second one selects for the inequality SEGMENT1_VARCHAR2 != 'Value02'.

Example 25-16 Scripts for Inserting Filter Conditions into the Application Database

Example of EQUALTO filter:

Insert into KFF1_FLTR values(
1, 'EqualToFilter',
XMLType('<?xml version ="1.0" encoding ="UTF-8"?>
<FndFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/apps/fnd/applcore/filter/FndFilter.xsd">
  <KeyFlexFilter>
     <keyFlexfieldCode>KFF1</keyFlexfieldCode>
     <structureInstanceCode>VS_IND_CHR_ON_CHR</structureInstanceCode>
     <applicationShortName>FND</applicationShortName>
     <filterCriteriaRow>
        <filterCriteriaItem>
           <attributeName>_L10</attributeName>
           <columnName>SEGMENT1_VARCHAR2</columnName>
           <operator>EQUALTO</operator>
           <conjunction>AND</conjunction>
           <valueDataType>STRING</valueDataType>
           <value>Value04</value>
           <properties>
              <property>
                 <name>TestProp</name>
                 <value>ValueProp</value>
              </property>
           </properties>
        </filterCriteriaItem>
        <properties>
           <property>
              <name>TestProp</name>
              <value>ValueProp</value>
           </property>
        </properties>
     </filterCriteriaRow>
  </KeyFlexFilter>
</FndFilter>'));

Example of NOTEQUALTO filter:

Insert into KFF1_FLTR values(
2, 'NotEqualToFilter',
XMLType('<?xml version ="1.0" encoding ="UTF-8"?>
<FndFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/apps/fnd/applcore/filter/FndFilter.xsd">
  <KeyFlexFilter>
     <keyFlexfieldCode>KFF1</keyFlexfieldCode>
     <structureInstanceCode>VS_IND_CHR_ON_CHR</structureInstanceCode>
     <applicationShortName>FND</applicationShortName>
     <properties>
        <property>
           <name>TestKff</name>
           <value>Valkff</value>
        </property>
     </properties>
     <filterCriteriaRow>
        <filterCriteriaItem>
           <attributeName>_L10</attributeName>
           <columnName>SEGMENT1_VARCHAR2</columnName>
           <operator>NOTEQUALTO</operator>
           <conjunction>AND</conjunction>
           <valueDataType>STRING</valueDataType>
           <value>Value02</value>
           <properties>
              <property>
                 <name>TestItemName</name>
                 <value>ValItem</value>
              </property>
           </properties>
        </filterCriteriaItem>
        <conjunction>AND</conjunction>
        <properties>
           <property>
              <name>TestRowName</name>
              <value>ValRow</value>
           </property>
        </properties>
     </filterCriteriaRow>
  </KeyFlexFilter>
</FndFilter>'));

Note:

You might want to test these scripts to ensure that the database is, in fact, performing schema validation on the XML document, by attempting to insert XML that does not conform to this schema.

25.4.8 How to Apply Code-Combination Filters Using the PL/SQL Filter APIs

You can take advantage of code-combination filters (including filter-repository filters) without using them in your application user interface. You use the WHERE clause API for standard and cross-validation combination filters, and you use the XML API for filter repository filters.

25.4.8.1 Applying Standard Filters Using the WHERE Clause API

Applications Core provides a PL/SQL API for filtering at the back end. This API takes a filter condition as an input parameter in XMLType format, converts it to a SQL WHERE clause, and provides the clause as an output parameter for the segments upon which the filter condition has been defined. You use this API to integrate the WHERE clause into your SQL statements to include the filter conditions within your SQL scripts.

The PL/SQL combination filter WHERE clause API is based on the signature shown in Example 25-17.

Example 25-17 WHERE Clause Signature

FND_KF_COMBINATION_FILTER_API.BuildWhereClause(
                               filter            IN XMLType,
                               tableAlias        IN Varchar2,
                               bindPrefix        IN Varchar2,
                               sin               OUT Number,
                               bindValues        OUT NOCOPY BIND_VAL_TAB,
                               filterWhereClause OUT NOCOPY Varchar2);
 
/** ---------------------------------------------------------------------------
--  This procedure computes the WHERE clause for the filter
--  and provides it in the filterWhereClause parameter
--  Params
--     IN Params
--         filter      XMLType  - Filter to be converted to SQL clause
--         tableAlias  Varchar2 - Alias table name to be used in SQL clause
--         bindPrefix  Varchar2 - Bind Prefix
--      OUT Params
--         sin         Number         - Structure Instance Number
--         bindValues  BIND_VAL_TAB   - List of Bind Values
--         filterWhereClause Varchar2 - WHERE clause
----------------------------------------------------------------------------*/
 

The bind values are defined as shown in Example 25-18.

Example 25-18 Bind Values Definition

create or replace PACKAGE FND_KF_COMBINATION_FILTER_API AS
 
  VARCHAR_TYPE CONSTANT Varchar2(20) :='VARCHAR2';
  NUMBER_TYPE  CONSTANT Varchar2(20) :='NUMBER';
  DATE_TYPE    CONSTANT Varchar2(20) := 'DATE';
 
TYPE BIND_VALUE IS RECORD(
         NAME            Varchar2(30),
         TYPE            Varchar2(20),
         VALUE_VARCHAR2  Varchar2(32767),
         VALUE_NUMBER    Number,
         VALUE_DATE      Date);
 
 TYPE BIND_VAL_TAB IS TABLE OF BIND_VALUE INDEX BY BINARY_INTEGER;

Example 25-19, Example 25-20, and Example 25-21 demonstrate how to use the WHERE clause API for an EQUALTO condition, a BETWEEN condition, and multiple conditions.

Example 25-19 Using the WHERE Clause API for an EQUALTO Condition

Suppose that a filter condition has been defined in a combinations table as follows:

• Combinations table = FND_KF_TEST_CCT1

• Filter column = SEGMENT1_VARCHAR2

• Filter condition = 123

You would call the filter API as follows:

FND_KF_COMBINATION_FILTER_API.BuildWhereClause(
                              filter=>v_filter,
                              tableAlias => 'FKFF1',
                              bindPrefix => 'BND',
                              sin => v_sin,
                              bindValues => v_bind,
                              filterWhereClause => v_query);
 

Use the tableAlias value in WHERE clauses to represent the combinations table name. In this example, FND_KF_TEST_CCT1.SEGMENT1_VARCHAR2 should be entered as FKFF1.SEGMENT1_VARCHAR2.

Similarly, use the bindPrefix value as a prefix when referencing individual bind values, for example, :BND1, :BND2, or :BND3.

When invoked, the filter API in this example might produce the following values for its output parameters:

filterWhereClause - FKFF1.SEGMENT1_VARCHAR2 = :BND1
sin               - 12
bindValues        - bindValues(1).NAME = BND1
                  - bindValues(1).TYPE = VARCHAR2
                  - bindValues(1).VALUE_VARCHAR2 = 123
 

With this output, you can assemble the following WHERE clause for an EQUALTO filter condition:

select code_combination_id,
       Segment1_VARCHAR2
from FND_KF_TEST_CCT1 FKFF1
where FKFF1.structure_instance_number=12
and  FKFF1.SEGMENT1_VARCHAR2=123
 

Example 25-20 Using the WHERE Clause API for a BETWEEN Condition

The following shows an example of a BETWEEN operator used as part of a filter expression of the form "attribute BETWEEN value1 AND value2".

<?xml version ="1.0" encoding ="UTF-8"?>
<KeyFlexCodeCombinationFilter
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation=
"http://www.oracle.com/apps/fnd/applcore/flex/kff/KeyFlexCodeCombinationFilter.xsd">
  <keyFlexfieldCode>KFF1</keyFlexfieldCode>
  <structureInstanceCode>VS_FRM_NUM_ON_CHR</structureInstanceCode>
  <applicationShortName>FND</applicationShortName>
  <filterCriteriaRow>
    <filterCriteriaItem>
      <attributeName>_P6_S0</attributeName>
      <columnName>SEGMENT1_Varchar2</columnName>
      <operator>BETWEEN</operator>
      <conjunction>AND</conjunction>
      <valueDataType>NUMBER</valueDataType>
      <value>-500</value>
      <value>1000</value>
    </filterCriteriaItem>
  </filterCriteriaRow>
</KeyFlexCodeCombinationFilter>'));

The filter expression captured in the preceding XML resolves to the following:

SEGMENT1_VARCHAR2 BETWEEN -500 and 1000

You would call the filter API as follows:

FND_KF_COMBINATION_FILTER_API.BuildWhereClause(
                              filter=>v_filter,
                              tableAlias => 'FKFF1',
                              bindPrefix => 'BND',
                              sin => v_sin,
                              bindValues => v_bind,
                              filterWhereClause => v_query);
 

When invoked, the filter API in this example might produce the following values for its output parameters:

filterWhereClause - FKFF1.SEGMENT1_VARCHAR2 = :BND1
sin               - 12
bindValues        - bindValues(1).NAME = BND1
                  - bindValues(1).TYPE = VARCHAR2
                  - bindValues(1).VALUE_VARCHAR2 = -500
                  - bindValues(2).NAME = BND2
                  - bindValues(2).TYPE = VARCHAR2
                  - bindValues(2).VALUE_VARCHAR2 = 1000
 

With this output, you can assemble the following WHERE clause for a BETWEEN filter condition:

select code_combination_id,
       Segment1_VARCHAR2
from FND_KF_TEST_CCT1 FKFF1
where FKFF1.structure_instance_number=12
and  FKFF1.SEGMENT1_VARCHAR2 BETWEEN -500 AND 1000
 

Example 25-21 Using the WHERE Clause API for Multiple Conditions

The following shows an example of multiple operators used as part of a filter expression of the form "attribute1 EQUALTO value1 AND attribute2 EQUALTO value2."

<?xml version ="1.0" encoding ="UTF-8"?>
<KeyFlexCodeCombinationFilter
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:noNamespaceSchemaLocation="http://www.oracle.com/apps/fnd/applcore/flex/kff/KeyFlexCodeCombinationFilter.xsd">
  <keyFlexfieldCode>KFF1</keyFlexfieldCode>
  <structureInstanceCode>VS_FRM_NUM_ON_CHR</structureInstanceCode>
  <applicationShortName>FND</applicationShortName>
  <filterCriteriaRow>
    <filterCriteriaItem>
      <attributeName>_P6_S0</attributeName>
      <columnName>SEGMENT1_Varchar2</columnName>
      <operator>EQUALTO</operator>
      <conjunction>AND</conjunction>
      <valueDataType>NUMBER</valueDataType>
      <value>123456</value>
    </filterCriteriaItem>
    <filterCriteriaItem>
      <attributeName>_P6_S2</attributeName>
      <columnName>SEGMENT2_Varchar2</columnName>
      <operator>EQUALTO</operator>
      <conjunction>AND</conjunction>
      <valueDataType>NUMBER</valueDataType>
      <value>123.45</value>
    </filterCriteriaItem>
  </filterCriteriaRow>
</KeyFlexCodeCombinationFilter>'));

The filter expression captured in the preceding XML resolves to the following:

SEGMENT1_VARCHAR2 = 123456 and SEGMENT2_VARCHAR2 = 123.45

You would call the filter API as follows:

FND_KF_COMBINATION_FILTER_API.BuildWhereClause(
                              filter=>v_filter,
                              tableAlias => 'FKFF1',
                              bindPrefix => 'BND',
                              sin => v_sin,
                              bindValues => v_bind,
                              filterWhereClause => v_query);
 

When invoked, the filter API in this example might produce the following values for its output parameters:

filterWhereClause - FKFF1.SEGMENT1_VARCHAR2 = :BND1
                  - FKFF1.SEGMENT2_VARCHAR2 = :BND2
sin               - 12
bindValues        - bindValues(1).NAME = BND1
                  - bindValues(1).TYPE = VARCHAR2
                  - bindValues(1).VALUE_VARCHAR2 = 123456
                  - bindValues(2).NAME = BND2
                  - bindValues(2).TYPE = VARCHAR2
                  - bindValues(2).VALUE_VARCHAR2 = 123.45
 

With this output, you can assemble the following WHERE clause for a BETWEEN filter condition:

select code_combination_id,
       Segment1_VARCHAR2, Segment2_VARCHAR2
from FND_KF_TEST_CCT1 FKFF1
where FKFF1.structure_instance_number=12
and  FKFF1.SEGMENT1_VARCHAR2 = 123456
and  FKFF1.SEGMENT2_VARCHAR2 = 123.45
 

25.4.8.2 Applying Repository Filters for Oracle Enterprise Scheduler Service

The kff_filter PL/SQL procedure in the fnd_flex_xml_publisher_apis.pkb package is the public procedure for processing key flexfield repository filter lexicals. The signature is shown in Example 25-22.

Example 25-22 kff_filter Signature

/* PUBLIC PROCEDURE kff_filter EXPOSED FOR THIS PACKAGE */
 
PROCEDURE kff_filter
  (p_lexical_name                 IN VARCHAR2,
   p_application_short_name       IN fnd_application.application_short_name%TYPE,
   p_key_flexfield_code           IN fnd_kf_flexfields_b.key_flexfield_code%TYPE,
   p_filter_id                    IN NUMBER,
   p_code_combination_table_alias IN VARCHAR2,
   x_where_expression             OUT nocopy VARCHAR2,
   x_numof_bind_variables         OUT nocopy NUMBER,
   x_bind_variables               OUT nocopy bind_variables);
 

Example 25-23 demonstrates how to use this API to obtain the WHERE clause and bind variable information for a filter in the filter repository.

Example 25-23 Using the Filter Repository API

REM dbdrv: none
SET SERVEROUTPUT ON
WHENEVER SQLERROR CONTINUE
DECLARE
  l_tableAlias VARCHAR2(30);
  l_applicationShortName fnd_application.application_short_name%TYPE;
  l_keyFlexfieldCode fnd_kf_flexfields_b.key_flexfield_code%TYPE;
  l_filterWhereClause         VARCHAR2(32767);
  l_filterId                  NUMBER;
  l_filterName                VARCHAR2(32);
  l_bindVariables fnd_flex_xml_publisher_apis.bind_variables;
  l_numOfBindVariables NUMBER;
  CURSOR c_filter_id
  IS
    SELECT filter_id FROM fnd_kf_ess_filters;
BEGIN
 
    l_filterName           := 'DefaultFilter';
    l_tableAlias           := 'DefaultTable';
    l_keyFlexfieldCode     := 'KFF1';
    l_applicationShortName := 'FND';
    DBMS_OUTPUT.PUT_LINE('kff_filter');
    FOR filter_id IN c_filter_id
    LOOP
 
      fnd_flex_xml_publisher_apis.kff_filter(p_lexical_name=>l_filterName,
      p_application_short_name=>l_applicationShortName,
      p_key_flexfield_code=>l_keyFlexfieldCode,
      p_filter_id=>filter_id.filter_id,
      p_code_combination_table_alias=>l_tableAlias,
      x_where_expression=>l_filterWhereClause,
      x_numof_bind_variables=>l_numOfBindVariables,
      x_bind_variables=>l_bindVariables);
      DBMS_OUTPUT.PUT_LINE('filter Id: ' || filter_id.filter_id);
      DBMS_OUTPUT.PUT_LINE('filter Where Clause: ' || l_filterWhereClause);
    END LOOP;
  END;

25.4.9 How to Remove Code-Combination Filters from Your Application

To remove a code-combination filter, you remove the accessor that was previously created to associate the filter with a particular key flexfield.

In your project, right-click the view object that contains the filter and select Remove Flexfield Filters from the menu.

If the filter view object has more than one filter attribute with an accessor defined, then you will be presented with a list of those filter accessors. Select the one that you want to remove.

25.4.10 How to Remove Filters from the Filter Repository

The filter XML is stored in the FND_KF_ESS_FILTERS table. The number of rows in a filter repository can become large. You use the kff_filter_purge(...) procedure from the fnd_flex_xml_publisher_apis PL/SQL package to purge unused filters from the filter repository. This procedure takes the filter's ID, as shown in Example 25-24.

Example 25-24 Removing a Filter from the Filter Repository

DBMS_OUTPUT.PUT_LINE('kff_filter_purge to delete a valid filter');
l_filterId := 1001; 
--valid filter id
fnd_flex_xml_publisher_apis.kff_filter_purge(p_filter_id=>l_filterId);
DBMS_OUTPUT.PUT_LINE('Filter Id: ' || l_filterId);
DBMS_OUTPUT.PUT_LINE('VALID FILTER_ID DELETED SUCCESSFULLY');