[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hzp.party_name
into p_party_name
from hz_parties hzp, hz_party_sites hzps
where hzp.party_id = hzps.party_id
and hzps.party_site_id = p_party_site_id;
SELECT H3.party_name,
H3.known_as,
H1.postal_code
INTO p_business_name,
p_trade_name,
p_postal_code
FROM HZ_PARTIES H3,
HZ_LOCATIONS H1,
HZ_PARTY_SITES H2
WHERE h3.party_id = h2.party_id
AND h2.location_id = h1.location_id
AND h3.party_id = p_party_id
AND h2.party_site_id = p_party_site_id;
INSERT INTO jtf_tty_acct_qual_maps
(ACCOUNT_QUAL_MAP_ID,
OBJECT_VERSION_NUMBER,
NAMED_ACCOUNT_ID,
QUAL_USG_ID,
COMPARISON_OPERATOR,
VALUE1_CHAR,
VALUE2_CHAR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(
SELECT jtf_tty_acct_qual_maps_s.NEXTVAL,
1,
p_acct_id,
-1012,
'=',
UPPER(p_business_name),
NULL,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE FROM dual);
INSERT INTO jtf_tty_acct_qual_maps
(ACCOUNT_QUAL_MAP_ID,
OBJECT_VERSION_NUMBER,
NAMED_ACCOUNT_ID,
QUAL_USG_ID,
COMPARISON_OPERATOR,
VALUE1_CHAR,
VALUE2_CHAR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(SELECT jtf_tty_acct_qual_maps_s.NEXTVAL,
1,
p_acct_id,
-1012,
'=',
UPPER(p_trade_name),
NULL,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE FROM dual);
INSERT INTO jtf_tty_acct_qual_maps
(ACCOUNT_QUAL_MAP_ID,
OBJECT_VERSION_NUMBER,
NAMED_ACCOUNT_ID,
QUAL_USG_ID,
COMPARISON_OPERATOR,
VALUE1_CHAR,
VALUE2_CHAR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(SELECT jtf_tty_acct_qual_maps_s.NEXTVAL,
1,
p_acct_id,
-1007,
'=',
p_postal_code,
NULL,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE FROM dual);
SELECT named_account_id na_id
FROM jtf_tty_named_accts
WHERE party_id = p_party_id;
SELECT named_account_id na_id
FROM jtf_tty_named_accts
WHERE party_id = p_party_id;
SELECT tga.terr_group_account_id, tga.terr_group_id, tg.matching_rule_code
FROM jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
WHERE tga.terr_group_id = tg.terr_group_id
AND tga.named_account_id = p_na_id;
select 'Y', named_account_id
into p_from_na_flag, p_from_na_id
from jtf_tty_named_accts
where party_site_id = p_from_fk_id
and rownum < 2;
select 'Y', named_account_id
into p_to_na_flag, p_to_na_id
from jtf_tty_named_accts
where party_site_id = p_to_fk_id
and rownum < 2;
SELECT hzps.party_id
INTO l_from_party_id
FROM hz_party_sites hzps
WHERE hzps.party_site_id = p_from_fk_id
AND rownum < 2;
SELECT hzps.party_id
INTO l_to_party_id
FROM hz_party_sites hzps
WHERE hzps.party_site_id = p_to_fk_id
AND rownum < 2;
select 'N'
into l_dist_terr_region
from jtf_tty_terr_grp_accts jtga_outer
where jtga_outer.named_account_id = p_to_na_id
and jtga_outer.terr_group_id = tga_rec.terr_group_id
and rownum < 2;
/* Delete records from JTF_TERR_... tables corresponding to the named account */
JTF_TTY_GEN_TERR_PVT.delete_TGA(
p_terr_grp_acct_id => tga_rec.terr_group_account_id,
p_terr_group_id => tga_rec.terr_group_id,
p_catchall_terr_id =>-1,
p_change_type =>'SALES_TEAM_UPDATE'
);
/* Delete the named account resources for the from party */
DELETE FROM jtf_tty_named_acct_rsc
WHERE terr_group_account_id = tga_rec.terr_group_account_id;
/* Delete the terr_group accounts for the from party */
DELETE FROM jtf_tty_terr_grp_accts
WHERE terr_group_account_id = tga_rec.terr_group_account_id;
UPDATE jtf_tty_acct_qual_maps
SET named_account_id = p_to_na_id
WHERE named_account_id = p_from_na_id;
UPDATE jtf_tty_terr_grp_accts jtga
SET jtga.named_account_id = p_to_na_id
WHERE jtga.terr_group_account_id = tga_rec.terr_group_account_id;
SELECT 'Y'
INTO l_acct_qual_maps_exist
FROM jtf_tty_acct_qual_maps
WHERE named_account_id = p_to_na_id
AND rownum < 2;
UPDATE jtf_tty_acct_qual_maps
SET named_account_id = p_to_na_id
WHERE named_account_id = p_from_na_id;
/* Do delete from jtf_tty_named_accts and acct_qual_maps after all processing is done */
DELETE FROM jtf_tty_acct_qual_maps
WHERE named_account_id = p_from_na_id;
DELETE FROM jtf_tty_named_accts
WHERE party_id = l_from_party_id
AND party_site_id = p_from_fk_id;
SELECT hzps.party_id
INTO l_to_party_id
FROM hz_party_sites hzps
WHERE hzps.party_site_id = p_to_fk_id
AND rownum < 2;
SELECT 'Y'
INTO l_to_party_na_exists
FROM jtf_tty_terr_grp_accts tga,
jtf_tty_named_accts jna
WHERE jna.named_account_id = tga.named_account_id
AND jna.party_id = l_to_party_id
AND tga.terr_group_id = tga_rec.terr_group_id
AND rownum < 2;
/* Delete records from JTF_TERR_... tables corresponding to the from named account */
JTF_TTY_GEN_TERR_PVT.delete_TGA(
p_terr_grp_acct_id => tga_rec.terr_group_account_id,
p_terr_group_id => tga_rec.terr_group_id,
p_catchall_terr_id =>-1,
p_change_type =>'SALES_TEAM_UPDATE'
);
/* Delete the named account resources for the from party */
DELETE FROM jtf_tty_named_acct_rsc
WHERE terr_group_account_id = tga_rec.terr_group_account_id;
/* Delete the terr_group accounts for the from party */
DELETE FROM jtf_tty_terr_grp_accts
WHERE terr_group_account_id = tga_rec.terr_group_account_id;
DELETE FROM jtf_tty_acct_qual_maps
WHERE named_account_id = p_from_na_id;
DELETE FROM jtf_tty_acct_qual_maps
WHERE named_account_id = p_from_na_id;
SELECT 'Y'
INTO l_to_na_exists
FROM jtf_tty_named_accts
WHERE party_id = l_to_party_id
AND party_site_id = p_to_fk_id;
UPDATE jtf_tty_named_accts
SET party_id = l_to_party_id,
party_site_id = p_to_fk_id
WHERE party_id = l_from_party_id
AND party_site_id = p_from_fk_id;
/* Do delete from jtf_tty_named_accts and acct_qual_maps after all processing is done */
DELETE FROM jtf_tty_named_accts
WHERE party_id = l_from_party_id
AND party_site_id = p_from_fk_id;