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