Oracle® Fusion
Applications Incentive Compensation Implementation Guide 11g Release 1 (11.1.4) Part Number E20381-04 |
Contents |
Previous |
Next |
This chapter contains the following:
The application uses interval types to group specific calendar periods to accumulate achievements (for example interval-to-date attainment), earnings (for example, year-to-date earning), or payments during Calculation or Payment processing. There are four defined interval types, Year, Semi-Annual, Quarter, and Period and you can create custom ones, as required. The Period interval type has the same definition as the calendar period.
Tip
Group periods into an interval by giving the same interval number to each period to include in the group.
This is an example of how to use interval numbers to group periods into quarters.
Period |
Year |
From Date |
To Date |
Interval Number |
---|---|---|---|---|
Jan-10 |
2010 |
1-Jan-2010 |
31-Jan-2010 |
2010001 |
Feb-10 |
2010 |
1-Feb-2010 |
28-Feb-2010 |
2010001 |
Mar-10 |
2010 |
1-Mar-2010 |
31-Mar-2010 |
2010001 |
Apr-10 |
2010 |
1-Apr-2010 |
30-Apr-2010 |
2010002 |
May-10 |
2010 |
1-May-2010 |
31-May-2010 |
2010002 |
Jun-10 |
2010 |
1-Jun-2010 |
30-Jun-2010 |
2010002 |
Jul-10 |
2010 |
1-Jul-2010 |
31-Jul-2010 |
2010003 |
Aug-10 |
2010 |
1-Aug-2010 |
31-Aug-2010 |
2010003 |
Sep-10 |
2010 |
1-Sep-2010 |
30-Sep-2010 |
2010003 |
Oct-10 |
2010 |
1-Oct-2010 |
31-Oct-2010 |
2010004 |
Nov-10 |
2010 |
1-Nov-2010 |
30-Nov-2010 |
2010004 |
Dec-10 |
2010 |
1-Dec-2010 |
31-Dec-2010 |
2010004 |
This is an example of how to use an interval number to group periods into a year.
Period |
Year |
From Date |
To Date |
Interval Number |
---|---|---|---|---|
Jan-10 |
2010 |
1-Jan-2010 |
31-Jan-2010 |
2010 |
Feb-10 |
2010 |
1-Feb-2010 |
28-Feb-2010 |
2010 |
Mar-10 |
2010 |
1-Mar-2010 |
31-Mar-2010 |
2010 |
Apr-10 |
2010 |
1-Apr-2010 |
30-Apr-2010 |
2010 |
May-10 |
2010 |
1-May-2010 |
31-May-2010 |
2010 |
Jun-10 |
2010 |
1-Jun-2010 |
30-Jun-2010 |
2010 |
Jul-10 |
2010 |
1-Jul-2010 |
31-Jul-2010 |
2010 |
Aug-10 |
2010 |
1-Aug-2010 |
31-Aug-2010 |
2010 |
Sep-10 |
2010 |
1-Sep-2010 |
30-Sep-2010 |
2010 |
Oct-10 |
2010 |
1-Oct-2010 |
31-Oct-2010 |
2010 |
Nov-10 |
2010 |
1-Nov-2010 |
30-Nov-2010 |
2010 |
Dec-10 |
2010 |
1-Dec-2010 |
31-Dec-2010 |
2010 |
Run the Calculation and Payment processes again for all of the periods affected by the change in the interval numbers.
The application uses them in performance measures as performance intervals to accumulate attainment information and as goal intervals. It also uses them in plan components to specify how frequently to calculate earnings (payout frequency) and in incentive payment plans as payment intervals to define draw and cap rules.
You cannot delete a custom interval type if it is used in any of the previous manners.
You cannot delete the delivered incentive compensation interval types Year, Semi-Annual, Quarter, and Period nor can you delete any custom interval type that you created if anyone used it in a performance measure, plan component, goal, or payment plan.
This example demonstrates how to create a custom incentive compensation attribute with a list of values for use in crediting rules for those situations where the delivered attributes do not fulfill business requirements. The application has these attributes (in the CN_TP_TRANSACTIONS_ALL table) enabled for use in crediting and classification rules:
AREA_CODE
CITY
COUNTRY
CUSTOMER_ID
Tip
Use the customer (directory id) or customer name range for matching.
INVENTORY_ITEM_ID
Tip
Use the product id or sales catalog hierarchy for matching.
ORG_ID
PARTICIPANT_ID
POSTAL_CODE
PROVINCE
ROLE_ID (enabled for crediting rules only
SALES_CHANNEL
SOURCE_ORG_ID
STATE
TERR_NAME
TRANSACTION_AMT_FUNC_CURR
TRANSACTION_QTY
TRANSACTION_TYPE
The three tasks covered in this topic are:
Run SQL script to enable a custom attribute as a qualifier for crediting rules.
Enable the custom attribute.
Edit and deploy the relevant descriptive flexfield.
Tip
Also use the steps in these tasks to create a custom attribute for classification rules by changing the usage_id from -1001 to -1002.
In this task, use SQL and a tool such as SQL Developer to create new FND LOOKUP values to use in the choice list on the user interface (optional), run the CN_RS_CUST_ATTR.CREATE_QUAL application programming interface (API) to create the custom attribute, and update the BATCH_TOTAL_SQL to assign the new custom attribute.
-- Example: create a Line of Business lookup
-- LOOKUP_TYPE = CN_LINE_OF_BUSINESS
DECLARE
l_retcode VARCHAR2(30);
l_errbuf VARCHAR2(10000);
BEGIN
-- create the FND lookup TYPE
fnd_lookup_types_pkg.CREATE_OR_UPDATE_ROW(
X_VIEW_APPSNAME => 'FND',
X_LOOKUP_TYPE => 'CN_LINE_OF_BUSINESS',
X_APPLICATION_SHORT_NAME => 'CN',
X_MEANING => 'Line of Business',
X_DESCRIPTION => 'Fusion Example: Line of Business',
X_REFERENCE_GROUP_NAME => '');
-- populate the FND lookup values
fnd_lookup_values_pkg.CREATE_OR_UPDATE_ROW(
X_LOOKUP_TYPE => 'CN_LINE_OF_BUSINESS',
X_VIEW_APPSNAME => 'FND',
X_LOOKUP_CODE => 'COMMERCIAL_LEASE',
X_MEANING => 'Commercial Leasing',
X_SET_CODE => '',
X_DESCRIPTION => 'Commercial Leasing',
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => sysdate,
X_END_DATE_ACTIVE => '');
fnd_lookup_values_pkg.CREATE_OR_UPDATE_ROW(
X_LOOKUP_TYPE => 'CN_LINE_OF_BUSINESS',
X_VIEW_APPSNAME => 'FND',
X_LOOKUP_CODE => 'RESIDENTIAL',
X_MEANING => 'Residential',
X_SET_CODE => '',
X_DESCRIPTION => 'Residential',
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => sysdate,
X_END_DATE_ACTIVE => '');
fnd_lookup_values_pkg.CREATE_OR_UPDATE_ROW(
X_LOOKUP_TYPE => 'CN_LINE_OF_BUSINESS',
X_VIEW_APPSNAME => 'FND',
X_LOOKUP_CODE => 'CONSUMER_LOAN',
X_MEANING => 'Consumer Personal Loans',
X_SET_CODE => '',
X_DESCRIPTION => 'Consumer Personal Loans',
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => sysdate,
X_END_DATE_ACTIVE => '');
END;
/
API Parameter |
Description |
Sample Value |
---|---|---|
p_attribute_id |
Get the current minimum attribut_id and decrement it by 1
|
-2026 |
p_object_version_number |
Not available |
1 |
p_org_id |
|
458 |
p_source_lang |
Not available |
'US' |
p_language |
Not available |
'US' |
p_name |
This is the user interface display name and you can enter any name |
'Line of Business' |
p_description |
Not available |
'Custom Attribute : Line of Business' |
p_attr_usage_id |
Use any number or pick the next number from here:
It is good to use the same value p_attribute_id, unless it is already assigned. |
-2026 |
p_usage_id |
usage_id = -1001 for crediting usage_id = -1002 for classification |
-1001 |
p_indexed_col_name |
Corresponds to cn_rs_usage_details_all table batch_total_sql. Use the next available value for the same data type, in the unused column. To see which indexed_col_name are used:
|
'SQUAL_CHAR20' |
p_display_type |
'CHAR' = varchar or character 'NUMERIC' = number |
'CHAR' |
p_source_column_name |
From the batch_total_sql field, there are two tables CN_TP_TRANSACTIONS_ALL and HZ_PARTIES. Use one of these fields as the source_column_name. If you want to use a field from other tables, then batch_total_sql must include those tables.
|
'ATTRIBUTE38' |
p_attr_relation_factor |
Get the next prime number, see http://primes.utm.edu/lists/small/1000.txt. To see which prime number is already used:
|
227 |
p_comparison_operator_cn |
Define the value for this custom attribute. Use the positive value of p_attribute_id. Naming convention: q<p_attribute_id>_cp |
'q2026_cp' |
p_low_value_char_cn |
Populate if using the equal or between parameter. Naming convention: q<p_attribute_id>_low_value_char |
'q2026_low_value_char' |
p_high_value_char_cn |
Populate if using the between parameter. Naming convention: q<p_attr_usage_id>_high_value_char |
'q2026_high_value_char' |
p_equal_flag |
Enable the EQUAL operator |
'Y' |
p_batch_op_eol |
SQL syntax for EQUAL operand if p_equal_flag = 'Y' |
'( A.squal_char20 = B.q2026_low_value_char AND B.q2026_cp = ''='' )' |
p_like_flag |
Enable the LIKE operator |
'Y' |
p_batch_op_like |
SQL syntax for EQUAL operand if P_LIKE_FLAG = 'Y' |
'( A.squal_char20 like B.q2026_low_value_char AND B.q2026_cp = ''LIKE'' )' |
p_between_flag |
Enable the BETWEEN operator |
'Y' |
p_batch_op_between |
SQL syntax for EQUAL operand if P_BETWEEN_FLAG = 'Y' |
'( A.squal_char20 between B.q2026_low_value_char and B.q2026_high_value_char AND B.q2026_cp = ''BETWEEN'' )' |
p_convert_to_id_flag |
Convert the varchar2 column to id column, set this value to 'Y' |
'N' |
p_low_value_char_id_cn |
Convert the varchar2 column to id column. Only edit if this P_CONVERT_TO_ID_FLAG = 'Y' |
null |
p_html_lov_sql1 |
If you want an list of value (LOV) to be associated to the attribute. |
'select /*+ FIRST_ROWS(10) */ 1 lookup_id, meaning meaning, lookup_code lookup_code from cn_lookups where lookup_type = ''CN_EXAMPLE_LINE_OF_BUSINESS'' ' |
-- Example: ATTRIBUTE38 is enabled for crediting with list of value
-- from CN_LINE_OF_BUSINESS FND lookup
DECLARE
l_retcode VARCHAR2(30);
l_errbuf VARCHAR2(10000);
BEGIN
CN_RS_CUST_ATTR.CREATE_QUAL(
p_attribute_id => -2026,
p_object_version_number => 1,
p_org_id => 458,
p_SOURCE_LANG => 'US',
p_LANGUAGE => 'US',
p_name => 'Line of Business',
p_description => 'Custom Attribute : Line of Business',
p_ATTR_USAGE_ID => -2026,
p_USAGE_ID => -1001,
p_INDEXED_COL_NAME => 'SQUAL_CHAR20',
p_DISPLAY_TYPE => 'CHAR',
p_SOURCE_COLUMN_NAME => 'ATTRIBUTE38',
p_ATTR_RELATION_FACTOR => 227,
p_CONVERT_TO_ID_FLAG => 'N',
p_COMPARISON_OPERATOR_CN => 'q2026_cp',
p_LOW_VALUE_CHAR_CN => 'q2026_low_value_char',
p_HIGH_VALUE_CHAR_CN => NULL,
p_LOW_VALUE_CHAR_ID_CN => NULL,
p_LOW_VALUE_NUMBER_CN => NULL,
p_HIGH_VALUE_NUMBER_CN => NULL,
p_VALUE1_ID_CN => NULL,
p_VALUE2_ID_CN => NULL,
p_VALUE3_ID_CN => NULL,
p_VALUE4_ID_CN => NULL,
p_FIRST_CHAR_CN => NULL,
p_CURRENCY_CODE_CN => NULL,
p_EQUAL_FLAG => 'Y',
p_BATCH_OP_EQL => '( A.squal_char20 = B.q2026_low_value_char AND B.q2026_cp = ''='' )',
p_LIKE_FLAG => 'N',
p_BATCH_OP_LIKE => NULL,
p_BETWEEN_FLAG => 'N',
p_BATCH_OP_BETWEEN => NULL,
p_BATCH_OP_COMMON_WHERE => NULL,
p_SEEDED_FLAG => NULL,
p_ALIAS_RULE1 => NULL,
p_REAL_TIME_SELECT => NULL,
p_REAL_TIME_WHERE => NULL,
p_REAL_TIME_FROM => NULL,
p_HTML_LOV_SQL1 => 'select /*+ FIRST_ROWS(10) */ 1 lookup_id, meaning meaning, lookup_code lookup_code from cn_lookups where lookup_type = ''CN_EXAMPLE_LINE_OF_BUSINESS'' ',
p_HTML_LOV_SQL2 => NULL,
p_HTML_LOV_SQL3 => NULL,
p_HIERARCHY_TYPE => NULL,
retcode => l_retcode,
errbuf => l_errbuf);
END;
/
Important
Run this SQL to see which fields are already enabled in the existing batch_total_sql 's select statement.
sql> select batch_total_sql from cn_rs_usage_details_all where usage_id = -1001 and org_id = &org_id ;
DECLARE
l_retcode VARCHAR2(30);
l_errbuf VARCHAR2(10000);
BEGIN
update cn_rs_usage_details_all
set batch_total_sql =
' SELECT T.TRANSACTION_ID TRANS_OBJECT_ID , ' ||
' T.PARTICIPANT_ID ASSIGNED_OBJECT_ID , ' ||
' T.SOURCE_EVENT_DATE EVENT_DATE , ' ||
' T.WORKER_ID , ' ||
' UPPER(substr(C.ACCOUNT_NAME,1,1)) SQUAL_FC01 , ' ||
' null SQUAL_CURC01 , ' ||
' T.SALES_CHANNEL SQUAL_CHAR01 , ' ||
' T.CITY SQUAL_CHAR02 , ' ||
' T.STATE SQUAL_CHAR03 , ' ||
' T.COUNTRY SQUAL_CHAR04 , ' ||
' C.ACCOUNT_NAME SQUAL_CHAR05 , ' ||
' P.PARTY_NUMBER SQUAL_CHAR06 , ' ||
' P.CATEGORY_CODE SQUAL_CHAR07 , ' ||
' T.POSTAL_CODE SQUAL_CHAR08 , ' ||
' T.PROVINCE SQUAL_CHAR09 , ' ||
' T.AREA_CODE SQUAL_CHAR10 , ' ||
' T.CUSTOMER_ID SQUAL_NUM01 , ' ||
' T.INVENTORY_ITEM_ID SQUAL_NUM02 , ' ||
' NULL SQUAL_NUM03 , ' ||
' T.SOURCE_ORG_ID SQUAL_NUM04 , ' ||
' T.PARTICIPANT_ID SQUAL_NUM05 , ' ||
' null SQUAL_FC02 , ' ||
' null SQUAL_CURC02 , ' ||
' NULL SQUAL_CHAR11 , ' ||
' T.TRANSACTION_TYPE SQUAL_CHAR12 , ' ||
' T.TERR_NAME SQUAL_CHAR13 , ' ||
' NULL SQUAL_CHAR14 , ' ||
' NULL SQUAL_CHAR15 , ' ||
' NULL SQUAL_CHAR16 , ' ||
' NULL SQUAL_CHAR17 , ' ||
' NULL SQUAL_CHAR18 , ' ||
' null SQUAL_CHAR19 , ' ||
' T.ATTRIBUTE38 SQUAL_CHAR20 , ' ||
' T.TRANSACTION_AMT_FUNC_CURR SQUAL_NUM06 , ' ||
' T.TRANSACTION_QTY SQUAL_NUM07 , ' ||
' T.ORG_ID SQUAL_NUM08 , ' ||
' null SQUAL_NUM09 , ' ||
' null SQUAL_NUM10 , ' ||
' null SQUAL_FC03 , ' ||
' null SQUAL_CURC03 , ' ||
' null SQUAL_CHAR21 , ' ||
' null SQUAL_CHAR22 , ' ||
' null SQUAL_CHAR23 , ' ||
' null SQUAL_CHAR24 , ' ||
' null SQUAL_CHAR25 , ' ||
' null SQUAL_CHAR26 , ' ||
' null SQUAL_CHAR27 , ' ||
' null SQUAL_CHAR28 , ' ||
' null SQUAL_CHAR29 , ' ||
' null SQUAL_CHAR30 , ' ||
' null SQUAL_NUM11 , ' ||
' null SQUAL_NUM12 , ' ||
' null SQUAL_NUM13 , ' ||
' null SQUAL_NUM14 , ' ||
' null SQUAL_NUM15 , ' ||
' null SQUAL_FC04 , ' ||
' null SQUAL_CURC04 , ' ||
' null SQUAL_CHAR31 , ' ||
' null SQUAL_CHAR32 , ' ||
' null SQUAL_CHAR33 , ' ||
' null SQUAL_CHAR34 , ' ||
' null SQUAL_CHAR35 , ' ||
' null SQUAL_CHAR36 , ' ||
' null SQUAL_CHAR37 , ' ||
' null SQUAL_CHAR38 , ' ||
' null SQUAL_CHAR39 , ' ||
' null SQUAL_CHAR40 , ' ||
' null SQUAL_NUM16 , ' ||
' null SQUAL_NUM17 , ' ||
' null SQUAL_NUM18 , ' ||
' null SQUAL_NUM19 , ' ||
' null SQUAL_NUM20 , ' ||
' null SQUAL_FC05 , ' ||
' null SQUAL_CURC05 , ' ||
' null SQUAL_CHAR41 , ' ||
' null SQUAL_CHAR42 , ' ||
' null SQUAL_CHAR43 , ' ||
' null SQUAL_CHAR44 , ' ||
' null SQUAL_CHAR45 , ' ||
' null SQUAL_CHAR46 , ' ||
' null SQUAL_CHAR47 , ' ||
' null SQUAL_CHAR48 , ' ||
' null SQUAL_CHAR49 , ' ||
' null SQUAL_CHAR50 , ' ||
' null SQUAL_NUM21 , ' ||
' null SQUAL_NUM22 , ' ||
' null SQUAL_NUM23 , ' ||
' null SQUAL_NUM24 , ' ||
' null SQUAL_NUM25 ' ||
' from CN_TP_TRANSACTIONS_ALL T , ' ||
' HZ_PARTIES P ' ||
' where T.CUSTOMER_ID = P.PARTY_ID(+) '
where usage_id = -1001;
END;
/
If you want to enable the attribute for classification, then select Enable for Classification.
Tip
To expose your customer attribute in the user interface, and in application-generated spreadsheets, edit and deploy the descriptive flexfield.
This topic contains two sample SQL scripts for adding incentive compensation crediting rules qualifiers that correspond to the flexfield Attribute1 and Invoice_Date in the CN_TP_TRANSACTIONS_ALL table. The script includes comments to help you customize the SQL to your business requirements.
declare
l_retcode VARCHAR2(30);
l_errbuf VARCHAR2(10000);
begin
CN_RS_CUST_ATTR.CREATE_QUAL(
p_attribute_id => -1026, -- unique number in the cn_rs_attributes_all table
p_object_version_number => 1,
p_org_id => 204, -- operating unit
p_SOURCE_LANG => 'US',
p_LANGUAGE => 'US',
p_name => 'Attribute 1', -- display name
p_description => 'Custom Attribute: Attribute1',
p_ATTR_USAGE_ID => -1026, -- unique number in cn_rs_attr_usages_all
p_USAGE_ID => -1001,
p_INDEXED_COL_NAME => 'SQUAL_CHAR15', --corresponds to cn_rs_usage_details_all table batch_total_sql
p_DISPLAY_TYPE => 'CHAR',
p_SOURCE_COLUMN_NAME => 'ATTRIBUTE1', -- corresponds to cn_tp_transactions_all table column
p_ATTR_RELATION_FACTOR => 311, --http://primes.utm.edu/lists/small/1000.txt
p_CONVERT_TO_ID_FLAG => 'N', --to convert the varchar2 column to id column, set this value to 'Y'
p_COMPARISON_OPERATOR_CN => 'q1026_cp',
p_LOW_VALUE_CHAR_CN => 'q1026_low_value_char',
p_HIGH_VALUE_CHAR_CN => NULL,
p_LOW_VALUE_CHAR_ID_CN => NULL,
p_LOW_VALUE_NUMBER_CN => NULL,
p_HIGH_VALUE_NUMBER_CN => NULL,
p_VALUE1_ID_CN => NULL,
p_VALUE2_ID_CN => NULL,
p_VALUE3_ID_CN => NULL,
p_VALUE4_ID_CN => NULL,
p_FIRST_CHAR_CN => NULL,
p_CURRENCY_CODE_CN => NULL,
p_EQUAL_FLAG => 'Y', -- enable = operator p_BATCH_OP_EQL
p_BATCH_OP_EQL => '( A.squal_char15 = B.q1026_low_value_char AND B.q1026_cp = ''='' )',
p_LIKE_FLAG => 'N', -- enable LIKE operator p_BATCH_OP_LIKE
p_BATCH_OP_LIKE => NULL,
p_BETWEEN_FLAG => 'N', -- enable BETWEEN operator p_BATCH_OP_BETWEEN
p_BATCH_OP_BETWEEN => NULL,
p_BATCH_OP_COMMON_WHERE => NULL,
p_SEEDED_FLAG => NULL,
p_ALIAS_RULE1 => NULL,
p_REAL_TIME_SELECT => NULL,
p_REAL_TIME_WHERE => NULL,
p_REAL_TIME_FROM => NULL,
p_HTML_LOV_SQL1 => NULL,
p_HTML_LOV_SQL2 => NULL,
p_HTML_LOV_SQL3 => NULL,
p_HIERARCHY_TYPE => NULL,
retcode => l_retcode,
errbuf => l_errbuf);
update CN_RS_USAGE_DETAILS_ALL
set BATCH_TOTAL_SQL = 'SELECT '||
' T.TRANSACTION_ID TRANS_OBJECT_ID '||
' , T.PARTICIPANT_ID ASSIGNED_OBJECT_ID '||
' , T.SOURCE_EVENT_DATE EVENT_DATE '||
' , T.WORKER_ID '||
' , UPPER(substr(C.ACCOUNT_NAME,1,1)) SQUAL_FC01 '||
' , null SQUAL_CURC01 '||
' , T.SALES_CHANNEL SQUAL_CHAR01 '||
' , T.CITY SQUAL_CHAR02 '||
' , T.STATE SQUAL_CHAR03 '||
' , T.COUNTRY SQUAL_CHAR04 '||
' , C.ACCOUNT_NAME SQUAL_CHAR05 '||
' , P.PARTY_NUMBER SQUAL_CHAR06 '||
' , P.CATEGORY_CODE SQUAL_CHAR07 '||
' , T.POSTAL_CODE SQUAL_CHAR08 '||
' , T.PROVINCE SQUAL_CHAR09 '||
' , T.AREA_CODE SQUAL_CHAR10 '||
' , E.NAME SQUAL_CHAR11 '||
' , T.TRANSACTION_TYPE SQUAL_CHAR12 '||
' , T.TERR_NAME SQUAL_CHAR13 '||
' , R.ROLE_NAME SQUAL_CHAR14 '||
' , T.CUSTOMER_ID SQUAL_NUM01 '||
' , T.INVENTORY_ITEM_ID SQUAL_NUM02 '||
' , T.ELIGIBLE_CAT_ID SQUAL_NUM03 '||
' , T.SOURCE_ORG_ID SQUAL_NUM04 '||
' , T.PARTICIPANT_ID SQUAL_NUM05 '||
' , T.TRANSACTION_AMT_FUNC_CURR SQUAL_NUM06 '||
' , T.TRANSACTION_QTY SQUAL_NUM07 '||
' , T.ORG_ID SQUAL_NUM08 '||
' , null SQUAL_FC02 '||
' , null SQUAL_CURC02 '||
' , T.ATTRIBUTE1 SQUAL_CHAR15 '|| -- IMPORTANT! Update this value to correspond to index column name
' , NULL SQUAL_CHAR16 '||
' , null SQUAL_CHAR17 '||
' , null SQUAL_CHAR18 '||
' , null SQUAL_CHAR19 '||
' , null SQUAL_CHAR20 '||
' , null SQUAL_NUM09 '||
' , null SQUAL_NUM10 '||
' , null SQUAL_FC03 '||
' , null SQUAL_CURC03 '||
' , null SQUAL_CHAR21 '||
' , null SQUAL_CHAR22 '||
' , null SQUAL_CHAR23 '||
' , null SQUAL_CHAR24 '||
' , null SQUAL_CHAR25 '||
' , null SQUAL_CHAR26 '||
' , null SQUAL_CHAR27 '||
' , null SQUAL_CHAR28 '||
' , null SQUAL_CHAR29 '||
' , null SQUAL_CHAR30 '||
' , null SQUAL_NUM11 '||
' , null SQUAL_NUM12 '||
' , null SQUAL_NUM13 '||
' , null SQUAL_NUM14 '||
' , null SQUAL_NUM15 '||
' , null SQUAL_FC04 '||
' , null SQUAL_CURC04 '||
' , null SQUAL_CHAR31 '||
' , null SQUAL_CHAR32 '||
' , null SQUAL_CHAR33 '||
' , null SQUAL_CHAR34 '||
' , null SQUAL_CHAR35 '||
' , null SQUAL_CHAR36 '||
' , null SQUAL_CHAR37 '||
' , null SQUAL_CHAR38 '||
' , null SQUAL_CHAR39 '||
' , null SQUAL_CHAR40 '||
' , null SQUAL_NUM16 '||
' , null SQUAL_NUM17 '||
' , null SQUAL_NUM18 '||
' , null SQUAL_NUM19 '||
' , null SQUAL_NUM20 '||
' , null SQUAL_FC05 '||
' , null SQUAL_CURC05 '||
' , null SQUAL_CHAR41 '||
' , null SQUAL_CHAR42 '||
' , null SQUAL_CHAR43 '||
' , null SQUAL_CHAR44 '||
' , null SQUAL_CHAR45 '||
' , null SQUAL_CHAR46 '||
' , null SQUAL_CHAR47 '||
' , null SQUAL_CHAR48 '||
' , null SQUAL_CHAR49 '||
' , null SQUAL_CHAR50 '||
' , null SQUAL_NUM21 '||
' , null SQUAL_NUM22 '||
' , null SQUAL_NUM23 '||
' , null SQUAL_NUM24 '||
' , null SQUAL_NUM25 '||
' from CN_TP_TRANSACTIONS_ALL T ' ||
' , HZ_PARTIES P ' ||
' , HZ_CUST_ACCOUNTS C ' ||
' , JTF_RS_ROLES_VL R ' ||
' , cn_eligible_cats_all_vl E ' ||
' where T.CUSTOMER_ID = C.CUST_ACCOUNT_ID(+) '||
' AND C.PARTY_ID = P.PARTY_ID(+) ' ||
' AND T.ROLE_ID = R.ROLE_ID(+) ' ||
' AND T.ELIGIBLE_CAT_ID = E.ELIGIBLE_CAT_ID(+) '
where usage_id = -1001;
end;
commit;
exit;
declare
l_retcode VARCHAR2(30);
l_errbuf VARCHAR2(10000);
begin
CN_RS_CUST_ATTR.CREATE_QUAL(
p_attribute_id => -1029, -- unique number in cn_rs_attributes_all table
p_object_version_number => 1,
p_org_id => 204, -- operating unit
p_SOURCE_LANG => 'US',
p_LANGUAGE => 'US',
p_name => 'Invoice Date', -- display name
p_description => 'Custom Attribute: Invoice Date',
p_ATTR_USAGE_ID => -1029, -- unique number in cn_rs_attr_usages_all
p_USAGE_ID => -1001,
p_INDEXED_COL_NAME => 'SQUAL_CHAR19', -- corresponds to cn_rs_usage_details_all table batch_total_sql
p_DISPLAY_TYPE => 'CHAR',
p_SOURCE_COLUMN_NAME => 'INVOICE_DATE', -- corresponds to cn_tp_transactions_all table column
p_ATTR_RELATION_FACTOR => 331, --http://primes.utm.edu/lists/small/1000.txt
p_CONVERT_TO_ID_FLAG => 'N', --to convert the varchar2 column to id column, set this value to 'Y'
p_COMPARISON_OPERATOR_CN => 'q1029_cp',
p_LOW_VALUE_CHAR_CN => 'q1029_low_value_char',
p_HIGH_VALUE_CHAR_CN => 'q1029_high_value_char',
p_LOW_VALUE_CHAR_ID_CN => NULL,
p_LOW_VALUE_NUMBER_CN => NULL,
p_HIGH_VALUE_NUMBER_CN => NULL,
p_VALUE1_ID_CN => NULL,
p_VALUE2_ID_CN => NULL,
p_VALUE3_ID_CN => NULL,
p_VALUE4_ID_CN => NULL,
p_FIRST_CHAR_CN => NULL,
p_CURRENCY_CODE_CN => NULL,
p_EQUAL_FLAG => 'Y', -- enable = operator p_BATCH_OP_EQL
p_BATCH_OP_EQL => '( to_date(A.squal_char19,''YYYY-MM-DD'') = to_date(B.q1029_low_value_char,''YYYY-MM-DD'') AND B.q1029_cp = ''='' )',
p_LIKE_FLAG => 'N', -- enable LIKE operator p_BATCH_OP_LIKE
p_BATCH_OP_LIKE => NULL,
p_BETWEEN_FLAG => 'Y', -- enable BETWEEN operator p_BATCH_OP_BETWEEN
p_BATCH_OP_BETWEEN => '( to_date(A.squal_char19,''YYYY-MM-DD'') BETWEEN
to_date(B.q1029_low_value_char,''YYYY-MM-DD'') AND to_date(B.q1029_high_value_char,''YYYY-MM-DD'') and B.q1029_cp = ''BETWEEN'' )' ,
p_BATCH_OP_COMMON_WHERE => NULL,
p_SEEDED_FLAG => NULL,
p_ALIAS_RULE1 => NULL,
p_REAL_TIME_SELECT => NULL,
p_REAL_TIME_WHERE => NULL,
p_REAL_TIME_FROM => NULL,
p_HTML_LOV_SQL1 => NULL,
p_HTML_LOV_SQL2 => NULL,
p_HTML_LOV_SQL3 => NULL,
p_HIERARCHY_TYPE => NULL,
retcode => l_retcode,
errbuf => l_errbuf);
update CN_RS_USAGE_DETAILS_ALL
set BATCH_TOTAL_SQL = 'SELECT '||
' T.TRANSACTION_ID TRANS_OBJECT_ID '||
' , T.PARTICIPANT_ID ASSIGNED_OBJECT_ID '||
' , T.SOURCE_EVENT_DATE EVENT_DATE '||
' , T.WORKER_ID '||
' , UPPER(substr(C.ACCOUNT_NAME,1,1)) SQUAL_FC01 '||
' , null SQUAL_CURC01 '||
' , T.SALES_CHANNEL SQUAL_CHAR01 '||
' , T.CITY SQUAL_CHAR02 '||
' , T.STATE SQUAL_CHAR03 '||
' , T.COUNTRY SQUAL_CHAR04 '||
' , C.ACCOUNT_NAME SQUAL_CHAR05 '||
' , P.PARTY_NUMBER SQUAL_CHAR06 '||
' , P.CATEGORY_CODE SQUAL_CHAR07 '||
' , T.POSTAL_CODE SQUAL_CHAR08 '||
' , T.PROVINCE SQUAL_CHAR09 '||
' , T.AREA_CODE SQUAL_CHAR10 '||
' , E.NAME SQUAL_CHAR11 '||
' , T.TRANSACTION_TYPE SQUAL_CHAR12 '||
' , T.TERR_NAME SQUAL_CHAR13 '||
' , R.ROLE_NAME SQUAL_CHAR14 '||
' , T.CUSTOMER_ID SQUAL_NUM01 '||
' , T.INVENTORY_ITEM_ID SQUAL_NUM02 '||
' , T.ELIGIBLE_CAT_ID SQUAL_NUM03 '||
' , T.SOURCE_ORG_ID SQUAL_NUM04 '||
' , T.PARTICIPANT_ID SQUAL_NUM05 '||
' , T.TRANSACTION_AMT_FUNC_CURR SQUAL_NUM06 '||
' , T.TRANSACTION_QTY SQUAL_NUM07 '||
' , T.ORG_ID SQUAL_NUM08 '||
' , null SQUAL_FC02 '||
' , null SQUAL_CURC02 '||
' , NULL SQUAL_CHAR15 '|| -- IMPORTANT! Update this value to correspond to index column name
' , NULL SQUAL_CHAR16 '||
' , null SQUAL_CHAR17 '||
' , null SQUAL_CHAR18 '||
' , T.INVOICE_DATE SQUAL_CHAR19 '||
' , null SQUAL_CHAR20 '||
' , null SQUAL_NUM09 '||
' , null SQUAL_NUM10 '||
' , null SQUAL_FC03 '||
' , null SQUAL_CURC03 '||
' , null SQUAL_CHAR21 '||
' , null SQUAL_CHAR22 '||
' , null SQUAL_CHAR23 '||
' , null SQUAL_CHAR24 '||
' , null SQUAL_CHAR25 '||
' , null SQUAL_CHAR26 '||
' , null SQUAL_CHAR27 '||
' , null SQUAL_CHAR28 '||
' , null SQUAL_CHAR29 '||
' , null SQUAL_CHAR30 '||
' , null SQUAL_NUM11 '||
' , null SQUAL_NUM12 '||
' , null SQUAL_NUM13 '||
' , null SQUAL_NUM14 '||
' , null SQUAL_NUM15 '||
' , null SQUAL_FC04 '||
' , null SQUAL_CURC04 '||
' , null SQUAL_CHAR31 '||
' , null SQUAL_CHAR32 '||
' , null SQUAL_CHAR33 '||
' , null SQUAL_CHAR34 '||
' , null SQUAL_CHAR35 '||
' , null SQUAL_CHAR36 '||
' , null SQUAL_CHAR37 '||
' , null SQUAL_CHAR38 '||
' , null SQUAL_CHAR39 '||
' , null SQUAL_CHAR40 '||
' , null SQUAL_NUM16 '||
' , null SQUAL_NUM17 '||
' , null SQUAL_NUM18 '||
' , null SQUAL_NUM19 '||
' , null SQUAL_NUM20 '||
' , null SQUAL_FC05 '||
' , null SQUAL_CURC05 '||
' , null SQUAL_CHAR41 '||
' , null SQUAL_CHAR42 '||
' , null SQUAL_CHAR43 '||
' , null SQUAL_CHAR44 '||
' , null SQUAL_CHAR45 '||
' , null SQUAL_CHAR46 '||
' , null SQUAL_CHAR47 '||
' , null SQUAL_CHAR48 '||
' , null SQUAL_CHAR49 '||
' , null SQUAL_CHAR50 '||
' , null SQUAL_NUM21 '||
' , null SQUAL_NUM22 '||
' , null SQUAL_NUM23 '||
' , null SQUAL_NUM24 '||
' , null SQUAL_NUM25 '||
' from CN_TP_TRANSACTIONS_ALL T ' ||
' , HZ_PARTIES P ' ||
' , HZ_CUST_ACCOUNTS C ' ||
' , JTF_RS_ROLES_VL R ' ||
' , cn_eligible_cats_all_vl E ' ||
' where T.CUSTOMER_ID = C.CUST_ACCOUNT_ID(+) '||
' AND C.PARTY_ID = P.PARTY_ID(+) ' ||
' AND T.ROLE_ID = R.ROLE_ID(+) ' ||
' AND T.ELIGIBLE_CAT_ID = E.ELIGIBLE_CAT_ID(+) '
where usage_id = -1001;
end;
commit;
exit;
This example demonstrates how to edit and deploy descriptive flexfields so that the application exposes the customized attribute in the user interface (Create Transaction and Manage Transactions pages) and application-generated spreadsheets.
If you sign in with the Implementation Consultant duty role, click Manage Descriptive Flexfields to go to the Manage Descriptive Flexfields page. Skip to step 6.
After you deploy a descriptive flexfield, the new, or edited, attributes appear on all pages that contain the flexfield. It also propagates the attributes to any application-generated spreadsheets that contain the flexfield.
In the Define Business Unit Configuration for Incentive Compensation task list, Configure Tables and Columns task, enable the attribute for calculation and select the appropriate level 2 expression grouping. Also add a user-friendly name for the attribute, that the application displays in the incentive compensation expression builder.
You must first add the custom qualifiers to the incentive compensation classification or crediting rules.
Use earning types to determine the form that participant incentives take. For example, participants associated with plan components where the Earning Type is Monetary Earnings receive cash compensation for earnings related to these plan components. This is the earning type value that is delivered with the application. It is the only earning type that has monetary value and the only earning type included in incentive compensation payment processing. You can create other earning types and associate them with plan components. The application creates earnings for these plan components and stores them in the database so that they are available for you to use in reporting.
General steps for setting up nonmonetary goals:
Create your earning type, for example, Points.
Create a conversion factor to convert the earning type value to operating currency, for your date range.
Note
The application uses this conversion factor only for reporting. For example, your operating currency is USD and you define 1 Point = 0.10 operating currency from 1-Jan-10 through 31-Dec-10.
Restriction
When you create a conversion factor record, if you choose to set an end date, it must be later than the start date.
Associate your earning type with a plan component, for example you have a plan component Laptop Sales and set the earning type to Points.
Tip
You cannot delete the earning type Monetary Earnings. You can delete all other earning types as long as they are not associated with any plan component or used in converting other earning types.
When calculation computes the earning, it stores the commission (for example, 500 Points) under COMM_AMT_CALC_CURRENCY in the Earnings entity.
Tip
Calculation does not do any conversion on nonmonetary earnings (such as Points) to participant home currency or operating currency. It stores the nonmonetary balances or earnings, separately in the subledger.
Since the payment module does not include nonmonetary earnings, you can export those earnings and set their statuses to Posted to ensure that the same nonmonetary earnings are not included the next time that you export them.
When you run the report for the Points earning type, the participant reports show the earnings in the earning type. For example, since the application stored the nonmonetary earnings from our earlier example as 500 Points, along with certain plan component information, the reports show 500 Points.
Participant reports show only those plan components containing the earning type selected for the report run. Affected reports include: YTD, Earning Details, and Commission Statement.
This topic covers how an incentive compensation calendar supports processing activities for incentive compensation business units by first definingperiod types and periods, and then exploring how they work together.
Determines how you divide your calendar or fiscal year. The following table shows the delivered types and corresponding periods in a year:
Period Type |
Periods in a Year |
---|---|
Monthly |
12 |
Quarterly |
4 |
Semi-Annually |
2 |
Yearly |
1 |
Create custom period types as required. Enter the number of periods in a year so that the application can validate the periods that you define for the calendar. Select the shortest required period range (for example, weekly or monthly) to ensure that the application can accumulate attainments, maintain goals and subledger balances, and process payments at this level, or any higher level.
Restriction
You cannot accumulate attainments for a period type shorter than your selection. For example, if you select Monthly, you cannot accumulate attainments weekly.
You cannot edit or delete standard or custom period types used by any calendar.
Define for each calendar based on the selected period type, including the definition of period with year, period name, sequence, and start and end date--inclusive of both the dates. Abbreviate the period name if you want, for example Jan for January or W1 for Week1. The application arranges periods based on the sequence values that you enter.
Restriction
The date ranges within one period cannot overlap with other periods and must be continuous with reference to the previous and next periods.
After you associate a calendar with a business unit and open the first period for the business unit, you cannot edit the calendar.
Tip
Create all of the periods required for the compensation plans, measures, and goals before creating the plans, measures, and goals.
The following table shows the various calendar and period tasks that first you and then the application perform.
User Actions |
Application Actions |
---|---|
1. Create a calendar and associated periods as a single entity within the application. |
Store the calendar data in the calendar entity (CN_CALENDARS) and the period data in the period entity (CN_PERIODS). |
2. Assign a calendar to a business unit as part of the business unit set up. |
Insert the periods (zero or more) that are assigned to that calendar into the business unit level tables. For each interval type defined for the business unit, insert each period into the CN_CAL_PER_INT_TYPES table. The CN_PERIOD_STATUS table stores basic period data, including the default status (Never Opened) given to each period. |
3. Edit the period status (for example, select Open) within the business unit. |
Update the period statuses in the CN_PERIOD_STATUS table accordingly. If required, launch a concurrent request to populate the period data in all of the participant-related (SRP) tables. |
4. Create new, future periods in the calendar. |
Insert the new period records into the following tables:
|
There are different statuses that you can edit for incentive compensation periods. The current period status constrains the available values to which you can set the status.
Not Opened: Edit this status to either Future Entry or Open.
Tip
You cannot edit a period to Open if any prior period status is Not Opened or Future Entry.
Future Entry: Edit this status to Open.
Open: Edit this status to either Closed or Permanently Closed.
Tip
You cannot edit a period to Closed if:
Any prior period status is Not Opened, Future Entry, or Open
Any trial payment batches are unpaid (delete or pay the payment batch first, and then close the period)
You cannot edit a period to Permanently Closed if any prior period is Not Opened, Future Entry, Open, or Closed.
Closed: Edit this status to either Open or Permanently Closed.
Permanently Closed
Warning
After you permanently close a period, you cannot reopen it and the application does not process transactions of any kind. Be sure that there are no new transactions, adjustments, payments, or any other outstanding transactions before you permanently close a period.
Most likely, the incentive compensation period was used in one or more of the associated incentive compensation business units, or opened. Or, deleting this period may cause noncontinuous periods, which the application does not allow.
To edit details (such as dates and sequence) for a period that you have not yet opened and did assign to a business unit, delete the period and recreate it in the Define Business Unit Configuration for Incentive Compensation task list, Manage Open Period Process task.
To edit a period that is in the middle of the year, start from the last period defined. For example, if the date range for Sep-09 was wrongly defined and requires correction, starting from Dec-09 (assuming that this is the last defined period), delete the periods Dec-09, Nov-09, and Oct-09 before modifying details for Sep-09.
Import participants into Oracle Fusion Incentive Compensation using the Staging and Oracle Incentive Compensation Participant Import process, which is located in the Participant Assignments work area, on the Import Participants page.
In the Participant Assignments work area, select the Run For choice.
Staging and Oracle Incentive Compensation Participant Import: Use the default integration, which performs the participant import in two phases.
Oracle Incentive Compensation Participant Import: Use your own extraction, transformation, and load utility to populate the staging tables, and then run only the second of the two default phases.
There are two phases to the import process, which includes mapping for two Oracle Fusion Incentive Compensation tables.
Phase 1: Import Data to Staging Table (optional)
The first (optional) phase, Staging, runs the default Oracle Data Integrator (ODI) scenario to import the participant data into the CN_SRP_PARTICIPANTS_STAGING_T staging table. The application only runs this step if you select Staging and Oracle Incentive Compensation Participant Import from the Run For choice list. It copies all parties from Oracle Fusion Trading Community Architecture to the staging table that meet the specified filter parameters. The application also populates all of the participant attributes, such as country, currency, and analyst, in the staging table (the mappings are in following sections).
Modify this scenario or create your own script to import data into the staging table, based on your business requirements.
Important
If you use your own process to import data into the incentive compensation staging table, and use only Phase 2 of the standard import process, then it is possible that the staging table can have invalid data. If columns such as ANALYST_ID, COUNTRY, CURRENCY_CODE, or COST_CENTER are invalid, then you can correct them in the application. There is no way to correct columns such as PARTY_ID or SOURCE_SYSTEM_ID. The application does not expose the column SOURCE_SYSTEM_ID and if PARTY_ID is invalid, then the application will not even display the record.
Phase 2: Import Data to Participant Header Table
The second phase, Oracle Incentive Compensation Participant Import, collects the data into the participant header table CN_SRP_PARTICIPANTS_ALL and participant detail table CN_SRP_PARTICIPANT_DETAILS_ALL. Next, for the imported parties, if there is no party usage code specified, it inserts a record into the HZ_PARTY_USG_ASSIGNMENTS table with a party usage code of INCENTIVE_COMP_PARTICIPANT. Last, the application deletes the party records that were imported in phase 1, from the CN_SRP_PARTICIPANTS_STAGING_T staging table.
Restriction
The application will not copy any record where the combination of PART_ID and ORG_ID already exists in the CN_SRP_PARTICIPANTS_ALL header table.
Mapping for CN_SRP_PARTICPANTS_ALL
This table maps each CN_SRP_PARTICIPANTS_ALL column name to the corresponding Oracle Fusion Trading Community or Oracle Fusion Human Capital Management (HCM) column name and includes comments about the values.
Column name in CN_SRP_PARTICIPANTS_ALL |
Column Name in Oracle Fusion Trading Community or HCM |
Comments |
---|---|---|
PARTICIPANT_ID |
None |
Oracle Data Integrator generates it from the sequence CN_SRP_PARTICIPANTS_S1. |
PARTY_ID |
HZ_PARTIES.PARTY_ID |
|
START_DATE |
PER_ALL_PEOPLE_F.START_DATE HZ_PARTY_USG_ASSIGNMENTS.EFFECTIVE_START_DATE |
Oracle Data Integrator first tries to use the target parameter Active Start Date first. If it is null, then Oracle Data Integrator uses either PER_ALL_PEOPLE_F.START_DATE or HZ_PARTY_USG_ASSIGNMENTS.EFFECTIVE_START_DATE. |
END_DATE |
None |
Oracle Data Integrator does not populate it. |
ACTIVE_FLAG |
None |
Oracle Data Integrator always populated with Y (yes). |
ANALYST_ID |
None |
Oracle Data Integrator first tries to use the target parameter Analyst. If it is NULL, then ODI does not populate ANALYST_ID. |
PARTICIPANT_TYPE |
None |
Oracle Data Integrator uses the target parameter Participant Type, which defaults to Participant. |
COMPENSATION_END_DATE |
None |
Oracle Data Integrator does not populate it. |
HOLD_PAYMENT_FLAG |
None |
Oracle Data Integrator always populated with N (no). |
HOLD_REASON |
None |
Oracle Data Integrator does not populate it. |
DISPLAY_IDENTIFIER |
None |
Oracle Data Integrator does not populate it. |
SOURCE_SYSTEM |
None |
Not applicable. |
SOURCE_SYSTEM_ID |
PER_ALL_PEOPLE_F.PERSON_ID |
If there is no entry in Oracle Fusion HCM, then Oracle Data Integrator populates it with NULL. |
ORG_ID |
None |
Oracle Data Integrator uses the target parameter Business Unit. |
PARTY_NUMBER |
HZ_PARTIES.PARTY_NUMBER |
Oracle Data Integrator populates it. |
HR_PRIMARY_WORKER_NUMBER |
HZ_PARTIES.HR_PRIMARY_WORKER_NUMBER |
Oracle Data Integrator populates it. |
PAYEE_ONLY |
None |
Oracle Data Integrator does not populate it. |
USER_GUID |
None |
Oracle Data Integrator does not populate it. |
Mapping for CN_SRP_PARTICPANT_DETAILS_ALL
This table maps each CN_SRP_PARTICIPANT_DETAILS_ALL to the corresponding Oracle Fusion Trading Community column name and includes comments about the values.
CN_SRP_PARTICIPANT_DETAILS_ALL |
Column Name in Oracle Fusion Trading Community |
Comments |
---|---|---|
PARTICIPANT_DETAIL_ID |
None |
Oracle Data Integrator generates it from the sequence CN_SRP_PARTICIPANTS_S1. |
PARTICIPANT_ID |
None |
Oracle Data Integrator populates it with CN_SRP_PARTICIPANTS_ALL.PARTICIPANT_ID. |
START_DATE |
None |
Oracle Data Integrator populates it with CN_SRP_PARTICIPANTS_ALL.START_DATE. |
END_DATE |
None |
Oracle Data Integrator populates it with CN_SRP_PARTICIPANTS_ALL.END_DATE. |
ORG_ID |
None |
Oracle Data Integrator populates it with CN_SRP_PARTICIPANTS_ALL.ORG_ID. |
COST_CENTER |
None |
Oracle Data Integrator does not populate it. |
COUNTRY_CODE |
HZ_PARTIES.COUNTRY_CODE |
If the Oracle Fusion Trading Community column is NULL, then Oracle Data Integrator tries to use the target parameter Target Country. If it is also NULL, then Oracle Data Integrator does not populate COUNTRY_CODE. |
CURRENCY_CODE |
None |
Oracle Data Integrator first tries to use the target parameter Target Participant Home Currency. If it is NULL, then Oracle Data Integrator populates it with the operating currency of the ORG_ID (CN_REPOSITORIES_ALL_B.FUNCTIONAL_CURRENCY). |
One of the steps during implementation is to establish the people who receive incentives. In the Participant Assignment work area, on the Import Participants page the application enables you to search person, party, and resource information in the Oracle Fusion Human Capital Management (HCM) Foundation, Oracle Fusion Trading Community, or Oracle Fusion Resource Manager common objects, then select one or more individuals to import.
There are two types of parameters for you to use when importing participants:
Target: During the Staging and Oracle Incentive Compensation Participant Import process, when the application collects data into the staging table, it runs a script that attempts to map all participant attributes from the source and insert those values (for example, Active Start Date and Currency) into the staged record. When step two of the import process runs, the application applies any selected target parameters to the data it imports into the incentive compensation participant tables, when the value for that attribute is null.
Filter: The import process uses the parameters provided in the filter region as part of a query to run for the selection process. Select one or more of the available parameters to filter selection criteria.
Target Parameters
Business Unit
Required. Set the incentive compensation business unit into which the application imports the participants. During step one of the process, Oracle Data Integrator leaves the staging table value as NULL and for step two, uses it to populate CN_SRP_PARTICIPANTS_ALL.ORG_ID and CN_SRP_PARTICIPANT_DETAILS_ALL.ORG_ID.
Currency
Set it as a default currency during the import if the source application (and the incentive compensation staging table) does not contain this value. You can reset this parameter for each import.
If this target parameter has a value, Oracle Data Integrator uses it to populate CN_SRP_PARTICIPAN_DETAILS_ALL.CURRENCY_CODE.
If the value is NULL in staging, and you do not set the target parameter, Oracle Data Integrator uses the operating currency from CN_REPOSITORIES_ALL_B.
Target Participant Home Currency
If this target parameter has a value, Oracle Data Integrator uses it to populate CN_SRP_PARTICIPANT_DETAILS_ALL.CURRENCY_CODE.
If the staged record currency value is NULL, and you did not select a target parameter, Oracle Data Integrator uses the operating currency from CN_REPOSITORIES_ALL_B.
Analyst Name
If this target parameter has a value, Oracle Data Integrator uses it to populate CN_SRP_PARTICIPANTS_ALL.ANALYST_ID.
If there is no value, Oracle Data Integrator populates CN_SRP_PARTICIPANTS_ALL.ANALYST_ID with NULL.
Target Country
Get the value from the person record in Oracle Fusion Human Capital Management (HCM) or party location for primary address.
If the application does not find one, it leaves the value null. Oracle Data Integrator first tries to use the Oracle Fusion Trading Community Architecture value HZ_PARTIES.COUNTRY to populate CN_SRP_PARTICIPANT_DETAILS_ALL.COUNTRY_CODE.
If it is NULL, then Oracle Data Integrator uses this target parameter value. If the target parameter is also NULL, then CN_SRP_PARTICIPANT_DETAILS_ALL.COUNTRY_CODE is NULL.
Active Start Date
Get the start date from either the PER_ALL_PEOPLE_F.START_DATE or HZ_PARTIES.CREATION_DATE column. Oracle Data Integrator uses it to populate CN_SRP_PARTICIPANTS_ALL.START_DATE and CN_SRP_PARTICIPANT_DETAILS_ALL.START_DATE
Participant Type
Defaults to PARTICIPANT.
Filter Parameters
Important
Use filter parameters whenever possible, to avoid importing extraneous participants because there is no way to delete them after import.
Party Usage
Required. Defaults to Incentive Compensation, if you select a value, then Oracle Data Integrator matches it against the value in HZ_PARTY_USG_ASSIGNMENTS.PARTY_USAGE_CODE, where the HZ_PARTY_USG_ASSIGNMENTS.STATUS_FLAG value is A (active). Oracle Data Integrator uses the LIKE operator to support wildcard characters.
Party Name
Text input, with wildcard support. If you provide a value, Oracle Data Integrator applies the filter condition: UPPER(HZ_PARTIES.PARTY_NAME) LIKE UPPER(parameter value).
HCM Job
Text input, with wildcard support. Oracle Data Integrator uses the following SQL to filter the parties based on job code.
Select *
From hz_parties hp, hz_orig_sys_references ref, per_all_assignments_m assgn, per_jobs_f job
Where ref.ORIG_SYSTEM = 'FUSION_HCM'
And ref.OWNER_TABLE_NAME = 'HZ_PARTIES'
And HR.OWNER_TABLE_ID = HP.PARTY_ID
And HP.ORIG_SYSTEM_REFERENCE = ASSGN.PERSON_ID
And SYSDATE between ASSGN.EFFECTIVE_START_DATE and ASSGN.EFFECTIVE_END_DATE
And ASSGN.JOB_ID = JOB.JOB_ID
And JOB.JOB_CODE LIKE UPPER('parameter value');
Country
Text Input, without wildcard support. If you provide a value, Oracle Data Integrator matches it against the value in HZ_PARTIES.COUNTRY.
Start Date
Required. Oracle Data Integrator matches it against the value in HZ_PARTY_USG_ASSIGNMENTS.EFFECTIVE_START_DATE.
End Date
Oracle Data Integrator matches it against the value in HZ_PARTY_USG_ASSIGNMENTS.EFFECTIVE_END_DATE.
Role
Text input, with wildcard support. If you provide a value, Oracle Data Integrator matches it against the Oracle Fusion Resource Manager value JTF_RS_ROLES_B.ROLE_CODE.
Role Type
Text input, without wildcard support. If you provide a value, Oracle Data Integrator matches it against the value JTF_RS_ROLES_B.ROLE_TYPE.
Person Number
Text input, without wildcard support. If you provide a value, Oracle Data Integrator matches it against the Oracle Fusion HCM value PER_ALL_PEOPLE_F.PERSON_NUMBER.