1 Package pay_etp_shd AUTHID CURRENT_USER as
2 /* $Header: pyetprhi.pkh 120.3 2007/02/01 10:39:46 mshingan noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (element_type_id number(9)
10 ,effective_start_date date
11 ,effective_end_date date
12 ,business_group_id number(15)
13 ,legislation_code varchar2(30)
14 ,formula_id number(9)
15 ,input_currency_code varchar2(15)
16 ,output_currency_code varchar2(15)
17 ,classification_id number(9)
18 ,benefit_classification_id number(15)
19 ,additional_entry_allowed_flag varchar2(30)
20 ,adjustment_only_flag varchar2(30)
21 ,closed_for_entry_flag varchar2(30)
22 ,element_name varchar2(80)
23 ,indirect_only_flag varchar2(30)
24 ,multiple_entries_allowed_flag varchar2(30)
25 ,multiply_value_flag varchar2(30)
26 ,post_termination_rule varchar2(30)
27 ,process_in_run_flag varchar2(30)
28 ,processing_priority number(9)
29 ,processing_type varchar2(30)
30 ,standard_link_flag varchar2(30)
31 ,comment_id number(15)
32 ,comments varchar2(2000) -- pseudo column
33 ,description varchar2(240)
34 ,legislation_subgroup varchar2(30)
35 ,qualifying_age number(9) -- Increased length
36 ,qualifying_length_of_service number(11,2) -- Increased length
37 ,qualifying_units varchar2(30)
38 ,reporting_name varchar2(80)
39 ,attribute_category varchar2(30)
40 ,attribute1 varchar2(150)
41 ,attribute2 varchar2(150)
42 ,attribute3 varchar2(150)
43 ,attribute4 varchar2(150)
44 ,attribute5 varchar2(150)
45 ,attribute6 varchar2(150)
46 ,attribute7 varchar2(150)
47 ,attribute8 varchar2(150)
48 ,attribute9 varchar2(150)
49 ,attribute10 varchar2(150)
50 ,attribute11 varchar2(150)
51 ,attribute12 varchar2(150)
52 ,attribute13 varchar2(150)
53 ,attribute14 varchar2(150)
54 ,attribute15 varchar2(150)
55 ,attribute16 varchar2(150)
56 ,attribute17 varchar2(150)
57 ,attribute18 varchar2(150)
58 ,attribute19 varchar2(150)
59 ,attribute20 varchar2(150)
60 ,element_information_category varchar2(30)
61 ,element_information1 varchar2(150)
62 ,element_information2 varchar2(150)
63 ,element_information3 varchar2(150)
64 ,element_information4 varchar2(150)
65 ,element_information5 varchar2(150)
66 ,element_information6 varchar2(150)
67 ,element_information7 varchar2(150)
68 ,element_information8 varchar2(150)
69 ,element_information9 varchar2(150)
70 ,element_information10 varchar2(150)
71 ,element_information11 varchar2(150)
72 ,element_information12 varchar2(150)
73 ,element_information13 varchar2(150)
74 ,element_information14 varchar2(150)
75 ,element_information15 varchar2(150)
76 ,element_information16 varchar2(150)
77 ,element_information17 varchar2(150)
78 ,element_information18 varchar2(150)
79 ,element_information19 varchar2(150)
80 ,element_information20 varchar2(150)
81 ,third_party_pay_only_flag varchar2(30)
82 ,object_version_number number(9)
83 ,iterative_flag varchar2(30)
84 ,iterative_formula_id number(9)
85 ,iterative_priority number(9)
86 ,creator_type varchar2(30)
87 ,retro_summ_ele_id number(9)
88 ,grossup_flag varchar2(30)
89 ,process_mode varchar2(30)
90 ,advance_indicator varchar2(9) -- Increased length
91 ,advance_payable varchar2(9) -- Increased length
92 ,advance_deduction varchar2(9) -- Increased length
93 ,process_advance_entry varchar2(9) -- Increased length
94 ,proration_group_id number(15)
95 ,proration_formula_id number(9)
96 ,recalc_event_group_id number(15)
97 ,once_each_period_flag varchar2(30)
98 ,time_definition_type varchar2(9) -- Increased Length
99 ,time_definition_id number(9) -- Added Bug # 4929104
100 ,advance_element_type_id number(9) -- Added for Advance Pay
101 ,deduction_element_type_id number(9)
102 );
103 --
104 -- ----------------------------------------------------------------------------
105 -- | Global Definitions - Internal Development Use Only |
106 -- ----------------------------------------------------------------------------
107 --
108 g_old_rec g_rec_type; -- Global record definition
109 -- Global table name
110 g_tab_nam constant varchar2(30) := 'PAY_ELEMENT_TYPES_F';
111 g_api_dml boolean; -- Global api dml status
112 --
113 -- ----------------------------------------------------------------------------
114 -- |------------------------< return_api_dml_status >-------------------------|
115 -- ----------------------------------------------------------------------------
116 -- {Start Of Comments}
117 --
118 -- Description:
119 -- This function will return the current g_api_dml private global
120 -- boolean status.
121 -- The g_api_dml status determines if at the time of the function
122 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
123 -- is being issued from within an api.
124 -- If the status is TRUE then a dml statement is being issued from
125 -- within this entity api.
126 -- This function is primarily to support database triggers which
127 -- need to maintain the object_version_number for non-supported
128 -- dml statements (i.e. dml statement issued outside of the api layer).
129 --
130 -- Prerequisites:
131 -- None.
132 --
133 -- In Parameters:
134 -- None.
135 --
136 -- Post Success:
137 -- Processing continues.
138 -- If the function returns a TRUE value then, dml is being executed from
139 -- within this api.
140 --
141 -- Post Failure:
142 -- None.
143 --
144 -- Access Status:
145 -- Internal Row Handler Use Only.
146 --
147 -- {End Of Comments}
148 -- ----------------------------------------------------------------------------
149 Function return_api_dml_status Return Boolean;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |---------------------------< constraint_error >---------------------------|
153 -- ----------------------------------------------------------------------------
154 -- {Start Of Comments}
155 --
156 -- Description:
157 -- This procedure is called when a constraint has been violated (i.e.
158 -- The exception hr_api.check_integrity_violated,
159 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
160 -- hr_api.unique_integrity_violated has been raised).
161 -- The exceptions can only be raised as follows:
162 -- 1) A check constraint can only be violated during an INSERT or UPDATE
163 -- dml operation.
164 -- 2) A parent integrity constraint can only be violated during an
165 -- INSERT or UPDATE dml operation.
166 -- 3) A child integrity constraint can only be violated during an
167 -- DELETE dml operation.
168 -- 4) A unique integrity constraint can only be violated during INSERT or
169 -- UPDATE dml operation.
170 --
171 -- Prerequisites:
172 -- 1) Either hr_api.check_integrity_violated,
173 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
174 -- hr_api.unique_integrity_violated has been raised with the subsequent
175 -- stripping of the constraint name from the generated error message
176 -- text.
177 -- 2) Standalone validation test which corresponds with a constraint error.
178 --
179 -- In Parameter:
180 -- p_constraint_name is in upper format and is just the constraint name
181 -- (e.g. not prefixed by brackets, schema owner etc).
182 --
183 -- Post Success:
184 -- Development dependant.
185 --
186 -- Post Failure:
187 -- Developement dependant.
188 --
189 -- Developer Implementation Notes:
190 -- For each constraint being checked the hr system package failure message
191 -- has been generated as a template only. These system error messages should
192 -- be modified as required (i.e. change the system failure message to a user
193 -- friendly defined error message).
194 --
195 -- Access Status:
196 -- Internal Development Use Only.
197 --
198 -- {End Of Comments}
199 -- ----------------------------------------------------------------------------
200 Procedure constraint_error
201 (p_constraint_name in all_constraints.constraint_name%TYPE);
202 --
203 -- ----------------------------------------------------------------------------
204 -- |-----------------------------< api_updating >-----------------------------|
205 -- ----------------------------------------------------------------------------
206 -- {Start Of Comments}
207 --
208 -- Description:
209 -- This function is used to populate the g_old_rec record with the
210 -- current row from the database for the specified primary key
211 -- provided that the primary key exists and is valid and does not
212 -- already match the current g_old_rec. The function will always return
213 -- a TRUE value if the g_old_rec is populated with the current row.
214 -- A FALSE value will be returned if all of the primary key arguments
215 -- are null.
216 --
217 -- Prerequisites:
218 -- None.
219 --
220 -- In Parameters:
221 --
222 -- Post Success:
223 -- A value of TRUE will be returned indiciating that the g_old_rec
224 -- is current.
225 -- A value of FALSE will be returned if all of the primary key arguments
226 -- have a null value (this indicates that the row has not be inserted into
227 -- the Schema), and therefore could never have a corresponding row.
228 --
229 -- Post Failure:
230 -- A failure can only occur under two circumstances:
231 -- 1) The primary key is invalid (i.e. a row does not exist for the
232 -- specified primary key values).
233 -- 2) If an object_version_number exists but is NOT the same as the current
234 -- g_old_rec value.
235 --
236 -- Developer Implementation Notes:
237 -- None.
238 --
239 -- Access Status:
240 -- Internal Development Use Only.
241 --
242 -- {End Of Comments}
243 -- ----------------------------------------------------------------------------
244 Function api_updating
245 (p_effective_date in date
246 ,p_element_type_id in number
247 ,p_object_version_number in number
248 ) Return Boolean;
249 --
250 -- ----------------------------------------------------------------------------
251 -- |---------------------------< find_dt_upd_modes >--------------------------|
252 -- ----------------------------------------------------------------------------
253 -- {Start Of Comments}
254 --
255 -- Description:
256 -- This procedure is used to determine what datetrack update modes are
257 -- allowed as of the effective date for this entity. The procedure will
258 -- return a corresponding Boolean value for each of the update modes
259 -- available where TRUE indicates that the corresponding update mode
260 -- is available.
261 --
262 -- Prerequisites:
263 -- None.
264 --
265 -- In Parameters:
266 -- p_effective_date
267 -- Specifies the date at which the datetrack modes will be operated on.
268 -- p_base_key_value
269 -- Specifies the primary key value for this datetrack entity.
270 -- (E.g. For this entity the assignment of the argument would be:
271 -- p_base_key_value = :element_type_id).
272 --
273 -- Post Success:
274 -- Processing continues.
275 --
276 -- Post Failure:
277 -- Failure might occur if for the specified effective date and primary key
278 -- value a row doesn't exist.
279 --
280 -- Developer Implementation Notes:
281 -- This procedure could require changes if this entity has any sepcific
282 -- delete restrictions.
283 -- For example, this entity might disallow the datetrack update mode of
284 -- UPDATE. To implement this you would have to set and return a Boolean
285 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
286 --
287 -- Access Status:
288 -- Internal Development Use Only.
289 --
290 -- {End Of Comments}
291 -- ----------------------------------------------------------------------------
292 Procedure find_dt_upd_modes
293 (p_effective_date in date
294 ,p_base_key_value in number
295 ,p_correction out nocopy boolean
296 ,p_update out nocopy boolean
297 ,p_update_override out nocopy boolean
298 ,p_update_change_insert out nocopy boolean
299 );
300 --
301 -- ----------------------------------------------------------------------------
302 -- |---------------------------< find_dt_del_modes >--------------------------|
303 -- ----------------------------------------------------------------------------
304 -- {Start Of Comments}
305 --
306 -- Description:
307 -- This procedure is used to determine what datetrack delete modes are
308 -- allowed as of the effective date for this entity. The procedure will
309 -- return a corresponding Boolean value for each of the delete modes
310 -- available where TRUE indicates that the corresponding delete mode is
311 -- available.
312 --
313 -- Prerequisites:
314 -- None.
315 --
316 -- In Parameters:
317 -- p_effective_date
318 -- Specifies the date at which the datetrack modes will be operated on.
319 -- p_base_key_value
320 -- Specifies the primary key value for this datetrack entity.
321 -- (E.g. For this entity the assignment of the argument would be:
322 -- p_base_key_value = :element_type_id).
323 --
324 -- Post Success:
325 -- Processing continues.
326 --
327 -- Post Failure:
328 -- Failure might occur if for the specified effective date and primary key
329 -- value a row doesn't exist.
330 --
331 -- Developer Implementation Notes:
332 -- This procedure could require changes if this entity has any sepcific
333 -- delete restrictions.
334 -- For example, this entity might disallow the datetrack delete mode of
335 -- ZAP. To implement this you would have to set and return a Boolean value
336 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
337 --
338 -- Access Status:
339 -- Internal Development Use Only.
340 --
341 -- {End Of Comments}
342 -- ----------------------------------------------------------------------------
343 Procedure find_dt_del_modes
344 (p_effective_date in date
345 ,p_base_key_value in number
346 ,p_zap out nocopy boolean
347 ,p_delete out nocopy boolean
348 ,p_future_change out nocopy boolean
349 ,p_delete_next_change out nocopy boolean
350 );
351 --
352 -- ----------------------------------------------------------------------------
353 -- |-----------------------< upd_effective_end_date >-------------------------|
354 -- ----------------------------------------------------------------------------
355 -- {Start Of Comments}
356 --
357 -- Description:
358 -- This procedure will update the specified datetrack row with the
362 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
359 -- specified new effective end date. The object version number is also
360 -- set to the next object version number. DateTrack modes which call
361 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
363 -- This is an internal datetrack maintenance procedure which should
364 -- not be modified in anyway.
365 --
366 -- Prerequisites:
367 -- None.
368 --
369 -- In Parameters:
370 -- p_new_effective_end_date
371 -- Specifies the new effective end date which will be set for the
372 -- row as of the effective date.
373 -- p_base_key_value
374 -- Specifies the primary key value for this datetrack entity.
375 -- (E.g. For this entity the assignment of the argument would be:
376 -- p_base_key_value = :element_type_id).
377 --
378 -- Post Success:
379 -- The specified row will be updated with the new effective end date and
380 -- object_version_number.
381 --
382 -- Post Failure:
383 -- Failure might occur if for the specified effective date and primary key
384 -- value a row doesn't exist.
385 --
386 -- Developer Implementation Notes:
387 -- This is an internal datetrack maintenance procedure which should
388 -- not be modified in anyway.
389 --
390 -- Access Status:
391 -- Internal Row Handler Use Only.
392 --
393 -- {End Of Comments}
394 -- ----------------------------------------------------------------------------
395 Procedure upd_effective_end_date
396 (p_effective_date in date
397 ,p_base_key_value in number
398 ,p_new_effective_end_date in date
399 ,p_validation_start_date in date
400 ,p_validation_end_date in date
401 ,p_object_version_number out nocopy number
402 );
403 --
404 -- ----------------------------------------------------------------------------
405 -- |---------------------------------< lck >----------------------------------|
406 -- ----------------------------------------------------------------------------
407 -- {Start Of Comments}
408 --
409 -- Description:
410 -- The Lck process for datetrack is complicated and comprises of the
411 -- following processing
412 -- The processing steps are as follows:
413 -- 1) The row to be updated or deleted must be locked.
414 -- By locking this row, the g_old_rec record data type is populated.
415 -- 2) If a comment exists the text is selected from hr_comments.
416 -- 3) The datetrack mode is then validated to ensure the operation is
417 -- valid. If the mode is valid the validation start and end dates for
418 -- the mode will be derived and returned. Any required locking is
419 -- completed when the datetrack mode is validated.
420 --
421 -- Prerequisites:
422 -- When attempting to call the lck procedure the object version number,
423 -- primary key, effective date and datetrack mode must be specified.
424 --
425 -- In Parameters:
426 -- p_effective_date
427 -- Specifies the date of the datetrack update operation.
428 -- p_datetrack_mode
429 -- Determines the datetrack update or delete mode.
430 --
431 -- Post Success:
432 -- On successful completion of the Lck process the row to be updated or
433 -- deleted will be locked and selected into the global data structure
434 -- g_old_rec.
435 --
436 -- Post Failure:
437 -- The Lck process can fail for three reasons:
438 -- 1) When attempting to lock the row the row could already be locked by
439 -- another user. This will raise the HR_Api.Object_Locked exception.
440 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
441 -- This error is trapped and reported using the message name
442 -- 'HR_7220_INVALID_PRIMARY_KEY'.
443 -- 3) The row although existing in the HR Schema has a different object
444 -- version number than the object version number specified.
445 -- This error is trapped and reported using the message name
446 -- 'HR_7155_OBJECT_INVALID'.
447 --
448 -- Developer Implementation Notes:
449 -- None.
450 --
451 -- Access Status:
452 -- Internal Development Use Only.
453 --
454 -- {End Of Comments}
455 -- ----------------------------------------------------------------------------
456 Procedure lck
457 (p_effective_date in date
458 ,p_datetrack_mode in varchar2
459 ,p_element_type_id in number
460 ,p_object_version_number in number
461 ,p_validation_start_date out nocopy date
462 ,p_validation_end_date out nocopy date
463 );
464 --
465 -- ----------------------------------------------------------------------------
466 -- |-----------------------------< convert_args >-----------------------------|
467 -- ----------------------------------------------------------------------------
468 -- {Start Of Comments}
469 --
470 -- Description:
471 -- This function is used to turn attribute parameters into the record
472 -- structure parameter g_rec_type.
473 --
474 -- Prerequisites:
475 -- This is a private function and can only be called from the ins or upd
476 -- attribute processes.
477 --
478 -- In Parameters:
479 --
480 -- Post Success:
481 -- A returning record structure will be returned.
482 --
483 -- Post Failure:
484 -- No direct error handling is required within this function. Any possible
488 -- Developer Implementation Notes:
485 -- errors within this function will be a PL/SQL value error due to
486 -- conversion of datatypes or data lengths.
487 --
489 -- None.
490 --
491 -- Access Status:
492 -- Internal Row Handler Use Only.
493 --
494 -- {End Of Comments}
495 -- ----------------------------------------------------------------------------
496 Function convert_args
497 (p_element_type_id in number
498 ,p_effective_start_date in date
499 ,p_effective_end_date in date
500 ,p_business_group_id in number
501 ,p_legislation_code in varchar2
502 ,p_formula_id in number
503 ,p_input_currency_code in varchar2
504 ,p_output_currency_code in varchar2
505 ,p_classification_id in number
506 ,p_benefit_classification_id in number
507 ,p_additional_entry_allowed_fla in varchar2
508 ,p_adjustment_only_flag in varchar2
509 ,p_closed_for_entry_flag in varchar2
510 ,p_element_name in varchar2
511 ,p_indirect_only_flag in varchar2
512 ,p_multiple_entries_allowed_fla in varchar2
513 ,p_multiply_value_flag in varchar2
514 ,p_post_termination_rule in varchar2
515 ,p_process_in_run_flag in varchar2
516 ,p_processing_priority in number
517 ,p_processing_type in varchar2
518 ,p_standard_link_flag in varchar2
519 ,p_comment_id in number
520 ,p_comments in varchar2
521 ,p_description in varchar2
522 ,p_legislation_subgroup in varchar2
523 ,p_qualifying_age in number
524 ,p_qualifying_length_of_service in number
525 ,p_qualifying_units in varchar2
526 ,p_reporting_name in varchar2
527 ,p_attribute_category in varchar2
528 ,p_attribute1 in varchar2
529 ,p_attribute2 in varchar2
530 ,p_attribute3 in varchar2
531 ,p_attribute4 in varchar2
532 ,p_attribute5 in varchar2
533 ,p_attribute6 in varchar2
534 ,p_attribute7 in varchar2
535 ,p_attribute8 in varchar2
536 ,p_attribute9 in varchar2
537 ,p_attribute10 in varchar2
538 ,p_attribute11 in varchar2
539 ,p_attribute12 in varchar2
540 ,p_attribute13 in varchar2
541 ,p_attribute14 in varchar2
542 ,p_attribute15 in varchar2
543 ,p_attribute16 in varchar2
544 ,p_attribute17 in varchar2
545 ,p_attribute18 in varchar2
546 ,p_attribute19 in varchar2
547 ,p_attribute20 in varchar2
548 ,p_element_information_category in varchar2
549 ,p_element_information1 in varchar2
550 ,p_element_information2 in varchar2
551 ,p_element_information3 in varchar2
552 ,p_element_information4 in varchar2
553 ,p_element_information5 in varchar2
554 ,p_element_information6 in varchar2
555 ,p_element_information7 in varchar2
556 ,p_element_information8 in varchar2
557 ,p_element_information9 in varchar2
558 ,p_element_information10 in varchar2
559 ,p_element_information11 in varchar2
560 ,p_element_information12 in varchar2
561 ,p_element_information13 in varchar2
562 ,p_element_information14 in varchar2
563 ,p_element_information15 in varchar2
564 ,p_element_information16 in varchar2
565 ,p_element_information17 in varchar2
566 ,p_element_information18 in varchar2
567 ,p_element_information19 in varchar2
568 ,p_element_information20 in varchar2
569 ,p_third_party_pay_only_flag in varchar2
570 ,p_object_version_number in number
571 ,p_iterative_flag in varchar2
572 ,p_iterative_formula_id in number
573 ,p_iterative_priority in number
574 ,p_creator_type in varchar2
575 ,p_retro_summ_ele_id in number
576 ,p_grossup_flag in varchar2
577 ,p_process_mode in varchar2
578 ,p_advance_indicator in varchar2
579 ,p_advance_payable in varchar2
580 ,p_advance_deduction in varchar2
581 ,p_process_advance_entry in varchar2
582 ,p_proration_group_id in number
583 ,p_proration_formula_id in number
584 ,p_recalc_event_group_id in number
585 ,p_once_each_period_flag in varchar2
586 ,p_time_definition_type in varchar2
587 ,p_time_definition_id in number
588 ,p_advance_element_type_id in number
589 ,p_deduction_element_type_id in number
590 )
591 Return g_rec_type;
592 --
593 end pay_etp_shd;
[Home] [Help]