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