1 PACKAGE BODY OTA_OTARPBUD_XMLP_PKG AS
2 /* $Header: OTARPBUDB.pls 120.1 2007/12/07 05:59:35 amakrish noship $ */
3 function BeforeReport return boolean is
4
5 cursor c_activity is
6 select name
7 from ota_activity_definitions_tl
8 where activity_id = p_activity_id
9 and language = userenv('LANG') ;
10
11 cursor c_activity_version is
12 select version_name
13 from ota_activity_versions_tl
14 where activity_version_id = p_activity_version_id
15 and language = userenv('LANG') ;
16
17 cursor c_event(l_event_id number) is
18 select title
19 from ota_events_tl
20 where event_id = l_event_id
21 and language = userenv('LANG') ;
22
23
24
25 cursor c_res_booking_status(l_lookup_code varchar2) is
26 select meaning
27 from hr_lookups
28 where lookup_code = l_lookup_code
29 and lookup_type = 'RESOURCE_BOOKING_STATUS';
30
31 cursor c_transfer_status(l_lookup_code varchar2) is
32 select meaning
33 from hr_lookups
34 where lookup_code = l_lookup_code
35 and lookup_type = 'GL_TRANSFER_STATUS';
36
37
38
39 begin
40 --hr_standard.event('BEFORE REPORT');
41
42 c_business_group_name :=
43 hr_reports.get_business_group(p_business_group_id);
44
45 if p_activity_id is not null then
46 open c_activity;
47 fetch c_activity into c_activity_name;
48 close c_activity;
49 end if;
50
51 if p_activity_version_id is not null then
52 open c_activity_version;
53 fetch c_activity_version into c_activity_version_name;
54 close c_activity_version;
55 end if;
56
57 if p_event_id is not null then
58 open c_event(p_event_id);
59 fetch c_event into c_event_title;
60 close c_event;
61 end if;
62
63 if p_activity_id is not null then
64 open c_event(p_program_id);
65 fetch c_event into c_program_name;
66 close c_event;
67 end if;
68
69
70 if p_transfer_status is not null then
71 open c_transfer_status(p_transfer_status);
72 fetch c_transfer_status into cp_transfer_status;
73 close c_transfer_status;
74 end if;
75
76 if p_resource_booking_status is not null then
77 open c_res_booking_status(p_resource_booking_status);
78 fetch c_res_booking_status into cp_resource_booking_status;
79 close c_res_booking_status;
80 end if;
81
82 LP_SESSION_DATE := P_SESSION_DATE;
83 return (TRUE);
84 end;
85
86 function CF_eff_dateFormula return Date is
87 temp date;
88 begin
89
90 select effective_date
91 into temp
92 from fnd_sessions
93 where session_id=userenv('SESSIONID');
94
95 return temp;
96
97 RETURN NULL;
98
99 RETURN NULL; exception
100 when others then
101 temp:=sysdate;
102 /*srw.message(20,to_char(temp));*/null;
103
104 return temp;
105 end;
106
107 function cf_conv_amountformula(event_id in number, currency_code1 in varchar2, money_amount in number, cf_eff_date in date, cf_currency_type in varchar2) return number is
108 result number;
109 begin
110 if (cp_prev_event is NULL or cp_prev_event <> event_id)
111 then
112 cp_rev_curr:=0;
113 cp_prev_event:=event_id;
114 end if;
115
116
117 if currency_code1=p_delegate_display_currency or currency_code1 is null or p_delegate_display_currency is null
118 then
119 cp_conv:= money_amount;
120 else
121 result:=hr_currency_pkg.convert_amount_sql(currency_code1,
122 p_delegate_display_currency,
123 cf_eff_date,
124 money_amount,
125 cf_currency_type);
126 if result =-1 or result=-2 then
127 cp_rev_curr:=cp_rev_curr+1;
128 else
129 cp_conv:= result;
130 end if;
131 end if;
132 return 0;
133 end;
134
135 function cf_conv1formula(event_id in number, currency_code3 in varchar2, money_amount2 in number, cf_eff_date in date, cf_currency_type in varchar2) return number is
136 result number;
137 begin
138 if (cp_prev_event2 is NULL or cp_prev_event2 <> event_id)
139 then
140 cp_cost_curr:=0;
141 cp_prev_event2:=event_id;
142 end if;
143 if currency_code3=p_delegate_display_currency
144 then
145 cp_conv1:= money_amount2;
146 else
147 result:=hr_currency_pkg.convert_amount_sql(currency_code3,
148 nvl(p_delegate_display_currency,currency_code3),
149 cf_eff_date,
150 money_amount2,
151 cf_currency_type);
152 if result =-1 or result=-2 then
153 cp_cost_curr:=cp_cost_curr+1;
154 else
155 cp_conv1:= result;
156 end if;
157 end if;
158 return 0;
159 end;
160
161 function cf_currency_typeformula(cf_eff_date in date) return varchar2 is
162 begin
163 return hr_currency_pkg.get_rate_type(
164 p_business_group_id,
165 cf_eff_date,
166 'R');
167
168 exception
169 when others then
170 /*srw.message(10,'get_currency_type procedure failed' );*/null;
171
172 return NULL;
173
174 end;
175
176 function cf_venueformula
177 (event_id in number) return char
178 is
179 cursor c_venue(pevent_id number) is
180 select replace(name,fnd_global.local_chr(10),fnd_global.local_chr(46))
181 from hr_all_organization_units org,
182 ota_events_vl evt
183 where evt.event_id = pevent_id
184 and evt.training_center_id = org.organization_id;
185
186 begin
187
188
189 cp_venue := 'None listed';
190 open c_venue(event_id);
191 fetch c_venue into cp_venue;
192 close c_venue;
193 return(to_char(null));
194
195 end;
196
197 function AfterPForm return boolean is
198 begin
199
200 if (p_activity_version_id is not null or p_activity_id is not null or p_event_id is not null or p_program_id is not null) then
201 null;
202 else
203 /*srw.message(10,' One of the below Parameters Must be entered ');*/null;
204
205 /*srw.message(10,' Activity Type, Activity, Event Title, Program ');*/null;
206
207 raise_application_error(-20101,null);/*srw.program_abort;*/null;
208
209 end if;
210
211
212
213 return (TRUE);
214 end;
215
216 function AfterReport return boolean is
217 begin
218 --hr_standard.event('AFTER REPORT');
219 return (TRUE);
220 end;
221
222 --Functions to refer Oracle report placeholders--
223
224 Function CP_rev_curr_p return number is
225 Begin
226 return CP_rev_curr;
227 END;
228 Function CP_cost_curr_p return number is
229 Begin
230 return CP_cost_curr;
231 END;
232 Function CP_Venue_p return varchar2 is
233 Begin
234 return CP_Venue;
235 END;
236 Function CP_prev_event_p return number is
237 Begin
238 return CP_prev_event;
239 END;
240 Function CP_conv_p return number is
241 Begin
242 return CP_conv;
243 END;
244 Function CP_prev_event2_p return number is
245 Begin
246 return CP_prev_event2;
247 END;
248 Function CP_conv1_p return number is
249 Begin
250 return CP_conv1;
251 END;
252 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
253 Begin
254 return C_BUSINESS_GROUP_NAME;
255 END;
256 Function C_REPORT_SUBTITLE_p return varchar2 is
257 Begin
258 return C_REPORT_SUBTITLE;
259 END;
260 Function C_ACTIVITY_NAME_p return varchar2 is
261 Begin
262 return C_ACTIVITY_NAME;
263 END;
264 Function C_ACTIVITY_VERSION_NAME_p return varchar2 is
265 Begin
266 return C_ACTIVITY_VERSION_NAME;
267 END;
268 Function C_EVENT_TITLE_p return varchar2 is
269 Begin
270 return C_EVENT_TITLE;
271 END;
272 Function C_PROGRAM_NAME_p return varchar2 is
273 Begin
274 return C_PROGRAM_NAME;
275 END;
276 Function CP_transfer_status_p return varchar2 is
277 Begin
278 return CP_transfer_status;
279 END;
280 Function CP_resource_booking_status_p return varchar2 is
281 Begin
282 return CP_resource_booking_status;
283 END;
284 END OTA_OTARPBUD_XMLP_PKG ;
[Home] [Help]