[Home] [Help]
PACKAGE BODY: APPS.AS_SCORECARD_RULES_PVT
Source
1 PACKAGE BODY AS_SCORECARD_RULES_PVT AS
2 /* $Header: asxvscob.pls 120.1 2005/06/24 17:15:29 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_SCORECARD_RULES_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(16) := 'asxvscdb.pls';
6
7 Procedure Validate_Seed_Qual(
8 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
9 P_Validation_mode IN VARCHAR2,
10 P_CARDRULE_QUAL_rec IN AS_SCORECARD_RULES_PUB.CARDRULE_QUAL_REC_TYPE,
11 X_Return_Status OUT NOCOPY VARCHAR2,
12 X_Msg_Count OUT NOCOPY NUMBER,
13 X_Msg_Data OUT NOCOPY VARCHAR2
14 )
15 IS
16 l_cardrule_qual_rec AS_SCORECARD_RULES_PUB.CARDRULE_QUAL_REC_TYPE
17 := p_cardrule_qual_rec;
18
19 BEGIN
20 -- Initialize message list IF p_init_msg_list is set to TRUE.
21 IF FND_API.to_Boolean( p_init_msg_list )
22 THEN
23 FND_MSG_PUB.initialize;
24 END IF;
25
26 Validate_Seed_Qual_ID(
27 P_Init_Msg_List => FND_API.G_FALSE,
28 P_Validation_mode => P_Validation_mode,
29 P_SEED_QUAL_ID => l_cardrule_qual_rec.seed_qual_id,
30 X_Return_Status => x_return_status,
31 X_Msg_Count => x_msg_count,
32 X_Msg_Data => x_msg_data
33 );
34 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
35 raise FND_API.G_EXC_ERROR;
36 END IF;
37
38 Validate_Seed_Qual_Value_Num(
39 P_Init_Msg_List => FND_API.G_FALSE,
40 P_Validation_mode => P_Validation_mode,
41 P_SEED_QUAL_ID => l_cardrule_qual_rec.seed_qual_id,
42 P_High_value_number => l_cardrule_qual_rec.high_value_number,
43 P_Low_value_number => l_cardrule_qual_rec.low_value_number,
44 X_Return_Status => x_return_status,
45 X_Msg_Count => x_msg_count,
46 X_Msg_Data => x_msg_data
47 );
48 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
49 raise FND_API.G_EXC_ERROR;
50 END IF;
51
52 Validate_Seed_Qual_Value_Char(
53 P_Init_Msg_List => FND_API.G_FALSE,
54 P_Validation_mode => P_Validation_mode,
55 P_SEED_QUAL_ID => l_cardrule_qual_rec.seed_qual_id,
56 P_high_value_char => l_cardrule_qual_rec.high_value_char,
57 P_low_value_char => l_cardrule_qual_rec.low_value_char,
58 X_Return_Status => x_return_status,
59 X_Msg_Count => x_msg_count,
60 X_Msg_Data => x_msg_data
61 );
62 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
63 raise FND_API.G_EXC_ERROR;
64 END IF;
65
66 END Validate_Seed_Qual;
67
68 Procedure Validate_Seed_Qual_ID(
69 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
70 P_Validation_mode IN VARCHAR2,
71 P_SEED_QUAL_ID IN NUMBER,
72 X_Return_Status OUT NOCOPY VARCHAR2,
73 X_Msg_Count OUT NOCOPY NUMBER,
74 X_Msg_Data OUT NOCOPY VARCHAR2
75 )
76 IS
77 CURSOR C_GET_SEED (IN_SEED_QUAL_ID NUMBER) IS
78 SELECT
79 SEED_QUAL_ID
80 FROM AS_SALES_LEAD_QUALS_VL
81 WHERE SEED_QUAL_ID = IN_SEED_QUAL_ID;
82 --
83 l_SEED_QUAL_ID NUMBER;
84
85 BEGIN
86 -- Initialize message list IF p_init_msg_list is set to TRUE.
87 IF FND_API.to_Boolean( p_init_msg_list )
88 THEN
89 FND_MSG_PUB.initialize;
90 END IF;
91
92 -- Initialize API return status to SUCCESS
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94
95 -- Validate P_SEED_QUAL_ID
96 IF P_SEED_QUAL_ID is NOT NULL
97 and P_SEED_QUAL_ID <> FND_API.G_MISS_NUM
98 THEN
99 OPEN C_Get_Seed (p_seed_qual_id);
100 FETCH C_Get_Seed INTO l_SEED_QUAL_ID;
101
102 IF (C_Get_Seed%NOTFOUND)
103 THEN
104 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
105 THEN
106 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
107 FND_MESSAGE.Set_Token('COLUMN', 'SEED_QUAL_ID', FALSE);
108 FND_MESSAGE.Set_Token('VALUE', p_seed_qual_id, FALSE);
109 FND_MSG_PUB.Add;
110 END IF;
111 x_return_status := FND_API.G_RET_STS_ERROR;
112 END IF;
113 CLOSE C_GET_SEED;
114 END IF;
115
116 -- Standard call to get message count and IF count is 1, get message info.
117 FND_MSG_PUB.Count_And_Get
118 ( p_count => x_msg_count,
119 p_data => x_msg_data );
120
121 END Validate_Seed_Qual_ID;
122
123 Procedure Validate_Seed_Qual_Value_Num(
124 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
125 P_Validation_mode IN VARCHAR2,
126 P_SEED_QUAL_ID IN NUMBER,
127 P_High_Value_Number IN NUMBER,
128 P_Low_Value_Number IN NUMBER,
129 X_Return_Status OUT NOCOPY VARCHAR2,
130 X_Msg_Count OUT NOCOPY NUMBER,
131 X_Msg_Data OUT NOCOPY VARCHAR2
132 )
133 IS
134 CURSOR C_GET_SEED (IN_SEED_QUAL_ID NUMBER) IS
135 SELECT
136 SEED_QUAL_ID,
137 UPPER(RANGE_FLAG),
138 UPPER(DATA_TYPE)
139 FROM AS_SALES_LEAD_QUALS_VL
140 WHERE SEED_QUAL_ID = IN_SEED_QUAL_ID;
141
142 l_SEED_QUAL_ID NUMBER;
143 l_RANGE_FLAG VARCHAR2(1);
144 l_DATA_TYPE VARCHAR2(10);
145
146 BEGIN
147 -- Initialize message list IF p_init_msg_list is set to TRUE.
148 IF FND_API.to_Boolean( p_init_msg_list )
149 THEN
150 FND_MSG_PUB.initialize;
151 END IF;
152
153 -- Initialize API return status to SUCCESS
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155
156 -- Validate P_High_Value_Number and P_Low_Value_Number
157 /* Data_Type must be 'NUMBER';
158 * If Range_Flag = 'Y',
159 * then input value should be stored in HIGH_VALUE_NUMBER;
160 * If Range_Flag = 'N',
161 * then input value should be stored in LOW_VALUE_NUMBER;
162 */
163 IF ( P_High_Value_Number is NOT NULL
164 and P_High_Value_Number <> FND_API.G_MISS_NUM ) OR
165 ( P_Low_Value_Number is NOT NULL
166 and P_Low_Value_Number <> FND_API.G_MISS_NUM )
167 THEN
168 OPEN C_Get_Seed (p_seed_qual_id);
169 FETCH C_Get_Seed INTO l_SEED_QUAL_ID,
170 l_RANGE_FLAG,
171 l_DATA_TYPE;
172
173 IF l_DATA_TYPE <> 'NUMBER' THEN
174 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
175 THEN
176 FND_MESSAGE.Set_Name('AS', 'API_INVALID_DATA_TYPE');
177 FND_MESSAGE.Set_Token('COLUMN',
178 'HIGH_VALUE_NUMBER or LOW_VALUE_NUMBER',
179 FALSE);
180 FND_MSG_PUB.Add;
181 END IF;
182 x_return_status := FND_API.G_RET_STS_ERROR;
183 --
184 ELSE
185 IF (l_RANGE_FLAG = 'N') AND
186 (P_HIGH_VALUE_NUMBER is NULL or
187 P_High_Value_Number = FND_API.G_MISS_NUM )
188 THEN
189 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
190 THEN
191 FND_MESSAGE.Set_Name('AS', 'API_MISSING_VALUE');
192 FND_MESSAGE.Set_Token('COLUMN', 'HIGH_VALUE_NUMBER', FALSE);
193 FND_MSG_PUB.Add;
194 END IF;
195 x_return_status := FND_API.G_RET_STS_ERROR;
196 --
197 ELSIF (l_RANGE_FLAG = 'Y') AND
198 (P_LOW_VALUE_NUMBER is NULL or
199 P_Low_Value_Number = FND_API.G_MISS_NUM )
200 THEN
201 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
202 THEN
203 FND_MESSAGE.Set_Name('AS', 'API_MISSING_VALUE');
204 FND_MESSAGE.Set_Token('COLUMN', 'LOW_VALUE_NUMBER', FALSE);
205 FND_MSG_PUB.Add;
206 END IF;
207 x_return_status := FND_API.G_RET_STS_ERROR;
208 END IF;
209 END IF;
210
211 CLOSE C_Get_Seed;
212 END IF;
213
214 -- Standard call to get message count and IF count is 1, get message info.
215 FND_MSG_PUB.Count_And_Get
216 ( p_count => x_msg_count,
217 p_data => x_msg_data );
218
219 END Validate_Seed_Qual_Value_Num;
220
221 Procedure Validate_Seed_Qual_Value_Char(
222 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
223 P_Validation_mode IN VARCHAR2,
224 P_SEED_QUAL_ID IN NUMBER,
225 P_High_Value_Char IN VARCHAR2,
226 P_Low_Value_Char IN VARCHAR2,
227 X_Return_Status OUT NOCOPY VARCHAR2,
228 X_Msg_Count OUT NOCOPY NUMBER,
229 X_Msg_Data OUT NOCOPY VARCHAR2
230 )
231 IS
232 CURSOR C_GET_SEED (IN_SEED_QUAL_ID NUMBER) IS
233 SELECT
234 SEED_QUAL_ID,
235 UPPER(RANGE_FLAG),
236 UPPER(DATA_TYPE)
237 FROM AS_SALES_LEAD_QUALS_VL
238 WHERE SEED_QUAL_ID = IN_SEED_QUAL_ID;
239 --
240 l_SEED_QUAL_ID NUMBER;
241 l_RANGE_FLAG VARCHAR2(1);
242 l_DATA_TYPE VARCHAR2(10);
243
244 BEGIN
245 -- Initialize message list IF p_init_msg_list is set to TRUE.
246 IF FND_API.to_Boolean( p_init_msg_list )
247 THEN
248 FND_MSG_PUB.initialize;
249 END IF;
250
251 -- Initialize API return status to SUCCESS
252 x_return_status := FND_API.G_RET_STS_SUCCESS;
253
254 -- Validate P_High_Value_Char and P_Low_Value_Char
255 /* Data_Type must be 'CHAR';
256 * If Range_Flag = 'Y',
257 * then input value should be stored in HIGH_VALUE_CHAR;
258 * If Range_Flag = 'N',
259 * then input value should be stored in LOW_VALUE_CHAR;
260 */
261 IF ( P_High_Value_Char is NOT NULL
262 and P_High_Value_Char <> FND_API.G_MISS_CHAR ) OR
263 ( P_Low_Value_Char is NOT NULL
264 and P_Low_Value_Char <> FND_API.G_MISS_CHAR )
265 THEN
266
267 OPEN C_Get_Seed (p_seed_qual_id);
268 FETCH C_Get_Seed INTO l_SEED_QUAL_ID,
269 l_RANGE_FLAG,
270 l_DATA_TYPE;
271
272 IF l_DATA_TYPE <> 'VARCHAR2' THEN
273 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
274 THEN
275 FND_MESSAGE.Set_Name('AS', 'API_INVALID_DATA_TYPE');
276 FND_MESSAGE.Set_Token('COLUMN',
277 'HIGH_VALUE_CHAR or LOW_VALUE_CHAR',
278 FALSE);
279 FND_MSG_PUB.Add;
280 END IF;
281 x_return_status := FND_API.G_RET_STS_ERROR;
282 --
283 ELSE
284 IF (l_RANGE_FLAG = 'N') AND
285 (P_HIGH_VALUE_CHAR is NULL or
286 P_High_Value_Char = FND_API.G_MISS_CHAR )
287 THEN
288 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
289 THEN
290 FND_MESSAGE.Set_Name('AS', 'API_MISSING_VALUE');
291 FND_MESSAGE.Set_Token('COLUMN', 'HIGH_VALUE_CHAR', FALSE);
292 FND_MSG_PUB.Add;
293 END IF;
294 x_return_status := FND_API.G_RET_STS_ERROR;
295
296 ELSIF (l_RANGE_FLAG = 'Y') AND
297 (P_LOW_VALUE_CHAR is NULL or
298 P_Low_Value_Char = FND_API.G_MISS_CHAR )
299 THEN
300 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
301 THEN
302 FND_MESSAGE.Set_Name('AS', 'API_MISSING_VALUE');
303 FND_MESSAGE.Set_Token('COLUMN', 'LOW_VALUE_CHAR', FALSE);
304 FND_MSG_PUB.Add;
305 END IF;
306 x_return_status := FND_API.G_RET_STS_ERROR;
307 END IF;
308 END IF;
309
310 CLOSE C_Get_Seed;
311 END IF;
312
313 -- Standard call to get message count and IF count is 1, get message info.
314 FND_MSG_PUB.Count_And_Get
315 ( p_count => x_msg_count,
316 p_data => x_msg_data );
317
318 END Validate_Seed_Qual_Value_Char;
319
320 Procedure Validate_Seed_Qual_Value_Date(
321 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
322 P_Validation_mode IN VARCHAR2,
323 P_SEED_QUAL_ID IN NUMBER,
324 P_High_Value_Date IN DATE,
325 P_Low_Value_Date IN DATE,
326 X_Return_Status OUT NOCOPY VARCHAR2,
327 X_Msg_Count OUT NOCOPY NUMBER,
328 X_Msg_Data OUT NOCOPY VARCHAR2
329 )
330 IS
331 CURSOR C_GET_SEED (IN_SEED_QUAL_ID NUMBER) IS
332 SELECT
333 SEED_QUAL_ID,
334 UPPER(RANGE_FLAG),
335 UPPER(DATA_TYPE)
336 FROM AS_SALES_LEAD_QUALS_VL
337 WHERE SEED_QUAL_ID = IN_SEED_QUAL_ID;
338 --
339 l_SEED_QUAL_ID NUMBER;
340 l_RANGE_FLAG VARCHAR2(1);
341 l_DATA_TYPE VARCHAR2(10);
342
343 BEGIN
344 -- Initialize message list IF p_init_msg_list is set to TRUE.
345 IF FND_API.to_Boolean( p_init_msg_list )
346 THEN
347 FND_MSG_PUB.initialize;
348 END IF;
349
350 -- Initialize API return status to SUCCESS
351 x_return_status := FND_API.G_RET_STS_SUCCESS;
352
353 -- Validate P_High_Value_Date and P_Low_Value_Date
354 /* Data_Type must be 'DATE';
355 * If Range_Flag = 'Y',
356 * then input value should be stored in HIGH_Value_Date;
357 * If Range_Flag = 'N',
358 * then input value should be stored in LOW_Value_Date;
359 */
360
361 IF ( P_High_Value_Date is NOT NULL
362 and P_High_Value_Date <> FND_API.G_MISS_DATE ) OR
363 ( P_Low_Value_Date is NOT NULL
364 and P_Low_Value_Date <> FND_API.G_MISS_DATE )
365 THEN
366 OPEN C_Get_Seed (p_seed_qual_id);
367 FETCH C_Get_Seed INTO l_SEED_QUAL_ID,
368 l_RANGE_FLAG,
369 l_DATA_TYPE;
370
371 IF l_DATA_TYPE <> 'DATE' THEN
372 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
373 THEN
374 FND_MESSAGE.Set_Name('AS', 'API_INVALID_DATA_TYPE');
375 FND_MESSAGE.Set_Token('COLUMN',
376 'HIGH_Value_Date or LOW_Value_Date',
377 FALSE);
378 FND_MSG_PUB.Add;
379 END IF;
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 --
382 ELSE
383 IF (l_RANGE_FLAG = 'Y') AND
384 (P_HIGH_Value_Date is NULL or
385 P_High_Value_Date = FND_API.G_MISS_DATE )
386 THEN
387 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
388 THEN
389 FND_MESSAGE.Set_Name('AS', 'API_MISSING_VALUE');
390 FND_MESSAGE.Set_Token('COLUMN', 'HIGH_Value_Date', FALSE);
391 FND_MSG_PUB.Add;
392 END IF;
393 x_return_status := FND_API.G_RET_STS_ERROR;
394 --
395 ELSIF (l_RANGE_FLAG = 'N') AND
396 (P_LOW_Value_Date is NULL or
397 P_LOW_Value_Date = FND_API.G_MISS_DATE )
398 THEN
399 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
400 THEN
401 FND_MESSAGE.Set_Name('AS', 'API_MISSING_VALUE');
402 FND_MESSAGE.Set_Token('COLUMN', 'LOW_Value_Date', FALSE);
403 FND_MSG_PUB.Add;
404 END IF;
405 x_return_status := FND_API.G_RET_STS_ERROR;
406 END IF;
407 END IF;
408
409 CLOSE C_Get_Seed;
410 END IF;
411
412 -- Standard call to get message count and IF count is 1, get message info.
413 FND_MSG_PUB.Count_And_Get
414 ( p_count => x_msg_count,
415 p_data => x_msg_data );
416
417 END Validate_Seed_Qual_Value_Date;
418
419 Procedure Create_ScoreCard (
420 p_api_version IN NUMBER := 2.0,
421 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
422 p_commit IN VARCHAR2 := FND_API.G_FALSE,
423 p_validation_level IN NUMBER := AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
424 x_return_status OUT NOCOPY VARCHAR2,
425 x_msg_count OUT NOCOPY NUMBER,
426 x_msg_data OUT NOCOPY VARCHAR2,
427 P_SCORECARD_REC IN AS_SCORECARD_RULES_PUB.SCORECARD_REC_TYPE
428 := AS_SCORECARD_RULES_PUB.G_MISS_SCORECARD_REC,
429 X_SCORECARD_ID OUT NOCOPY NUMBER)
430 IS
431 CURSOR C_GET_SCORECARD_ID IS
432 SELECT AS_SALES_LEAD_SCORECARDS_S.NEXTVAL
433 FROM DUAL;
434 --
435 l_api_name CONSTANT VARCHAR2(30) := 'Create_ScoreCard';
436 l_api_version_number CONSTANT NUMBER := 2.0;
437 l_scorecard_id NUMBER;
438 l_qual_value_id NUMBER;
439 l_rowid VARCHAR2(50);
440 l_SALES_LEAD_SCORECARD_rec AS_SCORECARD_RULES_PUB.SCORECARD_REC_TYPE;
441 /*l_CardRule_Qual_rec AS_SCORECARD_RULES_PUB.CARDRULE_QUAL_REC_TYPE;*/
442
443
444 BEGIN
445 -- Standard Start of API savepoint
446 SAVEPOINT CREATE_SCORECARD_PVT;
447
448 -- Standard call to check for call compatibility.
449 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
450 p_api_version,
451 l_api_name,
452 G_PKG_NAME)
453 THEN
454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
455 END IF;
456
457
458 -- Initialize message list if p_init_msg_list is set to TRUE.
459 IF FND_API.to_Boolean( p_init_msg_list )
460 THEN
461 FND_MSG_PUB.initialize;
462 END IF;
463
464 -- Debug Message
465 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
466 'PVT: ' || l_api_name || ' start');
467
468 -- Initialize API return status to SUCCESS
469 x_return_status := FND_API.G_RET_STS_SUCCESS;
470
471 --
472 -- API body
473 --
474 IF FND_GLOBAL.User_Id IS NULL
475 THEN
476 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
477 THEN
478 FND_MESSAGE.Set_Name('AS',
479 'UT_CANNOT_GET_PROFILE_VALUE');
480 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
481 FND_MSG_PUB.ADD;
482 END IF;
483 RAISE FND_API.G_EXC_ERROR;
484 END IF;
485
486 l_SALES_LEAD_SCORECARD_rec := p_scorecard_rec;
487
488 OPEN C_GET_SCORECARD_ID;
489 FETCH C_GET_SCORECARD_ID into l_scorecard_id;
490 CLOSE C_GET_SCORECARD_ID;
491
492 -- Debug Message
493 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
494 'Calling SCORECARDS_Insert_Row');
495
496 -- disable all other scoreCards if this one has enabled_flag = 'Y'
497 if nvl(l_sales_lead_scorecard_rec.start_date_active, sysdate) < trunc(sysdate) then
498 -- Start Date should be today or in the future
499 AS_UTILITY_PVT.set_message(
500 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
501 p_msg_name => 'SCD_INVALID_START' );
502 x_return_status := FND_API.G_RET_STS_ERROR;
503
504 elsif ((l_sales_lead_scorecard_rec.end_date_active <
505 l_sales_lead_scorecard_rec.start_date_active) or (l_sales_lead_scorecard_rec.end_date_active is not
506 null and l_sales_lead_scorecard_rec.start_date_active is null)) then
507 -- End Date should be greater than Start Date
508 AS_UTILITY_PVT.set_message(
509 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
510 p_msg_name => 'SCD_INVALID_END' );
511 x_return_status := FND_API.G_RET_STS_ERROR;
512
513 else -- valid Start and End Dates for the scorecard
514
515 -- Invoke table handler(Sales_Lead_Insert_Row)
516 AS_SALES_LEAD_SCORECARDS_PKG.Insert_Row(
517 x_rowid => l_rowid
518 , x_scorecard_id => l_scorecard_id
519 , x_last_update_date => SYSDATE
520 , x_last_updated_by => FND_GLOBAL.USER_ID
521 , x_creation_date => SYSDATE
522 , x_created_by => FND_GLOBAL.USER_ID
523 , x_last_update_login => FND_GLOBAL.USER_ID
524 , x_description => l_SALES_LEAD_SCORECARD_rec.description
525 , x_enabled_flag => l_SALES_LEAD_SCORECARD_rec.enabled_flag
526 , x_start_date_active => l_SALES_LEAD_SCORECARD_rec.start_date_active
527 , x_end_date_active => l_SALES_LEAD_SCORECARD_rec.end_date_active
528 );
529
530 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
531 RAISE FND_API.G_EXC_ERROR;
532 END IF;
533
534 X_SCORECARD_ID := l_scorecard_id;
535 -- End of API body
536 --
537
538 -- Standard check for p_commit
539 IF FND_API.to_Boolean( p_commit )
540 THEN
541 COMMIT WORK;
542 END IF;
543 end if; -- end of if-then-else checking for Start and End Dates
544
545 -- Debug Message
546 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
547 'PVT: ' || l_api_name || ' end');
548
549
550 -- Standard call to get message count and if count is 1, get message info.
551 FND_MSG_PUB.Count_And_Get
552 ( p_count => x_msg_count,
553 p_data => x_msg_data
554 );
555
556 EXCEPTION
557 WHEN FND_API.G_EXC_ERROR THEN
558 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
559 P_API_NAME => L_API_NAME
560 ,P_PKG_NAME => G_PKG_NAME
561 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
562 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
563 ,X_MSG_COUNT => X_MSG_COUNT
564 ,X_MSG_DATA => X_MSG_DATA
565 ,X_RETURN_STATUS => X_RETURN_STATUS);
566
567 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
568 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
569 P_API_NAME => L_API_NAME
570 ,P_PKG_NAME => G_PKG_NAME
571 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
572 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
573 ,X_MSG_COUNT => X_MSG_COUNT
574 ,X_MSG_DATA => X_MSG_DATA
575 ,X_RETURN_STATUS => X_RETURN_STATUS);
576
577 WHEN OTHERS THEN
578 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
579 P_API_NAME => L_API_NAME
580 ,P_PKG_NAME => G_PKG_NAME
581 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
582 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
583 ,X_MSG_COUNT => X_MSG_COUNT
584 ,X_MSG_DATA => X_MSG_DATA
585 ,X_RETURN_STATUS => X_RETURN_STATUS);
586
587
588 END CREATE_SCORECARD;
589
590 Procedure Update_ScoreCard (
591 p_api_version IN NUMBER := 2.0,
592 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
593 p_commit IN VARCHAR2 := FND_API.G_FALSE,
594 p_validation_level IN NUMBER := AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
595 x_return_status OUT NOCOPY VARCHAR2,
596 x_msg_count OUT NOCOPY NUMBER,
597 x_msg_data OUT NOCOPY VARCHAR2,
598 P_SCORECARD_REC IN AS_SCORECARD_RULES_PUB.SCORECARD_REC_TYPE
599 := AS_SCORECARD_RULES_PUB.G_MISS_SCORECARD_REC)
600
601 IS
602 CURSOR C_GET_LEAD_SCORECARD (IN_SCORECARD_ID NUMBER) IS
603 SELECT
604 ROWID,
605 SCORECARD_ID,
606 LAST_UPDATE_DATE,
607 LAST_UPDATED_BY,
608 CREATION_DATE,
609 CREATED_BY,
610 LAST_UPDATE_LOGIN,
611 DESCRIPTION,
612 ENABLED_FLAG,
613 START_DATE_ACTIVE,
614 END_DATE_ACTIVE
615 FROM AS_SALES_LEAD_SCORECARDS
616 WHERE SCORECARD_ID = IN_SCORECARD_ID
617 FOR UPDATE NOWAIT;
618
619 CURSOR C_CHK_SCORECARD_FOR_DISABLE (IN_SCORECARD_ID NUMBER) IS
620 SELECT fpo.PROFILE_OPTION_ID,
621 fpo.PROFILE_OPTION_NAME,
622 fpo.USER_PROFILE_OPTION_NAME
623 FROM FND_PROFILE_OPTIONS_VL fpo,
624 FND_PROFILE_OPTION_VALUES fpov
625 WHERE fpo.PROFILE_OPTION_NAME = 'AS_DEFAULT_SCORECARD'
626 AND fpo.PROFILE_OPTION_ID = fpov.PROFILE_OPTION_ID
627 AND fpov.profile_option_value = IN_SCORECARD_ID;
628
629 --
630 l_api_name CONSTANT VARCHAR2(30) := 'Update_ScoreCard';
631 l_api_version_number CONSTANT NUMBER := 2.0;
632 l_scorecard_id NUMBER;
633 l_qual_value_id NUMBER;
634 l_rowid VARCHAR2(50);
635 l_SALES_LEAD_SCORECARD_rec AS_SCORECARD_RULES_PUB.SCORECARD_REC_TYPE
636 := p_scorecard_rec;
637 l_REF_SALES_LEAD_SCORECARD_rec AS_SCORECARD_RULES_PUB.SCORECARD_REC_TYPE;
638 /*
639 l_CardRule_Qual_rec AS_SCORECARD_RULES_PUB.CARDRULE_QUAL_REC_TYPE;
640 */
641 l_profile_option_id NUMBER;
642 l_profile_option_nm VARCHAR2(80);
643 l_user_profile_option_nm VARCHAR2(240);
644
645 BEGIN
646
647 -- Standard Start of API savepoint
648 SAVEPOINT UPDATE_SCORECARD_PVT;
649
650 -- Standard call to check for call compatibility.
651 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
652 p_api_version,
653 l_api_name,
654 G_PKG_NAME)
655 THEN
656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
657 END IF;
658
659 -- Initialize message list if p_init_msg_list is set to TRUE.
660 IF FND_API.to_Boolean( p_init_msg_list )
661 THEN
662 FND_MSG_PUB.initialize;
663 END IF;
664
665 -- Debug Message
666 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
667 'PVT: ' || l_api_name || ' start');
668
669 -- Initialize API return status to SUCCESS
670 x_return_status := FND_API.G_RET_STS_SUCCESS;
671
672 --
673 -- Api body
674 --
675 /*
676 IF(P_Check_Access_Flag = 'Y') THEN
677 END IF;
678
679 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
680 RAISE FND_API.G_EXC_ERROR;
681 END IF;
682 */
683 -- Debug Message
684 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
685 'Open C_Get_sales_lead_scorecard');
686
687 Open C_Get_Lead_Scorecard(l_SALES_LEAD_SCORECARD_rec.SCORECARD_ID);
688 Fetch C_Get_Lead_Scorecard into
689 l_rowid,
690 l_REF_SALES_LEAD_SCORECARD_rec.SCORECARD_ID,
691 l_REF_SALES_LEAD_SCORECARD_rec.LAST_UPDATE_DATE,
692 l_REF_SALES_LEAD_SCORECARD_rec.LAST_UPDATED_BY,
693 l_REF_SALES_LEAD_SCORECARD_rec.CREATION_DATE,
694 l_REF_SALES_LEAD_SCORECARD_rec.CREATED_BY,
695 l_REF_SALES_LEAD_SCORECARD_rec.LAST_UPDATE_LOGIN,
696 l_REF_SALES_LEAD_SCORECARD_rec.DESCRIPTION,
697 l_REF_SALES_LEAD_SCORECARD_rec.ENABLED_FLAG,
698 l_REF_SALES_LEAD_SCORECARD_rec.START_DATE_ACTIVE,
699 l_REF_SALES_LEAD_SCORECARD_rec.END_DATE_ACTIVE;
700
701 If ( C_Get_Lead_Scorecard%NOTFOUND) Then
702 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
703 THEN
704 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
705 FND_MESSAGE.Set_Token ('INFO', 'sales_lead_scorecard', FALSE);
706 FND_MSG_PUB.Add;
707 END IF;
708 raise FND_API.G_EXC_ERROR;
709 END IF;
710
711 -- Debug Message
712 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
713 'Close C_Get_lead_Scorecard');
714 Close C_Get_Lead_Scorecard;
715
716 -- Check Whether record has been changed by someone else
717 If (l_SALES_LEAD_SCORECARD_rec.last_update_date is NULL or
718 l_SALES_LEAD_SCORECARD_rec.last_update_date = FND_API.G_MISS_Date )
719 Then
720 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
721 THEN
722 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
723 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
724 FND_MSG_PUB.ADD;
725 END IF;
726 raise FND_API.G_EXC_ERROR;
727 End if;
728
729 if (((l_sales_lead_scorecard_rec.start_date_active < trunc(sysdate)) and
730 ((l_sales_lead_scorecard_rec.start_date_active <>
731 l_ref_sales_lead_scorecard_rec.start_date_active) or
732 (l_ref_sales_lead_scorecard_rec.start_date_active is null))) AND
733 l_sales_lead_scorecard_rec.start_date_active <> FND_API.G_MISS_DATE) then
734 -- new Start Date should be today or in the future if it has been changed
735 AS_UTILITY_PVT.set_message(
736 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
737 p_msg_name => 'SCD_INVALID_START' );
738 x_return_status := FND_API.G_RET_STS_ERROR;
739
740 elsif (((l_sales_lead_scorecard_rec.end_date_active <
741 l_sales_lead_scorecard_rec.start_date_active) or
742 (l_sales_lead_scorecard_rec.end_date_active is not null and
743 l_sales_lead_scorecard_rec.start_date_active is null) or
744 ((l_sales_lead_scorecard_rec.end_date_active < trunc(sysdate)) and
745 ((l_sales_lead_scorecard_rec.end_date_active <>
746 l_ref_sales_lead_scorecard_rec.end_date_active) or
747 (l_ref_sales_lead_scorecard_rec.end_date_active is null)))) AND
748 l_sales_lead_scorecard_rec.end_date_active <> FND_API.G_MISS_DATE)
749 then
750 -- new End Date should be greater than Start Date and in the future if it has been changed
751 AS_UTILITY_PVT.set_message(
752 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
753 p_msg_name => 'SCD_INVALID_END' );
754 x_return_status := FND_API.G_RET_STS_ERROR;
755
756 else -- valid Start and End Dates for the scorecard
757
758 -- Transfer Data into target record
759 l_SALES_LEAD_SCORECARD_rec.CREATION_DATE :=
760 l_ref_SALES_LEAD_SCORECARD_rec.CREATION_DATE;
761 l_SALES_LEAD_SCORECARD_rec.CREATED_BY :=
762 l_ref_SALES_LEAD_SCORECARD_rec.CREATED_BY;
763 IF (l_SALES_LEAD_SCORECARD_rec.DESCRIPTION = FND_API.G_MISS_CHAR) Then
764 l_SALES_LEAD_SCORECARD_rec.DESCRIPTION :=
765 l_ref_SALES_LEAD_SCORECARD_rec.DESCRIPTION;
766 END IF;
767
768
769
770 IF (l_SALES_LEAD_SCORECARD_rec.START_DATE_ACTIVE = FND_API.G_MISS_DATE) Then
771 l_SALES_LEAD_SCORECARD_rec.START_DATE_ACTIVE :=
772 l_ref_SALES_LEAD_SCORECARD_rec.START_DATE_ACTIVE;
773 END IF;
774 IF (l_SALES_LEAD_SCORECARD_rec.END_DATE_ACTIVE = FND_API.G_MISS_DATE) Then
775 l_SALES_LEAD_SCORECARD_rec.END_DATE_ACTIVE :=
776 l_ref_SALES_LEAD_SCORECARD_rec.END_DATE_ACTIVE;
777 END IF;
778
779 -- Debug Message
780 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
781 'Calling _SCORECARDS_Update_Row');
782
783 -- disable all other scoreCards if this one has enabled_flag = 'Y'
784 /* kmahajan 3/27/01 active_flag validation replaced by date validation
785 If l_sales_lead_scorecard_rec.enabled_flag = 'Y' then
786 Disable_All_ScoreCards;
787 End If;
788 */
789 /* kmahajan 5/1/01 - multiple active scorecards are allowed
790 if l_sales_lead_scorecard_rec.start_date_active is not null then
791 update_scd_dates(l_sales_lead_scorecard_rec.start_date_active,
792 l_sales_lead_scorecard_rec.end_date_active);
793 end if;
794 */
795 /* Code added by Rahul D. Sharma, to check before disabling any scorecard,
796 that it should not be referred in 'AS_LEAD_DEFAULT_SCORECARD' profile. */
797
798 If nvl(l_sales_lead_scorecard_rec.enabled_flag, 'N') <> 'Y' then
799 Open C_Chk_Scorecard_for_Disable(l_SALES_LEAD_SCORECARD_rec.SCORECARD_ID);
800 Fetch C_Chk_Scorecard_for_Disable into
801 l_profile_option_id,
802 l_profile_option_nm,
803 l_user_profile_option_nm;
804
805 If ( C_Chk_Scorecard_for_Disable%FOUND) Then
806 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
807 THEN
808 FND_MESSAGE.Set_Name('AS', 'SET_AS_DEFAULT_SCORECARD');
809 FND_MESSAGE.Set_Token('USERPROFILE',l_user_profile_option_nm, FALSE);
810 FND_MSG_PUB.Add;
811 END IF;
812 Close C_Chk_Scorecard_for_Disable;
813 raise FND_API.G_EXC_ERROR;
814 END IF;
815 Close C_Chk_Scorecard_for_Disable;
816 END IF;
817
818 -- Invoke table handler(Sales_Lead_ScoreCard_Update_Row)
819 AS_SALES_LEAD_SCORECARDS_PKG.Update_Row(
820 x_rowid => l_rowid
821 , x_scorecard_id => l_SALES_LEAD_SCORECARD_rec.scorecard_id
822 , x_last_update_date => SYSDATE
823 , x_last_updated_by => FND_GLOBAL.USER_ID
824 , x_creation_date => l_SALES_LEAD_SCORECARD_rec.creation_date
825 , x_created_by => l_SALES_LEAD_SCORECARD_rec.created_by
826 , x_last_update_login => FND_GLOBAL.USER_ID
827 , x_description => l_SALES_LEAD_SCORECARD_rec.description
828 , x_enabled_flag => l_SALES_LEAD_SCORECARD_rec.enabled_flag
829 , x_start_date_active => l_SALES_LEAD_SCORECARD_rec.start_date_active
830 , x_end_date_active => l_SALES_LEAD_SCORECARD_rec.end_date_active
831 );
832
833 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
834 RAISE FND_API.G_EXC_ERROR;
835 END IF;
836
837 -- Standard check for p_commit
838 IF FND_API.to_Boolean( p_commit )
839 THEN
840 COMMIT WORK;
841 END IF;
842
843 end if; -- end of if-then-else for Start and End dates
844
845 -- Debug Message
846 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
847 'PVT: ' || l_api_name || ' end');
848
849 -- Standard call to get message count and if count is 1, get message info.
850 FND_MSG_PUB.Count_And_Get
851 ( p_count => x_msg_count,
852 p_data => x_msg_data );
853
854 EXCEPTION
855 WHEN FND_API.G_EXC_ERROR THEN
856 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
857 P_API_NAME => L_API_NAME
858 ,P_PKG_NAME => G_PKG_NAME
859 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
860 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
861 ,X_MSG_COUNT => X_MSG_COUNT
862 ,X_MSG_DATA => X_MSG_DATA
863 ,X_RETURN_STATUS => X_RETURN_STATUS);
864
865 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
866 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
867 P_API_NAME => L_API_NAME
868 ,P_PKG_NAME => G_PKG_NAME
869 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
870 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
871 ,X_MSG_COUNT => X_MSG_COUNT
872 ,X_MSG_DATA => X_MSG_DATA
873 ,X_RETURN_STATUS => X_RETURN_STATUS);
874
875 WHEN OTHERS THEN
876 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
877 P_API_NAME => L_API_NAME
878 ,P_PKG_NAME => G_PKG_NAME
879 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
880 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
881 ,X_MSG_COUNT => X_MSG_COUNT
882 ,X_MSG_DATA => X_MSG_DATA
883 ,X_RETURN_STATUS => X_RETURN_STATUS);
884
885 END Update_ScoreCard;
886
887
888
889 /* Only Delete records from as_sales_lead_scorecards;
890 not delete records from as_sales_lead_card_rules;
891 */
892 Procedure Delete_ScoreCard (
893 p_api_version IN NUMBER := 2.0,
894 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
895 p_commit IN VARCHAR2 := FND_API.G_FALSE,
896 p_validation_level IN NUMBER := AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
897 x_return_status OUT NOCOPY VARCHAR2,
898 x_msg_count OUT NOCOPY NUMBER,
899 x_msg_data OUT NOCOPY VARCHAR2,
900 P_SCORECARD_ID IN NUMBER)
901 IS
902 CURSOR C_GET_LEAD_SCORECARD (IN_SCORECARD_ID NUMBER) IS
903 SELECT
904 SCORECARD_ID
905 FROM AS_SALES_LEAD_SCORECARDS
906 WHERE SCORECARD_ID = IN_SCORECARD_ID;
907 --
908 CURSOR C_GET_CARD_RULE (IN_SCORECARD_ID NUMBER) IS
909 SELECT CARD_RULE_ID
910 FROM AS_SALES_LEAD_CARD_RULES
911 WHERE SCORECARD_ID = IN_SCORECARD_ID;
912 --
913 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ScoreCard';
914 l_api_version_number CONSTANT NUMBER := 2.0;
915 l_scorecard_id NUMBER := p_scorecard_id;
916 l_rowid VARCHAR2(50);
917 l_REF_SALES_LEAD_SCORECARD_rec AS_SCORECARD_RULES_PUB.SCORECARD_REC_TYPE;
918
919 BEGIN
920 -- Standard Start of API savepoint
921 SAVEPOINT DELETE_SCORECARD_PVT;
922
923 -- Standard call to check for call compatibility.
924 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
925 p_api_version,
926 l_api_name,
927 G_PKG_NAME)
928 THEN
929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
930 END IF;
931
932 -- Initialize message list if p_init_msg_list is set to TRUE.
933 IF FND_API.to_Boolean( p_init_msg_list )
934 THEN
935 FND_MSG_PUB.initialize;
936 END IF;
937
938 -- Debug Message
939 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
940 'PVT:' || l_api_name || 'start');
941
942 -- Initialize API return status to SUCCESS
943 x_return_status := FND_API.G_RET_STS_SUCCESS;
944
945 --
946 -- Api body
947 --
948 IF FND_GLOBAL.User_Id IS NULL
949 THEN
950 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
951 THEN
952 FND_MESSAGE.Set_Name('AS',
953 'UT_CANNOT_GET_PROFILE_VALUE');
954 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
955 FND_MSG_PUB.ADD;
956 END IF;
957 RAISE FND_API.G_EXC_ERROR;
958 END IF;
959
960 -- Debug Message
961 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
962 'Open C_Get_sales_lead_scorecard');
963
964 Open C_Get_Lead_Scorecard(p_SCORECARD_ID);
965 Fetch C_Get_Lead_Scorecard into
966 l_SCORECARD_ID;
967
968 If ( C_Get_Lead_Scorecard%NOTFOUND) Then
969 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
970 THEN
971 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
972 FND_MESSAGE.Set_Token ('INFO', 'sales_lead_scorecard', FALSE);
973 FND_MSG_PUB.Add;
974 END IF;
975 raise FND_API.G_EXC_ERROR;
976 END IF;
977
978 -- Debug Message
979 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
980 'Close C_Get_scorecard_del');
981 Close C_Get_Lead_Scorecard;
982
983 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
984 'Calling _SCORECARD_Delete_Row');
985
986 -- Invoke table handler
987 AS_SALES_LEAD_SCORECARDS_PKG.Delete_Row(
988 x_scorecard_ID => l_SCORECARD_ID);
989
990 --
991 -- End of API body
992 --
993
994 -- Standard check for p_commit
995 IF FND_API.to_Boolean( p_commit )
996 THEN
997 COMMIT WORK;
998 END IF;
999
1000 -- Debug Message
1001 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1002 'PVT: ' || l_api_name || ' end');
1003 FND_MSG_PUB.Count_And_Get
1004 ( p_count => x_msg_count,
1005 p_data => x_msg_data );
1006
1007 EXCEPTION
1008 WHEN FND_API.G_EXC_ERROR THEN
1009 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1010 P_API_NAME => L_API_NAME
1011 ,P_PKG_NAME => G_PKG_NAME
1012 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1013 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1014 ,X_MSG_COUNT => X_MSG_COUNT
1015 ,X_MSG_DATA => X_MSG_DATA
1016 ,X_RETURN_STATUS => X_RETURN_STATUS);
1017
1018 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1019 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1020 P_API_NAME => L_API_NAME
1021 ,P_PKG_NAME => G_PKG_NAME
1022 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1023 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1024 ,X_MSG_COUNT => X_MSG_COUNT
1025 ,X_MSG_DATA => X_MSG_DATA
1026 ,X_RETURN_STATUS => X_RETURN_STATUS);
1027
1028 WHEN OTHERS THEN
1029 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1030 P_API_NAME => L_API_NAME
1031 ,P_PKG_NAME => G_PKG_NAME
1032 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1033 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1034 ,X_MSG_COUNT => X_MSG_COUNT
1035 ,X_MSG_DATA => X_MSG_DATA
1036 ,X_RETURN_STATUS => X_RETURN_STATUS);
1037
1038 END Delete_ScoreCard;
1039
1040
1041 Procedure Create_CardRule_QUAL
1042 (p_api_version IN NUMBER := 2.0,
1043 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1044 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1045 p_validation_level IN NUMBER :=
1046 AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
1047 x_return_status OUT NOCOPY VARCHAR2,
1048 x_msg_count OUT NOCOPY NUMBER,
1049 x_msg_data OUT NOCOPY VARCHAR2,
1050 p_CardRule_Qual_rec IN AS_SCORECARD_RULES_PUB.CARDRULE_QUAL_REC_TYPE,
1051 x_qual_value_id OUT NOCOPY NUMBER)
1052 IS
1053 CURSOR C_GET_QUAL_VALUE_ID IS
1054 SELECT AS_CARD_RULE_QUAL_VALUES_S.NEXTVAL
1055 FROM DUAL;
1056
1057 l_api_name CONSTANT VARCHAR2(30) := 'Create_CardRule_QUAL';
1058 l_api_version_number CONSTANT NUMBER := 2.0;
1059 l_scorecard_id NUMBER;
1060 l_qual_value_id NUMBER;
1061 l_rowid VARCHAR2(50);
1062 l_CardRule_QUAL_rec AS_SCORECARD_RULES_PUB.CARDRULE_QUAL_REC_TYPE :=
1063 p_cardrule_qual_rec;
1064
1065 BEGIN
1066 -- Standard Start of API savepoint
1067 SAVEPOINT CREATE_CARDRULE_qual_pvt;
1068
1069 -- Standard call to check for call compatibility.
1070 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1071 p_api_version,
1072 l_api_name,
1073 G_PKG_NAME)
1074 THEN
1075 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1076 END IF;
1077
1078
1079 -- Initialize message list if p_init_msg_list is set to TRUE.
1080 IF FND_API.to_Boolean( p_init_msg_list )
1081 THEN
1082 FND_MSG_PUB.initialize;
1083 END IF;
1084
1085 -- Debug Message
1086 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1087 'PVT: ' || l_api_name || ' start');
1088
1089 -- Initialize API return status to SUCCESS
1090 x_return_status := FND_API.G_RET_STS_SUCCESS;
1091
1092 --
1093 -- API body
1094 --
1095 IF FND_GLOBAL.User_Id IS NULL
1096 THEN
1097 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1098 THEN
1099 FND_MESSAGE.Set_Name('AS',
1100 'UT_CANNOT_GET_PROFILE_VALUE');
1101 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
1102 FND_MSG_PUB.ADD;
1103 END IF;
1104 RAISE FND_API.G_EXC_ERROR;
1105 END IF;
1106
1107 IF ( P_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM)
1108 THEN
1109 -- Debug message
1110 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1111 'Calling Validate_Seed_Qual');
1112
1113 -- Invoke validation procedures
1114 Validate_Seed_Qual(
1115 P_Init_Msg_List => FND_API.G_FALSE,
1116 P_Validation_mode => AS_UTILITY_PVT.G_CREATE,
1117 P_CARDRULE_QUAL_rec => l_cardrule_qual_rec,
1118 X_Return_Status => x_return_status,
1119 X_Msg_Count => x_msg_count,
1120 X_Msg_Data => x_msg_data
1121 );
1122 END IF;
1123
1124 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1125 RAISE FND_API.G_EXC_ERROR;
1126 END IF;
1127
1128
1129 OPEN C_GET_QUAL_VALUE_ID;
1130 FETCH C_GET_QUAL_VALUE_ID into l_qual_value_id;
1131 CLOSE C_GET_QUAL_VALUE_ID;
1132 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1133 'seq card qual id'||l_qual_value_id);
1134 -- Debug Message
1135 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1136 'Calling CARDRULE_QUAL_Insert_Row');
1137
1138 -- Invoke table handler(CARDRULE_QUAL_Insert_Row)
1139 AS_CARD_RULE_QUAL_VALUES_PKG.Insert_Row(
1140 x_rowid => l_rowid
1141 , x_qual_value_id => l_qual_value_id
1142 , x_last_update_date => SYSDATE
1143 , x_last_updated_by => FND_GLOBAL.USER_ID
1144 , x_creation_date => SYSDATE
1145 , x_created_by => FND_GLOBAL.USER_ID
1146 , x_last_update_login => FND_GLOBAL.USER_ID
1147 , x_scorecard_id => l_CARDRULE_QUAL_rec.scorecard_id
1148 , x_score => l_CARDRULE_QUAL_rec.score
1149 , x_card_rule_id => 0
1150 , x_seed_qual_id => l_CARDRULE_QUAL_rec.seed_qual_id
1151 , x_high_value_number => l_CARDRULE_QUAL_rec.high_value_number
1152 , x_low_value_number => l_CARDRULE_QUAL_rec.low_value_number
1153 , x_high_value_char => l_CARDRULE_QUAL_rec.high_value_char
1154 , x_low_value_char => l_CARDRULE_QUAL_rec.low_value_char
1155 , x_currency_code => l_CARDRULE_QUAL_rec.currency_code
1156 , x_low_value_date => l_CARDRULE_QUAL_rec.low_value_date
1157 , x_high_value_date => l_CARDRULE_QUAL_rec.high_value_date
1158 , x_start_date_active => l_CARDRULE_QUAL_rec.start_date_active
1159 , x_end_date_active => l_CARDRULE_QUAL_rec.end_date_active
1160 );
1161
1162 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1163 RAISE FND_API.G_EXC_ERROR;
1164 END IF;
1165
1166 X_QUAL_VALUE_ID := l_qual_value_id;
1167 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1168 'after insert '||l_qual_value_id);
1169
1170
1171 --
1172 -- End of API body
1173 --
1174
1175 -- Standard check for p_commit
1176 IF FND_API.to_Boolean( p_commit )
1177 THEN
1178 COMMIT WORK;
1179 END IF;
1180
1181 -- Debug Message
1182 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1183 'PVT: ' || l_api_name || ' end');
1184
1185 -- Standard call to get message count and if count is 1, get message info.
1186 FND_MSG_PUB.Count_And_Get
1187 ( p_count => x_msg_count,
1188 p_data => x_msg_data
1189 );
1190
1191 EXCEPTION
1192 WHEN FND_API.G_EXC_ERROR THEN
1193 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1194 P_API_NAME => L_API_NAME
1195 ,P_PKG_NAME => G_PKG_NAME
1196 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1197 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1198 ,X_MSG_COUNT => X_MSG_COUNT
1199 ,X_MSG_DATA => X_MSG_DATA
1200 ,X_RETURN_STATUS => X_RETURN_STATUS);
1201
1202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1203 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1204 P_API_NAME => L_API_NAME
1205 ,P_PKG_NAME => G_PKG_NAME
1206 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1207 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1208 ,X_MSG_COUNT => X_MSG_COUNT
1209 ,X_MSG_DATA => X_MSG_DATA
1210 ,X_RETURN_STATUS => X_RETURN_STATUS);
1211
1212 WHEN OTHERS THEN
1213 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1214 P_API_NAME => L_API_NAME
1215 ,P_PKG_NAME => G_PKG_NAME
1216 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1217 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1218 ,X_MSG_COUNT => X_MSG_COUNT
1219 ,X_MSG_DATA => X_MSG_DATA
1220 ,X_RETURN_STATUS => X_RETURN_STATUS);
1221
1222 END Create_CardRule_Qual;
1223
1224
1225
1226 Procedure Update_CardRule_QUAL
1227 (p_api_version IN NUMBER := 2.0,
1228 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1229 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1230 p_validation_level IN NUMBER :=
1231 AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
1232 x_return_status OUT NOCOPY VARCHAR2,
1233 x_msg_count OUT NOCOPY NUMBER,
1234 x_msg_data OUT NOCOPY VARCHAR2,
1235 p_CardRule_Qual_rec IN AS_SCORECARD_RULES_PUB.CARDRULE_QUAL_REC_TYPE)
1236 IS
1237 CURSOR C_GET_CARDRULE_QUAL (IN_QUAL_VALUE_ID NUMBER) IS
1238 SELECT
1239 rowid,
1240 QUAL_VALUE_ID
1241 FROM AS_CARD_RULE_QUAL_VALUES
1242 WHERE QUAL_VALUE_ID = IN_QUAL_VALUE_ID
1243 FOR UPDATE NOWAIT;
1244 --
1245 l_api_name CONSTANT VARCHAR2(30) := 'Update_CARDRULE_QUAL';
1246 l_api_version_number CONSTANT NUMBER := 2.0;
1247 l_scorecard_id NUMBER;
1248 l_rowid VARCHAR2(50);
1249 l_CARDRULE_QUAL_rec AS_SCORECARD_RULES_PUB.CARDRULE_QUAL_REC_TYPE
1250 := p_cardrule_qual_rec;
1251 l_ref_CARDRULE_QUAL_rec AS_SCORECARD_RULES_PUB.CARDRULE_QUAL_REC_TYPE;
1252
1253 BEGIN
1254
1255 -- dbms_output.put_line('in update_CardRule_Qual');
1256 -- Standard Start of API savepoint
1257 SAVEPOINT UPDATE_CARDRULE_QUAL_PVT;
1258
1259 -- Standard call to check for call compatibility.
1260 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1261 p_api_version,
1262 l_api_name,
1263 G_PKG_NAME)
1264 THEN
1265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1266 END IF;
1267
1268 -- Initialize message list if p_init_msg_list is set to TRUE.
1269 IF FND_API.to_Boolean( p_init_msg_list )
1270 THEN
1271 FND_MSG_PUB.initialize;
1272 END IF;
1273
1274 -- Debug Message
1275 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1276 'PVT: ' || l_api_name || ' start');
1277
1278 -- Initialize API return status to SUCCESS
1279 x_return_status := FND_API.G_RET_STS_SUCCESS;
1280
1281 --
1282 -- Api body
1283 --
1284
1285 -- Debug Message
1286 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1287 'Open C_Get_CARDRULE_QUAL');
1288
1289 -- dbms_output.put_line('in update_CardRule_Qual body');
1290
1291 Open C_Get_CARDRULE_QUAL (l_CARDRULE_QUAL_rec.QUAL_VALUE_ID);
1292 Fetch C_Get_CARDRULE_QUAL into
1293 l_rowid,
1294 l_REF_CARDRULE_QUAL_rec.QUAL_VALUE_ID;
1295
1296 If ( C_Get_CARDRULE_QUAL%NOTFOUND) Then
1297 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1298 THEN
1299 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
1300 FND_MESSAGE.Set_Token ('INFO', 'CARDRULE_QUAL', FALSE);
1301 FND_MSG_PUB.Add;
1302 END IF;
1303 raise FND_API.G_EXC_ERROR;
1304 END IF;
1305
1306 -- Debug Message
1307 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1308 'Close C_Get_CARDRULE_QUAL');
1309 Close C_Get_CARDRULE_QUAL;
1310
1311
1312 -- Check Whether record has been changed by someone else
1313 If (l_CARDRULE_QUAL_rec.last_update_date is NULL or
1314 l_CARDRULE_QUAL_rec.last_update_date = FND_API.G_MISS_Date )
1315 Then
1316 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1317 THEN
1318 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1319 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
1320 FND_MSG_PUB.ADD;
1321 END IF;
1322 raise FND_API.G_EXC_ERROR;
1323 End if;
1324
1325 -- Transfer Data into target record
1326 IF (l_CARDRULE_QUAL_rec.CARD_RULE_ID = FND_API.G_MISS_NUM) Then
1327 l_CARDRULE_QUAL_rec.CARD_RULE_ID :=
1328 l_ref_CARDRULE_QUAL_rec.CARD_RULE_ID;
1329 END IF;
1330 IF (l_CARDRULE_QUAL_rec.SEED_QUAL_ID = FND_API.G_MISS_NUM) Then
1331 l_CARDRULE_QUAL_rec.SEED_QUAL_ID :=
1332 l_ref_CARDRULE_QUAL_rec.SEED_QUAL_ID;
1333 END IF;
1334 IF (l_CARDRULE_QUAL_rec.LOW_VALUE_NUMBER = FND_API.G_MISS_NUM) Then
1335 l_CARDRULE_QUAL_rec.LOW_VALUE_NUMBER :=
1336 l_ref_CARDRULE_QUAL_rec.LOW_VALUE_NUMBER;
1337 END IF;
1338 IF (l_CARDRULE_QUAL_rec.HIGH_VALUE_NUMBER = FND_API.G_MISS_NUM) Then
1339 l_CARDRULE_QUAL_rec.HIGH_VALUE_NUMBER :=
1340 l_ref_CARDRULE_QUAL_rec.HIGH_VALUE_NUMBER;
1341 END IF;
1342 IF (l_CARDRULE_QUAL_rec.HIGH_VALUE_CHAR = FND_API.G_MISS_CHAR) Then
1343 l_CARDRULE_QUAL_rec.HIGH_VALUE_CHAR :=
1344 l_ref_CARDRULE_QUAL_rec.HIGH_VALUE_CHAR;
1345 END IF;
1346 IF (l_CARDRULE_QUAL_rec.LOW_VALUE_CHAR = FND_API.G_MISS_CHAR) Then
1347 l_CARDRULE_QUAL_rec.LOW_VALUE_CHAR :=
1348 l_ref_CARDRULE_QUAL_rec.LOW_VALUE_CHAR;
1349 END IF;
1350 IF (l_CARDRULE_QUAL_rec.CURRENCY_CODE= FND_API.G_MISS_CHAR) Then
1351 l_CARDRULE_QUAL_rec.CURRENCY_CODE :=
1352 l_ref_CARDRULE_QUAL_rec.CURRENCY_CODE;
1353 END IF;
1354 IF (l_CARDRULE_QUAL_rec.HIGH_VALUE_DATE = FND_API.G_MISS_DATE) Then
1355 l_CARDRULE_QUAL_rec.HIGH_VALUE_DATE :=
1356 l_ref_CARDRULE_QUAL_rec.HIGH_VALUE_DATE;
1357 END IF;
1358 IF (l_CARDRULE_QUAL_rec.LOW_VALUE_DATE = FND_API.G_MISS_DATE) Then
1359 l_CARDRULE_QUAL_rec.LOW_VALUE_DATE :=
1360 l_ref_CARDRULE_QUAL_rec.LOW_VALUE_DATE;
1361 END IF;
1362 IF (l_CARDRULE_QUAL_rec.START_DATE_ACTIVE = FND_API.G_MISS_DATE) Then
1363 l_CARDRULE_QUAL_rec.START_DATE_ACTIVE :=
1364 l_ref_CARDRULE_QUAL_rec.START_DATE_ACTIVE;
1365 END IF;
1366 IF (l_CARDRULE_QUAL_rec.END_DATE_ACTIVE = FND_API.G_MISS_DATE) Then
1367 l_CARDRULE_QUAL_rec.END_DATE_ACTIVE :=
1368 l_ref_CARDRULE_QUAL_rec.END_DATE_ACTIVE;
1369 END IF;
1370 IF (l_CARDRULE_QUAL_rec.SCORE = FND_API.G_MISS_NUM) Then
1371 l_CARDRULE_QUAL_rec.SCORE :=
1372 l_CARDRULE_QUAL_rec.SCORE;
1373 END IF;
1374 IF (l_CARDRULE_QUAL_rec.SCORECARD_ID = FND_API.G_MISS_NUM) Then
1375 l_CARDRULE_QUAL_rec.SCORECARD_ID :=
1376 l_CARDRULE_QUAL_rec.SCORECARD_ID;
1377 END IF;
1378
1379
1380 -- Invoke validation procedures
1381 IF ( P_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM)
1382 THEN
1383 -- Debug message
1384 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1385 'Calling Validate_Seed_Qual');
1386
1387 -- Invoke validation procedures
1388 Validate_Seed_Qual(
1389 P_Init_Msg_List => FND_API.G_FALSE,
1390 P_Validation_mode => AS_UTILITY_PVT.G_CREATE,
1391 P_CARDRULE_QUAL_rec => l_cardrule_qual_rec,
1392 X_Return_Status => x_return_status,
1393 X_Msg_Count => x_msg_count,
1394 X_Msg_Data => x_msg_data
1395 );
1396 END IF;
1397
1398 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1399 RAISE FND_API.G_EXC_ERROR;
1400 END IF;
1401
1402
1403
1404 -- Debug Message
1405 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1406 'Calling _CARDRULE_QUAL_Update_Row');
1407
1408 -- Invoke table handler(CARDRULE_QUAL_Update_Row)
1409 AS_CARD_RULE_QUAL_VALUES_PKG.Update_Row(
1410 x_rowid => l_rowid
1411 , x_qual_value_id => l_CARDRULE_QUAL_rec.qual_value_id
1412 , x_last_update_date => SYSDATE
1413 , x_last_updated_by => FND_GLOBAL.USER_ID
1414 , x_last_update_login => FND_GLOBAL.USER_ID
1415 , x_scorecard_id => l_CARDRULE_QUAL_rec.scorecard_id
1416 , x_score => l_CARDRULE_QUAL_rec.score
1417 , x_card_rule_id => -1
1418 , x_seed_qual_id => l_CARDRULE_QUAL_rec.seed_qual_id
1419 , x_high_value_number => l_CARDRULE_QUAL_rec.high_value_number
1420 , x_low_value_number => l_CARDRULE_QUAL_rec.low_value_number
1421 , x_high_value_char => l_CARDRULE_QUAL_rec.high_value_char
1422 , x_low_value_char => l_CARDRULE_QUAL_rec.low_value_char
1423 , x_currency_code => l_CARDRULE_QUAL_rec.currency_code
1424 , x_low_value_date => l_CARDRULE_QUAL_rec.low_value_date
1425 , x_high_value_date => l_CARDRULE_QUAL_rec.high_value_date
1426 , x_start_date_active => l_CARDRULE_QUAL_rec.start_date_active
1427 , x_end_date_active => l_CARDRULE_QUAL_rec.end_date_active
1428 );
1429
1430 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1431 RAISE FND_API.G_EXC_ERROR;
1432 END IF;
1433
1434 -- X_QUAL_VALUE_ID := l_CARDRULE_QUAL_rec.qual_value_id;
1435
1436 --
1437 -- End of API body.
1438 --
1439
1440 -- Standard check for p_commit
1441 IF FND_API.to_Boolean( p_commit )
1442 THEN
1443 COMMIT WORK;
1444 END IF;
1445
1446 -- Debug Message
1447 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1448 'PVT: ' || l_api_name || ' end');
1449
1450 -- Standard call to get message count and if count is 1, get message info.
1451 FND_MSG_PUB.Count_And_Get
1452 ( p_count => x_msg_count,
1453 p_data => x_msg_data );
1454
1455 EXCEPTION
1456 WHEN FND_API.G_EXC_ERROR THEN
1457 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1458 P_API_NAME => L_API_NAME
1459 ,P_PKG_NAME => G_PKG_NAME
1460 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1461 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1462 ,X_MSG_COUNT => X_MSG_COUNT
1463 ,X_MSG_DATA => X_MSG_DATA
1464 ,X_RETURN_STATUS => X_RETURN_STATUS);
1465
1466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1467 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1468 P_API_NAME => L_API_NAME
1469 ,P_PKG_NAME => G_PKG_NAME
1470 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1471 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1472 ,X_MSG_COUNT => X_MSG_COUNT
1473 ,X_MSG_DATA => X_MSG_DATA
1474 ,X_RETURN_STATUS => X_RETURN_STATUS);
1475
1476 WHEN OTHERS THEN
1477 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1478 P_API_NAME => L_API_NAME
1479 ,P_PKG_NAME => G_PKG_NAME
1480 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1481 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1482 ,X_MSG_COUNT => X_MSG_COUNT
1483 ,X_MSG_DATA => X_MSG_DATA
1484 ,X_RETURN_STATUS => X_RETURN_STATUS);
1485 END Update_CardRule_Qual;
1486
1487
1488
1489 -- pass in the qual value Id
1490 Procedure Delete_CardRule_QUAL
1491 (p_api_version IN NUMBER := 2.0,
1492 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1493 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1494 p_validation_level IN NUMBER :=
1495 AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
1496 x_return_status OUT NOCOPY VARCHAR2,
1497 x_msg_count OUT NOCOPY NUMBER,
1498 x_msg_data OUT NOCOPY VARCHAR2,
1499 p_qual_value_id IN NUMBER)
1500 IS
1501 CURSOR C_GET_CARDRULE_QUAL (IN_QUAL_VALUE_ID NUMBER) IS
1502 SELECT
1503 QUAL_VALUE_ID
1504 FROM AS_CARD_RULE_QUAL_VALUES
1505 WHERE QUAL_VALUE_ID = IN_QUAL_VALUE_ID
1506 FOR UPDATE NOWAIT;
1507 --
1508 l_api_name CONSTANT VARCHAR2(30) := 'Delete_CARDRULE_QUAL';
1509 l_api_version_number CONSTANT NUMBER := 2.0;
1510 l_qual_value_id NUMBER := p_qual_value_id;
1511 l_ref_CARDRULE_QUAL_rec AS_SCORECARD_RULES_PUB.CARDRULE_QUAL_REC_TYPE;
1512
1513 BEGIN
1514 -- Standard Start of API savepoint
1515 SAVEPOINT DELETE_CARD_RULE_PVT;
1516
1517 -- Standard call to check for call compatibility.
1518 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1519 p_api_version,
1520 l_api_name,
1521 G_PKG_NAME)
1522 THEN
1523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1524 END IF;
1525
1526 -- Initialize message list if p_init_msg_list is set to TRUE.
1527 IF FND_API.to_Boolean( p_init_msg_list )
1528 THEN
1529 FND_MSG_PUB.initialize;
1530 END IF;
1531
1532 -- Debug Message
1533 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1534 'PVT:' || l_api_name || 'start');
1535
1536 -- Initialize API return status to SUCCESS
1537 x_return_status := FND_API.G_RET_STS_SUCCESS;
1538
1539 --
1540 -- Api body
1541 --
1542 IF FND_GLOBAL.User_Id IS NULL
1543 THEN
1544 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1545 THEN
1546 FND_MESSAGE.Set_Name('AS',
1547 'UT_CANNOT_GET_PROFILE_VALUE');
1548 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
1549 FND_MSG_PUB.ADD;
1550 END IF;
1551 RAISE FND_API.G_EXC_ERROR;
1552 END IF;
1553
1554 -- Debug Message
1555 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1556 'Open C_Get_CARDRULE_QUAL');
1557
1558 Open C_Get_CARDRULE_QUAL(l_QUAL_VALUE_ID);
1559 Fetch C_Get_CARDRULE_QUAL into
1560 l_REF_CARDRULE_QUAL_rec.QUAL_VALUE_ID;
1561
1562 If ( C_Get_CARDRULE_QUAL%NOTFOUND) Then
1563 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1564 THEN
1565 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
1566 FND_MESSAGE.Set_Token ('INFO', 'CARDRULE_QUAL', FALSE);
1567 FND_MSG_PUB.Add;
1568 END IF;
1569 raise FND_API.G_EXC_ERROR;
1570 END IF;
1571
1572 -- Debug Message
1573 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1574 'Close C_Get_CARDRULE_QUAL_del');
1575 Close C_Get_CARDRULE_QUAL;
1576
1577 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1578 'Calling _CARDRULE_QUAL_Delete_Row');
1579
1580 -- Invoke table handler
1581 AS_CARD_RULE_QUAL_VALUES_PKG.Delete_Row(
1582 x_QUAL_VALUE_ID => l_QUAL_VALUE_ID);
1583
1584 --
1585 -- End of API body
1586 --
1587
1588 -- Standard check for p_commit
1589 IF FND_API.to_Boolean( p_commit )
1590 THEN
1591 COMMIT WORK;
1592 END IF;
1593
1594 -- Debug Message
1595 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1596 'PVT: ' || l_api_name || ' end');
1597 FND_MSG_PUB.Count_And_Get
1598 ( p_count => x_msg_count,
1599 p_data => x_msg_data );
1600
1601 EXCEPTION
1602 WHEN FND_API.G_EXC_ERROR THEN
1603 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1604 P_API_NAME => L_API_NAME
1605 ,P_PKG_NAME => G_PKG_NAME
1606 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1607 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1608 ,X_MSG_COUNT => X_MSG_COUNT
1609 ,X_MSG_DATA => X_MSG_DATA
1610 ,X_RETURN_STATUS => X_RETURN_STATUS);
1611
1612 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1613 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1614 P_API_NAME => L_API_NAME
1615 ,P_PKG_NAME => G_PKG_NAME
1616 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1617 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1618 ,X_MSG_COUNT => X_MSG_COUNT
1619 ,X_MSG_DATA => X_MSG_DATA
1620 ,X_RETURN_STATUS => X_RETURN_STATUS);
1621
1622 WHEN OTHERS THEN
1623 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1624 P_API_NAME => L_API_NAME
1625 ,P_PKG_NAME => G_PKG_NAME
1626 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1627 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1628 ,X_MSG_COUNT => X_MSG_COUNT
1629 ,X_MSG_DATA => X_MSG_DATA
1630 ,X_RETURN_STATUS => X_RETURN_STATUS);
1631
1632 END Delete_CardRule_Qual;
1633
1634 END AS_SCORECARD_RULES_PVT;