1 Package pay_cnu_bus1 AUTHID CURRENT_USER as
2 /* $Header: pycnurhi.pkh 120.0 2005/05/29 04:05:08 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |---------------------------< is_numeric >---------------------------------|
6 -- ----------------------------------------------------------------------------
7 -- {Start Of Comments}
8 --
9 -- Description:
10 -- This function validates that the caharacter passed to it is a numeric
11 -- ie 0 to 9.
12 --
13 -- Prerequisites:
14 --
15 -- In Parameter:
16 -- p_one_char
17 --
18 -- Post Success:
19 -- Processing continues. The function retruns 0.
20 --
21 -- Post Failure:
22 -- Processing continues. The function returns 1.
23 --
24 -- Developer Implementation Notes:
25 --
26 -- Access Status:
27 -- Internal Development Use Only.
28 --
29 -- {End Of Comments}
30 -- ----------------------------------------------------------------------------
31 function is_numeric (p_one_char in VARCHAR2) RETURN varchar2;
32 --
33 -- ----------------------------------------------------------------------------
34 -- |---------------------------< chk_element_name >---------------------------|
35 -- ----------------------------------------------------------------------------
36 -- {Start Of Comments}
37 --
38 -- Description:
39 -- This procedure validates the element_name is not null.
40 --
41 -- Prerequisites:
42 --
43 -- In Parameter:
44 -- p_element_name
45 --
46 -- Post Success:
47 -- Processing continues.
48 --
49 -- Post Failure:
50 -- An Error is raised and processing stops.
51 --
52 -- Developer Implementation Notes:
53 -- Only needs to be called from insert validate.
54 --
55 -- Access Status:
56 -- Internal Development Use Only. Insert Only.
57 --
58 -- {End Of Comments}
59 -- ----------------------------------------------------------------------------
60 Procedure chk_element_name (
61 p_element_name in pay_fr_contribution_usages.element_name%TYPE
62 );
63 -- ----------------------------------------------------------------------------
64 -- |---------------------------< chk_contribution_usage_type >----------------|
65 -- ----------------------------------------------------------------------------
66 -- {Start Of Comments}
67 --
68 -- Description:
69 -- This procedure validates the contribution_usage_type
70 -- An error is raised if the contribution_usage_type is not validated by the call
71 -- to hr_api.not_exists_in_hrstand_lookups
72 --
73 -- Prerequisites:
74 --
75 -- In Parameter:
76 -- p_effective_date - date at which the lookups must exist
77 -- p_contribution_usage_type - the lookup code that must exist
78 --
79 -- Post Success:
80 -- Processing continues.
81 --
82 -- Post Failure:
83 -- An Error is raised and processing stops.
84 --
85 -- Developer Implementation Notes:
86 -- Only needs to be called from insert validate.
87 --
88 -- Access Status:
89 -- Internal Development Use Only. Insert Only.
90 --
91 -- {End Of Comments}
92 -- ----------------------------------------------------------------------------
93 Procedure chk_contribution_usage_type (
94 p_effective_date in date
95 ,p_contribution_usage_type in pay_fr_contribution_usages.contribution_usage_type%TYPE
96 );
97 -- ----------------------------------------------------------------------------
98 -- |---------------------------< chk_rate_type >------------------------------|
99 -- ----------------------------------------------------------------------------
100 -- {Start Of Comments}
101 --
102 -- Description:
103 -- This procedure validates the rate_type
104 -- An error is raised if the rate_type is not validated by the call
105 -- to hr_api.not_exists_in_hrstand_lookups
106 --
107 -- Prerequisites:
108 --
109 -- In Parameter:
110 -- p_effective_date - date at which the lookups must exist
111 -- p_rate_type - the lookup code that must exist
112 --
113 -- Post Success:
114 -- Processing continues.
115 --
116 -- Post Failure:
117 -- An Error is raised and processing stops.
118 --
119 -- Developer Implementation Notes:
120 -- Only needs to be called from insert validate.
121 --
122 -- Access Status:
123 -- Internal Development Use Only. Insert Only.
124 --
125 -- {End Of Comments}
126 -- ----------------------------------------------------------------------------
127 Procedure chk_rate_type (
128 p_effective_date in date
129 ,p_rate_type in pay_fr_contribution_usages.rate_type%TYPE
130 );
131 -- ----------------------------------------------------------------------------
132 -- |---------------------------< chk_process_type >---------------------------|
133 -- ----------------------------------------------------------------------------
134 -- {Start Of Comments}
135 --
136 -- Description:
137 -- This procedure validates the process_type
138 -- An error is raised if the process type is not validated by the call
139 -- to hr_api.not_exists_in_hrstand_lookups
140 --
141 -- Prerequisites:
142 --
143 -- In Parameter:
144 -- p_effective_date - date at which the lookups must exist
145 -- p_process_type - the lookup code that must exist
146 --
147 -- Post Success:
148 -- Processing continues.
149 --
150 -- Post Failure:
151 -- An Error is raised and processing stops.
152 --
153 -- Developer Implementation Notes:
154 -- Only needs to be called from insert validate.
155 --
156 -- Access Status:
157 -- Internal Development Use Only. Insert Only.
158 --
159 -- {End Of Comments}
160 -- ----------------------------------------------------------------------------
161 Procedure chk_process_type (
162 p_effective_date in date
163 ,p_process_type in pay_fr_contribution_usages.process_type%TYPE
164 );
165 -- ----------------------------------------------------------------------------
166 -- |---------------------------< chk_lu_group_code >--------------------------|
167 -- ----------------------------------------------------------------------------
168 -- {Start Of Comments}
169 --
170 -- Description:
171 -- This procedure validates the group code
172 -- An error is raised if the group code is not validated by the call
173 -- to hr_api.not_exists_in_hrstand_lookups
174 --
175 -- Prerequisites:
176 --
177 -- In Parameter:
178 -- p_effective_date - date at which the lookups must exist
179 -- p_group_code - the lookup code that must exist
180 --
181 -- Post Success:
182 -- Processing continues.
183 --
184 -- Post Failure:
185 -- An Error is raised and processing stops.
186 --
187 -- Developer Implementation Notes:
188 -- Only needs to be called from insert validate.
189 --
190 -- Access Status:
191 -- Internal Development Use Only. Insert Only.
192 --
193 -- {End Of Comments}
194 -- ----------------------------------------------------------------------------
195 Procedure chk_lu_group_code (
196 p_effective_date in date
197 ,p_group_code in pay_fr_contribution_usages.group_code%TYPE
198 );
199 -- ----------------------------------------------------------------------------
200 -- |---------------------------< chk_business_group_id >-----------------------|
201 -- ----------------------------------------------------------------------------
202 -- {Start Of Comments}
203 --
204 -- Description:
205 -- This procedure checks that the GB is in a French Legislation, if the
206 -- BG is not null.
207 --
208 -- Prerequisites:
209 --
210 -- In Parameter:
211 -- p_business_group_id
212 --
213 -- Post Success:
214 -- Processing continues.
215 --
216 -- Post Failure:
217 -- An Error is raised and processing stops.
218 --
219 -- Developer Implementation Notes:
220 --
221 -- Access Status:
222 -- Internal Development Use Only. Insert Only.
223 --
224 -- {End Of Comments}
225 -- ----------------------------------------------------------------------------
226 Procedure chk_business_group_id (
227 p_business_group_id in pay_fr_contribution_usages.business_group_id%TYPE
228 );
229 -- ----------------------------------------------------------------------------
230 -- |---------------------------< chk_rate_category_type >---------------------|
231 -- ----------------------------------------------------------------------------
232 -- {Start Of Comments}
233 --
234 -- Description:
235 -- This procedure checks that the rate type and the rate category are consistent.
236 --
237 -- Prerequisites:
238 --
239 -- In Parameter:
240 -- p_rate_type
241 -- p_rate_category
242 --
243 -- Post Success:
244 -- processing continues.
245 --
246 -- Post Failure:
247 -- An Error is raised and processing stops.
248 --
249 -- Developer Implementation Notes:
250 --
251 -- Access Status:
252 -- Internal Development Use Only. Insert Only.
253 --
254 -- {End Of Comments}
255 -- ----------------------------------------------------------------------------
256 Procedure chk_rate_category_type (
257 p_rate_category in pay_fr_contribution_usages.rate_category%TYPE
258 ,p_rate_type in pay_fr_contribution_usages.rate_type%TYPE
259 );
260 -- ----------------------------------------------------------------------------
261 -- |---------------------------< chk_validate_code >---------------------------|
262 -- ----------------------------------------------------------------------------
263 -- {Start Of Comments}
264 --
265 -- Description:
266 -- This procedure checks that a contribution code and a code type are consistent.
267 --
268 -- Prerequisites:
269 -- The type has already been vaidated.
270 --
271 -- In Parameter:
272 -- p_code (either a retro or a normal code)
273 -- p_contribution_type (eg ARRCO, AGIRC..)
274 -- p_rate_category ('S', 'D'...)
275 -- Post Success:
276 -- Processing Continues
277 --
278 -- Post Failure:
279 -- An Error is raised and processing stops.
280 --
281 -- Developer Implementation Notes:
282 --
283 -- Access Status:
284 -- Internal Development Use Only.
285 --
286 -- {End Of Comments}
287 -- ----------------------------------------------------------------------------
288 Procedure chk_validate_code (
289 p_code in pay_fr_contribution_usages.contribution_code%TYPE
290 ,p_contribution_type in pay_fr_contribution_usages.contribution_type%TYPE
291 ,p_rate_category in pay_fr_contribution_usages.rate_category%TYPE
292 );
293 -- ----------------------------------------------------------------------------
294 -- |---------------------------< chk_contribution_type >----------------------|
295 -- ----------------------------------------------------------------------------
296 -- {Start Of Comments}
297 --
298 -- Description:
299 -- This procedure validates tha the contribution_type is correct
300 --
301 -- Prerequisites:
302 --
303 -- In Parameter:
304 -- p_contribution_type
305 --
306 -- Post Success:
307 -- Processing continues.
308 --
309 -- Post Failure:
310 -- An Error is raised and processing stops.
311 --
312 -- Developer Implementation Notes:
313 --
314 -- Access Status:
315 -- Internal Development Use Only. Insert test only.
316 --
317 -- {End Of Comments}
318 -- ----------------------------------------------------------------------------
319 Procedure chk_contribution_type (
320 p_contribution_type in pay_fr_contribution_usages.contribution_type%TYPE
321 );
322 -- ----------------------------------------------------------------------------
323 -- |---------------------------< chk_contribution_codes >----------------------|
324 -- ----------------------------------------------------------------------------
325 -- {Start Of Comments}
326 --
327 -- Description:
328 -- This procedure checks that a contribution code(either retro or normal)
329 -- conforms to the layout of the code type in p_contribution_type.
330 --
331 -- Prerequisites:
332 --
333 -- In Parameter:
334 -- p_contribution_usage_id
335 -- p_object_version_number
336 -- p_contribution_type in
337 -- p_contribution_code in
338 -- p_retro_contribution_code
339 --
340 -- Post Success:
341 -- Processing continues.
342 --
343 -- Post Failure:
344 -- An Error is raised and processing stops.
345 --
346 -- Developer Implementation Notes:
347 -- This procedure calls chk_validate_code to validate the codes (retro
348 -- or normal).
349 --
350 -- Access Status:
351 -- Internal Development Use Only.
352 --
353 -- {End Of Comments}
354 -- ----------------------------------------------------------------------------
355 Procedure chk_contribution_codes (
356 p_contribution_usage_id in pay_fr_contribution_usages.contribution_usage_id%TYPE
357 ,p_object_version_number in pay_fr_contribution_usages.object_version_number%TYPE
358 ,p_contribution_type in pay_fr_contribution_usages.contribution_type%TYPE
359 ,p_contribution_code in pay_fr_contribution_usages.contribution_code%TYPE
360 ,p_retro_contribution_code in pay_fr_contribution_usages.retro_contribution_code%TYPE
361 ,p_rate_category in pay_fr_contribution_usages.rate_category%TYPE
362 );
363 -- ----------------------------------------------------------------------------
364 -- |---------------------------< chk_group_code >------------------------------|
365 -- ----------------------------------------------------------------------------
366 -- {Start Of Comments}
367 --
368 -- Description:
369 -- This procedure validates the group code
370 -- There can only be one group_code for a combination of element_name, process_type
371 -- contribution_usage_type for :
372 -- if p_business_group_id is null, where bg is null
373 -- if p_business_group_id is not null, where bg = this bg, or bg is null
374 --
375 -- Prerequisites:
376 --
377 -- In Parameter:
378 -- p_group_code
379 -- p_process_type
380 -- p_element_name
381 -- p_contribution_usage_type
382 -- p_business_group_id
383 --
384 -- Post Success:
385 -- Processing continues.
386 --
387 -- Post Failure:
388 -- An Error is raised and processing stops.
389 --
390 -- Developer Implementation Notes:
391 --
392 -- Access Status:
393 -- Internal Development Use Only. Insert test only.
394 --
395 -- {End Of Comments}
396 -- ----------------------------------------------------------------------------
397 Procedure chk_group_code (
398 p_group_code in pay_fr_contribution_usages.group_code%TYPE
399 ,p_process_type in pay_fr_contribution_usages.process_type%TYPE
400 ,p_element_name in pay_fr_contribution_usages.element_name%TYPE
401 ,p_contribution_usage_type in pay_fr_contribution_usages.contribution_usage_type%TYPE
402 ,p_business_group_id in pay_fr_contribution_usages.business_group_id%TYPE
403 );
404 --
405 -- ----------------------------------------------------------------------------
406 -- |---------------------------< chk_dates >----------------------------------|
407 -- ----------------------------------------------------------------------------
408 -- {Start Of Comments}
409 --
410 -- Description:
411 -- There cannot be a duplicate of date_from, date_to, group_code, process_type
412 -- element_name, contribution_usage_type :
413 -- if p_business_group_id is null, where BG is null
414 -- if BG is not null, where BG = P_BG, and where BG is null
415 -- covering any period in the date_from -> date_to date range.
416 -- If p_date_to is null, use eot.
417 --
418 -- This can be called from insert (where ID and OVN are null)
419 -- or
423 -- Prerequisites:
420 -- from update, as date_to may have changed.
421 -- Only test if new insert, or date_to is changing.
422 --
424 --
425 -- In Parameter:
426 -- p_contribution_usage_id
427 -- p_object_version_number
428 -- p_date_from
429 -- p_date_to
430 -- p_group_code
431 -- p_process_type
432 -- p_element_name
433 -- p_contribution_usage_type
434 -- p_business_group_id
435 --
436 -- Post Success:
437 -- Processing continues.
438 --
439 -- Post Failure:
440 -- An Error is raised and processing stops.
441 --
442 -- Developer Implementation Notes:
443 --
444 -- Access Status:
445 -- Internal Development Use Only.
446 --
447 -- {End Of Comments}
448 -- ----------------------------------------------------------------------------
449 Procedure chk_dates (
450 p_contribution_usage_id in pay_fr_contribution_usages.contribution_usage_id%TYPE
451 ,p_object_version_number in pay_fr_contribution_usages.object_version_number%TYPE
452 ,p_date_from in pay_fr_contribution_usages.date_from%TYPE
453 ,p_date_to in pay_fr_contribution_usages.date_to%TYPE
454 ,p_group_code in pay_fr_contribution_usages.group_code%TYPE
455 ,p_process_type in pay_fr_contribution_usages.process_type%TYPE
456 ,p_element_name in pay_fr_contribution_usages.element_name%TYPE
457 ,p_contribution_usage_type in pay_fr_contribution_usages.contribution_usage_type%TYPE
458 ,p_business_group_id in pay_fr_contribution_usages.business_group_id%TYPE
459 );
460 --
461 -- ----------------------------------------------------------------------------
462 -- |---------------------------< LOAD_ROW >-----------------------------------|
463 -- ----------------------------------------------------------------------------
464 -- {Start Of Comments}
465 --
466 -- Description:
467 -- This procedure is called by the loader
468 -- It first tries to update the row, and if no data is found it tries to
469 -- insert the row.
470 -- If necessary the insert operation will adjust another row's end
471 -- date (for the same primary key) to put the existing row's date_to
472 -- to be the date before the new date_from.
473 -- This cannot call the row handlers, as they operate on the surrogate key
474 -- (ie contribution_usage_id)
475 -- and loaders operate on the primary key. This assumes the data was extracted from
476 -- a database, where the validation was performed.
477 --
478 -- Prerequisites:
479 --
480 -- In Parameter:
481 -- p_date_from
482 -- p_date_to
483 -- p_group_code
484 -- p_process_type
485 -- p_element_name
486 -- p_contribution_usage_type
487 -- p_rate_type
488 -- p_rate_category
489 -- p_contribution_code
490 -- p_contribution_type
491 -- p_retro_contribution_code
492 --
493 -- Post Success:
494 -- The row is udtated, or inserted.
495 --
496 -- Post Failure:
497 -- An Error is raised and processing stops.
498 --
499 -- Developer Implementation Notes:
500 -- Only to be used by the loader.
501 --
502 -- Access Status:
503 -- Only to be used by the loader.
504 --
505 -- {End Of Comments}
506 -- ----------------------------------------------------------------------------
507 Procedure load_row (
508 p_date_from in varchar2
509 ,p_date_to in varchar2
510 ,p_group_code in pay_fr_contribution_usages.group_code%TYPE
511 ,p_process_type in pay_fr_contribution_usages.process_type%TYPE
512 ,p_element_name in pay_fr_contribution_usages.element_name%TYPE
513 ,p_contribution_usage_type in pay_fr_contribution_usages.contribution_usage_type%TYPE
514 ,p_rate_type in pay_fr_contribution_usages.rate_type%TYPE
515 ,p_rate_category in pay_fr_contribution_usages.rate_category%TYPE
516 ,p_contribution_code in pay_fr_contribution_usages.contribution_code%TYPE
517 ,p_contribution_type in pay_fr_contribution_usages.contribution_type%TYPE
518 ,p_retro_contribution_code in pay_fr_contribution_usages.retro_contribution_code%TYPE
519 ,p_code_rate_id in pay_fr_contribution_usages.code_Rate_id%TYPE
520 );
521 --
522 -- ----------------------------------------------------------------------------
523 -- |---------------------------< chk_code_rate_id >----------------------------|
524 -- ----------------------------------------------------------------------------
525 -- {Start Of Comments}
526 --
527 -- Description:
528 -- There cannot be more than 1 CODE_RATE_ID for a given contribution code /
529 -- rate type / business group combination
530 --
531 -- Rate_categories of 'W' and 'T' must not have code_rate_IDs.
532 --
533 -- code_rate_Ids must not be specified for Business_group rows, and must not
534 -- be specified for business_group rows.
535 --
536 -- Business group rows must have code_Rate_ids <=50 and >=30.
537 -- Non-Business group rows must have code_Rate_ids >= 0 and <30.
538 --
539 -- This can be called from insert only
540 --
541 -- Prerequisites:
542 --
543 -- In Parameter:
544 -- p_contribution_code
545 -- p_object_version_number
546 -- p_rate_type
547 -- p_rate_category
548 -- p_business_group_id
549 --
550 -- Post Success:
551 -- Processing continues.
552 --
553 -- Post Failure:
554 -- An Error is raised and processing stops.
555 --
556 -- Developer Implementation Notes:
557 --
558 -- Access Status:
559 -- Internal Development Use Only.
560 --
561 -- {End Of Comments}
562 -- ----------------------------------------------------------------------------
563 Procedure chk_code_rate_id (
564 p_code_rate_id in out nocopy pay_fr_contribution_usages.code_rate_id%TYPE
565 ,p_contribution_code in pay_fr_contribution_usages.contribution_code%TYPE
566 ,p_business_group_id in pay_fr_contribution_usages.business_group_id%TYPE
567 ,p_rate_type in pay_fr_contribution_usages.rate_type%TYPE
568 ,p_rate_category in pay_fr_contribution_usages.rate_category%TYPE
569 );
570 end pay_cnu_bus1;
[Home] [Help]