1 package body PAY_NL_GENERAL as
2 /* $Header: pynlgenr.pkb 120.19.12020000.2 2012/11/29 14:12:27 mkuppuch ship $ */
3 --
4 g_package varchar2(30) := 'pay_nl_general';
5 hr_formula_error EXCEPTION;
6
7 -- +********************************************************************+
8 -- | PUBLIC FUNCTIONS |
9 -- +********************************************************************+
10 --
11 ------------------------------------------------------------------------
12 -- Function GET_POSTAL_CODE
13 -- This function gets a string with a space at the 5th position and
14 -- returns the string with the space removed.
15 ------------------------------------------------------------------------
16 function get_postal_code
17 (p_postal_code in varchar2)
18 return varchar2
19 is
20
21 begin
22 if length(p_postal_code) = 7 then
23 return concat(substr(p_postal_code,1,4),substr(p_postal_code,6,2));
24 else
25 return p_postal_code;
26 end if;
27 end get_postal_code;
28
29 ------------------------------------------------------------------------
30 -- Function GET_POSTAL_CODE_NEW
31 -- This function gets a string without a space and returns it with
32 -- a space at the 5th position.
33 ------------------------------------------------------------------------
34 function get_postal_code_new
35 (p_postal_code in varchar2)
36 return varchar2
37 is
38
39 begin
40 if length(p_postal_code) = 6 then
41 return concat(substr(p_postal_code,1,4),concat(' ',substr(p_postal_code,5,2)));
42 else
43 return p_postal_code;
44 end if;
45 end get_postal_code_new;
46
47
48 ------------------------------------------------------------------------
49 -- Function GET_MESSAGE
50 -- This function is used to obtain a message.
51 -- The token parameters must be of the form 'TOKEN_NAME:TOKEN_VALUE' i.e.
52 -- If you want to set the value of a token called ELEMENT to Social Ins
53 -- the token parameter would be 'ELEMENT:Social Ins.'
54 ------------------------------------------------------------------------
55 function get_message
56 (p_product in varchar2
57 ,p_message_name in varchar2
58 ,p_token1 in varchar2 default null
59 ,p_token2 in varchar2 default null
60 ,p_token3 in varchar2 default null)
61 return varchar2
62 is
63 l_message varchar2(2000);
64 l_token_name varchar2(20);
65 l_token_value varchar2(80);
66 l_colon_position number;
67 l_proc varchar2(72) := g_package||'.get_message';
68 --
69 begin
70 --
71 hr_utility.set_location('Entered '||l_proc,5);
72 hr_utility.set_location('. Message Name: '||p_message_name,40);
73
74 fnd_message.set_name(p_product, p_message_name);
75
76 if p_token1 is not null then
77 /* Obtain token 1 name and value */
78 l_colon_position := instr(p_token1,':');
79 l_token_name := substr(p_token1,1,l_colon_position-1);
80 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
81 fnd_message.set_token(l_token_name, l_token_value);
82 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
83 end if;
84
85 if p_token2 is not null then
86 /* Obtain token 2 name and value */
87 l_colon_position := instr(p_token2,':');
88 l_token_name := substr(p_token2,1,l_colon_position-1);
89 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
90 fnd_message.set_token(l_token_name, l_token_value);
91 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
92 end if;
93
94 if p_token3 is not null then
95 /* Obtain token 3 name and value */
96 l_colon_position := instr(p_token3,':');
97 l_token_name := substr(p_token3,1,l_colon_position-1);
98 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
99 fnd_message.set_token(l_token_name, l_token_value);
100 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
101 end if;
102
103 l_message := substrb(fnd_message.get,1,254);
104
105 hr_utility.set_location('leaving '||l_proc,100);
106
107 return l_message;
108 end get_message;
109 --
110
111 ---------------------------------------------------------------------------
112 -- Function: PAY_PERIOD_ASG_DATES
113 -- Description: Function returns pay period assignment dates
114 ---------------------------------------------------------------------------
115
116 function get_period_asg_dates (p_assignment_id in number
117 ,p_period_start_date in date
118 ,p_period_end_date in date
119 ,p_asg_start_date out nocopy date
120 ,p_asg_end_date out nocopy date
121 ) return number is
122
123
124 cursor csr_asg_dates is
125 select min(asg.effective_start_date) asg_start_date
126 ,max(asg.effective_end_date) asg_end_date
127 from per_assignments_f asg,
128 per_assignment_status_types past
129 where asg.assignment_id = p_assignment_id
130 and past.per_system_status = 'ACTIVE_ASSIGN'
131 and asg.assignment_status_type_id = past.assignment_status_type_id
132 and asg.effective_start_date <= p_period_end_date
133 and nvl(asg.effective_end_date, p_period_end_date) >= p_period_start_date;
134
135 cursor csr_asg_act_dates (p_date date) is
136 select asg.effective_end_date
137 from per_assignments_f asg,
138 per_assignment_status_types past
139 where asg.assignment_id = p_assignment_id
140 and past.per_system_status = 'ACTIVE_ASSIGN'
141 and asg.assignment_status_type_id = past.assignment_status_type_id
142 and asg.effective_start_date =p_date;
143
144 v_csr_asg_act_dates csr_asg_act_dates%ROWTYPE;
145
146 begin
147
148 hr_utility.set_location('get_period_asg_dates',1);
149
150 open csr_asg_dates;
151 fetch csr_asg_dates into p_asg_start_date,p_asg_end_date;
152 close csr_asg_dates;
153
154 --Bug 3119100
155 /*Check if the Assignment has a Active Record starting from
156 next day in which case return Effective End date of that assignment
157 record else return the date obtained as before(Indicating the asg
158 is inactive starting from the next day */
159 if p_asg_end_date = p_period_end_date then
160
161 OPEN csr_asg_act_dates(p_asg_end_date+1);
162 FETCH csr_asg_act_dates INTO v_csr_asg_act_dates;
163 IF csr_asg_act_dates%FOUND THEN
164 p_asg_end_date := v_csr_asg_act_dates.effective_end_date;
165 END IF;
166 CLOSE csr_asg_act_dates;
167
168 end if;
169
170 hr_utility.set_location('get_period_asg_dates',99);
171
172 return 1;
173
174 exception
175
176 when others then
177 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
178 raise;
179
180 end get_period_asg_dates;
181
182 ------------------------------------------------------------------
183 -- Function : get_run_result_value
184 -- This is a generic function that returns the run result value
185 -- given the assignment_action_id , element_Type_id,
186 -- input_value_id and run_result_id
187 ------------------------------------------------------------------
188
189 function get_run_result_value(p_assignment_action_id number,
190 p_element_type_id number,
191 p_input_value_id number,
192 p_run_result_id number,
193 p_UOM varchar2)return varchar2 is
194
195 cursor csr_get_run_result_value(p_assignment_action_id number,
196 p_element_type_id number,
197 p_input_value_id number,p_run_result_id number)is
198 select prrv.result_value from pay_run_result_values prrv,pay_run_results prr
199 where prr.assignment_action_id=p_assignment_action_id
200 and prr.element_type_id=p_element_type_id
201 and prr.run_result_id=p_run_result_id
202 and prrv.run_result_id=prr.run_result_id
203 and prrv.input_value_id=p_input_value_id;
204
205 l_result_value pay_run_result_values.result_value%TYPE;
206
207 begin
208
209 OPEN csr_get_run_result_value(p_assignment_action_id,p_element_type_id,p_input_value_id,p_run_result_id);
210 FETCH csr_get_run_result_value into l_result_value;
211 IF p_UOM = 'M' OR p_UOM = 'N' THEN
212 l_result_value := to_char(fnd_number.canonical_to_number(l_result_value));
213 END IF;
214 CLOSE csr_get_run_Result_value;
215
216 return l_result_value;
217
218 exception
219
220 when others then
221 hr_utility.trace('SQLERRM:'||substr(sqlerrm,1,200));
222 raise;
223
224 end get_run_result_value;
225
226
227 ------------------------------------------------------------------
228 -- Function : get_run_result_value
229 -- This is a generic function that returns the run result value
230 -- given the assignment_action_id , element_Type_id and
231 -- input_value_id
232 ------------------------------------------------------------------
233
234 function get_run_result_value(p_assignment_action_id number,
235 p_element_type_id number,
236 p_input_value_id number)return number is
237
238 cursor csr_get_run_result_value(p_assignment_action_id number,
239 p_element_type_id number,
240 p_input_value_id number)is
241 select fnd_number.canonical_to_number(prrv.result_value) from pay_run_result_values prrv,pay_run_results prr
242 where prr.assignment_action_id=p_assignment_action_id
243 and prr.element_type_id=p_element_type_id
244 and prrv.run_result_id=prr.run_result_id
245 and prrv.input_value_id=p_input_value_id;
246
247 l_result_value number;
248
249 begin
250
251 OPEN csr_get_run_result_value(p_assignment_action_id,p_element_type_id,p_input_value_id);
252 FETCH csr_get_run_result_value into l_result_value;
253 CLOSE csr_get_run_Result_value;
254
255 return l_result_value;
256
257 exception
258
259 when others then
260 hr_utility.trace('SQLERRM:'||substr(sqlerrm,1,200));
261 raise;
262
263 end get_run_result_value;
264
265
266 ---------------------------------------------------------------------------
267 -- Function : get_retro_period
268 -- Function returns the retro period for the given element_entry_id and
269 -- date_earned
270 ---------------------------------------------------------------------------
271
272 function get_retro_period
273 (
274 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
275 p_date_earned in pay_payroll_actions.date_earned%TYPE
276 )return date is
277
278 cursor c_get_creator_type(c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
279 c_date_earned in pay_payroll_actions.date_earned%TYPE
280 ) is
281 SELECT creator_type
282 FROM pay_element_entries_f pee
283 WHERE pee.element_entry_id=c_element_entry_id
284 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
285
286 cursor get_retro_period_rr
287 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
288 c_date_earned in pay_payroll_actions.date_earned%TYPE
289 ) is
290
291 SELECT ptp.start_date
292 FROM per_time_periods ptp,
293 pay_payroll_actions ppa,
294 pay_assignment_actions paa,
295 pay_run_results prr,
296 pay_element_entries_f pee
297 WHERE pee.element_entry_id=c_element_entry_id
298 and prr.run_result_id = pee.source_id
299 and paa.assignment_action_id=prr.assignment_action_id
300 and ppa.payroll_action_id=paa.payroll_action_id
301 and ptp.payroll_id=ppa.payroll_id
302 and pee.creator_type='RR'
303 and ppa.date_earned between ptp.start_date and ptp.end_date
304 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
305
306 cursor get_retro_period_nr
307 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
308 c_date_earned in pay_payroll_actions.date_earned%TYPE
309 ) is
310
311 SELECT ptp.start_date
312 FROM per_time_periods ptp,
313 pay_payroll_actions ppa,
314 pay_assignment_actions paa,
315 pay_run_results prr,
316 pay_element_entries_f pee
317 WHERE pee.element_entry_id=c_element_entry_id
318 and prr.run_result_id = pee.source_id
319 and paa.assignment_action_id=prr.assignment_action_id
320 and ppa.payroll_action_id=paa.payroll_action_id
321 and ptp.payroll_id=ppa.payroll_id
322 and pee.creator_type='NR'
323 and ppa.date_earned between ptp.start_date and ptp.end_date
324 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
325
326 cursor get_retro_period_pr
327 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
328 c_date_earned in pay_payroll_actions.date_earned%TYPE
329 ) is
330
331 SELECT ptp.start_date
332 FROM per_time_periods ptp,
333 pay_payroll_actions ppa,
334 pay_assignment_actions paa,
335 pay_run_results prr,
336 pay_element_entries_f pee
337 WHERE pee.element_entry_id=c_element_entry_id
338 and prr.run_result_id = pee.source_id
339 and paa.assignment_action_id=prr.assignment_action_id
340 and ppa.payroll_action_id=paa.payroll_action_id
341 and ptp.payroll_id=ppa.payroll_id
342 and pee.creator_type='PR'
343 and ppa.date_earned between ptp.start_date and ptp.end_date
344 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
345
346 cursor get_retro_period_ee
347 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
348 c_date_earned in pay_payroll_actions.date_earned%TYPE
349 ) is
350 SELECT ptp.start_date
351 FROM per_time_periods ptp,
352 pay_payroll_actions ppa,
353 pay_assignment_actions paa,
354 pay_element_entries_f pee
355 WHERE pee.element_entry_id=c_element_entry_id
356 and paa.assignment_action_id=pee.source_asg_action_id
357 and ppa.payroll_action_id=paa.payroll_action_id
358 and ptp.payroll_id=ppa.payroll_id
359 and pee.creator_type='EE'
360 and ppa.date_earned between ptp.start_date and ptp.end_date
361 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
362
363 l_creator_type pay_element_entries_f.creator_type%TYPE;
364 l_period_obtained_flag number;
365 l_retro_date date;
366
367
368
369 begin
370 l_period_obtained_flag:=1;
371 hr_utility.set_location('Entering: '||l_period_obtained_flag,1);
372
373 OPEN c_get_creator_type(p_element_entry_id,p_date_earned);
374 FETCH c_get_creator_type INTO l_creator_type ;
375 CLOSE c_get_creator_type;
376
377
378 if l_creator_type = 'RR' then
379 OPEN get_retro_period_rr(p_element_entry_id,p_date_earned);
380 FETCH get_retro_period_rr into l_retro_date;
381 CLOSE get_retro_period_rr;
382 l_period_obtained_flag:=1;
383 end if;
384
385 if l_creator_type = 'NR' then
386 OPEN get_retro_period_nr(p_element_entry_id,p_date_earned);
387 FETCH get_retro_period_nr into l_retro_date;
388 CLOSE get_retro_period_nr;
389 l_period_obtained_flag:=1;
390 end if;
391
392 if l_creator_type = 'PR' then
393 OPEN get_retro_period_pr(p_element_entry_id,p_date_earned);
394 FETCH get_retro_period_pr into l_retro_date;
395 CLOSE get_retro_period_pr;
396 l_period_obtained_flag:=1;
397 end if;
398
399 if l_creator_type = 'EE' then
400 OPEN get_retro_period_ee(p_element_entry_id,p_date_earned);
401 FETCH get_retro_period_ee into l_retro_date;
402 CLOSE get_retro_period_ee;
403 l_period_obtained_flag:=1;
404 end if;
405
406 hr_utility.set_location('Entering element entry id: '||p_element_entry_id,4);
407 hr_utility.set_location('Entering start date earned : '||p_date_earned,5);
408 hr_utility.set_location('Entering period obtained flag: '||l_period_obtained_flag,6);
409
410 return l_retro_date;
411
412
413 end get_retro_period;
414
415 ---------------------------------------------------------------------------
416 -- Function : get_defined_balance_id
417 -- Function returns the defined balance id
418 ---------------------------------------------------------------------------
419
420 FUNCTION GET_DEFINED_BALANCE_ID(p_user_name IN VARCHAR2) RETURN NUMBER
421 IS
422 /* Cursor to retrieve Defined Balance Id */
423 CURSOR csr_def_bal_id(p_user_name VARCHAR2) IS
424 SELECT u.creator_id
425 FROM ff_user_entities u,
426 ff_database_items d
427 WHERE d.user_name = p_user_name
428 AND u.user_entity_id = d.user_entity_id
429 AND (u.legislation_code = 'NL' )
430 AND (u.business_group_id IS NULL )
431 AND u.creator_type = 'B';
432
433 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
434
435 BEGIN
436 OPEN csr_def_bal_id(p_user_name);
437 FETCH csr_def_bal_id INTO l_defined_balance_id;
438 CLOSE csr_def_bal_id;
439 RETURN l_defined_balance_id;
440
441 END GET_DEFINED_BALANCE_ID;
442
443 ---------------------------------------------------------------------------
444 -- Function : get_iv_run_result
445 -- Function returns the input value run_result for the specified element
446 -- input value name
447 ---------------------------------------------------------------------------
448 FUNCTION get_iv_run_result(p_run_result_id IN NUMBER
449 ,p_element_type_id IN NUMBER
450 ,p_input_value_name IN VARCHAR2) RETURN VARCHAR2 IS
451 CURSOR cur_iv_id(lp_element_type_id number,lp_input_value_name varchar2) IS
452 SELECT iv.input_value_id
453 from pay_input_values_f iv
454 where iv.name=lp_input_value_name
455 and iv.element_type_id=lp_element_type_id;
456 v_cur_iv_id cur_iv_id%ROWTYPE;
457
458 CURSOR cur_iv_rrv(lp_input_value_id number,lp_run_result_id in number) IS
459 SELECT rrv.result_value
460 from pay_run_result_values rrv
461 where rrv.input_value_id=lp_input_value_id
462 and rrv.run_result_id=p_run_result_id;
463 v_cur_iv_rrv cur_iv_rrv%ROWTYPE;
464
465 BEGIN
466 v_cur_iv_id := null;
467
468 OPEN cur_iv_id(p_element_type_id,p_input_value_name);
469 FETCH cur_iv_id INTO v_cur_iv_id;
470 CLOSE cur_iv_id;
471
472 v_cur_iv_rrv := null;
473 OPEN cur_iv_rrv(v_cur_iv_id.input_value_id,p_run_result_id);
474 FETCH cur_iv_rrv INTO v_cur_iv_rrv;
475 CLOSE cur_iv_rrv;
476
477 return v_cur_iv_rrv.result_value;
478 END get_iv_run_result;
479
480 ---------------------------------------------------------------------------
481 -- Function : get_sit_type_name
482 -- Function returns the Si Type Name for specified Context Balance
483 ---------------------------------------------------------------------------
484 FUNCTION get_sit_type_name(p_balance_type_id pay_balance_types.balance_type_id%TYPE
485 ,p_assgn_action_id NUMBER
486 ,p_date_earned DATE
487 ,p_si_type VARCHAR2) RETURN VARCHAR2 IS
488
489 --
490 CURSOR csr_get_sit_type_name (lp_balance_type_id pay_balance_types.balance_type_id%TYPE
491 ,lp_assgn_action_id NUMBER
492 ,lp_date_earned DATE
493 ,lp_si_type VARCHAR2) IS
494 SELECT prrv1.result_value si_type_name
495 FROM pay_balance_feeds_f pbf
496 ,pay_balance_types pbt
497 ,pay_input_values_f piv
498 ,pay_input_values_f piv1
499 ,pay_input_values_f piv2
500 ,pay_element_types_f pet
501 ,pay_run_results prr
502 ,pay_run_result_values prrv
503 ,pay_run_result_values prrv1
504 WHERE pbf.balance_type_id = pbt.balance_type_id
505 AND pbt.balance_type_id = lp_balance_type_id
506 AND piv.input_value_id = pbf.input_value_id
507 AND (piv.name ='Pay Value'
508 OR piv.name ='Days')
509 AND pet.element_type_id = piv.element_type_id
510 AND pet.classification_id <> (SELECT classification_id
511 from pay_element_classifications
512 where classification_name ='Balance Initialization'
513 and business_group_id is null
514 and legislation_code is null)
515 AND piv1.element_type_id = pet.element_type_id
516 AND piv1.name = 'SI Type Name'
517 AND piv2.element_type_id = pet.element_type_id
518 AND piv2.name = 'SI Type'
519 AND prr.element_type_id = pet.element_type_id
520 AND prr.assignment_action_id = lp_assgn_action_id
521 AND prrv.run_result_id = prr.run_result_id
522 AND prrv.input_value_id = piv2.input_value_id
523 AND prrv.result_value = lp_si_type
524 AND prrv1.run_result_id = prrv.run_result_id
525 AND prrv1.input_value_id = piv1.input_value_id
526 AND lp_date_earned BETWEEN pbf.effective_start_date
527 AND pbf.effective_end_date
528 AND lp_date_earned BETWEEN pet.effective_start_date
529 AND pet.effective_end_date
530 AND lp_date_earned BETWEEN piv.effective_start_date
531 AND piv.effective_end_date
532 AND lp_date_earned BETWEEN piv1.effective_start_date
533 AND piv1.effective_end_date
534 AND lp_date_earned BETWEEN piv2.effective_start_date
535 AND piv2.effective_end_date;
536 v_csr_get_sit_type_name csr_get_sit_type_name%ROWTYPE;
537 BEGIN
538 OPEN csr_get_sit_type_name(p_balance_type_id
539 ,p_assgn_action_id
540 ,p_date_earned
541 ,p_si_type);
542 FETCH csr_get_sit_type_name INTO v_csr_get_sit_type_name;
543 CLOSE csr_get_sit_type_name;
544
545 RETURN v_csr_get_sit_type_name.si_type_name;
546
547 END get_sit_type_name;
548
549 ---------------------------------------------------------------------------
550 -- Procedure : insert_leg_rule
551 -- Creates a Row in Pay_Legislation_Rules
552 ---------------------------------------------------------------------------
553 /* Procedure to insert legislation rule via concurrent program*/
554
555 PROCEDURE insert_leg_rule(errbuf out nocopy varchar2, retcode out nocopy varchar2,p_retropay_method IN number) is
556
557 CURSOR c_leg_rule(p_rule_type varchar2) is
558 SELECT 'Y' FROM pay_legislation_rules
559 WHERE legislation_code='NL'
560 AND rule_type=p_rule_type;
561
562 l_rule_found varchar2(10):='N';
563
564 begin
565 retcode := 0;
566 fnd_file.put_line(FND_FILE.LOG,'Entering Procedure pay_nl_general.insert_leg_rule');
567
568 OPEN c_leg_rule('RETRO_COMP_DFLT_OVERRIDE');
569 FETCH c_leg_rule INTO l_rule_found;
570
571 IF c_leg_rule%NOTFOUND then
572 l_rule_found:='N';
573 END if;
574
575 CLOSE c_leg_rule;
576
577
578
579
580 if (p_retropay_method = 1 AND l_rule_found = 'N') then /* Replacement Method */
581 insert into pay_legislation_rules(legislation_code,rule_type,rule_mode) values ('NL','RETRO_COMP_DFLT_OVERRIDE','Y');
582 fnd_file.put_line(FND_FILE.LOG,'Legislation Rule Added,Retropay method is now REPLACEMENT');
583 else /*Standard Method*/
584 IF (p_retropay_method = 0 AND l_rule_found = 'Y') then
585 delete from pay_legislation_rules
586 where legislation_code='NL'
587 and rule_type='RETRO_COMP_DFLT_OVERRIDE';
588 fnd_file.put_line(FND_FILE.LOG,'Legislation Rule Removed,Retropay method is now STANDARD');
589 END if;
590 end if;
591 fnd_file.put_line(FND_FILE.LOG,'Leaving Procedure pay_nl_general.insert_leg_rule');
592 END insert_leg_rule;
593
594
595 ---------------------------------------------------------------------------
596 -- Function : get_default_retro_definition
597 -- Function returns the Default Retro Definition ID
598 ---------------------------------------------------------------------------
599
600
601 FUNCTION get_default_retro_definition(p_business_group_id IN number)
602 RETURN NUMBER
603 is
604
605 CURSOR c_leg_rule(p_rule_type varchar2) is
606 SELECT 'Y' FROM pay_legislation_rules
607 WHERE legislation_code='NL'
608 AND rule_type=p_rule_type;
609
610 CURSOR c_std_retro_definition
611 is
612 SELECT prd.retro_definition_id
613 FROM pay_retro_definitions prd
614 WHERE prd.legislation_code = 'NL'
615 AND prd.definition_name='Standard Retropay';
616
617 CURSOR c_rep_retro_definition
618 is
619 SELECT prd.retro_definition_id
620 FROM pay_retro_definitions prd
621 WHERE prd.legislation_code = 'NL'
622 AND prd.definition_name='Replacement Retropay';
623
624
625 Cursor c_retro_method(p_business_group_id in number)
626 is
627 Select hoi.org_information2
628 from hr_organization_information hoi
629 where hoi.org_information_context = 'NL_BG_INFO'
630 and hoi.organization_id = p_business_group_id;
631
632
633 l_rule_found varchar2(10):='N';
634 l_business_group_id number;
635 l_retro_method varchar2(10);
636 l_std_retro_definition_id number;
637 l_rep_retro_definition_id number;
638
639
640
641 BEGIN
642
643 OPEN c_leg_rule('RETRO_COMP_DFLT_OVERRIDE');
644 FETCH c_leg_rule INTO l_rule_found;
645
646 IF c_leg_rule%NOTFOUND then
647 l_rule_found:='N';
648 END if;
649
650 CLOSE c_leg_rule;
651
652 OPEN c_std_retro_definition;
653 FETCH c_std_retro_definition INTO l_std_retro_definition_id;
654 CLOSE c_std_retro_definition;
655
656 OPEN c_rep_retro_definition;
657 FETCH c_rep_retro_definition INTO l_rep_retro_definition_id;
658 CLOSE c_rep_retro_definition;
659
660
661 IF l_rule_found='Y' THEN
662
663 /* check if use intends to override replacement method at BG */
664
665 Open c_retro_method(p_business_group_id);
666 Fetch c_retro_method into l_retro_method;
667 Close c_retro_method;
668
669 IF l_retro_method='Y' THEN /* override replacement method, use standard retro definition*/
670 RETURN l_std_retro_definition_id;
671 else
672 RETURN l_rep_retro_definition_id;
673 END if;
674
675 else
676
677 RETURN l_std_retro_definition_id;
678
679
680 END if;
681
682
683
684 END get_default_retro_definition;
685
686 ---------------------------------------------------------------------------
687 -- Function : get_global_value
688 -- Function returns the global value for the given date earned
689 ---------------------------------------------------------------------------
690 Function get_global_value(l_date_earned date,l_global_name varchar2) return varchar2 is
691
692 cursor get_global_value(l_global_name varchar2
693
694
695
696 ,l_date_earned date) IS
697 select GLOBAL_VALUE
698 from ff_globals_f
699 where global_name = l_global_name
700 and LEGISLATION_CODE = 'NL'
701 and BUSINESS_GROUP_ID IS NULL
702 and l_date_earned between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
703
704 l_value ff_globals_f.global_value%TYPE;
705 Begin
706
707 OPEN get_global_value(l_global_name,l_date_earned);
708 FETCH get_global_value INTO l_value;
709 CLOSE get_global_value;
710 Return l_value;
711 EXCEPTION
712 when others then
713 hr_utility.trace('SQLERRM:'||substr(sqlerrm,1,200));
714 raise;
715 END get_global_value;
716
717
718 Function get_global_value(l_date_earned date,l_payroll_action_id number,l_global_name varchar2) return varchar2 is
719
720 cursor get_global_value(l_global_name varchar2
721 ,l_date date) IS
722 select GLOBAL_VALUE
723 from ff_globals_f
724 where global_name = l_global_name
725 and LEGISLATION_CODE = 'NL'
726 and BUSINESS_GROUP_ID IS NULL
727 and l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
728
729 l_value ff_globals_f.global_value%TYPE;
730
731 l_date date;
732 l_effective_date_ppa date;
733 l_date_earned_ppa date;
734
735 cursor csr_get_ppa_date is
736 select effective_date,date_earned
737 from pay_payroll_actions ppa
738 where ppa.payroll_action_id = l_payroll_action_id;
739 Begin
740
741 open csr_get_ppa_date;
742 fetch csr_get_ppa_date into l_effective_date_ppa,l_date_earned_ppa;
743 close csr_get_ppa_date;
744 if l_date_earned = l_date_earned_ppa then
745 l_date := l_effective_date_ppa;
746 else
747 l_date := l_date_earned;
748 end if;
749
750 OPEN get_global_value(l_global_name,l_date);
751 FETCH get_global_value INTO l_value;
752 CLOSE get_global_value;
753 Return l_value;
754 EXCEPTION
755 when others then
756 hr_utility.trace('SQLERRM:'||substr(sqlerrm,1,200));
757 raise;
758 END get_global_value;
759
760 --
761 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
762 -- +
763 -- create_scl_flex_dict +
764 -- +
765 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
766 /*
767 NAME
768 create_scl_flex_dict - create Soft Coded Legislation Keyflex DB items
769 --
770 DESCRIPTION
771 This procedure is the main entry point for creating Soft Coded
772 Legislation Keyflex database items. The parameter passed is is the
773 id flex number.
774 --
775 The database items created use the name as defined in the column
776 'segment_name' from the foundation table 'fnd_id_flex_segments'.
777 There are 3 levels of SCL keyflex:
778 --
779 ASSIGNMENT
780 PAYROLL
781 ORGANIZATION
782 --
783 The routine loops through and generates DB items for each level.
784 For a given SCL flexfield there could be several database items.
785 --
786 The routine has been enhanced to loop around to create dbitems
787 for each of the legislations which use the flex_num passed in.
788 NOTES
789 It is intended that this creation procedure be run from the
790 Standard Report Submission (SRS) form.
791 */
792 procedure create_scl_flex_dict
793 (
794 p_id_flex_num in number
795 ) is
796 --
797 -- declare cursor 0 for retrieving each legislation using this flex num
798 --
799 cursor c0 is
800 select legislation_code
801 from pay_legislation_rules
802 where rule_type = 'S'
803 and rule_mode = to_char (p_id_flex_num);
804 l_created_by number;
805 l_last_login number;
806 l_legislation_code pay_legislation_rules.legislation_code%type;
807 --
808 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
809 -- create_scl_flex +
810 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
811 /*
812 NAME
813 create_scl_flex - called from procedure create_scl_flex_dict
814 --
815 DESCRIPTION
816 This procedure is called from create_scl_flex_dict, and is responsible
817 for creating the user entity and database items for a particular SCL
818 flexfield.
819 */
820 procedure create_scl_flex
821 (
822 p_id_flex_num in number,
823 p_leg_code in varchar2,
824 p_route_name in varchar2,
825 p_entity_name in varchar2,
826 p_attribute_type in varchar2
827 ) is
828 --
829 -- declare cursor 1 for retrieving the segment names and target columns
830 --
831 cursor c1 is
832 select SEG.application_column_name c_def_text,
833 replace (ltrim(rtrim(upper(SEG.segment_name))),' ','_') c_db_name,
834 SEG.created_by c_created_by,
835 SEG.last_update_login c_last_login
836 from fnd_id_flex_segments SEG
837 , fnd_segment_attribute_values VALUE
838 where SEG.application_id = 800
839 and SEG.id_flex_code = 'SCL'
840 and SEG.id_flex_num = p_id_flex_num
841 and SEG.enabled_flag = 'Y'
842 and VALUE.application_column_name = SEG.application_column_name
843 and VALUE.id_flex_code = 'SCL'
844 and VALUE.id_flex_num = p_id_flex_num
845 and VALUE.segment_attribute_type = p_attribute_type
846 and VALUE.attribute_value = 'Y';
847 --
848 l_user_entity_id number;
849 l_db_item_exist boolean;
850 l_record_inserted boolean;
851 begin
852 l_db_item_exist := false;
853 for c1rec in c1 loop
854 if (l_db_item_exist = false) then -- first time through loop
855 --
856 -- create a user entity
857 --
858 l_created_by := c1rec.c_created_by;
859 l_last_login := c1rec.c_last_login;
860 --
861 hr_utility.trace ('creating SCL flex entity for '|| p_entity_name);
862 hrdyndbi.insert_user_entity (p_route_name,
863 p_entity_name,
864 'route for SCL level : '|| p_attribute_type,
865 'Y',
866 'KF',
867 p_id_flex_num,
868 null, -- null business group id
869 p_leg_code,
870 l_created_by,
871 l_last_login,
872 l_record_inserted);
873 --
874 -- only insert parameter values/database items if entity
875 -- was inserted
876 --
877 IF l_record_inserted THEN
878 --
879 -- insert the id flex num for the where clause filler
880 --
881 hrdyndbi.insert_parameter_value (p_id_flex_num, 1);
882 l_db_item_exist := true;
883 END IF;
884 end if;
885 --
886 -- now create the database item
887 --
888 IF p_entity_name = 'SCL_ASG_DE_NL' THEN
889 hrdyndbi.insert_database_item (substr(p_entity_name,1,8) || p_leg_code,
890 c1rec.c_db_name || '_DE' ,
891 'T', -- data type
892 'target.' || c1rec.c_def_text,
893 'Y', -- null allowed
894 'database item for : ' || p_entity_name);
895 ELSIF p_entity_name = 'SCL_ASG_DP_NL' THEN
896 hrdyndbi.insert_database_item (substr(p_entity_name,1,8) || p_leg_code,
897 c1rec.c_db_name || '_DP' ,
898 'T', -- data type
899 'target.' || c1rec.c_def_text,
900 'Y', -- null allowed
901 'database item for : ' || p_entity_name);
902 ELSE
903 hrdyndbi.insert_database_item (p_entity_name,
904 c1rec.c_db_name ,
905 'T', -- data type
906 'target.' || c1rec.c_def_text,
907 'Y', -- null allowed
908 'database item for : ' || p_entity_name);
909 END IF;
910 end loop; -- c1 loop
911 end create_scl_flex;
912 --
913 ---------------------- create_scl_flex_dict -------------------------
914 --
915 BEGIN
916 --
917 -- get each legislation code
918 --
919 hr_utility.set_location ('hrdyndbi.create_scl_flex_dict', 1);
920 --
921 for c0rec in c0 loop
922 --
923 l_legislation_code := c0rec.legislation_code;
924 --
925 -- delete any old SCL keyflex DB items that were created with the same id
926 --
927 hrdyndbi.delete_keyflex_dict (p_id_flex_num,
928 'SCL',
929 l_legislation_code,
930 null);
931
932 --
933 -- delete user entities (and dbitems) owned by a user entity
934 -- for an old value of the S leg rule
935 --
936 delete from ff_user_entities
937 where creator_type = 'KF'
938 and creator_id <> p_id_flex_num
939 and user_entity_name like 'SCL%'
940 and nvl (legislation_code, ' ') = nvl (l_legislation_code, ' ')
941 and business_group_id is null;
942
943 --
944 -- generate DB items for the 3 levels of SCL:
945 --
946
947 create_scl_flex (p_id_flex_num,
948 l_legislation_code,
949 'NL Soft Coded Key Flex Information',
950 'SCL_ASG_' || l_legislation_code,
951 'ASSIGNMENT');
952
953 create_scl_flex (p_id_flex_num,
954 l_legislation_code,
955 'SCL_ASS_FLEX_ROUTE',
956 'SCL_ASG_DE_' || l_legislation_code,
957 'ASSIGNMENT');
958 --
959 create_scl_flex (p_id_flex_num,
960 l_legislation_code,
961 'SCL_ASS_FLEX_DP',
962 'SCL_ASG_DP_' || l_legislation_code,
963 'ASSIGNMENT');
964 --
965 create_scl_flex (p_id_flex_num,
966 l_legislation_code,
967 'SCL_PAY_FLEX_ROUTE',
968 'SCL_PAY_' || l_legislation_code,
969 'PAYROLL');
970 --
971 create_scl_flex (p_id_flex_num,
972 l_legislation_code,
973 'SCL_ORG_FLEX_ROUTE',
974 'SCL_ORG_' || l_legislation_code,
975 'ORGANIZATION');
976 --
977 end loop; -- c0 loop
978 end create_scl_flex_dict;
979 --
980
981 PROCEDURE cache_formula(p_formula_name IN VARCHAR2
982 ,p_business_group_id IN NUMBER
983 ,p_effective_date IN DATE
984 ,p_formula_id IN OUT NOCOPY NUMBER
985 ,p_formula_exists IN OUT NOCOPY BOOLEAN
986 ,p_formula_cached IN OUT NOCOPY BOOLEAN
987 ) IS
988
989 --
990 CURSOR c_compiled_formula_exist IS
991 SELECT 'Y'
992 FROM ff_formulas_f ff
993 ,ff_compiled_info_f ffci
994 WHERE ff.formula_id = ffci.formula_id
995 AND ff.effective_start_date = ffci.effective_start_date
996 AND ff.effective_end_date = ffci.effective_end_date
997 AND ff.formula_id = p_formula_id
998 AND ff.business_group_id = p_business_group_id
999 AND p_effective_date BETWEEN ff.effective_start_date
1000 AND ff.effective_end_date;
1001 --
1002 CURSOR c_get_formula(p_formula_name ff_formulas_f.formula_name%TYPE
1003 ,p_effective_date DATE) IS
1004 SELECT ff.formula_id
1005 FROM ff_formulas_f ff
1006 WHERE ff.formula_name = p_formula_name
1007 AND ff.business_group_id = p_business_group_id
1008 AND p_effective_date BETWEEN ff.effective_start_date
1009 AND ff.effective_end_date;
1010 --
1011 l_test VARCHAR2(1);
1012
1013 BEGIN
1014 --
1015 IF p_formula_cached = FALSE THEN
1016 --
1017 --
1018 OPEN c_get_formula(p_formula_name,p_effective_date);
1019 FETCH c_get_formula INTO p_formula_id;
1020 IF c_get_formula%FOUND THEN
1021 OPEN c_compiled_formula_exist;
1022 FETCH c_compiled_formula_exist INTO l_test;
1023 IF c_compiled_formula_exist%NOTFOUND THEN
1024 p_formula_cached := FALSE;
1025 p_formula_exists := FALSE;
1026 --
1027 fnd_message.set_name('PAY','FFX03A_FORMULA_NOT_FOUND');
1028 fnd_message.set_token('1', p_formula_name);
1029 fnd_message.raise_error;
1030 ELSE
1031 p_formula_cached := TRUE;
1032 p_formula_exists := TRUE;
1033 END IF;
1034 ELSE
1035 p_formula_cached := FALSE;
1036
1037 p_formula_exists := FALSE;
1038 END IF;
1039 CLOSE c_get_formula;
1040 END IF;
1041 --
1042 END cache_formula;
1043
1044
1045 PROCEDURE run_formula(p_formula_id IN NUMBER
1046 ,p_effective_date IN DATE
1047 ,p_formula_name IN VARCHAR2
1048 ,p_inputs IN ff_exec.inputs_t
1049 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t) IS
1050
1051 l_inputs ff_exec.inputs_t;
1052 l_outputs ff_exec.outputs_t;
1053
1054 BEGIN
1055 hr_utility.set_location('--In Formula ',20);
1056 --
1057 -- Initialize the formula
1058 --
1059 ff_exec.init_formula(p_formula_id, p_effective_date , l_inputs, l_outputs);
1060 --
1061 -- Set up the input values
1062 --
1063 IF l_inputs.count > 0 and p_inputs.count > 0 THEN
1064 FOR i IN l_inputs.first..l_inputs.last LOOP
1065 FOR j IN p_inputs.first..p_inputs.last LOOP
1066 IF l_inputs(i).name = p_inputs(j).name THEN
1067 l_inputs(i).value := p_inputs(j).value;
1068 exit;
1069 END IF;
1070 END LOOP;
1071 END LOOP;
1072 END IF;
1073 --
1074 -- Run the formula
1075 --
1076 ff_exec.run_formula(l_inputs,l_outputs);
1077 --
1078 -- Populate the output table
1079 --
1080 IF l_outputs.count > 0 and p_inputs.count > 0 then
1081 FOR i IN l_outputs.first..l_outputs.last LOOP
1082 FOR j IN p_outputs.first..p_outputs.last LOOP
1083 IF l_outputs(i).name = p_outputs(j).name THEN
1084 p_outputs(j).value := l_outputs(i).value;
1085 exit;
1086 END IF;
1087 END LOOP;
1088 END LOOP;
1089 END IF;
1090 hr_utility.set_location('--Leaving Formula ',21);
1091 EXCEPTION
1092 WHEN hr_formula_error THEN
1093 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
1094 fnd_message.set_token('1', p_formula_name);
1095 fnd_message.raise_error;
1096 WHEN OTHERS THEN
1097 raise;
1098 --
1099 END run_formula;
1100
1101 FUNCTION get_element_type_id(p_element_name VARCHAR2,
1102 p_effective_date DATE)RETURN number
1103 IS
1104 l_element_type_id number;
1105 BEGIN
1106 select element_type_id into l_element_type_id
1107 from pay_element_types_f
1108 where element_name = p_element_name
1109 and p_effective_date between effective_start_date and effective_end_date
1110 and legislation_code='NL';
1111
1112 return l_element_type_id;
1113
1114 END get_element_type_id;
1115
1116 FUNCTION get_input_value_id(p_element_type_id NUMBER,
1117 p_input_value_name VARCHAR2,
1118 p_effective_date DATE)RETURN number
1119 IS
1120 l_input_value_id number;
1121 BEGIN
1122 select input_value_id into l_input_value_id
1123 from pay_input_values_f
1124 where element_type_id = p_element_type_id
1125 and name = p_input_value_name
1126 and p_effective_date between effective_start_date and effective_end_date
1127 and legislation_code='NL';
1128
1129 return l_input_value_id;
1130
1131 END get_input_value_id;
1132
1133 ------------------------------------------------------------------
1134 -- Function : get_employee_address
1135 -- This is a function that returns the employee address
1136 -- given the person_id , effective_date
1137 ------------------------------------------------------------------
1138 FUNCTION get_employee_address(p_person_id IN NUMBER
1139 ,p_effective_date IN DATE
1140 ,p_house_number IN OUT NOCOPY VARCHAR2
1141 ,p_house_no_add IN OUT NOCOPY VARCHAR2
1142 ,p_street_name IN OUT NOCOPY VARCHAR2
1143 ,p_line1 IN OUT NOCOPY VARCHAR2
1144 ,p_line2 IN OUT NOCOPY VARCHAR2
1145 ,p_line3 IN OUT NOCOPY VARCHAR2
1146 ,p_city IN OUT NOCOPY VARCHAR2
1147 ,p_country IN OUT NOCOPY VARCHAR2
1148 ,p_postal_code IN OUT NOCOPY VARCHAR2
1149 ,p_address_type IN VARCHAR2 DEFAULT NULL
1150 )
1151 RETURN NUMBER IS
1152
1153
1154 CURSOR emp_address
1155 IS
1156 SELECT pad.add_information13 house_number
1157 ,pad.add_information14 house_no_add
1158 ,pad.region_1 street_name
1159 ,pad.address_line1 address_line1
1160 ,pad.address_line2 address_line2
1161 ,pad.address_line3 address_line3
1162 ,get_postal_code(pad.postal_code) postcode --get the postal code and remove the space
1163 ,hr_general.decode_lookup('HR_NL_CITY',pad.town_or_city) city
1164 ,pad.country country
1165 FROM per_addresses pad
1166 WHERE pad.person_id=p_person_id
1167 AND p_effective_date between date_from AND NVL(date_to,hr_general.end_of_time)
1168 AND ((pad.primary_flag = 'Y' and p_address_type is null)
1169 or (p_address_type is not null and pad.address_type = p_address_type));
1170
1171 BEGIN
1172 OPEN emp_address;
1173 FETCH emp_address INTO p_house_number,p_house_no_add,p_street_name,p_line1,p_line2,p_line3,p_postal_code,p_city,p_country;
1174 CLOSE emp_address;
1175
1176 p_country := get_country_name(p_country);
1177
1178 RETURN 1;
1179 END get_employee_address;
1180
1181 ------------------------------------------------------------------
1182 -- Function : get_emp_address
1183 -- This function returns the employee address with postal code in the correct format
1184 -- given the person_id , effective_date
1185 ------------------------------------------------------------------
1186 FUNCTION get_emp_address(p_person_id IN NUMBER
1187 ,p_effective_date IN DATE
1188 ,p_house_number IN OUT NOCOPY VARCHAR2
1189 ,p_house_no_add IN OUT NOCOPY VARCHAR2
1190 ,p_street_name IN OUT NOCOPY VARCHAR2
1191 ,p_line1 IN OUT NOCOPY VARCHAR2
1192 ,p_line2 IN OUT NOCOPY VARCHAR2
1193 ,p_line3 IN OUT NOCOPY VARCHAR2
1194 ,p_city IN OUT NOCOPY VARCHAR2
1195 ,p_country IN OUT NOCOPY VARCHAR2
1196 ,p_postal_code IN OUT NOCOPY VARCHAR2
1197 ,p_address_type IN VARCHAR2 DEFAULT NULL
1198 )
1199 RETURN NUMBER IS
1200
1201
1202 CURSOR emp_address
1203 IS
1204 SELECT pad.add_information13 house_number
1205 ,pad.add_information14 house_no_add
1206 ,pad.region_1 street_name
1207 ,pad.address_line1 address_line1
1208 ,pad.address_line2 address_line2
1209 ,pad.address_line3 address_line3
1210 ,get_postal_code_new(pad.postal_code) postcode --introduce a space at the 5th position if there isn't one
1211 ,hr_general.decode_lookup('HR_NL_CITY',pad.town_or_city) city
1212 ,pad.country country
1213 FROM per_addresses pad
1214 WHERE pad.person_id=p_person_id
1215 AND p_effective_date between date_from AND NVL(date_to,hr_general.end_of_time)
1216 AND ((pad.primary_flag = 'Y' and p_address_type is null)
1217 or (p_address_type is not null and pad.address_type = p_address_type));
1218
1219 BEGIN
1220 OPEN emp_address;
1221 FETCH emp_address INTO p_house_number,p_house_no_add,p_street_name,p_line1,p_line2,p_line3,p_postal_code,p_city,p_country;
1222 CLOSE emp_address;
1223
1224 p_country := get_country_name(p_country);
1225
1226 RETURN 1;
1227 END get_emp_address;
1228
1229
1230 ------------------------------------------------------------------
1231 -- Function : get_organization_address
1232 -- This is a function that returns the organization address
1233 -- given the organization_id and business_group_id
1234 ------------------------------------------------------------------
1235 FUNCTION get_organization_address
1236 (p_org_id IN NUMBER,
1237 p_bg_id IN NUMBER,
1238 p_house_number IN OUT NOCOPY VARCHAR2,
1239 p_house_no_add IN OUT NOCOPY VARCHAR2,
1240 p_street_name IN OUT NOCOPY VARCHAR2,
1241 p_line1 IN OUT NOCOPY VARCHAR2,
1242 p_line2 IN OUT NOCOPY VARCHAR2,
1243 p_line3 IN OUT NOCOPY VARCHAR2,
1244 p_city IN OUT NOCOPY VARCHAR2,
1245 p_country IN OUT NOCOPY VARCHAR2,
1246 p_postal_code IN OUT NOCOPY VARCHAR2
1247 )
1248 RETURN NUMBER IS
1249
1250 CURSOR c_employer_address IS
1251 SELECT
1252 hlc.loc_information14 house_number
1253 ,hlc.loc_information15 house_number_add
1254 ,hlc.address_line_1 address_1
1255 ,hlc.address_line_2 address_2
1256 ,hlc.address_line_3 address_3
1257 ,hlc.region_1 street_name
1258 ,get_postal_code(hlc.postal_code) postcode --get the postal code and remove the space
1259 ,hr_general.decode_lookup('HR_NL_CITY',hlc.town_or_city) city
1260 ,hlc.country country
1261 FROM hr_locations hlc
1262 ,hr_organization_units hou
1263 WHERE hou.business_group_id = p_bg_id
1264 AND hou.organization_id = p_org_id
1265 AND hlc.location_id = hou.location_id;
1266
1267
1268
1269 BEGIN
1270
1271 OPEN c_employer_address;
1272 FETCH c_employer_address INTO p_house_number,p_house_no_add,p_line1,p_line2,p_line3,p_street_name,p_postal_code,p_city,p_country;
1273 CLOSE c_employer_address;
1274 p_country := get_country_name(p_country);
1275
1276 RETURN 1;
1277
1278 END get_organization_address;
1279
1280 ------------------------------------------------------------------
1281 -- Function : get_org_address
1282 -- This function returns the organization address with postal code in the correct format
1283 -- given the organization_id and business_group_id
1284 ------------------------------------------------------------------
1285 FUNCTION get_org_address
1286 (p_org_id IN NUMBER,
1287 p_bg_id IN NUMBER,
1288 p_house_number IN OUT NOCOPY VARCHAR2,
1289 p_house_no_add IN OUT NOCOPY VARCHAR2,
1290 p_street_name IN OUT NOCOPY VARCHAR2,
1291 p_line1 IN OUT NOCOPY VARCHAR2,
1292 p_line2 IN OUT NOCOPY VARCHAR2,
1293 p_line3 IN OUT NOCOPY VARCHAR2,
1294 p_city IN OUT NOCOPY VARCHAR2,
1295 p_country IN OUT NOCOPY VARCHAR2,
1296 p_postal_code IN OUT NOCOPY VARCHAR2
1297 )
1298 RETURN NUMBER IS
1299
1300 CURSOR c_employer_address IS
1301 SELECT
1302 hlc.loc_information14 house_number
1303 ,hlc.loc_information15 house_number_add
1304 ,hlc.address_line_1 address_1
1305 ,hlc.address_line_2 address_2
1306 ,hlc.address_line_3 address_3
1307 ,hlc.region_1 street_name
1308 ,get_postal_code_new(hlc.postal_code) postcode --introduce a space at the 5th position if there isn't one
1309 ,hr_general.decode_lookup('HR_NL_CITY',hlc.town_or_city) city
1310 ,hlc.country country
1311 FROM hr_locations hlc
1312 ,hr_organization_units hou
1313 WHERE hou.business_group_id = p_bg_id
1314 AND hou.organization_id = p_org_id
1315 AND hlc.location_id = hou.location_id;
1316
1317
1318
1319 BEGIN
1320
1321 OPEN c_employer_address;
1322 FETCH c_employer_address INTO p_house_number,p_house_no_add,p_line1,p_line2,p_line3,p_street_name,p_postal_code,p_city,p_country;
1323 CLOSE c_employer_address;
1324 p_country := get_country_name(p_country);
1325
1326 RETURN 1;
1327
1328 END get_org_address;
1329
1330 ------------------------------------------------------------------
1331 -- Function : get_country_name
1332 -- This is a function that returns the territory name
1333 -- given the territory code
1334 ------------------------------------------------------------------
1335 FUNCTION get_country_name(p_territory_code IN VARCHAR2)
1336 RETURN VARCHAR2 IS
1337
1338 l_territory_name fnd_territories_vl.territory_short_name%TYPE;
1339
1340 CURSOR c_territory_name IS
1341 SELECT ter.territory_short_name
1342 FROM fnd_territories_vl ter
1343 WHERE ter.territory_code = p_territory_code;
1344
1345 BEGIN
1346 OPEN c_territory_name;
1347 FETCH c_territory_name INTO l_territory_name;
1348 CLOSE c_territory_name;
1349
1350 RETURN l_territory_name;
1351 END get_country_name;
1352
1353 ------------------------------------------------------------------------------
1354 -- Function : get_retro_sum_pri_class
1355 -- Function returns the sum of retrospective values for a sub classification
1356 -- for a period.
1357 -------------------------------------------------------------------------------
1358 FUNCTION get_retro_sum_pri_class(p_retro_period IN DATE,
1359 p_pri_class_name IN VARCHAR2,
1360 p_assact_id IN NUMBER)
1361 RETURN NUMBER IS
1362 cursor csr_get_retro_bal_val IS
1363 select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
1364 from
1365 pay_payroll_actions ppa,
1366 pay_assignment_actions paa,
1367 pay_run_results prr,
1368 pay_run_result_values prv,
1369 pay_element_types_f pet,
1370 pay_element_classifications pec,
1371 pay_input_values_f piv
1372 where
1373 paa.payroll_action_id=ppa.payroll_action_id
1374 and paa.SOURCE_ACTION_ID is not null
1375 -- and ppa.payroll_action_id = paa.payroll_action_id
1376 and paa.action_status='C'
1377 and prr.ASSIGNMENT_ACTION_ID=paa.assignment_action_id
1378 and pay_nl_general.get_retro_period(prr.source_id,ppa.effective_date) = p_retro_period
1379 and prr.run_result_id=prv.run_result_id
1380 and pet.element_type_id = prr.element_type_id
1381 and pec.classification_id=pet.classification_id
1382 and pec.legislation_code = 'NL'
1383 and pet.element_type_id = piv.element_type_id
1384 and piv.name ='Pay Value'
1385 -- and piv.legislation_code='NL'
1386 and prv.input_value_id = piv.input_value_id
1387 and p_retro_period between piv.effective_start_date and piv.effective_end_date
1388 and pec.classification_name= p_pri_class_name
1389 -- and ppa.payroll_action_id= nvl(p_pact_id,ppa.payroll_action_id)
1390 and paa.assignment_action_id= p_assact_id;
1391 l_value number;
1392 begin
1393 OPEN csr_get_retro_bal_val;
1394 FETCH csr_get_retro_bal_val INTO l_value;
1395 CLOSE csr_get_retro_bal_val;
1396 RETURN l_value;
1397 END get_retro_sum_pri_class;
1398 -------------------------------------------------------------------------------
1399 -- Function : get_retro_sum_sec_class
1400 -- Function returns the sum of retrospective values for a sub classification
1401 -- for a period.
1402 -------------------------------------------------------------------------------
1403 FUNCTION get_retro_sum_sec_class(p_retro_period IN DATE,
1404 p_sec_class_name IN VARCHAR2,
1405 p_assact_id IN NUMBER)
1406 RETURN NUMBER IS
1407 CURSOR csr_retro_bal_val IS
1408 select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
1409 from
1410 pay_payroll_actions ppa,
1411 pay_assignment_actions paa,
1412 pay_run_results prr,
1413 pay_run_result_values prv,
1414 pay_element_types_f pet,
1415 pay_element_classifications pec,
1416 pay_sub_classification_rules_f pecs,
1417 pay_input_values_f piv
1418 where
1419 paa.payroll_action_id=ppa.payroll_action_id
1420 and paa.SOURCE_ACTION_ID is not null
1421 and ppa.payroll_action_id = paa.payroll_action_id
1422 and paa.action_status='C'
1423 and prr.ASSIGNMENT_ACTION_ID=paa.assignment_action_id
1424 and pay_nl_general.get_retro_period(prr.source_id,ppa.effective_date) = p_retro_period
1425 and prr.run_result_id=prv.run_result_id
1426 and pet.element_type_id = prr.element_type_id
1427 and pec.legislation_code = 'NL'
1428 and pecs.classification_id=pec.classification_id
1429 and pecs.element_type_id=pet.element_type_Id
1430 and pet.element_type_id = piv.element_type_id
1431 and piv.name ='Pay Value'
1432 -- and piv.legislation_code='NL'
1433 and prv.input_value_id = piv.input_value_id
1434 and p_retro_period between piv.effective_start_date and piv.effective_end_date
1435 and pec.classification_name=p_sec_class_name
1436 -- and ppa.payroll_action_id=nvl(p_pact_id,ppa.payroll_action_id)
1437 and paa.assignment_action_id= p_assact_id;
1438
1439 -- (select max(paa1.assignment_action_id) from
1440 -- pay_assignment_actions paa1
1441 -- where
1442 -- paa1.payroll_action_id=ppa.payroll_action_id
1443 -- and paa1.assignment_id=paa.assignment_id);
1444
1445 l_value number;
1446 begin
1447 OPEN csr_retro_bal_val;
1448 FETCH csr_retro_bal_val INTO l_value;
1449 CLOSE csr_retro_bal_val;
1450 RETURN l_value;
1451 END get_retro_sum_sec_class;
1452 -------------------------------------------------------------------------------
1453 -- Function : get_retro_sum_element
1454 -- Function returns the sum of retrospective values values for an element
1455 -------------------------------------------------------------------------------
1456 FUNCTION get_retro_sum_element(p_retro_period IN DATE,
1457 P_input_value_id IN NUMBER,
1458 p_element_type_id IN NUMBER,
1459 p_context IN VARCHAR2,
1460 p_end_of_year IN VARCHAR2,
1461 p_assact_id IN NUMBER)
1462 RETURN NUMBER IS
1463 CURSOR csr_retro_element_context IS
1464 select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
1465 from
1466 pay_payroll_actions ppa,
1467 pay_assignment_actions paa,
1468 pay_run_results prr,
1469 pay_run_result_values prv,
1470 pay_run_result_values prv1,
1471 pay_action_contexts pac,
1472 pay_input_values_f piv
1473 where
1474 paa.payroll_action_id=ppa.payroll_action_id
1475 and paa.SOURCE_ACTION_ID is not null
1476 and ppa.payroll_action_id = paa.payroll_action_id
1477 and paa.action_status='C'
1478 and prr.ASSIGNMENT_ACTION_ID=paa.assignment_action_id
1479 and paa.assignment_action_id = pac.assignment_action_id
1480 and pay_nl_general.get_retro_period(prr.source_id,ppa.effective_date) = p_retro_period
1481 and prr.run_result_id=prv.run_result_id
1482 and prv.input_value_id = P_input_value_id
1483 -- and ppa.payroll_action_id=nvl(p_pact_id,ppa.payroll_action_id)
1484 and prr.run_result_id=prv1.run_result_id
1485 AND prv1.input_value_id = piv.input_value_id
1486 and piv.name = 'SI Type'
1487 and pac.context_value = prv1.result_value
1488 and pac.context_value = p_context
1489 and ppa.effective_date
1490 between nvl(trunc(fnd_date.canonical_to_date(P_END_OF_YEAR),'Y'),ppa.effective_date)
1491 and nvl(fnd_date.canonical_to_date(P_END_OF_YEAR),ppa.effective_date)
1492 and ppa.effective_date between piv.effective_Start_Date and piv.effective_end_date
1493 and paa.assignment_action_id=p_assact_id;
1494
1495 CURSOR csr_retro_element IS
1496 select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
1497 from
1498 pay_payroll_actions ppa,
1499 pay_assignment_actions paa,
1500 pay_run_results prr,
1501 pay_run_result_values prv,
1502 pay_input_values_f piv
1503 Where
1504 paa.payroll_action_id=ppa.payroll_action_id
1505 and paa.SOURCE_ACTION_ID is not null
1506 and ppa.payroll_action_id = paa.payroll_action_id
1507 and paa.action_status='C'
1508 and prr.ASSIGNMENT_ACTION_ID=paa.assignment_action_id
1509 and pay_nl_general.get_retro_period(prr.source_id,ppa.effective_date) = p_retro_period
1510 and prr.run_result_id=prv.run_result_id
1511 and prr.element_type_id = p_element_type_id
1512 and piv.element_type_id = p_element_type_id
1513 and piv.name ='Pay Value'
1514 and prv.input_value_id = piv.input_value_id
1515 -- and ppa.payroll_action_id=nvl(p_pact_id,ppa.payroll_action_id)
1516 and ppa.effective_date
1517 between nvl(trunc(fnd_date.canonical_to_date(P_END_OF_YEAR),'Y'),ppa.effective_date)
1518 and nvl(fnd_date.canonical_to_date(P_END_OF_YEAR),ppa.effective_date)
1519 and paa.assignment_action_id=p_assact_id;
1520 l_value number;
1521 begin
1522 if p_context is not null then
1523 OPEN csr_retro_element_context;
1524 FETCH csr_retro_element_context INTO l_value;
1525 CLOSE csr_retro_element_context;
1526 else
1527 OPEN csr_retro_element;
1528 FETCH csr_retro_element INTO l_value;
1529 CLOSE csr_retro_element;
1530 end if;
1531 RETURN l_value;
1532 END get_retro_sum_element;
1533
1534 ------------------------------------------------------------------------------
1535 --Function :get_sum_element_pri_class
1536 --Function returns the sum of non retrospective values for an element
1537 --Classification
1538 -----------------------------------------------------------------------------
1539
1540 FUNCTION get_sum_element_pri_class(p_effective_date IN DATE,
1541 p_pri_class_name IN VARCHAR2,
1542 p_assact_id IN NUMBER)
1543 RETURN NUMBER IS
1544 cursor csr_get_elmt_bal_val IS
1545 select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
1546 from
1547 pay_payroll_actions ppa,
1548 pay_assignment_actions paa,
1549 pay_run_results prr,
1550 pay_run_result_values prv,
1551 pay_element_types_f pet,
1552 pay_element_classifications pec,
1553 pay_input_values_f piv
1554 where
1555 paa.payroll_action_id=ppa.payroll_action_id
1556 and paa.SOURCE_ACTION_ID is not null
1557 -- and ppa.payroll_action_id = paa.payroll_action_id
1558 and paa.action_status='C'
1559 and prr.ASSIGNMENT_ACTION_ID=paa.assignment_action_id
1560 and ppa.effective_date = p_effective_date
1561 -- and pay_nl_general.get_retro_period(prr.source_id,ppa.effective_date) = p_retro_period
1562 and prr.run_result_id=prv.run_result_id
1563 and pet.element_type_id = prr.element_type_id
1564 and pet.element_type_id = piv.element_type_id
1565 and piv.name ='Pay Value'
1566 -- and piv. legislation_code='NL'
1567 and prv.input_value_id = piv.input_value_id
1568 and pec.classification_id=pet.classification_id
1569 and pec.legislation_code = 'NL'
1570 and pec.classification_name= p_pri_class_name
1571 and p_effective_date between piv.effective_start_date and piv.effective_end_date
1572 -- and ppa.payroll_action_id= nvl(p_pact_id,ppa.payroll_action_id)
1573 and paa.assignment_action_id=p_assact_id;
1574
1575 l_value number;
1576 begin
1577 OPEN csr_get_elmt_bal_val;
1578 FETCH csr_get_elmt_bal_val INTO l_value;
1579 CLOSE csr_get_elmt_bal_val;
1580 RETURN l_value;
1581 END get_sum_element_pri_class;
1582
1583 -----------------------------------------------------------------------------
1584 --Function :format_number_field
1585 --Function returns a formatted string for a number with decimal
1586 -----------------------------------------------------------------------------
1587 function format_number_field(p_number number,
1588 p_mpy_factor number,
1589 p_field_length number)
1590 return varchar2 is
1591 l_format_value varchar2(30);
1592 begin
1593 l_format_value := lpad(p_number*p_mpy_factor,p_field_length,'0');
1594 return l_format_value;
1595 end format_number_field;
1596
1597 -----------------------------------------------------------------------------
1598 --Function : GET_PARAMETER
1599 -- GET_PARAMETER used in SQL to decode legislative parameters
1600 -----------------------------------------------------------------------------
1601 -----------------------------------------------------------------------------
1602 FUNCTION get_parameter(
1603 p_parameter_string IN VARCHAR2
1604 ,p_token IN VARCHAR2
1605 ,p_segment_number IN NUMBER DEFAULT NULL ) RETURN VARCHAR2
1606 IS
1607 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
1608 l_start_pos NUMBER;
1609 l_delimiter varchar2(1):=' ';
1610 l_proc VARCHAR2(60):= g_package||' get parameter ';
1611 BEGIN
1612 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
1613 IF l_start_pos = 0 THEN
1614 l_delimiter := '|';
1615 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
1616 end if;
1617 IF l_start_pos <> 0 THEN
1618 l_start_pos := l_start_pos + length(p_token||'=');
1619 l_parameter := substr(p_parameter_string,
1620 l_start_pos,
1621 instr(p_parameter_string||' ',
1622 ',',l_start_pos)
1623 - l_start_pos);
1624 IF p_segment_number IS NOT NULL THEN
1625 l_parameter := ':'||l_parameter||':';
1626 l_parameter := substr(l_parameter,
1627 instr(l_parameter,':',1,p_segment_number)+1,
1628 instr(l_parameter,':',1,p_segment_number+1) -1
1629 - instr(l_parameter,':',1,p_segment_number));
1630 END IF;
1631 END IF;
1632 RETURN l_parameter;
1633 END get_parameter;
1634 --
1635 -----------------------------------------------------------------------------
1636 -- Function :get_file_id
1637 -- Function returns file id on the effective date
1638 -------------------------------------------------------------------------------
1639 FUNCTION get_file_id(p_effective_date IN DATE) RETURN VARCHAR2 AS
1640 --
1641 CURSOR get_file_no_per_day IS
1642 SELECT count(*)
1643 FROM pay_payroll_actions ppa
1644 WHERE TRIM(PAY_NL_GENERAL.get_parameter(ppa.legislative_parameters
1645 ,'MAGTAPE_REPORT_ID'))= 'NL_PAYFILE'
1646 AND action_type = 'M'
1647 AND action_status = 'C'
1648 AND effective_date = p_effective_date;
1649 --
1650 l_file_id VARCHAR2(4);
1651 l_count NUMBER :=0;
1652 --
1653 BEGIN
1654 OPEN get_file_no_per_day;
1655 FETCH get_file_no_per_day INTO l_count;
1656 CLOSE get_file_no_per_day;
1657 RETURN (to_char(p_effective_date,'DD') ||
1658 lpad(to_char(mod(l_count,99)+1),2,'0')
1659 );
1660 END get_file_id;
1661
1662 -----------------------------------------------------------------------------
1663 -- Function :chk_multiple_assignments
1664 -- Function to determine the existance of multiple assignments for an employee
1665 -------------------------------------------------------------------------------
1666 FUNCTION chk_multiple_assignments(p_effective_date IN DATE
1667 ,p_person_id IN NUMBER) RETURN VARCHAR2 AS
1668 CURSOR get_multiple_assgts IS
1669 SELECT count(DISTINCT paf.assignment_id)
1670 FROM per_all_assignments_f paf
1671 ,per_assignment_status_types pas
1672 WHERE paf.assignment_type = 'E'
1673 AND paf.PERSON_ID = p_person_id
1674 AND p_effective_date between effective_start_date and effective_end_date
1675 AND paf.assignment_status_type_id = pas.assignment_status_type_id
1676 AND pas.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
1677 l_count NUMBER :=0;
1678 BEGIN
1679 OPEN get_multiple_assgts;
1680 FETCH get_multiple_assgts INTO l_count;
1681 CLOSE get_multiple_assgts;
1682 IF l_count > 1 THEN
1683 RETURN 'Y';
1684 ELSE
1685 RETURN 'N';
1686 END IF;
1687 END chk_multiple_assignments;
1688 -----------------------------------------------------------------------------
1689
1690 ------------------------------------------------------------------------------
1691 --Function :get_sum_element_sec_class
1692 --Function returns the sum of non retrospective values for an element
1693 --with the given Secondary Classification
1694 -----------------------------------------------------------------------------
1695 FUNCTION get_sum_element_sec_class(p_effective_date IN DATE,
1696 p_sec_class_name IN VARCHAR2,
1697 p_assact_id IN NUMBER)
1698 RETURN NUMBER AS
1699 cursor csr_get_elmt_bal_val is
1700 select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
1701 from
1702 pay_payroll_actions ppa,
1703 pay_assignment_actions paa,
1704 pay_run_results prr,
1705 pay_run_result_values prv,
1706 pay_element_types_f pet,
1707 pay_element_classifications pec,
1708 pay_sub_classification_rules_f pecs,
1709 pay_input_values_f piv
1710 where
1711 paa.payroll_action_id=ppa.payroll_action_id
1712 and paa.SOURCE_ACTION_ID is not null
1713 and paa.action_status='C'
1714 and prr.ASSIGNMENT_ACTION_ID=paa.assignment_action_id
1715 and ppa.effective_date = p_effective_date
1716 and prr.run_result_id=prv.run_result_id
1717 and pet.element_type_id = prr.element_type_id
1718 and pet.element_type_id = piv.element_type_id
1719 and piv.name ='Pay Value'
1720 and prv.input_value_id = piv.input_value_id
1721 and pec.legislation_code = 'NL'
1722 and pecs.classification_id=pec.classification_id
1723 and pecs.element_type_id=pet.element_type_Id
1724 and pec.classification_name= p_sec_class_name
1725 and p_effective_date between piv.effective_start_date and piv.effective_end_date
1726 and paa.assignment_action_id= p_assact_id;
1727
1728 l_value number;
1729
1730 Begin
1731 OPEN csr_get_elmt_bal_val;
1732 FETCH csr_get_elmt_bal_val INTO l_value;
1733 CLOSE csr_get_elmt_bal_val;
1734 RETURN l_value;
1735
1736 end get_sum_element_sec_class;
1737
1738 -----------------------------------------------------------------------------
1739 -- Function :get_retro_status
1740 -- Function to determine whether replacement retropay method is running
1741 -------------------------------------------------------------------------------
1742 FUNCTION get_retro_status(p_date_earned date,p_payroll_action_id number) return varchar2 is
1743
1744 l_retro_status varchar(10) := 'N';
1745 l_date_earned_ppa date;
1746
1747 cursor csr_get_ppa_date is
1748 select date_earned
1749 from pay_payroll_actions ppa
1750 where ppa.payroll_action_id = p_payroll_action_id;
1751
1752 Begin
1753
1754 open csr_get_ppa_date;
1755 fetch csr_get_ppa_date into l_date_earned_ppa;
1756 close csr_get_ppa_date;
1757
1758 if p_date_earned = l_date_earned_ppa then
1759 l_retro_status := 'N';
1760 else
1761 l_retro_status := 'Y';
1762 end if;
1763
1764 Return l_retro_status;
1765
1766 EXCEPTION
1767 when others then
1768 hr_utility.trace('SQLERRM:'||substr(sqlerrm,1,200));
1769 raise;
1770 END get_retro_status;
1771
1772 -----------------------------------------------------------------------------
1773 -- Function :get_num_payroll_periods
1774 -- Function to get number of payroll periods in a year
1775 -------------------------------------------------------------------------------
1776 FUNCTION get_num_payroll_periods(p_payroll_action_id IN NUMBER)
1777 RETURN NUMBER is
1778
1779 cursor csr_get_num_periods(c_payroll_action_id NUMBER) is
1780 select max(TPERIOD.period_num) from
1781 pay_payroll_actions PACTION
1782 ,per_time_periods TPERIOD
1783 where PACTION.payroll_action_id = c_payroll_action_id
1784 and TPERIOD.payroll_id = PACTION.payroll_id
1785 and to_char(PACTION.date_earned,'YYYY') = to_char(TPERIOD.regular_payment_date,'YYYY');
1786
1787 pay_periods_per_year number;
1788
1789 BEGIN
1790
1791 open csr_get_num_periods(p_payroll_action_id);
1792 fetch csr_get_num_periods into pay_periods_per_year;
1793 close csr_get_num_periods;
1794
1795 RETURN pay_periods_per_year;
1796
1797 END get_num_payroll_periods;
1798
1799 -----------------------------------------------------------------------------
1800 -- Function :check_de_dp_dimension
1801 -- Function to check whether Date paid or balance date dimenions to be used.
1802 -------------------------------------------------------------------------------
1803 FUNCTION check_de_dp_dimension(p_pay_act_id NUMBER
1804 ,p_ass_id NUMBER
1805 ,p_ass_act_id NUMBER) RETURN VARCHAR2 IS
1806 --
1807 /*
1808 CURSOR csr_prev_payroll_exists(c_assignment_action_id NUMBER
1809 ,c_assignment_id NUMBER
1810 ,c_start_date DATE
1811 ,c_end_date DATE)IS
1812 SELECT 'DP' FROM dual
1813 WHERE EXISTS (SELECT 1
1814 FROM pay_assignment_actions paa1
1815 ,pay_assignment_actions paa2
1816 ,pay_payroll_actions ppa
1817 WHERE paa1.assignment_action_id = c_assignment_action_id
1818 AND paa2.assignment_id = c_assignment_id
1819 AND paa1.tax_unit_id = paa2.tax_unit_id
1820 AND ppa.action_type IN ('Q','R')
1821 AND ppa.action_status = 'C'
1822 AND ppa.payroll_action_id = paa2.payroll_action_id
1823 AND ppa.effective_date < c_start_date);
1824
1825 --
1826 CURSOR csr_diff_de_dp(c_payroll_action_id NUMBER
1827 ,c_assignment_id NUMBER
1828 ,c_start_date DATE
1829 ,c_end_date DATE)IS
1830 SELECT 'DE' FROM dual
1831 WHERE EXISTS (SELECT 1
1832 FROM pay_assignment_actions paa
1833 ,pay_payroll_actions ppa
1834 WHERE paa.assignment_id = c_assignment_id
1835 AND ppa.payroll_action_id = paa.payroll_action_id
1836 AND ppa.action_type IN ('Q','R')
1837 AND ppa.action_status in ('C','P')
1838 AND ppa.date_earned NOT BETWEEN c_start_date AND c_end_date
1839 AND ppa.effective_date BETWEEN c_start_date AND c_end_date);
1840 */
1841 --
1842 CURSOR c1 (c_payroll_action_id NUMBER)IS
1843 SELECT ptp.start_date
1844 ,ptp.end_date
1845 ,ppa.action_type
1846 ,ppa.action_status
1847 ,ppa.date_earned
1848 ,ppa.business_group_id
1849 FROM pay_payroll_actions ppa
1850 ,per_time_periods ptp
1851 WHERE ppa.payroll_action_id = c_payroll_action_id
1852 AND ptp.time_period_id = ppa.time_period_id;
1853 --
1854 CURSOR csr_get_all_ass(c_assignment_id NUMBER
1855 ,c_start_date DATE
1856 ,c_end_date DATE) IS
1857 SELECT distinct pog2.parent_object_group_id parent_id
1858 ,pog2.source_id assignment_id
1859 FROM pay_object_groups pog1
1860 ,pay_object_groups pog2
1861 WHERE pog1.source_id = c_assignment_id
1862 AND pog1.source_type = pog2.source_type
1863 AND pog1.source_type = 'PAF'
1864 AND pog1.parent_object_group_id = pog2.parent_object_group_id
1865 AND (pog1.start_date <= c_end_date AND pog1.end_date >= c_start_date)
1866 AND (pog2.start_date <= c_end_date AND pog2.end_date >= c_start_date);
1867 --
1868 CURSOR csr_get_late_starter_flag(c_business_group_id NUMBER) IS
1869 SELECT org_information7
1870 FROM hr_organization_information
1871 WHERE organization_id = c_business_group_id
1872 AND org_information_context = 'NL_BG_INFO';
1873 --
1874 CURSOR csr_chk_element_entry(c_parent_id NUMBER
1875 ,c_eff_date DATE) IS
1876 SELECT 'Y'
1877 FROM dual
1878 WHERE exists (SELECT 1
1879 FROM pay_object_groups pog
1880 ,pay_element_entries_f peef
1881 ,pay_element_types_f petf
1882 WHERE pog.parent_object_group_id = c_parent_id
1883 AND peef.assignment_id = pog.source_id
1884 AND peef.element_type_id = petf.element_type_id
1885 AND petf.element_name = 'Late Hire Indicator'
1886 AND c_eff_date BETWEEN peef.effective_start_date AND peef.effective_end_date
1887 AND c_eff_date BETWEEN petf.effective_start_date AND petf.effective_end_date);
1888 --
1889 l_result VARCHAR2(5);
1890 l_start_date DATE;
1891 l_end_date DATE;
1892 l_type VARCHAR2(10);
1893 l_status VARCHAR2(10);
1894 l_date_earned DATE;
1895 l_parent_id NUMBER;
1896 l_bg_id NUMBER;
1897 l_late_hire_indicator VARCHAR2(10);
1898 l_entry_exist VARCHAR2(1);
1899 --
1900 BEGIN
1901 --
1902 --HR_UTILITY.TRACE_ON(NULL,'GR');
1903 HR_UTILITY.TRACE('~~ Assignment_id :'||p_ass_id);
1904 IF g_late_hire_indicator = 'N' THEN
1905 HR_UTILITY.TRACE('~~ 1 Result : DP');
1906 RETURN 'DP';
1907 END IF;
1908 --
1909 OPEN C1(p_pay_act_id);
1910 FETCH C1 INTO l_start_date,l_end_date,l_type,l_status,l_date_earned,l_bg_id;
1911 CLOSE C1;
1912 --
1913 IF NVL(g_late_hire_indicator,'X') = 'X' THEN
1914 OPEN csr_get_late_starter_flag(l_bg_id);
1915 FETCH csr_get_late_starter_flag INTO l_late_hire_indicator;
1916 CLOSE csr_get_late_starter_flag;
1917 --
1918 g_late_hire_indicator := NVL(l_late_hire_indicator,'Y');
1919 IF g_late_hire_indicator = 'N' THEN
1920 HR_UTILITY.TRACE('~~ 2 Result : DP');
1921 RETURN 'DP';
1922 END IF;
1923 END IF;
1924 --
1925 l_result := 'DP';
1926 --
1927 l_entry_exist := NULL;
1928 --
1929 FOR i in csr_get_all_ass(p_ass_id,l_start_date,l_end_date) LOOP
1930 IF NVL(g_parent_id,-1) = i.parent_id
1931 AND g_result IS NOT NULL
1932 AND NVL(g_payroll_action_id,-1) = p_pay_act_id THEN
1933 return g_result;
1934 END IF;
1935 l_parent_id := i.parent_id;
1936 --
1937 IF l_entry_exist IS NULL THEN
1938 OPEN csr_chk_element_entry(i.parent_id,l_date_earned);
1939 FETCH csr_chk_element_entry INTO l_entry_exist;
1940 CLOSE csr_chk_element_entry;
1941 --
1942 l_entry_exist := NVL(l_entry_exist,'N');
1943 --
1944 IF l_entry_exist = 'Y' THEN
1945 g_result := 'DE';
1946 g_payroll_action_id := p_pay_act_id;
1947 g_parent_id := l_parent_id;
1948 HR_UTILITY.TRACE('~~ 3 Result : '||g_result);
1949 RETURN g_result;
1950 END IF;
1951 END IF;
1952 --
1953 /*
1954 l_result := NULL;
1955 --
1956 OPEN csr_prev_payroll_exists(p_ass_act_id,i.assignment_id,l_start_date,l_end_date);
1957 FETCH csr_prev_payroll_exists INTO l_result;
1958 CLOSE csr_prev_payroll_exists;
1959 --
1960 --
1961 IF l_result IS NULL THEN
1962 OPEN csr_diff_de_dp(p_pay_act_id,i.assignment_id,l_start_date,l_end_date);
1963 FETCH csr_diff_de_dp INTO l_result;
1964 IF csr_diff_de_dp%FOUND THEN
1965 g_result := 'DE';
1966 g_payroll_action_id := p_pay_act_id;
1967 g_parent_id := l_parent_id;
1968 CLOSE csr_diff_de_dp;
1969 HR_UTILITY.TRACE('~~ 4 Result : DE');
1970 RETURN 'DE';
1971 END IF;
1972 CLOSE csr_diff_de_dp;
1973 END IF;
1974 */
1975 --
1976 END LOOP;
1977 --
1978 g_payroll_action_id := p_pay_act_id;
1979 g_parent_id := l_parent_id;
1980 g_result := 'DP';
1981 HR_UTILITY.TRACE('~~ 5 Result : '||g_result);
1982 RETURN l_result;
1983 --
1984 END check_de_dp_dimension;
1985 --
1986 -----------------------------------------------------------------------------
1987 -- Function :check_de_dp_dimension_old
1988 -------------------------------------------------------------------------------
1989
1990 FUNCTION check_de_dp_dimension_old(p_pay_act_id NUMBER
1991 ,p_ass_id NUMBER
1992 ,p_ass_act_id NUMBER) RETURN VARCHAR2 IS
1993 --
1994 CURSOR csr_prev_payroll_exists(c_assignment_action_id NUMBER
1995 ,c_assignment_id NUMBER
1996 ,c_start_date DATE
1997 ,c_end_date DATE)IS
1998 SELECT 'DP' FROM dual
1999 WHERE EXISTS (SELECT 1
2000 FROM pay_assignment_actions paa1
2001 ,pay_assignment_actions paa2
2002 ,pay_payroll_actions ppa
2003 WHERE paa1.assignment_action_id = c_assignment_action_id
2004 AND paa2.assignment_id = c_assignment_id
2005 AND paa1.tax_unit_id = paa2.tax_unit_id
2006 AND ppa.action_type IN ('Q','R')
2007 AND ppa.action_status = 'C'
2008 AND ppa.payroll_action_id = paa2.payroll_action_id
2009 AND ppa.effective_date < c_start_date);
2010
2011 --
2012 CURSOR csr_diff_de_dp(c_payroll_action_id NUMBER
2013 ,c_assignment_id NUMBER
2014 ,c_start_date DATE
2015 ,c_end_date DATE)IS
2016 SELECT 'DE' FROM dual
2017 WHERE EXISTS (SELECT 1
2018 FROM pay_assignment_actions paa
2019 ,pay_payroll_actions ppa
2020 WHERE paa.assignment_id = c_assignment_id
2021 AND ppa.payroll_action_id = paa.payroll_action_id
2022 AND ppa.action_type IN ('Q','R')
2023 AND ppa.action_status in ('C','P')
2024 AND ppa.date_earned NOT BETWEEN c_start_date AND c_end_date
2025 AND ppa.effective_date BETWEEN c_start_date AND c_end_date);
2026 --
2027 CURSOR c1 (c_payroll_action_id NUMBER)IS
2028 SELECT ptp.start_date
2029 ,ptp.end_date
2030 ,ppa.action_type
2031 ,ppa.action_status
2032 ,ppa.date_earned
2033 ,ppa.business_group_id
2034 FROM pay_payroll_actions ppa
2035 ,per_time_periods ptp
2036 WHERE ppa.payroll_action_id = c_payroll_action_id
2037 AND ptp.time_period_id = ppa.time_period_id;
2038 --
2039 CURSOR csr_get_all_ass(c_assignment_id NUMBER
2040 ,c_start_date DATE
2041 ,c_end_date DATE) IS
2042 SELECT distinct pog2.parent_object_group_id parent_id
2043 ,pog2.source_id assignment_id
2044 FROM pay_object_groups pog1
2045 ,pay_object_groups pog2
2046 WHERE pog1.source_id = c_assignment_id
2047 AND pog1.source_type = pog2.source_type
2048 AND pog1.source_type = 'PAF'
2049 AND pog1.parent_object_group_id = pog2.parent_object_group_id
2050 AND (pog1.start_date <= c_end_date AND pog1.end_date >= c_start_date)
2051 AND (pog2.start_date <= c_end_date AND pog2.end_date >= c_start_date);
2052 --
2053 CURSOR csr_get_late_starter_flag(c_business_group_id NUMBER) IS
2054 SELECT org_information7
2055 FROM hr_organization_information
2056 WHERE organization_id = c_business_group_id
2057 AND org_information_context = 'NL_BG_INFO';
2058 --
2059 CURSOR csr_chk_element_entry(c_parent_id NUMBER
2060 ,c_eff_date DATE) IS
2061 SELECT 'Y'
2062 FROM dual
2063 WHERE exists (SELECT 1
2064 FROM pay_object_groups pog
2065 ,pay_element_entries_f peef
2066 ,pay_element_types_f petf
2067 WHERE pog.parent_object_group_id = c_parent_id
2068 AND peef.assignment_id = pog.source_id
2069 AND peef.element_type_id = petf.element_type_id
2070 AND petf.element_name = 'Late Hire Indicator'
2071 AND c_eff_date BETWEEN peef.effective_start_date AND peef.effective_end_date
2072 AND c_eff_date BETWEEN petf.effective_start_date AND petf.effective_end_date);
2073 --
2074 l_result VARCHAR2(5);
2075 l_start_date DATE;
2076 l_end_date DATE;
2077 l_type VARCHAR2(10);
2078 l_status VARCHAR2(10);
2079 l_date_earned DATE;
2080 l_parent_id NUMBER;
2081 l_bg_id NUMBER;
2082 l_late_hire_indicator VARCHAR2(10);
2083 l_entry_exist VARCHAR2(1);
2084 --
2085 BEGIN
2086 --
2087 --HR_UTILITY.TRACE_ON(NULL,'GR');
2088 HR_UTILITY.TRACE('~~~ Assignment_id :'||p_ass_id);
2089 IF g_late_hire_indicator = 'N' THEN
2090 HR_UTILITY.TRACE('~~~ 1 Result : DP');
2091 RETURN 'DP';
2092 END IF;
2093 --
2094 OPEN C1(p_pay_act_id);
2095 FETCH C1 INTO l_start_date,l_end_date,l_type,l_status,l_date_earned,l_bg_id;
2096 CLOSE C1;
2097 --
2098 IF NVL(g_late_hire_indicator,'X') = 'X' THEN
2099 OPEN csr_get_late_starter_flag(l_bg_id);
2100 FETCH csr_get_late_starter_flag INTO l_late_hire_indicator;
2101 CLOSE csr_get_late_starter_flag;
2102 --
2103 g_late_hire_indicator := NVL(l_late_hire_indicator,'Y');
2104 IF g_late_hire_indicator = 'N' THEN
2105 HR_UTILITY.TRACE('~~~ 2 Result : DP');
2106 RETURN 'DP';
2107 END IF;
2108 END IF;
2109 --
2110 l_result := 'DP';
2111 --
2112 l_entry_exist := NULL;
2113 --
2114 FOR i in csr_get_all_ass(p_ass_id,l_start_date,l_end_date) LOOP
2115 IF NVL(g_parent_id,-1) = i.parent_id
2116 AND g_result IS NOT NULL
2117 AND NVL(g_payroll_action_id,-1) = p_pay_act_id THEN
2118 return g_result;
2119 END IF;
2120 l_parent_id := i.parent_id;
2121 --
2122 IF l_entry_exist IS NULL THEN
2123 OPEN csr_chk_element_entry(i.parent_id,l_date_earned);
2124 FETCH csr_chk_element_entry INTO l_entry_exist;
2125 CLOSE csr_chk_element_entry;
2126 --
2127 l_entry_exist := NVL(l_entry_exist,'N');
2128 --
2129 IF l_entry_exist = 'Y' THEN
2130 g_result := 'DE';
2131 g_payroll_action_id := p_pay_act_id;
2132 g_parent_id := l_parent_id;
2133 HR_UTILITY.TRACE('~~~ 3 Result : '||g_result);
2134 RETURN g_result;
2135 END IF;
2136 END IF;
2137 --
2138 l_result := NULL;
2139 --
2140 OPEN csr_prev_payroll_exists(p_ass_act_id,i.assignment_id,l_start_date,l_end_date);
2141 FETCH csr_prev_payroll_exists INTO l_result;
2142 CLOSE csr_prev_payroll_exists;
2143 --
2144 --
2145 IF l_result IS NULL THEN
2146 OPEN csr_diff_de_dp(p_pay_act_id,i.assignment_id,l_start_date,l_end_date);
2147 FETCH csr_diff_de_dp INTO l_result;
2148 IF csr_diff_de_dp%FOUND THEN
2149 g_result := 'DE';
2150 g_payroll_action_id := p_pay_act_id;
2151 g_parent_id := l_parent_id;
2152 CLOSE csr_diff_de_dp;
2153 HR_UTILITY.TRACE('~~~ 4 Result : DE');
2154 RETURN 'DE';
2155 END IF;
2156 CLOSE csr_diff_de_dp;
2157 END IF;
2158 --
2159 END LOOP;
2160 --
2161 g_payroll_action_id := p_pay_act_id;
2162 g_parent_id := l_parent_id;
2163 g_result := 'DP';
2164 HR_UTILITY.TRACE('~~~ 5 Result : '||g_result);
2165 RETURN g_result;
2166 --
2167 END check_de_dp_dimension_old;
2168
2169 --
2170 -----------------------------------------------------------------------------
2171 -- Function :check_de_dp_dimension_qtd
2172 -- Function to check whether Date paid or balance date dimenions is to be used
2173 -- for QTD dimensions .
2174 -------------------------------------------------------------------------------
2175 FUNCTION check_de_dp_dimension_qtd(p_pay_act_id NUMBER
2176 ,p_ass_id NUMBER
2177 ,p_ass_act_id NUMBER
2178 ,p_type VARCHAR2) RETURN VARCHAR2 IS
2179 --
2180 /*
2181 CURSOR csr_prev_qtd_payroll_exists(c_assignment_action_id NUMBER
2182 ,c_start_date DATE
2183 ,c_end_date DATE)IS
2184 SELECT 'DP' FROM dual
2185 WHERE EXISTS (SELECT 1
2186 FROM pay_assignment_actions paa1
2187 ,pay_assignment_actions paa2
2188 ,pay_payroll_actions ppa
2189 WHERE paa1.assignment_action_id = c_assignment_action_id
2190 AND paa2.assignment_id = paa1.assignment_id
2191 AND paa1.tax_unit_id = paa2.tax_unit_id
2192 AND ppa.action_type IN ('Q','R')
2193 AND ppa.action_status = 'C'
2194 AND ppa.payroll_action_id = paa2.payroll_action_id
2195 AND ppa.effective_date < TRUNC(c_start_date,'Q'));
2196 --
2197 CURSOR csr_prev_lqtd_payroll_exists(c_assignment_action_id NUMBER
2198 ,c_start_date DATE
2199 ,c_end_date DATE)IS
2200 SELECT 'DP' FROM dual
2201 WHERE EXISTS (SELECT 1
2202 FROM pay_assignment_actions paa1
2203 ,pay_assignment_actions paa2
2204 ,pay_payroll_actions ppa
2205 WHERE paa1.assignment_action_id = c_assignment_action_id
2206 AND paa2.assignment_id = paa1.assignment_id
2207 AND paa1.tax_unit_id = paa2.tax_unit_id
2208 AND ppa.action_type IN ('Q','R')
2209 AND ppa.action_status = 'C'
2210 AND ppa.payroll_action_id = paa2.payroll_action_id
2211 AND ppa.effective_date < c_start_date
2212 AND DECODE(trunc((to_number(to_char(ppa.effective_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(ppa.effective_date,'IW'))-1)/12)) <>
2213 DECODE(trunc((to_number(to_char(c_start_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(c_start_date,'IW'))-1)/12)));
2214 --
2215 CURSOR csr_diff_de_dp_qtd(c_payroll_action_id NUMBER
2216 ,c_assignment_action_id NUMBER
2217 ,c_start_date DATE
2218 ,c_end_date DATE)IS
2219 SELECT 'DE' FROM dual
2220 WHERE EXISTS (SELECT 1
2221 FROM pay_assignment_actions paa1
2222 ,pay_assignment_actions paa2
2223 ,pay_payroll_actions ppa1
2224 ,pay_payroll_actions ppa2
2225 WHERE ppa1.payroll_action_id = c_payroll_action_id
2226 AND ppa1.payroll_action_id = paa1.payroll_action_id
2227 AND paa1.assignment_action_id = c_assignment_action_id
2228 AND paa1.assignment_id = paa2.assignment_id
2229 AND paa1.tax_unit_id = paa2.tax_unit_id
2230 AND ppa2.payroll_action_id = paa2.payroll_action_id
2231 AND ppa2.action_type IN ('Q','R')
2232 AND ppa2.action_status in ('C','P')
2233 AND TRUNC(ppa2.date_earned,'Q') <> TRUNC(c_start_date,'Q')
2234 AND TRUNC(ppa2.effective_date,'Q') = TRUNC(c_start_date,'Q'));
2235 --
2236 CURSOR csr_diff_de_dp_lqtd(c_payroll_action_id NUMBER
2237 ,c_assignment_action_id NUMBER
2238 ,c_start_date DATE
2239 ,c_end_date DATE)IS
2240 SELECT 'DE' FROM dual
2241 WHERE EXISTS (SELECT 1
2242 FROM pay_assignment_actions paa1
2243 ,pay_assignment_actions paa2
2244 ,pay_payroll_actions ppa1
2245 ,pay_payroll_actions ppa2
2246 WHERE ppa1.payroll_action_id = c_payroll_action_id
2247 AND ppa1.payroll_action_id = paa1.payroll_action_id
2248 AND paa1.assignment_action_id = c_assignment_action_id
2249 AND paa1.assignment_id = paa2.assignment_id
2250 AND paa1.tax_unit_id = paa2.tax_unit_id
2251 AND ppa2.payroll_action_id = paa2.payroll_action_id
2252 AND ppa2.action_type IN ('Q','R')
2253 AND ppa2.action_status in ('C','P')
2254 AND DECODE(trunc((to_number(to_char(ppa2.date_earned,'IW'))-1)/12),4,3,trunc((to_number(to_char(ppa2.date_earned,'IW'))-1)/12)) <>
2255 DECODE(trunc((to_number(to_char(c_start_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(c_start_date,'IW'))-1)/12))
2256 AND DECODE(trunc((to_number(to_char(ppa2.effective_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(ppa2.effective_date,'IW'))-1)/12)) =
2257 DECODE(trunc((to_number(to_char(c_start_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(c_start_date,'IW'))-1)/12)) );
2258 */
2259 --
2260 CURSOR c1 (c_payroll_action_id NUMBER)IS
2261 SELECT ptp.start_date
2262 ,ptp.end_date
2263 ,ppa.date_earned
2264 ,business_group_id
2265 FROM pay_payroll_actions ppa
2266 ,per_time_periods ptp
2267 WHERE ppa.payroll_action_id = c_payroll_action_id
2268 AND ptp.time_period_id = ppa.time_period_id;
2269 --
2270 CURSOR csr_chk_pog_exist(c_assignment_id NUMBER
2271 ,c_start_date DATE
2272 ,c_end_date DATE) IS
2273 SELECT '1' FROM DUAL
2274 WHERE EXISTS (SELECT 1
2275 FROM pay_object_groups pog1
2276 ,pay_object_groups pog2
2277 WHERE pog1.source_id = c_assignment_id
2278 AND pog1.source_type = pog2.source_type
2279 AND pog1.source_type = 'PAF'
2280 AND pog1.parent_object_group_id = pog2.parent_object_group_id
2281 AND (pog1.start_date <= c_end_date AND pog1.end_date >= c_start_date)
2282 AND (pog2.start_date <= c_end_date AND pog2.end_date >= c_start_date));
2283 --
2284 CURSOR csr_get_late_starter_flag(c_business_group_id NUMBER) IS
2285 SELECT org_information7
2286 FROM hr_organization_information
2287 WHERE organization_id = c_business_group_id
2288 AND org_information_context = 'NL_BG_INFO';
2289 --
2290 CURSOR csr_chk_element_entry(c_ass_id NUMBER
2291 ,c_eff_date DATE) IS
2292 SELECT 'Y'
2293 FROM dual
2294 WHERE exists (SELECT 1
2295 FROM pay_element_entries_f peef
2296 ,pay_element_types_f petf
2297 WHERE peef.assignment_id = c_ass_id
2298 AND peef.element_type_id = petf.element_type_id
2299 AND petf.element_name = 'Late Hire Indicator'
2300 AND c_eff_date BETWEEN peef.effective_start_date AND peef.effective_end_date
2301 AND c_eff_date BETWEEN petf.effective_start_date AND petf.effective_end_date);
2302 --
2303 l_qtd_result VARCHAR2(5);
2304 l_start_date DATE;
2305 l_end_date DATE;
2306 l_parent_id NUMBER;
2307 l_chk VARCHAR2(1);
2308 l_date_earned DATE;
2309 l_bg_id NUMBER;
2310 l_late_hire_indicator VARCHAR2(10);
2311 l_entry_exist VARCHAR2(1);
2312 --
2313 BEGIN
2314 --
2315 HR_UTILITY.TRACE('~~ Assignment_id :'||p_ass_id);
2316 IF g_late_hire_indicator = 'N' THEN
2317 HR_UTILITY.TRACE('~~ 1 QResult : DP');
2318 RETURN 'DP';
2319 END IF;
2320 --
2321 OPEN C1(p_pay_act_id);
2322 FETCH C1 INTO l_start_date,l_end_date,l_date_earned,l_bg_id;
2323 CLOSE C1;
2324 --
2325 IF NVL(g_late_hire_indicator,'X') = 'X' THEN
2326 OPEN csr_get_late_starter_flag(l_bg_id);
2327 FETCH csr_get_late_starter_flag INTO l_late_hire_indicator;
2328 CLOSE csr_get_late_starter_flag;
2329 --
2330 g_late_hire_indicator := NVL(l_late_hire_indicator,'Y');
2331 IF g_late_hire_indicator = 'N' THEN
2332 HR_UTILITY.TRACE('~~ 2 QResult : DP');
2333 RETURN 'DP';
2334 END IF;
2335 END IF;
2336 --
2337 OPEN csr_chk_pog_exist(p_ass_id,l_start_date,l_end_date); -- not req after BG check
2338 FETCH csr_chk_pog_exist INTO l_chk;
2339 IF csr_chk_pog_exist%NOTFOUND THEN
2340 g_qtd_result := 'DP';
2341 CLOSE csr_chk_pog_exist;
2342 RETURN g_qtd_result;
2343 END IF;
2344 CLOSE csr_chk_pog_exist;
2345 --
2346 l_qtd_result := NULL;
2347 --
2348 IF g_qtd_result IS NOT NULL AND g_period_type = p_type
2349 AND NVL(g_assignment_id,-1) = p_ass_id AND NVL(g_payroll_action_id,-1) = p_pay_act_id THEN
2350 HR_UTILITY.TRACE('~~ 3 QResult : '||g_qtd_result);
2351 return g_qtd_result;
2352 END IF;
2353 --
2354 OPEN csr_chk_element_entry(p_ass_id,l_date_earned);
2355 FETCH csr_chk_element_entry INTO l_entry_exist;
2356 CLOSE csr_chk_element_entry;
2357 --
2358 IF NVL(l_entry_exist,'N') = 'Y' THEN
2359 g_payroll_action_id := p_pay_act_id;
2360 g_assignment_id := p_ass_id;
2361 g_period_type := p_type;
2362 g_qtd_result := 'DE';
2363 HR_UTILITY.TRACE('~~ 4 QResult : '||g_qtd_result);
2364 RETURN g_qtd_result;
2365 END IF;
2366 --
2367 /*
2368 IF p_type = 'QTD' THEN
2369 --
2370 OPEN csr_prev_qtd_payroll_exists(p_ass_act_id,l_start_date,l_end_date);
2371 FETCH csr_prev_qtd_payroll_exists INTO l_qtd_result;
2372 CLOSE csr_prev_qtd_payroll_exists;
2373 --
2374 IF l_qtd_result IS NULL THEN
2375 OPEN csr_diff_de_dp_qtd(p_pay_act_id,p_ass_act_id,l_start_date,l_end_date);
2376 FETCH csr_diff_de_dp_qtd INTO l_qtd_result;
2377 IF csr_diff_de_dp_qtd%FOUND THEN
2378 g_qtd_result := l_qtd_result;
2379 CLOSE csr_diff_de_dp_qtd;
2380 HR_UTILITY.TRACE('~~ 5 QResult : '||g_qtd_result);
2381 RETURN g_qtd_result;
2382 END IF;
2383 CLOSE csr_diff_de_dp_qtd;
2384 END IF;
2385 --
2386 ELSE
2387 OPEN csr_prev_lqtd_payroll_exists(p_ass_act_id,l_start_date,l_end_date);
2388 FETCH csr_prev_lqtd_payroll_exists INTO l_qtd_result;
2389 CLOSE csr_prev_lqtd_payroll_exists;
2390 --
2391 IF l_qtd_result IS NULL THEN
2392 OPEN csr_diff_de_dp_lqtd(p_pay_act_id,p_ass_act_id,l_start_date,l_end_date);
2393 FETCH csr_diff_de_dp_lqtd INTO l_qtd_result;
2394 IF csr_diff_de_dp_lqtd%FOUND THEN
2395 g_qtd_result := l_qtd_result;
2396 CLOSE csr_diff_de_dp_lqtd;
2397 HR_UTILITY.TRACE('~~ 6 QResult : '||g_qtd_result);
2398 RETURN g_qtd_result;
2399 END IF;
2400 CLOSE csr_diff_de_dp_lqtd;
2401 END IF;
2402 END IF;*/
2403 --
2404 g_payroll_action_id := p_pay_act_id;
2405 g_assignment_id := p_ass_id;
2406 g_period_type := p_type;
2407 g_qtd_result := NVL(l_qtd_result,'DP');
2408 --
2409 HR_UTILITY.TRACE('~~ 7 QResult : '||g_qtd_result);
2410 RETURN g_qtd_result;
2411 --
2412 END check_de_dp_dimension_qtd;
2413 --
2414 /*
2415 FUNCTION check_de_dp_dimension_qtd(p_pay_act_id NUMBER
2416 ,p_ass_id NUMBER
2417 ,p_ass_act_id NUMBER
2418 ,p_type VARCHAR2) RETURN VARCHAR2 IS
2419 --
2420 CURSOR csr_prev_qtd_payroll_exists(c_assignment_action_id NUMBER
2421 ,c_start_date DATE
2422 ,c_end_date DATE)IS
2423 SELECT 'DP' FROM dual
2424 WHERE EXISTS (SELECT 1
2425 FROM pay_assignment_actions paa1
2426 ,pay_assignment_actions paa2
2427 ,pay_payroll_actions ppa
2428 WHERE paa1.assignment_action_id = c_assignment_action_id
2429 AND paa2.assignment_id = paa1.assignment_id
2430 AND paa1.tax_unit_id = paa2.tax_unit_id
2431 AND ppa.action_type IN ('Q','R')
2432 AND ppa.action_status = 'C'
2433 AND ppa.payroll_action_id = paa2.payroll_action_id
2434 AND ppa.effective_date < TRUNC(c_start_date,'Q'));
2435 --
2436 CURSOR csr_prev_lqtd_payroll_exists(c_assignment_action_id NUMBER
2437 ,c_start_date DATE
2438 ,c_end_date DATE)IS
2439 SELECT 'DP' FROM dual
2440 WHERE EXISTS (SELECT 1
2441 FROM pay_assignment_actions paa1
2442 ,pay_assignment_actions paa2
2443 ,pay_payroll_actions ppa
2444 WHERE paa1.assignment_action_id = c_assignment_action_id
2445 AND paa2.assignment_id = paa1.assignment_id
2446 AND paa1.tax_unit_id = paa2.tax_unit_id
2447 AND ppa.action_type IN ('Q','R')
2448 AND ppa.action_status = 'C'
2449 AND ppa.payroll_action_id = paa2.payroll_action_id
2450 AND ppa.effective_date < c_start_date
2451 AND DECODE(trunc((to_number(to_char(ppa.effective_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(ppa.effective_date,'IW'))-1)/12)) <>
2452 DECODE(trunc((to_number(to_char(c_start_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(c_start_date,'IW'))-1)/12)));
2453 --
2454 CURSOR csr_diff_de_dp_qtd(c_payroll_action_id NUMBER
2455 ,c_assignment_action_id NUMBER
2456 ,c_start_date DATE
2457 ,c_end_date DATE)IS
2458 SELECT 'DE' FROM dual
2459 WHERE EXISTS (SELECT 1
2460 FROM pay_assignment_actions paa1
2461 ,pay_assignment_actions paa2
2462 ,pay_payroll_actions ppa1
2463 ,pay_payroll_actions ppa2
2464 WHERE ppa1.payroll_action_id = c_payroll_action_id
2465 AND ppa1.payroll_action_id = paa1.payroll_action_id
2466 AND paa1.assignment_action_id = c_assignment_action_id
2467 AND paa1.assignment_id = paa2.assignment_id
2468 AND paa1.tax_unit_id = paa2.tax_unit_id
2469 AND ppa2.payroll_action_id = paa2.payroll_action_id
2470 AND ppa2.action_type IN ('Q','R')
2471 AND ppa2.action_status in ('C','P')
2472 AND TRUNC(ppa2.date_earned,'Q') <> TRUNC(c_start_date,'Q')
2473 AND TRUNC(ppa2.effective_date,'Q') = TRUNC(c_start_date,'Q'));
2474 --
2475 CURSOR csr_diff_de_dp_lqtd(c_payroll_action_id NUMBER
2476 ,c_assignment_action_id NUMBER
2477 ,c_start_date DATE
2478 ,c_end_date DATE)IS
2479 SELECT 'DE' FROM dual
2480 WHERE EXISTS (SELECT 1
2481 FROM pay_assignment_actions paa1
2482 ,pay_assignment_actions paa2
2483 ,pay_payroll_actions ppa1
2484 ,pay_payroll_actions ppa2
2485 WHERE ppa1.payroll_action_id = c_payroll_action_id
2486 AND ppa1.payroll_action_id = paa1.payroll_action_id
2487 AND paa1.assignment_action_id = c_assignment_action_id
2488 AND paa1.assignment_id = paa2.assignment_id
2489 AND paa1.tax_unit_id = paa2.tax_unit_id
2490 AND ppa2.payroll_action_id = paa2.payroll_action_id
2491 AND ppa2.action_type IN ('Q','R')
2492 AND ppa2.action_status in ('C','P')
2493 AND DECODE(trunc((to_number(to_char(ppa2.date_earned,'IW'))-1)/12),4,3,trunc((to_number(to_char(ppa2.date_earned,'IW'))-1)/12)) <>
2494 DECODE(trunc((to_number(to_char(c_start_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(c_start_date,'IW'))-1)/12))
2495 AND DECODE(trunc((to_number(to_char(ppa2.effective_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(ppa2.effective_date,'IW'))-1)/12)) =
2496 DECODE(trunc((to_number(to_char(c_start_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(c_start_date,'IW'))-1)/12)) );
2497 --
2498 CURSOR c1 (c_payroll_action_id NUMBER)IS
2499 SELECT ptp.start_date
2500 ,ptp.end_date
2501 ,ppa.date_earned
2502 ,business_group_id
2503 FROM pay_payroll_actions ppa
2504 ,per_time_periods ptp
2505 WHERE ppa.payroll_action_id = c_payroll_action_id
2506 AND ptp.time_period_id = ppa.time_period_id;
2507 --
2508 CURSOR csr_chk_pog_exist(c_assignment_id NUMBER
2509 ,c_start_date DATE
2510 ,c_end_date DATE) IS
2511 SELECT '1' FROM DUAL
2512 WHERE EXISTS (SELECT 1
2513 FROM pay_object_groups pog1
2514 ,pay_object_groups pog2
2515 WHERE pog1.source_id = c_assignment_id
2516 AND pog1.source_type = pog2.source_type
2517 AND pog1.source_type = 'PAF'
2518 AND pog1.parent_object_group_id = pog2.parent_object_group_id
2519 AND (pog1.start_date <= c_end_date AND pog1.end_date >= c_start_date)
2520 AND (pog2.start_date <= c_end_date AND pog2.end_date >= c_start_date));
2521 --
2522 CURSOR csr_get_late_starter_flag(c_business_group_id NUMBER) IS
2523 SELECT org_information7
2524 FROM hr_organization_information
2525 WHERE organization_id = c_business_group_id
2526 AND org_information_context = 'NL_BG_INFO';
2527 --
2528 CURSOR csr_chk_element_entry(c_ass_id NUMBER
2529 ,c_eff_date DATE) IS
2530 SELECT 'Y'
2531 FROM dual
2532 WHERE exists (SELECT 1
2533 FROM pay_element_entries_f peef
2534 ,pay_element_types_f petf
2535 WHERE peef.assignment_id = c_ass_id
2536 AND peef.element_type_id = petf.element_type_id
2537 AND petf.element_name = 'Late Hire Indicator'
2538 AND c_eff_date BETWEEN peef.effective_start_date AND peef.effective_end_date
2539 AND c_eff_date BETWEEN petf.effective_start_date AND petf.effective_end_date);
2540 --
2541 l_qtd_result VARCHAR2(5);
2542 l_start_date DATE;
2543 l_end_date DATE;
2544 l_parent_id NUMBER;
2545 l_chk VARCHAR2(1);
2546 l_date_earned DATE;
2547 l_bg_id NUMBER;
2548 l_late_hire_indicator VARCHAR2(10);
2549 l_entry_exist VARCHAR2(1);
2550 --
2551 BEGIN
2552 --
2553 HR_UTILITY.TRACE('~~ Assignment_id :'||p_ass_id);
2554 IF g_late_hire_indicator = 'N' THEN
2555 HR_UTILITY.TRACE('~~ 1 QResult : DP');
2556 RETURN 'DP';
2557 END IF;
2558 --
2559 OPEN C1(p_pay_act_id);
2560 FETCH C1 INTO l_start_date,l_end_date,l_date_earned,l_bg_id;
2561 CLOSE C1;
2562 --
2563 IF NVL(g_late_hire_indicator,'X') = 'X' THEN
2564 OPEN csr_get_late_starter_flag(l_bg_id);
2565 FETCH csr_get_late_starter_flag INTO l_late_hire_indicator;
2566 CLOSE csr_get_late_starter_flag;
2567 --
2568 g_late_hire_indicator := NVL(l_late_hire_indicator,'Y');
2569 IF g_late_hire_indicator = 'N' THEN
2570 HR_UTILITY.TRACE('~~ 2 QResult : DP');
2571 RETURN 'DP';
2572 END IF;
2573 END IF;
2574 --
2575 OPEN csr_chk_pog_exist(p_ass_id,l_start_date,l_end_date); -- not req after BG check
2576 FETCH csr_chk_pog_exist INTO l_chk;
2577 IF csr_chk_pog_exist%NOTFOUND THEN
2578 g_qtd_result := 'DP';
2579 CLOSE csr_chk_pog_exist;
2580 RETURN g_qtd_result;
2581 END IF;
2582 CLOSE csr_chk_pog_exist;
2583 --
2584 l_qtd_result := NULL;
2585 --
2586 IF g_qtd_result IS NOT NULL AND g_period_type = p_type
2587 AND NVL(g_assignment_id,-1) = p_ass_id AND NVL(g_payroll_action_id,-1) = p_pay_act_id THEN
2588 HR_UTILITY.TRACE('~~ 3 QResult : '||g_qtd_result);
2589 return g_qtd_result;
2590 END IF;
2591 --
2592 OPEN csr_chk_element_entry(p_ass_id,l_date_earned);
2593 FETCH csr_chk_element_entry INTO l_entry_exist;
2594 CLOSE csr_chk_element_entry;
2595 --
2596 IF NVL(l_entry_exist,'N') = 'Y' THEN
2597 g_payroll_action_id := p_pay_act_id;
2598 g_assignment_id := p_ass_id;
2599 g_period_type := p_type;
2600 g_qtd_result := 'DE';
2601 HR_UTILITY.TRACE('~~ 4 QResult : '||g_qtd_result);
2602 RETURN g_qtd_result;
2603 END IF;
2604 --
2605 IF p_type = 'QTD' THEN
2606 --
2607 OPEN csr_prev_qtd_payroll_exists(p_ass_act_id,l_start_date,l_end_date);
2608 FETCH csr_prev_qtd_payroll_exists INTO l_qtd_result;
2609 CLOSE csr_prev_qtd_payroll_exists;
2610 --
2611 IF l_qtd_result IS NULL THEN
2612 OPEN csr_diff_de_dp_qtd(p_pay_act_id,p_ass_act_id,l_start_date,l_end_date);
2613 FETCH csr_diff_de_dp_qtd INTO l_qtd_result;
2614 IF csr_diff_de_dp_qtd%FOUND THEN
2615 g_qtd_result := l_qtd_result;
2616 CLOSE csr_diff_de_dp_qtd;
2617 HR_UTILITY.TRACE('~~ 5 QResult : '||g_qtd_result);
2618 RETURN g_qtd_result;
2619 END IF;
2620 CLOSE csr_diff_de_dp_qtd;
2621 END IF;
2622 --
2623 ELSE
2624 OPEN csr_prev_lqtd_payroll_exists(p_ass_act_id,l_start_date,l_end_date);
2625 FETCH csr_prev_lqtd_payroll_exists INTO l_qtd_result;
2626 CLOSE csr_prev_lqtd_payroll_exists;
2627 --
2628 IF l_qtd_result IS NULL THEN
2629 OPEN csr_diff_de_dp_lqtd(p_pay_act_id,p_ass_act_id,l_start_date,l_end_date);
2630 FETCH csr_diff_de_dp_lqtd INTO l_qtd_result;
2631 IF csr_diff_de_dp_lqtd%FOUND THEN
2632 g_qtd_result := l_qtd_result;
2633 CLOSE csr_diff_de_dp_lqtd;
2634 HR_UTILITY.TRACE('~~ 6 QResult : '||g_qtd_result);
2635 RETURN g_qtd_result;
2636 END IF;
2637 CLOSE csr_diff_de_dp_lqtd;
2638 END IF;
2639 END IF;
2640 --
2641 g_payroll_action_id := p_pay_act_id;
2642 g_assignment_id := p_ass_id;
2643 g_period_type := p_type;
2644 g_qtd_result := NVL(l_qtd_result,'DP');
2645 --
2646 HR_UTILITY.TRACE('~~ 7 QResult : '||g_qtd_result);
2647 RETURN g_qtd_result;
2648 --
2649 END check_de_dp_dimension_qtd;*/
2650 --
2651 --9453856
2652 PROCEDURE NL_ADJUST_TO_CORRECTION ( errbuf OUT NOCOPY VARCHAR2
2653 , retcode OUT NOCOPY NUMBER
2654 , p_bg_id IN NUMBER
2655 --, p_reprocess_date IN VARCHAR2 --9453856
2656 --, p_payroll_id IN NUMBER --9453856
2657 , p_adj_to_corr IN VARCHAR2
2658 )
2659 IS
2660
2661 nl_adj_exception EXCEPTION;
2662 error_message boolean;
2663 l_message VARCHAR2(300);
2664
2665 --Pick all the assignmnets for the payroll and reprocess_date passed to the concurrent program.
2666 --Payroll is an optional parameter.
2667 --Reprocess_date is a mandatory parameter.
2668 /*
2669 CURSOR c_valid_assignments
2670 IS
2671 select distinct pra.assignment_id, pra.retro_assignment_id
2672 from
2673 pay_retro_assignments pra,
2674 per_all_assignments_f paaf
2675 where
2676 pra.assignment_id = paaf.assignment_id
2677 and pra.reprocess_date >= fnd_date.canonical_to_date(p_reprocess_date) --from conc parameter
2678 and pra.retro_assignment_action_id IS NULL
2679 and pra.approval_status in ('A', 'P')
2680 and paaf.payroll_id = NVL(p_payroll_id,paaf.payroll_id) --from conc parameter
2681 and pra.reprocess_date between paaf.effective_start_date and paaf.effective_end_date
2682 and paaf.business_group_id = p_bg_id;
2683
2684 --Pick all the ABP/PGGM seeded element entries created with Adjustment as default.
2685 CURSOR c_valid_entries(p_retro_assignment_id number)
2686 IS
2687 select pre.* from
2688 pay_retro_entries pre,
2689 pay_element_entries_f peef,
2690 PAY_RETRO_COMPONENTS prc
2691 where
2692 pre.retro_assignment_id = p_retro_assignment_id
2693 and pre.element_entry_id = peef.element_entry_id
2694 and pre.reprocess_date between peef.effective_start_date and peef.effective_end_date
2695 and prc.retro_component_id = pre.retro_component_id
2696 and prc.component_name = 'Adjustment'
2697 and prc.legislation_code = 'NL'
2698 and EXISTS
2699 ( select 1
2700 from pay_element_types_f pet
2701 where
2702 pet.business_group_id IS NULL
2703 AND pet.legislation_code = 'NL'
2704 AND peef.element_type_id = pet.element_type_id
2705 AND (pet.element_name like '%ABP%' OR pet.element_name like '%PGGM%')
2706 AND EXISTS
2707 ( select 1 from
2708 pay_retro_component_usages prcu,
2709 PAY_RETRO_COMPONENTS prc1
2710 where
2711 prcu.creator_id = pet.element_type_id
2712 AND prcu.business_group_id IS NULL
2713 AND prcu.legislation_code = prc1.legislation_code
2714 AND prcu.legislation_code = 'NL'
2715 AND prcu.retro_component_id = prc1.retro_component_id
2716 AND
2717 (
2718 (prc1.component_name = 'Adjustment' AND prcu.default_component = 'Y' AND prcu.reprocess_type = 'R' ) --Adjustment
2719 OR
2720 (prc1.component_name = 'Correction' AND prcu.default_component = 'N' AND prcu.reprocess_type = 'S') --Correction
2721 )
2722 )
2723 );
2724 */
2725
2726 CURSOR csr_check
2727 IS
2728 SELECT *
2729 FROM pay_patch_status
2730 WHERE patch_number = 9453856
2731 AND patch_name = 'NL_9453856'
2732 AND phase = 'C'
2733 AND legislation_code = 'NL';
2734
2735 rg_csr_check csr_check%rowtype;
2736 l_already_run BOOLEAN;
2737
2738 CURSOR c_corr_adj_component(p_component_name VARCHAR2)
2739 IS
2740 SELECT retro_component_id
2741 FROM
2742 PAY_RETRO_COMPONENTS
2743 WHERE component_name = p_component_name
2744 AND legislation_code = 'NL';
2745
2746 l_corr_component_id number;
2747 l_adj_component_id number;
2748
2749 --Check if there is any ABP/PGGM entry processed with Adjustment.
2750 CURSOR c_adj_processed
2751 IS
2752 select 1
2753 from
2754 pay_retro_assignments pra,
2755 pay_retro_entries pre,
2756 pay_element_entries_f peef,
2757 pay_element_types_f petf,
2758 pay_retro_components prc
2759 where
2760 pra.RETRO_ASSIGNMENT_ID = pre.RETRO_ASSIGNMENT_ID
2761 and peef.ELEMENT_ENTRY_ID = pre.ELEMENT_ENTRY_ID
2762 and pre.reprocess_date between peef.effective_start_date and peef.effective_end_date
2763 and peef.element_type_id = petf.element_type_id
2764 --and (petf.element_name like '%ABP%' OR petf.element_name like '%PGGM%')
2765 and (petf.element_name = 'ABP Pensions' OR
2766 petf.element_name = 'ABP Pensions Part Time Percentage' OR
2767 petf.element_name = 'ABP Pensions Reporting Part Time Percentage' OR
2768 petf.element_name = 'ABP Pensions General Information' OR
2769 petf.element_name = 'PGGM Pensions General Information' OR
2770 petf.element_name = 'PGGM Pensions Part Time Percentage')
2771 and petf.legislation_code = 'NL' --9453856
2772 and pre.reprocess_date between petf.effective_start_date and petf.effective_end_date
2773 and prc.legislation_code = 'NL'
2774 and prc.RETRO_COMPONENT_ID = pre.RETRO_COMPONENT_ID
2775 and prc.COMPONENT_NAME = 'Adjustment';
2776
2777 l_adj_processed c_adj_processed%rowtype;
2778
2779 --Pick all ABP/PGGM seeded elements present
2780 --with 'Adjustment-Reprocess-Default' and 'Correction-Static'
2781 --and modify it to 'Correction-Reprocess-Default' and Delete 'Adjustment-Static'
2782 CURSOR c_ABP_PGGM_Elements
2783 IS
2784 select *
2785 from pay_element_types_f pet
2786 where
2787 pet.business_group_id IS NULL
2788 AND pet.legislation_code = 'NL'
2789 AND (pet.element_name like '%ABP%' OR pet.element_name like '%PGGM%')
2790 AND EXISTS
2791 ( select 1 from
2792 pay_retro_component_usages prcu,
2793 PAY_RETRO_COMPONENTS prc1
2794 where
2795 prcu.creator_id = pet.element_type_id
2796 AND prcu.business_group_id IS NULL
2797 AND prcu.legislation_code = prc1.legislation_code
2798 AND prcu.legislation_code = 'NL'
2799 AND prcu.retro_component_id = prc1.retro_component_id
2800 AND
2801 (
2802 (prc1.component_name = 'Adjustment' AND prcu.default_component = 'Y' AND prcu.reprocess_type = 'R' ) --Adjustment
2803 OR
2804 (prc1.component_name = 'Correction' AND prcu.default_component = 'N' AND prcu.reprocess_type = 'S') --Correction
2805 )
2806 );
2807
2808 CURSOR C_RET_COMP_USAGE(p_creator_id NUMBER, p_retro_component_id NUMBER)
2809 IS
2810 SELECT *
2811 FROM
2812 PAY_RETRO_COMPONENT_USAGES
2813 WHERE
2814 CREATOR_ID = p_creator_id
2815 AND RETRO_COMPONENT_ID = p_retro_component_id
2816 AND LEGISLATION_CODE = 'NL'
2817 AND CREATOR_TYPE = 'ET';
2818
2819
2820 BEGIN
2821
2822 hr_utility.set_location('Entering NL_ADJUST_TO_CORRECTION',250);
2823 --hr_utility.set_location('p_payroll_id '||p_payroll_id,250);
2824 --hr_utility.set_location('p_reprocess_date '||p_reprocess_date,250);
2825 hr_utility.set_location('p_bg_id '||p_bg_id,250 );
2826
2827 /*
2828 FOR rec_asg IN c_valid_assignments --(p_reprocess_date, p_payroll_id, p_bg_id)
2829 LOOP
2830 hr_utility.set_location('Retro Assignment ID '||rec_asg.retro_assignment_id,300 );
2831 hr_utility.set_location('Assignment ID '||rec_asg.assignment_id,300 );
2832 FOR rec_entry IN c_valid_entries(rec_asg.retro_assignment_id )
2833 LOOP
2834 hr_utility.set_location('Assignment ID '||rec_entry.element_entry_id,310 );
2835 --Update retro entries to corection.
2836 pay_retro_status_internal.maintain_retro_entry
2837 (p_retro_assignment_id => rec_entry.retro_assignment_id
2838 ,p_element_entry_id => rec_entry.element_entry_id
2839 ,p_reprocess_date => rec_entry.reprocess_date
2840 ,p_effective_date => rec_entry.effective_date
2841 ,p_retro_component_id => l_retro_component_id
2842 ,p_owner_type => rec_entry.owner_type
2843 ,p_system_reprocess_date => rec_entry.system_reprocess_date
2844 ,p_entry_param_name => NULL
2845 );
2846
2847 END LOOP;
2848 END LOOP;
2849 */
2850
2851 OPEN csr_check;
2852 FETCH csr_check into rg_csr_check;
2853 IF csr_check%NOTFOUND THEN
2854 l_already_run := FALSE;
2855 ELSE
2856 l_already_run := TRUE;
2857 END IF;
2858 CLOSE csr_check;
2859
2860 IF NOT l_already_run THEN
2861
2862 OPEN c_corr_adj_component('Correction');
2863 FETCH c_corr_adj_component INTO l_corr_component_id;
2864 CLOSE c_corr_adj_component;
2865
2866 hr_utility.set_location('l_corr_component_id '||l_corr_component_id,260 );
2867
2868 OPEN c_corr_adj_component('Adjustment');
2869 FETCH c_corr_adj_component INTO l_adj_component_id;
2870 CLOSE c_corr_adj_component;
2871
2872 hr_utility.set_location('l_adj_component_id '||l_adj_component_id,270 );
2873
2874 OPEN c_adj_processed;
2875 FETCH c_adj_processed INTO l_adj_processed;
2876
2877 IF c_adj_processed%FOUND THEN
2878 CLOSE c_adj_processed;
2879 Raise nl_adj_exception;
2880 ELSE
2881 CLOSE c_adj_processed; --9453856
2882 FOR rec_ele IN c_ABP_PGGM_Elements
2883 LOOP
2884
2885 --Modifying the Adjustment component to Non Default and Static
2886 /* UPDATE pay_retro_component_usages
2887 SET DEFAULT_COMPONENT = 'N', REPROCESS_TYPE = 'S'
2888 WHERE
2889 CREATOR_ID = rec_ele.element_type_id
2890 AND RETRO_COMPONENT_ID = l_adj_component_id
2891 AND CREATOR_TYPE = 'ET'
2892 AND LEGISLATION_CODE = 'NL'
2893 AND DEFAULT_COMPONENT = 'Y'
2894 AND REPROCESS_TYPE = 'R'; */
2895
2896 --Adjustment span and usage need to delete.
2897 FOR rec_usage IN C_RET_COMP_USAGE(rec_ele.element_type_id, l_adj_component_id)
2898 LOOP
2899
2900 --
2901 -- Delete from pay_element_span usages
2902 --
2903 DELETE FROM pay_element_span_usages
2904 WHERE legislation_code = 'NL'
2905 AND retro_component_usage_id = rec_usage.retro_component_usage_id;
2906
2907 --
2908 -- Delete from pay_retro_component_usages
2909 --
2910 DELETE FROM pay_retro_component_usages
2911 WHERE legislation_code = 'NL'
2912 AND retro_component_usage_id = rec_usage.retro_component_usage_id;
2913
2914 END LOOP;
2915
2916 --Modifying the Correction component to Default and Reprocess
2917 IF rec_ele.element_name IN ('PGGM Pensions General Information', 'ABP Pensions General Information')
2918 THEN
2919 UPDATE pay_retro_component_usages
2920 SET DEFAULT_COMPONENT = 'Y'
2921 WHERE
2922 CREATOR_ID = rec_ele.element_type_id
2923 AND RETRO_COMPONENT_ID = l_corr_component_id
2924 AND CREATOR_TYPE = 'ET'
2925 AND LEGISLATION_CODE = 'NL';
2926 ELSE
2927 UPDATE pay_retro_component_usages
2928 SET DEFAULT_COMPONENT = 'Y', REPROCESS_TYPE = 'R'
2929 WHERE
2930 CREATOR_ID = rec_ele.element_type_id
2931 AND RETRO_COMPONENT_ID = l_corr_component_id
2932 AND CREATOR_TYPE = 'ET'
2933 AND LEGISLATION_CODE = 'NL';
2934 END IF;
2935
2936 END LOOP;
2937 END IF;
2938
2939 INSERT INTO pay_patch_status
2940 (id,patch_number,patch_name,phase,applied_date,legislation_code)
2941 SELECT pay_patch_status_s.nextval,9453856,'NL_9453856','C',sysdate,'NL' FROM dual;
2942
2943 END IF;
2944
2945 hr_utility.set_location('Leaving NL_ADJUST_TO_CORRECTION',320);
2946
2947 EXCEPTION
2948 WHEN OTHERS then
2949 hr_utility.trace('SQLERRM:'||substr(sqlerrm,1,200));
2950 hr_utility.set_location('Leaving NL_ADJUST_TO_CORRECTION',330);
2951 --raise_application_error(-20001, 'This process does not correlate with the note id 558457.1.') ;
2952 l_message := substrb(fnd_message.get_string('PAY','HR_NL_ADJ_TO_CORR'),1,300); --9453856
2953 fnd_file.put_line(fnd_file.log,l_message);
2954 error_message := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_message);
2955
2956 END NL_ADJUST_TO_CORRECTION;
2957 --9453856
2958
2959
2960
2961 --13350181
2962 FUNCTION get_run_type(p_payroll_action_id IN NUMBER
2963 ,p_assignment_action_id IN NUMBER) RETURN VARCHAR2
2964 IS
2965
2966 CURSOR csr_run_type(c_payroll_action_id NUMBER
2967 ,c_assignment_action_id NUMBER)
2968 IS
2969 SELECT prt.shortname
2970 FROM pay_run_types_f prt
2971 ,pay_payroll_actions ppa
2972 ,pay_assignment_actions paa
2973 WHERE ppa.payroll_action_id = c_payroll_action_id
2974 AND paa.payroll_action_id = ppa.payroll_action_id
2975 AND prt.run_type_id = paa.run_type_id
2976 AND paa.assignment_action_id = c_assignment_action_id
2977 AND ppa.effective_date BETWEEN prt.effective_start_date
2978 AND prt.effective_end_date;
2979
2980 l_run_type pay_run_types_f.shortname%TYPE;
2981
2982 BEGIN
2983
2984 OPEN csr_run_type(p_payroll_action_id,p_assignment_action_id);
2985 FETCH csr_run_type into l_run_type;
2986 CLOSE csr_run_type;
2987
2988 fnd_file.put_line(FND_FILE.LOG,'Process Mode : '||l_run_type || ' for Payroll Action Id : '||p_payroll_action_id|| ' Assignment Action Id : '||p_assignment_action_id);
2989
2990 RETURN l_run_type;
2991
2992 END get_run_type;
2993
2994
2995 END PAY_NL_GENERAL;
[Home] [Help]