1 PACKAGE BODY PQP_ALIEN_EXPAT_WF_PKG AS
2 /* $Header: pqpalntf.pkb 115.6 2003/02/14 19:19:54 tmehra noship $ */
3 --
4 -- Procedure
5 -- StartAlienExpatWFProcess
6 --
7 -- Description
8 -- Start the Alien/ Expat workflow process for the given p_process_event_id
9 --
10 procedure StartAlienExpatWFProcess (
11 p_process_event_id in number
12 ,p_tran_type in varchar2
13 ,p_tran_date in date
14 ,p_itemtype in varchar2
15 ,p_alien_transaction_id in number
16 ,p_assignment_id in number
17 ,p_process_name in varchar2 ) IS
18 --
19 --
20 l_contact_source varchar2(50);
21 l_assignment_id per_all_assignments_f.assignment_id%type;
22 l_description pay_process_events.description%type;
23 l_error_indicator pqp_alien_transaction_data.error_indicator%type;
24 l_error_text pqp_alien_transaction_data.error_text%type;
25 l_person_id per_all_people_f.person_id%type;
26 l_next_contact_person_id per_all_people_f.person_id%type;
27 l_contact_user_name wf_users.name%type;
28 l_emp_username wf_users.name%type;
29 l_emp_disp_name wf_users.display_name%type;
30 l_emp_full_name per_all_people_f.full_name%type;
31 l_item_key wf_items.item_key%type;
32 l_income_code pqp_alien_transaction_data.income_code%type;
33 l_income_desc hr_lookups.meaning%type;
34 --
35 --
36 --
37 cursor csr_getpayprcdet is
38 SELECT assignment_id, description
39 FROM pay_process_events
40 WHERE process_event_id = p_process_event_id;
41 --
42 cursor csr_getaliendet is
43 SELECT ptd.error_indicator, ptd.error_text, ptd.income_code, hrl.meaning
44 FROM pqp_alien_transaction_data ptd, hr_lookups hrl
45 WHERE alien_transaction_id = p_alien_transaction_id
46 AND ptd.income_code = hrl.lookup_code
47 AND hrl.lookup_type = 'PQP_US_ALIEN_INCOME_BALANCE';
48 --
49 cursor csr_getperid is
50 SELECT person_id
51 FROM per_all_assignments_f paf
52 WHERE assignment_id = l_assignment_id
53 and trunc(p_tran_date) between
54 paf.effective_start_date and
55 paf.effective_end_date;
56 --
57 cursor csr_getperdet is
58 SELECT full_name
59 FROM per_all_people_f pap
60 WHERE person_id = l_person_id
61 and trunc(p_tran_date) between
62 pap.effective_start_date and
63 pap.effective_end_date;
64
65 -- Get HR/ Payroll Contact User Name
66 cursor csr_payroll_contact is
67 select prl.prl_information1
68 from pay_payrolls_f prl
69 ,per_assignments_f paf
70 where prl.payroll_id = paf.payroll_id
71 and prl.prl_information_category = 'US'
72 and paf.assignment_id = l_assignment_id
73 and trunc(p_tran_date) between prl.effective_start_date
74 and prl.effective_end_date;
75 -- Get HR/ Payroll contact from GRE
76 cursor csr_gre_contact is
77 select org.org_information1
78 from hr_organization_information org
79 where org.org_information_context = 'Contact Information'
80 and org.organization_id = (
81 select hsc.segment1
82 from per_assignments_f paf
83 ,hr_soft_coding_keyflex hsc
84 where hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
85 and paf.assignment_id = l_assignment_id
86 and trunc(p_tran_date) between paf.effective_start_date and
87 paf.effective_end_date);
88 --
89 begin
90 --
91 --
92 -- Get the next item key from the sequence
93 select pqp_alntf_wf_item_key_s.nextval
94 into l_item_key
95 from sys.dual;
96
97 --
98 if p_tran_type = 'READ' then
99 open csr_getpayprcdet;
100 -- Get Assignment ID, Error Message
101
102 fetch csr_getpayprcdet into l_assignment_id, l_description;
103 if csr_getpayprcdet%notfound then
104 null;
105 -- ?? Check with **** Error Message
106 -- hr_utility.set_message(XXX,'XXX');
107 -- hr_utility.raise_error;
108 end if;
109 close csr_getpayprcdet;
110 else
111 l_assignment_id := p_assignment_id;
112 open csr_getaliendet;
113 -- Get Assignment ID, Error Message
114 fetch csr_getaliendet into
115 l_error_indicator, l_error_text, l_income_code, l_income_desc;
116 if csr_getaliendet%notfound then
117 null;
118 -- ?? Check with **** Error Message
119 -- hr_utility.set_message(XXX,'XXX');
120 -- hr_utility.raise_error;
121 end if;
122 close csr_getaliendet;
123 end if;
124 --
125 -- Get Employee ID
126 open csr_getperid;
127 fetch csr_getperid into l_person_id;
128 if csr_getperid%notfound then
129 null;
130 -- ?? Check with **** Error Message
131 -- hr_utility.set_message(XXX,'XXX');
132 -- hr_utility.raise_error;
133 end if;
134 close csr_getperid;
135 --
136 -- Get FND profile value
137 l_contact_source := fnd_profile.value('HR_PAYROLL_CONTACT_SOURCE');
138 -- expose the wf control variables to the custom package
139 --
140 if l_contact_source = 'CUSTOM' then
141 -- call a custom notifier hook
142 l_next_contact_person_id :=
143 hr_approval_custom.Get_Next_Payroll_Notifier
144 (p_person_id => l_person_id);
145 elsif l_contact_source = 'PAYROLL' then
146 open csr_payroll_contact;
147 fetch csr_payroll_contact into l_contact_user_name;
148 close csr_payroll_contact;
149 elsif l_contact_source = 'GRE' then
150 open csr_gre_contact;
151 fetch csr_gre_contact into l_contact_user_name;
152 close csr_gre_contact;
153 -- else -- some other source we don't understand yet
154 -- result := 'ERROR:UNKNOWN_CONTACT_SOURCE';
155 end if;
156
157 -- Creates a new runtime process for the WF Item Type passed)
158 --
159 wf_engine.createProcess( ItemType => p_ItemType,
160 ItemKey => l_item_key,
161 process => p_process_name );
162 --
163 --
164 wf_engine.SetItemAttrDate ( itemtype => p_itemtype,
165 itemkey => l_item_key,
166 aname => 'ERR_DATE',
167 avalue => p_tran_date );
168 --
169 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
170 itemkey => l_item_key,
171 aname => 'TRAN_TYPE',
172 avalue => p_tran_type );
173 --
174 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
175 itemkey => l_item_key,
176 aname => 'CURRENT_ASSIGNMENT_ID',
177 avalue => l_assignment_id );
178 --
179 if p_tran_type = 'READ' then
180 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
181 itemkey => l_item_key,
182 aname => 'PROCESS_EVENT_ID',
183 avalue => p_process_event_id );
184 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
185 itemkey => l_item_key,
186 aname => 'ERR_MSG',
187 avalue => l_description );
188 else
189 wf_engine.SetItemAttrText ( itemtype=> p_itemtype,
190 itemkey => l_item_key,
191 aname => 'ERROR_INDICATOR',
192 avalue => l_error_indicator );
193 wf_engine.SetItemAttrText ( itemtype=> p_itemtype,
194 itemkey => l_item_key,
195 aname => 'ERROR_TEXT',
196 avalue => l_error_text );
197 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
198 itemkey => l_item_key,
199 aname => 'ALIEN_TRANSACTION_ID',
200 avalue => p_alien_transaction_id );
201
202 wf_engine.SetItemAttrText ( itemtype=> p_itemtype,
203 itemkey => l_item_key,
204 aname => 'INCOME_CODE',
205 avalue => l_income_code ||' ('||
206 l_income_desc||')' );
207 --
208 if l_error_indicator = 'WARNING : RETRO LOSS' then
209 wf_engine.SetItemAttrText(
210 itemtype=> p_itemtype,
211 itemkey => l_item_key,
212 aname => 'IS_RETRO',
213 avalue => 'Y' );
214 --
215 elsif l_error_indicator = 'WARNING : CHANGED INCOME CODE' then
216 wf_engine.SetItemAttrText(
217 itemtype=> p_itemtype,
218 itemkey => l_item_key,
219 aname => 'INCOME_CODE_CHANGED',
220 avalue => 'Y' );
221
222 wf_engine.SetItemAttrText ( itemtype=> p_itemtype,
223 itemkey => l_item_key,
224 aname => 'ERROR_TEXT',
225 avalue => ' is the analyzed employment income code.'||
226 ' Please attach this earnings element for ');
227
228 elsif l_error_indicator = 'WARNING : INVALID INCOME CODE' then
229 wf_engine.SetItemAttrText(
230 itemtype=> p_itemtype,
231 itemkey => l_item_key,
232 aname => 'INCOME_CODE_CHANGED',
233 avalue => 'Y' );
234
235 wf_engine.SetItemAttrText ( itemtype=> p_itemtype,
236 itemkey => l_item_key,
237 aname => 'ERROR_TEXT',
238 avalue => ' is no longer a valid employment income'||
239 ' code for ');
240 end if;
241
242 end if;
243
244 --
245 -- Get Employee details from the WF Dir Services
246 if l_contact_source = 'CUSTOM' then
247 wf_directory.GetUserName( p_orig_system => 'PER',
248 p_orig_system_id => l_next_contact_person_id,
249 p_name => l_emp_username,
250 p_display_name => l_emp_disp_name );
251 l_contact_user_name := l_emp_username;
252 else
253 wf_directory.GetUserName( p_orig_system => 'PER',
254 p_orig_system_id => l_person_id,
255 p_name => l_emp_username,
256 p_display_name => l_emp_disp_name );
257 end if;
258 --
259 --
260 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
261 itemkey => l_item_key,
262 aname => 'PERSON_ID',
263 avalue => l_person_id );
264 --
265
266 -- If Emp Name is not in WF Dir services then get from PER_ALL_PEOPLE_F
267 if l_emp_disp_name Is Null then
268 open csr_getperdet;
269 fetch csr_getperdet into l_emp_full_name;
270 close csr_getperdet;
271 l_emp_disp_name := l_emp_full_name;
272 end if;
273 --
274 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
275 itemkey => l_item_key,
276 aname => 'EMP_USERNAME',
277 avalue => l_emp_username );
278 --
279 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
280 itemkey => l_item_key,
281 aname => 'PERSON_DISPLAY_NAME',
282 avalue => l_emp_disp_name );
283
284 --
285 -- Set HR/ Payroll Contact User Name for Notification
286 --
287 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
288 itemkey => l_item_key,
289 aname => 'CONTACT_USERNAME',
290 avalue => l_contact_user_name );
291 --
292 --
293 wf_engine.StartProcess ( ItemType => p_itemtype,
294 ItemKey => l_item_key );
295 --
296 --
297 end StartAlienExpatWFProcess;
298 --
299 --
300 PROCEDURE check_req_ntf ( itemtype in varchar2,
301 itemkey in varchar2,
302 actid in number,
303 funcmode in varchar2,
304 result in out nocopy varchar2) is
305 --
306 --
307 --
308 begin
309 --
310 --
311 if funcmode = 'RUN' then
312 if Upper(wf_engine.GetItemAttrText
313 (itemtype => itemtype,
314 itemkey => itemkey ,
315 aname => 'REQ_NTF'
316 )) = 'Y' then
317 result := 'COMPLETE:Y';
318 return;
319 else
320 result := 'COMPLETE:N';
321 return;
322 end if;
323 end if;
324 --
325 -- Other execution modes may be created in the future.
326 -- Activity indicates that it does not implement a mode
327 -- by returning null
328 --
329 result := '';
330 return;
331 --
332 exception
333 when others then
334 -- The line below records this function call in the error system
335 -- in the case of an exception.
336 wf_core.context('PQPALNTF', 'PQP_ALIEN_EXPAT_WF_PKG.check_req_ntf',itemtype, itemkey, to_char(actid), funcmode);
337 raise;
338 result := '';
339 return;
340 --
341 --
342 end check_req_ntf;
343 --
344 --
345 PROCEDURE find_ntfr ( itemtype in varchar2,
346 itemkey in varchar2,
347 actid in number,
348 funcmode in varchar2,
349 result in out nocopy varchar2) is
350 --
351 --
352 --
353 begin
354 --
355 --
356 if funcmode = 'RUN' then
357 if wf_engine.GetItemAttrText
358 (itemtype => itemtype,
359 itemkey => itemkey ,
360 aname => 'CONTACT_USERNAME'
361 ) Is Null then
362 wf_engine.SetItemAttrText (
363 itemtype => itemtype,
364 itemkey => itemkey,
365 aname => 'CONTACT_USERNAME',
366 avalue => 'SYSADMIN' );
367 result := 'COMPLETE:YES';
368 return;
369 end if;
370 end if;
371 --
372 -- Other execution modes may be created in the future.
373 -- Activity indicates that it does not implement a mode
374 -- by returning null
375 --
376 result := '';
377 return;
378 --
379 exception
380 when others then
381 -- The line below records this function call in the error system
382 -- in the case of an exception.
383 wf_core.context('PQPALNTF', 'PQP_ALIEN_EXPAT_WF_PKG.find_ntfr',itemtype, itemkey, to_char(actid), funcmode);
384 raise;
385 result := '';
386 return;
387 --
388 --
389 end find_ntfr;
390 --
391 --
392 PROCEDURE check_tran_type ( itemtype in varchar2,
393 itemkey in varchar2,
394 actid in number,
395 funcmode in varchar2,
396 result in out nocopy varchar2) is
397 --
398 --
399 --
400 begin
401 --
402 --
403 if funcmode = 'RUN' then
404 if wf_engine.GetItemAttrText
405 (itemtype => itemtype,
406 itemkey => itemkey ,
407 aname => 'TRAN_TYPE'
408 ) = 'READ' then
409 --
410 result := 'COMPLETE:READ';
411 return;
412 else
413 result := 'COMPLETE:WRITE';
414 return;
415 end if;
416 end if;
417 --
418 -- Other execution modes may be created in the future.
419 -- Activity indicates that it does not implement a mode
420 -- by returning null
421 --
422 result := '';
423 return;
424 --
425 exception
426 when others then
427 -- The line below records this function call in the error system
428 -- in the case of an exception.
429 wf_core.context('PQPALNTF', 'PQP_ALIEN_EXPAT_WF_PKG.check_tran_type',itemtype, itemkey, to_char(actid), funcmode);
430 raise;
431 result := '';
432 return;
433 --
434 --
435 end check_tran_type;
436 --
437 procedure reset_read_api_retry
438 ( itemtype in varchar2
439 ,itemkey in varchar2
440 ,actid in number
441 ,funcmode in varchar2
442 ,result in out nocopy varchar2) is
443 --
444 --
445 --
446 begin
447 --
448 --
449 if funcmode = 'RUN' then
450 -- Call Reset Read API
451 -- Get Process Event ID
452 pqp_alien_expat_taxation_pkg.ResetForReadAPI
453 (p_process_event_id => wf_engine.GetItemAttrNumber
454 (itemtype => itemtype,
455 itemkey => itemkey ,
456 aname => 'PROCESS_EVENT_ID'
457 )
458 );
459 --
460 result := 'COMPLETE:SUCCESS';
461 return;
462 end if;
463 --
464 -- Other execution modes may be created in the future.
465 -- Activity indicates that it does not implement a mode
466 -- by returning null
467 --
468 result := '';
469 return;
470 --
471 exception
472 when others then
473 -- The line below records this function call in the error system
474 -- in the case of an exception.
475 wf_core.context('PQPALNTF', 'PQP_ALIEN_EXPAT_WF_PKG.reset_read_api_retry',itemtype, itemkey, to_char(actid), funcmode);
476 raise;
477 result := '';
478 return;
479 --
480 --
481 end reset_read_api_retry;
482 --
483 procedure abort_read_api_retry
484 ( itemtype in varchar2
485 ,itemkey in varchar2
486 ,actid in number
487 ,funcmode in varchar2
488 ,result in out nocopy varchar2) is
489 --
490 --
491 --
492 begin
493 --
494 --
495 if funcmode = 'RUN' then
496 -- Call Abort Read API
497 --
498 pqp_alien_expat_taxation_pkg.AbortReadAPI
499 (p_process_event_id => wf_engine.GetItemAttrNumber
500 (itemtype => itemtype,
501 itemkey => itemkey ,
502 aname => 'PROCESS_EVENT_ID'
503 )
504 );
505 result := 'COMPLETE:';
506 return;
507 end if;
508 --
509 -- Other execution modes may be created in the future.
510 -- Activity indicates that it does not implement a mode
511 -- by returning null
512 --
513 result := '';
514 return;
515 --
516 exception
517 when others then
518 -- The line below records this function call in the error system
519 -- in the case of an exception.
520 wf_core.context('PQPALNTF', 'PQP_ALIEN_EXPAT_WF_PKG.abort_read_api_retry',itemtype, itemkey, to_char(actid), funcmode);
521 raise;
522 result := '';
523 return;
524 end abort_read_api_retry;
525 --
526 --
527 PROCEDURE check_if_retro_loss ( itemtype in varchar2,
528 itemkey in varchar2,
529 actid in number,
530 funcmode in varchar2,
531 result in out nocopy varchar2) is
532 --
533 --
534 --
535 begin
536 --
537 --
538 if funcmode = 'RUN' then
539 if Upper(wf_engine.GetItemAttrText
540 (itemtype => itemtype,
541 itemkey => itemkey ,
542 aname => 'IS_RETRO'
543 )) = 'Y' then
544 result := 'COMPLETE:Y';
545 return;
546 else
547 result := 'COMPLETE:N';
548 return;
549 end if;
550 end if;
551 --
552 -- Other execution modes may be created in the future.
553 -- Activity indicates that it does not implement a mode
554 -- by returning null
555 --
556 result := '';
557 return;
558 --
559 exception
560 when others then
561 -- The line below records this function call in the error system
562 -- in the case of an exception.
563 wf_core.context('PQPALNTF', 'PQP_ALIEN_EXPAT_WF_PKG.check_if_retro_loss',
564 itemtype, itemkey, to_char(actid), funcmode);
565 raise;
566 result := '';
567 return;
568 --
569 --
570 end check_if_retro_loss;
571 --
572
573 PROCEDURE check_income_code_change( itemtype in varchar2,
574 itemkey in varchar2,
575 actid in number,
576 funcmode in varchar2,
577 result in out nocopy varchar2) is
578 --
579 begin
580 --
581 if funcmode = 'RUN' then
582 if Upper(wf_engine.GetItemAttrText(
583 itemtype => itemtype,
584 itemkey => itemkey ,
585 aname => 'INCOME_CODE_CHANGED')) = 'Y' then
586 result := 'COMPLETE:Y';
587 return;
588 else
589 result := 'COMPLETE:N';
590 return;
591 end if;
592 end if;
593 --
594 -- Other execution modes may be created in the future.
595 -- Activity indicates that it does not implement a mode
596 -- by returning null
597 --
598 result := '';
599 return;
600 --
601 exception
602 when others then
603 -- The line below records this function call in the error system
604 -- in the case of an exception.
605 wf_core.context('PQPALNTF',
606 'PQP_ALIEN_EXPAT_WF_PKG.check_if_retro_loss',
607 itemtype,
608 itemkey,
609 to_char(actid),
610 funcmode);
611 raise;
612 result := '';
613 return;
614 --
615 --
616 end check_income_code_change;
617 --
618
619 end PQP_ALIEN_EXPAT_WF_PKG;
[Home] [Help]