1 Package ben_cpi_shd as
2 /* $Header: becpirhi.pkh 120.0 2005/05/28 01:13:59 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (group_per_in_ler_id number(15)
10 ,assignment_id number(15)
11 ,person_id number(15)
12 ,supervisor_id number(15)
13 ,effective_date date
14 ,full_name varchar2(240)
15 ,brief_name varchar2(360)
16 ,custom_name varchar2(480)
17 ,supervisor_full_name varchar2(240)
18 ,supervisor_brief_name varchar2(360)
19 ,supervisor_custom_name varchar2(480)
20 ,legislation_code varchar2(30)
21 ,years_employed number
22 ,years_in_job number
23 ,years_in_position number
24 ,years_in_grade number
25 ,employee_number varchar2(30)
26 ,start_date date
27 ,original_start_date date
28 ,adjusted_svc_date date
29 ,base_salary number
30 ,base_salary_change_date date
31 ,payroll_name varchar2(80)
32 ,performance_rating varchar2(30)
33 ,performance_rating_type varchar2(30)
34 ,performance_rating_date date
35 ,business_group_id number(15)
36 ,organization_id number(15)
37 ,job_id number(15)
38 ,grade_id number(15)
39 ,position_id number(15)
40 ,people_group_id number(15)
41 ,soft_coding_keyflex_id number(15)
42 ,location_id number(15)
43 ,pay_rate_id number(15)
44 ,assignment_status_type_id number(15)
45 ,frequency varchar2(30)
46 ,grade_annulization_factor number
47 ,pay_annulization_factor number
48 ,grd_min_val number
49 ,grd_max_val number
50 ,grd_mid_point number
51 ,grd_quartile varchar2(30)
52 ,grd_comparatio number
53 ,emp_category varchar2(30)
54 ,change_reason varchar2(30)
55 ,normal_hours number
56 ,email_address varchar2(240)
57 ,base_salary_frequency varchar2(30)
58 ,new_assgn_ovn number(9)
59 ,new_perf_event_id number(15)
60 ,new_perf_review_id number(15)
61 ,post_process_stat_cd varchar2(30)
62 ,feedback_rating varchar2(30)
63 ,feedback_comments varchar2(2000)
64 ,object_version_number number(9)
65 ,custom_segment1 varchar2(150)
66 ,custom_segment2 varchar2(150)
67 ,custom_segment3 varchar2(150)
68 ,custom_segment4 varchar2(150)
69 ,custom_segment5 varchar2(150)
70 ,custom_segment6 varchar2(150)
71 ,custom_segment7 varchar2(150)
72 ,custom_segment8 varchar2(150)
73 ,custom_segment9 varchar2(150)
74 ,custom_segment10 varchar2(150)
75 ,custom_segment11 number
76 ,custom_segment12 number
77 ,custom_segment13 number
78 ,custom_segment14 number
79 ,custom_segment15 number
80 ,custom_segment16 number
81 ,custom_segment17 number
82 ,custom_segment18 number
83 ,custom_segment19 number
84 ,custom_segment20 number
85 ,people_group_name varchar2(240)
86 ,people_group_segment1 varchar2(60)
87 ,people_group_segment2 varchar2(60)
88 ,people_group_segment3 varchar2(60)
89 ,people_group_segment4 varchar2(60)
90 ,people_group_segment5 varchar2(60)
91 ,people_group_segment6 varchar2(60)
92 ,people_group_segment7 varchar2(60)
93 ,people_group_segment8 varchar2(60)
94 ,people_group_segment9 varchar2(60)
95 ,people_group_segment10 varchar2(60)
96 ,people_group_segment11 varchar2(60)
97 ,ass_attribute_category varchar2(30)
98 ,ass_attribute1 varchar2(150)
99 ,ass_attribute2 varchar2(150)
100 ,ass_attribute3 varchar2(150)
101 ,ass_attribute4 varchar2(150)
102 ,ass_attribute5 varchar2(150)
103 ,ass_attribute6 varchar2(150)
104 ,ass_attribute7 varchar2(150)
105 ,ass_attribute8 varchar2(150)
106 ,ass_attribute9 varchar2(150)
107 ,ass_attribute10 varchar2(150)
108 ,ass_attribute11 varchar2(150)
109 ,ass_attribute12 varchar2(150)
110 ,ass_attribute13 varchar2(150)
111 ,ass_attribute14 varchar2(150)
112 ,ass_attribute15 varchar2(150)
113 ,ass_attribute16 varchar2(150)
114 ,ass_attribute17 varchar2(150)
115 ,ass_attribute18 varchar2(150)
116 ,ass_attribute19 varchar2(150)
117 ,ass_attribute20 varchar2(150)
118 ,ass_attribute21 varchar2(150)
119 ,ass_attribute22 varchar2(150)
120 ,ass_attribute23 varchar2(150)
121 ,ass_attribute24 varchar2(150)
122 ,ass_attribute25 varchar2(150)
123 ,ass_attribute26 varchar2(150)
124 ,ass_attribute27 varchar2(150)
125 ,ass_attribute28 varchar2(150)
126 ,ass_attribute29 varchar2(150)
127 ,ass_attribute30 varchar2(150)
128 ,ws_comments varchar2(2000)
129 ,cpi_attribute_category varchar2(30)
130 ,cpi_attribute1 varchar2(150)
131 ,cpi_attribute2 varchar2(150)
132 ,cpi_attribute3 varchar2(150)
133 ,cpi_attribute4 varchar2(150)
134 ,cpi_attribute5 varchar2(150)
135 ,cpi_attribute6 varchar2(150)
136 ,cpi_attribute7 varchar2(150)
137 ,cpi_attribute8 varchar2(150)
138 ,cpi_attribute9 varchar2(150)
139 ,cpi_attribute10 varchar2(150)
140 ,cpi_attribute11 varchar2(150)
141 ,cpi_attribute12 varchar2(150)
142 ,cpi_attribute13 varchar2(150)
143 ,cpi_attribute14 varchar2(150)
144 ,cpi_attribute15 varchar2(150)
145 ,cpi_attribute16 varchar2(150)
146 ,cpi_attribute17 varchar2(150)
147 ,cpi_attribute18 varchar2(150)
148 ,cpi_attribute19 varchar2(150)
149 ,cpi_attribute20 varchar2(150)
150 ,cpi_attribute21 varchar2(150)
151 ,cpi_attribute22 varchar2(150)
152 ,cpi_attribute23 varchar2(150)
153 ,cpi_attribute24 varchar2(150)
154 ,cpi_attribute25 varchar2(150)
155 ,cpi_attribute26 varchar2(150)
156 ,cpi_attribute27 varchar2(150)
157 ,cpi_attribute28 varchar2(150)
158 ,cpi_attribute29 varchar2(150)
159 ,cpi_attribute30 varchar2(150)
160 ,feedback_date date
161 );
162 --
163 -- ----------------------------------------------------------------------------
164 -- | Global Definitions - Internal Development Use Only |
165 -- ----------------------------------------------------------------------------
166 --
167 g_old_rec g_rec_type; -- Global record definition
168 -- Global table name
169 g_tab_nam constant varchar2(30) := 'BEN_CWB_PERSON_INFO';
170 g_api_dml boolean; -- Global api dml status
171 --
172 -- ----------------------------------------------------------------------------
173 -- |------------------------< return_api_dml_status >-------------------------|
174 -- ----------------------------------------------------------------------------
175 -- {Start Of Comments}
176 --
177 -- Description:
178 -- This function will return the current g_api_dml private global
179 -- boolean status.
180 -- The g_api_dml status determines if at the time of the function
181 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
182 -- is being issued from within an api.
183 -- If the status is TRUE then a dml statement is being issued from
184 -- within this entity api.
185 -- This function is primarily to support database triggers which
186 -- need to maintain the object_version_number for non-supported
187 -- dml statements (i.e. dml statement issued outside of the api layer).
188 --
189 -- Prerequisites:
190 -- None.
191 --
192 -- In Parameters:
193 -- None.
194 --
195 -- Post Success:
196 -- Processing continues.
197 -- If the function returns a TRUE value then, dml is being executed from
198 -- within this api.
199 --
200 -- Post Failure:
201 -- None.
202 --
203 -- Access Status:
204 -- Internal Row Handler Use Only.
205 --
206 -- {End Of Comments}
207 -- ----------------------------------------------------------------------------
208 Function return_api_dml_status Return Boolean;
209 --
210 -- ----------------------------------------------------------------------------
211 -- |---------------------------< constraint_error >---------------------------|
212 -- ----------------------------------------------------------------------------
213 -- {Start Of Comments}
214 --
215 -- Description:
216 -- This procedure is called when a constraint has been violated (i.e.
217 -- The exception hr_api.check_integrity_violated,
218 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
219 -- hr_api.unique_integrity_violated has been raised).
220 -- The exceptions can only be raised as follows:
221 -- 1) A check constraint can only be violated during an INSERT or UPDATE
222 -- dml operation.
223 -- 2) A parent integrity constraint can only be violated during an
224 -- INSERT or UPDATE dml operation.
225 -- 3) A child integrity constraint can only be violated during an
226 -- DELETE dml operation.
227 -- 4) A unique integrity constraint can only be violated during INSERT or
228 -- UPDATE dml operation.
229 --
230 -- Prerequisites:
231 -- 1) Either hr_api.check_integrity_violated,
232 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
233 -- hr_api.unique_integrity_violated has been raised with the subsequent
234 -- stripping of the constraint name from the generated error message
235 -- text.
236 -- 2) Standalone validation test which corresponds with a constraint error.
237 --
238 -- In Parameter:
239 -- p_constraint_name is in upper format and is just the constraint name
240 -- (e.g. not prefixed by brackets, schema owner etc).
241 --
242 -- Post Success:
243 -- Development dependant.
244 --
245 -- Post Failure:
246 -- Developement dependant.
247 --
248 -- Developer Implementation Notes:
249 -- For each constraint being checked the hr system package failure message
250 -- has been generated as a template only. These system error messages should
251 -- be modified as required (i.e. change the system failure message to a user
252 -- friendly defined error message).
253 --
254 -- Access Status:
255 -- Internal Development Use Only.
256 --
257 -- {End Of Comments}
258 -- ----------------------------------------------------------------------------
259 Procedure constraint_error
260 (p_constraint_name in all_constraints.constraint_name%TYPE);
261 --
267 -- Description:
262 -- ----------------------------------------------------------------------------
263 -- |-----------------------------< api_updating >-----------------------------|
264 -- ----------------------------------------------------------------------------
265 -- {Start Of Comments}
266 --
268 -- This function is used to populate the g_old_rec record with the
269 -- current row from the database for the specified primary key
270 -- provided that the primary key exists and is valid and does not
271 -- already match the current g_old_rec. The function will always return
272 -- a TRUE value if the g_old_rec is populated with the current row.
273 -- A FALSE value will be returned if all of the primary key arguments
274 -- are null.
275 --
276 -- Prerequisites:
277 -- None.
278 --
279 -- In Parameters:
280 --
281 -- Post Success:
282 -- A value of TRUE will be returned indiciating that the g_old_rec
283 -- is current.
284 -- A value of FALSE will be returned if all of the primary key arguments
285 -- have a null value (this indicates that the row has not be inserted into
286 -- the Schema), and therefore could never have a corresponding row.
287 --
288 -- Post Failure:
289 -- A failure can only occur under two circumstances:
290 -- 1) The primary key is invalid (i.e. a row does not exist for the
291 -- specified primary key values).
292 -- 2) If an object_version_number exists but is NOT the same as the current
293 -- g_old_rec value.
294 --
295 -- Developer Implementation Notes:
296 -- None.
297 --
298 -- Access Status:
299 -- Internal Development Use Only.
300 --
301 -- {End Of Comments}
302 -- ----------------------------------------------------------------------------
303 Function api_updating
304 (p_group_per_in_ler_id in number
305 ,p_object_version_number in number
306 ) Return Boolean;
307 --
308 -- ----------------------------------------------------------------------------
309 -- |---------------------------------< lck >----------------------------------|
310 -- ----------------------------------------------------------------------------
311 -- {Start of comments}
312 --
313 -- Description:
314 -- The Lck process has two main functions to perform. Firstly, the row to be
315 -- updated or deleted must be locked. The locking of the row will only be
316 -- successful if the row is not currently locked by another user.
317 -- Secondly, during the locking of the row, the row is selected into
318 -- the g_old_rec data structure which enables the current row values from
319 -- the server to be available to the api.
320 --
321 -- Prerequisites:
322 -- When attempting to call the lock the object version number (if defined)
323 -- is mandatory.
324 --
325 -- In Parameters:
326 -- The arguments to the Lck process are the primary key(s) which uniquely
327 -- identify the row and the object version number of row.
328 --
329 -- Post Success:
330 -- On successful completion of the Lck process the row to be updated or
331 -- deleted will be locked and selected into the global data structure
332 -- g_old_rec.
333 --
334 -- Post Failure:
335 -- The Lck process can fail for three reasons:
336 -- 1) When attempting to lock the row the row could already be locked by
337 -- another user. This will raise the HR_Api.Object_Locked exception.
338 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
339 -- This error is trapped and reported using the message name
340 -- 'HR_7220_INVALID_PRIMARY_KEY'.
341 -- 3) The row although existing in the HR Schema has a different object
342 -- version number than the object version number specified.
343 -- This error is trapped and reported using the message name
344 -- 'HR_7155_OBJECT_INVALID'.
345 --
346 -- Developer Implementation Notes:
347 -- For each primary key and the object version number arguments add a
348 -- call to hr_api.mandatory_arg_error procedure to ensure that these
349 -- argument values are not null.
350 --
351 -- Access Status:
352 -- Internal Development Use Only.
353 --
354 -- {End of comments}
355 -- ----------------------------------------------------------------------------
356 Procedure lck
357 (p_group_per_in_ler_id in number
358 ,p_object_version_number in number
359 );
360 --
361 -- ----------------------------------------------------------------------------
362 -- |-----------------------------< convert_args >-----------------------------|
363 -- ----------------------------------------------------------------------------
364 -- {Start Of Comments}
365 --
366 -- Description:
367 -- This function is used to turn attribute parameters into the record
368 -- structure parameter g_rec_type.
369 --
370 -- Prerequisites:
371 -- This is a private function and can only be called from the ins or upd
372 -- attribute processes.
373 --
374 -- In Parameters:
375 --
376 -- Post Success:
377 -- A returning record structure will be returned.
378 --
379 -- Post Failure:
380 -- No direct error handling is required within this function. Any possible
381 -- errors within this function will be a PL/SQL value error due to
382 -- conversion of datatypes or data lengths.
383 --
384 -- Developer Implementation Notes:
385 -- None.
386 --
387 -- Access Status:
388 -- Internal Row Handler Use Only.
389 --
390 -- {End Of Comments}
391 -- ----------------------------------------------------------------------------
392 Function convert_args
393 (p_group_per_in_ler_id in number
394 ,p_assignment_id in number
395 ,p_person_id in number
396 ,p_supervisor_id in number
397 ,p_effective_date in date
398 ,p_full_name in varchar2
399 ,p_brief_name in varchar2
400 ,p_custom_name in varchar2
401 ,p_supervisor_full_name in varchar2
402 ,p_supervisor_brief_name in varchar2
403 ,p_supervisor_custom_name in varchar2
404 ,p_legislation_code in varchar2
405 ,p_years_employed in number
406 ,p_years_in_job in number
407 ,p_years_in_position in number
408 ,p_years_in_grade in number
409 ,p_employee_number in varchar2
410 ,p_start_date in date
411 ,p_original_start_date in date
412 ,p_adjusted_svc_date in date
416 ,p_performance_rating in varchar2
413 ,p_base_salary in number
414 ,p_base_salary_change_date in date
415 ,p_payroll_name in varchar2
417 ,p_performance_rating_type in varchar2
418 ,p_performance_rating_date in date
419 ,p_business_group_id in number
420 ,p_organization_id in number
421 ,p_job_id in number
422 ,p_grade_id in number
423 ,p_position_id in number
424 ,p_people_group_id in number
425 ,p_soft_coding_keyflex_id in number
426 ,p_location_id in number
427 ,p_pay_rate_id in number
428 ,p_assignment_status_type_id in number
429 ,p_frequency in varchar2
430 ,p_grade_annulization_factor in number
431 ,p_pay_annulization_factor in number
432 ,p_grd_min_val in number
433 ,p_grd_max_val in number
434 ,p_grd_mid_point in number
435 ,p_grd_quartile in varchar2
436 ,p_grd_comparatio in number
437 ,p_emp_category in varchar2
438 ,p_change_reason in varchar2
439 ,p_normal_hours in number
440 ,p_email_address in varchar2
441 ,p_base_salary_frequency in varchar2
442 ,p_new_assgn_ovn in number
443 ,p_new_perf_event_id in number
444 ,p_new_perf_review_id in number
445 ,p_post_process_stat_cd in varchar2
446 ,p_feedback_rating in varchar2
447 ,p_feedback_comments in varchar2
448 ,p_object_version_number in number
449 ,p_custom_segment1 in varchar2
450 ,p_custom_segment2 in varchar2
451 ,p_custom_segment3 in varchar2
452 ,p_custom_segment4 in varchar2
453 ,p_custom_segment5 in varchar2
454 ,p_custom_segment6 in varchar2
455 ,p_custom_segment7 in varchar2
456 ,p_custom_segment8 in varchar2
457 ,p_custom_segment9 in varchar2
458 ,p_custom_segment10 in varchar2
459 ,p_custom_segment11 in number
460 ,p_custom_segment12 in number
461 ,p_custom_segment13 in number
462 ,p_custom_segment14 in number
463 ,p_custom_segment15 in number
464 ,p_custom_segment16 in number
465 ,p_custom_segment17 in number
466 ,p_custom_segment18 in number
467 ,p_custom_segment19 in number
468 ,p_custom_segment20 in number
469 ,p_people_group_name in varchar2
470 ,p_people_group_segment1 in varchar2
471 ,p_people_group_segment2 in varchar2
472 ,p_people_group_segment3 in varchar2
473 ,p_people_group_segment4 in varchar2
474 ,p_people_group_segment5 in varchar2
475 ,p_people_group_segment6 in varchar2
476 ,p_people_group_segment7 in varchar2
477 ,p_people_group_segment8 in varchar2
478 ,p_people_group_segment9 in varchar2
479 ,p_people_group_segment10 in varchar2
480 ,p_people_group_segment11 in varchar2
481 ,p_ass_attribute_category in varchar2
482 ,p_ass_attribute1 in varchar2
483 ,p_ass_attribute2 in varchar2
484 ,p_ass_attribute3 in varchar2
485 ,p_ass_attribute4 in varchar2
486 ,p_ass_attribute5 in varchar2
487 ,p_ass_attribute6 in varchar2
488 ,p_ass_attribute7 in varchar2
489 ,p_ass_attribute8 in varchar2
490 ,p_ass_attribute9 in varchar2
491 ,p_ass_attribute10 in varchar2
492 ,p_ass_attribute11 in varchar2
493 ,p_ass_attribute12 in varchar2
494 ,p_ass_attribute13 in varchar2
495 ,p_ass_attribute14 in varchar2
496 ,p_ass_attribute15 in varchar2
497 ,p_ass_attribute16 in varchar2
498 ,p_ass_attribute17 in varchar2
499 ,p_ass_attribute18 in varchar2
500 ,p_ass_attribute19 in varchar2
501 ,p_ass_attribute20 in varchar2
502 ,p_ass_attribute21 in varchar2
503 ,p_ass_attribute22 in varchar2
504 ,p_ass_attribute23 in varchar2
505 ,p_ass_attribute24 in varchar2
506 ,p_ass_attribute25 in varchar2
507 ,p_ass_attribute26 in varchar2
508 ,p_ass_attribute27 in varchar2
509 ,p_ass_attribute28 in varchar2
510 ,p_ass_attribute29 in varchar2
511 ,p_ass_attribute30 in varchar2
512 ,p_ws_comments in varchar2
513 ,p_cpi_attribute_category in varchar2
514 ,p_cpi_attribute1 in varchar2
515 ,p_cpi_attribute2 in varchar2
516 ,p_cpi_attribute3 in varchar2
517 ,p_cpi_attribute4 in varchar2
518 ,p_cpi_attribute5 in varchar2
519 ,p_cpi_attribute6 in varchar2
520 ,p_cpi_attribute7 in varchar2
521 ,p_cpi_attribute8 in varchar2
522 ,p_cpi_attribute9 in varchar2
523 ,p_cpi_attribute10 in varchar2
524 ,p_cpi_attribute11 in varchar2
525 ,p_cpi_attribute12 in varchar2
526 ,p_cpi_attribute13 in varchar2
527 ,p_cpi_attribute14 in varchar2
528 ,p_cpi_attribute15 in varchar2
529 ,p_cpi_attribute16 in varchar2
530 ,p_cpi_attribute17 in varchar2
531 ,p_cpi_attribute18 in varchar2
532 ,p_cpi_attribute19 in varchar2
533 ,p_cpi_attribute20 in varchar2
534 ,p_cpi_attribute21 in varchar2
535 ,p_cpi_attribute22 in varchar2
536 ,p_cpi_attribute23 in varchar2
537 ,p_cpi_attribute24 in varchar2
538 ,p_cpi_attribute25 in varchar2
539 ,p_cpi_attribute26 in varchar2
540 ,p_cpi_attribute27 in varchar2
541 ,p_cpi_attribute28 in varchar2
542 ,p_cpi_attribute29 in varchar2
543 ,p_cpi_attribute30 in varchar2
544 ,p_feedback_date in date
545 )
546 Return g_rec_type;
547 --
548 end ben_cpi_shd;
[Home] [Help]