1 package body pqp_alien_expat_taxation_pkg as
2 /* $Header: pqalnexp.pkb 120.10 2009/12/22 22:26:56 rnestor noship $ */
3
4 -- global Variable
5 g_package constant varchar2(150) := 'pqp_alien_expat_taxation_pkg';
6 g_bus_grp_id number(15);
7 --
8 -- The cursor below selects the process_event_id, object_version_number,
9 -- assignment_id, description from pay_process_events with status = 'NOT_READ'
10 --
11 cursor pay_process_events_ovn_cursor(p_person_id1 in number
12 ,p_change_type1 in varchar2
13 ,p_effective_date1 in date) is
14
15 select ppe.process_event_id process_event_id
16 ,ppe.object_version_number object_version_number
17 ,paf.assignment_id assignment_id
18 ,ppe.description description
19
20 from pay_process_events ppe
21 ,per_people_f ppf
22 ,per_assignments_f paf
23
24 where ppf.person_id = p_person_id1
25 and ppf.person_id = paf.person_id
26 and ppe.assignment_id = paf.assignment_id
27 and ppe.change_type = p_change_type1
28 and ppf.effective_start_date <= to_date(('12/31/' ||
29 to_char(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
30 and ppf.effective_end_date >= to_date(('01/01/' ||
31 to_char(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
32 and ppf.effective_start_date =
33 (select max(effective_start_date)
34 from per_people_f
35 where person_id = ppf.person_id
36 and effective_start_date <=
37 to_date(('12/31/' ||TO_CHAR(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
38 )
39
40 and paf.effective_start_date <=
41 to_date(('12/31/' ||TO_CHAR(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
42 and paf.effective_end_date >=
43 to_date(('01/01/' || to_char(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
44 and paf.effective_start_date =
45 (select max(effective_start_date)
46 from per_assignments_f
47 where assignment_id = paf.assignment_id
48 and effective_start_date <=
49 to_date(('12/31/' ||to_char(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
50 )
51 and ppe.status = 'N';
52 --
53 -- The cursor below checks whether a country code passed is a valid
54 -- IRS country code or not
55 --
56 cursor c_tax_country_code_cursor(p_country_code in varchar2,
57 p_effective_date in date) is
58 select count(*)
59 from hr_lookups hrl
60 where hrl.lookup_type = 'PER_US_COUNTRY_CODE'
61 and hrl.enabled_flag = 'Y'
62 and nvl(start_date_active, p_effective_date) <= to_date(('12/31/' ||
63 to_char(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
64 and nvl(end_date_active, p_effective_date) >= to_date(('01/01/' ||
65 to_char(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
66 and upper(hrl.lookup_code) = upper(p_country_code)
67 order by hrl.lookup_code;
68 --
69 -- The cursor c_person_visit_spouse_info gives the Visit history of a particular
70 -- person id. Duplicated the information_category where clause of the virtual
71 -- view, in the actual where clause as this query behaves differently in
72 -- different databases.
73 --
74 cursor c_person_visit_spouse_info(p_person_id in number
75 ,p_effective_date in date) is
76 select pei_information5 purpose
77 ,pei_information7 start_date
78 ,pei_information8 end_date
79 ,pei_information9 spouse_accompanied
80 ,pei_information10 child_accompanied
81 from (select *
82 from per_people_extra_info
83 where information_type = 'PER_US_VISIT_HISTORY'
84 ) ppei
85 where ppei.person_id = p_person_id
86 and ppei.information_type = 'PER_US_VISIT_HISTORY'
87 and (to_char(fnd_date.canonical_to_date(ppei.pei_information7),'YYYY')=
88 to_char(p_effective_date, 'YYYY')
89 or
90 to_char(fnd_date.canonical_to_date(ppei.pei_information8),'YYYY')=
91 to_char(p_effective_date, 'YYYY')
92 or
93 p_effective_date
94 between fnd_date.canonical_to_date(ppei.pei_information7)
95 and nvl(fnd_date.canonical_to_date(ppei.pei_information8),
96 to_date('12/31/4712','MM/DD/YYYY')
97 )
98 )
99 order by 4 asc;
100 --
101 -- The cursor below gets the batch size
102 --
103 cursor c_pay_action_parameter is
104 select parameter_value
105 from pay_action_parameters
106 where parameter_name = 'PQP_US_WINDSTAR_READ_BATCH';
107 --
108 -- The cursor below gets the additional details of a person
109 --
110 cursor c_person_additional_info(p_person_id in number ) is
111 select pei_information5 residency_status
112 ,pei_information7 resident_status_date
113 ,pei_information12 process_type
114 ,pei_information8 first_entry_date
115 ,nvl(pei_information10, 0) dep_children_total
116 ,nvl(pei_information11, 0) dep_children_in_cntry
117 ,pei_information9 tax_res_country_code
118 from (select *
119 from per_people_extra_info
120 where information_type = 'PER_US_ADDITIONAL_DETAILS'
121 and person_id = p_person_id );
122 --
123 -- The cursor below selects the object version number in pay_process_events table.
124 --
125 cursor c_ovn_ppe(p_process_event_id in number) is
126 select object_version_number
127 from pay_process_events
128 where process_event_id = p_process_event_id;
129
130 -- local Variable
131 l_batch_size number;
132
133 -- ===========================================================================
134 -- Name : IsPayrollRun
135 -- Purpose : The following function return TRUE or FALSE when a person id
136 -- and a date in a year is passed as input. It return TRUE if a
137 -- payroll has been run for that person. Otherwise it returns a
138 -- FALSE.
139 -- Arguments :
140 -- IN
141 -- p_person_id : Person Id
142 -- p_effective_date : Effective date.
143 -- p_income_code : Income Code
144 -- OUT NOCOPY : Boolean
145 -- Notes : Private
146 -- Added p_income_code parameter and changed the main select statement
147 -- to check if the income code was processed during the payroll run.
148 -- ===========================================================================
149
150 function IsPayrollRun(p_person_id in number
151 ,p_effective_date in date
152 ,p_income_code in varchar2)
153 return boolean is
154
155 cursor IsPayrollRun (p_person_id in number
156 ,p_effective_date in date
157 ,p_income_code in varchar2 ) is
158 select 'Y' from
159 dual where exists
160 (select ppa.date_earned
161 from pay_payroll_actions ppa
162 ,pay_assignment_actions paa
163 ,pay_run_results prr
164 ,pay_element_types_f pet
165 ,pay_element_classifications pec
166 ,per_assignments_f paf
167 where ppa.payroll_action_id = paa.payroll_action_id
168 and paa.assignment_id = paf.assignment_id
169 and ppa.action_status = 'C'
170 and paa.action_status = 'C'
171 and ppa.action_type in ('R','Q','I','B','V')
172 and paf.person_id = p_person_id
173 and prr.assignment_action_id = paa.assignment_action_id
174 and pet.element_type_id = prr.element_type_id
175 and prr.status = 'P'
176 and pet.classification_id = pec.classification_id
177 and pec.classification_name = 'Alien/Expat Earnings'
178 and pet.element_information1 = p_income_code
179 and paf.effective_start_date <= p_effective_date
180 and ppa.effective_date <= p_effective_date);
181
182 l_temp_var varchar2(10);
183 l_proc constant varchar2(150) := g_package||'IsPayrollRun';
184
185 begin
186
187 hr_utility.set_location('Entering: '||l_proc, 5);
188
189 l_temp_var := 'N';
190
191 open IsPayrollRun(p_person_id,
192 p_effective_date,
193 p_income_code);
194 fetch IsPayrollRun into l_temp_var;
195 close IsPayrollRun;
196 if (l_temp_var = 'Y') then
197 return true;
198 else
199 return false;
200 end if;
201
202 hr_utility.set_location('Leaving: '||l_proc, 80);
203
204 end IsPayrollRun;
205
206 -- ===========================================================================
207 -- Name : PQP_Balance
208 -- Purpose : The following function is called from pqp_windstar_balance_read.
209 -- This returns the balance amount for an assignment and dimension
210 -- on an effective_date. If an assignment for the person is passed,
211 -- then the balances are given for a person. This is due to the
212 -- default dimension this function uses.
213 -- Arguments :
214 -- In
215 -- p_balance_name : Name of the balance
216 -- p_dimension_name : Dimension Name
217 -- p_assignment_id : Assignment Id
218 -- p_effective_date : Effective date.
219 -- Out NoCopy : None
220 -- Notes : Private
221 -- ===========================================================================
222 function PQP_Balance
223 (p_income_code in varchar2
224 ,p_dimension_name in varchar2
225 ,p_assignment_id in number
226 ,p_effective_date in date
227 ,p_state_code in varchar2
228 ,p_fit_wh_bal_flag in varchar2
229 ,p_balance_name in varchar2
230 )
231 return number is
232
233 l_balance_amount number := 0 ;
234 l_proc constant varchar2(150) := g_package||'PQP_Balance';
235
236 begin
237
238 hr_utility.set_location('Entering: '||l_proc, 5);
239
240 l_balance_amount := pqp_us_ff_functions.get_alien_bal
241 (p_assignment_id => p_assignment_id
242 ,p_effective_date => p_effective_date
243 ,p_tax_unit_id => null
244 ,p_income_code => p_income_code
245 ,p_balance_name => p_balance_name
246 ,p_dimension_name => p_dimension_name
247 ,p_state_code => p_state_code
248 ,p_fit_wh_bal_flag => p_fit_wh_bal_flag
249 );
250
251 hr_utility.set_location('Leaving: '||l_proc, 10);
252
253 return l_balance_amount;
254
255 exception
256 when others then
257 hr_utility.set_location('Leaving: '||l_proc, 15);
258 return 0;
259
260 end PQP_Balance;
261
262 -- ===========================================================================
263 -- Name : PQP_Forecasted_Balance
264 -- Purpose : The following function is called from pqp_windstar_balance_read.
265 -- This returns the forecasted balance amount for a person.
266 -- Arguments :
267 -- IN
268 -- p_person_id : Person Id
269 -- p_income_code : Income Code
270 -- p_effective_date : Effective date.
271 -- Out NoCopy : None
272 -- Notes : Private
273 -- ===========================================================================
274 function PQP_Forecasted_Balance
275 (p_person_id in number
276 ,p_income_code in varchar2
277 ,p_effective_date in date
278 )
279 return number is
280
281 --
282 -- Segments: Income_Code - pei_information5
283 -- Amount - pei_information7
284 -- Year - pei_information8
285 --
286 cursor c2 is
287 select pei_information7 amount
288 from (select *
289 from per_people_extra_info
290 where person_id = p_person_id
291 and information_type = 'PER_US_INCOME_FORECAST'
292 )
293 where pei_information5 = p_income_code
294 and pei_information8 = to_char(p_effective_date, 'YYYY');
295
296 lnum number;
297 l_proc constant varchar2(72) := g_package||'PQP_Forecasted_Balance';
298
299 begin
300
301 hr_utility.set_location('Entering: '||l_proc, 5);
302 lnum := 0;
303
304 for c2_cur in c2
305 loop
306 lnum := c2_cur.amount;
307 hr_utility.set_location(l_proc, 6);
308 end loop;
309
310 hr_utility.set_location('Leaving: '||l_proc, 10);
311
312 return lnum;
313 exception
314 when others then
315 hr_utility.set_location('Leaving: '||l_proc, 15);
316 return 0;
317
318 end PQP_Forecasted_Balance;
319
320 -- ===========================================================================
321 -- Name : PQP_Windstar_Person_Validate
322 -- Purpose : The following procedure is called from pqp_windstar_person_read.
323 -- This validates the person record.
324 -- Arguments :
325 -- IN
326 -- p_in_data_rec : The PL/SQL table that contains the Person Records
327 -- p_effective_date : DATE
328 -- OUT
329 -- p_out_mesg : Error Message.
330 -- Notes : Private
331 -- ===========================================================================
332 procedure PQP_Windstar_Person_Validate
333 (p_in_data_rec in t_people_rec_type
334 ,p_effective_date in date
335 ,p_out_mesg out nocopy out_mesg_type
336 ) is
337
338 --
339 -- The following cursor verifies whether the country code passed is a valid
340 -- coutry code
341 --
342 cursor c_non_us_address_cur(p_country_code in varchar2) is
343 select count(*)
344 from fnd_territories_vl
345 where territory_code = upper(p_country_code);
346
347 l_temp_prefix varchar2(45) := ':';
348 l_count number := 0;
349 l_non_us_country_code varchar2(100);
350 l_proc constant varchar2(150):= g_package||'PQP_Windstar_Person_Validate';
351
352 begin
353 hr_utility.set_location('Entering:'||l_proc, 5);
354
355 p_out_mesg := 'ERROR ==> ';
356
357 if (rtrim(ltrim(p_in_data_rec.last_name)) is null) then
358 p_out_mesg := p_out_mesg || l_temp_prefix || 'Last Name is NULL';
359 end if;
360 if (rtrim(ltrim(p_in_data_rec.first_name)) is null) then
361 p_out_mesg := p_out_mesg || l_temp_prefix || 'First Name is NULL';
362 end if;
363 if (rtrim(ltrim(p_in_data_rec.person_id)) is null) then
364 p_out_mesg := p_out_mesg || l_temp_prefix || 'Person Id is NULL';
365 end if;
366 if (rtrim(ltrim(p_in_data_rec.national_identifier)) is null) then
367 p_out_mesg := p_out_mesg || l_temp_prefix ||
368 'National Identifier is NULL';
369 end if;
370 if (rtrim(ltrim(p_in_data_rec.city)) is null) then
371 p_out_mesg := p_out_mesg || l_temp_prefix || 'City is NULL';
372 end if;
373 if (rtrim(ltrim(p_in_data_rec.address_line1)) is null) then
374 p_out_mesg := p_out_mesg || l_temp_prefix || 'Address Line1 is NULL';
375 end if;
376 if (rtrim(ltrim(p_in_data_rec.state)) is null) then
377 p_out_mesg := p_out_mesg || l_temp_prefix || 'State is NULL';
378 end if;
379 if (rtrim(ltrim(p_in_data_rec.postal_code)) is null) then
380 p_out_mesg := p_out_mesg || l_temp_prefix || 'Postal Code is NULL';
381 end if;
382 if (rtrim(ltrim(p_in_data_rec.citizenship_c_code)) is null or
383 p_in_data_rec.citizenship_c_code = ' ' ) then
384 p_out_mesg := p_out_mesg || l_temp_prefix || 'Citizenship Code is NULL';
385 null;
386 else
387 hr_utility.set_location(l_proc, 6);
388 open c_tax_country_code_cursor(p_in_data_rec.citizenship_c_code ,
389 p_effective_date );
390 fetch c_tax_country_code_cursor into l_count;
391 hr_utility.set_location(l_proc, 7);
392 close c_tax_country_code_cursor;
393 if (l_count = 0) then
394 p_out_mesg := p_out_mesg || l_temp_prefix ||
395 'citizenship code is invalid';
396 end if;
397 hr_utility.set_location(l_proc, 8);
398 end if;
399 if (p_out_mesg = 'ERROR ==> ') then
400 p_out_mesg := null;
401 end if;
402 hr_utility.set_location('Leaving: '||l_proc, 10);
403
404 exception
405 when others then
406 hr_utility.set_location('Leaving: '||l_proc, 15);
407
408 p_out_mesg := SUBSTR(p_out_mesg || TO_CHAR(SQLCODE) || SQLERRM, 1, 240) ;
409
410 end PQP_Windstar_Person_Validate;
411
412 -- ===========================================================================
413 -- Name : PQP_Windstar_Visa_Validate
414 -- Purpose : The following procedure is called from pqp_windstar_visa_read.
415 -- This validates the visa record.
416 -- Arguments :
417 -- IN
418 -- p_in_data_rec : The PL/SQL table that contains the Visa Records
419 -- p_effective_date : DATE
420 -- OUT
421 -- p_out_mesg : Error Message.
422 -- Notes : Private
423 -- ===========================================================================
424 procedure PQP_Windstar_Visa_Validate
425 (p_in_data_rec in t_visa_rec_type
426 ,p_effective_date in date
427 ,p_prev_end_date in date
428 ,p_out_mesg out nocopy out_mesg_type
429 ) is
430 l_proc constant varchar2(72) := g_package||'PQP_Windstar_Visa_Validate';
431 l_temp_prefix varchar2(45) := ':';
432
433 begin
434
435 hr_utility.set_location('Entering:'||l_proc, 5);
436
437 p_out_mesg := 'ERROR ==> ';
438 if (ltrim(rtrim(p_in_data_rec.visa_type)) is null) then
439 p_out_mesg := p_out_mesg || l_temp_prefix || 'Visa Type is NULL';
440 end if;
441
442 if (p_prev_end_date is not null) then
443 if (p_prev_end_date = p_in_data_rec.visa_start_date ) then
444 p_out_mesg := p_out_mesg || l_temp_prefix || 'Visa record having a'
445 ||' start date of ' || TO_CHAR(p_in_data_rec.visa_start_date,'DD/MM/YYYY')
446 || '(DD/MM/YYYY) is overlapping with the end date of another visa record';
447 end if;
448 end if;
449
450 if (p_in_data_rec.visa_type = 'J-1' or
451 p_in_data_rec.visa_type = 'J-2') then
452 if (ltrim(rtrim(p_in_data_rec.j_category_code)) is null) then
453 p_out_mesg := p_out_mesg || l_temp_prefix || 'Visa Category is NULL';
454 end if;
455 end if;
456
457 if (ltrim(rtrim(p_in_data_rec.visa_end_date)) is null) then
458 p_out_mesg := p_out_mesg || l_temp_prefix || 'Visa End Date is NULL';
459 end if;
460
461 if (ltrim(rtrim(p_in_data_rec.visa_number)) is null) then
462 p_out_mesg := p_out_mesg || l_temp_prefix || 'Visa number is NULL';
463 end if;
464
465 if (ltrim(rtrim(p_in_data_rec.primary_activity_code)) is null) then
466 p_out_mesg := p_out_mesg || l_temp_prefix ||
467 'Primary Activity/Purpose is NULL';
468 end if;
469
470 if (p_out_mesg = 'ERROR ==> ') then
471 p_out_mesg := null;
472 end if;
473
474 hr_utility.set_location('Leaving:'||l_proc, 10);
475
476 exception
477 when others then
478 hr_utility.set_location('Entering excep:'||l_proc, 15);
479 p_out_mesg := SUBSTR(p_out_mesg || TO_CHAR(SQLCODE) || SQLERRM, 1, 240) ;
480
481 end PQP_Windstar_Visa_Validate;
482
483 -- ===========================================================================
484 -- Name : PQP_Windstar_Balance_Validate
485 -- Purpose : The following procedure is called from pqp_windstar_person_read.
486 -- This validates the person record.
487 -- Arguments :
488 -- IN
489 -- p_in_data_rec : The PL/SQL table that contains the Person Records
490 -- p_effective_date : Date
491 -- OUT
492 -- p_out_mesg : Error Message.
493 -- Notes : Private
494 -- ===========================================================================
495 procedure PQP_Windstar_Balance_Validate
496 (p_in_data_rec in t_balance_rec_type
497 ,p_effective_date in date
498 ,p_out_mesg out nocopy out_mesg_type
499 ,p_forecasted in boolean
500 ) is
501 --
502 l_proc constant varchar2(72) := g_package||'PQP_Windstar_Balance_Validate';
503 l_temp_prefix varchar2(45) := ':';
504 --
505 begin
506 hr_utility.set_location('Entering: '||l_proc, 5);
507
508 p_out_mesg := 'ERROR ==> ';
509
510 if (rtrim(ltrim(p_in_data_rec.income_code)) is null) then
511 p_out_mesg := p_out_mesg || l_temp_prefix || 'Income Code is NULL';
512 end if;
513
514 if (rtrim(ltrim(p_in_data_rec.income_code_sub_type)) is null) then
515 p_out_mesg := p_out_mesg || l_temp_prefix ||
516 'Income Code Sub Type is NULL';
517 end if;
518
519 if (rtrim(ltrim(p_in_data_rec.exemption_code)) is null) then
520 p_out_mesg := p_out_mesg || l_temp_prefix || 'Exemption Code is NULL';
521 end if;
522
523 if (RTRIM(LTRIM(p_in_data_rec.gross_amount)) is null) then
524 p_out_mesg := p_out_mesg || l_temp_prefix || 'Gross Amount is NULL';
525 end if;
526
527 if (RTRIM(LTRIM(p_in_data_rec.withholding_allowance)) is null) then
528 p_out_mesg := p_out_mesg || l_temp_prefix ||
529 'Withholding Allowance is NULL';
530 end if;
531
532 if (RTRIM(LTRIM(p_in_data_rec.withholding_rate)) is null) then
533 p_out_mesg := p_out_mesg || l_temp_prefix || 'Withholding Rate is NULL';
534 end if;
535
536 if (RTRIM(LTRIM(p_in_data_rec.withheld_amount)) is null) then
537 p_out_mesg := p_out_mesg || l_temp_prefix || 'Withheld Amount is NULL';
538 end if;
539
540 if (RTRIM(LTRIM(p_in_data_rec.income_code_sub_type)) is null) then
541 p_out_mesg := p_out_mesg || l_temp_prefix ||
542 'Income Code Sub Type is NULL';
543 end if;
544 if (RTRIM(LTRIM(p_in_data_rec.country_code)) is null) then
545 p_out_mesg := p_out_mesg || l_temp_prefix || 'Country Code is NULL';
546 end if;
547
548 if (RTRIM(LTRIM(p_in_data_rec.tax_year)) is null) then
549 p_out_mesg := p_out_mesg || l_temp_prefix || 'Tax Year is NULL';
550 end if;
551
552 if (RTRIM(LTRIM(p_in_data_rec.state_withheld_amount)) is null) then
553 p_out_mesg := p_out_mesg || l_temp_prefix ||
554 'State Withheld Amount is NULL';
555 end if;
556
557 if (RTRIM(LTRIM(p_in_data_rec.state_code)) is null) then
558 p_out_mesg := p_out_mesg || l_temp_prefix || 'State Code is NULL';
559 end if;
560
561 if (RTRIM(LTRIM(p_in_data_rec.payment_type)) is null) then
562 p_out_mesg := p_out_mesg || l_temp_prefix || 'Payment Type is NULL';
563 end if;
564
565 if (RTRIM(LTRIM(p_in_data_rec.record_status)) is null) then
566 p_out_mesg := p_out_mesg || l_temp_prefix || 'Record Status is NULL';
567 end if;
568
569 --
570 -- commented the following by skutteti. Even though there is a record in
571 -- Analyzed alien data/details table, it does not mean that the payroll
572 -- has been run for the person. Hence the last date of earnings and
573 -- cycle date might be null. Since for forecasted it is null, temporarily
574 -- commenting it
575 --
576 --IF (p_forecasted = FALSE) THEN
577 -- IF (RTRIM(LTRIM(p_in_data_rec.last_date_of_earnings)) IS NULL) THEN
578 -- p_out_mesg := p_out_mesg || l_temp_prefix ||
579 -- 'Last date of earnings is NULL';
580 -- END IF;
581 -- IF (RTRIM(LTRIM(p_in_data_rec.cycle_date)) IS NULL) THEN
582 -- p_out_mesg := p_out_mesg || l_temp_prefix || 'Cycle Date is NULL';
583 -- END IF;
584 --END IF;
585
586 if hr_api.not_exists_in_hr_lookups
587 (p_lookup_type => 'PER_US_INCOME_TYPES'
588 ,p_lookup_code => p_in_data_rec.income_code ||
589 p_in_data_rec.income_code_sub_type
590 ,p_effective_date => p_effective_date) then
591
592 hr_utility.set_location(l_proc, 6);
593
594 p_out_mesg := p_out_mesg || l_temp_prefix ||
595 'Invalid combination of Income code and scholarship code :'
596 || p_in_data_rec.income_code ||
597 p_in_data_rec.income_code_sub_type;
598 end if;
599
600 if (p_out_mesg = 'ERROR ==> ') then
601 p_out_mesg := null;
602 end if;
603
604 hr_utility.set_location('Leaving:'||l_proc, 10);
605 exception
606 when OTHERS then
607 hr_utility.set_location('Entering excep:'||l_proc, 15);
608 p_out_mesg := SUBSTR(p_out_mesg || TO_CHAR(SQLCODE) || SQLERRM, 1, 240) ;
609
610 end PQP_Windstar_Balance_Validate;
611
612 -- ===========================================================================
613 -- Name : PQP_Process_Events_ErrorLog
614 -- Purpose : the following procedure is called from pqp_windstar_person_read.
615 -- This inserts a record in pay_process_events table
616 -- with DATA_VALIDATION_FAILED status. A record is created only
617 -- if a record for an assignment does not already exist
618 -- Arguments :
619 -- In
620 -- p_assignment_id1 : Assignment Id
621 -- p_effective_date1 : Effective date.
622 -- p_change_type1 : source type (Windstar)
623 -- p_status1 : DATA_VALIDATION_FAILED
624 -- p_description1 : Description of the error
625 -- Out NoCopy : none
626 -- Notes : private
627 -- ===========================================================================
628 procedure PQP_Process_Events_ErrorLog
629 (p_assignment_id1 in per_assignments_f.assignment_id%type
630 ,p_effective_date1 in date
631 ,p_change_type1 in pay_process_events.change_type%type
632 ,p_status1 in pay_process_events.status%type
633 ,p_description1 in pay_process_events.description%type
634 ) is
635 --
636 l_process_event_id pay_process_events.process_event_id%type;
637 l_object_version_number pay_process_events.object_version_number%type;
638 l_proc varchar2(72) := g_package||'PQP_Process_Events_ErrorLog';
639
640 begin
641 --
642 -- The procedure pqp_process_events_errorlog creates a record in pay_process_events
643 -- table, if a record for an assignment does not already exist
644 --
645 hr_utility.set_location('Entering:'||l_proc, 5);
646
647 --
648 -- The following procedure pay_ppe_api.create_process_event creates a record
649 -- in the pay_process_events table
650 --
651 pay_ppe_api.create_process_event
652 (p_validate => false
653 ,p_assignment_id => p_assignment_id1
654 ,p_effective_date => p_effective_date1
655 ,p_change_type => p_change_type1
656 ,p_status => p_status1
657 ,p_description => SUBSTR(p_description1, 1, 240)
658 ,p_process_event_id => l_process_event_id
659 ,p_object_version_number => l_object_version_number
660 );
661 hr_utility.set_location('Leaving:'||l_proc, 10);
662 exception
663 when OTHERS then
664 hr_utility.set_location('Entering exception:'||l_proc, 15);
665 hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
666 hr_utility.set_message_token('2', 'Error in '
667 || 'pqp_alien_expat_taxation_pkg.pqp_process_events_errorlog(create). Error '
668 || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
669 hr_utility.raise_error;
670
671 end PQP_Process_Events_ErrorLog;
672
673 -- ===========================================================================
674 -- Name : PQP_Process_Events_ErrorLog
675 --Purpose : the following procedure is called from pqp_windstar_person_read.
676 -- This updates a record in pay_process_events table
677 -- with DATA_VALIDATION_FAILED status.
678 --Arguments :
679 -- In
680 -- t_people_tab : PL/sql table contains the Personal details.
681 -- This is passed a an I/P parameter as this
682 -- procedure returns the visa details only
683 -- for the assignments present in this
684 -- table.
685 -- p_effective_date : Effective date.
686 -- Out
687 -- Arguments :
688 -- In
689 -- p_process_event_id1 : Process Event Id for the PK purpose
690 -- p_object_version_number1 : Object version number for the PK purpose
691 -- p_status1 : DATA_VALIDATION_FAILED
692 -- p_description1 : Description of the error
693 -- Out NoCopy : none
694 -- Notes : private
695 -- ===========================================================================
696 procedure pqp_process_events_errorlog
697 (p_process_event_id1 in pay_process_events.process_event_id%type
698 ,p_object_version_number1 in pay_process_events.object_version_number%type
699 ,p_status1 in pay_process_events.status%type
700 ,p_description1 in pay_process_events.description%type
701 ) is
702 --
703 -- the procedure pqp_process_events_errorlog updates a record in
704 -- pay_process_events table. the following procedure
705 -- pay_ppe_api.update_process_event updates a record
706 -- in the pay_process_events table
707 --
708
709 l_object_version_number pay_process_events.object_version_number%type;
710 l_proc varchar2(72) := g_package||'PQP_Process_Events_ErrorLog';
711
712 begin
713
714 hr_utility.set_location('Entering:'||l_proc, 5);
715
716 l_object_version_number := p_object_version_number1;
717
718 pay_ppe_api.update_process_event
719 (p_validate => false
720 ,p_status => p_status1
721 ,p_description => substr(p_description1, 1, 240)
722 ,p_process_event_id => p_process_event_id1
723 ,p_object_version_number => l_object_version_number
724 );
725
726 hr_utility.set_location('Leaving:'||l_proc, 10);
727
728 exception
729 when others then
730 hr_utility.set_location('Entering exception:'||l_proc, 15);
731 hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
732 hr_utility.set_message_token('2', 'Error in '
733 || 'pqp_alien_expat_taxation_pkg.pqp_process_events_errorlog(Update). Error '
734 || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
735 hr_utility.raise_error;
736
737 end PQP_Process_Events_ErrorLog;
738
739 -- ===========================================================================
740 -- Name : Insert_Pay_Process_Events
741 -- Purpose : The following procedure is called from pqp_windstar_person_read.
742 -- This inserts a record in pay_process_events table.
743 -- Arguments :
744 -- In
745 -- p_type 'ALL' or a valid SSN
746 -- p_effective_date Effective Date
747 --
748 -- Out NoCopy: NONE
749 --
750 -- Notes : Private
751 -- ===========================================================================
752
753 procedure Insert_Pay_Process_Events
754 (p_type in varchar2
755 ,p_effective_date in date) is
756
757 --
758 -- The following cursor gets executed when the p_type is ALL. It selects
759 -- all assignments that are active in the calendar year of the effective date.
760 --
761 cursor all_people_f_cursor_n (c_start_date in date
762 ,c_end_date in date
763 ,c_national_indentifier in varchar2
764 ,c_effective_date in date) is
765 select paf.assignment_id
766 ,paf.effective_start_date
767 from per_people_f ppf
768 ,per_person_types ppt
769 ,per_people_extra_info pei
770 ,per_all_assignments_f paf
771 where ppf.person_type_id = ppt.person_type_id
772 and ppf.business_group_id = ppt.business_group_id
773 and ppt.system_person_type in ('EMP', 'EX_EMP', 'EMP_APL')
774 --
775 and pei.person_id = ppf.person_id
776 and paf.assignment_type <> 'B' --RLN
777 and pei.information_type = 'PER_US_ADDITIONAL_DETAILS'
778 and pei.pei_information12 = 'WINDSTAR'
779 and to_char(c_effective_date, 'YYYY') <=
780 to_char(nvl(fnd_date.canonical_to_date(pei.pei_information13)
781 ,to_date('31/12/4712','DD/MM/YYYY')
782 ),'YYYY'
783 )
784 --
785 and paf.person_id = ppf.person_id
786 and paf.business_group_id = ppf.business_group_id
787 and paf.effective_end_date between ppf.effective_start_date
788 and ppf.effective_end_date
789 and ((c_end_date between paf.effective_start_date
790 and paf.effective_end_date
791 )
792 or
793 (paf.effective_end_date =
794 (select max(asx.effective_end_date)
795 from per_all_assignments_f asx
796 where asx.assignment_id = paf.assignment_id
797 and asx.effective_end_date between c_start_date
798 and c_end_date)
799 )
800 )
801 and not exists (select 1
802 from pay_process_events
803 where assignment_id = paf.assignment_id
804 and change_type = 'PQP_US_ALIEN_WINDSTAR'
805 and status in ('N', 'D')
806
807 )
808 order by paf.assignment_id desc;
809
810 /* CURSOR all_people_f_cursor_n IS
811 select paf.assignment_id ,
812 paf.effective_start_date
813 from per_people_f ppf ,
814 per_person_types ppt ,
815 per_people_extra_info ppei,
816 per_assignments_f paf
817 where ppf.person_type_id = ppt.person_type_id
818 and ppt.system_person_type in ('EMP' , 'EX_EMP')
819 and ppf.effective_start_date <=
820 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
821 and ppf.effective_end_date >=
822 TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
823 and ppf.effective_start_date = (select MAX(effective_start_date)
824 from per_people_f
825 where person_id =
826 ppf.person_id
827 and effective_start_date <=
828 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
829 and ppf.person_id = ppei.person_id
830 and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
831 and ppei.pei_information12 = 'WINDSTAR'
832 and TO_CHAR(p_effective_date, 'YYYY') <=
833 TO_CHAR(NVL(fnd_date.canonical_to_date(ppei.pei_information13),
834 TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
835 and paf.person_id = ppf.person_id
836 and paf.effective_start_date <= TO_DATE(('12/31/' ||
837 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
838 and paf.effective_end_date >= TO_DATE(('01/01/' ||
839 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
840 and paf.effective_start_date = (select MAX(effective_start_date)
841 from per_assignments_f
842 where assignment_id =
843 paf.assignment_id
844 and effective_start_date <=
845 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
846 and not exists (select 1
847 from pay_process_events
848 where assignment_id = paf.assignment_id
849 and status in ('N', 'D')
850 and change_type = 'PQP_US_ALIEN_WINDSTAR'
851 )
852 order by paf.assignment_id desc ;
853 */
854 --
855 -- The following cursor gets executed when the p_type is ALL. It selects
856 -- all assignments that are active in the calendar year of the effective date.
857 --
858 cursor all_people_f_cursor_d (c_start_date in date
859 ,c_end_date in date
860 ,c_national_indentifier in varchar2
861 ,c_effective_date in date) is
862 select paf.assignment_id
863 ,paf.effective_start_date
864 ,ppe.process_event_id
865 ,ppe.object_version_number
866
867 from per_people_f ppf
868 ,per_person_types ppt
869 ,per_people_extra_info pei
870 ,pay_process_events ppe
871 ,per_all_assignments_f paf
872
873 where ppt.person_type_id = ppf.person_type_id
874 and paf.assignment_type <> 'B' --RLN
875 and ppt.business_group_id = ppf.business_group_id
876 and ppt.system_person_type in ('EMP', 'EX_EMP', 'EMP_APL')
877 --
878 and ppe.assignment_id = paf.assignment_id
879 and ppe.change_type = 'PQP_US_ALIEN_WINDSTAR'
880 and ppe.status in ('D')
881 -- only if person EIT exists
882 and pei.person_id = ppf.person_id
883 and pei.information_type = 'PER_US_ADDITIONAL_DETAILS'
884 and pei.pei_information12 = 'WINDSTAR'
885 and to_char(c_effective_date, 'YYYY') <=
886 to_char(nvl(fnd_date.canonical_to_date(pei_information13)
887 ,to_date('31/12/4712','DD/MM/YYYY')
888 ),'YYYY'
889 )
890 --
891 and paf.person_id = ppf.person_id
892 and paf.business_group_id = ppf.business_group_id
893 and paf.effective_end_date between ppf.effective_start_date
894 and ppf.effective_end_date
895 and ((c_end_date between paf.effective_start_date
896 and paf.effective_end_date
897 )
898 or
899 (paf.effective_end_date =
900 (select max(asx.effective_end_date)
901 from per_all_assignments_f asx
902 where asx.assignment_id = paf.assignment_id
903 and asx.effective_end_date between c_start_date
904 and c_end_date)
905 )
906 )
907
908 --
909 order by paf.assignment_id desc;
910
911 /* CURSOR all_people_f_cursor_d IS
912 select paf.assignment_id ,
913 paf.effective_start_date ,
914 ppe.process_event_id ,
915 ppe.object_version_number
916 from per_people_f ppf ,
917 per_person_types ppt ,
918 per_people_extra_info ppei,
919 pay_process_events ppe ,
920 per_assignments_f paf
921 where ppf.person_type_id = ppt.person_type_id
922 and ppt.system_person_type in ('EMP' , 'EX_EMP')
923 and ppf.effective_start_date <=
924 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
925 and ppf.effective_end_date >=
926 TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
927 and ppf.effective_start_date = (select MAX(effective_start_date)
928 from per_people_f
929 where person_id =
930 ppf.person_id
931 and effective_start_date <=
932 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
933 and ppf.person_id = ppei.person_id
934 and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
935 and ppei.pei_information12 = 'WINDSTAR'
936 and TO_CHAR(p_effective_date, 'YYYY') <=
937 TO_CHAR(NVL(fnd_date.canonical_to_date(ppei.pei_information13),
938 TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
939 and paf.effective_start_date <= TO_DATE(('12/31/' ||
940 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
941 and paf.effective_end_date >= TO_DATE(('01/01/' ||
942 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
943 and paf.effective_start_date = (select MAX(effective_start_date)
944 from per_assignments_f
945 where assignment_id =
946 paf.assignment_id
947 and effective_start_date <=
948 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
949 and paf.person_id = ppf.person_id
950 and paf.assignment_id = ppe.assignment_id
951 and exists (select 1
952 from pay_process_events
953 where assignment_id = paf.assignment_id
954 and status in ('D')
955 and change_type = 'PQP_US_ALIEN_WINDSTAR'
956 )
957 order by paf.assignment_id desc; */
958
959 --
960 -- Cursor when national identifier is passed and no pay process events exists
961 --
962 cursor ssn_cursor_n (c_start_date in date
963 ,c_end_date in date
964 ,c_national_indentifier in varchar2
965 ,c_effective_date in date) is
966 select paf.assignment_id
967 ,paf.effective_start_date
968
969 from per_all_assignments_f paf
970 ,per_people_f ppf
971 ,per_person_types ppt
972
973 where ppf.person_id = paf.person_id
974 and paf.assignment_type <> 'B' --RLN
975 and ppf.person_type_id = ppt.person_type_id
976 and ppf.national_identifier = c_national_indentifier
977 and ppt.system_person_type in ('EMP', 'EX_EMP')
978 and ((c_end_date between paf.effective_start_date
979 and paf.effective_end_date
980 )
981 or
982 (paf.effective_end_date =
983 (select max(asx.effective_end_date)
984 from per_all_assignments_f asx
985 where asx.assignment_id = paf.assignment_id
986 and asx.effective_end_date between c_start_date
987 and c_end_date)
988 )
989 )
990 and paf.effective_end_date between ppf.effective_start_date
991 and ppf.effective_end_date
992 and not exists (select 1
993 from pay_process_events
994 where assignment_id = paf.assignment_id
995 and status in ('N', 'D')
996 and change_type = 'PQP_US_ALIEN_WINDSTAR'
997 )
998 and exists
999 (select 1
1000 from per_people_extra_info pei
1001 where pei.information_type = 'PER_US_ADDITIONAL_DETAILS'
1002 and pei.pei_information12 = 'WINDSTAR'
1003 and pei.person_id = ppf.person_id
1004 and to_char(c_effective_date, 'YYYY') <=
1005 to_char(nvl(fnd_date.canonical_to_date(pei_information13)
1006 ,to_date('31/12/4712','DD/MM/YYYY')
1007 ),'YYYY'
1008 )
1009 )
1010 order by paf.assignment_id;
1011
1012 /* CURSOR ssn_cursor_n IS
1013 select paf.assignment_id ,
1014 paf.effective_start_date
1015 from per_assignments_f paf ,
1016 per_people_f ppf ,
1017 per_person_types ppt ,
1018 (select * from per_people_extra_info
1019 where information_type = 'PER_US_ADDITIONAL_DETAILS'
1020 and pei_information12 = 'WINDSTAR'
1021 and TO_CHAR(p_effective_date, 'YYYY') <=
1022 TO_CHAR(NVL(fnd_date.canonical_to_date(pei_information13),
1023 TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
1024 ) ppei
1025 where ppf.person_id = paf.person_id
1026 and ppf.person_type_id = ppt.person_type_id
1027 and ppf.national_identifier= p_type
1028 and ppt.system_person_type in ('EMP' , 'EX_EMP')
1029 and ppf.effective_start_date <=
1030 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1031 and ppf.effective_end_date >=
1032 TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1033 and ppf.effective_start_date = (select MAX(effective_start_date)
1034 from per_people_f
1035 where person_id =
1036 ppf.person_id
1037 and effective_start_date <=
1038 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1039 and paf.effective_start_date <= TO_DATE(('12/31/' ||
1040 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1041 and paf.effective_end_date >= TO_DATE(('01/01/' ||
1042 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1043 and paf.effective_start_date = (select MAX(effective_start_date)
1044 from per_assignments_f
1045 where assignment_id =
1046 paf.assignment_id
1047 and effective_start_date <=
1048 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1049 and ppf.person_type_id = ppt.person_type_id
1050 and ppf.person_id = ppei.person_id
1051 and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
1052 and ppei.pei_information12 = 'WINDSTAR'
1053 and not exists (select 1
1054 from pay_process_events
1055 where assignment_id = paf.assignment_id
1056 and status in ('N', 'D')
1057 and change_type = 'PQP_US_ALIEN_WINDSTAR'
1058 )
1059 order by paf.assignment_id ;
1060 */
1061 --
1062 -- Cursor when national identifier is passed and with pay process events
1063 --
1064 cursor ssn_cursor_d (c_start_date in date
1065 ,c_end_date in date
1066 ,c_national_indentifier in varchar2
1067 ,c_effective_date in date) is
1068 select paf.assignment_id
1069 ,paf.effective_start_date
1070 ,ppe.process_event_id
1071 ,ppe.object_version_number
1072
1073 from per_all_assignments_f paf
1074 ,per_people_f ppf
1075 ,per_person_types ppt
1076 ,pay_process_events ppe
1077
1078 where ppf.person_id = paf.person_id
1079 and ppf.person_type_id = ppt.person_type_id
1080 and paf.assignment_type <> 'B' --RLN
1081 and ppf.business_group_id = ppt.business_group_id
1082 and ppf.national_identifier = c_national_indentifier
1083 and ppt.system_person_type in ('EMP', 'EX_EMP')
1084 and ((c_end_date between paf.effective_start_date
1085 and paf.effective_end_date
1086 )
1087 or
1088 (paf.effective_end_date =
1089 (select max(asx.effective_end_date)
1090 from per_all_assignments_f asx
1091 where asx.assignment_id = paf.assignment_id
1092 and asx.business_group_id = paf.business_group_id
1093 and asx.person_id = paf.person_id
1094 and asx.effective_end_date between c_start_date
1095 and c_end_date)
1096 )
1097 )
1098 and paf.effective_end_date between ppf.effective_start_date
1099 and ppf.effective_end_date
1100 and paf.business_group_id = ppf.business_group_id
1101 and ppe.assignment_id = paf.assignment_id
1102 and ppe.status in ('D')
1103 and ppe.change_type = 'PQP_US_ALIEN_WINDSTAR'
1104 and exists (select 1
1105 from per_people_extra_info pei
1106 where pei.information_type = 'PER_US_ADDITIONAL_DETAILS'
1107 and pei.pei_information12 = 'WINDSTAR'
1108 and pei.person_id = ppf.person_id
1109 and to_char(c_effective_date, 'YYYY') <=
1110 to_char(nvl(fnd_date.canonical_to_date(pei_information13)
1111 ,to_date('31/12/4712','DD/MM/YYYY')
1112 ),'YYYY'
1113 )
1114 )
1115 order by paf.assignment_id;
1116
1117 /* CURSOR ssn_cursor_d IS
1118 select paf.assignment_id,
1119 paf.effective_start_date,
1120 ppe.process_event_id,
1121 ppe.object_version_number
1122
1123 from per_assignments_f paf ,
1124 per_people_f ppf ,
1125 per_person_types ppt ,
1126 (select * from per_people_extra_info
1127 where information_type = 'PER_US_ADDITIONAL_DETAILS'
1128 and pei_information12 = 'WINDSTAR'
1129 and TO_CHAR(p_effective_date, 'YYYY') <=
1130 TO_CHAR(NVL(fnd_date.canonical_to_date(pei_information13),
1131 TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
1132 ) ppei ,
1133 pay_process_events ppe
1134 where ppf.person_id = paf.person_id
1135 and ppf.person_type_id = ppt.person_type_id
1136 and ppf.national_identifier= p_type
1137 and ppt.system_person_type in ('EMP' , 'EX_EMP')
1138 and ppf.effective_start_date <=
1139 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1140 and ppf.effective_end_date >=
1141 TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1142 and ppf.effective_start_date = (select MAX(effective_start_date)
1143 from per_people_f
1144 where person_id =
1145 ppf.person_id
1146 and effective_start_date <=
1147 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1148 and paf.effective_start_date <= TO_DATE(('12/31/' ||
1149 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1150 and paf.effective_end_date >= TO_DATE(('01/01/' ||
1151 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1152 and paf.effective_start_date = (select MAX(effective_start_date)
1153 from per_assignments_f
1154 where assignment_id =
1155 paf.assignment_id
1156 and effective_start_date <=
1157 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1158
1159 and ppf.person_id = ppei.person_id
1160 and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
1161 and ppei.pei_information12 = 'WINDSTAR'
1162 and paf.assignment_id = ppe.assignment_id
1163 and exists (select 1
1164 from pay_process_events
1165 where assignment_id = paf.assignment_id
1166 and status in ('D')
1167 and change_type = 'PQP_US_ALIEN_WINDSTAR'
1168 )
1169 order by paf.assignment_id ;
1170 */
1171 l_process_event_id number;
1172 l_object_version_number number;
1173 l_assignment_id number;
1174 l_start_date date;
1175 l_end_date date;
1176
1177 l_proc constant varchar2(150) := g_package||'Insert_Pay_Process_Events';
1178
1179 begin
1180 hr_utility.set_location('Entering:'||l_proc, 5);
1181 --
1182 -- Get the start and end date of year for the effective date passed.
1183 --
1184 l_start_date
1185 := to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY');
1186 l_end_date
1187 := to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY');
1188
1189 if p_type = 'ALL' then
1190
1191 hr_utility.set_location(l_proc, 10);
1192 --
1193 for apfc in all_people_f_cursor_n
1194 (c_start_date => l_start_date
1195 ,c_end_date => l_end_date
1196 ,c_national_indentifier => p_type
1197 ,c_effective_date => p_effective_date)
1198
1199 loop
1200
1201 begin
1202 hr_utility.set_location(l_proc, 20);
1203 pay_ppe_api.create_process_event
1204 (p_validate => false
1205 ,p_assignment_id => apfc.assignment_id
1206 ,p_effective_date => apfc.effective_start_date
1207 ,p_change_type => 'PQP_US_ALIEN_WINDSTAR'
1208 ,p_status => 'N'
1209 ,p_description => '| Inserted thru PL/SQL Code |'
1210 ,p_process_event_id => l_process_event_id
1211 ,p_object_version_number => l_object_version_number
1212 );
1213 hr_utility.set_location(l_proc, 30);
1214 exception
1215 when others then
1216 hr_utility.set_location(l_proc, 40);
1217 null;
1218 end;
1219
1220 end loop;
1221 --
1222 --
1223 for apfc in all_people_f_cursor_d
1224 (c_start_date => l_start_date
1225 ,c_end_date => l_end_date
1226 ,c_national_indentifier => p_type
1227 ,c_effective_date => p_effective_date)
1228 loop
1229
1230 begin
1231 hr_utility.set_location(l_proc, 50);
1232 pay_ppe_api.update_process_event
1233 (p_validate => false
1234 ,p_status => 'N'
1235 ,p_description => null
1236 ,p_process_event_id => apfc.process_event_id
1237 ,p_object_version_number => apfc.object_version_number
1238 );
1239 hr_utility.set_location(l_proc, 60);
1240 exception
1241 when others then
1242 hr_utility.set_location(l_proc, 70);
1243 null;
1244 end;
1245
1246 end loop;
1247 else
1248 hr_utility.set_location(l_proc, 80);
1249 --
1250 for c1 in ssn_cursor_n (c_start_date => l_start_date
1251 ,c_end_date => l_end_date
1252 ,c_national_indentifier => p_type
1253 ,c_effective_date => p_effective_date)
1254 loop
1255
1256 begin
1257 hr_utility.set_location(l_proc, 90);
1258 pay_ppe_api.create_process_event
1259 (p_validate => false
1260 ,p_assignment_id => c1.assignment_id
1261 ,p_effective_date => c1.effective_start_date
1262 ,p_change_type => 'PQP_US_ALIEN_WINDSTAR'
1263 ,p_status => 'N'
1264 ,p_description => '| Inserted thru PL/SQL Code |'
1265 ,p_process_event_id => l_process_event_id
1266 ,p_object_version_number => l_object_version_number
1267 );
1268 hr_utility.set_location(l_proc, 100);
1269 exception
1270 when others then
1271 hr_utility.set_location(l_proc, 110);
1272 null;
1273 end;
1274
1275 end loop;
1276 --
1277 --
1278 for c1 in ssn_cursor_d (c_start_date => l_start_date
1279 ,c_end_date => l_end_date
1280 ,c_national_indentifier => p_type
1281 ,c_effective_date => p_effective_date)
1282 loop
1283
1284 begin
1285 hr_utility.set_location(l_proc, 120);
1286 pay_ppe_api.update_process_event
1287 (p_validate => false
1288 ,p_status => 'N'
1289 ,p_description => null
1290 ,p_process_event_id => c1.process_event_id
1291 ,p_object_version_number => c1.object_version_number
1292 );
1293 hr_utility.set_location(l_proc, 130);
1294 exception
1295 when others then
1296 hr_utility.set_location(l_proc, 140);
1297 null;
1298 end;
1299
1300 end loop;
1301 --
1302 end if;
1303
1304 hr_utility.set_location('Leaving:'||l_proc, 150);
1305
1306 exception
1307 when others then
1308 hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
1309 hr_utility.set_message_token('2',
1310 'Error in pqp_alien_expat_taxation_pkg.insert_pay_process_'||
1311 'events. Error Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
1312 hr_utility.set_location('Leaving :'||l_proc, 160);
1313 hr_utility.raise_error;
1314
1315 end insert_pay_process_events;
1316
1317 /**************************************************************************
1318 name : address_select
1319 Purpose : the following procedure is called from pqp_windstar_person_read.
1320 This selects the address of an assignment.
1321 Arguments :
1322 in
1323 p_per_assign_id : Person or Assignment Id.
1324 Person Id if home address is
1325 needed. Assignment Id if work
1326 address is needed.
1327
1328 p_effective_date : Effective date
1329 in/out
1330 p_work_home : Flag to select Home or work
1331 address. if it is HOME, then
1332 home address is selected. if it
1333 is work then work address is
1334 selected.
1335 out
1336 p_county : County
1337 p_state : State
1338 p_city : City
1339 p_address_line1 : Address Line 1
1340 p_address_line2 : Address Line 2
1341 p_address_line3 : Address Line 3
1342 p_telephone_number_1: Tel Phone 1
1343 p_telephone_number_2: Tel Phone 2
1344 p_telephone_number_3: Tel Phone 3
1345 p_postal_code : Postal Code
1346 Notes : private
1347 ***************************************************************************/
1348 procedure address_select(p_per_assign_id in number ,
1349 p_effective_date in date ,
1350 p_work_home in out NOCOPY varchar2 ,
1351 p_county out NOCOPY varchar2 ,
1352 p_state out NOCOPY varchar2 ,
1353 p_city out NOCOPY varchar2 ,
1354 p_address_line1 out NOCOPY varchar2 ,
1355 p_address_line2 out NOCOPY varchar2 ,
1356 p_address_line3 out NOCOPY varchar2 ,
1357 p_telephone_number_1 out NOCOPY varchar2 ,
1358 p_telephone_number_2 out NOCOPY varchar2 ,
1359 p_telephone_number_3 out NOCOPY varchar2 ,
1360 p_postal_code out NOCOPY varchar2 )
1361 is --{
1362 /*****
1363 This procedure selects HOME/work the address of an assignment
1364 *****/
1365 /*****
1366 the following cursor selects the details of the home address
1367
1368 08-JAN-04 Bug #3347853 Fix latest addrress is send now instead of
1369 the address as of the interface date.
1370
1371 MAX(date_from) is now being equated instead of less then equal to.
1372 *****/
1373 cursor home_address_cur is
1374 select NVL(addr.add_information19 , addr.region_1 ) county ,
1375 NVL(addr.add_information17 , addr.region_2 ) state ,
1376 NVL(addr.add_information18 , addr.town_or_city) city ,
1377 NVL(addr.address_line1 , ' ' ) address_line1 ,
1378 NVL(addr.address_line2 , ' ' ) address_line2 ,
1379 NVL(addr.address_line3 , ' ' ) address_line3 ,
1380 NVL(addr.telephone_number_1, ' ' ) telephone_number_1 ,
1381 NVL(addr.telephone_number_2, ' ' ) telephone_number_2 ,
1382 NVL(addr.telephone_number_3, ' ' ) telephone_number_3 ,
1383 NVL(addr.postal_code , ' ' ) postal_code
1384 from per_addresses addr
1385 where addr.person_id = p_per_assign_id
1386 and addr.primary_flag = 'Y'
1387 and NVL(addr.address_type,' ') <> 'PHCA'
1388 and addr.date_from = (select MAX(date_From)
1389 from per_addresses
1390 where person_id = p_per_assign_id
1391 and primary_flag = 'Y'
1392 and NVL(address_type,' ') <> 'PHCA');
1393
1394 /*****
1395 the following cursor selects the details of the work address
1396 *****/
1397 cursor work_address_cur is
1398 select NVL(hrlock.loc_information19 , hrlock.region_1) county ,
1399 NVL(hrlock.loc_information17 , hrlock.region_2) state ,
1400 NVL(hrlock.loc_information18 , hrlock.town_or_city) city ,
1401 NVL(hrlock.address_line_1 , ' ' ) address_line_1 ,
1402 NVL(hrlock.address_line_2 , ' ' ) address_line_2 ,
1403 NVL(hrlock.address_line_3 , ' ' ) address_line_3 ,
1404 NVL(hrlock.telephone_number_1 , ' ' ) telephone_number_1,
1405 NVL(hrlock.telephone_number_2 , ' ' ) telephone_number_2,
1406 NVL(hrlock.telephone_number_3 , ' ' ) telephone_number_3,
1407 NVL(hrlock.postal_code , ' ' ) postal_code
1408 from hr_locations hrlock,
1409 hr_soft_coding_keyflex hrsckf,
1410 per_all_assignments_f assign
1411 where p_effective_date between assign.effective_start_date
1412 and assign.effective_end_date
1413 and assign.assignment_id = p_per_assign_id
1414 and assign.soft_coding_keyflex_id = hrsckf.soft_coding_keyflex_id
1415 and NVL(hrsckf.segment18,assign.location_id) = hrlock.location_id;
1416
1417 l_proc varchar2(72) := g_package||'address_select' ;
1418 begin --}{
1419 hr_utility.set_location('Entering:'||l_proc, 10);
1420
1421 /* Person Address Details */
1422 if (UPPER(p_work_home) = 'HOME') then
1423 hr_utility.set_location(l_proc, 20);
1424 open home_address_cur;
1425 fetch home_address_cur into
1426 p_county ,
1427 p_state ,
1428 p_city ,
1429 p_address_line1 ,
1430 p_address_line2 ,
1431 p_address_line3 ,
1432 p_telephone_number_1 ,
1433 p_telephone_number_2 ,
1434 p_telephone_number_3 ,
1435 p_postal_code ;
1436 hr_utility.set_location('Entering:'||l_proc, 30);
1437 if (home_address_cur%notfound) then
1438 p_county := '' ;
1439 p_state := '' ;
1440 p_city := '' ;
1441 p_address_line1 := '' ;
1442 p_address_line2 := '' ;
1443 p_address_line3 := '' ;
1444 p_telephone_number_1 := '' ;
1445 p_telephone_number_2 := '' ;
1446 p_telephone_number_3 := '' ;
1447 p_postal_code := '' ;
1448 p_work_home := 'NOT_FOUND';
1449 hr_utility.set_location(l_proc, 40);
1450 end if;
1451 close home_address_cur;
1452 elsif (UPPER(p_work_home) = 'WORK') then
1453 hr_utility.set_location(l_proc, 50);
1454 open work_address_cur;
1455 fetch work_address_cur into
1456 p_county ,
1457 p_state ,
1458 p_city ,
1459 p_address_line1 ,
1460 p_address_line2 ,
1461 p_address_line3 ,
1462 p_telephone_number_1 ,
1463 p_telephone_number_2 ,
1464 p_telephone_number_3 ,
1465 p_postal_code ;
1466 hr_utility.set_location(l_proc, 60);
1467 if (work_address_cur%notfound) then
1468 p_county := '' ;
1469 p_state := '' ;
1470 p_city := '' ;
1471 p_address_line1 := '' ;
1472 p_address_line2 := '' ;
1473 p_address_line3 := '' ;
1474 p_telephone_number_1 := '' ;
1475 p_telephone_number_2 := '' ;
1476 p_telephone_number_3 := '' ;
1477 p_postal_code := '' ;
1478 p_work_home := 'NOT_FOUND' ;
1479 hr_utility.set_location(l_proc, 70);
1480 end if;
1481 close work_address_cur;
1482 end if;
1483 hr_utility.set_location('Leaving:'||l_proc, 80);
1484 exception
1485 when OTHERS then
1486 hr_utility.set_location('Entering exc:'||l_proc, 90);
1487 p_county := '' ;
1488 p_state := '' ;
1489 p_city := '' ;
1490 p_address_line1 := '' ;
1491 p_address_line2 := '' ;
1492 p_address_line3 := '' ;
1493 p_telephone_number_1 := '' ;
1494 p_telephone_number_2 := '' ;
1495 p_telephone_number_3 := '' ;
1496 p_postal_code := '' ;
1497 p_work_home := 'NOT_FOUND' ;
1498 end address_select;
1499
1500 -- Function to format the telephone no.'s as required by Windstar before
1501 -- Interfacing.
1502 -- Added by tmehra - 09-APR-2002
1503
1504
1505 function format_tele
1506 ( p_tele in varchar2 --
1507 ) return varchar2 -- Return Formated value
1508 is
1509
1510 l_value hr_locations.telephone_number_1%type;
1511 l_len number := 0;
1512 l_sep_pos number := 0;
1513 l_pre hr_locations.telephone_number_1%type;
1514 l_char varchar2(1);
1515
1516 begin
1517 -- Strip the blanks
1518 l_value := trim(p_tele);
1519 l_pre := '';
1520 l_len := nvl(length(l_value),0);
1521 l_char := ' ';
1522
1523 if l_len = 0 then
1524 return ' ';
1525 end if;
1526
1527 for i in 1 .. l_len
1528 loop
1529 l_char := substr(l_value,i,1);
1530 l_sep_pos := instr('0123456789',l_char);
1531 if l_sep_pos <> 0 then
1532 l_pre := l_pre || l_char;
1533 end if;
1534 end loop;
1535
1536 return l_pre;
1537
1538 end;
1539 /**************************************************************************
1540 name : spouse_here
1541 Purpose : the following procedure is called from pqp_windstar_person_read.
1542 This returns Y/N depending on the condition whether the spouse
1543 of the person accompanied her.
1544 Arguments :
1545 in p_person_id : Person Id
1546
1547 p_effective_date : Effective date
1548 out NOCOPY Y/N
1549 Notes : private
1550 ***************************************************************************/
1551 function spouse_here(p_person_id in number ,
1552 p_effective_date in date ) return varchar2
1553 is
1554 l_spouse_here varchar2(1) := 'N';
1555 l_proc varchar2(72) := g_package||'spouse_here';
1556 begin -- {
1557 hr_utility.set_location('Entering '||l_proc, 10);
1558 for csh1 in c_person_visit_spouse_info(p_person_id ,
1559 p_effective_date)
1560
1561 loop
1562 l_spouse_here := csh1.spouse_accompanied;
1563 hr_utility.set_location('Leaving '||l_proc, 20);
1564 end loop;
1565 hr_utility.set_location('Leaving '||l_proc, 30);
1566 return l_spouse_here;
1567 end spouse_here;
1568 -- =============================================================================
1569 -- name : PQP_Windstar_Person_Read
1570 -- Purpose: The following procedure is called from pqp_read_public. This
1571 -- procedure returns the person details in a PL/sql table t_people_tab.
1572 -- Arguments :
1573 -- IN
1574 -- p_selection_criterion : if the user wants to select all records,
1575 -- or the records in the PAY_PROCESS_EVENTS table,
1576 -- or a specifice national_identifier.
1577 -- p_source_type : if the req is from Windstar or some other sys.
1578 -- p_effective_date : Effective date.
1579 -- Out
1580 -- t_people_tab : PL/sql table contains the Personal details.
1581 -- In Out
1582 -- t_error_tab : PL/sql table contains the Error details.
1583 --
1584 -- Notes : private
1585 -- =============================================================================
1586
1587 procedure PQP_Windstar_Person_Read
1588 (p_selection_criterion in varchar2
1589 ,p_source_type in varchar2
1590 ,p_effective_date in date
1591 ,t_people_tab out nocopy t_people_tab_type
1592 ,t_error_tab in out nocopy t_error_tab_type
1593 ,p_person_read_count out nocopy number
1594 ,p_person_err_count out nocopy number
1595 ) is
1596
1597 --
1598 -- The cursor selects all the assignment_id's from pay_process_events table
1599 -- that have a status of NOT_READ and then joins it with the per_people_f,
1600 -- and per_assignments_f table. This cursor can be coded without the parameter
1601 -- p_source_type, since the only user will be Windstar. But just to make the
1602 -- program flexible, p_source_type is used.
1603 -- 1. A status of 'N' means 'NOT_READ'
1604 -- 2. pei_information12 is process_type. It means that the person is an alien
1605 -- and has to be processed by WINDSTAR
1606 --
1607 cursor pay_process_events_cursor
1608 (c_year_start_date in date
1609 ,c_year_end_date in date
1610 ,p_source_type in varchar2) is
1611 select distinct
1612 ppf.last_name
1613 ,ppf.first_name
1614 ,ppf.middle_names
1615 ,ppf.national_identifier
1616 ,ppf.employee_number
1617 ,ppf.date_of_birth
1618 ,ppf.title
1619 ,ppf.suffix
1620 ,upper(ppf.marital_status)
1621 ,ppf.person_id
1622
1623 from per_all_assignments_f paf
1624 ,per_people_f ppf
1625 ,pay_process_events ppe
1626 ,per_person_types ppt
1627 ,per_people_extra_info pei
1628
1629 where ppf.person_id = paf.person_id
1630 and ppf.person_type_id = ppt.person_type_id
1631 and ppt.system_person_type in ('EMP', 'EX_EMP', 'EMP_APL')
1632 --
1633 and ppe.change_type = p_source_type
1634 and ppe.assignment_id = paf.assignment_id
1635 and ppe.status = 'N'
1636 -- Person extra Info
1637 and ppf.person_id = pei.person_id
1638 and pei.information_type = 'PER_US_ADDITIONAL_DETAILS'
1639 and pei.pei_information12 = 'WINDSTAR'
1640 and to_char(c_year_end_date, 'YYYY') <=
1641 to_char(nvl(fnd_date.canonical_to_date(pei.pei_information13),
1642 c_year_end_date),'YYYY')
1643 and ((c_year_end_date between paf.effective_start_date
1644 and paf.effective_end_date
1645 )
1646 or
1647 (paf.effective_end_date =
1648 (select max(asx.effective_end_date)
1649 from per_all_assignments_f asx
1650 where asx.assignment_id = paf.assignment_id
1651 and asx.business_group_id = paf.business_group_id
1652 and asx.person_id = paf.person_id
1653 and asx.effective_end_date between c_year_start_date
1654 and c_year_end_date)
1655 )
1656 )
1657 and ((c_year_end_date between ppf.effective_start_date
1658 and ppf.effective_end_date
1659 )
1660 or
1661 (paf.effective_end_date between ppf.effective_start_date
1662 and ppf.effective_end_date)
1663 )
1664 order by ppf.person_id;
1665
1666 /* cursor pay_process_events_cursor(p_effective_date in date ,
1667 p_source_type in varchar2) is
1668 select distinct
1669 ppf.last_name ,
1670 ppf.first_name ,
1671 ppf.middle_names ,
1672 ppf.national_identifier ,
1673 ppf.employee_number ,
1674 ppf.date_of_birth ,
1675 ppf.title ,
1676 ppf.suffix ,
1677 UPPER(ppf.marital_status),
1678 ppf.person_id
1679 from per_assignments_f paf ,
1680 per_people_f ppf ,
1681 pay_process_events ppe ,
1682 per_person_types ppt ,
1683 per_people_extra_info ppei
1684 where ppf.person_id = paf.person_id
1685 and ppf.effective_start_date <= TO_DATE(('12/31/' ||
1686 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1687 and ppf.effective_end_date >= TO_DATE(('01/01/' ||
1688 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1689 and ppf.effective_start_date = (select MAX(effective_start_date)
1690 from per_people_f
1691 where person_id = ppf.person_id
1692 and effective_start_date <=
1693 TO_DATE(('12/31/' ||
1694 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1695 and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
1696 and ppei.pei_information12 = 'WINDSTAR'
1697 and TO_CHAR(p_effective_date, 'YYYY') <=
1698 TO_CHAR(NVL(fnd_date.canonical_to_date(ppei.pei_information13),
1699 TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
1700
1701 and paf.effective_start_date <= TO_DATE(('12/31/' ||
1702 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1703 and paf.effective_end_date >= TO_DATE(('01/01/' ||
1704 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1705 and paf.effective_start_date = (select MAX(effective_start_date)
1706 from per_assignments_f
1707 where assignment_id =
1708 paf.assignment_id
1709 and effective_start_date <=
1710 TO_DATE(('12/31/' ||
1711 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1712 and ppf.person_type_id = ppt.person_type_id
1713 and ppt.system_person_type in ('EMP' , 'EX_EMP')
1714 and ppe.change_type = p_source_type
1715 and ppe.assignment_id = paf.assignment_id
1716 and ppe.status = 'N'
1717 and ppf.person_id = ppei.person_id
1718 order by ppf.person_id;
1719 */
1720
1721 --
1722 -- The cursor(written below) per_people_f_cursor selects the details of all the
1723 -- persons that are to be processed by Windstar. Basically pei_information12
1724 -- = 'WINDSTAR' tells that the particular person will be processed by Windstar
1725 --
1726
1727 cursor per_people_f_cursor(p_effective_date in date) is
1728 select ppf.last_name ,
1729 ppf.first_name ,
1730 ppf.middle_names ,
1731 ppf.national_identifier ,
1732 ppf.employee_number ,
1733 ppf.date_of_birth ,
1734 ppf.title ,
1735 ppf.suffix ,
1736 UPPER(ppf.marital_status) ,
1737 ppf.person_id
1738 from per_people_f ppf ,
1739 per_person_types ppt ,
1740 per_people_extra_info ppei
1741 where ppf.person_type_id = ppt.person_type_id
1742 and ppt.system_person_type in ('EMP', 'EX_EMP', 'EMP_APL')
1743 and ppf.effective_start_date <=
1744 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1745 and ppf.effective_end_date >=
1746 TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1747 and ppf.effective_start_date = (select MAX(effective_start_date)
1748 from per_people_f
1749 where person_id =
1750 ppf.person_id
1751 and effective_start_date <=
1752 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1753 and ppf.person_type_id = ppt.person_type_id
1754 and ppf.person_id = ppei.person_id
1755 and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
1756 and ppei.pei_information12 = 'WINDSTAR'
1757 and TO_CHAR(p_effective_date, 'YYYY') <=
1758 TO_CHAR(NVL(fnd_date.canonical_to_date(ppei.pei_information13),
1759 TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
1760 order by ppf.person_id ;
1761
1762 /*****
1763 the cursor(written below) national_identifier_cursor selects the details of
1764 a person with the passed national Identifier
1765 *****/
1766
1767 cursor national_identifier_cursor(p_effective_date in date ,
1768 p_national_identifier in varchar2) is
1769 select ppf.last_name ,
1770 ppf.first_name ,
1771 ppf.middle_names ,
1772 ppf.national_identifier ,
1773 ppf.employee_number ,
1774 ppf.date_of_birth ,
1775 ppf.title ,
1776 ppf.suffix ,
1777 UPPER(ppf.marital_status) ,
1778 ppf.person_id
1779 from per_people_f ppf ,
1780 per_person_types ppt ,
1781 (select * from per_people_extra_info
1782 where information_type = 'PER_US_ADDITIONAL_DETAILS'
1783 and pei_information12 = 'WINDSTAR'
1784 and TO_CHAR(p_effective_date, 'YYYY') <=
1785 TO_CHAR(NVL(fnd_date.canonical_to_date(pei_information13),
1786 TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
1787 ) ppei
1788 where ppf.person_type_id = ppt.person_type_id
1789 and ppt.system_person_type in ('EMP', 'EX_EMP', 'EMP_APL')
1790 and ppf.effective_start_date <=
1791 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1792 and ppf.effective_end_date >=
1793 TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1794 and ppf.effective_start_date = (select MAX(effective_start_date)
1795 from per_people_f
1796 where person_id =
1797 ppf.person_id
1798 and effective_start_date <=
1799 TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1800 and ppf.national_identifier = p_national_identifier
1801 and ppf.person_id = ppei.person_id
1802 and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
1803 and ppei.pei_information12 = 'WINDSTAR'
1804 order by ppf.person_id ;
1805
1806 /*****
1807 the cursor c_person_passport_info gives the passport details of a particular
1808 person id . rownum is used as we are interested in selecting just a single
1809 row.
1810 ****/
1811
1812 cursor c_person_passport_info(p_person_id in number ) is
1813 select ppei.pei_information5 country ,
1814 ppei.pei_information6 passport_number ,
1815 ppei.pei_information7 issue_date ,
1816 ppei.pei_information8 expiry_date
1817 from (select *
1818 from per_people_extra_info
1819 where information_type = 'PER_US_PASSPORT_DETAILS'
1820 and person_id = p_person_id) ppei
1821 where rownum < 2;
1822
1823 /*****
1824 the cursor c_lookup_values_cursor gives the count for a lookup_type
1825 and a country code. the lookup type used while invoking this cursor is
1826 PQP_US_DEPENDENTS_IN_USA. on the GUI, on person extra information
1827 'Additional Details', a user can enter value either in 'total dependents
1828 children' or 'dependent children in country'. If a row is present in
1829 fnd_common_lookups for lookup_type = PQP_US_DEPENDENTS_IN_USA and
1830 the respective country code, then the t_people_tab(i).dependents will
1831 be populated by value present in 'dependent children in country'.
1832
1833
1834 Bug 3780751 Fix - Changed the FND_COMMON_LOOKUP reference to hr_lookups
1835 by tmehra 23-dec-2004.
1836 ****/
1837
1838 cursor c_lookup_values_cursor(p_effective_date in date ,
1839 p_lookup_type in varchar2 ,
1840 p_country_code in varchar2 ) is
1841 select COUNT(*) count
1842 from hr_lookups
1843 where lookup_type = p_lookup_type
1844 and enabled_flag = 'Y'
1845 and NVL(end_date_active, p_effective_date) >= p_effective_date
1846 and lookup_code = p_country_code;
1847
1848 /*****
1849 the following cursor c_non_us_address_cur selects the Non US address for a
1850 person_id
1851 Added the code to fetch the complete non us address - tmehra 15-OCT-2001
1852 Added region_2 --> non_us_region_postal_cd - 05-APR-2002
1853
1854 08-JAN-04 Bug #3347853 Fix - foreign Address was not being passed if the primary address
1855 was updated and the update date was in the new year. A new clause to check for 'PHCA'
1856 has been added to the subquery.
1857 *****/
1858
1859 cursor c_non_us_address_cur(p_person_id in number ,
1860 p_effective_date in date ) is
1861 select NVL(addr.address_line1,' ') non_us_addr1,
1862 NVL(addr.address_line2,' ') non_us_addr2,
1863 NVL(addr.address_line3,' ') non_us_addr3,
1864 NVL(addr.postal_code,' ' ) non_us_city_postal_cd,
1865 NVL(addr.town_or_city,' ' ) non_us_city,
1866 NVL(addr.region_1,' ' ) non_us_region,
1867 NVL(addr.region_2,' ' ) non_us_region_postal_cd,
1868 NVL(addr.country, ' ' ) non_us_cc
1869 from per_addresses addr
1870 where addr.person_id = p_person_id
1871 and addr.address_type = 'PHCA'
1872 and addr.date_from = (select MAX(date_from)
1873 from per_addresses
1874 where person_id = p_person_id
1875 and address_type = 'PHCA'
1876 )
1877 and rownum < 2;
1878
1879 /* Original cursor
1880 cursor c_non_us_address_cur(p_person_id in number ,
1881 p_effective_date in date ) is
1882 select NVL(addr.country, ' ') non_us_cc
1883 from per_addresses addr
1884 where addr.person_id = p_person_id
1885 and addr.address_type = 'PHCA'
1886 and NVL(addr.date_from, p_effective_date) <= TO_DATE(('12/31/' ||
1887 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1888 and NVL(addr.date_to, p_effective_date) >= TO_DATE(('01/01/' ||
1889 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1890 and NVL(addr.date_from, p_effective_date) = (select MAX(date_from)
1891 from per_addresses
1892 where person_id = p_person_id
1893 and NVL(date_from, p_effective_date) <=
1894 TO_DATE(('12/31/' ||
1895 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1896 and rownum < 2;
1897 */
1898
1899
1900 /*****
1901 the cursor below gives the translation of Oracle Application Country codes to
1902 IRS country codes
1903 *****/
1904
1905 cursor c_country_code_xlat_cursor(p_country_code in varchar2 ,
1906 p_effective_date in date ) is
1907 select hrl.meaning
1908 from hr_lookups hrl
1909 where hrl.lookup_type = 'PQP_US_COUNTRY_TRANSLATE'
1910 and hrl.enabled_flag = 'Y'
1911 and NVL(start_date_active, p_effective_date) <= TO_DATE(('12/31/' ||
1912 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1913 and NVL(end_date_active, p_effective_date) >= TO_DATE(('01/01/' ||
1914 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1915 and UPPER(hrl.lookup_code) = UPPER(p_country_code)
1916 and rownum < 2;
1917
1918 t_people_rec t_people_rec_type ;
1919 l_last_name per_people_f.last_name%type ;
1920 l_first_name per_people_f.first_name%type ;
1921 l_middle_names per_people_f.middle_names%type ;
1922 l_national_identifier per_people_f.national_identifier%type ;
1923 l_employee_number per_people_f.employee_number%type ;
1924 l_date_of_birth per_people_f.date_of_birth%type ;
1925 l_title per_people_f.title%type ;
1926 l_suffix per_people_f.suffix%type ;
1927 l_person_id per_people_f.person_id%type ;
1928 l_marital_status per_people_f.marital_status%type ;
1929 l_assignment_id per_assignments_f.assignment_id%type ;
1930 l_county hr_locations.loc_information19%type ;
1931 l_state hr_locations.loc_information18%type ;
1932 l_city hr_locations.loc_information17%type ;
1933 l_address_line1 hr_locations.address_line_1%type ;
1934 l_address_line2 hr_locations.address_line_2%type ;
1935 l_address_line3 hr_locations.address_line_3%type ;
1936 l_telephone_number_1 hr_locations.telephone_number_1%type ;
1937 l_telephone_number_2 hr_locations.telephone_number_2%type ;
1938 l_telephone_number_3 hr_locations.telephone_number_3%type ;
1939 l_postal_code hr_locations.postal_code%type ;
1940 l_process_event_id pay_process_events.process_event_id%type ;
1941 l_object_version_number pay_process_events.object_version_number%type;
1942 l_out_mesg out_mesg_type ;
1943
1944 l_work_home varchar2(15) ;
1945 l_description varchar2(250) ;
1946 l_non_us_country_code varchar2(100) ;
1947 l_xlat_country varchar2(100) ;
1948 l_warn_mesg varchar2(100) ;
1949 l_proc varchar2(72) := g_package||'pqp_windstar_person_read';
1950
1951 i number ;
1952 j number ;
1953 l_err_count number ;
1954 l_count number ;
1955 l_temp_count number := 0 ;
1956 l_country_validate_count number := 0 ;
1957 l_person_read_count number := 0 ;
1958 l_person_err_count number := 0 ;
1959
1960 l_flag boolean := false;
1961 l_year_start_date date;
1962 l_year_end_date date;
1963
1964 begin
1965 hr_utility.set_location('Entering '||l_proc, 5);
1966
1967 l_year_start_date := to_date(('01/01/'||to_char(p_effective_date, 'YYYY'))
1968 ,'MM/DD/YYYY');
1969 l_year_end_date := to_date(('12/31/'||to_char(p_effective_date, 'YYYY'))
1970 ,'MM/DD/YYYY');
1971
1972 --
1973 -- raise error message as source type must be entered while invoking this
1974 -- procedure. The Error is to show user that a blank/Null Source
1975 -- Type has been passed.
1976 --
1977 if (p_source_type is null) then
1978 hr_utility.set_location('Entering '||l_proc, 6);
1979 hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
1980 hr_utility.set_message_token('ARGUMENT', 'Source Type');
1981 hr_utility.set_message_token
1982 ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1983 hr_utility.raise_error;
1984
1985 elsif (p_source_type <> 'PQP_US_ALIEN_WINDSTAR') then
1986 hr_utility.set_location('Entering '||l_proc, 6);
1987 --
1988 -- raise error message as this package caters to PQP_US_ALIEN_WINDSTAR
1989 -- only as of now. Error is to show user that Invalid Source
1990 -- Type has been passed.
1991 --
1992 hr_utility.set_message(800, 'HR_7462_PLK_INVLD_VALUE');
1993 hr_utility.set_message_token('COLUMN_NAME', 'Source Type');
1994 hr_utility.set_message_token
1995 ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1996 hr_utility.raise_error;
1997 end if;
1998
1999 hr_utility.set_location(l_proc, 10);
2000 --
2001 -- The following BEGIN...END block is used so that the error generated
2002 -- due to the above error condition is not trapped
2003 --
2004 if (p_source_type = 'PQP_US_ALIEN_WINDSTAR') then
2005 hr_utility.set_location(l_proc, 15);
2006
2007 if (p_selection_criterion = 'PAY_PROCESS_EVENTS' ) then
2008 hr_utility.set_location(l_proc, 20);
2009 open pay_process_events_cursor
2010 (c_year_start_date => l_year_start_date
2011 ,c_year_end_date => l_year_end_date
2012 ,p_source_type => p_source_type);
2013
2014 elsif (p_selection_criterion = 'ALL' ) then
2015 hr_utility.set_location(l_proc, 25);
2016 open per_people_f_cursor(p_effective_date);
2017
2018 else
2019 hr_utility.set_location(l_proc, 30);
2020 --
2021 -- Else executes when p_selection_criterion is neither 'ALL' nor
2022 -- 'PAY_PROCESS_EVENTS'. Program flow assumes that the
2023 -- NOT NULL string present in the p_selection_criterion is a valid
2024 -- National Identifier (SSN Number). p_selection_criterion string is
2025 -- NOT NULL at this stage as NULL error is checked earlier.
2026 --
2027 open national_identifier_cursor(p_effective_date ,
2028 p_selection_criterion );
2029 end if;
2030 end if;
2031
2032 hr_utility.set_location(l_proc, 35);
2033
2034 -- Counter for the t_people_tab - PL/SQL table
2035 i := 1;
2036 -- Counter for the t_error_tab - PL/SQL table
2037 l_err_count := 0;
2038
2039 if (p_selection_criterion = 'ALL') then
2040
2041 hr_utility.set_location(l_proc, 40);
2042 --
2043 -- Insert_Pay_Process_Events procedure inserts into pay_process_events
2044 -- table. The records are inserted in this table for the
2045 -- reconciliation purpose.
2046 --
2047 Insert_Pay_Process_Events
2048 (p_type => 'ALL'
2049 ,p_effective_date => p_effective_date);
2050
2051 loop
2052 begin
2053 l_last_name := null;
2054 l_first_name := null;
2055 l_middle_names := null;
2056 l_national_identifier := null;
2057 l_employee_number := null;
2058 l_date_of_birth := null;
2059 l_title := null;
2060 l_suffix := null;
2061 l_marital_status := null;
2062 l_person_id := null;
2063 l_work_home := null;
2064 l_county := null;
2065 l_state := null;
2066 l_city := null;
2067 l_address_line1 := null;
2068 l_address_line2 := null;
2069 l_address_line3 := null;
2070 l_telephone_number_1 := null;
2071 l_telephone_number_2 := null;
2072 l_telephone_number_3 := null;
2073 l_postal_code := null;
2074 l_out_mesg := null;
2075
2076 fetch per_people_f_cursor into
2077 l_last_name
2078 ,l_first_name
2079 ,l_middle_names
2080 ,l_national_identifier
2081 ,l_employee_number
2082 ,l_date_of_birth
2083 ,l_title
2084 ,l_suffix
2085 ,l_marital_status
2086 ,l_person_id;
2087
2088 hr_utility.set_location(l_proc, 50);
2089
2090 exit when per_people_f_cursor%notfound;
2091
2092 l_person_read_count := l_person_read_count + 1;
2093
2094 l_work_home := 'HOME';
2095
2096 Address_Select(l_person_id ,
2097 p_effective_date ,
2098 l_work_home ,
2099 l_county ,
2100 l_state ,
2101 l_city ,
2102 l_address_line1 ,
2103 l_address_line2 ,
2104 l_address_line3 ,
2105 l_telephone_number_1 ,
2106 l_telephone_number_2 ,
2107 l_telephone_number_3 ,
2108 l_postal_code);
2109
2110 hr_utility.set_location(l_proc, 60);
2111
2112 t_people_tab(i).last_name := l_last_name ;
2113 t_people_tab(i).first_name := l_first_name ;
2114 t_people_tab(i).middle_names := l_middle_names ;
2115 t_people_tab(i).national_identifier:= l_national_identifier ;
2116 t_people_tab(i).employee_number := l_employee_number ;
2117 t_people_tab(i).date_of_birth := l_date_of_birth ;
2118 t_people_tab(i).title := l_title ;
2119 t_people_tab(i).suffix := l_suffix ;
2120 t_people_tab(i).marital_status := l_marital_status ;
2121 t_people_tab(i).person_id := l_person_id ;
2122 t_people_tab(i).state := l_state ;
2123 t_people_tab(i).city := l_city ;
2124 t_people_tab(i).address_line1 := l_address_line1 ;
2125 t_people_tab(i).address_line2 := l_address_line2 ;
2126 t_people_tab(i).address_line3 := l_address_line3 ;
2127 t_people_tab(i).telephone_number_1 := format_tele(l_telephone_number_1) ;
2128 t_people_tab(i).telephone_number_2 := format_tele(l_telephone_number_2) ;
2129 t_people_tab(i).telephone_number_3 := format_tele(l_telephone_number_3) ;
2130 t_people_tab(i).postal_code := l_postal_code ;
2131 t_people_tab(i).spouse_here := spouse_here(l_person_id ,
2132 p_effective_date);
2133
2134
2135 for c_passport in c_person_passport_info(l_person_id)
2136 loop
2137 t_people_tab(i).passport_number := c_passport.passport_number;
2138 end loop;
2139 hr_utility.set_location(l_proc, 70);
2140
2141 for c_additional in c_person_additional_info(l_person_id)
2142 loop
2143 t_people_tab(i).citizenship_c_code
2144 := c_additional.tax_res_country_code;
2145 for c1_lookup in
2146 c_lookup_values_cursor
2147 (p_effective_date,
2148 'PQP_US_DEPENDENTS_IN_USA',
2149 t_people_tab(i).citizenship_c_code
2150 )
2151 loop
2152 l_temp_count := c1_lookup.count ;
2153 end loop;
2154
2155 if (l_temp_count > 0) then
2156 t_people_tab(i).dependents
2157 := c_additional.dep_children_in_cntry;
2158 else
2159 t_people_tab(i).dependents :=
2160 c_additional.dep_children_total;
2161 end if;
2162
2163 t_people_tab(i).date_first_entered_us :=
2164 fnd_date.canonical_to_date(c_additional.first_entry_date);
2165 end loop;
2166
2167 hr_utility.set_location(l_proc, 80);
2168 --
2169 -- to fetch the complete non us address
2170 --
2171 for c_non_us_addr in c_non_us_address_cur(l_person_id ,
2172 p_effective_date)
2173 loop
2174 t_people_tab(i).non_us_address_line1 := c_non_us_addr.non_us_addr1;
2175 t_people_tab(i).non_us_address_line2 := c_non_us_addr.non_us_addr2;
2176 t_people_tab(i).non_us_address_line3 := c_non_us_addr.non_us_addr3;
2177 t_people_tab(i).non_us_city_postal_cd := c_non_us_addr.non_us_city_postal_cd;
2178 t_people_tab(i).non_us_city := c_non_us_addr.non_us_city;
2179 t_people_tab(i).non_us_region := c_non_us_addr.non_us_region;
2180 t_people_tab(i).non_us_region_postal_cd := c_non_us_addr.non_us_region_postal_cd;
2181 t_people_tab(i).non_us_country_code := c_non_us_addr.non_us_cc;
2182 end loop;
2183
2184 hr_utility.set_location(l_proc, 90);
2185 --
2186 -- After a row in PL/SQL table t_people_tab is populated, we pass
2187 -- the just filled row of PL/SQL table to the validation proc
2188 -- pqp_windstar_person_validate
2189 --
2190 PQP_Windstar_Person_Validate
2191 (p_in_data_rec => t_people_tab(i)
2192 ,p_effective_date => p_effective_date
2193 ,p_out_mesg => l_out_mesg
2194 );
2195 hr_utility.set_location(l_proc, 100);
2196 --
2197 -- t_people_tab PL/SQL table cannot be modified in
2198 -- pqp_windstar_person_validate procedure. c_country_code_xlat_cursor
2199 -- cursor will translate the Oracle Application country code to
2200 -- a valid IRS country code
2201 --
2202 if (t_people_tab(i).non_us_country_code is not null) then
2203
2204 -- tmehra added the following code as a temporary measure
2205 -- to include more countries in the translation
2206 if t_people_tab(i).non_us_country_code = 'SG' then
2207 t_people_tab(i).non_us_country_code := 'SN';
2208
2209 elsif t_people_tab(i).non_us_country_code = 'NG' then
2210 t_people_tab(i).non_us_country_code := 'NI';
2211
2212 elsif t_people_tab(i).non_us_country_code = 'BD' then
2213 t_people_tab(i).non_us_country_code := 'BG';
2214
2215 elsif t_people_tab(i).non_us_country_code = 'NI' then
2216 t_people_tab(i).non_us_country_code := 'NU';
2217
2218 elsif t_people_tab(i).non_us_country_code = 'BA' then
2219 t_people_tab(i).non_us_country_code := 'BK';
2220
2221 else
2222 for c1_xlat in c_country_code_xlat_cursor
2223 (t_people_tab(i).non_us_country_code
2224 ,p_effective_date
2225 )
2226 loop
2227 --
2228 -- changed the following to strip the 'IRS-' from
2229 -- the meaning bug #2170501
2230 --
2231 t_people_tab(i).non_us_country_code
2232 := substr(c1_xlat.meaning,5,length(c1_xlat.meaning)) ;
2233 end loop;
2234 end if;
2235 end if;
2236
2237 hr_utility.set_location(l_proc, 110);
2238 --
2239 -- A warning message is appended to the description field of the
2240 -- pay_process_events table, if the Non US Country code is not a
2241 -- valid tax country code
2242 --
2243 l_country_validate_count := 0;
2244 l_warn_mesg := null;
2245
2246 open c_tax_country_code_cursor
2247 (t_people_tab(i).non_us_country_code,
2248 p_effective_date);
2249 fetch c_tax_country_code_cursor
2250 into l_country_validate_count;
2251 close c_tax_country_code_cursor;
2252
2253 if (l_country_validate_count = 0) then
2254 if (t_people_tab(i).non_us_country_code is null) then
2255 l_warn_mesg :='| Warning ==> Non US Country Code is NULL |';
2256 else
2257 l_warn_mesg := '| Warning ==> Non US Country Code [' ||
2258 t_people_tab(i).non_us_country_code ||
2259 '] may be Invalid |';
2260 end if;
2261 end if;
2262 hr_utility.set_location(l_proc, 120);
2263 --
2264 -- Delete the current row in the PL/SQL table. Update the status
2265 -- in the pay_process_events table to reflect the status as
2266 -- DATA_VALIDATION_FAILED. The row is deleted as we do not want
2267 -- to insert the row containing an error/validation failure
2268 -- in indv_rev1_temp table.
2269 --
2270 l_process_event_id := null;
2271
2272 open pay_process_events_ovn_cursor(l_person_id
2273 ,p_source_type
2274 ,p_effective_date);
2275 loop
2276 l_description := null;
2277 fetch pay_process_events_ovn_cursor
2278 into
2279 l_process_event_id ,
2280 l_object_version_number ,
2281 l_assignment_id ,
2282 l_description ;
2283 hr_utility.set_location(l_proc, 130);
2284
2285 exit when pay_process_events_ovn_cursor%notfound;
2286
2287 if (l_out_mesg is null) then
2288 --
2289 -- l_out_mesg = NULL means that there was no failure. Increment
2290 -- the counter and proceed for fetching of the next row from
2291 -- the respective cursor. We therefore do NOT change the status.
2292 -- The status is changed from N to R after a row in inserted
2293 -- in ten42s_state_temp table.
2294 --
2295 if (l_warn_mesg is not null) then
2296 pqp_process_events_errorlog
2297 (p_process_event_id1 => l_process_event_id
2298 ,p_object_version_number1 => l_object_version_number
2299 ,p_status1 => hr_api.g_varchar2
2300 ,p_description1 => substr(l_description || l_warn_mesg, 1, 240)
2301 );
2302 end if;
2303
2304 hr_utility.set_location(l_proc, 140);
2305 -- If the warning message is NOT null, then we do not
2306 -- change the status to D as this is just a warning
2307 else
2308 --
2309 -- Since l_out_mesg is NOT NULL, that means an error was
2310 -- detected. We therefore change the status of the
2311 -- pay_process_events table to 'D' meaning DATA_VALIDATION_FAILED
2312 --
2313 pqp_process_events_errorlog
2314 (p_process_event_id1 => l_process_event_id
2315 ,p_object_version_number1 => l_object_version_number
2316 ,p_status1 => 'D'
2317 ,p_description1 => SUBSTR(l_out_mesg || l_warn_mesg ||
2318 l_description, 1, 240)
2319 );
2320 end if;
2321 hr_utility.set_location(l_proc, 150);
2322
2323 end loop;
2324
2325 close pay_process_events_ovn_cursor;
2326
2327 if (l_out_mesg is null) then
2328 i := i + 1;
2329 hr_utility.set_location(l_proc, 160);
2330 else
2331 hr_utility.set_location(l_proc, 170);
2332 --
2333 -- for wf notification consolidation
2334 --
2335 if l_process_event_id is not null then
2336
2337 l_err_count := l_err_count+1;
2338 t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
2339 t_error_tab(l_err_count).process_event_id:= l_process_event_id;
2340
2341 end if;
2342
2343 t_people_tab.delete(i) ;
2344 l_person_err_count := l_person_err_count + 1;
2345 l_out_mesg := null;
2346 l_warn_mesg := null;
2347
2348 end if; -- if (l_out_mesg
2349
2350 exception
2351 when others then
2352 hr_utility.set_location(l_proc, 180);
2353 l_person_id := t_people_tab(i).person_id;
2354 l_out_mesg := SUBSTR('Oracle Error ' || TO_CHAR(sqlcode) ||
2355 sqlerrm, 1, 240);
2356 if (t_people_tab.exists(i)) then
2357 t_people_tab.delete(i) ;
2358 l_person_err_count := l_person_err_count + 1;
2359 end if;
2360 if (pay_process_events_ovn_cursor%isopen = true) then
2361 close pay_process_events_ovn_cursor;
2362 end if;
2363
2364 l_process_event_id := null;
2365
2366 open pay_process_events_ovn_cursor(l_person_id
2367 ,p_source_type
2368 ,p_effective_date);
2369 loop
2370 fetch pay_process_events_ovn_cursor
2371 into l_process_event_id
2372 ,l_object_version_number
2373 ,l_assignment_id
2374 ,l_description;
2375
2376 hr_utility.set_location(l_proc, 190);
2377
2378 exit when pay_process_events_ovn_cursor%notfound;
2379
2380 pqp_process_events_errorlog
2381 (p_process_event_id1 => l_process_event_id
2382 ,p_object_version_number1 => l_object_version_number
2383 ,p_status1 => 'D'
2384 ,p_description1 => substr('Oralce Error ' || to_char(sqlcode) ||
2385 ' ' ||sqlerrm, 1, 240)
2386 );
2387
2388 end loop;
2389 close pay_process_events_ovn_cursor;
2390 --
2391 -- for wf notification consolidation
2392 --
2393 if l_process_event_id is not null then
2394
2395 l_err_count := l_err_count+1;
2396 t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
2397 t_error_tab(l_err_count).process_event_id := l_process_event_id;
2398
2399 end if;
2400 l_out_mesg := null;
2401 l_warn_mesg := null;
2402 end;
2403 end loop;
2404
2405 close per_people_f_cursor;
2406
2407 elsif (p_selection_criterion = 'PAY_PROCESS_EVENTS' ) then
2408 loop
2409 begin
2410 l_last_name := null;
2411 l_first_name := null;
2412 l_middle_names := null;
2413 l_national_identifier := null;
2414 l_employee_number := null;
2415 l_date_of_birth := null;
2416 l_title := null;
2417 l_suffix := null;
2418 l_marital_status := null;
2419 l_person_id := null;
2420 l_work_home := null;
2421 l_county := null;
2422 l_state := null;
2423 l_city := null;
2424 l_address_line1 := null;
2425 l_address_line2 := null;
2426 l_address_line3 := null;
2427 l_telephone_number_1 := null;
2428 l_telephone_number_2 := null;
2429 l_telephone_number_3 := null;
2430 l_postal_code := null;
2431 l_out_mesg := null;
2432
2433 fetch pay_process_events_cursor
2434 into l_last_name
2435 ,l_first_name
2436 ,l_middle_names
2437 ,l_national_identifier
2438 ,l_employee_number
2439 ,l_date_of_birth
2440 ,l_title
2441 ,l_suffix
2442 ,l_marital_status
2443 ,l_person_id;
2444
2445 hr_utility.set_location(l_proc, 200);
2446
2447 exit when pay_process_events_cursor%notfound;
2448
2449 l_person_read_count := l_person_read_count + 1;
2450 l_work_home := 'HOME';
2451
2452 Address_Select(l_person_id ,
2453 p_effective_date ,
2454 l_work_home ,
2455 l_county ,
2456 l_state ,
2457 l_city ,
2458 l_address_line1 ,
2459 l_address_line2 ,
2460 l_address_line3 ,
2461 l_telephone_number_1 ,
2462 l_telephone_number_2 ,
2463 l_telephone_number_3 ,
2464 l_postal_code);
2465
2466 hr_utility.set_location(l_proc, 210);
2467
2468 t_people_tab(i).last_name := l_last_name ;
2469 t_people_tab(i).first_name := l_first_name ;
2470 t_people_tab(i).middle_names := l_middle_names ;
2471 t_people_tab(i).national_identifier:= l_national_identifier ;
2472 t_people_tab(i).employee_number := l_employee_number ;
2473 t_people_tab(i).date_of_birth := l_date_of_birth ;
2474 t_people_tab(i).title := l_title ;
2475 t_people_tab(i).suffix := l_suffix ;
2476 t_people_tab(i).marital_status := l_marital_status ;
2477 t_people_tab(i).person_id := l_person_id ;
2478 t_people_tab(i).state := l_state ;
2479 t_people_tab(i).city := l_city ;
2480 t_people_tab(i).address_line1 := l_address_line1 ;
2481 t_people_tab(i).address_line2 := l_address_line2 ;
2482 t_people_tab(i).address_line3 := l_address_line3 ;
2483 t_people_tab(i).telephone_number_1 := format_tele(l_telephone_number_1) ;
2484 t_people_tab(i).telephone_number_2 := format_tele(l_telephone_number_2) ;
2485 t_people_tab(i).telephone_number_3 := format_tele(l_telephone_number_3) ;
2486 t_people_tab(i).postal_code := l_postal_code ;
2487 t_people_tab(i).spouse_here := spouse_here(l_person_id ,
2488 p_effective_date);
2489
2490 for c_passport in c_person_passport_info(l_person_id)
2491 loop
2492 t_people_tab(i).passport_number := c_passport.passport_number;
2493 end loop;
2494
2495 hr_utility.set_location(l_proc, 220);
2496
2497 for c_additional in c_person_additional_info(l_person_id)
2498 loop
2499 t_people_tab(i).citizenship_c_code:=
2500 c_additional.tax_res_country_code;
2501 for c1_lookup in c_lookup_values_cursor
2502 (
2503 p_effective_date ,
2504 'PQP_US_DEPENDENTS_IN_USA' ,
2505 t_people_tab(i).citizenship_c_code
2506 )
2507 loop
2508 l_temp_count := c1_lookup.count ;
2509 end loop;
2510
2511 if (l_temp_count > 0) then
2512 t_people_tab(i).dependents :=
2513 c_additional.dep_children_in_cntry;
2514 else
2515 t_people_tab(i).dependents :=
2516 c_additional.dep_children_total;
2517 end if;
2518
2519 t_people_tab(i).date_first_entered_us :=
2520 fnd_date.canonical_to_date(c_additional.first_entry_date);
2521 end loop;
2522
2523 hr_utility.set_location(l_proc, 230);
2524
2525 -- to fetch the complete non us address
2526
2527 for c_non_us_addr in c_non_us_address_cur(l_person_id ,
2528 p_effective_date)
2529 loop
2530 t_people_tab(i).non_us_address_line1 := c_non_us_addr.non_us_addr1;
2531 t_people_tab(i).non_us_address_line2 := c_non_us_addr.non_us_addr2;
2532 t_people_tab(i).non_us_address_line3 := c_non_us_addr.non_us_addr3;
2533 t_people_tab(i).non_us_city_postal_cd := c_non_us_addr.non_us_city_postal_cd;
2534 t_people_tab(i).non_us_city := c_non_us_addr.non_us_city;
2535 t_people_tab(i).non_us_region := c_non_us_addr.non_us_region;
2536 t_people_tab(i).non_us_country_code := c_non_us_addr.non_us_cc;
2537 end loop;
2538
2539 hr_utility.set_location(l_proc, 240);
2540 -- After a row in PL/SQL table t_people_tab is populated, we pass
2541 -- the just filled row of PL/SQL table to the validation proc
2542 -- pqp_windstar_person_validate
2543 --
2544 pqp_windstar_person_validate
2545 (p_in_data_rec => t_people_tab(i)
2546 ,p_effective_date => p_effective_date
2547 ,p_out_mesg => l_out_mesg
2548 );
2549
2550 hr_utility.set_location(l_proc, 250);
2551 -- t_people_tab PL/SQL table cannot be modified in pqp_windstar_person_validate
2552 -- procedure. c_country_code_xlat_cursor cursor will translate the
2553 -- Oracle Application country code to a valid IRS country code
2554 --
2555 if (t_people_tab(i).non_us_country_code is not null) then
2556 for c1_xlat in c_country_code_xlat_cursor
2557 (t_people_tab(i).non_us_country_code
2558 ,p_effective_date
2559 )
2560 loop
2561 -- t_people_tab(i).non_us_country_code := c1_xlat.meaning ;
2562 -- changed the following to strip the 'IRS-' from the meaning
2563
2564 t_people_tab(i).non_us_country_code
2565 := SUBSTR(c1_xlat.meaning,5,length(c1_xlat.meaning)) ;
2566
2567 end loop;
2568 end if;
2569 hr_utility.set_location(l_proc, 260);
2570 -- A warning message is appended to the description field of
2571 -- the pay_process_events table, if the Non US Country code
2572 -- is not a valid tax country code
2573
2574 l_country_validate_count := 0;
2575 l_warn_mesg := null;
2576
2577 open c_tax_country_code_cursor(t_people_tab(i).non_us_country_code
2578 ,p_effective_date);
2579 fetch c_tax_country_code_cursor
2580 into l_country_validate_count;
2581 close c_tax_country_code_cursor;
2582
2583 if (l_country_validate_count = 0) then
2584 if (t_people_tab(i).non_us_country_code is null) then
2585 l_warn_mesg :='| Warning ==> Non US Country Code is NULL |';
2586 else
2587 l_warn_mesg := '| Warning ==> Non US Country Code [' ||
2588 t_people_tab(i).non_us_country_code ||
2589 '] may be Invalid |';
2590 end if;
2591 end if;
2592 hr_utility.set_location(l_proc, 270);
2593 -- Delete the current row in the PL/SQL table. Update the status in
2594 -- the pay_process_events table to reflect the status as DATA_VALIDATION_FAILED.
2595 -- The row is deleted as we do not want to insert the row containing
2596 -- an error/validation failure in indv_rev1_temp table.
2597
2598 l_process_event_id := null;
2599
2600 open pay_process_events_ovn_cursor(l_person_id ,
2601 p_source_type ,
2602 p_effective_date);
2603 loop
2604
2605 l_description := null;
2606 fetch pay_process_events_ovn_cursor into
2607 l_process_event_id ,
2608 l_object_version_number ,
2609 l_assignment_id ,
2610 l_description ;
2611 hr_utility.set_location(l_proc, 280);
2612
2613 exit when pay_process_events_ovn_cursor%notfound;
2614
2615 if (l_out_mesg is null) then
2616 hr_utility.set_location(l_proc, 290);
2617 -- l_out_mesg = NULL means that there was no failure.
2618 -- Increment the counter and proceed for fetching of the
2619 -- next row from the respective cursor. We therefore do
2620 -- NOT change the status. The status is changed from N to R
2621 -- after a row in inserted in ten42s_state_temp table
2622
2623 if (l_warn_mesg is not null) then
2624 hr_utility.set_location(l_proc, 300);
2625
2626 pqp_process_events_errorlog
2627 (p_process_event_id1 => l_process_event_id
2628 ,p_object_version_number1 => l_object_version_number
2629 ,p_status1 => hr_api.g_varchar2
2630 ,p_description1 => SUBSTR(l_description ||
2631 l_warn_mesg, 1, 240)
2632 );
2633 end if;
2634 else
2635 hr_utility.set_location(l_proc, 310);
2636 -- Since l_out_mesg is NOT NULL, that means an error was
2637 -- detected. We therefore change the status of the
2638 -- pay_process_events table to 'D' meaning DATA_VALIDATION_FAILED
2639 --
2640 pqp_process_events_errorlog
2641 (p_process_event_id1 => l_process_event_id
2642 ,p_object_version_number1 => l_object_version_number
2643 ,p_status1 => 'D'
2644 ,p_description1 => substr(l_out_mesg || l_warn_mesg ||
2645 l_description, 1, 240)
2646 );
2647 end if;
2648 end loop;
2649 close pay_process_events_ovn_cursor;
2650 if (l_out_mesg is null) then
2651 i := i + 1;
2652 hr_utility.set_location(l_proc, 320);
2653 else
2654 -- for wf notification consolidation
2655 if l_process_event_id is not null then
2656 l_err_count := l_err_count+1;
2657
2658 t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
2659 t_error_tab(l_err_count).process_event_id := l_process_event_id;
2660 end if;
2661
2662 t_people_tab.delete(i) ;
2663 l_person_err_count := l_person_err_count + 1;
2664 l_out_mesg := null;
2665 l_warn_mesg := null;
2666 hr_utility.set_location(l_proc, 330);
2667
2668 end if;
2669 exception
2670 when others then
2671 hr_utility.set_location(l_proc, 340);
2672 l_person_id := t_people_tab(i).person_id;
2673 --
2674 if (t_people_tab.exists(i)) then
2675 t_people_tab.delete(i) ;
2676 l_person_err_count := l_person_err_count + 1;
2677 end if;
2678 --
2679 l_out_mesg := SUBSTR('Oralce Error ' || TO_CHAR(sqlcode) ||
2680 sqlerrm, 1, 240);
2681 if (pay_process_events_ovn_cursor%isopen = true) then
2682 close pay_process_events_ovn_cursor;
2683 end if;
2684 l_process_event_id := null;
2685
2686 open pay_process_events_ovn_cursor(l_person_id ,
2687 p_source_type ,
2688 p_effective_date);
2689 loop
2690 fetch pay_process_events_ovn_cursor into
2691 l_process_event_id ,
2692 l_object_version_number ,
2693 l_assignment_id ,
2694 l_description ;
2695 hr_utility.set_location(l_proc, 350);
2696
2697 exit when pay_process_events_ovn_cursor%notfound;
2698 pqp_process_events_errorlog
2699 (
2700 p_process_event_id1 => l_process_event_id ,
2701 p_object_version_number1 => l_object_version_number ,
2702 p_status1 => 'D' ,
2703 p_description1 => l_out_mesg
2704 );
2705 end loop;
2706 close pay_process_events_ovn_cursor;
2707
2708 if l_process_event_id is not null then
2709 l_err_count := l_err_count+1;
2710 t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
2711 t_error_tab(l_err_count).process_event_id := l_process_event_id;
2712 end if;
2713
2714 l_out_mesg := null;
2715 l_warn_mesg := null;
2716 end; -- exception
2717
2718 end loop;
2719
2720 close pay_process_events_cursor;
2721
2722 else
2723 -- Executing the code for a particular National Identifier
2724 hr_utility.set_location(l_proc, 360);
2725 /******
2726 insert_pay_process_events procedure inserts into pay_process_events
2727 table. the records are inserted in this table for the reconciliation purpose.
2728 *****/
2729 insert_pay_process_events(p_selection_criterion ,
2730 p_effective_date );
2731 loop
2732 begin
2733 l_last_name := null;
2734 l_first_name := null;
2735 l_middle_names := null;
2736 l_national_identifier := null;
2737 l_employee_number := null;
2738 l_date_of_birth := null;
2739 l_title := null;
2740 l_suffix := null;
2741 l_marital_status := null;
2742 l_person_id := null;
2743 l_work_home := null;
2744 l_county := null;
2745 l_state := null;
2746 l_city := null;
2747 l_address_line1 := null;
2748 l_address_line2 := null;
2749 l_address_line3 := null;
2750 l_telephone_number_1 := null;
2751 l_telephone_number_2 := null;
2752 l_telephone_number_3 := null;
2753 l_postal_code := null;
2754 l_out_mesg := null;
2755
2756 fetch national_identifier_cursor into
2757 l_last_name ,
2758 l_first_name ,
2759 l_middle_names ,
2760 l_national_identifier ,
2761 l_employee_number ,
2762 l_date_of_birth ,
2763 l_title ,
2764 l_suffix ,
2765 l_marital_status ,
2766 l_person_id ;
2767 hr_utility.set_location(l_proc, 370);
2768
2769 exit when national_identifier_cursor%notfound;
2770 l_person_read_count := l_person_read_count + 1;
2771
2772 l_work_home := 'HOME';
2773
2774 address_select(l_person_id ,
2775 p_effective_date ,
2776 l_work_home ,
2777 l_county ,
2778 l_state ,
2779 l_city ,
2780 l_address_line1 ,
2781 l_address_line2 ,
2782 l_address_line3 ,
2783 l_telephone_number_1 ,
2784 l_telephone_number_2 ,
2785 l_telephone_number_3 ,
2786 l_postal_code );
2787 hr_utility.set_location(l_proc, 380);
2788
2789 t_people_tab(i).last_name := l_last_name ;
2790 t_people_tab(i).first_name := l_first_name ;
2791 t_people_tab(i).middle_names := l_middle_names ;
2792 t_people_tab(i).national_identifier:= l_national_identifier ;
2793 t_people_tab(i).employee_number := l_employee_number ;
2794 t_people_tab(i).date_of_birth := l_date_of_birth ;
2795 t_people_tab(i).title := l_title ;
2796 t_people_tab(i).suffix := l_suffix ;
2797 t_people_tab(i).marital_status := l_marital_status ;
2798 t_people_tab(i).person_id := l_person_id ;
2799 t_people_tab(i).state := l_state ;
2800 t_people_tab(i).city := l_city ;
2801 t_people_tab(i).address_line1 := l_address_line1 ;
2802 t_people_tab(i).address_line2 := l_address_line2 ;
2803 t_people_tab(i).address_line3 := l_address_line3 ;
2804 t_people_tab(i).telephone_number_1 := format_tele(l_telephone_number_1) ;
2805 t_people_tab(i).telephone_number_2 := format_tele(l_telephone_number_2) ;
2806 t_people_tab(i).telephone_number_3 := format_tele(l_telephone_number_3) ;
2807 t_people_tab(i).postal_code := l_postal_code ;
2808 t_people_tab(i).spouse_here := spouse_here(l_person_id ,
2809 p_effective_date);
2810
2811 for c_passport in c_person_passport_info(l_person_id) loop
2812 t_people_tab(i).passport_number := c_passport.passport_number;
2813 end loop;
2814 hr_utility.set_location(l_proc, 390);
2815
2816 for c_additional in c_person_additional_info(l_person_id) loop
2817 t_people_tab(i).citizenship_c_code:=
2818 c_additional.tax_res_country_code;
2819 for c1_lookup in c_lookup_values_cursor
2820 (
2821 p_effective_date ,
2822 'PQP_US_DEPENDENTS_IN_USA' ,
2823 t_people_tab(i).citizenship_c_code
2824 )
2825 loop
2826 l_temp_count := c1_lookup.count ;
2827 end loop;
2828
2829 if (l_temp_count > 0) then
2830 t_people_tab(i).dependents :=
2831 c_additional.dep_children_in_cntry;
2832 else
2833 t_people_tab(i).dependents :=
2834 c_additional.dep_children_total;
2835 end if;
2836
2837 t_people_tab(i).date_first_entered_us :=
2838 fnd_date.canonical_to_date(c_additional.first_entry_date);
2839 end loop;
2840 hr_utility.set_location(l_proc, 400);
2841
2842 /* Added the code to fetch the complete non us address - tmehra 15-OCT-2001 */
2843
2844 for c_non_us_addr in c_non_us_address_cur(l_person_id ,
2845 p_effective_date)
2846 loop
2847 t_people_tab(i).non_us_address_line1 := c_non_us_addr.non_us_addr1;
2848 t_people_tab(i).non_us_address_line2 := c_non_us_addr.non_us_addr2;
2849 t_people_tab(i).non_us_address_line3 := c_non_us_addr.non_us_addr3;
2850 t_people_tab(i).non_us_city_postal_cd := c_non_us_addr.non_us_city_postal_cd;
2851 t_people_tab(i).non_us_city := c_non_us_addr.non_us_city;
2852 t_people_tab(i).non_us_region := c_non_us_addr.non_us_region;
2853 t_people_tab(i).non_us_country_code := c_non_us_addr.non_us_cc;
2854 end loop;
2855 hr_utility.set_location(l_proc, 410);
2856 /*****
2857 after a row in PL/sql table t_people_tab is populated, we pass the just
2858 filled row of PL/sql table to the validation proc pqp_windstar_person_validate
2859 *****/
2860 pqp_windstar_person_validate
2861 (
2862 p_in_data_rec => t_people_tab(i) ,
2863 p_effective_date => p_effective_date ,
2864 p_out_mesg => l_out_mesg
2865 );
2866 hr_utility.set_location(l_proc, 420);
2867 /*****
2868 t_people_tab PL/sql table cannot be modified in pqp_windstar_person_validate
2869 procedure. c_country_code_xlat_cursor cursor will translate the Oracle
2870 Application country code to a valid IRS country code
2871 *****/
2872 if (t_people_tab(i).non_us_country_code is not null) then
2873 for c1_xlat in c_country_code_xlat_cursor
2874 (
2875 t_people_tab(i).non_us_country_code,
2876 p_effective_date
2877 )
2878 loop
2879 -- t_people_tab(i).non_us_country_code := c1_xlat.meaning ;
2880 -- changed the following to strip the 'IRS-' from the meaning
2881 -- fix for the bug #2170501 - tmehra
2882 t_people_tab(i).non_us_country_code := SUBSTR(c1_xlat.meaning,
2883 5,
2884 length(c1_xlat.meaning)) ;
2885
2886 end loop;
2887 end if;
2888 hr_utility.set_location(l_proc, 430);
2889 /*****
2890 A warning message is appended to the description field of the pay_process_events
2891 table, if the Non US Country code is not a valid tax country code
2892 *****/
2893 l_country_validate_count := 0;
2894 l_warn_mesg := null;
2895 open c_tax_country_code_cursor(t_people_tab(i).non_us_country_code ,
2896 p_effective_date );
2897 fetch c_tax_country_code_cursor
2898 into l_country_validate_count;
2899 close c_tax_country_code_cursor;
2900 if (l_country_validate_count = 0) then
2901 if (t_people_tab(i).non_us_country_code is null) then
2902 l_warn_mesg :='| Warning ==> Non US Country Code is NULL |';
2903 else
2904 l_warn_mesg := '| Warning ==> Non US Country Code [' ||
2905 t_people_tab(i).non_us_country_code ||
2906 '] may be Invalid |';
2907 end if;
2908 end if;
2909 hr_utility.set_location(l_proc, 440);
2910
2911 /*****
2912 delete the current row in the PL/sql table. update the status in the
2913 pay_process_events table to reflect the status as DATA_VALIDATION_FAILED.
2914 the row is deleted as we do not want to insert the row containing an
2915 error/validation failure in indv_rev1_temp table.
2916 *****/
2917 l_process_event_id := null;
2918
2919 open pay_process_events_ovn_cursor(l_person_id ,
2920 p_source_type ,
2921 p_effective_date);
2922 loop
2923
2924 l_description := null;
2925 fetch pay_process_events_ovn_cursor into
2926 l_process_event_id ,
2927 l_object_version_number ,
2928 l_assignment_id ,
2929 l_description ;
2930 hr_utility.set_location(l_proc, 450);
2931
2932 exit when pay_process_events_ovn_cursor%notfound;
2933
2934 if (l_out_mesg is null) then
2935 hr_utility.set_location(l_proc, 460);
2936 /*****
2937 l_out_mesg = null means that there was no failure. increment the counter
2938 and proceed for fetching of the next row from the respective cursor.
2939 We therefore do not change the status. the status is changed from N to R
2940 after a row in inserted in ten42s_state_temp table
2941 *****/
2942
2943 if (l_warn_mesg is not null) then
2944 hr_utility.set_location(l_proc, 470);
2945 pqp_process_events_errorlog
2946 (
2947 p_process_event_id1 => l_process_event_id ,
2948 p_object_version_number1 => l_object_version_number,
2949 p_status1 => hr_api.g_varchar2 ,
2950 p_description1 =>
2951 SUBSTR(l_description || l_warn_mesg, 1, 240)
2952 );
2953 end if;
2954 else
2955 hr_utility.set_location(l_proc, 480);
2956 /*****
2957 Since l_out_mesg is not null, that means an error was detected. We therefore
2958 change the status of the pay_process_events table to 'D' meaning
2959 DATA_VALIDATION_FAILED
2960 *****/
2961 pqp_process_events_errorlog
2962 (
2963 p_process_event_id1 => l_process_event_id ,
2964 p_object_version_number1 => l_object_version_number ,
2965 p_status1 => 'D' ,
2966 p_description1 =>
2967 SUBSTR(l_out_mesg || l_warn_mesg ||
2968 l_description, 1, 240)
2969 );
2970 end if;
2971 end loop;
2972
2973 close pay_process_events_ovn_cursor;
2974 if (l_out_mesg is null) then
2975 i := i + 1;
2976 hr_utility.set_location(l_proc, 490);
2977 else
2978 /* Added by tmehra for wf notification consolidation */
2979 if l_process_event_id is not null then
2980 l_err_count := l_err_count+1;
2981
2982 t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
2983 t_error_tab(l_err_count).process_event_id := l_process_event_id;
2984 end if;
2985 /* Changes for wf notification consolidation ends */
2986
2987 t_people_tab.delete(i) ;
2988 l_person_err_count := l_person_err_count + 1;
2989 l_out_mesg := null;
2990 l_warn_mesg := null;
2991 hr_utility.set_location(l_proc, 500);
2992 end if;
2993 exception
2994 when OTHERS then
2995 hr_utility.set_location(l_proc, 510);
2996 l_person_id := t_people_tab(i).person_id;
2997 if (t_people_tab.exists(i)) then
2998 t_people_tab.delete(i) ;
2999 l_person_err_count := l_person_err_count + 1;
3000 end if;
3001 l_out_mesg := SUBSTR('Oracle Error ' || TO_CHAR(sqlcode) ||
3002 sqlerrm, 1, 240);
3003 if (pay_process_events_ovn_cursor%isopen = true) then
3004 close pay_process_events_ovn_cursor;
3005 end if;
3006
3007 l_process_event_id := null;
3008
3009 open pay_process_events_ovn_cursor(l_person_id ,
3010 p_source_type ,
3011 p_effective_date);
3012 loop
3013 fetch pay_process_events_ovn_cursor into
3014 l_process_event_id ,
3015 l_object_version_number ,
3016 l_assignment_id ,
3017 l_description ;
3018 hr_utility.set_location(l_proc, 520);
3019
3020 exit when pay_process_events_ovn_cursor%notfound;
3021 pqp_process_events_errorlog
3022 (
3023 p_process_event_id1 => l_process_event_id ,
3024 p_object_version_number1 => l_object_version_number ,
3025 p_status1 => 'D' ,
3026 p_description1 => l_out_mesg
3027 );
3028 end loop;
3029 close pay_process_events_ovn_cursor;
3030 /* Added by tmehra for wf notification consolidation */
3031 if l_process_event_id is not null then
3032 l_err_count := l_err_count+1;
3033
3034 t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
3035 t_error_tab(l_err_count).process_event_id := l_process_event_id;
3036
3037 end if;
3038 /* Changes for wf notification consolidation ends */
3039
3040 l_out_mesg := null;
3041 l_warn_mesg := null;
3042 end;
3043 end loop;
3044 close national_identifier_cursor;
3045 end if;
3046 hr_utility.set_location(l_proc, 530);
3047 p_person_read_count := l_person_read_count ;
3048 p_person_err_count := l_person_err_count ;
3049 hr_utility.set_location('Leaving '||l_proc, 540);
3050 exception
3051 when OTHERS then
3052 p_person_read_count := l_person_read_count ;
3053 p_person_err_count := l_person_err_count ;
3054 hr_utility.set_location('Leaving '||l_proc, 550);
3055 hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3056 hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3057 || 'pqp_windstar_person_read. Error Code = ' || TO_CHAR(sqlcode) ||
3058 ' ' || sqlerrm);
3059 hr_utility.raise_error;
3060 end pqp_windstar_person_read;
3061 /***************************************************************************
3062 name : pqp_windstar_balance_read
3063 Purpose : the following procedure is called from the main procedure. This
3064 returns the balance details.
3065 Arguments :
3066 in
3067 t_people_tab : PL/sql table contains the Personal details.
3068 This is passed a an I/P parameter as this
3069 procedure returns the balance details only
3070 for the assignments present in this table.
3071 p_source_type : source type(Winstar or some other system.
3072 as of now it is Windstar.
3073 p_effective_date : Effective date.
3074 out
3075 t_balance_tab : PL/sql table contains the balance details.
3076 in out
3077 t_error_tab : PL/sql table contains the error details.
3078 Notes : private
3079 *****************************************************************************/
3080 procedure pqp_windstar_balance_read
3081 (
3082 t_people_tab in out NOCOPY t_people_tab_type ,
3083 t_error_tab in out NOCOPY t_error_tab_type ,
3084 p_source_type in varchar2 ,
3085 p_effective_date in date ,
3086 t_balance_tab out NOCOPY t_balance_tab_type
3087 ) is --{
3088 l_flag varchar2(3) ;
3089 l_sub_type varchar2(3) ;
3090 l_income_code varchar2(10) ;
3091 l_c_income_code varchar2(10) ; -- Added by tmehra oct02
3092 l_earning_ele_flag boolean ; -- Added by tmehra oct02
3093
3094 l_proc varchar2(72) :=
3095 g_package || 'pqp_windstar_balance_read';
3096 l_balance_name pay_balance_types.balance_name%type ;
3097 l_dimension_name pay_balance_dimensions.dimension_name%type ;
3098 l_state varchar2(100) ;
3099 l_last_name per_all_people_f.last_name%type ;
3100 l_first_name per_all_people_f.first_name%type;
3101 l_middle_names per_all_people_f.middle_names%type ;
3102 l_national_identifier per_all_people_f.national_identifier%type ;
3103 l_tax_residence_country_code varchar2(100) ;
3104 l_description varchar2(250) ;
3105
3106 l_out_mesg out_mesg_type ;
3107
3108 l_balance number ;
3109 l_year number ;
3110 l_prev_year number ;
3111 l_count number := 0 ;
3112 l_counter number := 0 ;
3113 l_counter1 number := 0 ;
3114 l_temp_assignment_id per_all_assignments_f.assignment_id%type ;
3115 l_assignment_id per_all_assignments_f.assignment_id%type ;
3116 j number ;
3117 i number ;
3118 l_err_count number ;
3119 l_person_id per_all_people_f.person_id%type ;
3120 l_income_code_count number := 0 ;
3121 l_process_event_id number ;
3122 l_object_version_number per_all_people_f.object_version_number%type ;
3123 l_prev_amount number ;
3124
3125 l_year_start date ;
3126 l_year_end date ;
3127 l_effective_date date ; -- Added by tmehra Oct02
3128 l_date_of_birth date ;
3129 l_sit_flag boolean ;
3130 l_analyzed_data_details_id
3131 pqp_analyzed_alien_details.analyzed_data_details_id%type;
3132 l_analyzed_data_id pqp_analyzed_alien_data.analyzed_data_id%type ;
3133 l_exemption_code pqp_analyzed_alien_details.exemption_code%type;
3134 l_withholding_rate
3135 pqp_analyzed_alien_details.withholding_rate%type ;
3136 l_wh_allowance
3137 pqp_analyzed_alien_data.withldg_allow_eligible_flag%type;
3138 l_income_code_sub_type
3139 pqp_analyzed_alien_details.income_code_sub_type%type ;
3140 l_constant_addl_tax pqp_analyzed_alien_details.constant_addl_tax%type ;
3141
3142
3143 type t_temp_person_assgn_rec is record
3144 (
3145 person_id number ,
3146 assgnment_id number
3147 );
3148
3149 type t_person_assign_table_type is table of t_temp_person_assgn_rec
3150 index by binary_integer ;
3151 type t_lookup_table_type is table of varchar2(45) index by binary_integer ;
3152
3153 t_temp_assignment_table t_person_assign_table_type ;
3154 t_lookup_table t_lookup_table_type ;
3155
3156 /*****
3157 the following cursor decides whether a row is present in the
3158 pqp_analyzed_alien_details table for the given income code and given
3159 assignment id
3160 *****/
3161
3162 cursor c_analyzed_data(p_income_code in varchar2 ,
3163 p_person_id in number ,
3164 p_tax_year in number ) is
3165 select income_code ,
3166 exemption_code ,
3167 withholding_rate ,
3168 income_code_sub_type ,
3169 constant_addl_tax
3170 from pqp_analyzed_alien_data paadat ,
3171 pqp_analyzed_alien_details paadet ,
3172 per_people_f ppf ,
3173 per_assignments_f paf
3174 where paadat.analyzed_data_id = paadet.analyzed_data_id
3175 and paadet.income_code = p_income_code
3176 and ppf.person_id = paf.person_id
3177 and ppf.person_id = p_person_id
3178 and paadat.tax_year = p_tax_year
3179 and paf.assignment_id = paadat.assignment_id
3180 and rownum < 2;
3181 --
3182 -- The following cursor selects rows if the person has earning elements in the
3183 -- calender year of the effective date.
3184 --
3185 cursor c_income_code_cursor(p_person_id in number
3186 ,c_year_start in date
3187 ,c_year_end in date ) is
3188 select distinct
3189 nvl(pet.element_information1, ' ') income_code
3190 from per_all_assignments_f paf
3191 ,per_all_people_f ppf
3192 ,pay_element_entries_f pee
3193 ,pay_element_links_f pel
3194 ,pay_element_types_f pet
3195 ,pay_element_classifications pec
3196 where paf.person_id = ppf.person_id
3197 and ppf.person_id = p_person_id
3198 and ((c_year_end between paf.effective_start_date
3199 and paf.effective_end_date
3200 )
3201 or
3202 (paf.effective_end_date =
3203 (select max(asx.effective_end_date)
3204 from per_all_assignments_f asx
3205 where asx.assignment_id = paf.assignment_id
3206 and asx.business_group_id = paf.business_group_id
3207 and asx.person_id = paf.person_id
3208 and asx.effective_end_date between c_year_start
3209 and c_year_end)
3210 )
3211 )
3212 and paf.effective_end_date between ppf.effective_start_date
3213 and ppf.effective_end_date
3214 and paf.assignment_id = pee.assignment_id
3215 and pee.element_link_id = pel.element_link_id
3216 and pel.element_type_id = pet.element_type_id
3217 and pet.classification_id = pec.classification_id
3218 and pec.classification_name = 'Alien/Expat Earnings'
3219 and ((c_year_end between pee.effective_start_date
3220 and pee.effective_end_date
3221 )
3222 or
3223 (pee.effective_end_date =
3224 (select max(pex.effective_end_date)
3225 from pay_element_entries_f pex
3226 where pex.assignment_id = paf.assignment_id
3227 and pex.effective_end_date between c_year_start
3228 and c_year_end)
3229 )
3230 )
3231 and pee.effective_end_date between pel.effective_start_date
3232 and pel.effective_end_date;
3233
3234 /*****
3235 the following cursor selects all the active assignments for the person
3236 in the calender year of the effective date
3237 ****/
3238
3239 cursor c_assignment_id(p_person_id in number ,
3240 p_effective_date in date ) is
3241 select person_id ,
3242 assignment_id
3243 from per_assignments_f paf
3244 where paf.person_id = p_person_id
3245 and paf.assignment_type <> 'B' --RLN
3246 and paf.effective_start_date <= TO_DATE(('12/31/' ||
3247 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3248 and paf.effective_end_date >= TO_DATE(('01/01/' ||
3249 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3250 and paf.effective_start_date = (select MAX(effective_start_date)
3251 from per_assignments_f
3252 where assignment_id =
3253 paf.assignment_id
3254 and effective_start_date <=
3255 TO_DATE(('12/31/' ||
3256 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
3257 order by person_id ,
3258 assignment_id;
3259
3260 /*****
3261 the following cursor selects all the details about the payroll information
3262 about the person.
3263 *****/
3264
3265 cursor c_person_payroll_info(p_person_id in number,
3266 p_income_code in varchar2,
3267 p_year in varchar2) is
3268 select pei_information5 income_code ,
3269 pei_information6 prev_er_treaty_ben_amt ,
3270 pei_information7 prev_er_treaty_ben_year
3271 from (select *
3272 from per_people_extra_info
3273 where information_type = 'PER_US_PAYROLL_DETAILS'
3274 and person_id = p_person_id )
3275 where pei_information7 = p_year
3276 and pei_information5 = p_income_code;
3277
3278 /*****
3279 the following cursor selects the primary assignment Id for the person
3280 in the calender year of the effective date. This cursor should always
3281 return 0 or 1 row as rownum < 2 has been yse
3282 *****/
3283 cursor c_person_assignment(p_person_id in number) is
3284 select distinct assignment_id
3285 from per_assignments_f paf,
3286 per_people_f ppf
3287 where ppf.person_id = paf.person_id
3288 and ppf.person_id = p_person_id
3289 and paf.assignment_type <> 'B' --RLN
3290 and ppf.effective_start_date <= TO_DATE(('12/31/' ||
3291 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3292 and ppf.effective_end_date >= TO_DATE(('01/01/' ||
3293 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3294 and ppf.effective_start_date = (select MAX(effective_start_date)
3295 from per_people_f
3296 where person_id = ppf.person_id
3297 and effective_start_date <=
3298 TO_DATE(('12/31/' ||
3299 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
3300 and paf.effective_start_date <= TO_DATE(('12/31/' ||
3301 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3302 and paf.effective_end_date >= TO_DATE(('01/01/' ||
3303 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3304 and paf.effective_start_date = (select MAX(effective_start_date)
3305 from per_assignments_f
3306 where assignment_id =
3307 paf.assignment_id
3308 and effective_start_date <=
3309 TO_DATE(('12/31/' ||
3310 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
3311 and paf.primary_flag = 'Y'
3312 and rownum < 2;
3313
3314 --
3315 -- The following cursor selects the work state of ther person
3316 --
3317 cursor c_work_state_cur(p_assign_id in number) is
3318 select nvl(hrlock.loc_information17
3319 ,hrlock.region_2) state
3320
3321 from hr_locations hrlock
3322 ,hr_soft_coding_keyflex hrsckf
3323 ,per_all_assignments_f paf
3324
3325 where paf.effective_start_date <=
3326 to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
3327 and paf.effective_end_date >=
3328 to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
3329 and paf.effective_start_date =
3330 (select max(effective_start_date)
3331 from per_assignments_f
3332 where assignment_id = paf.assignment_id
3333 and effective_start_date <=
3334 to_date(('12/31/'||to_char(p_effective_date,'YYYY'))
3335 ,'MM/DD/YYYY')
3336 )
3337 and paf.assignment_id = p_assign_id
3338 and paf.soft_coding_keyflex_id = hrsckf.soft_coding_keyflex_id
3339 and nvl(hrsckf.segment18,paf.location_id) = hrlock.location_id
3340 and rownum < 2;
3341 --
3342 -- Select the date Paid (cycle date as per windstar nomenclature) and
3343 -- date Earned(Last date of earning as per windstar nomenclature).
3344 --
3345 cursor c_date_paid_earned(p_person_id in number
3346 ,p_effective_date in date ) is
3347 select max(ppa.effective_date) date_paid ,
3348 max(ppa.date_earned) date_earned
3349 from pay_payroll_actions ppa
3350 ,pay_assignment_actions paa
3351 ,per_assignments_f paf
3352 where ppa.payroll_action_id = paa.payroll_action_id
3353 and paa.assignment_id = paf.assignment_id
3354 and ppa.action_status = 'C'
3355 and paa.action_status = 'C'
3356 and ppa.action_type in ('R','Q','I','B','V')
3357 and paf.person_id = p_person_id
3358 and paf.effective_start_date <= p_effective_date
3359 and ppa.effective_date <= p_effective_date;
3360 --
3361 -- Select the number of Days in a pay period (number of days in a pay
3362 -- cycle as per windstar nomenclature).
3363 --
3364 cursor c_days_in_cycle(p_person_id in number
3365 ,p_effective_date in date ) is
3366 select min(trunc((52/ number_per_fiscal_year) * 7)) days_in_cycle
3367 from per_time_periods ptp
3368 ,per_assignments_f paf
3369 ,per_time_period_types ptt
3370 where ptp.payroll_id = paf.payroll_id
3371 and ptp.period_type = ptt.period_type
3372 and paf.person_id = p_person_id
3373 and paf.effective_start_date <= p_effective_date;
3374
3375 --
3376 -- The following cursor selects the forecasted Income code for a given
3377 -- person_id and in a given year.
3378 --
3379 cursor c_forecasted_income_code(p_person_id in number
3380 ,p_assignment_id in number
3381 ,p_effective_date in date) is
3382 select pei_information5 income_code
3383 from per_people_extra_info
3384 where person_id = p_person_id
3385 and information_type = 'PER_US_INCOME_FORECAST'
3386 and pei_information8 = to_char(p_effective_date, 'YYYY');
3387
3388 --
3389 -- The following cursor selects the effective_end_date for all the assignments
3390 -- of a terminated employee.
3391 --
3392 cursor c_terminated_employee_asg(p_person_id in number) is
3393 SELECT paf.effective_end_date
3394 FROM per_people_f ppf,
3395 per_person_types ppt,
3396 per_person_type_usages_f pptu,
3397 per_assignments_f paf
3398 WHERE ppf.person_id = 57504
3399 -- and ppf.person_type_id = ppt.person_type_id
3400 AND ppt.system_person_type = 'EX_EMP'
3401 AND paf.person_id = ppf.person_id
3402 AND pptu.person_type_id = ppt.person_type_id
3403 AND pptu.person_id = ppf.person_id
3404 AND ppt.business_group_id = ppf.business_group_id
3405 AND pptu.effective_start_date BETWEEN paf.effective_start_date
3406 AND paf.effective_end_date
3407 AND paf.assignment_type <> 'B'
3408 AND pptu.effective_start_date BETWEEN ppf.effective_start_date
3409 AND ppf.effective_end_date;
3410
3411 begin
3412 hr_utility.set_location('Entering '||l_proc, 5);
3413 l_dimension_name := 'Assignment within Government Reporting Entity Year to Date';
3414
3415 l_year := to_number(to_char(p_effective_date, 'YYYY'));
3416 l_year_start:= to_date('01/01/'||to_char(p_effective_date, 'YYYY'),'MM/DD/YYYY');
3417 l_year_end := to_date('12/31/'||to_char(p_effective_date, 'YYYY'),'MM/DD/YYYY');
3418 l_prev_year := l_year - 1;
3419
3420 l_count := t_people_tab.count;
3421 l_err_count := t_error_tab.count ;
3422
3423 j := 1;
3424
3425 if l_count >= 1 then
3426 hr_utility.set_location(l_proc, 10);
3427
3428 l_sit_flag := true;
3429 for i in 1..l_count
3430 loop
3431 begin
3432 hr_utility.set_location(l_proc, 20);
3433 if (NVL(t_people_tab(i).validation_flag, ' ') <> '0') then
3434
3435 hr_utility.set_location(l_proc, 30);
3436
3437 l_sit_flag := true;
3438 l_person_id := t_people_tab(i).person_id;
3439 l_last_name := t_people_tab(i).last_name;
3440 l_first_name := t_people_tab(i).first_name;
3441 l_middle_names := t_people_tab(i).middle_names;
3442 l_national_identifier:= t_people_tab(i).national_identifier;
3443 l_date_of_birth := t_people_tab(i).date_of_birth;
3444
3445 for c_additional in c_person_additional_info(l_person_id)
3446 loop
3447 l_tax_residence_country_code :=
3448 c_additional.tax_res_country_code;
3449 hr_utility.set_location(l_proc, 40);
3450 end loop;
3451
3452 for c_ass in c_person_assignment(l_person_id)
3453 loop
3454 l_assignment_id := c_ass.assignment_id;
3455 hr_utility.set_location(l_proc, 50);
3456 end loop;
3457
3458 for c_state in c_work_state_cur(l_assignment_id)
3459 loop
3460 l_state := c_state.state;
3461 hr_utility.set_location(l_proc, 60);
3462 end loop;
3463
3464 open c_income_code_cursor( l_person_id,
3465 l_year_start,
3466 l_year_end
3467 );
3468
3469 l_income_code_count := 0;
3470
3471 loop
3472
3473 fetch c_income_code_cursor
3474 into l_c_income_code;
3475
3476 if c_income_code_cursor%notfound then
3477
3478 if (c_forecasted_income_code%isopen <> true) then
3479
3480 open c_forecasted_income_code (l_person_id,
3481 l_assignment_id,
3482 p_effective_date);
3483 end if;
3484
3485 fetch c_forecasted_income_code
3486 into l_c_income_code;
3487 -- Exclude this person if neither the
3488 -- Element Entry is attached not the
3489 -- Forecasted Income code is present
3490 exit when c_forecasted_income_code%notfound;
3491
3492 end if;
3493
3494 l_income_code_count := 1;
3495
3496 begin
3497 hr_utility.set_location(l_proc, 70);
3498 l_income_code := '';
3499 --
3500 -- The sql below decides if the respective earning entries
3501 -- attached to the assignment. Decide if the request is for
3502 -- the forecasted or the actual record. check here for
3503 -- forecasted vs actual record. select the data from the
3504 -- c_analyzed_data cursor. if a row is selected then it means
3505 -- that a record already exists.
3506 --
3507
3508 if (c_analyzed_data%isopen = true) then
3509 close c_analyzed_data;
3510 end if;
3511
3512 open c_analyzed_data(l_c_income_code
3513 ,l_person_id
3514 ,l_year);
3515 fetch c_analyzed_data
3516 into l_income_code
3517 ,l_exemption_code
3518 ,l_withholding_rate
3519 ,l_income_code_sub_type
3520 ,l_constant_addl_tax;
3521
3522 hr_utility.set_location(l_proc, 80);
3523
3524 l_balance := 0;
3525
3526 t_balance_tab(j).person_id := l_person_id;
3527 t_balance_tab(j).last_name := l_last_name;
3528 t_balance_tab(j).first_name := l_first_name;
3529 t_balance_tab(j).middle_names := l_middle_names;
3530 t_balance_tab(j).national_identifier :=l_national_identifier;
3531 t_balance_tab(j).date_of_birth := l_date_of_birth;
3532
3533 l_prev_amount := 0;
3534
3535 for c_payment in c_person_payroll_info
3536 (l_person_id ,
3537 l_c_income_code ,
3538 to_char(l_year)
3539 )
3540 loop
3541 l_prev_amount := c_payment.prev_er_treaty_ben_amt;
3542 end loop;
3543
3544 hr_utility.set_location(l_proc, 90);
3545
3546 if (length(l_c_income_code) > 2) then
3547 l_sub_type := substr(l_c_income_code, 3, 1);
3548 end if;
3549 hr_utility.set_location(l_proc, 100);
3550
3551 if (IsPayrollRun
3552 (l_person_id
3553 ,TO_DATE('31/12/'|| TO_CHAR(l_year),'DD/MM/YYYY')
3554 ,l_income_code) = false) then
3555
3556 hr_utility.set_location(l_proc, 110);
3557
3558 l_flag := 'F';
3559 l_balance := pqp_forecasted_balance
3560 (l_person_id ,
3561 l_c_income_code ,
3562 p_effective_date );
3563 t_balance_tab(j).gross_amount := l_balance;
3564 t_balance_tab(j).exemption_code := 0;
3565 t_balance_tab(j).withholding_allowance := 0;
3566 t_balance_tab(j).withholding_rate := 0;
3567 t_balance_tab(j).withheld_amount := 0;
3568 t_balance_tab(j).income_code_sub_type := l_sub_type;
3569 t_balance_tab(j).country_code :=
3570 l_tax_residence_country_code;
3571 t_balance_tab(j).cycle_date := null;
3572 t_balance_tab(j).tax_year := l_year;
3573 t_balance_tab(j).state_withheld_amount := 0;
3574 t_balance_tab(j).state_code := l_state;
3575 t_balance_tab(j).record_source := null;
3576 t_balance_tab(j).payment_type := 'Y';
3577 t_balance_tab(j).last_date_of_earnings := null;
3578 t_balance_tab(j).record_status := 'F';
3579 --
3580 -- How to calculate the last_date_of_earnings. This is the
3581 -- last date of payment check
3582 --
3583 t_balance_tab(j).prev_er_treaty_benefit_amount :=
3584 l_prev_amount ;
3585 t_balance_tab(j).person_id := l_person_id ;
3586 t_balance_tab(j).income_code :=
3587 SUBSTR(l_c_income_code, 1, 2);
3588 t_balance_tab(j).constant_addl_tax := 0 ;
3589 pqp_windstar_balance_validate
3590 ( p_in_data_rec => t_balance_tab(j) ,
3591 p_effective_date => p_effective_date ,
3592 p_out_mesg => l_out_mesg ,
3593 p_forecasted => true
3594 );
3595 hr_utility.set_location(l_proc, 120);
3596 else
3597 hr_utility.set_location(l_proc, 130);
3598 l_flag := 'A';
3599
3600 l_effective_date :=
3601 to_date('31/12/'||to_char(l_year),'DD/MM/YYYY');
3602
3603 for c_rec in c_terminated_employee_asg(l_person_id)
3604 loop
3605
3606 if c_rec.effective_end_date < l_effective_date then
3607 l_effective_date := c_rec.effective_end_date;
3608 end if;
3609
3610 end loop;
3611
3612 -- Gross Amount
3613 l_balance := 0;
3614 l_balance := pqp_balance
3615 (p_income_code => l_c_income_code
3616 ,p_dimension_name => null
3617 ,p_assignment_id => l_assignment_id
3618 ,p_effective_date => l_effective_date
3619 ,p_state_code => null
3620 ,p_fit_wh_bal_flag => 'N'
3621 ,p_balance_name => null)
3622 -
3623 pqp_balance
3624 (p_income_code => l_c_income_code
3625 ,p_dimension_name => null
3626 ,p_assignment_id => l_assignment_id
3627 ,p_effective_date => l_effective_date
3628 ,p_state_code => null
3629 ,p_fit_wh_bal_flag => 'P'
3630 ,p_balance_name => null
3631 );
3632 t_balance_tab(j).gross_amount := l_balance;
3633
3634 if (l_exemption_code) = '9' then
3635 l_exemption_code := '0';
3636 end if;
3637
3638 t_balance_tab(j).exemption_code := l_exemption_code ;
3639 t_balance_tab(j).withholding_allowance:= 0 ;
3640 t_balance_tab(j).withholding_rate :=
3641 lpad(to_char(nvl(l_withholding_rate, 0) * 10), 3, '0');
3642 t_balance_tab(j).constant_addl_tax:= l_constant_addl_tax;
3643
3644 l_balance := 0;
3645 l_balance := pqp_balance
3646 (p_income_code => l_c_income_code
3647 ,p_dimension_name => null
3648 ,p_assignment_id => l_assignment_id
3649 ,p_effective_date => l_effective_date
3650 ,p_state_code => null
3651 ,p_fit_wh_bal_flag => 'Y'
3652 ,p_balance_name => null
3653 );
3654
3655 t_balance_tab(j).withheld_amount := l_balance;
3656 t_balance_tab(j).income_code_sub_type := l_sub_type;
3657 t_balance_tab(j).country_code :=
3658 l_tax_residence_country_code;
3659
3660 for cdpe in c_date_paid_earned
3661 (l_person_id ,
3662 to_date('31/12/'||to_char(l_year),'DD/MM/YYYY')
3663 )
3664 loop
3665 t_balance_tab(j).cycle_date := cdpe.date_paid;
3666 t_balance_tab(j).last_date_of_earnings :=
3667 cdpe.date_earned ;
3668 end loop;
3669
3670 hr_utility.set_location(l_proc, 140);
3671
3672 t_balance_tab(j).tax_year := l_year ;
3673
3674 if (l_sit_flag = true) then
3675
3676 hr_utility.set_location(l_proc, 150);
3677
3678 l_balance := 0;
3679 l_balance := pqp_balance
3680 (p_income_code => null
3681 ,p_dimension_name =>'Person in JD within GRE Year to Date'
3682 ,p_assignment_id => l_assignment_id
3683 ,p_effective_date => l_effective_date
3684 ,p_state_code => l_state
3685 ,p_fit_wh_bal_flag => 'N'
3686 ,p_balance_name => 'SIT Alien Withheld'
3687 );
3688 t_balance_tab(j).state_withheld_amount := l_balance;
3689 l_sit_flag := false;
3690
3691 else
3692 hr_utility.set_location(l_proc, 160);
3693
3694 t_balance_tab(j).state_withheld_amount := 0;
3695 end if;
3696
3697 t_balance_tab(j).state_code := l_state;
3698 t_balance_tab(j).record_source := null;
3699 t_balance_tab(j).payment_type := 'Y';
3700 t_balance_tab(j).record_status := 'A';
3701
3702 if t_balance_tab(j).last_date_of_earnings is null then
3703 t_balance_tab(j).record_status := 'F';
3704 end if;
3705
3706 t_balance_tab(j).prev_er_treaty_benefit_amount
3707 := l_prev_amount ;
3708 t_balance_tab(j).person_id := l_person_id;
3709 t_balance_tab(j).income_code
3710 := substr(l_c_income_code, 1, 2);
3711 for cdic in c_days_in_cycle
3712 (l_person_id,
3713 to_date('31/12/'||to_char(l_year),'DD/MM/YYYY')
3714 )
3715 loop
3716 t_balance_tab(j).no_of_days_in_cycle
3717 := cdic.days_in_cycle;
3718 end loop;
3719
3720 hr_utility.set_location(l_proc, 170);
3721
3722 pqp_windstar_balance_validate
3723 (p_in_data_rec => t_balance_tab(j)
3724 ,p_effective_date => p_effective_date
3725 ,p_out_mesg => l_out_mesg
3726 ,p_forecasted => false
3727 );
3728
3729 hr_utility.set_location(l_proc, 180);
3730 end if;
3731 if (l_out_mesg is null) then
3732 --
3733 -- l_out_mesg means there is no failure. increment the
3734 -- counter and proceed for fetching of the next row from
3735 -- the respective cursor
3736 --
3737 j := j + 1;
3738 hr_utility.set_location(l_proc, 190);
3739 else --ELSE4}{
3740 hr_utility.set_location(l_proc, 200);
3741 -- Delete the current row in the PL/sql table. update the
3742 -- status in the pay_process_events table to reflect the
3743 -- status as DATA_VALIDATION_FAILED
3744 --
3745 if (pay_process_events_ovn_cursor%isopen = true) then
3746 close pay_process_events_ovn_cursor;
3747 end if;
3748
3749 l_process_event_id := null;
3750
3751 open pay_process_events_ovn_cursor(l_person_id ,
3752 p_source_type ,
3753 p_effective_date );
3754 loop
3755 fetch pay_process_events_ovn_cursor
3756 into l_process_event_id
3757 ,l_object_version_number
3758 ,l_assignment_id
3759 ,l_description;
3760
3761 hr_utility.set_location(l_proc, 210);
3762
3763 exit when pay_process_events_ovn_cursor%notfound;
3764
3765 pqp_process_events_errorlog
3766 (
3767 p_process_event_id1 => l_process_event_id ,
3768 p_object_version_number1 => l_object_version_number ,
3769 p_status1 => 'D' ,
3770 p_description1 =>
3771 SUBSTR(l_out_mesg ||
3772 l_description, 1, 240)
3773 );
3774
3775 end loop;
3776
3777 if (pay_process_events_ovn_cursor%isopen = true) then
3778 close pay_process_events_ovn_cursor;
3779 end if;
3780
3781 /* Added by tmehra for wf notification consolidation */
3782 if l_process_event_id is not null then
3783 l_err_count := l_err_count+1;
3784
3785 t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
3786 t_error_tab(l_err_count).process_event_id := l_process_event_id;
3787
3788 end if;
3789 /* Changes for wf notification consolidation ends */
3790
3791 t_balance_tab.delete(j) ;
3792 l_out_mesg := null;
3793 t_people_tab(i).validation_flag := '0';
3794 /* 0 indicates an Error */
3795 exit;
3796 hr_utility.set_location(l_proc, 260);
3797 end if; --ENDIF4}
3798 close c_analyzed_data;
3799 exception
3800 when OTHERS then
3801 hr_utility.set_location(l_proc, 270);
3802 if (pay_process_events_ovn_cursor%isopen = true) then
3803 close pay_process_events_ovn_cursor;
3804 end if;
3805 if (c_analyzed_data%isopen = true) then
3806 close c_analyzed_data;
3807 end if;
3808 l_out_mesg := SUBSTR('Error while processing 1042s ' ||
3809 TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
3810
3811 l_process_event_id := null;
3812
3813 open pay_process_events_ovn_cursor(l_person_id ,
3814 p_source_type ,
3815 p_effective_date );
3816 loop --LOOP3{
3817 fetch pay_process_events_ovn_cursor into
3818 l_process_event_id ,
3819 l_object_version_number ,
3820 l_assignment_id ,
3821 l_description ;
3822 exit when pay_process_events_ovn_cursor%notfound;
3823 hr_utility.set_location(l_proc, 280);
3824
3825 /* Update pay_process_events table with a status of 'D' */
3826
3827 pqp_process_events_errorlog
3828 (
3829 p_process_event_id1 =>l_process_event_id ,
3830 p_object_version_number1=>l_object_version_number,
3831 p_status1 => 'D' ,
3832 p_description1 => SUBSTR(l_out_mesg, 1, 240)
3833 );
3834
3835 end loop; --ENDLOOP3}
3836 close pay_process_events_ovn_cursor;
3837 if (t_balance_tab.exists(j)) then
3838 t_balance_tab.delete(j) ;
3839 end if;
3840
3841 /* Added by tmehra for wf notification consolidation */
3842 if l_process_event_id is not null then
3843 l_err_count := l_err_count+1;
3844
3845 t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
3846 t_error_tab(l_err_count).process_event_id := l_process_event_id;
3847
3848 end if;
3849
3850 /* Changes for wf notification consolidation ends */
3851
3852 l_out_mesg := null;
3853 t_people_tab(i).validation_flag := '0';
3854 t_people_tab(i).error_mesg :=
3855 SUBSTR('Error while processing 1042s details' ||
3856 l_out_mesg, 1, 240);
3857 l_out_mesg := null;
3858 exit;
3859 end; --END3}
3860
3861 exit when (c_income_code_cursor%notfound
3862 and c_forecasted_income_code%notfound);
3863
3864 end loop; --ENDLOOP2} c_income_code_cursor cursor
3865
3866 close c_income_code_cursor;
3867 close c_forecasted_income_code;
3868
3869 if l_income_code_count = 0 then
3870
3871 l_process_event_id := null;
3872
3873 open pay_process_events_ovn_cursor(l_person_id ,
3874 p_source_type ,
3875 p_effective_date );
3876 loop
3877 fetch pay_process_events_ovn_cursor into
3878 l_process_event_id ,
3879 l_object_version_number ,
3880 l_assignment_id ,
3881 l_description ;
3882 exit when pay_process_events_ovn_cursor%notfound;
3883 hr_utility.set_location(l_proc, 280);
3884
3885 /* Update pay_process_events table with a status of 'D' */
3886
3887 pqp_process_events_errorlog
3888 (p_process_event_id1 =>l_process_event_id ,
3889 p_object_version_number1=>l_object_version_number,
3890 p_status1 => 'D' ,
3891 p_description1 => 'No Alien Income or Forecast found'
3892 );
3893
3894 end loop;
3895
3896 /* Added by tmehra for wf notification consolidation */
3897 if l_process_event_id is not null then
3898 l_err_count := l_err_count+1;
3899
3900 t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
3901 t_error_tab(l_err_count).process_event_id := l_process_event_id;
3902
3903 end if;
3904 /* Changes for wf notification consolidation ends */
3905 close pay_process_events_ovn_cursor;
3906 l_out_mesg := null;
3907 t_people_tab(i).validation_flag := '0';
3908 t_people_tab(i).error_mesg :=
3909 SUBSTR('No Alien Income or Forecast found' ||
3910 l_out_mesg, 1, 240);
3911 l_out_mesg := null;
3912
3913 end if;
3914
3915 hr_utility.set_location(l_proc, 290);
3916
3917 end if; --ENDIF2} validation_flag = 0
3918
3919
3920 exception
3921 when OTHERS then
3922 hr_utility.set_location(l_proc, 300);
3923 l_out_mesg := SUBSTR(TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
3924 t_people_tab(i).validation_flag := '0';
3925 t_people_tab(i).error_mesg :=
3926 SUBSTR('Error while processing 1042s details' ||
3927 l_out_mesg, 1, 240);
3928 l_out_mesg := null;
3929 end; --END2}
3930 end loop; --ENDLOOP1}
3931 end if; /* END IF # 1 */ --ENDIF}
3932 hr_utility.set_location('Leaving '||l_proc, 310);
3933 exception --EXC1}{
3934 when OTHERS then
3935 hr_utility.set_location('Entering exc'||l_proc, 320);
3936 hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3937 hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3938 || 'pqp_windstar_balance_read. Error Code = ' || TO_CHAR(sqlcode) ||
3939 ' ' || sqlerrm);
3940 hr_utility.raise_error;
3941 end pqp_windstar_balance_read; --END1}
3942 /***************************************************************************
3943 name : pqp_windstar_visa_read
3944 Purpose : the following procedure is called from the main procedure. This
3945 returns the visa details.
3946 Arguments :
3947 in
3948 t_people_tab : PL/sql table contains the Personal details.
3949 This is passed a an I/P parameter as this
3950 procedure returns the visa details only
3951 for the assignments present in this
3952 table.
3953 p_effective_date : Effective date.
3954 out
3955 t_visa_tab : PL/sql table contains the visa details.
3956 in out
3957 t_error_tab : PL/sql table contains the error details.
3958
3959 Notes : private
3960 *****************************************************************************/
3961
3962 procedure pqp_windstar_visa_read
3963 (
3964 t_people_tab in out NOCOPY t_people_tab_type ,
3965 t_error_tab in out NOCOPY t_error_tab_type ,
3966 p_source_type in varchar2 ,
3967 p_effective_date in date ,
3968 t_visa_tab out NOCOPY t_visa_tab_type
3969 ) is
3970
3971 l_last_name per_all_people_f.last_name%type ;
3972 l_first_name per_all_people_f.first_name%type ;
3973 l_middle_names per_all_people_f.middle_names%type ;
3974 l_national_identifier per_all_people_f.national_identifier%type ;
3975 l_tax_residence_country_code varchar2(100) ;
3976 l_description varchar2(250) ;
3977 l_proc varchar2(72) := g_package||'pqp_windstar_visa_read' ;
3978 l_primary_activity varchar2(30) ;
3979 l_visa_start_date date ;
3980 l_visa_end_date date ;
3981 l_date_of_birth date ;
3982 l_01jan_date date ;
3983 l_31dec_date date ;
3984 l_prev_end_date date ;
3985 l_out_mesg out_mesg_type ;
3986 l_person_id per_all_people_f.person_id%type ;
3987 l_process_event_id number ;
3988 l_object_version_number per_all_people_f.object_version_number%type ;
3989 l_assignment_id number ;
3990 i number ;
3991 j number := 1 ;
3992 l_err_count number ;
3993 l_count number ;
3994 l_visa_found varchar2(10) := 'NONE';
3995 l_visa_err_mesg out_mesg_type ;
3996
3997 l_visa_count number :=0 ;
3998 l_skip_person boolean := false;
3999 /*****
4000 the following cursor selects all the visa details of a person. We are sending
4001 the status of the current visa record only to Windstar.
4002 *****/
4003 cursor c_person_visa_info(p_person_id in number,
4004 p_visa_no in varchar2) is
4005 select pei_information5 visa_type ,
4006 SUBSTR(pei_information6, 1, 20) visa_number ,
4007 fnd_date.canonical_to_date(pei_information7) visa_issue_date ,
4008 fnd_date.canonical_to_date(pei_information8) visa_expiry_date ,
4009 pei_information9 visa_category ,
4010 pei_information10 current_status
4011 from (select * from per_people_extra_info
4012 where information_type = 'PER_US_VISA_DETAILS' )
4013 where person_id = p_person_id
4014 and information_type = 'PER_US_VISA_DETAILS'
4015 and pei_information6 = NVL(p_visa_no, pei_information6)
4016 order by 6 desc, -- So that Y comes first
4017 3 asc,
4018 4 asc;
4019 /*****
4020 the cursor c_person_visit_visa_info gives the visa info of a particular
4021 person id.
4022 ****/
4023 cursor c_person_visit_visa_info(p_person_id in number ) is
4024 select pei_information5 purpose ,
4025 fnd_date.canonical_to_date(pei_information7) start_date ,
4026 fnd_date.canonical_to_date(pei_information8) end_date ,
4027 pei_information11 visa_number
4028 from (select * from per_people_extra_info
4029 where information_type = 'PER_US_VISIT_HISTORY'
4030 and person_id = p_person_id )
4031 order by 2 asc,
4032 3 asc;
4033
4034 /*****
4035 the cursor c_get_visa_count gives the visa info of a particular
4036 person id.
4037 ****/
4038
4039 cursor c_visa_count(p_person_id in number ) is
4040 select count(*) ct
4041 from
4042 (select *
4043 from per_people_extra_info
4044 where information_type = 'PER_US_VISA_DETAILS') visa
4045 where visa.person_id = p_person_id;
4046
4047
4048 /*****
4049 the cursor c_validate_visa_number gives the visa info of a particular
4050 person id.
4051 ****/
4052 cursor c_validate_visa_number(p_person_id in number ) is
4053 select visa.visa_number
4054 from
4055 (select *
4056 from per_people_extra_info
4057 where information_type = 'PER_US_ADDITIONAL_DETAILS'
4058 and pei_information12 = 'WINDSTAR') pei,
4059 (select person_id,
4060 SUBSTR(pei_information6, 1, 20) visa_number
4061 from per_people_extra_info
4062 where information_type = 'PER_US_VISA_DETAILS') visa
4063 where visa.person_id = pei.person_id
4064 and pei.person_id = p_person_id
4065 and not exists
4066 (select 'X'
4067 from per_people_extra_info
4068 where information_type = 'PER_US_VISIT_HISTORY'
4069 and person_id = visa.person_id
4070 and SUBSTR(pei_information11, 1, 20) = visa.visa_number
4071 );
4072
4073 begin
4074
4075 hr_utility.set_location('Entering '||l_proc, 5);
4076 l_count := t_people_tab.COUNT;
4077 l_err_count := t_error_tab.COUNT;
4078 l_01jan_date := TO_DATE('01/01/'|| TO_CHAR(p_effective_date, 'YYYY'),
4079 'DD/MM/YYYY');
4080 l_31dec_date := TO_DATE('31/12/' || TO_CHAR(p_effective_date, 'YYYY'),
4081 'DD/MM/YYYY');
4082 for i in 1..l_count
4083 loop --LOOP1{
4084 begin
4085
4086 hr_utility.set_location(l_proc, 10);
4087
4088 -- Get the errornous record count, Skip this person and raise the notification
4089
4090 l_skip_person := false;
4091 l_visa_err_mesg := '';
4092
4093 for c_rec in c_visa_count(t_people_tab(i).person_id)
4094 loop
4095
4096 l_visa_count := c_rec.ct;
4097
4098 end loop;
4099
4100 if l_visa_count > 1 then
4101 for c_rec in c_validate_visa_number(t_people_tab(i).person_id)
4102 loop
4103 l_visa_err_mesg := l_visa_err_mesg||' '||trim(c_rec.visa_number);
4104 l_skip_person := true;
4105 end loop;
4106 else
4107 l_skip_person := false;
4108 end if;
4109
4110 hr_utility.set_location(l_proc, 20);
4111
4112 if (NVL(t_people_tab(i).validation_flag, ' ') <> '0'
4113 and l_skip_person = false) then --IF1{
4114 hr_utility.set_location(l_proc, 30);
4115
4116 l_person_id := '' ;
4117 l_last_name := '' ;
4118 l_first_name := '' ;
4119 l_middle_names := '' ;
4120 l_national_identifier := '' ;
4121 l_date_of_birth := '' ;
4122
4123 l_person_id := t_people_tab(i).person_id ;
4124 l_last_name := t_people_tab(i).last_name ;
4125 l_first_name := t_people_tab(i).first_name ;
4126 l_middle_names := t_people_tab(i).middle_names ;
4127 l_national_identifier := t_people_tab(i).national_identifier;
4128 l_date_of_birth := t_people_tab(i).date_of_birth ;
4129 for c_additional in c_person_additional_info(l_person_id)
4130 loop
4131 l_tax_residence_country_code := c_additional.tax_res_country_code;
4132 end loop;
4133 hr_utility.set_location(l_proc, 40);
4134 l_prev_end_date := null;
4135
4136 l_visa_found := 'NONE';
4137
4138 for cpv in c_person_visit_visa_info(l_person_id )
4139 loop --LOOP2{
4140
4141 -- means Visit details are available
4142 l_visa_found := 'VISIT';
4143
4144 for c_person_visa in c_person_visa_info(t_people_tab(i).person_id ,
4145 cpv.visa_number )
4146 loop --LOOP3{
4147
4148 -- means Visa details are available
4149 l_visa_found := 'VISA';
4150
4151 begin
4152 hr_utility.set_location(l_proc, 50);
4153 t_visa_tab(j).person_id := l_person_id ;
4154 t_visa_tab(j).last_name := l_last_name ;
4155 t_visa_tab(j).first_name := l_first_name ;
4156 t_visa_tab(j).middle_names := l_middle_names ;
4157 t_visa_tab(j).national_identifier := l_national_identifier ;
4158 t_visa_tab(j).date_of_birth := l_date_of_birth ;
4159 t_visa_tab(j).tax_residence_country_code :=
4160 l_tax_residence_country_code ;
4161 t_visa_tab(j).visa_type := c_person_visa.visa_type ;
4162 t_visa_tab(j).j_category_code := c_person_visa.visa_category;
4163 t_visa_tab(j).visa_number := c_person_visa.visa_number ;
4164 t_visa_tab(j).primary_activity_code := cpv.purpose;
4165
4166 t_visa_tab(j).visa_start_date := cpv.start_date;
4167 t_visa_tab(j).visa_end_date :=
4168 NVL(cpv.end_date, c_person_visa.visa_expiry_date);
4169
4170 if (t_visa_tab(j).visa_end_date >
4171 c_person_visa.visa_expiry_date) then
4172 t_visa_tab(j).visa_end_date := c_person_visa.visa_expiry_date;
4173 end if;
4174
4175 pqp_windstar_visa_validate
4176 ( p_in_data_rec => t_visa_tab(j) ,
4177 p_effective_date => p_effective_date ,
4178 p_prev_end_date => l_prev_end_date ,
4179 p_out_mesg => l_out_mesg
4180 );
4181 if (l_out_mesg is null) then --IF3{
4182 /* Means there was no failure. Increment the counter and proceed for
4183 fetching of the next row from the respective cursor */
4184 l_prev_end_date := t_visa_tab(j).visa_end_date;
4185 j := j + 1;
4186 hr_utility.set_location(l_proc, 60);
4187 else --ELSE3}{
4188 /* Delete the current row in the PL/SQL table. Update the status in the
4189 pay_process_events table to reflect the status as DATA_VALIDATION_FAILED
4190 */
4191 hr_utility.set_location(l_proc, 70);
4192
4193 l_process_event_id := null;
4194
4195 open pay_process_events_ovn_cursor(l_person_id ,
4196 p_source_type ,
4197 p_effective_date );
4198 loop
4199 fetch pay_process_events_ovn_cursor into
4200 l_process_event_id ,
4201 l_object_version_number ,
4202 l_assignment_id ,
4203 l_description ;
4204 hr_utility.set_location(l_proc, 80);
4205 exit when pay_process_events_ovn_cursor%notfound;
4206 /* Update pay_process_events table */
4207 pqp_process_events_errorlog
4208 (
4209 p_process_event_id1 => l_process_event_id ,
4210 p_object_version_number1 => l_object_version_number ,
4211 p_status1 => 'D' ,
4212 p_description1 => SUBSTR(l_out_mesg, 1, 240)
4213 );
4214 hr_utility.set_location(l_proc, 90);
4215 hr_utility.set_location(l_proc, 100);
4216 end loop;
4217 close pay_process_events_ovn_cursor;
4218 if (t_visa_tab.exists(j)) then
4219 t_visa_tab.delete(j) ;
4220 end if;
4221 /* Added by tmehra for wf notification consolidation */
4222 if l_process_event_id is not null then
4223 l_err_count := l_err_count+1;
4224
4225 t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
4226 t_error_tab(l_err_count).process_event_id := l_process_event_id;
4227
4228 end if;
4229 /* Changes for wf notification consolidation ends */
4230
4231 l_out_mesg := null;
4232 t_people_tab(i).validation_flag := '0';
4233 exit;
4234 /*** The above EXIT is to make sure that we just do not process any more
4235 visa records of this person Id
4236 ***/
4237 end if; --ENDIF}
4238 exception
4239 when OTHERS then
4240 hr_utility.set_location(l_proc, 110);
4241 if (pay_process_events_ovn_cursor%isopen = true) then
4242 close pay_process_events_ovn_cursor;
4243 end if;
4244 l_out_mesg := SUBSTR(TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
4245
4246 l_process_event_id := null;
4247
4248 open pay_process_events_ovn_cursor(l_person_id ,
4249 p_source_type ,
4250 p_effective_date );
4251 loop
4252 fetch pay_process_events_ovn_cursor into
4253 l_process_event_id ,
4254 l_object_version_number ,
4255 l_assignment_id ,
4256 l_description ;
4257 hr_utility.set_location(l_proc, 120);
4258 exit when pay_process_events_ovn_cursor%notfound;
4259
4260 /* Update pay_process_events table with a status of 'D' */
4261
4262 pqp_process_events_errorlog
4263 (
4264 p_process_event_id1 => l_process_event_id ,
4265 p_object_version_number1 => l_object_version_number ,
4266 p_status1 => 'D' ,
4267 p_description1 => SUBSTR(l_out_mesg, 1, 240)
4268 );
4269 hr_utility.set_location(l_proc, 130);
4270
4271 end loop;
4272 close pay_process_events_ovn_cursor;
4273 if (t_visa_tab.exists(j)) then
4274 t_visa_tab.delete(j) ;
4275 end if;
4276 hr_utility.set_location(l_proc, 140);
4277
4278 /* Added by tmehra for wf notification consolidation */
4279 if l_process_event_id is not null then
4280 l_err_count := l_err_count+1;
4281
4282 t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
4283 t_error_tab(l_err_count).process_event_id := l_process_event_id;
4284
4285 end if;
4286
4287 /* Changes for wf notification consolidation ends */
4288
4289 l_out_mesg := null;
4290 t_people_tab(i).validation_flag := '0';
4291 t_people_tab(i).error_mesg :=
4292 SUBSTR('Error while processing visa details' || l_out_mesg,
4293 1, 240);
4294 l_out_mesg := null;
4295 exit;
4296 end;
4297 exit;
4298 end loop; --LOOP3}
4299 end loop; --LOOP2}
4300
4301 if (l_visa_found = 'NONE'
4302 or l_visa_found = 'VISIT') then
4303
4304 if l_visa_found = 'NONE' then
4305 l_visa_err_mesg := 'Employee visit history details not found';
4306 else
4307 l_visa_err_mesg := 'Employee VISA details not found';
4308 end if;
4309
4310 l_process_event_id := null;
4311
4312 open pay_process_events_ovn_cursor(l_person_id ,
4313 p_source_type ,
4314 p_effective_date );
4315 loop
4316 fetch pay_process_events_ovn_cursor into
4317 l_process_event_id ,
4318 l_object_version_number ,
4319 l_assignment_id ,
4320 l_description ;
4321 exit when pay_process_events_ovn_cursor%notfound;
4322 hr_utility.set_location(l_proc, 280);
4323
4324 /* Update pay_process_events table with a status of 'D' */
4325
4326 pqp_process_events_errorlog
4327 (p_process_event_id1 =>l_process_event_id ,
4328 p_object_version_number1=>l_object_version_number,
4329 p_status1 => 'D' ,
4330 p_description1 => l_visa_err_mesg
4331 );
4332
4333 end loop;
4334
4335 close pay_process_events_ovn_cursor;
4336
4337 /* Added by tmehra for wf notification consolidation */
4338 if l_process_event_id is not null then
4339 l_err_count := l_err_count+1;
4340
4341 t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
4342 t_error_tab(l_err_count).process_event_id := l_process_event_id;
4343
4344 end if;
4345
4346 /* Changes for wf notification consolidation ends */
4347
4348 l_out_mesg := null;
4349 t_people_tab(i).validation_flag := '0';
4350 t_people_tab(i).error_mesg :=
4351 SUBSTR(l_visa_err_mesg ||
4352 l_out_mesg, 1, 240);
4353 l_out_mesg := null;
4354
4355 end if;
4356
4357 else -- ENDIF1}
4358
4359 if l_skip_person = true then
4360
4361 l_visa_err_mesg := 'Visa Visit/Purpose History missing for the VISA:'||
4362 l_visa_err_mesg;
4363
4364 l_process_event_id := null;
4365
4366 open pay_process_events_ovn_cursor(l_person_id ,
4367 p_source_type ,
4368 p_effective_date );
4369 loop
4370 fetch pay_process_events_ovn_cursor into
4371 l_process_event_id ,
4372 l_object_version_number ,
4373 l_assignment_id ,
4374 l_description ;
4375 exit when pay_process_events_ovn_cursor%notfound;
4376 hr_utility.set_location(l_proc, 280);
4377
4378 /* Update pay_process_events table with a status of 'D' */
4379
4380 pqp_process_events_errorlog
4381 (p_process_event_id1 =>l_process_event_id ,
4382 p_object_version_number1=>l_object_version_number,
4383 p_status1 => 'D' ,
4384 p_description1 => SUBSTR(l_visa_err_mesg,1,240)
4385 );
4386
4387 end loop;
4388
4389 close pay_process_events_ovn_cursor;
4390
4391 /* Added by tmehra for wf notification consolidation */
4392 if l_process_event_id is not null then
4393 l_err_count := l_err_count+1;
4394
4395 t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
4396 t_error_tab(l_err_count).process_event_id := l_process_event_id;
4397 end if;
4398
4399 /* Changes for wf notification consolidation ends */
4400
4401 l_out_mesg := null;
4402 t_people_tab(i).validation_flag := '0';
4403 t_people_tab(i).error_mesg :=
4404 SUBSTR(l_visa_err_mesg ||
4405 l_out_mesg, 1, 240);
4406 l_out_mesg := null;
4407
4408 end if;
4409
4410 end if; --ENDIF1}
4411 exception
4412 when OTHERS then
4413 hr_utility.set_location(l_proc, 150);
4414 l_out_mesg := SUBSTR(TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
4415 t_people_tab(i).validation_flag := '0';
4416 t_people_tab(i).error_mesg :=
4417 SUBSTR('Error while processing visa details' || l_out_mesg,
4418 1, 240);
4419 l_out_mesg := null;
4420 end;
4421
4422 end loop; --LOOP1 }
4423 hr_utility.set_location('Leaving '||l_proc, 160);
4424 exception
4425 when OTHERS then
4426 hr_utility.set_location('Entering exc'||l_proc, 170);
4427 hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4428 hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
4429 || 'pqp_windstar_visa_read. Error Code = ' || TO_CHAR(sqlcode) ||
4430 ' ' || sqlerrm);
4431 hr_utility.raise_error;
4432 end pqp_windstar_visa_read;
4433 /****************************************************************************
4434 name : pqp_read_public
4435 Purpose : the following is the main procedure that is called from a
4436 wrapper script. This procedure returns 3 tables.
4437 Arguments :
4438 in
4439 p_selection_criterion : if the user wants to select all records,
4440 or the records in the PAY_PROCESS_EVENTS table,
4441 or a specifice national_identifier.
4442 p_effective_date : Effective date.
4443 out
4444 p_batch_size : out NOCOPY number gives the batch size
4445 t_people_tab : PL/sql table contains personal_details
4446 t_balance_tab : PL/sql table contains the balance details
4447 p_visa_tab : PL/sql table contains the visa details
4448 Notes : public
4449 ****************************************************************************/
4450
4451 procedure pqp_read_public
4452 (
4453 p_selection_criterion in varchar2 ,
4454 p_effective_date in date ,
4455 p_batch_size out NOCOPY number ,
4456 t_people_tab out NOCOPY t_people_tab_type ,
4457 t_balance_tab out NOCOPY t_balance_tab_type ,
4458 t_visa_tab out NOCOPY t_visa_tab_type ,
4459 p_person_read_count out NOCOPY number ,
4460 p_person_err_count out NOCOPY number
4461 )
4462 is
4463
4464 /*****
4465 This is the definition of the table of the t_error_rec_type record type
4466 the record and the table definition is being added to consolidate
4467 the wf (workflow) notification logic at one place.
4468 Added by tmehra 20-Oct-2003.
4469 *****/
4470
4471 l_count number := 0 ;
4472
4473 l_proc varchar2(72) := g_package||'pqp_read_public' ;
4474 l_person_read_count number := 0 ;
4475 l_person_err_count number := 0 ;
4476
4477 -- added by tmehra for wf notification consolidation
4478 t_error_tab t_error_tab_type ;
4479
4480 begin
4481 hr_utility.set_location('Entering:'||l_proc, 5);
4482
4483 /*****
4484 raise error message as Selection Criterion cannot be null
4485 ******/
4486
4487 if (p_selection_criterion is null) then
4488 hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
4489 hr_utility.set_message_token('ARGUMENT', 'Selection Criterion');
4490 hr_utility.set_message_token('API_NAME',
4491 'pqp_alien_expat_taxation_pkg.pqp_read_public');
4492 hr_utility.raise_error;
4493 end if;
4494 begin
4495 hr_utility.set_location(l_proc, 10);
4496 /*****
4497 call pqp_windstar_person_read procedure to read all the information about
4498 the person into PL/sql t_people_tab table.
4499 ******/
4500
4501 pqp_windstar_person_read(p_selection_criterion=> p_selection_criterion ,
4502 p_source_type =>'PQP_US_ALIEN_WINDSTAR',
4503 p_effective_date => p_effective_date ,
4504 t_people_tab => t_people_tab ,
4505 t_error_tab => t_error_tab ,
4506 p_person_read_count => l_person_read_count ,
4507 p_person_err_count => l_person_err_count );
4508 hr_utility.set_location(l_proc, 20);
4509
4510 p_person_read_count := l_person_read_count;
4511 p_person_err_count := l_person_err_count;
4512
4513 /* Call the pqp_windstar_person_read and get all the visa details of the
4514 assignments selected in the first procedure*/
4515
4516 /*****
4517 call pqp_windstar_visa_read procedure to read all the information about
4518 the visa into PL/sql t_visa_tab table.
4519 ******/
4520
4521 pqp_windstar_visa_read(t_people_tab ,
4522 t_error_tab ,
4523 'PQP_US_ALIEN_WINDSTAR' ,
4524 p_effective_date ,
4525 t_visa_tab );
4526 hr_utility.set_location(l_proc, 30);
4527
4528 /*****
4529 call pqp_windstar_balance_read procedure to read all the information about
4530 the balance into PL/sql t_balance_tab table.
4531 ******/
4532 pqp_windstar_balance_read(t_people_tab ,
4533 t_error_tab ,
4534 'PQP_US_ALIEN_WINDSTAR' ,
4535 p_effective_date ,
4536 t_balance_tab );
4537 hr_utility.set_location(l_proc, 40);
4538
4539 l_count := t_error_tab.COUNT;
4540
4541 /*****
4542 the following code has been added to consolidate the wf notifications.
4543 tmehra 20-OCT-2003
4544 ******/
4545
4546 for i in 1..l_count
4547 loop
4548 pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
4549 (p_process_event_id => t_error_tab(i).process_event_id,
4550 p_tran_type => 'READ' ,
4551 p_tran_date => SYSDATE ,
4552 p_itemtype => 'PQPALNTF' ,
4553 p_process_name => 'WIN_PRC' ,
4554 p_alien_transaction_id => null ,
4555 p_assignment_id => null
4556 ) ;
4557 end loop;
4558
4559 exception
4560 when OTHERS then
4561 hr_utility.set_location('Entering exception:'||l_proc, 50);
4562 hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4563 hr_utility.set_message_token('2', 'Error in '
4564 || 'pqp_alien_expat_taxation_pkg.pqp_read_public. Error '
4565 || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
4566 hr_utility.raise_error;
4567 end;
4568 hr_utility.set_location(l_proc, 60);
4569
4570 begin
4571 open c_pay_action_parameter;
4572 l_batch_size := null;
4573 loop
4574 fetch c_pay_action_parameter
4575 into l_batch_size;
4576 exit when c_pay_action_parameter%notfound;
4577 end loop;
4578 p_batch_size := l_batch_size;
4579 close c_pay_action_parameter;
4580 hr_utility.set_location('Leaving:'||l_proc, 70);
4581 exception
4582 when OTHERS then
4583 hr_utility.set_location(l_proc, 80);
4584 p_batch_size := null;
4585 end;
4586 end pqp_read_public;
4587 /********************************************************************
4588 name : update_pay_process_events
4589 Purpose : the following function is called from any wrapper script.
4590 This updates pay_process_events and changes the status.
4591 Arguments :
4592 in
4593 p_person_id : Person Id
4594 p_effective_date : Effective date.
4595 p_source_type : source of Request. Normally Windstar
4596 p_status : the final status of record being updated. read,
4597 DATE_VALIDATION_FAILED etc.
4598 p_desc : Description to be appended
4599 out NOCOPY : none
4600 Notes : public
4601 exception HANDLING???
4602 *************************************************************************/
4603 procedure update_pay_process_events
4604 (
4605 p_person_id in number ,
4606 p_effective_date in date ,
4607 p_source_type in varchar2 ,
4608 p_status in varchar2 ,
4609 p_desc in varchar2
4610 )
4611 is
4612 l_process_event_id number ;
4613 l_object_version_number number ;
4614 l_assignment_id number ;
4615 l_description varchar2(250);
4616 l_proc varchar2(72) := g_package||'update_pay_process_events' ;
4617
4618 begin
4619 hr_utility.set_location('Entering:'||l_proc, 5);
4620 if (pay_process_events_ovn_cursor%isopen = true) then
4621 close pay_process_events_ovn_cursor;
4622 end if;
4623 hr_utility.set_location(l_proc, 10);
4624 for ppeoc1 in pay_process_events_ovn_cursor(p_person_id ,
4625 p_source_type ,
4626 p_effective_date)
4627 loop
4628 hr_utility.set_location(l_proc, 20);
4629 l_process_event_id := ppeoc1.process_event_id ;
4630 l_object_version_number := ppeoc1.object_version_number;
4631 l_assignment_id := ppeoc1.assignment_id ;
4632 l_description := ppeoc1.description ;
4633
4634 /* Update pay_process_events table */
4635 pay_ppe_api.update_process_event
4636 ( p_validate => false ,
4637 p_status => p_status ,
4638 p_description =>
4639 SUBSTR('Record Read | '|| p_desc || l_description, 1, 240),
4640 p_process_event_id => l_process_event_id ,
4641 p_object_version_number => l_object_version_number
4642 );
4643 hr_utility.set_location(l_proc, 30);
4644 end loop;
4645 hr_utility.set_location('Leaving:'||l_proc, 50);
4646 end update_pay_process_events;
4647 /****************************************************************************
4648 name : pqp_windstar_reconcile
4649 Purpose : This procedure reconciles data in pay_process_events table.
4650 Arguments : none
4651 Notes : public
4652 ****************************************************************************/
4653 procedure pqp_windstar_reconcile(p_assignment_id in number ,
4654 p_effective_date in date ,
4655 p_source_type in varchar ,
4656 p_process_event_id out NOCOPY number ,
4657 p_object_version_number out NOCOPY number ) is
4658 cursor c_pay_process_events(p_assignment_id in number ,
4659 p_effective_date in date ,
4660 p_source_type in varchar2 ) is
4661 select process_event_id,
4662 object_version_number
4663 from pay_process_events
4664 where assignment_id = p_assignment_id
4665 and change_type = p_source_type
4666 and status in ('R', 'C')
4667 order by status asc;
4668
4669 /****
4670 This cursor will select all the rows for an assignment with a status of read
4671 or complete. order by asc has been used so that cursor selects all the rows
4672 with status 'C' first, and then selects all the rows with status = 'R'. in
4673 reconciliation, we will try to reconcile records with status with 'R' first,
4674 and then records with status = 'C'. Therefore if pa_process_events table has
4675 some rows with status = 'R' as well as 'C', then rows with the status = 'R'
4676 will be fetched in the end. We can this way return the process event Id
4677 with status 'R'. Otherwise we will return the process event id of a row with
4678 a status of 'C'.This cursor is to make sure that the assignment exists in
4679 pay_process_events table.
4680
4681 Status in 'C' was added on Oct 13, 2000 after discussion with Subbu.
4682 This will ensure that reconciliation occurs properly.
4683
4684 --- Nocopy changes. Added the exception block and Nullified the
4685 the process_event_id. Did not raise the exception since the
4686 the null process_event_id is being handled in the calling
4687 procedure and a proper notification is raised indicating
4688 that the Assignment is not reconciled.
4689 ****/
4690
4691 l_process_event_id number;
4692 l_object_version_number number;
4693 l_proc varchar2(72) := g_package || 'pqp_windstar_reconcile';
4694 begin
4695 hr_utility.set_location('Entering ' || l_proc, 10);
4696 l_process_event_id := null;
4697 for cppe in c_pay_process_events(p_assignment_id ,
4698 p_effective_date ,
4699 p_source_type )
4700 loop
4701 hr_utility.set_location(l_proc, 20);
4702 l_process_event_id := cppe.process_event_id ;
4703 l_object_version_number := cppe.object_version_number;
4704 end loop;
4705 hr_utility.set_location('Leaving ' || l_proc, 30);
4706 p_process_event_id := l_process_event_id ;
4707 p_object_version_number := l_object_version_number;
4708
4709 -- Added by tmehra for nocopy changes Feb'03
4710
4711 exception
4712 when OTHERS then
4713 hr_utility.set_location('Entering excep:'||l_proc, 35);
4714 p_process_event_id := null;
4715 p_object_version_number := null;
4716
4717 end pqp_windstar_reconcile;
4718
4719
4720 /****************************************************************************
4721 name : pqp_write_public
4722 Purpose : the procedure write data into PQP_ANALYZED_ALIEN_DATA,
4723 PQP_ANALYZED_ALIEN_DETAILS, and PQP_ANALYZED_ALIEN_DATA tables.
4724 Arguments :
4725 in
4726 Notes : public
4727 ****************************************************************************/
4728 procedure pqp_write_public
4729 (p_id in number
4730 ,p_last_name in varchar2
4731 ,p_first_name in varchar2
4732 ,p_middle_names in varchar2
4733 ,p_system_id_number in number
4734 ,p_social_security_number in varchar2
4735 ,p_institution_indiv_id in varchar2
4736 ,p_date_of_birth in date
4737 ,p_taxyear in number
4738 ,p_income_code in varchar2
4739 ,p_withholding_rate in varchar2
4740 ,p_scholarship_type in varchar2
4741 ,p_exemption_code in varchar2
4742 ,p_maximum_benefit in number
4743 ,p_retro_lose_on_amount in number
4744 ,p_date_benefit_ends in date
4745 ,p_retro_lose_on_date in number
4746 ,p_residency_status in varchar2
4747 ,p_date_becomes_ra in date
4748 ,p_target_departure_date in date
4749 ,p_date_record_created in date
4750 ,p_tax_residence_country_code in varchar2
4751 ,p_date_treaty_updated in date
4752 ,p_exempt_fica in number
4753 ,p_exempt_student_fica in number
4754 ,p_add_wh_for_nra_whennotreaty in number
4755 ,p_amount_of_addl_withholding in number
4756 ,p_personal_exemption in varchar2
4757 ,p_add_exemptions_allowed in number
4758 ,p_days_in_usa in number
4759 ,p_eligible_for_whallowance in number
4760 ,p_treatybenefits_allowed in number
4761 ,p_treatybenefit_startdate in date
4762 ,p_ra_effective_date in date
4763 ,p_state_code in varchar2
4764 ,p_state_honours_treaty in number
4765 ,p_ytd_payments in number
4766 ,p_ytd_w2payments in number
4767 ,p_ytd_withholding in number
4768 ,p_ytd_whallowance in number
4769 ,p_ytd_treaty_payments in number
4770 ,p_ytd_treaty_withheld_amts in number
4771 ,p_record_source in varchar2
4772 ,p_visa_type in varchar2
4773 ,p_jsub_type in varchar2
4774 ,p_primary_activity in varchar2
4775 ,p_nus_countrycode in varchar2
4776 ,p_citizenship in varchar2
4777 ,p_constant_additional_tax in number
4778 ,p_out_of_system_treaty in number
4779 ,p_amount_of_addl_wh_type in varchar2
4780 ,p_error_indicator in varchar2
4781 ,p_error_text in varchar2
4782 ,p_date_w4_signed in date
4783 ,p_date_8233_signed in date
4784 ,p_reconcile in boolean
4785 ,p_effective_date in date
4786 ,p_current_analysis in number
4787 ,p_forecast_income_code in varchar2
4788 ,p_error_message out nocopy varchar2
4789 ) is
4790
4791 t_balance_tab pqp_alien_expat_taxation_pkg.t_balance_tab_type;
4792 l_retro_lose_ben_amt_mesg pqp_alien_transaction_data.ERROR_TEXT%type;
4793 l_retro_lose_ben_date_mesg pqp_alien_transaction_data.ERROR_TEXT%type;
4794 l_income_code_change_mesg pqp_alien_transaction_data.ERROR_TEXT%type;
4795 l_current_analysis_mesg pqp_alien_transaction_data.ERROR_TEXT%type;
4796
4797 l_windstar_yes number := -1;
4798 l_windstar_no number := 0;
4799 l_alien_transaction_id number;
4800 l_analyzed_data_details_id number;
4801 l_assignment_id number;
4802 l_object_version_number number;
4803 l_analyzed_data_id number;
4804 l_batch_size number;
4805 l_person_id number;
4806 l_fed_tax_id number;
4807 l_fed_tax_ovn number;
4808 l_transaction_ovn number;
4809 l_analyzed_data_ovn number;
4810 l_analyzed_det_ovn number;
4811 l_process_event_id number;
4812 l_process_ovn number;
4813 l_atd_ovn number;
4814 l_stat_trans_audit_id number;
4815 l_cpa_assignment_id number;
4816 l_pri_assgn number;
4817 l_maximum_benefit number;
4818 l_withholding_rate number;
4819 l_amount_of_addl_withholding number;
4820
4821 l_personal_exemption varchar2(1);
4822 l_treaty_ben_allowed_flag varchar2(5);
4823 l_retro_lose_ben_amt_flag varchar2(5);
4824 l_retro_lose_ben_date_flag varchar2(5);
4825 l_nra_exempt_from_fica varchar2(5);
4826 l_student_exempt_from_fica varchar2(5);
4827 l_wthldg_allow_eligible_flag varchar2(5);
4828 l_addl_withholding_flag varchar2(5);
4829 l_state_honors_treaty_flag varchar2(5);
4830 l_assignment_exists varchar2(5);
4831 l_error_indicator varchar2(30) := 'ERROR';
4832 l_notification_sent varchar2(1);
4833 l_current_analysis varchar2(5);
4834 l_forecast_income_code varchar2(30);
4835
4836 l_period_type varchar2(10);
4837 l_logic_state varchar2(100);
4838 l_message varchar2(255);
4839 l_error_message varchar2(4000);
4840 l_err_message varchar2(4000);
4841 l_retro_lose_ben_amt_flag_old varchar2(5);
4842 l_retro_lose_ben_date_flag_old varchar2(5);
4843 l_additional_amt number;
4844
4845 l_eff_w4_date date;
4846 l_reco_flag boolean;
4847
4848 l_date_8233_signed date;
4849
4850 l_retro_lost boolean;
4851 l_entry_end_date date;
4852 l_ppe_status_n_recs boolean := false;
4853 l_proc constant varchar2(150) := g_package ||'pqp_write_public';
4854
4855 cursor c_person_assgn(p_person_id in number
4856 ,p_effective_date in date
4857 ,p_source_type in varchar) is
4858 select distinct
4859 paf.assignment_id
4860 from per_assignments_f paf,
4861 pay_process_events ppe
4862 where paf.person_id = p_person_id
4863 and paf.effective_start_date <=
4864 to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4865 and paf.effective_end_date >=
4866 to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4867 and paf.effective_start_date =
4868 (select max(effective_start_date)
4869 from per_assignments_f
4870 where assignment_id = paf.assignment_id
4871 and effective_start_date <=
4872 to_date(('12/31/'||to_char(p_effective_date, 'YYYY'))
4873 ,'MM/DD/YYYY'
4874 )
4875 )
4876 and paf.assignment_id = ppe.assignment_id
4877 and ppe.status in ( 'R','C')
4878 and ppe.change_type = p_source_type;
4879
4880 -- In the above sql statement (cursor c_person_assgn) the pay_process_events
4881 -- table is used due to the following reasons.
4882 --
4883 -- 1. It will select only those assignments that have a status of read. So if a new
4884 -- assignment is created for a person, and that assignment is with a status
4885 -- of NOT_READ, then no record is created in pqp_us_analyzed_data table for
4886 -- that assignment.
4887 --
4888 -- 2. Similarly if an assignment is deleted for a person, then still a record
4889 -- is created for that assignment in the pqp_us_analyzed_data table for the
4890 -- reconciliation purposes.
4891 -- Status = 'c' was added to make sure that we reconcile even those cases that
4892 -- have already been reconciled. This was added was discussion with Subbu on
4893 -- Oct 13, 2000. This way if someone reexports data in Windstar, we will not have
4894 -- any problem.
4895 --
4896
4897 --
4898 -- The following cursor select the person_id for a given SSN
4899 --
4900 cursor c_person_ssn(p_social_security_number in varchar2) is
4901 select person_id
4902 from per_all_people_f
4903 where national_identifier = p_social_security_number
4904 and rownum =1;
4905 --
4906 -- The following cursor verifies whether an assignment exists in the given tax
4907 -- year in pqp_analyzed_alien_data table or not.
4908 --
4909 cursor c_assign_exists(p_assignment_id in number
4910 ,p_tax_year in number) is
4911 select analyzed_data_id
4912 ,object_version_number
4913 from pqp_analyzed_alien_data
4914 where assignment_id = p_assignment_id
4915 and tax_year = p_tax_year;
4916 --
4917 -- The following cursor verifies whether an income_code exists in
4918 -- pqp_analyzed_alien_details table for a given analyzed_data_id. The
4919 -- assumption is that a single row will be present for an income code
4920 -- for an analyzed_data_id at a point in time.
4921 --
4922 cursor c_analyzed_det_exists(p_analyzed_data_id in number
4923 ,p_income_code in varchar2 ) is
4924 select analyzed_data_details_id
4925 ,object_version_number
4926 ,retro_lose_ben_amt_flag
4927 ,retro_lose_ben_date_flag
4928 from pqp_analyzed_alien_details
4929 where analyzed_data_id = p_analyzed_data_id
4930 and income_code = p_income_code;
4931 --
4932 -- Converts Oracle Pay periods to Windstar Pay periods
4933 --
4934 cursor c_winstar_oracle_pay_period(p_lookup_code in varchar2) is
4935 select lookup_code,
4936 meaning
4937 from hr_lookups
4938 where lookup_type = 'PQP_US_WIND_ORA_PERIODS'
4939 and lookup_code = p_lookup_code ;
4940 --
4941 -- The following cursor selects OVN and PK from
4942 -- PQP_ALIEN_TRANSACTION_DATA table
4943 --
4944 cursor c_atd(p_alien_transaction_id in number) is
4945 select object_version_number
4946 from pqp_alien_transaction_data
4947 where alien_transaction_id = p_alien_transaction_id;
4948 --
4949 -- The following cursor finds the effective end date for a Person.
4950 --
4951 cursor c_person(p_person_id in number
4952 ,p_effective_date in date ) is
4953 select MAX(effective_end_date) effective_end_date
4954 from per_people_f ppf
4955 ,per_person_types ppt
4956 where ppf.person_id = p_person_id
4957 and ppf.person_type_id = ppt.person_type_id
4958 and ppt.system_person_type in ('EMP', 'EX_EMP') -- RLN 7039307
4959 and ppf.effective_start_date <=
4960 to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4961 and ppf.effective_end_date >=
4962 to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY');
4963 --
4964 -- The following cursor finds the primary assignment id for a person.
4965 --
4966 cursor c_pri_assgn(p_person_id in number
4967 ,p_effective_date in date ) is
4968 select distinct
4969 paf.assignment_id
4970 from per_assignments_f paf
4971 where paf.person_id = p_person_id
4972 and paf.effective_start_date <=
4973 to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4974 and paf.effective_end_date >=
4975 to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4976 and paf.effective_start_date = (select max(effective_start_date)
4977 from per_assignments_f
4978 where assignment_id =
4979 paf.assignment_id
4980 and effective_start_date <=
4981 TO_DATE(('12/31/' ||
4982 TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'));
4983
4984 --
4985 -- The following cursor fetches the latest transaction from
4986 -- PQP_ALIEN_TRANSACTION_DATA table
4987 --
4988 cursor c_get_per_trans(c_person_id number) is
4989 select patd.date_w4_signed
4990 ,patd.personal_exemption
4991 ,patd.addl_exemption_allowed
4992 ,patd.addl_withholding_amt
4993 ,patd.constant_addl_tax
4994 ,patd.current_residency_status
4995 from pqp_alien_transaction_data patd
4996 where person_id = c_person_id
4997 and alien_transaction_id =
4998 (select MAX(patd1.alien_transaction_id)
4999 from PQP_ALIEN_TRANSACTION_DATA patd1
5000 where patd.person_id=patd1.person_id
5001 having tax_year =max(tax_year)
5002 group by tax_year);
5003
5004 l_get_per_trans c_get_per_trans%rowtype;
5005
5006
5007 --
5008 -- The following cursor fetches the latest element attached (element entry)
5009 -- to the person.
5010 --
5011 cursor c_get_element(p_person_id in number
5012 ,p_effective_date in date
5013 ,p_income_code in varchar2) is
5014
5015 select pee.element_entry_id element_entry_id,
5016 pet.element_name element_name,
5017 pee.effective_start_date entry_start_date,
5018 nvl(pet.element_information1, ' ') element_income_code
5019
5020 from per_all_assignments_f paf,
5021 per_all_people_f ppf,
5022 pay_element_entries_f pee,
5023 pay_element_links_f pel,
5024 pay_element_types_f pet,
5025 pay_element_classifications pec
5026
5027 where paf.person_id = ppf.person_id
5028 and paf.business_group_id = ppf.business_group_id
5029 and ppf.person_id = p_person_id
5030 and pec.classification_name = 'Alien/Expat Earnings'
5031 and pet.element_information1 = p_income_code
5032 and paf.assignment_id = pee.assignment_id
5033 and pee.element_link_id = pel.element_link_id
5034 and pel.business_group_id = ppf.business_group_id
5035 and pel.element_type_id = pet.element_type_id
5036 and pet.classification_id = pec.classification_id
5037 and p_effective_date between ppf.effective_start_date
5038 and ppf.effective_end_date
5039 and p_effective_date between paf.effective_start_date
5040 and paf.effective_end_date
5041 and p_effective_date between pee.effective_start_date
5042 and pee.effective_end_date
5043 and p_effective_date between pel.effective_start_date
5044 and pel.effective_end_date
5045 and p_effective_Date between pet.effective_start_date
5046 and pet.effective_end_date;
5047
5048 /*
5049 cursor c_get_element(p_person_id in number ,
5050 p_effective_date in date ,
5051 p_income_code in varchar2 ) is
5052 select pee.element_entry_id element_entry_id,
5053 pet.element_name element_name,
5054 pee.effective_start_date entry_start_date,
5055 NVL(pet.element_information1, ' ') element_income_code
5056 from per_assignments_f paf,
5057 per_people_f ppf,
5058 pay_element_entries_f pee,
5059 pay_element_links_f pel,
5060 pay_element_types_f pet,
5061 pay_element_classifications pec
5062 where paf.person_id = ppf.person_id
5063 and ppf.person_id = p_person_id
5064 and ppf.effective_start_date <= p_effective_date
5065 and ppf.effective_end_date >= p_effective_date
5066 and paf.effective_start_date <= p_effective_date
5067 and paf.effective_end_date >= p_effective_date
5068 and paf.assignment_id = pee.assignment_id
5069 and pee.element_link_id = pel.element_link_id
5070 and p_effective_date
5071 between pee.effective_start_date
5072 and pee.effective_end_date
5073 and pel.element_type_id = pet.element_type_id
5074 and p_effective_date
5075 between pel.effective_start_date
5076 and pel.effective_end_date
5077 and pet.classification_id = pec.classification_id
5078 and p_effective_Date
5079 between pet.effective_start_date
5080 and pet.effective_end_date
5081 and pec.classification_name = 'Alien/Expat Earnings'
5082 and pet.element_information1 = p_income_code;*/
5083
5084 --
5085 -- The following cursor fetches the current residency status
5086 -- of the person.
5087 --
5088 cursor c_person_residency_status(p_person_id in number) is
5089 select pei_information5 residency_status
5090 ,person_extra_info_id
5091 from per_people_extra_info
5092 where information_type = 'PER_US_ADDITIONAL_DETAILS'
5093 and person_id = p_person_id;
5094
5095 --
5096 -- The following cursor fetches the current pay_process_events records
5097 -- With status of 'N' or 'D'
5098 --
5099 cursor c_pay_process_events(p_assignment_id in number
5100 ,p_source_type in varchar2) is
5101 select process_event_id
5102 ,object_version_number
5103 from pay_process_events
5104 where assignment_id = p_assignment_id
5105 and change_type = p_source_type
5106 and status in ('N', 'D');
5107
5108 begin
5109 hr_utility.set_location('Entering:'||l_proc, 5);
5110 begin
5111 l_logic_state := ' while validating data selected from payment_export: ';
5112 --This loop selects Non read records from payment_export table
5113 l_error_message := null;
5114 pqp_atd_bus.g_error_message := null;
5115 -- Initialize error message for each iteration.
5116 l_treaty_ben_allowed_flag := 'N' ;
5117 l_retro_lose_ben_amt_flag := 'N' ;
5118 l_retro_lose_ben_date_flag := 'N' ;
5119 l_nra_exempt_from_fica := 'N' ;
5120 l_student_exempt_from_fica := 'N' ;
5121 l_wthldg_allow_eligible_flag := 'N' ;
5122 -- ====================================================
5123 -- TRANSLATION of FLAGS from -1/0 to Y/N respectively.
5124 -- ===================================================
5125 -- All these flags are defaulted to 'NO'. Assumption is that if the value
5126 -- present in these flags is something other than -1, then it is
5127 -- considered 0. for example, if a value of 2 is present in any of the
5128 -- flags then, the value will be treated as 0.
5129 --
5130 if (p_retro_lose_on_amount = l_windstar_yes ) then
5131 l_retro_lose_ben_amt_flag := 'Y';
5132 else
5133 l_retro_lose_ben_amt_flag := 'N';
5134 end if;
5135
5136 if (p_retro_lose_on_date = l_windstar_yes ) then
5137 l_retro_lose_ben_date_flag := 'Y';
5138 else
5139 l_retro_lose_ben_date_flag := 'N';
5140 end if;
5141
5142 if (p_exempt_fica = l_windstar_yes ) then
5143 l_nra_exempt_from_fica := 'Y';
5144 else
5145 l_nra_exempt_from_fica := 'N';
5146 end if;
5147
5148 if (p_exempt_student_fica = l_windstar_yes ) then
5149 l_student_exempt_from_fica := 'Y';
5150 else
5151 l_student_exempt_from_fica := 'N';
5152 end if;
5153
5154 if (p_eligible_for_whallowance = l_windstar_yes ) then
5155 l_wthldg_allow_eligible_flag := 'Y';
5156 else
5157 l_wthldg_allow_eligible_flag := 'N';
5158 end if;
5159
5160 if (p_treatybenefits_allowed = l_windstar_yes ) then
5161 l_treaty_ben_allowed_flag := 'Y';
5162 else
5163 l_treaty_ben_allowed_flag := 'N';
5164 end if;
5165
5166 if (p_add_wh_for_nra_whennotreaty = l_windstar_yes ) then
5167 l_addl_withholding_flag := 'Y';
5168 else
5169 l_addl_withholding_flag := 'N';
5170 end if;
5171
5172 if (p_state_honours_treaty = l_windstar_yes ) then
5173 l_state_honors_treaty_flag := 'Y';
5174 else
5175 l_state_honors_treaty_flag := 'N';
5176 end if;
5177
5178 if (p_current_analysis = l_windstar_yes ) then
5179 l_current_analysis := 'Y';
5180 else
5181 l_current_analysis := 'N';
5182 end if;
5183 --
5184 -- Windstar sends back the forecast_income code only for the
5185 -- 17, 18 and 19. It sends a null for all other codes.
5186 --
5187 if p_forecast_income_code is not null then
5188 l_forecast_income_code := p_forecast_income_code || p_scholarship_type;
5189 end if;
5190
5191 -- =============================================================
5192 -- Determination of Person Id from SSN, if Person Id is null
5193 -- =============================================================
5194 -- if person_id (That is present in institution_indiv_id field) is present
5195 -- then, it is take for all computational purposes. But if the person_id
5196 -- is null, and social security is given, then the SSN is used to
5197 -- determine the person_id
5198 --
5199
5200 l_person_id := null;
5201
5202 if (p_institution_indiv_id is null) then
5203 hr_utility.set_location(l_proc, 10);
5204 if (p_social_security_number is not null) then
5205 for cps in c_person_ssn(p_social_security_number)
5206 loop
5207 l_person_id := cps.person_id ;
5208 end loop;
5209 end if;
5210 else
5211 l_person_id := p_institution_indiv_id;
5212 end if;
5213
5214 if (l_person_id is null) then
5215 hr_utility.set_location(l_proc, 20);
5216 l_error_message := l_error_message ||
5217 'Person Id could not be determined';
5218 end if;
5219
5220 -- =========================================================
5221 -- Translation of Windstar Pay Periods to ORACLE pay periods
5222 -- =========================================================
5223
5224 l_period_type := null;
5225 if (p_amount_of_addl_wh_type is not null) then
5226 hr_utility.set_location(l_proc, 30);
5227 for cwopp in
5228 c_winstar_oracle_pay_period(p_amount_of_addl_wh_type)
5229 loop
5230 l_period_type := cwopp.meaning;
5231 end loop;
5232 --
5233 -- The mapping of pay periond translation is as shown below
5234 -- +--------------+--------------+----------------+-----------------+
5235 -- |Windstar Code | Meaning | Oracle Payroll | Meaning |
5236 -- +--------------+--------------+----------------+-----------------+
5237 -- | M | Monthly | CM | Calendar Month |
5238 -- | W | Weekly | W | Week |
5239 -- | S | Semi Monthly | SM | Semi-Month |
5240 -- | B | Bi weekly | F | Bi-Week |
5241 -- | L | Lump sump | Y | Year |
5242 -- +--------------+--------------+----------------+-----------------+
5243 --
5244 if (l_period_type is null) then
5245 hr_utility.set_location(l_proc, 40);
5246 l_error_message := l_error_message || ' Pay Period is Invalid';
5247 --
5248 -- l_period_type will be null if the value in
5249 -- p_addtnl_wthldng_amt_period_type is not either of M, W, S, B, L.
5250 -- then just update the pqp_us_alien_transaction_data table with
5251 -- the warning message and still continue with posting in the
5252 -- pqp_us_analyzed_data and pqp_us_analyzed_details tables
5253 --
5254 end if;
5255 else
5256 l_error_message := l_error_message || ' Pay Period is NULL';
5257 end if;
5258 --
5259 -- ==========================
5260 -- Personal Exemption check.
5261 -- ==========================
5262 -- The personal exemption should be between 0 and 9 (both inclusive).
5263 -- ASCII(0) = 48 and ASCII(9) = 57. as per Sirisha the possible
5264 -- valid values in Personal Exemption field are 0 and 1.
5265 --
5266 l_personal_exemption := p_personal_exemption;
5267
5268 if (ascii(p_personal_exemption) < 48 or
5269 ascii(p_personal_exemption) > 49 ) then
5270 l_error_message := l_error_message || '(' || 'personal_exemption = '
5271 || p_personal_exemption || ' is invalid.)';
5272 hr_utility.set_location(l_proc, 50);
5273 end if;
5274
5275 for cpas in c_pri_assgn(l_person_id,
5276 to_date('01/01'||to_char(p_taxyear),'DD/MM/YYYY'))
5277 loop
5278 hr_utility.set_location(l_proc, 60);
5279 l_pri_assgn := cpas.assignment_id;
5280 end loop;
5281
5282 l_maximum_benefit := p_maximum_benefit;
5283
5284 if p_date_8233_signed is null then
5285 l_date_8233_signed := TO_DATE('01/01'||TO_CHAR(p_taxyear),'DD/MM/YYYY');
5286 else
5287 l_date_8233_signed := p_date_8233_signed;
5288 end if;
5289
5290 l_withholding_rate := nvl(p_withholding_rate,0) / 10;
5291
5292 l_amount_of_addl_withholding := p_amount_of_addl_withholding / 100;
5293 --
5294 -- Required for W4 creation after the insert into the transaction table below
5295 --
5296 if (p_date_w4_signed is not null) then
5297 open c_get_per_trans(l_person_id);
5298 loop
5299 fetch c_get_per_trans into l_get_per_trans;
5300 exit when c_get_per_trans%notfound;
5301 end loop;
5302 close c_get_per_trans;
5303 end if;
5304 --
5305 -- Inserting into Alien_transaction_data table
5306 --
5307 l_logic_state := ' while inserting in PQP_ALIEN_TRANSACTION_DATA : ';
5308
5309 pqp_alien_trans_data_api.create_alien_trans_data
5310 (p_validate => false
5311 ,p_alien_transaction_id => l_alien_transaction_id
5312 ,p_data_source_type => 'PQP_US_ALIEN_WINDSTAR'
5313 ,p_tax_year => p_taxyear
5314 ,p_income_code => p_income_code || p_scholarship_type
5315 ,p_withholding_rate => l_withholding_rate
5316 ,p_income_code_sub_type => p_scholarship_type
5317 ,p_forecast_income_code => l_forecast_income_code
5318 ,p_exemption_code => p_exemption_code
5319 ,p_maximum_benefit_amount => l_maximum_benefit
5320 ,p_retro_lose_ben_amt_flag => l_retro_lose_ben_amt_flag
5321 ,p_date_benefit_ends => p_date_benefit_ends
5322 ,p_retro_lose_ben_date_flag => l_retro_lose_ben_date_flag
5323 ,p_current_residency_status => p_residency_status
5324 ,p_nra_to_ra_date => p_date_becomes_ra
5325 ,p_target_departure_date => p_target_departure_date
5326 ,p_tax_residence_country_code => p_tax_residence_country_code
5327 ,p_treaty_info_update_date => p_date_treaty_updated
5328 ,p_nra_exempt_from_fica => l_nra_exempt_from_fica
5329 ,p_student_exempt_from_fica => l_student_exempt_from_fica
5330 ,p_addl_withholding_flag => l_addl_withholding_flag
5331 ,p_addl_withholding_amt => p_amount_of_addl_withholding
5332 ,p_addl_wthldng_amt_period_type => l_period_type
5333 ,p_personal_exemption => l_personal_exemption
5334 ,p_addl_exemption_allowed => p_add_exemptions_allowed
5335 ,p_number_of_days_in_usa => p_days_in_usa
5336 ,p_current_analysis => l_current_analysis
5337 ,p_wthldg_allow_eligible_flag => l_wthldg_allow_eligible_flag
5338 ,p_treaty_ben_allowed_flag => l_treaty_ben_allowed_flag
5339 ,p_treaty_benefits_start_date => p_treatybenefit_startdate
5340 ,p_ra_effective_date => p_ra_effective_date
5341 ,p_state_code => p_state_code
5342 ,p_state_honors_treaty_flag => l_state_honors_treaty_flag
5343 ,p_ytd_payments => p_ytd_payments
5344 ,p_ytd_w2_payments => p_ytd_w2payments
5345 ,p_ytd_w2_withholding => p_ytd_withholding
5346 ,p_ytd_withholding_allowance => p_ytd_whallowance
5347 ,p_ytd_treaty_payments => p_ytd_treaty_payments
5348 ,p_ytd_treaty_withheld_amt => p_ytd_treaty_withheld_amts
5349 ,p_record_source => p_record_source
5350 ,p_visa_type => p_visa_type
5351 ,p_j_sub_type => p_jsub_type
5352 ,p_primary_activity => p_primary_activity
5353 ,p_non_us_country_code => p_nus_countrycode
5354 ,p_citizenship_country_code => p_citizenship
5355 ,p_constant_addl_tax => p_constant_additional_tax
5356 ,p_date_8233_signed => l_date_8233_signed
5357 ,p_date_w4_signed => p_date_w4_signed
5358 ,p_error_indicator => null
5359 ,p_prev_er_treaty_benefit_amt => p_out_of_system_treaty
5360 ,p_error_text => l_error_message
5361 ,p_object_version_number => l_transaction_ovn
5362 ,p_person_id => l_person_id
5363 ,p_effective_date =>
5364 TO_DATE('01/01' || TO_CHAR(p_taxyear), 'DD/MM/YYYY')
5365 );
5366 hr_utility.set_location(l_proc, 70);
5367 if (l_error_message is null and
5368 pqp_atd_bus.g_error_message is null) then
5369
5370 hr_utility.set_location(l_proc, 80);
5371 -- ==============================================
5372 -- W4 record creation
5373 -- ==============================================
5374 -- A W4 record will be created under the following conditions.
5375 -- 1. no data is present in the PQP_ANALYZED_ALIEN_DATA (This happens
5376 -- for the first time only),
5377 -- 2. date_w4_signed field is not null in the
5378 -- PQP_ALIEN_TRANSACTION_DATA table.
5379 -- 3. Either of the following values is present
5380 -- a. personal_exemption is present
5381 -- b. addl_exemption_allowed
5382 -- c. addl_withholding_amt
5383 -- d. constant_addl_tax
5384 -- a + b is Allowance on Tax screen.
5385 -- c + d is Additional Tax Amount on screen.
5386 -- The following changes have been made to the above logic.If the
5387 -- date_w4_signed is not null the exemption/allowance and the
5388 -- additional witholding amt fields are updated in the W4 Record.
5389 -- The additional exemption i.e. (a + b) was not being considered
5390 -- while updating the exemptions allowed. This is being corrected.
5391 --
5392 l_logic_state := ' while updating W4 Info: ';
5393
5394 if (p_date_w4_signed is not null) then
5395 --
5396 -- l_get_per_trans record is populated before the insert
5397 -- into the transaction table
5398 --
5399 if ( l_get_per_trans.date_w4_signed is null
5400 or
5401 (l_get_per_trans.date_w4_signed is not null and
5402 p_date_w4_signed <> l_get_per_trans.date_w4_signed)
5403 or
5404 (p_personal_exemption is not null
5405 and p_personal_exemption<>l_get_per_trans.personal_exemption)
5406 or
5407 (p_add_exemptions_allowed is not null
5408 and p_add_exemptions_allowed<>l_get_per_trans.addl_exemption_allowed)
5409 or
5410 (l_amount_of_addl_withholding is not null
5411 and l_amount_of_addl_withholding <> l_get_per_trans.addl_withholding_amt)
5412 or
5413 (p_constant_additional_tax is not null
5414 and p_constant_additional_tax<>l_get_per_trans.constant_addl_tax)
5415 or
5416 (p_residency_status = 'R'
5417 and l_get_per_trans.addl_withholding_amt <> 0)
5418 ) then
5419
5420 for c_person1 in c_person (l_person_id,
5421 TO_DATE('01/01/'||TO_CHAR(p_taxyear),'DD/MM/YYYY'))
5422 loop
5423 l_eff_w4_date := c_person1.effective_end_date;
5424 end loop;
5425
5426 if (p_residency_status = 'R') then
5427 l_additional_amt := 0;
5428 else
5429 l_additional_amt := NVL(l_amount_of_addl_withholding, 0) +
5430 NVL(p_constant_additional_tax,0);
5431 end if;
5432
5433
5434 hr_utility.set_location(l_proc, 100);
5435 pay_us_web_w4.update_alien_tax_records
5436 -- pay_us_otf_util_web.update_tax_records
5437 (p_filing_status_code => '01'
5438 ,p_allowances => (nvl(p_add_exemptions_allowed, 0) +
5439 nvl(p_personal_exemption,0))
5440 ,p_additional_amount => l_additional_amt
5441 ,p_exempt_status_code => 'N'
5442 --,p_process => 'PAY_FED_W4_NOTIFICATION_PRC'
5443 ,p_process => 'PAY_OTF_NOTIFY_PRC'
5444 ,p_itemtype => 'HRSSA'
5445 ,p_person_id => l_person_id
5446 ,p_effective_date => p_date_w4_signed
5447 ,p_source_name => 'PQP_US_ALIEN_WINDSTAR'
5448 );
5449
5450 hr_utility.set_location(l_proc, 110);
5451
5452 end if;
5453 end if;
5454 --
5455 -- The following cursor gives the assignment_id's of a person. All
5456 -- the assignments that are active in the year of the effective
5457 -- date(Tax year) are reported. But that assignment should be present
5458 -- in pay_process_events table with a status of 'R'.
5459 --
5460 l_reco_flag := false;
5461
5462 if (c_person_assgn%isopen = true) then
5463 close c_person_assgn;
5464 end if;
5465
5466 open c_person_assgn
5467 (l_person_id ,
5468 TO_DATE('01/01/' || TO_CHAR(p_taxyear), 'DD/MM/YYYY'),
5469 'PQP_US_ALIEN_WINDSTAR'
5470 );
5471 fetch c_person_assgn into l_cpa_assignment_id;
5472
5473 hr_utility.set_location(l_proc, 120);
5474
5475 if (c_person_assgn%found) then
5476 loop
5477 hr_utility.set_location(l_proc, 130);
5478 -- The following cursor checks whether an assignment exists in
5479 -- pqp_alien_data table for a given year or not
5480 --
5481 l_logic_state := ' while inserting in PQP_ANALYZED_ALIEN_DATA:';
5482
5483 if (c_assign_exists%isopen = true) then
5484 close c_assign_exists;
5485 end if;
5486
5487 if (c_assign_exists%isopen = true) then
5488 close c_assign_exists;
5489 end if;
5490
5491 open c_assign_exists(l_cpa_assignment_id ,
5492 p_taxyear );
5493 --
5494 -- c_assign_exists checks whether the given assignment exists in
5495 -- the pqp_analyzed_alien_data_api table for the given year or not
5496 --
5497 fetch c_assign_exists into l_analyzed_data_id ,
5498 l_analyzed_data_ovn ;
5499 hr_utility.set_location(l_proc, 140);
5500
5501 if (c_assign_exists%notfound) then
5502
5503 hr_utility.set_location(l_proc, 150);
5504 --
5505 -- if the row does not exist then create a row in
5506 -- analyzed_alien_data table
5507 --
5508 if (c_assign_exists%isopen = true) then
5509 close c_assign_exists;
5510 end if;
5511
5512 pqp_analyzed_alien_data_api.create_analyzed_alien_data
5513 (p_validate => false
5514 ,p_analyzed_data_id => l_analyzed_data_id
5515 ,p_assignment_id => l_cpa_assignment_id
5516 ,p_data_source => 'PQP_US_ALIEN_WINDSTAR'
5517 ,p_tax_year => p_taxyear
5518 ,p_current_residency_status => p_residency_status
5519 ,p_nra_to_ra_date => p_date_becomes_ra
5520 ,p_target_departure_date => p_target_departure_date
5521 ,p_tax_residence_country_code => p_tax_residence_country_code
5522 ,p_treaty_info_update_date => p_date_treaty_updated
5523 ,p_number_of_days_in_usa => p_days_in_usa
5524 ,p_withldg_allow_eligible_flag => l_wthldg_allow_eligible_flag
5525 ,p_ra_effective_date => p_ra_effective_date
5526 ,p_record_source => p_record_source
5527 ,p_visa_type => p_visa_type
5528 ,p_j_sub_type => p_jsub_type
5529 ,p_primary_activity => p_primary_activity
5530 ,p_non_us_country_code => p_nus_countrycode
5531 ,p_citizenship_country_code => p_citizenship
5532 ,p_object_version_number => l_analyzed_data_ovn
5533 ,p_date_w4_signed => p_date_w4_signed
5534 ,p_date_8233_signed => l_date_8233_signed
5535 ,p_effective_date => to_date('01/01/' ||
5536 p_taxyear, 'DD/MM/YYYY')
5537 );
5538 hr_utility.set_location(l_proc, 160);
5539
5540 else
5541
5542 hr_utility.set_location(l_proc, 170);
5543
5544 if (c_assign_exists%isopen = true) then
5545 close c_assign_exists;
5546 end if;
5547 pqp_analyzed_alien_data_api.update_analyzed_alien_data
5548 (p_validate => false
5549 ,p_analyzed_data_id => l_analyzed_data_id
5550 ,p_assignment_id => l_cpa_assignment_id
5551 ,p_data_source => 'PQP_US_ALIEN_WINDSTAR'
5552 ,p_tax_year => p_taxyear
5553 ,p_current_residency_status => p_residency_status
5554 ,p_nra_to_ra_date => p_date_becomes_ra
5555 ,p_target_departure_date => p_target_departure_date
5556 ,p_tax_residence_country_code => p_tax_residence_country_code
5557 ,p_treaty_info_update_date => p_date_treaty_updated
5558 ,p_number_of_days_in_usa => p_days_in_usa
5559 ,p_withldg_allow_eligible_flag => l_wthldg_allow_eligible_flag
5560 ,p_ra_effective_date => p_ra_effective_date
5561 ,p_record_source => p_record_source
5562 ,p_visa_type => p_visa_type
5563 ,p_j_sub_type => p_jsub_type
5564 ,p_primary_activity => p_primary_activity
5565 ,p_non_us_country_code => p_nus_countrycode
5566 ,p_citizenship_country_code => p_citizenship
5567 ,p_object_version_number => l_analyzed_data_ovn
5568 ,p_date_w4_signed => p_date_w4_signed
5569 ,p_date_8233_signed => l_date_8233_signed
5570 ,p_effective_date => to_date('01/01/' ||
5571 p_taxyear, 'DD/MM/YYYY')
5572 );
5573
5574 hr_utility.set_location(l_proc, 180);
5575
5576 end if;
5577 --
5578 -- Alien Details
5579 --
5580 l_logic_state := ' while inserting in PQP_ANALYZED_ALIEN_DETAIL: ';
5581
5582 if (c_analyzed_det_exists%isopen = true) then
5583 close c_analyzed_det_exists;
5584 end if;
5585
5586 open c_analyzed_det_exists(l_analyzed_data_id ,
5587 p_income_code ||p_scholarship_type );
5588 fetch c_analyzed_det_exists
5589 into l_analyzed_data_details_id ,
5590 l_analyzed_det_ovn,
5591 l_retro_lose_ben_amt_flag_old,
5592 l_retro_lose_ben_date_flag_old ;
5593
5594 hr_utility.set_location(l_proc, 190);
5595 --
5596 -- The following code raises a notification if the actual income
5597 -- code is different from the forecast income code. The notification
5598 -- would be send only if no analyzed data is available for this
5599 -- income code to avoid sending the notification repeatedly.
5600 --
5601
5602 if p_income_code <> p_forecast_income_code then
5603
5604 if c_analyzed_det_exists%notfound then
5605
5606 pqp_alien_trans_data_api.update_alien_trans_data
5607 (p_validate => false
5608 ,p_alien_transaction_id => l_alien_transaction_id
5609 ,p_object_version_number => l_transaction_ovn
5610 ,p_error_indicator => 'WARNING : CHANGED INCOME CODE'
5611 ,p_error_text => 'Changed Income Code'
5612 ,p_effective_date => TO_DATE('01/01/' ||
5613 p_taxyear, 'DD/MM/YYYY')
5614 );
5615
5616 pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
5617 (p_alien_transaction_id => l_alien_transaction_id
5618 ,p_assignment_id => l_pri_assgn
5619 ,p_tran_type => 'WRITE'
5620 ,p_tran_date => SYSDATE
5621 ,p_itemtype => 'PQPALNTF'
5622 ,p_process_name => 'WIN_PRC'
5623 ,p_process_event_id => null
5624 ) ;
5625 end if;
5626
5627 end if;
5628
5629
5630 if (c_analyzed_det_exists%notfound) then
5631
5632 hr_utility.set_location(l_proc, 200);
5633 if (c_analyzed_det_exists%isopen = true) then
5634 close c_analyzed_det_exists;
5635 end if;
5636
5637 pqp_analyzed_alien_det_api.create_analyzed_alien_det
5638 (p_validate => false
5639 ,p_analyzed_data_details_id => l_analyzed_data_details_id
5640 ,p_analyzed_data_id => l_analyzed_data_id
5641 ,p_income_code => p_income_code || p_scholarship_type
5642 ,p_current_analysis => l_current_analysis
5643 ,p_forecast_income_code => l_forecast_income_code
5644 ,p_withholding_rate => l_withholding_rate
5645 ,p_income_code_sub_type => p_scholarship_type
5646 ,p_exemption_code => p_exemption_code
5647 ,p_maximum_benefit_amount => l_maximum_benefit
5648 ,p_retro_lose_ben_amt_flag => l_retro_lose_ben_amt_flag
5649 ,p_date_benefit_ends => p_date_benefit_ends
5650 ,p_retro_lose_ben_date_flag => l_retro_lose_ben_date_flag
5651 ,p_nra_exempt_from_ss => l_nra_exempt_from_fica
5652 ,p_nra_exempt_from_medicare => l_nra_exempt_from_fica
5653 ,p_student_exempt_from_ss => l_student_exempt_from_fica
5654 ,p_student_exempt_from_medi => l_student_exempt_from_fica
5655 ,p_addl_withholding_flag => null
5656 ,p_constant_addl_tax => p_constant_additional_tax
5657 ,p_addl_withholding_amt => l_amount_of_addl_withholding
5658 ,p_addl_wthldng_amt_period_type => null
5659 ,p_personal_exemption => p_personal_exemption
5660 ,p_addl_exemption_allowed =>p_add_exemptions_allowed
5661 ,p_treaty_ben_allowed_flag => l_treaty_ben_allowed_flag
5662 ,p_treaty_benefits_start_date => p_treatybenefit_startdate
5663 ,p_object_version_number => l_analyzed_det_ovn
5664 ,p_effective_date => to_date('01/01/' ||
5665 p_taxyear, 'DD/MM/YYYY')
5666 );
5667
5668 hr_utility.set_location(l_proc, 210);
5669
5670 else
5671
5672 hr_utility.set_location(l_proc, 220);
5673
5674 if (c_analyzed_det_exists%isopen = true) then
5675 close c_analyzed_det_exists;
5676 end if;
5677 --
5678 -- Changed the above logic on 10-SEP-01 Bug #1891026
5679 -- Windstar sets the loss_benefit_flag to Y once the
5680 -- person is analysed. So a new field was introduced
5681 -- to keep track of the notification sent.
5682 if (p_ytd_treaty_withheld_amts >= p_maximum_benefit and
5683 p_maximum_benefit > 0 and
5684 l_retro_lose_ben_amt_flag = 'Y')then
5685 l_retro_lost := true;
5686 elsif trunc(p_date_benefit_ends) <= trunc(sysdate) and
5687 l_retro_lose_ben_date_flag = 'Y' then
5688 l_retro_lost := true;
5689 else
5690 l_retro_lost := false;
5691 end if;
5692
5693 pqp_analyzed_alien_det_api.update_analyzed_alien_det
5694 (p_validate => false ,
5695 p_analyzed_data_details_id => l_analyzed_data_details_id ,
5696 p_analyzed_data_id => l_analyzed_data_id ,
5697 p_income_code => p_income_code|| p_scholarship_type ,
5698 p_current_analysis => l_current_analysis , -- Oct02 changes
5699 p_forecast_income_code => l_forecast_income_code ,
5700 p_withholding_rate => l_withholding_rate ,
5701 p_income_code_sub_type => p_scholarship_type ,
5702 p_exemption_code => p_exemption_code ,
5703 p_maximum_benefit_amount => l_maximum_benefit ,
5704 p_retro_lose_ben_amt_flag => l_retro_lose_ben_amt_flag ,
5705 p_date_benefit_ends => p_date_benefit_ends ,
5706 p_retro_lose_ben_date_flag => l_retro_lose_ben_date_flag ,
5707 p_nra_exempt_from_ss => l_nra_exempt_from_fica ,
5708 p_nra_exempt_from_medicare => l_nra_exempt_from_fica ,
5709 p_student_exempt_from_ss => l_student_exempt_from_fica ,
5710 p_student_exempt_from_medi => l_student_exempt_from_fica ,
5711 p_addl_withholding_flag => null ,
5712 p_constant_addl_tax => p_constant_additional_tax ,
5713 p_addl_withholding_amt => l_amount_of_addl_withholding ,
5714 p_addl_wthldng_amt_period_type => null ,
5715 p_personal_exemption => p_personal_exemption ,
5716 p_addl_exemption_allowed => p_add_exemptions_allowed,
5717 p_treaty_ben_allowed_flag => l_treaty_ben_allowed_flag ,
5718 p_treaty_benefits_start_date => p_treatybenefit_startdate ,
5719 p_object_version_number => l_analyzed_det_ovn ,
5720 p_effective_date => TO_DATE('01/01/' || p_taxyear, 'DD/MM/YYYY')
5721 );
5722 hr_utility.set_location(l_proc, 230);
5723 end if;
5724
5725 if l_current_analysis = 'N' then
5726
5727 pqp_alien_trans_data_api.update_alien_trans_data
5728 (p_validate => false ,
5729 p_alien_transaction_id => l_alien_transaction_id ,
5730 p_object_version_number => l_transaction_ovn ,
5731 p_error_indicator => 'WARNING : INVALID INCOME CODE',
5732 p_error_text => 'Invalid Income Code',
5733 p_effective_date => TO_DATE('01/01/' ||
5734 p_taxyear, 'DD/MM/YYYY')
5735 );
5736
5737 pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
5738 (p_alien_transaction_id => l_alien_transaction_id ,
5739 p_assignment_id => l_pri_assgn ,
5740 p_tran_type => 'WRITE' ,
5741 p_tran_date => SYSDATE ,
5742 p_itemtype => 'PQPALNTF' ,
5743 p_process_name => 'WIN_PRC' ,
5744 p_process_event_id => null
5745 ) ;
5746
5747 end if;
5748
5749 -- Following code sets the residency status codein the person extra
5750 -- info as per the analysis. Made changes to the following logic
5751 -- So that no new pay_process_event is logged with the status 'N'.
5752 -- The code after changing Residency status would go and change the
5753 -- status from Not-Read to Read in the pay_process_events However if
5754 -- there is an existing record with a status of 'N' or 'D' the status
5755 -- would not be changed.
5756
5757 l_ppe_status_n_recs := false;
5758
5759 for c_rec in c_pay_process_events (l_cpa_assignment_id,
5760 'PQP_US_ALIEN_WINDSTAR')
5761 loop
5762 l_ppe_status_n_recs := true;
5763 end loop;
5764
5765 for c_rec in c_person_residency_status(l_person_id)
5766 loop
5767 if p_residency_status <> c_rec.residency_status then
5768 update per_people_extra_info
5769 set pei_information5 = p_residency_status
5770 where person_extra_info_id = c_rec.person_extra_info_id;
5771 end if;
5772 end loop;
5773
5774 if l_ppe_status_n_recs = false then
5775
5776 update pay_process_events
5777 set status = 'R',
5778 description = substr('Record Read | '|| description, 1, 240),
5779 object_version_number = object_version_number + 1
5780 where assignment_id = l_cpa_assignment_id
5781 and status = 'N'
5782 and change_type = 'PQP_US_ALIEN_WINDSTAR';
5783
5784 end if;
5785 --
5786 -- l_process_event_id is not null if an assignment id exists in the
5787 -- pay_process_events table with change_type PQP_US_WINSTAR,
5788 -- staus = read or complete in the given year
5789 --
5790 l_process_event_id := null;
5791 l_process_ovn := null;
5792 --
5793 -- RECONCILIATION STARTS
5794 --
5795 l_logic_state := ' while Reconciling: ';
5796
5797 pqp_windstar_reconcile
5798 (p_assignment_id => l_cpa_assignment_id ,
5799 p_effective_date => TO_DATE('01/01/' || p_taxyear,
5800 'DD/MM/YYYY') ,
5801 p_source_type => 'PQP_US_ALIEN_WINDSTAR' ,
5802 p_process_event_id => l_process_event_id ,
5803 p_object_version_number => l_process_ovn
5804 );
5805 hr_utility.set_location(l_proc, 240);
5806
5807 if (l_process_event_id is null and
5808 l_reco_flag = false) then
5809
5810 hr_utility.set_location(l_proc, 250);
5811 -- l_process_event_id will be null if an assignment id does
5812 -- not exist in the pay_process_events table for PQP_US_WINSTAR
5813 -- or such an assignment exists in the pay_process_events
5814 -- table, but the status of such record is not read
5815 --
5816 pqp_alien_trans_data_api.update_alien_trans_data
5817 (p_validate => false
5818 ,p_alien_transaction_id => l_alien_transaction_id
5819 ,p_object_version_number => l_transaction_ovn
5820 ,p_error_indicator => 'ERROR : NOT_RECONCILED 1'
5821 ,p_error_text => l_error_message ||
5822 'Assignment not Reconciled'
5823 ,p_effective_date => TO_DATE('01/01/' ||
5824 p_taxyear, 'DD/MM/YYYY')
5825 );
5826 hr_utility.set_location(l_proc, 260);
5827
5828 p_error_message := ' Assignment not Reconciled ';
5829
5830 l_reco_flag := true;
5831
5832 pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
5833 (p_alien_transaction_id => l_alien_transaction_id
5834 ,p_assignment_id => l_pri_assgn
5835 ,p_tran_type => 'WRITE'
5836 ,p_tran_date => SYSDATE
5837 ,p_itemtype => 'PQPALNTF'
5838 ,p_process_name => 'WIN_PRC'
5839 ,p_process_event_id => null
5840 ) ;
5841
5842 hr_utility.set_location(l_proc, 270);
5843
5844 elsif(l_process_event_id is not null and p_reconcile = true)
5845 then --ELSE7}{
5846 hr_utility.set_location(l_proc, 280);
5847
5848 pay_ppe_api.update_process_event
5849 (p_validate => false
5850 ,p_change_type => 'PQP_US_ALIEN_WINDSTAR'
5851 ,p_description => 'Assignment has been Reconciled'
5852 ,p_status => 'C'
5853 ,p_process_event_id => l_process_event_id
5854 ,p_object_version_number=> l_process_ovn
5855 );
5856 hr_utility.set_location(l_proc, 290);
5857 --
5858 -- There might be other open records with a status of 'R' for
5859 -- the same assignment as windstar read process might have
5860 -- read it twice. ie. there could be more than one record with
5861 -- a status of 'R' and this reconcilation logic changes the
5862 -- stauts of only one record to 'C'. Hence updating the
5863 -- remaining records to a status of 'C'.
5864 --
5865 begin
5866 update pay_process_events ppe
5867
5868 set ppe.status = 'C'
5869 ,ppe.description = 'Assignment has been Reconciled'
5870 ,ppe.object_version_number =
5871 ppe.object_version_number + 1
5872
5873 where ppe.assignment_id = l_pri_assgn
5874 and change_type = 'PQP_US_ALIEN_WINDSTAR'
5875 and ppe.status = 'R';
5876 --
5877 hr_utility.set_location(l_proc, 295);
5878 end;
5879
5880 begin
5881 select retro_loss_notification_sent
5882 into l_notification_sent
5883 from pqp_analyzed_alien_details
5884 where analyzed_data_details_id = l_analyzed_data_details_id
5885 and analyzed_data_id = l_analyzed_data_id;
5886 exception
5887 when NO_DATA_FOUND then
5888 l_notification_sent := 'Y';
5889 end;
5890
5891 begin
5892 if l_retro_lost = true and
5893 NVL(l_notification_sent,'N') = 'N' and
5894 NVL(l_current_analysis,'Y' ) = 'Y' then
5895
5896 if l_retro_lose_ben_date_flag ='Y' then
5897
5898 l_retro_lose_ben_date_mesg
5899 := 'This person has exceeded the treaty benefit end date of '
5900 ||p_date_benefit_ends;
5901 l_retro_lose_ben_date_mesg := l_retro_lose_ben_date_mesg
5902 ||' and is now subject to taxes retroactively on all income associated with the code '
5903 ||p_income_code||p_scholarship_type;
5904 l_retro_lose_ben_date_mesg:= l_retro_lose_ben_date_mesg
5905 ||' earned for '||p_taxyear||'.';
5906 l_retro_lose_ben_amt_mesg := null;
5907
5908 elsif l_retro_lose_ben_amt_flag = 'Y' then
5909
5910
5911 if (p_ytd_payments >= p_maximum_benefit) then
5912
5913 l_retro_lose_ben_amt_mesg :=
5914 'This person has reached the maximum treaty benefit amount limit of '||l_maximum_benefit;
5915 l_retro_lose_ben_amt_mesg := l_retro_lose_ben_amt_mesg||
5916 ' and may be subject to taxes retroactively on all income associated with the code '
5917 ||p_income_code||p_scholarship_type ;
5918 l_retro_lose_ben_amt_mesg := l_retro_lose_ben_amt_mesg
5919 ||' earned for '||p_taxyear||'.';
5920 l_retro_lose_ben_date_mesg := null;
5921 end if;
5922
5923 end if;
5924
5925 pqp_alien_trans_data_api.update_alien_trans_data
5926 (p_validate => false
5927 ,p_alien_transaction_id => l_alien_transaction_id
5928 ,p_object_version_number => l_transaction_ovn
5929 ,p_error_indicator => 'WARNING : RETRO LOSS'
5930 ,p_error_text => NVL(l_retro_lose_ben_date_mesg
5931 ,l_retro_lose_ben_amt_mesg)
5932 ,p_effective_date => TO_DATE('01/01/' ||
5933 p_taxyear, 'DD/MM/YYYY')
5934 );
5935
5936 pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
5937 (p_alien_transaction_id => l_alien_transaction_id
5938 ,p_assignment_id => l_pri_assgn
5939 ,p_tran_type => 'WRITE'
5940 ,p_tran_date => SYSDATE
5941 ,p_itemtype => 'PQPALNTF'
5942 ,p_process_name => 'WIN_PRC'
5943 ,p_process_event_id => null
5944 ) ;
5945 -- Update table to set the flag notification_sent = 'Y'
5946 pqp_analyzed_alien_det_api.update_analyzed_alien_det
5947 (p_validate => false
5948 ,p_analyzed_data_details_id => l_analyzed_data_details_id
5949 ,p_analyzed_data_id => l_analyzed_data_id
5950 ,p_effective_date => TO_DATE('01/01/' ||
5951 p_taxyear, 'DD/MM/YYYY')
5952 ,p_retro_loss_notification_sent => 'Y'
5953 ,p_object_version_number => l_transaction_ovn
5954 );
5955
5956 hr_utility.set_location(l_proc, 296);
5957 end if;
5958
5959 end;
5960 -- Workflow Notification: The control will come to this point
5961 -- only if no error was encountered above or the control will
5962 -- pass to Error block.
5963 -- RECONCILIATION ENDS
5964
5965 end if;
5966 fetch c_person_assgn
5967 into l_cpa_assignment_id;
5968
5969 exit when c_person_assgn%notfound;
5970 end loop;
5971
5972 else
5973 hr_utility.set_location(l_proc, 300);
5974 -- Means no assignment with read/complete status was present in
5975 -- pay_process_events table
5976 pqp_alien_trans_data_api.update_alien_trans_data
5977 (p_validate => false
5978 ,p_alien_transaction_id => l_alien_transaction_id
5979 ,p_object_version_number => l_transaction_ovn
5980 ,p_error_indicator => 'ERROR : NOT_RECONCILED 2'
5981 ,p_error_text => l_error_message||'Assignment not Reconciled'
5982 ,p_effective_date => TO_DATE('01/01/'||p_taxyear, 'DD/MM/YYYY')
5983 );
5984 hr_utility.set_location(l_proc, 310);
5985
5986 p_error_message := ' Assignment not Reconciled ';
5987
5988 pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
5989 (p_alien_transaction_id => l_alien_transaction_id ,
5990 p_assignment_id => l_pri_assgn ,
5991 p_tran_type => 'WRITE' ,
5992 p_tran_date => SYSDATE ,
5993 p_itemtype => 'PQPALNTF' ,
5994 p_process_name => 'WIN_PRC' ,
5995 p_process_event_id => null
5996 ) ;
5997 hr_utility.set_location(l_proc, 320);
5998
5999 end if;
6000 close c_person_assgn;
6001 else
6002 hr_utility.set_location(l_proc, 330);
6003 p_error_message := l_error_message ||pqp_atd_bus.g_error_message;
6004
6005 pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
6006 (p_alien_transaction_id => l_alien_transaction_id ,
6007 p_assignment_id => l_pri_assgn ,
6008 p_tran_type => 'WRITE' ,
6009 p_tran_date => SYSDATE ,
6010 p_itemtype => 'PQPALNTF' ,
6011 p_process_name => 'WIN_PRC' ,
6012 p_process_event_id => null
6013 ) ;
6014 hr_utility.set_location(l_proc, 340);
6015 end if;
6016 exception
6017 when OTHERS then
6018 hr_utility.set_location(l_proc, 350);
6019 l_atd_ovn := null;
6020 l_error_message := l_error_message ||TO_CHAR(SQLCODE) ||
6021 SQLERRM ||l_logic_state;
6022 p_error_message := p_error_message || l_error_message;
6023
6024 for c1 in c_atd(l_alien_transaction_id)
6025 loop
6026 l_atd_ovn := c1.object_version_number;
6027 end loop;
6028
6029 if (l_atd_ovn is not null) then
6030
6031 hr_utility.set_location(l_proc, 360);
6032
6033 pqp_alien_trans_data_api.update_alien_trans_data
6034 (p_validate => false
6035 ,p_alien_transaction_id => l_alien_transaction_id
6036 ,p_object_version_number => l_atd_ovn
6037 ,p_error_indicator => 'ERROR : ORACLE'
6038 ,p_error_text => l_error_message
6039 ,p_effective_date => to_date('01/01/' ||
6040 p_taxyear, 'DD/MM/YYYY')
6041 );
6042
6043 hr_utility.set_location(l_proc, 370);
6044
6045 pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
6046 (p_alien_transaction_id => l_alien_transaction_id
6047 ,p_assignment_id => l_pri_assgn
6048 ,p_tran_type => 'WRITE'
6049 ,p_tran_date => sysdate
6050 ,p_itemtype => 'PQPALNTF'
6051 ,p_process_name => 'WIN_PRC'
6052 ,p_process_event_id => null
6053 );
6054
6055 hr_utility.set_location(l_proc, 380);
6056 end if;
6057 end;
6058
6059 hr_utility.set_location('Leaving ' || l_proc, 390);
6060
6061 exception
6062 when others then
6063 hr_utility.set_location('Entering excep:'||l_proc, 395);
6064 p_error_message := p_error_message || sqlerrm;
6065
6066 end pqp_write_public;
6067
6068 -- =============================================================================
6069 -- Name : pqp_batch_size
6070 -- Purpose : the procedure returns the batch size.
6071 -- Arguments :
6072 -- Out : p_batch_size
6073 -- Notes : private
6074 -- =============================================================================
6075 procedure pqp_batch_size
6076 (p_batch_size out NOCOPY number
6077 ) is
6078
6079 l_batch_size number;
6080 l_proc constant varchar2(72) := g_package || 'pqp_batch_size';
6081
6082 begin
6083 hr_utility.set_location('Entering :' || l_proc, 10);
6084
6085 if (c_pay_action_parameter%isopen = true) then
6086 close c_pay_action_parameter;
6087 end if;
6088
6089 open c_pay_action_parameter;
6090 l_batch_size := null;
6091 loop
6092 fetch c_pay_action_parameter
6093 into l_batch_size;
6094 exit when c_pay_action_parameter%notfound;
6095 end loop;
6096 close c_pay_action_parameter;
6097
6098 hr_utility.set_location(l_proc, 20);
6099
6100 p_batch_size := l_batch_size;
6101
6102 if (l_batch_size is null) then
6103 p_batch_size := null;
6104 end if;
6105
6106 hr_utility.set_location('Leaving : ' || l_proc, 30);
6107
6108 exception
6109 when OTHERS then
6110 hr_utility.set_location('Entering excep:'||l_proc, 35);
6111 p_batch_size := null;
6112
6113 end pqp_batch_size;
6114
6115 -- =============================================================================
6116 -- Name : ResetForReadAPI
6117 -- Purpose : This resets the status in pay_process_events table back to 'N'.
6118 -- Arguments :
6119 -- IN : p_process_event_id
6120 -- Notes : public
6121 -- =============================================================================
6122 procedure ResetForReadAPI
6123 (p_process_event_id in number
6124 ) is
6125
6126 l_ovn number;
6127 l_proc constant varchar2(72) := g_package || 'ResetForReadAPI';
6128
6129 begin
6130 l_ovn := -1;
6131 hr_utility.set_location('Entering:'||l_proc, 5);
6132
6133 for cop in c_ovn_ppe(p_process_event_id)
6134 loop
6135 l_ovn := cop.object_version_number;
6136 end loop;
6137 if (l_ovn is not null and l_ovn <> -1) then
6138
6139 hr_utility.set_location(l_proc, 10);
6140
6141 pay_ppe_api.update_process_event
6142 (p_validate => false
6143 ,p_status => 'N'
6144 ,p_description => null
6145 ,p_process_event_id => p_process_event_id
6146 ,p_object_version_number => l_ovn
6147 );
6148
6149 end if;
6150
6151 hr_utility.set_location('Leaving:'||l_proc, 20);
6152
6153 end ResetForReadAPI;
6154
6155 -- =============================================================================
6156 -- Name : AbortReadAPI
6157 -- Purpose : This resets the status in pay_process_events table to 'C'.
6158 -- Arguments :
6159 -- IN : p_process_event_id
6160 -- Notes : public
6161 -- =============================================================================
6162 procedure AbortReadAPI
6163 (p_process_event_id in number
6164 ) is
6165
6166 l_ovn number;
6167 l_proc constant varchar2(72) := g_package || 'AbortReadAPI';
6168
6169 begin
6170
6171 hr_utility.set_location('Entering:'||l_proc, 5);
6172
6173 for cop in c_ovn_ppe(p_process_event_id)
6174 loop
6175 l_ovn := cop.object_version_number;
6176 end loop;
6177
6178 if (l_ovn is not null and l_ovn <> -1) then
6179
6180 hr_utility.set_location(l_proc, 10);
6181
6182 pay_ppe_api.update_process_event
6183 (p_validate => false
6184 ,p_status => 'C'
6185 ,p_description => 'This record was forcibly ABORTED using workflow'
6186 ,p_process_event_id => p_process_event_id
6187 ,p_object_version_number => l_ovn
6188 );
6189
6190 end if;
6191
6192 hr_utility.set_location('Leaving:' ||l_proc, 20);
6193
6194 end AbortReadAPI;
6195
6196
6197 end pqp_alien_expat_taxation_pkg;
[Home] [Help]