1 Package pay_ppd_shd as
2 /* $Header: pyppdrhi.pkh 120.1 2006/01/02 00:35 mseshadr noship $ */
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 ,per_or_asg_id number(10)
13 ,business_group_id number(15)
14 ,contract_category varchar2(30)
15 ,object_version_number number(9)
16 ,tax_reduction varchar2(30)
17 ,tax_calc_with_spouse_child varchar2(30)
18 ,income_reduction varchar2(30)
19 ,income_reduction_amount number(14,2)
20 ,rate_of_tax varchar2(30)
21 ,program_id number(15)
22 ,program_login_id number(15)
23 ,program_application_id number(15)
24 ,request_id number(15)
25 );
26 --
27 -- ----------------------------------------------------------------------------
28 -- | Global Definitions - Internal Development Use Only |
29 -- ----------------------------------------------------------------------------
30 --
31 g_old_rec g_rec_type; -- Global record definition
32 -- Global table name
33 g_tab_nam constant varchar2(30) := 'PAY_PL_PAYE_DETAILS_F';
34 --
35 -- ----------------------------------------------------------------------------
36 -- |---------------------------< constraint_error >---------------------------|
37 -- ----------------------------------------------------------------------------
38 -- {Start Of Comments}
39 --
40 -- Description:
41 -- This procedure is called when a constraint has been violated (i.e.
42 -- The exception hr_api.check_integrity_violated,
43 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
44 -- hr_api.unique_integrity_violated has been raised).
45 -- The exceptions can only be raised as follows:
46 -- 1) A check constraint can only be violated during an INSERT or UPDATE
47 -- dml operation.
48 -- 2) A parent integrity constraint can only be violated during an
49 -- INSERT or UPDATE dml operation.
50 -- 3) A child integrity constraint can only be violated during an
51 -- DELETE dml operation.
52 -- 4) A unique integrity constraint can only be violated during INSERT or
53 -- UPDATE dml operation.
54 --
55 -- Prerequisites:
56 -- 1) Either hr_api.check_integrity_violated,
57 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
58 -- hr_api.unique_integrity_violated has been raised with the subsequent
59 -- stripping of the constraint name from the generated error message
60 -- text.
61 -- 2) Standalone validation test which corresponds with a constraint error.
62 --
63 -- In Parameter:
64 -- p_constraint_name is in upper format and is just the constraint name
65 -- (e.g. not prefixed by brackets, schema owner etc).
66 --
67 -- Post Success:
68 -- Development dependant.
69 --
70 -- Post Failure:
71 -- Developement dependant.
72 --
73 -- Developer Implementation Notes:
74 -- For each constraint being checked the hr system package failure message
75 -- has been generated as a template only. These system error messages should
76 -- be modified as required (i.e. change the system failure message to a user
77 -- friendly defined error message).
78 --
79 -- Access Status:
80 -- Internal Development Use Only.
81 --
82 -- {End Of Comments}
83 -- ----------------------------------------------------------------------------
84 Procedure constraint_error
85 (p_constraint_name in all_constraints.constraint_name%TYPE);
86 --
87 -- ----------------------------------------------------------------------------
88 -- |-----------------------------< api_updating >-----------------------------|
89 -- ----------------------------------------------------------------------------
90 -- {Start Of Comments}
91 --
92 -- Description:
93 -- This function is used to populate the g_old_rec record with the
94 -- current row from the database for the specified primary key
95 -- provided that the primary key exists and is valid and does not
96 -- already match the current g_old_rec. The function will always return
97 -- a TRUE value if the g_old_rec is populated with the current row.
98 -- A FALSE value will be returned if all of the primary key arguments
99 -- are null.
100 --
101 -- Prerequisites:
102 -- None.
103 --
104 -- In Parameters:
105 --
106 -- Post Success:
107 -- A value of TRUE will be returned indiciating that the g_old_rec
108 -- is current.
109 -- A value of FALSE will be returned if all of the primary key arguments
110 -- have a null value (this indicates that the row has not be inserted into
111 -- the Schema), and therefore could never have a corresponding row.
112 --
113 -- Post Failure:
114 -- A failure can only occur under two circumstances:
115 -- 1) The primary key is invalid (i.e. a row does not exist for the
116 -- specified primary key values).
117 -- 2) If an object_version_number exists but is NOT the same as the current
118 -- g_old_rec value.
119 --
120 -- Developer Implementation Notes:
121 -- None.
122 --
123 -- Access Status:
124 -- Internal Development Use Only.
125 --
126 -- {End Of Comments}
127 -- ----------------------------------------------------------------------------
128 Function api_updating
129 (p_effective_date in date
130 ,p_paye_details_id in number
131 ,p_object_version_number in number
132 ) Return Boolean;
133 --
134 -- ----------------------------------------------------------------------------
135 -- |---------------------------< find_dt_upd_modes >--------------------------|
136 -- ----------------------------------------------------------------------------
137 -- {Start Of Comments}
138 --
139 -- Description:
140 -- This procedure is used to determine what datetrack update modes are
141 -- allowed as of the effective date for this entity. The procedure will
142 -- return a corresponding Boolean value for each of the update modes
143 -- available where TRUE indicates that the corresponding update mode
144 -- is available.
145 --
146 -- Prerequisites:
147 -- None.
148 --
149 -- In Parameters:
150 -- p_effective_date
151 -- Specifies the date at which the datetrack modes will be operated on.
152 -- p_base_key_value
153 -- Specifies the primary key value for this datetrack entity.
154 -- (E.g. For this entity the assignment of the argument would be:
155 -- p_base_key_value = :paye_details_id).
156 --
157 -- Post Success:
158 -- Processing continues.
159 --
160 -- Post Failure:
161 -- Failure might occur if for the specified effective date and primary key
162 -- value a row doesn't exist.
163 --
164 -- Developer Implementation Notes:
165 -- This procedure could require changes if this entity has any sepcific
166 -- delete restrictions.
167 -- For example, this entity might disallow the datetrack update mode of
168 -- UPDATE. To implement this you would have to set and return a Boolean
169 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
170 --
171 -- Access Status:
172 -- Internal Development Use Only.
173 --
174 -- {End Of Comments}
175 -- ----------------------------------------------------------------------------
176 Procedure find_dt_upd_modes
177 (p_effective_date in date
178 ,p_base_key_value in number
179 ,p_correction out nocopy boolean
180 ,p_update out nocopy boolean
181 ,p_update_override out nocopy boolean
182 ,p_update_change_insert out nocopy boolean
183 ,p_correction_start_date out nocopy date --Added since Self Service DT team calling with more number of parameters
184 ,p_correction_end_date out nocopy date
185 ,p_update_start_date out nocopy date
186 ,p_update_end_date out nocopy date
187 ,p_override_start_date out nocopy date
188 ,p_override_end_date out nocopy date
189 ,p_upd_chg_start_date out nocopy date
190 ,p_upd_chg_end_date out nocopy date
191 );
192 --
193 -- ----------------------------------------------------------------------------
194 -- |---------------------------< find_dt_del_modes >--------------------------|
195 -- ----------------------------------------------------------------------------
196 -- {Start Of Comments}
197 --
198 -- Description:
199 -- This procedure is used to determine what datetrack delete modes are
200 -- allowed as of the effective date for this entity. The procedure will
201 -- return a corresponding Boolean value for each of the delete modes
202 -- available where TRUE indicates that the corresponding delete mode is
203 -- available.
204 --
205 -- Prerequisites:
206 -- None.
207 --
208 -- In Parameters:
209 -- p_effective_date
210 -- Specifies the date at which the datetrack modes will be operated on.
211 -- p_base_key_value
212 -- Specifies the primary key value for this datetrack entity.
213 -- (E.g. For this entity the assignment of the argument would be:
214 -- p_base_key_value = :paye_details_id).
215 --
216 -- Post Success:
217 -- Processing continues.
218 --
219 -- Post Failure:
220 -- Failure might occur if for the specified effective date and primary key
221 -- value a row doesn't exist.
222 --
223 -- Developer Implementation Notes:
224 -- This procedure could require changes if this entity has any sepcific
225 -- delete restrictions.
226 -- For example, this entity might disallow the datetrack delete mode of
227 -- ZAP. To implement this you would have to set and return a Boolean value
228 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
229 --
230 -- Access Status:
231 -- Internal Development Use Only.
232 --
233 -- {End Of Comments}
234 -- ----------------------------------------------------------------------------
235 Procedure find_dt_del_modes
236 (p_effective_date in date
237 ,p_base_key_value in number
238 ,p_zap out nocopy boolean
239 ,p_delete out nocopy boolean
240 ,p_future_change out nocopy boolean
241 ,p_delete_next_change out nocopy boolean
242 );
243 --
244 -- ----------------------------------------------------------------------------
245 -- |-----------------------< upd_effective_end_date >-------------------------|
246 -- ----------------------------------------------------------------------------
247 -- {Start Of Comments}
248 --
249 -- Description:
250 -- This procedure will update the specified datetrack row with the
251 -- specified new effective end date. The object version number is also
252 -- set to the next object version number. DateTrack modes which call
253 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
254 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
255 -- This is an internal datetrack maintenance procedure which should
256 -- not be modified in anyway.
257 --
258 -- Prerequisites:
259 -- None.
260 --
261 -- In Parameters:
262 -- p_new_effective_end_date
263 -- Specifies the new effective end date which will be set for the
264 -- row as of the effective date.
265 -- p_base_key_value
266 -- Specifies the primary key value for this datetrack entity.
267 -- (E.g. For this entity the assignment of the argument would be:
268 -- p_base_key_value = :paye_details_id).
269 --
270 -- Post Success:
271 -- The specified row will be updated with the new effective end date and
272 -- object_version_number.
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 is an internal datetrack maintenance procedure which should
280 -- not be modified in anyway.
281 --
282 -- Access Status:
283 -- Internal Row Handler Use Only.
284 --
285 -- {End Of Comments}
286 -- ----------------------------------------------------------------------------
287 Procedure upd_effective_end_date
288 (p_effective_date in date
289 ,p_base_key_value in number
290 ,p_new_effective_end_date in date
291 ,p_validation_start_date in date
292 ,p_validation_end_date in date
293 ,p_object_version_number out nocopy number
294 );
295 --
296 -- ----------------------------------------------------------------------------
297 -- |---------------------------------< lck >----------------------------------|
298 -- ----------------------------------------------------------------------------
299 -- {Start Of Comments}
300 --
301 -- Description:
302 -- The Lck process for datetrack is complicated and comprises of the
303 -- following processing
304 -- The processing steps are as follows:
305 -- 1) The row to be updated or deleted must be locked.
306 -- By locking this row, the g_old_rec record data type is populated.
307 -- 2) If a comment exists the text is selected from hr_comments.
308 -- 3) The datetrack mode is then validated to ensure the operation is
309 -- valid. If the mode is valid the validation start and end dates for
310 -- the mode will be derived and returned. Any required locking is
311 -- completed when the datetrack mode is validated.
312 --
313 -- Prerequisites:
314 -- When attempting to call the lck procedure the object version number,
315 -- primary key, effective date and datetrack mode must be specified.
316 --
317 -- In Parameters:
318 -- p_effective_date
319 -- Specifies the date of the datetrack update operation.
320 -- p_datetrack_mode
321 -- Determines the datetrack update or delete mode.
322 --
323 -- Post Success:
324 -- On successful completion of the Lck process the row to be updated or
325 -- deleted will be locked and selected into the global data structure
326 -- g_old_rec.
327 --
328 -- Post Failure:
329 -- The Lck process can fail for three reasons:
330 -- 1) When attempting to lock the row the row could already be locked by
331 -- another user. This will raise the HR_Api.Object_Locked exception.
332 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
333 -- This error is trapped and reported using the message name
334 -- 'HR_7220_INVALID_PRIMARY_KEY'.
335 -- 3) The row although existing in the HR Schema has a different object
336 -- version number than the object version number specified.
337 -- This error is trapped and reported using the message name
338 -- 'HR_7155_OBJECT_INVALID'.
339 --
340 -- Developer Implementation Notes:
341 -- None.
342 --
343 -- Access Status:
344 -- Internal Development Use Only.
345 --
346 -- {End Of Comments}
347 -- ----------------------------------------------------------------------------
348 Procedure lck
349 (p_effective_date in date
350 ,p_datetrack_mode in varchar2
351 ,p_paye_details_id in number
352 ,p_object_version_number in number
353 ,p_validation_start_date out nocopy date
354 ,p_validation_end_date out nocopy date
355 );
356 --
357 -- ----------------------------------------------------------------------------
358 -- |-----------------------------< convert_args >-----------------------------|
359 -- ----------------------------------------------------------------------------
360 -- {Start Of Comments}
361 --
362 -- Description:
363 -- This function is used to turn attribute parameters into the record
364 -- structure parameter g_rec_type.
365 --
366 -- Prerequisites:
367 -- This is a private function and can only be called from the ins or upd
368 -- attribute processes.
369 --
370 -- In Parameters:
371 --
372 -- Post Success:
373 -- A returning record structure will be returned.
374 --
375 -- Post Failure:
376 -- No direct error handling is required within this function. Any possible
377 -- errors within this function will be a PL/SQL value error due to
378 -- conversion of datatypes or data lengths.
379 --
380 -- Developer Implementation Notes:
381 -- None.
382 --
383 -- Access Status:
384 -- Internal Row Handler Use Only.
385 --
386 -- {End Of Comments}
387 -- ----------------------------------------------------------------------------
388 Function convert_args
389 (p_paye_details_id in number
390 ,p_effective_start_date in date
391 ,p_effective_end_date in date
392 ,p_per_or_asg_id in number
393 ,p_business_group_id in number
394 ,p_contract_category in varchar2
395 ,p_object_version_number in number
396 ,p_tax_reduction in varchar2
397 ,p_tax_calc_with_spouse_child in varchar2
398 ,p_income_reduction in varchar2
399 ,p_income_reduction_amount in number
400 ,p_rate_of_tax in varchar2
401 ,p_program_id in number
402 ,p_program_login_id in number
403 ,p_program_application_id in number
404 ,p_request_id in number
405 )
406 Return g_rec_type;
407 --
408 end pay_ppd_shd;