Skip Headers

Oracle Territory Manager Implementation Guide
Release 12.1
Part Number E13505-04		Go to Table of Contents
Contents 		Go to previous page
Previous 		Go to next page
Next

Creating Custom Matching Attributes

CMA for Account: Party Type

This is an example of creating a custom matching attribute for Sales using the account transaction and the party type attribute.

Modify Script 1

Modify Script 1 to retrieve the relevant account transaction type SQL meta-data.

  1. Set the source ID corresponding to the usage (Sales or Collections). The source ID -1001 corresponds to the Sales usage. To find the desired usage ID query JTF_SOURES_ALL.

    	p_source_id      NUMBER := -1001;

  2. Set the transaction type ID. The ID -1002 corresponds to the Account transaction type ID. To find the desired transaction type ID, query JTF_QUAL_TYPES_ALL

    p_trans_type_id  NUMBER := -1002;

  3. Run the script and save the output to a file. Running this command will save the Account transaction type SQL meta-data to the “R12AccountTT.sql” file.

    SQL>spool <output filename - e.g., "d:\R12AccountTT.sql">
SQL> set serveroutput on size 999999
SQL> @Script1.sql /

Modify Transaction SQL Meta-Data

Modify the transaction type SQL meta-data (R12AccountTT.sql) to get the CMA’s corresponding transaction attribute value.

  1. Add the party_type attribute to each of the following SQLs:

  2. Modify the R12AccountTT.sql file with the appropriate package parameters to make the SQL meta-data change.

    JTY_TRANS_USG_PGM_SQL_PKG.Insert_Row(
   p_source_id => -1001 /* From JTF_SOURCES_ALL*/
  ,p_trans_type_id => -1002 /*From JTF_QUAL_TYPES_ALL*/
  ,p_program_name => 'SALES/ACCOUNT PROGRAM‘ /*FROM JTF_TRANS_USG_PGM_DETAILS*/
  ,p_version_name => '18-NOV-05: CMA PARTY TYPE ADDED'
  ,p_real_time_sql => l_real_time_sql
  ,p_batch_total_sql => l_batch_total_sql
  ,p_batch_incr_sql => l_batch_incr_sql
  ,p_batch_dea_sql => l_batch_dea_sql
  ,p_incr_reassign_sql => l_incr_reassign_sql
  ,p_use_total_for_dea_flag => null
  ,p_enabled_flag => 'Y'
  ,retcode => retcode
  ,errbuf => errbuf);

  3. Run the R12AccountTT.sql file.

    SQL> @R12AccountTT.sql /

Create the Custom Matching Attribute

Create the CMA. The basis of this script is to populate all the necessary information in the following tables:

JTF_SEEDED_QUAL_ALL_B

JTF_SEEDED_QUAL_ALL_TL

JTF_QUAL_USGS_ALL

  1. Ensure that there is not an existing CMA record for the unique matching attribute ID that you will use for the new CMA. 

    set serveroutput on size 999999

DECLARE

  l_real_time_sql varchar2(32000) := NULL;
  l_batch_total_sql varchar2(32000) := NULL;
  l_batch_incr_sql varchar2(32000) := NULL;
  l_batch_dea_sql varchar2(32000) := NULL;
  l_incr_reassign_sql varchar2(32000);
  retcode varchar2(250);
  errbuf  varchar2(1000);BEGIN
     DELETE
     FROM jtf_seeded_qual_all_b
     WHERE seeded_qual_id = -9010;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
     DELETE
     FROM jtf_seeded_qual_all_tl
     WHERE seeded_qual_id = -9010;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
     DELETE
     FROM jtf_qual_usgs_all
     WHERE qual_usg_id = -9010;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

  2. Create the meta-data for the CMA. Follow the comments provided in the script to enter the relevant CMA information. 

      /* The following meta-data will create the custom matching attribute
  ** for the Customer Party Type.
  */
  JTY_CUST_QUAL_PKG.create_qual(

     /* Unique Identifier/Primary Key */
     p_seeded_qual_id             => -9010 /*This number should correspond to the number used in the alias for the attribute*/
    ,p_name                       => 'Party Type (CMA)'
    ,p_description                => 'Party Type (CMA)'
    ,p_language                   => 'US'

    ,/* Sales Usage: FK to
       JTF_SOURCES_ALL.SOURCE_ID*/
     p_source_id                  => -1001

    ,/* Sales Account Transaction Type: FK to
        JTF_QUAL_TYPE_USGS_ALL.QUAL_TYPE_USG_ID*/
     p_trans_type_id              => -1002
    ,p_enabled_flag               => 'N'

    ,/* QUAL_RELATION_FACTOR
     This needs to be the next prime number greater than
     the value from the following SQL:

     SELECT MAX(qual_relation_factor)
     FROM jtf_qual_usgs_all

     The first 1000 primes can be found at:
     http://primes.utm.edu/lists/small/1000.txt

     Custom matching attributes should start at the 303rd prime
     which is 1999. This is to keep 1st to 302nd primes
     for product development to seed matching attributes.
     */
     p_qual_relation_factor       => 1999


    /* The following set of meta-data setups determine
    ** the behavior of the matching attribute in
    ** HTML and Excel UIs: how it is displayed; what
    ** is the LOV SQL; and, what comparison operators
    ** are supported.
    */

    ,/* CONVERT_TO_ID_FLAG: displayed qualifier value is a CHAR
but stored qualifier value is stored as an internal id. For example, customer name                                                                              is displayed as a char but stored by party_id value. */
     p_convert_to_id_flag         => 'N'

    ,/* DISPLAY_TYPE: display type on UI (NUMERIC/CHAR) */
     p_display_type               => 'CHAR'

    ,/* LOV SQL */
    p_html_lov_sql1              =>
     ' SELECT a.meaning col1_value, a.lookup_code col2_value ' || CHR(10) ||
     ' FROM ar_lookups a ' || CHR(10) ||
     ' WHERE a.lookup_type = ''PARTY_TYPE'' ' || CHR(10) ||
     ' AND a.lookup_code IN (''ORGANIZATION'', ''PERSON'') ' || CHR(10) ||
     ' ORDER BY col1_value '

/*Use the following parameters for dependent LOV SQLs */
    ,p_html_lov_sql2              => null
    ,p_html_lov_sql3              => null
    ,p_display_sql1               => null
    ,p_display_sql2               => null
    ,p_display_sql3               => null

/* The p_hierarchy_type value should always be set to null */
    ,p_hierarchy_type             => null 

    ,/* Is the "=" operator supported? */
    p_equal_flag                 => 'Y'
    ,/* Is the "LIKE" operator supported? */
    p_like_flag                  => 'N'
    ,/* Is the "BETWEEN" operator supported? */
    p_between_flag               => 'N'

    /* Values table – this table will store the matching attribute vales of a territory*/
    /* Specify which columns to use by giving it a name for the corresnding custom matching attribute values. In this example, the party type value (person, organization), will be stored in the ‘q9010_low_value_CHAR’ column. In order to avoid confusion, it is recommended that you start the column name with the custom matching attribute unique identifier (ie. “q9010”). Use the following table to determine which column to use based on the display type of the custom matching attribute. You can query JTF_TERR_VALUES_ALL to see existing matching attributes’ display type and the corresponding column(s). */
        ,p_comparison_operator        => 'q9010_cp'
    ,p_low_value_char             => 'q9010_low_value_CHAR'
    ,p_high_value_char            => null
    ,p_low_value_char_id          => null
    ,p_low_value_number           => null
    ,p_high_value_number          => null
    ,p_interest_type_id           => null
    ,p_primary_interest_code_id   => null
    ,p_sec_interest_code_id       => null
    ,p_value1_id                  => null
    ,p_value2_id                  => null
    ,p_value3_id                  => null
    ,p_value4_id                  => null
    ,p_first_char                 => null
    ,p_currency_code              => null


    ,/* Transaction Type Alias/_TRANS table column mapping */
     p_qual_col1                  => 'Q9010_PARTY_TYPE' /*This is the alias used in script #2*/

    ,/* TCA Classification for derivation. This value should remain null*/
     p_alias_rule1                => null

    ,/* Rule SQL. 
In this example we are matching the Q9010_PARTY_TYPE value from the transaction type SQL with the q9010_low_value_CHAR value stored in JTF_TERR_ALL_VALUES.
*/
     p_op_eql                     =>
      ' (A.Q9010_PARTY_TYPE = B.q9010_low_value_CHAR AND B.q9010_cp = ''='')'
    ,p_op_like                    => null
    ,p_op_between                 => NULL
    ,p_op_common_where            => null

    ,p_real_time_select           =>
    'SELECT DISTINCT A.trans_object_id, A.trans_detail_object_id, A.txn_date, B.terr_id, B.absolute_rank, B.top_level_terr_id, B.num_winners'
    ,p_real_time_from             => null
    ,p_real_time_where            =>
    'WHERE '  || CHR(10) ||
    '  B.q9010_low_value_CHAR = A.Q9010_PARTY_TYPE ' || CHR(10) ||
    '  AND B.q9010_cp = ''='' ' || CHR(10) ||
    '  AND B.source_id = -1001 ' || CHR(10) ||
    '  AND A.txn_date between B.start_date and B.end_date'


    ,retcode                      => retcode
    ,errbuf                       => errbuf);

  dbms_output.put_line('Retcode : ' || retcode);
  dbms_output.put_line('Errbuf : ' || errbuf);

  ad_morg.replicate_seed_data(NULL, 'JTF', NULL);

  COMMIT;

exception
  when others then
    dbms_output.put_line('Retcode : ' || retcode);
    dbms_output.put_line('Errbuf : ' || errbuf);
    raise;
end;
/

  3. Run this script.

CMA for Lead: Lead Status

This is an example of creating a custom matching attribute for Sales using the lead transaction and the lead status attribute.

Modify Script 1

Modify Script 1 to retrieve the relevant lead transaction type SQL meta-data.

  1. Set the source ID corresponding to the usage (Sales or Collections). The source ID -1001 corresponds to the Sales usage. To find the desired usage ID query JTF_SOURES_ALL.

    	p_source_id      NUMBER := -1001;

  2. Set the transaction type ID. The ID -1003 corresponds to the Lead transaction type ID. To find the desired transaction type ID, query JTF_QUAL_TYPES_ALL

    p_trans_type_id  NUMBER := -1003;

  3. Run the script and save the output to a file. Running this command will save the Lead transaction type SQL meta-data to the “R12LeadTT.sql” file.

    SQL>spool <output filename - e.g., "d:\R12LeadTT.sql">
SQL> set serveroutput on size 999999
SQL> @Script1.sql /

Modify Transaction SQL Meta-Data

Modify the transaction type SQL meta-data (R12ALeadTT.sql) to get the CMA’s corresponding transaction attribute value.

  1. Add the status_code attribute to each of the following SQLs:

  2. Modify the R12LeadTT.sql file with the appropriate package parameters to make the SQL meta-data change.

    JTY_TRANS_USG_PGM_SQL_PKG.Insert_Row(                                           
p_source_id => -1001                                                            
,p_trans_type_id => -1003                                                       
,p_program_name => 'SALES/LEAD PROGRAM'                                         
,p_version_name => '12-JAN-06: CMA LEAD STATUS ADDED'                            
,p_real_time_sql => l_real_time_sql                                             
,p_batch_total_sql => l_batch_total_sql                                         
,p_batch_incr_sql => l_batch_incr_sql                                           
,p_batch_dea_sql => l_batch_dea_sql                                             
,p_incr_reassign_sql => l_incr_reassign_sql                                     
,p_use_total_for_dea_flag => null                                               
,p_enabled_flag => 'Y'                                                          
,retcode => retcode                                                             
,errbuf => errbuf);                                                             
dbms_output.put_line('SALES/LEAD PROGRAM retcode : ' || retcode);               
dbms_output.put_line('SALES/LEAD PROGRAM errbuf : ' || errbuf);

  3. Run the R12LeadTT.sql file.

    SQL> @R12LeadTT.sql /

Create the Custom Matching Attribute

Create the CMA). The basis of this script is to populate all the necessary information in the following tables:

JTF_SEEDED_QUAL_ALL_B

JTF_SEEDED_QUAL_ALL_TL

JTF_QUAL_USGS_ALL

  1. Ensure that there is not an existing CMA record for the unique matching attribute ID that you will use for the new CMA. 

    set serveroutput on size 999999

DECLARE

  l_real_time_sql varchar2(32000) := NULL;
  l_batch_total_sql varchar2(32000) := NULL;
  l_batch_incr_sql varchar2(32000) := NULL;
  l_batch_dea_sql varchar2(32000) := NULL;
  l_incr_reassign_sql varchar2(32000);
  retcode varchar2(250);
  errbuf  varchar2(1000);

BEGIN

   BEGIN
     DELETE
     FROM jtf_seeded_qual_all_b
     WHERE seeded_qual_id = -9100;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
     DELETE
     FROM jtf_seeded_qual_all_tl
     WHERE seeded_qual_id = -9100;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
     DELETE
     FROM jtf_qual_usgs_all
     WHERE qual_usg_id = -9100;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

  2. Create the meta-data for the CMA. Follow the comments provided in the script to enter the relevant CMA information. 

    

  /* The following meta-data will create the custom qualifier
  ** for the Lead Status.
  */
  JTY_CUST_QUAL_PKG.create_qual(

     /* Unique Identifier/Primary Key */
     p_seeded_qual_id             => -9100
    ,p_name                       => 'Lead Status (CMA)'
    ,p_description                => 'Lead Status (CMA)'
    ,p_language                   => 'US'

    ,/* Sales Usage: FK to
       JTF_SOURCES_ALL.SOURCE_ID*/
     p_source_id                  => -1001

    ,/* Sales Lead Transaction Type: FK to
        JTF_QUAL_TYPE_USGS_ALL.QUAL_TYPE_USG_ID*/
     p_trans_type_id              => -1003

    ,p_enabled_flag               => 'N'

    ,/* QUAL_RELATION_FACTOR
     This needs to be the next prime number greater than
     the value from the following SQL:

     SELECT MAX(qual_relation_factor)
     FROM jtf_qual_usgs_all

     The first 1000 primes can be found at:
     http://primes.utm.edu/lists/small/1000.txt

     Custom qualifiers should start at the 303rd prime
     which is 1999. This is to keep 1st to 302nd primes
     for product development to seed qualifiers.
     */
     p_qual_relation_factor       => 2003


    /* The following set of meta-data setups determine
    ** the behaviour of the matching attribute in
    ** HTML and Excel UIs: how it is displayed; what
    ** is the LOV SQL; and, what comparison operators
    ** are supported.
    */

    ,/* CONVERT_TO_ID_FLAG: displayed qualifier value is a CHAR
        but stored qualifier value is stored as an internal id  */
     p_convert_to_id_flag         => 'N'

    ,/* DISPLAY_TYPE: display type on UI (NUMERIC/CHAR) */
     p_display_type               => 'CHAR'
    ,/* LOV SQL */
    p_html_lov_sql1              =>
     ' SELECT a.meaning col1_value, a.status_code col2_value ' || CHR(10) ||
     ' FROM as_statuses_vl a ' || CHR(10) ||
     ' WHERE a.enabled_flag = ''Y'' ' || CHR(10) ||
     ' AND a.lead_flag = ''Y'' ' || CHR(10) ||
     ' ORDER BY col1_value '

    ,p_html_lov_sql2              => null
    ,p_html_lov_sql3              => null
    ,p_display_sql1               => ' SELECT meaning FROM as_statuses_vl
 WHERE enabled_flag = ''Y'' AND lead_flag = ''Y'' AND STATUS_CODE=  ' 
    ,p_display_sql2               => null
    ,p_display_sql3               => null
,p_hierarchy_type             => null

    ,/* Is the "=" operator supported? */
    p_equal_flag                 => 'Y'
    ,/* Is the "LIKE" operator supported? */
    p_like_flag                  => 'N'
    ,/* Is the "BETWEEN" operator supported? */
    p_between_flag               => 'N'

    /* Values table */
    /* matching attribute comparison operator */
    ,p_comparison_operator        => 'q9100_cp'
    ,p_low_value_char             => 'q9100_low_value_CHAR'
    ,p_high_value_char            => null
    ,p_low_value_char_id          => null
    ,p_low_value_number           => null
    ,p_high_value_number          => null
    ,p_interest_type_id           => null
    ,p_primary_interest_code_id   => null
    ,p_sec_interest_code_id       => null
    ,p_value1_id                  => null
    ,p_value2_id                  => null
    ,p_value3_id                  => null
    ,p_value4_id                  => null
    ,p_first_char                 => null
    ,p_currency_code              => null


    ,/* Transaction Type Alias/_TRANS table column mapping */
     p_qual_col1                  => 'Q9100_LEAD_STATUS'

    ,/* TCA Classification for derivation */
     p_alias_rule1                => null

    ,/* OP_EQL FOR BATCH-MODE ASSIGNMENT:
     ** Since the AS_SALES_LEADS.STATUS_CODE value is not passed to the
     ** assignment API, we must the PK (SALES_LEAD_ID) to get the
     ** STATUS_CODE value from AS_SALES_LEADS and compare to the
     ** territory qualifier value
     */
     p_op_eql                     =>
      ' (A.Q9100_LEAD_STATUS = B.q9100_low_value_CHAR AND B.q9100_cp = ''='')'
    ,p_op_like                    => null
    ,p_op_between                 => NULL
    ,p_op_common_where            => null

    ,p_real_time_select           =>
    'SELECT DISTINCT A.trans_object_id, A.trans_detail_object_id, A.txn_date, B.terr_id, B.absolute_rank, B.top_level_terr_id, B.num_winners'
    ,p_real_time_from             => null
    ,p_real_time_where            =>
    'WHERE '  || CHR(10) ||
    '  B.q9010_low_value_CHAR = A.Q9100_LEAD_STATUS ' || CHR(10) ||
    '  AND B.q9010_cp = ''='' ' || CHR(10) ||
    '  AND B.source_id = -1001 ' || CHR(10) ||
    '  AND A.txn_date between B.start_date and B.end_date'


    ,retcode                      => retcode
    ,errbuf                       => errbuf);

  dbms_output.put_line('Retcode : ' || retcode);
  dbms_output.put_line('Errbuf : ' || errbuf);

  ad_morg.replicate_seed_data(NULL, 'JTF', NULL);

exception
  when others then
    dbms_output.put_line('Retcode : ' || retcode);
    dbms_output.put_line('Errbuf : ' || errbuf);
    raise;
end;
/

  3. Run this script.

CMA for Quote: Quote Source

This is an example of creating a custom matching attribute for Sales using the quote transaction and the quote source attribute.

Modify Script 1

Modify Script 1 to retrieve the relevant quote transaction type SQL meta-data.

  1. Set the source ID corresponding to the usage (Sales or Collections). The source ID -1001 corresponds to the Sales usage. To find the desired usage ID query JTF_SOURES_ALL.

    	p_source_id      NUMBER := -1001;

  2. Set the transaction type ID. The ID -1105 corresponds to the Quotetransaction type ID. To find the desired transaction type ID, query JTF_QUAL_TYPES_ALL

    p_trans_type_id  NUMBER := -1105;

  3. Run the script and save the output to a file. Running this command will save the Quote transaction type SQL meta-data to the “R12QuoteTT.sql” file.

    SQL>spool <output filename - e.g., "d:\R12QuoteTT.sql">
SQL> set serveroutput on size 999999
SQL> @Script1.sql /

Modify Transaction SQL Meta-Data

Modify the transaction type SQL meta-data (R12QuoteTT.sql,) to get the CMA’s corresponding transaction attribute value.

  1. Add the quote_source_code attribute to each of the following SQLs:

  2. Modify the R12QuoteTT.sql file with the appropriate package parameters to make the SQL meta-data change.

    JTY_TRANS_USG_PGM_SQL_PKG.Insert_Row(                                           
p_source_id => -1001                                                            
,p_trans_type_id => -1105                                                       
,p_program_name => 'SALES/QUOTE PROGRAM'                                        
,p_version_name => '12-JAN-06: CMA QUOTE SOURCE ADDED'                            
,p_real_time_sql => l_real_time_sql                                             
,p_batch_total_sql => l_batch_total_sql                                         
,p_batch_incr_sql => l_batch_incr_sql                                           
,p_batch_dea_sql => l_batch_dea_sql                                             
,p_incr_reassign_sql => l_incr_reassign_sql                                     
,p_use_total_for_dea_flag => null                                               
,p_enabled_flag => 'Y'                                                          
,retcode => retcode                                                             
,errbuf => errbuf);                                                             
dbms_output.put_line('SALES/QUOTE PROGRAM retcode : ' || retcode);              
dbms_output.put_line('SALES/QUOTE PROGRAM errbuf : ' || errbuf);

  3. Run the R12QuoteTT.sql file.

    SQL> @R12QuoteTT.sql /

Create the Custom Matching Attribute

Create the CMA. The basis of this script is to populate all the necessary information in the following tables:

JTF_SEEDED_QUAL_ALL_B

JTF_SEEDED_QUAL_ALL_TL

JTF_QUAL_USGS_ALL

  1. Ensure that there is not an existing CMA record for the unique matching attribute ID that you will use for the new CMA. 

    set serveroutput on size 999999

DECLARE

  l_real_time_sql varchar2(32000) := NULL;
  l_batch_total_sql varchar2(32000) := NULL;
  l_batch_incr_sql varchar2(32000) := NULL;
  l_batch_dea_sql varchar2(32000) := NULL;
  l_incr_reassign_sql varchar2(32000);
  retcode varchar2(250);
  errbuf  varchar2(1000);BEGIN

   BEGIN
     DELETE
     FROM jtf_seeded_qual_all_b
     WHERE seeded_qual_id = -9101;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
     DELETE
     FROM jtf_seeded_qual_all_tl
     WHERE seeded_qual_id = -9101;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
     DELETE
     FROM jtf_qual_usgs_all
     WHERE qual_usg_id = -9101;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

  2. Create the meta-data for the CMA. Follow the comments provided in the script to enter the relevant CMA information. 

      /* The following meta-data will create the custom qualifier
  ** for the Lead Status.
  */
  JTY_CUST_QUAL_PKG.create_qual(

     /* Unique Identifier/Primary Key */
     p_seeded_qual_id             => -9101
    ,p_name                       => 'Quote Source (CMA)'
    ,p_description                => 'Custom Matching Attribute for Source Marketing Campaign of a Quote'
    ,p_language                   => 'US'

    ,/* Sales Usage: FK to
       JTF_SOURCES_ALL.SOURCE_ID*/
     p_source_id                  => -1001

    ,/* Sales Lead Transaction Type: FK to
        JTF_QUAL_TYPE_USGS_ALL.QUAL_TYPE_USG_ID*/
     p_trans_type_id              => -1105

    ,p_enabled_flag               => 'N'

    ,/* QUAL_RELATION_FACTOR
     This needs to be the next prime number greater than
     the value from the following SQL:

     SELECT MAX(qual_relation_factor)
     FROM jtf_qual_usgs_all

     The first 1000 primes can be found at:
     http://primes.utm.edu/lists/small/1000.txt

     Custom qualifiers should start at the 303rd prime
     which is 1999. This is to keep 1st to 302nd primes
     for product development to seed qualifiers.
     */
     p_qual_relation_factor       => 2017


    /* The following set of meta-data setups determine
    ** the behaviour of the matching attribute in
    ** HTML and Excel UIs: how it is displayed; what
    ** is the LOV SQL; and, what comparison operators
    ** are supported.
    */

    ,/* CONVERT_TO_ID_FLAG: displayed qualifier value is a CHAR
        but stored qualifier value is stored as an internal id  */
     p_convert_to_id_flag         => 'N'

    ,/* DISPLAY_TYPE: display type on UI (NUMERIC/CHAR) */
     p_display_type               => 'CHAR'
    ,/* LOV SQL */
    p_html_lov_sql1              =>
     ' SELECT c.campaign_name || '': '' || b.status_code || '': '' || a.source_code || '': '' || c.description col1_value, '  || CHR(10) ||
     ' TO_CHAR(a.source_code_id) col2_value ' || CHR(10) ||
     ' FROM ams_source_codes a, ams_campaigns_all_b b, ams_campaigns_all_tl c  ' || CHR(10) ||
     ' WHERE b.source_code = a.source_code ' || CHR(10) ||
     ' AND a.arc_source_code_for = ''CAMP'' ' || CHR(10) ||
     ' AND b.campaign_id = c.campaign_id ' || CHR(10) ||
     ' AND c.LANGUAGE = userenv(''LANG'') ' || CHR(10) ||
     ' ORDER BY b.status_code, c.campaign_name '

    ,p_html_lov_sql2              => null
    ,p_html_lov_sql3              => null
    ,p_display_sql1               => null
    ,p_display_sql2               => null
    ,p_display_sql3               => null
    ,p_hierarchy_type             => null

    ,/* Is the "=" operator supported? */
    p_equal_flag                 => 'Y'
    ,/* Is the "LIKE" operator supported? */
    p_like_flag                  => 'N'
    ,/* Is the "BETWEEN" operator supported? */
    p_between_flag               => 'N'

    /* Values table */
    /* matching attribute comparison operator */
    ,p_comparison_operator        => 'q9101_cp'
    ,p_low_value_char             => 'q9101_low_value_char'
    ,p_high_value_char            => null
    ,p_low_value_char_id          => null
    ,p_low_value_number           => null
    ,p_high_value_number          => null
    ,p_interest_type_id           => null
    ,p_primary_interest_code_id   => null
    ,p_sec_interest_code_id       => null
    ,p_value1_id                  => null
    ,p_value2_id                  => null
    ,p_value3_id                  => null
    ,p_value4_id                  => null
    ,p_first_char                 => null
    ,p_currency_code              => null


    ,/* Transaction Type Alias/_TRANS table column mapping */
     p_qual_col1                  => 'Q9101_QUOTE_SOURCE'

    ,/* TCA Classification for derivation */
     p_alias_rule1                => null

    ,/* OP_EQL FOR BATCH-MODE ASSIGNMENT:
     ** Since the AS_SALES_LEADS.STATUS_CODE value is not passed to the
     ** assignment API, we must the PK (SALES_LEAD_ID) to get the
     ** STATUS_CODE value from AS_SALES_LEADS and compare to the
     ** territory qualifier value
     */
     p_op_eql                     =>
      ' (A.Q9101_QUOTE_SOURCE = B.q9101_low_value_CHAR AND B.q9101_cp = ''='')'
    ,p_op_like                    => null
    ,p_op_between                 => NULL
    ,p_op_common_where            => null

    ,p_real_time_select           =>
    'SELECT DISTINCT A.trans_object_id, A.trans_detail_object_id, A.txn_date, B.terr_id, B.absolute_rank, B.top_level_terr_id, B.num_winners'
    ,p_real_time_from             => null
    ,p_real_time_where            =>
    'WHERE '  || CHR(10) ||
    '  B.q9101_low_value_CHAR = A.Q9101_QUOTE_SOURCE ' || CHR(10) ||
    '  AND B.q9101_cp = ''='' ' || CHR(10) ||
    '  AND B.source_id = -1001 ' || CHR(10) ||
    '  AND A.txn_date between B.start_date and B.end_date'


    ,retcode                      => retcode
    ,errbuf                       => errbuf);

  dbms_output.put_line('Retcode : ' || retcode);
  dbms_output.put_line('Errbuf : ' || errbuf);

  ad_morg.replicate_seed_data(NULL, 'JTF', NULL);

exception
  when others then
    dbms_output.put_line('Retcode : ' || retcode);
    dbms_output.put_line('Errbuf : ' || errbuf);
    raise;
end;
/

  3. Run this script.

CMA for Proposal: Proposal Status

This is an example of creating a custom matching attribute for Sales using the proposal transaction and the proposal status attribute.

Modify Script 1

Modify Script 1 to retrieve the relevant proposal transaction type SQL meta-data.

  1. Set the source ID corresponding to the usage (Sales or Collections). The source ID -1001 corresponds to the Sales usage. To find the desired usage ID query JTF_SOURES_ALL.

    	p_source_id      NUMBER := -1001;

  2. Set the transaction type ID. The ID -1106 corresponds to the Proposal transaction type ID. To find the desired transaction type ID, query JTF_QUAL_TYPES_ALL

    p_trans_type_id  NUMBER := -1106;

  3. Run the script and save the output to a file. Running this command will save the Proposal transaction type SQL meta-data to the “R12ProposalTT.sql” file.

    SQL>spool <output filename - e.g., "d:\R12ProposalTT.sql">
SQL> set serveroutput on size 999999
SQL> @Script1.sql /

Modify Transaction SQL Meta-Data

Modify the transaction type SQL meta-data (R12AccountTT.sql) to get the CMA’s corresponding transaction attribute value.

  1. Add the proposal_status attribute to each of the following SQLs:

  2. Modify the R12ProposalTT.sql file with the appropriate package parameters to make the SQL meta-data change.

    JTY_TRANS_USG_PGM_SQL_PKG.Insert_Row(                                           
p_source_id => -1001                                                            
,p_trans_type_id => -1106                                                       
,p_program_name => 'SALES/PROPOSAL PROGRAM'                                     
,p_version_name => '12-JAN-06: CMA PROPOSAL STATUS ADDED'                            
,p_real_time_sql => l_real_time_sql                                             
,p_batch_total_sql => l_batch_total_sql                                         
,p_batch_incr_sql => l_batch_incr_sql                                           
,p_batch_dea_sql => l_batch_dea_sql                                             
,p_incr_reassign_sql => l_incr_reassign_sql                                     
,p_use_total_for_dea_flag => null                                               
,p_enabled_flag => 'Y'                                                          
,retcode => retcode                                                             
,errbuf => errbuf);                                                             
dbms_output.put_line('SALES/PROPOSAL PROGRAM retcode : ' || retcode);           
dbms_output.put_line('SALES/PROPOSAL PROGRAM errbuf : ' || errbuf);

  3. Run the R12ProposalTT.sql file.

    SQL> @R12ProposalTT.sql /

Create the Custom Matching Attribute

Create the CMA. The basis of this script is to populate all the necessary information in the following tables:

JTF_SEEDED_QUAL_ALL_B

JTF_SEEDED_QUAL_ALL_TL

JTF_QUAL_USGS_ALL

  1. Ensure that there is not an existing CMA record for the unique matching attribute ID that you will use for the new CMA. 

    set serveroutput on size 999999

DECLARE

  l_real_time_sql varchar2(32000) := NULL;
  l_batch_total_sql varchar2(32000) := NULL;
  l_batch_incr_sql varchar2(32000) := NULL;
  l_batch_dea_sql varchar2(32000) := NULL;
  l_incr_reassign_sql varchar2(32000);
  retcode varchar2(250);
  errbuf  varchar2(1000);

BEGIN

   BEGIN
     DELETE
     FROM jtf_seeded_qual_all_b
     WHERE seeded_qual_id = -9102;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
     DELETE
     FROM jtf_seeded_qual_all_tl
     WHERE seeded_qual_id = -9102;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
     DELETE
     FROM jtf_qual_usgs_all
     WHERE qual_usg_id = -9102;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

  2. Create the meta-data for the CMA. Follow the comments provided in the script to enter the relevant CMA information. 

      /* The following meta-data will create the custom qualifier
  ** for the Proposal Status.
  */
  JTY_CUST_QUAL_PKG.create_qual(

     /* Unique Identifier/Primary Key */
     p_seeded_qual_id             => -9102
    ,p_name                       => 'Proposal Status (CMA)'
    ,p_description                => 'Custom Matching Attribute for Proposal Status'
    ,p_language                   => 'US'

    ,/* Sales Usage: FK to
       JTF_SOURCES_ALL.SOURCE_ID*/
     p_source_id                  => -1001

    ,/* Sales Lead Transaction Type: FK to
        JTF_QUAL_TYPE_USGS_ALL.QUAL_TYPE_USG_ID*/
     p_trans_type_id              => -1106

    ,p_enabled_flag               => 'N'

    ,/* QUAL_RELATION_FACTOR
     This needs to be the next prime number greater than
     the value from the following SQL:

     SELECT MAX(qual_relation_factor)
     FROM jtf_qual_usgs_all

     The first 1000 primes can be found at:
     http://primes.utm.edu/lists/small/1000.txt

     Custom qualifiers should start at the 303rd prime
     which is 1999. This is to keep 1st to 302nd primes
     for product development to seed qualifiers.
     */
     p_qual_relation_factor       => 2029


    /* The following set of meta-data setups determine
    ** the behaviour of the matching attribute in
    ** HTML and Excel UIs: how it is displayed; what
    ** is the LOV SQL; and, what comparison operators
    ** are supported.
    */

    ,/* CONVERT_TO_ID_FLAG: displayed qualifier value is a CHAR
        but stored qualifier value is stored as an internal id  */
     p_convert_to_id_flag         => 'N'

    ,/* DISPLAY_TYPE: display type on UI (NUMERIC/CHAR) */
     p_display_type               => 'CHAR'
    ,/* LOV SQL */
    p_html_lov_sql1              => NULL
    ,p_html_lov_sql2              => null
    ,p_html_lov_sql3              => null
    ,p_display_sql1               => null
    ,p_display_sql2               => null
    ,p_display_sql3               => null
    ,p_hierarchy_type             => null

    ,/* Is the "=" operator supported? */
    p_equal_flag                 => 'Y'
    ,/* Is the "LIKE" operator supported? */
    p_like_flag                  => 'N'
    ,/* Is the "BETWEEN" operator supported? */
    p_between_flag               => 'N'

    /* Values table */
    /* matching attribute comparison operator */
    ,p_comparison_operator        => 'q9102_cp'
    ,p_low_value_char             => 'q9102_low_value_CHAR'
    ,p_high_value_char            => null
    ,p_low_value_char_id          => null
    ,p_low_value_number           => null
    ,p_high_value_number          => null
    ,p_interest_type_id           => null
    ,p_primary_interest_code_id   => null
    ,p_sec_interest_code_id       => null
    ,p_value1_id                  => null
    ,p_value2_id                  => null
    ,p_value3_id                  => null
    ,p_value4_id                  => null
    ,p_first_char                 => null
    ,p_currency_code              => null


    ,/* Transaction Type Alias/_TRANS table column mapping */
     p_qual_col1                  => 'Q9102_PROPOSAL_STATUS'

    ,/* TCA Classification for derivation */
     p_alias_rule1                => null

    ,/* OP_EQL FOR BATCH-MODE ASSIGNMENT:
     ** Since the AS_SALES_LEADS.STATUS_CODE value is not passed to the
     ** assignment API, we must the PK (SALES_LEAD_ID) to get the
     ** STATUS_CODE value from AS_SALES_LEADS and compare to the
     ** territory qualifier value
     */
     p_op_eql                     =>
      ' (A.Q9102_PROPOSAL_STATUS = B.q9102_low_value_CHAR AND B.q9102_cp = ''='')'
    ,p_op_like                    => null
    ,p_op_between                 => NULL
    ,p_op_common_where            => null

    ,p_real_time_select           =>
    'SELECT DISTINCT A.trans_object_id, A.trans_detail_object_id, A.txn_date, B.terr_id, B.absolute_rank, B.top_level_terr_id, B.num_winners'
    ,p_real_time_from             => null
    ,p_real_time_where            =>
    'WHERE '  || CHR(10) ||
    '  B.q9102_low_value_CHAR = A.Q9102_PROPOSAL_STATUS ' || CHR(10) ||
    '  AND B.q9102_cp = ''='' ' || CHR(10) ||
    '  AND B.source_id = -1001 ' || CHR(10) ||
    '  AND A.txn_date between B.start_date and B.end_date'


    ,retcode                      => retcode
    ,errbuf                       => errbuf);

  dbms_output.put_line('Retcode : ' || retcode);
  dbms_output.put_line('Errbuf : ' || errbuf);

  ad_morg.replicate_seed_data(NULL, 'JTF', NULL);

exception
  when others then
    dbms_output.put_line('Retcode : ' || retcode);
    dbms_output.put_line('Errbuf : ' || errbuf);
    raise;
end;
/

  3. Run this script.

CMA for Opportunity: Budget Status

This is an example of creating a custom matching attribute for Sales using the opportunity transaction and the budget status attribute.

Modify Script 1

Modify Script 1 to retrieve the relevant Opportunity transaction type SQL meta-data.

  1. Set the source ID corresponding to the usage (Sales or Collections). The source ID -1001 corresponds to the Sales usage. To find the desired usage ID query JTF_SOURES_ALL.

    	p_source_id      NUMBER := -1001;

  2. Set the transaction type ID. The ID -1004 corresponds to the Opportunity transaction type ID. To find the desired transaction type ID, query JTF_QUAL_TYPES_ALL

    p_trans_type_id  NUMBER := -1004;

  3. Run the script and save the output to a file. Running this command will save the Opportunity transaction type SQL meta-data to the “R12OpportunityTT.sql” file.

    SQL>spool <output filename - e.g., "d:\R12OpportunityTT.sql">
SQL> set serveroutput on size 999999
SQL> @Script1.sql /

Modify Transaction SQL Meta-Data

Modify the transaction type SQL meta-data (R12OpportunityTT.sql) to get the CMA’s corresponding transaction attribute value.

  1. Add the budget_status_code attribute to each of the following SQLs:

  2. Modify the R12OpportunityTT.sql file with the appropriate package parameters to make the SQL meta-data change.

    JTY_TRANS_USG_PGM_SQL_PKG.Insert_Row(                                           
p_source_id => -1001                                                            
,p_trans_type_id => -1004                                                       
,p_program_name => 'SALES/OPPORTUNITY PROGRAM'                                  
,p_version_name => '12-JAN-06: CMA BUDGET STATUS ADDED'                            
,p_real_time_sql => l_real_time_sql                                             
,p_batch_total_sql => l_batch_total_sql                                         
,p_batch_incr_sql => l_batch_incr_sql                                           
,p_batch_dea_sql => l_batch_dea_sql                                             
,p_incr_reassign_sql => l_incr_reassign_sql                                     
,p_use_total_for_dea_flag => null                                               
,p_enabled_flag => 'Y'                                                          
,retcode => retcode                                                             
,errbuf => errbuf);                                                             
dbms_output.put_line('SALES/OPPORTUNITY PROGRAM retcode : ' || retcode);        
dbms_output.put_line('SALES/OPPORTUNITY PROGRAM errbuf : ' || errbuf);

  3. Run the R12OpportunityTT.sql file.

    SQL> @R12OpportunityTT.sql /

Create the Custom Matching Attribute

Create the CMA. The basis of this script is to populate all the necessary information in the following tables:

JTF_SEEDED_QUAL_ALL_B

JTF_SEEDED_QUAL_ALL_TL

JTF_QUAL_USGS_ALL

  1. Ensure that there is not an existing CMA record for the unique matching attribute ID that you will use for the new CMA. 

    set serveroutput on size 999999

DECLARE

  l_real_time_sql varchar2(32000) := NULL;
  l_batch_total_sql varchar2(32000) := NULL;
  l_batch_incr_sql varchar2(32000) := NULL;
  l_batch_dea_sql varchar2(32000) := NULL;
  l_incr_reassign_sql varchar2(32000);
  retcode varchar2(250);
  errbuf  varchar2(1000);

BEGIN

   BEGIN
     DELETE
     FROM jtf_seeded_qual_all_b
     WHERE seeded_qual_id = -9103;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
     DELETE
     FROM jtf_seeded_qual_all_tl
     WHERE seeded_qual_id = -9103;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
     DELETE
     FROM jtf_qual_usgs_all
     WHERE qual_usg_id = -9103;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

  2. Create the meta-data for the CMA. Follow the comments provided in the script to enter the relevant CMA information. 

      /* The following meta-data will create the custom qualifier
  ** for the Lead Status.
  */
  JTY_CUST_QUAL_PKG.create_qual(

     /* Unique Identifier/Primary Key */
     p_seeded_qual_id             => -9103
    ,p_name                       => 'Opportunity Budget Status (CMA)'
    ,p_description                => 'Custom Matching Attribute for Opportunity Budget Status'
    ,p_language                   => 'US'

    ,/* Sales Usage: FK to
       JTF_SOURCES_ALL.SOURCE_ID*/
     p_source_id                  => -1001

    ,/* Sales Transaction Type: FK to
        JTF_QUAL_TYPE_USGS_ALL.QUAL_TYPE_USG_ID*/
     p_trans_type_id              => -1004

    ,p_enabled_flag               => 'N'

    ,/* QUAL_RELATION_FACTOR
     This needs to be the next prime number greater than
     the value from the following SQL:

     SELECT MAX(qual_relation_factor)
     FROM jtf_qual_usgs_all

     The first 1000 primes can be found at:
     http://primes.utm.edu/lists/small/1000.txt

     Custom qualifiers should start at the 303rd prime
     which is 1999. This is to keep 1st to 302nd primes
     for product development to seed qualifiers.
     */
     p_qual_relation_factor       => 2027


    /* The following set of meta-data setups determine
    ** the behaviour of the matching attribute in
    ** HTML and Excel UIs: how it is displayed; what
    ** is the LOV SQL; and, what comparison operators
    ** are supported.
    */

    ,/* CONVERT_TO_ID_FLAG: displayed qualifier value is a CHAR
        but stored qualifier value is stored as an internal id  */
     p_convert_to_id_flag         => 'N'

    ,/* DISPLAY_TYPE: display type on UI (NUMERIC/CHAR) */
     p_display_type               => 'CHAR'
    ,/* LOV SQL */
    p_html_lov_sql1              =>
     ''

    ,p_html_lov_sql2              => null
    ,p_html_lov_sql3              => null
    ,p_display_sql1               => null
    ,p_display_sql2               => null
    ,p_display_sql3               => null
    ,p_hierarchy_type             => null

    ,/* Is the "=" operator supported? */
    p_equal_flag                 => 'Y'
    ,/* Is the "LIKE" operator supported? */
    p_like_flag                  => 'N'
    ,/* Is the "BETWEEN" operator supported? */
    p_between_flag               => 'N'

    /* Values table */
    /* matching attribute comparison operator */
    ,p_comparison_operator        => 'q9103_cp'
    ,p_low_value_char             => 'q9103_low_value_CHAR'
    ,p_high_value_char            => null
    ,p_low_value_char_id          => null
    ,p_low_value_number           => null
    ,p_high_value_number          => null
    ,p_interest_type_id           => null
    ,p_primary_interest_code_id   => null
    ,p_sec_interest_code_id       => null
    ,p_value1_id                  => null
    ,p_value2_id                  => null
    ,p_value3_id                  => null
    ,p_value4_id                  => null
    ,p_first_char                 => null
    ,p_currency_code              => null


    ,/* Transaction Type Alias/_TRANS table column mapping */
     p_qual_col1                  => 'Q9103_BUDGET_STATUS'

    ,/* TCA Classification for derivation */
     p_alias_rule1                => null

    ,/* OP_EQL FOR BATCH-MODE ASSIGNMENT:
     ** Since the AS_SALES_LEADS.STATUS_CODE value is not passed to the
     ** assignment API, we must the PK (SALES_LEAD_ID) to get the
     ** STATUS_CODE value from AS_SALES_LEADS and compare to the
     ** territory qualifier value
     */
     p_op_eql                     =>
      ' (A.Q9103_BUDGET_STATUS = B.q9103_low_value_CHAR AND B.q9103_cp = ''='')'
    ,p_op_like                    => null
    ,p_op_between                 => NULL
    ,p_op_common_where            => null

    ,p_real_time_select           =>
    'SELECT DISTINCT A.trans_object_id, A.trans_detail_object_id, A.txn_date, B.terr_id, B.absolute_rank, B.top_level_terr_id, B.num_winners'
    ,p_real_time_from             => null
    ,p_real_time_where            =>
    'WHERE '  || CHR(10) ||
    '  B.q9103_low_value_CHAR = A.Q9103_BUDGET_STATUS ' || CHR(10) ||
    '  AND B.q9103_cp = ''='' ' || CHR(10) ||
    '  AND B.source_id = -1001 ' || CHR(10) ||
    '  AND A.txn_date between B.start_date and B.end_date'


    ,retcode                      => retcode
    ,errbuf                       => errbuf);

  dbms_output.put_line('Retcode : ' || retcode);
  dbms_output.put_line('Errbuf : ' || errbuf);

  ad_morg.replicate_seed_data(NULL, 'JTF', NULL);

exception
  when others then
    dbms_output.put_line('Retcode : ' || retcode);
    dbms_output.put_line('Errbuf : ' || errbuf);
    raise;
end;
/

  3. Run this script.

CMA for Oracle Collections, Customer: Customer Party Type

This is an example of creating a custom matching attribute for Sales using the Oracle Collections customer transaction and the customer party type attribute.

Modify Script 1

Modify Script 1 to retrieve the relevant customer transaction type SQL meta-data.

  1. Set the source ID corresponding to the usage (Sales or Collections). The source ID -1600 corresponds to the Collections usage. To find the desired usage ID query JTF_SOURES_ALL.

    	p_source_id      NUMBER := -1600;

  2. Set the transaction type ID. The ID -1602 corresponds to the Customer transaction type ID. To find the desired transaction type ID, query JTF_QUAL_TYPES_ALL

    p_trans_type_id  NUMBER := -1602;

  3. Run the script and save the output to a file. Running this command will save the Customer transaction type SQL meta-data to the “R12CustomerTT.sql” file.

    SQL>spool <output filename - e.g., "d:\R12CustomerTT.sql">
SQL> set serveroutput on size 999999
SQL> @Script1.sql /

Modify Transaction SQL Meta-Data

Modify the transaction type SQL meta-data (R12CustomerTT.sql) to get the CMA’s corresponding transaction attribute value.

  1. Add the party_type attribute to each of the following SQLs:

  2. Modify the R12CustomerTT.sql file with the appropriate package parameters to make the SQL meta-data change.

    JTY_TRANS_USG_PGM_SQL_PKG.Insert_Row(                                           
p_source_id => -1600                                                            
,p_trans_type_id => -1601                                                       
,p_program_name => 'COLLECTIONS/CUSTOMER PROGRAM'                               
,p_version_name => '16-JAN-06: CMA PARTY TYPE ADDED'                            
,p_real_time_sql => l_real_time_sql                                             
,p_batch_total_sql => l_batch_total_sql                                         
,p_batch_incr_sql => l_batch_incr_sql                                           
,p_batch_dea_sql => l_batch_dea_sql                                             
,p_incr_reassign_sql => l_incr_reassign_sql                                     
,p_use_total_for_dea_flag => null                                               
,p_enabled_flag => 'Y'                                                          
,retcode => retcode                                                             
,errbuf => errbuf);                                                             
dbms_output.put_line('COLLECTIONS/CUSTOMER PROGRAM retcode : ' || retcode);     
dbms_output.put_line('COLLECTIONS/CUSTOMER PROGRAM errbuf : ' || errbuf);       
COMMIT;                                                                         
END;                                                                            
/

  3. Run the R12CustomerTT.sql file.

    SQL> @R12CustomerTT.sql /

Create the Custom Matching Attribute

Create the CMA. The basis of this script is to populate all the necessary information in the following tables:

JTF_SEEDED_QUAL_ALL_B

JTF_SEEDED_QUAL_ALL_TL

JTF_QUAL_USGS_ALL

  1. Ensure that there is not an existing CMA record for the unique matching attribute ID that you will use for the new CMA. 

    set serveroutput on size 999999

DECLARE

  l_real_time_sql varchar2(32000) := NULL;
  l_batch_total_sql varchar2(32000) := NULL;
  l_batch_incr_sql varchar2(32000) := NULL;
  l_batch_dea_sql varchar2(32000) := NULL;
  l_incr_reassign_sql varchar2(32000);
  retcode varchar2(250);
  errbuf  varchar2(1000);

BEGIN

   BEGIN
     DELETE
     FROM jtf_seeded_qual_all_b
     WHERE seeded_qual_id = -9600;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
     DELETE
     FROM jtf_seeded_qual_all_tl
     WHERE seeded_qual_id = -9600;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
     DELETE
     FROM jtf_qual_usgs_all
     WHERE qual_usg_id = -9600;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

  2. Create the meta-data for the CMA. Follow the comments provided in the script to enter the relevant CMA information. 

      /* The following meta-data will create the custom qualifier
  ** for the Lead Status.
  */
  JTY_CUST_QUAL_PKG.create_qual(

     /* Unique Identifier/Primary Key */
     p_seeded_qual_id             => -9600
    ,p_name                       => 'Customer Party Type (CMA)'
    ,p_description                => 'Customer Party Type (CMA)'
    ,p_language                   => 'US'

    ,/* Sales Usage: FK to
       JTF_SOURCES_ALL.SOURCE_ID*/
     p_source_id                  => -1600

    ,/* Sales Lead Transaction Type: FK to
        JTF_QUAL_TYPE_USGS_ALL.QUAL_TYPE_USG_ID*/
     p_trans_type_id              => -1601

    ,p_enabled_flag               => 'N'

    ,/* QUAL_RELATION_FACTOR
     This needs to be the next prime number greater than
     the value from the following SQL:

     SELECT MAX(qual_relation_factor)
     FROM jtf_qual_usgs_all

     The first 1000 primes can be found at:
     http://primes.utm.edu/lists/small/1000.txt

     Custom qualifiers should start at the 303rd prime
     which is 1999. This is to keep 1st to 302nd primes
     for product development to seed qualifiers.
     */
     p_qual_relation_factor       => 2011


    /* The following set of meta-data setups determine
    ** the behaviour of the matching attribute in
    ** HTML and Excel UIs: how it is displayed; what
    ** is the LOV SQL; and, what comparison operators
    ** are supported.
    */

    ,/* CONVERT_TO_ID_FLAG: displayed qualifier value is a CHAR
        but stored qualifier value is stored as an internal id  */
     p_convert_to_id_flag         => 'N'

    ,/* DISPLAY_TYPE: display type on UI (NUMERIC/CHAR) */
     p_display_type               => 'CHAR'
    ,/* LOV SQL */
    p_html_lov_sql1              =>
     ' SELECT a.meaning col1_value, a.lookup_code col2_value ' || CHR(10) ||
     ' FROM ar_lookups a ' || CHR(10) ||
     ' WHERE a.lookup_type = ''PARTY_TYPE'' ' || CHR(10) ||
     ' AND a.lookup_code IN (''ORGANIZATION'', ''PERSON'') ' || CHR(10) ||
     ' ORDER BY col1_value '

    ,p_html_lov_sql2              => null
    ,p_html_lov_sql3              => null
    ,p_display_sql1               => null
    ,p_display_sql2               => null
    ,p_display_sql3               => null
    ,p_hierarchy_type             => null

    ,/* Is the "=" operator supported? */
    p_equal_flag                 => 'Y'
    ,/* Is the "LIKE" operator supported? */
    p_like_flag                  => 'N'
    ,/* Is the "BETWEEN" operator supported? */
    p_between_flag               => 'N'

    /* Values table */
    /* matching attribute comparison operator */
    ,p_comparison_operator        => 'q9600_cp'
    ,p_low_value_char             => 'q9600_low_value_CHAR'
    ,p_high_value_char            => null
    ,p_low_value_char_id          => null
    ,p_low_value_number           => null
    ,p_high_value_number          => null
    ,p_interest_type_id           => null
    ,p_primary_interest_code_id   => null
    ,p_sec_interest_code_id       => null
    ,p_value1_id                  => null
    ,p_value2_id                  => null
    ,p_value3_id                  => null
    ,p_value4_id                  => null
    ,p_first_char                 => null
    ,p_currency_code              => null


    ,/* Transaction Type Alias/_TRANS table column mapping */
     p_qual_col1                  => 'Q9600_PARTY_TYPE'

    ,/* TCA Classification for derivation */
     p_alias_rule1                => null

    ,/* OP_EQL FOR BATCH-MODE ASSIGNMENT:
     ** Since the AS_SALES_LEADS.STATUS_CODE value is not passed to the
     ** assignment API, we must the PK (SALES_LEAD_ID) to get the
     ** STATUS_CODE value from AS_SALES_LEADS and compare to the
     ** territory qualifier value
     */
     p_op_eql                     =>
      ' (A.Q9600_PARTY_TYPE = B.q9600_low_value_CHAR AND B.q9600_cp = ''='')'
    ,p_op_like                    => null
    ,p_op_between                 => NULL
    ,p_op_common_where            => null

    ,p_real_time_select           =>
    'SELECT DISTINCT A.trans_object_id, A.trans_detail_object_id, A.txn_date, B.terr_id, B.absolute_rank, B.top_level_terr_id, B.num_winners'
    ,p_real_time_from             => null
    ,p_real_time_where            =>
    'WHERE '  || CHR(10) ||
    '  B.q9600_low_value_CHAR = A.Q9600_PARTY_TYPE ' || CHR(10) ||
    '  AND B.q9600_cp = ''='' ' || CHR(10) ||
    '  AND B.source_id = -1600 ' || CHR(10) ||
    '  AND A.txn_date between B.start_date and B.end_date'


    ,retcode                      => retcode
    ,errbuf                       => errbuf);

  dbms_output.put_line('Retcode : ' || retcode);
  dbms_output.put_line('Errbuf : ' || errbuf);

  ad_morg.replicate_seed_data(NULL, 'JTF', NULL);

  COMMIT;

exception
  when others then
    dbms_output.put_line('Retcode : ' || retcode);
    dbms_output.put_line('Errbuf : ' || errbuf);
    raise;
end;
/

  3. Run this script.

Script 1

DECLARE

  CURSOR csr_real_time_sql (
                         lp_source_id       NUMBER
                       , lp_trans_type_id   NUMBER
                       , lp_program_name    VARCHAR2
                       , lp_sql_type        VARCHAR2 ) IS
   SELECT real_time_sql meta_data_SQL
   FROM jty_trans_usg_pgm_sql
   WHERE source_id = lp_source_id
     AND trans_type_id = lp_trans_type_id
     AND program_name LIKE lp_program_name
     AND lp_sql_type = 'real_time_sql';

  CURSOR csr_batch_total_sql (
                         lp_source_id       NUMBER
                       , lp_trans_type_id   NUMBER
                       , lp_program_name    VARCHAR2
                       , lp_sql_type        VARCHAR2 ) IS
   SELECT batch_total_sql meta_data_SQL
   FROM jty_trans_usg_pgm_sql
   WHERE source_id = lp_source_id
     AND trans_type_id = lp_trans_type_id
     AND program_name LIKE lp_program_name
     AND lp_sql_type = 'batch_total_sql';

  CURSOR csr_batch_incr_sql (
                         lp_source_id       NUMBER
                       , lp_trans_type_id   NUMBER
                       , lp_program_name    VARCHAR2
                       , lp_sql_type        VARCHAR2 ) IS
   SELECT batch_incr_sql meta_data_SQL
   FROM jty_trans_usg_pgm_sql
   WHERE source_id = lp_source_id
     AND trans_type_id = lp_trans_type_id
     AND program_name LIKE lp_program_name
     AND lp_sql_type = 'batch_incr_sql';

  CURSOR csr_batch_dea_sql (
                         lp_source_id       NUMBER
                       , lp_trans_type_id   NUMBER
                       , lp_program_name    VARCHAR2
                       , lp_sql_type        VARCHAR2 ) IS
   SELECT batch_dea_sql meta_data_SQL
   FROM jty_trans_usg_pgm_sql
   WHERE source_id = lp_source_id
     AND trans_type_id = lp_trans_type_id
     AND program_name LIKE lp_program_name
     AND lp_sql_type = 'batch_dea_sql';


  CURSOR csr_incr_reassign_sql (
                         lp_source_id       NUMBER
                       , lp_trans_type_id   NUMBER
                       , lp_program_name    VARCHAR2
                       , lp_sql_type        VARCHAR2 ) IS
   SELECT incr_reassign_sql meta_data_SQL
   FROM jty_trans_usg_pgm_sql
   WHERE source_id = lp_source_id
     AND trans_type_id = lp_trans_type_id
     AND program_name LIKE lp_program_name
     AND lp_sql_type = 'incr_reassign_sql';


  CURSOR csr_related_trans_types (
                 lp_source_id      NUMBER
               , lp_trans_type_id  NUMBER ) IS
  SELECT qtd.qual_type_id
       --, qt.name
       , pgm.program_name
       --, pgm.version_name
  FROM jty_trans_usg_pgm_sql pgm
     , jtf_qual_type_denorm_v qtd
     , jtf_qual_types_all qt
     , jtf_qual_type_usgs_all qtu
     , jtf_sources_all u
  WHERE pgm.enabled_flag = 'Y'
    AND pgm.source_id = qtu.source_id
    AND pgm.trans_type_id = qtu.qual_type_id
    AND qtd.qual_type_id = qt.qual_type_id
    AND qt.qual_type_id = qtu.qual_type_id
    AND qtu.source_id = u.source_id
    AND u.source_id = lp_source_id
    AND qtd.related_id = lp_trans_type_id
  ORDER BY qtd.qual_type_id, pgm.program_name;

  v_clob           CLOB := NULL;
  v_clob_length    NUMBER := 0;

  v_line_start     NUMBER := 1;
  v_line_end       NUMBER := 0; -- End of the current line which will be read
  v_line_length    NUMBER := 0; -- Length of current line which will be read
  v_line           VARCHAR2(32767);
  v_last_line      VARCHAR2(1) := 'N';

  v_line_number    NUMBER := 0;
  p_line_number    NUMBER := 0;

  p_source_id      NUMBER := -1001;
  p_trans_type_id  NUMBER := -1002;
  p_version_name   VARCHAR2(60) := SYSDATE ||  ': CMA PARTY TYPE ADDED';
  v_source_id      NUMBER := NULL;
  v_trans_type_id  NUMBER := NULL;
  v_program_name   VARCHAR2(60) := NULL;
  v_sql_type       VARCHAR2(60) := NULL;

  v_sql_number     NUMBER := 1;

BEGIN

  dbms_output.put_line('DECLARE');
  dbms_output.put_line('');
  dbms_output.put_line('  l_real_time_sql varchar2(32000) := NULL; ');
  dbms_output.put_line('  l_batch_total_sql varchar2(32000) := NULL; ');
  dbms_output.put_line('  l_batch_incr_sql varchar2(32000) := NULL; ');
  dbms_output.put_line('  l_batch_dea_sql varchar2(32000) := NULL; ');
  dbms_output.put_line('  l_incr_reassign_sql varchar2(32000) := NULL; ');
  dbms_output.put_line('');
  dbms_output.put_line('  retcode varchar2(250); ');
  dbms_output.put_line('  errbuf  varchar2(1000); ');
  dbms_output.put_line('');
  dbms_output.put_line('BEGIN');
  dbms_output.put_line('');


  FOR my_csr IN csr_related_trans_types(p_source_id, p_trans_type_id) LOOP

        v_source_id := p_source_id;
        v_trans_type_id := my_csr.qual_type_id;
        v_program_name := my_csr.program_name;
        v_sql_number := 1;

        dbms_output.put_line('/* ' || v_program_name || ' */');

        WHILE (v_sql_number <= 5) LOOP

          v_line_start     := 1;
          v_line_end       := 0;
          v_line_length    := 0;
          v_clob           := NULL;
          v_line           := NULL;

           /* Get Real Time SQL */
           IF (v_sql_number = 1) THEN

             v_sql_type := 'real_time_sql';
             OPEN csr_real_time_sql(v_source_id, v_trans_type_id, v_program_name, v_sql_type);
             FETCH csr_real_time_sql INTO v_clob;
             CLOSE csr_real_time_sql;

           /* Get Batch Total Mode SQL */
           ELSIF (v_sql_number = 2) THEN

             v_sql_type := 'batch_total_sql';
             OPEN csr_batch_total_sql(v_source_id, v_trans_type_id, v_program_name, v_sql_type);
             FETCH csr_batch_total_sql INTO v_clob;
             CLOSE csr_batch_total_sql;

           /* Get Batch Incremental Mode SQL */
           ELSIF (v_sql_number = 3) THEN

             v_sql_type := 'batch_incr_sql';
             OPEN csr_batch_incr_sql(v_source_id, v_trans_type_id, v_program_name, v_sql_type);
             FETCH csr_batch_incr_sql INTO v_clob;
             CLOSE csr_batch_incr_sql;

            /* Get Batch Mode Date Effective SQL */
           ELSIF (v_sql_number = 4) THEN

             v_sql_type := 'batch_dea_sql';
             OPEN csr_batch_dea_sql(v_source_id, v_trans_type_id, v_program_name, v_sql_type);
             FETCH csr_batch_dea_sql INTO v_clob;
             CLOSE csr_batch_dea_sql;



            /* Get Batch Mode Date Effective SQL */
           ELSIF (v_sql_number = 5) THEN

             v_sql_type := 'incr_reassign_sql';
             OPEN csr_incr_reassign_sql(v_source_id, v_trans_type_id, v_program_name, v_sql_type);
             FETCH csr_incr_reassign_sql INTO v_clob;
             CLOSE csr_incr_reassign_sql;

           END IF;

           v_clob_length := DBMS_LOB.GETLENGTH(v_clob);

           dbms_output.put_line('');
           dbms_output.put_line('');
           dbms_output.put_line('l_' || v_sql_type || ' := ');
           --dbms_output.put_line('START: v_line_start='||TO_CHAR(v_line_start));
           --dbms_output.put_line('LENGTH: v_clob_length='||TO_CHAR(v_clob_length));

           IF (v_clob_length = 0) THEN
             v_line := 'NULL;';
           END IF;

           dbms_output.put_line(v_line);

          /* Write the CLOB to file in chunks, each chunk
          ** is delimited by the new-line character
          */
          WHILE (v_line_start <= v_clob_length) LOOP

            v_line_number := v_line_number + 1;

            IF (p_line_number > 0 AND
                v_line_number = p_line_number) THEN
               EXIT;
            END IF;

            /* find position of next new-line character */
            v_line_end := DBMS_LOB.INSTR(v_clob, CHR(10), v_line_start, 1);
            --dbms_output.put_line('v_line_end='||TO_CHAR(v_line_end));

            IF (v_line_end = 0) THEN
              /* Last new-line reached so remaining part of CLOB to be
              ** output is from the current line start point to the
              ** end of the CLOB.
              */
              --dbms_output.put_line('Last new line reached at ' || TO_CHAR(v_line_start));
              v_line_end := v_clob_length + 1;
              --dbms_output.put_line('v_line_end='||TO_CHAR(v_line_end));

              v_last_line := 'Y';

            END IF;

            /* +1 as we want to include new-line
            ** character in the output
            */
            v_line_length := v_line_end - v_line_start + 1;
            --dbms_output.put_line('LENGTH: v_line_length='||TO_CHAR(v_line_length));

            v_line := TO_CHAR( DBMS_LOB.SUBSTR(v_clob, v_line_length-1, v_line_start));

            --v_line := REPLACE(v_line, ''', ''''') ;

            v_line := REPLACE(v_line, '''', '''''');

            IF (v_last_line = 'N') THEN
              dbms_output.put_line('''' || v_line || ' '' ');
            ELSE
              dbms_output.put_line('''' || v_line || ' '';');
            END IF;

            v_line_start := v_line_start + v_line_length;

            v_last_line := 'N';

          END LOOP;

          v_sql_number := v_sql_number + 1;

          dbms_output.put_line('');
          dbms_output.put_line('');
          --dbms_output.put_line('Number of lines = '||TO_CHAR(v_line_number));
          --dbms_output.put_line('VERY END: v_clob_position='||TO_CHAR(v_line_start));


        END LOOP; /* WHILE (v_sql_number <= 5) LOOP */

        dbms_output.put_line('');
        dbms_output.put_line('');
        dbms_output.put_line('  JTY_TRANS_USG_PGM_SQL_PKG.Insert_Row( ');
        dbms_output.put_line('   p_source_id => ' || p_source_id );
        dbms_output.put_line('  ,p_trans_type_id => ' || my_csr.qual_type_id );
        dbms_output.put_line('  ,p_program_name => ''' || my_csr.program_name || ''' ');
        dbms_output.put_line('  ,p_version_name => ''' || p_version_name || ''' ');
        dbms_output.put_line('  ,p_real_time_sql => l_real_time_sql ');
        dbms_output.put_line('  ,p_batch_total_sql => l_batch_total_sql ');
        dbms_output.put_line('  ,p_batch_incr_sql => l_batch_incr_sql ');
        dbms_output.put_line('  ,p_batch_dea_sql => l_batch_dea_sql ');
        dbms_output.put_line('  ,p_incr_reassign_sql => l_incr_reassign_sql ');
        dbms_output.put_line('  ,p_use_total_for_dea_flag => null ');
        dbms_output.put_line('  ,p_enabled_flag => ''Y'' ');
        dbms_output.put_line('  ,retcode => retcode ');
        dbms_output.put_line('  ,errbuf => errbuf); ');
        dbms_output.put_line('');
        dbms_output.put_line('');
        dbms_output.put_line('dbms_output.put_line(''' || my_csr.program_name || ' retcode : '' || retcode);');
        dbms_output.put_line('dbms_output.put_line(''' || my_csr.program_name || ' errbuf : '' || errbuf);');
        dbms_output.put_line('');

  END LOOP; /* OPEN csr_related_trans_types */

  dbms_output.put_line('');
  dbms_output.put_line('');
  dbms_output.put_line('COMMIT;');
  dbms_output.put_line('');
  dbms_output.put_line('END;');
  dbms_output.put_line('/');


END;

Contents   |   Previous   |   Top of Page   |   Next

Oracle Logo
Copyright © 2003, 2010, Oracle and/or its affiliates. All rights reserved.