[Home] [Help]
PACKAGE BODY: APPS.AMS_SOURCECODE_PVT
Source
1 PACKAGE BODY AMS_SourceCode_PVT AS
2 /*$Header: amsvscgb.pls 115.29 2002/11/22 23:38:55 dbiswas ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'AMS_SourceCode_PVT';
5
6 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
7 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
8 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
9
10 -- Forward declaration
11 /*PROCEDURE modify_sourcecode(
12 p_source_code IN VARCHAR2,
13 p_object_type IN VARCHAR2,
14 p_object_id IN NUMBER,
15 p_sourcecode_id IN NUMBER,
16 p_related_sourcecode IN VARCHAR2 := NULL,
17 p_releated_sourceobj IN VARCHAR2 := NULL,
18 p_related_sourceid IN NUMBER := NULL,
19
20 x_return_status OUT NOCOPY VARCHAR2
21 ) ;*/
22
23 -----------------------------------------------------------------
24 -- FUNCTION
25 -- is_source_code_unique(p_source_code)
26 -----------------------------------------------------------------
27
28
29 FUNCTION is_source_code_unique(
30 p_source_code IN VARCHAR2
31 )
32 RETURN VARCHAR2 IS
33
34 CURSOR c_source_code IS
35 SELECT 1
36 FROM ams_source_codes
37 WHERE source_code = p_source_code;
38
39 l_dummy NUMBER;
40
41 BEGIN
42
43 OPEN c_source_code;
44 FETCH c_source_code INTO l_dummy;
45 CLOSE c_source_code;
46
47 IF l_dummy = 1 THEN
48 RETURN FND_API.g_false;
49 ELSE
50 RETURN FND_API.g_true;
51 END IF;
52
53 END;
54
55
56 -----------------------------------------------------------------
57 -- FUNCTION
58 -- get_unique_sequence
59 -- DESCRIPTION
60 -- Return a unique sequence to be used as part of the
61 -- generated source code.
62 -- PARAMETERS
63 -- p_geo_code - code corresponding to the geographic region
64 -- of the user.
65 -- p_date_code - code corresponding to the current date using
66 -- the format specified in the profile.
67 -- p_cust_code - code associated to the marketing activity's
68 -- custom setup type.
69 -----------------------------------------------------------------
70 FUNCTION get_unique_sequence (
71 p_geo_code IN VARCHAR2,
72 p_date_code IN VARCHAR2,
73 p_cust_code IN VARCHAR2
74 )
75 RETURN VARCHAR2;
76
77 -----------------------------------------------------------------
78 -- FUNCTION
79 -- get_new_source_code (p_object_type, p_custsetup_id, p_global_flag)
80 -- NOTES
81 -- Internal Rollout Ref #20:
82 -- New source code generation algorithm
83 -- - geographic region code
84 -- - current month year
85 -- - numeric sequence
86 -- - suffix from custom seteups
87 -- Not doing anything with the p_object_type in the
88 -- current version.
89 -----------------------------------------------------------------
90 FUNCTION get_new_source_code (
91 p_object_type IN VARCHAR2,
92 p_custsetup_id IN NUMBER,
93 p_global_flag IN VARCHAR2 := FND_API.g_false
94 )
95 RETURN VARCHAR2
96 IS
97 L_CITY_PROFILE CONSTANT VARCHAR2(30) := 'AMS_SRCGEN_USER_CITY';
98 L_DATE_PROFILE CONSTANT VARCHAR2(30) := 'AMS_SRCGEN_DATE_PATTERN';
99 L_USER_GEO_TYPE CONSTANT VARCHAR2(30) := 'CITY';
100 MAX_GEO_LENGTH CONSTANT NUMBER := 5;
101 MAX_CUST_LENGTH CONSTANT NUMBER := 5;
102 MAX_DATE_LENGTH CONSTANT NUMBER := 6;
103
104 l_geo_code VARCHAR2(10);
105 l_date_code VARCHAR2(10);
106 l_num_code VARCHAR2(10);
107 l_cust_code VARCHAR2(10);
108
109 l_loc_hierarchy_id NUMBER;
110 l_date_pattern VARCHAR2(30);
111 l_sequence_length NUMBER;
112 l_sequence_code VARCHAR2(10);
113 l_target_area_type VARCHAR2(30) := 'AREA2';
114
115 l_source_code VARCHAR2(30);
116
117 l_exit_flag NUMBER;
118
119 CURSOR c_geo_code (p_global_flag IN VARCHAR2, p_loc_hierarchy_id IN NUMBER) IS
120 -- Following one line is changed by ptendulk on 30-Aug-2000 to support
121 -- the global flag
122 -- SELECT DECODE (p_global_flag, FND_API.g_true, area1_code, area2_code)
123 SELECT DECODE (p_global_flag, 'Y', area1_code, area2_code)
124 FROM jtf_loc_hierarchies_vl
125 WHERE location_hierarchy_id = p_loc_hierarchy_id
126 ;
127
128 CURSOR c_custom_setup (p_id NUMBER) IS
129 SELECT cte.source_code_suffix
130 FROM ams_custom_setups_vl cte
131 WHERE cte.custom_setup_id = p_id
132 ;
133 BEGIN
134 -------------------------------------------------
135 -- Source Code consists of the following pattern:
136 -- AAAMMYY999CUST
137 -------------------------------------------------
138
139 -- Need to validate that the code returned in
140 -- profiles is a number
141 BEGIN
142 --
143 -- Pattern: AAA
144 -- User's geographic code
145 l_loc_hierarchy_id := TO_NUMBER (FND_PROFILE.value (L_CITY_PROFILE));
146
147 -- Use AREA1 if the object is global
148 OPEN c_geo_code (p_global_flag, l_loc_hierarchy_id);
149 FETCH c_geo_code INTO l_geo_code;
150 CLOSE c_geo_code;
151 -- strip out the spaces
152 l_geo_code := UPPER (SUBSTR (REPLACE (l_geo_code, ' '), 1, MAX_GEO_LENGTH));
153 EXCEPTION
154 WHEN VALUE_ERROR THEN
155 l_geo_code := NULL;
156 END;
157
158 --
159 -- Pattern: MMYY
160 -- Date pattern used to denote month and year
161 l_date_pattern := FND_PROFILE.value (L_DATE_PROFILE);
162
163 --
164 -- choang - 18-May-2000
165 -- Added additional error handling for missing date
166 -- format and invalid date format.
167 IF l_date_pattern IS NULL OR l_date_pattern = '' THEN
168 AMS_Utility_PVT.error_message ('AMS_SRCGEN_NO_DATE_PATTERN');
169 RAISE FND_API.g_exc_error;
170 ELSIF LENGTH (l_date_pattern) > MAX_DATE_LENGTH THEN
171 AMS_Utility_PVT.error_message ('AMS_SRCGEN_DATE_TOO_LONG');
172 RAISE FND_API.g_exc_error;
173 END IF;
174
175 BEGIN
176 l_date_code := TO_CHAR (SYSDATE, l_date_pattern);
177 EXCEPTION
178 WHEN OTHERS THEN
179 IF SQLCODE = -1821 THEN -- date format not recognized
180 AMS_Utility_PVT.error_message ('AMS_SRCGEN_BAD_DATE_PATTERN');
181 RAISE FND_API.g_exc_error;
182 END IF;
183 END;
184
185 --
186 -- Pattern: CUST
187 -- Custom setup code
188 OPEN c_custom_setup (p_custsetup_id);
189 FETCH c_custom_setup INTO l_cust_code;
190 CLOSE c_custom_setup;
191 l_cust_code := UPPER (SUBSTR (REPLACE (l_cust_code, ' '), 1, MAX_CUST_LENGTH)); -- strip out the spaces.
192
193 --
194 -- Pattern: 999
195 -- Numeric sequence generated for a combination
196 -- of AAAMMYYCUST.
197 l_sequence_code := get_unique_sequence (l_geo_code, l_date_code, l_cust_code);
198
199 l_source_code := l_geo_code || l_date_code || l_sequence_code || l_cust_code;
200 RETURN l_source_code;
201 EXCEPTION
202 WHEN OTHERS THEN
203 IF c_geo_code%ISOPEN THEN
204 CLOSE c_geo_code;
205 END IF;
206 IF c_custom_setup%ISOPEN THEN
207 CLOSE c_custom_setup;
208 END IF;
209 RAISE;
210 END get_new_source_code;
211
212
213 -----------------------------------------------------------------
214 -- FUNCTION
215 -- get_source_code(p_arc_object, p_type_code)
216 -----------------------------------------------------------------
217 FUNCTION get_source_code(
218 p_arc_object IN VARCHAR2,
219 p_type_code IN VARCHAR2
220 )
221 RETURN VARCHAR2 IS
222
223 l_source_code VARCHAR2(30);
224 l_lookup_type VARCHAR2(30);
225 l_prefix VARCHAR2(30);
226 l_code VARCHAR2(30);
227 l_dummy NUMBER;
228
229 CURSOR c_seq IS
230 SELECT TO_CHAR(ams_source_codes_gen_s.NEXTVAL)
231 FROM DUAL;
232
233 CURSOR c_prefix IS
234 SELECT tag
235 FROM ams_lookups
236 WHERE lookup_type = l_lookup_type
237 AND lookup_code = p_type_code;
238
239 BEGIN
240
241 IF p_arc_object = 'CAMP' THEN
242 l_lookup_type := 'AMS_CAMPAIGN_PURPOSE';
243 ELSIF p_arc_object = 'EVEH' THEN
244 l_lookup_type := 'AMS_EVENT_TYPE';
245 ELSIF p_arc_object = 'OFFR' THEN
246 l_lookup_type := 'AMS_OFFER_TYPE';
247 ELSE
248 AMS_Utility_PVT.error_message('AMS_SCG_BAD_ARC_OBJECT');
249 RAISE FND_API.g_exc_error;
250 END IF;
251
252 IF p_type_code IS NULL THEN
253 l_prefix := p_arc_object;
254 ELSE
255 OPEN c_prefix;
256 FETCH c_prefix INTO l_prefix;
257 CLOSE c_prefix;
258
259 IF l_prefix IS NULL THEN
260 AMS_Utility_PVT.error_message('AMS_SCG_BAD_TYPE_CODE');
261 RAISE FND_API.g_exc_error;
262 END IF;
263 END IF;
264
265 LOOP
266 OPEN c_seq;
267 FETCH c_seq INTO l_code;
268 CLOSE c_seq;
269
270 l_source_code := l_prefix || l_code;
271 EXIT WHEN is_source_code_unique(l_source_code) = FND_API.g_true;
272 END LOOP;
273
274 RETURN l_source_code;
275
276 END;
277
278
279 -----------------------------------------------------------------
280 -- FUNCTION
281 -- get_source_code(p_parent_id, p_arc_object)
282 -----------------------------------------------------------------
283 FUNCTION get_source_code(
284 p_parent_id IN NUMBER,
285 p_arc_object IN VARCHAR2
286 )
287 RETURN VARCHAR2 IS
288
289 l_prefix VARCHAR2(30);
290 l_type VARCHAR2(30);
291 l_code VARCHAR2(30);
292 l_dummy NUMBER;
293
294 CURSOR c_camp IS
295 SELECT campaign_type
296 FROM ams_campaigns_vl
297 WHERE campaign_id = p_parent_id;
298
299 CURSOR c_eveh IS
300 SELECT event_type_code
301 FROM ams_event_headers_vl
302 WHERE event_header_id = p_parent_id;
303
304 BEGIN
305
306 IF p_arc_object = 'CSCH' THEN
307 OPEN c_camp;
308 FETCH c_camp INTO l_type;
309 IF c_camp%NOTFOUND THEN
310 CLOSE c_camp;
311 AMS_Utility_PVT.error_message('AMS_SCG_BAD_PARENT_ID');
312 RAISE FND_API.g_exc_error;
313 END IF;
314 CLOSE c_camp;
315 RETURN get_source_code('CAMP', l_type);
316 ELSIF p_arc_object = 'EVEO' THEN
317 OPEN c_eveh;
318 FETCH c_eveh INTO l_type;
319 IF c_eveh%NOTFOUND THEN
320 CLOSE c_eveh;
321 AMS_Utility_PVT.error_message('AMS_SCG_BAD_PARENT_ID');
322 RAISE FND_API.g_exc_error;
323 END IF;
324 CLOSE c_eveh;
325 RETURN get_source_code('EVEH', l_type);
326 ELSE
327 AMS_Utility_PVT.error_message('AMS_SCG_BAD_ARC_OBJECT');
328 RAISE FND_API.g_exc_error;
329 END IF;
330
331 END;
332
333 -----------------------------------------------------------------
334 -- FUNCTION
335 -- Added by MPANDE 02/16/2001
336 -- get_source_code(p_category_id, p_arc_object_from)
337 -----------------------------------------------------------------
338 FUNCTION get_source_code(
339 p_category_id IN NUMBER,
340 p_arc_object_for IN VARCHAR2
341 )
342 RETURN VARCHAR2 IS
343
344 l_prefix VARCHAR2(30);
345 l_code VARCHAR2(30);
346 -- change the cursor to ponit to ams_categories_vl table 03/05/2001
347 CURSOR c_suffix IS
348 SELECT budget_code_suffix
349 FROM ams_categories_vl
350 WHERE category_id = p_category_id;
351
352 BEGIN
353
354 OPEN c_suffix;
355 FETCH c_suffix INTO l_prefix;
356 CLOSE c_suffix;
357 RETURN l_prefix||get_source_code();
358
359 END get_source_code;
360
361 -----------------------------------------------------------------
362 -- FUNCTION
363 -- get_source_code
364 -----------------------------------------------------------------
365 FUNCTION get_source_code RETURN VARCHAR2 IS
366
367 l_code VARCHAR2(30);
368
369 CURSOR c_seq IS
370 SELECT TO_CHAR(ams_source_codes_gen_s.NEXTVAL)
371 FROM DUAL;
372
373 BEGIN
374
375 LOOP
376 OPEN c_seq;
377 FETCH c_seq INTO l_code;
378 CLOSE c_seq;
379 EXIT WHEN is_source_code_unique(l_code) = FND_API.g_true;
380 END LOOP;
381 RETURN l_code;
382
383 END get_source_code;
384
385 -----------------------------------------------------------------
386 -- PROCEDURE
387 -- create_sourcecode
388 -----------------------------------------------------------------
389 PROCEDURE create_sourcecode(
390 p_api_version IN NUMBER,
391 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
392 p_commit IN VARCHAR2 := FND_API.g_false,
393 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
394
395 x_return_status OUT NOCOPY VARCHAR2,
396 x_msg_count OUT NOCOPY NUMBER,
397 x_msg_data OUT NOCOPY VARCHAR2,
398
399 p_sourcecode IN VARCHAR2,
400 p_sourcecode_for IN VARCHAR2,
401 p_sourcecode_for_id IN NUMBER,
402 p_related_sourcecode IN VARCHAR2 := NULL,
403 p_releated_sourceobj IN VARCHAR2 := NULL,
404 p_related_sourceid IN NUMBER := NULL,
405 x_sourcecode_id OUT NOCOPY NUMBER
406 )
407 IS
408
409 l_api_version CONSTANT NUMBER := 1.0;
410 l_api_name CONSTANT VARCHAR2(30) := 'create_sourcecode';
411 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
412
413 CURSOR c_source_code IS
414 SELECT source_code_id, active_flag
415 FROM ams_source_codes
416 WHERE source_code = p_sourcecode;
417
418 CURSOR c_active_object IS
419 SELECT 'x'
420 FROM ams_source_codes
421 WHERE arc_source_code_for = p_sourcecode_for
422 AND source_code_for_id = p_sourcecode_for_id
423 AND active_flag = 'Y';
424
425 CURSOR c_sourcecode_seq IS
426 SELECT AMS_SOURCE_CODES_S.NEXTVAL
427 FROM DUAL;
428
429 CURSOR c_related_source_code IS
430 SELECT 1
431 FROM ams_source_codes
432 WHERE source_code = p_related_sourcecode;
433
434 l_return_status VARCHAR2(1);
435 l_sc_active_flag varchar2(1) := 'Y';
436 l_sourcecode_id NUMBER;
437 l_active_flag varchar2(1) := 'Y';
438 l_dummy_char varchar2(1);
439
440 BEGIN
441
442 --------------------- initialize -----------------------
443 SAVEPOINT create_sourcecode;
444
445 IF (AMS_DEBUG_HIGH_ON) THEN
446
447
448
449 AMS_Utility_PVT.debug_message(l_full_name||': start');
450
451 END IF;
452
453 IF FND_API.to_boolean(p_init_msg_list) THEN
454 FND_MSG_PUB.initialize;
455 END IF;
456
457 IF NOT FND_API.compatible_api_call(
458 l_api_version,
459 p_api_version,
460 l_api_name,
461 g_pkg_name
462 ) THEN
463 NULL;
464 --RAISE FND_API.g_exc_unexpected_error;
465 END IF;
466 x_return_status := FND_API.g_ret_sts_success;
467
468 ----------------------- validate -----------------------
469 -- check arc object
470 -- FOllowing line is modified by rrajesh on 02/02/02 to add a new object DILG.
471 --IF p_sourcecode_for not in ( 'CAMP', 'CSCH', 'OFFR', 'EVEH', 'EVEO','EONE')
472 IF p_sourcecode_for not in ( 'CAMP', 'CSCH', 'OFFR', 'EVEH', 'EVEO','EONE', 'DILG')
473 THEN
474 AMS_Utility_PVT.error_message('AMS_SCG_BAD_ARC_OBJECT');
475 RAISE FND_API.g_exc_error;
476 END IF;
477 /* 12-APR-2001 julou removed validation as Nari requested
478 -- 10-APR-2001 julou added validation for related source
479 -- validate related source code
480
481 -- check arc obj for related source code
482 IF p_releated_sourceobj NOT IN ( 'CAMP', 'CSCH', 'OFFR', 'EVEH', 'EVEO','EONE')
483 THEN
484 AMS_Utility_PVT.error_message('AMS_SCG_BAD_ARC_OBJECT');
485 RAISE FND_API.g_exc_error;
486 END IF;
487
488 -- check the existense of related source code
489 IF p_related_sourcecode IS NOT NULL THEN
490 OPEN c_related_source_code;
491 FETCH c_related_source_code INTO l_dummy_char;
492 IF c_related_source_code%NOTFOUND THEN
493 CLOSE c_related_source_code;
494 AMS_UTILITY_PVT.error_message('AMS_RELATE_SRC_CODE_NOT_FOUND');
495 RAISE FND_API.g_exc_error;
496 END IF;
497 CLOSE c_related_source_code;
498 END IF;
499 -- end of the added by julou
500 */
501 IF (AMS_DEBUG_HIGH_ON) THEN
502
503 AMS_Utility_PVT.debug_message(l_full_name ||': check if source code for object is already active');
504 END IF;
505
506 open c_active_object;
507 fetch c_active_object into l_dummy_char;
508 if c_active_object%found
509 then
510 close c_active_object;
511 -- raise an error The object already uses a valid source code.
512 AMS_UTILITY_PVT.error_message('AMS_OBJECT_CODE_ACTIVE');
513 RAISE FND_API.g_exc_error ;
514 end if;
515 close c_active_object;
516
517 IF (AMS_DEBUG_HIGH_ON) THEN
518
519
520
521 AMS_Utility_PVT.debug_message(l_full_name ||': check uniqueness');
522
523 END IF;
524
525 if is_source_code_unique(p_sourcecode) = FND_API.g_true
526 then
527 IF (AMS_DEBUG_HIGH_ON) THEN
528
529 AMS_Utility_PVT.debug_message(l_full_name ||': insert source code');
530 END IF;
531
532 -- fetch the source code id
533 open c_sourcecode_seq;
534 fetch c_sourcecode_seq into l_sourcecode_id;
535 close c_sourcecode_seq;
536
537 -- insert source code
538 insert into AMS_SOURCE_CODES (
539 SOURCE_CODE_ID,
540 LAST_UPDATE_DATE,
541 LAST_UPDATED_BY,
542 CREATION_DATE,
543 CREATED_BY,
544 LAST_UPDATE_LOGIN,
545 OBJECT_VERSION_NUMBER,
546 SOURCE_CODE,
547 SOURCE_CODE_FOR_ID,
548 ARC_SOURCE_CODE_FOR,
549 ACTIVE_FLAG
550 ,RELATED_SOURCE_CODE
551 ,RELATED_SOURCE_OBJECT
552 ,RELATED_SOURCE_ID)
553 values (
554 l_sourcecode_id,
555 SYSDATE,
556 FND_GLOBAL.user_id,
557 SYSDATE,
558 FND_GLOBAL.user_id,
559 FND_GLOBAL.conc_login_id,
560 1, -- object_version_number,
561 p_sourcecode,
562 p_sourcecode_for_id,
563 p_sourcecode_for,
564 l_active_flag
565 ,p_related_sourcecode
566 ,p_releated_sourceobj
567 ,p_related_sourceid
568 );
569 x_sourcecode_id := l_sourcecode_id;
570 else
571 -- if the source code is not unique then check if it is active
572 open c_source_code;
573 fetch c_source_code into l_sourcecode_id, l_sc_active_flag;
574 close c_source_code;
575 if l_sc_active_flag = 'N'
576 then
577 -- if the source is cancelled then modify it
578 IF (AMS_DEBUG_HIGH_ON) THEN
579
580 AMS_Utility_PVT.debug_message(l_full_name ||': modifying source code');
581 END IF;
582 modify_sourcecode(
583 p_source_code => p_sourcecode,
584 p_object_type => p_sourcecode_for,
585 p_object_id => p_sourcecode_for_id,
586 p_sourcecode_id => l_sourcecode_id,
587 p_related_sourcecode => p_related_sourcecode,
588 p_releated_sourceobj => p_releated_sourceobj,
589 p_related_sourceid => p_related_sourceid,
590 x_return_status => l_return_status
591 ) ;
592 IF l_return_status = FND_API.g_ret_sts_error THEN
593 RAISE FND_API.g_exc_error;
594 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
595 RAISE FND_API.g_exc_unexpected_error;
596 END IF;
597 elsif l_sc_active_flag = 'Y'
598 then
599 -- raise an error The source code is already being used.
600 AMS_UTILITY_PVT.error_message('AMS_DUPLICATE_SOURCE_CODE');
601 RAISE FND_API.g_exc_error;
602 end if;
603 end if;
604
605
606 IF FND_API.to_boolean(p_commit) THEN
607 COMMIT;
608 END IF;
609
610 FND_MSG_PUB.count_and_get(
611 p_encoded => FND_API.g_false,
612 p_count => x_msg_count,
613 p_data => x_msg_data
614 );
615
616 EXCEPTION
617
618 WHEN FND_API.g_exc_error THEN
619 ROLLBACK TO create_sourcecode;
620 x_return_status := FND_API.g_ret_sts_error;
621 FND_MSG_PUB.count_and_get(
622 p_encoded => FND_API.g_false,
623 p_count => x_msg_count,
624 p_data => x_msg_data
625 );
626
627 WHEN FND_API.g_exc_unexpected_error THEN
628 ROLLBACK TO create_sourcecode;
629 x_return_status := FND_API.g_ret_sts_unexp_error ;
630 FND_MSG_PUB.count_and_get(
631 p_encoded => FND_API.g_false,
632 p_count => x_msg_count,
633 p_data => x_msg_data
634 );
635
636
637 WHEN OTHERS THEN
638 ROLLBACK TO create_sourcecode;
639 x_return_status := FND_API.g_ret_sts_unexp_error ;
640
641 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
642 THEN
643 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
644 END IF;
645
646 FND_MSG_PUB.count_and_get(
647 p_encoded => FND_API.g_false,
648 p_count => x_msg_count,
649 p_data => x_msg_data
650 );
651
652 END create_sourcecode;
653
654
655 -----------------------------------------------------------------
656 -- PROCEDURE
657 -- revoke_sourcecode
658 -----------------------------------------------------------------
659 PROCEDURE revoke_sourcecode(
660 p_api_version IN NUMBER,
661 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
662 p_commit IN VARCHAR2 := FND_API.g_false,
663 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
664
665 x_return_status OUT NOCOPY VARCHAR2,
666 x_msg_count OUT NOCOPY NUMBER,
667 x_msg_data OUT NOCOPY VARCHAR2,
668
669 p_sourcecode IN VARCHAR2
670 )
671 IS
672
673 l_api_version CONSTANT NUMBER := 1.0;
674 l_api_name CONSTANT VARCHAR2(30) := 'revoke_source_code';
675 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
676
677 l_return_status VARCHAR2(1);
678
679 BEGIN
680
681 --------------------- initialize -----------------------
682 SAVEPOINT revoke_sourcecode;
683
684 IF (AMS_DEBUG_HIGH_ON) THEN
685
686
687
688 AMS_Utility_PVT.debug_message(l_full_name||': start');
689
690 END IF;
691
692 IF FND_API.to_boolean(p_init_msg_list) THEN
693 FND_MSG_PUB.initialize;
694 END IF;
695
696 IF NOT FND_API.compatible_api_call(
697 l_api_version,
698 p_api_version,
699 l_api_name,
700 g_pkg_name
701 ) THEN
702 RAISE FND_API.g_exc_unexpected_error;
703 END IF;
704
705 x_return_status := FND_API.g_ret_sts_success;
706
707 update AMS_SOURCE_CODES
708 set active_flag = 'N',
709 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
710 where SOURCE_CODE = p_sourcecode;
711
712 IF (SQL%NOTFOUND) THEN
713 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
714 THEN
715 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
716 FND_MSG_PUB.add;
717 END IF;
718 RAISE FND_API.g_exc_error;
719 END IF;
720
721 -------------------- finish --------------------------
722 IF FND_API.to_boolean(p_commit) THEN
723 COMMIT;
724 END IF;
725
726 FND_MSG_PUB.count_and_get(
727 p_encoded => FND_API.g_false,
728 p_count => x_msg_count,
729 p_data => x_msg_data
730 );
731
732 IF (AMS_DEBUG_HIGH_ON) THEN
733
734
735
736 AMS_Utility_PVT.debug_message(l_full_name ||': end');
737
738 END IF;
739
740 EXCEPTION
741
742 WHEN FND_API.g_exc_error THEN
743 ROLLBACK TO revoke_sourcecode;
744 x_return_status := FND_API.g_ret_sts_error;
745 FND_MSG_PUB.count_and_get(
746 p_encoded => FND_API.g_false,
747 p_count => x_msg_count,
748 p_data => x_msg_data
749 );
750
751 WHEN FND_API.g_exc_unexpected_error THEN
752 ROLLBACK TO revoke_sourcecode;
753 x_return_status := FND_API.g_ret_sts_unexp_error ;
754 FND_MSG_PUB.count_and_get(
755 p_encoded => FND_API.g_false,
756 p_count => x_msg_count,
757 p_data => x_msg_data
758 );
759
760 WHEN OTHERS THEN
761 ROLLBACK TO revoke_sourcecode;
762 x_return_status := FND_API.g_ret_sts_unexp_error ;
763
764 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
765 THEN
766 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
767 END IF;
768
769 FND_MSG_PUB.count_and_get(
770 p_encoded => FND_API.g_false,
771 p_count => x_msg_count,
772 p_data => x_msg_data
773 );
774
775 END revoke_sourcecode;
776
777
778 -----------------------------------------------------------------
779 -- PROCEDURE
780 -- modify_sourcecode
781 -----------------------------------------------------------------
782 PROCEDURE modify_sourcecode(
783 p_source_code IN VARCHAR2,
784 p_object_type IN VARCHAR2,
785 p_object_id IN NUMBER,
786 p_sourcecode_id IN NUMBER,
787 p_related_sourcecode IN VARCHAR2 := NULL,
788 p_releated_sourceobj IN VARCHAR2 := NULL,
789 p_related_sourceid IN NUMBER := NULL,
790
791 x_return_status OUT NOCOPY VARCHAR2
792 )
793 IS
794
795 l_return_status VARCHAR2(1);
796
797 BEGIN
798
799 --------------------- initialize -----------------------
800
801 x_return_status := FND_API.g_ret_sts_success;
802
803 IF (AMS_DEBUG_HIGH_ON) THEN
804
805
806
807 AMS_Utility_PVT.debug_message(' modifying source code record');
808
809 END IF;
810
811 update AMS_SOURCE_CODES
812 set ARC_SOURCE_CODE_FOR = p_object_type,
813 SOURCE_CODE_FOR_ID = p_object_id,
814 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
815 ACTIVE_FLAG = 'Y'
816 ,RELATED_SOURCE_CODE = p_related_sourcecode
817 ,RELATED_SOURCE_OBJECT = p_releated_sourceobj
818 ,RELATED_SOURCE_ID = p_related_sourceid
819 where SOURCE_CODE_ID = p_sourcecode_id;
820
821 IF (SQL%NOTFOUND) THEN
822 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
823 THEN
824 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
825 FND_MSG_PUB.add;
826 END IF;
827 x_return_status := FND_API.g_ret_sts_error;
828 END IF;
829
830 IF (AMS_DEBUG_HIGH_ON) THEN
831
832
833
834 AMS_Utility_PVT.debug_message(' modify source code end ');
835
836 END IF;
837
838
839 END modify_sourcecode;
840
841
842 FUNCTION get_unique_sequence (
843 p_geo_code IN VARCHAR2,
844 p_date_code IN VARCHAR2,
845 p_cust_code IN VARCHAR2
846 )
847 RETURN VARCHAR2
848 IS
849 PRAGMA AUTONOMOUS_TRANSACTION;
850
851 L_LENGTH_PROFILE CONSTANT VARCHAR2(30) := 'AMS_SRCGEN_SEQUENCE_LENGTH';
852 MIN_SEQUENCE_LENGTH CONSTANT NUMBER := 1;
853 MAX_SEQUENCE_LENGTH CONSTANT NUMBER := 10;
854
855 l_sequence_length NUMBER;
856 l_sequence_value NUMBER;
857 l_sequence_code VARCHAR2(10);
858 l_number_format VARCHAR2(30) := '0';
859 l_code_chars VARCHAR2(30);
860 l_source_code VARCHAR2(30);
861
862 l_ref_sequence NUMBER; -- reference number used to determine if sequences exhausted
863
864 CURSOR c_sequence_value (x_code_key VARCHAR2) IS
865 SELECT gde.scode_number_element
866 FROM ams_generated_codes gde
867 WHERE gde.scode_char_element = x_code_key
868 FOR UPDATE
869 ;
870 BEGIN
871 l_sequence_length := TO_NUMBER (FND_PROFILE.value (L_LENGTH_PROFILE));
872 l_code_chars := p_geo_code || p_date_code || p_cust_code;
873
874 --
875 -- choang - 18-May-2000
876 -- Added error handling for sequence_length.
877 IF l_sequence_length IS NULL THEN
878 AMS_Utility_PVT.error_message ('AMS_SRCGEN_NO_NUMBER_SIZE');
879 RAISE FND_API.g_exc_error;
880 END IF;
881 IF l_sequence_length NOT BETWEEN MIN_SEQUENCE_LENGTH AND MAX_SEQUENCE_LENGTH THEN
882 AMS_Utility_PVT.error_message ('AMS_SRCGEN_BAD_NUMBER_SIZE');
883 RAISE FND_API.g_exc_error;
884 END IF;
885
886 -- Pad the number format with 9's
887 -- if the number of digits is more
888 -- than 2.
889 FOR i IN 2..l_sequence_length LOOP
890 l_number_format := l_number_format || '9';
891 END LOOP;
892 OPEN c_sequence_value (l_code_chars);
893 FETCH c_sequence_value INTO l_sequence_value;
894 CLOSE c_sequence_value;
895 IF l_sequence_value IS NULL THEN
896 l_sequence_value := 0;
897
898 -- Create a generated code
899 INSERT INTO ams_generated_codes (
900 gen_code_id,
901 last_update_date,
902 last_updated_by,
903 creation_date,
904 created_by,
905 last_update_login,
906 object_version_number,
907 scode_char_element,
908 scode_number_element,
909 arc_source_code_for
910 ) VALUES (
911 ams_source_codes_gen_s.NEXTVAL,
912 SYSDATE,
913 FND_GLOBAL.user_id,
914 SYSDATE,
915 FND_GLObAL.user_id,
916 FND_GLOBAL.conc_login_id,
917 1, -- object version number
918 l_code_chars,
919 0,
920 'NONE' -- Not generated for any specific object
921 );
922
923 COMMIT;
924 ELSIF LENGTH (l_sequence_value) > l_sequence_length THEN
925 l_sequence_value := MOD (l_sequence_value, POWER(10, l_sequence_length) - 1);
926 END IF;
927
928 -- exit loop only after a unique code is generated
929 l_ref_sequence := l_sequence_value;
930 LOOP
931 l_sequence_value := MOD (l_sequence_value + 1, POWER(10, l_sequence_length) - 1);
932 l_sequence_code := LTRIM (TO_CHAR (l_sequence_value, l_number_format));
933 l_source_code := p_geo_code || p_date_code || l_sequence_code || p_cust_code;
934
935 EXIT WHEN is_source_code_unique (l_source_code) = FND_API.g_true;
936
937 --
938 -- Loop through all possible sequences
939 -- within the range of digits before
940 -- giving an error message.
941 IF l_ref_sequence = l_sequence_value THEN
942 AMS_Utility_PVT.error_message ('AMS_SRCGEN_OUT_OF_NUMBERS');
943 RAISE FND_API.g_exc_error;
944 END IF;
945 END LOOP;
946
947 -- Update the generate code with the new
948 -- upper limit of the numeric sequence.
949 UPDATE ams_generated_codes gde
950 SET gde.scode_number_element = l_sequence_value
951 WHERE gde.scode_char_element = l_code_chars;
952
953 COMMIT;
954
955 RETURN l_sequence_code;
956 EXCEPTION
957 WHEN OTHERS THEN
958 IF c_sequence_value%ISOPEN THEN
959 CLOSE c_sequence_value;
960 END IF;
961 RAISE;
962 END get_unique_sequence;
963
964
965 END AMS_SourceCode_PVT;