[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_SALES_TEAM(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_SQL_Trace IN VARCHAR2,
p_Debug_Flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_user_resource_id IN NUMBER, -- NOTE THIS IS NOT USED, user_attr1 used for user_id instead.
p_terr_group_id IN NUMBER,
p_user_attribute1 IN VARCHAR2,
p_user_attribute2 IN VARCHAR2,
p_added_rscs_tbl IN SALESREP_RSC_TBL_TYPE,
p_removed_rscs_tbl IN SALESREP_RSC_TBL_TYPE,
p_affected_parties_tbl IN AFFECTED_PARTY_TBL_TYPE,
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2
)
IS
l_user_id NUMBER := p_user_attribute1;
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 mem.resource_id, mem.group_id, rol.role_code
from jtf_rs_group_members mem,
jtf_rs_role_relations rlt,
jtf_rs_roles_b rol,
jtf_rs_group_members cgrpmem,
jtf_rs_resource_extns crsc,
jtf_rs_groups_denorm grpden
where crsc.user_id = cl_current_user_id
and crsc.resource_id = cgrpmem.resource_id
and cgrpmem.delete_flag = 'N'
and cgrpmem.group_id = mem.group_id
and 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.manager_flag = 'Y'
and rlt.role_resource_id = mem.group_member_id
and mem.delete_flag = 'N'
and mem.group_id = grpden.parent_group_id
and grpden.group_id IN ( select grv1.group_id
from jtf_rs_group_members grv1
where grv1.resource_id = cl_eff_resource_id );
**** if ASSIGN_SUBSIDIARIES has been selected for any resource
************************************************************/
l_user := fnd_global.USER_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
)
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
);
/* JRADHAKR: Inserting values to jtf_tty_named_acct_changes table for GTP
to do an incremental and Total Mode */
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';
/* JRADHAKR: Inserting values to jtf_tty_named_acct_changes table for GTP
to do an incremental and Total Mode */
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_assigned_rsc_exists
from jtf_tty_named_acct_rsc
where terr_group_account_id = l_terr_group_account_id
and assigned_flag = 'Y'
and rownum < 2;
UPDATE JTF_TTY_TERR_GRP_ACCTS
SET DN_JNR_ASSIGNED_FLAG = l_assign_flag
WHERE TERR_GROUP_ACCOUNT_ID = l_terr_group_account_id;
/* Start update jtf_terr_rsc_all */
BEGIN
FOR i IN G_AFFECT_PARTY_TBL.first.. G_AFFECT_PARTY_TBL.last LOOP
SELECT terr_group_id INTO l_terr_group_id
FROM jtf_tty_terr_grp_accts
WHERE terr_group_account_id = G_AFFECT_PARTY_TBL(i).terr_group_account_id;
Jtf_Tty_Gen_Terr_Pvt.update_terr_rscs_for_na
(G_AFFECT_PARTY_TBL(i).terr_group_account_id,
l_terr_group_id);
END UPDATE_SALES_TEAM;