[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* This inserts into Oppty winners */
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_START);
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || 'UPDATE FAF::' || AS_GAR.G_START);
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || 'UPDATE FAF::' || AS_GAR.G_END);
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || 'UPDATE FAF::' || AS_GAR.G_RETURN_STATUS || l_return_status);
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || 'UPDATE FAF::', l_errbuf, l_retcode);
-- Insert into Qot Accesses from Winners
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
AS_GAR_QOT_PUB.INSERT_ACCESSES_QOT(
x_errbuf => l_errbuf,
x_retcode => l_retcode,
p_terr_globals => l_terr_globals,
x_return_status => l_return_status);
AS_GAR_QOT_PUB.INSERT_TERR_ACCESSES_QOT(
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_QUOTE_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 = 'QUOTE'
AND rules.source_id = -1001
AND rsc.terr_rsc_id = acc.terr_rsc_id;
INSERT INTO JTF_TAE_1001_QUOTE_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,
ROLE_ID,
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.ROLE_ID,
T.primary_contact_flag,
J.person_id,
T.org_id,
T.worker_id
FROM
JTF_TAE_1001_QUOTE_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_QUOTE_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_QUOTE_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 = 'QUOTE'
AND rules.source_id = -1001
AND rsc.terr_rsc_id = acc.terr_rsc_id ;
INSERT INTO JTF_TAE_1001_QUOTE_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,
ROLE_ID,
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.ROLE_ID,
T.primary_contact_flag,
J.person_id,
T.org_id,
T.worker_id
FROM
JTF_TAE_1001_QUOTE_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_QUOTE_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));
| the merge update as this cursor should a diMINishing one
| when it is reopened repeatedly.
+-------------------------------------------------------------------------*/
PROCEDURE SET_TEAM_LEAD_QOT(
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
A.quote_number , A.resource_id
FROM JTF_TAE_1001_QUOTE_WINNERS WIN,
ASO_QUOTE_ACCESSES A
WHERE WIN.trans_object_id = A.quote_number
AND WIN.source_id = -1001
AND WIN.resource_id = A.RESOURCE_ID
AND WIN.worker_id = c_worker_id
AND WIN.resource_type = 'RS_EMPLOYEE'
AND NVL(A.keep_flag,'N') <> 'Y'
AND (NVL(win.group_id,-777) <> NVL(A.resource_grp_id,-777)
OR NVL(win.role_id,-777) <> NVL(A.role_id,-777))
AND NOT EXISTS
(SELECT 'X'
FROM JTF_TAE_1001_QUOTE_WINNERS WIN1
WHERE WIN1.trans_object_id = A.quote_number
AND WIN1.source_id = -1001
AND WIN1.resource_id = A.RESOURCE_ID
AND WIN1.worker_id = c_worker_id
AND WIN1.resource_type = 'RS_EMPLOYEE'
AND NVL(A.keep_flag,'N') <> 'Y'
AND NVL(WIN1.group_id,-777) = NVL(A.resource_grp_id,-777)
AND NVL(WIN1.role_id,-777) = NVL(A.role_id,-777))
ORDER BY quote_number;
l_resource_id.DELETE;
l_quote_number_id.DELETE;
UPDATE ASO_QUOTE_ACCESSES A
SET (update_access_flag ,
resource_grp_id ,
role_id ,
territory_id )
= (
SELECT MAX(W.full_access_flag ),MIN( W.group_id),MIN(w.role_id) ,MIN(W.terr_id)
FROM JTF_TAE_1001_QUOTE_WINNERS W
WHERE trans_object_id = l_quote_number_id(I)
AND resource_id = l_resource_id(i)
GROUP BY trans_object_id,resource_id
)
WHERE quote_number = l_quote_number_id(I)
AND resource_id = l_resource_id(i) ;
UPDATE ASO_QUOTE_ACCESSES A
SET (update_access_flag ,
resource_grp_id ,
role_id ,
territory_id )
= (
SELECT MAX(W.full_access_flag ),MIN( W.group_id),MIN(w.role_id) ,MIN(W.terr_id)
FROM JTF_TAE_1001_QUOTE_WINNERS W
WHERE trans_object_id = l_quote_number_id(I)
AND resource_id = l_resource_id(i)
GROUP BY trans_object_id,resource_id
)
WHERE quote_number = l_quote_number_id(I)
AND resource_id = l_resource_id(i) ;
l_quote_number_id.DELETE;
l_resource_id.DELETE;
SELECT DISTINCT
A.access_id,WIN.full_access_flag,WIN.terr_id
FROM JTF_TAE_1001_QUOTE_WINNERS WIN,
ASO_QUOTE_ACCESSES A
WHERE WIN.trans_object_id = A.quote_number
AND WIN.source_id = -1001
AND WIN.resource_id = A.RESOURCE_ID
AND WIN.worker_id = c_worker_id
AND WIN.resource_type = 'RS_EMPLOYEE'
AND NVL(A.keep_flag,'N') <> 'Y'
AND NVL(WIN.group_id,-777) = NVL(A.resource_grp_id,-777)
AND NVL(WIN.role_id,-777) = NVL(A.role_id,-777)
AND (WIN.full_access_flag <> A.update_access_flag
OR WIN.terr_id <> A.territory_id)
ORDER BY access_id;
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_START);
l_access_id.DELETE;
l_terr_id.DELETE;
l_faf.DELETE;
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || 'LOOPCOUNT :- ' || l_loop_count);
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
UPDATE ASO_QUOTE_ACCESSES A
SET update_access_flag = l_faf(i),
territory_id = l_terr_id(i)
WHERE access_id = l_access_id(i);
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_BULK_UPD || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-'|| l_last);
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_DEADLOCK ||l_attempts);
UPDATE ASO_QUOTE_ACCESSES A
SET update_access_flag = l_faf(i),
territory_id = l_terr_id(i)
WHERE access_id = l_access_id(i);
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_IND_UPD || AS_GAR.G_GENERAL_EXCEPTION);
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_BULK_UPD, SQLERRM, TO_CHAR(SQLCODE));
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_END);
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_N_ROWS_PROCESSED || l_access_id.COUNT);
l_access_id.DELETE;
l_terr_id.DELETE;
l_faf.DELETE;
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::', SQLERRM, TO_CHAR(SQLCODE));
/************************** Start Insert Into Entity Accesses*************/
PROCEDURE INSERT_ACCESSES_QOT(
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.trans_object_id,
W.resource_id,
MIN(w.person_id),
MIN(W.group_id),
MIN(W.role_id) ,
MAX(W.full_access_flag ) faf,
MIN(W.terr_id)
FROM JTF_TAE_1001_QUOTE_WINNERS W
WHERE W.resource_type = 'RS_EMPLOYEE'
AND W.source_id = -1001
AND W.worker_id = c_worker_id
AND NOT EXISTS
(SELECT 'X'
FROM aso_quote_accesses A
WHERE W.trans_object_id = A.quote_number
AND W.resource_id = a.RESOURCE_ID)
GROUP BY W.trans_object_id, W.resource_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_quote_number_id.DELETE;
l_resource_id.DELETE;
l_person_id.DELETE;
l_sales_group_id.DELETE;
l_role_id.DELETE;
l_faf.DELETE;
l_terr_id.DELETE;
INSERT INTO ASO_QUOTE_ACCESSES (
ACCESS_ID,
QUOTE_NUMBER,
RESOURCE_ID,
RESOURCE_GRP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
KEEP_FLAG,
UPDATE_ACCESS_FLAG,
CREATED_BY_TAP_FLAG,
TERRITORY_ID,
TERRITORY_SOURCE_FLAG,
ROLE_ID
) VALUES (
ASO_QUOTE_ACCESSES_S.nextval,
l_quote_number_id(i),
l_resource_id(i),
l_sales_group_id(i),
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
p_terr_globals.last_update_login,
SYSDATE,
p_terr_globals.request_id,
p_terr_globals.prog_appl_id,
p_terr_globals.prog_id,
SYSDATE,
'N',
l_faf(i),
'Y',
l_terr_id(i),
'Y',
l_role_id(i)
);
INSERT INTO ASO_QUOTE_ACCESSES (
access_id,
quote_number,
resource_id,
resource_grp_id,
created_by,
creation_date,
last_updated_by,
last_update_login,
last_update_date,
request_id,
program_application_id,
program_id,
program_update_date,
keep_flag,
update_access_flag,
created_by_tap_flag,
territory_id,
territory_source_flag,
role_id
) VALUES (
aso_quote_accesses_s.NEXTVAL,
l_quote_number_id(i),
l_resource_id(i),
l_sales_group_id(i),
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
p_terr_globals.last_update_login,
SYSDATE,
p_terr_globals.request_id,
p_terr_globals.prog_appl_id,
p_terr_globals.prog_id,
SYSDATE,
'N',
l_faf(i),
'Y',
l_terr_id(i),
'Y',
l_role_id(i)
);
END LOOP; /* loop for DUP_VAL_ON_INDEX individual insert */
l_quote_number_id.DELETE;
l_resource_id.DELETE;
l_person_id.DELETE;
l_sales_group_id.DELETE;
l_role_id.DELETE;
l_faf.DELETE;
l_terr_id.DELETE;
END INSERT_ACCESSES_QOT;
/************************** End Insert Into Entity Accesses*************/
/************************** Start Insert Into Quote Terr Accesses*************/
PROCEDURE INSERT_TERR_ACCESSES_QOT(
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
FROM JTF_TAE_1001_QUOTE_WINNERS W
WHERE W.SOURCE_ID = -1001
AND W.worker_id = c_worker_id
AND W.resource_type = 'RS_EMPLOYEE'
GROUP BY W.TERR_ID,
W.TRANS_OBJECT_ID,
W.RESOURCE_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_quote_number_id.DELETE;
l_resource_id.DELETE;
l_terr_id.DELETE;
INSERT INTO ASO_TERRITORY_ACCESSES
( access_id,
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),
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 AA.acc_id
FROM ( SELECT DISTINCT a.access_id acc_id
FROM ASO_QUOTE_ACCESSES A
WHERE A.quote_number = l_quote_number_id(i)
AND A.resource_id=l_resource_id(i)
) AA
WHERE NOT EXISTS
(SELECT 'X'
FROM ASO_TERRITORY_ACCESSES AST
WHERE AST.access_id = AA.acc_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 AA.acc_id
FROM ( SELECT DISTINCT a.access_id acc_id
FROM ASO_QUOTE_ACCESSES A
WHERE A.quote_number = l_quote_number_id(i)
AND A.resource_id=l_resource_id(i)
) AA
WHERE NOT EXISTS
(SELECT 'X'
FROM ASO_TERRITORY_ACCESSES AST
WHERE AST.access_id = AA.acc_id
AND AST.territory_id = l_terr_id(i))
) V
);
END LOOP; -- end loop for insert into territory accesses
l_quote_number_id.DELETE;
l_resource_id.DELETE;
l_terr_id.DELETE;
END INSERT_TERR_ACCESSES_QOT;
/************************** End Insert Into Quote Terr Accesses*************/
/**************************** Start Assign Quote Owner ********************/
PROCEDURE ASSIGN_QOT_OWNER(
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
l_return_status VARCHAR2(1);
SELECT W.trans_object_id, MAX(access_id)
FROM ASO_QUOTE_ACCESSES AQA1,
( SELECT DISTINCT trans_object_id -- Q8
FROM JTF_TAE_1001_QUOTE_TRANS TRANS ,ASO_QUOTE_HEADERS_ALL AQH
WHERE worker_id=c_worker_id
AND TRANS.trans_object_id = AQH.quote_number
AND NOT EXISTS
(
SELECT 'X'
FROM ASO_QUOTE_ACCESSES AQA2, JTF_RS_SRP_Vl SREP1
WHERE AQH.resource_Id = AQA2.resource_id
AND AQH.quote_number = AQA2.quote_number
AND SREP1.resource_id = AQA2.resource_id
AND NVL(AQA2.update_access_flag,'N') = 'Y'
AND NVL(SREP1.status,'A') = 'A'
AND NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
)-- all Q except Q8 (pick only the record which has an invalid resource set in header and access)
) W, JTF_RS_SRP_Vl SREP
WHERE AQA1.quote_number = W.trans_object_id
AND AQA1.resource_id = SREP.resource_id
AND NVL(AQA1.update_access_flag,'N') ='Y'
AND NVL(SREP.status,'A') = 'A'
AND NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
GROUP BY W.trans_object_id;
SELECT W.trans_object_id, MAX(access_id)
FROM ASO_QUOTE_ACCESSES AQA1,
( SELECT DISTINCT trans_object_id -- Q8
FROM JTF_TAE_1001_QUOTE_NM_TRANS TRANS ,ASO_QUOTE_HEADERS_ALL AQH
WHERE worker_id=c_worker_id
AND TRANS.trans_object_id = AQH.quote_number
AND NOT EXISTS
(
SELECT 'X'
FROM ASO_QUOTE_ACCESSES AQA2, JTF_RS_SRP_Vl SREP1
WHERE AQH.resource_Id = AQA2.resource_id
AND AQH.quote_number = AQA2.quote_number
AND SREP1.resource_id = AQA2.resource_id
AND NVL(AQA2.update_access_flag,'N') = 'Y'
AND NVL(SREP1.status,'A') = 'A'
AND NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
)-- all Q except Q8 (pick only the record which has an invalid resource set in header and access)
) W, JTF_RS_SRP_Vl SREP
WHERE AQA1.quote_number = W.trans_object_id
AND AQA1.resource_id = SREP.resource_id
AND NVL(AQA1.update_access_flag,'N') ='Y'
AND NVL(SREP.status,'A') = 'A'
AND NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
GROUP BY W.trans_object_id;
| These quote headers and accesses should be updated with the profile defaults
|-------------------------------------------------------------------------------*/
CURSOR set_primary_srep_sc2_total(c_worker_id NUMBER) IS
SELECT DISTINCT trans_object_id -- Q9, Q10
FROM JTF_TAE_1001_QUOTE_TRANS TRANS ,ASO_QUOTE_HEADERS_ALL AQH
WHERE worker_id=c_worker_id
AND TRANS.trans_object_id = AQH.quote_number
AND NOT EXISTS
(SELECT 'X'
FROM ASO_QUOTE_ACCESSES AQA2, JTF_RS_SRP_Vl SREP
WHERE SREP.resource_Id = AQA2.resource_id
AND AQH.quote_number = AQA2.quote_number
AND NVL(AQA2.update_access_flag,'N') = 'Y'
AND NVL(SREP.status,'A') = 'A'
AND NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
) -- R1,R7,R8,R9,R3,R4,R2,R5,R10,R11
GROUP BY trans_object_id;
SELECT DISTINCT trans_object_id -- Q9, Q10
FROM JTF_TAE_1001_QUOTE_NM_TRANS TRANS ,ASO_QUOTE_HEADERS_ALL AQH
WHERE worker_id=c_worker_id
AND TRANS.trans_object_id = AQH.quote_number
AND NOT EXISTS
(SELECT 'X'
FROM ASO_QUOTE_ACCESSES AQA2, JTF_RS_SRP_Vl SREP
WHERE SREP.resource_Id = AQA2.resource_id
AND AQH.quote_number = AQA2.quote_number
AND NVL(AQA2.update_access_flag,'N') = 'Y'
AND NVL(SREP.status,'A') = 'A'
AND NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
) -- R1,R7,R8,R9,R3,R4,R2,R5,R10,R11
GROUP BY trans_object_id;
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE ASO_QUOTE_HEADERS_ALL');
UPDATE ASO_QUOTE_HEADERS_ALL AQH
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
(resource_id,resource_grp_id) =
( SELECT resource_id,resource_grp_id
FROM ASO_QUOTE_ACCESSES AQA
WHERE AQA.access_id = l_access_id(i)
)
WHERE quote_number = l_quote_number_id(I);
UPDATE ASO_QUOTE_HEADERS_ALL AQH
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
(resource_id,resource_grp_id) =
( SELECT resource_id,resource_grp_id
FROM ASO_QUOTE_ACCESSES AQA
WHERE AQA.access_id = l_access_id(i)
)
WHERE quote_number = l_quote_number_id(I);
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF ASO_QUOTE_HEADERS', SQLERRM, TO_CHAR(SQLCODE));
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE ASO_QUOTE_HEADERS', SQLERRM, TO_CHAR(SQLCODE));
l_access_id.delete;
l_quote_number_id.delete;
| Insert into quote accesses
| Update quote headers
| We have requested quoting team for more info on how to obtain the
| following default values:
|
|
|
+---------------------------------------------------------------------*/
l_flag := TRUE;
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE ASO_QUOTE_HEADERS_ALL');
UPDATE ASO_QUOTE_HEADERS_ALL AQH
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
(resource_id,resource_grp_id) =
( SELECT resource_id,org_information3
FROM jtf_rs_Salesreps a , hr_organization_information b
WHERE a.salesrep_number = b.org_information2
AND a.org_id = b.organization_id
AND b.org_information_context = 'ASO_ORG_INFO'
AND b.organization_id = AQH.org_id)
WHERE quote_number = l_quote_number_id(I) RETURNING ORG_ID,RESOURCE_ID,RESOURCE_GRP_ID
BULK COLLECT INTO l_org_id,l_sales_rep_id,l_sales_grp_id;
INSERT INTO ASO_QUOTE_ACCESSES (
ACCESS_ID,
QUOTE_NUMBER,
RESOURCE_ID,
RESOURCE_GRP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
KEEP_FLAG,
UPDATE_ACCESS_FLAG,
CREATED_BY_TAP_FLAG,
TERRITORY_ID,
TERRITORY_SOURCE_FLAG,
ROLE_ID
)
SELECT ASO_QUOTE_ACCESSES_S.nextval,
l_quote_number_id(i),
l_sales_rep_id(i),
l_sales_grp_id(i),
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
p_terr_globals.last_update_login,
SYSDATE,
p_terr_globals.request_id,
p_terr_globals.prog_appl_id,
p_terr_globals.prog_id,
SYSDATE,
'N',
'Y',
'Y',
NULL,
'N',
org_information4
from hr_organization_information
where org_information_context = 'ASO_ORG_INFO'
and organization_id = l_org_id(i);
INSERT INTO ASO_QUOTE_ACCESSES (
ACCESS_ID,
QUOTE_NUMBER,
RESOURCE_ID,
RESOURCE_GRP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
KEEP_FLAG,
UPDATE_ACCESS_FLAG,
CREATED_BY_TAP_FLAG,
TERRITORY_ID,
TERRITORY_SOURCE_FLAG,
ROLE_ID
)
SELECT ASO_QUOTE_ACCESSES_S.nextval,
l_quote_number_id(i),
l_sales_rep_id(i),
l_sales_grp_id(i),
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
p_terr_globals.last_update_login,
SYSDATE,
p_terr_globals.request_id,
p_terr_globals.prog_appl_id,
p_terr_globals.prog_id,
SYSDATE,
'N',
'Y',
'Y',
NULL,
'N',
org_information4
from hr_organization_information
where org_information_context = 'ASO_ORG_INFO'
and organization_id = l_org_id(i);
UPDATE ASO_QUOTE_HEADERS_ALL AQH
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
(resource_id,resource_grp_id) =
( SELECT resource_id,org_information3
FROM jtf_rs_Salesreps a , hr_organization_information b
WHERE a.salesrep_number = b.org_information2
AND a.org_id = b.organization_id
AND b.org_information_context = 'ASO_ORG_INFO'
AND b.organization_id = AQH.org_id)
WHERE quote_number = l_quote_number_id(I) RETURNING ORG_ID,RESOURCE_ID,RESOURCE_GRP_ID
INTO l_ind_org_id,l_ind_sales_rep_id,l_ind_sales_grp_id;
INSERT INTO ASO_QUOTE_ACCESSES (
ACCESS_ID,
QUOTE_NUMBER,
RESOURCE_ID,
RESOURCE_GRP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
KEEP_FLAG,
UPDATE_ACCESS_FLAG,
CREATED_BY_TAP_FLAG,
TERRITORY_ID,
TERRITORY_SOURCE_FLAG,
ROLE_ID
)
SELECT ASO_QUOTE_ACCESSES_S.nextval,
l_quote_number_id(i),
l_sales_rep_id(i),
l_sales_grp_id(i),
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
p_terr_globals.last_update_login,
SYSDATE,
p_terr_globals.request_id,
p_terr_globals.prog_appl_id,
p_terr_globals.prog_id,
SYSDATE,
'N',
'Y',
'Y',
NULL,
'N',
org_information4
from hr_organization_information
where org_information_context = 'ASO_ORG_INFO'
and organization_id = l_org_id(i);
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF ASO_QUOTE_HEADERS', SQLERRM, TO_CHAR(SQLCODE));
INSERT INTO ASO_QUOTE_ACCESSES (
ACCESS_ID,
QUOTE_NUMBER,
RESOURCE_ID,
RESOURCE_GRP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
KEEP_FLAG,
UPDATE_ACCESS_FLAG,
CREATED_BY_TAP_FLAG,
TERRITORY_ID,
TERRITORY_SOURCE_FLAG,
ROLE_ID
)
SELECT ASO_QUOTE_ACCESSES_S.nextval,
l_quote_number_id(i),
l_sales_rep_id(i),
l_sales_grp_id(i),
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
p_terr_globals.last_update_login,
SYSDATE,
p_terr_globals.request_id,
p_terr_globals.prog_appl_id,
p_terr_globals.prog_id,
SYSDATE,
'N',
'Y',
'Y',
NULL,
'N',
org_information4
from hr_organization_information
where org_information_context = 'ASO_ORG_INFO'
and organization_id = l_org_id(i);
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF ASO_QUOTE_HEADERS', SQLERRM, TO_CHAR(SQLCODE));
AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE ASO_QUOTE_HEADERS', SQLERRM, TO_CHAR(SQLCODE));
l_access_id.delete;
l_quote_number_id.delete;
SELECT distinct trans_object_id
FROM JTF_TAE_1001_QUOTE_WINNERS
WHERE worker_id=c_worker_id;
LOOP --{L3 to update only 10k record at a time
IF (l_del_flag) THEN EXIT; END IF;
DELETE FROM ASO_QUOTE_ACCESSES ACC
WHERE ACC.QUOTE_NUMBER =l_quote_number_id(i)
AND NVL(ACC.KEEP_FLAG,'N') <> 'Y'
AND NOT EXISTS (SELECT 'X'
FROM JTF_TAE_1001_QUOTE_WINNERS W
WHERE W.TRANS_OBJECT_ID = ACC.QUOTE_NUMBER
AND W.WORKER_ID = l_worker_id
AND W.RESOURCE_ID = ACC.RESOURCE_ID)
AND ROWNUM < G_DEL_REC;
DELETE FROM ASO_QUOTE_ACCESSES ACC
WHERE ACC.QUOTE_NUMBER =l_quote_number_id(i)
AND NVL(ACC.KEEP_FLAG,'N') <> 'Y'
AND NOT EXISTS (SELECT 'X'
FROM JTF_TAE_1001_QUOTE_WINNERS W
WHERE W.TRANS_OBJECT_ID = ACC.QUOTE_NUMBER
AND W.WORKER_ID = l_worker_id
AND W.RESOURCE_ID = ACC.RESOURCE_ID);
END LOOP; --}L3 -- to update only 10k record at a time on accesses