[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- Insert into Proposal Accesses from Winners
-- Log Debug Messages.
LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
P_MODULE_NAME => l_api_name,
p_LOG_MESSAGE => 'Before Calling Insert_Accesses');
INSERT_ACCESSES(
x_errbuf => l_errbuf,
x_retcode => l_retcode,
p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
x_return_status => l_return_status);
p_LOG_MESSAGE => 'After Calling Insert_Accesses... ' ||' x_return_Status: ' || l_return_status);
-- Insert into territory Accesses
-- Log Debug Messages.
LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
P_MODULE_NAME => l_api_name,
p_LOG_MESSAGE => 'Before Calling Insert_Terr_Accesses');
INSERT_TERR_ACCESSES(
x_errbuf => l_errbuf,
x_retcode => l_retcode,
p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
x_return_status => l_return_status);
p_LOG_MESSAGE => 'After Calling Insert_Terr_Accesses... ' ||' x_return_Status: ' || l_return_status);
| INSERT these members INTO winners IF they are NOT already IN winners.
+-------------------------------------------------------------------------*/
l_errbuf VARCHAR2(4000);
SELECT resource_id, group_id , person_id
BULK COLLECT INTO l_resource_id, l_group_id,l_person_id
FROM
(
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 = 'SALES'
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')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.CATEGORY IN ('EMPLOYEE')
) 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 = 'SALES'
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')
AND tr.active_flag = 'Y'
AND tres.resource_id = tm.team_resource_id
AND tres.category IN ('EMPLOYEE')
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 = 'SALES'
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')
AND tr.active_flag = 'Y'
AND tres.resource_id = tm.team_resource_id
AND tres.category IN ('EMPLOYEE')
) 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 = 'SALES'
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')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.category IN ('EMPLOYEE')
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 = p_WinningTerrMember_tbl.resource_id(l_index);
So we insert into p_WinningTerrMember_tbl directly*/
IF l_group_id(i) IS NOT NULL THEN --- Resources without groups should NOT be added to the sales team
p_WinningTerrMember_tbl.resource_id.EXTEND;
P_MODULE_NAME => 'Insert_Terr_Accesses',
p_LOG_MESSAGE => 'Error While Exploding Teams.. ' ||' x_errbuf: ' || x_errbuf);
| a sales ROLE AND are an employee AND INSERT back INTO winners IF they are
| NOT already IN winners.
+-------------------------------------------------------------------------*/
l_errbuf VARCHAR2(4000);
SELECT resource_id, group_id,person_id
BULK COLLECT INTO l_resource_id, l_group_id,l_person_id
FROM
(
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 = 'SALES'
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')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.category IN ('EMPLOYEE')
GROUP BY m.group_member_id, m.resource_id, m.person_id,
m.group_id, res.CATEGORY) j
WHERE j.group_id = p_WinningTerrMember_tbl.resource_id(l_index);
So we insert into p_WinningTerrMember_tbl directly*/
IF l_group_id(i) IS NOT NULL THEN --- Resources without groups should NOT be added to the sales team
p_WinningTerrMember_tbl.resource_id.EXTEND;
P_MODULE_NAME => 'Insert_Terr_Accesses',
p_LOG_MESSAGE => 'Error While Exploding Groups.. ' ||' x_errbuf: ' || x_errbuf);
/************************** Start Insert Accessses ***************/
PROCEDURE INSERT_ACCESSES(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
INSERT INTO PRP_PROPOSAL_ACCESSES
(proposal_access_id ,
last_update_date ,
last_updated_by,
creation_date ,
created_by ,
last_update_login,
proposal_id,
resource_id,
resource_group_id,
Access_level,
Keep_flag)
SELECT PRP_PROPOSAL_ACCESSES_S1.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
pp.Proposal_id,
p_WinningTerrMember_tbl.resource_id(l_index),
p_WinningTerrMember_tbl.group_id(l_index),
DECODE(p_WinningTerrMember_tbl.full_access_flag(l_index),'Y','FULL','READ'),
'N'
FROM PRP_PROPOSALS pp
WHERE pp.proposal_id = G_Proposal_ID
AND NOT EXISTS
( SELECT NULL FROM PRP_PROPOSAL_ACCESSES ACC
WHERE ACC.proposal_id = pp.proposal_id
AND ACC.resource_id = p_WinningTerrMember_tbl.resource_id(l_index)
AND ACC.resource_group_id = p_WinningTerrMember_tbl.group_id(l_index) );
FND_MSG_PUB.Add_Exc_Msg('PRP_RTTAP_INT_PVT','INSERT_ACCESS',SQLERRM);
P_MODULE_NAME => 'Insert_Terr_Accesses',
p_LOG_MESSAGE => 'Error While Inserting into PRP_Accesses... ' ||' x_errbuf: ' || x_errbuf);
END INSERT_ACCESSES;
/************************** End Insert Accessses ***************/
/************************** Start Insert Territory Accessses ***************/
PROCEDURE INSERT_TERR_ACCESSES(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
/*------------------------------------------------------------------------------+
| we are deleting all rows for the entity from as_territory_accesses prior to
| inserting into it because the logic for removing only certain terr_id/access_id
| combinations is very complex and could be slow..
+-------------------------------------------------------------------------------*/
x_return_status := FND_API.G_RET_STS_SUCCESS;
DELETE FROM PRP_TERRITORY_ACCESSES TACC
WHERE TACC.proposal_access_id IN
(SELECT ACC.proposal_access_id
FROM PRP_PROPOSAL_ACCESSES ACC
WHERE proposal_id = G_PROPOSAL_ID);
INSERT INTO PRP_TERRITORY_ACCESSES
(proposal_access_id,
territory_id,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login )
SELECT
ACC.proposal_access_id,
p_WinningTerrMember_tbl.terr_id(l_index),
1,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID
FROM PRP_PROPOSAL_ACCESSES ACC
WHERE ACC.proposal_id = G_PROPOSAL_ID
AND ACC.resource_id = p_WinningTerrMember_tbl.resource_id(l_index)
AND ACC.resource_group_id = p_WinningTerrMember_tbl.group_id(l_index)
AND NOT EXISTS ( SELECT 'Y'
FROM PRP_TERRITORY_ACCESSES
WHERE proposal_ACCESS_ID = ACC.Proposal_access_id
AND TERRITORY_ID = p_WinningTerrMember_tbl.terr_id(l_index)) ;
FND_MSG_PUB.Add_Exc_Msg('PRP_RTTAP_INT_PVT','INSERT_TERR_ACCESSES',SQLERRM);
P_MODULE_NAME => 'Insert_Terr_Accesses',
p_LOG_MESSAGE => 'Error While Inserting into PRP_Terroritory_Accesses... ' ||' x_errbuf: ' || x_errbuf);
END INSERT_TERR_ACCESSES;
/************************** End Insert Territory Accessses ***************/
/************************** Start Perform Cleanup ***************/
PROCEDURE PERFORM_CLEANUP(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
DELETE FROM PRP_PROPOSAL_ACCESSES ACC
WHERE proposal_id = G_PROPOSAL_ID
AND NVL(keep_flag, 'N') <> 'Y'
AND RESOURCE_ID||RESOURCE_GROUP_ID NOT IN (
SELECT RESTAB.RES||GRPTAB.GRP FROM
(SELECT rownum ROW_NUM,A.COLUMN_VALUE RES FROM TABLE(CAST(p_WinningTerrMember_tbl.resource_id AS jtf_terr_number_list)) a) RESTAB,
(SELECT rownum ROW_NUM,b.COLUMN_VALUE GRP FROM TABLE(CAST(p_WinningTerrMember_tbl.group_id AS jtf_terr_number_list)) b) GRPTAB
WHERE RESTAB.ROW_NUM = GRPTAB.ROW_NUM
);
P_MODULE_NAME => 'Insert_Terr_Accesses',
p_LOG_MESSAGE => 'Error in Perform_Cleanup... ' ||' x_errbuf: ' || x_errbuf);