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