1 Package pqh_ptx_shd AUTHID CURRENT_USER as
2 /* $Header: pqptxrhi.pkh 120.2 2011/11/17 11:48:27 sidsaxen ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (
10 position_transaction_id number(15),
11 action_date date,
12 position_id number(15),
13 availability_status_id number(15),
14 business_group_id number(15),
15 entry_step_id number(15),
16 entry_grade_rule_id number(15),
17 job_id number(15),
18 location_id number(15),
19 organization_id number(15),
20 pay_freq_payroll_id number(15),
21 position_definition_id number(15),
22 prior_position_id number(15),
23 relief_position_id number(15),
24 entry_grade_id number(15),
25 successor_position_id number(15),
26 supervisor_position_id number(15),
27 amendment_date date,
28 amendment_recommendation varchar2(2000),
29 amendment_ref_number varchar2(30),
30 avail_status_prop_end_date date,
31 bargaining_unit_cd varchar2(30),
32 comments varchar2(2000), -- pseudo column
33 country1 varchar2(30),
34 country2 varchar2(30),
35 country3 varchar2(30),
36 current_job_prop_end_date date,
37 current_org_prop_end_date date,
38 date_effective date,
39 date_end date,
40 earliest_hire_date date,
41 fill_by_date date,
42 frequency varchar2(30),
43 fte number(15,5), -- Increased length and precision
44 fte_capacity varchar2(30),
45 location1 varchar2(30),
46 location2 varchar2(30),
47 location3 varchar2(30),
48 max_persons number(9), -- Increased length
49 name varchar2(240),
50 other_requirements varchar2(2000),
51 overlap_period number(19,0),
52 overlap_unit_cd varchar2(30),
53 passport_required varchar2(30),
54 pay_term_end_day_cd varchar2(30),
55 pay_term_end_month_cd varchar2(30),
56 permanent_temporary_flag varchar2(30),
57 permit_recruitment_flag varchar2(30),
58 position_type varchar2(30),
59 posting_description varchar2(2000),
60 probation_period number(19,0),
61 probation_period_unit_cd varchar2(30),
62 relocate_domestically varchar2(30),
63 relocate_internationally varchar2(30),
64 replacement_required_flag varchar2(30),
65 review_flag varchar2(30),
66 seasonal_flag varchar2(30),
67 security_requirements varchar2(2000),
68 service_minimum varchar2(30),
69 term_start_day_cd varchar2(30),
70 term_start_month_cd varchar2(30),
71 time_normal_finish varchar2(9), -- Increased length
72 time_normal_start varchar2(9), -- Increased length
73 transaction_status varchar2(30),
74 travel_required varchar2(30),
75 working_hours number(22,3),
76 works_council_approval_flag varchar2(30),
77 work_any_country varchar2(30),
78 work_any_location varchar2(30),
79 work_period_type_cd varchar2(30),
80 work_schedule varchar2(30),
81 work_duration varchar2(30),
82 work_term_end_day_cd varchar2(30),
83 work_term_end_month_cd varchar2(30),
84 proposed_fte_for_layoff number(15,2), -- Increased length
85 proposed_date_for_layoff date,
86 information1 varchar2(150),
87 information2 varchar2(150),
88 information3 varchar2(150),
89 information4 varchar2(150),
90 information5 varchar2(150),
91 information6 varchar2(150),
92 information7 varchar2(150),
93 information8 varchar2(150),
94 information9 varchar2(150),
95 information10 varchar2(150),
96 information11 varchar2(150),
97 information12 varchar2(150),
98 information13 varchar2(150),
99 information14 varchar2(150),
100 information15 varchar2(150),
101 information16 varchar2(150),
102 information17 varchar2(150),
103 information18 varchar2(150),
104 information19 varchar2(150),
105 information20 varchar2(150),
106 information21 varchar2(150),
107 information22 varchar2(150),
108 information23 varchar2(150),
109 information24 varchar2(150),
110 information25 varchar2(150),
111 information26 varchar2(150),
112 information27 varchar2(150),
113 information28 varchar2(150),
114 information29 varchar2(150),
115 information30 varchar2(150),
116 information_category varchar2(30),
117 attribute1 varchar2(150),
118 attribute2 varchar2(150),
119 attribute3 varchar2(150),
120 attribute4 varchar2(150),
121 attribute5 varchar2(150),
122 attribute6 varchar2(150),
123 attribute7 varchar2(150),
124 attribute8 varchar2(150),
125 attribute9 varchar2(150),
126 attribute10 varchar2(150),
127 attribute11 varchar2(150),
128 attribute12 varchar2(150),
129 attribute13 varchar2(150),
130 attribute14 varchar2(150),
131 attribute15 varchar2(150),
132 attribute16 varchar2(150),
133 attribute17 varchar2(150),
134 attribute18 varchar2(150),
135 attribute19 varchar2(150),
136 attribute20 varchar2(150),
137 attribute21 varchar2(150),
138 attribute22 varchar2(150),
139 attribute23 varchar2(150),
140 attribute24 varchar2(150),
141 attribute25 varchar2(150),
142 attribute26 varchar2(150),
143 attribute27 varchar2(150),
144 attribute28 varchar2(150),
145 attribute29 varchar2(150),
146 attribute30 varchar2(150),
147 attribute_category varchar2(30),
148 object_version_number number(9) ,
149 pay_basis_id number(9),
150 supervisor_id number(10),
151 wf_transaction_category_id number(15)
152 );
153 --
154 -- ----------------------------------------------------------------------------
155 -- | Global Definitions - Internal Development Use Only |
156 -- ----------------------------------------------------------------------------
157 --
158 g_old_rec g_rec_type; -- Global record definition
159 --
160 --
161 -- ----------------------------------------------------------------------------
162 -- |---------------------------< constraint_error >---------------------------|
163 -- ----------------------------------------------------------------------------
164 -- {Start Of Comments}
165 --
166 -- Description:
167 -- This procedure is called when a constraint has been violated (i.e.
168 -- The exception hr_api.check_integrity_violated,
169 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
170 -- hr_api.unique_integrity_violated has been raised).
171 -- The exceptions can only be raised as follows:
172 -- 1) A check constraint can only be violated during an INSERT or UPDATE
173 -- dml operation.
174 -- 2) A parent integrity constraint can only be violated during an
175 -- INSERT or UPDATE dml operation.
176 -- 3) A child integrity constraint can only be violated during an
177 -- DELETE dml operation.
178 -- 4) A unique integrity constraint can only be violated during INSERT or
179 -- UPDATE dml operation.
180 --
181 -- Prerequisites:
182 -- 1) Either hr_api.check_integrity_violated,
183 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
184 -- hr_api.unique_integrity_violated has been raised with the subsequent
185 -- stripping of the constraint name from the generated error message
186 -- text.
187 -- 2) Standalone validation test which corresponds with a constraint error.
188 --
189 -- In Parameter:
190 -- p_constraint_name is in upper format and is just the constraint name
191 -- (e.g. not prefixed by brackets, schema owner etc).
192 --
193 -- Post Success:
194 -- Development dependant.
195 --
196 -- Post Failure:
197 -- Developement dependant.
198 --
199 -- Developer Implementation Notes:
200 -- For each constraint being checked the hr system package failure message
201 -- has been generated as a template only. These system error messages should
202 -- be modified as required (i.e. change the system failure message to a user
203 -- friendly defined error message).
204 --
205 -- Access Status:
206 -- Internal Development Use Only.
207 --
208 -- {End Of Comments}
209 -- ----------------------------------------------------------------------------
210 Procedure constraint_error
211 (p_constraint_name in all_constraints.constraint_name%TYPE);
212 --
213 -- ----------------------------------------------------------------------------
214 -- |-----------------------------< api_updating >-----------------------------|
215 -- ----------------------------------------------------------------------------
216 -- {Start Of Comments}
217 --
218 -- Description:
219 -- This function is used to populate the g_old_rec record with the
220 -- current row from the database for the specified primary key
221 -- provided that the primary key exists and is valid and does not
222 -- already match the current g_old_rec. The function will always return
223 -- a TRUE value if the g_old_rec is populated with the current row.
224 -- A FALSE value will be returned if all of the primary key arguments
225 -- are null.
226 --
227 -- Prerequisites:
228 -- None.
229 --
230 -- In Parameters:
231 --
232 -- Post Success:
233 -- A value of TRUE will be returned indiciating that the g_old_rec
234 -- is current.
235 -- A value of FALSE will be returned if all of the primary key arguments
236 -- have a null value (this indicates that the row has not be inserted into
237 -- the Schema), and therefore could never have a corresponding row.
238 --
239 -- Post Failure:
240 -- A failure can only occur under two circumstances:
241 -- 1) The primary key is invalid (i.e. a row does not exist for the
242 -- specified primary key values).
243 -- 2) If an object_version_number exists but is NOT the same as the current
244 -- g_old_rec value.
245 --
246 -- Developer Implementation Notes:
247 -- None.
248 --
249 -- Access Status:
250 -- Internal Development Use Only.
251 --
252 -- {End Of Comments}
253 -- ----------------------------------------------------------------------------
254 Function api_updating
255 (
256 p_position_transaction_id in number,
257 p_object_version_number in number
258 ) Return Boolean;
259 --
260 -- ----------------------------------------------------------------------------
261 -- |---------------------------------< lck >----------------------------------|
262 -- ----------------------------------------------------------------------------
263 -- {Start Of Comments}
264 --
265 -- Description:
266 -- The Lck process has two main functions to perform. Firstly, the row to be
267 -- updated or deleted must be locked. The locking of the row will only be
268 -- successful if the row is not currently locked by another user.
269 -- Secondly, during the locking of the row, the row is selected into
270 -- the g_old_rec data structure which enables the current row values from the
271 -- server to be available to the api.
272 --
273 -- Prerequisites:
274 -- When attempting to call the lock the object version number (if defined)
275 -- is mandatory.
276 --
277 -- In Parameters:
278 -- The arguments to the Lck process are the primary key(s) which uniquely
279 -- identify the row and the object version number of row.
280 --
281 -- Post Success:
282 -- On successful completion of the Lck process the row to be updated or
283 -- deleted will be locked and selected into the global data structure
284 -- g_old_rec.
285 --
286 -- Post Failure:
287 -- The Lck process can fail for three reasons:
288 -- 1) When attempting to lock the row the row could already be locked by
289 -- another user. This will raise the HR_Api.Object_Locked exception.
290 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
291 -- This error is trapped and reported using the message name
292 -- 'HR_7220_INVALID_PRIMARY_KEY'.
293 -- 3) The row although existing in the HR Schema has a different object
294 -- version number than the object version number specified.
295 -- This error is trapped and reported using the message name
296 -- 'HR_7155_OBJECT_INVALID'.
297 --
298 -- Developer Implementation Notes:
299 -- For each primary key and the object version number arguments add a
300 -- call to hr_api.mandatory_arg_error procedure to ensure that these
301 -- argument values are not null.
302 --
303 -- Access Status:
304 -- Internal Development Use Only.
305 --
306 -- {End Of Comments}
307 -- ----------------------------------------------------------------------------
311 p_object_version_number in number
308 Procedure lck
309 (
310 p_position_transaction_id in number,
312 );
313 --
314 -- ----------------------------------------------------------------------------
315 -- |-----------------------------< convert_args >-----------------------------|
316 -- ----------------------------------------------------------------------------
317 -- {Start Of Comments}
318 --
319 -- Description:
320 -- This function is used to turn attribute parameters into the record
321 -- structure parameter g_rec_type.
322 --
323 -- Prerequisites:
324 -- This is a private function and can only be called from the ins or upd
325 -- attribute processes.
326 --
327 -- In Parameters:
328 --
329 -- Post Success:
330 -- A returning record structure will be returned.
331 --
332 -- Post Failure:
333 -- No direct error handling is required within this function. Any possible
334 -- errors within this function will be a PL/SQL value error due to conversion
335 -- of datatypes or data lengths.
336 --
337 -- Developer Implementation Notes:
338 -- None.
339 --
340 -- Access Status:
341 -- Internal Row Handler Use Only.
342 --
343 -- {End Of Comments}
344 -- ----------------------------------------------------------------------------
345 Function convert_args
346 (
347 p_position_transaction_id in number,
348 p_action_date in date,
349 p_position_id in number,
350 p_availability_status_id in number,
351 p_business_group_id in number,
352 p_entry_step_id in number,
353 p_entry_grade_rule_id in number,
354 p_job_id in number,
355 p_location_id in number,
356 p_organization_id in number,
357 p_pay_freq_payroll_id in number,
358 p_position_definition_id in number,
359 p_prior_position_id in number,
360 p_relief_position_id in number,
361 p_entry_grade_id in number,
362 p_successor_position_id in number,
363 p_supervisor_position_id in number,
364 p_amendment_date in date,
365 p_amendment_recommendation in varchar2,
366 p_amendment_ref_number in varchar2,
367 p_avail_status_prop_end_date in date,
368 p_bargaining_unit_cd in varchar2,
369 p_comments in varchar2,
370 p_country1 in varchar2,
371 p_country2 in varchar2,
372 p_country3 in varchar2,
373 p_current_job_prop_end_date in date,
374 p_current_org_prop_end_date in date,
375 p_date_effective in date,
376 p_date_end in date,
377 p_earliest_hire_date in date,
378 p_fill_by_date in date,
379 p_frequency in varchar2,
380 p_fte in number,
381 p_fte_capacity in varchar2,
382 p_location1 in varchar2,
383 p_location2 in varchar2,
384 p_location3 in varchar2,
385 p_max_persons in number,
386 p_name in varchar2,
387 p_other_requirements in varchar2,
388 p_overlap_period in number,
389 p_overlap_unit_cd in varchar2,
390 p_passport_required in varchar2,
391 p_pay_term_end_day_cd in varchar2,
392 p_pay_term_end_month_cd in varchar2,
393 p_permanent_temporary_flag in varchar2,
394 p_permit_recruitment_flag in varchar2,
395 p_position_type in varchar2,
396 p_posting_description in varchar2,
397 p_probation_period in number,
398 p_probation_period_unit_cd in varchar2,
399 p_relocate_domestically in varchar2,
400 p_relocate_internationally in varchar2,
401 p_replacement_required_flag in varchar2,
402 p_review_flag in varchar2,
403 p_seasonal_flag in varchar2,
404 p_security_requirements in varchar2,
405 p_service_minimum in varchar2,
406 p_term_start_day_cd in varchar2,
407 p_term_start_month_cd in varchar2,
408 p_time_normal_finish in varchar2,
409 p_time_normal_start in varchar2,
410 p_transaction_status in varchar2,
411 p_travel_required in varchar2,
412 p_working_hours in number,
413 p_works_council_approval_flag in varchar2,
414 p_work_any_country in varchar2,
415 p_work_any_location in varchar2,
416 p_work_period_type_cd in varchar2,
417 p_work_schedule in varchar2,
418 p_work_duration in varchar2,
419 p_work_term_end_day_cd in varchar2,
420 p_work_term_end_month_cd in varchar2,
421 p_proposed_fte_for_layoff in number,
422 p_proposed_date_for_layoff in date,
423 p_information1 in varchar2,
424 p_information2 in varchar2,
425 p_information3 in varchar2,
426 p_information4 in varchar2,
427 p_information5 in varchar2,
428 p_information6 in varchar2,
429 p_information7 in varchar2,
430 p_information8 in varchar2,
431 p_information9 in varchar2,
432 p_information10 in varchar2,
433 p_information11 in varchar2,
434 p_information12 in varchar2,
435 p_information13 in varchar2,
436 p_information14 in varchar2,
437 p_information15 in varchar2,
438 p_information16 in varchar2,
439 p_information17 in varchar2,
440 p_information18 in varchar2,
441 p_information19 in varchar2,
442 p_information20 in varchar2,
443 p_information21 in varchar2,
444 p_information22 in varchar2,
445 p_information23 in varchar2,
446 p_information24 in varchar2,
447 p_information25 in varchar2,
448 p_information26 in varchar2,
449 p_information27 in varchar2,
450 p_information28 in varchar2,
451 p_information29 in varchar2,
452 p_information30 in varchar2,
453 p_information_category in varchar2,
454 p_attribute1 in varchar2,
455 p_attribute2 in varchar2,
456 p_attribute3 in varchar2,
457 p_attribute4 in varchar2,
458 p_attribute5 in varchar2,
459 p_attribute6 in varchar2,
460 p_attribute7 in varchar2,
461 p_attribute8 in varchar2,
462 p_attribute9 in varchar2,
463 p_attribute10 in varchar2,
464 p_attribute11 in varchar2,
465 p_attribute12 in varchar2,
466 p_attribute13 in varchar2,
467 p_attribute14 in varchar2,
468 p_attribute15 in varchar2,
469 p_attribute16 in varchar2,
470 p_attribute17 in varchar2,
471 p_attribute18 in varchar2,
472 p_attribute19 in varchar2,
473 p_attribute20 in varchar2,
474 p_attribute21 in varchar2,
475 p_attribute22 in varchar2,
476 p_attribute23 in varchar2,
477 p_attribute24 in varchar2,
478 p_attribute25 in varchar2,
479 p_attribute26 in varchar2,
480 p_attribute27 in varchar2,
481 p_attribute28 in varchar2,
482 p_attribute29 in varchar2,
483 p_attribute30 in varchar2,
484 p_attribute_category in varchar2,
485 p_object_version_number in number,
486 p_pay_basis_id in number,
487 p_supervisor_id in number,
488 p_wf_transaction_category_id in number
489 )
490 Return g_rec_type;
491 --
492 end pqh_ptx_shd;