[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** Description : Create named account assignments for the selected Territory Alignment.
** Notes :
**
** End of comments
******************************************************************************/
( p_api_version_number IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_alignment_id IN NUMBER
, p_user_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Cursor to find all the named accounts associated with this alignment
cursor c_all_accounts IS
select ALAC.TERR_GROUP_ACCOUNT_ID
from JTF_TTY_ALIGN_ACCTS ALAC
where
ALAC.ALIGNMENT_ID = p_alignment_id;
SELECT 'Y'
FROM jtf_tty_align_accts aa
WHERE aa.alignment_id = c_alignment_id
AND aa.terr_group_account_id NOT IN
( SELECT ga.terr_group_account_id
FROM jtf_tty_terr_grp_accts ga,
jtf_tty_terr_groups ttygrp,
jtf_tty_named_acct_rsc narsc,
jtf_tty_srch_my_resources_v repdn
WHERE ttygrp.terr_group_id = ga.terr_group_id
AND ttygrp.active_from_date <= sysdate
AND ( ttygrp.active_to_date is null
or
ttygrp.active_to_date >= sysdate
)
AND 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 repdn.current_user_id = c_user_id
);
SELECT 'Y'
FROM jtf_tty_pterr_accts alpa
, jtf_tty_align_pterr alpt
, jtf_tty_align_accts alac
, jtf_tty_terr_grp_accts tga
WHERE alpt.align_proposed_terr_id = alpa.align_proposed_terr_id
AND alpa.align_acct_id = alac.align_acct_id
AND alac.terr_group_account_id = tga.terr_group_account_id
AND alac.alignment_id = c_alignment_id
AND NOT EXISTS ( SELECT 'Y'
FROM jtf_tty_terr_grp_roles TGR
WHERE tgr.role_code = alpt.rsc_role_code
AND tgr.terr_group_id = tga.terr_group_id
);
delete from JTF_TTY_NAMED_ACCT_RSC NARS
WHERE NARS.TERR_GROUP_ACCOUNT_ID = account_rsc_table(i)
AND (NARS.resource_id, NARS.rsc_group_id, NARS.rsc_role_code) IN (
/* Salesperson directly/indirectly reports to user */
SELECT dir.resource_id
, grpmemo.group_id
, rol.role_code
FROM jtf_rs_roles_b rol
, jtf_rs_role_relations rlt
, jtf_rs_group_members grpmemo
, jtf_rs_resource_extns dir
WHERE ( rol.manager_flag = 'Y' or rol.member_flag = 'Y' )
AND rlt.role_id = rol.role_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.delete_flag = 'N'
AND SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE+1)
AND rlt.role_resource_id = grpmemo.group_member_id
AND grpmemo.delete_flag = 'N'
AND grpmemo.resource_id = dir.resource_id
AND SYSDATE BETWEEN dir.start_date_active AND NVL(dir.end_date_active, SYSDATE+1)
AND grpmemo.group_id IN ( SELECT dv.group_id
FROM jtf_rs_group_usages usg
, jtf_rs_groups_denorm dv
, jtf_rs_rep_managers sgh
, jtf_rs_resource_extns mrsc
WHERE usg.usage = 'SALES'
AND usg.group_id = dv.group_id
-- AND dv.immediate_parent_flag = 'Y'
AND dv.parent_group_id = sgh.group_id
AND SYSDATE BETWEEN NVL(dv.start_date_active, SYSDATE-1)
AND NVL(dv.end_date_active, SYSDATE+1)
AND SYSDATE BETWEEN sgh.start_date_active AND NVL(sgh.end_date_active, SYSDATE+1)
AND sgh.hierarchy_type IN ('MGR_TO_MGR')
AND sgh.resource_id = sgh.parent_resource_id
AND mrsc.resource_id = sgh.resource_id
AND mrsc.user_id = p_user_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 ,
LAST_UPDATE_LOGIN
)
select
jtf_tty_named_acct_rsc_s.nextval
, 2
, ALAC.terr_group_account_id
, ALPT.RESOURCE_ID
, ALPT.RSC_GROUP_ID
, ALPT.RSC_ROLE_CODE
, 'Y'
, 'RS_EMPLOYEE'
, p_user_id
, sysdate
, p_user_id
, sysdate
, l_login_id
from
JTF_TTY_PTERR_ACCTS ALPA
, JTF_TTY_ALIGN_PTERR ALPT
, JTF_TTY_ALIGN_ACCTS ALAC
where
ALPA.ALIGN_PROPOSED_TERR_ID = ALPT.ALIGN_PROPOSED_TERR_ID
and ALAC.ALIGN_ACCT_ID = ALPA.ALIGN_ACCT_ID
and ALAC.TERR_GROUP_ACCOUNT_ID = account_rsc_table(j)
and ALAC.alignment_id = p_alignment_id
-- check to ensure inserted resource is still valid
and (ALPT.resource_id, ALPT.rsc_group_id, ALPT.rsc_role_code) IN
( SELECT dir.resource_id
, grpmemo.group_id
, rol.role_code
FROM jtf_rs_roles_b rol
, jtf_rs_role_relations rlt
, jtf_rs_group_members grpmemo
, jtf_rs_resource_extns dir
, ( SELECT distinct dv.group_id, dv.immediate_parent_flag child_group_flag
FROM jtf_rs_group_usages usg
, jtf_rs_groups_denorm dv
, jtf_rs_rep_managers sgh
, jtf_rs_resource_extns mrsc
WHERE usg.usage = 'SALES'
AND usg.group_id = dv.group_id
AND ( dv.immediate_parent_flag = 'Y' OR dv.group_id = dv.parent_group_id )
AND dv.parent_group_id = sgh.group_id
AND SYSDATE BETWEEN NVL(dv.start_date_active, SYSDATE-1)
AND NVL(dv.end_date_active, SYSDATE+1)
AND SYSDATE BETWEEN sgh.start_date_active AND NVL(sgh.end_date_active, SYSDATE+1)
AND sgh.hierarchy_type IN ('MGR_TO_MGR')
AND sgh.resource_id = sgh.parent_resource_id
AND mrsc.resource_id = sgh.resource_id
AND mrsc.user_id = p_user_id ) MY_GRPS
WHERE
rlt.role_id = rol.role_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.delete_flag = 'N'
AND SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE+1)
AND rlt.role_resource_id = grpmemo.group_member_id
AND grpmemo.delete_flag = 'N'
AND grpmemo.resource_id = dir.resource_id
AND SYSDATE BETWEEN dir.start_date_active AND NVL(dir.end_date_active, SYSDATE+1)
AND grpmemo.group_id = MY_GRPS.group_id
AND ( rol.manager_flag = 'Y' OR
( rol.member_flag = 'Y' and MY_GRPS.child_group_flag = 'N' ) )
) ;
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'
,account_rsc_table(j)
,'UPDATE'
,'ACTIVATE ALIGNMENT'
,p_user_id
,sysdate
,p_user_id
,sysdate
,l_login_id );
UPDATE JTF_TTY_ALIGNMENTS
SET activated_on = SYSDATE
,alignment_status = 'A'
,last_updated_by = p_user_id
,last_update_date = sysdate
WHERE alignment_id = p_alignment_id;