Creating Custom Matching Attributes

This appendix covers the following topics:

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:

    • l_real_time_sql :=

       SELECT   
         party.party_id                               trans_object_id,   
         null                                 trans_detail_object_id,   
         TO_CHAR(NULL)                                                city,   
         TO_CHAR(NULL)                                                postal_code,   
         TO_CHAR(NULL)                                                state,   
         TO_CHAR(NULL)                                                province,   
         TO_CHAR(NULL)                                                county,   
         TO_CHAR(NULL)                                                country,   
         party.party_id                                                       party_id,   
         TO_NUMBER(NULL)                                              party_site_id,   
         upper(party.primary_phone_area_code)                 area_code,   
         upper(party.party_name)                                      comp_name_range,   
         party.party_id                                               partner_id,   
         party.employees_total                                        num_of_employees,   
         upper(party.category_code)                                   category_code,   
         party.party_id                                               party_relationship_id,   
         upper(party.sic_code_type||  :   ||party.sic_code)   sic_code,   
         orgp.CURR_FY_POTENTIAL_REVENUE                  squal_num06,   
         upper(orgp.PREF_FUNCTIONAL_CURRENCY)        car_currency_code,   
         upper(party.duns_number_c)                                   squal_char11,   
         l_txn_date                                                   txn_date   
      
      -- Party Type CMA
        , UPPER(party.party_type)                                     Q9010_PARTY_TYPE   
      --
      
       FROM   
         HZ_PARTIES party,   
         HZ_ORGANIZATION_PROFILES ORGP   
       WHERE party.party_type IN (''PERSON'',''ORGANIZATION'')    
       AND   party.status=''A''   
       AND   party.party_id = orgp.party_id(+)   
      AND   nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate   
      AND   party.party_id = l_trans_object_id1 UNION ALL   
      SELECT   
        party.party_id                                                trans_object_id,  
        addr.party_site_id                                            trans_detail_object_id, 
        upper(LOC.city)                                               city,  
        upper(LOC.postal_code)                                        postal_code,  
        upper(LOC.state)                                              state,  
        upper(LOC.province)                                           province,  
        upper(LOC.county)                                             county,  
        upper(LOC.country)                                            country,  
        party.party_id                                                party_id,  
        addr.party_site_id                                            party_site_id,  
        upper(phone.phone_area_code)                          area_code,  
        upper(party.party_name)                                       comp_name_range,  
        party.party_id                                                partner_id,  
        party.employees_total                                         num_of_employees,  
        upper(party.category_code)                                    category_code,  
        party.party_id                                                party_relationship_id,  
        upper(party.sic_code_type||'': ''||party.sic_code)    sic_code,  
        orgp.CURR_FY_POTENTIAL_REVENUE                  squal_num06,  
        upper(orgp.PREF_FUNCTIONAL_CURRENCY)        car_currency_code,  
        upper(party.duns_number_c)                                    squal_char11,  
        l_txn_date                                                    txn_date  
      
      -- Party Type CMA
       , UPPER(party.party_type)                                      Q9010_PARTY_TYPE  
      --
      
      FROM  
        HZ_CONTACT_POINTS phone,  
        HZ_PARTIES party,  
        HZ_PARTY_SITES addr,  
        HZ_LOCATIONS LOC, 
        HZ_ORGANIZATION_PROFILES ORGP  
      WHERE  phone.owner_table_name(+) = ''HZ_PARTY_SITES''  
      AND    phone.primary_flag(+) = ''Y''  
      AND    phone.status(+) = ''A''  
      AND    phone.contact_point_type (+) = ''PHONE''  
      AND    addr.party_site_id = phone.owner_table_id(+)  
      AND    addr.party_id=party.party_id  
      AND    party.party_id = orgp.party_id(+)  
      AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  
      AND    party.party_type IN (''PERSON'',''ORGANIZATION'')  
      AND    party.status=''A''  
      AND    LOC.location_id = addr.location_id  
      AND    addr.status=''A''  
      AND    party.party_id = l_trans_object_id1  
      AND    addr.party_site_id = nvl(l_trans_object_id2, addr.party_site_id) ;
      
    • l_batch_total_sql :=

      select       /*+ parallel(ORGPRO) parallel(CNTPNT) parallel(X) use_hash(ORGPRO CNTPNT X) */  
        -1001                                         source_id,  
        -1002                                 trans_object_type_id,  
        X.party_id                            trans_object_id,  
        X.party_site_id                                       trans_detail_object_id,  
        x.party_type                                                  party_type,  
        UPPER(X.party_name)                   squal_char01,  
        UPPER(X.city)                         squal_char02,  
        UPPER(X.county)                               squal_char03,  
        UPPER(X.state)                                squal_char04,  
        UPPER(X.province)                                     squal_char05,  
        UPPER(X.postal_code)                  squal_char06,  
        UPPER(X.country)                                      squal_char07,  
        UPPER(CNTPNT.phone_area_code)         squal_char08,  
        UPPER(X.category_code)                                squal_char09,  
        UPPER(X.sic_code_type||'': ''||X.sic_code)            squal_char10,  
        UPPER(X.duns_number_c)                        squal_char11, 
        UPPER(ORGPRO.pref_functional_currency)        squal_curc01,  
        UPPER(X.party_name_substring)         squal_fc01,  
        X.party_id                            squal_num01,  
        X.party_site_id                               squal_num02,  
        X.party_id                            squal_num03,  
        X.party_id                            squal_num04,  
        X.employees_total                                     squal_num05, 
        ORGPRO.curr_fy_potential_revenue              squal_num06  
      
      -- Party Type CMA
      , UPPER(X.party_type)                                           Q9010_PARTY_TYPE  
      --
      
      from HZ_ORGANIZATION_PROFILES   ORGPRO ,  
           HZ_CONTACT_POINTS CNTPNT,  
           (select /*+  parallel(PARTY) parallel(SITE) parallel(LOC) use_hash(SITE) use_hash(PARTY) use_hash(LOC) */  
              PARTY.party_type party_type,  
              SITE.party_site_id party_site_id,  
              Site.party_id party_id, 
              LOC.city city,  
              LOC.country country, 
              LOC.county county,  
              LOC.state state,  
              LOC.province province,  
              LOC.postal_code postal_code,  
              PARTY.employees_total employees_total,  
              PARTY.sic_code_type sic_code_type,  
              PARTY.sic_code sic_code,  
              upper(substr(PARTY.party_name,1,1)) party_name_substring,  
              upper(PARTY.party_name) party_name,  
              PARTY.category_code category_code,  
              ''HZ_PARTY_SITES'' owner_table_name,  
              SITE.party_site_id owner_table_id,  
              PARTY.duns_number_c  
      
      -- Party Type CMA
             , party.party_type party_type 
      --
      
            from  HZ_PARTY_SITES   SITE,  
                  HZ_LOCATIONS   LOC,  
                  HZ_PARTIES   PARTY  
            where  SITE.status = ''A''  
            and SITE.party_id = PARTY.party_id  
            and PARTY.party_type in (''PERSON'', ''ORGANIZATION'')  
            and PARTY.status = ''A''  
            and LOC.location_id = SITE.location_id  
            union all  
            select /*+ parallel(PARTY)  */  
              PARTY.party_type party_type,  
              to_number(NULL) party_site_id ,  
              PARTY.party_id party_id ,  
              to_char(NULL) city ,  
              to_char(NULL) country ,  
              to_char(NULL) county ,  
              to_char(NULL) state ,  
              to_char(NULL) province ,  
              to_char(NULL) postal_code ,  
              PARTY.employees_total employees_total,  
              PARTY.sic_code_type sic_code_type,  
              PARTY.sic_code sic_code,  
              upper(substr(PARTY.party_name,1,1)) party_name_substring,  
              upper(PARTY.party_name) party_name,  
              PARTY.category_code category_code,  
              ''HZ_PARTIES'' owner_table_name,  
              PARTY.party_id owner_table_id,  
              PARTY.duns_number_c  
      
      
      -- Party Type CMA
             , party.party_type party_type
      --
      
            from  HZ_PARTIES PARTY' 
            where PARTY.party_type in (''PERSON'', ''ORGANIZATION'')  
            and PARTY.status = ''A''  
            ) X  
      where CNTPNT.owner_table_name(+) = X.owner_table_name  
      and CNTPNT.owner_table_id(+) = X.owner_table_id  
      and CNTPNT.status(+)=''A''  
      and CNTPNT.primary_flag(+)=''Y''  
      and CNTPNT.contact_point_type(+)=''PHONE''  
      and ORGPRO.party_id(+) = X.party_id  
      and nvl(ORGPRO.effective_end_date(+),sysdate+1) > sysdate ;
      
    • l_batch_incr_sql :=

       select /*+ use_nl(ORGPRO CNTPNT) */  
        -1001 source_id, 
        -1002 trans_object_type_id, 
        X.party_id trans_object_id, 
        X.party_site_id trans_detail_object_id,  
        x.party_type party_type,  
        X.party_id squal_num01,  
        X.party_site_id squal_num02,  
        X.party_id squal_num03,  
        X.party_id squal_num04,  
        X.employees_total squal_num05,  
        ORGPRO.curr_fy_potential_revenue squal_num06,  
        UPPER(ORGPRO.pref_functional_currency) squal_curc01,  
        UPPER(X.party_name_substring) squal_fc01,  
        UPPER(X.party_name) squal_char01,  
        UPPER(X.city) squal_char02,  
        UPPER(X.county) squal_char03, 
        UPPER(X.state) squal_char04,  
        UPPER(X.province) squal_char05,  
        UPPER(X.postal_code) squal_char06,  
        UPPER(X.country) squal_char07,  
        UPPER(CNTPNT.phone_area_code) squal_char08,  
        UPPER(X.category_code) squal_char09,  
        UPPER(X.sic_code_type||'': ''||X.sic_code) squal_char10,  
        UPPER(X.duns_number_c) squal_char11  
      
      -- Party Type CMA
       , UPPER(X.party_type)                            Q9010_PARTY_TYPE 
      --
      
      from HZ_ORGANIZATION_PROFILES   ORGPRO , 
           HZ_CONTACT_POINTS CNTPNT, 
           (select   /*+ leading(y) */ 
              Z.party_site_id,        Z.party_id,          z.party_type, Z.city, 
              Z.country,              Z.county,            Z.state, 
              Z.province,             Z.postal_code,       Z.employees_total, 
              Z.sic_code_type,        Z.sic_code,          Z.party_name_substring, 
              Z.party_name,           Z.category_code,     Z.owner_table_name, 
              Z.owner_table_id,       Z.duns_number_c 
            from (select  /*+ no_merge */ distinct customer_id 
                  from AS_CHANGED_ACCOUNTS_ALL CHGACC 
                  where lead_id is null and sales_lead_id is null 
                          and CHGACC.request_id = l_REQUEST_ID) Y, 
                 (select SITE.party_site_id party_site_id, 
                         SITE.party_id party_id, 
                         PARTY.PARTY_TYPE party_type, 
                         LOC.city city, 
                         LOC.country country, 
                         LOC.county county, 
                         LOC.state state, 
                         LOC.province province, 
                         LOC.postal_code postal_code, 
                         PARTY.employees_total employees_total, 
                         PARTY.sic_code_type sic_code_type, 
                         PARTY.sic_code sic_code, 
                         upper(substr(PARTY.party_name,1,1)) party_name_substring, 
                         upper(PARTY.party_name) party_name, 
                         PARTY.category_code category_code,  
                         ''HZ_PARTY_SITES'' owner_table_name,  
                         SITE.party_site_id owner_table_id,  
                         PARTY.duns_number_c  
      
      -- Party Type CMA
             , party.party_type' 
      --
      
      
                  from  HZ_PARTY_SITES  SITE, 
                        HZ_LOCATIONS  LOC, 
                        HZ_PARTIES  PARTY 
                  where SITE.status = ''A'' 
                  and   SITE.party_id = PARTY.party_id 
                  and   PARTY.party_type in (''PERSON'', ''ORGANIZATION'') 
                  and   PARTY.status = ''A'' 
                  and   LOC.location_id = SITE.location_id           union all 
                  select to_number(NULL) party_site_id, 
                         PARTY.party_id party_id, 
                         PARTY.PARTY_TYPE party_type, 
                         to_char(NULL) city, 
                         to_char(NULL) country, 
                         to_char(NULL) county, 
                         to_char(NULL) state, 
                         to_char(NULL) province, 
                         to_char(NULL) postal_code , 
                         PARTY.employees_total employees_total, 
                         PARTY.sic_code_type sic_code_type, 
                         PARTY.sic_code sic_code, 
                         upper(substr(PARTY.party_name,1,1)) party_name_substring, 
                         upper(PARTY.party_name) party_name, 
                         PARTY.category_code category_code, 
                         ''HZ_PARTIES'' owner_table_name, 
                         PARTY.party_id owner_table_id,  
                         PARTY.duns_number_c  
      
      
      -- Party Type CMA
             , party.party_type 
      --
                  from  HZ_PARTIES PARTY 
                      where PARTY.party_type in (''PERSON'', ''ORGANIZATION'') 
                      and PARTY.status = ''A'') Z 
              where y.customer_id = z.party_id) X 
              where CNTPNT.owner_table_name(+) = X.owner_table_name 
              and   CNTPNT.owner_table_id(+) = X.owner_table_id 
              and   CNTPNT.status(+)=''A'' 
              and   CNTPNT.primary_flag(+)=''Y'' 
              and   CNTPNT.contact_point_type(+)=''PHONE'' 
              and   ORGPRO.party_id(+) = X.party_id 
              and   nvl(ORGPRO.effective_end_date(+),sysdate+1) > sysdate ;
      
    • l_batch_dea_sql := NO

    • l_incr_reassign_sql :=

      select /*+ use_nl(ORGPRO CNTPNT) */  
        -1001 source_id, 
        -1002 trans_object_type_id, 
        X.party_id trans_object_id, 
        X.party_site_id trans_detail_object_id, 
        x.party_type party_type, 
        X.party_id squal_num01, 
        X.party_site_id squal_num02, 
        X.party_id squal_num03, 
        X.party_id squal_num04, 
        X.employees_total squal_num05, 
        ORGPRO.curr_fy_potential_revenue squal_num06, 
        UPPER(ORGPRO.pref_functional_currency) squal_curc01, 
        UPPER(X.party_name_substring) squal_fc01, 
        UPPER(X.party_name) squal_char01, 
        UPPER(X.city) squal_char02, 
        UPPER(X.county) squal_char03, 
        UPPER(X.state) squal_char04, 
        UPPER(X.province) squal_char05, 
        UPPER(X.postal_code) squal_char06, 
        UPPER(X.country) squal_char07, 
        UPPER(CNTPNT.phone_area_code) squal_char08, 
        UPPER(X.category_code) squal_char09, 
        UPPER(X.sic_code_type||'': ''||X.sic_code) squal_char10,  
        UPPER(X.duns_number_c) squal_char11  
      
      -- Party Type CMA
       , UPPER(X.party_type)                            Q9010_PARTY_TYPE 
      --
      
      from HZ_ORGANIZATION_PROFILES   ORGPRO , 
           HZ_CONTACT_POINTS CNTPNT, 
           (select   /*+ leading(y) */ 
              Z.party_site_id,        Z.party_id,                     z.party_type, Z.city, 
              Z.country,              Z.county,                       Z.state, 
              Z.province,             Z.postal_code,                  Z.employees_total, 
              Z.sic_code_type,        Z.sic_code,                     Z.party_name_substring, 
              Z.party_name,           Z.category_code,        Z.owner_table_name, 
              Z.owner_table_id,       Z.duns_number_c 
            from ( select distinct ACC.customer_id customer_id 
                   from ( select distinct terr_id terr_id 
                          from jty_changed_terrs 
                          where tap_request_id = l_request_id )  CHG_TERR, 
                        AS_ACCESSES_ALL ACC, 
                        AS_TERRITORY_ACCESSES TERR_ACC 
                   where CHG_TERR.terr_id = TERR_ACC.territory_id 
                           and TERR_ACC.access_id = acc.access_id 
                           and acc.lead_id is null 
                           and acc.sales_lead_id is null ) Y, 
                 ( select SITE.party_site_id party_site_id, 
                          SITE.party_id party_id, 
                PARTY.PARTY_TYPE party_type, 
                          LOC.city city, 
                          LOC.country country, 
                          LOC.county county, 
                          LOC.state state, 
                          LOC.province province, 
                          LOC.postal_code postal_code, 
                          PARTY.employees_total employees_total, 
                          PARTY.sic_code_type sic_code_type, 
                          PARTY.sic_code sic_code,  
                          upper(substr(PARTY.party_name,1,1)) party_name_substring,  
                          upper(PARTY.party_name) party_name, 
                          PARTY.category_code category_code, 
                          ''HZ_PARTY_SITES'' owner_table_name, 
                          SITE.party_site_id owner_table_id, 
                          PARTY.duns_number_c 
      -- Party Type CMA
             , party.party_type
      --
      
                   from  HZ_PARTY_SITES  SITE, 
                         HZ_LOCATIONS  LOC, 
                         HZ_PARTIES  PARTY 
                   where SITE.status = ''A'' 
                   and SITE.party_id = PARTY.party_id 
                   and PARTY.party_type in (''PERSON'', ''ORGANIZATION'') 
                   and PARTY.status = ''A'' 
                   and LOC.location_id = SITE.location_id union all 
                   select to_number(NULL) party_site_id, 
                         PARTY.party_id party_id, 
              PARTY.PARTY_TYPE party_type, 
              to_char(NULL) city, 
                          to_char(NULL) country, 
                          to_char(NULL) county, 
                          to_char(NULL) state, 
                          to_char(NULL) province, 
                          to_char(NULL) postal_code , 
                          PARTY.employees_total employees_total, 
                          PARTY.sic_code_type sic_code_type, 
                          PARTY.sic_code sic_code, 
                          upper(substr(PARTY.party_name,1,1)) party_name_substring, 
                          upper(PARTY.party_name) party_name, 
                          PARTY.category_code category_code, 
                          ''HZ_PARTIES'' owner_table_name, 
                          PARTY.party_id owner_table_id, 
                          PARTY.duns_number_c 
      -- Party Type CMA
             , party.party_type
      --
                   from  HZ_PARTIES PARTY 
                   where PARTY.party_type in (''PERSON'', ''ORGANIZATION'') 
                   and PARTY.status = ''A'') Z 
            where y.customer_id = z.party_id) X 
      where CNTPNT.owner_table_name(+) = X.owner_table_name 
      and CNTPNT.owner_table_id(+) = X.owner_table_id 
      and CNTPNT.status(+)=''A'' 
      and CNTPNT.primary_flag(+)=''Y'' 
      and CNTPNT.contact_point_type(+)=''PHONE'' 
      and ORGPRO.party_id(+) = X.party_id 
      and nvl(ORGPRO.effective_end_date(+),sysdate+1) > sysdate;
      
      
      
      
        JTY_TRANS_USG_PGM_SQL_PKG.Insert_Row(
         p_source_id => -1001
        ,p_trans_type_id => -1002
        ,p_program_name => 'SALES/ACCOUNT PROGRAM'
        ,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);
      
      
      dbms_output.put_line('SALES/ACCOUNT PROGRAM retcode : ' || retcode);
      dbms_output.put_line('SALES/ACCOUNT PROGRAM errbuf : ' || errbuf);
      
      COMMIT;
      
      END;
      
  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);
          
          TYPE namesarray IS VARRAY(5) OF VARCHAR2(500);
          TYPE descarray IS VARRAY(5) OF VARCHAR2(500);
          TYPE langarray IS VARRAY(5) OF VARCHAR2(50);
          names namesarray;
          descript descarray;
          lang langarray;
          total INTEGER;
     BEGIN
        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;
          BEGIN
  2. Create the meta-data for the CMA. Follow the comments provided in the script to enter the relevant CMA information.

    /* Provide CMA details in at least one language, such as 'US'*/
    /* To create a CMA in multi-language, provide those details in 'names', 'descript' and 'lang' as an array*/
          
                names := namesarray('Party Type (CMA)');
                descript := descarray('Party Type (CMA)');
                lang := langarray('US');
                total := names.count;
    
                      FOR i in 1 .. total LOOP
    /* The following meta-data will create the custom matching attribute for the Party Type.*/
                              JTY_CUST_QUAL_PKG.Create_qual( 
                            p_seeded_qual_id     => -9010, 
    /* This number should correspond to the number used in the alias for the attribute*/
                    p_name            => names(i), 
                    p_description     => descript(i), 
                    p_language        => lang(i),
      
        ,/* 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);
    
    EXIT WHEN retcode=2;
    END LOOP;
    END;  
    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:

    • l_real_time_sql :=

       'SELECT  ' || CHR(10) ||                                                        
      '   LEAD.sales_lead_id trans_object_id  ' || CHR(10) ||                         
      '  ,to_number(NULL) trans_detail_object_id  ' || CHR(10) ||                     
      '  ,LEAD.sales_lead_id sales_lead_id  ' || CHR(10) ||                           
      '  ,to_number(NULL) sales_lead_line_id  ' || CHR(10) ||                         
      '  ,TO_CHAR(NULL) city  ' || CHR(10) ||                                         
      '  ,TO_CHAR(NULL) postal_code  ' || CHR(10) ||                                  
      '  ,TO_CHAR(NULL) state  ' || CHR(10) ||                                        
      '  ,TO_CHAR(NULL) province  ' || CHR(10) ||                                     
      '  ,TO_CHAR(NULL) county  ' || CHR(10) ||                                       
      '  ,TO_CHAR(NULL) country  ' || CHR(10) ||                                      
      '  ,PARTY.PARTY_ID party_id  ' || CHR(10) ||                                    
      '  ,to_number(null) party_site_id  ' || CHR(10) ||                              
      '  ,upper(PARTY.PRIMARY_PHONE_AREA_CODE) area_code  ' || CHR(10) ||             
      '  ,upper(PARTY.PARTY_NAME) comp_name_range  ' || CHR(10) ||                    
      '  ,PARTY.PARTY_ID partner_id  ' || CHR(10) ||                                  
      '  ,PARTY.EMPLOYEES_TOTAL num_of_employees  ' || CHR(10) ||                     
      '  ,upper(PARTY.CATEGORY_CODE) category_code  ' || CHR(10) ||                   
      '  ,PARTY.PARTY_ID party_relationship_id  ' || CHR(10) ||                       
      '  ,upper(party.sic_code_type||'': ''||party.sic_code) sic_code  ' || CHR(10) ||
      '  ,LEAD.budget_amount budget_amount  ' || CHR(10) ||                           
      '  ,upper(lead.currency_code) currency_code  ' || CHR(10) ||                    
      '  ,LEAD.source_promotion_id source_promotion_id  ' || CHR(10) ||               
      '  ,ORGP.CURR_FY_POTENTIAL_REVENUE squal_num06  ' || CHR(10) ||                 
      '  ,upper(ORGP.PREF_FUNCTIONAL_CURRENCY) car_currency_code  ' || CHR(10) ||     
      '  ,PARTY.PARTY_ID squal_num01  ' || CHR(10) ||                                 
      '  ,UPPER(PARTY.DUNS_NUMBER_C) SQUAL_CHAR11  ' || CHR(10) ||                    
      '  ,UPPER(LEAD.channel_code) SQUAL_CHAR30  ' || CHR(10) ||                      
      '  ,l_txn_date txn_date  ' || CHR(10) ||  
      ' , UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||                                      
      '  ,UPPER(LEAD.status_code) Q9100_LEAD_STATUS  ' || CHR(10) ||                  
      'FROM  ' || CHR(10) ||                                                          
      '  AS_SALES_LEADS LEAD,  ' || CHR(10) ||                                        
      '  HZ_PARTIES PARTY,  ' || CHR(10) ||                                           
      '  HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                              
      '  AS_STATUSES_B STATUS  ' || CHR(10) ||                                        
      'WHERE  LEAD.ADDRESS_ID IS NULL  ' || CHR(10) ||                                
      'AND    LEAD.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                      
      'AND    party.party_id = orgp.party_id(+)  ' || CHR(10) ||                      
      'AND    party.STATUS = ''A''  ' || CHR(10) ||                                   
      'AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||  
      'AND    LEAD.status_code = STATUS.status_code  ' || CHR(10) ||                  
      'AND    STATUS.lead_flag = ''Y''  ' || CHR(10) ||                               
      'AND    LEAD.sales_lead_id = l_trans_object_id1  ' || CHR(10) ||                
      'UNION ALL  ' || CHR(10) ||                                                     
      'SELECT  ' || CHR(10) ||                                                        
      '   LEAD.sales_lead_id trans_object_id  ' || CHR(10) ||                         
      '  ,SITE.PARTY_SITE_ID trans_detail_object_id  ' || CHR(10) ||                  
      '  ,LEAD.sales_lead_id sales_lead_id  ' || CHR(10) ||                           
      '  ,SITE.PARTY_SITE_ID sales_lead_line_id  ' || CHR(10) ||                      
      '  ,upper(LOC.CITY) city  ' || CHR(10) ||                                       
      '  ,upper(LOC.POSTAL_CODE) postal_code  ' || CHR(10) ||                         
      '  ,upper(LOC.STATE) state  ' || CHR(10) ||                                     
      '  ,upper(LOC.PROVINCE) province  ' || CHR(10) ||                               
      '  ,upper(LOC.COUNTY) county  ' || CHR(10) ||                                   
      '  ,upper(LOC.COUNTRY) country  ' || CHR(10) ||                                 
      '  ,PARTY.PARTY_ID party_id  ' || CHR(10) ||                                    
      '  ,SITE.PARTY_SITE_ID party_site_id  ' || CHR(10) ||                           
      '  ,upper(CNTPNT.PHONE_AREA_CODE) area_code  ' || CHR(10) ||                    
      '  ,upper(PARTY.PARTY_NAME) comp_name_range  ' || CHR(10) ||                    
      '  ,PARTY.PARTY_ID partner_id  ' || CHR(10) ||                                  
      '  ,PARTY.EMPLOYEES_TOTAL num_of_employees  ' || CHR(10) ||                     
      '  ,upper(PARTY.CATEGORY_CODE) category_code  ' || CHR(10) ||                   
      '  ,PARTY.PARTY_ID party_relationship_id  ' || CHR(10) ||                       
      '  ,upper(party.sic_code_type||'': ''||party.sic_code) sic_code  ' || CHR(10) ||
      '  ,LEAD.budget_amount budget_amount  ' || CHR(10) ||                           
      '  ,upper(lead.currency_code) currency_code  ' || CHR(10) ||                    
      '  ,LEAD.source_promotion_id source_promotion_id  ' || CHR(10) ||               
      '  ,ORGP.CURR_FY_POTENTIAL_REVENUE squal_num06  ' || CHR(10) ||                 
      '  ,upper(ORGP.PREF_FUNCTIONAL_CURRENCY) car_currency_code  ' || CHR(10) ||     
      '  ,PARTY.PARTY_ID squal_num01  ' || CHR(10) ||                                 
      '  ,UPPER(PARTY.DUNS_NUMBER_C) SQUAL_CHAR11  ' || CHR(10) ||                    
      '  ,UPPER(LEAD.channel_code) SQUAL_CHAR30  ' || CHR(10) ||                      
      '  ,l_txn_date txn_date  ' || CHR(10) ||                                        
      ' , UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(LEAD.status_code) Q9100_LEAD_STATUS  ' || CHR(10) ||                  
      'FROM  ' || CHR(10) ||                                                          
      '  AS_SALES_LEADS LEAD,  ' || CHR(10) ||                                        
      '  HZ_CONTACT_POINTS CNTPNT,  ' || CHR(10) ||                                   
      '  HZ_PARTY_SITES SITE,  ' || CHR(10) ||                                        
      '  HZ_LOCATIONS LOC,  ' || CHR(10) ||                                           
      '  HZ_PARTIES PARTY,  ' || CHR(10) ||                                           
      '  HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                              
      '  AS_STATUSES_B STATUS  ' || CHR(10) ||                                        
      'WHERE  CNTPNT.OWNER_TABLE_NAME(+) = ''HZ_PARTY_SITES''  ' || CHR(10) ||        
      'AND    SITE.PARTY_SITE_ID = CNTPNT.OWNER_TABLE_ID(+)  ' || CHR(10) ||          
      'AND    CNTPNT.PRIMARY_FLAG(+) = ''Y''  ' || CHR(10) ||                         
      'AND    CNTPNT.STATUS(+) <> ''I''  ' || CHR(10) ||                              
      'AND    CNTPNT.contact_point_type(+)=''PHONE''  ' || CHR(10) ||                 
      'AND    LEAD.ADDRESS_ID = SITE.PARTY_SITE_ID  ' || CHR(10) ||                   
      'AND    SITE.LOCATION_ID = LOC.LOCATION_ID  ' || CHR(10) ||                     
      'AND    SITE.STATUS = ''A''  ' || CHR(10) ||                                    
      'AND    LEAD.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                      
      'AND    party.party_id = orgp.party_id(+)  ' || CHR(10) ||                      
      'AND    party.STATUS = ''A''  ' || CHR(10) ||                                   
      'AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||  
      'AND    LEAD.status_code = STATUS.status_code  ' || CHR(10) ||                  
      'AND    STATUS.lead_flag = ''Y''  ' || CHR(10) ||                               
      'AND    LEAD.sales_lead_id = l_trans_object_id1  ';                             
      
      
    • l_batch_total_sql :=

      l_batch_total_sql :=                                                            
      'SELECT  ' || CHR(10) ||                                                        
      '  -1001                                                                        source_id,  ' || CHR(10) ||               
      '  -1003                                                        trans_object_type_id,  ' || CHR(10) || 
      '  LEAD.sales_lead_id                                               trans_object_id,  ' || CHR(10) ||         
      '  to_number(NULL)                                                  trans_detail_object_id,  ' || CHR(10)
      ||                                                                              
      '  STATUS.OPP_OPEN_STATUS_FLAG                       OPEN_FLAG,  ' || CHR(10) ||
      '  PARTY.PARTY_ID                                               squal_num01,  ' || CHR(10) ||          
      '  to_number(null)                                                      squal_num02,  ' || CHR(10) ||           
      '  PARTY.PARTY_ID                                                           squal_num03,  ' || CHR(10) ||               
      '  lead.sales_lead_id                                           squal_num04,  ' || CHR(10) ||             
      '  PARTY.EMPLOYEES_TOTAL                                                    squal_num05,  ' || CHR(10) ||          
      '  ORGP.CURR_FY_POTENTIAL_REVENUE                                   squal_num06,  ' || CHR(10) ||         
      '  LEAD.budget_amount                                           squal_num30,  ' || CHR(10) ||            
      '  LEAD.source_promotion_id                                         squal_num31,  ' || CHR(10) ||        
      '  upper(ORGP.PREF_FUNCTIONAL_CURRENCY)                             squal_curc01,  ' || CHR(10) ||   
      '  upper(lead.currency_code)                                    squal_curc07,  ' || CHR(10) ||         
      '  upper(substr(PARTY.party_name,1,1))                      squal_fc01,  ' || CHR(10) ||       
      '  upper(PARTY.PARTY_NAME)                                              squal_char01,  ' || CHR(10) ||           
      '  TO_CHAR(NULL)                                                                squal_char02,  ' || CHR(10) ||            
      '  TO_CHAR(NULL)                                                                squal_char03,  ' || CHR(10) ||            
      '  TO_CHAR(NULL)                                                                squal_char04,  ' || CHR(10) ||            
      '  TO_CHAR(NULL)                                                                squal_char05,  ' || CHR(10) ||            
      '  TO_CHAR(NULL)                                     squal_char06,  ' || CHR(10)
      ||                                                                              
      '  TO_CHAR(NULL)                                                                squal_char07,  ' || CHR(10) ||            
      '  upper(PARTY.PRIMARY_PHONE_AREA_CODE)                     squal_char08,  ' || CHR(10) ||    
      '  upper(PARTY.CATEGORY_CODE)                        squal_char09,  ' || CHR(10)
      ||                                                                              
      '  upper(party.sic_code_type||'': ''||party.sic_code)   squal_char10,  ' ||       
      CHR(10) ||                                                                      
      '  UPPER(PARTY.DUNS_NUMBER_C)                                   squal_char11,  ' || CHR(10) ||         
      '  UPPER(LEAD.channel_code)                                         squal_char30  ' || CHR(10) ||        
      '  ,UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(LEAD.status_code) Q9100_LEAD_STATUS  ' || CHR(10) ||                  
      'FROM  ' || CHR(10) ||                                                          
      '  AS_SALES_LEADS LEAD,  ' || CHR(10) ||                                        
      '  HZ_PARTIES PARTY,  ' || CHR(10) ||                                           
      '  HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                              
      '  AS_STATUSES_B STATUS  ' || CHR(10) ||                                        
      'WHERE  LEAD.ADDRESS_ID IS NULL  ' || CHR(10) ||                                
      'AND    LEAD.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                      
      'AND    party.party_id = orgp.party_id(+)  ' || CHR(10) ||                      
      'AND    party.STATUS = ''A''  ' || CHR(10) ||                                   
      'AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||  
      'AND    LEAD.status_code = STATUS.status_code  ' || CHR(10) ||                  
      'AND    STATUS.lead_flag = ''Y'' UNION ALL  ' || CHR(10) ||                     
      'SELECT  ' || CHR(10) ||                                                        
      '  -1001                                                                            source_id,  ' || CHR(10) ||                  
      '  -1003                                                                            trans_object_type_id,  ' || CHR(10) ||       
      '  LEAD.sales_lead_id                                trans_object_id,  ' ||     
      CHR(10) ||                                                                      
      '  SITE.PARTY_SITE_ID                                               trans_detail_object_id,  ' || CHR(10) ||  
      '  STATUS.OPP_OPEN_STATUS_FLAG                       OPEN_FLAG,  ' || CHR(10) ||
      '  PARTY.PARTY_ID                                                           squal_num01,  ' || CHR(10) ||                
      '  SITE.PARTY_SITE_ID                                squal_num02,  ' || CHR(10) 
      ||                                                                              
      '  PARTY.PARTY_ID                                                           squal_num03,  ' || CHR(10) ||                
      '  LEAD.SALES_LEAD_ID                                               squal_num04,  ' || CHR(10) ||             
      '  PARTY.EMPLOYEES_TOTAL                             squal_num05,  ' || CHR(10) 
      ||                                                                              
      '  ORGP.CURR_FY_POTENTIAL_REVENUE                                   squal_num06,  ' || CHR(10) ||          
      '  LEAD.budget_amount                                                   squal_num30,  ' || CHR(10) ||            
      '  LEAD.source_promotion_id                                         squal_num31,  ' || CHR(10) ||        
      '  upper(ORGP.PREF_FUNCTIONAL_CURRENCY)                     squal_curc01,  ' || CHR(10) ||    
      '  upper(lead.currency_code)                                    squal_curc07,  ' || CHR(10) ||         
      '  upper(substr(PARTY.party_name,1,1))                      squal_fc01,  ' || CHR(10) ||       
      '  upper(PARTY.PARTY_NAME)                                              squal_char01,  ' || CHR(10) ||           
      '  upper(LOC.CITY)                                                          squal_char02,  ' || CHR(10) ||              
      '  upper(LOC.COUNTY)                                                    squal_char03,  ' || CHR(10) ||         
      '  upper(LOC.STATE)                                                     squal_char04,  ' || CHR(10) ||          
      '  upper(LOC.PROVINCE)                                              squal_char05,  ' || CHR(10) ||           
      '  upper(LOC.POSTAL_CODE)                                               squal_char06,  ' || CHR(10) ||            
      '  upper(LOC.COUNTRY)                                               squal_char07,  ' || CHR(10) ||            
      '  upper(CNTPNT.PHONE_AREA_CODE)                                        squal_char08,  ' || CHR(10) ||      
      '  upper(PARTY.CATEGORY_CODE)                       squal_char09,  ' || CHR(10) ||
      '  upper(party.sic_code_type||'': ''||party.sic_code)   squal_char10,  ' ||       
      CHR(10) ||                                                                      
      '  UPPER(PARTY.DUNS_NUMBER_C)                                   squal_char11,  ' || CHR(10) ||         
      '  UPPER(LEAD.channel_code)                                         squal_char30  ' || CHR(10) ||        
      '  ,UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(LEAD.status_code) Q9100_LEAD_STATUS  ' || CHR(10) ||                  
      'FROM  ' || CHR(10) ||                                                          
      '  AS_SALES_LEADS LEAD,  ' || CHR(10) ||                                        
      '  HZ_CONTACT_POINTS CNTPNT,  ' || CHR(10) ||                                   
      '  HZ_PARTY_SITES SITE,  ' || CHR(10) ||                                        
      '  HZ_LOCATIONS LOC,  ' || CHR(10) ||                                           
      '  HZ_PARTIES PARTY,  ' || CHR(10) ||                                           
      '  HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                              
      '  AS_STATUSES_B STATUS  ' || CHR(10) ||                                        
      'WHERE  CNTPNT.OWNER_TABLE_NAME(+) = ''HZ_PARTY_SITES''  ' || CHR(10) ||        
      'AND    SITE.PARTY_SITE_ID = CNTPNT.OWNER_TABLE_ID(+)  ' || CHR(10) ||          
      'AND    CNTPNT.PRIMARY_FLAG(+) = ''Y''  ' || CHR(10) ||                         
      'AND    CNTPNT.STATUS(+) <> ''I''  ' || CHR(10) ||                              
      'AND    CNTPNT.contact_point_type(+)=''PHONE''  ' || CHR(10) ||                 
      'AND    LEAD.ADDRESS_ID = SITE.PARTY_SITE_ID  ' || CHR(10) ||                   
      'AND    SITE.LOCATION_ID = LOC.LOCATION_ID  ' || CHR(10) ||                     
      'AND    SITE.STATUS = ''A''  ' || CHR(10) ||                                    
      'AND    LEAD.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                      
      'AND    party.party_id = orgp.party_id(+)  ' || CHR(10) ||                      
      'AND    party.STATUS = ''A''  ' || CHR(10) ||                                   
      'AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||  
      'AND    LEAD.status_code = STATUS.status_code  ' || CHR(10) ||                  
      'AND    STATUS.lead_flag = ''Y''  ';                                            
      
      
    • l_batch_incr_sql :=

      l_batch_incr_sql :=                                                             
      'SELECT  ' || CHR(10) ||                                                        
      '  -1001                                                            source_id,  ' || CHR(10) ||                           
      '  -1003                                        trans_object_type_id,  ' || CHR(10) ||          
      '  LEAD.sales_lead_id                                   trans_object_id,  ' || CHR(10) ||                     
      '  to_number(NULL)                                      trans_detail_object_id,  ' || CHR(10) ||         
      '  STATUS.OPP_OPEN_STATUS_FLAG           OPEN_FLAG,  ' || CHR(10) ||            
      '  PARTY.PARTY_ID                               squal_num01,  ' || CHR(10) ||                   
      '  to_number(null)                                          squal_num02,  ' || CHR(10) ||                       
      '  PARTY.PARTY_ID                                               squal_num03,  ' || CHR(10) ||                           
      '  lead.sales_lead_id                           squal_num04,  ' || CHR(10) ||                      
      '  PARTY.EMPLOYEES_TOTAL                                        squal_num05,  ' || CHR(10) ||                      
      '  ORGP.CURR_FY_POTENTIAL_REVENUE               squal_num06,  ' || CHR(10) ||               
      '  LEAD.budget_amount                           squal_num30,  ' || CHR(10) ||                     
      '  LEAD.source_promotion_id                             squal_num31,  ' || CHR(10) ||                    
      '  upper(ORGP.PREF_FUNCTIONAL_CURRENCY)  squal_curc01,  ' || CHR(10) ||         
      '  upper(lead.currency_code)                squal_curc07,  ' || CHR(10) ||               
      '  upper(substr(PARTY.party_name,1,1))  squal_fc01,  ' || CHR(10) ||             
      '  upper(PARTY.PARTY_NAME)                              squal_char01,  ' || CHR(10) ||                    
      '  TO_CHAR(NULL)                                                    squal_char02,  ' || CHR(10) ||                        
      '  TO_CHAR(NULL)                                                    squal_char03,  ' || CHR(10) ||                        
      '  TO_CHAR(NULL)                                                    squal_char04,  ' || CHR(10) ||                        
      '  TO_CHAR(NULL)                                                    squal_char05,  ' || CHR(10) ||                        
      '  TO_CHAR(NULL)                         squal_char06,  ' || CHR(10) ||         
      '  TO_CHAR(NULL)                                                    squal_char07,  ' || CHR(10) ||                        
      '  upper(PARTY.PRIMARY_PHONE_AREA_CODE) squal_char08,  ' || CHR(10) ||          
      '  upper(PARTY.CATEGORY_CODE)            squal_char09,  ' || CHR(10) ||         
      '  upper(party.sic_code_type||'': ''||party.sic_code)   squal_char10,  ' ||       
      CHR(10) ||                                                                      
      '  UPPER(PARTY.DUNS_NUMBER_C)                   squal_char11,  ' || CHR(10) ||                  
      '  UPPER(LEAD.channel_code)                             squal_char30  ' || CHR(10) ||                    
      '  ,UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(LEAD.status_code) Q9100_LEAD_STATUS  ' || CHR(10) ||                  
      'FROM AS_SALES_LEADS LEAD,  ' || CHR(10) ||                                     
      '     HZ_PARTIES PARTY,  ' || CHR(10) ||                                        
      '     HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                           
      '        AS_STATUSES_B STATUS,  ' || CHR(10) ||                                       
      '        AS_CHANGED_ACCOUNTS_ALL ACHNG  ' || CHR(10) ||                               
      'WHERE  lead.address_id is null  ' || CHR(10) ||                                
      'AND    lead.customer_id = party.party_id  ' || CHR(10) ||                      
      'AND    party.party_id = orgp.party_id(+)  ' || CHR(10) ||                      
      'AND    party.STATUS = ''A''  ' || CHR(10) ||                                   
      'AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||  
      'AND    lead.status_code = status.status_code  ' || CHR(10) ||                  
      'AND    status.lead_flag = ''Y''  ' || CHR(10) ||                               
      'AND    achng.sales_lead_id = lead.sales_lead_id  ' || CHR(10) ||               
      'AND    achng.request_id = l_REQUEST_ID UNION ALL  ' || CHR(10) ||              
      'SELECT  ' || CHR(10) ||                                                        
      '  -1001                                                                source_id,  ' || CHR(10) ||                              
      '  -1003                                                                trans_object_type_id,  ' || CHR(10) ||                   
      '  LEAD.sales_lead_id                    trans_object_id,  ' || CHR(10) ||      
      '  SITE.PARTY_SITE_ID                                   trans_detail_object_id,  ' || CHR(10) ||              
      '  STATUS.OPP_OPEN_STATUS_FLAG           OPEN_FLAG,  ' || CHR(10) ||            
      '  PARTY.PARTY_ID                                               squal_num01,  ' || CHR(10) ||                            
      '  SITE.PARTY_SITE_ID                    squal_num02,  ' || CHR(10) ||          
      '  PARTY.PARTY_ID                                               squal_num03,  ' || CHR(10) ||                            
      '  LEAD.SALES_LEAD_ID                                   squal_num04,  ' || CHR(10) ||                         
      '  PARTY.EMPLOYEES_TOTAL                 squal_num05,  ' || CHR(10) ||          
      '  ORGP.CURR_FY_POTENTIAL_REVENUE               squal_num06,  ' || CHR(10) ||                
      '  LEAD.budget_amount                                   squal_num30,  ' || CHR(10) ||                     
      '  LEAD.source_promotion_id                             squal_num31,  ' || CHR(10) ||                    
      '  upper(ORGP.PREF_FUNCTIONAL_CURRENCY) squal_curc01,  ' || CHR(10) ||          
      '  upper(lead.currency_code)                    squal_curc07,  ' || CHR(10) ||                  
      '  upper(substr(PARTY.party_name,1,1))  squal_fc01,  ' || CHR(10) ||             
      '  upper(PARTY.PARTY_NAME)                              squal_char01,  ' || CHR(10) ||                    
      '  upper(LOC.CITY)                                              squal_char02,  ' || CHR(10) ||                          
      '  upper(LOC.COUNTY)                                        squal_char03,  ' || CHR(10) ||                     
      '  upper(LOC.STATE)                                         squal_char04,  ' || CHR(10) ||                      
      '  upper(LOC.PROVINCE)                                  squal_char05,  ' || CHR(10) ||                       
      '  upper(LOC.POSTAL_CODE)                               squal_char06,  ' || CHR(10) ||                     
      '  upper(LOC.COUNTRY)                                   squal_char07,  ' || CHR(10) ||                        
      '  upper(CNTPNT.PHONE_AREA_CODE)                        squal_char08,  ' || CHR(10) ||               
      '  upper(PARTY.CATEGORY_CODE)           squal_char09,  ' || CHR(10) ||            
      '  upper(party.sic_code_type||'': ''||party.sic_code)   squal_char10,  ' ||       
      CHR(10) ||                                                                      
      '  UPPER(PARTY.DUNS_NUMBER_C)                   squal_char11,  ' || CHR(10) ||                  
      '  UPPER(LEAD.channel_code)                             squal_char30  ' || CHR(10) ||                    
      '  ,UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(LEAD.status_code) Q9100_LEAD_STATUS  ' || CHR(10) ||                  
      'FROM  AS_SALES_LEADS LEAD,  ' || CHR(10) ||                                    
      '      HZ_CONTACT_POINTS CNTPNT,  ' || CHR(10) ||                               
      '      HZ_PARTY_SITES SITE,  ' || CHR(10) ||                                    
      '      HZ_LOCATIONS LOC,  ' || CHR(10) ||                                       
      '      HZ_PARTIES PARTY,  ' || CHR(10) ||                                       
      '      HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                          
      '         AS_STATUSES_B STATUS,  ' || CHR(10) ||                                      
      '         AS_CHANGED_ACCOUNTS_ALL ACHNG  ' || CHR(10) ||                           
      'WHERE  CNTPNT.OWNER_TABLE_NAME(+) = ''HZ_PARTY_SITES''  ' || CHR(10) ||        
      'AND    SITE.PARTY_SITE_ID = CNTPNT.OWNER_TABLE_ID(+)  ' || CHR(10) ||          
      'AND    CNTPNT.PRIMARY_FLAG(+) = ''Y''  ' || CHR(10) ||                         
      'AND    CNTPNT.STATUS(+) <> ''I''  ' || CHR(10) ||                              
      'AND    CNTPNT.contact_point_type(+)=''PHONE''  ' || CHR(10) ||                 
      'AND    LEAD.ADDRESS_ID = SITE.PARTY_SITE_ID  ' || CHR(10) ||                   
      'AND    SITE.LOCATION_ID = LOC.LOCATION_ID  ' || CHR(10) ||                     
      'AND    SITE.STATUS = ''A''  ' || CHR(10) ||                                    
      'AND    LEAD.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                      
      'AND    party.party_id = orgp.party_id(+)  ' || CHR(10) ||                      
      'AND    party.STATUS = ''A''  ' || CHR(10) ||                                   
      'AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||  
      'AND    LEAD.status_code = STATUS.status_code  ' || CHR(10) ||                  
      'AND    STATUS.lead_flag = ''Y''  ' || CHR(10) ||                               
      'AND    achng.sales_lead_id = lead.sales_lead_id  ' || CHR(10) ||               
      'AND    achng.request_id = l_REQUEST_ID  ';                                    
      
    • l_batch_dea_sql :=

      l_batch_dea_sql :=                                                              
      NULL;                                                                           
      l_incr_reassign_sql :=                                                          
      'SELECT  ' || CHR(10) ||                                                        
      '  -1001                                                                source_id,  ' || CHR(10) ||                       
      '  -1003                                                trans_object_type_id,  ' || CHR(10) ||         
      '  LEAD.sales_lead_id                                       trans_object_id,  ' || CHR(10) ||                 
      '  STATUS.OPP_OPEN_STATUS_FLAG               OPEN_FLAG,  ' || CHR(10) ||        
      '  to_number(NULL)                                          trans_detail_object_id,  ' || CHR(10) ||     
      '  PARTY.PARTY_ID                                       squal_num01,  ' || CHR(10) ||                  
      '  to_number(null)                                              squal_num02,  ' || CHR(10) ||                   
      '  PARTY.PARTY_ID                                                   squal_num03,  ' || CHR(10) ||                       
      '  lead.sales_lead_id                                   squal_num04,  ' || CHR(10) ||                     
      '  PARTY.EMPLOYEES_TOTAL                                            squal_num05,  ' || CHR(10) ||                  
      '  ORGP.CURR_FY_POTENTIAL_REVENUE                       squal_num06,  ' || CHR(10) ||              
      '  LEAD.budget_amount                                   squal_num30,  ' || CHR(10) ||                    
      '  LEAD.source_promotion_id                                 squal_num31,  ' || CHR(10) ||                
      '  upper(ORGP.PREF_FUNCTIONAL_CURRENCY)                 squal_curc01,  ' || CHR(10) ||        
      '  upper(lead.currency_code)                            squal_curc07,  ' || CHR(10) ||                 
      '  upper(substr(PARTY.party_name,1,1))          squal_fc01,  ' || CHR(10) ||            
      '  upper(PARTY.PARTY_NAME)                                      squal_char01,  ' || CHR(10) ||                   
      '  TO_CHAR(NULL)                                                        squal_char02,  ' || CHR(10) ||                    
      '  TO_CHAR(NULL)                                                        squal_char03,  ' || CHR(10) ||                    
      '  TO_CHAR(NULL)                                                        squal_char04,  ' || CHR(10) ||                    
      '  TO_CHAR(NULL)                                                        squal_char05,  ' || CHR(10) ||                    
      '  TO_CHAR(NULL)                             squal_char06,  ' || CHR(10) ||     
      '  TO_CHAR(NULL)                                                        squal_char07,  ' || CHR(10) ||                    
      '  upper(PARTY.PRIMARY_PHONE_AREA_CODE)         squal_char08,  ' || CHR(10) ||         
      '  upper(PARTY.CATEGORY_CODE)                squal_char09,  ' || CHR(10) ||     
      '  upper(party.sic_code_type||'': ''||party.sic_code)   squal_char10,  ' ||       
      CHR(10) ||                                                                      
      '  UPPER(PARTY.DUNS_NUMBER_C)                           squal_char11,  ' || CHR(10) ||                 
      '  UPPER(LEAD.channel_code)                                 squal_char30  ' || CHR(10) ||                
      '  ,UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(LEAD.status_code) Q9100_LEAD_STATUS  ' || CHR(10) ||                  
      'FROM   AS_SALES_LEADS LEAD,  ' || CHR(10) ||                                   
      '       HZ_PARTIES PARTY,  ' || CHR(10) ||                                      
      '       HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                         
      '          AS_STATUSES_B STATUS,  ' || CHR(10) ||                                     
      '          ( select distinct ACC.sales_lead_id sales_lead_id  ' || CHR(10) ||         
      '         from (select distinct terr_id terr_id  ' || CHR(10) ||                
      '               from JTY_CHANGED_TERRS  ' || CHR(10) ||                         
      '               where tap_request_id = l_request_id) CHG_TERR,  ' || CHR(10) || 
      '              AS_ACCESSES_ALL ACC,  ' || CHR(10) ||                            
      '              AS_TERRITORY_ACCESSES TERR_ACC  ' || CHR(10) ||                  
      '         where CHG_TERR.terr_id = TERR_ACC.territory_id  ' || CHR(10) ||       
      '         and TERR_ACC.access_id = acc.access_id  ' || CHR(10) ||               
      '         and acc.sales_lead_id is not null  ' || CHR(10) ||                    
      '         and acc.lead_id is null ) ACHNG  ' || CHR(10) ||                      
      'WHERE  lead.address_id is null  ' || CHR(10) ||                                
      'AND  lead.customer_id = party.party_id  ' || CHR(10) ||                        
      'AND  party.party_id = orgp.party_id(+)  ' || CHR(10) ||                        
      'AND  party.STATUS = ''A''  ' || CHR(10) ||                                     
      'AND  nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||    
      'AND  lead.status_code = status.status_code  ' || CHR(10) ||                    
      'AND status.lead_flag = ''Y''  ' || CHR(10) ||                                  
      'AND achng.sales_lead_id = lead.sales_lead_id UNION ALL  ' || CHR(10) ||        
      'SELECT  ' || CHR(10) ||                                                        
      '  -1001                                                                    source_id,  ' || CHR(10) ||                          
      '  -1003                                                                    trans_object_type_id,  ' || CHR(10) ||               
      '  LEAD.sales_lead_id                        trans_object_id,  ' || CHR(10) ||  
      '  STATUS.OPP_OPEN_STATUS_FLAG               OPEN_FLAG,  ' || CHR(10) ||        
      '  SITE.PARTY_SITE_ID                                       trans_detail_object_id,  ' || CHR(10) ||          
      '  PARTY.PARTY_ID                                                   squal_num01,  ' || CHR(10) ||                        
      '  SITE.PARTY_SITE_ID                        squal_num02,  ' || CHR(10) ||      
      '  PARTY.PARTY_ID                                                   squal_num03,  ' || CHR(10) ||                        
      '  LEAD.SALES_LEAD_ID                                       squal_num04,  ' || CHR(10) ||                     
      '  PARTY.EMPLOYEES_TOTAL                     squal_num05,  ' || CHR(10) ||      
      '  ORGP.CURR_FY_POTENTIAL_REVENUE                       squal_num06,  ' || CHR(10) ||               
      '  LEAD.budget_amount                                           squal_num30,  ' || CHR(10) ||                    
      '  LEAD.source_promotion_id                                 squal_num31,  ' || CHR(10) ||                
      '  upper(ORGP.PREF_FUNCTIONAL_CURRENCY)         squal_curc01,  ' || CHR(10) ||         
      '  upper(lead.currency_code)                            squal_curc07,  ' || CHR(10) ||                 
      '  upper(substr(PARTY.party_name,1,1))          squal_fc01,  ' || CHR(10) ||            
      '  upper(PARTY.PARTY_NAME)                                      squal_char01,  ' || CHR(10) ||                   
      '  upper(LOC.CITY)                                                  squal_char02,  ' || CHR(10) ||                      
      '  upper(LOC.COUNTY)                                            squal_char03,  ' || CHR(10) ||                 
      '  upper(LOC.STATE)                                             squal_char04,  ' || CHR(10) ||                  
      '  upper(LOC.PROVINCE)                                      squal_char05,  ' || CHR(10) ||                   
      '  upper(LOC.POSTAL_CODE)                                       squal_char06,  ' || CHR(10) ||                    
      '  upper(LOC.COUNTRY)                                       squal_char07,  ' || CHR(10) ||                    
      '  upper(CNTPNT.PHONE_AREA_CODE)                                squal_char08,  ' || CHR(10) ||              
      '  upper(PARTY.CATEGORY_CODE)               squal_char09,  ' || CHR(10) ||        
      '  upper(party.sic_code_type||'': ''||party.sic_code)   squal_char10,  ' ||       
      CHR(10) ||                                                                      
      '  UPPER(PARTY.DUNS_NUMBER_C)                           squal_char11,  ' || CHR(10) ||                 
      '  UPPER(LEAD.channel_code)                                 squal_char30  ' || CHR(10) ||                
      '  ,UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(LEAD.status_code) Q9100_LEAD_STATUS  ' || CHR(10) ||                  
      'FROM  AS_SALES_LEADS LEAD,  ' || CHR(10) ||                                    
      '      HZ_CONTACT_POINTS CNTPNT,  ' || CHR(10) ||                               
      '      HZ_PARTY_SITES SITE,  ' || CHR(10) ||                                    
      '      HZ_LOCATIONS LOC,  ' || CHR(10) ||                                       
      '      HZ_PARTIES PARTY,  ' || CHR(10) ||                                       
      '      HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                          
      '         AS_STATUSES_B STATUS,  ' || CHR(10) ||                                      
      '         ( select distinct  ACC.sales_lead_id sales_lead_id  ' || CHR(10) ||      
      '        from (select distinct terr_id terr_id  ' || CHR(10) ||                 
      '              from JTY_CHANGED_TERRS  ' || CHR(10) ||                          
      '              where tap_request_id = l_request_id) CHG_TERR,  ' || CHR(10) ||  
      '             AS_ACCESSES_ALL ACC,  ' || CHR(10) ||                             
      '             AS_TERRITORY_ACCESSES TERR_ACC  ' || CHR(10) ||                   
      '        where CHG_TERR.terr_id = TERR_ACC.territory_id  ' || CHR(10) ||        
      '        and TERR_ACC.access_id = acc.access_id  ' || CHR(10) ||                
      '        and acc.sales_lead_id is not null  ' || CHR(10) ||                     
      '        and acc.lead_id is null ) ACHNG  ' || CHR(10) ||                       
      'WHERE  CNTPNT.OWNER_TABLE_NAME(+) = ''HZ_PARTY_SITES''  ' || CHR(10) ||        
      'AND  SITE.PARTY_SITE_ID = CNTPNT.OWNER_TABLE_ID(+)  ' || CHR(10) ||            
      'AND  CNTPNT.PRIMARY_FLAG(+) = ''Y''  ' || CHR(10) ||                           
      'AND  CNTPNT.STATUS(+) <> ''I''  ' || CHR(10) ||                                
      'AND  CNTPNT.contact_point_type(+)=''PHONE''  ' || CHR(10) ||                   
      'AND  LEAD.ADDRESS_ID = SITE.PARTY_SITE_ID  ' || CHR(10) ||                     
      'AND  SITE.LOCATION_ID = LOC.LOCATION_ID  ' || CHR(10) ||                       
      'AND  SITE.STATUS = ''A''  ' || CHR(10) ||                                      
      'AND  LEAD.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                        
      'AND  party.party_id = orgp.party_id(+)  ' || CHR(10) ||                        
      'AND  party.STATUS = ''A''  ' || CHR(10) ||                                     
      'AND  nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||    
      'AND LEAD.status_code = STATUS.status_code  ' || CHR(10) ||                     
      'AND STATUS.lead_flag = ''Y''  ' || CHR(10) ||                                  
      'AND achng.sales_lead_id = lead.sales_lead_id  ';                               
      
    • l_incr_reassign_sql :=

  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);
          
          TYPE namesarray IS VARRAY(5) OF VARCHAR2(500);
          TYPE descarray IS VARRAY(5) OF VARCHAR2(500);
          TYPE langarray IS VARRAY(5) OF VARCHAR2(50);
          names namesarray;
          descript descarray;
          lang langarray;
          total INTEGER;
     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;
          BEGIN
  2. Create the meta-data for the CMA. Follow the comments provided in the script to enter the relevant CMA information.

    /* Provide CMA details in at least one language, such as 'US'*/
    /* To create a CMA in multi-language, provide those details in 'names', 'descript' and 'lang' as an array*/
          
                names := namesarray('Lead Status (CMA)');
                descript := descarray('Lead Status (CMA)');
                lang := langarray('US');
                total := names.count; 
    
         FOR i in 1 .. total LOOP
    /* The following meta-data will create the custom matching attribute for the Lead Status.*/
           JTY_CUST_QUAL_PKG.Create_qual( 
         p_seeded_qual_id  => -9100,
    /* This number should correspond to the number used in the alias for the attribute*/
         p_name            => names(i), 
         p_description     => descript(i), 
         p_language        => lang(i),
      
        ,/* 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);
    
    EXIT WHEN retcode=2;
    END LOOP;
    END;
    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 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:

    • l_real_time_sql :=

      'select  ' || CHR(10) ||                                                        
      '   header.quote_header_id  trans_object_id  ' || CHR(10) ||                    
      '  ,header.quote_header_id  trans_detail_object_id  ' || CHR(10) ||             
      '  ,upper(location.city) city  ' || CHR(10) ||                                  
      '  ,upper(location.postal_code) postal_code  ' || CHR(10) ||                    
      '  ,upper(location.state) state  ' || CHR(10) ||                                
      '  ,upper(location.province) province  ' || CHR(10) ||                          
      '  ,upper(location.county) county  ' || CHR(10) ||                              
      '  ,upper(location.country) country  ' || CHR(10) ||                            
      '  ,header.cust_party_id party_id  ' || CHR(10) ||                              
      '  ,header.sold_to_party_site_id party_site_id  ' || CHR(10) ||                 
      '  ,upper(cust_contact.phone_area_code) area_code  ' || CHR(10) ||              
      '  ,upper(party.party_name) comp_name_range  ' || CHR(10) ||                    
      '  ,header.cust_party_id partner_id  ' || CHR(10) ||                            
      '  ,party.employees_total num_of_employees  ' || CHR(10) ||                     
      '  ,upper(party.category_code) category_code  ' || CHR(10) ||                   
      '  ,header.cust_party_id party_relationship_id  ' || CHR(10) ||                 
      '  ,upper(party.sic_code_type||'': ''||party.sic_code) sic_code  ' || CHR(10) ||
      '  ,header.quote_header_id  squal_num50  ' || CHR(10) ||                        
      '  ,UPPER(party.duns_number_c)    squal_char11  ' || CHR(10) ||                 
      '  ,orgp.CURR_FY_POTENTIAL_REVENUE squal_num06  ' || CHR(10) ||                 
      '  ,upper(orgp.PREF_FUNCTIONAL_CURRENCY) car_currency_code  ' || CHR(10) ||     
      '  ,l_txn_date txn_date  ' || CHR(10) || 
      '  ,UPPER(party.party_type)                            Q9010_PARTY_TYPE  ' ||  
      '  ,UPPER(header.quote_source_code)  Q9101_QUOTE_SOURCE ' ||                                    
      'from  aso_quote_headers_all  header,  ' || CHR(10) ||                          
      '      hz_parties             party,  ' || CHR(10) ||                           
      '      hz_party_sites         party_site,  ' || CHR(10) ||                      
      '      HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                          
      '      hz_locations           location,  ' || CHR(10) ||                        
      '      hz_contact_points      cust_contact  ' || CHR(10) ||                     
      'where header.cust_party_id             = party.party_id  ' || CHR(10) ||       
      'and   header.sold_to_party_site_id     = party_site.party_site_id  ' || CHR(10)
      ||                                                                              
      'and   party_site.location_id           = location.location_id  ' || CHR(10) || 
      'AND   party.party_id                   = orgp.party_id(+)  ' || CHR(10) ||     
      'AND   nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||   
      'and   cust_contact.owner_table_id(+)   = header.party_id  ' || CHR(10) ||      
      'and   cust_contact.owner_table_name(+) = ''HZ_PARTIES''  ' || CHR(10) ||       
      'and   cust_contact.contact_point_type(+) = ''PHONE''  ' || CHR(10) ||          
      'and   cust_contact.status(+)           = ''A''  ' || CHR(10) ||                
      'and   cust_contact.primary_flag(+)     = ''Y''  ' || CHR(10) ||                
      'and   header.quote_header_id           = l_trans_object_id1  ';                
      
      
    • l_batch_total_sql :=

      l_batch_total_sql :=                                                            
      'select  ' || CHR(10) ||                                                        
      '  -1001                                             source_id,  ' || CHR(10) ||
      '  -1105                                             trans_object_type_id,  ' ||
      CHR(10) ||                                                                      
      '  header.QUOTE_NUMBER                               trans_object_id,  ' ||     
      CHR(10) ||                                                                      
      '  header.sold_to_party_site_id                      trans_detail_object_id,  ' 
      || CHR(10) ||                                                                   
      '  header.order_id                                   order_id,  ' || CHR(10) || 
      '  header.quote_expiration_date                      quote_expiration_date,  '  
      || CHR(10) ||                                                                   
      '  header.party_id                                          squal_num01,  ' || CHR(10) ||   
      '  header.sold_to_party_site_id                      squal_num02,  ' || CHR(10) 
      ||                                                                              
      '  header.party_id                                   squal_num03,  ' || CHR(10) 
      ||                                                                              
      '  header.party_id                                   squal_num04,  ' || CHR(10) 
      ||                                                                              
      '  party.employees_total                             squal_num05,  ' || CHR(10) 
      ||                                                                              
      '  orgp.CURR_FY_POTENTIAL_REVENUE                    squal_num06,  ' || CHR(10) 
      ||                                                                              
      '  header.quote_header_id                            squal_num50,  ' || CHR(10) 
      ||                                                                              
      '  upper(party.party_name)                           squal_char01,  ' || CHR(10)
      ||                                                                              
      '  upper(location.city)                              squal_char02,  ' || CHR(10)
      ||                                                                              
      '  upper(location.county)                            squal_char03,  ' || CHR(10)
      ||                                                                              
      '  upper(location.state)                             squal_char04,  ' || CHR(10)
      ||                                                                              
      '  upper(location.province)                          squal_char05,  ' || CHR(10)
      ||                                                                              
      '  upper(location.postal_code)                       squal_char06,  ' || CHR(10)
      ||                                                                              
      '  upper(location.country)                           squal_char07,  ' || CHR(10)
      ||                                                                              
      '  upper(cust_contact.phone_area_code)               squal_char08,  ' || CHR(10)
      ||                                                                              
      '  upper(party.category_code)                        squal_char09,  ' || CHR(10)
      ||                                                                              
      '  upper(party.sic_code_type||'': ''||party.sic_code)  squal_char10,  ' ||      
      CHR(10) ||                                                                      
      '  UPPER(party.duns_number_c)                               squal_char11,  ' || CHR(10) ||  
      '  upper(orgp.PREF_FUNCTIONAL_CURRENCY)              squal_curc01,  ' || CHR(10)
      ||                                                                              
      '  upper(substr(party.party_name,1,1))               squal_fc01  ' || CHR(10) ||
      '  ,UPPER(party.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(header.quote_source_code)  Q9101_QUOTE_SOURCE ' ||
      'from  aso_quote_headers_all  header,  ' || CHR(10) ||                          
      '      hz_parties         party,  ' || CHR(10) ||                               
      '      hz_party_sites     party_site,  ' || CHR(10) ||                          
      '      HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                          
      '      hz_locations       location,  ' || CHR(10) ||                            
      '      hz_contact_points  cust_contact  ' || CHR(10) ||                         
      'where header.cust_party_id             = party.party_id  ' || CHR(10) ||       
      'and   header.sold_to_party_site_id     = party_site.party_site_id  ' || CHR(10)
      ||                                                                              
      'and   party_site.location_id           = location.location_id  ' || CHR(10) || 
      'AND   party.party_id                   = orgp.party_id(+)  ' || CHR(10) ||     
      'AND   nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||   
      'and   cust_contact.owner_table_id(+)   = header.party_id  ' || CHR(10) ||      
      'and   cust_contact.owner_table_name(+) = ''HZ_PARTIES''  ' || CHR(10) ||       
      'and   contact_point_type(+)            = ''PHONE''  ' || CHR(10) ||            
      'and   cust_contact.status(+)           = ''A''  ' || CHR(10) ||                
      'and   primary_flag(+)                  = ''Y''  ' || CHR(10) ||                
      'and   header.max_version_flag          = ''Y''  ';             
                      
      l_batch_dea_sql :=                                                              
      NULL;                                                                           
      
    • l_batch_incr_sql :=

      l_batch_incr_sql :=                                                             
      'select  ' || CHR(10) ||                                                        
      '  -1001                                                                        source_id,  ' || CHR(10) ||                      
      '  -1105                                                trans_object_type_id,  ' || CHR(10) 
      ||                                                                              
      '  header.QUOTE_NUMBER                                          trans_object_id,  ' || CHR(10) ||            
      '  header.sold_to_party_site_id                         trans_detail_object_id,  ' ||      
      CHR(10) ||                                                                      
      '  header.order_id                                                  order_id,  ' || CHR(10) ||                   
      '  header.quote_expiration_date                             quote_expiration_date,  ' || CHR(10) ||  
      '  header.party_id                                      squal_num01,  ' || CHR(10) ||          
      '  header.sold_to_party_site_id                         squal_num02,  ' || CHR(10) ||      
      '  header.party_id                                      squal_num03,  ' || CHR(10) ||          
      '  header.party_id                                      squal_num04,  ' || CHR(10) ||          
      '  party.employees_total                                squal_num05,  ' || CHR(10) ||      
      '  orgp.CURR_FY_POTENTIAL_REVENUE                squal_num06,  ' || CHR(10) ||  
      '  header.quote_header_id                                   squal_num50,  ' || CHR(10) ||              
      '  upper(party.party_name)                                  squal_char01,  ' || CHR(10) ||           
      '  upper(location.city)                                 squal_char02,  ' || CHR(10) ||     
      '  upper(location.county)                               squal_char03,  ' || CHR(10) ||     
      '  upper(location.state)                                squal_char04,  ' || CHR(10) ||     
      '  upper(location.province)                             squal_char05,  ' || CHR(10) ||     
      '  upper(location.postal_code)                          squal_char06,  ' || CHR(10) ||     
      '  upper(location.country)                              squal_char07,  ' || CHR(10) ||     
      '  upper(cust_contact.phone_area_code)                  squal_char08,  ' || CHR(10) ||     
      '  upper(party.category_code)                           squal_char09,  ' || CHR(10) ||     
      '  upper(party.sic_code_type||'': ''||party.sic_code)   squal_char10,  ' ||      
      CHR(10) ||                                                                      
      '  UPPER(party.duns_number_c)                               squal_char11,  ' || CHR(10) ||         
      '  upper(orgp.PREF_FUNCTIONAL_CURRENCY)          squal_curc01,  ' || CHR(10) || 
      '  upper(substr(party.party_name,1,1))                  squal_fc01  ' || CHR(10) ||  
      '  ,UPPER(party.party_type)                            Q9010_PARTY_TYPE  ' ||          
      '  ,UPPER(header.quote_source_code)  Q9101_QUOTE_SOURCE ' ||
      'from  aso_quote_headers_all  header,  ' || CHR(10) ||                          
      '      hz_parties         party,  ' || CHR(10) ||                               
      '      hz_party_sites     party_site,  ' || CHR(10) ||                          
      '      HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                          
      '      hz_locations       location,  ' || CHR(10) ||                            
      '      hz_contact_points  cust_contact,  ' || CHR(10) ||                        
      '      aso_changed_quotes chng  ' || CHR(10) ||                                 
      'where header.party_id                  = party.party_id  ' || CHR(10) ||       
      'and   header.sold_to_party_site_id     = party_site.party_site_id  ' || CHR(10)
      ||                                                                              
      'and   party_site.location_id           = location.location_id  ' || CHR(10) || 
      'AND   party.party_id                   = orgp.party_id(+)  ' || CHR(10) ||     
      'AND   nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||   
      'and   cust_contact.owner_table_id(+)   = header.party_id  ' || CHR(10) ||      
      'and   cust_contact.owner_table_name(+) = ''HZ_PARTIES''  ' || CHR(10) ||       
      'and   contact_point_type(+)            = ''PHONE''  ' || CHR(10) ||            
      'and   cust_contact.status(+)           = ''A''  ' || CHR(10) ||                
      'and   primary_flag(+)                  = ''Y''  ' || CHR(10) ||                
      'and   header.max_version_flag          = ''Y''  ' || CHR(10) ||                
      'and   chng.quote_number                = header.quote_number  ' || CHR(10) ||  
      'and   chng.conc_request_id             = l_REQUEST_ID  ';   
                         
      
    • l_batch_dea_sql := NO

    • l_incr_reassign_sql :=

      l_incr_reassign_sql :=                                                          
      'select  ' || CHR(10) ||                                                        
      '  -1001                                                        source_id,  ' || CHR(10) ||                                      
      '  -1105                                                trans_object_type_id,  ' || CHR(10) 
      ||                                                                              
      '  header.QUOTE_NUMBER                                  trans_object_id,  ' || CHR(10) ||                    
      '  header.order_id                                      order_id,  ' || CHR(10) ||                               
      '  header.quote_expiration_date                         quote_expiration_date,  ' || CHR(10) ||      
      '  header.sold_to_party_site_id                         trans_detail_object_id,  ' ||      
      CHR(10) ||                                                                      
      '  header.party_id                                      squal_num01,  ' || CHR(10) ||          
      '  header.sold_to_party_site_id                         squal_num02,  ' || CHR(10) ||      
      '  header.party_id                                      squal_num03,  ' || CHR(10) ||          
      '  header.party_id                                      squal_num04,  ' || CHR(10) ||          
      '  party.employees_total                                squal_num05,  ' || CHR(10) ||      
      '  orgp.CURR_FY_POTENTIAL_REVENUE                squal_num06,  ' || CHR(10) ||  
      '  header.quote_header_id                                   squal_num50,  ' || CHR(10) ||              
      '  upper(party.party_name)                              squal_char01,  ' || CHR(10) ||        
      '  upper(location.city)                                 squal_char02,  ' || CHR(10) ||     
      '  upper(location.county)                               squal_char03,  ' || CHR(10) ||     
      '  upper(location.state)                                squal_char04,  ' || CHR(10) ||     
      '  upper(location.province)                             squal_char05,  ' || CHR(10) ||     
      '  upper(location.postal_code)                          squal_char06,  ' || CHR(10) ||     
      '  upper(location.country)                              squal_char07,  ' || CHR(10) ||     
      '  upper(cust_contact.phone_area_code)                  squal_char08,  ' || CHR(10) ||     
      '  upper(party.category_code)                           squal_char09,  ' || CHR(10) ||     
      '  upper(party.sic_code_type||'': ''||party.sic_code)   squal_char10,  ' ||      
      CHR(10) ||                                                                      
      '  UPPER(party.duns_number_c)                               squal_char11,  ' || CHR(10) ||         
      '  upper(orgp.PREF_FUNCTIONAL_CURRENCY)          squal_curc01,  ' || CHR(10) || 
      '  upper(substr(party.party_name,1,1))                  squal_fc01  ' || CHR(10) ||        
      '  ,UPPER(party.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(header.quote_source_code)  Q9101_QUOTE_SOURCE ' ||
      'from  aso_quote_headers  header,  ' || CHR(10) ||                              
      '      hz_parties         party,  ' || CHR(10) ||                               
      '      hz_party_sites     party_site,  ' || CHR(10) ||                          
      '      HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                          
      '      hz_locations       location,  ' || CHR(10) ||                            
      '      hz_contact_points  cust_contact,  ' || CHR(10) ||                        
      '      ( select distinct acc.quote_number quote_number  ' || CHR(10) ||         
      '        from (select distinct terr_id terr_id  ' || CHR(10) ||                 
      '              from JTY_CHANGED_TERRS  ' || CHR(10) ||                          
      '              where tap_request_id = l_request_id) CHG_TERR,  ' || CHR(10) ||  
      '             ASO_QUOTE_ACCESSES ACC,  ' || CHR(10) ||                          
      '             ASO_TERRITORY_ACCESSES  TERR_ACC  ' || CHR(10) ||                 
      '        where CHG_TERR.terr_id = TERR_ACC.territory_id  ' || CHR(10) ||        
      '        and TERR_ACC.access_id = acc.access_id) chng  ' || CHR(10) ||          
      'where header.party_id                  = party.party_id  ' || CHR(10) ||       
      'and   header.sold_to_party_site_id     = party_site.party_site_id  ' || CHR(10)
      ||                                                                              
      'and   party_site.location_id           = location.location_id  ' || CHR(10) || 
      'AND   party.party_id                   = orgp.party_id(+)  ' || CHR(10) ||     
      'AND   nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||   
      'and   cust_contact.owner_table_id(+)   = header.party_id  ' || CHR(10) ||      
      'and   cust_contact.owner_table_name(+) = ''HZ_PARTIES''  ' || CHR(10) ||       
      'and   contact_point_type(+)            = ''PHONE''  ' || CHR(10) ||            
      'and   cust_contact.status(+)           = ''A''  ' || CHR(10) ||                
      'and   primary_flag(+)                  = ''Y''  ' || CHR(10) ||                
      'and   header.max_version_flag          = ''Y''  ' || CHR(10) ||                
      'and   chng.quote_number                = header.quote_number  ';    
      
  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);
          
          TYPE namesarray IS VARRAY(5) OF VARCHAR2(500);
          TYPE descarray IS VARRAY(5) OF VARCHAR2(500);
          TYPE langarray IS VARRAY(5) OF VARCHAR2(50);
          names namesarray;
          descript descarray;
          lang langarray;
          total INTEGER;
     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;
          BEGIN
  2. Create the meta-data for the CMA. Follow the comments provided in the script to enter the relevant CMA information.

    /* Provide CMA details in at least one language, such as 'US'*/
    /* To create a CMA in multi-language, provide those details in 'names', 'descript' and 'lang' as an array*/
          
                names := namesarray('Quote Source (CMA)');
                descript := descarray('Custom Matching Attribute for Source Marketing Campaign of a Quote');
                lang := langarray('US');
                total := names.count;
    
         FOR i in 1 .. total LOOP
    /* The following meta-data will create the custom matching attribute for the Quote Source.*/
                       JTY_CUST_QUAL_PKG.Create_qual( 
         p_seeded_qual_id     => -9101,
    /* This number should correspond to the number used in the alias for the attribute*/
         p_name               => names(i), 
         p_description        => descript(i), 
         p_language           => lang(i),
        
    ,/* 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);
    
    EXIT WHEN retcode=2;
    END LOOP;
    END;
    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 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:

    • l_real_time_sql :=

      'Select  ' || CHR(10) ||                                                        
      '   prop.proposal_id TRANS_OBJECT_ID  ' || CHR(10) ||                           
      '  ,SITE.party_site_id trans_detail_object_id  ' || CHR(10) ||                  
      '  ,UPPER(LOC.CITY) city  ' || CHR(10) ||                                       
      '  ,UPPER(LOC.POSTAL_CODE) postal_code  ' || CHR(10) ||                         
      '  ,UPPER(LOC.STATE) state  ' || CHR(10) ||                                     
      '  ,UPPER(LOC.PROVINCE) province  ' || CHR(10) ||                               
      '  ,UPPER(LOC.COUNTY) county  ' || CHR(10) ||                                   
      '  ,UPPER(LOC.COUNTRY) country  ' || CHR(10) ||                                 
      '  ,hzp.party_id party_id  ' || CHR(10) ||                                      
      '  ,SITE.party_site_id party_site_id  ' || CHR(10) ||                           
      '  ,UPPER(CNTPNT.phone_area_code) area_code  ' || CHR(10) ||                    
      '  ,UPPER(hzp.party_name) comp_name_range  ' || CHR(10) ||                      
      '  ,hzp.party_id partner_id  ' || CHR(10) ||                                    
      '  ,hzp.employees_total num_of_employees  ' || CHR(10) ||                       
      '  ,UPPER(hzp.category_code) category_code  ' || CHR(10) ||                     
      '  ,hzp.party_id party_relationship_id  ' || CHR(10) ||                         
      '  ,UPPER(hzp.sic_code_type||'': ''||hzp.sic_code) sic_code  ' || CHR(10) ||    
      '  ,ORGPRO.curr_fy_potential_revenue squal_num06  ' || CHR(10) ||               
      '  ,UPPER(ORGPRO.pref_functional_currency) car_currency_code  ' || CHR(10) ||   
      '  ,hzp.party_id squal_num01  ' || CHR(10) ||                                   
      '  ,UPPER(hzp.duns_number_c) SQUAL_CHAR11  ' || CHR(10) ||                      
      '  ,l_txn_date txn_date  ' || CHR(10) ||                                        
      '  ,UPPER(hzp.party_type)                            Q9010_PARTY_TYPE  ' ||   CHR(10) ||                                      
      '  ,UPPER(PROP.proposal_status)  Q9102_PROPOSAL_STATUS'   ||   CHR(10) ||                            
      'FROM HZ_PARTY_SITES   SITE,  ' || CHR(10) ||                                   
      '    HZ_LOCATIONS   LOC,  ' || CHR(10) ||                                       
      '    HZ_PARTIES   HZP,  ' || CHR(10) ||                                         
      '    HZ_ORGANIZATION_PROFILES  ORGPRO ,  ' || CHR(10) ||                        
      '    HZ_CONTACT_POINTS CNTPNT,  ' || CHR(10) ||                                 
      '    prp_proposals   PROP  ' || CHR(10) ||                                      
      'WHERE CNTPNT.owner_table_id(+) = HZP.party_id  ' || CHR(10) ||                 
      'and CNTPNT.status(+)=''A''  ' || CHR(10) ||                                    
      'and CNTPNT.primary_flag(+)=''Y''  ' || CHR(10) ||                              
      'and CNTPNT.contact_point_type(+)=''PHONE''  ' || CHR(10) ||                    
      'and ORGPRO.party_id(+) = HZP.party_id  ' || CHR(10) ||                         
      'and nvl(ORGPRO.effective_end_date(+),sysdate+1) > sysdate  ' || CHR(10) ||     
      'AND PROP.PARTY_ID = HZP.PARTY_ID  ' || CHR(10) ||                              
      'AND HZP.party_type in (''PERSON'', ''ORGANIZATION'')  ' || CHR(10) ||          
      'AND HZP.status = ''A''  ' || CHR(10) ||                                        
      'AND  SITE.status = ''A''  ' || CHR(10) ||                                      
      'and SITE.party_id = hzp.party_id  ' || CHR(10) ||                              
      'and LOC.location_id = SITE.location_id  ' || CHR(10) ||                        
      'AND PROP.Proposal_id = l_trans_object_id1  ' || CHR(10) ||                     
      'UNION ALL  ' || CHR(10) ||                                                     
      'select  ' || CHR(10) ||                                                        
      '   prop.proposal_id TRANS_OBJECT_ID  ' || CHR(10) ||                           
      '  ,to_number(NULL) trans_detail_object_id  ' || CHR(10) ||                     
      '  ,To_char(null) city  ' || CHR(10) ||                                         
      '  ,To_char(null) postal_code  ' || CHR(10) ||                                  
      '  ,To_char(null) state  ' || CHR(10) ||                                        
      '  ,To_char(null) province  ' || CHR(10) ||                                     
      '  ,To_char(null) county  ' || CHR(10) ||                                       
      '  ,To_char(null) country  ' || CHR(10) ||                                      
      '  ,hzp.party_id party_id  ' || CHR(10) ||                                      
      '  ,to_number(NULL) party_site_id  ' || CHR(10) ||                              
      '  ,UPPER(CNTPNT.phone_area_code) area_code  ' || CHR(10) ||                    
      '  ,UPPER(hzp.party_name) comp_name_range  ' || CHR(10) ||                      
      '  ,hzp.party_id partner_id  ' || CHR(10) ||                                    
      '  ,hzp.employees_total num_of_employees  ' || CHR(10) ||                       
      '  ,UPPER(hzp.category_code) category_code  ' || CHR(10) ||                     
      '  ,hzp.party_id party_relationship_id  ' || CHR(10) ||                         
      '  ,UPPER(hzp.sic_code_type||'': ''||hzp.sic_code) sic_code  ' || CHR(10) ||    
      '  ,ORGPRO.curr_fy_potential_revenue squal_num06  ' || CHR(10) ||               
      '  ,UPPER(ORGPRO.pref_functional_currency) car_currency_code  ' || CHR(10) ||   
      '  ,hzp.party_id squal_num01  ' || CHR(10) ||                                   
      '  ,UPPER(hzp.duns_number_c) SQUAL_CHAR11  ' || CHR(10) ||                      
      '  ,l_txn_date txn_date  ' || CHR(10) ||                                        
      '  ,UPPER(hzp.party_type)                            Q9010_PARTY_TYPE  ' ||     
      '  ,UPPER(PROP.proposal_status)  Q9102_PROPOSAL_STATUS'   ||   CHR(10) ||
      CHR(10) ||                                                                      
      'from HZ_ORGANIZATION_PROFILES  ORGPRO ,  ' || CHR(10) ||                       
      '   HZ_CONTACT_POINTS CNTPNT,  ' || CHR(10) ||                                  
      '   prp_proposals   PROP,  ' || CHR(10) ||                                      
      '   hz_parties hzp  ' || CHR(10) ||                                             
      'where CNTPNT.owner_table_id(+) = HZP.party_id  ' || CHR(10) ||                 
      'and CNTPNT.status(+)=''A''  ' || CHR(10) ||                                    
      'and CNTPNT.primary_flag(+)=''Y''  ' || CHR(10) ||                              
      'and CNTPNT.contact_point_type(+)=''PHONE''  ' || CHR(10) ||                    
      'and ORGPRO.party_id(+) = HZP.party_id  ' || CHR(10) ||                         
      'and nvl(ORGPRO.effective_end_date(+),sysdate+1) > sysdate  ' || CHR(10) ||     
      'AND PROP.PARTY_ID = HZP.PARTY_ID  ' || CHR(10) ||                              
      'AND HZP.party_type in (''PERSON'', ''ORGANIZATION'')  ' || CHR(10) ||          
      'and HZP.status = ''A''  ' || CHR(10) ||                                        
      'AND PROP.Proposal_id = l_trans_object_id1  ';                                  
       
    • l_batch_total_sql :=

      l_batch_total_sql :=                                                            
      NULL;                                                                           
      l_batch_incr_sql :=                                                             
      NULL;                                                                           
      l_batch_dea_sql :=                                                              
      NULL;                                                                           
      l_incr_reassign_sql :=                                                          
      NULL;   
                                                                              
      
    • l_batch_incr_sql :=

    • l_batch_dea_sql := NO

    • l_incr_reassign_sql :=

  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);
          
          TYPE namesarray IS VARRAY(5) OF VARCHAR2(500);
          TYPE descarray IS VARRAY(5) OF VARCHAR2(500);
          TYPE langarray IS VARRAY(5) OF VARCHAR2(50);
          names namesarray;
          descript descarray;
          lang langarray;
          total INTEGER;
     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;
          BEGIN
  2. Create the meta-data for the CMA. Follow the comments provided in the script to enter the relevant CMA information.

    /* Provide CMA details in at least one language, such as 'US'*/
    /* To create a CMA in multi-language, provide those details in 'names', 'descript' and 'lang' as an array*/
          
                names := namesarray('Proposal Status (CMA)');
                descript := descarray('Custom Matching Attribute for Proposal Status');
                lang := langarray('US');
                total := names.count;
    
         FOR i in 1 .. total LOOP
            /* The following meta-data will create the custom matching attribute for the Proposal Status.*/ 
            JTY_CUST_QUAL_PKG.Create_qual( 
         p_seeded_qual_id     => -9102,
    /* This number should correspond to the number used in the alias for the attribute*/
         p_name               => names(i), 
         p_description        => descript(i), 
         p_language           => lang(i),
      
        ,/* 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);
    
    EXIT WHEN retcode=2;
    END LOOP;
    END;
    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 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:

    • l_real_time_sql :=

      'SELECT  ' || CHR(10) ||                                                        
      '  OPP.LEAD_ID                                      trans_object_id,  ' ||      
      CHR(10) ||                                                                      
      '  -1                                               trans_detail_object_id,  '  
      || CHR(10) ||                                                                   
      '  OPP.LEAD_ID                                      lead_id,  ' || CHR(10) ||   
      '  -1                                               lead_line_id,  ' || CHR(10) 
      ||                                                                              
      '  TO_CHAR(NULL)                                    city,  ' || CHR(10) ||      
      '  TO_CHAR(NULL)                                    postal_code,  ' || CHR(10)  
      ||                                                                              
      '  TO_CHAR(NULL)                                    state,  ' || CHR(10) ||     
      '  TO_CHAR(NULL)                                    province,  ' || CHR(10) ||  
      '  TO_CHAR(NULL)                                    county,  ' || CHR(10) ||    
      '  TO_CHAR(NULL)                                    country,  ' || CHR(10) ||   
      '  PARTY.PARTY_ID                                   party_id,  ' || CHR(10) ||  
      '  TO_NUMBER(NULL)                                  party_site_id,  ' || CHR(10)
      ||                                                                              
      '  upper(PARTY.PRIMARY_PHONE_AREA_CODE)             area_code,  ' || CHR(10) || 
      '  upper(PARTY.PARTY_NAME)                          comp_name_range,  ' ||      
      CHR(10) ||                                                                      
      '  PARTY.PARTY_ID                                   partner_id,  ' || CHR(10) ||
      '  PARTY.EMPLOYEES_TOTAL                            num_of_employees,  ' ||     
      CHR(10) ||                                                                      
      '  upper(PARTY.CATEGORY_CODE)                       category_code,  ' || CHR(10)
      ||                                                                              
      '  PARTY.PARTY_ID                                   party_relationship_id,  ' ||
      CHR(10) ||                                                                      
      '  upper(party.sic_code_type||'': ''||party.sic_code) sic_code,  ' || CHR(10) ||
      '  OPP.TOTAL_AMOUNT                                 total_amount,  ' || CHR(10) 
      ||                                                                              
      '  upper(OPP.CURRENCY_CODE)                         currency_code,  ' || CHR(10)
      ||                                                                              
      '  NVL(OPP.DECISION_DATE, OPP.LAST_UPDATE_DATE)     pricing_date,  ' || CHR(10) 
      ||                                                                              
      '  upper(OPP.CHANNEL_CODE)                          channel_code,  ' || CHR(10) 
      ||                                                                              
      '  OPP.SOURCE_PROMOTION_ID                          squal_num40,  ' || CHR(10)  
      ||                                                                              
      '  upper(OPP.STATUS)                                squal_char41,  ' || CHR(10) 
      ||                                                                              
      '  ORGP.CURR_FY_POTENTIAL_REVENUE                   squal_num06,  ' || CHR(10)  
      ||                                                                              
      '  upper(ORGP.PREF_FUNCTIONAL_CURRENCY)             car_currency_code,  ' ||    
      CHR(10) ||                                                                      
      '  UPPER(PARTY.DUNS_NUMBER_C)                       squal_char11,  ' || CHR(10) 
      ||                                                                              
      '  l_txn_date                                       txn_date  ' || CHR(10) ||   
      ' ,UPPER(PARTY.party_type)                          Q9010_PARTY_TYPE  ' ||
      ' ,UPPER(OPP.budget_status_code)                    Q9103_BUDGET_STATUS ' ||
      'FROM  ' || CHR(10) ||                                                          
      '  AS_LEADS OPP,  ' || CHR(10) ||                                               
      '  HZ_PARTIES PARTY,  ' || CHR(10) ||                                           
      '  HZ_ORGANIZATION_PROFILES ORGP  ' || CHR(10) ||                               
      'WHERE  OPP.ADDRESS_ID IS NULL  ' || CHR(10) ||                                 
      'AND    OPP.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                       
      'AND    party.party_id = orgp.party_id(+)  ' || CHR(10) ||                      
      'AND    party.STATUS = ''A''  ' || CHR(10) ||                                   
      'AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||  
      'AND    OPP.lead_id = l_trans_object_id1 UNION ALL SELECT  ' || CHR(10) ||      
      '  OPP.LEAD_ID                                      trans_object_id,  ' ||      
      CHR(10) ||                                                                      
      '  -1                                               trans_detail_object_id,  '  
      || CHR(10) ||                                                                   
      '  OPP.LEAD_ID                                      lead_id,  ' || CHR(10) ||   
      '  -1                                               lead_line_id,  ' || CHR(10) 
      ||                                                                              
      '  upper(LOC.CITY)                                  city,  ' || CHR(10) ||      
      '  upper(LOC.POSTAL_CODE)                           postal_code,  ' || CHR(10)  
      ||                                                                              
      '  upper(LOC.STATE)                                 state,  ' || CHR(10) ||     
      '  upper(LOC.PROVINCE)                              province,  ' || CHR(10) ||  
      '  upper(LOC.COUNTY)                                county,  ' || CHR(10) ||    
      '  upper(LOC.COUNTRY)                               country,  ' || CHR(10) ||   
      '  PARTY.PARTY_ID                                   party_id,  ' || CHR(10) ||  
      '  SITE.PARTY_SITE_ID                               party_site_id,  ' || CHR(10)
      ||                                                                              
      '  upper(PHONE.AREA_CODE)                           area_code,  ' || CHR(10) || 
      '  upper(PARTY.PARTY_NAME)                          comp_name_range,  ' ||      
      CHR(10) ||                                                                      
      '  PARTY.PARTY_ID                                   partner_id,  ' || CHR(10) ||
      '  PARTY.EMPLOYEES_TOTAL                            num_of_employees,  ' ||     
      CHR(10) ||                                                                      
      '  upper(PARTY.CATEGORY_CODE)                       category_code,  ' || CHR(10)
      ||                                                                              
      '  PARTY.PARTY_ID                                   party_relationship_id,  ' ||
      CHR(10) ||                                                                      
      '  upper(party.sic_code_type||'': ''||party.sic_code) sic_code,  ' || CHR(10) ||
      '  OPP.TOTAL_AMOUNT                                 total_amount,  ' || CHR(10) 
      ||                                                                              
      '  upper(OPP.CURRENCY_CODE)                         currency_code,  ' || CHR(10)
      ||                                                                              
      '  NVL(OPP.DECISION_DATE, OPP.LAST_UPDATE_DATE)     pricing_date,  ' || CHR(10) 
      ||                                                                              
      '  upper(OPP.CHANNEL_CODE)                          channel_code,  ' || CHR(10) 
      ||                                                                              
      '  OPP.SOURCE_PROMOTION_ID                          squal_num40,  ' || CHR(10)  
      ||                                                                              
      '  upper(OPP.STATUS)                                squal_char41,  ' || CHR(10) 
      ||                                                                              
      '  ORGP.CURR_FY_POTENTIAL_REVENUE                   squal_num06,  ' || CHR(10)  
      ||                                                                              
      '  upper(ORGP.PREF_FUNCTIONAL_CURRENCY)             car_currency_code,  ' ||    
      CHR(10) ||                                                                      
      '  UPPER(PARTY.DUNS_NUMBER_C)                       squal_char11,  ' || CHR(10) 
      ||                                                                              
      '  l_txn_date                                       txn_date  ' || CHR(10) || 
      '  ,UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||  
      '  ,UPPER(OPP.budget_status_code)                    Q9103_BUDGET_STATUS ' ||
      'FROM  ' || CHR(10) ||                                                          
      '  AS_LEADS_ALL OPP,  ' || CHR(10) ||                                           
      '  AS_PARTY_PHONES_V PHONE,  ' || CHR(10) ||                                    
      '  HZ_PARTY_SITES SITE,  ' || CHR(10) ||                                        
      '  HZ_LOCATIONS LOC,  ' || CHR(10) ||                                           
      '  HZ_PARTIES PARTY,  ' || CHR(10) ||                                           
      '  HZ_ORGANIZATION_PROFILES ORGP  ' || CHR(10) ||                               
      'WHERE  PHONE.OWNER_TABLE_NAME(+) = ''HZ_PARTY_SITES''  ' || CHR(10) ||         
      'AND    PHONE.PRIMARY_FLAG(+) = ''Y''  ' || CHR(10) ||                          
      'AND    PHONE.STATUS_CODE(+) <> ''I''  ' || CHR(10) ||                          
      'AND    SITE.PARTY_SITE_ID = PHONE.OWNER_TABLE_ID(+)  ' || CHR(10) ||           
      'AND    OPP.ADDRESS_ID = SITE.PARTY_SITE_ID  ' || CHR(10) ||                    
      'AND    SITE.LOCATION_ID = LOC.LOCATION_ID  ' || CHR(10) ||                     
      'AND    SITE.STATUS = ''A''  ' || CHR(10) ||                                    
      'AND    OPP.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                       
      'AND    party.party_id = orgp.party_id(+)  ' || CHR(10) ||                      
      'AND    party.STATUS = ''A''  ' || CHR(10) ||                                   
      'AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||  
      'AND    OPP.lead_id = l_trans_object_id1  ';                                    
      
                                                 
      
    • l_batch_total_sql :=

      l_batch_total_sql :=                                                            
      'SELECT  ' || CHR(10) ||                                                        
      '  -1001                                                                        source_id,  ' || CHR(10) ||               
      '  -1004                                                    trans_object_type_id,  ' || CHR(10) 
      ||                                                                              
      '  OPP.lead_id                                                      trans_object_id,  ' || CHR(10) ||        
      '  STATUS.OPP_OPEN_STATUS_FLAG                       OPEN_FLAG,  ' || CHR(10) ||
      '  to_number(null)                                              trans_detail_object_id,  ' ||     
      CHR(10) ||                                                                      
      '  PARTY.PARTY_ID                                           squal_num01,  ' || CHR(10) ||       
      '  to_number(null)                                                  squal_num02,  ' || CHR(10) ||        
      '  PARTY.PARTY_ID                                                       squal_num03,  ' || CHR(10) ||            
      '  opp.lead_id                                                  squal_num04,  ' || CHR(10) ||           
      '  PARTY.EMPLOYEES_TOTAL                                                squal_num05,  ' || CHR(10) ||       
      '  ORGP.CURR_FY_POTENTIAL_REVENUE                               squal_num06,  ' || CHR(10) ||      
      '  OPP.SOURCE_PROMOTION_ID                                      squal_num40,  ' || CHR(10) ||     
      '  OPP.TOTAL_AMOUNT                                                 squal_num41,  ' || CHR(10) ||        
      '  upper(ORGP.PREF_FUNCTIONAL_CURRENCY)                         squal_curc01,  ' || CHR(10) ||
      '  upper(OPP.CURRENCY_CODE)                                     squal_curc07,  ' || CHR(10) ||   
      '  upper(substr(PARTY.party_name,1,1))                  squal_fc01,  ' || CHR(10) ||    
      '  upper(PARTY.PARTY_NAME)                                          squal_char01,  ' || CHR(10) ||        
      '  TO_CHAR(NULL)                                                            squal_char02,  ' || CHR(10) ||         
      '  TO_CHAR(NULL)                                                            squal_char03,  ' || CHR(10) ||         
      '  TO_CHAR(NULL)                                                            squal_char04,  ' || CHR(10) ||         
      '  TO_CHAR(NULL)                                                            squal_char05,  ' || CHR(10) ||         
      '  TO_CHAR(NULL)                                            squal_char06,  ' || CHR(10)
      ||                                                                              
      '  TO_CHAR(NULL)                                                            squal_char07,  ' || CHR(10) ||         
      '  upper(PARTY.PRIMARY_PHONE_AREA_CODE)                 squal_char08,  ' || CHR(10) || 
      '  upper(PARTY.CATEGORY_CODE)                        squal_char09,  ' || CHR(10)
      ||                                                                              
      '  upper(party.sic_code_type||'': ''||party.sic_code)  squal_char10,  ' ||      
      CHR(10) ||                                                                      
      '  UPPER(PARTY.DUNS_NUMBER_C)                               squal_char11,  ' || CHR(10) ||      
      '  upper(OPP.CHANNEL_CODE)                                          squal_char40,  ' || CHR(10) ||        
      '  upper(OPP.STATUS)                                    squal_char41  ' || CHR(10) ||
      '  ,UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(OPP.budget_status_code)                    Q9103_BUDGET_STATUS ' ||
      'FROM   AS_LEADS OPP,  ' || CHR(10) ||                                          
      '       HZ_PARTIES PARTY,  ' || CHR(10) ||                                      
      '       HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                         
      '          AS_STATUSES_B STATUS  ' || CHR(10) ||                                      
      'WHERE  OPP.ADDRESS_ID IS NULL  ' || CHR(10) ||                                 
      'AND    OPP.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                       
      'AND    party.party_id = orgp.party_id(+)  ' || CHR(10) ||                      
      'AND    party.STATUS = ''A''  ' || CHR(10) ||                                   
      'AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||  
      'AND    OPP.status = STATUS.status_code  ' || CHR(10) ||                        
      'AND    STATUS.opp_flag = ''Y'' UNION ALL  ' || CHR(10) ||                      
      'SELECT  ' || CHR(10) ||                                                        
      '  -1001                                                                            source_id,  ' || CHR(10) ||                  
      '  -1004                                                                            trans_object_type_id,  ' || CHR(10) ||       
      '  OPP.LEAD_ID                                                                  trans_object_id,  ' || CHR(10) ||         
      '  STATUS.OPP_OPEN_STATUS_FLAG                       OPEN_FLAG,  ' || CHR(10) ||
      '  SITE.PARTY_SITE_ID                                               trans_detail_object_id,  ' || CHR(10) ||  
      '  PARTY.PARTY_ID                                                           squal_num01,  ' || CHR(10) ||                
      '  SITE.PARTY_SITE_ID                                squal_num02,  ' || CHR(10) 
      ||                                                                              
      '  PARTY.PARTY_ID                                                           squal_num03,  ' || CHR(10) ||                
      '  OPP.LEAD_ID                                                          squal_num04,  ' || CHR(10) ||               
      '  PARTY.EMPLOYEES_TOTAL                             squal_num05,  ' || CHR(10) 
      ||                                                                              
      '  ORGP.CURR_FY_POTENTIAL_REVENUE                                   squal_num06,  ' || CHR(10) ||          
      '  OPP.SOURCE_PROMOTION_ID                                              squal_num40,  ' || CHR(10) ||            
      '  OPP.TOTAL_AMOUNT                                  squal_num41,  ' || CHR(10) 
      ||                                                                              
      '  upper(ORGP.PREF_FUNCTIONAL_CURRENCY)                     squal_curc01,  ' || CHR(10) ||    
      '  upper(OPP.CURRENCY_CODE)                                         squal_curc07,  ' || CHR(10) ||       
      '  upper(substr(PARTY.party_name,1,1))                      squal_fc01,  ' || CHR(10) ||       
      '  upper(PARTY.PARTY_NAME)                                              squal_char01,  ' || CHR(10) ||           
      '  upper(LOC.CITY)                                                          squal_char02,  ' || CHR(10) ||              
      '  upper(LOC.COUNTY)                                                    squal_char03,  ' || CHR(10) ||         
      '  upper(LOC.STATE)                                                     squal_char04,  ' || CHR(10) ||          
      '  upper(LOC.PROVINCE)                                              squal_char05,  ' || CHR(10) ||           
      '  upper(LOC.POSTAL_CODE)                                               squal_char06,  ' || CHR(10) ||            
      '  upper(LOC.COUNTRY)                                               squal_char07,  ' || CHR(10) ||            
      '  upper(CNTPNT.PHONE_AREA_CODE)                                        squal_char08,  ' || CHR(10) ||      
      '  upper(PARTY.CATEGORY_CODE)                        squal_char09,  ' || CHR(10)
      ||                                                                              
      '  upper(party.sic_code_type||'': ''||party.sic_code)   squal_char10,  ' ||       
      CHR(10) ||                                                                      
      '  UPPER(PARTY.DUNS_NUMBER_C)                                   squal_char11,  ' || CHR(10) ||         
      '  upper(OPP.CHANNEL_CODE)                           squal_char40,  ' || CHR(10)
      ||                                                                              
      '  upper(OPP.STATUS)                                 squal_char41  ' || CHR(10) 
      ||                                                                              
      '  ,UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(OPP.budget_status_code)                    Q9103_BUDGET_STATUS ' ||
      'FROM   AS_LEADS_ALL OPP,  ' || CHR(10) ||                                      
      '       HZ_CONTACT_POINTS CNTPNT,  ' || CHR(10) ||                              
      '       HZ_PARTY_SITES SITE,  ' || CHR(10) ||                                   
      '       HZ_LOCATIONS LOC,  ' || CHR(10) ||                                      
      '       HZ_PARTIES PARTY,  ' || CHR(10) ||                                      
      '       HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                         
      '          AS_STATUSES_B STATUS  ' || CHR(10) ||                                      
      'WHERE  CNTPNT.OWNER_TABLE_NAME(+) = ''HZ_PARTY_SITES''  ' || CHR(10) ||        
      'AND    SITE.PARTY_SITE_ID = CNTPNT.OWNER_TABLE_ID(+)  ' || CHR(10) ||          
      'AND    CNTPNT.PRIMARY_FLAG(+) = ''Y''  ' || CHR(10) ||                         
      'AND    CNTPNT.STATUS(+) <> ''I''  ' || CHR(10) ||                              
      'AND    CNTPNT.contact_point_type(+)=''PHONE''  ' || CHR(10) ||                 
      'AND    OPP.ADDRESS_ID = SITE.PARTY_SITE_ID  ' || CHR(10) ||                    
      'AND    SITE.LOCATION_ID = LOC.LOCATION_ID  ' || CHR(10) ||                     
      'AND    SITE.STATUS = ''A''  ' || CHR(10) ||                                    
      'AND    OPP.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                       
      'AND    party.party_id = orgp.party_id(+)  ' || CHR(10) ||                      
      'AND    party.STATUS = ''A''  ' || CHR(10) ||                                   
      'AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||  
      'AND    OPP.status = STATUS.status_code  ' || CHR(10) ||                        
      'AND    STATUS.opp_flag = ''Y''  ';  
      
    • l_batch_incr_sql :=

      l_batch_incr_sql :=                                                             
      'SELECT  ' || CHR(10) ||                                                        
      '  -1001                                                        source_id,  ' || CHR(10) ||                               
      '  -1004                                trans_object_type_id,  ' || CHR(10) ||           
      '  OPP.lead_id                                      trans_object_id,  ' || CHR(10) ||                        
      '  to_number(NULL)                              trans_detail_object_id,  ' || CHR(10) ||          
      '  STATUS.OPP_OPEN_STATUS_FLAG       OPEN_FLAG,  ' || CHR(10) ||                
      '  PARTY.PARTY_ID                       squal_num01,  ' || CHR(10) ||                    
      '  to_number(null)                                  squal_num02,  ' || CHR(10) ||                        
      '  PARTY.PARTY_ID                                       squal_num03,  ' || CHR(10) ||                            
      '  opp.lead_id                                  squal_num04,  ' || CHR(10) ||                           
      '  PARTY.EMPLOYEES_TOTAL                                squal_num05,  ' || CHR(10) ||                       
      '  ORGP.CURR_FY_POTENTIAL_REVENUE       squal_num06,  ' || CHR(10) ||                 
      '  OPP.SOURCE_PROMOTION_ID                      squal_num40,  ' || CHR(10) ||                     
      '  OPP.TOTAL_AMOUNT                                 squal_num41,  ' || CHR(10) ||                        
      '  upper(ORGP.PREF_FUNCTIONAL_CURRENCY) squal_curc01,  ' || CHR(10) ||          
      '  upper(OPP.CURRENCY_CODE)                     squal_curc07,  ' || CHR(10) ||                   
      '  upper(substr(PARTY.party_name,1,1)) squal_fc01,  ' || CHR(10) ||             
      '  upper(PARTY.PARTY_NAME)                      squal_char01,  ' || CHR(10) ||                     
      '  TO_CHAR(NULL)                                            squal_char02,  ' || CHR(10) ||                         
      '  TO_CHAR(NULL)                                            squal_char03,  ' || CHR(10) ||                         
      '  TO_CHAR(NULL)                                            squal_char04,  ' || CHR(10) ||                         
      '  TO_CHAR(NULL)                                            squal_char05,  ' || CHR(10) ||                         
      '  TO_CHAR(NULL)                            squal_char06,  ' || CHR(10) ||             
      '  TO_CHAR(NULL)                                            squal_char07,  ' || CHR(10) ||                         
      '  upper(PARTY.PRIMARY_PHONE_AREA_CODE) squal_char08,  ' || CHR(10) ||          
      '  upper(PARTY.CATEGORY_CODE)        squal_char09,  ' || CHR(10) ||             
      '  upper(party.sic_code_type||'': ''||party.sic_code) squal_char10,  ' ||       
      CHR(10) ||                                                                      
      '  UPPER(PARTY.DUNS_NUMBER_C)           squal_char11,  ' || CHR(10) ||                   
      '  upper(OPP.CHANNEL_CODE)                      squal_char40,  ' || CHR(10) ||                     
      '  upper(OPP.STATUS)                 squal_char41  ' || CHR(10) ||              
      '  ,UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(OPP.budget_status_code)                    Q9103_BUDGET_STATUS ' ||
      'FROM   AS_LEADS OPP,  ' || CHR(10) ||                                          
      '       HZ_PARTIES PARTY,  ' || CHR(10) ||                                      
      '       HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                         
      '          AS_STATUSES_B STATUS,  ' || CHR(10) ||                                     
      '          AS_CHANGED_ACCOUNTS_ALL ACHNG  ' || CHR(10) ||                             
      'WHERE  OPP.ADDRESS_ID IS NULL  ' || CHR(10) ||                                 
      'AND    OPP.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                       
      'AND    party.party_id = orgp.party_id(+)  ' || CHR(10) ||                      
      'AND    party.STATUS = ''A''  ' || CHR(10) ||                                   
      'AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||  
      'AND    OPP.status = STATUS.status_code  ' || CHR(10) ||                        
      'AND    STATUS.opp_flag = ''Y''  ' || CHR(10) ||                                
      'AND    achng.lead_id = opp.lead_id  ' || CHR(10) ||                            
      'AND    achng.request_id = l_REQUEST_ID UNION ALL  ' || CHR(10) ||              
      'SELECT  ' || CHR(10) ||                                                        
      '-1001                                                          source_id,  ' || CHR(10) ||                                
      '-1004                                                          trans_object_type_id,  ' || CHR(10) ||                     
      'OPP.LEAD_ID                                                trans_object_id,  ' || CHR(10) ||                       
      'SITE.PARTY_SITE_ID                                         trans_detail_object_id,  ' || CHR(10) ||            
      'STATUS.OPP_OPEN_STATUS_FLAG             OPEN_FLAG,  ' || CHR(10) ||            
      'PARTY.PARTY_ID                                             squal_num01,  ' || CHR(10) ||                          
      'SITE.PARTY_SITE_ID                      squal_num02,  ' || CHR(10) ||          
      'PARTY.PARTY_ID                                             squal_num03,  ' || CHR(10) ||                          
      'OPP.LEAD_ID                                                    squal_num04,  ' || CHR(10) ||                         
      'PARTY.EMPLOYEES_TOTAL                   squal_num05,  ' || CHR(10) ||          
      'ORGP.CURR_FY_POTENTIAL_REVENUE                 squal_num06,  ' || CHR(10) ||                 
      'OPP.SOURCE_PROMOTION_ID                                        squal_num40,  ' || CHR(10) ||                      
      'OPP.TOTAL_AMOUNT                        squal_num41,  ' || CHR(10) ||          
      'upper(ORGP.PREF_FUNCTIONAL_CURRENCY)   squal_curc01,  ' || CHR(10) ||            
      'upper(OPP.CURRENCY_CODE)                               squal_curc07,  ' || CHR(10) ||                     
      'upper(substr(PARTY.party_name,1,1))            squal_fc01,  ' || CHR(10) ||              
      'upper(PARTY.PARTY_NAME)                                        squal_char01,  ' || CHR(10) ||                     
      'upper(LOC.CITY)                                                    squal_char02,  ' || CHR(10) ||                        
      'upper(LOC.COUNTY)                                          squal_char03,  ' || CHR(10) ||                       
      'upper(LOC.STATE)                                           squal_char04,  ' || CHR(10) ||                        
      'upper(LOC.PROVINCE)                                        squal_char05,  ' || CHR(10) ||                     
      'upper(LOC.POSTAL_CODE)                                 squal_char06,  ' || CHR(10) ||                      
      'upper(LOC.COUNTRY)                                         squal_char07,  ' || CHR(10) ||                      
      'upper(CNTPNT.PHONE_AREA_CODE)                  squal_char08,  ' || CHR(10) ||                 
      'upper(PARTY.CATEGORY_CODE)              squal_char09,  ' || CHR(10) ||         
      'upper(party.sic_code_type||'': ''||party.sic_code)     squal_char10,  ' || CHR(10) 
      ||                                                                              
      'UPPER(PARTY.DUNS_NUMBER_C)                             squal_char11,  ' || CHR(10) ||                   
      'upper(OPP.CHANNEL_CODE)                 squal_char40,  ' || CHR(10) ||         
      'upper(OPP.STATUS)                       squal_char41  ' || CHR(10) ||          
      '  ,UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(OPP.budget_status_code)                    Q9103_BUDGET_STATUS ' ||
      'FROM   AS_LEADS_ALL OPP,  ' || CHR(10) ||                                      
      '       HZ_CONTACT_POINTS CNTPNT,  ' || CHR(10) ||                              
      '       HZ_PARTY_SITES SITE,  ' || CHR(10) ||                                   
      '       HZ_LOCATIONS LOC,  ' || CHR(10) ||                                      
      '       HZ_PARTIES PARTY,  ' || CHR(10) ||                                      
      '       HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                         
      '          AS_STATUSES_B STATUS,  ' || CHR(10) ||                                     
      '          AS_CHANGED_ACCOUNTS_ALL ACHNG  ' || CHR(10) ||                             
      'WHERE  CNTPNT.OWNER_TABLE_NAME(+) = ''HZ_PARTY_SITES''  ' || CHR(10) ||        
      'AND    SITE.PARTY_SITE_ID = CNTPNT.OWNER_TABLE_ID(+)  ' || CHR(10) ||          
      'AND    CNTPNT.PRIMARY_FLAG(+) = ''Y''  ' || CHR(10) ||                         
      'AND    CNTPNT.STATUS(+) <> ''I''  ' || CHR(10) ||                              
      'AND    CNTPNT.contact_point_type(+)=''PHONE''  ' || CHR(10) ||                 
      'AND    OPP.ADDRESS_ID = SITE.PARTY_SITE_ID  ' || CHR(10) ||                    
      'AND    SITE.LOCATION_ID = LOC.LOCATION_ID  ' || CHR(10) ||                     
      'AND    SITE.STATUS = ''A''  ' || CHR(10) ||                                    
      'AND    OPP.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                       
      'AND    party.party_id = orgp.party_id(+)  ' || CHR(10) ||                      
      'AND    party.STATUS = ''A''  ' || CHR(10) ||                                   
      'AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||  
      'AND    OPP.status = STATUS.status_code  ' || CHR(10) ||                        
      'AND    STATUS.opp_flag = ''Y''  ' || CHR(10) ||                                
      'AND    achng.lead_id = opp.lead_id  ' || CHR(10) ||                            
      'AND    achng.request_id = l_REQUEST_ID  ';                                     
      
      l_batch_dea_sql :=                                                              
      NULL;                                                                           
      
    • l_batch_dea_sql := NO

    • l_incr_reassign_sql :=

      l_incr_reassign_sql :=                                                          
      'SELECT  ' || CHR(10) ||                                                        
      '  -1001                                                            source_id,  ' || CHR(10) ||                           
      '  -1004                                        trans_object_type_id,  ' || CHR(10) ||          
      '  OPP.lead_id                                          trans_object_id,  ' || CHR(10) ||                    
      '  STATUS.OPP_OPEN_STATUS_FLAG           OPEN_FLAG,  ' || CHR(10) ||            
      '  to_number(NULL)                                  trans_detail_object_id,  ' || CHR(10) ||      
      '  PARTY.PARTY_ID                               squal_num01,  ' || CHR(10) ||                   
      '  to_number(null)                                      squal_num02,  ' || CHR(10) ||                    
      '  PARTY.PARTY_ID                                           squal_num03,  ' || CHR(10) ||                        
      '  opp.lead_id                                      squal_num04,  ' || CHR(10) ||                       
      '  PARTY.EMPLOYEES_TOTAL                                    squal_num05,  ' || CHR(10) ||                   
      '  ORGP.CURR_FY_POTENTIAL_REVENUE               squal_num06,  ' || CHR(10) ||               
      '  OPP.SOURCE_PROMOTION_ID                          squal_num40,  ' || CHR(10) ||                 
      '  OPP.TOTAL_AMOUNT                                     squal_num41,  ' || CHR(10) ||                    
      '  upper(ORGP.PREF_FUNCTIONAL_CURRENCY)         squal_curc01,  ' || CHR(10) ||         
      '  upper(OPP.CURRENCY_CODE)                         squal_curc07,  ' || CHR(10) ||               
      '  upper(substr(PARTY.party_name,1,1))  squal_fc01,  ' || CHR(10) ||             
      '  upper(PARTY.PARTY_NAME)                              squal_char01,  ' || CHR(10) ||                    
      '  TO_CHAR(NULL)                                                squal_char02,  ' || CHR(10) ||                     
      '  TO_CHAR(NULL)                                                squal_char03,  ' || CHR(10) ||                     
      '  TO_CHAR(NULL)                                                squal_char04,  ' || CHR(10) ||                     
      '  TO_CHAR(NULL)                                                squal_char05,  ' || CHR(10) ||                     
      '  TO_CHAR(NULL)                                squal_char06,  ' || CHR(10) ||         
      '  TO_CHAR(NULL)                                                squal_char07,  ' || CHR(10) ||                     
      '  upper(PARTY.PRIMARY_PHONE_AREA_CODE) squal_char08,  ' || CHR(10) ||          
      '  upper(PARTY.CATEGORY_CODE)            squal_char09,  ' || CHR(10) ||         
      '  upper(party.sic_code_type||'': ''||party.sic_code) squal_char10,  ' ||       
      CHR(10) ||                                                                      
      '  UPPER(PARTY.DUNS_NUMBER_C)                   squal_char11,  ' || CHR(10) ||                  
      '  upper(OPP.CHANNEL_CODE)                              squal_char40,  ' || CHR(10) ||                    
      '  upper(OPP.STATUS)                     squal_char41  ' || CHR(10) ||          
      '  ,UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(OPP.budget_status_code)                    Q9103_BUDGET_STATUS ' ||
      'FROM   AS_LEADS OPP,  ' || CHR(10) ||                                          
      '       HZ_PARTIES PARTY,  ' || CHR(10) ||                                      
      '       HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                         
      '          AS_STATUSES_B STATUS,  ' || CHR(10) ||                                     
      '          ( select distinct ACC.lead_id lead_id  ' || CHR(10) ||                     
      '         from (select distinct terr_id terr_id  ' || CHR(10) ||                
      '               from JTY_CHANGED_TERRS  ' || CHR(10) ||                         
      '               where tap_request_id = l_request_id) CHG_TERR,  ' || CHR(10) || 
      '              AS_ACCESSES_ALL ACC,  ' || CHR(10) ||                            
      '              AS_TERRITORY_ACCESSES TERR_ACC  ' || CHR(10) ||                  
      '         where CHG_TERR.terr_id = TERR_ACC.territory_id  ' || CHR(10) ||       
      '         and TERR_ACC.access_id = acc.access_id  ' || CHR(10) ||               
      '         and acc.lead_id is not null  ' || CHR(10) ||                          
      '         and acc.sales_lead_id is null )  ACHNG  ' || CHR(10) ||               
      'WHERE  OPP.ADDRESS_ID IS NULL  ' || CHR(10) ||                                 
      'AND  OPP.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                         
      'AND  party.party_id = orgp.party_id(+)  ' || CHR(10) ||                        
      'AND  party.STATUS = ''A''  ' || CHR(10) ||                                     
      'AND  nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||    
      'AND OPP.status = STATUS.status_code  ' || CHR(10) ||                           
      'AND STATUS.opp_flag = ''Y''  ' || CHR(10) ||                                   
      'AND achng.lead_id = opp.lead_id UNION ALL  ' || CHR(10) ||                     
      'SELECT  ' || CHR(10) ||                                                        
      '  -1001                                                                        source_id,  ' || CHR(10) ||                      
      '  -1004                                                                        trans_object_type_id,  ' || CHR(10) ||           
      '  OPP.LEAD_ID                                                              trans_object_id,  ' || CHR(10) ||             
      '  STATUS.OPP_OPEN_STATUS_FLAG                   OPEN_FLAG,  ' || CHR(10) ||    
      '  SITE.PARTY_SITE_ID                                           trans_detail_object_id,  ' || CHR(10) ||      
      '  PARTY.PARTY_ID                                                       squal_num01,  ' || CHR(10) ||                    
      '  SITE.PARTY_SITE_ID                            squal_num02,  ' || CHR(10) ||  
      '  PARTY.PARTY_ID                                                       squal_num03,  ' || CHR(10) ||                    
      '  OPP.LEAD_ID                                                      squal_num04,  ' || CHR(10) ||                   
      '  PARTY.EMPLOYEES_TOTAL                         squal_num05,  ' || CHR(10) ||  
      '  ORGP.CURR_FY_POTENTIAL_REVENUE                               squal_num06,  ' || CHR(10) ||              
      '  OPP.SOURCE_PROMOTION_ID                                          squal_num40,  ' || CHR(10) ||                
      '  OPP.TOTAL_AMOUNT                              squal_num41,  ' || CHR(10) ||  
      '  upper(ORGP.PREF_FUNCTIONAL_CURRENCY)                 squal_curc01,  ' || CHR(10) ||        
      '  upper(OPP.CURRENCY_CODE)                                     squal_curc07,  ' || CHR(10) ||           
      '  upper(substr(PARTY.party_name,1,1))                  squal_fc01,  ' || CHR(10) ||           
      '  upper(PARTY.PARTY_NAME)                                          squal_char01,  ' || CHR(10) ||               
      '  upper(LOC.CITY)                                                      squal_char02,  ' || CHR(10) ||                  
      '  upper(LOC.COUNTY)                                                squal_char03,  ' || CHR(10) ||             
      '  upper(LOC.STATE)                                                 squal_char04,  ' || CHR(10) ||              
      '  upper(LOC.PROVINCE)                                          squal_char05,  ' || CHR(10) ||               
      '  upper(LOC.POSTAL_CODE)                                           squal_char06,  ' || CHR(10) ||                
      '  upper(LOC.COUNTRY)                                           squal_char07,  ' || CHR(10) ||                
      '  upper(CNTPNT.PHONE_AREA_CODE)                                    squal_char08,  ' || CHR(10) ||          
      '  upper(PARTY.CATEGORY_CODE)                    squal_char09,  ' || CHR(10) || 
      '  upper(party.sic_code_type||'': ''||party.sic_code)   squal_char10,  ' ||       
      CHR(10) ||                                                                      
      '  UPPER(PARTY.DUNS_NUMBER_C)                               squal_char11,  ' || CHR(10) ||             
      '  upper(OPP.CHANNEL_CODE)                       squal_char40,  ' || CHR(10) || 
      '  upper(OPP.STATUS)                             squal_char41  ' || CHR(10) ||  
      '  ,UPPER(PARTY.party_type)                            Q9010_PARTY_TYPE  ' ||
      '  ,UPPER(OPP.budget_status_code)                    Q9103_BUDGET_STATUS ' ||
      'FROM   AS_LEADS_ALL OPP,  ' || CHR(10) ||                                      
      '       HZ_CONTACT_POINTS CNTPNT,  ' || CHR(10) ||                              
      '       HZ_PARTY_SITES SITE,  ' || CHR(10) ||                                   
      '       HZ_LOCATIONS LOC,  ' || CHR(10) ||                                      
      '       HZ_PARTIES PARTY,  ' || CHR(10) ||                                      
      '       HZ_ORGANIZATION_PROFILES ORGP,  ' || CHR(10) ||                         
      '          AS_STATUSES_B STATUS,  ' || CHR(10) ||                                     
      '          ( select distinct ACC.lead_id lead_id  ' || CHR(10) ||                     
      '         from (select distinct terr_id terr_id  ' || CHR(10) ||                
      '               from JTY_CHANGED_TERRS  ' || CHR(10) ||                         
      '               where tap_request_id = l_request_id) CHG_TERR,  ' || CHR(10) || 
      '              AS_ACCESSES_ALL ACC,  ' || CHR(10) ||                            
      '              AS_TERRITORY_ACCESSES TERR_ACC  ' || CHR(10) ||                  
      '         where CHG_TERR.terr_id = TERR_ACC.territory_id  ' || CHR(10) ||       
      '         and TERR_ACC.access_id = acc.access_id  ' || CHR(10) ||               
      '         and acc.lead_id is not null  ' || CHR(10) ||                          
      '         and acc.sales_lead_id is null )  ACHNG  ' || CHR(10) ||               
      'WHERE  CNTPNT.OWNER_TABLE_NAME(+) = ''HZ_PARTY_SITES''  ' || CHR(10) ||        
      'AND  SITE.PARTY_SITE_ID = CNTPNT.OWNER_TABLE_ID(+)  ' || CHR(10) ||            
      'AND  CNTPNT.PRIMARY_FLAG(+) = ''Y''  ' || CHR(10) ||                           
      'AND  CNTPNT.STATUS(+) <> ''I''  ' || CHR(10) ||                                
      'AND  CNTPNT.contact_point_type(+)=''PHONE''  ' || CHR(10) ||                   
      'AND  OPP.ADDRESS_ID = SITE.PARTY_SITE_ID  ' || CHR(10) ||                      
      'AND  SITE.LOCATION_ID = LOC.LOCATION_ID  ' || CHR(10) ||                       
      'AND  SITE.STATUS = ''A''  ' || CHR(10) ||                                      
      'AND  OPP.CUSTOMER_ID = PARTY.PARTY_ID  ' || CHR(10) ||                         
      'AND  party.party_id = orgp.party_id(+)  ' || CHR(10) ||                        
      'AND  party.STATUS = ''A''  ' || CHR(10) ||                                     
      'AND  nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate  ' || CHR(10) ||    
      'AND  OPP.status = STATUS.status_code  ' || CHR(10) ||                          
      'AND  STATUS.opp_flag = ''Y''  ' || CHR(10) ||                                  
      'AND  achng.lead_id = opp.lead_id  ';                                           
      
      
  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);
          
          TYPE namesarray IS VARRAY(5) OF VARCHAR2(500);
          TYPE descarray IS VARRAY(5) OF VARCHAR2(500);
          TYPE langarray IS VARRAY(5) OF VARCHAR2(50);
          names namesarray;
          descript descarray;
          lang langarray;
          total INTEGER;
     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;
          BEGIN
  2. Create the meta-data for the CMA. Follow the comments provided in the script to enter the relevant CMA information.

    /* Provide CMA details in at least one language, such as 'US'*/
    /* To create a CMA in multi-language, provide those details in 'names', 'descript' and 'lang' as an array*/
          
                names := namesarray('Opportunity Budget Status (CMA)';
                descript := descarray('Custom Matching Attribute for Opportunity Budget Status');
                lang := langarray('US');
                total := names.count;
    
         FOR i in 1 .. total LOOP
    /* The following meta-data will create the custom matching attribute for the Budget Status.*/ 
           JTY_CUST_QUAL_PKG.Create_qual( 
         p_seeded_qual_id     => -9103, 
    /* This number should correspond to the number used in the alias for the attribute*/
         p_name               => names(i), 
         p_description        => descript(i), 
         p_language           => lang(i),
      
         ,/* 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);
    
    EXIT WHEN retcode=2;
    END LOOP;
    END;
    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 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:

    • l_real_time_sql :=

      'SELECT ' || CHR(10) ||                                                         
      '  party.party_id                                   trans_object_id, ' ||       
      CHR(10) ||                                                                      
      '  null                                             trans_detail_object_id, ' ||
      CHR(10) ||                                                                      
      '  TO_CHAR(NULL)                                    squal_char02, ' || CHR(10)  
      ||                                                                              
      '  TO_CHAR(NULL)                                    squal_char06, ' || CHR(10)  
      ||                                                                              
      '  TO_CHAR(NULL)                                    squal_char04, ' || CHR(10)  
      ||                                                                              
      '  TO_CHAR(NULL)                                    squal_char05, ' || CHR(10)  
      ||                                                                              
      '  TO_CHAR(NULL)                                    squal_char03, ' || CHR(10)  
      ||                                                                              
      '  TO_CHAR(NULL)                                    squal_char07, ' || CHR(10)  
      ||                                                                              
      '  party.party_id                                   squal_num01, ' || CHR(10) ||
      '  TO_NUMBER(NULL)                                  squal_num02, ' || CHR(10) ||
      '  upper(party.primary_phone_area_code)             squal_char08, ' || CHR(10)  
      ||                                                                              
      '  upper(party.party_name)                          squal_char01, ' || CHR(10)  
      ||                                                                              
      '  party.party_id                                   squal_num03, ' || CHR(10) ||
      '  party.employees_total                            squal_num05, ' || CHR(10) ||
      '  upper(party.category_code)                       squal_char09, ' || CHR(10)  
      ||                                                                              
      '  party.party_id                                   squal_num04, ' || CHR(10) ||
      '  upper(party.sic_code_type||'': ''||party.sic_code) squal_char10, ' || CHR(10)
      ||                                                                              
      '  orgp.CURR_FY_POTENTIAL_REVENUE                   squal_num06, ' || CHR(10) ||
      '  upper(orgp.PREF_FUNCTIONAL_CURRENCY)             squal_curc01, ' || CHR(10)  
      ||                                                                              
      '  upper(party.duns_number_c)                       squal_char11, ' || CHR(10)  
      ||                                                                              
      '  l_txn_date                                       txn_date ' || CHR(10) ||    
      '  ,UPPER(party.party_type)                            Q9600_PARTY_TYPE  ' ||   
      'FROM ' || CHR(10) ||                                                           
      '  HZ_PARTIES party, ' || CHR(10) ||                                            
      '  HZ_ORGANIZATION_PROFILES ORGP ' || CHR(10) ||                                
      'WHERE party.party_type IN (''PERSON'',''ORGANIZATION'') ' || CHR(10) ||        
      'AND   party.status=''A'' ' || CHR(10) ||                                       
      'AND   party.party_id = orgp.party_id(+) ' || CHR(10) ||                        
      'AND   nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate ' || CHR(10) ||    
      'AND   party.party_id = l_trans_object_id1 UNION ALL SELECT ' || CHR(10) ||     
      '  party.party_id                                   trans_object_id, ' ||       
      CHR(10) ||                                                                      
      '  addr.party_site_id                               trans_detail_object_id, ' ||
      CHR(10) ||                                                                      
      '  upper(LOC.city)                                  squal_char02, ' || CHR(10)  
      ||                                                                              
      '  upper(LOC.postal_code)                           squal_char06, ' || CHR(10)  
      ||                                                                              
      '  upper(LOC.state)                                 squal_char04, ' || CHR(10)  
      ||                                                                              
      '  upper(LOC.province)                              squal_char05, ' || CHR(10)  
      ||                                                                              
      '  upper(LOC.county)                                squal_char03, ' || CHR(10)  
      ||                                                                              
      '  upper(LOC.country)                               squal_char07, ' || CHR(10)  
      ||                                                                              
      '  party.party_id                                   squal_num01, ' || CHR(10) ||
      '  addr.party_site_id                               squal_num02, ' || CHR(10) ||
      '  upper(phone.phone_area_code)                     squal_char08, ' || CHR(10)  
      ||                                                                              
      '  upper(party.party_name)                          squal_char01, ' || CHR(10)  
      ||                                                                              
      '  party.party_id                                   squal_num03, ' || CHR(10) ||
      '  party.employees_total                            squal_num05, ' || CHR(10) ||
      '  upper(party.category_code)                       squal_char09, ' || CHR(10)  
      ||                                                                              
      '  party.party_id                                   squal_num04, ' || CHR(10) ||
      '  upper(party.sic_code_type||'': ''||party.sic_code) squal_char10, ' || CHR(10)
      ||                                                                              
      '  orgp.CURR_FY_POTENTIAL_REVENUE                   squal_num06, ' || CHR(10) ||
      '  upper(orgp.PREF_FUNCTIONAL_CURRENCY)             squal_curc01, ' || CHR(10)  
      ||                                                                              
      '  upper(party.duns_number_c)                       squal_char11, ' || CHR(10)  
      ||                                                                              
      '  l_txn_date                                       txn_date ' || CHR(10) || 
      '  ,UPPER(party.party_type)                            Q9600_PARTY_TYPE  ' ||   
      'FROM ' || CHR(10) ||                                                           
      '  HZ_CONTACT_POINTS phone, ' || CHR(10) ||                                     
      '  HZ_PARTIES party, ' || CHR(10) ||                                            
      '  HZ_PARTY_SITES addr, ' || CHR(10) ||                                         
      '  HZ_LOCATIONS LOC, ' || CHR(10) ||                                            
      '  HZ_ORGANIZATION_PROFILES ORGP ' || CHR(10) ||                                
      'WHERE  phone.owner_table_name(+) = ''HZ_PARTY_SITES'' ' || CHR(10) ||          
      'AND    phone.primary_flag(+) = ''Y'' ' || CHR(10) ||                           
      'AND    phone.status(+) = ''A'' ' || CHR(10) ||                                 
      'AND    phone.contact_point_type (+) = ''PHONE'' ' || CHR(10) ||                
      'AND    addr.party_site_id = phone.owner_table_id(+) ' || CHR(10) ||            
      'AND    addr.party_id=party.party_id ' || CHR(10) ||                            
      'AND    party.party_id = orgp.party_id(+) ' || CHR(10) ||                       
      'AND    nvl(orgp.effective_end_date(+),sysdate + 1) > sysdate ' || CHR(10) ||   
      'AND    party.party_type IN (''PERSON'',''ORGANIZATION'') ' || CHR(10) ||       
      'AND    party.status=''A'' ' || CHR(10) ||                                      
      'AND    LOC.location_id = addr.location_id ' || CHR(10) ||                      
      'AND    addr.status=''A'' ' || CHR(10) ||                                       
      'AND    party.party_id = l_trans_object_id1 ' || CHR(10) ||                     
      'AND    addr.party_site_id = nvl(l_trans_object_id2, addr.party_site_id) ';     
      l_batch_total_sql :=                                                            
      'SELECT /*+ parallel(ORGPRO) parallel(CNTPNT) parallel(X) use_hash(ORGPRO CNTPNT
      X) */ ' || CHR(10) ||                                                           
      '-1600 source_id, ' || CHR(10) ||                                               
      '-1601 trans_object_type_id, ' || CHR(10) ||                                    
      'X.party_id  TRANS_OBJECT_ID, ' || CHR(10) ||                                   
      'X.party_site_id TRANS_DETAIL_OBJECT_ID, ' || CHR(10) ||                        
      'X.party_site_id SQUAL_NUM02, ' || CHR(10) ||                                   
      'X.party_id SQUAL_NUM04, ' || CHR(10) ||                                        
      'UPPER(CNTPNT.phone_area_code) SQUAL_CHAR08, ' || CHR(10) ||                    
      'UPPER(X.category_code) SQUAL_CHAR09, ' || CHR(10) ||                           
      'UPPER(ORGPRO.pref_functional_currency)  SQUAL_CURC01, ' || CHR(10) ||          
      'UPPER(X.city) SQUAL_CHAR02, ' || CHR(10) ||                                    
      'ORGPRO.curr_fy_potential_revenue SQUAL_NUM06, ' || CHR(10) ||                  
      'UPPER(X.country) SQUAL_CHAR07, ' || CHR(10) ||                                 
      'UPPER(X.county) SQUAL_CHAR03, ' || CHR(10) ||                                  
      'X.party_id SQUAL_NUM01, ' || CHR(10) ||                                        
      'UPPER(X.party_name_substring) SQUAL_FC01, ' || CHR(10) ||                      
      'UPPER(X.party_name) SQUAL_CHAR01, ' || CHR(10) ||                              
      'X.employees_total SQUAL_NUM05, ' || CHR(10) ||                                 
      'UPPER(X.postal_code) SQUAL_CHAR06, ' || CHR(10) ||                             
      'UPPER(X.province) SQUAL_CHAR05, ' || CHR(10) ||                                
      'X.party_id SQUAL_NUM03, ' || CHR(10) ||                                        
      'UPPER(X.sic_code_type||'': ''||X.sic_code) SQUAL_CHAR10, ' || CHR(10) ||       
      'UPPER(X.state)  SQUAL_CHAR04, ' || CHR(10) ||                                  
      'UPPER(X.duns_number_c) SQUAL_CHAR11 ' || CHR(10) ||                            
      ',UPPER(x.party_type)                            Q9600_PARTY_TYPE  ' ||  
      'from HZ_ORGANIZATION_PROFILES ORGPRO , ' || CHR(10) ||                         
      'HZ_CONTACT_POINTS CNTPNT, ' || CHR(10) ||                                      
      '(select /*+ parallel(PARTY) parallel(SITE) parallel(LOC) use_hash(SITE)        
      use_hash(PARTY) use_hash(LOC) */ ' || CHR(10) ||                                
      'SITE.party_site_id party_site_id,  Site.party_id party_id, ' || CHR(10) ||     
      'LOC.city city,  LOC.country country, ' || CHR(10) ||                           
      'LOC.county county,  LOC.state state, ' || CHR(10) ||                           
      'LOC.province province,  LOC.postal_code postal_code, ' || CHR(10) ||           
      'PARTY.employees_total employees_total, ' || CHR(10) ||                         
      'PARTY.sic_code_type sic_code_type, ' || CHR(10) ||                             
      'PARTY.sic_code sic_code, ' || CHR(10) ||                                       
      'upper(substr(PARTY.party_name,1,1)) party_name_substring, ' || CHR(10) ||      
      'upper(PARTY.party_name) party_name, ' || CHR(10) ||                            
      'PARTY.category_code category_code, ' || CHR(10) ||                             
      '''HZ_PARTY_SITES'' owner_table_name, ' || CHR(10) ||                           
      'SITE.party_site_id owner_table_id, ' || CHR(10) ||                             
      'PARTY.duns_number_c ' || CHR(10) || 
      ',PARTY.party_type party_type  ' || CHR(10) ||                                            
      'from HZ_PARTY_SITES SITE, ' || CHR(10) ||                                      
      'HZ_LOCATIONS LOC, HZ_PARTIES PARTY where ' || CHR(10) ||                       
      'SITE.status = ''A'' and SITE.party_id = PARTY.party_id and ' || CHR(10) ||     
      'PARTY.party_type in (''PERSON'', ''ORGANIZATION'') and PARTY.status = ''A'' and
      ' || CHR(10) ||                                                                 
      'LOC.location_id = SITE.location_id ' || CHR(10) ||                             
      'union all ' || CHR(10) ||                                                      
      'select /*+ parallel(PARTY) */ to_number(NULL) party_site_id , ' || CHR(10) ||  
      'PARTY.party_id party_id , ' || CHR(10) ||     
      'PARTY.party_type party_type,  ' || CHR(10) ||                                  
      'to_char(NULL) city ,  to_char(NULL) country , ' || CHR(10) ||                  
      'to_char(NULL) county , to_char(NULL) state , ' || CHR(10) ||                   
      'to_char(NULL) province ,  to_char(NULL) postal_code , ' || CHR(10) ||          
      'PARTY.employees_total employees_total, ' || CHR(10) ||                         
      'PARTY.sic_code_type sic_code_type, ' || CHR(10) ||                             
      'PARTY.sic_code sic_code, ' || CHR(10) ||                                       
      'upper(substr(PARTY.party_name,1,1)) party_name_substring, ' || CHR(10) ||      
      'upper(PARTY.party_name) party_name, ' || CHR(10) ||                            
      'PARTY.category_code category_code, ' || CHR(10) ||                             
      '''HZ_PARTIES'' owner_table_name, ' || CHR(10) ||                               
      'PARTY.party_id owner_table_id, ' || CHR(10) ||                                 
      'PARTY.duns_number_c ' || CHR(10) ||                                            
      'from HZ_PARTIES PARTY ' || CHR(10) ||                                          
      'where PARTY.party_type in (''PERSON'', ''ORGANIZATION'') and ' || CHR(10) ||   
      'PARTY.status = ''A'' ) X , ' || CHR(10) ||                                     
      'AR_TRX_BAL_SUMMARY arba, ' || CHR(10) ||                                       
      'hz_cust_accounts Hzc ' || CHR(10) ||                                           
      'WHERE ' || CHR(10) ||                                                          
      'CNTPNT.owner_table_name(+) = X.owner_table_name and ' || CHR(10) ||            
      'CNTPNT.owner_table_id(+) = X.owner_table_id and ' || CHR(10) ||                
      'CNTPNT.status(+)=''A'' and CNTPNT.primary_flag(+)=''Y'' and ' || CHR(10) ||    
      'CNTPNT.contact_point_type(+)=''PHONE'' and ' || CHR(10) ||                     
      'ORGPRO.party_id(+) = X.party_id and nvl(ORGPRO.effective_end_date(+),sysdate+1)
      > sysdate ' || CHR(10) ||                                                       
      'AND arba.CUST_ACCOUNT_ID = hzc.CUST_ACCOUNT_ID and hzc.party_id  = x.party_id  
      ';                                                                              
       
    • l_batch_total_sql :=

    • l_batch_incr_sql := NULL

    • l_batch_dea_sql := NULL

    • l_incr_reassign_sql := NULL

  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);
          
          TYPE namesarray IS VARRAY(5) OF VARCHAR2(500);
          TYPE descarray IS VARRAY(5) OF VARCHAR2(500);
          TYPE langarray IS VARRAY(5) OF VARCHAR2(50);
          names namesarray;
          descript descarray;
          lang langarray;
          total INTEGER;
     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;
          BEGIN
  2. Create the meta-data for the CMA. Follow the comments provided in the script to enter the relevant CMA information.

    /* Provide CMA details in at least one language, such as 'US'*/
    /* To create a CMA in multi-language, provide those details in 'names', 'descript' and 'lang' as an array*/
          
                names := namesarray('Customer Party Type (CMA)');
                descript := descarray('Customer Party Type (CMA)');
                lang := langarray('US');
                total := names.count;
    
         FOR i in 1 .. total LOOP
    /* The following meta-data will create the custom matching attribute for the Customer Party Type.*/  
           JTY_CUST_QUAL_PKG.Create_qual( 
         p_seeded_qual_id     => -9600,
    /* This number should correspond to the number used in the alias for the attribute*/
         p_name               => names(i),
        ,p_description        => descript(i),
        ,p_language           => lang(i),
     
         ,/* 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);
    
    EXIT WHEN retcode=2;
    END LOOP;
    END;
    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;