1 Package hr_psf_shd as
2 /* $Header: hrpsfrhi.pkh 120.1 2006/06/01 09:54:40 grreddy ship $ */
3 --
4 --
5 -- ----------------------------------------------------------------------------
6 -- | Global Record Type Specification |
7 -- ----------------------------------------------------------------------------
8 --
9 g_debug boolean := hr_utility.debug_enabled;
10
11 Type g_rec_type Is Record
12 (
13 position_id number(15),
14 effective_start_date date,
15 effective_end_date date,
16 availability_status_id number(15),
17 business_group_id number(15),
18 entry_step_id number(15),
19 entry_grade_rule_id number(15),
20 job_id number(15),
21 location_id number(15),
22 organization_id number(15),
23 pay_freq_payroll_id number(15),
24 position_definition_id number(15),
25 position_transaction_id number(15),
26 prior_position_id number(15),
27 relief_position_id number(15),
28 entry_grade_id number(15),
29 successor_position_id number(15),
30 supervisor_position_id number(15),
31 amendment_date date,
32 amendment_recommendation varchar2(2000),
33 amendment_ref_number varchar2(30),
34 bargaining_unit_cd varchar2(30),
35 comments varchar2(2000), -- pseudo column
36 current_job_prop_end_date date,
37 current_org_prop_end_date date,
38 avail_status_prop_end_date date,
39 date_effective date,
40 date_end date,
41 earliest_hire_date date,
42 fill_by_date date,
43 frequency varchar2(30),
44 fte number(11,2), -- Increased length
45 max_persons number(9), -- Increased length
46 name varchar2(240),
47 overlap_period number(22,2),
48 overlap_unit_cd varchar2(30),
49 pay_term_end_day_cd varchar2(30),
50 pay_term_end_month_cd varchar2(30),
51 permanent_temporary_flag varchar2(30),
52 permit_recruitment_flag varchar2(30),
53 position_type varchar2(30),
54 posting_description varchar2(2000),
55 probation_period number(22,2),
56 probation_period_unit_cd varchar2(30),
57 replacement_required_flag varchar2(30),
58 review_flag varchar2(30),
59 seasonal_flag varchar2(30),
60 security_requirements varchar2(2000),
61 status varchar2(30),
62 term_start_day_cd varchar2(30),
63 term_start_month_cd varchar2(30),
64 time_normal_finish varchar2(9), -- Increased length
65 time_normal_start varchar2(9), -- Increased length
66 update_source_cd varchar2(30),
67 working_hours number(22,3),
68 works_council_approval_flag varchar2(30),
69 work_period_type_cd varchar2(30),
70 work_term_end_day_cd varchar2(30),
71 work_term_end_month_cd varchar2(30),
72 proposed_fte_for_layoff number(11,2), -- Increased
73 proposed_date_for_layoff date,
74 pay_basis_id number(11,2), -- Increased
75 supervisor_id number(11,2), -- Increased
76 copied_to_old_table_flag varchar2(30),
77 information1 varchar2(150),
78 information2 varchar2(150),
79 information3 varchar2(150),
80 information4 varchar2(150),
81 information5 varchar2(150),
82 information6 varchar2(150),
83 information7 varchar2(150),
84 information8 varchar2(150),
85 information9 varchar2(150),
86 information10 varchar2(150),
87 information11 varchar2(150),
88 information12 varchar2(150),
89 information13 varchar2(150),
90 information14 varchar2(150),
91 information15 varchar2(150),
92 information16 varchar2(150),
93 information17 varchar2(150),
94 information18 varchar2(150),
95 information19 varchar2(150),
96 information20 varchar2(150),
97 information21 varchar2(150),
98 information22 varchar2(150),
99 information23 varchar2(150),
100 information24 varchar2(150),
101 information25 varchar2(150),
102 information26 varchar2(150),
103 information27 varchar2(150),
104 information28 varchar2(150),
105 information29 varchar2(150),
106 information30 varchar2(150),
107 information_category varchar2(30),
108 attribute1 varchar2(150),
109 attribute2 varchar2(150),
110 attribute3 varchar2(150),
111 attribute4 varchar2(150),
112 attribute5 varchar2(150),
113 attribute6 varchar2(150),
114 attribute7 varchar2(150),
115 attribute8 varchar2(150),
116 attribute9 varchar2(150),
117 attribute10 varchar2(150),
118 attribute11 varchar2(150),
119 attribute12 varchar2(150),
120 attribute13 varchar2(150),
121 attribute14 varchar2(150),
122 attribute15 varchar2(150),
123 attribute16 varchar2(150),
124 attribute17 varchar2(150),
125 attribute18 varchar2(150),
126 attribute19 varchar2(150),
127 attribute20 varchar2(150),
128 attribute21 varchar2(150),
129 attribute22 varchar2(150),
130 attribute23 varchar2(150),
131 attribute24 varchar2(150),
132 attribute25 varchar2(150),
133 attribute26 varchar2(150),
134 attribute27 varchar2(150),
135 attribute28 varchar2(150),
136 attribute29 varchar2(150),
137 attribute30 varchar2(150),
138 attribute_category varchar2(30),
139 request_id number(15),
140 program_application_id number(15),
141 program_id number(15),
142 program_update_date date,
143 object_version_number number(9),
144 security_profile_id number(15)
145 );
146 --
147 -- ----------------------------------------------------------------------------
148 -- | Global Definitions - Internal Development Use Only |
149 -- ----------------------------------------------------------------------------
150 --
151 g_old_rec g_rec_type; -- Global record definition
152 --
153 function get_availability_status(p_availability_status_id number
154 ,p_business_group_id number)
155 return varchar2 ;
156 --
157 procedure get_position_job_org(p_position_id number,
158 p_effective_date date default sysdate,
159 p_job_id out nocopy number,
160 p_organization_id out nocopy number
161 );
162 --
163 --
164 function SYSTEM_AVAILABILITY_STATUS (
165 p_availability_status_id number) return varchar2;
166 --
167 -- ----------------------------------------------------------------------------
168 -- |-------------------------< position_wf_sync >-----------------------------|
169 -- ----------------------------------------------------------------------------
170 --
171 procedure position_wf_sync(p_position_id number, p_effective_date date);
172 --
173 -- ----------------------------------------------------------------------------
174 -- |---------------------------< my_synch_routine >---------------------------|
175 -- ----------------------------------------------------------------------------
176 --
177 procedure my_synch_routine(mykey in varchar2);
178 --
179 --
180 -- ----------------------------------------------------------------------------
181 -- |---------------------------< constraint_error >---------------------------|
182 -- ----------------------------------------------------------------------------
183 -- {Start Of Comments}
184 --
185 -- Description:
186 -- This procedure is called when a constraint has been violated (i.e.
187 -- The exception hr_api.check_integrity_violated,
188 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
189 -- hr_api.unique_integrity_violated has been raised).
190 -- The exceptions can only be raised as follows:
191 -- 1) A check constraint can only be violated during an INSERT or UPDATE
192 -- dml operation.
193 -- 2) A parent integrity constraint can only be violated during an
194 -- INSERT or UPDATE dml operation.
195 -- 3) A child integrity constraint can only be violated during an
196 -- DELETE dml operation.
197 -- 4) A unique integrity constraint can only be violated during INSERT or
198 -- UPDATE dml operation.
199 --
200 -- Prerequisites:
201 -- 1) Either hr_api.check_integrity_violated,
202 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
203 -- hr_api.unique_integrity_violated has been raised with the subsequent
204 -- stripping of the constraint name from the generated error message
205 -- text.
206 -- 2) Standalone validation test which corresponds with a constraint error.
207 --
208 -- In Parameter:
209 -- p_constraint_name is in upper format and is just the constraint name
210 -- (e.g. not prefixed by brackets, schema owner etc).
211 --
212 -- Post Success:
213 -- Development dependant.
214 --
215 -- Post Failure:
216 -- Developement dependant.
217 --
218 -- Developer Implementation Notes:
219 -- For each constraint being checked the hr system package failure message
220 -- has been generated as a template only. These system error messages should
221 -- be modified as required (i.e. change the system failure message to a user
222 -- friendly defined error message).
223 --
224 -- Access Status:
225 -- Internal Development Use Only.
226 --
227 -- {End Of Comments}
228 -- ----------------------------------------------------------------------------
229 Procedure constraint_error
230 (p_constraint_name in all_constraints.constraint_name%TYPE);
231 --
232 -- ----------------------------------------------------------------------------
233 -- |-----------------------------< api_updating >-----------------------------|
234 -- ----------------------------------------------------------------------------
235 -- {Start Of Comments}
236 --
237 -- Description:
238 -- This function is used to populate the g_old_rec record with the current
239 -- row from the database for the specified primary key provided that the
240 -- primary key exists, and is valid, and does not already match the current
241 -- g_old_rec.
242 -- The function will always return a TRUE value if the g_old_rec is
243 -- populated with the current row. A FALSE value will be returned if all of
244 -- the primary key arguments are null.
245 --
246 -- Prerequisites:
247 -- None.
248 --
249 -- In Parameters:
250 --
251 -- Post Success:
252 -- A value of TRUE will be returned indiciating that the g_old_rec is
253 -- current.
254 -- A value of FALSE will be returned if all of the primary key arguments
255 -- have a null value (this indicates that the row has not be inserted into
256 -- the Schema), and therefore could never have a corresponding row.
257 --
258 -- Post Failure:
259 -- A failure can only occur under two circumstances:
260 -- 1) The primary key is invalid (i.e. a row does not exist for the
261 -- specified primary key values).
262 -- 2) If an object_version_number exists but is NOT the same as the current
263 -- g_old_rec value.
264 --
265 -- Developer Implementation Notes:
266 -- None.
267 --
268 -- Access Status:
269 -- Internal Development Use Only.
270 --
271 -- {End Of Comments}
272 -- ----------------------------------------------------------------------------
273 Function api_updating
274 (p_effective_date in date,
275 p_position_id in number,
276 p_object_version_number in number
277 ) Return Boolean;
278 --
279 -- ----------------------------------------------------------------------------
280 -- |--------------------------< find_dt_del_modes >---------------------------|
281 -- ----------------------------------------------------------------------------
282 -- {Start Of Comments}
283 --
284 -- Description:
285 -- This procedure is used to determine what datetrack delete modes are
286 -- allowed as of the effective date for this entity. The procedure will
287 -- return a corresponding Boolean value for each of the delete modes
288 -- available where TRUE indicates that the corresponding delete mode is
289 -- available.
290 --
291 -- Prerequisites:
292 -- None.
293 --
294 -- In Parameters:
295 -- p_effective_date
296 -- Specifies the date at which the datetrack modes will be operated on.
297 -- p_base_key_value
298 -- Specifies the primary key value for this datetrack entity.
299 -- (E.g. For this entity the assignment of the argument would be:
300 -- p_base_key_value = :position_id).
301 --
302 -- Post Success:
303 -- Processing continues.
304 --
305 -- Post Failure:
306 -- Failure might occur if for the specified effective date and primary key
307 -- value a row doesn't exist.
308 --
309 -- Developer Implementation Notes:
310 -- This procedure could require changes if this entity has any sepcific
311 -- delete restrictions.
312 -- For example, this entity might disallow the datetrack delete mode of
316 -- Access Status:
313 -- ZAP. To implement this you would have to set and return a Boolean value
314 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
315 --
317 -- Internal Development Use Only.
318 --
319 -- {End Of Comments}
320 -- ----------------------------------------------------------------------------
321 Procedure find_dt_del_modes
322 (p_effective_date in date,
323 p_base_key_value in number,
324 p_zap out nocopy boolean,
325 p_delete out nocopy boolean,
326 p_future_change out nocopy boolean,
327 p_delete_next_change out nocopy boolean);
328 --
329 -- ----------------------------------------------------------------------------
330 -- |--------------------------< find_dt_upd_modes >---------------------------|
331 -- ----------------------------------------------------------------------------
332 -- {Start Of Comments}
333 --
334 -- Description:
335 -- This procedure is used to determine what datetrack update modes are
336 -- allowed as of the effective date for this entity. The procedure will
337 -- return a corresponding Boolean value for each of the update modes
338 -- available where TRUE indicates that the corresponding update mode
339 -- is available.
340 --
341 -- Prerequisites:
342 -- None.
343 --
344 -- In Parameters:
345 -- p_effective_date
346 -- Specifies the date at which the datetrack modes will be operated on.
347 -- p_base_key_value
348 -- Specifies the primary key value for this datetrack entity.
349 -- (E.g. For this entity the assignment of the argument would be:
350 -- p_base_key_value = :position_id).
351 --
352 -- Post Success:
353 -- Processing continues.
354 --
355 -- Post Failure:
356 -- Failure might occur if for the specified effective date and primary key
357 -- value a row doesn't exist.
358 --
359 -- Developer Implementation Notes:
360 -- This procedure could require changes if this entity has any sepcific
361 -- delete restrictions.
362 -- For example, this entity might disallow the datetrack update mode of
363 -- UPDATE. To implement this you would have to set and return a Boolean
364 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
365 --
366 -- Access Status:
367 -- Internal Development Use Only.
368 --
369 -- {End Of Comments}
370 -- ----------------------------------------------------------------------------
371 Procedure find_dt_upd_modes
372 (p_effective_date in date,
373 p_base_key_value in number,
374 p_correction out nocopy boolean,
375 p_update out nocopy boolean,
376 p_update_override out nocopy boolean,
377 p_update_change_insert out nocopy boolean);
378 --
379 -- ----------------------------------------------------------------------------
380 -- |------------------------< upd_effective_end_date >------------------------|
381 -- ----------------------------------------------------------------------------
382 -- {Start Of Comments}
383 --
384 -- Description:
385 -- This procedure will update the specified datetrack row with the
386 -- specified new effective end date. The object version number is also
387 -- set to the next object version number. DateTrack modes which call
388 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
389 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
390 -- This is an internal datetrack maintenance procedure which should
391 -- not be modified in anyway.
392 --
393 -- Prerequisites:
394 -- None.
395 --
396 -- In Parameters:
397 -- p_new_effective_end_date
398 -- Specifies the new effective end date which will be set for the
399 -- row as of the effective date.
400 -- p_base_key_value
401 -- Specifies the primary key value for this datetrack entity.
402 -- (E.g. For this entity the assignment of the argument would be:
403 -- p_base_key_value = :position_id).
404 --
405 -- Post Success:
406 -- The specified row will be updated with the new effective end date and
407 -- object_version_number.
408 --
409 -- Post Failure:
410 -- Failure might occur if for the specified effective date and primary key
411 -- value a row doesn't exist.
412 --
413 -- Developer Implementation Notes:
414 -- This is an internal datetrack maintenance procedure which should
415 -- not be modified in anyway.
416 --
417 -- Access Status:
418 -- Internal Row Handler Use Only.
419 --
420 -- {End Of Comments}
421 -- ----------------------------------------------------------------------------
422 Procedure upd_effective_end_date
423 (p_effective_date in date,
424 p_base_key_value in number,
425 p_new_effective_end_date in date,
426 p_validation_start_date in date,
427 p_validation_end_date in date,
428 p_object_version_number out nocopy number);
429 --
430 -- ----------------------------------------------------------------------------
431 -- |---------------------------------< lck >----------------------------------|
432 -- ----------------------------------------------------------------------------
433 -- {Start Of Comments}
434 --
435 -- Description:
436 -- The Lck process for datetrack is complicated and comprises of the
437 -- following processing
438 -- The processing steps are as follows:
442 -- 3) The datetrack mode is then validated to ensure the operation is
439 -- 1) The row to be updated or deleted must be locked.
440 -- By locking this row, the g_old_rec record data type is populated.
441 -- 2) If a comment exists the text is selected from hr_comments.
443 -- valid. If the mode is valid the validation start and end dates for
444 -- the mode will be derived and returned. Any required locking is
445 -- completed when the datetrack mode is validated.
446 --
447 -- Prerequisites:
448 -- When attempting to call the lck procedure the object version number,
449 -- primary key, effective date and datetrack mode must be specified.
450 --
451 -- In Parameters:
452 -- p_effective_date
453 -- Specifies the date of the datetrack update operation.
454 -- p_datetrack_mode
455 -- Determines the datetrack update or delete mode.
456 --
457 -- Post Success:
458 -- On successful completion of the Lck process the row to be updated or
459 -- deleted will be locked and selected into the global data structure
460 -- g_old_rec.
461 --
462 -- Post Failure:
463 -- The Lck process can fail for three reasons:
464 -- 1) When attempting to lock the row the row could already be locked by
465 -- another user. This will raise the HR_Api.Object_Locked exception.
466 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
467 -- This error is trapped and reported using the message name
468 -- 'HR_7220_INVALID_PRIMARY_KEY'.
469 -- 3) The row although existing in the HR Schema has a different object
470 -- version number than the object version number specified.
471 -- This error is trapped and reported using the message name
472 -- 'HR_7155_OBJECT_INVALID'.
473 --
474 -- Developer Implementation Notes:
475 -- None.
476 --
477 -- Access Status:
478 -- Internal Development Use Only.
479 --
480 -- {End Of Comments}
481 -- ----------------------------------------------------------------------------
482 Procedure lck
483 (p_effective_date in date,
484 p_datetrack_mode in varchar2,
485 p_position_id in number,
486 p_object_version_number in number,
487 p_validation_start_date out nocopy date,
488 p_validation_end_date out nocopy date);
489 --
490 -- ----------------------------------------------------------------------------
491 -- |-----------------------------< convert_args >-----------------------------|
492 -- ----------------------------------------------------------------------------
493 -- {Start Of Comments}
494 --
495 -- Description:
496 -- This function is used to turn attribute parameters into the record
497 -- structure parameter g_rec_type.
498 --
499 -- Prerequisites:
500 -- This is a private function and can only be called from the ins or upd
501 -- attribute processes.
502 --
503 -- In Parameters:
504 --
505 -- Post Success:
506 -- A returning record structure will be returned.
507 --
508 -- Post Failure:
509 -- No direct error handling is required within this function. Any possible
510 -- errors within this function will be a PL/SQL value error due to conversion
511 -- of datatypes or data lengths.
512 --
513 -- Developer Implementation Notes:
514 -- None.
515 --
516 -- Access Status:
517 -- Internal Row Handler Use Only.
518 --
519 -- {End Of Comments}
520 -- ----------------------------------------------------------------------------
521 Function convert_args
522 (
523 p_position_id in number,
524 p_effective_start_date in date,
525 p_effective_end_date in date,
526 p_availability_status_id in number,
527 p_business_group_id in number,
528 p_entry_step_id in number,
529 p_entry_grade_rule_id in number,
530 p_job_id in number,
531 p_location_id in number,
532 p_organization_id in number,
533 p_pay_freq_payroll_id in number,
534 p_position_definition_id in number,
535 p_position_transaction_id in number,
536 p_prior_position_id in number,
537 p_relief_position_id in number,
538 p_entry_grade_id in number,
539 p_successor_position_id in number,
540 p_supervisor_position_id in number,
541 p_amendment_date in date,
542 p_amendment_recommendation in varchar2,
543 p_amendment_ref_number in varchar2,
544 p_bargaining_unit_cd in varchar2,
545 p_comments in varchar2,
546 p_current_job_prop_end_date in date,
547 p_current_org_prop_end_date in date,
548 p_avail_status_prop_end_date in date,
549 p_date_effective in date,
550 p_date_end in date,
551 p_earliest_hire_date in date,
552 p_fill_by_date in date,
553 p_frequency in varchar2,
554 p_fte in number,
555 p_max_persons in number,
556 p_name in varchar2,
557 p_overlap_period in number,
558 p_overlap_unit_cd in varchar2,
559 p_pay_term_end_day_cd in varchar2,
560 p_pay_term_end_month_cd in varchar2,
561 p_permanent_temporary_flag in varchar2,
562 p_permit_recruitment_flag in varchar2,
563 p_position_type in varchar2,
564 p_posting_description in varchar2,
565 p_probation_period in number,
566 p_probation_period_unit_cd in varchar2,
567 p_replacement_required_flag in varchar2,
568 p_review_flag in varchar2,
569 p_seasonal_flag in varchar2,
570 p_security_requirements in varchar2,
571 p_status in varchar2,
572 p_term_start_day_cd in varchar2,
573 p_term_start_month_cd in varchar2,
574 p_time_normal_finish in varchar2,
575 p_time_normal_start in varchar2,
576 p_update_source_cd in varchar2,
577 p_working_hours in number,
578 p_works_council_approval_flag in varchar2,
579 p_work_period_type_cd in varchar2,
580 p_work_term_end_day_cd in varchar2,
581 p_work_term_end_month_cd in varchar2,
582 p_proposed_fte_for_layoff in number,
583 p_proposed_date_for_layoff in date,
584 p_pay_basis_id in number,
585 p_supervisor_id in number,
586 p_copied_to_old_table_flag in varchar2,
587 p_information1 in varchar2,
588 p_information2 in varchar2,
589 p_information3 in varchar2,
590 p_information4 in varchar2,
591 p_information5 in varchar2,
592 p_information6 in varchar2,
593 p_information7 in varchar2,
594 p_information8 in varchar2,
595 p_information9 in varchar2,
596 p_information10 in varchar2,
597 p_information11 in varchar2,
598 p_information12 in varchar2,
599 p_information13 in varchar2,
600 p_information14 in varchar2,
601 p_information15 in varchar2,
602 p_information16 in varchar2,
603 p_information17 in varchar2,
604 p_information18 in varchar2,
605 p_information19 in varchar2,
606 p_information20 in varchar2,
607 p_information21 in varchar2,
608 p_information22 in varchar2,
609 p_information23 in varchar2,
610 p_information24 in varchar2,
611 p_information25 in varchar2,
612 p_information26 in varchar2,
613 p_information27 in varchar2,
614 p_information28 in varchar2,
615 p_information29 in varchar2,
616 p_information30 in varchar2,
617 p_information_category in varchar2,
618 p_attribute1 in varchar2,
619 p_attribute2 in varchar2,
620 p_attribute3 in varchar2,
621 p_attribute4 in varchar2,
622 p_attribute5 in varchar2,
623 p_attribute6 in varchar2,
624 p_attribute7 in varchar2,
625 p_attribute8 in varchar2,
626 p_attribute9 in varchar2,
627 p_attribute10 in varchar2,
628 p_attribute11 in varchar2,
629 p_attribute12 in varchar2,
630 p_attribute13 in varchar2,
631 p_attribute14 in varchar2,
632 p_attribute15 in varchar2,
633 p_attribute16 in varchar2,
634 p_attribute17 in varchar2,
635 p_attribute18 in varchar2,
636 p_attribute19 in varchar2,
637 p_attribute20 in varchar2,
638 p_attribute21 in varchar2,
639 p_attribute22 in varchar2,
640 p_attribute23 in varchar2,
641 p_attribute24 in varchar2,
642 p_attribute25 in varchar2,
643 p_attribute26 in varchar2,
644 p_attribute27 in varchar2,
645 p_attribute28 in varchar2,
646 p_attribute29 in varchar2,
647 p_attribute30 in varchar2,
648 p_attribute_category in varchar2,
649 p_request_id in number,
650 p_program_application_id in number,
651 p_program_id in number,
652 p_program_update_date in date,
653 p_object_version_number in number,
654 p_security_profile_id in number
655 )
656 Return g_rec_type;
657 --
658 function POS_SYSTEM_AVAILABILITY_STATUS (
659 p_position_id number,
660 p_effective_date date) return varchar2;
661 --
662 end hr_psf_shd;
[Home] [Help]