[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* This inserts into proposal winners */
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_START);
-- Insert into proposal Accesses from Winners
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
AS_GAR_PROPOSALS_PUB.INSERT_ACCESSES_PROPOSALS(
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_PROPOSALS_PUB.INSERT_TERR_ACCESSES_PROPOSALS(
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_PROP_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 = 'PROPOSAL'
AND rules.source_id = -1001
AND rsc.terr_rsc_id = acc.terr_rsc_id;
INSERT INTO JTF_TAE_1001_PROP_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_PROP_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_PROP_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_PROP_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 = 'PROPOSAL'
AND rules.source_id = -1001
AND rsc.terr_rsc_id = acc.terr_rsc_id;
INSERT INTO JTF_TAE_1001_PROP_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_PROP_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_PROP_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_PROPOSALS(
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 A.proposal_id, -- The use nested loop hint is removed in ACCOUNTS ONLY..
A.resource_id,
A.resource_group_id,
decode(NVL(WIN.full_access_flag,'N'),'Y','FULL','READ') full_access_flag
FROM PRP_PROPOSAL_ACCESSES A,
JTF_TAE_1001_PROP_WINNERS WIN
WHERE NVL(A.ACCESS_LEVEL,'N') <> decode(NVL(WIN.full_access_flag,'N'),'Y','FULL','READ')
AND WIN.SOURCE_ID = -1001
AND WIN.worker_id = c_worker_id
AND WIN.resource_type IN ('RS_EMPLOYEE','RS_PARTNER','RS_PARTY')
AND WIN.trans_object_id = A.proposal_id
AND WIN.resource_id = A.resource_id
AND NVL(WIN.group_id,-777) = NVL(A.resource_group_id,-777)
GROUP BY A.proposal_id,
A.resource_id,
A.resource_group_id,
WIN.full_access_flag;
l_proposal_id.DELETE;
l_resource_id.DELETE;
l_resource_group_id.DELETE;
l_faf.DELETE;
UPDATE PRP_PROPOSAL_ACCESSES 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.ACCESS_LEVEL = l_faf(i)
WHERE ACC.proposal_id = l_proposal_id(i)
AND ACC.resource_id = l_resource_id(i)
AND NVL(ACC.resource_group_id,-777) = NVL(l_resource_group_id(i),-777);
UPDATE PRP_PROPOSAL_ACCESSES 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.ACCESS_LEVEL = l_faf(i)
WHERE ACC.proposal_id = l_proposal_id(i)
AND ACC.resource_id = l_resource_id(i)
AND NVL(ACC.resource_group_id,-777) = NVL(l_resource_group_id(i),-777);
l_proposal_id.DELETE;
l_resource_id.DELETE;
l_resource_group_id.DELETE;
l_faf.DELETE;
/************************** Start Insert Into Entity Accesses*************/
PROCEDURE INSERT_ACCESSES_PROPOSALS(
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_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
SELECT W.resource_id,
W.group_id grp_id,
W.trans_object_id proposal_id,
DECODE(MAX(W.full_access_flag),'Y','FULL','READ') faf
FROM JTF_TAE_1001_PROP_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;
| 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_proposal_id.DELETE;
l_resource_id.DELETE;
l_resource_group_id.DELETE;
l_faf.DELETE;
INSERT INTO PRP_PROPOSAL_ACCESSES
(
proposal_access_id
,object_version_number
,proposal_id
,resource_id
,access_level
,resource_group_id
,keep_flag
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
(
SELECT prp_proposal_accesses_s1.nextval -- Check With Sequence
,1
,l_proposal_id(i)
,l_resource_id(i)
,l_faf(i)
,l_resource_group_id(i)
,'N'
,p_terr_globals.user_id
,SYSDATE
,p_terr_globals.user_id
,SYSDATE
,p_terr_globals.last_update_login
FROM DUAL
WHERE NOT EXISTS ( SELECT 'X'
FROM PRP_PROPOSAL_ACCESSES AA
WHERE AA.proposal_id = l_proposal_id(i)
AND AA.resource_id = l_resource_id(i)
AND NVL(AA.resource_group_id,-777)= NVL(l_resource_group_id(i),-777)
)
);
INSERT INTO PRP_PROPOSAL_ACCESSES
(
proposal_access_id
,object_version_number
,proposal_id
,resource_id
,access_level
,resource_group_id
,keep_flag
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
(
SELECT prp_proposal_accesses_s1.nextval -- Check With Sequence
,1
,l_proposal_id(i)
,l_resource_id(i)
,l_faf(i)
,l_resource_group_id(i)
,'N'
,p_terr_globals.user_id
,SYSDATE
,p_terr_globals.user_id
,SYSDATE
,p_terr_globals.last_update_login
FROM DUAL
WHERE NOT EXISTS ( SELECT 'X'
FROM PRP_PROPOSAL_ACCESSES AA
WHERE AA.proposal_id = l_proposal_id(i)
AND AA.resource_id = l_resource_id(i)
AND NVL(AA.resource_group_id,-777)= NVL(l_resource_group_id(i),-777)
)
);
END LOOP; /* loop for DUP_VAL_ON_INDEX individual insert */
l_proposal_id.DELETE;
l_resource_id.DELETE;
l_resource_group_id.DELETE;
l_faf.DELETE;
END INSERT_ACCESSES_PROPOSALS;
/************************** End Insert Into Entity Accesses*************/
/************************** Start Insert Into Terr Accesses*************/
PROCEDURE INSERT_TERR_ACCESSES_PROPOSALS(
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_id_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_PROP_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 prp_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_proposal_id.DELETE;
l_resource_id.DELETE;
l_resource_group_id.DELETE;
l_terr_id.DELETE;
INSERT INTO PRP_TERRITORY_ACCESSES
( proposal_access_id
,territory_id
,object_version_number
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,program_id
,program_login_id
,program_application_id
,request_id
,program_update_date
)
(
SELECT
V.acc_id,
l_terr_id(i),
1,
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.last_update_login,
p_terr_globals.prog_id,
p_terr_globals.prog_id,
p_terr_globals.prog_appl_id,
p_terr_globals.request_id,
SYSDATE
FROM
( SELECT DISTINCT a.proposal_access_id acc_id
FROM PRP_PROPOSAL_ACCESSES A
WHERE A.proposal_id=l_proposal_id(i)
AND NVL(A.resource_group_id,-777)=NVL(l_resource_group_id(i),-777)
AND A.resource_id=l_resource_id(i)
AND NOT EXISTS
(SELECT 'X'
FROM PRP_TERRITORY_ACCESSES AST
WHERE AST.proposal_access_id = A.proposal_access_id
AND AST.territory_id = l_terr_id(i))
) V
);
INSERT INTO PRP_TERRITORY_ACCESSES
( proposal_access_id
,territory_id
,object_version_number
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,program_id
,program_login_id
,program_application_id
,request_id
,program_update_date
)
(
SELECT
V.acc_id,
l_terr_id(i),
1,
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.user_id,
SYSDATE,
p_terr_globals.last_update_login,
p_terr_globals.prog_id,
p_terr_globals.prog_id,
p_terr_globals.prog_appl_id,
p_terr_globals.request_id,
SYSDATE
FROM
( SELECT DISTINCT a.proposal_access_id acc_id
FROM PRP_PROPOSAL_ACCESSES A
WHERE A.proposal_id=l_proposal_id(i)
AND NVL(A.resource_group_id,-777)=NVL(l_resource_group_id(i),-777)
AND A.resource_id=l_resource_id(i)
AND NOT EXISTS
(SELECT 'X'
FROM PRP_TERRITORY_ACCESSES AST
WHERE AST.proposal_access_id = A.proposal_access_id
AND AST.territory_id = l_terr_id(i))
) V
);
END LOOP; -- end loop for insert into territory accesses
l_proposal_id.DELETE;
l_resource_id.DELETE;
l_resource_group_id.DELETE;
l_terr_id.DELETE;
END INSERT_TERR_ACCESSES_PROPOSALS;
/************************** End Insert Into Terr Accesses*************/
/************************** Start Proposal Cleanup ***********************/
PROCEDURE Perform_Proposal_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_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
SELECT distinct trans_object_id
FROM JTF_TAE_1001_PROP_TRANS
WHERE worker_id=c_worker_id;
SELECT distinct trans_object_id
FROM JTF_TAE_1001_PROP_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;
DELETE PRP_PROPOSAL_ACCESSES ACC
WHERE ACC.PROPOSAL_ID=l_proposal_id(i)
AND ACC.KEEP_FLAG = 'N'
AND NOT EXISTS (SELECT 'X'
FROM JTF_TAE_1001_PROP_WINNERS W
WHERE W.TRANS_OBJECT_ID = ACC.PROPOSAL_ID
AND W.WORKER_ID = l_worker_id
AND W.RESOURCE_ID = ACC.RESOURCE_ID
AND NVL(W.GROUP_ID,-777)=NVL(ACC.RESOURCE_GROUP_ID,-777))
AND ROWNUM < G_DEL_REC;
DELETE PRP_PROPOSAL_ACCESSES ACC
WHERE ACC.PROPOSAL_ID=l_proposal_id(i)
AND ACC.KEEP_FLAG = 'N'
AND NOT EXISTS (SELECT 'X'
FROM JTF_TAE_1001_PROP_WINNERS W
WHERE W.TRANS_OBJECT_ID = ACC.PROPOSAL_ID
AND W.WORKER_ID = l_worker_id
AND W.RESOURCE_ID = ACC.RESOURCE_ID
AND NVL(W.GROUP_ID,-777)=NVL(ACC.RESOURCE_GROUP_ID,-777));