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