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