1 PACKAGE BODY OTA_OTARPSIS_XMLP_PKG AS
2 /* $Header: OTARPSISB.pls 120.4.12020000.2 2012/11/06 08:38:37 jaysridh ship $ */
3 calendar_aware number:=2;
4
5 function BeforeReport return boolean is
6 begin
7
8 --hr_standard.event('BEFORE REPORT') ;
9
10 return (TRUE);
11 end;
12
13 function CF_OPTIONAL_COLUMNFormula return Char is
14 begin
15
16 IF P_OPTIONAL_COLUMN = NULL THEN
17 return ('');
18 ELSE
19 return (P_OPTIONAL_COLUMN);
20 END IF;
21
22 end;
23
24 FUNCTION AfterPForm
25 RETURN BOOLEAN
26 IS
27 Cursor c_event(p_event_id number) is
28 Select title
29 from ota_events_tl
30 where event_id = p_event_id
31 and language = userenv('LANG') ;
32
33 Cursor c_training_center(center_id number) is
34 Select name
35 from hr_all_organization_units org
36 where ORG.ORGANIZATION_ID = center_id;
37
38 Cursor c_yes_no_meaning(p_code varchar2) is
39 Select meaning
40 from fnd_lookups
41 where lookup_type='YES_NO' and lookup_code= p_code;
42
43 BEGIN
44
45
46 IF p_event_id IS NULL and p_training_center_id IS NULL and p_course_start_date IS NULL
47 and p_course_end_date IS NULL and p_booking_id IS NULL and P_BOOKING_ID IS NULL THEN
48
49 /*SRW.message(100,'This report cannot be run without at least one parameter entered.');*/null;
50
51 RAISE_application_error(-20101,null);/*SRW.program_abort;*/null;
52
53 RETURN(FALSE);
54
55 END IF;
56
57 IF p_course_start_date > p_course_end_date THEN
58
59 /*SRW.message(200,'The course start start cannot be later than the course end date.');*/null;
60
61 RAISE_application_error(-20101,null);/*SRW.program_abort;*/null;
62
63 RETURN(FALSE);
64
65 END IF;
66
67 p_and := TO_CHAR(NULL);
68 p_trainer_and := ' ';
69
70 IF p_event_id IS NOT NULL THEN
71 p_and := p_and ||' AND evt.event_id = :p_event_id';
72 p_trainer_and := p_trainer_and || ' AND rb.event_id = :p_event_id';
73 END IF;
74
75 IF (p_course_start_date IS NOT NULL AND p_course_end_date IS NOT NULL) THEN
76 p_and := p_and || ' AND evt.course_start_date = fnd_date.canonical_to_date(:p_course_start_date)
77 AND evt.course_end_date = fnd_date.canonical_to_date(:p_course_end_date)';
78 END IF;
79
80 IF (p_course_start_date IS NOT NULL AND p_course_end_date IS NULL) THEN
81 p_and := p_and || ' AND evt.course_start_date = fnd_date.canonical_to_date(:p_course_start_date)';
82 END IF;
83
84 IF (p_course_start_date IS NULL AND p_course_end_date IS NOT NULL) THEN
85 p_and := p_and || ' AND evt.course_end_date = fnd_date.canonical_to_date(:p_course_end_date)';
86 END IF;
87
88 IF p_training_center_id IS NOT NULL THEN
89 p_and := p_and || ' AND evt.training_center_id = :p_training_center_id';
90 END IF;
91
92 IF p_booking_id IS NOT NULL THEN
93 p_and := p_and || ' AND db.booking_id = :p_booking_id';
94 END IF;
95
96
97
98
99 open c_event(p_event_id );
100 fetch c_event into p_event_name ;
101 close c_event;
102
103
104 open c_training_center(p_training_center_id );
105 fetch c_training_center into p_training_center_name ;
106 close c_training_center;
107
108 --Bug 14735864
109 open c_yes_no_meaning(P_display_pmt_conf );
110 fetch c_yes_no_meaning into P_DISPLAY_PMT_CONF_MEAN ;
111 close c_yes_no_meaning;
112
113 open c_yes_no_meaning(P_display_trainer_signature );
114 fetch c_yes_no_meaning into P_DISPLAY_TRAINER_SIG_MEAN ;
115 close c_yes_no_meaning;
116
117
118 RETURN(TRUE);
119
120 END;
121
122 function cf_venueformula
123 (event_id in number) return char
124 is
125 begin
126
127 select replace(name,fnd_global.local_chr(10),fnd_global.local_chr(46))
128 into cp_venue
129 from ota_suppliable_resources_tl sr,
130 ota_resource_bookings rb
131 where rb.event_id = cf_venueformula.event_id
132 and rb.primary_venue_flag = 'Y'
133 and sr.supplied_resource_id = rb.supplied_resource_id
134 and sr.language = userenv('LANG') ;
135
136 return(to_char(null));
137
138 exception
139 when no_data_found then
140 cp_venue := fnd_message.get_string('OTA','OTA_467241_RPT_VENUE_NULL');
141 return(to_char(null));
142 end;
143
144 function CF_course_end_dateFormula return Char is
145 begin
146 select fnd_date.date_to_displaydate(to_date(substr((p_course_end_date),1,10),'yyyy/mm/dd'),calendar_aware)
147 into cp_course_end_date
148 from dual;
149
150 return(to_char(null));
151 end;
152
153 function CF_course_start_dateFormula return Char is
154 begin
155 select fnd_date.date_to_displaydate(to_date(substr((p_course_start_date),1,10),'yyyy/mm/dd'),calendar_aware)
156 into cp_course_start_date
157 from dual;
158
159 return(to_char(null));
160 end;
161
162 function cf_event_durationformula(course_end in date, course_start in date) return number is
163 begin
164 return (course_end-course_start) +1;
165
166 end;
167
168 function cf_sign4formula(CF_event_duration in number) return char is
169 begin
170
171 if (CF_event_duration < '5') then
172 return(P_OPTIONAL_COLUMN);
173 else
174 return(fnd_message.get_string('OTA','OTA_467242_RPT_SIS_OPCO_HEADER'));
175 end if;
176
177 end;
178
179 function cf_sign3formula(CF_event_duration in number) return char is
180 begin
181 if (CF_event_duration < '4') then
182 return(P_OPTIONAL_COLUMN);
183 else
184 return(fnd_message.get_string('OTA','OTA_467242_RPT_SIS_OPCO_HEADER'));
185 end if;
186
187
188 end;
189
190 function cf_sign2formula(CF_event_duration in number) return char is
191 begin
192 if (CF_event_duration < '3') then
193 return(P_OPTIONAL_COLUMN);
194 else
195 return(fnd_message.get_string('OTA','OTA_467242_RPT_SIS_OPCO_HEADER'));
196 end if;
197
198
199 end;
200
201 function cf_sign1formula(CF_event_duration in number) return char is
202 begin
203 if (CF_event_duration < '2') then
204 return(P_OPTIONAL_COLUMN);
205 else
206 return(fnd_message.get_string('OTA','OTA_467242_RPT_SIS_OPCO_HEADER'));
207 end if;
208
209
210 end;
211
212 function CF_BG_NAMEFormula return Char is
213 begin
214 c_business_group_name := hr_reports.get_business_group(p_business_group_id);
215 return(to_char(null));
216
217 end;
218
219 function AfterReport return boolean is
220 begin
221 --hr_standard.event('AFTER REPORT') ;
222 return (TRUE);
223 end;
224
225 --Functions to refer Oracle report placeholders--
226
227 Function CP_venue_p return varchar2 is
228 Begin
229 return CP_venue;
230 END;
231 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
232 Begin
233 return C_BUSINESS_GROUP_NAME;
234 END;
235 Function C_REPORT_SUBTITLE_p return varchar2 is
236 Begin
237 return C_REPORT_SUBTITLE;
238 END;
239 Function C_EVENT_TITLE_p return varchar2 is
240 Begin
241 return C_EVENT_TITLE;
242 END;
243 Function CP_course_start_date_p return varchar2 is
244 Begin
245 return CP_course_start_date;
246 END;
247 Function CP_course_end_date_p return varchar2 is
248 Begin
249 return CP_course_end_date;
250 END;
251
252 --Bug 14735864
253 /* This function is used to return the meaning of the code 'Y' and 'N'
254 */
255 Function cf_yes_no_meaning(p_code varchar2) return varchar2 is
256 l_meaning varchar2(80);
257 Begin
258 Select meaning
259 into l_meaning
260 from fnd_lookups
261 where lookup_type='YES_NO' and lookup_code= p_code;
262
263 return(l_meaning);
264
265 exception
266 when no_data_found then
267 l_meaning := null;
268 return(l_meaning);
269 End cf_yes_no_meaning;
270
271 END OTA_OTARPSIS_XMLP_PKG ;
[Home] [Help]