1 Package pay_ipd_shd AUTHID CURRENT_USER as
2 /* $Header: pyipdrhi.pkh 120.1 2011/11/09 12:39:23 rsahai ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (paye_details_id number(10)
10 ,effective_start_date date
11 ,effective_end_date date
12 ,object_version_number number(9)
13 ,assignment_id number(10)
14 ,info_source varchar2(30)
15 ,comm_period_no number(10)
16 ,tax_basis varchar2(30)
17 ,certificate_start_date date
18 ,certificate_end_date date
19 ,tax_assess_basis varchar2(30)
20 ,weekly_tax_credit number
21 ,weekly_std_rate_cut_off number
22 ,monthly_tax_credit number
23 ,monthly_std_rate_cut_off number
24 ,request_id number(15)
25 ,program_application_id number(15)
26 ,program_id number(15)
27 ,program_update_date date
28 ,certificate_issue_date date
29 --13359423
30 ,yrly_tax_cred number
31 ,yrly_tax_rate_1 number
32 ,yrly_tax_rate_2 number
33 ,mthly_tax_rate_2 number
34 ,wkly_tax_rate_2 number
35 ,tax_rate_3 number
36 ,yrly_tax_rate_3 number
37 ,mthly_tax_rate_3 number
38 ,wkly_tax_rate_3 number
39 ,tax_rate_4 number
40 ,yrly_tax_rate_4 number
41 ,mthly_tax_rate_4 number
42 ,wkly_tax_rate_4 number
43 ,tax_rate_5 number
44 ,in_exempt_usc varchar2(1)
45 ,total_usc_pay_todate number
46 ,total_usc_tax_todate number
47 ,usc_rate_1 number
48 ,usc_yrly_cutoff_1 number
49 ,usc_mthly_cutoff_1 number
50 ,usc_wkly_cutoff_1 number
51 ,usc_rate_2 number
52 ,usc_yrly_cutoff_2 number
53 ,usc_mthly_cutoff_2 number
54 ,usc_wkly_cutoff_2 number
55 ,usc_rate_3 number
56 ,usc_yrly_cutoff_3 number
57 ,usc_mthly_cutoff_3 number
58 ,usc_wkly_cutoff_3 number
59 ,usc_rate_4 number
60 ,usc_yrly_cutoff_4 number
61 ,usc_mthly_cutoff_4 number
62 ,usc_wkly_cutoff_4 number
63 ,usc_rate_5 number
64 ,usc_tax_basis varchar2(30)
65 ,usc_info_source varchar2(30)
66 --13359423
67 );
68 --
69 -- ----------------------------------------------------------------------------
70 -- | Global Definitions - Internal Development Use Only |
71 -- ----------------------------------------------------------------------------
72 --
73 g_old_rec g_rec_type; -- Global record definition
74 --
75 -- ----------------------------------------------------------------------------
76 -- |---------------------------< constraint_error >---------------------------|
77 -- ----------------------------------------------------------------------------
78 -- {Start Of Comments}
79 --
80 -- Description:
81 -- This procedure is called when a constraint has been violated (i.e.
82 -- The exception hr_api.check_integrity_violated,
83 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
84 -- hr_api.unique_integrity_violated has been raised).
85 -- The exceptions can only be raised as follows:
86 -- 1) A check constraint can only be violated during an INSERT or UPDATE
87 -- dml operation.
88 -- 2) A parent integrity constraint can only be violated during an
89 -- INSERT or UPDATE dml operation.
90 -- 3) A child integrity constraint can only be violated during an
91 -- DELETE dml operation.
92 -- 4) A unique integrity constraint can only be violated during INSERT or
93 -- UPDATE dml operation.
94 --
95 -- Prerequisites:
96 -- 1) Either hr_api.check_integrity_violated,
97 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
98 -- hr_api.unique_integrity_violated has been raised with the subsequent
99 -- stripping of the constraint name from the generated error message
100 -- text.
101 -- 2) Standalone validation test which corresponds with a constraint error.
102 --
103 -- In Parameter:
104 -- p_constraint_name is in upper format and is just the constraint name
105 -- (e.g. not prefixed by brackets, schema owner etc).
106 --
107 -- Post Success:
108 -- Development dependant.
109 --
110 -- Post Failure:
111 -- Developement dependant.
112 --
113 -- Developer Implementation Notes:
114 -- For each constraint being checked the hr system package failure message
115 -- has been generated as a template only. These system error messages should
116 -- be modified as required (i.e. change the system failure message to a user
117 -- friendly defined error message).
118 --
119 -- Access Status:
120 -- Internal Development Use Only.
121 --
122 -- {End Of Comments}
123 -- ----------------------------------------------------------------------------
124 Procedure constraint_error
125 (p_constraint_name in all_constraints.constraint_name%TYPE);
126 --
127 -- ----------------------------------------------------------------------------
128 -- |-----------------------------< api_updating >-----------------------------|
129 -- ----------------------------------------------------------------------------
130 -- {Start Of Comments}
131 --
132 -- Description:
133 -- This function is used to populate the g_old_rec record with the
134 -- current row from the database for the specified primary key
135 -- provided that the primary key exists and is valid and does not
136 -- already match the current g_old_rec. The function will always return
137 -- a TRUE value if the g_old_rec is populated with the current row.
138 -- A FALSE value will be returned if all of the primary key arguments
139 -- are null.
140 --
141 -- Prerequisites:
142 -- None.
143 --
144 -- In Parameters:
145 --
146 -- Post Success:
147 -- A value of TRUE will be returned indiciating that the g_old_rec
148 -- is current.
149 -- A value of FALSE will be returned if all of the primary key arguments
150 -- have a null value (this indicates that the row has not be inserted into
151 -- the Schema), and therefore could never have a corresponding row.
152 --
153 -- Post Failure:
154 -- A failure can only occur under two circumstances:
155 -- 1) The primary key is invalid (i.e. a row does not exist for the
156 -- specified primary key values).
157 -- 2) If an object_version_number exists but is NOT the same as the current
158 -- g_old_rec value.
159 --
160 -- Developer Implementation Notes:
161 -- None.
162 --
163 -- Access Status:
164 -- Internal Development Use Only.
165 --
166 -- {End Of Comments}
167 -- ----------------------------------------------------------------------------
168 Function api_updating
169 (p_effective_date in date
170 ,p_paye_details_id in number
171 ,p_object_version_number in number
172 ) Return Boolean;
173 --
174 -- ----------------------------------------------------------------------------
175 -- |---------------------------< find_dt_upd_modes >--------------------------|
176 -- ----------------------------------------------------------------------------
177 -- {Start Of Comments}
178 --
179 -- Description:
180 -- This procedure is used to determine what datetrack update modes are
181 -- allowed as of the effective date for this entity. The procedure will
182 -- return a corresponding Boolean value for each of the update modes
183 -- available where TRUE indicates that the corresponding update mode
184 -- is available.
185 --
186 -- Prerequisites:
187 -- None.
188 --
189 -- In Parameters:
190 -- p_effective_date
191 -- Specifies the date at which the datetrack modes will be operated on.
192 -- p_base_key_value
193 -- Specifies the primary key value for this datetrack entity.
194 -- (E.g. For this entity the assignment of the argument would be:
195 -- p_base_key_value = :paye_details_id).
196 --
197 -- Post Success:
198 -- Processing continues.
199 --
200 -- Post Failure:
201 -- Failure might occur if for the specified effective date and primary key
202 -- value a row doesn't exist.
203 --
204 -- Developer Implementation Notes:
205 -- This procedure could require changes if this entity has any sepcific
206 -- delete restrictions.
207 -- For example, this entity might disallow the datetrack update mode of
208 -- UPDATE. To implement this you would have to set and return a Boolean
209 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
210 --
211 -- Access Status:
212 -- Internal Development Use Only.
213 --
214 -- {End Of Comments}
215 -- ----------------------------------------------------------------------------
216 Procedure find_dt_upd_modes
217 (p_effective_date in date
218 ,p_base_key_value in number
219 ,p_correction out nocopy boolean
220 ,p_update out nocopy boolean
221 ,p_update_override out nocopy boolean
222 ,p_update_change_insert out nocopy boolean
223 );
224 --
225 -- ----------------------------------------------------------------------------
226 -- |---------------------------< find_dt_del_modes >--------------------------|
227 -- ----------------------------------------------------------------------------
228 -- {Start Of Comments}
229 --
230 -- Description:
231 -- This procedure is used to determine what datetrack delete modes are
232 -- allowed as of the effective date for this entity. The procedure will
233 -- return a corresponding Boolean value for each of the delete modes
234 -- available where TRUE indicates that the corresponding delete mode is
235 -- available.
236 --
237 -- Prerequisites:
238 -- None.
239 --
240 -- In Parameters:
241 -- p_effective_date
242 -- Specifies the date at which the datetrack modes will be operated on.
243 -- p_base_key_value
244 -- Specifies the primary key value for this datetrack entity.
245 -- (E.g. For this entity the assignment of the argument would be:
246 -- p_base_key_value = :paye_details_id).
247 --
248 -- Post Success:
249 -- Processing continues.
250 --
251 -- Post Failure:
252 -- Failure might occur if for the specified effective date and primary key
253 -- value a row doesn't exist.
254 --
255 -- Developer Implementation Notes:
256 -- This procedure could require changes if this entity has any sepcific
257 -- delete restrictions.
258 -- For example, this entity might disallow the datetrack delete mode of
259 -- ZAP. To implement this you would have to set and return a Boolean value
260 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
261 --
262 -- Access Status:
263 -- Internal Development Use Only.
264 --
265 -- {End Of Comments}
266 -- ----------------------------------------------------------------------------
267 Procedure find_dt_del_modes
268 (p_effective_date in date
269 ,p_base_key_value in number
270 ,p_zap out nocopy boolean
271 ,p_delete out nocopy boolean
272 ,p_future_change out nocopy boolean
273 ,p_delete_next_change out nocopy boolean
274 );
275 --
276 -- ----------------------------------------------------------------------------
277 -- |-----------------------< upd_effective_end_date >-------------------------|
278 -- ----------------------------------------------------------------------------
279 -- {Start Of Comments}
280 --
281 -- Description:
282 -- This procedure will update the specified datetrack row with the
283 -- specified new effective end date. The object version number is also
284 -- set to the next object version number. DateTrack modes which call
285 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
286 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
287 -- This is an internal datetrack maintenance procedure which should
288 -- not be modified in anyway.
289 --
290 -- Prerequisites:
291 -- None.
292 --
293 -- In Parameters:
294 -- p_new_effective_end_date
295 -- Specifies the new effective end date which will be set for the
296 -- row as of the effective date.
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 = :paye_details_id).
301 --
302 -- Post Success:
303 -- The specified row will be updated with the new effective end date and
304 -- object_version_number.
305 --
306 -- Post Failure:
307 -- Failure might occur if for the specified effective date and primary key
308 -- value a row doesn't exist.
309 --
310 -- Developer Implementation Notes:
311 -- This is an internal datetrack maintenance procedure which should
312 -- not be modified in anyway.
313 --
314 -- Access Status:
315 -- Internal Row Handler Use Only.
316 --
317 -- {End Of Comments}
318 -- ----------------------------------------------------------------------------
319 Procedure upd_effective_end_date
320 (p_effective_date in date
321 ,p_base_key_value in number
322 ,p_new_effective_end_date in date
323 ,p_validation_start_date in date
324 ,p_validation_end_date in date
325 ,p_object_version_number out nocopy number
326 );
327 --
328 -- ----------------------------------------------------------------------------
329 -- |---------------------------------< lck >----------------------------------|
330 -- ----------------------------------------------------------------------------
331 -- {Start Of Comments}
332 --
333 -- Description:
334 -- The Lck process for datetrack is complicated and comprises of the
335 -- following processing
336 -- The processing steps are as follows:
337 -- 1) The row to be updated or deleted must be locked.
338 -- By locking this row, the g_old_rec record data type is populated.
339 -- 2) If a comment exists the text is selected from hr_comments.
340 -- 3) The datetrack mode is then validated to ensure the operation is
344 --
341 -- valid. If the mode is valid the validation start and end dates for
342 -- the mode will be derived and returned. Any required locking is
343 -- completed when the datetrack mode is validated.
345 -- Prerequisites:
346 -- When attempting to call the lck procedure the object version number,
347 -- primary key, effective date and datetrack mode must be specified.
348 --
349 -- In Parameters:
350 -- p_effective_date
351 -- Specifies the date of the datetrack update operation.
352 -- p_datetrack_mode
353 -- Determines the datetrack update or delete mode.
354 --
355 -- Post Success:
356 -- On successful completion of the Lck process the row to be updated or
357 -- deleted will be locked and selected into the global data structure
358 -- g_old_rec.
359 --
360 -- Post Failure:
361 -- The Lck process can fail for three reasons:
362 -- 1) When attempting to lock the row the row could already be locked by
363 -- another user. This will raise the HR_Api.Object_Locked exception.
364 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
365 -- This error is trapped and reported using the message name
366 -- 'HR_7220_INVALID_PRIMARY_KEY'.
367 -- 3) The row although existing in the HR Schema has a different object
368 -- version number than the object version number specified.
369 -- This error is trapped and reported using the message name
370 -- 'HR_7155_OBJECT_INVALID'.
371 --
372 -- Developer Implementation Notes:
373 -- None.
374 --
375 -- Access Status:
376 -- Internal Development Use Only.
377 --
378 -- {End Of Comments}
379 -- ----------------------------------------------------------------------------
380 Procedure lck
381 (p_effective_date in date
382 ,p_datetrack_mode in varchar2
383 ,p_paye_details_id in number
384 ,p_object_version_number in number
385 ,p_validation_start_date out nocopy date
386 ,p_validation_end_date out nocopy date
387 );
388 --
389 -- ----------------------------------------------------------------------------
390 -- |-----------------------------< convert_args >-----------------------------|
391 -- ----------------------------------------------------------------------------
392 -- {Start Of Comments}
393 --
394 -- Description:
395 -- This function is used to turn attribute parameters into the record
396 -- structure parameter g_rec_type.
397 --
398 -- Prerequisites:
399 -- This is a private function and can only be called from the ins or upd
400 -- attribute processes.
401 --
402 -- In Parameters:
403 --
404 -- Post Success:
405 -- A returning record structure will be returned.
406 --
407 -- Post Failure:
408 -- No direct error handling is required within this function. Any possible
409 -- errors within this function will be a PL/SQL value error due to
410 -- conversion of datatypes or data lengths.
411 --
412 -- Developer Implementation Notes:
413 -- None.
414 --
415 -- Access Status:
416 -- Internal Row Handler Use Only.
417 --
418 -- {End Of Comments}
419 -- ----------------------------------------------------------------------------
420 Function convert_args
421 (p_paye_details_id in number
422 ,p_effective_start_date in date
423 ,p_effective_end_date in date
424 ,p_object_version_number in number
425 ,p_assignment_id in number
426 ,p_info_source in varchar2
427 ,p_comm_period_no in number
428 ,p_tax_basis in varchar2
429 ,p_certificate_start_date in date
430 ,p_certificate_end_date in date
431 ,p_tax_assess_basis in varchar2
432 ,p_weekly_tax_credit in number
433 ,p_weekly_std_rate_cut_off in number
434 ,p_monthly_tax_credit in number
435 ,p_monthly_std_rate_cut_off in number
436 ,p_request_id in number
437 ,p_program_application_id in number
438 ,p_program_id in number
439 ,p_program_update_date in date
440 ,p_certificate_issue_date in date
441 --13359423
442 ,p_yrly_tax_cred in number
443 ,p_yrly_tax_rate_1 in number
444 ,p_yrly_tax_rate_2 in number
445 ,p_mthly_tax_rate_2 in number
446 ,p_wkly_tax_rate_2 in number
447 ,p_tax_rate_3 in number
448 ,p_yrly_tax_rate_3 in number
449 ,p_mthly_tax_rate_3 in number
450 ,p_wkly_tax_rate_3 in number
451 ,p_tax_rate_4 in number
452 ,p_yrly_tax_rate_4 in number
453 ,p_mthly_tax_rate_4 in number
454 ,p_wkly_tax_rate_4 in number
455 ,p_tax_rate_5 in number
456 ,p_in_exempt_usc in varchar2
457 ,p_total_usc_pay_todate in number
458 ,p_total_usc_tax_todate in number
459 ,p_usc_rate_1 in number
460 ,p_usc_yrly_cutoff_1 in number
461 ,p_usc_mthly_cutoff_1 in number
462 ,p_usc_wkly_cutoff_1 in number
463 ,p_usc_rate_2 in number
464 ,p_usc_yrly_cutoff_2 in number
465 ,p_usc_mthly_cutoff_2 in number
466 ,p_usc_wkly_cutoff_2 in number
467 ,p_usc_rate_3 in number
468 ,p_usc_yrly_cutoff_3 in number
469 ,p_usc_mthly_cutoff_3 in number
470 ,p_usc_wkly_cutoff_3 in number
471 ,p_usc_rate_4 in number
472 ,p_usc_yrly_cutoff_4 in number
473 ,p_usc_mthly_cutoff_4 in number
477 ,p_usc_info_source in varchar2
474 ,p_usc_wkly_cutoff_4 in number
475 ,p_usc_rate_5 in number
476 ,p_usc_tax_basis in varchar2
478 --13359423
479 )
480 Return g_rec_type;
481 --
482 end pay_ipd_shd;
[Home] [Help]