[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Quote_Header_Id, Quote_Number, Party_Id, Sold_To_Party_Site_Id, Cust_Party_Id
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;
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';
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;