This appendix covers the following topics:
This is an example of creating a custom matching attribute for Sales using the account transaction and the party type attribute.
Modify Script 1 to retrieve the relevant account transaction type SQL meta-data.
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;
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;
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 the transaction type SQL meta-data (R12AccountTT.sql) to get the CMA's corresponding transaction attribute value.
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;
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);
Run the R12AccountTT.sql file.
SQL> @R12AccountTT.sql /
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
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;
BEGINCreate 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;
/
Run this script.
This is an example of creating a custom matching attribute for Sales using the lead transaction and the lead status attribute.
Modify Script 1 to retrieve the relevant lead transaction type SQL meta-data.
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;
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;
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 the transaction type SQL meta-data (R12ALeadTT.sql) to get the CMA's corresponding transaction attribute value.
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 :=
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); Run the R12LeadTT.sql file.
SQL> @R12LeadTT.sql /
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
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;
BEGINCreate 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;
/Run this script.
This is an example of creating a custom matching attribute for Sales using the quote transaction and the quote source attribute.
Modify Script 1 to retrieve the relevant quote transaction type SQL meta-data.
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;
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;
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 the transaction type SQL meta-data (R12QuoteTT.sql,) to get the CMA's corresponding transaction attribute value.
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 ';
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); Run the R12QuoteTT.sql file.
SQL> @R12QuoteTT.sql /
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
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;
BEGINCreate 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;
/Run this script.
This is an example of creating a custom matching attribute for Sales using the proposal transaction and the proposal status attribute.
Modify Script 1 to retrieve the relevant proposal transaction type SQL meta-data.
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;
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;
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 the transaction type SQL meta-data (R12AccountTT.sql) to get the CMA's corresponding transaction attribute value.
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 :=
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); Run the R12ProposalTT.sql file.
SQL> @R12ProposalTT.sql /
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
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;
BEGINCreate 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;
/
Run this script.
This is an example of creating a custom matching attribute for Sales using the opportunity transaction and the budget status attribute.
Modify Script 1 to retrieve the relevant Opportunity transaction type SQL meta-data.
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;
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;
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 the transaction type SQL meta-data (R12OpportunityTT.sql) to get the CMA's corresponding transaction attribute value.
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 ';
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); Run the R12OpportunityTT.sql file.
SQL> @R12OpportunityTT.sql /
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
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;
BEGINCreate 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;
/Run this script.
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 to retrieve the relevant customer transaction type SQL meta-data.
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;
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;
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 the transaction type SQL meta-data (R12CustomerTT.sql) to get the CMA's corresponding transaction attribute value.
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
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;
/ Run the R12CustomerTT.sql file.
SQL> @R12CustomerTT.sql /
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
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;
BEGINCreate 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;
/Run this script.
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;