1 Package per_per_ins as
2 /* $Header: peperrhi.pkh 120.2.12010000.1 2008/07/28 05:14:29 appldev ship $ */
3 -- ----------------------------------------------------------------------------
4 -- |------------------------< set_base_key_value >----------------------------|
5 -- ----------------------------------------------------------------------------
6 -- {Start of Comments}
7 -- Description:
8 -- This procedure is called to register the next ID value from the database
9 -- sequence.
10 --
11 -- Prerequisites:
12 --
13 -- In Parameters:
14 -- Primary Key
15 --
16 -- Post Success:
17 --
18 -- Post Failure:
19 --
20 -- Developer Implementation Notes:
21 -- None.
22 --
23 -- Access Status:
24 -- Internal Development Use Only.
25 --
26 -- {End of Comments}
27 -- ----------------------------------------------------------------------------
28 procedure set_base_key_value
29 (p_person_id in number);
30 --
31 --
32 -- ----------------------------------------------------------------------------
33 -- |------------------------------< insert_dml >------------------------------|
34 -- ----------------------------------------------------------------------------
35 -- {Start Of Comments}
36 --
37 -- Description:
38 -- This procedure calls the dt_insert_dml control logic which handles
39 -- the actual datetrack dml.
40 --
41 -- Pre Conditions:
42 -- This is an internal private procedure which must be called from the ins
43 -- procedure and must have all mandatory arguments set (except the
44 -- object_version_number which is initialised within the dt_insert_dml
45 -- procedure).
46 --
47 -- In Arguments:
48 -- A Pl/Sql record structre.
49 --
50 -- Post Success:
51 -- Processing contines.
52 --
53 -- Post Failure:
54 -- No specific error handling is required within this procedure.
55 --
56 -- Developer Implementation Notes:
57 -- None.
58 --
59 -- Access Status:
60 -- Internal Table Handler Use Only.
61 --
62 -- {End Of Comments}
63 -- ----------------------------------------------------------------------------
64 Procedure insert_dml
65 (p_rec in out nocopy per_per_shd.g_rec_type,
66 p_effective_date in date,
67 p_datetrack_mode in varchar2,
68 p_validation_start_date in date,
69 p_validation_end_date in date);
70 --
71 -- ----------------------------------------------------------------------------
72 -- |---------------------------------< ins >----------------------------------|
73 -- ----------------------------------------------------------------------------
74 -- {Start Of Comments}
75 --
76 -- Description:
77 -- This procedure is the record interface for the insert business process
78 -- for the specified entity. The role of this process is to insert a fully
79 -- validated row, into the HR schema passing back to the calling process,
80 -- any system generated values (e.g. primary and object version number
81 -- attributes). This process is the main backbone of the ins business
82 -- process. The processing of this procedure is as follows:
83 -- 1) If the p_validate argument has been set to true then a savepoint is
84 -- issued.
85 -- 2) We must lock parent rows (if any exist).
86 -- 3) The controlling validation process insert_validate is then executed
87 -- which will execute all private and public validation business rule
88 -- processes.
89 -- 4) The pre_insert business process is then executed which enables any
90 -- logic to be processed before the insert dml process is executed.
91 -- 5) The insert_dml process will physical perform the insert dml into the
92 -- specified entity.
93 -- 6) The post_insert business process is then executed which enables any
94 -- logic to be processed after the insert dml process.
95 -- 7) If the p_validate argument has been set to true an exception is raised
96 -- which is handled and processed by performing a rollback to the
97 -- savepoint which was issued at the beginning of the Ins process.
98 --
99 -- Pre Conditions:
100 -- The main arguments to the business process have to be in the record
101 -- format.
102 -- The following attributes in p_rec are mandatory:
103 -- person_id, business_group_id, last_name, person_type_id, start_date,
104 -- effective_start_date, effective_end_date
105 --
106 -- In Arguments:
107 -- p_effective_date
108 -- Specifies the date of the datetrack insert operation.
109 -- p_validate
110 -- Determines if the business process is to be validated. Setting this
111 -- boolean value to true will invoke the process to be validated. The
112 -- default is false. The validation is controlled by a savepoint and
113 -- rollback mechanism. The savepoint is issued at the beginning of the
114 -- business process and is rollbacked at the end of the business process
115 -- when all the processing has been completed. The rollback is controlled
116 -- by raising and handling the exception hr_api.validate_enabled. We use
117 -- the exception because, by raising the exception with the business
118 -- process, we can exit successfully without having any of the 'OUT'
119 -- arguments being set.
120 --
121 -- Post Success:
122 -- A fully validated row will be inserted into the specified entity
123 -- without being committed. If the p_validate argument has been set to true
124 -- then all the work will be rolled back.
125 -- The primary key and object version number details for the inserted person
126 -- record will be returned in p_rec
127 --
128 -- Post Failure:
129 -- If an error has occurred, an error message will be supplied with the work
130 -- rolled back.
131 -- A failure will occur if any of the following conditions are found:
132 -- 1) Any of the mandatory arguments have not been set
133 -- 2) If date of birth > the effective start date then
134 -- 3) If system person type is 'EMP', 'EX_EMP', 'EMP_APL' or 'EX_EMP_APL' and
135 -- employee number is null
136 -- 4) If system person type is anything other than 'EMP', 'EX_EMP', 'EMP_APL'
137 -- or 'EX_EMP_APL' and employee number is not null
138 -- 5) If the employee number is not unique within the business group
139 -- 6) If a marital status value does not exist in hr_lookups
140 -- where lookup_type = 'MAR_STATUS'
141 -- 7) If the national identifier is not valid
142 -- 8) If a nationality value does not exist in hr_lookups
143 -- where lookup_type = 'NATIONALITY'
144 -- 9) If per_information_category is 'GB' and
145 -- the following conditions are true then
146 -- a) per_information1 value does not exist in hr_lookups
147 -- where lookup_type = 'ETH_TYPE'
148 -- b) per_information2 does not exist in hr_lookups
149 -- where lookup_type = 'YES_NO'
150 -- c) per_information4 does not exist in hr_lookups
151 -- d) per_information5 is a greater than 30 characters long or lower case
152 -- e) per_information3 or per_information6 to 20 values are not null
153 -- 10) person_type_id does not exist in per_person_types for the business
154 -- group
155 -- 11) system_person_type is anything other than 'EMP' or 'EX_EMP'
156 -- 12) The value of system_person_type is not consistent with the values of
157 -- current_employee_flag and current_emp_or_apl_flag
158 -- 13) If a sex value does not exist in hr_lookups
159 -- where lookup_type = 'SEX' then
160 -- 14) If a title value does not exist in hr_lookups
161 -- where lookup_type = 'TITLE' then
162 -- 15) If a sex value is 'M'
163 -- and the title value is 'MISS.','MRS.', 'MS.' then
164 -- 16) If a sex value is 'F'
165 -- and the title value is 'MR.'
166 -- 17) If the related system person type is 'EMP' or 'EX-EMP' then
167 -- if a sex value is null then
168 -- 18) If a start date is not the same as the effective start date when the
169 -- system person type is 'EMP' or 'EX-EMP'
170 --
171 -- Developer Implementation Notes:
172 -- None.
173 --
174 -- Access Status:
175 -- Internal Development Use Only.
176 --
177 -- {End Of Comments}
178 -- ----------------------------------------------------------------------------
179 Procedure ins
180 (
181 p_rec in out nocopy per_per_shd.g_rec_type,
182 p_effective_date in date,
183 p_validate in boolean default false,
184 p_name_combination_warning out nocopy boolean,
185 p_dob_null_warning out nocopy boolean,
186 p_orig_hire_warning out nocopy boolean
187 );
188 --
189 -- ----------------------------------------------------------------------------
190 -- |---------------------------------< ins >----------------------------------|
191 -- ----------------------------------------------------------------------------
192 -- {Start Of Comments}
193 --
194 -- Description:
195 -- This procedure is the attribute interface for the insert business
196 -- process for the specified entity and is the outermost layer. The role
197 -- of this process is to insert a fully validated row into the HR schema
198 -- passing back to the calling process, any system generated values
199 -- (e.g. object version number attributes).The processing of this
200 -- procedure is as follows:
201 -- 1) The attributes are converted into a local record structure by
202 -- calling the convert_args function.
203 -- 2) After the conversion has taken place, the corresponding record ins
204 -- interface business process is executed.
205 -- 3) OUT arguments are then set to their corresponding record arguments.
206 --
207 -- Pre Conditions:
208 --
209 -- In Arguments:
210 -- p_effective_date
211 -- Specifies the date of the datetrack insert operation.
212 -- p_validate
213 -- Determines if the business process is to be validated. Setting this
214 -- Boolean value to true will invoke the process to be validated.
215 -- The default is false.
216 --
217 -- Post Success:
218 -- A fully validated row will be inserted for the specified entity
219 -- without being committed (or rollbacked depending on the p_validate
220 -- status).
221 --
222 -- Post Failure:
223 -- If an error has occurred, an error message will be supplied with the work
224 -- rolled back. Refer to the ins record interface for details of possible
225 -- failures
226 --
227 -- Developer Implementation Notes:
228 -- None.
229 --
230 -- Access Status:
231 -- Internal Development Use Only.
232 --
233 -- {End Of Comments}
234 -- ----------------------------------------------------------------------------
235 Procedure ins
236 (
237 p_person_id out nocopy number,
238 p_effective_start_date out nocopy date,
239 p_effective_end_date out nocopy date,
240 p_business_group_id in number,
241 p_person_type_id in number,
242 p_last_name in varchar2,
243 p_start_date in date,
244 p_applicant_number in out nocopy varchar2,
245 p_comment_id out nocopy number,
246 p_comments in varchar2 default null,
247 p_current_applicant_flag out nocopy varchar2,
248 p_current_emp_or_apl_flag out nocopy varchar2,
249 p_current_employee_flag out nocopy varchar2,
250 p_date_employee_data_verified in date default null,
251 p_date_of_birth in date default null,
252 p_email_address in varchar2 default null,
253 p_employee_number in out nocopy varchar2,
254 p_expense_check_send_to_addres in varchar2 default null,
255 p_first_name in varchar2 default null,
256 p_full_name out nocopy varchar2,
257 p_known_as in varchar2 default null,
258 p_marital_status in varchar2 default null,
259 p_middle_names in varchar2 default null,
260 p_nationality in varchar2 default null,
261 p_national_identifier in varchar2 default null,
262 p_previous_last_name in varchar2 default null,
263 p_registered_disabled_flag in varchar2 default null,
264 p_sex in varchar2 default null,
265 p_title in varchar2 default null,
266 p_vendor_id in number default null,
267 p_work_telephone in varchar2 default null,
268 p_request_id in number default null,
269 p_program_application_id in number default null,
270 p_program_id in number default null,
271 p_program_update_date in date default null,
272 p_attribute_category in varchar2 default null,
273 p_attribute1 in varchar2 default null,
274 p_attribute2 in varchar2 default null,
275 p_attribute3 in varchar2 default null,
276 p_attribute4 in varchar2 default null,
277 p_attribute5 in varchar2 default null,
278 p_attribute6 in varchar2 default null,
279 p_attribute7 in varchar2 default null,
280 p_attribute8 in varchar2 default null,
281 p_attribute9 in varchar2 default null,
282 p_attribute10 in varchar2 default null,
283 p_attribute11 in varchar2 default null,
284 p_attribute12 in varchar2 default null,
285 p_attribute13 in varchar2 default null,
286 p_attribute14 in varchar2 default null,
287 p_attribute15 in varchar2 default null,
288 p_attribute16 in varchar2 default null,
289 p_attribute17 in varchar2 default null,
290 p_attribute18 in varchar2 default null,
291 p_attribute19 in varchar2 default null,
292 p_attribute20 in varchar2 default null,
293 p_attribute21 in varchar2 default null,
294 p_attribute22 in varchar2 default null,
295 p_attribute23 in varchar2 default null,
296 p_attribute24 in varchar2 default null,
297 p_attribute25 in varchar2 default null,
298 p_attribute26 in varchar2 default null,
299 p_attribute27 in varchar2 default null,
300 p_attribute28 in varchar2 default null,
301 p_attribute29 in varchar2 default null,
302 p_attribute30 in varchar2 default null,
303 p_per_information_category in varchar2 default null,
304 p_per_information1 in varchar2 default null,
305 p_per_information2 in varchar2 default null,
306 p_per_information3 in varchar2 default null,
307 p_per_information4 in varchar2 default null,
308 p_per_information5 in varchar2 default null,
309 p_per_information6 in varchar2 default null,
310 p_per_information7 in varchar2 default null,
311 p_per_information8 in varchar2 default null,
312 p_per_information9 in varchar2 default null,
313 p_per_information10 in varchar2 default null,
314 p_per_information11 in varchar2 default null,
315 p_per_information12 in varchar2 default null,
316 p_per_information13 in varchar2 default null,
317 p_per_information14 in varchar2 default null,
318 p_per_information15 in varchar2 default null,
319 p_per_information16 in varchar2 default null,
320 p_per_information17 in varchar2 default null,
321 p_per_information18 in varchar2 default null,
322 p_per_information19 in varchar2 default null,
323 p_per_information20 in varchar2 default null,
324 p_suffix in varchar2 default null,
325 p_DATE_OF_DEATH in date default null,
326 p_BACKGROUND_CHECK_STATUS in varchar2 default null,
327 p_BACKGROUND_DATE_CHECK in date default null,
328 p_BLOOD_TYPE in varchar2 default null,
329 p_CORRESPONDENCE_LANGUAGE in varchar2 default null,
333 p_HONORS in varchar2 default null,
330 p_FAST_PATH_EMPLOYEE in varchar2 default null,
331 p_FTE_CAPACITY in number default null,
332 p_HOLD_APPLICANT_DATE_UNTIL in date default null,
334 p_INTERNAL_LOCATION in varchar2 default null,
335 p_LAST_MEDICAL_TEST_BY in varchar2 default null,
336 p_LAST_MEDICAL_TEST_DATE in date default null,
337 p_MAILSTOP in varchar2 default null,
338 p_OFFICE_NUMBER in varchar2 default null,
339 p_ON_MILITARY_SERVICE in varchar2 default null,
340 p_ORDER_NAME in varchar2 default null,
341 p_PRE_NAME_ADJUNCT in varchar2 default null,
342 p_PROJECTED_START_DATE in date default null,
343 p_REHIRE_AUTHORIZOR in varchar2 default null,
344 p_REHIRE_RECOMMENDATION in varchar2 default null,
345 p_RESUME_EXISTS in varchar2 default null,
346 p_RESUME_LAST_UPDATED in date default null,
347 p_SECOND_PASSPORT_EXISTS in varchar2 default null,
348 p_STUDENT_STATUS in varchar2 default null,
349 p_WORK_SCHEDULE in varchar2 default null,
350 p_PER_INFORMATION21 in varchar2 default null,
351 p_PER_INFORMATION22 in varchar2 default null,
352 p_PER_INFORMATION23 in varchar2 default null,
353 p_PER_INFORMATION24 in varchar2 default null,
354 p_PER_INFORMATION25 in varchar2 default null,
355 p_PER_INFORMATION26 in varchar2 default null,
356 p_PER_INFORMATION27 in varchar2 default null,
357 p_PER_INFORMATION28 in varchar2 default null,
358 p_PER_INFORMATION29 in varchar2 default null,
359 p_PER_INFORMATION30 in varchar2 default null,
360 p_REHIRE_REASON in varchar2 default null,
361 p_benefit_group_id in number default null,
362 p_receipt_of_death_cert_date in date default null,
363 p_coord_ben_med_pln_no in varchar2 default null,
364 p_coord_ben_no_cvg_flag in varchar2 default 'N',
365 p_coord_ben_med_ext_er in varchar2 default null,
366 p_coord_ben_med_pl_name in varchar2 default null,
367 p_coord_ben_med_insr_crr_name in varchar2 default null,
368 p_coord_ben_med_insr_crr_ident in varchar2 default null,
369 p_coord_ben_med_cvg_strt_dt in date default null,
370 p_coord_ben_med_cvg_end_dt in date default null,
371 p_uses_tobacco_flag in varchar2 default null,
372 p_dpdnt_adoption_date in date default null,
373 p_dpdnt_vlntry_svce_flag in varchar2 default 'N',
374 p_original_date_of_hire in date default null,
375 p_town_of_birth in varchar2 default null,
376 p_region_of_birth in varchar2 default null,
377 p_country_of_birth in varchar2 default null,
378 p_global_person_id in varchar2 default null,
379 p_party_id in number default null,
380 p_npw_number in out nocopy varchar2,
381 p_current_npw_flag in varchar2 default null,
382 p_object_version_number out nocopy number,
383 p_effective_date in date,
384 p_validate in boolean default false,
385 p_name_combination_warning out nocopy boolean,
389 --
386 p_dob_null_warning out nocopy boolean,
387 p_orig_hire_warning out nocopy boolean
388 ) ;
390 end per_per_ins;