1 PACKAGE BODY PV_ENTY_ATTR_VALUE_PUB AS
2 /* $Header: pvxvavpb.pls 120.6 2005/11/11 15:28:20 amaram ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_ENTY_ATTR_VALUE_PUB
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PV_ENTY_ATTR_VALUE_PUB';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvavpb.pls';
19
20 G_USER_ID NUMBER := NVL(FND_GLOBAL.USER_ID,-1);
21 G_LOGIN_ID NUMBER := NVL(FND_GLOBAL.CONC_LOGIN_ID,-1);
22 FUNCTION is_number (stg_in IN VARCHAR2) RETURN BOOLEAN;
23 FUNCTION sub_string(string_in IN VARCHAR2,upto_char IN VARCHAR2) RETURN VARCHAR2 ;
24 FUNCTION MATCH_CODE_TO_VALUE(code IN VARCHAR2,lov_tbl IN PV_ATTRIBUTE_UTIL.lov_data_tbl_type) RETURN VARCHAR2;
25 FUNCTION CHECK_CURRECY_FORMAT( p_entity_attr_value IN VARCHAR2 ) RETURN NUMBER;
26
27 -- Hint: Primary key needs to be returned.
28
29 PROCEDURE Upsert_Attr_Value(
30 p_api_version_number IN NUMBER
31 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
32 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
33 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
34
35 ,x_return_status OUT NOCOPY VARCHAR2
36 ,x_msg_count OUT NOCOPY NUMBER
37 ,x_msg_data OUT NOCOPY VARCHAR2
38
39 ,p_attribute_id IN NUMBER
40 ,p_entity IN VARCHAR2
41 ,p_entity_id IN NUMBER
42 ,p_version IN NUMBER :=0
43 ,p_attr_val_tbl IN attr_value_tbl_type := g_miss_attr_value_tbl
44 )
45
46
47
48 IS
49 l_api_name CONSTANT VARCHAR2(30) := 'Upsert_Attr_Value';
50 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
51 l_api_version_number CONSTANT NUMBER := 1.0;
52 l_object_version_number NUMBER := 1;
53
54 x_enty_attr_val_id NUMBER;
55 x_object_version_number NUMBER;
56 l_attr_val_tbl attr_value_tbl_type := p_attr_val_tbl;
57 l_attr_val_rec attr_val_rec_type := g_miss_attr_val_rec;
58 l_enty_attr_val_rec PV_Enty_Attr_Value_PVT.enty_attr_val_rec_type := PV_Enty_Attr_Value_PVT.g_miss_enty_attr_val_rec;
59 l_version NUMBER := FND_API.G_MISS_NUM;
60
61 l_decimal_pts NUMBER;
62 l_attribute_type VARCHAR2(30);
63 l_display_style VARCHAR2(30);
64 l_attribute_name VARCHAR2(60) ;
65 l_external_update_text VARCHAR2(2000);
66 l_attr_data_type VARCHAR2(30);
67 l_value VARCHAR2(2000);
68 l_require_validation_flag VARCHAR2(1);
69 l_lov_string VARCHAR2(2000);
70 l_meaning VARCHAR2(80);
71 l_lead_record_exists VARCHAR2(1) := 'N';
72 l_index NUMBER :=0 ;
73 l_lead_enty_attr_val_rec PV_Enty_Attr_Value_PVT.enty_attr_val_rec_type := PV_Enty_Attr_Value_PVT.g_miss_enty_attr_val_rec;
74 l_curr_row1 NUMBER;
75 type cur_type IS REF CURSOR;
76 lc_lov_cursor cur_type;
77 l_lov_values_table JTF_VARCHAR2_TABLE_100;
78 l_lov_data_rec PV_ATTRIBUTE_UTIL.lov_data_rec_type := PV_ATTRIBUTE_UTIL.g_miss_lov_data_rec;
79 l_lov_data_tbl PV_ATTRIBUTE_UTIL.lov_data_tbl_type:= PV_ATTRIBUTE_UTIL.g_miss_lov_data_tbl;
80 l_percentage_total NUMBER := 0;
81 l_perc_sum_profile_value NUMBER := 100;
82 l_character_width NUMBER;
83 l_date DATE;
84
85 CURSOR c_get_previous_version(cv_attribute_id NUMBER,cv_entity_id NUMBER, cv_entity VARCHAR2) IS
86 SELECT distinct version
87 FROM PV_ENTY_ATTR_VALUES
88 WHERE attribute_id = cv_attribute_id and
89 entity_id = cv_entity_id and
90 entity = cv_entity and
91 latest_flag = 'Y';
92
93 CURSOR c_get_enty_attr_value(cv_attribute_id NUMBER,cv_entity_id NUMBER, cv_entity VARCHAR2) IS
94 SELECT *
95 FROM PV_ENTY_ATTR_VALUES
96 WHERE attribute_id = cv_attribute_id and
97 entity_id = cv_entity_id and
98 entity = cv_entity and
99 latest_flag = 'Y';
100
101 CURSOR c_get_attr_details(cv_attribute_id NUMBER) IS
102 SELECT attribute_type,display_style,DECIMAL_POINTS,name, character_width
103 FROM PV_ATTRIBUTES_VL
104 WHERE attribute_id = cv_attribute_id
105 ;
106
107 CURSOR c_get_attr_enty_details (pc_attribute_id IN NUMBER, pc_entity IN VARCHAR2) IS
108 SELECT external_update_text, attr_data_type, require_validation_flag, lov_string
109 FROM PV_ENTITY_ATTRS
110 WHERE attribute_id = pc_attribute_id AND
111 entity= pc_entity
112 ;
113
114 BEGIN
115 -- Standard Start of API savepoint
116 SAVEPOINT Upsert_Attr_Value_PUB;
117
118 -- Standard call to check for call compatibility.
119 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
120 p_api_version_number,
121 l_api_name,
122 G_PKG_NAME)
123 THEN
124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125 END IF;
126
127 -- Initialize message list if p_init_msg_list is set to TRUE.
128 IF FND_API.to_Boolean( p_init_msg_list )
129 THEN
130 FND_MSG_PUB.initialize;
131 END IF;
132
133 -- Debug Message
134 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
135 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - start');
136 END IF;
137
138 -- Initialize API return status to SUCCESS
139 x_return_status := FND_API.G_RET_STS_SUCCESS;
140 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
141 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
142 END IF;
143
144
145 --check for required items like attribute_id, entity, entity_id,versioin
146 IF p_attribute_id IS NULL THEN
147 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
148 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
149 FND_MESSAGE.set_token('COLUMN','attribute_id');
150 FND_MSG_PUB.add;
151 END IF;
152 x_return_status := FND_API.g_ret_sts_error;
153 RETURN;
154 END IF;
155 IF p_entity IS NULL THEN
156 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
157 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
158 FND_MESSAGE.set_token('COLUMN','entity type');
159 FND_MSG_PUB.add;
160 END IF;
161 x_return_status := FND_API.g_ret_sts_error;
162 RETURN;
163 END IF;
164 IF p_entity_id IS NULL THEN
165 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
166 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
167 FND_MESSAGE.set_token('COLUMN','entity_id');
168 FND_MSG_PUB.add;
169 END IF;
170 x_return_status := FND_API.g_ret_sts_error;
171 RETURN;
172 END IF;
173
174
175
176 --getting attribute details
177
178 for x in c_get_attr_details(cv_attribute_id => p_attribute_id )
179 loop
180 l_attribute_type := x.attribute_type;
181 l_display_style := x.display_style;
182 l_decimal_pts := x.decimal_points;
183 l_attribute_name:= x.name;
184 l_character_width := x.character_width;
185 end loop;
186
187 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
188 PVX_Utility_PVT.debug_message('Attribute ID:' || p_attribute_id);
189 PVX_Utility_PVT.debug_message('Attribute Type:' || l_attribute_type);
190 PVX_Utility_PVT.debug_message('Display style:' || l_display_style);
191 PVX_Utility_PVT.debug_message('Attribute Name:' || l_attribute_name);
192 PVX_Utility_PVT.debug_message('Attribute character width:' || l_character_width);
193
194 END IF;
195
196 --getting attribute entity details
197
198 for x in c_get_attr_enty_details (pc_attribute_id => p_attribute_id,
199 pc_entity => p_entity)
200 loop
201 l_external_update_text := x.external_update_text;
202 l_attr_data_type := x.attr_data_type;
203 l_require_validation_flag := x.require_validation_flag;
204 l_lov_string := x.lov_string;
205 end loop;
206
207 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
208 PVX_Utility_PVT.debug_message('entity:' || p_entity);
209 PVX_Utility_PVT.debug_message('external update text:' || l_external_update_text);
210 PVX_Utility_PVT.debug_message('attr data type:' || l_attr_data_type);
211 PVX_Utility_PVT.debug_message('validation flag:' || l_require_validation_flag);
212 PVX_Utility_PVT.debug_message('Lov String:' || l_lov_string);
213
214 END IF;
215
216 --if entity is LEAD (Opportunity), We are not dealing with all versioning and all.
217 -- In this case we just simply insert and update
218
219 if(p_entity='LEAD') then
220
221 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
222 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' - entity is opportunity');
223 END IF;
224
225 for x in c_get_enty_attr_value( cv_attribute_id => p_attribute_id,
226 cv_entity_id => p_entity_id,
227 cv_entity => p_entity
228 )
229 loop
230 l_lead_record_exists := 'Y';
231
232 end loop;
233
234 /*for x in c_get_enty_attr_value( cv_attribute_id => p_attribute_id,
235 cv_entity_id => p_entity_id,
236 cv_entity => p_entity
237 )
238 loop
239
240 l_lead_record_exists := 'Y';
241
242
243 l_lead_enty_attr_val_rec.enty_attr_val_id := x.enty_attr_val_id;
244 l_lead_enty_attr_val_rec.object_version_number := x.object_version_number;
245 l_lead_enty_attr_val_rec.entity := x.entity;
246 l_lead_enty_attr_val_rec.attribute_id := x.attribute_id;
247 l_lead_enty_attr_val_rec.party_id := x.party_id;
248 l_lead_enty_attr_val_rec.attr_value := x.attr_value;
249 l_lead_enty_attr_val_rec.score := x.score;
250 l_lead_enty_attr_val_rec.enabled_flag := x.enabled_flag;
251 l_lead_enty_attr_val_rec.entity_id := x.entity_id;
252
253 end loop;
254 */
255
256
257 IF (l_attr_val_tbl IS NULL OR
258 (l_attr_val_tbl IS NOT NULL AND l_attr_val_tbl.count = 0)
259
260 )
261 THEN
262
263 if(l_lead_record_exists = 'Y') then
264 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
265 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' -deleting act');
266 END IF;
267
268 for x in c_get_enty_attr_value( cv_attribute_id => p_attribute_id,
269 cv_entity_id => p_entity_id,
270 cv_entity => p_entity
271 )
272 loop
273 PV_Enty_Attr_Value_PVT.Delete_attr_value(
274
275 p_api_version_number => p_api_version_number
276 ,p_init_msg_list => p_init_msg_list
277 ,p_commit => p_commit
278 ,p_validation_level => p_validation_level
279
280 ,x_return_status => x_return_status
281 ,x_msg_count => x_msg_count
282 ,x_msg_data => x_msg_data
283
284 ,p_enty_attr_val_id => x.enty_attr_val_id
285 ,p_object_version_number => x.object_version_number
286
287 );
288
289
290 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
291 RAISE FND_API.G_EXC_ERROR;
292 END IF;
293 end loop;
294 end if;
295
296 ELSE --else of IF (l_attr_val_tbl IS NULL OR
297
298 if(l_lead_record_exists = 'Y') then
299 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
300 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' -update act');
301 END IF;
302 --if you find any rows, firsdt delete and add the new ones
303 for x in c_get_enty_attr_value( cv_attribute_id => p_attribute_id,
304 cv_entity_id => p_entity_id,
305 cv_entity => p_entity
306 )
307 loop
308 PV_Enty_Attr_Value_PVT.Delete_attr_value(
309
310 p_api_version_number => p_api_version_number
311 ,p_init_msg_list => p_init_msg_list
312 ,p_commit => p_commit
313 ,p_validation_level => p_validation_level
314 ,x_return_status => x_return_status
315 ,x_msg_count => x_msg_count
316 ,x_msg_data => x_msg_data
317
318
319 ,p_enty_attr_val_id => x.enty_attr_val_id
320 ,p_object_version_number => x.object_version_number
321
322 );
323 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
324 RAISE FND_API.G_EXC_ERROR;
325 END IF;
326 end loop;
327
328 FOR l_curr_row IN l_attr_val_tbl.first..l_attr_val_tbl.last LOOP
329
330 l_attr_val_rec := l_attr_val_tbl(l_curr_row);
331
332
333 --initialise it to null
334 l_lead_enty_attr_val_rec := PV_Enty_Attr_Value_PVT.g_miss_enty_attr_val_rec;
335
336 --initialising standard columns in the record
337 --initialising in parameters to record fields
338 l_lead_enty_attr_val_rec.entity := p_entity;
339 l_lead_enty_attr_val_rec.attribute_id := p_attribute_id;
340
341 l_lead_enty_attr_val_rec.attr_value := l_attr_val_rec.attr_value;
342 l_lead_enty_attr_val_rec.enabled_flag := 'Y';
343 l_lead_enty_attr_val_rec.entity_id := p_entity_id;
344
345
346 PV_Enty_Attr_Value_PVT.Create_attr_value(
347
348 p_api_version_number => p_api_version_number
349 ,p_init_msg_list => p_init_msg_list
350 ,p_commit => p_commit
351 ,p_validation_level => p_validation_level
352
353 ,x_return_status => x_return_status
354 ,x_msg_count => x_msg_count
355 ,x_msg_data => x_msg_data
356
357
358 ,p_enty_attr_val_rec => l_lead_enty_attr_val_rec
359 ,x_enty_attr_val_id => x_enty_attr_val_id
360 );
361 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
362 RAISE FND_API.G_EXC_ERROR;
363 END IF;
364
365 END LOOP; --FOR l_curr_row IN 1..l_attr_val_tbl.count LOOP
366
367
368 else
369 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
370 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' -creat act');
371 END IF;
372
373 FOR l_curr_row IN l_attr_val_tbl.first..l_attr_val_tbl.last LOOP
374
375 l_attr_val_rec := l_attr_val_tbl(l_curr_row);
376
377
378 --initialise it to null
379 l_lead_enty_attr_val_rec := PV_Enty_Attr_Value_PVT.g_miss_enty_attr_val_rec;
380
381 --initialising standard columns in the record
382 --initialising in parameters to record fields
383 l_lead_enty_attr_val_rec.entity := p_entity;
384 l_lead_enty_attr_val_rec.attribute_id := p_attribute_id;
385
386 l_lead_enty_attr_val_rec.attr_value := l_attr_val_rec.attr_value;
387 l_lead_enty_attr_val_rec.enabled_flag := 'Y';
388 l_lead_enty_attr_val_rec.entity_id := p_entity_id;
389
390
391 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
392 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' -creat act');
393 END IF;
394
395
396
397
398 PV_Enty_Attr_Value_PVT.Create_attr_value(
399
400 p_api_version_number => p_api_version_number
401 ,p_init_msg_list => p_init_msg_list
402 ,p_commit => p_commit
403 ,p_validation_level => p_validation_level
404
405 ,x_return_status => x_return_status
406 ,x_msg_count => x_msg_count
407 ,x_msg_data => x_msg_data
408
409
410 ,p_enty_attr_val_rec => l_lead_enty_attr_val_rec
411 ,x_enty_attr_val_id => x_enty_attr_val_id
412 );
413 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
414 RAISE FND_API.G_EXC_ERROR;
415 END IF;
416
417 END LOOP; --FOR l_curr_row IN 1..l_attr_val_tbl.count LOOP
418 end if; -- end of if(l_lead_record_exists = 'Y') then
419
420 END IF; -- end of IF (l_attr_val_tbl IS NULL OR
421
422
423
424 -- if entity is not LEAD, we just simply maintain all versioning and all.
425 else
426 -- If attribute type is EXTERNAL and EXT_INT, look for external_update_text and
427 -- see if it is not null. if it is not null, execute it .
428 -- In all other cases insert in to pv_enty_attr_values.
429
430 IF(l_attr_data_type in ('EXTERNAL','EXT_INT') AND
431 l_external_update_text is not null
432 ) THEN
433 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
434 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - attr_data_type is :'|| l_attr_data_type);
435 END IF;
436
437 --if update text is not null , then call update API for each attribute
438 --IF (l_external_update_text is not null) THEN
439
440
441 IF (l_attr_val_tbl IS NOT NULL AND l_attr_val_tbl.count <> 0
442 ) THEN
443
444 FOR l_curr_row IN l_attr_val_tbl.first..l_attr_val_tbl.last LOOP
445 l_attr_val_rec := l_attr_val_tbl(l_curr_row);
446
447 l_value := l_attr_val_rec.attr_value;
448 exit;
449 END LOOP; --FOR l_curr_row IN 1..l_attr_val_tbl.count LOOP
450
451
452
453 BEGIN
454
455 EXECUTE IMMEDIATE l_external_update_text USING p_api_version_number
456 ,p_init_msg_list
457 ,p_commit
458 ,p_validation_level
459 ,out x_return_status
460 ,out x_msg_count
461 ,out x_msg_data
462 ,p_entity
463 ,p_entity_id
464 ,l_value;
465
466
467 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
468
469 RAISE FND_API.G_EXC_ERROR;
470 END IF;
471
472 EXCEPTION
473 WHEN OTHERS THEN
474 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
475 FND_MESSAGE.set_name('PV', 'PV_API_EXECUTION_ERROR');
476 FND_MESSAGE.set_token('TEXT', l_external_update_text);
477 FND_MESSAGE.set_token('ID', TO_CHAR(p_attribute_id));
478 FND_MESSAGE.set_token('NAME', l_attribute_name);
479 FND_MESSAGE.set_token('ENTITY',p_entity);
480 FND_MSG_PUB.add;
481 END IF;
482 RAISE FND_API.G_EXC_ERROR;
483 END;
484
485 END IF;
486
487 --end if;
488
489 ELSE
490 -- In all other cases than l_attr_data_type in ('EXTERNAL','EXT_INT') and l_external_update_text is not null
491 -- this else block will end at last line
492
493
494
495 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
496 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - attr_data_type is :'|| l_attr_data_type);
497 END IF;
498 --check for version number
499 --Here we are treating version as object_version_number. So we check this version with previous versions
500
501 -- get previous version first
502
503 for x in c_get_previous_version(cv_attribute_id => p_attribute_id,
504 cv_entity_id => p_entity_id,
505 cv_entity => p_entity
506 )
507 loop
508 l_version := x.version;
509 end loop;
510
511 --if no row for this attribute id, entity, the you will get version as null,
512 --so you need to initialise version to 1
513
514 IF l_version IS NULL OR l_version = FND_API.G_MISS_NUM THEN
515 l_version := 0;
516 END IF;
517
518 -- Check Whether record has been changed by someone else
519 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' - l_version :' || l_version);
520
521 If (l_version <> p_version) Then
522 -- IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
523 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
524
525 FND_MESSAGE.set_name('PV', 'PV_API_RECORD_CHANGED');
526 FND_MESSAGE.set_token('VALUE','Attribute Entity Value');
527 FND_MSG_PUB.add;
528 END IF;
529 RAISE FND_API.G_EXC_ERROR;
530 End if;
531
532
533 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
534 THEN
535 -- Debug message
536 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
537 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' - Validate_attr_value');
538 END IF;
539
540
541 --since validation procedures will be done in record level in private API, No need to do any thing here
542 --We have done all validations that are to be done above.
543
544 END IF;
545
546 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
547 RAISE FND_API.G_EXC_ERROR;
548 END IF;
549
550
551
552
553 --Update attribute_values with latest_flag = 'Y' before inserting new rows by changing latest_flag to 'N'
554
555 for x in c_get_enty_attr_value(cv_attribute_id => p_attribute_id,
556 cv_entity_id => p_entity_id,
557 cv_entity => p_entity
558 )
559 loop
560 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
561 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' - Updating loop');
562 END IF;
563 l_enty_attr_val_rec := PV_Enty_Attr_Value_PVT.g_miss_enty_attr_val_rec;
564
565 --get all values from table and assign it to record and call update_attr_value of pv_enty_attr_value_pvt
566 l_enty_attr_val_rec.enty_attr_val_id := x.enty_attr_val_id;
567 l_enty_attr_val_rec.object_version_number := x.object_version_number;
568 l_enty_attr_val_rec.entity := x.entity;
569 l_enty_attr_val_rec.attribute_id := x.attribute_id;
570 l_enty_attr_val_rec.party_id := x.party_id;
571 l_enty_attr_val_rec.attr_value := x.attr_value;
572 l_enty_attr_val_rec.score := x.score;
573 l_enty_attr_val_rec.enabled_flag := x.enabled_flag;
574 l_enty_attr_val_rec.entity_id := x.entity_id;
575 l_enty_attr_val_rec.version := x.version;
576 --update latest flag with 'N'
577 l_enty_attr_val_rec.latest_flag := 'N';
578 l_enty_attr_val_rec.attr_value_extn := x.attr_value_extn;
579 l_enty_attr_val_rec.validation_id := x.validation_id;
580
581 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
582 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' - before calling pvt update method');
583 END IF;
584 PV_Enty_Attr_Value_PVT.Update_Attr_Value(
585
586 p_api_version_number => p_api_version_number
587 ,p_init_msg_list => p_init_msg_list
588 ,p_commit => p_commit
589 ,p_validation_level => p_validation_level
590
591 ,x_return_status => x_return_status
592 ,x_msg_count => x_msg_count
593 ,x_msg_data => x_msg_data
594
595 ,p_enty_attr_val_rec => l_enty_attr_val_rec
596 ,x_object_version_number => x_object_version_number
597
598 );
599
600 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
601 RAISE FND_API.G_EXC_ERROR;
602 END IF;
603
604 END LOOP; --for x in c_get_enty_attr_value
605
606 --getting attribute details
607
608 /*
609 for x in c_get_attr_details(cv_attribute_id => p_attribute_id )
610 loop
611 l_attribute_type := x.attribute_type;
612 l_display_style := x.display_style;
613 l_decimal_pts := x.decimal_points;
614 l_attribute_name:= x.name;
615 end loop;
616 */
617 if(l_decimal_pts is null ) then
618 l_decimal_pts :=2; -- hardcodeing decimal pts to 2
619 end if;
620 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
621 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' cursor c_get_attr_details closed');
622 END IF;
623
624 -- getting lov values table for dropdown attributes
625
626 IF(l_attribute_type = 'DROPDOWN') then
627 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
628 PVX_Utility_PVT.debug_message('Getting lov values table for LOV String: '||l_lov_string);
629 END IF;
630
631 IF (l_lov_string IS NOT NULL OR LENGTH(l_lov_string) <> 0) THEN
632 --replacing java bindings with pl/sql bindings.
633 BEGIN
634 OPEN lc_lov_cursor FOR replace(l_lov_string,'?',':1') using p_attribute_id;
635 LOOP
636 FETCH lc_lov_cursor INTO l_lov_data_rec;
637 EXIT WHEN lc_lov_cursor%NOTFOUND;
638 l_index := l_index +1;
639 l_lov_data_tbl(l_index) := l_lov_data_rec;
640 END LOOP;
641 CLOSE lc_lov_cursor;
642
643 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
644 PVX_Utility_PVT.debug_message('l_index:'||l_index||':');
645 END IF;
646
647 EXCEPTION
648 WHEN OTHERS THEN
649 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
650 FND_MESSAGE.set_name('PV', 'PV_LOV_EXECUTION_ERROR');
651 FND_MESSAGE.set_token('ATTRIBUTE_ID',p_attribute_id);
652 FND_MESSAGE.set_token('ATTRIBUTE_NAME',l_attribute_name);
653 FND_MESSAGE.set_token('ENTITY',p_entity);
654 FND_MSG_PUB.add;
655 END IF;
656 RAISE FND_API.G_EXC_ERROR;
657
658 end;
659
660 END IF;
661
662 end if;
663
664
665 begin --start block for (catching Numberexceptions
666 FOR l_curr_row IN 1..l_attr_val_tbl.count LOOP
667
668 l_attr_val_rec := l_attr_val_tbl(l_curr_row);
669
670 IF(l_attribute_type = 'DROPDOWN') then
671 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
672 PVX_Utility_PVT.debug_message('Performing all validatiosn for Dropdown attributes');
673 END IF;
674 --Checkign if Specified value(s) are in the look-up list of values
675 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
676 PVX_Utility_PVT.debug_message('Checkign if Specified value(s) are in the look-up list of values');
677 END IF;
678
679 IF (l_attr_val_tbl(l_curr_row).attr_value <> null and
680 l_attr_val_tbl(l_curr_row).attr_value <> '' and
681 MATCH_CODE_TO_VALUE(l_attr_val_tbl(l_curr_row).attr_value,l_lov_data_tbl) = '$$INVALID*$VALUE$$' )
682 THEN
683 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
684 FND_MESSAGE.set_name('PV', 'PV_ATTR_VALUE_LOV_ERROR');
685 FND_MESSAGE.set_token('ATTRIBUTE_ID',p_attribute_id);
686 FND_MESSAGE.set_token('ATTRIBUTE_NAME',l_attribute_name);
687 FND_MSG_PUB.add;
688 END IF;
689
690 RAISE FND_API.G_EXC_ERROR;
691 else
692 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
693 PVX_Utility_PVT.debug_message('Match Found');
694 END IF;
695
696 END IF;
697
698 IF( l_display_style= 'SINGLE' OR l_display_style= 'RADIO') THEN
699 IF(l_curr_row > 1) THEN
700
701 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
702 FND_MESSAGE.set_name('PV', 'PV_ATTR_ONLY_ONE_VALUE');
703 FND_MESSAGE.set_token('ATTRIBUTE_ID',p_attribute_id);
704 FND_MESSAGE.set_token('ATTRIBUTE_NAME',l_attribute_name);
705 FND_MSG_PUB.add;
706 END IF;
707
708 RAISE FND_API.G_EXC_ERROR;
709
710 END IF;
711
712 ELSIF( l_display_style= 'PERCENTAGE') THEN
713
714 IF( NOT is_number (l_attr_val_rec.attr_value_extn) ) THEN
715 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
716 FND_MESSAGE.set_name('PV', 'PV_ATTR_VALUE_NON_MUMERIC');
717 FND_MESSAGE.set_token('ATTRIBUTE_ID',p_attribute_id);
718 FND_MESSAGE.set_token('ATTRIBUTE_NAME',l_attribute_name);
719 FND_MSG_PUB.add;
720 END IF;
721
722 RAISE FND_API.G_EXC_ERROR;
723
724 END IF;
725
726 if(to_number(trim(l_attr_val_rec.attr_value_extn))<0) then
727
728 x_return_status := FND_API.g_ret_sts_error;
729 RAISE FND_API.G_EXC_ERROR;
730
731 elsif(to_number(trim(l_attr_val_rec.attr_value_extn))=0) then
732
733 l_attr_val_tbl.delete(l_curr_row);
734
735 else
736 l_attr_val_tbl(l_curr_row).attr_value_extn := ROUND(l_attr_val_tbl(l_curr_row).attr_value_extn,l_decimal_pts);
737
738 BEGIN
739 l_percentage_total := l_percentage_total + to_number(l_attr_val_tbl(l_curr_row).attr_value_extn);
740 EXCEPTION
741 WHEN OTHERS THEN
742 l_percentage_total := 0;
743 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
744 PVX_Utility_PVT.debug_message('Error in calculating percentage sum');
745 END IF;
746 END;
747
748 end if;
749
750 END IF;
751
752 ---- end of IF(l_attribute_type = 'DROPDOWN') then
753 ELSIF (l_attribute_type = 'TEXT') THEN
754
755 IF (l_display_style= 'NUMBER') THEN
756
757 IF( NOT is_number (l_attr_val_rec.attr_value) ) THEN
758 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
759 FND_MESSAGE.set_name('PV', 'PV_ATTR_VALUE_NON_NUMERIC');
760 FND_MESSAGE.set_token('ATTRIBUTE_ID',p_attribute_id);
761 FND_MESSAGE.set_token('ATTRIBUTE_NAME',l_attribute_name);
762 FND_MSG_PUB.add;
763 END IF;
764
765 RAISE FND_API.G_EXC_ERROR;
766 END IF;
767
768 l_attr_val_tbl(l_curr_row).attr_value := ROUND(l_attr_val_tbl(l_curr_row).attr_value,l_decimal_pts);
769 ELSIF (l_display_style= 'PERCENTAGE') THEN
770
771 IF( NOT is_number (l_attr_val_rec.attr_value) ) THEN
772 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
773 FND_MESSAGE.set_name('PV', 'PV_ATTR_VALUE_NON_NUMERIC');
774 FND_MESSAGE.set_token('ATTRIBUTE_ID',p_attribute_id);
775 FND_MESSAGE.set_token('ATTRIBUTE_NAME',l_attribute_name);
776 FND_MSG_PUB.add;
777 END IF;
778
779 RAISE FND_API.G_EXC_ERROR;
780 END IF;
781 ELSIF (l_display_style= 'STRING') THEN
782
783 IF( length(l_attr_val_rec.attr_value) > l_character_width) THEN
784 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
785 FND_MESSAGE.set_name('PV', 'PV_ATTR_VALUE_TEXT_LIMIT');
786 FND_MESSAGE.set_token('ATTRIBUTE_ID',p_attribute_id);
787 FND_MESSAGE.set_token('ATTRIBUTE_NAME',l_attribute_name);
788 FND_MSG_PUB.add;
789 END IF;
790
791 RAISE FND_API.G_EXC_ERROR;
792 END IF;
793
794 ELSIF (l_display_style= 'DATE') THEN
795
796 begin
797 l_date := to_date(l_attr_val_tbl(l_curr_row).attr_value,'yyyymmddhh24miss');
798 exception
799 when others then
800
801 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
802 FND_MESSAGE.set_name('PV', 'PV_ATTR_DATE_FORMAT_ERROR');
803 FND_MESSAGE.set_token('ATTRIBUTE_ID',p_attribute_id);
804 FND_MESSAGE.set_token('ATTRIBUTE_NAME',l_attribute_name);
805 FND_MSG_PUB.add;
806 END IF;
807
808 RAISE FND_API.G_EXC_ERROR;
809 end;
810
811
812 ELSIF (l_display_style= 'CURRENCY') THEN
813
814 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
815 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' CURRENCY' || sub_string(l_attr_val_rec.attr_value,':'));
816 END IF;
817
818 IF (--sub_string(l_attr_val_rec.attr_value,':') IS NULL OR
819 ( sub_string(l_attr_val_rec.attr_value,':') IS NOT NULL AND
820 NOT is_number (sub_string(l_attr_val_rec.attr_value,':'))
821 )
822 ) THEN
823
824 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
825 FND_MESSAGE.set_name('PV', 'PV_ATTR_VALUE_NON_NUMERIC');
826 FND_MESSAGE.set_token('ATTRIBUTE_ID',p_attribute_id);
827 FND_MESSAGE.set_token('ATTRIBUTE_NAME',l_attribute_name);
828 FND_MSG_PUB.add;
829 END IF;
830 RAISE FND_API.G_EXC_ERROR;
831
832
833 END IF;
834
835 IF(CHECK_CURRECY_FORMAT(l_attr_val_tbl(l_curr_row).attr_value) = 0) THEN
836 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
837 FND_MESSAGE.set_name('PV', 'PV_ATTR_CURR_FORMAT_ERROR');
838 FND_MESSAGE.set_token('ATTRIBUTE_ID',p_attribute_id);
839 FND_MESSAGE.set_token('ATTRIBUTE_NAME',l_attribute_name);
840 FND_MSG_PUB.add;
841 END IF;
842 RAISE FND_API.G_EXC_ERROR;
843 END IF;
844
845
846
847 l_attr_val_tbl(l_curr_row).attr_value :=
848 ROUND(
849 SUBSTR(
850 l_attr_val_tbl(l_curr_row).attr_value,
851 1,
852 INSTR(l_attr_val_tbl(l_curr_row).attr_value, ':', 1, 1)-1
853 )
854 ,l_decimal_pts
855 )
856
857 ||
858 SUBSTR(
859 l_attr_val_tbl(l_curr_row).attr_value,
860 INSTR(l_attr_val_tbl(l_curr_row).attr_value, ':', 1, 1)
861
862 )
863 ;
864 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
865 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' Val '|| l_attr_val_tbl(l_curr_row).attr_value);
866 END IF;
867 END IF;
868
869 END IF; -- end of ELSIF (l_attribute_type = 'TEXT') THEN
870 END LOOP;
871
872 --Check the percentage sume validation
873 IF (l_attribute_type='DROPDOWN' and
874 l_display_style= 'PERCENTAGE') THEN
875 BEGIN
876
877 l_perc_sum_profile_value:= to_number(nvl(fnd_profile.value('PV_ATTR_PERCENTAGE_TOTAL'), '100'));
878 EXCEPTION
879 WHEN OTHERS THEN
880 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
881 PVX_Utility_PVT.debug_message('Error in getting profile option value PV_ATTR_PERCENTAGE_TOTAL');
882 END IF;
883 l_perc_sum_profile_value:=100;
884 END;
885
886 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
887 PVX_Utility_PVT.debug_message('profile option PV_ATTR_PERCENTAGE_TOTAL value:'|| l_perc_sum_profile_value);
888 END IF;
889 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
890 PVX_Utility_PVT.debug_message('Sum of all Percentage attr values:'|| l_percentage_total);
891 END IF;
892
893 IF(l_percentage_total < 0 or l_percentage_total > l_perc_sum_profile_value) THEN
894
895 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
896 FND_MESSAGE.set_name('PV', 'PV_ATTR_PERCENT_SUM_ERROR');
897 FND_MESSAGE.set_token('TOTAL','' || l_perc_sum_profile_value);
898 FND_MESSAGE.set_token('ATTRIBUTE_ID',p_attribute_id);
899 FND_MESSAGE.set_token('ATTRIBUTE_NAME',l_attribute_name);
900 FND_MSG_PUB.add;
901 END IF;
902 RAISE FND_API.G_EXC_ERROR;
903
904 END IF;
905
906 END IF; -- end of IF (l_attribute_type='DROPDOWN' and l_display_style= 'CURRENCY') THEN
907
908 EXCEPTION
909 WHEN OTHERS THEN
910 ROLLBACK TO Upsert_Attr_Value_PUB;
911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
912 /*IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
913 THEN
914 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
915 END IF;
916 */
917 -- Standard call to get message count and if count=1, get the message
918 FND_MSG_PUB.Count_And_Get (
919 p_encoded => FND_API.G_FALSE
920 ,p_count => x_msg_count
921 ,p_data => x_msg_data
922 );
923 RAISE FND_API.G_EXC_ERROR;
924 End;
925
926 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
927 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' User Id:--' || fnd_global.user_id);
928 END IF;
929 -- calling notification API if validation rerquired for this attribute id
930 -- We are calling validation API only for attributes of type TEXT and DROPDOWN
931 -- We are skipping this call, if attribute is of type FUNCTION and not set validation flag
932
933 if(l_require_validation_flag='Y' and
934 l_attribute_type <> 'FUNCTION') then
935
936 PV_ATTR_VALIDATION_PUB.attribute_validate(
937 p_api_version_number => p_api_version_number
938 ,p_init_msg_list => p_init_msg_list
939 ,p_commit => p_commit
940 ,p_validation_level => p_validation_level
941
942 ,p_attribute_id => p_attribute_id
943 ,p_entity => p_entity
944 ,p_entity_id => p_entity_id
945 --p_resource_id IN VARCHAR2,
946 ,p_user_id => FND_GLOBAL.USER_ID
947
948 ,x_return_status => x_return_status
949 ,x_msg_count => x_msg_count
950 ,x_msg_data => x_msg_data
951 );
952
953
954 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
955 RAISE FND_API.G_EXC_ERROR;
956 END IF;
957
958 end if;
959
960
961 --end calling notification API
962
963
964 -- Debug Message
965 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
966 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - Calling Create Private API');
967 END IF;
968 --if there are no records in p_attr_val_tbl, that means we have to create adummy row
969 -- with higher version and latest_flag as 'Y' and attribute_value as null
970 --just for the sake of tracking history
971
972 IF (l_attr_val_tbl IS NULL OR
973 --p_attr_val_tbl = g_miss_attr_value_tbl OR
974 (l_attr_val_tbl IS NOT NULL AND l_attr_val_tbl.count = 0)
975 ) THEN
976
977 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
978 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - Value Table Null or Zero length');
979 END IF;
980 l_enty_attr_val_rec := PV_Enty_Attr_Value_PVT.g_miss_enty_attr_val_rec;
981
982 --initialising standard columns in the record
983 --l_enty_attr_val_rec.enty_attr_val_id := l_enty_attr_val_id;
984 /*l_enty_attr_val_rec.last_update_date := SYSDATE;
985 l_enty_attr_val_rec.last_updated_by := G_USER_ID;
986 l_enty_attr_val_rec.creation_date := SYSDATE;
987 l_enty_attr_val_rec.created_by := G_USER_ID;
988 l_enty_attr_val_rec.last_update_login := G_LOGIN_ID;
989 l_enty_attr_val_rec.object_version_number := l_object_version_number;
990 */
991 --initialising in parameters to record fields
992 l_enty_attr_val_rec.entity := p_entity;
993 l_enty_attr_val_rec.attribute_id := p_attribute_id;
994 --no party_id for record; not using thsi column
995
996 --CHECK FOR ATTR_VALUE NULL
997 l_enty_attr_val_rec.attr_value := null;
998 --no score
999 --no security_group_id
1000 l_enty_attr_val_rec.enabled_flag := 'Y';
1001 l_enty_attr_val_rec.entity_id := p_entity_id;
1002 --version need to be incremented by 1
1003 l_enty_attr_val_rec.version := p_version+1;
1004 l_enty_attr_val_rec.latest_flag := 'Y';
1005 l_enty_attr_val_rec.attr_value_extn :=null;
1006
1007
1008
1009 -- Invoke Private API(PV_ENTY_ATTR_VALUES_PVT.Create_Attr_Value
1010 PV_Enty_Attr_Value_PVT.Create_Attr_Value(
1011 p_api_version_number => p_api_version_number
1012 ,p_init_msg_list => p_init_msg_list
1013 ,p_commit => p_commit
1014 ,p_validation_level => p_validation_level
1015
1016 ,x_return_status => x_return_status
1017 ,x_msg_count => x_msg_count
1018 ,x_msg_data => x_msg_data
1019
1020 ,p_enty_attr_val_rec => l_enty_attr_val_rec
1021 ,x_enty_attr_val_id => x_enty_attr_val_id
1022 );
1023
1024
1025
1026 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1027 RAISE FND_API.G_EXC_ERROR;
1028 END IF;
1029
1030 ELSE -- there are records in l_attr_val_tbl
1031
1032
1033 -- User can not add multiple values for an attributes other than
1034 -- attribute type = Drop Down and style= Percentage, Multi-Select, check-Box, External_LOV
1035
1036 if(l_attr_val_tbl.count >= 2
1037 and not (l_attribute_type = 'DROPDOWN'
1038 and l_DISPLAY_STYLE in ('EXTERNAL_LOV','MULTI','CHECK','PERCENTAGE')
1039 )
1040 and not (l_attribute_type = 'FUNCTION')
1041
1042 ) then
1043 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1044 Fnd_Message.set_name('PV', 'PV_ENTY_ATTR_VAL_MULTI_ERROR');
1045
1046
1047 FOR x IN (select meaning from pv_lookups
1048 where lookup_type = 'PV_ATTRIBUTE_TYPE'
1049 and lookup_code = l_attribute_type
1050 ) LOOP
1051 l_meaning := x.meaning;
1052 END LOOP;
1053 Fnd_Message.set_token('ATTR_TYPE',l_meaning);
1054
1055 FOR x IN (select meaning from pv_lookups
1056 where lookup_type = 'PV_ATTR_DISPLAY_STYLE'
1057 and lookup_code = l_display_style
1058 ) LOOP
1059 l_meaning := x.meaning;
1060 END LOOP;
1061 Fnd_Message.set_token('ATTR_STYLE',l_meaning);
1062
1063
1064 Fnd_Msg_Pub.ADD;
1065 END IF;
1066 RAISE Fnd_Api.G_EXC_ERROR;
1067
1068
1069 end if;
1070
1071
1072 --FOR l_curr_row IN l_attr_val_tbl.first..l_attr_val_tbl.last LOOP
1073 l_curr_row1 := l_attr_val_tbl.first;
1074 WHILE l_curr_row1 <= l_attr_val_tbl.last LOOP
1075
1076 l_attr_val_rec := l_attr_val_tbl(l_curr_row1);
1077
1078 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1079 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' VAlue Table Not Null ');
1080 END IF;
1081 --initialise it to null
1082 l_enty_attr_val_rec := PV_Enty_Attr_Value_PVT.g_miss_enty_attr_val_rec;
1083
1084 --initialising standard columns in the record
1085 --l_enty_attr_val_rec.enty_attr_val_id := l_enty_attr_val_id;
1086 l_enty_attr_val_rec.last_update_date := SYSDATE;
1087 l_enty_attr_val_rec.last_updated_by := G_USER_ID;
1088 l_enty_attr_val_rec.creation_date := SYSDATE;
1089 l_enty_attr_val_rec.created_by := G_USER_ID;
1090 l_enty_attr_val_rec.last_update_login := G_LOGIN_ID;
1091 l_enty_attr_val_rec.object_version_number := l_object_version_number;
1092
1093 --initialising in parameters to record fields
1094 l_enty_attr_val_rec.entity := p_entity;
1095 l_enty_attr_val_rec.attribute_id := p_attribute_id;
1096 --no party_id for record; not using thsi column
1097
1098 --CHECK FOR ATTR_VALUE NULL
1099 l_enty_attr_val_rec.attr_value := l_attr_val_rec.attr_value;
1100 --no score
1101 --no security_group_id
1102 l_enty_attr_val_rec.enabled_flag := 'Y';
1103 l_enty_attr_val_rec.entity_id := p_entity_id;
1104 --version need to be incremented by 1
1105 l_enty_attr_val_rec.version := p_version+1;
1106 l_enty_attr_val_rec.latest_flag := 'Y';
1107 l_enty_attr_val_rec.attr_value_extn :=l_attr_val_rec.attr_value_extn;
1108
1109 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - after ');
1110
1111 -- Invoke Private API(PV_ENTY_ATTR_VALUES_PVT.Create_Attr_Value
1112 PV_Enty_Attr_Value_PVT.Create_Attr_Value(
1113 p_api_version_number => p_api_version_number
1114 ,p_init_msg_list => p_init_msg_list
1115 ,p_commit => p_commit
1116 ,p_validation_level => p_validation_level
1117
1118 ,x_return_status => x_return_status
1119 ,x_msg_count => x_msg_count
1120 ,x_msg_data => x_msg_data
1121
1122 ,p_enty_attr_val_rec => l_enty_attr_val_rec
1123 ,x_enty_attr_val_id => x_enty_attr_val_id
1124 );
1125
1126
1127
1128 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1129 RAISE FND_API.G_EXC_ERROR;
1130 END IF;
1131
1132 l_curr_row1:=l_attr_val_tbl.next(l_curr_row1);
1133
1134 END LOOP; --WHILE LOOP --FOR l_curr_row IN 1..l_attr_val_tbl.count LOOP
1135 END IF; -- end of IF (p_attr_val_tbl IS NULL OR
1136 END IF; -- end of main if block IF(l_attr_data_type in ('EXTERNAL','EXT_INT') ) THEN
1137 END IF;
1138 --
1139 -- End of API body
1140 --
1141
1142 -- Standard check for p_commit
1143 IF FND_API.to_Boolean( p_commit )
1144 THEN
1145 COMMIT WORK;
1146 END IF;
1147
1148
1149 -- Debug Message
1150 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1151 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - end');
1152 END IF;
1153
1154 -- Standard call to get message count and if count is 1, get message info.
1155 FND_MSG_PUB.Count_And_Get (
1156 p_count => x_msg_count
1157 ,p_data => x_msg_data
1158 );
1159
1160 EXCEPTION
1161 /*
1162 WHEN PVX_Utility_PVT.resource_locked THEN
1163 x_return_status := FND_API.g_ret_sts_error;
1164 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
1165 */
1166 WHEN FND_API.G_EXC_ERROR THEN
1167 ROLLBACK TO Upsert_Attr_Value_PUB;
1168 x_return_status := FND_API.G_RET_STS_ERROR;
1169 -- Standard call to get message count and if count=1, get the message
1170 FND_MSG_PUB.Count_And_Get (
1171 p_encoded => FND_API.G_FALSE
1172 ,p_count => x_msg_count
1173 ,p_data => x_msg_data
1174 );
1175
1176 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1177 ROLLBACK TO Upsert_Attr_Value_PUB;
1178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1179 -- Standard call to get message count and if count=1, get the message
1180 FND_MSG_PUB.Count_And_Get (
1181 p_encoded => FND_API.G_FALSE
1182 ,p_count => x_msg_count
1183 ,p_data => x_msg_data
1184 );
1185
1186 WHEN OTHERS THEN
1187 ROLLBACK TO Upsert_Attr_Value_PUB;
1188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1189 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1190 THEN
1191 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1192 END IF;
1193 -- Standard call to get message count and if count=1, get the message
1194 FND_MSG_PUB.Count_And_Get (
1195 p_encoded => FND_API.G_FALSE
1196 ,p_count => x_msg_count
1197 ,p_data => x_msg_data
1198 );
1199 End Upsert_Attr_Value;
1200
1201 FUNCTION is_number (stg_in IN VARCHAR2)
1202 RETURN BOOLEAN
1203 IS
1204 val NUMBER;
1205 BEGIN
1206 val := TO_NUMBER (stg_in);
1207 RETURN TRUE;
1208 EXCEPTION
1209 WHEN OTHERS THEN RETURN FALSE;
1210 END is_number;
1211
1212 FUNCTION CHECK_CURRECY_FORMAT(
1213 p_entity_attr_value IN VARCHAR2
1214 )
1215 RETURN NUMBER
1216 AS
1217 l_api_name VARCHAR2(30) := 'CHECK_CURRECY_FORMAT';
1218 l_entity_attr_value NUMBER;
1219 l_entity_currency_code VARCHAR2(10);
1220 l_currency_date DATE;
1221 l_num_of_tokens NUMBER;
1222
1223 BEGIN
1224 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1225 PVX_Utility_PVT.debug_message('Private API: '||l_api_name );
1226 END IF;
1227
1228 l_num_of_tokens := (LENGTH(p_entity_attr_value) -
1229 LENGTH(REPLACE(p_entity_attr_value, ':::', '')))
1230 /LENGTH(':::')
1231 + 1;
1232 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1233 PVX_Utility_PVT.debug_message('l_num_of_tokens: '||l_num_of_tokens);
1234 END IF;
1235
1236 IF (l_num_of_tokens <> 3) THEN
1237 return 0;
1238 END IF;
1239
1240 l_entity_attr_value := TO_NUMBER(PV_CHECK_MATCH_PUB.Retrieve_Token (
1241 p_delimiter => ':::',
1242 p_attr_value_string => p_entity_attr_value,
1243 p_input_type => 'IN TOKEN',
1244 p_index => 1
1245 ),
1246 '999999999999.99999999999999999999'
1247 );
1248
1249 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1250 PVX_Utility_PVT.debug_message('l_entity_attr_value: '||l_entity_attr_value);
1251 END IF;
1252
1253 l_entity_currency_code := PV_CHECK_MATCH_PUB.Retrieve_Token (
1254 p_delimiter => ':::',
1255 p_attr_value_string => p_entity_attr_value,
1256 p_input_type => 'IN TOKEN',
1257 p_index => 2
1258 );
1259 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1260 PVX_Utility_PVT.debug_message('l_entity_currency_code: '||l_entity_currency_code);
1261 END IF;
1262 IF (l_entity_currency_code IS NULL) THEN
1263 return 0;
1264 END IF;
1265
1266 l_currency_date := TO_DATE(PV_CHECK_MATCH_PUB.Retrieve_Token (
1267 p_delimiter => ':::',
1268 p_attr_value_string => p_entity_attr_value,
1269 p_input_type => 'IN TOKEN',
1270 p_index => 3
1271 ),
1272 'yyyymmddhh24miss');
1273 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1274 PVX_Utility_PVT.debug_message('l_currency_date: '||l_currency_date);
1275 END IF;
1276 IF (l_currency_date IS NULL) THEN
1277 return 0;
1278 END IF;
1279
1280 return 1;
1281
1282 EXCEPTION
1283 WHEN OTHERS THEN
1284 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1285 PVX_Utility_PVT.debug_message('Exception in checking the currency format ');
1286 END IF;
1287 return 0;
1288 END CHECK_CURRECY_FORMAT;
1289
1290
1291
1292
1293 FUNCTION MATCH_CODE_TO_VALUE ( code VARCHAR2,
1294 lov_tbl PV_ATTRIBUTE_UTIL.lov_data_tbl_type
1295 )
1296 RETURN VARCHAR2
1297 AS
1298
1299 BEGIN
1300 FOR i in 1..lov_tbl.count LOOP
1301
1302 if(rtrim(lov_tbl(i).code) = rtrim(code)) then
1303 return rtrim(lov_tbl(i).meaning);
1304 end if;
1305
1306 END LOOP;
1307
1308 --if(rtrim(code) = '' or code = null) then
1309 -- return '';
1310 --else
1311 return '$$INVALID*$VALUE$$';
1312 --end if;
1313
1314 END MATCH_CODE_TO_VALUE;
1315
1316
1317 FUNCTION sub_string (string_in IN VARCHAR2 , upto_char IN VARCHAR2)
1318 RETURN VARCHAR2
1319 IS
1320 string_out VARCHAR2(100);
1321
1322
1323 WORD_LENGTH number;
1324 CHAR_POS number;
1325 BEGIN
1326 if(string_in is null) then
1327 return string_in;
1328 end if;
1329
1330 WORD_LENGTH := LENGTH(string_in);
1331 for CHAR_POS in 1..WORD_LENGTH loop
1332 if(SUBSTR(string_in, CHAR_POS, 1) = upto_char) THEN
1333 return string_out;
1334 else
1335 string_out := string_out || SUBSTR(string_in, CHAR_POS, 1);
1336 end if;
1337 end loop;
1338 return string_out;
1339 END sub_string;
1340
1341
1342 PROCEDURE Copy_Partner_Attr_Values(
1343 p_api_version_number IN NUMBER
1344 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1345 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1346 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1347
1348 ,x_return_status OUT NOCOPY VARCHAR2
1349 ,x_msg_count OUT NOCOPY NUMBER
1350 ,x_msg_data OUT NOCOPY VARCHAR2
1351
1352 ,p_attr_id_tbl IN NUMBER_TABLE
1353 ,p_entity IN VARCHAR2
1354 ,p_entity_id IN NUMBER
1355 ,p_partner_id IN NUMBER
1356
1357 )
1358
1359 IS
1360 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Partner_Attr_Values';
1361 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1362 l_api_version_number CONSTANT NUMBER := 1.0;
1363 l_object_version_number NUMBER := 1;
1364
1365 l_attr_count NUMBER;
1366 l_attr_data_type VARCHAR2(30);
1367 l_version NUMBER;
1368
1369 l_attr_val_tbl attr_value_tbl_type := g_miss_attr_value_tbl;
1370 l_sql_text VARCHAR2(2000);
1371 l_index NUMBER;
1372 l_attr_value VARCHAR2(2000);
1373
1374 type cur_type IS REF CURSOR;
1375 lc_ext_cursor cur_type;
1376
1377 CURSOR c_attr_data_type(cv_attribute_id NUMBER, cv_entity_id NUMBER) IS
1378 SELECT distinct enty.attr_data_type, ENTY.SQL_TEXT -- VAL.version,
1379 FROM PV_ATTRIBUTES_VL ATTR, PV_ENTITY_ATTRS ENTY, PV_ENTY_ATTR_VALUES VAL
1380 WHERE ATTR.attribute_id = cv_attribute_id AND
1381 ENTY.ATTRIBUTE_ID = ATTR.attribute_id AND
1382 ENTY.ENTITY = 'PARTNER' AND
1383 VAL.ATTRIBUTE_ID (+) = ENTY.ATTRIBUTE_ID AND
1384 VAL.ENTITY (+) = ENTY.ENTITY AND
1385 VAL.ENTITY_ID (+) = cv_entity_id
1386 ;
1387
1388 CURSOR c_enty_attr_int_values(cv_attribute_id NUMBER, cv_entity_id NUMBER) IS
1389
1390 select enty.attr_value, enty.Attr_Value_Extn
1391 from pv_enty_attr_values enty
1392 where enty.attribute_id = cv_attribute_id
1393 and enty.entity = 'PARTNER' and
1394 enty.entity_id = cv_entity_id AND
1395 enty.LATEST_FLAG = 'Y' AND
1396 enty.ATTR_VALUE is not null
1397 ;
1398
1399 CURSOR c_get_previous_version(cv_attribute_id NUMBER,cv_entity_id NUMBER, cv_entity VARCHAR2) IS
1400 SELECT distinct version
1401 FROM PV_ENTY_ATTR_VALUES
1402 WHERE attribute_id = cv_attribute_id and
1403 entity_id = cv_entity_id and
1404 entity = cv_entity and
1405 latest_flag = 'Y';
1406
1407 BEGIN
1408 -- Standard Start of API savepoint
1409 SAVEPOINT Copy_Partner_Attr_Values_PUB;
1410
1411 -- Standard call to check for call compatibility.
1412 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1413 p_api_version_number,
1414 l_api_name,
1415 G_PKG_NAME)
1416 THEN
1417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1418 END IF;
1419
1420 -- Initialize message list if p_init_msg_list is set to TRUE.
1421 IF FND_API.to_Boolean( p_init_msg_list )
1422 THEN
1423 FND_MSG_PUB.initialize;
1424 END IF;
1425
1426 -- Debug Message
1427 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1428 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - start');
1429 END IF;
1430
1431
1432 -- Initialize API return status to SUCCESS
1433 x_return_status := FND_API.G_RET_STS_SUCCESS;
1434 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1435 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1436 END IF;
1437
1438 --check for required columns
1439
1440 IF p_attr_id_tbl IS NULL THEN
1441 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1442 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1443 FND_MESSAGE.set_token('COLUMN','p_attr_id_tbl');
1444 FND_MSG_PUB.add;
1445 END IF;
1446 x_return_status := FND_API.g_ret_sts_error;
1447 RETURN;
1448 END IF;
1449 IF p_entity_id IS NULL THEN
1450 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1451 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1452 FND_MESSAGE.set_token('COLUMN','p_entity_id');
1453 FND_MSG_PUB.add;
1454 END IF;
1455 x_return_status := FND_API.g_ret_sts_error;
1456 RETURN;
1457 END IF;
1458 IF p_entity IS NULL THEN
1459 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1460 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1461 FND_MESSAGE.set_token('COLUMN','p_entity');
1462 FND_MSG_PUB.add;
1463 END IF;
1464 x_return_status := FND_API.g_ret_sts_error;
1465 RETURN;
1466 END IF;
1467 IF p_partner_id IS NULL THEN
1468 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1469 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1470 FND_MESSAGE.set_token('COLUMN','p_partner_id');
1471 FND_MSG_PUB.add;
1472 END IF;
1473 x_return_status := FND_API.g_ret_sts_error;
1474 RETURN;
1475 END IF;
1476
1477
1478 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
1479 THEN
1480 -- Debug message
1481 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1482 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' - Validate_attr_value');
1483 END IF;
1484
1485 --since validation procedures will be done in record level in private API, No need to do any thing here
1486 --all validations would be done in inner API calls
1487
1488 END IF;
1489
1490 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1491 RAISE FND_API.G_EXC_ERROR;
1492 END IF;
1493
1494
1495 -- Debug Message
1496 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1497 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - Before copying......');
1498 END IF;
1499 --if there are no records in p_attr_val_tbl, that means we have to create adummy row
1500 -- with higher version and latest_flag as 'Y' and attribute_value as null
1501 --just for the sake of tracking history
1502
1503 IF (p_attr_id_tbl is not null and p_attr_id_tbl.count > 0 ) THEN
1504
1505 l_attr_count := 1;
1506 LOOP
1507
1508 EXIT WHEN l_attr_count = p_attr_id_tbl.count+1;
1509 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1510 PVX_Utility_PVT.debug_message('Public API:'||l_full_name||' attr id: ' || p_attr_id_tbl(l_attr_count) );
1511 END IF;
1512
1513 for x in c_get_previous_version(cv_attribute_id => p_attr_id_tbl(l_attr_count),
1514 cv_entity_id => p_entity_id,
1515 cv_entity => p_entity
1516 )
1517 loop
1518 l_version := x.version;
1519 end loop;
1520
1521 if(l_version is null) then
1522 l_version :=0;
1523 end if;
1524
1525 for x in c_attr_data_type(cv_attribute_id => p_attr_id_tbl(l_attr_count),
1526 cv_entity_id => p_partner_id)
1527 loop
1528 l_attr_data_type := x.attr_data_type;
1529 --l_version := x.version;
1530 l_sql_text := x.sql_text;
1531 end loop;
1532
1533
1534 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1535 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||'l_version: '||l_version ||
1536 'l_attr_data_type: ' || l_attr_data_type);
1537 END IF;
1538
1539
1540 if(l_attr_data_type is null) then
1541 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1542 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - No Attribute Type entities');
1543 END IF;
1544 elsif(l_attr_data_type = 'INTERNAL' or l_attr_data_type = 'INT_EXT') then
1545 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1546 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - Internal sql_text entities');
1547 END IF;
1548 l_index :=1;
1549 l_attr_val_tbl := g_miss_attr_value_tbl;
1550
1551 for x in c_enty_attr_int_values(cv_attribute_id => p_attr_id_tbl(l_attr_count),
1552 cv_entity_id => p_partner_id)
1553 loop
1554
1555 --Attr_Value_Extn
1556 --attr_value
1557 l_attr_val_tbl(l_index).attr_value := x.attr_value;
1558 l_attr_val_tbl(l_index).attr_value_extn := x.Attr_Value_Extn;
1559
1560 l_index := l_index +1;
1561 end loop;
1562
1563 if(l_attr_val_tbl is not null) then
1564 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1565 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - inserting Internal ...');
1566 END IF;
1567 Upsert_Attr_Value(
1568 p_api_version_number => p_api_version_number
1569 ,p_init_msg_list => p_init_msg_list
1570 ,p_commit => p_commit
1571 ,p_validation_level => p_validation_level
1572
1573 ,x_return_status => x_return_status
1574 ,x_msg_count => x_msg_count
1575 ,x_msg_data => x_msg_data
1576
1577 ,p_attribute_id => p_attr_id_tbl(l_attr_count)
1578 ,p_entity => p_entity
1579 ,p_entity_id => p_entity_id
1580 ,p_version => l_version
1581 ,p_attr_val_tbl => l_attr_val_tbl
1582 );
1583 end if;
1584 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1585 RAISE FND_API.G_EXC_ERROR;
1586 END IF;
1587
1588 elsif(l_attr_data_type = 'EXTERNAL' or l_attr_data_type = 'EXT_INT') then
1589 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1590 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - External sql_text entities');
1591 END IF;
1592 l_index :=1;
1593 l_attr_val_tbl := g_miss_attr_value_tbl;
1594
1595 IF (l_sql_text IS NOT NULL OR LENGTH(l_sql_text) <> 0) THEN
1596 OPEN lc_ext_cursor FOR l_sql_text USING p_attr_id_tbl(l_attr_count),p_entity, p_entity_id;
1597 LOOP
1598 FETCH lc_ext_cursor INTO l_attr_value;
1599 EXIT WHEN lc_ext_cursor%NOTFOUND;
1600 l_attr_val_tbl(l_index).attr_value := l_attr_value;
1601 l_attr_val_tbl(l_index).attr_value_extn := null;
1602
1603 l_index := l_index +1;
1604
1605 END LOOP;
1606 CLOSE lc_ext_cursor;
1607 END IF;
1608
1609 if(l_attr_val_tbl is not null) then
1610 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1611 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - inserting External ...');
1612 END IF;
1613 Upsert_Attr_Value(
1614 p_api_version_number => p_api_version_number
1615 ,p_init_msg_list => p_init_msg_list
1616 ,p_commit => p_commit
1617 ,p_validation_level => p_validation_level
1618
1619 ,x_return_status => x_return_status
1620 ,x_msg_count => x_msg_count
1621 ,x_msg_data => x_msg_data
1622
1623 ,p_attribute_id => p_attr_id_tbl(l_attr_count)
1624 ,p_entity => p_entity
1625 ,p_entity_id => p_entity_id
1626 ,p_version => l_version
1627 ,p_attr_val_tbl => l_attr_val_tbl
1628 );
1629 end if;
1630 else
1631 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1632 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - In Else Block');
1633 END IF;
1634
1635 END IF;
1636
1637 l_attr_count := l_attr_count + 1;
1638
1639
1640 END LOOP; -- end of main loop
1641
1642 END IF; -- end of IF (p_attr_id_tbl is not null and p_attr_id_tbl.count > 0 ) THEN
1643
1644
1645 --
1646 -- End of API body
1647 --
1648
1649 -- Standard check for p_commit
1650 IF FND_API.to_Boolean( p_commit )
1651 THEN
1652 COMMIT WORK;
1653 END IF;
1654
1655
1656 -- Debug Message
1657 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1658 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - end');
1659 END IF;
1660
1661 -- Standard call to get message count and if count is 1, get message info.
1662 FND_MSG_PUB.Count_And_Get (
1663 p_count => x_msg_count
1664 ,p_data => x_msg_data
1665 );
1666
1667 EXCEPTION
1668
1669 WHEN FND_API.G_EXC_ERROR THEN
1670 ROLLBACK TO Copy_Partner_Attr_Values_PUB;
1671 x_return_status := FND_API.G_RET_STS_ERROR;
1672 -- Standard call to get message count and if count=1, get the message
1673 FND_MSG_PUB.Count_And_Get (
1674 p_encoded => FND_API.G_FALSE
1675 ,p_count => x_msg_count
1676 ,p_data => x_msg_data
1677 );
1678
1679 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1680 ROLLBACK TO Copy_Partner_Attr_Values_PUB;
1681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1682 -- Standard call to get message count and if count=1, get the message
1683 FND_MSG_PUB.Count_And_Get (
1684 p_encoded => FND_API.G_FALSE
1685 ,p_count => x_msg_count
1686 ,p_data => x_msg_data
1687 );
1688
1689 WHEN OTHERS THEN
1690 ROLLBACK TO Copy_Partner_Attr_Values_PUB;
1691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1692 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1693 THEN
1694 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1695 END IF;
1696 -- Standard call to get message count and if count=1, get the message
1697 FND_MSG_PUB.Count_And_Get (
1698 p_encoded => FND_API.G_FALSE
1699 ,p_count => x_msg_count
1700 ,p_data => x_msg_data
1701 );
1702 End Copy_Partner_Attr_Values;
1703
1704 PROCEDURE Upsert_Partner_Types (
1705 p_api_version_number IN NUMBER
1706 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1707 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1708 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1709 ,x_return_status OUT NOCOPY VARCHAR2
1710 ,x_msg_count OUT NOCOPY NUMBER
1711 ,x_msg_data OUT NOCOPY VARCHAR2
1712 ,p_entity_id IN NUMBER
1713 ,p_version IN NUMBER :=0
1714 ,p_attr_val_tbl IN attr_value_tbl_type := g_miss_attr_value_tbl
1715 )
1716 IS
1717 l_api_name CONSTANT VARCHAR2(30) := 'Upsert_Partner_Types';
1718 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1719 l_api_version_number CONSTANT NUMBER := 1.0;
1720 l_object_version_number NUMBER := 1;
1721
1722 l_attr_val_tbl attr_value_tbl_type := p_attr_val_tbl;
1723 l_attr_val_rec attr_val_rec_type := g_miss_attr_val_rec;
1724
1725
1726 -- validation variables
1727 l_is_primary_partner_type VARCHAR2(1) ;
1728 --initially it would be null, as soon as it finds primary partner type, it becomes 'Y'
1729 --l_is_primary_partner_type is N --> throw error
1730 l_are_same_partner_types VARCHAR2(1) := 'N';
1731 --l_are_same_partner_types is Y --> throw error
1732
1733 l_attr_value_extn VARCHAR2(30);
1734 l_attr_value VARCHAR2(500);
1735 l_primary_partner_type VARCHAR2(500);
1736 -- end of validation variables
1737 l_attr_values_table JTF_VARCHAR2_TABLE_500;
1738 l_index number := 1;
1739
1740 BEGIN
1741
1742 -- Standard Start of API savepoint
1743 SAVEPOINT Upsert_Partner_Types_pub;
1744
1745 -- Standard call to check for call compatibility.
1746 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1747 p_api_version_number,
1748 l_api_name,
1749 G_PKG_NAME)
1750 THEN
1751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1752 END IF;
1753
1754 -- Initialize message list if p_init_msg_list is set to TRUE.
1755 IF FND_API.to_Boolean( p_init_msg_list )
1756 THEN
1757 FND_MSG_PUB.initialize;
1758 END IF;
1759
1760 -- Debug Message
1761 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1762 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - start');
1763 END IF;
1764
1765
1766 -- Initialize API return status to SUCCESS
1767 x_return_status := FND_API.G_RET_STS_SUCCESS;
1768 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1769 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1770 END IF;
1771
1772 --check for required columns
1773
1774 IF p_entity_id IS NULL THEN
1775 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1776 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1777 FND_MESSAGE.set_token('COLUMN','p_entity_id');
1778 FND_MSG_PUB.add;
1779 END IF;
1780 x_return_status := FND_API.g_ret_sts_error;
1781 RETURN;
1782 END IF;
1783 IF p_version IS NULL THEN
1784 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1785 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1786 FND_MESSAGE.set_token('COLUMN','p_version');
1787 FND_MSG_PUB.add;
1788 END IF;
1789 x_return_status := FND_API.g_ret_sts_error;
1790 RETURN;
1791 END IF;
1792
1793 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
1794 THEN
1795 -- Debug message
1796 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1797 PVX_Utility_PVT.debug_message('public API: '||l_full_name||' - Validate_attr_value');
1798 END IF;
1799
1800 --since validation procedures will be done in record level in private API,
1801 --No need to do any thing here
1802 --all validations would be done in inner API calls
1803
1804 END IF;
1805
1806 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1807 RAISE FND_API.G_EXC_ERROR;
1808 END IF;
1809
1810
1811 -- Debug Message
1812 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1813 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - Before Checking validations......');
1814 END IF;
1815
1816 -- At least one partner type should be there as partner type is mandatory
1817 -- If table is null, error should be thrown.
1818
1819 IF ( l_attr_val_tbl IS NULL OR
1820 (l_attr_val_tbl IS NOT NULL AND l_attr_val_tbl.count = 0)
1821 )
1822 THEN
1823 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1824 FND_MESSAGE.set_name('PV', 'PV_ATLEASTONE_PRTNR_TYPE');
1825 --FND_MESSAGE.set_token('COLUMN','p_attr_val_tbl');
1826 FND_MSG_PUB.add;
1827 END IF;
1828 x_return_status := FND_API.g_ret_sts_error;
1829 RETURN;
1830 END IF;
1831
1832 --Only one partner type should be there
1833
1834 IF ( l_attr_val_tbl IS NULL OR
1835 (l_attr_val_tbl IS NOT NULL AND l_attr_val_tbl.count >1 )
1836 )
1837 THEN
1838 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1839 FND_MESSAGE.set_name('PV', 'PV_ONLYONE_PRTNR_TYPE');
1840 --FND_MESSAGE.set_token('COLUMN','p_attr_val_tbl');
1841 FND_MSG_PUB.add;
1842 END IF;
1843 x_return_status := FND_API.g_ret_sts_error;
1844 RETURN;
1845 END IF;
1846
1847 --Checking if the partner type attribute value extension is Y or not.
1848
1849 FOR l_curr_row IN l_attr_val_tbl.first..l_attr_val_tbl.last LOOP
1850 l_attr_val_rec := l_attr_val_tbl(l_curr_row);
1851 l_attr_value := l_attr_val_rec.attr_value ;
1852 l_attr_value_extn := l_attr_val_rec.attr_value_extn;
1853
1854 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1855 PVX_Utility_PVT.debug_message('Attr Value Rec: attr_value:' || l_attr_value || '::::attr _value_extn:' || l_attr_value_extn );
1856 END IF;
1857
1858 IF(l_attr_value_extn is not null and
1859 l_attr_value_extn = 'Y' )
1860 THEN
1861 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1862 PVX_Utility_PVT.debug_message('Primary Partner Type found');
1863 END IF;
1864 l_is_primary_partner_type := 'Y';
1865
1866 else --if (l_attr_value_extn is null) then
1867 l_is_primary_partner_type := 'N';
1868 END IF;
1869 END LOOP; --FOR l_curr_row IN 1..l_attr_val_tbl.count LOOP
1870
1871 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1872 PVX_Utility_PVT.debug_message('l_is_primary_partner_type:' || l_is_primary_partner_type);
1873 END IF;
1874
1875 --Checkif primary partner type exists
1876 IF( l_is_primary_partner_type is null or
1877 (l_is_primary_partner_type is not null and l_is_primary_partner_type <> 'Y')
1878 ) then
1879 --Throw error no primary partner types is there
1880 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1881 FND_MESSAGE.set_name('PV', 'PV_ONLYONE_PRTNR_TYPE');
1882 FND_MSG_PUB.add;
1883 END IF;
1884 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1885 END IF;
1886
1887
1888 /*
1889 --initialising l_attr_values_table
1890 l_attr_values_table := JTF_VARCHAR2_TABLE_500();
1891 FOR l_curr_row IN l_attr_val_tbl.first..l_attr_val_tbl.last LOOP
1892 l_attr_val_rec := l_attr_val_tbl(l_curr_row);
1893 l_attr_value := l_attr_val_rec.attr_value ;
1894 l_attr_value_extn := l_attr_val_rec.attr_value_extn;
1895
1896 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1897 PVX_Utility_PVT.debug_message('Attr Value Rec: attr_value:' || l_attr_value || '::::attr _value_extn:' || l_attr_value_extn );
1898 END IF;
1899
1900 IF(l_attr_value_extn is not null and
1901 l_attr_value_extn = 'Y' )
1902 THEN
1903 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1904 PVX_Utility_PVT.debug_message('Primary Partner Type found');
1905 END IF;
1906
1907 if(l_is_primary_partner_type is null) then
1908 l_is_primary_partner_type := 'Y';
1909 l_primary_partner_type := l_attr_value;
1910 elsif(l_is_primary_partner_type = 'Y' ) then
1911 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1912 PVX_Utility_PVT.debug_message('duplicate primary partner type found');
1913 END IF;
1914 --Throw error multiple primary partner types can not be there
1915 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1916 FND_MESSAGE.set_name('PV', 'PV_NO_MULTI_PRIM_PRTNR_TYPES');
1917 FND_MSG_PUB.add;
1918 END IF;
1919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1920 end if;
1921 else --if (l_attr_value_extn is null) then
1922 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1923 PVX_Utility_PVT.debug_message('inserting it as additional partner type:'|| l_attr_value);
1924 END IF;
1925 l_attr_values_table.extend;
1926 l_attr_values_table(l_index) := l_attr_value;
1927 l_index := l_index + 1;
1928 END IF;
1929
1930
1931 END LOOP; --FOR l_curr_row IN 1..l_attr_val_tbl.count LOOP
1932
1933 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1934 PVX_Utility_PVT.debug_message('l_is_primary_partner_type:' || l_is_primary_partner_type);
1935 END IF;
1936
1937 --Checkif primary partner type exists
1938 IF( l_is_primary_partner_type is null or
1939 (l_is_primary_partner_type is not null and l_is_primary_partner_type <> 'Y')
1940 ) then
1941 --Throw error no primary partner types is there
1942 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1943 FND_MESSAGE.set_name('PV', 'PV_NO_PRIM_PRTNR_TYPES');
1944 FND_MSG_PUB.add;
1945 END IF;
1946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1947 END IF;
1948
1949
1950 --Checking if any of the additional partner types is primary partner type
1951 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1952 PVX_Utility_PVT.debug_message('Checking if any of the additional partner types is primary partner type');
1953 END IF;
1954
1955
1956 FOR i in 1 .. l_attr_values_table.count LOOP
1957 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1958 PVX_Utility_PVT.debug_message('l_attr_values_table('|| i ||'):' || l_attr_values_table(i) );
1959 END IF;
1960 if (l_primary_partner_type = l_attr_values_table(i) ) then
1961 --Throw error ::
1962 --The Additional Partner Type selected has already been selected as Primary Partner Type.
1963 --Please select different partner type
1964 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1965 PVX_Utility_PVT.debug_message('One of the the additional partner types is primary partner type');
1966 END IF;
1967
1968 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1969 FND_MESSAGE.set_name('PV', 'PV_INVALID_ADDTNL_PRTNR_TYPES');
1970 --FND_MESSAGE.set_token('COLUMN','p_attr_val_tbl');
1971 FND_MSG_PUB.add;
1972 END IF;
1973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1974 end if;
1975
1976 END LOOP;
1977 */
1978
1979 --No errors
1980 --Call Upsert API now
1981
1982 Upsert_Attr_Value(
1983 p_api_version_number => p_api_version_number
1984 ,p_init_msg_list => p_init_msg_list
1985 ,p_commit => p_commit
1986 ,p_validation_level => p_validation_level
1987
1988 ,x_return_status => x_return_status
1989 ,x_msg_count => x_msg_count
1990 ,x_msg_data => x_msg_data
1991
1992 ,p_attribute_id => 3
1993 ,p_entity => 'PARTNER'
1994 ,p_entity_id => p_entity_id
1995 ,p_version => p_version
1996 ,p_attr_val_tbl => l_attr_val_tbl
1997 );
1998
1999
2000 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2001 RAISE FND_API.G_EXC_ERROR;
2002 END IF;
2003
2004
2005 -- End of API body
2006 -- Standard check for p_commit
2007
2008 IF FND_API.to_Boolean( p_commit )
2009 THEN
2010 COMMIT WORK;
2011 END IF;
2012
2013 -- Debug Message
2014 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
2015 PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - end');
2016 END IF;
2017
2018 -- Standard call to get message count and if count is 1, get message info.
2019 FND_MSG_PUB.Count_And_Get (
2020 p_count => x_msg_count
2021 ,p_data => x_msg_data
2022 );
2023
2024 EXCEPTION
2025
2026 WHEN FND_API.G_EXC_ERROR THEN
2027 ROLLBACK TO Upsert_Partner_Types_pub;
2028 x_return_status := FND_API.G_RET_STS_ERROR;
2029 -- Standard call to get message count and if count=1, get the message
2030 FND_MSG_PUB.Count_And_Get (
2031 p_encoded => FND_API.G_FALSE
2032 ,p_count => x_msg_count
2033 ,p_data => x_msg_data
2034 );
2035
2036 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2037 ROLLBACK TO Upsert_Partner_Types_pub;
2038 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2039 -- Standard call to get message count and if count=1, get the message
2040 FND_MSG_PUB.Count_And_Get (
2041 p_encoded => FND_API.G_FALSE
2042 ,p_count => x_msg_count
2043 ,p_data => x_msg_data
2044 );
2045
2046 WHEN OTHERS THEN
2047 ROLLBACK TO Upsert_Partner_Types_pub;
2048 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2049 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2050 THEN
2051 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2052 END IF;
2053 -- Standard call to get message count and if count=1, get the message
2054 FND_MSG_PUB.Count_And_Get (
2055 p_encoded => FND_API.G_FALSE
2056 ,p_count => x_msg_count
2057 ,p_data => x_msg_data
2058 );
2059 End Upsert_Partner_Types;
2060
2061
2062
2063 END PV_ENTY_ATTR_VALUE_PUB;
[Home] [Help]