[Home] [Help]
PACKAGE BODY: APPS.AS_INTEREST_PUB
Source
1 PACKAGE BODY AS_INTEREST_PUB as
2 /* $Header: asxpintb.pls 120.2 2005/08/04 22:16:45 appldev ship $ */
3
4 --
5 -- NAME
6 -- AS_INTEREST_PUB
7 --
8 -- PURPOSE
9 -- Provide public interest record and table type to be used by APIs that
10 -- import interests/classifications into OSM
11 --
12 -- Convert the public interest records into private interest records for use by
13 -- the AS_INTEREST_PVT.Create_Interest routine
14 --
15 --
16 -- NOTES
17 -- The procedures in this package are not supported for use by anyone outside
18 -- of OSM. The procedures are called from the necessary API's to convert the
19 -- number into the table type excepted by the Private Interest API routine
20 -- (create_interest)
21 --
22 -- HISTORY
23 -- 11/12/96 JKORNBER Created
24 -- 08/28/98 AWU Add update_interest
25 -- Add interest_id, customer_id, address_id,
26 -- contact_id and lead_id into
27 -- interest record
28 -- Changed interest rec default value NULL to
29 -- FND_API.G_MISS for update purpose
30 --
31 --
32 G_PKG_NAME CONSTANT VARCHAR2(30):='AS_INTEREST_PUB';
33 G_FILE_NAME CONSTANT VARCHAR2(12):='asxpintb.pls';
34
35 --------------------------------Public Routines-------------------------------------
36
37 -- Start of Comments
38 --
39 -- NAME
40 -- create_interest
41 --
42 -- PURPOSE
43 -- Create an interest for an existing account/contact/lead.
44 --
45 -- NOTES
46 --
47 --
48 -- End of Comments
49
50 Procedure create_interest(p_api_version_number in number
51 ,p_init_msg_list in varchar2 := fnd_api.g_false
52 ,p_commit in varchar2 := fnd_api.g_false
53 ,p_validation_level IN NUMBER
54 := FND_API.G_VALID_LEVEL_FULL
55 ,p_interest_rec in interest_rec_type
56 ,p_customer_id in number
57 ,p_address_id in number
58 ,p_contact_id in number
59 ,p_lead_id in number
60 ,p_interest_use_code in varchar2
61 ,p_check_access_flag in varchar2
62 ,p_admin_flag in varchar2
63 ,p_admin_group_id in number
64 ,p_identity_salesforce_id in number
65 ,p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE
66 ,p_return_status OUT NOCOPY varchar2
67 ,p_msg_count OUT NOCOPY number
68 ,p_msg_data OUT NOCOPY varchar2
69 ,p_interest_out_id OUT NOCOPY number) is
70
71 l_api_name constant varchar2(30) := 'Create_Interest';
72 l_api_version_number constant number := 2.0;
73 l_interest_tbl interest_tbl_type;
74 l_pvt_classification_tbl as_interest_pvt.interest_tbl_type;
75 l_pvt_interest_out_tbl as_interest_pvt.interest_out_tbl_type;
76 l_return_status varchar2(1);
77 l_mode constant varchar2(30) := 'ON-INSERT';
78 l_module CONSTANT VARCHAR2(255) := 'as.plsql.intpb.create_interest';
79
80 begin
81
82 -- standard start of api savepoint
83 savepoint create_interest_pub;
84
85 -- standard call to check for call compatibility.
86 if not fnd_api.compatible_api_call ( l_api_version_number,
87 p_api_version_number,
88 l_api_name,
89 g_pkg_name)
90 then
91 raise fnd_api.g_exc_unexpected_error;
92 end if;
93
94
95 -- initialize message list if p_init_msg_list is set to true.
96 if fnd_api.to_boolean( p_init_msg_list )
97 then
98 fnd_msg_pub.initialize;
99 end if;
100
101 -- Debug Message
102 if fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
103 then
104 fnd_message.set_name('as', 'Public Create Interest: Start');
105 fnd_msg_pub.add;
106 end if;
107
108 -- Initialize API return status to success
109 p_return_status := FND_API.G_RET_STS_SUCCESS;
110
111 l_interest_tbl(1) := p_interest_rec;
112
113 -- Convert account classification values to ids
114 Convert_Values_To_Ids ( p_interest_tbl => l_interest_tbl,
115 p_pvt_interest_tbl => l_pvt_classification_tbl
116 );
117
118 as_interest_pvt.create_interest ( p_api_version_number => 2.0
119 ,p_init_msg_list => fnd_api.g_false
120 ,p_commit => fnd_api.g_false
121 ,p_validation_level => p_validation_level
122 ,p_interest_tbl => l_pvt_classification_tbl
123 ,p_customer_id => p_customer_id
124 ,p_address_id => p_address_id
125 ,p_contact_id => p_contact_id
126 ,p_lead_id => p_lead_id
127 ,p_interest_use_code => p_interest_use_code
128 ,p_check_access_flag => p_check_access_flag
129 ,p_admin_flag => p_admin_flag
130 ,p_admin_group_id => p_admin_group_id
131 ,p_identity_salesforce_id => p_identity_salesforce_id
132 ,p_access_profile_rec => p_access_profile_rec
133 ,p_return_status => l_return_status
134 ,p_msg_count => p_msg_count
135 ,p_msg_data => p_msg_data
136 ,p_interest_out_tbl => l_pvt_interest_out_tbl
137 );
138
139 p_return_status := l_return_status;
140 if l_return_status = fnd_api.g_ret_sts_error then
141 raise fnd_api.g_exc_error;
142 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
143 raise fnd_api.g_exc_unexpected_error;
144 end if;
145
146 p_interest_out_id := l_pvt_interest_out_tbl(1).interest_id;
147
148 --
149 -- End of API body.
150 --
151
152
153 AS_RTTAP_ACCOUNT.PROCESS_RTTAP_ACCOUNT
154 (p_interest_rec.customer_id,l_return_status);
155
156 if fnd_api.to_boolean ( p_commit )
157 then
158 commit work;
159 end if;
160
161 -- Debug Message
162 if fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
163 then
164 fnd_message.set_name('AS', 'Public Create Interest: End');
165 fnd_msg_pub.add;
166 end if;
167
168
169 -- Standard call to get message count and if count is 1, get message info.
170 fnd_msg_pub.count_and_get( p_count => p_msg_count
171 ,p_data => p_msg_data );
172
173 exception
174
175 WHEN FND_API.G_EXC_ERROR THEN
176
177 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
178 P_MODULE => l_module
179 ,P_API_NAME => L_API_NAME
180 ,P_PKG_NAME => G_PKG_NAME
181 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
182 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
183 ,X_MSG_COUNT => P_MSG_COUNT
184 ,X_MSG_DATA => P_MSG_DATA
185 ,X_RETURN_STATUS => P_RETURN_STATUS);
186
187 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
188
189 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
190 P_MODULE => l_module
191 ,P_API_NAME => L_API_NAME
192 ,P_PKG_NAME => G_PKG_NAME
193 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
194 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
195 ,X_MSG_COUNT => P_MSG_COUNT
196 ,X_MSG_DATA => P_MSG_DATA
197 ,X_RETURN_STATUS => P_RETURN_STATUS);
198
199 WHEN OTHERS THEN
200
201 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
202 P_MODULE => l_module
203 ,P_API_NAME => L_API_NAME
204 ,P_PKG_NAME => G_PKG_NAME
205 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
206 ,P_SQLCODE => SQLCODE
207 ,P_SQLERRM => SQLERRM
208 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
209 ,X_MSG_COUNT => P_MSG_COUNT
210 ,X_MSG_DATA => P_MSG_DATA
211 ,X_RETURN_STATUS => P_RETURN_STATUS);
212
213 end create_interest;
214
215 -- Start of Comments
216 --
217 -- API name : Update Interest
218 -- Type : Public
219 -- Function : Update Account, Contact, or Lead Classification Interest
220 -- Pre-reqs : Account, contact, or lead exists
221 -- Parameters
222 -- IN :
223 -- p_api_version_number IN NUMBER Required
224 -- p_init_msg_list IN VARCHAR2 Optional
225 -- Default = FND_API.G_FALSE
226 -- p_commit IN VARCHAR2 Optional
227 -- Default = FND_API.G_FALSE
228 -- p_validation_level IN NUMBER Optional
229 -- Default = FND_API.G_VALID_LEVEL_FULL
230 -- p_identity_salesforce_id IN NUMBER Optional
231 -- p_interest_rec IN INTEREST_REC_TYPE Required
232 -- p_interest_use_code IN VARCHAR2 Required
233 -- (LEAD_CLASSIFICATION, COMPANY_CLASSIFICATION,
234 -- CONTACT_INTEREST)
235 --
236 -- OUT :
237 -- x_return_status OUT VARCHAR2(1)
238 -- x_msg_count OUT NUMBER
239 -- x_msg_data OUT VARCHAR2(2000)
240 -- x_interest_id OUT NUMBER
241 --
242 --
243 -- Version : Current version 2.0
244 -- Initial Version
245 -- Initial version 2.0
246 --
247 -- Notes: For each interest, the interest type must be denoted properly
248 -- (i.e. for updating lead classifications, the interest
249 -- type must be denoted as a lead classification interest)
250 --
251 --
252 -- End of Comments
253
254 PROCEDURE Update_Interest
255 ( p_api_version_number IN NUMBER,
256 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
257 p_commit IN VARCHAR2 := FND_API.G_FALSE,
258 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
259 p_identity_salesforce_id IN NUMBER := NULL,
260 p_interest_rec IN INTEREST_REC_TYPE := G_MISS_INTEREST_REC,
261 p_interest_use_code IN VARCHAR2,
262 p_check_access_flag IN VARCHAR2,
263 p_admin_flag IN VARCHAR2,
264 p_admin_group_id IN NUMBER,
265 p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
266 x_return_status OUT NOCOPY VARCHAR2,
267 x_msg_count OUT NOCOPY NUMBER,
268 x_msg_data OUT NOCOPY VARCHAR2,
269 x_interest_id OUT NOCOPY NUMBER
270 ) is
271
272 l_api_name CONSTANT VARCHAR2(30) := 'Update_Interest';
273 l_api_version_number CONSTANT NUMBER := 2.0;
274 l_return_status VARCHAR2(1);
275 l_rowid ROWID;
276 l_interest_id NUMBER;
277 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
278 l_interest_rec as_interest_pvt.interest_rec_type;
279 l_interest_tbl as_interest_pub.interest_tbl_type;
280 l_pvt_interest_tbl as_interest_pvt.interest_tbl_type;
281 l_mode constant varchar2(30) := 'ON-UPDATE';
282 l_lead_id NUMBER;
283 l_address_id NUMBER;
284 l_module CONSTANT VARCHAR2(255) := 'as.plsql.intpb.Update_Interest';
285
286
287 BEGIN
288
289 -- Standard Start of API savepoint
290 SAVEPOINT UPDATE_INTEREST_PUB;
291
292 -- Standard call to check for call compatibility.
293 IF NOT FND_API.Compatible_API_Call( l_api_version_number,
294 p_api_version_number,
295 l_api_name,
296 G_PKG_NAME)
297 THEN
298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
299 END IF;
300
301
302 -- Initialize message list if p_init_msg_list is set to TRUE.
303 IF FND_API.to_Boolean( p_init_msg_list )
304 THEN
305 FND_MSG_PUB.initialize;
306 END IF;
307
308 -- Debug Message
309 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
310 THEN
311 FND_MESSAGE.Set_Name('AS', 'Pub Interest API: Start');
312 FND_MSG_PUB.Add;
313 END IF;
314
315 -- Initialize API return status to success
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317 l_return_status := FND_API.G_RET_STS_SUCCESS;
318
319 /*
320 Commented by gbatra as assumption is that ids are always passed
321 Convert_Interest_Values_To_Ids (p_interest_type => p_interest_rec.interest_type,
322 p_interest_type_id => p_interest_rec.interest_type_id,
323 p_primary_interest_code => p_interest_rec.primary_interest_code,
324 p_primary_interest_code_id => p_interest_rec.primary_interest_code_id,
325 p_secondary_interest_code => p_interest_rec.secondary_interest_code,
326 p_secondary_interest_code_id => p_interest_rec.secondary_interest_code_id,
327 p_description => p_interest_rec.description,
328 p_return_status => l_return_status,
329 p_out_interest_type_id => l_interest_rec.interest_type_id,
330 p_out_primary_interest_code_id => l_interest_rec.primary_interest_code_id,
331 p_out_second_interest_code_id => l_interest_rec.secondary_interest_code_id,
332 p_out_description => l_interest_rec.description
333 );
334 */
335 l_interest_rec.interest_type_id := p_interest_rec.interest_type_id;
336 l_interest_rec.primary_interest_code_id := p_interest_rec.primary_interest_code_id;
337 l_interest_rec.secondary_interest_code_id := p_interest_rec.secondary_interest_code_id;
338 l_interest_rec.description := p_interest_rec.description;
339 l_interest_rec.product_category_id := p_interest_rec.product_category_id;
340 l_interest_rec.product_cat_set_id := p_interest_rec.product_cat_set_id;
341 l_interest_rec.interest_id := p_interest_rec.interest_id;
342 l_interest_rec.customer_id := p_interest_rec.customer_id;
343 l_interest_rec.address_id := p_interest_rec.address_id;
344 l_interest_rec.contact_id := p_interest_rec.contact_id;
345 l_interest_rec.lead_id := p_interest_rec.lead_id;
346 l_interest_rec.last_update_date := p_interest_rec.last_update_date;
347 l_interest_rec.last_updated_by := p_interest_rec.last_updated_by;
348 l_interest_rec.creation_date := p_interest_rec.creation_date;
349 l_interest_rec.created_by := p_interest_rec.created_by;
350 l_interest_rec.last_update_login := p_interest_rec.last_update_login;
351 l_interest_rec.status_code := p_interest_rec.status_code;
352 l_interest_rec.status := p_interest_rec.status;
353 l_interest_rec.attribute_category := p_interest_rec.attribute_category ;
354 l_interest_rec.attribute1 := p_interest_rec.attribute1 ;
355 l_interest_rec.attribute2 := p_interest_rec.attribute2 ;
356 l_interest_rec.attribute3 := p_interest_rec.attribute3 ;
357 l_interest_rec.attribute4 := p_interest_rec.attribute4 ;
358 l_interest_rec.attribute5 := p_interest_rec.attribute5 ;
359 l_interest_rec.attribute6 := p_interest_rec.attribute6 ;
360 l_interest_rec.attribute7 := p_interest_rec.attribute7 ;
361 l_interest_rec.attribute8 := p_interest_rec.attribute8 ;
362 l_interest_rec.attribute9 := p_interest_rec.attribute9 ;
363 l_interest_rec.attribute10 := p_interest_rec.attribute10 ;
364 l_interest_rec.attribute11 := p_interest_rec.attribute11 ;
365 l_interest_rec.attribute12 := p_interest_rec.attribute12 ;
366 l_interest_rec.attribute13 := p_interest_rec.attribute13 ;
367 l_interest_rec.attribute14 := p_interest_rec.attribute14 ;
368 l_interest_rec.attribute15 := p_interest_rec.attribute15 ;
369
370 AS_INTEREST_PVT.Update_Interest ( p_api_version_number => 2.0,
371 p_init_msg_list => FND_API.G_FALSE,
372 p_commit => FND_API.G_FALSE,
373 p_validation_level => p_validation_level,
374 p_identity_salesforce_id=> p_identity_salesforce_id,
375 p_interest_rec => l_interest_rec,
376 p_interest_use_code => p_interest_use_code,
377 p_check_access_flag => p_check_access_flag,
378 p_admin_flag => p_admin_flag,
379 p_admin_group_id => p_admin_group_id,
380 p_access_profile_rec => p_access_profile_rec,
381 x_return_status => x_return_status,
382 x_msg_count => x_msg_count,
383 x_msg_data => x_msg_data,
384 x_interest_id => l_interest_id
385 );
386
387 x_interest_id := p_interest_rec.interest_id;
388 -- Success Message
389 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) and
390 x_return_status = FND_API.G_RET_STS_SUCCESS
391 THEN
392 FND_MESSAGE.Set_Name('AS', 'API_SUCCESS');
393 FND_MESSAGE.Set_Token('ROW', 'AS_INTEREST', TRUE);
394 FND_MSG_PUB.Add;
395 END IF;
396
397 l_address_id := p_interest_rec.address_id;
398 l_lead_id := p_interest_rec.lead_id;
399 IF l_lead_id = FND_API.G_MISS_NUM THEN
400 l_lead_id := NULL;
401 END IF;
402
403 AS_RTTAP_ACCOUNT.PROCESS_RTTAP_ACCOUNT
404 (p_interest_rec.customer_id,x_return_status);
405
406 -- Standard check of p_commit.
407 IF FND_API.To_Boolean ( p_commit )
408 THEN
409 COMMIT WORK;
410 END IF;
411
412 -- Debug Message
413 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
414 THEN
415 FND_MESSAGE.Set_Name('AS', 'Pub Interest API: End');
416 FND_MSG_PUB.Add;
417 END IF;
418
419 -- Standard call to get message count and if count is 1, get message info.
420 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
421 p_data => x_msg_data
422 );
423
424 EXCEPTION
425
426 WHEN FND_API.G_EXC_ERROR THEN
427 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
428 P_MODULE => l_module
429 ,P_API_NAME => L_API_NAME
430 ,P_PKG_NAME => G_PKG_NAME
431 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
432 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
433 ,X_MSG_COUNT => X_MSG_COUNT
434 ,X_MSG_DATA => X_MSG_DATA
435 ,X_RETURN_STATUS => X_RETURN_STATUS);
436
437 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
438 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
439 P_MODULE => l_module
440 ,P_API_NAME => L_API_NAME
441 ,P_PKG_NAME => G_PKG_NAME
442 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
443 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
444 ,X_MSG_COUNT => X_MSG_COUNT
445 ,X_MSG_DATA => X_MSG_DATA
446 ,X_RETURN_STATUS => X_RETURN_STATUS);
447
448 WHEN OTHERS THEN
449 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
450 P_MODULE => l_module
451 ,P_API_NAME => L_API_NAME
452 ,P_PKG_NAME => G_PKG_NAME
453 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
454 ,P_SQLCODE => SQLCODE
455 ,P_SQLERRM => SQLERRM
456 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
457 ,X_MSG_COUNT => X_MSG_COUNT
458 ,X_MSG_DATA => X_MSG_DATA
459 ,X_RETURN_STATUS => X_RETURN_STATUS);
460
461 END Update_Interest;
462
463
464 -- Purpose
465 --
466 -- This procedure takes as input a public interest table which may contain
467 -- both values and or ids. The values are then converted into ids and a
468 -- private interest table is returned for use in calling the Private
469 -- Create_Interest API
470 --
471 -- Notes
472 --
473 -- IDs take precedence over values, if both are present for a field
474 -- then the ID is used and a warning message is created
475 --
476 -- If one interest record fails, then we continue processing the rest of
477 -- the interest records and return an error message for the failure.
478 -- The failed record will not be returned in the pvt_interest_tbl since
479 -- it would fail on insert into the database.
480 --
481 --
482 PROCEDURE Convert_Values_To_Ids ( p_interest_tbl IN INTEREST_TBL_TYPE,
483 p_pvt_interest_tbl OUT NOCOPY AS_INTEREST_PVT.INTEREST_TBL_TYPE)
484 IS
485 l_interest_count CONSTANT NUMBER := p_interest_tbl.count;
486
487 l_any_errors BOOLEAN := FALSE;
488 l_any_row_errors BOOLEAN := FALSE;
489
490 l_val VARCHAR2(30);
491 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
492
493 l_pvt_interest_rec AS_INTEREST_PVT.INTEREST_REC_TYPE;
494
495 Cursor C_Get_Int_Status (X_Int_Status VARCHAR2, X_Product_Category_Id NUMBER, X_Product_Cat_Set_Id NUMBER) IS
496 SELECT ais.interest_status_code
497 FROM as_interest_statuses ais, as_lookups lkp
498 WHERE nls_upper(X_Int_Status) = nls_upper(lkp.meaning)
499 and product_category_id = X_Product_Category_Id
500 and product_cat_set_id = X_Product_Cat_Set_Id
501 and ais.interest_status_code = lkp.lookup_code
502 and lkp.lookup_type = 'INTEREST_STATUS';
503
504 BEGIN
505
506 -- Debug Message
507 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
508 FND_MESSAGE.Set_Name('AS', 'Int ValuesToIds: Start');
509 FND_MSG_PUB.Add;
510 END IF;
511
512 -- Loop through the interest table and convert values to ids
513 --
514 FOR l_curr_row in 1..l_interest_count
515 LOOP
516
517 ----------------- Start of Processing Interest Record -----------------------
518 BEGIN
519
520 -- Progress Message
521 --
522 /* IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
523 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
524 FND_MESSAGE.Set_Token ('ROW', 'AS_INTEREST', TRUE);
525 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
526 FND_MSG_PUB.Add;
527 END IF;
528 */
529
530 -- For each value - id pair, if id exists use it, otherwise convert values to ids
531 -- if necessary.
532 -- This is done for: Interest Type, Primary Interest, Secondary Interest, Status
533 --
534 /*
535 Commented by gbatra as assumption is that ids are always passed
536 Convert_Interest_Values_To_Ids (
537 p_interest_type => p_interest_tbl(l_curr_row).interest_type,
538 p_interest_type_id => p_interest_tbl(l_curr_row).interest_type_id,
539 p_primary_interest_code => p_interest_tbl(l_curr_row).primary_interest_code,
540 p_primary_interest_code_id => p_interest_tbl(l_curr_row).primary_interest_code_id,
541 p_secondary_interest_code => p_interest_tbl(l_curr_row).secondary_interest_code,
542 p_secondary_interest_code_id => p_interest_tbl(l_curr_row).secondary_interest_code_id,
543 p_description => p_interest_tbl(l_curr_row).description,
544 p_return_status => l_return_status,
545 p_out_interest_type_id => p_pvt_interest_tbl(l_curr_row).interest_type_id,
546 p_out_primary_interest_code_id => p_pvt_interest_tbl(l_curr_row).primary_interest_code_id,
547 p_out_second_interest_code_id => p_pvt_interest_tbl(l_curr_row).secondary_interest_code_id,
548 p_out_description => p_pvt_interest_tbl(l_curr_row).description
549 );
550
551 -- Process the return status from the procedure
552 --
553 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
554 THEN
555 l_any_row_errors := TRUE;
556 END IF;
557 */
558
559
560 -- Convert Interest Status
561 --
562 IF (p_interest_tbl(l_curr_row).status_code is not NULL
563 and p_interest_tbl(l_curr_row).status_code <> FND_API.G_MISS_CHAR)
564 THEN
565
566 p_pvt_interest_tbl(l_curr_row).status_code :=
567 p_interest_tbl(l_curr_row).status_code;
568
569 IF (p_interest_tbl(l_curr_row).status is not NULL
570 and p_interest_tbl(l_curr_row).status <> FND_API.G_MISS_CHAR)
571 THEN
572 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
573 THEN
574 FND_MESSAGE.Set_Name ('AS', 'API_ATTRIBUTE_IGNORED');
575 FND_MESSAGE.Set_Token ('COLUMN', 'STATUS', FALSE);
576 FND_MSG_PUB.Add;
577 END IF;
578 END IF;
579
580 ELSIF (p_interest_tbl(l_curr_row).status is not NULL
581 and p_interest_tbl(l_curr_row).status <> FND_API.G_MISS_CHAR)
582 THEN
583 OPEN C_Get_Int_Status (p_interest_tbl(l_curr_row).status,
584 p_pvt_interest_tbl(l_curr_row).product_category_id,
585 p_pvt_interest_tbl(l_curr_row).product_cat_set_id);
586 FETCH C_Get_Int_Status INTO l_val;
587 CLOSE C_Get_Int_Status;
588 p_pvt_interest_tbl(l_curr_row).status_code := l_val;
589
590 IF (l_val IS NULL)
591 THEN
592 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
593 THEN
594 FND_MESSAGE.Set_Name ('AS', 'API_ATTRIBUTE_CONVERSION_ERROR');
595 FND_MESSAGE.Set_Token ('COLUMN', 'STAUTS', FALSE);
596 FND_MESSAGE.Set_Token('VALUE', p_interest_tbl(l_curr_row).status, FALSE);
597 FND_MSG_PUB.Add;
598 END IF;
599 l_any_row_errors := TRUE;
600 END IF;
601 ELSE
602 p_pvt_interest_tbl(l_curr_row).status_code := NULL;
603 END IF;
604
605
606 -- Now copy the rest of the interest record columns from the public record
607 -- to the private record
608 p_pvt_interest_tbl(l_curr_row).interest_id := p_interest_tbl(l_curr_row).interest_id;
609 p_pvt_interest_tbl(l_curr_row).interest_type_id := p_interest_tbl(l_curr_row).interest_type_id;
610 p_pvt_interest_tbl(l_curr_row).primary_interest_code_id := p_interest_tbl(l_curr_row).primary_interest_code_id;
611 p_pvt_interest_tbl(l_curr_row).secondary_interest_code_id := p_interest_tbl(l_curr_row).secondary_interest_code_id;
612 p_pvt_interest_tbl(l_curr_row).description := p_interest_tbl(l_curr_row).description;
613 p_pvt_interest_tbl(l_curr_row).product_category_id := p_interest_tbl(l_curr_row).product_category_id;
614 p_pvt_interest_tbl(l_curr_row).product_cat_set_id := p_interest_tbl(l_curr_row).product_cat_set_id;
615 p_pvt_interest_tbl(l_curr_row).Attribute_Category := p_interest_tbl(l_curr_row).Attribute_Category;
616 p_pvt_interest_tbl(l_curr_row).Attribute1 := p_interest_tbl(l_curr_row).Attribute1;
617 p_pvt_interest_tbl(l_curr_row).Attribute2 := p_interest_tbl(l_curr_row).Attribute2;
618 p_pvt_interest_tbl(l_curr_row).Attribute3 := p_interest_tbl(l_curr_row).Attribute3;
619 p_pvt_interest_tbl(l_curr_row).Attribute4 := p_interest_tbl(l_curr_row).Attribute4;
620 p_pvt_interest_tbl(l_curr_row).Attribute5 := p_interest_tbl(l_curr_row).Attribute5;
621 p_pvt_interest_tbl(l_curr_row).Attribute6 := p_interest_tbl(l_curr_row).Attribute6;
622 p_pvt_interest_tbl(l_curr_row).Attribute7 := p_interest_tbl(l_curr_row).Attribute7;
623 p_pvt_interest_tbl(l_curr_row).Attribute8 := p_interest_tbl(l_curr_row).Attribute8;
624 p_pvt_interest_tbl(l_curr_row).Attribute9 := p_interest_tbl(l_curr_row).Attribute9;
625 p_pvt_interest_tbl(l_curr_row).Attribute10 := p_interest_tbl(l_curr_row).Attribute10;
626 p_pvt_interest_tbl(l_curr_row).Attribute11 := p_interest_tbl(l_curr_row).Attribute11;
627 p_pvt_interest_tbl(l_curr_row).Attribute12 := p_interest_tbl(l_curr_row).Attribute12;
628 p_pvt_interest_tbl(l_curr_row).Attribute13 := p_interest_tbl(l_curr_row).Attribute13;
629 p_pvt_interest_tbl(l_curr_row).Attribute14 := p_interest_tbl(l_curr_row).Attribute14;
630 p_pvt_interest_tbl(l_curr_row).Attribute15 := p_interest_tbl(l_curr_row).Attribute15;
631
632
633 -- If there was an error in processing the row, then raise an error
634 --
635 IF l_any_row_errors
636 THEN
637 raise FND_API.G_EXC_ERROR;
638 END IF;
639
640 EXCEPTION
641 WHEN OTHERS THEN
642 l_any_errors := TRUE;
643 l_any_row_errors := FALSE;
644 p_pvt_interest_tbl(l_curr_row) := l_pvt_interest_rec;
645
646 END;
647 ---------------- End of Processing Interest Record -----------------------
648
649 END LOOP;
650
651 -- Debug Message
652 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
653 THEN
654 FND_MESSAGE.Set_Name('AS', 'Int ValuesToIds: End');
655 FND_MSG_PUB.Add;
656 END IF;
657
658
659 IF l_any_errors
660 THEN
661 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
662 THEN
663 FND_MESSAGE.Set_Name('AS', 'API_ERRORS_IN_VALUES_TO_IDS');
664 FND_MESSAGE.Set_Token('ROW', 'AS_INTEREST', TRUE);
665 FND_MSG_PUB.Add;
666 END IF;
667 END IF;
668
669 END Convert_Values_To_Ids;
670
671
672 -- Purpose
673 -- Procedure converts interest type, primary, and secondar values to ids
674 --
675 -- Notes
676 -- This procedure is public so that it can be called by other API's.
677 -- Currently this procedure is used by the Create_Opportunity API to
678 -- convert the expected purchase values to ids and from the interest
679 -- Convert value to Ids routine found above
680 --
681 PROCEDURE Convert_Interest_Values_To_Ids (p_interest_type IN VARCHAR2,
682 p_interest_type_id IN NUMBER,
683 p_primary_interest_code IN VARCHAR2,
684 p_primary_interest_code_id IN NUMBER,
685 p_secondary_interest_code IN VARCHAR2,
686 p_secondary_interest_code_id IN NUMBER,
687 p_description IN VARCHAR2,
688 p_return_status OUT NOCOPY VARCHAR2,
689 p_out_interest_type_id OUT NOCOPY NUMBER,
690 p_out_primary_interest_code_id OUT NOCOPY NUMBER,
691 p_out_second_interest_code_id OUT NOCOPY NUMBER,
692 p_out_description OUT NOCOPY VARCHAR2
693 ) IS
694 Cursor C_Get_Int_Type (X_Int_Type VARCHAR2) IS
695 SELECT interest_type_id
696 FROM as_interest_types_vl
697 WHERE nls_upper(X_Int_Type) = nls_upper(interest_type)
698 and (interest_type like nls_upper(substr(X_Int_Type, 1, 1) || '%') or
699 interest_type like lower(substr(X_Int_Type, 1, 1) || '%'));
700
701 Cursor C_Get_Int_Code (X_Int_Code VARCHAR2, X_Int_Type_Id NUMBER) IS
702 SELECT interest_code_id
703 FROM as_interest_codes_v
704 WHERE nls_upper(X_Int_Code) = nls_upper(code)
705 and interest_type_id = X_Int_Type_Id;
706
707
708 CURSOR interest_code_desc( p_interest_code_id number
709 ,p_interest_type_id number) IS
710 SELECT description
711 FROM as_interest_codes_v
712 WHERE interest_code_id = p_interest_code_id
713 and interest_type_id = p_interest_type_id;
714
715 CURSOR interest_type_desc(p_interest_type_id number) IS
716 SELECT description
717 FROM as_interest_types_vl
718 WHERE interest_type_id = p_interest_type_id;
719
720 l_description varchar2(255);
721 l_interest_type_id NUMBER;
722 l_interest_code_id NUMBER;
723 l_secondary_interest_code_id NUMBER;
724 BEGIN
725
726 p_return_status := FND_API.G_RET_STS_SUCCESS;
727
728 -- Initialize Out Variables
729 p_out_interest_type_id := NULL;
730 p_out_primary_interest_code_id := NULL;
731 p_out_second_interest_code_id := NULL;
732 p_out_description := NULL;
733
734 -- Convert Interest Type
735 --
736 IF (p_interest_type_id is not NULL and
737 p_interest_type_id <> FND_API.G_MISS_NUM)
738 THEN
739 p_out_interest_type_id := p_interest_type_id;
740 l_interest_type_id := p_interest_type_id;
741
742 IF (p_interest_type is not NULL and
743 p_interest_type <> FND_API.G_MISS_CHAR)
744 THEN
745 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
746 THEN
747 FND_MESSAGE.Set_Name ('AS', 'API_ATTRIBUTE_IGNORED');
748 FND_MESSAGE.Set_Token ('COLUMN', 'INTEREST_TYPE', FALSE);
749 FND_MSG_PUB.Add;
750 END IF;
751 END IF;
752
753 ELSIF (p_interest_type is not NULL and
754 p_interest_type <> FND_API.G_MISS_CHAR)
755 THEN
756 OPEN C_Get_Int_Type ( p_interest_type );
757 FETCH C_Get_Int_Type INTO l_interest_type_id;
758
759 IF (C_Get_Int_Type%NOTFOUND) THEN
760 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
761 THEN
762 FND_MESSAGE.Set_Name ('AS', 'API_ATTRIBUTE_CONVERSION_ERROR');
763 FND_MESSAGE.Set_Token ('COLUMN', 'INTEREST_TYPE', FALSE);
764 FND_MESSAGE.Set_Token('VALUE', p_interest_type, FALSE);
765 FND_MSG_PUB.Add;
766 END IF;
767
768 -- This will raise an exception immediately, since all other processing
769 -- is dependent upon interest_type existing
770 --
771 raise FND_API.G_EXC_ERROR;
772
773 ELSE
774 p_out_interest_type_id := l_interest_type_id;
775 END IF;
776
777 CLOSE C_Get_Int_Type;
778 ELSE
779 -- If no interest type (value or id) exists, then this row is invalid
780 --
781 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
782 THEN
783 FND_MESSAGE.Set_Name ('AS','API_MISSING_ID');
784 FND_MESSAGE.Set_Token ('COLUMN', 'INTEREST_TYPE', FALSE);
785 FND_MSG_PUB.Add;
786 END IF;
787
788 -- This will raise an exception immediately, since all other processing
789 -- is dependent upon interest_type existing
790 --
791 raise FND_API.G_EXC_ERROR;
792 END IF;
793
794
795 -- Convert Primary Code
796 --
797 IF (p_primary_interest_code_id is not NULL and
798 p_primary_interest_code_id <> FND_API.G_MISS_NUM)
799 THEN
800 p_out_primary_interest_code_id := p_primary_interest_code_id;
801 l_interest_code_id := p_primary_interest_code_id;
802
803 IF (p_primary_interest_code is not NULL and
804 p_primary_interest_code <> FND_API.G_MISS_CHAR)
805 THEN
806 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
807 THEN
808 FND_MESSAGE.Set_Name ('AS','API_ATTRIBUTE_IGNORED');
809 FND_MESSAGE.Set_Token ('COLUMN', 'PRIMARY_INTEREST_CODE', FALSE);
810 FND_MSG_PUB.Add;
811 END IF;
812 END IF;
813
814 ELSIF (p_primary_interest_code is not NULL and
815 p_primary_interest_code <> FND_API.G_MISS_CHAR)
816 THEN
817 OPEN C_Get_Int_Code ( p_primary_interest_code, l_interest_type_id );
818 FETCH C_Get_Int_Code INTO l_interest_code_id;
819 IF (C_Get_Int_Code%NOTFOUND) THEN
820 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
821 FND_MESSAGE.Set_Name ('AS', 'API_ATTRIBUTE_CONVERSION_ERROR');
822 FND_MESSAGE.Set_Token ('COLUMN', 'PRIMARY_INTEREST_CODE', FALSE);
823 FND_MESSAGE.Set_Token('VALUE', p_primary_interest_code, FALSE);
824 FND_MSG_PUB.Add;
825 END IF;
826
827 p_return_status := FND_API.G_RET_STS_ERROR;
828
829 ELSE
830 p_out_primary_interest_code_id := l_interest_code_id;
831 END IF;
832 CLOSE C_Get_Int_Code;
833 END IF;
834
835
836 -- Convert Secondary Code
837 --
838 IF (p_secondary_interest_code_id is not NULL and
839 p_secondary_interest_code_id <> FND_API.G_MISS_NUM)
840 THEN
841 p_out_second_interest_code_id := p_secondary_interest_code_id;
842 l_secondary_interest_code_id := p_secondary_interest_code_id;
843
844 IF (p_secondary_interest_code is not NULL and
845 p_secondary_interest_code <> FND_API.G_MISS_CHAR)
846 THEN
847 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
848 THEN
849 FND_MESSAGE.Set_Name ('AS', 'API_ATTRIBUTE_IGNORED');
850 FND_MESSAGE.Set_Token ('COLUMN', 'SECONDARY_INTEREST_CODE', FALSE);
851 FND_MSG_PUB.Add;
852 END IF;
853 END IF;
854
855 ELSIF (p_secondary_interest_code is not NULL and
856 p_secondary_interest_code <> FND_API.G_MISS_CHAR)
857 THEN
858 OPEN C_Get_Int_Code ( p_secondary_interest_code, l_interest_type_id );
859 FETCH C_Get_Int_Code INTO l_secondary_interest_code_id;
860 IF(C_Get_Int_Code%NOTFOUND) THEN
861 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
862 FND_MESSAGE.Set_Name ('AS', 'API_ATTRIBUTE_CONVERSION_ERROR');
863 FND_MESSAGE.Set_Token ('COLUMN', 'SECONDARY_INTEREST_CODE', FALSE);
864 FND_MESSAGE.Set_Token('VALUE', p_secondary_interest_code, FALSE);
865 FND_MSG_PUB.Add;
866 END IF;
867 p_return_status := FND_API.G_RET_STS_ERROR;
868
869 ELSE
870 p_out_second_interest_code_id := l_secondary_interest_code_id;
871
872 END IF;
873 CLOSE C_Get_Int_Code;
874 END IF;
875
876 -- Calculate description field.
877 -- If not null use description, otherwise
878 -- If not null use secondary description, otherwise
879 -- If not null use primary description, otherwise
880 -- If not null use interest type description, otherwise
881 --
882 IF (p_Description is null and
883 p_Description = FND_API.G_MISS_CHAR)
884 THEN
885 IF l_Secondary_Interest_Code_Id is not null
886 THEN
887 open interest_code_desc(l_Secondary_Interest_Code_Id
888 ,l_Interest_Type_Id);
889 fetch interest_code_desc into p_out_description;
890
891 IF interest_code_desc%NOTFOUND THEN
892 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
893 THEN
894 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
895 FND_MESSAGE.Set_Token('COLUMN', 'SECONDARY_INTEREST_CODE_ID,INTEREST_TYPE_ID', FALSE);
896 FND_MESSAGE.Set_Token('VALUE', to_char(l_Secondary_Interest_Code_Id)
897 || ',' || to_char(l_Interest_Type_Id), FALSE);
898 FND_MSG_PUB.ADD;
899 END IF;
900 close interest_code_desc;
901 RAISE FND_API.G_EXC_ERROR;
902
903 ELSE
904 close interest_code_desc;
905 END IF;
906
907 ELSIF l_Interest_Code_Id is not null then
908 open interest_code_desc(l_Interest_Code_Id
909 ,l_Interest_Type_Id);
910 fetch interest_code_desc into p_out_description;
911
912 IF interest_code_desc%NOTFOUND THEN
913 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
914 THEN
915 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
916 FND_MESSAGE.Set_Token('COLUMN', 'PRIMARY_INTEREST_CODE_ID,INTEREST_TYPE_ID', FALSE);
917 FND_MESSAGE.Set_Token('VALUE', to_char(l_Interest_Code_Id)
918 || ',' || to_char(l_Interest_Type_Id), FALSE);
919 FND_MSG_PUB.ADD;
920 END IF;
921
922 close interest_code_desc;
923 RAISE FND_API.G_EXC_ERROR;
924
925 ELSE
926 close interest_code_desc;
927 END IF;
928
929 ELSE
930 open interest_type_desc(l_Interest_Type_Id);
931 fetch interest_type_desc into p_out_description;
932
933 IF interest_type_desc%NOTFOUND THEN
934 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
935 THEN
936 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
937 FND_MESSAGE.Set_Token('COLUMN', 'INTEREST_TYPE_ID', FALSE);
938 FND_MESSAGE.Set_Token('VALUE', l_Interest_Type_Id, FALSE);
939 FND_MSG_PUB.ADD;
940 END IF;
941 close interest_type_desc;
942 RAISE FND_API.G_EXC_ERROR;
943 ELSE
944 close interest_type_desc;
945 END IF;
946 END IF;
947
948 ELSE
949 p_out_description := p_Description;
950 END IF;
951
952
953 END Convert_Interest_Values_To_Ids;
954
955 PROCEDURE Delete_Interest
956 ( p_api_version_number IN NUMBER,
957 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
958 p_commit IN VARCHAR2 := FND_API.G_FALSE,
959 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
960 p_identity_salesforce_id IN NUMBER,
961 p_interest_rec IN INTEREST_REC_TYPE := G_MISS_INTEREST_REC,
962 p_interest_use_code IN VARCHAR2,
963 p_check_access_flag in varchar2,
964 p_admin_flag in varchar2,
965 p_admin_group_id in number,
966 p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
967 x_return_status OUT NOCOPY VARCHAR2,
968 x_msg_count OUT NOCOPY NUMBER,
969 x_msg_data OUT NOCOPY VARCHAR2
970 ) is
971 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Interest';
972 l_api_version_number CONSTANT NUMBER := 2.0;
973 l_interest_rec AS_INTEREST_PVT.Interest_Rec_Type;
974 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
975 l_module CONSTANT VARCHAR2(255) := 'as.plsql.intpb.Delete_Interest';
976 begin
977 SAVEPOINT DELETE_INTEREST_PUB;
978 -- Standard call to check for call compatibility.
979 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
980 p_api_version_number,
981 l_api_name,
982 G_PKG_NAME)
983 THEN
984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
985 END IF;
986
987 -- Initialize message list if p_init_msg_list is set to TRUE.
988 IF FND_API.to_Boolean( p_init_msg_list )
989 THEN
990 FND_MSG_PUB.initialize;
991 END IF;
992
993 -- Debug Message
994 IF l_debug THEN
995 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
996 'Public API: ' || l_api_name || 'start');
997
998 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
999 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1000 END IF;
1001
1002 -- Initialize API return status to SUCCESS
1003 x_return_status := FND_API.G_RET_STS_SUCCESS;
1004
1005 --
1006 -- API body
1007 --
1008 IF l_debug THEN
1009 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1010 'Public API: Calling AS_ACCESS_PVT.delete_interest');
1011 END IF;
1012
1013 l_interest_rec.interest_id := p_interest_rec.interest_id;
1014 l_interest_rec.customer_id := p_interest_rec.customer_id;
1015 l_interest_rec.address_id := p_interest_rec.address_id;
1016 l_interest_rec.lead_id := p_interest_rec.lead_id;
1017
1018 as_interest_pvt.Delete_Interest ( p_api_version_number => 2.0,
1019 p_init_msg_list => FND_API.G_FALSE,
1020 p_commit => FND_API.G_FALSE,
1021 p_validation_level => p_validation_level,
1022 p_identity_salesforce_id=> p_identity_salesforce_id,
1023 p_interest_rec => l_interest_rec,
1024 p_interest_use_code => p_interest_use_code,
1025 p_check_access_flag => p_check_access_flag,
1026 p_admin_flag => p_admin_flag,
1027 p_admin_group_id => p_admin_group_id,
1028 p_access_profile_rec => p_access_profile_rec,
1029 x_return_status => x_return_status,
1030 x_msg_count => x_msg_count,
1031 x_msg_data => x_msg_data
1032 );
1033
1034
1035 IF x_return_status = FND_API.G_RET_STS_ERROR then
1036 raise FND_API.G_EXC_ERROR;
1037 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1038 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1039 END IF;
1040
1041
1042 --
1043 -- End of API body.
1044 --
1045
1046 IF FND_API.to_Boolean( p_commit )
1047 THEN
1048 COMMIT WORK;
1049 END IF;
1050
1051
1052 -- Debug Message
1053 IF l_debug THEN
1054 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1055 'Public API: ' || l_api_name || 'end');
1056
1057 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
1058 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1059 END IF;
1060
1061
1062 FND_MSG_PUB.Count_And_Get
1063 ( p_count => x_msg_count,
1064 p_data => x_msg_data
1065 );
1066
1067 EXCEPTION
1068 WHEN FND_API.G_EXC_ERROR THEN
1069 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1070 P_MODULE => l_module
1071 ,P_API_NAME => L_API_NAME
1072 ,P_PKG_NAME => G_PKG_NAME
1073 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1074 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
1075 ,X_MSG_COUNT => X_MSG_COUNT
1076 ,X_MSG_DATA => X_MSG_DATA
1077 ,X_RETURN_STATUS => X_RETURN_STATUS);
1078
1079 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1080 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1081 P_MODULE => l_module
1082 ,P_API_NAME => L_API_NAME
1083 ,P_PKG_NAME => G_PKG_NAME
1084 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1085 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
1086 ,X_MSG_COUNT => X_MSG_COUNT
1087 ,X_MSG_DATA => X_MSG_DATA
1088 ,X_RETURN_STATUS => X_RETURN_STATUS);
1089
1090 WHEN OTHERS THEN
1091 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1092 P_MODULE => l_module
1093 ,P_API_NAME => L_API_NAME
1094 ,P_PKG_NAME => G_PKG_NAME
1095 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1096 ,P_SQLCODE => SQLCODE
1097 ,P_SQLERRM => SQLERRM
1098 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
1099 ,X_MSG_COUNT => X_MSG_COUNT
1100 ,X_MSG_DATA => X_MSG_DATA
1101 ,X_RETURN_STATUS => X_RETURN_STATUS);
1102
1103 End delete_interest;
1104
1105
1106
1107 END AS_INTEREST_PUB;