1 PACKAGE BODY as_interest_codes_pub as
2 /* $Header: asxintcb.pls 120.1 2005/06/14 01:30:16 appldev $ */
3 -- Delcare Global Variables
4 G_PKG_NAME CONSTANT VARCHAR2(30):='as_interest_codes_pub';
5
6 --
7 -- ****************************************************************************
8 --
9 -- NAME : AS_INTEREST_CODES_PUB
10 --
11 -- Purpose :
12 -- Public API to Create and Update Interest Codes in the Oracle Sales
13 -- Online
14 --
15 -- History
16 --
17 -- 09/14/2002 Rajan T Created
18 --
19 -- ****************************************************************************
20
21
22 -- Start of Comments
23 --
24 -- API Name : create_interest_code
25 -- Type : Public
26 -- Function : To create the Interest Codes using the table handler
27 -- Pre-Reqs : Table Handler as_interest_codes_pkg.insert_row should exist
28 -- Parameters :
29 -- IN : p_api_version_number IN NUMBER
30 -- p_init_msg_list IN VARCHAR2
31 -- p_commit IN VARCHAR2
32 -- p_validation_level IN NUMBER
33 -- p_interest_code_rec IN interest_code_rec_type
34 -- OUT : x_return_status OUT VARCHAR2
35 -- x_msg_count OUT NUMBER
36 -- x_msg_data OUT VARCHAR2
37 -- Version : 2.0
38 -- Purpose : Public API to Create Interest Codes in the Oracle Sales Online.
39 -- Notes : This procedure is a public procedure called using the
40 -- public API as_interest_code_pub to create interest codes.
41 --
42 -- End of Comments
43
44 PROCEDURE create_interest_code(
45 p_api_version_number IN NUMBER,
46 p_init_msg_list IN VARCHAR2 ,
47 p_commit IN VARCHAR2 ,
48 p_validation_level IN NUMBER ,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2,
52 p_interest_code_rec IN interest_code_rec_type ,
53 x_interest_code_id OUT NOCOPY NUMBER
54 ) IS
55 -- Declare Local Variables and Cursors
56 l_api_version NUMBER := p_api_version_number;
57 l_count NUMBER := 0;
58 l_api_name CONSTANT VARCHAR2(30) := 'create_interest_code';
59 x_row_id VARCHAR2(100) := '';
60 l_creation_date DATE;
61 l_created_by NUMBER;
62 l_last_update_date DATE;
63 l_current_last_update_date DATE;
64 l_last_update_login NUMBER;
65 l_last_updated_by NUMBER;
66 l_parent_interest_code_id NUMBER;
67 l_category_id NUMBER;
68 l_category_set_id NUMBER;
69 l_attribute_category VARCHAR2(30);
70 l_attribute1 VARCHAR2(150);
71 l_attribute2 VARCHAR2(150);
72 l_attribute3 VARCHAR2(150);
73 l_attribute4 VARCHAR2(150);
74 l_attribute5 VARCHAR2(150);
75 l_attribute6 VARCHAR2(150);
76 l_attribute7 VARCHAR2(150);
77 l_attribute8 VARCHAR2(150);
78 l_attribute9 VARCHAR2(150);
79 l_attribute10 VARCHAR2(150);
80 l_attribute11 VARCHAR2(150);
81 l_attribute12 VARCHAR2(150);
82 l_attribute13 VARCHAR2(150);
83 l_attribute14 VARCHAR2(150);
84 l_attribute15 VARCHAR2(150);
85 l_pf_item_id NUMBER;
86 l_pf_organization_id NUMBER;
87 l_price NUMBER;
88 l_currency_code VARCHAR2(15);
89 l_code VARCHAR2(100);
90 l_description VARCHAR2(240);
91 l_prod_cat_set_id NUMBER;
92 l_prod_cat_id NUMBER;
93 l_prod_cat_not_found VARCHAR2(1) := 'N';
94
95 CURSOR as_int_code_cur(p_parent_interest_code_id IN NUMBER) IS
96 SELECT 1
97 FROM as_interest_codes_vl
98 WHERE interest_code_id = p_parent_interest_code_id
99 AND interest_code_id IS NOT NULL;
100
101
102 CURSOR as_int_type_cur(p_interest_type_id IN NUMBER) IS
103 SELECT 1
104 FROM as_interest_types_vl
105 WHERE interest_type_id = p_interest_type_id
106 AND interest_type_id IS NOT NULL;
107
108
109 CURSOR as_int_codes_vl_cur(p_code IN VARCHAR2,p_interest_type_id IN NUMBER) IS
110 SELECT 1
111 FROM as_interest_codes_vl
112 WHERE TRIM(NLS_UPPER(code)) = p_code -- passing in trimmed value while opening.
113 AND interest_type_id = p_interest_type_id
114 AND parent_interest_code_id IS NULL ;
115
116 CURSOR as_int_codes_vl_1_cur(p_code IN VARCHAR2,p_interest_type_id IN NUMBER,p_parent_interest_code_id IN NUMBER) IS
117 SELECT 1
118 FROM as_interest_codes_vl
119 WHERE TRIM(NLS_UPPER(code)) = p_code -- passing in trimmed value while opening.
120 AND interest_type_id = p_interest_type_id
121 AND parent_interest_code_id = p_parent_interest_code_id ;
122
123 CURSOR as_int_code_nextval_cur IS
124 SELECT as_interest_codes_s.NEXTVAL
125 FROM DUAL;
126
127 CURSOR prod_category_val_cur(p_prod_cat_set_id IN NUMBER, p_prod_cat_id IN NUMBER) IS
128 SELECT 1 FROM ENI_PROD_DEN_HRCHY_PARENTS_V P
129 WHERE P.CATEGORY_ID = p_prod_cat_id
130 AND P.CATEGORY_SET_ID = p_prod_cat_set_id;
131 l_module CONSTANT VARCHAR2(255) := 'as.plsql.intc.create_interest_code';
132
133 BEGIN
134 -- Standard Start of API SavePoint
135 SAVEPOINT create_interest_code_PUB;
136
137 FND_MSG_PUB.DELETE_MSG;
138
139 -- Initialize message list if p_init_msg_list is set to TRUE.
140 IF FND_API.to_Boolean( p_init_msg_list ) THEN
141 FND_MSG_PUB.initialize;
142 END IF;
143
144 -- Standard call to check for call compatibility.
145 IF NOT FND_API.Compatible_API_Call (l_api_version,
146 p_api_version_number,
147 l_api_name,
148 G_PKG_NAME )
149 THEN
150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
151 END IF;
152
153 -- Initialize API return status to success
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155
156 -- API Body --
157
158 -- Check if Required Values have been passed
159 IF p_interest_code_rec.code = FND_API.G_MISS_CHAR
160 OR TRIM(p_interest_code_rec.code) IS NULL
161 THEN
162 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_INT_CODE');
163 --FND_MESSAGE.SET_TOKEN('INTEREST_CODE',p_interest_code_rec.code);
164 FND_MSG_PUB.Add;
165 RAISE FND_API.G_EXC_ERROR;
166 END IF;
167
168 IF p_interest_code_rec.interest_type_id = FND_API.G_MISS_NUM
169 OR TRIM(p_interest_code_rec.interest_type_id) IS NULL
170 THEN
171 FND_MESSAGE.SET_NAME ( 'ASF' , 'ASF_MISSING_INTEREST_TYPE');
172 --FND_MESSAGE.SET_TOKEN('INTEREST_TYPE_ID',p_interest_code_rec.interest_type_id);
173 FND_MSG_PUB.Add;
174 RAISE FND_API.G_EXC_ERROR;
175 END IF;
176
177 IF p_interest_code_rec.master_enabled_flag NOT IN ('N','Y')
178 OR TRIM(p_interest_code_rec.master_enabled_flag) IS NULL
179 OR p_interest_code_rec.master_enabled_flag = FND_API.G_MISS_CHAR
180 THEN
181 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_MAST_ENAB_FLAG');
182 FND_MESSAGE.SET_TOKEN('MASTER_ENABLED_FLAG',p_interest_code_rec.master_enabled_flag);
183 FND_MSG_PUB.Add;
184 RAISE FND_API.G_EXC_ERROR;
185 END IF;
186
187 -- Check for Who columns to see if they values passed is G_MISS
188 -- Replace the G_MISS with NULL else replace with the value passed
189
190 IF p_interest_code_rec.creation_date = FND_API.G_MISS_DATE
191 OR TRIM(p_interest_code_rec.creation_date) IS NULL
192 THEN
193 l_creation_date := SYSDATE;
194 ELSE
195 l_creation_date := p_interest_code_rec.creation_date;
196 END IF;
197 IF p_interest_code_rec.created_by = FND_API.G_MISS_NUM
198 OR TRIM(p_interest_code_rec.created_by) IS NULL
199 THEN
200 l_created_by := FND_GLOBAL.user_id;
201 ELSE
202 l_created_by := p_interest_code_rec.created_by;
203 END IF;
204 IF p_interest_code_rec.last_update_date = FND_API.G_MISS_DATE
205 OR TRIM(p_interest_code_rec.last_update_date) IS NULL
206 THEN
207 l_last_update_date := SYSDATE;
208 ELSE
209 l_last_update_date := p_interest_code_rec.last_update_date;
210 END IF;
211 IF p_interest_code_rec.last_updated_by = FND_API.G_MISS_NUM
212 OR TRIM(p_interest_code_rec.last_updated_by) IS NULL
213 THEN
214 l_last_updated_by := FND_GLOBAL.user_id;
215 ELSE
216 l_last_updated_by := p_interest_code_rec.last_updated_by;
217 END IF;
218 IF p_interest_code_rec.last_update_login = FND_API.G_MISS_NUM
219 OR TRIM(p_interest_code_rec.last_update_login) IS NULL
220 THEN
221 l_last_update_login := FND_GLOBAL.login_id;
222 ELSE
223 l_last_update_login := p_interest_code_rec.last_update_login;
224 END IF;
225
226 -- End of Who Columns Check
227
228 -- Check for all optional fields to see if the value is G_MISS then
229 -- replace with NULL before insert. Else use the value passed for insert.
230 -- Only optional fields needs to be checked for Insert and replace with
231 -- NULL for required fields the DB will throw error.
232
233 IF p_interest_code_rec.parent_interest_code_id = FND_API.G_MISS_NUM
234 THEN
235 l_parent_interest_code_id := NULL;
236 ELSE
237 l_parent_interest_code_id := p_interest_code_rec.parent_interest_code_id;
238 END IF;
239
240 IF p_interest_code_rec.category_id = FND_API.G_MISS_NUM
241 OR TRIM(p_interest_code_rec.category_id) IS NULL
242 THEN
243 l_category_id := NULL;
244 ELSE
245 l_category_id := p_interest_code_rec.category_id;
246 END IF;
247 IF p_interest_code_rec.category_set_id = FND_API.G_MISS_NUM
248 OR TRIM(p_interest_code_rec.category_set_id) IS NULL
249 THEN
250 l_category_set_id := NULL;
251 ELSE
252 l_category_set_id := p_interest_code_rec.category_set_id;
253 END IF;
254 IF p_interest_code_rec.attribute_category = FND_API.G_MISS_CHAR
255 OR TRIM(p_interest_code_rec.attribute_category) IS NULL
256 THEN
257 l_attribute_category := NULL;
258 ELSE
259 l_attribute_category := p_interest_code_rec.attribute_category ;
260 END IF;
261 IF p_interest_code_rec.attribute1 = FND_API.G_MISS_CHAR
262 OR TRIM(p_interest_code_rec.attribute1) IS NULL
263 THEN
264 l_attribute1 := NULL;
265 ELSE
266 l_attribute1 := p_interest_code_rec.attribute1;
267 END IF;
268 IF p_interest_code_rec.attribute2 = FND_API.G_MISS_CHAR
269 OR TRIM(p_interest_code_rec.attribute2) IS NULL
270 THEN
271 l_attribute2 := NULL;
272 ELSE
273 l_attribute2 := p_interest_code_rec.attribute2;
274 END IF;
275 IF p_interest_code_rec.attribute3 = FND_API.G_MISS_CHAR
276 OR TRIM(p_interest_code_rec.attribute3) IS NULL
277 THEN
278 l_attribute3 := NULL;
279 ELSE
280 l_attribute3 := p_interest_code_rec.attribute3;
281 END IF;
282 IF p_interest_code_rec.attribute4 = FND_API.G_MISS_CHAR
283 OR TRIM(p_interest_code_rec.attribute4) IS NULL
284 THEN
285 l_attribute4 := NULL;
286 ELSE
287 l_attribute4 := p_interest_code_rec.attribute4;
288 END IF;
289 IF p_interest_code_rec.attribute5 = FND_API.G_MISS_CHAR
290 OR TRIM(p_interest_code_rec.attribute5) IS NULL
291 THEN
292 l_attribute5 := NULL;
293 ELSE
294 l_attribute5 := p_interest_code_rec.attribute5;
295 END IF;
296 IF p_interest_code_rec.attribute6 = FND_API.G_MISS_CHAR
297 OR TRIM(p_interest_code_rec.attribute6) IS NULL
298 THEN
299 l_attribute6 := NULL;
300 ELSE
301 l_attribute6 := p_interest_code_rec.attribute6;
302 END IF;
303 IF p_interest_code_rec.attribute7 = FND_API.G_MISS_CHAR
304 OR TRIM(p_interest_code_rec.attribute7) IS NULL
305 THEN
306 l_attribute7 := NULL;
307 ELSE
308 l_attribute7 := p_interest_code_rec.attribute7;
309 END IF;
310 IF p_interest_code_rec.attribute8 = FND_API.G_MISS_CHAR
311 OR TRIM(p_interest_code_rec.attribute8) IS NULL
312 THEN
313 l_attribute8 := NULL;
314 ELSE
315 l_attribute8 := p_interest_code_rec.attribute8;
316 END IF;
317 IF p_interest_code_rec.attribute9 = FND_API.G_MISS_CHAR
318 OR TRIM(p_interest_code_rec.attribute9) IS NULL
319 THEN
320 l_attribute9 := NULL;
321 ELSE
322 l_attribute9 := p_interest_code_rec.attribute9;
323 END IF;
324 IF p_interest_code_rec.attribute10 = FND_API.G_MISS_CHAR
325 OR TRIM(p_interest_code_rec.attribute10) IS NULL
326 THEN
327 l_attribute10 := NULL;
328 ELSE
329 l_attribute10 := p_interest_code_rec.attribute10;
330 END IF;
331 IF p_interest_code_rec.attribute11 = FND_API.G_MISS_CHAR
332 OR TRIM(p_interest_code_rec.attribute11) IS NULL
333 THEN
334 l_attribute11 := NULL;
335 ELSE
336 l_attribute11 := p_interest_code_rec.attribute11;
337 END IF;
338 IF p_interest_code_rec.attribute12 = FND_API.G_MISS_CHAR
339 OR TRIM(p_interest_code_rec.attribute12) IS NULL
340 THEN
341 l_attribute12 := NULL;
342 ELSE
343 l_attribute12 := p_interest_code_rec.attribute12;
344 END IF;
345 IF p_interest_code_rec.attribute13 = FND_API.G_MISS_CHAR
346 OR TRIM(p_interest_code_rec.attribute13) IS NULL
347 THEN
348 l_attribute13 := NULL;
349 ELSE
350 l_attribute13 := p_interest_code_rec.attribute13;
351 END IF;
352 IF p_interest_code_rec.attribute14 = FND_API.G_MISS_CHAR
353 OR TRIM(p_interest_code_rec.attribute14) IS NULL
354 THEN
355 l_attribute14 := NULL;
356 ELSE
357 l_attribute14 := p_interest_code_rec.attribute14;
358 END IF;
359 IF p_interest_code_rec.attribute15 = FND_API.G_MISS_CHAR
360 OR TRIM(p_interest_code_rec.attribute15) IS NULL
361 THEN
362 l_attribute15 := NULL;
363 ELSE
364 l_attribute15 := p_interest_code_rec.attribute15;
365 END IF;
366 IF p_interest_code_rec.pf_item_id = FND_API.G_MISS_NUM
367 OR TRIM(p_interest_code_rec.pf_item_id) IS NULL
368 THEN
369 l_pf_item_id := NULL;
370 ELSE
371 l_pf_item_id := p_interest_code_rec.pf_item_id ;
372 END IF;
376 l_pf_organization_id := NULL;
373 IF p_interest_code_rec.pf_organization_id = FND_API.G_MISS_NUM
374 OR TRIM(p_interest_code_rec.pf_organization_id) IS NULL
375 THEN
377 ELSE
378 l_pf_organization_id := p_interest_code_rec.pf_organization_id ;
379 END IF;
380 IF p_interest_code_rec.price = FND_API.G_MISS_NUM
381 OR TRIM(p_interest_code_rec.price) IS NULL
382 THEN
383 l_price := NULL;
384 ELSE
385 l_price := p_interest_code_rec.price ;
386 END IF;
387 IF p_interest_code_rec.currency_code = FND_API.G_MISS_CHAR
388 OR TRIM(p_interest_code_rec.currency_code) IS NULL
389 THEN
390 l_currency_code := NULL;
391 ELSE
392 l_currency_code := p_interest_code_rec.currency_code ;
393 END IF;
394 IF p_interest_code_rec.code = FND_API.G_MISS_CHAR
395 OR TRIM(p_interest_code_rec.code) IS NULL
396 THEN
397 l_code := NULL;
398 ELSE
399 l_code := p_interest_code_rec.code ;
400 END IF;
401 IF p_interest_code_rec.description = FND_API.G_MISS_CHAR
402 OR TRIM(p_interest_code_rec.description) IS NULL
403 THEN
404 l_description := NULL;
405 ELSE
406 l_description := p_interest_code_rec.description ;
407 END IF;
408
409 IF (p_interest_code_rec.prod_cat_set_id = FND_API.G_MISS_NUM) THEN
410 l_prod_cat_set_id := NULL;
411 ELSE
412 l_prod_cat_set_id := p_interest_code_rec.prod_cat_set_id;
413 END IF;
414
415 IF (p_interest_code_rec.prod_cat_id = FND_API.G_MISS_NUM) THEN
416 l_prod_cat_id := NULL;
417 ELSE
418 l_prod_cat_id := p_interest_code_rec.prod_cat_id;
419 END IF;
420 -- End of all optional fields check for g_miss
421
422
423 -- Check if Interest Type ID and Parent Interest Code ID exist in
424 -- as_interest_types_vl and as_interest_codes_vl respectively
425
426 IF (l_parent_interest_code_id IS NOT NULL)
427 THEN
428 OPEN as_int_code_cur(p_interest_code_rec.parent_interest_code_id);
429 FETCH as_int_code_cur INTO l_count;
430 IF (as_int_code_cur%NOTFOUND)
431 THEN
432 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_PAR_INT_CODE_ID');
433 FND_MESSAGE.SET_TOKEN('PARENT_INTEREST_CODE_ID',p_interest_code_rec.parent_interest_code_id);
434 FND_MSG_PUB.Add;
435 CLOSE as_int_code_cur;
436 RAISE FND_API.G_EXC_ERROR;
437 END IF;
438 CLOSE as_int_code_cur;
439 END IF;
440
441 IF (p_interest_code_rec.interest_type_id IS NOT NULL) THEN
442 OPEN as_int_type_cur(p_interest_code_rec.interest_type_id);
443 FETCH as_int_type_cur INTO l_count;
444 IF (as_int_type_cur%NOTFOUND)
445 THEN
446 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_INT_TYPE_ID');
447 FND_MESSAGE.SET_TOKEN('INTEREST_TYPE_ID',p_interest_code_rec.interest_type_id);
448 FND_MSG_PUB.Add;
449 CLOSE as_int_type_cur;
450 RAISE FND_API.G_EXC_ERROR;
451 END IF;
452 CLOSE as_int_type_cur;
453 END IF;
454
455 -- Check if the Interest Code exists.
456
457 IF l_parent_interest_code_id IS NULL THEN
458
459 -- If parent interest code id is null, then the combination of
460 -- interest_type_id and code must be unique
461
462 OPEN as_int_codes_vl_cur(TRIM(NLS_UPPER(p_interest_code_rec.code)),p_interest_code_rec.interest_type_id);
463 FETCH as_int_codes_vl_cur INTO l_count;
464 IF as_int_codes_vl_cur%FOUND
465 THEN
466 FND_MESSAGE.SET_NAME('ASF', 'ASF_ADM_DUPLICATE');
467 FND_MSG_PUB.Add;
468 CLOSE as_int_codes_vl_cur;
469 RAISE FND_API.G_EXC_ERROR;
470 END IF;
471 CLOSE as_int_codes_vl_cur;
472
473 ELSE
474
475 -- If parent interest code id is Not null, then the
476 -- combination of interest_type_id,parent interest code id
477 -- and code must be unique
478
479 OPEN as_int_codes_vl_1_cur(TRIM(NLS_UPPER(p_interest_code_rec.code)),p_interest_code_rec.interest_type_id,p_interest_code_rec.parent_interest_code_id);
480 FETCH as_int_codes_vl_1_cur INTO l_count;
481 IF as_int_codes_vl_1_cur%FOUND
482 THEN
483 FND_MESSAGE.SET_NAME('ASF', 'ASF_ADM_DUPLICATE');
484 FND_MSG_PUB.Add;
485 CLOSE as_int_codes_vl_1_cur;
486 RAISE FND_API.G_EXC_ERROR;
487 END IF;
488 CLOSE as_int_codes_vl_1_cur;
489 END IF;
490
491 -- Get the sequence number before inserting.
492 OPEN as_int_code_nextval_cur;
493 FETCH as_int_code_nextval_cur INTO x_interest_code_id;
494 IF as_int_code_nextval_cur%NOTFOUND
495 THEN
496 CLOSE as_int_code_nextval_cur;
497 RAISE FND_API.G_EXC_ERROR;
498 END IF;
499 CLOSE as_int_code_nextval_cur;
500
501 if (l_prod_cat_set_id is not null
502 AND l_prod_cat_id is not null) then
503 OPEN prod_category_val_cur
504 (l_prod_cat_set_id, l_prod_cat_id);
505 FETCH prod_category_val_cur INTO l_count;
506 IF (prod_category_val_cur%NOTFOUND) THEN
507 l_prod_cat_not_found := 'Y';
508 CLOSE prod_category_val_cur;
509 END IF;
510 CLOSE prod_category_val_cur;
511 elsif (l_prod_cat_set_id is not null
512 OR l_prod_cat_id is not null) then
513 l_prod_cat_not_found := 'Y';
514 end if;
515
516 if (l_prod_cat_not_found = 'Y') THEN
517 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_PRODUCT_CATEGORY');
518 fnd_msg_pub.add;
519 RAISE fnd_api.g_exc_error;
520 end if;
521
522 -- Insert a New Interest Code into table
526 p_interest_code_rec.interest_type_id,
523 as_interest_codes_pkg.insert_row(
524 x_row_id,
525 x_interest_code_id,
527 p_interest_code_rec.master_enabled_flag,
528 l_parent_interest_code_id,
529 l_category_id,
530 l_category_set_id,
531 l_attribute_category,
532 l_attribute1,
533 l_attribute2,
534 l_attribute3,
535 l_attribute4,
536 l_attribute5,
537 l_attribute6,
538 l_attribute7,
539 l_attribute8,
540 l_attribute9,
541 l_attribute10,
542 l_attribute11,
543 l_attribute12,
544 l_attribute13,
545 l_attribute14,
546 l_attribute15,
547 l_pf_item_id,
548 l_pf_organization_id,
549 l_price,
550 l_currency_code,
551 TRIM(l_code),
552 l_description,
553 l_creation_date,
554 l_created_by,
555 l_last_update_date,
556 l_last_updated_by,
557 l_last_update_login,
558 l_prod_cat_set_id,
559 l_prod_cat_id
560 );
561
562
563 -- Standard check of p_commit.
564 IF FND_API.To_Boolean( p_commit ) THEN
565 COMMIT WORK;
566 END IF;
567
568 -- Standard call to get message count and if count is 1,
569 -- get message info.
570 FND_MSG_PUB.Count_And_Get(
571 p_count => x_msg_count,
572 p_data => x_msg_data
573 );
574 EXCEPTION
575 WHEN FND_API.G_EXC_ERROR THEN
576 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
577 P_MODULE => l_module
578 ,P_API_NAME => L_API_NAME
579 ,P_PKG_NAME => G_PKG_NAME
580 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
581 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
582 ,X_MSG_COUNT => X_MSG_COUNT
583 ,X_MSG_DATA => X_MSG_DATA
584 ,X_RETURN_STATUS => X_RETURN_STATUS);
585
586 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
587 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
588 P_MODULE => l_module
589 ,P_API_NAME => L_API_NAME
590 ,P_PKG_NAME => G_PKG_NAME
591 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
592 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
593 ,X_MSG_COUNT => X_MSG_COUNT
594 ,X_MSG_DATA => X_MSG_DATA
595 ,X_RETURN_STATUS => X_RETURN_STATUS);
596
597 WHEN OTHERS THEN
598 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
599 P_MODULE => l_module
600 ,P_API_NAME => L_API_NAME
601 ,P_PKG_NAME => G_PKG_NAME
602 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
603 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
604 ,X_MSG_COUNT => X_MSG_COUNT
605 ,X_MSG_DATA => X_MSG_DATA
606 ,X_RETURN_STATUS => X_RETURN_STATUS);
607 END create_interest_code;
608
609 -- Start of Comments
610 --
611 -- API Name : update_interest_code
612 -- Type : Public
613 -- Function : To update the Interest Codes using the table handler
614 -- Pre-Reqs : Table Handler as_interest_codes_pkg.update_row should exist
615 -- Parameters :
616 -- IN : p_api_version_number IN NUMBER
617 -- p_init_msg_list IN VARCHAR2
618 -- p_commit IN VARCHAR2
619 -- p_validation_level IN NUMBER
620 -- p_interest_code_rec IN interest_code_rec_type
621 -- OUT : x_return_status OUT VARCHAR2
622 -- x_msg_count OUT NUMBER
623 -- x_msg_data OUT VARCHAR2
624 -- Version : 2.0
625 -- Purpose : Public API to update Interest Codes in the Oracle Sales Online.
626 -- Notes : This procedure is a public procedure called using the
627 -- public API as_interest_code_pub to update interest codes.
628 --
629 -- End of Comments
630
631 PROCEDURE update_interest_code(
632 p_api_version_number IN NUMBER,
633 p_init_msg_list IN VARCHAR2 ,
634 p_commit IN VARCHAR2 ,
635 p_validation_level IN NUMBER ,
636 x_return_status OUT NOCOPY VARCHAR2,
637 x_msg_count OUT NOCOPY NUMBER,
638 x_msg_data OUT NOCOPY VARCHAR2,
639 p_interest_code_rec IN interest_code_rec_type
640 ) IS
641 -- Declare Local Variable and Cursors
642 l_api_version NUMBER := p_api_version_number;
643 l_count NUMBER := 0;
644 l_count_ids NUMBER := 0;
645 l_api_name CONSTANT VARCHAR2(30) := 'update_interest_code';
646 x_last_update_date DATE;
647 x_last_update_login NUMBER;
648 x_last_updated_by NUMBER;
649 l_last_update_date DATE;
650 l_current_last_update_date DATE;
651 l_last_update_login NUMBER;
652 l_last_updated_by NUMBER;
653 x_parent_interest_code_id NUMBER;
657 x_interest_type_id NUMBER;
654 x_interest_code_id NUMBER;
655 x_category_id NUMBER;
656 x_category_set_id NUMBER;
658 x_attribute_category VARCHAR2(30);
659 x_attribute1 VARCHAR2(150);
660 x_attribute2 VARCHAR2(150);
661 x_attribute3 VARCHAR2(150);
662 x_attribute4 VARCHAR2(150);
663 x_attribute5 VARCHAR2(150);
664 x_attribute6 VARCHAR2(150);
665 x_attribute7 VARCHAR2(150);
666 x_attribute8 VARCHAR2(150);
667 x_attribute9 VARCHAR2(150);
668 x_attribute10 VARCHAR2(150);
669 x_attribute11 VARCHAR2(150);
670 x_attribute12 VARCHAR2(150);
671 x_attribute13 VARCHAR2(150);
672 x_attribute14 VARCHAR2(150);
673 x_attribute15 VARCHAR2(150);
674 x_pf_item_id NUMBER;
675 x_pf_organization_id NUMBER;
676 x_price NUMBER;
677 x_currency_code VARCHAR2(15);
678 x_code VARCHAR2(100);
679 x_description VARCHAR2(240);
680 x_master_enabled_flag VARCHAR2(1);
681 l_parent_interest_code_id NUMBER;
682 l_interest_code_id NUMBER;
683 l_category_id NUMBER;
684 l_category_set_id NUMBER;
685 l_interest_type_id NUMBER;
686 l_attribute_category VARCHAR2(30);
687 l_attribute1 VARCHAR2(150);
688 l_attribute2 VARCHAR2(150);
689 l_attribute3 VARCHAR2(150);
690 l_attribute4 VARCHAR2(150);
691 l_attribute5 VARCHAR2(150);
692 l_attribute6 VARCHAR2(150);
693 l_attribute7 VARCHAR2(150);
694 l_attribute8 VARCHAR2(150);
695 l_attribute9 VARCHAR2(150);
696 l_attribute10 VARCHAR2(150);
697 l_attribute11 VARCHAR2(150);
698 l_attribute12 VARCHAR2(150);
699 l_attribute13 VARCHAR2(150);
700 l_attribute14 VARCHAR2(150);
701 l_attribute15 VARCHAR2(150);
702 l_pf_item_id NUMBER;
703 l_pf_organization_id NUMBER;
704 l_price NUMBER;
705 l_currency_code VARCHAR2(15);
706 l_code VARCHAR2(100);
707 l_description VARCHAR2(240);
708 l_master_enabled_flag VARCHAR2(1);
709 x_prod_cat_set_id NUMBER;
710 x_prod_cat_id NUMBER;
711 l_prod_cat_set_id NUMBER;
712 l_prod_cat_id NUMBER;
713 l_prod_cat_not_found VARCHAR2(1) := 'N';
714
715 CURSOR fetch_db_value_cur(p_interest_code_id IN NUMBER) IS SELECT
716 master_enabled_flag,
717 parent_interest_code_id,
718 category_id,
719 category_set_id,
720 attribute_category,
721 attribute1,
722 attribute2,
723 attribute3,
724 attribute4,
725 attribute5,
726 attribute6,
727 attribute7,
728 attribute8,
729 attribute9,
730 attribute10,
731 attribute11,
732 attribute12,
733 attribute13,
734 attribute14,
735 attribute15,
736 pf_item_id,
737 pf_organization_id,
738 price,
739 currency_code,
740 code,
741 description,
742 last_update_date,
743 last_updated_by,
744 last_update_login,
745 product_cat_set_id,
746 product_category_id
747 FROM AS_INTEREST_CODES_vl
748 WHERE interest_code_id = p_interest_code_id;
749
750 CURSOR validate_interest_type_cur(p_interest_type_id IN NUMBER) IS
751 SELECT 1
752 FROM AS_INTEREST_TYPES_B
753 WHERE INTEREST_TYPE_ID = p_interest_type_id
754 AND INTEREST_TYPE_ID IS NOT NULL;
755
756 CURSOR validate_parent_int_code_cur(p_parent_interest_code_id IN NUMBER) IS
757 SELECT 1
758 FROM AS_INTEREST_CODES_B
759 WHERE INTEREST_CODE_ID = p_parent_interest_code_id
760 AND INTEREST_CODE_ID IS NOT NULL;
761
762 CURSOR as_int_codes_vl_cur(p_interest_code_id IN NUMBER, p_code IN VARCHAR2,p_interest_type_id IN NUMBER) IS
763 SELECT 1
764 FROM as_interest_codes_vl
765 WHERE interest_code_id <> p_interest_code_id
766 AND TRIM(NLS_UPPER(code)) = p_code ---- -- passing in trimmed value while opening.
767 AND interest_type_id = p_interest_type_id
768 AND parent_interest_code_id IS NULL ;
769
770 CURSOR as_int_codes_vl_1_cur(p_interest_code_id IN NUMBER, p_code IN VARCHAR2,p_interest_type_id IN NUMBER,p_parent_interest_code_id IN NUMBER) IS
771 SELECT 1
772 FROM as_interest_codes_vl
773 WHERE interest_code_id <> p_interest_code_id
774 AND TRIM(NLS_UPPER(code)) = p_code ----- -- passing in trimmed value while opening.
775 AND interest_type_id = p_interest_type_id
776 AND parent_interest_code_id = p_parent_interest_code_id ;
777
778 CURSOR lock_row_for_update(p_interest_code_id in number) IS
782
779 SELECT last_update_date
780 FROM as_interest_codes_vl
781 WHERE interest_code_id = p_interest_code_id;
783 CURSOR prod_category_val_cur(p_prod_cat_set_id IN NUMBER, p_prod_cat_id IN NUMBER) IS
784 SELECT 1 FROM ENI_PROD_DEN_HRCHY_PARENTS_V P
785 WHERE P.CATEGORY_ID = p_prod_cat_id
786 AND P.CATEGORY_SET_ID = p_prod_cat_set_id;
787 l_module CONSTANT VARCHAR2(255) := 'as.plsql.intc.update_interest_code';
788
789 BEGIN
790
791 -- Standard STart of API SavePoint
792 SAVEPOINT update_interest_code_PUB;
793
794 FND_MSG_PUB.DELETE_MSG;
795
796 -- Initialize message list if p_init_msg_list is set to TRUE.
797 IF FND_API.to_Boolean( p_init_msg_list ) THEN
798 FND_MSG_PUB.initialize;
799 END IF;
800
801 -- Standard call to check for call compatibility.
802 IF NOT FND_API.Compatible_API_Call (l_api_version,
803 p_api_version_number,
804 l_api_name,
805 G_PKG_NAME )
806 THEN
807 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
808 END IF;
809
810
811 -- Initialize API return status to success
812 x_return_status := FND_API.G_RET_STS_SUCCESS;
813
814 -- API Body --
815 -- Check if Required Values have been passed
816
817 IF (p_interest_code_rec.interest_code_id = FND_API.G_MISS_NUM)
818 OR TRIM(p_interest_code_rec.interest_code_id) IS NULL
819 THEN
820 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_INT_CODE');
821 --FND_MESSAGE.SET_TOKEN('INTEREST_CODE',p_interest_code_rec.code);
822 FND_MSG_PUB.Add;
823 RAISE FND_API.G_EXC_ERROR;
824 END IF;
825
826 IF (p_interest_code_rec.interest_type_id = FND_API.G_MISS_NUM )
827 OR TRIM(p_interest_code_rec.interest_type_id) IS NULL
828 THEN
829 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_INT_TYPE_ID');
830 FND_MESSAGE.SET_TOKEN('INTEREST_TYPE_ID',p_interest_code_rec.interest_type_id);
831 FND_MSG_PUB.Add;
832 RAISE FND_API.G_EXC_ERROR;
833 ELSE
834 OPEN validate_interest_type_cur(p_interest_code_rec.interest_type_id);
835 FETCH validate_interest_type_cur INTO l_count;
836 IF validate_interest_type_cur%NOTFOUND
837 THEN
838 CLOSE validate_interest_type_cur;
839 RAISE FND_API.G_EXC_ERROR;
840 ELSE
841 x_interest_type_id := p_interest_code_rec.interest_type_id;
842 END IF;
843 CLOSE validate_interest_type_cur;
844 END IF;
845
846
847 -- Check if the value passed is G_MISS if so then replace it with the
848 -- database fetched column
849
850 OPEN fetch_db_value_cur(p_interest_code_rec.interest_code_id) ;
851 FETCH fetch_db_value_cur INTO
852 l_master_enabled_flag,
853 l_parent_interest_code_id,
854 l_category_id,
855 l_category_set_id,
856 l_attribute_category,
857 l_attribute1,
858 l_attribute2,
859 l_attribute3,
860 l_attribute4,
861 l_attribute5,
862 l_attribute6,
863 l_attribute7,
864 l_attribute8,
865 l_attribute9,
866 l_attribute10,
867 l_attribute11,
868 l_attribute12,
869 l_attribute13,
870 l_attribute14,
871 l_attribute15,
872 l_pf_item_id,
873 l_pf_organization_id,
874 l_price,
875 l_currency_code,
876 l_code,
877 l_description,
878 l_last_update_date,
879 l_last_updated_by,
880 l_last_update_login,
881 l_prod_cat_set_id,
882 l_prod_cat_id;
883
884 IF fetch_db_value_cur%NOTFOUND
885 THEN
886 CLOSE fetch_db_value_cur;
887 RAISE fnd_api.g_exc_error;
888 END IF;
889 CLOSE fetch_db_value_cur;
890
891
892 -- For Who Columns if the value passed is G_miss then default them
893 -- else use the passed value
894
895 IF p_interest_code_rec.last_update_date = FND_API.G_MISS_DATE
896 OR TRIM(p_interest_code_rec.last_update_date) IS NULL
897 THEN
898 x_last_update_date := SYSDATE;
899 ELSE
900 x_last_update_date := p_interest_code_rec.last_update_date;
901 END IF;
902 IF p_interest_code_rec.last_updated_by = FND_API.G_MISS_NUM
903 OR TRIM(p_interest_code_rec.last_updated_by) IS NULL
904 THEN
905 x_last_updated_by := FND_GLOBAL.user_id;
906 ELSE
907 x_last_updated_by := p_interest_code_rec.last_updated_by;
908 END IF;
909 IF p_interest_code_rec.last_update_login = FND_API.G_MISS_NUM
910 OR TRIM(p_interest_code_rec.last_update_login) IS NULL
911 THEN
912 x_last_update_login := FND_GLOBAL.login_id;
913 ELSE
914 x_last_update_login := p_interest_code_rec.last_update_login;
915 END IF;
916
917
918 -- For all other required and optional columns check to see if the
919 -- value is g_miss then replace them with the database fetched column.
920
921
922 IF p_interest_code_rec.parent_interest_code_id = FND_API.G_MISS_NUM
923 THEN
924 x_parent_interest_code_id := l_parent_interest_code_id;
925 ELSE
926 -- make sure it is a valid parent_interest_code_id
930 THEN
927 OPEN validate_parent_int_code_cur(p_interest_code_rec.parent_interest_code_id);
928 FETCH validate_parent_int_code_cur INTO l_count;
929 IF validate_parent_int_code_cur%NOTFOUND
931
932 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_PAR_INT_CODE_ID');
933 FND_MESSAGE.SET_TOKEN('PARENT_INTEREST_CODE_ID',p_interest_code_rec.parent_interest_code_id);
934 FND_MSG_PUB.Add;
935 CLOSE validate_parent_int_code_cur;
936 RAISE FND_API.G_EXC_ERROR;
937 ELSE
938 x_parent_interest_code_id := p_interest_code_rec.parent_interest_code_id;
939 END IF;
940 CLOSE validate_parent_int_code_cur;
941 END IF;
942
943 IF p_interest_code_rec.master_enabled_flag = FND_API.G_MISS_CHAR
944 THEN
945 x_master_enabled_flag := l_master_enabled_flag;
946 ELSE
947 -- make sure master enabled flag is valid
948 IF p_interest_code_rec.master_enabled_flag NOT IN ('N','Y') THEN
949 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_MAST_ENAB_FLAG');
950 FND_MSG_PUB.Add;
951 RAISE FND_API.G_EXC_ERROR;
952 ELSE
953 x_master_enabled_flag := p_interest_code_rec.master_enabled_flag;
954 END IF;
955 END IF;
956
957 -- Check to see if the value passed is G_MISS if so replace the
958 -- value with the fetched value from the database.
959
960 IF p_interest_code_rec.code = FND_API.G_MISS_CHAR
961 THEN
962 x_code := l_code ;
963 ELSE
964 x_code := p_interest_code_rec.code ;
965 END IF;
966 IF p_interest_code_rec.category_id = FND_API.G_MISS_NUM
967 THEN
968 x_category_id := l_category_id ;
969 ELSE
970 x_category_id := p_interest_code_rec.category_id;
971 END IF;
972 IF p_interest_code_rec.category_set_id = FND_API.G_MISS_NUM
973 THEN
974 x_category_set_id := l_category_set_id ;
975 ELSE
976 x_category_set_id := p_interest_code_rec.category_set_id;
977 END IF;
978 IF p_interest_code_rec.attribute_category = FND_API.G_MISS_CHAR
979 THEN
980 x_attribute_category := l_attribute_category ;
981 ELSE
982 x_attribute_category := p_interest_code_rec.attribute_category ;
983 END IF;
984 IF p_interest_code_rec.attribute1 = FND_API.G_MISS_CHAR
985 THEN
986 x_attribute1 := l_attribute1 ;
987 ELSE
988 x_attribute1 := p_interest_code_rec.attribute1;
989 END IF;
990 IF p_interest_code_rec.attribute2 = FND_API.G_MISS_CHAR
991 THEN
992 x_attribute2 := l_attribute2 ;
993 ELSE
994 x_attribute2 := p_interest_code_rec.attribute2;
995 END IF;
996 IF p_interest_code_rec.attribute3 = FND_API.G_MISS_CHAR
997 THEN
998 x_attribute3 := l_attribute3 ;
999 ELSE
1000 x_attribute3 := p_interest_code_rec.attribute3;
1001 END IF;
1002 IF p_interest_code_rec.attribute4 = FND_API.G_MISS_CHAR
1003 THEN
1004 x_attribute4 := l_attribute4 ;
1005 ELSE
1006 x_attribute4 := p_interest_code_rec.attribute4;
1007 END IF;
1008 IF p_interest_code_rec.attribute5 = FND_API.G_MISS_CHAR
1009 THEN
1010 x_attribute5 := l_attribute5 ;
1011 ELSE
1012 x_attribute5 := p_interest_code_rec.attribute5;
1013 END IF;
1014 IF p_interest_code_rec.attribute6 = FND_API.G_MISS_CHAR
1015 THEN
1016 x_attribute6 := l_attribute6 ;
1017 ELSE
1018 x_attribute6 := p_interest_code_rec.attribute6;
1019 END IF;
1020 IF p_interest_code_rec.attribute7 = FND_API.G_MISS_CHAR
1021 THEN
1022 x_attribute7 := l_attribute7 ;
1023 ELSE
1024 x_attribute7 := p_interest_code_rec.attribute7;
1025 END IF;
1026 IF p_interest_code_rec.attribute8 = FND_API.G_MISS_CHAR
1027 THEN
1028 x_attribute8 := l_attribute8 ;
1029 ELSE
1030 x_attribute8 := p_interest_code_rec.attribute8;
1031 END IF;
1032 IF p_interest_code_rec.attribute9 = FND_API.G_MISS_CHAR
1033 THEN
1034 x_attribute9 := l_attribute9 ;
1035 ELSE
1036 x_attribute9 := p_interest_code_rec.attribute9;
1037 END IF;
1038 IF p_interest_code_rec.attribute10 = FND_API.G_MISS_CHAR
1039 THEN
1040 x_attribute10 := l_attribute10 ;
1041 ELSE
1042 x_attribute10 := p_interest_code_rec.attribute10;
1043 END IF;
1044 IF p_interest_code_rec.attribute11 = FND_API.G_MISS_CHAR
1045 THEN
1046 x_attribute11 := l_attribute11 ;
1047 ELSE
1048 x_attribute11 := p_interest_code_rec.attribute11;
1049 END IF;
1050 IF p_interest_code_rec.attribute12 = FND_API.G_MISS_CHAR
1051 THEN
1052 x_attribute12 := l_attribute12 ;
1053 ELSE
1054 x_attribute12 := p_interest_code_rec.attribute12;
1055 END IF;
1056 IF p_interest_code_rec.attribute13 = FND_API.G_MISS_CHAR
1057 THEN
1058 x_attribute13 := l_attribute13 ;
1059 ELSE
1060 x_attribute13 := p_interest_code_rec.attribute13;
1061 END IF;
1062 IF p_interest_code_rec.attribute14 = FND_API.G_MISS_CHAR
1063 THEN
1064 x_attribute14 := l_attribute14 ;
1065 ELSE
1066 x_attribute14 := p_interest_code_rec.attribute14;
1067 END IF;
1068 IF p_interest_code_rec.attribute15 = FND_API.G_MISS_CHAR
1069 THEN
1070 x_attribute15 := l_attribute15 ;
1071 ELSE
1072 x_attribute15 := p_interest_code_rec.attribute15;
1073 END IF;
1074 IF p_interest_code_rec.pf_item_id = FND_API.G_MISS_NUM
1075 THEN
1079 END IF;
1076 x_pf_item_id := l_pf_item_id ;
1077 ELSE
1078 x_pf_item_id := p_interest_code_rec.pf_item_id ;
1080 IF p_interest_code_rec.pf_organization_id = FND_API.G_MISS_NUM
1081 THEN
1082 x_pf_organization_id := l_pf_organization_id ;
1083 ELSE
1084 x_pf_organization_id := p_interest_code_rec.pf_organization_id ;
1085 END IF;
1086 IF p_interest_code_rec.price = FND_API.G_MISS_NUM
1087 THEN
1088 x_price := l_price ;
1089 ELSE
1090 x_price := p_interest_code_rec.price ;
1091 END IF;
1092 IF p_interest_code_rec.currency_code = FND_API.G_MISS_CHAR
1093 THEN
1094 x_currency_code := l_currency_code ;
1095 ELSE
1096 x_currency_code := p_interest_code_rec.currency_code ;
1097 END IF;
1098 IF p_interest_code_rec.description = FND_API.G_MISS_CHAR
1099 THEN
1100 x_description := l_description;
1101 ELSE
1102 x_description := p_interest_code_rec.description ;
1103 END IF;
1104
1105 IF p_interest_code_rec.prod_cat_set_id = FND_API.G_MISS_NUM THEN
1106 x_prod_cat_set_id := l_prod_cat_set_id;
1107 ELSE
1108 x_prod_cat_set_id := p_interest_code_rec.prod_cat_set_id;
1109 END IF;
1110
1111 IF p_interest_code_rec.prod_cat_id = FND_API.G_MISS_NUM THEN
1112 x_prod_cat_id := l_prod_cat_id;
1113 ELSE
1114 x_prod_cat_id := p_interest_code_rec.prod_cat_id;
1115 END IF;
1116
1117 IF x_parent_interest_code_id IS NULL THEN
1118
1119 -- If parent interest code id is null, then the combination of
1120 -- interest_type_id and code must be unique
1121
1122 OPEN as_int_codes_vl_cur(p_interest_code_rec.interest_code_id,TRIM(NLS_UPPER(x_code)),p_interest_code_rec.interest_type_id);
1123 FETCH as_int_codes_vl_cur INTO l_count_ids;
1124 IF as_int_codes_vl_cur%FOUND
1125 THEN
1126 FND_MESSAGE.SET_NAME('ASF', 'ASF_ADM_DUPLICATE');
1127 FND_MSG_PUB.Add;
1128 CLOSE as_int_codes_vl_cur;
1129 RAISE FND_API.G_EXC_ERROR;
1130 END IF;
1131 CLOSE as_int_codes_vl_cur;
1132
1133 ELSE
1134
1135 -- If parent interest code id is Not null, then the
1136 -- combination of interest_type_id,parent interest code id
1137 -- and code must be unique
1138
1139 OPEN as_int_codes_vl_1_cur(p_interest_code_rec.interest_code_id,TRIM(NLS_UPPER(x_code)),p_interest_code_rec.interest_type_id,x_parent_interest_code_id);
1140 FETCH as_int_codes_vl_1_cur INTO l_count_ids;
1141
1142 IF as_int_codes_vl_1_cur%FOUND
1143 THEN
1144 FND_MESSAGE.SET_NAME('ASF', 'ASF_ADM_DUPLICATE');
1145 FND_MSG_PUB.Add;
1146 CLOSE as_int_codes_vl_1_cur;
1147 RAISE FND_API.G_EXC_ERROR;
1148 END IF;
1149 CLOSE as_int_codes_vl_1_cur;
1150 END IF;
1151
1152 if (x_prod_cat_set_id is not null
1153 AND x_prod_cat_id is not null) then
1154 OPEN prod_category_val_cur
1155 (x_prod_cat_set_id, x_prod_cat_id);
1156 FETCH prod_category_val_cur INTO l_count;
1157 IF (prod_category_val_cur%NOTFOUND) THEN
1158 l_prod_cat_not_found := 'Y';
1159 END IF;
1160 CLOSE prod_category_val_cur;
1161 elsif (x_prod_cat_set_id is not null
1162 OR x_prod_cat_id is not null) then
1163 l_prod_cat_not_found := 'Y';
1164 end if;
1165
1166 if (l_prod_cat_not_found = 'Y') THEN
1167 FND_MESSAGE.SET_NAME ( 'AS' , 'AS_INVALID_PRODUCT_CATEGORY');
1168 fnd_msg_pub.add;
1169 RAISE fnd_api.g_exc_error;
1170 end if;
1171
1172 -- Lock the row for update. Check to see if the fetched value is same still.
1173 -- If they are same then update the record else give a message that the row has been
1174 -- updated by others.
1175
1176 OPEN lock_row_for_update(TRIM(NLS_UPPER(p_interest_code_rec.interest_code_id)));
1177 FETCH lock_row_for_update INTO l_current_last_update_date;
1178 IF lock_row_for_update%NOTFOUND
1179 THEN
1180 CLOSE lock_row_for_update;
1181 RAISE fnd_api.g_exc_unexpected_error;
1182 END IF;
1183
1184 IF l_last_update_date <> l_current_last_update_date
1185 THEN
1186 fnd_message.set_name('AS', 'API_RECORD_CHANGED');
1187 FND_MESSAGE.Set_Token('INFO', 'interest_code', FALSE); -- ??
1188 fnd_msg_pub.add;
1189 RAISE fnd_api.g_exc_unexpected_error;
1190 END IF;
1191
1192 -- Update the Row using the API
1193 as_interest_codes_pkg.update_row(
1194 p_interest_code_rec.interest_code_id,
1195 p_interest_code_rec.interest_type_id,
1196 x_master_enabled_flag,
1197 x_parent_interest_code_id,
1198 x_category_id,
1199 x_category_set_id,
1200 x_attribute_category,
1201 x_attribute1,
1202 x_attribute2,
1203 x_attribute3,
1204 x_attribute4,
1205 x_attribute5,
1206 x_attribute6,
1207 x_attribute7,
1208 x_attribute8,
1209 x_attribute9,
1210 x_attribute10,
1211 x_attribute11,
1212 x_attribute12,
1213 x_attribute13,
1214 x_attribute14,
1215 x_attribute15,
1216 x_pf_item_id,
1217 x_pf_organization_id,
1218 x_price,
1219 x_currency_code,
1220 x_code,
1221 x_description,
1222 x_last_update_date,
1223 x_last_updated_by,
1224 x_last_update_login,
1225 x_prod_cat_set_id,
1226 x_prod_cat_id
1230 CLOSE lock_row_for_update ;
1227 );
1228
1229 -- Close Cursors
1231
1232 -- Standard check of p_commit.
1233 IF FND_API.To_Boolean( p_commit ) THEN
1234 COMMIT WORK;
1235 END IF;
1236
1237 -- Standard call to get message count and if count is 1,
1238 -- get message info.
1239 FND_MSG_PUB.Count_And_Get(
1240 p_count => x_msg_count,
1241 p_data => x_msg_data
1242 );
1243
1244 EXCEPTION
1245 WHEN FND_API.G_EXC_ERROR THEN
1246 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1247 P_MODULE => l_module
1248 ,P_API_NAME => L_API_NAME
1249 ,P_PKG_NAME => G_PKG_NAME
1250 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1251 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
1252 ,X_MSG_COUNT => X_MSG_COUNT
1253 ,X_MSG_DATA => X_MSG_DATA
1254 ,X_RETURN_STATUS => X_RETURN_STATUS);
1255
1256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1257 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1258 P_MODULE => l_module
1259 ,P_API_NAME => L_API_NAME
1260 ,P_PKG_NAME => G_PKG_NAME
1261 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1262 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
1263 ,X_MSG_COUNT => X_MSG_COUNT
1264 ,X_MSG_DATA => X_MSG_DATA
1265 ,X_RETURN_STATUS => X_RETURN_STATUS);
1266
1267 WHEN OTHERS THEN
1268 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1269 P_MODULE => l_module
1270 ,P_API_NAME => L_API_NAME
1271 ,P_PKG_NAME => G_PKG_NAME
1272 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1273 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
1274 ,X_MSG_COUNT => X_MSG_COUNT
1275 ,X_MSG_DATA => X_MSG_DATA
1276 ,X_RETURN_STATUS => X_RETURN_STATUS);
1277 END update_interest_code;
1278 END as_interest_codes_pub;
1279
[Home] [Help]