1 PACKAGE BODY OTA_OTARPREG_XMLP_PKG AS
2 /* $Header: OTARPREGB.pls 120.1 2007/12/07 05:59:53 amakrish noship $ */
3 function BeforeReport return boolean is
4
5 begin
6 --hr_standard.event('BEFORE REPORT');
7
8 c_business_group_name := hr_reports.get_business_group(p_business_group_id);
9
10 return (TRUE);
11
12 end;
13
14 FUNCTION AfterPForm
15 RETURN BOOLEAN
16 IS
17 Cursor c_event(p_event_id number) is
18 Select title
19 from ota_events_tl
20 where event_id = p_event_id
21 and language = userenv('LANG');
22
23 Cursor c_training_center(center_id number) is
24 Select name
25 from hr_all_organization_units org
26 where ORG.ORGANIZATION_ID = center_id;
27
28 BEGIN
29
30 IF p_event_id IS NULL and p_training_center_id IS NULL and p_course_start_date IS NULL
31 and p_course_end_date IS NULL and p_event_type IS NULL THEN
32
33 /*SRW.message(100,'This report cannot be run without at least one parameter entered.');*/null;
34
35 RAISE_application_error(-20101,null);/*SRW.program_abort;*/null;
36
37 RETURN(FALSE);
38
39 END IF;
40
41
42 IF p_course_start_date > p_course_end_date THEN
43
44 /*SRW.message(200,'The course start start cannot be later than the course end date.');*/null;
45
46 RAISE_application_error(-20101,null);/*SRW.program_abort;*/null;
47
48 RETURN(FALSE);
49
50 END IF;
51
52 p_and := '';
53
54
55 IF p_event_id IS NOT NULL THEN
56 p_and := p_and ||' AND evt.event_id = :p_event_id';
57 END IF;
58
59
60
61 IF (p_course_start_date IS NOT NULL AND p_course_end_date IS NOT NULL) THEN
62 p_and := p_and || ' AND evt.course_start_date = fnd_date.canonical_to_date(:p_course_start_date)
63 AND evt.course_end_date = fnd_date.canonical_to_date(:p_course_end_date)';
64 END IF;
65
66
67 IF (p_course_start_date IS NOT NULL AND p_course_end_date IS NULL) THEN
68 p_and := p_and || ' AND evt.course_start_date = fnd_date.canonical_to_date(:p_course_start_date)';
69 END IF;
70
71 IF (p_course_start_date IS NULL AND p_course_end_date IS NOT NULL) THEN
72 p_and := p_and || ' AND evt.course_end_date = fnd_date.canonical_to_date(:p_course_end_date)';
73 END IF;
74
75 IF p_training_center_id IS NOT NULL THEN
76 p_and := p_and || ' AND evt.training_center_id = :p_training_center_id';
77 END IF;
78
79 IF (p_event_type IS NOT NULL) THEN
80 p_and := p_and || ' AND ((UPPER(evt.event_type) LIKE UPPER(:p_event_type||''%''))
81 OR (UPPER(evt.event_type) LIKE '||'''%'''||
82 ' AND UPPER(:p_event_type) = '||'''ALL'''||'))';
83 END IF;
84
85
86
87
88
89 open c_event(p_event_id );
90 fetch c_event into p_event_name ;
91 close c_event;
92
93
94 open c_training_center(p_training_center_id );
95 fetch c_training_center into p_training_center_name ;
96 close c_training_center;
97
98
99
100
101 RETURN(TRUE);
102
103 END;
104
105 function CF_current_dateFormula
106 return Char
107 is
108 begin
109
110 select fnd_date.date_to_displaydate(sysdate)
111 into cp_current_date
112 from dual;
113
114 return(to_char(null));
115
116 end;
117
118 function cf_venueformula
119 (event_id in number) return char
120 is
121 begin
122
123 select replace(name,fnd_global.local_chr(10),fnd_global.local_chr(46))
124 into cp_venue
125 from ota_suppliable_resources_tl sr,
126 ota_resource_bookings rb
127 where rb.event_id = cf_venueformula.event_id
128 and rb.primary_venue_flag = 'Y'
129 and sr.supplied_resource_id = rb.supplied_resource_id
130 and sr.language = userenv('LANG') ;
131
132 return(to_char(null));
133
134 exception
135 when no_data_found then
136 cp_venue := 'None listed';
137 return(to_char(null));
138
139 end;
140
141 function CF_session_dateFormula return Char is
142 begin
143 select fnd_date.date_to_displaydate(p_session_date) into cp_session_date
144 from dual;
145
146 return (to_char(NULL));
147 end;
148
149 function CF_course_start_dateFormula return Char is
150 begin
151 select fnd_date.date_to_displaydate(to_date(substr((p_course_start_date),1,10),'yyyy/mm/dd'))
152 into cp_course_start_date
153 from dual;
154
155 return(to_char(null));
156 end;
157
158 function CF_course_end_dateFormula return Char is
159 begin
160 select fnd_date.date_to_displaydate(to_date(substr((p_course_end_date),1,10),'yyyy/mm/dd'))
161 into cp_course_end_date
162 from dual;
163
164 return(to_char(null));
165 end;
166
167 function AfterReport return boolean is
168 begin
169 --hr_standard.event('AFTER REPORT');
170 return (TRUE);
171 end;
172
173 --Functions to refer Oracle report placeholders--
174
175 Function CP_venue_p return varchar2 is
176 Begin
177 return CP_venue;
178 END;
179 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
180 Begin
181 return C_BUSINESS_GROUP_NAME;
182 END;
183 Function C_REPORT_SUBTITLE_p return varchar2 is
184 Begin
185 return C_REPORT_SUBTITLE;
186 END;
187 Function C_EVENT_TITLE_p return varchar2 is
188 Begin
189 return C_EVENT_TITLE;
190 END;
191 Function CP_current_date_p return varchar2 is
192 Begin
193 return CP_current_date;
194 END;
195 Function CP_session_date_p return varchar2 is
196 Begin
197 return CP_session_date;
198 END;
199 Function CP_course_start_date_p return varchar2 is
200 Begin
201 return CP_course_start_date;
202 END;
203 Function CP_course_end_date_p return varchar2 is
204 Begin
205 return CP_course_end_date;
206 END;
207 END OTA_OTARPREG_XMLP_PKG ;
[Home] [Help]