[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* changed. The insert statement for AMS_PARTY_MARKET_SEGMENTS
* is changed. It now inserts cust_account_id,cust_acct_site_id
* and cust_site_use_code.
******************************************************************************/
PROCEDURE generate_party_for_territory
( p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER,
p_terr_id IN NUMBER,
p_getparent_flag IN VARCHAR2 := 'N',
p_bind_vars IN BIND_TBL_TYPE,
p_hzparty_sql IN VARCHAR2 := null,
p_hzpartyacc_sql IN VARCHAR2 := null,
p_hzpartyrel_sql IN VARCHAR2 := null,
-- p_hzpartysite_sql IN VARCHAR2 := null,
p_hzpartysiteuse_sql IN VARCHAR2 := null,
p_hzcustprof_sql IN VARCHAR2 := null,
p_hzlocations_sql IN VARCHAR2 := null
-- p_hzcustname_sql IN VARCHAR2 := null,
-- p_hzcustcat_sql IN VARCHAR2 := null,
-- p_hzsaleschannel_sql IN VARCHAR2 := null
)
IS
l_full_name CONSTANT VARCHAR2(60) := 'GENERATE_PARTY_FOR_TERRITORY';
l_party_select_sql CLOB := null;
l_store_select_sql CLOB := null;
l_store_insert_sql CLOB := null;
CURSOR client_info_csr IS select org_id from jtf_terr_all where terr_id = p_terr_id;
l_party_select_sql := 'SELECT DISTINCT hzca.party_id, '||
'hzca.cust_account_id, '||
'hzcsua.cust_acct_site_id, '||
'hzcsua.site_use_id, '||
'hzcsua.bill_to_site_use_id, '||
'hzcsua.site_use_code ' ;
l_party_select_sql := l_party_select_sql ||
'FROM hz_cust_site_uses_all hzcsua, '||
'hz_cust_acct_sites_all hzcasa, '||
'hz_cust_accounts hzca, ';
l_party_select_sql := l_party_select_sql ||
'hz_party_sites hzps, '||
'hz_locations hzloc, '||
'hz_parties hzp ' ;
write_conc_log('l_hzparty_sql ' || l_party_select_sql || l_party_where_sql);
l_store_select_sql := 'SELECT DISTINCT hzp.party_id, '||
'hzpsu.party_site_use_id site_use_id, '||
'hzpsu.site_use_type site_use_code, '||
'hzps.LOCATION_ID ';
l_store_select_sql := l_store_select_sql ||
'FROM hz_parties hzp, '||
'hz_party_sites hzps, '||
'hz_party_site_uses hzpsu ';
l_store_final_sql := l_store_select_sql||l_store_where_sql;
IF l_party_select_sql IS NULL
THEN
--
l_party_select_sql := 'SELECT DISTINCT hzca.party_id, '||
'hzca.cust_account_id, '||
'hzcsua.cust_acct_site_id, '||
'hzcsua.site_use_id, '||
'hzcsua.bill_to_site_use_id, '||
'hzcsua.site_use_code ' ;
l_party_select_sql := l_party_select_sql ||
'FROM hz_cust_accounts hzca, '||
'hz_cust_site_uses_all hzcsua, '||
'hz_cust_acct_sites_all hzcasa ';
write_conc_log('IF l_hzpartysiteuse_sql ' || l_party_select_sql || l_party_where_sql);
write_conc_log('Else l_hzpartysiteuse_sql ' || l_party_select_sql || l_party_where_sql);
IF l_party_select_sql IS NULL
THEN
--
l_party_select_sql := 'SELECT DISTINCT hzca.party_id, '||
'hzca.cust_account_id, '||
'hzcsua.cust_acct_site_id, '||
'hzcsua.site_use_id, '||
'hzcsua.bill_to_site_use_id, '||
'hzcsua.site_use_code ';
l_party_select_sql := l_party_select_sql ||
'FROM hz_cust_accounts hzca, '||
'hz_cust_site_uses_all hzcsua, '||
'hz_cust_acct_sites_all hzcasa, ';
l_party_select_sql := l_party_select_sql ||
'hz_party_sites hzps ';
write_conc_log('IF l_hzpartyacc_sql ' || l_party_select_sql || l_party_where_sql);
IF INSTR(l_party_select_sql, 'hz_party_sites') = 0 THEN
l_party_select_sql := l_party_select_sql || ', hz_party_sites hzps ';
write_conc_log('ELSE l_hzpartyacc_sql ' || l_party_select_sql || l_party_where_sql);
IF l_party_select_sql IS NULL
THEN
--
l_party_select_sql := 'SELECT DISTINCT hzca.party_id, '||
'hzca.cust_account_id, '||
'hzcsua.cust_acct_site_id, '||
'hzcsua.site_use_id, '||
'hzcsua.bill_to_site_use_id, '||
'hzcsua.site_use_code ';
l_party_select_sql := l_party_select_sql ||
'FROM hz_cust_site_uses_all hzcsua, '||
'hz_cust_acct_sites_all hzcasa, '||
'hz_cust_accounts hzca, ';
l_party_select_sql := l_party_select_sql ||
'hz_relationships hzpr ';
write_conc_log('IF l_hzpartyrel_sql ' || l_party_select_sql || l_party_where_sql);
l_party_select_sql := l_party_select_sql ||
', hz_relationships hzpr ';
write_conc_log('Else l_hzpartyrel_sql ' || l_party_select_sql || l_party_where_sql);
IF l_party_select_sql IS NULL
THEN
--
l_party_select_sql := 'SELECT DISTINCT hzca.party_id, '||
'hzca.cust_account_id, '||
'hzcsua.cust_acct_site_id, '||
'hzcsua.site_use_id, '||
'hzcsua.bill_to_site_use_id, ';
l_party_select_sql := l_party_select_sql ||
'hzcsua.site_use_code '||
'FROM hz_cust_accounts hzca, '||
'hz_cust_site_uses_all hzcsua, ';
l_party_select_sql := l_party_select_sql ||
'hz_cust_acct_sites_all hzcasa, '||
'hz_customer_profiles hzcp ';
write_conc_log(' If l_hzcustprof_sql ' || l_party_select_sql || l_party_where_sql);
l_party_select_sql := l_party_select_sql ||
', hz_customer_profiles hzcp ';
write_conc_log(' If Else If l_hzcustprof_sql ' || l_party_select_sql || l_party_where_sql);
l_party_select_sql := l_party_select_sql ||
', hz_customer_profiles hzcp ';
write_conc_log(' If Else else l_hzcustprof_sql '|| l_party_select_sql || l_party_where_sql);
IF l_party_select_sql IS NULL
THEN
--
l_party_select_sql := 'SELECT DISTINCT hzca.party_id, '||
'hzca.cust_account_id, '||
'hzcsua.cust_acct_site_id, '||
'hzcsua.site_use_id, '||
'hzcsua.bill_to_site_use_id, '||
'hzcsua.site_use_code ';
l_party_select_sql := l_party_select_sql ||
'FROM hz_cust_site_uses_all hzcsua, '||
'hz_cust_acct_sites_all hzcasa, '||
'hz_cust_accounts hzca, ';
l_party_select_sql := l_party_select_sql ||
--R12: mkothari 'hz_relationships hzpr, '||
'hz_party_sites hzps, '||
'hz_locations hzloc ';
write_conc_log(' If l_hzloactions_sql ' || l_party_select_sql || l_party_where_sql);
IF INSTR(l_party_select_sql, 'hz_locations') = 0 THEN
l_party_select_sql := l_party_select_sql || ', hz_locations hzloc ';
IF INSTR(l_party_select_sql, 'hz_party_sites') = 0 THEN
l_party_select_sql := l_party_select_sql || ', hz_party_sites hzps ';
l_party_select_sql := l_party_select_sql ||
', hz_locations hzloc '||
', hz_party_sites hzps ';
write_conc_log(' Else l_hzloactions_sql ' || l_party_select_sql || l_party_where_sql);
l_store_select_sql := 'SELECT DISTINCT hzp.party_id, '||
'hzpsu.party_site_use_id site_use_id, '||
'hzpsu.site_use_type site_use_code,'||
'hzps.location_id ';
l_store_select_sql := l_store_select_sql ||
'FROM hz_parties hzp, '||
'hz_party_sites hzps, '||
'hz_party_site_uses hzpsu, '||
'hz_locations hzloc ';
l_store_final_sql := l_store_select_sql||l_store_where_sql;
DBMS_OUTPUT.PUT_LINE(' final from sql(' || length(l_party_select_sql) || '): ' || l_party_select_sql);
write_conc_log('############### Store Insert SQL >>> START');
l_store_insert_sql := 'INSERT INTO OZF_TP_TERRUSG_MAP('||
'OZF_TP_TERRUSG_MAP_ID, '||
'last_update_date, '||
'last_updated_by,';
l_store_insert_sql := l_store_insert_sql ||
'creation_date, '||
'created_by, '||
'last_update_login, ';
l_store_insert_sql := l_store_insert_sql ||
'party_id, '||
'start_date_active, '||
'end_date_active, ' ;
l_store_insert_sql := l_store_insert_sql ||
'market_qualifier_type, '||
'market_qualifier_reference, '||
'cust_account_id, '||
'cust_acct_site_id, '||
'site_use_id, '||
'bill_to_site_use_id, '||
'site_use_code, '||
'user_added, '||
'location_id )';
l_store_insert_sql := l_store_insert_sql ||
' SELECT OZF_TP_TERRUSG_MAP_S.nextval, '||
'SYSDATE, '||
'FND_GLOBAL.user_id, ';
l_store_insert_sql := l_store_insert_sql ||
'SYSDATE, '||
'FND_GLOBAL.user_id, '||
'FND_GLOBAL.conc_login_id, ';
l_store_insert_sql := l_store_insert_sql ||
'party_id, '||
'SYSDATE, '||
'NULL, ';
l_store_insert_sql := l_store_insert_sql ||
' ''TERRITORY'', '||
':terr_id market_qualifier_reference, '||
'NULL, '||
'NULL, '||
'site_use_id, '||
'NULL, '||
'site_use_code, '||
'''N'' user_added, '||
'location_id '||
'FROM (';
l_store_insert_sql := l_store_insert_sql ||
l_store_final_sql ||
')';
write_conc_log('############### Store Insert SQL >>> Constructed '||l_store_insert_sql);
write_conc_log('###############l_store_insert_sql: '||l_store_insert_sql);
IF l_store_insert_sql IS NOT NULL THEN
l_store_csr := DBMS_SQL.open_cursor;
DBMS_SQL.parse(l_store_csr, l_store_insert_sql, DBMS_SQL.native);
select length(l_store_final_sql) into len from dual;
select instr(translate(lvar,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X') into isNum FROM dual;
DELETE FROM OZF_TP_TERRUSG_MAP
WHERE market_qualifier_type = 'TERRITORY'
AND market_qualifier_reference = p_terr_id
AND user_added = 'N';
write_conc_log('############### Stores inserted in ozf mapping: '||l_store_index);
DELETE FROM OZF_PARTY_MARKET_SEGMENTS_T
WHERE market_qualifier_type = 'TERRITORY'
AND market_qualifier_reference = p_terr_id;
/* --this give GSCC error - "select * not allowed"
INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T
SELECT * FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
*/
INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T (
AMS_PARTY_MARKET_SEGMENT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER, MARKET_SEGMENT_ID, MARKET_SEGMENT_FLAG,
PARTY_ID, START_DATE_ACTIVE, END_DATE_ACTIVE,
ORG_ID, SECURITY_GROUP_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, TERRITORY_ID,
MARKET_QUALIFIER_TYPE, MARKET_QUALIFIER_REFERENCE, CUST_ACCOUNT_ID,
CUST_ACCT_SITE_ID, SITE_USE_CODE, BILL_TO_SITE_USE_ID,
ROLLUP_PARTY_ID, SITE_USE_ID)
SELECT AMS_PARTY_MARKET_SEGMENT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER, MARKET_SEGMENT_ID, MARKET_SEGMENT_FLAG,
PARTY_ID, START_DATE_ACTIVE, END_DATE_ACTIVE,
ORG_ID, SECURITY_GROUP_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, TERRITORY_ID,
MARKET_QUALIFIER_TYPE, MARKET_QUALIFIER_REFERENCE, CUST_ACCOUNT_ID,
CUST_ACCT_SITE_ID, SITE_USE_CODE, BILL_TO_SITE_USE_ID,
ROLLUP_PARTY_ID, SITE_USE_ID
FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
WHERE OLD_TERR.MARKET_QUALIFIER_TYPE='TERRITORY'
AND OLD_TERR.market_qualifier_reference = p_terr_id
AND OLD_TERR.site_use_code = 'SHIP_TO'
AND OLD_TERR.party_id IS NOT NULL
AND OLD_TERR.site_use_id IS NOT NULL;
DELETE FROM AMS_PARTY_MARKET_SEGMENTS
WHERE market_qualifier_type = 'TERRITORY'
AND market_qualifier_reference = p_terr_id;
l_party_select_sql ||
substr(l_party_where_sql, 1, length(l_party_where_sql)-4));
IF l_party_select_sql IS NOT NULL
THEN
-- yzhao: 05/08/2003 SQL bind variable project
l_final_sql := 'INSERT INTO AMS_PARTY_MARKET_SEGMENTS('||
'ams_party_market_segment_id, '||
'last_update_date, '||
'last_updated_by,';
'last_update_login, '||
'object_version_number, '||
'market_segment_id,';
' SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL, '||
'SYSDATE, '||
'FND_GLOBAL.user_id, ';
l_party_select_sql ||
substr(l_party_where_sql, 1, length(l_party_where_sql)-4) ||
')';
Ozf_Utility_pvt.write_conc_log(l_full_name || ': Rows inserted in ams denorm table: '||l_index);
INSERT INTO ams_party_market_segments
(
AMS_PARTY_MARKET_SEGMENT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MARKET_SEGMENT_ID,
MARKET_SEGMENT_FLAG,
PARTY_ID,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
MARKET_QUALIFIER_TYPE,
MARKET_QUALIFIER_REFERENCE,
CUST_ACCOUNT_ID,
CUST_ACCT_SITE_ID,
SITE_USE_CODE,
BILL_TO_SITE_USE_ID,
ROLLUP_PARTY_ID,
SITE_USE_ID,
ORG_ID
)
select
AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
0,
'N',
PARTY_ID,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
MARKET_QUALIFIER_TYPE,
MARKET_QUALIFIER_REFERENCE,
CUST_ACCOUNT_ID,
CUST_ACCT_SITE_ID,
SITE_USE_CODE,
BILL_TO_SITE_USE_ID,
ROLLUP_PARTY_ID,
SITE_USE_ID,
l_client_info
FROM ozf_tp_terrusg_map
WHERE MARKET_QUALIFIER_REFERENCE = p_terr_id;
FOR l_party_select_sql || substr(l_party_where_sql, 1, length(l_party_where_sql)-4);
INSERT INTO AMS_PARTY_MARKET_SEGMENTS
(
ams_party_market_segment_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, object_version_number
, market_segment_id
, market_segment_flag
, party_id
, start_date_active
, end_date_active
, org_id
, market_qualifier_type
, market_qualifier_reference
, cust_account_id
, cust_acct_site_id
, site_use_code
)
VALUES
(
l_party_mkt_seg_id
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.conc_login_id
, 1
, 0
, 'N'
, l_party_id
, SYSDATE
, NULL
, l_client_info
, 'TERRITORY'
, p_terr_id
,l_cust_account_id
,l_cust_acct_site_id
,l_cust_site_use_code
);
UPDATE ams_party_market_segments a
SET a.rollup_party_id = ( SELECT acct.party_id
FROM hz_cust_accounts_all acct,
hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_use
WHERE site_use.site_use_id = NVL(a.bill_to_site_use_id,a.site_use_id)
AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.cust_account_id = acct.cust_account_id)
WHERE a.market_qualifier_type = 'TERRITORY'
AND a.market_qualifier_reference = p_terr_id;
SELECT subject_id
FROM hz_relationships
WHERE relationship_code = fnd_profile.VALUE('OZF_PARTY_RELATIONS_TYPE')
AND subject_type = 'ORGANIZATION'
AND subject_table_name = 'HZ_PARTIES'
AND object_type = 'ORGANIZATION'
AND object_table_name = 'HZ_PARTIES'
AND start_date <= SYSDATE AND NVL(end_date, SYSDATE) >= SYSDATE
AND status = 'A'
AND object_id = p_bg_id
/* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI */
AND directional_flag = NVL(p_direction, directional_flag);
SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
FROM DUAL;
DELETE FROM AMS_PARTY_MARKET_SEGMENTS
WHERE market_qualifier_type = 'BG'
AND market_qualifier_reference = p_bg_id;
INSERT INTO AMS_PARTY_MARKET_SEGMENTS
(
ams_party_market_segment_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, object_version_number
, market_segment_id
, market_segment_flag
, party_id
, start_date_active
, end_date_active
, org_id
, market_qualifier_type
, market_qualifier_reference
)
VALUES
(
l_index
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.conc_login_id
, 1
, 0
, 'N'
, p_bg_id
, SYSDATE
, NULL
, l_client_info
, 'BG'
, p_bg_id
);
INSERT INTO AMS_PARTY_MARKET_SEGMENTS
(
ams_party_market_segment_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, object_version_number
, market_segment_id
, market_segment_flag
, party_id
, start_date_active
, end_date_active
, org_id
, market_qualifier_type
, market_qualifier_reference
)
VALUES
(
l_party_mkt_seg_id(I)
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.conc_login_id
, 1
, 0
, 'N'
, l_all_obj_list(I)
, SYSDATE
, NULL
, l_client_info
, 'BG'
, p_bg_id
);
for non-directional records, always insert a row pair of (A, B) and (B, A) */
IF (p_direction IS NOT NULL AND l_all_obj_list.FIRST IS NOT NULL) THEN
FOR I IN NVL(l_all_obj_list.FIRST, 1) .. NVL(l_all_obj_list.LAST, 0) LOOP
OPEN c_party_mkt_seg_seq;
INSERT INTO AMS_PARTY_MARKET_SEGMENTS
(
ams_party_market_segment_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, object_version_number
, market_segment_id
, market_segment_flag
, party_id
, start_date_active
, end_date_active
, org_id
, market_qualifier_type
, market_qualifier_reference
)
VALUES
(
l_party_mkt_seg_id(I)
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.conc_login_id
, 1
, 0
, 'N'
, p_bg_id
, SYSDATE
, NULL
, l_client_info
, 'BG'
, l_all_obj_list(I)
);
/*SELECT distinct terr_id
FROM jtf_terr_overview_v jtov
WHERE jtov.source_id = -1003
AND parent_territory_id = 1;
select distinct JTR.terr_id
FROM JTF_TERR_ALL JTR ,
JTF_TERR_USGS_ALL JTU ,
JTF_SOURCES_ALL JSE
WHERE JTU.TERR_ID = JTR.TERR_ID
AND JTU.SOURCE_ID = JSE.SOURCE_ID
AND JTU.SOURCE_ID = -1003
AND JTR.PARENT_TERRITORY_ID = 1
AND NVL(JTR.ORG_ID, -99) = NVL(JTU.ORG_ID, NVL(JTR.ORG_ID, -99))
AND JSE.ORG_ID IS NULL;
SELECT relationship_type, direction_code
FROM hz_relationship_types
WHERE (forward_rel_code = p_relationship_code
OR backward_rel_code = p_relationship_code)
AND subject_type = 'ORGANIZATION'
AND object_type = 'ORGANIZATION'
/* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
P - Parent C - Child N - non-directional
e.g. 'PARTNER_OF' is non-directional relationship
AND direction_code = 'P'
*/
AND direction_code IN ('P', 'N')
AND status = 'A'
/* mgudivak: Bug 3433528 */
AND hierarchical_flag = 'N';
SELECT distinct r1.object_id
FROM hz_relationships r1
WHERE r1.relationship_type = p_relationship_type
AND r1.relationship_code = p_relationship_code
AND r1.subject_type = 'ORGANIZATION'
AND r1.subject_table_name = 'HZ_PARTIES'
AND r1.object_type = 'ORGANIZATION'
AND r1.object_table_name = 'HZ_PARTIES'
AND r1.start_date <= SYSDATE AND NVL(r1.end_date, SYSDATE) >= SYSDATE
AND r1.status = 'A'
/* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
handle non-directional relationship e.g. PARTNER_OF
*/
AND r1.directional_flag = NVL(p_direction_code, r1.directional_flag)
AND NOT EXISTS
(SELECT 1
FROM hz_relationships r2
WHERE r1.object_id = r2.subject_id
AND r2.relationship_type = p_relationship_type
AND r2.relationship_code = p_relationship_code
AND r2.subject_type = 'ORGANIZATION'
AND r2.subject_table_name = 'HZ_PARTIES'
AND r2.object_type = 'ORGANIZATION'
AND r2.object_table_name = 'HZ_PARTIES'
AND r2.start_date <= SYSDATE AND NVL(r2.end_date, SYSDATE) >= SYSDATE
AND r2.status = 'A'
/* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
handle non-directional relationship e.g. PARTNER_OF
*/
AND r2.directional_flag = NVL(p_direction_code, r2.directional_flag)
);
CURSOR client_info_csr(l_terr_id NUMBER) IS select org_id from jtf_terr_all where terr_id = l_terr_id;
DELETE FROM OZF_PARTY_MARKET_SEGMENTS_T
WHERE market_qualifier_type = 'TERRITORY';
/* --this give GSCC error - "select * not allowed"
INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T
SELECT * FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
*/
/*
INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T (
AMS_PARTY_MARKET_SEGMENT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER, MARKET_SEGMENT_ID, MARKET_SEGMENT_FLAG,
PARTY_ID, START_DATE_ACTIVE, END_DATE_ACTIVE,
ORG_ID, SECURITY_GROUP_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, TERRITORY_ID,
MARKET_QUALIFIER_TYPE, MARKET_QUALIFIER_REFERENCE, CUST_ACCOUNT_ID,
CUST_ACCT_SITE_ID, SITE_USE_CODE, BILL_TO_SITE_USE_ID,
ROLLUP_PARTY_ID, SITE_USE_ID)
SELECT AMS_PARTY_MARKET_SEGMENT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER, MARKET_SEGMENT_ID, MARKET_SEGMENT_FLAG,
PARTY_ID, START_DATE_ACTIVE, END_DATE_ACTIVE,
ORG_ID, SECURITY_GROUP_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, TERRITORY_ID,
MARKET_QUALIFIER_TYPE, MARKET_QUALIFIER_REFERENCE, CUST_ACCOUNT_ID,
CUST_ACCT_SITE_ID, SITE_USE_CODE, BILL_TO_SITE_USE_ID,
ROLLUP_PARTY_ID, SITE_USE_ID
FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
WHERE OLD_TERR.MARKET_QUALIFIER_TYPE='TERRITORY'
AND OLD_TERR.site_use_code = 'SHIP_TO'
AND OLD_TERR.party_id IS NOT NULL
AND OLD_TERR.site_use_id IS NOT NULL;