1 package OTA_GENERAL AUTHID CURRENT_USER as
2 /* $Header: otgenral.pkh 120.3 2005/07/20 01:02:44 dbatra noship $ */
3 /*
4 ===========================================================================
5 | Copyright (c) 1996 Oracle Corporation |
6 | All rights reserved. |
7 ===========================================================================
8 Name
9 General Oracle Training utilities
10 Purpose
11 To provide widely used functions in a single shared area
12 History
13 9 Nov 94 M Roychowdhury Created
14 10 Nov 94 M Roychowdhury Added check for dates
15 11 Nov 94 M Roychowdhury Added check for person
16 Added value changed function
17 17 Nov 94 M Roychowdhury Added check for current employee
18 31 Aug 94 J Rhodes Added check_fnd_user
19 31 Aug 94 J Rhodes Added get_fnd_user
20 16 Feb 96 G Perry Added get_session_date
21 10.14 29 Mar 96 S Shah Added check_par_child_dates_fun
22 110.1 03 Sep 97 K habibul Fixed problem with 255 chrs width
23 110.2 03-Dec-98 C Tredwin Added char_to_number
24 115.2 11-OCT-99 R Raina Added function fnd_lang_desc
25 115.3 12-OCT-99 R Raina Added function fnd_currency_name,
26 fnd_lang_code,
27 hr_org_name
28 115.4 11-MAY-01 D HMulia Added function get_training_center ,
29 function get_location
30 115.5 10-JUL-01 D Hulia Added Function get_finance.
31 115.8 29-Nov-02 Jbharath Bug#2684733 NOCOPY added for IN OUT/OUT arguments of procedure
32 115.9 03-Nov-2004 sgokhale Bug 3953333 Validation for checking vendor validity wrt start date
33 rem 115.10 07-Apr-05 dbatra Added get_event_name and get_course_name
34 rem 115.11 30-Jun-05 dbatra 4465618 Modified get_course_name signature
35 rem 115.12 18-Jul-05 rdola 4490656 Added get_legislation_code method
36 rem 115.13 20-Jul-2005 dbatra 4496361 Added get_offering_name
37 */
38 --------------------------------------------------------------------------------
39 --
40 function get_event_name (p_event_id in number)
41 return varchar2;
42
43 function get_offering_name (p_offering_id in number)
44 return varchar2;
45
46 function get_course_name (p_activity_version_id in number default null,p_eventid in number default null)
47 return varchar2;
48
49 -- ----------------------------------------------------------------------------
50 -- |------------------------< check_current_employee >------------------------|
51 -- ----------------------------------------------------------------------------
52 --
53 -- PUBLIC
54 -- Description: Check Current Employee
55 --
56 -- Checks if the given person is a current employee on a given
57 -- date
58 --
59 Function check_current_employee (p_person_id in number,
60 p_date in date)
61 Return boolean;
62 --
63 -- ----------------------------------------------------------------------------
64 -- |------------------------< check_domain_value >----------------------------|
65 -- ----------------------------------------------------------------------------
66 --
67 -- PUBLIC
68 --
69 -- Description : Used to check if a value is in the specified domain
70 --
71 Procedure check_domain_value
72 (
73 p_domain_type in varchar2
74 ,p_domain_value in varchar2
75 );
76 -- ----------------------------------------------------------------------------
77 -- |------------------------< get_session_date >------------------------------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- PUBLIC
81 --
82 -- Description : Get session date from dual
83 --
84 function get_session_date(p_session_id number) return date;
85 --
86 -- ----------------------------------------------------------------------------
87 -- |---------------------------< check_start_end_date >-----------------------|
88 -- ----------------------------------------------------------------------------
89 --
90 -- PUBLIC
91 --
92 -- Description:
93 -- Validates the startdate and enddate.
94 -- p_start_date must be less than, or equal to, p_end_date.
95 --
96 Procedure check_start_end_dates
97 (
98 p_start_date in date
99 ,p_end_date in date
100 );
101 --
102 -- ----------------------------------------------------------------------------
103 -- |-----------------------< check_par_child_dates >--------------------------|
104 -- ----------------------------------------------------------------------------
105 --
106 -- PUPLIC
107 --
108 -- Description:
109 -- Validate the parent startdate, enddate and the child startdate, enddate.
110 -- The child start- and enddate have to be whitin the parent start-, enddate.
111 --
112 Procedure check_par_child_dates
113 (
114 p_par_start in date
115 ,p_par_end in date
116 ,p_child_start in date
117 ,p_child_end in date
118 );
119 --
120 -- ----------------------------------------------------------------------------
121 -- |-----------------------< check_par_child_dates_fun >----------------------|
122 -- ----------------------------------------------------------------------------
123 --
124 -- PUPLIC
125 --
126 -- Description:
127 -- Validate the parent startdate, enddate and the child startdate, enddate.
128 -- The child start- and enddate have to be whitin the parent start-, enddate.
129 --
130 Function check_par_child_dates_fun
131 (
132 p_par_start in date
133 ,p_par_end in date
134 ,p_child_start in date
135 ,p_child_end in date
136 ) Return Boolean;
137 --
138 -- ----------------------------------------------------------------------------
139 -- |---------------------------< check_start_end_time >-----------------------|
140 -- ----------------------------------------------------------------------------
141 --
142 -- PUBLIC
143 --
144 -- Description:
145 -- Validates the starttime and endtime.
146 -- start_time must be less than, or equal to, end_time.
147 --
148 Procedure check_start_end_time
149 (
150 p_start_time in varchar2
151 ,p_end_time in varchar2
152 );
153 --
154 -- ----------------------------------------------------------------------------
155 -- |----------------------------< get_fnd_user >----------------------------|
156 -- ----------------------------------------------------------------------------
157 --
158 -- PUBLIC
159 -- Description: Get FND USER
160 --
161 --
162 function get_fnd_user(p_user_id in number)
163 return varchar2;
164 --
165 --
166 -- ----------------------------------------------------------------------------
167 -- |----------------------------< check_fnd_user >----------------------------|
168 -- ----------------------------------------------------------------------------
169 --
170 -- PUBLIC
171 -- Description: Check Person
172 --
173 -- Checks that a given person is active on a given date
174 --
175 Function check_fnd_user(p_user_id in number) return boolean;
176 --
177 --
178 -- ----------------------------------------------------------------------------
179 -- |----------------------------< check_person >------------------------------|
180 -- ----------------------------------------------------------------------------
181 --
182 -- PUBLIC
183 -- Description: Check Person
184 --
185 -- Checks that a given person is active on a given date
186 --
187 Function check_person (p_person_id in number,
188 p_date in date) return boolean;
189 --
190 --
191 -- ----------------------------------------------------------------------------
192 -- |-----------------------------< value_changed >----------------------------|
193 -- ----------------------------------------------------------------------------
194 --
195 -- PUBLIC
196 -- Description: Value Changed (overloaded function)
197 --
198 -- Checks if two values are different
199 --
200 Function value_changed (p_old_value in varchar2,
201 p_new_value in varchar2) return boolean;
202 --
203 Function value_changed (p_old_value in number,
204 p_new_value in number) return boolean;
205 --
206 Function value_changed (p_old_value in date,
207 p_new_value in date) return boolean;
208 --
209 -- ----------------------------------------------------------------------------
210 -- |----------------------------< char_to_number >----------------------------|
211 -- ----------------------------------------------------------------------------
212 --
213 -- PUBLIC
214 -- Description: Converts character to number. Executed on the server
215 -- because clients are unable to read non-US values for
216 -- NLS_NUMERIC_CHARACTERS.
217 --
218 function char_to_number (p_input in varchar2) return number;
219 --
220 --
221 --------------------------------------------------------------------------------
222 function valid_vendor (p_vendor_id number,p_date date default null) return boolean;
223 function valid_vendor ( p_vendor_name varchar2) return boolean;
224 procedure check_vendor_is_valid (p_vendor_id number,p_date date default null);
225 --------------------------------------------------------------------------------
226 function valid_currency (p_currency_code varchar2) return boolean;
227 procedure check_currency_is_valid (p_currency_code varchar2);
228 --------------------------------------------------------------------------------
229 function valid_language (p_language_id number) return boolean;
230 procedure check_language_is_valid (p_language_id number);
231 --------------------------------------------------------------------------------
232 function vendor_name ( p_vendor_id number) return varchar2;
233 -- The following pragma allows this function to be used in SQL views
234 pragma restrict_references (vendor_name, WNDS, WNPS);
235 --------------------------------------------------------------------------------
236 function fnd_lang_desc (
237 p_language_id number
238 ) return varchar2;
239 pragma restrict_references (fnd_lang_desc, WNPS,WNDS);
240
241 -------------------------------------------------------------------------------
242 function fnd_currency_name (
243 p_currency_code varchar2
244 ) return varchar2;
245 pragma restrict_references (fnd_lang_desc, WNPS,WNDS);
246
247
248 --------------------------------------------------------------------------------
249 function fnd_lang_code (
250 p_language_id number
251 ) return varchar2;
252 pragma restrict_references (fnd_lang_code, WNPS,WNDS);
253
254 --------------------------------------------------------------------------------
255 function hr_org_name (
256 p_organization_id number
257 ) return varchar2;
258 pragma restrict_references (hr_org_name, WNPS,WNDS);
259
260 ---------------------------------------------------------------------------------
261 procedure get_defaults(p_business_group_id in number
262 ,p_default_activity_version out nocopy varchar2);
263 procedure get_defaults(p_default_source_of_booking out nocopy varchar2
264 ,p_default_enrolment_status out nocopy varchar2
265 ,p_overbooking_rule out nocopy varchar2);
266 procedure get_defaults(p_autogen_scheduled_event out nocopy varchar2
267 ,p_update_scheduled_event out nocopy varchar2);
268 procedure get_defaults(p_autogen_development_event out nocopy varchar2
269 ,p_update_development_event out nocopy varchar2);
270 --------------------------------------------------------------------------------
271
272 function get_business_group_id return number ;
273 --
274 -- If the user has signed on through applications then returns the value
275 -- of the Business Group profile option otherwise returns null.
276 --
277 -- Note the check that the user has signed on is so that views which
278 -- would normally restrict on business group can be made to retrieve
279 -- all rows when running in sql*plus or other environments. By default
280 -- the Business Group Profile option is defined at site level to be
281 -- the Setup Business group.
282 --
283
284 function get_training_center (p_training_center_id in number)
285 return varchar2 ;
286
287
288 function get_location (p_location_id in number)
289 return varchar2 ;
290
291 FUNCTION get_finance (p_booking_id in number)
292 return number ;
293
294 -- ----------------------------------------------------------------------------
295 -- |-------------------------< get_Location_code >----------------------------|
296 -- ----------------------------------------------------------------------------
297 --
298 -- Description: get the location code for the id passed in as a parameter.
299 --
300 --
301 FUNCTION get_Location_code(p_location_id IN NUMBER) RETURN VARCHAR2;
302 -- ----------------------------------------------------------------------------
303 -- |-------------------------< get_org_name >----------------------------|
304 -- ----------------------------------------------------------------------------
305 --
306 -- Description: get the name for the organization id passed in as a parameter.
307 --
308 --
309
310 FUNCTION get_org_name(p_organization_id IN NUMBER) RETURN VARCHAR2;
311
312 --
313 --------------------------------------------------------------------------------
314 -- ----------------------------------------------------------------------------
315 -- |-------------------------< get_legislation_code >----------------------------|
316 -- ----------------------------------------------------------------------------
317 --
318 -- Description: get the legislation code of the business group
319 --
320 --
321 FUNCTION get_legislation_code RETURN VARCHAR2;
322
323 end OTA_GENERAL;