1 Package pqh_corps_definitions_api as
2 /* $Header: pqcpdapi.pkh 120.1 2005/10/02 02:26:32 aroussel $ */
3 /*#
4 * This package contains APIs to validate, create, update and delete corps
5 * definition records.
6 * @rep:scope public
7 * @rep:product per
8 * @rep:displayname CORPS Definition for France
9 */
10 --
11 -- ----------------------------------------------------------------------------
12 -- |-------------------------< create_corps_definition >----------------------|
13 -- ----------------------------------------------------------------------------
14 --
15 -- {Start Of Comments}
16 /*#
17 * This API creates a new corps definition record.
18 *
19 * Corps is a French public sector specific work structure that captures the
20 * career progression related information for a civil servant joining the
21 * Public Sector Organization. Details recorded include the benefits program to
22 * which the corps is linked, category, type of public sector etc. It validates
23 * corps name for uniqueness. The record is created in PQH_CORPS_DEFINITIONS
24 * table.
25 *
26 * <p><b>Licensing</b><br>
27 * This API is licensed for use with Human Resources.
28 *
29 * <p><b>Prerequisites</b><br>
30 * Benefits program representing the grade ladder must exist as of the
31 * effective date.
32 *
33 * <p><b>Post Success</b><br>
34 * A new corps definition record is created in the database.
35 *
36 * <p><b>Post Failure</b><br>
37 * A corps definition record is not created in the database and an error is
38 * raised
39 * @param p_validate If true, then validation alone will be performed and the
40 * database will remain unchanged. If false and all validation checks pass,
41 * then the database will be modified.
42 * @param p_effective_date Specifies the reference date for validating lookup
43 * values, applicable within the active date range. This date does not
44 * determine when the changes take effect.
45 * @param p_corps_definition_id The process returns the unique corps definition
46 * identifier generated as the primary key for the new record
47 * @param p_business_group_id Identifies the business group identifier for
48 * which the corps definition record is being created
49 * @param p_name Unique name for the corps.
50 * @param p_status_cd Status of the corps as identified by lookup type
51 * 'PQH_CORPS_STATUS'
52 * @param p_retirement_age Retirement age of the employees on this corps. Its
53 * value is between 40 and 80
54 * @param p_category_cd Category code of the corps which will indicate its
55 * level of responsibility and management within the public sector. Valid
56 * values are defined by 'PQH_CORPS_CATEGORY' lookup type
57 * @param p_corps_type_cd Type of the corps. Valid values are defined by
58 * 'PQH_CORPS_TYPE' lookup type
59 * @param p_date_from {@rep:casecolumn PQH_CORPS_DEFINITIONS.DATE_FROM}
60 * @param p_date_to {@rep:casecolumn PQH_CORPS_DEFINITIONS.DATE_TO}
61 * @param p_recruitment_end_date Identifies the end date of recruitment for the
62 * corps. It should be between start date and end date
63 * @param p_starting_grade_step_id The starting grade step for the civil
64 * servant on this corps. It should be the identifier of a step in existence as
65 * of the effective date
66 * @param p_type_of_ps Type of the public sector organization. Valid values are
67 * defined by 'FR_PQH_ORG_CATEGORY' lookup type
68 * @param p_task_desc {@rep:casecolumn PQH_CORPS_DEFINITIONS.TASK_DESC}
69 * @param p_secondment_threshold Percentage of civil servants in this corps,
70 * who can be placed on secondment. Value should be between 0 and 100
71 * @param p_normal_hours Normal working hours for civil servants on this corps
72 * in terms of the unit given in normal_frequency parameter
73 * @param p_normal_hours_frequency Unit of time measurement in terms of which
74 * normal working hour for civil servants on this corps is provided. This value
75 * is mandatory if the normal_hours parameter has a not null value. Valid
76 * values are identified by 'FREQUENCY' lookup type.
77 * @param p_minimum_hours Minimum working hours for civil servants on this
78 * corps in terms of the unit given in minimum_frequency parameter. Should not
79 * be greater than the normal working hours.
80 * @param p_minimum_hours_frequency Unit of time measurement in terms of which
81 * minimum working hour for civil servants on this corps is provided. This
82 * value is mandatory if the minimum_hours parameter has a not null value.
83 * Valid values are identified by lookup type 'FREQUENCY'.
84 * @param p_attribute1 Descriptive flexfield segment.
85 * @param p_attribute2 Descriptive flexfield segment.
86 * @param p_attribute3 Descriptive flexfield segment.
87 * @param p_attribute4 Descriptive flexfield segment.
88 * @param p_attribute5 Descriptive flexfield segment.
89 * @param p_attribute6 Descriptive flexfield segment.
90 * @param p_attribute7 Descriptive flexfield segment.
91 * @param p_attribute8 Descriptive flexfield segment.
92 * @param p_attribute9 Descriptive flexfield segment.
93 * @param p_attribute10 Descriptive flexfield segment.
94 * @param p_attribute11 Descriptive flexfield segment.
95 * @param p_attribute12 Descriptive flexfield segment.
96 * @param p_attribute13 Descriptive flexfield segment.
97 * @param p_attribute14 Descriptive flexfield segment.
98 * @param p_attribute15 Descriptive flexfield segment.
99 * @param p_attribute16 Descriptive flexfield segment.
100 * @param p_attribute17 Descriptive flexfield segment.
101 * @param p_attribute18 Descriptive flexfield segment.
102 * @param p_attribute19 Descriptive flexfield segment.
103 * @param p_attribute20 Descriptive flexfield segment.
104 * @param p_attribute21 Descriptive flexfield segment.
105 * @param p_attribute22 Descriptive flexfield segment.
106 * @param p_attribute23 Descriptive flexfield segment.
107 * @param p_attribute24 Descriptive flexfield segment.
108 * @param p_attribute25 Descriptive flexfield segment.
109 * @param p_attribute26 Descriptive flexfield segment.
110 * @param p_attribute27 Descriptive flexfield segment.
111 * @param p_attribute28 Descriptive flexfield segment.
112 * @param p_attribute29 Descriptive flexfield segment.
113 * @param p_attribute30 Descriptive flexfield segment.
114 * @param p_attribute_category This context value determines which flexfield
115 * structure to use with the descriptive flexfield segments.
116 * @param p_object_version_number If p_validate is false, the process returns
117 * the version number of the created corps definition record. If p_validate is
118 * true, it returns null
119 * @param p_primary_prof_field_id Primary professional field shared type
120 * identifier. Valid values are defined by 'PER_SHARED_TYPES' table.
121 * @param p_starting_grade_id The starting grade step for the civil servant on
122 * this corps. It should be the identifier of a step in existence as of the
123 * effective date
124 * @param p_ben_pgm_id {@rep:casecolumn PQH_CORPS_DEFINITIONS.BEN_PGM_ID}
125 * @param p_probation_period {@rep:casecolumn
126 * PQH_CORPS_DEFINITIONS.PROBATION_PERIOD}
127 * @param p_probation_units Units for probation period duration. Valid values
128 * are identified by lookup type 'PROC_PERIOD_TYPE'.
129 * @rep:displayname Create CORPS Definition
130 * @rep:category BUSINESS_ENTITY PQH_FR_CORPS
131 * @rep:category MISC_EXTENSIONS HR_USER_HOOKS
132 * @rep:scope public
133 * @rep:lifecycle active
134 * @rep:ihelp PER/@scalapi APIs in Oracle HRMS
135 */
136 --
137 -- {End Of Comments}
138 --
139 procedure create_corps_definition
140 (
141 p_validate in boolean default false
142 ,p_effective_date in date
143 ,p_corps_definition_id out nocopy number
144 ,p_business_group_id in number
145 ,p_name in varchar2
146 ,p_status_cd in varchar2
147 ,p_retirement_age in number default null
148 ,p_category_cd in varchar2
149 ,p_corps_type_cd in varchar2
150 ,p_date_from in date
151 ,p_date_to in date default null
152 ,p_recruitment_end_date in date default null
153 ,p_starting_grade_step_id in number default null
154 ,p_type_of_ps in varchar2 default null
155 ,p_task_desc in varchar2 default null
156 ,p_secondment_threshold in number default null
157 ,p_normal_hours in number default null
158 ,p_normal_hours_frequency in varchar2 default null
159 ,p_minimum_hours in number default null
160 ,p_minimum_hours_frequency in varchar2 default null
161 ,p_attribute1 in varchar2 default null
162 ,p_attribute2 in varchar2 default null
163 ,p_attribute3 in varchar2 default null
164 ,p_attribute4 in varchar2 default null
165 ,p_attribute5 in varchar2 default null
166 ,p_attribute6 in varchar2 default null
167 ,p_attribute7 in varchar2 default null
168 ,p_attribute8 in varchar2 default null
169 ,p_attribute9 in varchar2 default null
170 ,p_attribute10 in varchar2 default null
171 ,p_attribute11 in varchar2 default null
172 ,p_attribute12 in varchar2 default null
173 ,p_attribute13 in varchar2 default null
174 ,p_attribute14 in varchar2 default null
175 ,p_attribute15 in varchar2 default null
176 ,p_attribute16 in varchar2 default null
177 ,p_attribute17 in varchar2 default null
178 ,p_attribute18 in varchar2 default null
179 ,p_attribute19 in varchar2 default null
180 ,p_attribute20 in varchar2 default null
181 ,p_attribute21 in varchar2 default null
182 ,p_attribute22 in varchar2 default null
183 ,p_attribute23 in varchar2 default null
184 ,p_attribute24 in varchar2 default null
185 ,p_attribute25 in varchar2 default null
186 ,p_attribute26 in varchar2 default null
187 ,p_attribute27 in varchar2 default null
188 ,p_attribute28 in varchar2 default null
189 ,p_attribute29 in varchar2 default null
190 ,p_attribute30 in varchar2 default null
191 ,p_attribute_category in varchar2 default null
192 ,p_object_version_number out nocopy number
193 ,p_primary_prof_field_id in number default null
194 ,p_starting_grade_id in number default null
195 ,p_ben_pgm_id in number default null
196 ,p_probation_period in number default null
197 ,p_probation_units in varchar2 default null
198 );
199 --
200 -- ----------------------------------------------------------------------------
201 -- |-------------------------< update_corps_definition >----------------------|
202 -- ----------------------------------------------------------------------------
203 --
204 -- {Start Of Comments}
205 /*#
206 * This API validates the record when an existing corps definition is changed
207 * and updates the record in the database.
208 *
209 * It validates the corps definition name for uniqueness. The record is updated
210 * in PQH_CORPS_DEFINITIONS table
211 *
212 * <p><b>Licensing</b><br>
213 * This API is licensed for use with Human Resources.
214 *
215 * <p><b>Prerequisites</b><br>
216 * The corps definition record must exist with the specified object version
217 * number.
218 *
219 * <p><b>Post Success</b><br>
220 * The existing corps definition record is updated in the database.
221 *
222 * <p><b>Post Failure</b><br>
223 * The existing corps definition record is not updated and an error is raised
224 * @param p_validate If true, then validation alone will be performed and the
225 * database will remain unchanged. If false and all validation checks pass,
226 * then the database will be modified.
227 * @param p_effective_date Determines when the Date Track operation comes into
228 * force
229 * @param p_corps_definition_id Unique corps definition identifier generated
230 * for this record when created as a Primary Key
231 * @param p_business_group_id Identifies the business group identifier for
232 * which the corps definition record is being created
233 * @param p_name Unique name for the corps
234 * @param p_status_cd Status of the corps as identified by lookup type
235 * 'PQH_CORPS_STATUS'
236 * @param p_retirement_age Retirement age of the employees on this corps. Value
237 * is between 40 and 80
238 * @param p_category_cd Category code of the corps which will indicate its
239 * level of responsibility and management within the public sector. Valid
240 * values are defined by 'PQH_CORPS_CATEGORY' lookup type
241 * @param p_starting_grade_step_id Type of the corps. Valid values are defined
242 * by 'PQH_CORPS_TYPE' lookup type
243 * @param p_type_of_ps {@rep:casecolumn PQH_CORPS_DEFINITIONS.DATE_FROM}
244 * @param p_corps_type_cd {@rep:casecolumn PQH_CORPS_DEFINITIONS.DATE_TO}
245 * @param p_date_from Identifies the end date of recruitment for the corps. It
246 * should be between start date and end date
247 * @param p_date_to The starting grade step for the civil servant on this
248 * corps. It should be the identifier of a step in existence as of the
249 * effective date
250 * @param p_recruitment_end_date Type of the public sector organization. Valid
251 * values are defined by 'FR_PQH_ORG_CATEGORY' lookup type
252 * @param p_task_desc {@rep:casecolumn PQH_CORPS_DEFINITIONS.TASK_DESC}
253 * @param p_secondment_threshold Percentage of civil servants in this corps who
254 * can be placed on secondment. Its value should be between 0 and 100
255 * @param p_normal_hours Normal working hours for civil servants on this corps
256 * in terms of the unit given in normal_frequency parameter
257 * @param p_normal_hours_frequency Unit of time measurement in terms of which
258 * normal working hour for civil servants on this corps is provided. This value
259 * is mandatory if the normal_hours parameter has a not null value. Valid
260 * values are identified by 'FREQUENCY' lookup type.
261 * @param p_minimum_hours Minimum working hours for civil servants on this
262 * corps in terms of the unit given in minimum_frequency parameter. It should
263 * not be greater than the normal working hours
264 * @param p_minimum_hours_frequency Unit of time measurement in terms of which
265 * minimum working hour for civil servants on this corps is provided. This
266 * value is mandatory if the minimum_hours parameter has a not null value.
267 * Valid values are identified by lookup type 'FREQUENCY'.
268 * @param p_attribute1 Descriptive flexfield segment.
269 * @param p_attribute2 Descriptive flexfield segment.
270 * @param p_attribute3 Descriptive flexfield segment.
271 * @param p_attribute4 Descriptive flexfield segment.
272 * @param p_attribute5 Descriptive flexfield segment.
273 * @param p_attribute6 Descriptive flexfield segment.
274 * @param p_attribute7 Descriptive flexfield segment.
275 * @param p_attribute8 Descriptive flexfield segment.
276 * @param p_attribute9 Descriptive flexfield segment.
277 * @param p_attribute10 Descriptive flexfield segment.
278 * @param p_attribute11 Descriptive flexfield segment.
279 * @param p_attribute12 Descriptive flexfield segment.
280 * @param p_attribute13 Descriptive flexfield segment.
281 * @param p_attribute14 Descriptive flexfield segment.
282 * @param p_attribute15 Descriptive flexfield segment.
283 * @param p_attribute16 Descriptive flexfield segment.
284 * @param p_attribute17 Descriptive flexfield segment.
285 * @param p_attribute18 Descriptive flexfield segment.
286 * @param p_attribute19 Descriptive flexfield segment.
287 * @param p_attribute20 Descriptive flexfield segment.
288 * @param p_attribute21 Descriptive flexfield segment.
289 * @param p_attribute22 Descriptive flexfield segment.
290 * @param p_attribute23 Descriptive flexfield segment.
291 * @param p_attribute24 Descriptive flexfield segment.
292 * @param p_attribute25 Descriptive flexfield segment.
293 * @param p_attribute26 Descriptive flexfield segment.
294 * @param p_attribute27 Descriptive flexfield segment.
295 * @param p_attribute28 Descriptive flexfield segment.
296 * @param p_attribute29 Descriptive flexfield segment.
297 * @param p_attribute30 Descriptive flexfield segment.
298 * @param p_attribute_category This context value determines which flexfield
299 * structure to use with the descriptive flexfield segments.
300 * @param p_object_version_number Identifies the current version number of the
301 * corps definition to be updated. When the API completes if p_validate is
305 * @param p_primary_prof_field_id Primary professional field shared type
302 * false, the process returns the new version number of the updated corps
303 * definition. If p_validate is true it returns the same value which was passed
304 * in
306 * identifier. Valid values are defined by 'PER_SHARED_TYPES' database table
307 * @param p_starting_grade_id The starting grade step for the civil servant on
308 * this corps. It should be the identifier of a step in existence as of the
309 * effective date
310 * @param p_ben_pgm_id {@rep:casecolumn PQH_CORPS_DEFINITIONS.BEN_PGM_ID}
311 * @param p_probation_period {@rep:casecolumn
312 * PQH_CORPS_DEFINITIONS.PROBATION_PERIOD}
313 * @param p_probation_units Units for probation period duration. Valid values
314 * are identified by lookup type 'PROC_PERIOD_TYPE'.
315 * @rep:displayname Update CORPS Definition
316 * @rep:category BUSINESS_ENTITY PQH_FR_CORPS
317 * @rep:category MISC_EXTENSIONS HR_USER_HOOKS
318 * @rep:scope public
319 * @rep:lifecycle active
320 * @rep:ihelp PER/@scalapi APIs in Oracle HRMS
321 */
322 --
323 -- {End Of Comments}
324 --
325 procedure update_corps_definition
326 (
327 p_validate in boolean default false
328 ,p_effective_date in date
329 ,p_corps_definition_id in number
330 ,p_business_group_id in number default hr_api.g_number
331 ,p_name in varchar2 default hr_api.g_varchar2
332 ,p_status_cd in varchar2 default hr_api.g_varchar2
333 ,p_retirement_age in number default hr_api.g_number
334 ,p_category_cd in varchar2 default hr_api.g_varchar2
335 ,p_starting_grade_step_id in number default hr_api.g_number
336 ,p_type_of_ps in varchar2 default hr_api.g_varchar2
337 ,p_corps_type_cd in varchar2 default hr_api.g_varchar2
338 ,p_date_from in date default hr_api.g_date
339 ,p_date_to in date default hr_api.g_date
340 ,p_recruitment_end_date in date default hr_api.g_date
341 ,p_task_desc in varchar2 default hr_api.g_varchar2
342 ,p_secondment_threshold in number default hr_api.g_number
343 ,p_normal_hours in number default hr_api.g_number
344 ,p_normal_hours_frequency in varchar2 default hr_api.g_varchar2
345 ,p_minimum_hours in number default hr_api.g_number
346 ,p_minimum_hours_frequency in varchar2 default hr_api.g_varchar2
347 ,p_attribute1 in varchar2 default hr_api.g_varchar2
348 ,p_attribute2 in varchar2 default hr_api.g_varchar2
349 ,p_attribute3 in varchar2 default hr_api.g_varchar2
350 ,p_attribute4 in varchar2 default hr_api.g_varchar2
351 ,p_attribute5 in varchar2 default hr_api.g_varchar2
352 ,p_attribute6 in varchar2 default hr_api.g_varchar2
353 ,p_attribute7 in varchar2 default hr_api.g_varchar2
354 ,p_attribute8 in varchar2 default hr_api.g_varchar2
355 ,p_attribute9 in varchar2 default hr_api.g_varchar2
356 ,p_attribute10 in varchar2 default hr_api.g_varchar2
357 ,p_attribute11 in varchar2 default hr_api.g_varchar2
358 ,p_attribute12 in varchar2 default hr_api.g_varchar2
359 ,p_attribute13 in varchar2 default hr_api.g_varchar2
360 ,p_attribute14 in varchar2 default hr_api.g_varchar2
361 ,p_attribute15 in varchar2 default hr_api.g_varchar2
362 ,p_attribute16 in varchar2 default hr_api.g_varchar2
363 ,p_attribute17 in varchar2 default hr_api.g_varchar2
364 ,p_attribute18 in varchar2 default hr_api.g_varchar2
365 ,p_attribute19 in varchar2 default hr_api.g_varchar2
366 ,p_attribute20 in varchar2 default hr_api.g_varchar2
367 ,p_attribute21 in varchar2 default hr_api.g_varchar2
368 ,p_attribute22 in varchar2 default hr_api.g_varchar2
369 ,p_attribute23 in varchar2 default hr_api.g_varchar2
370 ,p_attribute24 in varchar2 default hr_api.g_varchar2
371 ,p_attribute25 in varchar2 default hr_api.g_varchar2
372 ,p_attribute26 in varchar2 default hr_api.g_varchar2
373 ,p_attribute27 in varchar2 default hr_api.g_varchar2
374 ,p_attribute28 in varchar2 default hr_api.g_varchar2
375 ,p_attribute29 in varchar2 default hr_api.g_varchar2
376 ,p_attribute30 in varchar2 default hr_api.g_varchar2
377 ,p_attribute_category in varchar2 default hr_api.g_varchar2
378 ,p_object_version_number in out nocopy number
379 ,p_primary_prof_field_id in number default hr_api.g_number
380 ,p_starting_grade_id in number default hr_api.g_number
381 ,p_ben_pgm_id in number default hr_api.g_number
382 ,p_probation_period in number default hr_api.g_number
383 ,p_probation_units in varchar2 default hr_api.g_varchar2
384 );
385 --
386 -- ----------------------------------------------------------------------------
387 -- |-------------------------< delete_corps_definition >----------------------|
388 -- ----------------------------------------------------------------------------
389 --
390 -- {Start Of Comments}
391 /*#
392 * This API deletes corps definitions.
393 *
394 * The record is deleted from PQH_CORPS_DEFINITIONS table
395 *
396 * <p><b>Licensing</b><br>
397 * This API is licensed for use with Human Resources.
398 *
399 * <p><b>Prerequisites</b><br>
400 * This record should exist with the specified object version number. There
401 * should not be any placements on the corps. All the associated corps extra
402 * information record must have been deleted already.
403 *
404 * <p><b>Post Success</b><br>
405 * The corps definition record is deleted from the database.
406 *
407 * <p><b>Post Failure</b><br>
408 * The corps definition record is not deleted from the database and an error is
409 * raised
410 * @param p_validate If true, then validation alone will be performed and the
411 * database will remain unchanged. If false and all validation checks pass,
412 * then the database will be modified.
413 * @param p_corps_definition_id Unique corps definition identifier generated
414 * for this record when created as a Primary Key
415 * @param p_object_version_number Current version number of the corps
416 * definition to be deleted
417 * @rep:displayname Delete CORPS Definition
418 * @rep:category BUSINESS_ENTITY PQH_FR_CORPS
419 * @rep:category MISC_EXTENSIONS HR_USER_HOOKS
420 * @rep:scope public
421 * @rep:lifecycle active
422 * @rep:ihelp PER/@scalapi APIs in Oracle HRMS
423 */
424 --
425 -- {End Of Comments}
426 --
427 procedure delete_corps_definition
428 (
429 p_validate in boolean default false
430 ,p_corps_definition_id in number
431 ,p_object_version_number in number
432 );
433 --
434 end pqh_corps_definitions_api;