[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 lkp.meaning
INTO x_party_type
FROM fnd_lookups lkp
WHERE lkp.lookup_type = 'JTF_TTY_SITE_TYPE_CODE'
AND lkp.lookup_code = site_type_code;
PROCEDURE delete_bulk_TGA(p_tga_id_str IN VARCHAR2,
p_terr_gp_id_str IN VARCHAR2,
p_named_acct_id_str IN VARCHAR2,
p_change_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
head Number ;
'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
'Start of the procedure JTF_TTY_NA_TERRGP.delete_bulk_TGA');
'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
'Building PL/SQL tables from Input Strings');
'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
'Deleting from JTF_TTY... tables');
DELETE from jtf_tty_named_acct_rsc j WHERE j.TERR_GROUP_ACCOUNT_ID = l_grpAcctId_tbl(idx);
DELETE from JTF_TTY_TERR_GRP_ACCTS j WHERE j.TERR_GROUP_ACCOUNT_ID = l_grpAcctId_tbl(idx);
DELETE from JTF_TTY_NAMED_ACCTS A
WHERE A.named_account_id = l_acctId_tbl(idx)
AND NOT EXISTS
(SELECT 'Y'
FROM JTF_TTY_TERR_GRP_ACCTS tga
WHERE tga.named_account_id = A.named_account_id);
DELETE from JTF_TTY_ACCT_QUAL_MAPS AQM
WHERE AQM.NAMED_ACCOUNT_ID = l_acctId_tbl(idx)
AND NOT EXISTS ( SELECT 'x'
FROM JTF_TTY_NAMED_ACCTS a
WHERE a.named_account_id = AQM.named_account_id);
'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
'Calling procedure JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA');
JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA(l_terrGrpId_tbl,
l_grpAcctId_tbl,
p_change_type,
x_return_status,
x_msg_count,
x_msg_data );
'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
'JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA API has failed');
'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
'Returning from JTF_TTY_GEN_TERR_PVT.delete_bulk_TGA');
'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA',
'End of the procedure JTF_TTY_NA_TERRGP.delete_bulk_TGA');
'jtf.plsql.JTF_TTY_NA_TERRGP.delete_bulk_TGA.OTHERS',
substr(x_msg_data, 1, 4000));
END delete_bulk_TGA;
PROCEDURE delete_terrgp(p_terr_gp_id IN NUMBER)
AS
p_user_id NUMBER;
/* delete from the named acct sum only for affect resources */
/*
DELETE from jtf_tty_rsc_acct_summ j
WHERE j.RESOURCE_ID in (SELECT RESOURCE_ID
from jtf_tty_acct_rsc_dn j, jtf_tty_terr_grp_accts tga1
WHERE j.TERR_GROUP_ACCOUNT_ID = tga1.TERR_GROUP_ACCOUNT_ID
AND tga1.TERR_GROUP_ID = p_terr_gp_id)
AND j.RSC_GROUP_ID in (SELECT RSC_GROUP_ID
from jtf_tty_acct_rsc_dn j, jtf_tty_terr_grp_accts tga2
WHERE j.TERR_GROUP_ACCOUNT_ID = tga2.TERR_GROUP_ACCOUNT_ID
AND tga2.TERR_GROUP_ID = p_terr_gp_id);
/* sum the rsc acct sum table for the deletes resources only */
/* and for na's for different terr gp from the deleted one */
/*
insert into jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(select jtf_tty_rsc_acct_summ_s.nextval,
1,
ilv.RESOURCE_ID,
ilv.RSC_GROUP_ID,
'RS_EMPLOYEE',
ilv.site_type_code,
ilv.num_accts,
p_user_id,
sysdate,
p_user_id,
sysdate
FROM
(select narsc.RESOURCE_ID,
narsc.RSC_GROUP_ID,
'RS_EMPLOYEE',
na.site_type_code,
count(na.NAMED_ACCOUNT_ID) num_accts
from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
jtf_tty_terr_grp_accts tga
where na.named_account_id = tga.named_account_id
and narsc.resource_id in
(SELECT RESOURCE_ID
from jtf_tty_acct_rsc_dn j, jtf_tty_terr_grp_accts tga1
WHERE j.TERR_GROUP_ACCOUNT_ID = tga1.TERR_GROUP_ACCOUNT_ID
AND tga1.TERR_GROUP_ID = p_terr_gp_id)
and narsc.rsc_group_id in
(SELECT rsc_group_id
from jtf_tty_acct_rsc_dn j, jtf_tty_terr_grp_accts tga2
WHERE j.TERR_GROUP_ACCOUNT_ID = tga2.TERR_GROUP_ACCOUNT_ID
AND tga2.TERR_GROUP_ID = p_terr_gp_id)
and tga.terr_group_id <> p_terr_gp_id
and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
/* delete the existing assignments */
DELETE FROM jtf_tty_named_acct_rsc j
WHERE j.TERR_GROUP_ACCOUNT_ID IN
(SELECT TERR_GROUP_ACCOUNT_ID
FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id);
DELETE from jtf_tty_acct_rsc_dn j
WHERE j.TERR_GROUP_ACCOUNT_ID in
(SELECT TERR_GROUP_ACCOUNT_ID
FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id);
DELETE FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id;
DELETE FROM JTF_TTY_NAMED_ACCTS na
WHERE na.NAMED_ACCOUNT_ID NOT IN
(SELECT named_account_id FROM JTF_TTY_TERR_GRP_ACCTS);
/* delete the na mappings if a na is deleted or no reference to it exists */
DELETE FROM JTF_TTY_ACCT_QUAL_MAPS nam
WHERE nam.NAMED_ACCOUNT_ID NOT IN
(SELECT named_account_id FROM JTF_TTY_NAMED_ACCTS);
/* delete all the terr gp owners, access and product */
DELETE FROM jtf_tty_terr_grp_owners
WHERE terr_group_id = p_terr_gp_id;
DELETE FROM jtf_tty_role_prod_int
WHERE terr_group_role_id IN
(SELECT terr_group_role_id FROM jtf_tty_terr_grp_roles
WHERE terr_group_id = p_terr_gp_id);
DELETE FROM jtf_tty_role_access
WHERE terr_group_role_id IN
(SELECT terr_group_role_id FROM jtf_tty_terr_grp_roles
WHERE terr_group_id = p_terr_gp_id);
DELETE FROM jtf_tty_terr_grp_roles
WHERE terr_group_id = p_terr_gp_id;
/* finally delete the terr gp itself */
DELETE FROM jtf_tty_terr_groups
WHERE terr_group_id = p_terr_gp_id;
END delete_terrgp;
SELECT 'SAME'
INTO x_hierarchy_status
FROM dual
WHERE (p_group_id1 IN
(SELECT parent_group_id FROM jtf_rs_groups_denorm
WHERE group_id = p_group_id2)
OR p_group_id1 IN
(SELECT group_id FROM jtf_rs_groups_denorm
WHERE parent_group_id = p_group_id2))
AND ROWNUM < 2;
SELECT tga.terr_group_account_id tga_id,
NAR.assigned_flag aflag,
tgo.rsc_group_id parentgrpid
FROM JTF_TTY_NAMED_ACCT_RSC NAR,
JTF_TTY_TERR_GRP_ACCTS TGA,
jtf_tty_terr_grp_owners tgo,
jtf_rs_groups_denorm gd
WHERE NAR.terr_group_account_id = TGA.terr_group_account_id
AND TGA.TERR_GROUP_ID = p_terr_gp_id
AND NAR.rsc_resource_type = 'RS_EMPLOYEE'
AND gd.group_id = NAR.RSC_GROUP_ID
AND gd.parent_group_id = tgo.rsc_group_id
AND tgo.rsc_resource_type = 'RS_EMPLOYEE'
AND tgo.TERR_GROUP_ID = p_terr_gp_id
AND sysdate BETWEEN gd.start_date_active AND nvl(gd.end_date_active, sysdate);
SELECT tga.terr_group_account_id tga_id,
NAR.rsc_group_id currentgrpid
FROM JTF_TTY_NAMED_ACCT_RSC NAR,
JTF_TTY_TERR_GRP_ACCTS TGA
WHERE NAR.terr_group_account_id = TGA.terr_group_account_id
AND TGA.TERR_GROUP_ID = p_terr_gp_id
AND NAR.rsc_resource_type = 'RS_EMPLOYEE'
AND NOT EXISTS
(
SELECT NULL
FROM jtf_tty_terr_grp_owners tgo,
jtf_rs_groups_denorm gd
WHERE gd.group_id = NAR.RSC_GROUP_ID
AND gd.parent_group_id = tgo.rsc_group_id
AND tgo.rsc_resource_type = 'RS_EMPLOYEE'
AND tgo.TERR_GROUP_ID = p_terr_gp_id
AND sysdate BETWEEN gd.start_date_active AND nvl(gd.end_date_active, sysdate)
);
SELECT rsc_group_id,
resource_id,
rsc_role_code
FROM jtf_tty_terr_grp_owners tgo
WHERE terr_group_id = p_terr_gp_id
AND rsc_resource_type = 'RS_EMPLOYEE';
SELECT 'PARENT_CHILD' INTO result
FROM jtf_rs_groups_denorm
WHERE SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE)
AND
( group_id = DelCandidateGrp(idx)
AND parent_group_id = NewCandidateGrp(indx)
)
OR
( parent_group_id = DelCandidateGrp(idx)
AND group_id = NewCandidateGrp(indx)
);
SELECT 'Y' INTO result
FROM jtf_tty_named_acct_rsc narsc,
jtf_tty_terr_grp_accts tga,
jtf_rs_role_relations rlt ,
jtf_rs_group_members grpmem ,
jtf_rs_groups_denorm grpdn
WHERE narsc.terr_group_account_id = tga.terr_group_account_id
AND tga.terr_group_id = DelCandidateGrp(idx)
AND narsc.resource_id = grpmem.resource_id
AND narsc.rsc_group_id = grpmem.group_id
AND grpmem.group_member_id = rlt.role_resource_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 grpmem.delete_flag = 'N'
AND grpmem.group_id = grpdn.group_id
AND SYSDATE BETWEEN grpdn.start_date_active AND NVL(grpdn.end_date_active, SYSDATE+1)
AND grpdn.parent_group_id = NewCandidateGrp(indx)
AND EXISTS ( SELECT 'Y'
FROM jtf_rs_groups_denorm grpdn1
WHERE narsc.rsc_group_id = grpdn1.group_id
AND grpdn1.parent_group_id = DelCandidateGrp(idx)
)
AND ROWNUM < 2;
END LOOP; -- delete owner candidate
/*--------------- Process delete, new and replace owners ------------------*/
/*-------------------------------------------------------------------------*/
/* New owners: assign all accounts to new owners*/
IF NewOwnerRsc.COUNT>0 THEN
FOR idx IN NewOwnerRsc.FIRST .. NewOwnerRsc.LAST
LOOP
assign_accts( p_terr_gp_id,
NewOwnerRsc(idx),
NewOwnerGrp(idx),
NewOwnerRole(idx),
'NO', -- not in use
p_user_id);
/* deleted owners: delete all account assignments from deleted owner's hierarchy*/
IF DelOwnerRsc.COUNT>0 THEN
FOR idx IN DelOwnerRsc.FIRST .. DelOwnerRsc.LAST
LOOP
delete_assign_accts(p_terr_gp_id,
DelOwnerRsc(idx),
DelOwnerGrp(idx),
DelOwnerRole(idx));
/*update all account assignments that are owned by deleted manager
id ( by resource_id and group_id ) and assigned_flag = 'N'
with replaced owner */
UPDATE jtf_tty_named_acct_rsc narsc
SET resource_id = RplOwnerToRsc(idx),
rsc_group_id = RplOwnerToGrp(idx),
rsc_role_code = RplOwnerToRole(idx)
WHERE narsc.resource_id = RplOwnerFromRsc(idx)
AND narsc.rsc_group_id = RplOwnerFromGrp(idx)
AND narsc.assigned_flag = 'N';
/* delete all the account assignments that roll-up to deleted owner
but not the new owner return terr_group_account_id
and Assign all the terr_group_account_id to new owner */
terr_grp_id := mytabletype();
DELETE FROM jtf_tty_named_acct_rsc narsc
WHERE narsc.terr_group_account_id
IN ( SELECT terr_group_account_id
FROM jtf_tty_terr_grp_accts,
jtf_rs_role_relations rlt ,
jtf_rs_group_members grpmem ,
jtf_rs_groups_denorm grpdn
WHERE terr_group_id = p_terr_gp_id
AND grpmem.resource_id = narsc.resource_id
AND grpmem.group_id = narsc.rsc_group_id
AND grpmem.group_member_id = rlt.role_resource_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND grpmem.group_id = grpdn.group_id
AND grpdn.parent_group_id = rplownerfromgrp(idx)
AND NOT EXISTS
( select 'Y'
from jtf_rs_groups_denorm grpdn1
where grpmem.group_id = grpdn1.group_id
and grpdn1.parent_group_id = rplownertogrp(idx)
and sysdate between grpdn1.start_date_active
and nvl(grpdn1.end_date_active,sysdate+1)
)
)
RETURNING terr_group_account_id BULK COLLECT INTO terr_grp_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,
terrgrpid_nodup(i),
RplOwnerToRsc(idx),
RplOwnerToGrp(idx),
RplOwnerToRole(idx),
'N',
'RS_EMPLOYEE',
p_user_id,
SYSDATE,
p_user_id,
SYSDATE
FROM dual
);
DELETE from jtf_tty_named_acct_rsc j
WHERE j.TERR_GROUP_ACCOUNT_ID = tgaHasParent.tga_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,
tgaHasParent.tga_id,
owner.resource_id,
owner.rsc_group_id,
owner.rsc_role_code,
'N',
'RS_EMPLOYEE',
p_user_id,
sysdate,
p_user_id,
sysdate
FROM dual
WHERE NOT EXISTS
( SELECT NULL
FROM jtf_tty_named_acct_rsc r
WHERE r.TERR_GROUP_ACCOUNT_ID = tgaHasParent.tga_id
AND r.RESOURCE_ID = owner.resource_id
AND r.RSC_ROLE_CODE = owner.rsc_role_code
AND r.RSC_GROUP_ID = owner.rsc_group_id
AND r.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE'
AND r.RSC_GROUP_ID = tgaHasParent.parentgrpid
)
);
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,
tgaNoParent.tga_id,
owner.resource_id,
owner.rsc_group_id,
owner.rsc_role_code,
'N',
'RS_EMPLOYEE',
p_user_id,
sysdate,
p_user_id,
sysdate
FROM dual
WHERE NOT EXISTS
( SELECT NULL
FROM jtf_tty_named_acct_rsc r
WHERE r.TERR_GROUP_ACCOUNT_ID = tgaNoParent.tga_id
AND r.RESOURCE_ID = owner.resource_id
AND r.RSC_ROLE_CODE = owner.rsc_role_code
AND r.RSC_GROUP_ID = owner.rsc_group_id
AND r.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE'
)
AND EXISTS
(
SELECT NULL
FROM jtf_rs_groups_denorm gd
WHERE owner.rsc_group_id = gd.parent_group_id
AND gd.group_id = tgaNoParent.currentgrpid
)
);
DELETE from jtf_tty_acct_rsc_dn j
WHERE j.TERR_GROUP_ACCOUNT_ID in
(SELECT TERR_GROUP_ACCOUNT_ID
FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id)
AND j.RESOURCE_ID = p_resource_id
AND j.RSC_GROUP_ID = p_group_id
AND j.RSC_ROLE_CODE = p_role_code;
/* delete from the named acct sum */
/*
DELETE from jtf_tty_rsc_acct_summ j
WHERE j.RESOURCE_ID = p_resource_id
AND j.RSC_GROUP_ID = p_group_id
AND j.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE';
DELETE from jtf_tty_named_acct_rsc j
WHERE j.TERR_GROUP_ACCOUNT_ID in
(SELECT TERR_GROUP_ACCOUNT_ID
FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id)
AND j.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = managed_group_id);
DELETE from jtf_tty_acct_rsc_dn j
WHERE j.TERR_GROUP_ACCOUNT_ID in
(SELECT TERR_GROUP_ACCOUNT_ID
FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id)
AND j.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = managed_group_id);
/* delete from the named acct sum */
/*
DELETE from jtf_tty_rsc_acct_summ j
WHERE j.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = managed_group_id);
insert into jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(select jtf_tty_rsc_acct_summ_s.nextval,
1,
ilv.RESOURCE_ID,
ilv.RSC_GROUP_ID,
'RS_EMPLOYEE',
ilv.site_type_code,
ilv.num_accts,
p_user_id,
sysdate,
p_user_id,
sysdate
FROM
(select narsc.RESOURCE_ID,
narsc.RSC_GROUP_ID,
'RS_EMPLOYEE',
na.site_type_code,
count(na.NAMED_ACCOUNT_ID) num_accts
from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
jtf_tty_terr_grp_accts tga
where na.named_account_id = tga.named_account_id
and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
and narsc.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = managed_group_id)
group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
DELETE from jtf_tty_named_acct_rsc j
WHERE j.TERR_GROUP_ACCOUNT_ID in
(SELECT TERR_GROUP_ACCOUNT_ID
FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id)
AND j.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = child_group_id);
DELETE from jtf_tty_acct_rsc_dn j
WHERE j.TERR_GROUP_ACCOUNT_ID in
(SELECT TERR_GROUP_ACCOUNT_ID
FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id)
AND j.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = child_group_id);
/* delete from the named acct sum */
/*
DELETE from jtf_tty_rsc_acct_summ j
WHERE j.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = child_group_id);
insert into jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(select jtf_tty_rsc_acct_summ_s.nextval,
1,
ilv.RESOURCE_ID,
ilv.RSC_GROUP_ID,
'RS_EMPLOYEE',
ilv.site_type_code,
ilv.num_accts,
p_user_id,
sysdate,
p_user_id,
sysdate
FROM
(select narsc.RESOURCE_ID,
narsc.RSC_GROUP_ID,
'RS_EMPLOYEE',
na.site_type_code,
count(na.NAMED_ACCOUNT_ID) num_accts
from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
jtf_tty_terr_grp_accts tga
where na.named_account_id = tga.named_account_id
and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
and narsc.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = child_group_id)
group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
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_gp_acct_id,
p_resource_id,
p_group_id,
p_role_code,
'N',
'RS_EMPLOYEE',
p_user_id,
SYSDATE,
p_user_id,
SYSDATE
FROM dual
WHERE NOT EXISTS
( SELECT NULL FROM jtf_tty_named_acct_rsc r
WHERE r.TERR_GROUP_ACCOUNT_ID = p_terr_gp_acct_id
AND r.RESOURCE_ID = p_resource_id
AND r.RSC_ROLE_CODE = p_role_code
AND r.RSC_GROUP_ID = p_group_id
AND r.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE')
);
INSERT into jtf_tty_acct_rsc_dn(
ACCOUNT_RESOURCE_DN_ID,
OBJECT_VERSION_NUMBER,
TERR_GROUP_ACCOUNT_ID,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_ROLE_CODE,
RSC_RESOURCE_TYPE,
ASSIGNED_TO_DIRECT_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(select jtf_tty_acct_rsc_dn_s.nextval,
1,
p_terr_gp_acct_id,
p_resource_id,
p_group_id,
p_role_code,
'RS_EMPLOYEE',
'N',
p_user_id,
sysdate,
p_user_id,
sysdate
from dual
WHERE NOT EXISTS
( SELECT NULL FROM jtf_tty_acct_rsc_dn r
WHERE r.TERR_GROUP_ACCOUNT_ID = p_terr_gp_acct_id
AND r.RESOURCE_ID = p_resource_id
AND r.RSC_ROLE_CODE = p_role_code
AND r.RSC_GROUP_ID = p_group_id
AND r.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE')
);
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,
a.TERR_GROUP_ACCOUNT_ID,
p_resource_id,
p_group_id,
p_role_code,
'N',
'RS_EMPLOYEE',
p_user_id,
SYSDATE,
p_user_id,
SYSDATE
FROM jtf_tty_terr_grp_accts a, dual
WHERE terr_group_id = p_terr_gp_id
AND NOT EXISTS
( SELECT NULL FROM jtf_tty_named_acct_rsc r
WHERE r.TERR_GROUP_ACCOUNT_ID = a.TERR_GROUP_ACCOUNT_ID
AND r.RESOURCE_ID = p_resource_id
AND r.RSC_ROLE_CODE = p_role_code
AND r.RSC_GROUP_ID = p_group_id
AND r.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE')
);
INSERT into jtf_tty_acct_rsc_dn(
ACCOUNT_RESOURCE_DN_ID,
OBJECT_VERSION_NUMBER,
TERR_GROUP_ACCOUNT_ID,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_ROLE_CODE,
RSC_RESOURCE_TYPE,
ASSIGNED_TO_DIRECT_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(select jtf_tty_acct_rsc_dn_s.nextval,
1,
a.TERR_GROUP_ACCOUNT_ID,
p_resource_id,
p_group_id,
p_role_code,
'RS_EMPLOYEE',
'N',
p_user_id,
sysdate,
p_user_id,
sysdate
from jtf_tty_terr_grp_accts a, dual
where terr_group_id = p_terr_gp_id
AND NOT EXISTS
( SELECT NULL FROM jtf_tty_acct_rsc_dn r
WHERE r.TERR_GROUP_ACCOUNT_ID = a.TERR_GROUP_ACCOUNT_ID
AND r.RESOURCE_ID = p_resource_id
AND r.RSC_ROLE_CODE = p_role_code
AND r.RSC_GROUP_ID = p_group_id
AND r.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE')
);
delete from jtf_tty_rsc_acct_summ
where resource_id in(
select resource_id
from jtf_tty_terr_grp_owners
where terr_group_id = p_terr_gp_id)
and rsc_group_id in(
select rsc_group_id
from jtf_tty_terr_grp_owners
where terr_group_id = p_terr_gp_id);
insert into jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(select jtf_tty_rsc_acct_summ_s.nextval,
1,
ilv.RESOURCE_ID,
ilv.RSC_GROUP_ID,
'RS_EMPLOYEE',
ilv.site_type_code,
ilv.num_accts,
p_user_id,
sysdate,
p_user_id,
sysdate
FROM
(select narsc.RESOURCE_ID,
narsc.RSC_GROUP_ID,
'RS_EMPLOYEE',
na.site_type_code,
count(na.NAMED_ACCOUNT_ID) num_accts
from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
jtf_tty_terr_grp_accts tga, jtf_tty_terr_grp_owners tgo
where na.named_account_id = tga.named_account_id
and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
and narsc.RESOURCE_ID = tgo.RESOURCE_ID
and narsc.RSC_GROUP_ID = tgo.RSC_GROUP_ID
and tgo.TERR_GROUP_ID = p_terr_gp_id
group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
delete from jtf_tty_rsc_acct_summ
where RESOURCE_ID = p_resource_id
and RSC_GROUP_ID = p_rsc_group_id;
insert into jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(select jtf_tty_rsc_acct_summ_s.nextval,
1,
ilv.RESOURCE_ID,
ilv.RSC_GROUP_ID,
'RS_EMPLOYEE',
ilv.site_type_code,
ilv.num_accts,
p_user_id,
sysdate,
p_user_id,
sysdate
FROM
(select narsc.RESOURCE_ID,
narsc.RSC_GROUP_ID,
'RS_EMPLOYEE',
na.site_type_code,
count(na.NAMED_ACCOUNT_ID) num_accts
from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
jtf_tty_terr_grp_accts tga
where na.named_account_id = tga.named_account_id
and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
and narsc.RESOURCE_ID = p_resource_id
and narsc.RSC_GROUP_ID = p_rsc_group_id
group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
IS SELECT DISTINCT rs.user_id, tgo.rsc_group_id
FROM JTF_TTY_TERR_GRP_OWNERS tgo,
JTF_RS_RESOURCE_EXTNS rs
WHERE rs.resource_id = tgo.resource_id;
IS SELECT mdv.resource_id,
mdv.group_id,
mdv.dir_user_id
FROM jtf_tty_my_resources_v mdv,
jtf_rs_group_members mem,
jtf_rs_roles_b rol,
jtf_rs_role_relations rlt
WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND NVL(rlt.delete_flag, 'N') <> 'Y'
AND SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE)
AND rlt.role_id = rol.role_id
AND rol.manager_flag = 'Y'
AND rlt.role_resource_id = mem.group_member_id
AND NVL( mem.delete_flag, 'N') <> 'Y'
AND mem.resource_id = mdv.resource_id
AND rol.role_code = mdv.role_code
AND mem.group_id = mdv.group_id
AND mdv.parent_group_id = p_owner_group_id
AND mdv.current_user_id = p_owner_user_id;
IS SELECT DISTINCT sdv.resource_id,
sdv.group_id,
sdv.dir_user_id
FROM jtf_tty_my_resources_v sdv,
jtf_rs_group_members mem,
jtf_rs_roles_b rol,
jtf_rs_role_relations rlt
WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND NVL(rlt.delete_flag, 'N') <> 'Y'
AND SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE)
AND rlt.role_id = rol.role_id
AND rol.member_flag = 'Y'
AND rlt.role_resource_id = mem.group_member_id
AND NVL( mem.delete_flag, 'N') <> 'Y'
AND mem.resource_id = sdv.resource_id
AND mem.group_id = sdv.group_id
AND sdv.role_code = rol.role_code
AND sdv.parent_group_id = p_owner_group_id
AND sdv.current_user_id = p_owner_user_id;
and not exists (select mem1.resource_id from
jtf_rs_group_members mem1,
jtf_rs_roles_b rol1,
jtf_rs_role_relations rlt1
where rlt1.role_resource_type = 'RS_GROUP_MEMBER'
and nvl(rlt1.delete_flag, 'N') <> 'Y'
and sysdate between rlt1.start_date_active
and nvl(rlt1.end_date_active, sysdate)
and rlt1.role_id = rol1.role_id
and rol1.manager_flag = 'Y'
and rlt1.role_resource_id = mem1.group_member_id
and nvl( mem1.delete_flag, 'N') <> 'Y'
and mem1.resource_id = sdv.resource_id);
SELECT COUNT(DISTINCT na.named_account_id) num_accounts
FROM
jtf_tty_named_accts na,
jtf_tty_terr_grp_accts ga,
jtf_tty_my_resources_v repdn,
jtf_tty_named_acct_rsc narsc,
jtf_rs_resource_extns rs,
jtf_rs_group_members mem,
jtf_tty_terr_groups ttygrp
WHERE na.named_account_id = ga.named_account_id
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.parent_group_id = p_group_id
AND repdn.current_user_id = p_user_id
AND rs.user_id = repdn.current_user_id
AND rs.resource_id = mem.resource_id
AND ttygrp.terr_group_id = ga.terr_group_id
AND (ttygrp.active_from_date <= SYSDATE OR ttygrp.active_to_date IS NULL)
AND (ttygrp.active_to_date >= SYSDATE OR ttygrp.active_to_date IS NULL)
AND na.site_type_code = p_site_type_code;
SELECT COUNT(DISTINCT na.named_account_id) num_accounts
FROM
jtf_tty_named_accts na,
jtf_tty_terr_grp_accts ga,
jtf_tty_my_resources_v repdn,
jtf_tty_named_acct_rsc narsc,
jtf_rs_resource_extns rs,
jtf_rs_group_members mem,
jtf_tty_terr_groups ttygrp
WHERE na.named_account_id = ga.named_account_id
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.parent_group_id = p_group_id
AND repdn.current_user_id = p_user_id
AND rs.user_id = repdn.current_user_id
AND rs.resource_id = mem.resource_id
AND ttygrp.terr_group_id = ga.terr_group_id
AND (ttygrp.active_from_date <= SYSDATE OR ttygrp.active_to_date IS NULL)
AND (ttygrp.active_to_date >= SYSDATE OR ttygrp.active_to_date IS NULL);
SELECT DISTINCT a.parent_resource_id manager_id
FROM jtf_rs_rep_managers A
WHERE a.hierarchy_type <> 'MGR_TO_ADMIN'
AND a.reports_to_flag = 'Y'
AND a.parent_resource_id <> a.resource_id
AND SYSDATE BETWEEN a.start_date_active AND NVL(end_date_active, SYSDATE+1)
AND a.resource_id = p_resource_id
AND a.group_id = p_group_id;
SELECT COUNT(gt.geo_territory_id) geo
FROM jtf_tty_geo_terr_rsc gt, jtf_tty_geo_terr gterr, jtf_tty_terr_groups tg
WHERE gt.geo_territory_id = gterr.geo_territory_id
AND tg.terr_group_id = gterr.terr_group_id
AND TRUNC(tg.active_from_date) <= TRUNC(SYSDATE)
AND (tg.active_to_date IS NULL OR TRUNC(tg.active_to_date) >= TRUNC(SYSDATE))
AND gt.resource_id = p_resource_id
AND gt.rsc_group_id = p_group_id ;
SELECT lookup_code site_type_code
FROM fnd_lookups l
WHERE lookup_type = 'JTF_TTY_SITE_TYPE_CODE';
IS SELECT COUNT(na.NAMED_ACCOUNT_ID) num_accounts
FROM jtf_tty_named_accts na, jtf_tty_named_acct_rsc narsc,
jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
WHERE na.named_account_id = tga.named_account_id
AND tga.terr_group_id = tg.terr_group_id
AND (tg.active_from_date <= SYSDATE OR tg.active_from_date IS NULL)
AND (tg.active_to_date >= SYSDATE OR tg.active_to_date IS NULL)
AND narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
AND narsc.resource_id = p_resource_id
AND narsc.RSC_GROUP_ID = p_group_id
AND narsc.rsc_resource_type = 'RS_EMPLOYEE'
AND na.site_type_code = p_site_type_code;
IS SELECT COUNT(na.NAMED_ACCOUNT_ID) num_accounts
FROM jtf_tty_named_accts na, jtf_tty_named_acct_rsc narsc,
jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
WHERE na.named_account_id = tga.named_account_id
AND tga.terr_group_id = tg.terr_group_id
AND (tg.active_from_date <= SYSDATE OR tg.active_from_date IS NULL)
AND (tg.active_to_date >= SYSDATE OR tg.active_to_date IS NULL)
AND narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
AND narsc.resource_id = p_resource_id
AND narsc.RSC_GROUP_ID = p_group_id
AND narsc.rsc_resource_type = 'RS_EMPLOYEE';
DELETE jtf_tty_rsc_acct_summ;
SELECT COUNT(RESOURCE_ACCT_SUMM_ID)
INTO p_count
FROM jtf_tty_rsc_acct_summ
WHERE RESOURCE_ID = p_resource_id
AND RSC_GROUP_ID = p_group_id;
INSERT INTO jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
NUMBER_GEOS,
MANAGER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
1,
p_resource_id,
p_group_id,
'RS_EMPLOYEE',
p_site_type_code,
p_num_accts,
p_num_geos,
-999,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate);
INSERT INTO jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
NUMBER_GEOS,
MANAGER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
1,
p_resource_id,
p_group_id,
'RS_EMPLOYEE',
p_site_type_code,
p_num_accts,
p_num_geos,
p_manager_id,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate);
INSERT INTO jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
NUMBER_GEOS,
MANAGER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
1,
p_resource_id,
p_group_id,
'RS_EMPLOYEE',
p_site_type_code,
p_num_accts,
p_num_geos,
-999,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate);
INSERT INTO jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
NUMBER_GEOS,
MANAGER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
1,
p_resource_id,
p_group_id,
'RS_EMPLOYEE',
p_site_type_code,
p_num_accts,
p_num_geos,
p_manager_id,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate);
SELECT COUNT(RESOURCE_ACCT_SUMM_ID)
INTO p_count
FROM jtf_tty_rsc_acct_summ
WHERE RESOURCE_ID = p_resource_id
AND RSC_GROUP_ID = p_group_id;
INSERT INTO jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
NUMBER_GEOS,
MANAGER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
1,
p_resource_id,
p_group_id,
'RS_EMPLOYEE',
p_site_type_code,
p_num_accts,
p_num_geos,
-999,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate);
INSERT INTO jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
NUMBER_GEOS,
MANAGER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
1,
p_resource_id,
p_group_id,
'RS_EMPLOYEE',
p_site_type_code,
p_num_accts,
p_num_geos,
p_manager_id,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate);
INSERT INTO jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
NUMBER_GEOS,
MANAGER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
1,
p_resource_id,
p_group_id,
'RS_EMPLOYEE',
p_site_type_code,
p_num_accts,
p_num_geos,
-999,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate);
INSERT INTO jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
NUMBER_GEOS,
MANAGER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES(jtf_tty_rsc_acct_summ_s.NEXTVAL,
1,
p_resource_id,
p_group_id,
'RS_EMPLOYEE',
p_site_type_code,
p_num_accts,
p_num_geos,
p_manager_id,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate);
(select narsc.RESOURCE_ID,
narsc.RSC_GROUP_ID,
'RS_EMPLOYEE',
na.site_type_code,
count(na.NAMED_ACCOUNT_ID) num_accts
from jtf_tty_named_accts na, jtf_tty_named_acct_rsc narsc,
jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
where na.named_account_id = tga.named_account_id
and tga.terr_group_id = tg.terr_group_id
and (tg.active_from_date <= sysdate or tg.active_from_date is null)
and (tg.active_to_date >= sysdate or tg.active_to_date is null)
and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
and narsc.resource_id = p_resource_id
and narsc.RSC_GROUP_ID = p_group_id
group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
delete jtf_tty_rsc_acct_summ;
insert into jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(select jtf_tty_rsc_acct_summ_s.nextval,
1,
ilv.RESOURCE_ID,
ilv.RSC_GROUP_ID,
'RS_EMPLOYEE',
ilv.site_type_code,
ilv.num_accts,
p_user_id,
sysdate,
p_user_id,
sysdate
FROM
(select narsc.RESOURCE_ID,
narsc.RSC_GROUP_ID,
'RS_EMPLOYEE',
na.site_type_code,
count(na.NAMED_ACCOUNT_ID) num_accts
from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
where na.named_account_id = tga.named_account_id
and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
and tga.terr_group_id = tg.terr_group_id
and (tg.active_from_date <= sysdate or tg.active_from_date is null)
and (tg.active_to_date >= sysdate or tg.active_to_date is null)
group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
PROCEDURE delete_terrgp_owners_roles(p_terr_gp_id IN NUMBER)
AS
BEGIN
/* delete all the territory group owners */
DELETE FROM jtf_tty_terr_grp_owners
WHERE terr_group_id = p_terr_gp_id;
/* delete all the roles, first the product interests, access and finally the roles */
DELETE FROM jtf_tty_role_prod_int
WHERE terr_group_role_id IN (
SELECT terr_group_role_id
FROM jtf_tty_terr_grp_roles
WHERE terr_group_id = p_terr_gp_id);
DELETE FROM jtf_tty_role_access
WHERE terr_group_role_id IN (
SELECT terr_group_role_id
FROM jtf_tty_terr_grp_roles
WHERE terr_group_id = p_terr_gp_id);
DELETE FROM jtf_tty_terr_grp_roles
WHERE terr_group_id = p_terr_gp_id;
END delete_terrgp_owners_roles;
p_action_type IN VARCHAR2 DEFAULT 'INSERT',
p_catch_all_user_id IN NUMBER,
p_num_winners IN NUMBER,
p_generate_na_flag IN VARCHAR2,
p_group_type IN VARCHAR2 DEFAULT 'NAMED_ACCOUNT')
AS
p_workflow_process_name VARCHAR2(30) DEFAULT NULL;
SELECT COUNT(name)
INTO p_workflow_count
FROM wf_activities_vl
WHERE item_type = p_workflow_item_type
AND TYPE = 'PROCESS'
AND TRUNC(NVL(end_date,SYSDATE)) >= TRUNC(SYSDATE);
SELECT name
INTO p_workflow_process_name
FROM wf_activities_vl
WHERE item_type = p_workflow_item_type
AND TYPE = 'PROCESS'
AND TRUNC(NVL(end_date,SYSDATE)) >= TRUNC(SYSDATE)
AND ROWNUM < 2 ;
IF (p_action_type = 'INSERT') THEN
INSERT INTO JTF_TTY_TERR_GROUPS(
TERR_GROUP_ID,
OBJECT_VERSION_NUMBER,
TERR_GROUP_NAME,
DESCRIPTION,
RANK,
ACTIVE_FROM_DATE,
ACTIVE_TO_DATE,
PARENT_TERR_ID,
MATCHING_RULE_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
WORKFLOW_ITEM_TYPE,
WORKFLOW_PROCESS_NAME,
CATCH_ALL_RESOURCE_ID,
CATCH_ALL_RESOURCE_TYPE,
NUM_WINNERS,
GENERATE_CATCHALL_FLAG,
SELF_SERVICE_TYPE)
VALUES(
p_terr_gp_id,
1,
p_terr_gp_name,
p_description,
p_rank,
p_active_from_date,
p_active_to_date,
p_terr_id,
p_matching_rule_code,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_workflow_item_type,
p_workflow_process_name,
p_catch_all_user_id,
'RS_EMPLOYEE',
p_num_winners,
p_generate_na_flag,
p_group_type);
UPDATE JTF_TTY_TERR_GROUPS
SET TERR_GROUP_NAME = p_terr_gp_name,
DESCRIPTION = p_description,
RANK = p_rank,
ACTIVE_FROM_DATE = p_active_from_date,
ACTIVE_TO_DATE = p_active_to_date,
PARENT_TERR_ID = p_terr_id,
MATCHING_RULE_CODE = p_matching_rule_code,
WORKFLOW_ITEM_TYPE = p_workflow_item_type,
WORKFLOW_PROCESS_NAME = p_workflow_process_name,
CATCH_ALL_RESOURCE_ID = p_catch_all_user_id,
CATCH_ALL_RESOURCE_TYPE = 'RS_EMPLOYEE',
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_DATE = SYSDATE,
NUM_WINNERS = p_num_winners,
GENERATE_CATCHALL_FLAG = p_generate_na_flag
WHERE TERR_GROUP_ID = p_terr_gp_id;
UPDATE JTF_TTY_GEO_TERR
SET GEO_TERR_NAME = p_terr_gp_name
WHERE TERR_GROUP_ID = p_terr_gp_id
AND OWNER_RESOURCE_ID = -999
AND GEO_TERRITORY_ID = - PARENT_GEO_TERR_ID;
INSERT INTO JTF_TTY_TERR_GRP_ROLES(
TERR_GROUP_ROLE_ID,
OBJECT_VERSION_NUMBER,
TERR_GROUP_ID,
ROLE_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES(
p_terr_gp_role_id,
1,
p_terr_gp_id,
p_role_code,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id);
PROCEDURE delete_assign_accts(p_terr_gp_id IN NUMBER,
p_resource_id IN NUMBER,
p_group_id IN NUMBER,
p_role_code IN VARCHAR2)
AS
managed_group_id NUMBER;
SELECT mem.group_id
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 NVL(rlt.delete_flag, 'N') <> 'Y'
AND SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE)
AND rlt.role_id = rol.role_id
AND rol.manager_flag = 'Y'
AND rlt.role_resource_id = mem.group_member_id
AND NVL( mem.delete_flag, 'N') <> 'Y'
AND mem.resource_id = p_resource_id;
SELECT group_id
FROM jtf_rs_groups_denorm
WHERE PARENT_GROUP_ID = managed_group_id;
SELECT resource_id
FROM jtf_rs_group_members
WHERE group_id = x_group_id;
/* delete the existing assignments */
DELETE FROM jtf_tty_named_acct_rsc j
WHERE j.TERR_GROUP_ACCOUNT_ID IN
(SELECT TERR_GROUP_ACCOUNT_ID
FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id)
AND j.RESOURCE_ID = p_resource_id
AND j.RSC_GROUP_ID = p_group_id
AND j.RSC_ROLE_CODE = p_role_code;
DELETE from jtf_tty_acct_rsc_dn j
WHERE j.TERR_GROUP_ACCOUNT_ID in
(SELECT TERR_GROUP_ACCOUNT_ID
FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id)
AND j.RESOURCE_ID = p_resource_id
AND j.RSC_GROUP_ID = p_group_id
AND j.RSC_ROLE_CODE = p_role_code;
/* delete from the named acct sum */
/*
DELETE from jtf_tty_rsc_acct_summ j
WHERE j.RESOURCE_ID = p_resource_id
AND j.RSC_GROUP_ID = p_group_id
AND j.RSC_RESOURCE_TYPE = 'RS_EMPLOYEE';
DELETE FROM jtf_tty_named_acct_rsc j
WHERE j.TERR_GROUP_ACCOUNT_ID IN
(SELECT TERR_GROUP_ACCOUNT_ID
FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id)
AND j.RESOURCE_ID IN (
SELECT resource_id
FROM jtf_rs_group_members
WHERE group_id = managed_group_id);
DELETE from jtf_tty_acct_rsc_dn j
WHERE j.TERR_GROUP_ACCOUNT_ID in
(SELECT TERR_GROUP_ACCOUNT_ID
FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id)
AND j.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = managed_group_id);
/* delete from the named acct sum */
/*
DELETE from jtf_tty_rsc_acct_summ j
WHERE j.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = managed_group_id);
insert into jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(select jtf_tty_rsc_acct_summ_s.nextval,
1,
ilv.RESOURCE_ID,
ilv.RSC_GROUP_ID,
'RS_EMPLOYEE',
ilv.site_type_code,
ilv.num_accts,
p_user_id,
sysdate,
p_user_id,
sysdate
FROM
(select narsc.RESOURCE_ID,
narsc.RSC_GROUP_ID,
'RS_EMPLOYEE',
na.site_type_code,
count(na.NAMED_ACCOUNT_ID) num_accts
from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
jtf_tty_terr_grp_accts tga
where na.named_account_id = tga.named_account_id
and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
and narsc.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = managed_group_id)
group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
DELETE FROM jtf_tty_named_acct_rsc j
WHERE j.TERR_GROUP_ACCOUNT_ID IN
(SELECT TERR_GROUP_ACCOUNT_ID
FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id)
AND j.RESOURCE_ID IN (
SELECT resource_id
FROM jtf_rs_group_members
WHERE group_id = child_group_id);
DELETE from jtf_tty_acct_rsc_dn j
WHERE j.TERR_GROUP_ACCOUNT_ID in
(SELECT TERR_GROUP_ACCOUNT_ID
FROM JTF_TTY_TERR_GRP_ACCTS
WHERE TERR_GROUP_ID = p_terr_gp_id)
AND j.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = child_group_id);
/* delete from the named acct sum */
/*
DELETE from jtf_tty_rsc_acct_summ j
WHERE j.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = child_group_id);
insert into jtf_tty_rsc_acct_summ(
RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER,
RESOURCE_ID,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(select jtf_tty_rsc_acct_summ_s.nextval,
1,
ilv.RESOURCE_ID,
ilv.RSC_GROUP_ID,
'RS_EMPLOYEE',
ilv.site_type_code,
ilv.num_accts,
p_user_id,
sysdate,
p_user_id,
sysdate
FROM
(select narsc.RESOURCE_ID,
narsc.RSC_GROUP_ID,
'RS_EMPLOYEE',
na.site_type_code,
count(na.NAMED_ACCOUNT_ID) num_accts
from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
jtf_tty_terr_grp_accts tga
where na.named_account_id = tga.named_account_id
and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
and narsc.RESOURCE_ID IN (
select resource_id
from jtf_rs_group_members
where group_id = child_group_id)
group by narsc.RESOURCE_ID, narsc.RSC_GROUP_ID, na.site_type_code)ilv);
END delete_assign_accts;
INSERT INTO JTF_TTY_ROLE_ACCESS(
TERR_GROUP_ROLE_ACCESS_ID,
OBJECT_VERSION_NUMBER,
TERR_GROUP_ROLE_ID ,
ACCESS_TYPE ,
TRANS_ACCESS_CODE,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN)
VALUES(
JTF_TTY_ROLE_ACCESS_S.NEXTVAL,
1,
p_terr_gp_role_id,
p_access_type,
p_access_code,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id);
INSERT INTO JTF_TTY_ROLE_PROD_INT(
TERR_GROUP_ROLE_PROD_INT_ID,
OBJECT_VERSION_NUMBER ,
TERR_GROUP_ROLE_ID ,
INTEREST_TYPE_ID,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN)
VALUES(
JTF_TTY_ROLE_PROD_INT_S.NEXTVAL,
1,
p_terr_gp_role_id,
p_interest_type_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id);
INSERT INTO JTF_TTY_ROLE_PROD_INT(
TERR_GROUP_ROLE_PROD_INT_ID,
OBJECT_VERSION_NUMBER ,
TERR_GROUP_ROLE_ID ,
INTEREST_TYPE_ID,
PRODUCT_CATEGORY_ID ,
PRODUCT_CATEGORY_SET_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN)
VALUES(
JTF_TTY_ROLE_PROD_INT_S.NEXTVAL,
1,
p_terr_gp_role_id,
-999,
p_interest_type_id,
p_cat_set_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id);
INSERT INTO JTF_TTY_TERR_GRP_ROLES(
TERR_GROUP_ROLE_ID,
OBJECT_VERSION_NUMBER,
TERR_GROUP_ID,
ROLE_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES(
p_terr_gp_role_id,
1,
p_terr_gp_id,
p_role_code,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id);
INSERT INTO JTF_TTY_ROLE_ACCESS(
TERR_GROUP_ROLE_ACCESS_ID,
OBJECT_VERSION_NUMBER,
TERR_GROUP_ROLE_ID ,
ACCESS_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN)
VALUES(
JTF_TTY_ROLE_ACCESS_S.NEXTVAL,
1,
p_terr_gp_role_id,
p_access_type,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id);
INSERT INTO JTF_TTY_ROLE_PROD_INT(
TERR_GROUP_ROLE_PROD_INT_ID,
OBJECT_VERSION_NUMBER ,
TERR_GROUP_ROLE_ID ,
INTEREST_TYPE_ID,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN)
VALUES(
JTF_TTY_ROLE_PROD_INT_S.NEXTVAL,
1,
p_terr_gp_role_id,
p_interest_type_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id);
PROCEDURE delete_tgp_named_account(p_terr_gp_id IN NUMBER,
p_party_id IN NUMBER,
p_tga_id IN NUMBER)
AS
BEGIN
-- delete assignment for the grp account
DELETE FROM jtf_tty_named_acct_rsc j
WHERE j.TERR_GROUP_ACCOUNT_ID = p_tga_id;
DELETE FROM JTF_TTY_TERR_GRP_ACCTS
WHERE terr_group_account_id = p_tga_id;
DELETE FROM JTF_TTY_NAMED_ACCTS
WHERE party_id = p_party_id
AND party_id NOT IN
(SELECT party_id FROM JTF_TTY_NAMED_ACCTS na, JTF_TTY_TERR_GRP_ACCTS tga
WHERE tga.named_account_id = na.named_account_id);
END delete_tgp_named_account;
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.identifying_address_flag = 'Y';
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 1
INTO p_account_count
FROM jtf_tty_named_accts
WHERE party_id = p_party_id
AND ROWNUM < 2;
SELECT JTF_TTY_NAMED_ACCTS_S.NEXTVAL
INTO p_account_id
FROM dual;
INSERT INTO jtf_tty_named_accts
(NAMED_ACCOUNT_ID,
OBJECT_VERSION_NUMBER ,
PARTY_ID ,
MAPPING_COMPLETE_FLAG,
SITE_TYPE_CODE,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(p_account_id,
2,
p_party_id,
'N',
p_site_type_code,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id
);
SELECT named_account_id, mapping_complete_flag
INTO p_account_id, p_mapping_flag
FROM jtf_tty_named_accts
WHERE party_id = p_party_id;
SELECT 1
INTO p_grp_acct_count
FROM JTF_TTY_TERR_GRP_ACCTS tga, JTF_TTY_NAMED_ACCTS tna
WHERE tga.named_account_id = tna.named_account_id
AND tga.terr_group_id = p_terr_gp_id
AND tna.party_id = p_party_id
AND ROWNUM < 2;
SELECT tga.terr_group_account_id
INTO p_terr_gp_acct_id
FROM JTF_TTY_TERR_GRP_ACCTS tga, JTF_TTY_NAMED_ACCTS tna
WHERE tga.named_account_id = tna.named_account_id
AND tga.terr_group_id = p_terr_gp_id
AND tna.party_id = p_party_id;
SELECT JTF_TTY_TERR_GRP_ACCTS_S.NEXTVAL
INTO p_terr_gp_acct_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 ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(p_terr_gp_acct_id,
2,
p_terr_gp_id,
p_account_id,
p_mapping_flag,
p_site_type_code,
'N',
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id
);
SELECT COUNT(*)
INTO p_account_count
FROM jtf_tty_named_accts
WHERE party_id = p_party_id;
SELECT JTF_TTY_NAMED_ACCTS_S.NEXTVAL
INTO p_account_id
FROM dual;
INSERT INTO jtf_tty_named_accts
(NAMED_ACCOUNT_ID,
OBJECT_VERSION_NUMBER ,
PARTY_ID ,
MAPPING_COMPLETE_FLAG,
SITE_TYPE_CODE,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(p_account_id,
2,
p_party_id,
'N',
p_site_type_code,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id
);
SELECT COUNT(tga.terr_group_account_id)
INTO p_account_count
FROM JTF_TTY_TERR_GRP_ACCTS tga, JTF_TTY_NAMED_ACCTS tna
WHERE tga.named_account_id = tna.named_account_id
AND tna.party_id = p_party_id;
SELECT JTF_TTY_TERR_GRP_ACCTS_S.NEXTVAL
INTO p_terr_gp_acct_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_SITE_TYPE_CODE,
DN_JNR_ASSIGNED_FLAG ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(p_terr_gp_acct_id,
2,
p_terr_gp_id,
p_account_id,
p_site_type_code,
'N',
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id
);
SELECT jtf_tty_named_acct_rsc_s.NEXTVAL
INTO p_terr_gp_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 ,
LAST_UPDATE_LOGIN
)
VALUES(p_terr_gp_acct_rsc_id,
2,
p_terr_gp_acct_id,
p_resource_id,
p_rsc_group_id,
p_role_id,
'N',
'RS_EMPLOYEE',
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id
);
select jtf_tty_acct_rsc_dn_s.nextval
into p_terr_gp_acct_rsc_dn_id
from dual;
insert into jtf_tty_acct_rsc_dn
(ACCOUNT_RESOURCE_DN_ID,
OBJECT_VERSION_NUMBER ,
TERR_GROUP_ACCOUNT_ID,
RESOURCE_ID ,
RSC_GROUP_ID,
RSC_ROLE_CODE,
RSC_RESOURCE_TYPE,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(p_terr_gp_acct_rsc_dn_id,
2,
p_terr_gp_acct_id,
p_resource_id,
p_rsc_group_id,
p_role_id,
'RS_EMPLOYEE',
p_user_id,
sysdate,
p_user_id,
sysdate,
p_user_id
);
select count(*)
into p_rsc_acct_count
from jtf_tty_rsc_acct_summ
where resource_id = p_resource_id
and (rsc_group_id = p_rsc_group_id or p_rsc_group_id is null)
and rsc_resource_type = 'RS_EMPLOYEE'
and site_type_code = p_site_type_code;
insert into jtf_tty_rsc_acct_summ
(RESOURCE_ACCT_SUMM_ID,
OBJECT_VERSION_NUMBER ,
RESOURCE_ID ,
RSC_GROUP_ID,
RSC_RESOURCE_TYPE,
SITE_TYPE_CODE,
NUMBER_ACCOUNTS,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES(p_terr_gp_acct_rsc_dn_id,
2,
p_resource_id,
p_rsc_group_id,
'RS_EMPLOYEE',
p_site_type_code,
0,
p_user_id,
sysdate,
p_user_id,
sysdate,
p_user_id);
update jtf_tty_rsc_acct_summ
set NUMBER_ACCOUNTS = NUMBER_ACCOUNTS + 1
where resource_id = p_resource_id
and rsc_resource_type = 'RS_EMPLOYEE'
and site_type_code = p_site_type_code
and (rsc_group_id = p_rsc_group_id or p_rsc_group_id is null);
INSERT INTO jtf_tty_terr_grp_owners(
TERR_GROUP_OWNER_ID,
OBJECT_VERSION_NUMBER,
TERR_GROUP_ID,
RSC_GROUP_ID,
RESOURCE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
RSC_ROLE_CODE ,
RSC_RESOURCE_TYPE
)
VALUES(jtf_tty_terr_grp_owners_s.NEXTVAL,
1,
p_terr_gp_id,
p_rsc_gp_id,
p_resource_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_role_code,
'RS_EMPLOYEE');
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
)
VALUES(JTF_TTY_NAMED_ACCT_CHANGES_S.NEXTVAL,
1,
p_object_type,
p_object_id,
p_action_type,
p_from_where,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE);