[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_ACCESSES_ACCOUNTS(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
aso_debug_pub.add('Begin INSERT_ACCESSES_ACCOUNTS',1,'Y');
--added inline view in the select clause of Insert statement to fetch the salesforce role code for Employee resource --fix for bug 5869095
IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_EMPLOYEE' AND p_WinningTerrMember_tbl.group_id(l_index) IS NOT NULL THEN
aso_debug_pub.add('Begin INSERT_ACCESSES_ACCOUNTS'|| p_WinningTerrMember_tbl.resource_type(l_index),1,'Y');
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,
ROLE_ID,
OBJECT_VERSION_NUMBER )
select
ASO_QUOTE_ACCESSES_S.nextval,
P_Qte_Header_Rec.quote_number,
p_WinningTerrMember_tbl.resource_id(l_index),
p_WinningTerrMember_tbl.group_ID(l_index),
FND_GLOBAL.USER_ID ,
SYSDATE ,
FND_GLOBAL.USER_ID ,
FND_GLOBAL.USER_ID ,
SYSDATE,
null,
null
from dual where not exists (select 1 from
ASO_QUOTE_ACCESSES where resource_id = p_WinningTerrMember_tbl.resource_id(l_index)
and quote_number = P_Qte_Header_Rec.quote_number);
aso_debug_pub.add('Proc INSERT_ACCESSES_ACCOUNTS exception part',1,'Y');
END INSERT_ACCESSES_ACCOUNTS;
| 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;
SELECT Quote_Header_Id, Quote_Number, Party_Id, Sold_To_Party_Site_Id, Cust_Party_Id,
cust_account_id -- Code change done for Bug 11076978
FROM ASO_QUOTE_HEADERS_ALL
WHERE Quote_Header_Id = l_qte_hdr;
SELECT Quote_Header_Id, Last_Update_Date
FROM ASO_QUOTE_HEADERS_ALL
WHERE Quote_header_Id = l_qte_hdr;
SELECT 'Y'
FROM ASO_QUOTE_HEADERS_ALL
WHERE Quote_Header_Id = l_qte_hdr;
CURSOR C_Get_Update_Date(qte_hdr_id NUMBER) IS
SELECT Last_Update_Date
FROM ASO_QUOTE_HEADERS_ALL
WHERE Quote_Header_Id = qte_hdr_id;
SELECT 'Y'
FROM ASO_QUOTE_ACCESSES
WHERE Quote_Number = l_qte_number;
SELECT Resource_Id
FROM ASO_QUOTE_ACCESSES
WHERE Quote_Number = l_qte_number;
SELECT resource_id
FROM JTF_RS_RESOURCE_EXTNS
WHERE user_id = l_user_id
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
SELECT 'Y', Resource_Grp_Id
FROM ASO_QUOTE_ACCESSES
WHERE Quote_Number = l_qte_num
AND Resource_Id = l_res;
SELECT 'Y'
/* FROM JTF_RS_SRP_VL */ --Commented Code Yogeshwar (MOAC)
FROM JTF_RS_SALESREPS_MO_V -- New Code Yogeshwar (MOAC)
WHERE resource_id = l_res_id
AND NVL(status,'A') = 'A'
AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate);
SELECT Role_Id
FROM JTF_RS_ROLES_B
WHERE Role_Code = l_code
AND Role_Type_Code IN ('TELESALES', 'SALES', 'FIELDSALES', 'PRM');
SELECT Role_Id
FROM JTF_RS_ROLE_RELATIONS
WHERE Role_Resource_Id = l_res
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
SELECT Resource_Id
/* FROM JTF_RS_SRP_VL */ --Commented Code Yogeshwar (MOAC)
FROM JTF_RS_SALESREPS_MO_V --New Code Yogeshwar (MOAC)
WHERE Salesrep_Number = l_Srep
AND NVL(status,'A') = 'A'
AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate) ;
OPEN C_Get_Update_Date(P_Qte_Header_Rec.Quote_Header_Id);
FETCH C_Get_Update_Date INTO l_last_upd_date;
IF (C_Get_Update_Date%NOTFOUND) OR
(l_last_upd_date IS NULL OR l_last_upd_date = FND_API.G_MISS_DATE) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
CLOSE C_Get_Update_Date;
CLOSE C_Get_Update_Date;
IF (p_qte_header_rec.last_update_date IS NOT NULL AND
p_qte_header_rec.last_update_date <> FND_API.G_MISS_DATE) AND
(l_last_upd_date <> p_qte_header_rec.last_update_date) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
DELETE FROM ASO_STEAM_TEMP;
IF P_Operation <> 'CREATE' AND P_Operation <> 'UPDATE' AND P_Operation <> 'SUBMIT' THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name('ASO', 'ASO_NO_SALES_TEAM');
IF P_Operation = 'UPDATE' THEN -- istore case
IF aso_debug_pub.g_debug_flag = 'Y' THEN
aso_debug_pub.add('Before Update_Primary_SalesAgent(oprn=update): ',1,'Y');
ASO_SALES_TEAM_PVT.Update_Primary_SalesInfo (
P_Init_Msg_List => FND_API.G_FALSE,
P_Qte_Header_Rec => l_Qte_Header_Rec,
P_Primary_SalesAgent => NULL,
P_Primary_SalesGrp => NULL,
P_reassign_flag => l_reassign_flag,
X_Qte_Header_Rec => x_Qte_Header_Rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
aso_debug_pub.add('After Update_Primary_SalesInfo(oprn=update): '||x_return_status,1,'Y');
ASO_QUOTE_ACCESSES_PKG.Insert_Row(
px_ACCESS_ID => l_sequence,
p_QUOTE_NUMBER => l_Qte_Header_Rec.Quote_Number,
p_RESOURCE_ID => l_creator_res,
p_RESOURCE_GRP_ID => l_creator_grp,
p_CREATED_BY => G_USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_REQUEST_ID => FND_API.G_MISS_NUM,
p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
p_PROGRAM_ID => FND_API.G_MISS_NUM,
p_PROGRAM_UPDATE_DATE => FND_API.G_MISS_DATE,
p_KEEP_FLAG => 'N',
p_UPDATE_ACCESS_FLAG => 'Y',
p_CREATED_BY_TAP_FLAG => FND_API.G_MISS_CHAR,
p_TERRITORY_ID => FND_API.G_MISS_NUM,
p_TERRITORY_SOURCE_FLAG => 'N',
p_ROLE_ID => l_creator_role,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE16 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE17 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE18 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE19 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE20 => FND_API.G_MISS_CHAR,
p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
);
aso_debug_pub.add('nores:create:Assign_Sales_Team: Before primary salesagent insert:l_valid '||l_valid,1,'N');
ASO_QUOTE_ACCESSES_PKG.Insert_Row(
px_ACCESS_ID => l_sequence,
p_QUOTE_NUMBER => P_Qte_Header_Rec.Quote_Number,
p_RESOURCE_ID => l_primary_salesagent,
p_RESOURCE_GRP_ID => l_primary_res_grp,
p_CREATED_BY => G_USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_REQUEST_ID => FND_API.G_MISS_NUM,
p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
p_PROGRAM_ID => FND_API.G_MISS_NUM,
p_PROGRAM_UPDATE_DATE => FND_API.G_MISS_DATE,
p_KEEP_FLAG => FND_API.G_MISS_CHAR,
p_UPDATE_ACCESS_FLAG => 'Y',
p_CREATED_BY_TAP_FLAG => FND_API.G_MISS_CHAR,
p_TERRITORY_ID => FND_API.G_MISS_NUM,
p_TERRITORY_SOURCE_FLAG => FND_API.G_MISS_CHAR,
p_ROLE_ID => l_primary_role,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE16 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE17 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE18 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE19 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE20 => FND_API.G_MISS_CHAR,
p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
);
aso_debug_pub.add('nores:create:Assign_Sales_Team: Before primary salesagent insert in qte_hdr ',1,'N');
UPDATE ASO_QUOTE_HEADERS_ALL
SET Resource_Id = l_primary_salesagent,
Resource_Grp_Id = l_primary_res_grp,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id,
object_version_number = object_version_number+1
WHERE quote_number = P_Qte_Header_Rec.quote_number
AND max_version_flag = 'Y'
RETURNING quote_header_id, last_update_date, resource_id, resource_grp_id, object_version_number
INTO l_qte_header_rec.Quote_Header_Id, l_qte_header_rec.Last_Update_Date,
l_qte_header_rec.resource_id, l_qte_header_rec.resource_grp_id, l_qte_header_rec.object_version_number;
aso_debug_pub.add('Before Temp_Insert'||NVL(lx_gen_return_Rec.resource_id.COUNT,0),1,'Y');
INSERT INTO ASO_STEAM_TEMP ( Access_Id,
Quote_Number,
Resource_Id,
Resource_Grp_Id,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Login,
Last_Update_Date,
Keep_Flag,
Full_Access_Flag,
Territory_Id,
Territory_Source_Flag,
Role_Id )
SELECT null,
l_Qte_Header_Rec.Quote_Number,
lx_gen_return_Rec.resource_id(i),
lx_gen_return_Rec.group_id(i),
G_USER_ID,
SYSDATE,
G_USER_ID,
G_LOGIN_ID,
SYSDATE,
NULL,
lx_gen_return_Rec.full_access_flag(i),
lx_gen_return_Rec.terr_id(i),
'Y',
DECODE(lx_gen_return_Rec.role(i),NULL,NULL,A.Role_Id)
FROM JTF_RS_ROLES_B A
WHERE A.Role_Code = NVL(lx_gen_return_Rec.role(i),A.Role_Code)
AND rownum = 1;
aso_debug_pub.add('After Temp Insert',1,'Y');
select count(*) into i from aso_steam_temp;
aso_debug_pub.add('Before Delete Not Kept Existing Res ',1,'Y');
DELETE FROM ASO_QUOTE_ACCESSES C
WHERE C.resource_id NOT IN
(SELECT A.resource_id
FROM ASO_QUOTE_ACCESSES A , ASO_STEAM_TEMP B
WHERE ((A.resource_id = B.resource_id
AND NVL(A.resource_grp_id, -999) = NVL(B.resource_grp_id, -999)
AND NVL(A.role_id, -999) = NVL(B.role_id, -999)
AND NVL(A.keep_flag,'N') = 'N')
OR NVL(A.keep_flag,'N') = 'Y')
AND A.Quote_Number = l_qte_header_rec.quote_number)
AND C.Quote_Number = l_qte_header_rec.quote_number;
select count(*) into i from aso_steam_temp;
aso_debug_pub.add('Before Delete Kept Res ',1,'Y');
DELETE FROM ASO_STEAM_TEMP
WHERE Resource_Id = Keep_Res_Id(i);
select count(*) into i from aso_steam_temp;
aso_debug_pub.add('Before Delete Invalid Roles ',1,'Y');
DELETE FROM ASO_STEAM_TEMP
WHERE Role_Id IS NOT NULL
AND Role_Id NOT IN ( SELECT Role_Id
FROM JTF_RS_ROLES_B
WHERE Role_Type_Code IN ('TELESALES', 'SALES','FIELDSALES','PRM'));
select count(*) into i from aso_steam_temp;
aso_debug_pub.add('Before Delete Duplicate Res/Roles/Grp combos ',1,'Y');
l_dynamic := 'DELETE FROM ASO_STEAM_TEMP '||
'WHERE rowid NOT IN ( SELECT rowid '||
'FROM ( SELECT rowid, dense_rank() OVER '||
'( PARTITION BY Resource_Id '||
'ORDER BY Role_Id DESC nulls last, Resource_Grp_Id DESC nulls last) AS Rank_Val '||
'FROM ASO_STEAM_TEMP '||
'ORDER BY Role_Id DESC nulls last, Resource_Grp_Id DESC nulls last ) '||
'WHERE Rank_Val = 1 )';
select count(*) into i from aso_steam_temp;
aso_debug_pub.add('Before Delete Duplicate Resources ',1,'Y');
DELETE FROM ASO_STEAM_TEMP
WHERE rowid IN (
SELECT rowid FROM ASO_STEAM_TEMP
GROUP BY rowid, Resource_Id
MINUS
SELECT min(rowid) FROM ASO_STEAM_TEMP
GROUP BY Resource_Id);
select count(*) into i from aso_steam_temp;
aso_debug_pub.add('Before Insert into Quote_Accesses ',1,'Y');
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,
UPDATE_ACCESS_FLAG,
TERRITORY_ID,
TERRITORY_SOURCE_FLAG,
ROLE_ID )
SELECT ASO_QUOTE_ACCESSES_S.nextval,
Quote_Number,
Resource_Id,
Resource_Grp_Id,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Login,
Last_Update_Date,
Full_Access_Flag,
Territory_Id,
Territory_Source_Flag,
Role_Id
FROM ASO_STEAM_TEMP
WHERE Quote_Number = l_Qte_Header_Rec.Quote_Number
and not exists (select 1 from
ASO_QUOTE_ACCESSES where resource_id = ASO_STEAM_TEMP.resource_id
and quote_number = l_Qte_Header_Rec.Quote_Number);
aso_debug_pub.add('After Insert into Quote_Accesses ',1,'Y');
ASO_QUOTE_ACCESSES_PKG.Insert_Row(
px_ACCESS_ID => l_sequence,
p_QUOTE_NUMBER => l_Qte_Header_Rec.Quote_Number,
p_RESOURCE_ID => l_creator_res,
p_RESOURCE_GRP_ID => l_creator_grp,
p_CREATED_BY => G_USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_REQUEST_ID => FND_API.G_MISS_NUM,
p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
p_PROGRAM_ID => FND_API.G_MISS_NUM,
p_PROGRAM_UPDATE_DATE => FND_API.G_MISS_DATE,
p_KEEP_FLAG => 'N',
p_UPDATE_ACCESS_FLAG => 'Y',
p_CREATED_BY_TAP_FLAG => FND_API.G_MISS_CHAR,
p_TERRITORY_ID => FND_API.G_MISS_NUM,
p_TERRITORY_SOURCE_FLAG => 'N',
p_ROLE_ID => l_creator_role,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE16 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE17 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE18 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE19 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE20 => FND_API.G_MISS_CHAR,
p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
);
aso_debug_pub.add('Before Update_Primary_SalesAgent: ',1,'Y');
ASO_SALES_TEAM_PVT.Update_Primary_SalesInfo (
P_Init_Msg_List => FND_API.G_FALSE,
P_Qte_Header_Rec => l_Qte_Header_Rec,
P_Primary_SalesAgent => l_primary_salesagent,
P_Primary_SalesGrp => l_creator_grp,
P_reassign_flag => l_reassign_flag,
X_Qte_Header_Rec => x_Qte_Header_Rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
aso_debug_pub.add('After Update_Primary_SalesInfo: '||x_return_status,1,'Y');
aso_debug_pub.add('ASO_SALES_TEAM_PVT.Assign_Sales_Team : Calling ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES, quote number : ' || l_Qte_Header_Rec.Quote_Number, 1, 'Y');
ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES(l_Qte_Header_Rec.Quote_Number);
SELECT UPPER(party_name) party_name, UPPER(category_code) category_code, employees_total,
UPPER(SIC_Code) SIC_Code, UPPER(SIC_Code_Type) SIC_Code_Type
FROM HZ_PARTIES
WHERE party_id = l_party_id;
SELECT UPPER(B.city) city, UPPER(B.county) county, UPPER(B.state) state, UPPER(B.province) province,
UPPER(B.postal_code) postal_code, UPPER(B.country) country
FROM HZ_PARTY_SITES A, HZ_LOCATIONS B
WHERE A.Location_Id = B.Location_Id
AND A.party_site_id = l_party_site_id;
SELECT UPPER(Phone_Area_Code) Phone_Area_Code
FROM HZ_CONTACT_POINTS
WHERE Owner_Table_Id = l_party_id
AND Owner_Table_Name = 'HZ_PARTIES'
AND Contact_Point_Type = 'PHONE'
AND Status = 'A'
AND Primary_Flag = 'Y';
| 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;
INSERT_ACCESSES_ACCOUNTS(
x_errbuf => l_errbuf,
x_retcode => l_retcode,
P_Qte_Header_Rec=>P_qte_header_rec ,
p_WinningTerrMember_tbl => X_Winners_Rec,
x_return_status => l_return_status);
PROCEDURE Update_Primary_SalesInfo(
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
P_Primary_SalesAgent IN NUMBER,
P_Primary_SalesGrp IN NUMBER,
P_Reassign_Flag IN VARCHAR2,
X_Qte_Header_Rec OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Header_Rec_Type,
X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)
IS
CURSOR C_Get_Primary_Resource (l_qte_number NUMBER) IS
SELECT Resource_Id, Resource_Grp_Id
FROM ASO_QUOTE_HEADERS_ALL
WHERE Quote_Number = l_qte_number
AND Max_Version_Flag = 'Y';
SELECT 'Y'
FROM ASO_QUOTE_ACCESSES
WHERE Resource_Id = l_res
AND Quote_Number = l_qte_num;
SELECT A.Resource_Id, A.Resource_Grp_Id, A.Role_Id
/* FROM ASO_QUOTE_ACCESSES A, JTF_RS_SALESREPS B, */ --Commented Code Yogeshwar (MOAC)
FROM ASO_QUOTE_ACCESSES A, JTF_RS_SALESREPS_MO_V B, --New Code Yogeshwar (MOAC)
OE_SALES_CREDIT_TYPES ST
WHERE B.sales_credit_type_id = ST.sales_credit_type_id
AND A.Resource_Id = B.Resource_Id
AND A.Update_Access_Flag = 'Y'
AND A.Quote_Number = l_qte_num
AND NVL(B.status,'A') = 'A'
AND SYSDATE BETWEEN B.start_date_active AND NVL(B.end_date_active, SYSDATE) ;
SELECT Role_Id
FROM JTF_RS_ROLE_RELATIONS
WHERE Role_Resource_Id = l_res
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
SELECT Resource_Id
/* FROM JTF_RS_SRP_VL */ --Commented Code Yogeshwar (MOAC)
FROM JTF_RS_SALESREPS_MO_V --New Code Yogeshwar (MOAC)
WHERE Salesrep_Number = l_Srep
AND NVL(status,'A') = 'A'
AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate) ;
l_api_name CONSTANT VARCHAR2 ( 50 ) := 'Update_Primary_SalesInfo';
SAVEPOINT UPDATE_PRIMARY_SALESINFO_PVT;
aso_debug_pub.add('Assign_Sales_Team: Before primary salesagent insert: ',1,'N');
ASO_QUOTE_ACCESSES_PKG.Insert_Row(
px_ACCESS_ID => l_sequence,
p_QUOTE_NUMBER => P_Qte_Header_Rec.Quote_Number,
p_RESOURCE_ID => l_primary_salesagent,
p_RESOURCE_GRP_ID => l_primary_res_grp,
p_CREATED_BY => G_USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_REQUEST_ID => FND_API.G_MISS_NUM,
p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
p_PROGRAM_ID => FND_API.G_MISS_NUM,
p_PROGRAM_UPDATE_DATE => FND_API.G_MISS_DATE,
p_KEEP_FLAG => FND_API.G_MISS_CHAR,
p_UPDATE_ACCESS_FLAG => 'Y',
p_CREATED_BY_TAP_FLAG => FND_API.G_MISS_CHAR,
p_TERRITORY_ID => FND_API.G_MISS_NUM,
p_TERRITORY_SOURCE_FLAG => FND_API.G_MISS_CHAR,
p_ROLE_ID => l_primary_role,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE16 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE17 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE18 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE19 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE20 => FND_API.G_MISS_CHAR,
p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
);
aso_debug_pub.add('Assign_Sales_Team: Update primary salesagent in Hdr ',1,'N');
UPDATE ASO_QUOTE_HEADERS_ALL
SET Resource_Id = l_primary_salesagent,
Resource_Grp_Id = l_primary_res_grp,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id,
object_version_number = object_version_number+1
WHERE quote_number = P_Qte_Header_Rec.quote_number
AND max_version_flag = 'Y'
RETURNING quote_header_id, last_update_date, resource_id, resource_grp_id, object_version_number
INTO x_qte_header_rec.Quote_Header_Id, x_qte_header_rec.Last_Update_Date,
x_qte_header_rec.resource_id, x_qte_header_rec.resource_grp_id, x_qte_header_rec.object_version_number;
END Update_Primary_SalesInfo;
SELECT resource_id
FROM JTF_RS_RESOURCE_EXTNS
WHERE user_id = l_user_id
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
SELECT 'Y'
/* FROM JTF_RS_SRP_VL */ --Commented Code Yogeshwar (MOAC)
FROM JTF_RS_SALESREPS_MO_V --New Code Yogeshwar (MOAC)
WHERE resource_id = l_res_id
AND NVL(status,'A') = 'A'
AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate) ;
SELECT A.Resource_Id, A.Resource_Grp_Id
/* FROM ASO_QUOTE_ACCESSES A, JTF_RS_SALESREPS B, */ --Commented Code Yogeshwar ( MOAC)
FROM ASO_QUOTE_ACCESSES A,JTF_RS_SALESREPS_MO_V B, --New Code Yogeshwar (MOAC)
OE_SALES_CREDIT_TYPES ST
WHERE B.sales_credit_type_id = ST.sales_credit_type_id
AND A.Resource_Id = B.Resource_Id
AND A.Update_Access_Flag = 'Y'
AND A.Quote_Number = l_qte_num
AND NVL(B.status,'A') = 'A'
AND SYSDATE BETWEEN B.start_date_active AND NVL(B.end_date_active, SYSDATE) ;
SELECT 'Y', Resource_Grp_Id
FROM ASO_QUOTE_ACCESSES
WHERE Quote_Number = l_qte_num
AND Resource_Id = l_res;
SELECT Role_Id
FROM JTF_RS_ROLES_B
WHERE Role_Code = l_code
AND Role_Type_Code IN ('TELESALES', 'SALES', 'FIELDSALES', 'PRM');
SELECT Role_Id
FROM JTF_RS_ROLE_RELATIONS
WHERE Role_Resource_Id = l_res
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
SELECT Resource_Id
/* FROM JTF_RS_SRP_VL */ --Commented Code Yogeshwar (MOAC)
FROM JTF_RS_SALESREPS_MO_V --New Code Yogeshwar (MOAC)
WHERE Salesrep_Number = l_Srep
AND NVL(status,'A') = 'A'
AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate) ;
ASO_QUOTE_ACCESSES_PKG.Insert_Row(
px_ACCESS_ID => l_sequence,
p_QUOTE_NUMBER => P_Qte_Header_Rec.Quote_Number,
p_RESOURCE_ID => l_creator_res,
p_RESOURCE_GRP_ID => l_primary_res_grp,
p_CREATED_BY => G_USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_REQUEST_ID => FND_API.G_MISS_NUM,
p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
p_PROGRAM_ID => FND_API.G_MISS_NUM,
p_PROGRAM_UPDATE_DATE => FND_API.G_MISS_DATE,
p_KEEP_FLAG => 'N',
p_UPDATE_ACCESS_FLAG => 'Y',
p_CREATED_BY_TAP_FLAG => FND_API.G_MISS_CHAR,
p_TERRITORY_ID => FND_API.G_MISS_NUM,
p_TERRITORY_SOURCE_FLAG => 'N',
p_ROLE_ID => l_primary_role,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE16 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE17 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE18 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE19 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE20 => FND_API.G_MISS_CHAR,
p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
);
aso_debug_pub.add('Opp_Qte_PS: Before primary salesagent insert: ',1,'N');
ASO_QUOTE_ACCESSES_PKG.Insert_Row(
px_ACCESS_ID => l_sequence,
p_QUOTE_NUMBER => P_Qte_Header_Rec.Quote_Number,
p_RESOURCE_ID => l_primary_salesagent,
p_RESOURCE_GRP_ID => l_primary_res_grp,
p_CREATED_BY => G_USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_REQUEST_ID => FND_API.G_MISS_NUM,
p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
p_PROGRAM_ID => FND_API.G_MISS_NUM,
p_PROGRAM_UPDATE_DATE => FND_API.G_MISS_DATE,
p_KEEP_FLAG => FND_API.G_MISS_CHAR,
p_UPDATE_ACCESS_FLAG => 'Y',
p_CREATED_BY_TAP_FLAG => FND_API.G_MISS_CHAR,
p_TERRITORY_ID => FND_API.G_MISS_NUM,
p_TERRITORY_SOURCE_FLAG => FND_API.G_MISS_CHAR,
p_ROLE_ID => l_primary_role,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE16 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE17 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE18 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE19 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE20 => FND_API.G_MISS_CHAR,
p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
);
aso_debug_pub.add('Opp_Qte_PS: Update primary salesagent in Hdr ',1,'N');
UPDATE ASO_QUOTE_HEADERS_ALL
SET Resource_Id = l_primary_salesagent,
Resource_Grp_Id = l_primary_res_grp,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id,
object_version_number = object_version_number+1
WHERE quote_number = P_Qte_Header_Rec.quote_number
AND max_version_flag = 'Y'
RETURNING quote_header_id, last_update_date, resource_id, resource_grp_id, object_version_number
INTO x_qte_header_rec.Quote_Header_Id, x_qte_header_rec.Last_Update_Date,
x_qte_header_rec.resource_id, x_qte_header_rec.resource_grp_id, x_qte_header_rec.object_version_number;