PACKAGE BODY: APPS.OTA_OM_UTIL

Source

1 PACKAGE BODY OTA_OM_UTIL AS
2 /* $Header: otomutil.pkb 115.2 2003/04/09 06:29:54 pbhasin noship $ */
3 --
4 -- Package Variables
5 --
6 g_package  	VARCHAR2(33) := 'OTA_OM_UTIL.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< <get_event_details> >------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 PROCEDURE get_event_detail
13   (p_line_id		IN 	NUMBER,
14    p_UOM              	IN	VARCHAR2,
15    x_activity_name	OUT     NOCOPY VARCHAR2,
16    x_event_title	OUT 	NOCOPY VARCHAR2,
17    x_course_start_date	OUT	NOCOPY DATE,
18    x_course_end_date	OUT	NOCOPY DATE
19   ) IS
20   --
21   -- Declare cursors and local variables
22   --
23   l_proc                varchar2(72) := g_package||'get_event_detail';
24   --
25   CURSOR evt_cur IS
26   SELECT act.version_name,
27 	 evt.title,
28 	 evt.course_start_date,
29 	 evt.course_end_date
30     FROM ota_events_vl evt,  -- MLS change _vl added
31 	 ota_activity_versions_tl act -- MLS change _tl added
32    WHERE act.activity_version_id = evt.activity_version_id
33      AND evt.line_id = p_line_id;
34 
35 
36   CURSOR enr_cur IS
37   SELECT act.version_name,
38 	 evt.title,
39 	 evt.course_start_date,
40 	 evt.course_end_date
41     FROM ota_events_vl evt, -- MLS change _vl added
42 	 ota_activity_versions_tl act, --MLS change _tl added
43 	 ota_delegate_bookings dlb
44    WHERE act.activity_version_id = evt.activity_version_id
45      AND dlb.event_id = evt.event_id
46      AND dlb.line_id = p_line_id;
47 
48 l_no_record 	BOOLEAN := TRUE;
49 
50 BEGIN
51   hr_utility.set_location('Entering:'|| l_proc, 10);
52   --
53   	   IF p_UOM = 'ENR' THEN
54 		   FOR enr_rec IN enr_cur
55 		  LOOP
56 		       x_activity_name := enr_rec.version_name;
57 		       x_event_title := enr_rec.title;
58 		       x_course_start_date := enr_rec.course_start_date;
59 		       x_course_end_date := enr_rec.course_end_date;
60 		       l_no_record := FALSE;
61 		   END LOOP;
62 
63 	ELSIF p_UOM = 'EVT' THEN
64 		   FOR evt_rec IN evt_cur
65 		  LOOP
66 		       x_activity_name := evt_rec.version_name;
67 		       x_event_title := evt_rec.title;
68 		       x_course_start_date := evt_rec.course_start_date;
69 		       x_course_end_date := evt_rec.course_end_date;
70 		       l_no_record := FALSE;
71 		   END LOOP;
72 	 END IF;
73 
74 	  IF l_no_record THEN
75 	     x_activity_name := NULL;
76 	     x_event_title := NULL;
77 	     x_course_start_date := NULL;
78      	     x_course_end_date := NULL;
79 	 END IF;
80 
81   --
82   --
83   hr_utility.set_location(' Leaving:'||l_proc, 70);
84 EXCEPTION
85   WHEN others THEN
86     --
87     -- A validation or unexpected error has occured
88     --
89     hr_utility.set_location(' Leaving:'||l_proc, 90);
90 END get_event_detail;
91 --
92 
93 
94 END OTA_OM_UTIL;