[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_DIMENSION_GENERATOR_PKG
Source
1 package body pay_jp_dimension_generator_pkg as
2 /* $Header: pyjpdimg.pkb 120.0 2006/04/24 00:02 ttagawa noship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := 'pay_jp_dimension_generator_pkg.';
7 -- ----------------------------------------------------------------------------
8 -- |------------------------------< start_date >------------------------------|
9 -- ----------------------------------------------------------------------------
10 function start_date(
11 p_effective_date in date,
12 p_reset_date in date,
13 p_frequency_type in varchar2,
14 p_frequency in number) return date
15 is
16 l_start_date date;
17 l_start_date_temp date;
18 begin
19 if p_frequency_type = 'DAY' then
20 l_start_date := p_reset_date + floor((p_effective_date - p_reset_date) / p_frequency) * p_frequency;
21 elsif p_frequency_type = 'SMONTH' then
22 if mod(p_frequency, 2) = 0 then
23 l_start_date := start_date(p_effective_date, p_reset_date, 'MONTH', p_frequency / 2);
24 else
25 l_start_date := start_date(p_effective_date, p_reset_date, 'MONTH', p_frequency);
26 l_start_date_temp := add_months(l_start_date, floor(p_frequency / 2)) + 15;
27 if p_effective_date >= l_start_date_temp then
28 l_start_date := l_start_date_temp;
29 end if;
30 end if;
31 elsif p_frequency_type = 'MONTH' then
32 l_start_date := add_months(p_reset_date, floor(months_between(p_effective_date, p_reset_date) / p_frequency) * p_frequency);
33 else
34 fnd_message.set_name('PAY', 'PAY_JP_DIM_INVALID_FREQ_TYPE');
35 fnd_message.set_token('FREQUENCY_TYPE', p_frequency_type);
36 fnd_message.raise_error;
37 end if;
38 --
39 return l_start_date;
40 end start_date;
41 -- ----------------------------------------------------------------------------
42 -- |-------------------------------< end_date >-------------------------------|
43 -- ----------------------------------------------------------------------------
44 function end_date(
45 p_effective_date in date,
46 p_reset_date in date,
47 p_frequency_type in varchar2,
48 p_frequency in number) return date
49 is
50 l_start_date date;
51 l_end_date date;
52 begin
53 if p_frequency_type = 'DAY' then
54 l_end_date := start_date(p_effective_date, p_reset_date, p_frequency_type, p_frequency) + p_frequency - 1;
55 elsif p_frequency_type = 'SMONTH' then
56 if mod(p_frequency, 2) = 0 then
57 l_end_date := end_date(p_effective_date, p_reset_date, 'MONTH', p_frequency / 2);
58 else
59 l_start_date := start_date(p_effective_date, p_reset_date, 'MONTH', p_frequency);
60 l_end_date := add_months(l_start_date, floor(p_frequency / 2)) + 14;
61 if p_effective_date > l_end_date then
62 l_end_date := add_months(l_start_date, p_frequency) - 1;
63 end if;
64 end if;
65 elsif p_frequency_type = 'MONTH' then
66 l_end_date := add_months(start_date(p_effective_date, p_reset_date, p_frequency_type, p_frequency), p_frequency) - 1;
67 else
68 fnd_message.set_name('PAY', 'PAY_JP_DIM_INVALID_FREQ_TYPE');
69 fnd_message.set_token('FREQUENCY_TYPE', p_frequency_type);
70 fnd_message.raise_error;
71 end if;
72 --
73 return l_end_date;
74 end end_date;
75 -- ----------------------------------------------------------------------------
76 -- |------------------------------< my_replace >------------------------------|
77 -- ----------------------------------------------------------------------------
78 function my_replace(
79 p_src in varchar2,
80 p_old in varchar2,
81 p_new in date) return varchar2
82 is
83 begin
84 return replace(p_src, p_old, fnd_date.date_to_canonical(p_new));
85 end my_replace;
86 -- ----------------------------------------------------------------------------
87 -- |------------------------------< my_replace >------------------------------|
88 -- ----------------------------------------------------------------------------
89 function my_replace(
90 p_src in varchar2,
91 p_old in varchar2,
92 p_new in number) return varchar2
93 is
94 begin
95 return replace(p_src, p_old, fnd_number.number_to_canonical(p_new));
96 end my_replace;
97 -- ----------------------------------------------------------------------------
98 -- |------------------------------< my_replace >------------------------------|
99 -- ----------------------------------------------------------------------------
100 function my_replace(
101 p_src in varchar2,
102 p_old in varchar2,
103 p_new in boolean) return varchar2
104 is
105 begin
106 if p_new then
107 return replace(p_src, p_old, 'Y');
108 else
109 return replace(p_src, p_old, 'N');
110 end if;
111 end my_replace;
112 -- ----------------------------------------------------------------------------
113 -- |-------------------------< validate_parameters >--------------------------|
114 -- ----------------------------------------------------------------------------
115 procedure validate_parameters(
116 p_date_type in varchar2,
117 p_reset_date in date,
118 p_frequency_type in varchar2,
119 p_frequency in number)
120 is
121 c_proc constant varchar2(61) := c_package || 'validate_parameters';
122 begin
123 hr_api.mandatory_arg_error(
124 p_api_name => c_proc,
125 p_argument => 'date_type',
126 p_argument_value => p_date_type);
127 hr_api.mandatory_arg_error(
128 p_api_name => c_proc,
129 p_argument => 'reset_date',
130 p_argument_value => p_reset_date);
131 hr_api.mandatory_arg_error(
132 p_api_name => c_proc,
133 p_argument => 'frequency_type',
134 p_argument_value => p_frequency_type);
135 hr_api.mandatory_arg_error(
136 p_api_name => c_proc,
137 p_argument => 'frequency',
138 p_argument_value => p_frequency);
139 --
140 if p_date_type not in ('DP', 'DE') then
141 fnd_message.set_name('PAY', 'PAY_JP_DIM_INVALID_DATE_TYPE');
142 fnd_message.set_token('DATE_TYPE', p_date_type);
143 fnd_message.raise_error;
144 end if;
145 --
146 if p_reset_date <> trunc(p_reset_date) then
147 fnd_message.set_name('PAY', 'PAY_JP_DIM_INVALID_RESET_DATE');
148 fnd_message.set_token('RESET_DATE', fnd_date.date_to_chardt(p_reset_date));
149 fnd_message.raise_error;
150 end if;
151 --
152 if p_frequency_type not in ('DAY', 'SMONTH', 'MONTH') then
153 fnd_message.set_name('PAY', 'PAY_JP_DIM_INVALID_FREQ_TYPE');
154 fnd_message.set_token('FREQUENCY_TYPE', p_frequency_type);
155 fnd_message.raise_error;
156 end if;
157 --
158 if not (p_frequency between 1 and 99999)
159 or p_frequency <> trunc(p_frequency) then
160 fnd_message.set_name('PAY', 'PAY_JP_DIM_INVALID_FREQUENCY');
161 fnd_message.set_token('FREQUENCY', p_frequency);
162 fnd_message.raise_error;
163 end if;
164 end validate_parameters;
165 -- ----------------------------------------------------------------------------
166 -- |------------------------------< utilities >-------------------------------|
167 -- ----------------------------------------------------------------------------
168 function get_description(
169 p_date_type in varchar2,
170 p_reset_date in date,
171 p_frequency_type in varchar2,
172 p_frequency in number,
173 p_exclude_reversal in boolean) return varchar2
174 is
175 l_description varchar2(255);
176 begin
177 l_description := 'DATE_TYPE=<DATE_TYPE> RESET_DATE=<RESET_DATE> FREQUENCY_TYPE=<FREQUENCY_TYPE> FREQUENCY=<FREQUENCY> EXCLUDE_REVERSAL=<EXCLUDE_REVERSAL>';
178 l_description := replace(l_description, '<DATE_TYPE>', p_date_type);
179 l_description := my_replace(l_description, '<RESET_DATE>', p_reset_date);
180 l_description := replace(l_description, '<FREQUENCY_TYPE>', p_frequency_type);
181 l_description := my_replace(l_description, '<FREQUENCY>', p_frequency);
182 l_description := my_replace(l_description, '<EXCLUDE_REVERSAL>', p_exclude_reversal);
183 --
184 -- dbms_output.put_line(l_description);
185 --
186 return l_description;
187 end get_description;
188 --
189 function get_expiry_checking_code(
190 p_date_type in varchar2,
191 p_reset_date in date,
192 p_frequency_type in varchar2,
193 p_frequency in number) return varchar2
194 is
195 l_expiry_checking_code varchar2(255);
196 begin
197 l_expiry_checking_code := 'PAY_JP_DYNAMIC_DIMENSION_PKG.<DATE_TYPE>_<RESET_DATE>_<FREQUENCY_TYPE>_<FREQUENCY>_EC';
198 l_expiry_checking_code := replace(l_expiry_checking_code, '<DATE_TYPE>', p_date_type);
199 l_expiry_checking_code := replace(l_expiry_checking_code, '<RESET_DATE>', to_char(p_reset_date, 'YYYYMMDD'));
200 l_expiry_checking_code := replace(l_expiry_checking_code, '<FREQUENCY_TYPE>', p_frequency_type);
201 l_expiry_checking_code := my_replace(l_expiry_checking_code, '<FREQUENCY>', p_frequency);
202 --
203 -- dbms_output.put_line(l_expiry_checking_code);
204 --
205 return l_expiry_checking_code;
206 end get_expiry_checking_code;
207 --
208 function get_start_date_code(
209 p_date_type in varchar2,
210 p_reset_date in date,
211 p_frequency_type in varchar2,
212 p_frequency in number) return varchar2
213 is
214 l_start_date_code varchar2(255);
215 begin
216 l_start_date_code := 'PAY_JP_DYNAMIC_DIMENSION_PKG.<DATE_TYPE>_<RESET_DATE>_<FREQUENCY_TYPE>_<FREQUENCY>_SD';
217 l_start_date_code := replace(l_start_date_code, '<DATE_TYPE>', p_date_type);
218 l_start_date_code := replace(l_start_date_code, '<RESET_DATE>', to_char(p_reset_date, 'YYYYMMDD'));
219 l_start_date_code := replace(l_start_date_code, '<FREQUENCY_TYPE>', p_frequency_type);
220 l_start_date_code := my_replace(l_start_date_code, '<FREQUENCY>', p_frequency);
221 --
222 -- dbms_output.put_line(l_start_date_code);
223 --
224 return l_start_date_code;
225 end get_start_date_code;
226 --
227 function get_route_name(
228 p_route_type in varchar2,
229 p_date_type in varchar2,
230 p_reset_date in date,
231 p_frequency_type in varchar2,
232 p_frequency in number,
233 p_exclude_reversal in boolean) return varchar2
234 is
235 l_route_name varchar2(255);
236 begin
237 l_route_name := 'JP_ASG_<DATE_TYPE>_<RESET_DATE>_<FREQUENCY_TYPE>_<FREQUENCY>_<ROUTE_TYPE>_BALANCE_DIMENSION';
238 l_route_name := replace(l_route_name, '<DATE_TYPE>', p_date_type);
239 l_route_name := replace(l_route_name, '<RESET_DATE>', to_char(p_reset_date, 'YYYYMMDD'));
240 l_route_name := replace(l_route_name, '<FREQUENCY_TYPE>', p_frequency_type);
241 l_route_name := my_replace(l_route_name, '<FREQUENCY>', p_frequency);
242 l_route_name := replace(l_route_name, '<ROUTE_TYPE>', p_route_type);
243 if p_exclude_reversal then
244 l_route_name := l_route_name || '_EXC_REV';
245 end if;
246 --
247 -- dbms_output.put_line(l_route_name);
248 --
249 return l_route_name;
250 end get_route_name;
251 --
252 function get_template_route_name(
253 p_route_type in varchar2,
254 p_date_type in varchar2,
255 p_exclude_reversal in boolean) return varchar2
256 is
257 l_template_route_name varchar2(255);
258 begin
259 l_template_route_name := 'JP_ASG_<DATE_TYPE>_<ROUTE_TYPE>_BALANCE_DIMENSION';
260 l_template_route_name := replace(l_template_route_name, '<DATE_TYPE>', p_date_type);
261 l_template_route_name := replace(l_template_route_name, '<ROUTE_TYPE>', p_route_type);
262 if p_exclude_reversal then
263 l_template_route_name := l_template_route_name || '_EXC_REV';
264 end if;
265 l_template_route_name := l_template_route_name || '_TEMPLATE';
266 --
267 -- dbms_output.put_line(l_template_route_name);
268 --
269 return l_template_route_name;
270 end get_template_route_name;
271
272 function get_route_id(
273 p_route_name in varchar2,
274 p_raise_when_no_data_found boolean default true) return number
275 is
276 l_route_id number;
277 begin
278 select route_id
279 into l_route_id
280 from ff_routes
281 where route_name = p_route_name;
282 --
283 return l_route_id;
284 exception
285 when no_data_found then
286 if not p_raise_when_no_data_found then
287 return null;
288 else
289 raise;
290 end if;
291 end get_route_id;
292 -- ----------------------------------------------------------------------------
293 -- |-----------------------< upload_balance_dimension >-----------------------|
294 -- ----------------------------------------------------------------------------
295 procedure upload_balance_dimension(
296 p_balance_dimension_id in out nocopy number,
297 p_dimension_name in varchar2,
298 p_database_item_suffix in varchar2,
299 p_business_group_id in number,
300 p_date_type in varchar2,
301 p_reset_date in date,
302 p_frequency_type in varchar2,
303 p_frequency in number,
304 p_exclude_reversal in boolean)
305 is
306 l_route_id number;
307 l_description pay_balance_dimensions.description%type;
308 l_dimension_type pay_balance_dimensions.dimension_type%type;
309 l_expiry_checking_level pay_balance_dimensions.expiry_checking_level%type;
310 l_expiry_checking_code pay_balance_dimensions.expiry_checking_code%type;
311 l_period_type pay_balance_dimensions.period_type%type;
312 l_start_date_code pay_balance_dimensions.start_date_code%type;
313 begin
314 l_route_id := get_route_id('Core Balance Route No Contexts');
315 l_description := get_description(p_date_type, p_reset_date, p_frequency_type, p_frequency, p_exclude_reversal);
316 --
317 if p_date_type = 'DE' then
318 --
319 -- It is possible to support balance feeding of in-memory run results while running payroll run
320 -- for DATE_EARNED dimension using feed_checking_type = 'F'(Full PL/SQL feed checking).
324 -- Expiry checking for DATE_EARNED is supported by PYUGEN using expiry_checking_level = 'E'(Enhanced).
321 -- But full PL/SQL feed checking will cause severe performance loss against whole Payroll Run,
322 -- so balance feeding for DATE_EARNED dimension is de-supported at the moment.
323 --
325 -- New expiry_information "Previous Period"(2), "Current Period"(3) and "Rollover Expiry"(4)
326 -- can be used in this case. But feed checking is not supported for DATE_EARNED,
327 -- it is meaningless to support these expiry checking because current run results
328 -- are not added up to latest balance, which means latest balance is never expired.
329 --
330 -- Run balance mechanism is not supported for the following reasons.
331 -- 1. Current run results are not added up while running Payroll Run,
332 -- so PYUGEN cannot derive ASG_RUN value until in-memory run results
333 -- are flashed into DB.
334 -- 2. PAY_RUN_BALANCES table does not have DATE_EARNED column.
335 -- To derive DATE_EARNED, it is required to join additional
336 -- PAY_ASSIGNMENT_ACTIONS and PAY_PAYROLL_ACTIONS, which is
337 -- similar to Run Result route. This is nonsense.
338 -- 3. Validation using PAY_BALANCE_VALIDATION is not DATE_EARNED compliant
339 -- (pay_balance_pkg).
340 -- For these reasons, run balance is not supported for DATE_EARNED dimensions.
341 -- Intead of than, bulk get_value mechanism (RR route) is supported.
342 --
343 l_dimension_type := 'N';
344 else
345 l_dimension_type := 'A';
346 l_expiry_checking_level := 'P';
347 l_expiry_checking_code := get_expiry_checking_code(p_date_type, p_reset_date, p_frequency_type, p_frequency);
348 end if;
349 --
350 -- Following columns are populated not only for DP but also DE dimensions.
351 -- The reason to populate these columns for DE dimensions is to support get_value date mode
352 -- in pay_jp_balance_view_pkg.get_value. No SRW route is available for DE dimensions,
353 -- so there's no impact for run balance functionality.
354 --
355 l_period_type := 'DYNAMIC';
356 l_start_date_code := get_start_date_code(p_date_type, p_reset_date, p_frequency_type, p_frequency);
357 --
358 if p_balance_dimension_id is null then
359 select pay_balance_dimensions_s.nextval
360 into p_balance_dimension_id
361 from dual;
362 --
363 insert into pay_balance_dimensions(
364 BALANCE_DIMENSION_ID,
365 DIMENSION_NAME,
366 DATABASE_ITEM_SUFFIX,
367 BUSINESS_GROUP_ID,
368 LEGISLATION_CODE,
369 DESCRIPTION,
370 PAYMENTS_FLAG,
371 DIMENSION_TYPE,
372 EXPIRY_CHECKING_LEVEL,
373 EXPIRY_CHECKING_CODE,
374 FEED_CHECKING_TYPE,
375 FEED_CHECKING_CODE,
376 ROUTE_ID,
377 DATABASE_ITEM_FUNCTION,
378 DIMENSION_LEVEL,
379 ASG_ACTION_BALANCE_DIM_ID,
380 SAVE_RUN_BALANCE_ENABLED,
381 PERIOD_TYPE,
382 START_DATE_CODE)
383 values( p_balance_dimension_id,
384 p_dimension_name,
385 p_database_item_suffix,
386 p_business_group_id,
387 null,
388 l_description,
389 'N',
390 l_dimension_type,
391 l_expiry_checking_level,
392 l_expiry_checking_code,
393 null,
394 null,
395 l_route_id,
396 'Y',
397 'ASG',
398 null,
399 'N',
400 l_period_type,
401 l_start_date_code);
402 else
403 update pay_balance_dimensions
404 set description = l_description,
405 payments_flag = 'N',
406 dimension_type = l_dimension_type,
407 expiry_checking_level = l_expiry_checking_level,
408 expiry_checking_code = l_expiry_checking_code,
412 database_item_function = 'Y',
409 feed_checking_type = null,
410 feed_checking_code = null,
411 route_id = l_route_id,
413 dimension_level = 'ASG',
414 asg_action_balance_dim_id = null,
415 save_run_balance_enabled = 'N',
416 period_type = l_period_type,
417 start_date_code = l_start_date_code
418 where balance_dimension_id = p_balance_dimension_id;
419 --
420 if sql%rowcount <> 1 then
421 raise no_data_found;
422 end if;
423 end if;
424 end upload_balance_dimension;
425 -- ----------------------------------------------------------------------------
426 -- |-----------------------------< create_route >-----------------------------|
427 -- ----------------------------------------------------------------------------
428 procedure create_route(
429 p_route_type in varchar2,
430 p_date_type in varchar2,
431 p_reset_date in date,
432 p_frequency_type in varchar2,
433 p_frequency in number,
434 p_exclude_reversal in boolean,
435 p_route_id out nocopy number)
436 is
437 l_route_name varchar2(255);
438 l_template_route_name varchar2(255);
439 l_template_route_id number;
440 l_optimizer_hint ff_routes.optimizer_hint%type;
441 l_text varchar2(32767);
442 l_description ff_routes.description%type;
443 begin
444 l_route_name := get_route_name(p_route_type, p_date_type, p_reset_date, p_frequency_type, p_frequency, p_exclude_reversal);
445 p_route_id := get_route_id(l_route_name, false);
446 --
447 if p_route_id is not null then
448 return;
449 end if;
450 --
451 l_template_route_name := get_template_route_name(p_route_type, p_date_type, p_exclude_reversal);
452 --
453 select route_id,
454 optimizer_hint,
455 text
456 into l_template_route_id,
457 l_optimizer_hint,
458 l_text
459 from ff_routes
460 where route_name = l_template_route_name;
461 --
462 l_text := my_replace(l_text, '<RESET_DATE>', p_reset_date);
463 l_text := replace(l_text, '<FREQUENCY_TYPE>', p_frequency_type);
464 l_text := my_replace(l_text, '<FREQUENCY>', p_frequency);
465 --
466 l_description := p_route_type || ' Balance Dimension Route for ' ||
467 get_description(p_date_type, p_reset_date, p_frequency_type, p_frequency, p_exclude_reversal);
468 --
469 select ff_routes_s.nextval
470 into p_route_id
471 from dual;
472 --
473 -- Set user_defined_flag = 'Y'
474 --
475 insert into ff_routes(
476 ROUTE_ID,
477 ROUTE_NAME,
478 USER_DEFINED_FLAG,
479 DESCRIPTION,
480 OPTIMIZER_HINT,
481 TEXT)
482 values( p_route_id,
483 l_route_name,
484 'Y',
485 l_description,
486 l_optimizer_hint,
487 l_text);
488 --
489 insert into ff_route_context_usages(
490 ROUTE_ID,
491 CONTEXT_ID,
492 SEQUENCE_NO)
493 select p_route_id,
494 context_id,
495 sequence_no
496 from ff_route_context_usages
497 where route_id = l_template_route_id;
498 --
499 insert into ff_route_parameters(
500 ROUTE_PARAMETER_ID,
504 SEQUENCE_NO)
501 ROUTE_ID,
502 DATA_TYPE,
503 PARAMETER_NAME,
505 select ff_route_parameters_s.nextval,
506 p_route_id,
507 data_type,
508 parameter_name,
509 sequence_no
510 from ff_route_parameters
511 where route_id = l_template_route_id;
512 end create_route;
513 -- ----------------------------------------------------------------------------
514 -- |------------------------< create_dimension_route >------------------------|
515 -- ----------------------------------------------------------------------------
516 procedure create_dimension_route(
517 p_balance_dimension_id in number,
518 p_priority in number,
519 p_route_type in varchar2,
520 p_date_type in varchar2,
521 p_reset_date in date,
522 p_frequency_type in varchar2,
523 p_frequency in number,
524 p_exclude_reversal in boolean)
525 is
526 l_route_id number;
527 l_balance_type_column pay_dimension_routes.balance_type_column%type;
528 l_run_dimension_id number;
529 begin
530 if p_route_type = 'SRB' and p_date_type = 'DE' then
531 fnd_message.set_name('PAY', 'PAY_JP_DIM_DE_SRB_NOT_SUPPORT');
532 fnd_message.raise_error;
533 end if;
534 --
535 create_route(
536 p_route_type => p_route_type,
537 p_date_type => p_date_type,
538 p_reset_date => p_reset_date,
539 p_frequency_type => p_frequency_type,
540 p_frequency => p_frequency,
541 p_exclude_reversal => p_exclude_reversal,
542 p_route_id => l_route_id);
543 --
544 if p_route_type = 'RR' then
545 l_balance_type_column := 'FEED.balance_type_id';
546 else
547 select balance_dimension_id
548 into l_run_dimension_id
549 from pay_balance_dimensions
550 where dimension_name = '_ASG_RUN'
551 and legislation_code = 'JP';
552 end if;
553 --
554 insert into pay_dimension_routes(
555 BALANCE_DIMENSION_ID,
556 PRIORITY,
557 ROUTE_TYPE,
558 ROUTE_ID,
559 BALANCE_TYPE_COLUMN,
560 RUN_DIMENSION_ID,
561 OBJECT_VERSION_NUMBER)
562 values( p_balance_dimension_id,
563 p_priority,
564 p_route_type,
565 l_route_id,
566 l_balance_type_column,
567 l_run_dimension_id,
568 1);
569 end create_dimension_route;
570 -- ----------------------------------------------------------------------------
571 -- |--------------------------< chk_dimension_name >--------------------------|
572 -- ----------------------------------------------------------------------------
573 procedure chk_dimension_name(
574 p_dimension_name in varchar2,
575 p_business_group_id in number)
576 is
577 c_proc constant varchar2(61) := c_package || 'chk_dimension_name';
578 l_count number;
579 begin
580 hr_api.mandatory_arg_error(
581 p_api_name => c_proc,
582 p_argument => 'dimension_name',
583 p_argument_value => p_dimension_name);
584 --
585 select count(*)
586 into l_count
587 from pay_balance_dimensions
588 where replace(upper(dimension_name), ' ', '_') = replace(upper(p_dimension_name), ' ', '_')
589 and nvl(business_group_id, p_business_group_id) = p_business_group_id
590 and nvl(legislation_code, 'JP') = 'JP';
591 --
592 if l_count > 0 then
593 fnd_message.set_name('PAY', 'PAY_JP_DIM_DUP_DIM_NAME');
594 fnd_message.set_token('DIMENSION_NAME', p_dimension_name);
595 fnd_message.raise_error;
596 end if;
597 end chk_dimension_name;
598 -- ----------------------------------------------------------------------------
599 -- |-----------------------< chk_database_item_suffix >-----------------------|
600 -- ----------------------------------------------------------------------------
601 procedure chk_database_item_suffix(
602 p_database_item_suffix in varchar2,
603 p_business_group_id in number)
604 is
605 c_proc constant varchar2(61) := c_package || 'chk_database_item_suffix';
606 l_database_item_suffix pay_balance_dimensions.database_item_suffix%type;
607 l_dummy varchar2(1);
608 l_count number;
609 begin
610 hr_api.mandatory_arg_error(
611 p_api_name => c_proc,
612 p_argument => 'database_item_suffix',
613 p_argument_value => p_database_item_suffix);
614 --
615 -- If suffix starts with "_", checkformat will fail.
616 -- Following code is to remove preceding underscores.
617 --
618 l_database_item_suffix := replace(p_database_item_suffix, '_');
619 --
620 hr_chkfmt.checkformat(
621 value => l_database_item_suffix,
622 format => 'PAY_NAME',
623 output => l_database_item_suffix,
624 minimum => null,
625 maximum => null,
626 nullok => 'N',
627 rgeflg => l_dummy,
628 curcode => null);
629 --
633 where replace(upper(database_item_suffix), ' ', '_') = replace(upper(p_database_item_suffix), ' ', '_')
630 select count(*)
631 into l_count
632 from pay_balance_dimensions
634 and nvl(business_group_id, p_business_group_id) = p_business_group_id
635 and nvl(legislation_code, 'JP') = 'JP';
636 --
637 if l_count > 0 then
638 fnd_message.set_name('PAY', 'PAY_JP_DIM_DUP_DBI_SUFFIX');
639 fnd_message.set_token('DATABASE_ITEM_SUFFIX', p_database_item_suffix);
640 fnd_message.raise_error;
641 end if;
642 end chk_database_item_suffix;
643 -- ----------------------------------------------------------------------------
644 -- |-----------------------< create_balance_dimension >-----------------------|
645 -- ----------------------------------------------------------------------------
646 procedure create_balance_dimension(
647 p_dimension_name in varchar2,
648 p_database_item_suffix in varchar2,
649 p_business_group_id in number,
650 p_date_type in varchar2,
651 p_reset_date in date,
652 p_frequency_type in varchar2,
653 p_frequency in number,
654 p_exclude_reversal in boolean,
655 p_balance_dimension_id out nocopy number,
656 p_rebuild_package in boolean default true)
657 is
658 c_proc constant varchar2(61) := c_package || 'create_balance_dimension';
659 begin
660 hr_api.mandatory_arg_error(
661 p_api_name => c_proc,
662 p_argument => 'business_group_id',
663 p_argument_value => p_business_group_id);
664 --
665 chk_dimension_name(p_dimension_name, p_business_group_id);
666 chk_database_item_suffix(p_database_item_suffix, p_business_group_id);
667 --
668 validate_parameters(
669 p_date_type => p_date_type,
670 p_reset_date => p_reset_date,
671 p_frequency_type => p_frequency_type,
672 p_frequency => p_frequency);
673 --
674 upload_balance_dimension(
675 p_balance_dimension_id => p_balance_dimension_id,
676 p_dimension_name => p_dimension_name,
677 p_database_item_suffix => p_database_item_suffix,
678 p_business_group_id => p_business_group_id,
679 p_date_type => p_date_type,
680 p_reset_date => p_reset_date,
681 p_frequency_type => p_frequency_type,
682 p_frequency => p_frequency,
683 p_exclude_reversal => p_exclude_reversal);
684 --
685 if p_date_type = 'DP' then
686 create_dimension_route(
687 p_balance_dimension_id => p_balance_dimension_id,
688 p_priority => 1,
689 p_route_type => 'SRB',
690 p_date_type => p_date_type,
691 p_reset_date => p_reset_date,
692 p_frequency_type => p_frequency_type,
693 p_frequency => p_frequency,
694 p_exclude_reversal => p_exclude_reversal);
695 --
696 create_dimension_route(
697 p_balance_dimension_id => p_balance_dimension_id,
698 p_priority => 2,
699 p_route_type => 'RR',
700 p_date_type => p_date_type,
701 p_reset_date => p_reset_date,
702 p_frequency_type => p_frequency_type,
703 p_frequency => p_frequency,
704 p_exclude_reversal => p_exclude_reversal);
705 else
706 create_dimension_route(
707 p_balance_dimension_id => p_balance_dimension_id,
708 p_priority => 1,
709 p_route_type => 'RR',
710 p_date_type => p_date_type,
711 p_reset_date => p_reset_date,
712 p_frequency_type => p_frequency_type,
713 p_frequency => p_frequency,
714 p_exclude_reversal => p_exclude_reversal);
715 end if;
716 --
717 -- Rebuild Package of expiry_checking_code/start_date_code.
718 --
719 if p_rebuild_package then
720 rebuild_package;
721 end if;
722 end create_balance_dimension;
723 --
724 procedure create_balance_dimension(
725 errbuf out nocopy varchar2,
726 retcode out nocopy varchar2,
727 p_dimension_name in varchar2,
728 p_database_item_suffix in varchar2,
729 p_business_group_id in varchar2,
730 p_date_type in varchar2,
731 p_reset_date in varchar2,
732 p_frequency_type in varchar2,
733 p_frequency in varchar2,
734 p_exclude_reversal in varchar2)
735 is
736 l_balance_dimension_id number;
737 begin
738 retcode := 0;
739 --
740 create_balance_dimension(
741 p_dimension_name => p_dimension_name,
742 p_database_item_suffix => p_database_item_suffix,
743 p_business_group_id => p_business_group_id,
744 p_date_type => p_date_type,
745 p_reset_date => fnd_date.canonical_to_date(p_reset_date),
746 p_frequency_type => p_frequency_type,
747 p_frequency => fnd_number.canonical_to_number(p_frequency),
748 p_exclude_reversal => (p_exclude_reversal = 'Y'),
749 p_balance_dimension_id => l_balance_dimension_id);
750 exception
751 when others then
752 retcode := 2;
753 errbuf := sqlerrm;
754 rollback;
755 end create_balance_dimension;
756 -- ----------------------------------------------------------------------------
757 -- |------------------------------< delete_dbi >------------------------------|
758 -- ----------------------------------------------------------------------------
759 procedure delete_dbi(
760 p_defined_balance_id in number,
761 p_business_group_id in number)
762 is
763 begin
764 delete
765 from ff_fdi_usages_f
766 where formula_id in (
767 select distinct
768 ff.formula_id
769 from ff_user_entities u,
770 ff_database_items d,
771 ff_fdi_usages_f fdi,
772 ff_formulas_f ff
773 where u.creator_id = p_defined_balance_id
774 and u.creator_type in ('B', 'RB')
775 and d.user_entity_id = u.user_entity_id
776 and fdi.item_name = d.user_name
777 and fdi.usage = 'D'
778 and ff.formula_id = fdi.formula_id
779 and ff.effective_start_date = fdi.effective_start_date
780 and ff.effective_end_date = fdi.effective_end_date
784 from ff_compiled_info_f
781 and ff.business_group_id = p_business_group_id);
782 --
783 delete
785 where formula_id in (
786 select distinct
787 ff.formula_id
788 from ff_user_entities u,
789 ff_database_items d,
790 ff_fdi_usages_f fdi,
791 ff_formulas_f ff
792 where u.creator_id = p_defined_balance_id
793 and u.creator_type in ('B', 'RB')
794 and d.user_entity_id = u.user_entity_id
795 and fdi.item_name = d.user_name
796 and fdi.usage = 'D'
797 and ff.formula_id = fdi.formula_id
798 and ff.effective_start_date = fdi.effective_start_date
799 and ff.effective_end_date = fdi.effective_end_date
800 and ff.business_group_id = p_business_group_id);
801 --
802 delete
803 from ff_user_entities
804 where creator_id = p_defined_balance_id
805 and creator_type in ('B', 'RB');
806 end delete_dbi;
807 -- ----------------------------------------------------------------------------
808 -- |------------------------< delete_latest_balances >------------------------|
809 -- ----------------------------------------------------------------------------
810 procedure delete_latest_balances(p_defined_balance_id in number)
811 is
812 begin
813 delete
814 from pay_balance_context_values
815 where latest_balance_id in (
816 select latest_balance_id
817 from pay_assignment_latest_balances
818 where defined_balance_id = p_defined_balance_id
819 union all
820 select latest_balance_id
821 from pay_assignment_latest_balances
822 where defined_balance_id = p_defined_balance_id
823 union all
824 select latest_balance_id
825 from pay_latest_balances
826 where defined_balance_id = p_defined_balance_id);
827 --
828 delete
829 from pay_assignment_latest_balances
830 where defined_balance_id = p_defined_balance_id;
831 --
832 delete
833 from pay_person_latest_balances
834 where defined_balance_id = p_defined_balance_id;
835 --
836 delete
837 from pay_latest_balances
838 where defined_balance_id = p_defined_balance_id;
839 end delete_latest_balances;
840 -- ----------------------------------------------------------------------------
841 -- |-----------------------< update_balance_dimension >-----------------------|
842 -- ----------------------------------------------------------------------------
843 procedure update_balance_dimension(
844 p_balance_dimension_id in number,
845 p_date_type in varchar2,
846 p_reset_date in date,
847 p_frequency_type in varchar2,
848 p_frequency in number,
849 p_exclude_reversal in boolean,
850 p_rebuild_package in boolean default true)
851 is
852 l_balance_dimension_id number := p_balance_dimension_id;
853 l_business_group_id number;
854 l_dimension_name pay_balance_dimensions.dimension_name%type;
855 l_database_item_suffix pay_balance_dimensions.database_item_suffix%type;
856 --
857 type t_number_tbl is table of number index by binary_integer;
858 l_defined_balance_ids t_number_tbl;
862 select business_group_id,
859 l_balance_type_ids t_number_tbl;
860 l_route_ids t_number_tbl;
861 begin
863 dimension_name,
864 database_item_suffix
865 into l_business_group_id,
866 l_dimension_name,
867 l_database_item_suffix
868 from pay_balance_dimensions
869 where balance_dimension_id = p_balance_dimension_id;
870 --
871 -- Only user defined dimension is allowed to be updated.
872 --
873 if l_business_group_id is null then
874 fnd_message.set_name('PAY', 'PAY_JP_DIM_SEEDUPD_NOT_ALLOWED');
875 fnd_message.raise_error;
876 end if;
877 --
878 validate_parameters(
879 p_date_type => p_date_type,
880 p_reset_date => p_reset_date,
881 p_frequency_type => p_frequency_type,
882 p_frequency => p_frequency);
883 --
884 -- Delete current DBIs
885 --
886 select defined_balance_id,
887 balance_type_id
888 bulk collect
889 into l_defined_balance_ids,
890 l_balance_type_ids
891 from pay_defined_balances
892 where balance_dimension_id = p_balance_dimension_id;
893 --
894 for i in 1..l_defined_balance_ids.count loop
895 --
896 -- Delete compiled info and DBIs.
897 --
898 delete_dbi(l_defined_balance_ids(i), l_business_group_id);
899 --
900 -- Delete latest balances.
901 -- No need to trash run balances which is not affected
902 -- because those are ASG_RUN level balances.
903 --
904 delete_latest_balances(l_defined_balance_ids(i));
905 end loop;
906 --
907 delete
908 from pay_dimension_routes
909 where balance_dimension_id = p_balance_dimension_id;
910 --
911 upload_balance_dimension(
912 p_balance_dimension_id => l_balance_dimension_id,
913 p_dimension_name => l_dimension_name,
914 p_database_item_suffix => l_database_item_suffix,
915 p_business_group_id => l_business_group_id,
916 p_date_type => p_date_type,
917 p_reset_date => p_reset_date,
918 p_frequency_type => p_frequency_type,
919 p_frequency => p_frequency,
920 p_exclude_reversal => p_exclude_reversal);
921 --
922 if p_date_type = 'DP' then
923 create_dimension_route(
924 p_balance_dimension_id => p_balance_dimension_id,
925 p_priority => 1,
926 p_route_type => 'SRB',
927 p_date_type => p_date_type,
928 p_reset_date => p_reset_date,
929 p_frequency_type => p_frequency_type,
930 p_frequency => p_frequency,
931 p_exclude_reversal => p_exclude_reversal);
932 --
933 create_dimension_route(
934 p_balance_dimension_id => p_balance_dimension_id,
935 p_priority => 2,
936 p_route_type => 'RR',
937 p_date_type => p_date_type,
938 p_reset_date => p_reset_date,
939 p_frequency_type => p_frequency_type,
940 p_frequency => p_frequency,
941 p_exclude_reversal => p_exclude_reversal);
942 else
943 create_dimension_route(
944 p_balance_dimension_id => p_balance_dimension_id,
945 p_priority => 1,
946 p_route_type => 'RR',
947 p_date_type => p_date_type,
948 p_reset_date => p_reset_date,
949 p_frequency_type => p_frequency_type,
950 p_frequency => p_frequency,
951 p_exclude_reversal => p_exclude_reversal);
952 end if;
953 --
954 -- Rebuild DBIs
955 --
956 for i in 1..l_defined_balance_ids.count loop
960 p_balance_type_id => l_balance_type_ids(i),
957 hrdyndbi.new_defined_balance(
958 p_defined_balance_id => l_defined_balance_ids(i),
959 p_balance_dimension_id => p_balance_dimension_id,
961 p_business_group_id => l_business_group_id,
962 p_legislation_code => null);
963 end loop;
964 --
965 -- Rebuild Package of expiry_checking_code/start_date_code.
966 --
967 if p_rebuild_package then
968 rebuild_package;
969 end if;
970 --
971 -- Compile FF needs to be performed by user manually.
972 --
973 end update_balance_dimension;
974 --
975 procedure update_balance_dimension(
976 errbuf out nocopy varchar2,
977 retcode out nocopy varchar2,
978 p_balance_dimension_id in varchar2,
979 p_date_type in varchar2,
980 p_reset_date in varchar2,
981 p_frequency_type in varchar2,
982 p_frequency in varchar2,
983 p_exclude_reversal in varchar2)
984 is
985 begin
986 retcode := 0;
987 --
988 update_balance_dimension(
989 p_balance_dimension_id => fnd_number.canonical_to_number(p_balance_dimension_id),
990 p_date_type => p_date_type,
991 p_reset_date => fnd_date.canonical_to_date(p_reset_date),
992 p_frequency_type => p_frequency_type,
993 p_frequency => fnd_number.canonical_to_number(p_frequency),
994 p_exclude_reversal => (p_exclude_reversal = 'Y'));
995 exception
996 when others then
997 retcode := 2;
998 errbuf := sqlerrm;
999 rollback;
1000 end update_balance_dimension;
1001 -- ----------------------------------------------------------------------------
1002 -- |---------------------------< rebuild_package >----------------------------|
1003 -- ----------------------------------------------------------------------------
1004 procedure rebuild_package(p_rebuild_dimension in boolean default false)
1005 is
1006 l_header dbms_sql.varchar2a;
1007 l_body dbms_sql.varchar2a;
1008 l_csr number;
1009 l_dummy number;
1010 l_reset_date varchar2(30);
1011 l_frequency_type varchar2(30);
1012 l_frequency varchar2(30);
1013 --
1014 cursor csr_def is
1015 select def.defined_balance_id,
1016 def.business_group_id
1017 from per_business_groups_perf bg,
1018 pay_balance_dimensions dim,
1019 pay_defined_balances def
1020 where bg.legislation_code = 'JP'
1021 and dim.business_group_id = bg.business_group_id
1022 and pay_core_utils.get_parameter('DATE_TYPE', dim.description) is not null
1023 and pay_core_utils.get_parameter('RESET_DATE', dim.description) is not null
1024 and pay_core_utils.get_parameter('FREQUENCY_TYPE', dim.description) is not null
1025 and pay_core_utils.get_parameter('FREQUENCY', dim.description) is not null
1026 and def.balance_dimension_id = dim.balance_dimension_id;
1027 --
1028 cursor csr_dim is
1029 select dim.balance_dimension_id,
1030 dim.description
1031 from per_business_groups_perf bg,
1032 pay_balance_dimensions dim
1033 where bg.legislation_code = 'JP'
1034 and dim.business_group_id = bg.business_group_id
1035 and pay_core_utils.get_parameter('DATE_TYPE', dim.description) is not null
1036 and pay_core_utils.get_parameter('RESET_DATE', dim.description) is not null
1037 and pay_core_utils.get_parameter('FREQUENCY_TYPE', dim.description) is not null
1038 and pay_core_utils.get_parameter('FREQUENCY', dim.description) is not null;
1039 --
1040 cursor csr_code is
1041 --
1042 -- Do not group by description because expiry_checking_code and
1043 -- start_date_code are the same name for both reversal include type and
1044 -- reversal exclude type dimensions.
1045 --
1046 select upper(dim.expiry_checking_code) expiry_checking_code,
1047 upper(dim.start_date_code) start_date_code,
1048 min(dim.description) description
1049 from per_business_groups_perf bg,
1050 pay_balance_dimensions dim
1051 where bg.legislation_code = 'JP'
1052 and dim.business_group_id = bg.business_group_id
1053 and (dim.expiry_checking_code is not null or dim.start_date_code is not null)
1054 and pay_core_utils.get_parameter('DATE_TYPE', dim.description) is not null
1055 and pay_core_utils.get_parameter('RESET_DATE', dim.description) is not null
1056 and pay_core_utils.get_parameter('FREQUENCY_TYPE', dim.description) is not null
1057 and pay_core_utils.get_parameter('FREQUENCY', dim.description) is not null
1058 group by
1059 dim.expiry_checking_code,
1060 dim.start_date_code;
1061 --
1062 procedure add_header(p_str in varchar2)
1063 is
1064 begin
1065 if l_header.count = 0 then
1066 l_header(1) := 'create or replace package pay_jp_dynamic_dimension_pkg as';
1067 add_header('--');
1068 add_header('function start_date(');
1069 add_header(' p_effective_date in date,');
1070 add_header(' p_reset_date in date,');
1071 add_header(' p_frequency_type in varchar2,');
1072 add_header(' p_frequency in number) return date;');
1073 add_header('--');
1074 add_header('function end_date(');
1075 add_header(' p_effective_date in date,');
1076 add_header(' p_reset_date in date,');
1077 add_header(' p_frequency_type in varchar2,');
1078 add_header(' p_frequency in number) return date;');
1079 end if;
1080 --
1081 l_header(l_header.count + 1) := p_str;
1082 end add_header;
1083 --
1084 procedure add_body(p_str in varchar2)
1085 is
1086 begin
1087 if l_body.count = 0 then
1088 l_body(1) := 'create or replace package body pay_jp_dynamic_dimension_pkg as';
1089 add_body('--');
1090 add_body('-- ----------------------------------------------------------------------------');
1091 add_body('-- |------------------------------< start_date >------------------------------|');
1092 add_body('-- ----------------------------------------------------------------------------');
1093 add_body('function start_date(');
1094 add_body(' p_effective_date in date,');
1098 add_body('is');
1095 add_body(' p_reset_date in date,');
1096 add_body(' p_frequency_type in varchar2,');
1097 add_body(' p_frequency in number) return date');
1099 add_body(' l_start_date date;');
1100 add_body(' l_start_date_temp date;');
1101 add_body('begin');
1102 add_body(' if p_frequency_type = ''DAY'' then');
1103 add_body(' l_start_date := p_reset_date + floor((p_effective_date - p_reset_date) / p_frequency) * p_frequency;');
1104 add_body(' elsif p_frequency_type = ''SMONTH'' then');
1105 add_body(' if mod(p_frequency, 2) = 0 then');
1106 add_body(' l_start_date := start_date(p_effective_date, p_reset_date, ''MONTH'', p_frequency / 2);');
1107 add_body(' else');
1108 add_body(' l_start_date := start_date(p_effective_date, p_reset_date, ''MONTH'', p_frequency);');
1109 add_body(' l_start_date_temp := add_months(l_start_date, floor(p_frequency / 2)) + 15;');
1110 add_body(' if p_effective_date >= l_start_date_temp then');
1111 add_body(' l_start_date := l_start_date_temp;');
1112 add_body(' end if;');
1113 add_body(' end if;');
1114 add_body(' elsif p_frequency_type = ''MONTH'' then');
1115 add_body(' l_start_date := add_months(p_reset_date, floor(months_between(p_effective_date, p_reset_date) / p_frequency) * p_frequency);');
1116 add_body(' else');
1117 add_body(' fnd_message.set_name(''PAY'', ''PAY_JP_DIM_INVALID_FREQ_TYPE'');');
1118 add_body(' fnd_message.set_token(''FREQUENCY_TYPE'', p_frequency_type);');
1119 add_body(' fnd_message.raise_error;');
1120 add_body(' end if;');
1121 add_body(' --');
1122 add_body(' return l_start_date;');
1123 add_body('end start_date;');
1124 add_body('-- ----------------------------------------------------------------------------');
1125 add_body('-- |-------------------------------< end_date >-------------------------------|');
1126 add_body('-- ----------------------------------------------------------------------------');
1127 add_body('function end_date(');
1128 add_body(' p_effective_date in date,');
1129 add_body(' p_reset_date in date,');
1130 add_body(' p_frequency_type in varchar2,');
1131 add_body(' p_frequency in number) return date');
1132 add_body('is');
1133 add_body(' l_start_date date;');
1134 add_body(' l_end_date date;');
1135 add_body('begin');
1136 add_body(' if p_frequency_type = ''DAY'' then');
1137 add_body(' l_end_date := start_date(p_effective_date, p_reset_date, p_frequency_type, p_frequency) + p_frequency - 1;');
1138 add_body(' elsif p_frequency_type = ''SMONTH'' then');
1139 add_body(' if mod(p_frequency, 2) = 0 then');
1140 add_body(' l_end_date := end_date(p_effective_date, p_reset_date, ''MONTH'', p_frequency / 2);');
1141 add_body(' else');
1142 add_body(' l_start_date := start_date(p_effective_date, p_reset_date, ''MONTH'', p_frequency);');
1143 add_body(' l_end_date := add_months(l_start_date, floor(p_frequency / 2)) + 14;');
1144 add_body(' if p_effective_date > l_end_date then');
1145 add_body(' l_end_date := add_months(l_start_date, p_frequency) - 1;');
1146 add_body(' end if;');
1147 add_body(' end if;');
1148 add_body(' elsif p_frequency_type = ''MONTH'' then');
1149 add_body(' l_end_date := add_months(start_date(p_effective_date, p_reset_date, p_frequency_type, p_frequency), p_frequency) - 1;');
1150 add_body(' else');
1151 add_body(' fnd_message.set_name(''PAY'', ''PAY_JP_DIM_INVALID_FREQ_TYPE'');');
1152 add_body(' fnd_message.set_token(''FREQUENCY_TYPE'', p_frequency_type);');
1153 add_body(' fnd_message.raise_error;');
1154 add_body(' end if;');
1155 add_body(' --');
1156 add_body(' return l_end_date;');
1157 add_body('end end_date;');
1158 end if;
1159 --
1160 l_body(l_body.count + 1) := p_str;
1161 end add_body;
1162 begin
1163 if p_rebuild_dimension then
1164 --
1165 -- Delete fdi/compiled/DBI info which references DBI with user defined dimension.
1166 --
1167 for l_rec in csr_def loop
1168 delete_dbi(l_rec.defined_balance_id, l_rec.business_group_id);
1169 end loop;
1170 --
1171 -- Delete PAY_DIMENSION_ROUTES
1172 --
1173 for l_rec in csr_dim loop
1174 delete
1175 from pay_dimension_routes
1176 where balance_dimension_id = l_rec.balance_dimension_id;
1177 end loop;
1178 --
1179 -- Delete FF_ROUTES
1180 --
1181 delete
1182 from ff_routes
1183 where ( route_name like 'JP\_ASG\_DP\_%\_BALANCE_DIMENSION%' escape '\'
1184 or route_name like 'JP\_ASG\_DE\_%\_BALANCE_DIMENSION%' escape '\')
1185 and user_defined_flag = 'Y';
1186 --
1187 for l_rec in csr_dim loop
1188 --
1189 -- Rebuild Balance Dimension
1190 --
1191 update_balance_dimension(
1192 p_balance_dimension_id => l_rec.balance_dimension_id,
1193 p_date_type => pay_core_utils.get_parameter('DATE_TYPE', l_rec.description),
1194 p_reset_date => fnd_date.canonical_to_date(pay_core_utils.get_parameter('RESET_DATE', l_rec.description)),
1195 p_frequency_type => pay_core_utils.get_parameter('FREQUENCY_TYPE', l_rec.description),
1196 p_frequency => fnd_number.canonical_to_number(pay_core_utils.get_parameter('FREQUENCY', l_rec.description)),
1197 p_exclude_reversal => (pay_core_utils.get_parameter('EXCLUDE_REVERSAL', l_rec.description) = 'Y'),
1198 p_rebuild_package => false);
1199 end loop;
1200 end if;
1201 --
1202 for l_rec in csr_code loop
1203 l_reset_date := pay_core_utils.get_parameter('RESET_DATE', l_rec.description);
1204 l_frequency_type := pay_core_utils.get_parameter('FREQUENCY_TYPE', l_rec.description);
1205 l_frequency := pay_core_utils.get_parameter('FREQUENCY', l_rec.description);
1206 --
1207 if l_rec.expiry_checking_code is not null then
1208 l_rec.expiry_checking_code := substr(l_rec.expiry_checking_code, instr(l_rec.expiry_checking_code, '.') + 1);
1209 --
1210 add_header('--');
1211 add_header('procedure ' || l_rec.expiry_checking_code || '(');
1215 add_header(' p_user_assignment_action_id in number,');
1212 add_header(' p_owner_payroll_action_id in number,');
1213 add_header(' p_user_payroll_action_id in number,');
1214 add_header(' p_owner_assignment_action_id in number,');
1216 add_header(' p_owner_effective_date in date,');
1217 add_header(' p_user_effective_date in date,');
1218 add_header(' p_dimension_name in varchar2,');
1219 add_header(' p_expiry_information out nocopy number);');
1220 --
1221 add_body('--');
1222 add_body('procedure ' || l_rec.expiry_checking_code || '(');
1223 add_body(' p_owner_payroll_action_id in number,');
1224 add_body(' p_user_payroll_action_id in number,');
1225 add_body(' p_owner_assignment_action_id in number,');
1226 add_body(' p_user_assignment_action_id in number,');
1227 add_body(' p_owner_effective_date in date,');
1228 add_body(' p_user_effective_date in date,');
1229 add_body(' p_dimension_name in varchar2,');
1230 add_body(' p_expiry_information out nocopy number)');
1231 add_body('is');
1232 add_body('begin');
1233 add_body(' if start_date(p_user_effective_date, fnd_date.canonical_to_date(''' ||
1234 l_reset_date || '''), ''' || l_frequency_type || ''', ' || l_frequency || ') > p_owner_effective_date then');
1235 add_body(' p_expiry_information := 1;');
1236 add_body(' else');
1237 add_body(' p_expiry_information := 0;');
1238 add_body(' end if;');
1239 add_body('end ' || l_rec.expiry_checking_code || ';');
1240 --
1241 add_header('--');
1242 add_header('procedure ' || l_rec.expiry_checking_code || '(');
1243 add_header(' p_owner_payroll_action_id in number,');
1244 add_header(' p_user_payroll_action_id in number,');
1245 add_header(' p_owner_assignment_action_id in number,');
1246 add_header(' p_user_assignment_action_id in number,');
1247 add_header(' p_owner_effective_date in date,');
1248 add_header(' p_user_effective_date in date,');
1249 add_header(' p_dimension_name in varchar2,');
1250 add_header(' p_expiry_information out nocopy date);');
1251 --
1252 add_body('--');
1253 add_body('procedure ' || l_rec.expiry_checking_code || '(');
1254 add_body(' p_owner_payroll_action_id in number,');
1255 add_body(' p_user_payroll_action_id in number,');
1256 add_body(' p_owner_assignment_action_id in number,');
1257 add_body(' p_user_assignment_action_id in number,');
1258 add_body(' p_owner_effective_date in date,');
1259 add_body(' p_user_effective_date in date,');
1260 add_body(' p_dimension_name in varchar2,');
1261 add_body(' p_expiry_information out nocopy date)');
1262 add_body('is');
1263 add_body('begin');
1264 add_body(' p_expiry_information := end_date(p_owner_effective_date, fnd_date.canonical_to_date(''' ||
1265 l_reset_date || '''), ''' || l_frequency_type || ''', ' || l_frequency || ');');
1266 add_body('end ' || l_rec.expiry_checking_code || ';');
1267 end if;
1268 --
1269 if l_rec.start_date_code is not null then
1270 l_rec.start_date_code := substr(l_rec.start_date_code, instr(l_rec.start_date_code, '.') + 1);
1271 --
1272 add_header('--');
1273 add_header('procedure ' || l_rec.start_date_code || '(');
1274 add_header(' p_effective_date in date,');
1275 add_header(' p_start_date out nocopy date,');
1276 add_header(' p_payroll_id in number,');
1277 add_header(' p_bus_grp in number,');
1278 add_header(' p_asg_action in number);');
1279 --
1280 add_body('--');
1281 add_body('procedure ' || l_rec.start_date_code || '(');
1282 add_body(' p_effective_date in date,');
1283 add_body(' p_start_date out nocopy date,');
1284 add_body(' p_payroll_id in number,');
1285 add_body(' p_bus_grp in number,');
1286 add_body(' p_asg_action in number)');
1287 add_body('is');
1288 add_body('begin');
1289 add_body(' p_start_date := start_date(p_effective_date, fnd_date.canonical_to_date(''' ||
1290 l_reset_date || '''), ''' || l_frequency_type || ''', ' || l_frequency || ');');
1291 add_body('end ' || l_rec.start_date_code || ';');
1292 end if;
1293 end loop;
1294 --
1295 if l_header.count > 0 then
1296 add_header('--');
1297 add_header('end pay_jp_dynamic_dimension_pkg;');
1298 --
1299 add_body('--');
1300 add_body('end pay_jp_dynamic_dimension_pkg;');
1301 --
1302 l_csr := dbms_sql.open_cursor;
1303 --
1304 dbms_sql.parse(l_csr, l_header, 1, l_header.count, true, dbms_sql.native);
1305 l_dummy := dbms_sql.execute(l_csr);
1306 --
1307 dbms_sql.parse(l_csr, l_body, 1, l_body.count, true, dbms_sql.native);
1308 l_dummy := dbms_sql.execute(l_csr);
1309 --
1310 dbms_sql.close_cursor(l_csr);
1311 end if;
1312 end rebuild_package;
1313 --
1314 procedure rebuild_package(
1315 errbuf out nocopy varchar2,
1316 retcode out nocopy varchar2,
1317 p_rebuild_dimension in varchar2)
1318 is
1319 begin
1320 retcode := 0;
1321 --
1322 rebuild_package(p_rebuild_dimension = 'Y');
1323 exception
1324 when others then
1325 retcode := 2;
1326 errbuf := sqlerrm;
1327 rollback;
1328 end rebuild_package;
1329 --
1330 end pay_jp_dimension_generator_pkg;