1 PACKAGE BODY as_interest_types_pub as
2 /* $Header: asxintyb.pls 120.1 2005/06/14 01:30:21 appldev $ */
3 --
4 --***************************************************************************
5 -- Package Name : AS_INTEREST_TYPES_PUB
6 --
7 -- Purpose :
8 -- Public API to Create and Update Interest Types in the Oracle Sales
9 -- Online.
10 --
11 -- History:
12 --
13 -- 09/12/2002 Rajan T Created
14 --***************************************************************************
15 -- Delcare Global Variables
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'as_interest_types_pub';
18
19 -- Start of Comments
20 --
21 -- API Name : create_interest_type
22 -- Type : Public
23 -- Function : To create the Interest Types using the table handler
24 -- Pre-Reqs : Table Handler as_interest_types_pkg.insert_row should exist
25 -- Parameters :
26 -- IN : p_api_version_number IN NUMBER
27 -- p_init_msg_list IN VARCHAR2
28 -- p_commit IN VARCHAR2
29 -- p_validation_level IN NUMBER
30 -- p_interest_type_rec IN interest_code_rec_type
31 -- OUT : x_return_status OUT VARCHAR2
32 -- x_msg_count OUT NUMBER
33 -- x_msg_data OUT VARCHAR2
34 -- Version : 2.0
35 -- Purpose : Public API to Create Interest Types in the Oracle Sales Online.
36 -- Notes : This procedure is a public procedure called using the
37 -- public API as_interest_type_pub to create interest types.
38 --
39 -- End of Comments
40
41
42 PROCEDURE create_interest_type(
43 p_api_version_number IN NUMBER,
44 p_init_msg_list IN VARCHAR2 ,
45 p_commit IN VARCHAR2 ,
46 p_validation_level IN NUMBER ,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_count OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2,
50 p_interest_type_rec IN interest_type_rec_type ,
51 x_interest_type_id OUT NOCOPY NUMBER
52 ) IS
53 -- Declare Local variables and cursors
54 l_api_version NUMBER := p_api_version_number;
55 l_count NUMBER := 0;
56 l_api_name CONSTANT VARCHAR2(30) := 'create_interest_type';
57 l_row_id VARCHAR2(100) := '';
58 l_creation_date DATE := p_interest_type_rec.creation_date;
59 l_created_by NUMBER := p_interest_type_rec.created_by;
60 l_last_update_date DATE;
61 l_last_update_login NUMBER;
62 l_last_updated_by NUMBER;
63 l_description VARCHAR2(240);
64 l_master_enabled_flag VARCHAR2(1);
65 l_enabled_flag VARCHAR2(1);
66 l_company_classification_flag VARCHAR2(1);
67 l_contact_interest_flag VARCHAR2(1);
68 l_lead_classification_flag VARCHAR2(1);
69 l_expected_purchase_flag VARCHAR2(1);
70 l_current_environment_flag VARCHAR2(1);
71 l_org_id NUMBER;
72 l_interest_type VARCHAR2(80);
73 l_prod_cat_set_id NUMBER;
74 l_prod_cat_id NUMBER;
75 l_prod_cat_not_found VARCHAR2(1) := 'N';
76
77 CURSOR interest_dup_cur(p_interest_type IN VARCHAR2) IS
78 SELECT 1
79 FROM as_interest_types_vl
80 WHERE TRIM(NLS_UPPER(interest_type)) = p_interest_type; -- passing in trimmed value while opening.
81
82 CURSOR interest_id_cur IS
83 SELECT as_interest_types_s.NEXTVAL
84 FROM DUAL;
85
86 CURSOR prod_category_val_cur(p_prod_cat_set_id IN NUMBER, p_prod_cat_id IN NUMBER) IS
87 SELECT 1 FROM ENI_PROD_DEN_HRCHY_PARENTS_V P
88 WHERE P.CATEGORY_ID = p_prod_cat_id
89 AND P.CATEGORY_SET_ID = p_prod_cat_set_id;
90 l_module CONSTANT VARCHAR2(255) := 'as.plsql.intypub.create_interest_type';
91
92 BEGIN
93 -- Standard Start of API SavePoint
94 SAVEPOINT create_interest_type_PUB;
95
96 -- Initialize message list if p_init_msg_list is set to TRUE.
97 IF FND_API.to_Boolean( p_init_msg_list ) THEN
98 FND_MSG_PUB.initialize;
99 END IF;
100
101
102 -- Standard call to check for call compatibility.
103 IF NOT FND_API.Compatible_API_Call (l_api_version,
104 p_api_version_number,
105 l_api_name,
106 G_PKG_NAME )
107 THEN
108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
109 END IF;
110
111
112 -- Initialize API return status to success
113 x_return_status := FND_API.G_RET_STS_SUCCESS;
114
115 -- API Body --
116
117 -- Initialize the flags
118 IF ( p_interest_type_rec.master_enabled_flag = FND_API.G_MISS_CHAR OR
119 TRIM(p_interest_type_rec.master_enabled_flag) IS NULL) THEN
120 l_master_enabled_flag := 'N';
121 ELSE
122 l_master_enabled_flag := p_interest_type_rec.master_enabled_flag;
123 END IF;
124
125 IF ( p_interest_type_rec.company_classification_flag = FND_API.G_MISS_CHAR
126 OR TRIM(p_interest_type_rec.company_classification_flag ) IS NULL ) THEN
127 l_company_classification_flag := 'N';
128 ELSE
129 l_company_classification_flag := p_interest_type_rec.company_classification_flag;
130 END IF;
131
132 IF ( p_interest_type_rec.contact_interest_flag = FND_API.G_MISS_CHAR
133 OR TRIM(p_interest_type_rec.contact_interest_flag ) IS NULL ) THEN
134 l_contact_interest_flag := 'N';
135 ELSE
136 l_contact_interest_flag := p_interest_type_rec.contact_interest_flag;
137 END IF;
138
139 IF ( p_interest_type_rec.lead_classification_flag = FND_API.G_MISS_CHAR
140 OR TRIM(p_interest_type_rec.lead_classification_flag ) IS NULL ) THEN
141 l_lead_classification_flag := 'N';
142 ELSE
143 l_lead_classification_flag := p_interest_type_rec.lead_classification_flag;
144 END IF;
145
146 IF ( p_interest_type_rec.expected_purchase_flag = FND_API.G_MISS_CHAR
147 OR TRIM(p_interest_type_rec.expected_purchase_flag ) IS NULL) THEN
148 l_expected_purchase_flag := 'N';
149 ELSE
150 l_expected_purchase_flag := p_interest_type_rec.expected_purchase_flag;
151 END IF;
152
153 IF ( p_interest_type_rec.current_environment_flag = FND_API.G_MISS_CHAR
154 OR TRIM(p_interest_type_rec.current_environment_flag ) IS NULL ) THEN
155 l_current_environment_flag := 'N';
156 ELSE
157 l_current_environment_flag := p_interest_type_rec.current_environment_flag;
158 END IF;
159
160 IF ( p_interest_type_rec.enabled_flag = FND_API.G_MISS_CHAR
161 OR TRIM(p_interest_type_rec.enabled_flag ) IS NULL ) THEN
162 l_enabled_flag := 'N';
163 ELSE
164 l_enabled_flag := p_interest_type_rec.enabled_flag;
165 END IF;
166
167 -- Initialize the creation date and created by
168 -- if the User has not entered them.
169
170 IF (p_interest_type_rec.creation_date= FND_API.G_MISS_DATE)
171 OR TRIM(p_interest_type_rec.creation_date) IS NULL THEN
172 l_creation_date := SYSDATE;
173 END IF;
174
175 IF (p_interest_type_rec.created_by = FND_API.G_MISS_NUM)
176 OR TRIM(p_interest_type_rec.created_by ) IS NULL THEN
177 l_created_by := FND_GLOBAL.user_id;
178 ELSE
179 l_created_by := p_interest_type_rec.created_by;
180 END IF;
181
182 IF (p_interest_type_rec.last_update_date = FND_API.G_MISS_DATE)
183 OR TRIM(p_interest_type_rec.last_update_date) IS NULL THEN
184 l_last_update_date := sysdate;
185 ELSE
186 l_last_update_date := p_interest_type_rec.last_update_date;
187 END IF;
188
189 IF (p_interest_type_rec.last_updated_by = FND_API.G_MISS_NUM)
190 OR TRIM(p_interest_type_rec.last_updated_by ) IS NULL THEN
191 l_last_updated_by := FND_GLOBAL.user_id;
192 ELSE
193 l_last_updated_by := p_interest_type_rec.last_updated_by;
194 END IF;
195
196 IF (p_interest_type_rec.last_update_login = FND_API.G_MISS_NUM)
197 OR TRIM(p_interest_type_rec.last_update_login ) IS NULL THEN
198 l_last_update_login := FND_GLOBAL.login_id;
199 ELSE
200 l_last_update_login := p_interest_type_rec.last_update_login;
201 END IF;
202
203 -- If g_miss is passed then replace with null before insert
204 -- otherwise use the value passed for the insert.
205 -- This is done only for optional fields, for required fields
206 -- the DB will throw error.
207 IF p_interest_type_rec.description = FND_API.G_MISS_CHAR
208 OR TRIM(p_interest_type_rec.description ) IS NULL
209 THEN
210 l_description := NULL;
211 ELSE
212 l_description := p_interest_type_rec.description;
213 END IF;
214
215 IF (p_interest_type_rec.prod_cat_set_id = FND_API.G_MISS_NUM) THEN
216 l_prod_cat_set_id := NULL;
217 ELSE
218 l_prod_cat_set_id := p_interest_type_rec.prod_cat_set_id;
219 END IF;
220
221 IF (p_interest_type_rec.prod_cat_id = FND_API.G_MISS_NUM) THEN
222 l_prod_cat_id := NULL;
223 ELSE
224 l_prod_cat_id := p_interest_type_rec.prod_cat_id;
225 END IF;
226
227 -- Assign org_id
228 l_org_id := p_interest_type_rec.org_id ;
229
230 -- Assign interest_type
231 l_interest_type := p_interest_type_rec.interest_type;
232
233
234 -- Check if Required Values have been passed
235 IF ( l_master_enabled_flag NOT IN ('N','Y') ) THEN
236 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_MAST_ENAB_FLAG');
237 FND_MESSAGE.SET_TOKEN('MASTER_ENABLED_FLAG',l_master_enabled_flag);
238 FND_MSG_PUB.Add;
239 END IF;
240
241 IF ( l_interest_type = FND_API.G_MISS_CHAR
242 OR TRIM(l_interest_type) IS NULL) THEN
243 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_INT_TYPE');
244 FND_MESSAGE.SET_TOKEN('INTEREST_TYPE',l_interest_type);
245 FND_MSG_PUB.Add;
246 END IF;
247
248 IF ( l_company_classification_flag NOT IN ('N','Y') ) THEN
249 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_COMP_CLAS_FLAG');
250 FND_MESSAGE.SET_TOKEN('COMPANY_CLASSIFICATION_FLAG',l_company_classification_flag);
251 FND_MSG_PUB.Add;
252 END IF;
253
254 IF ( l_contact_interest_flag NOT IN ('N','Y') ) THEN
255 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_CONT_INT_FLAG');
256 FND_MESSAGE.SET_TOKEN('CONTACT_INTEREST_FLAG',l_contact_interest_flag);
257 FND_MSG_PUB.Add;
258 END IF;
259
260 IF ( l_lead_classification_flag NOT IN ('N','Y') ) THEN
261 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_LEAD_CLAS_FLAG');
262 FND_MESSAGE.SET_TOKEN('LEAD_CLASSIFICATION_FLAG',l_lead_classification_flag);
263 FND_MSG_PUB.Add;
264 END IF;
265
266 IF ( l_expected_purchase_flag NOT IN ('N','Y') ) THEN
267 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_EXP_PURC_FLAG');
268 FND_MESSAGE.SET_TOKEN('EXPECTED_PURCHASE_FLAG',l_expected_purchase_flag);
269 FND_MSG_PUB.Add;
270 END IF;
271
272 IF ( l_current_environment_flag NOT IN ('N','Y') ) THEN
273 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_CURR_ENVT_FLAG');
274 FND_MESSAGE.SET_TOKEN('CURRENT_ENVIRONMENT_FLAG',l_current_environment_flag);
275 FND_MSG_PUB.Add;
276 END IF;
277
278 IF ( l_enabled_flag NOT IN ('N','Y') ) THEN
279 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_ENABLED_FLAG');
280 FND_MESSAGE.SET_TOKEN('ENABLED_FLAG',l_enabled_flag);
281 FND_MSG_PUB.Add;
282 END IF;
283
284 IF ( l_org_id = FND_API.G_MISS_NUM
285 OR TRIM(l_org_id) IS NULL ) THEN
286 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_ORG_ID');
287 FND_MESSAGE.SET_TOKEN('ORG_ID',l_org_id);
288 FND_MSG_PUB.Add;
289 END IF;
290
291 -- Raise exception if error
292 IF (FND_MSG_PUB.COUNT_MSG > 0)
293 THEN
294 RAISE fnd_api.g_exc_error;
295 END IF;
296
297 -- Check for duplicate or uniqueness of the Interest Type
298 OPEN interest_dup_cur(TRIM(NLS_UPPER(p_interest_type_rec.interest_type)));
299 FETCH interest_dup_cur INTO l_count;
300 IF (interest_dup_cur%FOUND)
301 THEN
302 FND_MESSAGE.SET_NAME('ASF', 'ASF_ADM_DUPLICATE');
303 --FND_MESSAGE.SET_NAME ( 'AS' , 'AS_DUPLICATE_INTEREST_TYPE');
304 fnd_msg_pub.add;
305 CLOSE interest_dup_cur;
306 RAISE fnd_api.g_exc_error;
307 ELSE
308 -- get nextval for interest type id
309 OPEN interest_id_cur;
310 FETCH interest_id_cur INTO x_interest_type_id;
311 IF (interest_id_cur%NOTFOUND)
312 THEN
313 CLOSE interest_id_cur;
314 RAISE fnd_api.g_exc_error;
315 END IF;
316 CLOSE interest_id_cur;
317
318 if (l_prod_cat_set_id is not null
319 AND l_prod_cat_id is not null) then
320 OPEN prod_category_val_cur
321 (l_prod_cat_set_id, l_prod_cat_id);
322 FETCH prod_category_val_cur INTO l_count;
323 IF (prod_category_val_cur%NOTFOUND) THEN
324 l_prod_cat_not_found := 'Y';
325 CLOSE prod_category_val_cur;
326 END IF;
327 CLOSE prod_category_val_cur;
328 elsif (l_prod_cat_set_id is not null
329 OR l_prod_cat_id is not null) then
330 l_prod_cat_not_found := 'Y';
331 end if;
332
333 if (l_prod_cat_not_found = 'Y') THEN
334 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_PRODUCT_CATEGORY');
335 fnd_msg_pub.add;
336 RAISE fnd_api.g_exc_error;
337 end if;
338
339 -- Insert a New Interest Type into table
340 as_interest_types_pkg.insert_row(
341 l_row_id,
342 x_interest_type_id,
343 l_master_enabled_flag,
344 l_enabled_flag,
345 l_company_classification_flag,
346 l_contact_interest_flag,
347 l_lead_classification_flag,
348 l_expected_purchase_flag,
349 l_current_environment_flag,
350 l_org_id,
351 l_interest_type,
352 l_description,
353 l_creation_date,
354 l_created_by,
355 l_last_update_date,
356 l_last_updated_by,
357 l_last_update_login,
358 l_prod_cat_set_id,
359 l_prod_cat_id
360 );
361 END IF;
362
363 -- Standard check of p_commit.
364 IF FND_API.To_Boolean( p_commit ) THEN
365 COMMIT WORK;
366 END IF;
367
368 -- Standard call to get message count and if count is 1,
369 -- get message info.
370
371 FND_MSG_PUB.Count_And_Get(
372 p_count => x_msg_count,
373 p_data => x_msg_data
374 );
375
376 EXCEPTION
377 WHEN FND_API.G_EXC_ERROR THEN
378 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
379 P_MODULE => l_module
380 ,P_API_NAME => L_API_NAME
381 ,P_PKG_NAME => G_PKG_NAME
382 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
383 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
384 ,X_MSG_COUNT => X_MSG_COUNT
385 ,X_MSG_DATA => X_MSG_DATA
386 ,X_RETURN_STATUS => X_RETURN_STATUS);
387
388 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
389 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
390 P_MODULE => l_module
391 ,P_API_NAME => L_API_NAME
392 ,P_PKG_NAME => G_PKG_NAME
393 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
394 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
395 ,X_MSG_COUNT => X_MSG_COUNT
396 ,X_MSG_DATA => X_MSG_DATA
397 ,X_RETURN_STATUS => X_RETURN_STATUS);
398
399 WHEN OTHERS THEN
400 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
401 P_MODULE => l_module
402 ,P_API_NAME => L_API_NAME
403 ,P_PKG_NAME => G_PKG_NAME
404 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
405 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
406 ,X_MSG_COUNT => X_MSG_COUNT
407 ,X_MSG_DATA => X_MSG_DATA
408 ,X_RETURN_STATUS => X_RETURN_STATUS);
409 END create_interest_type;
410
411 -- Start of Comments
412 --
413 -- API Name : update_interest_type
414 -- Type : Public
415 -- Function : To update the Interest Types using the table handler
416 -- Pre-Reqs : Table Handler as_interest_types_pkg.update_row should exist
417 -- Parameters :
418 -- IN : p_api_version_number IN NUMBER
419 -- p_init_msg_list IN VARCHAR2
420 -- p_commit IN VARCHAR2
421 -- p_validation_level IN NUMBER
422 -- p_interest_type_rec IN interest_code_rec_type
423 -- OUT : x_return_status OUT VARCHAR2
424 -- x_msg_count OUT NUMBER
425 -- x_msg_data OUT VARCHAR2
426 -- Version : 2.0
427 -- Purpose : Public API to Update Interest Types in the Oracle Sales Online.
428 -- Notes : This procedure is a public procedure called using the
429 -- public API as_interest_type_pub to update interest types.
430 --
431 -- End of Comments
432
433
434 PROCEDURE update_interest_type(
435 p_api_version_number IN NUMBER,
436 p_init_msg_list IN VARCHAR2 ,
437 p_commit IN VARCHAR2 ,
438 p_validation_level IN NUMBER ,
439 x_return_status OUT NOCOPY VARCHAR2,
440 x_msg_count OUT NOCOPY NUMBER,
441 x_msg_data OUT NOCOPY VARCHAR2,
442 p_interest_type_rec IN as_interest_types_pub.interest_type_rec_type
443 ) IS
444 -- Devlare Local Variables and Cursors
445 l_api_version NUMBER := p_api_version_number;
446 l_count NUMBER := 0;
447 l_api_name CONSTANT VARCHAR2(30) := 'update_interest_type';
448 x_interest_type_id NUMBER;
449 x_master_enabled_flag VARCHAR2(1);
450 x_enabled_flag VARCHAR2(1);
451 x_company_classification_flag VARCHAR2(1);
452 X_contact_interest_flag VARCHAR2(1);
453 x_lead_classification_flag VARCHAR2(1);
454 x_expected_purchase_flag VARCHAR2(1);
455 x_current_environment_flag VARCHAR2(1);
456 x_org_id NUMBER;
457 x_interest_type VARCHAR2(80);
458 x_description VARCHAR2(240);
459 x_last_update_date DATE;
460 x_last_updated_by NUMBER;
461 x_last_update_login NUMBER;
462 x_prod_cat_set_id NUMBER;
463 x_prod_cat_id NUMBER;
464 l_interest_type_id NUMBER;
465 l_master_enabled_flag VARCHAR2(1);
466 l_enabled_flag VARCHAR2(1);
467 l_company_classification_flag VARCHAR2(1);
468 l_contact_interest_flag VARCHAR2(1);
469 l_lead_classification_flag VARCHAR2(1);
470 l_expected_purchase_flag VARCHAR2(1);
471 l_current_environment_flag VARCHAR2(1);
472 l_org_id NUMBER;
473 l_interest_type VARCHAR2(80);
474 l_description VARCHAR2(240);
475 l_last_update_date DATE;
476 l_last_updated_by NUMBER;
477 l_current_last_update_date DATE;
478 l_last_update_login NUMBER;
479 l_prod_cat_set_id NUMBER;
480 l_prod_cat_id NUMBER;
481 l_prod_cat_not_found VARCHAR2(1) := 'N';
482
483 CURSOR get_db_values_cur(p_interest_type_id IN NUMBER) IS
484 SELECT master_enabled_flag,
485 org_id,
486 description,
487 enabled_flag,
488 company_classification_flag,
489 contact_interest_flag,
490 lead_classification_flag,
491 expected_purchase_flag,
492 current_environment_flag,
493 interest_type,
494 last_update_date,
495 last_updated_by,
496 last_update_login,
497 product_cat_set_id,
498 product_category_id
499 FROM as_interest_types_vl
500 WHERE interest_type_id = p_interest_type_id;
501
502 CURSOR as_int_type_cur(p_interest_type_id IN NUMBER,p_interest_type IN VARCHAR2) IS
503 SELECT 1
504 FROM as_interest_types_vl
505 WHERE interest_type_id <> p_interest_type_id
506 AND TRIM(NLS_UPPER(interest_type)) = p_interest_type;
507
508
509 CURSOR lock_row_for_update( p_interest_type_id in NUMBER) IS
510 SELECT last_update_date
511 FROM as_interest_types_vl
512 WHERE interest_type_id = p_interest_type_id;
513
514 CURSOR prod_category_val_cur(p_prod_cat_set_id IN NUMBER, p_prod_cat_id IN NUMBER) IS
515 SELECT 1 FROM ENI_PROD_DEN_HRCHY_PARENTS_V P
516 WHERE P.CATEGORY_ID = p_prod_cat_id
517 AND P.CATEGORY_SET_ID = p_prod_cat_set_id;
518 l_module CONSTANT VARCHAR2(255) := 'as.plsql.intypub.update_interest_type';
519
520 BEGIN
521
522 -- Standard STart of API SavePoint
523 SAVEPOINT update_interest_type_PUB;
524
525 -- Initialize message list if p_init_msg_list is set to TRUE.
526 IF FND_API.to_Boolean( p_init_msg_list ) THEN
527 FND_MSG_PUB.initialize;
528 END IF;
529
530 -- Standard call to check for call compatibility.
531 IF NOT FND_API.Compatible_API_Call (l_api_version,
532 p_api_version_number,
533 l_api_name,
534 G_PKG_NAME )
535 THEN
536 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
537 END IF;
538
539 -- Initialize API return status to success
540 x_return_status := FND_API.G_RET_STS_SUCCESS;
541
542 -- API Body --
543
544 -- Check if Required Values have been passed
545 IF (p_interest_type_rec.interest_type_id = FND_API.G_MISS_NUM)
546 OR TRIM(p_interest_type_rec.interest_type_id) IS NULL THEN
547 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_INT_TYPE_ID');
548 FND_MESSAGE.SET_TOKEN('INTEREST_TYPE_ID',
549 p_interest_type_rec.interest_type_id);
550 FND_MSG_PUB.Add;
551 END IF;
552
553
554 -- Check if the values are passed, if the g_miss values are passed then
555 -- replace the g_miss values with the database value. If the user passes
556 -- the value then use that value for update.
557
558 OPEN get_db_values_cur(p_interest_type_rec.interest_type_id);
559 FETCH get_db_values_cur INTO
560 l_master_enabled_flag,
561 l_org_id,
562 l_description,
563 l_enabled_flag,
564 l_company_classification_flag,
565 l_contact_interest_flag,
566 l_lead_classification_flag,
567 l_expected_purchase_flag,
568 l_current_environment_flag,
569 l_interest_type,
570 l_last_update_date,
571 l_last_updated_by,
572 l_last_update_login,
573 l_prod_cat_set_id,
574 l_prod_cat_id;
575 IF get_db_values_cur%NOTFOUND
576 THEN
577 CLOSE get_db_values_cur;
578 RAISE FND_API.G_EXC_ERROR;
579 END IF;
580 CLOSE get_db_values_cur;
581
582
583 -- If the value passed in is G_MISS then replace the value with
584 -- fetched value from the database before updating.
585
586 IF p_interest_type_rec.master_enabled_flag = FND_API.G_MISS_CHAR
587 THEN
588 x_master_enabled_flag := l_master_enabled_flag ;
589 ELSE
590 x_master_enabled_flag := p_interest_type_rec.master_enabled_flag ;
591 END IF;
592
593 IF p_interest_type_rec.enabled_flag = FND_API.G_MISS_CHAR
594 THEN
595 x_enabled_flag := l_enabled_flag ;
596 ELSE
597 x_enabled_flag := p_interest_type_rec.enabled_flag ;
598 END IF;
599
600 IF p_interest_type_rec.company_classification_flag = FND_API.G_MISS_CHAR
601 THEN
602 x_company_classification_flag := l_company_classification_flag ;
603 ELSE
604 x_company_classification_flag := p_interest_type_rec.company_classification_flag ;
605 END IF;
606
607 IF p_interest_type_rec.contact_interest_flag = FND_API.G_MISS_CHAR
608 THEN
609 x_contact_interest_flag := l_contact_interest_flag ;
610 ELSE
611 x_contact_interest_flag := p_interest_type_rec.contact_interest_flag ;
612 END IF;
613
614 IF p_interest_type_rec.lead_classification_flag = FND_API.G_MISS_CHAR
615 THEN
616 x_lead_classification_flag := l_lead_classification_flag ;
617 ELSE
618 x_lead_classification_flag := p_interest_type_rec.lead_classification_flag ;
619 END IF;
620
621 IF p_interest_type_rec.expected_purchase_flag = FND_API.G_MISS_CHAR
622 THEN
623 x_expected_purchase_flag := l_expected_purchase_flag ;
624 ELSE
625 x_expected_purchase_flag := p_interest_type_rec.expected_purchase_flag ;
626 END IF;
627
628 IF p_interest_type_rec.current_environment_flag = FND_API.G_MISS_CHAR
629 THEN
630 x_current_environment_flag := l_current_environment_flag ;
631 ELSE
632 x_current_environment_flag := p_interest_type_rec.current_environment_flag ;
633 END IF;
634
635
636 IF p_interest_type_rec.org_id = FND_API.G_MISS_NUM
637 THEN
638 x_org_id := l_org_id ;
639 ELSE
640 x_org_id := p_interest_type_rec.org_id ;
641 END IF;
642
643
644 IF p_interest_type_rec.interest_type = FND_API.G_MISS_CHAR
645 THEN
646 x_interest_type := l_interest_type ;
647 ELSE
648 x_interest_type := p_interest_type_rec.interest_type ;
649 END IF;
650
651 IF p_interest_type_rec.description = FND_API.G_MISS_CHAR
652 THEN
653 x_description := l_description ;
654 ELSE
655 x_description := p_interest_type_rec.description ;
656 END IF;
657
658 IF p_interest_type_rec.last_update_date = FND_API.G_MISS_DATE
659 OR TRIM(p_interest_type_rec.last_update_date) IS NULL
660 THEN
661 x_last_update_date := sysdate ;
662 ELSE
663 x_last_update_date := p_interest_type_rec.last_update_date ;
664 END IF;
665
666 IF p_interest_type_rec.last_updated_by = FND_API.G_MISS_NUM
667 OR TRIM(p_interest_type_rec.last_updated_by ) IS NULL
668 THEN
669 x_last_updated_by := fnd_global.user_id ;
670 ELSE
671 x_last_updated_by := p_interest_type_rec.last_updated_by ;
672 END IF;
673
674 IF p_interest_type_rec.last_update_login = FND_API.G_MISS_NUM
675 OR TRIM(p_interest_type_rec.last_update_login ) IS NULL
676 THEN
677 x_last_update_login := fnd_global.login_id ;
678 ELSE
679 x_last_update_login := p_interest_type_rec.last_update_login ;
680 END IF;
681
682 IF p_interest_type_rec.prod_cat_set_id = FND_API.G_MISS_NUM
683 THEN
684 x_prod_cat_set_id := l_prod_cat_set_id;
685 ELSE
686 x_prod_cat_set_id := p_interest_type_rec.prod_cat_set_id;
687 END IF;
688 IF p_interest_type_rec.prod_cat_id = FND_API.G_MISS_NUM
689 THEN
690 x_prod_cat_id := l_prod_cat_id;
691 ELSE
692 x_prod_cat_id := p_interest_type_rec.prod_cat_id;
693 END IF;
694
695 --Default Flags if null
696 IF x_master_enabled_flag IS NULL THEN
697 x_master_enabled_flag := 'N';
698 END IF;
699
700 IF x_contact_interest_flag IS NULL THEN
701 x_contact_interest_flag := 'N';
702 END IF;
703
704 IF x_company_classification_flag IS NULL THEN
705 x_company_classification_flag := 'N';
706 END IF;
707
708 IF x_lead_classification_flag IS NULL THEN
709 x_lead_classification_flag := 'N';
710 END IF;
711
712 IF x_expected_purchase_flag IS NULL THEN
713 x_expected_purchase_flag := 'N';
714 END IF;
715
716 IF x_current_environment_flag IS NULL THEN
717 x_current_environment_flag := 'N';
718 END IF;
719
720 IF x_enabled_flag IS NULL THEN
721 x_enabled_flag := 'N';
722 END IF;
723
724
725 -- Check if Valid Values have been passed
726 -- e.g. If master_enabled_flag value is passed and Not N or Y
727 -- trap it as an error
728 --
729
730 IF x_master_enabled_flag NOT IN ('N','Y')
731 THEN
732 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_MAST_ENAB_FLAG');
733 FND_MESSAGE.SET_TOKEN('MASTER_ENABLED_FLAG',p_interest_type_rec.master_enabled_flag);
734 FND_MSG_PUB.Add;
735 END IF;
736
737 IF x_contact_interest_flag NOT IN ('N','Y')
738 THEN
739 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_CONT_INT_FLAG');
740 FND_MESSAGE.SET_TOKEN('CONTACT_INTEREST_FLAG',p_interest_type_rec.contact_interest_flag);
741 FND_MSG_PUB.Add;
742 END IF;
743
744 IF x_company_classification_flag NOT IN ('N','Y')
745 THEN
746 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_COMP_CLAS_FLAG');
747 FND_MESSAGE.SET_TOKEN('COMPANY_CLASSIFICAITON_FLAG',p_interest_type_rec.company_classification_flag);
748 FND_MSG_PUB.Add;
749 END IF;
750
751 IF x_lead_classification_flag NOT IN ('N','Y')
752 THEN
753 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_LEAD_CLAS_FLAG');
754 FND_MESSAGE.SET_TOKEN('LEAD_CLASSIFICATION_FLAG',p_interest_type_rec.lead_classification_flag);
755 FND_MSG_PUB.Add;
756 END IF;
757
758
759 IF x_expected_purchase_flag NOT IN ('N','Y')
760 THEN
761 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_EXP_PURC_FLAG');
762 FND_MESSAGE.SET_TOKEN('EXPECTED_PURCHASE_FLAG',p_interest_type_rec.expected_purchase_flag);
763 FND_MSG_PUB.Add;
764 END IF;
765
766
767 IF x_current_environment_flag NOT IN ('N','Y')
768 THEN
769 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_CURR_ENVT_FLAG');
770 FND_MESSAGE.SET_TOKEN('CURRENT_ENVIRONMENT_FLAG',p_interest_type_rec.current_environment_flag);
771 FND_MSG_PUB.Add;
772 END IF;
773
774
775 IF x_enabled_flag NOT IN ('N','Y')
776 THEN
777 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_ENABLED_FLAG');
778 FND_MESSAGE.SET_TOKEN('ENABLED_FLAG',p_interest_type_rec.enabled_flag);
779 FND_MSG_PUB.Add;
780 END IF;
781
782 IF x_org_id = FND_API.G_MISS_NUM
783 THEN
784 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_ORG_ID');
785 FND_MESSAGE.SET_TOKEN('ORG_ID',p_interest_type_rec.org_id);
786 FND_MSG_PUB.Add;
787 END IF;
788
789 IF (FND_MSG_PUB.COUNT_MSG > 0)
790 THEN
791 RAISE fnd_api.g_exc_error;
792 END IF;
793
794
795 -- Check if Interest Type ID and Type exists
796 OPEN as_int_type_cur(p_interest_type_rec.interest_type_id,TRIM(NLS_UPPER(p_interest_type_rec.interest_type)));
797 FETCH as_int_type_cur INTO l_count;
798 IF as_int_type_cur%FOUND
799 THEN
800 FND_MESSAGE.SET_NAME('ASF', 'ASF_ADM_DUPLICATE');
801 FND_MSG_PUB.Add;
802 CLOSE as_int_type_cur;
803 RAISE FND_API.G_EXC_ERROR;
804 END IF;
805 CLOSE as_int_type_cur;
806
807 if (x_prod_cat_set_id is not null
808 AND x_prod_cat_id is not null) then
809 OPEN prod_category_val_cur
810 (x_prod_cat_set_id, x_prod_cat_id);
811 FETCH prod_category_val_cur INTO l_count;
812 IF (prod_category_val_cur%NOTFOUND) THEN
813 l_prod_cat_not_found := 'Y';
814 END IF;
815 CLOSE prod_category_val_cur;
816 elsif (x_prod_cat_set_id is not null
817 OR x_prod_cat_id is not null) then
818 l_prod_cat_not_found := 'Y';
819 end if;
820
821 if (l_prod_cat_not_found = 'Y') THEN
822 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_PRODUCT_CATEGORY');
823 fnd_msg_pub.add;
824 RAISE fnd_api.g_exc_error;
825 end if;
826
827 -- Lock the row for update. Check to see if the fetched value is same still.
828 -- If they are same then update the record else give a message that the row has been
829 -- updated by others.
830
831 OPEN lock_row_for_update(p_interest_type_rec.interest_type_id);
832 FETCH lock_row_for_update INTO l_current_last_update_date;
833 IF lock_row_for_update%NOTFOUND
834 THEN
835 CLOSE lock_row_for_update;
836 RAISE fnd_api.g_exc_unexpected_error;
837 END IF;
838
839 IF l_last_update_date <> l_current_last_update_date
840 THEN
841 fnd_message.set_name('AS', 'API_RECORD_CHANGED');
842 FND_MESSAGE.Set_Token('INFO', 'interest_type', FALSE); -- ??
843 fnd_msg_pub.add;
844 RAISE fnd_api.g_exc_unexpected_error;
845 END IF;
846
847 -- update Interest Type ID in the table
848 as_interest_types_pkg.update_row(
849 p_interest_type_rec.interest_type_id,
850 x_master_enabled_flag,
851 x_enabled_flag,
852 x_company_classification_flag,
853 x_contact_interest_flag,
854 x_lead_classification_flag,
855 x_expected_purchase_flag,
856 x_current_environment_flag,
857 x_org_id,
858 x_interest_type,
859 x_description,
860 SYSDATE,
861 x_last_updated_by,
862 x_last_update_login,
863 x_prod_cat_set_id,
864 x_prod_cat_id);
865
866 -- Close Cursor
867 CLOSE lock_row_for_update ;
868
869 -- Standard check of p_commit.
870 IF FND_API.To_Boolean( p_commit ) THEN
871 COMMIT WORK;
872 END IF;
873
874 -- Standard call to get message count and if count is 1, get
875 -- message info.
876 FND_MSG_PUB.Count_And_Get(
877 p_count => x_msg_count,
878 p_data => x_msg_data
879 );
880 EXCEPTION
881 WHEN FND_API.G_EXC_ERROR THEN
882 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
883 P_MODULE => l_module
884 ,P_API_NAME => L_API_NAME
885 ,P_PKG_NAME => G_PKG_NAME
886 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
887 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
888 ,X_MSG_COUNT => X_MSG_COUNT
889 ,X_MSG_DATA => X_MSG_DATA
890 ,X_RETURN_STATUS => X_RETURN_STATUS);
891
892 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
893 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
894 P_MODULE => l_module
895 ,P_API_NAME => L_API_NAME
896 ,P_PKG_NAME => G_PKG_NAME
897 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
898 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
899 ,X_MSG_COUNT => X_MSG_COUNT
900 ,X_MSG_DATA => X_MSG_DATA
901 ,X_RETURN_STATUS => X_RETURN_STATUS);
902
903 WHEN OTHERS THEN
904 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
905 P_MODULE => l_module
906 ,P_API_NAME => L_API_NAME
907 ,P_PKG_NAME => G_PKG_NAME
908 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
909 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
910 ,X_MSG_COUNT => X_MSG_COUNT
911 ,X_MSG_DATA => X_MSG_DATA
912 ,X_RETURN_STATUS => X_RETURN_STATUS);
913 END update_interest_type;
914 END as_interest_types_pub;
915
[Home] [Help]