1 PACKAGE BODY OTA_OTARPATT_XMLP_PKG AS
2 /* $Header: OTARPATTB.pls 120.3.12020000.3 2012/11/02 06:19:11 jaysridh ship $ */
3 calendar_aware number:=2;
4
5 function BeforeReport return boolean is
6 begin
7 --hr_standard.event('BEFORE REPORT');
8 LP_SESSION_DATE:= to_char(P_SESSION_DATE,'DD-MON-YY');
9 return (TRUE);
10 end;
11
12 function CF_OPTIONAL_COLUMNFormula return Char is
13 begin
14
15 IF P_OPTIONAL_COLUMN = NULL THEN
16 return ('');
17 ELSE
18 return (P_OPTIONAL_COLUMN);
19 END IF;
20
21 end;
22
23 FUNCTION AfterPForm
24 RETURN BOOLEAN
25 IS
26 Cursor c_event(p_event_id number) is
27 Select title
28 from ota_events_tl
29 where event_id = p_event_id
30 and language = userenv('LANG') ;
31
32 Cursor c_training_center(center_id number) is
33 Select name
34 from hr_all_organization_units org
35 where ORG.ORGANIZATION_ID = center_id;
36
37 Cursor c_yes_no_meaning(p_code varchar2) is
38 Select meaning
39 from fnd_lookups
40 where lookup_type='YES_NO' and lookup_code= p_code;
41
42 BEGIN
43
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,'This report cannot be run without at least one parameter entered.');/*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,'The course start start cannot be later than the course end date.');/*SRW.program_abort;*/null;
62
63 RETURN(FALSE);
64
65 END IF;
66
67 p_and := TO_CHAR(NULL);
68 p_trainer_and := TO_CHAR(NULL);
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
109 --Added during DT Fixes
110 if p_and is null then
111 p_and := ' ';
112 end if;
113
114 if P_trainer_and is null then
115 P_trainer_and := ' ';
116 end if;
117 --End of DT Fixes
118
119 --Bug 14735864
120 open c_yes_no_meaning(P_display_pmt_conf );
121 fetch c_yes_no_meaning into P_DISPLAY_PMT_CONF_MEAN ;
122 close c_yes_no_meaning;
123
124 open c_yes_no_meaning(P_display_trainer_signature );
125 fetch c_yes_no_meaning into P_DISPLAY_TRAINER_SIG_MEAN ;
126 close c_yes_no_meaning;
127
128
129 RETURN(TRUE);
130
131 END;
132
133 function cf_venueformula
134 (event_id in number) return char
135 is
136 begin
137
138 select replace(name,fnd_global.local_chr(10),fnd_global.local_chr(46))
139 into cp_venue
140 from ota_suppliable_resources_tl sr,
141 ota_resource_bookings rb
142 where rb.event_id = cf_venueformula.event_id
143 and rb.primary_venue_flag = 'Y'
144 and sr.supplied_resource_id = rb.supplied_resource_id
145 and sr.language = userenv('LANG') ;
146 --Start of DT Fixes
147 -- return(to_char(null));
148 return cp_venue;
149 --End of DT Fixes
150 exception
151 when no_data_found then
152 cp_venue := fnd_message.get_string('OTA','OTA_467241_RPT_VENUE_NULL');
153 --Start of DT Fixes
154 -- return(to_char(null));
155 return cp_venue;
156 --End of DT Fixes
157 end;
158
159 function CF_course_end_dateFormula return Char is
160 begin
161 select fnd_date.date_to_displaydate(to_date(substr((p_course_end_date),1,10),'yyyy/mm/dd'),calendar_aware)
162 into cp_course_end_date
163 from dual;
164
165 return(to_char(null));
166 end;
167
168 function CF_course_start_dateFormula return Char is
169 begin
170 select fnd_date.date_to_displaydate(to_date(substr((p_course_start_date),1,10),'yyyy/mm/dd'),calendar_aware)
171 into cp_course_start_date
172 from dual;
173
174 return(to_char(null));
175 end;
176
177 function CF_BG_NAMEFormula return Char is
178 begin
179 c_business_group_name := hr_reports.get_business_group(p_business_group_id);
180 return(to_char(null));
181 end;
182
183 function AfterReport return boolean is
184 begin
185 --hr_standard.event('AFTER REPORT');
186 return (TRUE);
187 end;
188
189 --Functions to refer Oracle report placeholders--
190
191 Function CP_venue_p return varchar2 is
192 Begin
193 return CP_venue;
194 END;
195 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
196 Begin
197 return C_BUSINESS_GROUP_NAME;
198 END;
199 Function C_REPORT_SUBTITLE_p return varchar2 is
200 Begin
201 return C_REPORT_SUBTITLE;
202 END;
203 Function C_EVENT_TITLE_p return varchar2 is
204 Begin
205 return C_EVENT_TITLE;
206 END;
207 Function CP_course_start_date_p return varchar2 is
208 Begin
209 return CP_course_start_date;
210 END;
211 Function CP_course_end_date_p return varchar2 is
212 Begin
213 return CP_course_end_date;
214 END;
215 --Bug 14735864
216 /* This function is used to return the meaning of the code 'Y' and 'N'
217 */
218 Function cf_yes_no_meaning(p_code varchar2) return varchar2 is
219 l_meaning varchar2(80);
220 Begin
221 Select meaning
222 into l_meaning
223 from fnd_lookups
224 where lookup_type='YES_NO' and lookup_code= p_code;
225
226 return(l_meaning);
227
228 exception
229 when no_data_found then
230 l_meaning := null;
231 return(l_meaning);
232 End cf_yes_no_meaning;
233
234 END OTA_OTARPATT_XMLP_PKG ;
[Home] [Help]