q Click On APPLY Button
q In the next screen provide the “Description” same as the name
q Here you will get the columns associated with view selected (Ex. XX_TEST_V here) as Attributes
q Do the following changes against Display Name, Map To List Entries, Display In List Entries and Define LOV, Chart
q Just update the appropriate Display Name against each attributes.
q Click on ‘Search’ symbol, right side of the Map to List Entries column.
q Search by Column name and select the appropriate column.
Ø Check the check box against the attributes, which you want to show in the List/Campaign result.
***Note: If you define an SQL query for an LOV, ensure that it does not
return a null value for the columns you selected.
Ø Click on the symbol ‘Define LOV, Chart’ ageist an attribute
Ø Select the appropriate option for “Add List of Values Using”
>> Steps to do when you select “Custom SQL” as “Add List of Values Using”
v Click on “Go”
v Give the appropriate “List of Values Name” and “Custom SQL” value.
v Click on “Validate SQL”
v It will give the confirmation of valid query, click on “Apply”
>> Steps to do when you select “Another List of Values” as “Add List of Values Using”
v Case when you want to reuse the same LOV
v Click on “Go”
v It will Create a “Search” Button against “List of Value Name”
v Search (Click on the search Button) the appropriate LOV and Select.
v It will automatically populate Custom SQl, validate query and apply, as you did before.
q Steps to do when you select “Manual Entries” as “Add List of Values Using”
v Click on “Go”
v Then Click on “Add Another Row” Button
v Give the appropriate “List of Values Name” and add as many rows you need to add and “Apply” button.
Sales Query Templates enable the end users to quickly and easily create lists. When creating a list, end user hastwo different list template options to choose from:
• Standard List Template: You can specify conditions using the Natural Language
Query (NLQ) template, just as you would naturally think of them. You can define
your selection criteria by selecting the operator and the corresponding value for
each condition.
• Parameterized SQL List Template: The Parameterized SQL List Template is based
on an SQL statement with parameters you provide to your user. Users must enter
values from the selection parameters while creating a list.
Custom Sales Query Template can be created with additional user-defined Conditions to meet business requirements. These query templates can be based on the out-of-box data sources (Organization Contacts, Persons, Organizations) or any user-defined data source (for example, an industry-specific data mart). You can create these templates using either the Standard or Parameterized SQL template options.
Prerequisites – You should have a Data Source ready to use
You must configure the attributes that you will use to create a query, which are actually the parameters for the query while getting the Campaign List.
Attribute Name
|
Description
|
Attributes
|
The attributes that you define for the query
template. These are actually the parameters (where clause) of the query used at the time of Campaign List creation
|
Attribute Display Name
|
The name of the attribute as it is displayed in
the table. For example: an attribute “ZIP Code “might have “Postal Code”
as the display name.
|
Available Operators
|
These are specific operators that you may
choose from to add value to the attribute. For
example: you can define the operator 'is
between' , 'is after' for the “Contract Date”.
***Note :- You can choose more than one operator for an attribute.
|
Operator Default
|
Choose the default value of the operator
|
Value
|
List of values.
|
Value Display
|
Description against the attribute what to do, whether to enter the value manually or pick from the LOV
|
Default
|
If checked, this value is the default.
|
Mandatory
|
If checked, this attribute is the mandatory
|
In Use
|
Shows if the attribute is in use already.
***Note :- If it is checked, you cannot modify (update/delete) that attribute.
|
Go to Marketing Administrator Responsibility ->Campaign Dashboard
-> Right side in Related Link Click on Audience Workbench
-> Right side in Related Link Click on Audience Administrator
-> Right side in Shortcuts Click on Create Query Template
q Enter the following values and Click on ‘Continue’ button
Field
|
Value
|
Query Template Name
|
Test Sales Query Template
|
Query Template Type
|
Standard
|
Data Source Name
|
Test Data Source
|
Purpose
|
It is required to select a purpose. Purpose types are seeded, however, you can extend the purpose type lookup.
Seeded Purpose Types:
• Cross Sell: Used for creating lists for the cross selling purposes.
• Customer Acquisition: Used for creating lists for customer acquisition purposes.
• Customer Retention: Used for creating lists for customer retention purposes.
• General Purpose: Used for creating general lists (not tied to a specific purpose).
• Lead Maturation: Used for creating lead maturation lists
• Up Sell: Used for creating lists for up selling purposes.
>> Select as per your requirement, here we have selected “ALL”
|
Add Following attributes by clicking ‘Add Another Row’ Button.
Ø How to add an attribute
Click on Search button ‘Select the Attribute Value’ for “Attributes”
It will open an window with all the columns associated with the view linked with the related Data Source, ‘Select to Expand’ all the attributes/columns
Creating Self Joins for Complex Queries - * Not in the scope of this implementation
In Oracle Marketing you can create a Self Join for complex queries. A Self Join is a query in which a table is joined to itself. You can use a Self Join to compare the values in a column with other values in the same column of the same table. This way you can get running counts and running totals in the SQL query. You can create a Self Join at the attribute stage.
Select the appropriate attribute
Now click on the Search button for ‘Available Operators’ and select the appropriate operator/operators.
Next to click on Search button for ‘Operator Default’ and select the operator defaulted
Steps to select ALL VALUE for “Value “
Click on Search button for “Value” and select ‘List of Values’ from LOV and click on Go
Select all the values populated
v Example 1
v Example 2
Based on the operator selected please enter user friendly text in the ‘Value Display’ field, like “:Enter a Value”, “:Pick any value”
Click on ‘Validate’ for ‘SQL validation’ and then on ‘Back’
After adding all the attributes and associated values, click on ‘Finish’ button
*** We are done with the Setups***
Include a subset of test steps that will confirm that the setup has been done properly and working fine.
Please navigate to Marketing Administrator Responsibility -> Campaign Dashboard -> Audience Workbench. This should open Activities Page. Click on Create List button.
Enter the required fields
Field
|
Value
|
List Name
|
xx_test_list
|
Audience
|
Test Data Source
|
List Creation Method
|
Standard
|
List Template Name
|
Test Sales Query Template
|
Click on ‘Continue’ button
In next window default conditions/parameters/attributes will automatically be populated in ‘Selection’ section
Enter the values for those parameters.
Add more conditions by clicking on ‘Add Conditions’ button
Enter the values for the selected conditions and click on ‘Finish’
Click on ‘Generate List’
Refresh list status to generate the list ‘Status’ will change from draft->Scheduled-> Generating > Available
List Generation Status
• Draft: when the recalculation table is not available.
• In Progress: when you click the Apply button in the NLQB template definition
page. This implies that the concurrent request is submitted and recalculation table
generation is in process.
• Available: when the recalculation table generates successfully.
• Failed: when the recalculation table generation fails. Click the Apply button againto restart the recalculation table generation process
After changing the status to ‘Available’, check the generated list by clicking on ‘Entries’ tab.
Check the list summary by clicking on Selections->statistics tab.
There are few important points to remember while creating the custom list management process.
v Unique key limitations.
Ø While defining Data Sources, you need to mention the Unique Key, which should be one of the columns of the view used.
Ø Now while defining Sales Query Templates we define different parameters but Unique Key gets priority over all others and be seen as duplicate record in Campaign/List result.
Ex:- suppose we defined Party_id as a Unique Key, and party name, City, State, Service Contract etc. as other parameters in Sales Query Template.
While getting the Campaign/List we passed the parameter State as ‘WB’ and it gave three results as :
246836 Cognizant Technology Solutions Kolkata WB 23789
246836 Cognizant Technology Solutions Bardhman WB 68456
246836 Cognizant Technology Solutions Ramput Hat WB 98799
Even if these three rows are different, campaign/List results will be treated as Duplicate results, because Party_id is the Unique Key.
Solution :- If you want to see these three rows as unique rows, create a custom column, combining values of different columns, like here you can define a custom column by combining (party_id + territory_id + contract_number)
v Re-creation of Sales Query Templates.
Ø Once you done with creating Sales Query Template and used once, you cannot modify in between, means you cannot add new parameter in between. You can only add at the end, so get the final list and order of the parameters to be defined in the Template before creating.
v For using ‘Like’ operator, it needs to provide % operator explicitly in the parameter value.
v Parameter values cannot be handled through front end, it can only be handled while creating database view. Ex. For free form fields, you cannot allow user to enter text in any case, it should be fixed either in upper case or in lower case or the case which can be handled at the time of view creation.
v Profile Options values cannot be retrieved in view used, of course it can be retrieved when you fetch from application.
v No FNDLOAD available to download LDT of these setups, you have to do it manually if you want to migrate it to another instance
v List in 'Scheduled' Status.
Ø While creating List, sometimes List status always be there in ‘Scheduled’ status.
Ø Cause: The lock is in Workflow process at AMS_LIST_INIT_DET LIST_GENERATION
Ø Solution: To implement the solution, please execute the following steps:
1) Bounce the Concurrent Manager if Workflow Agent Listener is already running.
2) If after bouncing the Concurrent Manager it still does not work then restart the Workflow Agent Listener for (Deferred) events.
v Error: Query has exceeded <N number> rows. Potentially more rows exist, please restrict your query.
Ø When your LOV query returns more rows, you can get the above error at the time of List generation.
Ø Cause: The profile option 'FND: View Object Max Fetch Size' was set at 100. This profile option controls the number of rows displayed in the OA Framework.
Ø Solution: Increase the value for the profile option 'FND: View Object Max Fetch Size'. The default value is 200, but greater values can affect performance.
This white paper provides the basic features of Custom list management process, we have more sophisticated features like parent-child Data Source, parameterized Sales Query Template etc. which also have some limitations, but those parts have not been discussed here. Again it depends upon business requirement which way we can achieve.
To implement custom list management process we have more setup related activities rather than technical work (creating database view/table and writing SQL queries for list of values). We have few limitations while creating list management process but most of the business requirements can be achieved through it which cannot be done through seeded Data Sources and Sales Query Templates.
