This chapter covers the following topics:
In order for users to access Oracle Territory Manager, you need to assign roles to each user in addition to assigned the Territory Management responsibility. When users log in to Oracle Applications, all menu items associated to their assigned roles appear on their personal home pages. The following roles are available for assignment:
Sales Team Search User: Provides access to the stand-alone sales team search
Territory Reports User
Sales Territory Reports User (includes Territory Reports User)
Sales Territory User (includes Sales Team Search User)
Territory Manager Application Administrator
Sales Territory Administrator (includes Sales Team Search User and Sales Territory Reports User)
Collections Territory Administrator (includes Territory Reports User)
Partner Management Territory Administrator (includes Territory Reports User)
Service Contracts Territory Administrator (includes Territory Reports User)
Service Territory Administrator (includes Territory Reports User)
Field Service Territory Administrator (includes Territory Reports User)
Trade Management Territory Administrator (includes Territory Reports User)
See the Oracle E-Business Suite Security Guide for information on how to assign roles to users.
When properly set up, users in Oracle Territory Manager can make territory changes in different operating units.
Create a Global Security Profile in HRMS using the HRMS Management Responsibility > HRMS Manager > Security > Global Security Profile. Enter a Security Type: Secure organizations by organization hierarchy and/or organization list.
Set the MO: Security Profile profile option value at the Territory Management responsibility level to the security profile that you just created.
Run the Security List Maintenance concurrent program. You can run the program to process just your security profile by entering the following parameters:
Set Generate Lists for to One Named Security Profile
Set Security Profile to the profile you created in step 1.
Log into the Oracle Territory Manager application and verify that the multiple operating units appear in the Operating Unit dropdown list in either the Territory tab, Territory Type tab, or the Enable Matching Attributes page. If they do not appear, try bouncing Apache.
If you have VAT and want to use geographic matching attributes for the Sales usage, then you need to set up the geographies so that they appear in the lists of values (LOVs) for territory qualifiers in the Forms Territory Details window.
As the Sales Administrator, navigate to Sales Setup > Lookup Codes and create the lookup types and values for any of the following lookup types you may need:
TAP_CITY: to add cities
TAP_STATE: to add states
TAP_COUNTY: to add counties
TAP_PROVINCE: to add provinces
For the Sales or Collections usages, the system administrator can create custom matching attributes based on:
Any APPS schema table column attribute
Third party system table column attribute
To create a custom matching attribute
Obtain script number 1.
Modify the variable
p_source_id if needed. The number in the script is -1001, which is for the Sales usage. If you need to change the usage, query JTF_SOURCES_ALL for the correct number.
Modify the variable
p_trans_type_id to the value for the transaction type your new matching attribute relates to. The value in the script is -1002. You can query JTF_QUAL_TYPES_ALL to determine the correct ID.
Run script number 1, which retrieves the transaction type SQL.
Save the output of script number 1 to a text file, saved as name.sql. This becomes script number 2.
Modify each query from script number 2 to include the attribute you want to use. The queries are:
Real Time SQL
Batch Total SQL
Batch Date of Effectivity
Make a note of the attribute and its alias. You will need this for script number 3.
Near the end of the script, modify values for
p_trans_type_id to the same values as for script number 1.
Modify the value for
p_program_name. You can query JTY_TRANS_USG_PGM_DETAILS for the value.
Modify the values for
Run script number 2, which adds the attribute to the transaction type SQL.
Obtain script number 3, which creates the custom matching attribute (CMA).
Verify that there is not an existing CMA record for the unique matching attribute ID that you will use for the new CMA.
Follow the instructions in the script. Make sure you give a unique qualifier ID for each attribute you create. The ID numbers usually start from 9000. The ID in the script is -9010. Give your new attribute a name and description using the variables
Modify values for
p_trans_type_id to the same values as for scripts 1 and 2.
Specify which columns to use in the values table to store the values for your custom matching attribute.
Enter the alias from step 6 for
Run script number 3.
Enable your new matching attribute.
Associate the matching attribute with a territory type.
If you create a custom matching attribute for the Account transaction, then you must also add the same custom matching attribute to the other Sales transactions.
The values table stores the values for your custom matching attributes. You can query JTF_TERR_VALUES_ALL to see existing the display type and corresponding columns for existing matching attributes. The following table lists the available columns and display types for storing your custom matching attributes.
|CURRENCY||low_value_number, high_value_number, currency_code|
|DEP_2FIELDS_CHAR_2IDS||value1_id, value2_id, value3_id|
|DEP_3FIELDS_CHAR_3IDS||value1_id, value2_id, value3_id, value4_id|
|INTEREST_TYPE||interest_type_id, primary_interest_code_id, secondary_interest_code_id|
Matching attributes are seeded criteria used to identify territories. For example, country is a geographic matching attribute. Oracle Territory Manager has seeded matching attributes for the following usages and transaction types:
Partner Management: partner
Sales and TeleSales: account, lead, opportunity, proposal, quote
Service: service request, service request and task, task
Service Contracts: contract renewal
Trade Management: claim, offer
In addition, you can create your own custom matching attribute through a public API. Before you can assign a matching attribute to a territory type, you need to enable it.
Role: Territory Manager Application Administrator
The Service Request and Task transaction type means tasks are created through a service request. If it is a stand-alone task, use the Task transaction type instead.
You cannot disable a matching attribute that is part of a territory type definition.
The following table lists the transaction matching attributes used by Oracle Sales products and their respective uses. The Account transaction type is also used by Oracle Incentive Compensation.
|Transaction Type||Territory Matching Attribute||Sales/TeleSales Attribute|
|Account||Account Classification||Interest of "party site"|
|Account||Account Hierarchy||"Subsidiary Of" a particular organization|
|Account||Area Code||Area Code|
|Account||Company Annual Revenue||Annual Revenue|
|Account||Customer Category||Customer Category|
|Account||Customer Name||Customer Name|
|Account||Customer Name Range||Customer Name|
|Account||DUNS Number||D-U-N-S Number|
|Account||Number of Employees||Total Employees|
|Account||Site Number||Party Site Num|
|Account||Postal Code||Postal Code|
|Account||Product Hierarchy||Product Category|
|Account||Registry ID||Party Number or Registry ID|
|Account||SIC Code||SIC Code|
|Account||Sales Partner Of (This attribute is available only for Oracle Incentive Compensation)||Partner Of|
|Lead||Lead Product Category||Product Category|
|Lead||Lead Inventory Item||Inventory Item|
|Lead||Lead Source||Source Name|
|Opportunity||Opportunity Channel||Sales Channel|
|Opportunity||Opportunity Product Category||Product Category|
|Opportunity||Opportunity Inventory Item||Inventory Item|
It is important that the territory administrator runs the concurrent programs regularly in order to generate the territories and reflect all changes made.
Synchronize Territory Assignment Rules (STAR): It builds the territory assignment rules, builds the API that returns the winning territories and resources attached to the winning territories, which are defined in territory setup, generates all self-service territories.
Calculate Territory Alignment Metrics: Calculates information such as DNB annual revenue for named accounts for the time period specified in the system profile options Territory Alignment Metric Calculation From Date and Territory Alignment Metric Calculation To Date.
Named Account Territory Post Processing:
Kicks off named account catchall workflow processing.
Refreshes territory administration portals for Sales.
Refreshes the named account and geography distribution information for the Sales User responsibility.
The profile Batch Size for Territory Concurrent Programs commits the Generate Territory Concurrent (GTP) program transaction at regular intervals instead of committing the program after all the transactions are done. GTP populates all the territory details in denormalized form in the territory tables. Instead of committing after forming the denormalized territory details for all the territories, GTP commits after reaching the number of transactions mentioned in profile Batch Size for Territory Concurrent Programs. Doing so improves the performance of GTP. If this profile is not set, the default value for this profile is 50000.
STAR uses the following parameters:
Usage: Select the correct usage from the LOV, such as Oracle Sales and TeleSales or Oracle Service.
Run Mode: Set to Total Refresh the first time you run the concurrent program. Subsequently, set to Incremental Refresh to process only the changes in named account and geographic territories, which saves processing time. Set to Date Effective to assign resources for a specified date range.
Debug Flag: Yes to write debug messages.
If you are implementing sales territories, then you need to set up the Territory Assignment Program per the steps provided in either the Oracle Sales Implementation Guide and the Oracle TeleSales Implementation Guide.
If you want to use Dun & Bradstreet data for your named accounts, or use the DUNS number qualifier, then you need to purchase the data. See the Oracle Trading Community Architecture User Guide for more information.
The metrics DNB Number of Employees and DNB Annual Revenue, used for comparing alignments, depend upon information provided by Trading Community Architecture.
Log in to the Personal Home Page.
Trading Community Manager
Trading Community : Administration : Enrichment : Third Party Data Rules
Click Party Profile Entities.
In the Organization Profile Entity region, click View Attributes.
The View Attributes for Organization Profile Entity page displays a list of attributes.
Select the attribute Employees at Primary Address.
Click Update Individually.
The Select and Rank Data Sources for Attributes: Employees at Primary Address page appears.
Move the data source Dun & Bradstreet from the Select Data Sources box to the Rank Data Sources box and move it to the top of the box ahead of User Entered.
Territory Alignment Metric Calculation From Date (mm/dd/yyyy)
Territory Alignment Metric Calculation To Date (mm/dd/yyyy)
This concurrent program should be run whenever there are changes in the Dun & Bradstreet information, such as number of employees or annual revenue; changes in the desired date range; and when new named accounts are created.
Metric information is now calculated and available to use for territory alignments.
A user who is a member of a sales group and who is assigned the proxy user role can assign the named accounts or geographic territories owned by the manager of that sales group to any member of the sales group hierarchy that reports up to that manager. This role is typically assigned to a sales administrator or sales operations who can then assign accounts in the sales manager's organization.
Using Resource Manager, create a special role for this function of the type Sales and select the Administrator or Member flag.
Then assign the newly created role to a member of the sales group to be the proxy user.
Next, enter the role in the system profile option Self Service Named Account Proxy User Role. The category for this profile is Territory Administration.
When the proxy user logs in, he will see the same list of named accounts and geographic territories as the manager of the sales group that he is the proxy user for.
Territory creation requires the ability to export to and upload from spreadsheet. To use the export to Microsoft Excel and upload from spreadsheet features you need to do the following:
Install the Oracle Web Applications Desktop Integrator.
Enable Initialize and script Active X controls not marked as safe in your browser.
In your Microsoft Excel, set the macro security to low: Tools > Macro > Security.
You need to load geographic data into the table that is used by self-service geography. This section covers the public APIs you use to do so, and provides some sample SQL code. You must purchase your geography data, for example from the United States Postal Service. It needs to contain city, state or province, country, and postal code.
The name of the table that contains the geographic data is JTF_TTY_GEOGRAPHIES.
The types of geography supported in Oracle Territory Manager are:
Postal Code (POSTAL_CODE)
This API creates a geography. This is the API you will use to initially populate geographic data into the JTF_TTY_GEOGRAPHIES table.
Create_geo( p_geo_type IN VARCHAR2, p_geo_name IN VARCHAR2, p_geo_code IN VARCHAR2, p_country_code IN VARCHAR2, p_state_code IN VARCHAR2 default null, p_province_code IN VARCHAR2 default null, p_county_code IN VARCHAR2 default null, p_city_code IN VARCHAR2 default null, p_postal_code IN VARCHAR2 default null, x_return_status IN OUT NOCOPY VARCHAR2, x_error_msg IN OUT NOCOPY VARCHAR2)
DECLARE err_status VARCHAR2(80); err_msg VARCHAR2(255); BEGIN -- to populate country with 'US' JTF_TTY_GEOSOURCE_PUB.create_geo(p_geo_type => 'COUNTRY', p_geo_name => 'United States', p_geo_code => 'US', p_country_code => 'US', p_state_code => null, p_province_code => null, p_county_code => null, p_city_code => null, p_postal_code => null, x_return_status => err_status, x_error_msg => err_msg); -- to populate state with 'California' JTF_TTY_GEOSOURCE_PUB.create_geo(p_geo_type => 'STATE', p_geo_name => 'California', p_geo_code => 'CA', p_country_code => 'US', p_state_code => 'CA', p_province_code => null, p_county_code => null, p_city_code => null, p_postal_code => null, x_return_status => err_status, x_error_msg => err_msg); -- to populate city with 'San Francisco' JTF_TTY_GEOSOURCE_PUB.create_geo(p_geo_type => 'CITY', p_geo_name => 'San Francisco', p_geo_code => 'SAN_FRANCISCO', p_country_code => 'US', p_state_code => 'CA', p_province_code => null, p_county_code => null, p_city_code => 'SAN_FRANCISCO', p_postal_code => null, x_return_status => err_status, x_error_msg => err_msg); -- to populate postal code with '94065' JTF_TTY_GEOSOURCE_PUB.create_geo(p_geo_type => 'POSTAL_CODE', p_geo_name => '94065', p_geo_code => '94065', p_country_code => 'US', p_state_code => 'CA", p_province_code => null, p_county_code => null, p_city_code => 'SAN_FRANCISCO', p_postal_code => '94065', x_return_status => err_status, x_error_msg => err_msg); COMMIT; END; /
This API deletes a geography. This is the API you will use to delete geographic data into the JTF_TTY_GEOGRAPHIES table.
delete_geo( p_geo_type IN VARCHAR2, p_geo_code IN VARCHAR2, p_country_code IN VARCHAR2, p_state_code IN VARCHAR2 default null, p_province_code IN VARCHAR2 default null, p_county_code IN VARCHAR2 default null, p_city_code IN VARCHAR2 default null, p_postal_code IN VARCHAR2 default null, p_delete_cascade_flag IN VARCHAR2 default 'N', x_return_status IN OUT NOCOPY VARCHAR2, x_error_msg IN OUT NOCOPY VARCHAR2)
Example to delete state of 'Texas'
JTF_TTY_GEOSOURCE_PUB.delete_geo(p_geo_type => 'STATE', p_geo_code => 'TX', p_country_code => 'US', p_state_code => 'TX', p_province_code => null, p_county_code => null, p_city_code => null, p_postal_code=> null, p_delete_cascade_flag => 'N', x_return_status => err_status, x_error_msg => err_msg);
This API updates a geography. This is the API you will use to edit geographic data into the JTF_TTY_GEOGRAPHIES table.
update_geo( p_geo_id IN VARCHAR2, p_geo_name IN VARCHAR2, x_return_status IN OUT NOCOPY VARCHAR2, x_error_msg IN OUT NOCOPY VARCHAR2)
Example to update country name
JTF_TTY_GEOSOURCE_PUB.update_geo(p_geo_id => 249203, p_geo_name => 'CANADA', x_return_status => err_status, x_error_msg => err_msg);