1 PACKAGE hr_loc_shd AS
2 /* $Header: hrlocrhi.pkh 120.1 2005/07/18 06:20:20 bshukla noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 -- Note longitude and latitude are defined as number(10,7) on database, but we
9 -- need to be able to store hr_api.g_number (an 9 digit integer) for updates.
10 -- Therefore, in the record structure we use number(16,7).
11 --
12 --
13 TYPE g_rec_type IS RECORD
14 (
15 location_id NUMBER(15),
16 entered_by NUMBER(15),
17 location_code VARCHAR2(60),
18 timezone_code VARCHAR2(50),
19 address_line_1 VARCHAR2(240),
20 address_line_2 VARCHAR2(240),
21 address_line_3 VARCHAR2(240),
22 bill_to_site_flag VARCHAR2(30),
23 country VARCHAR2(60),
24 description VARCHAR2(240),
25 designated_receiver_id NUMBER(15),
26 in_organization_flag VARCHAR2(30),
27 inactive_date DATE,
28 inventory_organization_id NUMBER(15),
29 office_site_flag VARCHAR2(30),
30 postal_code VARCHAR2(30),
31 receiving_site_flag VARCHAR2(30),
32 region_1 VARCHAR2(120),
33 region_2 VARCHAR2(120),
34 region_3 VARCHAR2(120),
35 ship_to_location_id NUMBER(15),
36 ship_to_site_flag VARCHAR2(30),
37 derived_locale VARCHAR2(240),
38 style VARCHAR2(30),
39 tax_name VARCHAR2(15),
40 telephone_number_1 VARCHAR2(60),
41 telephone_number_2 VARCHAR2(60),
42 telephone_number_3 VARCHAR2(60),
43 town_or_city VARCHAR2(30),
44 loc_information13 VARCHAR2(150),
45 loc_information14 VARCHAR2(150),
46 loc_information15 VARCHAR2(150),
47 loc_information16 VARCHAR2(150),
48 loc_information17 VARCHAR2(150),
49 loc_information18 VARCHAR2(150),
50 loc_information19 VARCHAR2(150),
51 loc_information20 VARCHAR2(150),
52 attribute_category VARCHAR2(30),
53 attribute1 VARCHAR2(150),
54 attribute2 VARCHAR2(150),
55 attribute3 VARCHAR2(150),
56 attribute4 VARCHAR2(150),
57 attribute5 VARCHAR2(150),
58 attribute6 VARCHAR2(150),
59 attribute7 VARCHAR2(150),
60 attribute8 VARCHAR2(150),
61 attribute9 VARCHAR2(150),
62 attribute10 VARCHAR2(150),
63 attribute11 VARCHAR2(150),
64 attribute12 VARCHAR2(150),
65 attribute13 VARCHAR2(150),
66 attribute14 VARCHAR2(150),
67 attribute15 VARCHAR2(150),
68 attribute16 VARCHAR2(150),
69 attribute17 VARCHAR2(150),
70 attribute18 VARCHAR2(150),
71 attribute19 VARCHAR2(150),
72 attribute20 VARCHAR2(150),
73 global_attribute_category VARCHAR2(150),
74 global_attribute1 VARCHAR2(150),
75 global_attribute2 VARCHAR2(150),
76 global_attribute3 VARCHAR2(150),
77 global_attribute4 VARCHAR2(150),
78 global_attribute5 VARCHAR2(150),
79 global_attribute6 VARCHAR2(150),
80 global_attribute7 VARCHAR2(150),
81 global_attribute8 VARCHAR2(150),
82 global_attribute9 VARCHAR2(150),
83 global_attribute10 VARCHAR2(150),
84 global_attribute11 VARCHAR2(150),
85 global_attribute12 VARCHAR2(150),
86 global_attribute13 VARCHAR2(150),
87 global_attribute14 VARCHAR2(150),
88 global_attribute15 VARCHAR2(150),
89 global_attribute16 VARCHAR2(150),
90 global_attribute17 VARCHAR2(150),
91 global_attribute18 VARCHAR2(150),
92 global_attribute19 VARCHAR2(150),
93 global_attribute20 VARCHAR2(150),
94 legal_address_flag VARCHAR2(30),
95 tp_header_id NUMBER(15),
96 ece_tp_location_code VARCHAR2(35),
97 object_version_number NUMBER(9),
98 business_group_id NUMBER(15),
99 geometry MDSYS.SDO_GEOMETRY
100 );
101 --
102 -- ----------------------------------------------------------------------------
103 -- | Global Definitions - Internal Development Use Only |
104 -- ----------------------------------------------------------------------------
105 --
106
107 g_old_rec g_rec_type; -- Global record definition
108 g_api_dml BOOLEAN; -- Global api dml status --
109
110 -- ----------------------------------------------------------------------------
111 -- |------------------------< return_api_dml_status >-------------------------|
112 -- ----------------------------------------------------------------------------
113 -- {Start Of Comments}
114 --
115 -- Description:
116 -- This function will return the current g_api_dml private global
117 -- boolean status.
118 -- The g_api_dml status determines if at the time of the function
119 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
120 -- is being issued from within an api.
121 -- If the status is TRUE then a dml statement is being issued from
122 -- within this entity api.
123 -- This function is primarily to support database triggers which
124 -- need to maintain the object_version_number for non-supported
125 -- dml statements (i.e. dml statement issued outside of the api layer).
126 --
127 -- Prerequisites:
128 -- None.
129 --
130 -- In Parameters:
131 -- None.
132 --
133 -- Post Success:
134 -- Processing continues.
135 -- If the function returns a TRUE value then, dml is being executed from
136 -- within this api.
137 --
138 -- Post Failure:
139 -- None.
140 --
141 -- Access Status:
142 -- Internal Row Handler Use Only.
143 --
144 -- {End Of Comments}
145 -- ----------------------------------------------------------------------------
146 FUNCTION return_api_dml_status RETURN BOOLEAN;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |---------------------------< constraint_error >---------------------------|
150 -- ----------------------------------------------------------------------------
151 -- {Start Of Comments}
152 --
153 -- Description:
154 -- This procedure is called when a constraint has been violated (i.e.
155 -- The exception hr_api.check_integrity_violated,
156 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
157 -- hr_api.unique_integrity_violated has been raised).
158 -- The exceptions can only be raised as follows:
159 -- 1) A check constraint can only be violated during an INSERT or UPDATE
160 -- dml operation.
161 -- 2) A parent integrity constraint can only be violated during an
162 -- INSERT or UPDATE dml operation.
163 -- 3) A child integrity constraint can only be violated during an
164 -- DELETE dml operation.
165 -- 4) A unique integrity constraint can only be violated during INSERT or
166 -- UPDATE dml operation.
167 --
168 -- Prerequisites:
169 -- 1) Either hr_api.check_integrity_violated,
170 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
171 -- hr_api.unique_integrity_violated has been raised with the subsequent
172 -- stripping of the constraint name from the generated error message
173 -- text.
174 -- 2) Standalone validation test which corresponds with a constraint error.
175 --
176 -- In Parameter:
177 -- p_constraint_name is in upper format and is just the constraint name
178 -- (e.g. not prefixed by brackets, schema owner etc).
179 --
180 -- Post Success:
181 -- Development dependant.
182 --
183 -- Post Failure:
184 -- Developement dependant.
185 --
186 -- Developer Implementation Notes:
187 -- For each constraint being checked the hr system package failure message
188 -- has been generated as a template only. These system error messages should
189 -- be modified as required (i.e. change the system failure message to a user
190 -- friendly defined error message).
191 --
192 -- Access Status:
193 -- Internal Development Use Only.
194 --
195 -- {End Of Comments}
196 -- ----------------------------------------------------------------------------
197 PROCEDURE constraint_error
198 (p_constraint_name IN all_constraints.constraint_name%type);
199 --
200 -- ----------------------------------------------------------------------------
201 -- |-----------------------------< api_updating >-----------------------------|
202 -- ----------------------------------------------------------------------------
203 -- {Start Of Comments}
204 --
205 -- Description:
206 -- This function is used to populate the g_old_rec record with the
207 -- current row from the database for the specified primary key
208 -- provided that the primary key exists and is valid and does not
209 -- already match the current g_old_rec. The function will always return
210 -- a TRUE value if the g_old_rec is populated with the current row.
211 -- A FALSE value will be returned if all of the primary key arguments
212 -- are null.
213 --
214 -- Prerequisites:
215 -- None.
216 --
217 -- In Parameters:
218 --
219 -- Post Success:
220 -- A value of TRUE will be returned indiciating that the g_old_rec
221 -- is current.
222 -- A value of FALSE will be returned if all of the primary key arguments
223 -- have a null value (this indicates that the row has not be inserted into
224 -- the Schema), and therefore could never have a corresponding row.
225 --
226 -- Post Failure:
227 -- A failure can only occur under two circumstances:
228 -- 1) The primary key is invalid (i.e. a row does not exist for the
229 -- specified primary key values).
230 -- 2) If an object_version_number exists but is NOT the same as the current
231 -- g_old_rec value.
232 --
233 -- Developer Implementation Notes:
234 -- None.
235 --
236 -- Access Status:
237 -- Internal Development Use Only.
238 --
239 -- {End Of Comments}
240 -- ----------------------------------------------------------------------------
241 FUNCTION api_updating
242 (
243 p_location_id IN NUMBER,
244 p_object_version_number IN NUMBER
245 ) RETURN BOOLEAN;
246 --
247
248 -- ----------------------------------------------------------------------------
249 -- |---------------------------------< lck >----------------------------------|
250 -- ----------------------------------------------------------------------------
251 -- {Start Of Comments}
252 --
253 -- Description:
254 -- The Lck process has two main functions to perform. Firstly, the row to be
255 -- updated or deleted must be locked. The locking of the row will only be
256 -- successful if the row is not currently locked by another user.
257 -- Secondly, during the locking of the row, the row is selected into
258 -- the g_old_rec data structure which enables the current row values from the
259 -- server to be available to the api.
260 --
261 -- Prerequisites:
262 -- When attempting to call the lock the object version number (if defined)
263 -- is mandatory.
264 --
265 -- In Parameters:
266 -- The arguments to the Lck process are the primary key(s) which uniquely
267 -- identify the row and the object version number of row.
268 --
269 -- Post Success:
270 -- On successful completion of the Lck process the row to be updated or
271 -- deleted will be locked and selected into the global data structure
272 -- g_old_rec.
273 --
274 -- Post Failure:
275 -- The Lck process can fail for three reasons:
276 -- 1) When attempting to lock the row the row could already be locked by
277 -- another user. This will raise the HR_Api.Object_Locked exception.
278 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
279 -- This error is trapped and reported using the message name
280 -- 'HR_7220_INVALID_PRIMARY_KEY'.
281 -- 3) The row although existing in the HR Schema has a different object
282 -- version number than the object version number specified.
283 -- This error is trapped and reported using the message name
284 -- 'HR_7155_OBJECT_INVALID'.
285 --
286 -- Developer Implementation Notes:
287 -- For each primary key and the object version number arguments add a
288 -- call to hr_api.mandatory_arg_error procedure to ensure that these
289 -- argument values are not null.
290 --
291 -- Access Status:
292 -- Internal Development Use Only.
293 --
294 -- {End Of Comments}
295 -- ----------------------------------------------------------------------------
296 PROCEDURE lck
297 (
298 p_location_id IN NUMBER,
299 p_object_version_number IN NUMBER
300 );
301 --
302 -- ----------------------------------------------------------------------------
303 -- |-----------------------------< convert_args >-----------------------------|
304 -- ----------------------------------------------------------------------------
305 -- {Start Of Comments}
306 --
307 -- Description:
308 -- This function is used to turn attribute parameters into the record
309 -- structure parameter g_rec_type.
310 --
311 -- Prerequisites:
312 -- This is a private function and can only be called from the ins or upd
313 -- attribute processes.
314 --
315 -- In Parameters:
316 --
317 -- Post Success:
318 -- A returning record structure will be returned.
319 --
320 -- Post Failure:
321 -- No direct error handling is required within this function. Any possible
322 -- errors within this function will be a PL/SQL value error due to conversion
323 -- of datatypes or data lengths.
324 --
325 -- Developer Implementation Notes:
326 -- None.
327 --
328 -- Access Status:
329 -- Internal Row Handler Use Only.
330 --
331 -- {End Of Comments}
332 -- ----------------------------------------------------------------------------
333 FUNCTION convert_args
334 (
335 p_location_id IN NUMBER,
336 p_location_code IN VARCHAR2,
337 p_timezone_code IN VARCHAR2,
338 p_address_line_1 IN VARCHAR2,
339 p_address_line_2 IN VARCHAR2,
340 p_address_line_3 IN VARCHAR2,
341 p_bill_to_site_flag IN VARCHAR2,
342 p_country IN VARCHAR2,
343 p_description IN VARCHAR2,
344 p_designated_receiver_id IN NUMBER,
345 p_in_organization_flag IN VARCHAR2,
346 p_inactive_date IN DATE,
347 p_inventory_organization_id IN NUMBER,
348 p_office_site_flag IN VARCHAR2,
349 p_postal_code IN VARCHAR2,
350 p_receiving_site_flag IN VARCHAR2,
351 p_region_1 IN VARCHAR2,
352 p_region_2 IN VARCHAR2,
353 p_region_3 IN VARCHAR2,
354 p_ship_to_location_id IN NUMBER,
358 p_telephone_number_1 IN VARCHAR2,
355 p_ship_to_site_flag IN VARCHAR2,
356 p_style IN VARCHAR2,
357 p_tax_name IN VARCHAR2,
359 p_telephone_number_2 IN VARCHAR2,
360 p_telephone_number_3 IN VARCHAR2,
361 p_town_or_city IN VARCHAR2,
362 p_loc_information13 IN VARCHAR2,
363 p_loc_information14 IN VARCHAR2,
364 p_loc_information15 IN VARCHAR2,
365 p_loc_information16 IN VARCHAR2,
366 p_loc_information17 IN VARCHAR2,
367 p_loc_information18 IN VARCHAR2,
368 p_loc_information19 IN VARCHAR2,
369 p_loc_information20 IN VARCHAR2,
370 p_attribute_category IN VARCHAR2,
371 p_attribute1 IN VARCHAR2,
372 p_attribute2 IN VARCHAR2,
373 p_attribute3 IN VARCHAR2,
374 p_attribute4 IN VARCHAR2,
375 p_attribute5 IN VARCHAR2,
376 p_attribute6 IN VARCHAR2,
377 p_attribute7 IN VARCHAR2,
378 p_attribute8 IN VARCHAR2,
379 p_attribute9 IN VARCHAR2,
380 p_attribute10 IN VARCHAR2,
381 p_attribute11 IN VARCHAR2,
382 p_attribute12 IN VARCHAR2,
383 p_attribute13 IN VARCHAR2,
384 p_attribute14 IN VARCHAR2,
385 p_attribute15 IN VARCHAR2,
386 p_attribute16 IN VARCHAR2,
387 p_attribute17 IN VARCHAR2,
388 p_attribute18 IN VARCHAR2,
389 p_attribute19 IN VARCHAR2,
390 p_attribute20 IN VARCHAR2,
391 p_global_attribute_category IN VARCHAR2,
392 p_global_attribute1 IN VARCHAR2,
393 p_global_attribute2 IN VARCHAR2,
394 p_global_attribute3 IN VARCHAR2,
395 p_global_attribute4 IN VARCHAR2,
396 p_global_attribute5 IN VARCHAR2,
397 p_global_attribute6 IN VARCHAR2,
398 p_global_attribute7 IN VARCHAR2,
399 p_global_attribute8 IN VARCHAR2,
400 p_global_attribute9 IN VARCHAR2,
401 p_global_attribute10 IN VARCHAR2,
402 p_global_attribute11 IN VARCHAR2,
403 p_global_attribute12 IN VARCHAR2,
404 p_global_attribute13 IN VARCHAR2,
405 p_global_attribute14 IN VARCHAR2,
406 p_global_attribute15 IN VARCHAR2,
407 p_global_attribute16 IN VARCHAR2,
408 p_global_attribute17 IN VARCHAR2,
409 p_global_attribute18 IN VARCHAR2,
410 p_global_attribute19 IN VARCHAR2,
411 p_global_attribute20 IN VARCHAR2,
412 p_legal_address_flag IN VARCHAR2,
413 p_tp_header_id IN NUMBER,
414 p_ece_tp_location_code IN VARCHAR2,
415 p_object_version_number IN NUMBER,
416 p_business_group_id IN NUMBER
417 )
418 RETURN g_rec_type;
419 --
420 --
421 -- ---------------------------------------------------------------------------
422 -- |--------------------------< derive_locale >------------------------------|
423 -- ---------------------------------------------------------------------------
424 --
425 -- Description:
426 -- Populated the 'derived_locale' element of p_rec record structure. This
427 -- is acheived the use of legislative specific functions contained within
428 -- localization utility packages. If the localization package doesn't
429 -- exist, or does not contain the required function, a default value is
430 -- entered into the structure.
431 --
432 -- In Arguments:
433 -- p_rec
434 --
435 -- Access Status:
436 -- Internal Development Use Only.
437 --
438 procedure derive_locale(p_rec in out nocopy hr_loc_shd.g_rec_type);
439
440 END hr_loc_shd;