[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hzp.party_id
FROM hz_parties hzp
, hz_party_sites hzps
WHERE hzp.party_number = l_party_number
AND hzp.party_id = hzps.party_id
and hzps.party_site_id = l_party_site_id
AND hzp.status = 'A';
SELECT a.terr_group_id,
a.active_from_date,
NVL( a.active_to_date, ADD_MONTHS(a.active_from_date,120) ) active_to_date
FROM jtf_tty_terr_groups a, jtf_terr_all b
WHERE a.parent_terr_id = b.terr_id
AND upper(a.terr_group_name) = upper(l_tg_name)
-- and b.org_id = FND_PROFILE.VALUE('ORG_ID')
AND a.self_service_type = 'NAMED_ACCOUNT'
AND a.active_from_date <= sysdate
AND (a.active_to_date is null or a.active_to_date >= sysdate)
AND rownum < 2;
SELECT na.named_account_id, tga.terr_group_account_id
FROM jtf_tty_named_accts na, jtf_tty_terr_grp_accts tga
WHERE na.party_id = l_party_id
AND na.party_site_id = l_party_site_id
AND tga.named_account_id = na.named_account_id
AND tga.terr_group_id = l_tg_id;
update jtf_tty_terr_grp_accts
set ATTRIBUTE1 = P_ATTRIBUTE1,
ATTRIBUTE2 = P_ATTRIBUTE2,
ATTRIBUTE3 = P_ATTRIBUTE3,
ATTRIBUTE4 = P_ATTRIBUTE4,
ATTRIBUTE5 = P_ATTRIBUTE5,
ATTRIBUTE6 = P_ATTRIBUTE6,
ATTRIBUTE7 = P_ATTRIBUTE7,
ATTRIBUTE8 = P_ATTRIBUTE8,
ATTRIBUTE9 = P_ATTRIBUTE9,
ATTRIBUTE10 = P_ATTRIBUTE10,
ATTRIBUTE11 = P_ATTRIBUTE11,
ATTRIBUTE12 = P_ATTRIBUTE12,
ATTRIBUTE13 = P_ATTRIBUTE13,
ATTRIBUTE14 = P_ATTRIBUTE14,
ATTRIBUTE15 = P_ATTRIBUTE15,
START_DATE = p_start_date,
END_DATE = p_end_date
where terr_group_account_id = l_tga_id
and terr_group_id = l_tg_id;
PROCEDURE DELETE_ACCT_FROM_TG(
P_API_VERSION_NUMBER IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_VALIDATION_LEVEL IN NUMBER,
P_TERR_GRP_ACCT_ID IN NUMBER,
P_TERR_GRP_ID IN NUMBER,
P_NAMED_ACCT_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_user_id NUMBER;
DELETE from jtf_tty_named_acct_rsc
WHERE terr_group_account_id = p_terr_grp_acct_id;
DELETE from JTF_TTY_TERR_GRP_ACCTS
WHERE terr_group_account_id = p_terr_grp_acct_id;
DELETE from JTF_TTY_NAMED_ACCTS
WHERE named_account_id = P_NAMED_ACCT_ID
AND NOT EXISTS (SELECT named_account_id
from JTF_TTY_TERR_GRP_ACCTS a
where a.named_account_id = P_NAMED_ACCT_ID);
DELETE from JTF_TTY_ACCT_QUAL_MAPS
WHERE named_account_id = P_NAMED_ACCT_ID
AND NOT EXISTS (SELECT named_account_id
from JTF_TTY_NAMED_ACCTS a
where a.named_account_id = P_NAMED_ACCT_ID); --Delete named account if it has no other TG references
/*--Insert row to track changes for GTP
INSERT INTO jtf_tty_named_acct_changes
(NAMED_ACCT_CHANGE_ID,
OBJECT_VERSION_NUMBER,
OBJECT_TYPE,
OBJECT_ID,
CHANGE_TYPE,
FROM_WHERE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (JTF_TTY_NAMED_ACCT_CHANGES_S.NEXTVAL,
1,
'TGA',
p_terr_grp_acct_id,
'DELETE',
'DELETE NA',
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id);
JTF_TTY_GEN_TERR_PVT.delete_TGA(
p_terr_grp_acct_id =>p_terr_grp_acct_id,
p_terr_group_id =>p_terr_grp_id,
p_catchall_terr_id =>-1,
p_change_type =>'SALES_TEAM_UPDATE'
);
put_jty_log('Error in DELETE_ACCT_FROM_TG: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
END DELETE_ACCT_FROM_TG;
SELECT 'Y' INTO l_chk_done FROM DUAL
WHERE EXISTS (SELECT 'Y'
FROM hz_relationships hzr
WHERE hzr.subject_table_name = 'HZ_PARTIES'
AND hzr.object_table_name = 'HZ_PARTIES'
AND hzr.relationship_type = 'GLOBAL_ULTIMATE'
AND hzr.relationship_code = 'GLOBAL_ULTIMATE_OF'
AND hzr.status = 'A'
AND sysdate between hzr.start_date and nvl(hzr.end_date, sysdate)
AND hzr.subject_id = p_party_id );
SELECT 'Y' INTO l_chk_done FROM DUAL
WHERE EXISTS (SELECT 'Y'
FROM hz_relationships hzr
WHERE hzr.subject_table_name = 'HZ_PARTIES'
AND hzr.object_table_name = 'HZ_PARTIES'
AND hzr.relationship_type = 'DOMESTIC_ULTIMATE'
AND hzr.relationship_code = 'DOMESTIC_ULTIMATE_OF'
AND hzr.status = 'A'
AND sysdate between hzr.start_date and nvl(hzr.end_date, sysdate)
AND hzr.subject_id = p_party_id );
SELECT lkp.lookup_code INTO l_site_type_code
FROM fnd_lookups lkp, hz_parties hzp
WHERE lkp.lookup_type = 'JTF_TTY_SITE_TYPE_CODE'
AND hzp.hq_branch_ind = lkp.lookup_code
AND hzp.party_id = p_party_id;
SELECT H3.party_name,
H3.known_as,
H1.postal_code
INTO l_business_name,
l_trade_name,
l_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(l_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(l_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,
'=',
l_postal_code,
null,
p_user_id,
sysdate,
p_user_id,
sysdate
FROM DUAL);
SELECT hzp.party_id, hzp.duns_number_c
FROM hz_parties hzp
, hz_party_sites hzps
WHERE hzp.party_number = l_party_number
AND hzp.party_id = hzps.party_id
and hzps.party_site_id = l_party_site_id
AND hzp.status = 'A';
SELECT a.terr_group_id, a.matching_rule_code,
a.active_from_date,
NVL( a.active_to_date, ADD_MONTHS(a.active_from_date,120) ) active_to_date
FROM jtf_tty_terr_groups a, jtf_terr_all b
WHERE a.parent_terr_id = b.terr_id
AND upper(a.terr_group_name) like upper(l_tg_name)
AND a.self_service_type = 'NAMED_ACCOUNT'
AND a.active_from_date <= sysdate
AND (a.active_to_date is null or a.active_to_date >= sysdate)
AND rownum < 2;
SELECT na.named_account_id, tga.terr_group_account_id
FROM jtf_tty_named_accts na, jtf_tty_terr_grp_accts tga
WHERE na.party_id = l_party_id
AND na.party_site_id = l_party_site_id
AND tga.named_account_id = na.named_account_id
AND tga.terr_group_id = l_tg_id;
SELECT named_account_id, mapping_complete_flag
FROM jtf_tty_named_accts
WHERE party_id = l_party_id
and party_site_id = l_party_site_id ;
SELECT jtf_tty_named_accts_s.nextval INTO l_na_id FROM dual;
INSERT INTO jtf_tty_named_accts
(NAMED_ACCOUNT_ID,
OBJECT_VERSION_NUMBER,
PARTY_ID,
PARTY_SITE_ID,
MAPPING_COMPLETE_FLAG,
SITE_TYPE_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES
(l_na_id,
2,
l_party_id,
p_party_site_id,
l_mapping_flag,
l_site_type_code,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id);
SELECT jtf_tty_terr_grp_accts_s.nextval INTO l_tga_id FROM dual;
INSERT INTO jtf_tty_terr_grp_accts
(TERR_GROUP_ACCOUNT_ID,
OBJECT_VERSION_NUMBER,
TERR_GROUP_ID,
NAMED_ACCOUNT_ID,
DN_JNA_MAPPING_COMPLETE_FLAG,
DN_JNA_SITE_TYPE_CODE,
DN_JNR_ASSIGNED_FLAG,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
START_DATE,
END_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (l_tga_id,
2,
l_tg_id,
l_na_id,
l_mapping_flag,
l_site_type_code,
'N',
P_ATTRIBUTE1,
P_ATTRIBUTE2,
P_ATTRIBUTE3,
P_ATTRIBUTE4,
P_ATTRIBUTE5,
P_ATTRIBUTE6,
P_ATTRIBUTE7,
P_ATTRIBUTE8,
P_ATTRIBUTE9,
P_ATTRIBUTE10,
P_ATTRIBUTE11,
P_ATTRIBUTE12,
P_ATTRIBUTE13,
P_ATTRIBUTE14,
P_ATTRIBUTE15,
P_START_DATE,
P_END_DATE,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id);
/*--Insert row to track changes for GTP
INSERT INTO jtf_tty_named_acct_changes
(NAMED_ACCT_CHANGE_ID,
OBJECT_VERSION_NUMBER,
OBJECT_TYPE,
OBJECT_ID,
CHANGE_TYPE,
FROM_WHERE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (JTF_TTY_NAMED_ACCT_CHANGES_S.NEXTVAL,
1,
'TG',
l_tg_id,
'UPDATE',
'UPDATE TERRITORY GROUP',
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id);
SELECT rsc_group_id, resource_id, rsc_role_code, rsc_resource_type
FROM jtf_tty_terr_grp_owners
WHERE terr_group_id = l_tg_id;
INSERT INTO jtf_tty_named_acct_rsc
(ACCOUNT_RESOURCE_ID,
OBJECT_VERSION_NUMBER,
TERR_GROUP_ACCOUNT_ID,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_ROLE_CODE,
ASSIGNED_FLAG,
RSC_RESOURCE_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(SELECT jtf_tty_named_acct_rsc_s.nextval,
1,
p_terr_grp_acct_id,
l_resource_id,
l_rsc_group_id,
l_rsc_role_code,
'N',
l_rsc_resource_type,
l_user_id,
sysdate,
l_user_id,
sysdate
FROM dual
WHERE NOT EXISTS (SELECT null FROM jtf_tty_named_acct_rsc rsc
WHERE rsc.terr_group_account_id = p_terr_grp_acct_id
AND rsc.RESOURCE_ID = l_resource_id
AND rsc.RSC_ROLE_CODE = l_rsc_role_code
AND rsc.RSC_GROUP_ID = l_rsc_group_id
AND rsc.RSC_RESOURCE_TYPE = l_rsc_resource_type));
l_select varchar2(10);
SELECT RESOURCE_id
FROM jtf_rs_resource_extns_vl
WHERE upper(resource_name) = upper(c_resource_name)
AND category = 'EMPLOYEE'
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE+1);
SELECT group_id
FROM jtf_rs_groups_vl
WHERE upper(group_name) = upper(c_group_name)
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE+1);
SELECT rol.role_code
FROM jtf_rs_roles_vl rol
WHERE upper(rol.role_name) = upper(c_role_name)
AND (rol.role_type_code = 'SALES' OR
rol.role_type_code = 'TELESALES' OR
rol.role_type_code = 'FIELDSALES')
AND active_flag ='Y';
SELECT 'VALID' INTO l_select
FROM jtf_tty_terr_grp_accts tga,
jtf_tty_named_acct_rsc nar,
jtf_tty_terr_groups tg
WHERE nar.terr_group_account_id = tga.terr_group_account_id
AND nar.rsc_role_code = X_ROLE_CODE
AND tga.terr_group_id = tg.terr_group_id
AND sysdate >= tg.active_from_date
AND (tg.active_to_date is null OR
sysdate <= tg.active_to_date)
AND nar.resource_id = l_res_tbl(i)
AND nar.rsc_group_id = X_GROUP_ID
AND tga.named_account_id = P_NAMED_ACCOUNT_ID
AND tga.terr_group_id <>P_terr_group_id
AND rownum < 2;
SELECT 'VALID'
INTO l_select
FROM jtf_rs_group_members mem, jtf_rs_roles_b rol, jtf_rs_role_relations rlt
WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.delete_flag = 'N'
AND sysdate >= rlt.start_date_active
AND (rlt.end_date_active is null OR
sysdate <= rlt.end_date_active)
AND rlt.role_id = rol.role_id
AND rol.role_code = x_role_code
AND rlt.role_resource_id = mem.group_member_id
AND mem.delete_flag = 'N'
AND mem.group_id = x_group_id
AND mem.resource_id = l_res_tbl(i);
SELECT 'Y'
INTO l_select
FROM jtf_tty_terr_grp_roles
WHERE terr_group_id=P_terr_group_id
AND role_code = X_ROLE_CODE;
l_select varchar2(100);
SELECT 'VALID'
INTO l_select
FROM jtf_tty_srch_my_resources_v /*jtf_tty_my_resources_v*/ grv,
jtf_tty_terr_grp_owners jto,
jtf_rs_resource_extns res
WHERE EXISTS
( SELECT NULL
FROM jtf_rs_groups_denorm grpd
WHERE /* part of Salesgroup hierarchy of Territory Group owner */
grpd.parent_group_id = JTO.rsc_group_id
/* groups I (logged-in user) am 'member' of */
AND grpd.group_id = GRV.group_id
)
AND jto.terr_group_id = P_TG_ID
AND grv.ROLE_CODE = P_ROLE_CODE
AND grv.GROUP_ID = P_GROUP_ID
AND grv.resource_id = P_RESOURCE_ID
AND grv.CURRENT_USER_ID = res.USER_ID
AND jto.resource_id = res.resource_id
AND ROWNUM < 2;
l_select varchar2(100);
SELECT jto.resource_id, jto.rsc_group_id, jto.rsc_role_code, grpd.group_id
FROM jtf_rs_grp_denorm_vl grpd, jtf_tty_terr_grp_owners jto
WHERE grpd.parent_group_id = JTO.rsc_group_id
AND jto.terr_group_id = P_TERR_GP_ID
AND grpd.group_id IN (p_group_id1, p_group_id2, p_group_id3, p_group_id4, p_group_id5,
p_group_id6, p_group_id7, p_group_id8, p_group_id9, p_group_id10,
p_group_id11, p_group_id12, p_group_id13, p_group_id14, p_group_id15,
p_group_id16, p_group_id17, p_group_id18, p_group_id19, p_group_id20,
p_group_id21, p_group_id22, p_group_id23, p_group_id24, p_group_id25,
p_group_id26, p_group_id27, p_group_id28, p_group_id29, p_group_id30);
'SELECT jto.resource_id, jto.rsc_group_id, jto.rsc_role_code, grpd.group_id' ||
' FROM jtf_rs_grp_denorm_vl grpd, jtf_tty_terr_grp_owners jto ' ||
' WHERE grpd.parent_group_id = JTO.rsc_group_id' ||
' AND jto.terr_group_id = :P_TERR_GP_ID ' ||
' AND grpd.group_id IN (' || l_rsc_groups || ')';
select resource_id,
rsc_group_id,
rsc_role_code
from jtf_tty_terr_grp_owners
where terr_group_id = id;
SELECT 'X'
INTO p_exists_flag
FROM jtf_tty_terr_grp_accts ga,
jtf_tty_my_resources_v repdn,
jtf_tty_named_acct_rsc narsc
WHERE ga.terr_group_account_id = narsc.terr_group_account_id
AND narsc.resource_id = repdn.resource_id
AND narsc.rsc_group_id = repdn.group_id
AND narsc.rsc_role_code = repdn.role_code
AND repdn.parent_group_id = p_owner_group_id
AND repdn.current_user_rsc_id = p_owner_user_rsc_id
AND repdn.current_user_role_code = p_owner_role_code
AND ga.terr_group_id = p_terr_gp_id
AND ga.terr_group_account_id = p_terr_gp_acct_id
AND ROWNUM < 2;
INSERT INTO jtf_tty_named_acct_rsc(
ACCOUNT_RESOURCE_ID,
OBJECT_VERSION_NUMBER,
TERR_GROUP_ACCOUNT_ID,
RESOURCE_ID,
RSC_GROUP_ID ,
RSC_ROLE_CODE ,
RSC_RESOURCE_TYPE ,
ASSIGNED_FLAG ,
CREATED_BY,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN )
VALUES(jtf_tty_named_acct_rsc_s.nextval,
1,
p_terr_gp_acct_id,
p_owner_user_rsc_id,
p_owner_group_id,
p_owner_role_code,
'RS_EMPLOYEE',
'N',
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
null);
SELECT mgr.resource_id, mgr.group_id
FROM jtf_rs_rep_managers mgr,
jtf_rs_groups_denorm gd
WHERE mgr.hierarchy_type = 'MGR_TO_MGR'
AND mgr.resource_id = mgr.parent_resource_id
AND trunc(sysdate) BETWEEN mgr.start_date_active
AND NVL(mgr.end_date_active,trunc(sysdate))
AND mgr.group_id = gd.group_id
AND gd.parent_group_id = cl_group_id
AND mgr.resource_id = cl_parent_resource_id
AND rownum < 2;
SELECT DISTINCT RESOURCE_ID
FROM JTF_RS_REP_MANAGERS
WHERE group_id = cl_group_id
and resource_id <> cl_parent_resource_id
and parent_resource_id = cl_parent_resource_id;
select distinct gao.terr_group_account_id
from hz_relationships hzr,
jtf_tty_named_accts nai,
jtf_tty_terr_grp_accts gai,
jtf_tty_named_accts nao,
jtf_tty_terr_grp_accts gao
where gao.named_account_id = nao.named_account_id
and nao.party_id = hzr.object_id -- these are the subsidiary parties
and hzr.subject_table_name = 'HZ_PARTIES'
and hzr.object_table_name = 'HZ_PARTIES'
and hzr.relationship_code IN ( 'GLOBAL_ULTIMATE_OF', 'HEADQUARTERS_OF', 'DOMESTIC_ULTIMATE_OF', 'PARENT_OF' )
and hzr.status = 'A'
and sysdate between hzr.start_date and nvl( hzr.end_date, sysdate)
and hzr.subject_id = nai.party_id -- this is the parent party
and nai.named_account_id = gai.named_account_id
and gai.terr_group_account_id = cl_terr_group_account_id
-- subsidiaries that are owned by user
and exists( select 'Y'
from jtf_tty_named_acct_rsc narsc ,
jtf_tty_my_resources_v repdn
-- jtf_tty_named_accts na,
-- jtf_tty_terr_grp_accts ga
where narsc.terr_group_account_id = gao.terr_group_account_id
-- and ga.named_account_id = na.named_account_id
and narsc.resource_id = repdn.resource_id
and narsc.rsc_group_id = repdn.group_id
and repdn.current_user_id = l_user_id );
select grv.resource_id, grv.group_id, grv.role_code
from
jtf_tty_my_resources_v grv
, JTF_RS_GROUPS_DENORM grpd
, jtf_rs_roles_b rol
WHERE grpd.parent_group_id = grv.parent_group_id
and grpd.group_id IN ( select grv1.group_id
from jtf_rs_group_members grv1
where grv1.resource_id = cl_eff_resource_id )
and grv.CURRENT_USER_ID = cl_current_user_id
and grv.group_id = grv.parent_group_id
and grv.role_code = rol.role_code
and rol.manager_flag = 'Y';
**** if ASSIGN_SUBSIDIARIES has been selected for any resource
************************************************************/
l_user := fnd_global.USER_ID;
/* JRADHAKR: Inserting values to jtf_tty_named_acct_changes table for GTP
to do an incremental and Total Mode */
/* by shli, GSST Decom */
/*
select jtf_tty_named_acct_changes_s.nextval
into l_change_id
from sys.dual;
insert into jtf_tty_named_acct_changes
( NAMED_ACCT_CHANGE_ID
, OBJECT_VERSION_NUMBER
, OBJECT_TYPE
, OBJECT_ID
, CHANGE_TYPE
, FROM_WHERE
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
)
VALUES (
l_change_id
, 1
, 'TGA'
, l_terr_group_account_id
, 'UPDATE'
, 'UPDATE SALES TEAM'
, l_user
, sysdate
, l_user
, sysdate
, l_login_id
);
delete from jtf_tty_named_acct_rsc
where rsc_group_id = lp_group_id
and rsc_role_code = lp_role_code
and terr_group_account_id = l_terr_group_account_id
and resource_id = lp_resource_id;
select count(*) INTO l_directs_on_account
from jtf_tty_named_acct_rsc ar
where ar.terr_group_account_id = l_terr_group_account_id
and exists (
select 1
from jtf_tty_my_resources_v grv
, jtf_rs_groups_denorm grpd
WHERE ar.resource_id = grv.resource_id
and grpd.parent_group_id = grv.parent_group_id
and exists (
select 1
from jtf_rs_group_members grv1
where grpd.group_id = grv1.group_id
and grv1.resource_id = lp_resource_id )
and grv.CURRENT_USER_ID = l_user_id )
and rownum < 2;
select jtf_tty_named_acct_rsc_s.nextval into new_seq_acct_rsc_id
from dual;
insert into jtf_tty_named_acct_rsc (
account_resource_id,
object_version_number,
terr_group_account_id,
resource_id,
rsc_group_id,
rsc_role_code,
assigned_flag,
rsc_resource_type,
created_by,
creation_date,
last_updated_by,
last_update_date
)
VALUES (
new_seq_acct_rsc_id, --account_resource_id,
2, --object_version_number
l_terr_group_account_id, --terr_group_account_id
lp_mgr_resource_id, --resource_id,
lp_mgr_group_id, --rsc_group_id,
lp_mgr_role_code, --rsc_role_code,
'N', --assigned_flag,
lp_user_resource_type, --rsc_resource_type
1, --created_by
sysdate, --creation_date
1, --last_updated_by
sysdate --last_update_date
);
DELETE FROM JTF_TTY_NAMED_ACCT_RSC
WHERE rsc_role_code = lp_role_code
AND terr_group_account_id = l_terr_group_account_id
AND resource_id = crd.resource_id;
select count(*) into l_acct_rsc_exist_count
from (
select account_resource_id
from jtf_tty_named_acct_rsc
where resource_id = lp_resource_id
and rsc_group_id = lp_group_id
and rsc_role_code = lp_role_code
and terr_group_account_id = l_terr_group_account_id
and assigned_flag = 'Y' -- still need a Y assign flag on NA/RSC to abort addition.
and rownum < 2
);
select count(*) into l_acct_rsc_exist_count
from (
select account_resource_id
from jtf_tty_named_acct_rsc
where resource_id = lp_resource_id
and rsc_group_id = lp_group_id
and rsc_role_code = lp_role_code
and terr_group_account_id = l_terr_group_account_id
-- and assigned_flag = 'Y' bug 2803830
and rownum < 2
);
select jtf_tty_named_acct_rsc_s.nextval into new_seq_acct_rsc_id
from dual;
insert into jtf_tty_named_acct_rsc (
account_resource_id,
object_version_number,
terr_group_account_id,
resource_id,
rsc_group_id,
rsc_role_code,
assigned_flag,
rsc_resource_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
START_DATE,
END_DATE
)
VALUES (
new_seq_acct_rsc_id, --account_resource_id,
2, --object_version_number
l_terr_group_account_id, --terr_group_account_id
lp_resource_id, --resource_id,
lp_group_id, --rsc_group_id,
lp_role_code, --rsc_role_code,
l_assign_flag, --assigned_flag,
lp_resource_type, --rsc_resource_type
1, --created_by
sysdate, --creation_date
1, --last_updated_by
sysdate --last_update_date
,p_added_rscs_tbl(i).RESOURCE_ATT1
,p_added_rscs_tbl(i).RESOURCE_ATT2
,p_added_rscs_tbl(i).RESOURCE_ATT3
,p_added_rscs_tbl(i).RESOURCE_ATT4
,p_added_rscs_tbl(i).RESOURCE_ATT5
,p_added_rscs_tbl(i).RESOURCE_START_DATE
,p_added_rscs_tbl(i).RESOURCE_END_DATE
);
/* JRADHAKR: Inserting values to jtf_tty_named_acct_changes table for GTP
to do an incremental and Total Mode */
/* by shli, GSST Decom */
/* select jtf_tty_named_acct_changes_s.nextval
into l_change_id
from sys.dual;
insert into jtf_tty_named_acct_changes
( NAMED_ACCT_CHANGE_ID
, OBJECT_VERSION_NUMBER
, OBJECT_TYPE
, OBJECT_ID
, CHANGE_TYPE
, FROM_WHERE
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
)
VALUES (
l_change_id
, 1
, 'TGA'
, l_terr_group_account_id
, 'UPDATE'
, 'UPDATE SALES TEAM'
, l_user
, sysdate
, l_user
, sysdate
, l_login_id
);
delete from jtf_tty_named_acct_rsc
where 1=1
--and rsc_group_id = lp_group_id
--and rsc_role_code = lp_role_code
and terr_group_account_id = l_terr_group_account_id
and resource_id = lp_mgr_resource_id
and assigned_flag = 'N';
-- add 06/05/2006 bug 5246668, update resource attributes
update jtf_tty_named_acct_rsc
set attribute1 = p_added_rscs_tbl(i).RESOURCE_ATT1,
attribute2 = p_added_rscs_tbl(i).RESOURCE_ATT2,
attribute3 = p_added_rscs_tbl(i).RESOURCE_ATT3,
attribute4 = p_added_rscs_tbl(i).RESOURCE_ATT4,
attribute5 = p_added_rscs_tbl(i).RESOURCE_ATT5,
start_date = p_added_rscs_tbl(i).RESOURCE_START_DATE,
end_date = p_added_rscs_tbl(i).RESOURCE_END_DATE
where resource_id = lp_resource_id
and rsc_group_id = lp_group_id
and rsc_role_code = lp_role_code
and terr_group_account_id = l_terr_group_account_id;
/* Procedure to update the sales team assignments for an account
* It gets invoked by populate_admin_excel_data for update sales team
* it validates the salespersons, checks if they can be added then
* delete and addd salespersons, if needed and assign to owners of tg if
* needed.
*/
PROCEDURE UPDATE_SALES_TEAM (
p_Api_Version_Number IN NUMBER,
p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
p_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
p_added_rscs_tbl IN SALESREP_RSC_TBL_TYPE,
P_TERR_GP_ID IN NUMBER ,
P_TERR_GP_ACCT_ID IN NUMBER ,
P_NAMED_ACCT_ID IN NUMBER,
P_SALES_GROUP IN NUMBER,
P_SALES_ROLE IN VARCHAR2)
AS
i integer:=0;
IS select RESOURCE_ID, RSC_GROUP_ID , RSC_ROLE_CODE
from jtf_tty_named_acct_rsc
where TERR_GROUP_ACCOUNT_ID = l_terr_grp_acct_id
and (l_sales_group is null
or RSC_GROUP_ID in
(select group_id
from jtf_rs_groups_denorm
where parent_group_id = l_sales_group))
and (l_sales_role is null
or RSC_ROLE_CODE = l_sales_role);
l_delete_count NUMBER := 0;
select resource_id,
rsc_group_id,
rsc_role_code,
'N' delete_flag
from jtf_tty_terr_grp_owners
where terr_group_id = l_tg_id;
l_owners_tbl(j).delete_flag := 'N';
-- dbms_output.put_line('In update sales team, valid person flag: true');
l_owners_tbl(m).delete_flag := 'Y';
select ASSIGNED_FLAG
into l_assign_flag
from jtf_tty_named_acct_rsc
where TERR_GROUP_ACCOUNT_ID = P_TERR_GP_ACCT_ID
and RESOURCE_ID = l_added_rscs_tbl(j).Resource_id
and RSC_GROUP_ID = l_added_rscs_tbl(j).group_id
and RSC_ROLE_CODE = l_added_rscs_tbl(j).role_code
and RSC_RESOURCE_TYPE = 'RS_EMPLOYEE';
put_jty_log('Error in UPDATE_SALES_TEAM: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
if (l_owners_tbl(i).delete_flag = 'N') THEN
-- dbms_output.put_line ('In owner tbl');
l_delete_count := l_delete_count +1;
l_removed_rscs_tbl(l_delete_count).resource_id := c_res.Resource_id;
l_removed_rscs_tbl(l_delete_count).group_id := c_res.RSC_GROUP_ID;
l_removed_rscs_tbl(l_delete_count).role_code := c_res.RSC_ROLE_CODE;
l_removed_rscs_tbl(l_delete_count).attribute1 := 'N';
l_removed_rscs_tbl(l_delete_count).mgr_resource_id := -999;
l_removed_rscs_tbl(l_delete_count).mgr_group_id := -999;
l_removed_rscs_tbl(l_delete_count).mgr_role_code := '-999';
l_removed_rscs_tbl(l_delete_count).resource_type := 'RS_EMPLOYEE';
put_jty_log('Error in UPDATE_SALES_TEAM: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
put_jty_log('Error in UPDATE_SALES_TEAM: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
END UPDATE_SALES_TEAM;
/* Procedure called during Add to Org with Update Sales Team and Transfer to TG with Update Sales Team
* It makes the sales team assignments for the accounts
* APIs called: Populate Sales Team
*/
PROCEDURE ADD_SALES_TEAM (
p_Api_Version_Number IN NUMBER,
p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
p_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
p_added_rscs_tbl IN SALESREP_RSC_TBL_TYPE,
P_TERR_GP_ID IN NUMBER ,
P_TERR_GP_ACCT_ID IN NUMBER ,
P_NAMED_ACCT_ID IN NUMBER)
AS
errbuf varchar2(2000);
l_delete_count NUMBER := 0;
select resource_id,
rsc_group_id,
rsc_role_code,
'N' delete_flag
from jtf_tty_terr_grp_owners
where terr_group_id = l_tg_id;
l_owners_tbl(j).delete_flag := 'N';
l_owners_tbl(m).delete_flag := 'Y';
if (l_owners_tbl(i).delete_flag = 'N') THEN
l_add_rscs_tbl.extend;
P_DELETE_FLAG IN VARCHAR2,
P_RESOURCE1_NAME IN VARCHAR2,
P_GROUP1_NAME IN VARCHAR2,
P_ROLE1_NAME IN VARCHAR2,
P_RESOURCE2_NAME IN VARCHAR2,
P_GROUP2_NAME IN VARCHAR2,
P_ROLE2_NAME IN VARCHAR2,
P_RESOURCE3_NAME IN VARCHAR2,
P_GROUP3_NAME IN VARCHAR2,
P_ROLE3_NAME IN VARCHAR2,
P_RESOURCE4_NAME IN VARCHAR2,
P_GROUP4_NAME IN VARCHAR2,
P_ROLE4_NAME IN VARCHAR2,
P_RESOURCE5_NAME IN VARCHAR2,
P_GROUP5_NAME IN VARCHAR2,
P_ROLE5_NAME IN VARCHAR2,
P_RESOURCE6_NAME IN VARCHAR2,
P_GROUP6_NAME IN VARCHAR2,
P_ROLE6_NAME IN VARCHAR2,
P_RESOURCE7_NAME IN VARCHAR2,
P_GROUP7_NAME IN VARCHAR2,
P_ROLE7_NAME IN VARCHAR2,
P_RESOURCE8_NAME IN VARCHAR2,
P_GROUP8_NAME IN VARCHAR2,
P_ROLE8_NAME IN VARCHAR2,
P_RESOURCE9_NAME IN VARCHAR2,
P_GROUP9_NAME IN VARCHAR2,
P_ROLE9_NAME IN VARCHAR2,
P_RESOURCE10_NAME IN VARCHAR2,
P_GROUP10_NAME IN VARCHAR2,
P_ROLE10_NAME IN VARCHAR2,
P_RESOURCE11_NAME IN VARCHAR2,
P_GROUP11_NAME IN VARCHAR2,
P_ROLE11_NAME IN VARCHAR2,
P_RESOURCE12_NAME IN VARCHAR2,
P_GROUP12_NAME IN VARCHAR2,
P_ROLE12_NAME IN VARCHAR2,
P_RESOURCE13_NAME IN VARCHAR2,
P_GROUP13_NAME IN VARCHAR2,
P_ROLE13_NAME IN VARCHAR2,
P_RESOURCE14_NAME IN VARCHAR2,
P_GROUP14_NAME IN VARCHAR2,
P_ROLE14_NAME IN VARCHAR2,
P_RESOURCE15_NAME IN VARCHAR2,
P_GROUP15_NAME IN VARCHAR2,
P_ROLE15_NAME IN VARCHAR2,
P_RESOURCE16_NAME IN VARCHAR2,
P_GROUP16_NAME IN VARCHAR2,
P_ROLE16_NAME IN VARCHAR2,
P_RESOURCE17_NAME IN VARCHAR2,
P_GROUP17_NAME IN VARCHAR2,
P_ROLE17_NAME IN VARCHAR2,
P_RESOURCE18_NAME IN VARCHAR2,
P_GROUP18_NAME IN VARCHAR2,
P_ROLE18_NAME IN VARCHAR2,
P_RESOURCE19_NAME IN VARCHAR2,
P_GROUP19_NAME IN VARCHAR2,
P_ROLE19_NAME IN VARCHAR2,
P_RESOURCE20_NAME IN VARCHAR2,
P_GROUP20_NAME IN VARCHAR2,
P_ROLE20_NAME IN VARCHAR2,
P_RESOURCE21_NAME IN VARCHAR2,
P_GROUP21_NAME IN VARCHAR2,
P_ROLE21_NAME IN VARCHAR2,
P_RESOURCE22_NAME IN VARCHAR2,
P_GROUP22_NAME IN VARCHAR2,
P_ROLE22_NAME IN VARCHAR2,
P_RESOURCE23_NAME IN VARCHAR2,
P_GROUP23_NAME IN VARCHAR2,
P_ROLE23_NAME IN VARCHAR2,
P_RESOURCE24_NAME IN VARCHAR2,
P_GROUP24_NAME IN VARCHAR2,
P_ROLE24_NAME IN VARCHAR2,
P_RESOURCE25_NAME IN VARCHAR2,
P_GROUP25_NAME IN VARCHAR2,
P_ROLE25_NAME IN VARCHAR2,
P_RESOURCE26_NAME IN VARCHAR2,
P_GROUP26_NAME IN VARCHAR2,
P_ROLE26_NAME IN VARCHAR2,
P_RESOURCE27_NAME IN VARCHAR2,
P_GROUP27_NAME IN VARCHAR2,
P_ROLE27_NAME IN VARCHAR2,
P_RESOURCE28_NAME IN VARCHAR2,
P_GROUP28_NAME IN VARCHAR2,
P_ROLE28_NAME IN VARCHAR2,
P_RESOURCE29_NAME IN VARCHAR2,
P_GROUP29_NAME IN VARCHAR2,
P_ROLE29_NAME IN VARCHAR2,
P_RESOURCE30_NAME IN VARCHAR2,
P_GROUP30_NAME IN VARCHAR2,
P_ROLE30_NAME IN VARCHAR2,
P_PARTY_SITE_ID IN VARCHAR2,
P_SALES_GROUP IN VARCHAR2,
P_SALES_ROLE IN VARCHAR2,
P_PHONETIC_NAME IN VARCHAR2,
P_IDENTIFYING_ADDRESS IN VARCHAR2,
P_RES1_ATT1 IN VARCHAR2,
P_RES2_ATT1 IN VARCHAR2,
P_RES3_ATT1 IN VARCHAR2,
P_RES4_ATT1 IN VARCHAR2,
P_RES5_ATT1 IN VARCHAR2,
P_RES6_ATT1 IN VARCHAR2,
P_RES7_ATT1 IN VARCHAR2,
P_RES8_ATT1 IN VARCHAR2,
P_RES9_ATT1 IN VARCHAR2,
P_RES10_ATT1 IN VARCHAR2,
P_RES11_ATT1 IN VARCHAR2,
P_RES12_ATT1 IN VARCHAR2,
P_RES13_ATT1 IN VARCHAR2,
P_RES14_ATT1 IN VARCHAR2,
P_RES15_ATT1 IN VARCHAR2,
P_RES16_ATT1 IN VARCHAR2,
P_RES17_ATT1 IN VARCHAR2,
P_RES18_ATT1 IN VARCHAR2,
P_RES19_ATT1 IN VARCHAR2,
P_RES20_ATT1 IN VARCHAR2,
P_RES21_ATT1 IN VARCHAR2,
P_RES22_ATT1 IN VARCHAR2,
P_RES23_ATT1 IN VARCHAR2,
P_RES24_ATT1 IN VARCHAR2,
P_RES25_ATT1 IN VARCHAR2,
P_RES26_ATT1 IN VARCHAR2,
P_RES27_ATT1 IN VARCHAR2,
P_RES28_ATT1 IN VARCHAR2,
P_RES29_ATT1 IN VARCHAR2,
P_RES30_ATT1 IN VARCHAR2,
P_RES1_ATT2 IN VARCHAR2,
P_RES2_ATT2 IN VARCHAR2,
P_RES3_ATT2 IN VARCHAR2,
P_RES4_ATT2 IN VARCHAR2,
P_RES5_ATT2 IN VARCHAR2,
P_RES6_ATT2 IN VARCHAR2,
P_RES7_ATT2 IN VARCHAR2,
P_RES8_ATT2 IN VARCHAR2,
P_RES9_ATT2 IN VARCHAR2,
P_RES10_ATT2 IN VARCHAR2,
P_RES11_ATT2 IN VARCHAR2,
P_RES12_ATT2 IN VARCHAR2,
P_RES13_ATT2 IN VARCHAR2,
P_RES14_ATT2 IN VARCHAR2,
P_RES15_ATT2 IN VARCHAR2,
P_RES16_ATT2 IN VARCHAR2,
P_RES17_ATT2 IN VARCHAR2,
P_RES18_ATT2 IN VARCHAR2,
P_RES19_ATT2 IN VARCHAR2,
P_RES20_ATT2 IN VARCHAR2,
P_RES21_ATT2 IN VARCHAR2,
P_RES22_ATT2 IN VARCHAR2,
P_RES23_ATT2 IN VARCHAR2,
P_RES24_ATT2 IN VARCHAR2,
P_RES25_ATT2 IN VARCHAR2,
P_RES26_ATT2 IN VARCHAR2,
P_RES27_ATT2 IN VARCHAR2,
P_RES28_ATT2 IN VARCHAR2,
P_RES29_ATT2 IN VARCHAR2,
P_RES30_ATT2 IN VARCHAR2,
P_RES1_ATT3 IN VARCHAR2,
P_RES2_ATT3 IN VARCHAR2,
P_RES3_ATT3 IN VARCHAR2,
P_RES4_ATT3 IN VARCHAR2,
P_RES5_ATT3 IN VARCHAR2,
P_RES6_ATT3 IN VARCHAR2,
P_RES7_ATT3 IN VARCHAR2,
P_RES8_ATT3 IN VARCHAR2,
P_RES9_ATT3 IN VARCHAR2,
P_RES10_ATT3 IN VARCHAR2,
P_RES11_ATT3 IN VARCHAR2,
P_RES12_ATT3 IN VARCHAR2,
P_RES13_ATT3 IN VARCHAR2,
P_RES14_ATT3 IN VARCHAR2,
P_RES15_ATT3 IN VARCHAR2,
P_RES16_ATT3 IN VARCHAR2,
P_RES17_ATT3 IN VARCHAR2,
P_RES18_ATT3 IN VARCHAR2,
P_RES19_ATT3 IN VARCHAR2,
P_RES20_ATT3 IN VARCHAR2,
P_RES21_ATT3 IN VARCHAR2,
P_RES22_ATT3 IN VARCHAR2,
P_RES23_ATT3 IN VARCHAR2,
P_RES24_ATT3 IN VARCHAR2,
P_RES25_ATT3 IN VARCHAR2,
P_RES26_ATT3 IN VARCHAR2,
P_RES27_ATT3 IN VARCHAR2,
P_RES28_ATT3 IN VARCHAR2,
P_RES29_ATT3 IN VARCHAR2,
P_RES30_ATT3 IN VARCHAR2,
P_RES1_ATT4 IN VARCHAR2,
P_RES2_ATT4 IN VARCHAR2,
P_RES3_ATT4 IN VARCHAR2,
P_RES4_ATT4 IN VARCHAR2,
P_RES5_ATT4 IN VARCHAR2,
P_RES6_ATT4 IN VARCHAR2,
P_RES7_ATT4 IN VARCHAR2,
P_RES8_ATT4 IN VARCHAR2,
P_RES9_ATT4 IN VARCHAR2,
P_RES10_ATT4 IN VARCHAR2,
P_RES11_ATT4 IN VARCHAR2,
P_RES12_ATT4 IN VARCHAR2,
P_RES13_ATT4 IN VARCHAR2,
P_RES14_ATT4 IN VARCHAR2,
P_RES15_ATT4 IN VARCHAR2,
P_RES16_ATT4 IN VARCHAR2,
P_RES17_ATT4 IN VARCHAR2,
P_RES18_ATT4 IN VARCHAR2,
P_RES19_ATT4 IN VARCHAR2,
P_RES20_ATT4 IN VARCHAR2,
P_RES21_ATT4 IN VARCHAR2,
P_RES22_ATT4 IN VARCHAR2,
P_RES23_ATT4 IN VARCHAR2,
P_RES24_ATT4 IN VARCHAR2,
P_RES25_ATT4 IN VARCHAR2,
P_RES26_ATT4 IN VARCHAR2,
P_RES27_ATT4 IN VARCHAR2,
P_RES28_ATT4 IN VARCHAR2,
P_RES29_ATT4 IN VARCHAR2,
P_RES30_ATT4 IN VARCHAR2,
P_RES1_ATT5 IN VARCHAR2,
P_RES2_ATT5 IN VARCHAR2,
P_RES3_ATT5 IN VARCHAR2,
P_RES4_ATT5 IN VARCHAR2,
P_RES5_ATT5 IN VARCHAR2,
P_RES6_ATT5 IN VARCHAR2,
P_RES7_ATT5 IN VARCHAR2,
P_RES8_ATT5 IN VARCHAR2,
P_RES9_ATT5 IN VARCHAR2,
P_RES10_ATT5 IN VARCHAR2,
P_RES11_ATT5 IN VARCHAR2,
P_RES12_ATT5 IN VARCHAR2,
P_RES13_ATT5 IN VARCHAR2,
P_RES14_ATT5 IN VARCHAR2,
P_RES15_ATT5 IN VARCHAR2,
P_RES16_ATT5 IN VARCHAR2,
P_RES17_ATT5 IN VARCHAR2,
P_RES18_ATT5 IN VARCHAR2,
P_RES19_ATT5 IN VARCHAR2,
P_RES20_ATT5 IN VARCHAR2,
P_RES21_ATT5 IN VARCHAR2,
P_RES22_ATT5 IN VARCHAR2,
P_RES23_ATT5 IN VARCHAR2,
P_RES24_ATT5 IN VARCHAR2,
P_RES25_ATT5 IN VARCHAR2,
P_RES26_ATT5 IN VARCHAR2,
P_RES27_ATT5 IN VARCHAR2,
P_RES28_ATT5 IN VARCHAR2,
P_RES29_ATT5 IN VARCHAR2,
P_RES30_ATT5 IN VARCHAR2,
P_RES1_START_DATE IN DATE,
P_RES2_START_DATE IN DATE,
P_RES3_START_DATE IN DATE,
P_RES4_START_DATE IN DATE,
P_RES5_START_DATE IN DATE,
P_RES6_START_DATE IN DATE,
P_RES7_START_DATE IN DATE,
P_RES8_START_DATE IN DATE,
P_RES9_START_DATE IN DATE,
P_RES10_START_DATE IN DATE,
P_RES11_START_DATE IN DATE,
P_RES12_START_DATE IN DATE,
P_RES13_START_DATE IN DATE,
P_RES14_START_DATE IN DATE,
P_RES15_START_DATE IN DATE,
P_RES16_START_DATE IN DATE,
P_RES17_START_DATE IN DATE,
P_RES18_START_DATE IN DATE,
P_RES19_START_DATE IN DATE,
P_RES20_START_DATE IN DATE,
P_RES21_START_DATE IN DATE,
P_RES22_START_DATE IN DATE,
P_RES23_START_DATE IN DATE,
P_RES24_START_DATE IN DATE,
P_RES25_START_DATE IN DATE,
P_RES26_START_DATE IN DATE,
P_RES27_START_DATE IN DATE,
P_RES28_START_DATE IN DATE,
P_RES29_START_DATE IN DATE,
P_RES30_START_DATE IN DATE,
P_RES1_END_DATE IN DATE,
P_RES2_END_DATE IN DATE,
P_RES3_END_DATE IN DATE,
P_RES4_END_DATE IN DATE,
P_RES5_END_DATE IN DATE,
P_RES6_END_DATE IN DATE,
P_RES7_END_DATE IN DATE,
P_RES8_END_DATE IN DATE,
P_RES9_END_DATE IN DATE,
P_RES10_END_DATE IN DATE,
P_RES11_END_DATE IN DATE,
P_RES12_END_DATE IN DATE,
P_RES13_END_DATE IN DATE,
P_RES14_END_DATE IN DATE,
P_RES15_END_DATE IN DATE,
P_RES16_END_DATE IN DATE,
P_RES17_END_DATE IN DATE,
P_RES18_END_DATE IN DATE,
P_RES19_END_DATE IN DATE,
P_RES20_END_DATE IN DATE,
P_RES21_END_DATE IN DATE,
P_RES22_END_DATE IN DATE,
P_RES23_END_DATE IN DATE,
P_RES24_END_DATE IN DATE,
P_RES25_END_DATE IN DATE,
P_RES26_END_DATE IN DATE,
P_RES27_END_DATE IN DATE,
P_RES28_END_DATE IN DATE,
P_RES29_END_DATE IN DATE,
P_RES30_END_DATE IN DATE,
P_ATTRIBUTE1 IN VARCHAR2,
P_ATTRIBUTE2 IN VARCHAR2,
P_ATTRIBUTE3 IN VARCHAR2,
P_ATTRIBUTE4 IN VARCHAR2,
P_ATTRIBUTE5 IN VARCHAR2,
P_ATTRIBUTE6 IN VARCHAR2,
P_ATTRIBUTE7 IN VARCHAR2,
P_ATTRIBUTE8 IN VARCHAR2,
P_ATTRIBUTE9 IN VARCHAR2,
P_ATTRIBUTE10 IN VARCHAR2,
P_ATTRIBUTE11 IN VARCHAR2,
P_ATTRIBUTE12 IN VARCHAR2,
P_ATTRIBUTE13 IN VARCHAR2,
P_ATTRIBUTE14 IN VARCHAR2,
P_ATTRIBUTE15 IN VARCHAR2,
P_START_DATE IN DATE,
P_END_DATE IN DATE
)
AS
p_salesperson_flag VARCHAR2(1):= 'Y'; -- flag for salespersons, empty or not
p_delete_flag_code VARCHAR2(1);
Select matching_rule_code
into l_matching_rule_code
from jtf_tty_terr_groups
where terr_group_name = p_to_tg;
select party_site_id into l_party_site_id
from hz_party_sites party_site, hz_parties party
where party.party_number = p_party_number
and party.party_id = party_site.party_id
and party_site.status = 'A'
and party_site.identifying_address_flag = 'Y';
SELECT PARTY_SITE_ID
INTO l_party_site_id
FROM hz_party_sites
WHERE party_site_number = P_PARTY_SITE_ID;
/* check for invalid delete flag */
BEGIN
SELECT lookup_code
INTO p_delete_flag_code
FROM fnd_lookups
WHERE lookup_type = 'JTF_TERR_FLAGS'
AND upper(meaning) = upper(p_delete_flag)
AND rownum < 2;
FND_MESSAGE.SET_NAME ('JTF', 'JTF_TTY_INV_DELETE_FLAG');
/* Retrieve the terr group account details if trying to delete a
named account, transfer a named account or update sales team */
IF (p_from_tg is not null and trim(p_from_tg) is not null) THEN
-- dbms_output.put_line('Sandeep - before GET_TERR_GRP_ACCT_DETAILS');
SELECT PARTY_SITE_ID
INTO l_party_site_id
FROM hz_party_sites
WHERE party_site_number = P_PARTY_SITE_ID;
JTF_TTY_GEN_TERR_PVT.update_terr_for_na(x_from_tg_acct_id, x_from_tg_id );
IF (P_DELETE_FLAG_CODE = 'Y') THEN /* trying to delete from TG */
/* check for invalid scenarios */
IF (p_from_tg is null AND p_to_tg is not null) THEN
fnd_message.clear;
FND_MESSAGE.SET_NAME ('JTF', 'JTF_TTY_INV_PROMOTE_DELETE');
FND_MESSAGE.SET_NAME ('JTF', 'JTF_TTY_INV_TRANSFER_DELETE');
ELSE /* it is a valid delete from TG case */
DELETE_ACCT_FROM_TG(P_TERR_GRP_ACCT_ID => X_FROM_TG_ACCT_ID,
P_NAMED_ACCT_ID => X_NAMED_ACCT_ID,
P_TERR_GRP_ID => X_FROM_TG_ID,
P_Api_Version_Number => P_Api_Version_Number,
p_Init_Msg_List => p_Init_Msg_List,
p_Commit => p_Commit,
p_validation_level => p_validation_level,
X_Return_Status => X_Return_Status,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data) ;
ELSE /* Not a delete from the territory group, can be a
update sales team, Add to TG, Add to tg with update
salesteam, Transfer, or transfer with update sales team */
BEGIN
IF (p_from_tg is not null AND p_to_tg is null) THEN
/* i.e. update sales team */
-- dbms_output.put_line('Sandeep - Before doing validate Sales Team');
UPDATE_SALES_TEAM(P_Api_Version_Number ,
p_Init_Msg_List ,
p_Commit ,
p_validation_level ,
X_Return_Status ,
X_Msg_Count ,
X_Msg_Data ,
l_added_rscs_tbl ,
X_FROM_TG_ID ,
X_FROM_TG_ACCT_ID ,
X_NAMED_ACCT_ID,
P_SALES_GROUP,
P_SALES_ROLE);
JTF_TTY_GEN_TERR_PVT.update_terr_rscs_for_na(x_from_tg_acct_id, x_from_tg_id );
/* i.e. Add to Org or Add to Org with Update Sales Team */
-- dbms_output.put_line('Sandeep - Add to Org/Add to Org with update sales Team -- Before Add Org to TG');
else /* Add Org to TG with update sales team */
-- Check if all the salespersons are valid ones
-- if valid x_return_status <>
-- dbms_output.put_line('Sandeep - Add Org to TG with update Sales Team Before doing validate Sales Team');
END IF; /* end of update sales team */
ELSE /* Transfer or Transfer with update Sales Team
delete the account from the From TG */
DELETE_ACCT_FROM_TG(P_TERR_GRP_ACCT_ID => X_FROM_TG_ACCT_ID,
P_NAMED_ACCT_ID => X_NAMED_ACCT_ID,
P_TERR_GRP_ID => X_FROM_TG_ID,
P_Api_Version_Number => P_Api_Version_Number,
p_Init_Msg_List => p_Init_Msg_List,
p_Commit => p_Commit,
p_validation_level => p_validation_level,
X_Return_Status => X_Return_Status,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data) ;
/* i.e. delete was successful, so move to add org to To Tg */
ADD_ORG_TO_TG(P_Api_Version_Number => P_Api_Version_Number,
p_Init_Msg_List => p_Init_Msg_List,
p_Commit => p_Commit,
p_validation_level => p_validation_level,
X_Return_Status => X_Return_Status,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data,
p_party_number => p_party_number,
p_party_site_id => l_party_site_id,
p_terr_grp_name => p_to_tg,
P_ATTRIBUTE1 => P_ATTRIBUTE1,
P_ATTRIBUTE2 => P_ATTRIBUTE2,
P_ATTRIBUTE3 => P_ATTRIBUTE3,
P_ATTRIBUTE4 => P_ATTRIBUTE4,
P_ATTRIBUTE5 => P_ATTRIBUTE5,
P_ATTRIBUTE6 => P_ATTRIBUTE6,
P_ATTRIBUTE7 => P_ATTRIBUTE7,
P_ATTRIBUTE8 => P_ATTRIBUTE8,
P_ATTRIBUTE9 => P_ATTRIBUTE9,
P_ATTRIBUTE10 => P_ATTRIBUTE10,
P_ATTRIBUTE11 => P_ATTRIBUTE11,
P_ATTRIBUTE12 => P_ATTRIBUTE12,
P_ATTRIBUTE13 => P_ATTRIBUTE13,
P_ATTRIBUTE14 => P_ATTRIBUTE14,
P_ATTRIBUTE15 => P_ATTRIBUTE15,
P_START_DATE => L_START_DATE,
P_END_DATE => L_END_DATE,
x_terr_grp_acct_id => x_to_tg_acct_id,
x_terr_grp_id => x_to_tg_id,
x_named_acct_id => x_new_named_acct_id);
/* Transfer with no update sales team,
* assign the account to territory group owners*/
ASSIGN_ACCT_TO_TG_OWNERS(
P_Api_Version_Number => P_Api_Version_Number,
p_Init_Msg_List => p_Init_Msg_List,
p_Commit => p_Commit,
p_validation_level => p_validation_level,
X_Return_Status => X_Return_Status,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data,
p_terr_grp_acct_id => x_to_tg_acct_id,
p_terr_grp_id => x_to_tg_id);
else /* Transfer to TG with update sales team */
validate_sales_team(
P_Api_Version_Number ,
p_Init_Msg_List ,
p_Commit ,
p_validation_level ,
X_Return_Status ,
X_Msg_Count ,
X_Msg_Data ,
X_TO_TG_ID ,
L_START_DATE,
L_END_DATE,
l_excel_rscs_tbl ,
l_added_rscs_tbl);
END IF; /* end of update sales team */
END IF; /* end of delete of org from the from TG was successful */
END IF;/* end of transfer or transfer with update sales team */
END; /* of begin for not a delete form TG */
END IF; /* not a delete form TG */