[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* This inserts into Lead winners */
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_START);
-- Insert into Lead Accesses from Winners
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
AS_GAR_LEADS_PUB.INSERT_ACCESSES_LEADS(
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_LEADS_PUB.INSERT_TERR_ACCESSES_LEADS(
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_LEAD_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 = 'LEAD'
AND rules.source_id = -1001
AND rsc.terr_rsc_id = acc.terr_rsc_id;
INSERT INTO JTF_TAE_1001_LEAD_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_LEAD_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,
MIN(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_LEAD_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_LEAD_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 = 'LEAD'
AND rules.source_id = -1001
AND rsc.terr_rsc_id = acc.terr_rsc_id ;
INSERT INTO JTF_TAE_1001_LEAD_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_LEAD_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_LEAD_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_LEADS(
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) USE_NL(A WIN) INDEX(A) */ A.sales_lead_id,
A.salesforce_id,
A.sales_group_id,
NVL(WIN.full_access_flag,'N')
FROM AS_ACCESSES_ALL_ALL A,
JTF_TAE_1001_LEAD_WINNERS WIN
WHERE A.lead_id is NULL
AND A.sales_lead_id is NOT NULL
AND A.delete_flag 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.sales_lead_id
AND WIN.resource_id = A.salesforce_id
AND WIN.group_id = A.sales_group_id
GROUP BY A.sales_lead_id,
A.salesforce_id,
A.sales_group_id,
WIN.full_access_flag;
l_sales_lead_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.sales_lead_id is NOT NULL
AND ACC.sales_lead_id = l_sales_lead_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.sales_lead_id is NOT NULL
AND ACC.sales_lead_id = l_sales_lead_id(i)
AND ACC.salesforce_id = l_salesforce_id(i)
AND ACC.sales_group_id = l_sales_group_id(i);
l_sales_lead_id.DELETE;
l_faf.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
/************************** Start Insert Into Entity Accesses*************/
PROCEDURE INSERT_ACCESSES_LEADS(
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,
MIN(W.person_id) person_id,
W.trans_object_id sales_lead_id,
MAX(W.full_access_flag) FAF,
W.org_id
FROM JTF_TAE_1001_LEAD_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.trans_object_id,
W.resource_id,
W.group_id,
W.org_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 || AS_GAR.G_START);
l_sales_lead_id.DELETE;
l_org_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
l_person_id.DELETE;
l_faf.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
,sales_lead_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
,open_flag
,lead_rank_score
,object_creation_date
)
(
SELECT as_accesses_s.nextval
,'X'
,l_salesforce_id(i)
,l_sales_group_id(i)
,l_person_id(i)
,NULL
,L.customer_id
,L.address_id
,l_sales_lead_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(STS.opp_open_status_flag, 'Y', 'Y', NULL)
,L.lead_rank_score
,L.creation_date
FROM DUAL, AS_SALES_LEADS L, AS_STATUSES_B STS
WHERE L.sales_lead_id = l_sales_lead_id(i)
AND L.status_code = STS.status_code
AND NOT EXISTS ( SELECT 'X'
FROM AS_ACCESSES_ALL_ALL AA
WHERE AA.sales_lead_id IS NOT NULL
AND AA.lead_id IS NULL
AND AA.delete_flag IS NULL
AND AA.sales_lead_id = l_sales_lead_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
,sales_lead_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
,open_flag
,lead_rank_score
,object_creation_date
)
(
SELECT as_accesses_s.NEXTVAL
,'X'
,l_salesforce_id(i)
,l_sales_group_id(i)
,l_person_id(i)
,NULL
,L.customer_id
,L.address_id
,l_sales_lead_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(STS.opp_open_status_flag, 'Y', 'Y', NULL)
,L.lead_rank_score
,L.creation_date
FROM DUAL,AS_SALES_LEADS L, AS_STATUSES_B STS
WHERE L.sales_lead_id = l_sales_lead_id(i)
AND L.status_code = STS.status_code
AND NOT EXISTS ( SELECT 'X'
FROM AS_ACCESSES_ALL_ALL AA
WHERE AA.sales_lead_id IS NOT NULL
AND AA.lead_id IS NULL
AND AA.delete_flag IS NULL
AND AA.sales_lead_id = l_sales_lead_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_sales_lead_id.DELETE;
l_org_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
l_person_id.DELETE;
l_faf.DELETE;
END INSERT_ACCESSES_LEADS;
/************************** End Insert Into Entity Accesses*************/
/************************** Start Insert Into Terr Accesses*************/
PROCEDURE INSERT_TERR_ACCESSES_LEADS(
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_LEAD_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_sales_lead_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.sales_lead_id=l_sales_lead_id(i)
AND A.sales_group_id = l_sales_group_id(i)
AND A.salesforce_id=l_salesforce_id(i)
AND A.sales_lead_id is NOT NULL
AND A.delete_flag 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.sales_lead_id=l_sales_lead_id(i)
AND A.sales_group_id = l_sales_group_id(i)
AND A.salesforce_id=l_salesforce_id(i)
AND A.sales_lead_id is NOT NULL
AND A.lead_id is NULL
AND A.delete_flag 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_sales_lead_id.DELETE;
l_salesforce_id.DELETE;
l_sales_group_id.DELETE;
l_terr_id.DELETE;
END INSERT_TERR_ACCESSES_LEADS;
/************************** End Insert Into Terr Accesses*************/
/************************** Start Lead Cleanup ***********************/
PROCEDURE Perform_Lead_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 num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
SELECT distinct trans_object_id
FROM JTF_TAE_1001_LEAD_TRANS
WHERE worker_id=c_worker_id;
SELECT distinct trans_object_id
FROM JTF_TAE_1001_LEAD_NM_TRANS
WHERE worker_id=c_worker_id;
LOOP --{L3 to update only 10k record at a time
IF (l_del_flag) THEN EXIT; END IF;
UPDATE AS_ACCESSES_ALL_ALL ACC
SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
WHERE ACC.sales_lead_id=l_sales_lead_id(i)
AND ACC.freeze_flag = 'N'
AND ACC.lead_id IS NULL
AND ACC.sales_lead_id IS NOT NULL
AND ACC.delete_flag IS NULL
AND NOT EXISTS (SELECT 'X'
FROM JTF_TAE_1001_LEAD_WINNERS W
WHERE W.trans_object_id = ACC.sales_lead_id
AND W.worker_id = l_worker_id
AND W.resource_id = ACC.salesforce_id
AND W.group_id = ACC.sales_group_id)
AND ROWNUM < G_DEL_REC;
DELETE FROM AS_ACCESSES_ALL_ALL ACC
WHERE ACC.sales_lead_id=l_sales_lead_id(i)
AND ACC.freeze_flag = 'N'
AND ACC.lead_id IS NULL
AND ACC.sales_lead_id IS NOT NULL
AND NOT EXISTS (SELECT 'X'
FROM JTF_TAE_1001_LEAD_WINNERS W
WHERE W.trans_object_id = ACC.sales_lead_id
AND W.worker_id = l_worker_id
AND W.resource_id = ACC.salesforce_id
AND W.group_id = ACC.sales_group_id)
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.sales_lead_id = l_sales_lead_id(i)
AND ACC.freeze_flag = 'N'
AND ACC.lead_id IS NULL
AND ACC.sales_lead_id IS NOT NULL
AND ACC.delete_flag IS NULL
AND NOT EXISTS (SELECT 'X'
FROM JTF_TAE_1001_LEAD_WINNERS W
WHERE W.trans_object_id = ACC.sales_lead_id
AND W.resource_id = ACC.salesforce_id
AND W.worker_id = l_worker_id
AND W.group_id = ACC.sales_group_id);
DELETE FROM AS_ACCESSES_ALL_ALL ACC
WHERE ACC.sales_lead_id=l_sales_lead_id(i)
AND ACC.freeze_flag = 'N'
AND ACC.lead_id IS NULL
AND ACC.sales_lead_id IS NOT NULL
AND NOT EXISTS (SELECT 'X'
FROM JTF_TAE_1001_LEAD_WINNERS W
WHERE W.trans_object_id = ACC.sales_lead_id
AND W.resource_id = ACC.salesforce_id
AND W.worker_id = l_worker_id
AND W.group_id = ACC.sales_group_id);
END LOOP; --}L3 -- to update only 10k record at a time on accesses
SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
max(DECODE(aaa.created_by_tap_flag,'Y',aaa.access_id,-999)) access_id
FROM AS_ACCESSES_ALL_ALL aaa,
( SELECT distinct trans_object_id
FROM JTF_TAE_1001_LEAD_TRANS
WHERE worker_id=c_worker_id ) w
WHERE aaa.lead_id is NULL
AND aaa.sales_lead_id is NOT NULL
AND aaa.delete_flag is NULL
AND aaa.sales_lead_id=w.trans_object_id
AND aaa.sales_lead_id+0=w.trans_object_id
GROUP BY aaa.sales_lead_id
HAVING SUM(DECODE(aaa.CREATED_BY_TAP_FLAG,'Y',1,0)) > 0
AND SUM(DECODE(aaa.owner_flag,'Y',1,0)) = 0
UNION -- Union added for Bug#4035168
SELECT trans_object_id ,0
FROM JTF_TAE_1001_LEAD_TRANS w
WHERE worker_id = c_worker_id
AND NOT EXISTS
(SELECT 'x'
FROM AS_ACCESSES_ALL aaa
WHERE aaa.sales_lead_id =w.trans_object_id
AND (aaa.CREATED_BY_TAP_FLAG = 'Y'
OR aaa.owner_flag='Y'));
SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
max(DECODE(aaa.created_by_tap_flag,'Y',aaa.access_id,-999)) access_id
FROM AS_ACCESSES_ALL_ALL aaa,
( SELECT distinct trans_object_id
FROM JTF_TAE_1001_LEAD_NM_TRANS
WHERE worker_id=c_worker_id ) w
WHERE aaa.lead_id is null
AND aaa.delete_flag is null
AND aaa.sales_lead_id=w.trans_object_id
AND aaa.sales_lead_id+0=w.trans_object_id
GROUP BY aaa.sales_lead_id
HAVING SUM(DECODE(aaa.CREATED_BY_TAP_FLAG,'Y',1,0)) > 0
AND SUM(DECODE(aaa.owner_flag,'Y',1,0)) = 0
UNION -- Union added for Bug#4035168
SELECT trans_object_id ,0
FROM JTF_TAE_1001_LEAD_NM_TRANS w
WHERE worker_id = c_worker_id
AND NOT EXISTS
(SELECT 'x'
FROM AS_ACCESSES_ALL aaa
WHERE aaa.sales_lead_id =w.trans_object_id
AND (aaa.CREATED_BY_TAP_FLAG = 'Y'
OR aaa.owner_flag='Y'));
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS');
UPDATE AS_SALES_LEADS sl
SET sl.last_update_date = SYSDATE,
sl.last_updated_by = p_terr_globals.user_id,
sl.last_update_login = p_terr_globals.last_update_login,
sl.request_id = p_terr_globals.request_id,
sl.program_application_id = p_terr_globals.prog_appl_id,
sl.program_id = p_terr_globals.prog_id,
sl.program_update_date = SYSDATE,
( sl.assign_to_salesforce_id,
sl.assign_sales_group_id,
sl.assign_to_person_id
) =
( SELECT salesforce_id,sales_group_id,person_id
FROM as_accesses_all_all
WHERE access_id = l_access_id(i)
)
WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
UPDATE AS_SALES_LEADS sl
SET sl.last_update_date = SYSDATE,
sl.last_updated_by = p_terr_globals.user_id,
sl.last_update_login = p_terr_globals.last_update_login,
sl.request_id = p_terr_globals.request_id,
sl.program_application_id = p_terr_globals.prog_appl_id,
sl.program_id = p_terr_globals.prog_id,
sl.program_update_date = SYSDATE,
( sl.assign_to_salesforce_id,
sl.assign_sales_group_id,
sl.assign_to_person_id
) =
( SELECT salesforce_id,sales_group_id,person_id
FROM as_accesses_all_all
WHERE access_id = l_access_id(i)
)
WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF SALES LEADS', SQLERRM, TO_CHAR(SQLCODE));
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS', SQLERRM, TO_CHAR(SQLCODE));
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES');
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.owner_flag = 'Y'
WHERE ACC.access_id = l_access_id(i);
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES::' || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES::' || AS_GAR.G_DEADLOCK ||l_attempts );
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.owner_flag = 'Y'
WHERE ACC.access_id = l_access_id(i);
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF SALES LEADS ACCESSES', SQLERRM, TO_CHAR(SQLCODE));
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE SALES_LEADS ACCESSES', SQLERRM, TO_CHAR(SQLCODE));
l_access_id.delete;
l_sales_lead_id.delete;
SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id
FROM as_accesses_all_all aaa,
( select distinct trans_object_id
from jtf_tae_1001_lead_trans
where worker_id=c_worker_id ) w
WHERE aaa.lead_id is null
and aaa.delete_flag is null
AND aaa.sales_lead_id=w.trans_object_id
AND aaa.sales_lead_id+0=w.trans_object_id
GROUP BY aaa.sales_lead_id
HAVING sum(decode(aaa.owner_flag,'Y',1,0)) = 0
UNION -- Union added for Bug#4035168
select trans_object_id from jtf_tae_1001_lead_trans w
where worker_id = c_worker_id and not exists
(select 'x' from as_accesses_all aaa
where aaa.sales_lead_id =w.trans_object_id);
Select trans_object_id from jtf_tae_1001_lead_trans w
where worker_id = c_worker_id and not exists
(select 'x' from as_accesses_all aaa
where aaa.sales_lead_id =w.trans_object_id);
SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id
FROM as_accesses_all_all aaa,
( select distinct trans_object_id
from JTF_TAE_1001_LEAD_NM_TRANS
where worker_id=c_worker_id ) w
WHERE aaa.lead_id is null
and aaa.delete_flag is null
AND aaa.sales_lead_id=w.trans_object_id
AND aaa.sales_lead_id+0=w.trans_object_id
GROUP BY aaa.sales_lead_id
HAVING sum(decode(aaa.owner_flag,'Y',1,0)) = 0
UNION -- Union added for Bug#4035168
select trans_object_id from JTF_TAE_1001_LEAD_NM_TRANS w
where worker_id = c_worker_id and not exists
(select 'x' from as_accesses_all aaa
where aaa.sales_lead_id =w.trans_object_id);
Select trans_object_id from JTF_TAE_1001_LEAD_NM_TRANS w
where worker_id = c_worker_id and not exists
(select 'x' from as_accesses_all aaa
where aaa.sales_lead_id =w.trans_object_id);
SELECT grp.group_id
FROM JTF_RS_GROUP_MEMBERS mem,
JTF_RS_ROLE_RELATIONS rrel,
JTF_RS_ROLES_B role,
JTF_RS_GROUP_USAGES u,
JTF_RS_GROUPS_B grp
WHERE mem.group_member_id = rrel.role_resource_id
AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
AND rrel.role_id = role.role_id
AND role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM')
AND mem.delete_flag <> 'Y'
AND rrel.delete_flag <> 'Y'
AND SYSDATE BETWEEN rrel.start_date_active AND
NVL(rrel.end_date_active,SYSDATE)
AND mem.resource_id = c_resource_id
AND mem.group_id = u.group_id
AND u.usage = 'SALES'
AND mem.group_id = grp.group_id
AND SYSDATE BETWEEN grp.start_date_active AND
NVL(grp.end_date_active,SYSDATE)
AND ROWNUM < 2;
SELECT res.source_id
FROM jtf_rs_resource_extns res
WHERE res.resource_id = c_resource_id;
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'INSERT INTO AS_ACCESSES_ALL_ALL');
INSERT INTO AS_ACCESSES_ALL_ALL
(ACCESS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
ACCESS_TYPE,
FREEZE_FLAG,
REASSIGN_FLAG,
TEAM_LEADER_FLAG,
OWNER_FLAG,
CREATED_BY_TAP_FLAG,
CUSTOMER_ID,
ADDRESS_ID,
SALES_LEAD_ID,
SALESFORCE_ID,
PERSON_ID,
SALES_GROUP_ID,
REQUEST_ID,
OPEN_FLAG,
LEAD_RANK_SCORE,
OBJECT_CREATION_DATE)
( SELECT
as_accesses_s.nextval,
SYSDATE,
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
p_terr_globals.last_update_login,
p_terr_globals.prog_appl_id,
SYSDATE,
'X',
NVL(L.accept_flag, 'N'),
'N',
'Y',
'Y',
'N',
L.customer_id,
L.address_id,
l_sales_lead_id(i),
l_resource_id,
l_person_id,
l_group_id,
p_terr_globals.request_id,
L.status_open_flag,
L.lead_rank_score,
L.creation_date
FROM DUAL ,
AS_SALES_LEADS L
WHERE L.sales_lead_id = l_sales_lead_id(i)
AND NOT EXISTS ( select 'X'
from AS_ACCESSES_ALL_ALL aa
where aa.sales_lead_id is not null
and aa.lead_id is null
and aa.delete_flag is null
and aa.sales_lead_id = l_sales_lead_id(i)
and aa.salesforce_id = l_resource_id
and nvl(aa.sales_group_id,-777) = nvl(l_group_id,-777)
)
);
INSERT INTO AS_ACCESSES_ALL_ALL
(ACCESS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
ACCESS_TYPE,
FREEZE_FLAG,
REASSIGN_FLAG,
TEAM_LEADER_FLAG,
OWNER_FLAG,
CREATED_BY_TAP_FLAG,
CUSTOMER_ID,
ADDRESS_ID,
SALES_LEAD_ID,
SALESFORCE_ID,
PERSON_ID,
SALES_GROUP_ID,
REQUEST_ID,
OPEN_FLAG,
LEAD_RANK_SCORE,
OBJECT_CREATION_DATE)
( SELECT
as_accesses_s.nextval,
SYSDATE,
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
p_terr_globals.last_update_login,
p_terr_globals.prog_appl_id,
SYSDATE,
'X',
NVL(L.accept_flag, 'N'),
'N',
'Y',
'Y',
'N',
L.customer_id,
L.address_id,
l_sales_lead_id(i),
l_resource_id,
l_person_id,
l_group_id,
p_terr_globals.request_id,
L.status_open_flag,
L.lead_rank_score,
L.creation_date
FROM DUAL ,
AS_SALES_LEADS L
WHERE L.sales_lead_id = l_sales_lead_id(i)
AND NOT EXISTS ( select 'X'
from AS_ACCESSES_ALL_ALL aa
where aa.sales_lead_id is not null
and aa.lead_id is null
and aa.delete_flag is null
and aa.sales_lead_id = l_sales_lead_id(i)
and aa.salesforce_id = l_resource_id
and nvl(aa.sales_group_id,-777) = nvl(l_group_id,-777)
)
);
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW INSERT INTO AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'INSERT INTO AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL');
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.owner_flag = 'Y'
WHERE ACC.sales_lead_id is not null
and ACC.lead_id is null
and ACC.delete_flag is null
and ACC.sales_lead_id = l_sales_lead_id(i)
and ACC.salesforce_id = l_resource_id
and nvl(ACC.sales_group_id,-777) = nvl(l_group_id,-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.owner_flag = 'Y'
WHERE ACC.sales_lead_id is not null
and ACC.lead_id is null
and ACC.delete_flag is null
and ACC.sales_lead_id = l_sales_lead_id(i)
and ACC.salesforce_id = l_resource_id
and nvl(ACC.sales_group_id,-777) = nvl(l_group_id,-777);
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS');
UPDATE AS_SALES_LEADS sl SET
sl.last_update_date = SYSDATE,
sl.last_updated_by = p_terr_globals.user_id,
sl.last_update_login = p_terr_globals.last_update_login,
sl.request_id = p_terr_globals.request_id,
sl.program_application_id = p_terr_globals.prog_appl_id,
sl.program_id = p_terr_globals.prog_id,
sl.program_update_date = SYSDATE,
sl.assign_to_salesforce_id = l_resource_id,
sl.assign_sales_group_id = l_group_id,
sl.assign_to_person_id = l_person_id
WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS::' || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS::' || AS_GAR.G_DEADLOCK ||l_attempts );
UPDATE AS_SALES_LEADS sl SET
sl.last_update_date = SYSDATE,
sl.last_updated_by = p_terr_globals.user_id,
sl.last_update_login = p_terr_globals.last_update_login,
sl.request_id = p_terr_globals.request_id,
sl.program_application_id = p_terr_globals.prog_appl_id,
sl.program_id = p_terr_globals.prog_id,
sl.program_update_date = SYSDATE,
sl.assign_to_salesforce_id = l_resource_id,
sl.assign_sales_group_id = l_group_id,
sl.assign_to_person_id = l_person_id
WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE AS_SALES_LEADS', SQLERRM, TO_CHAR(SQLCODE));
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS', SQLERRM, TO_CHAR(SQLCODE));
l_access_id.delete;
l_sales_lead_id.delete;
SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id
FROM as_accesses_all_all aaa,
( select distinct trans_object_id
from jtf_tae_1001_lead_trans
where worker_id=c_worker_id ) w
WHERE aaa.lead_id is null
and aaa.delete_flag is null
AND aaa.sales_lead_id=w.trans_object_id
AND aaa.sales_lead_id+0=w.trans_object_id
GROUP BY aaa.sales_lead_id
HAVING sum(decode(aaa.owner_flag,'Y',1,0)) = 1;
SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id
FROM as_accesses_all_all aaa,
( select distinct trans_object_id
from JTF_TAE_1001_LEAD_NM_TRANS
where worker_id=c_worker_id ) w
WHERE aaa.lead_id is null
and aaa.delete_flag is null
AND aaa.sales_lead_id=w.trans_object_id
AND aaa.sales_lead_id+0=w.trans_object_id
GROUP BY aaa.sales_lead_id
HAVING sum(decode(aaa.owner_flag,'Y',1,0)) = 1;
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL');
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.owner_flag = 'N'
WHERE ACC.sales_lead_id = l_sales_lead_id(i)
and ACC.owner_flag = 'Y'
and ACC.freeze_flag = 'N';
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.owner_flag = 'N'
WHERE ACC.sales_lead_id = l_sales_lead_id(i)
and ACC.owner_flag = 'Y'
and ACC.freeze_flag = 'N';
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
l_access_id.delete;
l_sales_lead_id.delete;
SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
max(decode(aaa.owner_flag,'Y',aaa.access_id,-999)) access_id
FROM as_accesses_all_all aaa,
( select distinct trans_object_id
from jtf_tae_1001_lead_trans
where worker_id=c_worker_id ) w
WHERE aaa.lead_id is null
and aaa.delete_flag is null
AND aaa.sales_lead_id=w.trans_object_id
AND aaa.sales_lead_id+0=w.trans_object_id
GROUP BY aaa.sales_lead_id
UNION -- Union added for Bug#4035168
select trans_object_id ,0 from jtf_tae_1001_lead_trans w
where worker_id = c_worker_id and not exists
(select 'x' from as_accesses_all aaa
where aaa.sales_lead_id =w.trans_object_id);
SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
max(decode(aaa.owner_flag,'Y',aaa.access_id,-999)) access_id
FROM as_accesses_all_all aaa,
( select distinct trans_object_id
from JTF_TAE_1001_LEAD_NM_TRANS
where worker_id=c_worker_id ) w
WHERE aaa.lead_id is null
and aaa.delete_flag is null
AND aaa.sales_lead_id=w.trans_object_id
AND aaa.sales_lead_id+0=w.trans_object_id
GROUP BY aaa.sales_lead_id
UNION -- Union added for Bug#4035168
select trans_object_id ,0 from JTF_TAE_1001_LEAD_NM_TRANS w
where worker_id = c_worker_id and not exists
(select 'x' from as_accesses_all aaa
where aaa.sales_lead_id =w.trans_object_id);
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS');
UPDATE AS_SALES_LEADS sl SET
sl.last_update_date = SYSDATE,
sl.last_updated_by = p_terr_globals.user_id,
sl.last_update_login = p_terr_globals.last_update_login,
sl.request_id = p_terr_globals.request_id,
sl.program_application_id = p_terr_globals.prog_appl_id,
sl.program_id = p_terr_globals.prog_id,
sl.program_update_date = SYSDATE,
( sl.assign_to_salesforce_id,
sl.assign_sales_group_id,
sl.assign_to_person_id
) =
( SELECT salesforce_id,sales_group_id,person_id
FROM as_accesses_all_all
WHERE access_id = l_access_id(i)
)
WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
UPDATE AS_SALES_LEADS sl SET
sl.last_update_date = SYSDATE,
sl.last_updated_by = p_terr_globals.user_id,
sl.last_update_login = p_terr_globals.last_update_login,
sl.request_id = p_terr_globals.request_id,
sl.program_application_id = p_terr_globals.prog_appl_id,
sl.program_id = p_terr_globals.prog_id,
sl.program_update_date = SYSDATE,
( sl.assign_to_salesforce_id,
sl.assign_sales_group_id,
sl.assign_to_person_id
) =
( SELECT salesforce_id,sales_group_id,person_id
FROM as_accesses_all_all
WHERE access_id = l_access_id(i)
)
WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF SALES LEADS', SQLERRM, TO_CHAR(SQLCODE));
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS', SQLERRM, TO_CHAR(SQLCODE));
l_access_id.delete;
l_sales_lead_id.delete;