[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_MEMBER_PUB
Source
1 PACKAGE BODY AS_SALES_MEMBER_PUB as
2 /* $Header: asxpsmbb.pls 120.2 2005/06/14 01:31:52 appldev $ */
3
4 --
5 -- NAME
6 -- AS_SALES_MEMBER_PUB
7 --
8 -- HISTORY
9 -- 6/19/98 ALHUNG CREATED
10 --
11 --
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):='AS_SALES_MEMBER_PUB';
14 G_FILE_NAME CONSTANT VARCHAR2(12):='asxpsmbb.pls';
15
16
17 /*************************** PUBLIC ROUTINES *********************************/
18 --
19 -- NAME
20 -- Convert_Partner_to_ID
21 --
22 -- PURPOSE
23 --
24 --
25 -- NOTES
26 --
27 --
28 -- HISTORY
29 --
30 --
31 PROCEDURE Convert_Partner_to_ID
32 ( p_api_version_number IN NUMBER
33 ,p_init_msg_list IN VARCHAR2
34 := FND_API.G_FALSE
35 ,p_partner_customer_id IN Number
36 ,p_partner_address_id IN Number
37 ,x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2
38 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
39 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
40 ,x_sales_member_rec OUT NOCOPY /* file.sql.39 change */ Sales_Member_Rec_Type) IS
41
42 Cursor Get_Ptr_Salesforce_id(p_partner_customer_id IN Number,
43 p_partner_address_id IN Number ) IS
44 Select force.salesforce_id
45 ,force.type
46 ,force.start_date_active
47 ,force.end_date_active
48 ,force.Employee_Person_id
49 --,force.Sales_Group_id
50 ,force.Partner_address_id
51 ,force.Partner_customer_id
52 --,force.Partner_contact_id
53 from AS_SALESFORCE_V force
54 where partner_customer_id = p_partner_customer_id AND
55 partner_address_id = p_partner_address_id;
56
57 l_api_name CONSTANT VARCHAR2(30) := 'Convert_Partner_to_ID';
58 l_api_version_number CONSTANT NUMBER := 2.0;
59 l_module CONSTANT VARCHAR2(255) := 'as.plsql.smbp.Convert_Partner_to_ID';
60
61 Begin
62
63 -- Standard call to check for call compatibility.
64 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
65 p_api_version_number,
66 l_api_name,
67 G_PKG_NAME)
68 THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END IF;
71
72
73 -- Initialize message list if p_init_msg_list is set to TRUE.
74 IF FND_API.to_Boolean( p_init_msg_list ) THEN
75 FND_MSG_PUB.initialize;
76 END IF;
77
78 -- Debug Message
79 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
80 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_MEMBER_PUB.Convert_Partner_To_Id Begin');
81 END IF;
82
83 Open Get_Ptr_Salesforce_id(p_partner_customer_id, p_partner_address_id);
84 IF (Get_Ptr_Salesforce_id%ROWCOUNT > 1) Then
85
86 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
87 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_MEMBER_PUB - Found duplicated partner');
88 END IF;
89 End if;
90
91 Fetch Get_Ptr_Salesforce_id INTO x_sales_member_rec.salesforce_id
92 ,x_sales_member_rec.type
93 ,x_sales_member_rec.start_date_active
94 ,x_sales_member_rec.end_date_active
95 ,x_sales_member_rec.employee_person_id
96 --,x_sales_member_rec.sales_group_id
97 ,x_sales_member_rec.partner_address_id
98 ,x_sales_member_rec.partner_customer_id;
99 --,x_sales_member_rec.partner_contact_id ;
100 Close Get_Ptr_Salesforce_id;
101
102 x_return_status := FND_API.G_RET_STS_SUCCESS;
103
104
105 -- Standard call to get message count and if count is 1, get message info.
106 FND_MSG_PUB.Count_And_Get
107 ( p_count => x_msg_count,
108 p_data => x_msg_data );
109
110 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
111 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_MEMBER_PUB.Convert_Partner_To_Id End');
112 END IF;
113
114
115 EXCEPTION
116
117 WHEN NO_DATA_FOUND THEN
118
119 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
120 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_MEMBER_PUB - Cannot Find Sales Partner');
121 END IF;
122
123 x_return_status := FND_API.G_RET_STS_ERROR ;
124 x_sales_member_rec := G_MISS_SALES_MEMBER_REC;
125
126 WHEN FND_API.G_EXC_ERROR THEN
127
128 x_return_status := FND_API.G_RET_STS_ERROR ;
129
130 FND_MSG_PUB.Count_And_Get
131 ( p_count => x_msg_count,
132 p_data => x_msg_data
133 );
134
135
136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137
138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
139
140 FND_MSG_PUB.Count_And_Get
141 ( p_count => x_msg_count,
142 p_data => x_msg_data
143 );
144
145
146 WHEN OTHERS THEN
147
148
149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
150
151 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
152 THEN
153 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
154 END IF;
155
156 FND_MSG_PUB.Count_And_Get
157 ( p_count => x_msg_count,
158 p_data => x_msg_data
159 );
160
161 End Convert_Partner_to_ID;
162
163 --
164 -- NAME
165 -- Convert_SFID_to_Values
166 --
167 -- PURPOSE
168 --
169 --
170 -- NOTES
171 --
172 --
173 -- HISTORY
174 --
175 --
176 PROCEDURE Convert_SFID_to_Values
177 ( p_api_version_number IN NUMBER,
178 p_init_msg_list IN VARCHAR2
179 := FND_API.G_FALSE,
180 p_salesforce_id IN NUMBER,
181
182 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
183 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
184 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
185 x_sales_member_rec OUT NOCOPY /* file.sql.39 change */ Sales_Member_Rec_Type ) IS
186
187
188 Cursor C_GetSalesMember(p_salesforce_id Number) IS
189 Select salesforce_id, type, start_date_active, end_date_active, employee_person_id,
190 -- sales_group_id,
191 partner_customer_id, partner_address_id
192 --partner_contact_id
193 From AS_SALESFORCE_V
194 Where salesforce_id = p_salesforce_id;
195
196 Cursor C_GetPersonInfo(p_person_id Number) IS
197 Select last_name, first_name, full_name, email_address
198 From per_people_f
199 Where person_id = p_person_id;
200
201 l_api_name CONSTANT VARCHAR2(30) := 'Convert_SFID_to_Values';
202 l_api_version_number CONSTANT NUMBER := 2.0;
203 l_module CONSTANT VARCHAR2(255) := 'as.plsql.smbp.Convert_SFID_to_Values';
204
205 BEGIN
206
207 -- Standard call to check for call compatibility.
208 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
209 p_api_version_number,
210 l_api_name,
211 G_PKG_NAME)
212 THEN
213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
214 END IF;
215
216
217 -- Initialize message list if p_init_msg_list is set to TRUE.
218 IF FND_API.to_Boolean( p_init_msg_list ) THEN
219 FND_MSG_PUB.initialize;
220 END IF;
221
222 -- Debug Message
223 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
224 THEN
225 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_MEMBER_PUB.Convert_SFID_to_Values - BEGIN');
226 END IF;
227
228 -- Initialize API return status to success
229 x_return_status := FND_API.G_RET_STS_SUCCESS;
230
231 --
232 -- API body
233 --
234
235
236 -- ******************************************************************
237 -- Validate Environment
238 -- ******************************************************************
239
240 -- This simple conversion rountine does not check environment
241
242
243
244 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
245 THEN
246 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_MEMBER_PUB - Searching Sales Member');
247 END IF;
248
249
250
251 -- Invoke cursor to find sales member with salesforce_id
252 Begin
253 Open C_GetSalesMember(p_salesforce_id);
254 Fetch C_GetSalesMember into
255 x_sales_member_rec.salesforce_id,
256 x_sales_member_rec.type,
257 x_sales_member_rec.start_date_active,
258 x_sales_member_rec.end_date_active,
259 x_sales_member_rec.employee_person_id,
260 --x_sales_member_rec.sales_group_id,
261 x_sales_member_rec.partner_customer_id,
262 x_sales_member_rec.partner_address_id;
263 --x_sales_member_rec.partner_contact_id;
264 Close C_GetSalesMember;
265
266
267 -- If salesmember is a person, lookup person info.
268 If (x_sales_member_rec.type = 'EMPLOYEE') Then
269 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
270 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_MEMBER_PUB - Searching Sales Person Info');
271 END IF;
272
273 Open C_GetPersonInfo(x_sales_member_rec.employee_person_id);
274 Fetch C_GetPersonInfo into
275 x_sales_member_rec.last_name,
276 x_sales_member_rec.first_name,
277 x_sales_member_rec.full_name,
278 x_sales_member_rec.email_address;
279 Close C_GetPersonInfo;
280
281 -- Else if salesmember is a sales partner, call Accounts API's for info
282 Elsif (x_sales_member_rec.type = 'PARTNER') Then
283 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
284 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_MEMBER_PUB - Searching Sales Partner Info');
285 END IF;
286 -- CODE TO BE INSERTED
287 -- Need to invoke Accounts API here for the info
288 End if;
289
290
291 EXCEPTION
292
293 WHEN NO_DATA_FOUND THEN
294
295 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
296 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_MEMBER_PUB Cannot find Salesmember Info');
297 END IF;
298
299 x_return_status := FND_API.G_RET_STS_ERROR ;
300
301 FND_MSG_PUB.Count_And_Get
302 ( p_count => x_msg_count,
303 p_data => x_msg_data
304 );
305 return;
306
307 End;
308
309 -- End of API body.
310 --
311
312 x_return_status := FND_API.G_RET_STS_SUCCESS;
313
314 -- Success Message
315 -- MMSG
316 /*
317 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
318 THEN
319 FND_MESSAGE.Set_Name('AS', 'API_SUCCESS');
320 FND_MESSAGE.Set_Token('ROW', 'AS_OPPORTUNITY', TRUE);
321 FND_MSG_PUB.Add;
322 END IF;
323 */
324
325 -- Debug Message
326 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
327 THEN
328 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_MEMBER_PUB.Convert_SFID_to_Values End');
329 --FND_MESSAGE.Set_Name('AS_SALES_MEMBER_PUB.Convert_SFID_to_Values End');
330 --FND_MSG_PUB.Add;
331 END IF;
332
333 -- Standard call to get message count and if count is 1, get message info.
334 FND_MSG_PUB.Count_And_Get
335 ( p_count => x_msg_count,
336 p_data => x_msg_data
337 );
338
339
340 EXCEPTION
341
342 WHEN FND_API.G_EXC_ERROR THEN
343
344 x_return_status := FND_API.G_RET_STS_ERROR ;
345
346 FND_MSG_PUB.Count_And_Get
347 ( p_count => x_msg_count,
348 p_data => x_msg_data
349 );
350
351
352 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
353
354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
355
356 FND_MSG_PUB.Count_And_Get
357 ( p_count => x_msg_count,
358 p_data => x_msg_data
359 );
360
361
362 WHEN OTHERS THEN
363
364
365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
366
367 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
368 THEN
369 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
370 END IF;
371
372 FND_MSG_PUB.Count_And_Get
373 ( p_count => x_msg_count,
374 p_data => x_msg_data
375 );
376
377 END Convert_SFID_to_Values;
378
379
380 END AS_SALES_MEMBER_PUB;