1 PACKAGE BODY AS_LEAD_ROUTING_WF_CUHK AS
2 /* $Header: asxcldob.pls 115.7 2003/01/22 03:12:05 solin ship $ */
3
4 -- Start of Comments
5 -- Package Name : AS_LEAD_ROUTING_WF_CUHK
6 -- Purpose : This file is customizable for Oracle customers to
7 -- add logic to get owner of the lead.
8 -- NOTE :
9 -- History :
10 -- 12/06/2001 SOLIN Created
11 -- This is sample package body. This file should be
12 -- provided by Oracle's customer.
13 -- 12/08/2001 SOLIN, bug 2137318.
14 -- Customize for Oracle internal.
15 -- 11/19/2002 SOLIN, Bug 2629604.
16 -- The resource from territory API will have higher
17 -- precedence to be lead owner
18 -- 12/23/2002 SOLIN Bug 2724757
19 -- Incorrect lead owner due to extra resources
20 -- by build_lead_sales_team and rebuild_lead_sales_team.
21 -- End of Comments
22
23 /*-------------------------------------------------------------------------*
24 |
25 | PRIVATE CONSTANTS
26 |
27 *-------------------------------------------------------------------------*/
28 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_LEAD_ROUTING_WF_CUHK';
29 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxcldob.pls';
30
31 /*-------------------------------------------------------------------------*
32 |
33 | PRIVATE DATATYPES
34 |
35 *-------------------------------------------------------------------------*/
36
37 /*-------------------------------------------------------------------------*
38 |
39 | PRIVATE VARIABLES
40 |
41 *-------------------------------------------------------------------------*/
42
43 /*-------------------------------------------------------------------------*
44 |
45 | PRIVATE ROUTINES
46 |
47 *-------------------------------------------------------------------------*/
48
49 -- Start of Comments
50 --
51 -- API name : Get_Owner_Pre
52 -- Parameters :
53 -- IN :
54 -- p_api_version_number:
55 -- p_init_msg_list :
56 -- p_validation_level :
57 -- p_commit :
58 -- The above four parameters are standard input.
59 -- p_resource_id_tbl :
60 -- p_group_id_tbl :
61 -- p_person_id_tbl :
62 -- The above three parameters store the available
63 -- resources for this customized package to decide
64 -- owner of the sales lead. Their datatype is
65 -- TABLE of NUMBERs.
66 -- p_resource_flag_tbl :
67 -- This parameter specify the source of the
68 -- resource.
69 -- 'D': This is default resource, comes from the
70 -- profile AS_DEFAULT_RESOURCE_ID, "OS:
71 -- Default Resource ID used for Sales Lead
72 -- Assignment"
73 -- 'L': This is login user.
74 -- 'T': This resource comes from territory
75 -- definition.
76 -- p_sales_lead_rec :
77 -- This is the whole definition of the sales lead.
78 -- This record is provided to help Oracle customer
79 -- decide sales lead owner.
80 -- OUT :
81 -- x_resource_id :
82 -- x_group_id :
83 -- x_person_id :
84 -- The above three parameters store the result
85 -- of this user hook. It will be set as sales
86 -- lead owner. If x_resource_id is NULL, owner
87 -- will be decided based upon Oracle's logic.
88 -- x_return_status :
89 -- x_msg_count :
90 -- x_msg_data :
91 -- The above three parameters are standard output.
92 --
93 PROCEDURE Get_Owner_Pre(
94 p_api_version_number IN NUMBER,
95 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
96 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
97 p_commit IN VARCHAR2 := FND_API.G_FALSE,
98 p_resource_id_tbl IN AS_LEAD_ROUTING_WF.NUMBER_TABLE,
99 p_group_id_tbl IN AS_LEAD_ROUTING_WF.NUMBER_TABLE,
100 p_person_id_tbl IN AS_LEAD_ROUTING_WF.NUMBER_TABLE,
101 p_resource_flag_tbl IN AS_LEAD_ROUTING_WF.FLAG_TABLE,
102 p_sales_lead_rec IN AS_SALES_LEADS_PUB.SALES_LEAD_Rec_Type,
103 x_resource_id OUT NOCOPY NUMBER,
104 x_group_id OUT NOCOPY NUMBER,
105 x_person_id OUT NOCOPY NUMBER,
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_count OUT NOCOPY NUMBER,
108 x_msg_data OUT NOCOPY VARCHAR2
109 )
110 IS
111 CURSOR c_get_terr_resource1(c_sales_lead_id NUMBER, c_party_id NUMBER,
112 c_party_site_id NUMBER, c_lead VARCHAR2,
113 c_account VARCHAR2) IS
114 SELECT acc.salesforce_id, acc.sales_group_id
115 FROM as_accesses_all acc, as_territory_accesses terracc,
116 jtf_terr_rsc_all terrrsc
117 WHERE acc.sales_lead_id = c_sales_lead_id
118 AND acc.created_by_tap_flag = 'Y'
119 AND acc.access_id = terracc.access_id
120 AND terracc.territory_id = terrrsc.terr_id
121 AND terrrsc.resource_id = acc.salesforce_id
122 AND terrrsc.role = 'TELESALES_AGENT'
123 ORDER BY acc.access_id;
124
125 CURSOR c_get_terr_resource2(c_sales_lead_id NUMBER, c_party_id NUMBER,
126 c_party_site_id NUMBER, c_lead VARCHAR2,
127 c_account VARCHAR2) IS
128 SELECT acc.salesforce_id, acc.sales_group_id
129 FROM as_accesses_all acc
130 WHERE acc.sales_lead_id = c_sales_lead_id
131 AND acc.created_by_tap_flag = 'Y'
132 ORDER BY acc.access_id;
133
134 -- A resource may not be in any group. Besides, jtf_rs_group_members
135 -- may not have person_id for all resources. Therefore, get person_id
136 -- is this cursor.
137 CURSOR c_get_person_id(c_resource_id NUMBER) IS
138 SELECT res.source_id
139 FROM jtf_rs_resource_extns res
140 WHERE res.resource_id = c_resource_id;
141
142 CURSOR C_Get_Lead_Info(C_Sales_Lead_Id NUMBER) IS
143 SELECT SL.CUSTOMER_ID, SL.ADDRESS_ID
144 FROM AS_SALES_LEADS SL
145 WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
146
147 l_api_name CONSTANT VARCHAR2(30)
148 := 'Get_Owner_Pre';
149 l_api_version_number CONSTANT NUMBER := 2.0;
150 l_resource_id NUMBER := NULL;
151 l_group_id NUMBER;
152 l_person_id NUMBER;
153 l_customer_id NUMBER;
154 l_address_id NUMBER;
155 BEGIN
156 -- Standard Start of API savepoint
157 SAVEPOINT GET_OWNER_PRE_PVT;
158
159 -- Standard call to check for call compatibility.
160 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
161 p_api_version_number,
162 l_api_name,
163 G_PKG_NAME)
164 THEN
165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
166 END IF;
167
168 -- Initialize message list IF p_init_msg_list is set to TRUE.
169 IF FND_API.to_Boolean( p_init_msg_list )
170 THEN
171 FND_MSG_PUB.initialize;
172 END IF;
173
174 -- Debug Message
175 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
176 'PVT:' || l_api_name || ' Start');
177
178 -- Initialize API return status to SUCCESS
179 x_return_status := FND_API.G_RET_STS_SUCCESS;
180
181 --
182 -- Api body
183 --
184 -- ******************************************************************
185 -- Validate Environment
186 -- ******************************************************************
187
188 IF FND_GLOBAL.User_Id IS NULL
189 THEN
190 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
191 THEN
192 AS_UTILITY_PVT.Set_Message(
193 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
194 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
195 p_token1 => 'PROFILE',
196 p_token1_value => 'USER_ID');
197 END IF;
198 RAISE FND_API.G_EXC_ERROR;
199 END IF;
200
201 -- For Oracle internal use, p_resource_id_tbl, p_person_id_tbl,
202 -- p_group_id_tbl, p_resource_flag_tbl are all NULL. p_sales_lead_rec
203 -- has sales_lead_id populated only, other columns are all g_miss.
204
205 OPEN C_Get_Lead_Info(p_sales_lead_rec.sales_lead_id);
206 FETCH C_Get_Lead_Info INTO l_customer_id, l_address_id;
207 CLOSE C_Get_Lead_Info;
208
209 -- Get the first resource with TELESALES_AGENT role
210 OPEN c_get_terr_resource1(p_sales_lead_rec.sales_lead_id,
211 l_customer_id, l_address_id, 'LEAD',
212 'ACCOUNT');
213 FETCH c_get_terr_resource1 INTO l_resource_id, l_group_id;
214 CLOSE c_get_terr_resource1;
215
216 IF l_resource_id IS NULL
217 THEN
218
219 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
220 'There''s no Telesales Agent');
221 OPEN c_get_terr_resource2(p_sales_lead_rec.sales_lead_id,
222 l_customer_id, l_address_id, 'LEAD',
223 'ACCOUNT');
224 FETCH c_get_terr_resource2 INTO l_resource_id, l_group_id;
225 CLOSE c_get_terr_resource2;
226 END IF;
227
228 IF l_resource_id IS NOT NULL
229 THEN
230 OPEN c_get_person_id(l_resource_id);
231 FETCH c_get_person_id INTO l_person_id;
232 CLOSE c_get_person_id;
233
234 x_resource_id := l_resource_id;
235 x_group_id := l_group_id;
236 x_person_id := l_person_id;
237 ELSE
238 -- There's no resource found, return NULL.
239 -- Sales lead assignment API will pick owner from profile,
240 -- or current user.
241 x_resource_id := NULL;
242 END IF;
243
244 --
245 -- END of API body
246 --
247
248 -- Standard check for p_commit
249 IF FND_API.to_Boolean( p_commit )
250 THEN
251 COMMIT WORK;
252 END IF;
253
254 -- Debug Message
255 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
256 'PVT: ' || l_api_name || ' End');
257
258 -- Standard call to get message count and IF count is 1, get message info.
259 FND_MSG_PUB.Count_And_Get
260 ( p_count => x_msg_count,
261 p_data => x_msg_data );
262
263 EXCEPTION
264 WHEN FND_API.G_EXC_ERROR THEN
265 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
266 P_API_NAME => L_API_NAME
267 ,P_PKG_NAME => G_PKG_NAME
268 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
269 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
270 ,X_MSG_COUNT => X_MSG_COUNT
271 ,X_MSG_DATA => X_MSG_DATA
272 ,X_RETURN_STATUS => X_RETURN_STATUS);
273
274 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
275 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
276 P_API_NAME => L_API_NAME
277 ,P_PKG_NAME => G_PKG_NAME
278 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
279 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
280 ,X_MSG_COUNT => X_MSG_COUNT
281 ,X_MSG_DATA => X_MSG_DATA
282 ,X_RETURN_STATUS => X_RETURN_STATUS);
283
284 WHEN OTHERS THEN
285 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
286 P_API_NAME => L_API_NAME
287 ,P_PKG_NAME => G_PKG_NAME
288 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
289 ,P_SQLCODE => SQLCODE
290 ,P_SQLERRM => SQLERRM
291 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
292 ,X_MSG_COUNT => X_MSG_COUNT
293 ,X_MSG_DATA => X_MSG_DATA
294 ,X_RETURN_STATUS => X_RETURN_STATUS);
295 END Get_Owner_Pre;
296
297
298 END AS_LEAD_ROUTING_WF_CUHK;
299
[Home] [Help]