[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| to insert into changed accounts since the oppty is
| processed immediately.
+-------------------------------------------------------*/
INSERT INTO AS_CHANGED_ACCOUNTS_ALL
( customer_id,
address_id,
lead_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
change_type )
SELECT customer_id,
address_id,
lead_id,
SYSDATE,
0,
SYSDATE,
0,
0,
'OPPORTUNITY'
FROM AS_LEADS_ALL LDS
WHERE lead_id = G_LEAD_ID
AND NOT EXISTS
( SELECT 'X'
FROM AS_CHANGED_ACCOUNTS_ALL ACC
WHERE LDS.customer_id = ACC.customer_id
--AND LDS.address_id = ACC.address_id -- fix for bug#5116019
AND LDS.lead_id = ACC.lead_id
AND ACC.request_id IS NULL );
-- Insert into Oppty Accesses from Winners
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
AS_RTTAP_OPPTY.INSERT_ACCESSES_OPPTYS(
x_errbuf => l_errbuf,
x_retcode => l_retcode,
p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
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_RTTAP_OPPTY.INSERT_TERR_ACCESSES_OPPTYS(
x_errbuf => l_errbuf,
x_retcode => l_retcode,
p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
x_return_status => l_return_status);
-- Remove (soft delete) records in access table that are not qualified
AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_START);
UPDATE AS_ACCESSES_ALL acc
SET object_version_number = nvl(object_version_number,0) + 1, acc.OPEN_FLAG = 'Y'
WHERE acc.LEAD_ID = p_lead_id
AND EXISTS
(select 1
from as_leads_all ld,
as_statuses_b st
where st.opp_open_status_flag = 'Y'
and st.status_code = ld.status
and ld.lead_id = p_lead_id )
AND nvl(acc.OPEN_FLAG, 'N') <> 'Y';
UPDATE AS_ACCESSES_ALL acc
SET object_version_number = nvl(object_version_number,0) + 1, acc.OPEN_FLAG = 'N'
WHERE acc.LEAD_ID = p_lead_id
AND NOT EXISTS
(select 1
from as_leads_all ld,
as_statuses_b st
where st.opp_open_status_flag = 'Y'
and st.status_code = ld.status
and ld.lead_id = p_lead_id )
AND acc.OPEN_FLAG IS NOT NULL;
| 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 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 = 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;
| 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 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 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 = 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;
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 = FND_GLOBAL.USER_ID,
ACC.last_update_login = FND_GLOBAL.USER_ID,
ACC.team_leader_flag = NVL(p_WinningTerrMember_tbl.full_access_flag(l_index),'N')
WHERE ACC.lead_id = G_LEAD_ID
AND ACC.salesforce_id = p_WinningTerrMember_tbl.resource_id(l_index)
AND ACC.sales_group_id = p_WinningTerrMember_tbl.group_id(l_index)
AND NVL(ACC.team_leader_flag,'N') <> NVL(p_WinningTerrMember_tbl.full_access_flag(l_index),'N');
PROCEDURE INSERT_ACCESSES_OPPTYS(
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
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_START);
AS_GAR.LOG(G_ENTITY || G_LEAD_ID || '::' || 'BEFORE INSERT INTO AS_ACCESSED_ALL RESOURCE/GROUP::' || p_WinningTerrMember_tbl.resource_id(l_index)
|| '/' || p_WinningTerrMember_tbl.group_id(l_index));
INSERT INTO AS_ACCESSES_ALL
(access_id ,
last_update_date ,
last_updated_by,
creation_date ,
created_by ,
last_update_login,
access_type ,
freeze_flag,
reassign_flag,
team_leader_flag ,
customer_id ,
address_id ,
salesforce_id ,
person_id ,
sales_group_id,
lead_id,
created_by_tap_flag,
owner_flag,
open_flag,org_id)
SELECT AS_ACCESSES_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
'Online',
'N',
'N',
DECODE(p_WinningTerrMember_tbl.full_access_flag(l_index),'Y','Y','N'),
LDS.customer_id,
LDS.address_Id,
p_WinningTerrMember_tbl.resource_id(l_index),
p_WinningTerrMember_tbl.person_id(l_index),
p_WinningTerrMember_tbl.group_id(l_index),
LDS.lead_id,
'Y',
'N',
NVL(ST.opp_open_status_flag,'N'),
p_WinningTerrMember_tbl.org_id(l_index)
FROM AS_LEADS_ALL LDS, AS_STATUSES_B ST
WHERE LDS.status = ST.status_code
AND LDS.lead_id = G_LEAD_ID
AND NOT EXISTS
( SELECT NULL FROM AS_ACCESSES_ALL ACC
WHERE ACC.lead_id = LDS.lead_id
AND ACC.salesforce_id = p_WinningTerrMember_tbl.resource_id(l_index)
AND ACC.sales_group_id = p_WinningTerrMember_tbl.group_id(l_index) );
END INSERT_ACCESSES_OPPTYS;
PROCEDURE INSERT_TERR_ACCESSES_OPPTYS(
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
AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
| 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 AS_TERRITORY_ACCESSES TACC
WHERE TACC.access_id IN
(SELECT ACC.access_id
FROM AS_ACCESSES_ALL ACC
WHERE lead_id = G_LEAD_ID);
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 )
SELECT
ACC.access_id,
p_WinningTerrMember_tbl.terr_id(l_index),
p_WinningTerrMember_tbl.terr_id(l_index),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID
FROM AS_ACCESSES_ALL ACC
WHERE ACC.lead_id = G_LEAD_ID
AND ACC.salesforce_id = p_WinningTerrMember_tbl.resource_id(l_index)
AND ACC.sales_group_id = p_WinningTerrMember_tbl.group_id(l_index)
AND NOT EXISTS ( SELECT 'Y'
FROM AS_TERRITORY_ACCESSES
WHERE ACCESS_ID = ACC.access_id
AND TERRITORY_ID = p_WinningTerrMember_tbl.terr_id(l_index)) ;
END INSERT_TERR_ACCESSES_OPPTYS;
DELETE FROM AS_ACCESSES_ALL ACC
WHERE lead_id = G_LEAD_ID
AND NVL(freeze_flag, 'N') <> 'Y'
AND SALESFORCE_ID||SALES_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
)
AND NOT EXISTS (SELECT 'X'
FROM AS_SALES_CREDITS
WHERE salesforce_id = ACC.salesforce_id
AND salesgroup_id = ACC.sales_group_id
AND lead_id = G_LEAD_ID) ;
SELECT MAX(AAA.access_id) access_id -- /*+ index(aaa as_accesses_n3) */
FROM AS_ACCESSES_ALL AAA
WHERE AAA.lead_id = G_LEAD_ID
AND NVL(AAA.CREATED_BY_TAP_FLAG,'N') = 'Y' ;
SELECT 'X'
FROM AS_ACCESSES_ALL
WHERE lead_id = G_LEAD_ID
AND owner_flag = 'Y';
| Select MAX(access_id) from as_accesses for this lead where the created_by_tap
| flag is set and owner flag is not set..Is there anything else that we need to do ?
| Then update accesses,leads,and scd
+------------------------------------------------------------------------*/
x_return_status := FND_API.G_RET_STS_SUCCESS;
UPDATE AS_LEADS_ALL sl
SET SL.object_version_number = nvl(sl.object_version_number,0) + 1,
SL.last_update_date = SYSDATE,
SL.last_updated_by = FND_GLOBAL.USER_ID,
SL.last_update_login = FND_GLOBAL.USER_ID,
SL.owner_salesforce_id = NULL,
SL.owner_sales_group_id = NULL
WHERE SL.lead_id = G_LEAD_ID ;
UPDATE AS_SALES_CREDITS_DENORM SCD
SET SCD.object_version_number = nvl(scd.object_version_number,0) + 1,
SCD.last_update_date = SYSDATE,
SCD.last_updated_by = FND_GLOBAL.USER_ID,
SCD.last_update_login = FND_GLOBAL.USER_ID,
SCD.owner_salesforce_id = NULL,
SCD.owner_sales_group_id = NULL
WHERE SCD.lead_id = G_LEAD_ID ;
UPDATE AS_ACCESSES_ALL AAA
SET AAA.owner_flag = 'Y',
AAA.object_version_number = nvl(AAA.object_version_number,0) + 1,
AAA.last_update_date = SYSDATE,
AAA.last_updated_by = FND_GLOBAL.USER_ID,
AAA.last_update_login = FND_GLOBAL.USER_ID
WHERE access_id = v_acc_id
RETURNING salesforce_id,sales_group_id INTO v_srep_id,v_grp_id;
UPDATE AS_LEADS_ALL sl
SET SL.object_version_number = nvl(sl.object_version_number,0) + 1,
SL.last_update_date = SYSDATE,
SL.last_updated_by = FND_GLOBAL.USER_ID,
SL.last_update_login = FND_GLOBAL.USER_ID,
SL.owner_salesforce_id = v_srep_id,
SL.owner_sales_group_id = v_grp_id
WHERE SL.lead_id = G_LEAD_ID ;
UPDATE AS_SALES_CREDITS_DENORM SCD
SET SCD.object_version_number = nvl(scd.object_version_number,0) + 1,
SCD.last_update_date = SYSDATE,
SCD.last_updated_by = FND_GLOBAL.USER_ID,
SCD.last_update_login = FND_GLOBAL.USER_ID,
SCD.owner_salesforce_id = v_srep_id,
SCD.owner_sales_group_id = v_grp_id
WHERE SCD.lead_id = G_LEAD_ID ;
SELECT l_event_name || AS_BUSINESS_EVENT_S.nextval
INTO x_event_key
FROM DUAL;