1 PACKAGE BODY CSC_CORE_UTILS_PVT as
2 /* $Header: cscvcorb.pls 115.18 2002/12/04 19:13:32 jamose ship $ */
3 -- Start of Comments
4 -- Package name : CSC_CORE_UTILS_PVT
5 -- Purpose : This package contains all the common procedure, functions and global
6 -- variables that will be used by the CUSTOMER CARE-MODULE.
7 --
8 -- History
9 -- MM-DD-YYYY NAME MODIFICATION
10 -- 10-08-1999 dejoseph Created.
11 -- 12-08-1999 dejoseph 'Arcs'ed in for first code freeze.
12 -- 12-21-1999 dejoseph 'Arcs'ed in for second code freeze.
13 -- 01-03-2000 dejoseph 'Arcs'ed in for third code freeze. (10-JAN-2000)
14 -- 01-31-2000 dejoseph 'Arcs'ed in for fourth code freeze. (07-FEB-2000)
15 -- 02-04-2000 agaddam Added a new function get_counter_name to be used in a view.
16 -- 02-13-2000 dejoseph 'Arcs'ed on for fifth code freeze. (21-FEB-2000)
17 -- 02-28-2000 dejoseph 'Arcs'ed on for sixth code freeze. (06-MAR-2000)
18 -- 05-04-2000 dejoseph Included procedure to gather CBO statistics on a given
19 -- table.
20 -- 02-22-2001 dejoseph Added two more plan status functions, MERGE_PLAN and
21 -- TRANSFER_PLAN for the purpose of Party Merge.
22 -- 12-03-2002 jamose Added function for Fnd_Api_G_Miss* Changes
23 -- End of Comments
24 --
25
26 FUNCTION G_CREATE RETURN VARCHAR2 IS
27 BEGIN
28 RETURN L_CREATE;
29 END;
30
31 FUNCTION G_UPDATE RETURN VARCHAR2 IS
32 BEGIN
33 RETURN L_UPDATE;
34 END;
35
36
37 FUNCTION G_MISS_NUM RETURN NUMBER IS
38 BEGIN
39 RETURN FND_API.G_MISS_NUM ;
40 END G_MISS_NUM ;
41
42
43 FUNCTION G_MISS_CHAR RETURN VARCHAR2 IS
44 BEGIN
45 RETURN FND_API.G_MISS_CHAR ;
46 END G_MISS_CHAR ;
47
48
49 FUNCTION G_MISS_DATE RETURN DATE IS
50 BEGIN
51 RETURN FND_API.G_MISS_DATE ;
52 END G_MISS_DATE ;
53
54
55 FUNCTION G_RET_STS_SUCCESS RETURN VARCHAR2 IS
56 BEGIN
57 RETURN FND_API.G_RET_STS_SUCCESS ;
58 END G_RET_STS_SUCCESS ;
59
60
61 FUNCTION G_RET_STS_ERROR RETURN VARCHAR2 IS
62 BEGIN
63 RETURN FND_API.G_RET_STS_ERROR ;
64 END G_RET_STS_ERROR ;
65
66
67 FUNCTION G_RET_STS_UNEXP_ERROR RETURN VARCHAR2 IS
68 BEGIN
69 RETURN FND_API.G_RET_STS_UNEXP_ERROR ;
70 END G_RET_STS_UNEXP_ERROR ;
71
72
73 FUNCTION G_VALID_LEVEL_NONE RETURN NUMBER IS
74 BEGIN
75 RETURN FND_API.G_VALID_LEVEL_NONE ;
76 END;
77
78
79 FUNCTION G_VALID_LEVEL_FULL RETURN NUMBER IS
80 BEGIN
81 RETURN FND_API.G_VALID_LEVEL_FULL ;
82 END;
83
84
85 FUNCTION G_VALID_LEVEL_INT RETURN NUMBER IS
86 BEGIN
87 RETURN CS_INTERACTION_PVT.G_VALID_LEVEL_INT ;
88 END;
89
90
91 FUNCTION G_TRUE RETURN VARCHAR2 IS
92 BEGIN
93 return FND_API.G_TRUE ;
94 END;
95
96
97 FUNCTION G_FALSE RETURN VARCHAR2 IS
98 BEGIN
99 return FND_API.G_FALSE ;
100 END;
101
102 FUNCTION ENABLE_PLAN RETURN VARCHAR2 IS
103 BEGIN
104 return 'APPLIED';
105 END;
106
107 FUNCTION DISABLE_PLAN RETURN VARCHAR2 IS
108 BEGIN
109 return 'DISABLED';
110 END;
111
112 FUNCTION APPLY_PLAN RETURN VARCHAR2 IS
113 BEGIN
114 return 'APPLIED';
115 END;
116
117 FUNCTION REMOVE_PLAN RETURN VARCHAR2 IS
118 BEGIN
119 return 'REMOVED';
120 END;
121
122 FUNCTION MERGE_PLAN RETURN VARCHAR2 IS
123 BEGIN
124 return 'MERGED';
125 END;
126
127 FUNCTION TRANSFER_PLAN RETURN VARCHAR2 IS
128 BEGIN
129 return 'TRANSFERED';
130 END;
131
132 -- Fix for Bug#2443649
133 -- commenting out the procedure as this is not used in any appl.
134 -- parmateres for fnd_stats.gather_tabel_stats have been changed
135 /*
136 PROCEDURE GATHER_TABLE_STATS (
137 P_OWNNAME IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_APP_SHORTNAME,
138 P_TABNAME IN VARCHAR2,
139 P_PERCENT IN NUMBER := NULL,
140 P_DEGREE IN NUMBER := NULL,
141 P_PARTNAME IN VARCHAR2 := NULL,
142 P_BACKUP_FLAG IN VARCHAR2 := 'NOBACKUP',
143 P_CASCADE IN BOOLEAN := TRUE,
144 P_TMODE IN VARCHAR2 := 'NORMAL' ,
145 P_GRANULARITY IN VARCHAR2 := 'DEFAULT')
146 IS
147 BEGIN
148 FND_STATS.GATHER_TABLE_STATS (
149 OWNNAME => p_ownname,
150 TABNAME => p_tabname,
151 PERCENT => p_percent,
152 DEGREE => p_degree,
153 PARTNAME => p_partname,
154 BACKUP_FLAG => p_backup_flag,
155 CASCADE => p_cascade,
156 TMODE => p_tmode ,
157 GRANULARITY => p_granularity );
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 NULL;
162
163 END GATHER_TABLE_STATS;
164 */
165
166 PROCEDURE Handle_Exceptions(
167 P_API_NAME IN VARCHAR2,
168 P_PKG_NAME IN VARCHAR2,
169 P_EXCEPTION_LEVEL IN NUMBER DEFAULT NULL,
170 P_PACKAGE_TYPE IN VARCHAR2,
171 X_MSG_COUNT OUT NOCOPY NUMBER,
172 X_MSG_DATA OUT NOCOPY VARCHAR2,
173 X_RETURN_STATUS OUT NOCOPY VARCHAR2)
174
175 IS
176 l_api_name VARCHAR2(30);
177 BEGIN
178 l_api_name := UPPER(p_api_name);
179
180 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || p_package_type);
181 IF p_exception_level = FND_MSG_PUB.G_MSG_LVL_ERROR
182 THEN
183 x_return_status := FND_API.G_RET_STS_ERROR;
184 FND_MSG_PUB.Count_And_Get(
185 p_encoded => FND_API.G_FALSE,
186 p_count => x_msg_count,
187 p_data => x_msg_data);
188 ELSIF p_exception_level = FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
189 THEN
190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
191 FND_MSG_PUB.Count_And_Get(
192 p_encoded => FND_API.G_FALSE,
193 p_count => x_msg_count,
194 p_data => x_msg_data);
195 ELSIF p_exception_level = CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS
196 THEN
197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
198 -- Insert the 'sqlerrm' into the message stack.
199 FND_MSG_PUB.BUILD_EXC_MSG (
200 p_pkg_name => p_pkg_name,
201 p_procedure_name => p_api_name );
202
203 FND_MSG_PUB.Count_And_Get(
204 p_encoded => FND_API.G_FALSE,
205 p_count => x_msg_count,
206 p_data => x_msg_data);
207 END IF;
208 END Handle_exceptions;
209
210 PROCEDURE Validate_Dates (
211 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
212 p_validation_mode IN VARCHAR2,
213 P_START_DATE IN DATE,
214 P_END_DATE IN DATE,
215 x_return_status OUT NOCOPY VARCHAR2,
216 x_msg_count OUT NOCOPY NUMBER,
217 x_msg_data OUT NOCOPY VARCHAR2)
218
219 IS
220 BEGIN
221 -- Initialize message list if p_init_msg_list is set to TRUE.
222 IF FND_API.to_Boolean( p_init_msg_list )
223 THEN
224 FND_MSG_PUB.initialize;
225 END IF;
226
227 -- Initialize API return status to SUCCESS
228 x_return_status := FND_API.G_RET_STS_SUCCESS;
229
230
231 IF ( p_start_date > p_end_date ) then
232 fnd_message.set_name (G_APP_SHORTNAME, 'CS_ALL_START_DATE_AFTER_END');
233 --fnd_msg_pub.add;
234 x_return_status := FND_API.G_RET_STS_ERROR;
235 IF (p_end_date = G_MISS_DATE) THEN
236 x_return_status := FND_API.G_RET_STS_SUCCESS;
237 END IF;
238
239 END IF;
240
241 /*
242 if ( P_Validation_Mode = CSC_CORE_UTILS_PVT.G_UPDATE ) then
243 if ( p_start_date < sysdate ) then
244 FND_MESSAGE.SET_NAME (G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
245 FND_MESSAGE.SET_TOKEN('API_NAME', 'VALIDATE_DATES');
246 FND_MESSAGE.SET_TOKEN('VALUE', p_start_date); -- parameter here is start_date.
247 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_START_DATE');
248 --FND_MSG_PUB.Add;
249 end if;
250 if ( p_end_date < sysdate ) then
251 FND_MESSAGE.SET_NAME (G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
252 FND_MESSAGE.SET_TOKEN('API_NAME', 'VALIDATE_DATES');
253 FND_MESSAGE.SET_TOKEN('VALUE', p_end_date); -- parameter here is start_date.
254 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_END_DATE');
255 --FND_MSG_PUB.Add;
256 end if;
257 end if;
258 */
259
260 -- Standard call to get message count and if count is 1, get message info.
261 FND_MSG_PUB.Count_And_Get (
262 p_encoded => FND_API.G_FALSE,
263 p_count => x_msg_count,
264 p_data => x_msg_data);
265
266 END Validate_dates;
267
268
269 PROCEDURE Validate_Not_Nulls (
270 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
271 p_validation_mode IN VARCHAR2,
272 P_COLUMN_NAME IN VARCHAR2,
273 P_COLUMN_VALUE IN 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 l_api_name varchar2(30) := 'VALIDATE_NOT_NULLS';
279 BEGIN
280 -- Initialize message list if p_init_msg_list is set to TRUE.
281 IF FND_API.to_Boolean( p_init_msg_list )
282 THEN
283 FND_MSG_PUB.initialize;
284 END IF;
285
286 -- Initialize API return status to SUCCESS
287 x_return_status := FND_API.G_RET_STS_SUCCESS;
288
289 if (p_column_value is NULL or p_column_value = FND_API.G_MISS_CHAR ) then
290 fnd_message.set_name (G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
291 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
292 fnd_message.set_token('NULL_PARAM', p_column_name);
293 --fnd_msg_pub.add;
294 x_return_status := FND_API.G_RET_STS_ERROR;
295 end if;
296
297 -- Standard call to get message count and if count is 1, get message info.
298 FND_MSG_PUB.Count_And_Get (
299 p_encoded => FND_API.G_FALSE,
300 p_count => x_msg_count,
301 p_data => x_msg_data);
302
303 END Validate_Not_Nulls;
304
305 PROCEDURE Validate_Not_Nulls (
306 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
307 p_validation_mode IN VARCHAR2,
308 P_COLUMN_NAME IN VARCHAR2,
309 P_COLUMN_VALUE IN NUMBER,
310 x_return_status OUT NOCOPY VARCHAR2,
311 x_msg_count OUT NOCOPY NUMBER,
312 x_msg_data OUT NOCOPY VARCHAR2)
313 IS
314 l_api_name varchar2(30) := 'VALIDATE_NOT_NULLS';
315 BEGIN
316 -- Initialize message list if p_init_msg_list is set to TRUE.
317 IF FND_API.to_Boolean( p_init_msg_list )
318 THEN
319 FND_MSG_PUB.initialize;
320 END IF;
321
322 -- Initialize API return status to SUCCESS
323 x_return_status := FND_API.G_RET_STS_SUCCESS;
324
325 if (p_column_value is NULL or p_column_value = FND_API.G_MISS_NUM ) then
326 fnd_message.set_name (G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
327 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
328 fnd_message.set_token('NULL_PARAM', p_column_name);
329 --fnd_msg_pub.add;
330 x_return_status := FND_API.G_RET_STS_ERROR;
331 end if;
332
333 -- Standard call to get message count and if count is 1, get message info.
334 FND_MSG_PUB.Count_And_Get(
335 p_encoded => FND_API.G_FALSE,
336 p_count => x_msg_count,
337 p_data => x_msg_data);
338
339 END Validate_Not_Nulls;
340
341
342 PROCEDURE Validate_Not_Nulls (
343 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
344 p_validation_mode IN VARCHAR2,
345 P_COLUMN_NAME IN VARCHAR2,
346 P_COLUMN_VALUE IN DATE,
347 x_return_status OUT NOCOPY VARCHAR2,
348 x_msg_count OUT NOCOPY NUMBER,
349 x_msg_data OUT NOCOPY VARCHAR2)
350 IS
351 l_api_name varchar2(30) := 'VALIDATE_NOT_NULLS';
352 BEGIN
353 -- Initialize message list if p_init_msg_list is set to TRUE.
354 IF FND_API.to_Boolean( p_init_msg_list )
355 THEN
356 FND_MSG_PUB.initialize;
357 END IF;
358
359 -- Initialize API return status to SUCCESS
360 x_return_status := FND_API.G_RET_STS_SUCCESS;
361
362 if (p_column_value is NULL or p_column_value = FND_API.G_MISS_DATE ) then
363 fnd_message.set_name (G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
364 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
365 fnd_message.set_token('NULL_PARAM', p_column_name);
366 --fnd_msg_pub.add;
367 x_return_status := FND_API.G_RET_STS_ERROR;
368 end if;
369
370 -- Standard call to get message count and if count is 1, get message info.
371 FND_MSG_PUB.Count_And_Get (
372 p_encoded => FND_API.G_FALSE,
373 p_count => x_msg_count,
374 p_data => x_msg_data);
375
376 END Validate_Not_Nulls;
377
378 PROCEDURE Validate_Seeded_Flag
379 ( p_api_name IN VARCHAR2,
380 p_seeded_flag IN VARCHAR2,
381 x_return_status OUT NOCOPY VARCHAR2
382 ) IS
383 --
384 BEGIN
385 --
386 x_return_status := FND_API.G_RET_STS_SUCCESS;
387
388 -- check if the seeded flag is passed in and is not
389 -- null, if passed in check if the lookup code
390 -- exists in fnd lookups for this date, if not
391 -- its an invalid argument.
392 IF (( p_seeded_flag <> CSC_CORE_UTILS_PVT.G_MISS_CHAR ) AND
393 ( p_seeded_flag IS NOT NULL )) THEN
394 IF CSC_CORE_UTILS_PVT.lookup_code_not_exists(
395 p_effective_date => trunc(sysdate),
396
397 p_lookup_type => 'YES_NO',
398 p_lookup_code => p_seeded_flag ) <> FND_API.G_RET_STS_SUCCESS
399
400 THEN
401 x_return_status := FND_API.G_RET_STS_ERROR;
402 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(p_api_name => p_api_name,
403 p_argument_value => p_seeded_flag,
404 p_argument => 'p_Seeded_flag');
405 END IF;
406 END IF;
407 END Validate_Seeded_Flag;
408
409
410 /* Added this Procedure for validation of Application_id for Enhancement 1784578*/
411
412 PROCEDURE Validate_APPLICATION_ID (
413 P_Init_Msg_List IN VARCHAR2 :=CSC_CORE_UTILS_PVT.G_FALSE,
414 P_Application_ID IN NUMBER DEFAULT NULL,
415 X_Return_Status OUT NOCOPY VARCHAR2,
416 X_Msg_Count OUT NOCOPY NUMBER,
417 X_Msg_Data OUT NOCOPY VARCHAR2,
418 p_effective_date IN Date
419 )
420 IS
421 l_temp_id number:=0;
422
423 BEGIN
424 -- Initialize message list if p_init_msg_list is set to TRUE.
425 IF FND_API.to_Boolean( p_init_msg_list )
426 THEN
427 FND_MSG_PUB.initialize;
428 END IF;
429
430 -- Initialize API return status to SUCCESS
431 x_return_status := FND_API.G_RET_STS_SUCCESS;
432
433 IF (( p_application_id <> CSC_CORE_UTILS_PVT.G_MISS_NUM ) AND
434 ( p_application_id IS NOT NULL )) THEN
435
436 select 1 into l_temp_id from fnd_application
437 where application_id=p_application_id;
438 -- and trunc(p_effective_date) between trunc(nvl(start_date_active,p_effective_date))
439 -- and trunc(nvl(end_date_active,p_effective_date));
440 End If;
441
442
443 Exception
444
445 When no_data_found then
446 fnd_message.set_name ('FND', 'CONC-INVALID APPLICATION ID');
447 fnd_message.set_token('ID', p_application_id);
448 x_return_status := FND_API.G_RET_STS_ERROR;
449
450 When others Then
451 fnd_message.set_name ('FND', 'CONC-INVALID APPLICATION ID');
452 fnd_message.set_token('ID', p_application_id);
453 x_return_status := FND_API.G_RET_STS_ERROR;
454
455 END Validate_APPLICATION_ID;
456
457
458
459 PROCEDURE COMMIT_ROLLBACK(
460 COM_ROLL IN VARCHAR2 := 'ROLL')
461 IS
462 BEGIN
463 if ( COM_ROLL = 'COMMIT' ) then
464 commit;
465 else
466 rollback;
467 end if;
468 END;
469
470 FUNCTION GET_COUNTER_NAME( p_COUNTER_ID NUMBER )
471 RETURN VARCHAR2
472 IS
473 l_counter_name cs_counters.name%type;
474 Cursor C1 is
475 select name
476 from cs_counters
477 where counter_id = p_counter_id;
478 BEGIN
479 OPEN C1;
480 FETCH C1 into l_counter_name;
481 CLOSE C1;
482 return( l_counter_name );
483 END GET_COUNTER_NAME;
484
485
486
487 -- ----------------------------------------------------------------------------
488 -- |----------------------< currency_code_not_exists >---------------------------
489 -- ----------------------------------------------------------------------------
490 -- Description:
491 -- Function used to validate the currency code
492 -- for the specified period.
493 --
494 -- Returns the status depending on the outcome
495 -- If p_currency_code is valid for the specified
496 -- (p_effective_date) period then returns
497 -- FND_API.G_RET_STS_SUCCESS else G_RET_STS_ERROR.
498 --
499 -- The calling program should write the error message
500 -- depending on the return status from the function
501 --
502 -- For CSC Development.
503
504 FUNCTION currency_code_not_exists
505 (p_effective_date in date,
506 p_currency_code in varchar2
507 ) return varchar2 is
508 --
509 -- Declare Local Variables
510 --
511 l_dummy varchar2(1);
512 l_return_status varchar2(30);
513 --
514 -- Declare Local cursors
515 --
516 cursor csr_currency_look is
517 select null
518 from fnd_currencies
519 where currency_code = p_currency_code
520 and trunc(p_effective_date) between trunc(nvl(start_date_active, p_effective_date))
521 and trunc(nvl(end_date_active, p_effective_date));
522 BEGIN
523 -- Initialize the return status to success.
524 l_return_status := FND_API.G_RET_STS_SUCCESS;
525 open csr_currency_look;
526 fetch csr_currency_look into l_dummy;
527 if csr_currency_look%notfound then
528 close csr_currency_look;
529 l_return_status := FND_API.G_RET_STS_ERROR;
530 --** invalid arg;
531 end if;
532 if csr_currency_look%ISOPEN then
533 close csr_currency_look;
534 end if;
535 return (l_return_status);
536 END currency_code_not_exists;
537
538
539 -- ----------------------------------------------------------------------------
540 -- |----------------------< lookup_code_not_exists >---------------------------
541 -- ----------------------------------------------------------------------------
542 -- Description:
543 -- Function used to validate the lookup code using
544 -- the fnd_lookups table returns TRUE if the lookup code
545 -- exists else returns FALSE.
546 --
547 -- Uses p_effective_Date to date track the records.
548 --
549 -- The calling program should write the error message
550 -- depending on the return status from the function
551 --
552 -- Returns the status depending on the outcome.
553 -- If p_currency_code is enabled for the specified
554 -- (p_effective_date) period then returns
555 -- FND_API.G_RET_STS_SUCCESS else G_RET_STS_ERROR.
556 --
557 --
558 -- For CSC Development.
559
560 FUNCTION lookup_code_not_exists
561 (p_effective_date in date
562 ,p_lookup_type in varchar2
563 ,p_lookup_code in varchar2
564 ) return varchar2 is
565 --
566 -- Declare Local Variables
567 --
568 l_dummy varchar2(1);
569 l_return_status varchar2(30);
570 --
571 -- Declare Local cursors
572 --
573 cursor csr_lookup_code is
574 select null
575 from fnd_lookups
576 where lookup_code = p_lookup_code
577 and lookup_type = p_lookup_type
578 and enabled_flag = 'Y'
579 and trunc(p_effective_date) between
580 trunc(nvl(start_date_active, p_effective_date))
581 and trunc(nvl(end_date_active, p_effective_date));
582 BEGIN
583 -- Initialize the return status to success.
584 l_return_status := FND_API.G_RET_STS_SUCCESS;
585 open csr_lookup_code;
586 fetch csr_lookup_code into l_dummy;
587 if csr_lookup_code%notfound then
588 close csr_lookup_code;
589 l_return_status := FND_API.G_RET_STS_ERROR;
590 end if;
591 if csr_lookup_code%ISOPEN then
592 close csr_lookup_code;
593 end if;
594 return ( l_return_status );
595 END lookup_code_not_exists;
596
597
598 FUNCTION csc_lookup_code_not_exists
599 (p_effective_date DATE
600 ,p_lookup_type VARCHAR2
601 ,p_lookup_code VARCHAR2
602 ) return varchar2 IS
603
604 --
605 -- Declare Local Variables
606 --
607 l_dummy varchar2(1);
608 l_return_status varchar2(30);
609 --
610 -- Declare Local cursors
611 --
612 cursor csr_lookup_code is
613 select null
614 from CSC_LOOKUPS
615 where lookup_code = p_lookup_code
616 and lookup_type = p_lookup_type
617 and enabled_flag = 'Y'
618 and trunc(p_effective_date) between
619 trunc(nvl(start_date_active, p_effective_date))
620 and trunc(nvl(end_date_active, p_effective_date));
621 BEGIN
622
623 -- Initialize the return status to success.
624 l_return_status := FND_API.G_RET_STS_SUCCESS;
625
626 open csr_lookup_code;
627 fetch csr_lookup_code into l_dummy;
628 if csr_lookup_code%notfound then
629 close csr_lookup_code;
630 l_return_status := FND_API.G_RET_STS_ERROR;
631 end if;
632 if csr_lookup_code%ISOPEN then
633 close csr_lookup_code;
634 end if;
635
636 return ( l_return_status );
637
638 END csc_lookup_code_not_exists;
639
640 -- ---------------------------------------------------------------------------
641 -- -------------------------< Add_Invalid_Argument_Msg>------------------------
642 -- ----------------------------------------------------------------------------
643 -- Description:
644 -- This procedure adds Invalid arguments message to the
645 -- messages que
646 --
647 --
648 --
649 -- For CSC Development.
650
651 PROCEDURE Add_Invalid_Argument_Msg
652 ( p_api_name VARCHAR2,
653 p_argument VARCHAR2,
654 p_argument_value VARCHAR2
655 )
656 IS
657 BEGIN
658 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
659 FND_MESSAGE.Set_Name('CS', 'CS_API_ALL_INVALID_ARGUMENT');
660 FND_MESSAGE.Set_Token('API_NAME', p_api_name);
661 FND_MESSAGE.Set_Token('VALUE', p_argument_value);
662 FND_MESSAGE.Set_Token('PARAMETER', p_argument);
663 END IF;
664 END Add_Invalid_Argument_Msg;
665
666
667 -- ---------------------------------------------------------------------------
668 -- -------------------------< validate_start_end_dt>---------------------------
669 -- ----------------------------------------------------------------------------
670 -- Description:
671 -- Procedure used to validate the start and end dates.
672 -- Write a CS_ALL_START_DATE message if the start date
673 -- is less than the sysdate and CS_ALL_END_DATE message
674 -- if end date is less then the start date.
675 --
676 --
677 -- For CSC Development.
678
679 PROCEDURE Validate_Start_End_Dt
680 ( p_api_name IN VARCHAR2,
681 p_start_date IN DATE,
682 p_end_date IN DATE DEFAULT NULL,
683 x_return_status OUT NOCOPY VARCHAR2 )
684 IS
685 --
686 x_msg_count number;
687 x_msg_data varchar2(500);
688
689 BEGIN
690 --
691 x_return_status := FND_API.G_RET_STS_SUCCESS;
692 --
693
694 --** need to check out
695
696
697 Validate_Dates (
698 p_init_msg_list => FND_API.G_FALSE,
699 p_validation_mode => NULL,
700 P_START_DATE => p_start_date,
701 P_END_DATE => p_end_date,
702 x_return_status => x_return_status,
703 x_msg_count => x_msg_count,
704 x_msg_data => x_msg_data );
705
706 /*
707 IF ( p_start_date IS NOT NULL ) THEN
708 IF p_start_date < sysdate THEN
709 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
710 FND_MESSAGE.Set_Name('CS', 'CS_ALL_START_DATE');
711 FND_MESSAGE.Set_Token('START_DATE' ,p_start_date);
712 FND_MESSAGE.Set_Token('END_DATE' ,p_end_date);
713 END IF;
714 x_return_status := FND_API.G_RET_STS_ERROR;
715
716 END IF;
717 --
718 IF( p_end_date < p_start_date ) THEN
719 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
720 FND_MESSAGE.Set_Name('CS', 'CS_ALL_END_DATE');
721 FND_MESSAGE.Set_Token('START_DATE' ,p_start_date);
722 FND_MESSAGE.Set_Token('END_DATE' ,p_end_date);
723 END IF;
724 x_return_status := FND_API.G_RET_STS_ERROR;
725 END IF;
726 END IF;
727 */
728 END;
729
730 -- ---------------------------------------------------------------------------
731 -- -------------------------< Record_Is_Locked_Msg>------------------------
732 -- ----------------------------------------------------------------------------
733
734 -- Description:
735 -- This procedure adds Record Locked message to the
736 -- messages que
737 --
738 --
739 -- For CSC Development.
740
741
742 PROCEDURE Record_Is_Locked_Msg
743 ( p_api_name VARCHAR2
744 )
745 IS
746 BEGIN
747 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
748 FND_MESSAGE.Set_Name('CS', 'CS_API_ALL_CANT_LOCK_RECORD');
749 FND_MESSAGE.Set_Token('API_NAME', p_api_name);
750 END IF;
751 END Record_IS_Locked_Msg;
752
753 -- ---------------------------------------------------------------------------
754 -- -------------------------< Cannot_Update_Param_Msg>------------------------
755 -- ----------------------------------------------------------------------------
756 -- Description:
757 -- Adds a cannot update parameter messages.
758 --
759 --
760 --
761 --
762 -- For CSC Development.
763
764 PROCEDURE Cannot_Update_Param_Msg
765 ( p_api_name VARCHAR2,
766 p_argument VARCHAR2,
767 p_argument_value VARCHAR2
768 )
769 IS
770 BEGIN
771 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
772 --** check the message name
773 FND_MESSAGE.Set_Name('CS', 'CS_API_CHG_CANT_UPD_PARAM');
774 FND_MESSAGE.Set_Token('API_NAME', p_api_name);
775 FND_MESSAGE.Set_Token('COLUMN_NAME', p_argument);
776 FND_MESSAGE.Set_Token('VALUE', p_argument_value);
777 END IF;
778 END;
779
780 -- ---------------------------------------------------------------------------
781 -- -------------------------< Add_Null_Parameter_Msg>--------------------------
782 -- ----------------------------------------------------------------------------
783
784 -- Description:
785 -- Writes a Null Parameter Message to the message que.
786 --
787 --
788 --
789 -- For CSC Development.
790
791 PROCEDURE Add_Null_Parameter_Msg
792 ( p_api_name VARCHAR2,
793 p_argument VARCHAR2
794 )
795 IS
796 BEGIN
797 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
798 FND_MESSAGE.Set_Name('CS', 'CS_API_ALL_NULL_PARAMETER');
799 FND_MESSAGE.Set_Token('API_NAME', p_api_name);
800 FND_MESSAGE.Set_Token('NULL_PARAM', p_argument);
801 END IF;
802 END Add_Null_Parameter_Msg;
803
804 -- ---------------------------------------------------------------------------
805 -- -------------------------< Add_Duplicate_Value_Msg>------------------------
806 -- ----------------------------------------------------------------------------
807 -- Description:
808 -- This procedure adds Invalid arguments message to the
809 -- messages que
810 --
811 --
812 --
813 --
814 -- For CSC Development.
815
816 PROCEDURE Add_Duplicate_Value_Msg
817 ( p_api_name VARCHAR2,
818 p_argument VARCHAR2,
819 p_argument_value VARCHAR2
820 )
821 IS
822 BEGIN
823 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
824 FND_MESSAGE.Set_Name('CS', 'CS_API_ALL_DUPLICATE_VALUE');
825 FND_MESSAGE.Set_Token('API_NAME', p_api_name);
826 FND_MESSAGE.Set_Token('DUPLICATE_VAL_PARAM', p_argument_value);
827 END IF;
828 END Add_Duplicate_Value_Msg;
829
830 -- ---------------------------------------------------------------------------
831 -- -------------------------< Mandatory_arg_Error>--------------------------
832 -- ----------------------------------------------------------------------------
833 -- Description: This procedure is called by business processes which have
834 -- identified a mandatory argument which needs to be NOT null.
835 -- If the argument is null then need to error.
836 -- Varchar2 format.
837
838 Procedure mandatory_arg_error
839 (p_api_name in varchar2,
840 p_argument in varchar2,
841 p_argument_value in varchar2) is
842 --
843 Begin
844
845 --
846 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
847 FND_MESSAGE.Set_Name('CS', 'CS_API_ALL_MISSING_PARAM');
848 FND_MESSAGE.Set_Token('API_NAME', p_api_name);
849 FND_MESSAGE.Set_Token('MISSING_PARAM', p_argument);
850 End If;
851 --
852
853 End mandatory_arg_error;
854
855 -- ---------------------------------------------------------------------------
856 -- -------------------------< Mandatory_arg_Error>--------------------------
857 -- ----------------------------------------------------------------------------
858 -- Description: Overloaded procedure which converts argument into a varchar2.
859 --
860 Procedure mandatory_arg_error
861 (p_api_name in varchar2,
862 p_argument in varchar2,
863 p_argument_value in date) is
864 --
865 Begin
866 --
867
868 mandatory_arg_error(
869 p_api_name => p_api_name,
870 p_argument => p_argument,
871 p_argument_value => to_char(p_argument_value,'DD-MON-YYYY'));
872
873 --
874 End mandatory_arg_error;
875
876 -- ---------------------------------------------------------------------------
877 -- -------------------------< Mandatory_arg_Error>--------------------------
878 -- ----------------------------------------------------------------------------
879 -- Description: Overloaded procedure which converts argument into a varchar2.
880
881 --
882 Procedure mandatory_arg_error
883 (p_api_name in varchar2,
884 p_argument in varchar2,
885 p_argument_value in number) is
886 --
887 Begin
888 --
889 mandatory_arg_error(
890 p_api_name => p_api_name,
891 p_argument => p_argument,
892 p_argument_value => to_char(p_argument_value));
893 --
894 End mandatory_arg_error;
895
896 -- ---------------------------------------------------------------------------
897 -- -------------------------< Validate_Sql_Stmnt>--------------------------
898 -- ----------------------------------------------------------------------------
899 -- Description:
900
901 PROCEDURE Validate_Sql_Stmnt
902 ( p_sql_stmnt IN VARCHAR2,
903 x_return_status OUT NOCOPY VARCHAR2 )
904 IS
905 l_sql_cur_hdl INT;
906 BEGIN
907
908 -- initialize the return status to SUCCESS
909 x_return_status := FND_API.G_RET_STS_SUCCESS;
910
911 -- if the sql_statement is passed in and if its NOT NULL then
912 -- validate the sql_statement by parsing it using the dbms_sql
913 -- package.
914 IF (( p_sql_stmnt IS NOT NULL ) and
915 ( p_sql_stmnt <> FND_API.G_MISS_CHAR )) THEN
916 l_sql_cur_hdl := DBMS_SQL.OPEN_CURSOR;
917 DBMS_SQL.PARSE( l_sql_cur_hdl, p_sql_stmnt, DBMS_SQL.NATIVE );
918 DBMS_SQL.CLOSE_CURSOR( l_sql_cur_hdl );
919 END IF;
920 EXCEPTION
921 WHEN OTHERS THEN
922 IF DBMS_SQL.IS_OPEN( l_Sql_cur_hdl ) THEN
923 DBMS_SQL.CLOSE_CURSOR(l_Sql_cur_hdl );
924 END IF;
925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
926 END Validate_Sql_Stmnt;
927
928 -- ---------------------------------------------------------------------------
929 -- -------------------------< Build_Sql_Stmnt>--------------------------
930 -- ----------------------------------------------------------------------------
931 -- Description:
932
933 PROCEDURE Build_Sql_Stmnt
934 ( p_select_clause IN VARCHAR2,
935 p_from_clause IN VARCHAR2,
936 p_where_clause IN VARCHAR2,
937 p_other_clause IN VARCHAR2,
938 x_sql_Stmnt OUT NOCOPY VARCHAR2,
939 x_return_status OUT NOCOPY VARCHAR2 )
940 IS
941 l_sql_stmnt VARCHAR2(2000);
942 BEGIN
943
944 -- initialize the return status
945 x_return_status := FND_API.G_RET_STS_SUCCESS;
946
947 -- check if the select_clause and the from_Clause
948 -- is NULL or missing, if so we cannot form an
949 -- sql_statement.
950
951 IF ((( p_Select_Clause IS NULL ) and
952 ( p_Select_Clause = FND_API.G_MISS_CHAR ))
953 and (( p_from_Clause IS NULL ) and
954 ( p_from_Clause = FND_API.G_MISS_CHAR )))
955 THEN
956 -- invalid arguments exception
957 x_return_status := FND_API.G_RET_STS_ERROR;
958 ELSE
959 -- if present concatenate both the clauses.
960 l_sql_stmnt := 'SELECT '||p_select_clause||' FROM '||p_from_clause;
961 END IF;
962
963 -- if where_clause is passsed in contenate to the select and from clauses
964 IF (( p_where_clause IS NOT NULL )
965 and ( p_where_clause <> FND_API.G_MISS_CHAR )) THEN
966 l_sql_stmnt := l_sql_stmnt||' WHERE '||p_where_clause;
967 END IF;
968
969 -- if other_clause is not null then concatenate
970 IF (( p_other_clause IS NOT NULL )
971 and ( p_other_clause <> FND_API.G_MISS_CHAR )) THEN
972 l_sql_stmnt := l_sql_stmnt||' '||p_other_clause;
973 END IF;
974 x_sql_stmnt := l_sql_stmnt;
975 EXCEPTION
976 WHEN OTHERS THEN
977 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
978 END Build_Sql_Stmnt;
979
980
981 FUNCTION Get_G_Miss_Char(p_value IN Varchar2, p_old_value IN Varchar2)
982 RETURN VARCHAR2 IS
983 BEGIN
984 IF p_value = G_MISS_CHAR THEN
985 RETURN NULL;
986 ELSIF p_value IS NULL THEN
987 RETURN p_old_value;
988 ELSE RETURN p_value;
989 END IF;
990 END;
991
992 FUNCTION Get_G_Miss_Num(p_value IN NUMBER, p_old_value IN NUMBER)
993 RETURN NUMBER IS
994 BEGIN
995 IF p_value = G_MISS_NUM THEN
996 RETURN NULL;
997 ELSIF p_value IS NULL THEN
998 RETURN p_old_value;
999 ELSE
1000 RETURN p_value;
1001 END IF;
1002 END;
1003
1004 FUNCTION Get_G_Miss_Date(p_value IN DATE, p_old_value IN DATE)
1005 RETURN DATE IS
1006 BEGIN
1007 IF p_value = G_MISS_DATE THEN
1008 RETURN NULL;
1009 ELSIF p_value IS NULL THEN
1010 RETURN p_old_value;
1011 ELSE
1012 RETURN p_value;
1013 END IF;
1014 END;
1015
1016
1017 END CSC_CORE_UTILS_PVT;
[Home] [Help]