[Home] [Help]
PACKAGE BODY: APPS.IEX_SCORE_PVT
Source
1 PACKAGE BODY IEX_SCORE_PVT AS
2 /* $Header: iexvscrb.pls 120.9 2006/05/30 21:16:18 scherkas ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_SCORE_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvscrb.pls';
6
7 --bug 2902302 clchang updated 04/14/2003
8 --G_MIN_SCORE CONSTANT NUMBER := 10;
9 --G_MAX_SCORE CONSTANT NUMBER := 100;
10
11 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
12 PG_DEBUG NUMBER ;
13
14 Procedure Validate_Score(P_Init_Msg_List IN VARCHAR2 ,
15 P_Score_rec IN IEX_SCORE_PUB.SCORE_REC_TYPE,
16 X_Dup_Status OUT NOCOPY VARCHAR2,
17 X_Return_Status OUT NOCOPY VARCHAR2,
18 X_Msg_Count OUT NOCOPY NUMBER,
19 X_Msg_Data OUT NOCOPY VARCHAR2)
20 IS
21 l_score_rec IEX_SCORE_PUB.SCORE_REC_TYPE ;
22
23 BEGIN
24 l_score_rec := p_score_rec;
25
26 -- Initialize message list IF p_init_msg_list is set to TRUE.
27 IF FND_API.to_Boolean( p_init_msg_list )
28 THEN
29 FND_MSG_PUB.initialize;
30 END IF;
31 IEX_UTILITIES.VALIDATE_ANY_ID(P_COL_ID => l_score_rec.score_id,
32 P_COL_NAME => 'SCORE_ID',
33 P_TABLE_NAME => 'IEX_SCORES',
34 X_Return_Status => x_return_status,
35 X_Msg_Count => x_msg_count,
36 X_Msg_Data => x_msg_data,
37 P_Init_Msg_List => FND_API.G_FALSE);
38
39 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
40 RAISE FND_API.G_EXC_ERROR;
41 END IF;
42
43 Validate_Score_ID_Name(P_Init_Msg_List => FND_API.G_FALSE,
44 P_SCORE_ID => l_score_rec.score_id,
45 P_SCORE_Name => l_score_rec.score_Name,
46 X_Dup_Status => x_dup_status,
47 X_Return_Status => x_return_status,
48 X_Msg_Count => x_msg_count,
49 X_Msg_Data => x_msg_data);
50
51 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
52 RAISE FND_API.G_EXC_ERROR;
53 END IF;
54 END Validate_Score;
55
56
57
58 /*====================================================
59 * clchang updated 10/17/2003
60 * added P_SCORE_ID, and updated CURSOR C_GET_SCORE.
61 * This procedure will validate
62 * if any dup score name existing other than
63 * the score_name of the P_SCORE_ID.
64 *======================================================================*/
65
66 Procedure Validate_SCORE_Name(P_Init_Msg_List IN VARCHAR2 ,
67 P_Score_Name IN VARCHAR2 ,
68 P_Score_Id IN NUMBER ,
69 X_Dup_Status OUT NOCOPY VARCHAR2,
70 X_Return_Status OUT NOCOPY VARCHAR2,
71 X_Msg_Count OUT NOCOPY NUMBER,
72 X_Msg_Data OUT NOCOPY VARCHAR2)
73 IS
74 CURSOR C_GET_SCORE_name (IN_SCORE_Name VARCHAR2, IN_SCORE_ID NUMBER) IS
75 SELECT score_Name
76 FROM iex_scores
77 WHERE SCORE_Name = IN_SCORE_Name
78 AND SCORE_ID <> IN_SCORE_ID;
79 --
80 l_score_Name VARCHAR2(256);
81 l_msg VARCHAR2(100) ;
82
83 BEGIN
84 l_msg := 'iexvscrb:ValidateScrName:';
85
86 WriteLog(l_msg || 'Start');
87
88 -- Initialize message list IF p_init_msg_list is set to TRUE.
89 IF FND_API.to_Boolean( p_init_msg_list )
90 THEN
91 FND_MSG_PUB.initialize;
92 END IF;
93
94 -- Initialize API return status to SUCCESS
95 x_return_status := FND_API.G_RET_STS_SUCCESS;
96
97 WriteLog(l_msg || 'p_score_name='||p_score_name);
98 WriteLog(l_msg || 'p_score_id='||p_score_id);
99
100
101 IF P_SCORE_Name is NULL
102 or P_SCORE_Name = FND_API.G_FALSE
103 THEN
104 WriteLog(l_msg || ' no score name');
105 FND_MESSAGE.Set_Name('IEX', 'IEX_API_ALL_MISSING_PARAM');
106 FND_MESSAGE.Set_Token('API_NAME', 'Validate_SCORE_Name', FALSE);
107 FND_MESSAGE.Set_Token('MISSING_PARAM', 'SCORE_NAME', FALSE);
108 FND_MSG_PUB.Add;
109 x_return_status := FND_API.G_RET_STS_ERROR;
110
111 ELSE -- IF P_SCORE_Name is NULL or P_SCORE_Name = FND_API.G_FALSE
112
113 OPEN C_Get_Score_Name (p_score_Name, p_score_id);
114 FETCH C_Get_Score_Name INTO l_score_Name;
115
116 IF (C_Get_Score_Name%FOUND)
117 THEN
118 WriteLog(l_msg || ' got dup score name');
119 --IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
120 --THEN
121 FND_MESSAGE.Set_Name('IEX', 'IEX_API_DUPLICATE_NAME');
122 FND_MESSAGE.Set_Token('COLUMN', 'SCORE_NAME', FALSE);
123 FND_MESSAGE.Set_Token('VALUE', p_score_Name, FALSE);
124 FND_MSG_PUB.Add;
125 --END IF;
126 x_return_status := FND_API.G_RET_STS_ERROR;
127 x_dup_status := IEX_DUPLICATE_NAME;
128 END IF;
129 CLOSE C_GET_SCORE_Name;
130 END IF;
131
132 WriteLog(l_msg || 'x_return_status='||x_return_status);
133 WriteLog(l_msg || 'x_dup_status='||x_dup_status);
134
135 -- Standard call to get message count and IF count is 1, get message info.
136
137 FND_MSG_PUB.Count_And_Get
138 ( p_count => x_msg_count,
139 p_data => x_msg_data );
140
141 END Validate_Score_Name;
142
143
144
145 Procedure Validate_SCORE_ID_Name(P_Init_Msg_List IN VARCHAR2 ,
146 P_Score_ID IN NUMBER,
147 P_Score_Name IN VARCHAR2 ,
148 X_Dup_Status OUT NOCOPY VARCHAR2,
149 X_Return_Status OUT NOCOPY VARCHAR2,
150 X_Msg_Count OUT NOCOPY NUMBER,
151 X_Msg_Data OUT NOCOPY VARCHAR2)
152 IS
153 CURSOR C_GET_SCORE_ID_name (IN_SCORE_Name VARCHAR2, IN_SCORE_ID NUMBER) IS
154 SELECT score_Name
155 FROM iex_scores
156 WHERE SCORE_Name = IN_SCORE_Name and score_id <> IN_Score_ID;
157 --
158 l_score_Name VARCHAR2(256);
159
160 BEGIN
161 -- Initialize message list IF p_init_msg_list is set to TRUE.
162 IF FND_API.to_Boolean( p_init_msg_list )
163 THEN
164 FND_MSG_PUB.initialize;
165 END IF;
166
167 -- Initialize API return status to SUCCESS
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170 IF P_SCORE_Name is NULL
171 or P_SCORE_Name = FND_API.G_FALSE
172 THEN
173 FND_MESSAGE.Set_Name('IEX', 'IEX_API_ALL_MISSING_PARAM');
174 FND_MESSAGE.Set_Token('API_NAME', 'Validate_SCORE_ID_Name', FALSE);
175 FND_MESSAGE.Set_Token('MISSING_PARAM', 'SCORE_NAME', FALSE);
176 FND_MSG_PUB.Add;
177 x_return_status := FND_API.G_RET_STS_ERROR;
178
179 ELSE -- IF P_SCORE_Name is NULL or P_SCORE_Name = FND_API.G_FALSE
180
181 OPEN C_Get_Score_ID_Name (p_score_Name, P_SCORE_ID);
182 FETCH C_Get_Score_ID_Name INTO l_score_Name;
183
184 IF (C_Get_Score_ID_Name%FOUND)
185 THEN
186 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
187 THEN
188 FND_MESSAGE.Set_Name('IEX', 'IEX_API_DUPLICATE_NAME');
189 FND_MESSAGE.Set_Token('COLUMN', 'SCORE_NAME', FALSE);
190 FND_MESSAGE.Set_Token('VALUE', p_score_Name, FALSE);
191 FND_MSG_PUB.Add;
192 END IF;
193 x_return_status := FND_API.G_RET_STS_ERROR;
194 x_dup_status := IEX_DUPLICATE_NAME;
195 END IF;
196 CLOSE C_GET_SCORE_ID_Name;
197 END IF;
198
199 -- Standard call to get message count and IF count is 1, get message info.
200
201 FND_MSG_PUB.Count_And_Get
202 ( p_count => x_msg_count,
203 p_data => x_msg_data );
204
205 END Validate_Score_ID_Name;
206
207
208 Procedure Validate_SCORE_COMP_TYPE_NAME(P_Init_Msg_List IN VARCHAR2 ,
209 P_Score_Comp_NAME IN VARCHAR2 ,
210 X_Dup_Status OUT NOCOPY VARCHAR2,
211 X_Return_Status OUT NOCOPY VARCHAR2,
212 X_Msg_Count OUT NOCOPY NUMBER,
213 X_Msg_Data OUT NOCOPY VARCHAR2)
214 IS
215 CURSOR C_GET_SCORE_COMP_NAME (IN_SCORE_COMP_NAME VARCHAR2) IS
216 SELECT score_comp_Name
217 FROM iex_score_comp_types_vl
218 WHERE SCORE_comp_name = IN_SCORE_comp_Name;
219 --
220 l_score_comp_name VARCHAR2(30);
221
222 BEGIN
223 -- Initialize message list IF p_init_msg_list is set to TRUE.
224 IF FND_API.to_Boolean( p_init_msg_list )
225 THEN
226 FND_MSG_PUB.initialize;
227 END IF;
228
229 -- Initialize API return status to SUCCESS
230 x_return_status := FND_API.G_RET_STS_SUCCESS;
231
232 IF P_SCORE_COMP_NAME is NULL
233 or P_SCORE_COMP_NAME = FND_API.G_FALSE
234 THEN
235 FND_MESSAGE.Set_Name('IEX', 'IEX_API_ALL_MISSING_PARAM');
236 FND_MESSAGE.Set_Token('API_NAME', 'Validate_SCORE_COMP_TYPE_NAME', FALSE);
237 FND_MESSAGE.Set_Token('MISSING_PARAM', 'SCORE_COMP_NAME', FALSE);
238 FND_MSG_PUB.Add;
239 x_return_status := FND_API.G_RET_STS_ERROR;
240
241 ELSE -- IF P_SCORE_COMP_NAME is NOT NULL and P_SCORE_COMP_NAME <> FND_API.G_FALSE
242
243 OPEN C_Get_Score_comp_Name (p_SCORE_COMP_NAME);
244 FETCH C_Get_SCORE_COMP_Name INTO l_SCORE_COMP_NAME;
245
246 IF (C_Get_SCORE_COMP_NAME%FOUND)
247 THEN
248 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
249 THEN
250 FND_MESSAGE.Set_Name('IEX', 'IEX_API_DUPLICATE_NAME');
251 FND_MESSAGE.Set_Token('COLUMN', 'SCORE_COMP_NAME', FALSE);
252 FND_MESSAGE.Set_Token('VALUE', p_SCORE_COMP_NAME, FALSE);
253 FND_MSG_PUB.Add;
254 END IF;
255 x_return_status := FND_API.G_RET_STS_ERROR;
256 x_dup_status := IEX_DUPLICATE_NAME;
257 END IF;
258 CLOSE C_GET_SCORE_COMP_NAME;
259 END IF;
260
261 -- Standard call to get message count and IF count is 1, get message info.
262
263 FND_MSG_PUB.Count_And_Get
264 ( p_count => x_msg_count,
265 p_data => x_msg_data );
266
267 END Validate_Score_Comp_Type_NAME;
268
269
270 Procedure Val_SCORE_COMP_TYPE_ID_NAME(P_Init_Msg_List IN VARCHAR2 ,
271 P_Score_Comp_Type_ID IN NUMBER,
272 P_Score_Comp_NAME IN VARCHAR2 ,
273 X_Dup_Status OUT NOCOPY VARCHAR2,
274 X_Return_Status OUT NOCOPY VARCHAR2,
275 X_Msg_Count OUT NOCOPY NUMBER,
276 X_Msg_Data OUT NOCOPY VARCHAR2)
277 IS
278 CURSOR C_GET_SCORE_COMP_ID_NAME (IN_SCORE_COMP_NAME VARCHAR2, IN_SCORE_COMP_TYPE_ID NUMBER) IS
279 SELECT score_comp_Name
280 FROM iex_score_comp_types_vl
281 WHERE SCORE_comp_name = IN_SCORE_comp_Name
282 AND SCORE_COMP_TYPE_ID <> IN_SCORE_COMP_TYPE_ID;
283 --
284 l_score_comp_name VARCHAR2(30);
285
286 BEGIN
287 -- Initialize message list IF p_init_msg_list is set to TRUE.
288 IF FND_API.to_Boolean( p_init_msg_list )
289 THEN
290 FND_MSG_PUB.initialize;
291 END IF;
292
293 -- Initialize API return status to SUCCESS
294 x_return_status := FND_API.G_RET_STS_SUCCESS;
295
296 IF P_SCORE_COMP_NAME is NULL
297 or P_SCORE_COMP_NAME = FND_API.G_FALSE
298 THEN
299 FND_MESSAGE.Set_Name('IEX', 'IEX_API_ALL_MISSING_PARAM');
300 FND_MESSAGE.Set_Token('API_NAME', 'Val_SCORE_COMP_TYPE_ID_NAME', FALSE);
301 FND_MESSAGE.Set_Token('MISSING_PARAM', 'SCORE_COMP_NAME', FALSE);
302 FND_MSG_PUB.Add;
303 x_return_status := FND_API.G_RET_STS_ERROR;
304
305 ELSE -- IF P_SCORE_COMP_NAME is NOT NULL and P_SCORE_COMP_NAME <> FND_API.G_FALSE
306
307 OPEN C_Get_Score_comp_ID_Name (p_SCORE_COMP_NAME, P_SCORE_COMP_TYPE_ID);
308 FETCH C_Get_SCORE_COMP_ID_Name INTO l_SCORE_COMP_NAME;
309
310 IF (C_Get_SCORE_COMP_ID_NAME%FOUND)
311 THEN
312 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
313 THEN
314 FND_MESSAGE.Set_Name('IEX', 'IEX_API_DUPLICATE_NAME');
315 FND_MESSAGE.Set_Token('COLUMN', 'SCORE_COMP_NAME', FALSE);
316 FND_MESSAGE.Set_Token('VALUE', p_SCORE_COMP_NAME, FALSE);
317 FND_MSG_PUB.Add;
318 END IF;
319 x_return_status := FND_API.G_RET_STS_ERROR;
320 x_dup_status := IEX_DUPLICATE_NAME;
321 END IF;
322 CLOSE C_GET_SCORE_COMP_ID_NAME;
323 END IF;
324
325 -- Standard call to get message count and IF count is 1, get message info.
326
327 FND_MSG_PUB.Count_And_Get
328 ( p_count => x_msg_count,
329 p_data => x_msg_data );
330
331 END Val_Score_Comp_Type_ID_NAME;
332
333
334 PROCEDURE Create_Score(p_api_version IN NUMBER := 1.0,
335 p_init_msg_list IN VARCHAR2 ,
336 p_commit IN VARCHAR2 ,
337 P_SCORE_REC IN IEX_SCORE_PUB.SCORE_REC_TYPE,
338 x_dup_status OUT NOCOPY VARCHAR2,
339 x_return_status OUT NOCOPY VARCHAR2,
340 x_msg_count OUT NOCOPY NUMBER,
341 x_msg_data OUT NOCOPY VARCHAR2,
342 X_SCORE_ID OUT NOCOPY NUMBER)
343 IS
344 CURSOR get_seq_csr is
345 SELECT IEX_SCORES_S.nextval
346 FROM sys.dual;
347 --
348 l_api_name CONSTANT VARCHAR2(30) := 'Create_Score';
349 l_api_version_number CONSTANT NUMBER := 1.0;
350 l_return_status VARCHAR2(1);
351 l_msg_count NUMBER;
352 l_msg_data VARCHAR2(32767);
353 l_rowid Varchar2(50);
354 l_SCORE_REC IEX_SCORE_PUB.SCORE_REC_TYPE ;
355 l_score_id NUMBER ;
356 l_msg Varchar2(50);
357
358 BEGIN
359 l_SCORE_REC := p_score_rec;
360
361
362 -- Standard Start of API savepoint
363 SAVEPOINT CREATE_SCORE_PVT;
364
365 -- Standard call to check for call compatibility.
366 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
367 p_api_version,
368 l_api_name,
369 G_PKG_NAME)
370 THEN
371 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
372 END IF;
373
374 -- Initialize message list IF p_init_msg_list is set to TRUE.
375 IF FND_API.to_Boolean( p_init_msg_list )
376 THEN
377 FND_MSG_PUB.initialize;
378 END IF;
379
380 -- Debug Message
381 l_msg := 'iexvscrb:CreateScore:';
382
383 WriteLog(l_msg || 'START');
384
385 -- Initialize API return status to SUCCESS
386 x_return_status := FND_API.G_RET_STS_SUCCESS;
387
388 --
389 -- API body
390 --
391
392 -- Validate Data
393 WriteLog(l_msg || 'Validate_score_name');
394 WriteLog(l_msg || 'score_name='||l_score_rec.score_name);
395
396 Validate_Score_Name(P_Init_Msg_List => FND_API.G_FALSE,
397 P_SCORE_Name => l_score_rec.score_Name,
398 P_SCORE_ID => 0,
399 X_Dup_Status => x_Dup_status,
400 X_Return_Status => x_return_status,
401 X_Msg_Count => x_msg_count,
402 X_Msg_Data => x_msg_data);
403
404 WriteLog(l_msg || 'return_status='||x_return_status);
405 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
406 RAISE FND_API.G_EXC_ERROR;
407 END IF;
408
409 -- ******************************************************************
410 -- Validate Environment
411 -- ******************************************************************
412 /*
413 IF FND_GLOBAL.User_Id IS NULL
414 THEN
415 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
416 THEN
417 AS_UTILITY_PVT.Set_Message(
418 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
419 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
420 p_token1 => 'PROFILE',
421 p_token1_value => 'USER_ID');
422
423 END IF;
424 RAISE FND_API.G_EXC_ERROR;
425 END IF;
426 */
427
428 WriteLog(l_msg || 'score_id='||l_score_rec.score_id);
429
430 If ( (l_score_rec.score_id IS NULL) OR
431 (l_score_rec.score_id = FND_API.G_MISS_NUM) OR
432 (l_score_rec.score_id = 0)) then
433 WriteLog(l_msg || 'get score_id from seq');
434 OPEN get_seq_csr;
435 FETCH get_seq_csr INTO x_score_id ;
436 CLOSE get_seq_csr;
437 End If;
438 WriteLog(l_msg || 'x_score_id='||x_score_id);
439
440
441 WriteLog(l_msg || 'concurrent_prog_id='||l_score_rec.concurrent_prog_id);
442 WriteLog(l_msg || 'concurrent_prog_name='||l_score_rec.concurrent_prog_name);
443 IF (l_score_rec.CAMPAIGN_SCHED_ID = FND_API.G_MISS_NUM) THEN
444 l_score_rec.CAMPAIGN_SCHED_ID := NULL;
445 END IF;
446 IF (l_score_rec.REQUEST_ID = FND_API.G_MISS_NUM) THEN
447 l_score_rec.REQUEST_ID := NULL;
448 END IF;
449 IF (l_score_rec.PROGRAM_APPLICATION_ID = FND_API.G_MISS_NUM) THEN
450 l_score_rec.PROGRAM_APPLICATION_ID := NULL;
451 END IF;
452 IF (l_score_rec.PROGRAM_ID = FND_API.G_MISS_NUM) THEN
453 l_score_rec.PROGRAM_ID := NULL;
454 END IF;
455 IF (l_score_rec.SECURITY_GROUP_ID = FND_API.G_MISS_NUM) THEN
456 l_score_rec.SECURITY_GROUP_ID := NULL;
457 END IF;
458 IF (l_score_rec.CONCURRENT_PROG_ID = FND_API.G_MISS_NUM) THEN
459 l_score_rec.CONCURRENT_PROG_ID := NULL;
460 END IF;
461 IF (l_score_rec.CONCURRENT_PROG_NAME = FND_API.G_MISS_CHAR) THEN
462 l_score_rec.CONCURRENT_PROG_NAME := NULL;
463 END IF;
464
465 IF (l_score_rec.STATUS_DETERMINATION = 'Y') then
466 l_score_rec.CONCURRENT_PROG_NAME := NULL;
467 END IF;
468
469 WriteLog(l_msg || 'score_id='||l_score_rec.score_id);
470 WriteLog(l_msg || 'score_name='||l_score_rec.score_name);
471 WriteLog(l_msg || 'enabled_flag='||l_score_rec.enabled_flag);
472 WriteLog(l_msg || 'valid_from_dt='||l_score_rec.valid_from_dt);
473 WriteLog(l_msg || 'valid_to_dt='||l_score_rec.valid_to_dt);
474 WriteLog(l_msg || 'jtf_object_code='||l_score_rec.jtf_object_code);
475
476 WriteLog(l_msg || 'weight_required='||l_score_rec.weight_required);
477 WriteLog(l_msg || 'score_range_low='||l_score_rec.score_range_low);
478 WriteLog(l_msg || 'score_range_high='||l_score_rec.score_range_high);
479 WriteLog(l_msg || 'out_of_range_rule='||l_score_rec.out_of_range_rule);
480
481 WriteLog(l_msg || 'insert row');
482
483 -- Create Score
484 IEX_SCORES_PKG.insert_row(
485 x_rowid => l_rowid
486 , p_score_id => x_score_id
487 , p_security_group_id => l_score_rec.security_group_id
488 , p_score_name => l_score_rec.score_name
489 , p_score_description => l_score_rec.score_description
490 , p_enabled_flag => l_score_rec.enabled_flag
491 , p_valid_from_dt => l_score_rec.valid_from_dt
492 , p_valid_to_dt => l_score_rec.valid_to_dt
493 , p_campaign_sched_id => l_score_rec.campaign_sched_id
494 , p_jtf_object_code => l_score_rec.jtf_object_code
495 , p_concurrent_prog_name => l_score_rec.concurrent_prog_name
496 , p_last_update_date => sysdate
497 , p_last_updated_by => FND_GLOBAL.USER_ID
498 , p_creation_date => sysdate
499 , p_created_by => FND_GLOBAL.USER_ID
500 , p_last_update_login => FND_GLOBAL.USER_ID
501 , p_request_id => l_score_rec.request_id
502 , p_program_application_id => l_score_rec.program_application_id
503 , p_program_id => l_score_rec.program_id
504 , p_program_update_date => l_score_rec.program_update_date
505 , p_STATUS_DETERMINATION => l_score_rec.STATUS_DETERMINATION
506 , p_WEIGHT_REQUIRED => l_score_rec.WEIGHT_REQUIRED
507 , p_SCORE_RANGE_LOW => l_score_rec.SCORE_RANGE_LOW
508 , p_SCORE_RANGE_HIGH => l_score_rec.SCORE_RANGE_HIGH
509 , p_OUT_OF_RANGE_RULE => l_score_rec.OUT_OF_RANGE_RULE);
510
511 /*
512 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
513 RAISE FND_API.G_EXC_ERROR;
514 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
516 END IF;
517 */
518 --
519 -- End of API body
520 --
521
522 -- Standard check for p_commit
523 IF FND_API.to_Boolean( p_commit )
524 THEN
525 COMMIT WORK;
526 END IF;
527
528 -- Debug Message
529 WriteLog(l_msg || 'END');
530
531 -- Standard call to get message count and IF count is 1, get message info.
532 FND_MSG_PUB.Count_And_Get
533 ( p_count => x_msg_count,
534 p_data => x_msg_data);
535
536 EXCEPTION
537 WHEN FND_API.G_EXC_ERROR THEN
538 ROLLBACK TO CREATE_SCORE_PVT;
539 IEX_SCORE_PVT.WriteLog('iexvscrb:CreateScr: exc exp:'||SQLERRM);
540 x_return_status := FND_API.G_RET_STS_ERROR;
541 FND_MSG_PUB.Count_And_Get
542 ( p_count => x_msg_count,
543 p_data => x_msg_data);
544
545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
546 ROLLBACK TO CREATE_SCORE_PVT;
547 IEX_SCORE_PVT.WriteLog('iexvscrb:CreateScr: unexc exp:'||SQLERRM);
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 FND_MSG_PUB.Count_And_Get
550 ( p_count => x_msg_count,
551 p_data => x_msg_data);
552
553 WHEN OTHERS THEN
554 ROLLBACK TO CREATE_SCORE_PVT;
555 IEX_SCORE_PVT.WriteLog('iexvscrb:CreateScr: other exp:'||SQLERRM);
556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
557 FND_MSG_PUB.Count_And_Get
558 ( p_count => x_msg_count,
559 p_data => x_msg_data);
560
561 END CREATE_SCORE;
562
563
564
565 Procedure Update_Score(p_api_version IN NUMBER := 1.0,
566 p_init_msg_list IN VARCHAR2 ,
567 p_commit IN VARCHAR2 ,
568 P_SCORE_REC IN IEX_SCORE_PUB.SCORE_REC_TYPE,
569 x_dup_status OUT NOCOPY VARCHAR2,
570 x_return_status OUT NOCOPY VARCHAR2,
571 x_msg_count OUT NOCOPY NUMBER,
572 x_msg_data OUT NOCOPY VARCHAR2)
573
574 IS
575 CURSOR C_get_Score_Rec (IN_SCORE_ID NUMBER) is
576 SELECT ROWID,
577 SCORE_ID,
578 SCORE_NAME,
579 SCORE_DESCRIPTION,
580 ENABLED_FLAG ,
581 VALID_FROM_DT,
582 VALID_TO_DT,
583 CAMPAIGN_SCHED_ID,
584 JTF_OBJECT_CODE,
585 CONCURRENT_PROG_NAME,
586 LAST_UPDATE_DATE,
587 LAST_UPDATED_BY,
588 CREATION_DATE,
589 CREATED_BY ,
590 LAST_UPDATE_LOGIN,
591 STATUS_DETERMINATION,
592 WEIGHT_REQUIRED,
593 SCORE_RANGE_LOW,
594 SCORE_RANGE_HIGH,
595 OUT_OF_RANGE_RULE
596 from iex_scores
597 where score_id = in_score_id
598 FOR UPDATE NOWAIT;
599 --
600 l_api_name CONSTANT VARCHAR2(30) := 'Update_Score';
601 l_api_version_number CONSTANT NUMBER := 1.0;
602 l_return_status VARCHAR2(1);
603 l_msg_count NUMBER;
604 l_msg_data VARCHAR2(32767);
605 l_rowid Varchar2(50);
606 l_SCORE_REC IEX_SCORE_PUB.SCORE_REC_TYPE ;
607 l_score_id NUMBER ;
608 l_SCORE_REF_REC IEX_SCORE_PUB.SCORE_REC_TYPE ;
609
610
611 BEGIN
612
613 l_SCORE_REC := p_score_rec;
614
615 -- Standard Start of API savepoint
616 SAVEPOINT UPDATE_SCORE_PVT;
617
618 -- Standard call to check for call compatibility.
619 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
620 p_api_version,
621 l_api_name,
622 G_PKG_NAME)
623 THEN
624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
625 END IF;
626
627 -- Initialize message list IF p_init_msg_list is set to TRUE.
628 IF FND_API.to_Boolean( p_init_msg_list )
629 THEN
630 FND_MSG_PUB.initialize;
631 END IF;
632
633 -- Debug Message
634 WriteLog('iexvscrb:UpdScr: Start');
635 WriteLog('iexvscrb:UpdScr: scoreid='||l_score_rec.score_id);
636
637 -- Initialize API return status to SUCCESS
638 x_return_status := FND_API.G_RET_STS_SUCCESS;
639
640 --
641 -- Api body
642 --
643
644 -- Validate Data
645 WriteLog('iexvscrb:UpdScr: Validate_score_name');
646 WriteLog('iexvscrb:UpdScr: score_name='||l_score_rec.score_name);
647 WriteLog('iexvscrb:UpdScr: score_id='||l_score_rec.score_id);
648
649 Validate_Score_Name(P_Init_Msg_List => FND_API.G_FALSE,
650 P_SCORE_Name => l_score_rec.score_Name,
651 P_SCORE_ID => l_score_rec.score_id,
652 X_Dup_Status => x_Dup_status,
653 X_Return_Status => x_return_status,
654 X_Msg_Count => x_msg_count,
655 X_Msg_Data => x_msg_data);
656
657 WriteLog('iexvscrb:Updscr: return_status='||x_return_status);
658 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
659 RAISE FND_API.G_EXC_ERROR;
660 END IF;
661
662
663 -- Debug Message
664
665
666 -- Debug Message
667 WriteLog('iexvscrb:UpdScr: Open C_Get_Score_Rec');
668
669 Open C_Get_Score_Rec(l_score_rec.SCORE_ID);
670 Fetch C_Get_Score_Rec into
671 l_rowid,
672 l_score_ref_rec.SCORE_ID,
673 l_score_ref_rec.SCORE_NAME,
674 l_score_ref_rec.SCORE_DESCRIPTION,
675 l_score_ref_rec.ENABLED_FLAG,
676 l_score_ref_rec.VALID_FROM_DT,
677 l_score_ref_rec.VALID_TO_DT,
678 l_score_ref_rec.CAMPAIGN_SCHED_ID,
679 l_score_ref_rec.JTF_OBJECT_CODE,
680 l_score_ref_rec.CONCURRENT_PROG_NAME,
681 l_score_ref_rec.LAST_UPDATE_DATE,
682 l_score_ref_rec.LAST_UPDATED_BY,
683 l_score_ref_rec.CREATION_DATE,
684 l_score_ref_rec.CREATED_BY,
685 l_score_ref_rec.LAST_UPDATE_LOGIN,
686 l_score_ref_rec.STATUS_DETERMINATION,
687 l_score_ref_rec.WEIGHT_REQUIRED,
688 l_score_ref_rec.SCORE_RANGE_LOW,
689 l_score_ref_rec.SCORE_RANGE_HIGH,
690 l_score_ref_rec.OUT_OF_RANGE_RULE;
691
692 IF ( C_Get_SCORE_REC%NOTFOUND) THEN
693 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
694 THEN
695 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
696 FND_MESSAGE.Set_Token ('INFO', 'iex_scores', FALSE);
697 FND_MSG_PUB.Add;
698 END IF;
699 RAISE FND_API.G_EXC_ERROR;
700 END IF;
701
702 -- Debug Message
703 WriteLog('iexvscrb:UpdScr: Close C_Get_Score_Rec');
704 Close C_Get_Score_Rec;
705
706 IF (l_score_rec.last_update_date is NULL or
707 l_score_rec.last_update_date = FND_API.G_MISS_Date )
708 THEN
709 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
710 THEN
711 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_ID');
712 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
713 FND_MSG_PUB.ADD;
714 END IF;
715 RAISE FND_API.G_EXC_ERROR;
716 End IF;
717
718 WriteLog('iexvscrb:UpdScr: Transfer Data info target record');
719
720 -- Transfer Data into target record
721 l_score_rec.CREATION_DATE := l_score_ref_rec.CREATION_DATE;
722 l_score_rec.CREATED_BY := l_score_ref_rec.CREATED_BY;
723
724 IF ((l_score_rec.SCORE_NAME = FND_API.G_MISS_CHAR) OR
725 (l_score_rec.SCORE_NAME = NULL)) THEN
726 l_score_rec.SCORE_NAME := l_SCORE_REF_rec.SCORE_NAME;
727 END IF;
728 IF ((l_score_rec.SCORE_DESCRIPTION = FND_API.G_MISS_CHAR) OR
729 (l_score_rec.SCORE_DESCRIPTION = NULL)) THEN
730 l_score_rec.SCORE_DESCRIPTION := l_SCORE_REF_rec.SCORE_DESCRIPTION;
731 END IF;
732 IF ((l_score_rec.ENABLED_FLAG = FND_API.G_MISS_CHAR) OR
733 (l_score_rec.ENABLED_FLAG = NULL)) THEN
734 l_score_rec.ENABLED_FLAG := l_score_ref_rec.ENABLED_FLAG;
735 END IF;
736 IF ((l_score_rec.CAMPAIGN_SCHED_ID = FND_API.G_MISS_NUM) OR
737 (l_score_rec.CAMPAIGN_SCHED_ID = NULL)) THEN
738 l_score_rec.CAMPAIGN_SCHED_ID := l_score_ref_rec.CAMPAIGN_SCHED_ID;
739 END IF;
740 IF ((l_score_rec.VALID_FROM_DT = FND_API.G_MISS_DATE) OR
741 (l_score_rec.VALID_FROM_DT = NULL)) THEN
742 l_score_rec.VALID_FROM_DT := l_score_ref_rec.VALID_FROM_DT;
743 END IF;
744 IF ((l_score_rec.VALID_TO_DT = FND_API.G_MISS_DATE) OR
745 (l_score_rec.VALID_TO_DT = NULL)) THEN
746 l_score_rec.VALID_TO_DT := l_score_ref_rec.VALID_TO_DT;
747 END IF;
748 IF ((l_score_rec.JTF_OBJECT_CODE = FND_API.G_MISS_CHAR) OR
749 (l_score_rec.JTF_OBJECT_CODE = NULL)) THEN
750 l_score_rec.JTF_OBJECT_CODE := l_score_ref_rec.JTF_OBJECT_CODE;
751 END IF;
752 IF ((l_score_rec.CONCURRENT_PROG_NAME = FND_API.G_MISS_CHAR) OR
753 (l_score_rec.CONCURRENT_PROG_NAME = NULL)) THEN
754 l_score_rec.CONCURRENT_PROG_NAME := l_score_ref_rec.CONCURRENT_PROG_NAME;
755 END IF;
756
757 IF (l_score_rec.STATUS_DETERMINATION = 'Y') THEN
758 l_score_rec.CONCURRENT_PROG_NAME := NULL;
759 END IF;
760
761 IF ((l_score_rec.WEIGHT_REQUIRED = FND_API.G_MISS_CHAR) OR
762 (l_score_rec.WEIGHT_REQUIRED = NULL)) THEN
763 l_score_rec.WEIGHT_REQUIRED := l_score_ref_rec.WEIGHT_REQUIRED;
764 END IF;
765 IF ((l_score_rec.SCORE_RANGE_LOW = FND_API.G_MISS_CHAR) OR
766 (l_score_rec.SCORE_RANGE_LOW = NULL)) THEN
767 l_score_rec.SCORE_RANGE_LOW := l_score_ref_rec.SCORE_RANGE_LOW;
768 END IF;
769 IF ((l_score_rec.SCORE_RANGE_HIGH = FND_API.G_MISS_CHAR) OR
770 (l_score_rec.SCORE_RANGE_HIGH = NULL)) THEN
771 l_score_rec.SCORE_RANGE_HIGH := l_score_ref_rec.SCORE_RANGE_HIGH;
772 END IF;
773 IF ((l_score_rec.OUT_OF_RANGE_RULE = FND_API.G_MISS_CHAR) OR
774 (l_score_rec.OUT_OF_RANGE_RULE = NULL)) THEN
775 l_score_rec.OUT_OF_RANGE_RULE := l_score_ref_rec.OUT_OF_RANGE_RULE;
776 END IF;
777
778 WriteLog('iexvscrb:UpdScr: update row');
779
780 IEX_SCORES_PKG.update_row(
781 x_rowid => l_rowid
782 , p_score_id => l_score_rec.score_id
783 , p_security_group_id => l_score_rec.security_group_id
784 , p_score_name => l_score_rec.score_name
785 , p_score_description => l_score_rec.score_description
786 , p_enabled_flag => l_score_rec.enabled_flag
787 , p_valid_from_dt => l_score_rec.valid_from_dt
788 , p_valid_to_dt => l_score_rec.valid_to_dt
789 , p_campaign_sched_id => l_score_rec.campaign_sched_id
790 , p_jtf_object_code => l_score_rec.jtf_object_code
791 , p_concurrent_prog_name => l_score_rec.concurrent_prog_name
792 , p_last_update_date => sysdate
793 , p_last_updated_by => FND_GLOBAL.USER_ID
794 , p_creation_date => l_score_rec.creation_date
795 , p_created_by => l_score_rec.created_by
796 , p_last_update_login => FND_GLOBAL.USER_ID
797 , p_request_id => l_score_rec.request_id
798 , p_program_application_id => l_score_rec.program_application_id
799 , p_program_id => l_score_rec.program_id
800 , p_program_update_date => l_score_rec.program_update_date
801 , p_STATUS_DETERMINATION => l_score_rec.STATUS_DETERMINATION
802 , p_WEIGHT_REQUIRED => l_score_rec.WEIGHT_REQUIRED
803 , p_SCORE_RANGE_LOW => l_score_rec.SCORE_RANGE_LOW
804 , p_SCORE_RANGE_HIGH => l_score_rec.SCORE_RANGE_HIGH
805 , p_OUT_OF_RANGE_RULE => l_score_rec.OUT_OF_RANGE_RULE);
806
807 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
808 RAISE FND_API.G_EXC_ERROR;
809 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
810 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
811 END IF;
812
813 --
814 -- End of API body.
815 --
816
817 -- Standard check for p_commit
818 IF FND_API.to_Boolean( p_commit )
819 THEN
820 COMMIT WORK;
821 END IF;
822
823 -- Debug Message
824 WriteLog('iexvscrb:UpdScr: End');
825
826 -- Standard call to get message count and IF count is 1, get message info.
827 FND_MSG_PUB.Count_And_Get
828 ( p_count => x_msg_count,
829 p_data => x_msg_data );
830
831 EXCEPTION
832 WHEN FND_API.G_EXC_ERROR THEN
833 ROLLBACK TO UPDATE_SCORE_PVT;
834 IEX_SCORE_PVT.WriteLog('iexvscrb:UpdateScr: exc exp:'||SQLERRM);
835 x_return_status := FND_API.G_RET_STS_ERROR;
836 FND_MSG_PUB.Count_And_Get
837 ( p_count => x_msg_count,
838 p_data => x_msg_data);
839
840 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
841 ROLLBACK TO UPDATE_SCORE_PVT;
842 IEX_SCORE_PVT.WriteLog('iexvscrb:UpdateScr: unexc exp:'||SQLERRM);
843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
844 FND_MSG_PUB.Count_And_Get
845 ( p_count => x_msg_count,
846 p_data => x_msg_data);
847
848 WHEN OTHERS THEN
849 ROLLBACK TO UPDATE_SCORE_PVT;
850 IEX_SCORE_PVT.WriteLog('iexvscrb:UpdateScr: other exp:'||SQLERRM);
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
852 FND_MSG_PUB.Count_And_Get
853 ( p_count => x_msg_count,
854 p_data => x_msg_data);
855
856 END Update_Score;
857
858
859
860 Procedure Delete_Score(p_api_version IN NUMBER := 1.0,
861 p_init_msg_list IN VARCHAR2 ,
862 p_commit IN VARCHAR2 ,
863 P_SCORE_ID IN NUMBER,
864 x_return_status OUT NOCOPY VARCHAR2,
865 x_msg_count OUT NOCOPY NUMBER,
866 x_msg_data OUT NOCOPY VARCHAR2)
867
868 IS
869 CURSOR C_GET_SCORE (IN_SCORE_ID NUMBER) IS
870 SELECT rowid
871 FROM IEX_SCORES
872 WHERE SCORE_ID = IN_SCORE_ID;
873 --
874 CURSOR C_GET_SCORE_COMPS (IN_SCORE_ID NUMBER) IS
875 SELECT SCORE_COMPONENT_ID
876 FROM IEX_SCORE_COMPONENTS
877 WHERE SCORE_ID = IN_SCORE_ID;
878 --
879 CURSOR C_GET_SCORE_FILTER (IN_SCORE_ID NUMBER) IS
880 SELECT OBJECT_FILTER_ID
881 FROM IEX_OBJECT_FILTERS
882 WHERE OBJECT_ID = IN_SCORE_ID
883 AND OBJECT_FILTER_TYPE = 'IEXSCORE';
884 --
885 l_score_id NUMBER;
886 l_score_comp_id NUMBER;
887 l_object_filter_id NUMBER;
888 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Score';
889 l_api_version_number CONSTANT NUMBER := 1.0;
890 l_return_status VARCHAR2(1);
891 l_msg_count NUMBER;
892 l_msg_data VARCHAR2(32767);
893 l_rowid Varchar2(50);
894
895
896 BEGIN
897 -- Standard Start of API savepoint
898 SAVEPOINT DELETE_SCORE_PVT;
899
900 -- IF PG_DEBUG < 10 THEN
901 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
902 IEX_DEBUG_PUB.LogMessage('Delete_Score: ' || 'iexvscrb.pls:Delete_Score=>scoreid='||p_score_id);
903 END IF;
904
905 -- Standard call to check for call compatibility.
906 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
907 p_api_version,
908 l_api_name,
909 G_PKG_NAME)
910 THEN
911 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
912 END IF;
913
914 -- Initialize message list IF p_init_msg_list is set to TRUE.
915 IF FND_API.to_Boolean( p_init_msg_list )
916 THEN
917 FND_MSG_PUB.initialize;
918 END IF;
919
920 -- Debug Message
921 WriteLog( 'iexvscrb: DelScr: Start');
922
923 -- Initialize API return status to SUCCESS
924 x_return_status := FND_API.G_RET_STS_SUCCESS;
925
926 --
927 -- Api body
928 --
929
930 WriteLog( 'iexvscrb: DelScr: check score exists or not');
931
932 Open C_Get_SCORE(p_score_id);
933 Fetch C_Get_SCORE into
934 l_rowid;
935
936 IF ( C_Get_Score%NOTFOUND) THEN
937 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
938 FND_MESSAGE.Set_Token ('INFO', 'iex_scores', FALSE);
939 FND_MSG_PUB.Add;
940 x_return_status := FND_API.G_RET_STS_ERROR;
941 RAISE FND_API.G_EXC_ERROR;
942 END IF;
943
944 -- Debug Message
945 Close C_Get_Score;
946
947 WriteLog( 'iexvscrb: DelScr: Delete Row');
948
949
950 -- Invoke table handler
951 IEX_SCORES_PKG.Delete_Row(
952 x_rowid => l_rowid);
953
954 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
955 RAISE FND_API.G_EXC_ERROR;
956 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
958 END IF;
959
960 -- clchang updated 08/01/2002
961 -- delete score will delete score comp ,scorecomp details and filter;
962 -- score comp detail records will be deleted in Delete_Score_Comp;
963 --
964 -- delete score components
965 WriteLog('iexvscrb:Delete_Score=>delete scrcomp');
966 FOR s in C_GET_SCORE_COMPS (p_score_id)
967 LOOP
968 l_score_comp_id := s.score_component_id;
969
970 WriteLog('iexvscrb:Delete_Score=>scrcompid='||l_score_comp_id);
971 IEX_SCORE_PVT.Delete_Score_Comp(
972 p_api_version => p_api_version
973 , p_init_msg_list => p_init_msg_list
974 , p_commit => p_commit
975 , p_score_id => p_score_id
976 , p_score_comp_id => l_score_comp_id
977 , x_return_status => x_return_status
978 , x_msg_count => x_msg_count
979 , x_msg_data => x_msg_data
980 );
981
982 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
983 RAISE FND_API.G_EXC_ERROR;
984 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
985 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
986 END IF;
987
988 END LOOP;
989 WriteLog('iexvscrb:Delete_Score=>after delete scrcomp');
990
991 -- delete scoring filter
992 WriteLog('iexvscrb:Delete_Score=>delete filter');
993 FOR f in C_GET_SCORE_FILTER (p_score_id)
994 LOOP
995 l_object_filter_id := f.object_filter_id;
996
997 WriteLog('iexvscrb:Delete_Score=>filterid='||l_object_filter_id);
998 IEX_FILTER_PUB.Delete_OBJECT_FILTER(
999 p_api_version => p_api_version
1000 , p_init_msg_list => p_init_msg_list
1001 , p_commit => p_commit
1002 , p_object_filter_id => l_object_filter_id
1003 , x_return_status => x_return_status
1004 , x_msg_count => x_msg_count
1005 , x_msg_data => x_msg_data
1006 );
1007
1008 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1009 RAISE FND_API.G_EXC_ERROR;
1010 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1012 END IF;
1013
1014 END LOOP;
1015 WriteLog('iexvscrb:Delete_Score=>after delete filter');
1016
1017 --
1018 -- delete del statuses
1019 WriteLog('iexvscrb:Delete_Score=>delete del statuses');
1020 IEX_DEL_STATUSES_PKG.Delete_del_config(p_score_id);
1021 WriteLog('iexvscrb:Delete_Score=>after delete del statuses');
1022 --
1023 -- End of API body
1024 --
1025
1026 -- Standard check for p_commit
1027 IF FND_API.to_Boolean( p_commit )
1028 THEN
1029 COMMIT WORK;
1030 END IF;
1031
1032 -- Debug Message
1033 WriteLog('iexvscrb:DeleteScore: End');
1034
1035 FND_MSG_PUB.Count_And_Get
1036 ( p_count => x_msg_count,
1037 p_data => x_msg_data );
1038
1039 EXCEPTION
1040 WHEN FND_API.G_EXC_ERROR THEN
1041 ROLLBACK TO DELETE_SCORE_PVT;
1042 x_return_status := FND_API.G_RET_STS_ERROR;
1043 FND_MSG_PUB.Count_And_Get
1044 ( p_count => x_msg_count,
1045 p_data => x_msg_data);
1046
1047 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1048 ROLLBACK TO DELETE_SCORE_PVT;
1049 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1050 FND_MSG_PUB.Count_And_Get
1051 ( p_count => x_msg_count,
1052 p_data => x_msg_data);
1053
1054 WHEN OTHERS THEN
1055 ROLLBACK TO DELETE_SCORE_PVT;
1056 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1057 FND_MSG_PUB.Count_And_Get
1058 ( p_count => x_msg_count,
1059 p_data => x_msg_data);
1060
1061 END Delete_Score;
1062
1063
1064 Procedure Create_SCORE_COMP(p_api_version IN NUMBER := 1.0,
1065 p_init_msg_list IN VARCHAR2 ,
1066 p_commit IN VARCHAR2 ,
1067 P_SCORE_COMP_Rec IN IEX_SCORE_PUB.SCORE_COMP_Rec_TYPE,
1068 x_return_status OUT NOCOPY VARCHAR2,
1069 x_msg_count OUT NOCOPY NUMBER,
1070 x_msg_data OUT NOCOPY VARCHAR2,
1071 x_SCORE_COMP_ID OUT NOCOPY NUMBER)
1072
1073 IS
1074 CURSOR get_seq_csr is
1075 SELECT IEX_SCORE_COMPONENTS_S.nextval
1076 FROM sys.dual;
1077 --
1078
1079 l_api_name CONSTANT VARCHAR2(30) := 'Create_Score_Comp';
1080 l_api_version_number CONSTANT NUMBER := 1.0;
1081 l_return_status VARCHAR2(1);
1082 l_msg_count NUMBER;
1083 l_msg_data VARCHAR2(32767);
1084 l_rowid Varchar2(50);
1085 l_SCORE_COMP_REC IEX_SCORE_PUB.SCORE_COMP_REC_TYPE ;
1086 l_msg Varchar2(50);
1087
1088 BEGIN
1089 l_SCORE_COMP_REC := p_score_comp_rec;
1090
1091 -- Standard Start of API savepoint
1092 SAVEPOINT CREATE_Score_Comp_PVT;
1093
1094 -- Standard call to check for call compatibility.
1095 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1096 p_api_version,
1097 l_api_name,
1098 G_PKG_NAME)
1099 THEN
1100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1101 END IF;
1102
1103
1104 -- Initialize message list IF p_init_msg_list is set to TRUE.
1105 IF FND_API.to_Boolean( p_init_msg_list )
1106 THEN
1107 FND_MSG_PUB.initialize;
1108 END IF;
1109
1110 -- Debug Message
1111 l_msg := 'iexvscrb:CreateScrComp:';
1112 WriteLog(l_msg || 'START');
1113
1114 -- Initialize API return status to SUCCESS
1115 x_return_status := FND_API.G_RET_STS_SUCCESS;
1116
1117 --
1118 -- API body
1119 --
1120
1121
1122 -- Invoke validation procedures
1123 -- Validate Data
1124 WriteLog(l_msg || 'Validate');
1125 WriteLog(l_msg || 'score_id='||l_score_comp_rec.score_id);
1126
1127 IEX_UTILITIES.VALIDATE_ANY_ID(P_COL_ID => l_score_comp_rec.score_id,
1128 P_COL_NAME => 'SCORE_ID',
1129 P_TABLE_NAME => 'IEX_SCORES',
1130 X_Return_Status => x_return_status,
1131 X_Msg_Count => x_msg_count,
1132 X_Msg_Data => x_msg_data,
1133 P_Init_Msg_List => FND_API.G_FALSE);
1134
1135 WriteLog(l_msg || 'return_status='||x_return_status);
1136 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1137 RAISE FND_API.G_EXC_ERROR;
1138 END IF;
1139
1140 WriteLog(l_msg || 'type_id='||l_score_comp_rec.score_comp_type_id);
1141
1142 IEX_UTILITIES.VALIDATE_ANY_ID(P_COL_ID => l_score_comp_rec.score_comp_type_id,
1143 P_COL_NAME => 'SCORE_COMP_TYPE_ID',
1144 P_TABLE_NAME => 'IEX_SCORE_COMP_TYPES_VL',
1145 X_Return_Status => x_return_status,
1146 X_Msg_Count => x_msg_count,
1147 X_Msg_Data => x_msg_data,
1148 P_Init_Msg_List => FND_API.G_FALSE);
1149
1150 WriteLog(l_msg || 'return_status='||x_return_status);
1151 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1152 RAISE FND_API.G_EXC_ERROR;
1153 END IF;
1154
1155
1156 -- Validate Weight (sum of weights = 1.0)
1157 -- Validate Value (sum of (weight * value for each comp) = 100)
1158
1159
1160 WriteLog(l_msg || 'Get ScoreComp Seq');
1161
1162 If ( (l_score_comp_rec.score_component_id IS NULL) OR
1163 (l_score_comp_rec.score_component_id = 0 ) OR
1164 (l_score_comp_rec.score_component_id = FND_API.G_MISS_NUM) ) then
1165 OPEN get_seq_csr;
1166 FETCH get_seq_csr INTO x_score_comp_id ;
1167 CLOSE get_seq_csr;
1168 End If;
1169
1170 WriteLog(l_msg || 'ScrCompId='||x_score_comp_id);
1171
1172 WriteLog(l_msg || 'Insert Row');
1173 WriteLog(l_msg || 'weight='|| l_score_comp_rec.score_comp_weight);
1174 WriteLog(l_msg || 'scoreid='|| l_score_comp_rec.score_id);
1175 WriteLog(l_msg || 'enabled='|| l_score_comp_rec.enabled_flag);
1176 WriteLog(l_msg || 'typeid='|| l_score_comp_rec.score_comp_type_id);
1177
1178 -- Create Score Comp
1179 IEX_SCORE_COMPONENTS_PKG.insert_row(
1180 x_rowid => l_rowid
1181 , p_score_component_id => x_score_comp_id
1182 , p_last_update_date => sysdate
1183 , p_last_updated_by => FND_GLOBAL.USER_ID
1184 , p_creation_date => sysdate
1185 , p_created_by => FND_GLOBAL.USER_ID
1186 , p_last_update_login => FND_GLOBAL.USER_ID
1187 , p_score_comp_weight => l_score_comp_rec.score_comp_weight
1188 , p_score_id => l_score_comp_rec.score_id
1189 , p_enabled_flag => l_score_comp_rec.enabled_flag
1190 , P_SCORE_COMP_TYPE_ID => l_score_comp_rec.SCORE_COMP_TYPE_ID);
1191
1192 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1193 RAISE FND_API.G_EXC_ERROR;
1194 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1196 END IF;
1197
1198
1199 --
1200 -- End of API body
1201 --
1202
1203 -- Standard check for p_commit
1204 IF FND_API.to_Boolean( p_commit )
1205 THEN
1206 COMMIT WORK;
1207 END IF;
1208
1209 -- Debug Message
1210 WriteLog(l_msg || 'End' );
1211
1212 -- Standard call to get message count and IF count is 1, get message info.
1213 FND_MSG_PUB.Count_And_Get
1214 ( p_count => x_msg_count,
1215 p_data => x_msg_data);
1216
1217 EXCEPTION
1218 WHEN FND_API.G_EXC_ERROR THEN
1219 ROLLBACK TO CREATE_SCORE_COMP_PVT;
1220 x_return_status := FND_API.G_RET_STS_ERROR;
1221 FND_MSG_PUB.Count_And_Get
1222 ( p_count => x_msg_count,
1223 p_data => x_msg_data);
1224
1225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1226 ROLLBACK TO CREATE_SCORE_COMP_PVT;
1227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1228 FND_MSG_PUB.Count_And_Get
1229 ( p_count => x_msg_count,
1230 p_data => x_msg_data);
1231
1232 WHEN OTHERS THEN
1233 ROLLBACK TO CREATE_SCORE_COMP_PVT;
1234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1235 FND_MSG_PUB.Count_And_Get
1236 ( p_count => x_msg_count,
1237 p_data => x_msg_data);
1238
1239 END Create_Score_Comp;
1240
1241
1242
1243 Procedure Update_SCORE_COMP(p_api_version IN NUMBER := 1.0,
1244 p_init_msg_list IN VARCHAR2 ,
1245 p_commit IN VARCHAR2 ,
1246 P_SCORE_COMP_Rec IN IEX_SCORE_PUB.SCORE_COMP_Rec_TYPE,
1247 x_return_status OUT NOCOPY VARCHAR2,
1248 x_msg_count OUT NOCOPY NUMBER,
1249 x_msg_data OUT NOCOPY VARCHAR2)
1250
1251 IS
1252 CURSOR C_get_Score_Comp_Rec (IN_SCORE_COMP_ID NUMBER) is
1253 select ROWID,
1254 SCORE_COMPONENT_ID,
1255 SCORE_COMP_WEIGHT,
1256 SCORE_ID,
1257 ENABLED_FLAG,
1258 SCORE_COMP_TYPE_ID,
1259 LAST_UPDATE_DATE,
1260 LAST_UPDATED_BY,
1261 CREATION_DATE,
1262 CREATED_BY ,
1263 LAST_UPDATE_LOGIN
1264 from iex_score_components
1265 where score_component_id = in_score_comp_id
1266 FOR UPDATE NOWAIT;
1267 --
1268 l_api_name CONSTANT VARCHAR2(30) := 'Update_Score_Comp';
1269 l_api_version_number CONSTANT NUMBER := 1.0;
1270 l_return_status VARCHAR2(1);
1271 l_msg_count NUMBER;
1272 l_msg_data VARCHAR2(32767);
1273 l_rowid Varchar2(50);
1274 l_SCORE_COMP_REC IEX_SCORE_PUB.SCORE_COMP_REC_TYPE ;
1275 l_SCORE_COMP_REF_REC IEX_SCORE_PUB.SCORE_COMP_REC_TYPE ;
1276
1277
1278 BEGIN
1279 l_SCORE_COMP_REC := p_score_comp_rec;
1280
1281 -- Standard Start of API savepoint
1282 SAVEPOINT UPDATE_Score_Comp_PVT;
1283
1284 -- Standard call to check for call compatibility.
1285 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1286 p_api_version,
1287 l_api_name,
1288 G_PKG_NAME)
1289 THEN
1290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1291 END IF;
1292
1293 -- Initialize message list IF p_init_msg_list is set to TRUE.
1294 IF FND_API.to_Boolean( p_init_msg_list )
1295 THEN
1296 FND_MSG_PUB.initialize;
1297 END IF;
1298
1299 -- Debug Message
1300 WriteLog('iexvscrb:UpdScrComp: Start');
1301 WriteLog('iexvscrb:UpdScrComp: scoreid='||l_score_comp_rec.score_id);
1302 WriteLog('iexvscrb:UpdScrComp: scorecompid='||l_score_comp_rec.score_comp_type_id);
1303 WriteLog('iexvscrb:UpdScrComp: scorecomptypeid='||l_score_comp_rec.score_comp_type_id);
1304 WriteLog('iexvscrb:UpdScrComp: scorecompid='||l_score_comp_rec.score_component_id);
1305
1306 -- Initialize API return status to SUCCESS
1307 x_return_status := FND_API.G_RET_STS_SUCCESS;
1308
1309 -- Debug message
1310
1311 -- Invoke validation procedures
1312 -- Validate Data
1313
1314 WriteLog('iexvscrb:UpdScrComp: Validate Score_ID');
1315
1316 IEX_UTILITIES.VALIDATE_ANY_ID(P_COL_ID => l_score_comp_rec.score_id,
1317 P_COL_NAME => 'SCORE_ID',
1318 P_TABLE_NAME => 'IEX_SCORES',
1319 X_Return_Status => x_return_status,
1320 X_Msg_Count => x_msg_count,
1321 X_Msg_Data => x_msg_data,
1322 P_Init_Msg_List => FND_API.G_FALSE);
1323
1324 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1325 RAISE FND_API.G_EXC_ERROR;
1326 END IF;
1327
1328 WriteLog('iexvscrb:UpdScrComp: Validate Score_Comp_Type_ID');
1329
1330 IEX_UTILITIES.VALIDATE_ANY_ID(P_COL_ID => l_score_comp_rec.score_comp_type_id,
1331 P_COL_NAME => 'SCORE_COMP_TYPE_ID',
1332 P_TABLE_NAME => 'IEX_SCORE_COMP_TYPES_VL',
1333 X_Return_Status => x_return_status,
1334 X_Msg_Count => x_msg_count,
1335 X_Msg_Data => x_msg_data,
1336 P_Init_Msg_List => FND_API.G_FALSE);
1337 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1338 RAISE FND_API.G_EXC_ERROR;
1339 END IF;
1340 --
1341 -- Api body
1342 --
1343
1344 -- Debug Message
1345
1346 Open C_Get_Score_Comp_Rec(l_score_comp_rec.SCORE_COMPONENT_ID);
1347 Fetch C_Get_Score_Comp_Rec into
1348 l_rowid,
1349 l_score_comp_ref_rec.SCORE_COMPONENT_ID,
1350 l_score_comp_ref_rec.SCORE_COMP_WEIGHT,
1351 l_score_comp_ref_rec.SCORE_ID,
1352 l_score_comp_ref_rec.ENABLED_FLAG,
1353 l_score_comp_ref_rec.SCORE_COMP_TYPE_ID,
1354 l_score_comp_ref_rec.LAST_UPDATE_DATE,
1355 l_score_comp_ref_rec.LAST_UPDATED_BY,
1356 l_score_comp_ref_rec.CREATION_DATE,
1357 l_score_comp_ref_rec.CREATED_BY,
1358 l_score_comp_ref_rec.LAST_UPDATE_LOGIN;
1359
1360 IF ( C_Get_SCORE_COMP_REC%NOTFOUND) THEN
1361 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1362 THEN
1363 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
1364 FND_MESSAGE.Set_Token ('INFO', 'iex_score_components', FALSE);
1365 FND_MSG_PUB.Add;
1366 END IF;
1367 RAISE FND_API.G_EXC_ERROR;
1368 END IF;
1369
1370
1371 -- Debug Message
1372 Close C_Get_Score_Comp_Rec;
1373
1374
1375 IF (l_score_comp_rec.last_update_date is NULL or
1376 l_score_comp_rec.last_update_date = FND_API.G_MISS_Date )
1377 THEN
1378 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1379 THEN
1380 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_ID');
1381 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
1382 FND_MSG_PUB.ADD;
1383 END IF;
1384 RAISE FND_API.G_EXC_ERROR;
1385 End IF;
1386
1387
1388 WriteLog('iexvscrb:UpdScrComp:Transfer Data into Target rec');
1389
1390 -- Transfer Data into target record
1391 l_score_comp_rec.CREATION_DATE := l_score_comp_ref_rec.CREATION_DATE;
1392 l_score_comp_rec.CREATED_BY := l_score_comp_ref_rec.CREATED_BY;
1393 /*
1394 IF ((l_score_comp_rec.SCORE_COMP_NAME = FND_API.G_MISS_CHAR) OR
1395 (l_score_comp_rec.SCORE_COMP_NAME = NULL)) THEN
1396 l_score_comp_rec.SCORE_COMP_NAME := l_SCORE_COMP_REF_rec.SCORE_COMP_NAME;
1397 END IF;
1398 */
1399 IF ((l_score_comp_rec.SCORE_COMP_WEIGHT = FND_API.G_MISS_NUM) OR
1400 (l_score_comp_rec.SCORE_COMP_WEIGHT = NULL)) THEN
1401 l_score_comp_rec.SCORE_COMP_WEIGHT := l_SCORE_COMP_REF_rec.SCORE_COMP_WEIGHT;
1402 END IF;
1403 IF ((l_score_comp_rec.ENABLED_FLAG = FND_API.G_MISS_CHAR) OR
1404 (l_score_comp_rec.ENABLED_FLAG = NULL)) THEN
1405 l_score_comp_rec.ENABLED_FLAG := l_SCORE_COMP_REF_rec.ENABLED_FLAG;
1406 END IF;
1407 IF ((l_score_comp_rec.SCORE_COMP_TYPE_ID = FND_API.G_MISS_NUM) OR
1408 (l_score_comp_rec.SCORE_COMP_TYPE_ID = NULL)) THEN
1409 l_score_comp_rec.SCORE_COMP_TYPE_ID := l_SCORE_COMP_REF_rec.SCORE_COMP_TYPE_ID;
1410 END IF;
1411 -- cannot update score_id ???
1412
1413 WriteLog('iexvscrb:UpdScrComp: Update Row');
1414
1415 IEX_SCORE_COMPONENTS_PKG.update_row(
1416 x_rowid => l_rowid
1417 , p_score_component_id => l_score_comp_rec.score_component_id
1418 , p_score_comp_weight => l_score_comp_rec.score_comp_weight
1419 , p_score_id => l_score_comp_rec.score_id
1420 , p_enabled_flag => l_score_comp_rec.enabled_flag
1421 , P_SCORE_COMP_TYPE_ID => l_score_comp_rec.SCORE_COMP_TYPE_ID
1422 , p_last_update_date => sysdate
1423 , p_last_updated_by => FND_GLOBAL.USER_ID
1424 , p_creation_date => l_score_comp_rec.creation_date
1425 , p_created_by => l_score_Comp_rec.created_by
1426 , p_last_update_login => FND_GLOBAL.USER_ID);
1427
1428 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1429 RAISE FND_API.G_EXC_ERROR;
1430 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1432 END IF;
1433
1434 --
1435 -- End of API body.
1436 --
1437
1438 -- Standard check for p_commit
1439 IF FND_API.to_Boolean( p_commit )
1440 THEN
1441 COMMIT WORK;
1442 END IF;
1443
1444 -- Debug Message
1445 WriteLog('iexvscrb:UpdScrComp: End');
1446
1447
1448 -- Standard call to get message count and IF count is 1, get message info.
1449 FND_MSG_PUB.Count_And_Get
1450 ( p_count => x_msg_count,
1451 p_data => x_msg_data );
1452
1453 EXCEPTION
1454 WHEN FND_API.G_EXC_ERROR THEN
1455 ROLLBACK TO UPDATE_SCORE_COMP_PVT;
1456 x_return_status := FND_API.G_RET_STS_ERROR;
1457 FND_MSG_PUB.Count_And_Get
1458 ( p_count => x_msg_count,
1459 p_data => x_msg_data);
1460
1461 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1462 ROLLBACK TO UPDATE_SCORE_COMP_PVT;
1463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1464 FND_MSG_PUB.Count_And_Get
1465 ( p_count => x_msg_count,
1466 p_data => x_msg_data);
1467
1468 WHEN OTHERS THEN
1469 ROLLBACK TO UPDATE_SCORE_COMP_PVT;
1470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1471 FND_MSG_PUB.Count_And_Get
1472 ( p_count => x_msg_count,
1473 p_data => x_msg_data);
1474
1475 END Update_SCORE_COMP;
1476
1477
1478
1479 Procedure Delete_SCORE_COMP(p_api_version IN NUMBER := 1.0,
1480 p_init_msg_list IN VARCHAR2 ,
1481 p_commit IN VARCHAR2 ,
1482 p_SCORE_ID IN NUMBER,
1483 p_SCORE_COMP_ID IN NUMBER,
1484 x_return_status OUT NOCOPY VARCHAR2,
1485 x_msg_count OUT NOCOPY NUMBER,
1486 x_msg_data OUT NOCOPY VARCHAR2 )
1487
1488 IS
1489 CURSOR C_GET_SCORE_COMP (IN_SCORE_COMP_ID NUMBER) IS
1490 SELECT rowid
1491 FROM IEX_SCORE_COMPONENTS
1492 WHERE SCORE_COMPONENT_ID = IN_SCORE_COMP_ID;
1493 --
1494 CURSOR C_GET_SCORE_COMP_DET (IN_SCORE_COMP_ID NUMBER) IS
1495 SELECT Score_Comp_Det_id
1496 FROM IEX_SCORE_COMP_DET
1497 WHERE SCORE_COMPONENT_ID = IN_SCORE_COMP_ID;
1498 --
1499 l_score_comp_id NUMBER;
1500 l_score_comp_det_id NUMBER;
1501 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Score_Comp';
1502 l_api_version_number CONSTANT NUMBER := 1.0;
1503 l_return_status VARCHAR2(1);
1504 l_msg_count NUMBER;
1505 l_msg_data VARCHAR2(32767);
1506 l_rowid Varchar2(50);
1507
1508 BEGIN
1509 -- Standard Start of API savepoint
1510 SAVEPOINT DELETE_SCORE_COMP_PVT;
1511
1512 WriteLog('iexvscrb:Delete_Score_Comp=>Start');
1513 WriteLog('iexvscrb:Delete_Score_Comp=>scorecompid='||p_score_comp_id);
1514
1515 -- Standard call to check for call compatibility.
1516 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1517 p_api_version,
1518 l_api_name,
1519 G_PKG_NAME)
1520 THEN
1521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1522 END IF;
1523
1524 -- Initialize message list IF p_init_msg_list is set to TRUE.
1525 IF FND_API.to_Boolean( p_init_msg_list )
1526 THEN
1527 FND_MSG_PUB.initialize;
1528 END IF;
1529
1530 -- Debug Message
1531
1532 -- Initialize API return status to SUCCESS
1533 x_return_status := FND_API.G_RET_STS_SUCCESS;
1534
1535 --
1536 -- Api body
1537 --
1538 WriteLog('iexvscrb:Delete_Score_Comp=>check score comp exists or not');
1539 Open C_Get_SCORE_COMP(p_score_comp_id);
1540 Fetch C_Get_SCORE_COMP into
1541 l_rowid;
1542
1543 IF ( C_Get_Score_Comp%NOTFOUND) THEN
1544 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1545 THEN
1546 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
1547 FND_MESSAGE.Set_Token ('INFO', 'iex_score_components', FALSE);
1548 FND_MSG_PUB.Add;
1549 END IF;
1550 RAISE FND_API.G_EXC_ERROR;
1551 END IF;
1552
1553 -- Debug Message
1554 Close C_Get_Score_Comp;
1555
1556 WriteLog('iexvscrb:Delete_Score_Comp=>Delete Row');
1557
1558 -- Invoke table handler
1559 IEX_SCORE_COMPONENTS_PKG.Delete_Row(
1560 x_rowid => l_rowid);
1561
1562 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1563 RAISE FND_API.G_EXC_ERROR;
1564 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1566 END IF;
1567
1568 -- clchang updated 08/01/2002
1569 -- delete score_comp will delete score comp and score comp details;
1570 --
1571 -- delete score component details
1572 WriteLog('iexvscrb:Delete_Score_Comp=>delete scrcompdetails');
1573 FOR s in C_GET_SCORE_COMP_DET (p_score_comp_id)
1574 LOOP
1575 l_score_comp_det_id := s.score_comp_det_id;
1576 WriteLog('iexvscrb:Delete_Score_Comp=>scrcompdetid='||l_score_comp_Det_id);
1577 IEX_SCORE_PVT.Delete_Score_Comp_Det(
1578 p_api_version => p_api_version
1579 , p_init_msg_list => p_init_msg_list
1580 , p_commit => p_commit
1581 , p_score_comp_id => p_score_comp_id
1582 , p_score_comp_det_id => l_score_comp_det_id
1583 , x_return_status => x_return_status
1584 , x_msg_count => x_msg_count
1585 , x_msg_data => x_msg_data
1586 );
1587
1588 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1589 RAISE FND_API.G_EXC_ERROR;
1590 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1592 END IF;
1593
1594 END LOOP;
1595 --
1596 -- End of API body
1597 --
1598
1599 -- Standard check for p_commit
1600 IF FND_API.to_Boolean( p_commit )
1601 THEN
1602 COMMIT WORK;
1603 END IF;
1604
1605 -- Debug Message
1606 WriteLog('iexvscrb:Delete_Score_Comp=>end');
1607
1608 FND_MSG_PUB.Count_And_Get
1609 ( p_count => x_msg_count,
1610 p_data => x_msg_data );
1611
1612 EXCEPTION
1613 WHEN FND_API.G_EXC_ERROR THEN
1614 ROLLBACK TO DELETE_SCORE_COMP_PVT;
1615 x_return_status := FND_API.G_RET_STS_ERROR;
1616 FND_MSG_PUB.Count_And_Get
1617 ( p_count => x_msg_count,
1618 p_data => x_msg_data);
1619
1620 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1621 ROLLBACK TO DELETE_SCORE_COMP_PVT;
1622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1623 FND_MSG_PUB.Count_And_Get
1624 ( p_count => x_msg_count,
1625 p_data => x_msg_data);
1626
1627 WHEN OTHERS THEN
1628 ROLLBACK TO DELETE_SCORE_COMP_PVT;
1629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1630 FND_MSG_PUB.Count_And_Get
1631 ( p_count => x_msg_count,
1632 p_data => x_msg_data);
1633 END Delete_Score_Comp;
1634
1635
1636 -- updated by clchang 04/020/2004 for 11i.IEX.H
1637 -- new column METRIC_FLAG
1638 -- updated by jypark 11/05/2004 for 11i.IEX.H
1639 -- new column DISPLAY_ORDER
1640
1641 Procedure Create_SCORE_COMP_TYPE
1642 (p_api_version IN NUMBER := 1.0,
1643 p_init_msg_list IN VARCHAR2 ,
1644 p_commit IN VARCHAR2 ,
1645 P_SCORE_COMP_TYPE_Rec IN IEX_SCORE_PUB.SCORE_COMP_TYPE_Rec_TYPE ,
1646 x_dup_status OUT NOCOPY VARCHAR2,
1647 x_return_status OUT NOCOPY VARCHAR2,
1648 x_msg_count OUT NOCOPY NUMBER,
1649 x_msg_data OUT NOCOPY VARCHAR2,
1650 x_SCORE_COMP_TYPE_ID OUT NOCOPY NUMBER)
1651
1652 IS
1653 CURSOR get_seq_csr is
1654 SELECT IEX_SCORE_COMP_TYPES_B_S.nextval
1655 FROM sys.dual;
1656 --
1657
1658 l_api_name CONSTANT VARCHAR2(30) := 'Create_Score_Comp_Type';
1659 l_api_version_number CONSTANT NUMBER := 1.0;
1660 l_return_status VARCHAR2(1);
1661 l_msg_count NUMBER;
1662 l_msg_data VARCHAR2(32767);
1663 l_rowid Varchar2(50);
1664 l_SCORE_COMP_Type_REC IEX_SCORE_PUB.SCORE_COMP_Type_REC_TYPE ;
1665
1666 BEGIN
1667 l_SCORE_COMP_Type_REC := p_score_comp_type_rec;
1668
1669 -- Standard Start of API savepoint
1670 SAVEPOINT CREATE_Score_Comp_TYPE_PVT;
1671
1672 -- Standard call to check for call compatibility.
1673 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1674 p_api_version,
1675 l_api_name,
1676 G_PKG_NAME)
1677 THEN
1678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1679 END IF;
1680
1681 -- Initialize message list IF p_init_msg_list is set to TRUE.
1682 IF FND_API.to_Boolean( p_init_msg_list )
1683 THEN
1684 FND_MSG_PUB.initialize;
1685 END IF;
1686
1687 -- Debug Message
1688 WriteLog('iexvscrb:CreateScrCompType: Start');
1689
1690 -- Initialize API return status to SUCCESS
1691 x_return_status := FND_API.G_RET_STS_SUCCESS;
1692
1693 --
1694 -- API body
1695 --
1696
1697 -- Debug message
1698 WriteLog('iexvscrb:CreateScrCompType: Validate');
1699 WriteLog('iexvscrb:CreateScrCompType: CompName='||l_SCORE_COMP_Type_REC.score_comp_name);
1700
1701 -- Invoke validation procedures
1702 -- Validate Data
1703 Validate_Score_comp_type_Name(P_Init_Msg_List => FND_API.G_FALSE,
1704 P_SCORE_COMP_Name => l_SCORE_COMP_Type_REC.score_comp_name,
1705 X_dup_Status => x_dup_status,
1706 X_Return_Status => x_return_status,
1707 X_Msg_Count => x_msg_count,
1708 X_Msg_Data => x_msg_data);
1709
1710 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1711 RAISE FND_API.G_EXC_ERROR;
1712 END IF;
1713
1714 WriteLog('iexvscrb:CreateScrCompType: Get id from seq');
1715
1716
1717 If ( (l_score_comp_type_rec.score_comp_type_id IS NULL) OR
1718 (l_score_comp_type_rec.score_comp_type_id = 0 ) OR
1719 (l_score_comp_type_rec.score_comp_type_id = FND_API.G_MISS_NUM) ) then
1720 OPEN get_seq_csr;
1721 FETCH get_seq_csr INTO x_score_comp_type_id ;
1722 CLOSE get_seq_csr;
1723 End If;
1724
1725 WriteLog('iexvscrb:CreateScrCompType: comptypeid='|| x_score_comp_type_id);
1726 WriteLog('iexvscrb:CreateScrCompType: insert row');
1727
1728
1729 -- Create Score Comp Type
1730 IEX_SCORE_COMP_TYPES_PKG.insert_row(
1731 x_rowid => l_rowid
1732 , P_SCORE_COMP_TYPE_ID => x_score_comp_type_id
1733 , p_OBJECT_Version_Number => l_score_Comp_Type_rec.object_version_number
1734 , p_score_comp_value => l_score_comp_type_rec.score_comp_value
1735 , p_score_comp_name => l_score_comp_type_rec.score_comp_name
1736 , p_active_flag => l_score_comp_type_rec.active_flag
1737 , P_description => l_score_comp_type_rec.Description
1738 , P_jtf_object_code => l_score_comp_type_rec.jtf_object_code
1739 , p_last_update_date => sysdate
1740 , p_last_updated_by => FND_GLOBAL.USER_ID
1741 , p_creation_date => sysdate
1742 , p_created_by => FND_GLOBAL.USER_ID
1743 , p_last_update_login => FND_GLOBAL.USER_ID
1744 , p_function_flag => l_score_comp_type_rec.function_flag
1745 , p_metric_flag => l_score_comp_type_rec.metric_flag
1746 , p_display_order => l_score_comp_type_rec.display_order);
1747
1748 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1749 RAISE FND_API.G_EXC_ERROR;
1750 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1752 END IF;
1753
1754 --
1755 -- End of API body
1756 --
1757
1758 -- Standard check for p_commit
1759 IF FND_API.to_Boolean( p_commit )
1760 THEN
1761 COMMIT WORK;
1762 END IF;
1763
1764 -- Debug Message
1765 WriteLog('iexvscrb:CreateScrCompType: End');
1766
1767
1768 -- Standard call to get message count and IF count is 1, get message info.
1769 FND_MSG_PUB.Count_And_Get
1770 ( p_count => x_msg_count,
1771 p_data => x_msg_data);
1772
1773 EXCEPTION
1774 WHEN FND_API.G_EXC_ERROR THEN
1775 ROLLBACK TO CREATE_SCORE_COMP_TYPE_PVT;
1776 x_return_status := FND_API.G_RET_STS_ERROR;
1777 FND_MSG_PUB.Count_And_Get
1778 ( p_count => x_msg_count,
1779 p_data => x_msg_data);
1780
1781 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1782 ROLLBACK TO CREATE_SCORE_COMP_TYPE_PVT;
1783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1784 FND_MSG_PUB.Count_And_Get
1785 ( p_count => x_msg_count,
1786 p_data => x_msg_data);
1787
1788 WHEN OTHERS THEN
1789 ROLLBACK TO CREATE_SCORE_COMP_TYPE_PVT;
1790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1791 FND_MSG_PUB.Count_And_Get
1792 ( p_count => x_msg_count,
1793 p_data => x_msg_data);
1794
1795 END Create_Score_Comp_Type;
1796
1797
1798
1799 -- updated by clchang 04/020/2004 for 11i.IEX.H
1800 -- new column METRIC_FLAG
1801 -- updated by jypark 11/05/2004 for 11i.IEX.H
1802 -- new column DISPLAY_ORDER
1803 Procedure Update_SCORE_COMP_TYPE(p_api_version IN NUMBER := 1.0,
1804 p_init_msg_list IN VARCHAR2 ,
1805 p_commit IN VARCHAR2 ,
1806 P_SCORE_COMP_Type_Rec IN IEX_SCORE_PUB.SCORE_COMP_Type_Rec_TYPE,
1807 x_dup_status OUT NOCOPY VARCHAR2,
1808 x_return_status OUT NOCOPY VARCHAR2,
1809 x_msg_count OUT NOCOPY NUMBER,
1810 x_msg_data OUT NOCOPY VARCHAR2)
1811
1812 IS
1813 CURSOR C_get_Score_Comp_Type_B_Rec (IN_SCORE_COMP_Type_ID NUMBER) is
1814 select SCORE_COMP_TYPE_ID,
1815 SCORE_COMP_value,
1816 ACTIVE_FLAG,
1817 JTF_OBJECT_CODE,
1818 OBJECT_VERSION_NUMBER,
1819 LAST_UPDATE_DATE,
1820 LAST_UPDATED_BY,
1821 CREATION_DATE,
1822 CREATED_BY ,
1823 LAST_UPDATE_LOGIN,
1824 FUNCTION_FLAG,
1825 METRIC_FLAG,
1826 DISPLAY_ORDER
1827 from iex_score_comp_types_b
1828 where score_comp_type_id = in_score_comp_type_id
1829 FOR UPDATE NOWAIT;
1830 --
1831 CURSOR C_get_Score_Comp_Type_TL_Rec (IN_SCORE_COMP_Type_ID NUMBER) is
1832 select SCORE_COMP_TYPE_ID,
1833 SCORE_COMP_NAME,
1834 OBJECT_VERSION_NUMBER,
1835 LAST_UPDATE_DATE,
1836 LAST_UPDATED_BY,
1837 CREATION_DATE,
1838 CREATED_BY ,
1839 LAST_UPDATE_LOGIN,
1840 DESCRIPTION
1841 from iex_score_comp_types_tl
1842 where score_comp_type_id = in_score_comp_type_id
1843 FOR UPDATE NOWAIT;
1844 --
1845 l_api_name CONSTANT VARCHAR2(30) := 'Update_Score_Comp_Type';
1846 l_api_version_number CONSTANT NUMBER := 1.0;
1847 l_return_status VARCHAR2(1);
1848 l_msg_count NUMBER;
1849 l_msg_data VARCHAR2(32767);
1850 l_rowid Varchar2(50);
1851 l_SCORE_COMP_TYPE_REC IEX_SCORE_PUB.SCORE_COMP_TYPE_REC_TYPE ;
1852 l_SCORE_COMP_TYPE_REF_REC IEX_SCORE_PUB.SCORE_COMP_TYPE_REC_TYPE ;
1853
1854 BEGIN
1855
1856 l_SCORE_COMP_TYPE_REC := p_score_comp_type_rec;
1857
1858 -- Standard Start of API savepoint
1859 SAVEPOINT UPDATE_Score_Comp_TYPE_PVT;
1860
1861 -- Standard call to check for call compatibility.
1862 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1863 p_api_version,
1864 l_api_name,
1865 G_PKG_NAME)
1866 THEN
1867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1868 END IF;
1869
1870 -- Initialize message list IF p_init_msg_list is set to TRUE.
1871 IF FND_API.to_Boolean( p_init_msg_list )
1872 THEN
1873 FND_MSG_PUB.initialize;
1874 END IF;
1875
1876 -- Debug Message
1877 WriteLog ('iexvscrb:UpdScrCompType: Start');
1878
1879 -- Initialize API return status to SUCCESS
1880 x_return_status := FND_API.G_RET_STS_SUCCESS;
1881
1882 --
1883 -- Api body
1884 --
1885
1886 -- Debug Message
1887 WriteLog ('iexvscrb:UpdScrCompType: comptypeid='||l_score_comp_type_rec.SCORE_COMP_TYPE_ID);
1888
1889
1890 Open C_Get_Score_Comp_Type_B_Rec(l_score_comp_type_rec.SCORE_COMP_TYPE_ID);
1891 Fetch C_Get_Score_Comp_Type_B_Rec into
1892 l_score_comp_type_ref_rec.SCORE_COMP_TYPE_ID,
1893 l_score_comp_type_ref_rec.SCORE_COMP_VALUE,
1894 l_score_comp_type_ref_rec.ACTIVE_FLAG,
1895 l_score_comp_type_ref_rec.JTF_OBJECT_CODE,
1896 l_score_comp_type_ref_rec.OBJECT_VERSION_NUMBER,
1897 l_score_comp_type_ref_rec.LAST_UPDATE_DATE,
1898 l_score_comp_type_ref_rec.LAST_UPDATED_BY,
1899 l_score_comp_type_ref_rec.CREATION_DATE,
1900 l_score_comp_type_ref_rec.CREATED_BY,
1901 l_score_comp_type_ref_rec.LAST_UPDATE_LOGIN,
1902 l_score_comp_type_ref_rec.function_flag,
1903 l_score_comp_type_ref_rec.METRIC_flag,
1904 l_score_comp_type_ref_rec.DISPLAY_ORDER;
1905
1906 IF ( C_Get_SCORE_COMP_TYPE_B_REC%NOTFOUND) THEN
1907 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1908 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
1909 FND_MESSAGE.Set_Token ('INFO', 'iex_score_comp_types_b', FALSE);
1910 FND_MSG_PUB.Add;
1911 END IF;
1912 RAISE FND_API.G_EXC_ERROR;
1913 END IF;
1914
1915 -- Debug Message
1916 Close C_Get_Score_Comp_Type_B_Rec;
1917
1918
1919 Open C_Get_Score_Comp_Type_TL_Rec(l_score_comp_type_rec.SCORE_COMP_TYPE_ID);
1920 Fetch C_Get_Score_Comp_Type_TL_Rec into
1921 l_score_comp_type_ref_rec.SCORE_COMP_TYPE_ID,
1922 l_score_comp_type_ref_rec.SCORE_COMP_NAME,
1923 l_score_comp_type_ref_rec.OBJECT_VERSION_NUMBER,
1924 l_score_comp_type_ref_rec.LAST_UPDATE_DATE,
1925 l_score_comp_type_ref_rec.LAST_UPDATED_BY,
1926 l_score_comp_type_ref_rec.CREATION_DATE,
1927 l_score_comp_type_ref_rec.CREATED_BY,
1928 l_score_comp_type_ref_rec.LAST_UPDATE_LOGIN,
1929 l_score_comp_type_ref_rec.DESCRIPTION;
1930
1931 IF ( C_Get_SCORE_COMP_TYPE_TL_REC%NOTFOUND) THEN
1932 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1933 THEN
1934 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
1935 FND_MESSAGE.Set_Token ('INFO', 'iex_score_comp_types_TL', FALSE);
1936 FND_MSG_PUB.Add;
1937 END IF;
1938 RAISE FND_API.G_EXC_ERROR;
1939 END IF;
1940
1941
1942 -- Debug Message
1943 Close C_Get_Score_Comp_Type_TL_Rec;
1944
1945 IF (l_score_comp_type_rec.last_update_date is NULL or
1946 l_score_comp_type_rec.last_update_date = FND_API.G_MISS_Date )
1947 THEN
1948 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1949 THEN
1950 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_ID');
1951 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
1952 FND_MSG_PUB.ADD;
1953 END IF;
1954 RAISE FND_API.G_EXC_ERROR;
1955 End IF;
1956
1957
1958 WriteLog('iexvscrb:UpdScrCompType:Transfer Data into Target rec');
1959
1960 -- Transfer Data into target record
1961 l_score_comp_type_rec.CREATION_DATE := l_score_comp_type_ref_rec.CREATION_DATE;
1962 l_score_comp_type_rec.CREATED_BY := l_score_comp_type_ref_rec.CREATED_BY;
1963
1964 IF ((l_score_comp_type_rec.SCORE_COMP_NAME = FND_API.G_MISS_CHAR) OR
1965 (l_score_comp_type_rec.SCORE_COMP_NAME = NULL)) THEN
1966 l_score_comp_type_rec.SCORE_COMP_NAME := l_SCORE_COMP_TYPE_REF_rec.SCORE_COMP_NAME;
1967 END IF;
1968 IF ((l_score_comp_type_rec.SCORE_COMP_VALUE = FND_API.G_MISS_CHAR) OR
1969 (l_score_comp_type_rec.SCORE_COMP_VALUE = NULL)) THEN
1970 l_score_comp_type_rec.SCORE_COMP_VALUE := l_SCORE_COMP_TYPE_REF_rec.SCORE_COMP_VALUE;
1971 END IF;
1972 IF ((l_score_comp_type_rec.DESCRIPTION = FND_API.G_MISS_CHAR) OR
1973 (l_score_comp_type_rec.DESCRIPTION = NULL)) THEN
1974 l_score_comp_type_rec.DESCRIPTION := l_SCORE_COMP_TYPE_REF_rec.DESCRIPTION;
1975 END IF;
1976 /*
1977 IF ((l_score_comp_type_rec.SCORE_COMP_CODE = FND_API.G_MISS_CHAR) OR
1978 (l_score_comp_type_rec.SCORE_COMP_CODE = NULL)) THEN
1979 l_score_comp_type_rec.SCORE_COMP_CODE := l_SCORE_COMP_TYPE_REF_rec.SCORE_COMP_CODE;
1980 END IF;
1981 */
1982 IF ((l_score_comp_type_rec.ACTIVE_FLAG = FND_API.G_MISS_CHAR) OR
1983 (l_score_comp_type_rec.ACTIVE_FLAG = NULL)) THEN
1984 l_score_comp_type_rec.ACTIVE_FLAG := l_SCORE_COMP_TYPE_REF_rec.ACTIVE_FLAG;
1985 END IF;
1986 IF ((l_score_comp_type_rec.Object_Version_Number = FND_API.G_MISS_NUM) OR
1987 (l_score_comp_type_rec.Object_Version_Number = NULL)) THEN
1988 l_score_comp_type_rec.Object_Version_Number := l_SCORE_COMP_TYPE_REF_rec.Object_Version_Number;
1989 END IF;
1990 IF ((l_score_comp_type_rec.JTF_OBJECT_CODE = FND_API.G_MISS_CHAR) OR
1991 (l_score_comp_type_rec.JTF_OBJECT_CODE = NULL)) THEN
1992 l_score_comp_type_rec.JTF_OBJECT_CODE := l_SCORE_COMP_TYPE_REF_rec.JTF_OBJECT_CODE;
1993 END IF;
1994 IF (l_score_comp_type_rec.Function_Flag = NULL) THEN
1995 l_score_comp_type_rec.Function_flag := l_SCORE_COMP_TYPE_REF_rec.Function_flag;
1996 END IF;
1997 IF (l_score_comp_type_rec.METRIC_Flag = NULL) THEN
1998 l_score_comp_type_rec.metric_flag := l_SCORE_COMP_TYPE_REF_rec.metric_flag;
1999 END IF;
2000 IF (l_score_comp_type_rec.DISPLAY_ORDER = NULL) THEN
2001 l_score_comp_type_rec.DISPLAY_ORDER := l_SCORE_COMP_TYPE_REF_rec.DISPLAY_ORDER;
2002 END IF;
2003
2004 WriteLog('iexvscrb:UpdScrCompType:IEX_SCORE_COMP_TYPES.Update_Row');
2005
2006 IEX_SCORE_COMP_TYPES_PKG.update_row(
2007 p_score_comp_TYPE_id => l_score_comp_type_rec.score_comp_type_id
2008 , p_score_comp_name => l_score_comp_type_rec.score_comp_name
2009 , p_score_comp_value => l_score_comp_type_rec.score_comp_value
2010 , p_active_flag => l_score_comp_type_rec.active_flag
2011 , p_jtf_object_code => l_score_comp_type_rec.jtf_object_code
2012 , p_function_flag => l_score_comp_type_rec.function_flag
2013 , p_metric_flag => l_score_comp_type_rec.metric_flag
2014 , p_display_order => l_score_comp_type_rec.display_order
2015 , P_object_version_number => l_score_comp_type_rec.object_version_number
2016 , p_description => l_score_comp_type_rec.description
2017 , p_last_update_date => sysdate
2018 , p_last_updated_by => FND_GLOBAL.USER_ID
2019 , p_last_update_login => FND_GLOBAL.USER_ID);
2020
2021 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2022 RAISE FND_API.G_EXC_ERROR;
2023 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2024 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2025 END IF;
2026
2027 --
2028 -- End of API body.
2029 --
2030
2031 -- Standard check for p_commit
2032 IF FND_API.to_Boolean( p_commit )
2033 THEN
2034 COMMIT WORK;
2035 END IF;
2036
2037 -- Debug Message
2038 WriteLog('iexvscrb:UpdScrCompType:End');
2039
2040 -- Standard call to get message count and IF count is 1, get message info.
2041 FND_MSG_PUB.Count_And_Get
2042 ( p_count => x_msg_count,
2043 p_data => x_msg_data );
2044
2045 EXCEPTION
2046 WHEN FND_API.G_EXC_ERROR THEN
2047 ROLLBACK To UPDATE_Score_Comp_TYPE_PVT;
2048 x_return_status := FND_API.G_RET_STS_ERROR;
2049 FND_MSG_PUB.Count_And_Get
2050 ( p_count => x_msg_count,
2051 p_data => x_msg_data );
2052
2053 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2054 ROLLBACK To UPDATE_Score_Comp_TYPE_PVT;
2055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2056 FND_MSG_PUB.Count_And_Get
2057 ( p_count => x_msg_count,
2058 p_data => x_msg_data );
2059
2060 WHEN OTHERS THEN
2061 ROLLBACK To UPDATE_Score_Comp_TYPE_PVT;
2062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2063 FND_MSG_PUB.Count_And_Get
2064 ( p_count => x_msg_count,
2065 p_data => x_msg_data );
2066
2067 END Update_SCORE_COMP_TYPE;
2068
2069
2070
2071 Procedure Delete_SCORE_COMP_TYPE
2072 (p_api_version IN NUMBER := 1.0,
2073 p_init_msg_list IN VARCHAR2 ,
2074 p_commit IN VARCHAR2 ,
2075 P_SCORE_COMP_Type_ID IN NUMBER,
2076 x_return_status OUT NOCOPY VARCHAR2,
2077 x_msg_count OUT NOCOPY NUMBER,
2078 x_msg_data OUT NOCOPY VARCHAR2)
2079
2080 IS
2081 CURSOR C_GET_TYPE (IN_SCORE_COMP_TYPE_ID NUMBER) IS
2082 SELECT rowid
2083 FROM IEX_SCORE_COMP_TYPES_B
2084 WHERE SCORE_COMP_TYPE_ID = IN_SCORE_COMP_TYPE_ID;
2085 --
2086 l_score_comp_Type_id NUMBER ;
2087 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Score_Comp_Type';
2088 l_api_version_number CONSTANT NUMBER := 1.0;
2089 l_return_status VARCHAR2(1);
2090 l_msg_count NUMBER;
2091 l_msg_data VARCHAR2(32767);
2092 l_rowid Varchar2(50);
2093
2094
2095 BEGIN
2096 l_score_comp_Type_id := p_score_comp_type_id;
2097
2098 -- Standard Start of API savepoint
2099 SAVEPOINT DELETE_SCORE_COMP_TYPE_PVT;
2100
2101 -- Standard call to check for call compatibility.
2102 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2103 p_api_version,
2104 l_api_name,
2105 G_PKG_NAME)
2106 THEN
2107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2108 END IF;
2109
2110 -- Initialize message list IF p_init_msg_list is set to TRUE.
2111 IF FND_API.to_Boolean( p_init_msg_list )
2112 THEN
2113 FND_MSG_PUB.initialize;
2114 END IF;
2115
2116 -- Debug Message
2117 WriteLog('iexvscrb:DelScrCompType:Start');
2118
2119
2120 -- Initialize API return status to SUCCESS
2121 x_return_status := FND_API.G_RET_STS_SUCCESS;
2122
2123 --
2124 -- Api body
2125 --
2126
2127 Open C_Get_TYPE(l_score_COMP_TYPE_id);
2128 Fetch C_Get_TYPE into
2129 l_rowid;
2130
2131 IF ( C_Get_TYPE%NOTFOUND) THEN
2132 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2133 THEN
2134 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
2135 FND_MESSAGE.Set_Token ('INFO', 'iex_score_comp_Types', FALSE);
2136 FND_MSG_PUB.Add;
2137 END IF;
2138 RAISE FND_API.G_EXC_ERROR;
2139 END IF;
2140
2141 -- Debug Message
2142 Close C_Get_TYPE;
2143
2144
2145 WriteLog('iexvscrb:DelScrCompType:typeid='||l_score_comp_type_id);
2146
2147 -- Invoke table handler
2148 IEX_SCORE_COMP_TYPES_PKG .Delete_Row(
2149 p_score_comp_type_id => l_score_comp_type_id);
2150
2151 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2152 RAISE FND_API.G_EXC_ERROR;
2153 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2155 END IF;
2156
2157 --
2158 -- End of API body
2159 --
2160
2161 -- Standard check for p_commit
2162 IF FND_API.to_Boolean( p_commit )
2163 THEN
2164 COMMIT WORK;
2165 END IF;
2166
2167 -- Debug Message
2168 WriteLog('iexvscrb:DelScrCompType:End');
2169
2170 FND_MSG_PUB.Count_And_Get
2171 ( p_count => x_msg_count,
2172 p_data => x_msg_data );
2173
2174 EXCEPTION
2175 WHEN FND_API.G_EXC_ERROR THEN
2176 ROLLBACK To DELETE_Score_Comp_TYPE_PVT;
2177 x_return_status := FND_API.G_RET_STS_ERROR;
2178 FND_MSG_PUB.Count_And_Get
2179 ( p_count => x_msg_count,
2180 p_data => x_msg_data );
2181
2182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2183 ROLLBACK To DELETE_Score_Comp_TYPE_PVT;
2184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2185 FND_MSG_PUB.Count_And_Get
2186 ( p_count => x_msg_count,
2187 p_data => x_msg_data );
2188
2189 WHEN OTHERS THEN
2190 ROLLBACK To DELETE_Score_Comp_TYPE_PVT;
2191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2192 FND_MSG_PUB.Count_And_Get
2193 ( p_count => x_msg_count,
2194 p_data => x_msg_data );
2195
2196 END Delete_Score_comp_TYpe;
2197
2198
2199
2200
2201 Procedure Create_SCORE_COMP_DET(p_api_version IN NUMBER := 1.0,
2202 p_init_msg_list IN VARCHAR2 ,
2203 p_commit IN VARCHAR2 ,
2204 p_SCORE_COMP_DET_REC IN IEX_SCORE_PUB.SCORE_COMP_DET_REC_Type,
2205 x_return_status OUT NOCOPY VARCHAR2,
2206 x_msg_count OUT NOCOPY NUMBER,
2207 x_msg_data OUT NOCOPY VARCHAR2,
2208 x_score_comp_det_id OUT NOCOPY NUMBER)
2209
2210
2211 IS
2212 CURSOR get_seq_csr is
2213 SELECT IEX_SCORE_COMP_DET_S.nextval
2214 FROM sys.dual;
2215 --
2216 l_api_name CONSTANT VARCHAR2(30) := 'Create_Score_Comp_Det';
2217 l_api_version_number CONSTANT NUMBER := 1.0;
2218 l_return_status VARCHAR2(1);
2219 l_msg_count NUMBER;
2220 l_msg_data VARCHAR2(32767);
2221 l_rowid Varchar2(50);
2222 l_SCORE_COMP_DET_REC IEX_SCORE_PUB.SCORE_COMP_DET_REC_TYPE ;
2223 l_msg Varchar2(50);
2224
2225 BEGIN
2226 l_SCORE_COMP_DET_REC := p_SCORE_COMP_DET_REC;
2227
2228 -- Standard Start of API savepoint
2229 SAVEPOINT CREATE_Score_Comp_Det_PVT;
2230
2231 -- Standard call to check for call compatibility.
2232 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2233 p_api_version,
2234 l_api_name,
2235 G_PKG_NAME)
2236 THEN
2237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2238 END IF;
2239
2240
2241 -- Initialize message list IF p_init_msg_list is set to TRUE.
2242 IF FND_API.to_Boolean( p_init_msg_list )
2243 THEN
2244 FND_MSG_PUB.initialize;
2245 END IF;
2246
2247 -- Debug Message
2248 l_msg := 'iexvscrb:CreateScrCompDet:';
2249
2250 WriteLog(l_msg || 'START');
2251
2252 -- Initialize API return status to SUCCESS
2253 x_return_status := FND_API.G_RET_STS_SUCCESS;
2254
2255 --
2256 -- API body
2257 --
2258
2259 -- Debug Message
2260 WriteLog(l_msg || 'Validate');
2261 WriteLog(l_msg || 'CompId='|| l_score_comp_det_rec.score_component_id);
2262
2263 -- Validate Data
2264 IEX_UTILITIES.VALIDATE_ANY_ID(P_COL_ID => l_score_comp_det_rec.score_component_id,
2265 P_COL_NAME => 'SCORE_COMPONENT_ID',
2266 P_TABLE_NAME => 'IEX_SCORE_COMPONENTS',
2267 X_Return_Status => x_return_status,
2268 X_Msg_Count => x_msg_count,
2269 X_Msg_Data => x_msg_data,
2270 P_Init_Msg_List => FND_API.G_FALSE);
2271
2272 WriteLog(l_msg || 'return_status='|| x_return_status);
2273 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2274 WriteLog(l_msg || 'raise exc error');
2275 RAISE FND_API.G_EXC_ERROR;
2276 END IF;
2277
2278
2279
2280 -- Create Score Comp Det
2281
2282 WriteLog(l_msg || 'Get ScrCompDet Seq');
2283
2284 If ( (l_score_comp_det_rec.score_comp_det_id IS NULL) OR
2285 (l_score_comp_det_rec.score_comp_det_id = 0 ) OR
2286 (l_score_comp_det_rec.score_comp_det_id = FND_API.G_MISS_NUM) ) then
2287 OPEN get_seq_csr;
2288 FETCH get_seq_csr INTO x_score_comp_det_id ;
2289 CLOSE get_seq_csr;
2290 End If;
2291
2292 WriteLog(l_msg || 'ScrCompDetId='|| x_score_comp_det_id);
2293
2294 WriteLog(l_msg || 'Insert Row');
2295 WriteLog(l_msg || 'rangelow='|| l_score_comp_det_rec.range_low);
2296 WriteLog(l_msg || 'rangehigh='|| l_score_comp_det_rec.range_high);
2297 WriteLog(l_msg || 'value='|| l_score_comp_det_rec.value);
2298 WriteLog(l_msg || 'new_value='|| l_score_comp_det_rec.new_value);
2299 WriteLog(l_msg || 'scrcompid='|| l_score_comp_det_rec.score_component_id);
2300
2301 IEX_SCORE_COMP_DET_PKG.insert_row(
2302 x_rowid => l_rowid
2303 , p_score_comp_det_id => x_score_comp_det_id
2304 , p_range_low => l_score_comp_det_rec.range_low
2305 , p_range_high => l_score_comp_det_rec.range_high
2306 , p_value => l_score_comp_det_rec.value
2307 , p_new_value => l_score_comp_det_rec.new_value
2308 , p_score_component_id => l_score_comp_det_rec.score_component_id
2309 , p_object_version_number => l_score_comp_det_rec.object_version_number
2310 , p_program_id => l_score_comp_det_rec.program_id
2311 , p_last_update_date => sysdate
2312 , p_last_updated_by => FND_GLOBAL.USER_ID
2313 , p_creation_date => sysdate
2314 , p_created_by => FND_GLOBAL.USER_ID
2315 , p_last_update_login => FND_GLOBAL.USER_ID);
2316
2317
2318 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2319 RAISE FND_API.G_EXC_ERROR;
2320 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2322 END IF;
2323
2324
2325 --
2326 -- End of API body
2327 --
2328
2329 -- Standard check for p_commit
2330 IF FND_API.to_Boolean( p_commit )
2331 THEN
2332 COMMIT WORK;
2333 END IF;
2334
2335 -- Debug Message
2336 WriteLog(l_msg || 'END');
2337
2338 -- Standard call to get message count and IF count is 1, get message info.
2339 FND_MSG_PUB.Count_And_Get
2340 ( p_count => x_msg_count,
2341 p_data => x_msg_data
2342 );
2343
2344 EXCEPTION
2345 WHEN FND_API.G_EXC_ERROR THEN
2346 ROLLBACK To CREATE_Score_Comp_DET_PVT;
2347 x_return_status := FND_API.G_RET_STS_ERROR;
2348 FND_MSG_PUB.Count_And_Get
2349 ( p_count => x_msg_count,
2350 p_data => x_msg_data );
2351
2352 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2353 ROLLBACK To CREATE_Score_Comp_DET_PVT;
2354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2355 FND_MSG_PUB.Count_And_Get
2356 ( p_count => x_msg_count,
2357 p_data => x_msg_data );
2358
2359 WHEN OTHERS THEN
2360 ROLLBACK To CREATE_Score_Comp_DET_PVT;
2361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2362 FND_MSG_PUB.Count_And_Get
2363 ( p_count => x_msg_count,
2364 p_data => x_msg_data );
2365
2366 END Create_Score_Comp_det;
2367
2368
2369
2370 Procedure Update_SCORE_COMP_DET(p_api_version IN NUMBER := 1.0,
2371 p_init_msg_list IN VARCHAR2 ,
2372 p_commit IN VARCHAR2 ,
2373 p_SCORE_COMP_DET_Rec IN IEX_SCORE_PUB.SCORE_COMP_DET_REC_Type,
2374 x_return_status OUT NOCOPY VARCHAR2,
2375 x_msg_count OUT NOCOPY NUMBER,
2376 x_msg_data OUT NOCOPY VARCHAR2)
2377
2378 IS
2379 CURSOR C_get_Score_Comp_Det_Rec (IN_SCORE_COMP_Det_ID NUMBER) is
2380 select ROWID,
2381 SCORE_COMP_DET_ID,
2382 RANGE_LOW,
2383 RANGE_HIGH,
2384 VALUE,
2385 NEW_VALUE,
2386 SCORE_COMPONENT_ID,
2387 OBJECT_VERSION_NUMBER,
2388 PROGRAM_ID,
2389 LAST_UPDATE_DATE,
2390 LAST_UPDATED_BY,
2391 CREATION_DATE,
2392 CREATED_BY ,
2393 LAST_UPDATE_LOGIN
2394 from iex_score_comp_det
2395 where score_comp_det_id = in_score_comp_det_id
2396 FOR UPDATE NOWAIT;
2397 --
2398 l_api_name CONSTANT VARCHAR2(30) := 'Update_Score_Comp_Det';
2399 l_api_version_number CONSTANT NUMBER := 1.0;
2400 l_return_status VARCHAR2(1);
2401 l_msg_count NUMBER;
2402 l_msg_data VARCHAR2(32767);
2403 l_rowid Varchar2(50);
2404 l_SCORE_COMP_DET_REC IEX_SCORE_PUB.SCORE_COMP_DET_REC_TYPE ;
2405 l_SCORE_COMP_DET_REF_REC IEX_SCORE_PUB.SCORE_COMP_DET_REC_TYPE ;
2406
2407
2408 BEGIN
2409
2410 l_SCORE_COMP_DET_REC := p_score_comp_det_rec;
2411
2412 -- Standard Start of API savepoint
2413 SAVEPOINT UPDATE_Score_Comp_DET_PVT;
2414
2415 -- Standard call to check for call compatibility.
2416 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2417 p_api_version,
2418 l_api_name,
2419 G_PKG_NAME)
2420 THEN
2421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2422 END IF;
2423
2424 -- Initialize message list IF p_init_msg_list is set to TRUE.
2425 IF FND_API.to_Boolean( p_init_msg_list )
2426 THEN
2427 FND_MSG_PUB.initialize;
2428 END IF;
2429
2430 -- Debug Message
2431 WriteLog('iexvscrb:UpdScrCompDet: Start');
2432
2433 -- Initialize API return status to SUCCESS
2434 x_return_status := FND_API.G_RET_STS_SUCCESS;
2435
2436 --
2437 -- Api body
2438 --
2439
2440 -- Debug Message
2441 WriteLog('iexvscrb:UpdScrCompDet: detid='||l_score_comp_det_rec.score_Comp_det_id);
2442
2443
2444 Open C_Get_Score_Comp_Det_Rec(l_score_comp_det_rec.SCORE_COMP_DET_ID);
2445 Fetch C_Get_Score_Comp_Det_Rec into
2446 l_rowid,
2447 l_score_comp_det_ref_rec.SCORE_COMP_DET_ID,
2448 l_score_comp_det_ref_rec.RANGE_LOW,
2449 l_score_comp_det_ref_rec.RANGE_HIGH,
2450 l_score_comp_det_ref_rec.VALUE,
2451 l_score_comp_det_ref_rec.NEW_VALUE,
2452 l_score_comp_det_ref_rec.SCORE_COMPONENT_ID,
2453 l_score_comp_det_ref_rec.OBJECT_VERSION_NUMBER,
2454 l_score_comp_det_ref_rec.PROGRAM_ID,
2455 l_score_comp_det_ref_rec.LAST_UPDATE_DATE,
2456 l_score_comp_det_ref_rec.LAST_UPDATED_BY,
2457 l_score_comp_det_ref_rec.CREATION_DATE,
2458 l_score_comp_det_ref_rec.CREATED_BY,
2459 l_score_comp_det_ref_rec.LAST_UPDATE_LOGIN;
2460
2461 IF ( C_Get_SCORE_COMP_DET_REC%NOTFOUND) THEN
2462 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2463 THEN
2464 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
2465 FND_MESSAGE.Set_Token ('INFO', 'iex_score_comp_det', FALSE);
2466 FND_MSG_PUB.Add;
2467 END IF;
2468 RAISE FND_API.G_EXC_ERROR;
2469 END IF;
2470
2471
2472 -- Debug Message
2473 Close C_Get_Score_Comp_Det_Rec;
2474
2475
2476
2477 IF (l_score_comp_det_rec.last_update_date is NULL or
2478 l_score_comp_det_rec.last_update_date = FND_API.G_MISS_Date )
2479 THEN
2480 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2481 THEN
2482 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_ID');
2483 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
2484 FND_MSG_PUB.ADD;
2485 END IF;
2486 RAISE FND_API.G_EXC_ERROR;
2487 End IF;
2488
2489
2490 WriteLog('iexvscrb: UpdSrCompDet: Transfer Data into target rec');
2491
2492
2493 -- Transfer Data into target record
2494 l_score_comp_det_rec.CREATION_DATE := l_score_comp_det_ref_rec.CREATION_DATE;
2495 l_score_comp_det_rec.CREATED_BY := l_score_comp_det_ref_rec.CREATED_BY;
2496
2497 IF ((l_score_comp_det_rec.RANGE_LOW = FND_API.G_MISS_NUM) OR
2498 (l_score_comp_det_rec.RANGE_LOW = NULL)) THEN
2499 l_score_comp_det_rec.RANGE_LOW := l_SCORE_COMP_det_REF_rec.RANGE_LOW;
2500 END IF;
2501 IF ((l_score_comp_det_rec.RANGE_HIGH = FND_API.G_MISS_NUM) OR
2502 (l_score_comp_det_rec.RANGE_HIGH = NULL)) THEN
2503 l_score_comp_det_rec.RANGE_HIGH := l_SCORE_COMP_det_REF_rec.RANGE_HIGH;
2504 END IF;
2505 IF ((l_score_comp_det_rec.VALUE = FND_API.G_MISS_NUM) OR
2506 (l_score_comp_det_rec.VALUE = NULL)) THEN
2507 l_score_comp_det_rec.VALUE := l_SCORE_COMP_det_REF_rec.VALUE;
2508 END IF;
2509 IF ((l_score_comp_det_rec.NEW_VALUE = FND_API.G_MISS_CHAR) OR
2510 (l_score_comp_det_rec.NEW_VALUE = NULL)) THEN
2511 l_score_comp_det_rec.NEW_VALUE := l_SCORE_COMP_det_REF_rec.NEW_VALUE;
2512 END IF;
2513 IF ((l_score_comp_det_rec.object_version_number = FND_API.G_MISS_NUM) OR
2514 (l_score_comp_det_rec.object_version_number = NULL)) THEN
2515 l_score_comp_det_rec.object_version_number := l_SCORE_COMP_det_REF_rec.object_version_number;
2516 END IF;
2517 IF ((l_score_comp_det_rec.program_id = FND_API.G_MISS_NUM) OR
2518 (l_score_comp_det_rec.program_id = NULL)) THEN
2519 l_score_comp_det_rec.program_id := l_SCORE_COMP_det_REF_rec.program_id;
2520 END IF;
2521
2522
2523 WriteLog('iexvscrb: UpdSrCompDet: Update Row');
2524
2525 IEX_SCORE_COMP_DET_PKG.update_row(
2526 x_rowid => l_rowid
2527 , p_score_comp_det_id => l_score_comp_det_rec.score_comp_det_id
2528 , p_last_update_date => sysdate
2529 , p_last_updated_by => FND_GLOBAL.USER_ID
2530 , p_creation_date => l_score_comp_det_rec.CREATION_DATE
2531 , p_created_by => l_score_comp_det_rec.CREATED_BY
2532 , p_last_update_login => FND_GLOBAL.USER_ID
2533 , p_range_low => l_score_comp_det_rec.range_low
2534 , p_range_high => l_score_comp_det_rec.range_high
2535 , p_value => l_score_comp_det_rec.value
2536 , p_new_value => l_score_comp_det_rec.new_value
2537 , p_score_component_id => l_score_comp_det_rec.score_component_id
2538 , p_object_version_number => l_score_comp_det_rec.object_version_number
2539 , p_program_id => l_score_comp_det_rec.program_id);
2540
2541 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2542 RAISE FND_API.G_EXC_ERROR;
2543 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2545 END IF;
2546
2547 --
2548 -- End of API body.
2549 --
2550
2551 -- Standard check for p_commit
2552 IF FND_API.to_Boolean( p_commit )
2553 THEN
2554 COMMIT WORK;
2555 END IF;
2556
2557 -- Debug Message
2558 WriteLog('iexvscrb: UpdSrCompDet: End');
2559
2560
2561 -- Standard call to get message count and IF count is 1, get message info.
2562 FND_MSG_PUB.Count_And_Get
2563 ( p_count => x_msg_count,
2564 p_data => x_msg_data );
2565
2566 EXCEPTION
2567 WHEN FND_API.G_EXC_ERROR THEN
2568 ROLLBACK To UPDATE_Score_Comp_DET_PVT;
2569 x_return_status := FND_API.G_RET_STS_ERROR;
2570 FND_MSG_PUB.Count_And_Get
2571 ( p_count => x_msg_count,
2572 p_data => x_msg_data );
2573
2574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2575 ROLLBACK To UPDATE_Score_Comp_DET_PVT;
2576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2577 FND_MSG_PUB.Count_And_Get
2578 ( p_count => x_msg_count,
2579 p_data => x_msg_data );
2580
2581 WHEN OTHERS THEN
2582 ROLLBACK To UPDATE_Score_Comp_DET_PVT;
2583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2584 FND_MSG_PUB.Count_And_Get
2585 ( p_count => x_msg_count,
2586 p_data => x_msg_data );
2587 END Update_SCORE_COMP_DET;
2588
2589
2590
2591 Procedure Delete_SCORE_COMP_DET(p_api_version IN NUMBER := 1.0,
2592 p_init_msg_list IN VARCHAR2 ,
2593 p_commit IN VARCHAR2 ,
2594 p_SCORE_COMP_ID IN NUMBER,
2595 p_SCORE_COMP_DET_ID IN NUMBER,
2596 x_return_status OUT NOCOPY VARCHAR2,
2597 x_msg_count OUT NOCOPY NUMBER,
2598 x_msg_data OUT NOCOPY VARCHAR2)
2599
2600 IS
2601 CURSOR C_GET_SCORE_COMP_DET (IN_SCORE_COMP_DET_ID NUMBER) IS
2602 SELECT rowid
2603 FROM IEX_SCORE_COMP_DET
2604 WHERE SCORE_COMP_DET_ID = IN_SCORE_COMP_DET_ID;
2605 --
2606 l_score_comp_det_id NUMBER;
2607 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Score_Comp_DET';
2608 l_api_version_number CONSTANT NUMBER := 1.0;
2609 l_return_status VARCHAR2(1);
2610 l_msg_count NUMBER;
2611 l_msg_data VARCHAR2(32767);
2612 l_rowid Varchar2(50);
2613
2614 BEGIN
2615 -- Standard Start of API savepoint
2616 SAVEPOINT DELETE_SCORE_COMP_DET_PVT;
2617
2618 -- Standard call to check for call compatibility.
2619 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2620 p_api_version,
2621 l_api_name,
2622 G_PKG_NAME)
2623 THEN
2624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2625 END IF;
2626
2627 -- Initialize message list IF p_init_msg_list is set to TRUE.
2628 IF FND_API.to_Boolean( p_init_msg_list )
2629 THEN
2630 FND_MSG_PUB.initialize;
2631 END IF;
2632
2633 -- Debug Message
2634 WriteLog('iexvscrb: DelSrCompDet: Start');
2635 WriteLog('iexvscrb: DelSrCompDet: detid='||p_score_comp_det_id);
2636
2637
2638 -- Initialize API return status to SUCCESS
2639 x_return_status := FND_API.G_RET_STS_SUCCESS;
2640
2641 --
2642 -- Api body
2643 --
2644 Open C_Get_SCORE_COMP_DET(p_score_comp_det_id);
2645 Fetch C_Get_SCORE_COMP_DET into
2646 l_rowid;
2647
2648 IF ( C_Get_Score_Comp_Det%NOTFOUND) THEN
2649 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2650 THEN
2651 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
2652 FND_MESSAGE.Set_Token ('INFO', 'iex_score_comp_det', FALSE);
2653 FND_MSG_PUB.Add;
2654 END IF;
2655 RAISE FND_API.G_EXC_ERROR;
2656 END IF;
2657
2658 -- Debug Message
2659 Close C_Get_Score_Comp_DET;
2660
2661
2662 -- Invoke table handler
2663 IEX_SCORE_COMP_DET_PKG.Delete_Row(x_rowid => l_rowid);
2664
2665 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2666 RAISE FND_API.G_EXC_ERROR;
2667 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2669 END IF;
2670
2671 --
2672 -- End of API body
2673 --
2674
2675 -- Standard check for p_commit
2676 IF FND_API.to_Boolean( p_commit )
2677 THEN
2678 COMMIT WORK;
2679 END IF;
2680
2681 -- Debug Message
2682 WriteLog('iexvscrb: DelScrCompDet: End');
2683
2684 FND_MSG_PUB.Count_And_Get
2685 ( p_count => x_msg_count,
2686 p_data => x_msg_data );
2687
2688 EXCEPTION
2689 WHEN FND_API.G_EXC_ERROR THEN
2690 ROLLBACK To DELETE_Score_Comp_DET_PVT;
2691 x_return_status := FND_API.G_RET_STS_ERROR;
2692 FND_MSG_PUB.Count_And_Get
2693 ( p_count => x_msg_count,
2694 p_data => x_msg_data );
2695
2696 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2697 ROLLBACK To DELETE_Score_Comp_DET_PVT;
2698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2699 FND_MSG_PUB.Count_And_Get
2700 ( p_count => x_msg_count,
2701 p_data => x_msg_data );
2702
2703 WHEN OTHERS THEN
2704 ROLLBACK To DELETE_Score_Comp_DET_PVT;
2705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2706 FND_MSG_PUB.Count_And_Get
2707 ( p_count => x_msg_count,
2708 p_data => x_msg_data );
2709 END Delete_Score_Comp_Det;
2710
2711
2712
2713 /* 12/09/2002 clchang added
2714 * new function to make a copy of scoring engine.
2715 * it will copy all score components, scoring filters,
2716 * and score component details for this scoring engine.
2717 *
2718 * clchang updated 10/21/04 it should also copy data from iex_del_statuses;
2719 *
2720 */
2721 Procedure Copy_ScoringEngine
2722 (p_api_version IN NUMBER := 1.0,
2723 p_init_msg_list IN VARCHAR2 ,
2724 p_commit IN VARCHAR2 ,
2725 p_score_id IN NUMBER,
2726 x_score_id OUT NOCOPY NUMBER,
2727 x_return_status OUT NOCOPY VARCHAR2,
2728 x_msg_count OUT NOCOPY NUMBER,
2729 x_msg_data OUT NOCOPY VARCHAR2)
2730
2731 IS
2732 -- clchang updated 08/22/2003 - the new copy scr engine always disabled.
2733 CURSOR C_GET_SCORE(IN_SCORE_ID NUMBER) IS
2734 SELECT SCORE_NAME,
2735 LENGTH(SCORE_NAME),
2736 SCORE_DESCRIPTION,
2737 --ENABLED_FLAG ,
2738 'N' ,
2739 VALID_FROM_DT,
2740 VALID_TO_DT,
2741 CAMPAIGN_SCHED_ID,
2742 JTF_OBJECT_CODE,
2743 CONCURRENT_PROG_NAME,
2744 STATUS_DETERMINATION,
2745 WEIGHT_REQUIRED,
2746 SCORE_RANGE_LOW,
2747 SCORE_RANGE_HIGH,
2748 OUT_OF_RANGE_RULE
2749 FROM IEX_SCORES
2750 WHERE SCORE_ID = IN_SCORE_ID;
2751 --
2752 CURSOR C_GET_SCORE_COMPS (IN_SCORE_ID NUMBER) IS
2753 SELECT SCORE_COMPONENT_ID
2754 FROM IEX_SCORE_COMPONENTS
2755 WHERE SCORE_ID = IN_SCORE_ID;
2756 --
2757 CURSOR C_get_Score_Comp_Rec (IN_SCORE_COMP_ID NUMBER) is
2758 select SCORE_COMP_WEIGHT,
2759 SCORE_ID,
2760 ENABLED_FLAG,
2761 SCORE_COMP_TYPE_ID
2762 from iex_score_components
2763 where score_component_id = in_score_comp_id;
2764 --
2765 CURSOR C_GET_SCORE_COMP_DET (IN_SCORE_COMP_ID NUMBER) IS
2766 SELECT Score_Comp_Det_id
2767 FROM IEX_SCORE_COMP_DET
2768 WHERE SCORE_COMPONENT_ID = IN_SCORE_COMP_ID;
2769 --
2770 CURSOR C_get_Score_Comp_Det_Rec (IN_SCORE_COMP_Det_ID NUMBER) is
2771 select RANGE_LOW,
2772 RANGE_HIGH,
2773 VALUE,
2774 NEW_VALUE,
2775 SCORE_COMPONENT_ID,
2776 OBJECT_VERSION_NUMBER,
2777 PROGRAM_ID
2778 from iex_score_comp_det
2779 where score_comp_det_id = in_score_comp_det_id;
2780 --
2781 CURSOR C_GET_SCORE_FILTER (IN_SCORE_ID NUMBER) IS
2782 SELECT OBJECT_FILTER_ID,
2783 OBJECT_FILTER_NAME,
2784 OBJECT_ID,
2785 SELECT_COLUMN,
2786 ENTITY_NAME,
2787 ACTIVE_FLAG,
2788 OBJECT_VERSION_NUMBER
2789 FROM IEX_OBJECT_FILTERS
2790 WHERE OBJECT_ID = IN_SCORE_ID
2791 AND OBJECT_FILTER_TYPE = 'IEXSCORE';
2792 --
2793 CURSOR C_GET_SCORE_STATUS (IN_SCORE_ID NUMBER) IS
2794 SELECT DEL_STATUS_ID,
2795 SCORE_VALUE_LOW,
2796 SCORE_VALUE_HIGH,
2797 DEL_STATUS,
2798 SCORE_ID
2799 FROM IEX_DEL_STATUSES
2800 WHERE SCORE_ID = IN_SCORE_ID;
2801 --
2802 l_SCORE_REC IEX_SCORE_PUB.SCORE_REC_TYPE;
2803 l_score_id NUMBER ;
2804 l_len NUMBER ;
2805 l_add NUMBER ;
2806 l_score_name VARCHAR2(256);
2807 l_tmp_score_name VARCHAR2(60);
2808 l_SCORE_COMP_REC IEX_SCORE_PUB.SCORE_COMP_REC_TYPE ;
2809 l_score_comp_id NUMBER ;
2810 l_score_comp_id_new NUMBER ;
2811 l_score_comp_id_tbl DBMS_SQL.NUMBER_TABLE;
2812 l_score_comp_id_new_tbl DBMS_SQL.NUMBER_TABLE;
2813 l_SCORE_COMP_DET_REC IEX_SCORE_PUB.SCORE_COMP_DET_REC_TYPE;
2814 l_score_comp_det_id NUMBER;
2815 idx NUMBER;
2816 newidx NUMBER;
2817 l_FILTER_REC IEX_FILTER_PUB.FILTER_REC_TYPE;
2818 l_filter_id NUMBER;
2819 l_filter_name VARCHAR2(250);
2820 l_row_id VARCHAR2(2000);
2821 --
2822 l_api_name CONSTANT VARCHAR2(30) := 'COPY_SCORINGENGINE_PVT';
2823 l_api_version_number CONSTANT NUMBER := 1.0;
2824 l_return_status VARCHAR2(1);
2825 l_msg_count NUMBER;
2826 l_msg_data VARCHAR2(32767);
2827 l_rowid Varchar2(50);
2828 x_dup_status VARCHAR2(1);
2829
2830 BEGIN
2831 -- Standard Start of API savepoint
2832 SAVEPOINT COPY_SCORINGENGINE_PVT;
2833
2834 -- Standard call to check for call compatibility.
2835 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2836 p_api_version,
2837 l_api_name,
2838 G_PKG_NAME)
2839 THEN
2840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2841 END IF;
2842
2843 -- Initialize message list IF p_init_msg_list is set to TRUE.
2844 IF FND_API.to_Boolean( p_init_msg_list )
2845 THEN
2846 FND_MSG_PUB.initialize;
2847 END IF;
2848
2849
2850 -- Initialize API return status to SUCCESS
2851 x_return_status := FND_API.G_RET_STS_SUCCESS;
2852
2853 --dbms_output.put_line( FND_PROFILE.VALUE('IEX_DEBUG_LEVEL'));
2854 --dbms_output.put_line('debug=' || PG_DEBUG);
2855
2856 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Start');
2857 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: score_id='||p_score_id);
2858
2859 --
2860 -- Api body
2861 --
2862
2863 --
2864 -- Create Scoring Engine
2865
2866 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Create Score');
2867 Open C_Get_SCORE(p_score_id);
2868 Fetch C_Get_Score into
2869 l_score_rec.SCORE_NAME,
2870 l_len,
2871 l_score_rec.SCORE_DESCRIPTION,
2872 l_score_rec.ENABLED_FLAG,
2873 l_score_rec.VALID_FROM_DT,
2874 l_score_rec.VALID_TO_DT,
2875 l_score_rec.CAMPAIGN_SCHED_ID,
2876 l_score_rec.JTF_OBJECT_CODE,
2877 l_score_rec.CONCURRENT_PROG_NAME,
2878 l_score_rec.STATUS_DETERMINATION,
2879 l_score_rec.WEIGHT_REQUIRED,
2880 l_score_rec.SCORE_RANGE_LOW,
2881 l_score_rec.SCORE_RANGE_HIGH,
2882 l_score_rec.OUT_OF_RANGE_RULE;
2883
2884
2885 IF ( C_Get_Score%NOTFOUND) THEN
2886 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Score notfound');
2887 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
2888 FND_MESSAGE.Set_Token ('INFO', 'iex_Score', FALSE);
2889 FND_MSG_PUB.Add;
2890 RAISE FND_API.G_EXC_ERROR;
2891 END IF;
2892
2893 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: len(scrname)= '||l_len);
2894 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: scrname= '||l_score_rec.score_name);
2895
2896 -- Validate ScoreName
2897 -- 1.if the new scorename exists,
2898 -- then add extra 'Copy of ' to the score name;
2899 -- 2.in db, the max len of score_name is 256, and len('Copy Of ')= 8.
2900 -- so the org score_name cannot bigger than 248.
2901
2902 x_dup_status := IEX_DUPLICATE_NAME;
2903 l_add := 0;
2904 l_score_name := l_score_rec.score_name;
2905 WHILE x_dup_status = IEX_DUPLICATE_NAME
2906 LOOP
2907 EXIT when x_dup_status <> IEX_DUPLICATE_NAME ;
2908
2909 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: ScoreName Loop');
2910 if (l_len > 248) then
2911 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: ScoreName > 256');
2912 l_tmp_score_name := 'Copy of ' || l_score_name;
2913 FND_MESSAGE.Set_Name('IEX', 'IEX_API_LEN_ERR');
2914 FND_MESSAGE.Set_Token('COLUMN', 'SCORE_NAME', FALSE);
2915 FND_MESSAGE.Set_Token('VALUE', l_tmp_score_Name, FALSE);
2916 FND_MESSAGE.Set_Token ('LEN', '256', FALSE);
2917 FND_MSG_PUB.Add;
2918 x_dup_status := '';
2919 --x_score_id := l_add;
2920 x_return_status := FND_API.G_RET_STS_ERROR;
2921 GOTO END_COPY;
2922 else
2923 l_score_name := 'Copy Of ' || l_score_name;
2924 l_add := l_add + 1;
2925 l_len := l_len + 8;
2926 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Validate ScoreName');
2927 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: ScoreName= '||l_score_name);
2928 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: len(scrname)= '||l_len);
2929 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: l_add= '||l_add);
2930
2931 Validate_Score_Name(P_Init_Msg_List => FND_API.G_FALSE,
2932 P_SCORE_Name => l_score_name,
2933 P_SCORE_ID => 0,
2934 X_Dup_Status => x_Dup_status,
2935 X_Return_Status => x_return_status,
2936 X_Msg_Count => x_msg_count,
2937 X_Msg_Data => x_msg_data);
2938
2939 WriteLog('iexvscrb:Copy_SE:return_status='||x_return_status);
2940 WriteLog('iexvscrb:Copy_SE:dup_status='||x_dup_status);
2941 end if;
2942
2943 END LOOP;
2944
2945
2946 l_score_rec.score_name := l_score_name;
2947 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: ScoreName= '||l_score_name);
2948
2949 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Create Score ');
2950 Create_Score(
2951 p_api_version => p_api_version
2952 , p_init_msg_list => p_init_msg_list
2953 , p_commit => p_commit
2954 , p_score_rec => l_score_rec
2955 , x_dup_status => x_dup_status
2956 , x_return_status => x_return_status
2957 , x_msg_count => x_msg_count
2958 , x_msg_data => x_msg_data
2959 , x_score_id => x_score_id);
2960
2961 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: CreateScore Status= '||x_return_status);
2962
2963
2964 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2965 RAISE FND_API.G_EXC_ERROR;
2966 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2968 END IF;
2969
2970 l_score_id := x_score_id;
2971 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: ScoreId= '||l_score_id);
2972
2973 Close C_Get_Score;
2974
2975 --
2976 -- Create Score Components
2977 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Create ScoreComp');
2978 idx := 0;
2979 newidx := 0;
2980 FOR s in C_GET_SCORE_COMPS (p_score_id)
2981 LOOP
2982 idx := idx + 1;
2983 l_score_comp_id_tbl(idx) := s.score_component_id;
2984 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Existing ScoreCompId= '||s.score_component_id);
2985
2986 Open C_Get_Score_Comp_Rec(s.score_component_id);
2987 Fetch C_Get_Score_Comp_Rec into
2988 l_score_comp_rec.SCORE_COMP_WEIGHT,
2989 l_score_comp_rec.SCORE_ID,
2990 l_score_comp_rec.ENABLED_FLAG,
2991 l_score_comp_rec.SCORE_COMP_TYPE_ID;
2992
2993 WriteLog('iexvscrb:Copy_SE: TypeId= '||l_score_comp_rec.score_comp_type_id);
2994 l_score_comp_rec.score_id := l_score_id;
2995
2996 IEX_SCORE_PVT.Create_Score_Comp(
2997 p_api_version => p_api_version
2998 , p_init_msg_list => p_init_msg_list
2999 , p_commit => p_commit
3000 , p_score_comp_rec => l_score_comp_rec
3001 , x_score_comp_id => l_score_comp_id
3002 , x_return_status => x_return_status
3003 , x_msg_count => x_msg_count
3004 , x_msg_data => x_msg_data
3005 );
3006
3007 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: CreateScoreComp Status= '||x_return_status);
3008
3009
3010 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3011 RAISE FND_API.G_EXC_ERROR;
3012 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3013 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3014 END IF;
3015
3016
3017 newidx := newidx + 1;
3018 IF (newidx <> idx) THEN
3019 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3020 END IF;
3021
3022 l_score_comp_id_new_tbl(newidx) := l_score_comp_id;
3023
3024 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: created ScoreCompId= '||l_score_comp_id);
3025 Close C_Get_Score_Comp_Rec;
3026
3027 END LOOP;
3028
3029 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: End Of ScoreComp');
3030 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: ScoreComp Count='||idx);
3031 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: ScoreComp NewRecCnt='||newidx);
3032
3033
3034 --
3035 -- Create Score Component Details
3036
3037 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Create ScoreCompDet');
3038 FOR i in 1..l_score_Comp_id_tbl.count
3039 LOOP
3040 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE:i='|| i);
3041 l_score_comp_id := l_score_comp_id_tbl(i);
3042 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE:old_scrcompid='||l_score_comp_id);
3043 l_score_comp_id_new := l_score_comp_id_new_tbl(i);
3044 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE:new_scrcompid='||l_score_comp_id_new);
3045
3046 FOR s in C_GET_SCORE_COMP_DET (l_score_comp_id)
3047 LOOP
3048 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE:scrcompdet loop');
3049 --l_score_comp_det_rec := IEX_SCORE_PUB.G_MISS_SCORE_COMP_DET_REC;
3050 l_score_comp_det_rec := null;
3051 l_score_comp_det_id := s.score_comp_det_id;
3052 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE:scrcompdetid='||l_score_comp_det_id);
3053 Open C_Get_Score_Comp_Det_Rec(l_score_comp_det_id);
3054 Fetch C_Get_Score_Comp_Det_Rec into
3055 l_score_comp_det_rec.RANGE_LOW,
3056 l_score_comp_det_rec.RANGE_HIGH,
3057 l_score_comp_det_rec.VALUE,
3058 l_score_comp_det_rec.NEW_VALUE,
3059 l_score_comp_det_rec.SCORE_COMPONENT_ID,
3060 l_score_comp_det_rec.OBJECT_VERSION_NUMBER,
3061 l_score_comp_det_rec.PROGRAM_ID;
3062
3063 Close C_Get_Score_Comp_Det_Rec;
3064
3065 l_score_comp_det_rec.score_component_id := l_score_comp_id_new;
3066 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE:scrcompid='||l_score_comp_id_new);
3067
3068 IEX_SCORE_PVT.Create_Score_Comp_Det(
3069 p_api_version => p_api_version
3070 , p_init_msg_list => p_init_msg_list
3071 , p_commit => p_commit
3072 , p_score_comp_det_rec => l_score_comp_det_rec
3073 , x_score_comp_det_id => l_score_comp_det_id
3074 , x_return_status => x_return_status
3075 , x_msg_count => x_msg_count
3076 , x_msg_data => x_msg_data
3077 );
3078
3079 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: CreateScoreCompDet Status= '||x_return_status);
3080
3081
3082 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3083 RAISE FND_API.G_EXC_ERROR;
3084 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3085 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3086 END IF;
3087
3088
3089 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: ScoreCompDetId= '||l_score_comp_det_id);
3090
3091 END LOOP;
3092
3093 END LOOP;
3094
3095 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: End Of ScoreCompDet');
3096
3097 --
3098 -- Create Scoring Filter
3099
3100 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Create ScoreFilter');
3101
3102 Open C_Get_SCORE_FILTER(p_score_id);
3103 Fetch C_Get_SCORE_FILTER into
3104 l_filter_rec.OBJECT_FILTER_ID,
3105 l_filter_rec.OBJECT_FILTER_NAME,
3106 l_filter_rec.OBJECT_ID,
3107 l_filter_rec.SELECT_COLUMN,
3108 l_filter_rec.ENTITY_NAME,
3109 l_filter_rec.ACTIVE_FLAG,
3110 l_filter_rec.OBJECT_VERSION_NUMBER;
3111
3112
3113 IF ( C_Get_SCORE_FILTER%NOTFOUND) THEN
3114 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: SCORE_FILTER notfound');
3115 --FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
3116 --FND_MESSAGE.Set_Token ('INFO', 'iex_SCORE_FILTER', FALSE);
3117 --FND_MSG_PUB.Add;
3118 --RAISE FND_API.G_EXC_ERROR;
3119 ELSE
3120
3121 l_filter_name := 'Copy Of ' || l_filter_rec.object_filter_name;
3122 l_filter_rec.object_filter_name := l_filter_name;
3123 l_filter_rec.object_filter_type := 'IEXSCORE';
3124 l_filter_rec.object_id := l_score_id;
3125
3126 -- Validate FilterName
3127 -- if the new filtername exists,
3128 -- then add extra 'Copy of ' to the filter name;
3129 x_dup_status := IEX_DUPLICATE_NAME;
3130 WHILE x_dup_status = IEX_DUPLICATE_NAME
3131 LOOP
3132 EXIT when x_dup_status <> IEX_DUPLICATE_NAME ;
3133
3134 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Validate ScoreName');
3135 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: FilterName= '||l_filter_name);
3136
3137 IEX_FILTER_PUB.Validate_FILTER(
3138 P_FILTER_rec => l_filter_rec,
3139 X_Dup_Status => x_dup_status,
3140 X_Return_Status => x_return_status,
3141 X_Msg_Count => x_msg_count,
3142 X_Msg_Data => x_msg_data);
3143
3144
3145 WriteLog('iexvscrb:Copy_SE:return_status='||x_return_status);
3146 WriteLog('iexvscrb:Copy_SE:dup_status='||x_dup_status);
3147 IF x_dup_status = IEX_DUPLICATE_NAME THEN
3148 l_filter_name := 'Copy Of ' || l_filter_name;
3149 l_filter_rec.object_filter_name := l_filter_name;
3150 END IF;
3151
3152 END LOOP;
3153
3154 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: FilterName= '||l_filter_name);
3155
3156
3157 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Create ScoreFilter ');
3158 IEX_FILTER_PUB.Create_OBJECT_FILTER(
3159 p_api_version => p_api_version
3160 , p_init_msg_list => p_init_msg_list
3161 , p_commit => p_commit
3162 , p_filter_rec => l_filter_rec
3163 , x_dup_status => x_dup_status
3164 , x_return_status => x_return_status
3165 , x_msg_count => x_msg_count
3166 , x_msg_data => x_msg_data
3167 , x_filter_id => l_filter_id);
3168
3169 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: CreateFilter Status= '||x_return_status);
3170
3171
3172 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3173 RAISE FND_API.G_EXC_ERROR;
3174 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3176 END IF;
3177
3178
3179 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: FilterId= '||l_filter_id);
3180
3181 END IF;
3182
3183 Close C_Get_Score_Filter;
3184
3185 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: End of ScoreFilter');
3186
3187 --
3188
3189 --
3190 -- Create DEL STATUSES
3191
3192 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Create DelStatus');
3193
3194 FOR d in C_GET_SCORE_STATUS (p_score_id)
3195 LOOP
3196
3197 BEGIN
3198 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Create SCORE_DEL_STATUSES ');
3199 IEX_DEL_STATUSES_PKG.Insert_Row_With_Defaults(
3200 x_rowid => l_row_id
3201 , p_score_value_low => d.score_value_low
3202 , p_score_value_high => d.score_value_high
3203 , p_del_status => d.del_status
3204 , p_score_id => l_score_id
3205 , commit_flag => p_commit);
3206
3207 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: Create DelStatus: '||l_row_id);
3208 EXCEPTION
3209 WHEN OTHERS THEN
3210 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: exc exp:'||SQLERRM);
3211 RAISE FND_API.G_EXC_ERROR;
3212 END ;
3213 END LOOP;
3214
3215
3216 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: End of ScoreSTATUS');
3217
3218 --
3219 <<END_COPY>>
3220
3221 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: END_COPY');
3222 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: x_score_id='||x_score_id);
3223 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3224 RAISE FND_API.G_EXC_ERROR;
3225 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3227 END IF;
3228
3229
3230 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: End');
3231
3232 --
3233 -- End of API body
3234 --
3235
3236 -- Standard check for p_commit
3237 IF FND_API.to_Boolean( p_commit )
3238 THEN
3239 COMMIT WORK;
3240 END IF;
3241
3242
3243 FND_MSG_PUB.Count_And_Get
3244 ( p_count => x_msg_count,
3245 p_data => x_msg_data );
3246
3247 EXCEPTION
3248 WHEN FND_API.G_EXC_ERROR THEN
3249 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: exc exp:'||SQLERRM);
3250 ROLLBACK To Copy_ScoringEngine_PVT;
3251 x_return_status := FND_API.G_RET_STS_ERROR;
3252 FND_MSG_PUB.Count_And_Get
3253 ( p_count => x_msg_count,
3254 p_data => x_msg_data );
3255
3256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3257 ROLLBACK To Copy_ScoringEngine_PVT;
3258 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: unexc exp:'||SQLERRM);
3259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3260 FND_MSG_PUB.Count_And_Get
3261 ( p_count => x_msg_count,
3262 p_data => x_msg_data );
3263
3264 WHEN OTHERS THEN
3265 ROLLBACK To Copy_ScoringEngine_PVT;
3266 IEX_SCORE_PVT.WriteLog('iexvscrb:Copy_SE: other exp:'||SQLERRM);
3267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3268 FND_MSG_PUB.Count_And_Get
3269 ( p_count => x_msg_count,
3270 p_data => x_msg_data );
3271
3272 END Copy_ScoringEngine;
3273
3274
3275
3276 Procedure WriteLog (p_msg IN VARCHAR2)
3277 IS
3278 l_debug NUMBER(2) ;
3279 BEGIN
3280
3281 l_debug := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
3282 --dbms_output.put_line(p_msg);
3283 --IF PG_DEBUG < 10 THEN
3284 IF l_debug < 10 THEN
3285 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3286 IEX_DEBUG_PUB.LogMessage(p_msg);
3287 END IF;
3288 END IF;
3289
3290 END WriteLog;
3291
3292
3293 /* this is the main procedure for generating the collections_score for a party (hz_parties level score)
3294 Scoring logic:
3295
3296 1. Enumerate all components for this profile by calling get_components
3297 2. Identify Universe of Customers to Score
3298 3. for each component, execute SQL and get value
3299 4. For each component value, get the details of the component and store the value for that score_comp_detail
3300
3301 */
3302 Procedure Get_Score(p_api_version IN NUMBER := 1.0,
3303 p_init_msg_list IN VARCHAR2 ,
3304 p_commit IN VARCHAR2 ,
3305 p_score_id IN NUMBER ,
3306 x_return_status OUT NOCOPY VARCHAR2,
3307 x_msg_count OUT NOCOPY NUMBER,
3308 x_msg_data OUT NOCOPY VARCHAR2)
3309
3310 IS
3311 l_api_name CONSTANT VARCHAR2(30) := 'Get_Score';
3312 l_api_version_number CONSTANT NUMBER := 1.0;
3313 l_return_status VARCHAR2(1);
3314 l_msg_count NUMBER;
3315 l_msg_data VARCHAR2(32767);
3316
3317 l_score NUMBER;
3318 l_count NUMBER;
3319 l_score_id NUMBER := NULL;
3320 l_score_comp_tbl IEX_SCORE_PUB.SCORE_ENG_COMP_TBL;
3321 l_components_count NUMBER;
3322 i NUMBER := 0;
3323 l_party_id NUMBER;
3324 l_party_count NUMBER := 0;
3325 l_component_score NUMBER;
3326 l_raw_score NUMBER ;
3327 l_running_score NUMBER := 0;
3328 l_rowid VARCHAR2(1000);
3329 l_score_history_id NUMBER;
3330
3331 -- this represents the universe of customers to be scored
3332 CURSOR c_del_parties IS
3333 SELECT DISTINCT PARTY_CUST_ID
3334 --FROM IEX_DELINQUENCIES_ALL
3335 FROM IEX_DELINQUENCIES
3336 WHERE STATUS = 'OPEN';
3337
3338 type t_table is table of number
3339 index by binary_integer;
3340 l_party_tbl t_table;
3341 l_score_tbl t_table;
3342
3343 nCount number;
3344
3345 BEGIN
3346
3347 l_raw_score := IEX_SCORE_PVT.G_MIN_SCORE;
3348
3349 -- Standard Start of API savepoint
3350 SAVEPOINT Get_Score_PVT;
3351
3352 -- Standard call to check for call compatibility.
3353 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3354 p_api_version,
3355 l_api_name,
3356 G_PKG_NAME)
3357 THEN
3358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3359 END IF;
3360
3361 -- Initialize message list IF p_init_msg_list is set to TRUE.
3362 IF FND_API.to_Boolean( p_init_msg_list )
3363 THEN
3364 FND_MSG_PUB.initialize;
3365 END IF;
3366
3367 -- Debug Message
3368
3369 -- Initialize API return status to SUCCESS
3370 x_return_status := FND_API.G_RET_STS_SUCCESS;
3371
3372 --
3373 -- Api body
3374 --
3375
3376 /* 1. enumerate all components */
3377 IEX_SCORE_PVT.GET_COMPONENTS(p_score_id => l_score_id,
3378 x_score_comp_tbl => l_score_comp_tbl);
3379
3380 /* 2. get the parties to update */
3381 OPEN c_del_parties;
3382 LOOP
3383 i := i + 1;
3384 FETCH c_del_parties INTO
3385 l_party_id;
3386
3387 l_components_count := l_score_comp_tbl.count;
3388 l_running_score := 0;
3389
3390 /* 3. for each component, execute SQL and get value */
3391 FOR l_count IN 1..l_components_count LOOP
3392 --dbms_output.put_line('Computing Component ' || l_count || ' for party ' || l_party_id);
3393
3394 --dbms_output.put_line ('before dynamic execute ' || l_score_comp_tbl(l_count).SCORE_COMPONENT_ID || ' ' || l_score_comp_tbl(l_count).SCORE_COMP_VALUE);
3395
3396 -- initialize this to the minimum for any given component
3397 l_raw_score := IEX_SCORE_PVT.G_MIN_SCORE;
3398
3399 /* executing dynamic sql for component */
3400 BEGIN
3401 --DBMS_OUTPUT.PUT_LINE('before execute immediate');
3402 EXECUTE IMMEDIATE l_score_comp_tbl(l_count).SCORE_COMP_VALUE
3403 INTO l_component_score
3404 USING l_party_id;
3405
3406 EXCEPTION
3407 -- place holder -> how do we deal with no rows returned? WHEN NOT NVL
3408 WHEN NO_DATA_FOUND THEN
3409 --dbms_output.put_line('error here party = ' || l_party_id);
3410 l_component_score := 0;
3411 WHEN OTHERS THEN
3412 l_component_score := 0;
3413 END;
3414
3415 /* 4. For each component value, get the details of the component and store the value for that score_comp_detail */
3416 BEGIN
3417 --dbms_output.put_line('before get raw score');
3418 SELECT VALUE INTO l_raw_score
3419 FROM iex_score_comp_det
3420 WHERE score_component_id = l_score_comp_tbl(l_count).SCORE_COMPONENT_ID
3421 AND l_component_score >= RANGE_LOW
3422 AND l_component_score <= RANGE_HIGH;
3423
3424 --dbms_output.put_line('raw score = ' || l_raw_score);
3425 --dbms_output.put_line('prev score = ' || l_running_score);
3426 --dbms_output.put_line('to add = ' || (l_raw_score * l_score_comp_tbl(l_count).SCORE_COMP_WEIGHT));
3427
3428 l_running_score := l_running_score + (l_raw_score * l_score_comp_tbl(l_count).SCORE_COMP_WEIGHT);
3429 --dbms_output.put_line('party_id = ' || l_party_id || ' => running score = ' || l_running_score);
3430 EXCEPTION
3431 WHEN NO_DATA_FOUND THEN
3432 --l_component_score := 0;
3433 l_running_score := l_running_score;
3434 WHEN OTHERS THEN
3435 --l_component_score := 0;
3436 l_running_score := l_running_score;
3437 END;
3438
3439 END LOOP;
3440
3441 -- if the score value falls above or below the hard coded floor / ceiling we will force the score
3442 -- to the floor or ceiling
3443 if l_running_score < IEX_SCORE_PVT.G_MIN_SCORE then
3444 l_running_score := IEX_SCORE_PVT.G_MIN_SCORE;
3445 elsif l_running_score > IEX_SCORE_PVT.G_MAX_SCORE then
3446 l_running_score := IEX_SCORE_PVT.G_MAX_SCORE;
3447 end if;
3448
3449 --dbms_output.put_line('party_id = ' || l_party_id || ' FINAL score = ' || l_running_score);
3450 /* UPDATE IEX_SCORE_HISTORIES with the collections score, score_id, and last_score_date */
3451 /*
3452 SELECT IEX_SCORE_HISTORIES_S.nextval
3453 INTO l_score_history_id
3454 FROM dual;
3455
3456 IEX_SCORE_HISTORIES_PKG.Insert_Row(X_ROWID => l_rowid,
3457 P_SCORE_HISTORY_ID => l_score_history_id,
3458 P_OBJECT_VERSION_NUMBER => 1,
3459 P_PROGRAM_ID => 1,
3460 P_LAST_UPDATE_DATE => sysdate,
3461 P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
3462 P_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID,
3463 P_CREATION_DATE => sysdate,
3464 P_CREATED_BY => FND_GLOBAL.USER_ID,
3465 P_SCORE_VALUE => l_running_score,
3466 P_SCORE_ID => l_score_id,
3467 P_PARTY_ID => l_party_id);
3468 */
3469
3470 l_party_tbl(i) := l_party_id;
3471 l_score_tbl(i) := l_running_score;
3472
3473 EXIT WHEN c_del_parties%NOTFOUND;
3474
3475 END LOOP;
3476
3477 nCount := l_party_tbl.count;
3478
3479 FORALL n in 1..nCount
3480 insert into iex_score_histories(SCORE_HISTORY_ID
3481 ,OBJECT_VERSION_NUMBER
3482 ,PROGRAM_ID
3483 ,LAST_UPDATE_DATE
3484 ,LAST_UPDATED_BY
3485 ,LAST_UPDATE_LOGIN
3486 ,CREATION_DATE
3487 ,CREATED_BY
3488 ,SCORE_VALUE
3489 ,SCORE_ID
3490 ,PARTY_ID)
3491 values(IEX_SCORE_HISTORIES_S.nextval
3492 ,1
3493 ,1
3494 ,sysdate
3495 ,FND_GLOBAL.USER_ID
3496 ,FND_GLOBAL.USER_ID
3497 ,sysdate
3498 ,FND_GLOBAL.USER_ID
3499 ,l_score_tbl(n)
3500 ,l_score_id
3501 ,l_party_tbl(n));
3502
3503 l_party_count := i - 1;
3504 --dbms_output.put_line('parties to update = ' || l_party_count);
3505
3506 CLOSE c_del_parties;
3507 --
3508 -- End of API body
3509 --
3510
3511 -- Standard check for p_commit
3512 IF FND_API.to_Boolean(p_commit)
3513 THEN
3514 COMMIT WORK;
3515 END IF;
3516
3517 -- Debug Message
3518
3519 FND_MSG_PUB.Count_And_Get
3520 ( p_count => x_msg_count,
3521 p_data => x_msg_data );
3522
3523 EXCEPTION
3524 WHEN FND_API.G_EXC_ERROR THEN
3525 as_utility_pvt.HANDLE_EXCEPTIONS(
3526 P_API_NAME => L_API_NAME
3527 ,P_PKG_NAME => G_PKG_NAME
3528 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3529 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3530 ,X_MSG_COUNT => X_MSG_COUNT
3531 ,X_MSG_DATA => X_MSG_DATA
3532 ,X_RETURN_STATUS => X_RETURN_STATUS);
3533
3534 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3535 as_utility_pvt.HANDLE_EXCEPTIONS(
3536 P_API_NAME => L_API_NAME
3537 ,P_PKG_NAME => G_PKG_NAME
3538 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3539 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3540 ,X_MSG_COUNT => X_MSG_COUNT
3541 ,X_MSG_DATA => X_MSG_DATA
3542 ,X_RETURN_STATUS => X_RETURN_STATUS);
3543
3544 WHEN OTHERS THEN
3545 as_utility_pvt.HANDLE_EXCEPTIONS(
3546 P_API_NAME => L_API_NAME
3547 ,P_PKG_NAME => G_PKG_NAME
3548 ,P_EXCEPTION_LEVEL => as_utility_pvt.G_EXC_OTHERS
3549 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3550 ,X_MSG_COUNT => X_MSG_COUNT
3551 ,X_MSG_DATA => X_MSG_DATA
3552 ,X_RETURN_STATUS => X_RETURN_STATUS);
3553
3554 END GET_SCORE;
3555
3556 /* this procedure will return the components for a score engine, if no score_id is passed, then it will pick
3557 up the profile IEX_USE_THIS_SCORE to determine the engine to use
3558 */
3559 PROCEDURE Get_Components(P_SCORE_ID IN OUT NOCOPY NUMBER,
3560 X_SCORE_COMP_TBL OUT NOCOPY IEX_SCORE_PUB.SCORE_ENG_COMP_TBL)
3561 IS
3562
3563 l_score_comp_tbl IEX_SCORE_PUB.SCORE_ENG_COMP_TBL;
3564
3565 i NUMBER := 0;
3566 l_score_id NUMBER;
3567 l_score_engine_name VARCHAR2(50);
3568 l_score_comp_id NUMBER;
3569 l_score_component_weight NUMBER(2,2);
3570 l_score_comp_value VARCHAR2(2000);
3571
3572 -- use this to get the valid active score_card_id
3573 CURSOR c_score_name (p_score_name VARCHAR2) IS
3574 SELECT SCORE_ID
3575 FROM IEX_SCORES
3576 WHERE SCORE_NAME = p_score_name AND
3577 ENABLED_FLAG = 'Y' AND
3578 VALID_FROM_DT < sysdate AND
3579 VALID_TO_DT >= sysdate;
3580
3581 CURSOR c_score_id (p_score_id VARCHAR2) IS
3582 SELECT SCORE_ID
3583 FROM IEX_SCORES
3584 WHERE SCORE_ID = p_score_id AND
3585 ENABLED_FLAG = 'Y' AND
3586 VALID_FROM_DT < sysdate AND
3587 VALID_TO_DT >= sysdate;
3588
3589 -- this cursor will enumerate all components for a particular engine
3590 CURSOR c_score_components(p_score_id NUMBER) IS
3591 SELECT
3592 SCORE_COMPONENT_ID,
3593 SCORE_COMP_WEIGHT,
3594 SCORE_COMP_VALUE
3595 FROM
3596 IEX_SCORE_ENG_COMPONENTS_V
3597 WHERE SCORE_ID = p_score_id;
3598 /*
3599 -- allocate the components into v_components
3600 TYPE t_Components_table IS TABLE OF
3601 c_score_components%rowtype INDEX BY binary_integer;
3602 v_components t_Components_table;
3603 */
3604 BEGIN
3605
3606 IF p_score_id IS NULL THEN
3607 /* get the seeded profile option for the bucket name for collections */
3608 fnd_profile.get('IEX_USE_THIS_SCORE', l_score_engine_name);
3609 OPEN c_score_name(l_score_engine_name);
3610 FETCH c_score_name INTO l_score_id;
3611 CLOSE c_score_name;
3612
3613 -- the profile is not set OR the engine is not valid and active -> raise an error
3614 IF l_score_id IS NULL THEN
3615 FND_MESSAGE.Set_Name('IEX', 'IEX_NO_SCORE_ENGINE');
3616 FND_MSG_PUB.Add;
3617 RAISE FND_API.G_EXC_ERROR;
3618 END IF;
3619 ELSE
3620 l_score_id := p_score_id;
3621 END IF;
3622
3623 /* TO DO HERE --> write a engine validator */
3624
3625 /* step 1 enumerate all the components for this engine */
3626 OPEN c_score_components(l_score_id);
3627 LOOP
3628 --WHILE c_score_components%found LOOP
3629 i := i + 1;
3630 FETCH c_score_components INTO
3631 l_score_comp_id, l_score_component_weight, l_score_comp_value;
3632 EXIT WHEN c_score_components%NOTFOUND;
3633
3634 l_score_comp_tbl(i).SCORE_COMPONENT_ID := l_score_comp_id;
3635 l_score_comp_tbl(i).SCORE_COMP_WEIGHT := l_score_component_weight;
3636 l_score_comp_tbl(i).SCORE_COMP_VALUE := l_score_comp_value;
3637 END LOOP;
3638
3639 x_score_comp_tbl := l_score_comp_tbl;
3640 p_score_id := l_score_id;
3641 CLOSE c_score_components;
3642
3643 END Get_Components;
3644
3645 /* this will be called by the concurrent program to score customers
3646 */
3647 Procedure Score_Concur(ERRBUF OUT NOCOPY VARCHAR2,
3648 RETCODE OUT NOCOPY VARCHAR2)
3649 IS
3650
3651 l_return_status VARCHAR2(10);
3652 l_msg_data VARCHAR2(32767);
3653 l_msg_count NUMBER;
3654
3655 BEGIN
3656
3657 IEX_SCORE_PVT.Get_Score(p_api_version => 1.0,
3658 p_init_msg_list => FND_API.G_TRUE,
3659 p_commit => FND_API.G_TRUE,
3660 x_return_status => l_return_status,
3661 x_msg_count => l_msg_count,
3662 x_msg_data => l_msg_data);
3663
3664 RETCODE := l_return_status;
3665 ERRBUF := l_msg_data;
3666
3667
3668 END;
3669
3670 BEGIN
3671 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3672
3673 END IEX_SCORE_PVT;