[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.1 2011/04/27 03:26:18 keyazawa ship $ */
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).
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 --
324 -- Expiry checking for DATE_EARNED is supported by PYUGEN using expiry_checking_level = 'E'(Enhanced).
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,
409 feed_checking_type = null,
410 feed_checking_code = null,
411 route_id = l_route_id,
412 database_item_function = 'Y',
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,
501 ROUTE_ID,
502 DATA_TYPE,
503 PARAMETER_NAME,
504 SEQUENCE_NO)
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 --
630 select count(*)
631 into l_count
632 from pay_balance_dimensions
633 where replace(upper(database_item_suffix), ' ', '_') = replace(upper(p_database_item_suffix), ' ', '_')
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 --
739 -- disable in progress of online patch
740 if ad_zd.get_edition('PATCH') is not null then
741 --
742 fnd_message.set_name('FND','AD_ZD_DISABLED_FEATURE');
743 fnd_message.raise_error;
744 --
745 end if;
746 --
747 retcode := 0;
748 --
749 create_balance_dimension(
750 p_dimension_name => p_dimension_name,
751 p_database_item_suffix => p_database_item_suffix,
752 p_business_group_id => p_business_group_id,
753 p_date_type => p_date_type,
754 p_reset_date => fnd_date.canonical_to_date(p_reset_date),
755 p_frequency_type => p_frequency_type,
756 p_frequency => fnd_number.canonical_to_number(p_frequency),
757 p_exclude_reversal => (p_exclude_reversal = 'Y'),
758 p_balance_dimension_id => l_balance_dimension_id);
759 exception
760 when others then
761 retcode := 2;
762 errbuf := sqlerrm;
763 rollback;
764 end create_balance_dimension;
765 -- ----------------------------------------------------------------------------
766 -- |------------------------------< delete_dbi >------------------------------|
767 -- ----------------------------------------------------------------------------
768 procedure delete_dbi(
769 p_defined_balance_id in number,
770 p_business_group_id in number)
771 is
772 begin
773 delete
774 from ff_fdi_usages_f
775 where formula_id in (
776 select distinct
777 ff.formula_id
778 from ff_user_entities u,
779 ff_database_items d,
780 ff_fdi_usages_f fdi,
781 ff_formulas_f ff
782 where u.creator_id = p_defined_balance_id
783 and u.creator_type in ('B', 'RB')
784 and d.user_entity_id = u.user_entity_id
785 and fdi.item_name = d.user_name
786 and fdi.usage = 'D'
787 and ff.formula_id = fdi.formula_id
788 and ff.effective_start_date = fdi.effective_start_date
789 and ff.effective_end_date = fdi.effective_end_date
790 and ff.business_group_id = p_business_group_id);
791 --
792 delete
793 from ff_compiled_info_f
794 where formula_id in (
795 select distinct
796 ff.formula_id
797 from ff_user_entities u,
798 ff_database_items d,
799 ff_fdi_usages_f fdi,
800 ff_formulas_f ff
801 where u.creator_id = p_defined_balance_id
802 and u.creator_type in ('B', 'RB')
803 and d.user_entity_id = u.user_entity_id
804 and fdi.item_name = d.user_name
805 and fdi.usage = 'D'
806 and ff.formula_id = fdi.formula_id
807 and ff.effective_start_date = fdi.effective_start_date
808 and ff.effective_end_date = fdi.effective_end_date
809 and ff.business_group_id = p_business_group_id);
810 --
811 delete
812 from ff_user_entities
813 where creator_id = p_defined_balance_id
814 and creator_type in ('B', 'RB');
815 end delete_dbi;
816 -- ----------------------------------------------------------------------------
817 -- |------------------------< delete_latest_balances >------------------------|
818 -- ----------------------------------------------------------------------------
819 procedure delete_latest_balances(p_defined_balance_id in number)
820 is
821 begin
822 delete
823 from pay_balance_context_values
824 where latest_balance_id in (
825 select latest_balance_id
826 from pay_assignment_latest_balances
827 where defined_balance_id = p_defined_balance_id
828 union all
829 select latest_balance_id
830 from pay_assignment_latest_balances
831 where defined_balance_id = p_defined_balance_id
832 union all
833 select latest_balance_id
834 from pay_latest_balances
835 where defined_balance_id = p_defined_balance_id);
836 --
837 delete
838 from pay_assignment_latest_balances
839 where defined_balance_id = p_defined_balance_id;
840 --
841 delete
842 from pay_person_latest_balances
843 where defined_balance_id = p_defined_balance_id;
844 --
845 delete
846 from pay_latest_balances
847 where defined_balance_id = p_defined_balance_id;
848 end delete_latest_balances;
849 -- ----------------------------------------------------------------------------
850 -- |-----------------------< update_balance_dimension >-----------------------|
851 -- ----------------------------------------------------------------------------
852 procedure update_balance_dimension(
853 p_balance_dimension_id in number,
854 p_date_type in varchar2,
855 p_reset_date in date,
856 p_frequency_type in varchar2,
857 p_frequency in number,
858 p_exclude_reversal in boolean,
859 p_rebuild_package in boolean default true)
860 is
861 l_balance_dimension_id number := p_balance_dimension_id;
862 l_business_group_id number;
863 l_dimension_name pay_balance_dimensions.dimension_name%type;
864 l_database_item_suffix pay_balance_dimensions.database_item_suffix%type;
865 --
866 type t_number_tbl is table of number index by binary_integer;
867 l_defined_balance_ids t_number_tbl;
868 l_balance_type_ids t_number_tbl;
869 l_route_ids t_number_tbl;
870 begin
871 select business_group_id,
872 dimension_name,
873 database_item_suffix
874 into l_business_group_id,
875 l_dimension_name,
876 l_database_item_suffix
877 from pay_balance_dimensions
878 where balance_dimension_id = p_balance_dimension_id;
879 --
880 -- Only user defined dimension is allowed to be updated.
881 --
882 if l_business_group_id is null then
883 fnd_message.set_name('PAY', 'PAY_JP_DIM_SEEDUPD_NOT_ALLOWED');
884 fnd_message.raise_error;
885 end if;
886 --
887 validate_parameters(
888 p_date_type => p_date_type,
889 p_reset_date => p_reset_date,
890 p_frequency_type => p_frequency_type,
891 p_frequency => p_frequency);
892 --
893 -- Delete current DBIs
894 --
895 select defined_balance_id,
896 balance_type_id
897 bulk collect
898 into l_defined_balance_ids,
899 l_balance_type_ids
900 from pay_defined_balances
901 where balance_dimension_id = p_balance_dimension_id;
902 --
903 for i in 1..l_defined_balance_ids.count loop
904 --
905 -- Delete compiled info and DBIs.
906 --
907 delete_dbi(l_defined_balance_ids(i), l_business_group_id);
908 --
909 -- Delete latest balances.
910 -- No need to trash run balances which is not affected
911 -- because those are ASG_RUN level balances.
912 --
913 delete_latest_balances(l_defined_balance_ids(i));
914 end loop;
915 --
916 delete
917 from pay_dimension_routes
918 where balance_dimension_id = p_balance_dimension_id;
919 --
920 upload_balance_dimension(
921 p_balance_dimension_id => l_balance_dimension_id,
922 p_dimension_name => l_dimension_name,
923 p_database_item_suffix => l_database_item_suffix,
924 p_business_group_id => l_business_group_id,
925 p_date_type => p_date_type,
926 p_reset_date => p_reset_date,
927 p_frequency_type => p_frequency_type,
928 p_frequency => p_frequency,
929 p_exclude_reversal => p_exclude_reversal);
930 --
931 if p_date_type = 'DP' then
932 create_dimension_route(
933 p_balance_dimension_id => p_balance_dimension_id,
934 p_priority => 1,
935 p_route_type => 'SRB',
936 p_date_type => p_date_type,
937 p_reset_date => p_reset_date,
938 p_frequency_type => p_frequency_type,
939 p_frequency => p_frequency,
940 p_exclude_reversal => p_exclude_reversal);
941 --
942 create_dimension_route(
943 p_balance_dimension_id => p_balance_dimension_id,
944 p_priority => 2,
945 p_route_type => 'RR',
946 p_date_type => p_date_type,
947 p_reset_date => p_reset_date,
948 p_frequency_type => p_frequency_type,
949 p_frequency => p_frequency,
950 p_exclude_reversal => p_exclude_reversal);
951 else
952 create_dimension_route(
953 p_balance_dimension_id => p_balance_dimension_id,
954 p_priority => 1,
955 p_route_type => 'RR',
956 p_date_type => p_date_type,
957 p_reset_date => p_reset_date,
958 p_frequency_type => p_frequency_type,
959 p_frequency => p_frequency,
960 p_exclude_reversal => p_exclude_reversal);
961 end if;
962 --
963 -- Rebuild DBIs
964 --
965 for i in 1..l_defined_balance_ids.count loop
966 hrdyndbi.new_defined_balance(
967 p_defined_balance_id => l_defined_balance_ids(i),
968 p_balance_dimension_id => p_balance_dimension_id,
969 p_balance_type_id => l_balance_type_ids(i),
970 p_business_group_id => l_business_group_id,
971 p_legislation_code => null);
972 end loop;
973 --
974 -- Rebuild Package of expiry_checking_code/start_date_code.
975 --
976 if p_rebuild_package then
977 rebuild_package;
978 end if;
979 --
980 -- Compile FF needs to be performed by user manually.
981 --
982 end update_balance_dimension;
983 --
984 procedure update_balance_dimension(
985 errbuf out nocopy varchar2,
986 retcode out nocopy varchar2,
987 p_balance_dimension_id in varchar2,
988 p_date_type in varchar2,
989 p_reset_date in varchar2,
990 p_frequency_type in varchar2,
991 p_frequency in varchar2,
992 p_exclude_reversal in varchar2)
993 is
994 begin
995 --
996 -- disable in progress of online patch
997 if ad_zd.get_edition('PATCH') is not null then
998 --
999 fnd_message.set_name('FND','AD_ZD_DISABLED_FEATURE');
1000 fnd_message.raise_error;
1001 --
1002 end if;
1003 --
1004 retcode := 0;
1005 --
1006 update_balance_dimension(
1007 p_balance_dimension_id => fnd_number.canonical_to_number(p_balance_dimension_id),
1008 p_date_type => p_date_type,
1009 p_reset_date => fnd_date.canonical_to_date(p_reset_date),
1010 p_frequency_type => p_frequency_type,
1011 p_frequency => fnd_number.canonical_to_number(p_frequency),
1012 p_exclude_reversal => (p_exclude_reversal = 'Y'));
1013 exception
1014 when others then
1015 retcode := 2;
1016 errbuf := sqlerrm;
1017 rollback;
1018 end update_balance_dimension;
1019 -- ----------------------------------------------------------------------------
1020 -- |---------------------------< rebuild_package >----------------------------|
1021 -- ----------------------------------------------------------------------------
1022 procedure rebuild_package(p_rebuild_dimension in boolean default false)
1023 is
1024 l_header dbms_sql.varchar2a;
1025 l_body dbms_sql.varchar2a;
1026 l_csr number;
1027 l_dummy number;
1028 l_reset_date varchar2(30);
1029 l_frequency_type varchar2(30);
1030 l_frequency varchar2(30);
1031 --
1032 cursor csr_def is
1033 select def.defined_balance_id,
1034 def.business_group_id
1035 from per_business_groups_perf bg,
1036 pay_balance_dimensions dim,
1037 pay_defined_balances def
1038 where bg.legislation_code = 'JP'
1039 and dim.business_group_id = bg.business_group_id
1040 and pay_core_utils.get_parameter('DATE_TYPE', dim.description) is not null
1041 and pay_core_utils.get_parameter('RESET_DATE', dim.description) is not null
1042 and pay_core_utils.get_parameter('FREQUENCY_TYPE', dim.description) is not null
1043 and pay_core_utils.get_parameter('FREQUENCY', dim.description) is not null
1044 and def.balance_dimension_id = dim.balance_dimension_id;
1045 --
1046 cursor csr_dim is
1047 select dim.balance_dimension_id,
1048 dim.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 pay_core_utils.get_parameter('DATE_TYPE', dim.description) is not null
1054 and pay_core_utils.get_parameter('RESET_DATE', dim.description) is not null
1055 and pay_core_utils.get_parameter('FREQUENCY_TYPE', dim.description) is not null
1056 and pay_core_utils.get_parameter('FREQUENCY', dim.description) is not null;
1057 --
1058 cursor csr_code is
1059 --
1060 -- Do not group by description because expiry_checking_code and
1061 -- start_date_code are the same name for both reversal include type and
1062 -- reversal exclude type dimensions.
1063 --
1064 select upper(dim.expiry_checking_code) expiry_checking_code,
1065 upper(dim.start_date_code) start_date_code,
1066 min(dim.description) description
1067 from per_business_groups_perf bg,
1068 pay_balance_dimensions dim
1069 where bg.legislation_code = 'JP'
1070 and dim.business_group_id = bg.business_group_id
1071 and (dim.expiry_checking_code is not null or dim.start_date_code is not null)
1072 and pay_core_utils.get_parameter('DATE_TYPE', dim.description) is not null
1073 and pay_core_utils.get_parameter('RESET_DATE', dim.description) is not null
1074 and pay_core_utils.get_parameter('FREQUENCY_TYPE', dim.description) is not null
1075 and pay_core_utils.get_parameter('FREQUENCY', dim.description) is not null
1076 group by
1077 dim.expiry_checking_code,
1078 dim.start_date_code;
1079 --
1080 procedure add_header(p_str in varchar2)
1081 is
1082 begin
1083 if l_header.count = 0 then
1084 l_header(1) := 'create or replace package pay_jp_dynamic_dimension_pkg as';
1085 add_header('--');
1086 add_header('function start_date(');
1087 add_header(' p_effective_date in date,');
1088 add_header(' p_reset_date in date,');
1089 add_header(' p_frequency_type in varchar2,');
1090 add_header(' p_frequency in number) return date;');
1091 add_header('--');
1092 add_header('function end_date(');
1093 add_header(' p_effective_date in date,');
1094 add_header(' p_reset_date in date,');
1095 add_header(' p_frequency_type in varchar2,');
1096 add_header(' p_frequency in number) return date;');
1097 end if;
1098 --
1099 l_header(l_header.count + 1) := p_str;
1100 end add_header;
1101 --
1102 procedure add_body(p_str in varchar2)
1103 is
1104 begin
1105 if l_body.count = 0 then
1106 l_body(1) := 'create or replace package body pay_jp_dynamic_dimension_pkg as';
1107 add_body('--');
1108 add_body('-- ----------------------------------------------------------------------------');
1109 add_body('-- |------------------------------< start_date >------------------------------|');
1110 add_body('-- ----------------------------------------------------------------------------');
1111 add_body('function start_date(');
1112 add_body(' p_effective_date in date,');
1113 add_body(' p_reset_date in date,');
1114 add_body(' p_frequency_type in varchar2,');
1115 add_body(' p_frequency in number) return date');
1116 add_body('is');
1117 add_body(' l_start_date date;');
1118 add_body(' l_start_date_temp date;');
1119 add_body('begin');
1120 add_body(' if p_frequency_type = ''DAY'' then');
1121 add_body(' l_start_date := p_reset_date + floor((p_effective_date - p_reset_date) / p_frequency) * p_frequency;');
1122 add_body(' elsif p_frequency_type = ''SMONTH'' then');
1123 add_body(' if mod(p_frequency, 2) = 0 then');
1124 add_body(' l_start_date := start_date(p_effective_date, p_reset_date, ''MONTH'', p_frequency / 2);');
1125 add_body(' else');
1126 add_body(' l_start_date := start_date(p_effective_date, p_reset_date, ''MONTH'', p_frequency);');
1127 add_body(' l_start_date_temp := add_months(l_start_date, floor(p_frequency / 2)) + 15;');
1128 add_body(' if p_effective_date >= l_start_date_temp then');
1129 add_body(' l_start_date := l_start_date_temp;');
1130 add_body(' end if;');
1131 add_body(' end if;');
1132 add_body(' elsif p_frequency_type = ''MONTH'' then');
1133 add_body(' l_start_date := add_months(p_reset_date, floor(months_between(p_effective_date, p_reset_date) / p_frequency) * p_frequency);');
1134 add_body(' else');
1135 add_body(' fnd_message.set_name(''PAY'', ''PAY_JP_DIM_INVALID_FREQ_TYPE'');');
1136 add_body(' fnd_message.set_token(''FREQUENCY_TYPE'', p_frequency_type);');
1137 add_body(' fnd_message.raise_error;');
1138 add_body(' end if;');
1139 add_body(' --');
1140 add_body(' return l_start_date;');
1141 add_body('end start_date;');
1142 add_body('-- ----------------------------------------------------------------------------');
1143 add_body('-- |-------------------------------< end_date >-------------------------------|');
1144 add_body('-- ----------------------------------------------------------------------------');
1145 add_body('function end_date(');
1146 add_body(' p_effective_date in date,');
1147 add_body(' p_reset_date in date,');
1148 add_body(' p_frequency_type in varchar2,');
1149 add_body(' p_frequency in number) return date');
1150 add_body('is');
1151 add_body(' l_start_date date;');
1152 add_body(' l_end_date date;');
1153 add_body('begin');
1154 add_body(' if p_frequency_type = ''DAY'' then');
1155 add_body(' l_end_date := start_date(p_effective_date, p_reset_date, p_frequency_type, p_frequency) + p_frequency - 1;');
1156 add_body(' elsif p_frequency_type = ''SMONTH'' then');
1157 add_body(' if mod(p_frequency, 2) = 0 then');
1158 add_body(' l_end_date := end_date(p_effective_date, p_reset_date, ''MONTH'', p_frequency / 2);');
1159 add_body(' else');
1160 add_body(' l_start_date := start_date(p_effective_date, p_reset_date, ''MONTH'', p_frequency);');
1161 add_body(' l_end_date := add_months(l_start_date, floor(p_frequency / 2)) + 14;');
1162 add_body(' if p_effective_date > l_end_date then');
1163 add_body(' l_end_date := add_months(l_start_date, p_frequency) - 1;');
1164 add_body(' end if;');
1165 add_body(' end if;');
1166 add_body(' elsif p_frequency_type = ''MONTH'' then');
1167 add_body(' l_end_date := add_months(start_date(p_effective_date, p_reset_date, p_frequency_type, p_frequency), p_frequency) - 1;');
1168 add_body(' else');
1169 add_body(' fnd_message.set_name(''PAY'', ''PAY_JP_DIM_INVALID_FREQ_TYPE'');');
1170 add_body(' fnd_message.set_token(''FREQUENCY_TYPE'', p_frequency_type);');
1171 add_body(' fnd_message.raise_error;');
1172 add_body(' end if;');
1173 add_body(' --');
1174 add_body(' return l_end_date;');
1175 add_body('end end_date;');
1176 end if;
1177 --
1178 l_body(l_body.count + 1) := p_str;
1179 end add_body;
1180 begin
1181 if p_rebuild_dimension then
1182 --
1183 -- Delete fdi/compiled/DBI info which references DBI with user defined dimension.
1184 --
1185 for l_rec in csr_def loop
1186 delete_dbi(l_rec.defined_balance_id, l_rec.business_group_id);
1187 end loop;
1188 --
1189 -- Delete PAY_DIMENSION_ROUTES
1190 --
1191 for l_rec in csr_dim loop
1192 delete
1193 from pay_dimension_routes
1194 where balance_dimension_id = l_rec.balance_dimension_id;
1195 end loop;
1196 --
1197 -- Delete FF_ROUTES
1198 --
1199 delete
1200 from ff_routes
1201 where ( route_name like 'JP\_ASG\_DP\_%\_BALANCE_DIMENSION%' escape '\'
1202 or route_name like 'JP\_ASG\_DE\_%\_BALANCE_DIMENSION%' escape '\')
1203 and user_defined_flag = 'Y';
1204 --
1205 for l_rec in csr_dim loop
1206 --
1207 -- Rebuild Balance Dimension
1208 --
1209 update_balance_dimension(
1210 p_balance_dimension_id => l_rec.balance_dimension_id,
1211 p_date_type => pay_core_utils.get_parameter('DATE_TYPE', l_rec.description),
1212 p_reset_date => fnd_date.canonical_to_date(pay_core_utils.get_parameter('RESET_DATE', l_rec.description)),
1213 p_frequency_type => pay_core_utils.get_parameter('FREQUENCY_TYPE', l_rec.description),
1214 p_frequency => fnd_number.canonical_to_number(pay_core_utils.get_parameter('FREQUENCY', l_rec.description)),
1215 p_exclude_reversal => (pay_core_utils.get_parameter('EXCLUDE_REVERSAL', l_rec.description) = 'Y'),
1216 p_rebuild_package => false);
1217 end loop;
1218 end if;
1219 --
1220 for l_rec in csr_code loop
1221 l_reset_date := pay_core_utils.get_parameter('RESET_DATE', l_rec.description);
1222 l_frequency_type := pay_core_utils.get_parameter('FREQUENCY_TYPE', l_rec.description);
1223 l_frequency := pay_core_utils.get_parameter('FREQUENCY', l_rec.description);
1224 --
1225 if l_rec.expiry_checking_code is not null then
1226 l_rec.expiry_checking_code := substr(l_rec.expiry_checking_code, instr(l_rec.expiry_checking_code, '.') + 1);
1227 --
1228 add_header('--');
1229 add_header('procedure ' || l_rec.expiry_checking_code || '(');
1230 add_header(' p_owner_payroll_action_id in number,');
1231 add_header(' p_user_payroll_action_id in number,');
1232 add_header(' p_owner_assignment_action_id in number,');
1233 add_header(' p_user_assignment_action_id in number,');
1234 add_header(' p_owner_effective_date in date,');
1235 add_header(' p_user_effective_date in date,');
1236 add_header(' p_dimension_name in varchar2,');
1237 add_header(' p_expiry_information out nocopy number);');
1238 --
1239 add_body('--');
1240 add_body('procedure ' || l_rec.expiry_checking_code || '(');
1241 add_body(' p_owner_payroll_action_id in number,');
1242 add_body(' p_user_payroll_action_id in number,');
1243 add_body(' p_owner_assignment_action_id in number,');
1244 add_body(' p_user_assignment_action_id in number,');
1245 add_body(' p_owner_effective_date in date,');
1246 add_body(' p_user_effective_date in date,');
1247 add_body(' p_dimension_name in varchar2,');
1248 add_body(' p_expiry_information out nocopy number)');
1249 add_body('is');
1250 add_body('begin');
1251 add_body(' if start_date(p_user_effective_date, fnd_date.canonical_to_date(''' ||
1252 l_reset_date || '''), ''' || l_frequency_type || ''', ' || l_frequency || ') > p_owner_effective_date then');
1253 add_body(' p_expiry_information := 1;');
1254 add_body(' else');
1255 add_body(' p_expiry_information := 0;');
1256 add_body(' end if;');
1257 add_body('end ' || l_rec.expiry_checking_code || ';');
1258 --
1259 add_header('--');
1260 add_header('procedure ' || l_rec.expiry_checking_code || '(');
1261 add_header(' p_owner_payroll_action_id in number,');
1262 add_header(' p_user_payroll_action_id in number,');
1263 add_header(' p_owner_assignment_action_id in number,');
1264 add_header(' p_user_assignment_action_id in number,');
1265 add_header(' p_owner_effective_date in date,');
1266 add_header(' p_user_effective_date in date,');
1267 add_header(' p_dimension_name in varchar2,');
1268 add_header(' p_expiry_information out nocopy date);');
1269 --
1270 add_body('--');
1271 add_body('procedure ' || l_rec.expiry_checking_code || '(');
1272 add_body(' p_owner_payroll_action_id in number,');
1273 add_body(' p_user_payroll_action_id in number,');
1274 add_body(' p_owner_assignment_action_id in number,');
1275 add_body(' p_user_assignment_action_id in number,');
1276 add_body(' p_owner_effective_date in date,');
1277 add_body(' p_user_effective_date in date,');
1278 add_body(' p_dimension_name in varchar2,');
1279 add_body(' p_expiry_information out nocopy date)');
1280 add_body('is');
1281 add_body('begin');
1282 add_body(' p_expiry_information := end_date(p_owner_effective_date, fnd_date.canonical_to_date(''' ||
1283 l_reset_date || '''), ''' || l_frequency_type || ''', ' || l_frequency || ');');
1284 add_body('end ' || l_rec.expiry_checking_code || ';');
1285 end if;
1286 --
1287 if l_rec.start_date_code is not null then
1288 l_rec.start_date_code := substr(l_rec.start_date_code, instr(l_rec.start_date_code, '.') + 1);
1289 --
1290 add_header('--');
1291 add_header('procedure ' || l_rec.start_date_code || '(');
1292 add_header(' p_effective_date in date,');
1293 add_header(' p_start_date out nocopy date,');
1294 add_header(' p_payroll_id in number,');
1295 add_header(' p_bus_grp in number,');
1296 add_header(' p_asg_action in number);');
1297 --
1298 add_body('--');
1299 add_body('procedure ' || l_rec.start_date_code || '(');
1300 add_body(' p_effective_date in date,');
1301 add_body(' p_start_date out nocopy date,');
1302 add_body(' p_payroll_id in number,');
1303 add_body(' p_bus_grp in number,');
1304 add_body(' p_asg_action in number)');
1305 add_body('is');
1306 add_body('begin');
1307 add_body(' p_start_date := start_date(p_effective_date, fnd_date.canonical_to_date(''' ||
1308 l_reset_date || '''), ''' || l_frequency_type || ''', ' || l_frequency || ');');
1309 add_body('end ' || l_rec.start_date_code || ';');
1310 end if;
1311 end loop;
1312 --
1313 if l_header.count > 0 then
1314 add_header('--');
1315 add_header('end pay_jp_dynamic_dimension_pkg;');
1316 --
1317 add_body('--');
1318 add_body('end pay_jp_dynamic_dimension_pkg;');
1319 --
1320 l_csr := dbms_sql.open_cursor;
1321 --
1322 dbms_sql.parse(l_csr, l_header, 1, l_header.count, true, dbms_sql.native);
1323 l_dummy := dbms_sql.execute(l_csr);
1324 --
1325 dbms_sql.parse(l_csr, l_body, 1, l_body.count, true, dbms_sql.native);
1326 l_dummy := dbms_sql.execute(l_csr);
1327 --
1328 dbms_sql.close_cursor(l_csr);
1329 end if;
1330 end rebuild_package;
1331 --
1332 procedure rebuild_package(
1333 errbuf out nocopy varchar2,
1334 retcode out nocopy varchar2,
1335 p_rebuild_dimension in varchar2)
1336 is
1337 begin
1338 --
1339 -- disable in progress of online patch
1340 if ad_zd.get_edition('PATCH') is not null then
1341 --
1342 fnd_message.set_name('FND','AD_ZD_DISABLED_FEATURE');
1343 fnd_message.raise_error;
1344 --
1345 end if;
1346 --
1347 retcode := 0;
1348 --
1349 rebuild_package(p_rebuild_dimension = 'Y');
1350 exception
1351 when others then
1352 retcode := 2;
1353 errbuf := sqlerrm;
1354 rollback;
1355 end rebuild_package;
1356 --
1357 end pay_jp_dimension_generator_pkg;