[Home] [Help]
PACKAGE BODY: APPS.IGI_ITR_APPROVAL_PKG
Source
1 PACKAGE BODY IGI_ITR_APPROVAL_PKG AS
2 -- $Header: igiitrwb.pls 120.10.12000000.2 2007/09/17 16:35:44 smannava ship $
3 --
4
5 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 l_state_level number := FND_LOG.LEVEL_STATEMENT;
7 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
8 l_event_level number := FND_LOG.LEVEL_EVENT;
9 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
10 l_error_level number := FND_LOG.LEVEL_ERROR;
11 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
12 l_path VARCHAR2(50) := 'IGI.PLSQL.igiitrwb.IGI_ITR_APPROVAL_PKG.';
13
14
15 -- ****************************************************************************
16 -- Private procedure: Display diagnostic message
17 -- ****************************************************************************
18 PROCEDURE diagn_msg (p_level IN NUMBER, p_path IN VARCHAR2, p_mesg IN VARCHAR2) IS
19 BEGIN
20 IF (p_level >= l_debug_level ) THEN
21 FND_LOG.STRING (p_level , l_path || p_path , p_mesg );
22 END IF;
23
24 END ;
25
26
27 -- ****************************************************************************
28 -- Private function: Get authorization limit
29 -- ****************************************************************************
30 FUNCTION get_authorization_limit (p_employee_id NUMBER,
31 p_set_of_books_id NUMBER) RETURN NUMBER IS
32 l_limit NUMBER;
33 BEGIN
34
35 SELECT nvl(authorization_limit, 0)
36 INTO l_limit
37 FROM GL_AUTHORIZATION_LIMITS
38 WHERE employee_id = p_employee_id
39 AND ledger_id = p_set_of_books_id;
40
41 return (l_limit);
42
43 EXCEPTION
44 WHEN NO_DATA_FOUND THEN
45 l_limit := 0;
46 return (l_limit);
47 WHEN OTHERS THEN
48 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'get_authorization_limit',
49 null, null, null );
50 IF ( l_unexp_level >= l_debug_level) THEN
51 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
52 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
53 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
54 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.get_authorization_limit',TRUE);
55 END IF;
56 raise;
57 END get_authorization_limit;
58
59
60
61 -- ****************************************************************************
62 -- Start_Approval_Workflow
63 -- ****************************************************************************
64 PROCEDURE start_approval_workflow (p_cc_id IN NUMBER,
65 p_cc_line_num IN NUMBER,
66 p_preparer_fnd_user_id IN NUMBER,
67 p_cc_name IN VARCHAR2,
68 p_prep_auth IN VARCHAR2,
69 p_sec_apprv_fnd_id IN NUMBER) IS
70
71 -- Local variables
72 l_itemtype VARCHAR2(10) := 'ITRAPPRV';
73 l_itemkey VARCHAR2(50) ;
74 l_approval_run_id NUMBER;
75 l_preparer_id NUMBER;
76 l_preparer_name VARCHAR2(240);
77 l_preparer_display_name VARCHAR2(240);
78 l_sysadmin_id NUMBER;
79 l_sysadmin_name VARCHAR2(240);
80 l_sysadmin_display_name VARCHAR2(240);
81 l_sec_approver_id NUMBER;
82 l_sec_approver_name VARCHAR2(240);
83 l_sec_approver_display_name VARCHAR2(240);
84 l_userkey VARCHAR2(116);
85 l_func_currency VARCHAR2(15);
86 BEGIN
87 diagn_msg(l_state_level,'start_approval_workflow','Executing Start_Approval_Workflow for ITR cross charge line
88 '|| to_char(p_cc_id)||'*'||to_char(p_cc_line_num));
89
90
91 -- Update the status of the service line to 'W'
92 -- Awaiting creation approval
93 UPDATE igi_itr_charge_lines
94 SET status_flag = 'W'
95 ,submit_date = sysdate
96 WHERE it_header_id = p_cc_id
97 AND it_line_num = p_cc_line_num;
98
99
100
101 -- Get approval run id
102 SELECT IGI_ITR_APPROVAL_SS_S.nextval
103 INTO l_approval_run_id
104 FROM SYS.DUAL;
105
106 -- generate the item key
107 l_itemkey := to_char(p_cc_id)|| '/' ||to_char(p_cc_line_num)|| '/' || to_char(l_approval_run_id);
108
109 diagn_msg(l_state_level,'start_approval_workflow','Generated Item Key = ' ||l_itemkey);
110
111 -- generate the user key
112 l_userkey := p_cc_name||'/'||to_char(p_cc_line_num) ;
113
114 diagn_msg(l_state_level,'start_approval_workflow','Generated User Key = ' ||l_userkey);
115
116
117 -- Kick Off workflow process
118 wf_engine.CreateProcess( itemtype => l_itemtype,
119 itemkey => l_itemkey,
120 process => 'ITR_APPROVAL_TOP_PROCESS' );
121 diagn_msg(l_state_level,'start_approval_workflow','Process for ITR_APPROVAL_TOP_PROCESS created');
122
123 -- Set item user key
124 wf_engine.SetItemUserKey( itemtype => l_itemtype,
125 itemkey => l_itemkey,
126 userkey => l_userkey );
127
128 -- Set cross charge id (IT_HEADER_ID)
129 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
130 itemkey => l_itemkey,
131 aname => 'CROSS_CHARGE_ID',
132 avalue => p_cc_id );
133 diagn_msg(l_state_level,'start_approval_workflow','Attribute CROSS_CHARGE_ID set to' ||to_char(p_cc_id));
134
135 -- Set cross charge line num (IT_LINE_NUM)
136 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
137 itemkey => l_itemkey,
138 aname => 'CC_LINE_NUM',
139 avalue => p_cc_line_num );
140
141 -- Set cross charge name (CC_NAME)
142 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
143 itemkey => l_itemkey,
144 aname => 'CC_NAME',
145 avalue => p_cc_name );
146 diagn_msg(l_state_level,'start_approval_workflow','Attribute CC_NAME set to ' ||p_cc_name);
147
148 -- Set the unique item key
149 wf_engine.SetItemAttrText( itemtype => l_itemtype,
150 itemkey => l_itemkey,
151 aname => 'UNIQUE_ITEMKEY',
152 avalue => l_itemkey );
153 diagn_msg(l_state_level,'start_approval_workflow','Set the unique item key: '||l_itemkey);
154
155
156 -- Get employee ID
157 SELECT employee_id
158 INTO l_preparer_id
159 FROM fnd_user
160 WHERE user_id = p_preparer_fnd_user_id;
161
162 -- Set PersonID attribute (HR personID from PER_PERSONS_F)
163 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
164 itemkey => l_itemkey,
165 aname => 'PREPARER_ID',
166 avalue => l_preparer_id);
167 diagn_msg(l_state_level,'start_approval_workflow','Attribute PREPARER_ID set to ' ||l_preparer_id );
168
169 -- Set UserID attribute (AOL userID from FND_USER table).
170 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
171 itemkey => l_itemkey,
172 aname => 'PREPARER_FND_ID',
173 avalue => p_preparer_fnd_user_id);
174
175 -- Retrieve preparer's User name (Login name for Apps) and displayed name
176 wf_directory.GetUserName(p_orig_system => 'PER',
177 p_orig_system_id => l_preparer_id,
178 p_name => l_preparer_name,
179 p_display_name => l_preparer_display_name );
180 diagn_msg(l_state_level,'start_approval_workflow','Retrieved user name: '||l_preparer_name);
181
182 -- Copy username to Workflow
183 wf_engine.SetItemAttrText( itemtype => l_itemtype,
184 itemkey => l_itemkey,
185 aname => 'PREPARER_NAME',
186 avalue => l_preparer_name );
187 diagn_msg(l_state_level,'start_approval_workflow','Attribute PREPARER_NAME set to' ||l_preparer_name);
188
189 -- Copy displayed username to Workflow
190 wf_engine.SetItemAttrText( itemtype => l_itemtype,
191 itemkey => l_itemkey,
192 aname => 'PREPARER_DISPLAY_NAME',
193 avalue => l_preparer_display_name );
194 diagn_msg(l_state_level,'start_approval_workflow','Attribute PREPARER_DISPLAY_NAME set to '||l_preparer_display_name);
195
196 -- Populate preparer authorised attribute
197 wf_engine.SetItemAttrText( itemtype => l_itemtype,
198 itemkey => l_itemkey,
199 aname => 'PREPARER_AUTH',
200 avalue => p_prep_auth );
201 diagn_msg(l_state_level,'start_approval_workflow','Attribute PREPARER_AUTH set to '||p_prep_auth);
202
203 IF p_sec_apprv_fnd_id is NOT NULL THEN
204 -- set secondary approver attributes
205
206 -- Get employee ID of secondary approver
207 SELECT employee_id
208 INTO l_sec_approver_id
209 FROM fnd_user
210 WHERE user_id = p_sec_apprv_fnd_id;
211
212 -- Set PersonID attribute (HR personID from PER_PERSONS_F)
213 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
214 itemkey => l_itemkey,
215 aname => 'SEC_APPROVER_ID',
216 avalue => l_sec_approver_id);
217 diagn_msg(l_state_level,'start_approval_workflow','Attribute SEC_APPROVER_ID set to ' ||l_sec_approver_id );
218
219 -- Set UserID attribute (AOL userID from FND_USER table).
220 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
221 itemkey => l_itemkey,
222 aname => 'SEC_APPROVER_FND_ID',
223 avalue => p_sec_apprv_fnd_id);
224
225 -- Retrieve Secondary Approver's User name (Login name for Apps)
226 -- and displayed name
227 wf_directory.GetUserName(p_orig_system => 'PER',
228 p_orig_system_id => l_sec_approver_id,
229 p_name => l_sec_approver_name,
230 p_display_name => l_sec_approver_display_name );
231 diagn_msg(l_state_level,'start_approval_workflow','Retrieved user name: '||l_sec_approver_name);
232
233 -- Copy username to Workflow
234 wf_engine.SetItemAttrText( itemtype => l_itemtype,
235 itemkey => l_itemkey,
236 aname => 'SEC_APPROVER_NAME',
237 avalue => l_sec_approver_name );
238 diagn_msg(l_state_level,'start_approval_workflow','Attribute SEC_APPROVER_NAME set to' ||l_sec_approver_name);
239
240 -- Copy displayed username to Workflow
241 wf_engine.SetItemAttrText( itemtype => l_itemtype,
242 itemkey => l_itemkey,
243 aname => 'SEC_APPROVER_DISPLAY_NAME',
244 avalue => l_sec_approver_display_name );
245 diagn_msg(l_state_level,'start_approval_workflow','Attribute SEC_APPROVER_DISPLAY_NAME set to '||
246 l_sec_approver_display_name);
247
248 END IF;
249
250 -- Finally, start the process
251 wf_engine.StartProcess( itemtype => l_itemtype,
252 itemkey => l_itemkey );
253
254 diagn_msg(l_state_level,'start_approval_workflow','Process ITR_APPROVAL_PROCESS started');
255
256 commit;
257
258 EXCEPTION
259 WHEN OTHERS THEN
260 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'start_approval_workflow', l_itemtype, l_itemkey);
261 IF ( l_unexp_level >= l_debug_level) THEN
262 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
263 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
264 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
265 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.start_approval_workflow',TRUE);
266 END IF;
267 raise;
268
269 END start_approval_workflow;
270
271
272 --
273 -- *****************************************************************************
274 -- Get_SOB_Attributes
275 -- *****************************************************************************
276 --
277
278 --
279 -- Procedure
280 -- Get_SOB_Attributes
281 -- Purpose
282 -- Copy information about the SOB to worklow tables
283 -- History
284 -- 27-SEP-2000 S Brewer Created.
285 -- Arguments
286 -- itemtype Workflow item type (ITR Approval)
287 -- itemkey ID of cross charge
288 -- actid ID of activity, provided by workflow engine
289 -- (not used in this procedure)
290 -- funcmode Function mode (RUN or CANCEL)
291 -- result Result code of the activity
292 -- Example
293 -- N/A (not user-callable)
294 --
295 -- Notes
296 -- This procedure is called from the Oracle Workflow engine
297 -- It retrieves data elements about the Set of Books (identified by the itemkey
298 -- argument) and stores them in the workflow tables to make them available
299 -- for messages and subsequent procedures.
300 --
301 PROCEDURE get_sob_attributes ( itemtype IN VARCHAR2,
302 itemkey IN VARCHAR2,
303 actid IN NUMBER,
304 funcmode IN VARCHAR2,
305 result OUT NOCOPY VARCHAR2 ) IS
306 l_func_currency VARCHAR2(15);
307 l_cross_charge_id NUMBER;
308 l_set_of_books_id NUMBER;
309 l_timeout_days NUMBER;
310 l_timeout_mins NUMBER;
311 --
312 BEGIN
313
314 IF ( funcmode = 'RUN' ) THEN
315 -- Get Cross Charge ID
316 l_cross_charge_id := wf_engine.GetItemAttrNumber(
317 itemtype => itemtype,
318 itemkey => itemkey,
319 aname => 'CROSS_CHARGE_ID');
320
321
322 -- Get set of books id
323 SELECT set_of_books_id
324 INTO l_set_of_books_id
325 FROM igi_itr_charge_headers
326 WHERE it_header_id = l_cross_charge_id;
327
328 -- Retrieve set of books attributes
329
330 SELECT currency_Code
331 INTO l_func_currency
332 FROM gl_sets_of_books
333 WHERE set_of_books_id = l_set_of_books_id;
334
335 -- find the timeout value (automatic approval exceed days)
336 -- chosen for the set of books (default value should be 7 if
337 -- no value was chosen )
338
339 SELECT nvl(auto_approve_exceed_days,7)
340 INTO l_timeout_days
341 FROM igi_itr_charge_setup
342 WHERE set_of_books_id = l_set_of_books_id;
343
344 l_timeout_mins := l_timeout_days*24*60;
345
346 diagn_msg(l_state_level,'get_sob_attributes','SOB Attributes retrieved from db');
347
348 -- Set the corresponding attributes in workflow
349 wf_engine.SetItemAttrText ( itemtype => itemtype,
350 itemkey => itemkey,
351 aname => 'FUNC_CURRENCY',
352 avalue => l_func_currency );
353 diagn_msg(l_state_level,'get_sob_attributes','Get_SOB_Attributes: Func currency = '||l_func_currency);
354
355 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
356 itemkey => itemkey,
357 aname => 'SET_OF_BOOKS_ID',
358 avalue => l_set_of_books_id );
359 diagn_msg(l_state_level,'get_sob_attributes','Get_SOB_Attributes: Set of books id : ' ||to_char(l_set_of_books_id ));
360
361 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
362 itemkey => itemkey,
363 aname => 'TIMEOUT_DAYS',
364 avalue => l_timeout_days );
365 diagn_msg(l_state_level,'get_sob_attributes','Get_SOB_Attributes: timeout days: ' ||to_char(l_timeout_days));
366
367 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
368 itemkey => itemkey,
369 aname => 'TIMEOUT_MINS',
370 avalue => l_timeout_mins );
371 diagn_msg(l_state_level,'get_sob_attributes','Get_SOB_Attributes: timeout mins: ' ||to_char(l_timeout_mins));
372
373 ELSIF ( funcmode = 'CANCEL' ) THEN
374 null;
375 END IF;
376
377 EXCEPTION
378 WHEN OTHERS THEN
379 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'get_sob_attributes', itemtype, itemkey);
380 IF ( l_unexp_level >= l_debug_level) THEN
381 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
382 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
383 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
384 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.get_sob_attributes',TRUE);
385 END IF;
386 raise;
387 END get_sob_attributes;
388
389
390 --
391 -- *****************************************************************************
392 -- Get_CC_Attributes
393 -- *****************************************************************************
394 --
395 PROCEDURE get_cc_attributes(itemtype IN VARCHAR2,
396 itemkey IN VARCHAR2,
397 actid IN NUMBER,
398 funcmode IN VARCHAR2,
399 result OUT NOCOPY VARCHAR2 ) IS
400 l_cross_charge_id NUMBER;
401 l_cc_line_num NUMBER;
402 l_service_line_id NUMBER;
403 l_cc_line_tot NUMBER;
404 l_charge_center_name VARCHAR2(30);
405 l_charge_service_name VARCHAR2(30);
406
407 --
408 BEGIN
409
410 IF ( funcmode = 'RUN' ) THEN
411
412 -- Get cross charge ID (primary key)
413 l_cross_charge_id := wf_engine.GetItemAttrNumber(
414 itemtype => itemtype,
415 itemkey => itemkey,
416 aname => 'CROSS_CHARGE_ID');
417
418 -- Get cross charge line num
419 l_cc_line_num := wf_engine.GetItemAttrNumber(
420 itemtype => itemtype,
421 itemkey => itemkey,
422 aname => 'CC_LINE_NUM');
423
424 diagn_msg(l_state_level,'get_cc_attributes','Executing Get_CC_Attributes for cross charge line '
425 ||to_char(l_cross_charge_id)||'/'||to_char(l_cc_line_num));
426
427
428 -- Get the amount of the cross charge line, the service line id
429 -- and the charge center id of the receiving charge center
430 -- and the name of the service
431
432 --Bug 2885987. Sql modified to remove MJC.
433
434 SELECT abs(nvl(itrl.entered_dr, 0) - nvl(itrl.entered_cr, 0))
435 ,itrl.it_service_line_id
436 ,cc.name
437 ,servi.name
438 INTO l_cc_line_tot
439 ,l_service_line_id
440 ,l_charge_center_name
441 ,l_charge_service_name
442 FROM igi_itr_charge_lines itrl
443 ,igi_itr_service servi
444 ,igi_itr_charge_center cc
445 ,igi_itr_charge_service serv
446 WHERE itrl.it_header_id = l_cross_charge_id
447 AND itrl.it_line_num = l_cc_line_num
448 AND cc.charge_center_id = itrl.charge_center_id
449 AND serv.charge_service_id = itrl.charge_service_id
450 AND itrl.service_id = servi.service_id
451 AND servi.service_id = serv.service_id
452 AND serv.charge_center_id = cc.charge_center_id;
453
454 --
455 diagn_msg(l_state_level,'get_cc_attributes','CC Attributes retrieved from db');
456 --
457 -- Copy cross charge total to corresponding item attribute in workflow
458 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
459 itemkey => itemkey,
460 aname => 'CC_LINE_AMOUNT',
461 avalue => l_cc_line_tot );
462 diagn_msg(l_state_level,'get_cc_attributes','get_cc_attributes: Cross Charge Line Amount = ' ||to_char(l_cc_line_tot));
463
464
465 -- Copy service line id to corresponding item attribute in workflow
466 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
467 itemkey => itemkey,
468 aname => 'SERVICE_LINE_ID',
469 avalue => l_service_line_id );
470 diagn_msg(l_state_level,'get_cc_attributes','get_cc_attributes: Service Line Id = ' ||to_char(l_service_line_id));
471
472 -- Copy charge center name to corresponding item attribute in workflow
473 wf_engine.SetItemAttrText ( itemtype => itemtype,
474 itemkey => itemkey,
475 aname => 'RECV_CHARGE_CENTER',
476 avalue => l_charge_center_name);
477 diagn_msg(l_state_level,'get_cc_attributes','get_cc_attributes: Charge Center Name = ' ||l_charge_center_name);
478
479 -- Copy charge service name to corresponding item attribute in workflow
480 wf_engine.SetItemAttrText ( itemtype => itemtype,
481 itemkey => itemkey,
482 aname => 'CHARGE_SERVICE_NAME',
483 avalue => l_charge_service_name);
484 diagn_msg(l_state_level,'get_cc_attributes','get_cc_attributes: Charge Service Name = ' ||l_charge_service_name);
485
486 diagn_msg(l_state_level,'get_cc_attributes','CC Attributes stored in WF tables');
487
488 ELSIF ( funcmode = 'CANCEL' ) THEN
489 null;
490 END IF;
491
492 EXCEPTION
493 WHEN OTHERS THEN
494 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'get_cc_attributes', itemtype, itemkey);
495 IF ( l_unexp_level >= l_debug_level) THEN
496 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
497 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
498 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
499 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.get_cc_attributes',TRUE);
500 END IF;
501 raise;
502
503 END get_cc_attributes;
504
505
506 --
507 -- *****************************************************************************
508 -- Did_Preparer_Approve
509 -- *****************************************************************************
510 --
511 PROCEDURE did_preparer_approve (itemtype IN VARCHAR2,
512 itemkey IN VARCHAR2,
513 actid IN NUMBER,
514 funcmode IN VARCHAR2,
515 result OUT NOCOPY VARCHAR2 ) IS
516 l_preparer_auth VARCHAR2(1);
517 BEGIN
518 IF ( funcmode = 'RUN') THEN
519 l_preparer_auth := wf_engine.GetItemAttrText( itemtype => itemtype,
520 itemkey => itemkey,
521 aname => 'PREPARER_AUTH');
522 diagn_msg(l_state_level,'did_preparer_approve','Preparer authorised to approve = '||l_preparer_auth);
523
524 IF l_preparer_auth = 'Y' THEN
525 result := 'COMPLETE:Y';
526 return;
527 ELSE
528 result := 'COMPLETE:N';
529 return;
530 END IF;
531
532 ELSIF ( funcmode = 'CANCEL' ) THEN
533 null;
534 END IF;
535
536 EXCEPTION
537 WHEN OTHERS THEN
538 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'did_preparer_approve', itemtype, itemkey);
539 IF ( l_unexp_level >= l_debug_level) THEN
540 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
541 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
542 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
543 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.did_preparer_approve',TRUE);
544 END IF;
545 raise;
546 END did_preparer_approve;
547
548
549
550 --
551 -- *****************************************************************************
552 -- Set_Approver_Name_to_Prep
553 -- *****************************************************************************
554 --
555 PROCEDURE set_approver_name_to_prep (itemtype IN VARCHAR2,
556 itemkey IN VARCHAR2,
557 actid IN NUMBER,
558 funcmode IN VARCHAR2,
559 result OUT NOCOPY VARCHAR2 ) IS
560 l_preparer_id NUMBER;
561 l_preparer_name VARCHAR2(240);
562 l_preparer_display_name VARCHAR2(240);
563 BEGIN
564 IF ( funcmode = 'RUN') THEN
565 l_preparer_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
566 itemkey => itemkey,
567 aname => 'PREPARER_ID');
568 l_preparer_name := wf_engine.GetItemAttrText( itemtype => itemtype,
569 itemkey => itemkey,
570 aname => 'PREPARER_NAME');
571 l_preparer_display_name := wf_engine.GetItemAttrText( itemtype => itemtype,
572 itemkey => itemkey,
573 aname => 'PREPARER_DISPLAY_NAME');
574 wf_engine.SetItemAttrNumber( itemtype => itemtype,
575 itemkey => itemkey,
576 aname => 'APPROVER_ID',
577 avalue => l_preparer_id );
578 wf_engine.SetItemAttrText( itemtype => itemtype,
579 itemkey => itemkey,
580 aname => 'APPROVER_NAME',
581 avalue => l_preparer_name );
582 wf_engine.SetItemAttrText( itemtype => itemtype,
583 itemkey => itemkey,
584 aname => 'APPROVER_DISPLAY_NAME',
585 avalue => l_preparer_display_name );
586 diagn_msg(l_state_level,'set_approver_name_to_prep','Approver name set for cross charge line ');
587 ELSIF ( funcmode = 'CANCEL' ) THEN
588 null;
589 END IF;
590
591 EXCEPTION
592 WHEN OTHERS THEN
593 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'set_approver_name_to_prep', itemtype, itemkey);
594 IF ( l_unexp_level >= l_debug_level) THEN
595 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
596 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
597 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
598 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.set_approver_name_to_prep',TRUE);
599 END IF;
600 raise;
601 END set_approver_name_to_prep;
602
603
604
605 --
606 -- *****************************************************************************
607 -- Secondary_approver_selected
608 -- *****************************************************************************
609 --
610 PROCEDURE secondary_approver_selected(itemtype IN VARCHAR2,
611 itemkey IN VARCHAR2,
612 actid IN NUMBER,
613 funcmode IN VARCHAR2,
614 result OUT NOCOPY VARCHAR2 ) IS
615 l_sec_approver_fnd_id NUMBER;
616 BEGIN
617 IF ( funcmode = 'RUN') THEN
618 l_sec_approver_fnd_id := wf_engine.GetItemAttrText( itemtype => itemtype,
619 itemkey => itemkey,
620 aname => 'SEC_APPROVER_FND_ID');
621
622 IF l_sec_approver_fnd_id is not null THEN
623 result := 'COMPLETE:Y';
624 return;
625 ELSE
626 result := 'COMPLETE:N';
627 return;
628 END IF;
629
630 ELSIF ( funcmode = 'CANCEL' ) THEN
631 null;
632 END IF;
633
634 EXCEPTION
635 WHEN OTHERS THEN
636 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'secondary_approver_selected', itemtype, itemkey);
637 IF ( l_unexp_level >= l_debug_level) THEN
638 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
639 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
640 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
641 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.secondary_approver_selected',TRUE);
642 END IF;
643 raise;
644 END secondary_approver_selected;
645
646
647
648 --
649 -- *****************************************************************************
650 -- Set_Approver_Name_to_Sec_App
651 -- *****************************************************************************
652 --
653 PROCEDURE set_approver_name_to_sec_app (itemtype IN VARCHAR2,
654 itemkey IN VARCHAR2,
655 actid IN NUMBER,
656 funcmode IN VARCHAR2,
657 result OUT NOCOPY VARCHAR2 ) IS
658 l_sec_approver_id NUMBER;
659 l_sec_approver_name VARCHAR2(240);
660 l_sec_approver_display_name VARCHAR2(240);
661 BEGIN
662 IF ( funcmode = 'RUN') THEN
663 l_sec_approver_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
664 itemkey => itemkey,
665 aname => 'SEC_APPROVER_ID');
666 l_sec_approver_name := wf_engine.GetItemAttrText( itemtype => itemtype,
667 itemkey => itemkey,
668 aname => 'SEC_APPROVER_NAME');
669 l_sec_approver_display_name := wf_engine.GetItemAttrText( itemtype => itemtype,
670 itemkey => itemkey,
671 aname => 'SEC_APPROVER_DISPLAY_NAME');
672 wf_engine.SetItemAttrNumber( itemtype => itemtype,
673 itemkey => itemkey,
674 aname => 'APPROVER_ID',
675 avalue => l_sec_approver_id );
676 wf_engine.SetItemAttrText( itemtype => itemtype,
677 itemkey => itemkey,
678 aname => 'APPROVER_NAME',
679 avalue => l_sec_approver_name );
680 wf_engine.SetItemAttrText( itemtype => itemtype,
681 itemkey => itemkey,
682 aname => 'APPROVER_DISPLAY_NAME',
683 avalue => l_sec_approver_display_name );
684 diagn_msg(l_state_level,'set_approver_name_to_sec_app','Approver name set for cross charge line ');
685 ELSIF ( funcmode = 'CANCEL' ) THEN
686 null;
687 END IF;
688
689 EXCEPTION
690 WHEN OTHERS THEN
691 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'set_approver_name_to_sec_app', itemtype, itemkey);
692 IF ( l_unexp_level >= l_debug_level) THEN
693 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
694 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
695 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
696 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.set_approver_name_to_sec_app',TRUE);
697 END IF;
698 raise;
699 END set_approver_name_to_sec_app;
700
701
702 --
703 -- ****************************************************************************
704 -- Procedure maintain_history
705 -- ****************************************************************************
706 --
707 PROCEDURE maintain_history (itemtype IN VARCHAR2,
708 itemkey IN VARCHAR2,
709 actid IN NUMBER,
710 funcmode IN VARCHAR2,
711 result OUT NOCOPY VARCHAR2) IS
712 l_service_line_id NUMBER;
713 l_sequence_num NUMBER;
714 l_action_code VARCHAR2(1);
715 l_performer_id NUMBER;
716 l_user_id NUMBER;
717 l_login_id NUMBER;
718 BEGIN
719 IF funcmode = 'RUN' THEN
720 diagn_msg(l_state_level,'maintain_history','Executing maintain_history');
721
722 l_service_line_id := wf_engine.GetItemAttrNumber(
723 itemtype => itemtype,
724 itemkey => itemkey,
725 aname => 'SERVICE_LINE_ID' );
726
727 SELECT max(sequence_num) + 1
728 INTO l_sequence_num
729 FROM igi_itr_action_history
730 WHERE it_service_line_id = l_service_line_id;
731
732 l_action_code := wf_engine.GetActivityAttrText(
733 itemtype => itemtype,
734 itemkey => itemkey,
735 actid => actid,
736 aname => 'ACTION_CODE' );
737
738 l_performer_id := wf_engine.GetActivityAttrText(
739 itemtype => itemtype,
740 itemkey => itemkey,
741 actid => actid,
742 aname => 'PERFORMER_ID' );
743
744 l_user_id := fnd_global.user_id;
745 l_user_id := fnd_global.login_id;
746
747
748 -- Call the table handler to update the ITR action history table
749 -- with the action performed
750
751 igi_itr_action_history_ss_pkg.insert_row(
752 X_Service_Line_Id => l_service_line_id
753 ,X_Sequence_Num => l_sequence_num
754 ,X_Action_Code => l_action_code
755 ,X_Action_Date => sysdate
756 ,X_Employee_Id => l_performer_id
757 ,X_Use_Workflow_Flag => 'Y'
758 ,X_Note => null
759 ,X_Created_By => l_user_id
760 ,X_Creation_Date => sysdate
761 ,X_Last_Update_Login => l_login_id
762 ,X_Last_Update_Date => sysdate
763 ,X_Last_Updated_By => l_user_id);
764
765
766 ELSIF ( funcmode = 'CANCEL' ) THEN
767 null;
768
769 END IF;
770
771 EXCEPTION
772 WHEN OTHERS THEN
773 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'maintain_history', itemtype, itemkey);
774 IF ( l_unexp_level >= l_debug_level) THEN
775 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
776 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
777 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
778 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.maintain_history',TRUE);
779 END IF;
780 raise;
781 END maintain_history;
782
783
784
785
786 --
787 -- ****************************************************************************
788 -- Procedure submit_cc_line
789 -- ****************************************************************************
790 --
791 PROCEDURE submit_cc_line (itemtype IN VARCHAR2,
792 itemkey IN VARCHAR2,
793 actid IN NUMBER,
794 funcmode IN VARCHAR2,
795 result OUT NOCOPY VARCHAR2) IS
796 l_cross_charge_id NUMBER;
797 l_cc_line_num NUMBER;
798 BEGIN
799 IF funcmode = 'RUN' THEN
800 diagn_msg(l_state_level,'submit_cc_line','Executing Submit_CC_line');
801
802 l_cross_charge_id := wf_engine.GetItemAttrNumber(
803 itemtype => itemtype,
804 itemkey => itemkey,
805 aname => 'CROSS_CHARGE_ID' );
806
807 l_cc_line_num := wf_engine.GetItemAttrNumber( itemtype => itemtype,
808 itemkey => itemkey,
809 aname => 'CC_LINE_NUM');
810
811 --
812 -- Set status of Cross Charge Line to 'V' for 'Awaiting Receiver Approval'
813
814 UPDATE IGI_ITR_CHARGE_LINES
815 SET status_flag = 'V'
816 WHERE it_header_id = l_cross_charge_id
817 AND it_line_num = l_cc_line_num;
818
819
820 diagn_msg(l_state_level,'submit_cc_line','Cross Charge Line'||to_char(l_cross_charge_id)||'/'||to_char(l_cc_line_num)||' has been submitted');
821 --
822 ELSIF ( funcmode = 'CANCEL' ) THEN
823 null;
824
825 END IF;
826
827 EXCEPTION
828 WHEN OTHERS THEN
829 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'submit_cc_line', itemtype, itemkey);
830 IF ( l_unexp_level >= l_debug_level) THEN
831 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
832 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
833 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
834 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.submit_cc_line',TRUE);
835 END IF;
836 raise;
837 END submit_cc_line;
838
839
840
841 --
842 -- ****************************************************************************
843 -- Procedure no_submit_cc_line
844 -- ****************************************************************************
845 --
846 PROCEDURE no_submit_cc_line (itemtype IN VARCHAR2,
847 itemkey IN VARCHAR2,
848 actid IN NUMBER,
849 funcmode IN VARCHAR2,
850 result OUT NOCOPY VARCHAR2) IS
851 l_cross_charge_id NUMBER;
852 l_cc_line_num NUMBER;
853 BEGIN
854 IF funcmode = 'RUN' THEN
855 diagn_msg(l_state_level,'no_submit_cc_line','Executing No_Submit_CC_line');
856
857 l_cross_charge_id := wf_engine.GetItemAttrNumber(
858 itemtype => itemtype,
859 itemkey => itemkey,
860 aname => 'CROSS_CHARGE_ID' );
861
862 l_cc_line_num := wf_engine.GetItemAttrNumber( itemtype => itemtype,
863 itemkey => itemkey,
864 aname => 'CC_LINE_NUM');
865
866 --
867 -- Set status of Cross Charge Line to 'J' for 'Rejected in Creation'
868
869 UPDATE IGI_ITR_CHARGE_LINES
870 SET status_flag = 'J'
871 WHERE it_header_id = l_cross_charge_id
872 AND it_line_num = l_cc_line_num;
873
874 diagn_msg(l_state_level,'no_submit_cc_line','Cross Charge Line'||to_char(l_cross_charge_id)||'/'||to_char(l_cc_line_num)||' has not been submitted');
875 --
876 ELSIF ( funcmode = 'CANCEL' ) THEN
877 null;
878
879 END IF;
880
881 EXCEPTION
882 WHEN OTHERS THEN
883 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'no_submit_cc_line', itemtype, itemkey);
884 IF ( l_unexp_level >= l_debug_level) THEN
885 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
886 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
887 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
888 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.no_submit_cc_line',TRUE);
889 END IF;
890 raise;
891 END no_submit_cc_line;
892
893
894
895
896
897 --
898 -- *****************************************************************************
899 -- find_cc_receiver
900 -- *****************************************************************************
901 --
902 PROCEDURE find_cc_receiver(itemtype IN VARCHAR2,
903 itemkey IN VARCHAR2,
904 actid IN NUMBER,
905 funcmode IN VARCHAR2,
906 result OUT NOCOPY VARCHAR2 ) IS
907
908 l_cross_charge_id NUMBER;
909 l_cc_line_num NUMBER;
910 l_charge_range_id NUMBER;
911 l_rec_fnd_user_id NUMBER;
912 l_receiver_id NUMBER;
913 l_receiver_name VARCHAR2(240);
914 l_receiver_display_name VARCHAR2(240);
915
916
917 BEGIN
918
919 IF ( funcmode = 'RUN' ) THEN
920
921 -- Get cross charge ID (primary key)
922 l_cross_charge_id := wf_engine.GetItemAttrNumber(
923 itemtype => itemtype,
924 itemkey => itemkey,
925 aname => 'CROSS_CHARGE_ID');
926
927 -- Get cross charge line num
928 l_cc_line_num := wf_engine.GetItemAttrNumber(
929 itemtype => itemtype,
930 itemkey => itemkey,
931 aname => 'CC_LINE_NUM');
932
933
934 -- Get the receiver fnd user id for the service line
935 -- This is found using the charge_range_id of the charge range
936 -- which was valid at the time of charge entry.
937
938 SELECT auth.authoriser_id
939 INTO l_rec_fnd_user_id
940 FROM igi_itr_charge_ranges auth
941 ,igi_itr_charge_lines itrl
942 WHERE itrl.it_header_id = l_cross_charge_id
943 AND itrl.it_line_num = l_cc_line_num
944 AND itrl.charge_range_id = auth.charge_range_id;
945
946
947 -- Set Receiver fnd user ID attribute (AOL user ID from FND_USER)
948 wf_engine.SetItemAttrNumber( itemtype => itemtype,
949 itemkey => itemkey,
950 aname => 'RECEIVER_FND_ID',
951 avalue => l_rec_fnd_user_id);
952 diagn_msg(l_state_level,'find_cc_receiver','Attribute RECEIVER_FND_ID set to ' ||l_rec_fnd_user_id );
953
954
955 -- Get employee ID of receiver
956 SELECT employee_id
957 INTO l_receiver_id
958 FROM fnd_user
959 WHERE user_id = l_rec_fnd_user_id;
960
961 -- Set PersonID attribute (HR personID from PER_PERSONS_F)
962 wf_engine.SetItemAttrNumber( itemtype => itemtype,
963 itemkey => itemkey,
964 aname => 'RECEIVER_ID',
965 avalue => l_receiver_id);
966 diagn_msg(l_state_level,'find_cc_receiver','Attribute RECEIVER_ID set to ' ||l_receiver_id );
967
968
969 -- Retrieve receiver's User name (Login name for Apps) and displayed name
970 wf_directory.GetUserName(p_orig_system => 'PER',
971 p_orig_system_id => l_receiver_id,
972 p_name => l_receiver_name,
973 p_display_name => l_receiver_display_name );
974 diagn_msg(l_state_level,'find_cc_receiver','Retrieved user name: '||l_receiver_name);
975 diagn_msg(l_state_level,'find_cc_receiver','Retrieved user display name: '||l_receiver_display_name);
976
977 -- Copy username to Workflow
978 wf_engine.SetItemAttrText( itemtype => itemtype,
979 itemkey => itemkey,
980 aname => 'RECEIVER_NAME',
981 avalue => l_receiver_name );
982 diagn_msg(l_state_level,'find_cc_receiver','Attribute RECEIVER_NAME set to' ||l_receiver_name);
983
984 -- Copy displayed username to Workflow
985 wf_engine.SetItemAttrText( itemtype => itemtype,
986 itemkey => itemkey,
987 aname => 'RECEIVER_DISPLAY_NAME',
988 avalue => l_receiver_display_name );
989 diagn_msg(l_state_level,'find_cc_receiver','Attribute RECEIVER_DISPLAY_NAME set to '||l_receiver_display_name);
990
991
992
993 result := 'COMPLETE:Y';
994
995 ELSIF (funcmode = 'CANCEL') THEN
996 NULL;
997 END IF;
998
999 EXCEPTION
1000 WHEN OTHERS THEN
1001 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'find_cc_receiver', itemtype, itemkey);
1002 IF ( l_unexp_level >= l_debug_level) THEN
1003 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1004 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1005 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1006 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.find_cc_receiver',TRUE);
1007 END IF;
1008 raise;
1009 END find_cc_receiver;
1010
1011
1012 --
1013 -- *****************************************************************************
1014 -- Set_Approver_Name_to_Rec
1015 -- *****************************************************************************
1016 --
1017 PROCEDURE set_approver_name_to_rec (itemtype IN VARCHAR2,
1018 itemkey IN VARCHAR2,
1019 actid IN NUMBER,
1020 funcmode IN VARCHAR2,
1021 result OUT NOCOPY VARCHAR2 ) IS
1022 l_receiver_id NUMBER;
1023 l_receiver_name VARCHAR2(240);
1024 l_receiver_display_name VARCHAR2(240);
1025 BEGIN
1026 IF ( funcmode = 'RUN') THEN
1027 l_receiver_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1028 itemkey => itemkey,
1029 aname => 'RECEIVER_ID');
1030 l_receiver_name := wf_engine.GetItemAttrText( itemtype => itemtype,
1031 itemkey => itemkey,
1032 aname => 'RECEIVER_NAME');
1033 l_receiver_display_name := wf_engine.GetItemAttrText( itemtype => itemtype,
1034 itemkey => itemkey,
1035 aname => 'RECEIVER_DISPLAY_NAME');
1036 wf_engine.SetItemAttrNumber( itemtype => itemtype,
1037 itemkey => itemkey,
1038 aname => 'APPROVER_ID',
1039 avalue => l_receiver_id );
1040 wf_engine.SetItemAttrText( itemtype => itemtype,
1041 itemkey => itemkey,
1042 aname => 'APPROVER_NAME',
1043 avalue => l_receiver_name );
1044 wf_engine.SetItemAttrText( itemtype => itemtype,
1045 itemkey => itemkey,
1046 aname => 'APPROVER_DISPLAY_NAME',
1047 avalue => l_receiver_display_name );
1048 diagn_msg(l_state_level,'set_approver_name_to_rec','Approver name set for cross charge line ');
1049 ELSIF ( funcmode = 'CANCEL' ) THEN
1050 null;
1051 END IF;
1052
1053 EXCEPTION
1054 WHEN OTHERS THEN
1055 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'set_approver_name_to_rec', itemtype, itemkey);
1056 IF ( l_unexp_level >= l_debug_level) THEN
1057 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1058 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1059 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1060 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.set_approver_name_to_rec',TRUE);
1061 END IF;
1062 raise;
1063 END set_approver_name_to_rec;
1064
1065
1066 --
1067 -- *****************************************************************************
1068 -- Procedure Double_Timeout
1069 -- *****************************************************************************
1070 --
1071 PROCEDURE double_timeout (itemtype IN VARCHAR2,
1072 itemkey IN VARCHAR2,
1073 actid IN NUMBER,
1074 funcmode IN VARCHAR2,
1075 result OUT NOCOPY VARCHAR2 ) IS
1076
1077 l_set_of_books_id NUMBER;
1078 l_use_double_timeout VARCHAR2(1);
1079
1080 BEGIN
1081 IF ( funcmode = 'RUN') THEN
1082
1083 diagn_msg(l_state_level,'double_timeout','Double_Timeout');
1084
1085 l_set_of_books_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1086 itemkey => itemkey,
1087 aname => 'SET_OF_BOOKS_ID');
1088
1089
1090 SELECT nvl(use_double_timeout_flag,'N')
1091 INTO l_use_double_timeout
1092 FROM igi_itr_charge_setup
1093 WHERE set_of_books_id = l_set_of_books_id;
1094
1095 IF (l_use_double_timeout = 'Y') THEN
1096 result := 'COMPLETE:Y';
1097 return;
1098 ELSE
1099 result := 'COMPLETE:N';
1100 return;
1101 END IF;
1102
1103 ELSIF ( funcmode = 'CANCEL' ) THEN
1104 null;
1105 END IF;
1106
1107 EXCEPTION
1108 WHEN OTHERS THEN
1109 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'double_timeout', itemtype, itemkey);
1110 IF ( l_unexp_level >= l_debug_level) THEN
1111 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1112 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1113 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1114 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.double_timeout',TRUE);
1115 END IF;
1116 raise;
1117 END double_timeout;
1118
1119
1120
1121 --
1122 -- *****************************************************************************
1123 -- Procedure Final_Approver
1124 -- *****************************************************************************
1125 --
1126 PROCEDURE final_approver (itemtype IN VARCHAR2,
1127 itemkey IN VARCHAR2,
1128 actid IN NUMBER,
1129 funcmode IN VARCHAR2,
1130 result OUT NOCOPY VARCHAR2 ) IS
1131 l_approver_id NUMBER;
1132 l_approval_amount NUMBER;
1133 l_set_of_books_id NUMBER;
1134 l_approval_limit NUMBER;
1135 BEGIN
1136 IF ( funcmode = 'RUN') THEN
1137
1138 diagn_msg(l_state_level,'final_approver','Final_Approver');
1139 l_approver_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1140 itemkey => itemkey,
1141 aname => 'APPROVER_ID');
1142 l_approval_amount := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1143 itemkey => itemkey,
1144 aname => 'CC_LINE_AMOUNT');
1145 l_set_of_books_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1146 itemkey => itemkey,
1147 aname => 'SET_OF_BOOKS_ID');
1148
1149 l_approval_limit := get_authorization_limit(l_approver_id,
1150 l_set_of_books_id);
1151
1152 IF (l_approval_limit >= l_approval_amount) THEN
1153 result := 'COMPLETE:Y';
1154 return;
1155 ELSE
1156 result := 'COMPLETE:N';
1157 return;
1158 END IF;
1159
1160 ELSIF ( funcmode = 'CANCEL' ) THEN
1161 null;
1162 END IF;
1163
1164 EXCEPTION
1165 WHEN OTHERS THEN
1166 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'final_approver', itemtype, itemkey);
1167 IF ( l_unexp_level >= l_debug_level) THEN
1168 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1169 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1170 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1171 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.final_approver',TRUE);
1172 END IF;
1173 raise;
1174 END final_approver;
1175
1176
1177 --
1178 -- *****************************************************************************
1179 -- Procedure Is_Receiver_Final_Approver
1180 -- *****************************************************************************
1181 --
1182 PROCEDURE is_receiver_final_approver (itemtype IN VARCHAR2,
1183 itemkey IN VARCHAR2,
1184 actid IN NUMBER,
1185 funcmode IN VARCHAR2,
1186 result OUT NOCOPY VARCHAR2 ) IS
1187 l_approver_id NUMBER;
1188 l_receiver_id NUMBER;
1189 BEGIN
1190 IF ( funcmode = 'RUN') THEN
1191
1192 diagn_msg(l_state_level,'is_receiver_final_approver','Is Receiver Final_Approver');
1193 l_approver_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1194 itemkey => itemkey,
1195 aname => 'APPROVER_ID');
1196 l_receiver_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1197 itemkey => itemkey,
1198 aname => 'RECEIVER_ID');
1199
1200 IF (l_receiver_id = l_approver_id) THEN
1201 result := 'COMPLETE:Y';
1202 return;
1203 ELSE
1204 result := 'COMPLETE:N';
1205 return;
1206 END IF;
1207
1208 ELSIF ( funcmode = 'CANCEL' ) THEN
1209 null;
1210 END IF;
1211
1212 EXCEPTION
1213 WHEN OTHERS THEN
1214 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'is_receiver_final_approver', itemtype, itemkey);
1215 IF ( l_unexp_level >= l_debug_level) THEN
1216 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1217 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1218 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1219 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.is_receiver_final_approver',TRUE);
1220 END IF;
1221 raise;
1222 END is_receiver_final_approver;
1223
1224
1225 --
1226 -- ****************************************************************************
1227 -- Procedure Verify_Authority
1228 -- ****************************************************************************
1229 --
1230 PROCEDURE verify_authority( itemtype IN VARCHAR2,
1231 itemkey IN VARCHAR2,
1232 actid IN NUMBER,
1233 funcmode IN VARCHAR2,
1234 result OUT NOCOPY VARCHAR2 ) IS
1235 l_approver_id NUMBER;
1236 l_approval_limit NUMBER;
1237 l_approval_amount NUMBER;
1238 l_set_of_books_id NUMBER;
1239 BEGIN
1240 IF funcmode = 'RUN' THEN
1241
1242 diagn_msg(l_state_level,'verify_authority','Executing Verify_Authority');
1243 l_approver_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1244 itemkey => itemkey,
1245 aname => 'APPROVER_ID');
1246 l_approval_amount := wf_engine.GetItemAttrNumber( itemtype,
1247 itemkey,
1248 'CC_LINE_AMOUNT');
1249 l_set_of_books_id := wf_engine.GetItemAttrNumber(
1250 itemtype => itemtype,
1251 itemkey => itemkey,
1252 aname => 'SET_OF_BOOKS_ID');
1253
1254 l_approval_limit := get_authorization_limit(l_approver_id, l_set_of_books_id);
1255
1256 IF (l_approval_limit >= l_approval_amount) THEN
1257 result := 'COMPLETE:PASS';
1258 ELSE
1259 result := 'COMPLETE:FAIL';
1260 END IF;
1261
1262 ELSIF ( funcmode = 'CANCEL' ) THEN
1263 NULL;
1264 END IF;
1265
1266 EXCEPTION
1267 WHEN OTHERS THEN
1268 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'verify_authority', itemtype, itemkey);
1269 IF ( l_unexp_level >= l_debug_level) THEN
1270 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1271 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1272 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1273 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.verify_authority',TRUE);
1274 END IF;
1275 raise;
1276 END verify_authority;
1277
1278
1279 --
1280 -- ****************************************************************************
1281 -- Procedure approve_cc_line
1282 -- ****************************************************************************
1283 --
1284 PROCEDURE approve_cc_line (itemtype IN VARCHAR2,
1285 itemkey IN VARCHAR2,
1286 actid IN NUMBER,
1287 funcmode IN VARCHAR2,
1288 result OUT NOCOPY VARCHAR2) IS
1289 l_cross_charge_id NUMBER;
1290 l_cc_line_num NUMBER;
1291 BEGIN
1292 IF funcmode = 'RUN' THEN
1293 diagn_msg(l_state_level,'approve_cc_line','Executing Approve_CC_line');
1294
1295 l_cross_charge_id := wf_engine.GetItemAttrNumber(
1296 itemtype => itemtype,
1297 itemkey => itemkey,
1298 aname => 'CROSS_CHARGE_ID' );
1299
1300 l_cc_line_num := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1301 itemkey => itemkey,
1302 aname => 'CC_LINE_NUM');
1303
1304 --
1305 -- Set status of Cross Charge Line to 'A' for 'Approved'
1306
1307 UPDATE IGI_ITR_CHARGE_LINES
1308 SET status_flag = 'A'
1309 WHERE it_header_id = l_cross_charge_id
1310 AND it_line_num = l_cc_line_num;
1311
1312 diagn_msg(l_state_level,'approve_cc_line','Cross Charge Line'||to_char(l_cross_charge_id)||'/'||to_char(l_cc_line_num)||' has been accepted');
1313 --
1314 -- Since this service line has been approved, need to check the
1315 -- cross charge to see if ALL the service lines have been either approved
1316 -- or cancelled, in which case the cross charge will be Complete
1317 -- Therefore, need to call the following procedure which will
1318 -- handle this checking.
1319
1320 IGIGITCH.update_header_status(l_cross_charge_id);
1321
1322
1323 ELSIF ( funcmode = 'CANCEL' ) THEN
1324 null;
1325
1326 END IF;
1327
1328 EXCEPTION
1329 WHEN OTHERS THEN
1330 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'approve_cc_line', itemtype, itemkey);
1331 IF ( l_unexp_level >= l_debug_level) THEN
1332 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1333 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1334 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1335 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.approve_cc_line',TRUE);
1336 END IF;
1337 raise;
1338 END approve_cc_line;
1339
1340
1341 --
1342 -- ****************************************************************************
1343 -- Procedure Reject_cc_line
1344 -- ****************************************************************************
1345 --
1346 PROCEDURE reject_cc_line (itemtype IN VARCHAR2,
1347 itemkey IN VARCHAR2,
1348 actid IN NUMBER,
1349 funcmode IN VARCHAR2,
1350 result OUT NOCOPY VARCHAR2 ) IS
1351 l_cross_charge_id NUMBER;
1352 l_cc_line_num NUMBER;
1353 BEGIN
1354 IF ( funcmode = 'RUN') THEN
1355 l_cross_charge_id := wf_engine.GetItemAttrNumber(
1356 itemtype => itemtype,
1357 itemkey => itemkey,
1358 aname => 'CROSS_CHARGE_ID');
1359
1360 l_cc_line_num := wf_engine.GetItemAttrNumber(
1361 itemtype => itemtype,
1362 itemkey => itemkey,
1363 aname => 'CC_LINE_NUM');
1364
1365 -- change status of cross charge line to 'R' for 'Rejected by Receiver'
1366
1367 UPDATE IGI_ITR_CHARGE_LINES
1368 SET status_flag = 'R'
1369 WHERE it_header_id = l_cross_charge_id
1370 AND it_line_num = l_cc_line_num;
1371
1372 ELSIF ( funcmode = 'CANCEL' ) THEN
1373 null;
1374 END IF;
1375
1376 EXCEPTION
1377 WHEN OTHERS THEN
1378 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'reject_cc_line', itemtype, itemkey);
1379 IF ( l_unexp_level >= l_debug_level) THEN
1380 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1381 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1382 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1383 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.reject_cc_line',TRUE);
1384 END IF;
1385 raise;
1386 END reject_cc_line;
1387
1388
1389 --
1390 -- ****************************************************************************
1391 -- Procedure getmanager
1392 -- ****************************************************************************
1393 --
1394 PROCEDURE getmanager( employee_id IN NUMBER,
1395 manager_id OUT NOCOPY NUMBER) IS
1396 others EXCEPTION;
1397 l_employee_id NUMBER := employee_id;
1398 BEGIN
1399
1400 diagn_msg(l_state_level,'getmanager','getmanager: employee_id =' ||to_char(employee_id));
1401
1402
1403 SELECT supervisor_id
1404 INTO manager_id
1405 FROM GL_HR_EMPLOYEES_CURRENT_V
1406 WHERE employee_id = l_employee_id;
1407
1408 EXCEPTION
1409 WHEN NO_DATA_FOUND THEN
1410 manager_id := NULL;
1411 WHEN OTHERS THEN
1412 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'getmanager',
1413 null, null, null );
1414 IF ( l_unexp_level >= l_debug_level) THEN
1415 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1416 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1417 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1418 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.getmanager',TRUE);
1419 END IF;
1420 raise;
1421 END getmanager;
1422
1423
1424
1425 --
1426 -- ****************************************************************************
1427 -- Procedure setpersonas
1428 -- ****************************************************************************
1429 --
1430 PROCEDURE setpersonas( manager_id IN NUMBER,
1431 item_type IN VARCHAR2,
1432 item_key IN VARCHAR2,
1433 manager_target IN VARCHAR2) IS
1434 l_manager_name VARCHAR2(240);
1435 l_manager_display_name VARCHAR2(240);
1436 BEGIN
1437
1438 diagn_msg(l_state_level,'setpersonas','Executing the setpersonas activity..');
1439
1440 WF_DIRECTORY.GetUserName('PER',
1441 manager_id,
1442 l_manager_name,
1443 l_manager_display_name);
1444
1445 diagn_msg(l_state_level,'setpersonas','setpersonas: manager_name = ' ||l_manager_name );
1446 diagn_msg(l_state_level,'setpersonas','setpersonas: manager_display_name = ' ||l_manager_display_name );
1447
1448 IF ( manager_target = 'MANAGER') THEN
1449
1450 WF_ENGINE.SetItemAttrText( item_type,
1451 item_key,
1452 'MANAGER_ID',
1453 manager_id);
1454
1455 WF_ENGINE.SetItemAttrText( item_type,
1456 item_key,
1457 'MANAGER_NAME',
1458 l_manager_name);
1459
1460 WF_ENGINE.SetItemAttrText( item_type,
1461 item_key,
1462 'MANAGER_DISPLAY_NAME',
1463 l_manager_display_name);
1464
1465 ELSE
1466
1467 WF_ENGINE.SetItemAttrText( item_type,
1468 item_key,
1469 'APPROVER_ID',
1470 manager_id);
1471
1472 WF_ENGINE.SetItemAttrText( item_type,
1473 item_key,
1474 'APPROVER_NAME',
1475 l_manager_name);
1476
1477 WF_ENGINE.SetItemAttrText( item_type,
1478 item_key,
1479 'APPROVER_DISPLAY_NAME',
1480 l_manager_display_name);
1481
1482 END IF;
1483
1484 EXCEPTION
1485 WHEN OTHERS THEN
1486 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'setpersonas',
1487 item_type, item_key, null );
1488 IF ( l_unexp_level >= l_debug_level) THEN
1489 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1490 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1491 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1492 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.setpersonas',TRUE);
1493 END IF;
1494 raise;
1495 END setpersonas;
1496
1497
1498 --
1499 -- ****************************************************************************
1500 -- Procedure getfinalapprover
1501 -- The parameter l_approver_id_out was added for bug 2709021
1502 -- for the nocopy changes
1503 -- ****************************************************************************
1504 --
1505 PROCEDURE getfinalapprover( p_employee_id IN NUMBER,
1506 p_set_of_books_id IN NUMBER,
1507 p_approval_amount IN NUMBER,
1508 p_item_type IN VARCHAR2,
1509 p_final_approver_id OUT NOCOPY NUMBER) IS
1510 l_approver_id NUMBER;
1511 l_approval_limit NUMBER;
1512 l_approver_id_out NUMBER;
1513 BEGIN
1514
1515 GetManager(p_employee_id,
1516 l_approver_id);
1517
1518 IF (l_approver_id IS NULL) THEN
1519 p_final_approver_id := NULL;
1520 return;
1521 END IF;
1522
1523 LOOP
1524 l_approval_limit := get_authorization_limit(l_approver_id, p_set_of_books_id);
1525
1526 IF (l_approval_limit >= p_approval_amount) THEN
1527 p_final_approver_id := l_approver_id;
1528 return;
1529 END IF;
1530
1531 GetManager(l_approver_id,
1532 l_approver_id_out);
1533
1534 IF (l_approver_id_out IS NULL) THEN
1535 p_final_approver_id := NULL;
1536 return;
1537 END IF;
1538
1539 END LOOP;
1540
1541 EXCEPTION
1542 WHEN OTHERS THEN
1543 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'getfinalapprover',
1544 p_item_type, null, null );
1545 IF ( l_unexp_level >= l_debug_level) THEN
1546 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1547 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1548 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1549 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.getfinalapprover',TRUE);
1550 END IF;
1551 raise;
1552 END getfinalapprover;
1553
1554
1555
1556 --
1557 -- ****************************************************************************
1558 -- Procedure getapprover
1559 -- ****************************************************************************
1560 --
1561 PROCEDURE getapprover( employee_id IN NUMBER,
1562 approval_amount IN NUMBER,
1563 item_type IN VARCHAR2,
1564 item_key IN VARCHAR2,
1565 curr_approver_id IN NUMBER,
1566 find_approver_method IN VARCHAR2,
1567 next_approver_id IN OUT NOCOPY NUMBER ) IS
1568 l_error_message VARCHAR2(2000);
1569 l_set_of_books_id NUMBER;
1570 l_next_approver_id_old NUMBER;
1571
1572 BEGIN
1573
1574 l_next_approver_id_old := next_approver_id;
1575
1576 -- Get set of books id
1577 l_set_of_books_id := wf_engine.GetItemAttrNumber(
1578 itemtype => item_type,
1579 itemkey => item_key,
1580 aname => 'SET_OF_BOOKS_ID');
1581
1582 IF ( find_approver_method = 'S') THEN
1583
1584 IF ( next_approver_id IS NULL) THEN
1585
1586 diagn_msg(l_state_level,'getapprover','Getapprover: Calling getmanager with method equal S ');
1587
1588 IGI_ITR_APPROVAL_PKG.getmanager( curr_approver_id,
1589 next_approver_id);
1590
1591 END IF;
1592
1593 ELSIF ( find_approver_method = 'D') THEN
1594
1595 diagn_msg(l_state_level,'getapprover','Getapprover: Calling getfinalapprover with method equal D');
1596
1597 IGI_ITR_APPROVAL_PKG.getfinalapprover( employee_id,
1598 l_set_of_books_id,
1599 approval_amount,
1600 item_type,
1601 next_approver_id);
1602
1603
1604 ELSIF ( find_approver_method = 'L') THEN
1605
1606 IF ( next_approver_id IS NULL) THEN
1607
1608 diagn_msg(l_state_level,'getapprover','Getapprover: Calling getfinalapprover with method equal L');
1609
1610 IGI_ITR_APPROVAL_PKG.getfinalapprover( curr_approver_id,
1611 l_set_of_books_id,
1612 approval_amount,
1613 item_type,
1614 next_approver_id);
1615 END IF;
1616 ELSE
1617 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_INVALID_APPROVER_METHOD');
1618 l_error_message := FND_MESSAGE.Get;
1619
1620 IF( l_error_level >= l_debug_level) THEN
1621 FND_LOG.MESSAGE(l_error_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.getapprover.msg1', FALSE);
1622 END IF;
1623 wf_engine.SetItemAttrText( item_type,
1624 item_key,
1625 'ERROR_MESSAGE',
1626 l_error_message);
1627 return;
1628
1629 END IF;
1630
1631 EXCEPTION
1632 WHEN OTHERS THEN
1633 next_approver_id := l_next_approver_id_old;
1634 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'getapprover',
1635 null, null, null );
1636 IF ( l_unexp_level >= l_debug_level) THEN
1637 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1638 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1639 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1640 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.getapprover.msg2',TRUE);
1641 END IF;
1642 raise;
1643 END getapprover;
1644
1645
1646 --
1647 -- ****************************************************************************
1648 -- Procedure find_approver
1649 -- ****************************************************************************
1650 --
1651 PROCEDURE find_approver( item_type IN VARCHAR2,
1652 item_key IN VARCHAR2,
1653 actid IN NUMBER,
1654 funmode IN VARCHAR2,
1655 result OUT NOCOPY VARCHAR2) IS
1656 l_employee_id NUMBER;
1657 l_approval_amount NUMBER;
1658 l_sob_id NUMBER;
1659 l_curr_approver_id NUMBER := NULL;
1660 l_next_approver_id NUMBER := NULL;
1661 l_dir_manager_id NUMBER := NULL;
1662 l_find_approver_method VARCHAR2(240);
1663 l_defined BOOLEAN;
1664 l_find_approver_counter NUMBER;
1665 l_error_message VARCHAR2(2000);
1666 BEGIN
1667
1668 IF ( funmode = 'RUN') THEN
1669 diagn_msg(l_state_level,'find_approver','Entering Find_Approver activity');
1670
1671 l_employee_id := wf_engine.GetItemAttrNumber( item_type,
1672 item_key,
1673 'EMPLOYEE_ID');
1674
1675 l_approval_amount := wf_engine.GetItemAttrNumber( item_type,
1676 item_key,
1677 'CC_LINE_AMOUNT');
1678
1679
1680 l_curr_approver_id := wf_engine.GetItemAttrNumber( item_type,
1681 item_key,
1682 'APPROVER_ID');
1683
1684 l_sob_id := wf_engine.GetItemAttrNumber( item_type,
1685 item_key,
1686 'SET_OF_BOOKS_ID');
1687
1688
1689 -- Get the value for the find approver method
1690 SELECT find_approver_method
1691 INTO l_find_approver_method
1692 FROM igi_itr_charge_setup
1693 WHERE set_of_books_id = l_sob_id;
1694
1695
1696 IF (l_find_approver_method IS NULL) THEN
1697 l_find_approver_method := 'S';
1698 END IF;
1699
1700 l_find_approver_counter := wf_engine.GetItemAttrNumber(
1701 item_type,
1702 item_key,
1703 'FIND_APPROVER_COUNTER');
1704
1705 IF (l_find_approver_counter = 0) THEN
1706
1707 diagn_msg(l_state_level,'find_approver','Find_Approver activity is called for the first time. ');
1708
1709 IGI_ITR_APPROVAL_PKG.getmanager(l_employee_id,
1710 l_dir_manager_id);
1711
1712 IGI_ITR_APPROVAL_PKG.setpersonas(l_dir_manager_id,
1713 item_type,
1714 item_key,
1715 'MANAGER');
1716
1717 IF (l_dir_manager_id IS NOT NULL) THEN
1718 l_next_approver_id := l_dir_manager_id;
1719 ELSE
1720 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_CANNOT_FIND_MANAGER');
1721 l_error_message := FND_MESSAGE.Get;
1722 IF( l_error_level >= l_debug_level) THEN
1723 FND_LOG.MESSAGE(l_error_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.getapprover.msg1', FALSE);
1724 END IF;
1725 wf_engine.SetItemAttrText( item_type,
1726 item_key,
1727 'ERROR_MESSAGE',
1728 l_error_message);
1729
1730 result := 'COMPLETE:N';
1731 END IF;
1732
1733
1734 END IF;
1735
1736 IF ((l_curr_approver_id IS NOT NULL) OR
1737 (l_find_approver_method = 'D')) THEN
1738
1739 diagn_msg(l_state_level,'find_approver','Find_Approver: Calling Get Approver ');
1740
1741 GetApprover(l_employee_id,
1742 l_approval_amount,
1743 item_type,
1744 item_key,
1745 l_curr_approver_id,
1746 l_find_approver_method,
1747 l_next_approver_id );
1748
1749 END IF;
1750
1751 IF (l_next_approver_id IS NULL) THEN
1752 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_CANNOT_FIND_APPROVER');
1753 l_error_message := FND_MESSAGE.Get;
1754 IF( l_error_level >= l_debug_level) THEN
1755 FND_LOG.MESSAGE(l_error_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.getapprover.msg2', FALSE);
1756 END IF;
1757 WF_ENGINE.SetItemAttrText( item_type,
1758 item_key,
1759 'ERROR_MESSAGE',
1760 l_error_message);
1761
1762 result := 'COMPLETE:N';
1763
1764 ELSE
1765
1766 IGI_ITR_APPROVAL_PKG.setpersonas(l_next_approver_id,
1767 item_type,
1768 item_key,
1769 'APPROVER');
1770
1771 WF_ENGINE.SetItemAttrNumber( item_type,
1772 item_key,
1773 'FIND_APPROVER_COUNTER',
1774 l_find_approver_counter+1);
1775
1776 result := 'COMPLETE:Y';
1777
1778 END IF;
1779
1780 ELSIF ( funmode = 'CANCEL') THEN
1781 NULL;
1782 END IF;
1783
1784 EXCEPTION
1785 WHEN OTHERS THEN
1786 result := NULL;
1787 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'Find_Approver',
1788 item_type, item_key, null );
1789 IF ( l_unexp_level >= l_debug_level) THEN
1790 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1791 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1792 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1793 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.find_approver',TRUE);
1794 END IF;
1795 raise;
1796
1797 END find_approver;
1798
1799
1800 --
1801 -- ****************************************************************************
1802 -- Procedure record_forward_from_info
1803 -- ****************************************************************************
1804 --
1805 PROCEDURE record_forward_from_info( p_item_type IN VARCHAR2,
1806 p_item_key IN VARCHAR2,
1807 p_actid IN NUMBER,
1808 p_funmode IN VARCHAR2,
1809 p_result OUT NOCOPY VARCHAR2) IS
1810 l_approver_id NUMBER;
1811 l_approver_name VARCHAR2(240);
1812 l_approver_display_name VARCHAR2(240);
1813 BEGIN
1814
1815 IF (p_funmode = 'RUN') THEN
1816
1817 l_approver_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1818 p_item_key,
1819 'APPROVER_ID');
1820
1821 l_approver_name := WF_ENGINE.GetItemAttrText(p_item_type,
1822 p_item_key,
1823 'APPROVER_NAME');
1824
1825 l_approver_display_name := WF_ENGINE.GetItemAttrText(p_item_type,
1826 p_item_key,
1827 'APPROVER_DISPLAY_NAME');
1828
1829 WF_ENGINE.SetItemAttrNumber(p_item_type,
1830 p_item_key,
1831 'FORWARD_FROM_ID',
1832 l_approver_id);
1833
1834 WF_ENGINE.SetItemAttrText(p_item_type,
1835 p_item_key,
1836 'FORWARD_FROM_NAME',
1837 l_approver_name);
1838
1839 WF_ENGINE.SetItemAttrText(p_item_type,
1840 p_item_key,
1841 'FORWARD_FROM_DISPLAY_NAME',
1842 l_approver_display_name);
1843
1844 ELSIF (p_funmode = 'CANCEL') THEN
1845 NULL;
1846 END IF;
1847
1848 EXCEPTION
1849 WHEN OTHERS THEN
1850 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'record_forward_from_info',
1851 p_item_type, p_item_key, to_char(p_actid) );
1852 IF ( l_unexp_level >= l_debug_level) THEN
1853 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1854 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1855 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1856 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.record_forward_from_info',TRUE);
1857 END IF;
1858 raise;
1859 END record_forward_from_info;
1860
1861
1862
1863 --
1864 -- ****************************************************************************
1865 -- PROCEDURE mgr_equalto_aprv
1866 -- ****************************************************************************
1867 --
1868 PROCEDURE mgr_equalto_aprv(p_item_type IN VARCHAR2,
1869 p_item_key IN VARCHAR2,
1870 p_actid IN NUMBER,
1871 p_funmode IN VARCHAR2,
1872 p_result OUT NOCOPY VARCHAR2) IS
1873 l_approver_id NUMBER;
1874 l_manager_id NUMBER;
1875 BEGIN
1876
1877 IF (p_funmode = 'RUN') THEN
1878
1879 l_approver_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1880 p_item_key,
1881 'APPROVER_ID');
1882
1883 l_manager_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1884 p_item_key,
1885 'MANAGER_ID');
1886
1887 IF (l_approver_id <> l_manager_id) THEN
1888 p_result := 'COMPLETE:N';
1889 ELSE
1890 p_result := 'COMPLETE:Y';
1891 END IF;
1892
1893 ELSIF (p_funmode = 'CANCEL') THEN
1894 NULL;
1895 END IF;
1896
1897 EXCEPTION
1898 WHEN OTHERS THEN
1899 p_result := NULL;
1900 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'mgr_equalto_aprv',
1901 p_item_type, p_item_key, to_char(p_actid));
1902 IF ( l_unexp_level >= l_debug_level) THEN
1903 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1904 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1905 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1906 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.mgr_equalto_aprv',TRUE);
1907 END IF;
1908 raise;
1909 END mgr_equalto_aprv;
1910
1911
1912
1913
1914 --
1915 -- *****************************************************************************
1916 -- PROCEDURE First_Approver
1917 -- *****************************************************************************
1918 --
1919 PROCEDURE first_approver(p_item_type IN VARCHAR2,
1920 p_item_key IN VARCHAR2,
1921 p_actid IN NUMBER,
1922 p_funmode IN VARCHAR2,
1923 p_result OUT NOCOPY VARCHAR2) IS
1924 l_find_approver_counter NUMBER;
1925 BEGIN
1926
1927 IF (p_funmode = 'RUN') THEN
1928
1929 diagn_msg(l_state_level,'first_approver','First_Approver: Retrieving Find_Approver_Counter Item Attribute');
1930
1931 l_find_approver_counter := WF_ENGINE.GetItemAttrNumber(p_item_type,
1932 p_item_key,
1933 'FIND_APPROVER_COUNTER');
1934
1935 -- Set the approver comment attribute to null
1936 WF_ENGINE.SetItemAttrText(p_item_type,
1937 p_item_key,
1938 'APPROVER_COMMENT',
1939 '');
1940
1941 IF (l_find_approver_counter = 1) THEN
1942 p_result := 'COMPLETE:Y';
1943 ELSE
1944 p_result := 'COMPLETE:N';
1945 END IF;
1946
1947 ELSIF (p_funmode = 'CANCEL') THEN
1948 NULL;
1949 END IF;
1950
1951 EXCEPTION
1952 WHEN OTHERS THEN
1953 p_result := NULL;
1954 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'first_approver',
1955 p_item_type, p_item_key, to_char(p_actid));
1956 IF ( l_unexp_level >= l_debug_level) THEN
1957 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1958 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1959 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1960 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.first_approver',TRUE);
1961 END IF;
1962 raise;
1963 END first_approver;
1964
1965
1966 --
1967 -- *****************************************************************************
1968 -- Set_Employee_Name_to_Prep
1969 -- *****************************************************************************
1970 --
1971 PROCEDURE set_employee_name_to_prep(itemtype IN VARCHAR2,
1972 itemkey IN VARCHAR2,
1973 actid IN NUMBER,
1974 funcmode IN VARCHAR2,
1975 result OUT NOCOPY VARCHAR2 ) IS
1976 l_preparer_id NUMBER;
1977 l_preparer_name VARCHAR2(240);
1978 l_preparer_display_name VARCHAR2(240);
1979 BEGIN
1980 IF ( funcmode = 'RUN') THEN
1981 l_preparer_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1982 itemkey => itemkey,
1983 aname => 'PREPARER_ID');
1984 l_preparer_name := wf_engine.GetItemAttrText( itemtype => itemtype,
1985 itemkey => itemkey,
1986 aname => 'PREPARER_NAME');
1987 l_preparer_display_name := wf_engine.GetItemAttrText( itemtype => itemtype,
1988 itemkey => itemkey,
1989 aname => 'PREPARER_DISPLAY_NAME');
1990 wf_engine.SetItemAttrNumber( itemtype => itemtype,
1991 itemkey => itemkey,
1992 aname => 'EMPLOYEE_ID',
1993 avalue => l_preparer_id );
1994 wf_engine.SetItemAttrText( itemtype => itemtype,
1995 itemkey => itemkey,
1996 aname => 'EMPLOYEE_NAME',
1997 avalue => l_preparer_name );
1998 wf_engine.SetItemAttrText( itemtype => itemtype,
1999 itemkey => itemkey,
2000 aname => 'EMPLOYEE_DISPLAY_NAME',
2001 avalue => l_preparer_display_name );
2002 diagn_msg(l_state_level,'set_employee_name_to_prep','Employee name set to preparer for cross charge line ');
2003 ELSIF ( funcmode = 'CANCEL' ) THEN
2004 null;
2005 END IF;
2006
2007 EXCEPTION
2008 WHEN OTHERS THEN
2009 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'set_employee_name_to_prep', itemtype, itemkey);
2010 IF ( l_unexp_level >= l_debug_level) THEN
2011 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
2012 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
2013 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
2014 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.set_employee_name_to_prep',TRUE);
2015 END IF;
2016 raise;
2017 END set_employee_name_to_prep;
2018
2019
2020 --
2021 -- *****************************************************************************
2022 -- Set_Employee_Name_to_Rec
2023 -- *****************************************************************************
2024 --
2025 PROCEDURE set_employee_name_to_rec (itemtype IN VARCHAR2,
2026 itemkey IN VARCHAR2,
2027 actid IN NUMBER,
2028 funcmode IN VARCHAR2,
2029 result OUT NOCOPY VARCHAR2 ) IS
2030 l_receiver_id NUMBER;
2031 l_receiver_name VARCHAR2(240);
2032 l_receiver_display_name VARCHAR2(240);
2033 BEGIN
2034 IF ( funcmode = 'RUN') THEN
2035 l_receiver_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2036 itemkey => itemkey,
2037 aname => 'RECEIVER_ID');
2038 l_receiver_name := wf_engine.GetItemAttrText( itemtype => itemtype,
2039 itemkey => itemkey,
2040 aname => 'RECEIVER_NAME');
2041 l_receiver_display_name := wf_engine.GetItemAttrText( itemtype => itemtype,
2042 itemkey => itemkey,
2043 aname => 'RECEIVER_DISPLAY_NAME');
2044 wf_engine.SetItemAttrNumber( itemtype => itemtype,
2045 itemkey => itemkey,
2046 aname => 'EMPLOYEE_ID',
2047 avalue => l_receiver_id );
2048 wf_engine.SetItemAttrText( itemtype => itemtype,
2049 itemkey => itemkey,
2050 aname => 'EMPLOYEE_NAME',
2051 avalue => l_receiver_name );
2052 wf_engine.SetItemAttrText( itemtype => itemtype,
2053 itemkey => itemkey,
2054 aname => 'EMPLOYEE_DISPLAY_NAME',
2055 avalue => l_receiver_display_name );
2056 diagn_msg(l_state_level,'set_employee_name_to_rec','Employee name set to receiver for cross charge line ');
2057 ELSIF ( funcmode = 'CANCEL' ) THEN
2058 null;
2059 END IF;
2060
2061 EXCEPTION
2062 WHEN OTHERS THEN
2063 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'set_employee_name_to_rec', itemtype, itemkey);
2064 IF ( l_unexp_level >= l_debug_level) THEN
2065 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
2066 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
2067 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
2068 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.set_employee_name_to_rec',TRUE);
2069 END IF;
2070 raise;
2071 END set_employee_name_to_rec;
2072
2073
2074
2075 --
2076 -- *****************************************************************************
2077 -- Reset_Approval_Attributes
2078 -- *****************************************************************************
2079 --
2080 PROCEDURE reset_approval_attributes(itemtype IN VARCHAR2,
2081 itemkey IN VARCHAR2,
2082 actid IN NUMBER,
2083 funcmode IN VARCHAR2,
2084 result OUT NOCOPY VARCHAR2 ) IS
2085 BEGIN
2086 IF ( funcmode = 'RUN') THEN
2087 -- setting manager attributes to null
2088 wf_engine.SetItemAttrNumber( itemtype => itemtype,
2089 itemkey => itemkey,
2090 aname => 'MANAGER_ID',
2091 avalue => '' );
2092 wf_engine.SetItemAttrText( itemtype => itemtype,
2093 itemkey => itemkey,
2094 aname => 'MANAGER_NAME',
2095 avalue => '' );
2096 wf_engine.SetItemAttrText( itemtype => itemtype,
2097 itemkey => itemkey,
2098 aname => 'MANAGER_DISPLAY_NAME',
2099 avalue => '' );
2100
2101
2102 -- setting forward_from attributes to null
2103 wf_engine.SetItemAttrNumber( itemtype => itemtype,
2104 itemkey => itemkey,
2105 aname => 'FORWARD_FROM_ID',
2106 avalue => '' );
2107 wf_engine.SetItemAttrText( itemtype => itemtype,
2108 itemkey => itemkey,
2109 aname => 'FORWARD_FROM_NAME',
2110 avalue => '' );
2111 wf_engine.SetItemAttrText( itemtype => itemtype,
2112 itemkey => itemkey,
2113 aname => 'FORWARD_FROM_DISPLAY_NAME',
2114 avalue => '' );
2115
2116
2117 -- setting approver attributes to null
2118 wf_engine.SetItemAttrNumber( itemtype => itemtype,
2119 itemkey => itemkey,
2120 aname => 'APPROVER_ID',
2121 avalue => '' );
2122 wf_engine.SetItemAttrText( itemtype => itemtype,
2123 itemkey => itemkey,
2124 aname => 'APPROVER_NAME',
2125 avalue => '' );
2126 wf_engine.SetItemAttrText( itemtype => itemtype,
2127 itemkey => itemkey,
2128 aname => 'APPROVER_DISPLAY_NAME',
2129 avalue => '' );
2130
2131 -- setting find approver counter attribute to 0
2132 wf_engine.SetItemAttrNumber( itemtype => itemtype,
2133 itemkey => itemkey,
2134 aname => 'FIND_APPROVER_COUNTER',
2135 avalue => 0 );
2136
2137 diagn_msg(l_state_level,'reset_approval_attributes','Reset manager, forward_from and approver attributes');
2138 ELSIF ( funcmode = 'CANCEL' ) THEN
2139 null;
2140 END IF;
2141
2142 EXCEPTION
2143 WHEN OTHERS THEN
2144 Wf_Core.Context('IGI_ITR_APPROVAL_PKG', 'reset_approval_attributes', itemtype, itemkey);
2145 IF ( l_unexp_level >= l_debug_level) THEN
2146 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
2147 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
2148 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
2149 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_APPROVAL_PKG.reset_approval_attributes',TRUE);
2150 END IF;
2151 raise;
2152 END reset_approval_attributes;
2153
2154
2155
2156 --
2157
2158 END IGI_ITR_APPROVAL_PKG;