[Home] [Help]
PACKAGE BODY: APPS.PAY_FR_BAL_UPLOAD
Source
4 --
1 package body pay_fr_bal_upload as
2 /* $Header: pyfrupld.pkb 120.0 2005/05/29 05:12:46 appldev noship $ */
3 --
5 g_sot date := hr_api.g_sot;
6 g_eot date := hr_api.g_eot;
7 g_iv_limit constant number := 15;
8 g_classification_name constant varchar2(30) := 'Balance Initialization';
9 g_element_name_prefix constant varchar2(30) := 'Initial_Value_';
10 g_alphabet constant varchar2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
11 g_package constant varchar2(30) := 'pay_fr_bal_upload.';
12 -----------------------------------------------------------------------------
13 --
14 -- NAME
15 -- expiry_date
16 -- PURPOSE
17 -- Returns the expiry date of a given dimension relative to a date.
18 -- ARGUMENTS
19 -- p_upload_date - the date on which the balance should be correct.
20 -- p_dimension_name - the dimension being set (in caps).
21 -- p_assignment_id - the assignment involved.
22 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
23 -- USES
24 -- NOTES
25 -- This is used by pay_balance_upload.dim_expiry_date.
26 -- If the expiry date cannot be derived then it is set to the end of time
27 -- to indicate that a failure has occured. The process that uses the
31 (
28 -- expiry date knows this rule and acts accordingly.
29 -----------------------------------------------------------------------------
30 function expiry_date
32 p_upload_date date,
33 p_dimension_name varchar2,
34 p_assignment_id number,
35 p_original_entry_id number
36 ) return date is
37 --
38 cursor csr_asg_itd_start is
39 -- Returns the Earliest date that can be used for uploading
40 -- for the assignment, therefore ensures that a time period
41 -- exists, and uses the greatest of the assignment start and
42 -- time period start. Used for ITD date, and as a minimum
43 -- for other dimensions.
44 select nvl(greatest(min(ASS.effective_start_date),
45 min(PTP.start_date)), g_eot)
46 from per_assignments_f ASS
47 ,per_time_periods PTP
48 where ASS.assignment_id = p_assignment_id
49 and ASS.effective_start_date <= p_upload_date
50 and PTP.start_date <= p_upload_date
51 and PTP.payroll_id = ASS.payroll_id
52 and ASS.establishment_id is not null;
53 --
54 cursor csr_ele_itd_start is
55 -- Returns the earliest date on which the element entry exists.
56 --
57 select nvl(min(EE.effective_start_date), g_eot)
58 from pay_element_entries_f EE
59 where EE.assignment_id = p_assignment_id
60 and (EE.element_entry_id = p_original_entry_id or
61 EE.original_entry_id = p_original_entry_id)
62 and EE.effective_start_date <= p_upload_date;
63 --
64 cursor csr_period_start is
65 -- Returns the start date of the current period on the upload date.
66 select nvl(PTP.start_date, g_eot)
67 from per_time_periods PTP
68 ,per_assignments_f ASS
69 where ASS.assignment_id = p_assignment_id
70 and p_upload_date between ASS.effective_start_date
71 and ASS.effective_end_date
72 and PTP.payroll_id = ASS.payroll_id
73 and p_upload_date between PTP.start_date
74 and PTP.end_date;
75 --
76 l_asg_itd_start_date date; -- The assignment start date.
77 l_ele_itd_start_date date; -- The earliest date an element entry exists.
78 l_period_start_date date; -- start date of the upload date period.
79 l_expiry_date date;
80 begin
81 open csr_asg_itd_start;
82 fetch csr_asg_itd_start into l_asg_itd_start_date;
83 close csr_asg_itd_start;
84 if p_dimension_name = 'FR ELEMENT-LEVEL ELE_ITD' then
85 open csr_ele_itd_start;
86 fetch csr_ele_itd_start into l_ele_itd_start_date;
87 close csr_ele_itd_start;
88 l_expiry_date := greatest(l_ele_itd_start_date,l_asg_itd_start_date);
89 elsif p_dimension_name = 'ASSIGNMENT PRORATION RUN TO DATE' then
90 if p_upload_date >= l_asg_itd_start_date then
91 l_expiry_date := p_upload_date;
92 else
93 l_expiry_date := g_eot;
94 end if;
95 elsif p_dimension_name = 'ASSIGNMENT PERIOD TO DATE' then
96 open csr_period_start;
97 fetch csr_period_start into l_period_start_date;
98 close csr_period_start;
99 l_expiry_date := greatest(l_period_start_date,l_asg_itd_start_date);
100 elsif p_dimension_name in ('ASSIGNMENT ESTABLISHMENT YEAR TO DATE',
101 'ASSIGNMENT YEAR TO DATE') then
102 l_expiry_date := greatest(trunc(p_upload_date,'Y'),
103 l_asg_itd_start_date);
104 elsif p_dimension_name = 'FR ASSIGNMENT-LEVEL ASG_ITD' then
105 l_expiry_date := l_asg_itd_start_date;
106 else
107 l_expiry_date := g_eot;
108 end if;
109 return l_expiry_date;
110 exception
111 when others then
112 return g_eot;
113 end expiry_date;
114
115 -----------------------------------------------------------------------------
116 -- NAME
117 -- is_supported
118 -- PURPOSE
119 -- Checks if the dimension is supported by the upload process.
120 -- ARGUMENTS
121 -- p_dimension_name - the balance dimension to be checked (in caps).
122 -- USES
123 -- NOTES
124 -- This is used by pay_balance_upload.validate_dimension.
125 -- Only a subset of the FR dimensions are supported.
126 -- A return of zero denotes that the dimension is not supported.
127 -----------------------------------------------------------------------------
128 function is_supported(p_dimension_name varchar2) return number is
129 begin
130 if p_dimension_name in ('FR ELEMENT-LEVEL ELE_ITD',
131 'ASSIGNMENT PRORATION RUN TO DATE',
132 'ASSIGNMENT PERIOD TO DATE',
133 'ASSIGNMENT ESTABLISHMENT YEAR TO DATE',
134 'ASSIGNMENT YEAR TO DATE',
135 'FR ASSIGNMENT-LEVEL ASG_ITD')
136 then
137 return 1;
138 else
139 return 0;
140 end if;
141 end is_supported;
142
143
144 -----------------------------------------------------------------------------
145 -- NAME
146 -- validate_batch_lines
147 -- PURPOSE
148 -- Applies FR specific validation to the batch.
149 -- ARGUMENTS
150 -- p_batch_id - the batch to be validate_batch_linesd.
151 -- USES
152 -- NOTES
153 -- This is used by pay_balance_upload.validate_batch_lines.
154 -----------------------------------------------------------------------------
155 procedure validate_batch_lines(p_batch_id number)
156 is
157 type t_message_line is record (
158 batch_line_id pay_balance_batch_lines.batch_line_id%TYPE,
162 index by binary_integer;
159 message_number binary_integer);
160 type t_message_lines is table of t_message_line index by binary_integer;
161 type t_messages is table of pay_message_lines.line_text%TYPE
163 tbl_messages t_messages;
164 tbl_msg_lines t_message_lines;
165 --
166 --cursor csr_batch_header_details(p_batch_id number) is
167 --select upload_date
168 --from pay_balance_batch_headers
169 --where batch_id = p_batch_id;
170 --
171 cursor csr_batch_line_validate(p_batch_id number) is
172 select *
173 from pay_balance_batch_lines BL
174 where BL.batch_id = p_batch_id
175 and BL.batch_line_status in ('V','E')
176 for update;
177 --
178 l_proc constant varchar2(61) := g_package||'validate_batch_lines';
179 l_next_msg_line binary_integer := 1;
180 l_line_in_error boolean;
181 --l_batch csr_batch_header_details%ROWTYPE;
182 --
183 procedure write_message_line(p_batch_line_id number
184 ,p_message_name varchar2
185 ,p_message_num binary_integer) is
186 begin
187 if not tbl_messages.exists(p_message_num) then
188 hr_utility.set_message(801, p_message_name);
189 tbl_messages(p_message_num) := substrb(hr_utility.get_message, 1, 240);
190 end if;
191 tbl_msg_lines(l_next_msg_line).batch_line_id := p_batch_line_id;
192 tbl_msg_lines(l_next_msg_line).message_number := p_message_num;
193 l_next_msg_line := l_next_msg_line + 1;
194 end write_message_line;
195 --
196 procedure write_message_lines is
197 begin
198 for i in 1..l_next_msg_line-1 loop
199 insert into pay_message_lines
200 (line_sequence
201 ,message_level
202 ,source_id
203 ,source_type
204 ,line_text)
205 values
206 (pay_message_lines_s.nextval
207 ,'F' -- 'F'atal
208 ,tbl_msg_lines(i).batch_line_id
209 ,'L'
210 ,tbl_messages(tbl_msg_lines(i).message_number));
211 end loop;
212 end write_message_lines;
213 --
214 procedure error_message_line(p_batch_line_id number) is
215 begin
216 update pay_balance_batch_lines
217 set batch_line_status = 'E'
218 where batch_id = p_batch_id
219 and batch_line_id = p_batch_line_id;
220 end error_message_line;
221 --
222 begin -- validate_batch_lines
223 hr_utility.set_location('Entering: '|| l_proc, 10);
224 --open csr_batch_header_details(p_batch_id);
225 --fetch csr_batch_header_details into l_batch;
226 --close csr_batch_header_details;
227 --
228 for batch_line in csr_batch_line_validate(p_batch_id) loop
229 l_line_in_error := false;
230 if batch_line.dimension_name in ('ASSIGNMENT PERIOD TO DATE',
231 'ASSIGNMENT PRORATION RUN TO DATE')
232 and batch_line.upload_date is null
233 then
234 l_line_in_error := true;
235 write_message_line(batch_line.batch_line_id,
236 'PAY_75092_BLD_HISTORICAL_ONLY',
237 75092);
238 end if;
239 --
240 if l_line_in_error and batch_line.batch_line_status <> 'E' then
241 error_message_line(batch_line.batch_line_id);
242 end if;
243 end loop;
244 write_message_lines;
245 hr_utility.set_location(' Leaving: '||l_proc, 90);
246 end validate_batch_lines;
247 --
248 -----------------------------------------------------------------------------
249 -- NAME
250 -- create_structure
251 -- PURPOSE
252 -- Creates the structure for Balance Upload
253 -- ARGUMENTS
254 -- p_batch_id - the batch for which a structure needs to be generated
255 -- NOTES
256 -- This is called from the SRS
257 -----------------------------------------------------------------------------
258 --
259 procedure create_structure(p_business_group_id in number,
260 p_batch_id in number)
261 ------------------------------------------------------------------------
262 is
263 type t_cxt_iv is record (
264 iv_name pay_legislation_contexts.input_value_name%TYPE,
265 UOM pay_input_values_f.uom%TYPE);
266 type t_cxt_ivs is table of t_cxt_iv index by binary_integer;
267 tbl_cxt_ivs t_cxt_ivs;
268 --
269 l_num_cxts number := 0;
270 l_business_group_name per_business_groups_perf.name%TYPE;
271 l_legislation_code per_business_groups_perf.legislation_code%TYPE;
272 l_BG_currency_code per_business_groups_perf.currency_code%TYPE;
273 l_prev_currency_code pay_balance_types.currency_code%TYPE;
274 l_element_type_id pay_element_types_f.element_type_id%TYPE;
275 l_element_name pay_element_types_f.element_name%TYPE;
276 l_element_counter number;
277 l_element_link_id number;
278 l_iv_counter number;
279 --
280 -- Cursor to derive necessary parameters in later phase.
281 --
282 cursor csr_bg is
283 select
284 pbg.name,
285 pbg.legislation_code,
286 pbg.currency_code
287 from
288 per_business_groups_perf pbg
289 where pbg.business_group_id = p_business_group_id;
290 --
291 Cursor csr_cxt_ivs is
292 select lc.input_value_name,
293 decode(c.data_type,'T','C',c.data_type) UOM
294 from pay_legislation_contexts lc,
295 ff_contexts c
296 where c.context_id = lc.context_id
297 and lc.input_value_name is not null
298 and lc.legislation_code = l_legislation_code;
299 --
303 cursor csr_balance_wo_feed is
300 -- Cursor to return balance types without balance initialization element feed
301 -- for current batch_id
302 --
304 select pbt2.balance_type_id id,
305 pbt2.balance_name name,
306 pbt2.balance_uom uom,
307 nvl(pbt2.currency_code,l_BG_currency_code) currency_code
308 from pay_balance_types pbt2
309 where pbt2.balance_type_id in (select pbt.balance_type_id
310 from pay_balance_batch_lines bbl,
311 pay_balance_types pbt
312 where bbl.batch_id = p_batch_id
313 and (bbl.balance_type_id = pbt.balance_type_id or
314 (bbl.balance_type_id is null
315 and upper(pbt.balance_name) = upper(bbl.balance_name)))
316 and nvl(pbt.business_group_id, p_business_group_id) = p_business_group_id
317 and nvl(pbt.legislation_code, l_legislation_code) = l_legislation_code
318 and not exists(
319 select 1
320 from pay_element_classifications pec,
321 pay_element_types_f pet,
322 pay_input_values_f piv,
323 pay_balance_feeds_f pbf
324 where pbf.balance_type_id = pbt.balance_type_id
325 and pbf.effective_start_date = g_sot
326 and pbf.effective_end_date = g_eot
327 and nvl(pbf.business_group_id, p_business_group_id) = p_business_group_id
328 and nvl(pbf.legislation_code, l_legislation_code) = l_legislation_code
329 and piv.input_value_id = pbf.input_value_id
330 and piv.effective_start_date = g_sot
331 and piv.effective_end_date = g_eot
332 and pet.element_type_id = piv.element_type_id
333 and pet.effective_start_date = g_sot
334 and pet.effective_end_date = g_eot
335 and pec.classification_id = pet.classification_id
336 and pec.balance_initialization_flag = 'Y'))
337 order by nvl(pbt2.currency_code,l_BG_currency_code)
338 for update;
339
340 --------------------------------------------------------------
341 function create_iv(
342 p_element_type_id in number,
343 p_element_name in varchar2,
344 p_element_link_id in number,
345 p_input_value_name in varchar2,
346 p_uom in varchar2,
347 p_display_sequence in number) return number
348 --------------------------------------------------------------
349 is
350 l_input_value_id number;
351 begin
352 l_input_value_id := pay_db_pay_setup.create_input_value(
353 p_element_name => p_element_name,
354 p_name => p_input_value_name,
355 p_uom_code => p_uom,
356 p_business_group_name => l_business_group_name,
357 p_effective_start_date => g_sot,
358 p_effective_end_date => g_eot,
359 p_display_sequence => p_display_sequence);
360 --
361 hr_input_values.create_link_input_value(
362 p_insert_type => 'INSERT_INPUT_VALUE',
363 p_element_link_id => p_element_link_id,
364 p_input_value_id => l_input_value_id,
365 p_input_value_name => p_input_value_name,
366 p_costable_type => NULL,
367 p_validation_start_date => g_sot,
368 p_validation_end_date => g_eot,
369 p_default_value => NULL,
370 p_max_value => NULL,
371 p_min_value => NULL,
372 p_warning_or_error_flag => NULL,
373 p_hot_default_flag => NULL,
374 p_legislation_code => NULL,
375 p_pay_value_name => NULL,
376 p_element_type_id => p_element_type_id);
377 --
378 return l_input_value_id;
379 end create_iv;
380 --------------------------------------------------------------
381 procedure create_iv_bf(
382 p_balance_type_id in number,
383 p_balance_uom in varchar2,
384 p_element_type_id in number,
385 p_element_name in varchar2,
386 p_element_link_id in number,
387 p_input_value_name in varchar2,
388 p_display_sequence in number)
389 --------------------------------------------------------------
390 is
391 l_input_value_id number;
392 begin
393 l_input_value_id := create_iv(
394 p_element_type_id => p_element_type_id,
395 p_element_name => p_element_name,
396 p_element_link_id => p_element_link_id,
397 p_input_value_name => p_input_value_name,
398 p_uom => p_balance_uom,
399 p_display_sequence => p_display_sequence);
400 --
401 hr_balances.ins_balance_feed(
402 p_option => 'INS_MANUAL_FEED',
403 p_input_value_id => l_input_value_id,
404 p_element_type_id => p_element_type_id,
405 p_primary_classification_id => NULL,
406 p_sub_classification_id => NULL,
407 p_sub_classification_rule_id => NULL,
408 p_balance_type_id => p_balance_type_id,
409 p_scale => '1',
410 p_session_date => g_sot,
411 p_business_group => p_business_group_id,
412 p_legislation_code => NULL,
413 p_mode => 'USER');
414 end create_iv_bf;
415 --------------------------------------------------------------
416 procedure create_et_el(
417 p_currency_code in varchar2,
418 p_element_type_id out NOCOPY number,
419 p_element_name out NOCOPY varchar2,
420 p_element_link_id out NOCOPY number)
421 --------------------------------------------------------------
422 is
423 --
424 l_iv_id number;
425 --
426 procedure init_element_counter
427 is
428 cursor csr_et(p_prefix varchar2) is
429 select
430 nvl(max(to_number(translate(upper(substr(element_name,
431 instr(element_name,'_',-1)+1))
432 ,'0 _'||g_alphabet,'0'))),0)+1
433 from pay_element_types_f
434 where element_name like p_prefix;
435 begin
436 open csr_et(g_element_name_prefix || p_batch_id || '%');
437 fetch csr_et into l_element_counter;
438 close csr_et;
439 end init_element_counter;
440 begin -- create_et_el
441 if l_element_counter is null then
442 init_element_counter;
443 else l_element_counter := l_element_counter + 1;
444 end if;
445
446 p_element_name := g_element_name_prefix || p_batch_id || '_' ||
447 l_element_counter;
448 --
449 p_element_type_id := pay_db_pay_setup.create_element(
450 p_element_name => p_element_name,
451 p_effective_start_date => g_sot,
452 p_effective_end_date => g_eot,
453 p_classification_name => g_classification_name,
454 p_input_currency_code => p_currency_code,
455 p_output_currency_code => p_currency_code,
456 p_processing_type => 'N',
457 p_adjustment_only_flag => 'Y',
458 p_process_in_run_flag => 'Y',
459 p_business_group_name => l_business_group_name,
460 p_post_termination_rule => 'Final Close');
461 --
462 update pay_element_types_f
463 set element_information1 = 'B'
464 where element_type_id = p_element_type_id;
465 --
466 p_element_link_id := pay_db_pay_setup.create_element_link(
467 p_element_name => p_element_name,
468 p_link_to_all_pyrlls_fl => 'Y',
469 p_standard_link_flag => 'N',
470 p_effective_start_date => g_sot,
471 p_effective_end_date => g_eot,
472 p_business_group_name => l_business_group_name);
473 for i in 1..l_num_cxts loop
474 l_iv_id := create_iv(
475 p_element_type_id => p_element_type_id,
476 p_element_name => p_element_name,
477 p_element_link_id => p_element_link_id,
478 p_input_value_name => tbl_cxt_ivs(i).iv_name,
479 p_uom => tbl_cxt_ivs(i).UOM,
480 p_display_sequence => i);
481 end loop;
482 end create_et_el;
483 begin -- create_structure
484 open csr_bg;
485 fetch csr_bg into
486 l_business_group_name,
487 l_legislation_code,
488 l_BG_currency_code;
489 close csr_bg;
490 -- Cache the contexts
491 for l_cxt_iv in csr_cxt_ivs loop
492 l_num_cxts := l_num_cxts +1;
493 tbl_cxt_ivs(l_num_cxts).iv_name := l_cxt_iv.input_value_name;
494 tbl_cxt_ivs(l_num_cxts).UOM := l_cxt_iv.UOM;
495 end loop;
496 --
497 -- Loop of balances without initial balance feed for current batch_id.
498 --
499 for l_bal in csr_balance_wo_feed loop
500 if l_iv_counter is null or l_iv_counter >= g_iv_limit - l_num_cxts
501 or l_bal.currency_code <> l_prev_currency_code
502 then
503 l_iv_counter := 1;
504 --
505 -- Create element type and link, plus context inputs with link inputs.
506 --
507 create_et_el(
508 p_currency_code => l_bal.currency_code, -- IN
509 p_element_type_id => l_element_type_id, -- OUT
510 p_element_name => l_element_name, -- OUT
511 p_element_link_id => l_element_link_id); -- OUT
512 --
513 l_prev_currency_code := l_bal.currency_code;
514 else
515 l_iv_counter := l_iv_counter + 1;
516 end if;
517 --
518 -- Create input_value, link_input_value and balance_feed.
519 --
520 create_iv_bf(
521 p_balance_type_id => l_bal.id,
522 p_balance_uom => l_bal.uom,
523 p_element_type_id => l_element_type_id,
524 p_element_name => l_element_name,
525 p_element_link_id => l_element_link_id,
526 p_input_value_name => rtrim(substr(l_bal.name, 1, 27)) || '_' ||
527 lpad(to_char(l_iv_counter),2,'0'),
528 p_display_sequence => l_iv_counter+l_num_cxts);
529 end loop;
530 end create_structure;
531 ------------------------------------------------------------------------
532 procedure create_structure(
533 errbuf out NOCOPY varchar2,
534 retcode out NOCOPY number,
535 p_business_group_id in number,
536 p_batch_id in number)
537 is
538 begin
539 --
540 -- errbuf and retcode are special parameters needed for the SRS.
541 -- retcode = 0 means no error and retcode = 2 means an error occurred.
542 --
543 retcode := 0;
544 create_structure(p_batch_id => p_batch_id,
545 p_business_group_id => p_business_group_id);
546 --
547 commit;
548 exception
549 WHEN HR_UTILITY.HR_ERROR THEN
550 retcode := 2;
551 errbuf := SUBSTRB(HR_UTILITY.GET_MESSAGE,1,240);
552 rollback;
553 when others then
554 retcode := 2;
555 errbuf := substrb(SQLERRM,1,240);
556 rollback;
557 end create_structure;
558 --
559 end pay_fr_bal_upload;