[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*)
FROM JTF_TAE_1001_ACCOUNT_WINNERS
WHERE worker_id = c_worker_id
AND resource_type IN ('RS_PARTNER','RS_PARTY')
AND ROWNUM < 2;
/* This inserts into account winners */
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_START);
-- Insert into Account Accesses from Winners
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
AS_GAR_ACCOUNTS_PUB.INSERT_ACCESSES_ACCOUNTS(
x_errbuf => l_errbuf,
x_retcode => l_retcode,
p_terr_globals => l_terr_globals,
x_return_status => l_return_status);
-- Insert into territory Accesses
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
AS_GAR_ACCOUNTS_PUB.INSERT_TERR_ACCESSES_ACCOUNTS(
x_errbuf => l_errbuf,
x_retcode => l_retcode,
p_terr_globals => l_terr_globals,
x_return_status => l_return_status);
-- Insert into Account Accesses from Winners
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || 'PRM:: ' || AS_GAR.G_START);
AS_GAR_ACCOUNTS_PUB.INSERT_ACCESSES_PRM_ACCOUNTS(
x_errbuf => l_errbuf,
x_retcode => l_retcode,
p_terr_globals => l_terr_globals,
x_return_status => l_return_status);
-- Insert into territory Accesses
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || 'PRM:: ' || AS_GAR.G_START);
AS_GAR_ACCOUNTS_PUB.INSERT_TERR_ACCESSES_PRM_ACCS(
x_errbuf => l_errbuf,
x_retcode => l_retcode,
p_terr_globals => l_terr_globals,
x_return_status => l_return_status);
| INSERT these members INTO winners IF they are NOT already IN winners.
+-------------------------------------------------------------------------*/
l_errbuf VARCHAR2(4000);
SELECT count(*)
FROM JTF_TAE_1001_ACCOUNT_WINNERS
WHERE request_id = c_request_id
AND resource_type = c_resource_type
AND worker_id = c_worker_id
AND ROWNUM < 2;
SELECT count(*)
FROM JTF_TERR_RSC_ALL rsc,
JTF_TERR_DENORM_RULES_ALL rules,
JTF_TERR_RSC_ACCESS_ALL acc
WHERE rules.terr_id = rsc.terr_id
AND rsc.resource_type = 'RS_TEAM'
AND acc.access_type = 'ACCOUNT'
AND rules.source_id = -1001
AND rsc.terr_rsc_id = acc.terr_rsc_id;
INSERT INTO JTF_TAE_1001_ACCOUNT_WINNERS
(trans_object_id,
trans_detail_object_id,
terr_id,
resource_id,
resource_type,
group_id,
full_access_flag,
request_id,
program_application_id,
program_id,
program_update_date,
source_id,
trans_object_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
absolute_rank,
top_level_terr_id,
num_winners,
terr_rsc_id,
role,
primary_contact_flag,
person_id,
org_id,
worker_id)
SELECT T.trans_object_id,
T.trans_detail_object_id,
T.terr_id,
J.resource_id,
DECODE(J.resource_category,'PARTY','RS_PARTY',
'PARTNER','RS_PARTNER',
'EMPLOYEE','RS_EMPLOYEE','UNKNOWN'),
J.group_id,
T.full_access_flag,
T.request_id,
T.program_application_id,
T.program_id, T.program_update_date,
T.source_id,
T.trans_object_type_id,
SYSDATE,
T.last_updated_by,
SYSDATE,
T.created_by,
T.last_update_login,
T.absolute_rank,
T.top_level_terr_id,
T.num_winners,
T.terr_rsc_id,
T.role,
T.primary_contact_flag,
J.person_id,
T.org_id,
T.worker_id
FROM
JTF_TAE_1001_ACCOUNT_WINNERS T,
(
SELECT TM.team_resource_id resource_id,
TM.person_id person_id2,
MIN(G.group_id)group_id,
MIN(T.team_id) team_id,
TRES.category resource_category,
TRES.source_id person_id
FROM jtf_rs_team_members TM, jtf_rs_teams_b T,
jtf_rs_team_usages TU, jtf_rs_role_relations TRR,
jtf_rs_roles_b TR, jtf_rs_resource_extns TRES,
(
SELECT m.group_id group_id, m.resource_id resource_id
FROM jtf_rs_group_members m,
jtf_rs_groups_b g,
jtf_rs_group_usages u,
jtf_rs_role_relations rr,
jtf_rs_roles_b r,
jtf_rs_resource_extns res
WHERE m.group_id = g.group_id
AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
AND NVL(g.end_date_active,SYSDATE)
AND u.group_id = g.group_id
AND u.usage IN ('SALES','PRM')
AND m.group_member_id = rr.role_resource_id
AND rr.role_resource_type = 'RS_GROUP_MEMBER'
AND rr.delete_flag <> 'Y'
AND SYSDATE BETWEEN rr.start_date_active
AND NVL(rr.end_date_active,SYSDATE)
AND rr.role_id = r.role_id
AND r.role_type_code
IN ('SALES', 'TELESALES', 'FIELDSALES','PRM')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.category IN ('EMPLOYEE','PARTY','PARTNER')
) G
WHERE tm.team_id = t.team_id
AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
AND NVL(t.end_date_active,SYSDATE)
AND tu.team_id = t.team_id
AND tu.usage IN ('SALES','PRM')
AND tm.team_member_id = trr.role_resource_id
AND tm.delete_flag <> 'Y'
AND tm.resource_type = 'INDIVIDUAL'
AND trr.role_resource_type = 'RS_TEAM_MEMBER'
AND trr.delete_flag <> 'Y'
AND SYSDATE BETWEEN trr.start_date_active AND
NVL(trr.end_date_active,SYSDATE)
AND trr.role_id = tr.role_id
AND tr.role_type_code IN
('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND tr.active_flag = 'Y'
AND tres.resource_id = tm.team_resource_id
AND tres.category IN ('EMPLOYEE','PARTY','PARTNER')
AND tm.team_resource_id = g.resource_id
GROUP BY tm.team_resource_id,
tm.person_id,
tres.category,
tres.source_id
UNION ALL
SELECT MIN(m.resource_id) resource_id,
MIN(m.person_id) person_id2, MIN(m.group_id) group_id,
MIN(jtm.team_id) team_id, res.category resource_category,
MIN(res.source_id) person_id
FROM jtf_rs_group_members m, jtf_rs_groups_b g,
jtf_rs_group_usages u, jtf_rs_role_relations rr,
jtf_rs_roles_b r, jtf_rs_resource_extns res,
(
SELECT tm.team_resource_id group_id,
t.team_id team_id
FROM jtf_rs_team_members tm, jtf_rs_teams_b t,
jtf_rs_team_usages tu,jtf_rs_role_relations trr,
jtf_rs_roles_b tr, jtf_rs_resource_extns tres
WHERE tm.team_id = t.team_id
AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
AND NVL(t.end_date_active,SYSDATE)
AND tu.team_id = t.team_id
AND tu.usage IN ('SALES','PRM')
AND tm.team_member_id = trr.role_resource_id
AND tm.delete_flag <> 'Y'
AND tm.resource_type = 'GROUP'
AND trr.role_resource_type = 'RS_TEAM_MEMBER'
AND trr.delete_flag <> 'Y'
AND SYSDATE BETWEEN trr.start_date_active
AND NVL(trr.end_date_active,SYSDATE)
AND trr.role_id = tr.role_id
AND tr.role_type_code IN
('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND tr.active_flag = 'Y'
AND tres.resource_id = tm.team_resource_id
AND tres.category IN ('EMPLOYEE','PARTY','PARTNER')
) jtm
WHERE m.group_id = g.group_id
AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
AND NVL(g.end_date_active,SYSDATE)
AND u.group_id = g.group_id
AND u.usage IN ('SALES','PRM')
AND m.group_member_id = rr.role_resource_id
AND rr.role_resource_type = 'RS_GROUP_MEMBER'
AND rr.delete_flag <> 'Y'
AND SYSDATE BETWEEN rr.start_date_active AND
NVL(rr.end_date_active,SYSDATE)
AND rr.role_id = r.role_id
AND r.role_type_code IN
('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.category IN ('EMPLOYEE','PARTY','PARTNER')
AND jtm.group_id = g.group_id
GROUP BY m.resource_id, m.person_id,
jtm.team_id, res.category) J
WHERE j.team_id = t.resource_id
AND t.request_id = l_request_id
AND t.worker_id = l_worker_id
AND t.resource_type = 'RS_TEAM'
AND NOT EXISTS (SELECT 1 FROM JTF_TAE_1001_ACCOUNT_WINNERS rt1
WHERE rt1.resource_id = j.resource_id
AND NVL(rt1.group_id,-1) = NVL(j.group_id,-1)
AND rt1.request_id = t.request_id
AND rt1.worker_id = t.worker_id
AND rt1.trans_object_id = t.trans_object_id
AND NVL(rt1.trans_detail_object_id,-1) =
NVL(t.trans_detail_object_id,-1));
| a sales ROLE AND are either an employee OR partner AND INSERT back INTO
| winners IF they are NOT already IN winners.
+-------------------------------------------------------------------------*/
l_errbuf VARCHAR2(4000);
SELECT count(*)
FROM JTF_TAE_1001_ACCOUNT_WINNERS
WHERE request_id = c_request_id
AND resource_type = c_resource_type
AND worker_id = c_worker_id
AND ROWNUM < 2;
SELECT count(*)
FROM JTF_TERR_RSC_ALL rsc,
JTF_TERR_DENORM_RULES_ALL rules,
JTF_TERR_RSC_ACCESS_ALL acc
WHERE rules.terr_id = rsc.terr_id
AND rsc.resource_type = 'RS_GROUP'
AND acc.access_type = 'ACCOUNT'
AND rules.source_id = -1001
AND rsc.terr_rsc_id = acc.terr_rsc_id;
INSERT INTO JTF_TAE_1001_ACCOUNT_WINNERS
(trans_object_id,
trans_detail_object_id,
terr_id,
resource_id,
resource_type,
group_id,
full_access_flag,
request_id,
program_application_id,
program_id,
program_update_date,
source_id,
trans_object_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
absolute_rank,
top_level_terr_id,
num_winners,
terr_rsc_id,
role,
primary_contact_flag,
person_id,
org_id,
worker_id)
SELECT T.trans_object_id,
T.trans_detail_object_id,
T.terr_id,
J.resource_id,
DECODE(J.resource_category,'PARTY','RS_PARTY',
'PARTNER','RS_PARTNER',
'EMPLOYEE','RS_EMPLOYEE','UNKNOWN'),
J.group_id,
T.full_access_flag,
T.request_id,
T.program_application_id,
T.program_id,
T.program_update_date,
T.source_id,
T.trans_object_type_id,
SYSDATE,
T.last_updated_by,
SYSDATE,
T.created_by,
T.last_update_login,
T.absolute_rank,
T.top_level_terr_id,
T.num_winners,
T.terr_rsc_id,
T.role,
T.primary_contact_flag,
J.person_id,
T.org_id,
T.worker_id
FROM
JTF_TAE_1001_ACCOUNT_WINNERS t,
(
SELECT MIN(m.resource_id) resource_id,
res.CATEGORY resource_category,
m.group_id group_id, MIN(res.source_id) person_id
FROM jtf_rs_group_members m, jtf_rs_groups_b g,
jtf_rs_group_usages u, jtf_rs_role_relations rr,
jtf_rs_roles_b r, jtf_rs_resource_extns res
WHERE m.group_id = g.group_id
AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
AND NVL(g.end_date_active,SYSDATE)
AND u.group_id = g.group_id
AND u.usage IN ('SALES','PRM')
AND m.group_member_id = rr.role_resource_id
AND rr.role_resource_type = 'RS_GROUP_MEMBER'
AND rr.role_id = r.role_id
AND rr.delete_flag <> 'Y'
AND SYSDATE BETWEEN rr.start_date_active AND
NVL(rr.end_date_active,SYSDATE)
AND r.role_type_code IN
('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.category IN ('EMPLOYEE','PARTY','PARTNER')
GROUP BY m.group_member_id, m.resource_id, m.person_id,
m.group_id, res.category) j
WHERE j.group_id = t.resource_id
AND t.request_id = l_request_id
AND t.worker_id = l_worker_id
AND t.resource_type = 'RS_GROUP'
AND NOT EXISTS (SELECT 1 FROM JTF_TAE_1001_ACCOUNT_WINNERS rt1
WHERE rt1.resource_id = j.resource_id
AND NVL(rt1.group_id,-1) = NVL(j.group_id,-1)
AND rt1.request_id = t.request_id
AND rt1.worker_id = t.worker_id
AND rt1.trans_object_id = t.trans_object_id
AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
| Try 3 times to bulk update acesses
| if all 3 attempts fail because of deadlock:
| Update on record at a time.
+-------------------------------------------------------------------------*/
PROCEDURE SET_TEAM_LEAD_ACCOUNTS(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
p_terr_globals IN AS_GAR.TERR_GLOBALS,
x_return_status OUT NOCOPY VARCHAR2)
IS
TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
SELECT /*+ LEADING(WIN) */ A.customer_id, -- The use nested loop hint is removed in ACCOUNTS ONLY..
A.salesforce_id,
A.sales_group_id,
NVL(WIN.full_access_flag,'N')
FROM AS_ACCESSES_ALL_ALL A,
JTF_TAE_1001_ACCOUNT_WINNERS WIN
WHERE A.lead_id is NULL
AND A.delete_flag is NULL
AND A.sales_lead_id is NULL
AND NVL(A.team_leader_flag,'N') <> NVL(WIN.full_access_flag,'N')
AND WIN.SOURCE_ID = -1001
AND WIN.worker_id = c_worker_id
AND WIN.resource_type ='RS_EMPLOYEE'
AND WIN.trans_object_id = A.customer_id
AND WIN.resource_id = A.salesforce_id
AND WIN.group_id = A.sales_group_id
GROUP BY A.customer_id,
A.salesforce_id,
A.sales_group_id,
WIN.full_access_flag;
l_customer_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
l_faf.DELETE;
UPDATE AS_ACCESSES_ALL_ALL ACC
SET object_version_number = NVL(object_version_number,0) + 1,
ACC.last_update_date = SYSDATE,
ACC.last_updated_by = p_terr_globals.user_id,
ACC.last_update_login = p_terr_globals.last_update_login,
ACC.request_id = p_terr_globals.request_id,
ACC.program_application_id = p_terr_globals.prog_appl_id,
ACC.program_id = p_terr_globals.prog_id,
ACC.program_update_date = SYSDATE,
ACC.team_leader_flag = l_faf(i)
WHERE ACC.lead_id is NULL
AND ACC.delete_flag is NULL
AND ACC.sales_lead_id is NULL
AND ACC.customer_id = l_customer_id(i)
AND ACC.salesforce_id = l_salesforce_id(i)
AND ACC.sales_group_id = l_sales_group_id(i);
UPDATE AS_ACCESSES_ALL_ALL ACC
SET object_version_number = NVL(object_version_number,0) + 1,
ACC.last_update_date = SYSDATE,
ACC.last_updated_by = p_terr_globals.user_id,
ACC.last_update_login = p_terr_globals.last_update_login,
ACC.request_id = p_terr_globals.request_id,
ACC.program_application_id = p_terr_globals.prog_appl_id,
ACC.program_id = p_terr_globals.prog_id,
ACC.program_update_date = SYSDATE,
ACC.team_leader_flag = l_faf(i)
WHERE ACC.lead_id is NULL
AND ACC.delete_flag is NULL
AND ACC.sales_lead_id is NULL
AND ACC.customer_id = l_customer_id(i)
AND ACC.salesforce_id = l_salesforce_id(i)
AND ACC.sales_group_id = l_sales_group_id(i);
l_customer_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
l_faf.DELETE;
/************************** Start Insert Into Entity Accesses*************/
PROCEDURE INSERT_ACCESSES_ACCOUNTS(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
p_terr_globals IN AS_GAR.TERR_GLOBALS,
x_return_status OUT NOCOPY VARCHAR2)
IS
TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
SELECT W.resource_id,
W.group_id grp_id,
MIN(W.person_id) person_id,
W.trans_object_id cus_id,
MIN(W.trans_detail_object_id) add_id,
MAX(W.full_access_flag) faf,
W.resource_type,
org_id,
NULL
FROM JTF_TAE_1001_ACCOUNT_WINNERS W
WHERE W.source_id = -1001
AND W.worker_id = c_worker_id
AND W.resource_type ='RS_EMPLOYEE'
AND W.group_id is NOT NULL
GROUP BY W.trans_object_id,
W.resource_id,
W.group_id,
W.resource_type,
W.org_id;
SELECT W.RESOURCE_ID,
W.GROUP_ID GRP_ID,
MIN(W.PERSON_ID) PERSON_ID,
W.TRANS_OBJECT_ID CUS_ID,
MIN(W.TRANS_DETAIL_OBJECT_ID) ADD_ID,
MAX(W.FULL_ACCESS_FLAG) FAF,
W.RESOURCE_TYPE,
ORG_ID,
ATR.RESOURCE_TYPE SALESFORCE_ROLE_CODE
FROM JTF_TAE_1001_ACCOUNT_WINNERS W,
AS_TERR_RESOURCES_TMP ATR
WHERE W.SOURCE_ID = -1001
AND W.worker_id = c_worker_id
AND W.RESOURCE_TYPE = 'RS_EMPLOYEE'
AND W.PERSON_ID=ATR.RESOURCE_ID(+)
GROUP BY W.TRANS_OBJECT_ID,
W.RESOURCE_ID,
W.GROUP_ID,
W.RESOURCE_TYPE,
W.ORG_ID,
ATR.RESOURCE_TYPE;
select 'Y'
from dual
where exists ( SELECT 'Y'
FROM FND_PROFILE_OPTION_VALUES VAL,
FND_PROFILE_OPTIONS OPTIONS,
FND_USER USERS
WHERE VAL.LEVEL_ID = 10004
AND USERS.USER_ID = VAL.LEVEL_VALUE
AND OPTIONS.PROFILE_OPTION_ID = VAL.PROFILE_OPTION_ID
AND OPTIONS.APPLICATION_ID = VAL.APPLICATION_ID
AND OPTIONS.PROFILE_OPTION_NAME = 'AS_DEF_CUST_ST_ROLE');
| inserted into accesses along with the default role for every user and bulk
| collect into an array.
| If it not set, then do the same, except that in this case, the default role
| will always be null.
| Try bulk inserting into accesses. If this fails, insert records one by one.
|
+-------------------------------------------------------------------------*/
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_START);
l_customer_id.DELETE;
l_org_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
l_person_id.DELETE;
l_address_id.DELETE;
l_faf.DELETE;
l_resource_type.DELETE;
l_src.DELETE;
INSERT INTO AS_ACCESSES_ALL_ALL
( access_id
,access_type
,salesforce_id
,sales_group_id
,person_id
,salesforce_role_code
,customer_id
,address_id
,freeze_flag
,reassign_flag
,team_leader_flag
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,created_by_tap_flag
,org_id
)
(
SELECT as_accesses_s.nextval
,'X'
,l_salesforce_id(i)
,l_sales_group_id(i)
,l_person_id(i)
,l_src(i)
,l_customer_id(i)
,l_address_id(i)
,'N'
,'N'
,l_faf(i)
,SYSDATE
,p_terr_globals.user_id
,SYSDATE
,p_terr_globals.user_id
,p_terr_globals.last_update_login
,p_terr_globals.request_id
,p_terr_globals.prog_appl_id
,p_terr_globals.prog_id
,SYSDATE
,'Y'
,l_org_id(i)
FROM DUAL
WHERE NOT EXISTS ( SELECT 'X'
FROM AS_ACCESSES_ALL_ALL AA
WHERE AA.sales_lead_id IS NULL
AND AA.lead_id IS NULL
AND AA.delete_flag is NULL
AND AA.customer_id = l_customer_id(i)
AND AA.salesforce_id = l_salesforce_id(i)
AND AA.sales_group_id = l_sales_group_id(i)
)
);
INSERT INTO AS_ACCESSES_ALL_ALL
( access_id
,access_type
,salesforce_id
,sales_group_id
,person_id
,salesforce_role_code
,customer_id
,address_id
,freeze_flag
,reassign_flag
,team_leader_flag
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,created_by_tap_flag
,org_id
)
(
SELECT as_accesses_s.NEXTVAL
,'X'
,l_salesforce_id(i)
,l_sales_group_id(i)
,l_person_id(i)
,l_src(i)
,l_customer_id(i)
,l_address_id(i)
,'N'
,'N'
,l_faf(i)
,SYSDATE
,p_terr_globals.user_id
,SYSDATE
,p_terr_globals.user_id
,p_terr_globals.last_update_login
,p_terr_globals.request_id
,p_terr_globals.prog_appl_id
,p_terr_globals.prog_id
,SYSDATE
,'Y'
,l_org_id(i)
from dual
where not exists ( SELECT 'X'
FROM AS_ACCESSES_ALL_ALL AA
WHERE AA.sales_lead_id IS NULL
AND AA.lead_id IS NULL
AND AA.delete_flag is NULL
AND AA.customer_id = l_customer_id(i)
AND AA.salesforce_id = l_salesforce_id(i)
AND AA.sales_group_id = l_sales_group_id(i)
)
);
END LOOP; /* loop for DUP_VAL_ON_INDEX individual insert */
l_customer_id.DELETE;
l_org_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
l_person_id.DELETE;
l_address_id.DELETE;
l_faf.DELETE;
l_src.DELETE;
l_resource_type.DELETE;
END INSERT_ACCESSES_ACCOUNTS;
/************************** End Insert Into Entity Accesses*************/
/************************** Start Insert Into Terr Accesses*************/
PROCEDURE INSERT_TERR_ACCESSES_ACCOUNTS(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
p_terr_globals IN AS_GAR.TERR_GLOBALS,
x_return_status OUT NOCOPY VARCHAR2)
IS
TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
SELECT w.terr_id
,w.trans_object_id
,w.resource_id
,w.group_id
FROM JTF_TAE_1001_ACCOUNT_WINNERS W
WHERE W.source_id = -1001
AND W.worker_id = c_worker_id
AND W.resource_type = 'RS_EMPLOYEE'
AND W.group_id IS NOT NULL --- Added to work around the JTY functionality which allows group_id to be NULL during setup of resources.
GROUP BY W.terr_id,
W.trans_object_id,
W.resource_id,
W.group_id;
| Almost the same as accesses, except the insertion is into as_territory_accesses
| and there is no involvement of role.
|
+-------------------------------------------------------------------------*/
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
l_customer_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
l_terr_id.DELETE;
INSERT INTO AS_TERRITORY_ACCESSES
( access_id,
territory_id,
user_territory_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
(
SELECT
V.acc_id,
l_terr_id(i),
l_terr_id(i),
SYSDATE,
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
p_terr_globals.last_update_login,
p_terr_globals.request_id,
p_terr_globals.prog_appl_id,
p_terr_globals.prog_id,
SYSDATE
FROM
( SELECT DISTINCT a.access_id acc_id
FROM AS_ACCESSES_ALL_ALL A
WHERE A.customer_id=l_customer_id(i)
AND A.delete_flag is NULL
AND NVL(A.sales_group_id,-777) = NVL(l_sales_group_id(i),-777)
AND A.salesforce_id=l_salesforce_id(i)
AND A.sales_lead_id IS NULL
AND A.lead_id IS NULL
AND NOT EXISTS
(SELECT 'X'
FROM AS_TERRITORY_ACCESSES AST
WHERE AST.access_id = A.access_id
AND AST.territory_id = l_terr_id(i))
) V
);
INSERT INTO AS_TERRITORY_ACCESSES
( access_id,
territory_id,
user_territory_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
(
SELECT
v.acc_id,
l_terr_id(i),
l_terr_id(i),
SYSDATE,
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
p_terr_globals.last_update_login,
p_terr_globals.request_id,
p_terr_globals.prog_appl_id,
p_terr_globals.prog_id,
SYSDATE
FROM
( SELECT distinct A.access_id acc_id
FROM AS_ACCESSES_ALL_ALL A
WHERE A.customer_id=l_customer_id(i)
AND A.delete_flag is NULL
AND NVL(A.sales_group_id,-777) = NVL(l_sales_group_id(i),-777)
AND A.salesforce_id=l_salesforce_id(i)
AND A.sales_lead_id is NULL
AND A.lead_id is NULL
AND NOT EXISTS
(SELECT 'X'
FROM AS_TERRITORY_ACCESSES AST
WHERE AST.access_id = A.access_id
AND AST.territory_id = l_terr_id(i))
) V
);
END LOOP; -- end loop for insert into territory accesses
l_customer_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
l_terr_id.DELETE;
END INSERT_TERR_ACCESSES_ACCOUNTS;
/************************** End Insert Into Terr Accesses*************/
/************************** Start Set Accounts Team Leader PRM****************/
/*-------------------------------------------------------------------------+
| PROGRAM LOGIC
|
| Winners table records are striped by worker id.
| All the logic pertains to what happens within a single worker.
| Get a list of resources who are marked as full access in winners but are
| not marked as full access in accesses (CURSOR team_leader).
| Loop within the worker for sets of records within winners ---?
| Bulk collect from team_leader cursor into array.
| Break up the array into batches based on global var bulk_size.
| For each batch:
| Try 3 times to bulk update acesses
| if all 3 attempts fail because of deadlock:
| Update on record at a time.
+-------------------------------------------------------------------------*/
PROCEDURE SET_TEAM_LEAD_PRM_ACCOUNTS(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
p_terr_globals IN AS_GAR.TERR_GLOBALS,
x_return_status OUT NOCOPY VARCHAR2)
IS
TYPE customer_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
SELECT /*+ LEADING(WIN) USE_NL(A WIN) INDEX(A) */ A.customer_id,
A.salesforce_id,
A.sales_group_id,
A.partner_customer_id,
A.partner_cont_party_id,
NVL(WIN.full_access_flag,'N')
FROM AS_ACCESSES_ALL_ALL A,
JTF_TAE_1001_ACCOUNT_WINNERS WIN,
JTF_RS_ROLE_RELATIONS REL,
JTF_RS_ROLES_B ROL
WHERE WIN.source_id = -1001
AND A.delete_flag is NULL
AND WIN.worker_id = c_worker_id
AND NVL(A.team_leader_flag,'N') <> NVL(WIN.full_access_flag,'N')
AND WIN.resource_type in ('RS_PARTNER','RS_PARTY')
AND WIN.resource_id=REL.role_resource_id
AND REL.role_id=ROL.role_id
AND ROL.role_type_code='PRM'
AND REL.role_resource_type='RS_INDIVIDUAL'
AND REL.delete_flag <> 'Y'
AND SYSDATE between REL.start_date_active
AND NVL(REL.end_date_active,SYSDATE)
AND WIN.trans_object_id = A.customer_id
AND WIN.resource_id = A.salesforce_id
AND NVL(WIN.group_id,-777) = NVL(A.sales_group_id,-777)
AND A.lead_id is NULL
AND A.sales_lead_id is NULL
AND (A.partner_customer_id IS NOT NULL OR A.partner_cont_party_id IS NOT NULL )
GROUP BY A.customer_id,
A.salesforce_id,
A.sales_group_id,
A.org_id,
A.partner_customer_id,
A.partner_cont_party_id,
WIN.full_access_flag;
l_customer_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
l_part_cust_id.DELETE;
l_part_cont_party_id.DELETE;
l_faf.DELETE;
UPDATE AS_ACCESSES_ALL_ALL ACC
SET object_version_number = NVL(object_version_number,0) + 1,
ACC.last_update_date = SYSDATE,
ACC.last_updated_by = p_terr_globals.user_id,
ACC.last_update_login = p_terr_globals.last_update_login,
ACC.request_id = p_terr_globals.request_id,
ACC.program_application_id = p_terr_globals.prog_appl_id,
ACC.program_id = p_terr_globals.prog_id,
ACC.program_update_date = SYSDATE,
ACC.team_leader_flag = l_faf(i)
WHERE ACC.lead_id is NULL
AND ACC.sales_lead_id is NULL
AND ACC.delete_flag is NULL
AND ACC.customer_id = l_customer_id(i)
AND ACC.salesforce_id = l_salesforce_id(i)
AND NVL(ACC.sales_group_id,-777) = NVL(l_sales_group_id(i),-777)
AND (NVL(ACC.partner_customer_id,-777)= NVL(l_part_cust_id(i),-777)
OR NVL(ACC.partner_cont_party_id,-777)=NVL(l_part_cont_party_id(i),-777));
UPDATE AS_ACCESSES_ALL_ALL ACC
SET object_version_number = NVL(object_version_number,0) + 1,
ACC.last_update_date = SYSDATE,
ACC.last_updated_by = p_terr_globals.user_id,
ACC.last_update_login = p_terr_globals.last_update_login,
ACC.request_id = p_terr_globals.request_id,
ACC.program_application_id = p_terr_globals.prog_appl_id,
ACC.program_id = p_terr_globals.prog_id,
ACC.program_update_date = SYSDATE,
ACC.team_leader_flag = l_faf(i)
WHERE ACC.lead_id is NULL
AND ACC.delete_flag is NULL
AND ACC.sales_lead_id is NULL
AND ACC.customer_id = l_customer_id(i)
AND ACC.salesforce_id = l_salesforce_id(i)
AND NVL(ACC.sales_group_id,-777) = NVL(l_sales_group_id(i),-777)
AND (NVL(ACC.partner_customer_id,-777)= NVL(l_part_cust_id(i),-777)
OR NVL(ACC.partner_cont_party_id,-777)=NVL(l_part_cont_party_id(i),-777));
l_customer_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
l_part_cust_id.DELETE;
l_part_cont_party_id.DELETE;
l_faf.DELETE;
/************************** Start Insert Into Entity Accesses PRM ***********/
PROCEDURE INSERT_ACCESSES_PRM_ACCOUNTS(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
p_terr_globals IN AS_GAR.TERR_GLOBALS,
x_return_status OUT NOCOPY VARCHAR2)
IS
TYPE customer_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
SELECT W.resource_id,
W.group_id grp_id,
MIN(W.person_id) person_id,
W.trans_object_id cus_id,
MIN(W.trans_detail_object_id) add_id,
MAX(W.full_access_flag) faf,
W.org_id,
W.resource_type,
RES.source_id
FROM JTF_TAE_1001_ACCOUNT_WINNERS W,
JTF_RS_RESOURCE_EXTNS RES,
JTF_RS_ROLE_RELATIONS REL,
JTF_RS_ROLES_B ROL
WHERE W.source_id = -1001
AND W.resource_id=REL.role_resource_id
AND W.worker_id = c_worker_id
AND W.resource_type in ('RS_PARTY','RS_PARTNER')
AND REL.role_id=ROL.role_id
AND ROL.role_type_code='PRM'
AND REL.role_resource_type='RS_INDIVIDUAL'
AND REL.delete_flag <> 'Y'
AND SYSDATE between REL.start_date_active
AND NVL(REL.end_date_active,SYSDATE)
AND W.resource_id=RES.resource_id
AND NOT EXISTS ( SELECT 1
FROM AS_ACCESSES_ALL_ALL AA
WHERE AA.sales_lead_id IS NULL
AND AA.lead_id IS NULL
AND (AA.partner_customer_id IS NOT NULL OR AA.partner_cont_party_id IS NOT NULL )
AND AA.customer_id = W.trans_object_id
AND AA.salesforce_id = W.resource_id
AND NVL(AA.sales_group_id,-777) = NVL(W.group_id,-777)
AND AA.delete_flag is NULL
)
GROUP BY W.trans_object_id,
W.resource_id,
W.group_id,
W.org_id,
W.resource_type,
RES.source_id;
| Try bulk inserting into accesses. If this fails, insert records one by one.
|
+-------------------------------------------------------------------------*/
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'PRM::' || AS_GAR.G_START);
l_customer_id.DELETE;
l_org_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
l_person_id.DELETE;
l_address_id.DELETE;
l_faf.DELETE;
l_src.DELETE;
l_resource_type.DELETE;
INSERT INTO AS_ACCESSES_ALL_ALL
( access_id
,access_type
,salesforce_id
,sales_group_id
,person_id
,customer_id
,address_id
,freeze_flag
,reassign_flag
,team_leader_flag
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,created_by_tap_flag
,org_id
,partner_customer_id
,partner_cont_party_id
)
(
SELECT as_accesses_s.NEXTVAL
,'X'
,l_salesforce_id(i)
,l_sales_group_id(i)
,null
,l_customer_id(i)
,l_address_id(i)
,'N'
,'N'
,l_faf(i)
,SYSDATE
,p_terr_globals.user_id
,SYSDATE
,p_terr_globals.user_id
,p_terr_globals.last_update_login
,p_terr_globals.request_id
,p_terr_globals.prog_appl_id
,p_terr_globals.prog_id
,SYSDATE
,'Y'
,l_org_id(i)
,DECODE(l_resource_type(i),'RS_PARTNER',l_src(i),NULL)
,DECODE(l_resource_type(i),'RS_PARTY',l_src(i),NULL)
FROM DUAL
);
INSERT INTO AS_ACCESSES_ALL_ALL
( access_id
,access_type
,salesforce_id
,sales_group_id
,person_id
,customer_id
,address_id
,freeze_flag
,reassign_flag
,team_leader_flag
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,created_by_tap_flag
,org_id
,partner_customer_id
,partner_cont_party_id
)
(
SELECT as_accesses_s.NEXTVAL
,'X'
,l_salesforce_id(i)
,l_sales_group_id(i)
,null
,l_customer_id(i)
,l_address_id(i)
,'N'
,'N'
,l_faf(i)
,SYSDATE
,p_terr_globals.user_id
,SYSDATE
,p_terr_globals.user_id
,p_terr_globals.last_update_login
,p_terr_globals.request_id
,p_terr_globals.prog_appl_id
,p_terr_globals.prog_id
,SYSDATE
,'Y'
,l_org_id(i)
,DECODE(l_resource_type(i),'RS_PARTNER',l_src(i),NULL)
,DECODE(l_resource_type(i),'RS_PARTY',l_src(i),NULL)
FROM DUAL
);
END LOOP; /* loop for DUP_VAL_ON_INDEX individual insert */
l_customer_id.DELETE;
l_org_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
l_person_id.DELETE;
l_address_id.DELETE;
l_faf.DELETE;
l_src.DELETE;
l_resource_type.DELETE;
END INSERT_ACCESSES_PRM_ACCOUNTS;
/************************** End Insert Into Entity Accesses PRM ********/
/************************** Start Insert Into Terr Accesses PRM *********/
PROCEDURE INSERT_TERR_ACCESSES_PRM_ACCS(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
p_terr_globals IN AS_GAR.TERR_GLOBALS,
x_return_status OUT NOCOPY VARCHAR2)
IS
TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
SELECT w.terr_id
,w.trans_object_id
,w.resource_id
,w.group_id
FROM JTF_TAE_1001_ACCOUNT_WINNERS W,JTF_RS_ROLE_RELATIONS REL,JTF_RS_ROLES_B ROL
WHERE W.source_id = -1001
AND W.worker_id = c_worker_id
AND W.resource_type in ('RS_PARTNER','RS_PARTY')
AND W.resource_id=REL.role_resource_id
AND REL.role_id=ROL.role_id
AND ROL.role_type_code='PRM'
AND REL.role_resource_type='RS_INDIVIDUAL'
AND REL.delete_flag <> 'Y'
AND SYSDATE between REL.start_date_active
AND NVL(REL.end_date_active,SYSDATE)
GROUP BY W.terr_id,
W.trans_object_id,
W.resource_id,
W.group_id;
| Almost the same as accesses, except the insertion is into as_territory_accesses
| and there is no involvement of role.
|
+-------------------------------------------------------------------------*/
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || AS_GAR.G_START);
l_terr_id.DELETE;
l_customer_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
INSERT INTO AS_TERRITORY_ACCESSES
( access_id,
territory_id,
user_territory_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
(
SELECT
V.acc_id,
l_terr_id(i),
l_terr_id(i),
SYSDATE,
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
p_terr_globals.last_update_login,
p_terr_globals.request_id,
p_terr_globals.prog_appl_id,
p_terr_globals.prog_id,
SYSDATE
FROM
( SELECT distinct A.access_id acc_id
FROM AS_ACCESSES_ALL_ALL A
WHERE A.customer_id=l_customer_id(i)
AND (A.partner_customer_id is NOT NULL OR A.partner_cont_party_id is NOT NULL )
AND A.delete_flag is NULL
AND NVL(A.sales_group_id,-777)=NVL(l_sales_group_id(i),-777)
AND A.salesforce_id=l_salesforce_id(i)
AND A.sales_lead_id is NULL
AND A.lead_id is NULL
AND NOT EXISTS
(SELECT 'X'
FROM AS_TERRITORY_ACCESSES AST
WHERE AST.access_id = A.access_id
AND AST.territory_id = l_terr_id(i))
) V
);
INSERT INTO AS_TERRITORY_ACCESSES
( access_id,
territory_id,
user_territory_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
(
SELECT
V.acc_id,
l_terr_id(i),
l_terr_id(i),
SYSDATE,
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
p_terr_globals.last_update_login,
p_terr_globals.request_id,
p_terr_globals.prog_appl_id,
p_terr_globals.prog_id,
SYSDATE
FROM
( SELECT distinct A.access_id acc_id
FROM AS_ACCESSES_ALL_ALL A
WHERE A.customer_id=l_customer_id(i)
AND A.delete_flag is NULL
AND (A.partner_customer_id is NOT NULL OR A.partner_cont_party_id is NOT NULL )
AND NVL(A.sales_group_id,-777)=NVL(l_sales_group_id(i),-777)
AND A.salesforce_id=l_salesforce_id(i)
AND A.sales_lead_id is NULL
AND A.lead_id is NULL
AND NOT EXISTS
(SELECT 'X'
FROM AS_TERRITORY_ACCESSES AST
WHERE AST.access_id = A.access_id
AND AST.territory_id = l_terr_id(i))
) V
);
END LOOP; -- end loop for insert into territory accesses
l_terr_id.DELETE;
l_customer_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
END INSERT_TERR_ACCESSES_PRM_ACCS;
/************************** End Insert Into Terr Accesses PRM ********/
/************************** Start Account Cleanup ***********************/
PROCEDURE Perform_Account_Cleanup(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
p_terr_globals IN AS_GAR.TERR_GLOBALS,
x_return_status OUT NOCOPY VARCHAR2)
IS
TYPE customer_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
SELECT distinct trans_object_id
FROM JTF_TAE_1001_ACCOUNT_TRANS
WHERE worker_id=c_worker_id;
SELECT distinct trans_object_id
FROM JTF_TAE_1001_ACCOUNT_NM_TRANS
WHERE worker_id=c_worker_id;
UPDATE AS_ACCESSES_ALL_ALL ACC
SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
WHERE ACC.customer_id=l_customer_id(i)
AND ACC.delete_flag is NULL
AND NVL(ACC.freeze_flag,'N') = 'N'
AND ACC.lead_id IS NULL
AND ACC.sales_lead_id IS NULL
AND NOT EXISTS (SELECT 'X'
FROM JTF_TAE_1001_ACCOUNT_WINNERS W
WHERE W.trans_object_id = ACC.customer_id
AND W.worker_id = l_worker_id
AND W.resource_id = ACC.salesforce_id
AND NVL(W.group_id,-777)=NVL(ACC.sales_group_id,-777))
AND ROWNUM < G_DEL_REC;
DELETE FROM AS_ACCESSES_ALL_ALL ACC
WHERE ACC.customer_id=l_customer_id(i)
AND NVL(ACC.freeze_flag,'N') = 'N'
AND ACC.lead_id IS NULL
AND ACC.delete_flag is NULL
AND ACC.sales_lead_id IS NULL
AND NOT EXISTS (SELECT 'X'
FROM JTF_TAE_1001_ACCOUNT_WINNERS W
WHERE W.trans_object_id = ACC.customer_id
AND W.worker_id = l_worker_id
AND W.resource_id = ACC.salesforce_id
AND NVL(W.group_id,-777)= NVL(ACC.sales_group_id,-777))
AND ROWNUM < G_DEL_REC;
UPDATE AS_ACCESSES_ALL_ALL ACC
SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
WHERE ACC.customer_id=l_customer_id(i)
AND ACC.delete_flag is NULL
AND NVL(ACC.freeze_flag,'N') = 'N'
AND ACC.lead_id IS NULL
AND ACC.sales_lead_id IS NULL
AND NOT EXISTS (SELECT 'X'
FROM JTF_TAE_1001_ACCOUNT_WINNERS W
WHERE W.trans_object_id = ACC.customer_id
AND W.worker_id = l_worker_id
AND W.resource_id = ACC.salesforce_id
AND NVL(W.group_id,-777)= NVL(ACC.sales_group_id,-777));
DELETE FROM AS_ACCESSES_ALL_ALL ACC
WHERE ACC.customer_id=l_customer_id(i)
AND NVL(ACC.freeze_flag,'N') = 'N'
AND ACC.delete_flag is NULL
AND ACC.lead_id IS NULL
AND ACC.sales_lead_id IS NULL
AND NOT EXISTS (SELECT 'X'
FROM JTF_TAE_1001_ACCOUNT_WINNERS W
WHERE W.trans_object_id = ACC.customer_id
AND W.worker_id = l_worker_id
AND W.resource_id = ACC.salesforce_id
AND NVL(W.group_id,-777)= NVL(ACC.sales_group_id,-777));