1 PACKAGE BODY OTA_OTARPATT_XMLP_PKG AS
2 /* $Header: OTARPATTB.pls 120.1 2007/12/07 05:59:16 amakrish noship $ */
3 function BeforeReport return boolean is
4 begin
5 --hr_standard.event('BEFORE REPORT');
6 LP_SESSION_DATE:= to_char(P_SESSION_DATE,'DD-MON-YY');
7 return (TRUE);
8 end;
9
10 function CF_OPTIONAL_COLUMNFormula return Char is
11 begin
12
13 IF P_OPTIONAL_COLUMN = NULL THEN
14 return ('');
15 ELSE
16 return (P_OPTIONAL_COLUMN);
17 END IF;
18
19 end;
20
21 FUNCTION AfterPForm
22 RETURN BOOLEAN
23 IS
24 Cursor c_event(p_event_id number) is
25 Select title
26 from ota_events_tl
27 where event_id = p_event_id
28 and language = userenv('LANG') ;
29
30 Cursor c_training_center(center_id number) is
31 Select name
32 from hr_all_organization_units org
33 where ORG.ORGANIZATION_ID = center_id;
34
35 BEGIN
36
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,'This report cannot be run without at least one parameter entered.');/*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,'The course start start cannot be later than the course end date.');/*SRW.program_abort;*/null;
55
56 RETURN(FALSE);
57
58 END IF;
59
60 p_and := TO_CHAR(NULL);
61 p_trainer_and := TO_CHAR(NULL);
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 --Added during DT Fixes
103 if p_and is null then
104 p_and := ' ';
105 end if;
106
107 if P_trainer_and is null then
108 P_trainer_and := ' ';
109 end if;
110 --End of DT Fixes
111
112
113 RETURN(TRUE);
114
115 END;
116
117 function cf_venueformula
118 (event_id in number) return char
119 is
120 begin
121
122 select replace(name,fnd_global.local_chr(10),fnd_global.local_chr(46))
123 into cp_venue
124 from ota_suppliable_resources_tl sr,
125 ota_resource_bookings rb
126 where rb.event_id = cf_venueformula.event_id
127 and rb.primary_venue_flag = 'Y'
128 and sr.supplied_resource_id = rb.supplied_resource_id
129 and sr.language = userenv('LANG') ;
130 --Start of DT Fixes
131 -- return(to_char(null));
132 return cp_venue;
133 --End of DT Fixes
134 exception
135 when no_data_found then
136 cp_venue := 'None listed';
137 --Start of DT Fixes
138 -- return(to_char(null));
139 return cp_venue;
140 --End of DT Fixes
141 end;
142
143 function CF_course_end_dateFormula return Char is
144 begin
145 select fnd_date.date_to_displaydate(to_date(substr((p_course_end_date),1,10),'yyyy/mm/dd'))
146 into cp_course_end_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_BG_NAMEFormula return Char is
162 begin
163 c_business_group_name := hr_reports.get_business_group(p_business_group_id);
164 return(to_char(null));
165 end;
166
167 function AfterReport return boolean is
168 begin
169 --hr_standard.event('AFTER REPORT');
170 return (TRUE);
171 end;
172
173 --Functions to refer Oracle report placeholders--
174
175 Function CP_venue_p return varchar2 is
176 Begin
177 return CP_venue;
178 END;
179 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
180 Begin
181 return C_BUSINESS_GROUP_NAME;
182 END;
183 Function C_REPORT_SUBTITLE_p return varchar2 is
184 Begin
185 return C_REPORT_SUBTITLE;
186 END;
187 Function C_EVENT_TITLE_p return varchar2 is
188 Begin
189 return C_EVENT_TITLE;
190 END;
191 Function CP_course_start_date_p return varchar2 is
192 Begin
193 return CP_course_start_date;
194 END;
195 Function CP_course_end_date_p return varchar2 is
196 Begin
197 return CP_course_end_date;
198 END;
199 END OTA_OTARPATT_XMLP_PKG ;
[Home] [Help]