1 Package pay_pay_shd as
2 /* $Header: pypayrhi.pkh 120.2 2007/09/10 12:32:13 ckesanap noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (payroll_id number(9)
10 ,effective_start_date date
11 ,effective_end_date date
12 ,default_payment_method_id number(9)
13 ,business_group_id number(15)
14 ,consolidation_set_id number(9)
15 ,cost_allocation_keyflex_id number(9)
16 ,suspense_account_keyflex_id number(9)
17 ,gl_set_of_books_id number(15)
18 ,soft_coding_keyflex_id number(15)
19 ,period_type varchar2(30)
20 ,organization_id number(15)
21 ,cut_off_date_offset number(9) -- Increased length
22 ,direct_deposit_date_offset number(9) -- Increased length
23 ,first_period_end_date date
24 ,negative_pay_allowed_flag varchar2(30)
25 ,number_of_years number(9) -- Increased length
26 ,pay_advice_date_offset number(9) -- Increased length
27 ,pay_date_offset number(9) -- Increased length
28 ,payroll_name varchar2(80)
29 ,workload_shifting_level varchar2(30)
30 ,comment_id number(15)
31 ,comments varchar2(2000) -- pseudo column
32 ,midpoint_offset number(9) -- Increased length
33 ,attribute_category varchar2(30)
34 ,attribute1 varchar2(150)
35 ,attribute2 varchar2(150)
36 ,attribute3 varchar2(150)
37 ,attribute4 varchar2(150)
38 ,attribute5 varchar2(150)
39 ,attribute6 varchar2(150)
40 ,attribute7 varchar2(150)
41 ,attribute8 varchar2(150)
42 ,attribute9 varchar2(150)
43 ,attribute10 varchar2(150)
44 ,attribute11 varchar2(150)
45 ,attribute12 varchar2(150)
46 ,attribute13 varchar2(150)
47 ,attribute14 varchar2(150)
48 ,attribute15 varchar2(150)
49 ,attribute16 varchar2(150)
50 ,attribute17 varchar2(150)
51 ,attribute18 varchar2(150)
52 ,attribute19 varchar2(150)
53 ,attribute20 varchar2(150)
54 ,arrears_flag varchar2(30)
55 ,payroll_type varchar2(30)
56 ,prl_information_category varchar2(30)
57 ,prl_information1 varchar2(150)
58 ,prl_information2 varchar2(150)
59 ,prl_information3 varchar2(150)
60 ,prl_information4 varchar2(150)
61 ,prl_information5 varchar2(150)
62 ,prl_information6 varchar2(150)
63 ,prl_information7 varchar2(150)
64 ,prl_information8 varchar2(150)
65 ,prl_information9 varchar2(150)
66 ,prl_information10 varchar2(150)
67 ,prl_information11 varchar2(150)
68 ,prl_information12 varchar2(150)
69 ,prl_information13 varchar2(150)
70 ,prl_information14 varchar2(150)
71 ,prl_information15 varchar2(150)
72 ,prl_information16 varchar2(150)
73 ,prl_information17 varchar2(150)
74 ,prl_information18 varchar2(150)
75 ,prl_information19 varchar2(150)
76 ,prl_information20 varchar2(150)
77 ,prl_information21 varchar2(150)
78 ,prl_information22 varchar2(150)
79 ,prl_information23 varchar2(150)
80 ,prl_information24 varchar2(150)
81 ,prl_information25 varchar2(150)
82 ,prl_information26 varchar2(150)
83 ,prl_information27 varchar2(150)
84 ,prl_information28 varchar2(150)
85 ,prl_information29 varchar2(150)
86 ,prl_information30 varchar2(150)
87 ,multi_assignments_flag varchar2(30)
88 ,period_reset_years varchar2(30)
89 ,object_version_number number(9)
90 ,payslip_view_date_offset number -- 6402753
91 );
92 --
93 Type g_seg_rec_type Is Record
94 (
95 segment1 varchar2(60),
96 segment2 varchar2(60),
97 segment3 varchar2(60),
98 segment4 varchar2(60),
99 segment5 varchar2(60),
100 segment6 varchar2(60),
101 segment7 varchar2(60),
102 segment8 varchar2(60),
103 segment9 varchar2(60),
104 segment10 varchar2(60),
105 segment11 varchar2(60),
106 segment12 varchar2(60),
107 segment13 varchar2(60),
108 segment14 varchar2(60),
109 segment15 varchar2(60),
110 segment16 varchar2(60),
111 segment17 varchar2(60),
112 segment18 varchar2(60),
113 segment19 varchar2(60),
114 segment20 varchar2(60),
115 segment21 varchar2(60),
116 segment22 varchar2(60),
117 segment23 varchar2(60),
118 segment24 varchar2(60),
119 segment25 varchar2(60),
120 segment26 varchar2(60),
121 segment27 varchar2(60),
122 segment28 varchar2(60),
123 segment29 varchar2(60),
124 segment30 varchar2(60)
125 );
126 -- ----------------------------------------------------------------------------
127 -- | Global Definitions - Internal Development Use Only |
128 -- ----------------------------------------------------------------------------
129 --
130 g_old_rec g_rec_type; -- Global record definition
131 -- Global table name
132 g_tab_nam constant varchar2(30) := 'PAY_ALL_PAYROLLS_F';
133 g_api_dml boolean; -- Global api dml status
134 --
135 -- ----------------------------------------------------------------------------
136 -- |------------------------< return_api_dml_status >-------------------------|
137 -- ----------------------------------------------------------------------------
138 -- {Start Of Comments}
139 --
140 -- Description:
141 -- This function will return the current g_api_dml private global
142 -- boolean status.
143 -- The g_api_dml status determines if at the time of the function
144 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
145 -- is being issued from within an api.
146 -- If the status is TRUE then a dml statement is being issued from
147 -- within this entity api.
148 -- This function is primarily to support database triggers which
149 -- need to maintain the object_version_number for non-supported
150 -- dml statements (i.e. dml statement issued outside of the api layer).
151 --
152 -- Prerequisites:
153 -- None.
154 --
155 -- In Parameters:
156 -- None.
157 --
158 -- Post Success:
159 -- Processing continues.
160 -- If the function returns a TRUE value then, dml is being executed from
161 -- within this api.
162 --
163 -- Post Failure:
164 -- None.
165 --
166 -- Access Status:
167 -- Internal Row Handler Use Only.
168 --
169 -- {End Of Comments}
170 -- ----------------------------------------------------------------------------
171 Function return_api_dml_status Return Boolean;
172 --
173 -- ----------------------------------------------------------------------------
174 -- |---------------------------< constraint_error >---------------------------|
175 -- ----------------------------------------------------------------------------
176 -- {Start Of Comments}
177 --
178 -- Description:
179 -- This procedure is called when a constraint has been violated (i.e.
180 -- The exception hr_api.check_integrity_violated,
181 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
182 -- hr_api.unique_integrity_violated has been raised).
183 -- The exceptions can only be raised as follows:
184 -- 1) A check constraint can only be violated during an INSERT or UPDATE
185 -- dml operation.
186 -- 2) A parent integrity constraint can only be violated during an
187 -- INSERT or UPDATE dml operation.
188 -- 3) A child integrity constraint can only be violated during an
189 -- DELETE dml operation.
190 -- 4) A unique integrity constraint can only be violated during INSERT or
191 -- UPDATE dml operation.
192 --
193 -- Prerequisites:
194 -- 1) Either hr_api.check_integrity_violated,
195 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
196 -- hr_api.unique_integrity_violated has been raised with the subsequent
197 -- stripping of the constraint name from the generated error message
198 -- text.
199 -- 2) Standalone validation test which corresponds with a constraint error.
200 --
201 -- In Parameter:
202 -- p_constraint_name is in upper format and is just the constraint name
203 -- (e.g. not prefixed by brackets, schema owner etc).
204 --
205 -- Post Success:
206 -- Development dependant.
207 --
208 -- Post Failure:
209 -- Developement dependant.
210 --
211 -- Developer Implementation Notes:
212 -- For each constraint being checked the hr system package failure message
213 -- has been generated as a template only. These system error messages should
214 -- be modified as required (i.e. change the system failure message to a user
215 -- friendly defined error message).
216 --
217 -- Access Status:
218 -- Internal Development Use Only.
219 --
220 -- {End Of Comments}
221 -- ----------------------------------------------------------------------------
222 Procedure constraint_error
223 (p_constraint_name in all_constraints.constraint_name%TYPE);
224 --
225 -- ----------------------------------------------------------------------------
226 -- |-----------------------------< api_updating >-----------------------------|
227 -- ----------------------------------------------------------------------------
228 -- {Start Of Comments}
229 --
230 -- Description:
231 -- This function is used to populate the g_old_rec record with the
232 -- current row from the database for the specified primary key
233 -- provided that the primary key exists and is valid and does not
234 -- already match the current g_old_rec. The function will always return
235 -- a TRUE value if the g_old_rec is populated with the current row.
236 -- A FALSE value will be returned if all of the primary key arguments
237 -- are null.
238 --
239 -- Prerequisites:
240 -- None.
241 --
242 -- In Parameters:
243 --
244 -- Post Success:
245 -- A value of TRUE will be returned indiciating that the g_old_rec
246 -- is current.
247 -- A value of FALSE will be returned if all of the primary key arguments
248 -- have a null value (this indicates that the row has not be inserted into
249 -- the Schema), and therefore could never have a corresponding row.
250 --
251 -- Post Failure:
252 -- A failure can only occur under two circumstances:
253 -- 1) The primary key is invalid (i.e. a row does not exist for the
254 -- specified primary key values).
255 -- 2) If an object_version_number exists but is NOT the same as the current
256 -- g_old_rec value.
257 --
258 -- Developer Implementation Notes:
259 -- None.
260 --
261 -- Access Status:
262 -- Internal Development Use Only.
263 --
264 -- {End Of Comments}
265 -- ----------------------------------------------------------------------------
266 Function api_updating
267 (p_effective_date in date
268 ,p_payroll_id in number
269 ,p_object_version_number in number
270 ) Return Boolean;
271 --
272 -- ----------------------------------------------------------------------------
273 -- |---------------------------< find_dt_upd_modes >--------------------------|
274 -- ----------------------------------------------------------------------------
275 -- {Start Of Comments}
276 --
277 -- Description:
278 -- This procedure is used to determine what datetrack update modes are
279 -- allowed as of the effective date for this entity. The procedure will
280 -- return a corresponding Boolean value for each of the update modes
281 -- available where TRUE indicates that the corresponding update mode
282 -- is available.
283 --
284 -- Prerequisites:
285 -- None.
286 --
287 -- In Parameters:
288 -- p_effective_date
289 -- Specifies the date at which the datetrack modes will be operated on.
290 -- p_base_key_value
291 -- Specifies the primary key value for this datetrack entity.
292 -- (E.g. For this entity the assignment of the argument would be:
293 -- p_base_key_value = :payroll_id).
294 --
295 -- Post Success:
296 -- Processing continues.
297 --
298 -- Post Failure:
299 -- Failure might occur if for the specified effective date and primary key
300 -- value a row doesn't exist.
301 --
302 -- Developer Implementation Notes:
303 -- This procedure could require changes if this entity has any sepcific
304 -- delete restrictions.
305 -- For example, this entity might disallow the datetrack update mode of
306 -- UPDATE. To implement this you would have to set and return a Boolean
307 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
308 --
309 -- Access Status:
310 -- Internal Development Use Only.
311 --
312 -- {End Of Comments}
313 -- ----------------------------------------------------------------------------
314 Procedure find_dt_upd_modes
315 (p_effective_date in date
316 ,p_base_key_value in number
317 ,p_correction out nocopy boolean
318 ,p_update out nocopy boolean
319 ,p_update_override out nocopy boolean
320 ,p_update_change_insert out nocopy boolean
321 );
322 --
323 -- ----------------------------------------------------------------------------
324 -- |---------------------------< find_dt_del_modes >--------------------------|
325 -- ----------------------------------------------------------------------------
326 -- {Start Of Comments}
327 --
328 -- Description:
329 -- This procedure is used to determine what datetrack delete modes are
330 -- allowed as of the effective date for this entity. The procedure will
331 -- return a corresponding Boolean value for each of the delete modes
332 -- available where TRUE indicates that the corresponding delete mode is
333 -- available.
334 --
335 -- Prerequisites:
336 -- None.
337 --
338 -- In Parameters:
339 -- p_effective_date
340 -- Specifies the date at which the datetrack modes will be operated on.
341 -- p_base_key_value
342 -- Specifies the primary key value for this datetrack entity.
343 -- (E.g. For this entity the assignment of the argument would be:
344 -- p_base_key_value = :payroll_id).
345 --
346 -- Post Success:
347 -- Processing continues.
348 --
349 -- Post Failure:
350 -- Failure might occur if for the specified effective date and primary key
351 -- value a row doesn't exist.
352 --
353 -- Developer Implementation Notes:
354 -- This procedure could require changes if this entity has any sepcific
355 -- delete restrictions.
356 -- For example, this entity might disallow the datetrack delete mode of
357 -- ZAP. To implement this you would have to set and return a Boolean value
358 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
359 --
360 -- Access Status:
361 -- Internal Development Use Only.
362 --
363 -- {End Of Comments}
364 -- ----------------------------------------------------------------------------
365 Procedure find_dt_del_modes
366 (p_effective_date in date
367 ,p_base_key_value in number
368 ,p_zap out nocopy boolean
369 ,p_delete out nocopy boolean
370 ,p_future_change out nocopy boolean
371 ,p_delete_next_change out nocopy boolean
372 );
373 --
374 -- ----------------------------------------------------------------------------
375 -- |-----------------------< upd_effective_end_date >-------------------------|
376 -- ----------------------------------------------------------------------------
377 -- {Start Of Comments}
378 --
379 -- Description:
380 -- This procedure will update the specified datetrack row with the
381 -- specified new effective end date. The object version number is also
382 -- set to the next object version number. DateTrack modes which call
383 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
384 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
385 -- This is an internal datetrack maintenance procedure which should
386 -- not be modified in anyway.
387 --
388 -- Prerequisites:
389 -- None.
390 --
391 -- In Parameters:
392 -- p_new_effective_end_date
393 -- Specifies the new effective end date which will be set for the
394 -- row as of the effective date.
395 -- p_base_key_value
396 -- Specifies the primary key value for this datetrack entity.
397 -- (E.g. For this entity the assignment of the argument would be:
398 -- p_base_key_value = :payroll_id).
399 --
400 -- Post Success:
401 -- The specified row will be updated with the new effective end date and
402 -- object_version_number.
403 --
404 -- Post Failure:
405 -- Failure might occur if for the specified effective date and primary key
406 -- value a row doesn't exist.
407 --
408 -- Developer Implementation Notes:
409 -- This is an internal datetrack maintenance procedure which should
410 -- not be modified in anyway.
411 --
412 -- Access Status:
413 -- Internal Row Handler Use Only.
414 --
415 -- {End Of Comments}
416 -- ----------------------------------------------------------------------------
417 Procedure upd_effective_end_date
418 (p_effective_date in date
419 ,p_base_key_value in number
420 ,p_new_effective_end_date in date
421 ,p_validation_start_date in date
422 ,p_validation_end_date in date
423 ,p_object_version_number out nocopy number
424 );
425 --
426 --
427 --
428 -- ----------------------------------------------------------------------------
429 -- |---------------------------------< lck >----------------------------------|
430 -- ----------------------------------------------------------------------------
431 -- {Start Of Comments}
432 --
433 -- Description:
434 -- The Lck process for datetrack is complicated and comprises of the
435 -- following processing
436 -- The processing steps are as follows:
437 -- 1) The row to be updated or deleted must be locked.
438 -- By locking this row, the g_old_rec record data type is populated.
439 -- 2) If a comment exists the text is selected from hr_comments.
440 -- 3) The datetrack mode is then validated to ensure the operation is
441 -- valid. If the mode is valid the validation start and end dates for
442 -- the mode will be derived and returned. Any required locking is
443 -- completed when the datetrack mode is validated.
444 --
445 -- Prerequisites:
446 -- When attempting to call the lck procedure the object version number,
447 -- primary key, effective date and datetrack mode must be specified.
448 --
449 -- In Parameters:
450 -- p_effective_date
451 -- Specifies the date of the datetrack update operation.
452 -- p_datetrack_mode
453 -- Determines the datetrack update or delete mode.
454 --
455 -- Post Success:
456 -- On successful completion of the Lck process the row to be updated or
457 -- deleted will be locked and selected into the global data structure
458 -- g_old_rec.
459 --
460 -- Post Failure:
461 -- The Lck process can fail for three reasons:
462 -- 1) When attempting to lock the row the row could already be locked by
463 -- another user. This will raise the HR_Api.Object_Locked exception.
464 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
465 -- This error is trapped and reported using the message name
466 -- 'HR_7220_INVALID_PRIMARY_KEY'.
467 -- 3) The row although existing in the HR Schema has a different object
468 -- version number than the object version number specified.
469 -- This error is trapped and reported using the message name
470 -- 'HR_7155_OBJECT_INVALID'.
471 --
472 -- Developer Implementation Notes:
473 -- None.
474 --
475 -- Access Status:
476 -- Internal Development Use Only.
477 --
478 -- {End Of Comments}
479 -- ----------------------------------------------------------------------------
480 Procedure lck
481 (p_effective_date in date
482 ,p_datetrack_mode in varchar2
483 ,p_payroll_id in number
484 ,p_object_version_number in number
485 ,p_validation_start_date out nocopy date
486 ,p_validation_end_date out nocopy date
487 );
488 --
489 -- ----------------------------------------------------------------------------
490 -- |-----------------------------< convert_args >-----------------------------|
491 -- ----------------------------------------------------------------------------
492 -- {Start Of Comments}
493 --
494 -- Description:
495 -- This function is used to turn attribute parameters into the record
496 -- structure parameter g_rec_type.
497 --
498 -- Prerequisites:
499 -- This is a private function and can only be called from the ins or upd
500 -- attribute processes.
501 --
502 -- In Parameters:
503 --
504 -- Post Success:
505 -- A returning record structure will be returned.
506 --
507 -- Post Failure:
508 -- No direct error handling is required within this function. Any possible
509 -- errors within this function will be a PL/SQL value error due to
510 -- conversion of datatypes or data lengths.
511 --
512 -- Developer Implementation Notes:
513 -- None.
514 --
515 -- Access Status:
516 -- Internal Row Handler Use Only.
517 --
518 -- {End Of Comments}
519 -- ----------------------------------------------------------------------------
520 Function convert_args
521 (p_payroll_id in number
522 ,p_effective_start_date in date
523 ,p_effective_end_date in date
524 ,p_default_payment_method_id in number
525 ,p_business_group_id in number
526 ,p_consolidation_set_id in number
527 ,p_cost_allocation_keyflex_id in number
528 ,p_suspense_account_keyflex_id in number
529 ,p_gl_set_of_books_id in number
530 ,p_soft_coding_keyflex_id in number
531 ,p_period_type in varchar2
532 ,p_organization_id in number
533 ,p_cut_off_date_offset in number
534 ,p_direct_deposit_date_offset in number
535 ,p_first_period_end_date in date
536 ,p_negative_pay_allowed_flag in varchar2
537 ,p_number_of_years in number
538 ,p_pay_advice_date_offset in number
539 ,p_pay_date_offset in number
540 ,p_payroll_name in varchar2
541 ,p_workload_shifting_level in varchar2
542 ,p_comment_id in number
543 ,p_comments in varchar2
544 ,p_midpoint_offset in number
545 ,p_attribute_category in varchar2
549 ,p_attribute4 in varchar2
546 ,p_attribute1 in varchar2
547 ,p_attribute2 in varchar2
548 ,p_attribute3 in varchar2
550 ,p_attribute5 in varchar2
551 ,p_attribute6 in varchar2
552 ,p_attribute7 in varchar2
553 ,p_attribute8 in varchar2
554 ,p_attribute9 in varchar2
555 ,p_attribute10 in varchar2
556 ,p_attribute11 in varchar2
557 ,p_attribute12 in varchar2
558 ,p_attribute13 in varchar2
559 ,p_attribute14 in varchar2
560 ,p_attribute15 in varchar2
561 ,p_attribute16 in varchar2
562 ,p_attribute17 in varchar2
563 ,p_attribute18 in varchar2
564 ,p_attribute19 in varchar2
565 ,p_attribute20 in varchar2
566 ,p_arrears_flag in varchar2
567 ,p_payroll_type in varchar2
568 ,p_prl_information_category in varchar2
569 ,p_prl_information1 in varchar2
570 ,p_prl_information2 in varchar2
571 ,p_prl_information3 in varchar2
572 ,p_prl_information4 in varchar2
573 ,p_prl_information5 in varchar2
574 ,p_prl_information6 in varchar2
575 ,p_prl_information7 in varchar2
576 ,p_prl_information8 in varchar2
577 ,p_prl_information9 in varchar2
578 ,p_prl_information10 in varchar2
579 ,p_prl_information11 in varchar2
580 ,p_prl_information12 in varchar2
581 ,p_prl_information13 in varchar2
582 ,p_prl_information14 in varchar2
583 ,p_prl_information15 in varchar2
584 ,p_prl_information16 in varchar2
585 ,p_prl_information17 in varchar2
586 ,p_prl_information18 in varchar2
587 ,p_prl_information19 in varchar2
588 ,p_prl_information20 in varchar2
589 ,p_prl_information21 in varchar2
590 ,p_prl_information22 in varchar2
591 ,p_prl_information23 in varchar2
592 ,p_prl_information24 in varchar2
593 ,p_prl_information25 in varchar2
594 ,p_prl_information26 in varchar2
595 ,p_prl_information27 in varchar2
596 ,p_prl_information28 in varchar2
597 ,p_prl_information29 in varchar2
598 ,p_prl_information30 in varchar2
599 ,p_multi_assignments_flag in varchar2
600 ,p_period_reset_years in varchar2
601 ,p_object_version_number in number
602
603 ,p_payslip_view_date_offset in number
604 )
605 Return g_rec_type;
606 --
607 end pay_pay_shd;
[Home] [Help]